SF.net SVN: ledger-smb:[3652] trunk/sql/modules

[..hidden..]

Revision: 3652
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3652&view=rev
Author:   einhverfr
Date:     2011-08-10 04:44:17 +0000 (Wed, 10 Aug 2011)

Log Message:
-----------
John Locke's patches for receipt values in voucher list, and date_paid field.

Modified Paths:
--------------
    trunk/sql/modules/Payment.sql
    trunk/sql/modules/Voucher.sql

Modified: trunk/sql/modules/Payment.sql
===================================================================
--- trunk/sql/modules/Payment.sql	2011-08-09 23:17:32 UTC (rev 3651)
+++ trunk/sql/modules/Payment.sql	2011-08-10 04:44:17 UTC (rev 3652)
@@ -530,14 +530,16 @@
         IF in_account_class = 1 THEN
         	EXECUTE $E$
 	        	UPDATE ap 
-		        set paid = paid + (select amount from bulk_payments_in b 
-		         	where b.id = ap.id)
+		        set paid = paid + (select amount from bulk_payments_in b
+		         	where b.id = ap.id),
+                            datepaid = $E$ || quote_literal(in_payment_date) || $E$
 		         where id in (select id from bulk_payments_in) $E$;
         ELSE
         	EXECUTE $E$
 	        	UPDATE ar 
 		        set paid = paid + (select amount from bulk_payments_in b 
-		         	where b.id = ar.id)
+		         	where b.id = ar.id),
+                            datepaid = $E$ || quote_literal(in_payment_date) || $E$
 		         where id in (select id from bulk_payments_in) $E$;
         END IF;
 	EXECUTE $E$ DROP TABLE bulk_payments_in $E$;

Modified: trunk/sql/modules/Voucher.sql
===================================================================
--- trunk/sql/modules/Voucher.sql	2011-08-09 23:17:32 UTC (rev 3651)
+++ trunk/sql/modules/Voucher.sql	2011-08-10 04:44:17 UTC (rev 3652)
@@ -83,14 +83,14 @@
 		UNION ALL
 		SELECT v.id, a.source, a.memo, 
 			v.batch_id, v.trans_id, 
-			CASE WHEN bc.class LIKE 'receipt%' THEN a.amount * -1
-			     ELSE a.amount  END, a.transdate, 
+			CASE WHEN bc.class LIKE 'receipt%' THEN sum(a.amount) * -1
+			     ELSE sum(a.amount)  END, a.transdate, 
 			CASE WHEN bc.class = 'receipt' THEN 'Receipt'
 			     WHEN bc.class = 'receipt_reversal' 
 			     THEN 'Receipt Reversal'
 			END
 		FROM voucher v
-		JOIN acc_trans a ON (v.trans_id = a.trans_id)
+		JOIN acc_trans a ON (v.id = a.voucher_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)
@@ -100,6 +100,8 @@
 		WHERE v.batch_id = in_batch_id 
 			AND a.voucher_id = v.id
 			AND (bc.class like 'receipt%' AND c.link = 'AR')
+		GROUP BY v.id, a.source, cr.meta_number, co.legal_name ,
+                        v.batch_id, v.trans_id, a.transdate, bc.class
 		UNION ALL
 		SELECT v.id, g.reference, g.description, 
 			v.batch_id, v.trans_id,


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.