using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Http;
using System.Threading.Tasks;
using Mapster;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
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.Common;
using NCC.Extend.Entitys.Dto.LqKdDeductinfo;
using NCC.Extend.Entitys.Dto.LqKdKdjlb;
using NCC.Extend.Entitys.Enum;
using NCC.Extend.Entitys.lq_hytk_hytk;
using NCC.Extend.Entitys.lq_hytk_mx;
using NCC.Extend.Entitys.lq_hytk_jksyj;
using NCC.Extend.Entitys.lq_hytk_kjbsyj;
using NCC.Extend.Entitys.lq_jinsanjiao_user;
using NCC.Extend.Entitys.lq_kd_deductinfo;
using NCC.Extend.Entitys.lq_xh_hyhk;
using NCC.Extend.Entitys.lq_xh_pxmx;
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_kd_pxmx;
using NCC.Extend.Entitys.lq_khxx;
using NCC.Extend.Entitys.lq_xmzl;
using NCC.Extend.Interfaces.LqKdKdjlb;
using NCC.Extend.Utils;
using NCC.FriendlyException;
using NCC.JsonSerialization;
using NCC.System.Entitys.Permission;
using SqlSugar;
using Yitter.IdGenerator;
using NCC.Extend.Entitys.lq_package_info;
using NCC.Extend.Entitys.lq_mdxx;
using NCC.Extend.Entitys.lq_card_transfer_log;

namespace NCC.Extend.LqKdKdjlb
{
    /// <summary>
    /// 开单记录表服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "绿纤开单记录表服务", Name = "LqKdKdjlb", Order = 200)]
    [Route("api/Extend/[controller]")]
    public class LqKdKdjlbService : ILqKdKdjlbService, IDynamicApiController, ITransient
    {
        private readonly ISqlSugarRepository<LqKdKdjlbEntity> _lqKdKdjlbRepository;
        private readonly ISqlSugarRepository<LqKdJksyjEntity> _lqKdJksyjRepository;
        private readonly ISqlSugarRepository<LqKdKjbsyjEntity> _lqKdKjbsyjRepository;
        private readonly ISqlSugarRepository<LqKdPxmxEntity> _lqKdPxmxRepository;
        private readonly SqlSugarScope _db;
        private readonly IUserManager _userManager;
        private readonly WeChatBotService _weChatBotService;
        private readonly LqKdKdjlbStringGenerator _stringGenerator;
        private readonly ILogger<LqKdKdjlbService> _logger;

        /// <summary>
        /// 初始化一个<see cref="LqKdKdjlbService"/>类型的新实例
        /// </summary>
        public LqKdKdjlbService(
            ISqlSugarRepository<LqKdKdjlbEntity> lqKdKdjlbRepository,
            ISqlSugarRepository<LqKdJksyjEntity> lqKdJksyjRepository,
            ISqlSugarRepository<LqKdKjbsyjEntity> lqKdKjbsyjRepository,
            ISqlSugarRepository<LqKdPxmxEntity> lqKdPxmxRepository,
            IUserManager userManager,
            WeChatBotService weChatBotService,
            LqKdKdjlbStringGenerator stringGenerator,
            ILogger<LqKdKdjlbService> logger
        )
        {
            _lqKdKdjlbRepository = lqKdKdjlbRepository;
            _db = _lqKdKdjlbRepository.Context;
            _lqKdJksyjRepository = lqKdJksyjRepository;
            _lqKdKjbsyjRepository = lqKdKjbsyjRepository;
            _lqKdPxmxRepository = lqKdPxmxRepository;
            _userManager = userManager;
            _weChatBotService = weChatBotService;
            _stringGenerator = stringGenerator;
            _logger = logger;
        }

        #region 获取开单记录表
        /// <summary>
        /// 获取开单记录表
        /// </summary>
        /// <remarks>
        /// 获取开单记录及其关联的品项明细、健康师业绩、科技部老师业绩信息
        /// 按照开单的完整格式返回数据，不包含汇总信息
        ///
        /// 返回数据结构：
        /// - 主表信息：开单基础信息、业绩信息、支付信息、会员信息等
        /// - 品项明细列表：每个品项包含完整的项目信息（项目次数、是否有效、来源类型等）
        /// - 健康师业绩列表：按品项关联的健康师业绩信息
        /// - 科技部老师业绩列表：按品项关联的科技部老师业绩信息
        /// </remarks>
        /// <param name="id">开单记录主键ID</param>
        /// <returns>开单记录完整信息</returns>
        /// <response code="200">查询成功</response>
        /// <response code="404">开单记录不存在</response>
        /// <response code="500">服务器内部错误</response>
        [HttpGet("{id}")]
        public async Task<dynamic> GetInfo(string id)
        {
            try
            {
                // 1. 查询主表信息
                var entity = await _db.Queryable<LqKdKdjlbEntity>().FirstAsync(p => p.Id == id);
                if (entity == null)
                {
                    throw NCCException.Oh(ErrorCode.COM1005, "开单记录不存在");
                }

                var output = entity.Adapt<LqKdKdjlbInfoOutput>();
                if (output.activityId != null)
                {
                    output.activityName = await _db.Queryable<LqPackageInfoEntity>().Where(x => x.Id == entity.ActivityId).Select(x => x.ActivityName).FirstAsync();
                }
                //获取会员信息
                var MemberInfo = await _db.Queryable<LqKhxxEntity>().FirstAsync(p => p.Id == entity.Kdhy);
                output.kdhyc = MemberInfo.Khmc;
                output.kdhysjh = MemberInfo.Sjh;

                if (!string.IsNullOrEmpty(entity.ActivityId))
                {
                    output.activityName = await _db.Queryable<LqPackageInfoEntity>().Where(x => x.Id == entity.ActivityId).Select(x => x.ActivityName).FirstAsync();
                }
                // 2. 查询品项明细列表
                var lqKdPxmxList = await _db.Queryable<LqKdPxmxEntity>().Where(w => w.Glkdbh == entity.Id).ToListAsync();

                // 3. 查询健康师业绩列表
                var lqKdJksyjList = await _db.Queryable<LqKdJksyjEntity>().Where(w => w.Glkdbh == entity.Id).ToListAsync();

                // 4. 查询科技部老师业绩列表
                var lqKdKjbsyjList = await _db.Queryable<LqKdKjbsyjEntity>().Where(w => w.Glkdbh == entity.Id).ToListAsync();

                // 5. 查询扣款信息列表
                var lqKdDeductList = await _db.Queryable<LqKdDeductinfoEntity>().Where(w => w.BillingId == entity.Id).ToListAsync();

                // 5. 构建品项明细输出，每个品项关联对应的业绩信息
                var pxmxOutputList = new List<LqKdPxmxInfoOutput>();
                foreach (var pxmx in lqKdPxmxList)
                {
                    var pxmxOutput = new LqKdPxmxInfoOutput
                    {
                        id = pxmx.Id,
                        glkdbh = pxmx.Glkdbh,
                        px = pxmx.Px,
                        pxmc = pxmx.Pxmc,
                        pxjg = pxmx.Pxjg,
                        projectNumber = pxmx.ProjectNumber,
                        isEnabled = pxmx.IsEnabled,
                        sourceType = pxmx.SourceType,
                        memberId = pxmx.MemberId,
                        createTime = pxmx.CreateTIme,
                        totalPrice = pxmx.TotalPrice,
                        actualPrice = pxmx.ActualPrice,
                        remark = pxmx.Remark,
                        isEffective = pxmx.IsEffective,
                    };

                    // 关联该品项的健康师业绩
                    var jksyjForPx = lqKdJksyjList.Where(j => j.Kdpxid == pxmx.Id).ToList();
                    pxmxOutput.lqKdJksyjList = jksyjForPx.Adapt<List<LqKdJksyjInfoOutput>>();

                    // 关联该品项的科技部老师业绩
                    var kjbsyjForPx = lqKdKjbsyjList.Where(k => k.Kdpxid == pxmx.Id).ToList();
                    pxmxOutput.lqKdKjbsyjList = kjbsyjForPx.Adapt<List<LqKdKjbsyjInfoOutput>>();


                    pxmxOutputList.Add(pxmxOutput);
                }

                // 6. 设置输出结果
                output.lqKdPxmxList = pxmxOutputList;

                // 7. 设置全局业绩列表（用于兼容性，但主要使用品项关联的业绩）
                output.lqKdJksyjList = lqKdJksyjList.Adapt<List<LqKdJksyjInfoOutput>>();
                output.lqKdKjbsyjList = lqKdKjbsyjList.Adapt<List<LqKdKjbsyjInfoOutput>>();
                // 8. 设置扣款信息列表
                output.lqKdDeductList = lqKdDeductList.Adapt<List<LqKdDeductinfoInfoOutput>>();

                return output;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"获取开单记录失败: {ex.Message}");
                throw NCCException.Oh(ErrorCode.COM1000, "获取开单记录失败");
            }
        }
        #endregion

        #region 获取开单记录表列表

