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

[jftempo]

=== modified file 'bin/addons/account_override/__openerp__.py'
--- bin/addons/account_override/__openerp__.py	2016-02-04 16:43:13 +0000
+++ bin/addons/account_override/__openerp__.py	2018-11-07 14:15:37 +0000
@@ -43,6 +43,7 @@
         'wizard/account_chart.xml',
         'wizard/import_invoice.xml',
         'wizard/split_invoice.xml',
+        'wizard/integrity_finance_wizard_view.xml',
         'attachment_view.xml'
     ],
     'test': [],

=== modified file 'bin/addons/account_override/account_view.xml'
--- bin/addons/account_override/account_view.xml	2018-09-06 12:58:16 +0000
+++ bin/addons/account_override/account_view.xml	2018-11-07 14:15:37 +0000
@@ -206,15 +206,5 @@
             menu="False"
          />
 
-
-        <menuitem
-            name="Entries Data Integrity"
-            action="board.integrity_finance"
-            id="menu_integrity_finance"
-            parent="account.menu_finance_generic_reporting"
-            sequence="100"
-            type="report.xml"
-        />
-
     </data>
 </openerp>

=== modified file 'bin/addons/account_override/period.py'
--- bin/addons/account_override/period.py	2017-12-01 16:19:33 +0000
+++ bin/addons/account_override/period.py	2018-11-07 14:15:37 +0000
@@ -22,6 +22,8 @@
 ##############################################################################
 
 from osv import osv
