#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
宝锐 2026年8月月报 数据入库脚本
解析: 数据/上传/2026年8月月报/2026年8月月报/ 下的多部门多月度文件
输出: 数据/月报库/report_store.json
"""
import os, json, re, glob
import pandas as pd
import xlrd

BASE = os.path.expanduser('~/Desktop/Hermes输出-工作类/数据/上传/2026年8月月报/2026年8月月报/')
OUT_DIR = os.path.expanduser('~/Desktop/Hermes输出-工作类/数据/月报库/')
os.makedirs(OUT_DIR, exist_ok=True)

MONTHS = ['1月','2月','3月','4月','5月','6月','7月','8月','9月','10月','11月','12月']

def sf(v):
    """安全浮点"""
    try:
        if v is None: return None
        s = str(v).strip().replace(',','')
        if s in ('','None','nan','NaN','-1','/','\\'): return None
        return float(s)
    except:
        return None

def dept_of(path):
    fn = os.path.basename(path)
    if '销售拓展部' in fn: return '销售拓展部'
    if '生命科学' in fn: return '生命科学部'
    if '大客户' in fn: return '大客户部'
    return None

def month_of(path):
    m = re.search(r'/(\d+)月份/', path)
    return int(m.group(1)) if m else None

def find_monthly_data():
    """扫描所有 月度数据 文件 → [(path, dept, month)]"""
    out = []
    for root, dirs, files in os.walk(BASE):
        for f in files:
            if f.startswith('.') or '__MACOSX' in root: continue
            if '月度数据' in f and f.endswith('.xlsx'):
                p = os.path.join(root, f)
                d = dept_of(p)
                m = month_of(p)
                if d and m:
                    out.append((p, d, m))
    return out

# ============ 1. 销售额：部门年度目标 + 月度目标 + 实际 ============
def parse_sales_target():
    sales = {}
    # 只用最新月份(8月)文件，数据最全（含全年目标+逐月实际+年度目标）
    for p, d, m in find_monthly_data():
        if m != 8: continue
        try:
            xl = pd.ExcelFile(p)
            sn = [s for s in xl.sheet_names if '年度指标达成' in s]
            if not sn: continue
            df = pd.read_excel(p, sheet_name=sn[0], header=None)
            if len(df) < 6: continue
            row_target = df.iloc[4]
            row_actual = df.iloc[5]
            if d not in sales:
                sales[d] = {'annual': None, 'months': {}}
            # 年度目标 = 列15
            annual = sf(row_target.iloc[15]) if len(row_target) > 15 else None
            if annual is not None:
                sales[d]['annual'] = annual
            # 月份 = 列3-14（1-12月）
            for i, mon in enumerate(MONTHS):
                col = 3 + i
                if col >= len(row_target): break
                t = sf(row_target.iloc[col])
                a = sf(row_actual.iloc[col])
                if t is None and a is None: continue
                sales[d]['months'][mon] = {'target': t, 'actual': a}
        except Exception as e:
            print(f'  [sales_target 跳过] {p}: {e}')
    return sales

# ============ 2. 近三年销售（部门级） ============
def parse_three_year():
    ty = {}
    for p, d, m in find_monthly_data():
        if d not in ('销售拓展部','大客户部'): continue
        if m != 8: continue  # 只用最新8月文件（含完整1-8月+全年合计）
        try:
            xl = pd.ExcelFile(p)
            sn = None
            # 精确匹配"近三年销售数据"（部门级逐月），避免误匹配"近三年销售情况"（客户级明细）
            for s in xl.sheet_names:
                if '近三年销售数据' in s or '三年销售数据' in s:
                    sn = s; break
            if not sn: continue
            df = pd.read_excel(p, sheet_name=sn, header=None)
            # 行3=2024, 行4=2025, 行5=2026, 列3-14=1-12月, 列15=合计
            if len(df) < 6: continue
            years = ['2024','2025','2026']
            if d not in ty: ty[d] = {}
            for yi, yr in enumerate(years):
                row = df.iloc[3 + yi]
                vals = []
                for col in range(3, 15):
                    vals.append(sf(row.iloc[col]))
                total = sf(row.iloc[15]) if len(row) > 15 else None
                ty[d][yr] = {'months': vals, 'total': total}
        except Exception as e:
            print(f'  [three_year 跳过] {p}: {e}')
    return ty

# ============ 3. 客户清单 ============
def parse_customers():
    custs = {}
    # 最新客户清单
    cl = BASE + '诊断原料大客户_生命科学销售部/8月份/诊断原料&生命科学客户清单20260831.xls'
    if not os.path.exists(cl):
        return custs
    wb = xlrd.open_workbook(cl)
    for sn in wb.sheet_names():
        ws = wb.sheet_by_name(sn)
        if sn in ('华北华西','华中华南','华东'):
            # 集团简称(0) 客户名称(1) 销售员(2)
            for i in range(1, ws.nrows):
                grp = str(ws.cell_value(i,0)).strip()
                cust = str(ws.cell_value(i,1)).strip()
                sp = str(ws.cell_value(i,2)).strip()
                if cust:
                    custs[cust] = {'group': grp or cust, 'salesperson': sp, 'region': sn}
        elif sn in ('大客户销售部','销售拓展部'):
            for i in range(2, ws.nrows):
                grp = str(ws.cell_value(i,1)).strip()
                cust = str(ws.cell_value(i,2)).strip()
                sp = str(ws.cell_value(i,3)).strip()
                listed = str(ws.cell_value(i,4)).strip()
                if cust:
                    custs[cust] = {'group': grp or cust, 'salesperson': sp, 'dept': sn, 'listed': listed}
        elif sn == '生命科学销售部':
            for i in range(2, ws.nrows):
                cust = str(ws.cell_value(i,1)).strip()
                sp = str(ws.cell_value(i,2)).strip()
                region = str(ws.cell_value(i,3)).strip()
                if cust:
                    custs[cust] = {'group': cust, 'salesperson': sp, 'dept': '生命科学销售部', 'region': region}
    return custs

# ============ 4. 过程指标（按人，销售拓展部 + 大客户部） ============
def parse_person_perf():
    """销售拓展部和大客户部的月度绩效（单月拜访/测试单/订单）"""
    perf = {}
    for p, d, m in find_monthly_data():
        if d not in ('销售拓展部','大客户部'): continue
        try:
            xl = pd.ExcelFile(p)
            sn = [s for s in xl.sheet_names if '过程指标' in s]
            if not sn: continue
            df = pd.read_excel(p, sheet_name=sn[0], header=None)
            mon_key = MONTHS[m-1]
            if mon_key not in perf: perf[mon_key] = {}
            rows = []
            # 从行4开始（数据行）
            for i in range(4, len(df)):
                row = df.iloc[i]
                name = str(row.iloc[1]).strip() if row.iloc[1] is not None else ''
                if not name or name in ('合计','NaN','nan') or name.startswith('NaN'): 
                    continue
                if '合计' in name: continue
                # 通用字段：拜访(列2或3)、订单数量/金额（找列）
                # 大客户部: 列2=拜访, 列4=总发放数, 列5=NGS发放, 列6=反馈, 列7=反馈率, 列8=订单数量, 列9=订单金额
                # 销售拓展部: 列3=拜访总数, ... 列12=订单数量, 列13=订单金额
                entry = {'name': name}
                # 列索引（各部门表头不同，2026-09-05 实测确认）：
                # 大客户部: 2=拜访 3=测试发放 4=NGS发放 5=测试反馈 6=反馈率 7=订单数 8=订单金额
                # 销售拓展部: 2=拜访 3=PDRN拜访 4=NGS拜访 5=测试发放 8=测试反馈 9=反馈率 11=订单数 12=订单金额
                def g(col):
                    return sf(row.iloc[col]) if len(row) > col else None
                if d == '大客户部':
                    entry['拜访'] = g(2)
                    entry['测试发放'] = g(3)
                    entry['NGS发放'] = g(4)
                    entry['测试反馈'] = g(5)
                    entry['反馈率'] = g(6)
                    entry['订单数'] = g(7)
                    entry['订单金额'] = g(8)
                else:
                    entry['拜访'] = g(2)
                    entry['PDRN拜访'] = g(3)
                    entry['NGS拜访'] = g(4)
                    entry['测试发放'] = g(5)
                    entry['测试反馈'] = g(8)
                    entry['反馈率'] = g(9)
                    entry['订单数'] = g(11)
                    entry['订单金额'] = g(12)
                rows.append(entry)
            if rows:
                perf[mon_key][d] = rows
        except Exception as e:
            print(f'  [person_perf 跳过] {p}: {e}')
    return perf

# ============ 5. 生命科学部订单矩阵（按人×月份，含区域） ============
def parse_life_sci_orders():
    """生命科学的过程指标是订单月度矩阵（按人×8个月，列0=区域浙江/广东）"""
    orders = []
    for p, d, m in find_monthly_data():
        if d != '生命科学部': continue
        if m != 8: continue  # 8月文件含完整1-8月矩阵
        try:
            xl = pd.ExcelFile(p)
            sn = [s for s in xl.sheet_names if '过程指标' in s]
            if not sn: continue
            df = pd.read_excel(p, sheet_name=sn[0], header=None)
            # 行3起数据：列0=区域(合并单元格需ffill), 列1=姓名, 列2+2*(mi-1)=mi月订单数, 列3+2*(mi-1)=mi月金额
            last_region = ''
            for i in range(3, len(df)):
                row = df.iloc[i]
                r0 = str(row.iloc[0]).strip() if row.iloc[0] is not None else ''
                if r0 and r0 not in ('nan','NaN'):
                    last_region = r0
                region = last_region
                name = str(row.iloc[1]).strip() if row.iloc[1] is not None else ''
                if not name or name in ('合计','nan','NaN') or '合计' in name: continue
                entry = {'name': name, 'region': region, 'months': {}}
                for mi in range(1, 9):
                    c1 = 2 + 2*(mi-1); c2 = 3 + 2*(mi-1)
                    qty = sf(row.iloc[c1]) if len(row) > c1 else None
                    amt = sf(row.iloc[c2]) if len(row) > c2 else None
                    if qty is not None or amt is not None:
                        entry['months'][MONTHS[mi-1]] = {'订单数': qty, '订单金额': amt}
                orders.append(entry)
        except Exception as e:
            print(f'  [life_sci 跳过] {p}: {e}')
    return orders


# ============ 6. 绩效目标（关键任务项 T1/T2/T3） ============
def parse_perf_plan():
    """各部门关键任务项定义（权重 + T1/T2/T3），9月执行版（生命科学是8月）"""
    files = {
        '销售拓展部': ('⭐️三部门月度绩效共享文档-9月执行版/⭐️拓展部-月度关键任务责任书-2026-9月.xlsx', '9月'),
        '大客户部': ('⭐️三部门月度绩效共享文档-9月执行版/⭐️大客户-月度关键任务协议书-2026-9月.xlsx', '9月'),
        '生命科学部': ('⭐️三部门月度绩效共享文档-9月执行版/🌟生命科学销售部-销售工程师月度关键任务协议书-2026年8月.xlsx', '2026年8月'),
    }
    plan = {}
    for dept, (rel, mon) in files.items():
        f = BASE + rel
        if not os.path.exists(f):
            print(f'  [perf_plan 缺失] {rel}')
            continue
        try:
            xl = pd.ExcelFile(f)
            sn = [s for s in xl.sheet_names if mon in s and '指派' not in s]
            if not sn:
                sn = [s for s in xl.sheet_names if '工程师' in s or '大客户部-' in s]
            if not sn: continue
            df = pd.read_excel(f, sheet_name=sn[0], header=None)
            tasks = []
            for r in range(5, min(20, len(df))):
                row = df.iloc[r]
                seq = str(row.iloc[1]).strip() if len(row) > 1 and row.iloc[1] is not None else ''
                if not seq.isdigit(): continue
                task = str(row.iloc[2]).strip() if len(row) > 2 and row.iloc[2] is not None else ''
                w = str(row.iloc[4]).strip() if len(row) > 4 and row.iloc[4] is not None else ''
                t1 = str(row.iloc[5]).strip() if len(row) > 5 and row.iloc[5] is not None else ''
                t2 = str(row.iloc[6]).strip() if len(row) > 6 and row.iloc[6] is not None else ''
                t3 = str(row.iloc[7]).strip() if len(row) > 7 and row.iloc[7] is not None else ''
                tasks.append({'name': task, 'weight': w, 'T1': t1, 'T2': t2, 'T3': t3})
            if tasks:
                plan[dept] = {'month': mon, 'tasks': tasks}
        except Exception as e:
            print(f'  [perf_plan 跳过] {rel}: {e}')
    return plan


# ============ 7. 客户级销售额排名（2025 vs 2026） ============
def parse_customer_sales():
    """大客户部客户级销售额排名：左=2025年，右=2026年1-8月"""
    f = BASE + '诊断原料大客户_生命科学销售部/8月份/1、2026月度数据-诊断原料大客户销售部.xlsx'
    if not os.path.exists(f): return {}
    try:
        xl = pd.ExcelFile(f)
        sn = [s for s in xl.sheet_names if '近三年销售情况' in s]
        if not sn: return {}
        df = pd.read_excel(f, sheet_name=sn[0], header=None)
        out = {'2025': [], '2026': []}
        for r in range(2, len(df)):
            row = df.iloc[r]
            # 区块一（2025年）：列1=集团 2=客户 3=销售员 4=销售额
            g1 = str(row.iloc[1]).strip() if row.iloc[1] is not None else ''
            c1 = str(row.iloc[2]).strip() if len(row) > 2 and row.iloc[2] is not None else ''
            s1 = str(row.iloc[3]).strip() if len(row) > 3 and row.iloc[3] is not None else ''
            a1 = sf(row.iloc[4]) if len(row) > 4 else None
            if c1 and c1 not in ('nan', 'NaN') and not c1.isdigit():
                gg1 = g1 if g1 and g1 not in ('nan', 'NaN') else c1  # 空集团用客户名
                out['2025'].append({'group': gg1, 'customer': c1, 'salesperson': s1, 'amount': a1})
            # 区块二（2026年1-8月）：列8=集团 9=客户 10=销售员 11=销售额
            g2 = str(row.iloc[8]).strip() if len(row) > 8 and row.iloc[8] is not None else ''
            c2 = str(row.iloc[9]).strip() if len(row) > 9 and row.iloc[9] is not None else ''
            s2 = str(row.iloc[10]).strip() if len(row) > 10 and row.iloc[10] is not None else ''
            a2 = sf(row.iloc[11]) if len(row) > 11 else None
            if c2 and c2 not in ('nan', 'NaN') and not c2.isdigit():
                gg2 = g2 if g2 and g2 not in ('nan', 'NaN') else c2  # 空集团用客户名
                out['2026'].append({'group': gg2, 'customer': c2, 'salesperson': s2, 'amount': a2})
        return out
    except Exception as e:
        print(f'  [customer_sales 跳过]: {e}')
        return {}


# ============ 8. 大客户1岗订单（按客户归属聚合，23家清单） ============
def parse_dkl1_orders():
    """大客户1岗（刘子研）23家客户 1-8月订单金额，按客户归属聚合（含代管期6/8月）"""
    kc_path = os.path.expanduser('~/Desktop/Hermes输出-工作类/数据/pricing/_key_customers.json')
    if not os.path.exists(kc_path):
        print('  [dkl1 跳过] _key_customers.json 不存在')
        return {}
    kc = json.load(open(kc_path))
    lzy_customers = set(x['name'] for x in kc if x.get('salesperson') == '刘子研')
    DKL = BASE + '诊断原料大客户_生命科学销售部/'
    files = {
        '1月': DKL+'1月份/1月份订单数据-大客户部.xls',
        '2月': DKL+'2月份/1、大客户部/源数据/3.销售订单/销售订单.xls',
        '3月': DKL+'3月份/1、大客户部/源数据/3.销售订单/订单.xls',
        '4月': DKL+'4月份/1、大客户部/源数据/3.销售订单/销售订单.xls',
        '5月': DKL+'5月份/源数据/3.销售订单/订单数据.xls',
        '6月': DKL+'6月份/1、大客户部/源数据/3.销售订单/销售订单.xls',
        '7月': DKL+'7月份/1、大客户部/源数据/3.销售订单/订单数据.xls',
        '8月': DKL+'8月份/1、大客户部/源数据/3.销售订单/订单数据.xls',
    }
    def col_of(hdr, name):
        for i, h in enumerate(hdr):
            if h == name: return i
        return None
    months = {}
    for mon, f in files.items():
        if not os.path.exists(f):
            months[mon] = 0; continue
        wb = xlrd.open_workbook(f); sh = wb.sheet_by_index(0)
        hdr = [str(sh.cell_value(0, c)).strip() for c in range(sh.ncols)]
        cc = col_of(hdr, '客户'); ct = col_of(hdr, '单据类型'); cg = col_of(hdr, '是否赠品')
        ca = col_of(hdr, '价税合计（本位币）') or col_of(hdr, '价税合计')
        if None in (cc, ca):
            months[mon] = 0; continue
        monthly = 0
        for r in range(1, sh.nrows):
            customer = str(sh.cell_value(r, cc)).strip()
            if customer not in lzy_customers: continue
            if str(sh.cell_value(r, cg)).strip() == '是': continue  # 剔除赠品
            if str(sh.cell_value(r, ct)).strip() != '标准销售订单': continue
            amt = sh.cell_value(r, ca)
            if isinstance(amt, (int, float)): monthly += amt
        months[mon] = round(monthly, 2)
    total = round(sum(months.values()), 2)
    return {'customers': sorted(lzy_customers), 'months': months, 'total': total}


if __name__ == '__main__':
    print('=== 开始入库 ===')
    print('1/7 解析销售额(部门目标+实际)...')
    sales = parse_sales_target()
    print('2/7 解析近三年销售...')
    ty = parse_three_year()
    print('3/7 解析客户清单...')
    custs = parse_customers()
    print('4/7 解析过程指标(按人)...')
    perf = parse_person_perf()
    print('5/7 解析生命科学订单矩阵...')
    life_sci = parse_life_sci_orders()
    print('6/8 解析绩效目标(T1/T2/T3)...')
    plan = parse_perf_plan()
    print('7/8 解析客户级销售额排名...')
    cust_sales = parse_customer_sales()
    print('8/8 解析大客户1岗订单(23家客户归属聚合)...')
    dkl1 = parse_dkl1_orders()

    store = {
        'meta': {
            'source': '2026年8月月报数据包',
            'generated': '2026-09-05',
            'depts': ['销售拓展部','大客户部','生命科学部'],
        },
        'sales_target': sales,
        'three_year': ty,
        'customers': custs,
        'person_perf': perf,
        'life_sci_orders': life_sci,
        'perf_plan': plan,
        'customer_sales': cust_sales,
        'dkl1_orders': dkl1,
    }
    outp = os.path.join(OUT_DIR, 'report_store.json')
    with open(outp, 'w', encoding='utf-8') as f:
        json.dump(store, f, ensure_ascii=False, indent=2)
    print(f'\n✅ 入库完成: {outp}')
    print(f'  sales_target: {list(sales.keys())}')
    print(f'  three_year: {list(ty.keys())}')
    print(f'  customers: {len(custs)} 个客户')
    print(f'  person_perf 月份: {list(perf.keys())}')
    print(f'  life_sci_orders: {len(life_sci)} 人')
    print(f'  perf_plan: {list(plan.keys())}')
    print(f'  customer_sales: 2025年{len(cust_sales.get("2025",[]))}条, 2026年{len(cust_sales.get("2026",[]))}条')
    print(f'  dkl1_orders: 合计 {dkl1.get("total",0)/1e4:.1f}万 (23家客户)')