#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
生成三位一体对比表的详细报告
"""

import pandas as pd

def main():
    # 读取Excel文件
    df = pd.read_excel("许可事项三位一体对比表_v2.xlsx")
    
    # 生成文本报告
    with open("三位一体对比报告.txt", "w", encoding="utf-8") as f:
        f.write("="*80 + "\n")
        f.write("许可事项三位一体对比表 - 详细报告\n")
        f.write("="*80 + "\n\n")
        
        f.write(f"总事项数量: {len(df)}\n\n")
        
        # 统计各列的勾选情况
        client_count = (df['客户提供'] == '✓').sum()
        received_count = (df['已接收'] == '✓').sum()
        db_count = (df['已入库'] == '✓').sum()
        
        f.write(f"1. 客户提供的事项: {client_count}\n")
        f.write(f"2. 已接收的事项: {received_count}\n")
        f.write(f"3. 已入库的事项: {db_count}\n\n")
        
        # 统计状态分布
        f.write("状态分布:\n")
        f.write("-" * 40 + "\n")
        status_counts = df['状态说明'].value_counts()
        for status, count in status_counts.items():
            f.write(f"  {status}: {count}\n")
        
        f.write("\n" + "="*80 + "\n")
        f.write("需要关注的事项\n")
        f.write("="*80 + "\n\n")
        
        # 完整的事项（三方都有）
        complete = df[df['状态说明'] == '完整（三方都有）']
        f.write(f"【完整（三方都有）】({len(complete)} 项)\n")
        if len(complete) > 0:
            for idx, row in complete.iterrows():
                f.write(f"  {row['序号']}. {row['事项名称']}\n")
        f.write("\n")
        
        # 待入库的事项
        to_import = df[df['状态说明'] == '待入库']
        f.write(f"【待入库】({len(to_import)} 项)\n")
        f.write("说明: 客户要求显示，已接收文件，但尚未导入数据库\n")
        if len(to_import) > 0:
            for idx, row in to_import.iterrows():
                f.write(f"  {row['序号']}. {row['事项名称']}\n")
        f.write("\n")
        
        # 缺少源文件的事项
        missing_source = df[df['状态说明'] == '缺少源文件']
        f.write(f"【缺少源文件】({len(missing_source)} 项)\n")
        f.write("说明: 客户要求显示，已入库，但缺少原始文件\n")
        if len(missing_source) > 0:
            for idx, row in missing_source.iterrows():
                f.write(f"  {row['序号']}. {row['事项名称']}\n")
        f.write("\n")
        
        # 缺少文件和数据的事项
        missing_all = df[df['状态说明'] == '缺少文件和数据']
        f.write(f"【缺少文件和数据】({len(missing_all)} 项)\n")
        f.write("说明: 客户要求显示，但既没有文件也没有入库\n")
        if len(missing_all) > 0:
            for idx, row in missing_all.head(50).iterrows():
                f.write(f"  {row['序号']}. {row['事项名称']}\n")
            if len(missing_all) > 50:
                f.write(f"  ... 还有 {len(missing_all) - 50} 项\n")
        f.write("\n")
        
        # 未入库且客户未要求
        not_required = df[df['状态说明'] == '未入库且客户未要求']
        f.write(f"【未入库且客户未要求】({len(not_required)} 项)\n")
        f.write("说明: 已接收文件但未入库，且客户未要求显示\n")
        if len(not_required) > 0:
            for idx, row in not_required.head(50).iterrows():
                f.write(f"  {row['序号']}. {row['事项名称']}\n")
            if len(not_required) > 50:
                f.write(f"  ... 还有 {len(not_required) - 50} 项\n")
        f.write("\n")
        
        # 已接收且已入库（客户未要求）
        received_and_db = df[df['状态说明'] == '客户未要求']
        f.write(f"【已接收且已入库（客户未要求）】({len(received_and_db)} 项)\n")
        f.write("说明: 已接收并入库，但客户未要求显示\n")
        if len(received_and_db) > 0:
            for idx, row in received_and_db.iterrows():
                f.write(f"  {row['序号']}. {row['事项名称']}\n")
        f.write("\n")
        
        # 仅数据库有
        only_db = df[df['状态说明'] == '仅数据库有']
        f.write(f"【仅数据库有】({len(only_db)} 项)\n")
        f.write("说明: 仅在数据库中，没有源文件且客户未要求\n")
        if len(only_db) > 0:
            for idx, row in only_db.iterrows():
                f.write(f"  {row['序号']}. {row['事项名称']}\n")
        f.write("\n")
        
        f.write("="*80 + "\n")
        f.write("对比表文件: 许可事项三位一体对比表_v2.xlsx\n")
        f.write("="*80 + "\n")
    
    print("报告已生成: 三位一体对比报告.txt")
    print(f"总事项数: {len(df)}")
    print(f"客户提供: {client_count}, 已接收: {received_count}, 已入库: {db_count}")

if __name__ == "__main__":
    main()