From e1021070ef7f45a9e5b04455c49058154b05d9b0 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 and booking.reservation, along with money.grocery. Several other small changes are made here: - The 30 day repeat interval is changed to 1 month to avoid changing day of the month. - A filter is added to the json example that prevents creating events that we know will be invalid (e.g. patrons with balances <= 0). - The new hook, validator, event_definition and environment variable (already in the update script) are added to the seed data file, and the new money view is added to the cross-schema views file. - 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 | 6 ++++- Open-ILS/src/sql/Pg/500.view.cross-schema.sql | 20 ++++++++++++++++ Open-ILS/src/sql/Pg/950.data.seed-values.sql | 28 ++++++++++++++++++++++ ...tion_trigger_for_periodic_billing_statement.sql | 28 ++++++++++++---------- ...tion_trigger_for_periodic_billing_statement.txt | 2 +- 5 files changed, 70 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..dd7bb7f9ed 100644 --- a/Open-ILS/examples/action_trigger_filters.json.example +++ b/Open-ILS/examples/action_trigger_filters.json.example @@ -40,7 +40,11 @@ "active":"t", "deleted":"f" } + }, "patron_has_bills" : { - "context_org" : "billing_ou" + "context_org" : "billing_ou", + "filter": { + "balance_owed": {">": "0"} + } } } diff --git a/Open-ILS/src/sql/Pg/500.view.cross-schema.sql b/Open-ILS/src/sql/Pg/500.view.cross-schema.sql index 2dd3592b52..19ea7d7a81 100644 --- a/Open-ILS/src/sql/Pg/500.view.cross-schema.sql +++ b/Open-ILS/src/sql/Pg/500.view.cross-schema.sql @@ -49,6 +49,26 @@ CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS WHERE xact_type = 'circulation' AND xact_finish IS NULL GROUP BY usr; +CREATE OR REPLACE VIEW money.usr_summary_per_org_unit AS + 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 + UNION + SELECT id, request_lib AS billing_ou FROM booking.reservation + ) + 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 + OWNER TO evergreen; + -- Not a view, but it's cross-schema.. CREATE TABLE config.idl_field_doc ( 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 75d7ffbcb3..e465072236 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -11460,6 +11460,34 @@ INSERT INTO action_trigger.environment ( ,( 32, 'items' ) ; +-- Periodic email to patrons with balances over a certain amount +INSERT INTO action_trigger.hook (key,core_type,description,passive) +VALUES('patron_has_bills','muspou','Patron has bills. Usually used in conjunction with reactor SendEmail and library setting "Notify Patron bill when exceeds".','t'); + +INSERT INTO action_trigger.validator (module,description) VALUES('PatronExceedsBills','Event is valid if the library setting "Notify Patron bill when exceeds" setting threshold is exceeded'); + +INSERT INTO action_trigger.event_definition (active, owner, name, hook, validator, reactor, delay_field, repeat_delay, template) + 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: 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 %], + + You have an outstanding balance at the library: + + [%- target.balance_owed -%] + +$$ +); + +INSERT INTO action_trigger.environment(event_def,path) VALUES(CURRVAL('action_trigger.event_definition_id_seq'),'usr'); + + -- Use the ISO 4217 abbreviations for currency codes INSERT INTO acq.currency_type (code, label) VALUES ('USD', oils_i18n_gettext('USD', 'US Dollars', 'acqct', 'label')); INSERT INTO acq.currency_type (code, label) VALUES ('CAN', oils_i18n_gettext('CAN', 'Canadian Dollars', 'acqct', 'label')); 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..9340bd3fb3 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,20 @@ 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 + UNION + SELECT id, request_lib AS billing_ou FROM booking.reservation + ) + 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 +50,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