using System; using System.Collections.Generic; using System.Globalization; using System.Linq; using System.Threading.Tasks; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Logging; using NCC.Common.Configuration; using NCC.Common.Core.Manager; using NCC.Common.Model.NPOI; using NCC.DataEncryption; using NCC.Dependency; using NCC.DynamicApiController; using NCC.FriendlyException; using NCC.Extend.Entitys.Dto.LqTechDepartmentDashboard; using NCC.Extend.Entitys.Enum; using NCC.Extend.Entitys.lq_kd_jksyj; using NCC.Extend.Entitys.lq_hytk_jksyj; using NCC.Extend.Entitys.lq_md_target; using NCC.Extend.Entitys.lq_xh_hyhk; using NCC.Extend.Entitys.lq_xh_pxmx; using NCC.Extend.Entitys.lq_kd_kdjlb; using NCC.Extend.Entitys.lq_kd_pxmx; using NCC.Extend.Entitys.lq_mdxx; using NCC.Extend.Entitys.lq_tech_teacher_salary_statistics; using NCC.Extend.Entitys.lq_tech_general_manager_salary_statistics; using NCC.Extend.Entitys.lq_xh_kjbsyj; using NCC.Extend.Entitys.lq_xmzl; using NCC.Extend.Entitys.lq_hytk_hytk; using NCC.Extend.Entitys.lq_kd_kjbsyj; using NCC.Extend.Entitys.lq_hytk_kjbsyj; using NCC.Extend.Entitys.lq_kd_deductinfo; using NCC.Extend.Entitys.lq_khxx; using NCC.Extend.Entitys; using NCC.Extend.Helpers; using NCC.Common.Filter; using NCC.System.Entitys.Permission; using SqlSugar; namespace NCC.Extend { /// /// 科技部驾驶舱服务 /// [ApiDescriptionSettings(Tag = "科技部驾驶舱服务", Name = "LqTechDepartmentDashboard", Order = 202)] [Route("api/Extend/[controller]")] public class LqTechDepartmentDashboardService : IDynamicApiController, ITransient { private readonly ISqlSugarClient _db; private readonly ILogger _logger; private readonly IUserManager _userManager; /// /// 初始化科技部驾驶舱服务 /// public LqTechDepartmentDashboardService( ISqlSugarClient db, ILogger logger, IUserManager userManager) { _db = db; _logger = logger; _userManager = userManager; } #region 公共筛选 private class TechDashboardRangeContext { public DateTime StartInclusive { get; set; } public DateTime EndInclusive { get; set; } public List StatisticsMonths { get; set; } = new List(); public string StatisticsMonthFallback { get; set; } public bool HasDateRange { get; set; } } private void ValidateStatisticsInput(TechDepartmentDashboardStatisticsInput input) { if (input == null) throw NCCException.Oh("请求参数不能为空"); if (input.StartDate.HasValue ^ input.EndDate.HasValue) throw NCCException.Oh("开始日期与结束日期需同时传入"); if (input.StartDate.HasValue && input.EndDate.HasValue) { if (input.EndDate.Value.Date < input.StartDate.Value.Date) throw NCCException.Oh("结束日期不能早于开始日期"); } else if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6) { throw NCCException.Oh("请传入开始日期与结束日期,或统计月份(YYYYMM格式)"); } if (string.IsNullOrWhiteSpace(input.TechDepartmentId) && (input.StoreIds == null || !input.StoreIds.Any())) throw NCCException.Oh("科技部ID和门店ID列表不能同时为空,必须传入其中一个"); if (!string.IsNullOrWhiteSpace(input.TechDepartmentId) && input.StoreIds != null && input.StoreIds.Any()) throw NCCException.Oh("科技部ID和门店ID列表不能同时传入,请只传入其中一个"); } private static List BuildMonthList(DateTime start, DateTime end) { var months = new List(); var cursor = new DateTime(start.Year, start.Month, 1); var endMonth = new DateTime(end.Year, end.Month, 1); while (cursor <= endMonth) { months.Add(cursor.ToString("yyyyMM")); cursor = cursor.AddMonths(1); } return months; } private static DateTime ResolveEndDateTime(DateTime endDate) { var end = endDate.Date; return end == DateTime.Today ? DateTime.Now : end.AddHours(23).AddMinutes(59).AddSeconds(59); } private static (DateTime startDate, DateTime endDateTime) GetMonthRange(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 TechDashboardRangeContext NormalizeDateRange(TechDepartmentDashboardStatisticsInput input) { if (input.StartDate.HasValue && input.EndDate.HasValue) { var start = input.StartDate.Value.Date; var end = input.EndDate.Value.Date; if (end < start) throw NCCException.Oh("结束日期不能早于开始日期"); return new TechDashboardRangeContext { StartInclusive = start, EndInclusive = ResolveEndDateTime(end), StatisticsMonths = BuildMonthList(start, end), StatisticsMonthFallback = end.ToString("yyyyMM"), HasDateRange = true }; } if (!string.IsNullOrWhiteSpace(input.StatisticsMonth)) { if (input.StatisticsMonth.Length != 6) throw NCCException.Oh("统计月份格式错误,必须为YYYYMM格式"); var (startDate, endDateTime) = GetMonthRange(input.StatisticsMonth); return new TechDashboardRangeContext { StartInclusive = startDate, EndInclusive = endDateTime, StatisticsMonths = new List { input.StatisticsMonth }, StatisticsMonthFallback = input.StatisticsMonth, HasDateRange = false }; } throw NCCException.Oh("请传入开始日期与结束日期,或统计月份"); } private static TechDashboardRangeContext NormalizeDateRange(DateTime start, DateTime end) { var startDate = start.Date; var endDate = end.Date; if (endDate < startDate) throw NCCException.Oh("结束日期不能早于开始日期"); return new TechDashboardRangeContext { StartInclusive = startDate, EndInclusive = ResolveEndDateTime(endDate), StatisticsMonths = BuildMonthList(startDate, endDate), StatisticsMonthFallback = endDate.ToString("yyyyMM"), HasDateRange = true }; } private async Task<(List storeIds, TechDashboardRangeContext range)> ResolveRangeAsync( TechDepartmentDashboardStatisticsInput input) { ValidateStatisticsInput(input); var range = NormalizeDateRange(input); var storeIds = await GetStoreIdsForDateRangeAsync( input.TechDepartmentId, input.StoreIds, range.StatisticsMonths); return (storeIds, range); } private async Task> GetStoreIdsAsync(string techDepartmentId, List storeIds, string statisticsMonth) { if (!string.IsNullOrWhiteSpace(techDepartmentId)) { var deptIds = await ResolveTechDepartmentIdsAsync(techDepartmentId); return await _db.Queryable() .Where(x => deptIds.Contains(x.TechDepartment) && x.Month == statisticsMonth) .Select(x => x.StoreId) .Distinct() .ToListAsync(); } return storeIds ?? new List(); } private async Task> GetStoreIdsAsync(TechDepartmentDashboardStatisticsInput input) { var (storeIds, _) = await ResolveRangeAsync(input); return storeIds; } private async Task> GetStoreIdsForMonthAsync(string techDepartmentId, List storeIds, string month) { return await GetStoreIdsAsync(techDepartmentId, storeIds, month); } private async Task> GetStoreIdsForDateRangeAsync( string techDepartmentId, List storeIds, List months) { if (months == null || !months.Any()) return new List(); var map = await BuildMonthStoreIdsMapAsync(techDepartmentId, storeIds, months.Distinct().ToList()); return map.Values.SelectMany(x => x).Distinct().ToList(); } /// /// 各自然月门店归属(科技部维度一次查 lq_md_target;仅 storeIds 时每月相同列表) /// private async Task>> BuildMonthStoreIdsMapAsync( string techDepartmentId, List storeIds, IReadOnlyList months) { var map = months.ToDictionary(m => m, _ => new List()); if (months == null || months.Count == 0) return map; if (!string.IsNullOrWhiteSpace(techDepartmentId)) { var deptIds = await ResolveTechDepartmentIdsAsync(techDepartmentId); var monthList = months.Distinct().ToList(); var rows = await _db.Queryable() .Where(x => deptIds.Contains(x.TechDepartment) && monthList.Contains(x.Month)) .Select(x => new { x.Month, x.StoreId }) .ToListAsync(); foreach (var row in rows) { if (string.IsNullOrWhiteSpace(row.Month) || string.IsNullOrWhiteSpace(row.StoreId)) continue; if (map.TryGetValue(row.Month, out var list)) list.Add(row.StoreId); } foreach (var m in months) map[m] = map[m].Distinct().ToList(); } else { var ids = (storeIds ?? new List()).Where(x => !string.IsNullOrWhiteSpace(x)).Distinct().ToList(); foreach (var m in months) map[m] = ids.ToList(); } return map; } private static List BuildTrendMonthsFromBase(string baseMonthYyyyMm, int monthCount) { var baseMonth = DateTime.ParseExact(baseMonthYyyyMm, "yyyyMM", CultureInfo.InvariantCulture); var months = new List(monthCount); for (var i = monthCount - 1; i >= 0; i--) months.Add(baseMonth.AddMonths(-i).ToString("yyyyMM")); return months; } private static (DateTime rangeStart, DateTime rangeEndDateTime, DateTime refundEndDate) GetTrendMonthsQueryRange( IReadOnlyList monthsOrdered) { var first = monthsOrdered[0]; var last = monthsOrdered[monthsOrdered.Count - 1]; var (rangeStart, _) = GetMonthRange(first); var (_, rangeEndDateTime) = GetMonthRange(last); return (rangeStart, rangeEndDateTime, rangeEndDateTime.Date); } private class MonthPerformanceBillingAggRow { public string StatMonth { get; set; } public decimal TraceBilling { get; set; } public decimal CellBilling { get; set; } public decimal SlimBilling { get; set; } } private class MonthPerformanceRefundAggRow { public string StatMonth { get; set; } public decimal TraceRefund { get; set; } public decimal CellRefund { get; set; } public decimal SlimRefund { get; set; } } private class MonthDecimalAggRow { public string StatMonth { get; set; } public decimal Amount { get; set; } } private static string BuildTechDepartmentStoreMonthJoinSql( IReadOnlyList techDepartmentIds, string storeColumn, string timeColumn) { if (techDepartmentIds == null || !techDepartmentIds.Any()) return string.Empty; var idsStr = string.Join( "','", techDepartmentIds.Where(x => !string.IsNullOrWhiteSpace(x)).Distinct()); if (string.IsNullOrWhiteSpace(idsStr)) return string.Empty; return $@" INNER JOIN lq_md_target tgt ON tgt.F_StoreId = {storeColumn} AND tgt.F_Month = DATE_FORMAT({timeColumn}, '%Y%m') AND tgt.F_TechDepartment IN ('{idsStr}')"; } private string BuildStoreIdsFilterSql(string techDepartmentId, List storeIds, string storeColumn) { if (!string.IsNullOrWhiteSpace(techDepartmentId)) return string.Empty; if (storeIds == null || !storeIds.Any()) return " AND 1=0 "; var storeIdsStr = string.Join("','", storeIds); return $" AND {storeColumn} IN ('{storeIdsStr}') "; } private async Task> QueryPerformanceBillingByMonthAsync( string techDepartmentId, List storeIds, DateTime rangeStart, DateTime rangeEndDateTime) { var deptIds = await ResolveTechDepartmentIdsAsync(techDepartmentId); var targetJoin = BuildTechDepartmentStoreMonthJoinSql(deptIds, "j.F_StoreId", "j.yjsj"); var storeFilter = BuildStoreIdsFilterSql(techDepartmentId, storeIds, "j.F_StoreId"); var sql = $@" SELECT DATE_FORMAT(j.yjsj, '%Y%m') AS StatMonth, COALESCE(SUM(CASE WHEN j.F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlTraceabilityIn}) THEN CAST(j.jksyj AS DECIMAL(18,2)) ELSE 0 END), 0) AS TraceBilling, COALESCE(SUM(CASE WHEN j.F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlCellIn}) THEN CAST(j.jksyj AS DECIMAL(18,2)) ELSE 0 END), 0) AS CellBilling, COALESCE(SUM(CASE WHEN j.F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlSlimIn}) THEN CAST(j.jksyj AS DECIMAL(18,2)) ELSE 0 END), 0) AS SlimBilling FROM lq_kd_jksyj j {targetJoin} WHERE j.F_IsEffective = 1 AND j.yjsj >= '{rangeStart:yyyy-MM-dd HH:mm:ss}' AND j.yjsj <= '{rangeEndDateTime:yyyy-MM-dd HH:mm:ss}' AND j.F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlDashboardKemeiIn}) {storeFilter} GROUP BY StatMonth"; var rows = await _db.Ado.SqlQueryAsync(sql); return (rows ?? new List()) .Where(x => !string.IsNullOrWhiteSpace(x.StatMonth)) .ToDictionary( x => x.StatMonth, x => (x.TraceBilling, x.CellBilling, x.SlimBilling)); } private async Task> QueryPerformanceRefundByMonthAsync( string techDepartmentId, List storeIds, DateTime rangeStart, DateTime refundEndDate) { var deptIds = await ResolveTechDepartmentIdsAsync(techDepartmentId); var targetJoin = BuildTechDepartmentStoreMonthJoinSql(deptIds, "r.F_StoreId", "r.tksj"); var storeFilter = BuildStoreIdsFilterSql(techDepartmentId, storeIds, "r.F_StoreId"); var sql = $@" SELECT DATE_FORMAT(r.tksj, '%Y%m') AS StatMonth, COALESCE(SUM(CASE WHEN r.F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlTraceabilityIn}) THEN CAST(r.jksyj AS DECIMAL(18,2)) ELSE 0 END), 0) AS TraceRefund, COALESCE(SUM(CASE WHEN r.F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlCellIn}) THEN CAST(r.jksyj AS DECIMAL(18,2)) ELSE 0 END), 0) AS CellRefund, COALESCE(SUM(CASE WHEN r.F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlSlimIn}) THEN CAST(r.jksyj AS DECIMAL(18,2)) ELSE 0 END), 0) AS SlimRefund FROM lq_hytk_jksyj r {targetJoin} WHERE r.F_IsEffective = 1 AND r.tksj >= '{rangeStart:yyyy-MM-dd} 00:00:00' AND r.tksj <= '{refundEndDate:yyyy-MM-dd} 23:59:59' {storeFilter} GROUP BY StatMonth"; var rows = await _db.Ado.SqlQueryAsync(sql); return (rows ?? new List()) .Where(x => !string.IsNullOrWhiteSpace(x.StatMonth)) .ToDictionary( x => x.StatMonth, x => (x.TraceRefund, x.CellRefund, x.SlimRefund)); } /// /// 完整自然月业绩趋势(批量聚合开单/退卡,按月 md_target 归属口径) /// private async Task> BuildPerformanceTrendByFullMonthsAsync( string techDepartmentId, List storeIds, IReadOnlyList monthsOrdered) { var monthStoreMap = await BuildMonthStoreIdsMapAsync(techDepartmentId, storeIds, monthsOrdered); var (rangeStart, rangeEndDateTime, refundEndDate) = GetTrendMonthsQueryRange(monthsOrdered); var billingByMonth = await QueryPerformanceBillingByMonthAsync( techDepartmentId, storeIds, rangeStart, rangeEndDateTime); var refundByMonth = await QueryPerformanceRefundByMonthAsync( techDepartmentId, storeIds, rangeStart, refundEndDate); var trendData = new List(); decimal cumulativeTraceability = 0m; decimal cumulativeCell = 0m; decimal cumulativeSlim = 0m; decimal cumulativeTotal = 0m; foreach (var month in monthsOrdered) { if (!monthStoreMap.TryGetValue(month, out var storeIdsForMonth) || !storeIdsForMonth.Any()) { trendData.Add(new PerformanceTrendPoint { Month = month }); continue; } billingByMonth.TryGetValue(month, out var billing); refundByMonth.TryGetValue(month, out var refund); var traceabilityAmount = billing.trace - refund.trace; var cellAmount = billing.cell - refund.cell; var slimAmount = billing.slim - refund.slim; var totalKemeiIncome = traceabilityAmount + cellAmount + slimAmount; cumulativeTraceability += traceabilityAmount; cumulativeCell += cellAmount; cumulativeSlim += slimAmount; cumulativeTotal += totalKemeiIncome; trendData.Add(new PerformanceTrendPoint { Month = month, TraceabilityAmount = traceabilityAmount, CellAmount = cellAmount, SlimAmount = slimAmount, TotalKemeiIncome = totalKemeiIncome, CumulativeTraceabilityAmount = cumulativeTraceability, CumulativeCellAmount = cumulativeCell, CumulativeSlimAmount = cumulativeSlim, CumulativeTotalKemeiIncome = cumulativeTotal }); } return trendData; } private async Task> QueryShareKemeiIncomeByMonthAsync( string techDepartmentId, List storeIds, DateTime rangeStart, DateTime rangeEndDateTime) { var deptIds = await ResolveTechDepartmentIdsAsync(techDepartmentId); var targetJoin = BuildTechDepartmentStoreMonthJoinSql(deptIds, "kd.djmd", "px.yjsj"); var storeFilter = BuildStoreIdsFilterSql(techDepartmentId, storeIds, "kd.djmd"); var sql = $@" SELECT DATE_FORMAT(px.yjsj, '%Y%m') AS StatMonth, COALESCE(SUM(px.F_TotalPrice), 0) AS Amount FROM lq_kd_pxmx px INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id {targetJoin} WHERE px.F_IsEffective = 1 AND px.F_ItemCategory = '科美' AND px.yjsj >= '{rangeStart:yyyy-MM-dd HH:mm:ss}' AND px.yjsj <= '{rangeEndDateTime:yyyy-MM-dd HH:mm:ss}' {storeFilter} GROUP BY StatMonth"; var rows = await _db.Ado.SqlQueryAsync(sql); return (rows ?? new List()) .Where(x => !string.IsNullOrWhiteSpace(x.StatMonth)) .ToDictionary(x => x.StatMonth, x => x.Amount); } private async Task> QueryKemeiRefundAmountByMonthAsync( string techDepartmentId, List storeIds, DateTime rangeStart, DateTime refundEndDate) { var deptIds = await ResolveTechDepartmentIdsAsync(techDepartmentId); var targetJoin = BuildTechDepartmentStoreMonthJoinSql(deptIds, "refund.md", "refund.tksj"); var storeFilter = BuildStoreIdsFilterSql(techDepartmentId, storeIds, "refund.md"); var sql = $@" SELECT DATE_FORMAT(refund.tksj, '%Y%m') AS StatMonth, COALESCE(SUM(COALESCE(mx.tkje, 0)), 0) AS Amount FROM lq_hytk_hytk refund INNER JOIN lq_hytk_mx mx ON mx.F_RefundInfoId = refund.F_Id AND mx.F_IsEffective = 1 AND mx.F_ItemCategory = '科美' {targetJoin} WHERE refund.F_IsEffective = 1 AND refund.tksj >= '{rangeStart:yyyy-MM-dd HH:mm:ss}' AND refund.tksj <= '{refundEndDate:yyyy-MM-dd} 23:59:59' {storeFilter} GROUP BY StatMonth"; var rows = await _db.Ado.SqlQueryAsync(sql); return (rows ?? new List()) .Where(x => !string.IsNullOrWhiteSpace(x.StatMonth)) .ToDictionary(x => x.StatMonth, x => x.Amount); } private async Task<(decimal reimbursement, decimal teacherBase, decimal teacherManual, decimal teacherBillingComm, decimal teacherConsumeComm, decimal gmBase, decimal gmComm)> CalculateShareTrendCostsAsync( List storeIds, DateTime startDate, DateTime endDateTime, string salaryMonth, IReadOnlyList positionNames) { if (storeIds == null || !storeIds.Any()) return (0, 0, 0, 0, 0, 0, 0); var reimbursementAmount = await _db.Queryable( (app, purchase, category) => app.PurchaseRecordsId == purchase.Id && purchase.ReimbursementCategoryId == category.Id) .Where((app, purchase, category) => category.Level1Name == "科技部费用" && app.ApprovalStatus == "已通过" && storeIds.Contains(app.ApplicationStoreId) && app.ApplicationTime >= startDate && app.ApplicationTime <= endDateTime) .SumAsync((app, purchase, category) => purchase.Amount); var teacherBaseSalary = await _db.Queryable() .Where(x => x.StatisticsMonth == salaryMonth && storeIds.Contains(x.StoreId)) .SumAsync(x => x.BaseSalary); var teacherManualFee = await _db.Queryable() .Where(x => x.ItemCategory == "科美" && storeIds.Contains(x.StoreId) && x.Yjsj >= startDate && x.Yjsj <= endDateTime && x.IsEffective == 1) .SumAsync(x => x.LaborCost ?? 0); var teacherBillingComm = await _db.Queryable() .Where(x => x.StatisticsMonth == salaryMonth && storeIds.Contains(x.StoreId)) .SumAsync(x => x.PerformanceCommissionAmount); var teacherConsumeComm = await _db.Queryable() .Where(x => x.StatisticsMonth == salaryMonth && storeIds.Contains(x.StoreId)) .SumAsync(x => x.ConsumeCommissionAmount); var gmBaseSalary = 0m; var gmComm = 0m; if (positionNames != null && positionNames.Any()) { gmBaseSalary = await _db.Queryable() .Where(x => x.StatisticsMonth == salaryMonth && positionNames.Contains(x.Position)) .SumAsync(x => x.BaseSalary); gmComm = await _db.Queryable() .Where(x => x.StatisticsMonth == salaryMonth && positionNames.Contains(x.Position)) .SumAsync(x => x.TotalCommission); } return (reimbursementAmount, teacherBaseSalary, teacherManualFee, teacherBillingComm, teacherConsumeComm, gmBaseSalary, gmComm); } private async Task> BuildShareTrendByFullMonthsAsync( string techDepartmentId, List storeIds, IReadOnlyList monthsOrdered, IReadOnlyList positionNames) { var monthStoreMap = await BuildMonthStoreIdsMapAsync(techDepartmentId, storeIds, monthsOrdered); var (rangeStart, rangeEndDateTime, refundEndDate) = GetTrendMonthsQueryRange(monthsOrdered); var kemeiIncomeByMonth = await QueryShareKemeiIncomeByMonthAsync( techDepartmentId, storeIds, rangeStart, rangeEndDateTime); var kemeiRefundByMonth = await QueryKemeiRefundAmountByMonthAsync( techDepartmentId, storeIds, rangeStart, refundEndDate); var trendData = new List(); decimal cumulativeIncome = 0m; decimal cumulativeCost = 0m; decimal cumulativeProfit = 0m; foreach (var month in monthsOrdered) { if (!monthStoreMap.TryGetValue(month, out var storeIdsForMonth) || !storeIdsForMonth.Any()) { trendData.Add(new ShareTrendPoint { Month = month }); continue; } var (startDate, endDateTime) = GetMonthRange(month); kemeiIncomeByMonth.TryGetValue(month, out var kemeiIncome); kemeiRefundByMonth.TryGetValue(month, out var kemeiRefund); var income = (kemeiIncome - kemeiRefund) * 0.3m; var costs = await CalculateShareTrendCostsAsync( storeIdsForMonth, startDate, endDateTime, month, positionNames); var totalCost = costs.reimbursement + costs.teacherBase + costs.teacherManual + costs.teacherBillingComm + costs.teacherConsumeComm + costs.gmBase + costs.gmComm; var profit = income - totalCost; cumulativeIncome += income; cumulativeCost += totalCost; cumulativeProfit += profit; trendData.Add(new ShareTrendPoint { Month = month, Income = income, CostReimbursement = costs.reimbursement, CostTeacherBase = costs.teacherBase, CostTeacherManual = costs.teacherManual, CostTeacherBillingComm = costs.teacherBillingComm, CostTeacherConsumeComm = costs.teacherConsumeComm, CostGMBase = costs.gmBase, CostGMComm = costs.gmComm, Profit = profit, ProfitRate = income > 0 ? (profit / income) * 100 : 0, CumulativeIncome = cumulativeIncome, CumulativeCost = cumulativeCost, CumulativeProfit = cumulativeProfit }); } return trendData; } private static (DateTime pointStart, DateTime pointEnd) ClipMonthToRange( string statisticsMonth, TechDashboardRangeContext range) { var (monthStart, monthEnd) = GetMonthRange(statisticsMonth); var pointStart = monthStart < range.StartInclusive ? range.StartInclusive : monthStart; var pointEnd = monthEnd > range.EndInclusive ? range.EndInclusive : monthEnd; return (pointStart, pointEnd); } private static bool IsValidTrendMonthCount(int monthCount) { return monthCount == 3 || monthCount == 6 || monthCount == 12; } /// /// 趋势图基准月:有结束日期时用结束日所在月,否则用 statisticsMonth。 /// private static string ResolveTrendBaseMonth(TechDepartmentDashboardStatisticsInput input) { if (input.StartDate.HasValue && input.EndDate.HasValue) return input.EndDate.Value.Date.ToString("yyyyMM", CultureInfo.InvariantCulture); if (!string.IsNullOrWhiteSpace(input.StatisticsMonth) && input.StatisticsMonth.Length == 6) return input.StatisticsMonth; return DateTime.Now.ToString("yyyyMM", CultureInfo.InvariantCulture); } private static TechDepartmentDashboardStatisticsInput ToStatisticsInput( TechDepartmentDashboardStoreDetailListInput input) { return new TechDepartmentDashboardStatisticsInput { TechDepartmentId = input.TechDepartmentId, StoreIds = input.StoreIds, StatisticsMonth = input.StatisticsMonth, StartDate = input.StartDate, EndDate = input.EndDate }; } private static TechDepartmentDashboardStatisticsInput ToStatisticsInput( TechDepartmentDashboardTeacherDetailListInput input) { return new TechDepartmentDashboardStatisticsInput { TechDepartmentId = input.TechDepartmentId, StoreIds = input.StoreIds, StatisticsMonth = input.StatisticsMonth, StartDate = input.StartDate, EndDate = input.EndDate }; } private static TechDepartmentDashboardStatisticsInput ToStatisticsInput( TechDepartmentDashboardBillingDetailListInput input) { return new TechDepartmentDashboardStatisticsInput { TechDepartmentId = input.TechDepartmentId, StoreIds = input.StoreIds, StatisticsMonth = input.StatisticsMonth, StartDate = input.StartDate, EndDate = input.EndDate }; } private static TechDepartmentDashboardStatisticsInput ToStatisticsInput( TechDepartmentDashboardConsumeDetailListInput input) { return new TechDepartmentDashboardStatisticsInput { TechDepartmentId = input.TechDepartmentId, StoreIds = input.StoreIds, StatisticsMonth = input.StatisticsMonth, StartDate = input.StartDate, EndDate = input.EndDate }; } private async Task ResolveTechDepartmentNameAsync(string techDepartmentId) { if (string.IsNullOrWhiteSpace(techDepartmentId)) return null; return await _db.Queryable() .Where(x => x.Id == techDepartmentId && x.DeleteMark == null && x.EnabledMark == 1) .Select(x => x.FullName) .FirstAsync(); } /// /// 科技部门店归属筛选:自身 + 直接子组织(lq_md_target 仅存子部门时,选父级需展开) /// private async Task> ResolveTechDepartmentIdsAsync(string techDepartmentId) { if (string.IsNullOrWhiteSpace(techDepartmentId)) return new List(); var ids = new List { techDepartmentId }; var childIds = await _db.Queryable() .Where(x => x.ParentId == techDepartmentId && x.DeleteMark == null && x.EnabledMark == 1) .Select(x => x.Id) .ToListAsync(); ids.AddRange(childIds); return ids.Distinct().ToList(); } /// /// 总经理薪酬 Position 匹配:有子部门时按子部门全称汇总,否则用当前组织全称 /// private async Task> ResolveTechDepartmentPositionNamesAsync(string techDepartmentId) { if (string.IsNullOrWhiteSpace(techDepartmentId)) return new List(); var childIds = await _db.Queryable() .Where(x => x.ParentId == techDepartmentId && x.DeleteMark == null && x.EnabledMark == 1) .Select(x => x.Id) .ToListAsync(); if (childIds.Any()) { return await _db.Queryable() .Where(x => childIds.Contains(x.Id) && x.DeleteMark == null && x.EnabledMark == 1) .Select(x => x.FullName) .ToListAsync(); } var name = await ResolveTechDepartmentNameAsync(techDepartmentId); return string.IsNullOrWhiteSpace(name) ? new List() : new List { name }; } #endregion /// /// 科美开单实付金额:按品项明细 F_ActualPrice 汇总(非整单 sfyj,避免一单多老师业绩重复累加)。 /// 开单次数为含科美品项的去重开单数。 /// private async Task<(int BillingCount, decimal BillingAmount)> QueryKemeiBillingItemStatsAsync( List storeIds, DateTime startDate, DateTime endDateTime) { if (storeIds == null || !storeIds.Any()) { return (0, 0m); } var storeIdsStr = string.Join("','", storeIds); var sql = $@" SELECT COUNT(DISTINCT kd.F_Id) AS BillingCount, COALESCE(SUM(px.F_ActualPrice), 0) AS BillingAmount FROM lq_kd_pxmx px INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id WHERE px.F_IsEffective = 1 AND kd.F_IsEffective = 1 AND px.F_ItemCategory = '科美' AND kd.djmd IN ('{storeIdsStr}') AND kd.kdrq >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND kd.kdrq <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'"; var rows = await _db.Ado.SqlQueryAsync(sql); var row = rows?.FirstOrDefault(); if (row == null) { return (0, 0m); } return ( Convert.ToInt32(row.BillingCount ?? 0), Convert.ToDecimal(row.BillingAmount ?? 0)); } /// /// 科美退卡统计:仅汇总品项明细表中科美品项的退款金额(lq_hytk_mx.tkje),不含混合退卡中的生美/医美金额。 /// private async Task<(int RefundCount, decimal RefundAmount)> QueryKemeiRefundStatsAsync( List storeIds, DateTime startDate, DateTime endDateTime) { if (storeIds == null || !storeIds.Any()) { return (0, 0m); } var storeIdsStr = string.Join("','", storeIds); var sql = $@" SELECT COUNT(DISTINCT refund.F_Id) AS RefundCount, COALESCE(SUM(COALESCE(mx.tkje, 0)), 0) AS RefundAmount FROM lq_hytk_hytk refund INNER JOIN lq_hytk_mx mx ON mx.F_RefundInfoId = refund.F_Id AND mx.F_IsEffective = 1 AND mx.F_ItemCategory = '科美' WHERE refund.F_IsEffective = 1 AND refund.md IN ('{storeIdsStr}') AND refund.tksj >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND refund.tksj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'"; var rows = await _db.Ado.SqlQueryAsync(sql); var row = rows?.FirstOrDefault(); if (row == null) { return (0, 0m); } return ( Convert.ToInt32(row.RefundCount ?? 0), Convert.ToDecimal(row.RefundAmount ?? 0)); } /// /// 读取 SqlQueryAsync<dynamic> 行中的金额字段(避免 dynamic ?? 0 触发 decimal/int 绑定异常) /// private static decimal ReadDynamicDecimal(dynamic value) { if (value == null || value is DBNull) { return 0m; } return Convert.ToDecimal(value); } // 辅助方法:计算指定日期区间业绩指标(溯源 / Cell / Slim) private async Task<(decimal traceabilityAmount, decimal cellAmount, decimal slimAmount, decimal totalKemeiIncome)> CalculateRangePerformance( List storeIds, DateTime startDate, DateTime endDateTime) { if (!storeIds.Any()) return (0, 0, 0, 0); var endDate = endDateTime.Date; var storeIdsStr = string.Join("','", storeIds); // 溯源金额 var billingTraceabilitySql = $@" SELECT COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as Amount FROM lq_kd_jksyj WHERE F_IsEffective = 1 AND F_StoreId IN ('{storeIdsStr}') AND F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlTraceabilityIn}) AND yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'"; var billingTraceabilityResult = await _db.Ado.SqlQueryAsync(billingTraceabilitySql); var billingTraceabilityAmount = billingTraceabilityResult?.FirstOrDefault() != null ? Convert.ToDecimal(billingTraceabilityResult.FirstOrDefault().Amount ?? 0) : 0m; var refundTraceabilityAmount = await _db.Queryable() .Where(x => x.IsEffective == 1) .Where(x => storeIds.Contains(x.StoreId)) .Where(x => x.BeautyType == "溯源系统" || x.BeautyType == "溯源") .Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date) .SumAsync(x => (decimal?)(x.Jksyj ?? 0m)) ?? 0m; var traceabilityAmount = billingTraceabilityAmount - refundTraceabilityAmount; // Cell金额 var billingCellSql = $@" SELECT COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as Amount FROM lq_kd_jksyj WHERE F_IsEffective = 1 AND F_StoreId IN ('{storeIdsStr}') AND F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlCellIn}) AND yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'"; var billingCellResult = await _db.Ado.SqlQueryAsync(billingCellSql); var billingCellAmount = billingCellResult?.FirstOrDefault() != null ? Convert.ToDecimal(billingCellResult.FirstOrDefault().Amount ?? 0) : 0m; var refundCellAmount = await _db.Queryable() .Where(x => x.IsEffective == 1) .Where(x => storeIds.Contains(x.StoreId)) .Where(x => x.BeautyType == "cell" || x.BeautyType == "Cell") .Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date) .SumAsync(x => (decimal?)(x.Jksyj ?? 0m)) ?? 0m; var cellAmount = billingCellAmount - refundCellAmount; // Slim金额 var billingSlimSql = $@" SELECT COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as Amount FROM lq_kd_jksyj WHERE F_IsEffective = 1 AND F_StoreId IN ('{storeIdsStr}') AND F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlSlimIn}) AND yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'"; var billingSlimResult = await _db.Ado.SqlQueryAsync(billingSlimSql); var billingSlimAmount = billingSlimResult?.FirstOrDefault() != null ? Convert.ToDecimal(billingSlimResult.FirstOrDefault().Amount ?? 0) : 0m; var refundSlimAmount = await _db.Queryable() .Where(x => x.IsEffective == 1) .Where(x => storeIds.Contains(x.StoreId)) .Where(x => x.BeautyType == "slim" || x.BeautyType == "Slim") .Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date) .SumAsync(x => (decimal?)(x.Jksyj ?? 0m)) ?? 0m; var slimAmount = billingSlimAmount - refundSlimAmount; var totalKemeiIncome = traceabilityAmount + cellAmount + slimAmount; return (traceabilityAmount, cellAmount, slimAmount, totalKemeiIncome); } private Task<(decimal traceabilityAmount, decimal cellAmount, decimal slimAmount, decimal totalKemeiIncome)> CalculateMonthlyPerformance(List storeIds, string month) { var (startDate, endDateTime) = GetMonthRange(month); return CalculateRangePerformance(storeIds, startDate, endDateTime); } /// /// 获取科技部驾驶舱统计数据 /// /// /// 获取指定科技部在指定时间范围内的核心指标:溯源金额、Cell金额、科美总收入、管理的门店数、活跃门店数。 /// /// 示例请求(日期区间,优先): /// ```json /// { /// "techDepartmentId": "1649328471923847169", /// "startDate": "2025-09-01", /// "endDate": "2025-09-18" /// } /// ``` /// /// 示例请求(兼容整月): /// ```json /// { /// "techDepartmentId": "1649328471923847169", /// "statisticsMonth": "202512" /// } /// ``` /// /// 或者: /// ```json /// { /// "storeIds": ["1649328471923847169", "1649328471923847170"], /// "statisticsMonth": "202512" /// } /// ``` /// /// 参数说明: /// - techDepartmentId: 科技部ID(BASE_ORGANIZE表的组织ID),与 storeIds 二选一 /// - storeIds: 门店ID列表,与 techDepartmentId 二选一 /// - startDate/endDate: 统计日期区间,成对传入时优先于 statisticsMonth /// - statisticsMonth: 统计月份,格式 YYYYMM(与日期区间二选一,兼容旧调用) /// /// 返回数据说明: /// - TraceabilityAmount: 溯源金额(开单溯源金额 - 退卡溯源金额) /// - CellAmount: Cell金额(开单Cell金额 - 退卡Cell金额) /// - TotalKemeiIncome: 科美总收入(溯源金额 + Cell金额) /// - ManagedStoreCount: 管理的门店数 /// - ActiveStoreCount: 活跃门店数(有科美开单或消耗的门店数量) /// /// 查询参数 /// 科技部驾驶舱统计数据 /// 成功返回统计数据 /// 参数错误 /// 服务器错误 [HttpPost("GetStatistics")] public async Task GetStatistics([FromBody] TechDepartmentDashboardStatisticsInput input) { try { var (storeIds, range) = await ResolveRangeAsync(input); _logger.LogInformation( "开始查询科技部驾驶舱统计数据,科技部ID:{TechDepartmentId},门店ID列表:{StoreIds},区间:{StartDate}~{EndDate}", input.TechDepartmentId, input.StoreIds != null ? string.Join(",", input.StoreIds) : "", range.StartInclusive, range.EndInclusive); if (!storeIds.Any()) { _logger.LogWarning("科技部ID:{TechDepartmentId} 在指定时间范围内没有管理的门店", input.TechDepartmentId); return new TechDepartmentDashboardStatisticsOutput(); } var startDate = range.StartInclusive; var endDateTime = range.EndInclusive; var endDate = endDateTime.Date; // 1. 统计开单溯源金额(使用SQL查询,因为jksyj字段是字符串类型) var billingTraceabilitySql = $@" SELECT COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as Amount FROM lq_kd_jksyj WHERE F_IsEffective = 1 AND F_StoreId IN ('{string.Join("','", storeIds)}') AND (F_BeautyType = '溯源系统' OR F_BeautyType = '溯源') AND yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'"; var billingTraceabilityResult = await _db.Ado.SqlQueryAsync(billingTraceabilitySql); var billingTraceabilityAmount = billingTraceabilityResult?.FirstOrDefault() != null ? Convert.ToDecimal(billingTraceabilityResult.FirstOrDefault().Amount ?? 0) : 0m; // 2. 统计退卡溯源金额 var refundTraceabilityAmount = await _db.Queryable() .Where(x => x.IsEffective == 1) .Where(x => storeIds.Contains(x.StoreId)) .Where(x => (x.BeautyType == "溯源系统" || x.BeautyType == "溯源")) .Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date) .SumAsync(x => (decimal?)(x.Jksyj ?? 0m)) ?? 0m; // 3. 计算净溯源金额 var traceabilityAmount = billingTraceabilityAmount - refundTraceabilityAmount; // 4. 统计开单Cell金额(使用SQL查询,因为jksyj字段是字符串类型) var billingCellSql = $@" SELECT COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as Amount FROM lq_kd_jksyj WHERE F_IsEffective = 1 AND F_StoreId IN ('{string.Join("','", storeIds)}') AND (F_BeautyType = 'cell' OR F_BeautyType = 'Cell') AND yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'"; var billingCellResult = await _db.Ado.SqlQueryAsync(billingCellSql); var billingCellAmount = billingCellResult?.FirstOrDefault() != null ? Convert.ToDecimal(billingCellResult.FirstOrDefault().Amount ?? 0) : 0m; // 5. 统计退卡Cell金额 var refundCellAmount = await _db.Queryable() .Where(x => x.IsEffective == 1) .Where(x => storeIds.Contains(x.StoreId)) .Where(x => (x.BeautyType == "cell" || x.BeautyType == "Cell")) .Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date) .SumAsync(x => (decimal?)(x.Jksyj ?? 0m)) ?? 0m; // 6. 计算净Cell金额 var cellAmount = billingCellAmount - refundCellAmount; // 7. 统计开单Slim金额 var billingSlimSql = $@" SELECT COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as Amount FROM lq_kd_jksyj WHERE F_IsEffective = 1 AND F_StoreId IN ('{string.Join("','", storeIds)}') AND F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlSlimIn}) AND yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'"; var billingSlimResult = await _db.Ado.SqlQueryAsync(billingSlimSql); var billingSlimAmount = billingSlimResult?.FirstOrDefault() != null ? Convert.ToDecimal(billingSlimResult.FirstOrDefault().Amount ?? 0) : 0m; // 8. 统计退卡Slim金额 var refundSlimAmount = await _db.Queryable() .Where(x => x.IsEffective == 1) .Where(x => storeIds.Contains(x.StoreId)) .Where(x => x.BeautyType == "slim" || x.BeautyType == "Slim") .Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date) .SumAsync(x => (decimal?)(x.Jksyj ?? 0m)) ?? 0m; // 9. 计算净Slim金额 var slimAmount = billingSlimAmount - refundSlimAmount; // 10. 计算科美总收入 var totalKemeiIncome = traceabilityAmount + cellAmount + slimAmount; // 11. 管理的门店数 var managedStoreCount = storeIds.Count; // 12. 统计活跃门店数(有科美开单或消耗的门店数量) // 先获取有科美开单的门店 var storesWithBilling = await _db.Queryable() .Where(x => x.IsEffective == 1) .Where(x => storeIds.Contains(x.StoreId)) .Where(x => x.BeautyType == "溯源系统" || x.BeautyType == "溯源" || x.BeautyType == "cell" || x.BeautyType == "Cell" || x.BeautyType == "slim" || x.BeautyType == "Slim") .Where(x => x.Yjsj.HasValue && x.Yjsj.Value >= startDate && x.Yjsj.Value <= endDateTime) .Select(x => x.StoreId) .Distinct() .ToListAsync(); // 获取有科美消耗的门店(通过关联lq_xh_pxmx表,筛选科美类型) var storesWithConsumeSql = $@" SELECT DISTINCT xh.Md as StoreId FROM lq_xh_hyhk xh INNER JOIN lq_xh_pxmx px ON px.F_ConsumeInfoId = xh.F_Id INNER JOIN lq_xmzl xm ON px.px = xm.F_Id WHERE xh.F_IsEffective = 1 AND px.F_IsEffective = 1 AND xh.Md IN ('{string.Join("','", storeIds)}') AND xm.qt2 = '科美' AND xh.Hksj >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND xh.Hksj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'"; var storesWithConsumeData = await _db.Ado.SqlQueryAsync(storesWithConsumeSql); var storesWithConsume = new List(); if (storesWithConsumeData != null) { foreach (var row in storesWithConsumeData) { var storeId = row?.StoreId?.ToString(); if (!string.IsNullOrEmpty(storeId) && !storesWithConsume.Contains(storeId)) { storesWithConsume.Add(storeId); } } } // 合并并去重 var allActiveStoreIds = storesWithBilling.Union(storesWithConsume).Distinct().ToList(); var activeStoreCount = allActiveStoreIds.Count; // 10. 统计消耗金额(科技部老师消耗业绩) var consumeAmount = 0m; if (storeIds.Any()) { var storeIdsStr = string.Join("','", storeIds); var consumeSql = $@" SELECT COALESCE(SUM(kjbsyj.kjblsyj), 0) as ConsumeAmount FROM lq_xh_kjbsyj kjbsyj WHERE kjbsyj.F_IsEffective = 1 AND kjbsyj.F_ItemCategory = '科美' AND kjbsyj.F_StoreId IN ('{storeIdsStr}') AND kjbsyj.yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND kjbsyj.yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'"; var consumeResult = await _db.Ado.SqlQueryAsync(consumeSql); consumeAmount = consumeResult?.FirstOrDefault() != null ? Convert.ToDecimal(consumeResult.FirstOrDefault().ConsumeAmount ?? 0) : 0m; } // 11. 统计手工费(科技部老师手工费) var handworkFee = 0m; if (storeIds.Any()) { var storeIdsStr = string.Join("','", storeIds); var handworkFeeSql = $@" SELECT COALESCE(SUM(kjbsyj.F_LaborCost), 0) as HandworkFee FROM lq_xh_kjbsyj kjbsyj WHERE kjbsyj.F_IsEffective = 1 AND kjbsyj.F_ItemCategory = '科美' AND kjbsyj.F_StoreId IN ('{storeIdsStr}') AND kjbsyj.yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND kjbsyj.yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'"; var handworkFeeResult = await _db.Ado.SqlQueryAsync(handworkFeeSql); handworkFee = handworkFeeResult?.FirstOrDefault() != null ? Convert.ToDecimal(handworkFeeResult.FirstOrDefault().HandworkFee ?? 0) : 0m; } // 12. 统计开单金额(科美品项实付 F_ActualPrice,非整单 sfyj) var (_, billingAmount) = await QueryKemeiBillingItemStatsAsync(storeIds, startDate, endDateTime); // 13. 统计退款金额(仅科美品项明细退款金额) var (_, refundAmount) = await QueryKemeiRefundStatsAsync(storeIds, startDate, endDateTime); // 14. 统计储扣金额(科美开单的储扣金额) var deductAmount = 0m; if (storeIds.Any()) { var storeIdsStr = string.Join("','", storeIds); var deductAmountSql = $@" SELECT COALESCE(SUM(deduct.F_Amount), 0) as DeductAmount FROM lq_kd_deductinfo deduct INNER JOIN lq_kd_kdjlb billing ON deduct.F_BillingId = billing.F_Id LEFT JOIN lq_xmzl item ON deduct.F_ItemId = item.F_Id AND item.F_IsEffective = 1 WHERE deduct.F_IsEffective = 1 AND billing.F_IsEffective = 1 AND COALESCE(item.qt2, '其他') = '科美' AND billing.djmd IN ('{storeIdsStr}') AND COALESCE(deduct.F_BillingTime, billing.kdrq) >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND COALESCE(deduct.F_BillingTime, billing.kdrq) <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'"; var deductAmountResult = await _db.Ado.SqlQueryAsync(deductAmountSql); deductAmount = deductAmountResult?.FirstOrDefault() != null ? Convert.ToDecimal(deductAmountResult.FirstOrDefault().DeductAmount ?? 0) : 0m; } var result = new TechDepartmentDashboardStatisticsOutput { TraceabilityAmount = traceabilityAmount, CellAmount = cellAmount, SlimAmount = slimAmount, TotalKemeiIncome = totalKemeiIncome, ManagedStoreCount = managedStoreCount, ActiveStoreCount = activeStoreCount, BillingTraceabilityAmount = billingTraceabilityAmount, RefundTraceabilityAmount = refundTraceabilityAmount, BillingCellAmount = billingCellAmount, RefundCellAmount = refundCellAmount, BillingSlimAmount = billingSlimAmount, RefundSlimAmount = refundSlimAmount, BillingAmount = billingAmount, DeductAmount = deductAmount, RefundAmount = refundAmount, ConsumeAmount = consumeAmount, HandworkFee = handworkFee }; _logger.LogInformation( $"科技部驾驶舱统计数据查询完成,区间:{range.StartInclusive:yyyy-MM-dd}~{range.EndInclusive:yyyy-MM-dd},管理的门店数:{managedStoreCount},活跃门店数:{activeStoreCount},科美总收入:{totalKemeiIncome:F2}"); return result; } catch (Exception ex) { _logger.LogError(ex, "查询科技部驾驶舱统计数据失败,科技部ID:{TechDepartmentId},统计月份:{StatisticsMonth}", input?.TechDepartmentId, input?.StatisticsMonth); throw NCCException.Oh($"查询科技部驾驶舱统计数据失败:{ex.Message}"); } } /// /// 获取科技部驾驶舱股份统计数据 /// /// /// 获取指定科技部在指定月份的股份相关指标:收入、成本(报销、人工各项明细)、利润 /// /// 示例请求: /// ```json /// { /// "techDepartmentId": "1649328471923847169", /// "statisticsMonth": "202512" /// } /// ``` /// /// 参数说明: /// - techDepartmentId: 科技部ID,与 storeIds 二选一 /// - storeIds: 门店ID列表,与 techDepartmentId 二选一 /// - startDate/endDate: 统计日期区间,成对传入时优先于 statisticsMonth /// - statisticsMonth: 统计月份 YYYYMM(与日期区间二选一) /// /// 返回数据说明: /// - Income: 收入(科美开单30%,需减去退款) /// - CostReimbursement: 成本-报销 /// - CostTeacherBase: 成本-人工-老师底薪 /// - CostTeacherManual: 成本-人工-老师手工费 /// - CostTeacherBillingComm: 成本-人工-老师开单提成 /// - CostTeacherConsumeComm: 成本-人工-老师消耗提成 /// - CostGMBase: 成本-人工-总经理底薪 /// - CostGMComm: 成本-人工-总经理提成 /// - Profit: 利润(收入 - 所有成本) /// /// 查询参数 /// 科技部驾驶舱股份统计数据 /// 成功返回统计数据 /// 参数错误 /// 服务器错误 [HttpPost("GetShareStatistics")] public async Task GetShareStatistics([FromBody] TechDepartmentDashboardStatisticsInput input) { try { var (storeIds, range) = await ResolveRangeAsync(input); _logger.LogInformation( "开始查询科技部驾驶舱股份统计数据,科技部ID:{TechDepartmentId},区间:{StartDate}~{EndDate}", input.TechDepartmentId, range.StartInclusive, range.EndInclusive); if (!storeIds.Any()) { return new TechDepartmentDashboardShareStatisticsOutput(); } var startDate = range.StartInclusive; var endDateTime = range.EndInclusive; var positionNames = await ResolveTechDepartmentPositionNamesAsync(input.TechDepartmentId); // 1. 计算收入:科美开单30%(需减去退款) decimal kemeiIncome = 0; if (storeIds.Any()) { var storeIdsStr = string.Join("','", storeIds); var kemeiIncomeSql = $@" SELECT COALESCE(SUM(px.F_TotalPrice), 0) as Total FROM lq_kd_pxmx px INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id WHERE kd.djmd IN ('{storeIdsStr}') AND px.yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND px.yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}' AND px.F_IsEffective = 1 AND px.F_ItemCategory = '科美'"; var kemeiIncomeResult = await _db.Ado.SqlQueryAsync(kemeiIncomeSql); if (kemeiIncomeResult != null && kemeiIncomeResult.Any()) { var first = kemeiIncomeResult.FirstOrDefault(); if (first != null) { kemeiIncome = Convert.ToDecimal(first.Total ?? 0); } } } // 减去对应的科美品项明细退款金额 var (_, kemeiRefund) = await QueryKemeiRefundStatsAsync(storeIds, startDate, endDateTime); // 收入 = (科美开单 - 科美退卡) * 30% var income = (kemeiIncome - kemeiRefund) * 0.3m; // 2. 成本-报销:筛选一级分类为"科技部费用"的申请 var reimbursementAmount = 0m; if (storeIds.Any()) { reimbursementAmount = await _db.Queryable( (app, purchase, category) => app.PurchaseRecordsId == purchase.Id && purchase.ReimbursementCategoryId == category.Id) .Where((app, purchase, category) => category.Level1Name == "科技部费用" && app.ApprovalStatus == "已通过" && storeIds.Contains(app.ApplicationStoreId) && app.ApplicationTime >= startDate && app.ApplicationTime <= endDateTime) .SumAsync((app, purchase, category) => purchase.Amount); } // 3. 成本-人工-科技部老师底薪(跨月聚合) var teacherBaseSalary = await _db.Queryable() .Where(x => range.StatisticsMonths.Contains(x.StatisticsMonth) && storeIds.Contains(x.StoreId)) .SumAsync(x => x.BaseSalary); // 4. 成本-人工-科技部手工费 var teacherManualFee = await _db.Queryable() .Where(x => x.ItemCategory == "科美" && storeIds.Contains(x.StoreId) && x.Yjsj >= startDate && x.Yjsj <= endDateTime && x.IsEffective == 1) .SumAsync(x => x.LaborCost ?? 0); // 5. 成本-人工-科技部开单提成(跨月聚合) var teacherBillingComm = await _db.Queryable() .Where(x => range.StatisticsMonths.Contains(x.StatisticsMonth) && storeIds.Contains(x.StoreId)) .SumAsync(x => x.PerformanceCommissionAmount); // 6. 成本-人工-科技部消耗提成(跨月聚合) var teacherConsumeComm = await _db.Queryable() .Where(x => range.StatisticsMonths.Contains(x.StatisticsMonth) && storeIds.Contains(x.StoreId)) .SumAsync(x => x.ConsumeCommissionAmount); // 7. 成本-人工-科技部总经理底薪和提成(Position 与子部门全称匹配) var gmBaseSalary = 0m; var gmComm = 0m; if (positionNames != null && positionNames.Any()) { gmBaseSalary = await _db.Queryable() .Where(x => range.StatisticsMonths.Contains(x.StatisticsMonth) && positionNames.Contains(x.Position)) .SumAsync(x => x.BaseSalary); gmComm = await _db.Queryable() .Where(x => range.StatisticsMonths.Contains(x.StatisticsMonth) && positionNames.Contains(x.Position)) .SumAsync(x => x.TotalCommission); } // 8. 计算利润 var totalCost = reimbursementAmount + teacherBaseSalary + teacherManualFee + teacherBillingComm + teacherConsumeComm + gmBaseSalary + gmComm; var profit = income - totalCost; var result = new TechDepartmentDashboardShareStatisticsOutput { Income = income, CostReimbursement = reimbursementAmount, CostTeacherBase = teacherBaseSalary, CostTeacherManual = teacherManualFee, CostTeacherBillingComm = teacherBillingComm, CostTeacherConsumeComm = teacherConsumeComm, CostGMBase = gmBaseSalary, CostGMComm = gmComm, Profit = profit }; _logger.LogInformation( "科技部驾驶舱股份统计数据查询完成,区间:{StartDate}~{EndDate},收入:{Income:F2},成本:{TotalCost:F2},利润:{Profit:F2}", range.StartInclusive, range.EndInclusive, income, totalCost, profit); return result; } catch (Exception ex) { _logger.LogError(ex, "查询科技部驾驶舱股份统计数据失败,科技部ID:{TechDepartmentId},统计月份:{StatisticsMonth}", input?.TechDepartmentId, input?.StatisticsMonth); throw NCCException.Oh($"查询科技部驾驶舱股份统计数据失败:{ex.Message}"); } } /// /// 获取科技部驾驶舱业绩趋势数据 /// /// /// 获取指定科技部在指定时间范围内的业绩趋势:溯源金额趋势、Cell金额趋势、科美总收入趋势。 /// monthCount 为 3/6/12 时,以结束日期所在月(或 statisticsMonth)为基准向前取 N 个完整自然月,忽略 startDate/endDate 对月份列表的裁剪。 /// 未传有效 monthCount 且传入 startDate/endDate 时,按区间覆盖的自然月出点(每月数据裁剪到区间内)。 /// /// 示例请求(日期区间): /// ```json /// { /// "techDepartmentId": "1649328471923847169", /// "startDate": "2025-07-01", /// "endDate": "2025-09-18" /// } /// ``` /// /// 查询参数 /// 业绩趋势数据 /// 成功返回趋势数据 /// 参数错误 /// 服务器错误 [HttpPost("GetPerformanceTrend")] public async Task GetPerformanceTrend([FromBody] TechDepartmentDashboardPerformanceTrendInput input) { try { ValidateStatisticsInput(input); var result = new TechDepartmentDashboardPerformanceTrendOutput(); if (IsValidTrendMonthCount(input.MonthCount)) { var months = BuildTrendMonthsFromBase(ResolveTrendBaseMonth(input), input.MonthCount); result.TrendData = await BuildPerformanceTrendByFullMonthsAsync( input.TechDepartmentId, input.StoreIds, months); return result; } if (input.StartDate.HasValue && input.EndDate.HasValue) { var range = NormalizeDateRange(input); var monthStoreMap = await BuildMonthStoreIdsMapAsync( input.TechDepartmentId, input.StoreIds, range.StatisticsMonths); decimal cumulativeTraceability = 0m; decimal cumulativeCell = 0m; decimal cumulativeSlim = 0m; decimal cumulativeTotal = 0m; foreach (var trendMonthStr in range.StatisticsMonths) { var storeIds = monthStoreMap.TryGetValue(trendMonthStr, out var ids) ? ids : new List(); if (!storeIds.Any()) { result.TrendData.Add(new PerformanceTrendPoint { Month = trendMonthStr }); continue; } var (pointStart, pointEnd) = ClipMonthToRange(trendMonthStr, range); var (traceabilityAmount, cellAmount, slimAmount, totalKemeiIncome) = await CalculateRangePerformance(storeIds, pointStart, pointEnd); cumulativeTraceability += traceabilityAmount; cumulativeCell += cellAmount; cumulativeSlim += slimAmount; cumulativeTotal += totalKemeiIncome; result.TrendData.Add(new PerformanceTrendPoint { Month = trendMonthStr, TraceabilityAmount = traceabilityAmount, CellAmount = cellAmount, SlimAmount = slimAmount, TotalKemeiIncome = totalKemeiIncome, CumulativeTraceabilityAmount = cumulativeTraceability, CumulativeCellAmount = cumulativeCell, CumulativeSlimAmount = cumulativeSlim, CumulativeTotalKemeiIncome = cumulativeTotal }); } return result; } var monthsFallback = BuildTrendMonthsFromBase(input.StatisticsMonth, 12); result.TrendData = await BuildPerformanceTrendByFullMonthsAsync( input.TechDepartmentId, input.StoreIds, monthsFallback); return result; } catch (Exception ex) { _logger.LogError(ex, "查询科技部驾驶舱业绩趋势数据失败"); throw NCCException.Oh($"查询失败:{ex.Message}"); } } private async Task CalculateShareTrendPointAsync( List storeIds, DateTime startDate, DateTime endDateTime, string salaryMonth, IReadOnlyList positionNames) { if (!storeIds.Any()) { return new ShareTrendPoint { Month = salaryMonth }; } decimal kemeiIncome = 0; var storeIdsStr = string.Join("','", storeIds); var kemeiIncomeSql = $@" SELECT COALESCE(SUM(px.F_TotalPrice), 0) as Total FROM lq_kd_pxmx px INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id WHERE kd.djmd IN ('{storeIdsStr}') AND px.yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND px.yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}' AND px.F_IsEffective = 1 AND px.F_ItemCategory = '科美'"; var kemeiIncomeResult = await _db.Ado.SqlQueryAsync(kemeiIncomeSql); if (kemeiIncomeResult?.Any() == true) kemeiIncome = Convert.ToDecimal(kemeiIncomeResult.FirstOrDefault().Total ?? 0); var (_, kemeiRefund) = await QueryKemeiRefundStatsAsync(storeIds, startDate, endDateTime); var income = (kemeiIncome - kemeiRefund) * 0.3m; var costs = await CalculateShareTrendCostsAsync(storeIds, startDate, endDateTime, salaryMonth, positionNames); var totalCost = costs.reimbursement + costs.teacherBase + costs.teacherManual + costs.teacherBillingComm + costs.teacherConsumeComm + costs.gmBase + costs.gmComm; var profit = income - totalCost; return new ShareTrendPoint { Month = salaryMonth, Income = income, CostReimbursement = costs.reimbursement, CostTeacherBase = costs.teacherBase, CostTeacherManual = costs.teacherManual, CostTeacherBillingComm = costs.teacherBillingComm, CostTeacherConsumeComm = costs.teacherConsumeComm, CostGMBase = costs.gmBase, CostGMComm = costs.gmComm, Profit = profit, ProfitRate = income > 0 ? (profit / income) * 100 : 0 }; } /// /// 获取科技部驾驶舱股份趋势数据 /// /// /// 获取指定科技部在指定时间范围内的股份趋势:收入趋势、成本趋势、利润趋势。 /// monthCount 为 3/6/12 时,以结束日期所在月(或 statisticsMonth)为基准向前取 N 个完整自然月,忽略 startDate/endDate 对月份列表的裁剪。 /// 未传有效 monthCount 且传入 startDate/endDate 时,按区间覆盖的自然月出点(每月数据裁剪到区间内)。 /// /// 查询参数 /// 股份趋势数据 /// 成功返回趋势数据 /// 参数错误 /// 服务器错误 [HttpPost("GetShareTrend")] public async Task GetShareTrend([FromBody] TechDepartmentDashboardShareTrendInput input) { try { ValidateStatisticsInput(input); var result = new TechDepartmentDashboardShareTrendOutput(); var positionNames = await ResolveTechDepartmentPositionNamesAsync(input.TechDepartmentId); if (IsValidTrendMonthCount(input.MonthCount)) { var months = BuildTrendMonthsFromBase(ResolveTrendBaseMonth(input), input.MonthCount); result.TrendData = await BuildShareTrendByFullMonthsAsync( input.TechDepartmentId, input.StoreIds, months, positionNames); return result; } if (input.StartDate.HasValue && input.EndDate.HasValue) { var range = NormalizeDateRange(input); var monthStoreMap = await BuildMonthStoreIdsMapAsync( input.TechDepartmentId, input.StoreIds, range.StatisticsMonths); decimal cumulativeIncome = 0m; decimal cumulativeCost = 0m; decimal cumulativeProfit = 0m; foreach (var trendMonthStr in range.StatisticsMonths) { var storeIds = monthStoreMap.TryGetValue(trendMonthStr, out var ids) ? ids : new List(); var (pointStart, pointEnd) = ClipMonthToRange(trendMonthStr, range); var point = await CalculateShareTrendPointAsync(storeIds, pointStart, pointEnd, trendMonthStr, positionNames); cumulativeIncome += point.Income; cumulativeCost += point.CostReimbursement + point.CostTeacherBase + point.CostTeacherManual + point.CostTeacherBillingComm + point.CostTeacherConsumeComm + point.CostGMBase + point.CostGMComm; cumulativeProfit += point.Profit; point.CumulativeIncome = cumulativeIncome; point.CumulativeCost = cumulativeCost; point.CumulativeProfit = cumulativeProfit; result.TrendData.Add(point); } return result; } var monthsFallback = BuildTrendMonthsFromBase(input.StatisticsMonth, 12); result.TrendData = await BuildShareTrendByFullMonthsAsync( input.TechDepartmentId, input.StoreIds, monthsFallback, positionNames); return result; } catch (Exception ex) { _logger.LogError(ex, "查询科技部驾驶舱股份趋势数据失败"); throw NCCException.Oh($"查询失败:{ex.Message}"); } } /// /// 获取科技部驾驶舱门店业绩排行 /// /// /// 获取指定科技部在指定月份的门店业绩排行:门店溯源金额排行、门店Cell金额排行、门店科美总收入排行 /// /// 查询参数 /// 门店业绩排行数据 [HttpPost("GetStoreRanking")] public async Task GetStoreRanking([FromBody] TechDepartmentDashboardStatisticsInput input) { try { var (storeIds, range) = await ResolveRangeAsync(input); var startDate = range.StartInclusive; var endDateTime = range.EndInclusive; var endDate = endDateTime.Date; if (!storeIds.Any()) { return new TechDepartmentDashboardStoreRankingOutput(); } var storeIdsStr = string.Join("','", storeIds); // 统计各门店的业绩 var storePerformanceSql = $@" SELECT * FROM ( SELECT md.F_Id as StoreId, md.dm as StoreName, COALESCE(SUM(CASE WHEN jksyj.F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlTraceabilityIn}) THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END), 0) - COALESCE(refund_trace.RefundAmount, 0) as TraceabilityAmount, COALESCE(SUM(CASE WHEN jksyj.F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlCellIn}) THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END), 0) - COALESCE(refund_cell.RefundAmount, 0) as CellAmount, COALESCE(SUM(CASE WHEN jksyj.F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlSlimIn}) THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END), 0) - COALESCE(refund_slim.RefundAmount, 0) as SlimAmount FROM lq_mdxx md LEFT JOIN lq_kd_jksyj jksyj ON md.F_Id = jksyj.F_StoreId AND jksyj.F_IsEffective = 1 AND jksyj.yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND jksyj.yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}' LEFT JOIN ( SELECT F_StoreId, COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as RefundAmount FROM lq_hytk_jksyj WHERE F_IsEffective = 1 AND F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlTraceabilityIn}) AND tksj >= '{startDate:yyyy-MM-dd} 00:00:00' AND tksj <= '{endDate:yyyy-MM-dd} 23:59:59' GROUP BY F_StoreId ) refund_trace ON md.F_Id = refund_trace.F_StoreId LEFT JOIN ( SELECT F_StoreId, COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as RefundAmount FROM lq_hytk_jksyj WHERE F_IsEffective = 1 AND F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlCellIn}) AND tksj >= '{startDate:yyyy-MM-dd} 00:00:00' AND tksj <= '{endDate:yyyy-MM-dd} 23:59:59' GROUP BY F_StoreId ) refund_cell ON md.F_Id = refund_cell.F_StoreId LEFT JOIN ( SELECT F_StoreId, COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as RefundAmount FROM lq_hytk_jksyj WHERE F_IsEffective = 1 AND F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlSlimIn}) AND tksj >= '{startDate:yyyy-MM-dd} 00:00:00' AND tksj <= '{endDate:yyyy-MM-dd} 23:59:59' GROUP BY F_StoreId ) refund_slim ON md.F_Id = refund_slim.F_StoreId WHERE md.F_Id IN ('{storeIdsStr}') GROUP BY md.F_Id, md.dm, refund_trace.RefundAmount, refund_cell.RefundAmount, refund_slim.RefundAmount ) t WHERE TraceabilityAmount > 0 OR CellAmount > 0 OR SlimAmount > 0 ORDER BY (TraceabilityAmount + CellAmount + SlimAmount) DESC"; var storePerformanceData = await _db.Ado.SqlQueryAsync(storePerformanceSql); var storePerformanceList = storePerformanceData ?? new List(); decimal totalKemeiIncome = storePerformanceList.Sum(x => x.TraceabilityAmount + x.CellAmount + x.SlimAmount); var result = new TechDepartmentDashboardStoreRankingOutput(); int rankingIndex = 1; foreach (var item in storePerformanceList) { var totalVal = item.TraceabilityAmount + item.CellAmount + item.SlimAmount; var rankingItem = new StoreRankingItem { Ranking = rankingIndex, StoreId = item.StoreId ?? "", StoreName = item.StoreName ?? "未知门店", TraceabilityAmount = item.TraceabilityAmount, CellAmount = item.CellAmount, SlimAmount = item.SlimAmount, TotalKemeiIncome = totalVal, Percentage = totalKemeiIncome > 0 ? (totalVal / totalKemeiIncome) * 100 : 0 }; result.RankingData.Add(rankingItem); rankingIndex++; } return result; } catch (Exception ex) { _logger.LogError(ex, "查询科技部驾驶舱门店业绩排行失败"); throw NCCException.Oh($"查询失败:{ex.Message}"); } } /// /// 获取科技部驾驶舱门店业绩分布 /// /// /// 获取指定科技部在指定月份的门店业绩分布数据(用于饼图展示) /// /// 查询参数 /// 门店业绩分布数据 [HttpPost("GetStoreDistribution")] public async Task GetStoreDistribution([FromBody] TechDepartmentDashboardStatisticsInput input) { try { var (storeIds, range) = await ResolveRangeAsync(input); var startDate = range.StartInclusive; var endDateTime = range.EndInclusive; var endDate = endDateTime.Date; if (!storeIds.Any()) { return new TechDepartmentDashboardStoreDistributionOutput(); } var storeIdsStr = string.Join("','", storeIds); // 统计各门店的业绩(与GetStoreRanking逻辑相同) var storePerformanceSql = $@" SELECT md.F_Id as StoreId, md.dm as StoreName, COALESCE(SUM(CASE WHEN jksyj.F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlTraceabilityIn}) THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END), 0) - COALESCE(refund_trace.RefundAmount, 0) as TraceabilityAmount, COALESCE(SUM(CASE WHEN jksyj.F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlCellIn}) THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END), 0) - COALESCE(refund_cell.RefundAmount, 0) as CellAmount, COALESCE(SUM(CASE WHEN jksyj.F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlSlimIn}) THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END), 0) - COALESCE(refund_slim.RefundAmount, 0) as SlimAmount FROM lq_mdxx md LEFT JOIN lq_kd_jksyj jksyj ON md.F_Id = jksyj.F_StoreId AND jksyj.F_IsEffective = 1 AND jksyj.yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND jksyj.yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}' LEFT JOIN ( SELECT F_StoreId, COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as RefundAmount FROM lq_hytk_jksyj WHERE F_IsEffective = 1 AND F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlTraceabilityIn}) AND tksj >= '{startDate:yyyy-MM-dd}' AND tksj <= '{endDate:yyyy-MM-dd}' GROUP BY F_StoreId ) refund_trace ON md.F_Id = refund_trace.F_StoreId LEFT JOIN ( SELECT F_StoreId, COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as RefundAmount FROM lq_hytk_jksyj WHERE F_IsEffective = 1 AND F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlCellIn}) AND tksj >= '{startDate:yyyy-MM-dd}' AND tksj <= '{endDate:yyyy-MM-dd}' GROUP BY F_StoreId ) refund_cell ON md.F_Id = refund_cell.F_StoreId LEFT JOIN ( SELECT F_StoreId, COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as RefundAmount FROM lq_hytk_jksyj WHERE F_IsEffective = 1 AND F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlSlimIn}) AND tksj >= '{startDate:yyyy-MM-dd}' AND tksj <= '{endDate:yyyy-MM-dd}' GROUP BY F_StoreId ) refund_slim ON md.F_Id = refund_slim.F_StoreId WHERE md.F_Id IN ('{storeIdsStr}') GROUP BY md.F_Id, md.dm, refund_trace.RefundAmount, refund_cell.RefundAmount, refund_slim.RefundAmount"; var storePerformanceData = await _db.Ado.SqlQueryAsync(storePerformanceSql); var storePerformanceList = storePerformanceData ?? new List(); decimal totalKemeiIncome = storePerformanceList.Sum(x => x.TraceabilityAmount + x.CellAmount + x.SlimAmount); var result = new TechDepartmentDashboardStoreDistributionOutput(); foreach (var item in storePerformanceList) { var totalVal = item.TraceabilityAmount + item.CellAmount + item.SlimAmount; result.DistributionData.Add(new StoreDistributionItem { StoreId = item.StoreId ?? "", StoreName = item.StoreName ?? "未知门店", TraceabilityAmount = item.TraceabilityAmount, CellAmount = item.CellAmount, SlimAmount = item.SlimAmount, TotalKemeiIncome = totalVal, Percentage = totalKemeiIncome > 0 ? (totalVal / totalKemeiIncome) * 100 : 0 }); } return result; } catch (Exception ex) { _logger.LogError(ex, "查询科技部驾驶舱门店业绩分布失败"); throw NCCException.Oh($"查询失败:{ex.Message}"); } } /// /// 获取科技部驾驶舱老师业绩排行 /// /// /// 获取指定科技部在指定月份的科技部老师业绩排行 /// /// 查询参数 /// 老师业绩排行数据 [HttpPost("GetTeacherRanking")] public async Task GetTeacherRanking([FromBody] TechDepartmentDashboardStatisticsInput input) { try { var (storeIds, range) = await ResolveRangeAsync(input); if (!storeIds.Any()) { return new TechDepartmentDashboardTeacherRankingOutput(); } var teacherStats = await _db.Queryable() .Where(x => range.StatisticsMonths.Contains(x.StatisticsMonth) && storeIds.Contains(x.StoreId)) .ToListAsync(); var aggregatedTeachers = teacherStats .GroupBy(x => x.EmployeeId) .Select(g => { var first = g.First(); return new TeacherRankingItem { EmployeeId = first.EmployeeId ?? "", EmployeeName = first.EmployeeName ?? "", EmployeeAccount = first.EmployeeAccount ?? "", StoreId = first.StoreId ?? "", StoreName = first.StoreName ?? "", OrderAchievement = g.Sum(x => x.OrderAchievement), ConsumeAchievement = g.Sum(x => x.ConsumeAchievement), RefundAchievement = g.Sum(x => x.RefundAchievement), TotalPerformance = g.Sum(x => x.TotalPerformance), ProjectCount = g.Sum(x => x.ProjectCount), BaseSalary = g.Sum(x => x.BaseSalary), PerformanceCommissionAmount = g.Sum(x => x.PerformanceCommissionAmount), ConsumeCommissionAmount = g.Sum(x => x.ConsumeCommissionAmount), FinalGrossSalary = g.Sum(x => x.FinalGrossSalary) }; }) .OrderByDescending(x => x.TotalPerformance) .ToList(); var result = new TechDepartmentDashboardTeacherRankingOutput(); var ranking = 1; foreach (var teacher in aggregatedTeachers) { teacher.Ranking = ranking++; result.RankingData.Add(teacher); } return result; } catch (Exception ex) { _logger.LogError(ex, "查询科技部驾驶舱老师业绩排行失败"); throw NCCException.Oh($"查询失败:{ex.Message}"); } } /// /// 获取科技部驾驶舱运营统计数据 /// /// /// 获取指定科技部在指定月份的运营分析:开单分析、消耗分析、退卡分析 /// /// 查询参数 /// 运营统计数据 [HttpPost("GetOperationStatistics")] public async Task GetOperationStatistics([FromBody] TechDepartmentDashboardStatisticsInput input) { try { var (storeIds, range) = await ResolveRangeAsync(input); var startDate = range.StartInclusive; var endDateTime = range.EndInclusive; if (!storeIds.Any()) { return new TechDepartmentDashboardOperationStatisticsOutput(); } var storeIdsStr = string.Join("','", storeIds); // 开单分析:科美品项实付金额与含科美品项的去重开单数(与 GetStatistics.BillingAmount 同口径) var (billingCount, billingAmount) = await QueryKemeiBillingItemStatsAsync(storeIds, startDate, endDateTime); // 消耗分析:消耗次数、消耗金额 var consumeStatsSql = $@" SELECT COUNT(DISTINCT xh.F_Id) as ConsumeCount, COALESCE(SUM(CASE WHEN xmzl.qt2 = '科美' THEN CAST(COALESCE(xhpx.F_TotalPrice, xhpx.pxjg * xhpx.F_ProjectNumber, 0) AS DECIMAL(18,2)) ELSE 0 END), 0) as ConsumeAmount FROM lq_xh_hyhk xh INNER JOIN lq_xh_pxmx xhpx ON xh.F_Id = xhpx.F_ConsumeInfoId INNER JOIN lq_xmzl xmzl ON xhpx.px = xmzl.F_Id WHERE xh.F_IsEffective = 1 AND xhpx.F_IsEffective = 1 AND xh.Md IN ('{storeIdsStr}') AND xmzl.qt2 = '科美' AND xh.Hksj >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND xh.Hksj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'"; var consumeStatsResult = await _db.Ado.SqlQueryAsync(consumeStatsSql); var consumeCount = consumeStatsResult?.FirstOrDefault() != null ? Convert.ToInt32(consumeStatsResult.FirstOrDefault().ConsumeCount ?? 0) : 0; var consumeAmount = consumeStatsResult?.FirstOrDefault() != null ? Convert.ToDecimal(consumeStatsResult.FirstOrDefault().ConsumeAmount ?? 0) : 0m; // 退卡分析:退卡次数、退卡金额(仅科美品项明细) var (refundCount, refundAmount) = await QueryKemeiRefundStatsAsync(storeIds, startDate, endDateTime); // 计算消耗率和退卡率 var consumeRate = billingAmount > 0 ? (consumeAmount / billingAmount) * 100 : 0; var refundRate = billingAmount > 0 ? (refundAmount / billingAmount) * 100 : 0; var result = new TechDepartmentDashboardOperationStatisticsOutput { BillingAnalysis = new BillingAnalysis { BillingCount = billingCount, BillingAmount = billingAmount, AverageBillingAmount = billingCount > 0 ? billingAmount / billingCount : 0 }, ConsumeAnalysis = new ConsumeAnalysis { ConsumeCount = consumeCount, ConsumeAmount = consumeAmount, ConsumeRate = consumeRate }, RefundAnalysis = new RefundAnalysis { RefundCount = refundCount, RefundAmount = refundAmount, RefundRate = refundRate } }; return result; } catch (Exception ex) { _logger.LogError(ex, "查询科技部驾驶舱运营统计数据失败"); throw NCCException.Oh($"查询失败:{ex.Message}"); } } private static ComparisonData BuildTechComparisonData( (decimal traceability, decimal cell, decimal slim, decimal total) current, (decimal traceability, decimal cell, decimal slim, decimal total) previous) { return new ComparisonData { TraceabilityAmountChangeRate = previous.traceability > 0 ? ((current.traceability - previous.traceability) / previous.traceability) * 100 : 0, CellAmountChangeRate = previous.cell > 0 ? ((current.cell - previous.cell) / previous.cell) * 100 : 0, SlimAmountChangeRate = previous.slim > 0 ? ((current.slim - previous.slim) / previous.slim) * 100 : 0, TotalKemeiIncomeChangeRate = previous.total > 0 ? ((current.total - previous.total) / previous.total) * 100 : 0 }; } /// /// 获取科技部驾驶舱对比分析数据 /// /// /// 获取环比、同比对比数据。传入 startDate/endDate 时为等长上一段环比 + 去年同期同比;仅 statisticsMonth 时为整月环比/同比。 /// /// 示例请求(日期区间): /// ```json /// { /// "techDepartmentId": "1649328471923847169", /// "startDate": "2025-09-01", /// "endDate": "2025-09-18" /// } /// ``` /// /// 查询参数 /// 对比分析数据 /// 成功返回对比分析 /// 参数错误 /// 服务器错误 [HttpPost("GetComparisonAnalysis")] public async Task GetComparisonAnalysis([FromBody] TechDepartmentDashboardStatisticsInput input) { try { ValidateStatisticsInput(input); if (input.StartDate.HasValue && input.EndDate.HasValue) { var range = NormalizeDateRange(input); var currentStoreIds = await GetStoreIdsForDateRangeAsync( input.TechDepartmentId, input.StoreIds, range.StatisticsMonths); var current = await CalculateRangePerformance( currentStoreIds, range.StartInclusive, range.EndInclusive); var dayCount = (range.EndInclusive.Date - range.StartInclusive.Date).Days + 1; var prevEndDate = range.StartInclusive.Date.AddDays(-1); var prevStartDate = prevEndDate.AddDays(-(dayCount - 1)); var prevRange = NormalizeDateRange(prevStartDate, prevEndDate); var prevStoreIds = await GetStoreIdsForDateRangeAsync( input.TechDepartmentId, input.StoreIds, prevRange.StatisticsMonths); var previous = await CalculateRangePerformance( prevStoreIds, prevRange.StartInclusive, prevRange.EndInclusive); var lastYearStart = range.StartInclusive.AddYears(-1); var lastYearEndDate = range.EndInclusive.Date.AddYears(-1); var lastYearRange = NormalizeDateRange(lastYearStart, lastYearEndDate); var lastYearRangeStoreIds = await GetStoreIdsForDateRangeAsync( input.TechDepartmentId, input.StoreIds, lastYearRange.StatisticsMonths); var lastYear = await CalculateRangePerformance( lastYearRangeStoreIds, lastYearRange.StartInclusive, lastYearRange.EndInclusive); return new TechDepartmentDashboardComparisonAnalysisOutput { TimeComparison = new TimeComparison { MonthOverMonth = BuildTechComparisonData(current, previous), YearOverYear = BuildTechComparisonData(current, lastYear) } }; } var currentMonth = DateTime.ParseExact(input.StatisticsMonth, "yyyyMM", CultureInfo.InvariantCulture); var lastMonthStr = currentMonth.AddMonths(-1).ToString("yyyyMM"); var lastYearMonthStr = currentMonth.AddYears(-1).ToString("yyyyMM"); var currentStoreIdsLegacy = await GetStoreIdsAsync(input.TechDepartmentId, input.StoreIds, input.StatisticsMonth); var currentLegacy = await CalculateMonthlyPerformance(currentStoreIdsLegacy, input.StatisticsMonth); var lastMonthStoreIds = await GetStoreIdsForMonthAsync(input.TechDepartmentId, input.StoreIds, lastMonthStr); var lastMonthLegacy = await CalculateMonthlyPerformance(lastMonthStoreIds, lastMonthStr); var lastYearStoreIds = await GetStoreIdsForMonthAsync(input.TechDepartmentId, input.StoreIds, lastYearMonthStr); var lastYearLegacy = await CalculateMonthlyPerformance(lastYearStoreIds, lastYearMonthStr); return new TechDepartmentDashboardComparisonAnalysisOutput { TimeComparison = new TimeComparison { MonthOverMonth = BuildTechComparisonData(currentLegacy, lastMonthLegacy), YearOverYear = BuildTechComparisonData(currentLegacy, lastYearLegacy) } }; } catch (Exception ex) { _logger.LogError(ex, "查询科技部驾驶舱对比分析数据失败"); throw NCCException.Oh($"查询失败:{ex.Message}"); } } /// /// 获取科技部驾驶舱门店明细列表 /// /// /// 获取指定科技部在指定月份的门店明细列表(支持分页、排序、筛选) /// /// 查询参数 /// 门店明细列表 [HttpPost("GetStoreDetailList")] public async Task GetStoreDetailList( [FromBody] TechDepartmentDashboardStoreDetailListInput input) { try { if (input == null) throw NCCException.Oh("请求参数不能为空"); var statisticsInput = ToStatisticsInput(input); var (storeIds, range) = await ResolveRangeAsync(statisticsInput); var startDate = range.StartInclusive; var endDateTime = range.EndInclusive; var endDate = endDateTime.Date; var periodLabel = range.HasDateRange ? $"{range.StartInclusive:yyyyMMdd}-{range.EndInclusive:yyyyMMdd}" : range.StatisticsMonthFallback; if (!storeIds.Any()) { return PageResult .SqlSugarPageResult(new SqlSugarPagedList { list = new List(), pagination = new SqlSugar.PagedModel { PageIndex = input.currentPage, PageSize = input.pageSize, Total = 0 } }); } var pageIndex = input.currentPage <= 0 ? 1 : input.currentPage; var pageSize = input.pageSize <= 0 ? 20 : input.pageSize; /* ============================== * ① 只查【门店分页】 * ============================== */ var storeQuery = _db.Queryable() .Where(md => storeIds.Contains(md.Id)); if (!string.IsNullOrWhiteSpace(input.StoreName)) { storeQuery = storeQuery.Where(md => md.Dm.Contains(input.StoreName)); } var total = await storeQuery.CountAsync(); var pageStores = await storeQuery .OrderBy(md => md.Dm) .ToPageListAsync(pageIndex, pageSize); var pageStoreIds = pageStores.Select(x => x.Id).ToList(); if (!pageStoreIds.Any()) return PageResult.SqlSugarPageResult(new SqlSugarPagedList { list = new List(), pagination = new SqlSugar.PagedModel { PageIndex = pageIndex, PageSize = pageSize, Total = 0 } }); /* ============================== * ② 只对【当前页门店】做统计 - 开单数据 * ============================== */ var storeIdsStr = string.Join(",", pageStoreIds.Select(x => $"'{x}'")); var billingSql = $@" SELECT jksyj.F_StoreId AS StoreId, SUM(CASE WHEN jksyj.F_BeautyType IN ('溯源系统','溯源') THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END) AS BillingTraceabilityAmount, SUM(CASE WHEN jksyj.F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlCellIn}) THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END) AS BillingCellAmount, SUM(CASE WHEN jksyj.F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlSlimIn}) THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END) AS BillingSlimAmount, COUNT(DISTINCT jksyj.Glkdbh) AS BillingCount FROM lq_kd_jksyj jksyj WHERE jksyj.F_IsEffective = 1 AND jksyj.F_StoreId IN ({storeIdsStr}) AND jksyj.yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND jksyj.yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}' GROUP BY jksyj.F_StoreId"; var billingStatList = await _db.Ado.SqlQueryAsync(billingSql); var billingStatDict = billingStatList.ToDictionary(x => x.StoreId); /* ============================== * ③ 查询退卡数据 * ============================== */ var refundSql = $@" SELECT jksyj.F_StoreId AS StoreId, SUM(CASE WHEN jksyj.F_BeautyType IN ('溯源系统','溯源') THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END) AS RefundTraceabilityAmount, SUM(CASE WHEN jksyj.F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlCellIn}) THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END) AS RefundCellAmount, SUM(CASE WHEN jksyj.F_BeautyType IN ({TechDepartmentKemeiBeautyTypeHelper.SqlSlimIn}) THEN CAST(jksyj.jksyj AS DECIMAL(18,2)) ELSE 0 END) AS RefundSlimAmount FROM lq_hytk_jksyj jksyj WHERE jksyj.F_IsEffective = 1 AND jksyj.F_StoreId IN ({storeIdsStr}) AND jksyj.tksj >= '{startDate:yyyy-MM-dd 00:00:00}' AND jksyj.tksj <= '{endDate:yyyy-MM-dd 23:59:59}' GROUP BY jksyj.F_StoreId"; var refundStatList = await _db.Ado.SqlQueryAsync(refundSql); var refundStatDict = refundStatList.ToDictionary(x => x.StoreId); /* ============================== * ④ 查询消耗次数和退卡次数 * ============================== */ var consumeSql = $@" SELECT hyhk.md AS StoreId, COUNT(DISTINCT hyhk.F_Id) AS ConsumeCount FROM lq_xh_hyhk hyhk WHERE hyhk.F_IsEffective = 1 AND hyhk.md IN ({storeIdsStr}) AND hyhk.Hksj >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND hyhk.Hksj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}' GROUP BY hyhk.md"; var consumeStatList = await _db.Ado.SqlQueryAsync(consumeSql); var consumeStatDict = consumeStatList.ToDictionary(x => x.StoreId); var refundCountSql = $@" SELECT hytk.md AS StoreId, COUNT(DISTINCT hytk.F_Id) AS RefundCount FROM lq_hytk_hytk hytk WHERE hytk.F_IsEffective = 1 AND hytk.md IN ({storeIdsStr}) AND hytk.tksj >= '{startDate:yyyy-MM-dd 00:00:00}' AND hytk.tksj <= '{endDate:yyyy-MM-dd 23:59:59}' GROUP BY hytk.md"; var refundCountStatList = await _db.Ado.SqlQueryAsync(refundCountSql); var refundCountStatDict = refundCountStatList.ToDictionary(x => x.StoreId); /* ============================== * ⑤ 内存合并 + 计算 * ============================== */ var resultList = pageStores.Select(md => { billingStatDict.TryGetValue(md.Id, out var billingStat); refundStatDict.TryGetValue(md.Id, out var refundStat); consumeStatDict.TryGetValue(md.Id, out var consumeStat); refundCountStatDict.TryGetValue(md.Id, out var refundCountStat); var billingTrace = billingStat?.BillingTraceabilityAmount ?? 0m; var refundTrace = refundStat?.RefundTraceabilityAmount ?? 0m; var trace = billingTrace - refundTrace; var billingCell = billingStat?.BillingCellAmount ?? 0m; var refundCell = refundStat?.RefundCellAmount ?? 0m; var cell = billingCell - refundCell; var billingSlim = billingStat?.BillingSlimAmount ?? 0m; var refundSlim = refundStat?.RefundSlimAmount ?? 0m; var slim = billingSlim - refundSlim; return new TechDepartmentDashboardStoreDetailListOutput { StoreId = md.Id, StoreName = md.Dm, StoreType = md.StoreType, StoreCategory = md.StoreCategory, Month = periodLabel, BillingTraceabilityAmount = billingTrace, RefundTraceabilityAmount = refundTrace, TraceabilityAmount = trace, BillingCellAmount = billingCell, RefundCellAmount = refundCell, CellAmount = cell, BillingSlimAmount = billingSlim, RefundSlimAmount = refundSlim, SlimAmount = slim, TotalKemeiIncome = trace + cell + slim, BillingCount = Convert.ToInt32(billingStat?.BillingCount ?? 0), ConsumeCount = Convert.ToInt32(consumeStat?.ConsumeCount ?? 0), RefundCount = Convert.ToInt32(refundCountStat?.RefundCount ?? 0) }; }).ToList(); return PageResult .SqlSugarPageResult(new SqlSugarPagedList { list = resultList, pagination = new SqlSugar.PagedModel { PageIndex = pageIndex, PageSize = pageSize, Total = total } }); } catch (Exception ex) { _logger.LogError(ex, "查询科技部驾驶舱门店明细列表失败"); throw NCCException.Oh($"查询失败:{ex.Message}"); } } /// /// 获取科技部驾驶舱老师明细列表 /// /// /// 获取指定科技部在指定月份的科技部老师明细列表(支持分页、排序、筛选) /// /// 查询参数 /// 老师明细列表 [HttpPost("GetTeacherDetailList")] public async Task GetTeacherDetailList([FromBody] TechDepartmentDashboardTeacherDetailListInput input) { try { if (input == null) throw NCCException.Oh("请求参数不能为空"); var statisticsInput = ToStatisticsInput(input); var (storeIds, range) = await ResolveRangeAsync(statisticsInput); if (!storeIds.Any()) { var emptyPagination = new SqlSugarPagedList { list = new List(), pagination = new SqlSugar.PagedModel { PageIndex = input.currentPage, PageSize = input.pageSize, Total = 0 } }; return PageResult.SqlSugarPageResult(emptyPagination); } var teacherStats = await _db.Queryable() .Where(x => range.StatisticsMonths.Contains(x.StatisticsMonth) && storeIds.Contains(x.StoreId)) .WhereIF(!string.IsNullOrWhiteSpace(input.TeacherName), x => x.EmployeeName.Contains(input.TeacherName)) .WhereIF(!string.IsNullOrWhiteSpace(input.StoreId), x => x.StoreId == input.StoreId) .ToListAsync(); var aggregatedList = teacherStats .GroupBy(x => x.EmployeeId) .Select(g => { var first = g.First(); return new TechDepartmentDashboardTeacherDetailListOutput { EmployeeId = first.EmployeeId ?? "", EmployeeName = first.EmployeeName ?? "", EmployeeAccount = first.EmployeeAccount ?? "", StoreId = first.StoreId ?? "", StoreName = first.StoreName ?? "", OrderAchievement = g.Sum(x => x.OrderAchievement), ConsumeAchievement = g.Sum(x => x.ConsumeAchievement), RefundAchievement = g.Sum(x => x.RefundAchievement), TotalPerformance = g.Sum(x => x.TotalPerformance), ProjectCount = g.Sum(x => x.ProjectCount), BaseSalary = g.Sum(x => x.BaseSalary), PerformanceCommissionAmount = g.Sum(x => x.PerformanceCommissionAmount), ConsumeCommissionAmount = g.Sum(x => x.ConsumeCommissionAmount), HandworkFee = g.Sum(x => x.HandworkFee), FinalGrossSalary = g.Sum(x => x.FinalGrossSalary) }; }) .OrderByDescending(x => x.TotalPerformance) .ToList(); var pageIndex = input.currentPage <= 0 ? 1 : input.currentPage; var pageSize = input.pageSize <= 0 ? 20 : input.pageSize; var pageList = aggregatedList.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList(); return PageResult.SqlSugarPageResult( new SqlSugarPagedList { list = pageList, pagination = new SqlSugar.PagedModel { PageIndex = pageIndex, PageSize = pageSize, Total = aggregatedList.Count } }); } catch (Exception ex) { _logger.LogError(ex, "查询科技部驾驶舱老师明细列表失败"); throw NCCException.Oh($"查询失败:{ex.Message}"); } } /// /// 获取科技部驾驶舱开单明细列表 /// /// /// 获取指定科技部在指定月份的科美开单明细列表(支持分页、排序、筛选) /// /// 查询参数 /// 开单明细列表 [HttpPost("GetBillingDetailList")] public async Task GetBillingDetailList([FromBody] TechDepartmentDashboardBillingDetailListInput input) { try { if (input == null) throw NCCException.Oh("请求参数不能为空"); var statisticsInput = ToStatisticsInput(input); var (storeIds, range) = await ResolveRangeAsync(statisticsInput); var startDate = range.StartInclusive; var endDateTime = range.EndInclusive; if (!storeIds.Any()) { var emptyPagination = new SqlSugarPagedList { list = new List(), pagination = new SqlSugar.PagedModel { PageIndex = input.currentPage, PageSize = input.pageSize, Total = 0 } }; return PageResult.SqlSugarPageResult(emptyPagination); } var query = _db.Queryable( (jksyj, kd, kh, md) => jksyj.Glkdbh == kd.Id && kd.Kdhy == kh.Id && jksyj.StoreId == md.Id) .Where((jksyj, kd, kh, md) => jksyj.IsEffective == 1 && kd.IsEffective == 1) .Where((jksyj, kd, kh, md) => storeIds.Contains(jksyj.StoreId)) .Where((jksyj, kd, kh, md) => jksyj.BeautyType == "溯源系统" || jksyj.BeautyType == "溯源" || jksyj.BeautyType == "cell" || jksyj.BeautyType == "Cell" || jksyj.BeautyType == "slim" || jksyj.BeautyType == "Slim") .Where((jksyj, kd, kh, md) => jksyj.Yjsj >= startDate && jksyj.Yjsj <= endDateTime) .WhereIF(!string.IsNullOrWhiteSpace(input.StoreId), (jksyj, kd, kh, md) => jksyj.StoreId == input.StoreId) .WhereIF(!string.IsNullOrWhiteSpace(input.MemberName), (jksyj, kd, kh, md) => kh.Khmc.Contains(input.MemberName)) .WhereIF(!string.IsNullOrWhiteSpace(input.BeautyType), (jksyj, kd, kh, md) => jksyj.BeautyType.Contains(input.BeautyType)) .OrderBy((jksyj, kd, kh, md) => jksyj.Yjsj, OrderByType.Desc); var result = await query.Select((jksyj, kd, kh, md) => new TechDepartmentDashboardBillingDetailListOutput { BillingId = kd.Id ?? "", StoreId = jksyj.StoreId ?? "", StoreName = md.Dm ?? "", MemberId = kd.Kdhy ?? "", MemberName = kh.Khmc ?? "", BeautyType = jksyj.BeautyType ?? "", BillingAmount = SqlFunc.ToDecimal(jksyj.Jksyj), ActualAmount = SqlFunc.ToDecimal(jksyj.Jksyj), BillingDate = jksyj.Yjsj, HealthCoachName = "", TechTeacherName = "" }).ToPagedListAsync(input.currentPage, input.pageSize); return PageResult.SqlSugarPageResult(result); } catch (Exception ex) { _logger.LogError(ex, "查询科技部驾驶舱开单明细列表失败"); throw NCCException.Oh($"查询失败:{ex.Message}"); } } /// /// 获取科技部驾驶舱消耗明细列表 /// /// /// 获取指定科技部在指定月份的科美消耗明细列表(支持分页、排序、筛选) /// /// 查询参数 /// 消耗明细列表 [HttpPost("GetConsumeDetailList")] public async Task GetConsumeDetailList([FromBody] TechDepartmentDashboardConsumeDetailListInput input) { try { if (input == null) throw NCCException.Oh("请求参数不能为空"); var statisticsInput = ToStatisticsInput(input); var (storeIds, range) = await ResolveRangeAsync(statisticsInput); var startDate = range.StartInclusive; var endDateTime = range.EndInclusive; if (!storeIds.Any()) { var emptyPagination = new SqlSugarPagedList { list = new List(), pagination = new SqlSugar.PagedModel { PageIndex = input.currentPage, PageSize = input.pageSize, Total = 0 } }; return PageResult.SqlSugarPageResult(emptyPagination); } var query = _db.Queryable( (xh, pxmx, xm, kh, md) => xh.Id == pxmx.ConsumeInfoId && pxmx.Px == xm.Id && xh.Hy == kh.Id && xh.Md == md.Id) .Where((xh, pxmx, xm, kh, md) => xh.IsEffective == 1 && pxmx.IsEffective == 1 && xm.Qt2 == "科美") .Where((xh, pxmx, xm, kh, md) => storeIds.Contains(xh.Md)) .Where((xh, pxmx, xm, kh, md) => xh.Hksj >= startDate && xh.Hksj <= endDateTime) .WhereIF(!string.IsNullOrWhiteSpace(input.StoreId), (xh, pxmx, xm, kh, md) => xh.Md == input.StoreId) .WhereIF(!string.IsNullOrWhiteSpace(input.MemberName), (xh, pxmx, xm, kh, md) => kh.Khmc.Contains(input.MemberName)) .OrderBy((xh, pxmx, xm, kh, md) => xh.Hksj, OrderByType.Desc); var result = await query.Select((xh, pxmx, xm, kh, md) => new TechDepartmentDashboardConsumeDetailListOutput { ConsumeId = xh.Id ?? "", StoreId = xh.Md ?? "", StoreName = md.Dm ?? "", MemberId = xh.Hy ?? "", MemberName = kh.Khmc ?? "", ItemId = xm.Id ?? "", ItemName = xm.Xmmc ?? "", BeautyType = xm.Qt2 ?? "", ConsumeAmount = pxmx.TotalPrice > 0 ? pxmx.TotalPrice : (pxmx.Pxjg * pxmx.ProjectNumber), ConsumeCount = pxmx.ProjectNumber, ConsumeDate = xh.Hksj, HealthCoachName = "", TechTeacherName = "" }).ToPagedListAsync(input.currentPage, input.pageSize); return PageResult.SqlSugarPageResult(result); } catch (Exception ex) { _logger.LogError(ex, "查询科技部驾驶舱消耗明细列表失败"); throw NCCException.Oh($"查询失败:{ex.Message}"); } } } public class StoreKemeiPerformanceRowDto { public string StoreId { get; set; } public string StoreName { get; set; } public decimal TraceabilityAmount { get; set; } public decimal CellAmount { get; set; } public decimal SlimAmount { get; set; } } public class StoreBaseDto { public string StoreId { get; set; } public string StoreName { get; set; } public int? StoreType { get; set; } public int? StoreCategory { get; set; } } public class StoreStatDto { public string StoreId { get; set; } public decimal BillingTraceabilityAmount { get; set; } public decimal BillingCellAmount { get; set; } public decimal BillingSlimAmount { get; set; } public long BillingCount { get; set; } } public class StoreRefundStatDto { public string StoreId { get; set; } public decimal RefundTraceabilityAmount { get; set; } public decimal RefundCellAmount { get; set; } public decimal RefundSlimAmount { get; set; } } public class StoreConsumeRefundCountDto { public string StoreId { get; set; } public long ConsumeCount { get; set; } public long RefundCount { get; set; } } }