+from tools.translate import _
+
 
 def get_period_from_date(self, cr, uid, date=False, context=None):
     """
@@ -100,6 +102,42 @@
         period_id = period_id and get_next_period_id(self, cr, uid, period_id, context=context)
     return period_id or False
 
+
+def get_period_range(self, cr, uid, period_from_id, period_to_id, context=None):
+    """
+    Returns the ids of all the periods included between 2 other periods.
+    Special periods 13 to 16 are included, period 0 is excluded.
+    """
+    if context is None:
+        context = {}
+    field_list = ['number', 'fiscalyear_id', 'date_start']
+    initial_period = self.browse(cr, uid, period_from_id, fields_to_fetch=field_list, context=context)
+    final_period = self.browse(cr, uid, period_to_id, fields_to_fetch=field_list, context=context)
+    initial_fy_id = initial_period.fiscalyear_id.id
+    initial_number = initial_period.number
+    final_fy_id = final_period.fiscalyear_id.id
+    final_number = final_period.number
+    same_fy = initial_fy_id == final_fy_id
+    if (final_period.date_start < initial_period.date_start) or \
+            (same_fy and final_period.number < initial_period.number):  # e.g. Period 13 2018 precedes Period 14 2018
+        raise osv.except_osv(_('Error'), _("The End period can't precede the Start period."))
+    if same_fy:  # all the periods are within the same Fiscal Year
+        period_dom = [
+            ('number', '!=', 0),
+            ('number', '>=', initial_number),
+            ('number', '<=', final_number),
+            ('fiscalyear_id', '=', initial_fy_id)]
+    else:
+        # ex: from Nov. 2018 to Jan. 2019 => Nov 2018 / Dec 2018 / Periods 13->16 2018 / Jan 2019
+        period_dom = [
+            ('number', '!=', 0),
+            '|',
+            '&', ('number', '>=', initial_number), ('fiscalyear_id', '=', initial_fy_id),
+            '&', ('number', '<=', final_number), ('fiscalyear_id', '=', final_fy_id)]
+    period_ids = self.search(cr, uid, period_dom, order='id', context=context)
+    return period_ids
+
+
 class account_period(osv.osv):
     _name = 'account.period'
     _inherit = 'account.period'
@@ -116,5 +154,9 @@
     def get_next_period_id_at_index(self, cr, uid, period_id, index, context=None):
         return get_next_period_id_at_index(self, cr, uid, period_id, index, context)
 
+    def get_period_range(self, cr, uid, period_from_id, period_to_id, context=None):
+        return get_period_range(self, cr, uid, period_from_id, period_to_id, context=context)
+
+
 account_period()
 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:

=== modified file 'bin/addons/account_override/wizard/__init__.py'
--- bin/addons/account_override/wizard/__init__.py	2017-08-02 09:28:06 +0000
+++ bin/addons/account_override/wizard/__init__.py	2018-11-07 14:15:37 +0000
@@ -2,3 +2,4 @@
 import split_invoice
 import import_invoice
 import report_paid_invoices
+import integrity_finance_wizard

=== added file 'bin/addons/account_override/wizard/integrity_finance_wizard.py'
--- bin/addons/account_override/wizard/integrity_finance_wizard.py	1970-01-01 00:00:00 +0000
+++ bin/addons/account_override/wizard/integrity_finance_wizard.py	2018-11-07 14:15:37 +0000
@@ -0,0 +1,121 @@
+# -*- coding: utf-8 -*-
+##############################################################################
+#
+#    OpenERP, Open Source Management Solution
+#    Copyright (C) 2018 TeMPO Consulting, MSF. All Rights Reserved
+#
+#    This program is free software: you can redistribute it and/or modify
+#    it under the terms of the GNU Affero General Public License as
+#    published by the Free Software Foundation, either version 3 of the
+#    License, or (at your option) any later version.
+#
+#    This program is distributed in the hope that it will be useful,
+#    but WITHOUT ANY WARRANTY; without even the implied warranty of
+#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+#    GNU Affero General Public License for more details.
+#
+#    You should have received a copy of the GNU Affero General Public License
+#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
+#
+##############################################################################
+
+from osv import fields
+from osv import osv
+from tools.translate import _
+
+from datetime import datetime
+
+
+class integrity_finance_wizard(osv.osv_memory):
+    _name = 'integrity.finance.wizard'
+
+    _columns = {
+        'fiscalyear_id': fields.many2one('account.fiscalyear', 'Fiscal year'),
+        'filter': fields.selection([
+            ('filter_no', 'No Filters'),
+            ('filter_date_doc', 'Document Date'),
+            ('filter_date', 'Posting Date'),
+            ('filter_period', 'Period')
+        ], "Filter by", required=True),
+        'period_from': fields.many2one('account.period', 'Start period'),
+        'period_to': fields.many2one('account.period', 'End period'),
+        'date_from': fields.date("Start date"),
+        'date_to': fields.date("End date"),
+        'instance_ids': fields.many2many('msf.instance', 'integrity_finance_wizard_instance_rel',
+                                         'wizard_id', 'instance_id',
+                                         string='Proprietary Instances'),
+    }
+
+    _defaults = {
+        'filter': 'filter_no',
+    }
+
+    def onchange_filter(self, cr, uid, ids, filter, context=None):
+        """
+        Adapts the date/period filter according to the selection made in "Filter by"
+        """
+        res = {}
+        if filter == 'filter_no':
+            res['value'] = {'period_from': False, 'period_to': False, 'date_from': False, 'date_to': False}
+        elif filter in ('filter_date', 'filter_date_doc', ):
+            res['value'] = {'period_from': False, 'period_to': False}
+        elif filter == 'filter_period':
+            res['value'] = {'date_from': False, 'date_to': False}
+        return res
+
+    def onchange_fiscalyear_id(self, cr, uid, ids, fiscalyear_id, context=None):
+        """
+        (Only) if a FY is selected: resets the periods selected and restricts their domain to within the FY
+        """
+        res = {}
+        if fiscalyear_id:
+            res = {
+                'value': {
+                    'period_from': False,
+                    'period_to': False,
+                    },
+                'domain': {
+                    'period_from': [('fiscalyear_id', '=', fiscalyear_id)],
+                    'period_to': [('fiscalyear_id', '=', fiscalyear_id)],
+                    }
+                }
+        return res
+
+    def print_integrity_finance_report(self, cr, uid, ids, context=None):
+        """
+        Prints the "Entries Data Integrity" report
+        """
+        if context is None:
+            context = {}
+        if isinstance(ids, (int, long)):
+            ids = [ids]
+        user_obj = self.pool.get('res.users')
+        wiz = self.browse(cr, uid, ids[0], context=context)
+        data = {
+            'form': {},
+            'context': context,
+        }
+        # store the selected criteria
+        data['form'].update({
+            'fiscalyear_id': wiz.fiscalyear_id and wiz.fiscalyear_id.id or False,
+            'filter': wiz.filter,
+            'period_from': wiz.period_from and wiz.period_from.id or False,
+            'period_to': wiz.period_to and wiz.period_to.id or False,
+            'date_from': wiz.date_from or False,
+            'date_to': wiz.date_to or False,
+            'instance_ids': wiz.instance_ids and [inst.id for inst in wiz.instance_ids],
+        })
+        company = user_obj.browse(cr, uid, uid, fields_to_fetch=['company_id'], context=context).company_id
+        current_instance = company.instance_id and company.instance_id.code or ''
+        current_date = datetime.today().strftime('%Y%m%d')
+        data['target_filename'] = "%s %s %s" % (_('Entries Data Integrity'), current_instance, current_date)
+        return {
+            'type': 'ir.actions.report.xml',
+            'report_name': 'integrity.finance',
+            'datas': data,
+            'context': context,
+        }
+
+
+integrity_finance_wizard()
+# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:

=== added file 'bin/addons/account_override/wizard/integrity_finance_wizard_view.xml'
--- bin/addons/account_override/wizard/integrity_finance_wizard_view.xml	1970-01-01 00:00:00 +0000
+++ bin/addons/account_override/wizard/integrity_finance_wizard_view.xml	2018-11-07 14:15:37 +0000
@@ -0,0 +1,63 @@
+<?xml version="1.0" encoding="utf-8"?>
+<openerp>
+    <data>
+
+        <!-- Entries Data Integrity - Wizard -->
+        <record id="integrity_finance_wizard_view" model="ir.ui.view">
+            <field name="name">Entries Data Integrity</field>
+            <field name="model">integrity.finance.wizard</field>
+            <field name="type">form</field>
+            <field name="arch" type="xml">
+                <form>
+                    <field name="instance_ids" domain="[('instance_to_display_ids', '=', True)]">
+                        <tree noteditable="1">
+                            <field name="code"/>
+                            <field name="name"/>
+                        </tree>
+                    </field>
+                    <field name="fiscalyear_id" on_change="onchange_fiscalyear_id(fiscalyear_id)"/>
+                    <notebook tabpos="up" colspan="4">
+                        <page string="Filters" name="filters">
+                            <field name="filter" on_change="onchange_filter(filter)" colspan="4"/>
+                            <separator string="Dates" colspan="4"/>
+                            <field name="date_from" attrs="{'readonly': [('filter', 'not in', ('filter_date', 'filter_date_doc'))],
+                                                            'required': [('filter', 'in', ('filter_date', 'filter_date_doc'))]}" colspan="4"/>
+                            <field name="date_to" attrs="{'readonly': [('filter', 'not in', ('filter_date', 'filter_date_doc'))],
+                                                          'required': [('filter', 'in', ('filter_date', 'filter_date_doc'))]}"  colspan="4"/>
+                            <separator string="Periods"  colspan="4"/>
+                            <field name="period_from"
+                                   attrs="{'readonly': [('filter', '!=', 'filter_period')],
+                                           'required':[('filter', '=', 'filter_period')]}" colspan="4"/>
+                            <field name="period_to"
+                                   attrs="{'readonly': [('filter', '!=', 'filter_period')],
+                                           'required':[('filter', '=', 'filter_period')]}" colspan="4"/>
+                        </page>
+                    </notebook>
+                    <group colspan="6" col="4">
+                        <button icon="gtk-cancel" string="Cancel" special="cancel" colspan="2"/>
+                        <button icon="gtk-print" string="Print" name="print_integrity_finance_report" type="object"
+                                default_focus="1" colspan="2"/>
+                    </group>
+                </form>
+            </field>
+        </record>
+
+        <!-- Entries Data Integrity - Menu Entry -->
+        <record id="action_integrity_finance_report" model="ir.actions.act_window">
+             <field name="name">Entries Data Integrity</field>
+             <field name="res_model">integrity.finance.wizard</field>
+             <field name="type">ir.actions.act_window</field>
+             <field name="view_type">form</field>
+             <field name="view_mode">form</field>
+             <field name="view_id" ref="integrity_finance_wizard_view"/>
+             <field name="target">new</field>
+        </record>
+        <menuitem
+            name="Entries Data Integrity"
+            action="action_integrity_finance_report"
+            id="menu_integrity_finance_report"
+            parent="account.menu_finance_generic_reporting"
+            sequence="100"/>
+
+    </data>
+</openerp>

=== modified file 'bin/addons/board/queries_finance.py'
--- bin/addons/board/queries_finance.py	2018-02-19 16:13:36 +0000
+++ bin/addons/board/queries_finance.py	2018-11-07 14:15:37 +0000
@@ -7,6 +7,7 @@
 
 queries = [
     {
+        'ref': 'ji_unbalanced_booking',
         'title': _('Journal Items that are not balanced in booking currency'),
         'headers': [_('Period'), _('Entry Sequence'), _('Difference')],
         'query': """select p.name period, m.name, sum(l.credit_currency-l.debit_currency) difference
