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