antissoft / lvqianmeiye_ERP

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 
                        jksyj.jkszh AS EmployeeId,
                        u.F_REALNAME AS EmployeeName,
                        u.F_MDID AS StoreId,
                        COALESCE(md.dm, '') AS StoreName,
                        COALESCE(jsj.F_Id, '') AS GoldTriangleId,
                        COALESCE(jsj.jsj, '') AS GoldTriangleName,
                        CASE 
                            WHEN jsjUser.is_leader = 1 THEN '顾问'
                            ELSE COALESCE(u.F_GW, '')
                        END AS Position,
                        COUNT(DISTINCT jksyj.glkdbh) AS OrderCount,
                        COUNT(DISTINCT CASE WHEN kd.sfskdd = '是' THEN jksyj.glkdbh END) AS FirstOrderCount,
                        COUNT(DISTINCT CASE WHEN kd.sfskdd = '否' THEN jksyj.glkdbh END) AS UpgradeOrderCount,
                        SUM(CASE WHEN kd.sfskdd = '是' THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END) AS FirstOrderPerformance,
                        SUM(CASE WHEN kd.sfskdd = '否' THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END) AS UpgradeOrderPerformance,
                        MAX(jksyj.yjsj) AS LastOrderDate,
                        MIN(jksyj.yjsj) AS FirstOrderDate,
                        SUM(
                            CASE 
                                WHEN xmzl.fl3 = '合作业绩' THEN CAST(jksyj.jksyj AS DECIMAL(18,2))
                                ELSE 0
                            END
                        ) AS CooperationPerformance,
                        SUM(
                            CASE 
                                WHEN xmzl.fl3 IS NULL OR xmzl.fl3 != '合作业绩' THEN CAST(jksyj.jksyj AS DECIMAL(18,2))
                                ELSE 0
                            END
                        ) AS BasePerformance,
                        SUM(CAST(jksyj.jksyj AS DECIMAL(18,2))) AS TotalPerformance
                    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 = kd.F_Id
                    INNER JOIN lq_xmzl xmzl ON pxmx.px = xmzl.F_Id
                    INNER JOIN BASE_USER u ON jksyj.jkszh = u.F_Id
                    LEFT JOIN lq_mdxx md ON u.F_MDID = md.F_Id
                    LEFT JOIN lq_ycsd_jsj jsj ON u.F_MDID = jsj.md AND jsj.yf = @statisticsMonth
                    LEFT JOIN (
                        SELECT DISTINCT user_id, F_Month, MAX(is_leader) as is_leader
                        FROM lq_jinsanjiao_user 
                        WHERE F_Month = @statisticsMonth
                        GROUP BY user_id, F_Month
                    ) jsjUser ON jksyj.jkszh = jsjUser.user_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
                    GROUP BY 
                        jksyj.jkszh,
                        u.F_REALNAME,
                        u.F_MDID,
                        md.dm,
                        jsj.F_Id,
                        jsj.jsj,
                        jsjUser.is_leader,
                        u.F_GW
                    ORDER BY 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),
                    BasePerformance = Convert.ToDecimal(stats.BasePerformance ?? 0),
                    CooperationPerformance = Convert.ToDecimal(stats.CooperationPerformance ?? 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.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
                }).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
                    FROM lq_xh_jksyj jksyj
                    INNER JOIN lq_xh_hyhk hyhk ON jksyj.glkdbh = hyhk.F_Id AND hyhk.F_IsEffective = 1
                    LEFT JOIN lq_mdxx md ON hyhk.md = md.F_Id
                    WHERE jksyj.F_IsEffective = 1
                        AND DATE_FORMAT(hyhk.hksj, '%Y%m') = @statisticsMonth
                    GROUP BY jksyj.jkszh, jksyj.jksxm, hyhk.md, md.mdbm, md.dm";

                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,
                        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
                    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,
                        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 
                        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(pxmx.F_Id) as F_ItemQuantity,
                        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 kd.djmd = md.F_Id
                    LEFT JOIN lq_kd_pxmx pxmx ON kd.F_Id = pxmx.glkdbh AND pxmx.F_IsEffective = 1
                    WHERE kd.F_IsEffective = 1
                        AND DATE_FORMAT(kd.kdrq, '%Y%m') = @statisticsMonth
                    GROUP BY kd.djmd, md.dm";

                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),
                    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,
                        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,
                    PerformanceRatio = x.Percentage,
                    NewCustomerPerformance = x.NewCustomerPerformance,
                    NewCustomerConversionRate = x.NewCustomerConversionRate,
                    NewCustomerPoint = x.NewCustomerPoint,
                    UpgradePerformance = x.UpgradePerformance,
                    UpgradePoint = x.UpgradePoint,
                    Consumption = x.Consumption,
                    ProjectCount = x.ProjectCount,
                    AttendanceDays = x.CustomerCount,
                    StoreDays = 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,
                    COALESCE(u.F_GW, '') 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
                    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 = @statisticsMonth 
                        AND jsu.status = 'ACTIVE'
                        AND (jsu.F_DeleteMark IS NULL OR jsu.F_DeleteMark = 0)
                        AND jsj.yf = @statisticsMonth
                ) 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_unicode_ci = p.F_EmployeeId COLLATE utf8mb4_unicode_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_ProjectCount = p.F_OrderCount,
                    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_unicode_ci = st.F_StoreId COLLATE utf8mb4_unicode_ci 
                    AND s.F_StatisticsMonth = st.F_StatisticsMonth
                SET 
                    s.F_StoreTotalPerformance = st.F_TotalPerformance
                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_unicode_ci = gt.F_GoldTriangleId COLLATE utf8mb4_unicode_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
            });

            _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 = (int)it.ConsumeQuantity, // 使用ConsumeQuantity作为订单数量
                    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,
                    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}");
            }
        }

        #endregion

    }
}