Open-ILS/src/sql/Pg/upgrade/XXXX.schema.patron-guardian.sql

   1
   2 BEGIN;
   3
   4 ALTER TABLE actor.usr ADD COLUMN guardian TEXT;
   5
   6 CREATE INDEX actor_usr_guardian_idx
   7     ON actor.usr (evergreen.lowercase(guardian));
   8 CREATE INDEX actor_usr_guardian_unaccent_idx
   9     ON actor.usr (evergreen.unaccent_and_squash(guardian));
  10
  11 -- Modify auditor tables accordingly.
  12 SELECT auditor.update_auditors();
  13
  14 -- clear the guardian field on delete
  15 CREATE OR REPLACE FUNCTION actor.usr_delete(
  16         src_usr  IN INTEGER,
  17         dest_usr IN INTEGER
  18 ) RETURNS VOID AS $$
  19 DECLARE
  20         old_profile actor.usr.profile%type;
  21         old_home_ou actor.usr.home_ou%type;
  22         new_profile actor.usr.profile%type;
  23         new_home_ou actor.usr.home_ou%type;
  24         new_name    text;
  25         new_dob     actor.usr.dob%type;
  26 BEGIN
  27         SELECT
  28                 id || '-PURGED-' || now(),
  29                 profile,
  30                 home_ou,
  31                 dob
  32         INTO
  33                 new_name,
  34                 old_profile,
  35                 old_home_ou,
  36                 new_dob
  37         FROM
  38                 actor.usr
  39         WHERE
  40                 id = src_usr;
  41         --
  42         -- Quit if no such user
  43         --
  44         IF old_profile IS NULL THEN
  45                 RETURN;
  46         END IF;
  47         --
  48         perform actor.usr_purge_data( src_usr, dest_usr );
  49         --
  50         -- Find the root grp_tree and the root org_unit.  This would be simpler if we
  51         -- could assume that there is only one root.  Theoretically, someday, maybe,
  52         -- there could be multiple roots, so we take extra trouble to get the right ones.
  53         --
  54         SELECT
  55                 id
  56         INTO
  57                 new_profile
  58         FROM
  59                 permission.grp_ancestors( old_profile )
  60         WHERE
  61                 parent is null;
  62         --
  63         SELECT
  64                 id
  65         INTO
  66                 new_home_ou
  67         FROM
  68                 actor.org_unit_ancestors( old_home_ou )
  69         WHERE
  70                 parent_ou is null;
  71         --
  72         -- Truncate date of birth
  73         --
  74         IF new_dob IS NOT NULL THEN
  75                 new_dob := date_trunc( 'year', new_dob );
  76         END IF;
  77         --
  78         UPDATE
  79                 actor.usr
  80                 SET
  81                         card = NULL,
  82                         profile = new_profile,
  83                         usrname = new_name,
  84                         email = NULL,
  85                         passwd = random()::text,
  86                         standing = DEFAULT,
  87                         ident_type =
  88                         (
  89                                 SELECT MIN( id )
  90                                 FROM config.identification_type
  91                         ),
  92                         ident_value = NULL,
  93                         ident_type2 = NULL,
  94                         ident_value2 = NULL,
  95                         net_access_level = DEFAULT,
  96                         photo_url = NULL,
  97                         prefix = NULL,
  98                         first_given_name = new_name,
  99                         guardian = NULL,
 100                         family_name = new_name,
 101                         suffix = NULL,
 102                         alias = NULL,
 103             guardian = NULL,
 104                         day_phone = NULL,
 105                         evening_phone = NULL,
 106                         other_phone = NULL,
 107                         mailing_address = NULL,
 108                         billing_address = NULL,
 109                         home_ou = new_home_ou,
 110                         dob = new_dob,
 111                         active = FALSE,
 112                         master_account = DEFAULT,
 113                         super_user = DEFAULT,
 114                         barred = FALSE,
 115                         deleted = TRUE,
 116                         juvenile = DEFAULT,
 117                         usrgroup = 0,
 118                         claims_returned_count = DEFAULT,
 119                         credit_forward_balance = DEFAULT,
 120                         last_xact_id = DEFAULT,
 121                         alert_message = NULL,
 122                         create_date = now(),
 123                         expire_date = now()
 124         WHERE
 125                 id = src_usr;
 126 END;
 127 $$ LANGUAGE plpgsql;
 128
 129 INSERT into config.org_unit_setting_type (name, label, description, datatype)
 130 VALUES (
 131     'ui.patron.edit.au.guardian.show',
 132     oils_i18n_gettext(
 133         'ui.patron.edit.au.guardian.show',
 134         'GUI: Show guardian field on patron registration',
 135         'coust', 'label'
 136     ),
 137     oils_i18n_gettext(
 138         'ui.patron.edit.au.guardian.show',
 139         'The guardian field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.',
 140         'coust', 'description'
 141     ),
 142     'bool'
 143 ), (
 144     'ui.patron.edit.au.guardian.suggest',
 145     oils_i18n_gettext(
 146         'ui.patron.edit.au.guardian.suggest',
 147         'GUI: Suggest guardian field on patron registration',
 148         'coust', 'label'
 149     ),
 150     oils_i18n_gettext(
 151         'ui.patron.edit.au.guardian.suggest',
 152         'The guardian field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.',
 153         'coust', 'description'),
 154     'bool'
 155 ), (
 156     'ui.patron.edit.guardian_required_for_juv',
 157     oils_i18n_gettext(
 158         'ui.patron.edit.guardian_required_for_juv',
 159         'GUI: Juvenile account requires parent/guardian',
 160         'coust', 'label'
 161     ),
 162     oils_i18n_gettext(
 163         'ui.patron.edit.guardian_required_for_juv',
 164         'Require a value for the parent/guardian field in the patron editor for patrons marked as juvenile',
 165         'coust', 'description'),
 166     'bool'
 167 );
 168
 169
 170 COMMIT;
 171