using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Threading.Tasks; using Microsoft.AspNetCore.Mvc; using Newtonsoft.Json; using NCC.Common.Core.Manager; using NCC.Common.Extension; using NCC.Common.Filter; using NCC.Common.Helper; using NCC.Common.Model.NPOI; using NCC.DataEncryption; using NCC.Extend.Entitys.Dto.LqBusinessUnitDashboard; using NCC.Extend.Entitys.Enum; using NCC.Extend.Entitys.lq_hytk_hytk; using NCC.Extend.Entitys.lq_kd_kdjlb; using NCC.Extend.Entitys.lq_khxx; using NCC.Extend.Entitys.lq_md_target; using NCC.Extend.Entitys.lq_mdxx; using NCC.Extend.Entitys.lq_xh_feedback; using NCC.Extend.Entitys.lq_xh_hyhk; using NCC.Extend.Interfaces.BusinessOperationLog; using NCC.FriendlyException; using NCC.System.Entitys.Permission; using SqlSugar; namespace NCC.Extend { /// /// 事业部驾驶舱四项能力:三美占比、服务日志、重复充值 /// public partial class LqBusinessUnitDashboardService { private const string CapabilityBizLogModule = "事业部看板"; #region 三美占比 /// /// 获取事业部驾驶舱三美净业绩占比 /// /// /// 口径:净业绩 = 开单品项实付 − 退卡;占比分母 = 生美+科美+医美合计(不含产品)。 /// /// 示例请求: /// ```json /// { /// "businessUnitId": "734725299018663173", /// "statisticsMonth": "202607" /// } /// ``` /// /// 公共筛选条件 /// 三美净业绩及占比 /// 成功返回占比统计 /// 参数错误 /// 服务器错误 [HttpPost("GetCategoryShareStatistics")] public async Task GetCategoryShareStatistics( [FromBody] BusinessUnitDashboardStatisticsInput input) { ValidateCapabilityStatisticsInput(input); var storeIds = await GetStoreIdsAsync(input.BusinessUnitId, input.StoreIds, input.StatisticsMonth); if (!storeIds.Any()) return new BusinessUnitDashboardCategoryShareOutput(); var (billingLife, billingTech, billingMedical, billingProduct) = await CalculatePerformanceByCategory(storeIds, input.StatisticsMonth, false); var (refundLife, refundTech, refundMedical, refundProduct) = await CalculatePerformanceByCategory(storeIds, input.StatisticsMonth, true); var lifeNet = billingLife - refundLife; var techNet = billingTech - refundTech; var medicalNet = billingMedical - refundMedical; var productNet = billingProduct - refundProduct; var threeBeautyTotal = lifeNet + techNet + medicalNet; var result = new BusinessUnitDashboardCategoryShareOutput { LifeBeautyNetPerformance = lifeNet, TechBeautyNetPerformance = techNet, MedicalBeautyNetPerformance = medicalNet, ProductNetPerformance = productNet, ThreeBeautyTotal = threeBeautyTotal, LifeBeautyPercentage = threeBeautyTotal > 0 ? decimal.Round(lifeNet / threeBeautyTotal * 100m, 2) : 0m, TechBeautyPercentage = threeBeautyTotal > 0 ? decimal.Round(techNet / threeBeautyTotal * 100m, 2) : 0m, MedicalBeautyPercentage = threeBeautyTotal > 0 ? decimal.Round(medicalNet / threeBeautyTotal * 100m, 2) : 0m, ShareData = new List { new BusinessUnitCategoryShareItem { ItemCategory = "生美", NetPerformance = lifeNet, Percentage = threeBeautyTotal > 0 ? decimal.Round(lifeNet / threeBeautyTotal * 100m, 2) : 0m }, new BusinessUnitCategoryShareItem { ItemCategory = "科美", NetPerformance = techNet, Percentage = threeBeautyTotal > 0 ? decimal.Round(techNet / threeBeautyTotal * 100m, 2) : 0m }, new BusinessUnitCategoryShareItem { ItemCategory = "医美", NetPerformance = medicalNet, Percentage = threeBeautyTotal > 0 ? decimal.Round(medicalNet / threeBeautyTotal * 100m, 2) : 0m } } }; return result; } #endregion #region 服务日志 /// /// 获取事业部驾驶舱服务日志统计 /// /// /// 管辖门店取自 lq_md_target;日志表 lq_xh_feedback; /// 覆盖率分母为有效耗卡(不限生美)。 /// /// 示例请求: /// ```json /// { /// "businessUnitId": "734725299018663173", /// "statisticsMonth": "202607" /// } /// ``` /// /// 公共筛选条件 /// 日志数、覆盖率、照片完整率 /// 成功返回统计 /// 参数错误 /// 服务器错误 [HttpPost("GetServiceLogStatistics")] public async Task GetServiceLogStatistics( [FromBody] BusinessUnitDashboardStatisticsInput input) { ValidateCapabilityStatisticsInput(input); var storeIds = await GetStoreIdsAsync(input.BusinessUnitId, input.StoreIds, input.StatisticsMonth); if (!storeIds.Any()) return new BusinessUnitServiceLogStatisticsOutput(); var (startDate, endDateTime) = GetCapabilityMonthRange(input.StatisticsMonth); var consumeIds = await QueryValidConsumeIdsAsync(storeIds, startDate, endDateTime); if (!consumeIds.Any()) return new BusinessUnitServiceLogStatisticsOutput(); var logs = await _db.Queryable() .Where(x => consumeIds.Contains(x.ConsumeId)) .Where(x => x.CreateTime >= startDate && x.CreateTime <= endDateTime) .Select(x => new ServiceLogMetricRow { ConsumeId = x.ConsumeId, BeforeImage = x.BeforeImage, AfterImage = x.AfterImage }) .ToListAsync(); var coveredConsumeCount = logs.Select(x => x.ConsumeId).Distinct().Count(); var completePhotoCount = logs.Count(x => !string.IsNullOrWhiteSpace(x.BeforeImage) && !string.IsNullOrWhiteSpace(x.AfterImage)); return new BusinessUnitServiceLogStatisticsOutput { ServiceLogCount = logs.Count, ValidConsumeCount = consumeIds.Count, CoveredConsumeCount = coveredConsumeCount, CoverageRate = consumeIds.Count > 0 ? decimal.Round((decimal)coveredConsumeCount / consumeIds.Count * 100m, 2) : 0m, CompletePhotoLogCount = completePhotoCount, CompletePhotoRate = logs.Count > 0 ? decimal.Round((decimal)completePhotoCount / logs.Count * 100m, 2) : 0m }; } /// /// 分页查询事业部驾驶舱服务日志 /// /// /// 筛选条件与 GetServiceLogStatistics 一致,关联有效耗卡(不限生美)。 /// /// 示例请求: /// ```json /// { /// "businessUnitId": "734725299018663173", /// "statisticsMonth": "202607", /// "currentPage": 1, /// "pageSize": 20 /// } /// ``` /// /// 分页筛选条件 /// 服务日志分页列表 /// 成功返回列表 /// 参数错误 /// 服务器错误 [HttpPost("GetServiceLogList")] public async Task GetServiceLogList([FromBody] BusinessUnitServiceLogListInput input) { var (list, total) = await QueryServiceLogListAsync(input, true); return PageResult.SqlSugarPageResult( new SqlSugarPagedList { list = list, pagination = new PagedModel { PageIndex = NormalizeCapabilityPageIndex(input.currentPage), PageSize = NormalizeCapabilityPageSize(input.pageSize), Total = total } }); } /// /// 导出事业部驾驶舱服务日志 /// /// /// 导出条件与 GetServiceLogList 一致,成功后写入中文业务操作日志。 /// /// 筛选条件 /// Excel 下载信息 /// 成功返回下载链接 /// 参数错误 /// 服务器错误 [HttpPost("ExportServiceLogList")] public async Task ExportServiceLogList([FromBody] BusinessUnitServiceLogListInput input) { var (list, _) = await QueryServiceLogListAsync(input, false); return await ExportCapabilityDataAsync( list, "导出服务日志", "lq_xh_feedback", input, "[{\"value\":\"门店\",\"field\":\"storeName\"},{\"value\":\"会员\",\"field\":\"memberName\"}," + "{\"value\":\"老师\",\"field\":\"createUserName\"},{\"value\":\"备注\",\"field\":\"remark\"}," + "{\"value\":\"前照片\",\"field\":\"beforeImage\"},{\"value\":\"后照片\",\"field\":\"afterImage\"}," + "{\"value\":\"照片完整\",\"field\":\"hasCompletePhotos\"},{\"value\":\"创建时间\",\"field\":\"createTime\"}]"); } #endregion #region 重复充值 /// /// 校验同会员同自然日是否已有有效开单或耗卡(软提醒) /// /// /// 开单/耗卡保存前调用;scene 为 Billing 或 Consume;编辑时传 excludeId 排除当前单据。 /// /// 示例请求: /// ```json /// { /// "memberId": "834643959459349765", /// "bizDate": "2026-07-27", /// "scene": "Billing", /// "excludeId": null /// } /// ``` /// /// 校验参数 /// 是否存在重复及提醒文案 /// 成功返回校验结果 /// 参数错误 /// 服务器错误 [HttpPost("CheckSameDayDuplicate")] public async Task CheckSameDayDuplicate( [FromBody] BusinessUnitCheckSameDayDuplicateInput input) { if (input == null) throw NCCException.Oh("请求参数不能为空"); if (string.IsNullOrWhiteSpace(input.MemberId)) throw NCCException.Oh("会员ID不能为空"); if (input.BizDate == default) throw NCCException.Oh("业务日期不能为空"); var dateStart = input.BizDate.Date; var dateEnd = dateStart.AddDays(1); var scene = (input.Scene ?? string.Empty).Trim(); var billingQuery = _db.Queryable() .Where(x => x.Kdhy == input.MemberId && x.IsEffective == 1) .Where(x => x.Kdrq >= dateStart && x.Kdrq < dateEnd); if (!string.IsNullOrWhiteSpace(input.ExcludeId) && scene.Equals(nameof(DuplicateRechargeScene.Billing), StringComparison.OrdinalIgnoreCase)) { billingQuery = billingQuery.Where(x => x.Id != input.ExcludeId); } var consumeQuery = _db.Queryable() .Where(x => x.Hy == input.MemberId && x.IsEffective == 1) .Where(x => x.Hksj >= dateStart && x.Hksj < dateEnd); if (!string.IsNullOrWhiteSpace(input.ExcludeId) && scene.Equals(nameof(DuplicateRechargeScene.Consume), StringComparison.OrdinalIgnoreCase)) { consumeQuery = consumeQuery.Where(x => x.Id != input.ExcludeId); } var billingCount = await billingQuery.CountAsync(); var consumeCount = await consumeQuery.CountAsync(); var existingCount = billingCount + consumeCount; return new BusinessUnitCheckSameDayDuplicateOutput { HasDuplicate = existingCount > 0, ExistingCount = existingCount, Message = existingCount > 0 ? "该会员当日已有有效开单或耗卡记录,请确认是否继续操作" : string.Empty }; } /// /// 分页查询重复充值审计列表(同会员同自然日第2笔及以后的开单+耗卡) /// /// /// 门店范围取自 lq_md_target;统计月份内按会员+自然日合并开单与耗卡,返回当日第2笔及以后记录。 /// /// 示例请求: /// ```json /// { /// "businessUnitId": "734725299018663173", /// "statisticsMonth": "202607", /// "currentPage": 1, /// "pageSize": 20 /// } /// ``` /// /// 分页筛选条件 /// 重复充值审计分页列表 /// 成功返回审计列表 /// 参数错误 /// 服务器错误 [HttpPost("GetDuplicateRechargeAuditList")] public async Task GetDuplicateRechargeAuditList( [FromBody] BusinessUnitDuplicateRechargeAuditListInput input) { if (input == null) throw NCCException.Oh("请求参数不能为空"); var (startDate, endDateTime) = ResolveDuplicateAuditDateRange(input); var storeIds = await ResolveDuplicateAuditStoreIdsAsync(input, startDate); if (!storeIds.Any()) { return PageResult.SqlSugarPageResult( new SqlSugarPagedList { list = new List(), pagination = new PagedModel { PageIndex = NormalizeCapabilityPageIndex(input.currentPage), PageSize = NormalizeCapabilityPageSize(input.pageSize), Total = 0 } }); } var auditRows = await BuildDuplicateRechargeAuditRowsAsync(storeIds, startDate, endDateTime); if (!string.IsNullOrWhiteSpace(input.StoreId)) auditRows = auditRows.Where(x => x.StoreId == input.StoreId).ToList(); if (!string.IsNullOrWhiteSpace(input.MemberName)) { var memberName = input.MemberName.Trim(); auditRows = auditRows.Where(x => !string.IsNullOrWhiteSpace(x.MemberName) && x.MemberName.Contains(memberName)).ToList(); } if (!string.IsNullOrWhiteSpace(input.BizType)) { var bizType = input.BizType.Trim(); auditRows = auditRows.Where(x => x.BizType.Equals(bizType, StringComparison.OrdinalIgnoreCase)).ToList(); } var keyword = string.IsNullOrWhiteSpace(input.keyword) ? null : input.keyword.Trim(); if (keyword != null) { auditRows = auditRows.Where(x => (x.MemberName != null && x.MemberName.Contains(keyword)) || (x.StoreName != null && x.StoreName.Contains(keyword))).ToList(); } auditRows = auditRows.OrderByDescending(x => x.BizTime).ToList(); var total = auditRows.Count; var pageIndex = NormalizeCapabilityPageIndex(input.currentPage); var pageSize = NormalizeCapabilityPageSize(input.pageSize); var pageList = auditRows.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList(); return PageResult.SqlSugarPageResult( new SqlSugarPagedList { list = pageList, pagination = new PagedModel { PageIndex = pageIndex, PageSize = pageSize, Total = total } }); } #endregion #region 私有辅助 private void ValidateCapabilityStatisticsInput(BusinessUnitDashboardStatisticsInput input) { if (input == null) throw NCCException.Oh("请求参数不能为空"); if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6) throw NCCException.Oh("统计月份格式错误,必须为YYYYMM格式"); if (string.IsNullOrWhiteSpace(input.BusinessUnitId) && (input.StoreIds == null || !input.StoreIds.Any())) throw NCCException.Oh("事业部ID和门店ID列表不能同时为空,必须传入其中一个"); } /// /// 解析重复充值审计日期范围:优先 StartTime/EndTime,否则按 StatisticsMonth。 /// private static (DateTime startDate, DateTime endDateTime) ResolveDuplicateAuditDateRange( BusinessUnitDuplicateRechargeAuditListInput input) { if (input.StartTime.HasValue || input.EndTime.HasValue) { var start = (input.StartTime ?? input.EndTime ?? DateTime.Today).Date; var endDay = (input.EndTime ?? input.StartTime ?? DateTime.Today).Date; if (endDay < start) throw NCCException.Oh("结束时间不能早于开始时间"); if ((endDay - start).TotalDays > 92) throw NCCException.Oh("审计日期范围不能超过 92 天"); return (start, endDay.AddDays(1).AddSeconds(-1)); } if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6) throw NCCException.Oh("请传入日期范围或统计月份(YYYYMM)"); return GetCapabilityMonthRange(input.StatisticsMonth); } /// /// 解析重复充值审计门店范围:事业部/门店列表/全开店。 /// private async Task> ResolveDuplicateAuditStoreIdsAsync( BusinessUnitDuplicateRechargeAuditListInput input, DateTime startDate) { if (!string.IsNullOrWhiteSpace(input.StoreId)) return new List { input.StoreId }; if (input.StoreIds != null && input.StoreIds.Any(x => !string.IsNullOrWhiteSpace(x))) return input.StoreIds.Where(x => !string.IsNullOrWhiteSpace(x)).Distinct().ToList(); if (!string.IsNullOrWhiteSpace(input.BusinessUnitId)) { var month = !string.IsNullOrWhiteSpace(input.StatisticsMonth) && input.StatisticsMonth.Length == 6 ? input.StatisticsMonth : startDate.ToString("yyyyMM"); return await GetStoreIdsAsync(input.BusinessUnitId, null, month); } // 监控中心未选事业部:取全部开店门店 return await _db.Queryable() .Where(x => x.Zxzt == "开店") .Select(x => x.Id) .ToListAsync(); } private static (DateTime startDate, DateTime endDateTime) GetCapabilityMonthRange(string statisticsMonth) { var year = int.Parse(statisticsMonth.Substring(0, 4)); var month = int.Parse(statisticsMonth.Substring(4, 2)); var startDate = new DateTime(year, month, 1); var endDate = startDate.AddMonths(1).AddDays(-1); var endDateTime = statisticsMonth == DateTime.Now.ToString("yyyyMM") ? DateTime.Now : endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59); return (startDate, endDateTime); } private static int NormalizeCapabilityPageIndex(int value) => value > 0 ? value : 1; private static int NormalizeCapabilityPageSize(int value) => value > 0 ? value : 20; private async Task> QueryValidConsumeIdsAsync( List storeIds, DateTime startDate, DateTime endDateTime) { return await _db.Queryable() .Where(x => storeIds.Contains(x.Md) && x.IsEffective == 1) .Where(x => x.Hksj.HasValue && x.Hksj.Value >= startDate && x.Hksj.Value <= endDateTime) .Select(x => x.Id) .Distinct() .ToListAsync(); } private async Task<(List list, int total)> QueryServiceLogListAsync( BusinessUnitServiceLogListInput input, bool paged) { if (input == null) throw NCCException.Oh("请求参数不能为空"); ValidateCapabilityStatisticsInput(new BusinessUnitDashboardStatisticsInput { BusinessUnitId = input.BusinessUnitId, StoreIds = input.StoreIds, StatisticsMonth = input.StatisticsMonth }); var storeIds = await GetStoreIdsAsync(input.BusinessUnitId, input.StoreIds, input.StatisticsMonth); if (!storeIds.Any()) return (new List(), 0); var (startDate, endDateTime) = GetCapabilityMonthRange(input.StatisticsMonth); // 直接按门店+有效耗卡时间过滤,避免数千 consumeId 的 IN 导致列表/导出超时 var keyword = string.IsNullOrWhiteSpace(input.keyword) ? null : input.keyword.Trim(); var query = _db.Queryable< LqXhFeedbackEntity, LqXhHyhkEntity, LqKhxxEntity, LqMdxxEntity, UserEntity>((log, consume, member, store, user) => new JoinQueryInfos( JoinType.Inner, log.ConsumeId == consume.Id, JoinType.Left, log.MemberId == member.Id, JoinType.Left, consume.Md == store.Id, JoinType.Left, log.CreateUser == user.Id)) .Where((log, consume, member, store, user) => storeIds.Contains(consume.Md) && consume.IsEffective == 1 && consume.Hksj.HasValue && consume.Hksj.Value >= startDate && consume.Hksj.Value <= endDateTime && log.CreateTime >= startDate && log.CreateTime <= endDateTime) .WhereIF(!string.IsNullOrWhiteSpace(input.StoreId), (log, consume, member, store, user) => consume.Md == input.StoreId) .WhereIF(!string.IsNullOrWhiteSpace(input.MemberName), (log, consume, member, store, user) => member.Khmc.Contains(input.MemberName)) .WhereIF(!string.IsNullOrWhiteSpace(input.CreateUserName), (log, consume, member, store, user) => user.RealName.Contains(input.CreateUserName)) .WhereIF(input.HasCompletePhotos == true, (log, consume, member, store, user) => log.BeforeImage != null && log.BeforeImage != "" && log.AfterImage != null && log.AfterImage != "") .WhereIF(input.HasCompletePhotos == false, (log, consume, member, store, user) => log.BeforeImage == null || log.BeforeImage == "" || log.AfterImage == null || log.AfterImage == "") .WhereIF(keyword != null, (log, consume, member, store, user) => member.Khmc.Contains(keyword) || store.Dm.Contains(keyword) || user.RealName.Contains(keyword) || log.Remark.Contains(keyword)) .OrderBy((log, consume, member, store, user) => log.CreateTime, OrderByType.Desc) .Select((log, consume, member, store, user) => new BusinessUnitServiceLogListOutput { ServiceLogId = log.Id, ConsumeId = log.ConsumeId, StoreId = consume.Md, StoreName = store.Dm, MemberId = log.MemberId, MemberName = member.Khmc, CreateUserId = log.CreateUser, CreateUserName = user.RealName, Remark = log.Remark, BeforeImage = log.BeforeImage, AfterImage = log.AfterImage, HasCompletePhotos = log.BeforeImage != null && log.BeforeImage != "" && log.AfterImage != null && log.AfterImage != "", CreateTime = log.CreateTime }); if (paged) { var page = await query.ToPagedListAsync( NormalizeCapabilityPageIndex(input.currentPage), NormalizeCapabilityPageSize(input.pageSize)); return (page.list.ToList(), page.pagination.Total); } var list = await query.ToListAsync(); return (list, list.Count); } private async Task> BuildDuplicateRechargeAuditRowsAsync( List storeIds, DateTime startDate, DateTime endDateTime) { var billingRows = await _db.Queryable( (bill, store, member) => new JoinQueryInfos( JoinType.Left, bill.Djmd == store.Id, JoinType.Left, bill.Kdhy == member.Id)) .Where((bill, store, member) => storeIds.Contains(bill.Djmd) && bill.IsEffective == 1 && bill.Kdrq.HasValue && bill.Kdrq.Value >= startDate && bill.Kdrq.Value <= endDateTime && !string.IsNullOrWhiteSpace(bill.Kdhy)) .Select((bill, store, member) => new DuplicateRechargeRawRow { BizId = bill.Id, BizType = nameof(DuplicateRechargeBizType.Billing), BizTypeName = "开单", MemberId = bill.Kdhy, MemberName = member.Khmc, StoreId = bill.Djmd, StoreName = store.Dm, BizTime = bill.Kdrq.Value, Amount = bill.Sfyj }) .ToListAsync(); var consumeRows = await _db.Queryable( (consume, store, member) => new JoinQueryInfos( JoinType.Left, consume.Md == store.Id, JoinType.Left, consume.Hy == member.Id)) .Where((consume, store, member) => storeIds.Contains(consume.Md) && consume.IsEffective == 1 && consume.Hksj.HasValue && consume.Hksj.Value >= startDate && consume.Hksj.Value <= endDateTime && !string.IsNullOrWhiteSpace(consume.Hy)) .Select((consume, store, member) => new DuplicateRechargeRawRow { BizId = consume.Id, BizType = nameof(DuplicateRechargeBizType.Consume), BizTypeName = "耗卡", MemberId = consume.Hy, MemberName = member.Khmc ?? consume.Hymc, StoreId = consume.Md, StoreName = store.Dm, BizTime = consume.Hksj.Value, Amount = consume.Xfje ?? 0m }) .ToListAsync(); var grouped = billingRows.Concat(consumeRows) .GroupBy(x => new { x.MemberId, BizDate = x.BizTime.Date }) .Where(g => g.Count() >= 2); var result = new List(); foreach (var group in grouped) { var ordered = group.OrderBy(x => x.BizTime).ThenBy(x => x.BizId).ToList(); var dayTotal = ordered.Count; for (var i = 1; i < ordered.Count; i++) { var row = ordered[i]; result.Add(new BusinessUnitDuplicateRechargeAuditListOutput { BizId = row.BizId, BizType = row.BizType, BizTypeName = row.BizTypeName, MemberId = row.MemberId, MemberName = string.IsNullOrWhiteSpace(row.MemberName) ? "无" : row.MemberName, StoreId = row.StoreId, StoreName = string.IsNullOrWhiteSpace(row.StoreName) ? "无" : row.StoreName, BizTime = row.BizTime, BizDate = row.BizTime.Date, Amount = row.Amount, DaySequence = i + 1, DayTotalCount = dayTotal }); } } return result; } private async Task ExportCapabilityDataAsync( List exportData, string actionCode, string bizType, object filters, string columnsJson) { if (exportData == null || !exportData.Any()) return new { name = string.Empty, url = string.Empty, message = "没有找到符合条件的数据" }; var columns = columnsJson.ToList(); var excelConfig = new ExcelConfig { FileName = actionCode + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls", HeadFont = "微软雅黑", HeadPoint = 10, IsAllSizeColumn = true, ColumnModel = columns.Select(x => new ExcelColumnModel { Column = x.field, ExcelColumn = x.value }).ToList() }; var exportFilesPath = Path.Combine(ResolveCapabilityProjectRoot(), "ExportFiles"); if (!Directory.Exists(exportFilesPath)) Directory.CreateDirectory(exportFilesPath); var addPath = Path.Combine(exportFilesPath, excelConfig.FileName); ExcelExportHelper.Export(exportData, excelConfig, addPath); var encryptedFileName = _userManager.UserId + "|" + addPath + "|xls"; await _businessOperationLogService.WriteAsync( CapabilityBizLogModule, actionCode, bizType, null, $"{actionCode},共 {exportData.Count} 条", JsonConvert.SerializeObject(new { filters, rowCount = exportData.Count, fileName = excelConfig.FileName })); return new { name = excelConfig.FileName, url = "/api/File/Download?encryption=" + DESCEncryption.Encrypt(encryptedFileName, "NCC") }; } private static string ResolveCapabilityProjectRoot() { var baseDir = AppContext.BaseDirectory; var dir = new DirectoryInfo(baseDir); while (dir != null && dir.Parent != null) { if (dir.GetDirectories(".git").Any()) return dir.FullName; dir = dir.Parent; } dir = new DirectoryInfo(baseDir); while (dir != null && dir.Parent != null) { if (dir.GetFiles("*.sln").Any()) return dir.FullName; dir = dir.Parent; } return baseDir; } private class ServiceLogMetricRow { public string ConsumeId { get; set; } public string BeforeImage { get; set; } public string AfterImage { get; set; } } private class DuplicateRechargeRawRow { public string BizId { get; set; } public string BizType { get; set; } public string BizTypeName { get; set; } public string MemberId { get; set; } public string MemberName { get; set; } public string StoreId { get; set; } public string StoreName { get; set; } public DateTime BizTime { get; set; } public decimal Amount { get; set; } } private class StoreRankingMetricsRow { public string StoreId { get; set; } public decimal BillingPerformance { get; set; } public decimal ConsumePerformance { get; set; } public decimal RefundAmount { get; set; } public int BillingCount { get; set; } public int ConsumeCount { get; set; } public int HeadCount { get; set; } public int PersonCount { get; set; } public decimal ProjectCount { get; set; } public decimal TargetPerformance { get; set; } } /// /// 批量加载门店排行指标,避免门店循环 N+1 /// private async Task> LoadStoreRankingMetricsBatchAsync( List storeIds, DateTime startDate, DateTime endDateTime, string statisticsMonth) { if (!storeIds.Any()) return new List(); var billingRows = await _db.Queryable() .Where(x => storeIds.Contains(x.Djmd) && x.IsEffective == 1) .Where(x => x.Kdrq.HasValue && x.Kdrq.Value >= startDate && x.Kdrq.Value <= endDateTime) .GroupBy(x => x.Djmd) .Select(x => new StoreRankingMetricsRow { StoreId = x.Djmd, BillingPerformance = SqlFunc.AggregateSum(x.Sfyj), BillingCount = SqlFunc.AggregateCount(x.Id) }) .ToListAsync(); var refundRows = await _db.Queryable() .Where(x => storeIds.Contains(x.Md) && x.IsEffective == 1) .Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDateTime.Date) .GroupBy(x => x.Md) .Select(x => new { StoreId = x.Md, RefundAmount = SqlFunc.AggregateSum(x.ActualRefundAmount ?? x.Tkje ?? 0) }) .ToListAsync(); var consumeRows = await _db.Queryable() .Where(x => storeIds.Contains(x.Md) && x.IsEffective == 1) .Where(x => x.Hksj.HasValue && x.Hksj.Value >= startDate && x.Hksj.Value <= endDateTime) .GroupBy(x => x.Md) .Select(x => new StoreRankingMetricsRow { StoreId = x.Md, ConsumePerformance = SqlFunc.AggregateSum(x.Xfje ?? 0), ConsumeCount = SqlFunc.AggregateCount(x.Id) }) .ToListAsync(); var headCountRows = await _db.Queryable() .Where(x => storeIds.Contains(x.Md) && x.IsEffective == 1) .Where(x => x.Hksj.HasValue && x.Hksj.Value >= startDate && x.Hksj.Value <= endDateTime) .Where(x => !string.IsNullOrWhiteSpace(x.Hy)) .GroupBy(x => x.Md) .Select(x => new { StoreId = x.Md, HeadCount = SqlFunc.AggregateDistinctCount(x.Hy) }) .ToListAsync(); var storeIdsStr = string.Join("','", storeIds); var personCountSql = $@" SELECT xh.Md AS StoreId, COUNT(DISTINCT CONCAT(xh.Hy, '-', DATE_FORMAT(xh.Hksj, '%Y-%m-%d'))) AS PersonCount FROM lq_xh_hyhk xh WHERE xh.Md IN ('{storeIdsStr}') AND xh.F_IsEffective = 1 AND xh.Hksj >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND xh.Hksj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}' AND xh.Hy IS NOT NULL AND TRIM(xh.Hy) != '' GROUP BY xh.Md"; var personCountRows = await _db.Ado.SqlQueryAsync(personCountSql); var projectCountSql = $@" SELECT xh.Md AS StoreId, COALESCE(SUM(COALESCE(px.F_OriginalProjectNumber, px.F_ProjectNumber, 0)), 0) AS ProjectCount FROM lq_xh_pxmx px INNER JOIN lq_xh_hyhk xh ON px.F_ConsumeInfoId = xh.F_Id WHERE xh.Md IN ('{storeIdsStr}') AND xh.F_IsEffective = 1 AND px.F_IsEffective = 1 AND xh.Hksj >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND xh.Hksj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}' GROUP BY xh.Md"; var projectCountRows = await _db.Ado.SqlQueryAsync(projectCountSql); var targetRows = await _db.Queryable() .Where(x => storeIds.Contains(x.StoreId) && x.Month == statisticsMonth) .GroupBy(x => x.StoreId) .Select(x => new { StoreId = x.StoreId, Target = SqlFunc.AggregateSum(x.BusinessUnitTarget) }) .ToListAsync(); var billingDict = billingRows.ToDictionary(x => x.StoreId ?? string.Empty, x => x); var refundDict = refundRows.ToDictionary(x => x.StoreId ?? string.Empty, x => x.RefundAmount); var consumeDict = consumeRows.ToDictionary(x => x.StoreId ?? string.Empty, x => x); var headCountDict = headCountRows.ToDictionary(x => x.StoreId ?? string.Empty, x => x.HeadCount); var personCountDict = (personCountRows ?? Enumerable.Empty()) .ToDictionary(x => (string)(x.StoreId ?? string.Empty), x => Convert.ToInt32(x.PersonCount ?? 0)); var projectCountDict = (projectCountRows ?? Enumerable.Empty()) .ToDictionary(x => (string)(x.StoreId ?? string.Empty), x => Convert.ToDecimal(x.ProjectCount ?? 0)); var targetDict = targetRows.ToDictionary(x => x.StoreId ?? string.Empty, x => x.Target); return storeIds.Select(storeId => { billingDict.TryGetValue(storeId, out var billing); consumeDict.TryGetValue(storeId, out var consume); refundDict.TryGetValue(storeId, out var refundAmount); headCountDict.TryGetValue(storeId, out var headCount); personCountDict.TryGetValue(storeId, out var personCount); projectCountDict.TryGetValue(storeId, out var projectCount); targetDict.TryGetValue(storeId, out var targetPerformance); var billingPerformance = billing?.BillingPerformance ?? 0m; return new StoreRankingMetricsRow { StoreId = storeId, BillingPerformance = billingPerformance, ConsumePerformance = consume?.ConsumePerformance ?? 0m, RefundAmount = refundAmount, BillingCount = billing?.BillingCount ?? 0, ConsumeCount = consume?.ConsumeCount ?? 0, HeadCount = headCount, PersonCount = personCount, ProjectCount = projectCount, TargetPerformance = targetPerformance }; }).ToList(); } #endregion } }