From 32ec9551c2c00aa85c4479451e5d4d55d09679e0 Mon Sep 17 00:00:00 2001 From: einhverfr Date: Mon, 17 Dec 2007 23:22:43 +0000 Subject: Batch enhancements git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1980 4979c152-3d1c-0410-bac9-87ea11338e46 --- sql/modules/Voucher.sql | 70 +++++++++++++++++++++++++++++++++++++++++++------ 1 file changed, 62 insertions(+), 8 deletions(-) (limited to 'sql/modules/Voucher.sql') diff --git a/sql/modules/Voucher.sql b/sql/modules/Voucher.sql index 0633ab5d..2bfc6fcb 100644 --- a/sql/modules/Voucher.sql +++ b/sql/modules/Voucher.sql @@ -101,10 +101,12 @@ $$ language plpgsql; CREATE TYPE batch_list_item AS ( id integer, batch_class text, + control_code text, description text, created_by text, created_on date, - total numeric + transaction_total numeric, + payment_total numeric ); CREATE OR REPLACE FUNCTION @@ -116,9 +118,23 @@ $$ DECLARE out_value batch_list_item; BEGIN FOR out_value IN - SELECT b.id, c.class, b.description, u.username, b.created_on, - sum(coalesce(ar.amount - ar.paid, ap.amount - ap.paid, - al.amount)) AS amount + SELECT b.id, c.class, b.control_code, b.description, u.username, + b.created_on, + sum( + CASE WHEN vc.id = 5 AND al.amount > 0 + THEN al.amount + WHEN vc.id NOT IN (3, 4, 6, 7) + THEN coalesce(ar.amount, ap.amount, 0) + ELSE 0 + END) AS transaction_total, + sum( + CASE WHEN alc.link = 'AR' AND vc.id IN (3,4,6,7) + THEN al.amount + WHEN alc.link = 'AP' AND vc.id IN (3,4,6,7) + THEN al.amount * -1 + ELSE 0 + END + ) AS payment_total FROM batch b JOIN batch_class c ON (b.batch_class_id = c.id) JOIN users u ON (u.entity_id = b.created_by) @@ -128,8 +144,9 @@ BEGIN LEFT JOIN ap ON (vc.id = 1 AND v.trans_id = ap.id) LEFT JOIN acc_trans al ON ((vc.id = 5 AND v.trans_id = al.trans_id) OR - (vc.id IN (3, 4, 7, 8) AND al.voucher_id = v.id) + (vc.id IN (3, 4, 6, 7) AND al.voucher_id = v.id) AND al.amount > 0) + LEFT JOIN chart alc ON (al.chart_id = alc.id) WHERE c.id = coalesce(in_class_id, c.id) AND b.description LIKE '%' || coalesce(in_description, '') || '%' AND @@ -139,7 +156,8 @@ BEGIN approved_on IS NULL) OR (in_approved = true AND approved_on IS NOT NULL) ) - GROUP BY b.id, c.class, b.description, u.username, b.created_on + GROUP BY b.id, c.class, b.description, u.username, b.created_on, + b.control_code HAVING sum(coalesce(ar.amount - ar.paid, ap.amount - ap.paid, al.amount)) @@ -208,6 +226,18 @@ BEGIN END; $$ language plpgsql; +CREATE OR REPLACE FUNCTION batch_get_users() RETURNS SETOF users AS +$$ +DECLARE out_record users%ROWTYPE; +BEGIN + FOR out_record IN + SELECT * from users WHERE entity_id IN (select created_by from batch) + LOOP + RETURN NEXT out_record; + END LOOP; +END; +$$ LANGUAGE PLPGSQL; + CREATE OR REPLACE FUNCTION batch_create( in_batch_number text, in_description text, in_batch_class text) RETURNS int AS $$ @@ -227,12 +257,34 @@ $$ DECLARE t_transaction_ids int[]; BEGIN - + -- Adjust AR/AP tables for payment and payment reversal vouchers -- voucher_id is only set in acc_trans on payment/receipt vouchers and -- their reversals. -CT + update ar set paid = amount - + (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 + (select id from voucher + 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 + (select id from voucher + 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)); + DELETE FROM acc_trans WHERE voucher_id IN - (select voucher_id FROM voucher where batch_id = in_batch_id); + (select id FROM voucher where batch_id = in_batch_id); + -- The rest of this function involves the deletion of actual + -- transactions, vouchers, and batches, and jobs which are in progress. + -- -CT SELECT as_array(trans_id) INTO t_transaction_ids FROM voucher WHERE batch_id = in_batch_id AND batch_class IN (1, 2, 5); @@ -240,6 +292,8 @@ BEGIN DELETE FROM ap WHERE id = ANY(t_transaction_ids); DELETE FROM gl WHERE id = ANY(t_transaction_ids); DELETE FROM voucher WHERE batch_id = in_batch_id; + DELETE FROM payments_queue WHERE batch_id = in_batch_id; + DELETE FROM pending_job WHERE batch_id = in_batch_id; DELETE FROM batch WHERE id = in_batch_id; DELETE FROM transactions WHERE id = ANY(t_transaction_ids); -- cgit v1.2.3