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