3 SELECT evergreen.upgrade_deps_block_check('0945', :eg_version);
5 -- run the entire update inside a DO block for managing the logic
6 -- of whether to recreate the optional reporter views
9 has_current_circ BOOLEAN;
10 has_billing_summary BOOLEAN;
13 SELECT INTO has_current_circ TRUE FROM pg_views
14 WHERE schemaname = 'reporter' AND viewname = 'classic_current_circ';
16 SELECT INTO has_billing_summary TRUE FROM pg_views
17 WHERE schemaname = 'reporter' AND
18 viewname = 'classic_current_billing_summary';
20 DROP VIEW action.all_circulation;
21 DROP VIEW IF EXISTS reporter.classic_current_circ;
22 DROP VIEW IF EXISTS reporter.classic_current_billing_summary;
23 DROP VIEW reporter.demographic;
24 DROP VIEW auditor.actor_usr_lifecycle;
25 DROP VIEW action.all_hold_request;
28 ALTER dob TYPE DATE USING (dob + '3 hours'::INTERVAL)::DATE;
30 -- alter the auditor table manually to apply the same
31 -- dob mangling logic as above.
32 ALTER TABLE auditor.actor_usr_history
33 ALTER dob TYPE DATE USING (dob + '3 hours'::INTERVAL)::DATE;
35 -- this recreates auditor.actor_usr_lifecycle
36 PERFORM auditor.update_auditors();
38 CREATE VIEW reporter.demographic AS
41 WHEN u.dob IS NULL THEN 'Adult'::text
42 WHEN age(u.dob) > '18 years'::interval THEN 'Adult'::text
44 END AS general_division
47 CREATE VIEW action.all_circulation AS
48 SELECT aged_circulation.id, aged_circulation.usr_post_code,
49 aged_circulation.usr_home_ou, aged_circulation.usr_profile,
50 aged_circulation.usr_birth_year, aged_circulation.copy_call_number,
51 aged_circulation.copy_location, aged_circulation.copy_owning_lib,
52 aged_circulation.copy_circ_lib, aged_circulation.copy_bib_record,
53 aged_circulation.xact_start, aged_circulation.xact_finish,
54 aged_circulation.target_copy, aged_circulation.circ_lib,
55 aged_circulation.circ_staff, aged_circulation.checkin_staff,
56 aged_circulation.checkin_lib, aged_circulation.renewal_remaining,
57 aged_circulation.grace_period, aged_circulation.due_date,
58 aged_circulation.stop_fines_time, aged_circulation.checkin_time,
59 aged_circulation.create_time, aged_circulation.duration,
60 aged_circulation.fine_interval, aged_circulation.recurring_fine,
61 aged_circulation.max_fine, aged_circulation.phone_renewal,
62 aged_circulation.desk_renewal, aged_circulation.opac_renewal,
63 aged_circulation.duration_rule,
64 aged_circulation.recurring_fine_rule,
65 aged_circulation.max_fine_rule, aged_circulation.stop_fines,
66 aged_circulation.workstation, aged_circulation.checkin_workstation,
67 aged_circulation.checkin_scan_time, aged_circulation.parent_circ
68 FROM action.aged_circulation
70 SELECT DISTINCT circ.id,
71 COALESCE(a.post_code, b.post_code) AS usr_post_code,
72 p.home_ou AS usr_home_ou, p.profile AS usr_profile,
73 date_part('year'::text, p.dob)::integer AS usr_birth_year,
74 cp.call_number AS copy_call_number, circ.copy_location,
75 cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
76 cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish,
77 circ.target_copy, circ.circ_lib, circ.circ_staff,
78 circ.checkin_staff, circ.checkin_lib, circ.renewal_remaining,
79 circ.grace_period, circ.due_date, circ.stop_fines_time,
80 circ.checkin_time, circ.create_time, circ.duration,
81 circ.fine_interval, circ.recurring_fine, circ.max_fine,
82 circ.phone_renewal, circ.desk_renewal, circ.opac_renewal,
83 circ.duration_rule, circ.recurring_fine_rule, circ.max_fine_rule,
84 circ.stop_fines, circ.workstation, circ.checkin_workstation,
85 circ.checkin_scan_time, circ.parent_circ
86 FROM action.circulation circ
87 JOIN asset.copy cp ON circ.target_copy = cp.id
88 JOIN asset.call_number cn ON cp.call_number = cn.id
89 JOIN actor.usr p ON circ.usr = p.id
90 LEFT JOIN actor.usr_address a ON p.mailing_address = a.id
91 LEFT JOIN actor.usr_address b ON p.billing_address = b.id;
93 CREATE OR REPLACE VIEW action.all_hold_request AS
94 SELECT DISTINCT COALESCE(a.post_code, b.post_code) AS usr_post_code,
95 p.home_ou AS usr_home_ou, p.profile AS usr_profile,
96 date_part('year'::text, p.dob)::integer AS usr_birth_year,
97 ahr.requestor <> ahr.usr AS staff_placed, ahr.id, ahr.request_time,
98 ahr.capture_time, ahr.fulfillment_time, ahr.checkin_time,
99 ahr.return_time, ahr.prev_check_time, ahr.expire_time,
100 ahr.cancel_time, ahr.cancel_cause, ahr.cancel_note, ahr.target,
101 ahr.current_copy, ahr.fulfillment_staff, ahr.fulfillment_lib,
102 ahr.request_lib, ahr.selection_ou, ahr.selection_depth,
103 ahr.pickup_lib, ahr.hold_type, ahr.holdable_formats,
105 WHEN ahr.phone_notify IS NULL THEN false
106 WHEN ahr.phone_notify = ''::text THEN false
111 WHEN ahr.sms_notify IS NULL THEN false
112 WHEN ahr.sms_notify = ''::text THEN false
115 ahr.frozen, ahr.thaw_date, ahr.shelf_time, ahr.cut_in_line,
116 ahr.mint_condition, ahr.shelf_expire_time, ahr.current_shelf_lib,
118 FROM action.hold_request ahr
119 JOIN actor.usr p ON ahr.usr = p.id
120 LEFT JOIN actor.usr_address a ON p.mailing_address = a.id
121 LEFT JOIN actor.usr_address b ON p.billing_address = b.id
123 SELECT aged_hold_request.usr_post_code, aged_hold_request.usr_home_ou,
124 aged_hold_request.usr_profile, aged_hold_request.usr_birth_year,
125 aged_hold_request.staff_placed, aged_hold_request.id,
126 aged_hold_request.request_time, aged_hold_request.capture_time,
127 aged_hold_request.fulfillment_time, aged_hold_request.checkin_time,
128 aged_hold_request.return_time, aged_hold_request.prev_check_time,
129 aged_hold_request.expire_time, aged_hold_request.cancel_time,
130 aged_hold_request.cancel_cause, aged_hold_request.cancel_note,
131 aged_hold_request.target, aged_hold_request.current_copy,
132 aged_hold_request.fulfillment_staff,
133 aged_hold_request.fulfillment_lib, aged_hold_request.request_lib,
134 aged_hold_request.selection_ou, aged_hold_request.selection_depth,
135 aged_hold_request.pickup_lib, aged_hold_request.hold_type,
136 aged_hold_request.holdable_formats, aged_hold_request.phone_notify,
137 aged_hold_request.email_notify, aged_hold_request.sms_notify,
138 aged_hold_request.frozen, aged_hold_request.thaw_date,
139 aged_hold_request.shelf_time, aged_hold_request.cut_in_line,
140 aged_hold_request.mint_condition,
141 aged_hold_request.shelf_expire_time,
142 aged_hold_request.current_shelf_lib, aged_hold_request.behind_desk
143 FROM action.aged_hold_request;
145 IF has_current_circ THEN
146 RAISE NOTICE 'Recreating optional view reporter.classic_current_circ';
148 CREATE OR REPLACE VIEW reporter.classic_current_circ AS
149 SELECT cl.shortname AS circ_lib,
150 cl.id AS circ_lib_id,
151 circ.xact_start AS xact_start,
152 circ_type.type AS circ_type,
155 ol.shortname AS owning_lib_name,
156 lm.value AS language,
157 lfm.value AS lit_form,
158 ifm.value AS item_form,
159 itm.value AS item_type,
160 sl.name AS shelving_location,
162 g.name AS profile_group,
163 dem.general_division AS demographic_general_division,
165 cn.id AS call_number,
166 cn.label AS call_number_label,
167 call_number_dewey(cn.label) AS dewey,
169 WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$'
173 10 * floor((call_number_dewey(cn.label)::float) / 10), '000'
177 END AS dewey_block_tens,
179 WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$'
183 100 * floor((call_number_dewey(cn.label)::float) / 100), '000'
187 END AS dewey_block_hundreds,
189 WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$'
193 10 * floor((call_number_dewey(cn.label)::float) / 10), '000'
199 10 * floor((call_number_dewey(cn.label)::float) / 10) + 9, '000'
203 END AS dewey_range_tens,
205 WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$'
209 100 * floor((call_number_dewey(cn.label)::float) / 100), '000'
215 100 * floor((call_number_dewey(cn.label)::float) / 100) + 99, '000'
219 END AS dewey_range_hundreds,
220 hl.id AS patron_home_lib,
221 hl.shortname AS patron_home_lib_shortname,
222 paddr.county AS patron_county,
223 paddr.city AS patron_city,
224 paddr.post_code AS patron_zip,
225 sc1.stat_cat_entry AS stat_cat_1,
226 sc2.stat_cat_entry AS stat_cat_2,
227 sce1.value AS stat_cat_1_value,
228 sce2.value AS stat_cat_2_value
229 FROM action.circulation circ
230 JOIN reporter.circ_type circ_type ON (circ.id = circ_type.id)
231 JOIN asset.copy cp ON (cp.id = circ.target_copy)
232 JOIN asset.copy_location sl ON (cp.location = sl.id)
233 JOIN asset.call_number cn ON (cp.call_number = cn.id)
234 JOIN actor.org_unit ol ON (cn.owning_lib = ol.id)
235 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record)
236 JOIN actor.org_unit cl ON (circ.circ_lib = cl.id)
237 JOIN actor.usr p ON (p.id = circ.usr)
238 JOIN actor.org_unit hl ON (p.home_ou = hl.id)
239 JOIN permission.grp_tree g ON (p.profile = g.id)
240 JOIN reporter.demographic dem ON (dem.id = p.id)
241 JOIN actor.usr_address paddr ON (paddr.id = p.billing_address)
242 LEFT JOIN config.language_map lm ON (rd.item_lang = lm.code)
243 LEFT JOIN config.lit_form_map lfm ON (rd.lit_form = lfm.code)
244 LEFT JOIN config.item_form_map ifm ON (rd.item_form = ifm.code)
245 LEFT JOIN config.item_type_map itm ON (rd.item_type = itm.code)
246 LEFT JOIN asset.stat_cat_entry_copy_map sc1 ON (sc1.owning_copy = cp.id AND sc1.stat_cat = 1)
247 LEFT JOIN asset.stat_cat_entry sce1 ON (sce1.id = sc1.stat_cat_entry)
248 LEFT JOIN asset.stat_cat_entry_copy_map sc2 ON (sc2.owning_copy = cp.id AND sc2.stat_cat = 2)
249 LEFT JOIN asset.stat_cat_entry sce2 ON (sce2.id = sc2.stat_cat_entry);
252 IF has_billing_summary THEN
253 RAISE NOTICE 'Recreating optional view reporter.classic_current_billing_summary';
255 CREATE OR REPLACE VIEW reporter.classic_current_billing_summary AS
258 bl.shortname AS billing_location_shortname,
259 bl.name AS billing_location_name,
260 x.billing_location AS billing_location,
261 c.barcode AS barcode,
262 u.home_ou AS usr_home_ou,
263 ul.shortname AS usr_home_ou_shortname,
264 ul.name AS usr_home_ou_name,
265 x.xact_start AS xact_start,
266 x.xact_finish AS xact_finish,
267 x.xact_type AS xact_type,
268 x.total_paid AS total_paid,
269 x.total_owed AS total_owed,
270 x.balance_owed AS balance_owed,
271 x.last_payment_ts AS last_payment_ts,
272 x.last_payment_note AS last_payment_note,
273 x.last_payment_type AS last_payment_type,
274 x.last_billing_ts AS last_billing_ts,
275 x.last_billing_note AS last_billing_note,
276 x.last_billing_type AS last_billing_type,
277 paddr.county AS patron_county,
278 paddr.city AS patron_city,
279 paddr.post_code AS patron_zip,
280 g.name AS profile_group,
281 dem.general_division AS demographic_general_division
282 FROM money.open_billable_xact_summary x
283 JOIN actor.org_unit bl ON (x.billing_location = bl.id)
284 JOIN actor.usr u ON (u.id = x.usr)
285 JOIN actor.org_unit ul ON (u.home_ou = ul.id)
286 JOIN actor.card c ON (u.card = c.id)
287 JOIN permission.grp_tree g ON (u.profile = g.id)
288 JOIN reporter.demographic dem ON (dem.id = u.id)
289 JOIN actor.usr_address paddr ON (paddr.id = u.billing_address);