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