import os
from flask import flash, redirect, url_for, get_flashed_messages

from flask import Blueprint, render_template, request, g
from sqlalchemy import text, or_, and_, distinct
from exts import db
from decorators import login_required
from forms.wgcommform import MealSetupForm
from models.iccard import *
from forms.wgcommform import *  # AddControllerForm, LoginForm
import vwAppConfig
import datetime
# from openpyxl import Workbook
from urllib.parse import quote
from flask import make_response
from flask import send_file
import re

__all__ = [
    "bp",
    "web_report_meal",
    "web_report_meal_create",
    "web_report_meal_setup",
]

from vwAppConfigParam import vwAppConfigParam

bp = Blueprint('wgcomm_report_meal', __name__, url_prefix='/')


def get_last_month_range() -> [datetime.date, datetime.date]:
    today = datetime.date.today()  # 当前日期（2025-07-12）
    # 上Unit月最后一天 = 当月第一天减去1天
    first_day_of_current_month = today.replace(day=1)
    last_day_of_last_month = first_day_of_current_month - datetime.timedelta(days=1)
    # 上Unit月第一天 = 最后一天的day替换为1
    first_day_of_last_month = last_day_of_last_month.replace(day=1)
    return first_day_of_last_month, last_day_of_last_month


@bp.route('/report_meal', methods=['GET', 'POST'])
@login_required
def web_report_meal():  #
    return web_report_meal_create()


@bp.route('/report_meal_create', methods=['POST'])
@login_required
def web_report_meal_create():
    url4htmltemplate = "wgcomm/report-meal.html"  # template 实际操作界面
    url4redirect = "wgcomm_report_meal.web_report_meal"  # redirect 返回上一Unit界面 主界面
    get_flashed_messages() #  2025-07-22 11:38:05 清空缓存内容信息
    wgform = AddUserForm()
    if request.form.get('f_BeginYMD'):  # 如果有日期 生成新的报表
        wgform = AddUserFormWithoutCard(request.form)
        if wgform.submitcancel.data:  # 取消时 退出到列表
            return redirect(url_for(url4redirect))  # url_for("wgcomm.controller_list"))  # 要跳转 controller_list()
        if wgform.submitsaveandadd.data:  # 下载上次生成的报表
            file_path = f"{vwAppConfigParam.wgcloudpathdir}MealReport.xlsx  "
            if os.path.exists(file_path):
                response = make_response(send_file(file_path))
                safe_name = quote("MealReport.xlsx  ")  # 文件名URL编码
                response.headers["Content-Disposition"] = "attachment; filename*=UTF-8''{}".format(safe_name)
                return response
            else:
                flash("Report does not exist, please regenerate!  ")
                return redirect(url_for(url4redirect))
        f_BeginYMD = wgform.f_BeginYMD.data
        f_EndYMD = wgform.f_EndYMD.data
        f_BeginYMD = vwAppConfig.getDateTimeFromStr(f_BeginYMD)  # 2023-12-16 18:55:51
        f_EndYMD = vwAppConfig.getDateTimeFromStr(f_EndYMD)  # 2023-12-16 18:56:08
        f_GroupName = request.form.get('f_GroupName')
        # info = startDeal(vwAppConfig.getStrOfDatetimeYMD(f_BeginYMD),
        #                  vwAppConfig.getStrOfDatetimeYMD(f_EndYMD),
        #                  f_GroupName)  # 2025-07-12 17:27:07
        try:
            info = startDeal(vwAppConfig.getStrOfDatetimeYMD(f_BeginYMD),
                             vwAppConfig.getStrOfDatetimeYMD(f_EndYMD),
                             f_GroupName)  # 2025-07-12 17:27:07
            vwAppConfig.vwlog('{}--{} '.format(g.user.f_OperatorName, "Report generated successfully!  "))  # 增加日志
            # flash("Report generated successfully!  ")
            return info  # 下载文件
        except Exception as e:
            print(e)
            flash("Report generation failed!  ")
            vwAppConfig.vwlog('{}--{}[{}] '.format(g.user.f_OperatorName, "Report generation failed!  ", e))  # 增加日志
            wgform = AddUserForm(request.form)
            # return render_template(url4htmltemplate, wgform=wgform)
    query_obj = TBGroup.query.order_by(TBGroup.f_GroupNO)
    departentlist = query_obj.all()
    first_day, last_day = get_last_month_range()
    wgform.f_BeginYMD.data = first_day
    wgform.f_EndYMD.data = last_day
    context = {
        "wgform": wgform,
        "departments": departentlist
    }
    return render_template(url4htmltemplate, **context)  # wgform=wgform


# 获取Time和日期的查询
def getSqlOfDateTime(beginYMD, endYMD) -> str:
    strSql = "  ([f_ReadDate]>='{}') ".format(beginYMD)
    strSql += " AND "
    # strSql += "  ([f_ReadDate]<= '{}') ".format(endYMD)
    strSql += "  ([f_ReadDate]<= '{} 23:59:59') ".format(endYMD)
    return strSql


def get_sql_find_swipe_record_4_meal(
        str_base_info: str,
        from_main_dt: str,
        str_time_con: str = "",
        group_min_no: int = 0,
        group_id_of_min_no: int = 0,
        group_max_no: int = 0,
        find_name: str = "",
        find_card: int = 0,
        find_consumer_id: int = 0
) -> str:
    """
    动态生成 SQL 查询语句，支持多条件筛选

    参数:
        str_base_info: SELECT 字段部分
        from_main_dt: 主表名
        str_time_con: Time条件表达式
        group_min_no: 最小Department编号
        group_id_of_min_no: 最小Department编号对应的ID
        group_max_no: 最大Department编号
        find_name: Name搜索关键词
        find_card: Card NO搜索值
        find_consumer_id: 消费者ID

    返回:
        完整的 SQL 查询语句
    """

    # 安全过滤函数（防止SQL注入）
    def safe_sql_value(value):
        if isinstance(value, str):
            # 转义单引号并移除危险字符
            value = re.sub(r"[;\\-]", "", value)
            value = value.replace("'", "''")
            return f"'{value}'"
        return str(value)

    # 基础 WHERE 条件
    conditions = ["1=1"]

    # Time条件处理
    if str_time_con:
        conditions.append(f"({str_time_con})")

    # 消费者ID精确查询
    if find_consumer_id > 0:
        conditions.append(f"t_b_Consumer.f_ConsumerID = {find_consumer_id}")
        join_clause = f"""
            (((t_b_Consumer
            INNER JOIN {from_main_dt} ON t_b_Consumer.f_ConsumerID = {from_main_dt}.f_ConsumerID)
            INNER JOIN t_b_Reader ON t_b_Reader.f_ReaderID = {from_main_dt}.f_ReaderID)
            INNER JOIN t_b_Controller ON t_b_Reader.f_ControllerID = t_b_Controller.f_ControllerID)
            LEFT JOIN t_b_Group ON t_b_Consumer.f_GroupID = t_b_Group.f_GroupID
        """
        #  2025-07-17 12:24:42
        join_clause = f"""
            (t_b_Consumer
            INNER JOIN {from_main_dt} ON t_b_Consumer.f_ConsumerID = {from_main_dt}.f_ConsumerID)
            LEFT JOIN t_b_Group ON t_b_Consumer.f_GroupID = t_b_Group.f_GroupID
        """
        join_clause = f"""
            (((t_b_Consumer
            INNER JOIN {from_main_dt} ON t_b_Consumer.f_ConsumerID = {from_main_dt}.f_ConsumerID)
            INNER JOIN t_b_door ON t_b_door.f_DoorID = {from_main_dt}.f_DoorID)
            )
            LEFT JOIN t_b_Group ON t_b_Consumer.f_GroupID = t_b_Group.f_GroupID
        """
    else:
        # Name模糊查询
        if find_name:
            safe_name = safe_sql_value(f"%{find_name}%")
            conditions.append(f"t_b_Consumer.f_ConsumerName LIKE {safe_name}")

        # Card NO精确查询
        if find_card > 0:
            conditions.append(f"{from_main_dt}.f_CardNO = {find_card}")

        # Department范围查询
        if group_min_no > 0:
            if group_min_no >= group_max_no:
                conditions.append(f"t_b_Group.f_GroupID = {group_id_of_min_no}")
            else:
                conditions.append(f"t_b_Group.f_GroupNO BETWEEN {group_min_no} AND {group_max_no}")

            # join_clause = f"""
            #     (((t_b_Consumer
            #     INNER JOIN {from_main_dt} ON t_b_Consumer.f_ConsumerID = {from_main_dt}.f_ConsumerID)
            #     INNER JOIN t_b_Reader ON t_b_Reader.f_ReaderID = {from_main_dt}.f_ReaderID)
            #     INNER JOIN t_b_Controller ON t_b_Reader.f_ControllerID = t_b_Controller.f_ControllerID)
            #     INNER JOIN t_b_Group ON t_b_Consumer.f_GroupID = t_b_Group.f_GroupID
            # """
            join_clause = f"""
                (t_b_Consumer
                INNER JOIN {from_main_dt} ON t_b_Consumer.f_ConsumerID = {from_main_dt}.f_ConsumerID)                
                INNER JOIN t_b_Group ON t_b_Consumer.f_GroupID = t_b_Group.f_GroupID
            """
            join_clause = f"""
                (((t_b_Consumer
                INNER JOIN {from_main_dt} ON t_b_Consumer.f_ConsumerID = {from_main_dt}.f_ConsumerID)
                INNER JOIN t_b_door ON t_b_door.f_DoorID = {from_main_dt}.f_DoorID)
                )
                INNER JOIN t_b_Group ON t_b_Consumer.f_GroupID = t_b_Group.f_GroupID
            """
        else:
            join_clause = f"""
                ((({from_main_dt}
                INNER JOIN t_b_Consumer ON t_b_Consumer.f_ConsumerID = {from_main_dt}.f_ConsumerID)
                INNER JOIN t_b_Reader ON t_b_Reader.f_ReaderID = {from_main_dt}.f_ReaderID)
                INNER JOIN t_b_Controller ON t_b_Reader.f_ControllerID = t_b_Controller.f_ControllerID)
                LEFT JOIN t_b_Group ON t_b_Consumer.f_GroupID = t_b_Group.f_GroupID
            """
            join_clause = f"""
                ({from_main_dt}
                INNER JOIN t_b_Consumer ON t_b_Consumer.f_ConsumerID = {from_main_dt}.f_ConsumerID)
                LEFT JOIN t_b_Group ON t_b_Consumer.f_GroupID = t_b_Group.f_GroupID
            """
            join_clause = f"""
                ((({from_main_dt}
                INNER JOIN t_b_Consumer ON t_b_Consumer.f_ConsumerID = {from_main_dt}.f_ConsumerID)
                INNER JOIN t_b_door ON t_b_door.f_DoorID = {from_main_dt}.f_DoorID)
                )
                LEFT JOIN t_b_Group ON t_b_Consumer.f_GroupID = t_b_Group.f_GroupID
            """
    # 构建完整 SQL
    where_clause = " AND ".join(conditions)
    return f"""
        {str_base_info}
        FROM {join_clause}
        WHERE {where_clause}
    """


