2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2007-2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
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.
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.
18 DROP SCHEMA IF EXISTS action CASCADE;
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()
31 CREATE INDEX action_in_house_use_staff_idx ON action.in_house_use ( staff );
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()
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 );
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()
51 CREATE INDEX non_cat_in_house_use_staff_idx ON action.non_cat_in_house_use ( staff );
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,
63 description TEXT NOT NULL
65 CREATE UNIQUE INDEX asv_once_per_owner_idx ON action.survey (owner,name);
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
73 CREATE TABLE action.survey_answer (
74 id SERIAL PRIMARY KEY,
75 question INT NOT NULL REFERENCES action.survey_question DEFERRABLE INITIALLY DEFERRED,
79 CREATE SEQUENCE action.survey_response_group_id_seq;
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()
91 CREATE INDEX action_survey_response_usr_idx ON action.survey_response ( usr );
93 CREATE OR REPLACE FUNCTION action.survey_response_answer_date_fixup () RETURNS TRIGGER AS '
95 NEW.answer_date := NOW();
99 CREATE TRIGGER action_survey_response_answer_date_fixup_tgr
100 BEFORE INSERT ON action.survey_response
102 EXECUTE PROCEDURE action.survey_response_answer_date_fixup ();
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,
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,
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)
145 DEFERRABLE INITIALLY DEFERRED,
146 checkin_workstation INT REFERENCES actor.workstation(id)
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;
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');
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 ();
175 CREATE OR REPLACE FUNCTION action.push_circ_due_time () RETURNS TRIGGER AS $$
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;
185 CREATE TRIGGER push_due_date_tgr BEFORE INSERT OR UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.push_circ_due_time();
187 CREATE OR REPLACE FUNCTION action.fill_circ_copy_location () RETURNS TRIGGER AS $$
189 SELECT INTO NEW.copy_location location FROM asset.copy WHERE id = NEW.target_copy;
194 CREATE TRIGGER fill_circ_copy_location_tgr BEFORE INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.fill_circ_copy_location();
196 CREATE OR REPLACE FUNCTION action.archive_stat_cats () RETURNS TRIGGER AS $$
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;
213 CREATE TRIGGER archive_stat_cats_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.archive_stat_cats();
215 CREATE TABLE action.aged_circulation (
217 usr_home_ou INT NOT NULL,
218 usr_profile INT NOT NULL,
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
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);
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,
245 FROM action.aged_circulation
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);
261 CREATE OR REPLACE VIEW action.all_circulation_slim AS
262 SELECT * FROM action.circulation
297 FROM action.aged_circulation
302 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
307 -- If there are any renewals for this circulation, don't archive or delete
308 -- it yet. We'll do so later, when we archive and delete the renewals.
310 SELECT 'Y' INTO found
311 FROM action.circulation
312 WHERE parent_circ = OLD.id
316 RETURN NULL; -- don't delete
319 -- Archive a copy of the old row to action.aged_circulation
321 INSERT INTO action.aged_circulation
322 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
323 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
324 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
325 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
326 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
327 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ)
329 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
330 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
331 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
332 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
333 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
334 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
335 FROM action.all_circulation WHERE id = OLD.id;
339 $$ LANGUAGE 'plpgsql';
341 CREATE TRIGGER action_circulation_aging_tgr
342 BEFORE DELETE ON action.circulation
344 EXECUTE PROCEDURE action.age_circ_on_delete ();
347 CREATE OR REPLACE FUNCTION action.age_parent_circ_on_delete () RETURNS TRIGGER AS $$
350 -- Having deleted a renewal, we can delete the original circulation (or a previous
351 -- renewal, if that's what parent_circ is pointing to). That deletion will trigger
352 -- deletion of any prior parents, etc. recursively.
354 IF OLD.parent_circ IS NOT NULL THEN
355 DELETE FROM action.circulation
356 WHERE id = OLD.parent_circ;
361 $$ LANGUAGE 'plpgsql';
363 CREATE TRIGGER age_parent_circ
364 AFTER DELETE ON action.circulation
366 EXECUTE PROCEDURE action.age_parent_circ_on_delete ();
369 CREATE OR REPLACE VIEW action.open_circulation AS
371 FROM action.circulation
372 WHERE checkin_time IS NULL
376 CREATE OR REPLACE VIEW action.billable_circulations AS
378 FROM action.circulation
379 WHERE xact_finish IS NULL;
381 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
383 IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
384 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
385 UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
387 IF NEW.stop_fines = 'CLAIMSNEVERCHECKEDOUT' THEN
388 UPDATE actor.usr SET claims_never_checked_out_count = claims_never_checked_out_count + 1 WHERE id = NEW.usr;
390 IF NEW.stop_fines = 'LOST' THEN
391 UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
396 $$ LANGUAGE 'plpgsql';
397 CREATE TRIGGER action_circulation_stop_fines_tgr
398 BEFORE UPDATE ON action.circulation
400 EXECUTE PROCEDURE action.circulation_claims_returned ();
402 CREATE TABLE action.hold_request_cancel_cause (
403 id SERIAL PRIMARY KEY,
407 CREATE TABLE action.hold_request (
408 id SERIAL PRIMARY KEY,
409 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
410 capture_time TIMESTAMP WITH TIME ZONE,
411 fulfillment_time TIMESTAMP WITH TIME ZONE,
412 checkin_time TIMESTAMP WITH TIME ZONE,
413 return_time TIMESTAMP WITH TIME ZONE,
414 prev_check_time TIMESTAMP WITH TIME ZONE,
415 expire_time TIMESTAMP WITH TIME ZONE,
416 cancel_time TIMESTAMP WITH TIME ZONE,
417 cancel_cause INT REFERENCES action.hold_request_cancel_cause (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
419 target BIGINT NOT NULL, -- see hold_type
420 current_copy BIGINT, -- REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.unit now...
421 fulfillment_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
422 fulfillment_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
423 request_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
424 requestor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
425 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
426 selection_ou INT NOT NULL,
427 selection_depth INT NOT NULL DEFAULT 0,
428 pickup_lib INT NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
429 hold_type TEXT NOT NULL, -- CHECK (hold_type IN ('M','T','V','C')), -- XXX constraint too constraining...
430 holdable_formats TEXT,
432 email_notify BOOL NOT NULL DEFAULT FALSE,
434 sms_carrier INT REFERENCES config.sms_carrier (id),
435 frozen BOOL NOT NULL DEFAULT FALSE,
436 thaw_date TIMESTAMP WITH TIME ZONE,
437 shelf_time TIMESTAMP WITH TIME ZONE,
439 mint_condition BOOL NOT NULL DEFAULT TRUE,
440 shelf_expire_time TIMESTAMPTZ,
441 current_shelf_lib INT REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
442 behind_desk BOOLEAN NOT NULL DEFAULT FALSE
444 ALTER TABLE action.hold_request ADD CONSTRAINT sms_check CHECK (
446 OR sms_carrier IS NOT NULL -- and implied sms_notify IS NOT NULL
450 CREATE OR REPLACE FUNCTION action.hold_request_clear_map () RETURNS TRIGGER AS $$
452 DELETE FROM action.hold_copy_map WHERE hold = NEW.id;
457 CREATE TRIGGER hold_request_clear_map_tgr
458 AFTER UPDATE ON action.hold_request
461 (NEW.cancel_time IS NOT NULL AND OLD.cancel_time IS NULL)
462 OR (NEW.fulfillment_time IS NOT NULL AND OLD.fulfillment_time IS NULL)
464 EXECUTE PROCEDURE action.hold_request_clear_map();
466 CREATE INDEX hold_request_target_idx ON action.hold_request (target);
467 CREATE INDEX hold_request_usr_idx ON action.hold_request (usr);
468 CREATE INDEX hold_request_pickup_lib_idx ON action.hold_request (pickup_lib);
469 CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy);
470 CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time);
471 CREATE INDEX hold_request_fulfillment_staff_idx ON action.hold_request ( fulfillment_staff );
472 CREATE INDEX hold_request_requestor_idx ON action.hold_request ( requestor );
473 CREATE INDEX hold_request_open_idx ON action.hold_request (id) WHERE cancel_time IS NULL AND fulfillment_time IS NULL;
474 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;
475 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;
476 CREATE INDEX hold_request_copy_capture_time_idx ON action.hold_request (current_copy,capture_time);
477 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);
478 CREATE INDEX hold_fulfillment_time_idx ON action.hold_request (fulfillment_time) WHERE fulfillment_time IS NOT NULL;
479 CREATE INDEX hold_request_time_idx ON action.hold_request (request_time);
482 CREATE TABLE action.hold_request_note (
484 id BIGSERIAL PRIMARY KEY,
485 hold BIGINT NOT NULL REFERENCES action.hold_request (id)
487 DEFERRABLE INITIALLY DEFERRED,
490 slip BOOL NOT NULL DEFAULT FALSE,
491 pub BOOL NOT NULL DEFAULT FALSE,
492 staff BOOL NOT NULL DEFAULT FALSE -- created by staff
495 CREATE INDEX ahrn_hold_idx ON action.hold_request_note (hold);
498 CREATE TABLE action.hold_notification (
499 id SERIAL PRIMARY KEY,
500 hold INT NOT NULL REFERENCES action.hold_request (id)
502 DEFERRABLE INITIALLY DEFERRED,
503 notify_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
504 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
505 method TEXT NOT NULL, -- email address or phone number
508 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
509 CREATE INDEX ahn_notify_staff_idx ON action.hold_notification ( notify_staff );
511 CREATE TABLE action.hold_copy_map (
512 id BIGSERIAL PRIMARY KEY,
513 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
514 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
516 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
518 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
519 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
521 CREATE OR REPLACE FUNCTION
522 action.hold_request_regen_copy_maps(
523 hold_id INTEGER, copy_ids INTEGER[]) RETURNS VOID AS $$
524 DELETE FROM action.hold_copy_map WHERE hold = $1;
525 INSERT INTO action.hold_copy_map (hold, target_copy) SELECT $1, UNNEST($2);
528 CREATE TABLE action.transit_copy (
529 id SERIAL PRIMARY KEY,
530 source_send_time TIMESTAMP WITH TIME ZONE,
531 dest_recv_time TIMESTAMP WITH TIME ZONE,
532 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
533 source INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
534 dest INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
535 prev_hop INT REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED,
536 copy_status INT NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
537 persistant_transfer BOOL NOT NULL DEFAULT FALSE,
538 prev_dest INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
539 cancel_time TIMESTAMP WITH TIME ZONE
541 CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest);
542 CREATE INDEX active_transit_source_idx ON "action".transit_copy (source);
543 CREATE INDEX active_transit_cp_idx ON "action".transit_copy (target_copy);
546 CREATE TABLE action.hold_transit_copy (
547 hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
548 ) INHERITS (action.transit_copy);
549 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id);
550 -- 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
551 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest);
552 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
553 CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy);
554 CREATE INDEX hold_transit_copy_hold_idx on action.hold_transit_copy (hold);
557 CREATE TABLE action.unfulfilled_hold_list (
558 id BIGSERIAL PRIMARY KEY,
559 current_copy BIGINT NOT NULL,
561 circ_lib INT NOT NULL,
562 fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
564 CREATE INDEX uhr_hold_idx ON action.unfulfilled_hold_list (hold);
566 CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS
570 FROM action.unfulfilled_hold_list u
571 JOIN asset.copy c ON (c.id = u.current_copy)
574 CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS
577 FROM action.unfulfilled_hold_loops
580 CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS
582 FROM action.unfulfilled_hold_loops l
583 JOIN action.unfulfilled_hold_min_loop m USING (hold)
584 WHERE l.count = m.min;
586 CREATE VIEW action.unfulfilled_hold_max_loop AS
589 FROM action.unfulfilled_hold_loops
593 CREATE TABLE action.aged_hold_request (
595 usr_home_ou INT NOT NULL,
596 usr_profile INT NOT NULL,
598 staff_placed BOOLEAN NOT NULL,
599 LIKE action.hold_request
601 ALTER TABLE action.aged_hold_request
602 ADD PRIMARY KEY (id),
604 DROP COLUMN requestor,
605 DROP COLUMN sms_carrier,
606 ALTER COLUMN phone_notify TYPE BOOLEAN
607 USING CASE WHEN phone_notify IS NULL OR phone_notify = '' THEN FALSE ELSE TRUE END,
608 ALTER COLUMN sms_notify TYPE BOOLEAN
609 USING CASE WHEN sms_notify IS NULL OR sms_notify = '' THEN FALSE ELSE TRUE END,
610 ALTER COLUMN phone_notify SET NOT NULL,
611 ALTER COLUMN sms_notify SET NOT NULL;
612 CREATE INDEX aged_hold_request_target_idx ON action.aged_hold_request (target);
613 CREATE INDEX aged_hold_request_pickup_lib_idx ON action.aged_hold_request (pickup_lib);
614 CREATE INDEX aged_hold_request_current_copy_idx ON action.aged_hold_request (current_copy);
615 CREATE INDEX aged_hold_request_fulfillment_staff_idx ON action.aged_hold_request ( fulfillment_staff );
617 CREATE OR REPLACE VIEW action.all_hold_request AS
619 COALESCE(a.post_code, b.post_code) AS usr_post_code,
620 p.home_ou AS usr_home_ou,
621 p.profile AS usr_profile,
622 EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
623 CAST(ahr.requestor <> ahr.usr AS BOOLEAN) AS staff_placed,
627 ahr.fulfillment_time,
637 ahr.fulfillment_staff,
644 ahr.holdable_formats,
646 WHEN ahr.phone_notify IS NULL THEN FALSE
647 WHEN ahr.phone_notify = '' THEN FALSE
652 WHEN ahr.sms_notify IS NULL THEN FALSE
653 WHEN ahr.sms_notify = '' THEN FALSE
661 ahr.shelf_expire_time,
662 ahr.current_shelf_lib,
664 FROM action.hold_request ahr
665 JOIN actor.usr p ON (ahr.usr = p.id)
666 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
667 LEFT JOIN actor.usr_address b ON (p.billing_address = b.id)
707 FROM action.aged_hold_request;
709 CREATE OR REPLACE FUNCTION action.age_hold_on_delete () RETURNS TRIGGER AS $$
712 -- Archive a copy of the old row to action.aged_hold_request
714 INSERT INTO action.aged_hold_request
790 FROM action.all_hold_request WHERE id = OLD.id;
794 $$ LANGUAGE 'plpgsql';
796 CREATE TRIGGER action_hold_request_aging_tgr
797 BEFORE DELETE ON action.hold_request
799 EXECUTE PROCEDURE action.age_hold_on_delete ();
801 CREATE TABLE action.fieldset (
802 id SERIAL PRIMARY KEY,
803 owner INT NOT NULL REFERENCES actor.usr (id)
804 DEFERRABLE INITIALLY DEFERRED,
805 owning_lib INT NOT NULL REFERENCES actor.org_unit (id)
806 DEFERRABLE INITIALLY DEFERRED,
808 CONSTRAINT valid_status CHECK ( status in
809 ( 'PENDING', 'APPLIED', 'ERROR' )),
810 creation_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
811 scheduled_time TIMESTAMPTZ,
812 applied_time TIMESTAMPTZ,
813 classname TEXT NOT NULL, -- an IDL class name
815 stored_query INT REFERENCES query.stored_query (id)
816 DEFERRABLE INITIALLY DEFERRED,
818 CONSTRAINT lib_name_unique UNIQUE (owning_lib, name),
819 CONSTRAINT fieldset_one_or_the_other CHECK (
820 (stored_query IS NOT NULL AND pkey_value IS NULL) OR
821 (pkey_value IS NOT NULL AND stored_query IS NULL)
823 -- the CHECK constraint means we can update the fields for a single
824 -- row without all the extra overhead involved in a query
827 CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time );
828 CREATE INDEX action_owner_idx ON action.fieldset( owner );
831 CREATE TABLE action.fieldset_col_val (
832 id SERIAL PRIMARY KEY,
833 fieldset INT NOT NULL REFERENCES action.fieldset
835 DEFERRABLE INITIALLY DEFERRED,
836 col TEXT NOT NULL, -- "field" from the idl ... the column on the table
837 val TEXT, -- value for the column ... NULL means, well, NULL
838 CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col)
842 -- represents a circ chain summary
843 CREATE TYPE action.circ_chain_summary AS (
845 start_time TIMESTAMP WITH TIME ZONE,
846 checkout_workstation TEXT,
847 last_renewal_time TIMESTAMP WITH TIME ZONE, -- NULL if no renewals
848 last_stop_fines TEXT,
849 last_stop_fines_time TIMESTAMP WITH TIME ZONE,
850 last_renewal_workstation TEXT, -- NULL if no renewals
851 last_checkin_workstation TEXT,
852 last_checkin_time TIMESTAMP WITH TIME ZONE,
853 last_checkin_scan_time TIMESTAMP WITH TIME ZONE
857 CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id BIGINT ) RETURNS SETOF action.circulation AS $$
859 tmp_circ action.circulation%ROWTYPE;
860 circ_0 action.circulation%ROWTYPE;
863 SELECT INTO tmp_circ * FROM action.circulation WHERE id = ctx_circ_id;
865 IF tmp_circ IS NULL THEN
866 RETURN NEXT tmp_circ;
870 -- find the front of the chain
872 SELECT INTO tmp_circ * FROM action.circulation WHERE id = tmp_circ.parent_circ;
873 IF tmp_circ IS NULL THEN
879 -- now send the circs to the caller, oldest to newest
882 IF tmp_circ IS NULL THEN
885 RETURN NEXT tmp_circ;
886 SELECT INTO tmp_circ * FROM action.circulation WHERE parent_circ = tmp_circ.id;
890 $$ LANGUAGE 'plpgsql';
892 CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id BIGINT ) RETURNS action.circ_chain_summary AS $$
896 -- first circ in the chain
897 circ_0 action.circulation%ROWTYPE;
899 -- last circ in the chain
900 circ_n action.circulation%ROWTYPE;
902 -- circ chain under construction
903 chain action.circ_chain_summary;
904 tmp_circ action.circulation%ROWTYPE;
908 chain.num_circs := 0;
909 FOR tmp_circ IN SELECT * FROM action.circ_chain(ctx_circ_id) LOOP
911 IF chain.num_circs = 0 THEN
915 chain.num_circs := chain.num_circs + 1;
919 chain.start_time := circ_0.xact_start;
920 chain.last_stop_fines := circ_n.stop_fines;
921 chain.last_stop_fines_time := circ_n.stop_fines_time;
922 chain.last_checkin_time := circ_n.checkin_time;
923 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
924 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
925 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
927 IF chain.num_circs > 1 THEN
928 chain.last_renewal_time := circ_n.xact_start;
929 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
935 $$ LANGUAGE 'plpgsql';
937 -- same as action.circ_chain, but returns action.all_circulation
938 -- rows which may include aged circulations.
939 CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER)
940 RETURNS SETOF action.all_circulation_slim AS $$
942 tmp_circ action.all_circulation_slim%ROWTYPE;
943 circ_0 action.all_circulation_slim%ROWTYPE;
946 SELECT INTO tmp_circ * FROM action.all_circulation_slim WHERE id = ctx_circ_id;
948 IF tmp_circ IS NULL THEN
949 RETURN NEXT tmp_circ;
953 -- find the front of the chain
955 SELECT INTO tmp_circ * FROM action.all_circulation_slim
956 WHERE id = tmp_circ.parent_circ;
957 IF tmp_circ IS NULL THEN
963 -- now send the circs to the caller, oldest to newest
966 IF tmp_circ IS NULL THEN
969 RETURN NEXT tmp_circ;
970 SELECT INTO tmp_circ * FROM action.all_circulation_slim
971 WHERE parent_circ = tmp_circ.id;
975 $$ LANGUAGE 'plpgsql';
977 -- same as action.summarize_circ_chain, but returns data collected
978 -- from action.all_circulation, which may include aged circulations.
979 CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain
980 (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
984 -- first circ in the chain
985 circ_0 action.all_circulation_slim%ROWTYPE;
987 -- last circ in the chain
988 circ_n action.all_circulation_slim%ROWTYPE;
990 -- circ chain under construction
991 chain action.circ_chain_summary;
992 tmp_circ action.all_circulation_slim%ROWTYPE;
996 chain.num_circs := 0;
997 FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
999 IF chain.num_circs = 0 THEN
1003 chain.num_circs := chain.num_circs + 1;
1007 chain.start_time := circ_0.xact_start;
1008 chain.last_stop_fines := circ_n.stop_fines;
1009 chain.last_stop_fines_time := circ_n.stop_fines_time;
1010 chain.last_checkin_time := circ_n.checkin_time;
1011 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
1012 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
1013 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
1015 IF chain.num_circs > 1 THEN
1016 chain.last_renewal_time := circ_n.xact_start;
1017 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
1023 $$ LANGUAGE 'plpgsql';
1025 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
1027 h action.hold_request%ROWTYPE;
1030 usr_view_count actor.usr_setting%ROWTYPE;
1031 usr_view_age actor.usr_setting%ROWTYPE;
1032 usr_view_start actor.usr_setting%ROWTYPE;
1034 SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
1035 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
1036 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
1040 FROM action.hold_request
1042 AND fulfillment_time IS NULL
1043 AND cancel_time IS NULL
1044 ORDER BY request_time DESC
1049 IF usr_view_start.value IS NULL THEN
1053 IF usr_view_age.value IS NOT NULL THEN
1054 -- User opted in and supplied a retention age
1055 IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
1056 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
1058 view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
1062 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
1065 IF usr_view_count.value IS NOT NULL THEN
1066 view_count := oils_json_to_text(usr_view_count.value)::INT;
1071 -- show some fulfilled/canceled holds
1074 FROM action.hold_request
1076 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
1077 AND COALESCE(fulfillment_time, cancel_time) > NOW() - view_age
1078 ORDER BY COALESCE(fulfillment_time, cancel_time) DESC
1086 $func$ LANGUAGE PLPGSQL;
1088 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
1090 org_keep_age INTERVAL;
1091 org_use_last BOOL = false;
1092 org_age_is_min BOOL = false;
1098 circ_chain_head action.circulation%ROWTYPE;
1099 circ_chain_tail action.circulation%ROWTYPE;
1104 last_finished TIMESTAMP WITH TIME ZONE;
1109 SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
1111 SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
1112 IF org_keep_count IS NULL THEN
1113 RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
1116 SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished';
1117 SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min';
1119 -- First, find copies with more than keep_count non-renewal circs
1122 COUNT(*) AS total_real_circs
1123 FROM action.circulation
1124 WHERE parent_circ IS NULL
1125 AND xact_finish IS NOT NULL
1126 GROUP BY target_copy
1127 HAVING COUNT(*) > org_keep_count
1129 -- And, for those, select circs that are finished and older than keep_age
1130 FOR circ_chain_head IN
1131 -- For reference, the subquery uses a window function to order the circs newest to oldest and number them
1132 -- The outer query then uses that information to skip the most recent set the library wants to keep
1133 -- End result is we don't care what order they come out in, as they are all potentials for deletion.
1134 SELECT ac.* FROM action.circulation ac JOIN (
1135 SELECT rank() OVER (ORDER BY xact_start DESC), ac.id
1136 FROM action.circulation ac
1137 WHERE ac.target_copy = target_acp.target_copy
1138 AND ac.parent_circ IS NULL
1139 ORDER BY ac.xact_start ) ranked USING (id)
1140 WHERE ranked.rank > org_keep_count
1143 SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
1144 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);
1145 CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0;
1147 IF NOT org_use_last THEN
1148 last_finished := circ_chain_tail.xact_finish;
1151 keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL );
1153 IF org_age_is_min THEN
1154 keep_age := GREATEST( keep_age, org_keep_age );
1157 CONTINUE WHEN AGE(NOW(), last_finished) < keep_age;
1159 -- We've passed the purging tests, purge the circ chain starting at the end
1160 -- A trigger should auto-purge the rest of the chain.
1161 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
1163 count_purged := count_purged + 1;
1168 return count_purged;
1170 $func$ LANGUAGE PLPGSQL;
1172 CREATE OR REPLACE FUNCTION action.purge_holds() RETURNS INT AS $func$
1174 current_hold RECORD;
1180 user_start TIMESTAMPTZ;
1185 SELECT INTO cgf_d value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age' AND enabled;
1186 SELECT INTO cgf_f value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_fulfilled' AND enabled;
1187 SELECT INTO cgf_c value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_canceled' AND enabled;
1190 rank() OVER (PARTITION BY usr ORDER BY COALESCE(fulfillment_time, cancel_time) DESC),
1191 cgf_cs.value::INTERVAL as cgf_cs,
1194 action.hold_request ahr
1195 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)
1197 (fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL)
1199 IF prev_usr IS NULL OR prev_usr != current_hold.usr THEN
1200 prev_usr := current_hold.usr;
1201 SELECT INTO user_start oils_json_to_text(value)::TIMESTAMPTZ FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_start';
1202 SELECT INTO user_age oils_json_to_text(value)::INTERVAL FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_age';
1203 SELECT INTO user_count oils_json_to_text(value)::INT FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_count';
1204 IF user_start IS NOT NULL THEN
1205 user_age := LEAST(user_age, AGE(NOW(), user_start));
1207 IF user_count IS NULL THEN
1208 user_count := 1000; -- Assumption based on the user visible holds routine
1211 -- Library keep age trumps user keep anything, for purposes of being able to hold on to things when staff canceled and such.
1212 IF current_hold.fulfillment_time IS NOT NULL AND current_hold.fulfillment_time > NOW() - COALESCE(cgf_f, cgf_d) THEN
1215 IF current_hold.cancel_time IS NOT NULL AND current_hold.cancel_time > NOW() - COALESCE(current_hold.cgf_cs, cgf_c, cgf_d) THEN
1219 -- User keep age needs combining with count. If too old AND within the count, keep!
1220 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
1224 -- All checks should have passed, delete!
1225 DELETE FROM action.hold_request WHERE id = current_hold.id;
1226 purged_holds := purged_holds + 1;
1228 RETURN purged_holds;
1230 $func$ LANGUAGE plpgsql;
1232 CREATE OR REPLACE FUNCTION action.apply_fieldset(
1233 fieldset_id IN INT, -- id from action.fieldset
1234 table_name IN TEXT, -- table to be updated
1235 pkey_name IN TEXT, -- name of primary key column in that table
1236 query IN TEXT -- query constructed by qstore (for query-based
1237 -- fieldsets only; otherwise null
1252 IF fieldset_id IS NULL THEN
1253 RETURN 'Fieldset ID parameter is NULL';
1255 IF table_name IS NULL THEN
1256 RETURN 'Table name parameter is NULL';
1258 IF pkey_name IS NULL THEN
1259 RETURN 'Primary key name parameter is NULL';
1262 statement := 'UPDATE ' || table_name || ' SET';
1266 quote_literal( pkey_value )
1275 IF fs_status IS NULL THEN
1276 RETURN 'No fieldset found for id = ' || fieldset_id;
1277 ELSIF fs_status = 'APPLIED' THEN
1278 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
1285 FROM action.fieldset_col_val
1286 WHERE fieldset = fieldset_id
1288 statement := statement || sep || ' ' || cv.col
1289 || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
1294 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
1297 -- Add the WHERE clause. This differs according to whether it's a
1298 -- single-row fieldset or a query-based fieldset.
1300 IF query IS NULL AND fs_pkey_value IS NULL THEN
1301 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
1302 ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
1303 fs_query := rtrim( query, ';' );
1304 statement := statement || ' WHERE ' || pkey_name || ' IN ( '
1305 || fs_query || ' );';
1306 ELSIF query IS NULL AND fs_pkey_value IS NOT NULL THEN
1307 statement := statement || ' WHERE ' || pkey_name || ' = '
1308 || fs_pkey_value || ';';
1309 ELSE -- both are not null
1310 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
1313 -- Execute the update
1317 GET DIAGNOSTICS update_count = ROW_COUNT;
1319 IF UPDATE_COUNT > 0 THEN
1320 status_code := 'APPLIED';
1323 status_code := 'ERROR';
1324 msg := 'No eligible rows found for fieldset ' || fieldset_id;
1326 EXCEPTION WHEN OTHERS THEN
1327 status_code := 'ERROR';
1328 msg := 'Unable to apply fieldset ' || fieldset_id
1332 -- Update fieldset status
1334 UPDATE action.fieldset
1335 SET status = status_code,
1336 applied_time = now()
1337 WHERE id = fieldset_id;
1341 $$ LANGUAGE plpgsql;
1343 COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$
1344 Applies a specified fieldset, using a supplied table name and primary
1345 key name. The query parameter should be non-null only for
1346 query-based fieldsets.
1348 Returns NULL if successful, or an error message if not.
1351 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
1354 copy_context_ou INT DEFAULT NULL
1355 -- TODO maybe? hold_context_ou INT DEFAULT NULL. This would optionally
1356 -- support an "ahprox" measurement: adjust prox between copy circ lib and
1357 -- hold request lib, but I'm unsure whether to use this theoretical
1358 -- argument only in the baseline calculation or later in the other
1359 -- queries in this function.
1360 ) RETURNS NUMERIC AS $f$
1362 aoupa actor.org_unit_proximity_adjustment%ROWTYPE;
1363 ahr action.hold_request%ROWTYPE;
1364 acp asset.copy%ROWTYPE;
1365 acn asset.call_number%ROWTYPE;
1366 acl asset.copy_location%ROWTYPE;
1367 baseline_prox NUMERIC;
1377 SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
1378 SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
1379 SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
1380 SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
1382 IF copy_context_ou IS NULL THEN
1383 copy_context_ou := acp.circ_lib;
1386 -- First, gather the baseline proximity of "here" to pickup lib
1387 SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = copy_context_ou AND to_org = ahr.pickup_lib;
1389 -- Find any absolute adjustments, and set the baseline prox to that
1390 SELECT adj.* INTO aoupa
1391 FROM actor.org_unit_proximity_adjustment adj
1392 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1393 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1394 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acl_ol.id = adj.copy_location)
1395 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1396 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1397 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1398 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1399 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1400 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1401 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1402 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1403 absolute_adjustment AND
1404 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1406 COALESCE(acp_cl.distance,999)
1407 + COALESCE(acn_ol.distance,999)
1408 + COALESCE(acl_ol.distance,999)
1409 + COALESCE(ahr_pl.distance,999)
1410 + COALESCE(ahr_rl.distance,999),
1415 baseline_prox := aoupa.prox_adjustment;
1418 -- Now find any relative adjustments, and change the baseline prox based on them
1421 FROM actor.org_unit_proximity_adjustment adj
1422 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1423 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1424 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
1425 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1426 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1427 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1428 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1429 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1430 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1431 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1432 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1433 NOT absolute_adjustment AND
1434 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1436 baseline_prox := baseline_prox + aoupa.prox_adjustment;
1439 RETURN baseline_prox;
1441 $f$ LANGUAGE PLPGSQL;
1443 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity_update () RETURNS TRIGGER AS $f$
1445 NEW.proximity := action.hold_copy_calculated_proximity(NEW.hold,NEW.target_copy);
1448 $f$ LANGUAGE PLPGSQL;
1450 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 ();
1452 CREATE TABLE action.usr_circ_history (
1453 id BIGSERIAL PRIMARY KEY,
1454 usr INTEGER NOT NULL REFERENCES actor.usr(id)
1455 DEFERRABLE INITIALLY DEFERRED,
1456 xact_start TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1457 target_copy BIGINT NOT NULL, -- asset.copy.id / serial.unit.id
1458 due_date TIMESTAMP WITH TIME ZONE NOT NULL,
1459 checkin_time TIMESTAMP WITH TIME ZONE,
1460 source_circ BIGINT REFERENCES action.circulation(id)
1461 ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
1464 CREATE INDEX action_usr_circ_history_usr_idx ON action.usr_circ_history ( usr );
1466 CREATE TRIGGER action_usr_circ_history_target_copy_trig
1467 AFTER INSERT OR UPDATE ON action.usr_circ_history
1468 FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy');
1470 CREATE OR REPLACE FUNCTION action.maintain_usr_circ_history()
1471 RETURNS TRIGGER AS $FUNK$
1477 -- Any retention value signifies history is enabled.
1478 -- This assumes that clearing these values via external
1479 -- process deletes the action.usr_circ_history rows.
1480 -- TODO: replace these settings w/ a single bool setting?
1481 PERFORM 1 FROM actor.usr_setting
1482 WHERE usr = NEW.usr AND value IS NOT NULL AND name IN (
1483 'history.circ.retention_age',
1484 'history.circ.retention_start'
1491 IF TG_OP = 'INSERT' AND NEW.parent_circ IS NULL THEN
1492 -- Starting a new circulation. Insert the history row.
1493 INSERT INTO action.usr_circ_history
1494 (usr, xact_start, target_copy, due_date, source_circ)
1506 -- find the first and last circs in the circ chain
1507 -- for the currently modified circ.
1508 FOR cur_circ IN SELECT id FROM action.circ_chain(NEW.id) LOOP
1509 IF first_circ IS NULL THEN
1510 first_circ := cur_circ;
1513 -- Allow the loop to continue so that at as the loop
1514 -- completes cur_circ points to the final circulation.
1517 IF NEW.id <> cur_circ THEN
1518 -- Modifying an intermediate circ. Ignore it.
1522 -- Update the due_date/checkin_time on the history row if the current
1523 -- circ is the last circ in the chain and an update is warranted.
1525 UPDATE action.usr_circ_history
1527 due_date = NEW.due_date,
1528 checkin_time = NEW.checkin_time
1530 source_circ = first_circ
1532 due_date <> NEW.due_date OR (
1533 (checkin_time IS NULL AND NEW.checkin_time IS NOT NULL) OR
1534 (checkin_time IS NOT NULL AND NEW.checkin_time IS NULL) OR
1535 (checkin_time <> NEW.checkin_time)
1540 $FUNK$ LANGUAGE PLPGSQL;
1542 CREATE TRIGGER maintain_usr_circ_history_tgr
1543 AFTER INSERT OR UPDATE ON action.circulation
1544 FOR EACH ROW EXECUTE PROCEDURE action.maintain_usr_circ_history();
1546 CREATE OR REPLACE VIEW action.all_circulation_combined_types AS
1547 SELECT acirc.id AS id,
1552 ac_acirc.circ_modifier AS item_type,
1553 'regular_circ'::text AS circ_type
1554 FROM action.circulation acirc,
1556 WHERE acirc.target_copy = ac_acirc.id
1558 SELECT ancc.id::BIGINT AS id,
1559 ancc.circ_time AS xact_start,
1561 ancc.staff AS circ_staff,
1562 ancc.circ_time AS create_time,
1563 cnct_ancc.name AS item_type,
1564 'non-cat_circ'::text AS circ_type
1565 FROM action.non_cataloged_circulation ancc,
1566 config.non_cataloged_type cnct_ancc
1567 WHERE ancc.item_type = cnct_ancc.id
1569 SELECT aihu.id::BIGINT AS id,
1570 aihu.use_time AS xact_start,
1571 aihu.org_unit AS circ_lib,
1572 aihu.staff AS circ_staff,
1573 aihu.use_time AS create_time,
1574 ac_aihu.circ_modifier AS item_type,
1575 'in-house_use'::text AS circ_type
1576 FROM action.in_house_use aihu,
1578 WHERE aihu.item = ac_aihu.id
1580 SELECT ancihu.id::BIGINT AS id,
1581 ancihu.use_time AS xact_start,
1582 ancihu.org_unit AS circ_lib,
1583 ancihu.staff AS circ_staff,
1584 ancihu.use_time AS create_time,
1585 cnct_ancihu.name AS item_type,
1586 'non-cat_circ'::text AS circ_type
1587 FROM action.non_cat_in_house_use ancihu,
1588 config.non_cataloged_type cnct_ancihu
1589 WHERE ancihu.item_type = cnct_ancihu.id
1591 SELECT aacirc.id AS id,
1596 ac_aacirc.circ_modifier AS item_type,
1597 'aged_circ'::text AS circ_type
1598 FROM action.aged_circulation aacirc,
1599 asset.copy ac_aacirc
1600 WHERE aacirc.target_copy = ac_aacirc.id;