rijizhang/main.py

import pandas as pd
import tkinter as tk
from tkinter import ttk, filedialog, messagebox
from datetime import datetime
import os
from openpyxl.styles import Font

# -------------------------- 全局配置 --------------------------
# 定义参数表的sheet名称（根据实际情况调整，若不同需修改）
ACCOUNT_PARAM_SHEET = "账户信息参数"  # 参数表中账户信息的sheet名
CATEGORY_PARAM_SHEET = "收支分类参数"  # 参数表中收支分类的sheet名
# 固定匹配的对手户名（结算收支）
SETTLEMENT_COMPANIES = ["成都合煦商贸有限公司", "四川泗讯科技有限公司"]

# -------------------------- 核心数据处理函数 --------------------------
def load_parameter_table(param_file_path):
    """加载参数表，返回账户信息和收支分类"""
    try:
        # 加载账户信息参数（列名在第1行，所以使用header=1）
        account_params = pd.read_excel(param_file_path, sheet_name=ACCOUNT_PARAM_SHEET, header=1)
        # 加载收支分类参数（列名在第1行，所以使用header=1）
        category_params = pd.read_excel(param_file_path, sheet_name=CATEGORY_PARAM_SHEET, header=1)
        return account_params, category_params
    except Exception as e:
        messagebox.showerror("错误", f"加载参数表失败：{str(e)}")
        return None, None

def parse_ccb_flow(flow_file_path):
    """解析建行流水文件，返回标准化的流水数据"""
    try:
        # 读取建行流水（适配xls格式，列名在第9行）
        flow_df = pd.read_excel(flow_file_path, dtype=str, header=9)  # 先以字符串读取避免格式丢失
        
        # 去除列名中的空格
        flow_df.columns = flow_df.columns.str.strip()
        
        # 标准化字段名（根据建行流水实际列名调整，以下是常见建行流水列名，需核对）
        # 你可根据实际流水列名修改key值
        field_mapping = {
            "交易时间": "交易时间",
            "对方户名": "对方户名",
            "摘要": "摘要",
            "贷方发生额/元(收入)": "收入",
            "借方发生额/元(支取)": "支出"
        }
        
        # 检查必要列是否存在
        missing_fields = [k for k in field_mapping.keys() if k not in flow_df.columns]
        if missing_fields:
            messagebox.showerror("错误", f"建行流水缺少必要列：{missing_fields}")
            return None
        
        # 保留所有需要的列，并重命名部分列
        required_columns = ["交易时间", "对方户名", "摘要", "贷方发生额/元(收入)", "借方发生额/元(支取)", "对方开户机构", "备注", "余额"]
        standard_flow = flow_df[required_columns].rename(columns=field_mapping)
        
        # 处理空值和格式
        standard_flow["收入"] = pd.to_numeric(standard_flow["收入"], errors='coerce').fillna(0)
        standard_flow["支出"] = pd.to_numeric(standard_flow["支出"], errors='coerce').fillna(0)
        
        return standard_flow
    except Exception as e:
        messagebox.showerror("错误", f"解析建行流水失败：{str(e)}")
        return None

