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