217 lines
9.1 KiB
Python
217 lines
9.1 KiB
Python
import json
|
|
import logging
|
|
import re
|
|
from collections import defaultdict
|
|
from odoo import models, fields, api
|
|
|
|
_logger = logging.getLogger(__name__)
|
|
|
|
|
|
class FusionRecurringPattern(models.Model):
|
|
_name = 'fusion.recurring.pattern'
|
|
_description = 'Recurring Bank Transaction Pattern (AI Cache)'
|
|
_order = 'occurrences desc'
|
|
|
|
name = fields.Char(string='Pattern Name', required=True)
|
|
ref_keyword = fields.Char(
|
|
string='Reference Keyword',
|
|
help='The payment_ref substring that identifies this pattern.',
|
|
index=True,
|
|
)
|
|
amount = fields.Float(string='Amount', digits=(12, 2))
|
|
amount_is_fixed = fields.Boolean(
|
|
string='Fixed Amount',
|
|
help='True if the amount is always the same. False if it varies.',
|
|
)
|
|
journal_id = fields.Many2one('account.journal', string='Bank Journal')
|
|
company_id = fields.Many2one(
|
|
'res.company', string='Company',
|
|
default=lambda self: self.env.company,
|
|
)
|
|
|
|
# How this was coded historically
|
|
expense_account_id = fields.Many2one(
|
|
'account.account', string='Expense Account',
|
|
)
|
|
expense_account_code = fields.Char(
|
|
related='expense_account_id.code', string='Account Code', store=True,
|
|
)
|
|
has_hst = fields.Boolean(string='Has HST')
|
|
partner_id = fields.Many2one('res.partner', string='Partner')
|
|
reconcile_model_id = fields.Many2one(
|
|
'account.reconcile.model', string='Reconciliation Model',
|
|
help='If this pattern was handled by a reconciliation model.',
|
|
)
|
|
|
|
# AI-readable instructions
|
|
action_note = fields.Text(
|
|
string='Action (AI-Readable)',
|
|
help='Plain English instructions for the AI on how to handle this pattern.',
|
|
)
|
|
|
|
# Stats
|
|
occurrences = fields.Integer(string='Times Seen')
|
|
first_seen = fields.Date(string='First Seen')
|
|
last_seen = fields.Date(string='Last Seen')
|
|
last_computed = fields.Datetime(string='Last Computed')
|
|
|
|
_sql_constraints = [
|
|
('pattern_uniq', 'unique(ref_keyword, amount, company_id)',
|
|
'One pattern per keyword+amount per company'),
|
|
]
|
|
|
|
def _rebuild_all_patterns(self, min_occurrences=3, since='2024-01-01'):
|
|
"""Scan reconciled bank lines for recurring patterns and cache how they were coded."""
|
|
_logger.info("Rebuilding recurring patterns (min=%d, since=%s)...", min_occurrences, since)
|
|
companies = self.env['res.company'].search([])
|
|
total_created = 0
|
|
total_updated = 0
|
|
|
|
for company in companies:
|
|
# Step 1: Find recurring ref+amount combinations
|
|
self.env.cr.execute("""
|
|
SELECT LEFT(bsl.payment_ref, 60) as ref_pattern,
|
|
bsl.amount,
|
|
count(*) as occurrences,
|
|
MIN(am.date) as first_seen,
|
|
MAX(am.date) as last_seen,
|
|
MODE() WITHIN GROUP (ORDER BY am.journal_id) as journal_id
|
|
FROM account_bank_statement_line bsl
|
|
JOIN account_move am ON bsl.move_id = am.id
|
|
WHERE bsl.is_reconciled = true
|
|
AND am.company_id = %s
|
|
AND am.date >= %s
|
|
AND bsl.payment_ref IS NOT NULL
|
|
AND bsl.payment_ref != ''
|
|
GROUP BY LEFT(bsl.payment_ref, 60), bsl.amount
|
|
HAVING count(*) >= %s
|
|
ORDER BY count(*) DESC
|
|
LIMIT 200
|
|
""", (company.id, since, min_occurrences))
|
|
patterns = self.env.cr.dictfetchall()
|
|
|
|
for pat in patterns:
|
|
ref = pat['ref_pattern'].strip()
|
|
if not ref or len(ref) < 3:
|
|
continue
|
|
|
|
# Step 2: Trace how one instance was coded
|
|
self.env.cr.execute("""
|
|
SELECT aml.account_id, aml.tax_line_id, aml.partner_id
|
|
FROM account_bank_statement_line bsl
|
|
JOIN account_move am ON bsl.move_id = am.id
|
|
JOIN account_move_line aml ON aml.move_id = am.id
|
|
WHERE bsl.is_reconciled = true
|
|
AND bsl.payment_ref ILIKE %s
|
|
AND bsl.amount = %s
|
|
AND am.company_id = %s
|
|
AND aml.display_type NOT IN ('line_section', 'line_note')
|
|
AND aml.account_id NOT IN (
|
|
SELECT default_account_id FROM account_journal
|
|
WHERE company_id = %s AND default_account_id IS NOT NULL
|
|
)
|
|
ORDER BY bsl.id DESC
|
|
LIMIT 5
|
|
""", (f'%{ref[:40]}%', pat['amount'], company.id, company.id))
|
|
coded_lines = self.env.cr.dictfetchall()
|
|
|
|
expense_account_id = None
|
|
has_hst = False
|
|
partner_id = None
|
|
|
|
for cl in coded_lines:
|
|
if cl['tax_line_id']:
|
|
has_hst = True
|
|
elif cl['account_id'] and not expense_account_id:
|
|
acct = self.env['account.account'].browse(cl['account_id'])
|
|
if acct.exists() and acct.account_type in (
|
|
'expense', 'expense_direct_cost', 'expense_depreciation',
|
|
'asset_non_current', 'liability_non_current',
|
|
):
|
|
expense_account_id = cl['account_id']
|
|
if cl['partner_id'] and not partner_id:
|
|
partner_id = cl['partner_id']
|
|
|
|
# Build a friendly name
|
|
clean_ref = re.sub(r'[X*]{3,}[\w-]*', '', ref).strip()
|
|
clean_ref = re.sub(r'\s{2,}', ' ', clean_ref)[:50]
|
|
|
|
# Build AI action note
|
|
acct_name = ''
|
|
if expense_account_id:
|
|
acct = self.env['account.account'].browse(expense_account_id)
|
|
acct_name = f'{acct.code} {acct.name}' if acct.exists() else ''
|
|
|
|
partner_name = ''
|
|
if partner_id:
|
|
p = self.env['res.partner'].browse(partner_id)
|
|
partner_name = p.name if p.exists() else ''
|
|
|
|
action_parts = [f'RECURRING PAYMENT (seen {pat["occurrences"]} times).']
|
|
if expense_account_id:
|
|
action_parts.append(f'Post to account: {acct_name}.')
|
|
if has_hst:
|
|
action_parts.append('HST applies — split with 13% ITC.')
|
|
else:
|
|
action_parts.append('No HST — post without tax.')
|
|
if partner_name:
|
|
action_parts.append(f'Partner: {partner_name}.')
|
|
action_parts.append('Apply same coding as previous occurrences — no user input needed.')
|
|
|
|
action_note = ' '.join(action_parts)
|
|
|
|
# Step 3: Check if a reconciliation model already handles this pattern
|
|
reco_model_id = None
|
|
try:
|
|
reco_models = self.env['account.reconcile.model'].search([
|
|
('company_id', '=', company.id),
|
|
('active', '=', True),
|
|
('match_label_param', '!=', False),
|
|
])
|
|
ref_lower = ref.lower()
|
|
for rm in reco_models:
|
|
if rm.match_label_param and rm.match_label_param.lower() in ref_lower:
|
|
reco_model_id = rm.id
|
|
action_parts.append(
|
|
f'Reconciliation model "{rm.name}" (ID:{rm.id}) already handles this — '
|
|
f'use apply_reconcile_model to apply it automatically.'
|
|
)
|
|
break
|
|
except Exception:
|
|
pass
|
|
|
|
# Upsert
|
|
existing = self.search([
|
|
('ref_keyword', '=', ref),
|
|
('amount', '=', pat['amount']),
|
|
('company_id', '=', company.id),
|
|
], limit=1)
|
|
|
|
vals = {
|
|
'name': clean_ref,
|
|
'ref_keyword': ref,
|
|
'amount': pat['amount'],
|
|
'amount_is_fixed': True,
|
|
'journal_id': pat['journal_id'],
|
|
'company_id': company.id,
|
|
'expense_account_id': expense_account_id,
|
|
'has_hst': has_hst,
|
|
'partner_id': partner_id,
|
|
'reconcile_model_id': reco_model_id,
|
|
'action_note': action_note,
|
|
'occurrences': pat['occurrences'],
|
|
'first_seen': pat['first_seen'],
|
|
'last_seen': pat['last_seen'],
|
|
'last_computed': fields.Datetime.now(),
|
|
}
|
|
|
|
if existing:
|
|
existing.write(vals)
|
|
total_updated += 1
|
|
else:
|
|
self.create(vals)
|
|
total_created += 1
|
|
|
|
_logger.info("Recurring patterns rebuilt: %d created, %d updated", total_created, total_updated)
|
|
return {'created': total_created, 'updated': total_updated}
|