File size: 16,327 Bytes
4cfab62
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2e7e617
4cfab62
 
 
ce9586e
4cfab62
 
dec006b
4cfab62
dec006b
4cfab62
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2e7e617
 
 
ce9586e
 
2e7e617
 
 
 
 
 
 
 
 
 
 
 
ce9586e
 
 
 
 
 
 
 
 
 
 
4cfab62
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
dec006b
 
 
 
 
 
 
 
 
 
 
 
 
 
4cfab62
 
dec006b
4cfab62
 
 
 
 
 
 
dec006b
 
 
4cfab62
 
dec006b
4cfab62
dec006b
 
4cfab62
 
 
 
 
 
 
 
 
 
dec006b
 
2e7e617
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
"""Collections statements β€” the send layer behind the Collections page's Statements section.

Folded in from the standalone collections_app (2026-07-05). THE one sanctioned exception to the
app's read-only-on-Odoo rule, unchanged from the standalone tool: WRITE is whitelisted to exactly
one operation β€”

    create on mail.mail   (queueing an outbound statement email)

A mail.mail record with state='outgoing' is picked up by Odoo's "Mail: Email Queue Manager" cron
(every ~15 min) and delivered through the company's Office 365 relay. Because we set model/res_id,
each sent statement also appears in the customer's chatter in Odoo β€” the audit log lives where AR
already works. This module has its OWN narrow XML-RPC client; the app-wide odoo_client stays
hard-blocking on all writes. The UI gates the section to admin users; SAFE_MODE is enforced HERE
in the data layer so the UI cannot bypass it.

Env (Space secrets / .env): ODOO_URL, ODOO_DB, ODOO_USER, ODOO_API_KEY
Optional: SAFE_MODE (default ON; only 0/false/no turn it off), SAFE_RECIPIENTS, SENDER_NAME, SENDER_EMAIL, REPLY_TO,
COMPANY_NAME, ROYAL_MAIL_SERVER_ID, ROYAL_AUTHOR_ID
"""
import os
import logging
import datetime as dt
import xmlrpc.client
from html import escape
from pathlib import Path
from urllib.parse import urlsplit

try:  # self-contained: load credentials from the app root .env if present (HF uses Secrets)
    from dotenv import load_dotenv
    load_dotenv(Path(__file__).resolve().parents[1] / '.env')
except Exception:
    pass

WRITE_WHITELIST = {('mail.mail', 'create')}

EXCLUDE_NAMES = {'GIFTWARE DEALS'}  # the Amazon channel β€” not part of Fisch or Royal collections
DOMAIN = [('followup_reminder_type', '=', 'automatic'), ('credit', '>', 1)]

COMPANY = os.environ.get('COMPANY_NAME', 'Royal Imports')

# --- Sender identity (statements go out AS Royal Imports) ---
# Odoo routes outbound mail to the matching ir.mail_server by from_filter, and the Office 365
# relay only accepts sends as its authenticated address. The "Office 365 - Royal" server (id 2)
# authenticates as contact@royalimports.com with from_filter='contact@royalimports.com' β€” so the
# From MUST be that address for delivery to succeed. Friendly display name; replies routed to AR.
SENDER_NAME = os.environ.get('SENDER_NAME', 'Royal Imports Accounts Receivable')
SENDER_EMAIL = os.environ.get('SENDER_EMAIL', 'contact@royalimports.com')
REPLY_TO = os.environ.get('REPLY_TO', 'accounting@royalimports.com')
ROYAL_MAIL_SERVER_ID = int(os.environ.get('ROYAL_MAIL_SERVER_ID', '2'))
ROYAL_AUTHOR_ID = int(os.environ.get('ROYAL_AUTHOR_ID', '8978'))  # "Royal Imports" partner

SENDER_DISPLAY = f'"{SENDER_NAME}" <{SENDER_EMAIL}>'


class WriteBlocked(RuntimeError):
    pass


class SafeModeBlocked(RuntimeError):
    pass


# --- Testing guardrail -------------------------------------------------------
# While SAFE_MODE is on, NO email can be queued to any address outside the allow-list β€” enforced
# here in the data layer so the UI cannot bypass it. Default: ON; only an explicit recognised
# opt-out turns it off. An empty or blank value is NOT an opt-out.
# To go live for real customers, set the Space secret SAFE_MODE=0.
#: [D-349] 2026-09-30, S68-T71: safe_mode_from keeps unset, empty, blank and unknown values ON;
#: read_safe_mode keeps a raised setting read ON. Only 0/false/no opt out.
SAFE_MODE_OPT_OUT = frozenset({'0', 'false', 'no'})


