import tkinter as tk from tkinter import ttk, messagebox, filedialog, simpledialog from datetime import datetime, date, timedelta from typing import List, Dict, Optional from dataclasses import dataclass import ssl import urllib.request import io import traceback import pandas as pd from openpyxl import Workbook from openpyxl.styles import Font, Alignment, Border, Side from openpyxl.utils import get_column_letter import warnings import certifi import requests warnings.filterwarnings('ignore') # 配置 SSL 证书（解决 macOS 上 SSL 验证失败的问题） ssl_context = ssl.create_default_context(cafile=certifi.where()) @dataclass class Transaction: trans_date: date trans_type: str # "add" 或 "repay" amount: float effective_date: Optional[date] = None project_name: str = "" note: str = "" class LPRDataFetcher: _CSV_PATH = 'lpr_data.csv' @staticmethod def fetch_lpr_data() -> tuple[Dict[date, float], str]: # 三级缓存机制：在线爬取 → CSV读取 → 内置数据 # 返回值：(数据字典, 来源说明) # 1. 尝试在线爬取 try: data = LPRDataFetcher._fetch_from_web() if data: print("LPR数据来源：在线爬取") LPRDataFetcher._save_to_csv(data) return data, "在线爬取" except Exception as e: print(f"在线爬取失败: {e}") # 2. 尝试读取本地CSV try: data = LPRDataFetcher._load_from_csv() if data: print("LPR数据来源：本地CSV文件") return data, "本地CSV" except Exception as e: print(f"读取CSV失败: {e}") # 3. 使用内置数据 print("LPR数据来源：内置备用数据") return LPRDataFetcher._get_fallback_data(), "内置备用" @staticmethod def _fetch_from_web() -> Optional[Dict[date, float]]: url = 'https://www.boc.cn/fimarkets/lilv/fd32/201310/t20131031_2591219.html' headers = { 'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36', 'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8', 'Accept-Language': 'zh-CN,zh;q=0.9', } response = requests.get(url, headers=headers, timeout=15) response.encoding = 'utf-8' html = response.text dfs = pd.read_html(io.StringIO(html)) print(f"成功解析出 {len(dfs)} 个表格") target = None for df in dfs: cols = [str(c).strip() for c in df.columns] if any('1年期' in c for c in cols): target = df print(f"找到目标表格，列名：{cols}") break if target is None: for df in dfs: if len(df.columns) >= 2: sample = str(df.iloc[0, 0]) if len(df) > 0 else '' if '-' in sample and len(sample) == 10: target = df print(f"按位置找到目标表格，第一列样例：{sample}") break if target is None: raise Exception("未找到目标表格") date_col = None rate_col = None for col in target.columns: col_str = str(col).strip() if 'LPR' in col_str or '报价' in col_str or '日期' in col_str: date_col = col if '1年期' in col_str: rate_col = col if date_col is None: date_col = target.columns[0] if rate_col is None: if len(target.columns) >= 2: rate_col = target.columns[1] else: raise Exception("无法定位利率列") df_work = target[[date_col, rate_col]].copy() df_work.columns = ['date_str', 'rate_str'] df_work = df_work.dropna() df_work['date'] = pd.to_datetime(df_work['date_str'], errors='coerce').dt.date df_work = df_work.dropna(subset=['date']) df_work['rate_str'] = df_work['rate_str'].astype(str).str.replace('%', '').str.strip() df_work['rate'] = pd.to_numeric(df_work['rate_str'], errors='coerce') df_work = df_work.dropna(subset=['rate']) lpr_map = {row['date']: row['rate'] for _, row in df_work.iterrows()} if not lpr_map: raise Exception("没有有效的LPR数据") print(f"成功解析 {len(lpr_map)} 条LPR数据") return dict(sorted(lpr_map.items())) @staticmethod def _save_to_csv(data: Dict[date, float]) -> None: import os df = pd.DataFrame(list(data.items()), columns=['date', 'rate']) df['date'] = df['date'].astype(str) df.to_csv(LPRDataFetcher._CSV_PATH, index=False, encoding='utf-8') print(f"LPR数据已保存到 {os.path.abspath(LPRDataFetcher._CSV_PATH)}") @staticmethod def _load_from_csv() -> Optional[Dict[date, float]]: import os if not os.path.exists(LPRDataFetcher._CSV_PATH): raise Exception(f"CSV文件不存在: {LPRDataFetcher._CSV_PATH}") df = pd.read_csv(LPRDataFetcher._CSV_PATH, encoding='utf-8') df['date'] = pd.to_datetime(df['date']).dt.date lpr_map = {row['date']: row['rate'] for _, row in df.iterrows()} print(f"从CSV读取到 {len(lpr_map)} 条LPR数据") return lpr_map @staticmethod def _get_fallback_data() -> Dict[date, float]: fallback_data = { date(2026,5,20):3.00, date(2026,4,20):3.00, date(2026,3,20):3.00, date(2026,2,24):3.00, date(2026,1,20):3.00, date(2025,12,22):3.00, date(2025,11,20):3.00, date(2025,10,20):3.00, date(2025,9,22):3.00, date(2025,8,20):3.00, date(2025,7,21):3.00, date(2025,6,20):3.00, date(2025,5,20):3.00, date(2025,4,21):3.10, date(2025,3,20):3.10, date(2025,2,20):3.10, date(2025,1,20):3.10, date(2024,12,20):3.10, date(2024,11,20):3.10, date(2024,10,21):3.10, date(2024,9,20):3.35, date(2024,8,20):3.35, date(2024,7,22):3.35, date(2024,6,20):3.45, date(2024,5,20):3.45, date(2024,4,22):3.45, date(2024,3,20):3.45, date(2024,2,20):3.45, date(2024,1,22):3.45, date(2023,12,20):3.45, date(2023,11,20):3.45, date(2023,10,20):3.45, date(2023,9,20):3.45, date(2023,8,21):3.45, date(2023,7,20):3.55, date(2023,6,20):3.55, date(2023,5,22):3.65, date(2023,4,20):3.65, date(2023,3,20):3.65, date(2023,2,20):3.65, date(2023,1,20):3.65, date(2022,12,20):3.65, date(2022,11,21):3.65, date(2022,10,20):3.65, date(2022,9,20):3.65, date(2022,8,22):3.65, date(2022,7,20):3.70, date(2022,6,20):3.70, date(2022,5,20):3.70, date(2022,4,20):3.70, date(2022,3,21):3.70, date(2022,2,21):3.70, date(2022,1,20):3.70, date(2021,12,20):3.80, date(2021,11,22):3.85, date(2021,10,20):3.85, date(2021,9,22):3.85, date(2021,8,20):3.85, date(2021,7,20):3.85, date(2021,6,21):3.85, date(2021,5,20):3.85, date(2021,4,20):3.85, date(2021,3,22):3.85, date(2021,2,22):3.85, date(2021,1,20):3.85, date(2020,12,21):3.85, date(2020,11,20):3.85, date(2020,10,20):3.85, date(2020,9,21):3.85, date(2020,8,20):3.85, date(2020,7,20):3.85, date(2020,6,22):3.85, date(2020,5,20):3.85, date(2020,4,20):3.85, date(2020,3,20):4.05, date(2020,2,20):4.05, date(2020,1,20):4.15, date(2019,12,20):4.15, date(2019,11,20):4.15, date(2019,10,21):4.20, date(2019,9,20):4.20, date(2019,8,20):4.25, } return dict(sorted(fallback_data.items())) @staticmethod def get_rate_for_date(target_date: date, lpr_map: Dict[date, float]) -> float: applicable_date = None for pub_date in sorted(lpr_map.keys()): if pub_date <= target_date: applicable_date = pub_date else: break return lpr_map[applicable_date] if applicable_date else 4.25 class InterestFirstCalculator: def __init__(self, lpr_map: Dict[date, float], days_per_year: int = 360, multiplier: float = 4.0, manual_rate: Optional[float] = None): self.lpr_map = lpr_map self.days_per_year = days_per_year self.multiplier = multiplier self.manual_rate = manual_rate self.reset() def reset(self): self.principal = 0.0 self.interest_unpaid = 0.0 self.prepayment = 0.0 self.last_date: Optional[date] = None self.last_rate_pct: Optional[float] = None self.ledger: List[Dict] = [] def _get_current_rate(self, date_obj: date) -> float: if self.manual_rate is not None: return self.manual_rate base = LPRDataFetcher.get_rate_for_date(date_obj, self.lpr_map) return base * self.multiplier def _calc_interest_between(self, principal: float, start: date, end: date) -> float: if start >= end or principal <= 0: return 0.0 if self.manual_rate is not None: days = (end - start).days return principal * (self.manual_rate / 100) * days / self.days_per_year changes = [pub for pub in self.lpr_map.keys() if start < pub < end] changes.sort() total = 0.0 current = start for change_date in changes: days = (change_date - current).days if days > 0: rate = self._get_current_rate(current) total += principal * (rate / 100) * days / self.days_per_year current = change_date if end > current: days = (end - current).days if days > 0: rate = self._get_current_rate(current) total += principal * (rate / 100) * days / self.days_per_year return total def _accrue_interest_to_date(self, to_date: date) -> float: if self.last_date is None: self.last_date = to_date self.last_rate_pct = self._get_current_rate(to_date) return 0.0 if to_date <= self.last_date: return 0.0 if self.manual_rate is not None: days = (to_date - self.last_date).days if days <= 0: return 0.0 interest = self.principal * (self.manual_rate / 100) * days / self.days_per_year self.interest_unpaid += interest self.last_date = to_date self.last_rate_pct = self.manual_rate return interest changes = [pub for pub in self.lpr_map.keys() if self.last_date < pub < to_date] changes.sort() total = 0.0 current = self.last_date for change_date in changes: days = (change_date - current).days if days > 0: rate = self._get_current_rate(current) total += self.principal * (rate / 100) * days / self.days_per_year current = change_date if to_date > current: days = (to_date - current).days if days > 0: rate = self._get_current_rate(current) total += self.principal * (rate / 100) * days / self.days_per_year self.interest_unpaid += total self.last_date = to_date self.last_rate_pct = self._get_current_rate(to_date) return total def add_principal(self, amount: float, trans_date: date, effective_date: date, project_name: str = "", note: str = ""): self._accrue_interest_to_date(effective_date) actual_add = amount deduct = 0.0 if self.prepayment > 0: deduct = min(self.prepayment, actual_add) actual_add -= deduct self.prepayment -= deduct row = { "日期": trans_date, "类型": "本金增加", "结算金额": amount, "实际增加本金": actual_add, "项目名称": project_name, "本金变动前": round(self.principal, 2), "期间利息": 0.0, "未付利息(前)": round(self.interest_unpaid, 4), "利息冲减": 0, "本金变动后": round(self.principal + actual_add, 2), "未付利息(后)": round(self.interest_unpaid, 4), "备注": note + (f" (抵扣预付款{deduct:.2f})" if deduct > 0 else ""), "有效起息日": effective_date, "还本金额": 0, "还息金额": 0, "盈余": 0, } self.principal += actual_add self.last_date = effective_date self.last_rate_pct = self._get_current_rate(effective_date) self.ledger.append(row) def repay(self, amount: float, repay_date: date, note: str = ""): self._accrue_interest_to_date(repay_date) principal_before = self.principal interest_before = self.interest_unpaid total_debt = principal_before + interest_before if amount >= total_debt: interest_paid = interest_before principal_paid = principal_before surplus = amount - total_debt self.principal = 0 self.interest_unpaid = 0 self.prepayment += surplus row = { "日期": repay_date, "类型": "还款", "结算金额": 0, "项目名称": "", "本金变动前": round(principal_before, 2), "期间利息": 0.0, "未付利息(前)": round(interest_before, 4), "利息冲减": round(interest_paid, 2), "本金变动后": round(self.principal, 2), "未付利息(后)": round(self.interest_unpaid, 4), "备注": f"{note} (还息{interest_paid:.2f}，还本{principal_paid:.2f}，盈余{surplus:.2f})", "还本金额": principal_paid, "还息金额": interest_paid, "盈余": surplus, "有效起息日": None, } else: interest_paid = min(self.interest_unpaid, amount) remaining = amount - interest_paid principal_paid = min(self.principal, remaining) self.principal -= principal_paid self.interest_unpaid -= interest_paid row = { "日期": repay_date, "类型": "还款", "结算金额": 0, "项目名称": "", "本金变动前": round(principal_before, 2), "期间利息": 0.0, "未付利息(前)": round(interest_before, 4), "利息冲减": round(interest_paid, 2), "本金变动后": round(self.principal, 2), "未付利息(后)": round(self.interest_unpaid, 4), "备注": f"{note} (还息{interest_paid:.2f}，还本{principal_paid:.2f})", "还本金额": principal_paid, "还息金额": interest_paid, "盈余": 0, "有效起息日": None, } self.ledger.append(row) self.last_date = repay_date self.last_rate_pct = self._get_current_rate(repay_date) def final_accrue(self, end_date: date): """累加最后一段利息，并更新最后一行的展示字段""" added_interest = self._accrue_interest_to_date(end_date) if added_interest > 0 and self.ledger: last_row = self.ledger[-1] # 更新最后一行的期间利息和未付利息(后) last_row['期间利息'] = round(last_row.get('期间利息', 0) + added_interest, 4) last_row['未付利息(后)'] = round(last_row.get('未付利息(后)', 0) + added_interest, 4) # 同时更新未付利息(前) 保持一致性（不影响实际值，仅为展示） last_row['未付利息(前)'] = round(last_row.get('未付利息(前)', 0) + added_interest, 4) if '计息至' not in last_row.get('备注', ''): last_row['备注'] = last_row.get('备注', '') + f" (含截至{end_date}利息)" def get_final_status(self) -> Dict: # 直接从最后一行的未付利息(后)和本金变动后计算 if not self.ledger: return {"未清偿本金": 0, "未清偿利息": 0, "合计": 0} last_row = self.ledger[-1] principal = last_row['本金变动后'] interest = last_row['未付利息(后)'] return { "未清偿本金": round(principal, 2), "未清偿利息": round(interest, 2), "合计": round(principal + interest, 2) } def build_display_ledger(self, end_date: date) -> List[Dict]: if not self.ledger: return [] # 构建事件列表 events = [] for row in self.ledger: if row['类型'] == '本金增加': effective = row.get('有效起息日', row['日期']) else: effective = row['日期'] events.append({ 'row': row, 'effective_date': effective, 'principal_after': row['本金变动后'], 'principal_before': row['本金变动前'], 'interest_before': row['未付利息(前)'], }) events.sort(key=lambda e: (e['effective_date'], 0 if e['row']['类型'] == '还款' else 1)) display_rows = [] for i, ev in enumerate(events): row = ev['row'] start = ev['effective_date'] if i + 1 < len(events): end_d = events[i+1]['effective_date'] else: end_d = end_date principal_for_interest = ev['principal_after'] interest = self._calc_interest_between(principal_for_interest, start, end_d) # 应付款金额/计息金额：统一显示用于计算逾期利息的本金基数 due_amount = ev['principal_after'] # 未付款总额：直接使用该行事件后的本金+利息（注意：利息已包含到 end_date 的累积） # 对于最后一行，利息已经由 final_accrue 更新到 row['未付利息(后)'] 中 total_due = ev['principal_after'] + row['未付利息(后)'] display_row = { **row, '归属利息': round(interest, 4), '计息截止日': end_d, '应付款金额': due_amount, '未付款总额': round(total_due, 2) } display_rows.append(display_row) return display_rows class InterestCalculatorApp: def __init__(self, root): self.root = root self.root.title("先息后本利息计算器（LPR/固定利率）") self.root.geometry("1300x950") self.lpr_data: Dict[date, float] = {} self.transactions: List[Transaction] = [] self.project_name = "" self.edit_index = -1 self.create_widgets() self.fetch_lpr_data() def fetch_lpr_data(self): self.status_var.set("正在获取LPR历史数据...") self.root.update() self.root.after(100, self._do_fetch_lpr) def _do_fetch_lpr(self): try: self.lpr_data, data_source = LPRDataFetcher.fetch_lpr_data() if self.lpr_data: latest_date, latest_rate = list(self.lpr_data.items())[-1] if self.rate_mode_var.get() == "lpr": multiplier = float(self.lpr_multiplier_var.get()) if data_source == "在线爬取": self.status_var.set(f"LPR数据获取成功！最新：{latest_date} 1年期={latest_rate}%") elif data_source == "本地CSV": self.status_var.set(f"LPR数据来源：本地CSV，最新：{latest_date} 1年期={latest_rate}%") else: self.status_var.set(f"LPR数据来源：内置备用，最新：{latest_date} 1年期={latest_rate}%") self.rate_label.config(text=f"{latest_rate}% × {multiplier} = {latest_rate * multiplier:.2f}%") else: if data_source == "在线爬取": self.status_var.set(f"LPR数据获取成功（当前为手动利率模式）") elif data_source == "本地CSV": self.status_var.set(f"LPR数据已加载（来源：本地CSV，当前为手动利率模式）") else: self.status_var.set(f"LPR数据已加载（来源：内置备用，当前为手动利率模式）") else: self.status_var.set("LPR数据获取失败，使用备用数据") except Exception as e: self.status_var.set(f"LPR获取异常: {e}，使用备用数据") def create_widgets(self): # 利率模式选择 mode_frame = ttk.LabelFrame(self.root, text="利率模式", padding=10) mode_frame.pack(fill=tk.X, padx=10, pady=5) self.rate_mode_var = tk.StringVar(value="lpr") ttk.Radiobutton(mode_frame, text="LPR浮动利率", variable=self.rate_mode_var, value="lpr", command=self.on_rate_mode_change).grid(row=0, column=0, padx=5) ttk.Radiobutton(mode_frame, text="手动固定利率", variable=self.rate_mode_var, value="manual", command=self.on_rate_mode_change).grid(row=0, column=1, padx=5) ttk.Label(mode_frame, text="固定年利率(%)：").grid(row=0, column=2, padx=5) self.manual_rate_entry = ttk.Entry(mode_frame, width=8, state="disabled") self.manual_rate_entry.grid(row=0, column=3) self.manual_rate_entry.insert(0, "4.0") ttk.Label(mode_frame, text="%").grid(row=0, column=4) # 计息参数 param_frame = ttk.LabelFrame(self.root, text="计息参数", padding=10) param_frame.pack(fill=tk.X, padx=10, pady=5) ttk.Label(param_frame, text="计息基准天数：").grid(row=0, column=0, sticky=tk.W, padx=5) self.days_var = tk.StringVar(value="360") ttk.Combobox(param_frame, textvariable=self.days_var, values=["360","365"], width=5).grid(row=0, column=1, sticky=tk.W) ttk.Label(param_frame, text="LPR倍数：").grid(row=0, column=2, sticky=tk.W, padx=5) self.lpr_multiplier_var = tk.StringVar(value="4") self.lpr_combo = ttk.Combobox(param_frame, textvariable=self.lpr_multiplier_var, values=["1","1.5","2","3","4","5"], width=5) self.lpr_combo.grid(row=0, column=3, sticky=tk.W) self.lpr_combo.bind("<>", self.on_lpr_multiplier_change) ttk.Label(param_frame, text="计息年利率：").grid(row=0, column=4, sticky=tk.W, padx=5) self.rate_label = ttk.Label(param_frame, text="自动根据LPR计算", foreground="blue") self.rate_label.grid(row=0, column=5, sticky=tk.W) self.status_var = tk.StringVar(value="等待获取LPR数据...") ttk.Label(param_frame, textvariable=self.status_var, foreground="gray").grid(row=0, column=6, columnspan=3, sticky=tk.E, padx=10) # 项目名称 proj_frame = ttk.Frame(self.root) proj_frame.pack(fill=tk.X, padx=10, pady=5) ttk.Label(proj_frame, text="项目名称：").pack(side=tk.LEFT, padx=5) self.project_entry = ttk.Entry(proj_frame, width=40) self.project_entry.pack(side=tk.LEFT, padx=5) ttk.Button(proj_frame, text="设置为当前项目", command=self.set_project).pack(side=tk.LEFT, padx=5) # 交易录入 entry_frame = ttk.LabelFrame(self.root, text="添加/编辑交易记录", padding=10) entry_frame.pack(fill=tk.X, padx=10, pady=5) ttk.Label(entry_frame, text="交易日期 (YYYY-MM-DD)：").grid(row=0, column=0, sticky=tk.W, padx=5) self.date_entry = ttk.Entry(entry_frame, width=12) self.date_entry.grid(row=0, column=1) self.date_entry.insert(0, datetime.today().strftime("%Y-%m-%d")) ttk.Label(entry_frame, text="类型：").grid(row=0, column=2, sticky=tk.W, padx=5) self.type_var = tk.StringVar(value="本金增加") type_combo = ttk.Combobox(entry_frame, textvariable=self.type_var, values=["本金增加","还款"], width=10, state="readonly") type_combo.grid(row=0, column=3) type_combo.bind("<>", self.on_type_change) ttk.Label(entry_frame, text="金额：").grid(row=0, column=4, sticky=tk.W, padx=5) self.amount_entry = ttk.Entry(entry_frame, width=12) self.amount_entry.grid(row=0, column=5) ttk.Label(entry_frame, text="起息日 (YYYY-MM-DD)：").grid(row=1, column=0, sticky=tk.W, padx=5) self.effective_entry = ttk.Entry(entry_frame, width=12) self.effective_entry.grid(row=1, column=1) self.effective_entry.insert(0, datetime.today().strftime("%Y-%m-%d")) ttk.Label(entry_frame, text="备注：").grid(row=1, column=2, sticky=tk.W, padx=5) self.note_entry = ttk.Entry(entry_frame, width=30) self.note_entry.grid(row=1, column=3, columnspan=3, sticky=tk.W) btn_frame = ttk.Frame(entry_frame) btn_frame.grid(row=2, column=0, columnspan=6, pady=10) self.add_btn = ttk.Button(btn_frame, text="添加本金增加", command=self.add_add_transaction) self.add_btn.pack(side=tk.LEFT, padx=5) self.repay_btn = ttk.Button(btn_frame, text="添加还款", command=self.add_repay_transaction) self.repay_btn.pack(side=tk.LEFT, padx=5) self.edit_btn = ttk.Button(btn_frame, text="保存修改", command=self.edit_transaction, state="disabled") self.edit_btn.pack(side=tk.LEFT, padx=5) ttk.Button(btn_frame, text="删除选中", command=self.delete_selected).pack(side=tk.LEFT, padx=5) ttk.Button(btn_frame, text="清空全部", command=self.clear_all).pack(side=tk.LEFT, padx=5) ttk.Button(btn_frame, text="重新获取LPR", command=self.fetch_lpr_data).pack(side=tk.LEFT, padx=5) ttk.Button(btn_frame, text="导入交易", command=self.import_transactions).pack(side=tk.LEFT, padx=5) ttk.Button(btn_frame, text="保存交易", command=self.save_transactions).pack(side=tk.LEFT, padx=5) # 交易列表 list_frame = ttk.LabelFrame(self.root, text="交易列表（双击可编辑）", padding=5) list_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=5) self.tree = ttk.Treeview(list_frame, columns=("date","type","amount","effective","note"), show="headings") self.tree.heading("date", text="交易日期") self.tree.heading("type", text="类型") self.tree.heading("amount", text="金额") self.tree.heading("effective", text="起息日") self.tree.heading("note", text="备注") for col in ["date","type","amount","effective","note"]: self.tree.column(col, width=100 if col!="note" else 200) vsb = ttk.Scrollbar(list_frame, orient="vertical", command=self.tree.yview) self.tree.configure(yscrollcommand=vsb.set) self.tree.pack(side=tk.LEFT, fill=tk.BOTH, expand=True) vsb.pack(side=tk.RIGHT, fill=tk.Y) self.tree.bind("", self.on_tree_double_click) # 结果展示 result_frame = ttk.LabelFrame(self.root, text="计息明细与结果", padding=5) result_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=5) self.result_text = tk.Text(result_frame, wrap=tk.WORD, height=12) scrollbar = ttk.Scrollbar(result_frame, orient="vertical", command=self.result_text.yview) self.result_text.configure(yscrollcommand=scrollbar.set) self.result_text.pack(side=tk.LEFT, fill=tk.BOTH, expand=True) scrollbar.pack(side=tk.RIGHT, fill=tk.Y) # 操作按钮 export_frame = ttk.Frame(self.root) export_frame.pack(fill=tk.X, padx=10, pady=10) ttk.Button(export_frame, text="开始计算并生成Excel", command=self.calculate_and_export, width=25).pack(side=tk.LEFT, padx=5) ttk.Button(export_frame, text="仅计算（不导出）", command=self.calculate_only, width=18).pack(side=tk.LEFT, padx=5) self.on_type_change() self.on_rate_mode_change() def _sort_transactions(self): self.transactions.sort(key=lambda t: (t.trans_date, 0 if t.trans_type == "repay" else 1)) def set_project(self): self.project_name = self.project_entry.get().strip() self.status_var.set(f"当前项目：{self.project_name}") def on_rate_mode_change(self): mode = self.rate_mode_var.get() if mode == "lpr": self.lpr_combo.config(state="normal") self.manual_rate_entry.config(state="disabled") if self.lpr_data: self.on_lpr_multiplier_change() else: self.rate_label.config(text="等待LPR数据...") else: self.lpr_combo.config(state="disabled") self.manual_rate_entry.config(state="normal") try: manual_rate = float(self.manual_rate_entry.get()) self.rate_label.config(text=f"{manual_rate}%（固定）") except: self.rate_label.config(text="无效利率") def on_type_change(self, event=None): if self.type_var.get() == "本金增加": self.effective_entry.config(state="normal") else: self.effective_entry.config(state="disabled") def on_lpr_multiplier_change(self, event=None): if self.rate_mode_var.get() != "lpr": return if self.lpr_data: mult = float(self.lpr_multiplier_var.get()) latest_date, latest_rate = list(self.lpr_data.items())[-1] self.rate_label.config(text=f"{latest_rate}% × {mult} = {latest_rate * mult:.2f}%") def parse_date(self, date_str: str) -> Optional[date]: try: date_str = date_str.strip() if len(date_str)==8 and date_str.isdigit(): return datetime.strptime(date_str, "%Y%m%d").date() return datetime.strptime(date_str, "%Y-%m-%d").date() except: messagebox.showerror("日期错误", "请使用 YYYY-MM-DD 或 YYYYMMDD") return None def _exit_edit_mode(self): self.edit_index = -1 self.add_btn.config(state="normal") self.repay_btn.config(state="normal") self.edit_btn.config(state="disabled") self.date_entry.delete(0, tk.END); self.date_entry.insert(0, datetime.today().strftime("%Y-%m-%d")) self.type_var.set("本金增加") self.amount_entry.delete(0, tk.END) self.effective_entry.config(state="normal") self.effective_entry.delete(0, tk.END); self.effective_entry.insert(0, datetime.today().strftime("%Y-%m-%d")) self.note_entry.delete(0, tk.END) def _load_transaction_to_entries(self, trans: Transaction): self.date_entry.delete(0, tk.END); self.date_entry.insert(0, trans.trans_date.strftime("%Y-%m-%d")) self.type_var.set("本金增加" if trans.trans_type == "add" else "还款") self.amount_entry.delete(0, tk.END); self.amount_entry.insert(0, f"{trans.amount:.2f}") self.effective_entry.delete(0, tk.END) if trans.effective_date: self.effective_entry.insert(0, trans.effective_date.strftime("%Y-%m-%d")) self.note_entry.delete(0, tk.END); self.note_entry.insert(0, trans.note) self.on_type_change() def on_tree_double_click(self, event): selected = self.tree.selection() if not selected: return index = self.tree.index(selected[0]) if 0 <= index < len(self.transactions): self.edit_index = index self._load_transaction_to_entries(self.transactions[index]) self.add_btn.config(state="disabled") self.repay_btn.config(state="disabled") self.edit_btn.config(state="normal") def edit_transaction(self): if self.edit_index < 0: return trans_date = self.parse_date(self.date_entry.get().strip()) if not trans_date: return try: amount = float(self.amount_entry.get().strip()) if amount <= 0: raise ValueError except: messagebox.showerror("金额错误", "请输入正数"); return trans_type = "add" if self.type_var.get() == "本金增加" else "repay" effective_date = None if trans_type == "add": eff_str = self.effective_entry.get().strip() effective_date = self.parse_date(eff_str) if eff_str else trans_date if not effective_date: return note = self.note_entry.get().strip() self.transactions[self.edit_index] = Transaction(trans_date, trans_type, amount, effective_date, self.project_name, note) self._sort_transactions() self.refresh_tree() self._exit_edit_mode() def add_transaction(self, trans_type: str): if self.edit_index >= 0: self._exit_edit_mode() trans_date = self.parse_date(self.date_entry.get().strip()) if not trans_date: return try: amount = float(self.amount_entry.get().strip()) if amount <= 0: raise ValueError except: messagebox.showerror("金额错误", "请输入正数"); return effective_date = None if trans_type == "add": eff_str = self.effective_entry.get().strip() effective_date = self.parse_date(eff_str) if eff_str else trans_date if not effective_date: return note = self.note_entry.get().strip() self.transactions.append(Transaction(trans_date, trans_type, amount, effective_date, self.project_name, note)) self._sort_transactions() self.refresh_tree() self.amount_entry.delete(0, tk.END) self.note_entry.delete(0, tk.END) def add_add_transaction(self): self.add_transaction("add") def add_repay_transaction(self): self.add_transaction("repay") def delete_selected(self): selected = self.tree.selection() if not selected: messagebox.showinfo("提示", "请先选中记录"); return indices = [self.tree.index(item) for item in selected] indices.sort(reverse=True) for idx in indices: if 0 <= idx < len(self.transactions): del self.transactions[idx] self.refresh_tree() if self.edit_index >= len(self.transactions): self._exit_edit_mode() def clear_all(self): if messagebox.askyesno("确认", "清空所有交易记录？"): self.transactions.clear() self.refresh_tree() self.result_text.delete(1.0, tk.END) self._exit_edit_mode() def refresh_tree(self): self._sort_transactions() for item in self.tree.get_children(): self.tree.delete(item) for trans in self.transactions: type_disp = "本金增加" if trans.trans_type=="add" else "还款" eff_str = trans.effective_date.strftime("%Y-%m-%d") if trans.effective_date else "" self.tree.insert("", tk.END, values=(trans.trans_date.strftime("%Y-%m-%d"), type_disp, f"{trans.amount:.2f}", eff_str, trans.note)) def get_manual_rate(self) -> Optional[float]: if self.rate_mode_var.get() != "manual": return None try: rate = float(self.manual_rate_entry.get().strip()) if rate <= 0: raise ValueError return rate except: messagebox.showerror("利率错误", "请输入正数的固定年利率"); return None def calculate(self): if self.rate_mode_var.get() == "manual": manual_rate = self.get_manual_rate() if manual_rate is None: return None calc = InterestFirstCalculator({}, int(self.days_var.get()), 1.0, manual_rate) else: if not self.lpr_data: messagebox.showwarning("LPR未就绪", "请稍后或点击'重新获取LPR'"); return None multiplier = float(self.lpr_multiplier_var.get()) calc = InterestFirstCalculator(self.lpr_data, int(self.days_var.get()), multiplier, None) if not self.transactions: messagebox.showinfo("提示", "没有交易记录"); return None def get_effective_date(trans: Transaction): if trans.trans_type == "add": return trans.effective_date if trans.effective_date else trans.trans_date else: return trans.trans_date events = sorted(self.transactions, key=lambda t: (get_effective_date(t), 0 if t.trans_type == "repay" else 1)) for trans in events: if trans.trans_type == "add": eff = trans.effective_date if trans.effective_date else trans.trans_date calc.add_principal(trans.amount, trans.trans_date, eff, trans.project_name, trans.note) else: calc.repay(trans.amount, trans.trans_date, trans.note) return calc def calculate_only(self): calc = self.calculate() if not calc: return calc.final_accrue(date.today()) self._display_result(calc, date.today()) def calculate_and_export(self): calc = self.calculate() if not calc: return end_str = simpledialog.askstring("计息截止日期", "请输入计息截止日期 (YYYY-MM-DD)：", initialvalue=date.today().strftime("%Y-%m-%d")) if not end_str: return end_date = self.parse_date(end_str) if not end_date: return calc.final_accrue(end_date) self._display_result(calc, end_date) self.export_to_excel(calc, end_date) def _display_result(self, calc, end_date): self.result_text.delete(1.0, tk.END) self.result_text.insert(tk.END, "【计息流水明细】\n") self.result_text.insert(tk.END, f"{'日期':<12} {'类型':<8} {'金额':>12} {'本金(前)':>12} {'期间利息':>14} {'未付息(前)':>14} {'利息冲减':>10} {'本金(后)':>12} {'未付息(后)':>14} 备注\n") self.result_text.insert(tk.END, "-"*130+"\n") display_rows = calc.build_display_ledger(end_date) for row in display_rows: line = (f"{row['日期'].strftime('%Y-%m-%d'):<12} {row['类型']:<8} {row['结算金额']:>12.2f} " f"{row['本金变动前']:>12.2f} {row['归属利息']:>14.4f} {row['未付利息(前)']:>14.4f} " f"{row['利息冲减']:>10.2f} {row['本金变动后']:>12.2f} {row['未付利息(后)']:>14.4f} {row['备注']}\n") self.result_text.insert(tk.END, line) status = calc.get_final_status() self.result_text.insert(tk.END, "\n"+"="*60+"\n") self.result_text.insert(tk.END, f"【最终结算（截至{end_date}）】\n") self.result_text.insert(tk.END, f"未清偿本金：{status['未清偿本金']:.2f}\n") self.result_text.insert(tk.END, f"未清偿利息：{status['未清偿利息']:.2f}\n") self.result_text.insert(tk.END, f"合计：{status['合计']:.2f}\n") def export_to_excel(self, calc: InterestFirstCalculator, end_date: date): file_path = filedialog.asksaveasfilename(defaultextension=".xlsx", filetypes=[("Excel文件", "*.xlsx")], initialfile=f"利息计算表_{self.project_name or 'output'}_{end_date}.xlsx") if not file_path: return wb = Workbook() ws = wb.active ws.title = "Sheet1" header_font = Font(bold=True, size=11) border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) center = Alignment(horizontal='center', vertical='center') right_align = Alignment(horizontal='right', vertical='center') proj_title = self.project_entry.get().strip() or "先息后本利息计算" ws.merge_cells('A1:J1') ws['A1'] = proj_title ws['A1'].font = Font(bold=True, size=14) ws['A1'].alignment = center headers = ["结算金额", "结算时间", "应付款金额/计息金额", "应付款时间/利息起算时间", "付款金额", "付款时间", "计息截止时间", "逾期付款利息", "未付款金额（含利息）", "备注"] for col, h in enumerate(headers, 1): cell = ws.cell(row=2, column=col, value=h) cell.font = header_font cell.alignment = center cell.border = border display_rows = calc.build_display_ledger(end_date) row_num = 3 for row in display_rows: rec_type = row['类型'] trans_date = row['日期'] principal_after = row['本金变动后'] interest_to_show = row['归属利息'] end_d = row['计息截止日'] note = row['备注'] if rec_type == '本金增加': ws.cell(row=row_num, column=1, value=round(row['结算金额'], 2)) ws.cell(row=row_num, column=2, value=trans_date.strftime("%Y.%m.%d")) ws.cell(row=row_num, column=3, value=round(row['应付款金额'], 2)) eff_date = row.get('有效起息日', trans_date) ws.cell(row=row_num, column=4, value=eff_date.strftime("%Y.%m.%d") if eff_date else "") ws.cell(row=row_num, column=5, value=0) ws.cell(row=row_num, column=6, value="") ws.cell(row=row_num, column=7, value=end_d.strftime("%Y.%m.%d") if end_d else "") ws.cell(row=row_num, column=8, value=round(interest_to_show, 4)) ws.cell(row=row_num, column=9, value=round(row['未付款总额'], 2)) ws.cell(row=row_num, column=10, value=note) else: # 还款 ws.cell(row=row_num, column=1, value="") ws.cell(row=row_num, column=2, value="") ws.cell(row=row_num, column=3, value=round(row['应付款金额'], 2)) ws.cell(row=row_num, column=4, value="") principal_paid = row.get('还本金额', 0) interest_paid = row.get('还息金额', 0) surplus = row.get('盈余', 0) ws.cell(row=row_num, column=5, value=round(principal_paid + interest_paid + surplus, 2)) ws.cell(row=row_num, column=6, value=trans_date.strftime("%Y.%m.%d")) ws.cell(row=row_num, column=7, value=end_d.strftime("%Y.%m.%d") if end_d else "") ws.cell(row=row_num, column=8, value=round(interest_to_show, 4)) ws.cell(row=row_num, column=9, value=round(row['未付款总额'], 2)) ws.cell(row=row_num, column=10, value=note) for col in range(1, 11): cell = ws.cell(row=row_num, column=col) cell.border = border if col in [1, 3, 5, 8, 9]: cell.alignment = right_align else: cell.alignment = center row_num += 1 summary_row = row_num + 1 status = calc.get_final_status() ws.cell(row=summary_row, column=1, value="最终状态：") ws.cell(row=summary_row+1, column=1, value="未清偿本金：") ws.cell(row=summary_row+1, column=2, value=status['未清偿本金']) ws.cell(row=summary_row+2, column=1, value="未清偿利息：") ws.cell(row=summary_row+2, column=2, value=status['未清偿利息']) ws.cell(row=summary_row+3, column=1, value="合计：") ws.cell(row=summary_row+3, column=2, value=status['合计']) widths = [12, 12, 18, 18, 10, 12, 14, 14, 18, 20] for i, w in enumerate(widths, 1): ws.column_dimensions[get_column_letter(i)].width = w wb.save(file_path) self.result_text.insert(tk.END, f"\nExcel文件已保存至：{file_path}\n") messagebox.showinfo("导出成功", f"Excel文件已保存至：\n{file_path}") def _serialize_transactions(self) -> pd.DataFrame: data = [] for t in self.transactions: data.append({ "交易日期": t.trans_date.strftime("%Y-%m-%d"), "类型": "本金增加" if t.trans_type == "add" else "还款", "金额": t.amount, "起息日": t.effective_date.strftime("%Y-%m-%d") if t.effective_date else "", "项目名称": t.project_name, "备注": t.note }) return pd.DataFrame(data) def save_transactions(self): if not self.transactions: messagebox.showinfo("提示", "没有交易记录可保存"); return file_path = filedialog.asksaveasfilename(defaultextension=".xlsx", filetypes=[("Excel文件", "*.xlsx"), ("CSV文件", "*.csv")], initialfile="交易列表") if not file_path: return try: df = self._serialize_transactions() if file_path.endswith('.csv'): df.to_csv(file_path, index=False, encoding='utf-8-sig') else: df.to_excel(file_path, index=False, engine='openpyxl') messagebox.showinfo("成功", f"交易列表已保存至：\n{file_path}") except Exception as e: messagebox.showerror("保存失败", f"发生错误：{e}") def import_transactions(self): file_path = filedialog.askopenfilename(filetypes=[("支持的文件", "*.xlsx *.csv"), ("Excel文件", "*.xlsx"), ("CSV文件", "*.csv")]) if not file_path: return try: if file_path.endswith('.csv'): df = pd.read_csv(file_path) else: df = pd.read_excel(file_path, engine='openpyxl') col_map = { '交易日期': 'date', '日期': 'date', 'date': 'date', '类型': 'type', 'type': 'type', '金额': 'amount', 'amount': 'amount', '起息日': 'effective', 'effective': 'effective', '项目名称': 'project', 'project': 'project', '备注': 'note', 'note': 'note' } df.rename(columns={col: col_map.get(col, col) for col in df.columns}, inplace=True) required = ['date', 'type', 'amount'] for col in required: if col not in df.columns: raise ValueError(f"缺少必需列：{col}") new_transactions = [] for _, row in df.iterrows(): try: trans_date = self.parse_date(str(row['date'])) if not trans_date: continue t_type = "add" if str(row['type']).strip() in ("本金增加", "add") else "repay" amount = float(row['amount']) effective = None if 'effective' in df.columns and pd.notna(row['effective']): effective = self.parse_date(str(row['effective'])) project = str(row.get('project', self.project_name)) if pd.notna(row.get('project')) else self.project_name note = str(row.get('note', '')) if pd.notna(row.get('note')) else '' new_transactions.append(Transaction(trans_date, t_type, amount, effective, project, note)) except Exception as row_err: print(f"跳过无效行: {row_err}") if not new_transactions: messagebox.showwarning("导入失败", "没有解析到有效交易记录"); return if messagebox.askyesno("导入确认", f"将替换当前 {len(self.transactions)} 条记录为 {len(new_transactions)} 条，确定？"): self.transactions = new_transactions self._sort_transactions() self.refresh_tree() self._exit_edit_mode() self.status_var.set(f"已导入 {len(new_transactions)} 条交易记录") except Exception as e: messagebox.showerror("导入失败", f"读取文件出错：\n{e}") if __name__ == "__main__": root = tk.Tk() app = InterestCalculatorApp(root) root.mainloop()