antissoft / lvqianmeiye_ERP

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Mapster;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using NCC.ClayObject;
using NCC.Code;
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.LqKhxx;
using NCC.Extend.Entitys.Dto.LqXhHyhk;
using NCC.Extend.Entitys.Enum;
using NCC.Extend.Entitys.lq_hytk_hytk;
using NCC.Extend.Entitys.lq_hytk_mx;
using NCC.Extend.Entitys.lq_kd_kdjlb;
using NCC.Extend.Entitys.lq_kd_pxmx;
using NCC.Extend.Entitys.lq_khxx;
using NCC.Extend.Entitys.lq_mdxx;
using NCC.Extend.Entitys.lq_xh_hyhk;
using NCC.Extend.Entitys.lq_xh_pxmx;
using NCC.Extend.Entitys.lq_xmzl;
using NCC.Extend.Interfaces.LqKhxx;
using NCC.Extend.Entitys.Config;
using NCC.System.Entitys.Permission;
using NCC.FriendlyException;
using NCC.JsonSerialization;
using SqlSugar;
using Yitter.IdGenerator;
using NCC.Extend.Entitys.lq_kd_deductinfo;
using Microsoft.AspNetCore.Authorization;

namespace NCC.Extend.LqKhxx
{
    /// <summary>
    /// 绿纤客户（会员）资料服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "绿纤客户（会员）资料服务", Name = "LqKhxx", Order = 200)]
    [Route("api/Extend/[controller]")]
    public class LqKhxxService : ILqKhxxService, IDynamicApiController, ITransient
    {
        private readonly ISqlSugarRepository<LqKhxxEntity> _lqKhxxRepository;
        private readonly SqlSugarScope _db;
        private readonly IUserManager _userManager;
        private readonly ILogger<LqKhxxService> _logger;

        /// <summary>
        /// 初始化一个<see cref="LqKhxxService"/>类型的新实例
        /// </summary>
        public LqKhxxService(ISqlSugarRepository<LqKhxxEntity> lqKhxxRepository, IUserManager userManager, ILogger<LqKhxxService> logger)
        {
            _lqKhxxRepository = lqKhxxRepository;
            _db = _lqKhxxRepository.Context;
            _userManager = userManager;
            _logger = logger;
        }

        #region 客户资料
        /// <summary>
        /// 获取客户资料
        /// </summary>
        /// <param name="id">参数</param>
        /// <returns></returns>
        [HttpGet("{id}")]
        public async Task<dynamic> GetInfo(string id)
        {
            var entity = await _db.Queryable<LqKhxxEntity>().FirstAsync(p => p.Id == id);
            var output = entity.Adapt<LqKhxxInfoOutput>();

            // 获取归属门店名称
            if (!string.IsNullOrEmpty(entity.Gsmd))
            {
                var store = await _db.Queryable<LqMdxxEntity>().Where(s => s.Id == entity.Gsmd).FirstAsync();
                output.gsmdName = store?.Dm ?? "";
            }

            if (!string.IsNullOrEmpty(entity.ExpandUser))
            {
                var expandUser = await _db.Queryable<UserEntity>().Where(u => u.Id == entity.ExpandUser).FirstAsync();
                output.expandUserName = expandUser?.RealName ?? "";
            }

            if (!string.IsNullOrEmpty(entity.MainHealthUser))
            {
                var mainHealthUser = await _db.Queryable<UserEntity>().Where(u => u.Id == entity.MainHealthUser).FirstAsync();
                output.mainHealthUserName = mainHealthUser?.RealName ?? "";
            }

            if (!string.IsNullOrEmpty(entity.SubHealthUser))
            {
                var subHealthUser = await _db.Queryable<UserEntity>().Where(u => u.Id == entity.SubHealthUser).FirstAsync();
                output.subHealthUserName = subHealthUser?.RealName ?? "";
            }
            if (!string.IsNullOrEmpty(entity.Tjr))
            {
                var tjr = await _db.Queryable<UserEntity>().Where(u => u.Id == entity.Tjr).FirstAsync();
                output.tjrName = tjr?.RealName ?? "";
            }
            return output;
        }
        #endregion

