Merge lp:~julie-w/unifield-server/US-3182 into lp:unifield-server

[jftempo]

=== modified file 'bin/addons/account/report/account_liquidity_balance.py'
--- bin/addons/account/report/account_liquidity_balance.py	2017-05-26 15:28:23 +0000
+++ bin/addons/account/report/account_liquidity_balance.py	2018-03-01 09:44:19 +0000
@@ -41,13 +41,69 @@
     def _get_register_data(self):
         """
         Returns a list of dicts, each containing the data of the liquidity registers for the selected period and instances
+        For Cash & Bank registers the calculation is the one used in OCB VI.
+        For Cheque Registers:
+            - opening: balance at end of N-1 of entries which are not reconciled within period N-1 or before
+            - calculated: always 0.00
+            - closing: balance at end of N of entries which are not reconciled within period N or before
         """
+        res = []
         period = self.pool.get('account.period').browse(self.cr, self.uid, self.period_id, context=self.context,
                                                         fields_to_fetch=['date_start', 'date_stop'])
         period_yyyymm = "{0}{1}".format(self.year, self.month)
-        params = (tuple([period_yyyymm]), period.date_start, period.id, period.date_stop, tuple(self.instance_ids))
+        # Cash and Bank registers
+        reg_types = ('cash', 'bank')
+        params = (tuple([period_yyyymm]), reg_types, period.date_start, reg_types, period.id, reg_types,
+                  period.date_stop, tuple(self.instance_ids))
         self.cr.execute(self.liquidity_sql, params)
-        return self.cr.dictfetchall()
+        cash_bank_res = self.cr.dictfetchall()
+        res.extend(cash_bank_res)
+        # Cheque registers
+        cheque_sql = """
+                    SELECT i.code AS instance, j.code, j.name, %s AS period, req.opening, req.calculated, req.closing, 
+                    c.name AS currency
+                    FROM res_currency c,
+                    (
+                        SELECT journal_id, account_id, SUM(col1) AS opening, SUM(col2) AS calculated, SUM(col3) AS closing
+                        FROM (
+                            (
+                                SELECT aml.journal_id AS journal_id, aml.account_id AS account_id, 
+                                ROUND(SUM(amount_currency), 2) as col1, 0.00 as col2, 0.00 as col3
+                                FROM account_move_line AS aml 
+                                LEFT JOIN account_journal j ON aml.journal_id = j.id 
+                                WHERE j.type = 'cheque'
+                                AND aml.date < %s
+                                AND aml.account_id IN (j.default_debit_account_id, j.default_credit_account_id)
+                                AND ((reconcile_id IS NULL AND reconcile_partial_id IS NULL) OR reconcile_date >= %s)
+                                GROUP BY aml.journal_id, aml.account_id
+                            )
+                        UNION
+                            (
+                                SELECT aml.journal_id AS journal_id, aml.account_id AS account_id, 0.00 as col1, 
+                                0.00 as col2, ROUND(SUM(amount_currency), 2) as col3
+                                FROM account_move_line AS aml 
+                                LEFT JOIN account_journal j ON aml.journal_id = j.id 
+                                WHERE j.type = 'cheque'
+                                AND aml.date <= %s
+                                AND aml.account_id IN (j.default_debit_account_id, j.default_credit_account_id)
+                                AND ((reconcile_id IS NULL AND reconcile_partial_id IS NULL) OR reconcile_date > %s)
+                                GROUP BY aml.journal_id, aml.account_id
+                            )
+                        ) AS ssreq
+                        GROUP BY journal_id, account_id
+                        ORDER BY journal_id, account_id
+                    ) AS req, account_journal j, msf_instance i
+                    WHERE req.journal_id = j.id
+                    AND j.instance_id = i.id
+                    AND j.currency = c.id
+                    AND j.instance_id IN %s;
+                    """
+        cheque_params = (tuple([period_yyyymm]), period.date_start, period.date_start, period.date_stop,
+                         period.date_stop, tuple(self.instance_ids))
+        self.cr.execute(cheque_sql, cheque_params)
+        cheque_res = self.cr.dictfetchall()
+        res.extend(cheque_res)
+        return res
 
     def set_context(self, objects, data, ids, report_type=None):
         # get the selection made by the user

=== modified file 'bin/addons/vertical_integration/report/hq_report_ocb.py'
--- bin/addons/vertical_integration/report/hq_report_ocb.py	2017-05-26 10:19:33 +0000
+++ bin/addons/vertical_integration/report/hq_report_ocb.py	2018-03-01 09:44:19 +0000
@@ -260,7 +260,7 @@
                         FROM account_move_line AS aml 
                         LEFT JOIN account_journal j 
                             ON aml.journal_id = j.id 
-                        WHERE j.type IN ('cash', 'bank', 'cheque')
+                        WHERE j.type IN %s
                         AND aml.date < %s
                         AND aml.account_id IN (j.default_debit_account_id, j.default_credit_account_id)
                         GROUP BY aml.journal_id, aml.account_id
@@ -271,7 +271,7 @@
                         FROM account_move_line AS aml 
                         LEFT JOIN account_journal j 
                             ON aml.journal_id = j.id 
-                        WHERE j.type IN ('cash', 'bank', 'cheque')
+                        WHERE j.type IN %s
                         AND aml.period_id = %s
                         AND aml.account_id IN (j.default_debit_account_id, j.default_credit_account_id)
                         GROUP BY aml.journal_id, aml.account_id
@@ -282,7 +282,7 @@
                         FROM account_move_line AS aml 
                         LEFT JOIN account_journal j 
                             ON aml.journal_id = j.id 
-                        WHERE j.type IN ('cash', 'bank', 'cheque')
+                        WHERE j.type IN %s
                         AND aml.date <= %s
                         AND aml.account_id IN (j.default_debit_account_id, j.default_credit_account_id)
                         GROUP BY aml.journal_id, aml.account_id
@@ -606,6 +606,7 @@
         # + More than 1 request in 1 file: just use same filename for each request you want to be in the same file.
         # + If you cannot do a SQL request to create the content of the file, do a simple request (with key) and add a postprocess function that returns the result you want
         instance_name = 'OCB'  # since US-949
+        reg_types = ('cash', 'bank', 'cheque')
         processrequests = [
             {
                 'headers': ['XML_ID', 'Name', 'Reference', 'Partner type', 'Active/inactive'],
@@ -646,7 +647,7 @@
                 'headers': ['Instance', 'Code', 'Name', 'Period', 'Opening balance', 'Calculated balance', 'Closing balance', 'Currency'],
                 'filename': instance_name + '_' + year + month + '_Liquidity Balances.csv',
                 'key': 'liquidity',
-                'query_params': (tuple([period_yyyymm]), first_day_of_period, period.id, last_day_of_period, tuple(instance_ids)),
+                'query_params': (tuple([period_yyyymm]), reg_types, first_day_of_period, reg_types, period.id, reg_types, last_day_of_period, tuple(instance_ids)),
             },
             {
                 'headers': ['Name', 'Code', 'Donor code', 'Grant amount', 'Reporting CCY', 'State'],