import pandas as pd
from datetime import datetime

# 读取所有数据
print("正在读取数据...")
consumption_df = pd.read_excel(r'e:\project\python\工具\智慧校园\本部\消费数据\2024学生.xlsx')
grade_df = pd.read_excel(r'e:\project\python\工具\智慧校园\本部\消费数据\年级表.xlsx')
class_df = pd.read_excel(r'e:\project\python\工具\智慧校园\本部\消费数据\班级表.xlsx')

print(f"消费数据: {consumption_df.shape[0]} 条记录")
print(f"年级表: {grade_df.shape[0]} 个年级")
print(f"班级表: {class_df.shape[0]} 个班级")

# 创建年级ID到名称的映射
grade_map = dict(zip(grade_df['id'], grade_df['grade_name']))

# 创建班级ID到名称的映射
class_map = dict(zip(class_df['id'], class_df['class_name']))

# 创建班级ID到年级ID的映射（用于验证）
class_to_grade_map = dict(zip(class_df['id'], class_df['grade_id']))

print("\n正在处理消费数据...")

# 处理消费数据
processed_data = []

for _, row in consumption_df.iterrows():
    grade_id = row['gradeId']
    class_id = row['classId']
    
    # 获取年级和班级名称
    grade_name = grade_map.get(grade_id, f'未知年级({grade_id})')
    class_name = class_map.get(class_id, f'未知班级({class_id})')
    
    # 处理时间格式
    create_time = row['createTime']
    pay_time = row['payTime']
    
    if pd.notna(create_time):
        if isinstance(create_time, str):
            create_time_str = create_time
        else:
            create_time_str = create_time.strftime('%Y-%m-%d %H:%M:%S')
    else:
        create_time_str = ''
    
    if pd.notna(pay_time):
        if isinstance(pay_time, str):
            pay_time_str = pay_time
        else:
            pay_time_str = pay_time.strftime('%Y-%m-%d %H:%M:%S')
    else:
        pay_time_str = ''
    
    # 处理支付状态
    pay_state_map = {0: '确认中', 1: '支付成功', 2: '已取消', 3: '退款'}
    pay_state = pay_state_map.get(row['payState'], f'未知状态({row["payState"]})')
    
    # 处理充值状态
    change_state_map = {0: '充值中', 1: '已取消', 2: '充值成功', 3: '充值失败'}
    change_state = change_state_map.get(row['changeState'], f'未知状态({row["changeState"]})')
    
    record = {
        '学号': str(row['stuNo']),
        '姓名': row['stuName'],
        '年级ID': grade_id,
        '年级名称': grade_name,
        '班级ID': class_id,
        '班级名称': class_name,
        '支付金额(元)': row['totalFee'] / 100,
        '支付状态': pay_state,
        '充值状态': change_state,
        '创建时间': create_time_str,
        '支付时间': pay_time_str,
        '物理卡号': str(int(row['serialNo'])) if pd.notna(row['serialNo']) else '',
        '订单编号': row['outTradeNo'],
        '微信订单号': row['transactionId'] if pd.notna(row['transactionId']) else '',
        '支付手机号': str(row['wxPhone']),
        'teamId': row['teamId']
    }
    processed_data.append(record)

# 创建DataFrame
result_df = pd.DataFrame(processed_data)

# 生成输出文件名
output_file = r'e:\project\python\工具\智慧校园\本部\消费数据\学生消费记录整理.xlsx'

# 写入Excel，使用多个sheet
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
    # Sheet 1: 详细记录
    result_df.to_excel(writer, sheet_name='详细记录', index=False)
    
    # Sheet 2: 按年级汇总
    grade_summary = result_df.groupby(['年级ID', '年级名称']).agg({
        '学号': 'nunique',
        '支付金额(元)': 'sum',
        '姓名': 'count'
    }).reset_index()
    grade_summary.columns = ['年级ID', '年级名称', '学生人数', '总金额(元)', '消费笔数']
    grade_summary = grade_summary.sort_values('年级ID')
    grade_summary.to_excel(writer, sheet_name='按年级汇总', index=False)
    
    # Sheet 3: 按班级汇总
    class_summary = result_df.groupby(['班级ID', '班级名称', '年级名称']).agg({
        '学号': 'nunique',
        '支付金额(元)': 'sum',
        '姓名': 'count'
    }).reset_index()
    class_summary.columns = ['班级ID', '班级名称', '年级名称', '学生人数', '总金额(元)', '消费笔数']
    class_summary = class_summary.sort_values(['年级名称', '班级ID'])
    class_summary.to_excel(writer, sheet_name='按班级汇总', index=False)
    
    # Sheet 4: 按学生汇总
    student_summary = result_df.groupby(['学号', '姓名', '年级名称', '班级名称']).agg({
        '支付金额(元)': 'sum',
        '创建时间': 'count'
    }).reset_index()
    student_summary.columns = ['学号', '姓名', '年级名称', '班级名称', '总金额(元)', '消费笔数']
    student_summary = student_summary.sort_values(['年级名称', '班级名称', '学号'])
    student_summary.to_excel(writer, sheet_name='按学生汇总', index=False)

print(f"\n✅ 数据整理完成！")
print(f"📁 输出文件: {output_file}")
print(f"\n📊 统计信息:")
print(f"   - 总记录数: {len(result_df)}")
print(f"   - 唯一学生数: {result_df['学号'].nunique()}")
print(f"   - 总金额: {result_df['支付金额(元)'].sum():.2f} 元")
print(f"\n📋 Excel包含以下工作表:")
print(f"   1. 详细记录 - 所有消费明细")
print(f"   2. 按年级汇总 - 各年级消费统计")
print(f"   3. 按班级汇总 - 各班级消费统计")
print(f"   4. 按学生汇总 - 各学生消费统计")

# 显示年级分布
print(f"\n🏫 年级分布:")
grade_dist = result_df.groupby('年级名称')['学号'].nunique().sort_values(ascending=False)
for grade, count in grade_dist.items():
    print(f"   {grade}: {count} 人")