def safe_mode_from(value):
    """True (guardrail ON) unless `value` is one of the recognised opt-out tokens.

    Fails CLOSED on purpose: an empty or blank SAFE_MODE used to read as an opt-out, which
    silently disarmed the only guard on the one path in the product allowed to write to Odoo.
    """
    return str(value if value is not None else '').strip().lower() not in SAFE_MODE_OPT_OUT


def read_safe_mode(getter=None) -> bool:
    """Keep the guard on if reading or interpreting its setting raises."""
    try:
        value = os.environ.get('SAFE_MODE', '1') if getter is None else getter('SAFE_MODE', '1')
        return safe_mode_from(value)
    except Exception:
        logging.getLogger(__name__).warning('SAFE_MODE setting could not be read; guard remains ON')
        return True


SAFE_MODE = read_safe_mode()
SAFE_RECIPIENTS = {a.strip().lower() for a in
                   os.environ.get('SAFE_RECIPIENTS', 'farhan@teamroyalimports.com').split(',')
                   if a.strip()}


def safe_recipient_ok(addr):
    return (not SAFE_MODE) or (str(addr or '').strip().lower() in SAFE_RECIPIENTS)


class Odoo:
    """Narrow client: read anything, write ONLY the whitelisted mail.mail create."""

    def __init__(self):
        self.url = os.environ.get('ODOO_URL', '').rstrip('/')
        self.db = os.environ.get('ODOO_DB', '')
        self.user = os.environ.get('ODOO_USER', '')
        self.key = os.environ.get('ODOO_API_KEY', '')
        missing = [k for k, v in [('ODOO_URL', self.url), ('ODOO_DB', self.db),
                                   ('ODOO_USER', self.user), ('ODOO_API_KEY', self.key)] if not v]
        if missing:
            raise RuntimeError(f"Missing secrets: {', '.join(missing)}")
        common = xmlrpc.client.ServerProxy(f'{self.url}/xmlrpc/2/common')
        self.uid = common.authenticate(self.db, self.user, self.key, {})
        if not self.uid:
            raise RuntimeError('Odoo authentication failed')
        self.models = xmlrpc.client.ServerProxy(f'{self.url}/xmlrpc/2/object')

    def _exec(self, model, method, args, kwargs=None):
        mutating = method in ('write', 'create', 'unlink', 'copy') or \
            any(method.startswith(p) for p in ('action_', 'button_', 'do_', 'send_',
                                               'set_', 'update_', 'process_'))
        if mutating and (model, method) not in WRITE_WHITELIST:
            raise WriteBlocked(f'{method} on {model} is not allowed from this app')
        return self.models.execute_kw(self.db, self.uid, self.key,
                                      model, method, args, kwargs or {})

    def search_read(self, model, domain=None, fields=None, limit=None, order=None):
        kw = {'fields': fields or []}
        if limit is not None:
            kw['limit'] = limit
        if order:
            kw['order'] = order
        return self._exec(model, 'search_read', [domain or []], kw)

    def queue_mail(self, payload):
        """The single allowed write: queue an outbound email."""
        return self._exec('mail.mail', 'create', [payload])


