253 lines
8.7 KiB
Python
253 lines
8.7 KiB
Python
#!/usr/bin/env python3
|
||
"""
|
||
订单汇总 A–X 全量镜像刷新
|
||
触发：Step2（Cursor Step1 完成后 @小溪）
|
||
归属：小溪 (xiaoxi)
|
||
|
||
进表条件：K=是 · O>0 · 非全额退(P空或P<O) · L≥C
|
||
全额退 → 整行不进订单表（销售行清 K/O/P/Q）
|
||
镜像 A–V 原样 + W 渠道归属 + X=1
|
||
|
||
分工约定见 docs/bot-step2-schedule-and-orders.md
|
||
"""
|
||
import json, time, re, sys, requests, psycopg2
|
||
from datetime import datetime
|
||
from feishu_sheet_utils import FeishuSheetWriter
|
||
|
||
# ── 配置 ──
|
||
APP_ID = "cli_a929ae22e0b8dcc8"
|
||
APP_SECRET = "OtFjMy7p3qE3VvLbMdcWidwgHOnGD4FJ"
|
||
SPREADSHEET_TOKEN = "NoZqsFi47hIOHEt9j8WcfRtbnug"
|
||
SALES_SHEETS = {"f975f0": "吴迪", "qJF4I": "小龙", "qJF4J": "成都"}
|
||
SUMMARY_SHEET = "2smjwA"
|
||
|
||
def _get_pg_password():
|
||
import os
|
||
secrets_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), "..", "secrets.env")
|
||
with open(secrets_path) as f:
|
||
for line in f:
|
||
if line.startswith("PG_ONLINE_PASSWORD="):
|
||
return line.strip().split("=", 1)[1].strip('"').strip("'")
|
||
raise RuntimeError("PG_ONLINE_PASSWORD not found in secrets.env")
|
||
|
||
PG_CONFIG = {
|
||
"host": "bj-postgres-16pob4sg.sql.tencentcdb.com", "port": 28591,
|
||
"user": "ai_member", "password": _get_pg_password(), "database": "vala_bi",
|
||
}
|
||
|
||
GOODS_MAP = {
|
||
57: "瓦拉英语level1·单季", 60: "瓦拉英语level1", 63: "瓦拉英语level1·单季",
|
||
31: "瓦拉英语年包", 32: "瓦拉英语单季度包", 33: "瓦拉英语level2", 54: "瓦拉英语季度包",
|
||
61: "瓦拉英语level1+2",
|
||
}
|
||
|
||
# 达人昵称关键词
|
||
DAREN_NICKNAMES = ["晚柠", "学霸", "念妈", "神奇瓜妈", "三人行", "老王"]
|
||
|
||
|
||
def get_token():
|
||
r = requests.post("https://open.feishu.cn/open-apis/auth/v3/tenant_access_token/internal",
|
||
json={"app_id": APP_ID, "app_secret": APP_SECRET}, timeout=15)
|
||
return r.json()["tenant_access_token"]
|
||
|
||
|
||
def read_sheet(token, sheet_id, range_str):
|
||
url = f"https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/{SPREADSHEET_TOKEN}/values/{sheet_id}!{range_str}?valueRenderOption=ToString"
|
||
r = requests.get(url, headers={"Authorization": f"Bearer {token}"}, timeout=30)
|
||
data = r.json()
|
||
if data.get("code") != 0:
|
||
print(f"Error reading {sheet_id}: {data}")
|
||
return []
|
||
return data["data"]["valueRange"]["values"]
|
||
|
||
|
||
def put_values(token, sheet_id, range_str, values, retries=3):
|
||
url = f"https://open.feishu.cn/open-apis/sheets/v2/spreadsheets/{SPREADSHEET_TOKEN}/values"
|
||
body = {"valueRange": {"range": f"{sheet_id}!{range_str}", "values": values}}
|
||
for attempt in range(retries):
|
||
r = requests.put(url, headers={
|
||
"Authorization": f"Bearer {token}",
|
||
"Content-Type": "application/json"
|
||
}, json=body, timeout=30)
|
||
result = r.json()
|
||
if result.get("code") == 0:
|
||
return True
|
||
print(f" Retry {attempt+1} for {range_str}: {result.get('msg','')}")
|
||
time.sleep(1)
|
||
print(f" FAILED {range_str}")
|
||
return False
|
||
|
||
|
||
def parse_date(s):
|
||
"""Parse date string to (year, month, day) tuple."""
|
||
s = str(s).strip()
|
||
if not s:
|
||
return None
|
||
# YYYY-MM-DD
|
||
m = re.match(r'(\d{4})-(\d{1,2})-(\d{1,2})', s)
|
||
if m:
|
||
return (int(m.group(1)), int(m.group(2)), int(m.group(3)))
|
||
# M月D日
|
||
m = re.match(r'(\d{1,2})月(\d{1,2})日', s)
|
||
if m:
|
||
return (2026, int(m.group(1)), int(m.group(2)))
|
||
# YYYY/M/D
|
||
m = re.match(r'(\d{4})/(\d{1,2})/(\d{1,2})', s)
|
||
if m:
|
||
return (int(m.group(1)), int(m.group(2)), int(m.group(3)))
|
||
return None
|
||
|
||
|
||
def date_le(a, b):
|
||
"""Return True if date a <= date b."""
|
||
if a is None or b is None:
|
||
return False
|
||
return a <= b
|
||
|
||
|
||
# W列渠道归属分类规则 [王虹茗确认 2026-06-15]
|
||
def classify_w_channel(m_channel, sales_name=""):
|
||
"""
|
||
W 渠道归属，基于 M 成交渠道 + 销售昵称。
|
||
端内: 精确匹配 3 个渠道
|
||
销转: sales-adp-*
|
||
达人: newmedia-daren-* / newmedia-dianpu-wwxx-0-0 / 昵称含达人关键词
|
||
直购: 其余全部
|
||
"""
|
||
m = str(m_channel).strip() if m_channel else ""
|
||
|
||
# 达人（昵称关键词优先，保持原有逻辑）
|
||
if any(nick in str(sales_name) for nick in DAREN_NICKNAMES):
|
||
return "达人"
|
||
|
||
if not m:
|
||
return "直购"
|
||
|
||
if m in ("app-active-h5-0-0", "app-sales-bj-qhm-0", "app-sales-bj-wd-0"):
|
||
return "端内"
|
||
if m.startswith("sales-adp-"):
|
||
return "销转"
|
||
if m.startswith("newmedia-daren-") or m == "newmedia-dianpu-wwxx-0-0":
|
||
return "达人"
|
||
# 其余: dianpu(不含wwxx) + partner/stream/miniprogram/jingxuan/空/shuadan等 → 直购
|
||
return "直购"
|
||
|
||
|
||
def phone_match(sheet_phone, db_tel):
|
||
"""Match sheet phone number against DB tel (masked like 138****4503)."""
|
||
if not sheet_phone or not db_tel:
|
||
return False
|
||
sheet_phone = str(sheet_phone).strip()
|
||
db_tel = str(db_tel).strip()
|
||
if sheet_phone == db_tel:
|
||
return True
|
||
if "****" in db_tel:
|
||
parts = db_tel.split("****")
|
||
if len(parts) == 2:
|
||
prefix, suffix = parts
|
||
if sheet_phone.startswith(prefix) and sheet_phone.endswith(suffix):
|
||
return True
|
||
return False
|
||
|
||
|
||
def main():
|
||
print(f"[{datetime.now():%Y-%m-%d %H:%M:%S}] 订单汇总全量刷新 启动")
|
||
token = get_token()
|
||
|
||
# ── Step 1: 读取销售三表（含 X/Y 列）──
|
||
all_rows = []
|
||
for sid, name in SALES_SHEETS.items():
|
||
print(f"Reading {name}...")
|
||
vals = read_sheet(token, sid, "A3:Y10000")
|
||
filtered = []
|
||
for i, row in enumerate(vals):
|
||
while len(row) < 25:
|
||
row.append("")
|
||
b = str(row[1]).strip() if row[1] else ""
|
||
e = str(row[4]).strip() if row[4] else ""
|
||
h = str(row[7]).strip() if row[7] else ""
|
||
if b or e or h:
|
||
filtered.append({
|
||
"sid": sid, "name": name, "row": i + 3,
|
||
"raw": row[:25], # A-Y
|
||
})
|
||
print(f" {len(filtered)} non-empty rows")
|
||
all_rows.extend(filtered)
|
||
|
||
print(f"Total rows: {len(all_rows)}")
|
||
|
||
# ── Step 2: 筛选进订单汇总的行 ──
|
||
# 条件：X=1 (gate)
|
||
order_rows = []
|
||
for r in all_rows:
|
||
raw = r["raw"]
|
||
# X 列 (index 23) = 1
|
||
try:
|
||
x_val = int(float(raw[23])) if len(raw) > 23 and raw[23] not in (None, "") else 0
|
||
except (ValueError, TypeError):
|
||
x_val = 0
|
||
if x_val != 1:
|
||
continue
|
||
|
||
order_rows.append(r)
|
||
|
||
print(f"Order rows after filter: {len(order_rows)}")
|
||
|
||
# ── Step 2.5: 去重（同 UID 多表出现，保留行号最小）──
|
||
uid_best = {}
|
||
for r in order_rows:
|
||
raw = r["raw"]
|
||
uid = str(raw[7]).strip() if len(raw) > 7 and raw[7] not in (None, "") else ""
|
||
try:
|
||
uid = str(int(float(raw[7])))
|
||
except:
|
||
pass
|
||
if not uid:
|
||
continue
|
||
if uid not in uid_best or r["row"] < uid_best[uid][0]:
|
||
uid_best[uid] = (r["row"], r)
|
||
order_rows = [v[1] for v in uid_best.values()]
|
||
print(f" After UID dedup: {len(order_rows)} rows")
|
||
|
||
# ── Step 3: 按 K 下单日降序 ──
|
||
order_rows.sort(key=lambda r: str(r["raw"][10]) if len(r["raw"]) > 10 and r["raw"][10] else "", reverse=True)
|
||
|
||
# ── Step 4: 构建 A–W 行（A-V 镜像 + W=渠道=三表Y + 无X列）──
|
||
summary_rows = []
|
||
for r in order_rows:
|
||
raw = r["raw"]
|
||
# A–V 原样镜像 (indices 0-21)
|
||
new_row = list(raw[:22])
|
||
|
||
# W: 渠道归属 = 三表 Y 列 (index 24)
|
||
y_val = str(raw[24]).strip() if len(raw) > 24 and raw[24] not in (None, "") else ""
|
||
new_row.append(y_val)
|
||
|
||
summary_rows.append(new_row)
|
||
|
||
print(f"Summary rows: {len(summary_rows)}")
|
||
|
||
# ── Step 5: 写入订单汇总（使用安全写入工具，自动遵守 5000 格上限）──
|
||
print("Writing to 订单汇总...")
|
||
writer = FeishuSheetWriter(SPREADSHEET_TOKEN, token)
|
||
|
||
# 先清空旧数据区（23 列 A-W，自动计算批大小 ≤ 4400 格/批）
|
||
writer.clear(SUMMARY_SHEET, start_row=3, end_row=2000, cols=23)
|
||
time.sleep(0.5)
|
||
|
||
# 写入新数据（23 列 A-W，自动分批）
|
||
total = len(summary_rows)
|
||
writer.write(SUMMARY_SHEET, start_row=3, rows=summary_rows, cols=23)
|
||
|
||
# ── Step 6: 清除多余旧行 ──
|
||
existing = read_sheet(token, SUMMARY_SHEET, "A3:A4000")
|
||
old_count = len([r for r in existing if r and any(c for c in r if c)])
|
||
if old_count > total:
|
||
writer.clear(SUMMARY_SHEET, start_row=3 + total, end_row=3 + old_count - 1, cols=23)
|
||
|
||
print(f"[{datetime.now():%Y-%m-%d %H:%M:%S}] ✅ 订单汇总刷新完成")
|
||
|
||
|
||
if __name__ == "__main__":
|
||
main()
|