| 1 |
# Part of Odoo. See LICENSE file for full copyright and licensing details. |
| 2 |
from datetime import datetime, timedelta |
| 3 |
|
| 4 |
from odoo import models, api, fields |
| 5 |
from odoo.fields import Datetime as FieldDateTime, Domain |
| 6 |
from dateutil.relativedelta import relativedelta |
| 7 |
from odoo.tools.translate import _ |
| 8 |
from odoo.exceptions import UserError |
| 9 |
|
| 10 |
|
| 11 |
class AccountSaleClosing(models.Model): |
| 12 |
""" |
| 13 |
This object holds an interval total and a grand total of the accounts of type receivable for a company, |
| 14 |
as well as the last account_move that has been counted in a previous object |
| 15 |
It takes its earliest brother to infer from when the computation needs to be done |
| 16 |
in order to compute its own data. |
| 17 |
""" |
| 18 |
_name = 'account.sale.closing' |
| 19 |
_order = 'date_closing_stop desc, sequence_number desc' |
| 20 |
_description = "Sale Closing" |
| 21 |
|
| 22 |
name = fields.Char(help="Frequency and unique sequence number", required=True) |
| 23 |
company_id = fields.Many2one('res.company', string='Company', readonly=True, required=True) |
| 24 |
date_closing_stop = fields.Datetime(string="Closing Date", help='Date to which the values are computed', readonly=True, required=True) |
| 25 |
date_closing_start = fields.Datetime(string="Starting Date", help='Date from which the total interval is computed', readonly=True, required=True) |
| 26 |
frequency = fields.Selection(string='Closing Type', selection=[('daily', 'Daily'), ('monthly', 'Monthly'), ('annually', 'Annual')], readonly=True, required=True) |
| 27 |
total_interval = fields.Monetary(string="Period Total", help='Total in receivable accounts during the interval, excluding overlapping periods', readonly=True, required=True) |
| 28 |
cumulative_total = fields.Monetary(string="Cumulative Grand Total", help='Total in receivable accounts since the beginnig of times', readonly=True, required=True) |
| 29 |
sequence_number = fields.Integer('Sequence #', readonly=True, required=True) |
| 30 |
last_order_id = fields.Many2one('pos.order', string='Last Pos Order', help='Last Pos order included in the grand total', readonly=True) |
| 31 |
last_order_hash = fields.Char(string='Last Order entry\'s inalteralbility hash', readonly=True) |
| 32 |
currency_id = fields.Many2one('res.currency', string='Currency', help="The company's currency", readonly=True, related='company_id.currency_id', store=True) |
| 33 |
|
| 34 |
def _query_for_aml(self, company, first_move_sequence_number, date_start): |
| 35 |
params = {'company_id': company.id} |
| 36 |
query = '''WITH aggregate AS (SELECT m.id AS move_id, |
| 37 |
aml.balance AS balance, |
| 38 |
aml.id as line_id |
| 39 |
FROM account_move_line aml |
| 40 |
JOIN account_journal j ON aml.journal_id = j.id |
| 41 |
JOIN account_account acc ON acc.id = aml.account_id |
| 42 |
JOIN account_move m ON m.id = aml.move_id |
| 43 |
JOIN res_company move_company ON move_company.id = m.company_id |
| 44 |
WHERE j.type = 'sale' |
| 45 |
AND SPLIT_PART(move_company.parent_path, '/', 1)::int = %(company_id)s |
| 46 |
AND m.state = 'posted' |
| 47 |
AND acc.account_type = 'asset_receivable' ''' |
| 48 |
|
| 49 |
if first_move_sequence_number is not False and first_move_sequence_number is not None: |
| 50 |
params['first_move_sequence_number'] = first_move_sequence_number |
| 51 |
query += '''AND m.secure_sequence_number > %(first_move_sequence_number)s''' |
| 52 |
elif date_start: |
| 53 |
#the first time we compute the closing, we consider only from the installation of the module |
| 54 |
params['date_start'] = date_start |
| 55 |
query += '''AND m.date >= %(date_start)s''' |
| 56 |
|
| 57 |
query += " ORDER BY m.secure_sequence_number DESC) " |
| 58 |
query += '''SELECT array_agg(move_id) AS move_ids, |
| 59 |
array_agg(line_id) AS line_ids, |
| 60 |
sum(balance) AS balance |
| 61 |
FROM aggregate''' |
| 62 |
|
| 63 |
self.env.cr.execute(query, params) |
| 64 |
return self.env.cr.dictfetchall()[0] |
| 65 |
|
| 66 |
def _compute_amounts(self, frequency, company): |
| 67 |
""" |
| 68 |
Method used to compute all the business data of the new object. |
| 69 |
It will search for previous closings of the same frequency to infer the move from which |
| 70 |
account move lines should be fetched. |
| 71 |
@param {string} frequency: a valid value of the selection field on the object (daily, monthly, annually) |
| 72 |
frequencies are literal (daily means 24 hours and so on) |
| 73 |
@param {recordset} company: the company for which the closing is done |
| 74 |
@return {dict} containing {field: value} for each business field of the object |
| 75 |
""" |
| 76 |
interval_dates = self._interval_dates(frequency, company) |
| 77 |
previous_closing = self.search([ |
| 78 |
('frequency', '=', frequency), |
| 79 |
('company_id', '=', company.id)], limit=1, order='sequence_number desc') |
| 80 |
|
| 81 |
first_order = self.env['pos.order'] |
| 82 |
date_start = interval_dates['interval_from'] |
| 83 |
cumulative_total = 0 |
| 84 |
if previous_closing: |
| 85 |
first_order = previous_closing.last_order_id |
| 86 |
date_start = previous_closing.create_date |
| 87 |
cumulative_total += previous_closing.cumulative_total |
| 88 |
|
| 89 |
domain = Domain('company_id', '=', company.id) & Domain('state', 'in', ('paid', 'done')) |
| 90 |
if first_order.l10n_fr_secure_sequence_number is not False and first_order.l10n_fr_secure_sequence_number is not None: |
| 91 |
domain &= Domain('l10n_fr_secure_sequence_number', '>', first_order.l10n_fr_secure_sequence_number) |
| 92 |
elif date_start: |
| 93 |
# the first time we compute the closing, we consider only from the installation of the module |
| 94 |
domain &= Domain('date_order', '>=', date_start) |
| 95 |
|
| 96 |
orders = self.env['pos.order'].search(domain, order='date_order desc') |
| 97 |
|
| 98 |
total_interval = sum(orders.mapped('amount_total')) |
| 99 |
cumulative_total += total_interval |
| 100 |
|
| 101 |
# We keep the reference to avoid gaps (like daily object during the weekend) |
| 102 |
last_order = first_order |
| 103 |
if orders: |
| 104 |
last_order = orders[0] |
| 105 |
|
| 106 |
return {'total_interval': total_interval, |
| 107 |
'cumulative_total': cumulative_total, |
| 108 |
'last_order_id': last_order.id, |
| 109 |
'last_order_hash': last_order.l10n_fr_secure_sequence_number, |
| 110 |
'date_closing_stop': interval_dates['date_stop'], |
| 111 |
'date_closing_start': date_start, |
| 112 |
'name': interval_dates['name_interval'] + ' - ' + interval_dates['date_stop'][:10]} |
| 113 |
|
| 114 |
def _interval_dates(self, frequency, company): |
| 115 |
""" |
| 116 |
Method used to compute the theoretical date from which account move lines should be fetched |
| 117 |
@param {string} frequency: a valid value of the selection field on the object (daily, monthly, annually) |
| 118 |
frequencies are literal (daily means 24 hours and so on) |
| 119 |
@param {recordset} company: the company for which the closing is done |
| 120 |
@return {dict} the theoretical date from which account move lines are fetched. |
| 121 |
date_stop date to which the move lines are fetched, always now() |
| 122 |
the dates are in their Odoo Database string representation |
| 123 |
""" |
| 124 |
date_stop = datetime.utcnow() |
| 125 |
interval_from = None |
| 126 |
name_interval = '' |
| 127 |
if frequency == 'daily': |
| 128 |
interval_from = date_stop - timedelta(days=1) |
| 129 |
name_interval = _('Daily Closing') |
| 130 |
elif frequency == 'monthly': |
| 131 |
interval_from = date_stop - relativedelta(months=1) |
| 132 |
name_interval = _('Monthly Closing') |
| 133 |
elif frequency == 'annually': |
| 134 |
interval_from = date_stop - relativedelta(years=1) |
| 135 |
name_interval = _('Annual Closing') |
| 136 |
|
| 137 |
return {'interval_from': FieldDateTime.to_string(interval_from), |
| 138 |
'date_stop': FieldDateTime.to_string(date_stop), |
| 139 |
'name_interval': name_interval} |
| 140 |
|
| 141 |
def write(self, vals): |
| 142 |
raise UserError(_('Sale Closings are not meant to be written or deleted under any circumstances.')) |
| 143 |
|
| 144 |
@api.ondelete(at_uninstall=True) |
| 145 |
def _unlink_never(self): |
| 146 |
raise UserError(_('Sale Closings are not meant to be written or deleted under any circumstances.')) |
| 147 |
|
| 148 |
@api.model |
| 149 |
def _automated_closing(self, frequency='daily'): |
| 150 |
"""To be executed by the CRON to create an object of the given frequency for each company that needs it |
| 151 |
@param {string} frequency: a valid value of the selection field on the object (daily, monthly, annually) |
| 152 |
frequencies are literal (daily means 24 hours and so on) |
| 153 |
@return {recordset} all the objects created for the given frequency |
| 154 |
""" |
| 155 |
res_company = self.env['res.company'].search([]) |
| 156 |
account_closings = self.env['account.sale.closing'] |
| 157 |
for company in res_company.filtered(lambda c: c._is_accounting_unalterable()): |
| 158 |
new_sequence_number = company.l10n_fr_closing_sequence_id.next_by_id() |
| 159 |
values = self._compute_amounts(frequency, company) |
| 160 |
values['frequency'] = frequency |
| 161 |
values['company_id'] = company.id |
| 162 |
values['sequence_number'] = new_sequence_number |
| 163 |
account_closings |= account_closings.create(values) |
| 164 |
|
| 165 |
return account_closings |