Open-ILS/src/sql/Pg/upgrade/0945.schema.dob-as-date.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('0945', :eg_version);
   4
   5 -- run the entire update inside a DO block for managing the logic
   6 -- of whether to recreate the optional reporter views
   7 DO $$
   8 DECLARE
   9     has_current_circ BOOLEAN;
  10     has_billing_summary BOOLEAN;
  11 BEGIN
  12
  13 SELECT INTO has_current_circ TRUE FROM pg_views
  14     WHERE schemaname = 'reporter' AND viewname = 'classic_current_circ';
  15
  16 SELECT INTO has_billing_summary TRUE FROM pg_views
  17     WHERE schemaname = 'reporter' AND
  18     viewname = 'classic_current_billing_summary';
  19
  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;
  26
  27 ALTER TABLE actor.usr
  28     ALTER dob TYPE DATE USING (dob + '3 hours'::INTERVAL)::DATE;
  29
  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;
  34
  35 -- this recreates auditor.actor_usr_lifecycle
  36 PERFORM auditor.update_auditors();
  37
  38 CREATE VIEW reporter.demographic AS
  39     SELECT u.id, u.dob,
  40         CASE
  41             WHEN u.dob IS NULL THEN 'Adult'::text
  42             WHEN age(u.dob) > '18 years'::interval THEN 'Adult'::text
  43             ELSE 'Juvenile'::text
  44         END AS general_division
  45     FROM actor.usr u;
  46
  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
  69 UNION ALL
  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;
  92
  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,
 104                 CASE
 105                     WHEN ahr.phone_notify IS NULL THEN false
 106                     WHEN ahr.phone_notify = ''::text THEN false
 107                     ELSE true
 108                 END AS phone_notify,
 109             ahr.email_notify,
 110                 CASE
 111                     WHEN ahr.sms_notify IS NULL THEN false
 112                     WHEN ahr.sms_notify = ''::text THEN false
 113                     ELSE true
 114                 END AS sms_notify,
 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,
 117             ahr.behind_desk
 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
 122 UNION ALL
 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;
 144
 145 IF has_current_circ THEN
 146 RAISE NOTICE 'Recreating optional view reporter.classic_current_circ';
 147
 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,
 153         cp.id AS copy_id,
 154         cp.circ_modifier,
 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,
 161         p.id AS patron_id,
 162         g.name AS profile_group,
 163         dem.general_division AS demographic_general_division,
 164         circ.id AS id,
 165         cn.id AS call_number,
 166         cn.label AS call_number_label,
 167         call_number_dewey(cn.label) AS dewey,
 168         CASE
 169                 WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
 170                         THEN
 171                                 btrim(
 172                                         to_char(
 173                                                 10 * floor((call_number_dewey(cn.label)::float) / 10), '000'
 174                                         )
 175                                 )
 176                 ELSE NULL
 177         END AS dewey_block_tens,
 178         CASE
 179                 WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
 180                         THEN
 181                                 btrim(
 182                                         to_char(
 183                                                 100 * floor((call_number_dewey(cn.label)::float) / 100), '000'
 184                                         )
 185                                 )
 186                 ELSE NULL
 187         END AS dewey_block_hundreds,
 188         CASE
 189                 WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
 190                         THEN
 191                                 btrim(
 192                                         to_char(
 193                                                 10 * floor((call_number_dewey(cn.label)::float) / 10), '000'
 194                                         )
 195                                 )
 196                                 || '-' ||
 197                                 btrim(
 198                                         to_char(
 199                                                 10 * floor((call_number_dewey(cn.label)::float) / 10) + 9, '000'
 200                                         )
 201                                 )
 202                 ELSE NULL
 203         END AS dewey_range_tens,
 204         CASE
 205                 WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
 206                         THEN
 207                                 btrim(
 208                                         to_char(
 209                                                 100 * floor((call_number_dewey(cn.label)::float) / 100), '000'
 210                                         )
 211                                 )
 212                                 || '-' ||
 213                                 btrim(
 214                                         to_char(
 215                                                 100 * floor((call_number_dewey(cn.label)::float) / 100) + 99, '000'
 216                                         )
 217                                 )
 218                 ELSE NULL
 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);
 250 END IF;
 251
 252 IF has_billing_summary THEN
 253 RAISE NOTICE 'Recreating optional view reporter.classic_current_billing_summary';
 254
 255 CREATE OR REPLACE VIEW reporter.classic_current_billing_summary AS
 256 SELECT  x.id AS id,
 257         x.usr AS usr,
 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);
 290 END IF;
 291
 292 END $$;
 293
 294 COMMIT;