antissoft / lvqianmeiye_ERP

  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Threading.Tasks;
  using Mapster;
  using Microsoft.AspNetCore.Mvc;
  using NCC.ClayObject;
  using NCC.Common.Configuration;
  using NCC.Common.Core.Manager;

  using NCC.Common.Enum;
  using NCC.Common.Extension;
  using NCC.Common.Filter;

  using NCC.Common.Helper;
  using NCC.Common.Model.NPOI;
  using NCC.DataEncryption;

  using NCC.Dependency;
  using NCC.DynamicApiController;

  using NCC.Extend.Entitys.Dto.LqTkjlb;

  using NCC.Extend.Entitys.Enum;

  using NCC.Extend.Entitys.lq_event;

  using NCC.Extend.Entitys.lq_eventuser;

  using NCC.Extend.Entitys.lq_kd_kdjlb;

  using NCC.Extend.Entitys.lq_khxx;

  using NCC.Extend.Entitys.lq_mdxx;

  using NCC.Extend.Entitys.lq_tkjlb;
  using NCC.Extend.Interfaces.LqTkjlb;
  using NCC.FriendlyException;

  using NCC.JsonSerialization;

  using NCC.System.Entitys.Permission;

  using SqlSugar;
  using Yitter.IdGenerator;

  
  namespace NCC.Extend.LqTkjlb
  {
      /// <summary>
      /// 拓客管理服务
      /// </summary>

      [ApiDescriptionSettings(Tag = "绿纤拓客管理服务", Name = "LqTkjlb", Order = 200)]

      [Route("api/Extend/[controller]")]
      public class LqTkjlbService : ILqTkjlbService, IDynamicApiController, ITransient
      {
          private readonly ISqlSugarRepository<LqTkjlbEntity> _lqTkjlbRepository;
          private readonly SqlSugarScope _db;
          private readonly IUserManager _userManager;
  
          /// <summary>
          /// 初始化一个<see cref="LqTkjlbService"/>类型的新实例
          /// </summary>

          public LqTkjlbService(ISqlSugarRepository<LqTkjlbEntity> lqTkjlbRepository, IUserManager userManager)

          {

              _lqTkjlbRepository = lqTkjlbRepository;

              _db = _lqTkjlbRepository.Context;
              _userManager = userManager;
          }
  

          #region 获取拓客管理

          /// <summary>
          /// 获取拓客管理
          /// </summary>
          /// <param name="id">参数</param>
          /// <returns></returns>
          [HttpGet("{id}")]
          public async Task<dynamic> GetInfo(string id)
          {

              // 使用SqlFunc在查询时直接获取用户名和活动名称
              var result = await _db.Queryable<LqTkjlbEntity>()
                  .Where(p => p.Id == id)
                  .Select(p => new LqTkjlbInfoOutput
                  {
                      id = p.Id,
                      expansionTime = p.ExpansionTime,
                      expansionUserId = p.ExpansionUserId,
                      expansionUserName = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == p.ExpansionUserId).Select(u => u.RealName),
                      customerName = p.CustomerName,
                      customerPhone = p.CustomerPhone,
                      buyNumber = p.BuyNumber,
                      paymentMethod = p.PaymentMethod,
                      isAddWeChat = p.IsAddWeChat,
                      remarks = p.Remarks,
                      storeId = p.StoreId,
                      teamName = p.TeamName,
                      eventId = p.EventId,
                      eventName = SqlFunc.Subqueryable<LqEventEntity>().Where(e => e.Id == p.EventId).Select(e => e.EventName),

                      storeName = SqlFunc.Subqueryable<LqMdxxEntity>().Where(u => u.Id == p.StoreId).Select(u => u.Dm)

                  })
                  .FirstAsync();
  
              return result;

          }

          #endregion

          #region 获取拓客管理列表

  
          /// <summary>

          /// 获取拓客管理列表
          /// </summary>
          /// <param name="input">请求参数</param>
          /// <returns></returns>

          [HttpGet("")]
          public async Task<dynamic> GetList([FromQuery] LqTkjlbListQueryInput input)
          {
              var sidx = input.sidx == null ? "id" : input.sidx;

              List<string> queryTksj = input.expansionTime != null ? input.expansionTime.Split(',').ToObeject<List<string>>() : null;

              DateTime? startTksj = queryTksj != null ? Ext.GetDateTime(queryTksj.First()) : null;
              DateTime? endTksj = queryTksj != null ? Ext.GetDateTime(queryTksj.Last()) : null;
              var data = await _db.Queryable<LqTkjlbEntity>()
                  .WhereIF(!string.IsNullOrEmpty(input.id), p => p.Id.Contains(input.id))

                  .WhereIF(queryTksj != null, p => p.ExpansionTime >= new DateTime(startTksj.ToDate().Year, startTksj.ToDate().Month, startTksj.ToDate().Day, 0, 0, 0))
                  .WhereIF(queryTksj != null, p => p.ExpansionTime <= new DateTime(endTksj.ToDate().Year, endTksj.ToDate().Month, endTksj.ToDate().Day, 23, 59, 59))
                  .WhereIF(!string.IsNullOrEmpty(input.expansionUserId), p => p.ExpansionUserId.Equals(input.expansionUserId))
                  .WhereIF(!string.IsNullOrEmpty(input.customerName), p => p.CustomerName.Contains(input.customerName))
                  .WhereIF(!string.IsNullOrEmpty(input.customerPhone), p => p.CustomerPhone.Contains(input.customerPhone))
                  .WhereIF(!string.IsNullOrEmpty(input.buyNumber), p => p.BuyNumber.Equals(input.buyNumber))
                  .WhereIF(!string.IsNullOrEmpty(input.paymentMethod), p => p.PaymentMethod.Equals(input.paymentMethod))
                  .WhereIF(!string.IsNullOrEmpty(input.isAddWeChat), p => p.IsAddWeChat.Equals(input.isAddWeChat))
                  .WhereIF(!string.IsNullOrEmpty(input.remarks), p => p.Remarks.Contains(input.remarks))
                  .WhereIF(!string.IsNullOrEmpty(input.storeId), p => p.StoreId.Contains(input.storeId))
                  .WhereIF(!string.IsNullOrEmpty(input.teamName), p => p.TeamName.Contains(input.teamName))
                  .WhereIF(!string.IsNullOrEmpty(input.eventId), p => p.EventId.Contains(input.eventId))

                  .Select(it => new LqTkjlbListOutput

                  {
                      id = it.Id,

                      expansionTime = it.ExpansionTime,
                      expansionUserId = it.ExpansionUserId,

                      expansionUserName = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == it.ExpansionUserId).Select(u => u.RealName),

                      customerName = it.CustomerName,
                      customerPhone = it.CustomerPhone,
                      buyNumber = it.BuyNumber,
                      paymentMethod = it.PaymentMethod,
                      isAddWeChat = it.IsAddWeChat,
                      remarks = it.Remarks,
                      storeId = it.StoreId,

                      storeName = SqlFunc.Subqueryable<LqMdxxEntity>().Where(u => u.Id == it.StoreId).Select(u => u.Dm),

                      teamName = it.TeamName,

                      eventId = it.EventId,
                      eventName = SqlFunc.Subqueryable<LqEventEntity>().Where(u => u.Id == it.EventId).Select(u => u.EventName),

                      depId = it.DepId,
                      depName = SqlFunc.Subqueryable<OrganizeEntity>().Where(u => u.Id == it.DepId).Select(u => u.FullName),

                  })
                  .MergeTable()
                  .OrderBy(sidx + " " + input.sort)
                  .ToPagedListAsync(input.currentPage, input.pageSize);
              return PageResult<LqTkjlbListOutput>.SqlSugarPageResult(data);

          }

          #endregion

          #region 新建拓客管理

          /// <summary>
          /// 新建拓客管理
          /// </summary>
          /// <param name="input">参数</param>
          /// <returns></returns>
          [HttpPost("")]

          public async Task<dynamic> Create([FromBody] LqTkjlbCrInput input)

          {
              var userInfo = await _userManager.GetUserInfo();

              //通过input.dhhm去查询用户信息

              var user = _db.Queryable<LqKhxxEntity>().Where(u => u.Sjh == input.customerPhone).Any();

              if (user)

              {

                  return NCCException.Oh("该手机号码已存在于会员或线索池中");

              }

              // 使用事务确保数据一致性
              var result = await _db.Ado.UseTranAsync(async () =>
              {
                  //通过input.eventId去查询拓客活动信息

                  var eventUserInfoList = await _db.Queryable<LqEventUserEntity>().Where(u => u.EventId == input.eventId && u.UserId == input.expansionUserId).ToListAsync();

                  if (eventUserInfoList == null || eventUserInfoList.Count == 0)
                  {
                      throw NCCException.Oh("未找到对应的拓客活动用户信息，请确认活动ID和用户ID是否正确");
                  }

                  var MemberNumber = "LQ" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
                  var MemberId = YitIdHelper.NextId().ToString();

                  var eventUserInfo = eventUserInfoList.First();

                  // 创建拓客记录
                  var entity = input.Adapt<LqTkjlbEntity>();
                  entity.Id = YitIdHelper.NextId().ToString();

                  entity.TeamName = eventUserInfo.TeamName;
                  entity.StoreId = eventUserInfo.StoreId;
                  entity.DepId = eventUserInfo.DepId;
                  entity.ExpansionTime = DateTime.Now;

                  entity.MemberId = MemberId;

                  var isOk = await _db.Insertable(entity).IgnoreColumns(ignoreNullColumn: true).ExecuteCommandAsync();
                  if (!(isOk > 0))
                      throw NCCException.Oh("创建拓客记录失败");
                  // 创建客户信息
                  LqKhxxEntity MemberInfo = new LqKhxxEntity();

                  MemberInfo.Id = MemberId;

                  MemberInfo.Khmc = entity.CustomerName;
                  MemberInfo.Sjh = input.customerPhone; // 设置手机号

                  MemberInfo.Khlx = MemberTypeEnum.线索.GetHashCode().ToString();

                  MemberInfo.Dah = MemberNumber;

                  MemberInfo.Jdqd = "19.9卡";

                  //找到input.expansionUserId的用户信息
                  var userInfo = await _db.Queryable<UserEntity>().Where(u => u.Id == input.expansionUserId).FirstAsync();

                  //判断 岗位是否为健康师，如果是健康师，则设置拓客人员是健康师

                  MemberInfo.ExpandUser = input.expansionUserId;

                  MemberInfo.Gsmd = eventUserInfo.StoreId;

                  var memberResult = await _db.Insertable(MemberInfo).IgnoreColumns(ignoreNullColumn: true).ExecuteCommandAsync();
                  if (!(memberResult > 0))

                  {

                      throw NCCException.Oh("创建客户信息失败");

                  }
                  var storeinfo = await _db.Queryable<LqMdxxEntity>().Where(u => u.Id == eventUserInfo.StoreId).FirstAsync();
                  return new { entity = entity, storeinfo = storeinfo };

              });

              if (!result.IsSuccess)
              {

                  return NCCException.Oh($"创建拓客记录失败：{result.ErrorMessage}");

              }

              return result.Data;

          }

          #endregion

          #region 获取拓客管理无分页列表

          /// <summary>

          /// 获取拓客管理无分页列表
          /// </summary>
          /// <param name="input">请求参数</param>
          /// <returns></returns>

          [NonAction]
          public async Task<dynamic> GetNoPagingList([FromQuery] LqTkjlbListQueryInput input)
          {
              var sidx = input.sidx == null ? "id" : input.sidx;

              List<string> queryExpansionTime = input.expansionTime != null ? input.expansionTime.Split(',').ToObeject<List<string>>() : null;
              DateTime? startExpansionTime = queryExpansionTime != null ? Ext.GetDateTime(queryExpansionTime.First()) : null;
              DateTime? endExpansionTime = queryExpansionTime != null ? Ext.GetDateTime(queryExpansionTime.Last()) : null;

              var data = await _db.Queryable<LqTkjlbEntity>()
                  .WhereIF(!string.IsNullOrEmpty(input.id), p => p.Id.Contains(input.id))

                  .WhereIF(
                      queryExpansionTime != null,
                      p => p.ExpansionTime >= new DateTime(startExpansionTime.ToDate().Year, startExpansionTime.ToDate().Month, startExpansionTime.ToDate().Day, 0, 0, 0)
                  )
                  .WhereIF(queryExpansionTime != null, p => p.ExpansionTime <= new DateTime(endExpansionTime.ToDate().Year, endExpansionTime.ToDate().Month, endExpansionTime.ToDate().Day, 23, 59, 59))
                  .WhereIF(!string.IsNullOrEmpty(input.expansionUserId), p => p.ExpansionUserId.Equals(input.expansionUserId))
                  .WhereIF(!string.IsNullOrEmpty(input.customerName), p => p.CustomerName.Contains(input.customerName))
                  .WhereIF(!string.IsNullOrEmpty(input.customerPhone), p => p.CustomerPhone.Contains(input.customerPhone))
                  .WhereIF(!string.IsNullOrEmpty(input.buyNumber), p => p.BuyNumber.ToString().Equals(input.buyNumber))
                  .WhereIF(!string.IsNullOrEmpty(input.paymentMethod), p => p.PaymentMethod.Equals(input.paymentMethod))
                  .WhereIF(!string.IsNullOrEmpty(input.isAddWeChat), p => p.IsAddWeChat.Equals(input.isAddWeChat))
                  .WhereIF(!string.IsNullOrEmpty(input.remarks), p => p.Remarks.Contains(input.remarks))
                  .WhereIF(!string.IsNullOrEmpty(input.storeId), p => p.StoreId.Contains(input.storeId))
                  .WhereIF(!string.IsNullOrEmpty(input.teamName), p => p.TeamName.Contains(input.teamName))
                  .WhereIF(!string.IsNullOrEmpty(input.eventId), p => p.EventId.Equals(input.eventId))

                  .Select(it => new LqTkjlbListOutput

                  {
                      id = it.Id,

                      expansionTime = it.ExpansionTime,
                      expansionUserId = it.ExpansionUserId,
                      expansionUserName = it.ExpansionUserId, // 这里需要根据实际业务逻辑获取用户名
                      customerName = it.CustomerName,
                      customerPhone = it.CustomerPhone,
                      buyNumber = it.BuyNumber,
                      paymentMethod = it.PaymentMethod,
                      isAddWeChat = it.IsAddWeChat,
                      remarks = it.Remarks,
                      storeId = it.StoreId,
                      teamName = it.TeamName,
                      eventId = it.EventId,
                      eventName = it.EventId, // 这里需要根据实际业务逻辑获取活动名称

                  })
                  .MergeTable()
                  .OrderBy(sidx + " " + input.sort)
                  .ToListAsync();
              return data;

          }

          #endregion

          #region 获取拓客管理带中文名称的数据（简化版）

          /// <summary>

          /// 获取拓客管理带中文名称的数据（简化版，用于导出）

          /// </summary>

          /// <param name="eventId">活动编号（可选）</param>
          /// <param name="endTime">截止时间（可选）</param>

          /// <returns></returns>

          [NonAction]
          public async Task<List<TkjlbExportModel>> GetTkjlbWithChineseNamesSimple(string eventId = null, DateTime? endTime = null)
          {
              var data = await _db.Queryable<LqTkjlbEntity, LqMdxxEntity, LqEventEntity, UserEntity>(
                      (tk, md, ev, user) => tk.StoreId == md.Id && tk.EventId == ev.Id && tk.ExpansionUserId == user.Id
                  )
                  .WhereIF(!string.IsNullOrEmpty(eventId), (tk, md, ev, user) => tk.EventId == eventId)
                  .WhereIF(endTime.HasValue, (tk, md, ev, user) => tk.ExpansionTime <= endTime.Value)
                  .Select((tk, md, ev, user) => new TkjlbExportModel
                  {
                      id = tk.Id,
                      expansionTime = tk.ExpansionTime,
                      expansionUserId = tk.ExpansionUserId,
                      expansionUserName = user.RealName,
                      customerName = tk.CustomerName,
                      customerPhone = tk.CustomerPhone,
                      buyNumber = tk.BuyNumber,
                      paymentMethod = tk.PaymentMethod,
                      isAddWeChat = tk.IsAddWeChat,
                      remarks = tk.Remarks,
                      storeId = tk.StoreId,
                      storeName = md.Dm,
                      teamName = tk.TeamName,
                      eventId = tk.EventId,
                      eventName = ev.EventName,
                  })
                  .MergeTable()
                  .OrderBy("expansionTime desc")
                  .ToListAsync();
  
              return data;
          }
          #endregion
  
          #region 导出拓客管理
          /// <summary>
          /// 导出拓客管理（固定字段，显示中文名称）
          /// </summary>
          /// <param name="eventId">活动编号（可选）</param>
          /// <param name="endTime">截止时间（可选）</param>
          /// <returns>导出文件信息</returns>

          [HttpGet("Actions/Export")]

          public async Task<dynamic> Export([FromQuery] string eventId = null, [FromQuery] DateTime? endTime = null)

          {

              try

              {

                  var userInfo = await _userManager.GetUserInfo();
  
                  // 使用联合查询获取带中文名称的数据
                  var exportData = await GetTkjlbWithChineseNamesSimple(eventId, endTime);
  
                  // 检查数据是否为空
                  if (exportData == null || exportData.Count == 0)
                  {
                      return new { name = "拓客管理.xls", url = "", message = "没有找到符合条件的数据" };
                  }
  
                  // 固定导出字段
                  List<ParamsModel> paramList =
                      "[{\"value\":\"拓客编号\",\"field\":\"id\"},{\"value\":\"拓客时间\",\"field\":\"expansionTime\"},{\"value\":\"拓客人员ID\",\"field\":\"expansionUserId\"},{\"value\":\"拓客人员姓名\",\"field\":\"expansionUserName\"},{\"value\":\"顾客姓名\",\"field\":\"customerName\"},{\"value\":\"顾客电话\",\"field\":\"customerPhone\"},{\"value\":\"购买张数\",\"field\":\"buyNumber\"},{\"value\":\"支付方式\",\"field\":\"paymentMethod\"},{\"value\":\"是否加微信\",\"field\":\"isAddWeChat\"},{\"value\":\"备注\",\"field\":\"remarks\"},{\"value\":\"门店ID\",\"field\":\"storeId\"},{\"value\":\"门店名称\",\"field\":\"storeName\"},{\"value\":\"战队名称\",\"field\":\"teamName\"},{\"value\":\"活动ID\",\"field\":\"eventId\"},{\"value\":\"活动名称\",\"field\":\"eventName\"}]".ToList<ParamsModel>();
  
                  // 检查paramList是否为空
                  if (paramList == null || paramList.Count == 0)

                  {

                      return new { name = "拓客管理.xls", url = "", message = "参数列表解析失败" };

                  }

  
                  ExcelConfig excelconfig = new ExcelConfig();
                  excelconfig.FileName = "拓客管理" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                  excelconfig.HeadFont = "微软雅黑";
                  excelconfig.HeadPoint = 10;
                  excelconfig.IsAllSizeColumn = true;
                  excelconfig.ColumnModel = new List<ExcelColumnModel>();
  
                  // 固定字段列表
                  List<string> selectKeyList = new List<string> { "id", "expansionTime", "expansionUserId", "expansionUserName", "customerName", "customerPhone", "buyNumber", "paymentMethod", "isAddWeChat", "remarks", "storeId", "storeName", "teamName", "eventId", "eventName" };
  
                  foreach (var item in selectKeyList)
                  {
                      var isExist = paramList.Find(p => p.field == item);
                      if (isExist != null)
                      {
                          excelconfig.ColumnModel.Add(new ExcelColumnModel() { Column = isExist.field, ExcelColumn = isExist.value });
                      }
                  }
  
                  var addPath = FileVariable.TemporaryFilePath + excelconfig.FileName;
                  ExcelExportHelper<TkjlbExportModel>.Export(exportData, excelconfig, addPath);
                  var fileName = _userManager.UserId + "|" + excelconfig.FileName + "|temporary";
                  var output = new { name = excelconfig.FileName, url = "/api/File/Download?encryption=" + DESCEncryption.Encrypt(fileName, "NCC") };
                  return output;
              }
              catch (Exception ex)
              {
                  return new { name = "拓客管理" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls", url = "", message = $"导出失败: {ex.Message}", stackTrace = ex.StackTrace };

              }

          }

          #endregion

          #region 批量删除拓客管理

  
          /// <summary>
          /// 批量删除拓客管理
          /// </summary>
          /// <param name="ids">主键数组</param>
          /// <returns></returns>
          [HttpPost("batchRemove")]
          public async Task BatchRemove([FromBody] List<string> ids)
          {
              var entitys = await _db.Queryable<LqTkjlbEntity>().In(it => it.Id, ids).ToListAsync();
              if (entitys.Count > 0)
              {
                  try
                  {
                      //开启事务
                      _db.BeginTran();
                      //批量删除拓客管理

                      await _db.Deleteable<LqTkjlbEntity>().In(d => d.Id, ids).ExecuteCommandAsync();

                      //关闭事务
                      _db.CommitTran();
                  }
                  catch (Exception)
                  {
                      //回滚事务
                      _db.RollbackTran();
                      throw NCCException.Oh(ErrorCode.COM1002);
                  }
              }
          }

          #endregion

          #region 更新拓客管理

          /// <summary>
          /// 更新拓客管理
          /// </summary>
          /// <param name="id">主键</param>
          /// <param name="input">参数</param>
          /// <returns></returns>
          [HttpPut("{id}")]
          public async Task Update(string id, [FromBody] LqTkjlbUpInput input)
          {
              var entity = input.Adapt<LqTkjlbEntity>();
              var isOk = await _db.Updateable(entity).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommandAsync();

              if (!(isOk > 0))
                  throw NCCException.Oh(ErrorCode.COM1001);

          }

          #endregion

          #region 删除拓客管理

          /// <summary>
          /// 删除拓客管理
          /// </summary>
          /// <returns></returns>
          [HttpDelete("{id}")]
          public async Task Delete(string id)
          {
              var entity = await _db.Queryable<LqTkjlbEntity>().FirstAsync(p => p.Id == id);
              _ = entity ?? throw NCCException.Oh(ErrorCode.COM1005);
              var isOk = await _db.Deleteable<LqTkjlbEntity>().Where(d => d.Id == id).ExecuteCommandAsync();

              if (!(isOk > 0))
                  throw NCCException.Oh(ErrorCode.COM1002);

          }

          #endregion

          #region 获取拓客排行榜

          /// <summary>
          /// 获取拓客排行榜
          /// </summary>
          /// <returns></returns>
          [HttpGet("Actions/GetRanking")]
          public async Task<dynamic> GetRanking()
          {

              var data = await _db.Queryable<LqTkjlbEntity>()
                  .GroupBy(it => it.TeamName)
                  .Select(it => new LqTkjlbRankingOutput { teamName = it.TeamName, expansionUserCount = SqlFunc.AggregateCount(it.Id) })
                  .ToListAsync();

  
              // 在内存中排序并添加排名

              var sortedData = data.OrderByDescending(it => it.expansionUserCount).ToList();

              for (int i = 0; i < sortedData.Count; i++)
              {
                  sortedData[i].ranking = i + 1;
              }
  
              return sortedData;
          }

          #endregion

          #region 获取战队人员详细报表

          /// <summary>
          /// 获取战队人员详细报表
          /// </summary>
          /// <returns></returns>
          [HttpGet("Actions/GetTeamDetail")]
          public async Task<dynamic> GetTeamDetail()
          {
              var result = new List<LqTkjlbTeamDetailOutput>();
  
              // 获取所有战队

              var teams = await _db.Queryable<LqTkjlbEntity>().GroupBy(it => it.TeamName).Select(it => it.TeamName).ToListAsync();

  
              foreach (var team in teams)
              {
                  // 获取该战队所有人员的拓客数据
                  var teamMembers = await _db.Queryable<LqTkjlbEntity>()

                      .Where(it => it.TeamName == team)
                      .GroupBy(it => it.ExpansionUserId)

                      .Select(it => new
                      {

                          // tkry = it.Tkry,

                          tkry = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == it.ExpansionUserId).Select(u => u.RealName),

                          tkrs = SqlFunc.AggregateCount(it.Id),

                      })
                      .ToListAsync();
  
                  // 按拓客数量排序
                  var sortedMembers = teamMembers.OrderByDescending(m => m.tkrs).ToList();

                  // 计算战队总拓客数量
                  var totalCount = sortedMembers.Sum(m => m.tkrs);
  
                  // 构建战队详情
                  var teamDetail = new LqTkjlbTeamDetailOutput
                  {
                      sszd = team,
                      totalCount = totalCount,

                      members = new List<TeamMemberDetail>(),

                  };
  
                  // 添加成员详情
                  for (int i = 0; i < sortedMembers.Count; i++)
                  {
                      var member = sortedMembers[i];
                      var percentage = totalCount > 0 ? (int)Math.Round((double)member.tkrs / totalCount * 100) : 0;

  
                      teamDetail.members.Add(
                          new TeamMemberDetail
                          {
                              tkry = member.tkry,
                              tkrs = member.tkrs,
                              teamRanking = i + 1,
                              percentage = percentage,
                          }
                      );

                  }
  
                  result.Add(teamDetail);
              }
  
              // 按战队总拓客数量排序
              return result.OrderByDescending(t => t.totalCount).ToList();
          }

          #endregion

          #region 漏斗统计
          /// <summary>

          /// 获取拓客活动漏斗统计数据（优化版本）

          /// </summary>
          /// <param name="eventId">活动ID</param>
          /// <returns>漏斗统计数据</returns>
          [HttpGet("GetFunnelStatistics/{eventId}")]
          public async Task<dynamic> GetFunnelStatistics(string eventId)
          {
              try
              {

                  // 优化版本：使用子查询减少JOIN复杂度

                  var sql = @"
                      SELECT 
                          md.F_Id as store_id,
                          md.dm as store_name,

                          tk_stats.tk_count,
                          tk_stats.yaoy_count,
                          tk_stats.yy_count,
                          tk_stats.hk_count,
                          tk_stats.kd_count,
                          tk_stats.hk_amount,
                          tk_stats.kd_amount,
                          CASE 
                              WHEN tk_stats.tk_count > 0 
                              THEN ROUND(tk_stats.yy_count * 100.0 / tk_stats.tk_count, 2)
                              ELSE 0 
                          END as yy_conversion_rate,
                          CASE 
                              WHEN tk_stats.yy_count > 0 
                              THEN ROUND(tk_stats.hk_count * 100.0 / tk_stats.yy_count, 2)
                              ELSE 0 
                          END as hk_conversion_rate
                      FROM lq_mdxx md
                      LEFT JOIN (
                          SELECT 
                              tk.F_StoreId,
                              COUNT(DISTINCT tk.F_Id) as tk_count,
                              COUNT(DISTINCT yy.F_Id) as yaoy_count,
                              COUNT(DISTINCT yyjl.F_Id) as yy_count,
                              COUNT(DISTINCT xh.F_Id) as hk_count,
                              COUNT(DISTINCT kd.F_Id) as kd_count,
                              COALESCE(SUM(xh.xfje), 0) as hk_amount,
                              COALESCE(SUM(kd.sfyj), 0) as kd_amount
                          FROM lq_tkjlb tk
                          LEFT JOIN lq_yaoyjl yy ON tk.F_MemberId = yy.yykh AND yy.F_StoreId = tk.F_StoreId
                          LEFT JOIN lq_yyjl yyjl ON tk.F_MemberId = yyjl.gk AND yyjl.F_Status = '已确认'
                          LEFT JOIN lq_xh_hyhk xh ON tk.F_MemberId = xh.hy AND xh.F_IsEffective = 1
                          LEFT JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy AND kd.F_IsEffective = 1
                          WHERE tk.F_EventId = @eventId
                          GROUP BY tk.F_StoreId
                      ) tk_stats ON md.F_Id = tk_stats.F_StoreId
                      WHERE tk_stats.F_StoreId IS NOT NULL
                      ORDER BY tk_stats.tk_count DESC";

  
                  var result = await _db.Ado.SqlQueryAsync<dynamic>(sql, new { eventId });
  
                  return new
                  {
                      success = true,
                      data = result,
                      message = "获取漏斗统计数据成功"
                  };
              }
              catch (Exception ex)
              {
                  throw NCCException.Oh("获取漏斗统计数据失败：" + ex.Message);
              }
          }
  
          /// <summary>

          /// 获取拓客活动总体漏斗统计（优化版本）

          /// </summary>
          /// <param name="eventId">活动ID</param>
          /// <returns>总体漏斗统计数据</returns>
          [HttpGet("GetOverallFunnelStatistics/{eventId}")]
          public async Task<dynamic> GetOverallFunnelStatistics(string eventId)
          {
              try
              {
                  var sql = @"
                      SELECT 

                          SUM(tk_count) as total_tk_count,
                          SUM(yaoy_count) as total_yaoy_count,
                          SUM(yy_count) as total_yy_count,
                          SUM(hk_count) as total_hk_count,
                          SUM(kd_count) as total_kd_count,
                          SUM(hk_amount) as total_hk_amount,
                          SUM(kd_amount) as total_kd_amount,
                          CASE 
                              WHEN SUM(tk_count) > 0 
                              THEN ROUND(SUM(yy_count) * 100.0 / SUM(tk_count), 2)
                              ELSE 0 
                          END as overall_yy_conversion_rate,
                          CASE 
                              WHEN SUM(yy_count) > 0 
                              THEN ROUND(SUM(hk_count) * 100.0 / SUM(yy_count), 2)
                              ELSE 0 
                          END as overall_hk_conversion_rate
                      FROM (
                          SELECT 
                              COUNT(DISTINCT tk.F_Id) as tk_count,
                              COUNT(DISTINCT yy.F_Id) as yaoy_count,
                              COUNT(DISTINCT yyjl.F_Id) as yy_count,
                              COUNT(DISTINCT xh.F_Id) as hk_count,
                              COUNT(DISTINCT kd.F_Id) as kd_count,
                              COALESCE(SUM(xh.xfje), 0) as hk_amount,
                              COALESCE(SUM(kd.sfyj), 0) as kd_amount
                          FROM lq_tkjlb tk
                          LEFT JOIN lq_yaoyjl yy ON tk.F_MemberId = yy.yykh AND yy.F_StoreId = tk.F_StoreId
                          LEFT JOIN lq_yyjl yyjl ON tk.F_MemberId = yyjl.gk AND yyjl.F_Status = '已确认'
                          LEFT JOIN lq_xh_hyhk xh ON tk.F_MemberId = xh.hy AND xh.F_IsEffective = 1
                          LEFT JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy AND kd.F_IsEffective = 1
                          WHERE tk.F_EventId = @eventId
                      ) stats";

  
                  var result = await _db.Ado.SqlQueryAsync<dynamic>(sql, new { eventId });
  
                  return new
                  {
                      success = true,
                      data = result?.FirstOrDefault(),
                      message = "获取总体漏斗统计数据成功"
                  };
              }
              catch (Exception ex)
              {
                  throw NCCException.Oh("获取总体漏斗统计数据失败：" + ex.Message);

              }
          }
  
          /// <summary>
          /// 获取拓客活动漏斗统计数据（高性能版本 - 分步查询）
          /// </summary>
          /// <param name="eventId">活动ID</param>
          /// <returns>漏斗统计数据</returns>
          [HttpGet("GetFunnelStatisticsFast/{eventId}")]
          public async Task<dynamic> GetFunnelStatisticsFast(string eventId)
          {
              try
              {
                  // 第一步：获取拓客基础数据
                  var tkSql = @"
                      SELECT 
                          tk.F_StoreId,
                          md.dm as store_name,
                          COUNT(DISTINCT tk.F_Id) as tk_count
                      FROM lq_tkjlb tk
                      JOIN lq_mdxx md ON tk.F_StoreId = md.F_Id
                      WHERE tk.F_EventId = @eventId
                      GROUP BY tk.F_StoreId, md.dm";
  
                  var tkData = await _db.Ado.SqlQueryAsync<dynamic>(tkSql, new { eventId });
                  var tkDict = tkData.ToDictionary(x => (string)x.F_StoreId, x => x);
  
                  // 第二步：获取邀约数据
                  var yaoySql = @"
                      SELECT 
                          tk.F_StoreId,
                          COUNT(DISTINCT yy.F_Id) as yaoy_count
                      FROM lq_tkjlb tk
                      LEFT JOIN lq_yaoyjl yy ON tk.F_MemberId = yy.yykh AND yy.F_StoreId = tk.F_StoreId
                      WHERE tk.F_EventId = @eventId
                      GROUP BY tk.F_StoreId";
  
                  var yaoyData = await _db.Ado.SqlQueryAsync<dynamic>(yaoySql, new { eventId });
                  var yaoyDict = yaoyData.ToDictionary(x => (string)x.F_StoreId, x => (int)x.yaoy_count);
  
                  // 第三步：获取预约数据
                  var yySql = @"
                      SELECT 
                          tk.F_StoreId,
                          COUNT(DISTINCT yyjl.F_Id) as yy_count
                      FROM lq_tkjlb tk
                      LEFT JOIN lq_yyjl yyjl ON tk.F_MemberId = yyjl.gk AND yyjl.F_Status = '已确认'
                      WHERE tk.F_EventId = @eventId
                      GROUP BY tk.F_StoreId";
  
                  var yyData = await _db.Ado.SqlQueryAsync<dynamic>(yySql, new { eventId });
                  var yyDict = yyData.ToDictionary(x => (string)x.F_StoreId, x => (int)x.yy_count);
  
                  // 第四步：获取耗卡数据
                  var hkSql = @"
                      SELECT 
                          tk.F_StoreId,
                          COUNT(DISTINCT xh.F_Id) as hk_count,
                          COALESCE(SUM(xh.xfje), 0) as hk_amount
                      FROM lq_tkjlb tk
                      LEFT JOIN lq_xh_hyhk xh ON tk.F_MemberId = xh.hy AND xh.F_IsEffective = 1
                      WHERE tk.F_EventId = @eventId
                      GROUP BY tk.F_StoreId";
  
                  var hkData = await _db.Ado.SqlQueryAsync<dynamic>(hkSql, new { eventId });
                  var hkDict = hkData.ToDictionary(x => (string)x.F_StoreId, x => new { count = (int)x.hk_count, amount = (decimal)x.hk_amount });
  
                  // 第五步：获取开单数据
                  var kdSql = @"
                      SELECT 
                          tk.F_StoreId,
                          COUNT(DISTINCT kd.F_Id) as kd_count,
                          COALESCE(SUM(kd.sfyj), 0) as kd_amount
                      FROM lq_tkjlb tk
                      LEFT JOIN lq_kd_kdjlb kd ON tk.F_MemberId = kd.kdhy AND kd.F_IsEffective = 1
                      WHERE tk.F_EventId = @eventId
                      GROUP BY tk.F_StoreId";
  
                  var kdData = await _db.Ado.SqlQueryAsync<dynamic>(kdSql, new { eventId });
                  var kdDict = kdData.ToDictionary(x => (string)x.F_StoreId, x => new { count = (int)x.kd_count, amount = (decimal)x.kd_amount });
  
                  // 合并结果
                  var result = tkDict.Values.Select(tk => new
                  {
                      store_id = tk.F_StoreId,
                      store_name = tk.store_name,
                      tk_count = (int)tk.tk_count,
                      yaoy_count = yaoyDict.ContainsKey(tk.F_StoreId) ? yaoyDict[tk.F_StoreId] : 0,
                      yy_count = yyDict.ContainsKey(tk.F_StoreId) ? yyDict[tk.F_StoreId] : 0,
                      hk_count = hkDict.ContainsKey(tk.F_StoreId) ? hkDict[tk.F_StoreId].count : 0,
                      kd_count = kdDict.ContainsKey(tk.F_StoreId) ? kdDict[tk.F_StoreId].count : 0,
                      hk_amount = hkDict.ContainsKey(tk.F_StoreId) ? hkDict[tk.F_StoreId].amount : 0m,
                      kd_amount = kdDict.ContainsKey(tk.F_StoreId) ? kdDict[tk.F_StoreId].amount : 0m,
                      yy_conversion_rate = (int)tk.tk_count > 0 ? Math.Round((yyDict.ContainsKey(tk.F_StoreId) ? yyDict[tk.F_StoreId] : 0) * 100.0 / (int)tk.tk_count, 2) : 0,
                      hk_conversion_rate = (yyDict.ContainsKey(tk.F_StoreId) ? yyDict[tk.F_StoreId] : 0) > 0 ? Math.Round((hkDict.ContainsKey(tk.F_StoreId) ? hkDict[tk.F_StoreId].count : 0) * 100.0 / (yyDict.ContainsKey(tk.F_StoreId) ? yyDict[tk.F_StoreId] : 0), 2) : 0
                  }).OrderByDescending(x => x.tk_count).ToList();
  
                  return new
                  {
                      success = true,
                      data = result,
                      message = "获取漏斗统计数据成功（高性能版本）"
                  };
              }
              catch (Exception ex)
              {
                  throw NCCException.Oh("获取漏斗统计数据失败：" + ex.Message);