rijizhang/journal_generator.py

import pandas as pd
from tkinter import messagebox


class JournalGenerator:
    """日记账生成器"""
    
    def __init__(self, account_param_sheet="账户信息参数", category_param_sheet="收支分类参数"):
        self.account_param_sheet = account_param_sheet
        self.category_param_sheet = category_param_sheet
        self.settlement_companies = ["成都合煦商贸有限公司", "四川泗讯科技有限公司"]
    
    def load_parameter_table(self, param_file_path):
        """加载参数表，返回账户信息和收支分类"""
        try:
            account_params = pd.read_excel(param_file_path, sheet_name=self.account_param_sheet, header=1)
            category_params = pd.read_excel(param_file_path, sheet_name=self.category_param_sheet, header=1)
            return account_params, category_params
        except Exception as e:
            messagebox.showerror("错误", f"加载参数表失败：{str(e)}")
            return None, None
    
    def generate_journal_data(self, company_name, bank_name, param_file, flow_df):
        """核心：生成日记账数据"""
        account_params, category_params = self.load_parameter_table(param_file)
        if account_params is None:
            return None
        
        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]
        
        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 = ""
            
            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": ""
            }
            
            if (opponent_name in account_params["公司"].values) or (opponent_name in self.settlement_companies):
                if income > 0:
                    journal_row["资金分类-1级"] = "结算收入"
                    journal_row["资金分类-2级"] = "结算收入"
                    journal_row["资金分类-3级"] = "结算收入"
                else:
                    journal_row["资金分类-1级"] = "结算支出"
                    journal_row["资金分类-2级"] = "结算支出"
                    journal_row["资金分类-3级"] = "结算支出"
            elif "手续费" in str(row["摘要"]) or "对公跨行转账汇款手续费" in str(row["摘要"]):
                journal_row["资金分类-1级"] = "财务费用"
                journal_row["资金分类-2级"] = "财务费用"
                journal_row["资金分类-3级"] = "手续费"
                journal_row["对手户"] = "手续费"
                journal_row["备注"] = "手续费"
            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)
        
        journal_df = pd.DataFrame(journal_data)
        journal_columns = [
            "编号", "简称", "企业", "日期", "月份", "收支",
            "资金分类-1级", "资金分类-2级", "资金分类-3级", "对手户", "备注", "发生额", "余额", "备注.1"
        ]
        journal_df = journal_df[journal_columns]
        
        return journal_df
    
    def save_journal(self, journal_df, save_path):
        """保存日记账到Excel文件"""
        try:
            from openpyxl.styles import Font
            
            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
            
            return True
        except Exception as e:
            messagebox.showerror("错误", f"保存日记账失败：{str(e)}")
            return False