File size: 14,144 Bytes
149aa87
 
 
 
 
 
 
 
 
 
 
 
 
2e7e617
149aa87
 
 
 
 
 
 
 
 
 
 
 
2e7e617
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
149aa87
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
82cdb59
149aa87
 
 
 
 
 
 
 
 
 
 
 
 
 
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
"""modules/management.py — Management Income Statements (FFS-M / RI-M / GD-M / BU-M),
reproduced from RAW ODOO for the Sep-2024 cutover onward.

Domain logic only (pure: data in -> plain dicts out). The heavy lifting is in
core/management_pl.py (live clean-entity pull + `-M = clean + overlay`); this module shapes it
for the page and provides validate(). See MODEL_LOGIC_MAP.md §10.

Company-level financial reporting with its own entity + month picker (the management basis spans
Fisch / Royal / Giftware + a consolidation — not the 2-team Fisch/Royal DBA brand), so it is an HQ
module that ignores the global brand selector.
"""
import json
import os
from copy import deepcopy

from core import management_pl as MP
from core import odoo as O

ENTITIES = [('FFS', 'Fisch (FFS)'), ('RI', 'Royal (RI)'), ('GD', 'Giftware (GD)'), ('BU', 'Consolidated')]
ENTITY_LABEL = dict(ENTITIES)
_EXCL_PATH = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), 'data', 'mgmt_exclusions.json')
_SECTION_ORDER = ['income', 'other_income', 'cogs', 'expense', 'deprec', '']
_SECTION_LABEL = {'income': 'Income', 'other_income': 'Other income', 'cogs': 'Cost of revenue',
                  'expense': 'Operating expense', 'deprec': 'Depreciation', '': ''}


_MANAGEMENT_COMPARISON_DASHBOARD = {
    'key': 'management-comparison',
    'title': 'Actual vs plan',
    'kind': 'comparison',
    'columns': ['Actual', 'Budget', 'Prior'],
    'rows': [
        {
            'key': 'revenue', 'label': 'Revenue',
            'basisLabel': 'Order basis: confirmed sale/done wholesale lines, untaxed, GIFTWARE DEALS excluded',
            'actual': {'state': 'fact', 'factKey': 'revenue'},
            'budget': {
                'state': 'unavailable',
                'missingSource': 'No approved budget series is bound to the canonical Finance mirror.',
            },
            'prior': {
                'state': 'unavailable',
                'missingSource': 'The application request has no governed prior-period window or comparison fact.',
            },
            'validationMethod': 'financial.validate',
        },
        {
            'key': 'gross_margin', 'label': 'Gross margin',
            'basisLabel': 'Sale-line margin basis: confirmed sale/done wholesale lines with GIFTWARE DEALS excluded',
            'actual': {
                'state': 'unavailable',
                'missingSource': 'No validated gross-margin fact is exposed by the canonical Finance spine.',
            },
            'budget': {
                'state': 'unavailable',
                'missingSource': 'No approved gross-margin budget series is bound to the canonical Finance mirror.',
            },
            'prior': {
                'state': 'unavailable',
                'missingSource': 'The application request has no governed prior-period gross-margin window.',
            },
            'validationMethod': 'financial.validate',
        },
        {
            'key': 'opex', 'label': 'Operating expense',
            'basisLabel': 'Posted GL basis: HQ consolidated operating-expense accounts',
            'actual': {'state': 'fact', 'factKey': 'opex'},
            'budget': {
                'state': 'unavailable',
                'missingSource': 'No approved GL budget series is bound to the canonical Finance mirror.',
            },
            'prior': {
                'state': 'unavailable',
                'missingSource': 'The application request has no governed prior-period GL window.',
            },
            'validationMethod': 'management.validate',
        },
        {
            'key': 'commission_accrued', 'label': 'Commission accrued',
            'basisLabel': 'Posted customer-invoice commission-line basis, scoped to the selected Business Unit',
            'actual': {'state': 'fact', 'factKey': 'commission_accrued'},
            'budget': {
                'state': 'unavailable',
                'missingSource': 'No approved commission budget series is bound to the canonical Finance mirror.',
            },
            'prior': {
                'state': 'unavailable',
                'missingSource': 'The application request has no governed prior-period commission window.',
            },
            'validationMethod': 'commission_accrued',
        },
    ],
    'notice': 'Actual rows retain their source basis and are not summed into a synthetic P&L.',
}


def comparison_dashboard_contract():
    """Return the mirror-safe actual/budget/prior projection without loading a statement."""
    return deepcopy(_MANAGEMENT_COMPARISON_DASHBOARD)


def months_available():
    """Months we can reproduce -M for = the months present in the extracted overlay
    (Sep-2024 .. last reconciled month in the model). Ascending list of (year, month)."""
    out = []
    for ym in sorted(MP._overlay().keys()):
        y, m = ym.split('-')
        out.append((int(y), int(m)))
    return out


