Merge lp:~oddbloke/openobject-addons/623334 into lp:openobject-addons/5.0

[Dan Watkins]

Fixes bug #623334, which involves Partner Account reports in the account module showing incorrect figures.

Essentially, wherever credit and debit are calculated, we now join with the account_moves that the account_move_lines are part of, to ensure that they aren't in Draft status.

In the Partner Ledger report, I've then consolidated the shared SQL strings into _get_line_sql, _get_credit_sql and _get_debit_sql.

Apologies for the proposal spam, I haven't quite got the hang of this part of LP yet.

[Dan Watkins (oddbloke)]

So this doesn't fully fix bug #623334, that was a mis-type. It does, however, fix the Partner Ledger report problem.

[Dan Watkins (oddbloke)]

The above comment is no longer correct, I think this does fix bug #623334 fully.

[Dimitri John Ledkov (ex-credativ) (dle-credativ)]

How come this hasn't been reviewed?

Unmerged revisions

2827. By Dan Watkins on 2010-08-24

Fix aged_trial_balance report.

2826. By Dan Watkins on 2010-08-24

Fix individual lines in partner_balance report.

2825. By Dan Watkins on 2010-08-24

Fix grand total display in the partner_balance report.

2824. By Dan Watkins on 2010-08-24

Remove unused _sum_sdebit and _sum_scredit methods from partner_balance report.

2823. By Dan Watkins on 2010-08-24

Fix third party ledger report.

=== modified file 'account/report/aged_trial_balance.py'
--- account/report/aged_trial_balance.py	2010-06-14 10:58:21 +0000
+++ account/report/aged_trial_balance.py	2010-08-24 13:59:55 +0000
@@ -79,7 +79,7 @@
         # This dictionary will store the debit-credit for all partners, using partner_id as key.
         totals = {}
         self.cr.execute("""SELECT partner_id, SUM(debit-credit)
-                    FROM account_move_line AS line, account_account
+                    FROM (account_move_line AS line LEFT JOIN account_move m ON (line.move_id = m.id)), account_account
                     WHERE (line.account_id = account_account.id)
                     AND (account_account.type IN %s)
                     AND (partner_id in %s)
@@ -87,6 +87,7 @@
                     OR (reconcile_id IN (SELECT recon.id FROM account_move_reconcile AS recon WHERE recon.create_date > %s )))
                     AND (account_account.company_id = %s)
                     AND account_account.active
+                    AND m.state = 'posted'
                     GROUP BY partner_id""" , (
                         self.ACCOUNT_TYPE, partner_ids,
                         form['date1'], form['company_id']))

=== modified file 'account/report/partner_balance.py'
--- account/report/partner_balance.py	2010-08-17 07:00:51 +0000
+++ account/report/partner_balance.py	2010-08-24 13:59:55 +0000
@@ -37,8 +37,6 @@
             'sum_debit': self._sum_debit,
             'sum_credit': self._sum_credit,
             'sum_litige': self._sum_litige,
-            'sum_sdebit': self._sum_sdebit,
-            'sum_scredit': self._sum_scredit,
             'solde_debit': self._solde_balance_debit,
             'solde_credit': self._solde_balance_credit,
             'get_company': self._get_company,
@@ -213,9 +211,11 @@
                         ") AS enlitige " \
                 "FROM account_move_line l LEFT JOIN res_partner p ON (l.partner_id=p.id) " \
                 "JOIN account_account ac ON (l.account_id = ac.id)" \
+                "LEFT JOIN account_move m ON (l.move_id = m.id) " \
                 "WHERE ac.type IN %s " \
                     "AND l.date IN %s " \
                     "AND ac.company_id = %s " \
+                    "AND m.state = 'posted' " \
                 "GROUP BY p.id, p.ref, p.name,l.account_id,ac.name,ac.code " \
                 "ORDER BY l.account_id,p.name",
                 (tuple(self.date_lst), self.ACCOUNT_TYPE, tuple(self.date_lst), data['form']['company_id']))
@@ -355,8 +355,10 @@
             self.cr.execute(
                     "SELECT sum(debit) " \
                     "FROM account_move_line AS l " \
+                    "LEFT JOIN account_move m ON (l.move_id = m.id) " \
                     "WHERE l.account_id IN (" + self.account_ids + ") " \
-                        "AND l.date IN %s",
+                        "AND l.date IN %s" \
+                        "AND m.state = 'posted' ",
                 (tuple(self.date_lst),))
             temp_res = float(self.cr.fetchone()[0] or 0.0)
         result_tmp = result_tmp + temp_res
@@ -374,8 +376,10 @@
             self.cr.execute(
                     "SELECT sum(credit) " \
                     "FROM account_move_line AS l " \
+                    "LEFT JOIN account_move m ON (l.move_id = m.id) " \
                     "WHERE l.account_id IN (" + self.account_ids + ") " \
-                        "AND l.date IN %s",
+                        "AND l.date IN %s" \
+                        "AND m.state = 'posted' ",
                 (tuple(self.date_lst),))
             temp_res = float(self.cr.fetchone()[0] or 0.0)
         result_tmp = result_tmp + temp_res