def parse_boc_flow(flow_file_path):
    """解析中国银行流水文件，返回标准化的流水数据"""
    try:
        # 读取中国银行流水（列名在第8行）
        flow_df = pd.read_excel(flow_file_path, dtype=str, header=8)
        
        # 去除列名中的空格
        flow_df.columns = flow_df.columns.str.strip()
        
        # 检查必要列是否存在
        required_columns = [
            "交易类型[ Transaction Type ]",
            "交易日期[ Transaction Date ]",
            "交易时间[ Transaction time ]",
            "交易金额[ Trade Amount ]",
            "交易后余额[ After-transaction balance ]",
            "摘要[ Reference ]",
            "收款人名称[ Payee's Name ]",
            "收款人开户行名[ Beneficiary account bank ]",
            "付款人名称[ Payer's Name ]",
            "付款人开户行名[ Payer account bank ]"
        ]
        
        missing_fields = [k for k in required_columns if k not in flow_df.columns]
        if missing_fields:
            messagebox.showerror("错误", f"中国银行流水缺少必要列：{missing_fields}")
            return None
        
        # 构建标准化流水数据
        standard_flow = []
        for _, row in flow_df.iterrows():
            transaction_type = str(row["交易类型[ Transaction Type ]"]).strip()
            transaction_date = str(row["交易日期[ Transaction Date ]"]).strip()
            transaction_time = str(row["交易时间[ Transaction time ]"]).strip()
            amount = pd.to_numeric(row["交易金额[ Trade Amount ]"], errors='coerce')
            balance = str(row["交易后余额[ After-transaction balance ]"]).strip()
            reference = str(row["摘要[ Reference ]"]).strip()
            payee_name = str(row["收款人名称[ Payee's Name ]"]).strip()
            payee_bank = str(row["收款人开户行名[ Beneficiary account bank ]"]).strip()
            payer_name = str(row["付款人名称[ Payer's Name ]"]).strip()
            payer_bank = str(row["付款人开户行名[ Payer account bank ]"]).strip()
            
            # 跳过空行
            if pd.isna(amount) or amount == 0:
                continue
            
            # 根据交易类型确定收支
            if transaction_type == "往账":
                income = 0
                expense = abs(amount)
                opponent_name = payee_name
                opponent_bank = payee_bank
            elif transaction_type == "来账":
                income = abs(amount)
                expense = 0
                opponent_name = payer_name
                opponent_bank = payer_bank
            else:
                continue
            
            # 合并日期和时间
            transaction_datetime = f"{transaction_date} {transaction_time}"
            
            standard_flow.append({
                "交易时间": transaction_datetime,
                "对方户名": opponent_name,
                "摘要": reference,
                "收入": income,
                "支出": expense,
                "对方开户机构": opponent_bank,
                "备注": opponent_bank,
                "余额": balance
            })
        
        return pd.DataFrame(standard_flow)
    except Exception as e:
        messagebox.showerror("错误", f"解析中国银行流水失败：{str(e)}")
        return None

def generate_journal_data(company_name, bank_name, param_file, flow_file):
    """核心：生成日记账数据"""
    # 1. 加载参数表
    account_params, category_params = load_parameter_table(param_file)
    if account_params is None:
        return None
    
    # 2. 根据银行名称选择不同的解析函数
    if "中国银行" in bank_name:
        flow_df = parse_boc_flow(flow_file)
    elif "建设银行" in bank_name:
        flow_df = parse_ccb_flow(flow_file)
    else:
        messagebox.showerror("错误", f"不支持的银行：{bank_name}")
        return None
    
    if flow_df is None:
        return None
    
    # 3. 匹配账户编号和简称（按公司+开户行）
    # 先精确匹配公司，然后模糊匹配开户行
    account_filter = account_params["公司"] == company_name
    account_info = account_params[account_filter]
    
    # 如果开户行不是具体的支行名称，则进行模糊匹配
    if not account_info.empty and bank_name not in account_info["开户行"].values:
        # 模糊匹配：检查开户行是否包含银行名称
        account_filter = account_filter & account_params["开户行"].str.contains(bank_name, na=False)
        account_info = account_params[account_filter]
    
    if account_info.empty:
        messagebox.showerror("错误", f"参数表中未找到{company_name}-{bank_name}的账户信息")
        return None
    
    account_code = account_info["编号"].iloc[0]
    account_short_name = account_info["简称"].iloc[0]
    company_name_full = account_info["公司"].iloc[0]
    
    # 4. 构建日记账数据
    journal_data = []
    for _, row in flow_df.iterrows():
        # 跳过空行（交易时间为空）
        if pd.isna(row["交易时间"]) or str(row["交易时间"]).strip() == "":
            continue
        
        # 计算收支金额
        income = row["收入"] if pd.notna(row["收入"]) else 0
        expense = row["支出"] if pd.notna(row["支出"]) else 0
        
        # 确定收支类型和金额
        if income > 0:
            shouzhi_type = "收入"
            amount = income
        elif expense > 0:
            shouzhi_type = "支出"
            amount = expense
        else:
            shouzhi_type = ""
            amount = 0
        
        # 提取月份（从日期中提取）
        try:
            date_obj = pd.to_datetime(row["交易时间"], errors='coerce')
            month = date_obj.month if pd.notna(date_obj) else ""
        except:
            month = ""
        
        # 格式化日期为 2026/1/26 格式
        try:
            date_obj = pd.to_datetime(row["交易时间"], errors='coerce')
            if pd.notna(date_obj):
                formatted_date = f"{date_obj.year}/{date_obj.month}/{date_obj.day}"
            else:
                formatted_date = row["交易时间"]
        except:
            formatted_date = row["交易时间"]
        
        # 判断对方户名是否为个人（简单判断：如果对方户名较短且不包含"公司"等关键词）
        opponent_name = str(row["对方户名"]).strip()
        is_personal = len(opponent_name) < 5 or "公司" not in opponent_name
        
        # 构建备注：对方户名+对方开户机构，如果是个人则用对方户名+备注
        if is_personal:
            remark = f"{opponent_name}{row['备注']}"
        else:
            remark = f"{opponent_name}{row['对方开户机构']}"
        
        # 基础字段
        journal_row = {
            "编号": account_code,
            "简称": account_short_name,
            "企业": company_name_full,
            "日期": formatted_date,
            "月份": month,
            "收支": shouzhi_type,
            "资金分类-1级": "",
            "资金分类-2级": "",
            "资金分类-3级": "",
            "对手户": opponent_name,
            "备注": remark,
            "发生额": amount,
            "余额": row["余额"],
            "备注.1": ""
        }
        
        # 5. 匹配资金分类（核心规则）
        # 规则1：对手户是参数表中的公司 或 指定的两家公司 → 结算收入/支出
        if (opponent_name in account_params["公司"].values) or (opponent_name in SETTLEMENT_COMPANIES):
            if income > 0:
                # 结算收入
                journal_row["资金分类-1级"] = "结算收入"
                journal_row["资金分类-2级"] = "结算收入"
                journal_row["资金分类-3级"] = "结算收入"
            else:
                # 结算支出
                journal_row["资金分类-1级"] = "结算支出"
                journal_row["资金分类-2级"] = "结算支出"
                journal_row["资金分类-3级"] = "结算支出"
        # 规则2：摘要含"手续费"或"对公跨行转账汇款手续费" → 财务费用-财务费用-手续费
        elif "手续费" in str(row["摘要"]) or "对公跨行转账汇款手续费" in str(row["摘要"]):
            journal_row["资金分类-1级"] = "财务费用"
            journal_row["资金分类-2级"] = "财务费用"
            journal_row["资金分类-3级"] = "手续费"
            # 对手户和备注都只写成手续费
            journal_row["对手户"] = "手续费"
            journal_row["备注"] = "手续费"
        # 规则3：备注是"会长提现" → 结算支出-结算支出-会长提现支付
        elif "会长提现" in str(row["备注"]):
            journal_row["资金分类-1级"] = "结算支出"
            journal_row["资金分类-2级"] = "结算支出"
            journal_row["资金分类-3级"] = "会长提现支付"
        # 其他情况（可后续补充规则）
        else:
            journal_row["资金分类-1级"] = "待分类"
            journal_row["资金分类-2级"] = "待分类"
            journal_row["资金分类-3级"] = "待分类"
        
        journal_data.append(journal_row)
    
    # 转换为DataFrame
    journal_df = pd.DataFrame(journal_data)
    # 确保列顺序匹配参考日记账
    journal_columns = [
        "编号", "简称", "企业", "日期", "月份", "收支",
        "资金分类-1级", "资金分类-2级", "资金分类-3级", "对手户", "备注", "发生额", "余额", "备注.1"
    ]
    journal_df = journal_df[journal_columns]
    
    return journal_df

# -------------------------- GUI界面 --------------------------
def select_param_file():
    """选择参数表文件"""
    file_path = filedialog.askopenfilename(
        title="选择参数表文件",
        filetypes=[("Excel文件", "*.xlsx *.xls"), ("所有文件", "*.*")]
    )
    if file_path:
        param_file_var.set(file_path)

def select_flow_file():
    """选择银行流水文件"""
    bank = bank_var.get()
    file_path = filedialog.askopenfilename(
        title=f"选择{bank}流水文件",
        filetypes=[("Excel文件", "*.xlsx *.xls"), ("所有文件", "*.*")]
    )
    if file_path:
        flow_file_var.set(file_path)

def generate_journal():
    """生成日记账"""
    # 获取界面输入
    company = company_var.get()
    bank = bank_var.get()
    param_file = param_file_var.get()
    flow_file = flow_file_var.get()
    
    # 校验输入
    if not param_file or not flow_file:
        messagebox.showwarning("提示", "请先选择参数表和流水文件")
        return
    
    # 生成日记账数据
    journal_df = generate_journal_data(company, bank, param_file, flow_file)
    if journal_df is None:
        return
    
    # 保存文件
    today = datetime.now().strftime("%Y年%m月%d日")
    save_path = filedialog.asksaveasfilename(
        title="保存日记账",
        defaultextension=".xlsx",
        initialfile=f"{company}-{bank}-日记账-{today}.xlsx",
        filetypes=[("Excel文件", "*.xlsx"), ("所有文件", "*.*")]
    )
    if save_path:
        # 保存时保持格式（匹配参考日记账）
        with pd.ExcelWriter(save_path, engine="openpyxl") as writer:
            journal_df.to_excel(writer, index=False, sheet_name="日记账")
            
            # 设置表头加粗
            workbook = writer.book
            worksheet = writer.sheets["日记账"]
            
            # 创建加粗字体样式
            bold_font = Font(bold=True)
            
            # 设置表头行加粗
            for cell in worksheet[1]:
                cell.font = bold_font
        
        messagebox.showinfo("成功", f"日记账已生成：{save_path}")

# 初始化GUI
root = tk.Tk()
root.title("出纳日记账生成工具")
root.geometry("600x300")

# 变量定义
company_var = tk.StringVar(value="成都锦高量科科技有限公司")
bank_var = tk.StringVar(value="建设银行")
param_file_var = tk.StringVar()
flow_file_var = tk.StringVar()

# 界面布局
# 1. 公司选择
frame_company = ttk.Frame(root, padding="10")
frame_company.pack(fill=tk.X)
ttk.Label(frame_company, text="公司名称：").pack(side=tk.LEFT)
company_combo = ttk.Combobox(frame_company, textvariable=company_var, state="readonly")
company_combo["values"] = ["成都锦高量科科技有限公司", "成都云启寰宇科技有限公司", "成都橙风有量科技有限公司"]
company_combo.pack(side=tk.LEFT, padx=5)

# 2. 银行选择
frame_bank = ttk.Frame(root, padding="10")
frame_bank.pack(fill=tk.X)
ttk.Label(frame_bank, text="开户银行：").pack(side=tk.LEFT)
bank_combo = ttk.Combobox(frame_bank, textvariable=bank_var, state="readonly")
bank_combo["values"] = ["建设银行", "中国银行"]
bank_combo.pack(side=tk.LEFT, padx=5)

# 3. 参数表选择
frame_param = ttk.Frame(root, padding="10")
frame_param.pack(fill=tk.X)
ttk.Label(frame_param, text="参数表文件：").pack(side=tk.LEFT)
ttk.Entry(frame_param, textvariable=param_file_var, width=50).pack(side=tk.LEFT, padx=5)
ttk.Button(frame_param, text="选择", command=select_param_file).pack(side=tk.LEFT)

# 4. 流水文件选择
frame_flow = ttk.Frame(root, padding="10")
frame_flow.pack(fill=tk.X)
ttk.Label(frame_flow, text="流水文件：").pack(side=tk.LEFT)
ttk.Entry(frame_flow, textvariable=flow_file_var, width=50).pack(side=tk.LEFT, padx=5)
ttk.Button(frame_flow, text="选择", command=select_flow_file).pack(side=tk.LEFT)

# 5. 生成按钮
frame_generate = ttk.Frame(root, padding="10")
frame_generate.pack(fill=tk.X)
ttk.Button(frame_generate, text="生成日记账", command=generate_journal, style="Accent.TButton").pack()

# 启动GUI
root.mainloop()