From 2bb8fd7688f33e9f6ffe1ef805e031f05861a916 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Thu, 13 Feb 2020 11:01:41 -0500 Subject: [PATCH] LP1858448 Aged money control flags Adds 2 global flags for controling if/when billings and payments are aged. history.money.age_with_circs history.money.retention_age Adds a srfsh script for manually aging money based on the 'history.money.retention_age' setting value. Signed-off-by: Bill Erickson Signed-off-by: John Amundson Signed-off-by: Jason Stephenson --- Open-ILS/src/Makefile.am | 2 + Open-ILS/src/sql/Pg/080.schema.money.sql | 59 +++++++++++- Open-ILS/src/sql/Pg/090.schema.action.sql | 13 +-- Open-ILS/src/sql/Pg/950.data.seed-values.sql | 24 +++++ .../upgrade/XXXX.schema.aged-money-fields.sql | 95 +++++++++++++++++-- Open-ILS/src/support-scripts/age_money.srfsh | 7 ++ .../Administration/aged-money.adoc | 21 ++++ .../Reports/aged-payment-fields.adoc | 6 -- 8 files changed, 201 insertions(+), 26 deletions(-) create mode 100755 Open-ILS/src/support-scripts/age_money.srfsh create mode 100644 docs/RELEASE_NOTES_NEXT/Administration/aged-money.adoc delete mode 100644 docs/RELEASE_NOTES_NEXT/Reports/aged-payment-fields.adoc diff --git a/Open-ILS/src/Makefile.am b/Open-ILS/src/Makefile.am index 9a0d5a7549..87a8b00488 100644 --- a/Open-ILS/src/Makefile.am +++ b/Open-ILS/src/Makefile.am @@ -75,6 +75,7 @@ core_scripts = $(examples)/oils_ctl.sh \ $(supportscr)/purge_circulations.srfsh \ $(supportscr)/purge_pending_users.srfsh \ $(supportscr)/clear_cc_number.srfsh \ + $(supportscr)/age_money.srfsh \ $(supportscr)/sitemap_generator \ $(srcdir)/extras/eg_config \ $(srcdir)/extras/openurl_map.pl \ @@ -288,6 +289,7 @@ ilscore-install: sed -i 's|SYSCONFDIR|@sysconfdir@|g' '$(DESTDIR)@bindir@/long-overdue-status-update.pl' sed -i 's|BINDIR|@bindir@|g' '$(DESTDIR)@bindir@/thaw_expired_frozen_holds.srfsh' sed -i 's|BINDIR|@bindir@|g' '$(DESTDIR)@bindir@/clear_cc_number.srfsh' + sed -i 's|BINDIR|@bindir@|g' '$(DESTDIR)@bindir@/age_money.srfsh' sed -i 's|BINDIR|@bindir@|g' '$(DESTDIR)@bindir@/purge_at_events.srfsh' sed -i 's|BINDIR|@bindir@|g' '$(DESTDIR)@bindir@/purge_circulations.srfsh' sed -i 's|BINDIR|@bindir@|g' '$(DESTDIR)@bindir@/purge_holds.srfsh' diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql index 631c7a5e0e..2589923cff 100644 --- a/Open-ILS/src/sql/Pg/080.schema.money.sql +++ b/Open-ILS/src/sql/Pg/080.schema.money.sql @@ -701,7 +701,7 @@ CREATE OR REPLACE VIEW money.cashdrawer_payment_view AS LEFT JOIN money.payment_view t ON (p.id = t.id); -- serves as the basis for the aged payments data. -CREATE OR REPLACE VIEW money.payment_view_extended AS +CREATE OR REPLACE VIEW money.payment_view_for_aging AS SELECT p.*, bnm.accepting_usr, bnmd.cash_drawer, @@ -726,7 +726,7 @@ CREATE INDEX aged_payment_billing_idx ON money.aged_payment(billing); CREATE TABLE money.aged_billing (LIKE money.billing INCLUDING INDEXES); CREATE OR REPLACE VIEW money.all_payments AS - SELECT * FROM money.payment_view_extended + SELECT * FROM money.payment_view_for_aging UNION ALL SELECT * FROM money.aged_payment; @@ -735,5 +735,60 @@ CREATE OR REPLACE VIEW money.all_billings AS UNION ALL SELECT * FROM money.aged_billing; +CREATE OR REPLACE FUNCTION money.age_billings_and_payments() RETURNS INTEGER AS $FUNC$ +-- Age billings and payments linked to transactions which were +-- completed at least 'older_than' time ago. +DECLARE + xact_id BIGINT; + counter INTEGER DEFAULT 0; + keep_age INTERVAL; +BEGIN + + SELECT value::INTERVAL INTO keep_age FROM config.global_flag + WHERE name = 'history.money.retention_age' AND enabled; + + -- Confirm interval-based aging is enabled. + IF keep_age IS NULL THEN RETURN counter; END IF; + + -- Start with non-circulation transactions + FOR xact_id IN SELECT DISTINCT(xact.id) FROM money.billable_xact xact + -- confirm there is something to age + JOIN money.billing mb ON mb.xact = xact.id + -- Avoid aging money linked to non-aged circulations. + LEFT JOIN action.circulation circ ON circ.id = xact.id + WHERE circ.id IS NULL AND AGE(NOW(), xact.xact_finish) > keep_age LOOP + + PERFORM money.age_billings_and_payments_for_xact(xact_id); + counter := counter + 1; + END LOOP; + + -- Then handle aged circulation money. + FOR xact_id IN SELECT DISTINCT(xact.id) FROM action.aged_circulation xact + -- confirm there is something to age + JOIN money.billing mb ON mb.xact = xact.id + WHERE AGE(NOW(), xact.xact_finish) > keep_age LOOP + + PERFORM money.age_billings_and_payments_for_xact(xact_id); + counter := counter + 1; + END LOOP; + + RETURN counter; +END; +$FUNC$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION money.age_billings_and_payments_for_xact + (xact_id BIGINT) RETURNS VOID AS $FUNC$ + + INSERT INTO money.aged_billing + SELECT * FROM money.billing WHERE xact = $1; + + INSERT INTO money.aged_payment + SELECT * FROM money.payment_view_for_aging WHERE xact = xact_id; + + DELETE FROM money.payment WHERE xact = $1; + DELETE FROM money.billing WHERE xact = $1; + +$FUNC$ LANGUAGE SQL; + COMMIT; diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index ae1fa9adfd..af8a3e36b9 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -314,7 +314,6 @@ UNION ALL ; - CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$ DECLARE found char := 'N'; @@ -354,14 +353,12 @@ BEGIN -- Migrate billings and payments to aged tables - INSERT INTO money.aged_billing - SELECT * FROM money.billing WHERE xact = OLD.id; - - INSERT INTO money.aged_payment - SELECT * FROM money.payment_view_extended WHERE xact = OLD.id; + SELECT 'Y' INTO found FROM config.global_flag + WHERE name = 'history.money.age_with_circs' AND enabled; - DELETE FROM money.payment WHERE xact = OLD.id; - DELETE FROM money.billing WHERE xact = OLD.id; + IF found = 'Y' THEN + PERFORM money.age_billings_and_payments_for_xact(OLD.id); + END IF; RETURN OLD; END; 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 0b30009bee..b74f672a5d 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -20375,3 +20375,27 @@ VALUES ( ) ); +INSERT INTO config.global_flag (name, value, enabled, label) +VALUES ( + 'history.money.age_with_circs', + NULL, + FALSE, + oils_i18n_gettext( + 'history.money.age_with_circs', + 'Age billings and payments when cirulcations are aged.', + 'cgf', 'label' + ) +), ( + 'history.money.retention_age', + NULL, + FALSE, + oils_i18n_gettext( + 'history.money.retention_age', + 'Age billings and payments whose transactions were completed ' || + 'this long ago. For circulation transactions, this setting ' || + 'is superseded by the "history.money.age_with_circs" setting', + 'cgf', 'label' + ) +); + + diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-money-fields.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-money-fields.sql index 456ea11b11..6cd0738c0e 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-money-fields.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-money-fields.sql @@ -2,9 +2,32 @@ BEGIN; -- SELECT evergreen.upgrade_deps_block_check('TODO', :eg_version); +INSERT INTO config.global_flag (name, value, enabled, label) +VALUES ( + 'history.money.age_with_circs', + NULL, + FALSE, + oils_i18n_gettext( + 'history.money.age_with_circs', + 'Age billings and payments when cirulcations are aged.', + 'cgf', 'label' + ) +), ( + 'history.money.retention_age', + NULL, + FALSE, + oils_i18n_gettext( + 'history.money.retention_age', + 'Age billings and payments whose transactions were completed ' || + 'this long ago. For circulation transactions, this setting ' || + 'is superseded by the "history.money.age_with_circs" setting', + 'cgf', 'label' + ) +); + DROP VIEW money.all_payments; -CREATE OR REPLACE VIEW money.payment_view_extended AS +CREATE OR REPLACE VIEW money.payment_view_for_aging AS SELECT p.*, bnm.accepting_usr, bnmd.cash_drawer, @@ -24,10 +47,65 @@ CREATE INDEX aged_payment_cash_drawer_idx ON money.aged_payment(cash_drawer); CREATE INDEX aged_payment_billing_idx ON money.aged_payment(billing); CREATE OR REPLACE VIEW money.all_payments AS - SELECT * FROM money.payment_view_extended + SELECT * FROM money.payment_view_for_aging UNION ALL SELECT * FROM money.aged_payment; +CREATE OR REPLACE FUNCTION money.age_billings_and_payments() RETURNS INTEGER AS $FUNC$ +-- Age billings and payments linked to transactions which were +-- completed at least 'older_than' time ago. +DECLARE + xact_id BIGINT; + counter INTEGER DEFAULT 0; + keep_age INTERVAL; +BEGIN + + SELECT value::INTERVAL INTO keep_age FROM config.global_flag + WHERE name = 'history.money.retention_age' AND enabled; + + -- Confirm interval-based aging is enabled. + IF keep_age IS NULL THEN RETURN counter; END IF; + + -- Start with non-circulation transactions + FOR xact_id IN SELECT DISTINCT(xact.id) FROM money.billable_xact xact + -- confirm there is something to age + JOIN money.billing mb ON mb.xact = xact.id + -- Avoid aging money linked to non-aged circulations. + LEFT JOIN action.circulation circ ON circ.id = xact.id + WHERE circ.id IS NULL AND AGE(NOW(), xact.xact_finish) > keep_age LOOP + + PERFORM money.age_billings_and_payments_for_xact(xact_id); + counter := counter + 1; + END LOOP; + + -- Then handle aged circulation money. + FOR xact_id IN SELECT DISTINCT(xact.id) FROM action.aged_circulation xact + -- confirm there is something to age + JOIN money.billing mb ON mb.xact = xact.id + WHERE AGE(NOW(), xact.xact_finish) > keep_age LOOP + + PERFORM money.age_billings_and_payments_for_xact(xact_id); + counter := counter + 1; + END LOOP; + + RETURN counter; +END; +$FUNC$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION money.age_billings_and_payments_for_xact + (xact_id BIGINT) RETURNS VOID AS $FUNC$ + + INSERT INTO money.aged_billing + SELECT * FROM money.billing WHERE xact = $1; + + INSERT INTO money.aged_payment + SELECT * FROM money.payment_view_for_aging WHERE xact = xact_id; + + DELETE FROM money.payment WHERE xact = $1; + DELETE FROM money.billing WHERE xact = $1; + +$FUNC$ LANGUAGE SQL; + CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$ DECLARE found char := 'N'; @@ -67,18 +145,15 @@ BEGIN -- Migrate billings and payments to aged tables - INSERT INTO money.aged_billing - SELECT * FROM money.billing WHERE xact = OLD.id; - - INSERT INTO money.aged_payment - SELECT * FROM money.payment_view_extended WHERE xact = OLD.id; + SELECT 'Y' INTO found FROM config.global_flag + WHERE name = 'history.money.age_with_circs' AND enabled; - DELETE FROM money.payment WHERE xact = OLD.id; - DELETE FROM money.billing WHERE xact = OLD.id; + IF found = 'Y' THEN + PERFORM money.age_billings_and_payments_for_xact(OLD.id); + END IF; RETURN OLD; END; $$ LANGUAGE 'plpgsql'; - COMMIT; diff --git a/Open-ILS/src/support-scripts/age_money.srfsh b/Open-ILS/src/support-scripts/age_money.srfsh new file mode 100755 index 0000000000..533403c61a --- /dev/null +++ b/Open-ILS/src/support-scripts/age_money.srfsh @@ -0,0 +1,7 @@ +#!BINDIR/srfsh +open open-ils.cstore +request open-ils.cstore open-ils.cstore.transaction.begin +request open-ils.cstore open-ils.cstore.json_query {"from":["action.purge_circulations"]} +request open-ils.cstore open-ils.cstore.transaction.commit +close open-ils.cstore + diff --git a/docs/RELEASE_NOTES_NEXT/Administration/aged-money.adoc b/docs/RELEASE_NOTES_NEXT/Administration/aged-money.adoc new file mode 100644 index 0000000000..f6d736f99d --- /dev/null +++ b/docs/RELEASE_NOTES_NEXT/Administration/aged-money.adoc @@ -0,0 +1,21 @@ +Aged Money Changes +^^^^^^^^^^^^^^^^^^ + +Two new global flag settings have been added to control if/when billings and +payments are aged. Both settings are disabled by default. + +* 'history.money.age_with_circs' + ** Age billings and payments linked to circulations when the cirulcation + is aged. +* 'history.money.retention_age' + ** Age billings and payments based on the age of the finish date for + the linked transaction. + ** To age money based on this setting, there is a new srfsh script + at (by default) /openils/bin/age_money.srfsh. + +Aged Payment Additional Fields +++++++++++++++++++++++++++++++ + +The aged payment table now has accepting_usr, cash_drawer, and billing +columns to improve reporting of aged money. + diff --git a/docs/RELEASE_NOTES_NEXT/Reports/aged-payment-fields.adoc b/docs/RELEASE_NOTES_NEXT/Reports/aged-payment-fields.adoc deleted file mode 100644 index 475d06a2ff..0000000000 --- a/docs/RELEASE_NOTES_NEXT/Reports/aged-payment-fields.adoc +++ /dev/null @@ -1,6 +0,0 @@ -Aged Payment Additional Fields -^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ - -The aged payment table now has accepting_usr, cash_drawer, and billing -columns to improve reporting of aged money. - -- 2.43.2