def get_query_consumer_condition_str(
        str_base_info: str,
        from_main_dt: str = "",  # 注意：此参数在函数中未使用，但保留参数位置
        str_time_con: str = "",
        group_min_no: int = 0,
        group_id_of_min_no: int = 0,
        group_max_no: int = 0,
        find_name: str = '',
        find_card: int = 0,
        find_consumer_id: int = 0
) -> str:
    """
    构建消费者查询条件的SQL语句

    参数:
        str_base_info: SELECT子句部分
        from_main_dt: 主表名（未使用）
        str_time_con: Time条件（未使用）
        group_min_no: 最小Department编号
        group_id_of_min_no: 最小Department编号对应ID
        group_max_no: 最大Department编号
        find_name: Name搜索关键词
        find_card: Card NO搜索值
        find_consumer_id: 消费者ID

    返回:
        完整的SQL查询语句
    """

    # 安全处理字符串值（防SQL注入）
    def safe_sql_value(value: str) -> str:
        escaped = value.replace("'", "''")  # 转义单引号
        return f"N'{escaped}'"  # Unicode安全格式[1](@ref)

    try:
        # 基础WHERE条件
        conditions = ["1>0"]
        if str_time_con:
            conditions.append(f"({str_time_con})")

        # 消费者ID精确查询
        if find_consumer_id > 0:
            return f"""
                {str_base_info}
                FROM t_b_Consumer
                LEFT JOIN t_b_Group ON t_b_Consumer.f_GroupID = t_b_Group.f_GroupID
                WHERE t_b_Consumer.f_ConsumerID = {find_consumer_id}
            """

        # Name模糊查询
        if find_name:
            like_value = f"%{find_name}%"
            conditions.append(f"t_b_Consumer.f_ConsumerName LIKE {safe_sql_value(like_value)}")

        # Card NO精确查询
        if find_card > 0:
            conditions.append(f"t_b_Consumer.f_CardNO = {find_card}")

        # Department范围查询
        if group_min_no > 0:
            if group_min_no >= group_max_no:
                join_condition = f"AND t_b_Group.f_GroupID = {group_id_of_min_no}"
                sql = f"""
                    {str_base_info}
                    FROM t_b_Consumer
                    INNER JOIN t_b_Group ON (
                        t_b_Consumer.f_GroupID = t_b_Group.f_GroupID 
                        {join_condition}
                    )
                """
            else:
                range_condition = (
                    f"AND t_b_Group.f_GroupNO >= {group_min_no} "
                    f"AND t_b_Group.f_GroupNO <= {group_max_no}"
                )
                sql = f"""
                    {str_base_info}
                    FROM t_b_Consumer
                    INNER JOIN t_b_Group ON (
                        t_b_Consumer.f_GroupID = t_b_Group.f_GroupID 
                        {range_condition}
                    )
                """
        else:
            sql = f"""
                {str_base_info}
                FROM t_b_Consumer
                LEFT JOIN t_b_Group ON t_b_Consumer.f_GroupID = t_b_Group.f_GroupID
            """

        # 组合WHERE条件
        where_clause = " AND ".join(conditions)
        return f"{sql} WHERE {where_clause}"

    except Exception as ex:
        # 实际项目中应使用日志系统
        print(f"SQL generation error:   {ex}")
        vwAppConfig.vwlog('{}--{}[{}]'.format(g.user.f_OperatorName, "".format(
            f"SQL generation error:   {ex}"), "ex"))  # 2024-01-13 12:59:02 增加日志
        return ""  # 返回空字符串避免中断流程


# 步骤1：模拟 DataView 过滤功能
def filter_data(table, condition):
    """实现类似 DataView.RowFilter 的过滤功能[2,10](@ref)"""
    if not condition:
        return table
    key, value = condition.split("=")
    key = key.strip()
    value = int(value.strip())  # 根据实际类型调整
    return [row for row in table if row.get(key) == value]