# --------------------------------------------------------------- data builders
def load_collection_list(odoo):
    """The saved follow-up filter (Reminders=Automatic, Receivable>$1), GIFTWARE excluded,
    with priority tiers."""
    partners = odoo.search_read('res.partner', DOMAIN,
        ['name', 'credit', 'total_overdue', 'followup_status',
         'followup_next_action_date', 'followup_responsible_id',
         'email', 'phone', 'mobile'])
    partners = [p for p in partners if (p['name'] or '').strip().upper() not in EXCLUDE_NAMES]
    pids = [p['id'] for p in partners]

    docs = odoo.search_read('account.move',
        [('move_type', 'in', ['out_invoice', 'out_refund']), ('state', '=', 'posted'),
         ('payment_state', 'in', ['not_paid', 'partial']), ('partner_id', 'in', pids)],
        ['name', 'partner_id', 'move_type', 'invoice_date', 'invoice_date_due',
         'amount_total', 'amount_residual_signed'])

    today = dt.date.today()
    by_partner = {}
    for d in docs:
        pid = d['partner_id'][0]
        due = d.get('invoice_date_due')
        try:
            days = (today - dt.date.fromisoformat(due)).days if due else 0
        except Exception:
            days = 0
        d['days_overdue'] = max(days, 0)
        d['open_signed'] = d['amount_residual_signed']
        by_partner.setdefault(pid, []).append(d)

    rows = []
    for p in partners:
        pid = p['id']
        odoo_overdue = float(p.get('total_overdue') or 0)
        docs_p = sorted(by_partner.get(pid, []), key=lambda x: x.get('invoice_date_due') or '')
        inv_overdue = sum(d['open_signed'] for d in docs_p if d['days_overdue'] > 0)
        oldest = max((d['days_overdue'] for d in docs_p), default=0)
        gap = inv_overdue - odoo_overdue
        reconcile = abs(gap) > 50

        if odoo_overdue >= 5000 or (odoo_overdue > 0 and oldest > 90):
            tier = 'A-Urgent'
        elif odoo_overdue >= 1000 or (odoo_overdue > 0 and oldest > 30):
            tier = 'B-Active'
        elif odoo_overdue > 0:
            tier = 'C-Light'
        else:
            tier = 'Monitor'

        rows.append({
            'partner_id': pid,
            'Customer': p['name'],
            'Tier': tier,
            'Overdue': odoo_overdue,
            'Receivable': float(p.get('credit') or 0),
            'Oldest (days)': oldest,
            'Open Docs': len(docs_p),
            'Email': p.get('email') or '',
            'Phone': p.get('phone') or p.get('mobile') or '',
            'Status': (p.get('followup_status') or '').replace('_', ' '),
            'Reconcile?': 'YES' if reconcile else '',
            '_docs': docs_p,
        })
    tier_rank = {'A-Urgent': 0, 'B-Active': 1, 'C-Light': 2, 'Monitor': 3}
    rows.sort(key=lambda r: (tier_rank[r['Tier']], -r['Overdue']))
    return rows


# --------------------------------------------------------------- statement email
# β›” STANDING RULE 2, AND THIS IS THE ONE STRING IN THE PRODUCT THAT LEAVES THE BUILDING.
# Every other finding `web_prose` reports is copy on a screen somebody here opens; this is the
# SUBJECT LINE of mail queued to a real debtor, over the single sanctioned Odoo write. It read
# 'Statement of Account (em dash) {company} (em dash) {month}' until W36-T42.
DEFAULT_SUBJECT = 'Statement of Account from {company}, {month}'
DEFAULT_INTRO = (
    'Dear {customer},<br><br>'
    'Please find below your current statement of account with {company}. '
    'According to our records, the following invoices remain open:'
)
DEFAULT_FOOTER = (
    'If you have already sent payment, please disregard this notice, and thank you. '
    'For any questions about an invoice, simply reply to this email.<br><br>'
    'Thank you for your business,<br>{company}<br>Accounts Receivable'
)


def render_statement_html(row, intro_tpl=DEFAULT_INTRO, footer_tpl=DEFAULT_FOOTER):
    month = dt.date.today().strftime('%B %Y')
    intro = intro_tpl.format(customer=row['Customer'], company=COMPANY, month=month)
    footer = footer_tpl.format(customer=row['Customer'], company=COMPANY, month=month)

    lines = []
    total_open = 0.0
    for d in row['_docs']:
        kind = 'Credit Note' if d['move_type'] == 'out_refund' else 'Invoice'
        amt = d['open_signed']
        total_open += amt
        overdue_txt = f"{d['days_overdue']}d overdue" if d['days_overdue'] > 0 else 'current'
        color = '#C0392B' if d['days_overdue'] > 0 else '#1F4E78'
        lines.append(
            f"<tr><td style='padding:6px 10px;border-bottom:1px solid #e3e8ef'>{d['name']} <span style='color:#888'>({kind})</span></td>"
            f"<td style='padding:6px 10px;border-bottom:1px solid #e3e8ef'>{d.get('invoice_date') or ''}</td>"
            f"<td style='padding:6px 10px;border-bottom:1px solid #e3e8ef'>{d.get('invoice_date_due') or ''}</td>"
            f"<td style='padding:6px 10px;border-bottom:1px solid #e3e8ef;color:{color}'>{overdue_txt}</td>"
            f"<td style='padding:6px 10px;border-bottom:1px solid #e3e8ef;text-align:right'>${amt:,.2f}</td></tr>")

    table = (
        "<table style='border-collapse:collapse;font-size:14px;margin:14px 0'>"
        "<tr style='background:#1F4E78;color:#fff'>"
        "<th style='padding:7px 10px;text-align:left'>Document</th>"
        "<th style='padding:7px 10px;text-align:left'>Date</th>"
        "<th style='padding:7px 10px;text-align:left'>Due</th>"
        "<th style='padding:7px 10px;text-align:left'>Status</th>"
        "<th style='padding:7px 10px;text-align:right'>Open Balance</th></tr>"
        + ''.join(lines) +
        f"<tr><td colspan='4' style='padding:8px 10px;font-weight:bold;text-align:right'>Total open</td>"
        f"<td style='padding:8px 10px;font-weight:bold;text-align:right'>${total_open:,.2f}</td></tr>"
        f"<tr><td colspan='4' style='padding:2px 10px;font-weight:bold;text-align:right;color:#C0392B'>Of which overdue</td>"
        f"<td style='padding:2px 10px;font-weight:bold;text-align:right;color:#C0392B'>${row['Overdue']:,.2f}</td></tr>"
        "</table>")

    return (f"<div style='font-family:Calibri,Arial,sans-serif;color:#1a1a1a;font-size:14px'>"
            f"{intro}{table}{footer}</div>")


