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_xdbhsj;
using NCC.Extend.Entitys.lq_xh_kjbsyj;
using NCC.Extend.Entitys.lq_ycsd_jsj;
using NCC.Extend.Entitys.lq_yjmxb;
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;

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>
        /// <param name="input">查询参数</param>
        /// <returns>科技部老师业绩统计结果</returns>
        [HttpPost("GetTechTeacherStatistics")]
        [AllowAnonymous]
        public async Task<List<TechTeacherSimpleStatisticsOutput>> GetTechTeacherStatistics(TechTeacherStatisticsInput input)
        {
            try
            { // 1. 从用户表获取所有科技部老师
                var allTeachers = await _db.Queryable<UserEntity>()
                    .Where(x => x.Gw == "科技老师")
                    .Select(x => new
                    {
                        TeacherId = x.Id,
                        TeacherName = x.RealName,
                        TeacherAccount = x.Account,
                    })
                    .ToListAsync();

                // 2. 获取业绩流水数据
                var flowQuery = _db.Queryable<VTechTeacherFlowEntity>();

                // 老师过滤
                if (!string.IsNullOrEmpty(input.TeacherId))
                {
                    flowQuery = flowQuery.Where(x => x.TeacherId == input.TeacherId);
                }

                if (!string.IsNullOrEmpty(input.TeacherName))
                {
                    flowQuery = flowQuery.Where(x => x.TeacherName.Contains(input.TeacherName));
                }

                // 日期过滤
                if (input.StartDate.HasValue)
                {
                    flowQuery = flowQuery.Where(x => x.BusinessDate >= input.StartDate.Value);
                }

                if (input.EndDate.HasValue)
                {
                    flowQuery = flowQuery.Where(x => x.BusinessDate <= input.EndDate.Value);
                }

                var flowRecords = await flowQuery.ToListAsync();

                // 3. 按老师分组统计业绩数据
                var teacherStatsDict = flowRecords
                    .GroupBy(x => new
                    {
                        x.TeacherId,
                        x.TeacherName,
                        x.TeacherAccount,
                    })
                    .ToDictionary(
                        g => g.Key,
                        g => new
                        {
                            ConsumeProjectCount = (int)g.Where(x => x.BusinessType == "耗卡").Sum(x => x.ProjectCount),
                            ConsumeAchievement = g.Where(x => x.BusinessType == "耗卡").Sum(x => x.Achievement),
                            OrderAchievement = g.Where(x => x.BusinessType == "开卡").Sum(x => x.Achievement),
                            OrderItemCount = g.Where(x => x.BusinessType == "开卡").Sum(x => x.ItemCount),
                            ConsumeItemCount = g.Where(x => x.BusinessType == "耗卡").Sum(x => x.ItemCount),
                        }
                    );

                // 4. 构建结果，包含所有老师
                var result = new List<TechTeacherSimpleStatisticsOutput>();

                foreach (var teacher in allTeachers)
                {
                    // 应用过滤条件
                    if (!string.IsNullOrEmpty(input.TeacherId) && teacher.TeacherId != input.TeacherId)
                        continue;

                    if (!string.IsNullOrEmpty(input.TeacherName) && !teacher.TeacherName.Contains(input.TeacherName))
                        continue;

                    var stats = teacherStatsDict.GetValueOrDefault(
                        new
                        {
                            TeacherId = teacher.TeacherId,
                            TeacherName = teacher.TeacherName,
                            TeacherAccount = teacher.TeacherAccount,
                        },
                        new
                        {
                            ConsumeProjectCount = 0,
                            ConsumeAchievement = 0m,
                            OrderAchievement = 0m,
                            OrderItemCount = 0,
                            ConsumeItemCount = 0,
                        }
                    );

                    var teacherStats = new TechTeacherSimpleStatisticsOutput
                    {
                        DepartmentName = "科技部", // 固定为科技部
                        TeacherName = teacher.TeacherName,
                        ConsumeProjectCount = stats.ConsumeProjectCount,
                        ConsumeAchievement = stats.ConsumeAchievement,
                        OrderAchievement = stats.OrderAchievement,
                        OrderItemCount = stats.OrderItemCount,
                        ConsumeItemCount = stats.ConsumeItemCount,
                    };

                    result.Add(teacherStats);
                }

                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取科技部老师业绩统计时发生错误");
                throw NCCException.Oh("获取科技部老师业绩统计失败", ex);
            }
        }

        #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}%\n\n"
                    + $"http://lvqian.antissoft.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);

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

                // 查询本月全门店统计数据
                var sql =
                    @"
                    SELECT 
                        SUM(target_performance) AS TotalTargetPerformance,
                        SUM(actual_performance) AS TotalActualPerformance,
                        CASE 
                            WHEN SUM(target_performance) > 0 
                            THEN (SUM(actual_performance) / SUM(target_performance)) * 100 
                            ELSE 0 
                        END AS TotalCompletionRate
                    FROM v_store_performance_simple";

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

                if (stats != null)
                {
                    var targetPerformance = Convert.ToDecimal(stats.TotalTargetPerformance ?? 0);
                    var actualPerformance = Convert.ToDecimal(stats.TotalActualPerformance ?? 0);
                    var completionRate = Convert.ToDecimal(stats.TotalCompletionRate ?? 0);

                    return new
                    {
                        TargetPerformance = targetPerformance,
                        ActualPerformance = actualPerformance,
                        CompletionRate = completionRate,
                        Month = now.ToString("yyyy年MM月"),
                    };
                }

                return new
                {
                    TargetPerformance = 0m,
                    ActualPerformance = 0m,
                    CompletionRate = 0m,
                    Month = now.ToString("yyyy年MM月"),
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取本月全门店统计数据时发生错误");
                return new
                {
                    TargetPerformance = 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 YEAR(jksyj.yjsj) = @year
                            AND MONTH(jksyj.yjsj) = @month
                            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 YEAR(jksyj.yjsj) = @year
                            AND MONTH(jksyj.yjsj) = @month
                            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 YEAR(hytk_jksyj.tksj) = @year
                            AND MONTH(hytk_jksyj.tksj) = @month
                        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": "张三",
        ///   "pageIndex": 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
            {
                var query = _db.Queryable<LqStatisticsPersonalPerformanceEntity>();

                // 添加查询条件
                query = query.WhereIF(!string.IsNullOrEmpty(input.StatisticsMonth), x => x.StatisticsMonth == input.StatisticsMonth);
                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.WhereIF(!string.IsNullOrEmpty(input.EmployeeId), x => x.EmployeeId == input.EmployeeId);
                query = query.WhereIF(!string.IsNullOrEmpty(input.EmployeeName), x => x.EmployeeName.Contains(input.EmployeeName));
                query = query.WhereIF(!string.IsNullOrEmpty(input.GoldTriangleId), x => x.GoldTriangleId == input.GoldTriangleId);
                query = query.WhereIF(!string.IsNullOrEmpty(input.Position), x => x.Position == input.Position);

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

                // 分页查询并映射到DTO
                var result = await query.Select(it => new LqStatisticsPersonalPerformanceListOutput
                {
                    Id = it.Id,
                    StatisticsMonth = it.StatisticsMonth,
                    StoreId = it.StoreId,
                    StoreName = it.StoreName,
                    GoldTriangleId = it.GoldTriangleId,
                    GoldTriangleName = it.GoldTriangleName,
                    Position = it.Position,
                    EmployeeId = it.EmployeeId,
                    EmployeeName = it.EmployeeName,
                    TotalPerformance = it.TotalPerformance,
                    BasePerformance = it.BasePerformance,
                    CooperationPerformance = it.CooperationPerformance,
                    OrderCount = it.OrderCount,
                    FirstOrderCount = it.FirstOrderCount,
                    UpgradeOrderCount = it.UpgradeOrderCount,
                    FirstOrderPerformance = it.FirstOrderPerformance,
                    UpgradeOrderPerformance = it.UpgradeOrderPerformance,
                    LastOrderDate = it.LastOrderDate,
                    FirstOrderDate = it.FirstOrderDate,
                    CreateTime = it.CreateTime,
                    RefundPerformance = it.RefundPerformance,
                    RefundCount = it.RefundCount,
                    ActualPerformance = it.ActualPerformance
                }).ToPagedListAsync(input.currentPage, input.pageSize);

                return new
                {
                    list = result.list,
                    pagination = new
                    {
                        pageIndex = input.currentPage,
                        pageSize = input.pageSize,
                        total = result.pagination.Total
                    }
                };
            }
            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.jkszh, 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.tkje), 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>
        /// <param name="input">查询参数</param>
        /// <returns>分页结果</returns>
        [HttpPost("get-gold-triangle-statistics-list")]
        public async Task<dynamic> GetGoldTriangleStatisticsList([FromBody] LqGoldTriangleStatisticsListQueryInput input)
        {
            try
            {
                var query = _db.Queryable<LqStatisticsGoldTriangleEntity>();

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

                // 按创建时间降序排序
                query = query.OrderBy(x => x.CreateTime, OrderByType.Desc);

                // 分页查询并映射到DTO
                var result = await query.Select(it => new LqGoldTriangleStatisticsListOutput
                {
                    Id = it.Id,
                    GoldTriangleId = it.GoldTriangleId,
                    GoldTriangleName = it.GoldTriangleName,
                    StatisticsMonth = it.StatisticsMonth,
                    StoreId = it.StoreId,
                    StoreName = it.StoreName,
                    OrderCount = it.OrderCount,
                    TotalPerformance = it.TotalPerformance,
                    FirstOrderDate = it.FirstOrderDate,
                    LastOrderDate = it.LastOrderDate,
                    CreateTime = it.CreateTime
                }).ToPagedListAsync(input.PageIndex, input.PageSize);

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

        /// <summary>
        /// 获取科技部开单业绩统计列表
        /// </summary>
        /// <param name="input">查询参数</param>
        /// <returns>分页结果</returns>
        [HttpPost("get-tech-performance-statistics-list")]
        public async Task<dynamic> GetTechPerformanceStatisticsList([FromBody] LqTechPerformanceStatisticsListQueryInput 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.TeacherName), x => x.TeacherName.Contains(input.TeacherName));
                query = query.WhereIF(!string.IsNullOrEmpty(input.StoreName), x => x.StoreName.Contains(input.StoreName));

                // 按创建时间降序排序
                query = query.OrderBy(x => x.CreateTime, OrderByType.Desc);

                // 分页查询并映射到DTO
                var result = await query.Select(it => new LqTechPerformanceStatisticsListOutput
                {
                    Id = it.Id,
                    StatisticsMonth = it.StatisticsMonth,
                    TeacherId = it.TeacherId,
                    TeacherName = it.TeacherName,
                    StoreId = it.StoreId,
                    StoreName = it.StoreName,
                    TotalPerformance = it.TotalPerformance,
                    OrderCount = it.OrderCount,
                    CreateTime = it.CreateTime
                }).ToPagedListAsync(input.PageIndex, input.PageSize);

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

        /// <summary>
        /// 获取门店耗卡业绩统计列表
        /// </summary>
        /// <param name="input">查询参数</param>
        /// <returns>分页结果</returns>
        [HttpPost("get-store-consume-performance-statistics-list")]
        public async Task<dynamic> GetStoreConsumePerformanceStatisticsList([FromBody] LqStoreConsumePerformanceStatisticsListQueryInput input)
        {
            try
            {
                var query = _db.Queryable<LqStatisticsStoreConsumePerformanceEntity>();

                // 添加查询条件
                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.OrderBy(x => x.CreateTime, OrderByType.Desc);

                // 分页查询并映射到DTO
                var pagedResult = await query.ToPagedListAsync(input.PageIndex, input.PageSize);

                var outputList = pagedResult.list.Select(it => new LqStoreConsumePerformanceStatisticsListOutput
                {
                    Id = it.Id,
                    StatisticsMonth = it.StatisticsMonth,
                    StoreId = it.StoreId,
                    StoreName = it.StoreName,
                    TotalPerformance = it.ConsumePerformance, // 使用ConsumePerformance作为总业绩
                    ConsumePerformance = it.ConsumePerformance,
                    OrderCount = (int)it.ConsumeQuantity, // 使用ConsumeQuantity作为订单数量
                    IsNewStore = false, // 暂时设为false，因为Entity中没有这个字段
                    CreateTime = it.CreateTime.HasValue ? it.CreateTime.Value : DateTime.Now
                }).ToList();

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

        /// <summary>
        /// 获取个人消耗业绩统计列表
        /// </summary>
        /// <param name="input">查询参数</param>
        /// <returns>分页结果</returns>
        [HttpPost("get-department-consume-performance-statistics-list")]
        public async Task<dynamic> GetDepartmentConsumePerformanceStatisticsList([FromBody] LqDepartmentConsumePerformanceStatisticsListQueryInput input)
        {
            try
            {
                var query = _db.Queryable<LqStatisticsDepartmentConsumePerformanceEntity>();

                // 添加查询条件
                query = query.WhereIF(!string.IsNullOrEmpty(input.StatisticsMonth), x => x.StatisticsMonth == input.StatisticsMonth);
                query = query.WhereIF(!string.IsNullOrEmpty(input.EmployeeName), x => x.UserName.Contains(input.EmployeeName));
                query = query.WhereIF(!string.IsNullOrEmpty(input.StoreName), x => x.StoreName.Contains(input.StoreName));
                query = query.WhereIF(!string.IsNullOrEmpty(input.Position), x => x.DepartmentType.Contains(input.Position));

                // 按创建时间降序排序
                query = query.OrderBy(x => x.CreateTime, OrderByType.Desc);

                // 分页查询并映射到DTO
                var pagedResult = await query.ToPagedListAsync(input.PageIndex, input.PageSize);

                var outputList = pagedResult.list.Select(it => new LqDepartmentConsumePerformanceStatisticsListOutput
                {
                    Id = it.Id,
                    StatisticsMonth = it.StatisticsMonth,
                    EmployeeId = it.UserId,
                    EmployeeName = it.UserName,
                    StoreId = it.StoreId,
                    StoreName = it.StoreName,
                    Position = it.DepartmentType,
                    TotalPerformance = it.ConsumePerformance, // 使用ConsumePerformance作为总业绩
                    ConsumePerformance = it.ConsumePerformance,
                    OrderCount = it.ConsumeQuantity, // 使用ConsumeQuantity作为订单数量
                    HeadCount = it.HeadCount, // 人头数
                    PersonCount = it.PersonCount, // 人次
                    CreateTime = it.CreateTime.HasValue ? it.CreateTime.Value : DateTime.Now
                }).ToList();

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

        /// <summary>
        /// 获取门店总业绩统计列表
        /// </summary>
        /// <param name="input">查询参数</param>
        /// <returns>分页结果</returns>
        [HttpPost("get-store-total-performance-statistics-list")]
        public async Task<dynamic> GetStoreTotalPerformanceStatisticsList([FromBody] LqStoreTotalPerformanceStatisticsListQueryInput input)
        {
            try
            {
                var query = _db.Queryable<LqStatisticsStoreTotalPerformanceEntity>();

                // 添加查询条件
                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.OrderBy(x => x.CreateTime, OrderByType.Desc);

                // 分页查询并映射到DTO
                var pagedResult = await query.ToPagedListAsync(input.PageIndex, input.PageSize);

                var outputList = pagedResult.list.Select(it => new LqStoreTotalPerformanceStatisticsListOutput
                {
                    Id = it.Id,
                    StatisticsMonth = it.StatisticsMonth,
                    StoreId = it.StoreId,
                    StoreName = it.StoreName,
                    TotalPerformance = it.TotalPerformance,
                    DebtAmount = it.DebtAmount,
                    TotalOrderPerformance = it.TotalOrderPerformance,
                    StorageDeductionAmount = it.StorageDeductionAmount,
                    ItemQuantity = it.ItemQuantity,
                    FirstOrderCount = it.FirstOrderCount,
                    UpgradeOrderCount = it.UpgradeOrderCount,
                    RefundAmount = it.RefundAmount,
                    RefundCount = it.RefundCount,
                    CreateTime = it.CreateTime.HasValue ? it.CreateTime.Value : DateTime.Now,
                    ActualPerformance = it.ActualPerformance
                }).ToList();

                return new
                {
                    list = outputList,
                    pagination = new
                    {
                        pageIndex = input.PageIndex,
                        pageSize = input.PageSize,
                        total = pagedResult.pagination.Total
                    }
                };
            }
            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: 消耗项目数
        /// - RefundCount: 退卡数量
        /// - RefundAmount: 退卡金额
        /// - HeadCount: 人头（月度去重客户数）
        /// - PersonCount: 人次（日度去重客户数）
        /// </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 consumeProjectCount = await GetConsumeProjectCount(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 = consumeProjectCount
                };
            }
            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(*) 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(*) as Count,
                    COALESCE(SUM(jksyj), 0) as Amount
                FROM lq_hytk_jksyj
                WHERE jkszh = '{userId}'
                    AND F_IsEffective = 1
                    AND DATE_FORMAT(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)
        {
            var sql = $@"
                SELECT COUNT(DISTINCT hyhk.hy) as Count
                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.ToInt32(result.FirstOrDefault()?.Count ?? 0);
        }

        /// <summary>
        /// 统计人次（日度去重客户数）
        /// </summary>
        private async Task<int> GetPersonCount(string userId, string month)
        {
            var sql = $@"
                SELECT COUNT(DISTINCT CONCAT(hyhk.hy, '-', DATE(hyhk.hksj))) as Count
                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.ToInt32(result.FirstOrDefault()?.Count ?? 0);
        }

        /// <summary>
        /// 统计开单项目数
        /// </summary>
        private async Task<int> GetBillingProjectCount(string userId, string month)
        {
            var sql = $@"
                SELECT COUNT(DISTINCT pxmx.px) as Count
                FROM lq_kd_jksyj jksyj
                INNER JOIN lq_kd_pxmx pxmx ON jksyj.F_kdpxid = pxmx.F_Id
                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);
            return Convert.ToInt32(result.FirstOrDefault()?.Count ?? 0);
        }

        /// <summary>
        /// 统计消耗项目数
        /// </summary>
        private async Task<int> GetConsumeProjectCount(string userId, string month)
        {
            var sql = $@"
                SELECT COUNT(DISTINCT pxmx.px) 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);
        }

        #endregion

    }
}