$(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 \
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'
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,
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;
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;
;
-
CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
DECLARE
found char := 'N';
-- 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;
)
);
+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'
+ )
+);
+
+
-- 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,
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';
-- 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;
--- /dev/null
+#!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
+
--- /dev/null
+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.
+
+++ /dev/null
-Aged Payment Additional Fields
-^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-
-The aged payment table now has accepting_usr, cash_drawer, and billing
-columns to improve reporting of aged money.
-
projects / evergreen-equinox.git / commitdiff