# -*- coding: utf-8 -*-
"""货运月报「持续在规企业」分市州 1-8 月累计运距及同比 台账生成（v2：同比基期改为官方全口径）

数据源：traffic_audit.h2032_enterprise_monthly（《道路货物运输月度生产情况》H203-2 企业月报）
        导出件 _tmp_persist_scope.tsv（企业级累计）、_tmp_persist_scope_monthly.tsv（企业级分月）
口径（v2 修订）：
  · 持续在规企业 = 2026 年 1-8 月连续 8 个月均上报月报的企业（636 家，用户 2026-09-18 确认）；
  · 累计平均运距（公里）= 累计货物周转量 ÷ 累计货运量（加权平均）；
  · 同比 = 2026 年 1-8 月累计值 ÷ 2025 年 1-8 月累计值 − 1，**基期为 2025 年 1-8 月全部规上企业上报数**
    （全省 664 家），与汇总表「货运」页 T 列 `=S/SUM(V7,X7,…,AJ7)-1` 同一口径；
    v1 误用「两年均持续在规的 545 家」作基期，导致同比偏小，已修正。
"""
import io, os
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

SRC = r'D:\文档\ChatGPT\traffic-audit\_tmp_persist_scope.tsv'
MONTHLY_SRC = r'D:\文档\ChatGPT\traffic-audit\_tmp_persist_scope_monthly.tsv'
OUT_DIR = r'D:\文档\ChatGPT\traffic-audit\outputs'
OUT = os.path.join(OUT_DIR, '货运月报_持续在规企业_分市州1-8月累计运距及同比_2026-09-18.xlsx')

PROV = {
    'vol26': 31399.82, 'turn26': 4927161.77,       # 已发布：2026 年 1-8 月累计
    'vol25': 33994.45, 'turn25': 5102518.95,       # 汇总表「货运」页 2025 年 1-8 月（同比基期）
    'vol26_08': 3665.36, 'turn26_08': 570004.44,   # 已发布：2026 年 8 月当月
    'yoy_vol': -0.0763, 'yoy_turn': -0.0344,       # 已发布：1-8 月累计同比
}

CITY_ORDER = ['武汉市', '黄石市', '十堰市', '宜昌市', '襄阳市', '鄂州市', '荆门市', '孝感市', '荆州市',
              '黄冈市', '咸宁市', '随州市', '恩施州', '仙桃市', '潜江市', '天门市', '神农架林区']
CITY = {'4201': '武汉市', '4202': '黄石市', '4203': '十堰市', '4205': '宜昌市', '4206': '襄阳市',
        '4207': '鄂州市', '4208': '荆门市', '4209': '孝感市', '4210': '荆州市', '4211': '黄冈市',
        '4212': '咸宁市', '4213': '随州市', '4228': '恩施州',
        '429004': '仙桃市', '429005': '潜江市', '429006': '天门市', '429021': '神农架林区'}
MONTHS26 = ['2026-%02d' % m for m in range(1, 9)]


def city_of(rc):
    rc = (rc or '').strip()
    return CITY[rc] if rc in CITY else CITY.get(rc[:4], '未识别(%s)' % rc)


def num(x):
    try:
        return float(x)
    except Exception:
        return 0.0


def dist(f, t):
    return (t / f) if f else None


def growth(now, base):
    return (now / base - 1) if (now is not None and base) else None


# ---------- 读企业级累计数据 ----------
rows = []
with io.open(SRC, 'r', encoding='utf-8') as f:
    hdr = f.readline().rstrip('\r\n').split('\t')
    for line in f:
        p = line.rstrip('\r\n').split('\t')
        if len(p) < len(hdr):
            continue
        d = dict(zip(hdr, p))
        if int(num(d['m26'])) != 8:
            continue
        rows.append(dict(city=city_of(d['rc']), rc=d['rc'], en=d['en'], ec=d['ec'], ucc=d['ucc'],
                         f26=num(d['f26']), t26=num(d['t26']),
                         f25=num(d['f25']), t25=num(d['t25']), cmp=(int(num(d['m25'])) == 8)))

by_city = {}
for r in rows:
    c = by_city.setdefault(r['city'], dict(n=0, f26=0.0, t26=0.0))
    c['n'] += 1
    c['f26'] += r['f26']; c['t26'] += r['t26']

tot = dict(n=0, f26=0.0, t26=0.0)
for c in by_city.values():
    for k in tot:
        tot[k] += c[k]
by_city_cmp = {}
for r in rows:
    if not r['cmp']:
        continue
    c = by_city_cmp.setdefault(r['city'], dict(n=0, f26c=0.0, t26c=0.0, f25c=0.0, t25c=0.0))
    c['n'] += 1
    c['f26c'] += r['f26']; c['t26c'] += r['t26']
    c['f25c'] += r['f25']; c['t25c'] += r['t25']
tot_cmp = dict(n=0, f26c=0.0, t26c=0.0, f25c=0.0, t25c=0.0)
for c in by_city_cmp.values():
    for k in tot_cmp:
        tot_cmp[k] += c[k]
unknown = sorted(k for k in by_city if k.startswith('未识别'))
persist_names = set(r['en'] for r in rows)

# ---------- 读企业级分月数据 ----------
cust = {}
with io.open(MONTHLY_SRC, 'r', encoding='utf-8') as f:
    for line in f:
        p = line.rstrip('\r\n').split('\t')
        if len(p) < 5 or p[0] == 'report_period':
            continue
        per, rc, en, fr, tu = p[0], p[1], p[2], p[3], p[4]
        cust.setdefault((per, city_of(rc)), {})[en] = (num(fr), num(tu))


def city_month(period, city, names=None):
    d = cust.get((period, city))
    if not d:
        return None, 0.0, 0.0
    fr = tu = 0.0
    for en, v in d.items():
        if names is None or en in names:
            fr += v[0]; tu += v[1]
    return dist(fr, tu), fr, tu


# ---------- 2025 年 1-8 月全口径基期 ----------
B25 = {}
for (per, city), d in cust.items():
    if not ('2025-01' <= per <= '2025-08'):
        continue
    c = B25.setdefault(city, dict(ents=set(), f=0.0, t=0.0))
    for en, v in d.items():
        c['ents'].add(en)
        c['f'] += v[0]; c['t'] += v[1]
tot25 = dict(ents=set(), f=0.0, t=0.0)
for c in B25.values():
    tot25['ents'] |= c['ents']
    tot25['f'] += c['f']; tot25['t'] += c['t']

# ---------- 样式 ----------
THIN = Side(style='thin', color='BFBFBF')
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
HDR_FILL = PatternFill('solid', fgColor='DDEBF7')
TOT_FILL = PatternFill('solid', fgColor='FFF2CC')
TITLE_FILL = PatternFill('solid', fgColor='1F4E79')
CT = Alignment(horizontal='center', vertical='center', wrap_text=True)
LT = Alignment(horizontal='left', vertical='center', wrap_text=True)
RT = Alignment(horizontal='right', vertical='center')
NF2, NF1, NFP = '#,##0.00', '#,##0.0', '0.00%'

wb = Workbook()

# ---------------- Sheet1 说明与口径 ----------------
ws = wb.active
ws.title = '说明与口径'
ws.sheet_view.showGridLines = False
ws.column_dimensions['A'].width = 16
ws.column_dimensions['B'].width = 106
t = ws.cell(1, 1, '货运月报「持续在规企业」分市州 1-8 月累计运距及同比情况表')
t.font = Font(bold=True, size=15, color='FFFFFF')
t.fill = TITLE_FILL
t.alignment = Alignment(horizontal='center', vertical='center')
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=2)
ws.row_dimensions[1].height = 30

info = [
    ('数据来源', '系统数据库 traffic_audit.h2032_enterprise_monthly《道路货物运输月度生产情况》（H203-2）企业月报，'
                 '取 2025 年 1 月—2026 年 8 月全部企业级上报记录。'),
    ('持续在规企业', '2026 年 1—8 月连续 8 个月均上报月报的企业，共 %d 家（2026 年各月上报企业数均为 636 家，全期一致；'
                 '口径经用户 2026-09-18 确认）。' % tot['n']),
    ('累计运距口径', '累计平均运距（公里）= 1—8 月累计货物周转量 ÷ 1—8 月累计货运量（加权平均）。源表单位为吨、吨公里，'
                     '本表折算为万吨、万吨公里，比值不变。'),
    ('同比口径', '同比 = 2026 年 1—8 月累计值 ÷ 2025 年 1—8 月累计值 − 1。**基期为 2025 年 1—8 月全部规上企业上报数**'
                 '（全省 %d 家），与汇总表「货运」页 T 列公式 =S/SUM(V7,X7,…,AJ7)-1 同一口径，可与发布报告直接对齐：'
                 '全省累计货运量同比 −7.63%%、周转量同比 −3.44%%、平均运距同比 +4.54%%。' % len(tot25['ents'])),
    ('全省合计', '一律为 17 个市州之和（不含跨市州重叠口径）。'),
    ('报表期', '本期累计 2026-01-01—2026-08-31；同比基期 2025-01-01—2025-08-31。'),
    ('页签说明', '「分市州汇总」按市州汇总 1-8 月累计运距与同比；「分市州分月」为各市州 2026 年逐月平均运距；'
                 '「企业明细」为 636 家持续在规企业逐户数据（企业级同比仅对两年均连续上报的 545 家给出）；'
                 '「口径对比」并列官方口径与同企业口径的同比；「口径校验」为明细与汇总对账，以及与汇总表、已发布报告的比对。'),
    ('注意事项', '① 有 5 家企业 1—8 月月报货运量、周转量均为 0，计入企业数但不影响运距；'
                 '② 武汉石化交通运输有限公司、湖北捷龙交通运业有限公司 2026 年 1 月的地区码与其后月份不同（420100 → 420107 / 420103），'
                 '但同属武汉市，不影响市州归集；③ 累计货运量为 0 的企业累计运距留空。'),
    ('版本', 'v2（2026-09-18）：修正同比基期口径——v1 误用「两年均持续在规的 545 家」作基期，全省累计运距同比算得 +2.90%%，'
             '官方口径应为 +4.54%%。'),
    ('生成时间', '2026-09-18'),
]
r = 3
for k, v in info:
    a = ws.cell(r, 1, k); a.font = Font(bold=True); a.alignment = LT; a.border = BORDER
    b = ws.cell(r, 2, v); b.alignment = LT; b.border = BORDER
    r += 1

# ---------------- Sheet2 分市州汇总 ----------------
ws2 = wb.create_sheet('分市州汇总')
heads = ['序号', '市州', '2026年1-8月持续在规企业数(家)', '2025年1-8月规上企业数(家)',
         '2026年1-8月累计货运量(万吨)', '2026年1-8月累计货物周转量(万吨公里)', '2026年1-8月累计平均运距(公里)',
         '2025年1-8月累计货运量(万吨)', '2025年1-8月累计货物周转量(万吨公里)', '2025年1-8月累计平均运距(公里)',
         '累计平均运距同比(%)', '累计货运量同比(%)', '累计货物周转量同比(%)']
widths = [6, 14, 20, 18, 20, 24, 20, 20, 24, 20, 15, 15, 17]
ws2.cell(1, 1, '货运月报持续在规企业分市州 1-8 月累计平均运距及同比情况（同比基期：2025 年 1-8 月全部规上企业）').font = Font(bold=True, size=13, color='FFFFFF')
ws2.cell(1, 1).fill = TITLE_FILL
ws2.cell(1, 1).alignment = Alignment(horizontal='center', vertical='center')
ws2.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(heads))
ws2.row_dimensions[1].height = 26
ws2.cell(2, 1, '注：第 5—7 列为 2026 年持续在规企业（636 家）累计值；第 8—10 列为 2025 年 1-8 月全部规上企业（全省 664 家）累计值，'
               '即汇总表「货运」页 T 列同比公式的基期；第 11—13 列同比口径与发布报告一致。')
ws2.cell(2, 1).font = Font(size=9, italic=True, color='808080')
ws2.merge_cells(start_row=2, start_column=1, end_row=2, end_column=len(heads))
for j, h in enumerate(heads, 1):
    c = ws2.cell(3, j, h)
    c.font = Font(bold=True); c.fill = HDR_FILL; c.alignment = CT; c.border = BORDER
    ws2.column_dimensions[get_column_letter(j)].width = widths[j - 1]
ws2.row_dimensions[3].height = 46

rr = 3
idx = 0
for city in CITY_ORDER:
    c = by_city.get(city)
    if not c or c['n'] == 0:
        continue
    idx += 1
    rr += 1
    b = B25.get(city, dict(ents=set(), f=0.0, t=0.0))
    d26, d25 = dist(c['f26'], c['t26']), dist(b['f'], b['t'])
    vals = [idx, city, c['n'], len(b['ents']),
            c['f26'] / 10000.0, c['t26'] / 10000.0, d26,
            b['f'] / 10000.0, b['t'] / 10000.0, d25,
            growth(d26, d25), growth(c['f26'], b['f']), growth(c['t26'], b['t'])]
    for j, v in enumerate(vals, 1):
        cell = ws2.cell(rr, j, v)
        cell.border = BORDER
        cell.alignment = CT if j <= 4 else RT
        if j in (5, 6, 7, 8, 9, 10):
            cell.number_format = NF2
        elif j in (11, 12, 13):
            cell.number_format = NFP
rr += 1
d26p, d25p = dist(tot['f26'], tot['t26']), dist(tot25['f'], tot25['t'])
vals = ['', '全省合计', tot['n'], len(tot25['ents']),
        tot['f26'] / 10000.0, tot['t26'] / 10000.0, d26p,
        tot25['f'] / 10000.0, tot25['t'] / 10000.0, d25p,
        growth(d26p, d25p), growth(tot['f26'], tot25['f']), growth(tot['t26'], tot25['t'])]
for j, v in enumerate(vals, 1):
    cell = ws2.cell(rr, j, v)
    cell.border = BORDER
    cell.fill = TOT_FILL
    cell.font = Font(bold=True)
    cell.alignment = CT if j <= 4 else RT
    if j in (5, 6, 7, 8, 9, 10):
        cell.number_format = NF2
    elif j in (11, 12, 13):
        cell.number_format = NFP
ws2.freeze_panes = 'C4'

# ---------------- Sheet3 分市州分月 ----------------
ws3 = wb.create_sheet('分市州分月')
h3 = ['序号', '市州', '持续在规企业数(家)'] + ['%s月平均运距(公里)' % m for m in range(1, 9)] + \
     ['2026年1-8月累计平均运距(公里)', '2025年1-8月累计平均运距(公里)', '累计运距同比(%)']
w3 = [6, 14, 18] + [15] * 8 + [24, 24, 15]
ws3.cell(1, 1, '分市州 2026 年逐月平均运距（持续在规企业口径；同比基期：2025 年 1-8 月全部规上企业）').font = Font(bold=True, size=13, color='FFFFFF')
ws3.cell(1, 1).fill = TITLE_FILL
ws3.cell(1, 1).alignment = Alignment(horizontal='center', vertical='center')
ws3.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(h3))
ws3.row_dimensions[1].height = 26
for j, h in enumerate(h3, 1):
    c = ws3.cell(2, j, h)
    c.font = Font(bold=True); c.fill = HDR_FILL; c.alignment = CT; c.border = BORDER
    ws3.column_dimensions[get_column_letter(j)].width = w3[j - 1]
ws3.row_dimensions[2].height = 40

rr = 2
idx = 0
for city in CITY_ORDER:
    c = by_city.get(city)
    if not c or c['n'] == 0:
        continue
    idx += 1
    rr += 1
    b = B25.get(city, dict(ents=set(), f=0.0, t=0.0))
    d26, d25 = dist(c['f26'], c['t26']), dist(b['f'], b['t'])
    vals = [idx, city, c['n']] + [city_month(p, city, persist_names)[0] for p in MONTHS26] + \
           [d26, d25, growth(d26, d25)]
    for j, v in enumerate(vals, 1):
        cell = ws3.cell(rr, j, v)
        cell.border = BORDER
        cell.alignment = CT if j <= 3 else RT
        if j >= 4:
            cell.number_format = NFP if j == 14 else NF2
rr += 1
vals = ['', '全省合计', tot['n']] + [city_month(p, None)[0] for p in MONTHS26] + \
       [d26p, d25p, growth(d26p, d25p)]
for j, v in enumerate(vals, 1):
    cell = ws3.cell(rr, j, v)
    cell.border = BORDER
    cell.fill = TOT_FILL
    cell.font = Font(bold=True)
    cell.alignment = CT if j <= 3 else RT
    if j >= 4:
        cell.number_format = NFP if j == 14 else NF2
ws3.freeze_panes = 'D3'

# ---------------- Sheet4 企业明细 ----------------
ws4 = wb.create_sheet('企业明细')
h4 = ['序号', '市州', '企业名称', '企业代码', '统一社会信用代码',
      '2026年1-8月货运量(万吨)', '2026年1-8月周转量(万吨公里)', '2026年1-8月累计平均运距(公里)',
      '2025年1-8月货运量(万吨)', '2025年1-8月周转量(万吨公里)', '2025年1-8月累计平均运距(公里)',
      '平均运距同比(%)', '是否两年均持续在规']
w4 = [6, 12, 42, 22, 24, 20, 24, 22, 20, 24, 22, 14, 18]
ws4.cell(1, 1, '持续在规企业逐户累计平均运距（2026 年 1-8 月；同期值与同比仅对两年均持续在规的 545 家给出）').font = Font(bold=True, size=13, color='FFFFFF')
ws4.cell(1, 1).fill = TITLE_FILL
ws4.cell(1, 1).alignment = Alignment(horizontal='center', vertical='center')
ws4.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(h4))
ws4.row_dimensions[1].height = 26
for j, h in enumerate(h4, 1):
    c = ws4.cell(2, j, h)
    c.font = Font(bold=True); c.fill = HDR_FILL; c.alignment = CT; c.border = BORDER
    ws4.column_dimensions[get_column_letter(j)].width = w4[j - 1]
ws4.row_dimensions[2].height = 40

order = {c: i for i, c in enumerate(CITY_ORDER)}
rows_sorted = sorted(rows, key=lambda x: (order.get(x['city'], 99), -(x['t26'])))
rr = 2
for i, x in enumerate(rows_sorted, 1):
    rr += 1
    d26, d25 = dist(x['f26'], x['t26']), dist(x['f25'], x['t25'])
    vals = [i, x['city'], x['en'], x['ec'], x['ucc'],
            x['f26'] / 10000.0, x['t26'] / 10000.0, d26,
            (x['f25'] / 10000.0) if x['cmp'] else None,
            (x['t25'] / 10000.0) if x['cmp'] else None,
            d25 if x['cmp'] else None,
            growth(d26, d25) if x['cmp'] else None,
            '是' if x['cmp'] else '否']
    for j, v in enumerate(vals, 1):
        cell = ws4.cell(rr, j, v)
        cell.border = BORDER
        cell.alignment = LT if j in (3, 4, 5) else (CT if j in (1, 2, 13) else RT)
        if j in (6, 7, 8, 9, 10, 11):
            cell.number_format = NF2
        elif j == 12:
            cell.number_format = NFP
        if j == 12 and isinstance(v, float) and v > 0.5:
            cell.font = Font(color='C00000', bold=True)
ws4.freeze_panes = 'D3'
ws4.auto_filter.ref = 'A2:M%d' % rr

# ---------------- Sheet5 口径对比 ----------------
ws6 = wb.create_sheet('口径对比')
h6 = ['序号', '市州', '2026年1-8月累计平均运距(公里)\n[持续在规企业636家]',
      '2025年1-8月累计平均运距(公里)\n[全部规上企业]', '官方口径同比(%)',
      '2025年1-8月累计平均运距(公里)\n[同企业545家]', '同企业口径同比(%)', '两者差异(百分点)', '说明']
w6 = [6, 14, 26, 24, 15, 24, 15, 16, 46]
ws6.cell(1, 1, '两种同比口径对比（官方口径 = 与汇总表「货运」页 T 列一致；同企业口径 = 仅两年均持续在规企业）').font = Font(bold=True, size=13, color='FFFFFF')
ws6.cell(1, 1).fill = TITLE_FILL
ws6.cell(1, 1).alignment = Alignment(horizontal='center', vertical='center')
ws6.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(h6))
ws6.row_dimensions[1].height = 26
ws6.cell(2, 1, '注：交付主表（分市州汇总）采用官方口径；本页两个口径并列，差异大的市州多为两年间规上企业名单变动（停产、新增、拆分）所致，供业务核对参考。')
ws6.cell(2, 1).font = Font(size=9, italic=True, color='808080')
ws6.merge_cells(start_row=2, start_column=1, end_row=2, end_column=len(h6))
for j, h in enumerate(h6, 1):
    c = ws6.cell(3, j, h)
    c.font = Font(bold=True); c.fill = HDR_FILL; c.alignment = CT; c.border = BORDER
    ws6.column_dimensions[get_column_letter(j)].width = w6[j - 1]
ws6.row_dimensions[3].height = 48

rr = 3
idx = 0
for city in CITY_ORDER:
    c = by_city.get(city)
    if not c or c['n'] == 0:
        continue
    idx += 1
    rr += 1
    b = B25.get(city, dict(ents=set(), f=0.0, t=0.0))
    g = by_city_cmp.get(city)
    d26, d25 = dist(c['f26'], c['t26']), dist(b['f'], b['t'])
    g_off = growth(d26, d25)
    if g:
        dc26, dc25 = dist(g['f26c'], g['t26c']), dist(g['f25c'], g['t25c'])
        g_same = growth(dc26, dc25)
    else:
        dc25, g_same = None, None
    diff = (g_off - g_same) if (g_off is not None and g_same is not None) else None
    note = ''
    if diff is not None and abs(diff) > 0.10:
        note = '两口径差异超 10 个百分点，主要因两年规上企业名单变动，建议核对该市州企业变动情况'
    vals = [idx, city, d26, d25, g_off, dc25, g_same, (diff * 100 if diff is not None else None), note]
    for j, v in enumerate(vals, 1):
        cell = ws6.cell(rr, j, v)
        cell.border = BORDER
        cell.alignment = LT if j in (1, 9) else (CT if j == 2 else RT)
        if j in (3, 4, 6):
            cell.number_format = NF2
        elif j in (5, 7):
            cell.number_format = NFP
        elif j == 8:
            cell.number_format = NF1
rr += 1
g_cmp = growth(dist(tot_cmp['f26c'], tot_cmp['t26c']), dist(tot_cmp['f25c'], tot_cmp['t25c']))
vals = ['', '全省合计', d26p, d25p, growth(d26p, d25p), dist(tot_cmp['f25c'], tot_cmp['t25c']), g_cmp,
        (growth(d26p, d25p) - g_cmp) * 100 if g_cmp is not None else None, '']
for j, v in enumerate(vals, 1):
    cell = ws6.cell(rr, j, v)
    cell.border = BORDER
    cell.fill = TOT_FILL
    cell.font = Font(bold=True)
    cell.alignment = CT if j == 2 else RT
    if j in (3, 4, 6):
        cell.number_format = NF2
    elif j in (5, 7):
        cell.number_format = NFP
    elif j == 8:
        cell.number_format = NF1
ws6.freeze_panes = 'C4'

# ---------------- Sheet5 口径校验 ----------------
ws5 = wb.create_sheet('口径校验')
ws5.sheet_view.showGridLines = False
ws5.column_dimensions['A'].width = 46
for col, w in (('B', 20), ('C', 20), ('D', 14), ('E', 46)):
    ws5.column_dimensions[col].width = w
ws5.cell(1, 1, '口径校验与对账').font = Font(bold=True, size=13, color='FFFFFF')
ws5.cell(1, 1).fill = TITLE_FILL
ws5.merge_cells(start_row=1, start_column=1, end_row=1, end_column=5)
sum_f = sum(r['f26'] for r in rows) / 10000.0
sum_t = sum(r['t26'] for r in rows) / 10000.0
aug_f = sum(v[0] for c in CITY_ORDER for en, v in cust.get(('2026-08', c), {}).items() if en in persist_names) / 10000.0
aug_t = sum(v[1] for c in CITY_ORDER for en, v in cust.get(('2026-08', c), {}).items() if en in persist_names) / 10000.0
chk = [
    ('校验项', '本表数值', '对照数值', '差异', '说明'),
    ('明细表企业数 = 汇总表企业数', len(rows), tot['n'], len(rows) - tot['n'], '636 家持续在规企业逐户一行'),
    ('明细表货运量合计(万吨)', round(sum_f, 2), round(tot['f26'] / 10000.0, 2), round(sum_f - tot['f26'] / 10000.0, 2), '明细求和 = 17 市州汇总合计'),
    ('明细表周转量合计(万吨公里)', round(sum_t, 2), round(tot['t26'] / 10000.0, 2), round(sum_t - tot['t26'] / 10000.0, 2), '明细求和 = 17 市州汇总合计'),
    ('2026年1-8月累计货运量(万吨)', round(tot['f26'] / 10000.0, 2), PROV['vol26'], round(tot['f26'] / 10000.0 - PROV['vol26'], 2), '对照已发布报告 1-8 月累计'),
    ('2026年1-8月累计周转量(万吨公里)', round(tot['t26'] / 10000.0, 2), PROV['turn26'], round(tot['t26'] / 10000.0 - PROV['turn26'], 2), '同上'),
    ('2026年8月全省货运量(万吨)', round(aug_f, 2), PROV['vol26_08'], round(aug_f - PROV['vol26_08'], 2), '验证市州归集无误'),
    ('2026年8月全省周转量(万吨公里)', round(aug_t, 2), PROV['turn26_08'], round(aug_t - PROV['turn26_08'], 2), '同上'),
    ('2025年1-8月累计货运量(万吨)【同比基期】', round(tot25['f'] / 10000.0, 2), PROV['vol25'], round(tot25['f'] / 10000.0 - PROV['vol25'], 2), '对照汇总表「货运」页 V7..AJ7（缓存值）'),
    ('2025年1-8月累计周转量(万吨公里)【同比基期】', round(tot25['t'] / 10000.0, 2), PROV['turn25'], round(tot25['t'] / 10000.0 - PROV['turn25'], 2), '对照汇总表「货运」页 V8..AJ8（缓存值）'),
    ('2025年1-8月全省规上企业数(家)', len(tot25['ents']), 664, len(tot25['ents']) - 664, '同比基期企业范围'),
    ('累计货运量同比', growth(tot['f26'], tot25['f']), PROV['yoy_vol'], round(growth(tot['f26'], tot25['f']) - PROV['yoy_vol'], 4), '对照已发布报告 1-8 月累计同比'),
    ('累计周转量同比', growth(tot['t26'], tot25['t']), PROV['yoy_turn'], round(growth(tot['t26'], tot25['t']) - PROV['yoy_turn'], 4), '同上'),
    ('2025年1-8月累计平均运距(公里)', round(d25p, 2), None, None, '= 2025 累计周转量 ÷ 累计货运量'),
    ('2026年1-8月累计平均运距(公里)', round(d26p, 2), None, None, '= 2026 累计周转量 ÷ 累计货运量'),
    ('全省累计平均运距同比', growth(d26p, d25p), None, None, '= 156.92 ÷ 150.10 − 1'),
    ('覆盖市州数', len([c for c in CITY_ORDER if by_city.get(c, {}).get('n')]), 17, None, '17 个市州全覆盖'),
    ('未识别地区码', len(unknown), 0, None, ('、'.join(unknown) if unknown else '无')),
]
for i, row in enumerate(chk, 1):
    for j, v in enumerate(row, 1):
        cell = ws5.cell(i + 2, j, v)
        cell.border = BORDER
        cell.alignment = CT if i == 1 else (LT if j in (1, 5) else RT)
        if i == 1:
            cell.font = Font(bold=True); cell.fill = HDR_FILL
        elif j in (2, 3, 4):
            cell.number_format = NFP if row[0].endswith('同比') else NF2

os.makedirs(OUT_DIR, exist_ok=True)
wb.save(OUT)
print('OK ->', OUT)
print('2026 1-8月: %.4f 万吨 / %.4f 万吨公里 / %.4f 公里' % (tot['f26'] / 10000.0, tot['t26'] / 10000.0, d26p))
print('2025 1-8月: %.4f 万吨 / %.4f 万吨公里 / %.4f 公里 (%d 家)' % (tot25['f'] / 10000.0, tot25['t'] / 10000.0, d25p, len(tot25['ents'])))
print('全省同比: 货运量 %.2f%%  周转量 %.2f%%  平均运距 %.2f%%' %
      (growth(tot['f26'], tot25['f']) * 100, growth(tot['t26'], tot25['t']) * 100, growth(d26p, d25p) * 100))
for city in CITY_ORDER:
    c = by_city.get(city)
    if c and c['n']:
        b = B25.get(city, dict(ents=set(), f=0.0, t=0.0))
        d1, d0 = dist(c['f26'], c['t26']), dist(b['f'], b['t'])
        print('%-8s n=%-4d 2026=%8.2f 2025=%8s 同比=%s' % (
            city, c['n'], d1, ('%.2f' % d0) if d0 else '-', ('%.1f%%' % (growth(d1, d0) * 100)) if d0 else '-'))