pythonTest/查询手机号.py

import requests
import json
from datetime import datetime, timezone, timedelta
import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import pandas as pd
import os
from threading import Thread


def parse_response(response_text):
    """
    从飞书多维表格API响应中提取指定字段数据
    """
    try:
        # 1. 解析外层JSON
        if isinstance(response_text, dict):
            outer_data = response_text
        else:
            outer_data = json.loads(response_text)
        
        # 2. 获取data中的records字符串
        records_str = outer_data.get('data', {}).get('records', '')
        if not records_str:
            return {
                '批量数据编码': None,
                '有顾问跟进': None,
                '批量创建时间': None,
                '批量创建人员': None,
                'error': 'records字段为空'
            }
        
        # 3. 解析内层records JSON
        records_data = json.loads(records_str)
        
        # 4. 获取第一条记录的key
        record_keys = list(records_data.get('recordMap', {}).keys())
        if not record_keys:
            return {
                '批量数据编码': None,
                '有顾问跟进': None,
                '批量创建时间': None,
                '批量创建人员': None,
                'error': 'recordMap为空'
            }
        first_record_key = record_keys[0]
        record = records_data['recordMap'][first_record_key]
        
        # 5. 获取fieldMap - 在 formulaInfo 里面！
        formula_info = records_data.get('formulaInfo', {})
        field_map = formula_info.get('fieldMap', {})
        
        # 6. 提取批量数据编码
        batch_code = None
        if 'fldbQDk5b7' in record:
            values = record['fldbQDk5b7'].get('value', [])
            if values and len(values) > 0:
                batch_code = values[0].get('number')
        
        # 7. 提取有顾问跟进
        has_advisor = None
        if 'fldSiIb9nZ' in field_map:
            field_value = field_map['fldSiIb9nZ'].get(first_record_key, {}).get('value')
            if field_value:
                try:
                    data = json.loads(field_value)
                    if data.get('data') and len(data['data']) > 0:
                        has_advisor = data['data'][0]
                except:
                    pass
        
        # 8. 提取批量创建时间
        batch_create_time = None
        if 'fldlzQO4tZ' in field_map:
            field_value = field_map['fldlzQO4tZ'].get(first_record_key, {}).get('value')
            if field_value:
                try:
                    data = json.loads(field_value)
                    if data.get('data') and len(data['data']) > 0:
                        timestamp_data = data['data'][0]
                        if isinstance(timestamp_data, dict) and 'value' in timestamp_data:
                            timestamp_ms = timestamp_data['value']
                            if timestamp_ms and isinstance(timestamp_ms, (int, float)):
                                if timestamp_ms > 1000000000000:
                                    timestamp_sec = timestamp_ms / 1000
                                else:
                                    timestamp_sec = timestamp_ms
                                dt = datetime.fromtimestamp(timestamp_sec, tz=timezone(timedelta(hours=8)))
                                batch_create_time = dt.strftime('%Y-%m-%d %H:%M:%S')
                except:
                    pass
        
        # 9. 提取批量创建人员
        batch_creator = None
        if 'fldm8odlu2' in field_map:
            field_value = field_map['fldm8odlu2'].get(first_record_key, {}).get('value')
            if field_value:
                try:
                    data = json.loads(field_value)
                    if data.get('data') and len(data['data']) > 0:
                        batch_creator = data['data'][0]
                except:
                    pass
        
        return {
            '批量数据编码': batch_code,
            '有顾问跟进': has_advisor,
            '批量创建时间': batch_create_time,
            '批量创建人员': batch_creator
        }
        
    except Exception as e:
        return {
            '批量数据编码': None,
            '有顾问跟进': None,
            '批量创建时间': None,
            '批量创建人员': None,
            'error': f'处理错误: {str(e)}'
        }


def search_single_phone(keyword):
    """
    查询单个手机号
    """
    try:
        payload = f'''{{\"ondemandVer\":2,\"localViewConfig\":{{\"filter\":null,\"sort\":\"[{{\\\"fieldId\\\":\\\"fldSiIb9nZ\\\",\\\"desc\\\":false}},{{\\\"fieldId\\\":\\\"fldbQDk5b7\\\",\\\"desc\\\":true}}]\",\"group\":\"[]\"}},\"ignoreRecordsNum\":false,\"groupByInfoList\":\"[{{\\\"id\\\":0,\\\"offset\\\":0,\\\"limit\\\":200}}]\",\"globalSearch\":{{\"keyword\":\"{keyword}\",\"fieldIDs\":[\"fldbQDk5b7\",\"fldSiIb9nZ\",\"fldlzQO4tZ\",\"fldm8odlu2\",\"fld9KcFahs\",\"fldkZzg0y4\",\"fldFL9eHjq\",\"fldq9cmDqw\",\"fld0af1E3B\",\"fldbsO0apx\",\"fldlbKssB8\",\"fldczSrVSQ\"]}},\"baseEngineJointRev\":13452036,\"withExtra\":{{\"groupInfos\":true}},\"removeFmlExtra\":true}}'''
        
        url = "https://xhd.feishu.cn/space/api/v1/bitable/W83XbyQuPajhc8s4Ghsc5xF9nTb/ondemand/records/search"
        querystring = {"tableID":"tblXQQeIwMqSFSME","viewID":"vewETuuNwn","tableRev":"1871920","jointRev":"13452036"}
        
        headers = {
            "accept": "application/json, text/plain, */*",
            "accept-language": "zh-CN,zh;q=0.9",
            "content-type": "application/json",
            "context": "request_id=2YHprDoRiCNb-ee5749b7ac0c1b41e6d5e9c37292b9f07330118d;os=windows;app_version=1.0.19.9369;os_version=10;platform=web",
            "doc-biz": "Lark",
            "doc-os": "windows",
            "doc-platform": "web",
            "f-version": "docs-8-19-1787124320993",
            "origin": "https://xhd.feishu.cn",
            "priority": "u=1, i",
            "referer": "https://xhd.feishu.cn/base/W83XbyQuPajhc8s4Ghsc5xF9nTb?table=tblXQQeIwMqSFSME&view=vewETuuNwn",
            "request-id": "2YHprDoRiCNb-ee5749b7ac0c1b41e6d5e9c37292b9f07330118d",
            "rpc-persist-lane-c-lark-uid": "0",
            "sec-fetch-dest": "empty",
            "sec-fetch-mode": "cors",
            "sec-fetch-site": "same-origin",
            "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36",
            "x-csrftoken": "e58ad7674c941341cc4b663a42eed3390a50b387-1786763321",
            "x-lgw-app-id": "1161",
            "x-lgw-os-type": "1",
            "x-lgw-terminal-type": "2",
            "x-lsc-bizid": "2",
            "x-lsc-terminal": "web",
            "x-lsc-version": "1",
            "x-request-id": "2YHprDoRiCNb-ee5749b7ac0c1b41e6d5e9c37292b9f07330118d",
            "x-tt-logid": "02178759464265700000000000000000000ffff1705590efc4048",
            "x-tt-trace-id": "2YHprDoRiCNb-ee5749b7ac0c1b41e6d5e9c37292b9f07330118d",
            "Cookie": "__tea__ug__uid=4144561781538482924; _gcl_au=1.1.775810441.1781538483; passport_web_did=7651823325329411260; passport_trace_id=7651823325353757894; QXV0aHpDb250ZXh0=ba539bcfe4a042c8bc1e99d4d03add38; lang=zh; et=c0c63f4480fdf14d7e6e9a21bab2b7e1; ccm_cdn_host=//lf-package-cn.feishucdn.com/obj/feishu-static; _uetvid=9877555068d111f1992d03b61d301033; is_anonymous_session=; i18n_locale=zh-CN; i18n_locale=zh-CN; locale=zh-CN; s_v_web_id=verify_mqg251ox_Tp4ofGaR_NCf4_4zII_8Ad3_c3HxGb4PXIuN; Hm_lvt_a79616d9322d81f12a92402ac6ae32ea=1781538483,1781579751,1783595682; _csrf_token=e58ad7674c941341cc4b663a42eed3390a50b387-1786763321; js_version=1; _tea_utm_cache_8608=undefined; _tea_utm_cache_323044=undefined; _gid=GA1.2.1635584096.1787486227; _uuid_hera_ab_path_1=7677197560582245583; _ga=GA1.1.692261332.1781538484; landing_url=https://accounts.feishu.cn/accounts/page/login?app_id=2&no_trap=1&query_scope=all&redirect_uri=https%253A%252F%252Fxhd.feishu.cn%252Fapp%252FZaEWbUvjeaYgS6sof3ycpikNnwh%253Ffrom%253Dspace_home_recent%2526login_redirect_times%253D1%2526pre_pathname%253D%25252Fbase%2526previous_navigation_time%253D1787535012983%2526skip_html_cache%253D1; session=XN0YXJ0-2b4ha893-8dde-44b0-9da1-a45f033de7f3-WVuZA; session_list=XN0YXJ0-2b4ha893-8dde-44b0-9da1-a45f033de7f3-WVuZA; login_recently=1; ssrLocalKey=3b07d7d4a86ab7d7f6bfd714f3c0f3596cba82749fa9f4cc5020bfff57eaea92; msToken=3MPLenLPXfPJet02DA1uWTNf0EIiJic7Xy2mllIUmUa0vdZ1A28Iego52sEo545x7QFQX6aPwMIeNRPg2S_87YXmt6tpxndT2l5w8IXG6zerU6YqRV6Em7Kn7XZ878fm2DJmKTrc6VPvvW7XTDK5OzbY6LQ_4KIE88kVLTS38TFp; _ga_VPYRHN104D=GS2.1.s1787591886$o13$g1$t1787592091$j60$l0$h0; bitable_tableId_viewId_history=%7B%22CkXLbknSiaaRtCsbdwgciiZunge%22%3A%7B%22tableId%22%3A%22blksKA7e5zhv4YzB%22%2C%22viewId%22%3A%22%22%7D%2C%22VFCbbl0eTaHuZZsPGo2cA677nrf%22%3A%7B%22tableId%22%3A%22tbl1N9m2h1YAH52P%22%2C%22viewId%22%3A%22vew4RfmKci%22%7D%2C%22W83XbyQuPajhc8s4Ghsc5xF9nTb%22%3A%7B%22tableId%22%3A%22tblXQQeIwMqSFSME%22%2C%22viewId%22%3A%22vewETuuNwn%22%7D%7D; site_env=pre=0; passport_app_access_token=eyJhbGciOiJFUzI1NiIsInR5cCI6IkpXVCJ9.eyJleHAiOjE3ODc2MzU0NTAsInVuaXQiOiJldV9uYyIsInJhdyI6eyJtX2FjY2Vzc19pbmZvIjp7IjIiOnsiaWF0IjoxNzg3NTkxMzA2LCJhY2Nlc3MiOnRydWV9LCIyOSI6eyJpYXQiOjE3ODc1OTIyNTAsImFjY2VzcyI6dHJ1ZX19LCJzdW0iOiIyM2JkZjExYmFkODA4NjdhYzkzZjM2ZjU4OTc5MmNkZWM3NjBmOTI4Yjg5ZDNiMmFhZGNmYWE0ODRkNzk5MjRjIn19.xuvuwUjW-8fZ-hF6DqjzyjM3AeVb9wLx1dq_kTLCX0jUMMrsW3mHM7xBRjDc9ufTHdM-MZdl5rxnfw9dltLEcg; swp_csrf_token=5bf944ac-c7ef-40db-b815-ef07e0115c98; t_beda37=23bdf7a0fd3e381cc826da49adb45ab435050b508432c3e4ecd7aeb6243780a0; sl_session=eyJhbGciOiJFUzI1NiIsInR5cCI6IkpXVCJ9.eyJleHAiOjE3ODc2MzY2NjMsInVuaXQiOiJldV9uYyIsInJhdyI6eyJtZXRhIjoiQVdvYzIrdU1BNHZSYUhkQjl4N2lRQUpxTUx6RjlrN012R293dk1YMlRzeThhakM5WXdRQURMSUNLZ0VBUVVGQlFVRkJRVUZCUVVKeGFUVTNVWE5SUkV3eVVUMDkiLCJzdW0iOiIyM2JkZjExYmFkODA4NjdhYzkzZjM2ZjU4OTc5MmNkZWM3NjBmOTI4Yjg5ZDNiMmFhZGNmYWE0ODRkNzk5MjRjIiwibG9jIjoiemhfY24iLCJhcGMiOiJSZWxlYXNlIiwiaWF0IjoxNzg3NTkzNDYzLCJzYWMiOnsiVXNlclN0YWZmU3RhdHVzIjoiMSIsIlVzZXJUeXBlIjoiNDIifSwibG9kIjpudWxsLCJjbmYiOnsiamt0Ijoic3VWaTdnNDNJX0lUb0w2ZGo5QXhEWnBacUhkYldzNWJaaVhvV0s4akZrdyJ9LCJucyI6ImxhcmsiLCJuc191aWQiOiI3NjQ2MjI4MDcyMDgxMDk5NzI5IiwibnNfdGlkIjoiNzUyNzU1NzgzMTgwMzk0NDk2MiIsIm90IjoxLCJjdCI6MTc4NzUzNTA1NywicnQiOjE3ODc1MzUwNTcsInRjcyI6MiwidGJ0IjowfX0.4uYKYGFo9Y-SuLsPofFNvGd7K849RbFdgd62pmoU6npJEWpubrdO7cJyf-yZ8f9-jF5NzFPCm-zJ1RYvYr9jyQ",
            "Connection": "keep-alive"
        }
        
        response = requests.post(url, data=payload, headers=headers, params=querystring)
        result = parse_response(response.text)
        
        # 映射显示名称
        advisor_map = {
            'optXaXhoOh': '有顾问跟进',
            'optJdspBAs': '无顾问跟进'
        }
        
        if result.get('error'):
            return {
                '手机号': keyword,
                '批量数据编码': '查询失败',
                '有顾问跟进': result['error'],
                '批量创建时间': '--',
                '批量创建人员': '--'
            }
        else:
            return {
                '手机号': keyword,
                '批量数据编码': result.get('批量数据编码') or '无数据',
                '有顾问跟进': advisor_map.get(result.get('有顾问跟进'), result.get('有顾问跟进') or '未知'),
                '批量创建时间': result.get('批量创建时间') or '无数据',
                '批量创建人员': result.get('批量创建人员') or '无数据'
            }
            
    except Exception as e:
        return {
            '手机号': keyword,
            '批量数据编码': '请求失败',
            '有顾问跟进': str(e),
            '批量创建时间': '--',
            '批量创建人员': '--'
        }


def batch_search():
    """批量查询"""
    # 获取输入内容
    text_content = text_input.get("1.0", tk.END).strip()
    if not text_content:
        messagebox.showwarning("提示", "请输入要查询的手机号！")
        return
    
    # 解析手机号（支持换行、空格、逗号、分号分隔）
    phones = []
    for sep in ['\n', '，', ',', '、', ';', '；', ' ']:
        if sep in text_content:
            phones = [p.strip() for p in text_content.split(sep) if p.strip()]
            break
    if not phones:
        phones = [text_content.strip()]
    
    # 去重并过滤空值
    phones = list(set([p for p in phones if p]))
    
    if not phones:
        messagebox.showwarning("提示", "未识别到有效的手机号！")
        return
    
    # 确认批量查询
    if len(phones) > 10:
        if not messagebox.askyesno("确认", f"即将查询 {len(phones)} 个手机号，\n批量查询可能需要一些时间，是否继续？"):
            return
    
    # 禁用按钮
    btn_batch_search.config(state='disabled')
    btn_batch_search.config(text='查询中...')
    btn_export.config(state='disabled')
    
    # 清空表格
    tree.delete(*tree.get_children())
    
    # 更新状态
    label_status.config(text=f"正在查询 {len(phones)} 个手机号...", foreground='blue')
    root.update()
    
    # 在新线程中执行查询
    def do_search():
        try:
            results = []
            total = len(phones)
            
            for i, phone in enumerate(phones):
                # 更新状态
                root.after(0, lambda p=phone, idx=i, total=total: 
                          label_status.config(text=f"正在查询 ({idx+1}/{total}): {p}", foreground='blue'))
                root.update()
                
                # 查询
                result = search_single_phone(phone)
                results.append(result)
                
                # 显示在表格中
                root.after(0, lambda r=result: 
                          tree.insert('', 'end', values=(
                              r['手机号'],
                              r['批量数据编码'],
                              r['有顾问跟进'],
                              r['批量创建时间'],
                              r['批量创建人员']
                          )))
                root.update()
            
            # 保存结果到内存，供导出使用
            global last_results
            last_results = results
            
            # 更新状态
            root.after(0, lambda: label_status.config(
                text=f"查询完成！共查询 {len(results)} 个手机号，成功 {sum(1 for r in results if r['批量数据编码'] != '查询失败' and r['批量数据编码'] != '请求失败')} 个",
                foreground='green'))
            root.after(0, lambda: btn_export.config(state='normal'))
            
        except Exception as e:
            root.after(0, lambda: messagebox.showerror("错误", f"批量查询失败: {str(e)}"))
        finally:
            root.after(0, lambda: btn_batch_search.config(state='normal'))
            root.after(0, lambda: btn_batch_search.config(text='批量查询'))
    
    Thread(target=do_search, daemon=True).start()


def export_to_excel():
    """导出结果到Excel"""
    if not tree.get_children():
        messagebox.showwarning("提示", "没有数据可导出，请先执行查询！")
        return
    
    # 获取表格数据
    data = []
    for item in tree.get_children():
        values = tree.item(item)['values']
        data.append({
            '手机号': values[0],
            '批量数据编码': values[1],
            '有顾问跟进': values[2],
            '批量创建时间': values[3],
            '批量创建人员': values[4]
        })
    
    if not data:
        messagebox.showwarning("提示", "没有数据可导出！")
        return
    
    # 选择保存位置
    file_path = filedialog.asksaveasfilename(
        defaultextension=".xlsx",
        filetypes=[("Excel文件", "*.xlsx"), ("CSV文件", "*.csv")],
        title="保存查询结果",
        initialfile=f"查询结果_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
    )
    
    if not file_path:
        return
    
    try:
        df = pd.DataFrame(data)
        df.to_excel(file_path, index=False, engine='openpyxl')
        messagebox.showinfo("成功", f"数据已导出到：\n{file_path}")
        label_status.config(text=f"已导出到：{os.path.basename(file_path)}", foreground='green')
    except Exception as e:
        messagebox.showerror("错误", f"导出失败: {str(e)}")


def clear_all():
    """清空所有"""
    text_input.delete("1.0", tk.END)
    tree.delete(*tree.get_children())
    label_status.config(text="已清空", foreground='gray')
    btn_export.config(state='disabled')


# 创建GUI窗口
root = tk.Tk()
root.title("飞书多维表格批量查询工具")
root.geometry("900x650")
root.resizable(False, False)

# 设置样式
style = ttk.Style()
style.theme_use('clam')

# 顶部：输入区域
frame_top = ttk.LabelFrame(root, text="输入手机号（支持批量，用换行/逗号/空格分隔）", padding="10")
frame_top.pack(fill='x', padx=10, pady=5)

text_input = tk.Text(frame_top, font=('微软雅黑', 10), height=6, wrap='word')
text_input.pack(fill='x')

# 输入提示
hint_frame = ttk.Frame(frame_top)
hint_frame.pack(fill='x', pady=(5, 0))
ttk.Label(hint_frame, text="提示：每行输入一个手机号，或使用逗号、空格分隔", font=('微软雅黑', 9), foreground='gray').pack(side='left')

# 按钮区域
btn_frame = ttk.Frame(frame_top)
btn_frame.pack(fill='x', pady=(5, 0))

btn_batch_search = ttk.Button(btn_frame, text="批量查询", command=batch_search, width=12)
btn_batch_search.pack(side='left', padx=(0, 10))

btn_clear = ttk.Button(btn_frame, text="清空", command=clear_all, width=10)
btn_clear.pack(side='left', padx=(0, 10))

btn_export = ttk.Button(btn_frame, text="导出Excel", command=export_to_excel, width=12, state='disabled')
btn_export.pack(side='left')

# 中间：结果显示表格
frame_table = ttk.LabelFrame(root, text="查询结果", padding="10")
frame_table.pack(fill='both', expand=True, padx=10, pady=5)

# 创建Treeview（增加手机号列）
columns = ('手机号', '批量数据编码', '有顾问跟进', '批量创建时间', '批量创建人员')
tree = ttk.Treeview(frame_table, columns=columns, show='headings', height=12)

# 设置列标题和宽度
tree.heading('手机号', text='手机号')
tree.heading('批量数据编码', text='批量数据编码')
tree.heading('有顾问跟进', text='有顾问跟进')
tree.heading('批量创建时间', text='批量创建时间')
tree.heading('批量创建人员', text='批量创建人员')

tree.column('手机号', width=150, anchor='center')
tree.column('批量数据编码', width=180, anchor='center')
tree.column('有顾问跟进', width=130, anchor='center')
tree.column('批量创建时间', width=180, anchor='center')
tree.column('批量创建人员', width=150, anchor='center')

# 添加滚动条
scrollbar = ttk.Scrollbar(frame_table, orient='vertical', command=tree.yview)
tree.configure(yscrollcommand=scrollbar.set)

tree.pack(side='left', fill='both', expand=True)
scrollbar.pack(side='right', fill='y')

# 底部：状态栏
frame_bottom = ttk.Frame(root, padding="5")
frame_bottom.pack(fill='x')

label_status = ttk.Label(frame_bottom, text="请输入手机号，支持批量查询", font=('微软雅黑', 9), foreground='gray')
label_status.pack(side='left')

# 存储最近一次查询结果（用于导出）
last_results = []

# 运行主循环
root.mainloop()