[FIX] l10n_fr_pos_cert: move sale closing on pos

The report sale closing that was working on journal and takes the sales
total and cumulative total on periods of 'day', 'month' and 'years'. Is
now based on the pos orders and moved to the pos certification module.

closes odoo/odoo#36967

Task-id: 47990
Signed-off-by: pimodoo <pimodoo@users.noreply.github.com>

8 files changed, 285 insertions, 2 deletions

diff --git a/addons/l10n_fr_pos_cert/__manifest__.py b/addons/l10n_fr_pos_cert/__manifest__.py
index 9e9dce4d0cf..7d96df708ce 100644
--- a/addons/l10n_fr_pos_cert/__manifest__.py
+++ b/addons/l10n_fr_pos_cert/__manifest__.py
@@ -26,11 +26,15 @@ The module adds following features:
     'auto_install': True,
     'application': False,
     'data': [
-        'views/pos_inalterability_menuitem.xml',
         'views/account_views.xml',
         'views/l10n_fr_pos_cert_templates.xml',
         'views/pos_views.xml',
+        'views/account_sale_closure.xml',
+        'views/pos_inalterability_menuitem.xml',
         'report/pos_hash_integrity.xml',
+        'data/account_sale_closure_cron.xml',
+        'security/ir.model.access.csv',
+        'security/account_closing_intercompany.xml',
     ],
     'qweb': ['static/src/xml/pos.xml'],
     'post_init_hook': '_setup_inalterability',
diff --git a/addons/l10n_fr_pos_cert/data/account_sale_closure_cron.xml b/addons/l10n_fr_pos_cert/data/account_sale_closure_cron.xml
new file mode 100644
index 00000000000..ba2bd2f3a4b
--- /dev/null
+++ b/addons/l10n_fr_pos_cert/data/account_sale_closure_cron.xml
@@ -0,0 +1,34 @@
+<odoo>
+    <record model="ir.cron" id="account_sale_closing_daily">
+        <field name="name">Generate Daily Sales Closing</field>
+        <field name="interval_number">1</field>
+        <field name="interval_type">days</field>
+        <field name="numbercall">-1</field>
+        <field name="doall" eval="False"/>
+        <field name="model_id" ref="model_account_sale_closing"/>
+        <field name="state">code</field>
+        <field name="code">model._automated_closing('daily')</field>
+    </record>
+
+    <record model="ir.cron" id="account_sale_closing_monthly">
+        <field name="name">Generate Monthly Sales Closing</field>
+        <field name="interval_number">1</field>
+        <field name="interval_type">months</field>
+        <field name="numbercall">-1</field>
+        <field name="doall" eval="False"/>
+        <field name="model_id" ref="model_account_sale_closing"/>
+        <field name="state">code</field>
+        <field name="code">model._automated_closing('monthly')</field>
+    </record>
+
+    <record model="ir.cron" id="account_sale_closing_annually">
+        <field name="name">Generate Annual Sales Closing</field>
+        <field name="interval_number">12</field>
+        <field name="interval_type">months</field>
+        <field name="numbercall">-1</field>
+        <field name="doall" eval="False"/>
+        <field name="model_id" ref="model_account_sale_closing"/>
+        <field name="state">code</field>
+        <field name="code">model._automated_closing('annually')</field>
+    </record>
+</odoo>
diff --git a/addons/l10n_fr_pos_cert/models/__init__.py b/addons/l10n_fr_pos_cert/models/__init__.py
index 33657992e1c..771f4698144 100644
--- a/addons/l10n_fr_pos_cert/models/__init__.py
+++ b/addons/l10n_fr_pos_cert/models/__init__.py
@@ -4,3 +4,4 @@ from . import account_bank_statement
 from . import account_fiscal_position
 from . import res_company
 from . import pos
