From ff45812052b5ec0ec27c3462afeb4c9a0d4f6959 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Tue, 22 Dec 2015 15:49:29 -0500 Subject: [PATCH] LP#1527342 SQL / IDL Signed-off-by: Bill Erickson --- Open-ILS/src/sql/Pg/090.schema.action.sql | 89 ++++++++++++++++++++++ Open-ILS/src/sql/Pg/950.data.seed-values.sql | 16 +++- .../Pg/upgrade/XXXX.schema.decouple_co_history.sql | 3 +- 3 files changed, 103 insertions(+), 5 deletions(-) diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index a3f517c43c..e07b4549db 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -1306,4 +1306,93 @@ $f$ LANGUAGE PLPGSQL; 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 (); +CREATE TABLE action.usr_circ_history ( + id BIGSERIAL PRIMARY KEY, + usr INTEGER NOT NULL REFERENCES actor.usr(id) + DEFERRABLE INITIALLY DEFERRED, + xact_start TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + target_copy BIGINT NOT NULL REFERENCES asset.copy(id) + DEFERRABLE INITIALLY DEFERRED, + due_date TIMESTAMP WITH TIME ZONE NOT NULL, + checkin_time TIMESTAMP WITH TIME ZONE, + source_circ BIGINT NOT NULL REFERENCES action.circulation(id) + ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED +); + +CREATE OR REPLACE FUNCTION action.maintain_usr_circ_history() + RETURNS TRIGGER AS $FUNK$ +DECLARE + cur_circ BIGINT; + first_circ BIGINT; +BEGIN + + -- Any retention value signifies history is enabled. + -- This assumes that clearing these values via external + -- process deletes the action.usr_circ_history rows. + -- TODO: replace these settings w/ a single bool setting? + PERFORM 1 FROM actor.usr_setting + WHERE usr = NEW.usr AND value IS NOT NULL AND name IN ( + 'history.circ.retention_age', + 'history.circ.retention_start' + ); + + IF NOT FOUND THEN + RETURN NEW; + END IF; + + IF TG_OP = 'INSERT' AND NEW.parent_circ IS NULL THEN + -- Starting a new circulation. Insert the history row. + INSERT INTO action.usr_circ_history + (usr, xact_start, target_copy, due_date, source_circ) + VALUES ( + NEW.usr, + NEW.xact_start, + NEW.target_copy, + NEW.due_date, + NEW.id + ); + + RETURN NEW; + END IF; + + -- find the first and last circs in the circ chain + -- for the currently modified circ. + FOR cur_circ IN SELECT id FROM action.circ_chain(NEW.id) LOOP + IF first_circ IS NULL THEN + first_circ := cur_circ; + CONTINUE; + END IF; + -- Allow the loop to continue so that at as the loop + -- completes cur_circ points to the final circulation. + END LOOP; + + IF NEW.id <> cur_circ THEN + -- Modifying an intermediate circ. Ignore it. + RETURN NEW; + END IF; + + -- Update the due_date/checkin_time on the history row if the current + -- circ is the last circ in the chain and an update is warranted. + + UPDATE action.usr_circ_history + SET + due_date = NEW.due_date, + checkin_time = NEW.checkin_time + WHERE + source_circ = first_circ + AND ( + due_date <> NEW.due_date OR ( + (checkin_time IS NULL AND NEW.checkin_time IS NOT NULL) OR + (checkin_time IS NOT NULL AND NEW.checkin_time IS NULL) OR + (checkin_time <> NEW.checkin_time) + ) + ); + RETURN NEW; +END; +$FUNK$ LANGUAGE PLPGSQL; + +CREATE TRIGGER maintain_usr_circ_history_tgr + AFTER INSERT OR UPDATE ON action.circulation + FOR EACH ROW EXECUTE PROCEDURE action.maintain_usr_circ_history(); + COMMIT; diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql index ee87ff59f5..97db12beda 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -9442,7 +9442,7 @@ INSERT INTO INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ( 'circ.format.history.email', - 'circ', + 'auch', oils_i18n_gettext( 'circ.format.history.email', 'An email has been requested for a circ history.', @@ -9453,7 +9453,7 @@ INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ( ) ,( 'circ.format.history.print', - 'circ', + 'auch', oils_i18n_gettext( 'circ.format.history.print', 'A circ history needs to be formatted for printing.', @@ -9520,7 +9520,11 @@ Subject: Circulation History Barcode: [% circ.target_copy.barcode %] Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %] Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %] - Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %] + Returned: [% + date.format( + helpers.format_date(circ.checkin_time), '%Y-%m-%d') + IF circ.checkin_time; + %] [% END %] $$ ) @@ -9549,7 +9553,11 @@ $$
Barcode: [% circ.target_copy.barcode %]
Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]
Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
-
Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %]
+
Returned: [% + date.format( + helpers.format_date(circ.checkin_time), '%Y-%m-%d') + IF circ.checkin_time; -%] +
[% END %] diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.decouple_co_history.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.decouple_co_history.sql index 4689e1cc69..268a8e4889 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.decouple_co_history.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.decouple_co_history.sql @@ -156,7 +156,8 @@ $$
Returned: [% date.format( helpers.format_date(circ.checkin_time), '%Y-%m-%d') - IF circ.checkin_time; %]
+ IF circ.checkin_time; -%] + [% END %] -- 2.11.0