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

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