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_kd_pxmx;
using NCC.Extend.Entitys.lq_xh_pxmx;
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();
}
if (!string.IsNullOrWhiteSpace(input.ExceptionType))
{
var exceptionType = input.ExceptionType.Trim();
auditRows = auditRows.Where(x =>
!string.IsNullOrWhiteSpace(x.ExceptionType) &&
x.ExceptionType.Equals(exceptionType, 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,
OperatorId = bill.CreateUser
})
.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,
OperatorId = consume.Czry
})
.ToListAsync();
// 品项内容指纹:用于判定「内容一样」的重复开单/耗卡
await FillDuplicateRechargeContentKeysAsync(billingRows, consumeRows);
var grouped = billingRows.Concat(consumeRows)
.GroupBy(x => new { x.MemberId, BizDate = x.BizTime.Date })
.Where(g => g.Count() >= 2);
var result = new List();
// 短时间同金额同操作人同内容:默认 3 分钟,优先判定为异常开单/异常耗卡(疑似卡顿重复提交)
const int shortIntervalMinutes = 3;
const decimal amountEpsilon = 0.01m;
foreach (var group in grouped)
{
var ordered = group.OrderBy(x => x.BizTime).ThenBy(x => x.BizId).ToList();
var dayTotal = ordered.Count;
var coveredBizIds = new HashSet(StringComparer.OrdinalIgnoreCase);
var billingOrdered = ordered
.Where(x => x.BizType == nameof(DuplicateRechargeBizType.Billing))
.ToList();
var consumeOrdered = ordered
.Where(x => x.BizType == nameof(DuplicateRechargeBizType.Consume))
.ToList();
// 异常开单/耗卡:金额>0,短间隔同金额同操作人同内容,合并为单行
var shortIntervalClusters = BuildDuplicateRechargeShortIntervalClusters(
billingOrdered, shortIntervalMinutes, amountEpsilon)
.Concat(BuildDuplicateRechargeShortIntervalClusters(
consumeOrdered, shortIntervalMinutes, amountEpsilon));
foreach (var cluster in shortIntervalClusters)
{
foreach (var item in cluster)
coveredBizIds.Add(item.BizId);
var clusterOrdered = cluster
.OrderBy(x => x.BizTime)
.ThenBy(x => x.BizId)
.ToList();
var mainRow = clusterOrdered[clusterOrdered.Count - 1];
var clusterFirst = clusterOrdered[0];
var exceptionType = mainRow.BizType == nameof(DuplicateRechargeBizType.Billing)
? "ShortIntervalSameAmountBilling"
: "ShortIntervalSameAmountConsume";
var exceptionTypeName = mainRow.BizType == nameof(DuplicateRechargeBizType.Billing)
? "异常开单"
: "异常耗卡";
var clusterRelatedList = clusterOrdered.Select((x, idx) =>
new BusinessUnitDuplicateRechargeRelatedBizOutput
{
BizId = x.BizId,
BizType = x.BizType,
BizTypeName = x.BizTypeName,
BizTime = x.BizTime,
Amount = x.Amount,
DaySequence = idx + 1,
IsFirst = idx == 0,
IsCurrent = x.BizId == mainRow.BizId,
OperatorId = x.OperatorId
}).ToList();
result.Add(new BusinessUnitDuplicateRechargeAuditListOutput
{
BizId = mainRow.BizId,
BizType = mainRow.BizType,
BizTypeName = mainRow.BizTypeName,
MemberId = mainRow.MemberId,
MemberName = string.IsNullOrWhiteSpace(mainRow.MemberName) ? "无" : mainRow.MemberName,
StoreId = mainRow.StoreId,
StoreName = string.IsNullOrWhiteSpace(mainRow.StoreName) ? "无" : mainRow.StoreName,
BizTime = mainRow.BizTime,
BizDate = mainRow.BizTime.Date,
Amount = mainRow.Amount,
DaySequence = clusterOrdered.Count,
DayTotalCount = dayTotal,
ExceptionType = exceptionType,
ExceptionTypeName = exceptionTypeName,
IsHighlight = true,
FirstBizId = clusterFirst.BizId,
FirstBizTypeName = clusterFirst.BizTypeName,
FirstBizTime = clusterFirst.BizTime,
FirstAmount = clusterFirst.Amount,
RelatedBizSummary = BuildShortIntervalClusterSummary(clusterOrdered),
RelatedBizList = clusterRelatedList
});
}
// 同日重复:按类型各合并为 1 行,避免第2笔起逐条刷屏;类型优先级:同类型多笔 > 开单+耗卡
var uncoveredBilling = billingOrdered
.Where(x => !coveredBizIds.Contains(x.BizId))
.OrderBy(x => x.BizTime)
.ThenBy(x => x.BizId)
.ToList();
var uncoveredConsume = consumeOrdered
.Where(x => !coveredBizIds.Contains(x.BizId))
.OrderBy(x => x.BizTime)
.ThenBy(x => x.BizId)
.ToList();
if (uncoveredBilling.Count >= 2)
{
result.Add(BuildSameDayAuditRow(
uncoveredBilling,
dayTotal,
"SameDayDuplicateBilling",
"同日重复开单"));
}
if (uncoveredConsume.Count >= 2)
{
result.Add(BuildSameDayAuditRow(
uncoveredConsume,
dayTotal,
"SameDayDuplicateConsume",
"同日重复耗卡"));
}
// 说明:不再自动产出「同日开单+耗卡」。
// 一开一耗是门店常态,计入审计会严重刷屏并淹没真正的同类型重复/卡顿重复提交。
}
return result;
}
///
/// 构建同日重复/混合审计行(同组合并为单行,RelatedBizList 仅含本组单据)。
///
private static BusinessUnitDuplicateRechargeAuditListOutput BuildSameDayAuditRow(
IReadOnlyList groupOrdered,
int dayTotal,
string exceptionType,
string exceptionTypeName)
{
var mainRow = groupOrdered[groupOrdered.Count - 1];
var first = groupOrdered[0];
var relatedList = groupOrdered.Select((x, idx) =>
new BusinessUnitDuplicateRechargeRelatedBizOutput
{
BizId = x.BizId,
BizType = x.BizType,
BizTypeName = x.BizTypeName,
BizTime = x.BizTime,
Amount = x.Amount,
DaySequence = idx + 1,
IsFirst = idx == 0,
IsCurrent = x.BizId == mainRow.BizId,
OperatorId = x.OperatorId
}).ToList();
var relatedSummary = string.Join(";", groupOrdered.Select((x, idx) =>
$"第{idx + 1}笔[{x.BizTypeName}] {x.BizId} ¥{x.Amount:0.##} {x.BizTime:HH:mm:ss}"));
return new BusinessUnitDuplicateRechargeAuditListOutput
{
BizId = mainRow.BizId,
BizType = mainRow.BizType,
BizTypeName = mainRow.BizTypeName,
MemberId = mainRow.MemberId,
MemberName = string.IsNullOrWhiteSpace(mainRow.MemberName) ? "无" : mainRow.MemberName,
StoreId = mainRow.StoreId,
StoreName = string.IsNullOrWhiteSpace(mainRow.StoreName) ? "无" : mainRow.StoreName,
BizTime = mainRow.BizTime,
BizDate = mainRow.BizTime.Date,
Amount = mainRow.Amount,
DaySequence = groupOrdered.Count,
DayTotalCount = dayTotal,
ExceptionType = exceptionType,
ExceptionTypeName = exceptionTypeName,
IsHighlight = false,
FirstBizId = first.BizId,
FirstBizTypeName = first.BizTypeName,
FirstBizTime = first.BizTime,
FirstAmount = first.Amount,
RelatedBizSummary = relatedSummary,
RelatedBizList = relatedList
};
}
///
/// 按短间隔同金额同操作人同内容构建连通簇(开单/耗卡分别建簇,簇大小≥2)。
/// 先按操作人+内容指纹+金额分桶,再在桶内按时间间隔做连通,避免不同金额混入同组。
///
private static List> BuildDuplicateRechargeShortIntervalClusters(
IReadOnlyList sameTypeOrdered,
int shortIntervalMinutes,
decimal amountEpsilon)
{
if (sameTypeOrdered == null || sameTypeOrdered.Count < 2)
return new List>();
var result = new List>();
var buckets = sameTypeOrdered
.Where(x =>
x.Amount > 0m &&
!string.IsNullOrWhiteSpace(x.OperatorId) &&
!string.IsNullOrWhiteSpace(x.ContentKey))
.GroupBy(x => new
{
OperatorId = (x.OperatorId ?? string.Empty).Trim().ToLowerInvariant(),
ContentKey = x.ContentKey ?? string.Empty,
// 金额分桶:先按分取整,再在桶内用 epsilon 校验,杜绝不同金额进同一簇
AmountBucket = Math.Round(x.Amount, 2, MidpointRounding.AwayFromZero)
});
foreach (var bucket in buckets)
{
var ordered = bucket
.OrderBy(x => x.BizTime)
.ThenBy(x => x.BizId)
.ToList();
if (ordered.Count < 2) continue;
// 桶内再按 epsilon 拆成严格同金额子组(防止四舍五入边界误合)
var amountGroups = new List>();
foreach (var row in ordered)
{
var matched = amountGroups.FirstOrDefault(g =>
IsDuplicateRechargeSameAmount(g[0].Amount, row.Amount, amountEpsilon));
if (matched != null) matched.Add(row);
else amountGroups.Add(new List { row });
}
foreach (var amountGroup in amountGroups)
{
if (amountGroup.Count < 2) continue;
result.AddRange(BuildShortIntervalClustersByTime(amountGroup, shortIntervalMinutes));
}
}
return result;
}
///
/// 同一金额/操作人/内容下,按时间间隔≤N分钟做连通簇。
///
private static List> BuildShortIntervalClustersByTime(
IReadOnlyList sameAmountOrdered,
int shortIntervalMinutes)
{
var count = sameAmountOrdered.Count;
if (count < 2) return new List>();
var parent = Enumerable.Range(0, count).ToArray();
int Find(int x)
{
while (parent[x] != x)
{
parent[x] = parent[parent[x]];
x = parent[x];
}
return x;
}
void Union(int a, int b)
{
var rootA = Find(a);
var rootB = Find(b);
if (rootA != rootB) parent[rootB] = rootA;
}
for (var i = 0; i < count; i++)
{
for (var j = i + 1; j < count; j++)
{
if ((sameAmountOrdered[j].BizTime - sameAmountOrdered[i].BizTime).TotalMinutes >
shortIntervalMinutes)
break;
Union(i, j);
}
}
return parent
.Select((_, idx) => idx)
.GroupBy(Find)
.Where(g => g.Count() >= 2)
.Select(g => g
.OrderBy(idx => sameAmountOrdered[idx].BizTime)
.ThenBy(idx => sameAmountOrdered[idx].BizId)
.Select(idx => sameAmountOrdered[idx])
.ToList())
.ToList();
}
private static string BuildShortIntervalClusterSummary(IReadOnlyList clusterOrdered)
{
if (clusterOrdered == null || clusterOrdered.Count < 2) return string.Empty;
var parts = new List { $"共{clusterOrdered.Count}笔相同内容疑似重复提交" };
for (var i = 0; i < clusterOrdered.Count; i++)
{
var current = clusterOrdered[i];
var segment =
$"[{current.BizTypeName}] {current.BizId} ¥{current.Amount:0.##} {current.BizTime:HH:mm:ss}";
if (i > 0)
{
var previous = clusterOrdered[i - 1];
segment += $"(距上一笔 {(current.BizTime - previous.BizTime).TotalSeconds:0} 秒)";
}
parts.Add(segment);
}
return string.Join(";", parts);
}
///
/// 批量填充开单/耗卡品项内容指纹(品项+次数+金额)
///
private async Task FillDuplicateRechargeContentKeysAsync(
List billingRows,
List consumeRows)
{
var billingIds = billingRows.Select(x => x.BizId).Where(x => !string.IsNullOrWhiteSpace(x)).Distinct().ToList();
if (billingIds.Any())
{
var billingItems = await _db.Queryable()
.Where(x => billingIds.Contains(x.Glkdbh) && x.IsEffective == 1)
.Select(x => new { x.Glkdbh, x.Px, x.ProjectNumber, x.ActualPrice })
.ToListAsync();
var billingMap = billingItems
.GroupBy(x => x.Glkdbh)
.ToDictionary(
g => g.Key,
g => string.Join("|", g.OrderBy(i => i.Px)
.ThenBy(i => i.ProjectNumber)
.ThenBy(i => i.ActualPrice)
.Select(i => $"{i.Px}:{i.ProjectNumber:0.####}:{i.ActualPrice:0.####}")));
foreach (var row in billingRows)
{
row.ContentKey = billingMap.TryGetValue(row.BizId, out var key) ? key : string.Empty;
}
}
var consumeIds = consumeRows.Select(x => x.BizId).Where(x => !string.IsNullOrWhiteSpace(x)).Distinct().ToList();
if (consumeIds.Any())
{
var consumeItems = await _db.Queryable()
.Where(x => consumeIds.Contains(x.ConsumeInfoId) && x.IsEffective == 1)
.Select(x => new { x.ConsumeInfoId, x.Px, x.ProjectNumber, x.TotalPrice })
.ToListAsync();
var consumeMap = consumeItems
.GroupBy(x => x.ConsumeInfoId)
.ToDictionary(
g => g.Key,
g => string.Join("|", g.OrderBy(i => i.Px)
.ThenBy(i => i.ProjectNumber)
.ThenBy(i => i.TotalPrice)
.Select(i => $"{i.Px}:{i.ProjectNumber:0.####}:{i.TotalPrice:0.####}")));
foreach (var row in consumeRows)
{
row.ContentKey = consumeMap.TryGetValue(row.BizId, out var key) ? key : string.Empty;
}
}
}
private static bool IsDuplicateRechargeSameAmount(decimal a, decimal b, decimal epsilon)
{
return Math.Abs(a - b) < epsilon;
}
private static bool IsDuplicateRechargeSameOperator(string a, string b)
{
var left = (a ?? string.Empty).Trim();
var right = (b ?? string.Empty).Trim();
if (string.IsNullOrEmpty(left) || string.IsNullOrEmpty(right)) return false;
return string.Equals(left, right, StringComparison.OrdinalIgnoreCase);
}
private static bool IsDuplicateRechargeSameContent(string a, string b)
{
var left = a ?? string.Empty;
var right = b ?? string.Empty;
// 两边都无明细时,不因「空内容相同」误判为异常重复
if (string.IsNullOrWhiteSpace(left) || string.IsNullOrWhiteSpace(right)) return false;
return string.Equals(left, right, StringComparison.Ordinal);
}
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; }
/// 操作人(开单 CreateUser / 耗卡 Czry)
public string OperatorId { get; set; }
/// 品项内容指纹(品项+次数+金额)
public string ContentKey { 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
}
}