using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.FriendlyException;
using NCC.Extend.Entitys.Dto.LqBusinessUnitDashboard;
using NCC.Extend.Entitys.lq_kd_kdjlb;
using NCC.Extend.Entitys.lq_xh_hyhk;
using NCC.Extend.Entitys.lq_hytk_hytk;
using NCC.Extend.Entitys.lq_kd_pxmx;
using NCC.Extend.Entitys.lq_hytk_mx;
using NCC.Extend.Entitys.lq_md_target;
using NCC.Extend.Entitys.lq_xh_pxmx;
using NCC.Extend.Entitys.lq_mdxx;
using NCC.Extend.Entitys.lq_md_general_manager_lifeline;
using NCC.Extend.Entitys.lq_business_unit_manager_salary_statistics;
using NCC.Extend.Entitys.lq_kd_jksyj;
using NCC.Extend.Entitys.lq_xh_jksyj;
using NCC.Extend.Entitys.lq_hytk_jksyj;
using NCC.System.Entitys.Permission;
using NCC.Extend.Entitys;
using SqlSugar;
namespace NCC.Extend
{
///
/// 事业部驾驶舱服务
///
[ApiDescriptionSettings(Tag = "事业部驾驶舱服务", Name = "LqBusinessUnitDashboard", Order = 203)]
[Route("api/Extend/[controller]")]
public class LqBusinessUnitDashboardService : IDynamicApiController, ITransient
{
private readonly ISqlSugarClient _db;
private readonly ILogger _logger;
///
/// 初始化事业部驾驶舱服务
///
public LqBusinessUnitDashboardService(ISqlSugarClient db, ILogger logger)
{
_db = db;
_logger = logger;
}
// 辅助方法：获取门店列表
private async Task> GetStoreIdsAsync(string businessUnitId, List storeIds, string statisticsMonth)
{
if (!string.IsNullOrWhiteSpace(businessUnitId))
{
return await _db.Queryable()
.Where(x => x.BusinessUnit == businessUnitId && x.Month == statisticsMonth)
.Select(x => x.StoreId)
.Distinct()
.ToListAsync();
}
return storeIds ?? new List();
}
// 辅助方法：获取门店列表（重载）
private async Task> GetStoreIdsAsync(BusinessUnitDashboardStatisticsInput input)
{
return await GetStoreIdsAsync(input.BusinessUnitId, input.StoreIds, input.StatisticsMonth);
}
// 辅助方法：获取指定月份的门店列表
private async Task> GetStoreIdsForMonthAsync(string businessUnitId, List storeIds, string month)
{
if (!string.IsNullOrWhiteSpace(businessUnitId))
{
return await _db.Queryable()
.Where(x => x.BusinessUnit == businessUnitId && x.Month == month)
.Select(x => x.StoreId)
.Distinct()
.ToListAsync();
}
return storeIds ?? new List();
}
// 辅助方法：计算单月业绩指标
private async Task<(decimal billingPerformance, decimal consumePerformance, decimal refundAmount, decimal netPerformance, decimal targetPerformance, decimal completionRate,
decimal lifeBeauty, decimal techBeauty, decimal medicalBeauty, decimal product)> CalculateMonthlyPerformance(
List storeIds, string month)
{
if (!storeIds.Any()) return (0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
var year = int.Parse(month.Substring(0, 4));
var monthNum = int.Parse(month.Substring(4, 2));
var startDate = new DateTime(year, monthNum, 1);
var endDate = startDate.AddMonths(1).AddDays(-1);
var endDateTime = month == DateTime.Now.ToString("yyyyMM")
? DateTime.Now
: endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
// 开单业绩
var billingAmount = await _db.Queryable()
.Where(x => storeIds.Contains(x.Djmd) && x.IsEffective == 1)
.Where(x => x.Kdrq.HasValue && x.Kdrq.Value >= startDate && x.Kdrq.Value <= endDateTime)
.SumAsync(x => (decimal?)x.Sfyj) ?? 0m;
// 退卡金额
var refundAmount = await _db.Queryable()
.Where(x => storeIds.Contains(x.Md) && x.IsEffective == 1)
.Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date)
.SumAsync(x => (decimal?)(x.ActualRefundAmount ?? x.Tkje ?? 0)) ?? 0m;
// 净业绩
var netPerformance = billingAmount - refundAmount;
// 消耗业绩
var consumeAmount = await _db.Queryable()
.Where(x => storeIds.Contains(x.Md) && x.IsEffective == 1)
.Where(x => x.Hksj.HasValue && x.Hksj.Value >= startDate && x.Hksj.Value <= endDateTime)
.SumAsync(x => (decimal?)x.Xfje) ?? 0m;
// 目标业绩
var targetPerformance = await _db.Queryable()
.Where(x => storeIds.Contains(x.StoreId) && x.Month == month)
.SumAsync(x => (decimal?)x.BusinessUnitTarget) ?? 0m;
// 完成率
var completionRate = targetPerformance > 0 ? (netPerformance / targetPerformance * 100m) : 0m;
// 品项分类业绩
var (billingLifeBeauty, billingTechBeauty, billingMedicalBeauty, billingProduct) =
await CalculatePerformanceByCategory(storeIds, month, false);
var (refundLifeBeauty, refundTechBeauty, refundMedicalBeauty, refundProduct) =
await CalculatePerformanceByCategory(storeIds, month, true);
var lifeBeauty = billingLifeBeauty - refundLifeBeauty;
var techBeauty = billingTechBeauty - refundTechBeauty;
var medicalBeauty = billingMedicalBeauty - refundMedicalBeauty;
var product = billingProduct - refundProduct;
return (billingAmount, consumeAmount, refundAmount, netPerformance, targetPerformance, completionRate,
lifeBeauty, techBeauty, medicalBeauty, product);
}
// 辅助方法：计算品项分类业绩
private async Task<(decimal lifeBeauty, decimal techBeauty, decimal medicalBeauty, decimal product)> CalculatePerformanceByCategory(
List storeIds, string month, bool isRefund = false)
{
if (!storeIds.Any()) return (0, 0, 0, 0);
var year = int.Parse(month.Substring(0, 4));
var monthNum = int.Parse(month.Substring(4, 2));
var startDate = new DateTime(year, monthNum, 1);
var endDate = startDate.AddMonths(1).AddDays(-1);
var endDateTime = month == DateTime.Now.ToString("yyyyMM")
? DateTime.Now
: endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
var storeIdsStr = string.Join("','", storeIds);
if (isRefund)
{
// 退卡品项分类业绩
var refundSql = $@"
SELECT
COALESCE(refund_mx.F_ItemCategory, '') as ItemCategory,
COALESCE(SUM(COALESCE(refund_mx.tkje, 0)), 0) as Amount
FROM lq_hytk_mx refund_mx
INNER JOIN lq_hytk_hytk refund ON refund_mx.F_RefundInfoId = refund.F_Id
WHERE refund_mx.F_IsEffective = 1
AND refund.F_IsEffective = 1
AND refund.md IN ('{storeIdsStr}')
AND refund.tksj >= '{startDate:yyyy-MM-dd} 00:00:00'
AND refund.tksj < '{endDate.AddDays(1):yyyy-MM-dd} 00:00:00'
GROUP BY refund_mx.F_ItemCategory";
var refundData = await _db.Ado.SqlQueryAsync(refundSql);
decimal lifeBeauty = 0, techBeauty = 0, medicalBeauty = 0, product = 0;
foreach (var row in refundData ?? Enumerable.Empty())
{
var category = row?.ItemCategory?.ToString() ?? "";
var amount = row?.Amount != null ? Convert.ToDecimal(row.Amount) : 0m;
if (category == "生美") lifeBeauty = amount;
else if (category == "科美") techBeauty = amount;
else if (category == "医美") medicalBeauty = amount;
else if (category == "产品") product = amount;
}
return (lifeBeauty, techBeauty, medicalBeauty, product);
}
else
{
// 开单品项分类业绩（从lq_kd_pxmx表的F_ActualPrice字段统计）
var billingSql = $@"
SELECT
COALESCE(pxmx.F_ItemCategory, '') as ItemCategory,
COALESCE(SUM(COALESCE(pxmx.F_ActualPrice, 0)), 0) as Amount
FROM lq_kd_pxmx pxmx
INNER JOIN lq_kd_kdjlb billing ON pxmx.glkdbh = billing.F_Id
WHERE pxmx.F_IsEffective = 1
AND billing.F_IsEffective = 1
AND billing.djmd IN ('{storeIdsStr}')
AND billing.kdrq >= '{startDate:yyyy-MM-dd} 00:00:00'
AND billing.kdrq < '{endDate.AddDays(1):yyyy-MM-dd} 00:00:00'
GROUP BY pxmx.F_ItemCategory";
var billingData = await _db.Ado.SqlQueryAsync(billingSql);
decimal lifeBeauty = 0, techBeauty = 0, medicalBeauty = 0, product = 0;
foreach (var row in billingData ?? Enumerable.Empty())
{
var category = row?.ItemCategory?.ToString() ?? "";
var amount = row?.Amount != null ? Convert.ToDecimal(row.Amount) : 0m;
if (category == "生美") lifeBeauty = amount;
else if (category == "科美") techBeauty = amount;
else if (category == "医美") medicalBeauty = amount;
else if (category == "产品") product = amount;
}
return (lifeBeauty, techBeauty, medicalBeauty, product);
}
}
///
/// 获取事业部驾驶舱统计数据
///
///
/// 获取指定事业部在指定月份的核心指标：开单业绩、消耗业绩、净业绩、目标业绩、完成率、管理的门店数、活跃门店数、品项分类业绩等
///
/// 示例请求：
/// ```json
/// {
/// "businessUnitId": "1649328471923847169",
/// "statisticsMonth": "202512"
/// }
/// ```
///
/// 或者：
/// ```json
/// {
/// "storeIds": ["1649328471923847169", "1649328471923847170"],
/// "statisticsMonth": "202512"
/// }
/// ```
///
/// 参数说明：
/// - businessUnitId: 事业部ID（BASE_ORGANIZE表的组织ID），与storeIds两者必填其一
/// - storeIds: 门店ID列表，与businessUnitId两者必填其一
/// - statisticsMonth: 统计月份，格式为YYYYMM（必填）
///
/// 返回数据说明：
/// - BillingPerformance: 开单业绩（开单实付业绩总和）
/// - ConsumePerformance: 消耗业绩（消耗金额总和）
/// - RefundAmount: 退卡金额（退卡实退金额总和）
/// - NetPerformance: 净业绩（开单业绩 - 退卡金额）
/// - TargetPerformance: 目标业绩（管理的所有门店的事业部业绩目标总和）
/// - CompletionRate: 完成率（净业绩 / 目标业绩 × 100%）
/// - ManagedStoreCount: 管理的门店数
/// - ActiveStoreCount: 活跃门店数（有开单或消耗的门店数量）
/// - 其他运营指标：开单次数、消耗次数、退卡次数、平均金额、人头数、人次、项目数、消耗率、退卡率
/// - 品项分类业绩：生美业绩、科美业绩、医美业绩、产品业绩
///
/// 查询参数
/// 事业部驾驶舱统计数据
/// 成功返回统计数据
/// 参数错误
/// 服务器错误
[HttpPost("GetStatistics")]
public async Task GetStatistics([FromBody] BusinessUnitDashboardStatisticsInput input)
{
try
{
if (input == null)
{
throw NCCException.Oh("请求参数不能为空");
}
if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
{
throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
}
// 参数验证：事业部ID和门店ID列表两者必填其一
if (string.IsNullOrWhiteSpace(input.BusinessUnitId) &&
(input.StoreIds == null || !input.StoreIds.Any()))
{
throw NCCException.Oh("事业部ID和门店ID列表不能同时为空，必须传入其中一个");
}
if (!string.IsNullOrWhiteSpace(input.BusinessUnitId) &&
input.StoreIds != null && input.StoreIds.Any())
{
throw NCCException.Oh("事业部ID和门店ID列表不能同时传入，请只传入其中一个");
}
_logger.LogInformation("开始查询事业部驾驶舱统计数据，事业部ID：{BusinessUnitId}，门店ID列表：{StoreIds}，统计月份：{StatisticsMonth}",
input.BusinessUnitId, input.StoreIds != null ? string.Join(",", input.StoreIds) : "", input.StatisticsMonth);
// 解析月份获取时间范围
var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
var startDate = new DateTime(year, month, 1);
var endDate = startDate.AddMonths(1).AddDays(-1);
var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
? DateTime.Now
: endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
// 获取门店列表
List storeIds;
if (!string.IsNullOrWhiteSpace(input.BusinessUnitId))
{
// 方式1：从lq_md_target表查询该月份、该事业部归属的门店列表
storeIds = await _db.Queryable()
.Where(x => x.BusinessUnit == input.BusinessUnitId && x.Month == input.StatisticsMonth)
.Select(x => x.StoreId)
.Distinct()
.ToListAsync();
if (!storeIds.Any())
{
_logger.LogWarning("事业部ID：{BusinessUnitId} 在 {StatisticsMonth} 月份没有管理的门店", input.BusinessUnitId, input.StatisticsMonth);
// 返回空数据
return new BusinessUnitDashboardStatisticsOutput();
}
}
else
{
// 方式2：直接使用传入的门店ID列表
storeIds = input.StoreIds;
}
// 1. 统计开单业绩（从lq_kd_kdjlb表的sfyj字段）
var billingAmount = await _db.Queryable()
.Where(x => storeIds.Contains(x.Djmd) && x.IsEffective == 1)
.Where(x => x.Kdrq.HasValue && x.Kdrq.Value >= startDate && x.Kdrq.Value <= endDateTime)
.SumAsync(x => (decimal?)x.Sfyj) ?? 0m;
// 2. 统计退卡金额（从lq_hytk_hytk表的F_ActualRefundAmount或tkje字段）
var refundAmount = await _db.Queryable()
.Where(x => storeIds.Contains(x.Md) && x.IsEffective == 1)
.Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date)
.SumAsync(x => (decimal?)(x.ActualRefundAmount ?? x.Tkje ?? 0)) ?? 0m;
// 3. 计算净业绩
var netPerformance = billingAmount - refundAmount;
// 4. 统计消耗业绩（从lq_xh_hyhk表的xfje字段）
var consumeAmount = await _db.Queryable()
.Where(x => storeIds.Contains(x.Md) && x.IsEffective == 1)
.Where(x => x.Hksj.HasValue && x.Hksj.Value >= startDate && x.Hksj.Value <= endDateTime)
.SumAsync(x => (decimal?)x.Xfje) ?? 0m;
// 5. 统计目标业绩（从lq_md_target表的F_BusinessUnitTarget字段）
var targetPerformance = await _db.Queryable()
.Where(x => storeIds.Contains(x.StoreId) && x.Month == input.StatisticsMonth)
.SumAsync(x => (decimal?)x.BusinessUnitTarget) ?? 0m;
// 6. 计算完成率
var completionRate = targetPerformance > 0 ? (netPerformance / targetPerformance * 100m) : 0m;
// 7. 管理的门店数
var managedStoreCount = storeIds.Count;
// 8. 统计活跃门店数（有开单或消耗的门店数量）
var storesWithBilling = await _db.Queryable()
.Where(x => storeIds.Contains(x.Djmd) && x.IsEffective == 1)
.Where(x => x.Kdrq.HasValue && x.Kdrq.Value >= startDate && x.Kdrq.Value <= endDateTime)
.Select(x => x.Djmd)
.Distinct()
.ToListAsync();
var storesWithConsume = await _db.Queryable()
.Where(x => storeIds.Contains(x.Md) && x.IsEffective == 1)
.Where(x => x.Hksj.HasValue && x.Hksj.Value >= startDate && x.Hksj.Value <= endDateTime)
.Select(x => x.Md)
.Distinct()
.ToListAsync();
var allActiveStoreIds = storesWithBilling.Union(storesWithConsume).Distinct().ToList();
var activeStoreCount = allActiveStoreIds.Count;
// 9. 统计开单次数
var billingCount = await _db.Queryable()
.Where(x => storeIds.Contains(x.Djmd) && x.IsEffective == 1)
.Where(x => x.Kdrq.HasValue && x.Kdrq.Value >= startDate && x.Kdrq.Value <= endDateTime)
.CountAsync();
// 10. 统计消耗次数
var consumeCount = await _db.Queryable()
.Where(x => storeIds.Contains(x.Md) && x.IsEffective == 1)
.Where(x => x.Hksj.HasValue && x.Hksj.Value >= startDate && x.Hksj.Value <= endDateTime)
.CountAsync();
// 11. 统计退卡次数
var refundCount = await _db.Queryable()
.Where(x => storeIds.Contains(x.Md) && x.IsEffective == 1)
.Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date)
.CountAsync();
// 12. 计算平均开单金额
var avgBillingAmount = billingCount > 0 ? billingAmount / (decimal)billingCount : 0m;
// 13. 计算平均消耗金额
var avgConsumeAmount = consumeCount > 0 ? consumeAmount / (decimal)consumeCount : 0m;
// 14. 统计人头数（去重后的消费会员数）
var headCount = await _db.Queryable()
.Where(x => storeIds.Contains(x.Md) && x.IsEffective == 1)
.Where(x => x.Hksj.HasValue && x.Hksj.Value >= startDate && x.Hksj.Value <= endDateTime)
.Select(x => x.Hy)
.Distinct()
.CountAsync();
// 15. 统计人次（日度去重客户数）- 使用SQL查询
var storeIdsStr = string.Join("','", storeIds);
var personCountSql = $@"
SELECT COUNT(DISTINCT CONCAT(xh.Hy, '-', DATE_FORMAT(xh.Hksj, '%Y-%m-%d'))) as PersonCount
FROM lq_xh_hyhk xh
WHERE xh.Md IN ('{storeIdsStr}')
AND xh.F_IsEffective = 1
AND xh.Hksj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
AND xh.Hksj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";
var personCountResult = await _db.Ado.SqlQueryAsync(personCountSql);
var personCount = personCountResult?.FirstOrDefault() != null
? Convert.ToInt32(personCountResult.FirstOrDefault().PersonCount ?? 0)
: 0;
// 16. 统计项目数（消耗的项目总数，从品项明细表统计原始项目数）
var projectCountSql = $@"
SELECT COALESCE(SUM(COALESCE(px.F_OriginalProjectNumber, px.F_ProjectNumber, 0)), 0) as ProjectCount
FROM lq_xh_pxmx px
INNER JOIN lq_xh_hyhk xh ON px.F_ConsumeInfoId = xh.F_Id
WHERE xh.Md IN ('{storeIdsStr}')
AND xh.F_IsEffective = 1
AND px.F_IsEffective = 1
AND xh.Hksj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
AND xh.Hksj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";
var projectCountResult = await _db.Ado.SqlQueryAsync(projectCountSql);
var projectCount = projectCountResult?.FirstOrDefault() != null
? Convert.ToDecimal(projectCountResult.FirstOrDefault().ProjectCount ?? 0)
: 0m;
// 17. 计算消耗率
var consumeRate = billingAmount > 0 ? (consumeAmount / billingAmount * 100m) : 0m;
// 18. 计算退卡率
var refundRate = billingAmount > 0 ? (refundAmount / billingAmount * 100m) : 0m;
// 19. 计算品项分类业绩（开单业绩 - 退卡业绩）
var (billingLifeBeauty, billingTechBeauty, billingMedicalBeauty, billingProduct) =
await CalculatePerformanceByCategory(storeIds, input.StatisticsMonth, false);
var (refundLifeBeauty, refundTechBeauty, refundMedicalBeauty, refundProduct) =
await CalculatePerformanceByCategory(storeIds, input.StatisticsMonth, true);
var lifeBeautyPerformance = billingLifeBeauty - refundLifeBeauty;
var techBeautyPerformance = billingTechBeauty - refundTechBeauty;
var medicalBeautyPerformance = billingMedicalBeauty - refundMedicalBeauty;
var productPerformance = billingProduct - refundProduct;
var result = new BusinessUnitDashboardStatisticsOutput
{
BillingPerformance = billingAmount,
ConsumePerformance = consumeAmount,
RefundAmount = refundAmount,
NetPerformance = netPerformance,
TargetPerformance = targetPerformance,
CompletionRate = completionRate,
ManagedStoreCount = managedStoreCount,
ActiveStoreCount = activeStoreCount,
BillingCount = billingCount,
ConsumeCount = consumeCount,
RefundCount = refundCount,
AvgBillingAmount = avgBillingAmount,
AvgConsumeAmount = avgConsumeAmount,
HeadCount = headCount,
PersonCount = personCount,
ProjectCount = projectCount,
ConsumeRate = consumeRate,
RefundRate = refundRate,
LifeBeautyPerformance = lifeBeautyPerformance,
TechBeautyPerformance = techBeautyPerformance,
MedicalBeautyPerformance = medicalBeautyPerformance,
ProductPerformance = productPerformance
};
_logger.LogInformation("事业部驾驶舱统计数据查询完成，开单业绩：{BillingPerformance}，消耗业绩：{ConsumePerformance}，净业绩：{NetPerformance}",
result.BillingPerformance, result.ConsumePerformance, result.NetPerformance);
return result;
}
catch (Exception ex)
{
_logger.LogError(ex, "查询事业部驾驶舱统计数据失败");
throw;
}
}
///
/// 获取事业部驾驶舱业绩趋势数据
///
///
/// 获取指定事业部在指定时间范围内的业绩趋势：开单业绩趋势、消耗业绩趋势、净业绩趋势、品项分类业绩趋势
///
/// 示例请求：
/// ```json
/// {
/// "businessUnitId": "734725299018663173",
/// "statisticsMonth": "202512",
/// "monthCount": 12
/// }
/// ```
///
/// 参数说明：
/// - businessUnitId: 事业部ID（BASE_ORGANIZE表的组织ID），与storeIds两者必填其一
/// - storeIds: 门店ID列表，与businessUnitId两者必填其一
/// - statisticsMonth: 统计月份，格式为YYYYMM（必填，作为结束月份）
/// - monthCount: 月份数量（可选，默认12，支持：3、6、12）
///
/// 返回数据说明：
/// - TrendData: 趋势数据列表，每个数据点包含月份、开单业绩、消耗业绩、退卡金额、净业绩、目标业绩、完成率、品项分类业绩等
///
/// 查询参数
/// 事业部驾驶舱业绩趋势数据
/// 成功返回趋势数据
/// 参数错误
/// 服务器错误
[HttpPost("GetPerformanceTrend")]
public async Task GetPerformanceTrend([FromBody] BusinessUnitDashboardPerformanceTrendInput input)
{
try
{
if (input == null) throw NCCException.Oh("请求参数不能为空");
if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
if (string.IsNullOrWhiteSpace(input.BusinessUnitId) && (input.StoreIds == null || !input.StoreIds.Any()))
throw NCCException.Oh("事业部ID和门店ID列表不能同时为空，必须传入其中一个");
var monthCount = input.MonthCount;
if (monthCount != 3 && monthCount != 6 && monthCount != 12)
monthCount = 12;
var baseMonth = DateTime.ParseExact(input.StatisticsMonth, "yyyyMM", null);
var result = new BusinessUnitDashboardPerformanceTrendOutput();
for (int i = monthCount - 1; i >= 0; i--)
{
var trendMonth = baseMonth.AddMonths(-i);
var trendMonthStr = trendMonth.ToString("yyyyMM");
List storeIds;
if (!string.IsNullOrWhiteSpace(input.BusinessUnitId))
{
storeIds = await GetStoreIdsForMonthAsync(input.BusinessUnitId, null, trendMonthStr);
}
else
{
storeIds = input.StoreIds;
}
if (!storeIds.Any())
{
result.TrendData.Add(new BusinessUnitPerformanceTrendPoint { Month = trendMonthStr });
continue;
}
var (billingPerformance, consumePerformance, refundAmount, netPerformance, targetPerformance, completionRate,
lifeBeauty, techBeauty, medicalBeauty, product) = await CalculateMonthlyPerformance(storeIds, trendMonthStr);
result.TrendData.Add(new BusinessUnitPerformanceTrendPoint
{
Month = trendMonthStr,
BillingPerformance = billingPerformance,
ConsumePerformance = consumePerformance,
RefundAmount = refundAmount,
NetPerformance = netPerformance,
TargetPerformance = targetPerformance,
CompletionRate = completionRate,
LifeBeautyPerformance = lifeBeauty,
TechBeautyPerformance = techBeauty,
MedicalBeautyPerformance = medicalBeauty,
ProductPerformance = product
});
}
return result;
}
catch (Exception ex)
{
_logger.LogError(ex, "查询事业部驾驶舱业绩趋势数据失败");
throw NCCException.Oh($"查询失败：{ex.Message}");
}
}
///
/// 获取事业部驾驶舱门店排行数据
///
///
/// 获取指定事业部在指定月份的门店业绩排行数据，支持按开单业绩、消耗业绩、完成率排序
///
/// 示例请求：
/// ```json
/// {
/// "businessUnitId": "734725299018663173",
/// "statisticsMonth": "202512",
/// "rankingType": "Billing",
/// "topCount": 10
/// }
/// ```
///
/// 参数说明：
/// - businessUnitId: 事业部ID（BASE_ORGANIZE表的组织ID），与storeIds两者必填其一
/// - storeIds: 门店ID列表，与businessUnitId两者必填其一
/// - statisticsMonth: 统计月份，格式为YYYYMM（必填）
/// - rankingType: 排行类型（可选，默认：Billing）
/// - Billing - 开单业绩排行
/// - Consume - 消耗业绩排行
/// - CompletionRate - 完成率排行
/// - topCount: 排行数量（可选，默认10）
///
/// 返回数据说明：
/// - RankingData: 门店排行数据列表，包含排名、门店信息、业绩数据、完成率、运营指标等
///
/// 查询参数
/// 事业部驾驶舱门店排行数据
/// 成功返回排行数据
/// 参数错误
/// 服务器错误
[HttpPost("GetStoreRanking")]
public async Task GetStoreRanking([FromBody] BusinessUnitDashboardStoreRankingInput input)
{
try
{
if (input == null) throw NCCException.Oh("请求参数不能为空");
if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
if (string.IsNullOrWhiteSpace(input.BusinessUnitId) && (input.StoreIds == null || !input.StoreIds.Any()))
throw NCCException.Oh("事业部ID和门店ID列表不能同时为空，必须传入其中一个");
_logger.LogInformation("开始查询事业部驾驶舱门店排行数据，事业部ID：{BusinessUnitId}，统计月份：{StatisticsMonth}，排行类型：{RankingType}",
input.BusinessUnitId, input.StatisticsMonth, input.RankingType);
// 解析月份获取时间范围
var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
var startDate = new DateTime(year, month, 1);
var endDate = startDate.AddMonths(1).AddDays(-1);
var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
? DateTime.Now
: endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
// 获取门店列表
var storeIds = await GetStoreIdsAsync(input.BusinessUnitId, input.StoreIds, input.StatisticsMonth);
if (!storeIds.Any())
{
_logger.LogWarning("事业部ID：{BusinessUnitId} 在 {StatisticsMonth} 月份没有管理的门店", input.BusinessUnitId, input.StatisticsMonth);
return new BusinessUnitDashboardStoreRankingOutput();
}
// 获取门店信息（名称、编码）
var stores = await _db.Queryable()
.Where(x => storeIds.Contains(x.Id))
.Select(x => new { x.Id, x.Dm, x.Mdbm })
.ToListAsync();
var storeDict = stores.ToDictionary(x => x.Id, x => new { Name = x.Dm ?? "", Code = x.Mdbm ?? "" });
// 计算每个门店的业绩数据
var storeIdsStr = string.Join("','", storeIds);
var rankingData = new List();
foreach (var storeId in storeIds)
{
// 开单业绩
var billingAmount = await _db.Queryable()
.Where(x => x.Djmd == storeId && x.IsEffective == 1)
.Where(x => x.Kdrq.HasValue && x.Kdrq.Value >= startDate && x.Kdrq.Value <= endDateTime)
.SumAsync(x => (decimal?)x.Sfyj) ?? 0m;
// 退卡金额
var refundAmount = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date)
.SumAsync(x => (decimal?)(x.ActualRefundAmount ?? x.Tkje ?? 0)) ?? 0m;
// 净业绩
var netPerformance = billingAmount - refundAmount;
// 消耗业绩
var consumeAmount = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Hksj.HasValue && x.Hksj.Value >= startDate && x.Hksj.Value <= endDateTime)
.SumAsync(x => (decimal?)x.Xfje) ?? 0m;
// 目标业绩
var targetPerformance = await _db.Queryable()
.Where(x => x.StoreId == storeId && x.Month == input.StatisticsMonth)
.SumAsync(x => (decimal?)x.BusinessUnitTarget) ?? 0m;
// 完成率
var completionRate = targetPerformance > 0 ? (netPerformance / targetPerformance * 100m) : 0m;
// 开单次数
var billingCount = await _db.Queryable()
.Where(x => x.Djmd == storeId && x.IsEffective == 1)
.Where(x => x.Kdrq.HasValue && x.Kdrq.Value >= startDate && x.Kdrq.Value <= endDateTime)
.CountAsync();
// 消耗次数
var consumeCount = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Hksj.HasValue && x.Hksj.Value >= startDate && x.Hksj.Value <= endDateTime)
.CountAsync();
// 人头数
var headCount = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Hksj.HasValue && x.Hksj.Value >= startDate && x.Hksj.Value <= endDateTime)
.Select(x => x.Hy)
.Distinct()
.CountAsync();
// 人次
var personCountSql = $@"
SELECT COUNT(DISTINCT CONCAT(xh.Hy, '-', DATE_FORMAT(xh.Hksj, '%Y-%m-%d'))) as PersonCount
FROM lq_xh_hyhk xh
WHERE xh.Md = '{storeId}'
AND xh.F_IsEffective = 1
AND xh.Hksj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
AND xh.Hksj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";
var personCountResult = await _db.Ado.SqlQueryAsync(personCountSql);
var personCount = personCountResult?.FirstOrDefault() != null
? Convert.ToInt32(personCountResult.FirstOrDefault().PersonCount ?? 0)
: 0;
// 项目数
var projectCountSql = $@"
SELECT COALESCE(SUM(COALESCE(px.F_OriginalProjectNumber, px.F_ProjectNumber, 0)), 0) as ProjectCount
FROM lq_xh_pxmx px
INNER JOIN lq_xh_hyhk xh ON px.F_ConsumeInfoId = xh.F_Id
WHERE xh.Md = '{storeId}'
AND xh.F_IsEffective = 1
AND px.F_IsEffective = 1
AND xh.Hksj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
AND xh.Hksj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";
var projectCountResult = await _db.Ado.SqlQueryAsync(projectCountSql);
var projectCount = projectCountResult?.FirstOrDefault() != null
? Convert.ToDecimal(projectCountResult.FirstOrDefault().ProjectCount ?? 0)
: 0m;
var storeInfo = storeDict.ContainsKey(storeId) ? storeDict[storeId] : new { Name = "", Code = "" };
rankingData.Add(new BusinessUnitStoreRankingItem
{
StoreId = storeId,
StoreCode = storeInfo.Code,
StoreName = storeInfo.Name,
BillingPerformance = billingAmount,
ConsumePerformance = consumeAmount,
RefundAmount = refundAmount,
NetPerformance = netPerformance,
TargetPerformance = targetPerformance,
CompletionRate = completionRate,
BillingCount = billingCount,
ConsumeCount = consumeCount,
HeadCount = headCount,
PersonCount = personCount,
ProjectCount = projectCount,
Percentage = 0m // 占比稍后计算
});
}
// 根据排行类型排序
var rankingType = (input.RankingType ?? "Billing").ToLower();
switch (rankingType)
{
case "consume":
rankingData = rankingData.OrderByDescending(x => x.ConsumePerformance).ToList();
break;
case "completionrate":
rankingData = rankingData.OrderByDescending(x => x.CompletionRate).ToList();
break;
default: // Billing
rankingData = rankingData.OrderByDescending(x => x.BillingPerformance).ToList();
break;
}
// 计算总业绩（用于计算占比）
decimal totalPerformance = 0m;
switch (rankingType)
{
case "consume":
totalPerformance = rankingData.Sum(x => x.ConsumePerformance);
break;
case "completionrate":
totalPerformance = rankingData.Sum(x => x.NetPerformance);
break;
default: // Billing
totalPerformance = rankingData.Sum(x => x.BillingPerformance);
break;
}
// 计算占比并设置排名
var topCount = input.TopCount > 0 ? input.TopCount : 10;
var result = new BusinessUnitDashboardStoreRankingOutput();
for (int i = 0; i < Math.Min(topCount, rankingData.Count); i++)
{
var item = rankingData[i];
item.Ranking = i + 1;
item.Percentage = totalPerformance > 0
? (rankingType == "billing" ? item.BillingPerformance :
rankingType == "consume" ? item.ConsumePerformance : item.NetPerformance) / totalPerformance * 100m
: 0m;
result.RankingData.Add(item);
}
_logger.LogInformation("事业部驾驶舱门店排行数据查询完成，返回{Count}条数据", result.RankingData.Count);
return result;
}
catch (Exception ex)
{
_logger.LogError(ex, "查询事业部驾驶舱门店排行数据失败");
throw NCCException.Oh($"查询失败：{ex.Message}");
}
}
///
/// 获取事业部驾驶舱运营统计数据
///
///
/// 获取指定事业部在指定月份的运营分析数据：开单分析、消耗分析、退卡分析
///
/// 示例请求：
/// ```json
/// {
/// "businessUnitId": "734725299018663173",
/// "statisticsMonth": "202512"
/// }
/// ```
///
/// 参数说明：
/// - businessUnitId: 事业部ID（BASE_ORGANIZE表的组织ID），与storeIds两者必填其一
/// - storeIds: 门店ID列表，与businessUnitId两者必填其一
/// - statisticsMonth: 统计月份，格式为YYYYMM（必填）
///
/// 返回数据说明：
/// - BillingAnalysis: 开单分析（开单次数、平均开单金额、开单门店数）
/// - ConsumeAnalysis: 消耗分析（消耗次数、消耗金额、消耗率）
/// - RefundAnalysis: 退卡分析（退卡次数、退卡金额、退卡率）
///
/// 查询参数
/// 事业部驾驶舱运营统计数据
/// 成功返回运营统计数据
/// 参数错误
/// 服务器错误
[HttpPost("GetOperationStatistics")]
public async Task GetOperationStatistics([FromBody] BusinessUnitDashboardStatisticsInput input)
{
try
{
if (input == null) throw NCCException.Oh("请求参数不能为空");
if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
if (string.IsNullOrWhiteSpace(input.BusinessUnitId) && (input.StoreIds == null || !input.StoreIds.Any()))
throw NCCException.Oh("事业部ID和门店ID列表不能同时为空，必须传入其中一个");
_logger.LogInformation("开始查询事业部驾驶舱运营统计数据，事业部ID：{BusinessUnitId}，统计月份：{StatisticsMonth}",
input.BusinessUnitId, input.StatisticsMonth);
// 解析月份获取时间范围
var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
var startDate = new DateTime(year, month, 1);
var endDate = startDate.AddMonths(1).AddDays(-1);
var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
? DateTime.Now
: endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
// 获取门店列表
var storeIds = await GetStoreIdsAsync(input.BusinessUnitId, input.StoreIds, input.StatisticsMonth);
if (!storeIds.Any())
{
_logger.LogWarning("事业部ID：{BusinessUnitId} 在 {StatisticsMonth} 月份没有管理的门店", input.BusinessUnitId, input.StatisticsMonth);
return new BusinessUnitDashboardOperationStatisticsOutput();
}
// 1. 开单分析
var billingQuery = _db.Queryable()
.Where(x => storeIds.Contains(x.Djmd) && x.IsEffective == 1)
.Where(x => x.Kdrq.HasValue && x.Kdrq.Value >= startDate && x.Kdrq.Value <= endDateTime);
var billingCount = await billingQuery.CountAsync();
var billingAmount = await billingQuery.SumAsync(x => (decimal?)x.Sfyj) ?? 0m;
var avgBillingAmount = billingCount > 0 ? billingAmount / (decimal)billingCount : 0m;
// 开单门店数（有开单的门店数量）
var billingStoreIds = await billingQuery
.Select(x => x.Djmd)
.Distinct()
.ToListAsync();
var billingStoreCount = billingStoreIds.Count;
// 2. 消耗分析
var consumeQuery = _db.Queryable()
.Where(x => storeIds.Contains(x.Md) && x.IsEffective == 1)
.Where(x => x.Hksj.HasValue && x.Hksj.Value >= startDate && x.Hksj.Value <= endDateTime);
var consumeCount = await consumeQuery.CountAsync();
var consumeAmount = await consumeQuery.SumAsync(x => (decimal?)x.Xfje) ?? 0m;
var consumeRate = billingAmount > 0 ? (consumeAmount / billingAmount * 100m) : 0m;
// 3. 退卡分析
var refundQuery = _db.Queryable()
.Where(x => storeIds.Contains(x.Md) && x.IsEffective == 1)
.Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date);
var refundCount = await refundQuery.CountAsync();
var refundAmount = await refundQuery.SumAsync(x => (decimal?)(x.ActualRefundAmount ?? x.Tkje ?? 0)) ?? 0m;
var refundRate = billingAmount > 0 ? (refundAmount / billingAmount * 100m) : 0m;
var result = new BusinessUnitDashboardOperationStatisticsOutput
{
BillingAnalysis = new BusinessUnitBillingAnalysis
{
BillingCount = billingCount,
AverageBillingAmount = avgBillingAmount,
BillingStoreCount = billingStoreCount
},
ConsumeAnalysis = new BusinessUnitConsumeAnalysis
{
ConsumeCount = consumeCount,
ConsumeAmount = consumeAmount,
ConsumeRate = consumeRate
},
RefundAnalysis = new BusinessUnitRefundAnalysis
{
RefundCount = refundCount,
RefundAmount = refundAmount,
RefundRate = refundRate
}
};
_logger.LogInformation("事业部驾驶舱运营统计数据查询完成，开单次数：{BillingCount}，消耗次数：{ConsumeCount}，退卡次数：{RefundCount}",
result.BillingAnalysis.BillingCount, result.ConsumeAnalysis.ConsumeCount, result.RefundAnalysis.RefundCount);
return result;
}
catch (Exception ex)
{
_logger.LogError(ex, "查询事业部驾驶舱运营统计数据失败");
throw NCCException.Oh($"查询失败：{ex.Message}");
}
}
///
/// 获取事业部驾驶舱门店明细列表
///
///
/// 获取指定事业部在指定月份的门店业绩明细列表，支持分页、排序、筛选
///
/// 示例请求：
/// ```json
/// {
/// "businessUnitId": "734725299018663173",
/// "statisticsMonth": "202512",
/// "currentPage": 1,
/// "pageSize": 10,
/// "storeName": "门店名称（可选，模糊查询）"
/// }
/// ```
///
/// 参数说明：
/// - businessUnitId: 事业部ID（BASE_ORGANIZE表的组织ID），与storeIds两者必填其一
/// - storeIds: 门店ID列表，与businessUnitId两者必填其一
/// - statisticsMonth: 统计月份，格式为YYYYMM（必填）
/// - currentPage: 当前页码（可选，默认1）
/// - pageSize: 每页数量（可选，默认10）
/// - storeName: 门店名称（可选，模糊查询）
///
/// 返回数据说明：
/// - 返回分页结果，包含门店列表和分页信息
/// - 每个门店明细包含：门店信息、业绩数据、运营指标等
///
/// 查询参数
/// 事业部驾驶舱门店明细列表
/// 成功返回明细列表
/// 参数错误
/// 服务器错误
[HttpPost("GetStoreDetailList")]
public async Task GetStoreDetailList([FromBody] BusinessUnitDashboardStoreDetailListInput input)
{
try
{
if (input == null) throw NCCException.Oh("请求参数不能为空");
if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
if (string.IsNullOrWhiteSpace(input.BusinessUnitId) && (input.StoreIds == null || !input.StoreIds.Any()))
throw NCCException.Oh("事业部ID和门店ID列表不能同时为空，必须传入其中一个");
_logger.LogInformation("开始查询事业部驾驶舱门店明细列表，事业部ID：{BusinessUnitId}，统计月份：{StatisticsMonth}，页码：{CurrentPage}，每页数量：{PageSize}",
input.BusinessUnitId, input.StatisticsMonth, input.currentPage, input.pageSize);
// 解析月份获取时间范围
var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
var startDate = new DateTime(year, month, 1);
var endDate = startDate.AddMonths(1).AddDays(-1);
var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
? DateTime.Now
: endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
// 获取门店列表
var storeIds = await GetStoreIdsAsync(input.BusinessUnitId, input.StoreIds, input.StatisticsMonth);
if (!storeIds.Any())
{
_logger.LogWarning("事业部ID：{BusinessUnitId} 在 {StatisticsMonth} 月份没有管理的门店", input.BusinessUnitId, input.StatisticsMonth);
return new { total = 0, list = new List() };
}
// 获取门店信息（名称、编码），支持门店名称筛选
var storeQuery = _db.Queryable()
.Where(x => storeIds.Contains(x.Id));
if (!string.IsNullOrWhiteSpace(input.StoreName))
{
storeQuery = storeQuery.Where(x => x.Dm.Contains(input.StoreName));
}
var stores = await storeQuery.Select(x => new { x.Id, x.Dm, x.Mdbm }).ToListAsync();
var filteredStoreIds = stores.Select(x => x.Id).ToList();
if (!filteredStoreIds.Any())
{
return new { total = 0, list = new List() };
}
// 计算每个门店的业绩数据
var detailList = new List();
var storeDict = stores.ToDictionary(x => x.Id, x => new { Name = x.Dm ?? "", Code = x.Mdbm ?? "" });
foreach (var storeId in filteredStoreIds)
{
// 开单业绩
var billingAmount = await _db.Queryable()
.Where(x => x.Djmd == storeId && x.IsEffective == 1)
.Where(x => x.Kdrq.HasValue && x.Kdrq.Value >= startDate && x.Kdrq.Value <= endDateTime)
.SumAsync(x => (decimal?)x.Sfyj) ?? 0m;
// 退卡金额
var refundAmount = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date)
.SumAsync(x => (decimal?)(x.ActualRefundAmount ?? x.Tkje ?? 0)) ?? 0m;
// 净业绩
var netPerformance = billingAmount - refundAmount;
// 消耗业绩
var consumeAmount = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Hksj.HasValue && x.Hksj.Value >= startDate && x.Hksj.Value <= endDateTime)
.SumAsync(x => (decimal?)x.Xfje) ?? 0m;
// 目标业绩
var targetPerformance = await _db.Queryable()
.Where(x => x.StoreId == storeId && x.Month == input.StatisticsMonth)
.SumAsync(x => (decimal?)x.BusinessUnitTarget) ?? 0m;
// 完成率
var completionRate = targetPerformance > 0 ? (netPerformance / targetPerformance * 100m) : 0m;
// 开单次数
var billingCount = await _db.Queryable()
.Where(x => x.Djmd == storeId && x.IsEffective == 1)
.Where(x => x.Kdrq.HasValue && x.Kdrq.Value >= startDate && x.Kdrq.Value <= endDateTime)
.CountAsync();
// 消耗次数
var consumeCount = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Hksj.HasValue && x.Hksj.Value >= startDate && x.Hksj.Value <= endDateTime)
.CountAsync();
// 退卡次数
var refundCount = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date)
.CountAsync();
// 平均开单金额
var avgBillingAmount = billingCount > 0 ? billingAmount / (decimal)billingCount : 0m;
// 平均消耗金额
var avgConsumeAmount = consumeCount > 0 ? consumeAmount / (decimal)consumeCount : 0m;
// 人头数
var headCount = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Hksj.HasValue && x.Hksj.Value >= startDate && x.Hksj.Value <= endDateTime)
.Select(x => x.Hy)
.Distinct()
.CountAsync();
// 人次
var personCountSql = $@"
SELECT COUNT(DISTINCT CONCAT(xh.Hy, '-', DATE_FORMAT(xh.Hksj, '%Y-%m-%d'))) as PersonCount
FROM lq_xh_hyhk xh
WHERE xh.Md = '{storeId}'
AND xh.F_IsEffective = 1
AND xh.Hksj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
AND xh.Hksj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";
var personCountResult = await _db.Ado.SqlQueryAsync(personCountSql);
var personCount = personCountResult?.FirstOrDefault() != null
? Convert.ToInt32(personCountResult.FirstOrDefault().PersonCount ?? 0)
: 0;
// 项目数
var projectCountSql = $@"
SELECT COALESCE(SUM(COALESCE(px.F_OriginalProjectNumber, px.F_ProjectNumber, 0)), 0) as ProjectCount
FROM lq_xh_pxmx px
INNER JOIN lq_xh_hyhk xh ON px.F_ConsumeInfoId = xh.F_Id
WHERE xh.Md = '{storeId}'
AND xh.F_IsEffective = 1
AND px.F_IsEffective = 1
AND xh.Hksj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
AND xh.Hksj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";
var projectCountResult = await _db.Ado.SqlQueryAsync(projectCountSql);
var projectCount = projectCountResult?.FirstOrDefault() != null
? Convert.ToDecimal(projectCountResult.FirstOrDefault().ProjectCount ?? 0)
: 0m;
var storeInfo = storeDict.ContainsKey(storeId) ? storeDict[storeId] : new { Name = "", Code = "" };
detailList.Add(new BusinessUnitDashboardStoreDetailListOutput
{
StoreId = storeId,
StoreCode = storeInfo.Code,
StoreName = storeInfo.Name,
BillingPerformance = billingAmount,
ConsumePerformance = consumeAmount,
RefundAmount = refundAmount,
NetPerformance = netPerformance,
TargetPerformance = targetPerformance,
CompletionRate = completionRate,
BillingCount = billingCount,
ConsumeCount = consumeCount,
RefundCount = refundCount,
AvgBillingAmount = avgBillingAmount,
AvgConsumeAmount = avgConsumeAmount,
HeadCount = headCount,
PersonCount = personCount,
ProjectCount = projectCount
});
}
// 分页处理
var currentPage = input.currentPage > 0 ? input.currentPage : 1;
var pageSize = input.pageSize > 0 ? input.pageSize : 10;
var total = detailList.Count;
var pagedList = detailList.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
_logger.LogInformation("事业部驾驶舱门店明细列表查询完成，总数：{Total}，返回{Count}条数据", total, pagedList.Count);
return new { total = total, list = pagedList };
}
catch (Exception ex)
{
_logger.LogError(ex, "查询事业部驾驶舱门店明细列表失败");
throw NCCException.Oh($"查询失败：{ex.Message}");
}
}
///
/// 获取事业部驾驶舱总经理/经理业绩排行
///
///
/// 获取指定事业部在指定月份的总经理/经理业绩排行数据
///
/// 示例请求：
/// ```json
/// {
/// "businessUnitId": "734725299018663173",
/// "statisticsMonth": "202512",
/// "topCount": 10
/// }
/// ```
///
/// 参数说明：
/// - businessUnitId: 事业部ID（BASE_ORGANIZE表的组织ID），与storeIds两者必填其一
/// - storeIds: 门店ID列表，与businessUnitId两者必填其一
/// - statisticsMonth: 统计月份，格式为YYYYMM（必填）
/// - topCount: 排行数量（可选，默认10）
///
/// 返回数据说明：
/// - RankingData: 总经理/经理排行数据列表，包含排名、姓名、管理的门店、业绩、工资等信息
///
/// 查询参数
/// 事业部驾驶舱总经理/经理排行数据
/// 成功返回排行数据
/// 参数错误
/// 服务器错误
[HttpPost("GetManagerRanking")]
public async Task GetManagerRanking([FromBody] BusinessUnitDashboardManagerRankingInput input)
{
try
{
if (input == null) throw NCCException.Oh("请求参数不能为空");
if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
if (string.IsNullOrWhiteSpace(input.BusinessUnitId) && (input.StoreIds == null || !input.StoreIds.Any()))
throw NCCException.Oh("事业部ID和门店ID列表不能同时为空，必须传入其中一个");
_logger.LogInformation("开始查询事业部驾驶舱总经理/经理排行数据，事业部ID：{BusinessUnitId}，统计月份：{StatisticsMonth}",
input.BusinessUnitId, input.StatisticsMonth);
// 解析月份获取时间范围
var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
var startDate = new DateTime(year, month, 1);
var endDate = startDate.AddMonths(1).AddDays(-1);
var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
? DateTime.Now
: endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
// 获取门店列表
var storeIds = await GetStoreIdsAsync(input.BusinessUnitId, input.StoreIds, input.StatisticsMonth);
if (!storeIds.Any())
{
_logger.LogWarning("事业部ID：{BusinessUnitId} 在 {StatisticsMonth} 月份没有管理的门店", input.BusinessUnitId, input.StatisticsMonth);
return new BusinessUnitDashboardManagerRankingOutput();
}
// 1. 获取总经理/经理归属信息（从lq_md_general_manager_lifeline表，只查询管理这些门店的总经理/经理）
var lifelineList = await _db.Queryable()
.Where(x => x.Month == input.StatisticsMonth && storeIds.Contains(x.StoreId))
.ToListAsync();
if (!lifelineList.Any())
{
return new BusinessUnitDashboardManagerRankingOutput();
}
// 2. 获取所有不重复的总经理/经理ID
var allManagerIds = lifelineList
.Where(x => !string.IsNullOrEmpty(x.GeneralManagerId))
.Select(x => x.GeneralManagerId)
.Distinct()
.ToList();
// 3. 获取用户信息
var users = await _db.Queryable()
.Where(x => allManagerIds.Contains(x.Id) && x.DeleteMark == null)
.Select(x => new { x.Id, x.RealName })
.ToListAsync();
var userDict = users.ToDictionary(x => x.Id, x => x.RealName ?? "");
// 4. 获取门店信息
var stores = await _db.Queryable()
.Where(x => storeIds.Contains(x.Id))
.Select(x => new { x.Id, x.Dm })
.ToListAsync();
var storeDict = stores.ToDictionary(x => x.Id, x => x.Dm ?? "");
// 5. 获取工资统计数据
var salaryStats = await _db.Queryable()
.Where(x => x.StatisticsMonth == input.StatisticsMonth && allManagerIds.Contains(x.EmployeeId))
.ToListAsync();
var salaryDict = salaryStats.ToDictionary(x => x.EmployeeId, x => x);
// 6. 按总经理/经理ID分组，获取每个总经理/经理管理的门店
var managerStoreDict = lifelineList
.Where(x => !string.IsNullOrEmpty(x.GeneralManagerId) && !string.IsNullOrEmpty(x.StoreId))
.GroupBy(x => x.GeneralManagerId)
.ToDictionary(g => g.Key, g => g.Select(x => x.StoreId).Distinct().ToList());
// 7. 计算每个总经理/经理的业绩数据
var rankingData = new List();
foreach (var managerId in allManagerIds)
{
var managerStores = managerStoreDict.ContainsKey(managerId) ? managerStoreDict[managerId] : new List();
if (!managerStores.Any()) continue;
// 计算管理的门店总业绩
decimal totalBilling = 0m;
decimal totalRefund = 0m;
decimal totalConsume = 0m;
foreach (var storeId in managerStores)
{
// 开单业绩
var billing = await _db.Queryable()
.Where(x => x.Djmd == storeId && x.IsEffective == 1)
.Where(x => x.Kdrq.HasValue && x.Kdrq.Value >= startDate && x.Kdrq.Value <= endDateTime)
.SumAsync(x => (decimal?)x.Sfyj) ?? 0m;
totalBilling += billing;
// 退卡金额
var refund = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date)
.SumAsync(x => (decimal?)(x.ActualRefundAmount ?? x.Tkje ?? 0)) ?? 0m;
totalRefund += refund;
// 消耗业绩
var consume = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Hksj.HasValue && x.Hksj.Value >= startDate && x.Hksj.Value <= endDateTime)
.SumAsync(x => (decimal?)x.Xfje) ?? 0m;
totalConsume += consume;
}
var totalPerformance = totalBilling - totalRefund;
// 获取工资数据
var salary = salaryDict.ContainsKey(managerId) ? salaryDict[managerId] : null;
var managerLifeline = lifelineList.FirstOrDefault(x => x.GeneralManagerId == managerId);
var storeNames = managerStores
.Where(s => storeDict.ContainsKey(s))
.Select(s => storeDict[s])
.ToList();
rankingData.Add(new BusinessUnitManagerRankingItem
{
ManagerId = managerId,
ManagerName = userDict.ContainsKey(managerId) ? userDict[managerId] : "",
ManagerType = managerLifeline?.ManagerType ?? 1,
Position = managerLifeline?.ManagerType == 1 ? "总经理" : "经理",
ManagedStoreCount = managerStores.Count,
ManagedStoreNames = storeNames,
TotalBillingPerformance = totalBilling,
TotalConsumePerformance = totalConsume,
TotalGrossProfit = salary?.GrossProfit ?? 0m,
BaseSalary = salary?.BaseSalary ?? 0m,
Commission = salary?.TotalCommission ?? 0m,
TotalSalary = salary?.FinalGrossSalary ?? 0m,
Percentage = 0m // 稍后计算
});
}
// 8. 按总业绩排序
rankingData = rankingData.OrderByDescending(x => x.TotalBillingPerformance).ToList();
// 9. 计算总业绩和占比
var totalPerformanceSum = rankingData.Sum(x => x.TotalBillingPerformance);
var topCount = input.TopCount > 0 ? input.TopCount : 10;
var result = new BusinessUnitDashboardManagerRankingOutput();
for (int i = 0; i < Math.Min(topCount, rankingData.Count); i++)
{
var item = rankingData[i];
item.Ranking = i + 1;
item.Percentage = totalPerformanceSum > 0 ? (item.TotalBillingPerformance / totalPerformanceSum * 100m) : 0m;
result.RankingData.Add(item);
}
_logger.LogInformation("事业部驾驶舱总经理/经理排行数据查询完成，返回{Count}条数据", result.RankingData.Count);
return result;
}
catch (Exception ex)
{
_logger.LogError(ex, "查询事业部驾驶舱总经理/经理排行数据失败");
throw NCCException.Oh($"查询失败：{ex.Message}");
}
}
///
/// 获取事业部驾驶舱对比分析数据
///
///
/// 获取指定事业部的对比分析数据：时间对比（环比、同比）、部门对比
///
/// 示例请求：
/// ```json
/// {
/// "businessUnitId": "734725299018663173",
/// "statisticsMonth": "202512",
/// "comparisonType": "Time"
/// }
/// ```
///
/// 参数说明：
/// - businessUnitId: 事业部ID（BASE_ORGANIZE表的组织ID），与storeIds两者必填其一
/// - storeIds: 门店ID列表，与businessUnitId两者必填其一
/// - statisticsMonth: 统计月份，格式为YYYYMM（必填）
/// - comparisonType: 对比类型（可选，默认：Time）
/// - Time - 时间对比（环比、同比）
/// - Department - 部门对比（各事业部对比）
///
/// 返回数据说明：
/// - TimeComparison: 时间对比数据（环比、同比）
/// - DepartmentComparison: 部门对比数据（各事业部对比）
///
/// 查询参数
/// 事业部驾驶舱对比分析数据
/// 成功返回对比分析数据
/// 参数错误
/// 服务器错误
[HttpPost("GetComparisonAnalysis")]
public async Task GetComparisonAnalysis([FromBody] BusinessUnitDashboardComparisonAnalysisInput input)
{
try
{
if (input == null) throw NCCException.Oh("请求参数不能为空");
if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
if (string.IsNullOrWhiteSpace(input.BusinessUnitId) && (input.StoreIds == null || !input.StoreIds.Any()))
throw NCCException.Oh("事业部ID和门店ID列表不能同时为空，必须传入其中一个");
_logger.LogInformation("开始查询事业部驾驶舱对比分析数据，事业部ID：{BusinessUnitId}，统计月份：{StatisticsMonth}",
input.BusinessUnitId, input.StatisticsMonth);
var result = new BusinessUnitDashboardComparisonAnalysisOutput();
// 1. 时间对比（环比、同比）
var currentMonth = DateTime.ParseExact(input.StatisticsMonth, "yyyyMM", null);
var lastMonth = currentMonth.AddMonths(-1);
var lastYearMonth = currentMonth.AddYears(-1);
// 当前月数据
var currentData = await CalculateMonthlyPerformance(
await GetStoreIdsAsync(input.BusinessUnitId, input.StoreIds, input.StatisticsMonth),
input.StatisticsMonth);
// 上月数据（环比）
var lastMonthStr = lastMonth.ToString("yyyyMM");
var lastMonthStoreIds = await GetStoreIdsForMonthAsync(input.BusinessUnitId, input.StoreIds, lastMonthStr);
var lastMonthData = await CalculateMonthlyPerformance(lastMonthStoreIds, lastMonthStr);
// 去年同月数据（同比）
var lastYearMonthStr = lastYearMonth.ToString("yyyyMM");
var lastYearMonthStoreIds = await GetStoreIdsForMonthAsync(input.BusinessUnitId, input.StoreIds, lastYearMonthStr);
var lastYearMonthData = await CalculateMonthlyPerformance(lastYearMonthStoreIds, lastYearMonthStr);
// 环比对比
result.TimeComparison.MonthOverMonth = new BusinessUnitComparisonItem
{
BillingPerformance = new BusinessUnitComparisonValue
{
CurrentValue = currentData.billingPerformance,
CompareValue = lastMonthData.billingPerformance,
GrowthRate = lastMonthData.billingPerformance > 0
? ((currentData.billingPerformance - lastMonthData.billingPerformance) / lastMonthData.billingPerformance * 100m)
: 0m
},
ConsumePerformance = new BusinessUnitComparisonValue
{
CurrentValue = currentData.consumePerformance,
CompareValue = lastMonthData.consumePerformance,
GrowthRate = lastMonthData.consumePerformance > 0
? ((currentData.consumePerformance - lastMonthData.consumePerformance) / lastMonthData.consumePerformance * 100m)
: 0m
},
NetPerformance = new BusinessUnitComparisonValue
{
CurrentValue = currentData.netPerformance,
CompareValue = lastMonthData.netPerformance,
GrowthRate = lastMonthData.netPerformance > 0
? ((currentData.netPerformance - lastMonthData.netPerformance) / lastMonthData.netPerformance * 100m)
: 0m
},
CompletionRate = new BusinessUnitComparisonValue
{
CurrentValue = currentData.completionRate,
CompareValue = lastMonthData.completionRate,
GrowthRate = lastMonthData.completionRate > 0
? ((currentData.completionRate - lastMonthData.completionRate) / lastMonthData.completionRate * 100m)
: 0m
}
};
// 同比对比
result.TimeComparison.YearOverYear = new BusinessUnitComparisonItem
{
BillingPerformance = new BusinessUnitComparisonValue
{
CurrentValue = currentData.billingPerformance,
CompareValue = lastYearMonthData.billingPerformance,
GrowthRate = lastYearMonthData.billingPerformance > 0
? ((currentData.billingPerformance - lastYearMonthData.billingPerformance) / lastYearMonthData.billingPerformance * 100m)
: 0m
},
ConsumePerformance = new BusinessUnitComparisonValue
{
CurrentValue = currentData.consumePerformance,
CompareValue = lastYearMonthData.consumePerformance,
GrowthRate = lastYearMonthData.consumePerformance > 0
? ((currentData.consumePerformance - lastYearMonthData.consumePerformance) / lastYearMonthData.consumePerformance * 100m)
: 0m
},
NetPerformance = new BusinessUnitComparisonValue
{
CurrentValue = currentData.netPerformance,
CompareValue = lastYearMonthData.netPerformance,
GrowthRate = lastYearMonthData.netPerformance > 0
? ((currentData.netPerformance - lastYearMonthData.netPerformance) / lastYearMonthData.netPerformance * 100m)
: 0m
},
CompletionRate = new BusinessUnitComparisonValue
{
CurrentValue = currentData.completionRate,
CompareValue = lastYearMonthData.completionRate,
GrowthRate = lastYearMonthData.completionRate > 0
? ((currentData.completionRate - lastYearMonthData.completionRate) / lastYearMonthData.completionRate * 100m)
: 0m
}
};
// 2. 部门对比（各事业部对比）- 如果comparisonType包含Department
if (input.ComparisonType != null && input.ComparisonType.ToLower().Contains("department"))
{
// 获取所有事业部ID（从BASE_ORGANIZE表）
var allBusinessUnits = await _db.Queryable()
.Where(x => x.DeleteMark == null && x.EnabledMark == 1)
.Where(x => x.Category == "事业部" || x.FullName.Contains("事业部"))
.Select(x => new { x.Id, x.FullName })
.ToListAsync();
foreach (var bu in allBusinessUnits)
{
var buStoreIds = await GetStoreIdsForMonthAsync(bu.Id, null, input.StatisticsMonth);
if (!buStoreIds.Any()) continue;
var buData = await CalculateMonthlyPerformance(buStoreIds, input.StatisticsMonth);
result.DepartmentComparison.DepartmentData.Add(new BusinessUnitDepartmentComparisonItem
{
BusinessUnitId = bu.Id,
BusinessUnitName = bu.FullName ?? "",
BillingPerformance = buData.billingPerformance,
ConsumePerformance = buData.consumePerformance,
NetPerformance = buData.netPerformance,
CompletionRate = buData.completionRate
});
}
}
_logger.LogInformation("事业部驾驶舱对比分析数据查询完成");
return result;
}
catch (Exception ex)
{
_logger.LogError(ex, "查询事业部驾驶舱对比分析数据失败");
throw NCCException.Oh($"查询失败：{ex.Message}");
}
}
///
/// 获取事业部驾驶舱门店业绩分布数据
///
///
/// 获取指定事业部在指定月份的门店业绩分布数据（饼图和柱状图数据）
///
/// 示例请求：
/// ```json
/// {
/// "businessUnitId": "734725299018663173",
/// "statisticsMonth": "202512"
/// }
/// ```
///
/// 参数说明：
/// - businessUnitId: 事业部ID（BASE_ORGANIZE表的组织ID），与storeIds两者必填其一
/// - storeIds: 门店ID列表，与businessUnitId两者必填其一
/// - statisticsMonth: 统计月份，格式为YYYYMM（必填）
///
/// 返回数据说明：
/// - DistributionData: 门店业绩分布数据列表（饼图数据，包含占比）
/// - ComparisonData: 门店业绩对比数据列表（柱状图数据）
///
/// 查询参数
/// 事业部驾驶舱门店业绩分布数据
/// 成功返回分布数据
/// 参数错误
/// 服务器错误
[HttpPost("GetStoreDistribution")]
public async Task GetStoreDistribution([FromBody] BusinessUnitDashboardStatisticsInput input)
{
try
{
if (input == null) throw NCCException.Oh("请求参数不能为空");
if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
if (string.IsNullOrWhiteSpace(input.BusinessUnitId) && (input.StoreIds == null || !input.StoreIds.Any()))
throw NCCException.Oh("事业部ID和门店ID列表不能同时为空，必须传入其中一个");
_logger.LogInformation("开始查询事业部驾驶舱门店业绩分布数据，事业部ID：{BusinessUnitId}，统计月份：{StatisticsMonth}",
input.BusinessUnitId, input.StatisticsMonth);
// 解析月份获取时间范围
var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
var startDate = new DateTime(year, month, 1);
var endDate = startDate.AddMonths(1).AddDays(-1);
var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
? DateTime.Now
: endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
// 获取门店列表
var storeIds = await GetStoreIdsAsync(input.BusinessUnitId, input.StoreIds, input.StatisticsMonth);
if (!storeIds.Any())
{
_logger.LogWarning("事业部ID：{BusinessUnitId} 在 {StatisticsMonth} 月份没有管理的门店", input.BusinessUnitId, input.StatisticsMonth);
return new BusinessUnitDashboardStoreDistributionOutput();
}
// 获取门店信息
var stores = await _db.Queryable()
.Where(x => storeIds.Contains(x.Id))
.Select(x => new { x.Id, x.Dm })
.ToListAsync();
var storeDict = stores.ToDictionary(x => x.Id, x => x.Dm ?? "");
// 计算每个门店的业绩
var distributionList = new List();
decimal totalPerformance = 0m;
foreach (var storeId in storeIds)
{
// 开单业绩
var billingAmount = await _db.Queryable()
.Where(x => x.Djmd == storeId && x.IsEffective == 1)
.Where(x => x.Kdrq.HasValue && x.Kdrq.Value >= startDate && x.Kdrq.Value <= endDateTime)
.SumAsync(x => (decimal?)x.Sfyj) ?? 0m;
// 退卡金额
var refundAmount = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date)
.SumAsync(x => (decimal?)(x.ActualRefundAmount ?? x.Tkje ?? 0)) ?? 0m;
// 净业绩
var netPerformance = billingAmount - refundAmount;
totalPerformance += netPerformance;
distributionList.Add(new BusinessUnitStoreDistributionItem
{
StoreId = storeId,
StoreName = storeDict.ContainsKey(storeId) ? storeDict[storeId] : "",
Performance = netPerformance,
Percentage = 0m // 稍后计算
});
}
// 计算占比
foreach (var item in distributionList)
{
item.Percentage = totalPerformance > 0 ? (item.Performance / totalPerformance * 100m) : 0m;
}
var result = new BusinessUnitDashboardStoreDistributionOutput
{
DistributionData = distributionList,
ComparisonData = distributionList.OrderByDescending(x => x.Performance).ToList()
};
_logger.LogInformation("事业部驾驶舱门店业绩分布数据查询完成，门店数量：{Count}", distributionList.Count);
return result;
}
catch (Exception ex)
{
_logger.LogError(ex, "查询事业部驾驶舱门店业绩分布数据失败");
throw NCCException.Oh($"查询失败：{ex.Message}");
}
}
///
/// 获取事业部驾驶舱总经理/经理业绩分布数据
///
///
/// 获取指定事业部在指定月份的总经理/经理业绩分布数据（饼图数据）
///
/// 示例请求：
/// ```json
/// {
/// "businessUnitId": "734725299018663173",
/// "statisticsMonth": "202512"
/// }
/// ```
///
/// 查询参数
/// 事业部驾驶舱总经理/经理业绩分布数据
[HttpPost("GetManagerDistribution")]
public async Task GetManagerDistribution([FromBody] BusinessUnitDashboardStatisticsInput input)
{
try
{
if (input == null) throw NCCException.Oh("请求参数不能为空");
if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
if (string.IsNullOrWhiteSpace(input.BusinessUnitId) && (input.StoreIds == null || !input.StoreIds.Any()))
throw NCCException.Oh("事业部ID和门店ID列表不能同时为空，必须传入其中一个");
// 解析月份获取时间范围
var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
var startDate = new DateTime(year, month, 1);
var endDate = startDate.AddMonths(1).AddDays(-1);
var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
? DateTime.Now
: endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
// 获取门店列表
var storeIds = await GetStoreIdsAsync(input.BusinessUnitId, input.StoreIds, input.StatisticsMonth);
if (!storeIds.Any())
{
return new BusinessUnitDashboardManagerDistributionOutput();
}
// 获取总经理/经理归属信息
var lifelineList = await _db.Queryable()
.Where(x => x.Month == input.StatisticsMonth && storeIds.Contains(x.StoreId))
.ToListAsync();
if (!lifelineList.Any())
{
return new BusinessUnitDashboardManagerDistributionOutput();
}
var allManagerIds = lifelineList
.Where(x => !string.IsNullOrEmpty(x.GeneralManagerId))
.Select(x => x.GeneralManagerId)
.Distinct()
.ToList();
// 获取用户信息
var users = await _db.Queryable()
.Where(x => allManagerIds.Contains(x.Id) && x.DeleteMark == null)
.Select(x => new { x.Id, x.RealName })
.ToListAsync();
var userDict = users.ToDictionary(x => x.Id, x => x.RealName ?? "");
// 按总经理/经理ID分组，获取每个总经理/经理管理的门店
var managerStoreDict = lifelineList
.Where(x => !string.IsNullOrEmpty(x.GeneralManagerId) && !string.IsNullOrEmpty(x.StoreId))
.GroupBy(x => x.GeneralManagerId)
.ToDictionary(g => g.Key, g => g.Select(x => x.StoreId).Distinct().ToList());
// 计算每个总经理/经理的业绩
var distributionList = new List();
decimal totalPerformance = 0m;
foreach (var managerId in allManagerIds)
{
var managerStores = managerStoreDict.ContainsKey(managerId) ? managerStoreDict[managerId] : new List();
if (!managerStores.Any()) continue;
decimal totalBilling = 0m;
decimal totalRefund = 0m;
foreach (var storeId in managerStores)
{
var billing = await _db.Queryable()
.Where(x => x.Djmd == storeId && x.IsEffective == 1)
.Where(x => x.Kdrq.HasValue && x.Kdrq.Value >= startDate && x.Kdrq.Value <= endDateTime)
.SumAsync(x => (decimal?)x.Sfyj) ?? 0m;
totalBilling += billing;
var refund = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date)
.SumAsync(x => (decimal?)(x.ActualRefundAmount ?? x.Tkje ?? 0)) ?? 0m;
totalRefund += refund;
}
var performance = totalBilling - totalRefund;
totalPerformance += performance;
distributionList.Add(new BusinessUnitManagerDistributionItem
{
ManagerId = managerId,
ManagerName = userDict.ContainsKey(managerId) ? userDict[managerId] : "",
Performance = performance,
Percentage = 0m // 稍后计算
});
}
// 计算占比
foreach (var item in distributionList)
{
item.Percentage = totalPerformance > 0 ? (item.Performance / totalPerformance * 100m) : 0m;
}
return new BusinessUnitDashboardManagerDistributionOutput
{
DistributionData = distributionList.OrderByDescending(x => x.Performance).ToList()
};
}
catch (Exception ex)
{
_logger.LogError(ex, "查询事业部驾驶舱总经理/经理业绩分布数据失败");
throw NCCException.Oh($"查询失败：{ex.Message}");
}
}
///
/// 获取事业部驾驶舱总经理/经理业绩趋势数据
///
///
/// 获取指定总经理/经理的近N个月业绩趋势数据
///
/// 示例请求：
/// ```json
/// {
/// "businessUnitId": "734725299018663173",
/// "statisticsMonth": "202512",
/// "managerIds": ["1649328471923847169"],
/// "monthCount": 12
/// }
/// ```
///
/// 查询参数
/// 事业部驾驶舱总经理/经理业绩趋势数据
[HttpPost("GetManagerTrend")]
public async Task GetManagerTrend([FromBody] BusinessUnitDashboardManagerTrendInput input)
{
try
{
if (input == null) throw NCCException.Oh("请求参数不能为空");
if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
if (input.ManagerIds == null || !input.ManagerIds.Any())
throw NCCException.Oh("总经理/经理ID列表不能为空");
var monthCount = input.MonthCount;
if (monthCount != 3 && monthCount != 6 && monthCount != 12)
monthCount = 12;
var baseMonth = DateTime.ParseExact(input.StatisticsMonth, "yyyyMM", null);
var result = new BusinessUnitDashboardManagerTrendOutput();
// 获取用户信息
var users = await _db.Queryable()
.Where(x => input.ManagerIds.Contains(x.Id) && x.DeleteMark == null)
.Select(x => new { x.Id, x.RealName })
.ToListAsync();
var userDict = users.ToDictionary(x => x.Id, x => x.RealName ?? "");
foreach (var managerId in input.ManagerIds)
{
var trendData = new BusinessUnitManagerTrendData
{
ManagerId = managerId,
ManagerName = userDict.ContainsKey(managerId) ? userDict[managerId] : ""
};
for (int i = monthCount - 1; i >= 0; i--)
{
var trendMonth = baseMonth.AddMonths(-i);
var trendMonthStr = trendMonth.ToString("yyyyMM");
// 获取该月份该总经理/经理管理的门店
var managerStores = await _db.Queryable()
.Where(x => x.GeneralManagerId == managerId && x.Month == trendMonthStr)
.Select(x => x.StoreId)
.Distinct()
.ToListAsync();
if (!managerStores.Any())
{
trendData.TrendPoints.Add(new BusinessUnitManagerTrendPoint { Month = trendMonthStr });
continue;
}
// 计算该月份的总业绩
var year = trendMonth.Year;
var month = trendMonth.Month;
var startDate = new DateTime(year, month, 1);
var endDate = startDate.AddMonths(1).AddDays(-1);
var endDateTime = trendMonthStr == DateTime.Now.ToString("yyyyMM")
? DateTime.Now
: endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
decimal totalBilling = 0m;
decimal totalRefund = 0m;
foreach (var storeId in managerStores)
{
var billing = await _db.Queryable()
.Where(x => x.Djmd == storeId && x.IsEffective == 1)
.Where(x => x.Kdrq.HasValue && x.Kdrq.Value >= startDate && x.Kdrq.Value <= endDateTime)
.SumAsync(x => (decimal?)x.Sfyj) ?? 0m;
totalBilling += billing;
var refund = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date)
.SumAsync(x => (decimal?)(x.ActualRefundAmount ?? x.Tkje ?? 0)) ?? 0m;
totalRefund += refund;
}
trendData.TrendPoints.Add(new BusinessUnitManagerTrendPoint
{
Month = trendMonthStr,
TotalPerformance = totalBilling - totalRefund
});
}
result.ManagerTrendData.Add(trendData);
}
return result;
}
catch (Exception ex)
{
_logger.LogError(ex, "查询事业部驾驶舱总经理/经理业绩趋势数据失败");
throw NCCException.Oh($"查询失败：{ex.Message}");
}
}
///
/// 获取事业部驾驶舱店长业绩排行
///
///
/// 获取指定事业部在指定月份的店长业绩排行数据
///
/// 示例请求：
/// ```json
/// {
/// "businessUnitId": "734725299018663173",
/// "statisticsMonth": "202512",
/// "topCount": 10
/// }
/// ```
///
/// 查询参数
/// 事业部驾驶舱店长业绩排行数据
[HttpPost("GetStoreManagerRanking")]
public async Task GetStoreManagerRanking([FromBody] BusinessUnitDashboardStoreManagerRankingInput input)
{
try
{
if (input == null) throw NCCException.Oh("请求参数不能为空");
if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
if (string.IsNullOrWhiteSpace(input.BusinessUnitId) && (input.StoreIds == null || !input.StoreIds.Any()))
throw NCCException.Oh("事业部ID和门店ID列表不能同时为空，必须传入其中一个");
// 解析月份获取时间范围
var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
var startDate = new DateTime(year, month, 1);
var endDate = startDate.AddMonths(1).AddDays(-1);
var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
? DateTime.Now
: endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
// 获取门店列表
var storeIds = await GetStoreIdsAsync(input.BusinessUnitId, input.StoreIds, input.StatisticsMonth);
if (!storeIds.Any())
{
return new BusinessUnitDashboardStoreManagerRankingOutput();
}
// 获取店长信息（BASE_USER表，F_GW = '店长'）
var storeManagers = await _db.Queryable()
.Where(x => x.Gw == "店长" && storeIds.Contains(x.Mdid) && x.DeleteMark == null && x.EnabledMark == 1)
.Select(x => new { x.Id, x.RealName, x.Mdid })
.ToListAsync();
if (!storeManagers.Any())
{
return new BusinessUnitDashboardStoreManagerRankingOutput();
}
// 获取门店信息
var stores = await _db.Queryable()
.Where(x => storeIds.Contains(x.Id))
.Select(x => new { x.Id, x.Dm })
.ToListAsync();
var storeDict = stores.ToDictionary(x => x.Id, x => x.Dm ?? "");
// 计算每个店长的业绩
var rankingData = new List();
foreach (var manager in storeManagers)
{
var storeId = manager.Mdid;
if (string.IsNullOrEmpty(storeId)) continue;
// 开单业绩
var billingAmount = await _db.Queryable()
.Where(x => x.Djmd == storeId && x.IsEffective == 1)
.Where(x => x.Kdrq.HasValue && x.Kdrq.Value >= startDate && x.Kdrq.Value <= endDateTime)
.SumAsync(x => (decimal?)x.Sfyj) ?? 0m;
// 退卡金额
var refundAmount = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date)
.SumAsync(x => (decimal?)(x.ActualRefundAmount ?? x.Tkje ?? 0)) ?? 0m;
// 净业绩
var netPerformance = billingAmount - refundAmount;
// 消耗业绩
var consumeAmount = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Hksj.HasValue && x.Hksj.Value >= startDate && x.Hksj.Value <= endDateTime)
.SumAsync(x => (decimal?)x.Xfje) ?? 0m;
// 目标业绩
var targetPerformance = await _db.Queryable()
.Where(x => x.StoreId == storeId && x.Month == input.StatisticsMonth)
.SumAsync(x => (decimal?)x.BusinessUnitTarget) ?? 0m;
// 完成率
var completionRate = targetPerformance > 0 ? (netPerformance / targetPerformance * 100m) : 0m;
rankingData.Add(new BusinessUnitStoreManagerRankingItem
{
StoreManagerId = manager.Id,
StoreManagerName = manager.RealName ?? "",
StoreId = storeId,
StoreName = storeDict.ContainsKey(storeId) ? storeDict[storeId] : "",
BillingPerformance = billingAmount,
ConsumePerformance = consumeAmount,
RefundAmount = refundAmount,
NetPerformance = netPerformance,
CompletionRate = completionRate,
Ranking = 0 // 稍后设置
});
}
// 按净业绩排序
rankingData = rankingData.OrderByDescending(x => x.NetPerformance).ToList();
// 设置排名
var topCount = input.TopCount > 0 ? input.TopCount : 10;
var result = new BusinessUnitDashboardStoreManagerRankingOutput();
for (int i = 0; i < Math.Min(topCount, rankingData.Count); i++)
{
rankingData[i].Ranking = i + 1;
result.RankingData.Add(rankingData[i]);
}
return result;
}
catch (Exception ex)
{
_logger.LogError(ex, "查询事业部驾驶舱店长业绩排行数据失败");
throw NCCException.Oh($"查询失败：{ex.Message}");
}
}
///
/// 获取事业部驾驶舱健康师业绩排行
///
///
/// 获取指定事业部在指定月份的健康师业绩排行数据
///
/// 示例请求：
/// ```json
/// {
/// "businessUnitId": "734725299018663173",
/// "statisticsMonth": "202512",
/// "topCount": 10
/// }
/// ```
///
/// 查询参数
/// 事业部驾驶舱健康师业绩排行数据
[HttpPost("GetHealthCoachRanking")]
public async Task GetHealthCoachRanking([FromBody] BusinessUnitDashboardHealthCoachRankingInput input)
{
try
{
if (input == null) throw NCCException.Oh("请求参数不能为空");
if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
if (string.IsNullOrWhiteSpace(input.BusinessUnitId) && (input.StoreIds == null || !input.StoreIds.Any()))
throw NCCException.Oh("事业部ID和门店ID列表不能同时为空，必须传入其中一个");
// 解析月份获取时间范围
var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
var startDate = new DateTime(year, month, 1);
var endDate = startDate.AddMonths(1).AddDays(-1);
var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
? DateTime.Now
: endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
// 获取门店列表
var storeIds = await GetStoreIdsAsync(input.BusinessUnitId, input.StoreIds, input.StatisticsMonth);
if (!storeIds.Any())
{
return new BusinessUnitDashboardHealthCoachRankingOutput();
}
// 获取健康师信息（BASE_USER表，F_GW = '健康师'）
var healthCoaches = await _db.Queryable()
.Where(x => x.Gw == "健康师" && storeIds.Contains(x.Mdid) && x.DeleteMark == null && x.EnabledMark == 1)
.Select(x => new { x.Id, x.RealName, x.Mdid })
.ToListAsync();
if (!healthCoaches.Any())
{
return new BusinessUnitDashboardHealthCoachRankingOutput();
}
// 获取门店信息
var stores = await _db.Queryable()
.Where(x => storeIds.Contains(x.Id))
.Select(x => new { x.Id, x.Dm })
.ToListAsync();
var storeDict = stores.ToDictionary(x => x.Id, x => x.Dm ?? "");
// 计算每个健康师的业绩
var rankingData = new List();
var storeIdsStr = string.Join("','", storeIds);
foreach (var coach in healthCoaches)
{
var storeId = coach.Mdid;
if (string.IsNullOrEmpty(storeId)) continue;
// 开单业绩（从lq_kd_jksyj表，jksyj字段是字符串类型，需要转换，使用jkszh字段匹配健康师ID）
var billingSql = $@"
SELECT COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as Amount
FROM lq_kd_jksyj
WHERE F_IsEffective = 1
AND F_StoreId = '{storeId}'
AND jkszh = '{coach.Id}'
AND yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
AND yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";
var billingResult = await _db.Ado.SqlQueryAsync(billingSql);
var billingPerformance = billingResult?.FirstOrDefault() != null
? Convert.ToDecimal(billingResult.FirstOrDefault().Amount ?? 0)
: 0m;
// 消耗业绩（从lq_xh_jksyj表，使用jkszh字段匹配健康师ID）
var consumePerformance = await _db.Queryable()
.Where(x => x.StoreId == storeId && x.Jkszh == coach.Id && x.IsEffective == 1)
.Where(x => x.Yjsj.HasValue && x.Yjsj.Value >= startDate && x.Yjsj.Value <= endDateTime)
.SumAsync(x => (decimal?)(x.Jksyj ?? 0)) ?? 0m;
// 退卡业绩（从lq_hytk_jksyj表，使用jkszh字段匹配健康师ID）
var refundPerformance = await _db.Queryable()
.Where(x => x.StoreId == storeId && x.Jkszh == coach.Id && x.IsEffective == 1)
.Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date)
.SumAsync(x => (decimal?)(x.Jksyj ?? 0)) ?? 0m;
// 总业绩
var totalPerformance = billingPerformance - refundPerformance;
// 项目数（从lq_xh_pxmx表，关联lq_xh_hyhk，再关联lq_xh_jksyj）
var projectCountSql = $@"
SELECT COALESCE(SUM(COALESCE(px.F_OriginalProjectNumber, px.F_ProjectNumber, 0)), 0) as ProjectCount
FROM lq_xh_pxmx px
INNER JOIN lq_xh_hyhk xh ON px.F_ConsumeInfoId = xh.F_Id
INNER JOIN lq_xh_jksyj jksyj ON jksyj.glkdbh = xh.F_Id
WHERE xh.Md = '{storeId}'
AND jksyj.jkszh = '{coach.Id}'
AND xh.F_IsEffective = 1
AND jksyj.F_IsEffective = 1
AND px.F_IsEffective = 1
AND xh.Hksj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
AND xh.Hksj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";
var projectCountResult = await _db.Ado.SqlQueryAsync(projectCountSql);
var projectCount = projectCountResult?.FirstOrDefault() != null
? Convert.ToDecimal(projectCountResult.FirstOrDefault().ProjectCount ?? 0)
: 0m;
rankingData.Add(new BusinessUnitHealthCoachRankingItem
{
HealthCoachId = coach.Id,
HealthCoachName = coach.RealName ?? "",
StoreId = storeId,
StoreName = storeDict.ContainsKey(storeId) ? storeDict[storeId] : "",
BillingPerformance = billingPerformance,
ConsumePerformance = consumePerformance,
RefundPerformance = refundPerformance,
TotalPerformance = totalPerformance,
ProjectCount = projectCount,
Ranking = 0 // 稍后设置
});
}
// 按总业绩排序
rankingData = rankingData.OrderByDescending(x => x.TotalPerformance).ToList();
// 设置排名
var topCount = input.TopCount > 0 ? input.TopCount : 10;
var result = new BusinessUnitDashboardHealthCoachRankingOutput();
for (int i = 0; i < Math.Min(topCount, rankingData.Count); i++)
{
rankingData[i].Ranking = i + 1;
result.RankingData.Add(rankingData[i]);
}
return result;
}
catch (Exception ex)
{
_logger.LogError(ex, "查询事业部驾驶舱健康师业绩排行数据失败");
throw NCCException.Oh($"查询失败：{ex.Message}");
}
}
///
/// 获取事业部驾驶舱总经理/经理明细列表
///
///
/// 获取指定事业部在指定月份的总经理/经理明细列表，支持分页、排序、筛选
///
/// 查询参数
/// 事业部驾驶舱总经理/经理明细列表
[HttpPost("GetManagerDetailList")]
public async Task GetManagerDetailList([FromBody] BusinessUnitDashboardManagerDetailListInput input)
{
try
{
if (input == null) throw NCCException.Oh("请求参数不能为空");
if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
if (string.IsNullOrWhiteSpace(input.BusinessUnitId) && (input.StoreIds == null || !input.StoreIds.Any()))
throw NCCException.Oh("事业部ID和门店ID列表不能同时为空，必须传入其中一个");
// 解析月份获取时间范围
var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
var startDate = new DateTime(year, month, 1);
var endDate = startDate.AddMonths(1).AddDays(-1);
var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
? DateTime.Now
: endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
// 获取门店列表
var storeIds = await GetStoreIdsAsync(input.BusinessUnitId, input.StoreIds, input.StatisticsMonth);
if (!storeIds.Any())
{
return new { total = 0, list = new List() };
}
// 获取总经理/经理归属信息
var lifelineList = await _db.Queryable()
.Where(x => x.Month == input.StatisticsMonth && storeIds.Contains(x.StoreId))
.ToListAsync();
if (!lifelineList.Any())
{
return new { total = 0, list = new List() };
}
var allManagerIds = lifelineList
.Where(x => !string.IsNullOrEmpty(x.GeneralManagerId))
.Select(x => x.GeneralManagerId)
.Distinct()
.ToList();
// 获取用户信息，支持姓名筛选
var userQuery = _db.Queryable()
.Where(x => allManagerIds.Contains(x.Id) && x.DeleteMark == null);
if (!string.IsNullOrWhiteSpace(input.ManagerName))
{
userQuery = userQuery.Where(x => x.RealName.Contains(input.ManagerName));
}
var users = await userQuery.Select(x => new { x.Id, x.RealName }).ToListAsync();
var filteredManagerIds = users.Select(x => x.Id).ToList();
if (!filteredManagerIds.Any())
{
return new { total = 0, list = new List() };
}
var userDict = users.ToDictionary(x => x.Id, x => x.RealName ?? "");
// 获取门店信息
var stores = await _db.Queryable()
.Where(x => storeIds.Contains(x.Id))
.Select(x => new { x.Id, x.Dm })
.ToListAsync();
var storeDict = stores.ToDictionary(x => x.Id, x => x.Dm ?? "");
// 获取工资统计数据
var salaryStats = await _db.Queryable()
.Where(x => x.StatisticsMonth == input.StatisticsMonth && filteredManagerIds.Contains(x.EmployeeId))
.ToListAsync();
var salaryDict = salaryStats.ToDictionary(x => x.EmployeeId, x => x);
// 按总经理/经理ID分组
var managerStoreDict = lifelineList
.Where(x => filteredManagerIds.Contains(x.GeneralManagerId) && !string.IsNullOrEmpty(x.StoreId))
.GroupBy(x => x.GeneralManagerId)
.ToDictionary(g => g.Key, g => g.Select(x => x.StoreId).Distinct().ToList());
// 计算每个总经理/经理的业绩数据
var detailList = new List();
foreach (var managerId in filteredManagerIds)
{
var managerStores = managerStoreDict.ContainsKey(managerId) ? managerStoreDict[managerId] : new List();
if (!managerStores.Any()) continue;
decimal totalBilling = 0m;
decimal totalRefund = 0m;
decimal totalConsume = 0m;
foreach (var storeId in managerStores)
{
var billing = await _db.Queryable()
.Where(x => x.Djmd == storeId && x.IsEffective == 1)
.Where(x => x.Kdrq.HasValue && x.Kdrq.Value >= startDate && x.Kdrq.Value <= endDateTime)
.SumAsync(x => (decimal?)x.Sfyj) ?? 0m;
totalBilling += billing;
var refund = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date)
.SumAsync(x => (decimal?)(x.ActualRefundAmount ?? x.Tkje ?? 0)) ?? 0m;
totalRefund += refund;
var consume = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Hksj.HasValue && x.Hksj.Value >= startDate && x.Hksj.Value <= endDateTime)
.SumAsync(x => (decimal?)x.Xfje) ?? 0m;
totalConsume += consume;
}
var salary = salaryDict.ContainsKey(managerId) ? salaryDict[managerId] : null;
var managerLifeline = lifelineList.FirstOrDefault(x => x.GeneralManagerId == managerId);
var storeNames = managerStores
.Where(s => storeDict.ContainsKey(s))
.Select(s => storeDict[s])
.ToList();
detailList.Add(new BusinessUnitDashboardManagerDetailListOutput
{
ManagerId = managerId,
ManagerName = userDict.ContainsKey(managerId) ? userDict[managerId] : "",
ManagerType = managerLifeline?.ManagerType ?? 1,
Position = managerLifeline?.ManagerType == 1 ? "总经理" : "经理",
ManagedStoreCount = managerStores.Count,
ManagedStoreNames = storeNames,
TotalBillingPerformance = totalBilling,
TotalConsumePerformance = totalConsume,
TotalGrossProfit = salary?.GrossProfit ?? 0m,
BaseSalary = salary?.BaseSalary ?? 0m,
Commission = salary?.TotalCommission ?? 0m,
TotalSalary = salary?.FinalGrossSalary ?? 0m
});
}
// 分页处理
var currentPage = input.currentPage > 0 ? input.currentPage : 1;
var pageSize = input.pageSize > 0 ? input.pageSize : 10;
var total = detailList.Count;
var pagedList = detailList.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
return new { total = total, list = pagedList };
}
catch (Exception ex)
{
_logger.LogError(ex, "查询事业部驾驶舱总经理/经理明细列表失败");
throw NCCException.Oh($"查询失败：{ex.Message}");
}
}
///
/// 获取事业部驾驶舱店长明细列表
///
///
/// 获取指定事业部在指定月份的店长明细列表，支持分页、排序、筛选
///
/// 查询参数
/// 事业部驾驶舱店长明细列表
[HttpPost("GetStoreManagerDetailList")]
public async Task GetStoreManagerDetailList([FromBody] BusinessUnitDashboardStoreManagerDetailListInput input)
{
try
{
if (input == null) throw NCCException.Oh("请求参数不能为空");
if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
if (string.IsNullOrWhiteSpace(input.BusinessUnitId) && (input.StoreIds == null || !input.StoreIds.Any()))
throw NCCException.Oh("事业部ID和门店ID列表不能同时为空，必须传入其中一个");
// 解析月份获取时间范围
var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
var startDate = new DateTime(year, month, 1);
var endDate = startDate.AddMonths(1).AddDays(-1);
var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
? DateTime.Now
: endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
// 获取门店列表
var storeIds = await GetStoreIdsAsync(input.BusinessUnitId, input.StoreIds, input.StatisticsMonth);
if (!storeIds.Any())
{
return new { total = 0, list = new List() };
}
// 获取门店信息，支持门店名称筛选
var storeQuery = _db.Queryable()
.Where(x => storeIds.Contains(x.Id));
if (!string.IsNullOrWhiteSpace(input.StoreName))
{
storeQuery = storeQuery.Where(x => x.Dm.Contains(input.StoreName));
}
var stores = await storeQuery.Select(x => new { x.Id, x.Dm }).ToListAsync();
var filteredStoreIds = stores.Select(x => x.Id).ToList();
if (!filteredStoreIds.Any())
{
return new { total = 0, list = new List() };
}
var storeDict = stores.ToDictionary(x => x.Id, x => x.Dm ?? "");
// 获取店长信息，支持店长姓名筛选
var managerQuery = _db.Queryable()
.Where(x => x.Gw == "店长" && filteredStoreIds.Contains(x.Mdid) && x.DeleteMark == null && x.EnabledMark == 1);
if (!string.IsNullOrWhiteSpace(input.StoreManagerName))
{
managerQuery = managerQuery.Where(x => x.RealName.Contains(input.StoreManagerName));
}
var storeManagers = await managerQuery.Select(x => new { x.Id, x.RealName, x.Mdid }).ToListAsync();
if (!storeManagers.Any())
{
return new { total = 0, list = new List() };
}
// 计算每个店长的业绩数据
var detailList = new List();
foreach (var manager in storeManagers)
{
var storeId = manager.Mdid;
if (string.IsNullOrEmpty(storeId)) continue;
// 开单业绩
var billingAmount = await _db.Queryable()
.Where(x => x.Djmd == storeId && x.IsEffective == 1)
.Where(x => x.Kdrq.HasValue && x.Kdrq.Value >= startDate && x.Kdrq.Value <= endDateTime)
.SumAsync(x => (decimal?)x.Sfyj) ?? 0m;
// 退卡金额
var refundAmount = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date)
.SumAsync(x => (decimal?)(x.ActualRefundAmount ?? x.Tkje ?? 0)) ?? 0m;
// 净业绩
var netPerformance = billingAmount - refundAmount;
// 消耗业绩
var consumeAmount = await _db.Queryable()
.Where(x => x.Md == storeId && x.IsEffective == 1)
.Where(x => x.Hksj.HasValue && x.Hksj.Value >= startDate && x.Hksj.Value <= endDateTime)
.SumAsync(x => (decimal?)x.Xfje) ?? 0m;
// 目标业绩
var targetPerformance = await _db.Queryable()
.Where(x => x.StoreId == storeId && x.Month == input.StatisticsMonth)
.SumAsync(x => (decimal?)x.BusinessUnitTarget) ?? 0m;
// 完成率
var completionRate = targetPerformance > 0 ? (netPerformance / targetPerformance * 100m) : 0m;
detailList.Add(new BusinessUnitDashboardStoreManagerDetailListOutput
{
StoreManagerId = manager.Id,
StoreManagerName = manager.RealName ?? "",
StoreId = storeId,
StoreName = storeDict.ContainsKey(storeId) ? storeDict[storeId] : "",
BillingPerformance = billingAmount,
ConsumePerformance = consumeAmount,
RefundAmount = refundAmount,
NetPerformance = netPerformance,
TargetPerformance = targetPerformance,
CompletionRate = completionRate
});
}
// 分页处理
var currentPage = input.currentPage > 0 ? input.currentPage : 1;
var pageSize = input.pageSize > 0 ? input.pageSize : 10;
var total = detailList.Count;
var pagedList = detailList.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
return new { total = total, list = pagedList };
}
catch (Exception ex)
{
_logger.LogError(ex, "查询事业部驾驶舱店长明细列表失败");
throw NCCException.Oh($"查询失败：{ex.Message}");
}
}
///
/// 获取事业部驾驶舱健康师明细列表
///
///
/// 获取指定事业部在指定月份的健康师明细列表，支持分页、排序、筛选
///
/// 查询参数
/// 事业部驾驶舱健康师明细列表
[HttpPost("GetHealthCoachDetailList")]
public async Task GetHealthCoachDetailList([FromBody] BusinessUnitDashboardHealthCoachDetailListInput input)
{
try
{
if (input == null) throw NCCException.Oh("请求参数不能为空");
if (string.IsNullOrWhiteSpace(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
throw NCCException.Oh("统计月份格式错误，必须为YYYYMM格式");
if (string.IsNullOrWhiteSpace(input.BusinessUnitId) && (input.StoreIds == null || !input.StoreIds.Any()))
throw NCCException.Oh("事业部ID和门店ID列表不能同时为空，必须传入其中一个");
// 解析月份获取时间范围
var year = int.Parse(input.StatisticsMonth.Substring(0, 4));
var month = int.Parse(input.StatisticsMonth.Substring(4, 2));
var startDate = new DateTime(year, month, 1);
var endDate = startDate.AddMonths(1).AddDays(-1);
var endDateTime = input.StatisticsMonth == DateTime.Now.ToString("yyyyMM")
? DateTime.Now
: endDate.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
// 获取门店列表
var storeIds = await GetStoreIdsAsync(input.BusinessUnitId, input.StoreIds, input.StatisticsMonth);
if (!storeIds.Any())
{
return new { total = 0, list = new List() };
}
// 获取门店信息，支持门店名称筛选
var storeQuery = _db.Queryable()
.Where(x => storeIds.Contains(x.Id));
if (!string.IsNullOrWhiteSpace(input.StoreName))
{
storeQuery = storeQuery.Where(x => x.Dm.Contains(input.StoreName));
}
var stores = await storeQuery.Select(x => new { x.Id, x.Dm }).ToListAsync();
var filteredStoreIds = stores.Select(x => x.Id).ToList();
if (!filteredStoreIds.Any())
{
return new { total = 0, list = new List() };
}
var storeDict = stores.ToDictionary(x => x.Id, x => x.Dm ?? "");
// 获取健康师信息，支持健康师姓名筛选
var coachQuery = _db.Queryable()
.Where(x => x.Gw == "健康师" && filteredStoreIds.Contains(x.Mdid) && x.DeleteMark == null && x.EnabledMark == 1);
if (!string.IsNullOrWhiteSpace(input.HealthCoachName))
{
coachQuery = coachQuery.Where(x => x.RealName.Contains(input.HealthCoachName));
}
var healthCoaches = await coachQuery.Select(x => new { x.Id, x.RealName, x.Mdid }).ToListAsync();
if (!healthCoaches.Any())
{
return new { total = 0, list = new List() };
}
// 计算每个健康师的业绩数据
var detailList = new List();
var storeIdsStr = string.Join("','", filteredStoreIds);
foreach (var coach in healthCoaches)
{
var storeId = coach.Mdid;
if (string.IsNullOrEmpty(storeId)) continue;
// 开单业绩（从lq_kd_jksyj表，jksyj字段是字符串类型，需要转换，使用jkszh字段匹配健康师ID）
var billingSql = $@"
SELECT COALESCE(SUM(CAST(jksyj AS DECIMAL(18,2))), 0) as Amount
FROM lq_kd_jksyj
WHERE F_IsEffective = 1
AND F_StoreId = '{storeId}'
AND jkszh = '{coach.Id}'
AND yjsj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
AND yjsj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";
var billingResult = await _db.Ado.SqlQueryAsync(billingSql);
var billingPerformance = billingResult?.FirstOrDefault() != null
? Convert.ToDecimal(billingResult.FirstOrDefault().Amount ?? 0)
: 0m;
// 消耗业绩（从lq_xh_jksyj表，使用jkszh字段匹配健康师ID）
var consumePerformance = await _db.Queryable()
.Where(x => x.StoreId == storeId && x.Jkszh == coach.Id && x.IsEffective == 1)
.Where(x => x.Yjsj.HasValue && x.Yjsj.Value >= startDate && x.Yjsj.Value <= endDateTime)
.SumAsync(x => (decimal?)(x.Jksyj ?? 0)) ?? 0m;
// 退卡业绩（从lq_hytk_jksyj表，使用jkszh字段匹配健康师ID）
var refundPerformance = await _db.Queryable()
.Where(x => x.StoreId == storeId && x.Jkszh == coach.Id && x.IsEffective == 1)
.Where(x => x.Tksj.HasValue && x.Tksj.Value.Date >= startDate.Date && x.Tksj.Value.Date <= endDate.Date)
.SumAsync(x => (decimal?)(x.Jksyj ?? 0)) ?? 0m;
// 总业绩
var totalPerformance = billingPerformance - refundPerformance;
// 项目数（从lq_xh_pxmx表，关联lq_xh_hyhk，再关联lq_xh_jksyj）
var projectCountSql = $@"
SELECT COALESCE(SUM(COALESCE(px.F_OriginalProjectNumber, px.F_ProjectNumber, 0)), 0) as ProjectCount
FROM lq_xh_pxmx px
INNER JOIN lq_xh_hyhk xh ON px.F_ConsumeInfoId = xh.F_Id
INNER JOIN lq_xh_jksyj jksyj ON jksyj.glkdbh = xh.F_Id
WHERE xh.Md = '{storeId}'
AND jksyj.jkszh = '{coach.Id}'
AND xh.F_IsEffective = 1
AND jksyj.F_IsEffective = 1
AND px.F_IsEffective = 1
AND xh.Hksj >= '{startDate:yyyy-MM-dd HH:mm:ss}'
AND xh.Hksj <= '{endDateTime:yyyy-MM-dd HH:mm:ss}'";
var projectCountResult = await _db.Ado.SqlQueryAsync(projectCountSql);
var projectCount = projectCountResult?.FirstOrDefault() != null
? Convert.ToDecimal(projectCountResult.FirstOrDefault().ProjectCount ?? 0)
: 0m;
detailList.Add(new BusinessUnitDashboardHealthCoachDetailListOutput
{
HealthCoachId = coach.Id,
HealthCoachName = coach.RealName ?? "",
StoreId = storeId,
StoreName = storeDict.ContainsKey(storeId) ? storeDict[storeId] : "",
BillingPerformance = billingPerformance,
ConsumePerformance = consumePerformance,
RefundPerformance = refundPerformance,
TotalPerformance = totalPerformance,
ProjectCount = projectCount
});
}
// 分页处理
var currentPage = input.currentPage > 0 ? input.currentPage : 1;
var pageSize = input.pageSize > 0 ? input.pageSize : 10;
var total = detailList.Count;
var pagedList = detailList.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
return new { total = total, list = pagedList };
}
catch (Exception ex)
{
_logger.LogError(ex, "查询事业部驾驶舱健康师明细列表失败");
throw NCCException.Oh($"查询失败：{ex.Message}");
}
}
}
}