using NCC.Common.Core.Manager;
using NCC.Common.Enum;
using NCC.Common.Extension;
using NCC.Common.Filter;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.FriendlyException;
using NCC.Extend.Interfaces.LqKhxx;
using Mapster;
using Microsoft.AspNetCore.Mvc;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using NCC.Extend.Entitys.lq_khxx;
using NCC.Extend.Entitys.Dto.LqKhxx;
using NCC.Extend.Entitys.lq_kd_kdjlb;
using NCC.Extend.Entitys.lq_kd_pxmx;
using NCC.Extend.Entitys.lq_xh_hyhk;
using NCC.Extend.Entitys.lq_xh_pxmx;
using NCC.Extend.Entitys.lq_hytk_hytk;
using NCC.Extend.Entitys.lq_hytk_mx;
using Yitter.IdGenerator;
using NCC.Common.Helper;
using NCC.JsonSerialization;
using NCC.Common.Model.NPOI;
using NCC.Common.Configuration;
using NCC.DataEncryption;
using NCC.ClayObject;
using Microsoft.Extensions.Logging;
using NCC.Extend.Entitys.Dto.LqXhHyhk;

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>();
            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> queryZjdlsj = input.zjdlsj != null ? input.zjdlsj.Split(',').ToObeject<List<string>>() : null;
            DateTime? startZjdlsj = queryZjdlsj != null ? Ext.GetDateTime(queryZjdlsj.First()) : null;
            DateTime? endZjdlsj = queryZjdlsj != null ? Ext.GetDateTime(queryZjdlsj.Last()) : null;
            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> queryScdd = input.scdd != null ? input.scdd.Split(',').ToObeject<List<string>>() : null;
            DateTime? startScdd = queryScdd != null ? Ext.GetDateTime(queryScdd.First()) : null;
            DateTime? endScdd = queryScdd != null ? Ext.GetDateTime(queryScdd.Last()) : null;
            List<string> queryZjdd = input.zjdd != null ? input.zjdd.Split(',').ToObeject<List<string>>() : null;
            DateTime? startZjdd = queryZjdd != null ? Ext.GetDateTime(queryZjdd.First()) : null;
            DateTime? endZjdd = queryZjdd != null ? Ext.GetDateTime(queryZjdd.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>()
                .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.gzhzt), p => p.Gzhzt.Equals(input.gzhzt))
                .WhereIF(!string.IsNullOrEmpty(input.wxnc), p => p.Wxnc.Contains(input.wxnc))
                .WhereIF(!string.IsNullOrEmpty(input.wxxcxzt), p => p.Wxxcxzt.Equals(input.wxxcxzt))
                //   .WhereIF(queryZjdlsj != null, p => p.Zjdlsj >= new DateTime(startZjdlsj.ToDate().Year, startZjdlsj.ToDate().Month, startZjdlsj.ToDate().Day, 0, 0, 0))
                //  .WhereIF(queryZjdlsj != null, p => p.Zjdlsj <= new DateTime(endZjdlsj.ToDate().Year, endZjdlsj.ToDate().Month, endZjdlsj.ToDate().Day, 23, 59, 59))
                .WhereIF(!string.IsNullOrEmpty(input.khmqgs), p => p.Khmqgs.Equals(input.khmqgs))
                .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.fzgw), p => p.Fzgw.Contains(input.fzgw))
                .WhereIF(!string.IsNullOrEmpty(input.mrs), p => p.Mrs.Contains(input.mrs))
                .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(queryScdd != null, p => p.Scdd >= new DateTime(startScdd.ToDate().Year, startScdd.ToDate().Month, startScdd.ToDate().Day, 0, 0, 0))
                .WhereIF(queryScdd != null, p => p.Scdd <= new DateTime(endScdd.ToDate().Year, endScdd.ToDate().Month, endScdd.ToDate().Day, 23, 59, 59))
                //   .WhereIF(queryZjdd != null, p => p.Zjdd >= new DateTime(startZjdd.ToDate().Year, startZjdd.ToDate().Month, startZjdd.ToDate().Day, 0, 0, 0))
                //     .WhereIF(queryZjdd != null, p => p.Zjdd <= new DateTime(endZjdd.ToDate().Year, endZjdd.ToDate().Month, endZjdd.ToDate().Day, 23, 59, 59))
                .WhereIF(!string.IsNullOrEmpty(input.wddts), p => p.Wddts.Contains(input.wddts))
                .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))
                .WhereIF(!string.IsNullOrEmpty(input.ml), p => p.Ml.Contains(input.ml))
                .WhereIF(!string.IsNullOrEmpty(input.zjycfwry), p => p.Zjycfwry.Contains(input.zjycfwry))
                .WhereIF(!string.IsNullOrEmpty(input.qjddpc), p => p.Qjddpc.Contains(input.qjddpc))
                .WhereIF(!string.IsNullOrEmpty(input.qjfwdc), p => p.Qjfwdc.Contains(input.qjfwdc))
                .WhereIF(!string.IsNullOrEmpty(input.qjxjxf), p => p.Qjxjxf.Contains(input.qjxjxf))
                .WhereIF(!string.IsNullOrEmpty(input.qjxmhk), p => p.Qjxmhk.Contains(input.qjxmhk))
                .WhereIF(!string.IsNullOrEmpty(input.qjczxh), p => p.Qjczxh.Contains(input.qjczxh))
                .WhereIF(!string.IsNullOrEmpty(input.sycz), p => p.Sycz.Equals(input.sycz))
                .WhereIF(!string.IsNullOrEmpty(input.syjf), p => p.Syjf.Equals(input.syjf))
                .WhereIF(!string.IsNullOrEmpty(input.sypxje), p => p.Sypxje.Equals(input.sypxje))
                .WhereIF(!string.IsNullOrEmpty(input.sytcje), p => p.Sytcje.Equals(input.sytcje))
                .WhereIF(!string.IsNullOrEmpty(input.ljxmhkje), p => p.Ljxmhkje.Equals(input.ljxmhkje))
                .WhereIF(!string.IsNullOrEmpty(input.ljczxh), p => p.Ljczxh.Equals(input.ljczxh))
                .WhereIF(!string.IsNullOrEmpty(input.ljxfcs), p => p.Ljxfcs.Equals(input.ljxfcs))
                .WhereIF(!string.IsNullOrEmpty(input.ljfwcs), p => p.Ljfwcs.Equals(input.ljfwcs))
                .WhereIF(!string.IsNullOrEmpty(input.ljxjxfje), p => p.Ljxjxfje.Equals(input.ljxjxfje))
                .Select(it => new LqKhxxListOutput
                {
                    id = it.Id,
                    khmc = it.Khmc,
                    sjh = it.Sjh,
                    dah = it.Dah,
                    xb = it.Xb,
                    gzhzt = it.Gzhzt,
                    wxnc = it.Wxnc,
                    wxxcxzt = it.Wxxcxzt,
                    zjdlsj = it.Zjdlsj,
                    khmqgs = it.Khmqgs,
                    gsmd = it.Gsmd,
                    zcsj = it.Zcsj,
                    khlx = it.Khlx,
                    khjd = it.Khjd,
                    khxf = it.Khxf,
                    xfpc = it.Xfpc,
                    tjr = it.Tjr,
                    fzgw = it.Fzgw,
                    mrs = it.Mrs,
                    jdqd = it.Jdqd,
                    lxdz = it.Lxdz,
                    bz = it.Bz,
                    scdd = it.Scdd,
                    zjdd = it.Zjdd,
                    wddts = it.Wddts,
                    yanglsr = it.Yanglsr,
                    yinlsr = it.Yinlsr,
                    ml = it.Ml,
                    zjycfwry = it.Zjycfwry,
                    qjddpc = it.Qjddpc,
                    qjfwdc = it.Qjfwdc,
                    qjxjxf = it.Qjxjxf,
                    qjxmhk = it.Qjxmhk,
                    qjczxh = it.Qjczxh,
                    sycz = it.Sycz,
                    syjf = it.Syjf,
                    sypxje = it.Sypxje,
                    sytcje = it.Sytcje,
                    ljxmhkje = it.Ljxmhkje,
                    ljczxh = it.Ljczxh,
                    ljxfcs = it.Ljxfcs,
                    ljfwcs = it.Ljfwcs,
                    ljxjxfje = it.Ljxjxfje,
                }).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 entity = input.Adapt<LqKhxxEntity>();
            entity.Id = YitIdHelper.NextId().ToString();
            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> queryZjdlsj = input.zjdlsj != null ? input.zjdlsj.Split(',').ToObeject<List<string>>() : null;
            DateTime? startZjdlsj = queryZjdlsj != null ? Ext.GetDateTime(queryZjdlsj.First()) : null;
            DateTime? endZjdlsj = queryZjdlsj != null ? Ext.GetDateTime(queryZjdlsj.Last()) : null;
            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> queryScdd = input.scdd != null ? input.scdd.Split(',').ToObeject<List<string>>() : null;
            DateTime? startScdd = queryScdd != null ? Ext.GetDateTime(queryScdd.First()) : null;
            DateTime? endScdd = queryScdd != null ? Ext.GetDateTime(queryScdd.Last()) : null;
            List<string> queryZjdd = input.zjdd != null ? input.zjdd.Split(',').ToObeject<List<string>>() : null;
            DateTime? startZjdd = queryZjdd != null ? Ext.GetDateTime(queryZjdd.First()) : null;
            DateTime? endZjdd = queryZjdd != null ? Ext.GetDateTime(queryZjdd.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>()
                .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.gzhzt), p => p.Gzhzt.Equals(input.gzhzt))
                .WhereIF(!string.IsNullOrEmpty(input.wxnc), p => p.Wxnc.Contains(input.wxnc))
                .WhereIF(!string.IsNullOrEmpty(input.wxxcxzt), p => p.Wxxcxzt.Equals(input.wxxcxzt))
                //       .WhereIF(queryZjdlsj != null, p => p.Zjdlsj >= new DateTime(startZjdlsj.ToDate().Year, startZjdlsj.ToDate().Month, startZjdlsj.ToDate().Day, 0, 0, 0))
                //    .WhereIF(queryZjdlsj != null, p => p.Zjdlsj <= new DateTime(endZjdlsj.ToDate().Year, endZjdlsj.ToDate().Month, endZjdlsj.ToDate().Day, 23, 59, 59))
                .WhereIF(!string.IsNullOrEmpty(input.khmqgs), p => p.Khmqgs.Equals(input.khmqgs))
                .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.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.fzgw), p => p.Fzgw.Contains(input.fzgw))
                .WhereIF(!string.IsNullOrEmpty(input.mrs), p => p.Mrs.Contains(input.mrs))
                .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(queryScdd != null, p => p.Scdd >= new DateTime(startScdd.ToDate().Year, startScdd.ToDate().Month, startScdd.ToDate().Day, 0, 0, 0))
                //   .WhereIF(queryScdd != null, p => p.Scdd <= new DateTime(endScdd.ToDate().Year, endScdd.ToDate().Month, endScdd.ToDate().Day, 23, 59, 59))
                //    .WhereIF(queryZjdd != null, p => p.Zjdd >= new DateTime(startZjdd.ToDate().Year, startZjdd.ToDate().Month, startZjdd.ToDate().Day, 0, 0, 0))
                //    .WhereIF(queryZjdd != null, p => p.Zjdd <= new DateTime(endZjdd.ToDate().Year, endZjdd.ToDate().Month, endZjdd.ToDate().Day, 23, 59, 59))
                .WhereIF(!string.IsNullOrEmpty(input.wddts), p => p.Wddts.Contains(input.wddts))
                .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))
                .WhereIF(!string.IsNullOrEmpty(input.ml), p => p.Ml.Contains(input.ml))
                .WhereIF(!string.IsNullOrEmpty(input.zjycfwry), p => p.Zjycfwry.Contains(input.zjycfwry))
                .WhereIF(!string.IsNullOrEmpty(input.qjddpc), p => p.Qjddpc.Contains(input.qjddpc))
                .WhereIF(!string.IsNullOrEmpty(input.qjfwdc), p => p.Qjfwdc.Contains(input.qjfwdc))
                .WhereIF(!string.IsNullOrEmpty(input.qjxjxf), p => p.Qjxjxf.Contains(input.qjxjxf))
                .WhereIF(!string.IsNullOrEmpty(input.qjxmhk), p => p.Qjxmhk.Contains(input.qjxmhk))
                .WhereIF(!string.IsNullOrEmpty(input.qjczxh), p => p.Qjczxh.Contains(input.qjczxh))
                .WhereIF(!string.IsNullOrEmpty(input.sycz), p => p.Sycz.Equals(input.sycz))
                .WhereIF(!string.IsNullOrEmpty(input.syjf), p => p.Syjf.Equals(input.syjf))
                .WhereIF(!string.IsNullOrEmpty(input.sypxje), p => p.Sypxje.Equals(input.sypxje))
                .WhereIF(!string.IsNullOrEmpty(input.sytcje), p => p.Sytcje.Equals(input.sytcje))
                .WhereIF(!string.IsNullOrEmpty(input.ljxmhkje), p => p.Ljxmhkje.Equals(input.ljxmhkje))
                .WhereIF(!string.IsNullOrEmpty(input.ljczxh), p => p.Ljczxh.Equals(input.ljczxh))
                .WhereIF(!string.IsNullOrEmpty(input.ljxfcs), p => p.Ljxfcs.Equals(input.ljxfcs))
                .WhereIF(!string.IsNullOrEmpty(input.ljfwcs), p => p.Ljfwcs.Equals(input.ljfwcs))
                .WhereIF(!string.IsNullOrEmpty(input.ljxjxfje), p => p.Ljxjxfje.Equals(input.ljxjxfje))
                .Select(it => new LqKhxxListOutput
                {
                    id = it.Id,
                    khmc = it.Khmc,
                    sjh = it.Sjh,
                    dah = it.Dah,
                    xb = it.Xb,
                    gzhzt = it.Gzhzt,
                    wxnc = it.Wxnc,
                    wxxcxzt = it.Wxxcxzt,
                    zjdlsj = it.Zjdlsj,
                    khmqgs = it.Khmqgs,
                    gsmd = it.Gsmd,
                    zcsj = it.Zcsj,
                    khlx = it.Khlx,
                    khjd = it.Khjd,
                    khxf = it.Khxf,
                    xfpc = it.Xfpc,
                    tjr = it.Tjr,
                    fzgw = it.Fzgw,
                    mrs = it.Mrs,
                    jdqd = it.Jdqd,
                    lxdz = it.Lxdz,
                    bz = it.Bz,
                    scdd = it.Scdd,
                    zjdd = it.Zjdd,
                    wddts = it.Wddts,
                    yanglsr = it.Yanglsr,
                    yinlsr = it.Yinlsr,
                    ml = it.Ml,
                    zjycfwry = it.Zjycfwry,
                    qjddpc = it.Qjddpc,
                    qjfwdc = it.Qjfwdc,
                    qjxjxf = it.Qjxjxf,
                    qjxmhk = it.Qjxmhk,
                    qjczxh = it.Qjczxh,
                    sycz = it.Sycz,
                    syjf = it.Syjf,
                    sypxje = it.Sypxje,
                    sytcje = it.Sytcje,
                    ljxmhkje = it.Ljxmhkje,
                    ljczxh = it.Ljczxh,
                    ljxfcs = it.Ljxfcs,
                    ljfwcs = it.Ljfwcs,
                    ljxjxfje = it.Ljxjxfje,
                }).MergeTable().OrderBy(sidx + " " + input.sort).ToListAsync();
            return data;
        }
        #endregion
       
        #region 导出客户资料            
        /// <summary>
        /// 导出客户资料
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("Actions/Export")]
        public async Task<dynamic> Export([FromQuery] 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\":\"scdd\"},{\"value\":\"最近到店\",\"field\":\"zjdd\"},{\"value\":\"未到店天数\",\"field\":\"wddts\"},{\"value\":\"阳历生日\",\"field\":\"yanglsr\"},{\"value\":\"阴历生日\",\"field\":\"yinlsr\"},{\"value\":\"年龄\",\"field\":\"ml\"},{\"value\":\"最近服务人员\",\"field\":\"zjycfwry\"},{\"value\":\"期间到店频次\",\"field\":\"qjddpc\"},{\"value\":\"期间服务单次\",\"field\":\"qjfwdc\"},{\"value\":\"期间现金消费\",\"field\":\"qjxjxf\"},{\"value\":\"期间项目耗卡\",\"field\":\"qjxmhk\"},{\"value\":\"期间储值消耗\",\"field\":\"qjczxh\"},{\"value\":\"剩余储值\",\"field\":\"sycz\"},{\"value\":\"剩余积分\",\"field\":\"syjf\"},{\"value\":\"剩余品项金额\",\"field\":\"sypxje\"},{\"value\":\"剩余套餐金额\",\"field\":\"sytcje\"},{\"value\":\"项目耗卡金额\",\"field\":\"ljxmhkje\"},{\"value\":\"累计储值消耗\",\"field\":\"ljczxh\"},{\"value\":\"累计消费次数\",\"field\":\"ljxfcs\"},{\"value\":\"累计服务次数\",\"field\":\"ljfwcs\"},{\"value\":\"现金消费金额\",\"field\":\"ljxjxfje\"},]".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>
        /// 批量删除客户资料
        /// </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>();
            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
            {
                _logger.LogInformation("开始查询会员剩余品项，会员ID：{MemberId}", memberId);

                // 1. 查询会员基本信息
                var memberInfo = await _db.Queryable<LqKhxxEntity>()
                    .Where(x => x.Id == memberId)
                    .FirstAsync();

                if (memberInfo == null)
                {
                    _logger.LogWarning("会员不存在，会员ID：{MemberId}", memberId);
                    throw NCCException.Oh(ErrorCode.COM1005, "会员不存在");
                }

                _logger.LogInformation("会员信息查询成功，会员姓名：{MemberName}", memberInfo.Khmc);

                // 2. 查询开单记录ID列表
                _logger.LogInformation("开始查询开单记录ID列表");
                var orderIds = await _db.Queryable<LqKdKdjlbEntity>()
                    .Where(kd => kd.Kdhy == memberId)
                    .Select(kd => kd.Id)
                    .ToListAsync();

                _logger.LogInformation("开单记录ID列表查询成功，数量：{Count}", orderIds.Count);

                // 3. 查询开单品项统计（按品项ID、名称、价格和来源类型分组）
                _logger.LogInformation("开始查询开单品项统计");
                var purchasedItems = new List<dynamic>();
                if (orderIds.Any())
                {
                    var tempPurchasedItems = await _db.Queryable<LqKdPxmxEntity>()
                        .Where(px => orderIds.Contains(px.Glkdbh))
                        .GroupBy(px => new { px.Px, px.Pxmc, px.Pxjg, px.SourceType })
                        .Select(px => new
                        {
                            ItemId = px.Px,
                            ItemName = px.Pxmc,
                            ItemPrice = px.Pxjg,
                            SourceType = SqlFunc.IsNull(px.SourceType, "未知"),
                            TotalPurchased = SqlFunc.AggregateSum(SqlFunc.ToDecimal(px.ProjectNumber))
                        })
                        .ToListAsync();

                    purchasedItems = tempPurchasedItems.Cast<dynamic>().ToList();
                    _logger.LogInformation("开单品项统计查询成功，数量：{Count}", purchasedItems.Count);
                    
                    // 调试：输出前几条记录查看SourceType字段
                    foreach (var item in purchasedItems.Take(3))
                    {
                        _logger.LogInformation($"调试 - 品项ID: {item.ItemId}, 品项名称: {item.ItemName}, 来源类型: {item.SourceType}");
                    }
                }

                // 4. 查询耗卡记录ID列表
                _logger.LogInformation("开始查询耗卡记录ID列表");
                var consumeIds = await _db.Queryable<LqXhHyhkEntity>()
                    .Where(hk => hk.Hy == memberId)
                    .Select(hk => hk.Id)
                    .ToListAsync();

                _logger.LogInformation("耗卡记录ID列表查询成功，数量：{Count}", consumeIds.Count);

                // 5. 查询耗卡品项统计（按品项ID和来源类型分组）
                _logger.LogInformation("开始查询耗卡品项统计");
                var consumedItems = new List<dynamic>();
                if (consumeIds.Any())
                {
                    var tempConsumedItems = await _db.Queryable<LqXhPxmxEntity>()
                        .Where(px => consumeIds.Contains(px.Glkdbh))
                        .GroupBy(px => new { px.Px, px.SourceType })
                        .Select(px => new
                        {
                            ItemId = px.Px,
                            SourceType = SqlFunc.IsNull(px.SourceType, "未知"),
                            ConsumedCount = SqlFunc.AggregateSum(SqlFunc.ToDecimal(px.ProjectNumber))
                        })
                        .ToListAsync();

                    consumedItems = tempConsumedItems.Cast<dynamic>().ToList();
                    _logger.LogInformation("耗卡品项统计查询成功，数量：{Count}", consumedItems.Count);
                }

                // 6. 查询退卡记录ID列表
                _logger.LogInformation("开始查询退卡记录ID列表");
                var refundIds = await _db.Queryable<LqHytkHytkEntity>()
                    .Where(tk => tk.Hy == memberId && tk.F_DeleteMark != 1)
                    .Select(tk => tk.Id)
                    .ToListAsync();

                _logger.LogInformation("退卡记录ID列表查询成功，数量：{Count}", refundIds.Count);

                // 7. 查询退卡品项统计（按品项ID和来源类型分组）
                _logger.LogInformation("开始查询退卡品项统计");
                var refundedItems = new List<dynamic>();
                if (refundIds.Any())
                {
                    var tempRefundedItems = await _db.Queryable<LqHytkMxEntity>()
                        .Where(px => refundIds.Contains(px.Gltkbh) && px.F_DeleteMark != 1)
                        .GroupBy(px => new { px.Px, px.F_SourceType })
                        .Select(px => new
                        {
                            ItemId = px.Px,
                            SourceType = SqlFunc.IsNull(px.F_SourceType, "未知"),
                            RefundedCount = SqlFunc.AggregateSum(SqlFunc.ToDecimal(px.F_ProjectNumber))
                        })
                        .ToListAsync();

                    refundedItems = tempRefundedItems.Cast<dynamic>().ToList();
                    _logger.LogInformation("退卡品项统计查询成功，数量：{Count}", refundedItems.Count);
                }

                // 8. 合并计算剩余品项（按品项ID和来源类型匹配）
                _logger.LogInformation("开始合并计算剩余品项");
                var remainingItems = new List<RemainingItemInfo>();

                // 处理有购买记录的品项
                foreach (var purchased in purchasedItems)
                {
                    // 按品项ID和来源类型匹配耗卡记录
                    var consumed = consumedItems.FirstOrDefault(x => 
                        x.ItemId == purchased.ItemId && 
                        x.SourceType == purchased.SourceType);
                    var consumedCount = consumed?.ConsumedCount ?? 0;

                    // 按品项ID和来源类型匹配退卡记录
                    var refunded = refundedItems.FirstOrDefault(x => 
                        x.ItemId == purchased.ItemId && 
                        x.SourceType == purchased.SourceType);
                    var refundedCount = refunded?.RefundedCount ?? 0;

                    // 计算剩余数量：购买数量 - 消费数量 - 退卡数量
                    var remainingCount = purchased.TotalPurchased - consumedCount - refundedCount;

                    if (remainingCount > 0)
                    {
                        remainingItems.Add(new RemainingItemInfo
                        {
                            ItemId = purchased.ItemId,
                            ItemName = purchased.ItemName,
                            ItemPrice = purchased.ItemPrice,
                            SourceType = purchased.SourceType?.ToString() ?? "未知",
                            TotalPurchased = purchased.TotalPurchased,
                            ConsumedCount = consumedCount,
                            RefundedCount = refundedCount,
                            RemainingCount = remainingCount
                        });
                    }
                }

                // 7. 按剩余数量降序排列
                remainingItems = remainingItems.OrderByDescending(x => x.RemainingCount).ToList();

                _logger.LogInformation("剩余品项计算完成，数量：{Count}", remainingItems.Count);

                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
    }
}