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 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (7,'Patron via SIP');
 370 SELECT SETVAL('action.hold_request_cancel_cause_id_seq', 100);
 371
 372 CREATE TABLE action.hold_request (
 373         id                      SERIAL                          PRIMARY KEY,
 374         request_time            TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 375         capture_time            TIMESTAMP WITH TIME ZONE,
 376         fulfillment_time        TIMESTAMP WITH TIME ZONE,
 377         checkin_time            TIMESTAMP WITH TIME ZONE,
 378         return_time             TIMESTAMP WITH TIME ZONE,
 379         prev_check_time         TIMESTAMP WITH TIME ZONE,
 380         expire_time             TIMESTAMP WITH TIME ZONE,
 381         cancel_time             TIMESTAMP WITH TIME ZONE,
 382         cancel_cause    INT REFERENCES action.hold_request_cancel_cause (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 383         cancel_note             TEXT,
 384         target                  BIGINT                          NOT NULL, -- see hold_type
 385         current_copy            BIGINT,                         -- REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,  -- XXX could be an serial.unit now...
 386         fulfillment_staff       INT                             REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 387         fulfillment_lib         INT                             REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 388         request_lib             INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 389         requestor               INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 390         usr                     INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 391         selection_ou            INT                             NOT NULL,
 392         selection_depth         INT                             NOT NULL DEFAULT 0,
 393         pickup_lib              INT                             NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
 394         hold_type               TEXT                            NOT NULL, -- CHECK (hold_type IN ('M','T','V','C')),  -- XXX constraint too constraining...
 395         holdable_formats        TEXT,
 396         phone_notify            TEXT,
 397         email_notify            BOOL                            NOT NULL DEFAULT FALSE,
 398         sms_notify              TEXT,
 399         sms_carrier             INT REFERENCES config.sms_carrier (id),
 400         frozen                  BOOL                            NOT NULL DEFAULT FALSE,
 401         thaw_date               TIMESTAMP WITH TIME ZONE,
 402         shelf_time              TIMESTAMP WITH TIME ZONE,
 403     cut_in_line     BOOL,
 404         mint_condition  BOOL NOT NULL DEFAULT TRUE,
 405         shelf_expire_time TIMESTAMPTZ,
 406         current_shelf_lib INT REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED
 407 );
 408 ALTER TABLE action.hold_request ADD CONSTRAINT sms_check CHECK (
 409     sms_notify IS NULL
 410     OR sms_carrier IS NOT NULL -- and implied sms_notify IS NOT NULL
 411 );
 412
 413
 414 CREATE INDEX hold_request_target_idx ON action.hold_request (target);
 415 CREATE INDEX hold_request_usr_idx ON action.hold_request (usr);
 416 CREATE INDEX hold_request_pickup_lib_idx ON action.hold_request (pickup_lib);
 417 CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy);
 418 CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time);
 419 CREATE INDEX hold_request_fulfillment_staff_idx ON action.hold_request ( fulfillment_staff );
 420 CREATE INDEX hold_request_requestor_idx         ON action.hold_request ( requestor );
 421 CREATE INDEX hold_request_open_idx ON action.hold_request (id) WHERE cancel_time IS NULL AND fulfillment_time IS NULL;
 422 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;
 423
 424
 425 CREATE TABLE action.hold_request_note (
 426
 427     id     BIGSERIAL PRIMARY KEY,
 428     hold   BIGINT    NOT NULL REFERENCES action.hold_request (id)
 429                               ON DELETE CASCADE
 430                               DEFERRABLE INITIALLY DEFERRED,
 431     title  TEXT      NOT NULL,
 432     body   TEXT      NOT NULL,
 433     slip   BOOL      NOT NULL DEFAULT FALSE,
 434     pub    BOOL      NOT NULL DEFAULT FALSE,
 435     staff  BOOL      NOT NULL DEFAULT FALSE  -- created by staff
 436
 437 );
 438 CREATE INDEX ahrn_hold_idx ON action.hold_request_note (hold);
 439
 440
 441 CREATE TABLE action.hold_notification (
 442         id              SERIAL                          PRIMARY KEY,
 443         hold            INT                             NOT NULL REFERENCES action.hold_request (id)
 444                                                                         ON DELETE CASCADE
 445                                                                         DEFERRABLE INITIALLY DEFERRED,
 446         notify_staff    INT                     REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 447         notify_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 448         method          TEXT                            NOT NULL, -- email address or phone number
 449         note            TEXT
 450 );
 451 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
 452 CREATE INDEX ahn_notify_staff_idx ON action.hold_notification ( notify_staff );
 453
 454 CREATE TABLE action.hold_copy_map (
 455         id              BIGSERIAL       PRIMARY KEY,
 456         hold            INT     NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 457         target_copy     BIGINT  NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
 458         proximity       NUMERIC,
 459         CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
 460 );
 461 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
 462 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
 463
 464 CREATE TABLE action.transit_copy (
 465         id                      SERIAL                          PRIMARY KEY,
 466         source_send_time        TIMESTAMP WITH TIME ZONE,
 467         dest_recv_time          TIMESTAMP WITH TIME ZONE,
 468         target_copy             BIGINT                          NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
 469         source                  INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 470         dest                    INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 471         prev_hop                INT                             REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED,
 472         copy_status             INT                             NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
 473         persistant_transfer     BOOL                            NOT NULL DEFAULT FALSE,
 474         prev_dest       INT                             REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
 475 );
 476 CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest);
 477 CREATE INDEX active_transit_source_idx ON "action".transit_copy (source);
 478 CREATE INDEX active_transit_cp_idx ON "action".transit_copy (target_copy);
 479
 480
 481 CREATE TABLE action.hold_transit_copy (
 482         hold    INT     REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
 483 ) INHERITS (action.transit_copy);
 484 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id);
 485 -- 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
 486 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest);
 487 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
 488 CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy);
 489 CREATE INDEX hold_transit_copy_hold_idx on action.hold_transit_copy (hold);
 490
 491
 492 CREATE TABLE action.unfulfilled_hold_list (
 493         id              BIGSERIAL                       PRIMARY KEY,
 494         current_copy    BIGINT                          NOT NULL,
 495         hold            INT                             NOT NULL,
 496         circ_lib        INT                             NOT NULL,
 497         fail_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
 498 );
 499 CREATE INDEX uhr_hold_idx ON action.unfulfilled_hold_list (hold);
 500
 501 CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS
 502     SELECT  u.hold,
 503             c.circ_lib,
 504             count(*)
 505       FROM  action.unfulfilled_hold_list u
 506             JOIN asset.copy c ON (c.id = u.current_copy)
 507       GROUP BY 1,2;
 508
 509 CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS
 510     SELECT  hold,
 511             min(count)
 512       FROM  action.unfulfilled_hold_loops
 513       GROUP BY 1;
 514
 515 CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS
 516     SELECT  DISTINCT l.*
 517       FROM  action.unfulfilled_hold_loops l
 518             JOIN action.unfulfilled_hold_min_loop m USING (hold)
 519       WHERE l.count = m.min;
 520
 521 CREATE VIEW action.unfulfilled_hold_max_loop AS
 522     SELECT  hold,
 523             max(count) AS max
 524       FROM  action.unfulfilled_hold_loops
 525       GROUP BY 1;
 526
 527
 528 CREATE TABLE action.aged_hold_request (
 529     usr_post_code               TEXT,
 530     usr_home_ou         INT     NOT NULL,
 531     usr_profile         INT     NOT NULL,
 532     usr_birth_year              INT,
 533     staff_placed        BOOLEAN NOT NULL,
 534     LIKE action.hold_request
 535 );
 536 ALTER TABLE action.aged_hold_request
 537       ADD PRIMARY KEY (id),
 538       DROP COLUMN usr,
 539       DROP COLUMN requestor,
 540       DROP COLUMN sms_carrier,
 541       ALTER COLUMN phone_notify TYPE BOOLEAN
 542             USING CASE WHEN phone_notify IS NULL OR phone_notify = '' THEN FALSE ELSE TRUE END,
 543       ALTER COLUMN sms_notify TYPE BOOLEAN
 544             USING CASE WHEN sms_notify IS NULL OR sms_notify = '' THEN FALSE ELSE TRUE END,
 545       ALTER COLUMN phone_notify SET NOT NULL,
 546       ALTER COLUMN sms_notify SET NOT NULL;
 547 CREATE INDEX aged_hold_request_target_idx ON action.aged_hold_request (target);
 548 CREATE INDEX aged_hold_request_pickup_lib_idx ON action.aged_hold_request (pickup_lib);
 549 CREATE INDEX aged_hold_request_current_copy_idx ON action.aged_hold_request (current_copy);
 550 CREATE INDEX aged_hold_request_fulfillment_staff_idx ON action.aged_hold_request ( fulfillment_staff );
 551
 552 CREATE OR REPLACE VIEW action.all_hold_request AS
 553     SELECT DISTINCT
 554            COALESCE(a.post_code, b.post_code) AS usr_post_code,
 555            p.home_ou AS usr_home_ou,
 556            p.profile AS usr_profile,
 557            EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
 558            CAST(ahr.requestor <> ahr.usr AS BOOLEAN) AS staff_placed,
 559            ahr.id,
 560            ahr.request_time,
 561            ahr.capture_time,
 562            ahr.fulfillment_time,
 563            ahr.checkin_time,
 564            ahr.return_time,
 565            ahr.prev_check_time,
 566            ahr.expire_time,
 567            ahr.cancel_time,
 568            ahr.cancel_cause,
 569            ahr.cancel_note,
 570            ahr.target,
 571            ahr.current_copy,
 572            ahr.fulfillment_staff,
 573            ahr.fulfillment_lib,
 574            ahr.request_lib,
 575            ahr.selection_ou,
 576            ahr.selection_depth,
 577            ahr.pickup_lib,
 578            ahr.hold_type,
 579            ahr.holdable_formats,
 580            CASE
 581            WHEN ahr.phone_notify IS NULL THEN FALSE
 582            WHEN ahr.phone_notify = '' THEN FALSE
 583            ELSE TRUE
 584            END AS phone_notify,
 585            ahr.email_notify,
 586            CASE
 587            WHEN ahr.sms_notify IS NULL THEN FALSE
 588            WHEN ahr.sms_notify = '' THEN FALSE
 589            ELSE TRUE
 590            END AS sms_notify,
 591            ahr.frozen,
 592            ahr.thaw_date,
 593            ahr.shelf_time,
 594            ahr.cut_in_line,
 595            ahr.mint_condition,
 596            ahr.shelf_expire_time,
 597            ahr.current_shelf_lib
 598     FROM action.hold_request ahr
 599          JOIN actor.usr p ON (ahr.usr = p.id)
 600          LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
 601          LEFT JOIN actor.usr_address b ON (p.billing_address = b.id)
 602     UNION ALL
 603     SELECT
 604            usr_post_code,
 605            usr_home_ou,
 606            usr_profile,
 607            usr_birth_year,
 608            staff_placed,
 609            id,
 610            request_time,
 611            capture_time,
 612            fulfillment_time,
 613            checkin_time,
 614            return_time,
 615            prev_check_time,
 616            expire_time,
 617            cancel_time,
 618            cancel_cause,
 619            cancel_note,
 620            target,
 621            current_copy,
 622            fulfillment_staff,
 623            fulfillment_lib,
 624            request_lib,
 625            selection_ou,
 626            selection_depth,
 627            pickup_lib,
 628            hold_type,
 629            holdable_formats,
 630            phone_notify,
 631            email_notify,
 632            sms_notify,
 633            frozen,
 634            thaw_date,
 635            shelf_time,
 636            cut_in_line,
 637            mint_condition,
 638            shelf_expire_time,
 639            current_shelf_lib
 640     FROM action.aged_hold_request;
 641
 642 CREATE OR REPLACE FUNCTION action.age_hold_on_delete () RETURNS TRIGGER AS $$
 643 DECLARE
 644 BEGIN
 645     -- Archive a copy of the old row to action.aged_hold_request
 646
 647     INSERT INTO action.aged_hold_request
 648            (usr_post_code,
 649             usr_home_ou,
 650             usr_profile,
 651             usr_birth_year,
 652             staff_placed,
 653             id,
 654             request_time,
 655             capture_time,
 656             fulfillment_time,
 657             checkin_time,
 658             return_time,
 659             prev_check_time,
 660             expire_time,
 661             cancel_time,
 662             cancel_cause,
 663             cancel_note,
 664             target,
 665             current_copy,
 666             fulfillment_staff,
 667             fulfillment_lib,
 668             request_lib,
 669             selection_ou,
 670             selection_depth,
 671             pickup_lib,
 672             hold_type,
 673             holdable_formats,
 674             phone_notify,
 675             email_notify,
 676             sms_notify,
 677             frozen,
 678             thaw_date,
 679             shelf_time,
 680             cut_in_line,
 681             mint_condition,
 682             shelf_expire_time,
 683             current_shelf_lib)
 684       SELECT
 685            usr_post_code,
 686            usr_home_ou,
 687            usr_profile,
 688            usr_birth_year,
 689            staff_placed,
 690            id,
 691            request_time,
 692            capture_time,
 693            fulfillment_time,
 694            checkin_time,
 695            return_time,
 696            prev_check_time,
 697            expire_time,
 698            cancel_time,
 699            cancel_cause,
 700            cancel_note,
 701            target,
 702            current_copy,
 703            fulfillment_staff,
 704            fulfillment_lib,
 705            request_lib,
 706            selection_ou,
 707            selection_depth,
 708            pickup_lib,
 709            hold_type,
 710            holdable_formats,
 711            phone_notify,
 712            email_notify,
 713            sms_notify,
 714            frozen,
 715            thaw_date,
 716            shelf_time,
 717            cut_in_line,
 718            mint_condition,
 719            shelf_expire_time,
 720            current_shelf_lib
 721         FROM action.all_hold_request WHERE id = OLD.id;
 722
 723     RETURN OLD;
 724 END;
 725 $$ LANGUAGE 'plpgsql';
 726
 727 CREATE TRIGGER action_hold_request_aging_tgr
 728         BEFORE DELETE ON action.hold_request
 729         FOR EACH ROW
 730         EXECUTE PROCEDURE action.age_hold_on_delete ();
 731
 732 CREATE TABLE action.fieldset (
 733     id              SERIAL          PRIMARY KEY,
 734     owner           INT             NOT NULL REFERENCES actor.usr (id)
 735                                     DEFERRABLE INITIALLY DEFERRED,
 736         owning_lib      INT             NOT NULL REFERENCES actor.org_unit (id)
 737                                     DEFERRABLE INITIALLY DEFERRED,
 738         status          TEXT            NOT NULL
 739                                         CONSTRAINT valid_status CHECK ( status in
 740                                                                         ( 'PENDING', 'APPLIED', 'ERROR' )),
 741     creation_time   TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
 742     scheduled_time  TIMESTAMPTZ,
 743     applied_time    TIMESTAMPTZ,
 744     classname       TEXT            NOT NULL, -- an IDL class name
 745     name            TEXT            NOT NULL,
 746     stored_query    INT             REFERENCES query.stored_query (id)
 747                                     DEFERRABLE INITIALLY DEFERRED,
 748     pkey_value      TEXT,
 749         CONSTRAINT lib_name_unique UNIQUE (owning_lib, name),
 750     CONSTRAINT fieldset_one_or_the_other CHECK (
 751         (stored_query IS NOT NULL AND pkey_value IS NULL) OR
 752         (pkey_value IS NOT NULL AND stored_query IS NULL)
 753     )
 754         -- the CHECK constraint means we can update the fields for a single
 755         -- row without all the extra overhead involved in a query
 756 );
 757
 758 CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time );
 759 CREATE INDEX action_owner_idx               ON action.fieldset( owner );
 760
 761
 762 CREATE TABLE action.fieldset_col_val (
 763     id              SERIAL  PRIMARY KEY,
 764     fieldset        INT     NOT NULL REFERENCES action.fieldset
 765                                          ON DELETE CASCADE
 766                                          DEFERRABLE INITIALLY DEFERRED,
 767     col             TEXT    NOT NULL,  -- "field" from the idl ... the column on the table
 768     val             TEXT,              -- value for the column ... NULL means, well, NULL
 769     CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col)
 770 );
 771
 772
 773 -- represents a circ chain summary
 774 CREATE TYPE action.circ_chain_summary AS (
 775     num_circs INTEGER,
 776     start_time TIMESTAMP WITH TIME ZONE,
 777     checkout_workstation TEXT,
 778     last_renewal_time TIMESTAMP WITH TIME ZONE, -- NULL if no renewals
 779     last_stop_fines TEXT,
 780     last_stop_fines_time TIMESTAMP WITH TIME ZONE,
 781     last_renewal_workstation TEXT, -- NULL if no renewals
 782     last_checkin_workstation TEXT,
 783     last_checkin_time TIMESTAMP WITH TIME ZONE,
 784     last_checkin_scan_time TIMESTAMP WITH TIME ZONE
 785 );
 786
 787
 788 CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id BIGINT ) RETURNS SETOF action.circulation AS $$
 789 DECLARE
 790     tmp_circ action.circulation%ROWTYPE;
 791     circ_0 action.circulation%ROWTYPE;
 792 BEGIN
 793
 794     SELECT INTO tmp_circ * FROM action.circulation WHERE id = ctx_circ_id;
 795
 796     IF tmp_circ IS NULL THEN
 797         RETURN NEXT tmp_circ;
 798     END IF;
 799     circ_0 := tmp_circ;
 800
 801     -- find the front of the chain
 802     WHILE TRUE LOOP
 803         SELECT INTO tmp_circ * FROM action.circulation WHERE id = tmp_circ.parent_circ;
 804         IF tmp_circ IS NULL THEN
 805             EXIT;
 806         END IF;
 807         circ_0 := tmp_circ;
 808     END LOOP;
 809
 810     -- now send the circs to the caller, oldest to newest
 811     tmp_circ := circ_0;
 812     WHILE TRUE LOOP
 813         IF tmp_circ IS NULL THEN
 814             EXIT;
 815         END IF;
 816         RETURN NEXT tmp_circ;
 817         SELECT INTO tmp_circ * FROM action.circulation WHERE parent_circ = tmp_circ.id;
 818     END LOOP;
 819
 820 END;
 821 $$ LANGUAGE 'plpgsql';
 822
 823 CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id BIGINT ) RETURNS action.circ_chain_summary AS $$
 824
 825 DECLARE
 826
 827     -- first circ in the chain
 828     circ_0 action.circulation%ROWTYPE;
 829
 830     -- last circ in the chain
 831     circ_n action.circulation%ROWTYPE;
 832
 833     -- circ chain under construction
 834     chain action.circ_chain_summary;
 835     tmp_circ action.circulation%ROWTYPE;
 836
 837 BEGIN
 838
 839     chain.num_circs := 0;
 840     FOR tmp_circ IN SELECT * FROM action.circ_chain(ctx_circ_id) LOOP
 841
 842         IF chain.num_circs = 0 THEN
 843             circ_0 := tmp_circ;
 844         END IF;
 845
 846         chain.num_circs := chain.num_circs + 1;
 847         circ_n := tmp_circ;
 848     END LOOP;
 849
 850     chain.start_time := circ_0.xact_start;
 851     chain.last_stop_fines := circ_n.stop_fines;
 852     chain.last_stop_fines_time := circ_n.stop_fines_time;
 853     chain.last_checkin_time := circ_n.checkin_time;
 854     chain.last_checkin_scan_time := circ_n.checkin_scan_time;
 855     SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
 856     SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
 857
 858     IF chain.num_circs > 1 THEN
 859         chain.last_renewal_time := circ_n.xact_start;
 860         SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
 861     END IF;
 862
 863     RETURN chain;
 864
 865 END;
 866 $$ LANGUAGE 'plpgsql';
 867
 868 -- Return the list of circ chain heads in xact_start order that the user has chosen to "retain"
 869 CREATE OR REPLACE FUNCTION action.usr_visible_circs (usr_id INT) RETURNS SETOF action.circulation AS $func$
 870 DECLARE
 871     c               action.circulation%ROWTYPE;
 872     view_age        INTERVAL;
 873     usr_view_age    actor.usr_setting%ROWTYPE;
 874     usr_view_start  actor.usr_setting%ROWTYPE;
 875 BEGIN
 876     SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_age';
 877     SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start';
 878
 879     IF usr_view_age.value IS NOT NULL AND usr_view_start.value IS NOT NULL THEN
 880         -- User opted in and supplied a retention age
 881         IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
 882             view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
 883         ELSE
 884             view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
 885         END IF;
 886     ELSIF usr_view_start.value IS NOT NULL THEN
 887         -- User opted in
 888         view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
 889     ELSE
 890         -- User did not opt in
 891         RETURN;
 892     END IF;
 893
 894     FOR c IN
 895         SELECT  *
 896           FROM  action.circulation
 897           WHERE usr = usr_id
 898                 AND parent_circ IS NULL
 899                 AND xact_start > NOW() - view_age
 900           ORDER BY xact_start DESC
 901     LOOP
 902         RETURN NEXT c;
 903     END LOOP;
 904
 905     RETURN;
 906 END;
 907 $func$ LANGUAGE PLPGSQL;
 908
 909 CREATE OR REPLACE FUNCTION action.usr_visible_circ_copies( INTEGER ) RETURNS SETOF BIGINT AS $$
 910     SELECT DISTINCT(target_copy) FROM action.usr_visible_circs($1)
 911 $$ LANGUAGE SQL ROWS 10;
 912
 913 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
 914 DECLARE
 915     h               action.hold_request%ROWTYPE;
 916     view_age        INTERVAL;
 917     view_count      INT;
 918     usr_view_count  actor.usr_setting%ROWTYPE;
 919     usr_view_age    actor.usr_setting%ROWTYPE;
 920     usr_view_start  actor.usr_setting%ROWTYPE;
 921 BEGIN
 922     SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
 923     SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
 924     SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
 925
 926     FOR h IN
 927         SELECT  *
 928           FROM  action.hold_request
 929           WHERE usr = usr_id
 930                 AND fulfillment_time IS NULL
 931                 AND cancel_time IS NULL
 932           ORDER BY request_time DESC
 933     LOOP
 934         RETURN NEXT h;
 935     END LOOP;
 936
 937     IF usr_view_start.value IS NULL THEN
 938         RETURN;
 939     END IF;
 940
 941     IF usr_view_age.value IS NOT NULL THEN
 942         -- User opted in and supplied a retention age
 943         IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
 944             view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
 945         ELSE
 946             view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
 947         END IF;
 948     ELSE
 949         -- User opted in
 950         view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
 951     END IF;
 952
 953     IF usr_view_count.value IS NOT NULL THEN
 954         view_count := oils_json_to_text(usr_view_count.value)::INT;
 955     ELSE
 956         view_count := 1000;
 957     END IF;
 958
 959     -- show some fulfilled/canceled holds
 960     FOR h IN
 961         SELECT  *
 962           FROM  action.hold_request
 963           WHERE usr = usr_id
 964                 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
 965                 AND COALESCE(fulfillment_time, cancel_time) > NOW() - view_age
 966           ORDER BY COALESCE(fulfillment_time, cancel_time) DESC
 967           LIMIT view_count
 968     LOOP
 969         RETURN NEXT h;
 970     END LOOP;
 971
 972     RETURN;
 973 END;
 974 $func$ LANGUAGE PLPGSQL;
 975
 976 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
 977 DECLARE
 978     usr_keep_age    actor.usr_setting%ROWTYPE;
 979     usr_keep_start  actor.usr_setting%ROWTYPE;
 980     org_keep_age    INTERVAL;
 981     org_use_last    BOOL = false;
 982     org_age_is_min  BOOL = false;
 983     org_keep_count  INT;
 984
 985     keep_age        INTERVAL;
 986
 987     target_acp      RECORD;
 988     circ_chain_head action.circulation%ROWTYPE;
 989     circ_chain_tail action.circulation%ROWTYPE;
 990
 991     count_purged    INT;
 992     num_incomplete  INT;
 993
 994     last_finished   TIMESTAMP WITH TIME ZONE;
 995 BEGIN
 996
 997     count_purged := 0;
 998
 999     SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
