Open-ILS/src/sql/Pg/upgrade/0797.schema.action.purge_holds.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0797', :eg_version); -- tsbere/Dyrcona/dbwells
   4
   5 -- New global flags for the purge function
   6 INSERT INTO config.global_flag  (name, label, enabled)
   7     VALUES (
   8         'history.hold.retention_age',
   9         oils_i18n_gettext('history.hold.retention_age', 'Historical Hold Retention Age', 'cgf', 'label'),
  10         TRUE
  11     ),(
  12         'history.hold.retention_age_fulfilled',
  13         oils_i18n_gettext('history.hold.retention_age_fulfilled', 'Historical Hold Retention Age - Fulfilled', 'cgf', 'label'),
  14         FALSE
  15     ),(
  16         'history.hold.retention_age_canceled',
  17         oils_i18n_gettext('history.hold.retention_age_canceled', 'Historical Hold Retention Age - Canceled (Default)', 'cgf', 'label'),
  18         FALSE
  19     ),(
  20         'history.hold.retention_age_canceled_1',
  21         oils_i18n_gettext('history.hold.retention_age_canceled_1', 'Historical Hold Retention Age - Canceled (Untarged expiration)', 'cgf', 'label'),
  22         FALSE
  23     ),(
  24         'history.hold.retention_age_canceled_2',
  25         oils_i18n_gettext('history.hold.retention_age_canceled_2', 'Historical Hold Retention Age - Canceled (Hold Shelf expiration)', 'cgf', 'label'),
  26         FALSE
  27     ),(
  28         'history.hold.retention_age_canceled_3',
  29         oils_i18n_gettext('history.hold.retention_age_canceled_3', 'Historical Hold Retention Age - Canceled (Patron via phone)', 'cgf', 'label'),
  30         TRUE
  31     ),(
  32         'history.hold.retention_age_canceled_4',
  33         oils_i18n_gettext('history.hold.retention_age_canceled_4', 'Historical Hold Retention Age - Canceled (Patron in person)', 'cgf', 'label'),
  34         TRUE
  35     ),(
  36         'history.hold.retention_age_canceled_5',
  37         oils_i18n_gettext('history.hold.retention_age_canceled_5', 'Historical Hold Retention Age - Canceled (Staff forced)', 'cgf', 'label'),
  38         TRUE
  39     ),(
  40         'history.hold.retention_age_canceled_6',
  41         oils_i18n_gettext('history.hold.retention_age_canceled_6', 'Historical Hold Retention Age - Canceled (Patron via OPAC)', 'cgf', 'label'),
  42         FALSE
  43     );
  44
  45 CREATE OR REPLACE FUNCTION action.purge_holds() RETURNS INT AS $func$
  46 DECLARE
  47   current_hold RECORD;
  48   purged_holds INT;
  49   cgf_d INTERVAL;
  50   cgf_f INTERVAL;
  51   cgf_c INTERVAL;
  52   prev_usr INT;
  53   user_start TIMESTAMPTZ;
  54   user_age INTERVAL;
  55   user_count INT;
  56 BEGIN
  57   purged_holds := 0;
  58   SELECT INTO cgf_d value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age' AND enabled;
  59   SELECT INTO cgf_f value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_fulfilled' AND enabled;
  60   SELECT INTO cgf_c value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_canceled' AND enabled;
  61   FOR current_hold IN
  62     SELECT
  63       rank() OVER (PARTITION BY usr ORDER BY COALESCE(fulfillment_time, cancel_time) DESC),
  64       cgf_cs.value::INTERVAL as cgf_cs,
  65       ahr.*
  66     FROM
  67       action.hold_request ahr
  68       LEFT JOIN config.global_flag cgf_cs ON (ahr.cancel_cause IS NOT NULL AND cgf_cs.name = 'history.hold.retention_age_canceled_' || ahr.cancel_cause AND cgf_cs.enabled)
  69     WHERE
  70       (fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL)
  71   LOOP
  72     IF prev_usr IS NULL OR prev_usr != current_hold.usr THEN
  73       prev_usr := current_hold.usr;
  74       SELECT INTO user_start oils_json_to_text(value)::TIMESTAMPTZ FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_start';
  75       SELECT INTO user_age oils_json_to_text(value)::INTERVAL FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_age';
  76       SELECT INTO user_count oils_json_to_text(value)::INT FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_count';
  77       IF user_start IS NOT NULL THEN
  78         user_age := LEAST(user_age, AGE(NOW(), user_start));
  79       END IF;
  80       IF user_count IS NULL THEN
  81         user_count := 1000; -- Assumption based on the user visible holds routine
  82       END IF;
  83     END IF;
  84     -- Library keep age trumps user keep anything, for purposes of being able to hold on to things when staff canceled and such.
  85     IF current_hold.fulfillment_time IS NOT NULL AND current_hold.fulfillment_time > NOW() - COALESCE(cgf_f, cgf_d) THEN
  86       CONTINUE;
  87     END IF;
  88     IF current_hold.cancel_time IS NOT NULL AND current_hold.cancel_time > NOW() - COALESCE(current_hold.cgf_cs, cgf_c, cgf_d) THEN
  89       CONTINUE;
  90     END IF;
  91
  92     -- User keep age needs combining with count. If too old AND within the count, keep!
  93     IF user_start IS NOT NULL AND COALESCE(current_hold.fulfillment_time, current_hold.cancel_time) > NOW() - user_age AND current_hold.rank <= user_count THEN
  94       CONTINUE;
  95     END IF;
  96
  97     -- All checks should have passed, delete!
  98     DELETE FROM action.hold_request WHERE id = current_hold.id;
  99     purged_holds := purged_holds + 1;
 100   END LOOP;
 101   RETURN purged_holds;
 102 END;
 103 $func$ LANGUAGE plpgsql;
 104
 105 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
 106 DECLARE
 107     h               action.hold_request%ROWTYPE;
 108     view_age        INTERVAL;
 109     view_count      INT;
 110     usr_view_count  actor.usr_setting%ROWTYPE;
 111     usr_view_age    actor.usr_setting%ROWTYPE;
 112     usr_view_start  actor.usr_setting%ROWTYPE;
 113 BEGIN
 114     SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
 115     SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
 116     SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
 117
 118     FOR h IN
 119         SELECT  *
 120           FROM  action.hold_request
 121           WHERE usr = usr_id
 122                 AND fulfillment_time IS NULL
 123                 AND cancel_time IS NULL
 124           ORDER BY request_time DESC
 125     LOOP
 126         RETURN NEXT h;
 127     END LOOP;
 128
 129     IF usr_view_start.value IS NULL THEN
 130         RETURN;
 131     END IF;
 132
 133     IF usr_view_age.value IS NOT NULL THEN
 134         -- User opted in and supplied a retention age
 135         IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
 136             view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
 137         ELSE
 138             view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
 139         END IF;
 140     ELSE
 141         -- User opted in
 142         view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
 143     END IF;
 144
 145     IF usr_view_count.value IS NOT NULL THEN
 146         view_count := oils_json_to_text(usr_view_count.value)::INT;
 147     ELSE
 148         view_count := 1000;
 149     END IF;
 150
 151     -- show some fulfilled/canceled holds
 152     FOR h IN
 153         SELECT  *
 154           FROM  action.hold_request
 155           WHERE usr = usr_id
 156                 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
 157                 AND COALESCE(fulfillment_time, cancel_time) > NOW() - view_age
 158           ORDER BY COALESCE(fulfillment_time, cancel_time) DESC
 159           LIMIT view_count
 160     LOOP
 161         RETURN NEXT h;
 162     END LOOP;
 163
 164     RETURN;
 165 END;
 166 $func$ LANGUAGE PLPGSQL;
 167
 168 CREATE TABLE action.aged_hold_request (
 169     usr_post_code               TEXT,
 170     usr_home_ou         INT     NOT NULL,
 171     usr_profile         INT     NOT NULL,
 172     usr_birth_year              INT,
 173     staff_placed        BOOLEAN NOT NULL,
 174     LIKE action.hold_request
 175 );
 176 ALTER TABLE action.aged_hold_request
 177       ADD PRIMARY KEY (id),
 178       DROP COLUMN usr,
 179       DROP COLUMN requestor,
 180       DROP COLUMN sms_carrier,
 181       ALTER COLUMN phone_notify TYPE BOOLEAN
 182             USING CASE WHEN phone_notify IS NULL OR phone_notify = '' THEN FALSE ELSE TRUE END,
 183       ALTER COLUMN sms_notify TYPE BOOLEAN
 184             USING CASE WHEN sms_notify IS NULL OR sms_notify = '' THEN FALSE ELSE TRUE END,
 185       ALTER COLUMN phone_notify SET NOT NULL,
 186       ALTER COLUMN sms_notify SET NOT NULL;
 187 CREATE INDEX aged_hold_request_target_idx ON action.aged_hold_request (target);
 188 CREATE INDEX aged_hold_request_pickup_lib_idx ON action.aged_hold_request (pickup_lib);
 189 CREATE INDEX aged_hold_request_current_copy_idx ON action.aged_hold_request (current_copy);
 190 CREATE INDEX aged_hold_request_fulfillment_staff_idx ON action.aged_hold_request ( fulfillment_staff );
 191
 192 CREATE OR REPLACE VIEW action.all_hold_request AS
 193     SELECT DISTINCT
 194            COALESCE(a.post_code, b.post_code) AS usr_post_code,
 195            p.home_ou AS usr_home_ou,
 196            p.profile AS usr_profile,
 197            EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
 198            CAST(ahr.requestor <> ahr.usr AS BOOLEAN) AS staff_placed,
 199            ahr.id,
 200            ahr.request_time,
 201            ahr.capture_time,
 202            ahr.fulfillment_time,
 203            ahr.checkin_time,
 204            ahr.return_time,
 205            ahr.prev_check_time,
 206            ahr.expire_time,
 207            ahr.cancel_time,
 208            ahr.cancel_cause,
 209            ahr.cancel_note,
 210            ahr.target,
 211            ahr.current_copy,
 212            ahr.fulfillment_staff,
 213            ahr.fulfillment_lib,
 214            ahr.request_lib,
 215            ahr.selection_ou,
 216            ahr.selection_depth,
 217            ahr.pickup_lib,
 218            ahr.hold_type,
 219            ahr.holdable_formats,
 220            CASE
 221            WHEN ahr.phone_notify IS NULL THEN FALSE
 222            WHEN ahr.phone_notify = '' THEN FALSE
 223            ELSE TRUE
 224            END AS phone_notify,
 225            ahr.email_notify,
 226            CASE
 227            WHEN ahr.sms_notify IS NULL THEN FALSE
 228            WHEN ahr.sms_notify = '' THEN FALSE
 229            ELSE TRUE
 230            END AS sms_notify,
 231            ahr.frozen,
 232            ahr.thaw_date,
 233            ahr.shelf_time,
 234            ahr.cut_in_line,
 235            ahr.mint_condition,
 236            ahr.shelf_expire_time,
 237            ahr.current_shelf_lib
 238     FROM action.hold_request ahr
 239          JOIN actor.usr p ON (ahr.usr = p.id)
 240          LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
 241          LEFT JOIN actor.usr_address b ON (p.billing_address = b.id)
 242     UNION ALL
 243     SELECT
 244            usr_post_code,
 245            usr_home_ou,
 246            usr_profile,
 247            usr_birth_year,
 248            staff_placed,
 249            id,
 250            request_time,
 251            capture_time,
 252            fulfillment_time,
 253            checkin_time,
 254            return_time,
 255            prev_check_time,
 256            expire_time,
 257            cancel_time,
 258            cancel_cause,
 259            cancel_note,
 260            target,
 261            current_copy,
 262            fulfillment_staff,
 263            fulfillment_lib,
 264            request_lib,
 265            selection_ou,
 266            selection_depth,
 267            pickup_lib,
 268            hold_type,
 269            holdable_formats,
 270            phone_notify,
 271            email_notify,
 272            sms_notify,
 273            frozen,
 274            thaw_date,
 275            shelf_time,
 276            cut_in_line,
 277            mint_condition,
 278            shelf_expire_time,
 279            current_shelf_lib
 280     FROM action.aged_hold_request;
 281
 282 CREATE OR REPLACE FUNCTION action.age_hold_on_delete () RETURNS TRIGGER AS $$
 283 DECLARE
 284 BEGIN
 285     -- Archive a copy of the old row to action.aged_hold_request
 286
 287     INSERT INTO action.aged_hold_request
 288            (usr_post_code,
 289             usr_home_ou,
 290             usr_profile,
 291             usr_birth_year,
 292             staff_placed,
 293             id,
 294             request_time,
 295             capture_time,
 296             fulfillment_time,
 297             checkin_time,
 298             return_time,
 299             prev_check_time,
 300             expire_time,
 301             cancel_time,
 302             cancel_cause,
 303             cancel_note,
 304             target,
 305             current_copy,
 306             fulfillment_staff,
 307             fulfillment_lib,
 308             request_lib,
 309             selection_ou,
 310             selection_depth,
 311             pickup_lib,
 312             hold_type,
 313             holdable_formats,
 314             phone_notify,
 315             email_notify,
 316             sms_notify,
 317             frozen,
 318             thaw_date,
 319             shelf_time,
 320             cut_in_line,
 321             mint_condition,
 322             shelf_expire_time,
 323             current_shelf_lib)
 324       SELECT
 325            usr_post_code,
 326            usr_home_ou,
 327            usr_profile,
 328            usr_birth_year,
 329            staff_placed,
 330            id,
 331            request_time,
 332            capture_time,
 333            fulfillment_time,
 334            checkin_time,
 335            return_time,
 336            prev_check_time,
 337            expire_time,
 338            cancel_time,
 339            cancel_cause,
 340            cancel_note,
 341            target,
 342            current_copy,
 343            fulfillment_staff,
 344            fulfillment_lib,
 345            request_lib,
 346            selection_ou,
 347            selection_depth,
 348            pickup_lib,
 349            hold_type,
 350            holdable_formats,
 351            phone_notify,
 352            email_notify,
 353            sms_notify,
 354            frozen,
 355            thaw_date,
 356            shelf_time,
 357            cut_in_line,
 358            mint_condition,
 359            shelf_expire_time,
 360            current_shelf_lib
 361         FROM action.all_hold_request WHERE id = OLD.id;
 362
 363     RETURN OLD;
 364 END;
 365 $$ LANGUAGE 'plpgsql';
 366
 367 CREATE TRIGGER action_hold_request_aging_tgr
 368         BEFORE DELETE ON action.hold_request
 369         FOR EACH ROW
 370         EXECUTE PROCEDURE action.age_hold_on_delete ();
 371
 372 COMMIT;