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