@@ -401,61 +405,6 @@
 
         return result_tmp
 
-    def _sum_sdebit(self,data):
-        if not self.ids:
-            return 0.0
-        account_move_line_obj = pooler.get_pool(self.cr.dbname).get('account.move.line')
-        result_tmp = 0.0
-        a = 0.0
-        if self.date_lst:
-            self.cr.execute(
-                "SELECT CASE WHEN sum(debit) > sum(credit) " \
-                        "THEN sum(debit) - sum(credit) " \
-                        "ELSE 0 " \
-                    "END " \
-                "FROM account_move_line AS l " \
-                "WHERE l.account_id IN (" + self.account_ids + ") " \
-                    "AND l.date IN %s " \
-                "GROUP BY l.partner_id",
-                (tuple(self.date_lst),))
-            a = self.cr.fetchone()[0]
-
-            if self.cr.fetchone() != None:
-                result_tmp = result_tmp + (a or 0.0)
-            else:
-                result_tmp = 0.0
-
-        return result_tmp
-
-    def _sum_scredit(self,data):
-
-        if not self.ids:
-            return 0.0
-        account_move_line_obj = pooler.get_pool(self.cr.dbname).get('account.move.line')
-
-        result_tmp = 0.0
-        a = 0.0
-        if self.date_lst:
-            self.cr.execute(
-                "SELECT CASE WHEN sum(debit) < sum(credit) " \
-                        "THEN sum(credit) - sum(debit) " \
-                        "ELSE 0 " \
-                    "END " \
-                "FROM account_move_line AS l " \
-                "WHERE l.account_id IN (" + self.account_ids + ") " \
-                "AND l.date IN %s " \
-                "GROUP BY l.partner_id",
-                (tuple(self.date_lst),))
-            a = self.cr.fetchone()[0] or 0.0
-
-            if self.cr.fetchone() != None:
-                result_tmp = result_tmp + (a or 0.0)
-
-            else:
-                result_tmp = 0.0
-
-        return result_tmp
-
     def _solde_balance_debit(self,data):
         debit, credit = self._sum_debit(data), self._sum_credit(data)
         return debit > credit and debit - credit

=== modified file 'account/report/third_party_ledger.py'
--- account/report/third_party_ledger.py	2010-05-27 10:59:00 +0000
+++ account/report/third_party_ledger.py	2010-08-24 13:59:55 +0000
@@ -256,12 +256,15 @@
 		if self.date_lst_string:
 			self.cr.execute(
 				"SELECT l.id,l.date,j.code, l.ref, l.name, l.debit, l.credit " \
-				"FROM account_move_line l " \
+				"FROM (account_move_line l " \
 				"LEFT JOIN account_journal j " \
-					"ON (l.journal_id = j.id) " \
+					"ON (l.journal_id = j.id)) " \
+					"LEFT JOIN account_move m " \
+						"ON (l.move_id = m.id) " \
 				"WHERE l.partner_id = %s " \
 					"AND l.account_id IN (" + self.account_ids + ") " \
 					"AND l.date IN (" + self.date_lst_string + ") " \
+					"AND m.state = 'posted' " \
 					" " + RECONCILE_TAG + " "\
 					"ORDER BY l.id",
 					(partner.id,))
@@ -274,6 +277,19 @@
 
 		return full_account
 
+	def _get_line_sql(self):
+		return "SELECT sum(line.%s) " \
+				"FROM account_move_line line " \
+				"LEFT JOIN account_move m ON (line.move_id = m.id) " \
+				"WHERE line.account_id IN (" + self.account_ids + ") " \
+				"AND m.state = 'posted' "
+
+	def _get_credit_sql(self):
+		return self._get_line_sql() % ("credit", )
+
+	def _get_debit_sql(self):
+		return self._get_line_sql() % ("debit", )
+
 	def _sum_debit_partner(self, partner,data):
 
 		account_move_line_obj = pooler.get_pool(self.cr.dbname).get('account.move.line')
@@ -283,13 +299,10 @@
 		else:
 			RECONCILE_TAG = "AND reconcile_id IS NULL"
 		if self.date_lst and data['form']['soldeinit'] :
