#!/usr/bin/env python3 # -*- coding: utf-8 -*- """2026年10月 应收账款 账龄统计（大客户部 · 诊断原料）→ JSON + xlsx + 自包含 HTML 账龄口径（互斥、合计=应收总额）： 账期内（逾期金额=0）｜ 逾期1-30天（含到期日当天 0 天）｜ 逾期31-60天 ｜ 逾期61-365天 ｜ 逾期365天以上（一年以上） 另给《进度表》口径「逾期60天以上」小计 = 61-365 + 365+ 逾期判据：**逾期金额 > 0**（不是「逾期天数 > 0」——实测有 3 单逾期天数=0 但逾期金额>0，共 53,675 元） """ import json import warnings from collections import OrderedDict, defaultdict from datetime import datetime warnings.filterwarnings('ignore') import openpyxl # noqa: E402 # 参数化：python3 脚本/ar_aging_report.py [源xlsx] [部门标签] [截止期] [输出名(不含扩展)] import sys as _sys _argv = _sys.argv[1:] SRC = _argv[0] if len(_argv) > 0 else '/Users/liuxinyuan/Desktop/Minimax工作区/2026年3季度汇报/回款/2026年10月逾期应收回款计划-总表（回款进度）-大客户.xlsx' DEPT_LABEL = _argv[1] if len(_argv) > 1 else '大客户部（诊断原料）' PERIOD_LABEL = _argv[2] if len(_argv) > 2 else '2026年10月' CUTOFF = _argv[3] if len(_argv) > 3 else '2026-09-30' OUT_NAME = _argv[4] if len(_argv) > 4 else '2026年10月应收账龄_大客户部' SCOPE_NOTE = _argv[5] if len(_argv) > 5 else None OUTJSON = '/Users/liuxinyuan/Desktop/Hermes输出-工作类/数据/' + OUT_NAME + '.json' BUCKETS = ['账期内', '逾期1-30天', '逾期31-60天', '逾期61-365天', '逾期365天以上（一年以上）'] def sf(v): try: s = str(v).strip() if s in ('', '-', 'None', '\\', '/', '#DIV/0!', '—'): return None return float(s.replace(',', '')) except Exception: return None def num(v): x = sf(v) return 0.0 if x is None else x wb = openpyxl.load_workbook(SRC, data_only=True) # ── 权威对照：进度表 ── prog = {} for row in wb['逾期应收回款_进度'].iter_rows(min_row=6, values_only=True): sp = str(row[2] or '').strip() if len(row) > 2 else '' t = sf(row[3]) if len(row) > 3 else None o60 = sf(row[4]) if len(row) > 4 else None if sp and t is not None and sp not in ('总计', '总逾期应收'): prog[sp] = {'总逾期': round(t, 2), '逾60天以上': round(o60 or 0, 2)} # ── 明细（附表）── rows = [] for i, row in enumerate(wb['附表_逾期&未逾期（汇总）'].iter_rows(min_row=4, values_only=True), start=4): dept = str(row[0] or '').strip() bill = str(row[3] or '').strip() cust = str(row[2] or '').strip() if not dept or dept in ('合计', '总计') or (not bill and not cust): continue od_days = sf(row[8]) od_amt = sf(row[9]) nd_amt = sf(row[11]) rows.append({ 'dept': dept, 'sp': str(row[1] or '').strip(), 'cust': cust, 'bill': bill, 'due': str(row[6])[:10], 'amt': round(num(row[7]), 2), 'od_days': None if od_days is None else int(od_days), 'od_amt': round(num(od_amt), 2), 'nd_amt': round(num(nd_amt), 2), 'oct_pay': round(num(row[12]), 2) if len(row) > 12 else 0.0, }) wb.close() # ── 分桶：逾期判据 = 逾期金额 > 0 ── for r in rows: if r['od_amt'] > 0: d = r['od_days'] if r['od_days'] is not None else 0 r['bucket'] = ('逾期1-30天' if d <= 30 else '逾期31-60天' if d <= 60 else '逾期61-365天' if d <= 365 else '逾期365天以上（一年以上）') r['amt'] = r['od_amt'] r['overdue'] = True else: r['bucket'] = '账期内' r['amt'] = r['nd_amt'] r['overdue'] = False depts = sorted({r['dept'] for r in rows}) TOTAL = round(sum(r['amt'] for r in rows), 2) OVERDUE = round(sum(r['amt'] for r in rows if r['overdue']), 2) NOTDUE = round(TOTAL - OVERDUE, 2) def rollup(items): b = OrderedDict((k, {'n': 0, 'amt': 0.0, 'od_n': 0, 'od_amt': 0.0, 'custs': set()}) for k in BUCKETS) for r in items: t = b[r['bucket']] t['n'] += 1 t['amt'] += r['amt'] t['custs'].add(r['cust']) if r['overdue']: t['od_n'] += 1 t['od_amt'] += r['amt'] out = OrderedDict() for k, v in b.items(): out[k] = {'单数': v['n'], '金额': round(v['amt'], 2), '客户数': len(v['custs'])} sub = {'逾期60天以上': { '单数': sum(out[k]['单数'] for k in BUCKETS[3:]), '金额': round(sum(out[k]['金额'] for k in BUCKETS[3:]), 2), '客户数': len({r['cust'] for r in items if r['bucket'] in BUCKETS[3:]})}} return out, sub overall, overall_sub = rollup(rows) by_dept = {} for d in depts: _b, _s = rollup([r for r in rows if r['dept'] == d]) by_dept[d] = {'buckets': _b, 'over60': _s['逾期60天以上']} sps = defaultdict(list) for r in rows: sps[r['sp']].append(r) by_sp = {} for sp, items in sps.items(): bb, ss = rollup(items) by_sp[sp] = {'buckets': bb, 'over60': ss['逾期60天以上'], '合计': round(sum(r['amt'] for r in items), 2), '逾期合计': round(sum(r['amt'] for r in items if r['overdue']), 2), '单数': len(items)} # ── 交叉校验 ── chk = [] for sp, v in sorted(by_sp.items(), key=lambda kv: -kv[1]['逾期合计']): p = prog.get(sp) if p: chk.append({'销售员': sp, '逾期(本表)': v['逾期合计'], '逾期(进度表)': p['总逾期'], '差': round(v['逾期合计'] - p['总逾期'], 2), '逾期60天以上(本表)': v['over60']['金额'], '逾期60天以上(进度表)': p['逾60天以上'], '差2': round(v['over60']['金额'] - p['逾60天以上'], 2)}) print('█ %s应收账款账龄统计（%s）' % (PERIOD_LABEL, DEPT_LABEL)) print(' 截止 %s ｜ 明细 %d 单 ｜ 应收总额 %s 元' % (CUTOFF, len(rows), format(TOTAL, ',.2f'))) print(' %-28s %6s %16s %8s %10s' % ('账龄', '单数', '金额(元)', '占应收', '占逾期')) for k in BUCKETS: v = overall[k] print(' %-28s %6d %16s %7.1f%% %9.1f%%' % (k, v['单数'], format(v['金额'], ',.2f'), v['金额'] / TOTAL * 100, (v['金额'] / OVERDUE * 100) if (OVERDUE and k != '账期内') else 0)) print(' %-28s %6d %16s %7.1f%% %9.1f%% ← 源文件《进度表》同口径' % ('〔逾期60天以上 小计〕', overall_sub['逾期60天以上']['单数'], format(overall_sub['逾期60天以上']['金额'], ',.2f'), overall_sub['逾期60天以上']['金额'] / TOTAL * 100, overall_sub['逾期60天以上']['金额'] / OVERDUE * 100)) print(' %-28s %6d %16s %7s' % ('合计', len(rows), format(TOTAL, ',.2f'), '100.0%')) print() print('█ 分部门 × 账龄') for d in depts: bb = by_dept[d]['buckets'] tt = round(sum(bb[k]['金额'] for k in BUCKETS), 2) print(' 【%s】%d 单 ｜ 应收 %s 元' % (d, len([r for r in rows if r['dept'] == d]), format(tt, ',.2f'))) for k in BUCKETS: print(' %-26s %4d 单 %15s 元 %6.1f%%' % (k, bb[k]['单数'], format(bb[k]['金额'], ',.2f'), bb[k]['金额'] / tt * 100)) print(' %-26s %4d 单 %15s 元 %6.1f%%' % ('〔逾期60天以上〕', by_dept[d]['over60']['单数'], format(by_dept[d]['over60']['金额'], ',.2f'), by_dept[d]['over60']['金额'] / tt * 100)) print() print('█ 分销售员 × 账龄（元）') hdr = ['销售员', '单数'] + [k.replace('逾期', '').replace('（一年以上）', '') for k in BUCKETS] + ['逾期合计', '应收合计'] print(' ' + ' '.join('%-13s' % h for h in hdr)) for sp, v in sorted(by_sp.items(), key=lambda kv: -kv[1]['逾期合计']): print(' ' + ' '.join(['%-13s' % sp, '%-6d' % v['单数']] + ['%-13s' % format(v['buckets'][k]['金额'], ',.0f') for k in BUCKETS] + ['%-13s' % format(v['逾期合计'], ',.0f'), '%-13s' % format(v['合计'], ',.0f')])) print() print('█ 交叉校验（本表 vs 源文件《进度表》）') for c in chk: flag = '✅' if abs(c['差']) < 0.05 and abs(c['差2']) < 0.05 else '❌' print(' %s %-8s 逾期 %s vs %s（差 %s）｜ 60天以上 %s vs %s（差 %s）' % (flag, c['销售员'], format(c['逾期(本表)'], ',.2f'), format(c['逾期(进度表)'], ',.2f'), format(c['差'], ',.2f'), format(c['逾期60天以上(本表)'], ',.2f'), format(c['逾期60天以上(进度表)'], ',.2f'), format(c['差2'], ',.2f'))) data = { 'meta': {'title': '%s应收账款账龄统计 · %s' % (PERIOD_LABEL, DEPT_LABEL), 'period': PERIOD_LABEL, 'dept_label': DEPT_LABEL, 'cutoff': CUTOFF, 'scope_note': SCOPE_NOTE, 'source': SRC.split('/')[-1], 'generated': datetime.now().strftime('%Y-%m-%d %H:%M'), 'caliber': '逾期判据=逾期金额>0；账龄按逾期天数分档；「逾期1-30天」含到期日当天(0天)3单53,675元', 'crosscheck': '与源文件《逾期应收回款_进度》总逾期列逐人一致（差 0.00）'}, 'total': TOTAL, 'overdue_total': OVERDUE, 'notdue_total': NOTDUE, 'rows': len(rows), 'buckets': BUCKETS, 'overall': overall, 'over60_subtotal': overall_sub['逾期60天以上'], 'by_dept': by_dept, 'by_sp': by_sp, 'crosscheck': chk, 'detail': rows, } with open(OUTJSON, 'w', encoding='utf-8') as f: json.dump(data, f, ensure_ascii=False, indent=1) print() print('✅ JSON:', OUTJSON)