190 lines
11 KiB
Python
190 lines
11 KiB
Python
#!/usr/bin/env python3
|
||
"""Check 57 gap orders against DB"""
|
||
import subprocess, json, re
|
||
|
||
# Gap data from chat: (month, source_sheet, sales, nickname, date_str, amount, channel, phone, has_clue, keyfrom, is_direct)
|
||
gaps = [
|
||
# === 3月 (20) ===
|
||
("3月","qX7oJ6","小龙","雪珂💗","3月6日",1999,"微信小店","","线索","sales-adp-bj-jxl-0",False),
|
||
("3月","qX7oJ6","小龙","Mars（燚）","3月7日",3598,"小红书-晚柠","","线索","",False),
|
||
("3月","qX7oJ6","小龙","薇薇","3月",1999,"小红书直购","13520306626","无线索","",True),
|
||
("3月","qX7oJ6","小龙","Yeah～^_^","3月20日",3598,"小红书-晚柠","","线索","",False),
|
||
("3月","qX7oJ6","小龙","TutuTu","3月",1999,"小红书直购","18107332677","线索","",True),
|
||
("3月","qX7oJ6","小龙","EFFIE","3月25日",3598,"微信小店","","线索","newmedia-dianpu-xhs-0-0",False),
|
||
("3月","sSCT22","Tom","JeanneLᴇᴇ🦄","3月4日",3598,"微信小店","","线索","newmedia-dianpu-wxxd-0-0",False),
|
||
("3月","sSCT22","Tom","薇妮姐","3月8日",599,"端内","","线索","",False),
|
||
("3月","sSCT22","Tom","欣","3月7日",599,"端内","","线索","app-active-h5-0-0",False),
|
||
("3月","sSCT22","Bob","Mogu","3月6日",599,"端内","","线索","",False),
|
||
("3月","sSCT22","Bob","L. 一颗夹星糖🌟","3月12日",3598,"小红书-晚柠","","线索","newmedia-daren-xhs-晚柠也是个妈妈了-0",False),
|
||
("3月","sSCT22","Tom","阿雅呀","3月15日",3598,"小红书-晚柠","","线索","newmedia-daren-xhs-晚柠也是个妈妈了-0",False),
|
||
("3月","sSCT22","Tom","Anna","3月4日",1999,"抖音","","线索","newmedia-dianpu-douyin-0-0",True),
|
||
("3月","sSCT22","Bob","Echo Liang","3月15日",1999,"微信小店","","线索","sales-adp-cd-zjf-0",False),
|
||
("3月","sSCT22","Tom","幼兒園高材生🍼","3月23日",3598,"微信小店","13055770067","线索","sales-adp-cd-yy-0",False),
|
||
("3月","sSCT22","Tom","Nancy","3月26日",599,"端内","","线索","",False),
|
||
("3月","sSCT22","Tom","依米","3月30日",3598,"微信小店","","线索","sales-adp-cd-zjf-0",False),
|
||
("3月","sSCT22","Bob","zhouyun","3月6日",1999,"小红书-学霸老王","13588706769","线索","",False),
|
||
("3月","sSCT22","Tom","💗小超人棒棒哒🍭","3月29日",3598,"小红书-晚柠","18630368296","线索","newmedia-daren-xhs-晚柠也是个妈妈了-0",False),
|
||
("3月","sSCT22","Bob","鹿","3月14日",3598,"小红书-晚柠","","线索","",False),
|
||
# === 4月 (15) ===
|
||
("4月","qX7oJ6","小龙","琳溪","4月19日",3598,"微信小店","","线索","",False),
|
||
("4月","qX7oJ6","小龙","小丽","4月26日",599,"端内","","线索","",False),
|
||
("4月","qX7oJ6","小龙","子曦","4月16日",3598,"晚柠-小红书","","线索","",False),
|
||
("4月","qX7oJ6","小龙","Gaᴗao","4月24日",3598,"微信小店","","线索","",False),
|
||
("4月","XqxgjP","吴迪","ཚེ་རིང་མཚོ彩让措","2026/4/26",599,"端内","","无线索","",False),
|
||
("4月","XqxgjP","吴迪","莉筱雅","2026/4/27",1999,"晚柠-小红书","","线索","newmedia-daren-xhs-晚柠也是个妈妈了-0",False),
|
||
("4月","XqxgjP","吴迪","爱吃巧克力","2026/4/24",1999,"微信小店","","无线索","",False),
|
||
("4月","XqxgjP","吴迪","Aa~Jessie💝","2026/4/27",1999,"抖音","","线索","newmedia-dianpu-douyin-0-0",True),
|
||
("4月","XqxgjP","吴迪","🐷","2026/4/26",3598,"宣儿妈妈-小红书","","线索","sales-adp-cd-yy-0",False),
|
||
("4月","XqxgjP","吴迪","🐷","2026/4/26",3598,"宣儿妈妈-小红书","","线索","sales-adp-cd-yy-0",False), # dup
|
||
("4月","sSCT22","Tom","静静是我🍃","4月8日",1999,"小红书-学霸老王","15975769851","线索","partner-actives-0-0-0",False),
|
||
("4月","sSCT22","Bob","胆大鬼","4月8日",3598,"小红书-学霸老王","15262255267","线索","newmedia-daren-xhs-学霸老王讲真话-0",False),
|
||
("4月","sSCT22","Tom","希Cissy-427","4月27日",3598,"微信小店","","线索","",False),
|
||
("4月","sSCT22","Tom","Kerry","4月9日",3598,"微信小店","18328334683","线索","sales-adp-cd-yy-0",False),
|
||
("4月","sSCT22","Tom","SHAN_Q_Q","4月1日",3598,"小红书-官店","","无线索","",True),
|
||
# === 5月 (22) ===
|
||
("5月","qX7oJ6","小龙","Sia","5月9日",599,"微信小店","","线索","",False),
|
||
("5月","XqxgjP","吴迪","璐","2026/5/9",1999,"微信小店","","线索","sales-adp-bj-wd-0",False),
|
||
("5月","XqxgjP","吴迪","^_^","2026/5/16",3598,"晚柠-小红书","","线索","newmedia-daren-xhs-晚柠也是个妈妈了-0",False),
|
||
("5月","XqxgjP","吴迪","🌸白色铃兰🌸","2026/5/17",3598,"抖音","","无线索","",True),
|
||
("5月","XqxgjP","吴迪","咔咔","2026/5/7",3598,"微信小店","","线索","sales-adp-bj-wd-0",False),
|
||
("5月","XqxgjP","吴迪","梦马","2026/5/21",3598,"小红书","","线索","newmedia-dianpu-xhs-0-0",False),
|
||
("5月","XqxgjP","吴迪","梦马","2026/5/21",3598,"小红书","","线索","newmedia-dianpu-xhs-0-0",False), # dup
|
||
("5月","XqxgjP","吴迪","小西瓜","2026/5/30",1999,"微信小店","","无线索","",False),
|
||
("5月","XqxgjP","吴迪","璐","2026/5/9",1999,"微信小店","","线索","sales-adp-bj-wd-0",False), # dup
|
||
("5月","sSCT22","Bob","Yuki-515","5月15日",3598,"小红书-晚柠","","线索","newmedia-daren-xhs-晚柠也是个妈妈了-0",False),
|
||
("5月","sSCT22","Tom","冬-515","5月15日",1999,"小红书-晚柠","","线索","newmedia-daren-xhs-晚柠也是个妈妈了-0",False),
|
||
("5月","sSCT22","Bob","微笑向暖","5月4日",1999,"微信小店","","无线索","",False),
|
||
("5月","sSCT22","Bob","cici-511","5月11日",1999,"微信小店","","线索","sales-adp-cd-xsy-0",False),
|
||
("5月","sSCT22","Tom","🐷-519","5月19日",1999,"小红书-宣儿妈妈","","线索","",False),
|
||
("5月","sSCT22","Bob","妃-516","5月16日",1999,"微信小店","","线索","",False),
|
||
("5月","sSCT22","Tom","黄晔-516","5月16日",3598,"微信小店","","无线索","",False),
|
||
("5月","sSCT22","Tom","朵朵呀！","5月25日",1999,"微信小店","","线索","",False),
|
||
("5月","sSCT22","Tom","雷鸣-414","5月14日",599,"微信小店","","无线索","",False),
|
||
("5月","sSCT22","Tom","毛阿毛🐱-520","5月20日",1999,"小红书-官店","","无线索","",True),
|
||
("5月","sSCT22","Tom","____Miss_y-519","5月19日",599,"微信小店","","线索","",False),
|
||
("5月","sSCT22","Tom","Mandy-526","5月26日",1999,"微信小店","","无线索","",False),
|
||
("5月","sSCT22","Bob","Yoki-529","5月29日",1999,"微信小店","","线索","",False),
|
||
]
|
||
|
||
# Map channel names to key_from patterns
|
||
def channel_to_pattern(channel):
|
||
if "微信小店" in channel:
|
||
return "newmedia-dianpu-wxxd-0-0"
|
||
if "晚柠" in channel or "小红书-晚柠" in channel:
|
||
return "newmedia-daren-xhs-晚柠也是个妈妈了-0"
|
||
if "学霸老王" in channel and "小红书" in channel:
|
||
return "newmedia-daren-xhs-学霸老王讲真话-0"
|
||
if "学霸三人行" in channel:
|
||
return "newmedia-daren-xhs-学霸三人行-0"
|
||
if "学霸老王" in channel and "抖音" in channel:
|
||
return "newmedia-daren-douyin-学霸老王%"
|
||
if "宣儿妈妈" in channel:
|
||
return "newmedia-daren-xhs-宣儿妈妈%"
|
||
if "念妈" in channel:
|
||
return "newmedia-daren%念妈%"
|
||
if "端内" in channel:
|
||
return "app-active-h5-0-0"
|
||
if "抖音" in channel and "直购" not in channel:
|
||
return "newmedia-dianpu-douyin-0-0"
|
||
if "小红书-官店" in channel:
|
||
return "newmedia-dianpu-xhs-0-0"
|
||
if "小红书直购" in channel:
|
||
return "newmedia-dianpu-xhs-0-0"
|
||
if "小红书" in channel:
|
||
return "newmedia-dianpu-xhs-0-0"
|
||
return None
|
||
|
||
# For each gap, try to find matching order in DB
|
||
import os
|
||
PGPASS = "LdfjdjL83h3h3^$&**YGG*"
|
||
|
||
def parse_date(d):
|
||
d = d.replace("日","").replace("月","/").replace(" ","").strip()
|
||
if "/" in d:
|
||
parts = d.split("/")
|
||
if len(parts) < 2 or not parts[1]:
|
||
return None
|
||
m = int(parts[0])
|
||
day = int(parts[1])
|
||
return f"2026-{m:02d}-{day:02d}"
|
||
else:
|
||
return None
|
||
|
||
# Build a set of unique dates to query
|
||
dates = set()
|
||
for g in gaps:
|
||
d = parse_date(g[4])
|
||
if d:
|
||
dates.add(d)
|
||
|
||
# Query all matching orders in one go
|
||
date_conditions = " OR ".join([f"o.pay_success_date::date = '{d}'" for d in sorted(dates)])
|
||
sql = f"""
|
||
SELECT o.id, o.trade_no, o.pay_amount_int, to_char(o.pay_success_date,'YYYY-MM-DD') as pay_date,
|
||
o.key_from, o.order_status, o.account_id, a.tel, a.created_at::date as reg_date
|
||
FROM bi_vala_order o
|
||
JOIN bi_vala_app_account a ON o.account_id = a.id
|
||
WHERE a.status = 1
|
||
AND o.pay_success_date >= '2026-03-01' AND o.pay_success_date < '2026-06-01'
|
||
AND o.order_status IN (3, 4)
|
||
AND o.pay_amount_int IN (59900, 199900, 359800)
|
||
AND ({date_conditions})
|
||
ORDER BY o.pay_success_date, o.pay_amount_int, o.key_from;
|
||
"""
|
||
|
||
result = subprocess.run([
|
||
"psql", "-h", "bj-postgres-16pob4sg.sql.tencentcdb.com",
|
||
"-p", "28591", "-U", "ai_member", "-d", "vala_bi",
|
||
"-c", sql
|
||
], env={**os.environ, "PGPASSWORD": PGPASS}, capture_output=True, text=True, timeout=30)
|
||
|
||
# Parse results
|
||
orders = []
|
||
for line in result.stdout.strip().split("\n")[2:-1]: # skip header and footer
|
||
parts = line.split("|")
|
||
if len(parts) >= 9:
|
||
orders.append({
|
||
"id": parts[0].strip(),
|
||
"trade_no": parts[1].strip(),
|
||
"amount": int(parts[2].strip()) if parts[2].strip() else 0,
|
||
"pay_date": parts[3].strip(),
|
||
"key_from": parts[4].strip(),
|
||
"order_status": parts[5].strip(),
|
||
"account_id": parts[6].strip(),
|
||
"tel": parts[7].strip(),
|
||
"reg_date": parts[8].strip() if len(parts) > 8 else "",
|
||
})
|
||
|
||
print(f"Total orders found: {len(orders)}")
|
||
|
||
# Now match each gap
|
||
for i, g in enumerate(gaps):
|
||
month, src, sales, nick, date_str, amount, channel, phone, clue, keyfrom, is_direct = g
|
||
idx = i + 1
|
||
d = parse_date(date_str)
|
||
amount_int = amount * 100
|
||
|
||
# If phone provided, match by phone
|
||
if phone:
|
||
matched = [o for o in orders if o["tel"].replace("*","")[-4:] == phone[-4:] and o["pay_date"] == d and o["amount"] == amount_int] if d else []
|
||
if not matched and d:
|
||
matched = [o for o in orders if o["tel"].replace("*","")[-4:] == phone[-4:]]
|
||
else:
|
||
# Match by date + amount + channel pattern
|
||
pat = keyfrom if keyfrom else channel_to_pattern(channel)
|
||
if d and pat:
|
||
if "%" in pat:
|
||
matched = [o for o in orders if o["pay_date"] == d and o["amount"] == amount_int and pat.replace("%","") in o["key_from"]]
|
||
else:
|
||
matched = [o for o in orders if o["pay_date"] == d and o["amount"] == amount_int and o["key_from"] == pat]
|
||
elif d:
|
||
matched = [o for o in orders if o["pay_date"] == d and o["amount"] == amount_int]
|
||
else:
|
||
matched = []
|
||
|
||
status = "有" if matched else "无"
|
||
print(f"{idx}. [{month}] {sales} {nick} | {date_str} ¥{amount} {channel} | {status} | {len(matched)} matches")
|
||
if matched:
|
||
for m in matched:
|
||
print(f" -> order_id={m['id']} {m['pay_date']} ¥{m['amount']/100} {m['key_from'][:60]} status={m['order_status']} tel={m['tel']} reg={m['reg_date']}")
|