rijizhang/journal_generator.py

import pandas as pd
from tkinter import messagebox


class JournalGenerator:
    """日记账生成器"""
    
    def __init__(self):
        self.param_df = None
        self.account_info_df = None
        self.shouzhi_class_df = None
    
    def load_parameter_table(self, param_file_path):
        """加载参数表"""
        try:
            self.param_df = pd.read_excel(param_file_path, dtype=str)
            
            account_info_sheet = pd.read_excel(param_file_path, sheet_name="账户信息参数", dtype=str, header=1)
            self.account_info_df = account_info_sheet
            
            shouzhi_class_sheet = pd.read_excel(param_file_path, sheet_name="收支分类参数", dtype=str, header=1)
            self.shouzhi_class_df = shouzhi_class_sheet
            
            return True
        except Exception as e:
            messagebox.showerror("错误", f"加载参数表失败：{str(e)}")
            return False
    
    def generate_journal_data(self, company_name, bank_name, param_file_path, flow_df):
        """生成日记账数据"""
        if not self.load_parameter_table(param_file_path):
            return None
        
        try:
            bank_keyword_mapping = {
                "微众银行": "微众",
                "中国银行": "中国银行",
                "农业银行": "农业银行",
                "建设银行": "建设银行"
            }
            
            bank_keyword = bank_keyword_mapping.get(bank_name, bank_name)
            
            company_accounts = self.account_info_df[self.account_info_df["公司"] == company_name]
            
            account_info = None
            for _, account in company_accounts.iterrows():
                if bank_keyword in account["开户行"]:
                    account_info = company_accounts[company_accounts.index == account.name]
                    break
            
            if account_info is None or account_info.empty:
                messagebox.showerror("错误", f"未找到账户信息：{company_name} - {bank_name}")
                return None
            
            account_code = account_info["编号"].values[0]
            account_short_name = account_info["简称"].values[0]
            company_name_full = account_info["公司"].values[0]
            
            journal_data = []
            
            for _, row in flow_df.iterrows():
                transaction_time = row["交易时间"]
                opponent_name = str(row["对方户名"]).strip()
                summary = str(row["摘要"]).strip()
                income = pd.to_numeric(row["收入"], errors="coerce")
                expense = pd.to_numeric(row["支出"], errors="coerce")
                balance = str(row["余额"]).strip()
                
                if pd.isna(income):
                    income = 0
                if pd.isna(expense):
                    expense = 0
                
                if income == 0 and expense == 0:
                    continue
                
                amount = income if income > 0 else expense
                shouzhi_type = "收入" if income > 0 else "支出"
                
                try:
                    transaction_date = pd.to_datetime(transaction_time, errors="coerce")
                    if pd.isna(transaction_date):
                        continue
                    formatted_date = f"{transaction_date.year}/{transaction_date.month}/{transaction_date.day}"
                    month = str(transaction_date.month)
                except:
                    continue
                
                opponent_bank = str(row.get("对方开户机构", "")).strip()
                flow_remark = str(row.get("备注", "")).strip()
                
                is_settlement = False
                if opponent_name in self.account_info_df["公司"].values:
                    is_settlement = True
                
                if is_settlement:
                    if income > 0:
                        fund_level1 = "结算收入"
                        fund_level2 = "结算收入"
                        fund_level3 = "结算收入"
                    else:
                        fund_level1 = "结算支出"
                        fund_level2 = "结算支出"
                        fund_level3 = "结算支出"
                elif "会长提现" in flow_remark or "提现" in flow_remark:
                        fund_level1 = "游戏业务"
                        fund_level2 = "结算支出"
                        fund_level3 = "会长提现支付"
                elif "手续费" in summary or "服务费" in summary:
                    fund_level1 = "财务费用"
                    fund_level2 = "财务费用"
                    fund_level3 = "手续费"
                else:
                    fund_level1 = ""
                    fund_level2 = ""
                    fund_level3 = ""
                
                if "手续费" in summary or "服务费" in summary:
                    opponent_name = "手续费"
                    remark = "手续费"
                elif "会长提现" in flow_remark or "提现" in flow_remark:
                    remark = opponent_name + flow_remark
                else:
                    remark = opponent_name + opponent_bank
                
                journal_row = {
                    "编号": account_code,
                    "简称": account_short_name,
                    "企业": company_name_full,
                    "日期": formatted_date,
                    "月份": month,
                    "收支": shouzhi_type,
                    "资金分类-1级": fund_level1,
                    "资金分类-2级": fund_level2,
                    "资金分类-3级": fund_level3,
                    "对手户": opponent_name,
                    "备注": remark,
                    "发生额": amount,
                    "余额": balance,
                    "备注.1": ""
                }
                
                journal_data.append(journal_row)
            
            if not journal_data:
                messagebox.showwarning("警告", "未生成任何日记账数据")
                return None
            
            journal_df = pd.DataFrame(journal_data)
            
            column_order = ["编号", "简称", "企业", "日期", "月份", "收支", "资金分类-1级", 
                          "资金分类-2级", "资金分类-3级", "对手户", "备注", "发生额", "余额", "备注.1"]
            journal_df = journal_df[column_order]
            
            return journal_df
            
        except Exception as e:
            messagebox.showerror("错误", f"生成日记账数据失败：{str(e)}")
            return None
    
    def save_journal(self, journal_df, save_path):
        """保存日记账"""
        try:
            journal_df.to_excel(save_path, index=False)
            return True
        except Exception as e:
            messagebox.showerror("错误", f"保存日记账失败：{str(e)}")
            return False