def summary_kpis(year, month, entity):
    s = MP.statement(year, month, entity)
    rev = s['income']
    return {'income': rev, 'gross_profit': s['gross_profit'], 'net_profit': s['net_profit'],
            'gross_margin': (s['gross_profit'] / rev * 100) if rev else 0.0,
            'net_margin': (s['net_profit'] / rev * 100) if rev else 0.0}


def statement(year, month, entity):
    """Section subtotals (income / cogs / gross / opex / deprec / net) for one entity-month."""
    return MP.statement(year, month, entity)


def line_rows(year, month, entity):
    """Account-level rows for the statement table: {section,code,account,amount}, P&L-ordered."""
    s = MP.statement(year, month, entity)
    name_by_code = {a['code']: a['name'] for a in MP.account_master().values()}
    rows = [{'section': _SECTION_LABEL.get(MP._bucket(code) or '', ''),
             'code': code, 'account': name_by_code.get(code, ''), 'amount': round(amt, 2)}
            for code, amt in s['lines'].items()]
    order = {lbl: i for i, lbl in enumerate(_SECTION_LABEL[k] for k in _SECTION_ORDER)}
    rows.sort(key=lambda r: (order.get(r['section'], 9), str(r['code'])))
    return rows


def _exclusions():
    """{entity: [{code,name,flag}]} — GL codes excluded from each entity's analytics (from the
    model's -M tabs, via build_mgmt_overlay.py). flag=True = a real misclassification to flag."""
    try:
        with open(_EXCL_PATH, encoding='utf-8') as f:
            return json.load(f)
    except (FileNotFoundError, json.JSONDecodeError):
        return {}


def _odoo_base():
    return os.environ.get('ODOO_URL', '').rstrip('/')


def _move_links(gid, ana, start, end):
    """Deep-links to the Odoo journal entries behind a misclassified balance, so the bookkeeper can
    open each and re-tag its analytic. Read-only: these are just URLs into Odoo's own UI."""
    base = _odoo_base()
    if not base:
        return []
    lines = O.search_read('account.analytic.line',
                          [('date', '>=', start), ('date', '<=', end),
                           ('general_account_id', '=', gid), ('account_id', '=', ana)], ['move_line_id'])
    mlids = [l['move_line_id'][0] for l in lines if l.get('move_line_id')]
    moves = []
    if mlids:
        mls = O.search_read('account.move.line', [('id', 'in', mlids)], ['move_id'])
        moves = sorted({ml['move_id'][0] for ml in mls if ml.get('move_id')})
    return [f"{base}/web#id={mid}&model=account.move&view_type=form" for mid in moves]


def classification_flags(year=None, month=None):
    """Health check: any *flaggable* excluded GL code with a nonzero balance in its entity's analytic
    is a misclassification (e.g. an Amazon cost booked to Fisch, or a mortgage to a BU instead of HQ).
    EBMS / inventory adjustments are excluded by design and never flagged. Each flag carries the
    offending amount + click-to-fix Odoo journal-entry links. Defaults to the latest month."""
    if year is None or month is None:
        av = months_available()
        if not av:
            return []
        year, month = av[-1]
    excl = _exclusions()
    start, end = MP.month_bounds(year, month)
    code_id = {a['code']: gid for gid, a in MP.account_master().items()}
    flags = []
    for entity, items in excl.items():
        ana = MP.ENTITY_ANALYTIC.get(entity)
        if ana is None:
            continue
        for it in items:
            if not it.get('flag'):
                continue
            gid = code_id.get(it['code'])
            if not gid:
                continue
            bal = O.sum_field('account.analytic.line',
                              [('date', '>=', start), ('date', '<=', end),
                               ('general_account_id', '=', gid), ('account_id', '=', ana)], 'amount')
            if abs(bal) > 0.005:
                flags.append({'entity': entity, 'entity_label': ENTITY_LABEL.get(entity, entity),
                              'code': it['code'], 'account': it['name'], 'amount': round(bal, 2),
                              'moves': _move_links(gid, ana, start, end)})
    return flags


def _recent_months(n, anchor=None):
    """The last `n` (year, month) tuples ending at `anchor` (default = latest reproducible month,
    else the current month)."""
    import datetime
    if anchor is None:
        av = months_available()
        anchor = av[-1] if av else (datetime.date.today().year, datetime.date.today().month)
    y, m = anchor
    out = []
    for _ in range(n):
        out.append((y, m)); m -= 1
        if m == 0:
            y, m = y - 1, 12
    return list(reversed(out))