@@ -20,12 +21,14 @@
 m.state='posted' and
 m.journal_id = j.id and
 j.type != 'system'
+%s
 group by p.name, m.name, l.move_id, p.date_start
 having abs(sum(l.credit_currency-l.debit_currency)) > 0.00001
-order by p.date_start, m.name
+order by p.date_start, m.name;
 """
     },
     {
+        'ref': 'ji_unbalanced_fctal',
         'title': _('Journal Items that are not balanced in functional currency'),
         'headers': [_('Period'), _('Entry Sequence'), _('Difference')],
         'query': """select p.name period, m.name, sum(l.credit-l.debit) difference
@@ -39,82 +42,91 @@
 m.state='posted' and
 m.journal_id = j.id and
 j.type != 'system'
+%s
 group by p.name, m.name, l.move_id, p.date_start
 having abs(sum(l.credit-l.debit)) > 0.00001
-order by p.date_start, m.name"""
+order by p.date_start, m.name;"""
     },
     {
+        'ref': 'mismatch_ji_aji_booking',
         'title': _('P&L Journal Items vs Analytic Journal Items mismatch in booking currency (except FXA and REV)'),
         'headers': [_('Period'), _('Entry Sequence'), _('Account Code'), _('JI Book. Amount'), _('AJI Book. Amount'), _('Difference')],
         'query': """SELECT
 account_period.name,
-account_move.name,
+m.name,
 account_account.code,
-avg(account_move_line.credit_currency-account_move_line.debit_currency) JI,
+avg(l.credit_currency - l.debit_currency) JI,
 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
+abs(abs(avg(l.debit_currency - l.credit_currency)) - abs(sum(COALESCE(account_analytic_line.amount_currency, 0)))) difference
 FROM
-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
+account_move_line l
+JOIN account_move m ON m.id = l.move_id
+JOIN account_account ON account_account.id = l.account_id
+JOIN account_journal ON account_journal.id = m.journal_id
+JOIN account_period ON m.period_id = account_period.id
+LEFT JOIN account_analytic_line on account_analytic_line.move_id = l.id
 LEFT JOIN account_analytic_account ON account_analytic_line.account_id = account_analytic_account.id
 WHERE
 account_journal.type not in ('system', 'revaluation', 'cur_adj') AND
 account_account.is_analytic_addicted = 't' AND
 account_analytic_account.category not in ('FREE1', 'FREE2')
-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(COALESCE(account_analytic_line.amount_currency, 0)))) > 0.00001
-ORDER BY account_period.date_start, account_move.name"""
+%s
+GROUP BY account_period.name, m.name, l.id, account_period.date_start, account_account.code
+HAVING abs(abs(avg(l.debit_currency - l.credit_currency)) - abs(sum(COALESCE(account_analytic_line.amount_currency, 0)))) > 0.00001
+ORDER BY account_period.date_start, m.name;"""
     },
     {
+        'ref': 'mismatch_ji_aji_fctal',
         'title': _('P&L Journal Items vs Analytic Journal Items mismatch in functional currency (FXA and REV only)'),
         'headers': [_('Period'), _('Entry Sequence'), _('Account Code'), _('JI Func. Amount'), _('AJI Func. Amount'), _('Difference')],
         'query': """SELECT
 account_period.name,
-account_move.name,
+m.name,
 account_account.code,
-avg(account_move_line.credit-account_move_line.debit) JI,
+avg(l.credit - l.debit) JI,
 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
+abs(avg(l.credit - l.debit) - sum(COALESCE(account_analytic_line.amount, 0))) difference
 FROM
-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
+account_move_line l
+JOIN account_move m ON m.id = l.move_id
+JOIN account_account ON account_account.id = l.account_id
+JOIN account_journal ON m.journal_id = account_journal.id
+JOIN account_period ON account_period.id = m.period_id
+LEFT JOIN account_analytic_line ON account_analytic_line.move_id = l.id
 LEFT JOIN account_analytic_account ON account_analytic_line.account_id = account_analytic_account.id
 WHERE
 account_journal.type in ('revaluation', 'cur_adj') AND
 account_account.is_analytic_addicted = 't' AND
 account_analytic_account.category not in ('FREE1', 'FREE2')
-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(COALESCE(account_analytic_line.amount, 0))) > 0.00001
-order by account_period.date_start, account_move.name"""
+%s
+GROUP BY account_period.name, m.name, l.id, account_period.date_start, account_account.code
+HAVING abs(avg(l.credit - l.debit) - sum(COALESCE(account_analytic_line.amount, 0))) > 0.00001
+order by account_period.date_start, m.name;"""
     },
     {
+        'ref': 'unbalanced_rec_fctal',
         'title': _('Unbalanced reconciliations in functional currency'),
-        'headers': [_('Reconcile number'), _('Difference')],
-        'query': """SELECT rec.name, sum(l.credit-l.debit)
+        'headers': [_('Reconcile number'), _('Reconcile date'), _('Difference')],
+        'query': """SELECT rec.name, 'rec_date', sum(l.credit-l.debit)
 from account_move_line l, account_move_reconcile rec
 where l.reconcile_id=rec.id
+%s
 group by rec.id, rec.name
 having(abs(sum(l.credit-l.debit)) > 0.0001)
-order by rec.name
+order by rec.name;
 """
     },
     {
+        'ref': 'unbalanced_rec_booking',
         'title': _('Unbalanced reconciliations in booking currency'),
-        'headers': [_('Reconcile number'), _('Difference')],
-        'query': """SELECT rec.name, sum(l.credit_currency-l.debit_currency)
+        'headers': [_('Reconcile number'), _('Reconcile date'), _('Difference')],
+        'query': """SELECT rec.name, 'rec_date', sum(l.credit_currency-l.debit_currency)
 from account_move_line l, account_move_reconcile rec
 where l.reconcile_id=rec.id
+%s
 group by rec.id, rec.name
 having(abs(sum(l.credit_currency-l.debit_currency)) > 0.0001 and count(distinct(l.currency_id))=1)
-order by rec.name
+order by rec.name;
 """
     },
 ]

