ai_member_xiaoxi/scripts/migrate_old_leads.py

#!/usr/bin/env python3
"""迁移旧销售线索表 → 新表"""
import openpyxl, json, requests, os, sys

OLD_FILE = "/root/.openclaw/media/inbound/é_è½_å_æ_ä_è_-ç_ä_ç_ï¼_è_æ_ï¼_å_溪å_å_ç---7af347cb-3646-4ebc-97a4-a70b9165c363.xlsx"
SPREADSHEET_TOKEN = "NoZqsFi47hIOHEt9j8WcfRtbnug"
SHEET_IDS = {"吴迪": "f975f0", "小龙": "qJF4I", "成都": "qJF4J"}
CRED_DIR = "/root/.openclaw/credentials/xiaoxi"

def get_fs_token():
    with open(os.path.join(CRED_DIR, "config.json")) as f:
        cfg = json.load(f)
    resp = requests.post(
        "https://open.feishu.cn/open-apis/auth/v3/tenant_access_token/internal",
        json={"app_id": cfg["apps"][0]["appId"], "app_secret": cfg["apps"][0]["appSecret"]},
        timeout=15
    )
    return resp.json()["tenant_access_token"]

def put_values(token, sheet_id, range_str, values):
    url = f"https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/{SPREADSHEET_TOKEN}/values"
    body = {"valueRange": {"range": f"{sheet_id}!{range_str}", "values": values}}
    resp = requests.put(url, headers={
        "Authorization": f"Bearer {token}",
        "Content-Type": "application/json"
    }, json=body, timeout=30)
    r = resp.json()
    if r.get("code") != 0:
        print(f"  写入失败 {range_str}: {r}")
    return r

def safe_str(v):
    if v is None:
        return ""
    s = str(v).strip()
    # 处理公式残留
    if s.startswith("=") or s.startswith("IF("):
        return ""
    return s

