Open-ILS/src/sql/Pg/090.schema.action.sql

   1 /*
   2  * Copyright (C) 2004-2008  Georgia Public Library Service
   3  * Copyright (C) 2007-2008  Equinox Software, Inc.
   4  * Mike Rylander <miker@esilibrary.com>
   5  *
   6  * This program is free software; you can redistribute it and/or
   7  * modify it under the terms of the GNU General Public License
   8  * as published by the Free Software Foundation; either version 2
   9  * of the License, or (at your option) any later version.
  10  *
  11  * This program is distributed in the hope that it will be useful,
  12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  14  * GNU General Public License for more details.
  15  *
  16  */
  17
  18 DROP SCHEMA IF EXISTS action CASCADE;
  19
  20 BEGIN;
  21
  22 CREATE SCHEMA action;
  23
  24 CREATE TABLE action.in_house_use (
  25         id              SERIAL                          PRIMARY KEY,
  26         item            BIGINT                          NOT NULL, -- REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
  27         staff           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  28         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  29         use_time        TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
  30 );
  31 CREATE INDEX action_in_house_use_staff_idx      ON action.in_house_use ( staff );
  32
  33 CREATE TABLE action.non_cataloged_circulation (
  34         id              SERIAL                          PRIMARY KEY,
  35         patron          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  36         staff           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  37         circ_lib        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  38         item_type       INT                             NOT NULL REFERENCES config.non_cataloged_type (id) DEFERRABLE INITIALLY DEFERRED,
  39         circ_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
  40 );
  41 CREATE INDEX action_non_cat_circ_patron_idx ON action.non_cataloged_circulation ( patron );
  42 CREATE INDEX action_non_cat_circ_staff_idx  ON action.non_cataloged_circulation ( staff );
  43
  44 CREATE TABLE action.non_cat_in_house_use (
  45         id              SERIAL                          PRIMARY KEY,
  46         item_type       BIGINT                          NOT NULL REFERENCES config.non_cataloged_type(id) DEFERRABLE INITIALLY DEFERRED,
  47         staff           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  48         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  49         use_time        TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
  50 );
  51 CREATE INDEX non_cat_in_house_use_staff_idx ON action.non_cat_in_house_use ( staff );
  52
  53 CREATE TABLE action.survey (
  54         id              SERIAL                          PRIMARY KEY,
  55         owner           INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  56         start_date      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  57         end_date        TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW() + '10 years'::INTERVAL,
  58         usr_summary     BOOL                            NOT NULL DEFAULT FALSE,
  59         opac            BOOL                            NOT NULL DEFAULT FALSE,
  60         poll            BOOL                            NOT NULL DEFAULT FALSE,
  61         required        BOOL                            NOT NULL DEFAULT FALSE,
  62         name            TEXT                            NOT NULL,
  63         description     TEXT                            NOT NULL
  64 );
  65 CREATE UNIQUE INDEX asv_once_per_owner_idx ON action.survey (owner,name);
  66
  67 CREATE TABLE action.survey_question (
  68         id              SERIAL  PRIMARY KEY,
  69         survey          INT     NOT NULL REFERENCES action.survey DEFERRABLE INITIALLY DEFERRED,
  70         question        TEXT    NOT NULL
  71 );
  72
  73 CREATE TABLE action.survey_answer (
  74         id              SERIAL  PRIMARY KEY,
  75         question        INT     NOT NULL REFERENCES action.survey_question DEFERRABLE INITIALLY DEFERRED,
  76         answer          TEXT    NOT NULL
  77 );
  78
  79 CREATE SEQUENCE action.survey_response_group_id_seq;
  80
  81 CREATE TABLE action.survey_response (
  82         id                      BIGSERIAL                       PRIMARY KEY,
  83         response_group_id       INT,
  84         usr                     INT, -- REFERENCES actor.usr
  85         survey                  INT                             NOT NULL REFERENCES action.survey DEFERRABLE INITIALLY DEFERRED,
  86         question                INT                             NOT NULL REFERENCES action.survey_question DEFERRABLE INITIALLY DEFERRED,
  87         answer                  INT                             NOT NULL REFERENCES action.survey_answer DEFERRABLE INITIALLY DEFERRED,
  88         answer_date             TIMESTAMP WITH TIME ZONE,
  89         effective_date          TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
  90 );
  91 CREATE INDEX action_survey_response_usr_idx ON action.survey_response ( usr );
  92
  93 CREATE OR REPLACE FUNCTION action.survey_response_answer_date_fixup () RETURNS TRIGGER AS '
  94 BEGIN
  95         NEW.answer_date := NOW();
  96         RETURN NEW;
  97 END;
  98 ' LANGUAGE 'plpgsql';
  99 CREATE TRIGGER action_survey_response_answer_date_fixup_tgr
 100         BEFORE INSERT ON action.survey_response
 101         FOR EACH ROW
 102         EXECUTE PROCEDURE action.survey_response_answer_date_fixup ();
 103
 104 CREATE TABLE action.archive_actor_stat_cat (
 105     id          BIGSERIAL   PRIMARY KEY,
 106     xact        BIGINT      NOT NULL, -- action.circulation (+aged/all)
 107     stat_cat    INT         NOT NULL,
 108     value       TEXT        NOT NULL
 109 );
 110
 111 CREATE TABLE action.archive_asset_stat_cat (
 112     id          BIGSERIAL   PRIMARY KEY,
 113     xact        BIGINT      NOT NULL, -- action.circulation (+aged/all)
 114     stat_cat    INT         NOT NULL,
 115     value       TEXT        NOT NULL
 116 );
 117
 118
 119 CREATE TABLE action.circulation (
 120         target_copy             BIGINT                          NOT NULL, -- asset.copy.id
 121         circ_lib                INT                             NOT NULL, -- actor.org_unit.id
 122         circ_staff              INT                             NOT NULL, -- actor.usr.id
 123         checkin_staff           INT,                                      -- actor.usr.id
 124         checkin_lib             INT,                                      -- actor.org_unit.id
 125         renewal_remaining       INT                             NOT NULL, -- derived from "circ duration" rule
 126     grace_period           INTERVAL             NOT NULL, -- derived from "circ fine" rule
 127         due_date                TIMESTAMP WITH TIME ZONE,
 128         stop_fines_time         TIMESTAMP WITH TIME ZONE,
 129         checkin_time            TIMESTAMP WITH TIME ZONE,
 130         create_time             TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
 131         duration                INTERVAL,                                 -- derived from "circ duration" rule
 132         fine_interval           INTERVAL                        NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule
 133         recurring_fine          NUMERIC(6,2),                             -- derived from "circ fine" rule
 134         max_fine                NUMERIC(6,2),                             -- derived from "max fine" rule
 135         phone_renewal           BOOL                            NOT NULL DEFAULT FALSE,
 136         desk_renewal            BOOL                            NOT NULL DEFAULT FALSE,
 137         opac_renewal            BOOL                            NOT NULL DEFAULT FALSE,
 138         duration_rule           TEXT                            NOT NULL, -- name of "circ duration" rule
 139         recurring_fine_rule     TEXT                            NOT NULL, -- name of "circ fine" rule
 140         max_fine_rule           TEXT                            NOT NULL, -- name of "max fine" rule
 141         stop_fines              TEXT                            CHECK (stop_fines IN (
 142                                                'CHECKIN','CLAIMSRETURNED','LOST','MAXFINES','RENEW','LONGOVERDUE','CLAIMSNEVERCHECKEDOUT')),
 143         workstation         INT        REFERENCES actor.workstation(id)
 144                                        ON DELETE SET NULL
 145                                                                    DEFERRABLE INITIALLY DEFERRED,
 146         checkin_workstation INT        REFERENCES actor.workstation(id)
 147                                        ON DELETE SET NULL
 148                                                                    DEFERRABLE INITIALLY DEFERRED,
 149         copy_location   INT                             NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
 150         checkin_scan_time   TIMESTAMP WITH TIME ZONE
 151 ) INHERITS (money.billable_xact);
 152 ALTER TABLE action.circulation ADD PRIMARY KEY (id);
 153 ALTER TABLE action.circulation
 154         ADD COLUMN parent_circ BIGINT
 155         REFERENCES action.circulation( id )
 156         DEFERRABLE INITIALLY DEFERRED;
 157 CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL;
 158 CREATE INDEX circ_outstanding_idx ON action.circulation (usr) WHERE checkin_time IS NULL;
 159 CREATE INDEX circ_checkin_time ON "action".circulation (checkin_time) WHERE checkin_time IS NOT NULL;
 160 CREATE INDEX circ_circ_lib_idx ON "action".circulation (circ_lib);
 161 CREATE INDEX circ_open_date_idx ON "action".circulation (xact_start) WHERE xact_finish IS NULL;
 162 CREATE INDEX circ_all_usr_idx       ON action.circulation ( usr );
 163 CREATE INDEX circ_circ_staff_idx    ON action.circulation ( circ_staff );
 164 CREATE INDEX circ_checkin_staff_idx ON action.circulation ( checkin_staff );
 165 CREATE INDEX action_circulation_target_copy_idx ON action.circulation (target_copy);
 166 CREATE UNIQUE INDEX circ_parent_idx ON action.circulation ( parent_circ ) WHERE parent_circ IS NOT NULL;
 167 CREATE UNIQUE INDEX only_one_concurrent_checkout_per_copy ON action.circulation(target_copy) WHERE checkin_time IS NULL;
 168
 169 CREATE TRIGGER action_circulation_target_copy_trig AFTER INSERT OR UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy');
 170
 171 CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('circulation');
 172 CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update ();
 173 CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete ();
 174
 175 CREATE OR REPLACE FUNCTION action.push_circ_due_time () RETURNS TRIGGER AS $$
 176 BEGIN
 177     IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 THEN
 178         NEW.due_date = (NEW.due_date::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL)::TIMESTAMPTZ;
 179     END IF;
 180
 181     RETURN NEW;
 182 END;
 183 $$ LANGUAGE PLPGSQL;
 184
 185 CREATE TRIGGER push_due_date_tgr BEFORE INSERT OR UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.push_circ_due_time();
 186
 187 CREATE OR REPLACE FUNCTION action.fill_circ_copy_location () RETURNS TRIGGER AS $$
 188 BEGIN
 189     SELECT INTO NEW.copy_location location FROM asset.copy WHERE id = NEW.target_copy;
 190     RETURN NEW;
 191 END;
 192 $$ LANGUAGE PLPGSQL;
 193
 194 CREATE TRIGGER fill_circ_copy_location_tgr BEFORE INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.fill_circ_copy_location();
 195
 196 CREATE OR REPLACE FUNCTION action.archive_stat_cats () RETURNS TRIGGER AS $$
 197 BEGIN
 198     INSERT INTO action.archive_actor_stat_cat(xact, stat_cat, value)
 199         SELECT NEW.id, asceum.stat_cat, asceum.stat_cat_entry
 200         FROM actor.stat_cat_entry_usr_map asceum
 201              JOIN actor.stat_cat sc ON asceum.stat_cat = sc.id
 202         WHERE NEW.usr = asceum.target_usr AND sc.checkout_archive;
 203     INSERT INTO action.archive_asset_stat_cat(xact, stat_cat, value)
 204         SELECT NEW.id, ascecm.stat_cat, asce.value
 205         FROM asset.stat_cat_entry_copy_map ascecm
 206              JOIN asset.stat_cat sc ON ascecm.stat_cat = sc.id
 207              JOIN asset.stat_cat_entry asce ON ascecm.stat_cat_entry = asce.id
 208         WHERE NEW.target_copy = ascecm.owning_copy AND sc.checkout_archive;
 209     RETURN NULL;
 210 END;
 211 $$ LANGUAGE PLPGSQL;
 212
 213 CREATE TRIGGER archive_stat_cats_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.archive_stat_cats();
 214
 215 CREATE TABLE action.aged_circulation (
 216         usr_post_code           TEXT,
 217         usr_home_ou             INT     NOT NULL,
 218         usr_profile             INT     NOT NULL,
 219         usr_birth_year          INT,
 220         copy_call_number        INT     NOT NULL,
 221         copy_owning_lib         INT     NOT NULL,
 222         copy_circ_lib           INT     NOT NULL,
 223         copy_bib_record         BIGINT  NOT NULL,
 224         LIKE action.circulation
 225
 226 );
 227 ALTER TABLE action.aged_circulation ADD PRIMARY KEY (id);
 228 ALTER TABLE action.aged_circulation DROP COLUMN usr;
 229 CREATE INDEX aged_circ_circ_lib_idx ON "action".aged_circulation (circ_lib);
 230 CREATE INDEX aged_circ_start_idx ON "action".aged_circulation (xact_start);
 231 CREATE INDEX aged_circ_copy_circ_lib_idx ON "action".aged_circulation (copy_circ_lib);
 232 CREATE INDEX aged_circ_copy_owning_lib_idx ON "action".aged_circulation (copy_owning_lib);
 233 CREATE INDEX aged_circ_copy_location_idx ON "action".aged_circulation (copy_location);
 234 CREATE INDEX action_aged_circulation_target_copy_idx ON action.aged_circulation (target_copy);
 235
 236 CREATE OR REPLACE VIEW action.all_circulation AS
 237     SELECT  id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
 238         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
 239         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
 240         stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
 241         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
 242         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
 243       FROM  action.aged_circulation
 244             UNION ALL
 245     SELECT  DISTINCT circ.id,COALESCE(a.post_code,b.post_code) AS usr_post_code, p.home_ou AS usr_home_ou, p.profile AS usr_profile, EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
 246         cp.call_number AS copy_call_number, circ.copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
 247         cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
 248         circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
 249         circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
 250         circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
 251         circ.parent_circ
 252       FROM  action.circulation circ
 253         JOIN asset.copy cp ON (circ.target_copy = cp.id)
 254         JOIN asset.call_number cn ON (cp.call_number = cn.id)
 255         JOIN actor.usr p ON (circ.usr = p.id)
 256         LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
 257         LEFT JOIN actor.usr_address b ON (p.billing_address = b.id);
 258
 259 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
 260 DECLARE
 261 found char := 'N';
 262 BEGIN
 263
 264     -- If there are any renewals for this circulation, don't archive or delete
 265     -- it yet.   We'll do so later, when we archive and delete the renewals.
 266
 267     SELECT 'Y' INTO found
 268     FROM action.circulation
 269     WHERE parent_circ = OLD.id
 270     LIMIT 1;
 271
 272     IF found = 'Y' THEN
 273         RETURN NULL;  -- don't delete
 274         END IF;
 275
 276     -- Archive a copy of the old row to action.aged_circulation
 277
 278     INSERT INTO action.aged_circulation
 279         (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
 280         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
 281         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
 282         stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
 283         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
 284         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ)
 285       SELECT
 286         id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
 287         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
 288         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
 289         stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
 290         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
 291         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
 292         FROM action.all_circulation WHERE id = OLD.id;
 293
 294     RETURN OLD;
 295 END;
 296 $$ LANGUAGE 'plpgsql';
 297
 298 CREATE TRIGGER action_circulation_aging_tgr
 299         BEFORE DELETE ON action.circulation
 300         FOR EACH ROW
 301         EXECUTE PROCEDURE action.age_circ_on_delete ();
 302
 303
 304 CREATE OR REPLACE FUNCTION action.age_parent_circ_on_delete () RETURNS TRIGGER AS $$
 305 BEGIN
 306
 307     -- Having deleted a renewal, we can delete the original circulation (or a previous
 308     -- renewal, if that's what parent_circ is pointing to).  That deletion will trigger
 309     -- deletion of any prior parents, etc. recursively.
 310
 311     IF OLD.parent_circ IS NOT NULL THEN
 312         DELETE FROM action.circulation
 313         WHERE id = OLD.parent_circ;
 314     END IF;
 315
 316     RETURN OLD;
 317 END;
 318 $$ LANGUAGE 'plpgsql';
 319
 320 CREATE TRIGGER age_parent_circ
 321         AFTER DELETE ON action.circulation
 322         FOR EACH ROW
 323         EXECUTE PROCEDURE action.age_parent_circ_on_delete ();
 324
 325
 326 CREATE OR REPLACE VIEW action.open_circulation AS
 327         SELECT  *
 328           FROM  action.circulation
 329           WHERE checkin_time IS NULL
 330           ORDER BY due_date;
 331
 332
 333 CREATE OR REPLACE VIEW action.billable_circulations AS
 334         SELECT  *
 335           FROM  action.circulation
 336           WHERE xact_finish IS NULL;
 337
 338 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
 339 BEGIN
 340         IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
 341                 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
 342                         UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
 343                 END IF;
 344                 IF NEW.stop_fines = 'CLAIMSNEVERCHECKEDOUT' THEN
 345                         UPDATE actor.usr SET claims_never_checked_out_count = claims_never_checked_out_count + 1 WHERE id = NEW.usr;
 346                 END IF;
 347                 IF NEW.stop_fines = 'LOST' THEN
 348                         UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
 349                 END IF;
 350         END IF;
 351         RETURN NEW;
 352 END;
 353 $$ LANGUAGE 'plpgsql';
 354 CREATE TRIGGER action_circulation_stop_fines_tgr
 355         BEFORE UPDATE ON action.circulation
 356         FOR EACH ROW
 357         EXECUTE PROCEDURE action.circulation_claims_returned ();
 358
 359 CREATE TABLE action.hold_request_cancel_cause (
 360     id      SERIAL  PRIMARY KEY,
 361     label   TEXT    UNIQUE
 362 );
 363 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (1,'Untargeted expiration');
 364 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (2,'Hold Shelf expiration');
 365 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (3,'Patron via phone');
 366 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (4,'Patron in person');
 367 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (5,'Staff forced');
 368 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (6,'Patron via OPAC');
 369 SELECT SETVAL('action.hold_request_cancel_cause_id_seq', 100);
 370
 371 CREATE TABLE action.hold_request (
 372         id                      SERIAL                          PRIMARY KEY,
 373         request_time            TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 374         capture_time            TIMESTAMP WITH TIME ZONE,
 375         fulfillment_time        TIMESTAMP WITH TIME ZONE,
 376         checkin_time            TIMESTAMP WITH TIME ZONE,
 377         return_time             TIMESTAMP WITH TIME ZONE,
 378         prev_check_time         TIMESTAMP WITH TIME ZONE,
 379         expire_time             TIMESTAMP WITH TIME ZONE,
 380         cancel_time             TIMESTAMP WITH TIME ZONE,
 381         cancel_cause    INT REFERENCES action.hold_request_cancel_cause (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 382         cancel_note             TEXT,
 383         target                  BIGINT                          NOT NULL, -- see hold_type
 384         current_copy            BIGINT,                         -- REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,  -- XXX could be an serial.unit now...
 385         fulfillment_staff       INT                             REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 386         fulfillment_lib         INT                             REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 387         request_lib             INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 388         requestor               INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 389         usr                     INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 390         selection_ou            INT                             NOT NULL,
 391         selection_depth         INT                             NOT NULL DEFAULT 0,
 392         pickup_lib              INT                             NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
 393         hold_type               TEXT                            NOT NULL, -- CHECK (hold_type IN ('M','T','V','C')),  -- XXX constraint too constraining...
 394         holdable_formats        TEXT,
 395         phone_notify            TEXT,
 396         email_notify            BOOL                            NOT NULL DEFAULT FALSE,
 397         sms_notify              TEXT,
 398         sms_carrier             INT REFERENCES config.sms_carrier (id),
 399         frozen                  BOOL                            NOT NULL DEFAULT FALSE,
 400         thaw_date               TIMESTAMP WITH TIME ZONE,
 401         shelf_time              TIMESTAMP WITH TIME ZONE,
 402     cut_in_line     BOOL,
 403         mint_condition  BOOL NOT NULL DEFAULT TRUE,
 404         shelf_expire_time TIMESTAMPTZ,
 405         current_shelf_lib INT REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED
 406 );
 407 ALTER TABLE action.hold_request ADD CONSTRAINT sms_check CHECK (
 408     sms_notify IS NULL
 409     OR sms_carrier IS NOT NULL -- and implied sms_notify IS NOT NULL
 410 );
 411
 412
 413 CREATE INDEX hold_request_target_idx ON action.hold_request (target);
 414 CREATE INDEX hold_request_usr_idx ON action.hold_request (usr);
 415 CREATE INDEX hold_request_pickup_lib_idx ON action.hold_request (pickup_lib);
 416 CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy);
 417 CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time);
 418 CREATE INDEX hold_request_fulfillment_staff_idx ON action.hold_request ( fulfillment_staff );
 419 CREATE INDEX hold_request_requestor_idx         ON action.hold_request ( requestor );
 420 CREATE INDEX hold_request_open_idx ON action.hold_request (id) WHERE cancel_time IS NULL AND fulfillment_time IS NULL;
 421 CREATE INDEX hold_request_current_copy_before_cap_idx ON action.hold_request (current_copy) WHERE capture_time IS NULL AND cancel_time IS NULL;
 422
 423
 424 CREATE TABLE action.hold_request_note (
 425
 426     id     BIGSERIAL PRIMARY KEY,
 427     hold   BIGINT    NOT NULL REFERENCES action.hold_request (id)
 428                               ON DELETE CASCADE
 429                               DEFERRABLE INITIALLY DEFERRED,
 430     title  TEXT      NOT NULL,
 431     body   TEXT      NOT NULL,
 432     slip   BOOL      NOT NULL DEFAULT FALSE,
 433     pub    BOOL      NOT NULL DEFAULT FALSE,
 434     staff  BOOL      NOT NULL DEFAULT FALSE  -- created by staff
 435
 436 );
 437 CREATE INDEX ahrn_hold_idx ON action.hold_request_note (hold);
 438
 439
 440 CREATE TABLE action.hold_notification (
 441         id              SERIAL                          PRIMARY KEY,
 442         hold            INT                             NOT NULL REFERENCES action.hold_request (id)
 443                                                                         ON DELETE CASCADE
 444                                                                         DEFERRABLE INITIALLY DEFERRED,
 445         notify_staff    INT                     REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 446         notify_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 447         method          TEXT                            NOT NULL, -- email address or phone number
 448         note            TEXT
 449 );
 450 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
 451 CREATE INDEX ahn_notify_staff_idx ON action.hold_notification ( notify_staff );
 452
 453 CREATE TABLE action.hold_copy_map (
 454         id              BIGSERIAL       PRIMARY KEY,
 455         hold            INT     NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 456         target_copy     BIGINT  NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
 457         proximity       NUMERIC,
 458         CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
 459 );
 460 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
 461 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
 462
 463 CREATE TABLE action.transit_copy (
 464         id                      SERIAL                          PRIMARY KEY,
 465         source_send_time        TIMESTAMP WITH TIME ZONE,
 466         dest_recv_time          TIMESTAMP WITH TIME ZONE,
 467         target_copy             BIGINT                          NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
 468         source                  INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 469         dest                    INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 470         prev_hop                INT                             REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED,
 471         copy_status             INT                             NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
 472         persistant_transfer     BOOL                            NOT NULL DEFAULT FALSE,
 473         prev_dest       INT                             REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
 474 );
 475 CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest);
 476 CREATE INDEX active_transit_source_idx ON "action".transit_copy (source);
 477 CREATE INDEX active_transit_cp_idx ON "action".transit_copy (target_copy);
 478
 479
 480 CREATE TABLE action.hold_transit_copy (
 481         hold    INT     REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
 482 ) INHERITS (action.transit_copy);
 483 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id);
 484 -- ALTER TABLE action.hold_transit_copy ADD CONSTRAINT ahtc_tc_fkey FOREIGN KEY (target_copy) REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; -- XXX could be an serial.issuance
 485 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest);
 486 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
 487 CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy);
 488 CREATE INDEX hold_transit_copy_hold_idx on action.hold_transit_copy (hold);
 489
 490
 491 CREATE TABLE action.unfulfilled_hold_list (
 492         id              BIGSERIAL                       PRIMARY KEY,
 493         current_copy    BIGINT                          NOT NULL,
 494         hold            INT                             NOT NULL,
 495         circ_lib        INT                             NOT NULL,
 496         fail_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
 497 );
 498 CREATE INDEX uhr_hold_idx ON action.unfulfilled_hold_list (hold);
 499
 500 CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS
 501     SELECT  u.hold,
 502             c.circ_lib,
 503             count(*)
 504       FROM  action.unfulfilled_hold_list u
 505             JOIN asset.copy c ON (c.id = u.current_copy)
 506       GROUP BY 1,2;
 507
 508 CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS
 509     SELECT  hold,
 510             min(count)
 511       FROM  action.unfulfilled_hold_loops
 512       GROUP BY 1;
 513
 514 CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS
 515     SELECT  DISTINCT l.*
 516       FROM  action.unfulfilled_hold_loops l
 517             JOIN action.unfulfilled_hold_min_loop m USING (hold)
 518       WHERE l.count = m.min;
 519
 520 CREATE VIEW action.unfulfilled_hold_max_loop AS
 521     SELECT  hold,
 522             max(count) AS max
 523       FROM  action.unfulfilled_hold_loops
 524       GROUP BY 1;
 525
 526
 527 CREATE TABLE action.aged_hold_request (
 528     usr_post_code               TEXT,
 529     usr_home_ou         INT     NOT NULL,
 530     usr_profile         INT     NOT NULL,
 531     usr_birth_year              INT,
 532     staff_placed        BOOLEAN NOT NULL,
 533     LIKE action.hold_request
 534 );
 535 ALTER TABLE action.aged_hold_request
 536       ADD PRIMARY KEY (id),
 537       DROP COLUMN usr,
 538       DROP COLUMN requestor,
 539       DROP COLUMN sms_carrier,
 540       ALTER COLUMN phone_notify TYPE BOOLEAN
 541             USING CASE WHEN phone_notify IS NULL OR phone_notify = '' THEN FALSE ELSE TRUE END,
 542       ALTER COLUMN sms_notify TYPE BOOLEAN
 543             USING CASE WHEN sms_notify IS NULL OR sms_notify = '' THEN FALSE ELSE TRUE END,
 544       ALTER COLUMN phone_notify SET NOT NULL,
 545       ALTER COLUMN sms_notify SET NOT NULL;
 546 CREATE INDEX aged_hold_request_target_idx ON action.aged_hold_request (target);
 547 CREATE INDEX aged_hold_request_pickup_lib_idx ON action.aged_hold_request (pickup_lib);
 548 CREATE INDEX aged_hold_request_current_copy_idx ON action.aged_hold_request (current_copy);
 549 CREATE INDEX aged_hold_request_fulfillment_staff_idx ON action.aged_hold_request ( fulfillment_staff );
 550
 551 CREATE OR REPLACE VIEW action.all_hold_request AS
 552     SELECT DISTINCT
 553            COALESCE(a.post_code, b.post_code) AS usr_post_code,
 554            p.home_ou AS usr_home_ou,
 555            p.profile AS usr_profile,
 556            EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
 557            CAST(ahr.requestor <> ahr.usr AS BOOLEAN) AS staff_placed,
 558            ahr.id,
 559            ahr.request_time,
 560            ahr.capture_time,
 561            ahr.fulfillment_time,
 562            ahr.checkin_time,
 563            ahr.return_time,
 564            ahr.prev_check_time,
 565            ahr.expire_time,
 566            ahr.cancel_time,
 567            ahr.cancel_cause,
 568            ahr.cancel_note,
 569            ahr.target,
 570            ahr.current_copy,
 571            ahr.fulfillment_staff,
 572            ahr.fulfillment_lib,
 573            ahr.request_lib,
 574            ahr.selection_ou,
 575            ahr.selection_depth,
 576            ahr.pickup_lib,
 577            ahr.hold_type,
 578            ahr.holdable_formats,
 579            CASE
 580            WHEN ahr.phone_notify IS NULL THEN FALSE
 581            WHEN ahr.phone_notify = '' THEN FALSE
 582            ELSE TRUE
 583            END AS phone_notify,
 584            ahr.email_notify,
 585            CASE
 586            WHEN ahr.sms_notify IS NULL THEN FALSE
 587            WHEN ahr.sms_notify = '' THEN FALSE
 588            ELSE TRUE
 589            END AS sms_notify,
 590            ahr.frozen,
 591            ahr.thaw_date,
 592            ahr.shelf_time,
 593            ahr.cut_in_line,
 594            ahr.mint_condition,
 595            ahr.shelf_expire_time,
 596            ahr.current_shelf_lib
 597     FROM action.hold_request ahr
 598          JOIN actor.usr p ON (ahr.usr = p.id)
 599          LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
 600          LEFT JOIN actor.usr_address b ON (p.billing_address = b.id)
 601     UNION ALL
 602     SELECT
 603            usr_post_code,
 604            usr_home_ou,
 605            usr_profile,
 606            usr_birth_year,
 607            staff_placed,
 608            id,
 609            request_time,
 610            capture_time,
 611            fulfillment_time,
 612            checkin_time,
 613            return_time,
 614            prev_check_time,
 615            expire_time,
 616            cancel_time,
 617            cancel_cause,
 618            cancel_note,
 619            target,
 620            current_copy,
 621            fulfillment_staff,
 622            fulfillment_lib,
 623            request_lib,
 624            selection_ou,
 625            selection_depth,
 626            pickup_lib,
 627            hold_type,
 628            holdable_formats,
 629            phone_notify,
 630            email_notify,
 631            sms_notify,
 632            frozen,
 633            thaw_date,
 634            shelf_time,
 635            cut_in_line,
 636            mint_condition,
 637            shelf_expire_time,
 638            current_shelf_lib
 639     FROM action.aged_hold_request;
 640
 641 CREATE OR REPLACE FUNCTION action.age_hold_on_delete () RETURNS TRIGGER AS $$
 642 DECLARE
 643 BEGIN
 644     -- Archive a copy of the old row to action.aged_hold_request
 645
 646     INSERT INTO action.aged_hold_request
 647            (usr_post_code,
 648             usr_home_ou,
 649             usr_profile,
 650             usr_birth_year,
 651             staff_placed,
 652             id,
 653             request_time,
 654             capture_time,
 655             fulfillment_time,
 656             checkin_time,
 657             return_time,
 658             prev_check_time,
 659             expire_time,
 660             cancel_time,
 661             cancel_cause,
 662             cancel_note,
 663             target,
 664             current_copy,
 665             fulfillment_staff,
 666             fulfillment_lib,
 667             request_lib,
 668             selection_ou,
 669             selection_depth,
 670             pickup_lib,
 671             hold_type,
 672             holdable_formats,
 673             phone_notify,
 674             email_notify,
 675             sms_notify,
 676             frozen,
 677             thaw_date,
 678             shelf_time,
 679             cut_in_line,
 680             mint_condition,
 681             shelf_expire_time,
 682             current_shelf_lib)
 683       SELECT
 684            usr_post_code,
 685            usr_home_ou,
 686            usr_profile,
 687            usr_birth_year,
 688            staff_placed,
 689            id,
 690            request_time,
 691            capture_time,
 692            fulfillment_time,
 693            checkin_time,
 694            return_time,
 695            prev_check_time,
 696            expire_time,
 697            cancel_time,
 698            cancel_cause,
 699            cancel_note,
 700            target,
 701            current_copy,
 702            fulfillment_staff,
 703            fulfillment_lib,
 704            request_lib,
 705            selection_ou,
 706            selection_depth,
 707            pickup_lib,
 708            hold_type,
 709            holdable_formats,
 710            phone_notify,
 711            email_notify,
 712            sms_notify,
 713            frozen,
 714            thaw_date,
 715            shelf_time,
 716            cut_in_line,
 717            mint_condition,
 718            shelf_expire_time,
 719            current_shelf_lib
 720         FROM action.all_hold_request WHERE id = OLD.id;
 721
 722     RETURN OLD;
 723 END;
 724 $$ LANGUAGE 'plpgsql';
 725
 726 CREATE TRIGGER action_hold_request_aging_tgr
 727         BEFORE DELETE ON action.hold_request
 728         FOR EACH ROW
 729         EXECUTE PROCEDURE action.age_hold_on_delete ();
 730
 731 CREATE TABLE action.fieldset (
 732     id              SERIAL          PRIMARY KEY,
 733     owner           INT             NOT NULL REFERENCES actor.usr (id)
 734                                     DEFERRABLE INITIALLY DEFERRED,
 735         owning_lib      INT             NOT NULL REFERENCES actor.org_unit (id)
 736                                     DEFERRABLE INITIALLY DEFERRED,
 737         status          TEXT            NOT NULL
 738                                         CONSTRAINT valid_status CHECK ( status in
 739                                                                         ( 'PENDING', 'APPLIED', 'ERROR' )),
 740     creation_time   TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
 741     scheduled_time  TIMESTAMPTZ,
 742     applied_time    TIMESTAMPTZ,
 743     classname       TEXT            NOT NULL, -- an IDL class name
 744     name            TEXT            NOT NULL,
 745     stored_query    INT             REFERENCES query.stored_query (id)
 746                                     DEFERRABLE INITIALLY DEFERRED,
 747     pkey_value      TEXT,
 748         CONSTRAINT lib_name_unique UNIQUE (owning_lib, name),
 749     CONSTRAINT fieldset_one_or_the_other CHECK (
 750         (stored_query IS NOT NULL AND pkey_value IS NULL) OR
 751         (pkey_value IS NOT NULL AND stored_query IS NULL)
 752     )
 753         -- the CHECK constraint means we can update the fields for a single
 754         -- row without all the extra overhead involved in a query
 755 );
 756
 757 CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time );
 758 CREATE INDEX action_owner_idx               ON action.fieldset( owner );
 759
 760
 761 CREATE TABLE action.fieldset_col_val (
 762     id              SERIAL  PRIMARY KEY,
 763     fieldset        INT     NOT NULL REFERENCES action.fieldset
 764                                          ON DELETE CASCADE
 765                                          DEFERRABLE INITIALLY DEFERRED,
 766     col             TEXT    NOT NULL,  -- "field" from the idl ... the column on the table
 767     val             TEXT,              -- value for the column ... NULL means, well, NULL
 768     CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col)
 769 );
 770
 771
 772 -- represents a circ chain summary
 773 CREATE TYPE action.circ_chain_summary AS (
 774     num_circs INTEGER,
 775     start_time TIMESTAMP WITH TIME ZONE,
 776     checkout_workstation TEXT,
 777     last_renewal_time TIMESTAMP WITH TIME ZONE, -- NULL if no renewals
 778     last_stop_fines TEXT,
 779     last_stop_fines_time TIMESTAMP WITH TIME ZONE,
 780     last_renewal_workstation TEXT, -- NULL if no renewals
 781     last_checkin_workstation TEXT,
 782     last_checkin_time TIMESTAMP WITH TIME ZONE,
 783     last_checkin_scan_time TIMESTAMP WITH TIME ZONE
 784 );
 785
 786
 787 CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id BIGINT ) RETURNS SETOF action.circulation AS $$
 788 DECLARE
 789     tmp_circ action.circulation%ROWTYPE;
 790     circ_0 action.circulation%ROWTYPE;
 791 BEGIN
 792
 793     SELECT INTO tmp_circ * FROM action.circulation WHERE id = ctx_circ_id;
 794
 795     IF tmp_circ IS NULL THEN
 796         RETURN NEXT tmp_circ;
 797     END IF;
 798     circ_0 := tmp_circ;
 799
 800     -- find the front of the chain
 801     WHILE TRUE LOOP
 802         SELECT INTO tmp_circ * FROM action.circulation WHERE id = tmp_circ.parent_circ;
 803         IF tmp_circ IS NULL THEN
 804             EXIT;
 805         END IF;
 806         circ_0 := tmp_circ;
 807     END LOOP;
 808
 809     -- now send the circs to the caller, oldest to newest
 810     tmp_circ := circ_0;
 811     WHILE TRUE LOOP
 812         IF tmp_circ IS NULL THEN
 813             EXIT;
 814         END IF;
 815         RETURN NEXT tmp_circ;
 816         SELECT INTO tmp_circ * FROM action.circulation WHERE parent_circ = tmp_circ.id;
 817     END LOOP;
 818
 819 END;
 820 $$ LANGUAGE 'plpgsql';
 821
 822 CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id BIGINT ) RETURNS action.circ_chain_summary AS $$
 823
 824 DECLARE
 825
 826     -- first circ in the chain
 827     circ_0 action.circulation%ROWTYPE;
 828
 829     -- last circ in the chain
 830     circ_n action.circulation%ROWTYPE;
 831
 832     -- circ chain under construction
 833     chain action.circ_chain_summary;
 834     tmp_circ action.circulation%ROWTYPE;
 835
 836 BEGIN
 837
 838     chain.num_circs := 0;
 839     FOR tmp_circ IN SELECT * FROM action.circ_chain(ctx_circ_id) LOOP
 840
 841         IF chain.num_circs = 0 THEN
 842             circ_0 := tmp_circ;
 843         END IF;
 844
 845         chain.num_circs := chain.num_circs + 1;
 846         circ_n := tmp_circ;
 847     END LOOP;
 848
 849     chain.start_time := circ_0.xact_start;
 850     chain.last_stop_fines := circ_n.stop_fines;
 851     chain.last_stop_fines_time := circ_n.stop_fines_time;
 852     chain.last_checkin_time := circ_n.checkin_time;
 853     chain.last_checkin_scan_time := circ_n.checkin_scan_time;
 854     SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
 855     SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
 856
 857     IF chain.num_circs > 1 THEN
 858         chain.last_renewal_time := circ_n.xact_start;
 859         SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
 860     END IF;
 861
 862     RETURN chain;
 863
 864 END;
 865 $$ LANGUAGE 'plpgsql';
 866
 867 -- Return the list of circ chain heads in xact_start order that the user has chosen to "retain"
 868 CREATE OR REPLACE FUNCTION action.usr_visible_circs (usr_id INT) RETURNS SETOF action.circulation AS $func$
 869 DECLARE
 870     c               action.circulation%ROWTYPE;
 871     view_age        INTERVAL;
 872     usr_view_age    actor.usr_setting%ROWTYPE;
 873     usr_view_start  actor.usr_setting%ROWTYPE;
 874 BEGIN
 875     SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_age';
 876     SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start';
 877
 878     IF usr_view_age.value IS NOT NULL AND usr_view_start.value IS NOT NULL THEN
 879         -- User opted in and supplied a retention age
 880         IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
 881             view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
 882         ELSE
 883             view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
 884         END IF;
 885     ELSIF usr_view_start.value IS NOT NULL THEN
 886         -- User opted in
 887         view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
 888     ELSE
 889         -- User did not opt in
 890         RETURN;
 891     END IF;
 892
 893     FOR c IN
 894         SELECT  *
 895           FROM  action.circulation
 896           WHERE usr = usr_id
 897                 AND parent_circ IS NULL
 898                 AND xact_start > NOW() - view_age
 899           ORDER BY xact_start DESC
 900     LOOP
 901         RETURN NEXT c;
 902     END LOOP;
 903
 904     RETURN;
 905 END;
 906 $func$ LANGUAGE PLPGSQL;
 907
 908 CREATE OR REPLACE FUNCTION action.usr_visible_circ_copies( INTEGER ) RETURNS SETOF BIGINT AS $$
 909     SELECT DISTINCT(target_copy) FROM action.usr_visible_circs($1)
 910 $$ LANGUAGE SQL ROWS 10;
 911
 912 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
 913 DECLARE
 914     h               action.hold_request%ROWTYPE;
 915     view_age        INTERVAL;
 916     view_count      INT;
 917     usr_view_count  actor.usr_setting%ROWTYPE;
 918     usr_view_age    actor.usr_setting%ROWTYPE;
 919     usr_view_start  actor.usr_setting%ROWTYPE;
 920 BEGIN
 921     SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
 922     SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
 923     SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
 924
 925     FOR h IN
 926         SELECT  *
 927           FROM  action.hold_request
 928           WHERE usr = usr_id
 929                 AND fulfillment_time IS NULL
 930                 AND cancel_time IS NULL
 931           ORDER BY request_time DESC
 932     LOOP
 933         RETURN NEXT h;
 934     END LOOP;
 935
 936     IF usr_view_start.value IS NULL THEN
 937         RETURN;
 938     END IF;
 939
 940     IF usr_view_age.value IS NOT NULL THEN
 941         -- User opted in and supplied a retention age
 942         IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
 943             view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
 944         ELSE
 945             view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
 946         END IF;
 947     ELSE
 948         -- User opted in
 949         view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
 950     END IF;
 951
 952     IF usr_view_count.value IS NOT NULL THEN
 953         view_count := oils_json_to_text(usr_view_count.value)::INT;
 954     ELSE
 955         view_count := 1000;
 956     END IF;
 957
 958     -- show some fulfilled/canceled holds
 959     FOR h IN
 960         SELECT  *
 961           FROM  action.hold_request
 962           WHERE usr = usr_id
 963                 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
 964                 AND COALESCE(fulfillment_time, cancel_time) > NOW() - view_age
 965           ORDER BY COALESCE(fulfillment_time, cancel_time) DESC
 966           LIMIT view_count
 967     LOOP
 968         RETURN NEXT h;
 969     END LOOP;
 970
 971     RETURN;
 972 END;
 973 $func$ LANGUAGE PLPGSQL;
 974
 975 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
 976 DECLARE
 977     usr_keep_age    actor.usr_setting%ROWTYPE;
 978     usr_keep_start  actor.usr_setting%ROWTYPE;
 979     org_keep_age    INTERVAL;
 980     org_use_last    BOOL = false;
 981     org_age_is_min  BOOL = false;
 982     org_keep_count  INT;
 983
 984     keep_age        INTERVAL;
 985
 986     target_acp      RECORD;
 987     circ_chain_head action.circulation%ROWTYPE;
 988     circ_chain_tail action.circulation%ROWTYPE;
 989
 990     count_purged    INT;
 991     num_incomplete  INT;
 992
 993     last_finished   TIMESTAMP WITH TIME ZONE;
 994 BEGIN
 995
 996     count_purged := 0;
 997
 998     SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
 999
1000     SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
1001     IF org_keep_count IS NULL THEN
1002         RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
1003     END IF;
1004
1005     SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished';
1006     SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min';
1007
1008     -- First, find copies with more than keep_count non-renewal circs
1009     FOR target_acp IN
1010         SELECT  target_copy,
1011                 COUNT(*) AS total_real_circs
1012           FROM  action.circulation
1013           WHERE parent_circ IS NULL
1014                 AND xact_finish IS NOT NULL
1015           GROUP BY target_copy
1016           HAVING COUNT(*) > org_keep_count
1017     LOOP
1018         -- And, for those, select circs that are finished and older than keep_age
1019         FOR circ_chain_head IN
1020             -- For reference, the subquery uses a window function to order the circs newest to oldest and number them
1021             -- The outer query then uses that information to skip the most recent set the library wants to keep
1022             -- End result is we don't care what order they come out in, as they are all potentials for deletion.
1023             SELECT ac.* FROM action.circulation ac JOIN (
1024               SELECT  rank() OVER (ORDER BY xact_start DESC), ac.id
1025                 FROM  action.circulation ac
1026                 WHERE ac.target_copy = target_acp.target_copy
1027                   AND ac.parent_circ IS NULL
1028                 ORDER BY ac.xact_start ) ranked USING (id)
1029                 WHERE ranked.rank > org_keep_count
1030         LOOP
1031
1032             SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
1033             SELECT COUNT(CASE WHEN xact_finish IS NULL THEN 1 ELSE NULL END), MAX(xact_finish) INTO num_incomplete, last_finished FROM action.circ_chain(circ_chain_head.id);
1034             CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0;
1035
1036             IF NOT org_use_last THEN
1037                 last_finished := circ_chain_tail.xact_finish;
1038             END IF;
1039
1040             -- Now get the user settings, if any, to block purging if the user wants to keep more circs
1041             usr_keep_age.value := NULL;
1042             SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age';
1043
1044             usr_keep_start.value := NULL;
1045             SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start';
1046
1047             IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN
1048                 IF oils_json_to_text(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ) THEN
1049                     keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
1050                 ELSE
1051                     keep_age := oils_json_to_text(usr_keep_age.value)::INTERVAL;
1052                 END IF;
1053             ELSIF usr_keep_start.value IS NOT NULL THEN
1054                 keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
1055             ELSE
1056                 keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL );
1057             END IF;
1058
1059             IF org_age_is_min THEN
1060                 keep_age := GREATEST( keep_age, org_keep_age );
1061             END IF;
1062
1063             CONTINUE WHEN AGE(NOW(), last_finished) < keep_age;
1064
1065             -- We've passed the purging tests, purge the circ chain starting at the end
1066             -- A trigger should auto-purge the rest of the chain.
1067             DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
1068
1069             count_purged := count_purged + 1;
1070
1071         END LOOP;
1072     END LOOP;
1073
1074     return count_purged;
1075 END;
1076 $func$ LANGUAGE PLPGSQL;
1077
1078 CREATE OR REPLACE FUNCTION action.purge_holds() RETURNS INT AS $func$
1079 DECLARE
1080   current_hold RECORD;
1081   purged_holds INT;
1082   cgf_d INTERVAL;
1083   cgf_f INTERVAL;
1084   cgf_c INTERVAL;
1085   prev_usr INT;
1086   user_start TIMESTAMPTZ;
1087   user_age INTERVAL;
1088   user_count INT;
1089 BEGIN
1090   purged_holds := 0;
1091   SELECT INTO cgf_d value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age' AND enabled;
1092   SELECT INTO cgf_f value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_fulfilled' AND enabled;
1093   SELECT INTO cgf_c value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_canceled' AND enabled;
1094   FOR current_hold IN
1095     SELECT
1096       rank() OVER (PARTITION BY usr ORDER BY COALESCE(fulfillment_time, cancel_time) DESC),
1097       cgf_cs.value::INTERVAL as cgf_cs,
1098       ahr.*
1099     FROM
1100       action.hold_request ahr
1101       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)
1102     WHERE
1103       (fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL)
1104   LOOP
1105     IF prev_usr IS NULL OR prev_usr != current_hold.usr THEN
1106       prev_usr := current_hold.usr;
1107       SELECT INTO user_start oils_json_to_text(value)::TIMESTAMPTZ FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_start';
1108       SELECT INTO user_age oils_json_to_text(value)::INTERVAL FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_age';
1109       SELECT INTO user_count oils_json_to_text(value)::INT FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_count';
1110       IF user_start IS NOT NULL THEN
1111         user_age := LEAST(user_age, AGE(NOW(), user_start));
1112       END IF;
1113       IF user_count IS NULL THEN
1114         user_count := 1000; -- Assumption based on the user visible holds routine
1115       END IF;
1116     END IF;
1117     -- Library keep age trumps user keep anything, for purposes of being able to hold on to things when staff canceled and such.
1118     IF current_hold.fulfillment_time IS NOT NULL AND current_hold.fulfillment_time > NOW() - COALESCE(cgf_f, cgf_d) THEN
1119       CONTINUE;
1120     END IF;
1121     IF current_hold.cancel_time IS NOT NULL AND current_hold.cancel_time > NOW() - COALESCE(current_hold.cgf_cs, cgf_c, cgf_d) THEN
1122       CONTINUE;
1123     END IF;
1124
1125     -- User keep age needs combining with count. If too old AND within the count, keep!
1126     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
1127       CONTINUE;
1128     END IF;
1129
1130     -- All checks should have passed, delete!
1131     DELETE FROM action.hold_request WHERE id = current_hold.id;
1132     purged_holds := purged_holds + 1;
1133   END LOOP;
1134   RETURN purged_holds;
1135 END;
1136 $func$ LANGUAGE plpgsql;
1137
1138 CREATE OR REPLACE FUNCTION action.apply_fieldset(
1139         fieldset_id IN INT,        -- id from action.fieldset
1140         table_name  IN TEXT,       -- table to be updated
1141         pkey_name   IN TEXT,       -- name of primary key column in that table
1142         query       IN TEXT        -- query constructed by qstore (for query-based
1143                                    --    fieldsets only; otherwise null
1144 )
1145 RETURNS TEXT AS $$
1146 DECLARE
1147         statement TEXT;
1148         fs_status TEXT;
1149         fs_pkey_value TEXT;
1150         fs_query TEXT;
1151         sep CHAR;
1152         status_code TEXT;
1153         msg TEXT;
1154         update_count INT;
1155         cv RECORD;
1156 BEGIN
1157         -- Sanity checks
1158         IF fieldset_id IS NULL THEN
1159                 RETURN 'Fieldset ID parameter is NULL';
1160         END IF;
1161         IF table_name IS NULL THEN
1162                 RETURN 'Table name parameter is NULL';
1163         END IF;
1164         IF pkey_name IS NULL THEN
1165                 RETURN 'Primary key name parameter is NULL';
1166         END IF;
1167         --
1168         statement := 'UPDATE ' || table_name || ' SET';
1169         --
1170         SELECT
1171                 status,
1172                 quote_literal( pkey_value )
1173         INTO
1174                 fs_status,
1175                 fs_pkey_value
1176         FROM
1177                 action.fieldset
1178         WHERE
1179                 id = fieldset_id;
1180         --
1181         IF fs_status IS NULL THEN
1182                 RETURN 'No fieldset found for id = ' || fieldset_id;
1183         ELSIF fs_status = 'APPLIED' THEN
1184                 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
1185         END IF;
1186         --
1187         sep := '';
1188         FOR cv IN
1189                 SELECT  col,
1190                                 val
1191                 FROM    action.fieldset_col_val
1192                 WHERE   fieldset = fieldset_id
1193         LOOP
1194                 statement := statement || sep || ' ' || cv.col
1195                                          || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
1196                 sep := ',';
1197         END LOOP;
1198         --
1199         IF sep = '' THEN
1200                 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
1201         END IF;
1202         --
1203         -- Add the WHERE clause.  This differs according to whether it's a
1204         -- single-row fieldset or a query-based fieldset.
1205         --
1206         IF query IS NULL        AND fs_pkey_value IS NULL THEN
1207                 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
1208         ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
1209             fs_query := rtrim( query, ';' );
1210             statement := statement || ' WHERE ' || pkey_name || ' IN ( '
1211                          || fs_query || ' );';
1212         ELSIF query IS NULL     AND fs_pkey_value IS NOT NULL THEN
1213                 statement := statement || ' WHERE ' || pkey_name || ' = '
1214                                      || fs_pkey_value || ';';
1215         ELSE  -- both are not null
1216                 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
1217         END IF;
1218         --
1219         -- Execute the update
1220         --
1221         BEGIN
1222                 EXECUTE statement;
1223                 GET DIAGNOSTICS update_count = ROW_COUNT;
1224                 --
1225                 IF UPDATE_COUNT > 0 THEN
1226                         status_code := 'APPLIED';
1227                         msg := NULL;
1228                 ELSE
1229                         status_code := 'ERROR';
1230                         msg := 'No eligible rows found for fieldset ' || fieldset_id;
1231         END IF;
1232         EXCEPTION WHEN OTHERS THEN
1233                 status_code := 'ERROR';
1234                 msg := 'Unable to apply fieldset ' || fieldset_id
1235                            || ': ' || sqlerrm;
1236         END;
1237         --
1238         -- Update fieldset status
1239         --
1240         UPDATE action.fieldset
1241         SET status       = status_code,
1242             applied_time = now()
1243         WHERE id = fieldset_id;
1244         --
1245         RETURN msg;
1246 END;
1247 $$ LANGUAGE plpgsql;
1248
1249 COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$
1250 Applies a specified fieldset, using a supplied table name and primary
1251 key name.  The query parameter should be non-null only for
1252 query-based fieldsets.
1253
1254 Returns NULL if successful, or an error message if not.
1255 $$;
1256
1257 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
1258     ahr_id INT,
1259     acp_id BIGINT,
1260     copy_context_ou INT DEFAULT NULL
1261     -- TODO maybe? hold_context_ou INT DEFAULT NULL.  This would optionally
1262     -- support an "ahprox" measurement: adjust prox between copy circ lib and
1263     -- hold request lib, but I'm unsure whether to use this theoretical
1264     -- argument only in the baseline calculation or later in the other
1265     -- queries in this function.
1266 ) RETURNS NUMERIC AS $f$
1267 DECLARE
1268     aoupa           actor.org_unit_proximity_adjustment%ROWTYPE;
1269     ahr             action.hold_request%ROWTYPE;
1270     acp             asset.copy%ROWTYPE;
1271     acn             asset.call_number%ROWTYPE;
1272     acl             asset.copy_location%ROWTYPE;
1273     baseline_prox   NUMERIC;
1274
1275     icl_list        INT[];
1276     iol_list        INT[];
1277     isl_list        INT[];
1278     hpl_list        INT[];
1279     hrl_list        INT[];
1280
1281 BEGIN
1282
1283     SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
1284     SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
1285     SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
1286     SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
1287
1288     IF copy_context_ou IS NULL THEN
1289         copy_context_ou := acp.circ_lib;
1290     END IF;
1291
1292     -- First, gather the baseline proximity of "here" to pickup lib
1293     SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = copy_context_ou AND to_org = ahr.pickup_lib;
1294
1295     -- Find any absolute adjustments, and set the baseline prox to that
1296     SELECT  adj.* INTO aoupa
1297       FROM  actor.org_unit_proximity_adjustment adj
1298             LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1299             LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1300             LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
1301             LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1302             LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1303       WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1304         absolute_adjustment AND
1305         COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1306       ORDER BY
1307             COALESCE(acp_cl.distance,999)
1308                 + COALESCE(acn_ol.distance,999)
1309                 + COALESCE(acl_ol.distance,999)
1310                 + COALESCE(ahr_pl.distance,999)
1311                 + COALESCE(ahr_rl.distance,999),
1312             adj.pos
1313       LIMIT 1;
1314
1315     IF FOUND THEN
1316         baseline_prox := aoupa.prox_adjustment;
1317     END IF;
1318
1319     -- Now find any relative adjustments, and change the baseline prox based on them
1320     FOR aoupa IN
1321         SELECT  adj.*
1322           FROM  actor.org_unit_proximity_adjustment adj
1323                 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1324                 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1325                 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
1326                 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1327                 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1328           WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1329             NOT absolute_adjustment AND
1330             COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1331     LOOP
1332         baseline_prox := baseline_prox + aoupa.prox_adjustment;
1333     END LOOP;
1334
1335     RETURN baseline_prox;
1336 END;
1337 $f$ LANGUAGE PLPGSQL;
1338
1339 COMMIT;