=== modified file 'bin/addons/board/report/integrity.mako'
--- bin/addons/board/report/integrity.mako	2016-11-09 16:46:03 +0000
+++ bin/addons/board/report/integrity.mako	2018-11-07 14:15:37 +0000
@@ -93,16 +93,24 @@
 <Cell ss:StyleID="ssH"><Data ss:Type="String">${_t(header)|x}</Data></Cell>
 % endfor
 </Row>
-% for result in get_results(check.get('query')):
+% for result in get_results(check.get('query'), check.get('ref')):
 <Row>
   % for cell in result:
-  <Cell ss:StyleID="ssBorder">
-    % if isinstance(cell, (int, float, long)):
-    <Data ss:Type="Number">${cell}</Data>
+    % if cell == 'rec_date':
+      <%
+      reconcile_ref = result[0]
+      reconcile_date = get_reconcile_date(reconcile_ref)
+      %>
+      % if reconcile_date:
+        <Cell ss:StyleID="sShortDate"><Data ss:Type="DateTime">${reconcile_date|n}T00:00:00.000</Data></Cell>
+      % else:
+        <Cell ss:StyleID="ssBorder"></Cell>
+      % endif
+    % elif isinstance(cell, (int, float, long)):
+      <Cell ss:StyleID="ssBorder"><Data ss:Type="Number">${cell}</Data></Cell>
     % else:
-    <Data ss:Type="String">${cell|x}</Data>
+      <Cell ss:StyleID="ssBorder"><Data ss:Type="String">${cell|x}</Data></Cell>
     % endif
-  </Cell>
   %endfor
 </Row>
 % endfor

=== modified file 'bin/addons/board/report/integrity_finance.py'
--- bin/addons/board/report/integrity_finance.py	2016-11-02 09:29:48 +0000
+++ bin/addons/board/report/integrity_finance.py	2018-11-07 14:15:37 +0000
@@ -3,31 +3,162 @@
 from report import report_sxw
 from spreadsheet_xml.spreadsheet_xml_write import SpreadsheetReport
 from board import queries_finance
+from osv import osv
 from tools.translate import _
 
 
 class integrity_finance(report_sxw.rml_parse):
     def __init__(self, cr, uid, name, context):
         super(integrity_finance, self).__init__(cr, uid, name, context=context)
