using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using Mapster; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Logging; using NCC.ClayObject; using NCC.Common.Configuration; using NCC.Common.Core.Manager; using NCC.Common.Enum; using NCC.Common.Extension; using NCC.Common.Filter; using NCC.Common.Helper; using NCC.Common.Model.NPOI; using NCC.DataEncryption; using NCC.Dependency; using NCC.DynamicApiController; using NCC.Extend.Entitys.Dto.Common; using NCC.Extend.Entitys.Dto.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 { /// /// 绿纤客户（会员）资料服务 /// [ApiDescriptionSettings(Tag = "绿纤客户（会员）资料服务", Name = "LqKhxx", Order = 200)] [Route("api/Extend/[controller]")] public class LqKhxxService : ILqKhxxService, IDynamicApiController, ITransient { private readonly ISqlSugarRepository _lqKhxxRepository; private readonly SqlSugarScope _db; private readonly IUserManager _userManager; private readonly ILogger _logger; /// /// 初始化一个类型的新实例 /// public LqKhxxService(ISqlSugarRepository lqKhxxRepository, IUserManager userManager, ILogger logger) { _lqKhxxRepository = lqKhxxRepository; _db = _lqKhxxRepository.Context; _userManager = userManager; _logger = logger; } #region 客户资料 /// /// 获取客户资料 /// /// 参数 /// [HttpGet("{id}")] public async Task GetInfo(string id) { var entity = await _db.Queryable().FirstAsync(p => p.Id == id); var output = entity.Adapt(); if (!string.IsNullOrEmpty(entity.ExpandUser)) { var expandUser = await _db.Queryable().Where(u => u.Id == entity.ExpandUser).FirstAsync(); output.expandUserName = expandUser?.RealName ?? ""; } if (!string.IsNullOrEmpty(entity.MainHealthUser)) { var mainHealthUser = await _db.Queryable().Where(u => u.Id == entity.MainHealthUser).FirstAsync(); output.mainHealthUserName = mainHealthUser?.RealName ?? ""; } if (!string.IsNullOrEmpty(entity.SubHealthUser)) { var subHealthUser = await _db.Queryable().Where(u => u.Id == entity.SubHealthUser).FirstAsync(); output.subHealthUserName = subHealthUser?.RealName ?? ""; } if (!string.IsNullOrEmpty(entity.Tjr)) { var tjr = await _db.Queryable().Where(u => u.Id == entity.Tjr).FirstAsync(); output.tjrName = tjr?.RealName ?? ""; } return output; } #endregion #region 客户资料列表 /// /// 获取客户资料列表 /// /// 请求参数 /// [HttpGet("")] public async Task GetList([FromQuery] LqKhxxListQueryInput input) { var sidx = input.sidx == null ? "id" : input.sidx; List queryZcsj = input.zcsj != null ? input.zcsj.Split(',').ToObeject>() : null; DateTime? startZcsj = queryZcsj != null ? Ext.GetDateTime(queryZcsj.First()) : null; DateTime? endZcsj = queryZcsj != null ? Ext.GetDateTime(queryZcsj.Last()) : null; var data = await _db.Queryable() .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.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(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().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().Where(u => u.Id == it.ExpandUser).Select(u => u.RealName), mainHealthUserName = SqlFunc.Subqueryable().Where(u => u.Id == it.MainHealthUser).Select(u => u.RealName), subHealthUserName = SqlFunc.Subqueryable().Where(u => u.Id == it.SubHealthUser).Select(u => u.RealName), tjrName = SqlFunc.Subqueryable().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 }) .MergeTable() .OrderBy(sidx + " " + input.sort) .ToPagedListAsync(input.currentPage, input.pageSize); return PageResult.SqlSugarPageResult(data); } #endregion #region 新建客户资料 /// /// 新建客户资料 /// /// 参数 /// [HttpPost("")] public async Task Create([FromBody] LqKhxxCrInput input) { var userInfo = await _userManager.GetUserInfo(); //判断手机号是否存在 var user = _db.Queryable().Where(u => u.Sjh == input.sjh).Any(); if (user) { throw NCCException.Oh("该会员已存在"); } var entity = input.Adapt(); 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 获取客户资料无分页列表 /// /// 获取客户资料无分页列表 /// /// 请求参数 /// [NonAction] public async Task GetNoPagingList([FromQuery] LqKhxxListQueryInput input) { var sidx = input.sidx == null ? "id" : input.sidx; List queryZcsj = input.zcsj != null ? input.zcsj.Split(',').ToObeject>() : null; DateTime? startZcsj = queryZcsj != null ? Ext.GetDateTime(queryZcsj.First()) : null; DateTime? endZcsj = queryZcsj != null ? Ext.GetDateTime(queryZcsj.Last()) : null; List queryYanglsr = input.yanglsr != null ? input.yanglsr.Split(',').ToObeject>() : null; DateTime? startYanglsr = queryYanglsr != null ? Ext.GetDateTime(queryYanglsr.First()) : null; DateTime? endYanglsr = queryYanglsr != null ? Ext.GetDateTime(queryYanglsr.Last()) : null; List queryYinlsr = input.yinlsr != null ? input.yinlsr.Split(',').ToObeject>() : null; DateTime? startYinlsr = queryYinlsr != null ? Ext.GetDateTime(queryYinlsr.First()) : null; DateTime? endYinlsr = queryYinlsr != null ? Ext.GetDateTime(queryYinlsr.Last()) : null; var data = await _db.Queryable() .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.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(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, 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, }) .MergeTable() .OrderBy(sidx + " " + input.sort) .ToListAsync(); return data; } #endregion #region 获取线索池客户列表 /// /// 获取线索池客户列表 /// /// 查询参数 /// 线索池客户列表 [HttpGet("leads-pool")] public async Task> GetLeadsPoolList([FromQuery] LqKhxxListQueryInput input) { var sidx = input.sidx == null ? "id" : input.sidx; List queryZcsj = input.zcsj != null ? input.zcsj.Split(',').ToObeject>() : null; DateTime? startZcsj = queryZcsj != null ? Ext.GetDateTime(queryZcsj.First()) : null; DateTime? endZcsj = queryZcsj != null ? Ext.GetDateTime(queryZcsj.Last()) : null; List queryYanglsr = input.yanglsr != null ? input.yanglsr.Split(',').ToObeject>() : null; DateTime? startYanglsr = queryYanglsr != null ? Ext.GetDateTime(queryYanglsr.First()) : null; DateTime? endYanglsr = queryYanglsr != null ? Ext.GetDateTime(queryYanglsr.Last()) : null; List queryYinlsr = input.yinlsr != null ? input.yinlsr.Split(',').ToObeject>() : null; DateTime? startYinlsr = queryYinlsr != null ? Ext.GetDateTime(queryYinlsr.First()) : null; DateTime? endYinlsr = queryYinlsr != null ? Ext.GetDateTime(queryYinlsr.Last()) : null; var data = await _db.Queryable() .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, 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, }) .MergeTable() .OrderBy(sidx + " " + input.sort) .ToPagedListAsync(input.currentPage, input.pageSize); return PageResult.SqlSugarPageResult(data); } #endregion #region 获取客户订单类型（首单/升单） /// /// 获取客户订单类型（首单/升单） /// /// 客户ID /// 客户订单类型信息 [HttpGet("order-type/{customerId}")] public async Task GetCustomerOrderType(string customerId) { if (string.IsNullOrEmpty(customerId)) { throw NCCException.Oh("客户ID不能为空"); } // 查询客户是否存在 var customerExists = await _db.Queryable().Where(c => c.Id == customerId).AnyAsync(); if (!customerExists) { throw NCCException.Oh("客户不存在"); } // 查询开单记录数量 var orderCount = await _db.Queryable().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 判断客户是否开过指定类型的开单 /// /// 判断客户是否开过生美的开单 /// /// 会员ID /// 1表示有，0表示没有 [HttpGet("has-life-beauty-billing/{memberId}")] public async Task HasLifeBeautyBilling(string memberId) { if (string.IsNullOrEmpty(memberId)) { throw NCCException.Oh("会员ID不能为空"); } // 查询该会员是否有开过生美品项的开单 var hasBilling = await _db.Queryable() .InnerJoin((kdjlb, pxmx) => kdjlb.Id == pxmx.Glkdbh) .InnerJoin((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; } /// /// 判断客户是否开过科美的开单 /// /// 会员ID /// 1表示有，0表示没有 [HttpGet("has-tech-beauty-billing/{memberId}")] public async Task HasTechBeautyBilling(string memberId) { if (string.IsNullOrEmpty(memberId)) { throw NCCException.Oh("会员ID不能为空"); } // 查询该会员是否有开过科美品项的开单 var hasBilling = await _db.Queryable() .InnerJoin((kdjlb, pxmx) => kdjlb.Id == pxmx.Glkdbh) .InnerJoin((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; } /// /// 判断客户是否开过医美的开单 /// /// 会员ID /// 1表示有，0表示没有 [HttpGet("has-medical-beauty-billing/{memberId}")] public async Task HasMedicalBeautyBilling(string memberId) { if (string.IsNullOrEmpty(memberId)) { throw NCCException.Oh("会员ID不能为空"); } // 查询该会员是否有开过医美品项的开单 var hasBilling = await _db.Queryable() .InnerJoin((kdjlb, pxmx) => kdjlb.Id == pxmx.Glkdbh) .InnerJoin((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 导出客户资料 /// /// 导出客户资料 /// /// 请求参数 /// [HttpGet("Actions/Export")] public async Task Export([FromQuery] LqKhxxListQueryInput input) { var userInfo = await _userManager.GetUserInfo(); var exportData = new List(); if (input.dataType == 0) { var data = Clay.Object(await this.GetList(input)); exportData = data.Solidify>().list; } else { exportData = await this.GetNoPagingList(input); } List 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\"},]".ToList(); ExcelConfig excelconfig = new ExcelConfig(); excelconfig.FileName = "客户资料.xls"; excelconfig.HeadFont = "微软雅黑"; excelconfig.HeadPoint = 10; excelconfig.IsAllSizeColumn = true; excelconfig.ColumnModel = new List(); List 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.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 批量删除客户资料 /// /// 批量删除客户资料 /// /// 主键数组 /// [HttpPost("batchRemove")] public async Task BatchRemove([FromBody] List ids) { var entitys = await _db.Queryable().In(it => it.Id, ids).ToListAsync(); if (entitys.Count > 0) { try { //开启事务 _db.BeginTran(); //批量删除客户资料 await _db.Deleteable().In(d => d.Id, ids).ExecuteCommandAsync(); //关闭事务 _db.CommitTran(); } catch (Exception) { //回滚事务 _db.RollbackTran(); throw NCCException.Oh(ErrorCode.COM1002); } } } #endregion #region 更新客户资料 /// /// 更新客户资料 /// /// 主键 /// 参数 /// [HttpPut("{id}")] public async Task Update(string id, [FromBody] LqKhxxUpInput input) { var entity = input.Adapt(); // 处理客户消费字段：将数组转换为逗号分隔的字符串 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 删除客户资料 /// /// 删除客户资料 /// /// [HttpDelete("{id}")] public async Task Delete(string id) { var entity = await _db.Queryable().FirstAsync(p => p.Id == id); _ = entity ?? throw NCCException.Oh(ErrorCode.COM1005); var isOk = await _db.Deleteable().Where(d => d.Id == id).ExecuteCommandAsync(); if (!(isOk > 0)) throw NCCException.Oh(ErrorCode.COM1002); } #endregion #region 查询会员剩余品项 /// /// 查询会员剩余品项信息 /// /// /// 根据会员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正确分组且不重叠 /// - 退卡记录会减少剩余品项数量 /// /// 会员ID /// 会员剩余品项信息 /// 成功返回会员剩余品项信息 /// 参数错误，会员ID不能为空 /// 服务器错误或会员不存在 [HttpGet("GetMemberRemainingItems")] public async Task GetMemberRemainingItems(string memberId) { if (string.IsNullOrEmpty(memberId)) { throw NCCException.Oh(ErrorCode.COM1000, "会员ID不能为空"); } try { // 1. 查询会员基本信息 var memberInfo = await _db.Queryable().Where(x => x.Id == memberId).FirstAsync(); if (memberInfo == null) { _logger.LogWarning("会员不存在，会员ID：{MemberId}", memberId); throw NCCException.Oh("会员不存在"); } // 2. 分步查询，避免子查询问题 // 先查询基础品项数据 var baseItems = await _db.Queryable() .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() .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() .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() .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 获取会员类型枚举内容 /// /// 获取会员类型枚举内容 /// /// 扣款类型枚举列表 [HttpGet("deduct-types")] public List GetDeductTypes() { return Enum.GetValues() .Select(e => new EnumOutput { Value = (int)e, Name = e.ToString(), Description = e.GetDescription(), }) .ToList(); } #endregion #region 导入客户信息 /// /// 从"清理跨店的重复"表导入客户信息 /// /// /// 将"清理跨店的重复"表中的数据导入到客户表中，客户类型设置为"潜客" /// /// 导入规则： /// 1. 根据客户电话去重，避免重复导入 /// 2. 客户类型统一设置为"潜客" /// 3. 使用YitIdHelper生成客户ID /// 4. 根据门店ID设置归属门店 /// 5. 根据拓客员工设置拓客人员 /// /// 字段映射： /// - 客户姓名 -> khmc (客户名称) /// - 客户电话 -> sjh (手机号) /// - F_StoreId -> gsmd (归属门店) /// - 拓客员工 -> F_ExpandUser (拓客人员) /// - 日期 -> zcsj (注册时间) /// /// 导入结果 /// 导入成功 /// 服务器内部错误 [HttpPost("import-customers-from-cleanup")] public async Task ImportCustomersFromCleanup() { try { _db.BeginTran(); // 1. 查询"清理跨店的重复"表数据，按客户电话去重 var cleanupData = await _db.Ado.SqlQueryAsync(@" 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(); foreach (var item in cleanupData) { try { // 2. 检查客户是否已存在（根据手机号） string phoneNumber = (string)item.客户电话; var existingCustomer = await _db.Queryable() .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 获取会员品项信息 /// /// 获取所有会员的品项信息（分页） /// /// /// 查询所有会员的品项信息，包括购买品项、赠送品项、体验品项、已消耗品项、已退卡品项、已储扣品项 /// /// 示例请求： /// ```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: 总金额（单价 × 次数） /// /// 查询参数 /// 会员品项信息列表（分页） /// 查询成功，返回会员品项信息列表 /// 参数错误，如页码或页大小无效 /// 服务器错误，查询过程中发生异常 [HttpPost("get-member-item-info")] public async Task GetMemberItemInfo([FromBody] MemberItemInfoQueryInput input) { try { // 1. 构建会员查询条件 var memberQuery = _db.Queryable(); // 筛选条件 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等条件，需要通过开单表进行筛选 if (!string.IsNullOrEmpty(input.BillingStoreId) || !string.IsNullOrEmpty(input.BillingUserId) || !string.IsNullOrEmpty(input.PurchaseItemId) || !string.IsNullOrEmpty(input.GiftItemId) || !string.IsNullOrEmpty(input.ExperienceItemId)) { // 先查询符合条件的开单记录，获取会员ID列表 var billingQuery = _db.Queryable() .Where(x => x.IsEffective == StatusEnum.有效.GetHashCode()); if (!string.IsNullOrEmpty(input.BillingStoreId)) { billingQuery = billingQuery.Where(x => x.Djmd == input.BillingStoreId); } 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().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.SqlSugarPageResult(new SqlSugarPagedList { list = new List(), 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.SqlSugarPageResult(new SqlSugarPagedList { list = new List(), 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 { list = memberList.Cast().ToList(), pagination = new PagedModel { PageIndex = input.currentPage, PageSize = input.pageSize, Total = totalCount } }; if (memberList == null || !memberList.Any()) { return PageResult.SqlSugarPageResult(new SqlSugarPagedList { list = new List(), 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() .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(); if (billingIds.Any()) { var itemDetailsData = await _db.Queryable() .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().ToList(); } var itemDetailIds = itemDetails.Select(x => (string)x.Id).ToList(); // 5. 批量查询消耗品项 var consumedItems = new List(); if (itemDetailIds.Any()) { var consumedItemsData = await _db.Queryable() .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().ToList(); } // 6. 批量查询退卡品项 var refundedItems = new List(); if (itemDetailIds.Any()) { var refundedItemsData = await _db.Queryable() .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().ToList(); } // 7. 批量查询储扣品项 var deductedItems = new List(); if (billingIds.Any()) { var deductedItemsData = await _db.Queryable() .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().ToList(); } // 8. 批量查询门店信息 var storeIds = new List(); 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(); if (storeIds.Any()) { var storeList = await _db.Queryable() .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(); if (billingRecords != null && billingRecords.Any()) { userIds = billingRecords.Select(x => x.CreateUser) .Where(x => !string.IsNullOrEmpty(x)) .Distinct() .ToList(); } var users = new Dictionary(); if (userIds.Any()) { var userList = await _db.Queryable() .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(); 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.SqlSugarPageResult(new SqlSugarPagedList { 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 会员信息更新 /// /// 更新会员信息（单个会员） /// /// 会员ID /// [HttpPost("UpdateMemberInfo")] public async Task UpdateMemberInfo([FromBody] string memberId) { try { if (string.IsNullOrEmpty(memberId)) { throw NCCException.Oh("会员ID不能为空"); } var member = await _db.Queryable() .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}"); } } /// /// 批量更新所有会员信息（高性能版：使用SQL批量更新）【通过定时任务去执行，每天晚上执行一次】 /// /// [HttpPost("BatchUpdateMemberInfo")] [AllowAnonymous] public async Task BatchUpdateMemberInfo() { try { var startTime = DateTime.Now; _logger.LogInformation("开始批量更新会员信息（高性能SQL版）"); // 1. 获取所有有效会员ID var memberIds = await _db.Queryable() .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}"); } } /// /// 更新会员信息内部方法 /// /// 会员实体 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(); } /// /// 更新会员类型标识（生美/医美/科技部会员） /// private async Task UpdateMemberTypeFlags(LqKhxxEntity member) { // 查询该会员的所有有效开单品项明细 var billingItems = await _db.Queryable() .InnerJoin((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; } } /// /// 更新到店天数 /// private async Task UpdateVisitDays(LqKhxxEntity member) { // 查询该会员的所有有效耗卡记录，按天去重 var visitDates = await _db.Queryable() .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() .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() .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; } } /// /// 更新沉睡天数 /// 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; } /// /// 更新消费信息（开卡总金额、耗卡总金额、剩余权益总金额、消费等级） /// private async Task UpdateConsumeInfo(LqKhxxEntity member) { // 1. 计算开卡总金额（累计所有开单的实付业绩总和） var totalBillingAmount = await _db.Queryable().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().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().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 } }