From 5b20c0d8b91b5bc7c6cee549eff6c057aa210d44 Mon Sep 17 00:00:00 2001 From: einhverfr Date: Wed, 19 Dec 2007 17:25:47 +0000 Subject: More batch enhancements git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1982 4979c152-3d1c-0410-bac9-87ea11338e46 --- sql/modules/Payment.sql | 26 ++++++++++++-------------- sql/modules/Voucher.sql | 41 ++++++++++++++++++++++++++--------------- 2 files changed, 38 insertions(+), 29 deletions(-) (limited to 'sql') diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql index 56eb0e9d..48c3b7f3 100644 --- a/sql/modules/Payment.sql +++ b/sql/modules/Payment.sql @@ -312,6 +312,8 @@ DECLARE t_voucher_id int; t_trans_id int; t_amount numeric; + t_ar_ap_id int; + t_cash_id int; BEGIN IF in_batch_id IS NULL THEN -- t_voucher_id := NULL; @@ -322,21 +324,21 @@ BEGIN t_voucher_id := currval('voucher_id_seq'); END IF; + + select id into t_ar_ap_id from chart where accno = in_ar_ap_accno; + select id into t_cash_id from chart where accno = in_cash_accno; + FOR out_count IN - array_lower(in_transactions, 1) .. - array_upper(in_transactions, 1) + array_lower(in_transactions, 1) .. + array_upper(in_transactions, 1) LOOP INSERT INTO acc_trans (trans_id, chart_id, amount, approved, voucher_id, transdate) VALUES (in_transactions[out_count][1], - case when in_account_class = 1 THEN - (SELECT id FROM chart - WHERE accno = in_cash_accno) - WHEN in_account_class = 2 THEN - (SELECT id FROM chart - WHERE accno = in_ar_ap_accno) + case when in_account_class = 1 THEN t_cash_id + WHEN in_account_class = 2 THEN t_ar_ap_id ELSE -1 END, in_transactions[out_count][2], @@ -346,12 +348,8 @@ BEGIN t_voucher_id, in_payment_date), (in_transactions[out_count][1], - case when in_account_class = 1 THEN - (SELECT id FROM chart - WHERE accno = in_ar_ap_accno) - WHEN in_account_class = 2 THEN - (SELECT id FROM chart - WHERE accno = in_cash_accno) + case when in_account_class = 1 THEN t_ar_ap_id + WHEN in_account_class = 2 THEN t_cash_id ELSE -1 END, in_transactions[out_count][2]* -1, diff --git a/sql/modules/Voucher.sql b/sql/modules/Voucher.sql index e27b199c..7abc65e7 100644 --- a/sql/modules/Voucher.sql +++ b/sql/modules/Voucher.sql @@ -11,7 +11,7 @@ END; $$ language plpgsql; -CREATE OR REPLACE FUNCTION batch_post (in_batch text, in_login varchar, in_entered date, +CREATE OR REPLACE FUNCTION batch_update (in_batch text, in_login varchar, in_entered date, in_batch_number text, in_description text, in_id integer) RETURNS integer AS $$ @@ -53,32 +53,43 @@ declare voucher_item record; BEGIN FOR voucher_item IN SELECT v.id, a.invnumber, e.name, v.batch_id, v.trans_id, - a.amount - a.paid, a.transdate, 'Payable' + a.amount, a.transdate, 'Payable' FROM voucher v JOIN ap a ON (v.trans_id = a.id) JOIN entity e ON (a.entity_id = e.id) WHERE v.batch_id = in_batch_id AND v.batch_class = (select id from batch_class - WHERE class = 'payable') + WHERE class = 'ap') UNION SELECT v.id, a.invnumber, e.name, v.batch_id, v.trans_id, - a.amount - a.paid, a.transdate, 'Receivable' + a.amount, a.transdate, 'Receivable' FROM voucher v JOIN ar a ON (v.trans_id = a.id) JOIN entity e ON (a.entity_id = e.id) WHERE v.batch_id = in_batch_id AND v.batch_class = (select id from batch_class - WHERE class = 'receivable') + WHERE class = 'ar') UNION + -- TODO: Add the class labels to the class table. SELECT v.id, a.source, a.memo, v.batch_id, v.trans_id, - a.amount, a.transdate, bc.class + CASE WHEN bc.class LIKE 'payment%' THEN a.amount * -1 + ELSE amount END, a.transdate, + CASE WHEN bc.class = 'payment' THEN 'Payment' + WHEN bc.class = 'receipt' THEN 'Receipt' + WHEN bc.class = 'payment_reversal' + THEN 'Payment Reversal' + WHEN bc.class = 'receipt_reversal' + THEN 'Receipt Reversal' + ELSE 'UNKNOWN' + END FROM voucher v JOIN acc_trans a ON (v.trans_id = a.trans_id) JOIN batch_class bc ON (bc.id = v.batch_class) + JOIN chart c ON (a.chart_id = c.id) WHERE v.batch_id = in_batch_id AND a.voucher_id = v.id - AND bc.class like 'payment%' - OR bc.class like 'receipt%' + AND (bc.class like 'payment%' AND c.link = 'AP') + OR (bc.class like 'receipt%' AND c.link = 'AR') UNION SELECT v.id, g.reference, g.description, v.batch_id, v.trans_id, sum(a.amount), g.transdate, 'gl' @@ -200,15 +211,15 @@ BEGIN AND batch_class = 5); UPDATE acc_trans SET approved = true - WHERE id IN (select trans_id FROM voucher + WHERE trans_id IN (select trans_id FROM voucher WHERE batch_id = in_batch_id AND batch_class IN (3, 4, 7, 8)); UPDATE batch SET approved_on = now(), approved_by = (select entity_id FROM users - WHERE login = SESSION_USER) - WHERE batch_id = in_batch_id; + WHERE username = SESSION_USER) + WHERE id = in_batch_id; RETURN now()::date; END; @@ -263,18 +274,18 @@ BEGIN (select sum(amount) * -1 from acc_trans join chart ON (acc_trans.chart_id = chart.id) where link = 'AR' AND trans_id = ar.id - AND voucher_id NOT IN + AND (voucher_id IS NULL OR voucher_id NOT IN (select id from voucher - WHERE batch_id = in_batch_id)) + WHERE batch_id = in_batch_id))) where id in (select trans_id from acc_trans where voucher_id IN (select id from voucher where batch_id = in_batch_id)); update ap set paid = amount - (select sum(amount) from acc_trans join chart ON (acc_trans.chart_id = chart.id) where link = 'AP' AND trans_id = ap.id - AND voucher_id NOT IN + AND (voucher_id IS NULL OR voucher_id NOT IN (select id from voucher - WHERE batch_id = in_batch_id)) + WHERE batch_id = in_batch_id))) where id in (select trans_id from acc_trans where voucher_id IN (select id from voucher where batch_id = in_batch_id)); -- cgit v1.2.3