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

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