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