From a1181abcae108f8cecbad358b66a002887e55d7b Mon Sep 17 00:00:00 2001 From: Remington Steed Date: Thu, 9 Feb 2017 16:32:09 -0500 Subject: [PATCH] LP#1496522 Include circs in money summary view This commit changes the new view money.usr_summary_per_org_unit to include bills from action.circulation along with money.grocery. This also changes the 30 day repeat interval to 1 month to avoid changing day of the month. And a few typos are fixed, including some missing punctuation in action_trigger_filters.json.example. Signed-off-by: Remington Steed --- .../examples/action_trigger_filters.json.example | 3 ++- ...tion_trigger_for_periodic_billing_statement.sql | 26 ++++++++++++---------- ...tion_trigger_for_periodic_billing_statement.txt | 2 +- 3 files changed, 17 insertions(+), 14 deletions(-) diff --git a/Open-ILS/examples/action_trigger_filters.json.example b/Open-ILS/examples/action_trigger_filters.json.example index 2005e5b226..1709744882 100644 --- a/Open-ILS/examples/action_trigger_filters.json.example +++ b/Open-ILS/examples/action_trigger_filters.json.example @@ -40,7 +40,8 @@ "active":"t", "deleted":"f" } + }, "patron_has_bills" : { - "context_org" : "billing_ou" + "context_org" : "billing_ou" } } diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.action_trigger_for_periodic_billing_statement.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.action_trigger_for_periodic_billing_statement.sql index 2b17f24d82..ffc38520ef 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.action_trigger_for_periodic_billing_statement.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.action_trigger_for_periodic_billing_statement.sql @@ -28,16 +28,18 @@ validator "PatronExceedsBills" required', -- Create the view CREATE OR REPLACE VIEW money.usr_summary_per_org_unit AS - SELECT materialized_billable_xact_summary.usr, - money.grocery.billing_location AS billing_ou, - sum(materialized_billable_xact_summary.total_paid) AS total_paid, - sum(materialized_billable_xact_summary.total_owed) AS total_owed, - sum(materialized_billable_xact_summary.balance_owed) AS balance_owed, - -- This needs to be non-null for patron bill notification - COALESCE(MAX(materialized_billable_xact_summary.last_payment_ts),'0001-01-01'::TIMESTAMP) AS last_payment_ts - FROM money.materialized_billable_xact_summary, money.grocery - WHERE money.grocery.id = money.materialized_billable_xact_summary.id - GROUP BY materialized_billable_xact_summary.usr, money.grocery.billing_location; + WITH located_xact AS ( + SELECT id, circ_lib AS billing_ou FROM action.circulation + UNION + SELECT id, billing_location AS billing_ou FROM money.grocery + ) + SELECT mmbts.usr, billing_ou, sum(mmbts.total_paid) AS total_paid, + sum(mmbts.total_owed) AS total_owed, + sum(mmbts.balance_owed) AS balance_owed, + COALESCE(MAX(mmbts.last_payment_ts),'0001-01-01'::TIMESTAMP) AS last_payment_ts + FROM money.materialized_billable_xact_summary mmbts + JOIN located_xact ON located_xact.id = mmbts.id + GROUP BY mmbts.usr, billing_ou; ALTER TABLE money.usr_summary_per_org_unit @@ -46,13 +48,13 @@ money.usr_summary_per_org_unit -- Create the action trigger event definition INSERT INTO action_trigger.event_definition (active, owner, name, hook, validator, reactor, delay_field, repeat_delay, template) - VALUES ('f', '1', 'Patron recurring 30 billing notice', 'patron_has_bills', 'PatronExceedsBills', 'SendEmail', 'last_payment_ts', '30 days', + VALUES ('f', '1', 'Patron recurring 1 month billing notice', 'patron_has_bills', 'PatronExceedsBills', 'SendEmail', 'last_payment_ts', '1 month', $$ [%- USE date -%] [%- user = target.usr -%] To: [%- params.recipient_email || user.email %] From: [%- user.home_ou.name %] <[% helpers.get_org_setting(user.home_ou, 'org.bounced_emails') || lib.email || params.sender_email || default_sender %]> -Subject: Libarary bills +Subject: Library bills [%# You can use the library setting "Notify Patron bill when exceeds" to squelch this notification when the balance owed doesnt exceed your configuration %] Dear [% user.first_given_name %] [% user.family_name %], diff --git a/docs/RELEASE_NOTES_NEXT/LP1496522_action_trigger_for_periodic_billing_statement.txt b/docs/RELEASE_NOTES_NEXT/LP1496522_action_trigger_for_periodic_billing_statement.txt index f1c8d65c9b..d3559753f6 100644 --- a/docs/RELEASE_NOTES_NEXT/LP1496522_action_trigger_for_periodic_billing_statement.txt +++ b/docs/RELEASE_NOTES_NEXT/LP1496522_action_trigger_for_periodic_billing_statement.txt @@ -2,7 +2,7 @@ Patron Periodic Billing Statement Action Trigger ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ You can setup an action trigger that will notify patrons about their oustanding bills. -A new library setting "patron.notify_bills_when_exceeds" can bet setup to only notify +A new library setting "patron.notify_bills_when_exceeds" can be setup to only notify patrons who exceed a certain amount. By default, patrons will be notified if they exceed 0. -- 2.11.0