using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Mapster;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using NCC.ClayObject;
using NCC.Code;
using NCC.Common.Configuration;
using NCC.Common.Core.Manager;
using NCC.Common.Enum;
using NCC.Common.Extension;
using NCC.Common.Filter;
using NCC.Common.Helper;
using NCC.Common.Model.NPOI;
using NCC.DataEncryption;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.Extend.Entitys.Dto.Common;
using NCC.Extend.Entitys.Dto.LqKhxx;
using NCC.Extend.Entitys.Dto.LqXhHyhk;
using NCC.Extend.Entitys.Enum;
using NCC.Extend.Entitys.lq_hytk_hytk;
using NCC.Extend.Entitys.lq_hytk_mx;
using NCC.Extend.Entitys.lq_kd_kdjlb;
using NCC.Extend.Entitys.lq_kd_pxmx;
using NCC.Extend.Entitys.lq_khxx;
using NCC.Extend.Entitys.lq_mdxx;
using NCC.Extend.Entitys.lq_xh_hyhk;
using NCC.Extend.Entitys.lq_xh_pxmx;
using NCC.Extend.Entitys.lq_xmzl;
using NCC.Extend.Interfaces.LqKhxx;
using NCC.Extend.Entitys.Config;
using NCC.System.Entitys.Permission;
using NCC.FriendlyException;
using NCC.JsonSerialization;
using SqlSugar;
using Yitter.IdGenerator;
using NCC.Extend.Entitys.lq_kd_deductinfo;
using Microsoft.AspNetCore.Authorization;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.Reflection;
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 客户资料
///
/// 阳历转农历（用于前端选择阳历后自动填入农历生日）
///
/// 阳历日期，格式 yyyy-MM-dd
/// 农历字符串，如 腊月十九
[HttpGet("solar-to-lunar")]
[AllowAnonymous]
public object SolarToLunar([FromQuery] string date)
{
if (string.IsNullOrEmpty(date) || !DateTime.TryParse(date, out var solarDate))
return new { code = 400, msg = "日期格式错误，请使用 yyyy-MM-dd" };
var yinlsr = SolarToLunarString(solarDate);
return new { code = 200, msg = "成功", data = new { yinlsr } };
}
///
/// 获取客户资料
///
/// 参数
///
[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.Gsmd))
{
var store = await _db.Queryable().Where(s => s.Id == entity.Gsmd).FirstAsync();
output.gsmdName = store?.Dm ?? "";
}
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;
// 处理沉睡天数范围
List querySleepDaysRange = input.SleepDaysRange != null ? input.SleepDaysRange.Split(',').ToObeject>() : null;
int? minSleepDays = null;
int? maxSleepDays = null;
if (querySleepDaysRange != null && querySleepDaysRange.Count >= 2)
{
int.TryParse(querySleepDaysRange[0], out int min);
int.TryParse(querySleepDaysRange[1], out int max);
minSleepDays = min;
maxSleepDays = max;
}
// 处理剩余权益范围
List queryRemainingRightsRange = input.RemainingRightsRange != null ? input.RemainingRightsRange.Split(',').ToObeject>() : null;
decimal? minRemainingRights = null;
decimal? maxRemainingRights = null;
if (queryRemainingRightsRange != null && queryRemainingRightsRange.Count >= 2)
{
decimal.TryParse(queryRemainingRightsRange[0], out decimal min);
decimal.TryParse(queryRemainingRightsRange[1], out decimal max);
minRemainingRights = min;
maxRemainingRights = max;
}
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.mainHealthUser), p => p.MainHealthUser.Contains(input.mainHealthUser))
.WhereIF(!string.IsNullOrEmpty(input.subHealthUser), p => p.SubHealthUser.Contains(input.subHealthUser))
.WhereIF(!string.IsNullOrEmpty(input.khlx), p => p.Khlx.Equals(input.khlx))
.WhereIF(!string.IsNullOrEmpty(input.khjd), p => p.Khjd.Equals(input.khjd))
.WhereIF(!string.IsNullOrEmpty(input.khxf), p => p.Khxf.Contains(input.khxf))
.WhereIF(!string.IsNullOrEmpty(input.xfpc), p => p.Xfpc.Equals(input.xfpc))
.WhereIF(!string.IsNullOrEmpty(input.tjr), p => p.Tjr.Contains(input.tjr))
.WhereIF(!string.IsNullOrEmpty(input.jdqd), p => p.Jdqd.Contains(input.jdqd))
.WhereIF(!string.IsNullOrEmpty(input.lxdz), p => p.Lxdz.Contains(input.lxdz))
.WhereIF(!string.IsNullOrEmpty(input.bz), p => p.Bz.Contains(input.bz))
.WhereIF(input.ConsumeLevel.HasValue, p => p.ConsumeLevel == input.ConsumeLevel.Value)
.WhereIF(minSleepDays.HasValue, p => p.SleepDays >= minSleepDays.Value)
.WhereIF(maxSleepDays.HasValue, p => p.SleepDays <= maxSleepDays.Value)
.WhereIF(minRemainingRights.HasValue, p => p.RemainingRightsAmount >= minRemainingRights.Value)
.WhereIF(maxRemainingRights.HasValue, p => p.RemainingRightsAmount <= maxRemainingRights.Value)
.WhereIF(queryZcsj != null, p => p.CreateTime >= new DateTime(startZcsj.ToDate().Year, startZcsj.ToDate().Month, startZcsj.ToDate().Day, 0, 0, 0))
.WhereIF(queryZcsj != null, p => p.CreateTime <= new DateTime(endZcsj.ToDate().Year, endZcsj.ToDate().Month, endZcsj.ToDate().Day, 23, 59, 59))
.Select(it => new LqKhxxListOutput
{
id = it.Id,
khmc = it.Khmc,
sjh = it.Sjh,
dah = it.Dah,
xb = it.Xb,
gsmd = it.Gsmd,
gsmdName = SqlFunc.Subqueryable().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,
birthdayType = it.BirthdayType,
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,
isEffective = it.IsEffective
})
.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;
// 阳历生日转农历：若填写了 yanglsr，自动转换并保存到 yinlsr（格式：腊月十九、四月廿一等，便于直接阅读）
if (input.yanglsr.HasValue)
{
entity.Yinlsr = SolarToLunarString(input.yanglsr.Value);
}
// 处理客户消费字段：将数组转换为逗号分隔的字符串
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 querySleepDaysRange = input.SleepDaysRange != null ? input.SleepDaysRange.Split(',').ToObeject>() : null;
int? minSleepDays = null;
int? maxSleepDays = null;
if (querySleepDaysRange != null && querySleepDaysRange.Count >= 2)
{
int.TryParse(querySleepDaysRange[0], out int min);
int.TryParse(querySleepDaysRange[1], out int max);
minSleepDays = min;
maxSleepDays = max;
}
// 处理剩余权益范围
List queryRemainingRightsRange = input.RemainingRightsRange != null ? input.RemainingRightsRange.Split(',').ToObeject>() : null;
decimal? minRemainingRights = null;
decimal? maxRemainingRights = null;
if (queryRemainingRightsRange != null && queryRemainingRightsRange.Count >= 2)
{
decimal.TryParse(queryRemainingRightsRange[0], out decimal min);
decimal.TryParse(queryRemainingRightsRange[1], out decimal max);
minRemainingRights = min;
maxRemainingRights = max;
}
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(!string.IsNullOrEmpty(input.mainHealthUser), p => p.MainHealthUser.Contains(input.mainHealthUser))
.WhereIF(!string.IsNullOrEmpty(input.subHealthUser), p => p.SubHealthUser.Contains(input.subHealthUser))
.WhereIF(queryZcsj != null, p => p.Zcsj >= new DateTime(startZcsj.ToDate().Year, startZcsj.ToDate().Month, startZcsj.ToDate().Day, 0, 0, 0))
.WhereIF(queryZcsj != null, p => p.Zcsj <= new DateTime(endZcsj.ToDate().Year, endZcsj.ToDate().Month, endZcsj.ToDate().Day, 23, 59, 59))
.WhereIF(!string.IsNullOrEmpty(input.khlx), p => p.Khlx.Equals(input.khlx))
.WhereIF(!string.IsNullOrEmpty(input.khjd), p => p.Khjd.Equals(input.khjd))
.WhereIF(!string.IsNullOrEmpty(input.khxf), p => p.Khxf.Equals(input.khxf))
.WhereIF(!string.IsNullOrEmpty(input.xfpc), p => p.Xfpc.Equals(input.xfpc))
.WhereIF(!string.IsNullOrEmpty(input.tjr), p => p.Tjr.Contains(input.tjr))
.WhereIF(!string.IsNullOrEmpty(input.jdqd), p => p.Jdqd.Contains(input.jdqd))
.WhereIF(!string.IsNullOrEmpty(input.lxdz), p => p.Lxdz.Contains(input.lxdz))
.WhereIF(!string.IsNullOrEmpty(input.bz), p => p.Bz.Contains(input.bz))
.WhereIF(input.ConsumeLevel.HasValue, p => p.ConsumeLevel == input.ConsumeLevel.Value)
.WhereIF(minSleepDays.HasValue, p => p.SleepDays >= minSleepDays.Value)
.WhereIF(maxSleepDays.HasValue, p => p.SleepDays <= maxSleepDays.Value)
.WhereIF(minRemainingRights.HasValue, p => p.RemainingRightsAmount >= minRemainingRights.Value)
.WhereIF(maxRemainingRights.HasValue, p => p.RemainingRightsAmount <= maxRemainingRights.Value)
.WhereIF(queryYanglsr != null, p => p.Yanglsr >= new DateTime(startYanglsr.ToDate().Year, startYanglsr.ToDate().Month, startYanglsr.ToDate().Day, 0, 0, 0))
.WhereIF(queryYanglsr != null, p => p.Yanglsr <= new DateTime(endYanglsr.ToDate().Year, endYanglsr.ToDate().Month, endYanglsr.ToDate().Day, 23, 59, 59))
.WhereIF(!string.IsNullOrEmpty(input.yinlsr), p => p.Yinlsr != null && p.Yinlsr.Contains(input.yinlsr))
.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,
birthdayType = it.BirthdayType,
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,
isEffective = it.IsEffective
})
.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;
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(!string.IsNullOrEmpty(input.yinlsr), p => p.Yinlsr != null && p.Yinlsr.Contains(input.yinlsr))
.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,
birthdayType = it.BirthdayType,
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,
isEffective = it.IsEffective
})
.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\"},{\"value\":\"消费等级\",\"field\":\"consumeLevelName\"},{\"value\":\"开单总金额\",\"field\":\"totalBillingAmount\"},{\"value\":\"剩余权益总金额\",\"field\":\"remainingRightsAmount\"},{\"value\":\"首次到店时间\",\"field\":\"firstVisitTime\"},{\"value\":\"最后到店时间\",\"field\":\"lastVisitTime\"},{\"value\":\"到店天数\",\"field\":\"visitDays\"},{\"value\":\"沉睡天数\",\"field\":\"sleepDays\"},]".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 导出客户资料（优化版）
///
/// 导出客户资料（优化版）
/// 使用LEFT JOIN优化查询效率，固定导出指定字段
///
/// 请求参数
///
[HttpGet("Actions/ExportOptimized")]
public async Task ExportOptimized([FromQuery] LqKhxxListQueryInput input)
{
var userInfo = await _userManager.GetUserInfo();
// 构建查询条件
var sidx = input.sidx == null ? "F_Id" : input.sidx;
// 将前端字段名映射到数据库字段名
var fieldMapping = new Dictionary
{
{ "id", "F_Id" },
{ "khmc", "khmc" },
{ "sjh", "sjh" },
{ "dah", "dah" },
{ "xb", "xb" },
{ "zcsj", "zcsj" },
{ "khlx", "khlx" },
{ "khjd", "khjd" },
{ "khxf", "khxf" },
{ "xfpc", "xfpc" },
{ "yanglsr", "yanglsr" }
};
if (fieldMapping.ContainsKey(sidx))
{
sidx = fieldMapping[sidx];
}
// 确保 sort 参数不为空
var sortOrder = string.IsNullOrEmpty(input.sort) ? "desc" : input.sort;
List 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 querySleepDaysRange = input.SleepDaysRange != null ? input.SleepDaysRange.Split(',').ToObeject>() : null;
int? minSleepDays = null;
int? maxSleepDays = null;
if (querySleepDaysRange != null && querySleepDaysRange.Count >= 2)
{
int.TryParse(querySleepDaysRange[0], out int min);
int.TryParse(querySleepDaysRange[1], out int max);
minSleepDays = min;
maxSleepDays = max;
}
// 处理剩余权益范围
List queryRemainingRightsRange = input.RemainingRightsRange != null ? input.RemainingRightsRange.Split(',').ToObeject>() : null;
decimal? minRemainingRights = null;
decimal? maxRemainingRights = null;
if (queryRemainingRightsRange != null && queryRemainingRightsRange.Count >= 2)
{
decimal.TryParse(queryRemainingRightsRange[0], out decimal min);
decimal.TryParse(queryRemainingRightsRange[1], out decimal max);
minRemainingRights = min;
maxRemainingRights = max;
}
// 先查询客户基础数据（使用子查询方式，类似GetNoPagingList，但优化为批量查询关联数据）
var customerList = 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(!string.IsNullOrEmpty(input.mainHealthUser), p => p.MainHealthUser.Contains(input.mainHealthUser))
.WhereIF(!string.IsNullOrEmpty(input.subHealthUser), p => p.SubHealthUser.Contains(input.subHealthUser))
.WhereIF(queryZcsj != null, p => p.Zcsj >= new DateTime(startZcsj.ToDate().Year, startZcsj.ToDate().Month, startZcsj.ToDate().Day, 0, 0, 0))
.WhereIF(queryZcsj != null, p => p.Zcsj <= new DateTime(endZcsj.ToDate().Year, endZcsj.ToDate().Month, endZcsj.ToDate().Day, 23, 59, 59))
.WhereIF(!string.IsNullOrEmpty(input.khlx), p => p.Khlx.Equals(input.khlx))
.WhereIF(!string.IsNullOrEmpty(input.khjd), p => p.Khjd.Equals(input.khjd))
.WhereIF(!string.IsNullOrEmpty(input.khxf), p => p.Khxf.Contains(input.khxf))
.WhereIF(!string.IsNullOrEmpty(input.xfpc), p => p.Xfpc.Equals(input.xfpc))
.WhereIF(!string.IsNullOrEmpty(input.tjr), p => p.Tjr.Contains(input.tjr))
.WhereIF(!string.IsNullOrEmpty(input.jdqd), p => p.Jdqd.Contains(input.jdqd))
.WhereIF(!string.IsNullOrEmpty(input.lxdz), p => p.Lxdz.Contains(input.lxdz))
.WhereIF(input.ConsumeLevel.HasValue, p => p.ConsumeLevel == input.ConsumeLevel.Value)
.WhereIF(minSleepDays.HasValue, p => p.SleepDays >= minSleepDays.Value)
.WhereIF(maxSleepDays.HasValue, p => p.SleepDays <= maxSleepDays.Value)
.WhereIF(minRemainingRights.HasValue, p => p.RemainingRightsAmount >= minRemainingRights.Value)
.WhereIF(maxRemainingRights.HasValue, p => p.RemainingRightsAmount <= maxRemainingRights.Value)
.WhereIF(!string.IsNullOrEmpty(input.bz), p => p.Bz.Contains(input.bz))
.WhereIF(queryYanglsr != null, p => p.Yanglsr >= new DateTime(startYanglsr.ToDate().Year, startYanglsr.ToDate().Month, startYanglsr.ToDate().Day, 0, 0, 0))
.WhereIF(queryYanglsr != null, p => p.Yanglsr <= new DateTime(endYanglsr.ToDate().Year, endYanglsr.ToDate().Month, endYanglsr.ToDate().Day, 23, 59, 59))
.Where(p => p.IsEffective == StatusEnum.有效.GetHashCode())
.OrderBy(sidx + " " + sortOrder)
.ToListAsync();
if (customerList == null || !customerList.Any())
{
// 如果没有数据，返回空结果
var emptyExcelConfig = new ExcelConfig();
emptyExcelConfig.FileName = "客户资料导出_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
emptyExcelConfig.HeadFont = "微软雅黑";
emptyExcelConfig.HeadPoint = 10;
emptyExcelConfig.IsAllSizeColumn = true;
emptyExcelConfig.ColumnModel = new List();
// 查找项目根目录并创建ExportFiles文件夹
var emptyBaseDir = AppContext.BaseDirectory;
var emptyProjectRoot = emptyBaseDir;
var emptyDir = new DirectoryInfo(emptyBaseDir);
while (emptyDir != null && emptyDir.Parent != null)
{
try
{
if (emptyDir.GetDirectories(".git").Any() || emptyDir.GetFiles("*.sln").Any())
{
emptyProjectRoot = emptyDir.FullName;
break;
}
}
catch { }
emptyDir = emptyDir.Parent;
}
var emptyExportFilesPath = Path.Combine(emptyProjectRoot, "ExportFiles");
if (!Directory.Exists(emptyExportFilesPath))
{
Directory.CreateDirectory(emptyExportFilesPath);
}
var emptyAddPath = Path.Combine(emptyExportFilesPath, emptyExcelConfig.FileName);
ExcelExportHelper.Export(new List(), emptyExcelConfig, emptyAddPath);
var emptyFileName = _userManager.UserId + "|" + emptyAddPath + "|xls";
return new { name = emptyExcelConfig.FileName, url = "/api/File/Download?encryption=" + DESCEncryption.Encrypt(emptyFileName, "NCC") };
}
// 批量查询门店信息（优化：一次性查询所有需要的门店）
var storeIds = customerList
.Where(c => !string.IsNullOrEmpty(c.Gsmd))
.Select(c => c.Gsmd)
.Distinct()
.Where(id => !string.IsNullOrEmpty(id))
.ToList();
var storeDict = new Dictionary();
if (storeIds != null && storeIds.Any())
{
var stores = await _db.Queryable()
.Where(s => storeIds.Contains(s.Id) && !string.IsNullOrEmpty(s.Id))
.ToListAsync();
if (stores != null && stores.Any())
{
foreach (var store in stores)
{
if (!string.IsNullOrEmpty(store.Id) && !storeDict.ContainsKey(store.Id))
{
storeDict[store.Id] = store;
}
}
}
}
// 批量查询推荐人信息
var tjrIds = customerList
.Where(c => !string.IsNullOrEmpty(c.Tjr))
.Select(c => c.Tjr)
.Distinct()
.Where(id => !string.IsNullOrEmpty(id))
.ToList();
var tjrDict = new Dictionary();
if (tjrIds != null && tjrIds.Any())
{
var tjrs = await _db.Queryable()
.Where(t => tjrIds.Contains(t.Id) && !string.IsNullOrEmpty(t.Id))
.Select(t => new { t.Id, t.Khmc })
.ToListAsync();
if (tjrs != null && tjrs.Any())
{
foreach (var tjr in tjrs)
{
if (!string.IsNullOrEmpty(tjr.Id) && !tjrDict.ContainsKey(tjr.Id))
{
tjrDict[tjr.Id] = tjr.Khmc ?? "";
}
}
}
}
// 批量查询主健康师信息
var mainHealthUserIds = customerList
.Where(c => !string.IsNullOrEmpty(c.MainHealthUser))
.Select(c => c.MainHealthUser)
.Distinct()
.Where(id => !string.IsNullOrEmpty(id))
.ToList();
var mainHealthUserDict = new Dictionary();
if (mainHealthUserIds != null && mainHealthUserIds.Any())
{
var mainHealthUsers = await _db.Queryable()
.Where(u => mainHealthUserIds.Contains(u.Id) && !string.IsNullOrEmpty(u.Id))
.Select(u => new { u.Id, u.RealName })
.ToListAsync();
if (mainHealthUsers != null && mainHealthUsers.Any())
{
foreach (var user in mainHealthUsers)
{
if (!string.IsNullOrEmpty(user.Id) && !mainHealthUserDict.ContainsKey(user.Id))
{
mainHealthUserDict[user.Id] = user.RealName ?? "";
}
}
}
}
// 批量查询副健康师信息
var subHealthUserIds = customerList
.Where(c => !string.IsNullOrEmpty(c.SubHealthUser))
.Select(c => c.SubHealthUser)
.Distinct()
.Where(id => !string.IsNullOrEmpty(id))
.ToList();
var subHealthUserDict = new Dictionary();
if (subHealthUserIds != null && subHealthUserIds.Any())
{
var subHealthUsers = await _db.Queryable()
.Where(u => subHealthUserIds.Contains(u.Id) && !string.IsNullOrEmpty(u.Id))
.Select(u => new { u.Id, u.RealName })
.ToListAsync();
if (subHealthUsers != null && subHealthUsers.Any())
{
foreach (var user in subHealthUsers)
{
if (!string.IsNullOrEmpty(user.Id) && !subHealthUserDict.ContainsKey(user.Id))
{
subHealthUserDict[user.Id] = user.RealName ?? "";
}
}
}
}
// 批量查询拓客人员信息
var expandUserIds = customerList
.Where(c => !string.IsNullOrEmpty(c.ExpandUser))
.Select(c => c.ExpandUser)
.Distinct()
.Where(id => !string.IsNullOrEmpty(id))
.ToList();
var expandUserDict = new Dictionary();
if (expandUserIds != null && expandUserIds.Any())
{
var expandUsers = await _db.Queryable()
.Where(u => expandUserIds.Contains(u.Id) && !string.IsNullOrEmpty(u.Id))
.Select(u => new { u.Id, u.RealName })
.ToListAsync();
if (expandUsers != null && expandUsers.Any())
{
foreach (var user in expandUsers)
{
if (!string.IsNullOrEmpty(user.Id) && !expandUserDict.ContainsKey(user.Id))
{
expandUserDict[user.Id] = user.RealName ?? "";
}
}
}
}
// 转换为导出DTO
var exportData = customerList.Select(kh => new LqKhxxExportOutput
{
id = kh.Id,
khmc = kh.Khmc,
sjh = kh.Sjh,
dah = kh.Dah,
xb = kh.Xb,
gsmdName = (!string.IsNullOrEmpty(kh.Gsmd) && storeDict.ContainsKey(kh.Gsmd)) ? (storeDict[kh.Gsmd].Dm ?? "") : "",
zcsj = kh.Zcsj,
khlxName = GetMemberTypeName(kh.Khlx),
mainHealthUserName = (!string.IsNullOrEmpty(kh.MainHealthUser) && mainHealthUserDict.ContainsKey(kh.MainHealthUser)) ? mainHealthUserDict[kh.MainHealthUser] : "",
subHealthUserName = (!string.IsNullOrEmpty(kh.SubHealthUser) && subHealthUserDict.ContainsKey(kh.SubHealthUser)) ? subHealthUserDict[kh.SubHealthUser] : "",
expandUserName = (!string.IsNullOrEmpty(kh.ExpandUser) && expandUserDict.ContainsKey(kh.ExpandUser)) ? expandUserDict[kh.ExpandUser] : "",
consumeLevelName = GetConsumeLevelNameFromLevel(kh.ConsumeLevel),
tjrName = (!string.IsNullOrEmpty(kh.Tjr) && tjrDict.ContainsKey(kh.Tjr)) ? tjrDict[kh.Tjr] : "",
jdqd = kh.Jdqd,
lxdz = kh.Lxdz,
bz = kh.Bz,
yanglsr = kh.Yanglsr,
firstVisitTime = kh.FirstVisitTime,
lastVisitTime = kh.LastVisitTime,
visitDays = kh.VisitDays,
sleepDays = kh.SleepDays,
totalBillingAmount = kh.TotalBillingAmount,
remainingRightsAmount = kh.RemainingRightsAmount
}).ToList();
// 获取客户类型名称的辅助方法
string GetMemberTypeName(string khlx)
{
if (string.IsNullOrEmpty(khlx)) return "无";
if (int.TryParse(khlx, out var type))
{
return NCC.Code.EnumHelper.GetEnumDesc(type);
}
return "无";
}
// 根据消费等级编号获取消费等级名称
string GetConsumeLevelNameFromLevel(int level)
{
return MemberInfoUpdateConfig.ConsumeLevelNames.ContainsKey(level)
? MemberInfoUpdateConfig.ConsumeLevelNames[level]
: "D";
}
// 定义导出字段映射
List paramList = new List
{
new ParamsModel { value = "客户编码", field = "id" },
new ParamsModel { value = "客户名称", field = "khmc" },
new ParamsModel { value = "手机号", field = "sjh" },
new ParamsModel { value = "档案号", field = "dah" },
new ParamsModel { value = "性别", field = "xb" },
new ParamsModel { value = "归属门店名称", field = "gsmdName" },
new ParamsModel { value = "注册时间", field = "zcsj" },
new ParamsModel { value = "客户类型", field = "khlxName" },
new ParamsModel { value = "主健康师", field = "mainHealthUserName" },
new ParamsModel { value = "副健康师", field = "subHealthUserName" },
new ParamsModel { value = "拓客人员", field = "expandUserName" },
new ParamsModel { value = "消费等级", field = "consumeLevelName" },
new ParamsModel { value = "推荐人", field = "tjrName" },
new ParamsModel { value = "进店渠道", field = "jdqd" },
new ParamsModel { value = "联系地址", field = "lxdz" },
new ParamsModel { value = "备注", field = "bz" },
new ParamsModel { value = "阳历生日", field = "yanglsr" },
new ParamsModel { value = "首次到店时间", field = "firstVisitTime" },
new ParamsModel { value = "最后到店时间", field = "lastVisitTime" },
new ParamsModel { value = "到店天数", field = "visitDays" },
new ParamsModel { value = "沉睡天数", field = "sleepDays" },
new ParamsModel { value = "开单总金额", field = "totalBillingAmount" },
new ParamsModel { value = "剩余权益总金额", field = "remainingRightsAmount" }
};
// 配置Excel导出
ExcelConfig excelconfig = new ExcelConfig();
excelconfig.FileName = "客户资料导出_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
excelconfig.HeadFont = "微软雅黑";
excelconfig.HeadPoint = 10;
excelconfig.IsAllSizeColumn = true;
excelconfig.ColumnModel = new List();
// 添加所有字段到导出配置
foreach (var param in paramList)
{
excelconfig.ColumnModel.Add(new ExcelColumnModel() { Column = param.field, ExcelColumn = param.value });
}
// 查找项目根目录
var baseDir = AppContext.BaseDirectory;
var projectRoot = baseDir;
var dir = new DirectoryInfo(baseDir);
// 优先查找包含 .git 目录的目录（真正的项目根目录）
while (dir != null && dir.Parent != null)
{
try
{
if (dir.GetDirectories(".git").Any())
{
projectRoot = dir.FullName;
break;
}
}
catch
{
// 忽略访问错误，继续向上查找
}
dir = dir.Parent;
}
// 如果没找到 .git 目录，再查找包含 .sln 文件的目录
if (projectRoot == baseDir)
{
dir = new DirectoryInfo(baseDir);
while (dir != null && dir.Parent != null)
{
try
{
if (dir.GetFiles("*.sln").Any())
{
projectRoot = dir.FullName;
break;
}
}
catch
{
// 忽略访问错误，继续向上查找
}
dir = dir.Parent;
}
}
// 在项目根目录下创建 ExportFiles 文件夹
var exportFilesPath = Path.Combine(projectRoot, "ExportFiles");
if (!Directory.Exists(exportFilesPath))
{
Directory.CreateDirectory(exportFilesPath);
}
var addPath = Path.Combine(exportFilesPath, excelconfig.FileName);
// 导出Excel文件
ExcelExportHelper.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();
// 阳历生日转农历：若填写了 yanglsr，自动转换并保存到 yinlsr（格式：腊月十九、四月廿一等，便于直接阅读）
if (input.yanglsr.HasValue)
{
entity.Yinlsr = SolarToLunarString(input.yanglsr.Value);
}
// 处理客户消费字段：将数组转换为逗号分隔的字符串
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 导出所有会员剩余品项
///
/// 导出所有会员剩余品项
///
///
/// 导出所有会员的剩余品项信息到Excel文件
///
/// 示例请求：
/// ```http
/// GET /api/Extend/LqKhxx/Actions/ExportAllMemberRemainingItems
/// ```
///
/// 返回数据说明：
/// - 返回Excel文件下载链接
/// - Excel文件包含所有会员的剩余品项信息
///
/// Excel文件下载信息
/// 成功返回Excel文件下载链接
/// 服务器错误
[HttpGet("Actions/ExportAllMemberRemainingItems")]
public async Task ExportAllMemberRemainingItems()
{
try
{
_logger.LogInformation("开始导出所有会员剩余品项");
// 1. 查询所有有效会员
var members = await _db.Queryable()
.Where(x => x.IsEffective == StatusEnum.有效.GetHashCode())
.Select(x => new { x.Id, x.Khmc, x.Sjh, x.Gsmd })
.ToListAsync();
if (members == null || !members.Any())
{
throw NCCException.Oh("没有找到会员数据");
}
// 2. 批量查询门店信息
var storeIds = members.Where(x => !string.IsNullOrEmpty(x.Gsmd)).Select(x => x.Gsmd).Distinct().ToList();
var storeDict = new Dictionary();
if (storeIds.Any())
{
var stores = await _db.Queryable()
.Where(x => storeIds.Contains(x.Id))
.Select(x => new { x.Id, x.Dm })
.ToListAsync();
storeDict = stores.ToDictionary(x => x.Id, x => x.Dm ?? "");
}
var memberIds = members.Select(x => x.Id).ToList();
// 3. 批量查询所有开单品项数据
var baseItems = await _db.Queryable()
.Where(x => memberIds.Contains(x.MemberId))
.Where(x => x.IsEffective == StatusEnum.有效.GetHashCode())
.Select(x => new
{
x.Id,
x.MemberId,
x.Px,
x.Pxmc,
x.Pxjg,
x.SourceType,
x.ProjectNumber,
x.Remark
})
.ToListAsync();
if (!baseItems.Any())
{
throw NCCException.Oh("没有找到品项数据");
}
var billingItemIds = baseItems.Select(x => x.Id).ToList();
// 4. 批量查询消费数据
var consumedData = await _db.Queryable()
.Where(x => billingItemIds.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();
// 5. 批量查询退卡数据
var refundedData = await _db.Queryable()
.Where(x => billingItemIds.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();
// 6. 批量查询储扣数据
var deductData = await _db.Queryable()
.Where(x => billingItemIds.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();
// 7. 构建字典以提高查询效率
var memberDict = members.ToDictionary(x => x.Id, x => x);
// 8. 组装导出数据
var exportData = new List();
foreach (var item in baseItems)
{
if (!memberDict.ContainsKey(item.MemberId)) continue;
var member = memberDict[item.MemberId];
var consumed = consumedData.FirstOrDefault(c => c.BillingItemId == item.Id)?.TotalConsumed ?? 0m;
var refunded = refundedData.FirstOrDefault(r => r.BillingItemId == item.Id)?.TotalRefunded ?? 0m;
var deduct = deductData.FirstOrDefault(d => d.BillingItemId == item.Id)?.TotalDeduct ?? 0m;
var remaining = item.ProjectNumber - consumed - refunded - deduct;
// 只导出剩余数量不等于0的品项
if (remaining != 0)
{
exportData.Add(new MemberRemainingItemsExportOutput
{
memberId = item.MemberId,
memberName = member.Khmc ?? "",
phone = member.Sjh ?? "",
storeName = !string.IsNullOrEmpty(member.Gsmd) && storeDict.ContainsKey(member.Gsmd) ? storeDict[member.Gsmd] : "",
billingItemId = item.Id,
itemId = item.Px ?? "",
itemName = item.Pxmc ?? "",
itemPrice = item.Pxjg,
sourceType = item.SourceType ?? "",
totalPurchased = item.ProjectNumber,
consumedCount = consumed,
refundedCount = refunded,
deductCount = deduct,
remainingCount = remaining,
remark = item.Remark ?? ""
});
}
}
// 9. 配置Excel导出
ExcelConfig excelconfig = new ExcelConfig();
excelconfig.FileName = "所有会员剩余品项_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
excelconfig.HeadFont = "微软雅黑";
excelconfig.HeadPoint = 10;
excelconfig.IsAllSizeColumn = true;
excelconfig.ColumnModel = new List
{
new ExcelColumnModel { Column = "memberId", ExcelColumn = "会员ID" },
new ExcelColumnModel { Column = "memberName", ExcelColumn = "会员姓名" },
new ExcelColumnModel { Column = "phone", ExcelColumn = "手机号" },
new ExcelColumnModel { Column = "storeName", ExcelColumn = "归属门店" },
new ExcelColumnModel { Column = "billingItemId", ExcelColumn = "开单品项ID" },
new ExcelColumnModel { Column = "itemId", ExcelColumn = "品项ID" },
new ExcelColumnModel { Column = "itemName", ExcelColumn = "品项名称" },
new ExcelColumnModel { Column = "itemPrice", ExcelColumn = "品项单价" },
new ExcelColumnModel { Column = "sourceType", ExcelColumn = "来源类型" },
new ExcelColumnModel { Column = "totalPurchased", ExcelColumn = "总购买数量" },
new ExcelColumnModel { Column = "consumedCount", ExcelColumn = "已耗卡数量" },
new ExcelColumnModel { Column = "refundedCount", ExcelColumn = "已退卡数量" },
new ExcelColumnModel { Column = "deductCount", ExcelColumn = "已储扣数量" },
new ExcelColumnModel { Column = "remainingCount", ExcelColumn = "剩余数量" },
new ExcelColumnModel { Column = "remark", ExcelColumn = "备注" }
};
// 10. 查找项目根目录并创建ExportFiles文件夹
var baseDir = AppContext.BaseDirectory;
var projectRoot = baseDir;
var dir = new DirectoryInfo(baseDir);
while (dir != null && dir.Parent != null)
{
try
{
if (dir.GetDirectories(".git").Any() || dir.GetFiles("*.sln").Any())
{
projectRoot = dir.FullName;
break;
}
}
catch
{
// 忽略访问错误，继续向上查找
}
dir = dir.Parent;
}
// 如果没找到 .git 或 .sln 目录，再查找包含 .sln 文件的目录
if (projectRoot == baseDir)
{
dir = new DirectoryInfo(baseDir);
while (dir != null && dir.Parent != null)
{
try
{
if (dir.GetFiles("*.sln").Any())
{
projectRoot = dir.FullName;
break;
}
}
catch
{
// 忽略访问错误，继续向上查找
}
dir = dir.Parent;
}
}
// 在项目根目录下创建 ExportFiles 文件夹
var exportFilesPath = Path.Combine(projectRoot, "ExportFiles");
if (!Directory.Exists(exportFilesPath))
{
Directory.CreateDirectory(exportFilesPath);
}
var addPath = Path.Combine(exportFilesPath, excelconfig.FileName);
// 11. 导出Excel文件（支持多sheet，每个sheet最多65535行数据）
ExportToExcelWithMultipleSheets(exportData, excelconfig, addPath);
var fileName = _userManager.UserId + "|" + addPath + "|xlsx";
var output = new
{
name = excelconfig.FileName,
url = "/api/File/Download?encryption=" + DESCEncryption.Encrypt(fileName, "NCC")
};
_logger.LogInformation("导出所有会员剩余品项完成，共{Count}条记录", exportData.Count);
return output;
}
catch (Exception ex)
{
_logger.LogError(ex, "导出所有会员剩余品项失败");
throw NCCException.Oh($"导出所有会员剩余品项失败：{ex.Message}");
}
}
///
/// 导出Excel文件（支持多sheet，每个sheet最多65535行数据）
///
private void ExportToExcelWithMultipleSheets(List exportData, ExcelConfig excelConfig, string filePath)
{
const int maxRowsPerSheet = 65535; // 每个sheet最多65535行数据（不包括表头）
var workbook = new XSSFWorkbook();
var type = typeof(MemberRemainingItemsExportOutput);
var properties = type.GetProperties();
// 创建样式
var headStyle = workbook.CreateCellStyle();
var headFont = workbook.CreateFont();
headFont.FontHeightInPoints = (short)excelConfig.HeadPoint;
headFont.FontName = excelConfig.HeadFont;
headFont.Boldweight = (short)700; // 粗体
headStyle.SetFont(headFont);
headStyle.Alignment = HorizontalAlignment.Left;
var dateStyle = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
// 计算列宽
var columnWidths = new int[excelConfig.ColumnModel.Count];
for (int i = 0; i < excelConfig.ColumnModel.Count; i++)
{
var columnName = excelConfig.ColumnModel[i].ExcelColumn;
columnWidths[i] = Encoding.UTF8.GetBytes(columnName).Length + 2;
}
int dataIndex = 0;
int sheetIndex = 0;
while (dataIndex < exportData.Count)
{
// 创建新的sheet
var sheetName = sheetIndex == 0 ? "Sheet1" : $"Sheet{sheetIndex + 1}";
var sheet = workbook.CreateSheet(sheetName);
// 创建表头
var headerRow = sheet.CreateRow(0);
for (int col = 0; col < excelConfig.ColumnModel.Count; col++)
{
var cell = headerRow.CreateCell(col);
cell.SetCellValue(excelConfig.ColumnModel[col].ExcelColumn);
cell.CellStyle = headStyle;
sheet.SetColumnWidth(col, (columnWidths[col] + 1) * 256);
}
// 填充数据
int rowIndex = 1;
while (dataIndex < exportData.Count && rowIndex <= maxRowsPerSheet)
{
var item = exportData[dataIndex];
var dataRow = sheet.CreateRow(rowIndex);
for (int col = 0; col < excelConfig.ColumnModel.Count; col++)
{
var column = excelConfig.ColumnModel[col];
var property = properties.FirstOrDefault(p => p.Name == column.Column);
if (property != null)
{
var cell = dataRow.CreateCell(col);
var value = property.GetValue(item);
SetCellValue(cell, value, property.PropertyType, dateStyle);
}
}
rowIndex++;
dataIndex++;
}
sheetIndex++;
}
// 保存文件
using (var fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(fileStream);
}
}
///
/// 设置单元格值
///
private void SetCellValue(ICell cell, object value, Type propertyType, ICellStyle dateStyle)
{
if (value == null)
{
cell.SetCellValue("");
return;
}
var typeName = propertyType.IsGenericType && propertyType.GetGenericTypeDefinition() == typeof(Nullable<>)
? propertyType.GetGenericArguments()[0].ToString()
: propertyType.ToString();
switch (typeName)
{
case "System.String":
cell.SetCellValue(value.ToString());
break;
case "System.DateTime":
if (value is DateTime dateTime)
{
cell.SetCellValue(dateTime);
cell.CellStyle = dateStyle;
}
else
{
cell.SetCellValue("");
}
break;
case "System.Boolean":
cell.SetCellValue((bool)value);
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
cell.SetCellValue(Convert.ToDouble(value));
break;
case "System.Decimal":
case "System.Double":
case "System.Single":
cell.SetCellValue(Convert.ToDouble(value));
break;
default:
cell.SetCellValue(value.ToString());
break;
}
}
#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等条件，需要通过开单表进行筛选
// 处理多门店筛选：优先使用BillingStoreIds，如果没有则使用BillingStoreId
var billingStoreIds = new List();
if (input.BillingStoreIds != null && input.BillingStoreIds.Any())
{
billingStoreIds = input.BillingStoreIds.Where(x => !string.IsNullOrEmpty(x)).ToList();
}
else if (!string.IsNullOrEmpty(input.BillingStoreId))
{
billingStoreIds = new List { input.BillingStoreId };
}
if (billingStoreIds.Any() ||
!string.IsNullOrEmpty(input.BillingUserId) ||
!string.IsNullOrEmpty(input.PurchaseItemId) ||
!string.IsNullOrEmpty(input.GiftItemId) ||
!string.IsNullOrEmpty(input.ExperienceItemId))
{
// 先查询符合条件的开单记录，获取会员ID列表
var billingQuery = _db.Queryable()
.Where(x => x.IsEffective == StatusEnum.有效.GetHashCode());
if (billingStoreIds.Any())
{
billingQuery = billingQuery.Where(x => billingStoreIds.Contains(x.Djmd));
}
if (!string.IsNullOrEmpty(input.BillingUserId))
{
billingQuery = billingQuery.Where(x => x.CreateUser == input.BillingUserId);
}
// 如果指定了品项ID，需要通过品项明细表进行筛选
if (!string.IsNullOrEmpty(input.PurchaseItemId) || !string.IsNullOrEmpty(input.GiftItemId) || !string.IsNullOrEmpty(input.ExperienceItemId))
{
var itemQuery = _db.Queryable().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();
// 4.5. 双向查询：直接查询会员的所有耗卡记录，确保不遗漏数据
// 通过耗卡记录反向查找可能遗漏的开单品项明细和开单记录
var allConsumedItemIds = new HashSet();
var additionalBillingItemIds = new HashSet();
if (memberIdsList.Any())
{
// 查询会员的所有耗卡记录
var allConsumedItemsFromConsume = await _db.Queryable(
(pxmx, hyhk) => new JoinQueryInfos(JoinType.Inner, pxmx.ConsumeInfoId == hyhk.Id))
.Where((pxmx, hyhk) => memberIdsList.Contains(hyhk.Hy)
&& pxmx.IsEffective == StatusEnum.有效.GetHashCode()
&& hyhk.IsEffective == StatusEnum.有效.GetHashCode())
.Where((pxmx, hyhk) => !string.IsNullOrEmpty(pxmx.BillingItemId))
.Select((pxmx, hyhk) => new
{
pxmx.BillingItemId,
pxmx.Px,
pxmx.Pxmc,
pxmx.Pxjg,
pxmx.ProjectNumber,
pxmx.TotalPrice
})
.ToListAsync();
// 收集所有耗卡记录关联的开单品项明细ID
foreach (var consumedItem in allConsumedItemsFromConsume)
{
if (!string.IsNullOrEmpty(consumedItem.BillingItemId))
{
allConsumedItemIds.Add(consumedItem.BillingItemId);
// 如果这个BillingItemId不在当前的itemDetailIds中，需要额外查询
if (!itemDetailIds.Contains(consumedItem.BillingItemId))
{
additionalBillingItemIds.Add(consumedItem.BillingItemId);
}
}
}
// 如果有额外的开单品项明细ID，查询这些品项明细对应的开单记录
if (additionalBillingItemIds.Any())
{
// 查询这些开单品项明细
var additionalItemDetailsData = await _db.Queryable()
.Where(x => additionalBillingItemIds.Contains(x.Id) && 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中
itemDetails.AddRange(additionalItemDetailsData.Cast());
itemDetailIds.AddRange(additionalItemDetailsData.Select(x => x.Id));
// 查询这些品项明细对应的开单记录（如果不在当前的billingIds中）
var additionalBillingIds = additionalItemDetailsData
.Select(x => x.Glkdbh)
.Where(x => !string.IsNullOrEmpty(x) && !billingIds.Contains(x))
.Distinct()
.ToList();
if (additionalBillingIds.Any())
{
var additionalBillingRecords = await _db.Queryable()
.Where(x => additionalBillingIds.Contains(x.Id) && x.IsEffective == StatusEnum.有效.GetHashCode())
.Select(x => new
{
x.Id,
x.Kdhy,
x.Kdrq,
x.Djmd,
x.CreateUser
})
.ToListAsync();
// 将额外的开单记录添加到billingRecords中（转换为dynamic以便后续使用）
foreach (var record in additionalBillingRecords)
{
billingRecords.Add(record);
}
billingIds.AddRange(additionalBillingIds);
}
}
}
// 5. 批量查询消耗品项（包括所有通过耗卡找到的BillingItemId）
var consumedItems = new List();
if (allConsumedItemIds.Any())
{
// 使用allConsumedItemIds（来自耗卡记录的所有BillingItemId）查询消耗品项
var consumedItemsData = await _db.Queryable()
.Where(x => allConsumedItemIds.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();
}
else if (itemDetailIds.Any())
{
// 兼容原有逻辑：如果没有耗卡记录，使用itemDetailIds查询
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. 批量查询退卡品项（使用itemDetailIds，因为退卡也是关联开单品项明细）
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();
}
// 6.5. 双向查询退卡品项：直接从会员的退卡记录查询，确保不遗漏
if (memberIdsList.Any())
{
var allRefundedItemsFromRefund = await _db.Queryable(
(mx, hytk) => new JoinQueryInfos(JoinType.Inner, mx.RefundInfoId == hytk.Id))
.Where((mx, hytk) => memberIdsList.Contains(hytk.Hy)
&& mx.IsEffective == StatusEnum.有效.GetHashCode()
&& hytk.IsEffective == StatusEnum.有效.GetHashCode())
.Where((mx, hytk) => !string.IsNullOrEmpty(mx.BillingItemId))
.Select((mx, hytk) => new
{
mx.BillingItemId,
mx.Px,
mx.Pxmc,
mx.Pxjg,
mx.ProjectNumber,
mx.Tkje
})
.ToListAsync();
// 收集退卡记录关联的开单品项明细ID
var refundedBillingItemIds = allRefundedItemsFromRefund
.Select(x => x.BillingItemId)
.Where(x => !string.IsNullOrEmpty(x))
.Distinct()
.ToList();
// 如果退卡关联的开单品项明细不在当前的itemDetailIds中，也需要查询对应的开单记录
var missingRefundedBillingItemIds = refundedBillingItemIds
.Where(x => !itemDetailIds.Contains(x))
.ToList();
if (missingRefundedBillingItemIds.Any())
{
// 查询这些开单品项明细
var refundedAdditionalItemDetailsData = await _db.Queryable()
.Where(x => missingRefundedBillingItemIds.Contains(x.Id) && 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中
itemDetails.AddRange(refundedAdditionalItemDetailsData.Cast());
itemDetailIds.AddRange(refundedAdditionalItemDetailsData.Select(x => x.Id));
// 查询这些品项明细对应的开单记录（如果不在当前的billingIds中）
var refundedAdditionalBillingIds = refundedAdditionalItemDetailsData
.Select(x => x.Glkdbh)
.Where(x => !string.IsNullOrEmpty(x) && !billingIds.Contains(x))
.Distinct()
.ToList();
if (refundedAdditionalBillingIds.Any())
{
var refundedAdditionalBillingRecords = await _db.Queryable()
.Where(x => refundedAdditionalBillingIds.Contains(x.Id) && x.IsEffective == StatusEnum.有效.GetHashCode())
.Select(x => new
{
x.Id,
x.Kdhy,
x.Kdrq,
x.Djmd,
x.CreateUser
})
.ToListAsync();
// 将额外的开单记录添加到billingRecords中（转换为dynamic以便后续使用）
foreach (var record in refundedAdditionalBillingRecords)
{
billingRecords.Add(record);
}
billingIds.AddRange(refundedAdditionalBillingIds);
}
// 将退卡数据添加到refundedItems中（去重）
foreach (var refundedItem in allRefundedItemsFromRefund)
{
var refundedItemDynamic = refundedItem as dynamic;
if (!refundedItems.Any(x => x.BillingItemId?.ToString() == refundedItemDynamic.BillingItemId?.ToString()))
{
refundedItems.Add(refundedItemDynamic);
}
}
}
else
{
// 如果所有退卡记录关联的开单品项明细都在itemDetailIds中，只需要合并退卡数据（去重）
foreach (var refundedItem in allRefundedItemsFromRefund)
{
var refundedItemDynamic = refundedItem as dynamic;
if (!refundedItems.Any(x => x.BillingItemId?.ToString() == refundedItemDynamic.BillingItemId?.ToString()))
{
refundedItems.Add(refundedItemDynamic);
}
}
}
}
// 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
#region 会员生日管理
///
/// 获取门店未来30天过生日的会员列表
///
///
/// 根据门店ID获取未来30天内过生日的会员信息，支持阳历生日和农历生日
/// - 阳历生日：直接按公历月日计算今年/明年生日
/// - 农历生日：将今年/明年农历月日转为公历日期后判断是否在未来30天内（便于按公历日期查询提醒）
///
/// 会员等级说明：0=D，1=C，2=B，3=A，4=A+，5=A++
///
/// 示例请求：
/// ```http
/// GET /api/Extend/LqKhxx/GetUpcomingBirthdays?storeId=门店ID
/// ```
///
/// 参数说明：
/// - storeId: 门店ID（可选，不传则查询所有门店）
///
/// 门店ID（可选）
/// 会员生日信息列表，包含会员等级、生日日期、剩余权益等信息
/// 成功返回会员生日列表
/// 服务器错误
[HttpGet("GetUpcomingBirthdays")]
public async Task GetUpcomingBirthdays(string storeId = null)
{
try
{
var now = DateTime.Now;
var endDate = now.AddDays(30);
var cal = new ChineseLunisolarCalendar();
// 构建查询：阳历或农历生日任一有值即可
var query = _db.Queryable()
.Where(x => x.IsEffective == StatusEnum.有效.GetHashCode())
.Where(x => x.Yanglsr != null || !string.IsNullOrEmpty(x.Yinlsr));
// 门店过滤
if (!string.IsNullOrEmpty(storeId))
{
query = query.Where(x => x.Gsmd == storeId);
}
// 获取所有会员数据
var members = await query.ToListAsync();
// 在内存中筛选未来30天过生日的会员
var upcomingBirthdays = new List();
foreach (var member in members)
{
DateTime? upcomingBirthday = null;
string birthdayDateStr;
int age;
DateTime? birthDateForAge = null;
// 优先使用阳历生日
if (member.Yanglsr.HasValue)
{
var birthday = member.Yanglsr.Value;
birthDateForAge = birthday;
var thisYearBirthday = new DateTime(now.Year, birthday.Month, birthday.Day);
var nextYearBirthday = new DateTime(now.Year + 1, birthday.Month, birthday.Day);
if (thisYearBirthday >= now.Date && thisYearBirthday <= endDate.Date)
upcomingBirthday = thisYearBirthday;
else if (nextYearBirthday >= now.Date && nextYearBirthday <= endDate.Date)
upcomingBirthday = nextYearBirthday;
birthdayDateStr = birthday.ToString("MM-dd");
}
// 无阳历有农历：将今年/明年农历生日转为公历后判断
else if (!string.IsNullOrEmpty(member.Yinlsr))
{
int currentLunarYear = cal.GetYear(now);
int nextLunarYear = currentLunarYear + 1;
var (lunarMonth, lunarDay) = LunarStringToMonthDay(member.Yinlsr, cal, currentLunarYear);
if (!lunarMonth.HasValue || !lunarDay.HasValue)
{
continue;
}
DateTime? thisYearSolar = LunarToSolar(cal, currentLunarYear, lunarMonth.Value, lunarDay.Value);
DateTime? nextYearSolar = LunarToSolar(cal, nextLunarYear, lunarMonth.Value, lunarDay.Value);
if (thisYearSolar.HasValue && thisYearSolar.Value >= now.Date && thisYearSolar.Value <= endDate.Date)
upcomingBirthday = thisYearSolar;
else if (nextYearSolar.HasValue && nextYearSolar.Value >= now.Date && nextYearSolar.Value <= endDate.Date)
upcomingBirthday = nextYearSolar;
birthdayDateStr = member.Yinlsr;
}
else
{
continue;
}
if (!upcomingBirthday.HasValue) continue;
// 计算年龄（有阳历用阳历，否则用农历年近似）
age = birthDateForAge.HasValue ? now.Year - birthDateForAge.Value.Year : 0;
if (age > 0 && birthDateForAge.HasValue &&
(now.Month < birthDateForAge.Value.Month || (now.Month == birthDateForAge.Value.Month && now.Day < birthDateForAge.Value.Day)))
{
age--;
}
var output = new LqKhxxBirthdayOutput
{
id = member.Id,
khmc = member.Khmc,
sjh = member.Sjh,
dah = member.Dah,
xb = member.Xb,
gsmd = member.Gsmd,
yanglsr = member.Yanglsr,
yinlsr = member.Yinlsr,
birthdayType = member.BirthdayType,
birthdayDate = birthdayDateStr,
birthdayFullDate = upcomingBirthday.Value,
consumeLevel = member.ConsumeLevel,
consumeLevelName = GetConsumeLevelName(member.ConsumeLevel),
remainingRightsAmount = member.RemainingRightsAmount,
bz = member.Bz,
age = age
};
upcomingBirthdays.Add(output);
}
// 获取门店名称
if (upcomingBirthdays.Any())
{
var storeIds = upcomingBirthdays.Select(x => x.gsmd).Distinct().Where(x => !string.IsNullOrEmpty(x)).ToList();
var stores = await _db.Queryable().Where(s => storeIds.Contains(s.Id)).ToListAsync();
foreach (var item in upcomingBirthdays)
{
if (!string.IsNullOrEmpty(item.gsmd))
{
var store = stores.FirstOrDefault(s => s.Id == item.gsmd);
item.gsmdName = store?.Dm ?? "";
}
}
}
// 按生日日期排序
upcomingBirthdays = upcomingBirthdays.OrderBy(x => x.birthdayFullDate).ToList();
return new
{
code = 200,
msg = "获取成功",
data = upcomingBirthdays
};
}
catch (Exception ex)
{
_logger.LogError(ex, "获取会员生日列表失败");
return new
{
code = 500,
msg = $"获取失败：{ex.Message}"
};
}
}
///
/// 获取消费等级名称
///
private string GetConsumeLevelName(int level)
{
return level switch
{
0 => "D级会员",
1 => "C级会员",
2 => "B级会员",
3 => "A级会员",
4 => "A+级会员",
5 => "A++级会员",
_ => "D级会员"
};
}
#endregion
#region 阳历转农历
private static readonly string[] LunarMonthNames = { "正月", "二月", "三月", "四月", "五月", "六月", "七月", "八月", "九月", "十月", "冬月", "腊月" };
private static readonly string[] LunarDayNames = { "初一", "初二", "初三", "初四", "初五", "初六", "初七", "初八", "初九", "初十", "十一", "十二", "十三", "十四", "十五", "十六", "十七", "十八", "十九", "二十", "廿一", "廿二", "廿三", "廿四", "廿五", "廿六", "廿七", "廿八", "廿九", "三十" };
///
/// 将阳历日期转换为农历字符串（格式：腊月十九、四月廿一等），便于直接阅读
///
/// 阳历日期
/// 农历字符串，若转换失败返回 null
private static string SolarToLunarString(DateTime solarDate)
{
try
{
var cal = new ChineseLunisolarCalendar();
int year = cal.GetYear(solarDate);
int month = cal.GetMonth(solarDate);
int day = cal.GetDayOfMonth(solarDate);
int leapMonth = cal.GetLeapMonth(year);
string monthStr;
if (leapMonth > 0 && month == leapMonth + 1)
{
monthStr = "闰" + LunarMonthNames[leapMonth - 1];
}
else if (leapMonth > 0 && month > leapMonth + 1)
{
monthStr = LunarMonthNames[month - 2];
}
else
{
monthStr = LunarMonthNames[month - 1];
}
string dayStr = day >= 1 && day <= 30 ? LunarDayNames[day - 1] : day.ToString();
return monthStr + dayStr;
}
catch (ArgumentOutOfRangeException)
{
return null;
}
}
///
/// 将农历字符串（腊月十九、四月廿一等）解析为日历月、日数字（闰月需结合年份转换为日历月份）
///
/// 农历字符串
/// 农历日历
/// 农历年（用于闰月转换）
/// (日历月, 日)，解析失败返回 (null, null)
private static (int? month, int? day) LunarStringToMonthDay(string lunarStr, ChineseLunisolarCalendar cal, int lunarYear)
{
if (string.IsNullOrEmpty(lunarStr)) return (null, null);
if (DateTime.TryParse(lunarStr, out var dt))
{
return (dt.Month, dt.Day);
}
bool isLeap = lunarStr.StartsWith("闰");
int startIdx = isLeap ? 1 : 0;
int baseMonth = 0;
for (int i = 0; i < LunarMonthNames.Length; i++)
{
if (lunarStr.IndexOf(LunarMonthNames[i], startIdx, StringComparison.Ordinal) == startIdx)
{
baseMonth = i + 1;
startIdx += LunarMonthNames[i].Length;
break;
}
}
if (baseMonth == 0) return (null, null);
string dayPart = lunarStr.Substring(startIdx);
int day = 0;
for (int i = 0; i < LunarDayNames.Length; i++)
{
if (dayPart == LunarDayNames[i] || dayPart.StartsWith(LunarDayNames[i]))
{
day = i + 1;
break;
}
}
if (day == 0) return (null, null);
int leapMonth = cal.GetLeapMonth(lunarYear);
int calendarMonth;
if (isLeap)
{
calendarMonth = baseMonth + 1;
}
else if (leapMonth > 0 && baseMonth > leapMonth)
{
calendarMonth = baseMonth + 1;
}
else
{
calendarMonth = baseMonth;
}
return (calendarMonth, day);
}
///
/// 将农历日期转换为公历日期，用于生日提醒时按公历日期查询
///
private static DateTime? LunarToSolar(ChineseLunisolarCalendar cal, int lunarYear, int lunarMonth, int lunarDay)
{
try
{
return cal.ToDateTime(lunarYear, lunarMonth, lunarDay, 0, 0, 0, 0);
}
catch (ArgumentOutOfRangeException)
{
return null;
}
}
#endregion
}
}