def startDeal(beginYMD, endYMD, f_GroupName):  # 开始处理生成报表
    groupMinNO = 0
    groupName = f_GroupName  # "" # "b11"
    groupNameCondition = "1=1"
    if groupName != '':
        groupNameCondition = f"(f_GroupName == '{groupName}' or f_GroupName like '{groupName}/%')"
    strSqlBaseInfo = " SELECT t_d_SwipeRecord2022.f_RecID, t_d_SwipeRecord2022.f_CardNO,  "
    strSqlBaseInfo += "        t_b_Consumer.f_ConsumerNO, t_b_Consumer.f_ConsumerName, "
    strSqlBaseInfo += "        t_b_Group.f_GroupName, t_d_SwipeRecord2022.f_ReadDate, t_b_door.f_DoorName  as f_ReaderName, t_b_door.f_DoorID as f_ReaderID, t_b_Consumer.f_ConsumerID, "  # 2025-07-17 12:11:49 不定位到读卡器
    strSqlBaseInfo += "        t_d_SwipeRecord2022.f_Character, t_d_SwipeRecord2022.f_Desc "  # 2025-07-17 12:22:15
    # 2011 - 12 - 27_23: 12:17
    # 显示所有记录[存在user记录]
    strSpecialCon = " ( 1=1 ) "
    if getSqlOfDateTime(beginYMD, endYMD) != "":
        strSpecialCon += " AND " + getSqlOfDateTime(beginYMD, endYMD)

    strSpecialCon += " AND " + groupNameCondition
    strSql = get_sql_find_swipe_record_4_meal(strSqlBaseInfo, "t_d_SwipeRecord2022", strSpecialCon)

    # //*************************取刷卡记录[有Time约束]
    query_obj = db.session.execute(text(strSql))
    columnsSwipeRecord = query_obj.keys()
    objt_d_SwipeRecord = query_obj.fetchall()  # .all()

    # 取Department[有Department约束]
    strSql = " SELECT a.f_GroupID,a.f_GroupName,  0 as f_MorningCount, 0 as f_LunchCount, 0 as f_EveningCount ,0 as f_OtherCount, 0 as f_groupTotalTimes, 0.01 as f_groupTotal, b.f_Enabled, b.f_Morning,b.f_Lunch, b.f_Evening,b.f_Other from t_b_Group a LEFT JOIN t_b_group4Meal b ON a.f_GroupID = b.f_GroupID "
    strSql += " WHERE " + groupNameCondition
    strSql += " order by f_GroupName  + '\\' ASC"
    query_obj = db.session.execute(text(strSql))
    objtable = query_obj.fetchall()
    # # 转换为列表（可Edit）
    # objt_b_group4Meal = [list(row) for row in objt_b_group4Meal]

    columns = query_obj.keys()  # [desc[0] for desc in query_obj.description]  # 获取列名
    objt_b_group4Meal = [dict(zip(columns, row)) for row in objtable]

    # 按Department的Statistics  表  不存在的给初值
    for itm in objt_b_group4Meal:
        if itm['f_Morning'] is None:
            # 设置默认值
            itm['f_MorningCount'] = 0
            itm['f_LunchCount'] = 0
            itm['f_EveningCount'] = 0
            itm['f_OtherCount'] = 0

            itm['f_Morning'] = 0.00
            itm['f_Lunch'] = 0.00
            itm['f_Evening'] = 0.00
            itm['f_Other'] = 0.00
            itm['f_Enabled'] = 1  # 默认为启用
        itm['f_groupTotal'] = 0.00
        itm['f_groupTotalTimes'] = 0
    if groupMinNO > 0 or groupName != '':  # 指定的目录时
        pass
    else:
        # itm = objt_b_group4Meal[0].copy()
        # 2025-07-21 16:06:54 itm = {} #  2025-07-21 16:05:42
        # itm['f_GroupID'] = 0
        # itm['f_GroupName'] = "(Department not filled)  "
        # itm['f_MorningCount'] = 0
        # itm['f_LunchCount'] = 0
        # itm['f_EveningCount'] = 0
        # itm['f_OtherCount'] = 0
        #
        # itm['f_Morning'] = 0.00
        # itm['f_Lunch'] = 0.00
        # itm['f_Evening'] = 0.00
        # itm['f_Other'] = 0.00
        # itm['f_Enabled'] = 1  # 默认为启用
        # itm['f_groupTotal'] = 0.00
        # itm['f_groupTotalTimes'] = 0

        itm = {'f_GroupID': 0, 'f_GroupName': "(Department not filled)  ", 'f_MorningCount': 0, 'f_LunchCount': 0,
               'f_EveningCount': 0, 'f_OtherCount': 0, 'f_Morning': 0.00, 'f_Lunch': 0.00, 'f_Evening': 0.00,
               'f_Other': 0.00, 'f_Enabled': 1, 'f_groupTotal': 0.00, 'f_groupTotalTimes': 0}  #  2025-07-21 16:05:42
        objt_b_group4Meal.append(itm)

    #    //*************************创建显示用的数据表结构[Department班组 user编号 User ID Name Location   Time  消费额]
    #            //Details  消费记录
    strSql = " SELECT  f_RecID, 0 as f_ConsumerID, \'\' AS f_GroupName, \'\' as f_ConsumerNO,  \'\' AS f_ConsumerName,t_d_SwipeRecord2022.f_ReadDate , \'\' as f_MealName, 0.01 as f_Cost, \'\' as [f_ReaderName],0 as f_ReaderID  "  # 2025-07-17 12:17:13
    strSql += ", 0 as f_GroupID"  # 增加组
    strSql += " FROM t_d_SwipeRecord2022 "
    strSql += " WHERE 1<0 "
    query_obj = db.session.execute(text(strSql))
    objtable = query_obj.fetchall()
    columns = query_obj.keys()  # 获取列名
    objMealReport = [dict(zip(columns, row)) for row in objtable]

    # 加载相应的参数 读卡器用于消费
    #  2025-07-17 14:21:55 门用于消费
    strSql = "  SELECT  t_d_Reader4Meal.*  "
    strSql += " FROM  t_b_Reader,t_d_Reader4Meal, t_b_Controller WHERE ( t_b_Reader.f_ControllerID = t_b_Controller.f_ControllerID )  AND t_b_Reader.f_ReaderID = t_d_Reader4Meal.f_ReaderID "
    strSql += " ORDER BY  t_b_Reader.f_ReaderID  "
    query_obj = db.session.execute(text(strSql))
    objtable = query_obj.fetchall()
    columns = query_obj.keys()  # 获取列名
    objt_d_Reader4Meal = [dict(zip(columns, row)) for row in objtable]

    # 读卡器Statistics  表
    #  2025-07-17 14:21:55 门用于消费
    strSql = "  SELECT  t_b_door.f_DoorID as f_ReaderID , t_b_door.f_DoorName as [f_ReaderName], 0 as f_CostCount, 0.01 as f_CostTotal4Reader  "
    strSql += " FROM  t_b_door"
    strSql += " ORDER BY  f_ReaderID  "
    query_obj = db.session.execute(text(strSql))
    objtable = query_obj.fetchall()
    columns = query_obj.keys()  # 获取列名
    objReaderStatistics = [dict(zip(columns, row)) for row in objtable]
    for itm in objReaderStatistics:
        itm["f_CostTotal4Reader"] = 0

    # 读卡器约束
    if len(objReaderStatistics) > 0:
        allreadid = []
        for itm in objReaderStatistics:
            allreadid.append(itm["f_ReaderID"])
        more_reader_name_filter = allreadid  # 2025-07-17 15:11:06
    else:
        more_reader_name_filter = []  # ""  #没有选定读卡器时  则采用所有

    #                //人Statistics  表
    strSqlBaseInfo = " SELECT    f_ConsumerID, f_GroupName,  f_ConsumerNO, f_ConsumerName ";
    strSqlBaseInfo += " , 0 as f_CostMorningCount, 0 as f_CostLunchCount, 0 as f_CostEveningCount ,0 as f_CostOtherCount, 0 as f_CostTotalCount, 0.01 as f_CostTotal,  0.01 as f_CostMorning, 0.01 as f_CostLunch, 0.01 as f_CostEvening ,0.01 as f_CostOther ";
    strSqlBaseInfo += " , t_b_Consumer.f_GroupID "  # 增加组
    strSql = get_query_consumer_condition_str(strSqlBaseInfo, "",   groupNameCondition)
    query_obj = db.session.execute(text(strSql))
    objtable = query_obj.fetchall()
    columns = query_obj.keys()  # 获取列名
    objConsumerStatistics = [dict(zip(columns, row)) for row in objtable]
    for itm in objConsumerStatistics:
        itm["f_CostMorning"] = 0
        itm["f_CostLunch"] = 0
        itm["f_CostEvening"] = 0
        itm["f_CostOther"] = 0
        itm["f_CostTotal"] = 0

    # 所有读卡器
    # All Doors
    # strSql = "SELECT * from t_b_door "
    # query_obj = db.session.execute(text(strSql))
    # objtable = query_obj.fetchall()
    # columns = query_obj.keys()  # 获取列名
    # objt_b_reader = [dict(zip(columns, row)) for row in objtable]

    #                //************************将取得的记录填充显示表[原则: 依据所选择的消费原则].
    #            //从规则库中取记录有效规则 和 Time点
    valid_span = 0  # 不受间隔限制
    mealMode = 0  # 就餐模式0= 表示刷一次算一次,1=刷多次只算一次,2=小于指定秒内只算一次
    b_department_mode = False  # ; //2013-9-27_08:28:35 Department计价方式

    # 参数值
    strSql = "SELECT * from t_b_MealSetup WHERE f_ID=1 "
    query_obj = db.session.execute(text(strSql))
    objtable = query_obj.fetchall()
    columns = query_obj.keys()  # 获取列名
    objedit = [dict(zip(columns, row)) for row in objtable]
    # mealMode = 0
    # valid_span = 0
    for rd in objedit:
        if rd["f_Value"] == 1:
            mealMode = 1
        elif rd["f_Value"] == 2:
            mealMode = 2
            valid_span = rd["f_ParamVal"]
        break
    # 就餐Time
    strSql = "SELECT * from t_b_MealSetup WHERE f_ID > 1 ORDER BY f_ID ASC"
    query_obj = db.session.execute(text(strSql))
    objtable = query_obj.fetchall()
    columns = query_obj.keys()  # 获取列名
    objt_b_MealSetup = [dict(zip(columns, row)) for row in objtable]

    # from datetime import time
    from decimal import Decimal
    # 初始化所有数组（列表）
    arr_meal = [0] * 4
    arr_meal_name = [""] * 4
    arr_meal_field_name = [""] * 4
    arr_begin_hms = [datetime.time(0, 0)] * 4
    arr_end_hms = [datetime.time(0, 0)] * 4
    arr_cost_one_meal = [Decimal(0)] * 4

    # 设置餐段名称
    arr_meal_name[0] = "Breakfast  "
    arr_meal_name[1] = "Lunch  "
    arr_meal_name[2] = "Dinner  "
    arr_meal_name[3] = "OtherRecord"

    # 设置数据库字段名
    arr_meal_field_name[0] = "f_CostMorning"
    arr_meal_field_name[1] = "f_CostLunch"
    arr_meal_field_name[2] = "f_CostEvening"
    arr_meal_field_name[3] = "f_CostOther"

    # more_reader_name_filterf_CharacterAllSwipe = True #所有记录
    only_allow_valid_swipe = True  # False # False所有, True为只许有效记录,  默认为True
    # 规则部分设置
    for row in objt_b_MealSetup:  # 假设 cursor 是数据库游标对象
        f_id = int(row['f_ID'])
        f_value = int(row['f_Value'])

        # 处理餐段配置 (ID 2-5)
        if 2 <= f_id <= 5:
            if f_value > 0:
                j = f_id - 2  # 转换为 0-3 的索引
                # 更新餐段Status
                arr_meal[j] = 1

                # 处理开始Time (清除秒数)
                begin_time = row['f_BeginHMS']
                if isinstance(begin_time, str):
                    begin_time = datetime.datetime.strptime(begin_time, '%Y-%m-%d %H:%M:%S')
                arr_begin_hms[j] = begin_time.replace(second=0).time()  # strftime('%H:%M:%S')

                # 处理结束Time (设置为 HH:mm:59)
                end_time = row['f_EndHMS']
                if isinstance(end_time, str):
                    end_time = datetime.datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S')
                arr_end_hms[j] = end_time.replace(second=59).time()  # .strftime('%H:%M:%S')

                # 处理餐费 (Decimal 类型)
                param_val = str(row['f_ParamVal']).strip()
                arr_cost_one_meal[j] = Decimal(param_val) if param_val else Decimal(0)

        # 处理读卡器筛选条件 (ID 6)
        elif f_id == 6 and f_value == 0:
            #  2025-07-17 15:01:34 more_reader_name_filter += " AND f_Character=1"
            # more_reader_name_filter += " AND rec.f_Character=1" #  2025-07-17 15:01:44
            only_allow_valid_swipe = False  # True #" AND rec.f_Character=1" #  2025-07-17 15:01:44

        # 处理Department计价模式 (ID 7)
        elif f_id == 7 and f_value > 0:
            b_department_mode = True

    # //2013-9-26_17:32:06 启用的才显示...
    # ????????? for (int mealindex = 0; mealindex < 4; mealindex++)
    # {
    #     dgvStatistics.Columns[4 + mealindex].Visible = (arrMeal[mealindex] == 1);
    # }

    # 初始化变量
    onetime_span = 12  # 默认Time间隔为12小时
    # import datetime
    fixed_date = datetime.date(2015, 10, 20)  # 使用固定日期计算Time差

    # 遍历餐段数组
    for j in range(len(arr_meal)):
        if arr_meal[j] > 0:  # 仅处理启用的餐段
            if j < 3:  # 只处理Breakfast  、Lunch  、Dinner  （前三Unit餐段）
                # 将Time对象转换为当天的完整 datetime 对象
                start_time = datetime.datetime.combine(fixed_date, arr_begin_hms[j])
                end_time = datetime.datetime.combine(fixed_date, arr_end_hms[j])

                # 计算Time差（小时）
                time_diff = (end_time - start_time).total_seconds() / 3600

                # 更新Time间隔逻辑
                if time_diff > onetime_span:
                    onetime_span = int(time_diff)  # 取整数部分
                    onetime_span += 1  # 增加1小时缓冲

                    # 确保不超过24小时上限
                    if onetime_span > 24:
                        onetime_span = 24
            else:  # Others  餐段处理（保留原注释逻辑）
                # if (arrBeginHMS[j]) < arrEndHMS[j]
                pass  # 无实际操作

    from operator import itemgetter
    # dvcardrec.Sort = "f_ReadDate ASC"; // 按Time升序
    # 按第 6 列升序
    # dvcardrec = sorted(objt_d_SwipeRecord, key=itemgetter(5))

    # # 按多列排序：先第 2 列升序，再第 1 列升序
    # sorted_table = sorted(objtable, key=itemgetter(1, 0))

    # import datetime
    from decimal import Decimal

    # 初始化Statistics  计数器
    subtotal_count_morning = 0
    subtotal_count_lunch = 0
    subtotal_count_evening = 0
    subtotal_count_other = 0

    # 按Time升序排序刷卡记录
    # dvcardrec_sorted = sorted(objt_d_SwipeRecord, key=lambda x: x["f_ReadDate"])
    dvcardrec_sorted = sorted(objt_d_SwipeRecord, key=lambda x: x.f_ReadDate)

    # 遍历消费者Statistics  记录
    for consumer in objConsumerStatistics:
        # 模拟进度条更新（实际项目中可用tqdm）
        # progress_bar.update(1)

        # Department过滤逻辑
        if b_department_mode and consumer["f_GroupID"] <= 0:
            continue
        # print(consumer)
        # print(f"f_ConsumerID={consumer['f_ConsumerID']}") # 2025-07-17 15:54:10

        # 过滤当前消费者的刷卡记录
        # consumer_records = [
        #     rec for rec in dvcardrec_sorted
        #     if rec.f_ConsumerID == consumer["f_ConsumerID"]  and (more_reader_name_filter is None or eval(more_reader_name_filter))  # 简化的过滤条件
        # ]
        consumer_records = [
            rec for rec in dvcardrec_sorted
            if rec.f_ConsumerID == consumer["f_ConsumerID"] and (
                    more_reader_name_filter is None or rec.f_ReaderID in more_reader_name_filter)  # 简化的过滤条件
               and (not only_allow_valid_swipe or (only_allow_valid_swipe and rec.f_Character == 1))
        ]

        if not consumer_records:
            continue

        # 初始化就餐跟踪变量
        last_meal_date = datetime.datetime.max
        last_meal_name = ""
        last_meal_reader = -1

        # 处理当前消费者的每条记录
        for rec in consumer_records:
            record = dict(zip(columnsSwipeRecord, rec))  # 2025-07-17 14:54:52转换
            tm = vwAppConfig.strtotime(record["f_ReadDate"])  # record["f_ReadDate"]
            b_valid_rec = False
            meal_name = ""
            meal_field_name = ""
            j_index = -1

            # 判断记录所属餐段
            for j in range(len(arr_meal)):
                if arr_meal[j] <= 0:
                    continue

                begin_time = arr_begin_hms[j]
                end_time = arr_end_hms[j]
                #  2025-07-17 15:42:16
                record_time = tm.time()
                # record_time = tm[11:19] #str.strip(tm,0,2)

                if j < 3:  # 早中Dinner  
                    if begin_time <= record_time <= end_time:
                        b_valid_rec = True
                        meal_name = arr_meal_name[j]
                        meal_field_name = arr_meal_field_name[j]
                        j_index = j
                        break
                else:  # Others  餐段
                    if begin_time < end_time:  # 同一天
                        if begin_time <= record_time <= end_time:
                            b_valid_rec = True
                            meal_name = arr_meal_name[j]
                            meal_field_name = arr_meal_field_name[j]
                            j_index = j
                            break
                    else:  # 跨天
                        if record_time >= begin_time or record_time <= end_time:
                            b_valid_rec = True
                            meal_name = arr_meal_name[j]
                            meal_field_name = arr_meal_field_name[j]
                            j_index = j
                            break

            if not b_valid_rec:
                continue

            # 应用就餐规则验证
            b_valid_rule = True
            time_diff = (tm - last_meal_date).total_seconds()

            if mealMode == 1:  # Time间隔模式
                if (last_meal_name == meal_name and
                        abs(time_diff) < onetime_span * 3600):
                    b_valid_rule = False

            elif mealMode == 2:  # 同读卡器模式
                if (last_meal_name == meal_name and
                        last_meal_reader == record["f_ReaderID"] and
                        abs(time_diff) < valid_span):
                    b_valid_rule = False

            if not b_valid_rule:
                continue

            # 更新就餐跟踪
            last_meal_name = meal_name
            last_meal_date = tm
            last_meal_reader = record["f_ReaderID"]

            # 创建新就餐记录
            new_meal = {
                "f_RecID": record["f_RecID"],
                "f_GroupName": record["f_GroupName"],
                "f_ConsumerNO": record["f_ConsumerNO"],
                "f_ConsumerID": record["f_ConsumerID"],
                "f_ConsumerName": record["f_ConsumerName"],
                "f_ReaderName": record["f_ReaderName"],
                "f_ReaderID": record["f_ReaderID"],
                "f_ReadDate": tm,
                "f_MealName": meal_name,
                "f_Cost": Decimal(str(arr_cost_one_meal[j_index]))
            }

            # 查找Department配置
            group_config = next(
                (g for g in objt_b_group4Meal if g["f_GroupID"] == consumer["f_GroupID"]),
                None
            )
            # Department定价逻辑
            if b_department_mode and consumer["f_GroupID"] > 0:
                if group_config and group_config["f_Enabled"] == 1:
                    if j_index == 0:
                        new_meal["f_Cost"] = Decimal(str(group_config["f_Morning"]))
                    elif j_index == 1:
                        new_meal["f_Cost"] = Decimal(str(group_config["f_Lunch"]))
                    elif j_index == 2:
                        new_meal["f_Cost"] = Decimal(str(group_config["f_Evening"]))
                    elif j_index == 3:
                        new_meal["f_Cost"] = Decimal(str(group_config["f_Other"]))

            # 更新DepartmentStatistics  
            if group_config:
                if meal_name == arr_meal_name[0]:
                    group_config["f_MorningCount"] += 1
                    subtotal_count_morning += 1
                elif meal_name == arr_meal_name[1]:
                    group_config["f_LunchCount"] += 1
                    subtotal_count_lunch += 1
                elif meal_name == arr_meal_name[2]:
                    group_config["f_EveningCount"] += 1
                    subtotal_count_evening += 1
                elif meal_name == arr_meal_name[3]:
                    group_config["f_OtherCount"] += 1
                    subtotal_count_other += 1

                group_config["f_groupTotalTimes"] += 1
                group_config["f_groupTotal"] += float(new_meal["f_Cost"])  # 2025-07-17 16:32:17 采用float可以了, 不用decimal
            # 添加到结果集
            objMealReport.append(new_meal)

    # 步骤2：读卡器Statistics  逻辑
    subtotal_count = 0
    subtotal_cost = 0.0
    # meal_report = ds.get("MealReport", [])

    # 遍历每Unit读卡器进行Statistics  [3](@ref)
    for reader_stat in objReaderStatistics:
        reader_id = reader_stat["f_ReaderID"]
        # 过滤当前读卡器的记录
        filtered_rows = filter_data(objMealReport, f"f_ReaderID = {reader_id}")

        if filtered_rows:
            # 更新读卡器Statistics  ：就餐次数
            meal_count = len(filtered_rows)
            reader_stat["f_CostCount"] = reader_stat.get("f_CostCount", 0) + meal_count
            subtotal_count += meal_count

            # 更新读卡器Statistics  ：消费总额
            reader_cost = sum(row["f_Cost"] for row in filtered_rows)
            reader_stat["f_CostTotal4Reader"] = reader_stat.get("f_CostTotal4Reader", 0.0) + reader_cost
            subtotal_cost += float(reader_cost)

    # 步骤3：添加读卡器Statistics  Total行[10](@ref)
    objReaderStatistics.append({
        "f_ReaderName": "Total",  # 替换为实际的Total文本
        "f_CostCount": subtotal_count,
        "f_CostTotal4Reader": subtotal_cost
    })

    # 步骤4：添加DepartmentStatistics  Total行[11](@ref)
    objt_b_group4Meal.append({
        "f_GroupID": -1,
        "f_GroupName": "Total",  # 替换为实际的Total文本
        "f_Morning": 0.0, "f_Lunch": 0.0, "f_Evening": 0.0, "f_Other": 0.0,
        "f_MorningCount": subtotal_count_morning,  # 来自前文计算的变量
        "f_LunchCount": subtotal_count_lunch,
        "f_EveningCount": subtotal_count_evening,
        "f_OtherCount": subtotal_count_other,
        "f_groupTotalTimes": subtotal_count,
        "f_groupTotal": subtotal_cost
    })

    # 最终Statistics  输出
    # print(f"Breakfast count  : {subtotal_count_morning}")
    # print(f"Lunch  次数: {subtotal_count_lunch}")
    # print(f"Dinner count  : {subtotal_count_evening}")
    # print(f"Others  餐次: {subtotal_count_other}")

    # //进行人的Statistics  表
    # 步骤1：初始化Statistics  变量
    # total_consumers = len(dtConsumerStatistics)
    subtotal_counts = {field: 0 for field in arr_meal_field_name}  # 各餐次Total数
    subtotal_costs = {field: 0.0 for field in arr_meal_field_name}  # 各餐次总费用

    # 步骤2：主处理逻辑
    dv = objMealReport
    if dv:  # 等价 if dv.Count > 0
        # 进度模拟（可选）
        # print(f"Processing 0/{total_consumers}")

        for consumer in objConsumerStatistics:
            # 进度更新（控制台模拟进度条）
            # if idx % 100 == 0:
            #     print(f"Processing {idx}/{total_consumers}")

            # 过滤当前消费者的记录（等效 RowFilter）
            consumer_id = consumer["f_ConsumerID"]
            filtered_rows = [
                row for row in dv
                if row["f_ConsumerID"] == consumer_id
            ]

            if not filtered_rows:
                continue

            # 遍历每Unit餐次类型
            for j in range(len(arr_meal)):
                if arr_meal[j] <= 0:  # 无效餐次跳过
                    continue

                meal_name = arr_meal_name[j]
                field_prefix = arr_meal_field_name[j]

                # 二次过滤：当前餐次类型
                meal_rows = [
                    row for row in filtered_rows
                    if row["f_MealName"] == meal_name
                ]
                meal_count = len(meal_rows)

                if meal_count == 0:
                    continue

                # 更新消费者Statistics  ：次数
                count_key = f"{field_prefix}Count"
                consumer[count_key] = consumer.get(count_key, 0) + meal_count
                consumer["f_CostTotalCount"] = consumer.get("f_CostTotalCount", 0) + meal_count

                # 更新消费者Statistics  ：费用
                meal_cost = sum(row["f_Cost"] for row in meal_rows)
                cost_key = field_prefix
                consumer[cost_key] = consumer.get(cost_key, 0.0) + meal_cost
                consumer["f_CostTotal"] = consumer.get("f_CostTotal", 0.0) + meal_cost

                # 更新全局Statistics  
                subtotal_counts[field_prefix] += meal_count
                subtotal_costs[field_prefix] += float(meal_cost)

    # 步骤3：输出结果（可选）
    # print("Consumer statistics updated")
    # print(f"Total Breakfast Count: {subtotal_counts.get('Morning', 0)}")
    # print(f"Total Breakfast Cost: {subtotal_costs.get('Morning', 0.0):.2f}")

    ## 1. 创建Total行（drtotal）
    drtotal = {}  # 创建空字典作为Total行
    # 设置固定值字段
    drtotal["f_ConsumerID"] = -1  # 2025-07-17 16:08:47
    drtotal["f_GroupName"] = "=========="
    drtotal["f_ConsumerNO"] = "=========="
    drtotal["f_ConsumerName"] = "Total"  # 替换ResStrings.CommonStr.strMealTotal

    # 2. 初始化Statistics  字段（索引4到13）
    # 确定需要Statistics  的字段列表（根据实际数据结构调整）
    stat_fields = [
        "f_CostMorning", "f_CostLunch", "f_CostEvening", "f_CostOther",
        "f_CostTotal", "f_CostMorningCount", "f_CostLunchCount",
        "f_CostEveningCount", "f_CostOtherCount", "f_CostTotalCount"
    ]

    # 初始化Statistics  字段为0
    for field in stat_fields:
        drtotal[field] = 0  # 统一初始化为整数类型

    # 3. 遍历所有行进行累加
    # total_rows = len(dtConsumerStatistics)
    for row in objConsumerStatistics:
        # 进度提示（模拟ProgressBar）
        # if index % 100 == 0:
        #     print(f"处理进度: {index}/{total_rows}")  # 实际项目中可替换为tqdm进度条

        # 累加每UnitStatistics  字段
        for field in stat_fields:
            # 获取当前值（处理可能缺失的字段）
            current_value = row.get(field, 0)

            # 根据值类型执行累加
            if isinstance(current_value, float):
                drtotal[field] += current_value  # 浮点数累加
            else:
                drtotal[field] += int(current_value)  # Others  类型转为整数累加

    # 4. 添加Total行到结果集
    objConsumerStatistics.append(drtotal)
    # nowstr = datetime.datetime.now().strftime("%Y-%m-%d_%H_%M_%S")
    logtitle = f"{beginYMD} to {endYMD}"
    return createTestReport(objMealReport, objReaderStatistics, objt_b_group4Meal, objConsumerStatistics, logtitle)
    # return createTestReport(objt_b_group4Meal)

