LqStatisticsService.cs 322 KB

Edit Raw Blame History

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using NCC.Common.Core.Manager;
using NCC.Common.Enum;
using NCC.Common.Extension;
using NCC.Common.Filter;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.Extend.Entitys.Dto.LqMdxx;
using NCC.Extend.Entitys.Dto.LqStatistics;
using NCC.Extend.Entitys.Dto.LqStatistics.DepartmentInfo;
using NCC.Extend.Entitys.Dto.LqStatisticsStoreConsumePerformance;
using NCC.Extend.Entitys.Dto.LqStatisticsStoreTotalPerformance;
using NCC.Extend.Entitys.Dto.LqStatisticsTechPerformance;
using NCC.Extend.Entitys.Dto.LqSalaryStatistics;
using NCC.Extend.Entitys.Dto.LqStatisticsDepartmentConsumePerformance;
using NCC.Extend.Entitys.Dto.LqYcsdJsj;
using NCC.Extend.Entitys.lq_hytk_kjbsyj;
using NCC.Extend.Entitys.lq_jinsanjiao_user;
using NCC.Extend.Entitys.lq_kd_jksyj;
using NCC.Extend.Entitys.lq_kd_kdjlb;
using NCC.Extend.Entitys.lq_kd_kjbsyj;
using NCC.Extend.Entitys.lq_mdxx;
using NCC.Extend.Entitys.lq_md_target;
using NCC.Extend.Entitys.lq_hytk_hytk;
using NCC.Extend.Entitys.lq_md_xdbhsj;
using NCC.Extend.Entitys.lq_xh_kjbsyj;
using NCC.Extend.Entitys.lq_xh_hyhk;
using NCC.Extend.Entitys.lq_xh_pxmx;
using NCC.Extend.Entitys.lq_ycsd_jsj;
using NCC.Extend.Entitys.lq_yjmxb;
using NCC.Extend.Entitys.Enum;
using NCC.Extend.Entitys.lq_statistics_gold_triangle;
using NCC.Extend.Entitys.lq_statistics_personal_performance;
using NCC.Extend.Entitys.lq_statistics_store_consume_performance;
using NCC.Extend.Entitys.lq_statistics_store_total_performance;
using NCC.Extend.Entitys.lq_statistics_tech_performance;
using NCC.Extend.Entitys.lq_salary_statistics;
using NCC.Extend.Entitys.lq_statistics_department_consume_performance;
using NCC.Extend.Entitys.lq_xmzl;
using NCC.Extend.Entitys.Dto.LqStatisticsPersonalPerformance;
using NCC.Extend.Entitys.v_tech_teacher_flow;
using NCC.Extend.Interfaces.LqStatistics;
using NCC.Extend.Utils;
using NCC.FriendlyException;
using NCC.System.Entitys.Permission;
using SqlSugar;
using Yitter.IdGenerator;
using NCC.Extend.Entitys.lq_kd_pxmx;
using NCC.Extend.Entitys.lq_khxx;
using NCC.Extend.Entitys.lq_tkjlb;
using NCC.Extend.Entitys.lq_yaoyjl;
using NCC.Extend.Entitys.lq_yyjl;
using NCC.Extend.Entitys.lq_store_manager_salary_statistics;
using NCC.System.Entitys.System;

namespace NCC.Extend.LqStatistics
{
    /// <summary>
    /// 绿纤统计服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "绿纤统计服务", Name = "LqStatistics", Order = 200, Groups = new[] { "Default" })]
    [Route("api/Extend/[controller]")]
    public class LqStatisticsService : ILqStatisticsService, IDynamicApiController, ITransient
    {
        private readonly ISqlSugarRepository<LqMdxxEntity> _lqMdxxRepository;
        private readonly SqlSugarScope _db;
        private readonly IUserManager _userManager;
        private readonly ILogger<LqStatisticsService> _logger;
        private readonly WeChatBotService _weChatBotService;

        /// <summary>
        /// 初始化一个<see cref="LqStatisticsService"/>类型的新实例
        /// </summary>
        public LqStatisticsService(ISqlSugarRepository<LqMdxxEntity> lqMdxxRepository, IUserManager userManager, ILogger<LqStatisticsService> logger, WeChatBotService weChatBotService)
        {
            _lqMdxxRepository = lqMdxxRepository;
            _db = _lqMdxxRepository.Context;
            _userManager = userManager;
            _logger = logger;
            _weChatBotService = weChatBotService;
        }

        #region 获取门店耗卡业绩统计列表
        /// <summary>
        /// 获取门店业绩统计列表
        /// </summary>
        /// <remarks>
        /// 获取所有门店的耗卡业绩统计信息，包括门店ID、名称、编码、目标业绩、耗卡业绩、完成率等关键指标
        ///
        /// 返回数据说明：
        /// - StoreId: 门店ID
        /// - StoreName: 门店名称
        /// - StoreCode: 门店编码
        /// - BusinessUnitId: 事业部ID（暂未关联）
        /// - BusinessUnitName: 事业部名称（暂未关联）
        /// - TargetPerformance: 目标业绩（门店生命线）
        /// - ActualPerformance: 耗卡业绩（当月耗卡金额汇总）
        /// - CompletionRate: 完成率（耗卡业绩/目标业绩*100）
        /// - OrderCount: 耗卡记录数（当月耗卡记录数）
        ///
        /// 数据来源：v_store_consume_performance_simple 视图
        /// </remarks>
        /// <returns>门店业绩统计列表</returns>
        /// <response code="200">成功返回门店业绩统计列表</response>
        /// <response code="500">服务器内部错误</response>
        [HttpGet("GetStorePerformanceList")]
        [AllowAnonymous]
        public async Task<List<StorePerformanceOutput>> GetStorePerformanceList()
        {
            try
            {
                _logger.LogInformation("开始查询门店业绩统计列表");

                // 使用耗卡业绩统计视图
                var storeList = await _db.Ado.SqlQueryAsync<StorePerformanceOutput>(
                    "SELECT "
                        + "store_id AS StoreId, "
                        + "store_code AS StoreCode, "
                        + "store_name AS StoreName, "
                        + "'' AS BusinessUnitId, "
                        + "'' AS BusinessUnitName, "
                        + "target_performance AS TargetPerformance, "
                        + "actual_performance AS ActualPerformance, "
                        + "completion_rate AS CompletionRate, "
                        + "order_count AS OrderCount "
                        + "FROM v_store_consume_performance_simple "
                        + "ORDER BY actual_performance DESC"
                );

                _logger.LogInformation("门店业绩统计列表查询完成，返回{Count}条记录", storeList.Count);

                return storeList;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询门店业绩统计列表时发生错误");
                throw NCCException.Oh("查询门店业绩统计列表失败", ex);
            }
        }
        #endregion

        #region 获取门店开单业绩统计列表
        /// <summary>
        /// 获取门店开单业绩统计列表
        /// </summary>
        /// <remarks>
        /// 获取所有门店的开单业绩统计信息，包括门店ID、名称、编码、目标业绩、开单业绩、完成率等关键指标
        ///
        /// 返回数据说明：
        /// - StoreId: 门店ID
        /// - StoreName: 门店名称
        /// - StoreCode: 门店编码
        /// - BusinessUnitId: 事业部ID（暂未关联）
        /// - BusinessUnitName: 事业部名称（暂未关联）
        /// - TargetPerformance: 目标业绩（门店生命线）
        /// - ActualPerformance: 开单业绩（当月开单金额汇总）
        /// - CompletionRate: 完成率（开单业绩/目标业绩*100）
        /// - OrderCount: 开单记录数（当月开单记录数）
        ///
        /// 数据来源：v_store_performance_simple 视图
        /// </remarks>
        /// <returns>门店开单业绩统计列表</returns>
        /// <response code="200">成功返回门店开单业绩统计列表</response>
        /// <response code="500">服务器内部错误</response>
        [HttpGet("GetStoreOrderPerformanceList")]
        [AllowAnonymous]
        public async Task<List<StorePerformanceOutput>> GetStoreOrderPerformanceList()
        {
            try
            {
                _logger.LogInformation("开始查询门店开单业绩统计列表");

                // 使用开单业绩统计视图
                var storeList = await _db.Ado.SqlQueryAsync<StorePerformanceOutput>(
                    "SELECT "
                        + "store_id AS StoreId, "
                        + "store_code AS StoreCode, "
                        + "store_name AS StoreName, "
                        + "'' AS BusinessUnitId, "
                        + "'' AS BusinessUnitName, "
                        + "target_performance AS TargetPerformance, "
                        + "actual_performance AS ActualPerformance, "
                        + "completion_rate AS CompletionRate, "
                        + "order_count AS OrderCount "
                        + "FROM v_store_performance_simple "
                        + "ORDER BY actual_performance DESC"
                );

                _logger.LogInformation("门店开单业绩统计列表查询完成，返回{Count}条记录", storeList.Count);

                return storeList ?? new List<StorePerformanceOutput>();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询门店开单业绩统计列表时发生错误");
                throw NCCException.Oh("查询门店开单业绩统计列表失败", ex);
            }
        }
        #endregion

        #region 获取门店统计信息
        /// <summary>
        /// 获取门店统计信息
        /// </summary>
        /// <remarks>
        /// 根据指定日期范围和门店ID查询门店的详细业绩统计信息，包括目标业绩、完成业绩、完成率等
        ///
        /// 示例请求：
        /// ```json
        /// {
        ///   "startDate": "2025-01-01T00:00:00",
        ///   "endDate": "2025-01-31T23:59:59",
        ///   "storeId": "门店ID（可选）"
        /// }
        /// ```
        ///
        /// 参数说明：
        /// - startDate: 开始日期，格式：yyyy-MM-ddTHH:mm:ss
        /// - endDate: 结束日期，格式：yyyy-MM-ddTHH:mm:ss
        /// - storeId: 门店ID，可选参数，不传则查询所有门店
        ///
        /// 返回数据说明：
        /// - StoreId: 门店ID
        /// - StoreName: 门店名称
        /// - StoreCode: 门店编码
        /// - BusinessUnitId: 事业部ID
        /// - BusinessUnitName: 事业部名称
        /// - TargetPerformance: 目标业绩
        /// - ActualPerformance: 完成业绩
        /// - OrderCount: 开单数量
        /// - CompletionRate: 完成率（%）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>门店统计结果</returns>
        /// <response code="200">成功返回门店统计数据</response>
        /// <response code="400">请求参数错误</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("StoreStatistics")]
        [AllowAnonymous]
        public async Task<StoreStatisticsOutput> GetStoreStatistics(StoreStatisticsInput input)
        {
            try
            {
                _logger.LogInformation("开始查询门店统计信息，查询日期：{StartDate} - {EndDate}，门店ID：{StoreId}", input.StartDate, input.EndDate, input.StoreId);

                // 构建查询参数
                var parameters = new Dictionary<string, object> { { "@startDate", input.StartDate.ToString("yyyy-MM-dd 00:00:00") }, { "@endDate", input.EndDate.ToString("yyyy-MM-dd 23:59:59") } };

                // 构建WHERE条件
                var whereClause = "WHERE 1=1";
                if (!string.IsNullOrEmpty(input.StoreId))
                {
                    whereClause += " AND store_id = @storeId";
                    parameters.Add("@storeId", input.StoreId);
                }

                // 使用SQL查询门店统计信息，包含日期范围过滤
                var sql =
                    $@"
                    SELECT
                        store_id AS StoreId,
                        store_code AS StoreCode,
                        store_name AS StoreName,
                        '' AS BusinessUnitId,
                        '' AS BusinessUnitName,
                        target_performance AS TargetPerformance,
                        actual_performance AS ActualPerformance,
                        completion_rate AS CompletionRate,
                        order_count AS OrderCount
                    FROM v_store_performance_simple
                    {whereClause}
                    ORDER BY actual_performance DESC";
                _logger.LogInformation("执行SQL查询：{Sql}", sql);
                _logger.LogInformation("查询参数：{Parameters}", string.Join(", ", parameters.Select(p => $"{p.Key}={p.Value}")));

                var results = await _db.Ado.SqlQueryAsync<dynamic>(sql, parameters);

                _logger.LogInformation("查询到门店统计数据数量：{Count}", results.Count);

                // 转换为输出格式
                var storeList = results
                    .Select(r => new StoreStatisticsInfo
                    {
                        StoreId = r.StoreId?.ToString() ?? "",
                        StoreName = r.StoreName?.ToString() ?? "",
                        StoreCode = r.StoreCode?.ToString() ?? "",
                        BusinessUnitId = r.BusinessUnitId?.ToString() ?? "",
                        BusinessUnitName = r.BusinessUnitName?.ToString() ?? "",
                        TargetPerformance = Convert.ToDecimal(r.TargetPerformance ?? 0),
                        ActualPerformance = Convert.ToDecimal(r.ActualPerformance ?? 0),
                        OrderCount = Convert.ToInt32(r.OrderCount ?? 0),
                        CompletionRate = Convert.ToDecimal(r.CompletionRate ?? 0),
                    })
                    .ToList();

                _logger.LogInformation("门店统计信息查询完成，返回{Count}条记录", storeList.Count);

                return new StoreStatisticsOutput { StoreList = storeList, TotalCount = storeList.Count };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询门店统计信息时发生错误，查询日期：{StartDate} - {EndDate}，门店ID：{StoreId}", input.StartDate, input.EndDate, input.StoreId);
                throw NCCException.Oh("查询门店统计信息失败", ex);
            }
        }
        #endregion

        #region 获取事业部业绩统计
        /// <summary>
        /// 获取事业部业绩统计
        /// </summary>
        /// <remarks>
        /// 根据指定日期范围和事业部ID查询事业部的业绩统计信息，包括目标业绩总和、完成业绩总和、完成率等
        ///
        /// 示例请求：
        /// ```json
        /// {
        ///   "startDate": "2025-01-01T00:00:00",
        ///   "endDate": "2025-01-31T23:59:59",
        ///   "businessUnitId": "事业部ID（可选）"
        /// }
        /// ```
        ///
        /// 参数说明：
        /// - startDate: 开始日期，格式：yyyy-MM-ddTHH:mm:ss
        /// - endDate: 结束日期，格式：yyyy-MM-ddTHH:mm:ss
        /// - businessUnitId: 事业部ID，可选参数，不传则查询所有事业部
        ///
        /// 返回数据说明：
        /// - DepartmentId: 部门ID
        /// - DepartmentName: 部门名称
        /// - ParentId: 父部门ID
        /// - ParentName: 父部门名称
        /// - TotalTargetAmount: 目标业绩总和
        /// - TotalActualAmount: 完成业绩总和
        /// - TotalOrderCount: 开单总数量
        /// - CompletionRate: 完成率（%）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>事业部业绩统计结果</returns>
        /// <response code="200">成功返回事业部业绩统计数据</response>
        /// <response code="400">请求参数错误</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("BusinessUnitStatistics")]
        [AllowAnonymous]
        public async Task<BusinessUnitStatisticsOutput> GetBusinessUnitStatistics(BusinessUnitStatisticsInput input)
        {
            try
            {
                _logger.LogInformation("开始查询事业部业绩统计，查询日期：{StartDate} - {EndDate}，事业部ID：{BusinessUnitId}", input.StartDate, input.EndDate, input.BusinessUnitId);

                // 构建查询参数
                var parameters = new Dictionary<string, object> { { "@startDate", input.StartDate.ToString("yyyy-MM-dd 00:00:00") }, { "@endDate", input.EndDate.ToString("yyyy-MM-dd 23:59:59") } };

                // 构建WHERE条件
                var whereClause = "WHERE 1=1";
                if (!string.IsNullOrEmpty(input.BusinessUnitId))
                {
                    whereClause += " AND dept.F_Id = @businessUnitId";
                    parameters.Add("@businessUnitId", input.BusinessUnitId);
                }

                // 构建SQL查询 - 先查询所有事业部，然后左连接业绩数据
                var sql =
                    $@"
                    SELECT
                        dept.F_Id AS DepartmentId,
                        dept.F_FullName AS DepartmentName,
                        dept.F_ParentId AS ParentId,
                        parent.F_FullName AS ParentName,
                        COALESCE(SUM(flow.actual_amount), 0) AS TotalActualAmount,
                        COALESCE(COUNT(flow.order_id), 0) AS TotalOrderCount,
                        CASE
                            WHEN COALESCE(SUM(flow.actual_amount), 0) > 0
                            THEN ROUND((COALESCE(SUM(flow.actual_amount), 0) / 100000) * 100, 2)
                            ELSE 0
                        END AS CompletionRate
                    FROM base_organize dept
                    LEFT JOIN base_organize parent ON dept.F_ParentId = parent.F_Id
                    LEFT JOIN v_department_performance_flow flow ON dept.F_Id = flow.department_id
                        AND (flow.order_date >= @startDate AND flow.order_date <= @endDate OR flow.order_date IS NULL)
                    WHERE dept.F_ParentId = (SELECT F_Id FROM base_organize WHERE F_FullName = '事业部')
                        AND dept.F_EnabledMark = 1
                        AND dept.F_DeleteMark IS NULL
                        {whereClause.Replace("WHERE 1=1", "")}
                    GROUP BY
                        dept.F_Id,
                        dept.F_FullName,
                        dept.F_ParentId,
                        parent.F_FullName
                    ORDER BY TotalActualAmount DESC";
                _logger.LogInformation("执行SQL查询：{Sql}", sql);
                _logger.LogInformation("查询参数：{Parameters}", string.Join(", ", parameters.Select(p => $"{p.Key}={p.Value}")));

                var results = await _db.Ado.SqlQueryAsync<dynamic>(sql, parameters);

                _logger.LogInformation("查询到事业部业绩数据数量：{Count}", results.Count);

                // 转换为输出格式
                var businessUnitList = results
                    .Select(r => new BusinessUnitStatisticsInfo
                    {
                        DepartmentId = r.DepartmentId?.ToString() ?? "",
                        DepartmentName = r.DepartmentName?.ToString() ?? "",
                        ParentId = r.ParentId?.ToString() ?? "",
                        ParentName = r.ParentName?.ToString() ?? "",
                        TotalTargetAmount = 100000, // 默认目标业绩10万
                        TotalActualAmount = Convert.ToDecimal(r.TotalActualAmount ?? 0),
                        TotalOrderCount = Convert.ToInt32(r.TotalOrderCount ?? 0),
                        CompletionRate = Convert.ToDecimal(r.CompletionRate ?? 0),
                    })
                    .ToList();

                _logger.LogInformation("事业部业绩统计查询完成，返回{Count}条记录", businessUnitList.Count);

                return new BusinessUnitStatisticsOutput { BusinessUnitList = businessUnitList, TotalCount = businessUnitList.Count };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询事业部业绩统计时发生错误，查询日期：{StartDate} - {EndDate}，事业部ID：{BusinessUnitId}", input.StartDate, input.EndDate, input.BusinessUnitId);
                throw NCCException.Oh("查询事业部业绩统计失败", ex);
            }
        }
        #endregion

        #region 获取其他部门业绩统计
        /// <summary>
        /// 获取其他部门业绩统计
        /// </summary>
        /// <remarks>
        /// 根据指定日期范围和部门ID查询其他部门（教育部、科技部、大项目部）的业绩统计信息
        ///
        /// 示例请求：
        /// ```json
        /// {
        ///   "startDate": "2025-01-01T00:00:00",
        ///   "endDate": "2025-01-31T23:59:59",
        ///   "departmentId": "部门ID（可选）"
        /// }
        /// ```
        ///
        /// 参数说明：
        /// - startDate: 开始日期，格式：yyyy-MM-ddTHH:mm:ss
        /// - endDate: 结束日期，格式：yyyy-MM-ddTHH:mm:ss
        /// - departmentId: 部门ID，可选参数，不传则查询所有其他部门
        ///
        /// 返回数据说明：
        /// - DepartmentId: 部门ID
        /// - DepartmentName: 部门名称
        /// - ParentId: 父部门ID
        /// - ParentName: 父部门名称
        /// - TotalTargetAmount: 目标业绩总和
        /// - TotalActualAmount: 完成业绩总和
        /// - TotalOrderCount: 开单总数量
        /// - CompletionRate: 完成率（%）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>其他部门业绩统计结果</returns>
        /// <response code="200">成功返回其他部门业绩统计数据</response>
        /// <response code="400">请求参数错误</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("OtherDepartmentStatistics")]
        [AllowAnonymous]
        public async Task<OtherDepartmentStatisticsOutput> GetOtherDepartmentStatistics(OtherDepartmentStatisticsInput input)
        {
            try
            {
                _logger.LogInformation("开始查询其他部门业绩统计，查询日期：{StartDate} - {EndDate}，部门ID：{DepartmentId}", input.StartDate, input.EndDate, input.DepartmentId);

                // 构建查询参数
                var parameters = new Dictionary<string, object> { { "@startDate", input.StartDate.ToString("yyyy-MM-dd 00:00:00") }, { "@endDate", input.EndDate.ToString("yyyy-MM-dd 23:59:59") } };

                // 构建WHERE条件
                var whereClause = "WHERE 1=1";
                if (!string.IsNullOrEmpty(input.DepartmentId))
                {
                    whereClause += " AND dept.F_Id = @departmentId";
                    parameters.Add("@departmentId", input.DepartmentId);
                }

                // 构建SQL查询 - 先查询所有其他部门的子部门，然后左连接业绩数据
                var sql =
                    $@"
                    SELECT
                        dept.F_Id AS DepartmentId,
                        dept.F_FullName AS DepartmentName,
                        dept.F_ParentId AS ParentId,
                        parent.F_FullName AS ParentName,
                        COALESCE(SUM(flow.actual_amount), 0) AS TotalActualAmount,
                        COALESCE(COUNT(flow.order_id), 0) AS TotalOrderCount,
                        CASE
                            WHEN COALESCE(SUM(flow.actual_amount), 0) > 0
                            THEN ROUND((COALESCE(SUM(flow.actual_amount), 0) / 50000) * 100, 2)
                            ELSE 0
                        END AS CompletionRate
                    FROM base_organize dept
                    LEFT JOIN base_organize parent ON dept.F_ParentId = parent.F_Id
                    LEFT JOIN v_other_department_performance_flow flow ON dept.F_Id = flow.department_id
                        AND (flow.order_date >= @startDate AND flow.order_date <= @endDate OR flow.order_date IS NULL)
                    WHERE dept.F_ParentId IN (SELECT F_Id FROM base_organize WHERE F_FullName IN ('教育部', '科技部', '大项目部'))
                        AND dept.F_EnabledMark = 1
                        AND dept.F_DeleteMark IS NULL
                        {whereClause.Replace("WHERE 1=1", "")}
                    GROUP BY
                        dept.F_Id,
                        dept.F_FullName,
                        dept.F_ParentId,
                        parent.F_FullName
                    ORDER BY TotalActualAmount DESC";
                _logger.LogInformation("执行SQL查询：{Sql}", sql);
                _logger.LogInformation("查询参数：{Parameters}", string.Join(", ", parameters.Select(p => $"{p.Key}={p.Value}")));

                var results = await _db.Ado.SqlQueryAsync<dynamic>(sql, parameters);

                _logger.LogInformation("查询到其他部门业绩数据数量：{Count}", results.Count);

                // 转换为输出格式
                var departmentList = results
                    .Select(r => new OtherDepartmentStatisticsInfo
                    {
                        DepartmentId = r.DepartmentId?.ToString() ?? "",
                        DepartmentName = r.DepartmentName?.ToString() ?? "",
                        ParentId = r.ParentId?.ToString() ?? "",
                        ParentName = r.ParentName?.ToString() ?? "",
                        TotalTargetAmount = 50000, // 默认目标业绩5万
                        TotalActualAmount = Convert.ToDecimal(r.TotalActualAmount ?? 0),
                        TotalOrderCount = Convert.ToInt32(r.TotalOrderCount ?? 0),
                        CompletionRate = Convert.ToDecimal(r.CompletionRate ?? 0),
                    })
                    .ToList();

                _logger.LogInformation("其他部门业绩统计查询完成，返回{Count}条记录", departmentList.Count);

                return new OtherDepartmentStatisticsOutput { DepartmentList = departmentList, TotalCount = departmentList.Count };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询其他部门业绩统计时发生错误，查询日期：{StartDate} - {EndDate}，部门ID：{DepartmentId}", input.StartDate, input.EndDate, input.DepartmentId);
                throw NCCException.Oh("查询其他部门业绩统计失败", ex);
            }
        }
        #endregion

        #region 获取经理业绩统计
        /// <summary>
        /// 获取经理业绩统计
        /// </summary>
        /// <remarks>
        /// 根据指定日期范围和事业部ID查询经理的业绩统计信息，按经理和门店维度进行统计
        ///
        /// 示例请求：
        /// ```json
        /// {
        ///   "startDate": "2025-01-01T00:00:00",
        ///   "endDate": "2025-01-31T23:59:59",
        ///   "businessUnitId": "事业部ID（可选）"
        /// }
        /// ```
        ///
        /// 参数说明：
        /// - startDate: 开始日期，格式：yyyy-MM-ddTHH:mm:ss
        /// - endDate: 结束日期，格式：yyyy-MM-ddTHH:mm:ss
        /// - businessUnitId: 事业部ID，可选参数，不传则查询所有事业部
        ///
        /// 返回数据说明：
        /// - ManagerName: 经理姓名
        /// - ManagerUserId: 经理用户ID
        /// - BusinessUnitName: 事业部名称
        /// - BusinessUnitId: 事业部ID
        /// - StoreName: 门店名称
        /// - StoreId: 门店ID
        /// - TargetPerformance: 目标业绩
        /// - ActualPerformance: 完成业绩
        /// - OrderCount: 开单数量
        /// - CompletionRate: 完成率（%）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>经理业绩统计结果</returns>
        /// <response code="200">成功返回经理业绩统计数据</response>
        /// <response code="400">请求参数错误</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("ManagerStatistics")]
        [AllowAnonymous]
        public async Task<ManagerStatisticsOutput> GetManagerStatistics(ManagerStatisticsInput input)
        {
            try
            {
                _logger.LogInformation("开始查询经理业绩统计，查询日期：{StartDate} - {EndDate}，事业部ID：{BusinessUnitId}", input.StartDate, input.EndDate, input.BusinessUnitId);

                // 构建查询参数
                var parameters = new Dictionary<string, object> { { "@startDate", input.StartDate.ToString("yyyy-MM-dd 00:00:00") }, { "@endDate", input.EndDate.ToString("yyyy-MM-dd 23:59:59") } };

                // 构建WHERE条件
                var whereClause = "WHERE (flow.order_date >= @startDate AND flow.order_date <= @endDate OR flow.order_date IS NULL)";

                if (!string.IsNullOrEmpty(input.BusinessUnitId))
                {
                    whereClause += " AND basic.business_unit_id = @businessUnitId";
                    parameters.Add("@businessUnitId", input.BusinessUnitId);
                }

                // 构建SQL查询 - 按经理和门店统计
                var sql =
                    $@"
                    SELECT
                        basic.manager_user_id AS ManagerUserId,
                        basic.manager_name AS ManagerName,
                        basic.business_unit_id AS BusinessUnitId,
                        basic.business_unit_name AS BusinessUnitName,
                        basic.store_id AS StoreId,
                        basic.store_name AS StoreName,
                        COALESCE(smx.smx1, 0) AS TargetPerformance,
                        SUM(COALESCE(flow.actual_amount, 0)) AS ActualPerformance,
                        COUNT(flow.order_id) AS OrderCount,
                        CASE
                            WHEN COALESCE(smx.smx1, 0) > 0
                            THEN ROUND((SUM(COALESCE(flow.actual_amount, 0)) / COALESCE(smx.smx1, 0)) * 100, 2)
                            ELSE 0
                        END AS CompletionRate
                    FROM v_manager_store_basic basic
                    LEFT JOIN lq_zjl_mdsmxsz smx ON basic.store_id = smx.md_id AND basic.manager_user_id = smx.zjl_userid
                    LEFT JOIN v_department_performance_flow flow ON basic.store_id = flow.store_id
                    {whereClause}
                    GROUP BY
                        basic.manager_user_id,
                        basic.manager_name,
                        basic.business_unit_id,
                        basic.business_unit_name,
                        basic.store_id,
                        basic.store_name,
                        smx.smx1
                    ORDER BY ActualPerformance DESC";
                _logger.LogInformation("执行SQL查询：{Sql}", sql);
                _logger.LogInformation("查询参数：{Parameters}", string.Join(", ", parameters.Select(p => $"{p.Key}={p.Value}")));

                var results = await _db.Ado.SqlQueryAsync<dynamic>(sql, parameters);

                _logger.LogInformation("查询到经理业绩数据数量：{Count}", results.Count);

                // 转换为输出格式
                var managerList = results
                    .Select(r => new ManagerStatisticsInfo
                    {
                        ManagerName = r.ManagerName?.ToString() ?? "",
                        ManagerUserId = r.ManagerUserId?.ToString() ?? "",
                        BusinessUnitName = r.BusinessUnitName?.ToString() ?? "",
                        BusinessUnitId = r.BusinessUnitId?.ToString() ?? "",
                        StoreName = r.StoreName?.ToString() ?? "",
                        StoreId = r.StoreId?.ToString() ?? "",
                        TargetPerformance = Convert.ToDecimal(r.TargetPerformance ?? 0),
                        ActualPerformance = Convert.ToDecimal(r.ActualPerformance ?? 0),
                        OrderCount = Convert.ToInt32(r.OrderCount ?? 0),
                        CompletionRate = Convert.ToDecimal(r.CompletionRate ?? 0),
                    })
                    .ToList();

                _logger.LogInformation("经理业绩统计查询完成，返回{Count}条记录", managerList.Count);

                return new ManagerStatisticsOutput { ManagerList = managerList, TotalCount = managerList.Count };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询经理业绩统计时发生错误，查询日期：{StartDate} - {EndDate}，事业部ID：{BusinessUnitId}", input.StartDate, input.EndDate, input.BusinessUnitId);
                throw NCCException.Oh("查询经理业绩统计失败", ex);
            }
        }
        #endregion

        #region 获取经理业绩汇总统计
        /// <summary>
        /// 获取经理业绩汇总统计
        /// </summary>
        /// <remarks>
        /// 统计每个经理的目标业绩总和、完成业绩总和、完成率等汇总信息
        ///
        /// 示例请求：
        /// ```json
        /// {
        ///   "startDate": "2025-01-01T00:00:00",
        ///   "endDate": "2025-01-31T23:59:59",
        ///   "businessUnitId": "事业部ID（可选）"
        /// }
        /// ```
        ///
        /// 参数说明：
        /// - startDate: 开始日期，格式：yyyy-MM-ddTHH:mm:ss
        /// - endDate: 结束日期，格式：yyyy-MM-ddTHH:mm:ss
        /// - businessUnitId: 事业部ID，可选参数，不传则查询所有事业部
        ///
        /// 返回数据说明：
        /// - ManagerName: 经理姓名
        /// - ManagerUserId: 经理用户ID
        /// - BusinessUnitName: 事业部名称
        /// - BusinessUnitId: 事业部ID
        /// - StoreCount: 管理门店数量
        /// - TotalTargetPerformance: 目标业绩总和
        /// - TotalActualPerformance: 完成业绩总和
        /// - CompletionRate: 完成率（%）
        /// - TotalOrderCount: 开单总数量
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>经理业绩汇总统计结果</returns>
        /// <response code="200">成功返回经理业绩汇总统计数据</response>
        /// <response code="400">请求参数错误</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("ManagerSummaryStatistics")]
        [AllowAnonymous]
        public async Task<ManagerSummaryStatisticsOutput> GetManagerSummaryStatistics(ManagerStatisticsInput input)
        {
            try
            {
                _logger.LogInformation("开始查询经理业绩汇总统计，查询日期：{StartDate} - {EndDate}，事业部ID：{BusinessUnitId}", input.StartDate, input.EndDate, input.BusinessUnitId);

                // 构建查询参数
                var parameters = new Dictionary<string, object> { { "@startDate", input.StartDate.ToString("yyyy-MM-dd 00:00:00") }, { "@endDate", input.EndDate.ToString("yyyy-MM-dd 23:59:59") } };

                // 构建WHERE条件
                var whereClause = "WHERE (flow.order_date >= @startDate AND flow.order_date <= @endDate OR flow.order_date IS NULL)";

                if (!string.IsNullOrEmpty(input.BusinessUnitId))
                {
                    whereClause += " AND basic.business_unit_id = @businessUnitId";
                    parameters.Add("@businessUnitId", input.BusinessUnitId);
                }

                // 构建SQL查询 - 按经理汇总统计
                var sql =
                    $@"
                    SELECT
                        basic.manager_user_id AS ManagerUserId,
                        basic.manager_name AS ManagerName,
                        basic.business_unit_id AS BusinessUnitId,
                        basic.business_unit_name AS BusinessUnitName,
                        COUNT(DISTINCT basic.store_id) AS StoreCount,
                        SUM(COALESCE(smx.smx1, 0)) AS TotalTargetPerformance,
                        SUM(COALESCE(flow.actual_amount, 0)) AS TotalActualPerformance,
                        COUNT(flow.order_id) AS TotalOrderCount,
                        CASE
                            WHEN SUM(COALESCE(smx.smx1, 0)) > 0
                            THEN ROUND((SUM(COALESCE(flow.actual_amount, 0)) / SUM(COALESCE(smx.smx1, 0))) * 100, 2)
                            ELSE 0
                        END AS CompletionRate
                    FROM v_manager_store_basic basic
                    LEFT JOIN lq_zjl_mdsmxsz smx ON basic.store_id = smx.md_id AND basic.manager_user_id = smx.zjl_userid
                    LEFT JOIN v_department_performance_flow flow ON basic.store_id = flow.store_id
                    {whereClause}
                    GROUP BY
                        basic.manager_user_id,
                        basic.manager_name,
                        basic.business_unit_id,
                        basic.business_unit_name
                    ORDER BY TotalActualPerformance DESC";
                _logger.LogInformation("执行SQL查询：{Sql}", sql);
                _logger.LogInformation("查询参数：{Parameters}", string.Join(", ", parameters.Select(p => $"{p.Key}={p.Value}")));

                var results = await _db.Ado.SqlQueryAsync<dynamic>(sql, parameters);

                _logger.LogInformation("查询到经理业绩汇总数据数量：{Count}", results.Count);

                // 转换为输出格式
                var managerSummaryList = results
                    .Select(r => new ManagerSummaryStatisticsInfo
                    {
                        ManagerName = r.ManagerName?.ToString() ?? "",
                        ManagerUserId = r.ManagerUserId?.ToString() ?? "",
                        BusinessUnitName = r.BusinessUnitName?.ToString() ?? "",
                        BusinessUnitId = r.BusinessUnitId?.ToString() ?? "",
                        StoreCount = Convert.ToInt32(r.StoreCount ?? 0),
                        TotalTargetPerformance = Convert.ToDecimal(r.TotalTargetPerformance ?? 0),
                        TotalActualPerformance = Convert.ToDecimal(r.TotalActualPerformance ?? 0),
                        TotalOrderCount = Convert.ToInt32(r.TotalOrderCount ?? 0),
                        CompletionRate = Convert.ToDecimal(r.CompletionRate ?? 0),
                    })
                    .ToList();

                _logger.LogInformation("经理业绩汇总统计查询完成，返回{Count}条记录", managerSummaryList.Count);

                return new ManagerSummaryStatisticsOutput { ManagerSummaryList = managerSummaryList, TotalCount = managerSummaryList.Count };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询经理业绩汇总统计时发生错误，查询日期：{StartDate} - {EndDate}，事业部ID：{BusinessUnitId}", input.StartDate, input.EndDate, input.BusinessUnitId);
                throw NCCException.Oh("查询经理业绩汇总统计失败", ex);
            }
        }
        #endregion

        #region 私有方法
        /// <summary>
        /// 递归获取子部门列表
        /// </summary>
        /// <param name="parentId">父部门ID</param>
        /// <returns>子部门列表</returns>
        private async Task<List<DepartmentInfo>> GetSubDepartmentsRecursively(string parentId)
        {
            var subDepartments = new List<DepartmentInfo>();

            try
            {
                // 查询直接子部门
                var directChildren = await _db.Queryable<OrganizeEntity>()
                    .Where(x => x.ParentId == parentId && x.EnabledMark == 1 && x.DeleteMark == null)
                    .Select(x => new DepartmentInfo
                    {
                        DepartmentId = x.Id,
                        DepartmentName = x.FullName,
                        ParentId = x.ParentId,
                    })
                    .ToListAsync();

                subDepartments.AddRange(directChildren);

                // 递归查询每个子部门的子部门
                foreach (var child in directChildren)
                {
                    var grandChildren = await GetSubDepartmentsRecursively(child.DepartmentId);
                    subDepartments.AddRange(grandChildren);
                }

                return subDepartments;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "递归获取子部门列表时发生错误，父部门ID：{ParentId}", parentId);
                return subDepartments;
            }
        }
        #endregion