def main():
    wb = openpyxl.load_workbook(OLD_FILE, data_only=True)
    token = get_fs_token()

    for sheet_name in ["吴迪", "小龙", "成都"]:
        if sheet_name not in wb.sheetnames:
            print(f"⚠️ 未找到 sheet: {sheet_name}")
            continue
        ws = wb[sheet_name]
        sheet_id = SHEET_IDS[sheet_name]
        print(f"\n{'='*40}\n迁移 [{sheet_name}] → {sheet_id}")

        # 收集有效数据行（从第4行开始，跳过表头/标注/说明）
        rows_out = []
        for row_idx in range(4, ws.max_row + 1):
            a = safe_str(ws.cell(row=row_idx, column=1).value)  # 销售归属
            b = safe_str(ws.cell(row=row_idx, column=2).value)  # 微信昵称
            c = safe_str(ws.cell(row=row_idx, column=3).value)  # 进线日期
            e = safe_str(ws.cell(row=row_idx, column=5).value)  # 手机号
            if not a and not b and not e:
                continue  # 空行跳过

            # 旧列 → 新列映射
            # A:销售归属 B:微信昵称 C:进线日期 D:体验节数 E:手机号 F:用户年级 G:课史/跟进
            d_old = safe_str(ws.cell(row=row_idx, column=4).value)   # 体验节数(小溪)
            f = safe_str(ws.cell(row=row_idx, column=6).value)       # 用户年级
            g = safe_str(ws.cell(row=row_idx, column=7).value)       # 课史/跟进
            h_old = safe_str(ws.cell(row=row_idx, column=8).value)   # 用户ID(选填)
            w_col = safe_str(ws.cell(row=row_idx, column=23).value)  # 匹配uid

            # H:用户ID — 优先用匹配uid(W列)，其次用户ID(H列)
            uid = w_col if w_col else h_old

            # 下单信息
            i_old = safe_str(ws.cell(row=row_idx, column=9).value)   # 下单日期 → L
            j_old = safe_str(ws.cell(row=row_idx, column=10).value)  # 成交渠道 → M
            k_old = safe_str(ws.cell(row=row_idx, column=11).value)  # 产品 → N
            l_old = safe_str(ws.cell(row=row_idx, column=12).value)  # 实付金额 → O
            m_old = safe_str(ws.cell(row=row_idx, column=13).value)  # 退款金额 → P

            # 行课信息
            n_old = safe_str(ws.cell(row=row_idx, column=14).value)  # 行课状态(自动) → S
            o_old = safe_str(ws.cell(row=row_idx, column=15).value)  # 最近行课 → T

            # 同步时间
            y_old = safe_str(ws.cell(row=row_idx, column=25).value)  # 同步时间 → V

            # 构建新行 (22列 A-V)
            new_row = [
                a,          # A: 销售归属
                b,          # B: 微信昵称
                c,          # C: 进线日期
                d_old,      # D: 体验节数 (保留旧值)
                e,          # E: 手机号
                f,          # F: 用户年级
                g,          # G: 课史/跟进
                uid,        # H: 用户ID
                "",         # I: 注册日期 (自动回填)
                "",         # J: 下载渠道 (自动回填)
                "",         # K: 是否下单 (自动回填)
                i_old,      # L: 下单日期
                j_old,      # M: 成交渠道
                k_old,      # N: 产品
                l_old,      # O: 下单金额(GMV)
                m_old,      # P: 退款金额
                "",         # Q: 实际收入(GSV) (自动回填)
                "",         # R: 激活课程 (自动回填)
                n_old,      # S: 当前行课进度 (旧行课状态)
                o_old,      # T: 最近行课时间
                "",         # U: 累计学习时长 (自动回填)
                y_old,      # V: 更新时间
            ]
            rows_out.append(new_row)

        print(f"  有效数据: {len(rows_out)} 行")

        if not rows_out:
            continue

        # 分批写入（飞书API限制，每批最多写一定行数）
        BATCH = 50
        for batch_start in range(0, len(rows_out), BATCH):
            batch = rows_out[batch_start:batch_start + BATCH]
            start_row = 3 + batch_start  # 从第3行开始（跳过表头和标注行）
            end_row = start_row + len(batch) - 1
            range_str = f"A{start_row}:V{end_row}"
            put_values(token, sheet_id, range_str, batch)
            print(f"  写入 {range_str} ({len(batch)} 行)")

    # ── 订单汇总 sheet ──
    if "订单汇总" in wb.sheetnames:
        print(f"\n{'='*40}\n迁移 [订单汇总]")
        ws = wb["订单汇总"]
        rows_out = []
        for row_idx in range(4, ws.max_row + 1):
            a = safe_str(ws.cell(row=row_idx, column=1).value)  # 销售归属
            b = safe_str(ws.cell(row=row_idx, column=2).value)  # 进线日期
            c = safe_str(ws.cell(row=row_idx, column=3).value)  # 下单日期
            d = safe_str(ws.cell(row=row_idx, column=4).value)  # 微信昵称
            if not a and not d:
                continue

            e = safe_str(ws.cell(row=row_idx, column=5).value)   # 成交渠道
            f = safe_str(ws.cell(row=row_idx, column=6).value)   # 产品
            g = safe_str(ws.cell(row=row_idx, column=7).value)   # 实付金额
            h = safe_str(ws.cell(row=row_idx, column=8).value)   # 退款金额
            i = safe_str(ws.cell(row=row_idx, column=9).value)   # 落单渠道
            j = safe_str(ws.cell(row=row_idx, column=10).value)  # 渠道归属
            k = safe_str(ws.cell(row=row_idx, column=11).value)  # 订单状态
            l = safe_str(ws.cell(row=row_idx, column=12).value)  # 有效成单

            new_row = [a, b, c, d, e, f, g, h, i, j, k, l]
            rows_out.append(new_row)

        print(f"  有效数据: {len(rows_out)} 行")

        if rows_out:
            # 先创建订单汇总 sheet
            # 先看看有没有这个sheet，没有就创建
            token2 = get_fs_token()
            # 创建新 sheet
            resp = requests.post(
                f"https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/{SPREADSHEET_TOKEN}/sheets_batch_update",
                headers={"Authorization": f"Bearer {token2}", "Content-Type": "application/json"},
                json={"requests": [{"addSheet": {"properties": {"title": "订单汇总", "index": 3}}}]},
                timeout=15
            )
            r = resp.json()
            print(f"  创建订单汇总sheet: {r.get('code')}")
            if r.get("code") == 0:
                summary_sheet_id = r["data"]["replies"][0]["addSheet"]["properties"]["sheetId"]
            else:
                print(f"  创建失败: {r}")
                wb.close()
                return

            # 写表头
            headers = [["销售归属","进线日期","下单日期","微信昵称","成交渠道","产品","实付金额(¥)","退款金额","落单渠道","渠道归属","订单状态","有效成单"]]
            put_values(token2, summary_sheet_id, "A1:L1", headers)

            # 写数据
            BATCH = 50
            for batch_start in range(0, len(rows_out), BATCH):
                batch = rows_out[batch_start:batch_start + BATCH]
                start_row = 2 + batch_start
                end_row = start_row + len(batch) - 1
                range_str = f"A{start_row}:L{end_row}"
                put_values(token2, summary_sheet_id, range_str, batch)
                print(f"  写入 {range_str} ({len(batch)} 行)")

    wb.close()
    print("\n✅ 迁移完成！")

if __name__ == "__main__":
    main()