Open-ILS/src/sql/Pg/upgrade/XXXX.function.actor_usr_delete.sql

   1 BEGIN;
   2
   3 --SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
   4
   5 CREATE OR REPLACE FUNCTION actor.usr_delete(
   6         src_usr  IN INTEGER,
   7         dest_usr IN INTEGER
   8 ) RETURNS VOID AS $$
   9 DECLARE
  10         old_profile actor.usr.profile%type;
  11         old_home_ou actor.usr.home_ou%type;
  12         new_profile actor.usr.profile%type;
  13         new_home_ou actor.usr.home_ou%type;
  14         new_name    text;
  15         new_dob     actor.usr.dob%type;
  16 BEGIN
  17         SELECT
  18                 id || '-PURGED-' || now(),
  19                 profile,
  20                 home_ou,
  21                 dob
  22         INTO
  23                 new_name,
  24                 old_profile,
  25                 old_home_ou,
  26                 new_dob
  27         FROM
  28                 actor.usr
  29         WHERE
  30                 id = src_usr;
  31         --
  32         -- Quit if no such user
  33         --
  34         IF old_profile IS NULL THEN
  35                 RETURN;
  36         END IF;
  37         --
  38         perform actor.usr_purge_data( src_usr, dest_usr );
  39         --
  40         -- Find the root grp_tree and the root org_unit.  This would be simpler if we
  41         -- could assume that there is only one root.  Theoretically, someday, maybe,
  42         -- there could be multiple roots, so we take extra trouble to get the right ones.
  43         --
  44         SELECT
  45                 id
  46         INTO
  47                 new_profile
  48         FROM
  49                 permission.grp_ancestors( old_profile )
  50         WHERE
  51                 parent is null;
  52         --
  53         SELECT
  54                 id
  55         INTO
  56                 new_home_ou
  57         FROM
  58                 actor.org_unit_ancestors( old_home_ou )
  59         WHERE
  60                 parent_ou is null;
  61         --
  62         -- Truncate date of birth
  63         --
  64         IF new_dob IS NOT NULL THEN
  65                 new_dob := date_trunc( 'year', new_dob );
  66         END IF;
  67         --
  68         UPDATE
  69                 actor.usr
  70                 SET
  71                         card = NULL,
  72                         profile = new_profile,
  73                         usrname = new_name,
  74                         email = NULL,
  75                         passwd = random()::text,
  76                         standing = DEFAULT,
  77                         ident_type =
  78                         (
  79                                 SELECT MIN( id )
  80                                 FROM config.identification_type
  81                         ),
  82                         ident_value = NULL,
  83                         ident_type2 = NULL,
  84                         ident_value2 = NULL,
  85                         net_access_level = DEFAULT,
  86                         photo_url = NULL,
  87                         prefix = NULL,
  88                         first_given_name = new_name,
  89                         second_given_name = NULL,
  90                         family_name = new_name,
  91                         suffix = NULL,
  92                         alias = NULL,
  93             guardian = NULL,
  94                         day_phone = NULL,
  95                         evening_phone = NULL,
  96                         other_phone = NULL,
  97                         mailing_address = NULL,
  98                         billing_address = NULL,
  99                         home_ou = new_home_ou,
 100                         dob = new_dob,
 101                         active = FALSE,
 102                         master_account = DEFAULT,
 103                         super_user = DEFAULT,
 104                         barred = FALSE,
 105                         deleted = TRUE,
 106                         juvenile = DEFAULT,
 107                         usrgroup = 0,
 108                         claims_returned_count = DEFAULT,
 109                         credit_forward_balance = DEFAULT,
 110                         last_xact_id = DEFAULT,
 111                         alert_message = NULL,
 112                         pref_prefix = NULL,
 113                         pref_first_given_name = NULL,
 114                         pref_second_given_name = NULL,
 115                         pref_family_name = NULL,
 116                         pref_suffix = NULL,
 117                         name_keywords = NULL,
 118                         create_date = now(),
 119                         expire_date = now()
 120         WHERE
 121                 id = src_usr;
 122 END;
 123 $$ LANGUAGE plpgsql;
 124
 125 COMMIT;