+from . import account_closing
diff --git a/addons/l10n_fr_pos_cert/models/account_closing.py b/addons/l10n_fr_pos_cert/models/account_closing.py
new file mode 100644
index 00000000000..ecc77bac8a6
--- /dev/null
+++ b/addons/l10n_fr_pos_cert/models/account_closing.py
@@ -0,0 +1,167 @@
+# -*- coding: utf-8 -*-
+# Part of Odoo. See LICENSE file for full copyright and licensing details.
+from datetime import datetime, timedelta
+
+from odoo import models, api, fields
+from odoo.fields import Datetime as FieldDateTime
+from odoo.tools.translate import _
+from odoo.exceptions import UserError
+from odoo.osv.expression import AND
+
+
+class AccountClosing(models.Model):
+    """
+    This object holds an interval total and a grand total of the accounts of type receivable for a company,
+    as well as the last account_move that has been counted in a previous object
+    It takes its earliest brother to infer from when the computation needs to be done
+    in order to compute its own data.
+    """
+    _name = 'account.sale.closing'
+    _order = 'date_closing_stop desc, sequence_number desc'
+    _description = "Sale Closing"
+
+    name = fields.Char(help="Frequency and unique sequence number", required=True)
+    company_id = fields.Many2one('res.company', string='Company', readonly=True, required=True)
+    date_closing_stop = fields.Datetime(string="Closing Date", help='Date to which the values are computed', readonly=True, required=True)
+    date_closing_start = fields.Datetime(string="Starting Date", help='Date from which the total interval is computed', readonly=True, required=True)
+    frequency = fields.Selection(string='Closing Type', selection=[('daily', 'Daily'), ('monthly', 'Monthly'), ('annually', 'Annual')], readonly=True, required=True)
+    total_interval = fields.Monetary(string="Period Total", help='Total in receivable accounts during the interval, excluding overlapping periods', readonly=True, required=True)
+    cumulative_total = fields.Monetary(string="Cumulative Grand Total", help='Total in receivable accounts since the beginnig of times', readonly=True, required=True)
+    sequence_number = fields.Integer('Sequence #', readonly=True, required=True)
+    last_order_id = fields.Many2one('pos.order', string='Last Pos Order', help='Last Pos order included in the grand total', readonly=True)
+    last_order_hash = fields.Char(string='Last Order entry\'s inalteralbility hash', readonly=True)
+    currency_id = fields.Many2one('res.currency', string='Currency', help="The company's currency", readonly=True, related='company_id.currency_id', store=True)
+
+    def _query_for_aml(self, company, first_move_sequence_number, date_start):
+        params = {'company_id': company.id}
+        query = '''WITH aggregate AS (SELECT m.id AS move_id,
+                    aml.balance AS balance,
+                    aml.id as line_id
+            FROM account_move_line aml
+            JOIN account_journal j ON aml.journal_id = j.id
+            JOIN account_account acc ON acc.id = aml.account_id
+            JOIN account_account_type t ON (t.id = acc.user_type_id AND t.type = 'receivable')
+            JOIN account_move m ON m.id = aml.move_id
+            WHERE j.type = 'sale'
+                AND aml.company_id = %(company_id)s
+                AND m.state = 'posted' '''
+
+        if first_move_sequence_number is not False and first_move_sequence_number is not None:
+            params['first_move_sequence_number'] = first_move_sequence_number
+            query += '''AND m.secure_sequence_number > %(first_move_sequence_number)s'''
+        elif date_start:
+            #the first time we compute the closing, we consider only from the installation of the module
+            params['date_start'] = date_start
+            query += '''AND m.date >= %(date_start)s'''
+
+        query += " ORDER BY m.secure_sequence_number DESC) "
+        query += '''SELECT array_agg(move_id) AS move_ids,
+                           array_agg(line_id) AS line_ids,
+                           sum(balance) AS balance
+                    FROM aggregate'''
+
+        self.env.cr.execute(query, params)
+        return self.env.cr.dictfetchall()[0]
+
+    def _compute_amounts(self, frequency, company):
+        """
+        Method used to compute all the business data of the new object.
+        It will search for previous closings of the same frequency to infer the move from which
+        account move lines should be fetched.
+        @param {string} frequency: a valid value of the selection field on the object (daily, monthly, annually)
+            frequencies are literal (daily means 24 hours and so on)
+        @param {recordset} company: the company for which the closing is done
+        @return {dict} containing {field: value} for each business field of the object
+        """
+        interval_dates = self._interval_dates(frequency, company)
+        previous_closing = self.search([
+            ('frequency', '=', frequency),
+            ('company_id', '=', company.id)], limit=1, order='sequence_number desc')
+
+        first_order = self.env['pos.order']
+        date_start = interval_dates['interval_from']
+        cumulative_total = 0
+        if previous_closing:
+            first_order = previous_closing.last_order_id
+            date_start = previous_closing.create_date
+            cumulative_total += previous_closing.cumulative_total
+
+        domain = [('company_id', '=', company.id), ('state', 'in', ('paid', 'done', 'invoiced'))]
+        if first_order.l10n_fr_secure_sequence_number is not False and first_order.l10n_fr_secure_sequence_number is not None:
+            domain = AND([domain, [('l10n_fr_secure_sequence_number', '>', first_order.l10n_fr_secure_sequence_number)]])
+        elif date_start:
+            #the first time we compute the closing, we consider only from the installation of the module
+            domain = AND([domain, [('date_order', '>=', date_start)]])
+
+        orders = self.env['pos.order'].search(domain, order='date_order desc')
+
+        total_interval = sum(orders.mapped('amount_total'))
+        cumulative_total += total_interval
+
+        # We keep the reference to avoid gaps (like daily object during the weekend)
+        last_order = first_order
+        if orders:
+            last_order = orders[0]
+
+        return {'total_interval': total_interval,
+                'cumulative_total': cumulative_total,
+                'last_order_id': last_order.id,
+                'last_order_hash': last_order.l10n_fr_secure_sequence_number,
+                'date_closing_stop': interval_dates['date_stop'],
+                'date_closing_start': date_start,
+                'name': interval_dates['name_interval'] + ' - ' + interval_dates['date_stop'][:10]}
+
+    def _interval_dates(self, frequency, company):
+        """
+        Method used to compute the theoretical date from which account move lines should be fetched
+        @param {string} frequency: a valid value of the selection field on the object (daily, monthly, annually)
+            frequencies are literal (daily means 24 hours and so on)
+        @param {recordset} company: the company for which the closing is done
+        @return {dict} the theoretical date from which account move lines are fetched.
+            date_stop date to which the move lines are fetched, always now()
+            the dates are in their Odoo Database string representation
+        """
+        date_stop = datetime.utcnow()
+        interval_from = None
+        name_interval = ''
+        if frequency == 'daily':
+            interval_from = date_stop - timedelta(days=1)
+            name_interval = _('Daily Closing')
+        elif frequency == 'monthly':
+            month_target = date_stop.month > 1 and date_stop.month - 1 or 12
+            year_target = month_target < 12 and date_stop.year or date_stop.year - 1
+            interval_from = date_stop.replace(year=year_target, month=month_target)
+            name_interval = _('Monthly Closing')
+        elif frequency == 'annually':
+            year_target = date_stop.year - 1
+            interval_from = date_stop.replace(year=year_target)
+            name_interval = _('Annual Closing')
+
+        return {'interval_from': FieldDateTime.to_string(interval_from),
+                'date_stop': FieldDateTime.to_string(date_stop),
+                'name_interval': name_interval}
+
+    def write(self, vals):
+        raise UserError(_('Sale Closings are not meant to be written or deleted under any circumstances.'))
+
+    def unlink(self):
+        raise UserError(_('Sale Closings are not meant to be written or deleted under any circumstances.'))
+
+    @api.model
+    def _automated_closing(self, frequency='daily'):
+        """To be executed by the CRON to create an object of the given frequency for each company that needs it
+        @param {string} frequency: a valid value of the selection field on the object (daily, monthly, annually)
+            frequencies are literal (daily means 24 hours and so on)
+        @return {recordset} all the objects created for the given frequency
+        """
+        res_company = self.env['res.company'].search([])
+        account_closings = self.env['account.sale.closing']
+        for company in res_company.filtered(lambda c: c._is_accounting_unalterable()):
+            new_sequence_number = company.l10n_fr_closing_sequence_id.next_by_id()
+            values = self._compute_amounts(frequency, company)
+            values['frequency'] = frequency
+            values['company_id'] = company.id
+            values['sequence_number'] = new_sequence_number
+            account_closings |= account_closings.create(values)
+
+        return account_closings
diff --git a/addons/l10n_fr_pos_cert/security/account_closing_intercompany.xml b/addons/l10n_fr_pos_cert/security/account_closing_intercompany.xml
new file mode 100644
index 00000000000..a316014794e
--- /dev/null
+++ b/addons/l10n_fr_pos_cert/security/account_closing_intercompany.xml
@@ -0,0 +1,8 @@
+<odoo>
+    <record model="ir.rule" id="account_sale_closing_multi_company">
+      <field name="name">Sale Closing multi-company</field>
+      <field name="model_id" ref="model_account_sale_closing"/>
+      <field name="domain_force">[('company_id', 'in', company_ids)]</field>
+    </record>
+
+</odoo>
diff --git a/addons/l10n_fr_pos_cert/security/ir.model.access.csv b/addons/l10n_fr_pos_cert/security/ir.model.access.csv
new file mode 100644
index 00000000000..a2553eb8de9
--- /dev/null
+++ b/addons/l10n_fr_pos_cert/security/ir.model.access.csv
@@ -0,0 +1,2 @@
+id,name,model_id:id,group_id:id,perm_read,perm_write,perm_create,perm_unlink
+access_l10n_fr_pos_cert_account_sale_closing_user,l10n_fr_pos_cert.account.sale.closing.user,l10n_fr_pos_cert.model_account_sale_closing,base.group_user,1,0,0,0
diff --git a/addons/l10n_fr_pos_cert/views/account_sale_closure.xml b/addons/l10n_fr_pos_cert/views/account_sale_closure.xml
new file mode 100644
index 00000000000..24e4c14cd71
--- /dev/null
+++ b/addons/l10n_fr_pos_cert/views/account_sale_closure.xml
@@ -0,0 +1,67 @@
+<odoo>
+    <record id="list_view_account_sale_closing" model="ir.ui.view">
+        <field name="name">Sales Closings</field>
+        <field name="model">account.sale.closing</field>
+        <field name="arch" type="xml">
+            <tree create="false" import="false">
+                <field name="date_closing_start"/>
+                <field name="date_closing_stop"/>
+                <field name="company_id" groups="base.group_multi_company"/>
+                <field name="currency_id" invisible="1"/>
+                <field name="frequency"/>
+                <field name="sequence_number" groups="base.group_no_one"/>
+                <field name="total_interval"/>
+                <field name="cumulative_total"/>
+            </tree>
+        </field>
+    </record>
+
+    <record id="form_view_account_sale_closing" model="ir.ui.view">
+        <field name="name">Sales Closings</field>
+        <field name="model">account.sale.closing</field>
+        <field name="arch" type="xml">
+            <form create="false" edit="false" string="Account Closing">
+                <sheet>
+                    <div class="oe_title">
+                        <h1>
+                            <field name="name"/>
+                        </h1>
+                    </div>
+                    <group>
+                        <group>
+                            <field name="date_closing_start"/>
+                            <field name="date_closing_stop"/>
+                            <field name="frequency"/>
+                            <field name="sequence_number" groups="base.group_no_one"/>
+                        </group>
+                        <group>
+                            <field name="total_interval"/>
+                            <field name="cumulative_total"/>
+                            <field name="last_order_id" groups="account.group_account_user"/>
+                            <field name="last_order_hash" groups="account.group_account_user"/>
+                        </group>
+                        <group>
+                            <field name="company_id" groups="base.group_multi_company"/>
+                            <field name="currency_id" invisible="1"/>
+                        </group>
+                    </group>
+                </sheet>
+            </form>
+        </field>
+    </record>
+
+    <record id="action_list_view_account_sale_closing" model="ir.actions.act_window">
+        <field name="name">Sales Closings</field>
+        <field name="res_model">account.sale.closing</field>
+        <field name="view_mode">list,form</field>
+        <field name="help" type="html">
+            <p class="o_view_nocontent_nocreate">
+                The closings are created by Odoo
+                </p><p>
+                Sales closings run automatically on a daily, monthly and annual basis. It computes both period and cumulative totals from all the sales entries posted in the system after the previous closing.
+            </p>
+        </field>
+    </record>
+
+    <menuitem action="action_list_view_account_sale_closing" id="menu_account_closing_reporting" parent="l10n_fr.account_reports_fr_statements_menu" sequence="90"/>
+</odoo>
diff --git a/addons/l10n_fr_pos_cert/views/pos_inalterability_menuitem.xml b/addons/l10n_fr_pos_cert/views/pos_inalterability_menuitem.xml
index efaab7ef24f..3557204c8a3 100644
--- a/addons/l10n_fr_pos_cert/views/pos_inalterability_menuitem.xml
+++ b/addons/l10n_fr_pos_cert/views/pos_inalterability_menuitem.xml
@@ -18,6 +18,6 @@
         </field>
     </record>
         <menuitem id="pos_fr_statements_menu" name="French Statements" parent="point_of_sale.menu_point_rep" sequence="9" />
-        <menuitem action="l10n_fr.action_list_view_account_sale_closing" id="menu_account_closing" parent="pos_fr_statements_menu" sequence="80"/>
+        <menuitem action="l10n_fr_pos_cert.action_list_view_account_sale_closing" id="menu_account_closing" parent="pos_fr_statements_menu" sequence="80"/>
         <menuitem action="l10n_fr_pos_cert.action_check_pos_hash_integrity" id="menu_check_move_integrity_reporting" parent="pos_fr_statements_menu" sequence="90"/>
 </odoo>