using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Threading.Tasks; using Mapster; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Logging; using NCC.ClayObject; using NCC.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.LqPersonTimesRecord; using NCC.Extend.Entitys.Dto.LqXhHyhk; using NCC.Extend.Entitys.Dto.LqXhJksyj; using NCC.Extend.Entitys.Dto.LqXhKjbsyj; using NCC.Extend.Entitys.Dto.LqXhPxmx; using NCC.Extend.Entitys.Dto.LqYyjl; using NCC.Extend.Entitys.Enum; 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_person_times_record; using NCC.Extend.Entitys.lq_xh_feedback; using NCC.Extend.Entitys.lq_xh_hyhk; using NCC.Extend.Entitys.lq_xh_jksyj; using NCC.Extend.Entitys.lq_xh_kjbsyj; using NCC.Extend.Entitys.lq_xh_pxmx; using NCC.Extend.Entitys.lq_xmzl; using NCC.Extend.Entitys.lq_mdxx; using NCC.Extend.Interfaces.LqXhHyhk; using NCC.FriendlyException; using NCC.JsonSerialization; using SqlSugar; using Yitter.IdGenerator; namespace NCC.Extend.LqXhHyhk { /// /// 耗卡记录表服务 /// [ApiDescriptionSettings(Tag = "绿纤耗卡记录表服务", Name = "LqXhHyhk", Order = 200, Groups = new[] { "Default" })] [Route("api/Extend/[controller]")] public class LqXhHyhkService : ILqXhHyhkService, IDynamicApiController, ITransient { private readonly ISqlSugarRepository _lqXhHyhkRepository; private readonly ISqlSugarRepository _lqXhJksyjRepository; private readonly ISqlSugarRepository _lqXhKjbsyjRepository; private readonly ISqlSugarRepository _lqXhPxmxRepository; private readonly SqlSugarScope _db; private readonly IUserManager _userManager; private readonly ILogger _logger; /// /// 初始化一个类型的新实例 /// public LqXhHyhkService( ISqlSugarRepository lqXhHyhkRepository, ISqlSugarRepository lqXhJksyjRepository, ISqlSugarRepository lqXhKjbsyjRepository, ISqlSugarRepository lqXhPxmxRepository, IUserManager userManager, ILogger logger ) { _lqXhHyhkRepository = lqXhHyhkRepository; _db = _lqXhHyhkRepository.Context; _lqXhJksyjRepository = lqXhJksyjRepository; _lqXhKjbsyjRepository = lqXhKjbsyjRepository; _lqXhPxmxRepository = lqXhPxmxRepository; _userManager = userManager; _logger = logger; } #region 获取会员耗卡 /// /// 获取会员耗卡 /// /// /// 获取耗卡记录及其关联的品项明细、健康师业绩、科技部老师业绩信息 /// 按照耗卡的完整格式返回数据，不包含汇总信息 /// /// 返回数据结构： /// - 主表信息：耗卡基础信息、门店信息、会员信息等 /// - 品项明细列表：每个品项包含完整的项目信息（项目次数、是否有效、来源类型等） /// - 健康师业绩列表：按品项关联的健康师业绩信息 /// - 科技部老师业绩列表：按品项关联的科技部老师业绩信息 /// /// 耗卡记录主键ID /// 耗卡记录完整信息 /// 查询成功 /// 耗卡记录不存在 /// 服务器内部错误 [HttpGet("{id}")] public async Task GetInfo(string id) { try { // 1. 查询主表信息 var entity = await _db.Queryable().FirstAsync(p => p.Id == id); if (entity == null) { throw NCCException.Oh(ErrorCode.COM1005, "耗卡记录不存在"); } var output = entity.Adapt(); //获取会员信息 var MemberInfo = await _db.Queryable().FirstAsync(p => p.Id == entity.Hy); output.hymc = MemberInfo.Khmc; output.memberPhone = MemberInfo.Sjh; // 2. 查询品项明细列表 var lqXhPxmxList = await _db.Queryable().Where(w => w.ConsumeInfoId == entity.Id).ToListAsync(); // 3. 查询健康师业绩列表 var lqXhJksyjList = await _db.Queryable().Where(w => w.Glkdbh == entity.Id).ToListAsync(); // 4. 查询科技部老师业绩列表 var lqXhKjbsyjList = await _db.Queryable().Where(w => w.Glkdbh == entity.Id).ToListAsync(); // 获取人次记录列表 var personTimesRecordList = await _db.Queryable().Where(x => x.BusinessId == entity.Id && x.IsEffective == StatusEnum.有效.GetHashCode()).ToListAsync(); // 5. 构建品项明细输出，每个品项关联对应的业绩信息 var pxmxOutputList = new List(); foreach (var pxmx in lqXhPxmxList) { var pxmxOutput = new LqXhPxmxInfoOutput { id = pxmx.Id, consumeInfoId = pxmx.ConsumeInfoId, billingItemId = pxmx.BillingItemId, px = pxmx.Px, pxmc = pxmx.Pxmc, pxjg = pxmx.Pxjg, memberId = pxmx.MemberId, createTime = pxmx.CreateTIme, projectNumber = pxmx.ProjectNumber, sourceType = pxmx.SourceType, totalPrice = pxmx.TotalPrice, isEffective = pxmx.IsEffective, originalProjectNumber = pxmx.OriginalProjectNumber, overtimeProjectNumber = pxmx.OvertimeProjectNumber }; // 关联该品项的健康师业绩 var jksyjForPx = lqXhJksyjList.Where(j => j.Kdpxid == pxmx.Id).ToList(); pxmxOutput.lqXhJksyjList = jksyjForPx.Adapt>(); // 关联该品项的科技部老师业绩 var kjbsyjForPx = lqXhKjbsyjList.Where(k => k.Hkpxid == pxmx.Id).ToList(); pxmxOutput.lqXhKjbsyjList = kjbsyjForPx.Adapt>(); pxmxOutputList.Add(pxmxOutput); } // 6. 设置输出结果 output.lqXhPxmxList = pxmxOutputList; // 7. 设置全局业绩列表（用于兼容性，但主要使用品项关联的业绩） output.lqXhJksyjList = lqXhJksyjList.Adapt>(); output.lqXhKjbsyjList = lqXhKjbsyjList.Adapt>(); return output; } catch (Exception ex) { _logger.LogError(ex, "获取耗卡记录失败，ID：{Id}", id); throw NCCException.Oh(ErrorCode.COM1000, "获取耗卡记录失败"); } } #endregion #region 获取会员耗卡列表 /// /// 获取会员耗卡列表 /// /// /// 根据多种条件查询会员耗卡记录列表，支持分页、排序、筛选等功能 /// /// 示例请求： /// GET /api/Extend/LqXhHyhk/GetList?currentPage=1&pageSize=10&jksId=健康师ID&startTime=2025-01-01&endTime=2025-01-31 /// /// 传入参数说明： /// - currentPage: 当前页码（必填，默认1） /// - pageSize: 每页数量（必填，默认10） /// - sidx: 排序字段（可选，默认"id"） /// - sort: 排序方式（可选，ASC/DESC，默认DESC） /// - keyword: 关键字搜索（可选，搜索会员名称、账号、手机号） /// - id: 耗卡编号（可选，模糊匹配） /// - md: 门店ID（可选，精确匹配） /// - mdbh: 门店编号（可选，模糊匹配） /// - mdmc: 门店名称（可选，模糊匹配） /// - hy: 会员ID（可选，精确匹配） /// - hyzh: 会员账号（可选，模糊匹配） /// - hymc: 会员名称（可选，模糊匹配） /// - gklx: 顾客类型（可选，精确匹配） /// - sfykjb: 是否有科技部（可选，精确匹配） /// - hksj: 耗卡时间（可选，格式：开始时间,结束时间，如：2025-01-01,2025-01-31） /// - czry: 操作人员ID（可选，精确匹配） /// - isEffective: 是否有效（可选，0=全部，1=有效，2=无效，默认0） /// - jksId: 健康师ID（可选，传入后只返回该健康师参与的耗卡记录） /// - kjblsId: 科技部老师ID（可选，传入后只返回该老师参与的耗卡记录） /// /// 返回结果说明： /// - code: 响应状态码（200=成功） /// - msg: 响应消息 /// - data: 分页数据对象 /// - list: 耗卡记录列表，每个记录包含： /// - id: 耗卡编号 /// - md: 门店ID /// - mdbh: 门店编号 /// - mdmc: 门店名称 /// - hy: 会员ID /// - hyzh: 会员账号 /// - hymc: 会员名称 /// - memberPhone: 会员手机号 /// - gklx: 顾客类型 /// - xfje: 消费金额 /// - sgfy: 手工费用 /// - sfykjb: 是否有科技部 /// - hksj: 耗卡时间 /// - czry: 操作人员ID /// - isEffective: 是否有效 /// - signatureFile: 签名文件 /// - overtimeCoefficient: 加班系数（NULL或0表示非加班单，大于0表示加班单） /// - originalSgfy: 原始手工费（用户输入的原始值） /// - overtimeSgfy: 加班手工费（加班计算后的增量值） /// - ConsumeDetails: 耗卡明细列表（品项明细），每个明细包含： /// - id: 明细编号 /// - consumeInfoId: 耗卡记录ID /// - billingItemId: 开单品项明细表ID /// - px: 品项编号 /// - pxmc: 品项名称 /// - pxjg: 品项价格 /// - memberId: 会员ID /// - createTime: 创建时间 /// - projectNumber: 项目次数 /// - originalProjectNumber: 原始项目次数 /// - overtimeProjectNumber: 加班项目次数 /// - sourceType: 来源类型（开卡/赠送/其他） /// - totalPrice: 合计金额（品项价格 × 项目次数） /// - isEffective: 是否有效 /// - lqXhJksyjList: 健康师业绩列表，每个业绩包含： /// - id: 业绩编号 /// - glkdbh: 关联耗卡编号 /// - jks: 健康师ID /// - jksxm: 健康师姓名 /// - jkszh: 健康师账号 /// - jksyj: 健康师业绩 /// - yjsj: 业绩时间 /// - jsjId: 金三角ID /// - kdpxid: 耗卡品项ID /// - laborCost: 手工费 /// - kdpxNumber: 耗卡品项次数 /// - originalKdpxNumber: 原始耗卡品项次数 /// - overtimeKdpxNumber: 加班耗卡品项次数 /// - originalLaborCost: 原始手工费 /// - overtimeLaborCost: 加班手工费 /// - isAccompanied: 是否陪同（0=否，1=是） /// - accompaniedProjectNumber: 陪同项目数 /// - memberId: 会员ID /// - memberName: 会员名称 /// - lqXhKjbsyjList: 科技部老师业绩列表，每个业绩包含： /// - id: 业绩编号 /// - glkdbh: 关联耗卡编号 /// - kjbls: 科技部老师ID /// - kjblsxm: 科技部老师姓名 /// - kjblszh: 科技部老师账号 /// - kjblsyj: 科技部老师业绩 /// - yjsj: 业绩时间 /// - hkpxid: 耗卡品项ID /// - laborCost: 手工费 /// - hdpxNumber: 耗卡品项次数 /// - originalHdpxNumber: 原始耗卡品项次数 /// - overtimeHdpxNumber: 加班耗卡品项次数 /// - originalLaborCost: 原始手工费 /// - overtimeLaborCost: 加班手工费 /// - pagination: 分页信息 /// - total: 总记录数 /// - pageSize: 每页数量 /// - currentPage: 当前页码 /// - totalPages: 总页数 /// /// 查询参数 /// 分页的耗卡记录列表，包含耗卡基本信息、耗卡明细、健康师业绩、科技部老师业绩 /// 成功返回耗卡列表 /// 参数错误 /// 服务器内部错误 [HttpGet("")] public async Task GetList([FromQuery] LqXhHyhkListQueryInput input) { var sidx = input.sidx == null ? "id" : input.sidx; var sort = string.IsNullOrEmpty(input.sort) ? "DESC" : input.sort; List queryHksj = input.hksj != null ? input.hksj.Split(',').ToObeject>() : null; DateTime? startHksj = queryHksj != null ? Ext.GetDateTime(queryHksj.First()) : null; DateTime? endHksj = queryHksj != null ? Ext.GetDateTime(queryHksj.Last()) : null; // 根据是否传入健康师ID或科技部老师ID来决定查询方式 ISugarQueryable query = null; // 如果两个都传入了，需要同时JOIN两个表 if (!string.IsNullOrEmpty(input.jksId) && !string.IsNullOrEmpty(input.kjblsId)) { query = _db.Queryable((jksyj, kjbsyj, hyhk) => jksyj.Glkdbh == hyhk.Id && kjbsyj.Glkdbh == hyhk.Id) .Where((jksyj, kjbsyj, hyhk) => jksyj.Jkszh == input.jksId && jksyj.IsEffective == StatusEnum.有效.GetHashCode()) .Where((jksyj, kjbsyj, hyhk) => kjbsyj.Kjblszh == input.kjblsId && kjbsyj.IsEffective == StatusEnum.有效.GetHashCode()) .Select((jksyj, kjbsyj, hyhk) => hyhk) .Distinct() .MergeTable(); // 将多表结果集变成单表，后续可以使用it别名 } // 如果只传入了健康师ID，需要通过JOIN健康师业绩表来过滤 else if (!string.IsNullOrEmpty(input.jksId)) { query = _db.Queryable( (jksyj, hyhk) => jksyj.Glkdbh == hyhk.Id) .Where((jksyj, hyhk) => jksyj.Jkszh == input.jksId && jksyj.IsEffective == StatusEnum.有效.GetHashCode()) .Select((jksyj, hyhk) => hyhk) .Distinct() .MergeTable(); // 将多表结果集变成单表，后续可以使用it别名 } // 如果只传入了科技部老师ID，需要通过JOIN科技部老师业绩表来过滤 else if (!string.IsNullOrEmpty(input.kjblsId)) { query = _db.Queryable( (kjbsyj, hyhk) => kjbsyj.Glkdbh == hyhk.Id) .Where((kjbsyj, hyhk) => kjbsyj.Kjblszh == input.kjblsId && kjbsyj.IsEffective == StatusEnum.有效.GetHashCode()) .Select((kjbsyj, hyhk) => hyhk) .Distinct() .MergeTable(); // 将多表结果集变成单表，后续可以使用it别名 } // 如果都没有传入，直接查询耗卡表 else { query = _db.Queryable(); } var data = await query .WhereIF(!string.IsNullOrEmpty(input.keyword), p => p.Hymc.Contains(input.keyword) || p.Hyzh.Contains(input.keyword) || p.MemberPhone.Contains(input.keyword)) .WhereIF(!string.IsNullOrEmpty(input.id), p => p.Id.Contains(input.id)) .WhereIF(!string.IsNullOrEmpty(input.md), p => p.Md.Equals(input.md)) .WhereIF(!string.IsNullOrEmpty(input.mdbh), p => p.Mdbh.Contains(input.mdbh)) .WhereIF(!string.IsNullOrEmpty(input.mdmc), p => p.Mdmc.Contains(input.mdmc)) .WhereIF(!string.IsNullOrEmpty(input.hy), p => p.Hy.Equals(input.hy)) .WhereIF(!string.IsNullOrEmpty(input.hyzh), p => p.Hyzh.Contains(input.hyzh)) .WhereIF(!string.IsNullOrEmpty(input.hymc), p => p.Hymc.Contains(input.hymc)) .WhereIF(!string.IsNullOrEmpty(input.gklx), p => p.Gklx.Equals(input.gklx)) .WhereIF(!string.IsNullOrEmpty(input.sfykjb), p => p.Sfykjb.Equals(input.sfykjb)) .WhereIF(queryHksj != null, p => p.Hksj >= new DateTime(startHksj.ToDate().Year, startHksj.ToDate().Month, startHksj.ToDate().Day, 0, 0, 0)) .WhereIF(queryHksj != null, p => p.Hksj <= new DateTime(endHksj.ToDate().Year, endHksj.ToDate().Month, endHksj.ToDate().Day, 23, 59, 59)) .WhereIF(!string.IsNullOrEmpty(input.czry), p => p.Czry.Equals(input.czry)) .WhereIF(input.isEffective != 0, p => p.IsEffective == input.isEffective) .Select(it => new LqXhHyhkListOutput { id = it.Id, md = it.Md, mdbh = it.Mdbh, mdmc = it.Mdmc, hy = it.Hy, hyzh = it.Hyzh, hymc = SqlFunc.Subqueryable().Where(w => w.Id == it.Hy).Select(w => w.Khmc), gklx = it.Gklx, xfje = SqlFunc.ToString(it.Xfje), sgfy = SqlFunc.ToString(it.Sgfy), sfykjb = it.Sfykjb, hksj = it.Hksj, czry = it.Czry, memberPhone = SqlFunc.Subqueryable().Where(w => w.Id == it.Hy).Select(w => w.Sjh), isEffective = it.IsEffective, signatureFile = it.SignatureFile, remark = it.Remark, overtimeCoefficient = it.OvertimeCoefficient, originalSgfy = it.OriginalSgfy, overtimeSgfy = it.OvertimeSgfy, appointmentId = it.AppointmentId, }) .MergeTable() .OrderBy($"{sidx} {sort}") .ToPagedListAsync(input.currentPage, input.pageSize); // 获取当前页的耗卡记录ID列表 var consumeIds = data.list.Select(x => x.id).ToList(); // 批量查询耗卡明细 var consumeDetails = new List(); if (consumeIds.Any()) { consumeDetails = await _db.Queryable() .Where(x => consumeIds.Contains(x.ConsumeInfoId) && x.IsEffective == StatusEnum.有效.GetHashCode()) .Select(x => new LqXhPxmxInfoOutput { id = x.Id, consumeInfoId = x.ConsumeInfoId, billingItemId = x.BillingItemId, px = x.Px, pxmc = x.Pxmc, pxjg = x.Pxjg, memberId = x.MemberId, createTime = x.CreateTIme, projectNumber = x.ProjectNumber, originalProjectNumber = x.OriginalProjectNumber, overtimeProjectNumber = x.OvertimeProjectNumber, sourceType = x.SourceType, totalPrice = x.TotalPrice, isEffective = x.IsEffective, }) .ToListAsync(); } // 按耗卡记录ID分组耗卡明细 var consumeDetailsGrouped = consumeDetails.GroupBy(x => x.consumeInfoId) .ToDictionary(g => g.Key, g => g.ToList()); // 批量查询健康师业绩（性能优化：一次性查询所有耗卡的健康师业绩） var jksyjList = new List(); if (consumeIds.Any()) { // 先查询业绩数据 var jksyjEntities = await _db.Queryable() .Where(x => consumeIds.Contains(x.Glkdbh) && x.IsEffective == StatusEnum.有效.GetHashCode()) .ToListAsync(); // 批量查询耗卡记录获取会员ID var hyhkList = await _db.Queryable() .Where(x => consumeIds.Contains(x.Id)) .Select(x => new { x.Id, x.Hy }) .ToListAsync(); var hyhkDict = hyhkList.ToDictionary(x => x.Id, x => x.Hy); // 批量查询会员信息 var memberIds = hyhkDict.Values.Where(x => !string.IsNullOrEmpty(x)).Distinct().ToList(); var memberList = new List<(string Id, string Khmc)>(); if (memberIds.Any()) { var memberData = await _db.Queryable() .Where(x => memberIds.Contains(x.Id)) .Select(x => new { x.Id, x.Khmc }) .ToListAsync(); memberList = memberData.Select(x => (x.Id, x.Khmc)).ToList(); } var memberDict = memberList.ToDictionary(x => x.Id, x => x.Khmc); // 转换为输出DTO jksyjList = jksyjEntities.Select(x => new LqXhJksyjInfoOutput { id = x.Id, glkdbh = x.Glkdbh, jks = x.Jks, jksxm = x.Jksxm, jkszh = x.Jkszh, jksyj = x.Jksyj?.ToString() ?? "0", yjsj = x.Yjsj, jsjId = x.JsjId, kdpxid = x.Kdpxid, laborCost = x.LaborCost, kdpxNumber = x.KdpxNumber, originalKdpxNumber = x.OriginalKdpxNumber, overtimeKdpxNumber = x.OvertimeKdpxNumber, originalLaborCost = x.OriginalLaborCost, overtimeLaborCost = x.OvertimeLaborCost, isAccompanied = x.IsAccompanied, accompaniedProjectNumber = x.AccompaniedProjectNumber, memberId = hyhkDict.ContainsKey(x.Glkdbh) ? hyhkDict[x.Glkdbh] : null, memberName = hyhkDict.ContainsKey(x.Glkdbh) && memberDict.ContainsKey(hyhkDict[x.Glkdbh]) ? memberDict[hyhkDict[x.Glkdbh]] : null, }).ToList(); } // 批量查询科技部老师业绩（性能优化：一次性查询所有耗卡的科技部老师业绩） var kjbsyjList = new List(); if (consumeIds.Any()) { kjbsyjList = await _db.Queryable() .Where(x => consumeIds.Contains(x.Glkdbh) && x.IsEffective == StatusEnum.有效.GetHashCode()) .Select(x => new LqXhKjbsyjInfoOutput { id = x.Id, glkdbh = x.Glkdbh, kjbls = x.Kjbls, kjblsxm = x.Kjblsxm, kjblszh = x.Kjblszh, kjblsyj = SqlFunc.ToString(x.Kjblsyj), yjsj = x.Yjsj, hkpxid = x.Hkpxid, laborCost = x.LaborCost, hdpxNumber = x.HdpxNumber, originalHdpxNumber = x.OriginalHdpxNumber, overtimeHdpxNumber = x.OvertimeHdpxNumber, originalLaborCost = x.OriginalLaborCost, overtimeLaborCost = x.OvertimeLaborCost, }) .ToListAsync(); } // 按耗卡记录ID分组健康师业绩 var jksyjGrouped = jksyjList.GroupBy(x => x.glkdbh) .ToDictionary(g => g.Key, g => g.ToList()); // 按耗卡记录ID分组科技部老师业绩 var kjbsyjGrouped = kjbsyjList.GroupBy(x => x.glkdbh) .ToDictionary(g => g.Key, g => g.ToList()); // 为每个耗卡记录分配耗卡明细、健康师业绩和科技部老师业绩 foreach (var item in data.list) { item.ConsumeDetails = consumeDetailsGrouped.ContainsKey(item.id) ? consumeDetailsGrouped[item.id] : new List(); item.lqXhJksyjList = jksyjGrouped.ContainsKey(item.id) ? jksyjGrouped[item.id] : new List(); item.lqXhKjbsyjList = kjbsyjGrouped.ContainsKey(item.id) ? kjbsyjGrouped[item.id] : new List(); } return PageResult.SqlSugarPageResult(data); } /// /// 获取无耗卡日志的耗卡记录列表 /// /// /// 查询已发生耗卡但尚未填写耗卡日志（服务日志）的记录，支持按耗卡日期、会员姓名、电话等条件筛选 /// /// 示例请求： /// GET /api/Extend/LqXhHyhk/Actions/GetNoFeedbackList?currentPage=1&pageSize=10&hksj=2025-01-01,2025-01-31&hymc=张三&memberPhone=138 /// /// 参数说明： /// - hksj: 耗卡日期范围（格式：开始时间,结束时间，如：2025-01-01,2025-01-31） /// - hymc: 会员姓名（模糊匹配） /// - memberPhone: 会员电话（模糊匹配） /// - keyword: 关键字（可选，搜索会员名称、账号、手机号） /// - md: 门店ID（可选） /// - currentPage: 当前页码 /// - pageSize: 每页数量 /// - sidx: 排序字段（默认hksj） /// - sort: 排序方式（asc/desc，默认desc） /// /// 返回说明： /// - 返回格式与GetList接口相同，包含耗卡基本信息、耗卡明细、健康师业绩、科技部老师业绩 /// /// 查询参数 /// 无耗卡日志的耗卡记录列表（分页） /// 查询成功 /// 参数错误 /// 服务器内部错误 [HttpGet("Actions/GetNoFeedbackList")] public async Task GetNoFeedbackList([FromQuery] LqXhHyhkNoFeedbackListQueryInput input) { var sidx = input.sidx == null ? "hksj" : input.sidx; var sort = string.IsNullOrEmpty(input.sort) ? "DESC" : input.sort; DateTime? startHksj = null; DateTime? endHksj = null; if (!string.IsNullOrEmpty(input.hksj)) { var parts = input.hksj.Split(','); if (parts.Length >= 1 && DateTime.TryParse(parts[0].Trim(), out var start)) startHksj = start; if (parts.Length >= 2 && DateTime.TryParse(parts[1].Trim(), out var end)) endHksj = end; } var queryHksj = startHksj != null || endHksj != null; // 耗卡表 LEFT JOIN 耗卡反馈表，筛选 feedback.Id 为 NULL 的记录（即无耗卡日志） var query = _db.Queryable((hyhk, feedback) => new JoinQueryInfos( JoinType.Left, hyhk.Id == feedback.ConsumeId)) .Where((hyhk, feedback) => feedback.Id == null) .Where((hyhk, feedback) => hyhk.IsEffective == StatusEnum.有效.GetHashCode()) .WhereIF(!string.IsNullOrEmpty(input.keyword), (hyhk, feedback) => hyhk.Hymc.Contains(input.keyword) || hyhk.Hyzh.Contains(input.keyword) || hyhk.MemberPhone.Contains(input.keyword)) .WhereIF(!string.IsNullOrEmpty(input.md), (hyhk, feedback) => hyhk.Md == input.md) .WhereIF(!string.IsNullOrEmpty(input.hymc), (hyhk, feedback) => hyhk.Hymc.Contains(input.hymc)) .WhereIF(!string.IsNullOrEmpty(input.memberPhone), (hyhk, feedback) => hyhk.MemberPhone.Contains(input.memberPhone)) .WhereIF(queryHksj && startHksj.HasValue, (hyhk, feedback) => hyhk.Hksj >= new DateTime(startHksj.Value.Year, startHksj.Value.Month, startHksj.Value.Day, 0, 0, 0)) .WhereIF(queryHksj && endHksj.HasValue, (hyhk, feedback) => hyhk.Hksj <= new DateTime(endHksj.Value.Year, endHksj.Value.Month, endHksj.Value.Day, 23, 59, 59)) .Select((hyhk, feedback) => new LqXhHyhkListOutput { id = hyhk.Id, md = hyhk.Md, mdbh = hyhk.Mdbh, mdmc = hyhk.Mdmc, hy = hyhk.Hy, hyzh = hyhk.Hyzh, hymc = SqlFunc.Subqueryable().Where(w => w.Id == hyhk.Hy).Select(w => w.Khmc), gklx = hyhk.Gklx, xfje = SqlFunc.ToString(hyhk.Xfje), sgfy = SqlFunc.ToString(hyhk.Sgfy), sfykjb = hyhk.Sfykjb, hksj = hyhk.Hksj, czry = hyhk.Czry, memberPhone = SqlFunc.Subqueryable().Where(w => w.Id == hyhk.Hy).Select(w => w.Sjh), isEffective = hyhk.IsEffective, signatureFile = hyhk.SignatureFile, remark = hyhk.Remark, overtimeCoefficient = hyhk.OvertimeCoefficient, originalSgfy = hyhk.OriginalSgfy, overtimeSgfy = hyhk.OvertimeSgfy, appointmentId = hyhk.AppointmentId, }) .MergeTable() .OrderBy($"{sidx} {sort}") .ToPagedListAsync(input.currentPage, input.pageSize); var data = await query; // 获取当前页的耗卡记录ID列表 var consumeIds = data.list.Select(x => x.id).ToList(); // 批量查询耗卡明细、健康师业绩、科技部老师业绩、人次记录 var consumeDetails = new List(); var jksyjList = new List(); var kjbsyjList = new List(); var personTimesRecordList = new List(); if (consumeIds.Any()) { consumeDetails = await _db.Queryable() .Where(x => consumeIds.Contains(x.ConsumeInfoId) && x.IsEffective == StatusEnum.有效.GetHashCode()) .Select(x => new LqXhPxmxInfoOutput { id = x.Id, consumeInfoId = x.ConsumeInfoId, billingItemId = x.BillingItemId, px = x.Px, pxmc = x.Pxmc, pxjg = x.Pxjg, memberId = x.MemberId, createTime = x.CreateTIme, projectNumber = x.ProjectNumber, originalProjectNumber = x.OriginalProjectNumber, overtimeProjectNumber = x.OvertimeProjectNumber, sourceType = x.SourceType, totalPrice = x.TotalPrice, isEffective = x.IsEffective, }) .ToListAsync(); jksyjList = await _db.Queryable() .Where(x => consumeIds.Contains(x.Glkdbh) && x.IsEffective == StatusEnum.有效.GetHashCode()) .Select(x => new LqXhJksyjInfoOutput { id = x.Id, glkdbh = x.Glkdbh, jks = x.Jks, jkszh = x.Jkszh, jksxm = x.Jksxm, jksyj = SqlFunc.ToString(x.Jksyj), yjsj = x.Yjsj, jsjId = x.JsjId, kdpxid = x.Kdpxid, laborCost = x.LaborCost, kdpxNumber = x.KdpxNumber, originalKdpxNumber = x.OriginalKdpxNumber, overtimeKdpxNumber = x.OvertimeKdpxNumber, originalLaborCost = x.OriginalLaborCost, overtimeLaborCost = x.OvertimeLaborCost, isAccompanied = x.IsAccompanied, accompaniedProjectNumber = x.AccompaniedProjectNumber, }) .ToListAsync(); kjbsyjList = await _db.Queryable() .Where(x => consumeIds.Contains(x.Glkdbh) && x.IsEffective == StatusEnum.有效.GetHashCode()) .Select(x => new LqXhKjbsyjInfoOutput { id = x.Id, glkdbh = x.Glkdbh, kjbls = x.Kjbls, kjblszh = x.Kjblszh, kjblsxm = x.Kjblsxm, kjblsyj = SqlFunc.ToString(x.Kjblsyj), yjsj = x.Yjsj, hkpxid = x.Hkpxid, laborCost = x.LaborCost, hdpxNumber = x.HdpxNumber, originalHdpxNumber = x.OriginalHdpxNumber, overtimeHdpxNumber = x.OvertimeHdpxNumber, originalLaborCost = x.OriginalLaborCost, overtimeLaborCost = x.OvertimeLaborCost, }) .ToListAsync(); personTimesRecordList = await _db.Queryable() .Where(x => consumeIds.Contains(x.BusinessId) && x.IsEffective == StatusEnum.有效.GetHashCode()) .Select(x => new LqPersonTimesRecordListOutput { id = x.Id, businessId = x.BusinessId, businessType = x.BusinessType, hasBilling = x.HasBilling, personType = x.PersonType, personId = x.PersonId, personName = x.PersonName, memberId = x.MemberId, memberName = x.MemberName, workDate = x.WorkDate, workMonth = x.WorkMonth, quantity = x.Quantity, createTime = x.CreateTime, isEffective = x.IsEffective, }) .ToListAsync(); } // 按耗卡记录ID分组 var consumeDetailsGrouped = consumeDetails.GroupBy(x => x.consumeInfoId) .ToDictionary(g => g.Key, g => g.ToList()); var jksyjGrouped = jksyjList.GroupBy(x => x.glkdbh) .ToDictionary(g => g.Key, g => g.ToList()); var kjbsyjGrouped = kjbsyjList.GroupBy(x => x.glkdbh) .ToDictionary(g => g.Key, g => g.ToList()); var personTimesRecordGrouped = personTimesRecordList.GroupBy(x => x.businessId) .ToDictionary(g => g.Key, g => g.ToList()); foreach (var item in data.list) { item.ConsumeDetails = consumeDetailsGrouped.ContainsKey(item.id) ? consumeDetailsGrouped[item.id] : new List(); item.lqXhJksyjList = jksyjGrouped.ContainsKey(item.id) ? jksyjGrouped[item.id] : new List(); item.lqXhKjbsyjList = kjbsyjGrouped.ContainsKey(item.id) ? kjbsyjGrouped[item.id] : new List(); item.personTimesRecordList = personTimesRecordGrouped.ContainsKey(item.id) ? personTimesRecordGrouped[item.id] : new List(); } return PageResult.SqlSugarPageResult(data); } #endregion #region 根据健康师ID获取耗卡列表 /// /// 根据健康师ID获取耗卡列表 /// /// /// 根据健康师ID查询该健康师参与的所有耗卡记录，支持时间周期查询和分页 /// /// 示例请求： /// GET /api/Extend/LqXhHyhk/GetListByJksId?jksId=健康师ID&startTime=2025-01-01&endTime=2025-01-31&currentPage=1&pageSize=10 /// /// 参数说明： /// - jksId: 健康师ID（必填） /// - startTime: 开始时间（可选，格式：yyyy-MM-dd 或 yyyy-MM-dd HH:mm:ss） /// - endTime: 结束时间（可选，格式：yyyy-MM-dd 或 yyyy-MM-dd HH:mm:ss） /// - md: 门店ID（可选） /// - isEffective: 是否有效（可选，0=全部，1=有效，2=无效，默认1） /// - currentPage: 当前页码（必填） /// - pageSize: 每页数量（必填） /// /// 返回说明： /// - 返回格式与GetList接口相同，包含耗卡基本信息及耗卡明细列表 /// /// 查询参数 /// 耗卡列表（分页） /// 查询成功 /// 参数错误 /// 服务器内部错误 [HttpGet("GetListByJksId")] public async Task GetListByJksId([FromQuery] LqXhHyhkListByJksQueryInput input) { try { if (string.IsNullOrEmpty(input.jksId)) { throw NCCException.Oh("健康师ID不能为空"); } var sidx = input.sidx == null ? "hksj" : input.sidx; var sort = string.IsNullOrEmpty(input.sort) ? "DESC" : input.sort; // 解析时间范围 DateTime? startDate = null; DateTime? endDate = null; if (!string.IsNullOrEmpty(input.startTime)) { startDate = Ext.GetDateTime(input.startTime); } if (!string.IsNullOrEmpty(input.endTime)) { endDate = Ext.GetDateTime(input.endTime); // 如果只传了日期，则设置为当天的23:59:59 if (endDate.HasValue && !input.endTime.Contains(":")) { endDate = new DateTime(endDate.Value.Year, endDate.Value.Month, endDate.Value.Day, 23, 59, 59); } } // 通过健康师业绩表关联查询耗卡记录 var data = await _db.Queryable((jksyj, hyhk) => jksyj.Glkdbh == hyhk.Id) .Where((jksyj, hyhk) => jksyj.Jkszh == input.jksId) .WhereIF(input.isEffective != 0, (jksyj, hyhk) => jksyj.IsEffective == input.isEffective && hyhk.IsEffective == input.isEffective) .WhereIF(input.isEffective == 0, (jksyj, hyhk) => jksyj.IsEffective == StatusEnum.有效.GetHashCode() && hyhk.IsEffective == StatusEnum.有效.GetHashCode()) .WhereIF(startDate.HasValue, (jksyj, hyhk) => hyhk.Hksj >= startDate.Value) .WhereIF(endDate.HasValue, (jksyj, hyhk) => hyhk.Hksj <= endDate.Value) .WhereIF(!string.IsNullOrEmpty(input.md), (jksyj, hyhk) => hyhk.Md == input.md) .Select((jksyj, hyhk) => new LqXhHyhkListOutput { id = hyhk.Id, md = hyhk.Md, mdbh = hyhk.Mdbh, mdmc = hyhk.Mdmc, hy = hyhk.Hy, hyzh = hyhk.Hyzh, hymc = SqlFunc.Subqueryable().Where(w => w.Id == hyhk.Hy).Select(w => w.Khmc), gklx = hyhk.Gklx, xfje = SqlFunc.ToString(hyhk.Xfje), sgfy = SqlFunc.ToString(hyhk.Sgfy), sfykjb = hyhk.Sfykjb, hksj = hyhk.Hksj, czry = hyhk.Czry, memberPhone = SqlFunc.Subqueryable().Where(w => w.Id == hyhk.Hy).Select(w => w.Sjh), isEffective = hyhk.IsEffective, signatureFile = hyhk.SignatureFile, remark = hyhk.Remark, overtimeCoefficient = hyhk.OvertimeCoefficient, originalSgfy = hyhk.OriginalSgfy, overtimeSgfy = hyhk.OvertimeSgfy, }) .MergeTable() .Distinct() // 去重，因为一个耗卡可能对应多个健康师业绩记录 .OrderBy($"{sidx} {sort}") .ToPagedListAsync(input.currentPage, input.pageSize); // 获取当前页的耗卡记录ID列表 var consumeIds = data.list.Select(x => x.id).ToList(); // 获取人次记录列表 var personTimesRecordList = await _db.Queryable().Where(x => consumeIds.Contains(x.BusinessId) && x.IsEffective == StatusEnum.有效.GetHashCode()) .Select(x => new LqPersonTimesRecordListOutput { id = x.Id, businessId = x.BusinessId, businessType = x.BusinessType, personType = x.PersonType, personId = x.PersonId, personName = x.PersonName, memberId = x.MemberId, memberName = x.MemberName, workDate = x.WorkDate, workMonth = x.WorkMonth, quantity = x.Quantity, createTime = x.CreateTime, isEffective = x.IsEffective, }) .ToListAsync(); // 按耗卡记录ID分组人次记录 var personTimesRecordGrouped = personTimesRecordList.GroupBy(x => x.businessId).ToDictionary(g => g.Key, g => g.ToList()); // 为每个耗卡记录分配人次记录 foreach (var item in data.list) { item.personTimesRecordList = personTimesRecordGrouped.ContainsKey(item.id) ? personTimesRecordGrouped[item.id] : new List(); } // 批量查询耗卡明细 var consumeDetails = new List(); if (consumeIds.Any()) { consumeDetails = await _db.Queryable().Where(x => consumeIds.Contains(x.ConsumeInfoId) && x.IsEffective == StatusEnum.有效.GetHashCode()) .Select(x => new LqXhPxmxInfoOutput { id = x.Id, consumeInfoId = x.ConsumeInfoId, billingItemId = x.BillingItemId, px = x.Px, pxmc = x.Pxmc, pxjg = x.Pxjg, memberId = x.MemberId, createTime = x.CreateTIme, projectNumber = x.ProjectNumber, originalProjectNumber = x.OriginalProjectNumber, overtimeProjectNumber = x.OvertimeProjectNumber, sourceType = x.SourceType, totalPrice = x.TotalPrice, isEffective = x.IsEffective, }) .ToListAsync(); } // 按耗卡记录ID分组耗卡明细 var consumeDetailsGrouped = consumeDetails.GroupBy(x => x.consumeInfoId).ToDictionary(g => g.Key, g => g.ToList()); // 为每个耗卡记录分配耗卡明细 foreach (var item in data.list) { item.ConsumeDetails = consumeDetailsGrouped.ContainsKey(item.id) ? consumeDetailsGrouped[item.id] : new List(); } return PageResult.SqlSugarPageResult(data); } catch (Exception ex) { _logger.LogError(ex, $"根据健康师ID获取耗卡列表失败 - 健康师ID: {input?.jksId}, 开始时间: {input?.startTime}, 结束时间: {input?.endTime}"); throw NCCException.Oh($"根据健康师ID获取耗卡列表失败: {ex.Message}"); } } #endregion #region 根据科技部老师ID获取耗卡列表 /// /// 根据科技部老师ID获取耗卡列表 /// /// /// 根据科技部老师ID查询该老师参与的所有耗卡记录，支持时间周期查询和分页 /// /// 示例请求： /// GET /api/Extend/LqXhHyhk/GetListByKjbId?kjblsId=科技部老师ID&startTime=2025-01-01&endTime=2025-01-31&currentPage=1&pageSize=10 /// /// 参数说明： /// - kjblsId: 科技部老师ID（必填） /// - startTime: 开始时间（可选，格式：yyyy-MM-dd 或 yyyy-MM-dd HH:mm:ss） /// - endTime: 结束时间（可选，格式：yyyy-MM-dd 或 yyyy-MM-dd HH:mm:ss） /// - md: 门店ID（可选） /// - isEffective: 是否有效（可选，0=全部，1=有效，2=无效，默认1） /// - currentPage: 当前页码（必填） /// - pageSize: 每页数量（必填） /// /// 返回说明： /// - 返回格式与GetList接口相同，包含耗卡基本信息及耗卡明细列表 /// /// 查询参数 /// 耗卡列表（分页） /// 查询成功 /// 参数错误 /// 服务器内部错误 [HttpGet("GetListByKjbId")] public async Task GetListByKjbId([FromQuery] LqXhHyhkListByKjbQueryInput input) { try { if (string.IsNullOrEmpty(input.kjblsId)) { throw NCCException.Oh("科技部老师ID不能为空"); } var sidx = input.sidx == null ? "hksj" : input.sidx; var sort = string.IsNullOrEmpty(input.sort) ? "DESC" : input.sort; // 解析时间范围 DateTime? startDate = null; DateTime? endDate = null; if (!string.IsNullOrEmpty(input.startTime)) { startDate = Ext.GetDateTime(input.startTime); } if (!string.IsNullOrEmpty(input.endTime)) { endDate = Ext.GetDateTime(input.endTime); // 如果只传了日期，则设置为当天的23:59:59 if (endDate.HasValue && !input.endTime.Contains(":")) { endDate = new DateTime(endDate.Value.Year, endDate.Value.Month, endDate.Value.Day, 23, 59, 59); } } // 通过科技部老师业绩表关联查询耗卡记录 var data = await _db.Queryable( (kjbsyj, hyhk) => kjbsyj.Glkdbh == hyhk.Id) .Where((kjbsyj, hyhk) => kjbsyj.Kjblszh == input.kjblsId) .WhereIF(input.isEffective != 0, (kjbsyj, hyhk) => kjbsyj.IsEffective == input.isEffective && hyhk.IsEffective == input.isEffective) .WhereIF(input.isEffective == 0, (kjbsyj, hyhk) => kjbsyj.IsEffective == StatusEnum.有效.GetHashCode() && hyhk.IsEffective == StatusEnum.有效.GetHashCode()) .WhereIF(startDate.HasValue, (kjbsyj, hyhk) => hyhk.Hksj >= startDate.Value) .WhereIF(endDate.HasValue, (kjbsyj, hyhk) => hyhk.Hksj <= endDate.Value) .WhereIF(!string.IsNullOrEmpty(input.md), (kjbsyj, hyhk) => hyhk.Md == input.md) .Select((kjbsyj, hyhk) => new LqXhHyhkListOutput { id = hyhk.Id, md = hyhk.Md, mdbh = hyhk.Mdbh, mdmc = hyhk.Mdmc, hy = hyhk.Hy, hyzh = hyhk.Hyzh, hymc = SqlFunc.Subqueryable().Where(w => w.Id == hyhk.Hy).Select(w => w.Khmc), gklx = hyhk.Gklx, xfje = SqlFunc.ToString(hyhk.Xfje), sgfy = SqlFunc.ToString(hyhk.Sgfy), sfykjb = hyhk.Sfykjb, hksj = hyhk.Hksj, czry = hyhk.Czry, memberPhone = SqlFunc.Subqueryable().Where(w => w.Id == hyhk.Hy).Select(w => w.Sjh), isEffective = hyhk.IsEffective, signatureFile = hyhk.SignatureFile, remark = hyhk.Remark, overtimeCoefficient = hyhk.OvertimeCoefficient, originalSgfy = hyhk.OriginalSgfy, overtimeSgfy = hyhk.OvertimeSgfy, }) .MergeTable() .Distinct() // 去重，因为一个耗卡可能对应多个科技部老师业绩记录 .OrderBy($"{sidx} {sort}") .ToPagedListAsync(input.currentPage, input.pageSize); // 获取当前页的耗卡记录ID列表 var consumeIds = data.list.Select(x => x.id).ToList(); // 批量查询耗卡明细 var consumeDetails = new List(); if (consumeIds.Any()) { consumeDetails = await _db.Queryable() .Where(x => consumeIds.Contains(x.ConsumeInfoId) && x.IsEffective == StatusEnum.有效.GetHashCode()) .Select(x => new LqXhPxmxInfoOutput { id = x.Id, consumeInfoId = x.ConsumeInfoId, billingItemId = x.BillingItemId, px = x.Px, pxmc = x.Pxmc, pxjg = x.Pxjg, memberId = x.MemberId, createTime = x.CreateTIme, projectNumber = x.ProjectNumber, originalProjectNumber = x.OriginalProjectNumber, overtimeProjectNumber = x.OvertimeProjectNumber, sourceType = x.SourceType, totalPrice = x.TotalPrice, isEffective = x.IsEffective, }) .ToListAsync(); } // 按耗卡记录ID分组耗卡明细 var consumeDetailsGrouped = consumeDetails.GroupBy(x => x.consumeInfoId) .ToDictionary(g => g.Key, g => g.ToList()); // 为每个耗卡记录分配耗卡明细 foreach (var item in data.list) { item.ConsumeDetails = consumeDetailsGrouped.ContainsKey(item.id) ? consumeDetailsGrouped[item.id] : new List(); } return PageResult.SqlSugarPageResult(data); } catch (Exception ex) { _logger.LogError(ex, $"根据科技部老师ID获取耗卡列表失败 - 科技部老师ID: {input?.kjblsId}, 开始时间: {input?.startTime}, 结束时间: {input?.endTime}"); throw NCCException.Oh($"根据科技部老师ID获取耗卡列表失败: {ex.Message}"); } } #endregion #region 新建会员耗卡 /// /// 新建会员耗卡 /// /// /// 创建会员耗卡记录及其关联的品项明细、健康师业绩、科技部老师业绩信息 /// /// 示例请求： /// ```json /// { /// "hyid": "会员ID", /// "hkmd": "耗卡门店", /// "hksj": "2025-01-11", /// "lqXhPxmxList": [ /// { /// "px": "品项编号", /// "pxmc": "品项名称", /// "pxjg": 100.00, /// "projectNumber": 1, /// "xfzs": "否", /// "lqXhJksyjList": [ /// { /// "jks": "健康师", /// "jksxm": "健康师姓名", /// "jksyj": "100" /// } /// ] /// } /// ] /// } /// ``` /// /// 参数说明： /// - hyid: 会员ID /// - hkmd: 耗卡门店 /// - lqXhPxmxList: 耗卡品项明细列表 /// /// 会员耗卡创建参数 /// 无返回值 /// 创建成功 /// 参数错误或数据验证失败 /// 服务器内部错误 [HttpPost("")] public async Task Create([FromBody] LqXhHyhkCrInput input) { var userInfo = await _userManager.GetUserInfo(); //首先判断当前时间是否是加班时间 var entity = input.Adapt(); entity.Id = YitIdHelper.NextId().ToString(); entity.Czry = _userManager.UserId; entity.MemberPhone = _db.Queryable().Where(w => w.Id == entity.Hy).First().Sjh; entity.CreateTime = DateTime.Now; entity.IsEffective = StatusEnum.有效.GetHashCode(); entity.UpdateTime = DateTime.Now; entity.OvertimeCoefficient = input.overtimeCoefficient ?? 0; entity.OriginalSgfy = input.sgfy; entity.AppointmentId = input.appointmentId; // 加班手工费 = 健康师原始手工费之和 × 加班系数（科技部不参与加班） var jksOriginalLaborCostSum = input.lqXhPxmxList? .SelectMany(p => p.lqXhJksyjList ?? Enumerable.Empty()) .Sum(j => j.laborCost ?? 0) ?? 0; entity.OvertimeSgfy = (decimal)(jksOriginalLaborCostSum * entity.OvertimeCoefficient); // 最终手工费 = 原始手工费 + 加班手工费 entity.Sgfy = entity.OriginalSgfy + entity.OvertimeSgfy; try { // 开启事务 _db.BeginTran(); // 查询会员信息 var memberInfo = await _db.Queryable().Where(w => w.Id == entity.Hy).FirstAsync(); //如果会员类型是线索，那么就更新为新客 if (memberInfo.Khlx == MemberTypeEnum.线索.GetHashCode().ToString()) { memberInfo.Khlx = MemberTypeEnum.新客.GetHashCode().ToString(); } // 只要有耗卡记录且消费金额大于0，就更新最后消费时间（用于沉睡天数计算） // 使用耗卡时间而不是当前时间，确保准确性 if (entity.Xfje > 0) { memberInfo.LastConsumeTime = entity.Hksj; } //保存会员信息 await _db.Updateable(memberInfo).ExecuteCommandAsync(); // 新增会员耗卡记录 var newEntity = await _db.Insertable(entity).IgnoreColumns(ignoreNullColumn: true).ExecuteReturnEntityAsync(); // 收集所有需要插入的实体，然后批量插入 var allPxmxEntities = new List(); var allJksyjEntities = new List(); var allKjbsyjEntities = new List(); var allPersonTimesRecordEntities = new List(); // 处理品项明细列表 if (input.lqXhPxmxList != null && input.lqXhPxmxList.Any()) { foreach (var item in input.lqXhPxmxList) { // 创建品项明细实体 var lqXhPxmxEntity = new LqXhPxmxEntity { Id = YitIdHelper.NextId().ToString(), ConsumeInfoId = newEntity.Id, BillingItemId = item.billingItemId, CreateTIme = DateTime.Now, Yjsj = input.hksj, MemberId = entity.Hy, OriginalProjectNumber = item.projectNumber ?? 0, OvertimeProjectNumber = (decimal)(entity.OvertimeCoefficient * (item.projectNumber ?? 0)), ProjectNumber = (decimal)((item.projectNumber ?? 0) + (entity.OvertimeCoefficient * (item.projectNumber ?? 0))), TotalPrice = (decimal)(item.pxjg * (item.projectNumber ?? 1)), Px = item.px, Pxmc = item.pxmc, Pxjg = item.pxjg, SourceType = item.sourceType, IsEffective = StatusEnum.有效.GetHashCode(), ItemCategory = await _db.Queryable().Where(x => x.Id == item.px).Select(x => x.Qt2).FirstAsync(), PerformanceType = await _db.Queryable().Where(x => x.Id == item.px).Select(x => x.Fl3).FirstAsync() ?? "", BeautyType = await _db.Queryable().Where(x => x.Id == item.px).Select(x => x.BeautyType).FirstAsync(), }; allPxmxEntities.Add(lqXhPxmxEntity); // 收集该品项关联的健康师业绩 if (item.lqXhJksyjList != null && item.lqXhJksyjList.Any()) { foreach (var ijks_tem in item.lqXhJksyjList) { allJksyjEntities.Add( new LqXhJksyjEntity { Id = YitIdHelper.NextId().ToString(), Glkdbh = newEntity.Id, Jks = ijks_tem.jks, Jksxm = ijks_tem.jksxm, Jkszh = ijks_tem.jkszh, Jksyj = ijks_tem.jksyj, Yjsj = input.hksj, CreateTime = DateTime.Now, JsjId = ijks_tem.jsjId, Kdpxid = lqXhPxmxEntity.Id, OriginalLaborCost = ijks_tem.laborCost, OvertimeLaborCost = (decimal)(entity.OvertimeCoefficient * (ijks_tem.laborCost ?? 0)), LaborCost = (decimal)((ijks_tem.laborCost ?? 0) + (entity.OvertimeCoefficient * (ijks_tem.laborCost ?? 0))), OriginalKdpxNumber = ijks_tem.kdpxNumber, OvertimeKdpxNumber = (decimal)(entity.OvertimeCoefficient * (ijks_tem.kdpxNumber ?? 0)), KdpxNumber = (decimal)((ijks_tem.kdpxNumber ?? 0) + (entity.OvertimeCoefficient * (ijks_tem.kdpxNumber ?? 0))) + (ijks_tem.accompaniedProjectNumber ?? 0), IsAccompanied = ijks_tem.isAccompanied, AccompaniedProjectNumber = ijks_tem.accompaniedProjectNumber, IsEffective = StatusEnum.有效.GetHashCode(), ItemCategory = lqXhPxmxEntity.ItemCategory, ItemId = lqXhPxmxEntity.Px, StoreId = entity.Md, ItemName = lqXhPxmxEntity.Pxmc, PerformanceType = lqXhPxmxEntity.PerformanceType, BeautyType = lqXhPxmxEntity.BeautyType, } ); } } // 收集该品项关联的科技部老师业绩 if (item.lqXhKjbsyjList != null && item.lqXhKjbsyjList.Any()) { foreach (var ikjbs_tem in item.lqXhKjbsyjList) { allKjbsyjEntities.Add( new LqXhKjbsyjEntity { Id = YitIdHelper.NextId().ToString(), Glkdbh = newEntity.Id, Kjbls = ikjbs_tem.kjbls, Kjblsxm = ikjbs_tem.kjblsxm, Kjblszh = ikjbs_tem.kjblszh, Kjblsyj = ikjbs_tem.kjblsyj, Yjsj = input.hksj, CreateTime = DateTime.Now, Hkpxid = lqXhPxmxEntity.Id, // OriginalHdpxNumber = ikjbs_tem.hdpxNumber, // OvertimeHdpxNumber = (decimal)(entity.OvertimeCoefficient * (ikjbs_tem.hdpxNumber ?? 0)), // HdpxNumber = (decimal)((ikjbs_tem.hdpxNumber ?? 0) + (entity.OvertimeCoefficient * (ikjbs_tem.hdpxNumber ?? 0))), OriginalHdpxNumber = ikjbs_tem.hdpxNumber, OvertimeHdpxNumber = 0, HdpxNumber = ikjbs_tem.hdpxNumber, // OriginalLaborCost = ikjbs_tem.laborCost, // OvertimeLaborCost = (decimal)(entity.OvertimeCoefficient * (ikjbs_tem.laborCost ?? 0)), // LaborCost = (decimal)((ikjbs_tem.laborCost ?? 0) + (entity.OvertimeCoefficient * (ikjbs_tem.laborCost ?? 0))), OriginalLaborCost = ikjbs_tem.laborCost, OvertimeLaborCost = 0, LaborCost = ikjbs_tem.laborCost, IsEffective = StatusEnum.有效.GetHashCode(), ItemCategory = lqXhPxmxEntity.ItemCategory, ItemId = lqXhPxmxEntity.Px, StoreId = entity.Md, ItemName = lqXhPxmxEntity.Pxmc, PerformanceType = lqXhPxmxEntity.PerformanceType, BeautyType = lqXhPxmxEntity.BeautyType, } ); } } } } //查询开单记录 var billingRecord = await _db.Queryable().Where(x => x.Kdhy == entity.Hy && x.IsEffective == StatusEnum.有效.GetHashCode() && x.Sfyj > 0).AnyAsync(); // 剔除所有T区健康师（姓名中包含"T区"的健康师） var NoTallJksyjEntities = allJksyjEntities.Where(x => x.Jks != null && x.Jks != "" && (x.Jksxm == null || !x.Jksxm.Contains("T区"))).ToList(); //获取这次耗卡有多少个健康师 var jksCount = NoTallJksyjEntities.GroupBy(x => x.Jks).Count(); //添加到人次表里面去 foreach (var item in NoTallJksyjEntities.Select(x => x.Jks).Distinct()) { LqPersonTimesRecordEntity personTimesRecordEntity = new LqPersonTimesRecordEntity { Id = YitIdHelper.NextId().ToString(), BusinessId = newEntity.Id, BusinessType = "耗卡", PersonType = "健康师", PersonId = item, PersonName = NoTallJksyjEntities.Where(x => x.Jks == item).Select(x => x.Jksxm).First(), MemberId = entity.Hy, MemberName = memberInfo.Khmc, WorkDate = input.hksj, WorkMonth = input.hksj.ToDate().ToString("yyyyMM"), Quantity = 1 / (decimal)jksCount, CreateTime = DateTime.Now, IsEffective = StatusEnum.有效.GetHashCode(), HasBilling = billingRecord ? 1 : 0, }; allPersonTimesRecordEntities.Add(personTimesRecordEntity); } //剔除所有T区科技部老师（姓名中包含"T区"的科技部老师） var NoTallKjbsyjEntities = allKjbsyjEntities.Where(x => x.Kjbls != null && x.Kjbls != "" && (x.Kjblsxm == null || !x.Kjblsxm.Contains("T区"))).ToList(); //获取这次耗卡有多少个科技部老师 var kjbCount = NoTallKjbsyjEntities.GroupBy(x => x.Kjbls).Count(); //添加到人次表里面去 foreach (var item in NoTallKjbsyjEntities.Select(x => x.Kjbls).Distinct()) { LqPersonTimesRecordEntity personTimesRecordEntity = new LqPersonTimesRecordEntity { Id = YitIdHelper.NextId().ToString(), BusinessId = newEntity.Id, BusinessType = "耗卡", PersonType = "科技部老师", PersonId = item, PersonName = NoTallKjbsyjEntities.Where(x => x.Kjbls == item).Select(x => x.Kjblsxm).First(), MemberId = entity.Hy, MemberName = memberInfo.Khmc, WorkDate = input.hksj, WorkMonth = input.hksj.ToDate().ToString("yyyyMM"), Quantity = 1 / (decimal)kjbCount, CreateTime = DateTime.Now, IsEffective = StatusEnum.有效.GetHashCode(), HasBilling = billingRecord ? 1 : 0, }; allPersonTimesRecordEntities.Add(personTimesRecordEntity); } // 批量插入人次记录 if (allPersonTimesRecordEntities.Any()) { await _db.Insertable(allPersonTimesRecordEntities).ExecuteCommandAsync(); } // 批量插入品项明细 if (allPxmxEntities.Any()) { await _db.Insertable(allPxmxEntities).ExecuteCommandAsync(); } // 批量插入健康师业绩 if (allJksyjEntities.Any()) { await _db.Insertable(allJksyjEntities).ExecuteCommandAsync(); } // 批量插入科技部老师业绩 if (allKjbsyjEntities.Any()) { await _db.Insertable(allKjbsyjEntities).ExecuteCommandAsync(); } // 提交事务 _db.CommitTran(); } catch (Exception ex) { // 回滚事务 _db.RollbackTran(); Console.WriteLine($"新建会员耗卡失败: {ex.Message}"); Console.WriteLine($"堆栈跟踪: {ex.StackTrace}"); throw NCCException.Oh(ErrorCode.COM1000, $"新建会员耗卡失败: {ex.Message}"); } } #endregion #region 获取会员耗卡无分页列表 /// /// 获取会员耗卡无分页列表 /// /// 请求参数 /// [NonAction] public async Task GetNoPagingList([FromQuery] LqXhHyhkListQueryInput input) { var sidx = input.sidx == null ? "id" : input.sidx; List queryHksj = input.hksj != null ? input.hksj.Split(',').ToObeject>() : null; DateTime? startHksj = queryHksj != null ? Ext.GetDateTime(queryHksj.First()) : null; DateTime? endHksj = queryHksj != null ? Ext.GetDateTime(queryHksj.Last()) : null; var data = await _db.Queryable() .WhereIF(!string.IsNullOrEmpty(input.id), p => p.Id.Contains(input.id)) .WhereIF(!string.IsNullOrEmpty(input.md), p => p.Md.Equals(input.md)) .WhereIF(!string.IsNullOrEmpty(input.mdbh), p => p.Mdbh.Contains(input.mdbh)) .WhereIF(!string.IsNullOrEmpty(input.mdmc), p => p.Mdmc.Contains(input.mdmc)) .WhereIF(!string.IsNullOrEmpty(input.hy), p => p.Hy.Equals(input.hy)) .WhereIF(!string.IsNullOrEmpty(input.hyzh), p => p.Hyzh.Contains(input.hyzh)) .WhereIF(!string.IsNullOrEmpty(input.hymc), p => p.Hymc.Contains(input.hymc)) .WhereIF(!string.IsNullOrEmpty(input.gklx), p => p.Gklx.Equals(input.gklx)) .WhereIF(!string.IsNullOrEmpty(input.sfykjb), p => p.Sfykjb.Equals(input.sfykjb)) .WhereIF(queryHksj != null, p => p.Hksj >= new DateTime(startHksj.ToDate().Year, startHksj.ToDate().Month, startHksj.ToDate().Day, 0, 0, 0)) .WhereIF(queryHksj != null, p => p.Hksj <= new DateTime(endHksj.ToDate().Year, endHksj.ToDate().Month, endHksj.ToDate().Day, 23, 59, 59)) .WhereIF(!string.IsNullOrEmpty(input.czry), p => p.Czry.Equals(input.czry)) .Select(it => new LqXhHyhkListOutput { id = it.Id, md = it.Md, mdbh = it.Mdbh, mdmc = it.Mdmc, hy = it.Hy, hyzh = it.Hyzh, hymc = SqlFunc.Subqueryable().Where(w => w.Id == it.Hy).Select(w => w.Khmc), memberPhone = SqlFunc.Subqueryable().Where(w => w.Id == it.Hy).Select(w => w.Sjh), gklx = it.Gklx, xfje = SqlFunc.ToString(it.Xfje), sgfy = SqlFunc.ToString(it.Sgfy), sfykjb = it.Sfykjb, hksj = it.Hksj, czry = it.Czry, remark = it.Remark, }) .MergeTable() .OrderBy(sidx + " " + input.sort) .ToListAsync(); return data; } #endregion #region 导出会员耗卡 /// /// 导出会员耗卡 /// /// 请求参数 /// [HttpGet("Actions/Export")] public async Task Export([FromQuery] LqXhHyhkListQueryInput 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\":\"md\"},{\"value\":\"门店编号\",\"field\":\"mdbh\"},{\"value\":\"门店名称\",\"field\":\"mdmc\"},{\"value\":\"会员\",\"field\":\"hy\"},{\"value\":\"会员账号\",\"field\":\"hyzh\"},{\"value\":\"会员名称\",\"field\":\"hymc\"},{\"value\":\"顾客类型\",\"field\":\"gklx\"},{\"value\":\"消费金额\",\"field\":\"xfje\"},{\"value\":\"手工费用\",\"field\":\"sgfy\"},{\"value\":\"是否有科技部\",\"field\":\"sfykjb\"},{\"value\":\"耗卡时间\",\"field\":\"hksj\"},{\"value\":\"操作人员\",\"field\":\"czry\"},]".ToList(); ExcelConfig excelconfig = new ExcelConfig(); excelconfig.FileName = "会员耗卡.xls"; excelconfig.HeadFont = "微软雅黑"; excelconfig.HeadPoint = 10; excelconfig.IsAllSizeColumn = true; excelconfig.ColumnModel = new List(); List selectKeyList = input.selectKey.Split(',').ToList(); foreach (var item in selectKeyList) { var isExist = paramList.Find(p => p.field == item); if (isExist != null) { excelconfig.ColumnModel.Add(new ExcelColumnModel() { Column = isExist.field, ExcelColumn = isExist.value }); } } var addPath = FileVariable.TemporaryFilePath + excelconfig.FileName; ExcelExportHelper.Export(exportData, excelconfig, addPath); var fileName = _userManager.UserId + "|" + addPath + "|xls"; var output = new { name = excelconfig.FileName, url = "/api/File/Download?encryption=" + DESCEncryption.Encrypt(fileName, "NCC") }; return output; } #endregion #region 批量删除会员耗卡 /// /// 批量删除会员耗卡 /// /// 主键数组 /// [HttpPost("batchRemove")] public async Task BatchRemove([FromBody] List ids) { var entitys = await _db.Queryable().In(it => it.Id, ids).ToListAsync(); if (entitys.Count > 0) { try { //开启事务 _db.BeginTran(); //批量删除会员耗卡 await _db.Deleteable().In(d => d.Id, ids).ExecuteCommandAsync(); //清空子表数据 await _db.Deleteable().In(u => u.Glkdbh, ids).ExecuteCommandAsync(); //清空子表数据 await _db.Deleteable().In(u => u.Glkdbh, ids).ExecuteCommandAsync(); //清空子表数据 await _db.Deleteable().In(u => u.ConsumeInfoId, ids).ExecuteCommandAsync(); //关闭事务 _db.CommitTran(); } catch (Exception) { //回滚事务 _db.RollbackTran(); throw NCCException.Oh(ErrorCode.COM1002); } } } #endregion #region 更新会员耗卡 /// /// 更新会员耗卡 /// /// 主键 /// 参数 /// [HttpPut("{id}")] public async Task Update(string id, [FromBody] LqXhHyhkUpInput input) { var entity = input.Adapt(); try { //开启事务 _db.BeginTran(); //查询记录 var LqXhHyhkInfo = await _db.Queryable().Where(p => p.Id == id && p.IsEffective == StatusEnum.有效.GetHashCode()).FirstAsync(); if (LqXhHyhkInfo == null) { throw NCCException.Oh(ErrorCode.COM1005, "耗卡记录不存在或已作废"); } entity.UpdateTime = DateTime.Now; entity.OvertimeCoefficient = input.overtimeCoefficient ?? 0; entity.OriginalSgfy = input.sgfy; // 加班手工费 = 健康师原始手工费之和 × 加班系数（科技部不参与加班） var jksOriginalLaborCostSum = input.lqXhPxmxList? .SelectMany(p => p.lqXhJksyjList ?? Enumerable.Empty()) .Sum(j => j.laborCost ?? 0) ?? 0; entity.OvertimeSgfy = (decimal)(jksOriginalLaborCostSum * (entity.OvertimeCoefficient ?? 0)); entity.Sgfy = entity.OriginalSgfy + entity.OvertimeSgfy; entity.AppointmentId = input.appointmentId; //更新会员耗卡记录 await _db.Updateable(entity).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommandAsync(); var memberInfo = await _db.Queryable().Where(w => w.Id == entity.Hy).FirstAsync(); //如果会员类型是线索，那么就更新为新客 if (memberInfo.Khlx == MemberTypeEnum.线索.GetHashCode().ToString()) { memberInfo.Khlx = MemberTypeEnum.新客.GetHashCode().ToString(); } // 如果消费金额大于0，更新最后消费时间（用于沉睡天数计算） if (entity.Xfje > 0) { memberInfo.LastConsumeTime = entity.Hksj; } //保存会员信息 await _db.Updateable(memberInfo).ExecuteCommandAsync(); //清空原有数据 await _db.Deleteable().Where(u => u.Glkdbh == id).ExecuteCommandAsync(); await _db.Deleteable().Where(u => u.Glkdbh == id).ExecuteCommandAsync(); await _db.Deleteable().Where(u => u.ConsumeInfoId == id).ExecuteCommandAsync(); await _db.Deleteable().Where(u => u.BusinessId == id).ExecuteCommandAsync(); // 收集所有需要插入的实体，然后批量插入 var allPxmxEntities = new List(); var allJksyjEntities = new List(); var allKjbsyjEntities = new List(); var allPersonTimesRecordEntities = new List(); // 处理品项明细列表 if (input.lqXhPxmxList != null && input.lqXhPxmxList.Any()) { foreach (var item in input.lqXhPxmxList) { // 创建品项明细实体 var lqXhPxmxEntity = new LqXhPxmxEntity { Id = YitIdHelper.NextId().ToString(), ConsumeInfoId = input.id, BillingItemId = item.billingItemId, CreateTIme = DateTime.Now, Yjsj = input.hksj, MemberId = entity.Hy, TotalPrice = item.pxjg * (item.projectNumber ?? 1), Px = item.px, Pxmc = item.pxmc, Pxjg = item.pxjg, SourceType = item.sourceType, IsEffective = StatusEnum.有效.GetHashCode(), OriginalProjectNumber = item.projectNumber ?? 0, OvertimeProjectNumber = (decimal)(entity.OvertimeCoefficient * (item.projectNumber ?? 0)), ProjectNumber = (decimal)((item.projectNumber ?? 0) + (entity.OvertimeCoefficient * (item.projectNumber ?? 0))), ItemCategory = await _db.Queryable().Where(x => x.Id == item.px).Select(x => x.Qt2).FirstAsync(), PerformanceType = await _db.Queryable().Where(x => x.Id == item.px).Select(x => x.Fl3).FirstAsync() ?? "", BeautyType = await _db.Queryable().Where(x => x.Id == item.px).Select(x => x.BeautyType).FirstAsync(), }; allPxmxEntities.Add(lqXhPxmxEntity); // 收集该品项关联的健康师业绩 if (item.lqXhJksyjList != null && item.lqXhJksyjList.Any()) { foreach (var ijks_tem in item.lqXhJksyjList) { allJksyjEntities.Add( new LqXhJksyjEntity { Id = YitIdHelper.NextId().ToString(), Glkdbh = input.id, Jks = ijks_tem.jks, Jksxm = ijks_tem.jksxm, Jkszh = ijks_tem.jkszh, Jksyj = ijks_tem.jksyj, Yjsj = input.hksj, CreateTime = DateTime.Now, JsjId = ijks_tem.jsjId, Kdpxid = lqXhPxmxEntity.Id, OriginalKdpxNumber = ijks_tem.kdpxNumber, OvertimeKdpxNumber = (decimal)(entity.OvertimeCoefficient * (ijks_tem.kdpxNumber ?? 0)), KdpxNumber = (decimal)((ijks_tem.kdpxNumber ?? 0) + (entity.OvertimeCoefficient * (ijks_tem.kdpxNumber ?? 0))) + (ijks_tem.accompaniedProjectNumber ?? 0), OvertimeLaborCost = (decimal)(entity.OvertimeCoefficient * (ijks_tem.laborCost ?? 0)), LaborCost = ijks_tem.laborCost + (entity.OvertimeCoefficient * (ijks_tem.laborCost ?? 0)), OriginalLaborCost = ijks_tem.laborCost, IsEffective = StatusEnum.有效.GetHashCode(), IsAccompanied = ijks_tem.isAccompanied, AccompaniedProjectNumber = ijks_tem.accompaniedProjectNumber, ItemCategory = lqXhPxmxEntity.ItemCategory, ItemId = lqXhPxmxEntity.Px, StoreId = entity.Md, ItemName = lqXhPxmxEntity.Pxmc, PerformanceType = lqXhPxmxEntity.PerformanceType, BeautyType = lqXhPxmxEntity.BeautyType, } ); } } // 收集该品项关联的科技部老师业绩 if (item.lqXhKjbsyjList != null && item.lqXhKjbsyjList.Any()) { foreach (var ikjbs_tem in item.lqXhKjbsyjList) { allKjbsyjEntities.Add(new LqXhKjbsyjEntity { Id = YitIdHelper.NextId().ToString(), Glkdbh = input.id, Kjbls = ikjbs_tem.kjbls, Kjblsxm = ikjbs_tem.kjblsxm, Kjblszh = ikjbs_tem.kjblszh, Kjblsyj = ikjbs_tem.kjblsyj, Yjsj = input.hksj, CreateTime = DateTime.Now, Hkpxid = lqXhPxmxEntity.Id, // OriginalHdpxNumber = ikjbs_tem.hdpxNumber, // OvertimeHdpxNumber = (decimal)(entity.OvertimeCoefficient * (ikjbs_tem.hdpxNumber ?? 0)), // HdpxNumber = (decimal)((ikjbs_tem.hdpxNumber ?? 0) + (entity.OvertimeCoefficient * (ikjbs_tem.hdpxNumber ?? 0))), // OriginalLaborCost = ikjbs_tem.laborCost, // OvertimeLaborCost = (decimal)(entity.OvertimeCoefficient * (ikjbs_tem.laborCost ?? 0)), // LaborCost = (decimal)((ikjbs_tem.laborCost ?? 0) + (entity.OvertimeCoefficient * (ikjbs_tem.laborCost ?? 0))), OriginalHdpxNumber = ikjbs_tem.hdpxNumber, OvertimeHdpxNumber = 0, HdpxNumber = ikjbs_tem.hdpxNumber, OriginalLaborCost = ikjbs_tem.laborCost, OvertimeLaborCost = 0, LaborCost = ikjbs_tem.laborCost, IsEffective = StatusEnum.有效.GetHashCode(), ItemCategory = lqXhPxmxEntity.ItemCategory, ItemId = lqXhPxmxEntity.Px, StoreId = entity.Md, ItemName = lqXhPxmxEntity.Pxmc, PerformanceType = lqXhPxmxEntity.PerformanceType, BeautyType = lqXhPxmxEntity.BeautyType, }); } } } } //查询开单记录 var billingRecord = await _db.Queryable().Where(x => x.Kdhy == entity.Hy && x.IsEffective == StatusEnum.有效.GetHashCode() && x.Sfyj > 0).AnyAsync(); //剔除所有T区健康师（姓名中包含"T区"的健康师） var NoTallJksyjEntities = allJksyjEntities.Where(x => x.Jks != null && x.Jks != "" && (x.Jksxm == null || !x.Jksxm.Contains("T区"))).ToList(); //获取这次耗卡有多少个健康师 var jksCount = NoTallJksyjEntities.GroupBy(x => x.Jks).Count(); //添加到人次表里面去 foreach (var item in NoTallJksyjEntities.Select(x => x.Jks).Distinct()) { LqPersonTimesRecordEntity personTimesRecordEntity = new LqPersonTimesRecordEntity { Id = YitIdHelper.NextId().ToString(), BusinessId = input.id, BusinessType = "耗卡", PersonType = "健康师", PersonId = item, PersonName = NoTallJksyjEntities.Where(x => x.Jks == item).Select(x => x.Jksxm).First(), MemberId = entity.Hy, MemberName = memberInfo.Khmc, WorkDate = input.hksj, WorkMonth = input.hksj.ToDate().ToString("yyyyMM"), Quantity = 1 / (decimal)jksCount, CreateTime = DateTime.Now, IsEffective = StatusEnum.有效.GetHashCode(), HasBilling = billingRecord ? 1 : 0, }; allPersonTimesRecordEntities.Add(personTimesRecordEntity); } //剔除所有T区科技部老师（姓名中包含"T区"的科技部老师） var NoTallKjbsyjEntities = allKjbsyjEntities.Where(x => x.Kjbls != null && x.Kjbls != "" && (x.Kjblsxm == null || !x.Kjblsxm.Contains("T区"))).ToList(); //获取这次耗卡有多少个科技部老师 var kjbCount = NoTallKjbsyjEntities.GroupBy(x => x.Kjbls).Count(); //添加到人次表里面去 foreach (var item in NoTallKjbsyjEntities.Select(x => x.Kjbls).Distinct()) { LqPersonTimesRecordEntity personTimesRecordEntity = new LqPersonTimesRecordEntity { Id = YitIdHelper.NextId().ToString(), BusinessId = input.id, BusinessType = "耗卡", PersonType = "科技部老师", PersonId = item, PersonName = NoTallKjbsyjEntities.Where(x => x.Kjbls == item).Select(x => x.Kjblsxm).First(), MemberId = entity.Hy, MemberName = memberInfo.Khmc, WorkDate = input.hksj, WorkMonth = input.hksj.ToDate().ToString("yyyyMM"), Quantity = 1 / (decimal)kjbCount, CreateTime = DateTime.Now, IsEffective = StatusEnum.有效.GetHashCode(), HasBilling = billingRecord ? 1 : 0, }; allPersonTimesRecordEntities.Add(personTimesRecordEntity); } // 批量插入人次记录 if (allPersonTimesRecordEntities.Any()) { await _db.Insertable(allPersonTimesRecordEntities).ExecuteCommandAsync(); } // 批量插入品项明细 if (allPxmxEntities.Any()) { await _db.Insertable(allPxmxEntities).ExecuteCommandAsync(); } // 批量插入健康师业绩 if (allJksyjEntities.Any()) { await _db.Insertable(allJksyjEntities).ExecuteCommandAsync(); } // 批量插入科技部老师业绩 if (allKjbsyjEntities.Any()) { await _db.Insertable(allKjbsyjEntities).ExecuteCommandAsync(); } // 提交事务 _db.CommitTran(); } catch (Exception) { //回滚事务 _db.RollbackTran(); throw NCCException.Oh(ErrorCode.COM1001); } } #endregion #region 更新耗卡备注 /// /// 更新耗卡备注 /// /// /// 专门用于更新耗卡记录的备注信息 /// /// 示例请求： /// PUT /api/Extend/LqXhHyhk/{id}/UpdateRemark /// /// 请求体： /// ```json /// { /// "remark": "备注信息（最大2000字符）" /// } /// ``` /// /// 耗卡记录ID /// 备注信息 /// 无返回值 /// 更新成功 /// 参数错误 /// 耗卡记录不存在 /// 服务器错误 [HttpPut("{id}/UpdateRemark")] public async Task UpdateRemark(string id, [FromBody] LqXhHyhkUpdateRemarkInput input) { try { // 查询记录 var entity = await _db.Queryable() .Where(p => p.Id == id && p.IsEffective == StatusEnum.有效.GetHashCode()) .FirstAsync(); if (entity == null) { throw NCCException.Oh(ErrorCode.COM1005, "耗卡记录不存在或已作废"); } // 更新备注 entity.Remark = input.remark; entity.UpdateTime = DateTime.Now; // 保存更新 await _db.Updateable(entity) .UpdateColumns(it => new { it.Remark, it.UpdateTime }) .ExecuteCommandAsync(); _logger.LogInformation($"更新耗卡备注成功，ID：{id}"); } catch (Exception ex) { _logger.LogError(ex, "更新耗卡备注失败，ID：{Id}", id); throw; } } #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); try { //开启事务 _db.BeginTran(); //删除会员耗卡记录 await _db.Deleteable().Where(d => d.Id == id).ExecuteCommandAsync(); //清空子表数据 await _db.Deleteable().Where(u => u.Glkdbh == id).ExecuteCommandAsync(); //清空子表数据 await _db.Deleteable().Where(u => u.Glkdbh == id).ExecuteCommandAsync(); //清空子表数据 await _db.Deleteable().Where(u => u.ConsumeInfoId == id).ExecuteCommandAsync(); //清空子表数据 await _db.Deleteable().Where(u => u.BusinessId == id).ExecuteCommandAsync(); //关闭事务 _db.CommitTran(); } catch (Exception) { //回滚事务 _db.RollbackTran(); throw NCCException.Oh(ErrorCode.COM1002); } } #endregion #region 修改加班系数 /// /// 修改消耗单的加班系数 /// /// /// 只修改加班系数，自动重新计算所有相关的加班字段 /// /// 计算逻辑： /// 1. 主表（lq_xh_hyhk）： /// - 加班手工费 = 健康师原始手工费之和 × 新加班系数（科技部不参与加班） /// - 最终手工费 = 原始手工费 + 加班手工费 /// /// 2. 品项明细表（lq_xh_pxmx）： /// - 加班项目次数 = 原始项目次数 × 新加班系数 /// - 最终项目次数 = 原始项目次数 + 加班项目次数 /// /// 3. 健康师业绩表（lq_xh_jksyj）： /// - 加班耗卡品项次数 = 原始耗卡品项次数 × 新加班系数 /// - 最终耗卡品项次数 = 原始耗卡品项次数 + 加班耗卡品项次数 + 陪同项目次数 /// - 加班手工费 = 原始手工费 × 新加班系数 /// - 最终手工费 = 原始手工费 + 加班手工费 /// /// 示例请求： /// ```json /// { /// "overtimeCoefficient": 0.5 /// } /// ``` /// /// 耗卡编号 /// 参数 /// 无返回值 /// 修改成功 /// 参数错误或数据验证失败 /// 服务器内部错误 [HttpPut("{id}/overtime-coefficient")] public async Task UpdateOvertimeCoefficient(string id, [FromBody] LqXhHyhkUpdateOvertimeInput input) { try { // 开启事务 _db.BeginTran(); // 1. 查询主表记录 var entity = await _db.Queryable() .Where(p => p.Id == id && p.IsEffective == StatusEnum.有效.GetHashCode()) .FirstAsync(); if (entity == null) { throw NCCException.Oh(ErrorCode.COM1005, "耗卡记录不存在或已作废"); } // 验证原始手工费是否存在 if (entity.OriginalSgfy == null || entity.OriginalSgfy == 0) { // 如果原始手工费为空，使用当前的手工费作为原始值 if (entity.Sgfy != null && entity.Sgfy > 0) { entity.OriginalSgfy = entity.Sgfy; } else { throw NCCException.Oh("原始手工费不存在，无法修改加班系数"); } } // 2. 查询健康师业绩，计算主表加班手工费（科技部不参与加班） var jksyjList = await _db.Queryable() .Where(x => x.Glkdbh == id && x.IsEffective == StatusEnum.有效.GetHashCode()) .ToListAsync(); var jksOriginalLaborCostSum = jksyjList.Sum(j => { var original = j.OriginalLaborCost ?? 0; if (original == 0 && (j.LaborCost ?? 0) > 0) { original = (j.LaborCost ?? 0) - (j.OvertimeLaborCost ?? 0); if (original < 0) original = 0; } return original; }); // 3. 更新主表加班系数和相关字段 var newCoefficient = input.overtimeCoefficient ?? 0; var originalSgfy = entity.OriginalSgfy ?? 0; entity.OvertimeCoefficient = newCoefficient; entity.OvertimeSgfy = (decimal)(jksOriginalLaborCostSum * newCoefficient); entity.Sgfy = originalSgfy + (entity.OvertimeSgfy ?? 0); entity.UpdateTime = DateTime.Now; await _db.Updateable(entity) .UpdateColumns(x => new { x.OvertimeCoefficient, x.OvertimeSgfy, x.Sgfy, x.UpdateTime }) .ExecuteCommandAsync(); // 4. 查询所有品项明细，更新加班字段 var pxmxList = await _db.Queryable() .Where(x => x.ConsumeInfoId == id && x.IsEffective == StatusEnum.有效.GetHashCode()) .ToListAsync(); foreach (var pxmx in pxmxList) { // 如果原始项目次数为空，使用当前项目次数作为原始值 if (pxmx.OriginalProjectNumber == null || pxmx.OriginalProjectNumber == 0) { if (pxmx.ProjectNumber != null && pxmx.ProjectNumber > 0) { pxmx.OriginalProjectNumber = pxmx.ProjectNumber; } else { pxmx.OriginalProjectNumber = 0; } } var originalProjectNumber = pxmx.OriginalProjectNumber ?? 0; pxmx.OvertimeProjectNumber = (decimal)(originalProjectNumber * newCoefficient); pxmx.ProjectNumber = originalProjectNumber + (pxmx.OvertimeProjectNumber ?? 0); await _db.Updateable(pxmx) .UpdateColumns(x => new { x.OvertimeProjectNumber, x.ProjectNumber }) .ExecuteCommandAsync(); } // 5. 更新健康师业绩加班字段（jksyjList 已在步骤2查询） foreach (var jksyj in jksyjList) { // 如果原始耗卡品项次数为空，使用当前值作为原始值 if (jksyj.OriginalKdpxNumber == null || jksyj.OriginalKdpxNumber == 0) { // 从最终值中减去陪同项目次数和加班值，得到原始值 var currentKdpxNumber = jksyj.KdpxNumber ?? 0; var accompaniedNumber = jksyj.AccompaniedProjectNumber ?? 0; var currentOvertime = jksyj.OvertimeKdpxNumber ?? 0; jksyj.OriginalKdpxNumber = currentKdpxNumber - accompaniedNumber - currentOvertime; if (jksyj.OriginalKdpxNumber < 0) { jksyj.OriginalKdpxNumber = 0; } } // 如果原始手工费为空，使用当前值作为原始值 if (jksyj.OriginalLaborCost == null || jksyj.OriginalLaborCost == 0) { var currentLaborCost = jksyj.LaborCost ?? 0; var currentOvertimeLaborCost = jksyj.OvertimeLaborCost ?? 0; jksyj.OriginalLaborCost = currentLaborCost - currentOvertimeLaborCost; if (jksyj.OriginalLaborCost < 0) { jksyj.OriginalLaborCost = 0; } } // 重新计算加班字段 var originalKdpxNumber = jksyj.OriginalKdpxNumber ?? 0; var originalLaborCost = jksyj.OriginalLaborCost ?? 0; jksyj.OvertimeKdpxNumber = (decimal)(originalKdpxNumber * newCoefficient); var accompaniedNumberForCalc = jksyj.AccompaniedProjectNumber ?? 0; jksyj.KdpxNumber = originalKdpxNumber + (jksyj.OvertimeKdpxNumber ?? 0) + accompaniedNumberForCalc; jksyj.OvertimeLaborCost = (decimal)(originalLaborCost * newCoefficient); jksyj.LaborCost = originalLaborCost + (jksyj.OvertimeLaborCost ?? 0); await _db.Updateable(jksyj) .UpdateColumns(x => new { x.OvertimeKdpxNumber, x.KdpxNumber, x.OvertimeLaborCost, x.LaborCost }) .ExecuteCommandAsync(); } // 6. 科技部老师业绩表：当前代码中不参与加班计算，保持原值不变 // 如果需要支持，可以取消注释以下代码 /* var kjbsyjList = await _db.Queryable() .Where(x => x.Glkdbh == id && x.IsEffective == StatusEnum.有效.GetHashCode()) .ToListAsync(); foreach (var kjbsyj in kjbsyjList) { if (kjbsyj.OriginalHdpxNumber == null || kjbsyj.OriginalHdpxNumber == 0) { var currentHdpxNumber = kjbsyj.HdpxNumber ?? 0; var currentOvertime = kjbsyj.OvertimeHdpxNumber ?? 0; kjbsyj.OriginalHdpxNumber = currentHdpxNumber - currentOvertime; if (kjbsyj.OriginalHdpxNumber < 0) { kjbsyj.OriginalHdpxNumber = 0; } } if (kjbsyj.OriginalLaborCost == null || kjbsyj.OriginalLaborCost == 0) { var currentLaborCost = kjbsyj.LaborCost ?? 0; var currentOvertimeLaborCost = kjbsyj.OvertimeLaborCost ?? 0; kjbsyj.OriginalLaborCost = currentLaborCost - currentOvertimeLaborCost; if (kjbsyj.OriginalLaborCost < 0) { kjbsyj.OriginalLaborCost = 0; } } kjbsyj.OvertimeHdpxNumber = (decimal)(kjbsyj.OriginalHdpxNumber * newCoefficient); kjbsyj.HdpxNumber = kjbsyj.OriginalHdpxNumber + kjbsyj.OvertimeHdpxNumber; kjbsyj.OvertimeLaborCost = (decimal)(kjbsyj.OriginalLaborCost * newCoefficient); kjbsyj.LaborCost = kjbsyj.OriginalLaborCost + kjbsyj.OvertimeLaborCost; await _db.Updateable(kjbsyj) .UpdateColumns(x => new { x.OvertimeHdpxNumber, x.HdpxNumber, x.OvertimeLaborCost, x.LaborCost }) .ExecuteCommandAsync(); } */ // 提交事务 _db.CommitTran(); } catch (Exception ex) { // 回滚事务 _db.RollbackTran(); throw; } } #endregion #region 查询健康师消耗业绩列表 /// /// 查询健康师业绩列表 /// /// 查询参数 /// 分页的健康师业绩列表 /// /// 查询健康师业绩记录，支持分页和时间筛选 /// /// 示例请求： /// ```json /// { /// "glkdbh": "123456789", /// "jksId": "健康师ID", /// "startTime": "2025-01-01T00:00:00", /// "endTime": "2025-01-31T23:59:59", /// "currentPage": 1, /// "pageSize": 10, /// "sidx": "yjsj", /// "sort": "desc" /// } /// ``` /// /// 参数说明： /// - glkdbh: 开单记录ID（可选） /// - jksId: 健康师ID（可选） /// - startTime: 查询开始时间（可选） /// - endTime: 查询结束时间（可选） /// - currentPage: 当前页码 /// - pageSize: 每页大小 /// /// 成功获取健康师业绩列表 /// 请求参数错误 /// 服务器内部错误 [HttpGet("GetJksyjList")] public async Task GetJksyjList([FromQuery] LqXhJksyjQueryInput input) { try { var sidx = string.IsNullOrEmpty(input.sidx) ? "yjsj" : input.sidx; var sort = string.IsNullOrEmpty(input.sort) ? "desc" : input.sort; var data = await _db.Queryable() .WhereIF(!string.IsNullOrEmpty(input.Glkdbh), w => w.Glkdbh == input.Glkdbh) .WhereIF(!string.IsNullOrEmpty(input.JksId), w => w.Jks == input.JksId) .WhereIF(input.StartTime.HasValue, w => w.Yjsj >= input.StartTime.Value) .WhereIF(input.EndTime.HasValue, w => w.Yjsj <= input.EndTime.Value) .Where(w => w.IsEffective == StatusEnum.有效.GetHashCode()) .Select(it => new LqXhJksyjInfoOutput { id = it.Id, glkdbh = it.Glkdbh, jks = it.Jks, jksxm = it.Jksxm, jkszh = it.Jkszh, jksyj = it.Jksyj.ToString(), yjsj = it.Yjsj, jsjId = it.JsjId, kdpxid = it.Kdpxid, laborCost = it.LaborCost, kdpxNumber = it.KdpxNumber, storeName = SqlFunc.Subqueryable().Where(w => w.Id == it.Glkdbh).Select(w => w.Mdmc), memberId = SqlFunc.Subqueryable().Where(w => w.Id == it.Glkdbh).Select(w => w.Hy), memberName = SqlFunc.Subqueryable().Where(w => w.Id == it.Glkdbh).Select(w => w.Hymc), }) .MergeTable() .OrderBy(sidx + " " + sort) .ToPagedListAsync(input.currentPage, input.pageSize); return PageResult.SqlSugarPageResult(data); } catch (Exception ex) { throw NCCException.Oh($"获取健康师业绩列表失败: {ex.Message}"); } } #endregion #region 耗卡记录作废 /// /// 耗卡记录作废 /// /// 耗卡记录ID /// 作废备注 /// [HttpPut("CancelCardUsageRecord/{id}")] public async Task CancelCardUsageRecord(string id, [FromQuery] string remarks = null) { try { //开启事务 _db.BeginTran(); //查询消耗记录表信息 var LqhyhkInfo = await _db.Queryable().Where(p => p.Id == id && p.IsEffective == StatusEnum.有效.GetHashCode()).FirstAsync(); if (LqhyhkInfo == null) { throw NCCException.Oh(ErrorCode.COM1005, "耗卡记录不存在或已作废"); } //更新耗卡记录状态 LqhyhkInfo.IsEffective = StatusEnum.无效.GetHashCode(); LqhyhkInfo.UpdateTime = DateTime.Now; LqhyhkInfo.CancelRemark = remarks; await _db.Updateable(LqhyhkInfo).ExecuteCommandAsync(); //更新品项明细表状态 await _db.Updateable().SetColumns(it => new LqXhPxmxEntity { IsEffective = StatusEnum.无效.GetHashCode() }).Where(w => w.ConsumeInfoId == id).ExecuteCommandAsync(); //更新健康师业绩表状态 await _db.Updateable().SetColumns(it => new LqXhJksyjEntity { IsEffective = StatusEnum.无效.GetHashCode() }).Where(w => w.Glkdbh == id).ExecuteCommandAsync(); //更新科技部老师业绩表状态 await _db.Updateable().SetColumns(it => new LqXhKjbsyjEntity { IsEffective = StatusEnum.无效.GetHashCode() }).Where(w => w.Glkdbh == id).ExecuteCommandAsync(); //更新人次记录表状态 await _db.Updateable().SetColumns(it => new LqPersonTimesRecordEntity { IsEffective = StatusEnum.无效.GetHashCode() }).Where(w => w.BusinessId == id).ExecuteCommandAsync(); //关闭事务 _db.CommitTran(); return LqhyhkInfo; } catch (Exception) { //回滚事务 _db.RollbackTran(); throw NCCException.Oh(ErrorCode.COM1001); } } #endregion #region 获取某个会员的耗卡记录 /// /// 获取某个会员的耗卡记录（分页） /// /// 查询参数 /// [HttpGet("GetMemberCardUsageRecord")] public async Task GetMemberCardUsageRecord([FromQuery] LqXhHyhkMemberCardUsageQueryInput input) { try { // 参数验证 if (string.IsNullOrEmpty(input.MemberId)) { throw NCCException.Oh("会员ID不能为空"); } // 分页查询会员耗卡记录 var data = await _db.Queryable() .Where(p => p.Hy == input.MemberId && p.IsEffective == StatusEnum.有效.GetHashCode()) .WhereIF(!string.IsNullOrEmpty(input.StoreId), p => p.Md == input.StoreId) .WhereIF(input.StartTime.HasValue, p => p.Hksj >= input.StartTime.Value) .WhereIF(input.EndTime.HasValue, p => p.Hksj <= input.EndTime.Value) .Select(it => new LqXhHyhkMemberCardUsageOutput { Id = it.Id, MemberId = it.Hy, MemberName = SqlFunc.Subqueryable().Where(w => w.Id == it.Hy).Select(w => w.Khmc), StoreId = it.Md, StoreName = it.Mdmc, UsageDate = it.Hksj, TotalAmount = it.Xfje, Remarks = it.SignatureFile, CreateTime = it.CreateTime }) .MergeTable() .OrderBy((input.sidx == null ? "UsageDate" : input.sidx) + " " + (input.sort ?? "desc")) .ToPagedListAsync(input.currentPage, input.pageSize); // 获取当前页耗卡记录ID，直接查询对应的品项明细 if (data.list.Any()) { var recordIds = data.list.Select(r => r.Id).ToList(); // 批量查询当前页记录的所有品项明细 var itemDetails = await _db.Queryable().Where(w => recordIds.Contains(w.ConsumeInfoId) && w.IsEffective == StatusEnum.有效.GetHashCode()).ToListAsync(); // 按耗卡记录ID分组明细 var detailsGrouped = itemDetails.GroupBy(d => d.ConsumeInfoId).ToDictionary(g => g.Key, g => g.ToList()); // 为每个记录添加品项明细 foreach (var record in data.list) { if (detailsGrouped.ContainsKey(record.Id)) { record.ItemDetails = detailsGrouped[record.Id].Select(detail => new LqXhHyhkMemberCardUsageItemDetail { Id = detail.Id, ItemId = detail.Px, ItemName = detail.Pxmc, UnitPrice = detail.Pxjg, ProjectNumber = detail.ProjectNumber, TotalPrice = detail.TotalPrice, SourceType = detail.SourceType, CreateTime = detail.CreateTIme }).ToList(); } } } return PageResult.SqlSugarPageResult(data); } catch (Exception ex) { throw NCCException.Oh($"获取会员耗卡记录失败: {ex.Message}"); } } #endregion #region 同步耗卡数据到人次记录表（同步的时候才用） /// /// 同步耗卡数据到人次记录表（同步的时候才用） /// /// /// 将耗卡记录中的健康师和科技老师业绩数据同步到人次记录表（lq_person_times_record） /// /// 同步逻辑： /// 1. 查询所有有效的耗卡记录 /// 2. 对于每条耗卡记录，查询关联的健康师业绩和科技老师业绩 /// 3. 对健康师和科技老师进行去重（按人员ID） /// 4. 为每个去重后的健康师和科技老师创建人次记录 /// 5. 计算人次数量：1 / 健康师（或科技老师）数量 /// /// 字段映射： /// - F_BusinessId: 耗卡ID（lq_xh_hyhk.F_Id） /// - F_BusinessType: "耗卡" /// - F_PersonType: "健康师" 或 "科技老师" /// - F_PersonId: 健康师ID（lq_xh_jksyj.jks）或科技老师ID（lq_xh_kjbsyj.kjbls） /// - F_PersonName: 健康师姓名（lq_xh_jksyj.jksxm）或科技老师姓名（lq_xh_kjbsyj.kjblsxm） /// - F_MemberId: 客户ID（lq_xh_hyhk.hy） /// - F_MemberName: 客户姓名（lq_xh_hyhk.hymc） /// - F_WorkDate: 耗卡时间的日期部分（lq_xh_hyhk.hksj） /// - F_WorkMonth: 耗卡时间的月份部分，格式：YYYYMM（lq_xh_hyhk.hksj） /// - F_Quantity: 人次数量 = 1 / 健康师（或科技老师）数量 /// /// 注意事项： /// - 只同步有效的耗卡记录（F_IsEffective = 1） /// - 只同步有效的健康师业绩和科技老师业绩（F_IsEffective = 1） /// - 如果已存在相同业务ID的记录，会先删除再重新插入 /// /// 可选，指定耗卡ID，如果为空则同步所有耗卡数据 /// 同步结果，包含同步的记录数 /// 同步成功 /// 服务器内部错误 [HttpPost("sync-person-times-record")] public async Task SyncPersonTimesRecord([FromQuery] string consumeId = null) { try { _logger.LogInformation($"开始同步耗卡数据到人次记录表，耗卡ID: {consumeId ?? "全部"}"); // 1. 查询耗卡记录 var consumeQuery = _db.Queryable().Where(x => x.IsEffective == StatusEnum.有效.GetHashCode()); if (!string.IsNullOrEmpty(consumeId)) { consumeQuery = consumeQuery.Where(x => x.Id == consumeId); } var consumeList = await consumeQuery.ToListAsync(); if (!consumeList.Any()) { return new { success = true, message = "没有需要同步的耗卡记录", count = 0 }; } _logger.LogInformation($"找到 {consumeList.Count} 条耗卡记录需要同步"); // 2. 批量查询关联数据 var consumeIds = consumeList.Select(x => x.Id).ToList(); // 查询健康师业绩 var jksyjList = await _db.Queryable().Where(x => consumeIds.Contains(x.Glkdbh) && x.IsEffective == StatusEnum.有效.GetHashCode()).ToListAsync(); // 查询科技老师业绩 var kjbsyjList = await _db.Queryable().Where(x => consumeIds.Contains(x.Glkdbh) && x.IsEffective == StatusEnum.有效.GetHashCode()).ToListAsync(); // 查询已存在的人次记录（按BusinessId去重，避免重复添加） var existingBusinessIds = await _db.Queryable().Where(x => consumeIds.Contains(x.BusinessId) && x.BusinessType == "耗卡" && x.IsEffective == StatusEnum.有效.GetHashCode()).Select(x => x.BusinessId).Distinct().ToListAsync(); // 批量查询所有会员是否有开单记录（优化：避免在循环中执行N次查询） var memberIds = consumeList.Where(x => !string.IsNullOrEmpty(x.Hy)).Select(x => x.Hy).Distinct().ToList(); var membersWithBilling = new HashSet(); if (memberIds.Any()) { var billingMemberIds = await _db.Queryable() .Where(x => memberIds.Contains(x.Kdhy) && x.IsEffective == StatusEnum.有效.GetHashCode() && x.Sfyj > 0) .Select(x => x.Kdhy) .Distinct() .ToListAsync(); membersWithBilling = new HashSet(billingMemberIds); } // 3. 构建人次记录列表 var personTimesRecords = new List(); foreach (var consume in consumeList) { // 如果该耗卡记录已存在，跳过 if (existingBusinessIds.Contains(consume.Id)) { _logger.LogInformation($"耗卡记录 {consume.Id} 已存在人次记录，跳过"); continue; } if (consume.Hksj == null) { _logger.LogWarning($"耗卡记录 {consume.Id} 的耗卡时间为空，跳过"); continue; } var workDate = consume.Hksj.Value.Date; // 工作日期（用于人次统计） var workMonth = consume.Hksj.Value.ToString("yyyyMM"); // 工作月份（用于人头统计） //查看该会员是否在开单记录表中存在开单记录（从批量查询结果中查找） var billingRecord = !string.IsNullOrEmpty(consume.Hy) && membersWithBilling.Contains(consume.Hy); // 处理健康师业绩：去重后计算人次数量（剔除T区健康师） var consumeJksyjList = jksyjList.Where(x => x.Glkdbh == consume.Id && !string.IsNullOrEmpty(x.Jks) && !string.IsNullOrEmpty(x.Jksxm) && (x.Jksxm == null || !x.Jksxm.Contains("T区"))).ToList(); if (consumeJksyjList.Any()) { // 按健康师ID去重 var distinctJksyjList = consumeJksyjList.GroupBy(x => x.Jks).Select(g => g.First()).ToList(); // 计算人次数量：1 / 健康师数量 var jksQuantity = distinctJksyjList.Count > 0 ? 1.0m / distinctJksyjList.Count : 0; foreach (var jksyj in distinctJksyjList) { personTimesRecords.Add(new LqPersonTimesRecordEntity { Id = YitIdHelper.NextId().ToString(), BusinessId = consume.Id, BusinessType = "耗卡", PersonType = "健康师", PersonId = jksyj.Jks, PersonName = jksyj.Jksxm, MemberId = consume.Hy, MemberName = consume.Hymc, WorkDate = workDate, WorkMonth = workMonth, Quantity = jksQuantity, CreateTime = DateTime.Now, IsEffective = StatusEnum.有效.GetHashCode(), HasBilling = billingRecord ? 1 : 0 }); } } // 处理科技老师业绩：去重后计算人次数量（剔除T区科技老师） var consumeKjbsyjList = kjbsyjList.Where(x => x.Glkdbh == consume.Id && !string.IsNullOrEmpty(x.Kjbls) && !string.IsNullOrEmpty(x.Kjblsxm) && (x.Kjblsxm == null || !x.Kjblsxm.Contains("T区"))).ToList(); if (consumeKjbsyjList.Any()) { // 按科技老师ID去重 var distinctKjbsyjList = consumeKjbsyjList.GroupBy(x => x.Kjbls).Select(g => g.First()).ToList(); // 计算人次数量：1 / 科技老师数量 var kjbsQuantity = distinctKjbsyjList.Count > 0 ? 1.0m / distinctKjbsyjList.Count : 0; foreach (var kjbsyj in distinctKjbsyjList) { personTimesRecords.Add(new LqPersonTimesRecordEntity { Id = YitIdHelper.NextId().ToString(), BusinessId = consume.Id, BusinessType = "耗卡", PersonType = "科技老师", PersonId = kjbsyj.Kjbls, PersonName = kjbsyj.Kjblsxm, MemberId = consume.Hy, MemberName = consume.Hymc, WorkDate = workDate, WorkMonth = workMonth, Quantity = kjbsQuantity, CreateTime = DateTime.Now, IsEffective = StatusEnum.有效.GetHashCode(), HasBilling = billingRecord ? 1 : 0 }); } } } // 4. 使用事务保存数据 var result = await _db.Ado.UseTranAsync(async () => { // 如果指定了耗卡ID，删除该耗卡的旧记录（用于重新同步） // 如果没有指定耗卡ID，不删除任何记录（因为已经在构建记录列表时跳过了已存在的记录） if (!string.IsNullOrEmpty(consumeId)) { await _db.Deleteable().Where(x => x.BusinessId == consumeId && x.BusinessType == "耗卡").ExecuteCommandAsync(); } // 批量插入新记录 if (personTimesRecords.Any()) { // 分批插入，每批1000条 var batchSize = 1000; for (int i = 0; i < personTimesRecords.Count; i += batchSize) { var batch = personTimesRecords.Skip(i).Take(batchSize).ToList(); await _db.Insertable(batch).ExecuteCommandAsync(); } } return personTimesRecords.Count; }); if (result.IsSuccess) { _logger.LogInformation($"同步完成，共同步 {result.Data} 条人次记录"); return new { success = true, message = "同步成功", count = result.Data }; } else { _logger.LogError($"同步失败: {result.ErrorMessage}"); throw NCCException.Oh(ErrorCode.COM1000, $"同步失败: {result.ErrorMessage}"); } } catch (Exception ex) { _logger.LogError(ex, $"同步耗卡数据到人次记录表失败: {ex.ToString()}"); throw NCCException.Oh(ErrorCode.COM1005, $"同步耗卡数据到人次记录表失败: {ex.Message}"); } } #endregion #region 耗卡品项明细列表 /// /// 获取耗卡品项明细记录列表 /// /// /// 根据多种条件查询耗卡品项明细记录列表，支持分页、排序、筛选等功能 /// /// 示例请求： /// GET /api/Extend/LqXhHyhk/consume-item-detail-list?currentPage=1&pageSize=10&startTime=2025-01-01&endTime=2025-01-31 /// /// 传入参数说明： /// - currentPage: 当前页码（必填，默认1） /// - pageSize: 每页数量（必填，默认10） /// - sidx: 排序字段（可选，默认"yjsj"） /// - sort: 排序方式（可选，ASC/DESC，默认DESC） /// - Id: 品项明细ID（可选，精确匹配） /// - ConsumeId: 耗卡记录ID（可选，精确匹配） /// - StartConsumeTime/EndConsumeTime: 耗卡时间范围（可选，格式：yyyy-MM-dd） /// - startTime/endTime: 耗卡时间范围（兼容参数名，格式：yyyy-MM-dd） /// - MemberId: 会员ID（可选，精确匹配） /// - MemberName: 会员名称（可选，模糊查询） /// - MemberPhone: 会员手机号（可选，精确匹配） /// - ItemId: 品项ID（可选，精确匹配） /// - ItemName: 品项名称（可选，模糊查询） /// - ItemType: 品项类型（可选，精确匹配） /// - SourceType: 来源类型（可选，精确匹配） /// - StoreId: 门店ID（可选，精确匹配） /// /// 返回字段说明： /// - id: 品项明细ID /// - consumeId: 耗卡记录ID /// - consumeTime: 耗卡时间（DateTime格式） /// - memberName: 会员名称（关联会员表） /// - memberPhone: 会员手机号（关联会员表） /// - itemName: 品项名称（品项明细表字段） /// - itemType: 品项类型（关联项目资料表的qt2字段） /// - itemPrice: 品项价格（decimal类型） /// - projectNumber: 项目次数（decimal类型，支持小数） /// - originalProjectNumber: 原始项目次数（decimal类型） /// - totalPrice: 合计金额（decimal类型） /// - sourceType: 来源类型（字符串，如：购买、赠送、体验） /// - storeId: 门店ID（关联耗卡记录表的门店ID） /// - storeName: 门店名称（关联门店表的店名字段） /// - lqXhJksyjList: 健康师业绩列表（关联健康师业绩表，通过F_kdpxid关联品项明细ID） /// /// 查询参数 /// 耗卡品项明细记录列表（分页） /// 查询成功，返回耗卡品项明细记录列表 /// 参数错误，如页码或页大小无效 /// 服务器错误，查询过程中发生异常 [HttpGet("consume-item-detail-list")] public async Task GetConsumeItemDetailList([FromQuery] ConsumeItemDetailListQueryInput input) { try { var sidx = input.sidx == null ? "yjsj" : input.sidx; var sort = string.IsNullOrEmpty(input.sort) ? "DESC" : input.sort; // 处理耗卡时间范围（兼容 StartConsumeTime/EndConsumeTime 和 startTime/endTime 两种参数名） DateTime? startConsumeTime = null; DateTime? endConsumeTime = null; // 优先使用 StartConsumeTime/EndConsumeTime，如果没有则使用 startTime/endTime string startTimeStr = !string.IsNullOrEmpty(input.StartConsumeTime) ? input.StartConsumeTime : input.startTime; string endTimeStr = !string.IsNullOrEmpty(input.EndConsumeTime) ? input.EndConsumeTime : input.endTime; if (!string.IsNullOrEmpty(startTimeStr) && !string.IsNullOrEmpty(endTimeStr)) { // 尝试解析日期字符串（支持多种格式） if (DateTime.TryParse(startTimeStr, out DateTime startDate)) { startConsumeTime = startDate; } else { throw NCCException.Oh($"开始时间格式错误：{startTimeStr}"); } if (DateTime.TryParse(endTimeStr, out DateTime endDate)) { endConsumeTime = endDate; } else { throw NCCException.Oh($"结束时间格式错误：{endTimeStr}"); } } // 优化查询：先分页查询主表，再批量查询关联数据，避免子查询性能问题 // 1. 先构建基础查询条件 var baseQuery = _db.Queryable() .Where(pxmx => pxmx.IsEffective == StatusEnum.有效.GetHashCode()) .WhereIF(!string.IsNullOrEmpty(input.Id), pxmx => pxmx.Id == input.Id) .WhereIF(!string.IsNullOrEmpty(input.ConsumeId), pxmx => pxmx.ConsumeInfoId == input.ConsumeId) .WhereIF(startConsumeTime.HasValue, pxmx => pxmx.Yjsj >= new DateTime(startConsumeTime.Value.Year, startConsumeTime.Value.Month, startConsumeTime.Value.Day, 0, 0, 0)) .WhereIF(endConsumeTime.HasValue, pxmx => pxmx.Yjsj <= new DateTime(endConsumeTime.Value.Year, endConsumeTime.Value.Month, endConsumeTime.Value.Day, 23, 59, 59)) .WhereIF(!string.IsNullOrEmpty(input.MemberId), pxmx => pxmx.MemberId == input.MemberId) .WhereIF(!string.IsNullOrEmpty(input.ItemId), pxmx => pxmx.Px == input.ItemId) .WhereIF(!string.IsNullOrEmpty(input.ItemName), pxmx => pxmx.Pxmc != null && pxmx.Pxmc.Contains(input.ItemName)) .WhereIF(!string.IsNullOrEmpty(input.SourceType), pxmx => pxmx.SourceType == input.SourceType) .WhereIF(!string.IsNullOrEmpty(input.ItemType), pxmx => pxmx.ItemCategory == input.ItemType); // 2. 通过 EXISTS 子查询筛选关联字段（在分页前筛选，确保分页准确） // 处理多门店筛选：优先使用StoreIds，如果没有则使用StoreId var filterStoreIds = new List(); if (!string.IsNullOrEmpty(input.StoreIds)) { // 解析逗号分隔的门店ID字符串 filterStoreIds = input.StoreIds.Split(',') .Where(x => !string.IsNullOrWhiteSpace(x)) .Select(x => x.Trim()) .ToList(); } else if (!string.IsNullOrEmpty(input.StoreId)) { filterStoreIds = new List { input.StoreId }; } baseQuery = baseQuery.WhereIF(!string.IsNullOrEmpty(input.MemberName), pxmx => SqlFunc.Subqueryable().Where(x => x.Id == pxmx.MemberId && x.Khmc != null && x.Khmc.Contains(input.MemberName)).Any()) .WhereIF(!string.IsNullOrEmpty(input.MemberPhone), pxmx => SqlFunc.Subqueryable().Where(x => x.Id == pxmx.MemberId && x.Sjh == input.MemberPhone).Any()) .WhereIF(filterStoreIds.Any(), pxmx => SqlFunc.Subqueryable().Where(x => x.Id == pxmx.ConsumeInfoId && filterStoreIds.Contains(x.Md)).Any()); // 3. 先分页查询主表数据（查询实体类，提高性能） var pagedData = await baseQuery.OrderBy(sidx + " " + sort).ToPagedListAsync(input.currentPage, input.pageSize); // 4. 批量查询关联数据 var itemIds = pagedData.list.Select(x => x.Id).ToList(); var memberIds = pagedData.list.Where(x => !string.IsNullOrEmpty(x.MemberId)).Select(x => x.MemberId).Distinct().ToList(); var consumeIds = pagedData.list.Where(x => !string.IsNullOrEmpty(x.ConsumeInfoId)).Select(x => x.ConsumeInfoId).Distinct().ToList(); // 批量查询会员信息 var memberDict = new Dictionary(); if (memberIds.Any()) { var members = await _db.Queryable().Where(x => memberIds.Contains(x.Id)).Select(x => new { x.Id, x.Khmc, x.Sjh }).ToListAsync(); memberDict = members.ToDictionary(x => x.Id, x => (x.Khmc ?? "", x.Sjh ?? "")); } // 批量查询耗卡记录，获取门店ID和耗卡时间 var consumeDict = new Dictionary(); if (consumeIds.Any()) { var consumes = await _db.Queryable().Where(x => consumeIds.Contains(x.Id)).Select(x => new { x.Id, x.Md, x.Hksj }).ToListAsync(); consumeDict = consumes.ToDictionary(x => x.Id, x => (x.Md ?? "", x.Hksj)); } // 批量查询门店信息 var storeDict = new Dictionary(); var storeIds = consumeDict.Values.Where(x => !string.IsNullOrEmpty(x.StoreId)).Select(x => x.StoreId).Distinct().ToList(); 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 ?? ""); } // 批量查询健康师业绩信息（通过 F_kdpxid 关联到品项明细ID） var jksyjDict = new Dictionary>(); if (itemIds.Any()) { var jksyjEntities = await _db.Queryable() .Where(x => itemIds.Contains(x.Kdpxid) && x.IsEffective == StatusEnum.有效.GetHashCode()) .ToListAsync(); var jksyjList = jksyjEntities.Select(x => new LqXhJksyjInfoOutput { id = x.Id, glkdbh = x.Glkdbh, jks = x.Jks, jksxm = x.Jksxm, jkszh = x.Jkszh, jksyj = x.Jksyj?.ToString() ?? "0", yjsj = x.Yjsj, jsjId = x.JsjId, kdpxid = x.Kdpxid, laborCost = x.LaborCost, kdpxNumber = x.KdpxNumber, originalKdpxNumber = x.OriginalKdpxNumber, overtimeKdpxNumber = x.OvertimeKdpxNumber, originalLaborCost = x.OriginalLaborCost, overtimeLaborCost = x.OvertimeLaborCost, isAccompanied = x.IsAccompanied, accompaniedProjectNumber = x.AccompaniedProjectNumber }).ToList(); jksyjDict = jksyjList .Where(x => !string.IsNullOrEmpty(x.kdpxid)) .GroupBy(x => x.kdpxid) .ToDictionary(g => g.Key, g => g.ToList()); } // 5. 组装返回数据 var resultList = pagedData.list.Select(pxmx => { var jksyjList = jksyjDict.ContainsKey(pxmx.Id) ? jksyjDict[pxmx.Id] : new List(); var firstJksyj = jksyjList.FirstOrDefault(); return new ConsumeItemDetailListOutput { id = pxmx.Id, consumeId = pxmx.ConsumeInfoId, consumeTime = pxmx.ConsumeInfoId != null && consumeDict.ContainsKey(pxmx.ConsumeInfoId) ? consumeDict[pxmx.ConsumeInfoId].ConsumeTime : pxmx.Yjsj, memberName = pxmx.MemberId != null && memberDict.ContainsKey(pxmx.MemberId) ? memberDict[pxmx.MemberId].Name : "", memberPhone = pxmx.MemberId != null && memberDict.ContainsKey(pxmx.MemberId) ? memberDict[pxmx.MemberId].Phone : "", itemName = pxmx.Pxmc, itemType = pxmx.ItemCategory, itemPrice = pxmx.Pxjg, projectNumber = pxmx.ProjectNumber, originalProjectNumber = pxmx.OriginalProjectNumber, totalPrice = pxmx.TotalPrice, sourceType = pxmx.SourceType, storeId = pxmx.ConsumeInfoId != null && consumeDict.ContainsKey(pxmx.ConsumeInfoId) ? consumeDict[pxmx.ConsumeInfoId].StoreId : "", storeName = pxmx.ConsumeInfoId != null && consumeDict.ContainsKey(pxmx.ConsumeInfoId) && !string.IsNullOrEmpty(consumeDict[pxmx.ConsumeInfoId].StoreId) && storeDict.ContainsKey(consumeDict[pxmx.ConsumeInfoId].StoreId) ? storeDict[consumeDict[pxmx.ConsumeInfoId].StoreId] : "", healthCoachId = firstJksyj?.jks ?? "", healthCoachName = firstJksyj?.jksxm ?? "", healthCoachAccount = firstJksyj?.jkszh ?? "", lqXhJksyjList = jksyjList }; }).ToList(); // 6. 返回分页结果 var result = new SqlSugarPagedList { list = resultList, pagination = pagedData.pagination }; return PageResult.SqlSugarPageResult(result); } catch (Exception ex) { _logger.LogError(ex, $"获取耗卡品项明细记录列表失败: {ex.ToString()}"); throw NCCException.Oh(ErrorCode.COM1005, $"获取耗卡品项明细记录列表失败: {ex.Message}"); } } #endregion #region 耗卡品项明细导出（每个健康师一行） /// /// 导出耗卡品项明细记录（每个健康师一行） /// /// /// 导出耗卡品项明细记录，如果一个耗卡品项明细有多个健康师，每个健康师单独一行 /// /// 示例请求： /// GET /api/Extend/LqXhHyhk/consume-item-detail-export?StartConsumeTime=2026-01-01&EndConsumeTime=2026-01-21 /// /// 查询参数（与consume-item-detail-list相同） /// Excel文件下载链接 [HttpGet("consume-item-detail-export")] public async Task ExportConsumeItemDetailList([FromQuery] ConsumeItemDetailListQueryInput input) { try { // 获取所有数据（不分页） var sidx = input.sidx == null ? "yjsj" : input.sidx; var sort = string.IsNullOrEmpty(input.sort) ? "DESC" : input.sort; // 处理耗卡时间范围（兼容 StartConsumeTime/EndConsumeTime 和 startTime/endTime 两种参数名） DateTime? startConsumeTime = null; DateTime? endConsumeTime = null; string startTimeStr = !string.IsNullOrEmpty(input.StartConsumeTime) ? input.StartConsumeTime : input.startTime; string endTimeStr = !string.IsNullOrEmpty(input.EndConsumeTime) ? input.EndConsumeTime : input.endTime; if (!string.IsNullOrEmpty(startTimeStr) && !string.IsNullOrEmpty(endTimeStr)) { if (DateTime.TryParse(startTimeStr, out DateTime startDate)) { startConsumeTime = startDate; } else { throw NCCException.Oh($"开始时间格式错误：{startTimeStr}"); } if (DateTime.TryParse(endTimeStr, out DateTime endDate)) { endConsumeTime = endDate; } else { throw NCCException.Oh($"结束时间格式错误：{endTimeStr}"); } } // 构建基础查询条件（与GetConsumeItemDetailList相同） var baseQuery = _db.Queryable() .Where(pxmx => pxmx.IsEffective == StatusEnum.有效.GetHashCode()) .WhereIF(!string.IsNullOrEmpty(input.Id), pxmx => pxmx.Id == input.Id) .WhereIF(!string.IsNullOrEmpty(input.ConsumeId), pxmx => pxmx.ConsumeInfoId == input.ConsumeId) .WhereIF(startConsumeTime.HasValue, pxmx => pxmx.Yjsj >= new DateTime(startConsumeTime.Value.Year, startConsumeTime.Value.Month, startConsumeTime.Value.Day, 0, 0, 0)) .WhereIF(endConsumeTime.HasValue, pxmx => pxmx.Yjsj <= new DateTime(endConsumeTime.Value.Year, endConsumeTime.Value.Month, endConsumeTime.Value.Day, 23, 59, 59)) .WhereIF(!string.IsNullOrEmpty(input.MemberId), pxmx => pxmx.MemberId == input.MemberId) .WhereIF(!string.IsNullOrEmpty(input.ItemId), pxmx => pxmx.Px == input.ItemId) .WhereIF(!string.IsNullOrEmpty(input.ItemName), pxmx => pxmx.Pxmc != null && pxmx.Pxmc.Contains(input.ItemName)) .WhereIF(!string.IsNullOrEmpty(input.SourceType), pxmx => pxmx.SourceType == input.SourceType) .WhereIF(!string.IsNullOrEmpty(input.ItemType), pxmx => pxmx.ItemCategory == input.ItemType); // 处理多门店筛选 var filterStoreIds = new List(); if (!string.IsNullOrEmpty(input.StoreIds)) { filterStoreIds = input.StoreIds.Split(',') .Where(x => !string.IsNullOrWhiteSpace(x)) .Select(x => x.Trim()) .ToList(); } else if (!string.IsNullOrEmpty(input.StoreId)) { filterStoreIds = new List { input.StoreId }; } baseQuery = baseQuery.WhereIF(!string.IsNullOrEmpty(input.MemberName), pxmx => SqlFunc.Subqueryable().Where(x => x.Id == pxmx.MemberId && x.Khmc != null && x.Khmc.Contains(input.MemberName)).Any()) .WhereIF(!string.IsNullOrEmpty(input.MemberPhone), pxmx => SqlFunc.Subqueryable().Where(x => x.Id == pxmx.MemberId && x.Sjh == input.MemberPhone).Any()) .WhereIF(filterStoreIds.Any(), pxmx => SqlFunc.Subqueryable().Where(x => x.Id == pxmx.ConsumeInfoId && filterStoreIds.Contains(x.Md)).Any()); // 获取所有数据（不分页） _logger.LogInformation($"导出耗卡品项明细：开始查询，时间范围: {startTimeStr} 到 {endTimeStr}"); var allData = await baseQuery.OrderBy(sidx + " " + sort).ToListAsync(); _logger.LogInformation($"导出耗卡品项明细：查询到 {allData?.Count ?? 0} 条品项明细记录"); if (allData == null || !allData.Any()) { _logger.LogWarning($"导出耗卡品项明细：查询结果为空，查询条件 - StartConsumeTime: {startTimeStr}, EndConsumeTime: {endTimeStr}, StartTime: {input.startTime}, EndTime: {input.endTime}"); throw NCCException.Oh(ErrorCode.COM1005, "没有符合条件的数据可导出"); } // 批量查询关联数据 var itemIds = allData.Select(x => x.Id).ToList(); var memberIds = allData.Where(x => !string.IsNullOrEmpty(x.MemberId)).Select(x => x.MemberId).Distinct().ToList(); var consumeIds = allData.Where(x => !string.IsNullOrEmpty(x.ConsumeInfoId)).Select(x => x.ConsumeInfoId).Distinct().ToList(); // 批量查询会员信息 var memberDict = new Dictionary(); if (memberIds.Any()) { var members = await _db.Queryable().Where(x => memberIds.Contains(x.Id)).Select(x => new { x.Id, x.Khmc, x.Sjh }).ToListAsync(); memberDict = members.ToDictionary(x => x.Id, x => (x.Khmc ?? "", x.Sjh ?? "")); } // 批量查询耗卡记录 var consumeDict = new Dictionary(); if (consumeIds.Any()) { var consumes = await _db.Queryable().Where(x => consumeIds.Contains(x.Id)).Select(x => new { x.Id, x.Md, x.Hksj }).ToListAsync(); consumeDict = consumes.ToDictionary(x => x.Id, x => (x.Md ?? "", x.Hksj)); } // 批量查询门店信息 var storeDict = new Dictionary(); var storeIds = consumeDict.Values.Where(x => !string.IsNullOrEmpty(x.StoreId)).Select(x => x.StoreId).Distinct().ToList(); 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 jksyjDict = new Dictionary>(); if (itemIds.Any()) { var jksyjEntities = await _db.Queryable() .Where(x => itemIds.Contains(x.Kdpxid) && x.IsEffective == StatusEnum.有效.GetHashCode()) .ToListAsync(); var jksyjList = jksyjEntities.Select(x => new LqXhJksyjInfoOutput { id = x.Id, glkdbh = x.Glkdbh, jks = x.Jks, jksxm = x.Jksxm, jkszh = x.Jkszh, jksyj = x.Jksyj?.ToString() ?? "0", yjsj = x.Yjsj, jsjId = x.JsjId, kdpxid = x.Kdpxid, laborCost = x.LaborCost, kdpxNumber = x.KdpxNumber, originalKdpxNumber = x.OriginalKdpxNumber, overtimeKdpxNumber = x.OvertimeKdpxNumber, originalLaborCost = x.OriginalLaborCost, overtimeLaborCost = x.OvertimeLaborCost, isAccompanied = x.IsAccompanied, accompaniedProjectNumber = x.AccompaniedProjectNumber }).ToList(); jksyjDict = jksyjList .Where(x => !string.IsNullOrEmpty(x.kdpxid)) .GroupBy(x => x.kdpxid) .ToDictionary(g => g.Key, g => g.ToList()); } // 展开数据：每个健康师一行 var exportData = new List(); _logger.LogInformation($"开始展开数据，品项明细数量: {allData.Count}, 健康师业绩字典数量: {jksyjDict.Count}"); int processedCount = 0; foreach (var pxmx in allData) { var jksyjList = jksyjDict.ContainsKey(pxmx.Id) ? jksyjDict[pxmx.Id] : new List(); // 基础信息 var baseInfo = new { id = pxmx.Id, consumeId = pxmx.ConsumeInfoId, consumeTime = pxmx.ConsumeInfoId != null && consumeDict.ContainsKey(pxmx.ConsumeInfoId) ? consumeDict[pxmx.ConsumeInfoId].ConsumeTime : pxmx.Yjsj, memberName = pxmx.MemberId != null && memberDict.ContainsKey(pxmx.MemberId) ? memberDict[pxmx.MemberId].Name : "", memberPhone = pxmx.MemberId != null && memberDict.ContainsKey(pxmx.MemberId) ? memberDict[pxmx.MemberId].Phone : "", itemName = pxmx.Pxmc, itemType = pxmx.ItemCategory, itemPrice = pxmx.Pxjg, projectNumber = pxmx.ProjectNumber, originalProjectNumber = pxmx.OriginalProjectNumber, totalPrice = pxmx.TotalPrice, sourceType = pxmx.SourceType, storeId = pxmx.ConsumeInfoId != null && consumeDict.ContainsKey(pxmx.ConsumeInfoId) ? consumeDict[pxmx.ConsumeInfoId].StoreId : "", storeName = pxmx.ConsumeInfoId != null && consumeDict.ContainsKey(pxmx.ConsumeInfoId) && !string.IsNullOrEmpty(consumeDict[pxmx.ConsumeInfoId].StoreId) && storeDict.ContainsKey(consumeDict[pxmx.ConsumeInfoId].StoreId) ? storeDict[consumeDict[pxmx.ConsumeInfoId].StoreId] : "" }; // 如果没有健康师，也要导出一行（健康师信息为空） if (jksyjList == null || !jksyjList.Any()) { exportData.Add(new ConsumeItemDetailExportOutput { id = baseInfo.id, consumeId = baseInfo.consumeId, consumeTime = baseInfo.consumeTime, memberName = baseInfo.memberName, memberPhone = baseInfo.memberPhone, itemName = baseInfo.itemName, itemType = baseInfo.itemType, itemPrice = baseInfo.itemPrice, projectNumber = baseInfo.projectNumber, originalProjectNumber = baseInfo.originalProjectNumber, totalPrice = baseInfo.totalPrice, sourceType = baseInfo.sourceType, storeId = baseInfo.storeId, storeName = baseInfo.storeName, healthCoachId = "", healthCoachName = "", healthCoachAccount = "", healthCoachPerformance = "", laborCost = null, kdpxNumber = null }); } else { // 每个健康师一行 foreach (var jksyj in jksyjList) { exportData.Add(new ConsumeItemDetailExportOutput { id = baseInfo.id, consumeId = baseInfo.consumeId, consumeTime = baseInfo.consumeTime, memberName = baseInfo.memberName, memberPhone = baseInfo.memberPhone, itemName = baseInfo.itemName, itemType = baseInfo.itemType, itemPrice = baseInfo.itemPrice, projectNumber = baseInfo.projectNumber, originalProjectNumber = baseInfo.originalProjectNumber, totalPrice = baseInfo.totalPrice, sourceType = baseInfo.sourceType, storeId = baseInfo.storeId, storeName = baseInfo.storeName, healthCoachId = jksyj.jks ?? "", healthCoachName = jksyj.jksxm ?? "", healthCoachAccount = jksyj.jkszh ?? "", healthCoachPerformance = jksyj.jksyj ?? "0", laborCost = jksyj.laborCost, kdpxNumber = jksyj.kdpxNumber }); } } processedCount++; if (processedCount % 1000 == 0) { _logger.LogInformation($"已处理 {processedCount}/{allData.Count} 条品项明细，当前导出数据行数: {exportData.Count}"); } } _logger.LogInformation($"数据展开完成，处理品项明细数量: {processedCount}, 导出数据行数: {exportData?.Count ?? 0}"); // 配置Excel导出 List paramList = "[{\"value\":\"品项明细ID\",\"field\":\"id\"},{\"value\":\"耗卡记录ID\",\"field\":\"consumeId\"},{\"value\":\"耗卡时间\",\"field\":\"consumeTime\"},{\"value\":\"会员名称\",\"field\":\"memberName\"},{\"value\":\"会员手机\",\"field\":\"memberPhone\"},{\"value\":\"品项名称\",\"field\":\"itemName\"},{\"value\":\"品项类型\",\"field\":\"itemType\"},{\"value\":\"品项价格\",\"field\":\"itemPrice\"},{\"value\":\"项目次数\",\"field\":\"projectNumber\"},{\"value\":\"原始项目次数\",\"field\":\"originalProjectNumber\"},{\"value\":\"合计金额\",\"field\":\"totalPrice\"},{\"value\":\"来源类型\",\"field\":\"sourceType\"},{\"value\":\"门店ID\",\"field\":\"storeId\"},{\"value\":\"门店名称\",\"field\":\"storeName\"},{\"value\":\"健康师ID\",\"field\":\"healthCoachId\"},{\"value\":\"健康师姓名\",\"field\":\"healthCoachName\"},{\"value\":\"健康师账号\",\"field\":\"healthCoachAccount\"},{\"value\":\"健康师业绩\",\"field\":\"healthCoachPerformance\"},{\"value\":\"手工费\",\"field\":\"laborCost\"},{\"value\":\"耗卡品项次数\",\"field\":\"kdpxNumber\"}]".ToList(); 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 }); } // 查找项目根目录并创建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); 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; } catch (Exception ex) { _logger.LogError(ex, $"导出耗卡品项明细记录失败: {ex.ToString()}"); var errorMessage = $"导出耗卡品项明细记录失败: {ex.Message}"; if (ex.InnerException != null) { errorMessage += $" | 内部异常: {ex.InnerException.Message}"; } throw NCCException.Oh(ErrorCode.COM1005, errorMessage); } } #endregion } }