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/Payment.sql | 43 ++++++++++++++++++++---------- sql/modules/Voucher.sql | 70 +++++++++++++++++++++++++++++++++++++++++++------ 2 files changed, 91 insertions(+), 22 deletions(-) (limited to 'sql') diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql index 77a48045..56eb0e9d 100644 --- a/sql/modules/Payment.sql +++ b/sql/modules/Payment.sql @@ -149,12 +149,12 @@ BEGIN JOIN entity_credit_account c ON (e.id = c.entity_id) JOIN (SELECT id, invnumber, transdate, amount, entity_id, paid, curr, 1 as invoice_class, - entity_credit_account + entity_credit_account, on_hold FROM ap UNION SELECT id, invnumber, transdate, amount, entity_id, paid, curr, 2 as invoice_class, - entity_credit_account + entity_credit_account, on_hold FROM ar ORDER BY transdate ) a USING (entity_id) @@ -170,6 +170,7 @@ BEGIN AND a.curr = in_currency AND a.entity_credit_account = c.id AND a.amount - a.paid <> 0 + AND NOT a.on_hold AND NOT (t.locked_by IS NOT NULL AND t.locked_by IN (select "session_id" FROM "session" WHERE users_id IN @@ -231,6 +232,7 @@ RETURNS bool AS $$ DECLARE queue_record RECORD; t_auth_name text; + t_counter int; BEGIN -- TODO: Move the set session authorization into a utility function SELECT entered_by INTO t_auth_name FROM pending_job @@ -238,18 +240,30 @@ BEGIN EXECUTE 'SET SESSION AUTHORIZATION ' || quote_ident(t_auth_name); - FOR queue_record IN - SELECT * from payments_queue WHERE job_id = in_job_id + t_counter := 0; + + FOR queue_record IN + SELECT * + FROM payments_queue WHERE job_id = in_job_id LOOP PERFORM payment_bulk_post - (transactions, batch_id, source, total, ar_ap_accno, cash_accno, - payment_date, account_class) - FROM payments_queue WHERE job_id = in_job_id; - END LOOP; - UPDATE pending_job - SET completed_at = timeofday()::timestamp, - success = true - WHERE id = in_job_id; + (queue_record.transactions, queue_record.batch_id, + queue_record.source, queue_record.total, + queue_record.ar_ap_accno, + queue_record.cash_accno, + queue_record.payment_date, + queue_record.account_class); + + t_counter := t_counter + 1; + RAISE NOTICE 'Processed record %, starting transaction %', + t_counter, queue_record.transactions[1][1]; + END LOOP; + DELETE FROM payments_queue WHERE job_id = in_job_id; + + UPDATE pending_job + SET completed_at = timeofday()::timestamp, + success = true + WHERE id = in_job_id; RETURN TRUE; END; $$ language plpgsql; @@ -259,7 +273,7 @@ RETURNS int AS $$ BEGIN INSERT INTO pending_job (batch_class, batch_id) - VALUES (in_batch_class, in_batch_id); + VALUES (coalesce(in_batch_class, 3), in_batch_id); RETURN currval('pending_job_id_seq'); END; @@ -300,7 +314,8 @@ DECLARE t_amount numeric; BEGIN IF in_batch_id IS NULL THEN - t_voucher_id := NULL; + -- t_voucher_id := NULL; + RAISE EXCEPTION 'Bulk Post Must be from Batch!'; ELSE INSERT INTO voucher (batch_id, batch_class, trans_id) values (in_batch_id, 3, in_transactions[1][1]); 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