Open-ILS/src/sql/Pg/090.schema.action.sql

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