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