Some bulk payment enhancements

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

2 files changed, 52 insertions, 34 deletions

diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql
index 48c3b7f3..0dabd003 100644
--- a/sql/modules/Payment.sql
+++ b/sql/modules/Payment.sql
@@ -113,7 +113,8 @@ CREATE TYPE payment_contact_invoice AS (
 	contact_name text,
 	account_number text,
 	total_due numeric,
-	invoices text[]
+	invoices text[],
+        has_vouchers int
 );
 
 CREATE OR REPLACE FUNCTION payment_get_all_contact_invoices
@@ -142,25 +143,30 @@ BEGIN
 		                   THEN 0
 		                   ELSE (a.amount - coalesce(a.paid, 0)) * coalesce(c.discount, 0) / 100
 		              END))::text]]),
+                              sum(case when v.batch_id = in_batch_id then 1
+		                  else 0 END),
 		              bool_and(lock_record(a.id, (select max(session_id) 				FROM "session" where users_id = (
 					select id from users WHERE username =
 					SESSION_USER))))
+                           
 		    FROM entity e
 		    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, on_hold
 		            FROM ap
+			   WHERE in_account_class = 1
 		           UNION
 		          SELECT id, invnumber, transdate, amount, entity_id,
 		                 paid, curr, 2 as invoice_class, 
 		                 entity_credit_account, on_hold
 		            FROM ar
+			   WHERE in_account_class = 2
 			ORDER BY transdate
 		         ) a USING (entity_id)
 		    JOIN transactions t ON (a.id = t.id)
-		   WHERE a.id IN (select voucher.trans_id FROM voucher
-		                          WHERE batch_id = in_batch_id)
+		  LEFT JOIN voucher v ON (v.trans_id = a.id)
+		   WHERE v.batch_id = in_batch_id
 		          OR (a.invoice_class = in_account_class
 			 AND c.business_id = 
 				coalesce(in_business_id, c.business_id)
@@ -184,6 +190,8 @@ BEGIN
 		                    ))
 		GROUP BY c.id, e.name, c.meta_number, c.threshold
 		  HAVING sum(a.amount - a.paid) > c.threshold
+			OR sum(case when v.batch_id = in_batch_id then 1
+                                  else 0 END) > 0
 	LOOP
 		RETURN NEXT payment_item;
 	END LOOP;
@@ -334,7 +342,7 @@ BEGIN
 	LOOP
 		INSERT INTO acc_trans 
 			(trans_id, chart_id, amount, approved, voucher_id,
-			transdate)
+			transdate, source)
 		VALUES
 			(in_transactions[out_count][1], 
 				case when in_account_class = 1 THEN t_cash_id
@@ -345,7 +353,7 @@ BEGIN
 	
 				CASE WHEN t_voucher_id IS NULL THEN true
 				ELSE false END,
-				t_voucher_id, in_payment_date),
+				t_voucher_id, in_payment_date, in_source),
 
 			(in_transactions[out_count][1], 
 				case when in_account_class = 1 THEN t_ar_ap_id
@@ -356,7 +364,7 @@ BEGIN
 
 				CASE WHEN t_voucher_id IS NULL THEN true
 				ELSE false END,
-				t_voucher_id, in_payment_date);
+				t_voucher_id, in_payment_date, in_source);
 		UPDATE ap 
 		set paid = paid +in_transactions[out_count][2]
 		where id =in_transactions[out_count][1];
@@ -565,28 +573,18 @@ BEGIN
 			max(cc.description), max(ac.id), max(ac.accno), 
 			max(ac.description)
 		FROM acc_trans at
-		JOIN entity_credit_account ec ON
-			(at.trans_id IN 
-				(select id FROM ar 
-				WHERE in_account_class = 2
-					AND entity_credit_account =
-						(SELECT id 
-						FROM entity_credit_account
-						WHERE meta_number 
-							= in_meta_number
-							AND entity_class = 
-							in_account_class)
-				UNION
-				SELECT id FROM ap
-				WHERE in_account_class = 1 AND
-					entity_credit_account = 
-						(select id 
-						FROM entity_credit_account
-						WHERE meta_number 
-							= in_meta_number
-							AND entity_class = 
-							in_account_class)))
-				
+		JOIN (select id, entity_credit_account 
+			FROM ar 
+			WHERE in_account_class = 2
+			UNION
+			SELECT id, entity_credit_account
+			FROM ap
+			WHERE in_account_class = 1) arap
+			ON (arap.id = at.trans_id)
+
+		JOIN entity_credit_account ec ON (
+			ec.entity_class = in_account_class
+			AND arap.entity_credit_account = ec.id)
 		JOIN company c ON (ec.entity_id = c.entity_id)
 		LEFT JOIN chart cc ON (at.chart_id = cc.id AND
 			cc.link LIKE '%paid%')
diff --git a/sql/modules/Voucher.sql b/sql/modules/Voucher.sql
index 7abc65e7..b5dc76b1 100644
--- a/sql/modules/Voucher.sql
+++ b/sql/modules/Voucher.sql
@@ -71,25 +71,45 @@ BEGIN
 					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, 
+		SELECT v.id, a.source,
+			cr.meta_number || '--'  || co.legal_name , 
+			v.batch_id, v.trans_id, 
 			CASE WHEN bc.class LIKE 'payment%' THEN a.amount * -1
-			     ELSE amount  END, a.transdate, 
+			     ELSE a.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'
+			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)
+		JOIN ap ON (ap.id = a.trans_id)
+		JOIN entity_credit_account cr 
+			ON (ap.entity_credit_account = cr.id)
+		JOIN company co ON (cr.entity_id = co.entity_id)
+		WHERE v.batch_id = in_batch_id 
+			AND a.voucher_id = v.id
+			AND (bc.class like 'payment%' AND c.link = 'AP')
+		UNION
+		SELECT v.id, a.source, a.memo, v.batch_id, v.trans_id, 
+			CASE WHEN bc.class LIKE 'payment%' THEN a.amount * -1
+			     ELSE a.amount  END, a.transdate, 
+			CASE WHEN bc.class = 'receipt' THEN 'Receipt'
 			     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)
+		JOIN ar ON (ar.id = a.trans_id)
+		JOIN entity_credit_account cr 
+			ON (ar.entity_credit_account = cr.id)
+		JOIN company co ON (cr.entity_id = co.entity_id)
 		WHERE v.batch_id = in_batch_id 
 			AND a.voucher_id = v.id
-			AND (bc.class like 'payment%' AND c.link = 'AP')
-			OR (bc.class like 'receipt%' AND c.link = 'AR')
+			AND (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'