        #region 匿名接口

        /// <summary>
        /// 发送每日统计消息（匿名接口）
        /// </summary>
        /// <returns>发送结果</returns>
        [HttpPost("SendDailyReportMessage")]
        [AllowAnonymous]
        public async Task<object> SendDailyReportMessage()
        {
            try
            {
                // 获取本月全门店统计数据
                var monthlyStats = await GetMonthlyStoreStatistics();

                // 构建包含统计数据的消息
                var messageContent =
                    $"📊 今日日报已生成，点击链接查看\n\n"
                    + $"本月全门店目标业绩：{monthlyStats.TargetPerformance:N0}元\n"
                    + $"本月已完成业绩：{monthlyStats.ActualPerformance:N0}元\n"
                    + $"完成率：{monthlyStats.CompletionRate:F2}%";
                // + $"https://erp.lvqianmeiye.com/html/dailyReportnew.html";
                var result = await _weChatBotService.SendTextMessage(messageContent);
                return new
                {
                    success = result,
                    message = result ? "每日统计消息发送成功" : "每日统计消息发送失败",
                    timestamp = DateTime.Now,
                    statistics = monthlyStats,
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "发送每日统计消息时发生错误");
                return new
                {
                    success = false,
                    message = "发送每日统计消息时发生错误: " + ex.Message,
                    timestamp = DateTime.Now,
                };
            }
        }

        /// <summary>
        /// 获取本月全门店统计数据
        /// </summary>
        /// <returns>本月统计数据</returns>
        private async Task<dynamic> GetMonthlyStoreStatistics()
        {
            try
            {
                // 获取本月开始和结束日期
                var now = DateTime.Now;
                var startDate = new DateTime(now.Year, now.Month, 1);
                var endDate = startDate.AddMonths(1).AddDays(-1);
                // 获取当前月份（YYYYMM格式）
                var currentMonth = now.ToString("yyyyMM");
                // 1. 获取本月的门店目标业绩总和（从门店目标表获取 F_StoreTarget 字段，按月份过滤）
                var targetPerformance = await _db.Queryable<LqMdTargetEntity>().Where(x => x.Month == currentMonth).SumAsync(x => (decimal?)x.StoreTarget) ?? 0m;
                // 2. 从开单记录表获取本月实付金额的总和（sfyj 字段）
                var endDateTime = endDate.Date.AddDays(1).AddSeconds(-1); // 结束日期的23:59:59
                var billingPerformance = await _db.Queryable<LqKdKdjlbEntity>().Where(x => x.IsEffective == 1).Where(x => x.Kdrq.HasValue && x.Kdrq.Value >= startDate && x.Kdrq.Value <= endDateTime).SumAsync(x => (decimal?)x.Sfyj) ?? 0m;
                // 3. 从退卡记录表获取本月退卡金额的总和（F_ActualRefundAmount 字段）
                var refundPerformance = await _db.Queryable<LqHytkHytkEntity>().Where(x => x.IsEffective == 1).Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date).SumAsync(x => x.ActualRefundAmount) ?? 0m;
                // 4. 计算实际开单业绩（开单业绩 - 退卡金额）
                var actualPerformance = billingPerformance - refundPerformance;
                // 5. 计算完成率（使用实际开单业绩）
                var completionRate = 0m;
                if (targetPerformance > 0)
                {
                    completionRate = (actualPerformance / targetPerformance) * 100m;
                }

                return new
                {
                    TargetPerformance = targetPerformance,
                    BillingPerformance = billingPerformance,
                    RefundPerformance = refundPerformance,
                    ActualPerformance = actualPerformance,
                    CompletionRate = decimal.Round(completionRate, 2),
                    Month = now.ToString("yyyy年MM月"),
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取本月全门店统计数据时发生错误");
                return new
                {
                    TargetPerformance = 0m,
                    BillingPerformance = 0m,
                    RefundPerformance = 0m,
                    ActualPerformance = 0m,
                    CompletionRate = 0m,
                    Month = DateTime.Now.ToString("yyyy年MM月"),
                };
            }
        }

        #endregion

        #region 金三角业绩统计
        /// <summary>
        /// 查询所有金三角的业绩统计
        /// </summary>
        /// <remarks>
        /// 根据金三角成员状态进行不同的统计方式：
        /// 1. 活跃成员：业绩计入金三角总业绩
        /// 2. 非活跃成员：按单人业绩统计，金三角名称为"门店名+姓名"
        ///
        /// 示例请求：
        /// GET /api/Extend/LqStatistics/GetJsjPerformance?startMonth=202401&amp;endMonth=202412
        ///
        /// 参数说明：
        /// - startMonth: 开始月份，格式为yyyyMM，可选
        /// - endMonth: 结束月份，格式为yyyyMM，可选
        /// - jsjId: 金三角ID，可选
        /// - storeId: 门店ID，可选
        ///
        /// 返回信息包括：
        /// - 金三角基本信息（ID、名称、月份、门店）
        /// - 业绩统计（订单数量、总业绩、首次/最后订单日期）
        /// - 统计类型（ACTIVE-活跃金三角业绩，INACTIVE-单人业绩）
        /// - 成员姓名（仅单人业绩时使用）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>金三角业绩统计列表</returns>
        /// <response code="200">查询成功</response>
        /// <response code="400">参数错误</response>
        [HttpGet("GetJsjPerformance")]
        public async Task<List<LqYcsdJsjPerformanceOutput>> GetJsjPerformance([FromQuery] LqYcsdJsjPerformanceInput input)
        {
            var result = new List<LqYcsdJsjPerformanceOutput>();

            try
            {
                // 1. 查询活跃金三角成员的业绩（计入金三角总业绩）
                var activePerformanceQuery = _db.Queryable<LqYcsdJsjEntity>()
                    .LeftJoin<LqJinsanjiaoUserEntity>((jsj, user) => jsj.Id == user.JsjId && user.Status == "ACTIVE" && user.DeleteMark == 0)
                    .LeftJoin<LqKdJksyjEntity>((jsj, user, jksyj) => jsj.Id == jksyj.Jsj_id && user.UserId == jksyj.Jkszh)
                    .LeftJoin<LqMdxxEntity>((jsj, user, jksyj, md) => jsj.Md == md.Id)
                    .Where((jsj, user, jksyj, md) => jsj.Yf != null && jksyj.Yjsj != null && jksyj.Jksyj != null && jksyj.Jksyj != "" && jksyj.Jksyj != "0")
                    .WhereIF(!string.IsNullOrEmpty(input.StartMonth), (jsj, user, jksyj, md) => jsj.Yf.CompareTo(input.StartMonth) >= 0)
                    .WhereIF(!string.IsNullOrEmpty(input.EndMonth), (jsj, user, jksyj, md) => jsj.Yf.CompareTo(input.EndMonth) <= 0)
                    .WhereIF(!string.IsNullOrEmpty(input.JsjId), (jsj, user, jksyj, md) => jsj.Id == input.JsjId)
                    .WhereIF(!string.IsNullOrEmpty(input.StoreId), (jsj, user, jksyj, md) => jsj.Md == input.StoreId)
                    .GroupBy(
                        (jsj, user, jksyj, md) =>
                            new
                            {
                                jsj.Id,
                                jsj.Jsj,
                                jsj.Yf,
                                jsj.Md,
                                md.Dm,
                            }
                    )
                    .Select(
                        (jsj, user, jksyj, md) =>
                            new LqYcsdJsjPerformanceOutput
                            {
                                JsjId = jsj.Id,
                                JsjName = jsj.Jsj,
                                Month = jsj.Yf,
                                StoreId = jsj.Md,
                                StoreName = md.Dm,
                                OrderCount = SqlFunc.AggregateCount(jksyj.Glkdbh),
                                TotalPerformance = SqlFunc.AggregateSum(SqlFunc.ToDecimal(jksyj.Jksyj)),
                                LastOrderDate = SqlFunc.AggregateMax(jksyj.Yjsj),
                                FirstOrderDate = SqlFunc.AggregateMin(jksyj.Yjsj),
                                StatisticsType = "ACTIVE",
                                MemberName = null,
                            }
                    );

                var activeResults = await activePerformanceQuery.ToListAsync();
                result.AddRange(activeResults);

                // 2. 查询非活跃金三角成员的业绩（按单人业绩统计）
                var inactivePerformanceQuery = _db.Queryable<LqJinsanjiaoUserEntity>()
                    .LeftJoin<LqYcsdJsjEntity>((user, jsj) => user.JsjId == jsj.Id)
                    .LeftJoin<LqKdJksyjEntity>((user, jsj, jksyj) => user.UserId == jksyj.Jkszh)
                    .LeftJoin<LqMdxxEntity>((user, jsj, jksyj, md) => jsj.Md == md.Id)
                    .Where(
                        (user, jsj, jksyj, md) =>
                            user.Status == "INACTIVE" && user.DeleteMark == 0 && jsj.Yf != null && jksyj.Yjsj != null && jksyj.Jksyj != null && jksyj.Jksyj != "" && jksyj.Jksyj != "0"
                    )
                    .WhereIF(!string.IsNullOrEmpty(input.StartMonth), (user, jsj, jksyj, md) => jsj.Yf.CompareTo(input.StartMonth) >= 0)
                    .WhereIF(!string.IsNullOrEmpty(input.EndMonth), (user, jsj, jksyj, md) => jsj.Yf.CompareTo(input.EndMonth) <= 0)
                    .WhereIF(!string.IsNullOrEmpty(input.JsjId), (user, jsj, jksyj, md) => jsj.Id == input.JsjId)
                    .WhereIF(!string.IsNullOrEmpty(input.StoreId), (user, jsj, jksyj, md) => jsj.Md == input.StoreId)
                    .GroupBy(
                        (user, jsj, jksyj, md) =>
                            new
                            {
                                user.Id,
                                user.UserName,
                                jsjId = jsj.Id,
                                jsj.Yf,
                                jsj.Md,
                                md.Dm,
                            }
                    )
                    .Select(
                        (user, jsj, jksyj, md) =>
                            new LqYcsdJsjPerformanceOutput
                            {
                                JsjId = user.Id, // 使用用户ID作为标识
                                JsjName = md.Dm + "-" + user.UserName, // 门店名+姓名
                                Month = jsj.Yf,
                                StoreId = jsj.Md,
                                StoreName = md.Dm,
                                OrderCount = SqlFunc.AggregateCount(jksyj.Glkdbh),
                                TotalPerformance = SqlFunc.AggregateSum(SqlFunc.ToDecimal(jksyj.Jksyj)),
                                LastOrderDate = SqlFunc.AggregateMax(jksyj.Yjsj),
                                FirstOrderDate = SqlFunc.AggregateMin(jksyj.Yjsj),
                                StatisticsType = "INACTIVE",
                                MemberName = user.UserName,
                            }
                    );

                var inactiveResults = await inactivePerformanceQuery.ToListAsync();
                result.AddRange(inactiveResults);

                // 3. 按月份和业绩排序
                return result.OrderByDescending(x => x.Month).ThenByDescending(x => x.TotalPerformance).ToList();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询金三角业绩统计时发生错误");
                throw NCCException.Oh(ErrorCode.COM1001, "查询金三角业绩统计失败");
            }
        }

        #endregion

        #region 金三角统计数据保存