+        self.sql_additional = ""  # to add the criteria from the wizard filters
+        self.sql_params = []
+        self.sql_rec_additional = ""  # specific to queries related to reconciliations
+        self.sql_rec_params = []
         self.localcontext.update({
             'get_title': self.get_title,
             'list_checks': self.list_checks,
             'get_results': self.get_results,
+            'get_reconcile_date': self.get_reconcile_date,
             '_t': self._t,
         })
 
+    def set_context(self, objects, data, ids, report_type=None):
+        """
+        Fills in:
+        - self.sql_additional and self.sql_rec_additional with the part of SQL request corresponding to the criteria selected (string)
+        - self.sql_params and self.sql_rec_params with the related parameters (list)
+
+        For reconciliation queries the reconciliation dates must be within the FY/periods/dates selected.
+        For other queries the JI dates are used.
+        """
+        period_obj = self.pool.get('account.period')
+        fy_obj = self.pool.get('account.fiscalyear')
+        if data.get('form', False):
+            # note: the JE id is used and not the JI one to make sure whole entries are retrieved (cf. JI doc dates can differ within a JE)
+            sql_additional_subreq = """
+                AND m.id IN 
+                (
+                    SELECT DISTINCT (m.id)
+                    FROM account_move m, account_move_line l
+                    WHERE l.move_id = m.id
+                    %s
+                )
+            """
+            sql_rec_additional_subreq = """
+                AND l.reconcile_id IN 
+                (
+                    SELECT DISTINCT (reconcile_id)
+                    FROM account_move_line
+                    WHERE reconcile_id IS NOT NULL
+                    %s
+                )
+            """
+            # instances
+            instance_ids = data['form'].get('instance_ids', False)
+            if instance_ids:
+                self.sql_additional += " AND l.instance_id IN %s "
+                self.sql_params.append(tuple(instance_ids,))
+                self.sql_rec_additional += " AND instance_id IN %s "
+                self.sql_rec_params.append(tuple(instance_ids,))
+            # FY
+            fiscalyear_id = data['form'].get('fiscalyear_id', False)
+            if fiscalyear_id:
+                self.sql_additional += " AND l.period_id IN (SELECT id FROM account_period WHERE fiscalyear_id = %s) "
+                self.sql_params.append(fiscalyear_id)
+                fiscalyear = fy_obj.browse(self.cr, self.uid, fiscalyear_id, fields_to_fetch=['date_start', 'date_stop'], context=data.get('context', {}))
+                self.sql_rec_additional += " AND reconcile_date >= %s AND reconcile_date <= %s "
+                self.sql_rec_params.append(fiscalyear.date_start)
+                self.sql_rec_params.append(fiscalyear.date_stop)
+            wiz_filter = data['form'].get('filter', '')
+            # periods
+            if wiz_filter == 'filter_period':
+                period_from = data['form'].get('period_from', False)
+                period_to = data['form'].get('period_to', False)
+                if not period_from or not period_to:
+                    raise osv.except_osv(_('Error'), _('Either the Start period or the End period is missing.'))
+                else:
+                    period_ids = period_obj.get_period_range(self.cr, self.uid, period_from, period_to, context=data.get('context', {}))
+                    if not period_ids:
+                        raise osv.except_osv(_('Error'), _('No period matches the selected criteria.'))
+                    self.sql_additional += " AND l.period_id IN %s "
+                    self.sql_params.append(tuple(period_ids,))
+                    per_from = period_obj.browse(self.cr, self.uid, period_from, fields_to_fetch=['date_start'], context=data.get('context', {}))
+                    per_to = period_obj.browse(self.cr, self.uid, period_to, fields_to_fetch=['date_stop'], context=data.get('context', {}))
+                    self.sql_rec_additional += " AND reconcile_date >= %s AND reconcile_date <= %s "
+                    self.sql_rec_params.append(per_from.date_start)
+                    self.sql_rec_params.append(per_to.date_stop)
+            # dates
+            if wiz_filter in ('filter_date_doc', 'filter_date'):
+                date_from = data['form'].get('date_from', False)
+                date_to = data['form'].get('date_to', False)
+                if not date_from or not date_to:
+                    raise osv.except_osv(_('Error'), _('Either the Start date or the End date is missing.'))
+                else:
+                    if wiz_filter == 'filter_date_doc':
+                        # JI doc dates
+                        self.sql_additional += " AND l.document_date >= %s AND l.document_date <= %s "
+                    else:
+                        # JI posting dates
+                        self.sql_additional += " AND l.date >= %s AND l.date <= %s "
+                    self.sql_params.append(date_from)
+                    self.sql_params.append(date_to)
+                    # reconciliation dates
+                    self.sql_rec_additional += " AND reconcile_date >= %s AND reconcile_date <= %s "
+                    self.sql_rec_params.append(date_from)
+                    self.sql_rec_params.append(date_to)
+            # LAST STEP: if the request additional parts aren't empty: add the related subrequests
+            if self.sql_additional:
+                self.sql_additional = sql_additional_subreq % self.sql_additional
+            if self.sql_rec_additional:
+                self.sql_rec_additional = sql_rec_additional_subreq % self.sql_rec_additional
+        return super(integrity_finance, self).set_context(objects, data, ids, report_type=report_type)
+
     def get_title(self):
         return _('Entries Data Integrity')
 
     def list_checks(self):
         return queries_finance.queries
 
-    def get_results(self, sql):
+    def get_results(self, sql, query_ref):
         if not sql:
             return []
