From 03bfa825f47a35ff8fac5c9a0ffa95107687901c Mon Sep 17 00:00:00 2001 From: einhverfr Date: Thu, 13 Dec 2007 21:27:59 +0000 Subject: Added a routine to sanitize the CGI variables. Needed for some CSV exports routines. git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1978 4979c152-3d1c-0410-bac9-87ea11338e46 --- sql/modules/Payment.sql | 15 ++++++--------- sql/modules/Voucher.sql | 25 +++++++++++++++++++++++++ 2 files changed, 31 insertions(+), 9 deletions(-) (limited to 'sql') diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql index f48bd22f..77a48045 100644 --- a/sql/modules/Payment.sql +++ b/sql/modules/Payment.sql @@ -207,10 +207,6 @@ This then returns a set of contact information with a 2 dimensional array cnsisting of outstanding invoices. $$; -CREATE OR REPLACE FUNCTION payment_create_queue_entry() RETURNS int AS -$$ -$$ LANGUAGE PLPGSQL; - CREATE OR REPLACE FUNCTION payment_bulk_queue (in_transactions numeric[], in_batch_id int, in_source text, in_total numeric, in_ar_ap_accno text, in_cash_accno text, @@ -233,21 +229,22 @@ $$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION job__process_payment(in_job_id int) RETURNS bool AS $$ DECLARE - queue_record RECORD - t_auth_name text, + queue_record RECORD; + t_auth_name text; BEGIN -- TODO: Move the set session authorization into a utility function - SELECT created_by INTO t_auth_name FROM pending_jobs + SELECT entered_by INTO t_auth_name FROM pending_job WHERE id = in_job_id; - EXECUTE 'SET SESSION AUTHORIZATION ' quote_ident(t_auth_name); + EXECUTE 'SET SESSION AUTHORIZATION ' || quote_ident(t_auth_name); 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); + payment_date, account_class) + FROM payments_queue WHERE job_id = in_job_id; END LOOP; UPDATE pending_job SET completed_at = timeofday()::timestamp, diff --git a/sql/modules/Voucher.sql b/sql/modules/Voucher.sql index 5227a848..0633ab5d 100644 --- a/sql/modules/Voucher.sql +++ b/sql/modules/Voucher.sql @@ -221,3 +221,28 @@ BEGIN return currval('batch_id_seq'); END; $$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION batch_delete(in_batch_id int) RETURNS int AS +$$ +DECLARE + t_transaction_ids int[]; +BEGIN + + -- voucher_id is only set in acc_trans on payment/receipt vouchers and + -- their reversals. -CT + DELETE FROM acc_trans WHERE voucher_id IN + (select voucher_id FROM voucher where batch_id = in_batch_id); + + SELECT as_array(trans_id) INTO t_transaction_ids + FROM voucher WHERE batch_id = in_batch_id AND batch_class IN (1, 2, 5); + + DELETE FROM acc_trans WHERE trans_id = ANY(t_transaction_ids); + 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 batch WHERE id = in_batch_id; + DELETE FROM transactions WHERE id = ANY(t_transaction_ids); + + RETURN 1; +END; +$$ language plpgsql; -- cgit v1.2.3