LqStatisticsService.cs
9.11 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
using NCC.Common.Core.Manager;
using NCC.Common.Enum;
using NCC.Common.Extension;
using NCC.Common.Filter;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.FriendlyException;
using NCC.Extend.Interfaces.LqStatistics;
using NCC.Extend.Entitys.Dto.LqStatistics;
using Microsoft.AspNetCore.Mvc;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using NCC.Extend.Entitys.lq_mdxx;
using NCC.Extend.Entitys.Dto.LqMdxx;
using NCC.System.Entitys.Permission;
using Microsoft.Extensions.Logging;
namespace NCC.Extend.LqStatistics
{
/// <summary>
/// 绿纤统计服务
/// </summary>
[ApiDescriptionSettings(Tag = "绿纤统计服务", Name = "LqStatistics", Order = 200, Groups = new[] { "Default" })]
[Route("api/Extend/[controller]")]
public class LqStatisticsService : ILqStatisticsService, IDynamicApiController, ITransient
{
private readonly ISqlSugarRepository<LqMdxxEntity> _lqMdxxRepository;
private readonly SqlSugarScope _db;
private readonly IUserManager _userManager;
private readonly ILogger<LqStatisticsService> _logger;
/// <summary>
/// 初始化一个<see cref="LqStatisticsService"/>类型的新实例
/// </summary>
public LqStatisticsService(
ISqlSugarRepository<LqMdxxEntity> lqMdxxRepository,
IUserManager userManager,
ILogger<LqStatisticsService> logger)
{
_lqMdxxRepository = lqMdxxRepository;
_db = _lqMdxxRepository.Context;
_userManager = userManager;
_logger = logger;
}
#region 获取门店业绩统计列表
/// <summary>
/// 获取门店业绩统计列表
/// </summary>
/// <remarks>
/// 查询所有门店的目标业绩、完成业绩、完成率等关键指标
///
/// 返回数据包含：
/// - 门店编码和店名
/// - 目标业绩（生命线）
/// - 完成业绩（实付业绩）
/// - 完成率（百分比）
/// - 开单数量
///
/// 数据来源：v_store_performance_simple 视图
/// </remarks>
/// <returns>门店业绩统计列表</returns>
/// <response code="200">成功返回门店业绩统计列表</response>
/// <response code="500">服务器内部错误</response>
[HttpGet]
public async Task<List<StorePerformanceOutput>> GetStorePerformanceList()
{
try
{
var result = await _db.Ado.SqlQueryAsync<StorePerformanceOutput>(
"SELECT " +
"store_code AS StoreCode, " +
"store_name AS StoreName, " +
"target_performance AS TargetPerformance, " +
"actual_performance AS ActualPerformance, " +
"completion_rate AS CompletionRate, " +
"order_count AS OrderCount " +
"FROM v_store_performance_simple " +
"ORDER BY actual_performance DESC");
return result ?? new List<StorePerformanceOutput>();
}
catch (Exception ex)
{
throw NCCException.Oh(ErrorCode.COM1005, ex.Message);
}
}
#endregion
#region 门店统计信息
/// <summary>
/// 获取门店统计信息
/// </summary>
/// <remarks>
/// 统计各个门店在员工数量、项目数（耗卡里面的项目数量）和消耗业绩（耗卡里面的金额）
/// 可以传入日期，然后去统计本月一号到传入的日期
///
/// 示例请求：
/// ```json
/// {
/// "queryDate": "2025-09-14"
/// }
/// ```
///
/// 参数说明：
/// - queryDate: 查询日期，系统会自动计算本月1号到该日期的统计范围
/// </remarks>
/// <param name="input">查询参数</param>
/// <returns>门店统计结果</returns>
/// <response code="200">查询成功</response>
/// <response code="400">参数错误</response>
/// <response code="500">服务器内部错误</response>
[HttpPost("StoreStatistics")]
public async Task<StoreStatisticsOutput> GetStoreStatistics([FromBody] StoreStatisticsInput input)
{
try
{
_logger.LogInformation("开始查询门店统计信息，查询日期：{QueryDate}", input.QueryDate);
// 计算查询时间范围：本月1号到传入日期
var startDate = new DateTime(input.QueryDate.Year, input.QueryDate.Month, 1);
var endDate = input.QueryDate.Date.AddDays(1).AddSeconds(-1); // 包含当天结束时间
_logger.LogInformation("统计时间范围：{StartDate} 到 {EndDate}", startDate, endDate);
// 1. 查询门店基础信息
var storeList = await _db.Queryable<LqMdxxEntity>()
.Select(it => new StoreStatisticsInfo
{
StoreId = it.Id,
StoreCode = it.Mdbm,
StoreName = it.Dm,
City = it.Cs,
BusinessUnit = it.Syb,
EmployeeCount = 0,
ProjectCount = 0,
ConsumeAmount = 0
})
.ToListAsync();
_logger.LogInformation("查询到门店数量：{Count}", storeList.Count);
// 2. 查询员工数量（按门店统计）
var employeeCounts = await _db.Queryable<UserEntity>()
.Where(u => !string.IsNullOrEmpty(u.Mdid) && u.DeleteMark != 1)
.GroupBy(u => u.Mdid)
.Select(g => new { StoreId = g.Mdid, Count = SqlFunc.AggregateCount(g.Id) })
.ToListAsync();
_logger.LogInformation("查询到员工统计数量：{Count}", employeeCounts.Count);
// 3. 从视图查询项目数和消耗业绩
var consumeStats = await _db.SqlQueryable<dynamic>(@"
SELECT
store_id,
SUM(total_project_count) as total_project_count,
SUM(total_consume_amount) as total_consume_amount
FROM v_store_daily_consume_stats
WHERE consume_date >= @startDate AND consume_date <= @endDate
GROUP BY store_id")
.AddParameters(new { startDate = startDate.ToString("yyyy-MM-dd"), endDate = input.QueryDate.ToString("yyyy-MM-dd") })
.ToListAsync();
_logger.LogInformation("查询到消耗统计数量：{Count}", consumeStats.Count);
// 4. 合并统计数据
foreach (var store in storeList)
{
// 匹配员工数量
var employeeCount = employeeCounts.FirstOrDefault(e => e.StoreId == store.StoreId);
if (employeeCount != null)
{
store.EmployeeCount = employeeCount.Count;
}
// 匹配项目数和消耗业绩
var consumeStat = consumeStats.FirstOrDefault(c => c.store_id?.ToString() == store.StoreId);
if (consumeStat != null)
{
store.ProjectCount = Convert.ToDecimal(consumeStat.total_project_count ?? 0);
store.ConsumeAmount = Convert.ToDecimal(consumeStat.total_consume_amount ?? 0);
}
}
// 5. 计算汇总统计
var summary = new StoreSummaryInfo
{
TotalStoreCount = storeList.Count,
TotalEmployeeCount = storeList.Sum(s => s.EmployeeCount),
TotalProjectCount = storeList.Sum(s => s.ProjectCount),
TotalConsumeAmount = storeList.Sum(s => s.ConsumeAmount)
};
var result = new StoreStatisticsOutput
{
StartDate = startDate,
EndDate = input.QueryDate,
StoreList = storeList.OrderByDescending(s => s.ConsumeAmount).ToList(),
Summary = summary
};
_logger.LogInformation("门店统计查询完成，门店数：{StoreCount}，总员工数：{EmployeeCount}，总项目数：{ProjectCount}，总消耗业绩：{ConsumeAmount}",
summary.TotalStoreCount, summary.TotalEmployeeCount, summary.TotalProjectCount, summary.TotalConsumeAmount);
return result;
}
catch (Exception ex)
{
_logger.LogError(ex, "查询门店统计信息时发生错误，查询日期：{QueryDate}", input.QueryDate);
throw NCCException.Oh(ErrorCode.COM1000, "查询门店统计信息失败");
}
}
#endregion
}
}