Stamping 0868 - 'behind_desk' fixes
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / 0868.schema.all_hold_behind_desk.sql
1 -- add missing behind_desk column
2
3 BEGIN;
4
5 SELECT evergreen.upgrade_deps_block_check('0868', :eg_version);
6
7 CREATE OR REPLACE VIEW action.all_hold_request AS
8     SELECT DISTINCT
9            COALESCE(a.post_code, b.post_code) AS usr_post_code,
10            p.home_ou AS usr_home_ou,
11            p.profile AS usr_profile,
12            EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
13            CAST(ahr.requestor <> ahr.usr AS BOOLEAN) AS staff_placed,
14            ahr.id,
15            ahr.request_time,
16            ahr.capture_time,
17            ahr.fulfillment_time,
18            ahr.checkin_time,
19            ahr.return_time,
20            ahr.prev_check_time,
21            ahr.expire_time,
22            ahr.cancel_time,
23            ahr.cancel_cause,
24            ahr.cancel_note,
25            ahr.target,
26            ahr.current_copy,
27            ahr.fulfillment_staff,
28            ahr.fulfillment_lib,
29            ahr.request_lib,
30            ahr.selection_ou,
31            ahr.selection_depth,
32            ahr.pickup_lib,
33            ahr.hold_type,
34            ahr.holdable_formats,
35            CASE
36            WHEN ahr.phone_notify IS NULL THEN FALSE
37            WHEN ahr.phone_notify = '' THEN FALSE
38            ELSE TRUE
39            END AS phone_notify,
40            ahr.email_notify,
41            CASE
42            WHEN ahr.sms_notify IS NULL THEN FALSE
43            WHEN ahr.sms_notify = '' THEN FALSE
44            ELSE TRUE
45            END AS sms_notify,
46            ahr.frozen,
47            ahr.thaw_date,
48            ahr.shelf_time,
49            ahr.cut_in_line,
50            ahr.mint_condition,
51            ahr.shelf_expire_time,
52            ahr.current_shelf_lib,
53            ahr.behind_desk
54     FROM action.hold_request ahr
55          JOIN actor.usr p ON (ahr.usr = p.id)
56          LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
57          LEFT JOIN actor.usr_address b ON (p.billing_address = b.id)
58     UNION ALL
59     SELECT 
60            usr_post_code,
61            usr_home_ou,
62            usr_profile,
63            usr_birth_year,
64            staff_placed,
65            id,
66            request_time,
67            capture_time,
68            fulfillment_time,
69            checkin_time,
70            return_time,
71            prev_check_time,
72            expire_time,
73            cancel_time,
74            cancel_cause,
75            cancel_note,
76            target,
77            current_copy,
78            fulfillment_staff,
79            fulfillment_lib,
80            request_lib,
81            selection_ou,
82            selection_depth,
83            pickup_lib,
84            hold_type,
85            holdable_formats,
86            phone_notify,
87            email_notify,
88            sms_notify,
89            frozen,
90            thaw_date,
91            shelf_time,
92            cut_in_line,
93            mint_condition,
94            shelf_expire_time,
95            current_shelf_lib,
96            behind_desk
97     FROM action.aged_hold_request;
98
99
100
101 CREATE OR REPLACE FUNCTION action.age_hold_on_delete () RETURNS TRIGGER AS $$
102 DECLARE
103 BEGIN
104     -- Archive a copy of the old row to action.aged_hold_request
105
106     INSERT INTO action.aged_hold_request
107            (usr_post_code,
108             usr_home_ou,
109             usr_profile,
110             usr_birth_year,
111             staff_placed,
112             id,
113             request_time,
114             capture_time,
115             fulfillment_time,
116             checkin_time,
117             return_time,
118             prev_check_time,
119             expire_time,
120             cancel_time,
121             cancel_cause,
122             cancel_note,
123             target,
124             current_copy,
125             fulfillment_staff,
126             fulfillment_lib,
127             request_lib,
128             selection_ou,
129             selection_depth,
130             pickup_lib,
131             hold_type,
132             holdable_formats,
133             phone_notify,
134             email_notify,
135             sms_notify,
136             frozen,
137             thaw_date,
138             shelf_time,
139             cut_in_line,
140             mint_condition,
141             shelf_expire_time,
142             current_shelf_lib,
143             behind_desk)
144       SELECT 
145            usr_post_code,
146            usr_home_ou,
147            usr_profile,
148            usr_birth_year,
149            staff_placed,
150            id,
151            request_time,
152            capture_time,
153            fulfillment_time,
154            checkin_time,
155            return_time,
156            prev_check_time,
157            expire_time,
158            cancel_time,
159            cancel_cause,
160            cancel_note,
161            target,
162            current_copy,
163            fulfillment_staff,
164            fulfillment_lib,
165            request_lib,
166            selection_ou,
167            selection_depth,
168            pickup_lib,
169            hold_type,
170            holdable_formats,
171            phone_notify,
172            email_notify,
173            sms_notify,
174            frozen,
175            thaw_date,
176            shelf_time,
177            cut_in_line,
178            mint_condition,
179            shelf_expire_time,
180            current_shelf_lib,
181            behind_desk
182         FROM action.all_hold_request WHERE id = OLD.id;
183
184     RETURN OLD;
185 END;
186 $$ LANGUAGE 'plpgsql';
187
188 COMMIT;
189