From 7f98ae06bc27dbe4df64b82184d09b67bfe1e779 Mon Sep 17 00:00:00 2001 From: phasefx Date: Tue, 6 Jul 2010 16:00:08 +0000 Subject: [PATCH] augment these payment receipt templates with more info git-svn-id: svn://svn.open-ils.org/ILS/trunk@16852 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/950.data.seed-values.sql | 156 +++++++++++++++------ .../upgrade/0326.data.payment_receipt_format.sql | 152 ++++++++++++++++++++ 3 files changed, 270 insertions(+), 40 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0326.data.payment_receipt_format.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 989a07fa37..bd68c08806 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -68,7 +68,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0325'); -- miker +INSERT INTO config.upgrade_log (version) VALUES ('0326'); -- phasefx CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, 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 316ae1b479..dc46bf7130 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -5200,6 +5200,7 @@ INSERT INTO action_trigger.environment ( ; -- 0289.data.payment_receipt_format.sql +-- 0326.data.payment_receipt_format.sql INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ( 'money.format.payment_receipt.email', @@ -5253,23 +5254,60 @@ To: [%- params.recipient_email || user.email %] From: [%- params.sender_email || default_sender %] Subject: Payment Receipt - [% FOR mp IN target %] - Payment ID: [% mp.id %] - Paid [% mp.amount %] via [% SWITCH mp.payment_type %] - [% CASE "cash_payment" %]cash - [% CASE "check_payment" %]check - [% CASE "credit_card_payment" %]credit card - [% CASE "credit_payment" %]credit - [% CASE "forgive_payment" %]forgiveness - [% CASE "goods_payment" %]goods - [% CASE "work_payment" %]work - [% END %] on [% mp.payment_ts %] for - [% IF mp.xact.circulation %] - [% helpers.get_copy_bib_basics(mp.xact.circulation.target_copy).title %] - [% ELSE %] - grocery - [% END %] +[% date.format -%] +[%- SET xact_mp_hash = {} -%] +[%- FOR mp IN target %][%# Template is hooked around payments, but let us make the receipt focused on transactions -%] + [%- SET xact_id = mp.xact.id -%] + [%- IF ! xact_mp_hash.defined( xact_id ) -%][%- xact_mp_hash.$xact_id = { 'xact' => mp.xact, 'payments' => [] } -%][%- END -%] + [%- xact_mp_hash.$xact_id.payments.push(mp) -%] +[%- END -%] +[%- FOR xact_id IN xact_mp_hash.keys.sort -%] + [%- SET xact = xact_mp_hash.$xact_id.xact %] +Transaction ID: [% xact_id %] + [% IF xact.circulation %][% helpers.get_copy_bib_basics(xact.circulation.target_copy).title %] + [% ELSE %]Miscellaneous [% END %] + Line item billings: + [%- SET mb_type_hash = {} -%] + [%- FOR mb IN xact.billings %][%# Group billings by their btype -%] + [%- IF mb.voided == 'f' -%] + [%- SET mb_type = mb.btype.id -%] + [%- IF ! mb_type_hash.defined( mb_type ) -%][%- mb_type_hash.$mb_type = { 'sum' => 0.00, 'billings' => [] } -%][%- END -%] + [%- IF ! mb_type_hash.$mb_type.defined( 'first_ts' ) -%][%- mb_type_hash.$mb_type.first_ts = mb.billing_ts -%][%- END -%] + [%- mb_type_hash.$mb_type.last_ts = mb.billing_ts -%] + [%- mb_type_hash.$mb_type.sum = mb_type_hash.$mb_type.sum + mb.amount -%] + [%- mb_type_hash.$mb_type.billings.push( mb ) -%] + [%- END -%] + [%- END -%] + [%- FOR mb_type IN mb_type_hash.keys.sort -%] + [%- IF mb_type == 1 %][%-# Consolidated view of overdue billings -%] + $[% mb_type_hash.$mb_type.sum %] for [% mb_type_hash.$mb_type.billings.0.btype.name %] + on [% mb_type_hash.$mb_type.first_ts %] through [% mb_type_hash.$mb_type.last_ts %] + [%- ELSE -%][%# all other billings show individually %] + [% FOR mb IN mb_type_hash.$mb_type.billings %] + $[% mb.amount %] for [% mb.btype.name %] on [% mb.billing_ts %] [% mb.note %] + [% END %] + [% END %] + [% END %] + Line item payments: + [% FOR mp IN xact_mp_hash.$xact_id.payments %] + Payment ID: [% mp.id %] + Paid [% mp.amount %] via [% SWITCH mp.payment_type -%] + [% CASE "cash_payment" %]cash + [% CASE "check_payment" %]check + [% CASE "credit_card_payment" %]credit card ( + [%- SET cc_chunks = mp.credit_card_payment.cc_number.replace(' ','').chunk(4); -%] + [%- cc_chunks.slice(0, -1+cc_chunks.max).join.replace('\S','X') -%] + [% cc_chunks.last -%] + exp [% mp.credit_card_payment.expire_month %]/[% mp.credit_card_payment.expire_year -%] + ) + [% CASE "credit_payment" %]credit + [% CASE "forgive_payment" %]forgiveness + [% CASE "goods_payment" %]goods + [% CASE "work_payment" %]work + [%- END %] on [% mp.payment_ts %] [% mp.note %] + [% END %] +[% END %] $$ ) ,( @@ -5283,31 +5321,65 @@ $$ 'xact.usr', 'print-on-demand', $$ -[%- USE date -%] -[%- SET user = target.0.xact.usr -%] -
- -
[% date.format %]
-
- +[%- USE date -%][%- SET user = target.0.xact.usr -%] +
+
[% date.format %]

    - [% FOR mp IN target %] -
  1. - Payment ID: [% mp.id %] - Paid [% mp.amount %] via [% SWITCH mp.payment_type %] - [% CASE "cash_payment" %]cash - [% CASE "check_payment" %]check - [% CASE "credit_card_payment" %]credit card - [% CASE "credit_payment" %]credit - [% CASE "forgive_payment" %]forgiveness - [% CASE "goods_payment" %]goods - [% CASE "work_payment" %]work - [% END %] on [% mp.payment_ts %] for - [% IF mp.xact.circulation %] - [% helpers.get_copy_bib_basics(mp.xact.circulation.target_copy).title %] - [% ELSE %] - grocery + [% SET xact_mp_hash = {} %] + [% FOR mp IN target %][%# Template is hooked around payments, but let us make the receipt focused on transactions %] + [% SET xact_id = mp.xact.id %] + [% IF ! xact_mp_hash.defined( xact_id ) %][% xact_mp_hash.$xact_id = { 'xact' => mp.xact, 'payments' => [] } %][% END %] + [% xact_mp_hash.$xact_id.payments.push(mp) %] + [% END %] + [% FOR xact_id IN xact_mp_hash.keys.sort %] + [% SET xact = xact_mp_hash.$xact_id.xact %] +
  2. Transaction ID: [% xact_id %] + [% IF xact.circulation %][% helpers.get_copy_bib_basics(xact.circulation.target_copy).title %] + [% ELSE %]Miscellaneous [% END %] + Line item billings:
      + [% SET mb_type_hash = {} %] + [% FOR mb IN xact.billings %][%# Group billings by their btype %] + [% IF mb.voided == 'f' %] + [% SET mb_type = mb.btype.id %] + [% IF ! mb_type_hash.defined( mb_type ) %][% mb_type_hash.$mb_type = { 'sum' => 0.00, 'billings' => [] } %][% END %] + [% IF ! mb_type_hash.$mb_type.defined( 'first_ts' ) %][% mb_type_hash.$mb_type.first_ts = mb.billing_ts %][% END %] + [% mb_type_hash.$mb_type.last_ts = mb.billing_ts %] + [% mb_type_hash.$mb_type.sum = mb_type_hash.$mb_type.sum + mb.amount %] + [% mb_type_hash.$mb_type.billings.push( mb ) %] + [% END %] + [% END %] + [% FOR mb_type IN mb_type_hash.keys.sort %] +
    1. [% IF mb_type == 1 %][%# Consolidated view of overdue billings %] + $[% mb_type_hash.$mb_type.sum %] for [% mb_type_hash.$mb_type.billings.0.btype.name %] + on [% mb_type_hash.$mb_type.first_ts %] through [% mb_type_hash.$mb_type.last_ts %] + [% ELSE %][%# all other billings show individually %] + [% FOR mb IN mb_type_hash.$mb_type.billings %] + $[% mb.amount %] for [% mb.btype.name %] on [% mb.billing_ts %] [% mb.note %] + [% END %] + [% END %]
      2. + [% END %] +
    + Line item payments:
      + [% FOR mp IN xact_mp_hash.$xact_id.payments %] +
    1. Payment ID: [% mp.id %] + Paid [% mp.amount %] via [% SWITCH mp.payment_type -%] + [% CASE "cash_payment" %]cash + [% CASE "check_payment" %]check + [% CASE "credit_card_payment" %]credit card ( + [%- SET cc_chunks = mp.credit_card_payment.cc_number.replace(' ','').chunk(4); -%] + [%- cc_chunks.slice(0, -1+cc_chunks.max).join.replace('\S','X') -%] + [% cc_chunks.last -%] + exp [% mp.credit_card_payment.expire_month %]/[% mp.credit_card_payment.expire_year -%] + ) + [% CASE "credit_payment" %]credit + [% CASE "forgive_payment" %]forgiveness + [% CASE "goods_payment" %]goods + [% CASE "work_payment" %]work + [%- END %] on [% mp.payment_ts %] [% mp.note %] +
      2. + [% END %] +
    3. [% END %]
@@ -5325,11 +5397,17 @@ INSERT INTO action_trigger.environment ( ,( 29, 'xact.grocery' ) ,( 29, 'xact.circulation' ) ,( 29, 'xact.summary' ) + ,( 29, 'credit_card_payment') + ,( 29, 'xact.billings') + ,( 29, 'xact.billings.btype') ,( 30, 'xact') ,( 30, 'xact.usr') ,( 30, 'xact.grocery' ) ,( 30, 'xact.circulation' ) ,( 30, 'xact.summary' ) + ,( 30, 'credit_card_payment') + ,( 30, 'xact.billings') + ,( 30, 'xact.billings.btype') ; -- 0294.data.bre_format.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/0326.data.payment_receipt_format.sql b/Open-ILS/src/sql/Pg/upgrade/0326.data.payment_receipt_format.sql new file mode 100644 index 0000000000..67eb01f631 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0326.data.payment_receipt_format.sql @@ -0,0 +1,152 @@ +BEGIN; + +-- Replaces and augments some of the data from 0289.data.payment_receipt_format.sql +INSERT INTO config.upgrade_log (version) VALUES ('0326'); -- phasefx + +UPDATE action_trigger.event_definition SET template = +$$ +[%- USE date -%] +[%- SET user = target.0.xact.usr -%] +To: [%- params.recipient_email || user.email %] +From: [%- params.sender_email || default_sender %] +Subject: Payment Receipt + +[% date.format -%] +[%- SET xact_mp_hash = {} -%] +[%- FOR mp IN target %][%# Template is hooked around payments, but let us make the receipt focused on transactions -%] + [%- SET xact_id = mp.xact.id -%] + [%- IF ! xact_mp_hash.defined( xact_id ) -%][%- xact_mp_hash.$xact_id = { 'xact' => mp.xact, 'payments' => [] } -%][%- END -%] + [%- xact_mp_hash.$xact_id.payments.push(mp) -%] +[%- END -%] +[%- FOR xact_id IN xact_mp_hash.keys.sort -%] + [%- SET xact = xact_mp_hash.$xact_id.xact %] +Transaction ID: [% xact_id %] + [% IF xact.circulation %][% helpers.get_copy_bib_basics(xact.circulation.target_copy).title %] + [% ELSE %]Miscellaneous + [% END %] + Line item billings: + [%- SET mb_type_hash = {} -%] + [%- FOR mb IN xact.billings %][%# Group billings by their btype -%] + [%- IF mb.voided == 'f' -%] + [%- SET mb_type = mb.btype.id -%] + [%- IF ! mb_type_hash.defined( mb_type ) -%][%- mb_type_hash.$mb_type = { 'sum' => 0.00, 'billings' => [] } -%][%- END -%] + [%- IF ! mb_type_hash.$mb_type.defined( 'first_ts' ) -%][%- mb_type_hash.$mb_type.first_ts = mb.billing_ts -%][%- END -%] + [%- mb_type_hash.$mb_type.last_ts = mb.billing_ts -%] + [%- mb_type_hash.$mb_type.sum = mb_type_hash.$mb_type.sum + mb.amount -%] + [%- mb_type_hash.$mb_type.billings.push( mb ) -%] + [%- END -%] + [%- END -%] + [%- FOR mb_type IN mb_type_hash.keys.sort -%] + [%- IF mb_type == 1 %][%-# Consolidated view of overdue billings -%] + $[% mb_type_hash.$mb_type.sum %] for [% mb_type_hash.$mb_type.billings.0.btype.name %] + on [% mb_type_hash.$mb_type.first_ts %] through [% mb_type_hash.$mb_type.last_ts %] + [%- ELSE -%][%# all other billings show individually %] + [% FOR mb IN mb_type_hash.$mb_type.billings %] + $[% mb.amount %] for [% mb.btype.name %] on [% mb.billing_ts %] [% mb.note %] + [% END %] + [% END %] + [% END %] + Line item payments: + [% FOR mp IN xact_mp_hash.$xact_id.payments %] + Payment ID: [% mp.id %] + Paid [% mp.amount %] via [% SWITCH mp.payment_type -%] + [% CASE "cash_payment" %]cash + [% CASE "check_payment" %]check + [% CASE "credit_card_payment" %]credit card ( + [%- SET cc_chunks = mp.credit_card_payment.cc_number.replace(' ','').chunk(4); -%] + [%- cc_chunks.slice(0, -1+cc_chunks.max).join.replace('\S','X') -%] + [% cc_chunks.last -%] + exp [% mp.credit_card_payment.expire_month %]/[% mp.credit_card_payment.expire_year -%] + ) + [% CASE "credit_payment" %]credit + [% CASE "forgive_payment" %]forgiveness + [% CASE "goods_payment" %]goods + [% CASE "work_payment" %]work + [%- END %] on [% mp.payment_ts %] [% mp.note %] + [% END %] +[% END %] +$$ +WHERE id = 29; + +UPDATE action_trigger.event_definition SET template = +$$ +[%- USE date -%][%- SET user = target.0.xact.usr -%] +
+
[% date.format %]

+
    + [% SET xact_mp_hash = {} %] + [% FOR mp IN target %][%# Template is hooked around payments, but let us make the receipt focused on transactions %] + [% SET xact_id = mp.xact.id %] + [% IF ! xact_mp_hash.defined( xact_id ) %][% xact_mp_hash.$xact_id = { 'xact' => mp.xact, 'payments' => [] } %][% END %] + [% xact_mp_hash.$xact_id.payments.push(mp) %] + [% END %] + [% FOR xact_id IN xact_mp_hash.keys.sort %] + [% SET xact = xact_mp_hash.$xact_id.xact %] +
  1. Transaction ID: [% xact_id %] + [% IF xact.circulation %][% helpers.get_copy_bib_basics(xact.circulation.target_copy).title %] + [% ELSE %]Miscellaneous + [% END %] + Line item billings:
      + [% SET mb_type_hash = {} %] + [% FOR mb IN xact.billings %][%# Group billings by their btype %] + [% IF mb.voided == 'f' %] + [% SET mb_type = mb.btype.id %] + [% IF ! mb_type_hash.defined( mb_type ) %][% mb_type_hash.$mb_type = { 'sum' => 0.00, 'billings' => [] } %][% END %] + [% IF ! mb_type_hash.$mb_type.defined( 'first_ts' ) %][% mb_type_hash.$mb_type.first_ts = mb.billing_ts %][% END %] + [% mb_type_hash.$mb_type.last_ts = mb.billing_ts %] + [% mb_type_hash.$mb_type.sum = mb_type_hash.$mb_type.sum + mb.amount %] + [% mb_type_hash.$mb_type.billings.push( mb ) %] + [% END %] + [% END %] + [% FOR mb_type IN mb_type_hash.keys.sort %] +
    1. [% IF mb_type == 1 %][%# Consolidated view of overdue billings %] + $[% mb_type_hash.$mb_type.sum %] for [% mb_type_hash.$mb_type.billings.0.btype.name %] + on [% mb_type_hash.$mb_type.first_ts %] through [% mb_type_hash.$mb_type.last_ts %] + [% ELSE %][%# all other billings show individually %] + [% FOR mb IN mb_type_hash.$mb_type.billings %] + $[% mb.amount %] for [% mb.btype.name %] on [% mb.billing_ts %] [% mb.note %] + [% END %] + [% END %]
      2. + [% END %] +
    + Line item payments:
      + [% FOR mp IN xact_mp_hash.$xact_id.payments %] +
    1. Payment ID: [% mp.id %] + Paid [% mp.amount %] via [% SWITCH mp.payment_type -%] + [% CASE "cash_payment" %]cash + [% CASE "check_payment" %]check + [% CASE "credit_card_payment" %]credit card ( + [%- SET cc_chunks = mp.credit_card_payment.cc_number.replace(' ','').chunk(4); -%] + [%- cc_chunks.slice(0, -1+cc_chunks.max).join.replace('\S','X') -%] + [% cc_chunks.last -%] + exp [% mp.credit_card_payment.expire_month %]/[% mp.credit_card_payment.expire_year -%] + ) + [% CASE "credit_payment" %]credit + [% CASE "forgive_payment" %]forgiveness + [% CASE "goods_payment" %]goods + [% CASE "work_payment" %]work + [%- END %] on [% mp.payment_ts %] [% mp.note %] +
      2. + [% END %] +
    +
    2. + [% END %] +
+
+$$ +WHERE id = 30; + +INSERT INTO action_trigger.environment ( + event_def, + path + ) VALUES -- for fleshing mp objects + ( 29, 'credit_card_payment') + ,( 29, 'xact.billings') + ,( 29, 'xact.billings.btype') + ,( 30, 'credit_card_payment') + ,( 30, 'xact.billings') + ,( 30, 'xact.billings.btype') +; + +COMMIT; + -- 2.11.0