-        self.cr.execute(sql)
+        # reconciliation queries
+        if query_ref in ('unbalanced_rec_fctal', 'unbalanced_rec_booking'):
+            sql = sql % self.sql_rec_additional
+            if self.sql_rec_params:
+                self.cr.execute(sql, tuple(self.sql_rec_params))
+            else:
+                self.cr.execute(sql)
+        # other queries
+        else:
+            sql = sql % self.sql_additional
+            if self.sql_params:
+                self.cr.execute(sql, tuple(self.sql_params))
+            else:
+                self.cr.execute(sql)
         return self.cr.fetchall()
 
+    def get_reconcile_date(self, reconcile_ref):
+        """
+        Returns the reconcile_date of the reconciliation in parameter (or None if the reconciled entries have no reconcile_date).
+        Note that this date isn't retrieved directly in the original requests as there are old entries for which within a same reconciliation
+        some lines have a reconcile_date and some others haven't any, so to be consistent the results can't be "grouped by" reconcile date.
+        """
+        reconcile_date = None
+        if reconcile_ref:
+            rec_date_sql = """
+            SELECT reconcile_date
+            FROM account_move_line
+            WHERE reconcile_date IS NOT NULL
+            AND reconcile_id = (SELECT id FROM account_move_reconcile WHERE name = %s LIMIT 1)
+            LIMIT 1;
+            """
+            self.cr.execute(rec_date_sql, (reconcile_ref,))
+            rec_date_res = self.cr.fetchone()
+            if rec_date_res:
+                reconcile_date = rec_date_res[0]
+        return reconcile_date
+
     def _t(self, source):
         return _(source)
 

=== modified file 'bin/addons/msf_profile/i18n/fr_MF.po'
--- bin/addons/msf_profile/i18n/fr_MF.po	2018-10-30 10:56:14 +0000
+++ bin/addons/msf_profile/i18n/fr_MF.po	2018-11-07 14:15:37 +0000
@@ -10256,7 +10256,7 @@
 msgid "Back Order of :"
 msgstr "Back Order of :"
 
-#. modules: purchase, account, msf_outgoing, financing_contract, register_accounting, sale, product_expiry, stock
+#. modules: purchase, account, msf_outgoing, financing_contract, register_accounting, sale, product_expiry, stock, account_override
 #: view:account.bank.statement:0
 #: view:account.common.report:0
 #: view:account.move:0
@@ -10272,6 +10272,7 @@
 #: view:stock.move:0
 #: view:stock.picking:0
 #: view:free.allocation.wizard:0
+#: view:integrity.finance.wizard:0
 msgid "Dates"
 msgstr "Dates"
 
@@ -10322,7 +10323,7 @@
 #: code:addons/board/queries_finance.py:47
 #, python-format
 msgid "P&L Journal Items vs Analytic Journal Items mismatch in booking currency (except FXA and REV)"
-msgstr "Différence en devise d'enregistrement entre entrées comptables et analytiques (exceptés FAX et REV)"
+msgstr "Différence en devise d'enregistrement entre entrées comptables et analytiques (exceptés FXA et REV)"
 
 #. modules: sale, msf_supply_doc_export
 #: report:po.follow.up_rml:0
@@ -17716,7 +17717,7 @@
 msgid "Create Access"
 msgstr "Créer l'Accès "
 
-#. modules: account, base, procurement_request
+#. modules: account, base, procurement_request, account_override
 #: view:account.common.report:0
 #: view:ir.actions.act_window:0
 #: model:ir.actions.act_window,name:base.actions_ir_filters_view
@@ -17724,6 +17725,7 @@
 #: model:ir.model,name:base.model_ir_filters
 #: model:ir.ui.menu,name:base.menu_ir_filters
 #: view:sale.order:0
+#: view:integrity.finance.wizard:0
 msgid "Filters"
 msgstr "Filtres"
 
@@ -20061,7 +20063,9 @@
 msgstr "MANIFESTE DE FRET"
 
 #. modules: account_override, board
-#: model:ir.ui.menu,name:account_override.menu_integrity_finance
+#: model:ir.actions.act_window,name:account_override.action_integrity_finance_report
+#: model:ir.ui.menu,name:account_override.menu_integrity_finance_report
+#: code:addons/account_override/wizard/integrity_finance_wizard.py:111
 #: code:addons/board/report/integrity_finance.py:20
 #, python-format
 msgid "Entries Data Integrity"
@@ -22622,7 +22626,7 @@
 msgid "Populate fields order"
 msgstr "Remplir les champs commande"
 
-#. modules: account, finance
+#. modules: account, finance, account_override
 #: field:account.aged.trial.balance,filter:0
 #: field:account.balance.report,filter:0
 #: field:account.bs.report,filter:0
@@ -22639,6 +22643,7 @@
 #: field:account.report.general.ledger,filter:0
 #: field:account.vat.declaration,filter:0
 #: field:wizard.account.partner.balance.tree,filter:0
+#: field:integrity.finance.wizard,filter:0
 msgid "Filter by"
 msgstr "Filtrer par"
 
@@ -24255,7 +24260,7 @@
 msgid "Create Composition List"
 msgstr "Créer une Liste de Composition"
 
-#. modules: account, res_currency_tables, finance
+#. modules: account, res_currency_tables, finance, account_override
 #: field:account.common.partner.report,period_from:0
 #: field:account.partner.balance,period_from:0
 #: field:account.partner.ledger,period_from:0
