3 SELECT evergreen.upgrade_deps_block_check('1200', :eg_version);
5 CREATE TABLE money.debit_card_payment () INHERITS (money.bnm_desk_payment);
6 ALTER TABLE money.debit_card_payment ADD PRIMARY KEY (id);
7 CREATE INDEX money_debit_card_payment_xact_idx ON money.debit_card_payment (xact);
8 CREATE INDEX money_debit_card_id_idx ON money.debit_card_payment (id);
9 CREATE INDEX money_debit_card_payment_ts_idx ON money.debit_card_payment (payment_ts);
10 CREATE INDEX money_debit_card_payment_accepting_usr_idx ON money.debit_card_payment (accepting_usr);
11 CREATE INDEX money_debit_card_payment_cash_drawer_idx ON money.debit_card_payment (cash_drawer);
13 CREATE TRIGGER mat_summary_add_tgr AFTER INSERT ON money.debit_card_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_add ('debit_card_payment');
14 CREATE TRIGGER mat_summary_upd_tgr AFTER UPDATE ON money.debit_card_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_update ('debit_card_payment');
15 CREATE TRIGGER mat_summary_del_tgr BEFORE DELETE ON money.debit_card_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_del ('debit_card_payment');
17 CREATE OR REPLACE VIEW money.non_drawer_payment_view AS
18 SELECT p.*, c.relname AS payment_type
19 FROM money.bnm_payment p
20 JOIN pg_class c ON p.tableoid = c.oid
21 WHERE c.relname NOT IN ('cash_payment','check_payment','credit_card_payment','debit_card_payment');
23 UPDATE action_trigger.event_definition
26 [%- SET user = target.0.xact.usr -%]
27 To: [%- params.recipient_email || user.email %]
28 From: [%- params.sender_email || default_sender %]
29 Date: [%- date.format(date.now, '%a, %d %b %Y %T -0000', gmt => 1) %]
30 Subject: Payment Receipt
31 Auto-Submitted: auto-generated
34 [%- SET xact_mp_hash = {} -%]
35 [%- FOR mp IN target %][%# Template is hooked around payments, but let us make the receipt focused on transactions -%]
36 [%- SET xact_id = mp.xact.id -%]
37 [%- IF ! xact_mp_hash.defined( xact_id ) -%][%- xact_mp_hash.$xact_id = { 'xact' => mp.xact, 'payments' => [] } -%][%- END -%]
38 [%- xact_mp_hash.$xact_id.payments.push(mp) -%]
40 [%- FOR xact_id IN xact_mp_hash.keys.sort -%]
41 [%- SET xact = xact_mp_hash.$xact_id.xact %]
42 Transaction ID: [% xact_id %]
43 [% IF xact.circulation %][% helpers.get_copy_bib_basics(xact.circulation.target_copy).title %]
44 [% ELSE %]Miscellaneous
47 [%- SET mb_type_hash = {} -%]
48 [%- FOR mb IN xact.billings %][%# Group billings by their btype -%]
49 [%- IF mb.voided == 'f' -%]
50 [%- SET mb_type = mb.btype.id -%]
51 [%- IF ! mb_type_hash.defined( mb_type ) -%][%- mb_type_hash.$mb_type = { 'sum' => 0.00, 'billings' => [] } -%][%- END -%]
52 [%- IF ! mb_type_hash.$mb_type.defined( 'first_ts' ) -%][%- mb_type_hash.$mb_type.first_ts = mb.billing_ts -%][%- END -%]
53 [%- mb_type_hash.$mb_type.last_ts = mb.billing_ts -%]
54 [%- mb_type_hash.$mb_type.sum = mb_type_hash.$mb_type.sum + mb.amount -%]
55 [%- mb_type_hash.$mb_type.billings.push( mb ) -%]
58 [%- FOR mb_type IN mb_type_hash.keys.sort -%]
59 [%- IF mb_type == 1 %][%-# Consolidated view of overdue billings -%]
60 $[% mb_type_hash.$mb_type.sum %] for [% mb_type_hash.$mb_type.billings.0.btype.name %]
61 on [% mb_type_hash.$mb_type.first_ts %] through [% mb_type_hash.$mb_type.last_ts %]
62 [%- ELSE -%][%# all other billings show individually %]
63 [% FOR mb IN mb_type_hash.$mb_type.billings %]
64 $[% mb.amount %] for [% mb.btype.name %] on [% mb.billing_ts %] [% mb.note %]
69 [% FOR mp IN xact_mp_hash.$xact_id.payments %]
70 Payment ID: [% mp.id %]
71 Paid [% mp.amount %] via [% SWITCH mp.payment_type -%]
72 [% CASE "cash_payment" %]cash
73 [% CASE "check_payment" %]check
74 [% CASE "credit_card_payment" %]credit card
75 [%- IF mp.credit_card_payment.cc_number %] ([% mp.credit_card_payment.cc_number %])[% END %]
76 [% CASE "debit_card_payment" %]debit card
77 [% CASE "credit_payment" %]credit
78 [% CASE "forgive_payment" %]forgiveness
79 [% CASE "goods_payment" %]goods
80 [% CASE "work_payment" %]work
81 [%- END %] on [% mp.payment_ts %] [% mp.note %]
85 WHERE id = 29 AND template = $$
87 [%- SET user = target.0.xact.usr -%]
88 To: [%- params.recipient_email || user.email %]
89 From: [%- params.sender_email || default_sender %]
90 Date: [%- date.format(date.now, '%a, %d %b %Y %T -0000', gmt => 1) %]
91 Subject: Payment Receipt
92 Auto-Submitted: auto-generated
95 [%- SET xact_mp_hash = {} -%]
96 [%- FOR mp IN target %][%# Template is hooked around payments, but let us make the receipt focused on transactions -%]
97 [%- SET xact_id = mp.xact.id -%]
98 [%- IF ! xact_mp_hash.defined( xact_id ) -%][%- xact_mp_hash.$xact_id = { 'xact' => mp.xact, 'payments' => [] } -%][%- END -%]
99 [%- xact_mp_hash.$xact_id.payments.push(mp) -%]
101 [%- FOR xact_id IN xact_mp_hash.keys.sort -%]
102 [%- SET xact = xact_mp_hash.$xact_id.xact %]
103 Transaction ID: [% xact_id %]
104 [% IF xact.circulation %][% helpers.get_copy_bib_basics(xact.circulation.target_copy).title %]
105 [% ELSE %]Miscellaneous
108 [%- SET mb_type_hash = {} -%]
109 [%- FOR mb IN xact.billings %][%# Group billings by their btype -%]
110 [%- IF mb.voided == 'f' -%]
111 [%- SET mb_type = mb.btype.id -%]
112 [%- IF ! mb_type_hash.defined( mb_type ) -%][%- mb_type_hash.$mb_type = { 'sum' => 0.00, 'billings' => [] } -%][%- END -%]
113 [%- IF ! mb_type_hash.$mb_type.defined( 'first_ts' ) -%][%- mb_type_hash.$mb_type.first_ts = mb.billing_ts -%][%- END -%]
114 [%- mb_type_hash.$mb_type.last_ts = mb.billing_ts -%]
115 [%- mb_type_hash.$mb_type.sum = mb_type_hash.$mb_type.sum + mb.amount -%]
116 [%- mb_type_hash.$mb_type.billings.push( mb ) -%]
119 [%- FOR mb_type IN mb_type_hash.keys.sort -%]
120 [%- IF mb_type == 1 %][%-# Consolidated view of overdue billings -%]
121 $[% mb_type_hash.$mb_type.sum %] for [% mb_type_hash.$mb_type.billings.0.btype.name %]
122 on [% mb_type_hash.$mb_type.first_ts %] through [% mb_type_hash.$mb_type.last_ts %]
123 [%- ELSE -%][%# all other billings show individually %]
124 [% FOR mb IN mb_type_hash.$mb_type.billings %]
125 $[% mb.amount %] for [% mb.btype.name %] on [% mb.billing_ts %] [% mb.note %]
130 [% FOR mp IN xact_mp_hash.$xact_id.payments %]
131 Payment ID: [% mp.id %]
132 Paid [% mp.amount %] via [% SWITCH mp.payment_type -%]
133 [% CASE "cash_payment" %]cash
134 [% CASE "check_payment" %]check
135 [% CASE "credit_card_payment" %]credit card
136 [%- IF mp.credit_card_payment.cc_number %] ([% mp.credit_card_payment.cc_number %])[% END %]
137 [% CASE "credit_payment" %]credit
138 [% CASE "forgive_payment" %]forgiveness
139 [% CASE "goods_payment" %]goods
140 [% CASE "work_payment" %]work
141 [%- END %] on [% mp.payment_ts %] [% mp.note %]