9cd4e80698eed78a4f3739ec565852b08d514252
[Evergreen.git] / 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                         create_date = now(),
118                         expire_date = now()
119         WHERE
120                 id = src_usr;
121 END;
122 $$ LANGUAGE plpgsql;
123
124 COMMIT;