def account_detail(code, months=13):
    """Canonical financial-account view (consolidated, brand-independent): one GL account's monthly
    trend split by business unit (clean analytic basis, management display sign) + recent posted
    journal lines + an Odoo deep-link. Same data wherever the account is opened from. None if unknown."""
    am = MP.account_master()
    gid = next((i for i, a in am.items() if a['code'] == code), None)
    if gid is None:
        return None
    acc = am[gid]
    section = _SECTION_LABEL.get(MP._bucket(code) or '', '') or (acc.get('type') or '')
    yms = _recent_months(months)
    ent_keys = [('FFS', 'Fisch'), ('RI', 'Royal'), ('GD', 'GD'), ('HQ', 'HQ')]
    trend = []
    for (y, m) in yms:
        ce = MP.clean_entities(y, m)
        row = {'month': f'{y}-{m:02d}'}
        tot = 0.0
        for k, _lbl in ent_keys:
            v = ce.get(k, {}).get(code, 0.0)
            row[k] = round(v, 2); tot += v
        tot += ce.get('Internal', {}).get(code, 0.0)
        row['total'] = round(tot, 2)
        trend.append(row)

    base = _odoo_base()
    jl = O.search_read('account.move.line', [('account_id', '=', gid), ('parent_state', '=', 'posted')],
                       ['date', 'move_id', 'partner_id', 'name', 'balance'], order='date desc', limit=30)
    lines = []
    for l in jl:
        mid = l['move_id'][0] if l.get('move_id') else None
        lines.append({'date': l.get('date') or '', 'entry': O.m2o_name(l.get('move_id')),
                      'partner': O.m2o_name(l.get('partner_id')) or '', 'label': l.get('name') or '',
                      'amount': round(l.get('balance') or 0.0, 2),
                      'link': (f"{base}/web#id={mid}&model=account.move&view_type=form" if base and mid else None)})
    return {'code': code, 'name': acc.get('name', ''), 'type': acc.get('type'), 'section': section,
            'window': f"{yms[0][0]}-{yms[0][1]:02d} -> {yms[-1][0]}-{yms[-1][1]:02d}",
            'window_total': round(sum(r['total'] for r in trend), 2),
            'odoo_link': (f"{base}/web#id={gid}&model=account.account&view_type=form" if base else None),
            'trend': trend, 'lines': lines}


def validate(year=None, month=None):
    """Reconcile the reproduced consolidated -M to INDEPENDENT Odoo aggregates (works on the Space,
    no workbook): each headline = a separate read_group sum straight from Odoo. Defaults to the
    latest reproducible month. Returns [{check,a,b,gap,ok}] per the platform module contract."""
    if year is None or month is None:
        avail = months_available()
        if not avail:
            return [{'check': 'overlay present (data/mgmt_overlay.json)', 'a': 0, 'b': 1, 'gap': -1, 'ok': False}]
        year, month = avail[-1]
    checks = []
    start, end = MP.month_bounds(year, month)
    clean = MP.clean_entities(year, month)
    am = MP.account_master()
    code_id = {a['code']: gid for gid, a in am.items()}
    bu_ana = [MP.ENTITY_ANALYTIC[b] for b in MP.BUS]

    for code, label in (('51000', 'Product Sales'), ('600000', 'Product Cost')):
        gid = code_id.get(code)
        indep = 0.0
        if gid:
            g = O.read_group('account.analytic.line',
                             domain=[('date', '>=', start), ('date', '<=', end),
                                     ('general_account_id', '=', gid), ('account_id', 'in', bu_ana)],
                             fields=['amount:sum'], groupby=[], lazy=False)
            indep = abs((g[0].get('amount') or 0.0) if g else 0.0)  # display positive
        repro = sum(clean.get(b, {}).get(code, 0.0) for b in MP.BUS)
        gap = round(repro - indep, 2)
        checks.append({'check': f'{code} {label}: consolidated clean vs direct Odoo sum',
                       'a': round(repro, 2), 'b': round(indep, 2), 'gap': gap, 'ok': abs(gap) < 0.01})

    # consolidation consistency: BU net == FFS + RI + GD net
    bu_net = MP.statement(year, month, 'BU')['net_profit']
    parts = sum(MP.statement(year, month, b)['net_profit'] for b in MP.BUS)
    gap = round(bu_net - parts, 2)
    checks.append({'check': 'BU-M net profit == FFS-M + RI-M + GD-M',
                   'a': round(bu_net, 2), 'b': round(parts, 2), 'gap': gap, 'ok': abs(gap) < 0.01})

    # classification health: no excluded GL code should post to the wrong entity's analytic
    nflag = len(classification_flags(year, month))
    checks.append({'check': 'No GL misclassifications (excluded codes in wrong business unit)',
                   'a': nflag, 'b': 0, 'gap': nflag, 'ok': nflag == 0})
    return checks