# import xlwt, datetime, os
# from urllib.parse import quote
# from flask import make_response, send_file   # 假设你用 Flask

# ---------- 工具：把像素宽转成 xlwt 的“字符宽” ----------
def px2char(px):
    return int(px / 8.43)          # 8.43 px ≈ 1 Unit字符宽

def createTestReport(objMealReport, objReaderStatistics, objt_b_group4Meal,
                     objConsumerStatistics, logtitle):
    try:
        import xlwt
    except Exception as e:
        print(e)
        return None

    book = xlwt.Workbook(encoding='utf-8')

    # ===== 1. Details   =====
    sh1 = book.add_sheet('Details  ')
    mealkeysTitle = ['ID', 'Department', 'User ID', 'Name', 'Time', 'Meal name  ', 'Meal amount (yuan)  ', 'Location  ']
    mealkeys = ['f_RecID', 'f_GroupName', 'f_ConsumerNO', 'f_ConsumerName',
                'f_ReadDate', 'f_MealName', 'f_Cost', 'f_ReaderName']
    widths = [85, 100, 100, 100, 200, 100, 120, 145]   # 与原 px Consistent  
    for c, w in enumerate(widths):
        sh1.col(c).width = px2char(w) * 256            # xlwt 单位是 1/256 字符

    # 写表头
    for c, hd in enumerate(mealkeysTitle):
        sh1.write(0, c, hd)

    # 写数据
    for r, row in enumerate(objMealReport, 1):
        for c, k in enumerate(mealkeys):
            sh1.write(r, c, row.get(k, ''))

    # ===== 2. Subtotal by meal location   =====
    sh2 = book.add_sheet('Subtotal by meal location  ')
    title2 = ['Location  ', 'Total number of meals  ', 'Total meal amount ($)  ']
    keys2 = ['f_ReaderName', 'f_CostCount', 'f_CostTotal4Reader']
    widths2 = [145, 120, 120]
    for c, w in enumerate(widths2):
        sh2.col(c).width = px2char(w) * 256
    for c, hd in enumerate(title2):
        sh2.write(0, c, hd)
    for r, row in enumerate(objReaderStatistics, 1):
        for c, k in enumerate(keys2):
            sh2.write(r, c, row.get(k, ''))

    # ===== 3. Department subtotal   =====
    sh3 = book.add_sheet('Department subtotal  ')
    title3 = ['Department', 'Breakfast count  ', 'Lunch count  ', 'Dinner count  ', 'Night snack count  ', 'Total number of meals  ', 'Total meal amount ($)  ']
    keys3 = ['f_GroupName', 'f_MorningCount', 'f_LunchCount', 'f_EveningCount',
             'f_OtherCount', 'f_groupTotalTimes', 'f_groupTotal']
    widths3 = [145, 80, 80, 80, 80, 120, 120]
    for c, w in enumerate(widths3):
        sh3.col(c).width = px2char(w) * 256
    for c, hd in enumerate(title3):
        sh3.write(0, c, hd)
    for r, row in enumerate(objt_b_group4Meal, 1):
        for c, k in enumerate(keys3):
            sh3.write(r, c, row.get(k, ''))

    # ===== 4. 每人Statistics   =====
    sh4 = book.add_sheet('Statistics  ')
    title4 = ['Department', 'User ID', 'Name', 'Breakfast count  ', 'Lunch count  ', 'Dinner count  ', 'Night snack count  ', 'Total number of meals  ', 'Total meal amount ($)  ']
    keys4 = ['f_GroupName', 'f_ConsumerNO', 'f_ConsumerName', 'f_CostMorningCount',
             'f_CostLunchCount', 'f_CostEveningCount', 'f_CostOtherCount',
             'f_CostTotalCount', 'f_CostTotal']
    widths4 = [145, 100, 100, 80, 80, 80, 80, 80, 120, 120]
    for c, w in enumerate(widths4):
        sh4.col(c).width = px2char(w) * 256
    for c, hd in enumerate(title4):
        sh4.write(0, c, hd)
    for r, row in enumerate(objConsumerStatistics, 1):
        for c, k in enumerate(keys4):
            sh4.write(r, c, row.get(k, ''))

    # ===== 5. 日志页 =====
    sh5 = book.add_sheet(logtitle)
    nowstr = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    sh5.write(0, 0, f"Generation time  : {nowstr}")

    # ---------- 保存到磁盘 ----------
    save_dir = vwAppConfigParam.wgcloudpathdir
    os.makedirs(save_dir, exist_ok=True)
    file_path = os.path.join(save_dir, "Meal Report.xls")   # 注意扩展名 .xls
    book.save(file_path)

    # ---------- Flask 下载 ----------
    response = make_response(send_file(file_path))
    safe_name = quote("Meal Report.xls")
    response.headers["Content-Disposition"] = f"attachment; filename*=UTF-8''{safe_name}"
    return response


