netcore/src/Modularity/Extend/NCC.Extend/LqStatisticsService.cs

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.lq_hytk_kjbsyj;
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_xh_kjbsyj;
using NCC.Extend.Entitys.lq_yjmxb;
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;
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
    }
    /// <summary>
    /// 部门信息
    /// </summary>
    public class DepartmentInfo
    {
        /// <summary>
        /// 部门ID
        /// </summary>
        public string DepartmentId { get; set; }
        /// <summary>
        /// 部门名称
        /// </summary>
        public string DepartmentName { get; set; }
        /// <summary>
        /// 父部门ID
        /// </summary>
        public string ParentId { get; set; }
    }
}