using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using NCC.Common.Core.Manager;
using NCC.Common.Extension;
using NCC.Common.Filter;
using NCC.Common.Helper;
using NCC.Common.Model.NPOI;
using NCC.DataEncryption;
using NCC.Extend.Entitys.Dto.LqEducationDepartmentDashboard;
using NCC.Extend.Entitys.Enum;
using NCC.Extend.Entitys.lq_education_dashboard_featured_item;
using NCC.Extend.Entitys.lq_khxx;
using NCC.Extend.Entitys.lq_md_major_project_teacher_assignment;
using NCC.Extend.Entitys.lq_mdxx;
using NCC.Extend.Entitys.lq_xh_feedback;
using NCC.Extend.Entitys.lq_xh_hyhk;
using NCC.Extend.Entitys.lq_xmzl;
using NCC.FriendlyException;
using NCC.System.Entitys.Permission;
using SqlSugar;
using Yitter.IdGenerator;
namespace NCC.Extend
{
///
/// 教育部经营看板重点品项、客户与服务日志功能
///
public partial class LqEducationDepartmentDashboardService
{
private const string FeaturedItemBizType = "lq_education_dashboard_featured_item";
#region 重点品项配置
///
/// 分页查询教育部经营看板重点品项配置
///
///
/// 查询重点品项配置及原始生美品项信息。
///
/// 示例请求:
/// ```json
/// {
/// "currentPage": 1,
/// "pageSize": 20,
/// "keyword": "生命",
/// "enabled": 1
/// }
/// ```
///
/// 参数说明:
/// - keyword: 按原始品项名称、编号或显示名模糊搜索
/// - enabled: 0禁用,1启用
///
/// 分页查询条件
/// 重点品项配置分页列表
/// 成功返回配置列表
/// 参数错误
/// 服务器错误
[HttpPost("GetFeaturedItemConfigList")]
public async Task GetFeaturedItemConfigList(
[FromBody] EducationDashboardFeaturedItemListInput input)
{
input ??= new EducationDashboardFeaturedItemListInput();
var keyword = string.IsNullOrWhiteSpace(input.keyword) ? null : input.keyword.Trim();
var page = await _db.Queryable(
(config, item) => config.ItemId == item.Id)
.WhereIF(input.Enabled.HasValue, (config, item) => config.Enabled == input.Enabled.Value)
.WhereIF(keyword != null, (config, item) =>
config.DisplayName.Contains(keyword) ||
item.Xmmc.Contains(keyword) ||
item.Xmbh.Contains(keyword))
.OrderBy((config, item) => config.Sort)
.OrderBy((config, item) => config.CreateTime, OrderByType.Desc)
.Select((config, item) => new EducationDashboardFeaturedItemListOutput
{
Id = config.Id,
ItemId = config.ItemId,
ItemCode = item.Xmbh,
OriginalItemName = item.Xmmc,
ItemCategory = item.Qt2,
DisplayName = config.DisplayName,
Sort = config.Sort,
Enabled = config.Enabled,
CreateTime = config.CreateTime,
UpdateTime = config.UpdateTime
})
.ToPagedListAsync(input.currentPage, input.pageSize);
return PageResult.SqlSugarPageResult(page);
}
///
/// 搜索可配置的生美品项
///
///
/// 仅搜索有效且 lq_xmzl.qt2=生美 的原始品项,可排除已配置品项。
///
/// 示例请求:
/// ```json
/// {
/// "currentPage": 1,
/// "pageSize": 20,
/// "keyword": "生命",
/// "excludeConfigured": true
/// }
/// ```
///
/// 品项搜索条件
/// 生美品项分页列表
/// 成功返回品项列表
/// 参数错误
/// 服务器错误
[HttpPost("SearchLifeBeautyItems")]
public async Task SearchLifeBeautyItems(
[FromBody] EducationDashboardLifeBeautyItemSearchInput input)
{
input ??= new EducationDashboardLifeBeautyItemSearchInput();
var keyword = string.IsNullOrWhiteSpace(input.keyword) ? null : input.keyword.Trim();
var configuredItemIds = await _db.Queryable()
.Select(x => x.ItemId)
.ToListAsync();
var page = await _db.Queryable()
.Where(x => x.IsEffective == 1 && x.Qt2 == LifeBeautyCategory)
.WhereIF(input.ExcludeConfigured && configuredItemIds.Any(),
x => !configuredItemIds.Contains(x.Id))
.WhereIF(keyword != null, x => x.Xmmc.Contains(keyword) || x.Xmbh.Contains(keyword))
.OrderBy(x => x.Xmmc)
.Select(x => new EducationDashboardLifeBeautyItemOptionOutput
{
ItemId = x.Id,
ItemCode = x.Xmbh,
ItemName = x.Xmmc,
ItemCategory = x.Qt2,
Configured = configuredItemIds.Contains(x.Id)
})
.ToPagedListAsync(input.currentPage, input.pageSize);
return PageResult.SqlSugarPageResult(page);
}
///
/// 保存教育部经营看板重点品项配置
///
///
/// ID 为空时新增,否则编辑;同一原始品项只能配置一次。
///
/// 示例请求:
/// ```json
/// {
/// "id": "",
/// "itemId": "品项ID",
/// "displayName": "生命之波",
/// "sort": 10,
/// "enabled": 1
/// }
/// ```
///
/// 保存参数
/// 配置ID
/// 保存成功
/// 参数或唯一性校验失败
/// 服务器错误
[HttpPost("SaveFeaturedItemConfig")]
public async Task SaveFeaturedItemConfig(
[FromBody] EducationDashboardFeaturedItemSaveInput input)
{
ValidateFeaturedItemSaveInput(input);
var item = await GetLifeBeautyItemAsync(input.ItemId);
var duplicate = await _db.Queryable()
.Where(x => x.ItemId == input.ItemId)
.WhereIF(!string.IsNullOrWhiteSpace(input.Id), x => x.Id != input.Id)
.AnyAsync();
if (duplicate) throw NCCException.Oh("该生美品项已配置,请勿重复添加");
if (string.IsNullOrWhiteSpace(input.Id))
{
var entity = new LqEducationDashboardFeaturedItemEntity
{
Id = YitIdHelper.NextId().ToString(),
ItemId = input.ItemId.Trim(),
DisplayName = input.DisplayName.Trim(),
Sort = input.Sort,
Enabled = input.Enabled,
CreateUserId = _userManager.UserId,
CreateTime = DateTime.Now
};
await _db.Insertable(entity).ExecuteCommandAsync();
await WriteConfigLogAsync("新增重点品项", entity.Id, null, entity,
$"新增重点品项配置:{entity.DisplayName}({item.Xmmc})");
return new { id = entity.Id };
}
var existing = await _db.Queryable()
.Where(x => x.Id == input.Id)
.FirstAsync();
if (existing == null) throw NCCException.Oh("重点品项配置不存在");
var before = SnapshotFeaturedItem(existing);
existing.ItemId = input.ItemId.Trim();
existing.DisplayName = input.DisplayName.Trim();
existing.Sort = input.Sort;
existing.Enabled = input.Enabled;
existing.UpdateUserId = _userManager.UserId;
existing.UpdateTime = DateTime.Now;
await _db.Updateable(existing).ExecuteCommandAsync();
await WriteConfigLogAsync("修改重点品项", existing.Id, before, existing,
$"修改重点品项配置:{existing.DisplayName}({item.Xmmc})");
return new { id = existing.Id };
}
///
/// 删除教育部经营看板重点品项配置
///
///
/// 物理删除指定配置,不影响原始品项资料。
///
/// 示例请求:`DELETE /api/Extend/LqEducationDepartmentDashboard/DeleteFeaturedItemConfig/{id}`
///
/// 配置ID
/// 删除结果
/// 删除成功
/// 配置不存在
/// 服务器错误
[HttpDelete("DeleteFeaturedItemConfig/{id}")]
public async Task DeleteFeaturedItemConfig([FromRoute] string id)
{
if (string.IsNullOrWhiteSpace(id)) throw NCCException.Oh("配置ID不能为空");
var entity = await _db.Queryable()
.Where(x => x.Id == id)
.FirstAsync();
if (entity == null) throw NCCException.Oh("重点品项配置不存在");
await _db.Deleteable()
.Where(x => x.Id == id)
.ExecuteCommandAsync();
await WriteConfigLogAsync("删除重点品项", entity.Id, entity, null,
$"删除重点品项配置:{entity.DisplayName}");
}
///
/// 变更教育部经营看板重点品项启用状态
///
///
/// 启用或禁用指定重点品项。
///
/// 示例请求:
/// ```json
/// {
/// "id": "配置ID",
/// "enabled": 1
/// }
/// ```
///
/// 状态变更参数
/// 状态变更结果
/// 状态变更成功
/// 参数错误或配置不存在
/// 服务器错误
[HttpPut("ChangeFeaturedItemStatus")]
public async Task ChangeFeaturedItemStatus(
[FromBody] EducationDashboardFeaturedItemStatusInput input)
{
if (input == null || string.IsNullOrWhiteSpace(input.Id))
throw NCCException.Oh("配置ID不能为空");
if (!Enum.IsDefined(typeof(EducationDashboardFeaturedItemStatus), input.Enabled))
throw NCCException.Oh("启用状态无效");
var entity = await _db.Queryable()
.Where(x => x.Id == input.Id)
.FirstAsync();
if (entity == null) throw NCCException.Oh("重点品项配置不存在");
var before = SnapshotFeaturedItem(entity);
entity.Enabled = input.Enabled;
entity.UpdateUserId = _userManager.UserId;
entity.UpdateTime = DateTime.Now;
await _db.Updateable(entity)
.UpdateColumns(x => new { x.Enabled, x.UpdateUserId, x.UpdateTime })
.ExecuteCommandAsync();
var action = input.Enabled == EducationDashboardFeaturedItemStatus.Enabled
? "启用重点品项"
: "禁用重点品项";
await WriteConfigLogAsync(action, entity.Id, before, entity,
$"{action}:{entity.DisplayName}");
}
private static void ValidateFeaturedItemSaveInput(EducationDashboardFeaturedItemSaveInput input)
{
if (input == null) throw NCCException.Oh("请求参数不能为空");
if (string.IsNullOrWhiteSpace(input.ItemId)) throw NCCException.Oh("品项ID不能为空");
if (string.IsNullOrWhiteSpace(input.DisplayName)) throw NCCException.Oh("显示名不能为空");
if (input.DisplayName.Trim().Length > 200) throw NCCException.Oh("显示名不能超过200个字符");
if (input.Sort < 0) throw NCCException.Oh("排序号不能小于0");
if (!Enum.IsDefined(typeof(EducationDashboardFeaturedItemStatus), input.Enabled))
throw NCCException.Oh("启用状态无效");
}
private async Task GetLifeBeautyItemAsync(string itemId)
{
var item = await _db.Queryable()
.Where(x => x.Id == itemId && x.IsEffective == 1 && x.Qt2 == LifeBeautyCategory)
.FirstAsync();
if (item == null) throw NCCException.Oh("所选品项不存在、已失效或不属于生美");
return item;
}
private static object SnapshotFeaturedItem(LqEducationDashboardFeaturedItemEntity entity)
{
if (entity == null) return null;
return new
{
entity.Id,
entity.ItemId,
entity.DisplayName,
entity.Sort,
entity.Enabled,
entity.CreateUserId,
entity.CreateTime,
entity.UpdateUserId,
entity.UpdateTime
};
}
private async Task WriteConfigLogAsync(
string actionCode,
string bizId,
object before,
object after,
string summary)
{
await _businessOperationLogService.WriteAsync(
BizLogModule,
actionCode,
FeaturedItemBizType,
bizId,
summary,
JsonConvert.SerializeObject(new { before, after }));
}
#endregion
#region 重点品项与客户统计
///
/// 获取教育部经营看板重点品项统计
///
///
/// 按已启用配置汇总开单金额、耗卡金额、主单次数及环比同比,不硬编码品项。
///
/// 示例请求:
/// ```json
/// {
/// "educationDepartmentId": "734725416912160005",
/// "statisticsMonth": "202604"
/// }
/// ```
///
/// 公共看板筛选条件
/// 重点品项统计
/// 成功返回统计结果
/// 参数错误
/// 服务器错误
[HttpPost("GetFeaturedItemStatistics")]
public async Task GetFeaturedItemStatistics(
[FromBody] EducationDepartmentDashboardStatisticsInput input)
{
ValidateStatisticsInput(input);
var configs = await LoadEnabledFeaturedItemsAsync();
if (!configs.Any()) return new EducationDepartmentFeaturedItemStatisticsOutput();
var currentMonth = DateTime.ParseExact(input.StatisticsMonth, "yyyyMM", null);
var lastMonth = currentMonth.AddMonths(-1).ToString("yyyyMM");
var lastYearMonth = currentMonth.AddYears(-1).ToString("yyyyMM");
var currentStores = await GetStoreIdsAsync(input.EducationDepartmentId, input.StoreIds, input.StatisticsMonth);
var lastMonthStores = await GetStoreIdsForMonthAsync(input.EducationDepartmentId, input.StoreIds, lastMonth);
var lastYearStores = await GetStoreIdsForMonthAsync(input.EducationDepartmentId, input.StoreIds, lastYearMonth);
var current = await BuildMonthlySnapshotAsync(currentStores, input.StatisticsMonth);
var previous = await BuildMonthlySnapshotAsync(lastMonthStores, lastMonth);
var lastYear = await BuildMonthlySnapshotAsync(lastYearStores, lastYearMonth);
var result = new EducationDepartmentFeaturedItemStatisticsOutput();
foreach (var config in configs)
{
var currentBilling = current.SourceData.BillingRows.Where(x => x.ItemId == config.ItemId).ToList();
var currentConsume = current.SourceData.ConsumeRows.Where(x => x.ItemId == config.ItemId).ToList();
var previousBilling = previous.SourceData.BillingRows.Where(x => x.ItemId == config.ItemId).ToList();
var previousConsume = previous.SourceData.ConsumeRows.Where(x => x.ItemId == config.ItemId).ToList();
var lastYearBilling = lastYear.SourceData.BillingRows.Where(x => x.ItemId == config.ItemId).ToList();
var lastYearConsume = lastYear.SourceData.ConsumeRows.Where(x => x.ItemId == config.ItemId).ToList();
var billingAmount = currentBilling.Sum(x => x.Amount);
var consumeAmount = currentConsume.Sum(x => x.Amount);
var previousBillingAmount = previousBilling.Sum(x => x.Amount);
var previousConsumeAmount = previousConsume.Sum(x => x.Amount);
var lastYearBillingAmount = lastYearBilling.Sum(x => x.Amount);
var lastYearConsumeAmount = lastYearConsume.Sum(x => x.Amount);
result.Items.Add(new EducationDepartmentFeaturedItemStatisticsItem
{
ConfigId = config.Id,
ItemId = config.ItemId,
OriginalItemName = config.OriginalItemName,
ItemCategory = config.ItemCategory,
DisplayName = config.DisplayName,
Sort = config.Sort,
BillingAmount = billingAmount,
BillingCount = currentBilling.Select(x => x.BillId).Distinct().Count(),
ConsumeAmount = consumeAmount,
ConsumeCount = currentConsume.Select(x => x.ConsumeId).Distinct().Count(),
BillingMonthOverMonthRate = CalculateGrowthRate(billingAmount, previousBillingAmount),
BillingYearOverYearRate = CalculateGrowthRate(billingAmount, lastYearBillingAmount),
ConsumeMonthOverMonthRate = CalculateGrowthRate(consumeAmount, previousConsumeAmount),
ConsumeYearOverYearRate = CalculateGrowthRate(consumeAmount, lastYearConsumeAmount)
});
}
return result;
}
///
/// 获取教育部经营看板客户消费频次
///
///
/// 按生美品项统计耗卡客户数、耗卡主单次数和人均频次;配置显示名仅作为附加展示名。
///
/// 示例请求:
/// ```json
/// {
/// "educationDepartmentId": "734725416912160005",
/// "statisticsMonth": "202604"
/// }
/// ```
///
/// 公共看板筛选条件
/// 客户频次统计
/// 成功返回统计结果
/// 参数错误
/// 服务器错误
[HttpPost("GetCustomerFrequencyStatistics")]
public async Task GetCustomerFrequencyStatistics(
[FromBody] EducationDepartmentDashboardStatisticsInput input)
{
ValidateStatisticsInput(input);
var storeIds = await GetStoreIdsAsync(input.EducationDepartmentId, input.StoreIds, input.StatisticsMonth);
if (!storeIds.Any()) return new EducationDepartmentCustomerFrequencyStatisticsOutput();
var snapshot = await BuildMonthlySnapshotAsync(storeIds, input.StatisticsMonth);
var configMap = (await LoadEnabledFeaturedItemsAsync())
.ToDictionary(x => x.ItemId, x => x.DisplayName);
var items = snapshot.SourceData.ConsumeRows
.GroupBy(x => new { x.ItemId, x.ItemName, x.ItemCategory })
.Select(g =>
{
var customerCount = g.Select(x => x.MemberId)
.Where(x => !string.IsNullOrWhiteSpace(x))
.Distinct()
.Count();
var consumeCount = g.Select(x => x.ConsumeId).Distinct().Count();
return new EducationDepartmentCustomerFrequencyItem
{
ItemId = g.Key.ItemId,
OriginalItemName = g.Key.ItemName,
ItemCategory = g.Key.ItemCategory,
DisplayName = configMap.TryGetValue(g.Key.ItemId, out var displayName)
? displayName
: string.Empty,
CustomerCount = customerCount,
TotalConsumeCount = consumeCount,
AverageFrequency = customerCount > 0
? decimal.Round((decimal)consumeCount / customerCount, 2)
: 0m
};
})
.OrderByDescending(x => x.TotalConsumeCount)
.ToList();
return new EducationDepartmentCustomerFrequencyStatisticsOutput { Items = items };
}
///
/// 获取教育部经营看板客户维度汇总
///
///
/// 返回教育部会员、新增会员、生美开单会员、生美耗卡会员和去重开单主单数。
///
/// 示例请求:
/// ```json
/// {
/// "educationDepartmentId": "734725416912160005",
/// "statisticsMonth": "202604"
/// }
/// ```
///
/// 公共看板筛选条件
/// 客户维度汇总
/// 成功返回汇总
/// 参数错误
/// 服务器错误
[HttpPost("GetCustomerDimensionStatistics")]
public async Task GetCustomerDimensionStatistics(
[FromBody] EducationDepartmentDashboardStatisticsInput input)
{
ValidateStatisticsInput(input);
var storeIds = await GetStoreIdsAsync(input.EducationDepartmentId, input.StoreIds, input.StatisticsMonth);
if (!storeIds.Any()) return new EducationDepartmentCustomerDimensionStatisticsOutput();
var (startDate, endDateTime) = GetMonthRange(input.StatisticsMonth);
var snapshot = await BuildMonthlySnapshotAsync(storeIds, input.StatisticsMonth);
var (memberCount, newMemberCount) =
await QueryEducationMemberStatsAsync(storeIds, startDate, endDateTime);
return new EducationDepartmentCustomerDimensionStatisticsOutput
{
EducationMemberCount = memberCount,
NewEducationMemberCount = newMemberCount,
BillingMemberCount = snapshot.Metrics.BillingMemberCount,
ConsumeMemberCount = snapshot.Metrics.ConsumeMemberCount,
BillingCount = snapshot.Metrics.BillingCount
};
}
///
/// 分页查询教育部经营看板客户明细
///
///
/// 查询教育部会员及当月发生生美开单/耗卡/退卡的客户,返回统一业务口径的客户明细。
///
/// 示例请求:
/// ```json
/// {
/// "educationDepartmentId": "734725416912160005",
/// "statisticsMonth": "202604",
/// "currentPage": 1,
/// "pageSize": 20,
/// "memberName": "张"
/// }
/// ```
///
/// 分页筛选条件
/// 客户明细分页列表
/// 成功返回客户明细
/// 参数错误
/// 服务器错误
[HttpPost("GetCustomerDetailList")]
public async Task GetCustomerDetailList(
[FromBody] EducationDepartmentCustomerDetailListInput input)
{
var (list, total) = await QueryCustomerDetailListAsync(input, true);
return PageResult.SqlSugarPageResult(
new SqlSugarPagedList
{
list = list,
pagination = new PagedModel
{
PageIndex = NormalizePageIndex(input.currentPage),
PageSize = NormalizePageSize(input.pageSize),
Total = total
}
});
}
#endregion
#region 服务日志
///
/// 获取教育部经营看板服务日志统计
///
///
/// 仅统计 lq_xh_feedback,并通过 F_ConsumeId 关联有效生美耗卡记录和门店。
/// 教育老师仅统计本人创建且属于本人当月负责门店的日志。
///
/// 示例请求:
/// ```json
/// {
/// "educationDepartmentId": "734725416912160005",
/// "statisticsMonth": "202604"
/// }
/// ```
///
/// 公共看板筛选条件
/// 日志数、覆盖率和前后照片完整率
/// 成功返回统计结果
/// 参数错误
/// 服务器错误
[HttpPost("GetServiceLogStatistics")]
public async Task GetServiceLogStatistics(
[FromBody] EducationDepartmentDashboardStatisticsInput input)
{
ValidateStatisticsInput(input);
var storeIds = await GetStoreIdsAsync(input.EducationDepartmentId, input.StoreIds, input.StatisticsMonth);
if (!storeIds.Any()) return new EducationDepartmentServiceLogStatisticsOutput();
var snapshot = await BuildMonthlySnapshotAsync(storeIds, input.StatisticsMonth);
var consumeIds = snapshot.SourceData.ConsumeRows
.Select(x => x.ConsumeId)
.Where(x => !string.IsNullOrWhiteSpace(x))
.Distinct()
.ToList();
if (!consumeIds.Any()) return new EducationDepartmentServiceLogStatisticsOutput();
var (startDate, endDateTime) = GetMonthRange(input.StatisticsMonth);
var isEducationTeacher = await IsCurrentEducationTeacherAsync(input.StatisticsMonth);
var logs = await _db.Queryable()
.Where(x => consumeIds.Contains(x.ConsumeId))
.Where(x => x.CreateTime >= startDate && x.CreateTime <= endDateTime)
.WhereIF(isEducationTeacher, x => x.CreateUser == _userManager.UserId)
.Select(x => new ServiceLogStatisticRow
{
ConsumeId = x.ConsumeId,
BeforeImage = x.BeforeImage,
AfterImage = x.AfterImage
})
.ToListAsync();
var coveredConsumeCount = logs.Select(x => x.ConsumeId).Distinct().Count();
var completePhotoCount = logs.Count(x =>
!string.IsNullOrWhiteSpace(x.BeforeImage) &&
!string.IsNullOrWhiteSpace(x.AfterImage));
return new EducationDepartmentServiceLogStatisticsOutput
{
ServiceLogCount = logs.Count,
LifeBeautyConsumeCount = consumeIds.Count,
CoveredConsumeCount = coveredConsumeCount,
CoverageRate = consumeIds.Count > 0
? decimal.Round((decimal)coveredConsumeCount / consumeIds.Count * 100m, 2)
: 0m,
CompletePhotoLogCount = completePhotoCount,
CompletePhotoRate = logs.Count > 0
? decimal.Round((decimal)completePhotoCount / logs.Count * 100m, 2)
: 0m
};
}
///
/// 分页查询教育部经营看板服务日志
///
///
/// 仅查询关联有效生美耗卡记录的 lq_xh_feedback。
/// 教育老师仅可查看本人创建且属于本人负责门店的日志。
///
/// 示例请求:
/// ```json
/// {
/// "educationDepartmentId": "734725416912160005",
/// "statisticsMonth": "202604",
/// "currentPage": 1,
/// "pageSize": 20,
/// "hasCompletePhotos": true
/// }
/// ```
///
/// 分页筛选条件
/// 服务日志分页列表
/// 成功返回日志明细
/// 参数错误
/// 服务器错误
[HttpPost("GetServiceLogList")]
public async Task GetServiceLogList(
[FromBody] EducationDepartmentServiceLogListInput input)
{
var (list, total) = await QueryServiceLogListAsync(input, true);
return PageResult.SqlSugarPageResult(
new SqlSugarPagedList
{
list = list,
pagination = new PagedModel
{
PageIndex = NormalizePageIndex(input.currentPage),
PageSize = NormalizePageSize(input.pageSize),
Total = total
}
});
}
#endregion
#region 新增导出
///
/// 导出教育部经营看板门店明细
///
///
/// 导出条件与 GetStoreDetailList 完全一致,成功后写中文业务操作日志。
///
/// 门店明细筛选条件
/// Excel 下载信息
/// 成功返回下载链接
/// 参数错误
/// 服务器错误
[HttpPost("ExportStoreDetailList")]
public async Task ExportStoreDetailList(
[FromBody] EducationDepartmentDashboardStoreDetailListInput input)
{
var list = await QueryStoreDetailDataAsync(input);
return await ExportFeaturedDataAsync(
list,
"导出门店明细",
"lq_md_target",
input,
"[{\"value\":\"门店编码\",\"field\":\"storeCode\"},{\"value\":\"门店名称\",\"field\":\"storeName\"}," +
"{\"value\":\"开单金额\",\"field\":\"billingAmount\"},{\"value\":\"退卡金额\",\"field\":\"refundAmount\"}," +
"{\"value\":\"净业绩\",\"field\":\"netPerformance\"},{\"value\":\"储扣金额\",\"field\":\"deductAmount\"}," +
"{\"value\":\"耗卡金额\",\"field\":\"consumeAmount\"},{\"value\":\"目标业绩\",\"field\":\"targetPerformance\"}," +
"{\"value\":\"完成率\",\"field\":\"completionRate\"},{\"value\":\"开单次数\",\"field\":\"billingCount\"}," +
"{\"value\":\"耗卡次数\",\"field\":\"consumeCount\"},{\"value\":\"退卡次数\",\"field\":\"refundCount\"}," +
"{\"value\":\"耗卡人头\",\"field\":\"consumeMemberCount\"},{\"value\":\"项目数\",\"field\":\"projectCount\"}]");
}
///
/// 导出教育部经营看板客户明细
///
///
/// 导出条件与 GetCustomerDetailList 完全一致,成功后写中文业务操作日志。
///
/// 客户明细筛选条件
/// Excel 下载信息
/// 成功返回下载链接
/// 参数错误
/// 服务器错误
[HttpPost("ExportCustomerDetailList")]
public async Task ExportCustomerDetailList(
[FromBody] EducationDepartmentCustomerDetailListInput input)
{
var (list, _) = await QueryCustomerDetailListAsync(input, false);
return await ExportFeaturedDataAsync(
list,
"导出客户明细",
"lq_khxx",
input,
"[{\"value\":\"会员姓名\",\"field\":\"memberName\"},{\"value\":\"手机号\",\"field\":\"mobile\"}," +
"{\"value\":\"门店\",\"field\":\"storeName\"},{\"value\":\"是否教育部会员\",\"field\":\"isEducationMember\"}," +
"{\"value\":\"成为教育部会员时间\",\"field\":\"educationMemberTime\"},{\"value\":\"开单次数\",\"field\":\"billingCount\"}," +
"{\"value\":\"开单金额\",\"field\":\"billingAmount\"},{\"value\":\"耗卡次数\",\"field\":\"consumeCount\"}," +
"{\"value\":\"耗卡金额\",\"field\":\"consumeAmount\"},{\"value\":\"退卡次数\",\"field\":\"refundCount\"}," +
"{\"value\":\"退卡金额\",\"field\":\"refundAmount\"},{\"value\":\"最近耗卡日\",\"field\":\"lastConsumeDate\"}]");
}
///
/// 导出教育部经营看板服务日志
///
///
/// 导出条件与 GetServiceLogList 完全一致,并沿用教育老师本人/负责门店权限。
///
/// 服务日志筛选条件
/// Excel 下载信息
/// 成功返回下载链接
/// 参数错误
/// 服务器错误
[HttpPost("ExportServiceLogList")]
public async Task ExportServiceLogList(
[FromBody] EducationDepartmentServiceLogListInput input)
{
var (list, _) = await QueryServiceLogListAsync(input, false);
return await ExportFeaturedDataAsync(
list,
"导出服务日志",
"lq_xh_feedback",
input,
"[{\"value\":\"门店\",\"field\":\"storeName\"},{\"value\":\"会员\",\"field\":\"memberName\"}," +
"{\"value\":\"老师\",\"field\":\"createUserName\"},{\"value\":\"备注\",\"field\":\"remark\"}," +
"{\"value\":\"前照片\",\"field\":\"beforeImage\"},{\"value\":\"后照片\",\"field\":\"afterImage\"}," +
"{\"value\":\"照片完整\",\"field\":\"hasCompletePhotos\"},{\"value\":\"创建时间\",\"field\":\"createTime\"}]");
}
#endregion
#region 私有查询
private async Task> LoadEnabledFeaturedItemsAsync()
{
return await _db.Queryable(
(config, item) => config.ItemId == item.Id)
.Where((config, item) =>
config.Enabled == EducationDashboardFeaturedItemStatus.Enabled &&
item.IsEffective == 1 &&
item.Qt2 == LifeBeautyCategory)
.OrderBy((config, item) => config.Sort)
.Select((config, item) => new FeaturedItemConfigRow
{
Id = config.Id,
ItemId = config.ItemId,
OriginalItemName = item.Xmmc,
ItemCategory = item.Qt2,
DisplayName = config.DisplayName,
Sort = config.Sort
})
.ToListAsync();
}
private async Task IsCurrentEducationTeacherAsync(string statisticsMonth)
{
var year = statisticsMonth.Substring(0, 4);
var month = statisticsMonth.Substring(4, 2);
return await _db.Queryable()
.Where(x => x.Year == year &&
x.Month == month &&
x.EducationTeacherId == _userManager.UserId)
.AnyAsync();
}
private async Task<(List list, int total)>
QueryCustomerDetailListAsync(EducationDepartmentCustomerDetailListInput input, bool paged)
{
if (input == null) throw NCCException.Oh("请求参数不能为空");
ValidateStatisticsInput(ToStatisticsInput(input));
var storeIds = await GetStoreIdsAsync(input.EducationDepartmentId, input.StoreIds, input.StatisticsMonth);
if (!storeIds.Any()) return (new List(), 0);
var snapshot = await BuildMonthlySnapshotAsync(storeIds, input.StatisticsMonth);
var activityMemberIds = snapshot.SourceData.BillingRows.Select(x => x.MemberId)
.Concat(snapshot.SourceData.ConsumeRows.Select(x => x.MemberId))
.Concat(snapshot.SourceData.RefundRows.Select(x => x.MemberId))
.Where(x => !string.IsNullOrWhiteSpace(x))
.Distinct()
.ToList();
var keyword = string.IsNullOrWhiteSpace(input.keyword) ? null : input.keyword.Trim();
var query = _db.Queryable(
(member, store) => new JoinQueryInfos(JoinType.Left, member.Gsmd == store.Id))
.Where((member, store) =>
storeIds.Contains(member.Gsmd) &&
(member.IsEducationMember == 1 || activityMemberIds.Contains(member.Id)))
.WhereIF(!string.IsNullOrWhiteSpace(input.StoreId), (member, store) => member.Gsmd == input.StoreId)
.WhereIF(!string.IsNullOrWhiteSpace(input.MemberName), (member, store) =>
member.Khmc.Contains(input.MemberName))
.WhereIF(!string.IsNullOrWhiteSpace(input.Mobile), (member, store) =>
member.Sjh.Contains(input.Mobile))
.WhereIF(input.IsEducationMember.HasValue, (member, store) =>
member.IsEducationMember == input.IsEducationMember.Value)
.WhereIF(keyword != null, (member, store) =>
member.Khmc.Contains(keyword) ||
member.Sjh.Contains(keyword) ||
store.Dm.Contains(keyword))
.OrderBy((member, store) => member.EducationMemberTime, OrderByType.Desc)
.Select((member, store) => new EducationDepartmentCustomerDetailListOutput
{
MemberId = member.Id,
MemberName = member.Khmc,
Mobile = member.Sjh,
StoreId = member.Gsmd,
StoreName = store.Dm,
IsEducationMember = member.IsEducationMember,
EducationMemberTime = member.EducationMemberTime
});
List members;
int total;
if (paged)
{
var page = await query.ToPagedListAsync(
NormalizePageIndex(input.currentPage),
NormalizePageSize(input.pageSize));
members = page.list.ToList();
total = page.pagination.Total;
}
else
{
members = await query.ToListAsync();
total = members.Count;
}
foreach (var member in members)
{
var billing = snapshot.SourceData.BillingRows.Where(x => x.MemberId == member.MemberId).ToList();
var consume = snapshot.SourceData.ConsumeRows.Where(x => x.MemberId == member.MemberId).ToList();
var refund = snapshot.SourceData.RefundRows.Where(x => x.MemberId == member.MemberId).ToList();
member.BillingCount = billing.Select(x => x.BillId).Distinct().Count();
member.BillingAmount = billing.Sum(x => x.Amount);
member.ConsumeCount = consume.Select(x => x.ConsumeId).Distinct().Count();
member.ConsumeAmount = consume.Sum(x => x.Amount);
member.RefundCount = refund.Select(x => x.RefundId).Distinct().Count();
member.RefundAmount = refund.Sum(x => x.Amount);
member.LastConsumeDate = consume.Any()
? consume.Max(x => x.ConsumeDate)
: null;
}
return (members, total);
}
private async Task<(List list, int total)>
QueryServiceLogListAsync(EducationDepartmentServiceLogListInput input, bool paged)
{
if (input == null) throw NCCException.Oh("请求参数不能为空");
ValidateStatisticsInput(ToStatisticsInput(input));
var storeIds = await GetStoreIdsAsync(input.EducationDepartmentId, input.StoreIds, input.StatisticsMonth);
if (!storeIds.Any()) return (new List(), 0);
var snapshot = await BuildMonthlySnapshotAsync(storeIds, input.StatisticsMonth);
var consumeIds = snapshot.SourceData.ConsumeRows.Select(x => x.ConsumeId)
.Where(x => !string.IsNullOrWhiteSpace(x))
.Distinct()
.ToList();
if (!consumeIds.Any()) return (new List(), 0);
var (startDate, endDateTime) = GetMonthRange(input.StatisticsMonth);
var isEducationTeacher = await IsCurrentEducationTeacherAsync(input.StatisticsMonth);
var keyword = string.IsNullOrWhiteSpace(input.keyword) ? null : input.keyword.Trim();
var query = _db.Queryable<
LqXhFeedbackEntity,
LqXhHyhkEntity,
LqKhxxEntity,
LqMdxxEntity,
UserEntity>((log, consume, member, store, user) => new JoinQueryInfos(
JoinType.Inner, log.ConsumeId == consume.Id,
JoinType.Left, log.MemberId == member.Id,
JoinType.Left, consume.Md == store.Id,
JoinType.Left, log.CreateUser == user.Id))
.Where((log, consume, member, store, user) =>
consumeIds.Contains(log.ConsumeId) &&
storeIds.Contains(consume.Md) &&
log.CreateTime >= startDate &&
log.CreateTime <= endDateTime)
.WhereIF(isEducationTeacher, (log, consume, member, store, user) =>
log.CreateUser == _userManager.UserId)
.WhereIF(!string.IsNullOrWhiteSpace(input.StoreId), (log, consume, member, store, user) =>
consume.Md == input.StoreId)
.WhereIF(!string.IsNullOrWhiteSpace(input.MemberName), (log, consume, member, store, user) =>
member.Khmc.Contains(input.MemberName))
.WhereIF(!string.IsNullOrWhiteSpace(input.CreateUserName), (log, consume, member, store, user) =>
user.RealName.Contains(input.CreateUserName))
.WhereIF(input.HasCompletePhotos == true, (log, consume, member, store, user) =>
log.BeforeImage != null && log.BeforeImage != "" &&
log.AfterImage != null && log.AfterImage != "")
.WhereIF(input.HasCompletePhotos == false, (log, consume, member, store, user) =>
log.BeforeImage == null || log.BeforeImage == "" ||
log.AfterImage == null || log.AfterImage == "")
.WhereIF(keyword != null, (log, consume, member, store, user) =>
member.Khmc.Contains(keyword) ||
store.Dm.Contains(keyword) ||
user.RealName.Contains(keyword) ||
log.Remark.Contains(keyword))
.OrderBy((log, consume, member, store, user) => log.CreateTime, OrderByType.Desc)
.Select((log, consume, member, store, user) => new EducationDepartmentServiceLogListOutput
{
ServiceLogId = log.Id,
ConsumeId = log.ConsumeId,
StoreId = consume.Md,
StoreName = store.Dm,
MemberId = log.MemberId,
MemberName = member.Khmc,
CreateUserId = log.CreateUser,
CreateUserName = user.RealName,
Remark = log.Remark,
BeforeImage = log.BeforeImage,
AfterImage = log.AfterImage,
HasCompletePhotos =
log.BeforeImage != null && log.BeforeImage != "" &&
log.AfterImage != null && log.AfterImage != "",
CreateTime = log.CreateTime
});
if (paged)
{
var page = await query.ToPagedListAsync(
NormalizePageIndex(input.currentPage),
NormalizePageSize(input.pageSize));
return (page.list.ToList(), page.pagination.Total);
}
var list = await query.ToListAsync();
return (list, list.Count);
}
private async Task>
QueryStoreDetailDataAsync(EducationDepartmentDashboardStoreDetailListInput input)
{
if (input == null) throw NCCException.Oh("请求参数不能为空");
ValidateStatisticsInput(new EducationDepartmentDashboardStatisticsInput
{
EducationDepartmentId = input.EducationDepartmentId,
StoreIds = input.StoreIds,
StatisticsMonth = input.StatisticsMonth
});
var storeIds = await GetStoreIdsAsync(input.EducationDepartmentId, input.StoreIds, input.StatisticsMonth);
if (!storeIds.Any()) return new List();
var snapshot = await BuildMonthlySnapshotAsync(storeIds, input.StatisticsMonth);
var targetByStore = await GetTargetByStoreAsync(storeIds, input.StatisticsMonth);
var deductByStore = await LoadDeductByStoreAsync(storeIds, input.StatisticsMonth);
var storeQuery = _db.Queryable()
.Where(x => storeIds.Contains(x.Id))
.WhereIF(!string.IsNullOrWhiteSpace(input.StoreName), x => x.Dm.Contains(input.StoreName))
.WhereIF(!string.IsNullOrWhiteSpace(input.keyword),
x => x.Dm.Contains(input.keyword) || x.Mdbm.Contains(input.keyword));
var stores = await storeQuery.Select(x => new { x.Id, x.Dm, x.Mdbm }).ToListAsync();
return stores.Select(store =>
{
var metrics = BuildMetrics(
snapshot.SourceData.BillingRows.Where(x => x.StoreId == store.Id),
snapshot.SourceData.ConsumeRows.Where(x => x.StoreId == store.Id),
snapshot.SourceData.RefundRows.Where(x => x.StoreId == store.Id),
targetByStore.TryGetValue(store.Id, out var target) ? target : 0m,
deductByStore.TryGetValue(store.Id, out var deduct) ? deduct : 0m);
return new EducationDepartmentDashboardStoreDetailListOutput
{
StoreId = store.Id,
StoreCode = store.Mdbm ?? string.Empty,
StoreName = store.Dm ?? string.Empty,
BillingAmount = metrics.BillingAmount,
RefundAmount = metrics.RefundAmount,
NetPerformance = metrics.NetPerformance,
DeductAmount = metrics.DeductAmount,
ConsumeAmount = metrics.ConsumeAmount,
TargetPerformance = metrics.TargetPerformance,
CompletionRate = metrics.CompletionRate,
BillingCount = metrics.BillingCount,
ConsumeCount = metrics.ConsumeCount,
RefundCount = metrics.RefundCount,
ConsumeMemberCount = metrics.ConsumeMemberCount,
ProjectCount = metrics.ProjectCount
};
}).OrderByDescending(x => x.NetPerformance).ToList();
}
private async Task ExportFeaturedDataAsync(
List exportData,
string actionCode,
string bizType,
object filters,
string columnsJson)
{
if (exportData == null || !exportData.Any())
return new { name = string.Empty, url = string.Empty, message = "没有找到符合条件的数据" };
var columns = columnsJson.ToList();
var excelConfig = new ExcelConfig
{
FileName = actionCode + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls",
HeadFont = "微软雅黑",
HeadPoint = 10,
IsAllSizeColumn = true,
ColumnModel = columns.Select(x => new ExcelColumnModel
{
Column = x.field,
ExcelColumn = x.value
}).ToList()
};
var exportFilesPath = Path.Combine(ResolveProjectRoot(), "ExportFiles");
if (!Directory.Exists(exportFilesPath)) Directory.CreateDirectory(exportFilesPath);
var addPath = Path.Combine(exportFilesPath, excelConfig.FileName);
ExcelExportHelper.Export(exportData, excelConfig, addPath);
var encryptedFileName = _userManager.UserId + "|" + addPath + "|xls";
await _businessOperationLogService.WriteAsync(
BizLogModule,
actionCode,
bizType,
null,
$"{actionCode},共 {exportData.Count} 条",
JsonConvert.SerializeObject(new
{
filters,
rowCount = exportData.Count,
fileName = excelConfig.FileName
}));
return new
{
name = excelConfig.FileName,
url = "/api/File/Download?encryption=" +
DESCEncryption.Encrypt(encryptedFileName, "NCC")
};
}
private static EducationDepartmentDashboardStatisticsInput ToStatisticsInput(
EducationDepartmentCustomerDetailListInput input)
{
return new EducationDepartmentDashboardStatisticsInput
{
EducationDepartmentId = input.EducationDepartmentId,
StoreIds = input.StoreIds,
StatisticsMonth = input.StatisticsMonth
};
}
private static EducationDepartmentDashboardStatisticsInput ToStatisticsInput(
EducationDepartmentServiceLogListInput input)
{
return new EducationDepartmentDashboardStatisticsInput
{
EducationDepartmentId = input.EducationDepartmentId,
StoreIds = input.StoreIds,
StatisticsMonth = input.StatisticsMonth
};
}
private static decimal CalculateGrowthRate(decimal current, decimal compare)
{
return compare == 0m ? 0m : decimal.Round((current - compare) / compare * 100m, 2);
}
private static int NormalizePageIndex(int value) => value > 0 ? value : 1;
private static int NormalizePageSize(int value) => value > 0 ? value : 20;
private class FeaturedItemConfigRow
{
public string Id { get; set; }
public string ItemId { get; set; }
public string OriginalItemName { get; set; }
public string ItemCategory { get; set; }
public string DisplayName { get; set; }
public int Sort { get; set; }
}
private class ServiceLogStatisticRow
{
public string ConsumeId { get; set; }
public string BeforeImage { get; set; }
public string AfterImage { get; set; }
}
#endregion
}
}