# def createTestReport2025old(objMealReport, objReaderStatistics, objt_b_group4Meal, objConsumerStatistics, logtitle):
#     # 创建新的工作簿
#     try:
#         from openpyxl import Workbook  # 2025-11-15 16:08:06
#     except Exception as e:
#         return None
#     workbook = Workbook()
#     sheet = workbook.active  # 获取默认工作表
#     sheet.title = "Details  "  # 设置工作表名称
#     # mealkeys = list(objt_b_group4Meal[0].keys())
#     mealkeysTitle = ['ID', 'Department', 'User ID', 'Name', 'Time', 'Meal name  ', 'Meal amount (yuan)  ', 'Location  ']
#     mealkeys = ['f_RecID', 'f_GroupName', 'f_ConsumerNO', 'f_ConsumerName', 'f_ReadDate', 'f_MealName', 'f_Cost',
#                 'f_ReaderName']

#     ws = sheet
#     widthunit = 10
#     ws.column_dimensions['A'].width = 85 / widthunit
#     ws.column_dimensions['B'].width = 100 / widthunit
#     ws.column_dimensions['C'].width = 100 / widthunit
#     ws.column_dimensions['D'].width = 100 / widthunit
#     ws.column_dimensions['E'].width = 200 / widthunit
#     ws.column_dimensions['F'].width = 100 / widthunit
#     ws.column_dimensions['G'].width = 120 / widthunit
#     ws.column_dimensions['H'].width = 145 / widthunit

