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