#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
查询绿纤明信店2025年12月毛巾总成本
"""

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 query_laundry_cost():
    """查询绿纤明信店2025年12月毛巾总成本"""
    connection = None
    try:
        connection = pymysql.connect(**DB_CONFIG)
        cursor = connection.cursor(pymysql.cursors.DictCursor)
        
        # 门店ID：绿纤明信店
        store_id = '1649328471923847187'
        month_str = '202512'  # 2025年12月
        
        # 查询总成本
        sql = """
        SELECT 
            F_StoreId as 门店ID,
            SUM(F_TotalPrice) as 毛巾总成本,
            COUNT(*) as 记录数量,
            MIN(COALESCE(F_SendTime, F_CreateTime)) as 最早记录时间,
            MAX(COALESCE(F_SendTime, F_CreateTime)) as 最晚记录时间
        FROM lq_laundry_flow
        WHERE F_IsEffective = 1
            AND F_FlowType = 0
            AND F_StoreId = %s
            AND DATE_FORMAT(COALESCE(F_SendTime, F_CreateTime), '%%Y%%m') = %s
        GROUP BY F_StoreId
        """
        
        cursor.execute(sql, (store_id, month_str))
        result = cursor.fetchone()
        
        if result:
            print("=" * 60)
            print("绿纤明信店 2025年12月 毛巾成本统计")
            print("=" * 60)
            print(f"门店ID: {result['门店ID']}")
            print(f"毛巾总成本: {result['毛巾总成本']:.2f} 元")
            print(f"记录数量: {result['记录数量']} 条")
            print(f"最早记录时间: {result['最早记录时间']}")
            print(f"最晚记录时间: {result['最晚记录时间']}")
            print("=" * 60)
            
            # 查询详细信息
            detail_sql = """
            SELECT 
                F_Id as 记录ID,
                F_BatchNumber as 批次号,
                F_ProductType as 产品类型,
                F_Quantity as 数量,
                F_LaundryPrice as 清洗单价,
                F_TotalPrice as 总费用,
                F_SendTime as 送出时间,
                F_CreateTime as 创建时间,
                COALESCE(F_SendTime, F_CreateTime) as 统计时间
            FROM lq_laundry_flow
            WHERE F_IsEffective = 1
                AND F_FlowType = 0
                AND F_StoreId = %s
                AND DATE_FORMAT(COALESCE(F_SendTime, F_CreateTime), '%%Y%%m') = %s
            ORDER BY COALESCE(F_SendTime, F_CreateTime)
            """
            
            cursor.execute(detail_sql, (store_id, month_str))
            details = cursor.fetchall()
            
            if details:
                print(f"\n详细记录列表（共{len(details)}条）:")
                print("-" * 100)
                print(f"{'批次号':<20} {'产品类型':<10} {'数量':<8} {'单价':<10} {'总费用':<12} {'统计时间':<20}")
                print("-" * 100)
                for detail in details:
                    print(f"{detail['批次号'] or '':<20} "
                          f"{detail['产品类型'] or '':<10} "
                          f"{detail['数量']:<8} "
                          f"{detail['清洗单价']:<10.2f} "
                          f"{detail['总费用']:<12.2f} "
                          f"{str(detail['统计时间']):<20}")
                print("-" * 100)
                print(f"总计: {result['毛巾总成本']:.2f} 元")
        else:
            print(f"未找到绿纤明信店（{store_id}）2025年12月的毛巾送出记录")
            
        return result
        
    except Exception as e:
        print(f"查询失败: {str(e)}")
        import traceback
        traceback.print_exc()
        return None
    finally:
        if connection:
            connection.close()

if __name__ == '__main__':
    query_laundry_cost()