#     sheet.append(mealkeysTitle)
#     for row in objMealReport:
#         sheet.append([row[x] for x in mealkeys])

#     sheet = workbook.create_sheet()  # 获取默认工作表
#     sheet.title = "Subtotal by meal location  "  # 设置工作表名称
#     mealkeysTitle = ['Location  ', 'Total number of meals  ', 'Total meal amount ($)  ']
#     mealkeys = ['f_ReaderName', 'f_CostCount', 'f_CostTotal4Reader']
#     ws = sheet
#     widthunit = 10
#     ws.column_dimensions['A'].width = 145 / widthunit
#     ws.column_dimensions['B'].width = 120 / widthunit
#     ws.column_dimensions['C'].width = 120 / widthunit
#     sheet.append(mealkeysTitle)
#     for row in objReaderStatistics:
#         sheet.append([row[x] for x in mealkeys])

#     sheet = workbook.create_sheet()  # 获取默认工作表
#     sheet.title = "Department subtotal  "  # 设置工作表名称
#     mealkeysTitle = ['Department', 'Breakfast count  ', 'Lunch count  ', 'Dinner count  ', 'Night snack count  ', 'Total number of meals  ', 'Total meal amount ($)  ']
#     mealkeys = ['f_GroupName', 'f_MorningCount', 'f_LunchCount', 'f_EveningCount', 'f_OtherCount', 'f_groupTotalTimes',
#                 'f_groupTotal']
#     ws = sheet
#     widthunit = 10
#     ws.column_dimensions['A'].width = 145 / widthunit
#     ws.column_dimensions['B'].width = 80 / widthunit
#     ws.column_dimensions['C'].width = 80 / widthunit
#     ws.column_dimensions['D'].width = 80 / widthunit
#     ws.column_dimensions['E'].width = 80 / widthunit
#     ws.column_dimensions['F'].width = 120 / widthunit
#     ws.column_dimensions['G'].width = 120 / widthunit
#     sheet.append(mealkeysTitle)
#     for row in objt_b_group4Meal:
#         sheet.append([row[x] for x in mealkeys])

#     # 每人的Statistics  
#     sheet = workbook.create_sheet()  # 获取默认工作表
#     sheet.title = "Statistics  "  # 设置工作表名称
#     mealkeysTitle = ['Department', 'User ID', 'Name', 'Breakfast count  ', 'Lunch count  ', 'Dinner count  ', 'Night snack count  ', 'Total number of meals  ',
#                      'Total meal amount ($)  ']
#     mealkeys = ['f_GroupName', 'f_ConsumerNO', 'f_ConsumerName', 'f_CostMorningCount', 'f_CostLunchCount',
#                 'f_CostEveningCount', 'f_CostOtherCount', 'f_CostTotalCount', 'f_CostTotal']
#     ws = sheet
#     widthunit = 10
#     ws.column_dimensions['A'].width = 145 / widthunit
#     ws.column_dimensions['C'].width = 100 / widthunit
#     ws.column_dimensions['D'].width = 100 / widthunit
#     ws.column_dimensions['E'].width = 80 / widthunit
#     ws.column_dimensions['F'].width = 80 / widthunit
#     ws.column_dimensions['G'].width = 80 / widthunit
#     ws.column_dimensions['H'].width = 80 / widthunit
#     ws.column_dimensions['I'].width = 120 / widthunit
#     ws.column_dimensions['J'].width = 120 / widthunit
#     sheet.append(mealkeysTitle)

#     for row in objConsumerStatistics:
#         sheet.append([row[x] for x in mealkeys])

#     # 日志
#     sheet = workbook.create_sheet()  # 获取默认工作表
#     sheet.title = logtitle  # 设置工作表名称
#     nowstr = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
#     loginfo = f"Generation time  : {nowstr}"
#     sheet.append([loginfo])

#     filename = f"{vwAppConfigParam.wgcloudpathdir}MealReport.xlsx  "
#     # vwAppConfig.vwlog(f"wgcloudpathdir =  {vwAppConfigParam.wgcloudpathdir}")
#     # vwAppConfig.vwlog(f"消费表格创建  {filename}")
#     # 保存文件
#     workbook.save(filename) # f"{vwAppConfigParam.wgcloudpathdir}MealReport.xlsx  ")
#     # vwAppConfig.vwlog(f"消费表格已创建并保存为  {filename}")
#     # print("消费表格已创建并保存为 '消费记录表.xlsx'")
#     file_path =f"{vwAppConfigParam.wgcloudpathdir}MealReport.xlsx  "
#     response = make_response(send_file(file_path))
#     safe_name = quote("MealReport.xlsx  ")  # 文件名URL编码
#     response.headers["Content-Disposition"] = "attachment; filename*=UTF-8''{}".format(safe_name)
#     return response

def get_report_meal_setup():  # 获取规则信息
    wgform = MealSetupForm()

    #            //从规则库中取记录有效规则 和 Time点
    valid_span = 0  # 不受间隔限制
    mealMode = 0  # 就餐模式0= 表示刷一次算一次,1=刷多次只算一次,2=小于指定秒内只算一次
    b_department_mode = False  # ; //2013-9-27_08:28:35 Department计价方式

    # 参数值
    strSql = "SELECT * from t_b_MealSetup WHERE f_ID=1 "
    query_obj = db.session.execute(text(strSql))
    objtable = query_obj.fetchall()
    if len(objtable) == 0:
        return wgform
    columns = query_obj.keys()  # 获取列名
    objedit = [dict(zip(columns, row)) for row in objtable]
    # mealMode = 0
    # valid_span = 0
    for rd in objedit:
        if rd["f_Value"] == 1:
            mealMode = 1
        elif rd["f_Value"] == 2:
            mealMode = 2
            valid_span = rd["f_ParamVal"]
        break
    wgform.meal_rule_a.data = mealMode

    # 就餐Time
    strSql = "SELECT * from t_b_MealSetup WHERE f_ID > 1 ORDER BY f_ID ASC"
    query_obj = db.session.execute(text(strSql))
    objtable = query_obj.fetchall()
    columns = query_obj.keys()  # 获取列名
    objt_b_MealSetup = [dict(zip(columns, row)) for row in objtable]

    # from datetime import time
    from decimal import Decimal
    # 初始化所有数组（列表）
    arr_meal = [0] * 4
    arr_meal_name = [""] * 4
    arr_meal_field_name = [""] * 4
    arr_begin_hms = [datetime.time(0, 0)] * 4
    arr_end_hms = [datetime.time(0, 0)] * 4
    arr_cost_one_meal = [Decimal(0)] * 4

    # 设置餐段名称
    arr_meal_name[0] = "Breakfast  "
    arr_meal_name[1] = "Lunch  "
    arr_meal_name[2] = "Dinner  "
    arr_meal_name[3] = "OtherRecord"

    # 设置数据库字段名
    arr_meal_field_name[0] = "f_CostMorning"
    arr_meal_field_name[1] = "f_CostLunch"
    arr_meal_field_name[2] = "f_CostEvening"
    arr_meal_field_name[3] = "f_CostOther"

    # more_reader_name_filterf_CharacterAllSwipe = True #所有记录
    only_allow_valid_swipe = True  # False # False所有, True为只许有效记录,  默认为True
    # 规则部分设置
    for row in objt_b_MealSetup:  # 假设 cursor 是数据库游标对象
        f_id = int(row['f_ID'])
        f_value = int(row['f_Value'])

        # 处理餐段配置 (ID 2-5)
        if 2 <= f_id <= 5:
            j = f_id - 2  # 转换为 0-3 的索引
            # 更新餐段Status
            if f_value > 0:  # 启用时
                arr_meal[j] = 1

            # 处理开始Time (清除秒数)
            begin_time = row['f_BeginHMS']
            if isinstance(begin_time, str):
                begin_time = datetime.datetime.strptime(begin_time, '%Y-%m-%d %H:%M:%S')
            arr_begin_hms[j] = vwAppConfig.getStrOfDatetimeHHMM(
                begin_time.replace(second=0).time())  # strftime('%H:%M:%S')

            # 处理结束Time (设置为 HH:mm:59)
            end_time = row['f_EndHMS']
            if isinstance(end_time, str):
                end_time = datetime.datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S')
            arr_end_hms[j] = vwAppConfig.getStrOfDatetimeHHMM(
                end_time.replace(second=59).time())  # .strftime('%H:%M:%S')

            # 处理餐费 (Decimal 类型)
            param_val = str(row['f_ParamVal']).strip()
            arr_cost_one_meal[j] = Decimal(param_val) if param_val else Decimal(0)

        # 处理读卡器筛选条件 (ID 6)
        elif f_id == 6 and f_value == 0:
            #  2025-07-17 15:01:34 more_reader_name_filter += " AND f_Character=1"
            # more_reader_name_filter += " AND rec.f_Character=1" #  2025-07-17 15:01:44
            only_allow_valid_swipe = False  # True #" AND rec.f_Character=1" #  2025-07-17 15:01:44

        # 处理Department计价模式 (ID 7)
        elif f_id == 7 and f_value > 0:
            b_department_mode = True
    if only_allow_valid_swipe:
        wgform.meal_rule_b.data = 1
    else:
        wgform.meal_rule_b.data = 0

    wgform.f_morning_meal.data = arr_meal[0]
    wgform.f_lunch_meal.data = arr_meal[1]
    wgform.f_evening_meal.data = arr_meal[2]
    wgform.f_other_meal.data = arr_meal[3]
    wgform.f_BeginHMS1.data = arr_begin_hms[0]
    wgform.f_EndHMS1.data = arr_end_hms[0]
    wgform.f_BeginHMS2.data = arr_begin_hms[1]
    wgform.f_EndHMS2.data = arr_end_hms[1]
    wgform.f_BeginHMS3.data = arr_begin_hms[2]
    wgform.f_EndHMS3.data = arr_end_hms[2]
    wgform.f_BeginHMS4.data = arr_begin_hms[3]
    wgform.f_EndHMS4.data = arr_end_hms[3]
    wgform.f_cost1.data = arr_cost_one_meal[0]
    wgform.f_cost2.data = arr_cost_one_meal[1]
    wgform.f_cost3.data = arr_cost_one_meal[2]
    wgform.f_cost4.data = arr_cost_one_meal[3]
    return wgform


@bp.route('/report_meal_setup', methods=['POST', 'GET'])
@login_required
def web_report_meal_setup():  # Edit就餐设置  就餐规则
    url4htmltemplate = "wgcomm/report-meal-setup.html"  # template 实际操作界面
    url4redirect = "wgcomm_report_meal.web_report_meal_setup"  # redirect 返回上一Unit界面 主界面
    wgform = MealSetupForm()
    if not request.form.get('meal_rule_a'):
        # 加载数据
        pass
        wgform = get_report_meal_setup()
    else:
        if request.form.get('meal_rule_a'):  # 如果有日期 生成新的报表
            wgform = MealSetupForm(request.form)
            if wgform.submitcancel.data:  # 取消时 退出到列表
                # return redirect(url_for(url4redirect))  # url_for("wgcomm.controller_list"))  # 要跳转 controller_list()
                return redirect(url_for(
                    "wgcomm_report_meal.web_report_meal"))  # 回到生成部分 url_for("wgcomm.controller_list"))  # 要跳转 controller_list()
            if wgform.validate():
                # 初始化所有数组（列表）

                # arr_meal = [0] * 4
                # arr_meal_name = [""] * 4
                # arr_meal_field_name = [""] * 4
                # arr_begin_hms = [datetime.time(0, 0)] * 4
                # arr_end_hms = [datetime.time(0, 0)] * 4
                # arr_cost_one_meal = [float(0)] * 4

                meal_mode = wgform.meal_rule_a.data
                allow_allid6 = wgform.meal_rule_b.data

                arr_meal = [wgform.f_morning_meal.data, wgform.f_lunch_meal.data, wgform.f_evening_meal.data,
                            wgform.f_other_meal.data]
                arr_begin_hms = [wgform.f_BeginHMS1.data, wgform.f_BeginHMS2.data, wgform.f_BeginHMS3.data,
                                 wgform.f_BeginHMS4.data]
                arr_end_hms = [wgform.f_EndHMS1.data, wgform.f_EndHMS2.data, wgform.f_EndHMS3.data,
                               wgform.f_EndHMS4.data]
                arr_cost_one_meal = [wgform.f_cost1.data, wgform.f_cost2.data, wgform.f_cost3.data, wgform.f_cost4.data]

                try:
                    db.session.execute(text(f"DELETE FROM t_b_MealSetup"))  # 清空数据
                    db.session.commit()
                    strsql = 'Insert Into t_b_MealSetup VALUES ({},{},{},{},{},NULL)'.format(1, meal_mode, 'NULL',
                                                                                             'NULL', 60)
                    db.session.execute(text(strsql))
                    for mealid in range(2, 6):
                        begin_hms = vwAppConfig.getStrOfDatetime(
                            vwAppConfig.getDateTimeFromStr("{} {}:00".format("2025-07-18 ", arr_begin_hms[mealid - 2])))
                        end_hms = vwAppConfig.getStrOfDatetime(
                            vwAppConfig.getDateTimeFromStr("{} {}:00".format("2025-07-18 ", arr_end_hms[mealid - 2])))
                        active = 0
                        if arr_meal[mealid - 2]:
                            active = 1
                        strsql = "Insert Into t_b_MealSetup VALUES ({},{},'{}','{}',{},NULL)".format(
                            mealid, active, begin_hms, end_hms, arr_cost_one_meal[mealid - 2])
                        db.session.execute(text(strsql))
                    strsql = 'Insert Into t_b_MealSetup VALUES ({},{},{},{},{},NULL)'.format(6, allow_allid6, 'NULL',
                                                                                             'NULL', 0)
                    db.session.execute(text(strsql))
                    db.session.commit()

                    # vwAppConfig.setusercardChangeTime()  # 2024-05-10 06:14:09 user变更
                    vwAppConfig.vwlog('{}--{} '.format(g.user.f_OperatorName,
                                                       "Meal settings modified successfully!  ".format(
                                                       )))  # 2024-01-13 12:59:02 增加日志
                    flash("Meal settings modified successfully!  ")
                    # return startDeal(vwAppConfig.getStrOfDatetimeYMD(f_BeginYMD),vwAppConfig.getStrOfDatetimeYMD(f_EndYMD), f_GroupName)  #  2025-07-12 17:27:07
                    return redirect(url_for(url4redirect))
                except Exception as e:
                    print(e)
                    # 加入数据库commit提交 Failed ，必须回滚！！！
                    db.session.rollback()
                    flash("Meal settings modification failed!")
                    vwAppConfig.vwlog('{}--{}[{}] '.format(g.user.f_OperatorName, "Meal settings modification failed!", e))
                    wgform = MealSetupForm(request.form)
            else:
                for message in wgform.messages:
                    flash(message)
                wgform = MealSetupForm(request.form)
    context = {
        "wgform": wgform,
    }
    return render_template(url4htmltemplate, **context)  # wgform=wgform
