#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
生成2025年12月客户开单数据Excel文件
包含字段：客户档案号、姓名、注册时间、来源、开单业绩、时间、归属门店
"""

import pymysql
import pandas as pd
from datetime import datetime
import os

# 数据库配置（从appsettings.json读取）
DB_CONFIG = {
    'host': 'rm-2vccze142rc9a8f58bo.mysql.cn-chengdu.rds.aliyuncs.com',
    'port': 3306,
    'user': 'lvqiansql',
    'password': 'LvQ1@n!20251211',
    'database': 'lqerp_dev',
    'charset': 'utf8mb4'
}

def query_data():
    """查询2025年12月的客户开单数据"""
    connection = None
    try:
        connection = pymysql.connect(**DB_CONFIG)
        
        sql = """
        SELECT 
            COALESCE(kh.dah, '') AS 客户档案号,
            COALESCE(kh.khmc, '') AS 姓名,
            kh.zcsj AS 注册时间,
            COALESCE(kd.khly, '') AS 来源,
            CAST(kd.sfyj AS DECIMAL(18,2)) AS 开单业绩,
            kd.kdrq AS 时间,
            COALESCE(md.dm, '') AS 归属门店
        FROM lq_kd_kdjlb kd
        INNER JOIN lq_khxx kh ON kd.kdhy = kh.F_Id
        LEFT JOIN lq_mdxx md ON kh.gsmd = md.F_Id
        WHERE kd.F_IsEffective = 1
            AND kh.F_IsEffective = 1
            AND YEAR(kd.kdrq) = 2025
            AND MONTH(kd.kdrq) = 12
        ORDER BY kd.kdrq DESC
        """
        
        df = pd.read_sql(sql, connection)
        
        # 格式化时间字段
        if '注册时间' in df.columns:
            df['注册时间'] = pd.to_datetime(df['注册时间']).dt.strftime('%Y-%m-%d %H:%M:%S')
            df['注册时间'] = df['注册时间'].replace('NaT', '')
        
        if '时间' in df.columns:
            df['时间'] = pd.to_datetime(df['时间']).dt.strftime('%Y-%m-%d %H:%M:%S')
        
        # 确保开单业绩为数值类型
        df['开单业绩'] = pd.to_numeric(df['开单业绩'], errors='coerce').fillna(0)
        
        return df
        
    except Exception as e:
        print(f"查询数据时发生错误: {e}")
        raise
    finally:
        if connection:
            connection.close()

def generate_excel(df, output_file='2025年12月客户开单数据.xlsx'):
    """生成Excel文件"""
    try:
        # 创建Excel写入器
        with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
            df.to_excel(writer, sheet_name='客户开单数据', index=False)
            
            # 获取工作表对象进行格式设置
            worksheet = writer.sheets['客户开单数据']
            
            # 设置列宽
            column_widths = {
                'A': 20,  # 客户档案号
                'B': 15,  # 姓名
                'C': 20,  # 注册时间
                'D': 15,  # 来源
                'E': 15,  # 开单业绩
                'F': 20,  # 时间
                'G': 20   # 归属门店
            }
            
            for col, width in column_widths.items():
                worksheet.column_dimensions[col].width = width
            
            # 设置表头样式
            from openpyxl.styles import Font, Alignment, PatternFill
            
            header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
            header_font = Font(bold=True, color='FFFFFF', size=11)
            
            for cell in worksheet[1]:
                cell.fill = header_fill
                cell.font = header_font
                cell.alignment = Alignment(horizontal='center', vertical='center')
            
            # 设置数据对齐方式
            for row in worksheet.iter_rows(min_row=2, max_row=worksheet.max_row):
                for cell in row:
                    if cell.column_letter == 'E':  # 开单业绩列右对齐
                        cell.alignment = Alignment(horizontal='right', vertical='center')
                    else:
                        cell.alignment = Alignment(horizontal='left', vertical='center')
        
        print(f"Excel文件已生成: {output_file}")
        print(f"共 {len(df)} 条记录")
        return output_file
        
    except Exception as e:
        print(f"生成Excel文件时发生错误: {e}")
        raise

def main():
    """主函数"""
    print("开始查询2025年12月客户开单数据...")
    df = query_data()
    
    if df.empty:
        print("未查询到数据")
        return
    
    print(f"查询到 {len(df)} 条记录")
    print("\n前5条数据预览:")
    print(df.head())
    
    # 生成Excel文件
    output_file = f"2025年12月客户开单数据_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
    generate_excel(df, output_file)
    
    print(f"\n文件保存路径: {os.path.abspath(output_file)}")

if __name__ == '__main__':
    main()