@@ -24274,6 +24279,7 @@
 #: field:account.vat.declaration,period_from:0
 #: field:wizard.report.currency.table,start_period_id:0
 #: field:wizard.report.rates.table,start_period_id:0
+#: field:integrity.finance.wizard,period_from:0
 msgid "Start period"
 msgstr "Période de début"
 
@@ -25353,6 +25359,7 @@
 #: report:addons/consumption_calculation/report/product_likely_expire_xls.mako:86
 #: report:addons/account/report/free_allocation_report.mako:171
 #: field:free.allocation.wizard,period_id:0
+#: selection:integrity.finance.wizard,filter:0
 #, python-format
 msgid "Period"
 msgstr "Période"
@@ -27281,11 +27288,12 @@
 msgid "The number of packages by layer"
 msgstr "Le nombre de colis par couche"
 
-#. modules: account_reconciliation, account
+#. modules: account_reconciliation, account, board
 #: selection:account.journal.column,field:0
 #: field:account.move.line,reconcile_date:0
+#: code:addons/board/queries_finance.py:100
 msgid "Reconcile date"
-msgstr "Reconcile date"
+msgstr "Date de lettrage"
 
 #. module: account
 #: report:account.vat.declaration:0
@@ -28368,7 +28376,7 @@
 msgid "XML Identifier"
 msgstr "Identifiant XML"
 
-#. modules: msf_budget, account, stock_move_tracking, product, sale, stock_forecast, purchase_allocation_report, order_types
+#. modules: msf_budget, account, stock_move_tracking, product, sale, stock_forecast, purchase_allocation_report, order_types, account_override
 #: view:account.aged.trial.balance:0
 #: view:account.analytic.Journal.report:0
 #: view:account.analytic.balance:0
@@ -28385,6 +28393,7 @@
 #: view:wizard.fo.allocation.report:0
 #: view:stock.forecast:0
 #: view:stock.move.tracking:0
+#: view:integrity.finance.wizard:0
 msgid "Print"
 msgstr "Imprimer"
 
@@ -29745,10 +29754,11 @@
 msgid "Fiscal Position Template"
 msgstr "Modèle de Position Fiscale"
 
-#. modules: account, finance
+#. modules: account, finance, account_override
 #: field:account.partner.balance,period_to:0
 #: field:account.partner.ledger,period_to:0
 #: field:wizard.account.partner.balance.tree,period_to:0
+#: field:integrity.finance.wizard,period_to:0
 msgid "End period"
 msgstr "Période de fin"
 
@@ -35001,7 +35011,7 @@
 msgid "Shipment Date:"
 msgstr "Shipment Date:"
 
-#. modules: sales_followup, sync_client, procurement_request, sale, mission_stock, msf_doc_import, purchase_followup
+#. modules: sales_followup, sync_client, procurement_request, sale, mission_stock, msf_doc_import, purchase_followup, account_override
 #: field:msr_in_progress,start_date:0
 #: field:abstract.wizard.import,start_date:0
 #: field:wizard.import.batch,start_date:0
@@ -35014,6 +35024,7 @@
 #: field:po.track.changes.wizard,start_date:0
 #: field:sale.loan.stock.moves,start_date:0
 #: field:ir.track.changes.wizard,start_date:0
+#: field:integrity.finance.wizard,date_from:0
 msgid "Start date"
 msgstr "Date de début"
 
@@ -41033,7 +41044,7 @@
 msgid "Name of the batch will be ignored because the batch is 'Internal' so name is created by the system"
 msgstr "Le nom du batch sera ignoré car le batch est 'Interne' donc son nom sera créé par le système"
 
-#. modules: account, msf_instance, finance, account_mcdb
+#. modules: account, msf_instance, finance, account_mcdb, account_override
 #: report:account.general.ledger_landscape:0
 #: report:account.general.ledger_landscape_tb:0
 #: report:account.partner.balance:0
@@ -41058,6 +41069,7 @@
 #: model:ir.actions.act_window,name:msf_instance.action_msf_instance_tree
 #: model:ir.ui.menu,name:msf_instance.menu_action_msf_instance_tree
 #: view:msf.instance:0
+#: field:integrity.finance.wizard,instance_ids:0
 msgid "Proprietary Instances"
 msgstr "Instances Propriétaires"
 
@@ -44422,13 +44434,14 @@
 msgid "Reordering Mode"
 msgstr "Mode de Réapprovisionnement"
 
-#. modules: msf_doc_import, sale, sales_followup
+#. modules: msf_doc_import, sale, sales_followups, account_override
 #: field:abstract.wizard.import,end_date:0
 #: field:wizard.import.batch,end_date:0
 #: field:sale.donation.stock.moves,end_date:0
 #: field:sale.order.sourcing.progress,end_date:0
 #: field:ir.followup.location.wizard,end_date:0
 #: field:sale.followup.multi.wizard,end_date:0
+#: field:integrity.finance.wizard,date_to:0
 msgid "End date"
 msgstr "Date de fin"
 
@@ -51614,7 +51627,7 @@
 msgid "Donation Line"
 msgstr "Donation Line"
 
-#. module: account
+#. modules: account, account_override
 #: selection:account.aged.trial.balance,filter:0
 #: selection:account.balance.report,filter:0
 #: selection:account.bs.report,filter:0
@@ -51630,6 +51643,7 @@
 #: selection:account.print.journal,filter:0
 #: selection:account.report.general.ledger,filter:0
 #: selection:account.vat.declaration,filter:0
