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.LqStatisticsTechPerformance;

  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_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_tech_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