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