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