Batch enhancements

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1980 4979c152-3d1c-0410-bac9-87ea11338e46

2 files changed, 91 insertions, 22 deletions

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);