def customer_card_message(row, link):
    """Render the exact plain text confirmation and its escaped mail body."""
    parsed = urlsplit(link)
    if (parsed.scheme not in {'https', 'http'} or not parsed.hostname
            or parsed.username or parsed.password or any(c in link for c in '\r\n')):
        raise ValueError('The customer card link is unavailable.')
    subject = f'Your customer card from {COMPANY}'
    body = f"Dear {row['Customer']},\n\nYour customer card:\n{link}\n\n{COMPANY}"
    html = (f"<p>Dear {escape(str(row['Customer']))},<br><br>Your customer card:<br>"
            f'<a href="{escape(link, quote=True)}">{escape(link)}</a><br><br>{escape(COMPANY)}</p>')
    return {'to': row['Email'], 'subject': subject, 'body': body,
            'html': html}


def queue_statement(odoo, row, subject_tpl=DEFAULT_SUBJECT,
                    intro_tpl=DEFAULT_INTRO, footer_tpl=DEFAULT_FOOTER,
                    override_to=None, *, card_link=None):
    """Queue one statement email in Odoo. Returns mail.mail id.
    override_to: send to a different address (used by the test-send button)."""
    to = override_to or row['Email']
    if not to:
        raise ValueError(f"{row['Customer']} has no email address")
    # Hard guardrail β€” refuse any recipient outside the allow-list while SAFE_MODE is on.
    if not safe_recipient_ok(to):
        raise SafeModeBlocked('Safe mode does not allow this recipient address.')
    if any(c in to for c in '\r\n'):
        raise ValueError('The customer email address is invalid.')
    month = dt.date.today().strftime('%B %Y')
    subject = subject_tpl.format(customer=row['Customer'], company=COMPANY, month=month)
    message = customer_card_message(row, card_link) if card_link is not None else None
    payload = {
        'subject': message['subject'] if message else subject,
        'body_html': message['html'] if message else render_statement_html(row, intro_tpl, footer_tpl),
        'email_to': to,
        'email_from': SENDER_DISPLAY,            # From: Royal Imports
        'reply_to': REPLY_TO,                    # replies -> AR
        'mail_server_id': ROYAL_MAIL_SERVER_ID,  # force Royal O365 relay
        'author_id': ROYAL_AUTHOR_ID,            # clean attribution in chatter
        'state': 'outgoing',
        'auto_delete': False,
        'model': 'res.partner',
        'res_id': row['partner_id'],
    }
    client = odoo() if callable(odoo) else odoo
    return client.queue_mail(payload)


def queue_statement_batch(odoo, rows, subject_tpl=DEFAULT_SUBJECT,
                          intro_tpl=DEFAULT_INTRO, footer_tpl=DEFAULT_FOOTER):
    """Queue one statement per row through the existing guarded statement sender.

    A missing address or a safe-mode refusal does not stop the remaining rows.
    """
    created, refused = [], []
    for row in rows:
        try:
            mail_id = queue_statement(odoo, row, subject_tpl, intro_tpl, footer_tpl)
        except SafeModeBlocked:
            refused.append({"customer": row["Customer"],
                            "reason": "Safe mode does not allow this recipient address."})
        except ValueError:
            reason = ("No email address is saved for this customer." if not row.get("Email")
                      else "The statement could not be prepared from the current customer details.")
            refused.append({"customer": row["Customer"], "reason": reason})
        else:
            created.append({"customer": row["Customer"], "to": row["Email"], "mailId": mail_id})
    return {"created": created, "refused": refused, "safe_mode": bool(SAFE_MODE)}