+#: selection:integrity.finance.wizard,filter:0
 msgid "No Filters"
 msgstr "Aucun Filtre"
 
@@ -54842,7 +54856,7 @@
 msgid "Africa/Timbuktu"
 msgstr "Africa/Timbuktu"
 
-#. modules: account, finance, account_mcdb, analytic_distribution
+#. modules: account, finance, account_mcdb, analytic_distribution, account_override
 #: selection:account.aged.trial.balance,filter:0
 #: selection:account.balance.report,filter:0
 #: selection:account.bs.report,filter:0
@@ -54879,6 +54893,7 @@
 #: selection:wizard.account.partner.balance.tree,filter:0
 #: view:account.period.state:0
 #: view:account.analytic.chart:0
+#: view:integrity.finance.wizard:0
 #, python-format
 msgid "Periods"
 msgstr "Périodes"
@@ -65785,7 +65800,7 @@
 msgid "PO line to confirm"
 msgstr "PO line to confirm"
 
-#. modules: account, finance, account_mcdb, register_accounting, account_hq_entries, account_override, analytic, msf_accrual, vertical_integration
+#. modules: account, finance, account_mcdb, register_accounting, account_hq_entries, account_override, analytic, msf_accrual, vertical_integration, board
 #: field:account.bank.statement.line,date:0
 #: field:account.invoice,date_invoice:0
 #: report:combined.journals.report.pdf:0
@@ -65826,6 +65841,7 @@
 #: report:addons/register_accounting/report/pending_cheque_xls.mako:218
 #: report:addons/vertical_integration/report/open_invoices_xls.mako:291
 #: field:hq.entries.split,date:0
+#: selection:integrity.finance.wizard,filter:0
 #, python-format
 msgid "Posting Date"
 msgstr "Date de Comptabilisation"
@@ -75641,7 +75657,7 @@
 msgid "Update Sent Monitor"
 msgstr "Surveillance des Mises à jour Envoyées"
 
-#. modules: msf_budget, account, account_period_closing_level, finance, vertical_integration, msf_currency_revaluation, analytic_distribution
+#. modules: msf_budget, account, account_period_closing_level, finance, vertical_integration, msf_currency_revaluation, analytic_distribution, account_override
 #: field:account.aged.trial.balance,fiscalyear_id:0
 #: field:account.balance.report,fiscalyear_id:0
 #: field:account.bs.report,fiscalyear_id:0
@@ -75672,6 +75688,7 @@
 #: field:ocp.matching.export.wizard,fiscalyear_id:0
 #: field:wizard.hq.report.oca,fiscalyear_id:0
 #: field:wizard.hq.report.ocg,fiscalyear_id:0
+#: field:integrity.finance.wizard,fiscalyear_id:0
 #, python-format
 msgid "Fiscal year"
 msgstr "Exercice Comptable"
@@ -97048,6 +97065,7 @@
 #: selection:account.balance.report,filter:0
 #: selection:account.journal.column,field:0
 #: selection:account.report.general.ledger,filter:0
+#: selection:integrity.finance.wizard,filter:0
 #: code:addons/account/report/common_report_header.py:166
 #: report:addons/account_mcdb/report/report_account_analytic_line_free_xls.mako:78
 #: report:addons/account_override/report/open_invoices_xls.mako:330
@@ -97764,6 +97782,7 @@
 #: view:wizard.import.mapping:0
 #: view:automated.export.job:0
 #: view:free.allocation.wizard:0
+#: view:integrity.finance.wizard:0
 #, python-format
 msgid "Cancel"
 msgstr "Annuler"
@@ -101982,6 +102001,30 @@
 msgid "Related entries"
 msgstr "Ecritures associées"
 
+#. module: board
+#: code:addons/board/report/integrity_finance.py:46
+#, python-format
+msgid "Either the Start period or the End period is missing."
+msgstr "Il manque soit la Période de début soit la Période de fin."
+
+#. module: board
+#: code:addons/board/report/integrity_finance.py:62
+#, python-format
+msgid "No period matches the selected criteria."
+msgstr "Aucune période ne correspond aux critères sélectionnés."
+
+#. module: account_override
+#: code:addons/account_override/period.py:129
+#, python-format
+msgid "The End period can't precede the Start period."
+msgstr "La Période de fin ne peut pas précéder la Période de début."
+
+#. module: board
+#: code:addons/board/report/integrity_finance.py:57
+#, python-format
+msgid "Either the Start date or the End date is missing."
+msgstr "Il manque soit la Date de début soit la Date de fin."
+
 #. module: stock
 #: code:addons/stock/physical_inventory.py:938
 #, python-format

=== modified file 'bin/service/web_services.py'
--- bin/service/web_services.py	2018-10-05 08:17:24 +0000
+++ bin/service/web_services.py	2018-11-07 14:15:37 +0000
@@ -1104,7 +1104,7 @@
                 tb_s = "".join(traceback.format_exception(*tb))
                 logger = netsvc.Logger()
                 logger.notifyChannel('web-services', netsvc.LOG_ERROR,
-                                     'Exception: %s\n%s' % (str(exception), tb_s))
+                                     'Exception: %s\n%s' % (tools.ustr(exception), tb_s))
                 if hasattr(exception, 'name') and hasattr(exception, 'value'):
                     self._reports[id]['exception'] = ExceptionWithTraceback(tools.ustr(exception.name), tools.ustr(exception.value))
                 else: