buff3.0/new-software/profit_calculator.py

# -*- coding: utf-8 -*-
"""
扫货利润计算工具
用于计算扫货成功数据的利润和利润率
"""

import tkinter as tk
from tkinter import ttk, filedialog, messagebox
import pandas as pd
import pymysql
import os
from datetime import datetime
import sys

# 判断是否为打包环境
if getattr(sys, 'frozen', False):
    APPLICATION_PATH = sys._MEIPASS
else:
    APPLICATION_PATH = os.path.dirname(os.path.abspath(__file__))


class ProfitCalculatorApp:
    def __init__(self, root):
        self.root = root
        self.root.title("扫货利润计算工具 v1.0")
        self.root.geometry("900x650")
        self.root.resizable(True, True)
        
        # 程序图标 (打包后从资源目录读取)
        try:
            if getattr(sys, 'frozen', False):
                icon_path = os.path.join(APPLICATION_PATH, 'icon.ico')
                if os.path.exists(icon_path):
                    root.iconbitmap(icon_path)
        except Exception:
            pass
        
        # 数据库配置 - 从环境变量读取
        self.db_config = {
            'host': os.getenv('DB_HOST', '125.122.32.114'),
            'port': int(os.getenv('DB_PORT', 3306)),
            'user': os.getenv('DB_USER', 'root'),
            'password': os.getenv('DB_PASSWORD', 'Buff123456'),
            'database': os.getenv('DB_NAME', 'buff'),
            'charset': 'utf8mb4'
        }
        
        # 价格缓存 - 启动时加载
        self.price_cache = {}
        
        # 当前处理的结果数据
        self.current_results = []
        
        # 上次输出路径
        self.last_output_path = None
        
        self.setup_ui()
        self.load_price_data()
    
    def setup_ui(self):
        """设置UI界面"""
        # 主框架
        main_frame = ttk.Frame(self.root, padding="15")
        main_frame.pack(fill=tk.BOTH, expand=True)
        
        # 顶部区域 - 标题和状态
        top_frame = ttk.Frame(main_frame)
        top_frame.pack(fill=tk.X, pady=(0, 10))
        
        # 标题
        title_label = ttk.Label(top_frame, text="扫货利润计算工具", 
                               font=("微软雅黑", 20, "bold"))
        title_label.pack(side=tk.LEFT, pady=10)
        
        # 状态区域
        status_frame = ttk.Frame(top_frame)
        status_frame.pack(side=tk.RIGHT)
        
        # 数据库状态
        self.db_status_label = ttk.Label(status_frame, text="数据库: 连接中...", 
                                        font=("微软雅黑", 9))
        self.db_status_label.pack(anchor=tk.E)
        
        # 价格数据加载状态
        self.cache_status_label = ttk.Label(status_frame, text="价格缓存: 加载中...", 
                                            font=("微软雅黑", 9))
        self.cache_status_label.pack(anchor=tk.E)
        
        # 按钮区域
        btn_frame = ttk.Frame(main_frame)
        btn_frame.pack(fill=tk.X, pady=15)
        
        # 左侧按钮
        left_btn_frame = ttk.Frame(btn_frame)
        left_btn_frame.pack(side=tk.LEFT)
        
        self.upload_btn = ttk.Button(left_btn_frame, text="📂 选择Excel文件", 
                                     command=self.upload_excel, width=18)
        self.upload_btn.pack(side=tk.LEFT, padx=5)
        
        self.reload_btn = ttk.Button(left_btn_frame, text="🔄 重新加载数据", 
                                     command=self.reload_data, width=18)
        self.reload_btn.pack(side=tk.LEFT, padx=5)
        
        # 右侧导出按钮
        right_btn_frame = ttk.Frame(btn_frame)
        right_btn_frame.pack(side=tk.RIGHT)
        
        self.export_btn = ttk.Button(right_btn_frame, text="💾 导出Excel", 
                                     command=self.export_excel, width=18)
        self.export_btn.pack(side=tk.LEFT, padx=5)
        self.export_btn.config(state=tk.DISABLED)
        
        self.export_as_btn = ttk.Button(right_btn_frame, text="📤 另存为...", 
                                        command=self.export_as_excel, width=18)
        self.export_as_btn.pack(side=tk.LEFT, padx=5)
        self.export_as_btn.config(state=tk.DISABLED)
        
        # 文件路径显示
        self.file_path_var = tk.StringVar(value="未选择文件")
        self.file_path_label = ttk.Label(main_frame, textvariable=self.file_path_var, 
                                        font=("微软雅黑", 9), foreground="gray")
        self.file_path_label.pack(pady=5)
        
        # 处理结果区域
        result_frame = ttk.LabelFrame(main_frame, text="处理结果", padding="10")
        result_frame.pack(fill=tk.BOTH, expand=True, pady=10)
        
        # 结果文本框
        self.result_text = tk.Text(result_frame, height=18, width=100, 
                                   font=("微软雅黑", 10))
        self.result_text.pack(fill=tk.BOTH, expand=True, side=tk.LEFT)
        
        # 滚动条
        scrollbar = ttk.Scrollbar(result_frame, command=self.result_text.yview)
        scrollbar.pack(side=tk.RIGHT, fill=tk.Y)
        self.result_text.config(yscrollcommand=scrollbar.set)
        
        # 底部说明
        info_frame = ttk.Frame(main_frame)
        info_frame.pack(fill=tk.X, pady=5)
        
        info_label = ttk.Label(info_frame, 
                              text="Excel模版：序号 | 饰品ID | 饰品名称 | 已购磨损值 | 目标价格(美元) | 成交价格(美元) | 成交价格(人民币) | 购买账号 | 扫货时间",
                              font=("微软雅黑", 8), foreground="gray")
        info_label.pack(side=tk.LEFT)
        
        # 统计标签
        self.stats_label = ttk.Label(info_frame, text="", font=("微软雅黑", 9, "bold"))
        self.stats_label.pack(side=tk.RIGHT)
    
    def reload_data(self):
        """重新加载数据"""
        self.result_text.delete(1.0, tk.END)
        self.result_text.insert(tk.END, "正在重新加载数据...\n")
        self.root.update()
        self.load_price_data()
        self.result_text.insert(tk.END, "数据重新加载完成\n")
    
    def load_price_data(self):
        """从数据库加载价格数据到缓存"""
        try:
            conn = pymysql.connect(**self.db_config)
            cursor = conn.cursor(pymysql.cursors.DictCursor)
            
            sql = """
                SELECT goods_id, min_float, max_float, qj_min_price 
                FROM buff_goods_float_price
            """
            cursor.execute(sql)
            rows = cursor.fetchall()
            
            self.price_cache = {}
            for row in rows:
                goods_id = str(row['goods_id'])
                min_float = float(row['min_float'])
                max_float = float(row['max_float'])
                qj_min_price = float(row['qj_min_price'])
                
                if goods_id not in self.price_cache:
                    self.price_cache[goods_id] = []
                
                self.price_cache[goods_id].append({
                    'min_float': min_float,
                    'max_float': max_float,
                    'qj_min_price': qj_min_price
                })
            
            cursor.close()
            conn.close()
            
            self.db_status_label.config(text="数据库: ✅ 已连接", foreground="green")
            self.cache_status_label.config(
                text=f"价格缓存: ✅ {len(self.price_cache)} 个饰品", 
                foreground="green"
            )
            
        except Exception as e:
            self.db_status_label.config(text="数据库: ❌ 连接失败", foreground="red")
            self.cache_status_label.config(text=f"价格缓存: ❌ - {str(e)[:30]}", 
                                           foreground="red")
    
    def find_price_in_range(self, goods_id, wear_value):
        """根据饰品ID和磨损值查找对应区间的底价"""
        goods_id_str = str(goods_id)
        
        if goods_id_str not in self.price_cache:
            return None
        
        ranges = self.price_cache[goods_id_str]
        for r in ranges:
            if r['min_float'] <= wear_value <= r['max_float']:
                return r['qj_min_price']
        
        return None
    
    def upload_excel(self):
        """处理上传的Excel文件"""
        file_path = filedialog.askopenfilename(
            title="选择Excel文件",
            filetypes=[("Excel文件", "*.xlsx *.xls"), ("所有文件", "*.*")]
        )
        
        if not file_path:
            return
        
        self.file_path_var.set(file_path)
        self.result_text.delete(1.0, tk.END)
        self.result_text.insert(tk.END, f"正在处理: {file_path}\n")
        self.result_text.insert(tk.END, "=" * 70 + "\n\n")
        self.root.update()
        
        try:
            df = pd.read_excel(file_path)
            
            if len(df.columns) < 7:
                messagebox.showerror("错误", "Excel文件列数不足，需要至少7列数据")
                return
            
            self.current_results = []
            found_count = 0
            not_found_count = 0
            empty_price_count = 0
            total_profit = 0
            
            for idx, row in df.iterrows():
                try:
                    goods_id = str(row.iloc[1])
                    wear_value = float(row.iloc[3])
                    deal_price_cny_raw = row.iloc[6]
                    
                    # 检查成交价格是否为空
                    if pd.isna(deal_price_cny_raw):
                        name_display = str(row.iloc[2])[:25]
                        if len(str(row.iloc[2])) > 25:
                            name_display += "..."
                        self.result_text.insert(tk.END, 
                            f"⚠️ {row.iloc[0]:>3} | {name_display:<28} | 成交价为空\n")
                        empty_price_count += 1
                        continue
                    
                    deal_price_cny = float(deal_price_cny_raw)
                    
                    range_min_price = self.find_price_in_range(goods_id, wear_value)
                    
                    if range_min_price is not None:
                        profit = range_min_price - deal_price_cny
                        profit_rate = (profit / deal_price_cny * 100) if deal_price_cny > 0 else 0
                        total_profit += profit
                        
                        self.current_results.append({
                            '序号': row.iloc[0],
                            '饰品ID': goods_id,
                            '饰品名称': row.iloc[2],
                            '已购磨损值': wear_value,
                            '目标价格美元': row.iloc[4],
                            '成交价格美元': row.iloc[5],
                            '成交价格人民币': deal_price_cny,
                            '购买账号': row.iloc[7] if len(df.columns) > 7 else '',
                            '扫货时间': row.iloc[8] if len(df.columns) > 8 else '',
                            '区间底价': range_min_price,
                            '利润': round(profit, 2),
                            '利润率(%)': round(profit_rate, 2)
                        })
                        found_count += 1
                        
                        name_display = str(row.iloc[2])[:25] + "..." if len(str(row.iloc[2])) > 25 else row.iloc[2]
                        self.result_text.insert(tk.END, 
                            f"✅ {row.iloc[0]:>3} | {name_display:<28} | 利润: ¥{round(profit, 2):>8} ({round(profit_rate, 1):>5}%)\n")
                    else:
                        not_found_count += 1
                        name_display = str(row.iloc[2])[:25] + "..." if len(str(row.iloc[2])) > 25 else row.iloc[2]
                        self.result_text.insert(tk.END, 
                            f"⚠️ {row.iloc[0]:>3} | {name_display:<28} | 未匹配到区间\n")
                
                except Exception as e:
                    self.result_text.insert(tk.END, 
                        f"❌ 序号 {row.iloc[0]}: {str(e)[:50]}\n")
            
            if self.current_results:
                # 生成输出文件名
                timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
                base_name = os.path.splitext(os.path.basename(file_path))[0]
                self.last_output_path = os.path.join(
                    os.path.dirname(file_path), 
                    f"{base_name}_利润分析_{timestamp}.xlsx"
                )
                
                result_df = pd.DataFrame(self.current_results)
                result_df.to_excel(self.last_output_path, index=False)
                
                self.result_text.insert(tk.END, "\n" + "=" * 70 + "\n")
                self.result_text.insert(tk.END, f"✅ 处理完成!\n")
                self.result_text.insert(tk.END, f"   成功匹配: {found_count} 条\n")
                self.result_text.insert(tk.END, f"   成交价为空: {empty_price_count} 条\n")
                self.result_text.insert(tk.END, f"   未匹配区间: {not_found_count} 条\n")
                self.result_text.insert(tk.END, f"   总利润: ¥{round(total_profit, 2)}\n")
                self.result_text.insert(tk.END, f"   结果已保存: {self.last_output_path}\n")
                
                # 启用导出按钮
                self.export_btn.config(state=tk.NORMAL)
                self.export_as_btn.config(state=tk.NORMAL)
                
                # 更新统计
                stats_text = f"成功: {found_count} | 成交价空: {empty_price_count} | 未匹配: {not_found_count} | 利润: ¥{round(total_profit, 2)}"
                self.stats_label.config(text=stats_text, foreground="green")
                
                messagebox.showinfo("处理完成", 
                    f"成功匹配: {found_count} 条\n成交价为空: {empty_price_count} 条\n未匹配区间: {not_found_count} 条\n总利润: ¥{round(total_profit, 2)}\n\n结果已保存")
            else:
                self.stats_label.config(text="无匹配数据", foreground="orange")
                messagebox.showwarning("无匹配数据", "没有找到任何匹配的饰品数据")
                
        except Exception as e:
            messagebox.showerror("处理错误", f"处理文件时出错:\n{str(e)}")
            self.result_text.insert(tk.END, f"\n❌ 错误: {str(e)}")
    
    def export_excel(self):
        """导出上次的结果到Excel"""
        if not self.current_results:
            messagebox.showwarning("无数据", "没有可导出的数据，请先选择Excel文件处理")
            return
        
        if self.last_output_path and os.path.exists(self.last_output_path):
            result_df = pd.DataFrame(self.current_results)
            result_df.to_excel(self.last_output_path, index=False)
            messagebox.showinfo("导出成功", f"已导出到:\n{self.last_output_path}")
        else:
            self.export_as_excel()
    
    def export_as_excel(self):
        """另存为Excel"""
        if not self.current_results:
            messagebox.showwarning("无数据", "没有可导出的数据，请先选择Excel文件处理")
            return
        
        file_path = filedialog.asksaveasfilename(
            title="另存为",
            defaultextension=".xlsx",
            filetypes=[("Excel文件", "*.xlsx"), ("所有文件", "*.*")],
            initialfile=f"利润分析_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
        )
        
        if not file_path:
            return
        
        try:
            result_df = pd.DataFrame(self.current_results)
            result_df.to_excel(file_path, index=False)
            self.last_output_path = file_path
            messagebox.showinfo("导出成功", f"已导出到:\n{file_path}")
        except Exception as e:
            messagebox.showerror("导出失败", f"导出时出错:\n{str(e)}")


def main():
    root = tk.Tk()
    app = ProfitCalculatorApp(root)
    root.mainloop()


if __name__ == "__main__":
    main()