Merge lp:~jfb-tempo-consulting/unifield-server/us-2146 into lp:unifield-server

[jftempo]

=== modified file 'bin/addons/board/queries_finance.py'
--- bin/addons/board/queries_finance.py	2016-11-10 15:58:28 +0000
+++ bin/addons/board/queries_finance.py	2017-01-23 11:00:37 +0000
@@ -51,34 +51,20 @@
 account_move.name,
 account_account.code,
 avg(account_move_line.credit_currency-account_move_line.debit_currency) JI,
-sum(account_analytic_line.amount_currency) AJI,
-abs(abs(avg(account_move_line.debit_currency-account_move_line.credit_currency)) - abs(sum(account_analytic_line.amount_currency))) difference
+sum(COALESCE(account_analytic_line.amount_currency, 0)) AJI,
+abs(abs(avg(account_move_line.debit_currency-account_move_line.credit_currency)) - abs(sum(COALESCE(account_analytic_line.amount_currency, 0)))) difference
 FROM
-account_move,
-account_move_line,
-account_account,
-account_analytic_line,
-account_journal,
-account_period
+account_move_line
+JOIN account_move ON account_move.id = account_move_line.move_id
+JOIN account_account ON account_account.id = account_move_line.account_id
+JOIN account_journal ON account_journal.id = account_move.journal_id
+JOIN account_period ON account_move.period_id = account_period.id
+LEFT JOIN account_analytic_line on account_analytic_line.move_id = account_move_line.id
 WHERE
-account_analytic_line.move_id = account_move_line.id and
-account_move_line.move_id = account_move.id AND
-account_move_line.account_id = account_account.id AND
-account_journal.id = account_move.journal_id AND
-account_move.period_id = account_period.id AND
 account_journal.type not in ('system', 'revaluation', 'cur_adj') AND
-account_account.code in (
-SELECT
-account_account.code
-FROM
-account_account,
-account_account_type
-WHERE
-account_account.user_type = account_account_type.id and
-account_account_type.code in ('income', 'expense')
-)
+account_account.is_analytic_addicted = 't'
 GROUP BY account_period.name, account_move.name, account_move_line.id, account_period.date_start, account_account.code
-HAVING abs(abs(avg(account_move_line.debit_currency-account_move_line.credit_currency)) - abs(sum(account_analytic_line.amount_currency))) > 0.00001
+HAVING abs(abs(avg(account_move_line.debit_currency-account_move_line.credit_currency)) - abs(sum(COALESCE(account_analytic_line.amount_currency, 0)))) > 0.00001
 ORDER BY account_period.date_start, account_move.name"""
     },
     {
@@ -89,34 +75,20 @@
 account_move.name,
 account_account.code,
 avg(account_move_line.credit-account_move_line.debit) JI,
-sum(account_analytic_line.amount) AJI,
-abs(avg(account_move_line.credit-account_move_line.debit) - sum(account_analytic_line.amount)) difference
+sum(COALESCE(account_analytic_line.amount, 0)) AJI,
+abs(avg(account_move_line.credit-account_move_line.debit) - sum(COALESCE(account_analytic_line.amount, 0))) difference
 FROM
-account_move,
-account_move_line,
-account_account,
-account_analytic_line,
-account_journal,
-account_period
+account_move_line
+JOIN account_move ON account_move.id = account_move_line.move_id
+JOIN account_account ON account_account.id = account_move_line.account_id
+JOIN account_journal ON account_move.journal_id = account_journal.id
+JOIN account_period ON account_period.id = account_move.period_id
+LEFT JOIN account_analytic_line ON account_analytic_line.move_id = account_move_line.id
 WHERE
-account_analytic_line.move_id = account_move_line.id and
-account_move_line.move_id = account_move.id AND
-account_move_line.account_id = account_account.id AND
-account_journal.id = account_move.journal_id AND
-account_move.period_id = account_period.id AND
 account_journal.type in ('revaluation', 'cur_adj') AND
-account_account.code in (
-SELECT
-account_account.code
-FROM
-account_account,
-account_account_type
-WHERE
-account_account.user_type = account_account_type.id and
-account_account_type.code in ('income', 'expense')
-)
+account_account.is_analytic_addicted = 't'
 GROUP BY account_period.name, account_move.name, account_move_line.id, account_period.date_start, account_account.code
-HAVING abs(avg(account_move_line.credit-account_move_line.debit) - sum(account_analytic_line.amount)) > 0.00001
+HAVING abs(avg(account_move_line.credit-account_move_line.debit) - sum(COALESCE(account_analytic_line.amount, 0))) > 0.00001
 order by account_period.date_start, account_move.name"""
     },
     {