1000
1001     SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
1002     IF org_keep_count IS NULL THEN
1003         RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
1004     END IF;
1005
1006     SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished';
1007     SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min';
1008
1009     -- First, find copies with more than keep_count non-renewal circs
1010     FOR target_acp IN
1011         SELECT  target_copy,
1012                 COUNT(*) AS total_real_circs
1013           FROM  action.circulation
1014           WHERE parent_circ IS NULL
1015                 AND xact_finish IS NOT NULL
1016           GROUP BY target_copy
1017           HAVING COUNT(*) > org_keep_count
1018     LOOP
1019         -- And, for those, select circs that are finished and older than keep_age
1020         FOR circ_chain_head IN
1021             -- For reference, the subquery uses a window function to order the circs newest to oldest and number them
1022             -- The outer query then uses that information to skip the most recent set the library wants to keep
1023             -- End result is we don't care what order they come out in, as they are all potentials for deletion.
1024             SELECT ac.* FROM action.circulation ac JOIN (
1025               SELECT  rank() OVER (ORDER BY xact_start DESC), ac.id
1026                 FROM  action.circulation ac
1027                 WHERE ac.target_copy = target_acp.target_copy
1028                   AND ac.parent_circ IS NULL
1029                 ORDER BY ac.xact_start ) ranked USING (id)
1030                 WHERE ranked.rank > org_keep_count
1031         LOOP
1032
1033             SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
1034             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);
1035             CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0;
1036
1037             IF NOT org_use_last THEN
1038                 last_finished := circ_chain_tail.xact_finish;
1039             END IF;
1040
1041             -- Now get the user settings, if any, to block purging if the user wants to keep more circs
1042             usr_keep_age.value := NULL;
1043             SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age';
1044
1045             usr_keep_start.value := NULL;
1046             SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start';
1047
1048             IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN
1049                 IF oils_json_to_text(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ) THEN
1050                     keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
1051                 ELSE
1052                     keep_age := oils_json_to_text(usr_keep_age.value)::INTERVAL;
1053                 END IF;
1054             ELSIF usr_keep_start.value IS NOT NULL THEN
1055                 keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
1056             ELSE
1057                 keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL );
1058             END IF;
1059
1060             IF org_age_is_min THEN
1061                 keep_age := GREATEST( keep_age, org_keep_age );
1062             END IF;
1063
1064             CONTINUE WHEN AGE(NOW(), last_finished) < keep_age;
1065
1066             -- We've passed the purging tests, purge the circ chain starting at the end
1067             -- A trigger should auto-purge the rest of the chain.
1068             DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
1069
1070             count_purged := count_purged + 1;
1071
1072         END LOOP;
1073     END LOOP;
1074
1075     return count_purged;
1076 END;
1077 $func$ LANGUAGE PLPGSQL;
1078
1079 CREATE OR REPLACE FUNCTION action.purge_holds() RETURNS INT AS $func$
1080 DECLARE
1081   current_hold RECORD;
1082   purged_holds INT;
1083   cgf_d INTERVAL;
1084   cgf_f INTERVAL;
1085   cgf_c INTERVAL;
1086   prev_usr INT;
1087   user_start TIMESTAMPTZ;
1088   user_age INTERVAL;
1089   user_count INT;
1090 BEGIN
1091   purged_holds := 0;
1092   SELECT INTO cgf_d value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age' AND enabled;
1093   SELECT INTO cgf_f value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_fulfilled' AND enabled;
1094   SELECT INTO cgf_c value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_canceled' AND enabled;
1095   FOR current_hold IN
1096     SELECT
1097       rank() OVER (PARTITION BY usr ORDER BY COALESCE(fulfillment_time, cancel_time) DESC),
1098       cgf_cs.value::INTERVAL as cgf_cs,
1099       ahr.*
1100     FROM
1101       action.hold_request ahr
1102       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)
1103     WHERE
1104       (fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL)
1105   LOOP
1106     IF prev_usr IS NULL OR prev_usr != current_hold.usr THEN
1107       prev_usr := current_hold.usr;
1108       SELECT INTO user_start oils_json_to_text(value)::TIMESTAMPTZ FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_start';
1109       SELECT INTO user_age oils_json_to_text(value)::INTERVAL FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_age';
1110       SELECT INTO user_count oils_json_to_text(value)::INT FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_count';
1111       IF user_start IS NOT NULL THEN
1112         user_age := LEAST(user_age, AGE(NOW(), user_start));
1113       END IF;
1114       IF user_count IS NULL THEN
1115         user_count := 1000; -- Assumption based on the user visible holds routine
1116       END IF;
1117     END IF;
1118     -- Library keep age trumps user keep anything, for purposes of being able to hold on to things when staff canceled and such.
1119     IF current_hold.fulfillment_time IS NOT NULL AND current_hold.fulfillment_time > NOW() - COALESCE(cgf_f, cgf_d) THEN
1120       CONTINUE;
1121     END IF;
1122     IF current_hold.cancel_time IS NOT NULL AND current_hold.cancel_time > NOW() - COALESCE(current_hold.cgf_cs, cgf_c, cgf_d) THEN
1123       CONTINUE;
1124     END IF;
1125
1126     -- User keep age needs combining with count. If too old AND within the count, keep!
1127     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
1128       CONTINUE;
1129     END IF;
1130
1131     -- All checks should have passed, delete!
1132     DELETE FROM action.hold_request WHERE id = current_hold.id;
1133     purged_holds := purged_holds + 1;
1134   END LOOP;
1135   RETURN purged_holds;
1136 END;
1137 $func$ LANGUAGE plpgsql;
1138
1139 CREATE OR REPLACE FUNCTION action.apply_fieldset(
1140         fieldset_id IN INT,        -- id from action.fieldset
1141         table_name  IN TEXT,       -- table to be updated
1142         pkey_name   IN TEXT,       -- name of primary key column in that table
1143         query       IN TEXT        -- query constructed by qstore (for query-based
1144                                    --    fieldsets only; otherwise null
1145 )
1146 RETURNS TEXT AS $$
1147 DECLARE
1148         statement TEXT;
1149         fs_status TEXT;
1150         fs_pkey_value TEXT;
1151         fs_query TEXT;
1152         sep CHAR;
1153         status_code TEXT;
1154         msg TEXT;
1155         update_count INT;
1156         cv RECORD;
1157 BEGIN
1158         -- Sanity checks
1159         IF fieldset_id IS NULL THEN
1160                 RETURN 'Fieldset ID parameter is NULL';
1161         END IF;
1162         IF table_name IS NULL THEN
1163                 RETURN 'Table name parameter is NULL';
1164         END IF;
1165         IF pkey_name IS NULL THEN
1166                 RETURN 'Primary key name parameter is NULL';
1167         END IF;
1168         --
1169         statement := 'UPDATE ' || table_name || ' SET';
1170         --
1171         SELECT
1172                 status,
1173                 quote_literal( pkey_value )
1174         INTO
1175                 fs_status,
1176                 fs_pkey_value
1177         FROM
1178                 action.fieldset
1179         WHERE
1180                 id = fieldset_id;
1181         --
1182         IF fs_status IS NULL THEN
1183                 RETURN 'No fieldset found for id = ' || fieldset_id;
1184         ELSIF fs_status = 'APPLIED' THEN
1185                 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
1186         END IF;
1187         --
1188         sep := '';
1189         FOR cv IN
1190                 SELECT  col,
1191                                 val
1192                 FROM    action.fieldset_col_val
1193                 WHERE   fieldset = fieldset_id
1194         LOOP
1195                 statement := statement || sep || ' ' || cv.col
1196                                          || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
1197                 sep := ',';
1198         END LOOP;
1199         --
1200         IF sep = '' THEN
1201                 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
1202         END IF;
1203         --
1204         -- Add the WHERE clause.  This differs according to whether it's a
1205         -- single-row fieldset or a query-based fieldset.
1206         --
1207         IF query IS NULL        AND fs_pkey_value IS NULL THEN
1208                 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
1209         ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
1210             fs_query := rtrim( query, ';' );
1211             statement := statement || ' WHERE ' || pkey_name || ' IN ( '
1212                          || fs_query || ' );';
1213         ELSIF query IS NULL     AND fs_pkey_value IS NOT NULL THEN
1214                 statement := statement || ' WHERE ' || pkey_name || ' = '
1215                                      || fs_pkey_value || ';';
1216         ELSE  -- both are not null
1217                 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
1218         END IF;
1219         --
1220         -- Execute the update
1221         --
1222         BEGIN
1223                 EXECUTE statement;
1224                 GET DIAGNOSTICS update_count = ROW_COUNT;
1225                 --
1226                 IF UPDATE_COUNT > 0 THEN
1227                         status_code := 'APPLIED';
1228                         msg := NULL;
1229                 ELSE
1230                         status_code := 'ERROR';
1231                         msg := 'No eligible rows found for fieldset ' || fieldset_id;
1232         END IF;
1233         EXCEPTION WHEN OTHERS THEN
1234                 status_code := 'ERROR';
1235                 msg := 'Unable to apply fieldset ' || fieldset_id
1236                            || ': ' || sqlerrm;
1237         END;
1238         --
1239         -- Update fieldset status
1240         --
1241         UPDATE action.fieldset
1242         SET status       = status_code,
1243             applied_time = now()
1244         WHERE id = fieldset_id;
1245         --
1246         RETURN msg;
1247 END;
1248 $$ LANGUAGE plpgsql;
1249
1250 COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$
1251 Applies a specified fieldset, using a supplied table name and primary
1252 key name.  The query parameter should be non-null only for
1253 query-based fieldsets.
1254
1255 Returns NULL if successful, or an error message if not.
1256 $$;
1257
1258 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
1259     ahr_id INT,
1260     acp_id BIGINT,
1261     copy_context_ou INT DEFAULT NULL
1262     -- TODO maybe? hold_context_ou INT DEFAULT NULL.  This would optionally
1263     -- support an "ahprox" measurement: adjust prox between copy circ lib and
1264     -- hold request lib, but I'm unsure whether to use this theoretical
1265     -- argument only in the baseline calculation or later in the other
1266     -- queries in this function.
1267 ) RETURNS NUMERIC AS $f$
1268 DECLARE
1269     aoupa           actor.org_unit_proximity_adjustment%ROWTYPE;
1270     ahr             action.hold_request%ROWTYPE;
1271     acp             asset.copy%ROWTYPE;
1272     acn             asset.call_number%ROWTYPE;
1273     acl             asset.copy_location%ROWTYPE;
1274     baseline_prox   NUMERIC;
1275
1276     icl_list        INT[];
1277     iol_list        INT[];
1278     isl_list        INT[];
1279     hpl_list        INT[];
1280     hrl_list        INT[];
1281
1282 BEGIN
1283
1284     SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
1285     SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
1286     SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
1287     SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
1288
1289     IF copy_context_ou IS NULL THEN
1290         copy_context_ou := acp.circ_lib;
1291     END IF;
1292
1293     -- First, gather the baseline proximity of "here" to pickup lib
1294     SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = copy_context_ou AND to_org = ahr.pickup_lib;
1295
1296     -- Find any absolute adjustments, and set the baseline prox to that
1297     SELECT  adj.* INTO aoupa
1298       FROM  actor.org_unit_proximity_adjustment adj
1299             LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1300             LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1301             LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
1302             LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1303             LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1304       WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1305         absolute_adjustment AND
1306         COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1307       ORDER BY
1308             COALESCE(acp_cl.distance,999)
1309                 + COALESCE(acn_ol.distance,999)
1310                 + COALESCE(acl_ol.distance,999)
1311                 + COALESCE(ahr_pl.distance,999)
1312                 + COALESCE(ahr_rl.distance,999),
1313             adj.pos
1314       LIMIT 1;
1315
1316     IF FOUND THEN
1317         baseline_prox := aoupa.prox_adjustment;
1318     END IF;
1319
1320     -- Now find any relative adjustments, and change the baseline prox based on them
1321     FOR aoupa IN
1322         SELECT  adj.*
1323           FROM  actor.org_unit_proximity_adjustment adj
1324                 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1325                 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1326                 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
1327                 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1328                 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1329           WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1330             NOT absolute_adjustment AND
1331             COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1332     LOOP
1333         baseline_prox := baseline_prox + aoupa.prox_adjustment;
1334     END LOOP;
1335
1336     RETURN baseline_prox;
1337 END;
1338 $f$ LANGUAGE PLPGSQL;
1339
1340 COMMIT;