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