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

[..hidden..]

Revision: 5710
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5710&view=rev
Author:   ehuelsmann
Date:     2013-04-07 16:10:43 +0000 (Sun, 07 Apr 2013)
Log Message:
-----------
Clear only those items which are on the exact same day as the item
being added by adding an explicit date restriction.

Modified Paths:
--------------
    trunk/sql/modules/Reconciliation.sql

Modified: trunk/sql/modules/Reconciliation.sql
===================================================================
--- trunk/sql/modules/Reconciliation.sql	2013-04-06 15:24:56 UTC (rev 5709)
+++ trunk/sql/modules/Reconciliation.sql	2013-04-07 16:10:43 UTC (rev 5710)
@@ -254,7 +254,7 @@
                 -- could this be changed to update, if not found insert?
 		SELECT count(*) INTO in_count FROM cr_report_line
 		WHERE scn ilike t_scn AND report_id = in_report_id 
-			AND their_balance = 0;
+			AND their_balance = 0 AND post_date = in_date;
 
 		IF in_count = 0 THEN
 			INSERT INTO cr_report_line
@@ -268,15 +268,18 @@
 			SET their_balance = t_amount, clear_time = in_date,
 				cleared = true
 			WHERE t_scn = scn AND report_id = in_report_id
-				AND their_balance = 0;
+				AND their_balance = 0 AND post_date = in_date;
 		ELSE 
 			SELECT count(*) INTO in_count FROM cr_report_line
 			WHERE t_scn ilike scn AND report_id = in_report_id
-				AND our_value = t_amount and their_balance = 0;
+				AND our_value = t_amount and their_balance = 0
+                                AND post_date = in_date;
 
 			IF in_count = 0 THEN -- no match among many of values
 				SELECT id INTO lid FROM cr_report_line
-                        	WHERE t_scn ilike scn AND report_id = in_report_id
+                        	WHERE t_scn ilike scn
+                                      AND report_id = in_report_id
+                                      AND post_date = in_date
 				ORDER BY our_balance ASC limit 1;
 
 				UPDATE cr_report_line
@@ -294,11 +297,13 @@
 					cleared = true
 				WHERE t_scn = scn AND report_id = in_report_id
                                 	AND our_value = t_amount 
-					AND their_balance = 0;
+					AND their_balance = 0
+                                        AND post_date = in_date;
 			ELSE -- More than one match
 				SELECT id INTO lid FROM cr_report_line
                         	WHERE t_scn ilike scn AND report_id = in_report_id
                                 	AND our_value = t_amount
+                                        AND post_date = in_date
 				ORDER BY id ASC limit 1;
 
 				UPDATE cr_report_line
@@ -313,7 +318,7 @@
 	ELSE -- scn IS NULL, check on amount instead
 		SELECT count(*) INTO in_count FROM cr_report_line
 		WHERE report_id = in_report_id AND our_balance = t_amount
-			AND their_balance = 0 and post_date = in_date
+			AND their_balance = 0 AND post_date = in_date
 			and scn NOT LIKE t_prefix || '%';
 
 		IF in_count = 0 THEN -- no match
@@ -328,13 +333,15 @@
 					trans_type = in_type,
 					clear_time = in_date,
 					cleared = true
-			WHERE report_id = in_report_id AND our_balance = t_amount
-                        	AND their_balance = 0 and
+			WHERE report_id = in_report_id
+                                AND our_balance = t_amount
+                        	AND their_balance = 0
+                                AND post_date = in_date
 				in_scn NOT LIKE t_prefix || '%';
 		ELSE -- more than one match
 			SELECT min(id) INTO lid FROM cr_report_line
 			WHERE report_id = in_report_id AND our_balance = t_amount
-                        	AND their_balance = 0 and post_date = in_date
+                        	AND their_balance = 0 AND post_date = in_date
 				AND scn NOT LIKE t_prefix || '%'
 			LIMIT 1;
 

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