        #region 客户资料列表
        /// <summary>
        /// 获取客户资料列表
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("")]
        public async Task<dynamic> GetList([FromQuery] LqKhxxListQueryInput input)
        {
            var sidx = input.sidx == null ? "id" : input.sidx;
            List<string> queryZcsj = input.zcsj != null ? input.zcsj.Split(',').ToObeject<List<string>>() : null;
            DateTime? startZcsj = queryZcsj != null ? Ext.GetDateTime(queryZcsj.First()) : null;
            DateTime? endZcsj = queryZcsj != null ? Ext.GetDateTime(queryZcsj.Last()) : null;
            
            // 处理沉睡天数范围
            List<string> querySleepDaysRange = input.SleepDaysRange != null ? input.SleepDaysRange.Split(',').ToObeject<List<string>>() : null;
            int? minSleepDays = null;
            int? maxSleepDays = null;
            if (querySleepDaysRange != null && querySleepDaysRange.Count >= 2)
            {
                int.TryParse(querySleepDaysRange[0], out int min);
                int.TryParse(querySleepDaysRange[1], out int max);
                minSleepDays = min;
                maxSleepDays = max;
            }
            
            var data = await _db.Queryable<LqKhxxEntity>()
                .WhereIF(!string.IsNullOrEmpty(input.keyWord), p => p.Khmc.Contains(input.keyWord) || p.Sjh.Contains(input.keyWord) || p.Dah.Contains(input.keyWord))
                .WhereIF(input.IsTechMemberbh.HasValue, p => p.IsTechMember == input.IsTechMemberbh)
                .WhereIF(input.IsEducationMember.HasValue, p => p.IsEducationMember == input.IsEducationMember)
                .WhereIF(!string.IsNullOrEmpty(input.id), p => p.Id.Contains(input.id))
                .WhereIF(!string.IsNullOrEmpty(input.khmc), p => p.Khmc.Contains(input.khmc))
                .WhereIF(!string.IsNullOrEmpty(input.sjh), p => p.Sjh.Contains(input.sjh))
                .WhereIF(!string.IsNullOrEmpty(input.dah), p => p.Dah.Contains(input.dah))
                .WhereIF(!string.IsNullOrEmpty(input.xb), p => p.Xb.Equals(input.xb))
                .WhereIF(!string.IsNullOrEmpty(input.gsmd), p => p.Gsmd.Contains(input.gsmd))
                .WhereIF(!string.IsNullOrEmpty(input.mainHealthUser), p => p.MainHealthUser.Contains(input.mainHealthUser))
                .WhereIF(!string.IsNullOrEmpty(input.subHealthUser), p => p.SubHealthUser.Contains(input.subHealthUser))
                .WhereIF(!string.IsNullOrEmpty(input.khlx), p => p.Khlx.Equals(input.khlx))
                .WhereIF(!string.IsNullOrEmpty(input.khjd), p => p.Khjd.Equals(input.khjd))
                .WhereIF(!string.IsNullOrEmpty(input.khxf), p => p.Khxf.Contains(input.khxf))
                .WhereIF(!string.IsNullOrEmpty(input.xfpc), p => p.Xfpc.Equals(input.xfpc))
                .WhereIF(!string.IsNullOrEmpty(input.tjr), p => p.Tjr.Contains(input.tjr))
                .WhereIF(!string.IsNullOrEmpty(input.jdqd), p => p.Jdqd.Contains(input.jdqd))
                .WhereIF(!string.IsNullOrEmpty(input.lxdz), p => p.Lxdz.Contains(input.lxdz))
                .WhereIF(!string.IsNullOrEmpty(input.bz), p => p.Bz.Contains(input.bz))
                .WhereIF(input.ConsumeLevel.HasValue, p => p.ConsumeLevel == input.ConsumeLevel.Value)
                .WhereIF(minSleepDays.HasValue, p => p.SleepDays >= minSleepDays.Value)
                .WhereIF(maxSleepDays.HasValue, p => p.SleepDays <= maxSleepDays.Value)
                .WhereIF(queryZcsj != null, p => p.CreateTime >= new DateTime(startZcsj.ToDate().Year, startZcsj.ToDate().Month, startZcsj.ToDate().Day, 0, 0, 0))
                .WhereIF(queryZcsj != null, p => p.CreateTime <= new DateTime(endZcsj.ToDate().Year, endZcsj.ToDate().Month, endZcsj.ToDate().Day, 23, 59, 59))
                .Select(it => new LqKhxxListOutput
                {
                    id = it.Id,
                    khmc = it.Khmc,
                    sjh = it.Sjh,
                    dah = it.Dah,
                    xb = it.Xb,
                    gsmd = it.Gsmd,
                    gsmdName = SqlFunc.Subqueryable<LqMdxxEntity>().Where(u => u.Id == it.Gsmd).Select(u => u.Dm),
                    zcsj = it.Zcsj,
                    khlx = it.Khlx,
                    khjd = it.Khjd,
                    khxf = it.Khxf,
                    xfpc = it.Xfpc,
                    tjr = it.Tjr,
                    jdqd = it.Jdqd,
                    lxdz = it.Lxdz,
                    bz = it.Bz,
                    yanglsr = it.Yanglsr,
                    yinlsr = it.Yinlsr,
                    createTime = it.CreateTime,
                    expandUser = it.ExpandUser,
                    mainHealthUser = it.MainHealthUser,
                    subHealthUser = it.SubHealthUser,
                    expandUserName = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == it.ExpandUser).Select(u => u.RealName),
                    mainHealthUserName = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == it.MainHealthUser).Select(u => u.RealName),
                    subHealthUserName = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == it.SubHealthUser).Select(u => u.RealName),
                    tjrName = SqlFunc.Subqueryable<LqKhxxEntity>().Where(u => u.Id == it.Tjr).Select(u => u.Khmc),
                    lastConsumeTime = it.LastConsumeTime,
                    isBeautyMember = it.IsBeautyMember,
                    isMedicalMember = it.IsMedicalMember,
                    isTechMember = it.IsTechMember,
                    isEducationMember = it.IsEducationMember,
                    beautyMemberTime = it.BeautyMemberTime,
                    medicalMemberTime = it.MedicalMemberTime,
                    techMemberTime = it.TechMemberTime,
                    educationMemberTime = it.EducationMemberTime,
                    firstVisitTime = it.FirstVisitTime,
                    lastVisitTime = it.LastVisitTime,
                    visitDays = it.VisitDays,
                    sleepStartTime = it.SleepStartTime,
                    sleepDays = it.SleepDays,
                    totalConsumeAmount = it.TotalConsumeAmount,
                    totalBillingAmount = it.TotalBillingAmount,
                    remainingRightsAmount = it.RemainingRightsAmount,
                    consumeLevel = it.ConsumeLevel,
                    consumeLevelUpdateTime = it.ConsumeLevelUpdateTime,
                    isEffective = it.IsEffective
                })
                .MergeTable()
                .OrderBy(sidx + " " + input.sort)
                .ToPagedListAsync(input.currentPage, input.pageSize);
            return PageResult<LqKhxxListOutput>.SqlSugarPageResult(data);
        }
        #endregion

        #region 新建客户资料
        /// <summary>
        /// 新建客户资料
        /// </summary>
        /// <param name="input">参数</param>
        /// <returns></returns>
        [HttpPost("")]
        public async Task Create([FromBody] LqKhxxCrInput input)
        {
            var userInfo = await _userManager.GetUserInfo();
            //判断手机号是否存在
            var user = _db.Queryable<LqKhxxEntity>().Where(u => u.Sjh == input.sjh).Any();
            if (user)
            {
                throw NCCException.Oh("该会员已存在");
            }
            var entity = input.Adapt<LqKhxxEntity>();
            entity.Id = YitIdHelper.NextId().ToString();
            entity.Dah = "LQ" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
            entity.Zcsj = DateTime.Now;
            // 处理客户消费字段：将数组转换为逗号分隔的字符串
            if (input.khxf != null && input.khxf.Count > 0)
            {
                entity.Khxf = string.Join(",", input.khxf);
            }

            var isOk = await _db.Insertable(entity).IgnoreColumns(ignoreNullColumn: true).ExecuteCommandAsync();
            if (!(isOk > 0))
                throw NCCException.Oh(ErrorCode.COM1000);
        }
        #endregion

        #region 获取客户资料无分页列表
        /// <summary>
        /// 获取客户资料无分页列表
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [NonAction]
        public async Task<dynamic> GetNoPagingList([FromQuery] LqKhxxListQueryInput input)
        {
            var sidx = input.sidx == null ? "id" : input.sidx;
            List<string> queryZcsj = input.zcsj != null ? input.zcsj.Split(',').ToObeject<List<string>>() : null;
            DateTime? startZcsj = queryZcsj != null ? Ext.GetDateTime(queryZcsj.First()) : null;
            DateTime? endZcsj = queryZcsj != null ? Ext.GetDateTime(queryZcsj.Last()) : null;
            List<string> queryYanglsr = input.yanglsr != null ? input.yanglsr.Split(',').ToObeject<List<string>>() : null;
            DateTime? startYanglsr = queryYanglsr != null ? Ext.GetDateTime(queryYanglsr.First()) : null;
            DateTime? endYanglsr = queryYanglsr != null ? Ext.GetDateTime(queryYanglsr.Last()) : null;
            List<string> queryYinlsr = input.yinlsr != null ? input.yinlsr.Split(',').ToObeject<List<string>>() : null;
            DateTime? startYinlsr = queryYinlsr != null ? Ext.GetDateTime(queryYinlsr.First()) : null;
            DateTime? endYinlsr = queryYinlsr != null ? Ext.GetDateTime(queryYinlsr.Last()) : null;
            
            // 处理沉睡天数范围
            List<string> querySleepDaysRange = input.SleepDaysRange != null ? input.SleepDaysRange.Split(',').ToObeject<List<string>>() : null;
            int? minSleepDays = null;
            int? maxSleepDays = null;
            if (querySleepDaysRange != null && querySleepDaysRange.Count >= 2)
            {
                int.TryParse(querySleepDaysRange[0], out int min);
                int.TryParse(querySleepDaysRange[1], out int max);
                minSleepDays = min;
                maxSleepDays = max;
            }
            
            var data = await _db.Queryable<LqKhxxEntity>()
                .WhereIF(!string.IsNullOrEmpty(input.id), p => p.Id.Contains(input.id))
                .WhereIF(!string.IsNullOrEmpty(input.khmc), p => p.Khmc.Contains(input.khmc))
                .WhereIF(!string.IsNullOrEmpty(input.sjh), p => p.Sjh.Contains(input.sjh))
                .WhereIF(!string.IsNullOrEmpty(input.dah), p => p.Dah.Contains(input.dah))
                .WhereIF(!string.IsNullOrEmpty(input.xb), p => p.Xb.Equals(input.xb))
                .WhereIF(!string.IsNullOrEmpty(input.gsmd), p => p.Gsmd.Contains(input.gsmd))
                .WhereIF(!string.IsNullOrEmpty(input.mainHealthUser), p => p.MainHealthUser.Contains(input.mainHealthUser))
                .WhereIF(!string.IsNullOrEmpty(input.subHealthUser), p => p.SubHealthUser.Contains(input.subHealthUser))
                .WhereIF(queryZcsj != null, p => p.Zcsj >= new DateTime(startZcsj.ToDate().Year, startZcsj.ToDate().Month, startZcsj.ToDate().Day, 0, 0, 0))
                .WhereIF(queryZcsj != null, p => p.Zcsj <= new DateTime(endZcsj.ToDate().Year, endZcsj.ToDate().Month, endZcsj.ToDate().Day, 23, 59, 59))
                .WhereIF(!string.IsNullOrEmpty(input.khlx), p => p.Khlx.Equals(input.khlx))
                .WhereIF(!string.IsNullOrEmpty(input.khjd), p => p.Khjd.Equals(input.khjd))
                .WhereIF(!string.IsNullOrEmpty(input.khxf), p => p.Khxf.Equals(input.khxf))
                .WhereIF(!string.IsNullOrEmpty(input.xfpc), p => p.Xfpc.Equals(input.xfpc))
                .WhereIF(!string.IsNullOrEmpty(input.tjr), p => p.Tjr.Contains(input.tjr))
                .WhereIF(!string.IsNullOrEmpty(input.jdqd), p => p.Jdqd.Contains(input.jdqd))
                .WhereIF(!string.IsNullOrEmpty(input.lxdz), p => p.Lxdz.Contains(input.lxdz))
                .WhereIF(!string.IsNullOrEmpty(input.bz), p => p.Bz.Contains(input.bz))
                .WhereIF(input.ConsumeLevel.HasValue, p => p.ConsumeLevel == input.ConsumeLevel.Value)
                .WhereIF(minSleepDays.HasValue, p => p.SleepDays >= minSleepDays.Value)
                .WhereIF(maxSleepDays.HasValue, p => p.SleepDays <= maxSleepDays.Value)
                .WhereIF(queryYanglsr != null, p => p.Yanglsr >= new DateTime(startYanglsr.ToDate().Year, startYanglsr.ToDate().Month, startYanglsr.ToDate().Day, 0, 0, 0))
                .WhereIF(queryYanglsr != null, p => p.Yanglsr <= new DateTime(endYanglsr.ToDate().Year, endYanglsr.ToDate().Month, endYanglsr.ToDate().Day, 23, 59, 59))
                .WhereIF(queryYinlsr != null, p => p.Yinlsr >= new DateTime(startYinlsr.ToDate().Year, startYinlsr.ToDate().Month, startYinlsr.ToDate().Day, 0, 0, 0))
                .WhereIF(queryYinlsr != null, p => p.Yinlsr <= new DateTime(endYinlsr.ToDate().Year, endYinlsr.ToDate().Month, endYinlsr.ToDate().Day, 23, 59, 59))
                .Select(it => new LqKhxxListOutput
                {
                    id = it.Id,
                    khmc = it.Khmc,
                    sjh = it.Sjh,
                    dah = it.Dah,
                    xb = it.Xb,
                    gsmd = it.Gsmd,
                    gsmdName = SqlFunc.Subqueryable<LqMdxxEntity>().Where(u => u.Id == it.Gsmd).Select(u => u.Dm),
                    zcsj = it.Zcsj,
                    khlx = it.Khlx,
                    khjd = it.Khjd,
                    khxf = it.Khxf,
                    xfpc = it.Xfpc,
                    tjr = it.Tjr,
                    jdqd = it.Jdqd,
                    lxdz = it.Lxdz,
                    bz = it.Bz,
                    yanglsr = it.Yanglsr,
                    yinlsr = it.Yinlsr,
                    createTime = it.CreateTime,
                    expandUser = it.ExpandUser,
                    mainHealthUser = it.MainHealthUser,
                    subHealthUser = it.SubHealthUser,
                    expandUserName = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == it.ExpandUser).Select(u => u.RealName),
                    mainHealthUserName = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == it.MainHealthUser).Select(u => u.RealName),
                    subHealthUserName = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == it.SubHealthUser).Select(u => u.RealName),
                    tjrName = SqlFunc.Subqueryable<LqKhxxEntity>().Where(u => u.Id == it.Tjr).Select(u => u.Khmc),
                    lastConsumeTime = it.LastConsumeTime,
                    isBeautyMember = it.IsBeautyMember,
                    isMedicalMember = it.IsMedicalMember,
                    isTechMember = it.IsTechMember,
                    isEducationMember = it.IsEducationMember,
                    beautyMemberTime = it.BeautyMemberTime,
                    medicalMemberTime = it.MedicalMemberTime,
                    techMemberTime = it.TechMemberTime,
                    educationMemberTime = it.EducationMemberTime,
                    firstVisitTime = it.FirstVisitTime,
                    lastVisitTime = it.LastVisitTime,
                    visitDays = it.VisitDays,
                    sleepStartTime = it.SleepStartTime,
                    sleepDays = it.SleepDays,
                    totalConsumeAmount = it.TotalConsumeAmount,
                    totalBillingAmount = it.TotalBillingAmount,
                    remainingRightsAmount = it.RemainingRightsAmount,
                    consumeLevel = it.ConsumeLevel,
                    consumeLevelUpdateTime = it.ConsumeLevelUpdateTime,
                    isEffective = it.IsEffective
                })
                .MergeTable()
                .OrderBy(sidx + " " + input.sort)
                .ToListAsync();
            return data;
        }
        #endregion

        #region 获取线索池客户列表
        /// <summary>
        /// 获取线索池客户列表
        /// </summary>
        /// <param name="input">查询参数</param>
        /// <returns>线索池客户列表</returns>
        [HttpGet("leads-pool")]
        public async Task<PageResult<LqKhxxListOutput>> GetLeadsPoolList([FromQuery] LqKhxxListQueryInput input)
        {
            var sidx = input.sidx == null ? "id" : input.sidx;
            List<string> queryZcsj = input.zcsj != null ? input.zcsj.Split(',').ToObeject<List<string>>() : null;
            DateTime? startZcsj = queryZcsj != null ? Ext.GetDateTime(queryZcsj.First()) : null;
            DateTime? endZcsj = queryZcsj != null ? Ext.GetDateTime(queryZcsj.Last()) : null;
            List<string> queryYanglsr = input.yanglsr != null ? input.yanglsr.Split(',').ToObeject<List<string>>() : null;
            DateTime? startYanglsr = queryYanglsr != null ? Ext.GetDateTime(queryYanglsr.First()) : null;
            DateTime? endYanglsr = queryYanglsr != null ? Ext.GetDateTime(queryYanglsr.Last()) : null;
            List<string> queryYinlsr = input.yinlsr != null ? input.yinlsr.Split(',').ToObeject<List<string>>() : null;
            DateTime? startYinlsr = queryYinlsr != null ? Ext.GetDateTime(queryYinlsr.First()) : null;
            DateTime? endYinlsr = queryYinlsr != null ? Ext.GetDateTime(queryYinlsr.Last()) : null;

            var data = await _db.Queryable<LqKhxxEntity>()
                .Where(p => p.Khlx == "线索") // 固定查询线索池客户
                .WhereIF(!string.IsNullOrEmpty(input.id), p => p.Id.Contains(input.id))
                .WhereIF(!string.IsNullOrEmpty(input.khmc), p => p.Khmc.Contains(input.khmc))
                .WhereIF(!string.IsNullOrEmpty(input.sjh), p => p.Sjh.Contains(input.sjh))
                .WhereIF(!string.IsNullOrEmpty(input.dah), p => p.Dah.Contains(input.dah))
                .WhereIF(!string.IsNullOrEmpty(input.xb), p => p.Xb.Equals(input.xb))
                .WhereIF(!string.IsNullOrEmpty(input.gsmd), p => p.Gsmd.Contains(input.gsmd))
                .WhereIF(queryZcsj != null, p => p.Zcsj >= new DateTime(startZcsj.ToDate().Year, startZcsj.ToDate().Month, startZcsj.ToDate().Day, 0, 0, 0))
                .WhereIF(queryZcsj != null, p => p.Zcsj <= new DateTime(endZcsj.ToDate().Year, endZcsj.ToDate().Month, endZcsj.ToDate().Day, 23, 59, 59))
                .WhereIF(!string.IsNullOrEmpty(input.khlx), p => p.Khlx.Equals(input.khlx))
                .WhereIF(!string.IsNullOrEmpty(input.khjd), p => p.Khjd.Equals(input.khjd))
                .WhereIF(!string.IsNullOrEmpty(input.khxf), p => p.Khxf.Contains(input.khxf))
                .WhereIF(!string.IsNullOrEmpty(input.xfpc), p => p.Xfpc.Equals(input.xfpc))
                .WhereIF(!string.IsNullOrEmpty(input.tjr), p => p.Tjr.Contains(input.tjr))
                .WhereIF(!string.IsNullOrEmpty(input.jdqd), p => p.Jdqd.Contains(input.jdqd))
                .WhereIF(!string.IsNullOrEmpty(input.lxdz), p => p.Lxdz.Contains(input.lxdz))
                .WhereIF(!string.IsNullOrEmpty(input.bz), p => p.Bz.Contains(input.bz))
                .WhereIF(queryYanglsr != null, p => p.Yanglsr >= new DateTime(startYanglsr.ToDate().Year, startYanglsr.ToDate().Month, startYanglsr.ToDate().Day, 0, 0, 0))
                .WhereIF(queryYanglsr != null, p => p.Yanglsr <= new DateTime(endYanglsr.ToDate().Year, endYanglsr.ToDate().Month, endYanglsr.ToDate().Day, 23, 59, 59))
                .WhereIF(queryYinlsr != null, p => p.Yinlsr >= new DateTime(startYinlsr.ToDate().Year, startYinlsr.ToDate().Month, startYinlsr.ToDate().Day, 0, 0, 0))
                .WhereIF(queryYinlsr != null, p => p.Yinlsr <= new DateTime(endYinlsr.ToDate().Year, endYinlsr.ToDate().Month, endYinlsr.ToDate().Day, 23, 59, 59))
                .Select(it => new LqKhxxListOutput
                {
                    id = it.Id,
                    khmc = it.Khmc,
                    sjh = it.Sjh,
                    dah = it.Dah,
                    xb = it.Xb,
                    gsmd = it.Gsmd,
                    gsmdName = SqlFunc.Subqueryable<LqMdxxEntity>().Where(u => u.Id == it.Gsmd).Select(u => u.Dm),
                    zcsj = it.Zcsj,
                    khlx = it.Khlx,
                    khjd = it.Khjd,
                    khxf = it.Khxf,
                    xfpc = it.Xfpc,
                    tjr = it.Tjr,
                    jdqd = it.Jdqd,
                    lxdz = it.Lxdz,
                    bz = it.Bz,
                    yanglsr = it.Yanglsr,
                    yinlsr = it.Yinlsr,
                    createTime = it.CreateTime,
                    expandUser = it.ExpandUser,
                    mainHealthUser = it.MainHealthUser,
                    subHealthUser = it.SubHealthUser,
                    expandUserName = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == it.ExpandUser).Select(u => u.RealName),
                    mainHealthUserName = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == it.MainHealthUser).Select(u => u.RealName),
                    subHealthUserName = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == it.SubHealthUser).Select(u => u.RealName),
                    tjrName = SqlFunc.Subqueryable<LqKhxxEntity>().Where(u => u.Id == it.Tjr).Select(u => u.Khmc),
                    lastConsumeTime = it.LastConsumeTime,
                    isBeautyMember = it.IsBeautyMember,
                    isMedicalMember = it.IsMedicalMember,
                    isTechMember = it.IsTechMember,
                    isEducationMember = it.IsEducationMember,
                    beautyMemberTime = it.BeautyMemberTime,
                    medicalMemberTime = it.MedicalMemberTime,
                    techMemberTime = it.TechMemberTime,
                    educationMemberTime = it.EducationMemberTime,
                    firstVisitTime = it.FirstVisitTime,
                    lastVisitTime = it.LastVisitTime,
                    visitDays = it.VisitDays,
                    sleepStartTime = it.SleepStartTime,
                    sleepDays = it.SleepDays,
                    totalConsumeAmount = it.TotalConsumeAmount,
                    totalBillingAmount = it.TotalBillingAmount,
                    remainingRightsAmount = it.RemainingRightsAmount,
                    consumeLevel = it.ConsumeLevel,
                    consumeLevelUpdateTime = it.ConsumeLevelUpdateTime,
                    isEffective = it.IsEffective
                })
                .MergeTable()
                .OrderBy(sidx + " " + input.sort)
                .ToPagedListAsync(input.currentPage, input.pageSize);
            return PageResult<LqKhxxListOutput>.SqlSugarPageResult(data);
        }
        #endregion

        #region 获取客户订单类型（首单/升单）
        /// <summary>
        /// 获取客户订单类型（首单/升单）
        /// </summary>
        /// <param name="customerId">客户ID</param>
        /// <returns>客户订单类型信息</returns>
        [HttpGet("order-type/{customerId}")]
        public async Task<LqKhxxOrderTypeOutput> GetCustomerOrderType(string customerId)
        {
            if (string.IsNullOrEmpty(customerId))
            {
                throw NCCException.Oh("客户ID不能为空");
            }
            // 查询客户是否存在
            var customerExists = await _db.Queryable<LqKhxxEntity>().Where(c => c.Id == customerId).AnyAsync();

            if (!customerExists)
            {
                throw NCCException.Oh("客户不存在");
            }
            // 查询开单记录数量
            var orderCount = await _db.Queryable<LqKdKdjlbEntity>().Where(o => o.Kdhy == customerId && o.IsEffective == StatusEnum.有效.GetHashCode() && o.Sfyj > 0).CountAsync();
            var hasOrderRecord = orderCount > 0;
            var orderType = hasOrderRecord ? "repeat_order" : "first_order";
            return new LqKhxxOrderTypeOutput
            {
                CustomerId = customerId,
                OrderType = orderType,
                HasOrderRecord = hasOrderRecord,
                OrderCount = orderCount,
            };
        }
        #endregion

        #region 判断客户是否开过指定类型的开单
        /// <summary>
        /// 判断客户是否开过生美的开单
        /// </summary>
        /// <param name="memberId">会员ID</param>
        /// <returns>1表示有，0表示没有</returns>
        [HttpGet("has-life-beauty-billing/{memberId}")]
        public async Task<int> HasLifeBeautyBilling(string memberId)
        {
            if (string.IsNullOrEmpty(memberId))
            {
                throw NCCException.Oh("会员ID不能为空");
            }

            // 查询该会员是否有开过生美品项的开单
            var hasBilling = await _db.Queryable<LqKdKdjlbEntity>()
                .InnerJoin<LqKdPxmxEntity>((kdjlb, pxmx) => kdjlb.Id == pxmx.Glkdbh)
                .InnerJoin<LqXmzlEntity>((kdjlb, pxmx, xmzl) => pxmx.Px == xmzl.Id)
                .Where((kdjlb, pxmx, xmzl) => kdjlb.Kdhy == memberId
                    && kdjlb.IsEffective == StatusEnum.有效.GetHashCode()
                    && pxmx.IsEffective == StatusEnum.有效.GetHashCode()
                    && xmzl.IsEffective == StatusEnum.有效.GetHashCode()
                    && xmzl.Qt2 == "生美")
                .AnyAsync();

            return hasBilling ? 1 : 0;
        }

        /// <summary>
        /// 判断客户是否开过科美的开单
        /// </summary>
        /// <param name="memberId">会员ID</param>
        /// <returns>1表示有，0表示没有</returns>
        [HttpGet("has-tech-beauty-billing/{memberId}")]
        public async Task<int> HasTechBeautyBilling(string memberId)
        {
            if (string.IsNullOrEmpty(memberId))
            {
                throw NCCException.Oh("会员ID不能为空");
            }

            // 查询该会员是否有开过科美品项的开单
            var hasBilling = await _db.Queryable<LqKdKdjlbEntity>()
                .InnerJoin<LqKdPxmxEntity>((kdjlb, pxmx) => kdjlb.Id == pxmx.Glkdbh)
                .InnerJoin<LqXmzlEntity>((kdjlb, pxmx, xmzl) => pxmx.Px == xmzl.Id)
                .Where((kdjlb, pxmx, xmzl) => kdjlb.Kdhy == memberId
                    && kdjlb.IsEffective == StatusEnum.有效.GetHashCode()
                    && pxmx.IsEffective == StatusEnum.有效.GetHashCode()
                    && xmzl.IsEffective == StatusEnum.有效.GetHashCode()
                    && xmzl.Qt2 == "科美")
                .AnyAsync();

            return hasBilling ? 1 : 0;
        }

        /// <summary>
        /// 判断客户是否开过医美的开单
        /// </summary>
        /// <param name="memberId">会员ID</param>
        /// <returns>1表示有，0表示没有</returns>
        [HttpGet("has-medical-beauty-billing/{memberId}")]
        public async Task<int> HasMedicalBeautyBilling(string memberId)
        {
            if (string.IsNullOrEmpty(memberId))
            {
                throw NCCException.Oh("会员ID不能为空");
            }

            // 查询该会员是否有开过医美品项的开单
            var hasBilling = await _db.Queryable<LqKdKdjlbEntity>()
                .InnerJoin<LqKdPxmxEntity>((kdjlb, pxmx) => kdjlb.Id == pxmx.Glkdbh)
                .InnerJoin<LqXmzlEntity>((kdjlb, pxmx, xmzl) => pxmx.Px == xmzl.Id)
                .Where((kdjlb, pxmx, xmzl) => kdjlb.Kdhy == memberId
                    && kdjlb.IsEffective == StatusEnum.有效.GetHashCode()
                    && pxmx.IsEffective == StatusEnum.有效.GetHashCode()
                    && xmzl.IsEffective == StatusEnum.有效.GetHashCode()
                    && xmzl.Qt2 == "医美")
                .AnyAsync();

            return hasBilling ? 1 : 0;
        }
        #endregion

        #region 导出客户资料
        /// <summary>
        /// 导出客户资料
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("Actions/Export")]
        public async Task<dynamic> Export([FromQuery] LqKhxxListQueryInput input)
        {
            var userInfo = await _userManager.GetUserInfo();
            var exportData = new List<LqKhxxListOutput>();
            if (input.dataType == 0)
            {
                var data = Clay.Object(await this.GetList(input));
                exportData = data.Solidify<PageResult<LqKhxxListOutput>>().list;
            }
            else
            {
                exportData = await this.GetNoPagingList(input);
            }
            List<ParamsModel> paramList =
                "[{\"value\":\"客户编码\",\"field\":\"id\"},{\"value\":\"客户名称\",\"field\":\"khmc\"},{\"value\":\"手机号\",\"field\":\"sjh\"},{\"value\":\"档案号\",\"field\":\"dah\"},{\"value\":\"性别\",\"field\":\"xb\"},{\"value\":\"公众号状态\",\"field\":\"gzhzt\"},{\"value\":\"微信昵称\",\"field\":\"wxnc\"},{\"value\":\"小程序状态\",\"field\":\"wxxcxzt\"},{\"value\":\"最近登录时间\",\"field\":\"zjdlsj\"},{\"value\":\"客户目前归属\",\"field\":\"khmqgs\"},{\"value\":\"归属门店\",\"field\":\"gsmd\"},{\"value\":\"注册时间\",\"field\":\"zcsj\"},{\"value\":\"客户类型\",\"field\":\"khlx\"},{\"value\":\"客户阶段\",\"field\":\"khjd\"},{\"value\":\"客户消费\",\"field\":\"khxf\"},{\"value\":\"消费频次\",\"field\":\"xfpc\"},{\"value\":\"推荐人\",\"field\":\"tjr\"},{\"value\":\"负责顾问\",\"field\":\"fzgw\"},{\"value\":\"美容师\",\"field\":\"mrs\"},{\"value\":\"进店渠道\",\"field\":\"jdqd\"},{\"value\":\"联系地址\",\"field\":\"lxdz\"},{\"value\":\"备注\",\"field\":\"bz\"},{\"value\":\"阳历生日\",\"field\":\"yanglsr\"},{\"value\":\"阴历生日\",\"field\":\"yinlsr\"},{\"value\":\"年龄\",\"field\":\"ml\"},{\"value\":\"消费等级\",\"field\":\"consumeLevelName\"},{\"value\":\"开单总金额\",\"field\":\"totalBillingAmount\"},{\"value\":\"剩余权益总金额\",\"field\":\"remainingRightsAmount\"},{\"value\":\"首次到店时间\",\"field\":\"firstVisitTime\"},{\"value\":\"最后到店时间\",\"field\":\"lastVisitTime\"},{\"value\":\"到店天数\",\"field\":\"visitDays\"},{\"value\":\"沉睡天数\",\"field\":\"sleepDays\"},]".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<LqKhxxListOutput>.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>
        /// 导出客户资料（优化版）
        /// 使用LEFT JOIN优化查询效率，固定导出指定字段
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("Actions/ExportOptimized")]
        public async Task<dynamic> ExportOptimized([FromQuery] LqKhxxListQueryInput input)
        {
            var userInfo = await _userManager.GetUserInfo();

            // 构建查询条件
            var sidx = input.sidx == null ? "F_Id" : input.sidx;
            // 将前端字段名映射到数据库字段名
            var fieldMapping = new Dictionary<string, string>
            {
                { "id", "F_Id" },
                { "khmc", "khmc" },
                { "sjh", "sjh" },
                { "dah", "dah" },
                { "xb", "xb" },
                { "zcsj", "zcsj" },
                { "khlx", "khlx" },
                { "khjd", "khjd" },
                { "khxf", "khxf" },
                { "xfpc", "xfpc" },
                { "yanglsr", "yanglsr" }
            };
            if (fieldMapping.ContainsKey(sidx))
            {
                sidx = fieldMapping[sidx];
            }
            // 确保 sort 参数不为空
            var sortOrder = string.IsNullOrEmpty(input.sort) ? "desc" : input.sort;
            List<string> queryZcsj = input.zcsj != null ? input.zcsj.Split(',').ToObeject<List<string>>() : null;
            DateTime? startZcsj = queryZcsj != null ? Ext.GetDateTime(queryZcsj.First()) : null;
            DateTime? endZcsj = queryZcsj != null ? Ext.GetDateTime(queryZcsj.Last()) : null;
            List<string> queryYanglsr = input.yanglsr != null ? input.yanglsr.Split(',').ToObeject<List<string>>() : null;
            DateTime? startYanglsr = queryYanglsr != null ? Ext.GetDateTime(queryYanglsr.First()) : null;
            DateTime? endYanglsr = queryYanglsr != null ? Ext.GetDateTime(queryYanglsr.Last()) : null;
            
            // 处理沉睡天数范围
            List<string> querySleepDaysRange = input.SleepDaysRange != null ? input.SleepDaysRange.Split(',').ToObeject<List<string>>() : null;
            int? minSleepDays = null;
            int? maxSleepDays = null;
            if (querySleepDaysRange != null && querySleepDaysRange.Count >= 2)
            {
                int.TryParse(querySleepDaysRange[0], out int min);
                int.TryParse(querySleepDaysRange[1], out int max);
                minSleepDays = min;
                maxSleepDays = max;
            }

            // 先查询客户基础数据（使用子查询方式，类似GetNoPagingList，但优化为批量查询关联数据）
            var customerList = await _db.Queryable<LqKhxxEntity>()
                .WhereIF(!string.IsNullOrEmpty(input.id), p => p.Id.Contains(input.id))
                .WhereIF(!string.IsNullOrEmpty(input.khmc), p => p.Khmc.Contains(input.khmc))
                .WhereIF(!string.IsNullOrEmpty(input.sjh), p => p.Sjh.Contains(input.sjh))
                .WhereIF(!string.IsNullOrEmpty(input.dah), p => p.Dah.Contains(input.dah))
                .WhereIF(!string.IsNullOrEmpty(input.xb), p => p.Xb.Equals(input.xb))
                .WhereIF(!string.IsNullOrEmpty(input.gsmd), p => p.Gsmd.Contains(input.gsmd))
                .WhereIF(!string.IsNullOrEmpty(input.mainHealthUser), p => p.MainHealthUser.Contains(input.mainHealthUser))
                .WhereIF(!string.IsNullOrEmpty(input.subHealthUser), p => p.SubHealthUser.Contains(input.subHealthUser))
                .WhereIF(queryZcsj != null, p => p.Zcsj >= new DateTime(startZcsj.ToDate().Year, startZcsj.ToDate().Month, startZcsj.ToDate().Day, 0, 0, 0))
                .WhereIF(queryZcsj != null, p => p.Zcsj <= new DateTime(endZcsj.ToDate().Year, endZcsj.ToDate().Month, endZcsj.ToDate().Day, 23, 59, 59))
                .WhereIF(!string.IsNullOrEmpty(input.khlx), p => p.Khlx.Equals(input.khlx))
                .WhereIF(!string.IsNullOrEmpty(input.khjd), p => p.Khjd.Equals(input.khjd))
                .WhereIF(!string.IsNullOrEmpty(input.khxf), p => p.Khxf.Contains(input.khxf))
                .WhereIF(!string.IsNullOrEmpty(input.xfpc), p => p.Xfpc.Equals(input.xfpc))
                .WhereIF(!string.IsNullOrEmpty(input.tjr), p => p.Tjr.Contains(input.tjr))
                .WhereIF(!string.IsNullOrEmpty(input.jdqd), p => p.Jdqd.Contains(input.jdqd))
                .WhereIF(!string.IsNullOrEmpty(input.lxdz), p => p.Lxdz.Contains(input.lxdz))
                .WhereIF(input.ConsumeLevel.HasValue, p => p.ConsumeLevel == input.ConsumeLevel.Value)
                .WhereIF(minSleepDays.HasValue, p => p.SleepDays >= minSleepDays.Value)
                .WhereIF(maxSleepDays.HasValue, p => p.SleepDays <= maxSleepDays.Value)
                .WhereIF(!string.IsNullOrEmpty(input.bz), p => p.Bz.Contains(input.bz))
                .WhereIF(queryYanglsr != null, p => p.Yanglsr >= new DateTime(startYanglsr.ToDate().Year, startYanglsr.ToDate().Month, startYanglsr.ToDate().Day, 0, 0, 0))
                .WhereIF(queryYanglsr != null, p => p.Yanglsr <= new DateTime(endYanglsr.ToDate().Year, endYanglsr.ToDate().Month, endYanglsr.ToDate().Day, 23, 59, 59))
                .Where(p => p.IsEffective == StatusEnum.有效.GetHashCode())
                .OrderBy(sidx + " " + sortOrder)
                .ToListAsync();

            if (customerList == null || !customerList.Any())
            {
                // 如果没有数据，返回空结果
                var emptyExcelConfig = new ExcelConfig();
                emptyExcelConfig.FileName = "客户资料导出_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                emptyExcelConfig.HeadFont = "微软雅黑";
                emptyExcelConfig.HeadPoint = 10;
                emptyExcelConfig.IsAllSizeColumn = true;
                emptyExcelConfig.ColumnModel = new List<ExcelColumnModel>();

                // 查找项目根目录并创建ExportFiles文件夹
                var emptyBaseDir = AppContext.BaseDirectory;
                var emptyProjectRoot = emptyBaseDir;
                var emptyDir = new DirectoryInfo(emptyBaseDir);
                while (emptyDir != null && emptyDir.Parent != null)
                {
                    try
                    {
                        if (emptyDir.GetDirectories(".git").Any() || emptyDir.GetFiles("*.sln").Any())
                        {
                            emptyProjectRoot = emptyDir.FullName;
                            break;
                        }
                    }
                    catch { }
                    emptyDir = emptyDir.Parent;
                }
                var emptyExportFilesPath = Path.Combine(emptyProjectRoot, "ExportFiles");
                if (!Directory.Exists(emptyExportFilesPath))
                {
                    Directory.CreateDirectory(emptyExportFilesPath);
                }
                var emptyAddPath = Path.Combine(emptyExportFilesPath, emptyExcelConfig.FileName);
                ExcelExportHelper<LqKhxxExportOutput>.Export(new List<LqKhxxExportOutput>(), emptyExcelConfig, emptyAddPath);
                var emptyFileName = _userManager.UserId + "|" + emptyAddPath + "|xls";
                return new { name = emptyExcelConfig.FileName, url = "/api/File/Download?encryption=" + DESCEncryption.Encrypt(emptyFileName, "NCC") };
            }

            // 批量查询门店信息（优化：一次性查询所有需要的门店）
            var storeIds = customerList
                .Where(c => !string.IsNullOrEmpty(c.Gsmd))
                .Select(c => c.Gsmd)
                .Distinct()
                .Where(id => !string.IsNullOrEmpty(id))
                .ToList();
            var storeDict = new Dictionary<string, LqMdxxEntity>();
            if (storeIds != null && storeIds.Any())
            {
                var stores = await _db.Queryable<LqMdxxEntity>()
                    .Where(s => storeIds.Contains(s.Id) && !string.IsNullOrEmpty(s.Id))
                    .ToListAsync();
                if (stores != null && stores.Any())
                {
                    foreach (var store in stores)
                    {
                        if (!string.IsNullOrEmpty(store.Id) && !storeDict.ContainsKey(store.Id))
                        {
                            storeDict[store.Id] = store;
                        }
                    }
                }
            }

            // 批量查询推荐人信息
            var tjrIds = customerList
                .Where(c => !string.IsNullOrEmpty(c.Tjr))
                .Select(c => c.Tjr)
                .Distinct()
                .Where(id => !string.IsNullOrEmpty(id))
                .ToList();
            var tjrDict = new Dictionary<string, string>();
            if (tjrIds != null && tjrIds.Any())
            {
                var tjrs = await _db.Queryable<LqKhxxEntity>()
                    .Where(t => tjrIds.Contains(t.Id) && !string.IsNullOrEmpty(t.Id))
                    .Select(t => new { t.Id, t.Khmc })
                    .ToListAsync();
                if (tjrs != null && tjrs.Any())
                {
                    foreach (var tjr in tjrs)
                    {
                        if (!string.IsNullOrEmpty(tjr.Id) && !tjrDict.ContainsKey(tjr.Id))
                        {
                            tjrDict[tjr.Id] = tjr.Khmc ?? "";
                        }
                    }
                }
            }

            // 批量查询主健康师信息
            var mainHealthUserIds = customerList
                .Where(c => !string.IsNullOrEmpty(c.MainHealthUser))
                .Select(c => c.MainHealthUser)
                .Distinct()
                .Where(id => !string.IsNullOrEmpty(id))
                .ToList();
            var mainHealthUserDict = new Dictionary<string, string>();
            if (mainHealthUserIds != null && mainHealthUserIds.Any())
            {
                var mainHealthUsers = await _db.Queryable<UserEntity>()
                    .Where(u => mainHealthUserIds.Contains(u.Id) && !string.IsNullOrEmpty(u.Id))
                    .Select(u => new { u.Id, u.RealName })
                    .ToListAsync();
                if (mainHealthUsers != null && mainHealthUsers.Any())
                {
                    foreach (var user in mainHealthUsers)
                    {
                        if (!string.IsNullOrEmpty(user.Id) && !mainHealthUserDict.ContainsKey(user.Id))
                        {
                            mainHealthUserDict[user.Id] = user.RealName ?? "";
                        }
                    }
                }
            }

            // 批量查询副健康师信息
            var subHealthUserIds = customerList
                .Where(c => !string.IsNullOrEmpty(c.SubHealthUser))
                .Select(c => c.SubHealthUser)
                .Distinct()
                .Where(id => !string.IsNullOrEmpty(id))
                .ToList();
            var subHealthUserDict = new Dictionary<string, string>();
            if (subHealthUserIds != null && subHealthUserIds.Any())
            {
                var subHealthUsers = await _db.Queryable<UserEntity>()
                    .Where(u => subHealthUserIds.Contains(u.Id) && !string.IsNullOrEmpty(u.Id))
                    .Select(u => new { u.Id, u.RealName })
                    .ToListAsync();
                if (subHealthUsers != null && subHealthUsers.Any())
                {
                    foreach (var user in subHealthUsers)
                    {
                        if (!string.IsNullOrEmpty(user.Id) && !subHealthUserDict.ContainsKey(user.Id))
                        {
                            subHealthUserDict[user.Id] = user.RealName ?? "";
                        }
                    }
                }
            }

            // 批量查询拓客人员信息
            var expandUserIds = customerList
                .Where(c => !string.IsNullOrEmpty(c.ExpandUser))
                .Select(c => c.ExpandUser)
                .Distinct()
                .Where(id => !string.IsNullOrEmpty(id))
                .ToList();
            var expandUserDict = new Dictionary<string, string>();
            if (expandUserIds != null && expandUserIds.Any())
            {
                var expandUsers = await _db.Queryable<UserEntity>()
                    .Where(u => expandUserIds.Contains(u.Id) && !string.IsNullOrEmpty(u.Id))
                    .Select(u => new { u.Id, u.RealName })
                    .ToListAsync();
                if (expandUsers != null && expandUsers.Any())
                {
                    foreach (var user in expandUsers)
                    {
                        if (!string.IsNullOrEmpty(user.Id) && !expandUserDict.ContainsKey(user.Id))
                        {
                            expandUserDict[user.Id] = user.RealName ?? "";
                        }
                    }
                }
            }

            // 转换为导出DTO
            var exportData = customerList.Select(kh => new LqKhxxExportOutput
            {
                id = kh.Id,
                khmc = kh.Khmc,
                sjh = kh.Sjh,
                dah = kh.Dah,
                xb = kh.Xb,
                gsmdName = (!string.IsNullOrEmpty(kh.Gsmd) && storeDict.ContainsKey(kh.Gsmd)) ? (storeDict[kh.Gsmd].Dm ?? "") : "",
                zcsj = kh.Zcsj,
                khlxName = GetMemberTypeName(kh.Khlx),
                mainHealthUserName = (!string.IsNullOrEmpty(kh.MainHealthUser) && mainHealthUserDict.ContainsKey(kh.MainHealthUser)) ? mainHealthUserDict[kh.MainHealthUser] : "",
                subHealthUserName = (!string.IsNullOrEmpty(kh.SubHealthUser) && subHealthUserDict.ContainsKey(kh.SubHealthUser)) ? subHealthUserDict[kh.SubHealthUser] : "",
                expandUserName = (!string.IsNullOrEmpty(kh.ExpandUser) && expandUserDict.ContainsKey(kh.ExpandUser)) ? expandUserDict[kh.ExpandUser] : "",
                consumeLevelName = GetConsumeLevelNameFromLevel(kh.ConsumeLevel),
                tjrName = (!string.IsNullOrEmpty(kh.Tjr) && tjrDict.ContainsKey(kh.Tjr)) ? tjrDict[kh.Tjr] : "",
                jdqd = kh.Jdqd,
                lxdz = kh.Lxdz,
                bz = kh.Bz,
                yanglsr = kh.Yanglsr,
                firstVisitTime = kh.FirstVisitTime,
                lastVisitTime = kh.LastVisitTime,
                visitDays = kh.VisitDays,
                sleepDays = kh.SleepDays,
                totalBillingAmount = kh.TotalBillingAmount,
                remainingRightsAmount = kh.RemainingRightsAmount
            }).ToList();

            // 获取客户类型名称的辅助方法
            string GetMemberTypeName(string khlx)
            {
                if (string.IsNullOrEmpty(khlx)) return "无";
                if (int.TryParse(khlx, out var type))
                {
                    return NCC.Code.EnumHelper.GetEnumDesc<MemberTypeEnum>(type);
                }
                return "无";
            }

            // 根据消费等级编号获取消费等级名称
            string GetConsumeLevelNameFromLevel(int level)
            {
                return MemberInfoUpdateConfig.ConsumeLevelNames.ContainsKey(level) 
                    ? MemberInfoUpdateConfig.ConsumeLevelNames[level] 
                    : "D";
            }

            // 定义导出字段映射
            List<ParamsModel> paramList = new List<ParamsModel>
            {
                new ParamsModel { value = "客户编码", field = "id" },
                new ParamsModel { value = "客户名称", field = "khmc" },
                new ParamsModel { value = "手机号", field = "sjh" },
                new ParamsModel { value = "档案号", field = "dah" },
                new ParamsModel { value = "性别", field = "xb" },
                new ParamsModel { value = "归属门店名称", field = "gsmdName" },
                new ParamsModel { value = "注册时间", field = "zcsj" },
                new ParamsModel { value = "客户类型", field = "khlxName" },
                new ParamsModel { value = "主健康师", field = "mainHealthUserName" },
                new ParamsModel { value = "副健康师", field = "subHealthUserName" },
                new ParamsModel { value = "拓客人员", field = "expandUserName" },
                new ParamsModel { value = "消费等级", field = "consumeLevelName" },
                new ParamsModel { value = "推荐人", field = "tjrName" },
                new ParamsModel { value = "进店渠道", field = "jdqd" },
                new ParamsModel { value = "联系地址", field = "lxdz" },
                new ParamsModel { value = "备注", field = "bz" },
                new ParamsModel { value = "阳历生日", field = "yanglsr" },
                new ParamsModel { value = "首次到店时间", field = "firstVisitTime" },
                new ParamsModel { value = "最后到店时间", field = "lastVisitTime" },
                new ParamsModel { value = "到店天数", field = "visitDays" },
                new ParamsModel { value = "沉睡天数", field = "sleepDays" },
                new ParamsModel { value = "开单总金额", field = "totalBillingAmount" },
                new ParamsModel { value = "剩余权益总金额", field = "remainingRightsAmount" }
            };

            // 配置Excel导出
            ExcelConfig excelconfig = new ExcelConfig();
            excelconfig.FileName = "客户资料导出_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            excelconfig.HeadFont = "微软雅黑";
            excelconfig.HeadPoint = 10;
            excelconfig.IsAllSizeColumn = true;
            excelconfig.ColumnModel = new List<ExcelColumnModel>();

            // 添加所有字段到导出配置
            foreach (var param in paramList)
            {
                excelconfig.ColumnModel.Add(new ExcelColumnModel() { Column = param.field, ExcelColumn = param.value });
            }

            // 查找项目根目录
            var baseDir = AppContext.BaseDirectory;
            var projectRoot = baseDir;
            var dir = new DirectoryInfo(baseDir);

            // 优先查找包含 .git 目录的目录（真正的项目根目录）
            while (dir != null && dir.Parent != null)
            {
                try
                {
                    if (dir.GetDirectories(".git").Any())
                    {
                        projectRoot = dir.FullName;
                        break;
                    }
                }
                catch
                {
                    // 忽略访问错误，继续向上查找
                }
                dir = dir.Parent;
            }

            // 如果没找到 .git 目录，再查找包含 .sln 文件的目录
            if (projectRoot == baseDir)
            {
                dir = new DirectoryInfo(baseDir);
                while (dir != null && dir.Parent != null)
                {
                    try
                    {
                        if (dir.GetFiles("*.sln").Any())
                        {
                            projectRoot = dir.FullName;
                            break;
                        }
                    }
                    catch
                    {
                        // 忽略访问错误，继续向上查找
                    }
                    dir = dir.Parent;
                }
            }

            // 在项目根目录下创建 ExportFiles 文件夹
            var exportFilesPath = Path.Combine(projectRoot, "ExportFiles");
            if (!Directory.Exists(exportFilesPath))
            {
                Directory.CreateDirectory(exportFilesPath);
            }

            var addPath = Path.Combine(exportFilesPath, excelconfig.FileName);

            // 导出Excel文件
            ExcelExportHelper<LqKhxxExportOutput>.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<LqKhxxEntity>().In(it => it.Id, ids).ToListAsync();
            if (entitys.Count > 0)
            {
                try
                {
                    //开启事务
                    _db.BeginTran();
                    //批量删除客户资料
                    await _db.Deleteable<LqKhxxEntity>().In(d => d.Id, ids).ExecuteCommandAsync();
                    //关闭事务
                    _db.CommitTran();
                }
                catch (Exception)
                {
                    //回滚事务
                    _db.RollbackTran();
                    throw NCCException.Oh(ErrorCode.COM1002);
                }
            }
        }
        #endregion

        #region 更新客户资料
        /// <summary>
        /// 更新客户资料
        /// </summary>
        /// <param name="id">主键</param>
        /// <param name="input">参数</param>
        /// <returns></returns>
        [HttpPut("{id}")]
        public async Task Update(string id, [FromBody] LqKhxxUpInput input)
        {
            var entity = input.Adapt<LqKhxxEntity>();

            // 处理客户消费字段：将数组转换为逗号分隔的字符串
            if (input.khxf != null && input.khxf.Count > 0)
            {
                entity.Khxf = string.Join(",", input.khxf);
            }

            var isOk = await _db.Updateable(entity).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommandAsync();
            if (!(isOk > 0))
                throw NCCException.Oh(ErrorCode.COM1001);
        }
        #endregion

        #region 删除客户资料
        /// <summary>
        /// 删除客户资料
        /// </summary>
        /// <returns></returns>
        [HttpDelete("{id}")]
        public async Task Delete(string id)
        {
            var entity = await _db.Queryable<LqKhxxEntity>().FirstAsync(p => p.Id == id);
            _ = entity ?? throw NCCException.Oh(ErrorCode.COM1005);
            var isOk = await _db.Deleteable<LqKhxxEntity>().Where(d => d.Id == id).ExecuteCommandAsync();
            if (!(isOk > 0))
                throw NCCException.Oh(ErrorCode.COM1002);
        }
        #endregion

        #region 查询会员剩余品项
        /// <summary>
        /// 查询会员剩余品项信息
        /// </summary>
        /// <remarks>
        /// 根据会员ID查询该会员的剩余品项信息，包括已购买但未消费完的品项详情
        /// 支持按来源类型(F_SourceType)区分相同品项的不同来源，确保数据准确分组
        /// 计算逻辑：剩余数量 = 购买数量 - 消费数量 - 退卡数量
        ///
        /// 示例请求：
        /// ```json
        /// GET /api/Extend/LqKhxx/GetMemberRemainingItems?memberId=GK2025022100004
        /// ```
        ///
        /// 参数说明：
        /// - memberId: 会员ID，必填参数
        ///
        /// 返回数据结构：
        /// - MemberId: 会员ID
        /// - MemberName: 会员姓名
        /// - RemainingItems: 剩余品项列表
        ///   - ItemId: 品项ID
        ///   - ItemName: 品项名称
        ///   - ItemPrice: 品项单价
        ///   - SourceType: 来源类型（用于区分相同品项的不同来源）
        ///   - TotalPurchased: 总购买数量
        ///   - ConsumedCount: 已消费数量
        ///   - RefundedCount: 已退卡数量
        ///   - RemainingCount: 剩余数量
        ///
        /// 特殊说明：
        /// - 相同品项但不同SourceType的记录会分开显示
        /// - 耗卡和退卡统计会按品项ID和SourceType精确匹配
        /// - 确保数据按SourceType正确分组且不重叠
        /// - 退卡记录会减少剩余品项数量
        /// </remarks>
        /// <param name="memberId">会员ID</param>
        /// <returns>会员剩余品项信息</returns>
        /// <response code="200">成功返回会员剩余品项信息</response>
        /// <response code="400">参数错误，会员ID不能为空</response>
        /// <response code="500">服务器错误或会员不存在</response>
        [HttpGet("GetMemberRemainingItems")]
        public async Task<MemberRemainingItemsOutput> GetMemberRemainingItems(string memberId)
        {
            if (string.IsNullOrEmpty(memberId))
            {
                throw NCCException.Oh(ErrorCode.COM1000, "会员ID不能为空");
            }
            try
            {
                // 1. 查询会员基本信息
                var memberInfo = await _db.Queryable<LqKhxxEntity>().Where(x => x.Id == memberId).FirstAsync();
                if (memberInfo == null)
                {
                    _logger.LogWarning("会员不存在，会员ID：{MemberId}", memberId);
                    throw NCCException.Oh("会员不存在");
                }
                // 2. 分步查询，避免子查询问题
                // 先查询基础品项数据
                var baseItems = await _db.Queryable<LqKdPxmxEntity>()
                    .Where(x => x.MemberId == memberId)
                    .Where(x => x.IsEffective == StatusEnum.有效.GetHashCode())
                    .Select(x => new
                    {
                        x.Id,
                        x.Px,
                        x.Pxmc,
                        x.Pxjg,
                        x.SourceType,
                        x.ProjectNumber,
                        x.Remark
                    })
                    .ToListAsync();

                // 查询消费数据
                var consumedData = await _db.Queryable<LqXhPxmxEntity>()
                    .Where(x => baseItems.Select(b => b.Id).Contains(x.BillingItemId))
                    .Where(x => x.IsEffective == StatusEnum.有效.GetHashCode())
                    .GroupBy(x => x.BillingItemId)
                    .Select(x => new
                    {
                        BillingItemId = x.BillingItemId,
                        TotalConsumed = SqlFunc.AggregateSum(x.OriginalProjectNumber)
                    })
                    .ToListAsync();

                // 查询退卡数据
                var refundedData = await _db.Queryable<LqHytkMxEntity>()
                    .Where(x => baseItems.Select(b => b.Id).Contains(x.BillingItemId))
                    .Where(x => x.IsEffective == StatusEnum.有效.GetHashCode())
                    .GroupBy(x => x.BillingItemId)
                    .Select(x => new
                    {
                        BillingItemId = x.BillingItemId,
                        TotalRefunded = SqlFunc.AggregateSum(x.ProjectNumber)
                    })
                    .ToListAsync();

                //查询储扣数据
                var deductData = await _db.Queryable<LqKdDeductinfoEntity>()
                    .Where(x => baseItems.Select(b => b.Id).Contains(x.DeductId))
                    .Where(x => x.IsEffective == StatusEnum.有效.GetHashCode())
                    .GroupBy(x => x.DeductId)
                    .Select(x => new
                    {
                        BillingItemId = x.DeductId,
                        TotalDeduct = SqlFunc.AggregateSum(x.ProjectNumber)
                    })
                    .ToListAsync();

                // 组装结果
                var remainingItems = baseItems.Select(item => new RemainingItemInfo
                {
                    BillingItemId = item.Id,
                    ItemId = item.Px,
                    ItemName = item.Pxmc,
                    ItemPrice = item.Pxjg,
                    SourceType = item.SourceType,
                    TotalPurchased = item.ProjectNumber,
                    Remark = item.Remark,
                    ConsumedCount = consumedData.FirstOrDefault(c => c.BillingItemId == item.Id)?.TotalConsumed ?? 0,
                    RefundedCount = refundedData.FirstOrDefault(r => r.BillingItemId == item.Id)?.TotalRefunded ?? 0,
                    DeductCount = deductData.FirstOrDefault(d => d.BillingItemId == item.Id)?.TotalDeduct ?? 0,
                }).ToList();

                // 计算剩余数量
                foreach (var item in remainingItems)
                {
                    item.RemainingCount = item.TotalPurchased - item.ConsumedCount - item.RefundedCount - item.DeductCount;
                }
                //过滤剩余数量为0的品项,先暂时为不等于 0 ，因为现在有问题，会出现负数，后面再修改
                remainingItems = remainingItems.Where(item => item.RemainingCount != 0).ToList();
                return new MemberRemainingItemsOutput
                {
                    MemberId = memberId,
                    MemberName = memberInfo.Khmc,
                    RemainingItems = remainingItems,
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询会员剩余品项失败，会员ID：{MemberId}", memberId);
                throw NCCException.Oh(ErrorCode.COM1005, "查询会员剩余品项失败");
            }
        }
        #endregion

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

        #region 导入客户信息
        /// <summary>
        /// 从"清理跨店的重复"表导入客户信息
        /// </summary>
        /// <remarks>
        /// 将"清理跨店的重复"表中的数据导入到客户表中，客户类型设置为"潜客"
        /// 
        /// 导入规则：
        /// 1. 根据客户电话去重，避免重复导入
        /// 2. 客户类型统一设置为"潜客"
        /// 3. 使用YitIdHelper生成客户ID
        /// 4. 根据门店ID设置归属门店
        /// 5. 根据拓客员工设置拓客人员
        /// 
        /// 字段映射：
        /// - 客户姓名 -> khmc (客户名称)
        /// - 客户电话 -> sjh (手机号)
        /// - F_StoreId -> gsmd (归属门店)
        /// - 拓客员工 -> F_ExpandUser (拓客人员)
        /// - 日期 -> zcsj (注册时间)
        /// </remarks>
        /// <returns>导入结果</returns>
        /// <response code="200">导入成功</response>
        /// <response code="500">服务器内部错误</response>
        [HttpPost("import-customers-from-cleanup")]
        public async Task<dynamic> ImportCustomersFromCleanup()
        {
            try
            {
                _db.BeginTran();

                // 1. 查询"清理跨店的重复"表数据，按客户电话去重
                var cleanupData = await _db.Ado.SqlQueryAsync<dynamic>(@"
                    SELECT 
                        F_Id,
                        客户姓名,
                        客户电话,
                        归属门店,
                        拓客员工,
                        日期,
                        F_StoreId,
                        F_UserId
                    FROM `清理跨店的重复`
                    WHERE 客户电话 IS NOT NULL 
                      AND 客户电话 != ''
                      AND 客户姓名 IS NOT NULL 
                      AND 客户姓名 != ''
                    GROUP BY 客户电话
                    ORDER BY 日期 DESC");

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

                foreach (var item in cleanupData)
                {
                    try
                    {
                        // 2. 检查客户是否已存在（根据手机号）
                        string phoneNumber = (string)item.客户电话;
                        var existingCustomer = await _db.Queryable<LqKhxxEntity>()
                            .Where(x => x.Sjh == phoneNumber)
                            .FirstAsync();

                        if (existingCustomer != null)
                        {
                            skipCount++;
                            continue;
                        }

                        // 3. 生成客户ID
                        string customerId = YitIdHelper.NextId().ToString();

                        // 4. 创建客户实体
                        var customerEntity = new LqKhxxEntity
                        {
                            Id = customerId,
                            Khmc = (string)item.客户姓名,
                            Sjh = (string)item.客户电话,
                            Dah = $"GK{DateTime.Now:yyyyMMdd}{customerId.Substring(customerId.Length - 6)}", // 生成档案号
                            Xb = "未知", // 默认性别
                            Gsmd = (string)item.F_StoreId, // 使用门店ID
                            Zcsj = DateTime.TryParse((string)item.日期, out DateTime regDate) ? regDate : DateTime.Now,
                            Khlx = MemberTypeEnum.线索.GetHashCode().ToString(), // 客户类型设置为潜客
                            Jdqd = "", // 进店渠道
                            Lxdz = "", // 联系地址
                            Bz = "历史潜客导入",
                            CreateTime = DateTime.Now,
                            ExpandUser = (string)item.F_UserId, // 拓客人员
                            MainHealthUser = null,
                            SubHealthUser = null
                        };

                        // 5. 插入客户记录
                        await _db.Insertable(customerEntity).ExecuteCommandAsync();

                        successCount++;
                    }
                    catch (Exception ex)
                    {
                        errorCount++;
                        errorMessages.Add($"处理客户 {(string)item.客户姓名}({(string)item.客户电话}) 时出错: {ex.Message}");
                    }
                }

                _db.CommitTran();

                return new
                {
                    success = true,
                    message = "客户信息导入完成",
                    totalRecords = cleanupData.Count,
                    successCount = successCount,
                    skipCount = skipCount,
                    errorCount = errorCount,
                    errorMessages = errorMessages.Take(10).ToList(), // 只返回前10个错误信息
                    importTime = DateTime.Now
                };
            }
            catch (Exception ex) when (!(ex is NCCException))
            {
                _db.RollbackTran();
                throw NCCException.Oh($"导入客户信息失败: {ex.Message}");
            }
        }
        #endregion

        #region 获取会员品项信息
        /// <summary>
        /// 获取所有会员的品项信息（分页）
        /// </summary>
        /// <remarks>
        /// 查询所有会员的品项信息，包括购买品项、赠送品项、体验品项、已消耗品项、已退卡品项、已储扣品项
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "currentPage": 1,
        ///   "pageSize": 10,
        ///   "MemberId": "会员ID",
        ///   "BelongStoreId": "所属门店ID",
        ///   "BillingStoreId": "开单门店ID",
        ///   "BillingUserId": "开单人ID",
        ///   "PurchaseItemId": "购买品项ID",
        ///   "GiftItemId": "赠送品项ID",
        ///   "ExperienceItemId": "体验品项ID"
        /// }
        /// ```
        /// 
        /// 参数说明：
        /// - currentPage: 当前页码（必填）
        /// - pageSize: 每页数量（必填）
        /// - MemberId: 会员ID（可选）
        /// - BelongStoreId: 所属门店ID（可选）
        /// - BillingStoreId: 开单门店ID（可选）
        /// - BillingUserId: 开单人ID（可选）
        /// - PurchaseItemId: 购买品项ID（可选）
        /// - GiftItemId: 赠送品项ID（可选）
        /// - ExperienceItemId: 体验品项ID（可选）
        /// 
        /// 返回数据结构说明：
        /// ```json
        /// {
        ///   "pagination": {
        ///     "pageIndex": 1,
        ///     "pageSize": 10,
        ///     "total": 100
        ///   },
        ///   "list": [
        ///     {
        ///       "memberId": "会员ID",
        ///       "memberCode": "会员编号（档案号）",
        ///       "memberName": "会员名称",
        ///       "memberPhone": "会员电话",
        ///       "belongStoreId": "所属门店ID",
        ///       "belongStoreName": "所属门店名称",
        ///       "billingItems": [
        ///         {
        ///           "billingId": "开单ID",
        ///           "billingTime": "2025-11-15T10:00:00",
        ///           "billingStoreId": "开单门店ID",
        ///           "billingStoreName": "开单门店名称",
        ///           "billingUserId": "开单人ID",
        ///           "billingUserName": "开单人名称",
        ///           "purchaseItems": [
        ///             {
        ///               "itemId": "品项ID",
        ///               "itemName": "品项名称",
        ///               "itemPrice": 100.00,
        ///               "projectNumber": 5.0,
        ///               "totalAmount": 500.00
        ///             }
        ///           ],
        ///           "purchaseItemsTotalAmount": 500.00,
        ///           "giftItems": [
        ///             {
        ///               "itemId": "品项ID",
        ///               "itemName": "品项名称",
        ///               "itemPrice": 50.00,
        ///               "projectNumber": 2.0,
        ///               "totalAmount": 100.00
        ///             }
        ///           ],
        ///           "experienceItems": [
        ///             {
        ///               "itemId": "品项ID",
        ///               "itemName": "品项名称",
        ///               "itemPrice": 30.00,
        ///               "projectNumber": 1.0,
        ///               "totalAmount": 30.00
        ///             }
        ///           ],
        ///           "consumedItems": [
        ///             {
        ///               "itemId": "品项ID",
        ///               "itemName": "品项名称",
        ///               "itemPrice": 100.00,
        ///               "projectNumber": 2.0,
        ///               "totalAmount": 200.00
        ///             }
        ///           ],
        ///           "consumedItemsTotalAmount": 200.00,
        ///           "refundedItems": [
        ///             {
        ///               "itemId": "品项ID",
        ///               "itemName": "品项名称",
        ///               "itemPrice": 100.00,
        ///               "projectNumber": 1.0,
        ///               "totalAmount": 100.00
        ///             }
        ///           ],
        ///           "refundedItemsTotalAmount": 100.00,
        ///           "deductedItems": [
        ///             {
        ///               "itemId": "品项ID",
        ///               "itemName": "品项名称",
        ///               "itemPrice": 100.00,
        ///               "projectNumber": 1.0,
        ///               "totalAmount": 100.00
        ///             }
        ///           ],
        ///           "deductedItemsTotalAmount": 100.00
        ///         }
        ///       ]
        ///     }
        ///   ]
        /// }
        /// ```
        /// 
        /// 返回字段说明：
        /// 
        /// **分页信息 (pagination)**:
        /// - pageIndex: 当前页码
        /// - pageSize: 每页数量
        /// - total: 总记录数
        /// 
        /// **会员信息 (list[].memberInfo)**:
        /// - memberId: 会员ID
        /// - memberCode: 会员编号（档案号）
        /// - memberName: 会员名称
        /// - memberPhone: 会员电话
        /// - belongStoreId: 所属门店ID
        /// - belongStoreName: 所属门店名称
        /// 
        /// **开单信息 (list[].billingItems[])**:
        /// - billingId: 开单ID
        /// - billingTime: 开单时间（DateTime格式）
        /// - billingStoreId: 开单门店ID
        /// - billingStoreName: 开单门店名称
        /// - billingUserId: 开单人ID
        /// - billingUserName: 开单人名称
        /// 
        /// **品项信息 (list[].billingItems[].*Items[])**:
        /// - purchaseItems: 购买品项列表
        /// - purchaseItemsTotalAmount: 购买品项总金额
        /// - giftItems: 赠送品项列表
        /// - experienceItems: 体验品项列表
        /// - consumedItems: 已消耗品项列表
        /// - consumedItemsTotalAmount: 已消耗品项总金额
        /// - refundedItems: 已退卡品项列表
        /// - refundedItemsTotalAmount: 已退卡品项总金额
        /// - deductedItems: 已储扣品项列表
        /// - deductedItemsTotalAmount: 已储扣品项总金额
        /// 
        /// **品项明细 (ItemDetail)**:
        /// - itemId: 品项ID
        /// - itemName: 品项名称
        /// - itemPrice: 品项单价
        /// - projectNumber: 项目次数（支持小数）
        /// - totalAmount: 总金额（单价 × 次数）
        /// </remarks>
        /// <param name="input">查询参数</param>
        /// <returns>会员品项信息列表（分页）</returns>
        /// <response code="200">查询成功，返回会员品项信息列表</response>
        /// <response code="400">参数错误，如页码或页大小无效</response>
        /// <response code="500">服务器错误，查询过程中发生异常</response>
        [HttpPost("get-member-item-info")]
        public async Task<dynamic> GetMemberItemInfo([FromBody] MemberItemInfoQueryInput input)
        {
            try
            {
                // 1. 构建会员查询条件
                var memberQuery = _db.Queryable<LqKhxxEntity>();

                // 筛选条件
                if (!string.IsNullOrEmpty(input.MemberId))
                {
                    memberQuery = memberQuery.Where(x => x.Id == input.MemberId);
                }

                if (!string.IsNullOrEmpty(input.BelongStoreId))
                {
                    memberQuery = memberQuery.Where(x => x.Gsmd == input.BelongStoreId);
                }

                if (input.MemberType >= 0)
                {
                    memberQuery = memberQuery.Where(x => x.Khlx == input.MemberType.GetHashCode().ToString());
                }

                // 如果指定了开单门店、开单人、品项ID等条件，需要通过开单表进行筛选
                // 处理多门店筛选：优先使用BillingStoreIds，如果没有则使用BillingStoreId
                var billingStoreIds = new List<string>();
                if (input.BillingStoreIds != null && input.BillingStoreIds.Any())
                {
                    billingStoreIds = input.BillingStoreIds.Where(x => !string.IsNullOrEmpty(x)).ToList();
                }
                else if (!string.IsNullOrEmpty(input.BillingStoreId))
                {
                    billingStoreIds = new List<string> { input.BillingStoreId };
                }

                if (billingStoreIds.Any() ||
                    !string.IsNullOrEmpty(input.BillingUserId) ||
                    !string.IsNullOrEmpty(input.PurchaseItemId) ||
                    !string.IsNullOrEmpty(input.GiftItemId) ||
                    !string.IsNullOrEmpty(input.ExperienceItemId))
                {
                    // 先查询符合条件的开单记录，获取会员ID列表
                    var billingQuery = _db.Queryable<LqKdKdjlbEntity>()
                        .Where(x => x.IsEffective == StatusEnum.有效.GetHashCode());

                    if (billingStoreIds.Any())
                    {
                        billingQuery = billingQuery.Where(x => billingStoreIds.Contains(x.Djmd));
                    }

                    if (!string.IsNullOrEmpty(input.BillingUserId))
                    {
                        billingQuery = billingQuery.Where(x => x.CreateUser == input.BillingUserId);
                    }

                    // 如果指定了品项ID，需要通过品项明细表进行筛选
                    if (!string.IsNullOrEmpty(input.PurchaseItemId) || !string.IsNullOrEmpty(input.GiftItemId) || !string.IsNullOrEmpty(input.ExperienceItemId))
                    {
                        var itemQuery = _db.Queryable<LqKdPxmxEntity>().Where(x => x.IsEffective == StatusEnum.有效.GetHashCode());
                        if (!string.IsNullOrEmpty(input.PurchaseItemId))
                        {
                            itemQuery = itemQuery.Where(x => x.Px == input.PurchaseItemId && x.SourceType == "购买");
                        }

                        if (!string.IsNullOrEmpty(input.GiftItemId))
                        {
                            itemQuery = itemQuery.Where(x => x.Px == input.GiftItemId && x.SourceType == "赠送");
                        }

                        if (!string.IsNullOrEmpty(input.ExperienceItemId))
                        {
                            itemQuery = itemQuery.Where(x => x.Px == input.ExperienceItemId && x.SourceType == "体验");
                        }

                        var itemBillingIds = await itemQuery.Select(x => x.Glkdbh).Distinct().ToListAsync();
                        if (itemBillingIds != null && itemBillingIds.Any())
                        {
                            billingQuery = billingQuery.Where(x => itemBillingIds.Contains(x.Id));
                        }
                        else
                        {
                            // 如果没有匹配的品项记录，返回空结果
                            return PageResult<MemberItemInfoOutput>.SqlSugarPageResult(new SqlSugarPagedList<MemberItemInfoOutput>
                            {
                                list = new List<MemberItemInfoOutput>(),
                                pagination = new PagedModel
                                {
                                    PageIndex = input.currentPage,
                                    PageSize = input.pageSize,
                                    Total = 0
                                }
                            });
                        }
                    }

                    var memberIds = await billingQuery.Select(x => x.Kdhy).Distinct().ToListAsync();
                    if (memberIds != null && memberIds.Any())
                    {
                        memberQuery = memberQuery.Where(x => memberIds.Contains(x.Id));
                    }
                    else
                    {
                        // 如果没有匹配的开单记录，返回空结果
                        return PageResult<MemberItemInfoOutput>.SqlSugarPageResult(new SqlSugarPagedList<MemberItemInfoOutput>
                        {
                            list = new List<MemberItemInfoOutput>(),
                            pagination = new PagedModel
                            {
                                PageIndex = input.currentPage,
                                PageSize = input.pageSize,
                                Total = 0
                            }
                        });
                    }
                }

                // 2. 分页查询会员列表
                var totalCount = await memberQuery.CountAsync();
                var skipCount = (input.currentPage - 1) * input.pageSize;
                var memberList = await memberQuery
                    .OrderBy(x => x.Id)
                    .Skip(skipCount)
                    .Take(input.pageSize)
                    .Select(x => new
                    {
                        x.Id,
                        x.Dah,
                        x.Khmc,
                        x.Sjh,
                        x.Gsmd,
                        x.Khlx
                    })
                    .ToListAsync();

                var memberData = new SqlSugarPagedList<dynamic>
                {
                    list = memberList.Cast<dynamic>().ToList(),
                    pagination = new PagedModel
                    {
                        PageIndex = input.currentPage,
                        PageSize = input.pageSize,
                        Total = totalCount
                    }
                };

                if (memberList == null || !memberList.Any())
                {
                    return PageResult<MemberItemInfoOutput>.SqlSugarPageResult(new SqlSugarPagedList<MemberItemInfoOutput>
                    {
                        list = new List<MemberItemInfoOutput>(),
                        pagination = new PagedModel
                        {
                            PageIndex = input.currentPage,
                            PageSize = input.pageSize,
                            Total = totalCount
                        }
                    });
                }

                var memberIdsList = memberList.Select(x => x.Id).ToList();

                // 3. 批量查询会员的开单记录
                var billingRecords = await _db.Queryable<LqKdKdjlbEntity>()
                    .Where(x => memberIdsList.Contains(x.Kdhy) && x.IsEffective == StatusEnum.有效.GetHashCode())
                    .Select(x => new
                    {
                        x.Id,
                        x.Kdhy,
                        x.Kdrq,
                        x.Djmd,
                        x.CreateUser
                    })
                    .ToListAsync();

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

                // 4. 批量查询开单的品项明细
                var itemDetails = new List<dynamic>();
                if (billingIds.Any())
                {
                    var itemDetailsData = await _db.Queryable<LqKdPxmxEntity>()
                        .Where(x => billingIds.Contains(x.Glkdbh) && x.IsEffective == StatusEnum.有效.GetHashCode())
                        .Select(x => new
                        {
                            x.Id,
                            x.Glkdbh,
                            x.Px,
                            x.Pxmc,
                            x.Pxjg,
                            x.SourceType,
                            x.ProjectNumber,
                            x.TotalPrice
                        })
                        .ToListAsync();
                    itemDetails = itemDetailsData.Cast<dynamic>().ToList();
                }

                var itemDetailIds = itemDetails.Select(x => (string)x.Id).ToList();

                // 5. 批量查询消耗品项
                var consumedItems = new List<dynamic>();
                if (itemDetailIds.Any())
                {
                    var consumedItemsData = await _db.Queryable<LqXhPxmxEntity>()
                        .Where(x => itemDetailIds.Contains(x.BillingItemId) && x.IsEffective == StatusEnum.有效.GetHashCode())
                        .Select(x => new
                        {
                            x.BillingItemId,
                            x.Px,
                            x.Pxmc,
                            x.Pxjg,
                            x.ProjectNumber,
                            x.TotalPrice
                        })
                        .ToListAsync();
                    consumedItems = consumedItemsData.Cast<dynamic>().ToList();
                }

                // 6. 批量查询退卡品项
                var refundedItems = new List<dynamic>();
                if (itemDetailIds.Any())
                {
                    var refundedItemsData = await _db.Queryable<LqHytkMxEntity>()
                        .Where(x => itemDetailIds.Contains(x.BillingItemId) && x.IsEffective == StatusEnum.有效.GetHashCode())
                        .Select(x => new
                        {
                            x.BillingItemId,
                            x.Px,
                            x.Pxmc,
                            x.Pxjg,
                            x.ProjectNumber,
                            x.Tkje
                        })
                        .ToListAsync();
                    refundedItems = refundedItemsData.Cast<dynamic>().ToList();
                }

                // 7. 批量查询储扣品项
                var deductedItems = new List<dynamic>();
                if (billingIds.Any())
                {
                    var deductedItemsData = await _db.Queryable<LqKdDeductinfoEntity>()
                        .Where(x => billingIds.Contains(x.BillingId) && x.IsEffective == StatusEnum.有效.GetHashCode())
                        .Select(x => new
                        {
                            x.BillingId,
                            x.ItemId,
                            x.ItemName,
                            x.UnitPrice,
                            x.ProjectNumber,
                            x.Amount
                        })
                        .ToListAsync();
                    deductedItems = deductedItemsData.Cast<dynamic>().ToList();
                }

                // 8. 批量查询门店信息
                var storeIds = new List<string>();
                if (memberList != null && memberList.Any())
                {
                    storeIds.AddRange(memberList.Select(x => x.Gsmd).Where(x => !string.IsNullOrEmpty(x)));
                }
                if (billingRecords != null && billingRecords.Any())
                {
                    storeIds.AddRange(billingRecords.Select(x => x.Djmd).Where(x => !string.IsNullOrEmpty(x)));
                }
                storeIds = storeIds.Distinct().ToList();

                var stores = new Dictionary<string, string>();
                if (storeIds.Any())
                {
                    var storeList = await _db.Queryable<LqMdxxEntity>()
                        .Where(x => storeIds.Contains(x.Id))
                        .Select(x => new { x.Id, x.Dm })
                        .ToListAsync();

                    stores = storeList.ToDictionary(x => x.Id, x => x.Dm ?? "");
                }

                // 9. 批量查询用户信息
                var userIds = new List<string>();
                if (billingRecords != null && billingRecords.Any())
                {
                    userIds = billingRecords.Select(x => x.CreateUser)
                        .Where(x => !string.IsNullOrEmpty(x))
                        .Distinct()
                        .ToList();
                }

                var users = new Dictionary<string, string>();
                if (userIds.Any())
                {
                    var userList = await _db.Queryable<UserEntity>()
                        .Where(x => userIds.Contains(x.Id))
                        .Select(x => new { x.Id, x.RealName })
                        .ToListAsync();

                    users = userList.ToDictionary(x => x.Id, x => x.RealName ?? "");
                }

                // 10. 组装数据
                var result = new List<MemberItemInfoOutput>();

                foreach (var member in memberList)
                {
                    var memberOutput = new MemberItemInfoOutput
                    {
                        MemberId = member.Id,
                        MemberCode = member.Dah ?? "",
                        MemberName = member.Khmc ?? "",
                        MemberPhone = member.Sjh ?? "",
                        MemberType = member.Khlx.ToInt(),
                        BelongStoreId = member.Gsmd ?? "",
                        BelongStoreName = stores.ContainsKey(member.Gsmd ?? "") ? stores[member.Gsmd] : ""
                    };

                    // 获取该会员的所有开单
                    var memberBillings = billingRecords.Where(x => x.Kdhy == member.Id).ToList();

                    foreach (var billing in memberBillings)
                    {
                        var billingItem = new BillingItemInfo
                        {
                            BillingId = billing.Id,
                            BillingTime = billing.Kdrq,
                            BillingStoreId = billing.Djmd ?? "",
                            BillingStoreName = stores.ContainsKey(billing.Djmd ?? "") ? stores[billing.Djmd] : "",
                            BillingUserId = billing.CreateUser ?? "",
                            BillingUserName = users.ContainsKey(billing.CreateUser ?? "") ? users[billing.CreateUser] : ""
                        };

                        // 获取该开单的所有品项明细
                        var billingItemDetails = itemDetails.Where(x =>
                        {
                            try
                            {
                                return x.Glkdbh?.ToString() == billing.Id;
                            }
                            catch
                            {
                                return false;
                            }
                        }).ToList();

                        // 分类品项
                        foreach (var item in billingItemDetails)
                        {
                            try
                            {
                                var itemDetail = new ItemDetail
                                {
                                    ItemId = item.Px?.ToString() ?? "",
                                    ItemName = item.Pxmc?.ToString() ?? "",
                                    ItemPrice = Convert.ToDecimal(item.Pxjg ?? 0),
                                    ProjectNumber = Convert.ToDecimal(item.ProjectNumber ?? 0),
                                    TotalAmount = Convert.ToDecimal(item.TotalPrice ?? 0)
                                };

                                var sourceType = item.SourceType?.ToString() ?? "";
                                if (sourceType == "购买")
                                {
                                    billingItem.PurchaseItems.Add(itemDetail);
                                    billingItem.PurchaseItemsTotalAmount += itemDetail.TotalAmount;
                                }
                                else if (sourceType == "赠送")
                                {
                                    billingItem.GiftItems.Add(itemDetail);
                                }
                                else if (sourceType == "体验")
                                {
                                    billingItem.ExperienceItems.Add(itemDetail);
                                }
                            }
                            catch (Exception ex)
                            {
                                _logger.LogWarning(ex, "处理品项明细时出错，开单ID：{BillingId}", billing.Id);
                            }
                        }

                        // 获取消耗品项
                        var billingItemIds = billingItemDetails.Select(x =>
                        {
                            try
                            {
                                return x.Id?.ToString() ?? "";
                            }
                            catch
                            {
                                return "";
                            }
                        }).Where(x => !string.IsNullOrEmpty(x)).ToList();

                        var consumed = consumedItems.Where(x =>
                        {
                            try
                            {
                                return billingItemIds.Contains(x.BillingItemId?.ToString() ?? "");
                            }
                            catch
                            {
                                return false;
                            }
                        }).ToList();

                        foreach (var consumedItem in consumed)
                        {
                            try
                            {
                                billingItem.ConsumedItems.Add(new ItemDetail
                                {
                                    ItemId = consumedItem.Px?.ToString() ?? "",
                                    ItemName = consumedItem.Pxmc?.ToString() ?? "",
                                    ItemPrice = Convert.ToDecimal(consumedItem.Pxjg ?? 0),
                                    ProjectNumber = Convert.ToDecimal(consumedItem.ProjectNumber ?? 0),
                                    TotalAmount = Convert.ToDecimal(consumedItem.TotalPrice ?? 0)
                                });
                                billingItem.ConsumedItemsTotalAmount += Convert.ToDecimal(consumedItem.TotalPrice ?? 0);
                            }
                            catch (Exception ex)
                            {
                                _logger.LogWarning(ex, "处理消耗品项时出错");
                            }
                        }

                        // 获取退卡品项
                        var refunded = refundedItems.Where(x =>
                        {
                            try
                            {
                                return billingItemIds.Contains(x.BillingItemId?.ToString() ?? "");
                            }
                            catch
                            {
                                return false;
                            }
                        }).ToList();

                        foreach (var refundedItem in refunded)
                        {
                            try
                            {
                                billingItem.RefundedItems.Add(new ItemDetail
                                {
                                    ItemId = refundedItem.Px?.ToString() ?? "",
                                    ItemName = refundedItem.Pxmc?.ToString() ?? "",
                                    ItemPrice = Convert.ToDecimal(refundedItem.Pxjg ?? 0),
                                    ProjectNumber = Convert.ToDecimal(refundedItem.ProjectNumber ?? 0),
                                    TotalAmount = Convert.ToDecimal(refundedItem.Tkje ?? 0)
                                });
                                billingItem.RefundedItemsTotalAmount += Convert.ToDecimal(refundedItem.Tkje ?? 0);
                            }
                            catch (Exception ex)
                            {
                                _logger.LogWarning(ex, "处理退卡品项时出错");
                            }
                        }

                        // 获取储扣品项
                        var deducted = deductedItems.Where(x =>
                        {
                            try
                            {
                                return x.BillingId?.ToString() == billing.Id;
                            }
                            catch
                            {
                                return false;
                            }
                        }).ToList();

                        foreach (var deductedItem in deducted)
                        {
                            try
                            {
                                billingItem.DeductedItems.Add(new ItemDetail
                                {
                                    ItemId = deductedItem.ItemId?.ToString() ?? "",
                                    ItemName = deductedItem.ItemName?.ToString() ?? "",
                                    ItemPrice = Convert.ToDecimal(deductedItem.UnitPrice ?? 0),
                                    ProjectNumber = Convert.ToDecimal(deductedItem.ProjectNumber ?? 0),
                                    TotalAmount = Convert.ToDecimal(deductedItem.Amount ?? 0)
                                });
                                billingItem.DeductedItemsTotalAmount += Convert.ToDecimal(deductedItem.Amount ?? 0);
                            }
                            catch (Exception ex)
                            {
                                _logger.LogWarning(ex, "处理储扣品项时出错");
                            }
                        }

                        memberOutput.BillingItems.Add(billingItem);
                    }

                    result.Add(memberOutput);
                }

                return PageResult<MemberItemInfoOutput>.SqlSugarPageResult(new SqlSugarPagedList<MemberItemInfoOutput>
                {
                    list = result,
                    pagination = new PagedModel
                    {
                        PageIndex = input.currentPage,
                        PageSize = input.pageSize,
                        Total = totalCount
                    }
                });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询会员品项信息失败，异常详情：{ExceptionDetail}", ex.ToString());
                throw NCCException.Oh(ErrorCode.COM1005, $"查询会员品项信息失败: {ex.Message}");
            }
        }
        #endregion

        #region 会员信息更新
        /// <summary>
        /// 更新会员信息（单个会员）
        /// </summary>
        /// <param name="memberId">会员ID</param>
        /// <returns></returns>
        [HttpPost("UpdateMemberInfo")]
        public async Task<dynamic> UpdateMemberInfo([FromBody] string memberId)
        {
            try
            {
                if (string.IsNullOrEmpty(memberId))
                {
                    throw NCCException.Oh("会员ID不能为空");
                }

                var member = await _db.Queryable<LqKhxxEntity>()
                    .Where(x => x.Id == memberId && x.IsEffective == StatusEnum.有效.GetHashCode())
                    .FirstAsync();

                if (member == null)
                {
                    throw NCCException.Oh("会员不存在");
                }

                await UpdateMemberInfoInternal(member);

                return new { success = true, message = "更新成功" };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "更新会员信息失败，会员ID：{MemberId}", memberId);
                throw NCCException.Oh($"更新会员信息失败：{ex.Message}");
            }
        }

        /// <summary>
        /// 批量更新所有会员信息（高性能版：使用SQL批量更新）【通过定时任务去执行，每天晚上执行一次】
        /// </summary>
        /// <returns></returns>
        [HttpPost("BatchUpdateMemberInfo")]
        [AllowAnonymous]
        public async Task<dynamic> BatchUpdateMemberInfo()
        {
            try
            {
                var startTime = DateTime.Now;
                _logger.LogInformation("开始批量更新会员信息（高性能SQL版）");

                // 1. 获取所有有效会员ID
                var memberIds = await _db.Queryable<LqKhxxEntity>()
                    .Where(x => x.IsEffective == StatusEnum.有效.GetHashCode())
                    .Select(x => x.Id)
                    .ToListAsync();

                var totalCount = memberIds.Count;

                if (totalCount == 0)
                {
                    return new
                    {
                        success = true,
                        totalCount = 0,
                        successCount = 0,
                        failCount = 0,
                        duration = "0秒"
                    };
                }

                _logger.LogInformation("开始使用SQL批量更新，会员总数：{Total}", totalCount);
                var sqlStartTime = DateTime.Now;

                // 使用原生SQL一次性更新所有会员信息
                var beautyThreshold = MemberInfoUpdateConfig.BeautyMemberAmountThreshold;
                var medicalThreshold = MemberInfoUpdateConfig.MedicalMemberAmountThreshold;
                var techThreshold = MemberInfoUpdateConfig.TechMemberAmountThreshold;
                var minSleepThreshold = MemberInfoUpdateConfig.SleepDaysThresholds.Min();
                var now = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                // 构建SQL更新语句
                var updateSql = $@"
UPDATE lq_khxx kh
LEFT JOIN (
    -- 生美会员判断
    SELECT 
        pxmx.F_MemberId as MemberId,
        CASE WHEN SUM(CASE WHEN pxmx.F_ItemCategory = '生美' THEN pxmx.F_ActualPrice ELSE 0 END) > {beautyThreshold} THEN 1 ELSE 0 END as IsBeautyMember,
        MIN(CASE WHEN pxmx.F_ItemCategory = '生美' AND kd.kdrq IS NOT NULL THEN kd.kdrq ELSE NULL END) as BeautyMemberTime
    FROM lq_kd_pxmx pxmx
    INNER JOIN lq_kd_kdjlb kd ON pxmx.glkdbh = kd.F_Id
    WHERE pxmx.F_IsEffective = 1 
        AND kd.F_IsEffective = 1
        AND pxmx.F_ActualPrice > 0
        AND pxmx.px != '61'
    GROUP BY pxmx.F_MemberId
) beauty ON kh.F_Id = beauty.MemberId
LEFT JOIN (
    -- 医美会员判断
    SELECT 
        pxmx.F_MemberId as MemberId,
        CASE WHEN SUM(CASE WHEN pxmx.F_ItemCategory = '医美' THEN pxmx.F_ActualPrice ELSE 0 END) > {medicalThreshold} THEN 1 ELSE 0 END as IsMedicalMember,
        MIN(CASE WHEN pxmx.F_ItemCategory = '医美' AND kd.kdrq IS NOT NULL THEN kd.kdrq ELSE NULL END) as MedicalMemberTime
    FROM lq_kd_pxmx pxmx
    INNER JOIN lq_kd_kdjlb kd ON pxmx.glkdbh = kd.F_Id
    WHERE pxmx.F_IsEffective = 1 
        AND kd.F_IsEffective = 1
        AND pxmx.F_ActualPrice > 0
        AND pxmx.px != '61'
    GROUP BY pxmx.F_MemberId
) medical ON kh.F_Id = medical.MemberId
LEFT JOIN (
    -- 科技部会员判断
    SELECT 
        pxmx.F_MemberId as MemberId,
        CASE WHEN MAX(CASE WHEN pxmx.F_ItemCategory = '科美' AND pxmx.F_ActualPrice >= {techThreshold} THEN 1 ELSE 0 END) = 1 THEN 1 ELSE 0 END as IsTechMember,
        MIN(CASE WHEN pxmx.F_ItemCategory = '科美' AND pxmx.F_ActualPrice >= {techThreshold} AND kd.kdrq IS NOT NULL THEN kd.kdrq ELSE NULL END) as TechMemberTime
    FROM lq_kd_pxmx pxmx
    INNER JOIN lq_kd_kdjlb kd ON pxmx.glkdbh = kd.F_Id
    WHERE pxmx.F_IsEffective = 1 
        AND kd.F_IsEffective = 1
        AND pxmx.F_ActualPrice > 0
        AND pxmx.px != '61'
    GROUP BY pxmx.F_MemberId
) tech ON kh.F_Id = tech.MemberId
LEFT JOIN (
    -- 到店天数、首次到店时间、最后到店时间
    SELECT 
        xh.hy as MemberId,
        COUNT(DISTINCT DATE(xh.hksj)) as VisitDays,
        MIN(xh.hksj) as FirstVisitTime,
        MAX(xh.hksj) as LastVisitTime
    FROM lq_xh_hyhk xh
    WHERE xh.F_IsEffective = 1
    GROUP BY xh.hy
) visit ON kh.F_Id = visit.MemberId
LEFT JOIN (
    -- 开卡总金额
    SELECT 
        kd.kdhy as MemberId,
        COALESCE(SUM(kd.sfyj), 0) as TotalBillingAmount
    FROM lq_kd_kdjlb kd
    WHERE kd.F_IsEffective = 1
    GROUP BY kd.kdhy
) billing ON kh.F_Id = billing.MemberId
LEFT JOIN (
    -- 耗卡总金额
    SELECT 
        xh.hy as MemberId,
        COALESCE(SUM(xh.xfje), 0) as TotalConsumeAmount
    FROM lq_xh_hyhk xh
    WHERE xh.F_IsEffective = 1
    GROUP BY xh.hy
) consume ON kh.F_Id = consume.MemberId
LEFT JOIN (
    -- 退款总金额
    SELECT 
        tk.hy as MemberId,
        COALESCE(SUM(tk.F_ActualRefundAmount), 0) as TotalRefundAmount
    FROM lq_hytk_hytk tk
    WHERE tk.F_IsEffective = 1
    GROUP BY tk.hy
) refund ON kh.F_Id = refund.MemberId
SET
    kh.F_IsBeautyMember = COALESCE(beauty.IsBeautyMember, 0),
    kh.F_IsMedicalMember = COALESCE(medical.IsMedicalMember, 0),
    kh.F_IsTechMember = COALESCE(tech.IsTechMember, 0),
    kh.F_BeautyMemberTime = CASE WHEN kh.F_BeautyMemberTime IS NULL THEN beauty.BeautyMemberTime ELSE kh.F_BeautyMemberTime END,
    kh.F_MedicalMemberTime = CASE WHEN kh.F_MedicalMemberTime IS NULL THEN medical.MedicalMemberTime ELSE kh.F_MedicalMemberTime END,
    kh.F_TechMemberTime = CASE WHEN kh.F_TechMemberTime IS NULL THEN tech.TechMemberTime ELSE kh.F_TechMemberTime END,
    kh.F_FirstVisitTime = visit.FirstVisitTime,
    kh.F_LastVisitTime = visit.LastVisitTime,
    kh.F_VisitDays = COALESCE(visit.VisitDays, 0),
    kh.F_TotalBillingAmount = COALESCE(billing.TotalBillingAmount, 0),
    kh.F_TotalConsumeAmount = COALESCE(consume.TotalConsumeAmount, 0),
    kh.F_RemainingRightsAmount = GREATEST(0, COALESCE(billing.TotalBillingAmount, 0) - COALESCE(consume.TotalConsumeAmount, 0) - COALESCE(refund.TotalRefundAmount, 0)),
    kh.F_SleepDays = GREATEST(0, DATEDIFF(CURDATE(), COALESCE(kh.F_LastConsumeTime, visit.LastVisitTime, kh.F_CreateTime))),
    kh.F_SleepStartTime = CASE 
        WHEN DATEDIFF(CURDATE(), COALESCE(kh.F_LastConsumeTime, visit.LastVisitTime, kh.F_CreateTime)) >= {minSleepThreshold} 
            AND kh.F_SleepStartTime IS NULL 
        THEN DATE(COALESCE(kh.F_LastConsumeTime, visit.LastVisitTime, kh.F_CreateTime))
        WHEN DATEDIFF(CURDATE(), COALESCE(kh.F_LastConsumeTime, visit.LastVisitTime, kh.F_CreateTime)) < {minSleepThreshold}
        THEN NULL
        ELSE kh.F_SleepStartTime
    END,
    kh.F_ConsumeLevel = CASE
        WHEN COALESCE(consume.TotalConsumeAmount, 0) >= 200000 THEN 5
        WHEN COALESCE(consume.TotalConsumeAmount, 0) >= 100000 THEN 4
        WHEN COALESCE(consume.TotalConsumeAmount, 0) >= 50000 THEN 3
        WHEN COALESCE(consume.TotalConsumeAmount, 0) >= 30000 THEN 2
        WHEN COALESCE(consume.TotalConsumeAmount, 0) >= 10000 THEN 1
        ELSE 0
    END,
    kh.F_ConsumeLevelUpdateTime = CASE 
        WHEN kh.F_ConsumeLevel != CASE
            WHEN COALESCE(consume.TotalConsumeAmount, 0) >= 200000 THEN 5
            WHEN COALESCE(consume.TotalConsumeAmount, 0) >= 100000 THEN 4
            WHEN COALESCE(consume.TotalConsumeAmount, 0) >= 50000 THEN 3
            WHEN COALESCE(consume.TotalConsumeAmount, 0) >= 30000 THEN 2
            WHEN COALESCE(consume.TotalConsumeAmount, 0) >= 10000 THEN 1
            ELSE 0
        END THEN NOW()
        WHEN kh.F_ConsumeLevelUpdateTime IS NULL AND CASE
            WHEN COALESCE(consume.TotalConsumeAmount, 0) >= 200000 THEN 5
            WHEN COALESCE(consume.TotalConsumeAmount, 0) >= 100000 THEN 4
            WHEN COALESCE(consume.TotalConsumeAmount, 0) >= 50000 THEN 3
            WHEN COALESCE(consume.TotalConsumeAmount, 0) >= 30000 THEN 2
            WHEN COALESCE(consume.TotalConsumeAmount, 0) >= 10000 THEN 1
            ELSE 0
        END > 0 THEN NOW()
        ELSE kh.F_ConsumeLevelUpdateTime
    END
WHERE kh.F_IsEffective = 1";

                var affectedRows = await _db.Ado.ExecuteCommandAsync(updateSql);
                var sqlEndTime = DateTime.Now;
                var duration = (sqlEndTime - sqlStartTime).TotalSeconds;

                _logger.LogInformation("SQL批量更新完成，影响行数：{Rows}，耗时：{Duration}秒", affectedRows, duration);

                var endTime = DateTime.Now;
                var totalDuration = (endTime - startTime).TotalSeconds;

                _logger.LogInformation("批量更新会员信息完成，总数量：{Total}，影响行数：{Rows}，耗时：{Duration}秒",
                    totalCount, affectedRows, totalDuration);

                return new
                {
                    success = true,
                    totalCount = totalCount,
                    successCount = affectedRows,
                    failCount = totalCount - affectedRows,
                    duration = $"{totalDuration:F2}秒",
                    sqlDuration = $"{duration:F2}秒"
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "批量更新会员信息失败");
                throw NCCException.Oh($"批量更新会员信息失败：{ex.Message}");
            }
        }

        /// <summary>
        /// 更新会员信息内部方法
        /// </summary>
        /// <param name="member">会员实体</param>
        private async Task UpdateMemberInfoInternal(LqKhxxEntity member)
        {
            // 1. 判断生美/医美/科技部会员
            await UpdateMemberTypeFlags(member);

            // 2. 计算到店天数
            await UpdateVisitDays(member);

            // 3. 计算沉睡天数
            await UpdateSleepDays(member);

            // 4. 计算消费金额和等级
            await UpdateConsumeInfo(member);

            // 5. 更新到数据库
            await _db.Updateable(member)
                .UpdateColumns(x => new
                {
                    x.IsBeautyMember,
                    x.IsMedicalMember,
                    x.IsTechMember,
                    x.BeautyMemberTime,
                    x.MedicalMemberTime,
                    x.TechMemberTime,
                    x.FirstVisitTime,
                    x.LastVisitTime,
                    x.VisitDays,
                    x.SleepStartTime,
                    x.SleepDays,
                    x.TotalConsumeAmount,
                    x.TotalBillingAmount,
                    x.RemainingRightsAmount,
                    x.ConsumeLevel,
                    x.ConsumeLevelUpdateTime
                })
                .ExecuteCommandAsync();
        }

        /// <summary>
        /// 更新会员类型标识（生美/医美/科技部会员）
        /// </summary>
        private async Task UpdateMemberTypeFlags(LqKhxxEntity member)
        {
            // 查询该会员的所有有效开单品项明细
            var billingItems = await _db.Queryable<LqKdPxmxEntity>()
                .InnerJoin<LqKdKdjlbEntity>((pxmx, kd) => pxmx.Glkdbh == kd.Id)
                .Where((pxmx, kd) =>
                    pxmx.MemberId == member.Id
                    && pxmx.IsEffective == StatusEnum.有效.GetHashCode()
                    && kd.IsEffective == StatusEnum.有效.GetHashCode()
                    && pxmx.ActualPrice > 0
                    && pxmx.Px != "61") // 排除女神卡
                .Select((pxmx, kd) => new
                {
                    pxmx.ItemCategory,
                    pxmx.ActualPrice,
                    pxmx.Pxjg,
                    kd.Kdrq
                })
                .ToListAsync();

            // 判断生美会员：开卡品项为生美并且金额大于阈值
            var beautyAmount = billingItems
                .Where(x => x.ItemCategory == "生美")
                .Sum(x => x.ActualPrice);

            if (beautyAmount > MemberInfoUpdateConfig.BeautyMemberAmountThreshold)
            {
                member.IsBeautyMember = 1;
                if (!member.BeautyMemberTime.HasValue)
                {
                    var firstBeautyBilling = billingItems
                        .Where(x => x.ItemCategory == "生美")
                        .OrderBy(x => x.Kdrq)
                        .FirstOrDefault();
                    member.BeautyMemberTime = firstBeautyBilling?.Kdrq;
                }
            }
            else
            {
                member.IsBeautyMember = 0;
                member.BeautyMemberTime = null;
            }

            // 判断医美会员：开卡品项为医美并且金额大于阈值
            var medicalAmount = billingItems
                .Where(x => x.ItemCategory == "医美")
                .Sum(x => x.ActualPrice);

            if (medicalAmount > MemberInfoUpdateConfig.MedicalMemberAmountThreshold)
            {
                member.IsMedicalMember = 1;
                if (!member.MedicalMemberTime.HasValue)
                {
                    var firstMedicalBilling = billingItems
                        .Where(x => x.ItemCategory == "医美")
                        .OrderBy(x => x.Kdrq)
                        .FirstOrDefault();
                    member.MedicalMemberTime = firstMedicalBilling?.Kdrq;
                }
            }
            else
            {
                member.IsMedicalMember = 0;
                member.MedicalMemberTime = null;
            }

            // 判断科技部会员：科美单品项金额大于等于阈值
            var hasTechMember = billingItems
                .Where(x => x.ItemCategory == "科美")
                .Any(x => x.ActualPrice >= MemberInfoUpdateConfig.TechMemberAmountThreshold);

            if (hasTechMember)
            {
                member.IsTechMember = 1;
                if (!member.TechMemberTime.HasValue)
                {
                    var firstTechBilling = billingItems
                        .Where(x => x.ItemCategory == "科美" && x.ActualPrice >= MemberInfoUpdateConfig.TechMemberAmountThreshold)
                        .OrderBy(x => x.Kdrq)
                        .FirstOrDefault();
                    member.TechMemberTime = firstTechBilling?.Kdrq;
                }
            }
            else
            {
                member.IsTechMember = 0;
                member.TechMemberTime = null;
            }
        }

        /// <summary>
        /// 更新到店天数
        /// </summary>
        private async Task UpdateVisitDays(LqKhxxEntity member)
        {
            // 查询该会员的所有有效耗卡记录，按天去重
            var visitDates = await _db.Queryable<LqXhHyhkEntity>()
                .Where(x =>
                    x.Hy == member.Id
                    && x.IsEffective == StatusEnum.有效.GetHashCode())
                .Select(x => new
                {
                    VisitDate = SqlFunc.ToDate(x.Hksj)
                })
                .Distinct()
                .ToListAsync();

            member.VisitDays = visitDates.Count;

            // 更新首次到店时间和最后到店时间
            if (visitDates.Any())
            {
                var firstVisit = await _db.Queryable<LqXhHyhkEntity>()
                    .Where(x => x.Hy == member.Id && x.IsEffective == StatusEnum.有效.GetHashCode())
                    .OrderBy(x => x.Hksj)
                    .Select(x => x.Hksj)
                    .FirstAsync();
                member.FirstVisitTime = firstVisit;

                var lastVisit = await _db.Queryable<LqXhHyhkEntity>()
                    .Where(x => x.Hy == member.Id && x.IsEffective == StatusEnum.有效.GetHashCode())
                    .OrderBy(x => x.Hksj, OrderByType.Desc)
                    .Select(x => x.Hksj)
                    .FirstAsync();
                member.LastVisitTime = lastVisit;
            }
            else
            {
                member.FirstVisitTime = null;
                member.LastVisitTime = null;
            }
        }

        /// <summary>
        /// 更新沉睡天数
        /// </summary>
        private Task UpdateSleepDays(LqKhxxEntity member)
        {
            var now = DateTime.Now;
            var lastConsumeTime = member.LastConsumeTime;

            if (!lastConsumeTime.HasValue)
            {
                // 如果没有最后消费时间，使用最后到店时间
                lastConsumeTime = member.LastVisitTime;
            }

            if (lastConsumeTime.HasValue)
            {
                var daysSinceLastConsume = (now.Date - lastConsumeTime.Value.Date).Days;
                member.SleepDays = Math.Max(0, daysSinceLastConsume);

                // 如果超过最小沉睡阈值，设置沉睡开始时间
                var minSleepThreshold = MemberInfoUpdateConfig.SleepDaysThresholds.Min();
                if (member.SleepDays >= minSleepThreshold)
                {
                    if (!member.SleepStartTime.HasValue)
                    {
                        member.SleepStartTime = lastConsumeTime.Value.Date;
                    }
                }
                else
                {
                    member.SleepStartTime = null;
                }
            }
            else
            {
                member.SleepDays = 0;
                member.SleepStartTime = null;
            }

            return Task.CompletedTask;
        }

        /// <summary>
        /// 更新消费信息（开卡总金额、耗卡总金额、剩余权益总金额、消费等级）
        /// </summary>
        private async Task UpdateConsumeInfo(LqKhxxEntity member)
        {
            // 1. 计算开卡总金额（累计所有开单的实付业绩总和）
            var totalBillingAmount = await _db.Queryable<LqKdKdjlbEntity>().Where(x => x.Kdhy == member.Id && x.IsEffective == StatusEnum.有效.GetHashCode()).SumAsync(x => (decimal?)x.Sfyj) ?? 0m;
            member.TotalBillingAmount = totalBillingAmount;

            // 2. 计算耗卡总金额（累计所有耗卡的金额总和）
            var totalConsumeAmount = await _db.Queryable<LqXhHyhkEntity>().Where(x => x.Hy == member.Id && x.IsEffective == StatusEnum.有效.GetHashCode()).SumAsync(x => (decimal?)x.Xfje) ?? 0m;
            member.TotalConsumeAmount = totalConsumeAmount;

            // 3. 计算退款总金额
            var totalRefundAmount = await _db.Queryable<LqHytkHytkEntity>().Where(x => x.Hy == member.Id && x.IsEffective == StatusEnum.有效.GetHashCode()).SumAsync(x => (decimal?)x.ActualRefundAmount) ?? 0m;

            // 4. 计算剩余权益总金额
            member.RemainingRightsAmount = totalBillingAmount - totalConsumeAmount - totalRefundAmount;
            if (member.RemainingRightsAmount < 0)
            {
                member.RemainingRightsAmount = 0m;
            }

            // 5. 计算消费等级（根据耗卡总金额）
            var oldLevel = member.ConsumeLevel;
            member.ConsumeLevel = MemberInfoUpdateConfig.GetConsumeLevel(totalConsumeAmount);

            // 如果等级发生变化，更新等级更新时间
            if (oldLevel != member.ConsumeLevel)
            {
                member.ConsumeLevelUpdateTime = DateTime.Now;
            }
            else if (!member.ConsumeLevelUpdateTime.HasValue && member.ConsumeLevel > 0)
            {
                // 如果之前没有等级更新时间，但当前有等级，设置当前时间
                member.ConsumeLevelUpdateTime = DateTime.Now;
            }
        }
        #endregion

        #region 会员生日管理

        /// <summary>
        /// 获取门店未来30天过生日的会员列表
        /// </summary>
        /// <remarks>
        /// 根据门店ID获取未来30天内过生日的会员信息，支持按阳历生日查询
        /// 
        /// 会员等级说明：0=D，1=C，2=B，3=A，4=A+，5=A++
        /// 
        /// 示例请求：
        /// ```http
        /// GET /api/Extend/LqKhxx/GetUpcomingBirthdays?storeId=门店ID
        /// ```
        /// 
        /// 参数说明：
        /// - storeId: 门店ID（可选，不传则查询所有门店）
        /// </remarks>
        /// <param name="storeId">门店ID（可选）</param>
        /// <returns>会员生日信息列表，包含会员等级、生日日期、剩余权益等信息</returns>
        /// <response code="200">成功返回会员生日列表</response>
        /// <response code="500">服务器错误</response>
        [HttpGet("GetUpcomingBirthdays")]
        public async Task<dynamic> GetUpcomingBirthdays(string storeId = null)
        {
            try
            {
                var now = DateTime.Now;
                var endDate = now.AddDays(30);
                
                // 构建查询
                var query = _db.Queryable<LqKhxxEntity>()
                    .Where(x => x.IsEffective == StatusEnum.有效.GetHashCode())
                    .Where(x => x.Yanglsr != null); // 必须有阳历生日

                // 门店过滤
                if (!string.IsNullOrEmpty(storeId))
                {
                    query = query.Where(x => x.Gsmd == storeId);
                }

                // 获取所有会员数据
                var members = await query.ToListAsync();

                // 在内存中筛选未来30天过生日的会员
                var upcomingBirthdays = new List<LqKhxxBirthdayOutput>();
                
                foreach (var member in members)
                {
                    if (!member.Yanglsr.HasValue) continue;

                    var birthday = member.Yanglsr.Value;
                    
                    // 计算今年和明年的生日
                    var thisYearBirthday = new DateTime(now.Year, birthday.Month, birthday.Day);
                    var nextYearBirthday = new DateTime(now.Year + 1, birthday.Month, birthday.Day);

                    DateTime upcomingBirthday;
                    
                    // 判断生日是今年还是明年
                    if (thisYearBirthday >= now.Date && thisYearBirthday <= endDate.Date)
                    {
                        upcomingBirthday = thisYearBirthday;
                    }
                    else if (nextYearBirthday >= now.Date && nextYearBirthday <= endDate.Date)
                    {
                        upcomingBirthday = nextYearBirthday;
                    }
                    else
                    {
                        continue; // 不在未来30天内
                    }

                    // 计算年龄
                    var age = now.Year - birthday.Year;
                    if (now.Month < birthday.Month || (now.Month == birthday.Month && now.Day < birthday.Day))
                    {
                        age--;
                    }

                    var output = new LqKhxxBirthdayOutput
                    {
                        id = member.Id,
                        khmc = member.Khmc,
                        sjh = member.Sjh,
                        dah = member.Dah,
                        xb = member.Xb,
                        gsmd = member.Gsmd,
                        yanglsr = member.Yanglsr,
                        yinlsr = member.Yinlsr,
                        birthdayDate = birthday.ToString("MM-dd"),
                        birthdayFullDate = upcomingBirthday,
                        consumeLevel = member.ConsumeLevel,
                        consumeLevelName = GetConsumeLevelName(member.ConsumeLevel),
                        remainingRightsAmount = member.RemainingRightsAmount,
                        bz = member.Bz,
                        age = age
                    };

                    upcomingBirthdays.Add(output);
                }

                // 获取门店名称
                if (upcomingBirthdays.Any())
                {
                    var storeIds = upcomingBirthdays.Select(x => x.gsmd).Distinct().Where(x => !string.IsNullOrEmpty(x)).ToList();
                    var stores = await _db.Queryable<LqMdxxEntity>().Where(s => storeIds.Contains(s.Id)).ToListAsync();
                    
                    foreach (var item in upcomingBirthdays)
                    {
                        if (!string.IsNullOrEmpty(item.gsmd))
                        {
                            var store = stores.FirstOrDefault(s => s.Id == item.gsmd);
                            item.gsmdName = store?.Dm ?? "";
                        }
                    }
                }

                // 按生日日期排序
                upcomingBirthdays = upcomingBirthdays.OrderBy(x => x.birthdayFullDate).ToList();

                return new
                {
                    code = 200,
                    msg = "获取成功",
                    data = upcomingBirthdays
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取会员生日列表失败");
                return new
                {
                    code = 500,
                    msg = $"获取失败：{ex.Message}"
                };
            }
        }

        /// <summary>
        /// 获取消费等级名称
        /// </summary>
        private string GetConsumeLevelName(int level)
        {
            return level switch
            {
                0 => "D级会员",
                1 => "C级会员",
                2 => "B级会员",
                3 => "A级会员",
                4 => "A+级会员",
                5 => "A++级会员",
                _ => "D级会员"
            };
        }

        #endregion

    }
}