        /// <summary>
        /// 获取开单记录表列表
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("")]
        public async Task<dynamic> GetList([FromQuery] LqKdKdjlbListQueryInput input)
        {
            var sidx = input.sidx == null ? "kdrq" : input.sidx;
            List<string> queryKdrq = input.kdrq != null ? input.kdrq.Split(',').ToObeject<List<string>>() : null;
            DateTime? startKdrq = queryKdrq != null ? Ext.GetDateTime(queryKdrq.First()) : null;
            DateTime? endKdrq = queryKdrq != null ? Ext.GetDateTime(queryKdrq.Last()) : null;
            var data = await _db.Queryable<LqKdKdjlbEntity>()
            .WhereIF(!string.IsNullOrEmpty(input.keyword), p => p.Kdhyc.Contains(input.keyword) || p.Kdhysjh.Contains(input.keyword))
                .WhereIF(!string.IsNullOrEmpty(input.id), p => p.Id.Contains(input.id))
                .WhereIF(!string.IsNullOrEmpty(input.djmd), p => p.Djmd.Equals(input.djmd))
                .WhereIF(!string.IsNullOrEmpty(input.jsj), p => p.Jsj.Equals(input.jsj))
                .WhereIF(queryKdrq != null, p => p.Kdrq >= new DateTime(startKdrq.ToDate().Year, startKdrq.ToDate().Month, startKdrq.ToDate().Day, 0, 0, 0))
                .WhereIF(queryKdrq != null, p => p.Kdrq <= new DateTime(endKdrq.ToDate().Year, endKdrq.ToDate().Month, endKdrq.ToDate().Day, 23, 59, 59))
                .WhereIF(!string.IsNullOrEmpty(input.gjlx), p => p.Gjlx.Equals(input.gjlx))
                .WhereIF(!string.IsNullOrEmpty(input.hgjg), p => p.Hgjg.Equals(input.hgjg))
                .WhereIF(input.zdyj != null, p => p.Zdyj > input.zdyj)
                .WhereIF(input.sfyj != null, p => p.Sfyj > input.sfyj)
                .WhereIF(input.qk != null, p => p.Qk > input.qk)
                .WhereIF(!string.IsNullOrEmpty(input.ckfs), p => p.Ckfs.Equals(input.ckfs))
                .WhereIF(!string.IsNullOrEmpty(input.fkfs), p => p.Fkfs.Equals(input.fkfs))
                .WhereIF(!string.IsNullOrEmpty(input.fkyy), p => p.Fkyy.Equals(input.fkyy))
                .WhereIF(!string.IsNullOrEmpty(input.fkpd), p => p.Fkpd.Contains(input.fkpd))
                .WhereIF(!string.IsNullOrEmpty(input.khly), p => p.Khly.Equals(input.khly))
                .WhereIF(!string.IsNullOrEmpty(input.tjr), p => p.Tjr.Contains(input.tjr))
                .WhereIF(!string.IsNullOrEmpty(input.sfskdd), p => p.Sfskdd.Equals(input.sfskdd))
                .WhereIF(!string.IsNullOrEmpty(input.jj), p => p.Jj.Contains(input.jj))
                .WhereIF(!string.IsNullOrEmpty(input.bz), p => p.Bz.Contains(input.bz))
                .WhereIF(!string.IsNullOrEmpty(input.kdhy), p => p.Kdhy.Equals(input.kdhy))
                .WhereIF(!string.IsNullOrEmpty(input.kdhyc), p => p.Kdhyc.Contains(input.kdhyc))
                .WhereIF(!string.IsNullOrEmpty(input.kdhysjh), p => p.Kdhysjh.Contains(input.kdhysjh))
                .WhereIF(!string.IsNullOrEmpty(input.F_FIleUrl), p => p.F_FIleUrl.Contains(input.F_FIleUrl))
                .WhereIF(!string.IsNullOrEmpty(input.CreateUser), p => p.CreateUser.Equals(input.CreateUser))
                .WhereIF(input.isEffective != 0, p => p.IsEffective == input.isEffective)
                .Select(it => new LqKdKdjlbListOutput
                {
                    id = it.Id,
                    djmd = it.Djmd,
                    jsj = it.Jsj,
                    kdrq = it.Kdrq,
                    gjlx = it.Gjlx,
                    hgjg = it.Hgjg,
                    zdyj = it.Zdyj,
                    sfyj = it.Sfyj,
                    qk = it.Qk,
                    ckfs = it.Ckfs,
                    fkfs = it.Fkfs,
                    fkyy = it.Fkyy,
                    fkpd = it.Fkpd,
                    khly = it.Khly,
                    tjr = it.Tjr,
                    deductAmount = it.DeductAmount,
                    paidDebt = it.PaidDebt,
                    supplementBillingId = it.SupplementBillingId,
                    sfskdd = it.Sfskdd,
                    jj = it.Jj,
                    bz = it.Bz,
                    kdhy = it.Kdhy,
                    kdhyc = SqlFunc.Subqueryable<LqKhxxEntity>().Where(x => x.Id == it.Kdhy).Select(x => x.Khmc),
                    kdhysjh = SqlFunc.Subqueryable<LqKhxxEntity>().Where(x => x.Id == it.Kdhy).Select(x => x.Sjh),
                    isEffective = it.IsEffective,
                    createUser = it.CreateUser,
                    createUserName = SqlFunc.Subqueryable<UserEntity>().Where(x => x.Id == it.CreateUser).Select(x => x.RealName),
                    activityId = it.ActivityId,
                    activityName = SqlFunc.Subqueryable<LqPackageInfoEntity>().Where(x => x.Id == it.ActivityId).Select(x => x.ActivityName),
                })
                .MergeTable()
                .OrderBy(sidx + " " + input.sort)
                .ToPagedListAsync(input.currentPage, input.pageSize);

            // 获取当前页的开单记录ID列表
            var billingIds = data.list.Select(x => x.id).ToList();

            // 批量查询品项明细
            var itemDetails = new List<LqKdPxmxInfoOutput>();
            if (billingIds.Any())
            {
                itemDetails = await _db.Queryable<LqKdPxmxEntity>()
                    .Where(x => billingIds.Contains(x.Glkdbh) && x.IsEffective == StatusEnum.有效.GetHashCode())
                    .Select(x => new LqKdPxmxInfoOutput
                    {
                        id = x.Id,
                        glkdbh = x.Glkdbh,
                        px = x.Px,
                        pxmc = x.Pxmc,
                        pxjg = x.Pxjg,
                        projectNumber = x.ProjectNumber,
                        isEnabled = x.IsEnabled,
                        sourceType = x.SourceType,
                        memberId = x.MemberId,
                        createTime = x.CreateTIme,
                        totalPrice = x.TotalPrice,
                        actualPrice = x.ActualPrice,
                        remark = x.Remark,
                        isEffective = x.IsEffective
                    })
                    .ToListAsync();
            }

            // 按开单ID分组品项明细
            var itemDetailsGrouped = itemDetails.GroupBy(x => x.glkdbh)
                .ToDictionary(g => g.Key, g => g.ToList());

            // 为每个开单记录分配品项明细
            foreach (var item in data.list)
            {
                item.ItemDetails = itemDetailsGrouped.ContainsKey(item.id)
                    ? itemDetailsGrouped[item.id]
                    : new List<LqKdPxmxInfoOutput>();
            }

            return PageResult<LqKdKdjlbListOutput>.SqlSugarPageResult(data);
        }
        #endregion

        #region 新建开单记录表
        /// <summary>
        /// 新建开单记录表
        /// </summary>
        /// <param name="input">参数</param>
        /// <returns></returns>
        [HttpPost("")]
        public async Task Create([FromBody] LqKdKdjlbCrInput input)
        {
            var userInfo = await _userManager.GetUserInfo();
            var entity = input.Adapt<LqKdKdjlbEntity>();
            var HealthInstructorNames = "";
            entity.Id = YitIdHelper.NextId().ToString();
            entity.CreateTime = DateTime.Now;
            entity.UpdateTime = DateTime.Now;
            entity.IsEffective = StatusEnum.有效.GetHashCode();
            try
            {
                //开启事务
                _db.BeginTran();
                //判断是否有作废关联id
                //暂时先不需要
                // if (!string.IsNullOrEmpty(input.cancelRefId))
                // {
                //     //查询作废关联id
                //     var cancelRefEntity = await _db.Queryable<LqKdKdjlbEntity>().FirstAsync(p => p.Id == input.cancelRefId);
                //     if (cancelRefEntity == null || cancelRefEntity.IsEffective == StatusEnum.无效.GetHashCode())
                //     {
                //         throw NCCException.Oh("该开单记录已经作废");
                //     }
                //     // 检查作废关联记录是否可以作废
                //     var (canCancel, errorMessage) = await CheckBillingCanCancelAsync(input.cancelRefId);
                //     if (!canCancel)
                //     {
                //         throw NCCException.Oh(errorMessage);
                //     }

                //     //将作废关联id的IsEffective设置为无效
                //     cancelRefEntity.IsEffective = StatusEnum.无效.GetHashCode();
                //     await _db.Updateable(cancelRefEntity).ExecuteCommandAsync();
                //     //把品项明细表的IsEffective设置为无效
                //     await _db.Updateable<LqKdPxmxEntity>().Where(p => p.Glkdbh == input.cancelRefId).UpdateColumns(p => new LqKdPxmxEntity { IsEffective = StatusEnum.无效.GetHashCode() }).ExecuteCommandAsync();
                //     //把健康师业绩表的IsEffective设置为无效
                //     await _db.Updateable<LqKdJksyjEntity>().Where(p => p.Glkdbh == input.cancelRefId).UpdateColumns(p => new LqKdJksyjEntity { IsEffective = StatusEnum.无效.GetHashCode() }).ExecuteCommandAsync();
                //     //把科技部老师业绩表的IsEffective设置为无效
                //     await _db.Updateable<LqKdKjbsyjEntity>().Where(p => p.Glkdbh == input.cancelRefId).UpdateColumns(p => new LqKdKjbsyjEntity { IsEffective = StatusEnum.无效.GetHashCode() }).ExecuteCommandAsync();
                //     //把扣款信息表的IsEffective设置为无效
                //     await _db.Updateable<LqKdDeductinfoEntity>().Where(p => p.BillingId == input.cancelRefId).UpdateColumns(p => new LqKdDeductinfoEntity { IsEffective = StatusEnum.无效.GetHashCode() }).ExecuteCommandAsync();
                // }

                //判断是否有补缴开单ID
                if (!string.IsNullOrEmpty(input.supplementBillingId))
                {
                    //查询补缴开单ID
                    var supplementBillingEntity = await _db.Queryable<LqKdKdjlbEntity>().FirstAsync(p => p.Id == input.supplementBillingId);
                    if (supplementBillingEntity == null || supplementBillingEntity.IsEffective == StatusEnum.无效.GetHashCode())
                    {
                        throw NCCException.Oh("补缴开单记录不存在或已作废");
                    }
                    //然后对之前的开单表的补缴金额进行累加
                    //需要判断补缴金额是否超过欠款金额，只记录应缴金额
                    var supplementAmount = supplementBillingEntity.Qk - supplementBillingEntity.PaidDebt;
                    supplementBillingEntity.PaidDebt += input.supplementAmount > supplementAmount ? supplementAmount : input.supplementAmount;
                    await _db.Updateable(supplementBillingEntity).ExecuteCommandAsync();
                }

                //新增开单记录表记录
                entity.CreateUser = userInfo.userId;
                entity.DeductAmount = input.lqKdKdjlbDeductList.Sum(x => x.Amount ?? 0);//计算储扣总金额
                var newEntity = await _db.Insertable(entity).IgnoreColumns(ignoreNullColumn: true).ExecuteReturnEntityAsync();
                //循环品相信息
                // 收集所有需要插入的实体，然后批量插入
                var allPxmxEntities = new List<LqKdPxmxEntity>();
                var allJksyjEntities = new List<LqKdJksyjEntity>();
                var allKjbsyjEntities = new List<LqKdKjbsyjEntity>();
                var allDeductEntities = new List<LqKdDeductinfoEntity>();
                // 处理扣款信息列表
                foreach (var item in input.lqKdKdjlbDeductList)
                {
                    var lqKdDeductEntity = new LqKdDeductinfoEntity
                    {
                        Id = YitIdHelper.NextId().ToString(),
                        BillingId = newEntity.Id,
                        DeductId = item.DeductId,
                        DeductType = item.DeductType,
                        Amount = item.Amount,
                        ProjectNumber = item.ProjectNumber,
                        UnitPrice = item.UnitPrice,
                        ItemName = item.ItemName,
                        ItemId = item.ItemId,
                        IsEffective = StatusEnum.有效.GetHashCode(), // 设置为有效
                        CreateTime = DateTime.Now, // 设置创建时间
                    };
                    allDeductEntities.Add(lqKdDeductEntity);
                }
                // 处理品项明细列表
                foreach (var item in input.lqKdPxmxList)
                {
                    // 创建品项明细实体
                    var lqKdPxmxEntity = new LqKdPxmxEntity
                    {
                        Id = YitIdHelper.NextId().ToString(),
                        Glkdbh = newEntity.Id,
                        Yjsj = input.kdrq,
                        CreateTIme = DateTime.Now,
                        MemberId = entity.Kdhy,
                        IsEnabled = StatusEnum.有效.GetHashCode(),
                        ProjectNumber = item.projectNumber,
                        TotalPrice = (decimal)(item.pxjg * item.projectNumber),
                        Px = item.px,
                        Pxmc = item.pxmc,
                        Pxjg = item.pxjg,
                        SourceType = item.sourceType,
                        ActualPrice = item.actualPrice,
                        Remark = item.remark,
                        IsEffective = StatusEnum.有效.GetHashCode(),
                        ActivityId = input.activityId,
                    };
                    allPxmxEntities.Add(lqKdPxmxEntity);

                    // 收集该品项关联的健康师业绩
                    if (item.lqKdJksyjList != null && item.lqKdJksyjList.Any())
                    {
                        //把jksxm保存到HealthInstructorNames
                        foreach (var ijks_tem in item.lqKdJksyjList)
                        {
                            HealthInstructorNames += ijks_tem.jksxm + ",";
                            allJksyjEntities.Add(new LqKdJksyjEntity
                            {
                                Id = YitIdHelper.NextId().ToString(),
                                Glkdbh = newEntity.Id,
                                Jks = ijks_tem.jks,
                                Jksxm = ijks_tem.jksxm,
                                Jkszh = ijks_tem.jkszh,
                                Jksyj = ijks_tem.jksyj,
                                Yjsj = input.kdrq,
                                Jsj_id = ijks_tem.jsj_id,
                                Kdpxid = lqKdPxmxEntity.Id,
                                IsEffective = StatusEnum.有效.GetHashCode(),
                                ActivityId = input.activityId,
                            });
                        }
                    }

                    // 收集该品项关联的科技部老师业绩
                    if (item.lqKdKjbsyjList != null && item.lqKdKjbsyjList.Any())
                    {
                        foreach (var ikjbs_tem in item.lqKdKjbsyjList)
                        {
                            allKjbsyjEntities.Add(new LqKdKjbsyjEntity
                            {
                                Id = YitIdHelper.NextId().ToString(),
                                Glkdbh = newEntity.Id,
                                Kjbls = ikjbs_tem.kjbls,
                                Kjblsxm = ikjbs_tem.kjblsxm,
                                Kjblszh = ikjbs_tem.kjblszh,
                                Kjblsyj = ikjbs_tem.kjblsyj,
                                Yjsj = input.kdrq,
                                Kdpxid = lqKdPxmxEntity.Id,
                                IsEffective = StatusEnum.有效.GetHashCode(),
                                ActivityId = input.activityId,
                            }
                            );
                        }
                    }
                }



                //通过会员id查询会员信息
                var memberInfo = await _db.Queryable<LqKhxxEntity>().Where(u => u.Id == entity.Kdhy).FirstAsync();
                //通过开单记录表查询这个会员开单金额
                var kdAmount = await _db.Queryable<LqKdKdjlbEntity>().Where(u => u.Kdhy == entity.Kdhy).SumAsync(u => u.Sfyj);
                //如果开单金额小于500，为散客，如果大于500，为会员
                if (kdAmount < 500 && kdAmount > 0)
                {
                    memberInfo.Khlx = MemberTypeEnum.散客.GetHashCode().ToString();
                }
                else
                {
                    memberInfo.Khlx = MemberTypeEnum.会员.GetHashCode().ToString();
                }
                await _db.Updateable(memberInfo).ExecuteCommandAsync();
                // 批量插入扣款信息
                if (allDeductEntities.Any())
                {
                    await _db.Insertable(allDeductEntities).ExecuteCommandAsync();
                }
                // 批量插入品项明细
                if (allPxmxEntities.Any())
                {
                    await _db.Insertable(allPxmxEntities).ExecuteCommandAsync();
                }
                // 批量插入健康师业绩
                if (allJksyjEntities.Any())
                {
                    await _db.Insertable(allJksyjEntities).ExecuteCommandAsync();
                }
                // 批量插入科技部老师业绩
                if (allKjbsyjEntities.Any())
                {
                    await _db.Insertable(allKjbsyjEntities).ExecuteCommandAsync();
                }

                //关闭事务
                _db.CommitTran();

                // 生成开单记录字符串并发送到企业微信
                try
                {
                    var entityInfo = await GetInfo(newEntity.Id);
                    if (entityInfo != null)
                    {
                        var orderRecordString = _stringGenerator.GenerateOrderRecordString(entityInfo, HealthInstructorNames);
                        Console.WriteLine("开单记录字符串生成成功:");
                        Console.WriteLine(orderRecordString);

                        // 发送到企业微信群
                        try
                        {
                            var sendResult = await _weChatBotService.SendOrderRecordMessage(orderRecordString);
                            if (sendResult)
                            {
                                Console.WriteLine("开单记录已成功发送到企业微信群");
                            }
                            else
                            {
                                Console.WriteLine("开单记录发送到企业微信群失败");
                            }
                        }
                        catch (Exception wechatEx)
                        {
                            Console.WriteLine($"发送企业微信消息异常: {wechatEx.Message}");
                        }
                    }
                }
                catch (Exception ex)
                {
                    // 字符串生成失败不影响主流程，只记录日志
                    Console.WriteLine($"生成开单记录字符串失败: {ex.Message}");
                }
            }
            catch (Exception ex)
            {
                //回滚事务
                _db.RollbackTran();
                Console.WriteLine($"开单创建失败: {ex.Message}");
                Console.WriteLine($"异常堆栈: {ex.StackTrace}");
                throw NCCException.Oh(ErrorCode.COM1000);
            }
        }
        #endregion

        #region 新建开单记录表_备份
        /// <summary>
        /// 新建开单记录表_备份
        /// </summary>
        /// <param name="input">参数</param>
        /// <returns></returns>
        [HttpPost("Create_Bak")]
        public async Task Create_Bak([FromBody] LqKdKdjlbCrInput input)
        {
            var userInfo = await _userManager.GetUserInfo();
            var entity = input.Adapt<LqKdKdjlbEntity>();
            var HealthInstructorNames = "";
            entity.Id = YitIdHelper.NextId().ToString();
            entity.CreateTime = DateTime.Now;
            entity.UpdateTime = DateTime.Now;
            try
            {
                //开启事务
                _db.BeginTran();
                //新增开单记录表记录
                entity.CreateUser = userInfo.userId;
                entity.DeductAmount = input.lqKdKdjlbDeductList.Sum(x => x.Amount ?? 0);//计算储扣总金额
                var newEntity = await _db.Insertable(entity).IgnoreColumns(ignoreNullColumn: true).ExecuteReturnEntityAsync();
                //循环品相信息
                // 收集所有需要插入的实体，然后批量插入
                var allPxmxEntities = new List<LqKdPxmxEntity>();
                var allJksyjEntities = new List<LqKdJksyjEntity>();
                var allKjbsyjEntities = new List<LqKdKjbsyjEntity>();
                var allDeductEntities = new List<LqKdDeductinfoEntity>();
                // 处理扣款信息列表
                foreach (var item in input.lqKdKdjlbDeductList)
                {
                    var lqKdDeductEntity = new LqKdDeductinfoEntity
                    {
                        Id = YitIdHelper.NextId().ToString(),
                        BillingId = newEntity.Id,
                        DeductId = item.DeductId,
                        DeductType = item.DeductType,
                        Amount = item.Amount,
                    };
                    allDeductEntities.Add(lqKdDeductEntity);
                }
                // 处理品项明细列表
                foreach (var item in input.lqKdPxmxList)
                {
                    // 创建品项明细实体
                    var lqKdPxmxEntity = new LqKdPxmxEntity
                    {
                        Id = YitIdHelper.NextId().ToString(),
                        Glkdbh = newEntity.Id,
                        Yjsj = input.kdrq,
                        CreateTIme = DateTime.Now,
                        MemberId = entity.Kdhy,
                        IsEnabled = 0,
                        ProjectNumber = item.projectNumber,
                        TotalPrice = (decimal)(item.pxjg * item.projectNumber),
                        Px = item.px,
                        Pxmc = item.pxmc,
                        Pxjg = item.pxjg,
                        SourceType = item.sourceType,
                        ActualPrice = item.actualPrice,
                    };
                    allPxmxEntities.Add(lqKdPxmxEntity);

                    // 收集该品项关联的健康师业绩
                    if (item.lqKdJksyjList != null && item.lqKdJksyjList.Any())
                    {
                        //把jksxm保存到HealthInstructorNames
                        foreach (var ijks_tem in item.lqKdJksyjList)
                        {
                            HealthInstructorNames += ijks_tem.jksxm + ",";
                            allJksyjEntities.Add(new LqKdJksyjEntity
                            {
                                Id = YitIdHelper.NextId().ToString(),
                                Glkdbh = newEntity.Id,
                                Jks = ijks_tem.jks,
                                Jksxm = ijks_tem.jksxm,
                                Jkszh = ijks_tem.jkszh,
                                Jksyj = ijks_tem.jksyj,
                                Yjsj = input.kdrq,
                                Jsj_id = ijks_tem.jsj_id,
                                Kdpxid = lqKdPxmxEntity.Id,
                            });
                        }
                    }

                    // 收集该品项关联的科技部老师业绩
                    if (item.lqKdKjbsyjList != null && item.lqKdKjbsyjList.Any())
                    {
                        foreach (var ikjbs_tem in item.lqKdKjbsyjList)
                        {
                            allKjbsyjEntities.Add(
                                new LqKdKjbsyjEntity
                                {
                                    Id = YitIdHelper.NextId().ToString(),
                                    Glkdbh = newEntity.Id,
                                    Kjbls = ikjbs_tem.kjbls,
                                    Kjblsxm = ikjbs_tem.kjblsxm,
                                    Kjblszh = ikjbs_tem.kjblszh,
                                    Kjblsyj = ikjbs_tem.kjblsyj,
                                    Yjsj = input.kdrq,
                                    Kdpxid = lqKdPxmxEntity.Id,
                                }
                            );
                        }
                    }
                }


                //通过会员id查询会员信息
                var memberInfo = await _db.Queryable<LqKhxxEntity>().Where(u => u.Id == entity.Kdhy).FirstAsync();
                //通过开单记录表查询这个会员开单金额
                var kdAmount = await _db.Queryable<LqKdKdjlbEntity>().Where(u => u.Kdhy == entity.Kdhy).SumAsync(u => u.Sfyj);
                //如果开单金额小于500，为散客，如果大于500，为会员
                if (kdAmount < 500)
                {
                    memberInfo.Khlx = MemberTypeEnum.散客.GetHashCode().ToString();
                }
                else
                {
                    memberInfo.Khlx = MemberTypeEnum.会员.GetHashCode().ToString();
                }
                await _db.Updateable(memberInfo).ExecuteCommandAsync();
                // 批量插入扣款信息
                if (allDeductEntities.Any())
                {
                    await _db.Insertable(allDeductEntities).ExecuteCommandAsync();
                }
                // 批量插入品项明细
                if (allPxmxEntities.Any())
                {
                    await _db.Insertable(allPxmxEntities).ExecuteCommandAsync();
                }
                // 批量插入健康师业绩
                if (allJksyjEntities.Any())
                {
                    await _db.Insertable(allJksyjEntities).ExecuteCommandAsync();
                }
                // 批量插入科技部老师业绩
                if (allKjbsyjEntities.Any())
                {
                    await _db.Insertable(allKjbsyjEntities).ExecuteCommandAsync();
                }

                //关闭事务
                _db.CommitTran();

                // 生成开单记录字符串并发送到企业微信
                try
                {
                    var entityInfo = await GetInfo(newEntity.Id);
                    if (entityInfo != null)
                    {
                        var orderRecordString = _stringGenerator.GenerateOrderRecordString(entityInfo, HealthInstructorNames);
                        Console.WriteLine("开单记录字符串生成成功:");
                        Console.WriteLine(orderRecordString);

                        // 发送到企业微信群
                        try
                        {
                            var sendResult = await _weChatBotService.SendOrderRecordMessage(orderRecordString);
                            if (sendResult)
                            {
                                Console.WriteLine("开单记录已成功发送到企业微信群");
                            }
                            else
                            {
                                Console.WriteLine("开单记录发送到企业微信群失败");
                            }
                        }
                        catch (Exception wechatEx)
                        {
                            Console.WriteLine($"发送企业微信消息异常: {wechatEx.Message}");
                        }
                    }
                }
                catch (Exception ex)
                {
                    // 字符串生成失败不影响主流程，只记录日志
                    Console.WriteLine($"生成开单记录字符串失败: {ex.Message}");
                }
            }
            catch (Exception ex)
            {
                //回滚事务
                _db.RollbackTran();
                Console.WriteLine($"开单创建失败: {ex.Message}");
                Console.WriteLine($"异常堆栈: {ex.StackTrace}");
                throw NCCException.Oh(ErrorCode.COM1000);
            }
        }
        #endregion

        #region 获取开单记录表无分页列表
        /// <summary>
        /// 获取开单记录表无分页列表
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [NonAction]
        public async Task<dynamic> GetNoPagingList([FromQuery] LqKdKdjlbListQueryInput input)
        {
            var sidx = input.sidx == null ? "id" : input.sidx;
            List<string> queryKdrq = input.kdrq != null ? input.kdrq.Split(',').ToObeject<List<string>>() : null;
            DateTime? startKdrq = queryKdrq != null ? Ext.GetDateTime(queryKdrq.First()) : null;
            DateTime? endKdrq = queryKdrq != null ? Ext.GetDateTime(queryKdrq.Last()) : null;
            var data = await _db.Queryable<LqKdKdjlbEntity>()
                .WhereIF(!string.IsNullOrEmpty(input.id), p => p.Id.Contains(input.id))
                .WhereIF(!string.IsNullOrEmpty(input.djmd), p => p.Djmd.Equals(input.djmd))
                .WhereIF(!string.IsNullOrEmpty(input.jsj), p => p.Jsj.Equals(input.jsj))
                .WhereIF(queryKdrq != null, p => p.Kdrq >= new DateTime(startKdrq.ToDate().Year, startKdrq.ToDate().Month, startKdrq.ToDate().Day, 0, 0, 0))
                .WhereIF(queryKdrq != null, p => p.Kdrq <= new DateTime(endKdrq.ToDate().Year, endKdrq.ToDate().Month, endKdrq.ToDate().Day, 23, 59, 59))
                .WhereIF(!string.IsNullOrEmpty(input.gjlx), p => p.Gjlx.Equals(input.gjlx))
                .WhereIF(!string.IsNullOrEmpty(input.hgjg), p => p.Hgjg.Equals(input.hgjg))
                .WhereIF(input.zdyj != null, p => p.Zdyj > input.zdyj)
                .WhereIF(input.sfyj != null, p => p.Sfyj > input.sfyj)
                .WhereIF(input.qk != null, p => p.Qk > input.qk)
                .WhereIF(!string.IsNullOrEmpty(input.ckfs), p => p.Ckfs.Equals(input.ckfs))
                .WhereIF(!string.IsNullOrEmpty(input.fkfs), p => p.Fkfs.Equals(input.fkfs))
                .WhereIF(!string.IsNullOrEmpty(input.fkyy), p => p.Fkyy.Equals(input.fkyy))
                .WhereIF(!string.IsNullOrEmpty(input.fkpd), p => p.Fkpd.Contains(input.fkpd))
                .WhereIF(!string.IsNullOrEmpty(input.khly), p => p.Khly.Equals(input.khly))
                .WhereIF(!string.IsNullOrEmpty(input.tjr), p => p.Tjr.Contains(input.tjr))
                .WhereIF(!string.IsNullOrEmpty(input.sfskdd), p => p.Sfskdd.Equals(input.sfskdd))
                .WhereIF(!string.IsNullOrEmpty(input.jj), p => p.Jj.Contains(input.jj))
                .WhereIF(!string.IsNullOrEmpty(input.bz), p => p.Bz.Contains(input.bz))
                .WhereIF(!string.IsNullOrEmpty(input.kdhy), p => p.Kdhy.Equals(input.kdhy))
                .WhereIF(!string.IsNullOrEmpty(input.kdhyc), p => p.Kdhyc.Contains(input.kdhyc))
                .WhereIF(!string.IsNullOrEmpty(input.kdhysjh), p => p.Kdhysjh.Contains(input.kdhysjh))
                .WhereIF(!string.IsNullOrEmpty(input.F_FIleUrl), p => p.F_FIleUrl.Contains(input.F_FIleUrl))
                .WhereIF(input.isEffective != 0, p => p.IsEffective == input.isEffective)
                .Select(it => new LqKdKdjlbListOutput
                {
                    id = it.Id,
                    djmd = it.Djmd,
                    jsj = it.Jsj,
                    kdrq = it.Kdrq,
                    gjlx = it.Gjlx,
                    hgjg = it.Hgjg,
                    zdyj = it.Zdyj,
                    sfyj = it.Sfyj,
                    qk = it.Qk,
                    ckfs = it.Ckfs,
                    deductAmount = it.DeductAmount,
                    fkfs = it.Fkfs,
                    fkyy = it.Fkyy,
                    fkpd = it.Fkpd,
                    khly = it.Khly,
                    tjr = it.Tjr,
                    sfskdd = it.Sfskdd,
                    jj = it.Jj,
                    bz = it.Bz,
                    kdhy = it.Kdhy,
                    kdhyc = it.Kdhyc,
                    kdhysjh = it.Kdhysjh,
                    fileUrl = it.F_FIleUrl,
                })
                .MergeTable()
                .OrderBy(sidx + " " + input.sort)
                .ToListAsync();
            return data;
        }
        #endregion

        #region 导出开单记录表
        /// <summary>
        /// 导出开单记录表
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("Actions/Export")]
        public async Task<dynamic> Export([FromQuery] LqKdKdjlbListQueryInput input)
        {
            var userInfo = await _userManager.GetUserInfo();
            var exportData = new List<LqKdKdjlbListOutput>();
            if (input.dataType == 0)
            {
                var data = Clay.Object(await this.GetList(input));
                exportData = data.Solidify<PageResult<LqKdKdjlbListOutput>>().list;
            }
            else
            {
                exportData = await this.GetNoPagingList(input);
            }
            List<ParamsModel> paramList =
                "[{\"value\":\"开单编号\",\"field\":\"id\"},{\"value\":\"开单会员\",\"field\":\"kdhy\"},{\"value\":\"开单会员名称\",\"field\":\"kdhyc\"},{\"value\":\"会员手机号\",\"field\":\"kdhysjh\"},{\"value\":\"单据门店\",\"field\":\"djmd\"},{\"value\":\"金三角\",\"field\":\"jsj\"},{\"value\":\"开单日期\",\"field\":\"kdrq\"},{\"value\":\"顾客类型\",\"field\":\"gjlx\"},{\"value\":\"合作机构\",\"field\":\"hgjg\"},{\"value\":\"整单业绩\",\"field\":\"zdyj\"},{\"value\":\"实付业绩\",\"field\":\"sfyj\"},{\"value\":\"欠款\",\"field\":\"qk\"},{\"value\":\"储扣方式\",\"field\":\"ckfs\"},{\"value\":\"储扣明细\",\"field\":\"ckmx\"},{\"value\":\"付款方式\",\"field\":\"fkfs\"},{\"value\":\"付款医院\",\"field\":\"fkyy\"},{\"value\":\"付款判断\",\"field\":\"fkpd\"},{\"value\":\"客户来源\",\"field\":\"khly\"},{\"value\":\"推荐人\",\"field\":\"tjr\"},{\"value\":\"是否首开订单\",\"field\":\"sfskdd\"},{\"value\":\"简介\",\"field\":\"jj\"},{\"value\":\"备注\",\"field\":\"bz\"},{\"value\":\"健康师业绩\",\"field\":\"jksyj\"},{\"value\":\"科技部老师业绩\",\"field\":\"kjblsyj\"},{\"value\":\"品项信息\",\"field\":\"pxxx\"},{\"value\":\"方案其他\",\"field\":\"F_FIleUrl\"},]".ToList<ParamsModel>();
            ExcelConfig excelconfig = new ExcelConfig();
            excelconfig.FileName = "开单记录表.xls";
            excelconfig.HeadFont = "微软雅黑";
            excelconfig.HeadPoint = 10;
            excelconfig.IsAllSizeColumn = true;
            excelconfig.ColumnModel = new List<ExcelColumnModel>();
            List<string> selectKeyList = input.selectKey.Split(',').ToList();
            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<LqKdKdjlbListOutput>.Export(exportData, excelconfig, addPath);
            var fileName = _userManager.UserId + "|" + addPath + "|xls";
            var output = new { name = excelconfig.FileName, url = "/api/File/Download?encryption=" + DESCEncryption.Encrypt(fileName, "NCC") };
            return output;
        }
        #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<LqKdKdjlbEntity>().In(it => it.Id, ids).ToListAsync();
            if (entitys.Count > 0)
            {
                try
                {
                    //开启事务
                    _db.BeginTran();
                    // 检查是否有消耗记录
                    var billingItemIds = await _db.Queryable<LqKdPxmxEntity>().Where(p => ids.Contains(p.Glkdbh)).Select(p => p.Id).ToListAsync();
                    if (billingItemIds.Any())
                    {
                        var consumeRecords = await _db.Queryable<LqXhPxmxEntity>().Where(x => x.IsEffective == StatusEnum.有效.GetHashCode()).Where(x => billingItemIds.Contains(x.BillingItemId)).AnyAsync();
                        if (consumeRecords)
                        {
                            throw NCCException.Oh("选中的开单记录中有已消耗的记录，不能删除");
                        }
                    }
                    // 检查是否有储扣记录
                    var deductRecords = await _db.Queryable<LqKdDeductinfoEntity>().Where(x => ids.Contains(x.BillingId) && x.IsEffective == StatusEnum.有效.GetHashCode()).AnyAsync();
                    if (deductRecords)
                    {
                        throw NCCException.Oh("选中的开单记录中有储扣记录，不能删除");
                    }
                    //批量删除开单记录表
                    await _db.Deleteable<LqKdKdjlbEntity>().In(d => d.Id, ids).ExecuteCommandAsync();

                    //清空健康师业绩
                    await _db.Deleteable<LqKdJksyjEntity>().In(u => u.Glkdbh, ids).ExecuteCommandAsync();

                    //清空科技部老师业绩
                    await _db.Deleteable<LqKdKjbsyjEntity>().In(u => u.Glkdbh, ids).ExecuteCommandAsync();

                    //清空品项明细
                    await _db.Deleteable<LqKdPxmxEntity>().In(u => u.Glkdbh, ids).ExecuteCommandAsync();

                    //清空扣款信息
                    await _db.Deleteable<LqKdDeductinfoEntity>().In(u => u.BillingId, ids).ExecuteCommandAsync();

                    //关闭事务
                    _db.CommitTran();
                }
                catch (Exception ex)
                {
                    //回滚事务
                    _db.RollbackTran();
                    throw NCCException.Oh($"批量删除开单记录表失败: {ex.Message}");
                }
            }
        }
        #endregion

        #region 更新开单记录表
        /// <summary>
        /// 更新开单记录表
        /// </summary>
        /// <remarks>
        /// 更新开单记录及其关联的品项明细、健康师业绩、科技部老师业绩信息
        ///
        /// 示例请求：
        /// ```json
        /// {
        ///   "id": "开单编号",
        ///   "djmd": "单据门店",
        ///   "jsj": "金三角",
        ///   "kdrq": "2025-01-11",
        ///   "lqKdPxmxList": [
        ///     {
        ///       "px": "品项编号",
        ///       "pxmc": "品项名称",
        ///       "pxjg": 100.00,
        ///       "projectNumber": 1,
        ///       "sourceType": "购买",
        ///       "lqKdJksyjList": [
        ///         {
        ///           "jks": "健康师",
        ///           "jksxm": "健康师姓名",
        ///           "jksyj": "100"
        ///         }
        ///       ]
        ///     }
        ///   ]
        /// }
        /// ```
        ///
        /// 参数说明：
        /// - id: 开单记录主键ID
        /// - input: 开单记录更新参数，包含品项明细和业绩信息
        /// </remarks>
        /// <param name="id">开单记录主键ID</param>
        /// <param name="input">开单记录更新参数</param>
        /// <returns>无返回值</returns>
        /// <response code="200">更新成功</response>
        /// <response code="400">参数错误或数据验证失败</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPut("{id}")]
        public async Task Update(string id, [FromBody] LqKdKdjlbUpInput input)
        {
            var entity = input.Adapt<LqKdKdjlbEntity>();
            try
            {
                //开启事务
                _db.BeginTran();

                //更新开单记录表记录
                await _db.Updateable(entity).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommandAsync();

                //清空原有数据
                await _db.Deleteable<LqKdJksyjEntity>().Where(u => u.Glkdbh == id).ExecuteCommandAsync();
                await _db.Deleteable<LqKdKjbsyjEntity>().Where(u => u.Glkdbh == id).ExecuteCommandAsync();
                await _db.Deleteable<LqKdPxmxEntity>().Where(u => u.Glkdbh == id).ExecuteCommandAsync();

                // 收集所有需要插入的实体，然后批量插入
                var allPxmxEntities = new List<LqKdPxmxEntity>();
                var allJksyjEntities = new List<LqKdJksyjEntity>();
                var allKjbsyjEntities = new List<LqKdKjbsyjEntity>();

                // 处理品项明细列表
                if (input.lqKdPxmxList != null && input.lqKdPxmxList.Any())
                {
                    foreach (var item in input.lqKdPxmxList)
                    {
                        // 创建品项明细实体
                        var lqKdPxmxEntity = new LqKdPxmxEntity
                        {
                            Id = YitIdHelper.NextId().ToString(),
                            Glkdbh = entity.Id,
                            CreateTIme = DateTime.Now,
                            MemberId = entity.Kdhy,
                            IsEnabled = 0,
                            ProjectNumber = item.projectNumber == 0 ? 1 : item.projectNumber,
                            TotalPrice = (decimal)(item.pxjg * (item.projectNumber == 0 ? 1 : item.projectNumber)),
                            Px = item.px,
                            Pxmc = item.pxmc,
                            Pxjg = item.pxjg,
                            SourceType = item.sourceType,
                        };
                        allPxmxEntities.Add(lqKdPxmxEntity);

                        // 收集该品项关联的健康师业绩
                        if (item.lqKdJksyjList != null && item.lqKdJksyjList.Any())
                        {
                            foreach (var ijks_tem in item.lqKdJksyjList)
                            {
                                allJksyjEntities.Add(
                                    new LqKdJksyjEntity
                                    {
                                        Id = YitIdHelper.NextId().ToString(),
                                        Glkdbh = entity.Id,
                                        Jks = ijks_tem.jks,
                                        Jksxm = ijks_tem.jksxm,
                                        Jkszh = ijks_tem.jkszh,
                                        Jksyj = ijks_tem.jksyj,
                                        Yjsj = DateTime.Now,
                                        Jsj_id = ijks_tem.jsj_id,
                                        Kdpxid = lqKdPxmxEntity.Id,
                                    }
                                );
                            }
                        }

                        // 收集该品项关联的科技部老师业绩
                        if (item.lqKdKjbsyjList != null && item.lqKdKjbsyjList.Any())
                        {
                            foreach (var ikjbs_tem in item.lqKdKjbsyjList)
                            {
                                allKjbsyjEntities.Add(
                                    new LqKdKjbsyjEntity
                                    {
                                        Id = YitIdHelper.NextId().ToString(),
                                        Glkdbh = entity.Id,
                                        Kjbls = ikjbs_tem.kjbls,
                                        Kjblsxm = ikjbs_tem.kjblsxm,
                                        Kjblszh = ikjbs_tem.kjblszh,
                                        Kjblsyj = ikjbs_tem.kjblsyj,
                                        Yjsj = DateTime.Now,
                                        Kdpxid = lqKdPxmxEntity.Id,
                                    }
                                );
                            }
                        }
                    }
                }

                // 批量插入品项明细
                if (allPxmxEntities.Any())
                {
                    await _db.Insertable(allPxmxEntities).ExecuteCommandAsync();
                }
                // 批量插入健康师业绩
                if (allJksyjEntities.Any())
                {
                    await _db.Insertable(allJksyjEntities).ExecuteCommandAsync();
                }
                // 批量插入科技部老师业绩
                if (allKjbsyjEntities.Any())
                {
                    await _db.Insertable(allKjbsyjEntities).ExecuteCommandAsync();
                }
                //关闭事务
                _db.CommitTran();
            }
            catch (Exception)
            {
                //回滚事务
                _db.RollbackTran();
                throw NCCException.Oh(ErrorCode.COM1001);
            }
        }
        #endregion

        #region 删除开单记录表
        /// <summary>
        /// 删除开单记录表
        /// </summary>
        /// <returns></returns>
        [HttpDelete("{id}")]
        public async Task Delete(string id)
        {
            var entity = await _db.Queryable<LqKdKdjlbEntity>().FirstAsync(p => p.Id == id);
            _ = entity ?? throw NCCException.Oh(ErrorCode.COM1005);
            try
            {
                //开启事务
                _db.BeginTran();
                //先查询开单品项明细
                var lqPxmxList = _db.Queryable<LqKdPxmxEntity>().Where(x => x.Glkdbh == id).ToList();
                //判断是否有对应的消耗记录
                var consumeRecords = await _db.Queryable<LqXhPxmxEntity>().Where(x => lqPxmxList.Select(y => y.Id).Contains(x.BillingItemId)).AnyAsync();
                if (consumeRecords)
                {
                    throw NCCException.Oh("选中的开单记录中有已消耗的记录，不能删除");
                }
                //判断是否有对应的退卡记录
                var refundRecords = await _db.Queryable<LqHytkMxEntity>().Where(x => lqPxmxList.Select(y => y.Id).Contains(x.BillingItemId)).AnyAsync();
                if (refundRecords)
                {
                    throw NCCException.Oh("选中的开单记录中有退卡记录，不能删除");
                }
                //判断是否有对应的储扣记录
                var deductRecords = await _db.Queryable<LqKdDeductinfoEntity>().Where(x => lqPxmxList.Select(y => y.Id).Contains(x.DeductId)).AnyAsync();
                if (deductRecords)
                {
                    throw NCCException.Oh("选中的开单记录中有储扣记录，不能删除");
                }

                //删除开单记录表记录
                await _db.Deleteable<LqKdKdjlbEntity>().Where(d => d.Id == id).ExecuteCommandAsync();

                //清空健康师业绩
                await _db.Deleteable<LqKdJksyjEntity>().Where(u => u.Glkdbh == id).ExecuteCommandAsync();

                //清空科技部老师业绩
                await _db.Deleteable<LqKdKjbsyjEntity>().Where(u => u.Glkdbh == id).ExecuteCommandAsync();

                //清空品项明细
                await _db.Deleteable<LqKdPxmxEntity>().Where(u => u.Glkdbh == id).ExecuteCommandAsync();

                //清空扣款信息
                await _db.Deleteable<LqKdDeductinfoEntity>().Where(u => u.BillingId == id).ExecuteCommandAsync();

                //关闭事务
                _db.CommitTran();
            }
            catch (Exception ex)
            {
                //回滚事务
                _db.RollbackTran();
                throw NCCException.Oh($"删除开单记录表失败: {ex.Message}");
            }
        }
        #endregion

        #region 获取扣款类型枚举内容
        /// <summary>
        /// 获取扣款类型枚举内容
        /// </summary>
        /// <returns>扣款类型枚举列表</returns>
        [HttpGet("deduct-types")]
        public List<EnumOutput> GetDeductTypes()
        {
            return Enum.GetValues<DeductTypeEnum>()
                .Select(e => new EnumOutput
                {
                    Value = (int)e,
                    Name = e.ToString(),
                    Description = e.GetDescription(),
                })
                .ToList();
        }
        #endregion

        #region 获取状态枚举内容（所有的状态通用）
        /// <summary>
        /// 获取状态枚举内容
        /// </summary>
        /// <returns>状态枚举列表</returns>
        [HttpGet("status-enum")]
        public List<EnumOutput> GetStatusEnum()
        {
            return Enum.GetValues<StatusEnum>()
                .Select(e => new EnumOutput
                {
                    Value = (int)e,
                    Name = e.ToString(),
                    Description = e.GetDescription(),
                })
                .ToList();
        }
        #endregion

        #region 修改开单记录
        /// <summary>
        /// 修改开单记录
        /// </summary>
        /// <remarks>
        /// 更新开单记录及其关联的品项明细、健康师业绩、科技部老师业绩信息
        ///
        /// 示例请求：
        /// ```json
        /// {
        ///   "id": "开单编号",
        ///   "djmd": "单据门店",
        ///   "jsj": "金三角",
        ///   "kdrq": "2025-01-11",
        ///   "lqKdPxmxList": [
        ///     {
        ///       "px": "品项编号",
        ///       "pxmc": "品项名称",
        ///       "pxjg": 100.00,
        ///       "projectNumber": 1,
        ///       "sourceType": "购买",
        ///       "lqKdJksyjList": [
        ///         {
        ///           "jks": "健康师",
        ///           "jksxm": "健康师姓名",
        ///           "jksyj": "100"
        ///         }
        ///       ]
        ///     }
        ///   ]
        /// }
        /// ```
        ///
        /// 参数说明：
        /// - id: 开单记录主键ID
        /// - input: 开单记录更新参数，包含品项明细和业绩信息
        /// </remarks>
        /// <param name="id">开单记录主键ID</param>
        /// <param name="input">开单记录更新参数</param>
        /// <returns>无返回值</returns>
        /// <response code="200">更新成功</response>
        /// <response code="400">参数错误或数据验证失败</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPut("UpdateForNoDelete/{id}")]
        public async Task UpdateForNoDelete(string id, [FromBody] LqKdKdjlbUpInput input)
        {
            var entity = input.Adapt<LqKdKdjlbEntity>();
            entity.Id = id; // 确保ID正确设置
            try
            {
                //检查开单记录是否可以操作
                var (canCancel, errorMessage) = await CheckBillingCanCancelAsync(id);
                if (!canCancel)
                {
                    throw NCCException.Oh(errorMessage);
                }
                //开启事务
                _db.BeginTran();
                //查询是否有对应的补缴开单ID
                if (!string.IsNullOrEmpty(entity.SupplementBillingId))
                {
                    //查询补缴开单ID
                    var supplementBillingEntity = await _db.Queryable<LqKdKdjlbEntity>().FirstAsync(p => p.Id == entity.SupplementBillingId);//900,900
                    if (supplementBillingEntity == null || supplementBillingEntity.IsEffective == StatusEnum.无效.GetHashCode())
                    {
                        throw NCCException.Oh("补缴开单记录不存在或已作废");
                    }
                    //查询当前开单已经补缴金额
                    var OldSupplementAmount = await _db.Queryable<LqKdKdjlbEntity>().Where(p => p.Id == id).SumAsync(p => p.SupplementAmount);//900,0
                    supplementBillingEntity.PaidDebt = supplementBillingEntity.PaidDebt - OldSupplementAmount + input.supplementAmount;
                    await _db.Updateable(supplementBillingEntity).ExecuteCommandAsync();
                }
                // 更新开单记录主表
                await _db.Updateable(entity).IgnoreColumns(ignoreAllNullColumns: true).IgnoreColumns(x => x.CreateTime).ExecuteCommandAsync();

                //清空原有品项明细
                await _db.Deleteable<LqKdPxmxEntity>().Where(x => x.Glkdbh == id).ExecuteCommandAsync();
                //清空原有健康师业绩
                await _db.Deleteable<LqKdJksyjEntity>().Where(x => x.Glkdbh == id).ExecuteCommandAsync();
                //清空原有科技部老师业绩
                await _db.Deleteable<LqKdKjbsyjEntity>().Where(x => x.Glkdbh == id).ExecuteCommandAsync();
                //清空原有扣款信息
                await _db.Deleteable<LqKdDeductinfoEntity>().Where(x => x.BillingId == id).ExecuteCommandAsync();
                //循环品相信息
                // 收集所有需要插入的实体，然后批量插入
                var allPxmxEntities = new List<LqKdPxmxEntity>();
                var allJksyjEntities = new List<LqKdJksyjEntity>();
                var allKjbsyjEntities = new List<LqKdKjbsyjEntity>();
                var allDeductEntities = new List<LqKdDeductinfoEntity>();
                // 处理扣款信息列表
                foreach (var item in input.lqKdKdjlbDeductList)
                {
                    var lqKdDeductEntity = new LqKdDeductinfoEntity
                    {
                        Id = YitIdHelper.NextId().ToString(),
                        BillingId = id,
                        DeductId = item.DeductId,
                        DeductType = item.DeductType,
                        Amount = item.Amount,
                        ProjectNumber = item.ProjectNumber,
                        UnitPrice = item.UnitPrice,
                        ItemName = item.ItemName,
                        ItemId = item.ItemId,
                        IsEffective = StatusEnum.有效.GetHashCode(), // 设置为有效
                        CreateTime = DateTime.Now, // 设置创建时间
                    };
                    allDeductEntities.Add(lqKdDeductEntity);
                }
                // 处理品项明细列表
                foreach (var item in input.lqKdPxmxList)
                {
                    // 创建品项明细实体
                    var lqKdPxmxEntity = new LqKdPxmxEntity
                    {
                        Id = YitIdHelper.NextId().ToString(),
                        Glkdbh = id,
                        Yjsj = input.kdrq,
                        CreateTIme = DateTime.Now,
                        MemberId = entity.Kdhy,
                        IsEnabled = StatusEnum.有效.GetHashCode(),
                        ProjectNumber = item.projectNumber,
                        TotalPrice = (decimal)(item.pxjg * item.projectNumber),
                        Px = item.px,
                        Pxmc = item.pxmc,
                        Pxjg = item.pxjg,
                        SourceType = item.sourceType,
                        ActualPrice = item.actualPrice,
                        Remark = item.remark,
                        IsEffective = StatusEnum.有效.GetHashCode(),
                        ActivityId = input.activityId,
                    };
                    allPxmxEntities.Add(lqKdPxmxEntity);

                    // 收集该品项关联的健康师业绩
                    if (item.lqKdJksyjList != null && item.lqKdJksyjList.Any())
                    {
                        //把jksxm保存到HealthInstructorNames
                        foreach (var ijks_tem in item.lqKdJksyjList)
                        {
                            allJksyjEntities.Add(new LqKdJksyjEntity
                            {
                                Id = YitIdHelper.NextId().ToString(),
                                Glkdbh = id,
                                Jks = ijks_tem.jks,
                                Jksxm = ijks_tem.jksxm,
                                Jkszh = ijks_tem.jkszh,
                                Jksyj = ijks_tem.jksyj,
                                Yjsj = input.kdrq,
                                Jsj_id = ijks_tem.jsj_id,
                                Kdpxid = lqKdPxmxEntity.Id,
                                IsEffective = StatusEnum.有效.GetHashCode(),
                                ActivityId = input.activityId,
                            });
                        }
                    }

                    // 收集该品项关联的科技部老师业绩
                    if (item.lqKdKjbsyjList != null && item.lqKdKjbsyjList.Any())
                    {
                        foreach (var ikjbs_tem in item.lqKdKjbsyjList)
                        {
                            allKjbsyjEntities.Add(new LqKdKjbsyjEntity
                            {
                                Id = YitIdHelper.NextId().ToString(),
                                Glkdbh = id,
                                Kjbls = ikjbs_tem.kjbls,
                                Kjblsxm = ikjbs_tem.kjblsxm,
                                Kjblszh = ikjbs_tem.kjblszh,
                                Kjblsyj = ikjbs_tem.kjblsyj,
                                Yjsj = input.kdrq,
                                Kdpxid = lqKdPxmxEntity.Id,
                                IsEffective = StatusEnum.有效.GetHashCode(),
                                ActivityId = input.activityId,
                            }
                            );
                        }
                    }
                }



                //通过会员id查询会员信息
                var memberInfo = await _db.Queryable<LqKhxxEntity>().Where(u => u.Id == entity.Kdhy).FirstAsync();
                //通过开单记录表查询这个会员开单金额
                var kdAmount = await _db.Queryable<LqKdKdjlbEntity>().Where(u => u.Kdhy == entity.Kdhy).SumAsync(u => u.Sfyj);
                //如果开单金额小于500，为散客，如果大于500，为会员
                if (kdAmount < 500)
                {
                    memberInfo.Khlx = MemberTypeEnum.散客.GetHashCode().ToString();
                }
                else
                {
                    memberInfo.Khlx = MemberTypeEnum.会员.GetHashCode().ToString();
                }
                await _db.Updateable(memberInfo).ExecuteCommandAsync();
                // 批量插入扣款信息
                if (allDeductEntities.Any())
                {
                    await _db.Insertable(allDeductEntities).ExecuteCommandAsync();
                }
                // 批量插入品项明细
                if (allPxmxEntities.Any())
                {
                    await _db.Insertable(allPxmxEntities).ExecuteCommandAsync();
                }
                // 批量插入健康师业绩
                if (allJksyjEntities.Any())
                {
                    await _db.Insertable(allJksyjEntities).ExecuteCommandAsync();
                }
                // 批量插入科技部老师业绩
                if (allKjbsyjEntities.Any())
                {
                    await _db.Insertable(allKjbsyjEntities).ExecuteCommandAsync();
                }

                //关闭事务
                _db.CommitTran();
            }
            catch (Exception ex)
            {
                //回滚事务
                _db.RollbackTran();
                throw NCCException.Oh($"创建开单记录失败: {ex.Message}");
            }
        }
        #endregion

        #region 作废开单记录
        /// <summary>
        /// 作废开单记录
        /// </summary>
        /// <param name="input">作废开单记录输入</param>
        /// <returns>无返回值</returns>
        /// <remarks>
        /// 作废指定的开单记录，包括相关的品项明细、业绩记录等
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "id": "123456789",
        ///   "remarks": "客户要求作废此订单"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - id: 开单记录主键ID（必填）
        /// - remarks: 作废备注说明
        /// </remarks>
        /// <response code="200">作废成功</response>
        /// <response code="400">参数错误，开单记录ID不能为空</response>
        /// <response code="404">开单记录不存在</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPut("Cancel")]
        public async Task Cancel(CancelBillingInput input)
        {
            if (string.IsNullOrEmpty(input.Id))
            {
                throw NCCException.Oh("开单记录ID不能为空");
            }
            try
            {
                //开启事务
                _db.BeginTran();
                // 检查开单记录是否可以作废
                var (canCancel, errorMessage) = await CheckBillingCanCancelAsync(input.Id);
                if (!canCancel)
                {
                    throw NCCException.Oh(errorMessage);
                }

                // 查询开单记录
                var entity = await _db.Queryable<LqKdKdjlbEntity>().FirstAsync(p => p.Id == input.Id);
                if (entity == null)
                {
                    throw NCCException.Oh("开单记录不存在");
                }

                // 标记开单记录为无效，并添加作废备注
                entity.IsEffective = StatusEnum.无效.GetHashCode();
                entity.CancelRefRemarks = input.Remarks;
                entity.UpdateTime = DateTime.Now;
                await _db.Updateable(entity).ExecuteCommandAsync();

                // 标记对应开单明细表为无效
                await _db.Updateable<LqKdPxmxEntity>().SetColumns(it => new LqKdPxmxEntity { IsEffective = StatusEnum.无效.GetHashCode() }).Where(it => it.Glkdbh == input.Id).ExecuteCommandAsync();

                // 标记健康师业绩为无效
                await _db.Updateable<LqKdJksyjEntity>().SetColumns(it => new LqKdJksyjEntity { IsEffective = StatusEnum.无效.GetHashCode() }).Where(it => it.Glkdbh == input.Id).ExecuteCommandAsync();

                // 标记科技部老师业绩为无效
                await _db.Updateable<LqKdKjbsyjEntity>().SetColumns(it => new LqKdKjbsyjEntity { IsEffective = StatusEnum.无效.GetHashCode() }).Where(it => it.Glkdbh == input.Id).ExecuteCommandAsync();

                // 标记开单_储扣详细表为无效
                await _db.Updateable<LqKdDeductinfoEntity>().SetColumns(it => new LqKdDeductinfoEntity { IsEffective = StatusEnum.无效.GetHashCode() }).Where(it => it.BillingId == input.Id).ExecuteCommandAsync();

                //如果存在补缴开单ID，则将补缴开单ID的IsEffective设置为无效
                if (!string.IsNullOrEmpty(entity.SupplementBillingId))
                {
                    var supplementBillingEntity = await _db.Queryable<LqKdKdjlbEntity>().FirstAsync(p => p.Id == entity.SupplementBillingId);
                    if (supplementBillingEntity != null && supplementBillingEntity.IsEffective == StatusEnum.有效.GetHashCode())
                    {
                        supplementBillingEntity.PaidDebt = supplementBillingEntity.PaidDebt - entity.SupplementAmount;
                        await _db.Updateable(supplementBillingEntity).ExecuteCommandAsync();
                    }
                }
                //关闭事务
                _db.CommitTran();
            }
            catch (Exception ex)
            {
                //回滚事务
                _db.RollbackTran();
                throw NCCException.Oh($"作废开单记录失败: {ex.Message}");
            }
        }
        #endregion

        #region 历史会员权益数据导入
        /// <summary>
        /// 导入历史会员权益数据到开单记录表和开单品项明细表
        /// </summary>
        /// <remarks>
        /// 将历史会员权益表的数据导入到开单记录表和开单品项明细表中
        /// 
        /// 导入规则：
        /// 1. 逐条处理历史会员权益数据，每条记录生成一个开单记录和一个品项明细
        /// 2. 开单时间和创建时间统一设置为 2025-06-01 00:00:00
        /// 3. 使用YitIdHelper生成所有ID
        /// 4. 直接使用F_StoreId作为门店ID
        /// 
        /// 字段映射：
        /// - F_MemberId/会员编号 -> kdhy (开单会员，优先使用会员ID)
        /// - 会员名称 -> kdhyc (开单会员名称)
        /// - 会员电话 -> kdhysjh (开单会员手机号)
        /// - F_StoreId -> djmd (单据门店ID)
        /// - 整单业绩 -> zdyj (整单业绩)
        /// - 实付业绩 -> sfyj (实付业绩)
        /// - 欠款 -> qk (欠款)
        /// - 来源 -> khly (客户来源)
        /// - 品项名称 -> pxmc (品项名称)
        /// - 品项价格 -> pxjg (品项价格)
        /// - 品项次数 -> F_ProjectNumber (项目次数)
        /// - F_MemberId/会员编号 -> F_MemberId (品项明细会员ID，优先使用会员ID)
        /// </remarks>
        /// <returns>导入结果统计</returns>
        /// <response code="200">导入成功</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("import-history-member-rights")]
        public async Task<dynamic> ImportHistoryMemberRights()
        {
            try
            {
                _db.BeginTran();

                // 设置固定的时间
                var fixedDateTime = new DateTime(2025, 6, 1, 0, 0, 0);

                // 1. 查询历史会员权益数据
                var historyData = await _db.Ado.SqlQueryAsync<dynamic>(@"
                    SELECT 
                        会员编号,
                        会员名称,
                        会员电话,
                        开单门店,
                        开单时间,
                        整单业绩,
                        实付业绩,
                        欠款,
                        来源,
                        品项名称,
                        品项价格,
                        品项次数,
                        F_StoreId,
                        F_ProjectId,
                        F_MemberId
                    FROM `历史会员权益`");

                int successCount = 0;
                int errorCount = 0;
                var errorMessages = new List<string>();

                // 2. 批量处理数据，每批1000条
                const int batchSize = 1000;
                var totalBatches = (int)Math.Ceiling((double)historyData.Count / batchSize);

                for (int batchIndex = 0; batchIndex < totalBatches; batchIndex++)
                {
                    var batchData = historyData.Skip(batchIndex * batchSize).Take(batchSize).ToList();

                    var kdjlbEntities = new List<LqKdKdjlbEntity>();
                    var pxmxEntities = new List<LqKdPxmxEntity>();

                    foreach (var item in batchData)
                    {
                        try
                        {
                            // 3. 生成开单记录ID
                            string kdjlbId = YitIdHelper.NextId().ToString();

                            // 4. 创建开单记录
                            var kdjlbEntity = new LqKdKdjlbEntity
                            {
                                Id = kdjlbId,
                                Djmd = (string)item.F_StoreId, // 直接使用F_StoreId
                                Kdhy = (string)item.F_MemberId,
                                Kdhyc = (string)item.会员名称,
                                Jsj = "暂无",
                                Kdhysjh = (string)item.会员电话,
                                Kdrq = fixedDateTime,
                                Zdyj = Convert.ToDecimal(item.整单业绩 ?? 0),
                                Sfyj = Convert.ToDecimal(item.实付业绩 ?? 0),
                                Qk = Convert.ToDecimal(item.欠款 ?? 0),
                                CreateTime = fixedDateTime,
                                UpdateTime = fixedDateTime,
                                Sfskdd = "否",
                                IsEffective = 1,
                                Bz = "导入历史会员权益数据"
                            };

                            kdjlbEntities.Add(kdjlbEntity);

                            // 5. 创建品项明细
                            string pxmxId = YitIdHelper.NextId().ToString();

                            var pxmxEntity = new LqKdPxmxEntity
                            {
                                Id = pxmxId,
                                Glkdbh = kdjlbId,
                                Px = (string)item.F_ProjectId,
                                Pxmc = (string)item.品项名称,
                                Pxjg = Convert.ToDecimal(item.品项价格 ?? 0) / Convert.ToDecimal(item.品项次数 ?? 0),
                                ProjectNumber = Convert.ToDecimal(item.品项次数 ?? 0),
                                SourceType = (string)item.来源,
                                MemberId = (string)item.F_MemberId,
                                Yjsj = fixedDateTime,
                                CreateTIme = fixedDateTime,
                                IsEffective = 1,
                                IsEnabled = 0,
                                TotalPrice = Convert.ToDecimal(item.品项价格 ?? 0),
                                ActualPrice = Convert.ToDecimal(item.品项价格 ?? 0)
                            };

                            pxmxEntities.Add(pxmxEntity);

                            successCount++;
                        }
                        catch (Exception ex)
                        {
                            errorCount++;
                            errorMessages.Add($"处理记录 {(string)item.会员编号}-{(string)item.品项名称} 时出错: {ex.Message}");
                        }
                    }

                    // 6. 批量插入开单记录
                    if (kdjlbEntities.Any())
                    {
                        await _db.Insertable(kdjlbEntities).ExecuteCommandAsync();
                    }

                    // 7. 批量插入品项明细
                    if (pxmxEntities.Any())
                    {
                        await _db.Insertable(pxmxEntities).ExecuteCommandAsync();
                    }

                    // 8. 显示进度
                    if (batchIndex % 10 == 0)
                    {
                        Console.WriteLine($"已处理 {batchIndex + 1}/{totalBatches} 批次，成功 {successCount} 条，失败 {errorCount} 条");
                    }
                }

                _db.CommitTran();

                return new
                {
                    success = true,
                    message = "历史会员权益数据导入完成",
                    totalRecords = historyData.Count,
                    successCount = successCount,
                    errorCount = errorCount,
                    errorMessages = errorMessages.Take(10).ToList(), // 只返回前10个错误信息
                    importTime = DateTime.Now
                };
            }
            catch (Exception ex)
            {
                _db.RollbackTran();
                throw NCCException.Oh($"导入历史会员权益数据失败: {ex.Message}");
            }
        }
        #endregion

        #region 根据门店获取该门店的欠款记录
        /// <summary>
        /// 根据门店获取该门店的欠款记录
        /// </summary>
        /// <param name="input">查询参数</param>
        /// <returns>分页的欠款记录</returns>
        /// <remarks>
        /// 获取指定门店的欠款记录，考虑已缴欠款情况，支持分页
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "storeId": "门店ID",
        ///   "currentPage": 1,
        ///   "pageSize": 10,
        ///   "sidx": "Kdrq",
        ///   "sort": "desc"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - storeId: 门店ID，必填
        /// - currentPage: 当前页码
        /// - pageSize: 每页大小
        /// - sidx: 排序字段，默认按开单日期
        /// - sort: 排序方式，默认降序
        /// 
        /// 返回说明：
        /// - 只返回仍有欠款的记录（总欠款 > 已缴欠款）
        /// - 包含开单编号、客户信息、欠款金额、已缴金额等
        /// </remarks>
        /// <response code="200">成功返回分页的欠款记录列表</response>
        /// <response code="400">门店ID不能为空</response>
        /// <response code="500">服务器错误</response>
        [HttpGet("GetDebtRecordByStoreId")]
        public async Task<dynamic> GetDebtRecordByStoreId([FromQuery] DebtRecordQueryInput input)
        {
            try
            {
                if (string.IsNullOrEmpty(input.StoreId))
                {
                    throw NCCException.Oh("门店ID不能为空");
                }
                var sidx = string.IsNullOrEmpty(input.sidx) ? "Kdrq" : input.sidx;
                var sort = string.IsNullOrEmpty(input.sort) ? "desc" : input.sort;
                // 获取该门店的欠款记录，考虑已缴欠款情况
                // 只返回仍有欠款的记录：总欠款 > 已缴欠款
                var debtRecords = await _db.Queryable<LqKdKdjlbEntity>()
                    .Where(p => p.Djmd == input.StoreId && p.Qk > p.PaidDebt && p.IsEffective == StatusEnum.有效.GetHashCode())
                    .Select(it => new LqKdKdjlbListOutput
                    {
                        id = it.Id,
                        djmd = it.Djmd,
                        jsj = it.Jsj,
                        kdrq = it.Kdrq,
                        gjlx = it.Gjlx,
                        hgjg = it.Hgjg,
                        zdyj = it.Zdyj,
                        sfyj = it.Sfyj,
                        qk = it.Qk,
                        ckfs = it.Ckfs,
                        paidDebt = it.PaidDebt,
                        fkfs = it.Fkfs,
                        fkyy = it.Fkyy,
                        fkpd = it.Fkpd,
                        khly = it.Khly,
                        tjr = it.Tjr,
                        deductAmount = it.DeductAmount,
                        sfskdd = it.Sfskdd,
                        jj = it.Jj,
                        bz = it.Bz,
                        kdhy = it.Kdhy,
                        kdhyc = SqlFunc.Subqueryable<LqKhxxEntity>().Where(x => x.Id == it.Kdhy).Select(x => x.Khmc),
                        kdhysjh = SqlFunc.Subqueryable<LqKhxxEntity>().Where(x => x.Id == it.Kdhy).Select(x => x.Sjh),
                        isEffective = it.IsEffective,
                        createUser = it.CreateUser,
                        createUserName = SqlFunc.Subqueryable<UserEntity>().Where(x => x.Id == it.CreateUser).Select(x => x.RealName),
                    })
                    .MergeTable()
                    .OrderBy(sidx + " " + sort)
                    .ToPagedListAsync(input.currentPage, input.pageSize);

                return PageResult<LqKdKdjlbListOutput>.SqlSugarPageResult(debtRecords);
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"获取门店欠款记录失败: {ex.Message}");
            }
        }
        #endregion

        #region 获取某个会员的开单记录
        /// <summary>
        /// 获取某个会员的开单记录
        /// </summary>
        /// <remarks>
        /// 根据会员ID查询该会员的所有开单记录，支持分页和时间筛选
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "memberId": "会员ID",
        ///   "storeId": "门店ID",
        ///   "startTime": "2025-01-01",
        ///   "endTime": "2025-12-31",
        ///   "currentPage": 1,
        ///   "pageSize": 20
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - memberId: 会员ID（必填）
        /// - storeId: 门店ID（可选）
        /// - startTime: 开始时间（可选）
        /// - endTime: 结束时间（可选）
        /// - currentPage: 当前页码
        /// - pageSize: 每页大小
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>分页的开单记录列表</returns>
        /// <response code="200">成功返回会员开单记录列表</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpGet("GetBillingRecordByMemberId")]
        public async Task<dynamic> GetBillingRecordByMemberId([FromQuery] BillingRecordQueryInput input)
        {
            try
            {
                var sidx = input.sidx == null ? "kdrq" : input.sidx;

                var data = await _db.Queryable<LqKdKdjlbEntity>()
                    .Where(p => p.Kdhy == input.MemberId)
                    .WhereIF(input.StartTime.HasValue, p => p.Kdrq >= input.StartTime.Value)
                    .WhereIF(input.EndTime.HasValue, p => p.Kdrq <= input.EndTime.Value)
                    .Select(it => new LqKdKdjlbListOutput
                    {
                        id = it.Id,
                        djmd = it.Djmd,
                        jsj = it.Jsj,
                        kdrq = it.Kdrq,
                        gjlx = it.Gjlx,
                        hgjg = it.Hgjg,
                        zdyj = it.Zdyj,
                        sfyj = it.Sfyj,
                        qk = it.Qk,
                        ckfs = it.Ckfs,
                        fkfs = it.Fkfs,
                        fkyy = it.Fkyy,
                        fkpd = it.Fkpd,
                        khly = it.Khly,
                        tjr = it.Tjr,
                        deductAmount = it.DeductAmount,
                        sfskdd = it.Sfskdd,
                        jj = it.Jj,
                        bz = it.Bz,
                        kdhy = it.Kdhy,
                        kdhyc = SqlFunc.Subqueryable<LqKhxxEntity>().Where(x => x.Id == it.Kdhy).Select(x => x.Khmc),
                        kdhysjh = SqlFunc.Subqueryable<LqKhxxEntity>().Where(x => x.Id == it.Kdhy).Select(x => x.Sjh),
                        isEffective = it.IsEffective,
                        createUser = it.CreateUser,
                        createUserName = SqlFunc.Subqueryable<UserEntity>().Where(x => x.Id == it.CreateUser).Select(x => x.RealName),
                    })
                    .MergeTable()
                    .OrderBy(sidx + " " + input.sort)
                    .ToPagedListAsync(input.currentPage, input.pageSize);

                // 获取当前页的开单记录ID列表
                var billingIds = data.list.Select(x => x.id).ToList();

                // 批量查询品项明细
                var itemDetails = new List<LqKdPxmxInfoOutput>();
                if (billingIds.Any())
                {
                    itemDetails = await _db.Queryable<LqKdPxmxEntity>()
                        .Where(x => billingIds.Contains(x.Glkdbh) && x.IsEffective == StatusEnum.有效.GetHashCode())
                        .Select(x => new LqKdPxmxInfoOutput
                        {
                            id = x.Id,
                            glkdbh = x.Glkdbh,
                            px = x.Px,
                            pxmc = x.Pxmc,
                            pxjg = x.Pxjg,
                            projectNumber = x.ProjectNumber,
                            isEnabled = x.IsEnabled,
                            sourceType = x.SourceType,
                            memberId = x.MemberId,
                            createTime = x.CreateTIme,
                            totalPrice = x.TotalPrice,
                            actualPrice = x.ActualPrice,
                            remark = x.Remark,
                            isEffective = x.IsEffective
                        })
                        .ToListAsync();
                }
                // 按开单ID分组品项明细
                var itemDetailsGrouped = itemDetails.GroupBy(x => x.glkdbh)
                    .ToDictionary(g => g.Key, g => g.ToList());

                // 为每个开单记录分配品项明细
                foreach (var item in data.list)
                {
                    item.ItemDetails = itemDetailsGrouped.ContainsKey(item.id)
                        ? itemDetailsGrouped[item.id]
                        : new List<LqKdPxmxInfoOutput>();
                }

                return PageResult<LqKdKdjlbListOutput>.SqlSugarPageResult(data);
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"获取会员开单记录失败: {ex.Message}");
            }
        }
        #endregion

        #region 根据开单id获取当前开单欠款信息
        /// <summary>
        /// 根据开单id获取当前开单欠款信息
        /// </summary>
        /// <param name="billingId">开单记录ID</param>
        /// <returns>当前开单欠款信息</returns>
        [HttpGet("GetBillingDebtInfo/{billingId}")]
        public async Task<dynamic> GetBillingDebtInfoByBillingId(string billingId)
        {
            //返回 整单金额、实付金额、欠款金额、已缴欠款金额
            var entity = await _db.Queryable<LqKdKdjlbEntity>().FirstAsync(p => p.Id == billingId);
            if (entity == null)
            {
                throw NCCException.Oh("开单记录不存在");
            }
            return new
            {
                zdyj = entity.Zdyj,
                sfyj = entity.Sfyj,
                qk = entity.Qk,
                PaidDebt = entity.PaidDebt,
            };
        }
        #endregion

        #region 修改开单金额
        /// <summary>
        /// 修改开单金额
        /// </summary>
        /// <param name="input">修改开单金额输入</param>
        /// <returns>修改结果</returns>
        /// <remarks>
        /// 修改开单记录表的金额以及品项明细表里面的金额
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "billingId": "123456789",
        ///   "zdyj": 1000.00,
        ///   "sfyj": 800.00,
        ///   "deductAmount": 200.00,
        ///   "qk": 0.00,
        ///   "itemDetails": [
        ///     {
        ///       "itemDetailId": "item001",
        ///       "pxjg": 500.00,
        ///       "totalPrice": 500.00,
        ///       "actualPrice": 400.00
        ///     }
        ///   ],
        ///   "remark": "金额调整"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - billingId: 开单记录ID（必填）
        /// - zdyj: 整单业绩
        /// - sfyj: 实付业绩
        /// - deductAmount: 储扣总金额
        /// - qk: 欠款
        /// - itemDetails: 品项明细金额列表
        /// - remark: 修改备注
        /// </remarks>
        /// <response code="200">成功修改开单金额</response>
        /// <response code="400">请求参数错误</response>
        /// <response code="404">开单记录不存在</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPut("UpdateBillingAmount")]
        public async Task<dynamic> UpdateBillingAmount(LqKdKdjlbUpdateAmountInput input)
        {
            try
            {
                // 验证输入参数
                if (string.IsNullOrEmpty(input.BillingId))
                {
                    throw NCCException.Oh("开单记录ID不能为空");
                }
                // 检查开单记录是否存在且有效
                var billingEntity = await _db.Queryable<LqKdKdjlbEntity>().Where(w => w.Id == input.BillingId && w.IsEffective == StatusEnum.有效.GetHashCode()).FirstAsync();
                if (billingEntity == null)
                {
                    throw NCCException.Oh("开单记录不存在或已作废");
                }
                // 验证品项明细
                if (input.ItemDetails != null && input.ItemDetails.Any())
                {
                    var itemDetailIds = input.ItemDetails.Select(x => x.ItemDetailId).ToList();
                    var existingItems = await _db.Queryable<LqKdPxmxEntity>().Where(w => w.Glkdbh == input.BillingId && itemDetailIds.Contains(w.Id)).ToListAsync();
                    if (existingItems.Count != input.ItemDetails.Count)
                    {
                        throw NCCException.Oh("部分品项明细不存在或不属于该开单记录");
                    }
                }
                // 开始事务
                _db.BeginTran();
                try
                {
                    // 更新开单记录金额
                    var updateResult = await _db.Updateable<LqKdKdjlbEntity>().SetColumns(it => new LqKdKdjlbEntity
                    {
                        Zdyj = input.Zdyj,
                        Sfyj = input.Sfyj,
                        DeductAmount = input.DeductAmount,
                        Qk = input.Qk,
                        UpdateTime = DateTime.Now
                    }).Where(w => w.Id == input.BillingId).ExecuteCommandAsync();

                    if (updateResult <= 0)
                    {
                        throw NCCException.Oh("更新开单记录金额失败");
                    }

                    // 更新品项明细金额
                    if (input.ItemDetails != null && input.ItemDetails.Any())
                    {
                        foreach (var itemDetail in input.ItemDetails)
                        {
                            var itemUpdateResult = await _db.Updateable<LqKdPxmxEntity>().SetColumns(it => new LqKdPxmxEntity
                            {
                                Pxjg = itemDetail.Pxjg,
                                TotalPrice = itemDetail.TotalPrice,
                                ActualPrice = itemDetail.ActualPrice
                            }).Where(w => w.Id == itemDetail.ItemDetailId && w.Glkdbh == input.BillingId).ExecuteCommandAsync();

                            if (itemUpdateResult <= 0)
                            {
                                throw NCCException.Oh($"更新品项明细金额失败，品项ID: {itemDetail.ItemDetailId}");
                            }
                        }
                    }
                    // 提交事务
                    _db.CommitTran();
                    return "修改开单金额成功";
                }
                catch (Exception ex)
                {
                    // 回滚事务
                    _db.RollbackTran();
                    throw NCCException.Oh($"修改开单金额失败: {ex.Message}");
                }
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"修改开单金额时发生错误: {ex.Message}");
            }
        }
        #endregion

        #region 获取门店某个时间段的开单记录汇总信息
        /// <summary>
        /// 获取门店某个时间段的开单记录汇总信息
        /// </summary>
        /// <param name="input">查询参数</param>
        /// <returns>开单记录汇总信息</returns>
        [HttpGet("GetBillingRecordSummaryByStoreId")]
        public async Task<dynamic> GetBillingRecordSummaryByStoreId([FromQuery] BillingRecordSummaryQueryInput input)
        {
            try
            {
                // 验证参数
                if (string.IsNullOrEmpty(input.StoreId))
                {
                    throw NCCException.Oh("门店ID不能为空");
                }

                // 如果开始时间和结束时间为空，就默认是当月
                if (input.StartTime == null || input.EndTime == null)
                {
                    input.StartTime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
                    input.EndTime = DateTime.Now.AddDays(DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month));
                }

                // 查询开单记录
                var billingRecords = await _db.Queryable<LqKdKdjlbEntity>()
                    .Where(w => w.Djmd == input.StoreId && w.Kdrq >= input.StartTime && w.Kdrq <= input.EndTime && w.IsEffective == StatusEnum.有效.GetHashCode())
                    .Select(it => new
                    {
                        id = it.Id,
                        djmd = it.Djmd,
                        jsj = it.Jsj,
                        kdrq = it.Kdrq,
                        gjlx = it.Gjlx,
                        zdyj = it.Zdyj,
                        sfyj = it.Sfyj,
                        qk = it.Qk,
                        kdhyc = SqlFunc.Subqueryable<LqKhxxEntity>().Where(x => x.Id == it.Kdhy).Select(x => x.Khmc),
                        kdhysjh = SqlFunc.Subqueryable<LqKhxxEntity>().Where(x => x.Id == it.Kdhy).Select(x => x.Sjh),
                        fkfs = it.Fkfs, // 付款方式
                        khly = it.Khly, // 客户来源
                        bz = it.Bz, // 备注
                        createTime = it.CreateTime
                    })
                    .ToListAsync();

                if (!billingRecords.Any())
                {
                    return new
                    {
                        success = true,
                        data = new
                        {
                            billingSummary = new
                            {
                                totalCount = 0,
                                totalAmount = 0,
                                totalPaidAmount = 0,
                                totalDebt = 0,
                                billingRecords = new List<object>()
                            },
                            itemSummary = new
                            {
                                purchased = new { count = 0, totalAmount = 0, items = new List<object>() },
                                gifted = new { count = 0, totalAmount = 0, items = new List<object>() },
                                experience = new { count = 0, totalAmount = 0, items = new List<object>() }
                            },
                            healthTeacherSummary = new
                            {
                                totalCount = 0,
                                teachers = new List<object>()
                            }
                        },
                        message = "该时间段内无开单记录"
                    };
                }

                var billingIds = billingRecords.Select(x => x.id).ToList();

                // 查询品项明细，按F_SourceType分类
                var itemDetails = await _db.Queryable<LqKdPxmxEntity>()
                    .Where(w => billingIds.Contains(w.Glkdbh) && w.IsEffective == StatusEnum.有效.GetHashCode())
                    .Select(it => new
                    {
                        id = it.Id,
                        glkdbh = it.Glkdbh,
                        px = it.Px,
                        pxmc = it.Pxmc,
                        pxjg = it.Pxjg,
                        sourceType = it.SourceType,
                        totalPrice = it.TotalPrice,
                        actualPrice = it.ActualPrice,
                        projectNumber = it.ProjectNumber,
                        remark = it.Remark
                    })
                    .ToListAsync();

                // 按来源类型分类品项
                var purchasedItems = itemDetails.Where(x => x.sourceType == "购买").ToList();
                var giftedItems = itemDetails.Where(x => x.sourceType == "赠送").ToList();
                var experienceItems = itemDetails.Where(x => x.sourceType == "体验").ToList();

                // 查询健康师业绩数据
                var healthTeacherData = await _db.Queryable<LqKdJksyjEntity>()
                    .Where(w => billingIds.Contains(w.Glkdbh) && w.IsEffective == StatusEnum.有效.GetHashCode())
                    .Select(it => new
                    {
                        id = it.Id,
                        glkdbh = it.Glkdbh,
                        jks = it.Jks,
                        jksxm = it.Jksxm,
                        jkszh = it.Jkszh,
                        jksyj = it.Jksyj,
                        kdpxid = it.Kdpxid,
                        yjsj = it.Yjsj,
                        jsj_id = it.Jsj_id
                    })
                    .ToListAsync();

                // 构建按开单记录分组的详细数据
                var detailedRecords = billingRecords.Select(billing => new
                {
                    // 基本信息
                    date = billing.kdrq?.ToString("yyyy-MM-dd"),
                    customerName = billing.kdhyc,
                    customerPhone = billing.kdhysjh,
                    storeId = billing.djmd,
                    goldTriangle = billing.jsj,
                    customerType = billing.gjlx,

                    // 品项分类
                    purchasedItems = itemDetails.Where(x => x.glkdbh == billing.id && x.sourceType == "购买").Select(x => new
                    {
                        id = x.id,
                        itemName = x.pxmc,
                        itemCode = x.px,
                        price = x.pxjg,
                        totalPrice = x.totalPrice,
                        actualPrice = x.actualPrice,
                        projectNumber = x.projectNumber,
                        remark = x.remark
                    }).ToList(),

                    giftedItems = itemDetails.Where(x => x.glkdbh == billing.id && x.sourceType == "赠送").Select(x => new
                    {
                        id = x.id,
                        itemName = x.pxmc,
                        itemCode = x.px,
                        price = x.pxjg,
                        totalPrice = x.totalPrice,
                        actualPrice = x.actualPrice,
                        projectNumber = x.projectNumber,
                        remark = x.remark
                    }).ToList(),

                    experienceItems = itemDetails.Where(x => x.glkdbh == billing.id && x.sourceType == "体验").Select(x => new
                    {
                        id = x.id,
                        itemName = x.pxmc,
                        itemCode = x.px,
                        price = x.pxjg,
                        totalPrice = x.totalPrice,
                        actualPrice = x.actualPrice,
                        projectNumber = x.projectNumber,
                        remark = x.remark
                    }).ToList(),

                    // 金额信息
                    paidAmount = billing.sfyj,
                    debtAmount = billing.qk,
                    totalAmount = billing.zdyj,

                    // 健康师信息
                    healthTeachers = healthTeacherData.Where(x => x.glkdbh == billing.id).Select(x => new
                    {
                        teacherId = x.jks,
                        teacherName = x.jksxm,
                        teacherAccount = x.jkszh,
                        performance = x.jksyj,
                        performanceTime = x.yjsj,
                        itemDetailId = x.kdpxid,
                        goldTriangleId = x.jsj_id
                    }).ToList(),

                    // 其他信息
                    source = billing.khly, // 客户来源
                    paymentMethod = billing.fkfs, // 支付方式
                    remark = billing.bz, // 备注
                    createTime = billing.createTime
                }).OrderByDescending(x => x.date).ToList();

                // 构建返回结果
                var result = new
                {
                    success = true,
                    data = new
                    {
                        // 汇总统计
                        summary = new
                        {
                            totalCount = billingRecords.Count,
                            totalAmount = billingRecords.Sum(x => x.zdyj),
                            totalPaidAmount = billingRecords.Sum(x => x.sfyj),
                            totalDebt = billingRecords.Sum(x => x.qk),
                            totalPurchasedItems = purchasedItems.Count,
                            totalGiftedItems = giftedItems.Count,
                            totalExperienceItems = experienceItems.Count,
                            totalHealthTeachers = healthTeacherData.GroupBy(x => x.jks).Count()
                        },
                        // 详细记录列表
                        records = detailedRecords
                    },
                    message = "获取开单记录汇总信息成功"
                };

                return result;
            }
            catch (Exception ex)
            {
                throw NCCException.Oh($"获取开单记录汇总信息失败：{ex.Message}");
            }
        }
        #endregion

        #region 私有方法
        /// <summary>
        /// 检查开单记录是否可以操作
        /// </summary>
        /// <param name="billingId">开单记录ID</param>
        /// <returns>是否可以作废</returns>
        private async Task<(bool canCancel, string errorMessage)> CheckBillingCanCancelAsync(string billingId)
        {
            try
            {
                // 查询开单记录
                var entity = await _db.Queryable<LqKdKdjlbEntity>().FirstAsync(p => p.Id == billingId);
                if (entity == null)
                {
                    return (false, "开单记录不存在");
                }
                // 检查是否已经作废
                if (entity.IsEffective == StatusEnum.无效.GetHashCode())
                {
                    return (false, "该开单记录已经作废");
                }
                // 判断是否有对应的补缴记录
                var qkbjList = await _db.Queryable<LqKdKdjlbEntity>().Where(p => p.SupplementBillingId == billingId && p.IsEffective == StatusEnum.有效.GetHashCode()).ToListAsync();
                if (qkbjList.Any())
                {
                    return (false, "该开单记录有对应的补缴记录，不能进行操作");
                }
                // 查询开单记录下的品项明细ID列表
                var pxmxIdList = await _db.Queryable<LqKdPxmxEntity>().Where(p => p.Glkdbh == billingId && p.IsEffective == StatusEnum.有效.GetHashCode()).Select(p => p.Id).Distinct().ToListAsync();
                // 判断是否有对应的消耗记录
                var xhPxmxList = await _db.Queryable<LqXhPxmxEntity>().Where(p => pxmxIdList.Contains(p.BillingItemId) && p.IsEffective == StatusEnum.有效.GetHashCode()).ToListAsync();
                if (xhPxmxList.Any())
                {
                    return (false, "该开单记录有对应的消耗记录，不能进行操作");
                }
                // 判断是否有退卡记录
                var hytkMxList = await _db.Queryable<LqHytkMxEntity>().Where(p => pxmxIdList.Contains(p.BillingItemId) && p.IsEffective == StatusEnum.有效.GetHashCode()).ToListAsync();
                if (hytkMxList.Any())
                {
                    return (false, "该开单记录有对应的退卡记录，不能进行操作");
                }
                // 判断是否已经有储扣记录
                var deductInfoList = await _db.Queryable<LqKdDeductinfoEntity>().Where(p => pxmxIdList.Contains(p.DeductId) && p.IsEffective == StatusEnum.有效.GetHashCode()).ToListAsync();
                if (deductInfoList.Any())
                {
                    return (false, "该开单记录有对应的储扣记录，不能进行操作");
                }

                return (true, string.Empty);
            }
            catch (Exception ex)
            {
                return (false, $"检查开单记录状态失败: {ex.Message}");
            }
        }
        #endregion

        #region 会员转卡操作
        /// <summary>
        /// 会员转卡操作
        /// </summary>
        /// <remarks>
        /// 转卡是指一个会员把自己剩余的某些品项，转给另外一个会员
        /// 转卡会员转出的品项，就做退卡操作
        /// 被转卡会员收到的品项，做开卡操作
        /// 系统会自动从原开单记录中获取健康师和科技部老师的业绩数据
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "fromMemberId": "member001",
        ///   "toMemberId": "member002", 
        ///   "storeId": "store001",
        ///   "signatureFile": "base64签名数据",
        ///   "remarks": "转卡备注信息",
        ///   "transferItems": [
        ///     {
        ///       "billingItemId": "pxmx001",
        ///       "transferQuantity": 5,
        ///       "itemName": "美容项目A",
        ///       "itemPrice": 100.00
        ///     }
        ///   ]
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - fromMemberId: 转出会员ID（必填）
        /// - toMemberId: 转入会员ID（必填）
        /// - storeId: 门店ID（必填）
        /// - signatureFile: 转出会员签名文件（必填）
        /// - remarks: 转卡备注信息（可选）
        /// - transferItems: 转出品项列表（必填）
        ///   - billingItemId: 开单品项明细ID（必填）
        ///   - transferQuantity: 转出数量（必填，大于0）
        ///   - itemName: 品项名称（必填）
        ///   - itemPrice: 品项价格（必填，大于等于0）
        /// 
        /// 业务规则：
        /// - 转出和转入会员必须存在且有效
        /// - 转出品项的剩余数量必须足够
        /// - 系统会自动从原开单记录中获取健康师和科技部老师业绩数据
        /// - 转卡操作会同时创建退卡记录和开卡记录
        /// - 所有操作在事务中执行，确保数据一致性
        /// </remarks>
        /// <param name="input">转卡输入参数</param>
        /// <returns>转卡结果</returns>
        /// <response code="200">转卡成功</response>
        /// <response code="400">参数错误或业务规则验证失败</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("TransferCard")]
        public async Task<TransferCardOutput> TransferCard([FromBody] TransferCardInput input)
        {
            if (input == null)
            {
                throw NCCException.Oh("转卡参数不能为空");
            }
            var userInfo = await _userManager.GetUserInfo();
            var transferTime = DateTime.Now;
            try
            {
                // 开启事务
                _db.BeginTran();
                // 1. 验证转出和转入会员是否存在
                var fromMember = await _db.Queryable<LqKhxxEntity>().Where(x => x.Id == input.FromMemberId && x.IsEffective == StatusEnum.有效.GetHashCode()).FirstAsync();
                if (fromMember == null)
                {
                    throw NCCException.Oh("转出会员不存在或已失效");
                }
                var toMember = await _db.Queryable<LqKhxxEntity>().Where(x => x.Id == input.ToMemberId && x.IsEffective == StatusEnum.有效.GetHashCode()).FirstAsync();
                if (toMember == null)
                {
                    throw NCCException.Oh("转入会员不存在或已失效");
                }
                // 2. 验证转出品项的余额是否足够
                foreach (var item in input.TransferItems)
                {
                    var billingItem = await _db.Queryable<LqKdPxmxEntity>().Where(x => x.Id == item.BillingItemId && x.IsEffective == StatusEnum.有效.GetHashCode()).FirstAsync();
                    if (billingItem == null)
                    {
                        throw NCCException.Oh($"品项明细不存在：{item.ItemName}");
                    }
                    // 查询剩余数量
                    var remainingCount = await GetItemRemainingCount(item.BillingItemId);
                    if (remainingCount < item.TransferQuantity)
                    {
                        throw NCCException.Oh($"品项 {item.ItemName} 剩余数量不足，当前剩余：{remainingCount}，需要转出：{item.TransferQuantity}");
                    }
                }

                // 3. 创建退卡记录（转出方）
                var refundId = YitIdHelper.NextId().ToString();
                var totalRefundAmount = input.TransferItems.Sum(x => x.ItemPrice * x.TransferQuantity);
                var StoreEntity = await _db.Queryable<LqMdxxEntity>().Where(p => p.Id == input.StoreId).FirstAsync();
                var refundEntity = new LqHytkHytkEntity
                {
                    Id = refundId,
                    F_CreateTime = transferTime,
                    F_CreateUser = userInfo.userId,
                    IsEffective = StatusEnum.有效.GetHashCode(),
                    Czry = userInfo.userId,
                    Hy = input.FromMemberId,
                    Hymc = fromMember.Khmc,
                    Md = input.StoreId,
                    Mdmc = StoreEntity.Dm,
                    Mdbh = StoreEntity.Mdbm,
                    SignatureFile = input.SignatureFile,
                    Hyzh = input.FromMemberId,
                    Gklx = fromMember.Khlx,
                    Tksj = DateTime.Now,
                    Tkje = totalRefundAmount,
                    ActualRefundAmount = totalRefundAmount, // 转卡时实退金额等于退卡总金额
                    Tkyy = "转卡",
                    Bz = $"转卡给会员：{toMember.Khmc}，{input.Remarks}"
                };
                await _db.Insertable(refundEntity).ExecuteCommandAsync();

                // 4. 创建退卡品项明细和业绩记录
                var refundMxEntities = new List<LqHytkMxEntity>();
                var refundJksyjEntities = new List<LqHytkJksyjEntity>();

                var refundKjbsyjEntities = new List<LqHytkKjbsyjEntity>();

                foreach (var item in input.TransferItems)
                {
                    var refundPxmxEntity = await _db.Queryable<LqKdPxmxEntity>().Where(p => p.Id == item.BillingItemId).FirstAsync();
                    //计算品项扣除总金额
                    var totalItemDeduction = item.ItemPrice * item.TransferQuantity;
                    //保存退卡明细表
                    var refundMxEntity = new LqHytkMxEntity
                    {
                        Id = YitIdHelper.NextId().ToString(),
                        RefundInfoId = refundId,
                        BillingItemId = item.BillingItemId,
                        CreateTime = transferTime,
                        CreateUser = userInfo.userId,
                        Px = refundPxmxEntity.Px,
                        Pxmc = item.ItemName,
                        Pxjg = item.ItemPrice,
                        Tkje = totalItemDeduction,
                        ProjectNumber = item.TransferQuantity,
                        SourceType = refundPxmxEntity.SourceType,
                        TotalPrice = totalItemDeduction,
                        IsEffective = StatusEnum.有效.GetHashCode()
                    };
                    refundMxEntities.Add(refundMxEntity);
                    var refundKdyjEntities = _db.Queryable<LqKdJksyjEntity>().Where(p => p.Kdpxid == item.BillingItemId).ToList();
                    //保存退卡健康师业绩表
                    foreach (var jks in refundKdyjEntities)
                    {
                        //获取健康师当月金三角id
                        var monthString = DateTime.Now.ToString("yyyyMM");
                        var GetMonTeamResult = await _db.Queryable<LqJinsanjiaoUserEntity>().Where(p => p.UserId == jks.Jkszh && p.Month == monthString).ToListAsync();
                        var GetMonTeam = GetMonTeamResult.FirstOrDefault();
                        if (GetMonTeam == null)
                        {
                            throw NCCException.Oh($"健康师 {jks.Jksxm} 在 {monthString} 月份的金三角团队中不存在，请先配置金三角团队信息");
                        }
                        //获取本月
                        refundJksyjEntities.Add(new LqHytkJksyjEntity
                        {
                            Id = YitIdHelper.NextId().ToString(),
                            Gltkbh = refundId,
                            Jks = jks.Jkszh,
                            Jksxm = jks.Jksxm,
                            Jkszh = jks.Jkszh,
                            Jksyj = (totalItemDeduction / refundKdyjEntities.Count()),
                            Tksj = DateTime.Now,
                            F_jsjid = GetMonTeam.JsjId,
                            F_tkpxid = refundMxEntity.Id,
                            F_tkpxNumber = item.TransferQuantity,
                            F_CreateTime = transferTime,
                            F_CreateUser = userInfo.userId,
                            CardReturn = refundMxEntity.Id,
                            IsEffective = StatusEnum.有效.GetHashCode()
                        });
                    }
                    //查询科技部老师的业绩
                    var TechTeacherEntities = _db.Queryable<LqKdKjbsyjEntity>().Where(p => p.Kdpxid == item.BillingItemId).ToList();
                    // 创建退卡科技部老师业绩记录
                    foreach (var kjbs in TechTeacherEntities)
                    {
                        refundKjbsyjEntities.Add(new LqHytkKjbsyjEntity
                        {
                            Id = YitIdHelper.NextId().ToString(),
                            Gltkbh = refundId,
                            Kjbls = kjbs.Kjbls,
                            Kjblsxm = kjbs.Kjblsxm,
                            Kjblszh = kjbs.Kjblszh,
                            Kjblsyj = (totalItemDeduction / TechTeacherEntities.Count()),
                            Tksj = transferTime,
                            F_tkpxid = refundMxEntity.Id,
                            F_LaborCost = kjbs.LaborCost,
                            F_tkpxNumber = item.TransferQuantity,
                            F_CreateTime = transferTime,
                            F_CreateUser = userInfo.userId,
                            CardReturn = refundMxEntity.Id,
                            IsEffective = StatusEnum.有效.GetHashCode()
                        });
                    }
                }

                await _db.Insertable(refundMxEntities).ExecuteCommandAsync();
                if (refundJksyjEntities.Any())
                {
                    await _db.Insertable(refundJksyjEntities).ExecuteCommandAsync();
                }
                if (refundKjbsyjEntities.Any())
                {
                    await _db.Insertable(refundKjbsyjEntities).ExecuteCommandAsync();
                }

                // 5. 创建开卡记录（转入方）
                var billingId = YitIdHelper.NextId().ToString();
                var billingEntity = new LqKdKdjlbEntity
                {
                    Id = billingId,
                    CreateTime = transferTime,
                    UpdateTime = transferTime,
                    IsEffective = StatusEnum.有效.GetHashCode(),
                    CreateUser = userInfo.userId,
                    Kdhy = input.ToMemberId,
                    Djmd = input.StoreId,
                    Kdrq = DateTime.Now,
                    Gjlx = toMember.Khlx,
                    Fkfs = "转卡",
                    Sfskdd = "否",
                    Zdyj = input.TransferItems.Sum(x => x.ItemPrice * x.TransferQuantity),
                    Sfyj = input.TransferItems.Sum(x => x.ItemPrice * x.TransferQuantity),
                    Bz = $"从会员 {fromMember.Khmc} 转入，{input.Remarks}"
                };
                await _db.Insertable(billingEntity).ExecuteCommandAsync();
                // 6. 创建开单品项明细和业绩记录
                var billingPxmxEntities = new List<LqKdPxmxEntity>();
                var billingJksyjEntities = new List<LqKdJksyjEntity>();
                var billingKjbsyjEntities = new List<LqKdKjbsyjEntity>();

                foreach (var item in input.TransferItems)
                {
                    var refundPxmxEntity = await _db.Queryable<LqKdPxmxEntity>().Where(p => p.Id == item.BillingItemId).FirstAsync();
                    var billingPxmxEntity = new LqKdPxmxEntity
                    {
                        Id = YitIdHelper.NextId().ToString(),
                        Glkdbh = billingId,
                        Px = refundPxmxEntity.Px,
                        Pxmc = item.ItemName,
                        Pxjg = item.ItemPrice,
                        MemberId = input.ToMemberId,
                        CreateTIme = transferTime,
                        ProjectNumber = item.TransferQuantity,
                        IsEnabled = StatusEnum.有效.GetHashCode(),
                        SourceType = refundPxmxEntity.SourceType,
                        TotalPrice = item.ItemPrice * item.TransferQuantity,
                        ActualPrice = item.ItemPrice * item.TransferQuantity,
                        IsEffective = StatusEnum.有效.GetHashCode(),
                        Remark = $"从会员 {fromMember.Khmc} 转入"
                    };
                    billingPxmxEntities.Add(billingPxmxEntity);

                    foreach (var jks in refundJksyjEntities)
                    {
                        billingJksyjEntities.Add(new LqKdJksyjEntity
                        {
                            Id = YitIdHelper.NextId().ToString(),
                            Glkdbh = billingId,
                            Jks = jks.Jkszh,
                            Jksxm = jks.Jksxm,
                            Jkszh = jks.Jkszh,
                            Jksyj = jks.Jksyj.ToString(),
                            Yjsj = transferTime,
                            Jsj_id = jks.F_jsjid,
                            Kdpxid = billingPxmxEntity.Id,
                            IsEffective = StatusEnum.有效.GetHashCode()
                        });
                    }

                    foreach (var kjbs in refundKjbsyjEntities)
                    {
                        billingKjbsyjEntities.Add(new LqKdKjbsyjEntity
                        {
                            Id = YitIdHelper.NextId().ToString(),
                            Glkdbh = billingId,
                            Kjbls = kjbs.Kjbls,
                            Kjblsxm = kjbs.Kjblsxm,
                            Kjblszh = kjbs.Kjblszh,
                            Kjblsyj = kjbs.Kjblsyj.ToString(),
                            Yjsj = transferTime,
                            Kdpxid = billingPxmxEntity.Id,
                            IsEffective = StatusEnum.有效.GetHashCode()
                        });
                    }
                }
                //记录转卡日志
                var transferLogEntity = new LqCardTransferLogEntity
                {
                    Id = YitIdHelper.NextId().ToString(),
                    RefundCardId = refundId,
                    BillingCardId = billingId,
                    TransferFromMemberId = input.FromMemberId,
                    TransferToMemberId = input.ToMemberId,
                    TransferAmount = input.TransferItems.Sum(x => x.ItemPrice * x.TransferQuantity),
                    TransferTime = transferTime,
                    OperatorId = userInfo.userId,
                    Remarks = input.Remarks,
                    CreateTime = DateTime.Now,
                    IsEffective = StatusEnum.有效.GetHashCode()

                };
                await _db.Insertable(transferLogEntity).ExecuteCommandAsync();

                await _db.Insertable(billingPxmxEntities).ExecuteCommandAsync();
                if (billingJksyjEntities.Any())
                {
                    await _db.Insertable(billingJksyjEntities).ExecuteCommandAsync();
                }
                if (billingKjbsyjEntities.Any())
                {
                    await _db.Insertable(billingKjbsyjEntities).ExecuteCommandAsync();
                }
                // 提交事务
                _db.CommitTran();

                return new TransferCardOutput
                {
                    Success = true,
                    TransferId = refundId,
                    RefundId = refundId,
                    BillingId = billingId,
                    TotalAmount = input.TransferItems.Sum(x => x.ItemPrice * x.TransferQuantity),
                    TotalQuantity = input.TransferItems.Sum(x => x.TransferQuantity),
                    FromMemberName = fromMember.Khmc,
                    ToMemberName = toMember.Khmc,
                    TransferTime = transferTime,
                    Message = "转卡操作成功"
                };
            }
            catch (Exception ex)
            {
                _db.RollbackTran();
                _logger.LogError(ex, "转卡操作失败：{Message}", ex.Message);
                throw NCCException.Oh($"转卡操作失败：{ex.Message}");
            }
        }
        #endregion

        #region 获取品项剩余数量
        /// <summary>
        /// 获取品项剩余数量
        /// </summary>
        /// <param name="billingItemId">开单品项明细ID</param>
        /// <returns>剩余数量</returns>
        private async Task<int> GetItemRemainingCount(string billingItemId)
        {
            try
            {
                // 查询购买数量
                var purchasedCount = await _db.Queryable<LqKdPxmxEntity>()
                    .Where(x => x.Id == billingItemId && x.IsEffective == StatusEnum.有效.GetHashCode())
                    .SumAsync(x => x.ProjectNumber);

                // 查询消费数量
                var consumedCount = await _db.Queryable<LqXhPxmxEntity>()
                    .Where(x => x.BillingItemId == billingItemId && x.IsEffective == StatusEnum.有效.GetHashCode())
                    .SumAsync(x => x.ProjectNumber);

                // 查询退卡数量
                var refundedCount = await _db.Queryable<LqHytkMxEntity>()
                    .Where(x => x.BillingItemId == billingItemId && x.IsEffective == StatusEnum.有效.GetHashCode())
                    .SumAsync(x => x.ProjectNumber);

                // 查询储扣数量
                var deductCount = await _db.Queryable<LqKdDeductinfoEntity>()
                    .Where(x => x.DeductId == billingItemId && x.IsEffective == StatusEnum.有效.GetHashCode())
                    .SumAsync(x => x.ProjectNumber) ?? 0;

                // 计算剩余数量
                var remainingCount = (int)(purchasedCount - consumedCount - refundedCount - deductCount);
                return Math.Max(0, remainingCount); // 确保不为负数
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取品项剩余数量失败，品项ID：{BillingItemId}", billingItemId);
                throw NCCException.Oh($"获取品项剩余数量失败：{ex.Message}");
            }
        }
        #endregion

        #region 门店整体统计表
        /// <summary>
        /// 门店整体统计表
        /// </summary>
        /// <remarks>
        /// 统计每个健康师在指定时间周期内的各项数据指标
        /// 包括：邀约人数、预约人数、到店人数、开单人数、开单金额、消耗金额、人头、人次、消耗项目数
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "startTime": "2025-10-01",
        ///   "endTime": "2025-10-31",
        ///   "departmentId": "部门ID",
        ///   "storeId": "门店ID",
        ///   "employeeName": "健康师姓名"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - startTime: 开始时间（可选，默认为当月1号）
        /// - endTime: 结束时间（可选，默认为当前时间）
        /// - departmentId: 事业部ID（可选）
        /// - storeId: 门店ID（可选）
        /// - employeeName: 健康师姓名（可选）
        /// 
        /// 返回字段说明：
        /// - EmployeeId: 健康师ID
        /// - EmployeeName: 健康师姓名
        /// - StoreId: 门店ID
        /// - StoreName: 门店名称
        /// - DepartmentId: 事业部ID
        /// - DepartmentName: 事业部名称
        /// - InviteCount: 邀约人数（按客户去重）
        /// - AppointmentCount: 预约人数（按客户去重，无论预约状态）
        /// - VisitCount: 到店人数（按客户去重，仅统计状态为'已确认'的预约）
        /// - BillingCount: 开单人数（按开单记录去重）
        /// - BillingAmount: 开单金额（开单业绩总金额）
        /// - ConsumeAmount: 消耗金额（消耗业绩总金额）
        /// - HeadCount: 人头（按客户去重）
        /// - PersonCount: 人次（按客户+日期去重，同一客户不同天算多次）
        /// - ProjectCount: 消耗项目数（项目总次数）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>健康师统计数据列表</returns>
        /// <response code="200">成功返回统计数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">服务器错误</response>
        [HttpGet("get-health-coach-statistics")]
        public async Task<dynamic> GetHealthCoachStatistics([FromQuery] HealthCoachStatisticsQueryInput input)
        {
            try
            {
                // 设置默认时间范围（如果未提供，默认为当月）
                var startTime = input.StartTime ?? new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
                var endTime = input.EndTime ?? DateTime.Now;

                // 构建SQL查询
                var sql = $@"
                    SELECT 
                        u.F_Id as EmployeeId,
                        u.F_REALNAME as EmployeeName,
                        u.F_MDID as StoreId,
                        md.dm as StoreName,
                        md.syb as DepartmentId,
                        dept.F_FullName as DepartmentName,
                        
                        -- 邀约人数
                        COALESCE(invite_stats.InviteCount, 0) as InviteCount,
                        
                        -- 预约人数（无论状态）
                        COALESCE(appointment_stats.AppointmentCount, 0) as AppointmentCount,
                        
                        -- 到店人数（已确认状态）
                        COALESCE(visit_stats.VisitCount, 0) as VisitCount,
                        
                        -- 开单人数和金额
                        COALESCE(billing_stats.BillingCount, 0) as BillingCount,
                        COALESCE(billing_stats.BillingAmount, 0) as BillingAmount,
                        
                        -- 消耗相关统计
                        COALESCE(consume_stats.ConsumeAmount, 0) as ConsumeAmount,
                        COALESCE(consume_stats.HeadCount, 0) as HeadCount,
                        COALESCE(consume_stats.PersonCount, 0) as PersonCount,
                        COALESCE(consume_stats.ProjectCount, 0) as ProjectCount

                    FROM BASE_USER u
                    LEFT JOIN lq_mdxx md ON u.F_MDID = md.F_Id
                    LEFT JOIN base_organize dept ON md.syb = dept.F_Id

                    -- 邀约统计子查询
                    LEFT JOIN (
                        SELECT 
                            yyr as EmployeeId,
                            COUNT(DISTINCT yykh) as InviteCount
                        FROM lq_yaoyjl
                        WHERE yyr IS NOT NULL
                            AND F_CreateTime >= @startTime
                            AND F_CreateTime <= @endTime
                        GROUP BY yyr
                    ) invite_stats ON u.F_Id = invite_stats.EmployeeId

                    -- 预约统计子查询
                    LEFT JOIN (
                        SELECT 
                            yyr as EmployeeId,
                            COUNT(DISTINCT gk) as AppointmentCount
                        FROM lq_yyjl
                        WHERE yyr IS NOT NULL
                            AND F_CreateTime >= @startTime
                            AND F_CreateTime <= @endTime
                        GROUP BY yyr
                    ) appointment_stats ON u.F_Id = appointment_stats.EmployeeId

                    -- 到店统计子查询
                    LEFT JOIN (
                        SELECT 
                            yyr as EmployeeId,
                            COUNT(DISTINCT gk) as VisitCount
                        FROM lq_yyjl
                        WHERE yyr IS NOT NULL
                            AND F_Status = '已确认'
                            AND F_CreateTime >= @startTime
                            AND F_CreateTime <= @endTime
                        GROUP BY yyr
                    ) visit_stats ON u.F_Id = visit_stats.EmployeeId

                    -- 开单统计子查询
                    LEFT JOIN (
                        SELECT 
                            jkszh as EmployeeId,
                            COUNT(DISTINCT glkdbh) as BillingCount,
                            SUM(CAST(jksyj AS DECIMAL(18,2))) as BillingAmount
                        FROM lq_kd_jksyj
                        WHERE jkszh IS NOT NULL
                            AND F_IsEffective = 1
                            AND yjsj >= @startTime
                            AND yjsj <= @endTime
                        GROUP BY jkszh
                    ) billing_stats ON u.F_Id = billing_stats.EmployeeId

                    -- 消耗统计子查询
                    LEFT JOIN (
                        SELECT 
                            jksyj.jkszh as EmployeeId,
                            SUM(jksyj.jksyj) as ConsumeAmount,
                            COUNT(DISTINCT hyhk.hy) as HeadCount,
                            COUNT(DISTINCT CONCAT(jksyj.jkszh, '_', hyhk.hy, '_', DATE(hyhk.hksj))) as PersonCount,
                            SUM(jksyj.F_kdpxNumber) as ProjectCount
                        FROM lq_xh_jksyj jksyj
                        INNER JOIN lq_xh_hyhk hyhk ON jksyj.glkdbh = hyhk.F_Id
                        WHERE jksyj.jkszh IS NOT NULL
                            AND jksyj.F_IsEffective = 1
                            AND hyhk.F_IsEffective = 1
                            AND hyhk.hksj >= @startTime
                            AND hyhk.hksj <= @endTime
                        GROUP BY jksyj.jkszh
                    ) consume_stats ON u.F_Id = consume_stats.EmployeeId

                    WHERE u.F_GW = '健康师'
                ";

                // 添加条件过滤
                var conditions = new List<string>();
                var parameters = new List<SugarParameter>
                {
                    new SugarParameter("@startTime", startTime),
                    new SugarParameter("@endTime", endTime)
                };

                if (!string.IsNullOrEmpty(input.DepartmentId))
                {
                    conditions.Add("md.syb = @departmentId");
                    parameters.Add(new SugarParameter("@departmentId", input.DepartmentId));
                }

                if (!string.IsNullOrEmpty(input.StoreId))
                {
                    conditions.Add("u.F_MDID = @storeId");
                    parameters.Add(new SugarParameter("@storeId", input.StoreId));
                }

                if (!string.IsNullOrEmpty(input.EmployeeName))
                {
                    conditions.Add("u.F_REALNAME LIKE @employeeName");
                    parameters.Add(new SugarParameter("@employeeName", $"%{input.EmployeeName}%"));
                }

                if (conditions.Any())
                {
                    sql += " AND " + string.Join(" AND ", conditions);
                }

                sql += " ORDER BY u.F_REALNAME";

                // 执行查询
                var allData = await _db.Ado.SqlQueryAsync<HealthCoachStatisticsOutput>(sql, parameters);

                // 手动分页
                var totalCount = allData.Count;
                var pagedData = allData
                    .Skip((input.currentPage - 1) * input.pageSize)
                    .Take(input.pageSize)
                    .ToList();

                // 直接返回分页结果
                return new
                {
                    list = pagedData,
                    pagination = new
                    {
                        pageIndex = input.currentPage,
                        pageSize = input.pageSize,
                        totalCount = totalCount
                    }
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取健康师统计数据失败");
                throw NCCException.Oh($"获取健康师统计数据失败：{ex.Message}");
            }
        }
        #endregion
    }
}