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