-			self.cr.execute(
-				"SELECT sum(debit) " \
-				"FROM account_move_line " \
-				"WHERE partner_id = %s " \
-					"AND account_id IN (" + self.account_ids + ") " \
-					"AND reconcile_id IS NULL " \
-					"AND date < %s " ,
+			self.cr.execute(self._get_debit_sql() +
+					"AND line.partner_id = %s " \
+					"AND line.reconcile_id IS NULL " \
+					"AND line.date < %s ",
 				(partner.id, self.date_lst[0],))
 			contemp = self.cr.fetchone()
 			if contemp != None:
@@ -298,13 +311,10 @@
 				result_tmp = result_tmp + 0.0
 
 		if self.date_lst_string:
-			self.cr.execute(
-					"SELECT sum(debit) " \
-					"FROM account_move_line " \
-					"WHERE partner_id = %s " \
-						"AND account_id IN (" + self.account_ids + ") " \
+			self.cr.execute(self._get_debit_sql() +
+						"AND line.partner_id = %s " \
 						" " + RECONCILE_TAG + " " \
-						"AND date IN (" + self.date_lst_string + ") " ,
+						"AND line.date IN (" + self.date_lst_string + ") ",
 					(partner.id,))
 
 			contemp = self.cr.fetchone()
@@ -322,13 +332,10 @@
 		else:
 			RECONCILE_TAG = "AND reconcile_id IS NULL"
 		if self.date_lst and data['form']['soldeinit'] :
-			self.cr.execute(
-					"SELECT sum(credit) " \
-					"FROM account_move_line " \
-					"WHERE partner_id=%s " \
-						"AND account_id IN (" + self.account_ids + ") " \
-						"AND reconcile_id IS NULL " \
-						"AND date < %s " ,
+			self.cr.execute(self._get_credit_sql() +
+						"AND line.partner_id=%s " \
+						"AND line.reconcile_id IS NULL " \
+						"AND line.date < %s ",
 					(partner.id,self.date_lst[0],))
 			contemp = self.cr.fetchone()
 			if contemp != None:
@@ -337,13 +344,10 @@
 				result_tmp = result_tmp + 0.0
 
 		if self.date_lst_string:
-			self.cr.execute(
-					"SELECT sum(credit) " \
-					"FROM account_move_line " \
-					"WHERE partner_id=%s " \
-						"AND account_id IN (" + self.account_ids + ") " \
+			self.cr.execute(self._get_credit_sql() +
+						"AND line.partner_id=%s " \
 						" " + RECONCILE_TAG + " " \
-						"AND date IN (" + self.date_lst_string + ") " ,
+						"AND line.date IN (" + self.date_lst_string + ") ",
 					(partner.id,))
 
 			contemp = self.cr.fetchone()
@@ -363,13 +367,10 @@
 		else:
 			RECONCILE_TAG = "AND reconcile_id IS NULL"
 		if self.date_lst and data['form']['soldeinit'] :
-			self.cr.execute(
-					"SELECT sum(debit) " \
-					"FROM account_move_line " \
-					"WHERE partner_id IN (" + self.partner_ids + ") " \
-						"AND account_id IN (" + self.account_ids + ") " \
-						"AND reconcile_id IS NULL " \
-						"AND date < %s " ,
+			self.cr.execute(self._get_debit_sql() +
+						"AND line.partner_id IN (" + self.partner_ids + ") " \
+						"AND line.reconcile_id IS NULL " \
+						"AND line.date < %s ",
 					(self.date_lst[0],))
 			contemp = self.cr.fetchone()
 			if contemp != None:
@@ -378,13 +379,10 @@
 				result_tmp = result_tmp + 0.0
 
 		if self.date_lst_string:
-			self.cr.execute(
-					"SELECT sum(debit) " \
-					"FROM account_move_line " \
-					"WHERE partner_id IN (" + self.partner_ids + ") " \
-						"AND account_id IN (" + self.account_ids + ") " \
+			self.cr.execute(self._get_debit_sql() +
+						"AND line.partner_id IN (" + self.partner_ids + ") " \
 						" " + RECONCILE_TAG + " " \
-						"AND date IN (" + self.date_lst_string + ") "
+						"AND line.date IN (" + self.date_lst_string + ") ",
 					)
 
 			contemp = self.cr.fetchone()
@@ -406,13 +404,10 @@
 		else:
 			RECONCILE_TAG = "AND reconcile_id IS NULL"
 		if self.date_lst and data['form']['soldeinit'] :
-			self.cr.execute(
-					"SELECT sum(credit) " \
-					"FROM account_move_line " \
-					"WHERE partner_id IN (" + self.partner_ids + ") " \
-						"AND account_id IN (" + self.account_ids + ") " \
-						"AND reconcile_id IS NULL " \
-						"AND date < %s " ,
+			self.cr.execute(self._get_credit_sql() +
+						"AND line.partner_id IN (" + self.partner_ids + ") " \
+						"AND line.reconcile_id IS NULL " \
+						"AND line.date < %s ",
 					(self.date_lst[0],))
 			contemp = self.cr.fetchone()
 			if contemp != None:
@@ -421,13 +416,10 @@
 				result_tmp = result_tmp + 0.0
 
 		if self.date_lst_string:
-			self.cr.execute(
-					"SELECT sum(credit) " \
-					"FROM account_move_line " \
-					"WHERE partner_id IN (" + self.partner_ids + ") " \
-						"AND account_id IN (" + self.account_ids + ") " \
+			self.cr.execute(self._get_credit_sql() +
+						"AND line.partner_id IN (" + self.partner_ids + ") " \
 						" " + RECONCILE_TAG + " " \
-						"AND date IN (" + self.date_lst_string + ") "
+						"AND line.date IN (" + self.date_lst_string + ") ",
 					)
 			contemp = self.cr.fetchone()
 			if contemp != None: