#!/usr/bin/env python3 # -*- coding: utf-8 -*- """2FM101-1 / 2FM101-2 套装销售额统计（2026 + 2025H2 参照）""" import datetime as dt import json import os from collections import defaultdict import openpyxl from openpyxl.styles import Alignment, Font, PatternFill from openpyxl.utils import get_column_letter P26 = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-20261020.xlsx' P25 = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/产品清单/产品物流客户价格大表-2025.6-2026.6.xlsx' OUT = os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '2FM101套装销售额统计_2026.xlsx') KITS = {'2FM101-1（50×Hyperstart Tth/UNG 套装）': ['2FM101-1-1', '2FM101-1-2', '2FM101-1-3'], '2FM101-2（50×Hyperstart Tth plus/UNG 套装）': ['2FM101-2-1', '2FM101-2-2', '2FM101-2-3']} CODES = [c for v in KITS.values() for c in v] NAMES = {'2FM101-1-1': '50×Hyperstart Tth/UNG酶', '2FM101-1-2': '8×Hyperstart Tth Buffer (Mn2+ free) (DG)', '2FM101-1-3': '1M MgCl2', '2FM101-2-1': '50×Hyperstart Tth plus/UNG酶', '2FM101-2-2': '8×Tth RT Buffer溶液 (with dNTPs) (Mn2+ free)', '2FM101-2-3': '200mM 醋酸锰'} def pdate(v): if isinstance(v, dt.datetime): return v.date() if isinstance(v, dt.date): return v s = str(v)[:19] for f in ('%Y-%m-%d %H:%M:%S', '%Y-%m-%d %H:%M', '%Y-%m-%d', '%Y/%m/%d'): try: return dt.datetime.strptime(s, f).date() except Exception: pass return None def load(path, include_test=False): wb = openpyxl.load_workbook(path, read_only=True, data_only=True) ws = wb.active rows = ws.iter_rows(values_only=True) H = {h: i for i, h in enumerate([str(x).strip() for x in next(rows)])} out = [] for r in rows: code = str(r[H['货号#']] or '').strip() if code not in CODES: continue typ = str(r[H['单据类型']] or '') st = str(r[H.get('单据状态', 3)] or '') if '单据状态' in H else '已审核' cust = str(r[H['客户']] or '') gift = str(r[H['是否赠品']] or '') ok = typ == '标准销售订单' and (st == '已审核' or st == '') and gift != '是' and not any(k in cust for k in ('宝锐', '宝泰', '横琴')) if not ok and not include_test: continue try: amt = float(r[H['价税合计']] or 0) except Exception: amt = 0.0 try: qty = float(r[H['销售数量']] or 0) except Exception: qty = 0.0 out.append({'date': pdate(r[H['合同日期']]), 'no': str(r[H['单据编号']] or ''), 'cust': cust, 'sales': str(r[H['销售员']] or ''), 'sku': code, 'qty': qty, 'unit': str(r[H['销售单位']] or ''), 'amt': round(amt, 2), 'dept': str(r[H['备货部门']] or ''), 'type': typ}) return out L26 = load(P26) L25 = [x for x in load(P25) if x['date'] and x['date'] < dt.date(2026, 1, 1)] # 汇总 def agg(lines): sku = defaultdict(float) kit = defaultdict(float) dept = defaultdict(float) cust = defaultdict(float) orders = set() for x in lines: sku[x['sku']] += x['amt'] dept[x['dept']] += x['amt'] cust[x['cust']] += x['amt'] orders.add(x['no']) for k, parts in KITS.items(): if x['sku'] in parts: kit[k] += x['amt'] return sku, kit, dept, cust, orders s26, k26, d26, c26, o26 = agg(L26) s25, k25, d25, c25, o25 = agg(L25) print('2026：行%d 订单%d 合计%.2f' % (len(L26), len(o26), sum(s26.values()))) for k, v in k26.items(): print(' ', k, round(v, 2)) print('2025H2：行%d 订单%d 合计%.2f' % (len(L25), len(o25), sum(s25.values()))) for k, v in k25.items(): print(' ', k, round(v, 2)) # Excel wb = openpyxl.Workbook() HF = PatternFill('solid', fgColor='1F4E79'); HFONT = Font(color='FFFFFF', bold=True, size=10) def style(ws, headers, widths, data, money=(), pct=()): for i, h in enumerate(headers, 1): c = ws.cell(row=1, column=i, value=h); c.fill = HF; c.font = HFONT c.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) ws.column_dimensions[get_column_letter(i)].width = widths[i - 1] for ri, row in enumerate(data, 2): for ci, v in enumerate(row, 1): c = ws.cell(row=ri, column=ci, value=v) if ci in money: c.number_format = '#,##0.00' if ci in pct: c.number_format = '0.00"%"' ws.freeze_panes = 'A2' ws1 = wb.active; ws1.title = '套装汇总' rows1 = [] for k, parts in KITS.items(): tot = k26.get(k, 0) rows1.append([k, round(tot, 2), len({x['no'] for x in L26 if x['sku'] in parts}), len({x['cust'] for x in L26 if x['sku'] in parts}), round(k25.get(k, 0), 2)]) rows1.append(['合计', round(sum(k26.values()), 2), len(o26), len({x['cust'] for x in L26}), round(sum(k25.values()), 2)]) style(ws1, ['套装', '2026 销售额(元)', '2026 订单数', '2026 客户数', '2025H2 销售额(元，参照)'], [40, 18, 12, 12, 22], rows1, money=(2, 5)) ws2 = wb.create_sheet('组分明细') rows2 = [] for k, parts in KITS.items(): for p in parts: rows2.append([k, p, NAMES.get(p, ''), round(s26.get(p, 0), 2), round(s25.get(p, 0), 2)]) style(ws2, ['套装', '组份货号', '组份名称', '2026 销售额(元)', '2025H2(元)'], [40, 14, 46, 16, 14], rows2, money=(4, 5)) ws3 = wb.create_sheet('订单明细_2026') rows3 = [] for x in sorted(L26, key=lambda y: (y['date'] or dt.date.min)): kit = [k for k, parts in KITS.items() if x['sku'] in parts][0] rows3.append([str(x['date']), x['no'], x['cust'], kit.split('（')[0], x['sku'], x['qty'], x['unit'], x['amt'], x['dept'], x['sales']]) style(ws3, ['日期', '订单号', '客户', '套装', '组份货号', '数量', '单位', '销售额(元)', '备货部门', '销售员'], [12, 18, 28, 12, 13, 9, 8, 14, 20, 10], rows3, money=(8,)) ws4 = wb.create_sheet('口径说明') notes = [ ['项目', '说明'], ['统计对象', '套装 2FM101-1 与 2FM101-2 的组份货号，按组份销售额加总为套装销售额'], ['组份', '2FM101-1＝-1-1 / -1-2 / -1-3；2FM101-2＝-2-1 / -2-2 / -2-3'], ['2026 数据源', '销售订单_20260101-20261020.xlsx（26,301 行，2026-01-04 ~ 2026-10-20）'], ['2025 参照', '产品物流客户价格大表-2025.6-2026.6.xlsx，仅取 2026-01-01 之前的行（即 2025 下半年）'], ['口径', '标准销售订单 + 已审核 + 非赠品 + 剔除宝锐/宝泰/横琴；金额=价税合计'], ['双重计数检查', '2026 订单中未出现 2FM101-1 / 2FM101-2 母号行，无需扣减'], ['空组份', '2FM101-1-3（1M MgCl2）与 2FM101-2-3（200mM 醋酸锰）2026 年无成交行'], ] for i, row in enumerate(notes, 1): for j, v in enumerate(row, 1): c = ws4.cell(row=i, column=j, value=v) if i == 1: c.fill = HF; c.font = HFONT ws4.column_dimensions['A'].width = 16 ws4.column_dimensions['B'].width = 92 wb.save(OUT) print('✅', OUT) json.dump({'y2026': [{**x, 'date': str(x['date'])} for x in L26], 'y2025': [{**x, 'date': str(x['date'])} for x in L25]}, open(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '数据', 'kit_2FM101_lines.json'), 'w'), ensure_ascii=False, indent=1)