#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
检查洗毛巾记录的时间字段差异问题
比较CreateTime和SendTime的差异
"""

import pymysql
from datetime import datetime

# 数据库配置
DB_CONFIG = {
    'host': 'rm-2vccze142rc9a8f58bo.mysql.cn-chengdu.rds.aliyuncs.com',
    'port': 3306,
    'user': 'lvqiansql',
    'password': 'LvQ1@n!20251211',
    'database': 'lqerp_dev',
    'charset': 'utf8mb4',
    'connect_timeout': 60,
    'read_timeout': 300,
    'write_timeout': 300
}

def check_time_issue():
    """检查时间字段差异"""
    connection = None
    try:
        connection = pymysql.connect(**DB_CONFIG)
        cursor = connection.cursor(pymysql.cursors.DictCursor)
        
        store_id = '1649328471923847187'
        month_str = '202512'
        
        # 查询所有相关记录
        sql = """
        SELECT 
            F_Id,
            F_BatchNumber,
            F_FlowType,
            F_ProductType,
            F_Quantity,
            F_TotalPrice,
            F_CreateTime,
            F_SendTime,
            COALESCE(F_SendTime, F_CreateTime) as StatTime,
            DATE_FORMAT(F_CreateTime, '%%Y%%m') as CreateMonth,
            DATE_FORMAT(COALESCE(F_SendTime, F_CreateTime), '%%Y%%m') as StatMonth
        FROM lq_laundry_flow
        WHERE F_IsEffective = 1
            AND F_FlowType = 0
            AND F_StoreId = %s
            AND (
                DATE_FORMAT(F_CreateTime, '%%Y%%m') = %s
                OR DATE_FORMAT(COALESCE(F_SendTime, F_CreateTime), '%%Y%%m') = %s
            )
        ORDER BY COALESCE(F_SendTime, F_CreateTime)
        """
        
        cursor.execute(sql, (store_id, month_str, month_str))
        records = cursor.fetchall()
        
        print("=" * 120)
        print("洗毛巾记录时间字段差异检查")
        print("=" * 120)
        print(f"\n查询条件：门店ID={store_id}, 月份={month_str}")
        print(f"\n共查询到 {len(records)} 条记录\n")
        
        # 按CreateTime统计
        create_time_records = [r for r in records if r['CreateMonth'] == month_str]
        print(f"按CreateTime过滤（12月创建）: {len(create_time_records)} 条")
        
        # 按StatTime统计（工资计算使用的逻辑）
        stat_time_records = [r for r in records if r['StatMonth'] == month_str]
        print(f"按StatTime过滤（12月统计）: {len(stat_time_records)} 条")
        
        # 找出差异
        create_time_ids = {r['F_Id'] for r in create_time_records}
        stat_time_ids = {r['F_Id'] for r in stat_time_records}
        
        diff_ids = stat_time_ids - create_time_ids
        if diff_ids:
            print(f"\n⚠️  差异记录（StatTime在12月，但CreateTime不在12月）: {len(diff_ids)} 条")
            print("-" * 120)
            print(f"{'批次号':<20} {'产品类型':<10} {'CreateTime':<20} {'SendTime':<20} {'总费用':<10}")
            print("-" * 120)
            diff_total = 0
            for r in records:
                if r['F_Id'] in diff_ids:
                    print(f"{r['F_BatchNumber']:<20} "
                          f"{r['F_ProductType'] or '':<10} "
                          f"{str(r['F_CreateTime']):<20} "
                          f"{str(r['F_SendTime'] or ''):<20} "
                          f"{r['F_TotalPrice']:<10.2f}")
                    diff_total += r['F_TotalPrice']
            print("-" * 120)
            print(f"差异金额总计: {diff_total:.2f} 元")
        
        # 汇总统计
        create_total = sum(r['F_TotalPrice'] for r in create_time_records)
        stat_total = sum(r['F_TotalPrice'] for r in stat_time_records)
        
        print(f"\n汇总统计:")
        print(f"  按CreateTime统计金额: {create_total:.2f} 元")
        print(f"  按StatTime统计金额: {stat_total:.2f} 元")
        print(f"  差异金额: {stat_total - create_total:.2f} 元")
        
        return records
        
    except Exception as e:
        print(f"查询失败: {str(e)}")
        import traceback
        traceback.print_exc()
        return None
    finally:
        if connection:
            connection.close()

if __name__ == '__main__':
    check_time_issue()