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 CREATE INDEX action_aged_circulation_parent_circ_idx ON action.aged_circulation (parent_circ);
 236
 237 CREATE OR REPLACE VIEW action.all_circulation AS
 238     SELECT  id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
 239         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
 240         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
 241         stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
 242         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
 243         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
 244         NULL AS usr
 245       FROM  action.aged_circulation
 246             UNION ALL
 247     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,
 248         cp.call_number AS copy_call_number, circ.copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
 249         cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
 250         circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
 251         circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
 252         circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
 253         circ.parent_circ, circ.usr
 254       FROM  action.circulation circ
 255         JOIN asset.copy cp ON (circ.target_copy = cp.id)
 256         JOIN asset.call_number cn ON (cp.call_number = cn.id)
 257         JOIN actor.usr p ON (circ.usr = p.id)
 258         LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
 259         LEFT JOIN actor.usr_address b ON (p.billing_address = b.id);
 260
 261 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
 262 DECLARE
 263 found char := 'N';
 264 BEGIN
 265
 266     -- If there are any renewals for this circulation, don't archive or delete
 267     -- it yet.   We'll do so later, when we archive and delete the renewals.
 268
 269     SELECT 'Y' INTO found
 270     FROM action.circulation
 271     WHERE parent_circ = OLD.id
 272     LIMIT 1;
 273
 274     IF found = 'Y' THEN
 275         RETURN NULL;  -- don't delete
 276         END IF;
 277
 278     -- Archive a copy of the old row to action.aged_circulation
 279
 280     INSERT INTO action.aged_circulation
 281         (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
 282         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
 283         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
 284         stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
 285         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
 286         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ)
 287       SELECT
 288         id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
 289         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
 290         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
 291         stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
 292         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
 293         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
 294         FROM action.all_circulation WHERE id = OLD.id;
 295
 296     RETURN OLD;
 297 END;
 298 $$ LANGUAGE 'plpgsql';
 299
 300 CREATE TRIGGER action_circulation_aging_tgr
 301         BEFORE DELETE ON action.circulation
 302         FOR EACH ROW
 303         EXECUTE PROCEDURE action.age_circ_on_delete ();
 304
 305
 306 CREATE OR REPLACE FUNCTION action.age_parent_circ_on_delete () RETURNS TRIGGER AS $$
 307 BEGIN
 308
 309     -- Having deleted a renewal, we can delete the original circulation (or a previous
 310     -- renewal, if that's what parent_circ is pointing to).  That deletion will trigger
 311     -- deletion of any prior parents, etc. recursively.
 312
 313     IF OLD.parent_circ IS NOT NULL THEN
 314         DELETE FROM action.circulation
 315         WHERE id = OLD.parent_circ;
 316     END IF;
 317
 318     RETURN OLD;
 319 END;
 320 $$ LANGUAGE 'plpgsql';
 321
 322 CREATE TRIGGER age_parent_circ
 323         AFTER DELETE ON action.circulation
 324         FOR EACH ROW
 325         EXECUTE PROCEDURE action.age_parent_circ_on_delete ();
 326
 327
 328 CREATE OR REPLACE VIEW action.open_circulation AS
 329         SELECT  *
 330           FROM  action.circulation
 331           WHERE checkin_time IS NULL
 332           ORDER BY due_date;
 333
 334
 335 CREATE OR REPLACE VIEW action.billable_circulations AS
 336         SELECT  *
 337           FROM  action.circulation
 338           WHERE xact_finish IS NULL;
 339
 340 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
 341 BEGIN
 342         IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
 343                 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
 344                         UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
 345                 END IF;
 346                 IF NEW.stop_fines = 'CLAIMSNEVERCHECKEDOUT' THEN
 347                         UPDATE actor.usr SET claims_never_checked_out_count = claims_never_checked_out_count + 1 WHERE id = NEW.usr;
 348                 END IF;
 349                 IF NEW.stop_fines = 'LOST' THEN
 350                         UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
 351                 END IF;
 352         END IF;
 353         RETURN NEW;
 354 END;
 355 $$ LANGUAGE 'plpgsql';
 356 CREATE TRIGGER action_circulation_stop_fines_tgr
 357         BEFORE UPDATE ON action.circulation
 358         FOR EACH ROW
 359         EXECUTE PROCEDURE action.circulation_claims_returned ();
 360
 361 CREATE TABLE action.hold_request_cancel_cause (
 362     id      SERIAL  PRIMARY KEY,
 363     label   TEXT    UNIQUE
 364 );
 365
 366 CREATE TABLE action.hold_request (
 367         id                      SERIAL                          PRIMARY KEY,
 368         request_time            TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 369         capture_time            TIMESTAMP WITH TIME ZONE,
 370         fulfillment_time        TIMESTAMP WITH TIME ZONE,
 371         checkin_time            TIMESTAMP WITH TIME ZONE,
 372         return_time             TIMESTAMP WITH TIME ZONE,
 373         prev_check_time         TIMESTAMP WITH TIME ZONE,
 374         expire_time             TIMESTAMP WITH TIME ZONE,
 375         cancel_time             TIMESTAMP WITH TIME ZONE,
 376         cancel_cause    INT REFERENCES action.hold_request_cancel_cause (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 377         cancel_note             TEXT,
 378         target                  BIGINT                          NOT NULL, -- see hold_type
 379         current_copy            BIGINT,                         -- REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,  -- XXX could be an serial.unit now...
 380         fulfillment_staff       INT                             REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 381         fulfillment_lib         INT                             REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 382         request_lib             INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 383         requestor               INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 384         usr                     INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 385         selection_ou            INT                             NOT NULL,
 386         selection_depth         INT                             NOT NULL DEFAULT 0,
 387         pickup_lib              INT                             NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
 388         hold_type               TEXT                            NOT NULL, -- CHECK (hold_type IN ('M','T','V','C')),  -- XXX constraint too constraining...
 389         holdable_formats        TEXT,
 390         phone_notify            TEXT,
 391         email_notify            BOOL                            NOT NULL DEFAULT FALSE,
 392         sms_notify              TEXT,
 393         sms_carrier             INT REFERENCES config.sms_carrier (id),
 394         frozen                  BOOL                            NOT NULL DEFAULT FALSE,
 395         thaw_date               TIMESTAMP WITH TIME ZONE,
 396         shelf_time              TIMESTAMP WITH TIME ZONE,
 397     cut_in_line     BOOL,
 398         mint_condition  BOOL NOT NULL DEFAULT TRUE,
 399         shelf_expire_time TIMESTAMPTZ,
 400         current_shelf_lib INT REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
 401     behind_desk BOOLEAN NOT NULL DEFAULT FALSE
 402 );
 403 ALTER TABLE action.hold_request ADD CONSTRAINT sms_check CHECK (
 404     sms_notify IS NULL
 405     OR sms_carrier IS NOT NULL -- and implied sms_notify IS NOT NULL
 406 );
 407
 408
 409 CREATE OR REPLACE FUNCTION action.hold_request_clear_map () RETURNS TRIGGER AS $$
 410 BEGIN
 411   DELETE FROM action.hold_copy_map WHERE hold = NEW.id;
 412   RETURN NEW;
 413 END;
 414 $$ LANGUAGE PLPGSQL;
 415
 416 CREATE TRIGGER hold_request_clear_map_tgr
 417     AFTER UPDATE ON action.hold_request
 418     FOR EACH ROW
 419     WHEN (
 420         (NEW.cancel_time IS NOT NULL AND OLD.cancel_time IS NULL)
 421         OR (NEW.fulfillment_time IS NOT NULL AND OLD.fulfillment_time IS NULL)
 422     )
 423     EXECUTE PROCEDURE action.hold_request_clear_map();
 424
 425 CREATE INDEX hold_request_target_idx ON action.hold_request (target);
 426 CREATE INDEX hold_request_usr_idx ON action.hold_request (usr);
 427 CREATE INDEX hold_request_pickup_lib_idx ON action.hold_request (pickup_lib);
 428 CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy);
 429 CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time);
 430 CREATE INDEX hold_request_fulfillment_staff_idx ON action.hold_request ( fulfillment_staff );
 431 CREATE INDEX hold_request_requestor_idx         ON action.hold_request ( requestor );
 432 CREATE INDEX hold_request_open_idx ON action.hold_request (id) WHERE cancel_time IS NULL AND fulfillment_time IS NULL;
 433 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;
 434 CREATE UNIQUE INDEX hold_request_capture_protect_idx ON action.hold_request (current_copy) WHERE current_copy IS NOT NULL AND capture_time IS NOT NULL AND cancel_time IS NULL AND fulfillment_time IS NULL;
 435 CREATE INDEX hold_request_copy_capture_time_idx ON action.hold_request (current_copy,capture_time);
 436 CREATE INDEX hold_request_open_captured_shelf_lib_idx ON action.hold_request (current_shelf_lib) WHERE capture_time IS NOT NULL AND fulfillment_time IS NULL AND (pickup_lib <> current_shelf_lib);
 437 CREATE INDEX hold_fulfillment_time_idx ON action.hold_request (fulfillment_time) WHERE fulfillment_time IS NOT NULL;
 438 CREATE INDEX hold_request_time_idx ON action.hold_request (request_time);
 439
 440
 441 CREATE TABLE action.hold_request_note (
 442
 443     id     BIGSERIAL PRIMARY KEY,
 444     hold   BIGINT    NOT NULL REFERENCES action.hold_request (id)
 445                               ON DELETE CASCADE
 446                               DEFERRABLE INITIALLY DEFERRED,
 447     title  TEXT      NOT NULL,
 448     body   TEXT      NOT NULL,
 449     slip   BOOL      NOT NULL DEFAULT FALSE,
 450     pub    BOOL      NOT NULL DEFAULT FALSE,
 451     staff  BOOL      NOT NULL DEFAULT FALSE  -- created by staff
 452
 453 );
 454 CREATE INDEX ahrn_hold_idx ON action.hold_request_note (hold);
 455
 456
 457 CREATE TABLE action.hold_notification (
 458         id              SERIAL                          PRIMARY KEY,
 459         hold            INT                             NOT NULL REFERENCES action.hold_request (id)
 460                                                                         ON DELETE CASCADE
 461                                                                         DEFERRABLE INITIALLY DEFERRED,
 462         notify_staff    INT                     REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 463         notify_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 464         method          TEXT                            NOT NULL, -- email address or phone number
 465         note            TEXT
 466 );
 467 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
 468 CREATE INDEX ahn_notify_staff_idx ON action.hold_notification ( notify_staff );
 469
 470 CREATE TABLE action.hold_copy_map (
 471         id              BIGSERIAL       PRIMARY KEY,
 472         hold            INT     NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 473         target_copy     BIGINT  NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
 474         proximity       NUMERIC,
 475         CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
 476 );
 477 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
 478 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
 479
 480 CREATE OR REPLACE FUNCTION
 481     action.hold_request_regen_copy_maps(
 482         hold_id INTEGER, copy_ids INTEGER[]) RETURNS VOID AS $$
 483     DELETE FROM action.hold_copy_map WHERE hold = $1;
 484     INSERT INTO action.hold_copy_map (hold, target_copy) SELECT $1, UNNEST($2);
 485 $$ LANGUAGE SQL;
 486
 487 CREATE TABLE action.transit_copy (
 488         id                      SERIAL                          PRIMARY KEY,
 489         source_send_time        TIMESTAMP WITH TIME ZONE,
 490         dest_recv_time          TIMESTAMP WITH TIME ZONE,
 491         target_copy             BIGINT                          NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
 492         source                  INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 493         dest                    INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 494         prev_hop                INT                             REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED,
 495         copy_status             INT                             NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
 496         persistant_transfer     BOOL                            NOT NULL DEFAULT FALSE,
 497         prev_dest               INT                             REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 498         cancel_time             TIMESTAMP WITH TIME ZONE
 499 );
 500 CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest);
 501 CREATE INDEX active_transit_source_idx ON "action".transit_copy (source);
 502 CREATE INDEX active_transit_cp_idx ON "action".transit_copy (target_copy);
 503
 504
 505 CREATE TABLE action.hold_transit_copy (
 506         hold    INT     REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
 507 ) INHERITS (action.transit_copy);
 508 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id);
 509 -- 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
 510 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest);
 511 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
 512 CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy);
 513 CREATE INDEX hold_transit_copy_hold_idx on action.hold_transit_copy (hold);
 514
 515
 516 CREATE TABLE action.unfulfilled_hold_list (
 517         id              BIGSERIAL                       PRIMARY KEY,
 518         current_copy    BIGINT                          NOT NULL,
 519         hold            INT                             NOT NULL,
 520         circ_lib        INT                             NOT NULL,
 521         fail_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
 522 );
 523 CREATE INDEX uhr_hold_idx ON action.unfulfilled_hold_list (hold);
 524
 525 CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS
 526     SELECT  u.hold,
 527             c.circ_lib,
 528             count(*)
 529       FROM  action.unfulfilled_hold_list u
 530             JOIN asset.copy c ON (c.id = u.current_copy)
 531       GROUP BY 1,2;
 532
 533 CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS
 534     SELECT  hold,
 535             min(count)
 536       FROM  action.unfulfilled_hold_loops
 537       GROUP BY 1;
 538
 539 CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS
 540     SELECT  DISTINCT l.*
 541       FROM  action.unfulfilled_hold_loops l
 542             JOIN action.unfulfilled_hold_min_loop m USING (hold)
 543       WHERE l.count = m.min;
 544
 545 CREATE VIEW action.unfulfilled_hold_max_loop AS
 546     SELECT  hold,
 547             max(count) AS max
 548       FROM  action.unfulfilled_hold_loops
 549       GROUP BY 1;
 550
 551
 552 CREATE TABLE action.aged_hold_request (
 553     usr_post_code               TEXT,
 554     usr_home_ou         INT     NOT NULL,
 555     usr_profile         INT     NOT NULL,
 556     usr_birth_year              INT,
 557     staff_placed        BOOLEAN NOT NULL,
 558     LIKE action.hold_request
 559 );
 560 ALTER TABLE action.aged_hold_request
 561       ADD PRIMARY KEY (id),
 562       DROP COLUMN usr,
 563       DROP COLUMN requestor,
 564       DROP COLUMN sms_carrier,
 565       ALTER COLUMN phone_notify TYPE BOOLEAN
 566             USING CASE WHEN phone_notify IS NULL OR phone_notify = '' THEN FALSE ELSE TRUE END,
 567       ALTER COLUMN sms_notify TYPE BOOLEAN
 568             USING CASE WHEN sms_notify IS NULL OR sms_notify = '' THEN FALSE ELSE TRUE END,
 569       ALTER COLUMN phone_notify SET NOT NULL,
 570       ALTER COLUMN sms_notify SET NOT NULL;
 571 CREATE INDEX aged_hold_request_target_idx ON action.aged_hold_request (target);
 572 CREATE INDEX aged_hold_request_pickup_lib_idx ON action.aged_hold_request (pickup_lib);
 573 CREATE INDEX aged_hold_request_current_copy_idx ON action.aged_hold_request (current_copy);
 574 CREATE INDEX aged_hold_request_fulfillment_staff_idx ON action.aged_hold_request ( fulfillment_staff );
 575
 576 CREATE OR REPLACE VIEW action.all_hold_request AS
 577     SELECT DISTINCT
 578            COALESCE(a.post_code, b.post_code) AS usr_post_code,
 579            p.home_ou AS usr_home_ou,
 580            p.profile AS usr_profile,
 581            EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
 582            CAST(ahr.requestor <> ahr.usr AS BOOLEAN) AS staff_placed,
 583            ahr.id,
 584            ahr.request_time,
 585            ahr.capture_time,
 586            ahr.fulfillment_time,
 587            ahr.checkin_time,
 588            ahr.return_time,
 589            ahr.prev_check_time,
 590            ahr.expire_time,
 591            ahr.cancel_time,
 592            ahr.cancel_cause,
 593            ahr.cancel_note,
 594            ahr.target,
 595            ahr.current_copy,
 596            ahr.fulfillment_staff,
 597            ahr.fulfillment_lib,
 598            ahr.request_lib,
 599            ahr.selection_ou,
 600            ahr.selection_depth,
 601            ahr.pickup_lib,
 602            ahr.hold_type,
 603            ahr.holdable_formats,
 604            CASE
 605            WHEN ahr.phone_notify IS NULL THEN FALSE
 606            WHEN ahr.phone_notify = '' THEN FALSE
 607            ELSE TRUE
 608            END AS phone_notify,
 609            ahr.email_notify,
 610            CASE
 611            WHEN ahr.sms_notify IS NULL THEN FALSE
 612            WHEN ahr.sms_notify = '' THEN FALSE
 613            ELSE TRUE
 614            END AS sms_notify,
 615            ahr.frozen,
 616            ahr.thaw_date,
 617            ahr.shelf_time,
 618            ahr.cut_in_line,
 619            ahr.mint_condition,
 620            ahr.shelf_expire_time,
 621            ahr.current_shelf_lib,
 622            ahr.behind_desk
 623     FROM action.hold_request ahr
 624          JOIN actor.usr p ON (ahr.usr = p.id)
 625          LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
 626          LEFT JOIN actor.usr_address b ON (p.billing_address = b.id)
 627     UNION ALL
 628     SELECT
 629            usr_post_code,
 630            usr_home_ou,
 631            usr_profile,
 632            usr_birth_year,
 633            staff_placed,
 634            id,
 635            request_time,
 636            capture_time,
 637            fulfillment_time,
 638            checkin_time,
 639            return_time,
 640            prev_check_time,
 641            expire_time,
 642            cancel_time,
 643            cancel_cause,
 644            cancel_note,
 645            target,
 646            current_copy,
 647            fulfillment_staff,
 648            fulfillment_lib,
 649            request_lib,
 650            selection_ou,
 651            selection_depth,
 652            pickup_lib,
 653            hold_type,
 654            holdable_formats,
 655            phone_notify,
 656            email_notify,
 657            sms_notify,
 658            frozen,
 659            thaw_date,
 660            shelf_time,
 661            cut_in_line,
 662            mint_condition,
 663            shelf_expire_time,
 664            current_shelf_lib,
 665            behind_desk
 666     FROM action.aged_hold_request;
 667
 668 CREATE OR REPLACE FUNCTION action.age_hold_on_delete () RETURNS TRIGGER AS $$
 669 DECLARE
 670 BEGIN
 671     -- Archive a copy of the old row to action.aged_hold_request
 672
 673     INSERT INTO action.aged_hold_request
 674            (usr_post_code,
 675             usr_home_ou,
 676             usr_profile,
 677             usr_birth_year,
 678             staff_placed,
 679             id,
 680             request_time,
 681             capture_time,
 682             fulfillment_time,
 683             checkin_time,
 684             return_time,
 685             prev_check_time,
 686             expire_time,
 687             cancel_time,
 688             cancel_cause,
 689             cancel_note,
 690             target,
 691             current_copy,
 692             fulfillment_staff,
 693             fulfillment_lib,
 694             request_lib,
 695             selection_ou,
 696             selection_depth,
 697             pickup_lib,
 698             hold_type,
 699             holdable_formats,
 700             phone_notify,
 701             email_notify,
 702             sms_notify,
 703             frozen,
 704             thaw_date,
 705             shelf_time,
 706             cut_in_line,
 707             mint_condition,
 708             shelf_expire_time,
 709             current_shelf_lib,
 710             behind_desk)
 711       SELECT
 712            usr_post_code,
 713            usr_home_ou,
 714            usr_profile,
 715            usr_birth_year,
 716            staff_placed,
 717            id,
 718            request_time,
 719            capture_time,
 720            fulfillment_time,
 721            checkin_time,
 722            return_time,
 723            prev_check_time,
 724            expire_time,
 725            cancel_time,
 726            cancel_cause,
 727            cancel_note,
 728            target,
 729            current_copy,
 730            fulfillment_staff,
 731            fulfillment_lib,
 732            request_lib,
 733            selection_ou,
 734            selection_depth,
 735            pickup_lib,
 736            hold_type,
 737            holdable_formats,
 738            phone_notify,
 739            email_notify,
 740            sms_notify,
 741            frozen,
 742            thaw_date,
 743            shelf_time,
 744            cut_in_line,
 745            mint_condition,
 746            shelf_expire_time,
 747            current_shelf_lib,
 748            behind_desk
 749         FROM action.all_hold_request WHERE id = OLD.id;
 750
 751     RETURN OLD;
 752 END;
 753 $$ LANGUAGE 'plpgsql';
 754
 755 CREATE TRIGGER action_hold_request_aging_tgr
 756         BEFORE DELETE ON action.hold_request
 757         FOR EACH ROW
 758         EXECUTE PROCEDURE action.age_hold_on_delete ();
 759
 760 CREATE TABLE action.fieldset (
 761     id              SERIAL          PRIMARY KEY,
 762     owner           INT             NOT NULL REFERENCES actor.usr (id)
 763                                     DEFERRABLE INITIALLY DEFERRED,
 764         owning_lib      INT             NOT NULL REFERENCES actor.org_unit (id)
 765                                     DEFERRABLE INITIALLY DEFERRED,
 766         status          TEXT            NOT NULL
 767                                         CONSTRAINT valid_status CHECK ( status in
 768                                                                         ( 'PENDING', 'APPLIED', 'ERROR' )),
 769     creation_time   TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
 770     scheduled_time  TIMESTAMPTZ,
 771     applied_time    TIMESTAMPTZ,
 772     classname       TEXT            NOT NULL, -- an IDL class name
 773     name            TEXT            NOT NULL,
 774     stored_query    INT             REFERENCES query.stored_query (id)
 775                                     DEFERRABLE INITIALLY DEFERRED,
 776     pkey_value      TEXT,
 777         CONSTRAINT lib_name_unique UNIQUE (owning_lib, name),
 778     CONSTRAINT fieldset_one_or_the_other CHECK (
 779         (stored_query IS NOT NULL AND pkey_value IS NULL) OR
 780         (pkey_value IS NOT NULL AND stored_query IS NULL)
 781     )
 782         -- the CHECK constraint means we can update the fields for a single
 783         -- row without all the extra overhead involved in a query
 784 );
 785
 786 CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time );
 787 CREATE INDEX action_owner_idx               ON action.fieldset( owner );
 788
 789
 790 CREATE TABLE action.fieldset_col_val (
 791     id              SERIAL  PRIMARY KEY,
 792     fieldset        INT     NOT NULL REFERENCES action.fieldset
 793                                          ON DELETE CASCADE
 794                                          DEFERRABLE INITIALLY DEFERRED,
 795     col             TEXT    NOT NULL,  -- "field" from the idl ... the column on the table
 796     val             TEXT,              -- value for the column ... NULL means, well, NULL
 797     CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col)
 798 );
 799
 800
 801 -- represents a circ chain summary
 802 CREATE TYPE action.circ_chain_summary AS (
 803     num_circs INTEGER,
 804     start_time TIMESTAMP WITH TIME ZONE,
 805     checkout_workstation TEXT,
 806     last_renewal_time TIMESTAMP WITH TIME ZONE, -- NULL if no renewals
 807     last_stop_fines TEXT,
 808     last_stop_fines_time TIMESTAMP WITH TIME ZONE,
 809     last_renewal_workstation TEXT, -- NULL if no renewals
 810     last_checkin_workstation TEXT,
 811     last_checkin_time TIMESTAMP WITH TIME ZONE,
 812     last_checkin_scan_time TIMESTAMP WITH TIME ZONE
 813 );
 814
 815
 816 CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id BIGINT ) RETURNS SETOF action.circulation AS $$
 817 DECLARE
 818     tmp_circ action.circulation%ROWTYPE;
 819     circ_0 action.circulation%ROWTYPE;
 820 BEGIN
 821
 822     SELECT INTO tmp_circ * FROM action.circulation WHERE id = ctx_circ_id;
 823
 824     IF tmp_circ IS NULL THEN
 825         RETURN NEXT tmp_circ;
 826     END IF;
 827     circ_0 := tmp_circ;
 828
 829     -- find the front of the chain
 830     WHILE TRUE LOOP
 831         SELECT INTO tmp_circ * FROM action.circulation WHERE id = tmp_circ.parent_circ;
 832         IF tmp_circ IS NULL THEN
 833             EXIT;
 834         END IF;
 835         circ_0 := tmp_circ;
 836     END LOOP;
 837
 838     -- now send the circs to the caller, oldest to newest
 839     tmp_circ := circ_0;
 840     WHILE TRUE LOOP
 841         IF tmp_circ IS NULL THEN
 842             EXIT;
 843         END IF;
 844         RETURN NEXT tmp_circ;
 845         SELECT INTO tmp_circ * FROM action.circulation WHERE parent_circ = tmp_circ.id;
 846     END LOOP;
 847
 848 END;
 849 $$ LANGUAGE 'plpgsql';
 850
 851 CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id BIGINT ) RETURNS action.circ_chain_summary AS $$
 852
 853 DECLARE
 854
 855     -- first circ in the chain
 856     circ_0 action.circulation%ROWTYPE;
 857
 858     -- last circ in the chain
 859     circ_n action.circulation%ROWTYPE;
 860
 861     -- circ chain under construction
 862     chain action.circ_chain_summary;
 863     tmp_circ action.circulation%ROWTYPE;
 864
 865 BEGIN
 866
 867     chain.num_circs := 0;
 868     FOR tmp_circ IN SELECT * FROM action.circ_chain(ctx_circ_id) LOOP
 869
 870         IF chain.num_circs = 0 THEN
 871             circ_0 := tmp_circ;
 872         END IF;
 873
 874         chain.num_circs := chain.num_circs + 1;
 875         circ_n := tmp_circ;
 876     END LOOP;
 877
 878     chain.start_time := circ_0.xact_start;
 879     chain.last_stop_fines := circ_n.stop_fines;
 880     chain.last_stop_fines_time := circ_n.stop_fines_time;
 881     chain.last_checkin_time := circ_n.checkin_time;
 882     chain.last_checkin_scan_time := circ_n.checkin_scan_time;
 883     SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
 884     SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
 885
 886     IF chain.num_circs > 1 THEN
 887         chain.last_renewal_time := circ_n.xact_start;
 888         SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
 889     END IF;
 890
 891     RETURN chain;
 892
 893 END;
 894 $$ LANGUAGE 'plpgsql';
 895
 896 -- same as action.circ_chain, but returns action.all_circulation
 897 -- rows which may include aged circulations.
 898 CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER)
 899     RETURNS SETOF action.all_circulation AS $$
 900 DECLARE
 901     tmp_circ action.all_circulation%ROWTYPE;
 902     circ_0 action.all_circulation%ROWTYPE;
 903 BEGIN
 904
 905     SELECT INTO tmp_circ * FROM action.all_circulation WHERE id = ctx_circ_id;
 906
 907     IF tmp_circ IS NULL THEN
 908         RETURN NEXT tmp_circ;
 909     END IF;
 910     circ_0 := tmp_circ;
 911
 912     -- find the front of the chain
 913     WHILE TRUE LOOP
 914         SELECT INTO tmp_circ * FROM action.all_circulation
 915             WHERE id = tmp_circ.parent_circ;
 916         IF tmp_circ IS NULL THEN
 917             EXIT;
 918         END IF;
 919         circ_0 := tmp_circ;
 920     END LOOP;
 921
 922     -- now send the circs to the caller, oldest to newest
 923     tmp_circ := circ_0;
 924     WHILE TRUE LOOP
 925         IF tmp_circ IS NULL THEN
 926             EXIT;
 927         END IF;
 928         RETURN NEXT tmp_circ;
 929         SELECT INTO tmp_circ * FROM action.all_circulation
 930             WHERE parent_circ = tmp_circ.id;
 931     END LOOP;
 932
 933 END;
 934 $$ LANGUAGE 'plpgsql';
 935
 936 -- same as action.summarize_circ_chain, but returns data collected
 937 -- from action.all_circulation, which may include aged circulations.
 938 CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain
 939     (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
 940
 941 DECLARE
 942
 943     -- first circ in the chain
 944     circ_0 action.all_circulation%ROWTYPE;
 945
 946     -- last circ in the chain
 947     circ_n action.all_circulation%ROWTYPE;
 948
 949     -- circ chain under construction
 950     chain action.circ_chain_summary;
 951     tmp_circ action.all_circulation%ROWTYPE;
 952
 953 BEGIN
 954
 955     chain.num_circs := 0;
 956     FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
 957
 958         IF chain.num_circs = 0 THEN
 959             circ_0 := tmp_circ;
 960         END IF;
 961
 962         chain.num_circs := chain.num_circs + 1;
 963         circ_n := tmp_circ;
 964     END LOOP;
 965
 966     chain.start_time := circ_0.xact_start;
 967     chain.last_stop_fines := circ_n.stop_fines;
 968     chain.last_stop_fines_time := circ_n.stop_fines_time;
 969     chain.last_checkin_time := circ_n.checkin_time;
 970     chain.last_checkin_scan_time := circ_n.checkin_scan_time;
 971     SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
 972     SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
 973
 974     IF chain.num_circs > 1 THEN
 975         chain.last_renewal_time := circ_n.xact_start;
 976         SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
 977     END IF;
 978
 979     RETURN chain;
 980
 981 END;
 982 $$ LANGUAGE 'plpgsql';
 983
 984
 985 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
 986 DECLARE
 987     h               action.hold_request%ROWTYPE;
 988     view_age        INTERVAL;
 989     view_count      INT;
 990     usr_view_count  actor.usr_setting%ROWTYPE;
 991     usr_view_age    actor.usr_setting%ROWTYPE;
 992     usr_view_start  actor.usr_setting%ROWTYPE;
 993 BEGIN
 994     SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
 995     SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
 996     SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
 997
 998     FOR h IN
 999         SELECT  *
1000           FROM  action.hold_request
1001           WHERE usr = usr_id
1002                 AND fulfillment_time IS NULL
1003                 AND cancel_time IS NULL
1004           ORDER BY request_time DESC
1005     LOOP
1006         RETURN NEXT h;
1007     END LOOP;
1008
1009     IF usr_view_start.value IS NULL THEN
1010         RETURN;
1011     END IF;
1012
1013     IF usr_view_age.value IS NOT NULL THEN
1014         -- User opted in and supplied a retention age
1015         IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
1016             view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
1017         ELSE
1018             view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
1019         END IF;
1020     ELSE
1021         -- User opted in
1022         view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
1023     END IF;
1024
1025     IF usr_view_count.value IS NOT NULL THEN
1026         view_count := oils_json_to_text(usr_view_count.value)::INT;
1027     ELSE
1028         view_count := 1000;
1029     END IF;
1030
1031     -- show some fulfilled/canceled holds
1032     FOR h IN
1033         SELECT  *
1034           FROM  action.hold_request
1035           WHERE usr = usr_id
1036                 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
1037                 AND COALESCE(fulfillment_time, cancel_time) > NOW() - view_age
1038           ORDER BY COALESCE(fulfillment_time, cancel_time) DESC
1039           LIMIT view_count
1040     LOOP
1041         RETURN NEXT h;
1042     END LOOP;
1043
1044     RETURN;
1045 END;
1046 $func$ LANGUAGE PLPGSQL;
1047
1048 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
1049 DECLARE
1050     org_keep_age    INTERVAL;
1051     org_use_last    BOOL = false;
1052     org_age_is_min  BOOL = false;
1053     org_keep_count  INT;
1054
1055     keep_age        INTERVAL;
1056
1057     target_acp      RECORD;
1058     circ_chain_head action.circulation%ROWTYPE;
1059     circ_chain_tail action.circulation%ROWTYPE;
1060
1061     count_purged    INT;
1062     num_incomplete  INT;
1063
1064     last_finished   TIMESTAMP WITH TIME ZONE;
1065 BEGIN
1066
1067     count_purged := 0;
1068
1069     SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
1070
1071     SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
1072     IF org_keep_count IS NULL THEN
1073         RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
1074     END IF;
1075
1076     SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished';
1077     SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min';
1078
1079     -- First, find copies with more than keep_count non-renewal circs
1080     FOR target_acp IN
1081         SELECT  target_copy,
1082                 COUNT(*) AS total_real_circs
1083           FROM  action.circulation
1084           WHERE parent_circ IS NULL
1085                 AND xact_finish IS NOT NULL
1086           GROUP BY target_copy
1087           HAVING COUNT(*) > org_keep_count
1088     LOOP
1089         -- And, for those, select circs that are finished and older than keep_age
1090         FOR circ_chain_head IN
1091             -- For reference, the subquery uses a window function to order the circs newest to oldest and number them
1092             -- The outer query then uses that information to skip the most recent set the library wants to keep
1093             -- End result is we don't care what order they come out in, as they are all potentials for deletion.
1094             SELECT ac.* FROM action.circulation ac JOIN (
1095               SELECT  rank() OVER (ORDER BY xact_start DESC), ac.id
1096                 FROM  action.circulation ac
1097                 WHERE ac.target_copy = target_acp.target_copy
1098                   AND ac.parent_circ IS NULL
1099                 ORDER BY ac.xact_start ) ranked USING (id)
1100                 WHERE ranked.rank > org_keep_count
1101         LOOP
1102
1103             SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
1104             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);
1105             CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0;
1106
1107             IF NOT org_use_last THEN
1108                 last_finished := circ_chain_tail.xact_finish;
1109             END IF;
1110
1111             keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL );
1112
1113             IF org_age_is_min THEN
1114                 keep_age := GREATEST( keep_age, org_keep_age );
1115             END IF;
1116
1117             CONTINUE WHEN AGE(NOW(), last_finished) < keep_age;
1118
1119             -- We've passed the purging tests, purge the circ chain starting at the end
1120             -- A trigger should auto-purge the rest of the chain.
1121             DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
1122
1123             count_purged := count_purged + 1;
1124
1125         END LOOP;
1126     END LOOP;
1127
1128     return count_purged;
1129 END;
1130 $func$ LANGUAGE PLPGSQL;
1131
1132 CREATE OR REPLACE FUNCTION action.purge_holds() RETURNS INT AS $func$
1133 DECLARE
1134   current_hold RECORD;
1135   purged_holds INT;
1136   cgf_d INTERVAL;
1137   cgf_f INTERVAL;
1138   cgf_c INTERVAL;
1139   prev_usr INT;
1140   user_start TIMESTAMPTZ;
1141   user_age INTERVAL;
1142   user_count INT;
1143 BEGIN
1144   purged_holds := 0;
1145   SELECT INTO cgf_d value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age' AND enabled;
1146   SELECT INTO cgf_f value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_fulfilled' AND enabled;
1147   SELECT INTO cgf_c value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_canceled' AND enabled;
1148   FOR current_hold IN
1149     SELECT
1150       rank() OVER (PARTITION BY usr ORDER BY COALESCE(fulfillment_time, cancel_time) DESC),
1151       cgf_cs.value::INTERVAL as cgf_cs,
1152       ahr.*
1153     FROM
1154       action.hold_request ahr
1155       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)
1156     WHERE
1157       (fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL)
1158   LOOP
1159     IF prev_usr IS NULL OR prev_usr != current_hold.usr THEN
1160       prev_usr := current_hold.usr;
1161       SELECT INTO user_start oils_json_to_text(value)::TIMESTAMPTZ FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_start';
1162       SELECT INTO user_age oils_json_to_text(value)::INTERVAL FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_age';
1163       SELECT INTO user_count oils_json_to_text(value)::INT FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_count';
1164       IF user_start IS NOT NULL THEN
1165         user_age := LEAST(user_age, AGE(NOW(), user_start));
1166       END IF;
1167       IF user_count IS NULL THEN
1168         user_count := 1000; -- Assumption based on the user visible holds routine
1169       END IF;
1170     END IF;
1171     -- Library keep age trumps user keep anything, for purposes of being able to hold on to things when staff canceled and such.
1172     IF current_hold.fulfillment_time IS NOT NULL AND current_hold.fulfillment_time > NOW() - COALESCE(cgf_f, cgf_d) THEN
1173       CONTINUE;
1174     END IF;
1175     IF current_hold.cancel_time IS NOT NULL AND current_hold.cancel_time > NOW() - COALESCE(current_hold.cgf_cs, cgf_c, cgf_d) THEN
1176       CONTINUE;
1177     END IF;
1178
1179     -- User keep age needs combining with count. If too old AND within the count, keep!
1180     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
1181       CONTINUE;
1182     END IF;
1183
1184     -- All checks should have passed, delete!
1185     DELETE FROM action.hold_request WHERE id = current_hold.id;
1186     purged_holds := purged_holds + 1;
1187   END LOOP;
1188   RETURN purged_holds;
1189 END;
1190 $func$ LANGUAGE plpgsql;
1191
1192 CREATE OR REPLACE FUNCTION action.apply_fieldset(
1193         fieldset_id IN INT,        -- id from action.fieldset
1194         table_name  IN TEXT,       -- table to be updated
1195         pkey_name   IN TEXT,       -- name of primary key column in that table
1196         query       IN TEXT        -- query constructed by qstore (for query-based
1197                                    --    fieldsets only; otherwise null
1198 )
1199 RETURNS TEXT AS $$
1200 DECLARE
1201         statement TEXT;
1202         fs_status TEXT;
1203         fs_pkey_value TEXT;
1204         fs_query TEXT;
1205         sep CHAR;
1206         status_code TEXT;
1207         msg TEXT;
1208         update_count INT;
1209         cv RECORD;
1210 BEGIN
1211         -- Sanity checks
1212         IF fieldset_id IS NULL THEN
1213                 RETURN 'Fieldset ID parameter is NULL';
1214         END IF;
1215         IF table_name IS NULL THEN
1216                 RETURN 'Table name parameter is NULL';
1217         END IF;
1218         IF pkey_name IS NULL THEN
1219                 RETURN 'Primary key name parameter is NULL';
1220         END IF;
1221         --
1222         statement := 'UPDATE ' || table_name || ' SET';
1223         --
1224         SELECT
1225                 status,
1226                 quote_literal( pkey_value )
1227         INTO
1228                 fs_status,
1229                 fs_pkey_value
1230         FROM
1231                 action.fieldset
1232         WHERE
1233                 id = fieldset_id;
1234         --
1235         IF fs_status IS NULL THEN
1236                 RETURN 'No fieldset found for id = ' || fieldset_id;
1237         ELSIF fs_status = 'APPLIED' THEN
1238                 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
1239         END IF;
1240         --
1241         sep := '';
1242         FOR cv IN
1243                 SELECT  col,
1244                                 val
1245                 FROM    action.fieldset_col_val
1246                 WHERE   fieldset = fieldset_id
1247         LOOP
1248                 statement := statement || sep || ' ' || cv.col
1249                                          || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
1250                 sep := ',';
1251         END LOOP;
1252         --
1253         IF sep = '' THEN
1254                 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
1255         END IF;
1256         --
1257         -- Add the WHERE clause.  This differs according to whether it's a
1258         -- single-row fieldset or a query-based fieldset.
1259         --
1260         IF query IS NULL        AND fs_pkey_value IS NULL THEN
1261                 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
1262         ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
1263             fs_query := rtrim( query, ';' );
1264             statement := statement || ' WHERE ' || pkey_name || ' IN ( '
1265                          || fs_query || ' );';
1266         ELSIF query IS NULL     AND fs_pkey_value IS NOT NULL THEN
1267                 statement := statement || ' WHERE ' || pkey_name || ' = '
1268                                      || fs_pkey_value || ';';
1269         ELSE  -- both are not null
1270                 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
1271         END IF;
1272         --
1273         -- Execute the update
1274         --
1275         BEGIN
1276                 EXECUTE statement;
1277                 GET DIAGNOSTICS update_count = ROW_COUNT;
1278                 --
1279                 IF UPDATE_COUNT > 0 THEN
1280                         status_code := 'APPLIED';
1281                         msg := NULL;
1282                 ELSE
1283                         status_code := 'ERROR';
1284                         msg := 'No eligible rows found for fieldset ' || fieldset_id;
1285         END IF;
1286         EXCEPTION WHEN OTHERS THEN
1287                 status_code := 'ERROR';
1288                 msg := 'Unable to apply fieldset ' || fieldset_id
1289                            || ': ' || sqlerrm;
1290         END;
1291         --
1292         -- Update fieldset status
1293         --
1294         UPDATE action.fieldset
1295         SET status       = status_code,
1296             applied_time = now()
1297         WHERE id = fieldset_id;
1298         --
1299         RETURN msg;
1300 END;
1301 $$ LANGUAGE plpgsql;
1302
1303 COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$
1304 Applies a specified fieldset, using a supplied table name and primary
1305 key name.  The query parameter should be non-null only for
1306 query-based fieldsets.
1307
1308 Returns NULL if successful, or an error message if not.
1309 $$;
1310
1311 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
1312     ahr_id INT,
1313     acp_id BIGINT,
1314     copy_context_ou INT DEFAULT NULL
1315     -- TODO maybe? hold_context_ou INT DEFAULT NULL.  This would optionally
1316     -- support an "ahprox" measurement: adjust prox between copy circ lib and
1317     -- hold request lib, but I'm unsure whether to use this theoretical
1318     -- argument only in the baseline calculation or later in the other
1319     -- queries in this function.
1320 ) RETURNS NUMERIC AS $f$
1321 DECLARE
1322     aoupa           actor.org_unit_proximity_adjustment%ROWTYPE;
1323     ahr             action.hold_request%ROWTYPE;
1324     acp             asset.copy%ROWTYPE;
1325     acn             asset.call_number%ROWTYPE;
1326     acl             asset.copy_location%ROWTYPE;
1327     baseline_prox   NUMERIC;
1328
1329     icl_list        INT[];
1330     iol_list        INT[];
1331     isl_list        INT[];
1332     hpl_list        INT[];
1333     hrl_list        INT[];
1334
1335 BEGIN
1336
1337     SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
1338     SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
1339     SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
1340     SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
1341
1342     IF copy_context_ou IS NULL THEN
1343         copy_context_ou := acp.circ_lib;
1344     END IF;
1345
1346     -- First, gather the baseline proximity of "here" to pickup lib
1347     SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = copy_context_ou AND to_org = ahr.pickup_lib;
1348
1349     -- Find any absolute adjustments, and set the baseline prox to that
1350     SELECT  adj.* INTO aoupa
1351       FROM  actor.org_unit_proximity_adjustment adj
1352             LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1353             LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1354             LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acl_ol.id = adj.copy_location)
1355             LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1356             LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1357       WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1358             (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1359             (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1360             (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1361             (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1362             (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1363             absolute_adjustment AND
1364             COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1365       ORDER BY
1366             COALESCE(acp_cl.distance,999)
1367                 + COALESCE(acn_ol.distance,999)
1368                 + COALESCE(acl_ol.distance,999)
1369                 + COALESCE(ahr_pl.distance,999)
1370                 + COALESCE(ahr_rl.distance,999),
1371             adj.pos
1372       LIMIT 1;
1373
1374     IF FOUND THEN
1375         baseline_prox := aoupa.prox_adjustment;
1376     END IF;
1377
1378     -- Now find any relative adjustments, and change the baseline prox based on them
1379     FOR aoupa IN
1380         SELECT  adj.*
1381           FROM  actor.org_unit_proximity_adjustment adj
1382                 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1383                 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1384                 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
1385                 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1386                 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1387           WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1388                 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1389                 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1390                 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1391                 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1392                 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1393                 NOT absolute_adjustment AND
1394                 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1395     LOOP
1396         baseline_prox := baseline_prox + aoupa.prox_adjustment;
1397     END LOOP;
1398
1399     RETURN baseline_prox;
1400 END;
1401 $f$ LANGUAGE PLPGSQL;
1402
1403 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity_update () RETURNS TRIGGER AS $f$
1404 BEGIN
1405     NEW.proximity := action.hold_copy_calculated_proximity(NEW.hold,NEW.target_copy);
1406     RETURN NEW;
1407 END;
1408 $f$ LANGUAGE PLPGSQL;
1409
1410 CREATE TRIGGER hold_copy_proximity_update_tgr BEFORE INSERT OR UPDATE ON action.hold_copy_map FOR EACH ROW EXECUTE PROCEDURE action.hold_copy_calculated_proximity_update ();
1411
1412 CREATE TABLE action.usr_circ_history (
1413     id           BIGSERIAL PRIMARY KEY,
1414     usr          INTEGER NOT NULL REFERENCES actor.usr(id)
1415                  DEFERRABLE INITIALLY DEFERRED,
1416     xact_start   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1417     target_copy  BIGINT NOT NULL, -- asset.copy.id / serial.unit.id
1418     due_date     TIMESTAMP WITH TIME ZONE NOT NULL,
1419     checkin_time TIMESTAMP WITH TIME ZONE,
1420     source_circ  BIGINT REFERENCES action.circulation(id)
1421                  ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
1422 );
1423
1424 CREATE INDEX action_usr_circ_history_usr_idx ON action.usr_circ_history ( usr );
1425
1426 CREATE TRIGGER action_usr_circ_history_target_copy_trig
1427     AFTER INSERT OR UPDATE ON action.usr_circ_history
1428     FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy');
1429
1430 CREATE OR REPLACE FUNCTION action.maintain_usr_circ_history()
1431     RETURNS TRIGGER AS $FUNK$
1432 DECLARE
1433     cur_circ  BIGINT;
1434     first_circ BIGINT;
1435 BEGIN
1436
1437     -- Any retention value signifies history is enabled.
1438     -- This assumes that clearing these values via external
1439     -- process deletes the action.usr_circ_history rows.
1440     -- TODO: replace these settings w/ a single bool setting?
1441     PERFORM 1 FROM actor.usr_setting
1442         WHERE usr = NEW.usr AND value IS NOT NULL AND name IN (
1443             'history.circ.retention_age',
1444             'history.circ.retention_start'
1445         );
1446
1447     IF NOT FOUND THEN
1448         RETURN NEW;
1449     END IF;
1450
1451     IF TG_OP = 'INSERT' AND NEW.parent_circ IS NULL THEN
1452         -- Starting a new circulation.  Insert the history row.
1453         INSERT INTO action.usr_circ_history
1454             (usr, xact_start, target_copy, due_date, source_circ)
1455         VALUES (
1456             NEW.usr,
1457             NEW.xact_start,
1458             NEW.target_copy,
1459             NEW.due_date,
1460             NEW.id
1461         );
1462
1463         RETURN NEW;
1464     END IF;
1465
1466     -- find the first and last circs in the circ chain
1467     -- for the currently modified circ.
1468     FOR cur_circ IN SELECT id FROM action.circ_chain(NEW.id) LOOP
1469         IF first_circ IS NULL THEN
1470             first_circ := cur_circ;
1471             CONTINUE;
1472         END IF;
1473         -- Allow the loop to continue so that at as the loop
1474         -- completes cur_circ points to the final circulation.
1475     END LOOP;
1476
1477     IF NEW.id <> cur_circ THEN
1478         -- Modifying an intermediate circ.  Ignore it.
1479         RETURN NEW;
1480     END IF;
1481
1482     -- Update the due_date/checkin_time on the history row if the current
1483     -- circ is the last circ in the chain and an update is warranted.
1484
1485     UPDATE action.usr_circ_history
1486         SET
1487             due_date = NEW.due_date,
1488             checkin_time = NEW.checkin_time
1489         WHERE
1490             source_circ = first_circ
1491             AND (
1492                 due_date <> NEW.due_date OR (
1493                     (checkin_time IS NULL AND NEW.checkin_time IS NOT NULL) OR
1494                     (checkin_time IS NOT NULL AND NEW.checkin_time IS NULL) OR
1495                     (checkin_time <> NEW.checkin_time)
1496                 )
1497             );
1498     RETURN NEW;
1499 END;
1500 $FUNK$ LANGUAGE PLPGSQL;
1501
1502 CREATE TRIGGER maintain_usr_circ_history_tgr
1503     AFTER INSERT OR UPDATE ON action.circulation
1504     FOR EACH ROW EXECUTE PROCEDURE action.maintain_usr_circ_history();
1505
1506 CREATE OR REPLACE VIEW action.all_circulation_combined_types AS
1507  SELECT acirc.id AS id,
1508     acirc.xact_start,
1509     acirc.circ_lib,
1510     acirc.circ_staff,
1511     acirc.create_time,
1512     ac_acirc.circ_modifier AS item_type,
1513     'regular_circ'::text AS circ_type
1514    FROM action.circulation acirc,
1515     asset.copy ac_acirc
1516   WHERE acirc.target_copy = ac_acirc.id
1517 UNION ALL
1518  SELECT ancc.id::BIGINT AS id,
1519     ancc.circ_time AS xact_start,
1520     ancc.circ_lib,
1521     ancc.staff AS circ_staff,
1522     ancc.circ_time AS create_time,
1523     cnct_ancc.name AS item_type,
1524     'non-cat_circ'::text AS circ_type
1525    FROM action.non_cataloged_circulation ancc,
1526     config.non_cataloged_type cnct_ancc
1527   WHERE ancc.item_type = cnct_ancc.id
1528 UNION ALL
1529  SELECT aihu.id::BIGINT AS id,
1530     aihu.use_time AS xact_start,
1531     aihu.org_unit AS circ_lib,
1532     aihu.staff AS circ_staff,
1533     aihu.use_time AS create_time,
1534     ac_aihu.circ_modifier AS item_type,
1535     'in-house_use'::text AS circ_type
1536    FROM action.in_house_use aihu,
1537     asset.copy ac_aihu
1538   WHERE aihu.item = ac_aihu.id
1539 UNION ALL
1540  SELECT ancihu.id::BIGINT AS id,
1541     ancihu.use_time AS xact_start,
1542     ancihu.org_unit AS circ_lib,
1543     ancihu.staff AS circ_staff,
1544     ancihu.use_time AS create_time,
1545     cnct_ancihu.name AS item_type,
1546     'non-cat_circ'::text AS circ_type
1547    FROM action.non_cat_in_house_use ancihu,
1548     config.non_cataloged_type cnct_ancihu
1549   WHERE ancihu.item_type = cnct_ancihu.id
1550 UNION ALL
1551  SELECT aacirc.id AS id,
1552     aacirc.xact_start,
1553     aacirc.circ_lib,
1554     aacirc.circ_staff,
1555     aacirc.create_time,
1556     ac_aacirc.circ_modifier AS item_type,
1557     'aged_circ'::text AS circ_type
1558    FROM action.aged_circulation aacirc,
1559     asset.copy ac_aacirc
1560   WHERE aacirc.target_copy = ac_aacirc.id;
1561
1562 COMMIT;