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