pythonTest/查询手机号.py

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_records_from_text(json_text):
    """
    从粘贴的JSON文本中解析记录数据
    """
    try:
        # 1. 解析外层JSON
        if isinstance(json_text, dict):
            data = json_text
        else:
            data = json.loads(json_text)
        
        # 获取records字段
        records_str = data.get('data', {}).get('records', '')
        if not records_str:
            return {'error': 'records字段为空', 'records': []}
        
        # 解析内层records JSON
        records_data = json.loads(records_str)
        record_map = records_data.get('recordMap', {})
        
        if not record_map:
            return {'error': 'recordMap为空', 'records': []}
        
        # 获取fieldMap
        formula_info = records_data.get('formulaInfo', {})
        field_map = formula_info.get('fieldMap', {})
        
        # 解析所有记录
        results = []
        for record_key, record in record_map.items():
            # 提取手机号
            phone = None
            if 'fld0af1E3B' in record:
                phone_data = record['fld0af1E3B'].get('value', {})
                if phone_data and isinstance(phone_data, dict):
                    phone = phone_data.get('fullPhoneNum')
            
            # 提取批量数据编码
            batch_code = None
            if 'fldbQDk5b7' in record:
                values = record['fldbQDk5b7'].get('value', [])
                if values and len(values) > 0:
                    batch_code = values[0].get('number')
            
            # 提取有顾问跟进
            has_advisor = None
            if 'fldSiIb9nZ' in field_map:
                field_value = field_map['fldSiIb9nZ'].get(record_key, {}).get('value')
                if field_value:
                    try:
                        field_data = json.loads(field_value)
                        if field_data.get('data') and len(field_data['data']) > 0:
                            has_advisor = field_data['data'][0]
                    except:
                        pass
            
            # 提取批量创建时间
            batch_create_time = None
            if 'fldlzQO4tZ' in field_map:
                field_value = field_map['fldlzQO4tZ'].get(record_key, {}).get('value')
                if field_value:
                    try:
                        field_data = json.loads(field_value)
                        if field_data.get('data') and len(field_data['data']) > 0:
                            timestamp_data = field_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
            
            # 提取批量创建人员
            batch_creator = None
            if 'fldm8odlu2' in field_map:
                field_value = field_map['fldm8odlu2'].get(record_key, {}).get('value')
                if field_value:
                    try:
                        field_data = json.loads(field_value)
                        if field_data.get('data') and len(field_data['data']) > 0:
                            batch_creator = field_data['data'][0]
                    except:
                        pass
            
            # 提取创建人姓名（从recordMeta）
            created_user = None
            if 'recordMeta' in records_data:
                meta = records_data['recordMeta'].get(record_key, {}).get('recMeta', {})
                if 'createdUser' in meta:
                    # 这里存储的是用户ID，可以尝试从userMap获取姓名
                    user_map = records_data.get('userMap', {})
                    user_info = user_map.get(meta['createdUser'], {})
                    if user_info:
                        created_user = user_info.get('name') or user_info.get('enName')
                    if not created_user:
                        created_user = meta['createdUser']
            
            results.append({
                '手机号': phone or '无数据',
                '批量数据编码': batch_code or '无数据',
                '有顾问跟进': has_advisor or '未知',
                '批量创建时间': batch_create_time or '无数据',
                '批量创建人员': batch_creator or '无数据',
                '创建人': created_user or '无数据'
            })
        
        # 映射显示名称
        advisor_map = {
            'optXaXhoOh': '有顾问跟进',
            'optJdspBAs': '无顾问跟进'
        }
        
        for record in results:
            if record['有顾问跟进'] in advisor_map:
                record['有顾问跟进'] = advisor_map[record['有顾问跟进']]
        
        return {'error': None, 'records': results, 'count': len(results)}
        
    except json.JSONDecodeError as e:
        return {'error': f'JSON解析错误: {str(e)}', 'records': []}
    except Exception as e:
        return {'error': f'处理错误: {str(e)}', 'records': []}


def parse_and_display():
    """解析粘贴的JSON数据并显示在表格中"""
    json_text = text_input.get("1.0", tk.END).strip()
    if not json_text:
        messagebox.showwarning("提示", "请粘贴JSON数据！")
        return
    
    # 禁用按钮
    btn_parse.config(state='disabled')
    btn_parse.config(text='解析中...')
    btn_export.config(state='disabled')
    
    # 清空表格
    tree.delete(*tree.get_children())
    
    # 更新状态
    label_status.config(text="正在解析数据...", foreground='blue')
    root.update()
    
    def do_parse():
        try:
            result = parse_records_from_text(json_text)
            
            if result.get('error'):
                root.after(0, lambda: messagebox.showerror("错误", f"解析失败: {result['error']}"))
                root.after(0, lambda: label_status.config(text="解析失败", foreground='red'))
            else:
                records = result.get('records', [])
                count = result.get('count', 0)
                
                # 显示在表格中
                for record in records:
                    root.after(0, lambda r=record: 
                              tree.insert('', 'end', values=(
                                  r['手机号'],
                                  r['批量数据编码'],
                                  r['有顾问跟进'],
                                  r['批量创建时间'],
                                  r['批量创建人员']
                              )))
                    root.update()
                
                # 保存结果到内存，供导出使用
                global last_results
                last_results = records
                
                # 更新状态
                root.after(0, lambda: label_status.config(
                    text=f"解析完成！共 {count} 条记录",
                    foreground='green'))
                root.after(0, lambda: btn_export.config(state='normal'))
                
                if count == 0:
                    root.after(0, lambda: messagebox.showinfo("提示", "未解析到任何记录"))
            
        except Exception as e:
            root.after(0, lambda: messagebox.showerror("错误", f"解析失败: {str(e)}"))
            root.after(0, lambda: label_status.config(text="解析失败", foreground='red'))
        finally:
            root.after(0, lambda: btn_parse.config(state='normal'))
            root.after(0, lambda: btn_parse.config(text='解析数据'))
    
    Thread(target=do_parse, 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')
    global last_results
    last_results = []


def load_example():
    """加载示例数据"""
    example_text = """{"code":0,"msg":"","data":{"records":"{\\"tableID\\":\\"tblXQQeIwMqSFSME\\",\\"recordMap\\":{\\"recvtyUtg2C0Xl\\":{\\"fld0af1E3B\\":{\\"value\\":{\\"fullPhoneNum\\":\\"18616756877\\"}},\\"fldbQDk5b7\\":{\\"value\\":[{\\"number\\":\\"SHPLSJ202608281532312\\"}]}},\\"recvtyUtg2Z6yf\\":{\\"fld0af1E3B\\":{\\"value\\":{\\"fullPhoneNum\\":\\"13918255747\\"}},\\"fldbQDk5b7\\":{\\"value\\":[{\\"number\\":\\"SHPLSJ202608281532385\\"}]}}},\\"formulaInfo\\":{\\"fieldMap\\":{\\"fldSiIb9nZ\\":{\\"recvtyUtg2C0Xl\\":{\\"value\\":\\"{\\\\\\"bus_type\\\\\\":[3],\\\\\\"data\\\\\\":[\\\\\\"optJdspBAs\\\\\\"]}\\"},\\"recvtyUtg2Z6yf\\":{\\"value\\":\\"{\\\\\\"bus_type\\\\\\":[3],\\\\\\"data\\\\\\":[\\\\\\"optJdspBAs\\\\\\"]}\\"}},\\"fldm8odlu2\\":{\\"recvtyUtg2C0Xl\\":{\\"value\\":\\"{\\\\\\"bus_type\\\\\\":[201],\\\\\\"data\\\\\\":[\\\\\\"/\\\\\\"]}\\"},\\"recvtyUtg2Z6yf\\":{\\"value\\":\\"{\\\\\\"bus_type\\\\\\":[201],\\\\\\"data\\\\\\":[\\\\\\"/\\\\\\"]}\\"}},\\"fldlzQO4tZ\\":{\\"recvtyUtg2C0Xl\\":{\\"value\\":\\"{\\\\\\"bus_type\\\\\\":[5],\\\\\\"data\\\\\\":[null]}\\"},\\"recvtyUtg2Z6yf\\":{\\"value\\":\\"{\\\\\\"bus_type\\\\\\":[5],\\\\\\"data\\\\\\":[{\\\\\\"value\\\\\\":1787082110000}]}\\"}}}},\\"recordMeta\\":{\\"recvtyUtg2C0Xl\\":{\\"recMeta\\":{\\"createdUser\\":\\"7646228072081099729\\"}},\\"recvtyUtg2Z6yf\\":{\\"recMeta\\":{\\"createdUser\\":\\"7646228072081099729\\"}}}}}"}}"""
    text_input.delete("1.0", tk.END)
    text_input.insert("1.0", example_text)
    label_status.config(text="已加载示例数据，点击「解析数据」查看结果", foreground='blue')


# 创建GUI窗口
root = tk.Tk()
root.title("飞书JSON数据解析工具")
root.geometry("950x700")
root.resizable(False, False)

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

# 顶部：输入区域
frame_top = ttk.LabelFrame(root, text="粘贴飞书API返回的JSON数据", padding="10")
frame_top.pack(fill='x', padx=10, pady=5)

text_input = tk.Text(frame_top, font=('Consolas', 9), height=10, wrap='none')
text_input.pack(fill='x', expand=True)

# 添加水平滚动条
scrollbar_h = ttk.Scrollbar(frame_top, orient='horizontal', command=text_input.xview)
text_input.configure(xscrollcommand=scrollbar_h.set)
scrollbar_h.pack(fill='x')

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

ttk.Label(hint_frame, text="提示：粘贴完整的API返回JSON（包含data.records字段）", 
          font=('微软雅黑', 9), foreground='gray').pack(side='left')

btn_frame = ttk.Frame(frame_top)
btn_frame.pack(fill='x', pady=(5, 0))

btn_parse = ttk.Button(btn_frame, text="解析数据", command=parse_and_display, width=12)
btn_parse.pack(side='left', padx=(0, 10))

btn_example = ttk.Button(btn_frame, text="加载示例", command=load_example, width=10)
btn_example.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=15)

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

tree.column('手机号', width=150, anchor='center')
tree.column('批量数据编码', width=200, anchor='center')
tree.column('有顾问跟进', width=150, anchor='center')
tree.column('批量创建时间', width=180, anchor='center')
tree.column('批量创建人员', width=180, 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="请粘贴JSON数据，点击「解析数据」", font=('微软雅黑', 9), foreground='gray')
label_status.pack(side='left')

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

# 运行主循环
root.mainloop()