        /// <summary>
        /// 【1】保存金三角开卡业绩统计数据
        /// </summary>
        /// <remarks>
        /// 根据金三角设定和开单记录统计金三角的业绩数据
        /// 使用SqlSugar框架实现，优化查询性能
        ///
        /// 示例请求：
        /// ```json
        /// POST /api/Extend/LqStatistics/save-gold-triangle-statistics
        /// {
        ///   "statisticsMonth": "202401"
        /// }
        /// ```
        /// </remarks>
        /// <param name="statisticsMonth">统计月份(YYYYMM格式)</param>
        /// <returns>保存结果</returns>
        /// <response code="200">成功保存统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("save-gold-triangle-stats")]
        public async Task<dynamic> SaveGoldTriangleStatistics([FromBody] SalaryStatisticsInput input)
        {
            if (input == null || string.IsNullOrEmpty(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
            {
                throw NCCException.Oh("统计月份格式错误，请使用YYYYMM格式");
            }

            var statisticsMonth = input.StatisticsMonth;

            try
            {
                // 使用数据库聚合方式，直接在数据库中完成所有统计计算
                var sql = @"
                    SELECT
                        jsj.F_Id AS GoldTriangleId,
                        jsj.jsj AS GoldTriangleName,
                        jsj.yf AS StatisticsMonth,
                        jsj.md AS StoreId,
                        COALESCE(md.dm, '') AS StoreName,
                        COUNT(DISTINCT jksyj.glkdbh) AS OrderCount,
                        SUM(CAST(jksyj.jksyj AS DECIMAL(18,2))) AS TotalPerformance,
                        MAX(jksyj.yjsj) AS LastOrderDate,
                        MIN(jksyj.yjsj) AS FirstOrderDate
                    FROM lq_ycsd_jsj jsj
                    LEFT JOIN lq_kd_jksyj jksyj ON (
                        jsj.F_Id = jksyj.jsj_id
                        AND YEAR(jksyj.yjsj) = SUBSTRING(jsj.yf, 1, 4)
                        AND MONTH(jksyj.yjsj) = SUBSTRING(jsj.yf, 5, 2)
                        AND jksyj.F_IsEffective = 1
                    )
                    LEFT JOIN lq_mdxx md ON jsj.md = md.F_Id
                    WHERE jsj.yf = @statisticsMonth
                        AND (jksyj.yjsj IS NULL OR (
                            jksyj.yjsj IS NOT NULL
                            AND jksyj.jksyj IS NOT NULL
                            AND jksyj.jksyj != ''
                            AND jksyj.jksyj != '0'
                        ))
                    GROUP BY
                        jsj.F_Id,
                        jsj.jsj,
                        jsj.yf,
                        jsj.md,
                        md.dm
                    ORDER BY TotalPerformance DESC";
                // 解析统计月份
                var year = int.Parse(statisticsMonth.Substring(0, 4));
                var month = int.Parse(statisticsMonth.Substring(4, 2));

                // 执行SQL查询
                var statisticsData = await _db.Ado.SqlQueryAsync<dynamic>(sql, new { statisticsMonth });

                if (!statisticsData.Any())
                {
                    return new
                    {
                        Success = true,
                        Message = $"未找到 {statisticsMonth} 月份的金三角统计数据",
                        SavedCount = 0
                    };
                }

                // 转换为实体对象
                var entities = statisticsData.Select(data => new LqStatisticsGoldTriangleEntity
                {
                    Id = YitIdHelper.NextId().ToString(),
                    GoldTriangleId = data.GoldTriangleId?.ToString() ?? "",
                    GoldTriangleName = data.GoldTriangleName?.ToString() ?? "",
                    StatisticsMonth = data.StatisticsMonth?.ToString() ?? statisticsMonth,
                    StoreId = data.StoreId?.ToString() ?? "",
                    StoreName = data.StoreName?.ToString() ?? "",
                    OrderCount = Convert.ToInt32(data.OrderCount ?? 0),
                    TotalPerformance = Convert.ToDecimal(data.TotalPerformance ?? 0),
                    LastOrderDate = data.LastOrderDate as DateTime?,
                    FirstOrderDate = data.FirstOrderDate as DateTime?,
                    CreateTime = DateTime.Now
                }).ToList();

                // 使用事务确保数据一致性
                var result = await _db.Ado.UseTranAsync(async () =>
                {
                    // 先删除该月份的历史数据
                    await _db.Deleteable<LqStatisticsGoldTriangleEntity>()
                        .Where(x => x.StatisticsMonth == statisticsMonth)
                        .ExecuteCommandAsync();

                    // 批量插入新数据
                    return await _db.Insertable(entities).ExecuteCommandAsync();
                });

                var savedCount = result.IsSuccess ? result.Data : 0;
                _logger.LogInformation($"成功保存金三角统计数据 - 月份: {statisticsMonth}, 记录数: {savedCount}");

                return new
                {
                    Success = true,
                    Message = $"成功保存 {savedCount} 条金三角统计数据",
                    SavedCount = savedCount,
                    StatisticsMonth = statisticsMonth
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"保存金三角统计数据失败 - 月份: {statisticsMonth}");
                throw NCCException.Oh($"保存金三角统计数据失败: {ex.Message}");
            }
        }

        /// <summary>
        /// 获取金三角统计数据
        /// </summary>
        /// <remarks>
        /// 查询指定月份的金三角统计数据
        ///
        /// 示例请求：
        /// ```json
        /// GET /api/Extend/LqStatistics/get-gold-triangle-statistics?statisticsMonth=202401
        /// ```
        /// </remarks>
        /// <param name="statisticsMonth">统计月份(YYYYMM格式)</param>
        /// <returns>统计数据列表</returns>
        /// <response code="200">成功返回统计数据</response>
        /// <response code="400">参数错误</response>
        [HttpGet("get-gold-triangle-statistics")]
        public async Task<dynamic> GetGoldTriangleStatistics(string statisticsMonth)
        {
            if (string.IsNullOrEmpty(statisticsMonth))
            {
                throw NCCException.Oh("统计月份不能为空");
            }

            try
            {
                var statistics = await _db.Queryable<LqStatisticsGoldTriangleEntity>()
                    .Where(x => x.StatisticsMonth == statisticsMonth)
                    .OrderBy(x => x.TotalPerformance, OrderByType.Desc)
                    .ToListAsync();

                return new
                {
                    Success = true,
                    Data = statistics,
                    Count = statistics.Count,
                    StatisticsMonth = statisticsMonth
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"查询金三角统计数据失败 - 月份: {statisticsMonth}");
                throw NCCException.Oh($"查询金三角统计数据失败: {ex.Message}");
            }
        }

        #endregion

        #region 个人开单业绩统计

        /// <summary>
        /// 【2】保存健康师个人开单业绩统计数据
        /// </summary>
        /// <remarks>
        /// 根据开单记录统计个人的业绩数据，包括基础业绩和合作业绩
        /// 基础业绩和合作业绩的划分根据品项ID查询lq_xmzl表的fl3字段
        /// 使用数据库聚合方式优化性能，避免大量数据加载到内存
        ///
        /// 示例请求：
        /// ```json
        /// POST /api/Extend/LqStatistics/save-personal-performance-statistics
        /// {
        ///   "statisticsMonth": "202401"
        /// }
        /// ```
        /// </remarks>
        /// <param name="statisticsMonth">统计月份(YYYYMM格式)</param>
        /// <returns>保存结果</returns>
        /// <response code="200">成功保存统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("save-personal-performance-stats")]
        public async Task<dynamic> SavePersonalPerformanceStatistics([FromBody] SalaryStatisticsInput input)
        {
            if (input == null || string.IsNullOrEmpty(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
            {
                throw NCCException.Oh("统计月份格式错误，请使用YYYYMM格式");
            }

            var statisticsMonth = input.StatisticsMonth;

            try
            {
                // 使用子查询避免重复计算，按开单记录统计业绩
                var sql = @"
                    SELECT
                        order_stats.EmployeeId,
                        order_stats.EmployeeName,
                        order_stats.StoreId,
                        order_stats.StoreName,
                        order_stats.GoldTriangleId,
                        order_stats.GoldTriangleName,
                        order_stats.Position,
                        order_stats.OrderCount,
                        order_stats.FirstOrderCount,
                        order_stats.UpgradeOrderCount,
                        order_stats.FirstOrderPerformance,
                        order_stats.UpgradeOrderPerformance,
                        order_stats.LastOrderDate,
                        order_stats.FirstOrderDate,
                        COALESCE(coop_stats.CooperationPerformance, 0) AS CooperationPerformance,
                        COALESCE(base_stats.BasePerformance, 0) AS BasePerformance,
                        COALESCE(refund_stats.RefundPerformance, 0) AS RefundPerformance,
                        COALESCE(refund_stats.RefundCount, 0) AS RefundCount,
                        order_stats.TotalPerformance
                    FROM (
                        -- 按开单记录统计基础数据，避免重复计算
                        SELECT
                            order_summary.jkszh AS EmployeeId,
                            u.F_REALNAME AS EmployeeName,
                            u.F_MDID AS StoreId,
                            COALESCE(md.dm, '') AS StoreName,
                            COALESCE(jsjUser.F_Id, '') AS GoldTriangleId,
                            COALESCE(jsjUser.jsj, '') AS GoldTriangleName,
                            CASE
                                WHEN jsjUser.is_leader = 1 THEN '顾问'
                                ELSE COALESCE(u.F_GW, '')
                            END AS Position,
                            COUNT(*) AS OrderCount,
                            COUNT(CASE WHEN order_summary.sfskdd = '是' THEN 1 END) AS FirstOrderCount,
                            COUNT(CASE WHEN order_summary.sfskdd = '否' THEN 1 END) AS UpgradeOrderCount,
                            SUM(CASE WHEN order_summary.sfskdd = '是' THEN order_summary.order_performance ELSE 0 END) AS FirstOrderPerformance,
                            SUM(CASE WHEN order_summary.sfskdd = '否' THEN order_summary.order_performance ELSE 0 END) AS UpgradeOrderPerformance,
                            MAX(order_summary.yjsj) AS LastOrderDate,
                            MIN(order_summary.yjsj) AS FirstOrderDate,
                            SUM(order_summary.order_performance) AS TotalPerformance
                        FROM (
                            -- 先按开单记录汇总业绩，避免重复计算
                            SELECT
                                jksyj.jkszh,
                                jksyj.glkdbh,
                                kd.sfskdd,
                                MAX(jksyj.yjsj) as yjsj,
                                SUM(CAST(jksyj.jksyj AS DECIMAL(18,2))) as order_performance
                            FROM lq_kd_jksyj jksyj
                            INNER JOIN lq_kd_pxmx pxmx ON jksyj.F_kdpxid = pxmx.F_Id AND pxmx.F_IsEffective = 1
                            INNER JOIN lq_kd_kdjlb kd ON jksyj.glkdbh = CONVERT(kd.F_Id USING utf8mb4)
                            WHERE jksyj.yjsj IS NOT NULL
                                AND jksyj.jksyj IS NOT NULL
                                AND jksyj.jksyj != ''
                                AND jksyj.jksyj != '0'
                                AND jksyj.F_kdpxid IS NOT NULL
                                AND jksyj.F_kdpxid != ''
                                AND jksyj.F_IsEffective = 1
                                AND YEAR(jksyj.yjsj) = @year
                                AND MONTH(jksyj.yjsj) = @month
                            GROUP BY jksyj.jkszh, jksyj.glkdbh, kd.sfskdd
                        ) order_summary
                        INNER JOIN BASE_USER u ON order_summary.jkszh = u.F_Id
                        LEFT JOIN lq_mdxx md ON u.F_MDID = md.F_Id
                        LEFT JOIN (
                            SELECT
                                jsjUser.user_id,
                                MIN(jsjUser.jsj_id) as F_Id,
                                MIN(jsj.jsj) as jsj,
                                MIN(jsjUser.is_leader) as is_leader
                            FROM lq_jinsanjiao_user jsjUser
                            INNER JOIN lq_ycsd_jsj jsj ON jsjUser.jsj_id COLLATE utf8mb4_general_ci = jsj.F_Id COLLATE utf8mb4_general_ci AND jsj.yf = @statisticsMonth
                            WHERE jsjUser.F_Month = @statisticsMonth
                                AND jsjUser.status = 'ACTIVE'
                                AND jsjUser.F_DeleteMark = 0
                            GROUP BY jsjUser.user_id
                        ) jsjUser ON order_summary.jkszh = jsjUser.user_id
                        GROUP BY
                            order_summary.jkszh,
                            u.F_REALNAME,
                            u.F_MDID,
                            md.dm,
                            jsjUser.F_Id,
                            jsjUser.jsj,
                            jsjUser.is_leader,
                            u.F_GW
                    ) order_stats
                    LEFT JOIN (
                        -- 合作业绩统计
                        SELECT
                            jksyj.jkszh AS EmployeeId,
                            SUM(CAST(jksyj.jksyj AS DECIMAL(18,2))) AS CooperationPerformance
                        FROM lq_kd_jksyj jksyj
                        INNER JOIN lq_kd_pxmx pxmx ON jksyj.F_kdpxid = pxmx.F_Id AND pxmx.F_IsEffective = 1
                        INNER JOIN lq_xmzl xmzl ON pxmx.px = xmzl.F_Id
                        WHERE jksyj.yjsj IS NOT NULL
                            AND jksyj.jksyj IS NOT NULL
                            AND jksyj.jksyj != ''
                            AND jksyj.jksyj != '0'
                            AND jksyj.F_kdpxid IS NOT NULL
                            AND jksyj.F_kdpxid != ''
                            AND jksyj.F_IsEffective = 1
                            AND jksyj.yjsj >= @startDate
                            AND jksyj.yjsj <= @endDate
                            AND xmzl.fl3 = '合作业绩'
                        GROUP BY jksyj.jkszh
                    ) coop_stats ON order_stats.EmployeeId = coop_stats.EmployeeId
                    LEFT JOIN (
                        -- 基础业绩统计
                        SELECT
                            jksyj.jkszh AS EmployeeId,
                            SUM(CAST(jksyj.jksyj AS DECIMAL(18,2))) AS BasePerformance
                        FROM lq_kd_jksyj jksyj
                        INNER JOIN lq_kd_pxmx pxmx ON jksyj.F_kdpxid = pxmx.F_Id AND pxmx.F_IsEffective = 1
                        LEFT JOIN lq_xmzl xmzl ON pxmx.px = xmzl.F_Id
                        WHERE jksyj.yjsj IS NOT NULL
                            AND jksyj.jksyj IS NOT NULL
                            AND jksyj.jksyj != ''
                            AND jksyj.jksyj != '0'
                            AND jksyj.F_kdpxid IS NOT NULL
                            AND jksyj.F_kdpxid != ''
                            AND jksyj.F_IsEffective = 1
                            AND jksyj.yjsj >= @startDate
                            AND jksyj.yjsj <= @endDate
                            AND (xmzl.fl3 IS NULL OR xmzl.fl3 != '合作业绩')
                        GROUP BY jksyj.jkszh
                    ) base_stats ON order_stats.EmployeeId = base_stats.EmployeeId
                    LEFT JOIN (
                        -- 退单业绩统计
                        SELECT
                            hytk_jksyj.jkszh AS EmployeeId,
                            SUM(CAST(hytk_jksyj.jksyj AS DECIMAL(18,2))) AS RefundPerformance,
                            COUNT(*) AS RefundCount
                        FROM lq_hytk_jksyj hytk_jksyj
                        INNER JOIN lq_hytk_hytk hytk ON hytk_jksyj.gltkbh = hytk.F_Id
                        WHERE hytk_jksyj.jksyj IS NOT NULL
                            AND hytk_jksyj.jksyj != ''
                            AND hytk_jksyj.jksyj != '0'
                            AND hytk_jksyj.F_IsEffective = 1
                            AND hytk.F_IsEffective = 1
                            AND hytk_jksyj.tksj >= @startDate
                            AND hytk_jksyj.tksj <= @endDate
                        GROUP BY hytk_jksyj.jks
                    ) refund_stats ON order_stats.EmployeeId = refund_stats.EmployeeId
                    ORDER BY order_stats.TotalPerformance DESC";
                // 解析统计月份
                var year = int.Parse(statisticsMonth.Substring(0, 4));
                var month = int.Parse(statisticsMonth.Substring(4, 2));

                var parameters = new Dictionary<string, object>
                {
                    { "@statisticsMonth", statisticsMonth },
                    { "@year", year },
                    { "@month", month }
                };

                _logger.LogInformation($"执行个人业绩统计SQL - 月份: {statisticsMonth}");

                var statisticsData = await _db.Ado.SqlQueryAsync<dynamic>(sql, parameters);

                if (!statisticsData.Any())
                {
                    return new
                    {
                        Success = true,
                        Message = $"未找到 {statisticsMonth} 月份的开单记录数据",
                        SavedCount = 0
                    };
                }

                _logger.LogInformation($"查询到个人业绩统计数据: {statisticsData.Count} 条");

                // 创建实体列表
                var entities = statisticsData.Select(stats => new LqStatisticsPersonalPerformanceEntity
                {
                    Id = YitIdHelper.NextId().ToString(),
                    StatisticsMonth = statisticsMonth,
                    StoreId = stats.StoreId?.ToString() ?? "",
                    StoreName = stats.StoreName?.ToString() ?? "",
                    GoldTriangleId = stats.GoldTriangleId?.ToString() ?? "",
                    GoldTriangleName = stats.GoldTriangleName?.ToString() ?? "",
                    Position = stats.Position?.ToString() ?? "",
                    EmployeeId = stats.EmployeeId?.ToString() ?? "",
                    EmployeeName = stats.EmployeeName?.ToString() ?? "",
                    TotalPerformance = Convert.ToDecimal(stats.TotalPerformance ?? 0) - Convert.ToDecimal(stats.RefundPerformance ?? 0),
                    BasePerformance = Convert.ToDecimal(stats.BasePerformance ?? 0),
                    CooperationPerformance = Convert.ToDecimal(stats.CooperationPerformance ?? 0),
                    RefundPerformance = Convert.ToDecimal(stats.RefundPerformance ?? 0),
                    RefundCount = Convert.ToInt32(stats.RefundCount ?? 0),
                    ActualPerformance = Convert.ToDecimal(stats.TotalPerformance ?? 0) - Convert.ToDecimal(stats.RefundPerformance ?? 0),
                    OrderCount = Convert.ToInt32(stats.OrderCount ?? 0),
                    FirstOrderCount = Convert.ToInt32(stats.FirstOrderCount ?? 0),
                    UpgradeOrderCount = Convert.ToInt32(stats.UpgradeOrderCount ?? 0),
                    FirstOrderPerformance = Convert.ToDecimal(stats.FirstOrderPerformance ?? 0),
                    UpgradeOrderPerformance = Convert.ToDecimal(stats.UpgradeOrderPerformance ?? 0),
                    LastOrderDate = stats.LastOrderDate as DateTime?,
                    FirstOrderDate = stats.FirstOrderDate as DateTime?,
                    CreateTime = DateTime.Now
                }).ToList();

                // 使用事务确保数据一致性
                var result = await _db.Ado.UseTranAsync(async () =>
                {
                    // 先删除该月份的历史数据
                    await _db.Deleteable<LqStatisticsPersonalPerformanceEntity>()
                        .Where(x => x.StatisticsMonth == statisticsMonth)
                        .ExecuteCommandAsync();

                    // 批量插入新数据
                    return await _db.Insertable(entities).ExecuteCommandAsync();
                });

                var savedCount = result.IsSuccess ? result.Data : 0;
                _logger.LogInformation($"成功保存个人业绩统计数据 - 月份: {statisticsMonth}, 记录数: {savedCount}");

                return new
                {
                    Success = true,
                    Message = $"成功保存 {savedCount} 条个人业绩统计数据",
                    SavedCount = savedCount,
                    StatisticsMonth = statisticsMonth
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"保存个人业绩统计数据失败 - 月份: {statisticsMonth}");
                throw NCCException.Oh($"保存个人业绩统计数据失败: {ex.Message}");
            }
        }

        /// <summary>
        /// 获取个人开单业绩统计数据
        /// </summary>
        /// <remarks>
        /// 查询指定月份的个人业绩统计数据
        ///
        /// 示例请求：
        /// ```json
        /// GET /api/Extend/LqStatistics/get-personal-performance-statistics?statisticsMonth=202401
        /// ```
        /// </remarks>
        /// <param name="statisticsMonth">统计月份(YYYYMM格式)</param>
        /// <returns>统计数据列表</returns>
        /// <response code="200">成功返回统计数据</response>
        /// <response code="400">参数错误</response>
        [HttpGet("get-personal-performance-statistics")]
        public async Task<dynamic> GetPersonalPerformanceStatistics(string statisticsMonth)
        {
            if (string.IsNullOrEmpty(statisticsMonth))
            {
                throw NCCException.Oh("统计月份不能为空");
            }

            try
            {
                var statistics = await _db.Queryable<LqStatisticsPersonalPerformanceEntity>()
                    .Where(x => x.StatisticsMonth == statisticsMonth)
                    .OrderBy(x => x.TotalPerformance, OrderByType.Desc)
                    .ToListAsync();

                return statistics;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"查询个人业绩统计数据失败 - 月份: {statisticsMonth}");
                throw NCCException.Oh($"查询个人业绩统计数据失败: {ex.Message}");
            }
        }

        /// <summary>
        /// 分页查询个人开单业绩统计数据（实时查询）
        /// </summary>
        /// <remarks>
        /// 实时查询个人业绩统计数据，支持多条件筛选，直接从开单记录表统计
        ///
        /// 示例请求：
        /// ```json
        /// POST /api/Extend/LqStatistics/get-personal-performance-statistics-list
        /// {
        ///   "statisticsMonth": "202401",
        ///   "storeId": "store123",
        ///   "employeeName": "张三",
        ///   "currentPage": 1,
        ///   "pageSize": 20
        /// }
        /// ```
        /// </remarks>
        /// <param name="input">查询条件</param>
        /// <returns>分页统计数据</returns>
        /// <response code="200">成功返回分页数据</response>
        /// <response code="400">参数错误</response>
        [HttpPost("get-personal-performance-statistics-list")]
        public async Task<dynamic> GetPersonalPerformanceStatisticsList(LqStatisticsPersonalPerformanceListQueryInput input)
        {
            try
            {
                // 验证统计月份必填
                if (string.IsNullOrEmpty(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
                {
                    throw NCCException.Oh("统计月份不能为空，格式为YYYYMM");
                }

                var statisticsMonth = input.StatisticsMonth;
                var year = int.Parse(statisticsMonth.Substring(0, 4));
                var month = int.Parse(statisticsMonth.Substring(4, 2));

                // 计算日期范围（使用日期范围查询替代YEAR/MONTH函数，提升性能）
                var startDate = new DateTime(year, month, 1);
                var endDate = startDate.AddMonths(1).AddDays(-1).Date.AddHours(23).AddMinutes(59).AddSeconds(59);

                // 构建筛选条件
                var innerWhereConditions = new List<string>(); // 子查询中的筛选条件
                var outerWhereConditions = new List<string>(); // 外层查询的筛选条件
                var parameters = new Dictionary<string, object>
                {
                    { "@statisticsMonth", statisticsMonth },
                    { "@startDate", startDate },
                    { "@endDate", endDate }
                };

                // 员工ID筛选（在子查询中）
                if (!string.IsNullOrEmpty(input.EmployeeId))
                {
                    innerWhereConditions.Add("jksyj.jkszh = @EmployeeId");
                    parameters.Add("@EmployeeId", input.EmployeeId);
                }

                // 门店ID筛选（在JOIN后）
                if (!string.IsNullOrEmpty(input.StoreId))
                {
                    outerWhereConditions.Add("u.F_MDID = @StoreId");
                    parameters.Add("@StoreId", input.StoreId);
                }

                // 门店名称筛选（在JOIN后）
                if (!string.IsNullOrEmpty(input.StoreName))
                {
                    outerWhereConditions.Add("md.dm LIKE @StoreName");
                    parameters.Add("@StoreName", $"%{input.StoreName}%");
                }

                // 员工姓名筛选（在JOIN后）
                if (!string.IsNullOrEmpty(input.EmployeeName))
                {
                    outerWhereConditions.Add("u.F_REALNAME LIKE @EmployeeName");
                    parameters.Add("@EmployeeName", $"%{input.EmployeeName}%");
                }

                // 金三角ID筛选（在JOIN后）
                if (!string.IsNullOrEmpty(input.GoldTriangleId))
                {
                    outerWhereConditions.Add("jsjUser.F_Id = @GoldTriangleId");
                    parameters.Add("@GoldTriangleId", input.GoldTriangleId);
                }

                var innerWhereClause = innerWhereConditions.Any() ? " AND " + string.Join(" AND ", innerWhereConditions) : "";
                var outerWhereClause = outerWhereConditions.Any() ? "WHERE " + string.Join(" AND ", outerWhereConditions) : "";

                // 构建优化的主查询SQL - 合并查询减少扫描次数，增加按品项分类的业绩统计
                var sql = $@"
                    SELECT
                        order_stats.EmployeeId,
                        order_stats.EmployeeName,
                        order_stats.StoreId,
                        order_stats.StoreName,
                        order_stats.GoldTriangleId,
                        order_stats.GoldTriangleName,
                        order_stats.Position,
                        order_stats.OrderCount,
                        order_stats.FirstOrderCount,
                        order_stats.UpgradeOrderCount,
                        order_stats.FirstOrderPerformance,
                        order_stats.UpgradeOrderPerformance,
                        order_stats.LastOrderDate,
                        order_stats.FirstOrderDate,
                        COALESCE(coop_stats.CooperationPerformance, 0) AS CooperationPerformance,
                        COALESCE(order_stats.TotalPerformance, 0) - COALESCE(coop_stats.CooperationPerformance, 0) AS BasePerformance,
                        COALESCE(refund_stats.RefundPerformance, 0) AS RefundPerformance,
                        COALESCE(refund_stats.RefundCount, 0) AS RefundCount,
                        order_stats.TotalPerformance,
                        COALESCE(cat_stats.LifeBeautyPerformance, 0) AS LifeBeautyPerformance,
                        COALESCE(cat_stats.MedicalBeautyPerformance, 0) AS MedicalBeautyPerformance,
                        COALESCE(cat_stats.TechBeautyPerformance, 0) AS TechBeautyPerformance,
                        COALESCE(cat_stats.CooperationCategoryPerformance, 0) AS CooperationCategoryPerformance,
                        COALESCE(cat_stats.OtherPerformance, 0) AS OtherPerformance,
                        COALESCE(cat_stats.ProductPerformance, 0) AS ProductPerformance
                    FROM (
                        SELECT
                            order_base.jkszh AS EmployeeId,
                            u.F_REALNAME AS EmployeeName,
                            u.F_MDID AS StoreId,
                            COALESCE(md.dm, '') AS StoreName,
                            COALESCE(jsjUser.F_Id, '') AS GoldTriangleId,
                            COALESCE(jsjUser.jsj, '') AS GoldTriangleName,
                            CASE
                                WHEN jsjUser.is_leader = 1 THEN '顾问'
                                ELSE COALESCE(u.F_GW, '')
                            END AS Position,
                            COUNT(*) AS OrderCount,
                            COUNT(CASE WHEN order_base.sfskdd = '是' THEN 1 END) AS FirstOrderCount,
                            COUNT(CASE WHEN order_base.sfskdd = '否' THEN 1 END) AS UpgradeOrderCount,
                            SUM(CASE WHEN order_base.sfskdd = '是' THEN order_base.order_performance ELSE 0 END) AS FirstOrderPerformance,
                            SUM(CASE WHEN order_base.sfskdd = '否' THEN order_base.order_performance ELSE 0 END) AS UpgradeOrderPerformance,
                            MAX(order_base.yjsj) AS LastOrderDate,
                            MIN(order_base.yjsj) AS FirstOrderDate,
                            SUM(order_base.order_performance) AS TotalPerformance,
                            0 AS CooperationPerformance,
                            0 AS BasePerformance
                        FROM (
                            -- 基础开单数据汇总（不包含合作业绩分类，提升性能）
                            SELECT
                                jksyj.jkszh,
                                jksyj.glkdbh,
                                kd.sfskdd,
                                MAX(jksyj.yjsj) as yjsj,
                                SUM(CAST(jksyj.jksyj AS DECIMAL(18,2))) as order_performance
                            FROM lq_kd_jksyj jksyj
                            INNER JOIN lq_kd_pxmx pxmx ON jksyj.F_kdpxid = pxmx.F_Id AND pxmx.F_IsEffective = 1
                            INNER JOIN lq_kd_kdjlb kd ON jksyj.glkdbh = CONVERT(kd.F_Id USING utf8mb4)
                            WHERE jksyj.yjsj IS NOT NULL
                                AND jksyj.jksyj IS NOT NULL
                                AND jksyj.jksyj != ''
                                AND jksyj.jksyj != '0'
                                AND jksyj.F_kdpxid IS NOT NULL
                                AND jksyj.F_kdpxid != ''
                                AND jksyj.F_IsEffective = 1
                                AND jksyj.yjsj >= @startDate
                                AND jksyj.yjsj <= @endDate
                                {innerWhereClause}
                            GROUP BY jksyj.jkszh, jksyj.glkdbh, kd.sfskdd
                        ) order_base
                        INNER JOIN BASE_USER u ON order_base.jkszh = u.F_Id
                        LEFT JOIN lq_mdxx md ON u.F_MDID = md.F_Id
                        LEFT JOIN (
                            SELECT
                                jsjUser.user_id,
                                MIN(jsjUser.jsj_id) as F_Id,
                                MIN(jsj.jsj) as jsj,
                                MIN(jsjUser.is_leader) as is_leader
                            FROM lq_jinsanjiao_user jsjUser
                            INNER JOIN lq_ycsd_jsj jsj ON jsjUser.jsj_id COLLATE utf8mb4_general_ci = jsj.F_Id COLLATE utf8mb4_general_ci AND jsj.yf = @statisticsMonth
                            WHERE jsjUser.F_Month = @statisticsMonth
                                AND jsjUser.status = 'ACTIVE'
                                AND jsjUser.F_DeleteMark = 0
                            GROUP BY jsjUser.user_id
                        ) jsjUser ON order_base.jkszh = jsjUser.user_id
                        {outerWhereClause}
                        GROUP BY
                            order_base.jkszh,
                            u.F_REALNAME,
                            u.F_MDID,
                            md.dm,
                            jsjUser.F_Id,
                            jsjUser.jsj,
                            jsjUser.is_leader,
                            u.F_GW
                    ) order_stats
                    LEFT JOIN (
                        -- 合作业绩统计（单独查询，提升性能）
                        SELECT
                            jksyj.jkszh AS EmployeeId,
                            SUM(CAST(jksyj.jksyj AS DECIMAL(18,2))) AS CooperationPerformance
                        FROM lq_kd_jksyj jksyj
                        INNER JOIN lq_kd_pxmx pxmx ON jksyj.F_kdpxid = pxmx.F_Id AND pxmx.F_IsEffective = 1
                        INNER JOIN lq_xmzl xmzl ON pxmx.px = xmzl.F_Id AND xmzl.fl3 = '合作业绩'
                        WHERE jksyj.yjsj IS NOT NULL
                            AND jksyj.jksyj IS NOT NULL
                            AND jksyj.jksyj != ''
                            AND jksyj.jksyj != '0'
                            AND jksyj.F_kdpxid IS NOT NULL
                            AND jksyj.F_kdpxid != ''
                            AND jksyj.F_IsEffective = 1
                            AND jksyj.yjsj >= @startDate
                            AND jksyj.yjsj <= @endDate
                        GROUP BY jksyj.jkszh
                    ) coop_stats ON order_stats.EmployeeId = coop_stats.EmployeeId
                    LEFT JOIN (
                        -- 按品项分类统计业绩（生美、医美、科美、合作、其他、产品），与 order_base 使用相同 JOIN 确保分类之和=总业绩
                        SELECT
                            jksyj.jkszh AS EmployeeId,
                            SUM(CASE WHEN COALESCE(jksyj.F_ItemCategory, '') = '生美' THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END) AS LifeBeautyPerformance,
                            SUM(CASE WHEN COALESCE(jksyj.F_ItemCategory, '') = '医美' THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END) AS MedicalBeautyPerformance,
                            SUM(CASE WHEN COALESCE(jksyj.F_ItemCategory, '') = '科美' THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END) AS TechBeautyPerformance,
                            SUM(CASE WHEN COALESCE(jksyj.F_ItemCategory, '') = '合作' THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END) AS CooperationCategoryPerformance,
                            SUM(CASE WHEN COALESCE(jksyj.F_ItemCategory, '') = '其他' OR jksyj.F_ItemCategory IS NULL OR jksyj.F_ItemCategory = '' THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END) AS OtherPerformance,
                            SUM(CASE WHEN COALESCE(jksyj.F_ItemCategory, '') = '产品' THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END) AS ProductPerformance
                        FROM lq_kd_jksyj jksyj
                        INNER JOIN lq_kd_pxmx pxmx ON jksyj.F_kdpxid = pxmx.F_Id AND pxmx.F_IsEffective = 1
                        INNER JOIN lq_kd_kdjlb kd ON jksyj.glkdbh = CONVERT(kd.F_Id USING utf8mb4)
                        WHERE jksyj.yjsj IS NOT NULL
                            AND jksyj.jksyj IS NOT NULL
                            AND jksyj.jksyj != ''
                            AND jksyj.jksyj != '0'
                            AND jksyj.F_kdpxid IS NOT NULL
                            AND jksyj.F_kdpxid != ''
                            AND jksyj.F_IsEffective = 1
                            AND jksyj.yjsj >= @startDate
                            AND jksyj.yjsj <= @endDate
                            {innerWhereClause}
                        GROUP BY jksyj.jkszh
                    ) cat_stats ON order_stats.EmployeeId = cat_stats.EmployeeId
                    LEFT JOIN (
                        -- 退单业绩统计
                        SELECT
                            hytk_jksyj.jkszh AS EmployeeId,
                            SUM(CAST(hytk_jksyj.jksyj AS DECIMAL(18,2))) AS RefundPerformance,
                            COUNT(*) AS RefundCount
                        FROM lq_hytk_jksyj hytk_jksyj
                        INNER JOIN lq_hytk_hytk hytk ON hytk_jksyj.gltkbh = hytk.F_Id
                        WHERE hytk_jksyj.jksyj IS NOT NULL
                            AND hytk_jksyj.jksyj != ''
                            AND hytk_jksyj.jksyj != '0'
                            AND hytk_jksyj.F_IsEffective = 1
                            AND hytk.F_IsEffective = 1
                            AND hytk_jksyj.tksj >= @startDate
                            AND hytk_jksyj.tksj <= @endDate
                        GROUP BY hytk_jksyj.jkszh
                    ) refund_stats ON order_stats.EmployeeId = refund_stats.EmployeeId";
                // 岗位筛选（需要在最外层，因为岗位是通过CASE计算的）
                var finalWhereConditions = new List<string>();
                if (!string.IsNullOrEmpty(input.Position))
                {
                    finalWhereConditions.Add("order_stats.Position = @Position");
                    parameters.Add("@Position", input.Position);
                }

                var finalWhereClause = finalWhereConditions.Any() ? " WHERE " + string.Join(" AND ", finalWhereConditions) : "";
                var finalSql = $@"SELECT * FROM ({sql}) AS order_stats{finalWhereClause} ORDER BY order_stats.TotalPerformance DESC";

                // 查询总数
                var countSql = $"SELECT COUNT(*) FROM ({finalSql}) AS total_count";
                var totalCount = await _db.Ado.GetIntAsync(countSql, parameters);

                // 分页查询（兼容前端传 pageIndex 或 currentPage）
                var pageIndex = input.pageIndex > 0 ? input.pageIndex : (input.currentPage > 0 ? input.currentPage : 1);
                var pageSize = input.pageSize > 0 ? input.pageSize : 20;
                var offset = (pageIndex - 1) * pageSize;
                var pagedSql = $"{finalSql} LIMIT {pageSize} OFFSET {offset}";

                _logger.LogInformation($"执行个人业绩统计实时查询SQL - 月份: {statisticsMonth}, 页码: {pageIndex}, 每页: {pageSize}");

                var statisticsData = await _db.Ado.SqlQueryAsync<dynamic>(pagedSql, parameters);

                // 映射到输出DTO
                var outputList = statisticsData.Select(stats => new LqStatisticsPersonalPerformanceListOutput
                {
                    Id = YitIdHelper.NextId().ToString(), // 实时查询没有ID，生成临时ID
                    StatisticsMonth = statisticsMonth,
                    StoreId = stats.StoreId?.ToString() ?? "",
                    StoreName = stats.StoreName?.ToString() ?? "",
                    GoldTriangleId = stats.GoldTriangleId?.ToString() ?? "",
                    GoldTriangleName = stats.GoldTriangleName?.ToString() ?? "",
                    Position = stats.Position?.ToString() ?? "",
                    EmployeeId = stats.EmployeeId?.ToString() ?? "",
                    EmployeeName = stats.EmployeeName?.ToString() ?? "",
                    TotalPerformance = Convert.ToDecimal(stats.TotalPerformance ?? 0) - Convert.ToDecimal(stats.RefundPerformance ?? 0),
                    BasePerformance = Convert.ToDecimal(stats.BasePerformance ?? 0),
                    CooperationPerformance = Convert.ToDecimal(stats.CooperationPerformance ?? 0),
                    LifeBeautyPerformance = Convert.ToDecimal(stats.LifeBeautyPerformance ?? 0),
                    MedicalBeautyPerformance = Convert.ToDecimal(stats.MedicalBeautyPerformance ?? 0),
                    TechBeautyPerformance = Convert.ToDecimal(stats.TechBeautyPerformance ?? 0),
                    CooperationCategoryPerformance = Convert.ToDecimal(stats.CooperationCategoryPerformance ?? 0),
                    OtherPerformance = Convert.ToDecimal(stats.OtherPerformance ?? 0),
                    ProductPerformance = Convert.ToDecimal(stats.ProductPerformance ?? 0),
                    RefundPerformance = Convert.ToDecimal(stats.RefundPerformance ?? 0),
                    RefundCount = Convert.ToInt32(stats.RefundCount ?? 0),
                    ActualPerformance = Convert.ToDecimal(stats.TotalPerformance ?? 0) - Convert.ToDecimal(stats.RefundPerformance ?? 0),
                    OrderCount = Convert.ToInt32(stats.OrderCount ?? 0),
                    FirstOrderCount = Convert.ToInt32(stats.FirstOrderCount ?? 0),
                    UpgradeOrderCount = Convert.ToInt32(stats.UpgradeOrderCount ?? 0),
                    FirstOrderPerformance = Convert.ToDecimal(stats.FirstOrderPerformance ?? 0),
                    UpgradeOrderPerformance = Convert.ToDecimal(stats.UpgradeOrderPerformance ?? 0),
                    LastOrderDate = stats.LastOrderDate as DateTime?,
                    FirstOrderDate = stats.FirstOrderDate as DateTime?,
                    CreateTime = DateTime.Now
                }).ToList();

                return new
                {
                    list = outputList,
                    pagination = new
                    {
                        pageIndex = pageIndex,
                        pageSize = pageSize,
                        total = totalCount
                    }
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询个人业绩统计数据列表失败");
                throw NCCException.Oh($"查询个人业绩统计数据列表失败: {ex.Message}");
            }
        }

        #endregion

        #region 科技部开单业绩统计

        /// <summary>
        /// 【3】保存科技部开单业绩统计数据
        /// </summary>
        /// <remarks>
        /// 根据指定月份统计科技部老师的开单业绩数据，只统计开卡业绩，不包含耗卡和退卡业绩
        /// 
        /// 示例请求：
        /// ```json
        /// POST /api/Extend/LqStatistics/save-tech-performance-statistics
        /// {
        ///   "statisticsMonth": "202401"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - statisticsMonth: 统计月份，格式为YYYYMM
        /// </remarks>
        /// <param name="statisticsMonth">统计月份(YYYYMM格式)</param>
        /// <returns>保存结果</returns>
        /// <response code="200">成功保存统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("save-tech-performance-stats")]
        public async Task<dynamic> SaveTechPerformanceStatistics([FromBody] SalaryStatisticsInput input)
        {
            if (input == null || string.IsNullOrEmpty(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
            {
                throw NCCException.Oh("统计月份格式错误，请使用YYYYMM格式");
            }

            var statisticsMonth = input.StatisticsMonth;

            try
            {
                // 使用数据库聚合方式，只统计开单业绩（开卡流水）
                var sql = @"
                    SELECT
                        k.kjbls AS TeacherId,
                        k.kjblsxm AS TeacherName,
                        k.kjblszh AS TeacherAccount,
                        NULL AS StoreId,
                        NULL AS StoreName,
                        COUNT(DISTINCT k.F_kdpxid) AS OrderCount,
                        SUM(CAST(COALESCE(k.kjblsyj, 0) AS DECIMAL(18,2))) AS TotalPerformance,
                        SUM(CAST(COALESCE(k.F_LaborCost, 0) AS DECIMAL(18,2))) AS LaborCost,
                        SUM(CAST(COALESCE(pm.F_ProjectNumber, 0) AS DECIMAL(18,2))) AS ProjectCount,
                        MAX(k.yjsj) AS LastOrderDate,
                        MIN(k.yjsj) AS FirstOrderDate
                    FROM lq_kd_kjbsyj k
                    LEFT JOIN lq_kd_pxmx pm ON k.F_kdpxid = pm.F_Id AND pm.F_IsEffective = 1
                    WHERE k.kjbls IS NOT NULL
                        AND k.kjblsxm IS NOT NULL
                        AND k.yjsj IS NOT NULL
                        AND k.kjblsyj IS NOT NULL
                        AND k.kjblsyj != ''
                        AND k.kjblsyj != '0'
                        AND k.F_IsEffective = 1
                        AND YEAR(k.yjsj) = @year
                        AND MONTH(k.yjsj) = @month
                    GROUP BY
                        k.kjbls,
                        k.kjblsxm,
                        k.kjblszh
                    ORDER BY TotalPerformance DESC";
                // 解析统计月份
                var year = int.Parse(statisticsMonth.Substring(0, 4));
                var month = int.Parse(statisticsMonth.Substring(4, 2));

                // 执行SQL查询
                var statisticsData = await _db.Ado.SqlQueryAsync<dynamic>(sql, new { year, month });

                _logger.LogInformation($"SQL查询结果数量: {statisticsData?.Count ?? 0}");
                if (statisticsData?.Any() == true)
                {
                    var firstData = statisticsData.First();
                    _logger.LogInformation($"第一条数据: TeacherId={firstData.TeacherId}, TeacherName={firstData.TeacherName}, TotalPerformance={firstData.TotalPerformance}");
                }

                if (!statisticsData.Any())
                {
                    return new
                    {
                        Success = true,
                        Message = $"未找到 {statisticsMonth} 月份的科技部开单业绩统计数据",
                        SavedCount = 0
                    };
                }

                // 转换为实体对象
                var entities = new List<LqStatisticsTechPerformanceEntity>();
                foreach (var data in statisticsData)
                {
                    try
                    {
                        var entity = new LqStatisticsTechPerformanceEntity
                        {
                            Id = YitIdHelper.NextId().ToString(),
                            StatisticsMonth = statisticsMonth,
                            TeacherId = data.TeacherId?.ToString() ?? "",
                            TeacherName = data.TeacherName?.ToString() ?? "",
                            TeacherAccount = data.TeacherAccount?.ToString() ?? "",
                            StoreId = data.StoreId?.ToString() ?? "",
                            StoreName = data.StoreName?.ToString() ?? "",
                            OrderCount = Convert.ToInt32(data.OrderCount ?? 0),
                            TotalPerformance = Convert.ToDecimal(data.TotalPerformance ?? 0),
                            LaborCost = Convert.ToDecimal(data.LaborCost ?? 0),
                            ProjectCount = Convert.ToDecimal(data.ProjectCount ?? 0),
                            LastOrderDate = data.LastOrderDate as DateTime?,
                            FirstOrderDate = data.FirstOrderDate as DateTime?,
                            CreateTime = DateTime.Now
                        };
                        entities.Add(entity);
                        _logger.LogInformation($"成功转换实体: TeacherId={entity.TeacherId}, TotalPerformance={entity.TotalPerformance}");
                    }
                    catch (Exception ex)
                    {
                        _logger.LogError($"转换实体失败: TeacherId={data.TeacherId}, Error={ex.Message}");
                    }
                }

                _logger.LogInformation($"准备插入 {entities.Count} 条实体数据");

                // 使用事务确保数据一致性
                var result = await _db.Ado.UseTranAsync(async () =>
                {
                    try
                    {
                        // 先删除该月份的历史数据
                        var deleteResult = await _db.Deleteable<LqStatisticsTechPerformanceEntity>()
                            .Where(x => x.StatisticsMonth == statisticsMonth)
                            .ExecuteCommandAsync();
                        _logger.LogInformation($"删除历史数据结果: {deleteResult}");

                        // 批量插入新数据
                        var insertResult = await _db.Insertable(entities).ExecuteCommandAsync();
                        _logger.LogInformation($"插入新数据结果: {insertResult}");
                        return insertResult;
                    }
                    catch (Exception ex)
                    {
                        _logger.LogError($"事务执行失败: {ex.Message}");
                        throw;
                    }
                });

                _logger.LogInformation($"事务执行结果: IsSuccess={result.IsSuccess}, Data={result.Data}, ErrorMessage={result.ErrorMessage}");
                var savedCount = result.IsSuccess ? result.Data : 0;
                _logger.LogInformation($"成功保存科技部开单业绩统计数据 - 月份: {statisticsMonth}, 记录数: {savedCount}");

                return new
                {
                    Success = true,
                    Message = $"成功保存 {savedCount} 条科技部开单业绩统计数据",
                    SavedCount = savedCount,
                    StatisticsMonth = statisticsMonth
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"保存科技部开单业绩统计数据失败 - 月份: {statisticsMonth}");
                throw NCCException.Oh($"保存科技部开单业绩统计数据失败: {ex.Message}");
            }
        }

        /// <summary>
        /// 获取科技部开单业绩统计数据
        /// </summary>
        /// <remarks>
        /// 分页查询科技部开单业绩统计数据
        /// 
        /// 示例请求：
        /// ```json
        /// GET /api/Extend/LqStatistics/get-tech-performance-statistics?PageIndex=1&amp;PageSize=10&amp;StatisticsMonth=202401
        /// ```
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>分页数据</returns>
        /// <response code="200">查询成功</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器内部错误</response>
        [HttpGet("get-tech-performance-statistics")]
        public async Task<dynamic> GetTechPerformanceStatistics([FromQuery] LqStatisticsTechPerformanceListQueryInput input)
        {
            try
            {
                var query = _db.Queryable<LqStatisticsTechPerformanceEntity>();

                // 添加查询条件
                query = query.WhereIF(!string.IsNullOrEmpty(input.StatisticsMonth), x => x.StatisticsMonth == input.StatisticsMonth);
                query = query.WhereIF(!string.IsNullOrEmpty(input.TeacherId), x => x.TeacherId == input.TeacherId);
                query = query.WhereIF(!string.IsNullOrEmpty(input.TeacherName), x => x.TeacherName.Contains(input.TeacherName));
                query = query.WhereIF(!string.IsNullOrEmpty(input.StoreId), x => x.StoreId == input.StoreId);
                query = query.WhereIF(!string.IsNullOrEmpty(input.StoreName), x => x.StoreName.Contains(input.StoreName));

                // 按业绩降序排序
                query = query.OrderBy(x => x.TotalPerformance, OrderByType.Desc);

                // 分页查询
                var result = await query.ToPagedListAsync(input.currentPage, input.pageSize);

                return new
                {
                    Success = true,
                    Data = new
                    {
                        Records = result.list.Select(x => new LqStatisticsTechPerformanceListOutput
                        {
                            Id = x.Id,
                            StatisticsMonth = x.StatisticsMonth,
                            TeacherId = x.TeacherId,
                            TeacherName = x.TeacherName,
                            TeacherAccount = x.TeacherAccount,
                            StoreId = x.StoreId,
                            StoreName = x.StoreName,
                            OrderCount = x.OrderCount,
                            TotalPerformance = x.TotalPerformance,
                            LaborCost = x.LaborCost,
                            ProjectCount = x.ProjectCount,
                            LastOrderDate = x.LastOrderDate,
                            FirstOrderDate = x.FirstOrderDate,
                            CreateTime = x.CreateTime
                        }).ToList(),
                        Total = result.pagination.Total,
                        PageIndex = input.currentPage,
                        PageSize = input.pageSize
                    }
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询科技部开单业绩统计数据失败");
                throw NCCException.Oh($"查询科技部开单业绩统计数据失败: {ex.Message}");
            }
        }

        #endregion

        #region 门店耗卡业绩统计

        /// <summary>
        /// 【4】保存门店耗卡业绩统计数据
        /// </summary>
        /// <remarks>
        /// 统计指定月份各门店的耗卡业绩数据，包括消耗业绩、消耗数量、手工费
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "statisticsMonth": "202501"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - statisticsMonth: 统计月份，格式为YYYYMM
        /// </remarks>
        /// <param name="input">统计月份输入参数</param>
        /// <returns>保存结果</returns>
        /// <response code="200">成功保存统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("save-store-consume-performance-stats")]
        public async Task<dynamic> SaveStoreConsumePerformanceStatistics([FromBody] SalaryStatisticsInput input)
        {
            if (input == null || string.IsNullOrEmpty(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
            {
                throw NCCException.Oh("统计月份格式错误，请使用YYYYMM格式");
            }

            var statisticsMonth = input.StatisticsMonth;

            try
            {
                _logger.LogInformation($"开始统计门店耗卡业绩数据，月份：{statisticsMonth}");

                // 使用数据库聚合方式，直接在数据库中完成所有统计计算
                var sql = @"
                    SELECT
                        hyhk.md AS StoreId,
                        mdxx.mdbm AS StoreCode,
                        hyhk.mdmc AS StoreName,
                        @statisticsMonth AS StatisticsMonth,
                        COALESCE(SUM(pxmx.F_TotalPrice), 0) AS ConsumePerformance,
                        COALESCE(SUM(pxmx.F_ProjectNumber), 0) AS ConsumeQuantity,
                        COALESCE(SUM(hyhk.sgfy), 0) AS ManualFee
                    FROM lq_xh_hyhk hyhk
                    LEFT JOIN lq_xh_pxmx pxmx ON hyhk.F_Id = pxmx.F_ConsumeInfoId AND pxmx.F_IsEffective = 1
                    LEFT JOIN lq_mdxx mdxx ON hyhk.md = mdxx.F_Id
                    WHERE hyhk.F_IsEffective = 1
                        AND DATE_FORMAT(hyhk.hksj, '%Y%m') = @statisticsMonth
                    GROUP BY hyhk.md, hyhk.mdmc, mdxx.mdbm
                    HAVING ConsumePerformance > 0 OR ConsumeQuantity > 0 OR ManualFee > 0
                    ORDER BY ConsumePerformance DESC";
                // 执行SQL查询
                var statisticsData = await _db.Ado.SqlQueryAsync<dynamic>(sql, new { statisticsMonth });

                if (!statisticsData.Any())
                {
                    return new
                    {
                        Success = true,
                        Message = $"未找到 {statisticsMonth} 月份的门店耗卡业绩统计数据",
                        SavedCount = 0
                    };
                }

                // 转换为实体对象
                var entities = statisticsData.Select(data => new LqStatisticsStoreConsumePerformanceEntity
                {
                    Id = YitIdHelper.NextId().ToString(),
                    StoreId = data.StoreId?.ToString() ?? "",
                    StoreCode = data.StoreCode?.ToString() ?? "",
                    StoreName = data.StoreName?.ToString() ?? "",
                    StatisticsMonth = data.StatisticsMonth?.ToString() ?? statisticsMonth,
                    ConsumePerformance = Convert.ToDecimal(data.ConsumePerformance ?? 0),
                    ConsumeQuantity = Convert.ToDecimal(data.ConsumeQuantity ?? 0),
                    ManualFee = Convert.ToDecimal(data.ManualFee ?? 0),
                    CreateTime = DateTime.Now,
                    UpdateTime = DateTime.Now
                }).ToList();

                // 使用事务确保数据一致性
                var result = await _db.Ado.UseTranAsync(async () =>
                {
                    // 先删除该月份的历史数据
                    await _db.Deleteable<LqStatisticsStoreConsumePerformanceEntity>()
                        .Where(x => x.StatisticsMonth == statisticsMonth)
                        .ExecuteCommandAsync();

                    // 批量插入新数据
                    return await _db.Insertable(entities).ExecuteCommandAsync();
                });

                var savedCount = result.IsSuccess ? result.Data : 0;
                _logger.LogInformation($"成功保存门店耗卡业绩统计数据 - 月份: {statisticsMonth}, 记录数: {savedCount}");

                return new
                {
                    Success = true,
                    Message = $"成功保存 {savedCount} 条门店耗卡业绩统计数据",
                    SavedCount = savedCount,
                    StatisticsMonth = statisticsMonth
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"保存门店耗卡业绩统计数据失败 - 月份: {statisticsMonth}");
                throw NCCException.Oh($"保存门店耗卡业绩统计数据失败: {ex.Message}");
            }
        }

        /// <summary>
        /// 查询门店耗卡业绩统计数据
        /// </summary>
        /// <remarks>
        /// 分页查询门店耗卡业绩统计数据，支持按门店、月份等条件筛选
        /// 
        /// 示例请求：
        /// ```
        /// GET /api/Extend/LqStatistics/get-store-consume-performance-stats?currentPage=1&amp;pageSize=10&amp;statisticsMonth=202501
        /// ```
        /// 
        /// 参数说明：
        /// - currentPage: 当前页码
        /// - pageSize: 每页大小
        /// - statisticsMonth: 统计月份筛选
        /// - storeId: 门店ID筛选
        /// - storeName: 门店名称筛选
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>分页的门店耗卡业绩统计数据</returns>
        /// <response code="200">成功返回统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpGet("get-store-consume-performance-stats")]
        public async Task<SqlSugarPagedList<LqStatisticsStoreConsumePerformanceEntity>> GetStoreConsumePerformanceStatistics([FromQuery] LqStatisticsStoreConsumePerformanceListQueryInput input)
        {
            try
            {
                var query = _db.Queryable<LqStatisticsStoreConsumePerformanceEntity>()
                    .WhereIF(!string.IsNullOrEmpty(input.storeId), x => x.StoreId.Contains(input.storeId))
                    .WhereIF(!string.IsNullOrEmpty(input.storeCode), x => x.StoreCode.Contains(input.storeCode))
                    .WhereIF(!string.IsNullOrEmpty(input.storeName), x => x.StoreName.Contains(input.storeName))
                    .WhereIF(!string.IsNullOrEmpty(input.statisticsMonth), x => x.StatisticsMonth == input.statisticsMonth)
                    .WhereIF(!string.IsNullOrEmpty(input.startMonth), x => string.Compare(x.StatisticsMonth, input.startMonth) >= 0)
                    .WhereIF(!string.IsNullOrEmpty(input.endMonth), x => string.Compare(x.StatisticsMonth, input.endMonth) <= 0)
                    .OrderBy(x => x.StatisticsMonth, OrderByType.Desc)
                    .OrderBy(x => x.StoreName, OrderByType.Asc);

                var result = await query.ToPagedListAsync(input.currentPage, input.pageSize);
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询门店耗卡业绩统计数据失败");
                throw NCCException.Oh($"查询门店耗卡业绩统计数据失败: {ex.Message}");
            }
        }

        #endregion

        #region 个人消耗业绩统计

        /// <summary>
        /// 【5】保存个人消耗业绩统计数据
        /// </summary>
        /// <remarks>
        /// 统计健康师和科技部老师的消耗业绩数据
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "statisticsMonth": "202501"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - statisticsMonth: 统计月份(YYYYMM格式)
        /// </remarks>
        /// <param name="input">统计输入参数</param>
        /// <returns>保存结果</returns>
        /// <response code="200">成功保存个人消耗业绩统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("save-department-consume-performance-stats")]
        public async Task<dynamic> SaveDepartmentConsumePerformanceStatistics([FromBody] SalaryStatisticsInput input)
        {
            try
            {
                if (input == null || string.IsNullOrEmpty(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
                {
                    throw NCCException.Oh("统计月份格式错误，请使用YYYYMM格式");
                }

                var statisticsMonth = input.StatisticsMonth;
                var allStatisticsData = new List<LqStatisticsDepartmentConsumePerformanceEntity>();

                // 1. 统计健康师消耗业绩
                var healthCoachSql = @"
                    SELECT
                        '健康师' as F_DepartmentType,
                        jksyj.jkszh as F_UserId,
                        jksyj.jkszh as F_UserCode,
                        jksyj.jksxm as F_UserName,
                        hyhk.md as F_StoreId,
                        md.mdbm as F_StoreCode,
                        md.dm as F_StoreName,
                        @statisticsMonth as F_StatisticsMonth,
                        COALESCE(SUM(jksyj.jksyj), 0) as F_ConsumePerformance,
                        COALESCE(SUM(jksyj.F_kdpxNumber), 0) as F_ConsumeQuantity,
                        COALESCE(SUM(jksyj.F_LaborCost), 0) as F_ManualFee,
                        COALESCE(headcount_stats.F_HeadCount, 0) as F_HeadCount,
                        COALESCE(personcount_stats.F_PersonCount, 0) as F_PersonCount
                    FROM lq_xh_jksyj jksyj
                    INNER JOIN lq_xh_hyhk hyhk ON jksyj.glkdbh = hyhk.F_Id AND hyhk.F_IsEffective = 1
                    LEFT JOIN lq_mdxx md ON hyhk.md = md.F_Id
                    LEFT JOIN (
                        -- 人头统计：月度去重客户数
                        SELECT
                            jksyj.jkszh,
                            hyhk.md,
                            COUNT(DISTINCT hyhk.hy) as F_HeadCount
                        FROM lq_xh_jksyj jksyj
                        INNER JOIN lq_xh_hyhk hyhk ON jksyj.glkdbh = hyhk.F_Id AND hyhk.F_IsEffective = 1
                        WHERE jksyj.F_IsEffective = 1
                            AND DATE_FORMAT(hyhk.hksj, '%Y%m') = @statisticsMonth
                        GROUP BY jksyj.jkszh, hyhk.md
                    ) headcount_stats ON jksyj.jkszh = headcount_stats.jkszh AND hyhk.md = headcount_stats.md
                    LEFT JOIN (
                        -- 人次统计：日度去重客户数（每天同一个客户只算一次）
                        SELECT
                            jksyj.jkszh,
                            hyhk.md,
                            COUNT(DISTINCT CONCAT(hyhk.hy, '-', DATE(hyhk.hksj))) as F_PersonCount
                        FROM lq_xh_jksyj jksyj
                        INNER JOIN lq_xh_hyhk hyhk ON jksyj.glkdbh = hyhk.F_Id AND hyhk.F_IsEffective = 1
                        WHERE jksyj.F_IsEffective = 1
                            AND DATE_FORMAT(hyhk.hksj, '%Y%m') = @statisticsMonth
                        GROUP BY jksyj.jkszh, hyhk.md
                    ) personcount_stats ON jksyj.jkszh = personcount_stats.jkszh AND hyhk.md = personcount_stats.md
                    WHERE jksyj.F_IsEffective = 1
                        AND DATE_FORMAT(hyhk.hksj, '%Y%m') = @statisticsMonth
                    GROUP BY jksyj.jks, jksyj.jksxm, hyhk.md, md.mdbm, md.dm, headcount_stats.F_HeadCount, personcount_stats.F_PersonCount";
                var healthCoachData = await _db.Ado.SqlQueryAsync<dynamic>(healthCoachSql, new { statisticsMonth });
                foreach (var item in healthCoachData)
                {
                    // 判断门店是否为新店（通过新店保护时间表查询）
                    var isNewStore = "否";
                    if (!string.IsNullOrEmpty(item.F_StoreId?.ToString()))
                    {
                        var statisticsDate = DateTime.ParseExact(statisticsMonth + "01", "yyyyMMdd", null);
                        var monthStart = statisticsDate.AddDays(-statisticsDate.Day + 1);
                        var monthEnd = statisticsDate.AddMonths(1).AddDays(-1);

                        // 查询门店在统计月份是否处于新店保护期内
                        var storeId = item.F_StoreId?.ToString();
                        var newStoreProtectionSql = @"
                            SELECT COUNT(1) as Count
                            FROM lq_md_xdbhsj
                            WHERE mdid = @storeId
                                AND sfqy = 1
                                AND bhkssj <= @monthEnd
                                AND bhjssj >= @monthStart";
                        var protectionResult = await _db.Ado.SqlQueryAsync<dynamic>(newStoreProtectionSql, new
                        {
                            storeId,
                            monthStart = monthStart.ToString("yyyy-MM-dd"),
                            monthEnd = monthEnd.ToString("yyyy-MM-dd")
                        });
                        var hasProtection = protectionResult.FirstOrDefault()?.Count > 0;

                        if (hasProtection)
                        {
                            isNewStore = "是";
                        }
                    }

                    allStatisticsData.Add(new LqStatisticsDepartmentConsumePerformanceEntity
                    {
                        Id = YitIdHelper.NextId().ToString(),
                        DepartmentType = item.F_DepartmentType?.ToString(),
                        UserId = item.F_UserId?.ToString(),
                        UserCode = item.F_UserCode?.ToString(),
                        UserName = item.F_UserName?.ToString(),
                        StoreId = item.F_StoreId?.ToString(),
                        StoreCode = item.F_StoreCode?.ToString(),
                        StoreName = item.F_StoreName?.ToString(),
                        StatisticsMonth = statisticsMonth,
                        ConsumePerformance = Convert.ToDecimal(item.F_ConsumePerformance ?? 0),
                        ConsumeQuantity = Convert.ToDecimal(item.F_ConsumeQuantity ?? 0),
                        ManualFee = Convert.ToDecimal(item.F_ManualFee ?? 0),
                        IsNewStore = isNewStore,
                        HeadCount = Convert.ToDecimal(item.F_HeadCount ?? 0),
                        PersonCount = Convert.ToDecimal(item.F_PersonCount ?? 0),
                        CreateTime = DateTime.Now
                    });
                }

                // 2. 统计科技部老师消耗业绩
                var techTeacherSql = @"
                    SELECT
                        '科技部老师' as F_DepartmentType,
                        kjbsyj.kjblszh as F_UserId,
                        kjbsyj.kjblszh as F_UserCode,
                        kjbsyj.kjblsxm as F_UserName,
                        hyhk.md as F_StoreId,
                        md.mdbm as F_StoreCode,
                        md.dm as F_StoreName,
                        @statisticsMonth as F_StatisticsMonth,
                        COALESCE(SUM(kjbsyj.kjblsyj), 0) as F_ConsumePerformance,
                        COALESCE(SUM(kjbsyj.F_hdpxNumber), 0) as F_ConsumeQuantity,
                        COALESCE(SUM(kjbsyj.F_LaborCost), 0) as F_ManualFee,
                        0 as F_HeadCount,
                        0 as F_PersonCount
                    FROM lq_xh_kjbsyj kjbsyj
                    INNER JOIN lq_xh_hyhk hyhk ON kjbsyj.glkdbh = hyhk.F_Id AND hyhk.F_IsEffective = 1
                    LEFT JOIN lq_mdxx md ON hyhk.md = md.F_Id
                    WHERE kjbsyj.F_IsEffective = 1
                        AND DATE_FORMAT(hyhk.hksj, '%Y%m') = @statisticsMonth
                    GROUP BY kjbsyj.kjblszh, kjbsyj.kjblsxm, hyhk.md, md.mdbm, md.dm";
                var techTeacherData = await _db.Ado.SqlQueryAsync<dynamic>(techTeacherSql, new { statisticsMonth });
                foreach (var item in techTeacherData)
                {
                    // 判断门店是否为新店（通过新店保护时间表查询）
                    var isNewStore = "否";
                    if (!string.IsNullOrEmpty(item.F_StoreId?.ToString()))
                    {
                        var statisticsDate = DateTime.ParseExact(statisticsMonth + "01", "yyyyMMdd", null);
                        var monthStart = statisticsDate.AddDays(-statisticsDate.Day + 1);
                        var monthEnd = statisticsDate.AddMonths(1).AddDays(-1);

                        // 查询门店在统计月份是否处于新店保护期内
                        var storeId = item.F_StoreId?.ToString();
                        var newStoreProtectionSql = @"
                            SELECT COUNT(1) as Count
                            FROM lq_md_xdbhsj
                            WHERE mdid = @storeId
                                AND sfqy = 1
                                AND bhkssj <= @monthEnd
                                AND bhjssj >= @monthStart";
                        var protectionResult = await _db.Ado.SqlQueryAsync<dynamic>(newStoreProtectionSql, new
                        {
                            storeId,
                            monthStart = monthStart.ToString("yyyy-MM-dd"),
                            monthEnd = monthEnd.ToString("yyyy-MM-dd")
                        });
                        var hasProtection = protectionResult.FirstOrDefault()?.Count > 0;

                        if (hasProtection)
                        {
                            isNewStore = "是";
                        }
                    }

                    allStatisticsData.Add(new LqStatisticsDepartmentConsumePerformanceEntity
                    {
                        Id = YitIdHelper.NextId().ToString(),
                        DepartmentType = item.F_DepartmentType?.ToString(),
                        UserId = item.F_UserId?.ToString(),
                        UserCode = item.F_UserCode?.ToString(),
                        UserName = item.F_UserName?.ToString(),
                        StoreId = item.F_StoreId?.ToString(),
                        StoreCode = item.F_StoreCode?.ToString(),
                        StoreName = item.F_StoreName?.ToString(),
                        StatisticsMonth = statisticsMonth,
                        ConsumePerformance = Convert.ToDecimal(item.F_ConsumePerformance ?? 0),
                        ConsumeQuantity = Convert.ToDecimal(item.F_ConsumeQuantity ?? 0),
                        ManualFee = Convert.ToDecimal(item.F_ManualFee ?? 0),
                        IsNewStore = isNewStore,
                        HeadCount = Convert.ToDecimal(item.F_HeadCount ?? 0),
                        PersonCount = Convert.ToDecimal(item.F_PersonCount ?? 0),
                        CreateTime = DateTime.Now
                    });
                }

                // 3. 使用事务保存数据
                var result = await _db.Ado.UseTranAsync(async () =>
                {
                    // 删除当月已存在的数据
                    await _db.Deleteable<LqStatisticsDepartmentConsumePerformanceEntity>()
                        .Where(x => x.StatisticsMonth == statisticsMonth)
                        .ExecuteCommandAsync();

                    // 批量插入新数据
                    if (allStatisticsData.Any())
                    {
                        await _db.Insertable(allStatisticsData).ExecuteCommandAsync();
                    }

                    return allStatisticsData.Count;
                });

                var savedCount = result.IsSuccess ? result.Data : 0;

                return new
                {
                    Success = true,
                    Message = $"成功保存 {savedCount} 条个人消耗业绩统计数据",
                    SavedCount = savedCount,
                    StatisticsMonth = statisticsMonth
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "保存个人消耗业绩统计数据失败");
                throw NCCException.Oh($"保存个人消耗业绩统计数据失败: {ex.Message}");
            }
        }

        /// <summary>
        /// 查询个人消耗业绩统计数据
        /// </summary>
        /// <remarks>
        /// 支持按部门类型、用户、门店、月份等条件查询个人消耗业绩统计数据
        /// 
        /// 示例请求：
        /// ```
        /// GET /api/Extend/LqStatistics/get-department-consume-performance-stats?departmentType=健康师&amp;statisticsMonth=202501&amp;currentPage=1&amp;pageSize=10
        /// ```
        /// 
        /// 参数说明：
        /// - departmentType: 部门类型(健康师, 科技部老师)
        /// - userId: 用户ID
        /// - userCode: 用户账号
        /// - userName: 用户姓名
        /// - storeId: 门店ID
        /// - storeCode: 门店编码
        /// - storeName: 门店名称
        /// - statisticsMonth: 统计月份(YYYYMM格式)
        /// - startMonth: 开始月份(YYYYMM格式)
        /// - endMonth: 结束月份(YYYYMM格式)
        /// - currentPage: 当前页码
        /// - pageSize: 每页大小
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>分页查询结果</returns>
        /// <response code="200">查询成功</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpGet("get-department-consume-performance-stats")]
        public async Task<SqlSugarPagedList<LqStatisticsDepartmentConsumePerformanceEntity>> GetDepartmentConsumePerformanceStatistics([FromQuery] LqStatisticsDepartmentConsumePerformanceListQueryInput input)
        {
            try
            {
                var query = _db.Queryable<LqStatisticsDepartmentConsumePerformanceEntity>()
                    .WhereIF(!string.IsNullOrEmpty(input.DepartmentType), x => x.DepartmentType.Contains(input.DepartmentType))
                    .WhereIF(!string.IsNullOrEmpty(input.UserId), x => x.UserId.Contains(input.UserId))
                    .WhereIF(!string.IsNullOrEmpty(input.UserCode), x => x.UserCode.Contains(input.UserCode))
                    .WhereIF(!string.IsNullOrEmpty(input.UserName), x => x.UserName.Contains(input.UserName))
                    .WhereIF(!string.IsNullOrEmpty(input.StoreId), x => x.StoreId.Contains(input.StoreId))
                    .WhereIF(!string.IsNullOrEmpty(input.StoreCode), x => x.StoreCode.Contains(input.StoreCode))
                    .WhereIF(!string.IsNullOrEmpty(input.StoreName), x => x.StoreName.Contains(input.StoreName))
                    .WhereIF(!string.IsNullOrEmpty(input.StatisticsMonth), x => x.StatisticsMonth == input.StatisticsMonth)
                    .WhereIF(!string.IsNullOrEmpty(input.StartMonth), x => string.Compare(x.StatisticsMonth, input.StartMonth) >= 0)
                    .WhereIF(!string.IsNullOrEmpty(input.EndMonth), x => string.Compare(x.StatisticsMonth, input.EndMonth) <= 0)
                    .OrderBy(x => x.StatisticsMonth, OrderByType.Desc)
                    .OrderBy(x => x.DepartmentType, OrderByType.Asc)
                    .OrderBy(x => x.UserName, OrderByType.Asc);

                var result = await query.ToPagedListAsync(input.currentPage, input.pageSize);
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询个人消耗业绩统计数据失败");
                throw NCCException.Oh($"查询个人消耗业绩统计数据失败: {ex.Message}");
            }
        }

        #endregion

        #region 门店总业绩统计

        /// <summary>
        /// 保存门店总业绩统计数据
        /// </summary>
        /// <remarks>
        /// 统计门店的总业绩、欠款金额、总单业绩、储扣总金额、品项数量、首开单数量、升单数量、退款业绩、退款次数
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "statisticsMonth": "202501"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - statisticsMonth: 统计月份，格式为YYYYMM
        /// 
        /// 统计指标：
        /// - 总业绩: 开单记录的总业绩金额
        /// - 欠款金额: 开单记录的欠款总额
        /// - 总单业绩: 开单记录的实付业绩
        /// - 储扣总金额: 储扣记录的金额总和
        /// - 品项数量: 开单品项的总数量
        /// - 首开单数量: 首次开单的数量
        /// - 升单数量: 升单的数量
        /// - 退款业绩: 退款记录的总金额
        /// - 退款次数: 退款记录的总次数
        /// </remarks>
        /// <param name="input">统计输入参数</param>
        /// <returns>保存结果</returns>
        /// <response code="200">成功保存门店总业绩统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("save-store-total-performance-stats")]
        public async Task<dynamic> SaveStoreTotalPerformanceStatistics([FromBody] SalaryStatisticsInput input)
        {
            try
            {
                if (input == null || string.IsNullOrEmpty(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
                {
                    throw NCCException.Oh("统计月份格式错误，请使用YYYYMM格式");
                }

                var statisticsMonth = input.StatisticsMonth;

                // 统计门店总业绩数据
                var storePerformanceSql = @"
                    SELECT
                        store_data.F_StoreId,
                        store_data.F_StoreName,
                        store_data.F_StatisticsMonth,
                        store_data.F_TotalPerformance,
                        store_data.F_DebtAmount,
                        store_data.F_TotalOrderPerformance,
                        store_data.F_StorageDeductionAmount,
                        COALESCE(item_data.F_ItemQuantity, 0) as F_ItemQuantity,
                        store_data.F_FirstOrderCount,
                        store_data.F_UpgradeOrderCount,
                        store_data.F_FirstOrderPerformance,
                        store_data.F_UpgradeOrderPerformance,
                        COALESCE(refund_data.F_RefundAmount, 0) as F_RefundAmount,
                        COALESCE(refund_data.F_RefundCount, 0) as F_RefundCount
                    FROM (
                        SELECT
                            kd.djmd as F_StoreId,
                            md.dm as F_StoreName,
                            @statisticsMonth as F_StatisticsMonth,
                            COALESCE(SUM(kd.zdyj), 0) as F_TotalPerformance,
                            COALESCE(SUM(kd.qk), 0) as F_DebtAmount,
                            COALESCE(SUM(kd.sfyj), 0) as F_TotalOrderPerformance,
                            COALESCE(SUM(kd.F_DeductAmount), 0) as F_StorageDeductionAmount,
                            COUNT(DISTINCT CASE WHEN kd.sfskdd = '是' THEN kd.F_Id END) as F_FirstOrderCount,
                            COUNT(DISTINCT CASE WHEN kd.sfskdd = '否' THEN kd.F_Id END) as F_UpgradeOrderCount,
                            SUM(CASE WHEN kd.sfskdd = '是' THEN COALESCE(kd.zdyj, 0) ELSE 0 END) as F_FirstOrderPerformance,
                            SUM(CASE WHEN kd.sfskdd = '否' THEN COALESCE(kd.zdyj, 0) ELSE 0 END) as F_UpgradeOrderPerformance
                        FROM lq_kd_kdjlb kd
                        LEFT JOIN lq_mdxx md ON CONVERT(kd.djmd USING utf8mb4) = md.F_Id
                        WHERE kd.F_IsEffective = 1
                            AND DATE_FORMAT(kd.kdrq, '%Y%m') = @statisticsMonth
                        GROUP BY kd.djmd, md.dm
                    ) store_data
                    LEFT JOIN (
                        SELECT
                            kd.djmd as F_StoreId,
                            COUNT(pxmx.F_ProjectNumber) as F_ItemQuantity
                        FROM lq_kd_kdjlb kd
                        LEFT JOIN lq_kd_pxmx pxmx ON CONVERT(kd.F_Id USING utf8mb4) = pxmx.glkdbh AND pxmx.F_IsEffective = 1
                        WHERE kd.F_IsEffective = 1
                            AND DATE_FORMAT(kd.kdrq, '%Y%m') = @statisticsMonth
                        GROUP BY kd.djmd
                    ) item_data ON store_data.F_StoreId = item_data.F_StoreId
                    LEFT JOIN (
                        SELECT
                            hytk.md as F_StoreId,
                            COALESCE(SUM(hytk.F_ActualRefundAmount), 0) as F_RefundAmount,
                            COUNT(DISTINCT hytk.F_Id) as F_RefundCount
                        FROM lq_hytk_hytk hytk
                        WHERE hytk.F_IsEffective = 1
                            AND DATE_FORMAT(hytk.tksj, '%Y%m') = @statisticsMonth
                        GROUP BY hytk.md
                    ) refund_data ON store_data.F_StoreId = refund_data.F_StoreId";
                var storePerformanceData = await _db.Ado.SqlQueryAsync<dynamic>(storePerformanceSql, new { statisticsMonth });

                var entities = storePerformanceData.Select(data => new LqStatisticsStoreTotalPerformanceEntity
                {
                    Id = YitIdHelper.NextId().ToString(),
                    StoreId = data.F_StoreId?.ToString() ?? "",
                    StoreName = data.F_StoreName?.ToString() ?? "",
                    StatisticsMonth = statisticsMonth,
                    TotalPerformance = Convert.ToDecimal(data.F_TotalPerformance ?? 0),
                    DebtAmount = Convert.ToDecimal(data.F_DebtAmount ?? 0),
                    TotalOrderPerformance = Convert.ToDecimal(data.F_TotalOrderPerformance ?? 0),
                    StorageDeductionAmount = Convert.ToDecimal(data.F_StorageDeductionAmount ?? 0),
                    ItemQuantity = Convert.ToInt32(data.F_ItemQuantity ?? 0),
                    FirstOrderCount = Convert.ToInt32(data.F_FirstOrderCount ?? 0),
                    UpgradeOrderCount = Convert.ToInt32(data.F_UpgradeOrderCount ?? 0),
                    FirstOrderPerformance = Convert.ToDecimal(data.F_FirstOrderPerformance ?? 0),
                    UpgradeOrderPerformance = Convert.ToDecimal(data.F_UpgradeOrderPerformance ?? 0),
                    RefundAmount = Convert.ToDecimal(data.F_RefundAmount ?? 0),
                    RefundCount = Convert.ToInt32(data.F_RefundCount ?? 0),
                    ActualPerformance = Convert.ToDecimal(data.F_TotalOrderPerformance ?? 0) - Convert.ToDecimal(data.F_RefundAmount ?? 0),
                    CreateTime = DateTime.Now
                }).ToList();

                // 使用事务确保数据一致性
                var result = await _db.Ado.UseTranAsync(async () =>
                {
                    // 先删除该月份的历史数据
                    await _db.Deleteable<LqStatisticsStoreTotalPerformanceEntity>()
                        .Where(x => x.StatisticsMonth == statisticsMonth)
                        .ExecuteCommandAsync();

                    // 批量插入新数据
                    if (entities.Any())
                    {
                        return await _db.Insertable(entities).ExecuteCommandAsync();
                    }
                    return 0;
                });

                var savedCount = result.IsSuccess ? result.Data : 0;
                _logger.LogInformation($"成功保存门店总业绩统计数据 - 月份: {statisticsMonth}, 记录数: {savedCount}");

                return new
                {
                    Success = true,
                    Message = $"成功保存 {savedCount} 条门店总业绩统计数据",
                    SavedCount = savedCount,
                    StatisticsMonth = statisticsMonth
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"保存门店总业绩统计数据失败 - 月份: {input?.StatisticsMonth}");
                throw NCCException.Oh($"保存门店总业绩统计数据失败: {ex.Message}");
            }
        }

        /// <summary>
        /// 获取门店总业绩统计列表
        /// </summary>
        /// <remarks>
        /// 分页查询门店总业绩统计数据
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "currentPage": 1,
        ///   "pageSize": 20,
        ///   "storeId": "门店ID",
        ///   "statisticsMonth": "202501"
        /// }
        /// ```
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>分页数据</returns>
        /// <response code="200">成功返回门店总业绩统计列表</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpGet("store-total-performance-list")]
        public async Task<dynamic> GetStoreTotalPerformanceList([FromQuery] LqStatisticsStoreTotalPerformanceListQueryInput input)
        {
            try
            {
                var sidx = input.sidx == null ? "createTime" : input.sidx;
                var data = await _db.Queryable<LqStatisticsStoreTotalPerformanceEntity>()
                    .WhereIF(!string.IsNullOrEmpty(input.storeId), p => p.StoreId.Contains(input.storeId))
                    .WhereIF(!string.IsNullOrEmpty(input.storeName), p => p.StoreName.Contains(input.storeName))
                    .WhereIF(!string.IsNullOrEmpty(input.statisticsMonth), p => p.StatisticsMonth.Equals(input.statisticsMonth))
                    .Select(it => new LqStatisticsStoreTotalPerformanceListOutput
                    {
                        id = it.Id,
                        storeId = it.StoreId,
                        storeName = it.StoreName,
                        statisticsMonth = it.StatisticsMonth,
                        totalPerformance = it.TotalPerformance,
                        debtAmount = it.DebtAmount,
                        totalOrderPerformance = it.TotalOrderPerformance,
                        storageDeductionAmount = it.StorageDeductionAmount,
                        itemQuantity = it.ItemQuantity,
                        firstOrderCount = it.FirstOrderCount,
                        upgradeOrderCount = it.UpgradeOrderCount,
                        firstOrderPerformance = it.FirstOrderPerformance,
                        upgradeOrderPerformance = it.UpgradeOrderPerformance,
                        refundAmount = it.RefundAmount,
                        refundCount = it.RefundCount,
                        actualPerformance = it.ActualPerformance,
                        createTime = it.CreateTime
                    })
                    .OrderBy(sidx + " " + input.sort)
                    .ToPagedListAsync(input.currentPage, input.pageSize);

                return PageResult<LqStatisticsStoreTotalPerformanceListOutput>.SqlSugarPageResult(data);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取门店总业绩统计列表失败");
                throw NCCException.Oh($"获取门店总业绩统计列表失败: {ex.Message}");
            }
        }

        #endregion

        #region 工资统计

        /// <summary>
        /// 保存工资统计数据
        /// </summary>
        /// <remarks>
        /// 初始化工资统计表，从其他统计表读取数据并计算工资
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "statisticsMonth": "202501"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - statisticsMonth: 统计月份，格式为YYYYMM
        /// </remarks>
        /// <param name="input">统计输入参数</param>
        /// <returns>保存结果</returns>
        /// <response code="200">成功保存工资统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("save-salary-statistics")]
        public async Task<dynamic> SaveSalaryStatistics([FromBody] SalaryStatisticsInput input)
        {
            try
            {
                if (input == null || string.IsNullOrEmpty(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
                {
                    throw NCCException.Oh("统计月份格式错误，请使用YYYYMM格式");
                }

                var statisticsMonth = input.StatisticsMonth;

                // 1. 先初始化所有员工到工资统计表
                await InitializeSalaryStatistics(statisticsMonth);

                // 2. 从其他统计表读取数据并更新工资统计表
                await UpdateSalaryStatisticsFromOtherTables(statisticsMonth);

                _logger.LogInformation($"成功保存工资统计数据 - 月份: {statisticsMonth}");

                return new
                {
                    Success = true,
                    Message = $"成功保存 {statisticsMonth} 月份工资统计数据",
                    StatisticsMonth = statisticsMonth
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"保存工资统计数据失败 - 月份: {input?.StatisticsMonth}");
                throw NCCException.Oh($"保存工资统计数据失败: {ex.Message}");
            }
        }

        /// <summary>
        /// 获取工资统计列表
        /// </summary>
        /// <remarks>
        /// 分页查询工资统计数据
        /// 
        /// 示例请求：
        /// ```json
        /// POST /api/Extend/LqStatistics/get-salary-statistics-list
        /// {
        ///   "currentPage": 1,
        ///   "pageSize": 10,
        ///   "statisticsMonth": "202401",
        ///   "storeName": "门店名称",
        ///   "employeeName": "员工姓名",
        ///   "position": "岗位"
        /// }
        /// ```
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>分页数据</returns>
        /// <response code="200">查询成功</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("get-salary-statistics-list")]
        public async Task<dynamic> GetSalaryStatisticsList([FromBody] LqSalaryStatisticsListQueryInput input)
        {
            try
            {
                var query = _db.Queryable<LqSalaryStatisticsEntity>();

                // 添加查询条件
                query = query.WhereIF(!string.IsNullOrEmpty(input.statisticsMonth), x => x.StatisticsMonth == input.statisticsMonth);
                query = query.WhereIF(!string.IsNullOrEmpty(input.storeName), x => x.StoreName.Contains(input.storeName));
                query = query.WhereIF(!string.IsNullOrEmpty(input.employeeName), x => x.EmployeeName.Contains(input.employeeName));
                query = query.WhereIF(!string.IsNullOrEmpty(input.position), x => x.Position == input.position);
                query = query.WhereIF(input.isLocked.HasValue, x => x.IsLocked == input.isLocked.Value);
                // 按总业绩降序排序
                // 分页查询
                var data = await query.Select(x => new LqSalaryStatisticsListOutput
                {
                    Id = x.Id,
                    StatisticsMonth = x.StatisticsMonth,
                    StoreId = x.StoreId,
                    StoreName = x.StoreName,
                    EmployeeId = x.EmployeeId,
                    EmployeeName = x.EmployeeName,
                    Position = x.Position,
                    GoldTriangleTeam = x.GoldTriangleTeam,
                    GoldTriangleId = x.GoldTriangleId,
                    TotalPerformance = x.TotalPerformance,
                    BasePerformance = x.BasePerformance,
                    CooperationPerformance = x.CooperationPerformance,
                    RewardPerformance = x.RewardPerformance,
                    StoreTotalPerformance = x.StoreTotalPerformance,
                    TeamPerformance = x.TeamPerformance,
                    Percentage = x.Percentage,
                    NewCustomerPerformance = x.NewCustomerPerformance,
                    NewCustomerConversionRate = x.NewCustomerConversionRate,
                    NewCustomerPoint = x.NewCustomerPoint,
                    UpgradePerformance = x.UpgradePerformance,
                    UpgradePoint = x.UpgradePoint,
                    Consumption = x.Consumption,
                    ProjectCount = x.ProjectCount,
                    CustomerCount = x.CustomerCount,
                    WorkingDays = x.WorkingDays,
                    LeaveDays = x.LeaveDays,
                    CommissionPoint = x.CommissionPoint,
                    BasePerformanceCommission = x.BasePerformanceCommission,
                    CooperationPerformanceCommission = x.CooperationPerformanceCommission,
                    ConsultantCommission = x.ConsultantCommission,
                    StoreTAreaCommission = x.StoreTZoneCommission,
                    CommissionTotal = x.TotalCommission,
                    HealthCoachBaseSalary = x.HealthCoachBaseSalary,
                    ManualWork = x.HandworkFee,
                    OutherHandworkFee = x.OutherHandworkFee,
                    CarAllowance = x.TransportationAllowance,
                    LessRest = x.LessRest,
                    FullAttendance = x.FullAttendance,
                    CalculatedGrossSalary = x.CalculatedGrossSalary,
                    Guarantee = x.GuaranteedSalary,
                    GuaranteeLeave = x.GuaranteedLeaveDeduction,
                    GuaranteeBaseSalary = x.GuaranteedBaseSalary,
                    GuaranteeSupplement = x.GuaranteedSupplement,
                    FinalGrossSalary = x.FinalGrossSalary,
                    MonthlyTrainingSubsidy = x.MonthlyTrainingSubsidy,
                    MonthlyTransportSubsidy = x.MonthlyTransportSubsidy,
                    LastMonthTrainingSubsidy = x.LastMonthTrainingSubsidy,
                    LastMonthTransportSubsidy = x.LastMonthTransportSubsidy,
                    SubsidyTotal = x.TotalSubsidy,
                    MissingCard = x.MissingCard,
                    Late = x.LateArrival,
                    Leave = x.LeaveDeduction,
                    SocialSecurityDeduction = x.SocialInsuranceDeduction,
                    RewardDeduction = x.RewardDeduction,
                    AccommodationDeduction = x.AccommodationDeduction,
                    StudyPeriodDeduction = x.StudyPeriodDeduction,
                    WorkClothesDeduction = x.WorkClothesDeduction,
                    DeductionTotal = x.TotalDeduction,
                    Bonus = x.Bonus,
                    PhoneDepositReturn = x.ReturnPhoneDeposit,
                    AccommodationDepositReturn = x.ReturnAccommodationDeposit,
                    ActualGrossSalary = x.ActualSalary,
                    MonthlyPaymentStatus = x.MonthlyPaymentStatus,
                    PaymentAmount = x.PaidAmount,
                    PendingPaymentAmount = x.PendingAmount,
                    LastMonthSupplement = x.LastMonthSupplement,
                    MonthlyPaymentTotal = x.MonthlyTotalPayment,
                    IsLocked = x.IsLocked == 1,
                    CreateTime = x.CreateTime,
                    UpdateTime = x.UpdateTime
                }).MergeTable().OrderBy(x => x.TotalPerformance, OrderByType.Desc).ToPagedListAsync(input.currentPage, input.pageSize);
                return PageResult<LqSalaryStatisticsListOutput>.SqlSugarPageResult(data);


            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取工资统计列表失败");
                throw NCCException.Oh($"获取工资统计列表失败: {ex.Message}");
            }
        }

        /// <summary>
        /// 初始化工资统计表
        /// </summary>
        private async Task InitializeSalaryStatistics(string statisticsMonth)
        {
            // 获取所有员工信息
            var employeesSql = @"
                SELECT
                    u.F_Id as EmployeeId,
                    u.F_RealName as EmployeeName,
                    u.F_MDID as StoreId,
                    COALESCE(md.dm, '') as StoreName,
                    CASE
                        WHEN jsj.is_leader = 1 THEN '顾问'
                        ELSE COALESCE(u.F_GW, '')
                    END as Position,
                    COALESCE(jsj.jsj, '') as GoldTriangleTeam,
                    COALESCE(jsj.jsj_id, '') as GoldTriangleId
                FROM BASE_USER u
                LEFT JOIN lq_mdxx md ON u.F_MDID COLLATE utf8mb4_general_ci = md.F_Id COLLATE utf8mb4_general_ci
                LEFT JOIN (
                    SELECT
                        jsu.user_id,
                        jsj.jsj,
                        jsj.F_Id as jsj_id,
                        jsu.is_leader
                    FROM lq_jinsanjiao_user jsu
                    INNER JOIN lq_ycsd_jsj jsj ON jsu.jsj_id COLLATE utf8mb4_general_ci = jsj.F_Id COLLATE utf8mb4_general_ci
                    WHERE jsu.F_Month COLLATE utf8mb4_general_ci = @statisticsMonth
                        AND jsu.status = 'ACTIVE'
                        AND (jsu.F_DeleteMark IS NULL OR jsu.F_DeleteMark = 0)
                ) jsj ON u.F_Id COLLATE utf8mb4_general_ci = jsj.user_id COLLATE utf8mb4_general_ci
                WHERE u.F_EnabledMark = 1 AND u.F_GW = '健康师'
                    AND (u.F_DeleteMark IS NULL OR u.F_DeleteMark = 0)";
            var employees = await _db.Ado.SqlQueryAsync<dynamic>(employeesSql, new { statisticsMonth });

            var salaryEntities = employees.Select(emp => new LqSalaryStatisticsEntity
            {
                Id = YitIdHelper.NextId().ToString(),
                StoreId = emp.StoreId?.ToString() ?? "",
                StoreName = emp.StoreName?.ToString() ?? "",
                Position = emp.Position?.ToString() ?? "",
                EmployeeName = emp.EmployeeName?.ToString() ?? "",
                EmployeeId = emp.EmployeeId?.ToString() ?? "",
                GoldTriangleTeam = emp.GoldTriangleTeam?.ToString() ?? "",
                GoldTriangleId = emp.GoldTriangleId?.ToString() ?? "",
                StatisticsMonth = statisticsMonth,
                IsLocked = 0,
                CreateTime = DateTime.Now,
                UpdateTime = DateTime.Now,
                CreateUser = _userManager.UserId,
                UpdateUser = _userManager.UserId
            }).ToList();

            // 使用事务确保数据一致性
            await _db.Ado.UseTranAsync(async () =>
            {
                // 先删除该月份的历史数据
                await _db.Deleteable<LqSalaryStatisticsEntity>()
                    .Where(x => x.StatisticsMonth == statisticsMonth)
                    .ExecuteCommandAsync();

                // 批量插入新数据
                if (salaryEntities.Any())
                {
                    await _db.Insertable(salaryEntities).ExecuteCommandAsync();
                }
            });

            _logger.LogInformation($"初始化工资统计表完成 - 月份: {statisticsMonth}, 员工数: {salaryEntities.Count}");
        }

        /// <summary>
        /// 从其他统计表更新工资统计数据
        /// </summary>
        private async Task UpdateSalaryStatisticsFromOtherTables(string statisticsMonth)
        {
            // 从个人业绩统计表更新数据
            var personalPerformanceSql = @"
                UPDATE lq_salary_statistics s
                INNER JOIN lq_statistics_personal_performance p
                    ON s.F_EmployeeId COLLATE utf8mb4_general_ci = p.F_EmployeeId COLLATE utf8mb4_general_ci
                    AND s.F_StatisticsMonth = p.F_StatisticsMonth
                SET
                    s.F_TotalPerformance = p.F_TotalPerformance,
                    s.F_BasePerformance = p.F_BasePerformance,
                    s.F_CooperationPerformance = p.F_CooperationPerformance,
                    s.F_NewCustomerPerformance = p.F_FirstOrderPerformance,
                    s.F_UpgradePerformance = p.F_UpgradeOrderPerformance
                WHERE s.F_StatisticsMonth = @statisticsMonth";
            await _db.Ado.ExecuteCommandAsync(personalPerformanceSql, new
            {
                statisticsMonth,
                userId = _userManager.UserId
            });

            // 从门店总业绩统计表更新门店相关数据
            var storePerformanceSql = @"
                UPDATE lq_salary_statistics s
                INNER JOIN lq_statistics_store_total_performance st
                    ON s.F_StoreId COLLATE utf8mb4_general_ci = st.F_StoreId COLLATE utf8mb4_general_ci
                    AND s.F_StatisticsMonth = st.F_StatisticsMonth
                SET
                    s.F_StoreTotalPerformance = st.F_TotalOrderPerformance
                WHERE s.F_StatisticsMonth = @statisticsMonth";
            await _db.Ado.ExecuteCommandAsync(storePerformanceSql, new
            {
                statisticsMonth,
                userId = _userManager.UserId
            });

            // 从金三角统计表更新队伍业绩
            var teamPerformanceSql = @"
                UPDATE lq_salary_statistics s
                INNER JOIN lq_statistics_gold_triangle gt
                    ON s.F_GoldTriangleId COLLATE utf8mb4_general_ci = gt.F_GoldTriangleId COLLATE utf8mb4_general_ci
                    AND s.F_StatisticsMonth = gt.F_StatisticsMonth
                SET
                    s.F_TeamPerformance = gt.F_TotalPerformance
                WHERE s.F_StatisticsMonth = @statisticsMonth
                    AND s.F_GoldTriangleId IS NOT NULL
                    AND s.F_GoldTriangleId != ''";
            await _db.Ado.ExecuteCommandAsync(teamPerformanceSql, new
            {
                statisticsMonth,
                userId = _userManager.UserId
            });

            // 计算占比：队伍业绩 / 个人总业绩
            var percentageSql = @"
                UPDATE lq_salary_statistics
                SET F_Percentage = CASE
                    WHEN F_TotalPerformance > 0 THEN ROUND(( F_TotalPerformance / F_TeamPerformance) * 100, 2)
                    ELSE 0
                END
                WHERE F_StatisticsMonth = @statisticsMonth
                    AND F_TeamPerformance > 0";
            await _db.Ado.ExecuteCommandAsync(percentageSql, new
            {
                statisticsMonth,
                userId = _userManager.UserId
            });

            // 从个人消耗业绩统计表更新消耗数据
            var consumePerformanceSql = @"
                UPDATE lq_salary_statistics s
                INNER JOIN lq_statistics_department_consume_performance cp
                    ON s.F_EmployeeId COLLATE utf8mb4_general_ci = cp.F_UserId COLLATE utf8mb4_general_ci
                    AND s.F_StatisticsMonth COLLATE utf8mb4_general_ci = cp.F_StatisticsMonth COLLATE utf8mb4_general_ci
                SET
                    s.F_Consumption = cp.F_ConsumePerformance,
                    s.F_ProjectCount = cp.F_ConsumeQuantity,
                    s.F_HandworkFee = cp.F_ManualFee,
                    s.F_CustomerCount = cp.F_HeadCount
                WHERE s.F_StatisticsMonth = @statisticsMonth";
            await _db.Ado.ExecuteCommandAsync(consumePerformanceSql, new
            {
                statisticsMonth,
                userId = _userManager.UserId
            });

            // 计算并更新底薪
            await CalculateAndUpdateBaseSalary(statisticsMonth);

            _logger.LogInformation($"从其他统计表更新工资数据完成 - 月份: {statisticsMonth}");
        }

        /// <summary>
        /// 计算并更新健康师底薪
        /// </summary>
        private async Task CalculateAndUpdateBaseSalary(string statisticsMonth)
        {
            // 健康师底薪计算
            var healthCoachSalarySql = @"
                UPDATE lq_salary_statistics s
                SET s.F_HealthCoachBaseSalary = CASE
                    -- 三星：月消耗达到40000元 且 项目数达到156个 → 底薪2400元
                    WHEN s.F_Consumption >= 40000 AND s.F_ProjectCount >= 156 THEN 2400
                    -- 二星：月消耗达到20000元 且 项目数达到126个 → 底薪2200元
                    WHEN s.F_Consumption >= 20000 AND s.F_ProjectCount >= 126 THEN 2200
                    -- 一星：月消耗达到10000元 或 项目数达到96个 → 底薪2000元
                    WHEN s.F_Consumption >= 10000 OR s.F_ProjectCount >= 96 THEN 2000
                    -- 0星：月消耗未达到10000元 且 项目数未达到96个 → 底薪1800元
                    ELSE 1800
                END
                WHERE s.F_StatisticsMonth = @statisticsMonth
                AND s.F_Position = '健康师' OR s.F_Position = '顾问'";
            await _db.Ado.ExecuteCommandAsync(healthCoachSalarySql, new
            {
                statisticsMonth
            });

            _logger.LogInformation($"健康师底薪计算完成 - 月份: {statisticsMonth}");
        }

        #endregion

        #region 其他统计模块列表查询接口

        /// <summary>
        /// 获取金三角开卡业绩统计列表（实时统计）
        /// </summary>
        /// <remarks>
        /// 实时从业务表统计金三角的开卡业绩数据，包括订单数量、总业绩、首次和最后订单日期
        /// 
        /// 数据来源：
        /// - lq_ycsd_jsj: 金三角基础信息表
        /// - lq_kd_jksyj: 健康师业绩表（开单业绩）
        /// - lq_mdxx: 门店信息表
        /// 
        /// 统计逻辑：
        /// - 按金三角ID、月份、门店分组统计
        /// - 订单数量：去重统计开单编号（glkdbh）
        /// - 总业绩：汇总健康师业绩（jksyj），过滤空值和0值
        /// - 首次/最后订单日期：取业绩时间（yjsj）的最小值和最大值
        /// 
        /// 性能优化：
        /// - 使用索引：jsj_id, yjsj, F_IsEffective
        /// - 先过滤再JOIN，减少数据量
        /// - 使用子查询优化聚合计算
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>分页结果</returns>
        [HttpPost("get-gold-triangle-statistics-list")]
        public async Task<dynamic> GetGoldTriangleStatisticsList([FromBody] LqGoldTriangleStatisticsListQueryInput input)
        {
            try
            {
                // 构建WHERE条件
                var whereConditions = new List<string>();
                var parameters = new List<SugarParameter>();

                // 月份条件
                if (!string.IsNullOrEmpty(input.StatisticsMonth))
                {
                    whereConditions.Add("jsj.yf = @StatisticsMonth");
                    parameters.Add(new SugarParameter("@StatisticsMonth", input.StatisticsMonth));
                }

                // 金三角名称条件
                if (!string.IsNullOrEmpty(input.GoldTriangleName))
                {
                    whereConditions.Add("jsj.jsj LIKE @GoldTriangleName");
                    parameters.Add(new SugarParameter("@GoldTriangleName", $"%{input.GoldTriangleName}%"));
                }

                // 门店名称条件
                if (!string.IsNullOrEmpty(input.StoreName))
                {
                    whereConditions.Add("md.dm LIKE @StoreName");
                    parameters.Add(new SugarParameter("@StoreName", $"%{input.StoreName}%"));
                }

                var whereClause = whereConditions.Any() ? "WHERE " + string.Join(" AND ", whereConditions) : "";

                // 实时统计SQL - 优化性能
                // 使用子查询先过滤有效业绩数据，减少JOIN数据量
                var sql = $@"
                    SELECT
                        CONCAT(jsj.F_Id, '_', jsj.yf) AS Id,
                        jsj.F_Id AS GoldTriangleId,
                        jsj.jsj AS GoldTriangleName,
                        jsj.yf AS StatisticsMonth,
                        jsj.md AS StoreId,
                        COALESCE(md.dm, '') AS StoreName,
                        COALESCE(stats.OrderCount, 0) AS OrderCount,
                        COALESCE(stats.TotalPerformance, 0) AS TotalPerformance,
                        stats.FirstOrderDate,
                        stats.LastOrderDate,
                        NOW() AS CreateTime
                    FROM lq_ycsd_jsj jsj
                    LEFT JOIN lq_mdxx md ON jsj.md = md.F_Id
                    LEFT JOIN (
                        -- 子查询：按金三角ID和月份统计业绩数据（先过滤再聚合，提高效率）
                        SELECT
                            jksyj.jsj_id,
                            YEAR(jksyj.yjsj) * 100 + MONTH(jksyj.yjsj) AS statistics_month,
                            COUNT(DISTINCT jksyj.glkdbh) AS OrderCount,
                            SUM(CAST(jksyj.jksyj AS DECIMAL(18,2))) AS TotalPerformance,
                            MIN(jksyj.yjsj) AS FirstOrderDate,
                            MAX(jksyj.yjsj) AS LastOrderDate
                        FROM lq_kd_jksyj jksyj
                        WHERE jksyj.F_IsEffective = 1
                            AND jksyj.yjsj IS NOT NULL
                            AND jksyj.jksyj IS NOT NULL
                            AND jksyj.jksyj != ''
                            AND jksyj.jksyj != '0'
                            AND jksyj.jsj_id IS NOT NULL
                            AND jksyj.jsj_id != ''
                        GROUP BY jksyj.jsj_id, YEAR(jksyj.yjsj), MONTH(jksyj.yjsj)
                    ) stats ON (
                        stats.jsj_id = jsj.F_Id
                        AND stats.statistics_month = CAST(jsj.yf AS UNSIGNED)
                    )
                    {whereClause}
                    ORDER BY stats.TotalPerformance DESC, jsj.yf DESC
                    LIMIT @PageSize OFFSET @Offset";
                parameters.Add(new SugarParameter("@PageSize", input.PageSize));
                parameters.Add(new SugarParameter("@Offset", (input.PageIndex - 1) * input.PageSize));

                // 查询总数
                var countSql = $@"
                    SELECT COUNT(DISTINCT jsj.F_Id)
                    FROM lq_ycsd_jsj jsj
                    LEFT JOIN lq_mdxx md ON jsj.md = md.F_Id
                    {whereClause}";
                var countParameters = parameters.Where(p => p.ParameterName != "@PageSize" && p.ParameterName != "@Offset").ToList();
                var totalCount = await _db.Ado.GetIntAsync(countSql, countParameters);

                // 执行查询
                var result = await _db.Ado.SqlQueryAsync<LqGoldTriangleStatisticsListOutput>(sql, parameters);

                return new
                {
                    list = result,
                    pagination = new
                    {
                        pageIndex = input.PageIndex,
                        pageSize = input.PageSize,
                        total = totalCount
                    }
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询金三角开卡业绩统计列表失败");
                throw NCCException.Oh($"查询金三角开卡业绩统计列表失败: {ex.Message}");
            }
        }

        /// <summary>
        /// 获取科技部开单业绩统计列表（实时统计）
        /// </summary>
        /// <remarks>
        /// 实时从业务表统计科技部老师的开单业绩数据，包括订单数量、总业绩等
        /// 
        /// 数据来源：
        /// - lq_kd_kjbsyj: 科技部老师开单业绩表
        /// - lq_kd_pxmx: 开单品项明细表（用于统计项目数量）
        /// 
        /// 统计逻辑：
        /// - 按老师ID分组统计
        /// - 订单数量：去重统计开单品项ID（F_kdpxid）
        /// - 总业绩：汇总科技部老师业绩（kjblsyj），过滤空值和0值
        /// - 项目数量：汇总品项明细的项目数量（F_ProjectNumber）
        /// 
        /// 性能优化：
        /// - 使用索引：kjbls, yjsj, F_IsEffective
        /// - 先过滤再聚合，减少数据量
        /// - 使用子查询优化聚合计算
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>分页结果</returns>
        [HttpPost("get-tech-performance-statistics-list")]
        public async Task<dynamic> GetTechPerformanceStatisticsList([FromBody] LqTechPerformanceStatisticsListQueryInput input)
        {
            try
            {
                // 构建WHERE条件
                var whereConditions = new List<string>();
                var parameters = new List<SugarParameter>();

                // 基础条件
                var baseConditions = new List<string>
                {
                    "k.kjbls IS NOT NULL",
                    "k.kjblsxm IS NOT NULL",
                    "k.yjsj IS NOT NULL",
                    "k.kjblsyj IS NOT NULL",
                    "k.kjblsyj != ''",
                    "k.kjblsyj != '0'",
                    "k.F_IsEffective = 1"
                };

                // 月份条件
                if (!string.IsNullOrEmpty(input.StatisticsMonth))
                {
                    var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
                    var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
                    baseConditions.Add("YEAR(k.yjsj) = @Year");
                    baseConditions.Add("MONTH(k.yjsj) = @Month");
                    parameters.Add(new SugarParameter("@Year", year));
                    parameters.Add(new SugarParameter("@Month", month));
                }

                // 老师姓名条件
                if (!string.IsNullOrEmpty(input.TeacherName))
                {
                    baseConditions.Add("k.kjblsxm LIKE @TeacherName");
                    parameters.Add(new SugarParameter("@TeacherName", $"%{input.TeacherName}%"));
                }

                var whereClause = "WHERE " + string.Join(" AND ", baseConditions);

                // 实时统计SQL - 优化性能
                var sql = $@"
                    SELECT
                        CONCAT(k.kjbls, '_', DATE_FORMAT(k.yjsj, '%Y%m')) AS Id,
                        DATE_FORMAT(k.yjsj, '%Y%m') AS StatisticsMonth,
                        k.kjbls AS TeacherId,
                        k.kjblsxm AS TeacherName,
                        '' AS StoreId,
                        '' AS StoreName,
                        COUNT(DISTINCT k.F_kdpxid) AS OrderCount,
                        SUM(CAST(COALESCE(k.kjblsyj, 0) AS DECIMAL(18,2))) AS TotalPerformance,
                        NOW() AS CreateTime
                    FROM lq_kd_kjbsyj k
                    LEFT JOIN lq_kd_pxmx pm ON k.F_kdpxid = pm.F_Id AND pm.F_IsEffective = 1
                    {whereClause}
                    GROUP BY k.kjbls, k.kjblsxm, DATE_FORMAT(k.yjsj, '%Y%m')
                    ORDER BY TotalPerformance DESC, StatisticsMonth DESC
                    LIMIT @PageSize OFFSET @Offset";
                parameters.Add(new SugarParameter("@PageSize", input.PageSize));
                parameters.Add(new SugarParameter("@Offset", (input.PageIndex - 1) * input.PageSize));

                // 查询总数
                var countSql = $@"
                    SELECT COUNT(DISTINCT CONCAT(k.kjbls, '_', DATE_FORMAT(k.yjsj, '%Y%m')))
                    FROM lq_kd_kjbsyj k
                    {whereClause}";
                var countParameters = parameters.Where(p => p.ParameterName != "@PageSize" && p.ParameterName != "@Offset").ToList();
                var totalCount = await _db.Ado.GetIntAsync(countSql, countParameters);

                // 执行查询
                var result = await _db.Ado.SqlQueryAsync<LqTechPerformanceStatisticsListOutput>(sql, parameters);

                return new
                {
                    list = result,
                    pagination = new
                    {
                        pageIndex = input.PageIndex,
                        pageSize = input.PageSize,
                        total = totalCount
                    }
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询科技部开单业绩统计列表失败");
                throw NCCException.Oh($"查询科技部开单业绩统计列表失败: {ex.Message}");
            }
        }

        /// <summary>
        /// 获取门店耗卡业绩统计列表（实时统计）
        /// </summary>
        /// <remarks>
        /// 实时从业务表统计门店的耗卡业绩数据，包括耗卡业绩、项目数量、手工费等
        /// 
        /// 数据来源：
        /// - lq_xh_hyhk: 耗卡表
        /// - lq_xh_pxmx: 耗卡品项明细表
        /// - lq_mdxx: 门店信息表
        /// 
        /// 统计逻辑：
        /// - 按门店分组统计
        /// - 耗卡业绩：汇总品项明细的总价（F_TotalPrice）
        /// - 项目数量：汇总品项明细的项目数量（F_ProjectNumber）
        /// - 手工费：汇总耗卡表的手工费（sgfy）
        /// 
        /// 性能优化：
        /// - 使用索引：md, hksj, F_IsEffective
        /// - 先过滤再聚合，减少数据量
        /// - 使用子查询优化聚合计算
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>分页结果</returns>
        [HttpPost("get-store-consume-performance-statistics-list")]
        public async Task<dynamic> GetStoreConsumePerformanceStatisticsList([FromBody] LqStoreConsumePerformanceStatisticsListQueryInput input)
        {
            try
            {
                // 构建WHERE条件
                var whereConditions = new List<string>();
                var parameters = new List<SugarParameter>();

                // 月份条件
                if (!string.IsNullOrEmpty(input.StatisticsMonth))
                {
                    whereConditions.Add("DATE_FORMAT(hyhk.hksj, '%Y%m') = @StatisticsMonth");
                    parameters.Add(new SugarParameter("@StatisticsMonth", input.StatisticsMonth));
                }

                // 门店名称条件
                if (!string.IsNullOrEmpty(input.StoreName))
                {
                    whereConditions.Add("(hyhk.mdmc LIKE @StoreName OR mdxx.dm LIKE @StoreName)");
                    parameters.Add(new SugarParameter("@StoreName", $"%{input.StoreName}%"));
                }

                // 基础条件
                whereConditions.Add("hyhk.F_IsEffective = 1");

                var whereClause = "WHERE " + string.Join(" AND ", whereConditions);

                // 实时统计SQL - 优化性能
                var sql = $@"
                    SELECT
                        CONCAT(hyhk.md, '_', DATE_FORMAT(hyhk.hksj, '%Y%m')) AS Id,
                        DATE_FORMAT(hyhk.hksj, '%Y%m') AS StatisticsMonth,
                        hyhk.md AS StoreId,
                        COALESCE(hyhk.mdmc, mdxx.dm, '') AS StoreName,
                        COALESCE(SUM(pxmx.F_TotalPrice), 0) AS ConsumePerformance,
                        COALESCE(SUM(pxmx.F_TotalPrice), 0) AS TotalPerformance,
                        CAST(COALESCE(SUM(pxmx.F_ProjectNumber), 0) AS UNSIGNED) AS OrderCount,
                        CASE
                            WHEN EXISTS (
                                SELECT 1 FROM lq_md_xdbhsj xdbh
                                WHERE xdbh.mdid = hyhk.md
                                    AND xdbh.sfqy = 1
                                    AND xdbh.bhkssj <= DATE_FORMAT(hyhk.hksj, '%Y-%m-%d')
                                    AND xdbh.bhjssj >= DATE_FORMAT(hyhk.hksj, '%Y-%m-%d')
                                LIMIT 1
                            ) THEN 1
                            ELSE 0
                        END AS IsNewStore,
                        NOW() AS CreateTime
                    FROM lq_xh_hyhk hyhk
                    LEFT JOIN lq_xh_pxmx pxmx ON hyhk.F_Id = pxmx.F_ConsumeInfoId AND pxmx.F_IsEffective = 1
                    LEFT JOIN lq_mdxx mdxx ON hyhk.md = mdxx.F_Id
                    {whereClause}
                    GROUP BY hyhk.md, hyhk.mdmc, mdxx.dm, DATE_FORMAT(hyhk.hksj, '%Y%m')
                    HAVING ConsumePerformance > 0 OR OrderCount > 0
                    ORDER BY ConsumePerformance DESC, StatisticsMonth DESC
                    LIMIT @PageSize OFFSET @Offset";
                parameters.Add(new SugarParameter("@PageSize", input.PageSize));
                parameters.Add(new SugarParameter("@Offset", (input.PageIndex - 1) * input.PageSize));

                // 查询总数（使用子查询统计满足条件的分组数）
                var countSql = $@"
                    SELECT COUNT(*)
                    FROM (
                        SELECT
                            hyhk.md,
                            DATE_FORMAT(hyhk.hksj, '%Y%m') AS month_key
                        FROM lq_xh_hyhk hyhk
                        LEFT JOIN lq_xh_pxmx pxmx ON hyhk.F_Id = pxmx.F_ConsumeInfoId AND pxmx.F_IsEffective = 1
                        LEFT JOIN lq_mdxx mdxx ON hyhk.md = mdxx.F_Id
                        {whereClause}
                        GROUP BY hyhk.md, DATE_FORMAT(hyhk.hksj, '%Y%m')
                        HAVING COALESCE(SUM(pxmx.F_TotalPrice), 0) > 0 OR COALESCE(SUM(pxmx.F_ProjectNumber), 0) > 0
                    ) AS grouped_data";
                var countParameters = parameters.Where(p => p.ParameterName != "@PageSize" && p.ParameterName != "@Offset").ToList();
                var totalCount = await _db.Ado.GetIntAsync(countSql, countParameters);

                // 执行查询
                var result = await _db.Ado.SqlQueryAsync<LqStoreConsumePerformanceStatisticsListOutput>(sql, parameters);

                return new
                {
                    list = result,
                    pagination = new
                    {
                        pageIndex = input.PageIndex,
                        pageSize = input.PageSize,
                        total = totalCount
                    }
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询门店耗卡业绩统计列表失败");
                throw NCCException.Oh($"查询门店耗卡业绩统计列表失败: {ex.Message}");
            }
        }

        /// <summary>
        /// 获取部门消耗业绩统计列表（实时统计）
        /// </summary>
        /// <remarks>
        /// 实时从业务表统计部门员工的消耗业绩数据，包括健康师和科技部老师两种类型
        /// 
        /// 数据来源：
        /// - lq_xh_jksyj: 健康师消耗业绩表
        /// - lq_xh_kjbsyj: 科技部老师消耗业绩表
        /// - lq_xh_hyhk: 耗卡表（用于获取时间和门店信息）
        /// - lq_mdxx: 门店信息表
        /// 
        /// 统计逻辑：
        /// - 健康师：按健康师ID、门店分组，统计消耗业绩、项目数量、人头、人次
        /// - 科技部老师：按老师ID、门店分组，统计消耗业绩、项目数量（人头、人次为0）
        /// - 人头：月度去重客户数
        /// - 人次：日度去重到店数（每天同一个客户只算一次）
        /// 
        /// 性能优化：
        /// - 使用UNION ALL合并健康师和科技部老师数据
        /// - 使用子查询优化人头和人次统计
        /// - 使用索引：jkszh/kjblszh, md, hksj, F_IsEffective
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>分页结果</returns>
        [HttpPost("get-department-consume-performance-statistics-list")]
        public async Task<dynamic> GetDepartmentConsumePerformanceStatisticsList([FromBody] LqDepartmentConsumePerformanceStatisticsListQueryInput input)
        {
            try
            {
                // 构建WHERE条件
                var whereConditions = new List<string>();
                var parameters = new List<SugarParameter>();

                // 月份条件（用于子查询）
                var monthCondition = "";
                if (!string.IsNullOrEmpty(input.StatisticsMonth))
                {
                    monthCondition = "AND DATE_FORMAT(hyhk_inner.hksj, '%Y%m') = @StatisticsMonth";
                    whereConditions.Add("DATE_FORMAT(hyhk.hksj, '%Y%m') = @StatisticsMonth");
                    parameters.Add(new SugarParameter("@StatisticsMonth", input.StatisticsMonth));
                }

                // 员工姓名条件（需要在UNION的两个查询中都添加）
                var employeeNameConditionJks = "";
                var employeeNameConditionKjbs = "";
                if (!string.IsNullOrEmpty(input.EmployeeName))
                {
                    employeeNameConditionJks = "AND jksyj.jksxm LIKE @EmployeeName";
                    employeeNameConditionKjbs = "AND kjbsyj.kjblsxm LIKE @EmployeeName";
                    parameters.Add(new SugarParameter("@EmployeeName", $"%{input.EmployeeName}%"));
                }

                // 门店名称条件
                var storeNameCondition = "";
                if (!string.IsNullOrEmpty(input.StoreName))
                {
                    storeNameCondition = "AND (hyhk.mdmc LIKE @StoreName OR md.dm LIKE @StoreName)";
                    parameters.Add(new SugarParameter("@StoreName", $"%{input.StoreName}%"));
                }

                // 岗位条件
                var positionCondition = "";
                if (!string.IsNullOrEmpty(input.Position))
                {
                    positionCondition = "AND dept_type LIKE @Position";
                    parameters.Add(new SugarParameter("@Position", $"%{input.Position}%"));
                }

                var baseWhere = whereConditions.Any() ? string.Join(" AND ", whereConditions) : "";
                var baseWhereClause = !string.IsNullOrEmpty(baseWhere) ? "WHERE " + baseWhere : "";

                // 实时统计SQL - 使用UNION ALL合并健康师和科技部老师数据
                var sql = $@"
                    SELECT
                        CONCAT(dept_type, '_', user_id, '_', store_id, '_', statistics_month) AS Id,
                        statistics_month AS StatisticsMonth,
                        user_id AS EmployeeId,
                        user_name AS EmployeeName,
                        store_id AS StoreId,
                        store_name AS StoreName,
                        dept_type AS Position,
                        consume_performance AS ConsumePerformance,
                        consume_performance AS TotalPerformance,
                        consume_quantity AS OrderCount,
                        head_count AS HeadCount,
                        person_count AS PersonCount,
                        0 AS RefundAmount,
                        0 AS RefundCount,
                        NOW() AS CreateTime
                    FROM (
                        -- 健康师消耗业绩统计
                        SELECT
                            '健康师' AS dept_type,
                            jksyj.jkszh AS user_id,
                            jksyj.jksxm AS user_name,
                            hyhk.md AS store_id,
                            COALESCE(hyhk.mdmc, md.dm, '') AS store_name,
                            DATE_FORMAT(hyhk.hksj, '%Y%m') AS statistics_month,
                            COALESCE(SUM(jksyj.jksyj), 0) AS consume_performance,
                            COALESCE(SUM(jksyj.F_kdpxNumber), 0) AS consume_quantity,
                            COALESCE(headcount_stats.head_count, 0) AS head_count,
                            COALESCE(personcount_stats.person_count, 0) AS person_count
                        FROM lq_xh_jksyj jksyj
                        INNER JOIN lq_xh_hyhk hyhk ON jksyj.glkdbh = hyhk.F_Id AND hyhk.F_IsEffective = 1
                        LEFT JOIN lq_mdxx md ON hyhk.md = md.F_Id
                        LEFT JOIN (
                            -- 人头统计：月度去重客户数
                            SELECT
                                jksyj_inner.jkszh,
                                hyhk_inner.md,
                                COUNT(DISTINCT hyhk_inner.hy) AS head_count
                            FROM lq_xh_jksyj jksyj_inner
                            INNER JOIN lq_xh_hyhk hyhk_inner ON jksyj_inner.glkdbh = hyhk_inner.F_Id AND hyhk_inner.F_IsEffective = 1
                            WHERE jksyj_inner.F_IsEffective = 1
                                {monthCondition}
                            GROUP BY jksyj_inner.jkszh, hyhk_inner.md
                        ) headcount_stats ON jksyj.jkszh = headcount_stats.jkszh AND hyhk.md = headcount_stats.md
                        LEFT JOIN (
                            -- 人次统计：日度去重客户数
                            SELECT
                                jksyj_inner.jkszh,
                                hyhk_inner.md,
                                COUNT(DISTINCT CONCAT(hyhk_inner.hy, '-', DATE(hyhk_inner.hksj))) AS person_count
                            FROM lq_xh_jksyj jksyj_inner
                            INNER JOIN lq_xh_hyhk hyhk_inner ON jksyj_inner.glkdbh = hyhk_inner.F_Id AND hyhk_inner.F_IsEffective = 1
                            WHERE jksyj_inner.F_IsEffective = 1
                                {monthCondition}
                            GROUP BY jksyj_inner.jkszh, hyhk_inner.md
                        ) personcount_stats ON jksyj.jkszh = personcount_stats.jkszh AND hyhk.md = personcount_stats.md
                        WHERE jksyj.F_IsEffective = 1
                            {(!string.IsNullOrEmpty(baseWhere) ? "AND " + baseWhere : "")}
                            {employeeNameConditionJks}
                            {storeNameCondition}
                        GROUP BY jksyj.jkszh, jksyj.jksxm, hyhk.md, hyhk.mdmc, md.dm, DATE_FORMAT(hyhk.hksj, '%Y%m'), headcount_stats.head_count, personcount_stats.person_count, statistics_month

                        UNION ALL

                        -- 科技部老师消耗业绩统计
                        SELECT
                            '科技部老师' AS dept_type,
                            kjbsyj.kjblszh AS user_id,
                            kjbsyj.kjblsxm AS user_name,
                            hyhk.md AS store_id,
                            COALESCE(hyhk.mdmc, md.dm, '') AS store_name,
                            DATE_FORMAT(hyhk.hksj, '%Y%m') AS statistics_month,
                            COALESCE(SUM(kjbsyj.kjblsyj), 0) AS consume_performance,
                            COALESCE(SUM(kjbsyj.F_hdpxNumber), 0) AS consume_quantity,
                            0 AS head_count,
                            0 AS person_count
                        FROM lq_xh_kjbsyj kjbsyj
                        INNER JOIN lq_xh_hyhk hyhk ON kjbsyj.glkdbh = hyhk.F_Id AND hyhk.F_IsEffective = 1
                        LEFT JOIN lq_mdxx md ON hyhk.md = md.F_Id
                        WHERE kjbsyj.F_IsEffective = 1
                            {(!string.IsNullOrEmpty(baseWhere) ? "AND " + baseWhere : "")}
                            {employeeNameConditionKjbs}
                            {storeNameCondition}
                        GROUP BY kjbsyj.kjblszh, kjbsyj.kjblsxm, hyhk.md, hyhk.mdmc, md.dm, DATE_FORMAT(hyhk.hksj, '%Y%m')
                    ) combined_stats
                    WHERE 1=1
                        {positionCondition}
                    ORDER BY consume_performance DESC, StatisticsMonth DESC
                    LIMIT @PageSize OFFSET @Offset";
                parameters.Add(new SugarParameter("@PageSize", input.PageSize));
                parameters.Add(new SugarParameter("@Offset", (input.PageIndex - 1) * input.PageSize));

                // 查询总数（需要重新构建，因为UNION ALL的结构）
                var countSql = $@"
                    SELECT COUNT(*)
                    FROM (
                        SELECT
                            '健康师' AS dept_type,
                            jksyj.jkszh AS user_id,
                            jksyj.jksxm AS user_name,
                            hyhk.md AS store_id,
                            COALESCE(hyhk.mdmc, md.dm, '') AS store_name
                        FROM lq_xh_jksyj jksyj
                        INNER JOIN lq_xh_hyhk hyhk ON jksyj.glkdbh = hyhk.F_Id AND hyhk.F_IsEffective = 1
                        LEFT JOIN lq_mdxx md ON hyhk.md = md.F_Id
                        WHERE jksyj.F_IsEffective = 1
                            {(!string.IsNullOrEmpty(baseWhere) ? "AND " + baseWhere : "")}
                            {employeeNameConditionJks}
                            {storeNameCondition}
                        GROUP BY jksyj.jkszh, jksyj.jksxm, hyhk.md, hyhk.mdmc, md.dm, DATE_FORMAT(hyhk.hksj, '%Y%m')

                        UNION ALL

                        SELECT
                            '科技部老师' AS dept_type,
                            kjbsyj.kjblszh AS user_id,
                            kjbsyj.kjblsxm AS user_name,
                            hyhk.md AS store_id,
                            COALESCE(hyhk.mdmc, md.dm, '') AS store_name
                        FROM lq_xh_kjbsyj kjbsyj
                        INNER JOIN lq_xh_hyhk hyhk ON kjbsyj.glkdbh = hyhk.F_Id AND hyhk.F_IsEffective = 1
                        LEFT JOIN lq_mdxx md ON hyhk.md = md.F_Id
                        WHERE kjbsyj.F_IsEffective = 1
                            {(!string.IsNullOrEmpty(baseWhere) ? "AND " + baseWhere : "")}
                            {employeeNameConditionKjbs}
                            {storeNameCondition}
                        GROUP BY kjbsyj.kjblszh, kjbsyj.kjblsxm, hyhk.md, hyhk.mdmc, md.dm, DATE_FORMAT(hyhk.hksj, '%Y%m')
                    ) combined_stats
                    WHERE 1=1
                        {positionCondition}";
                var countParameters = parameters.Where(p => p.ParameterName != "@PageSize" && p.ParameterName != "@Offset").ToList();
                var totalCount = await _db.Ado.GetIntAsync(countSql, countParameters);

                // 执行查询
                var result = await _db.Ado.SqlQueryAsync<LqDepartmentConsumePerformanceStatisticsListOutput>(sql, parameters);

                return new
                {
                    list = result,
                    pagination = new
                    {
                        pageIndex = input.PageIndex,
                        pageSize = input.PageSize,
                        total = totalCount
                    }
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询个人消耗业绩统计列表失败");
                throw NCCException.Oh($"查询个人消耗业绩统计列表失败: {ex.Message}");
            }
        }

        /// <summary>
        /// 获取门店总业绩统计列表（实时查询）
        /// </summary>
        /// <remarks>
        /// 实时查询门店总业绩统计数据，支持多条件筛选，直接从开单记录表统计
        /// 
        /// 示例请求：
        /// ```json
        /// POST /api/Extend/LqStatistics/get-store-total-performance-statistics-list
        /// {
        ///   "statisticsMonth": "202401",
        ///   "storeName": "门店名称",
        ///   "pageIndex": 1,
        ///   "pageSize": 20
        /// }
        /// ```
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>分页结果</returns>
        /// <response code="200">成功返回分页数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("get-store-total-performance-statistics-list")]
        public async Task<dynamic> GetStoreTotalPerformanceStatisticsList([FromBody] LqStoreTotalPerformanceStatisticsListQueryInput input)
        {
            try
            {
                // 验证统计月份必填
                if (string.IsNullOrEmpty(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
                {
                    throw NCCException.Oh("统计月份不能为空，格式为YYYYMM");
                }

                var statisticsMonth = input.StatisticsMonth;
                var year = int.Parse(statisticsMonth.Substring(0, 4));
                var month = int.Parse(statisticsMonth.Substring(4, 2));

                // 计算日期范围（使用日期范围查询替代DATE_FORMAT函数，提升性能）
                var startDate = new DateTime(year, month, 1);
                var endDate = startDate.AddMonths(1).AddDays(-1).Date.AddHours(23).AddMinutes(59).AddSeconds(59);

                // 构建筛选条件
                var whereConditions = new List<string>();
                var parameters = new Dictionary<string, object>
                {
                    { "@statisticsMonth", statisticsMonth },
                    { "@startDate", startDate },
                    { "@endDate", endDate }
                };

                // 门店名称筛选
                if (!string.IsNullOrEmpty(input.StoreName))
                {
                    whereConditions.Add("md.dm LIKE @StoreName");
                    parameters.Add("@StoreName", $"%{input.StoreName}%");
                }

                var whereClause = whereConditions.Any() ? "WHERE " + string.Join(" AND ", whereConditions) : "";

                // 构建实时查询SQL - 参考SaveStoreTotalPerformanceStatistics的逻辑
                var sql = $@"
                    SELECT
                        store_data.F_StoreId,
                        store_data.F_StoreName,
                        @statisticsMonth as F_StatisticsMonth,
                        store_data.F_TotalPerformance,
                        store_data.F_DebtAmount,
                        store_data.F_TotalOrderPerformance,
                        store_data.F_StorageDeductionAmount,
                        COALESCE(item_data.F_ItemQuantity, 0) as F_ItemQuantity,
                        store_data.F_FirstOrderCount,
                        store_data.F_UpgradeOrderCount,
                        store_data.F_FirstOrderPerformance,
                        store_data.F_UpgradeOrderPerformance,
                        COALESCE(refund_data.F_RefundAmount, 0) as F_RefundAmount,
                        COALESCE(refund_data.F_RefundCount, 0) as F_RefundCount
                    FROM (
                        SELECT
                            kd.djmd as F_StoreId,
                            md.dm as F_StoreName,
                            COALESCE(SUM(kd.zdyj), 0) as F_TotalPerformance,
                            COALESCE(SUM(kd.qk), 0) as F_DebtAmount,
                            COALESCE(SUM(kd.sfyj), 0) as F_TotalOrderPerformance,
                            COALESCE(SUM(kd.F_DeductAmount), 0) as F_StorageDeductionAmount,
                            COUNT(DISTINCT CASE WHEN kd.sfskdd = '是' THEN kd.F_Id END) as F_FirstOrderCount,
                            COUNT(DISTINCT CASE WHEN kd.sfskdd = '否' THEN kd.F_Id END) as F_UpgradeOrderCount,
                            SUM(CASE WHEN kd.sfskdd = '是' THEN COALESCE(kd.zdyj, 0) ELSE 0 END) as F_FirstOrderPerformance,
                            SUM(CASE WHEN kd.sfskdd = '否' THEN COALESCE(kd.zdyj, 0) ELSE 0 END) as F_UpgradeOrderPerformance
                        FROM lq_kd_kdjlb kd
                        LEFT JOIN lq_mdxx md ON CONVERT(kd.djmd USING utf8mb4) = md.F_Id
                        WHERE kd.F_IsEffective = 1
                            AND kd.kdrq >= @startDate
                            AND kd.kdrq <= @endDate
                        GROUP BY kd.djmd, md.dm
                    ) store_data
                    LEFT JOIN (
                        SELECT
                            kd.djmd as F_StoreId,
                            COUNT(pxmx.F_ProjectNumber) as F_ItemQuantity
                        FROM lq_kd_kdjlb kd
                        LEFT JOIN lq_kd_pxmx pxmx ON CONVERT(kd.F_Id USING utf8mb4) = pxmx.glkdbh AND pxmx.F_IsEffective = 1
                        WHERE kd.F_IsEffective = 1
                            AND kd.kdrq >= @startDate
                            AND kd.kdrq <= @endDate
                        GROUP BY kd.djmd
                    ) item_data ON store_data.F_StoreId = item_data.F_StoreId
                    LEFT JOIN (
                        SELECT
                            hytk.md as F_StoreId,
                            COALESCE(SUM(hytk.F_ActualRefundAmount), 0) as F_RefundAmount,
                            COUNT(DISTINCT hytk.F_Id) as F_RefundCount
                        FROM lq_hytk_hytk hytk
                        WHERE hytk.F_IsEffective = 1
                            AND hytk.tksj >= @startDate
                            AND hytk.tksj <= @endDate
                        GROUP BY hytk.md
                    ) refund_data ON store_data.F_StoreId = refund_data.F_StoreId
                    {whereClause}";
                // 查询总数
                var countSql = $"SELECT COUNT(*) FROM ({sql}) AS total_count";
                var totalCount = await _db.Ado.GetIntAsync(countSql, parameters);

                // 分页查询
                var pageIndex = input.PageIndex > 0 ? input.PageIndex : 1;
                var pageSize = input.PageSize > 0 ? input.PageSize : 20;
                var offset = (pageIndex - 1) * pageSize;
                var pagedSql = $"{sql} ORDER BY store_data.F_TotalPerformance DESC LIMIT {pageSize} OFFSET {offset}";

                _logger.LogInformation($"执行门店总业绩统计实时查询SQL - 月份: {statisticsMonth}, 页码: {pageIndex}, 每页: {pageSize}");

                var statisticsData = await _db.Ado.SqlQueryAsync<dynamic>(pagedSql, parameters);

                // 映射到输出DTO
                var outputList = statisticsData.Select(data => new LqStoreTotalPerformanceStatisticsListOutput
                {
                    Id = YitIdHelper.NextId().ToString(), // 实时查询没有ID，生成临时ID
                    StatisticsMonth = statisticsMonth,
                    StoreId = data.F_StoreId?.ToString() ?? "",
                    StoreName = data.F_StoreName?.ToString() ?? "",
                    TotalPerformance = Convert.ToDecimal(data.F_TotalPerformance ?? 0),
                    DebtAmount = Convert.ToDecimal(data.F_DebtAmount ?? 0),
                    TotalOrderPerformance = Convert.ToDecimal(data.F_TotalOrderPerformance ?? 0),
                    StorageDeductionAmount = Convert.ToDecimal(data.F_StorageDeductionAmount ?? 0),
                    ItemQuantity = Convert.ToInt32(data.F_ItemQuantity ?? 0),
                    FirstOrderCount = Convert.ToInt32(data.F_FirstOrderCount ?? 0),
                    UpgradeOrderCount = Convert.ToInt32(data.F_UpgradeOrderCount ?? 0),
                    RefundAmount = Convert.ToDecimal(data.F_RefundAmount ?? 0),
                    RefundCount = Convert.ToInt32(data.F_RefundCount ?? 0),
                    ActualPerformance = Convert.ToDecimal(data.F_TotalOrderPerformance ?? 0) - Convert.ToDecimal(data.F_RefundAmount ?? 0),
                    CreateTime = DateTime.Now
                }).ToList();

                return new
                {
                    list = outputList,
                    pagination = new
                    {
                        pageIndex = pageIndex,
                        pageSize = pageSize,
                        total = totalCount
                    }
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询门店总业绩统计列表失败");
                throw NCCException.Oh($"查询门店总业绩统计列表失败: {ex.Message}");
            }
        }

        #endregion

        #region 健康师业绩统计

        /// <summary>
        /// 获取健康师业绩统计数据
        /// </summary>
        /// <remarks>
        /// 根据健康师ID和月份统计健康师的完整业绩数据
        /// 包括：拓客人数、邀约人数、预约人数、开单、消耗、退卡、人头、人次、项目数
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "userId": "健康师ID",
        ///   "statisticsMonth": "202510"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - userId: 健康师ID（必填）
        /// - statisticsMonth: 统计月份，格式YYYYMM（必填）
        /// 
        /// 返回字段说明：
        /// - UserId: 健康师ID
        /// - StatisticsMonth: 统计月份
        /// - InvitationCount: 拓客人数
        /// - InviteCount: 邀约人数
        /// - AppointmentCount: 预约人数
        /// - BillingCount: 开单数量
        /// - BillingAmount: 开单金额
        /// - BillingProjectCount: 开单项目数（项目次数总和）
        /// - ConsumeCount: 消耗单数（消耗主表的去重记录数）
        /// - ConsumeAmount: 消耗金额
        /// - ConsumeProjectCount: 消耗项目数（项目次数总和，包含原始+加班+陪同）
        /// - ConsumeOriginalProjectCount: 消耗原始项目数（原始项目次数总和）
        /// - ConsumeOvertimeProjectCount: 消耗加班项目数（加班项目次数总和）
        /// - ConsumeAccompaniedProjectCount: 消耗陪同项目数（陪同项目次数总和）
        /// - RefundCount: 退卡单数量
        /// - RefundAmount: 退卡金额
        /// - HeadCount: 人头（月度去重客户数）
        /// - PersonCount: 人次（日度去重客户数）
        /// - LaborCost: 手工费（消耗手工费总和）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>员工业绩统计数据</returns>
        /// <response code="200">成功返回统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("get-employee-performance-statistics")]
        public async Task<object> GetEmployeePerformanceStatistics(EmployeePerformanceStatisticsInput input)
        {
            try
            {
                if (input == null || string.IsNullOrEmpty(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
                {
                    throw NCCException.Oh("统计月份格式错误，请使用YYYYMM格式");
                }

                var statisticsMonth = input.StatisticsMonth;

                // 分步统计，确保效率和可维护性
                // 1. 拓客人数统计
                var invitationCount = await GetInvitationCount(input.UserId, statisticsMonth);

                // 2. 邀约人数统计
                var inviteCount = await GetInviteCount(input.UserId, statisticsMonth);

                // 3. 预约人数统计
                var appointmentCount = await GetAppointmentCount(input.UserId, statisticsMonth);

                // 4. 开单统计（数量、金额）
                var billingStats = await GetBillingStats(input.UserId, statisticsMonth);

                // 5. 消耗统计（数量、金额）
                var consumeStats = await GetConsumeStats(input.UserId, statisticsMonth);

                // 6. 退卡统计（数量、金额）
                var refundStats = await GetRefundStats(input.UserId, statisticsMonth);

                // 7. 人头统计
                var headCount = await GetHeadCount(input.UserId, statisticsMonth);

                // 8. 人次统计
                var personCount = await GetPersonCount(input.UserId, statisticsMonth);

                // 9. 开单项目数统计
                var billingProjectCount = await GetBillingProjectCount(input.UserId, statisticsMonth);

                // 10. 消耗项目数统计（包含总项目数、原始项目数、加班项目数、陪同项目数）
                var consumeProjectStats = await GetConsumeProjectCountDetails(input.UserId, statisticsMonth);

                // 11. 手工费统计
                var laborCost = await GetLaborCost(input.UserId, statisticsMonth);

                return new EmployeePerformanceStatisticsOutput
                {
                    UserId = input.UserId,
                    StatisticsMonth = statisticsMonth,
                    InvitationCount = invitationCount,
                    InviteCount = inviteCount,
                    AppointmentCount = appointmentCount,
                    BillingCount = billingStats.Count,
                    BillingAmount = billingStats.Amount,
                    ConsumeCount = consumeStats.Count,
                    ConsumeAmount = consumeStats.Amount,
                    RefundCount = refundStats.Count,
                    RefundAmount = refundStats.Amount,
                    HeadCount = headCount,
                    PersonCount = personCount,
                    BillingProjectCount = billingProjectCount,
                    ConsumeProjectCount = consumeProjectStats.TotalCount,
                    ConsumeOriginalProjectCount = consumeProjectStats.OriginalCount,
                    ConsumeOvertimeProjectCount = consumeProjectStats.OvertimeCount,
                    ConsumeAccompaniedProjectCount = consumeProjectStats.AccompaniedCount,
                    LaborCost = laborCost
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"获取员工业绩统计数据失败 - 员工ID: {input?.UserId}, 月份: {input?.StatisticsMonth}");
                throw NCCException.Oh($"获取员工业绩统计数据失败: {ex.Message}");
            }
        }

        /// <summary>
        /// 统计拓客人数
        /// </summary>
        private async Task<int> GetInvitationCount(string userId, string month)
        {
            var sql = $@"
                SELECT COUNT(*) as Count
                FROM lq_tkjlb
                WHERE F_ExpansionUserId = '{userId}'
                    AND DATE_FORMAT(F_ExpansionTime, '%Y%m') = '{month}'";
            var result = await _db.Ado.SqlQueryAsync<dynamic>(sql);
            return Convert.ToInt32(result.FirstOrDefault()?.Count ?? 0);
        }

        /// <summary>
        /// 统计邀约人数
        /// </summary>
        private async Task<int> GetInviteCount(string userId, string month)
        {
            var sql = $@"
                SELECT COUNT(DISTINCT yykh) as Count
                FROM lq_yaoyjl
                WHERE yyr = '{userId}'
                    AND DATE_FORMAT(yysj, '%Y%m') = '{month}'";
            var result = await _db.Ado.SqlQueryAsync<dynamic>(sql);
            return Convert.ToInt32(result.FirstOrDefault()?.Count ?? 0);
        }

        /// <summary>
        /// 统计预约人数
        /// </summary>
        private async Task<int> GetAppointmentCount(string userId, string month)
        {
            var sql = $@"
                SELECT COUNT(DISTINCT gk) as Count
                FROM lq_yyjl
                WHERE yyr = '{userId}'
                    AND DATE_FORMAT(F_CreateTime, '%Y%m') = '{month}'";
            var result = await _db.Ado.SqlQueryAsync<dynamic>(sql);
            return Convert.ToInt32(result.FirstOrDefault()?.Count ?? 0);
        }

        /// <summary>
        /// 统计开单（数量和金额）
        /// </summary>
        private async Task<(int Count, decimal Amount)> GetBillingStats(string userId, string month)
        {
            var sql = $@"
                SELECT
                    COUNT(*) as Count,
                    COALESCE(SUM(jksyj), 0) as Amount
                FROM lq_kd_jksyj
                WHERE jkszh = '{userId}'
                    AND F_IsEffective = 1
                    AND DATE_FORMAT(yjsj, '%Y%m') = '{month}'";
            var result = await _db.Ado.SqlQueryAsync<dynamic>(sql);
            var data = result.FirstOrDefault();
            return (Convert.ToInt32(data?.Count ?? 0), Convert.ToDecimal(data?.Amount ?? 0));
        }

        /// <summary>
        /// 统计消耗（数量和金额）
        /// </summary>
        private async Task<(int Count, decimal Amount)> GetConsumeStats(string userId, string month)
        {
            // 统计消耗单数（消耗主表的去重记录数）和消耗金额（健康师业绩总和）
            var sql = $@"
                SELECT
                    COUNT(DISTINCT hyhk.F_Id) as Count,
                    COALESCE(SUM(jksyj.jksyj), 0) as Amount
                FROM lq_xh_jksyj jksyj
                INNER JOIN lq_xh_hyhk hyhk ON jksyj.glkdbh = hyhk.F_Id
                WHERE jksyj.jkszh = '{userId}'
                    AND jksyj.F_IsEffective = 1
                    AND hyhk.F_IsEffective = 1
                    AND DATE_FORMAT(hyhk.hksj, '%Y%m') = '{month}'";
            var result = await _db.Ado.SqlQueryAsync<dynamic>(sql);
            var data = result.FirstOrDefault();
            return (Convert.ToInt32(data?.Count ?? 0), Convert.ToDecimal(data?.Amount ?? 0));
        }

        /// <summary>
        /// 统计退卡（数量和金额）
        /// </summary>
        private async Task<(int Count, decimal Amount)> GetRefundStats(string userId, string month)
        {
            // 统计退卡单数（退卡主表的去重记录数）和退卡金额（健康师业绩总和）
            var sql = $@"
                SELECT
                    COUNT(DISTINCT hytk.F_Id) as Count,
                    COALESCE(SUM(jksyj.jksyj), 0) as Amount
                FROM lq_hytk_jksyj jksyj
                INNER JOIN lq_hytk_hytk hytk ON jksyj.gltkbh = hytk.F_Id
                WHERE jksyj.jkszh = '{userId}'
                    AND jksyj.F_IsEffective = 1
                    AND hytk.F_IsEffective = 1
                    AND DATE_FORMAT(hytk.tksj, '%Y%m') = '{month}'";
            var result = await _db.Ado.SqlQueryAsync<dynamic>(sql);
            var data = result.FirstOrDefault();
            return (Convert.ToInt32(data?.Count ?? 0), Convert.ToDecimal(data?.Amount ?? 0));
        }

        /// <summary>
        /// 统计人头（从人次记录表统计，按健康师+月份+客户+数量去重后累加）
        /// </summary>
        private async Task<int> GetHeadCount(string userId, string month)
        {
            // 按健康师+月份+客户+数量去重，然后累加数量
            // 注意：userId 和 month 都是内部参数，相对安全
            var sql = $@"
                SELECT COALESCE(SUM(F_Quantity), 0) as Count
                FROM (
                    SELECT
                        F_PersonId,
                        F_WorkMonth,
                        F_MemberId,
                        F_Quantity
                    FROM lq_person_times_record
                    WHERE F_PersonId = '{userId}'
                        AND F_PersonType = '健康师'
                        AND F_WorkMonth = '{month}'
                        AND F_IsEffective = 1
                        AND F_HasBilling = 1
                    GROUP BY F_PersonId, F_WorkMonth, F_MemberId, F_Quantity
                ) as distinct_records";
            var result = await _db.Ado.SqlQueryAsync<dynamic>(sql);
            var count = result.FirstOrDefault()?.Count;
            return count != null ? Convert.ToInt32(count) : 0;
        }

        /// <summary>
        /// 统计人次（从人次记录表统计，按健康师+日期+客户+数量去重后累加）
        /// </summary>
        private async Task<int> GetPersonCount(string userId, string month)
        {
            // 按健康师+日期+客户+数量去重，然后累加数量
            // 注意：userId 和 month 都是内部参数，相对安全
            var sql = $@"
                SELECT COALESCE(SUM(F_Quantity), 0) as Count
                FROM (
                    SELECT
                        F_PersonId,
                        F_WorkDate,
                        F_MemberId,
                        F_Quantity
                    FROM lq_person_times_record
                    WHERE F_PersonId = '{userId}'
                        AND F_PersonType = '健康师'
                        AND F_WorkMonth = '{month}'
                        AND F_IsEffective = 1
                        AND F_HasBilling = 1
                    GROUP BY F_PersonId, F_WorkDate, F_MemberId, F_Quantity
                ) as distinct_records";
            var result = await _db.Ado.SqlQueryAsync<dynamic>(sql);
            var count = result.FirstOrDefault()?.Count;
            return count != null ? Convert.ToInt32(count) : 0;
        }

        /// <summary>
        /// 统计开单项目数（项目次数总和）
        /// </summary>
        private async Task<int> GetBillingProjectCount(string userId, string month)
        {
            var sql = $@"
                SELECT COALESCE(SUM(pxmx.F_ProjectNumber), 0) as Count
                FROM lq_kd_jksyj jksyj
                INNER JOIN lq_kd_pxmx pxmx ON jksyj.F_kdpxid = pxmx.F_Id
                WHERE jksyj.jks = '{userId}'
                    AND jksyj.F_IsEffective = 1
                    AND DATE_FORMAT(jksyj.yjsj, '%Y%m') = '{month}'";
            var result = await _db.Ado.SqlQueryAsync<dynamic>(sql);
            return Convert.ToInt32(result.FirstOrDefault()?.Count ?? 0);
        }

        /// <summary>
        /// 统计消耗项目数（项目次数总和）
        /// </summary>
        private async Task<int> GetConsumeProjectCount(string userId, string month)
        {
            var sql = $@"
                SELECT COALESCE(SUM(pxmx.F_ProjectNumber), 0) as Count
                FROM lq_xh_jksyj jksyj
                INNER JOIN lq_xh_hyhk hyhk ON jksyj.glkdbh = hyhk.F_Id
                INNER JOIN lq_xh_pxmx pxmx ON pxmx.F_ConsumeInfoId = hyhk.F_Id
                WHERE jksyj.jkszh = '{userId}'
                    AND jksyj.F_IsEffective = 1
                    AND hyhk.F_IsEffective = 1
                    AND DATE_FORMAT(hyhk.hksj, '%Y%m') = '{month}'";
            var result = await _db.Ado.SqlQueryAsync<dynamic>(sql);
            return Convert.ToInt32(result.FirstOrDefault()?.Count ?? 0);
        }

        /// <summary>
        /// 统计消耗项目数详情（包含总项目数、原始项目数、加班项目数、陪同项目数）
        /// </summary>
        private async Task<(int TotalCount, decimal OriginalCount, decimal OvertimeCount, decimal AccompaniedCount)> GetConsumeProjectCountDetails(string userId, string month)
        {
            // 直接从健康师业绩表统计项目数，使用业绩时间字段过滤，不需要JOIN其他表
            var sql = $@"
                SELECT
                    COALESCE(SUM(jksyj.F_kdpxNumber), 0) as TotalCount,
                    COALESCE(SUM(COALESCE(jksyj.F_OriginalKdpxNumber, jksyj.F_kdpxNumber)), 0) as OriginalCount,
                    COALESCE(SUM(COALESCE(jksyj.F_OvertimeKdpxNumber, 0)), 0) as OvertimeCount,
                    COALESCE(SUM(COALESCE(jksyj.F_AccompaniedProjectNumber, 0)), 0) as AccompaniedCount
                FROM lq_xh_jksyj jksyj
                WHERE jksyj.jkszh = '{userId}'
                    AND jksyj.F_IsEffective = 1
                    AND DATE_FORMAT(jksyj.yjsj, '%Y%m') = '{month}'";
            var result = await _db.Ado.SqlQueryAsync<dynamic>(sql);
            var data = result.FirstOrDefault();
            return (
                Convert.ToInt32(data?.TotalCount ?? 0),
                Convert.ToDecimal(data?.OriginalCount ?? 0),
                Convert.ToDecimal(data?.OvertimeCount ?? 0),
                Convert.ToDecimal(data?.AccompaniedCount ?? 0)
            );
        }

        /// <summary>
        /// 统计手工费（消耗手工费总和）
        /// </summary>
        private async Task<decimal> GetLaborCost(string userId, string month)
        {
            var sql = $@"
                SELECT COALESCE(SUM(jksyj.F_LaborCost), 0) as LaborCost
                FROM lq_xh_jksyj jksyj
                INNER JOIN lq_xh_hyhk hyhk ON jksyj.glkdbh = hyhk.F_Id
                WHERE jksyj.jkszh = '{userId}'
                    AND jksyj.F_IsEffective = 1
                    AND hyhk.F_IsEffective = 1
                    AND DATE_FORMAT(hyhk.hksj, '%Y%m') = '{month}'";
            var result = await _db.Ado.SqlQueryAsync<dynamic>(sql);
            return Convert.ToDecimal(result.FirstOrDefault()?.LaborCost ?? 0);
        }
        #endregion

        #region 科技部老师业绩统计
        /// <summary>
        /// 获取科技部老师业绩统计
        /// </summary>
        /// <remarks>
        /// 统计科技部老师的开单业绩、消耗业绩、手工费等相关数据
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "startDate": "2025-01-01T00:00:00",
        ///   "endDate": "2025-01-31T23:59:59",
        ///   "teacherId": "科技部老师ID（可选）",
        ///   "teacherName": "科技部老师姓名（可选）"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - startDate: 开始日期（可选）
        /// - endDate: 结束日期（可选）
        /// - teacherId: 科技部老师ID（可选）
        /// - teacherName: 科技部老师姓名（可选）
        /// 
        /// 返回数据说明：
        /// - DepartmentName: 部门名称（固定为"科技部"）
        /// - TeacherName: 老师姓名
        /// - ConsumeProjectCount: 消耗项目数
        /// - ConsumeAchievement: 消耗业绩
        /// - OrderAchievement: 开单业绩（开卡业绩）
        /// - ConsumeItemCount: 耗卡品项次数
        /// - ConsumeLaborCost: 消耗手工费（耗卡手工费）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>科技部老师业绩统计结果列表，包含开单业绩、消耗业绩、手工费等数据</returns>
        /// <response code="200">成功返回统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("GetTechTeacherStatistics")]
        public async Task<List<TechTeacherSimpleStatisticsOutput>> GetTechTeacherStatistics(TechTeacherStatisticsInput input)
        {
            TechTeacherSimpleStatisticsOutput result = new TechTeacherSimpleStatisticsOutput
            {
                DepartmentName = "科技部",
                TeacherName = "",
                OrderAchievement = 0m,
                ConsumeAchievement = 0m,
                ConsumeItemCount = 0,
                ConsumeProjectCount = 0,
                ConsumeLaborCost = 0m,
                RefundAchievement = 0m,
            };
            try
            {
                // 1. 验证必须传入科技老师ID
                if (string.IsNullOrEmpty(input.TeacherId))
                {
                    return new List<TechTeacherSimpleStatisticsOutput> { result };
                }

                // 2. 获取科技老师信息
                var teacher = await _db.Queryable<UserEntity>().Where(x => x.Gw == "科技老师" && x.Id == input.TeacherId)
                .Select(x => new
                {
                    TeacherId = x.Id,
                    TeacherName = x.RealName,
                    TeacherAccount = x.Account,
                }).FirstAsync();

                if (teacher == null)
                {
                    return new List<TechTeacherSimpleStatisticsOutput> { result };
                }
                // 3. 查询开单业绩（从 lq_kd_kjbsyj 表）
                var OrderAchievement = await _db.Queryable<LqKdKjbsyjEntity>().Where(x => x.Kjbls == teacher.TeacherId && x.IsEffective == StatusEnum.有效.GetHashCode() && x.Yjsj >= input.StartDate.Value && x.Yjsj <= input.EndDate.Value).SumAsync(x => x.Kjblsyj);

                // 4. 查询耗卡业绩（从 lq_xh_kjbsyj 关联 lq_xh_hyhk 和 lq_xh_pxmx）
                var consumeQuery = _db.Queryable<LqXhKjbsyjEntity, LqXhHyhkEntity, LqXhPxmxEntity>((kjbsyj, hyhk, pxmx) => kjbsyj.Glkdbh == hyhk.Id && kjbsyj.Hkpxid == pxmx.Id)
                    .Where((kjbsyj, hyhk, pxmx) => kjbsyj.IsEffective == StatusEnum.有效.GetHashCode())
                    .Where((kjbsyj, hyhk, pxmx) => hyhk.IsEffective == StatusEnum.有效.GetHashCode())
                    .Where((kjbsyj, hyhk, pxmx) => kjbsyj.Kjblszh == teacher.TeacherAccount);

                // 日期过滤
                if (input.StartDate.HasValue)
                {
                    consumeQuery = consumeQuery.Where((kjbsyj, hyhk, pxmx) => kjbsyj.Yjsj >= input.StartDate.Value);
                }

                if (input.EndDate.HasValue)
                {
                    consumeQuery = consumeQuery.Where((kjbsyj, hyhk, pxmx) => kjbsyj.Yjsj <= input.EndDate.Value);
                }

                var consumeStats = await consumeQuery
                    .Select((kjbsyj, hyhk, pxmx) => new
                    {
                        ConsumeAchievement = kjbsyj.Kjblsyj,
                        ConsumeItemCount = SqlFunc.ToInt32(pxmx.ProjectNumber),
                        ConsumeProjectCount = SqlFunc.ToInt32(kjbsyj.HdpxNumber),
                        ConsumeLaborCost = kjbsyj.LaborCost,
                    })
                    .ToListAsync();
                // 5. 查询退卡业绩（从 lq_hytk_kjbsyj 表）
                var RefundAchievement = await _db.Queryable<LqHytkKjbsyjEntity>().Where(x => x.Kjbls == teacher.TeacherId && x.IsEffective == StatusEnum.有效.GetHashCode() && x.Tksj >= input.StartDate.Value && x.Tksj <= input.EndDate.Value).SumAsync(x => x.Kjblsyj);
                // 6. 统计并返回结果
                result.TeacherName = teacher.TeacherName;
                result.OrderAchievement = OrderAchievement.ToDecimal();
                result.ConsumeAchievement = consumeStats.Sum(x => x.ConsumeAchievement ?? 0m);
                result.ConsumeItemCount = consumeStats.Sum(x => x.ConsumeItemCount);
                result.ConsumeProjectCount = consumeStats.Sum(x => x.ConsumeProjectCount);
                result.ConsumeLaborCost = consumeStats.Sum(x => x.ConsumeLaborCost ?? 0m);
                result.RefundAchievement = RefundAchievement.ToDecimal();
                return new List<TechTeacherSimpleStatisticsOutput> { result };
            }
            catch (Exception)
            {
                return new List<TechTeacherSimpleStatisticsOutput> { result };
            }
        }

        #endregion

        #region 只买了女神卡的会员统计
        /// <summary>
        /// 获取只买了女神卡的会员及其开单数据（分页）
        /// </summary>
        /// <remarks>
        /// 统计所有只购买了女神卡（品项编号为61）的会员，并返回这些会员的开单记录
        /// 
        /// 判断逻辑：
        /// 1. 会员必须有购买女神卡的记录
        /// 2. 该会员的所有有效开单记录中，所有品项都必须是女神卡（px = "61"）
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "PageIndex": 1,
        ///   "PageSize": 20,
        ///   "StoreId": "门店ID",
        ///   "StoreIds": ["门店ID1", "门店ID2"]
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - PageIndex: 当前页码（从1开始）
        /// - PageSize: 每页数量
        /// - StoreId: 门店ID（单个门店筛选，与StoreIds二选一）
        /// - StoreIds: 门店ID列表（支持多门店筛选，与StoreId二选一）
        /// 
        /// 返回说明：
        /// - list: 会员列表
        ///   - memberId: 会员ID
        ///   - memberName: 会员名称
        ///   - phone: 手机号
        ///   - storeId: 归属门店ID
        ///   - storeName: 归属门店名称
        ///   - billingList: 开单记录列表
        ///     - billingId: 开单编号
        ///     - billingDate: 开单日期
        ///     - actualPerformance: 实付业绩
        ///     - storeId: 门店ID
        ///     - storeName: 门店名称
        ///     - itemList: 品项列表
        ///       - itemId: 品项明细ID
        ///       - itemCode: 品项编号（如61）
        ///       - itemName: 品项名称（如女神卡）
        ///       - itemPrice: 品项价格
        ///       - sourceType: 来源类型
        ///       - projectNumber: 项目次数
        ///       - totalPrice: 总价
        ///       - actualPrice: 实付金额
        ///       - performanceTime: 业绩时间
        /// - pagination: 分页信息
        ///   - pageIndex: 当前页码
        ///   - pageSize: 每页数量
        ///   - total: 总记录数
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>只买了女神卡的会员列表（分页）</returns>
        /// <response code="200">成功返回会员列表</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("GetGoddessCardMembers")]
        public async Task<dynamic> GetGoddessCardMembers([FromBody] GoddessCardMemberListQueryInput input)
        {
            try
            {
                // 构建门店筛选条件
                var storeFilterConditions = new List<string>();
                var parameters = new List<SugarParameter>();

                if (!string.IsNullOrEmpty(input.StoreId))
                {
                    storeFilterConditions.Add("kd1.djmd = @StoreId");
                    parameters.Add(new SugarParameter("@StoreId", input.StoreId));
                }
                else if (input.StoreIds != null && input.StoreIds.Any())
                {
                    var storeIdParams = string.Join(",", input.StoreIds.Select((_, i) => $"@StoreId{i}"));
                    storeFilterConditions.Add($"kd1.djmd IN ({storeIdParams})");
                    for (int i = 0; i < input.StoreIds.Count; i++)
                    {
                        parameters.Add(new SugarParameter($"@StoreId{i}", input.StoreIds[i]));
                    }
                }

                var storeFilterClause = storeFilterConditions.Any() ? "AND " + string.Join(" AND ", storeFilterConditions) : "";

                // 构建来源类型筛选条件
                var sourceTypeFilterConditions = new List<string>();
                if (!string.IsNullOrEmpty(input.SourceType))
                {
                    sourceTypeFilterConditions.Add("pxmx1.F_SourceType = @SourceType");
                    parameters.Add(new SugarParameter("@SourceType", input.SourceType));
                }
                else if (input.SourceTypes != null && input.SourceTypes.Any())
                {
                    var sourceTypeParams = string.Join(",", input.SourceTypes.Select((_, i) => $"@SourceType{i}"));
                    sourceTypeFilterConditions.Add($"pxmx1.F_SourceType IN ({sourceTypeParams})");
                    for (int i = 0; i < input.SourceTypes.Count; i++)
                    {
                        parameters.Add(new SugarParameter($"@SourceType{i}", input.SourceTypes[i]));
                    }
                }

                var sourceTypeFilterClause = sourceTypeFilterConditions.Any() ? "AND " + string.Join(" AND ", sourceTypeFilterConditions) : "";

                // 使用SQL一次性查询出符合条件的会员ID
                // 逻辑：会员有购买女神卡的记录，且该会员的所有开单记录的所有品项都是女神卡
                var sql = $@"
                    SELECT DISTINCT kd1.Kdhy AS MemberId
                    FROM lq_kd_pxmx pxmx1
                    INNER JOIN lq_kd_kdjlb kd1 ON pxmx1.glkdbh = kd1.F_Id
                    WHERE pxmx1.px = '61'
                        AND pxmx1.F_IsEffective = 1
                        AND kd1.F_IsEffective = 1
                        AND kd1.Kdhy IS NOT NULL
                        AND kd1.Kdhy != ''
                        {storeFilterClause}
                        {sourceTypeFilterClause}
                        AND NOT EXISTS (
                            -- 排除那些有非女神卡品项的会员
                            SELECT 1
                            FROM lq_kd_pxmx pxmx2
                            INNER JOIN lq_kd_kdjlb kd2 ON pxmx2.glkdbh = kd2.F_Id
                            WHERE kd2.Kdhy = kd1.Kdhy
                                AND pxmx2.F_IsEffective = 1
                                AND kd2.F_IsEffective = 1
                                AND pxmx2.px != '61'
                        )";
                var validMemberIds = await _db.Ado.SqlQueryAsync<string>(sql, parameters);

                if (!validMemberIds.Any())
                {
                    return new
                    {
                        list = new List<GoddessCardMemberOutput>(),
                        pagination = new
                        {
                            pageIndex = input.PageIndex,
                            pageSize = input.PageSize,
                            total = 0
                        }
                    };
                }

                // 分页处理
                var totalCount = validMemberIds.Count;
                var pagedMemberIds = validMemberIds
                    .Skip((input.PageIndex - 1) * input.PageSize)
                    .Take(input.PageSize)
                    .ToList();

                // 获取会员基本信息
                var members = await _db.Queryable<LqKhxxEntity>()
                    .Where(x => pagedMemberIds.Contains(x.Id))
                    .Select(x => new
                    {
                        x.Id,
                        x.Khmc,
                        x.Sjh,
                        x.Gsmd
                    })
                    .ToListAsync();

                // 获取门店信息
                var storeIds = members.Where(x => !string.IsNullOrEmpty(x.Gsmd)).Select(x => x.Gsmd).Distinct().ToList();
                var stores = new Dictionary<string, string>();
                if (storeIds.Any())
                {
                    var storeList = await _db.Queryable<LqMdxxEntity>()
                        .Where(x => storeIds.Contains(x.Id))
                        .Select(x => new { x.Id, x.Dm })
                        .ToListAsync();
                    stores = storeList.ToDictionary(x => x.Id, x => x.Dm ?? "");
                }

                // 获取所有开单记录
                var billings = await _db.Queryable<LqKdKdjlbEntity>().Where(x => pagedMemberIds.Contains(x.Kdhy) && x.IsEffective == StatusEnum.有效.GetHashCode()).ToListAsync();

                // 获取开单记录的门店信息
                var billingStoreIds = billings.Where(x => !string.IsNullOrEmpty(x.Djmd)).Select(x => x.Djmd).Distinct().ToList();
                var billingStores = new Dictionary<string, string>();
                if (billingStoreIds.Any())
                {
                    var billingStoreList = await _db.Queryable<LqMdxxEntity>()
                        .Where(x => billingStoreIds.Contains(x.Id))
                        .Select(x => new { x.Id, x.Dm })
                        .ToListAsync();
                    billingStores = billingStoreList.ToDictionary(x => x.Id, x => x.Dm ?? "");
                }

                // 获取所有开单的品项明细
                var billingIds = billings.Select(x => x.Id).ToList();
                var billingItems = await _db.Queryable<LqKdPxmxEntity>()
                    .Where(x => billingIds.Contains(x.Glkdbh) && x.IsEffective == StatusEnum.有效.GetHashCode())
                    .ToListAsync();

                // 按开单编号分组品项
                var itemsByBilling = billingItems
                    .GroupBy(x => x.Glkdbh)
                    .ToDictionary(g => g.Key, g => g.ToList());

                // 组装返回数据
                var result = members.Select(member =>
                {
                    var memberBillings = billings.Where(x => x.Kdhy == member.Id).ToList();

                    return new GoddessCardMemberOutput
                    {
                        memberId = member.Id,
                        memberName = member.Khmc ?? "",
                        phone = member.Sjh ?? "",
                        storeId = member.Gsmd ?? "",
                        storeName = !string.IsNullOrEmpty(member.Gsmd) && stores.ContainsKey(member.Gsmd) ? stores[member.Gsmd] : "",
                        billingList = memberBillings.Select(b =>
                        {
                            var hasItems = itemsByBilling.ContainsKey(b.Id);
                            var items = hasItems ? itemsByBilling[b.Id] : new List<LqKdPxmxEntity>();

                            return new GoddessCardBillingInfo
                            {
                                billingId = b.Id,
                                billingDate = b.Kdrq,
                                actualPerformance = b.Sfyj,
                                storeId = b.Djmd ?? "",
                                storeName = !string.IsNullOrEmpty(b.Djmd) && billingStores.ContainsKey(b.Djmd) ? billingStores[b.Djmd] : "",
                                itemList = items.Select(item => new GoddessCardBillingItemInfo
                                {
                                    itemId = item.Id ?? "",
                                    itemCode = item.Px ?? "",
                                    itemName = item.Pxmc ?? "",
                                    itemPrice = item.Pxjg,
                                    sourceType = item.SourceType ?? "",
                                    projectNumber = item.ProjectNumber,
                                    totalPrice = item.TotalPrice,
                                    actualPrice = item.ActualPrice,
                                    performanceTime = item.Yjsj
                                }).ToList()
                            };
                        }).ToList()
                    };
                }).ToList();

                return new
                {
                    list = result,
                    pagination = new
                    {
                        pageIndex = input.PageIndex,
                        pageSize = input.PageSize,
                        total = totalCount
                    }
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取只买了女神卡的会员数据时发生错误");
                throw NCCException.Oh($"获取数据失败：{ex.Message}");
            }
        }
        #endregion

        #region 线索池客户统计报表
        /// <summary>
        /// 获取线索池客户统计报表
        /// </summary>
        /// <remarks>
        /// 根据拓客记录统计线索池客户的邀约、预约、消耗、开单等信息
        /// 
        /// 业务链路：拓客 -> 邀约 -> 预约 -> 开单/消耗
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "pageIndex": 1,
        ///   "pageSize": 20,
        ///   "startTime": "2025-10-01T00:00:00",
        ///   "endTime": "2025-10-31T23:59:59",
        ///   "storeIds": ["store1", "store2"],
        ///   "eventId": "event123"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - pageIndex: 页码，从1开始
        /// - pageSize: 每页数量
        /// - startTime: 拓客时间范围开始时间
        /// - endTime: 拓客时间范围结束时间
        /// - storeIds: 门店ID列表，可传多个
        /// - eventId: 拓客活动ID
        /// 
        /// 返回数据说明：
        /// - LeadCustomerId: 线索池客户（拓客编号）
        /// - CustomerName: 客户姓名
        /// - ExpansionTime: 拓客时间
        /// - StoreId: 归属门店ID（从客户信息表获取）
        /// - StoreName: 归属门店名称
        /// - Phone: 电话（优先使用拓客记录的电话，如果没有则使用客户信息表的电话）
        /// - ExpansionUserId: 拓客人员ID
        /// - ExpansionUserName: 拓客人员姓名
        /// - HasInvite: 是否邀约（是/否），通过拓客编号关联邀约表
        /// - HasAppointment: 是否预约（是/否），只统计通过邀约产生的预约（预约表的F_InviteId关联邀约表）
        /// - HasConsume: 是否有消耗（是/否），只统计通过预约产生的耗卡（耗卡表的F_AppointmentId关联预约表）
        /// - HasBilling: 是否开单（是/否），只统计通过预约产生的开单（开单表的F_AppointmentId关联预约表）
        /// - NoBillingReason: 未开单原因，从预约记录的F_NoDealRemark字段获取
        /// - BillingAmount: 开卡金额，汇总通过预约产生的开单记录的整单业绩（zdyj）
        /// - BillingItems: 开卡卡项，汇总通过预约产生的开单品项名称，多个用顿号分隔
        /// - ActualAppointmentCount: 实际预约记录数（不管是否通过邀约产生），用于问题分析
        /// - ActualConsumeCount: 实际消耗记录数（不管是否通过预约产生），用于问题分析
        /// - ActualBillingCount: 实际开单记录数（不管是否通过预约产生），用于问题分析
        /// - Analysis: 问题分析说明，自动分析数据异常情况，如：有预约记录但未通过邀约产生、有消耗记录但未通过预约产生等
        /// 
        /// 返回示例：
        /// ```json
        /// {
        ///   "list": [
        ///     {
        ///       "LeadCustomerId": "751248448816153862",
        ///       "CustomerName": "王女士",
        ///       "ExpansionTime": "2025-10-24T03:33:10.000Z",
        ///       "StoreId": "1649328471923847169",
        ///       "StoreName": "绿纤紫荆店",
        ///       "Phone": "13800138000",
        ///       "ExpansionUserId": "123456789",
        ///       "ExpansionUserName": "张三",
        ///       "HasInvite": "否",
        ///       "HasAppointment": "否",
        ///       "HasConsume": "否",
        ///       "HasBilling": "否",
        ///       "NoBillingReason": null,
        ///       "BillingAmount": 0,
        ///       "BillingItems": null,
        ///       "ActualAppointmentCount": 3,
        ///       "ActualConsumeCount": 4,
        ///       "ActualBillingCount": 5,
        ///       "Analysis": "有3条预约记录，但未通过邀约产生（F_InviteId为null）；有4条消耗记录，但未通过预约产生（F_AppointmentId为null或预约未通过邀约产生）；有5条开单记录，但未通过预约产生（F_AppointmentId为null或预约未通过邀约产生）"
        ///     }
        ///   ],
        ///   "pagination": {
        ///     "pageIndex": 1,
        ///     "pageSize": 20,
        ///     "total": 1511
        ///   }
        /// }
        /// ```
        /// </remarks>
        /// <param name="input">查询条件</param>
        /// <returns>线索池客户统计报表列表，包含统计数据和问题分析</returns>
        /// <response code="200">查询成功，返回统计报表列表和分页信息</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("get-lead-customer-statistics-list")]
        public async Task<dynamic> GetLeadCustomerStatisticsList([FromBody] LeadCustomerStatisticsListQueryInput input)
        {
            try
            {
                // 构建WHERE条件
                var whereConditions = new List<string>();
                var parameters = new List<SugarParameter>();

                if (input.StartTime.HasValue)
                {
                    whereConditions.Add("tk.F_ExpansionTime >= @StartTime");
                    parameters.Add(new SugarParameter("@StartTime", input.StartTime.Value));
                }

                if (input.EndTime.HasValue)
                {
                    whereConditions.Add("tk.F_ExpansionTime <= @EndTime");
                    parameters.Add(new SugarParameter("@EndTime", input.EndTime.Value));
                }

                if (input.StoreIds != null && input.StoreIds.Any())
                {
                    var storeIdParams = string.Join(",", input.StoreIds.Select((_, i) => $"@StoreId{i}"));
                    whereConditions.Add($"tk.F_StoreId IN ({storeIdParams})");
                    for (int i = 0; i < input.StoreIds.Count; i++)
                    {
                        parameters.Add(new SugarParameter($"@StoreId{i}", input.StoreIds[i]));
                    }
                }

                if (!string.IsNullOrEmpty(input.EventId))
                {
                    whereConditions.Add("tk.F_EventId = @EventId");
                    parameters.Add(new SugarParameter("@EventId", input.EventId));
                }

                var whereClause = whereConditions.Any() ? "WHERE " + string.Join(" AND ", whereConditions) : "";

                // 使用子查询优化性能，避免复杂的JOIN和GROUP BY
                // 注意：邀约、预约、消耗、开单表存储的是会员ID（F_MemberId），不是线索池ID（F_Id）
                // 需要通过线索池的F_MemberId去关联这些表
                var sql = $@"
                    SELECT
                        tk.F_Id as LeadCustomerId,
                        tk.F_CustomerName as CustomerName,
                        tk.F_ExpansionTime as ExpansionTime,
                        -- 归属门店ID（从客户信息表获取）
                        COALESCE(kh.gsmd, '') as StoreId,
                        -- 归属门店名称
                        COALESCE(md.dm, '') as StoreName,
                        -- 电话（优先使用拓客记录的电话，如果没有则使用客户信息表的电话）
                        COALESCE(NULLIF(tk.F_CustomerPhone, ''), kh.sjh, '') as Phone,
                        -- 拓客人员ID
                        COALESCE(tk.F_ExpansionUserId, '') as ExpansionUserId,
                        -- 拓客人员姓名
                        COALESCE(usr.F_REALNAME, '') as ExpansionUserName,
                        -- 是否邀约：通过会员ID关联（邀约表的yykh字段存储的是会员ID）
                        CASE WHEN yaoy_stats.has_invite = 1 THEN '是' ELSE '否' END as HasInvite,
                        -- 是否预约：通过邀约ID关联（只统计通过邀约产生的预约）
                        CASE WHEN yy_stats.has_appointment = 1 THEN '是' ELSE '否' END as HasAppointment,
                        -- 是否有消耗：通过预约ID关联（只统计通过预约产生的耗卡）
                        CASE WHEN xh_stats.has_consume = 1 THEN '是' ELSE '否' END as HasConsume,
                        -- 是否开单：通过预约ID关联（只统计通过预约产生的开单）
                        CASE WHEN kd_stats.has_billing = 1 THEN '是' ELSE '否' END as HasBilling,
                        -- 未开单原因：从预约记录中获取（只取通过邀约产生的预约）
                        yy_stats.no_billing_reason as NoBillingReason,
                        -- 开卡金额：汇总通过预约产生的开单记录
                        COALESCE(kd_stats.billing_amount, 0) as BillingAmount,
                        -- 开卡卡项：汇总通过预约产生的开单品项
                        kd_stats.billing_items as BillingItems,
                        -- 实际预约记录数（不管是否通过邀约产生）
                        COALESCE(yy_actual.count, 0) as ActualAppointmentCount,
                        -- 实际消耗记录数（不管是否通过预约产生）
                        COALESCE(xh_actual.count, 0) as ActualConsumeCount,
                        -- 实际开单记录数（不管是否通过预约产生）
                        COALESCE(kd_actual.count, 0) as ActualBillingCount
                    FROM lq_tkjlb tk
                    -- 关联客户信息表（获取归属门店和电话）
                    LEFT JOIN lq_khxx kh ON kh.F_Id = tk.F_MemberId
                    -- 关联门店表（获取归属门店名称）
                    LEFT JOIN lq_mdxx md ON md.F_Id = kh.gsmd
                    -- 关联用户表（获取拓客人员姓名）
                    LEFT JOIN BASE_USER usr ON usr.F_Id = tk.F_ExpansionUserId AND usr.F_DeleteMark IS NULL
                    -- 邀约统计子查询（通过会员ID关联：邀约表的yykh字段存储的是会员ID）
                    LEFT JOIN (
                        SELECT
                            yaoy.yykh as member_id,
                            1 as has_invite
                        FROM lq_yaoyjl yaoy
                        WHERE yaoy.yykh IS NOT NULL
                        GROUP BY yaoy.yykh
                    ) yaoy_stats ON yaoy_stats.member_id = tk.F_MemberId
                    -- 预约统计子查询（只统计通过邀约产生的预约）
                    -- 通过会员ID关联：线索池 -> 邀约（通过会员ID） -> 预约（通过邀约ID，且会员ID匹配）
                    LEFT JOIN (
                        SELECT
                            tk_inner.F_MemberId as member_id,
                            1 as has_appointment,
                            MAX(yy.F_NoDealRemark) as no_billing_reason
                        FROM lq_tkjlb tk_inner
                        INNER JOIN lq_yaoyjl yaoy ON yaoy.yykh = tk_inner.F_MemberId
                        INNER JOIN lq_yyjl yy ON yy.F_InviteId = yaoy.F_Id AND yy.gk = tk_inner.F_MemberId
                        GROUP BY tk_inner.F_MemberId
                    ) yy_stats ON yy_stats.member_id = tk.F_MemberId
                    -- 消耗统计子查询（只统计通过预约产生的耗卡）
                    -- 通过会员ID关联：线索池 -> 邀约 -> 预约 -> 消耗（通过预约ID，且会员ID匹配）
                    LEFT JOIN (
                        SELECT
                            tk_inner.F_MemberId as member_id,
                            1 as has_consume
                        FROM lq_tkjlb tk_inner
                        INNER JOIN lq_yaoyjl yaoy ON yaoy.yykh = tk_inner.F_MemberId
                        INNER JOIN lq_yyjl yy ON yy.F_InviteId = yaoy.F_Id AND yy.gk = tk_inner.F_MemberId
                        INNER JOIN lq_xh_hyhk xh ON xh.F_AppointmentId = yy.F_Id AND xh.hy = tk_inner.F_MemberId AND xh.F_IsEffective = 1
                        GROUP BY tk_inner.F_MemberId
                    ) xh_stats ON xh_stats.member_id = tk.F_MemberId
                    -- 开单统计子查询（只统计通过预约产生的开单，包含金额和品项）
                    -- 通过会员ID关联：线索池 -> 邀约 -> 预约 -> 开单（通过预约ID，且会员ID匹配）
                    -- 注意：剔除品项ID是61且金额是0的记录
                    LEFT JOIN (
                        SELECT
                            tk_inner.F_MemberId as member_id,
                            1 as has_billing,
                            SUM(kd.zdyj) as billing_amount,
                            GROUP_CONCAT(DISTINCT kdpx.pxmc SEPARATOR '、') as billing_items
                        FROM lq_tkjlb tk_inner
                        INNER JOIN lq_yaoyjl yaoy ON yaoy.yykh = tk_inner.F_MemberId
                        INNER JOIN lq_yyjl yy ON yy.F_InviteId = yaoy.F_Id AND yy.gk = tk_inner.F_MemberId
                        INNER JOIN lq_kd_kdjlb kd ON kd.F_AppointmentId = yy.F_Id AND kd.kdhy = tk_inner.F_MemberId AND kd.F_IsEffective = 1
                        INNER JOIN (
                            SELECT DISTINCT glkdbh
                            FROM lq_kd_pxmx
                            WHERE F_IsEffective = 1
                                AND NOT (px = '61' AND (COALESCE(F_ActualPrice, 0) = 0 AND COALESCE(F_TotalPrice, 0) = 0))
                        ) valid_px ON valid_px.glkdbh = kd.F_Id
                        LEFT JOIN lq_kd_pxmx kdpx ON kdpx.glkdbh = kd.F_Id AND kdpx.F_IsEffective = 1
                            AND NOT (kdpx.px = '61' AND (COALESCE(kdpx.F_ActualPrice, 0) = 0 AND COALESCE(kdpx.F_TotalPrice, 0) = 0))
                        GROUP BY tk_inner.F_MemberId
                    ) kd_stats ON kd_stats.member_id = tk.F_MemberId
                    -- 实际预约记录数统计（不管是否通过邀约产生）
                    LEFT JOIN (
                        SELECT
                            yy.gk as member_id,
                            COUNT(*) as count
                        FROM lq_yyjl yy
                        GROUP BY yy.gk
                    ) yy_actual ON yy_actual.member_id = tk.F_MemberId
                    -- 实际消耗记录数统计（不管是否通过预约产生）
                    LEFT JOIN (
                        SELECT
                            xh.hy as member_id,
                            COUNT(*) as count
                        FROM lq_xh_hyhk xh
                        WHERE xh.F_IsEffective = 1
                        GROUP BY xh.hy
                    ) xh_actual ON xh_actual.member_id = tk.F_MemberId
                    -- 实际开单记录数统计（不管是否通过预约产生）
                    LEFT JOIN (
                        SELECT
                            kd.kdhy as member_id,
                            COUNT(*) as count
                        FROM lq_kd_kdjlb kd
                        WHERE kd.F_IsEffective = 1
                        GROUP BY kd.kdhy
                    ) kd_actual ON kd_actual.member_id = tk.F_MemberId
                    {whereClause}
                    ORDER BY tk.F_ExpansionTime DESC
                    LIMIT @PageSize OFFSET @Offset";
                parameters.Add(new SugarParameter("@PageSize", input.PageSize));
                parameters.Add(new SugarParameter("@Offset", (input.PageIndex - 1) * input.PageSize));

                // 查询总数
                var countSql = $@"
                    SELECT COUNT(*)
                    FROM lq_tkjlb tk
                    {whereClause}";
                var countParameters = parameters.Where(p => p.ParameterName != "@PageSize" && p.ParameterName != "@Offset").ToList();
                var totalCount = await _db.Ado.GetIntAsync(countSql, countParameters);

                // 执行查询
                var result = await _db.Ado.SqlQueryAsync<LeadCustomerStatisticsListOutput>(sql, parameters);

                // 生成问题分析说明
                // 完整链路1：邀约 -> 预约 -> 开单
                // 完整链路2：邀约 -> 预约 -> 消耗
                foreach (var item in result)
                {
                    var analysisList = new List<string>();
                    var completeChains = new List<string>();

                    // 判断是否形成完整链路
                    if (item.HasInvite == "是" && item.HasAppointment == "是")
                    {
                        if (item.HasBilling == "是")
                        {
                            completeChains.Add("邀约->预约->开单");
                        }
                        if (item.HasConsume == "是")
                        {
                            completeChains.Add("邀约->预约->消耗");
                        }
                    }

                    // 如果有完整链路，先说明链路完整
                    if (completeChains.Count > 0)
                    {
                        analysisList.Add($"✓ 链路完整：{string.Join("、", completeChains)}");
                    }

                    // 有预约记录，但未通过邀约产生
                    if (item.HasInvite == "否" && item.HasAppointment == "否" && item.ActualAppointmentCount > 0)
                    {
                        analysisList.Add($"有{item.ActualAppointmentCount}条预约记录，但未通过邀约产生（F_InviteId为null）");
                    }

                    // 有消耗记录，但未通过完整链路产生（邀约->预约->消耗）
                    if (item.HasAppointment == "否" && item.HasConsume == "否" && item.ActualConsumeCount > 0)
                    {
                        analysisList.Add($"有{item.ActualConsumeCount}条消耗记录，但未通过完整链路产生（邀约->预约->消耗）");
                    }

                    // 有开单记录，但未通过完整链路产生（邀约->预约->开单）
                    if (item.HasAppointment == "否" && item.HasBilling == "否" && item.ActualBillingCount > 0)
                    {
                        analysisList.Add($"有{item.ActualBillingCount}条开单记录，但未通过完整链路产生（邀约->预约->开单）");
                    }

                    // 有邀约记录，有预约记录，但预约记录的F_InviteId未关联到邀约记录
                    if (item.HasInvite == "是" && item.HasAppointment == "否" && item.ActualAppointmentCount > 0)
                    {
                        analysisList.Add($"有邀约记录，有{item.ActualAppointmentCount}条预约记录，但预约记录的F_InviteId未关联到邀约记录");
                    }

                    // 有预约记录（通过邀约产生），有消耗记录，但消耗记录的F_AppointmentId未关联到预约记录
                    if (item.HasAppointment == "是" && item.HasConsume == "否" && item.ActualConsumeCount > 0)
                    {
                        analysisList.Add($"有预约记录（通过邀约产生），有{item.ActualConsumeCount}条消耗记录，但消耗记录的F_AppointmentId未关联到预约记录，未形成完整链路（邀约->预约->消耗）");
                    }

                    // 有预约记录（通过邀约产生），有开单记录，但开单记录的F_AppointmentId未关联到预约记录
                    if (item.HasAppointment == "是" && item.HasBilling == "否" && item.ActualBillingCount > 0)
                    {
                        analysisList.Add($"有预约记录（通过邀约产生），有{item.ActualBillingCount}条开单记录，但开单记录的F_AppointmentId未关联到预约记录，未形成完整链路（邀约->预约->开单）");
                    }

                    // 生成最终分析说明
                    if (analysisList.Count == 0)
                    {
                        item.Analysis = "数据正常，符合业务链路：拓客 -> 邀约 -> 预约 -> 开单/消耗";
                    }
                    else
                    {
                        item.Analysis = string.Join("；", analysisList);
                    }
                }

                return new
                {
                    list = result,
                    pagination = new
                    {
                        pageIndex = input.PageIndex,
                        pageSize = input.PageSize,
                        total = totalCount
                    }
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取线索池客户统计报表失败");
                throw NCCException.Oh($"获取线索池客户统计报表失败：{ex.Message}");
            }
        }
        #endregion

        #region 线索池客户统计报表
        /// <summary>
        /// 获取线索池客户统计报表（BAK）
        /// </summary>
        /// <remarks>
        /// 根据拓客记录统计线索池客户的邀约、预约、消耗、开单等信息
        /// 
        /// 业务链路：拓客 -> 邀约 -> 预约 -> 开单/消耗
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "pageIndex": 1,
        ///   "pageSize": 20,
        ///   "startTime": "2025-10-01T00:00:00",
        ///   "endTime": "2025-10-31T23:59:59",
        ///   "storeIds": ["store1", "store2"],
        ///   "eventId": "event123"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - pageIndex: 页码，从1开始
        /// - pageSize: 每页数量
        /// - startTime: 拓客时间范围开始时间
        /// - endTime: 拓客时间范围结束时间
        /// - storeIds: 门店ID列表，可传多个
        /// - eventId: 拓客活动ID
        /// 
        /// 返回数据说明：
        /// - LeadCustomerId: 线索池客户（拓客编号）
        /// - CustomerName: 客户姓名
        /// - ExpansionTime: 拓客时间
        /// - HasInvite: 是否邀约（是/否），通过拓客编号关联邀约表
        /// - HasAppointment: 是否预约（是/否），只统计通过邀约产生的预约（预约表的F_InviteId关联邀约表）
        /// - HasConsume: 是否有消耗（是/否），只统计通过预约产生的耗卡（耗卡表的F_AppointmentId关联预约表）
        /// - HasBilling: 是否开单（是/否），只统计通过预约产生的开单（开单表的F_AppointmentId关联预约表）
        /// - NoBillingReason: 未开单原因，从预约记录的F_NoDealRemark字段获取
        /// - BillingAmount: 开卡金额，汇总通过预约产生的开单记录的整单业绩（zdyj）
        /// - BillingItems: 开卡卡项，汇总通过预约产生的开单品项名称，多个用顿号分隔
        /// - ActualAppointmentCount: 实际预约记录数（不管是否通过邀约产生），用于问题分析
        /// - ActualConsumeCount: 实际消耗记录数（不管是否通过预约产生），用于问题分析
        /// - ActualBillingCount: 实际开单记录数（不管是否通过预约产生），用于问题分析
        /// - Analysis: 问题分析说明，自动分析数据异常情况，如：有预约记录但未通过邀约产生、有消耗记录但未通过预约产生等
        /// 
        /// 返回示例：
        /// ```json
        /// {
        ///   "list": [
        ///     {
        ///       "LeadCustomerId": "751248448816153862",
        ///       "CustomerName": "王女士",
        ///       "ExpansionTime": "2025-10-24T03:33:10.000Z",
        ///       "HasInvite": "否",
        ///       "HasAppointment": "否",
        ///       "HasConsume": "否",
        ///       "HasBilling": "否",
        ///       "NoBillingReason": null,
        ///       "BillingAmount": 0,
        ///       "BillingItems": null,
        ///       "ActualAppointmentCount": 3,
        ///       "ActualConsumeCount": 4,
        ///       "ActualBillingCount": 5,
        ///       "Analysis": "有3条预约记录，但未通过邀约产生（F_InviteId为null）；有4条消耗记录，但未通过预约产生（F_AppointmentId为null或预约未通过邀约产生）；有5条开单记录，但未通过预约产生（F_AppointmentId为null或预约未通过邀约产生）"
        ///     }
        ///   ],
        ///   "pagination": {
        ///     "pageIndex": 1,
        ///     "pageSize": 20,
        ///     "total": 1511
        ///   }
        /// }
        /// ```
        /// </remarks>
        /// <param name="input">查询条件</param>
        /// <returns>线索池客户统计报表列表，包含统计数据和问题分析</returns>
        /// <response code="200">查询成功，返回统计报表列表和分页信息</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("get-lead-customer-statistics-list-bak")]
        public async Task<dynamic> GetLeadCustomerStatisticsList_bak([FromBody] LeadCustomerStatisticsListQueryInput input)
        {
            try
            {
                // 构建WHERE条件
                var whereConditions = new List<string>();
                var parameters = new List<SugarParameter>();

                if (input.StartTime.HasValue)
                {
                    whereConditions.Add("tk.F_ExpansionTime >= @StartTime");
                    parameters.Add(new SugarParameter("@StartTime", input.StartTime.Value));
                }

                if (input.EndTime.HasValue)
                {
                    whereConditions.Add("tk.F_ExpansionTime <= @EndTime");
                    parameters.Add(new SugarParameter("@EndTime", input.EndTime.Value));
                }

                if (input.StoreIds != null && input.StoreIds.Any())
                {
                    var storeIdParams = string.Join(",", input.StoreIds.Select((_, i) => $"@StoreId{i}"));
                    whereConditions.Add($"tk.F_StoreId IN ({storeIdParams})");
                    for (int i = 0; i < input.StoreIds.Count; i++)
                    {
                        parameters.Add(new SugarParameter($"@StoreId{i}", input.StoreIds[i]));
                    }
                }

                if (!string.IsNullOrEmpty(input.EventId))
                {
                    whereConditions.Add("tk.F_EventId = @EventId");
                    parameters.Add(new SugarParameter("@EventId", input.EventId));
                }

                var whereClause = whereConditions.Any() ? "WHERE " + string.Join(" AND ", whereConditions) : "";

                // 使用子查询优化性能，避免复杂的JOIN和GROUP BY
                var sql = $@"
                    SELECT
                        tk.F_Id as LeadCustomerId,
                        tk.F_CustomerName as CustomerName,
                        tk.F_ExpansionTime as ExpansionTime,
                        -- 是否邀约：通过拓客编号关联
                        CASE WHEN yaoy_stats.has_invite = 1 THEN '是' ELSE '否' END as HasInvite,
                        -- 是否预约：通过邀约ID关联（只统计通过邀约产生的预约）
                        CASE WHEN yy_stats.has_appointment = 1 THEN '是' ELSE '否' END as HasAppointment,
                        -- 是否有消耗：通过预约ID关联（只统计通过预约产生的耗卡）
                        CASE WHEN xh_stats.has_consume = 1 THEN '是' ELSE '否' END as HasConsume,
                        -- 是否开单：通过预约ID关联（只统计通过预约产生的开单）
                        CASE WHEN kd_stats.has_billing = 1 THEN '是' ELSE '否' END as HasBilling,
                        -- 未开单原因：从预约记录中获取（只取通过邀约产生的预约）
                        yy_stats.no_billing_reason as NoBillingReason,
                        -- 开卡金额：汇总通过预约产生的开单记录
                        COALESCE(kd_stats.billing_amount, 0) as BillingAmount,
                        -- 开卡卡项：汇总通过预约产生的开单品项
                        kd_stats.billing_items as BillingItems,
                        -- 实际预约记录数（不管是否通过邀约产生）
                        COALESCE(yy_actual.count, 0) as ActualAppointmentCount,
                        -- 实际消耗记录数（不管是否通过预约产生）
                        COALESCE(xh_actual.count, 0) as ActualConsumeCount,
                        -- 实际开单记录数（不管是否通过预约产生）
                        COALESCE(kd_actual.count, 0) as ActualBillingCount
                    FROM lq_tkjlb tk
                    -- 邀约统计子查询（通过会员ID关联：邀约表的yykh字段存储的是会员ID）
                    LEFT JOIN (
                        SELECT
                            yaoy.yykh as member_id,
                            1 as has_invite
                        FROM lq_yaoyjl yaoy
                        WHERE yaoy.yykh IS NOT NULL
                        GROUP BY yaoy.yykh
                    ) yaoy_stats ON yaoy_stats.member_id = tk.F_MemberId
                    -- 预约统计子查询（只统计通过邀约产生的预约）
                    -- 通过会员ID关联：线索池 -> 邀约（通过会员ID） -> 预约（通过邀约ID，且会员ID匹配）
                    LEFT JOIN (
                        SELECT
                            tk_inner.F_MemberId as member_id,
                            1 as has_appointment,
                            MAX(yy.F_NoDealRemark) as no_billing_reason
                        FROM lq_tkjlb tk_inner
                        INNER JOIN lq_yaoyjl yaoy ON yaoy.yykh = tk_inner.F_MemberId
                        INNER JOIN lq_yyjl yy ON yy.F_InviteId = yaoy.F_Id AND yy.gk = tk_inner.F_MemberId
                        GROUP BY tk_inner.F_MemberId
                    ) yy_stats ON yy_stats.member_id = tk.F_MemberId
                    -- 消耗统计子查询（只统计通过预约产生的耗卡）
                    -- 通过会员ID关联：线索池 -> 邀约 -> 预约 -> 消耗（通过预约ID，且会员ID匹配）
                    LEFT JOIN (
                        SELECT
                            tk_inner.F_MemberId as member_id,
                            1 as has_consume
                        FROM lq_tkjlb tk_inner
                        INNER JOIN lq_yaoyjl yaoy ON yaoy.yykh = tk_inner.F_MemberId
                        INNER JOIN lq_yyjl yy ON yy.F_InviteId = yaoy.F_Id AND yy.gk = tk_inner.F_MemberId
                        INNER JOIN lq_xh_hyhk xh ON xh.F_AppointmentId = yy.F_Id AND xh.hy = tk_inner.F_MemberId AND xh.F_IsEffective = 1
                        GROUP BY tk_inner.F_MemberId
                    ) xh_stats ON xh_stats.member_id = tk.F_MemberId
                    -- 开单统计子查询（只统计通过预约产生的开单，包含金额和品项）
                    -- 通过会员ID关联：线索池 -> 邀约 -> 预约 -> 开单（通过预约ID，且会员ID匹配）
                    -- 注意：剔除品项ID是61且金额是0的记录
                    LEFT JOIN (
                        SELECT
                            tk_inner.F_MemberId as member_id,
                            1 as has_billing,
                            SUM(kd.zdyj) as billing_amount,
                            GROUP_CONCAT(DISTINCT kdpx.pxmc SEPARATOR '、') as billing_items
                        FROM lq_tkjlb tk_inner
                        INNER JOIN lq_yaoyjl yaoy ON yaoy.yykh = tk_inner.F_MemberId
                        INNER JOIN lq_yyjl yy ON yy.F_InviteId = yaoy.F_Id AND yy.gk = tk_inner.F_MemberId
                        INNER JOIN lq_kd_kdjlb kd ON kd.F_AppointmentId = yy.F_Id AND kd.kdhy = tk_inner.F_MemberId AND kd.F_IsEffective = 1
                        INNER JOIN (
                            SELECT DISTINCT glkdbh
                            FROM lq_kd_pxmx
                            WHERE F_IsEffective = 1
                                AND NOT (px = '61' AND (COALESCE(F_ActualPrice, 0) = 0 AND COALESCE(F_TotalPrice, 0) = 0))
                        ) valid_px ON valid_px.glkdbh = kd.F_Id
                        LEFT JOIN lq_kd_pxmx kdpx ON kdpx.glkdbh = kd.F_Id AND kdpx.F_IsEffective = 1
                            AND NOT (kdpx.px = '61' AND (COALESCE(kdpx.F_ActualPrice, 0) = 0 AND COALESCE(kdpx.F_TotalPrice, 0) = 0))
                        GROUP BY tk_inner.F_MemberId
                    ) kd_stats ON kd_stats.member_id = tk.F_MemberId
                    -- 实际预约记录数统计（不管是否通过邀约产生）
                    LEFT JOIN (
                        SELECT
                            yy.gk as member_id,
                            COUNT(*) as count
                        FROM lq_yyjl yy
                        GROUP BY yy.gk
                    ) yy_actual ON yy_actual.member_id = tk.F_MemberId
                    -- 实际消耗记录数统计（不管是否通过预约产生）
                    LEFT JOIN (
                        SELECT
                            xh.hy as member_id,
                            COUNT(*) as count
                        FROM lq_xh_hyhk xh
                        WHERE xh.F_IsEffective = 1
                        GROUP BY xh.hy
                    ) xh_actual ON xh_actual.member_id = tk.F_MemberId
                    -- 实际开单记录数统计（不管是否通过预约产生）
                    LEFT JOIN (
                        SELECT
                            kd.kdhy as member_id,
                            COUNT(*) as count
                        FROM lq_kd_kdjlb kd
                        WHERE kd.F_IsEffective = 1
                        GROUP BY kd.kdhy
                    ) kd_actual ON kd_actual.member_id = tk.F_MemberId
                    {whereClause}
                    ORDER BY tk.F_ExpansionTime DESC
                    LIMIT @PageSize OFFSET @Offset";
                parameters.Add(new SugarParameter("@PageSize", input.PageSize));
                parameters.Add(new SugarParameter("@Offset", (input.PageIndex - 1) * input.PageSize));

                // 查询总数
                var countSql = $@"
                    SELECT COUNT(*)
                    FROM lq_tkjlb tk
                    {whereClause}";
                var countParameters = parameters.Where(p => p.ParameterName != "@PageSize" && p.ParameterName != "@Offset").ToList();
                var totalCount = await _db.Ado.GetIntAsync(countSql, countParameters);

                // 执行查询
                var result = await _db.Ado.SqlQueryAsync<LeadCustomerStatisticsListOutput>(sql, parameters);

                // 生成问题分析说明
                foreach (var item in result)
                {
                    var analysisList = new List<string>();

                    if (item.HasInvite == "否" && item.HasAppointment == "否" && item.ActualAppointmentCount > 0)
                    {
                        analysisList.Add($"有{item.ActualAppointmentCount}条预约记录，但未通过邀约产生（F_InviteId为null）");
                    }

                    if (item.HasAppointment == "否" && item.HasConsume == "否" && item.ActualConsumeCount > 0)
                    {
                        analysisList.Add($"有{item.ActualConsumeCount}条消耗记录，但未通过预约产生（F_AppointmentId为null或预约未通过邀约产生）");
                    }

                    if (item.HasAppointment == "否" && item.HasBilling == "否" && item.ActualBillingCount > 0)
                    {
                        analysisList.Add($"有{item.ActualBillingCount}条开单记录，但未通过预约产生（F_AppointmentId为null或预约未通过邀约产生）");
                    }

                    if (item.HasInvite == "是" && item.HasAppointment == "否" && item.ActualAppointmentCount > 0)
                    {
                        analysisList.Add($"有邀约记录，有{item.ActualAppointmentCount}条预约记录，但预约记录的F_InviteId未关联到邀约记录");
                    }

                    if (item.HasAppointment == "是" && item.HasConsume == "否" && item.ActualConsumeCount > 0)
                    {
                        analysisList.Add($"有预约记录，有{item.ActualConsumeCount}条消耗记录，但消耗记录的F_AppointmentId未关联到预约记录");
                    }

                    if (item.HasAppointment == "是" && item.HasBilling == "否" && item.ActualBillingCount > 0)
                    {
                        analysisList.Add($"有预约记录，有{item.ActualBillingCount}条开单记录，但开单记录的F_AppointmentId未关联到预约记录");
                    }

                    if (analysisList.Count == 0)
                    {
                        item.Analysis = "数据正常，符合业务链路：拓客 -> 邀约 -> 预约 -> 开单/消耗";
                    }
                    else
                    {
                        item.Analysis = string.Join("；", analysisList);
                    }
                }

                return new
                {
                    list = result,
                    pagination = new
                    {
                        pageIndex = input.PageIndex,
                        pageSize = input.PageSize,
                        total = totalCount
                    }
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取线索池客户统计报表失败");
                throw NCCException.Oh($"获取线索池客户统计报表失败：{ex.Message}");
            }
        }
        #endregion

        #region 门店统计报表
        /// <summary>
        /// 获取门店统计报表
        /// </summary>
        /// <remarks>
        /// 按门店统计拓客、邀约、预约、消耗、开单等数据
        /// 
        /// 业务链路：拓客 -> 邀约 -> 预约 -> 开单/消耗
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "startTime": "2025-10-01T00:00:00",
        ///   "endTime": "2025-10-31T23:59:59",
        ///   "storeIds": ["store1", "store2"],
        ///   "eventId": "event123"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - startTime: 拓客时间范围开始时间
        /// - endTime: 拓客时间范围结束时间
        /// - storeIds: 门店ID列表，可传多个
        /// - eventId: 拓客活动ID
        /// 
        /// 返回数据说明：
        /// - StoreId: 门店ID
        /// - StoreName: 门店名称
        /// - TotalCount: 总人数（从客户信息表按归属门店统计）
        /// - TkMemberCount: 拓客人数（拓客记录数，不去重）
        /// - InviteCount: 邀约数（通过拓客编号关联的邀约记录数）
        /// - AppointmentCount: 预约数（通过邀约ID关联的预约记录数，只统计通过邀约产生的预约）
        /// - ConsumeCount: 耗卡数（通过预约ID关联的耗卡记录数，只统计通过预约产生的耗卡）
        /// - BillingCount: 开单数（通过预约ID关联的开单记录数，只统计通过预约产生的开单）
        /// - BillingAmount: 开单金额（通过预约ID关联的开单记录金额汇总）
        /// 
        /// 返回示例：
        /// ```json
        /// {
        ///   "list": [
        ///     {
        ///       "StoreId": "1649328471923847169",
        ///       "StoreName": "绿纤紫荆店",
        ///       "TotalCount": 119,
        ///       "TkMemberCount": 117,
        ///       "InviteCount": 4,
        ///       "AppointmentCount": 2,
        ///       "ConsumeCount": 1,
        ///       "BillingCount": 1,
        ///       "BillingAmount": 199.00
        ///     }
        ///   ]
        /// }
        /// ```
        /// </remarks>
        /// <param name="input">查询条件</param>
        /// <returns>门店统计报表列表</returns>
        /// <response code="200">查询成功，返回门店统计报表列表</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("get-store-statistics-list")]
        public async Task<dynamic> GetStoreStatisticsList([FromBody] StoreStatisticsListQueryInput input)
        {
            try
            {
                // 构建WHERE条件（带表别名，用于子查询）
                var whereConditions = new List<string>();
                // 构建WHERE条件（不带表别名，用于UNION的SELECT）
                var whereConditionsNoAlias = new List<string>();
                var parameters = new List<SugarParameter>();

                if (input.StartTime.HasValue)
                {
                    whereConditions.Add("tk.F_ExpansionTime >= @StartTime");
                    whereConditionsNoAlias.Add("F_ExpansionTime >= @StartTime");
                    parameters.Add(new SugarParameter("@StartTime", input.StartTime.Value));
                }

                if (input.EndTime.HasValue)
                {
                    whereConditions.Add("tk.F_ExpansionTime <= @EndTime");
                    whereConditionsNoAlias.Add("F_ExpansionTime <= @EndTime");
                    parameters.Add(new SugarParameter("@EndTime", input.EndTime.Value));
                }

                if (input.StoreIds != null && input.StoreIds.Any())
                {
                    var storeIdParams = string.Join(",", input.StoreIds.Select((_, i) => $"@StoreId{i}"));
                    whereConditions.Add($"tk.F_StoreId IN ({storeIdParams})");
                    whereConditionsNoAlias.Add($"F_StoreId IN ({storeIdParams})");
                    for (int i = 0; i < input.StoreIds.Count; i++)
                    {
                        parameters.Add(new SugarParameter($"@StoreId{i}", input.StoreIds[i]));
                    }
                }

                if (!string.IsNullOrEmpty(input.EventId))
                {
                    whereConditions.Add("tk.F_EventId = @EventId");
                    whereConditionsNoAlias.Add("F_EventId = @EventId");
                    parameters.Add(new SugarParameter("@EventId", input.EventId));
                }

                var whereClause = whereConditions.Any() ? "WHERE " + string.Join(" AND ", whereConditions) : "";
                var whereClauseNoAlias = whereConditionsNoAlias.Any() ? "WHERE " + string.Join(" AND ", whereConditionsNoAlias) : "";

                // 构建门店筛选条件（用于客户信息表查询）
                var khWhereConditions = new List<string>();
                var khWhereConditionsNoAlias = new List<string>();
                if (input.StoreIds != null && input.StoreIds.Any())
                {
                    var storeIdParams = string.Join(",", input.StoreIds.Select((_, i) => $"@StoreId{i}"));
                    khWhereConditions.Add($"kh.gsmd IN ({storeIdParams})");
                    khWhereConditionsNoAlias.Add($"gsmd IN ({storeIdParams})");
                }

                var khWhereClause = khWhereConditions.Any() ? "WHERE " + string.Join(" AND ", khWhereConditions) : "WHERE kh.gsmd IS NOT NULL";
                var khWhereClauseNoAlias = khWhereConditionsNoAlias.Any() ? "WHERE " + string.Join(" AND ", khWhereConditionsNoAlias) : "WHERE gsmd IS NOT NULL";

                // 判断是否有活动筛选
                var hasEventFilter = !string.IsNullOrEmpty(input.EventId);

                // 使用子查询优化性能，避免复杂的JOIN
                // 构建时间过滤条件（用于全部数据统计）
                var timeFilterForAll = "";
                if (input.StartTime.HasValue && input.EndTime.HasValue)
                {
                    timeFilterForAll = $"AND xh.hksj >= '{input.StartTime.Value:yyyy-MM-dd HH:mm:ss}' AND xh.hksj <= '{input.EndTime.Value:yyyy-MM-dd HH:mm:ss}'";
                }
                else if (input.StartTime.HasValue)
                {
                    timeFilterForAll = $"AND xh.hksj >= '{input.StartTime.Value:yyyy-MM-dd HH:mm:ss}'";
                }
                else if (input.EndTime.HasValue)
                {
                    timeFilterForAll = $"AND xh.hksj <= '{input.EndTime.Value:yyyy-MM-dd HH:mm:ss}'";
                }

                var billingTimeFilterForAll = "";
                if (input.StartTime.HasValue && input.EndTime.HasValue)
                {
                    billingTimeFilterForAll = $"AND kd.kdrq >= '{input.StartTime.Value:yyyy-MM-dd HH:mm:ss}' AND kd.kdrq <= '{input.EndTime.Value:yyyy-MM-dd HH:mm:ss}'";
                }
                else if (input.StartTime.HasValue)
                {
                    billingTimeFilterForAll = $"AND kd.kdrq >= '{input.StartTime.Value:yyyy-MM-dd HH:mm:ss}'";
                }
                else if (input.EndTime.HasValue)
                {
                    billingTimeFilterForAll = $"AND kd.kdrq <= '{input.EndTime.Value:yyyy-MM-dd HH:mm:ss}'";
                }

                // 构建门店过滤条件（用于全部数据统计）
                var storeFilterForAll = "";
                if (input.StoreIds != null && input.StoreIds.Any())
                {
                    var storeIdParams = string.Join("','", input.StoreIds);
                    storeFilterForAll = $"AND xh.md IN ('{storeIdParams}')";
                }

                var billingStoreFilterForAll = "";
                if (input.StoreIds != null && input.StoreIds.Any())
                {
                    var storeIdParams = string.Join("','", input.StoreIds);
                    billingStoreFilterForAll = $"AND kd.djmd IN ('{storeIdParams}')";
                }

                var sql = $@"
                    SELECT
                        COALESCE(total_stats.StoreId, tk_stats.StoreId, yaoy_stats.StoreId, yy_stats.StoreId, xh_stats.StoreId, kd_stats.StoreId, visit_stats.StoreId, total_billing_stats.StoreId) as StoreId,
                        COALESCE(md.dm, '') as StoreName,
                        COALESCE(total_stats.TotalCount, 0) as TotalCount,
                        COALESCE(tk_stats.TkMemberCount, 0) as TkMemberCount,
                        COALESCE(yaoy_stats.InviteCount, 0) as InviteCount,
                        COALESCE(yy_stats.AppointmentCount, 0) as AppointmentCount,
                        COALESCE(xh_stats.ConsumeCount, 0) as ConsumeCount,
                        COALESCE(kd_stats.BillingCount, 0) as BillingCount,
                        COALESCE(kd_stats.BillingAmount, 0) as BillingAmount,
                        COALESCE(visit_stats.VisitCount, 0) as VisitCount,
                        COALESCE(total_billing_stats.TotalBillingCount, 0) as TotalBillingCount,
                        COALESCE(total_billing_stats.TotalBillingAmount, 0) as TotalBillingAmount
                    FROM (
                        SELECT DISTINCT StoreId FROM (
                            SELECT gsmd as StoreId FROM lq_khxx {khWhereClauseNoAlias}
                            UNION
                            SELECT F_StoreId as StoreId FROM lq_tkjlb {whereClauseNoAlias}
                            UNION
                            SELECT F_StoreId as StoreId FROM lq_yaoyjl WHERE F_StoreId IS NOT NULL
                            UNION
                            SELECT djmd as StoreId FROM lq_yyjl WHERE djmd IS NOT NULL
                            UNION
                            SELECT djmd as StoreId FROM lq_kd_kdjlb WHERE djmd IS NOT NULL AND F_IsEffective = 1
                            UNION
                            SELECT md as StoreId FROM lq_xh_hyhk WHERE md IS NOT NULL AND F_IsEffective = 1
                        ) as all_stores
                    ) as stores
                    LEFT JOIN lq_mdxx md ON md.F_Id = stores.StoreId
                    -- 总人数统计（从客户信息表按归属门店统计）
                    LEFT JOIN (
                        SELECT
                            kh.gsmd as StoreId,
                            COUNT(*) as TotalCount
                        FROM lq_khxx kh
                        {khWhereClause}
                        GROUP BY kh.gsmd
                    ) total_stats ON total_stats.StoreId = stores.StoreId
                    -- 拓客人数统计（不用去重）
                    LEFT JOIN (
                        SELECT
                            tk.F_StoreId as StoreId,
                            COUNT(tk.F_MemberId) as TkMemberCount
                        FROM lq_tkjlb tk
                        {(string.IsNullOrEmpty(whereClause) ? "" : whereClause)}
                        GROUP BY tk.F_StoreId
                    ) tk_stats ON tk_stats.StoreId = stores.StoreId
                    -- 邀约数统计（基于拓客会员数：统计有邀约的拓客会员数，按会员ID去重）
                    LEFT JOIN (
                        {(hasEventFilter ? $@"
                        SELECT
                            tk.F_StoreId as StoreId,
                            COUNT(DISTINCT tk.F_MemberId) as InviteCount
                        FROM lq_tkjlb tk
                        INNER JOIN lq_yaoyjl yaoy ON (yaoy.tkbh = tk.F_Id OR yaoy.yykh = tk.F_MemberId)
                        WHERE tk.F_StoreId IS NOT NULL
                            AND tk.F_EventId = @EventId
                        GROUP BY tk.F_StoreId" : $@"
                        SELECT
                            tk.F_StoreId as StoreId,
                            COUNT(DISTINCT tk.F_MemberId) as InviteCount
                        FROM lq_tkjlb tk
                        INNER JOIN lq_yaoyjl yaoy ON (yaoy.tkbh = tk.F_Id OR yaoy.yykh = tk.F_MemberId)
                        {(string.IsNullOrEmpty(whereClause) ? "" : whereClause)}
                        GROUP BY tk.F_StoreId")}
                    ) yaoy_stats ON yaoy_stats.StoreId = stores.StoreId
                    -- 预约数统计（基于拓客会员数：统计有预约的拓客会员数，按会员ID去重，且通过邀约产生）
                    LEFT JOIN (
                        {(hasEventFilter ? $@"
                        SELECT
                            tk.F_StoreId as StoreId,
                            COUNT(DISTINCT tk.F_MemberId) as AppointmentCount
                        FROM lq_tkjlb tk
                        INNER JOIN lq_yaoyjl yaoy ON (yaoy.tkbh = tk.F_Id OR yaoy.yykh = tk.F_MemberId)
                        INNER JOIN lq_yyjl yy ON yy.F_InviteId = yaoy.F_Id AND yy.gk = tk.F_MemberId
                        WHERE tk.F_StoreId IS NOT NULL
                            AND tk.F_EventId = @EventId
                        GROUP BY tk.F_StoreId" : $@"
                        SELECT
                            tk.F_StoreId as StoreId,
                            COUNT(DISTINCT tk.F_MemberId) as AppointmentCount
                        FROM lq_tkjlb tk
                        INNER JOIN lq_yaoyjl yaoy ON (yaoy.tkbh = tk.F_Id OR yaoy.yykh = tk.F_MemberId)
                        INNER JOIN lq_yyjl yy ON yy.F_InviteId = yaoy.F_Id AND yy.gk = tk.F_MemberId
                        {(string.IsNullOrEmpty(whereClause) ? "" : whereClause)}
                        GROUP BY tk.F_StoreId")}
                    ) yy_stats ON yy_stats.StoreId = stores.StoreId
                    -- 耗卡数统计（基于拓客会员数：统计有耗卡的拓客会员数，按会员ID去重，通过预约->邀约->拓客链路关联）
                    LEFT JOIN (
                        {(hasEventFilter ? $@"
                        SELECT
                            tk.F_StoreId as StoreId,
                            COUNT(DISTINCT tk.F_MemberId) as ConsumeCount
                        FROM lq_tkjlb tk
                        INNER JOIN lq_yaoyjl yaoy ON (yaoy.tkbh = tk.F_Id OR yaoy.yykh = tk.F_MemberId)
                        INNER JOIN lq_yyjl yy ON yy.F_InviteId = yaoy.F_Id AND yy.gk = tk.F_MemberId
                        INNER JOIN lq_xh_hyhk xh ON xh.F_AppointmentId = yy.F_Id AND xh.F_IsEffective = 1
                        WHERE tk.F_StoreId IS NOT NULL
                            AND tk.F_EventId = @EventId
                        GROUP BY tk.F_StoreId" : $@"
                        SELECT
                            tk.F_StoreId as StoreId,
                            COUNT(DISTINCT tk.F_MemberId) as ConsumeCount
                        FROM lq_tkjlb tk
                        INNER JOIN lq_yaoyjl yaoy ON (yaoy.tkbh = tk.F_Id OR yaoy.yykh = tk.F_MemberId)
                        INNER JOIN lq_yyjl yy ON yy.F_InviteId = yaoy.F_Id AND yy.gk = tk.F_MemberId
                        INNER JOIN lq_xh_hyhk xh ON xh.F_AppointmentId = yy.F_Id AND xh.F_IsEffective = 1
                        {(string.IsNullOrEmpty(whereClause) ? "" : whereClause)}
                        GROUP BY tk.F_StoreId")}
                    ) xh_stats ON xh_stats.StoreId = stores.StoreId
                    -- 开单数统计（基于拓客会员数：统计有开单的拓客会员数，按会员ID去重，通过预约->邀约->拓客链路关联）
                    LEFT JOIN (
                        {(hasEventFilter ? $@"
                        SELECT
                            tk.F_StoreId as StoreId,
                            COUNT(DISTINCT tk.F_MemberId) as BillingCount,
                            SUM(kd.zdyj) as BillingAmount
                        FROM lq_tkjlb tk
                        INNER JOIN lq_yaoyjl yaoy ON (yaoy.tkbh = tk.F_Id OR yaoy.yykh = tk.F_MemberId)
                        INNER JOIN lq_yyjl yy ON yy.F_InviteId = yaoy.F_Id AND yy.gk = tk.F_MemberId
                        INNER JOIN lq_kd_kdjlb kd ON kd.F_AppointmentId = yy.F_Id AND kd.kdhy = tk.F_MemberId AND kd.F_IsEffective = 1
                        WHERE kd.F_AppointmentId IS NOT NULL
                            AND kd.F_AppointmentId != ''
                            AND tk.F_StoreId IS NOT NULL
                            AND tk.F_EventId = @EventId
                        GROUP BY tk.F_StoreId" : $@"
                        SELECT
                            tk.F_StoreId as StoreId,
                            COUNT(DISTINCT tk.F_MemberId) as BillingCount,
                            SUM(kd.zdyj) as BillingAmount
                        FROM lq_tkjlb tk
                        INNER JOIN lq_yaoyjl yaoy ON (yaoy.tkbh = tk.F_Id OR yaoy.yykh = tk.F_MemberId)
                        INNER JOIN lq_yyjl yy ON yy.F_InviteId = yaoy.F_Id AND yy.gk = tk.F_MemberId
                        INNER JOIN lq_kd_kdjlb kd ON kd.F_AppointmentId = yy.F_Id AND kd.kdhy = tk.F_MemberId AND kd.F_IsEffective = 1
                        WHERE kd.F_AppointmentId IS NOT NULL
                            AND kd.F_AppointmentId != ''
                        {(string.IsNullOrEmpty(whereClause) ? "" : " AND " + whereClause.Replace("WHERE ", ""))}
                        GROUP BY tk.F_StoreId")}
                    ) kd_stats ON kd_stats.StoreId = stores.StoreId
                    -- 到店人数统计（基于拓客会员数：统计在统计周期内有消耗的拓客会员人数，按会员ID去重）
                    LEFT JOIN (
                        {(hasEventFilter ? $@"
                        SELECT
                            tk.F_StoreId as StoreId,
                            COUNT(DISTINCT tk.F_MemberId) as VisitCount
                        FROM lq_tkjlb tk
                        INNER JOIN lq_xh_hyhk xh ON xh.hy = tk.F_MemberId AND xh.F_IsEffective = 1
                        WHERE tk.F_StoreId IS NOT NULL
                            AND tk.F_EventId = @EventId
                        GROUP BY tk.F_StoreId" : $@"
                        SELECT
                            tk.F_StoreId as StoreId,
                            COUNT(DISTINCT tk.F_MemberId) as VisitCount
                        FROM lq_tkjlb tk
                        INNER JOIN lq_xh_hyhk xh ON xh.hy = tk.F_MemberId AND xh.F_IsEffective = 1
                        WHERE tk.F_StoreId IS NOT NULL
                            {(string.IsNullOrEmpty(whereClause) ? "" : " AND " + whereClause.Replace("WHERE ", ""))}
                            {timeFilterForAll}
                        GROUP BY tk.F_StoreId")}
                    ) visit_stats ON visit_stats.StoreId = stores.StoreId
                    -- 开单数统计（全部数据，不要求链路，按门店统计该门店的所有开单记录）
                    LEFT JOIN (
                        SELECT
                            kd.djmd as StoreId,
                            COUNT(DISTINCT kd.kdhy) as TotalBillingCount,
                            SUM(kd.zdyj) as TotalBillingAmount
                        FROM lq_kd_kdjlb kd
                        WHERE kd.F_IsEffective = 1
                            AND kd.djmd IS NOT NULL
                            {billingTimeFilterForAll}
                            {billingStoreFilterForAll}
                        GROUP BY kd.djmd
                    ) total_billing_stats ON total_billing_stats.StoreId = stores.StoreId
                    WHERE stores.StoreId IS NOT NULL
                    ORDER BY stores.StoreId";
                // 执行查询
                var result = await _db.Ado.SqlQueryAsync<StoreStatisticsListOutput>(sql, parameters);

                // 计算到店率和成交率
                foreach (var item in result)
                {
                    // 到店率 = 到店人数 / 拓客人数 * 100
                    item.VisitRate = item.TkMemberCount > 0
                        ? Math.Round(item.VisitCount * 100m / item.TkMemberCount, 2)
                        : 0m;

                    // 成交率 = 开单数（全部数据）/ 到店人数 * 100
                    item.ConversionRate = item.VisitCount > 0
                        ? Math.Round(item.TotalBillingCount * 100m / item.VisitCount, 2)
                        : 0m;
                }

                return new
                {
                    list = result
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取门店统计报表失败");
                throw NCCException.Oh($"获取门店统计报表失败：{ex.Message}");
            }
        }
        #endregion

        #region 会员升单统计
        /// <summary>
        /// 获取会员升单统计（前4单中是否有升医美、升科美、升生美）
        /// </summary>
        /// <remarks>
        /// 统计每个会员的前4单开单记录中是否有升医美、升科美、升生美
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "pageIndex": 1,
        ///   "pageSize": 20,
        ///   "memberIds": ["member1", "member2"],
        ///   "hasUpgradeMedicalBeauty": true,
        ///   "hasUpgradeTechBeauty": false,
        ///   "hasUpgradeLifeBeauty": null
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - pageIndex: 页码，从1开始
        /// - pageSize: 每页数量
        /// - memberIds: 会员ID列表（可选，不传则查询所有会员）
        /// - hasUpgradeMedicalBeauty: 是否升医美（true-是，false-否，null-不筛选）
        /// - hasUpgradeTechBeauty: 是否升科美（true-是，false-否，null-不筛选）
        /// - hasUpgradeLifeBeauty: 是否升生美（true-是，false-否，null-不筛选）
        /// 
        /// 返回数据说明：
        /// - MemberId: 会员ID
        /// - MemberName: 会员姓名
        /// - MemberPhone: 会员手机号
        /// - HasUpgradeMedicalBeauty: 前4单中是否有升医美（是/否）
        /// - HasUpgradeTechBeauty: 前4单中是否有升科美（是/否）
        /// - HasUpgradeLifeBeauty: 前4单中是否有升生美（是/否）
        /// 
        /// 返回示例：
        /// ```json
        /// {
        ///   "list": [
        ///     {
        ///       "MemberId": "744326092097062149",
        ///       "MemberName": "张女士",
        ///       "MemberPhone": "13800138000",
        ///       "HasUpgradeMedicalBeauty": "否",
        ///       "HasUpgradeTechBeauty": "否",
        ///       "HasUpgradeLifeBeauty": "否"
        ///     }
        ///   ],
        ///   "pagination": {
        ///     "pageIndex": 1,
        ///     "pageSize": 20,
        ///     "total": 100
        ///   }
        /// }
        /// ```
        /// </remarks>
        /// <param name="input">查询条件</param>
        /// <returns>会员升单统计列表</returns>
        /// <response code="200">查询成功，返回会员升单统计列表</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("get-member-upgrade-statistics-list")]
        public async Task<dynamic> GetMemberUpgradeStatisticsList([FromBody] MemberUpgradeStatisticsListQueryInput input)
        {
            try
            {
                // 构建WHERE条件
                var whereConditions = new List<string>();
                var parameters = new List<SugarParameter>();

                if (input.MemberIds != null && input.MemberIds.Any())
                {
                    var memberIdParams = string.Join(",", input.MemberIds.Select((_, i) => $"@MemberId{i}"));
                    whereConditions.Add($"kd.kdhy IN ({memberIdParams})");
                    for (int i = 0; i < input.MemberIds.Count; i++)
                    {
                        parameters.Add(new SugarParameter($"@MemberId{i}", input.MemberIds[i]));
                    }
                }

                // 添加多门店筛选
                if (input.StoreIds != null && input.StoreIds.Any())
                {
                    var storeIdParams = string.Join(",", input.StoreIds.Select((_, i) => $"@StoreId{i}"));
                    whereConditions.Add($"kd.Djmd IN ({storeIdParams})");
                    for (int i = 0; i < input.StoreIds.Count; i++)
                    {
                        parameters.Add(new SugarParameter($"@StoreId{i}", input.StoreIds[i]));
                    }
                }

                var whereClause = whereConditions.Any() ? "AND " + string.Join(" AND ", whereConditions) : "";

                // 构建HAVING条件（用于筛选升单条件）
                var havingConditions = new List<string>();
                if (input.HasUpgradeMedicalBeauty.HasValue)
                {
                    if (input.HasUpgradeMedicalBeauty.Value)
                    {
                        havingConditions.Add("MAX(CASE WHEN kd.F_UpgradeMedicalBeauty = '是' THEN 1 ELSE 0 END) = 1");
                    }
                    else
                    {
                        havingConditions.Add("MAX(CASE WHEN kd.F_UpgradeMedicalBeauty = '是' THEN 1 ELSE 0 END) = 0");
                    }
                }
                if (input.HasUpgradeTechBeauty.HasValue)
                {
                    if (input.HasUpgradeTechBeauty.Value)
                    {
                        havingConditions.Add("MAX(CASE WHEN kd.F_UpgradeTechBeauty = '是' THEN 1 ELSE 0 END) = 1");
                    }
                    else
                    {
                        havingConditions.Add("MAX(CASE WHEN kd.F_UpgradeTechBeauty = '是' THEN 1 ELSE 0 END) = 0");
                    }
                }
                if (input.HasUpgradeLifeBeauty.HasValue)
                {
                    if (input.HasUpgradeLifeBeauty.Value)
                    {
                        havingConditions.Add("MAX(CASE WHEN kd.F_UpgradeLifeBeauty = '是' THEN 1 ELSE 0 END) = 1");
                    }
                    else
                    {
                        havingConditions.Add("MAX(CASE WHEN kd.F_UpgradeLifeBeauty = '是' THEN 1 ELSE 0 END) = 0");
                    }
                }

                var havingClause = havingConditions.Any() ? "HAVING " + string.Join(" AND ", havingConditions) : "";

                // 分页参数
                var offset = (input.PageIndex - 1) * input.PageSize;
                parameters.Add(new SugarParameter("@PageSize", input.PageSize));
                parameters.Add(new SugarParameter("@Offset", offset));

                // 查询每个会员的前4单中是否有升医美、升科美、升生美
                var sql = $@"
                    SELECT
                        kd.kdhy as MemberId,
                        kh.khmc as MemberName,
                        kh.sjh as MemberPhone,
                        CASE WHEN MAX(CASE WHEN kd.F_UpgradeMedicalBeauty = '是' THEN 1 ELSE 0 END) = 1 THEN '是' ELSE '否' END as HasUpgradeMedicalBeauty,
                        CASE WHEN MAX(CASE WHEN kd.F_UpgradeTechBeauty = '是' THEN 1 ELSE 0 END) = 1 THEN '是' ELSE '否' END as HasUpgradeTechBeauty,
                        CASE WHEN MAX(CASE WHEN kd.F_UpgradeLifeBeauty = '是' THEN 1 ELSE 0 END) = 1 THEN '是' ELSE '否' END as HasUpgradeLifeBeauty
                    FROM (
                        SELECT
                            kd.kdhy,
                            kd.F_Id,
                            kd.kdrq,
                            kd.F_CreateTime,
                            kd.F_UpgradeMedicalBeauty,
                            kd.F_UpgradeTechBeauty,
                            kd.F_UpgradeLifeBeauty
                        FROM lq_kd_kdjlb kd
                        WHERE kd.F_IsEffective = 1
                            AND kd.kdhy IS NOT NULL
                            {whereClause}
                            AND (
                                SELECT COUNT(*)
                                FROM lq_kd_kdjlb kd2
                                WHERE kd2.kdhy = kd.kdhy
                                    AND kd2.F_IsEffective = 1
                                    AND (
                                        kd2.kdrq > kd.kdrq
                                        OR (kd2.kdrq = kd.kdrq AND kd2.F_CreateTime > kd.F_CreateTime)
                                        OR (kd2.kdrq = kd.kdrq AND kd2.F_CreateTime = kd.F_CreateTime AND kd2.F_Id > kd.F_Id)
                                    )
                            ) < 4
                    ) kd
                    LEFT JOIN lq_khxx kh ON kh.F_Id = kd.kdhy
                    GROUP BY kd.kdhy, kh.khmc, kh.sjh
                    {havingClause}
                    ORDER BY kd.kdhy
                    LIMIT @PageSize OFFSET @Offset";
                // 查询总数（需要应用相同的HAVING条件）
                var countSql = $@"
                    SELECT COUNT(*)
                    FROM (
                        SELECT
                            kd.kdhy,
                            CASE WHEN MAX(CASE WHEN kd.F_UpgradeMedicalBeauty = '是' THEN 1 ELSE 0 END) = 1 THEN '是' ELSE '否' END as HasUpgradeMedicalBeauty,
                            CASE WHEN MAX(CASE WHEN kd.F_UpgradeTechBeauty = '是' THEN 1 ELSE 0 END) = 1 THEN '是' ELSE '否' END as HasUpgradeTechBeauty,
                            CASE WHEN MAX(CASE WHEN kd.F_UpgradeLifeBeauty = '是' THEN 1 ELSE 0 END) = 1 THEN '是' ELSE '否' END as HasUpgradeLifeBeauty
                        FROM (
                            SELECT
                                kd.kdhy,
                                kd.F_Id,
                                kd.kdrq,
                                kd.F_CreateTime,
                                kd.F_UpgradeMedicalBeauty,
                                kd.F_UpgradeTechBeauty,
                                kd.F_UpgradeLifeBeauty
                            FROM lq_kd_kdjlb kd
                            WHERE kd.F_IsEffective = 1
                                AND kd.kdhy IS NOT NULL
                                {whereClause}
                                AND (
                                    SELECT COUNT(*)
                                    FROM lq_kd_kdjlb kd2
                                    WHERE kd2.kdhy = kd.kdhy
                                        AND kd2.F_IsEffective = 1
                                        AND (
                                            kd2.kdrq > kd.kdrq
                                            OR (kd2.kdrq = kd.kdrq AND kd2.F_CreateTime > kd.F_CreateTime)
                                            OR (kd2.kdrq = kd.kdrq AND kd2.F_CreateTime = kd.F_CreateTime AND kd2.F_Id > kd.F_Id)
                                        )
                                ) < 4
                        ) kd
                        GROUP BY kd.kdhy
                        {havingClause}
                    ) as filtered_results";
                var countParameters = parameters.Where(p => p.ParameterName != "@PageSize" && p.ParameterName != "@Offset").ToList();
                var totalCount = await _db.Ado.GetIntAsync(countSql, countParameters);

                // 执行查询
                var result = await _db.Ado.SqlQueryAsync<MemberUpgradeStatisticsListOutput>(sql, parameters);

                return new
                {
                    list = result,
                    pagination = new
                    {
                        pageIndex = input.PageIndex,
                        pageSize = input.PageSize,
                        total = totalCount
                    }
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取会员升单统计失败");
                throw NCCException.Oh($"获取会员升单统计失败：{ex.Message}");
            }
        }
        #endregion

        #region 店长三项指标统计

        /// <summary>
        /// 获取当月全部店长三项指标对比
        /// </summary>
        /// <remarks>
        /// 统计指定月份所有店长的三项指标完成情况，包括业绩、人头、消耗指标的完成度和底薪信息
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "statisticsMonth": "202601",
        ///   "storeIds": ["门店ID1", "门店ID2"],
        ///   "isNewStore": null,
        ///   "storeType": null,
        ///   "indicatorStatus": null
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - statisticsMonth: 统计月份（YYYYMM格式，必填）
        /// - storeIds: 门店ID列表（可选，不传则查询所有门店）
        /// - isNewStore: 是否新店（可选，true=新店，false=老店，null=全部）
        /// - storeType: 门店类型（可选，用于筛选旗舰店等）
        /// - indicatorStatus: 指标达标情况（可选，"all_reached"=全部达标，"partial"=部分达标，"all_unreached"=全部未达标，null=全部）
        /// 
        /// 返回数据说明：
        /// - list: 店长三项指标对比列表
        /// - summary: 汇总统计信息
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>店长三项指标对比结果</returns>
        /// <response code="200">成功返回统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("get-store-manager-indicators-comparison")]
        public async Task<dynamic> GetStoreManagerIndicatorsComparison([FromBody] StoreManagerIndicatorsComparisonInput input)
        {
            try
            {
                if (input == null || string.IsNullOrWhiteSpace(input.StatisticsMonth))
                {
                    throw NCCException.Oh("统计月份不能为空");
                }

                if (input.StatisticsMonth.Length != 6)
                {
                    throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
                }

                // 构建查询条件
                var query = _db.Queryable<LqStoreManagerSalaryStatisticsEntity>()
                    .Where(x => x.StatisticsMonth == input.StatisticsMonth && x.Position == "店长");

                // 门店筛选
                if (input.StoreIds != null && input.StoreIds.Any())
                {
                    query = query.Where(x => input.StoreIds.Contains(x.StoreId));
                }

                // 是否新店筛选
                if (input.IsNewStore.HasValue)
                {
                    var isNewStoreStr = input.IsNewStore.Value ? "是" : "否";
                    query = query.Where(x => x.IsNewStore == isNewStoreStr);
                }

                // 门店类型筛选
                if (input.StoreType.HasValue)
                {
                    query = query.Where(x => x.StoreType == input.StoreType.Value);
                }

                // 查询数据
                var salaryList = await query.ToListAsync();

                if (!salaryList.Any())
                {
                    return new
                    {
                        list = new List<StoreManagerIndicatorsComparisonOutput>(),
                        summary = new StoreManagerIndicatorsComparisonSummaryOutput
                        {
                            TotalCount = 0,
                            AllReachedCount = 0,
                            PartialReachedCount = 0,
                            AllUnreachedCount = 0,
                            AveragePerformanceCompletionRate = 0,
                            AverageHeadCountCompletionRate = 0,
                            AverageConsumeCompletionRate = 0
                        }
                    };
                }

                // 转换为输出格式
                var outputList = salaryList.Select(salary =>
                {
                    // 判断是否旗舰店
                    bool isFlagshipStore = salary.StoreType.HasValue && salary.StoreType.Value == 2;

                    // 判断是否新店
                    bool isNewStore = salary.IsNewStore == "是";

                    // 计算完成度
                    decimal performanceCompletionRate = salary.StoreLifeline > 0
                        ? Math.Round((salary.StoreTotalPerformance / salary.StoreLifeline) * 100m, 2)
                        : 0m;

                    decimal headCountCompletionRate = salary.TargetHeadCount > 0
                        ? Math.Round((salary.HeadCount / salary.TargetHeadCount) * 100m, 2)
                        : 0m;

                    decimal consumeCompletionRate = isNewStore || salary.TargetConsume <= 0
                        ? 0m
                        : Math.Round((salary.StoreConsume / salary.TargetConsume) * 100m, 2);

                    return new StoreManagerIndicatorsComparisonOutput
                    {
                        StoreId = salary.StoreId,
                        StoreName = salary.StoreName,
                        EmployeeId = salary.EmployeeId,
                        EmployeeName = salary.EmployeeName,
                        IsNewStore = salary.IsNewStore ?? "否",
                        StoreType = salary.StoreType,
                        IsFlagshipStore = isFlagshipStore,
                        PerformanceIndicator = new PerformanceIndicator
                        {
                            Target = salary.StoreLifeline,
                            Actual = salary.StoreTotalPerformance,
                            CompletionRate = performanceCompletionRate,
                            Reached = salary.PerformanceReached ?? "否"
                        },
                        HeadCountIndicator = new HeadCountIndicator
                        {
                            Target = salary.TargetHeadCount,
                            Actual = salary.HeadCount,
                            CompletionRate = headCountCompletionRate,
                            Reached = salary.HeadCountReached ?? "否"
                        },
                        ConsumeIndicator = new ConsumeIndicator
                        {
                            Target = salary.TargetConsume,
                            Actual = salary.StoreConsume,
                            CompletionRate = consumeCompletionRate,
                            Reached = salary.ConsumeReached ?? "否",
                            IsAssessed = !isNewStore
                        },
                        BaseSalary = new BaseSalaryInfo
                        {
                            FixedSalary = salary.BaseSalary,
                            UnreachedIndicatorCount = salary.UnreachedIndicatorCount,
                            AssessmentDeduction = salary.AssessmentDeduction,
                            FlagshipStoreDeduction = salary.FlagshipStoreDeduction,
                            ActualSalary = salary.ActualBaseSalary
                        }
                    };
                }).ToList();

                // 指标达标情况筛选
                if (!string.IsNullOrEmpty(input.IndicatorStatus))
                {
                    outputList = input.IndicatorStatus switch
                    {
                        "all_reached" => outputList.Where(x =>
                            x.PerformanceIndicator.Reached == "是" &&
                            x.HeadCountIndicator.Reached == "是" &&
                            (x.ConsumeIndicator.IsAssessed ? x.ConsumeIndicator.Reached == "是" : true)).ToList(),
                        "partial" => outputList.Where(x =>
                            (x.PerformanceIndicator.Reached == "是" || x.HeadCountIndicator.Reached == "是" || (x.ConsumeIndicator.IsAssessed && x.ConsumeIndicator.Reached == "是")) &&
                            !(x.PerformanceIndicator.Reached == "是" && x.HeadCountIndicator.Reached == "是" && (x.ConsumeIndicator.IsAssessed ? x.ConsumeIndicator.Reached == "是" : true)) &&
                            !(x.PerformanceIndicator.Reached == "否" && x.HeadCountIndicator.Reached == "否" && (x.ConsumeIndicator.IsAssessed ? x.ConsumeIndicator.Reached == "否" : true))).ToList(),
                        "all_unreached" => outputList.Where(x =>
                            x.PerformanceIndicator.Reached == "否" &&
                            x.HeadCountIndicator.Reached == "否" &&
                            (x.ConsumeIndicator.IsAssessed ? x.ConsumeIndicator.Reached == "否" : true)).ToList(),
                        _ => outputList
                    };
                }

                // 计算汇总统计
                var summary = new StoreManagerIndicatorsComparisonSummaryOutput
                {
                    TotalCount = outputList.Count,
                    AllReachedCount = outputList.Count(x =>
                        x.PerformanceIndicator.Reached == "是" &&
                        x.HeadCountIndicator.Reached == "是" &&
                        (x.ConsumeIndicator.IsAssessed ? x.ConsumeIndicator.Reached == "是" : true)),
                    PartialReachedCount = outputList.Count(x =>
                        (x.PerformanceIndicator.Reached == "是" || x.HeadCountIndicator.Reached == "是" || (x.ConsumeIndicator.IsAssessed && x.ConsumeIndicator.Reached == "是")) &&
                        !(x.PerformanceIndicator.Reached == "是" && x.HeadCountIndicator.Reached == "是" && (x.ConsumeIndicator.IsAssessed ? x.ConsumeIndicator.Reached == "是" : true)) &&
                        !(x.PerformanceIndicator.Reached == "否" && x.HeadCountIndicator.Reached == "否" && (x.ConsumeIndicator.IsAssessed ? x.ConsumeIndicator.Reached == "否" : true))),
                    AllUnreachedCount = outputList.Count(x =>
                        x.PerformanceIndicator.Reached == "否" &&
                        x.HeadCountIndicator.Reached == "否" &&
                        (x.ConsumeIndicator.IsAssessed ? x.ConsumeIndicator.Reached == "否" : true)),
                    AveragePerformanceCompletionRate = outputList.Any()
                        ? Math.Round(outputList.Average(x => x.PerformanceIndicator.CompletionRate), 2)
                        : 0m,
                    AverageHeadCountCompletionRate = outputList.Any()
                        ? Math.Round(outputList.Average(x => x.HeadCountIndicator.CompletionRate), 2)
                        : 0m,
                    AverageConsumeCompletionRate = outputList.Where(x => x.ConsumeIndicator.IsAssessed).Any()
                        ? Math.Round(outputList.Where(x => x.ConsumeIndicator.IsAssessed).Average(x => x.ConsumeIndicator.CompletionRate), 2)
                        : 0m
                };

                return new
                {
                    list = outputList,
                    summary = summary
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取店长三项指标对比失败，统计月份：{StatisticsMonth}", input?.StatisticsMonth);
                throw NCCException.Oh($"获取店长三项指标对比失败：{ex.Message}");
            }
        }

        /// <summary>
        /// 获取店长全年三项指标数据
        /// </summary>
        /// <remarks>
        /// 统计指定店长在指定年份的所有月份的三项指标完成情况
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "employeeId": "店长ID",
        ///   "year": 2026
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - employeeId: 员工ID（店长ID，必填）
        /// - year: 年份（必填，如：2026）
        /// 
        /// 返回数据说明：
        /// - employeeId: 员工ID
        /// - employeeName: 员工姓名
        /// - year: 年份
        /// - monthlyData: 月度数据列表（1-12月）
        /// - yearlySummary: 全年汇总统计
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>店长全年三项指标数据</returns>
        /// <response code="200">成功返回统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("get-store-manager-yearly-indicators")]
        public async Task<StoreManagerYearlyIndicatorsOutput> GetStoreManagerYearlyIndicators([FromBody] StoreManagerYearlyIndicatorsInput input)
        {
            try
            {
                if (input == null || string.IsNullOrWhiteSpace(input.EmployeeId))
                {
                    throw NCCException.Oh("员工ID不能为空");
                }

                if (input.Year <= 0 || input.Year > 9999)
                {
                    throw NCCException.Oh("年份参数不正确");
                }

                // 构建月份列表（1-12月）
                var months = Enumerable.Range(1, 12).Select(m => $"{input.Year}{m:D2}").ToList();

                // 查询该店长全年的工资统计数据
                var salaryList = await _db.Queryable<LqStoreManagerSalaryStatisticsEntity>()
                    .Where(x => x.EmployeeId == input.EmployeeId && x.Position == "店长" && months.Contains(x.StatisticsMonth))
                    .OrderBy(x => x.StatisticsMonth)
                    .ToListAsync();

                // 转换为月度数据
                var monthlyData = new List<StoreManagerMonthlyIndicatorsOutput>();

                foreach (var month in months)
                {
                    var salary = salaryList.FirstOrDefault(x => x.StatisticsMonth == month);

                    if (salary != null)
                    {
                        // 判断是否旗舰店
                        bool isFlagshipStore = salary.StoreType.HasValue && salary.StoreType.Value == 2;

                        // 判断是否新店
                        bool isNewStore = salary.IsNewStore == "是";

                        // 计算完成度
                        decimal performanceCompletionRate = salary.StoreLifeline > 0
                            ? Math.Round((salary.StoreTotalPerformance / salary.StoreLifeline) * 100m, 2)
                            : 0m;

                        decimal headCountCompletionRate = salary.TargetHeadCount > 0
                            ? Math.Round((salary.HeadCount / salary.TargetHeadCount) * 100m, 2)
                            : 0m;

                        decimal consumeCompletionRate = isNewStore || salary.TargetConsume <= 0
                            ? 0m
                            : Math.Round((salary.StoreConsume / salary.TargetConsume) * 100m, 2);

                        monthlyData.Add(new StoreManagerMonthlyIndicatorsOutput
                        {
                            Month = month,
                            StoreId = salary.StoreId,
                            StoreName = salary.StoreName,
                            IsNewStore = salary.IsNewStore ?? "否",
                            StoreType = salary.StoreType,
                            IsFlagshipStore = isFlagshipStore,
                            PerformanceIndicator = new PerformanceIndicator
                            {
                                Target = salary.StoreLifeline,
                                Actual = salary.StoreTotalPerformance,
                                CompletionRate = performanceCompletionRate,
                                Reached = salary.PerformanceReached ?? "否"
                            },
                            HeadCountIndicator = new HeadCountIndicator
                            {
                                Target = salary.TargetHeadCount,
                                Actual = salary.HeadCount,
                                CompletionRate = headCountCompletionRate,
                                Reached = salary.HeadCountReached ?? "否"
                            },
                            ConsumeIndicator = new ConsumeIndicator
                            {
                                Target = salary.TargetConsume,
                                Actual = salary.StoreConsume,
                                CompletionRate = consumeCompletionRate,
                                Reached = salary.ConsumeReached ?? "否",
                                IsAssessed = !isNewStore
                            },
                            BaseSalary = new BaseSalaryInfo
                            {
                                FixedSalary = salary.BaseSalary,
                                UnreachedIndicatorCount = salary.UnreachedIndicatorCount,
                                AssessmentDeduction = salary.AssessmentDeduction,
                                FlagshipStoreDeduction = salary.FlagshipStoreDeduction,
                                ActualSalary = salary.ActualBaseSalary
                            }
                        });
                    }
                }

                // 获取店长姓名（从第一条记录获取，如果为空则从用户表查询）
                string employeeName = "";
                if (salaryList.Any())
                {
                    employeeName = salaryList.First().EmployeeName;
                }

                if (string.IsNullOrEmpty(employeeName))
                {
                    var user = await _db.Queryable<UserEntity>()
                        .Where(x => x.Id == input.EmployeeId)
                        .Select(x => x.RealName)
                        .FirstAsync();
                    employeeName = user ?? "";
                }

                // 计算全年汇总统计
                var workedMonths = monthlyData.Count;
                var yearlySummary = new StoreManagerYearlySummaryOutput
                {
                    TotalMonths = 12,
                    WorkedMonths = workedMonths,
                    AveragePerformanceCompletionRate = workedMonths > 0
                        ? Math.Round(monthlyData.Average(x => x.PerformanceIndicator.CompletionRate), 2)
                        : 0m,
                    AverageHeadCountCompletionRate = workedMonths > 0
                        ? Math.Round(monthlyData.Average(x => x.HeadCountIndicator.CompletionRate), 2)
                        : 0m,
                    AverageConsumeCompletionRate = monthlyData.Where(x => x.ConsumeIndicator.IsAssessed).Any()
                        ? Math.Round(monthlyData.Where(x => x.ConsumeIndicator.IsAssessed).Average(x => x.ConsumeIndicator.CompletionRate), 2)
                        : 0m,
                    AllReachedMonths = monthlyData.Count(x =>
                        x.PerformanceIndicator.Reached == "是" &&
                        x.HeadCountIndicator.Reached == "是" &&
                        (x.ConsumeIndicator.IsAssessed ? x.ConsumeIndicator.Reached == "是" : true)),
                    PartialReachedMonths = monthlyData.Count(x =>
                        (x.PerformanceIndicator.Reached == "是" || x.HeadCountIndicator.Reached == "是" || (x.ConsumeIndicator.IsAssessed && x.ConsumeIndicator.Reached == "是")) &&
                        !(x.PerformanceIndicator.Reached == "是" && x.HeadCountIndicator.Reached == "是" && (x.ConsumeIndicator.IsAssessed ? x.ConsumeIndicator.Reached == "是" : true)) &&
                        !(x.PerformanceIndicator.Reached == "否" && x.HeadCountIndicator.Reached == "否" && (x.ConsumeIndicator.IsAssessed ? x.ConsumeIndicator.Reached == "否" : true))),
                    AllUnreachedMonths = monthlyData.Count(x =>
                        x.PerformanceIndicator.Reached == "否" &&
                        x.HeadCountIndicator.Reached == "否" &&
                        (x.ConsumeIndicator.IsAssessed ? x.ConsumeIndicator.Reached == "否" : true)),
                    AverageActualSalary = workedMonths > 0
                        ? Math.Round(monthlyData.Average(x => x.BaseSalary.ActualSalary), 2)
                        : 0m,
                    TotalAssessmentDeduction = monthlyData.Sum(x => x.BaseSalary.AssessmentDeduction)
                };

                return new StoreManagerYearlyIndicatorsOutput
                {
                    EmployeeId = input.EmployeeId,
                    EmployeeName = employeeName,
                    Year = input.Year,
                    MonthlyData = monthlyData,
                    YearlySummary = yearlySummary
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取店长全年三项指标数据失败，员工ID：{EmployeeId}，年份：{Year}", input?.EmployeeId, input?.Year);
                throw NCCException.Oh($"获取店长全年三项指标数据失败：{ex.Message}");
            }
        }

        #endregion


    }
}