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