LqShareStatisticsHqService.cs
10.1 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
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
using Microsoft.AspNetCore.Mvc;
using NCC.Common.Filter;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.Extend.Entitys.Dto.LqShareStatisticsHq;
using NCC.Extend.Entitys.lq_share_statistics_hq;
using NCC.Extend.Entitys.lq_kd_kdjlb;
using NCC.Extend.Entitys.lq_hytk_hytk;
using NCC.Extend.Entitys.lq_kd_pxmx;
using NCC.Extend.Entitys.lq_contract_rent_detail;
using NCC.Extend.Entitys.lq_contract;
using NCC.Extend.Entitys;
using SqlSugar;
using System;
using System.Linq;
using System.Threading.Tasks;
using Yitter.IdGenerator;
namespace NCC.Extend
{
/// <summary>
/// 总部股份统计服务
/// </summary>
[ApiDescriptionSettings(Tag = "总部股份统计服务", Name = "LqShareStatisticsHq", Order = 402)]
[Route("api/Extend/[controller]")]
public class LqShareStatisticsHqService : IDynamicApiController, ITransient
{
private readonly ISqlSugarClient _db;
public LqShareStatisticsHqService(ISqlSugarClient db)
{
_db = db;
}
/// <summary>
/// 生成总部股份统计数据
/// </summary>
/// <param name="input">生成参数</param>
/// <returns>生成结果</returns>
[HttpPost("generate")]
public async Task<dynamic> GenerateStatistics([FromBody] ShareStatisticsHqGenerateInput input)
{
if (string.IsNullOrEmpty(input.StatisticsMonth) || input.StatisticsMonth.Length != 6)
{
return new { code = 500, msg = "统计月份格式错误,应为 YYYYMM" };
}
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 existing = await _db.Queryable<LqShareStatisticsHqEntity>()
.FirstAsync(x => x.StatisticsMonth == input.StatisticsMonth);
var entity = existing ?? new LqShareStatisticsHqEntity
{
Id = YitIdHelper.NextId().ToString(),
StatisticsMonth = input.StatisticsMonth,
IsEffective = 1,
CreateTime = DateTime.Now,
CreateUser = "System"
};
// 计算各项数据
await CalculateIncome(entity, startDate, endDate);
await CalculateCost(entity, startDate, endDate, input.StatisticsMonth);
CalculateProfit(entity);
entity.UpdateTime = DateTime.Now;
entity.UpdateUser = "System";
if (existing == null)
{
await _db.Insertable(entity).ExecuteCommandAsync();
return new { code = 200, msg = "生成成功", data = new { generated = true } };
}
else
{
await _db.Updateable(entity).ExecuteCommandAsync();
return new { code = 200, msg = "更新成功", data = new { updated = true } };
}
}
/// <summary>
/// 查询总部股份统计列表
/// </summary>
/// <param name="input">查询参数</param>
/// <returns>统计列表</returns>
[HttpGet("list")]
public async Task<dynamic> GetList([FromQuery] ShareStatisticsHqQueryInput input)
{
var query = _db.Queryable<LqShareStatisticsHqEntity>()
.Where(x => x.IsEffective == 1);
if (!string.IsNullOrEmpty(input.StatisticsMonth))
{
query = query.Where(x => x.StatisticsMonth == input.StatisticsMonth);
}
var list = await query.OrderBy(x => x.StatisticsMonth, OrderByType.Desc)
.Select(x => new ShareStatisticsHqOutput
{
Id = x.Id,
StatisticsMonth = x.StatisticsMonth,
IncomeGeneral = x.IncomeGeneral,
IncomeTechDept = x.IncomeTechDept,
CostReimbursement = x.CostReimbursement,
CostLabor = x.CostLabor,
CostEducationRent = x.CostEducationRent,
CostWarehouseRent = x.CostWarehouseRent,
CostHQRent = x.CostHQRent,
OperationalProfit = x.OperationalProfit,
CreateTime = x.CreateTime,
UpdateTime = x.UpdateTime
})
.ToListAsync();
return new { code = 200, msg = "查询成功", data = list };
}
#region 私有计算方法
/// <summary>
/// 计算收入部分
/// </summary>
private async Task CalculateIncome(LqShareStatisticsHqEntity entity, DateTime startDate, DateTime endDate)
{
// 1. 收入-全部 = (所有门店的总开单实付业绩 - 所有门店的总实退金额) * 9%
var totalBilling = await _db.Queryable<LqKdKdjlbEntity>()
.Where(x => x.Kdrq >= startDate && x.Kdrq <= endDate && x.IsEffective == 1)
.SumAsync(x => x.Sfyj);
var totalRefund = await _db.Queryable<LqHytkHytkEntity>()
.Where(x => x.Tksj >= startDate && x.Tksj <= endDate && x.IsEffective == 1)
.SumAsync(x => x.Tkje ?? 0);
entity.IncomeGeneral = (totalBilling - totalRefund) * 0.09m;
// 2. 收入-科技部 = (总科美业绩 - 总科美退款) * 0.3 * 0.09
var kemeiPerformance = await _db.Queryable<LqKdPxmxEntity>()
.Where(x => x.Yjsj >= startDate && x.Yjsj <= endDate && x.IsEffective == 1)
.Where(x => x.ItemCategory == "科美")
.SumAsync(x => x.TotalPrice);
// TODO: 需要确认科美退款的统计方式
entity.IncomeTechDept = kemeiPerformance * 0.3m * 0.09m;
}
/// <summary>
/// 计算成本部分
/// </summary>
private async Task CalculateCost(LqShareStatisticsHqEntity entity, DateTime startDate, DateTime endDate, string statisticsMonth)
{
// 1. 成本-报销：筛选一级分类为"总部费用"的申请，审批状态为"已通过"
var reimbursementAmount = await _db.Queryable<LqReimbursementApplicationEntity, LqPurchaseRecordsEntity, LqReimbursementCategoryEntity>(
(app, purchase, category) => app.PurchaseRecordsId == purchase.Id && purchase.ReimbursementCategoryId == category.Id)
.Where((app, purchase, category) =>
category.Level1Name == "总部费用"
&& app.ApprovalStatus == "已通过"
&& app.ApplicationTime >= startDate
&& app.ApplicationTime <= endDate.AddDays(1))
.SumAsync((app, purchase, category) => purchase.Amount);
entity.CostReimbursement = reimbursementAmount;
// 2. 成本-人工 (保留)
entity.CostLabor = 0;
// 3. 成本-教育部房租
// 从合同月租明细表获取：门店是总部，分类是教育，统计月份匹配
// 使用月份字符串匹配，避免时区问题
var educationRent = await _db.Ado.SqlQuerySingleAsync<decimal>(
$@"SELECT COALESCE(SUM(d.F_DueAmount), 0)
FROM lq_contract_rent_detail d
INNER JOIN lq_contract c ON d.F_ContractId = c.F_Id
WHERE c.F_StoreId = '1649328471923847168'
AND c.F_Category = '教育'
AND DATE_FORMAT(d.F_PaymentMonth, '%Y%m') = @StatisticsMonth
AND c.F_IsEffective = 1
AND d.F_IsEffective = 1",
new { StatisticsMonth = statisticsMonth });
entity.CostEducationRent = educationRent;
// 4. 成本-仓库房租
// 从合同月租明细表获取：门店是总部，分类是仓库，统计月份匹配
// 使用月份字符串匹配，避免时区问题
var warehouseRent = await _db.Ado.SqlQuerySingleAsync<decimal>(
$@"SELECT COALESCE(SUM(d.F_DueAmount), 0)
FROM lq_contract_rent_detail d
INNER JOIN lq_contract c ON d.F_ContractId = c.F_Id
WHERE c.F_StoreId = '1649328471923847168'
AND c.F_Category = '仓库'
AND DATE_FORMAT(d.F_PaymentMonth, '%Y%m') = @StatisticsMonth
AND c.F_IsEffective = 1
AND d.F_IsEffective = 1",
new { StatisticsMonth = statisticsMonth });
entity.CostWarehouseRent = warehouseRent;
// 5. 成本-总部房租
// 从合同月租明细表获取：门店是总部，分类是总部，统计月份匹配
// 使用月份字符串匹配，避免时区问题
var hqRent = await _db.Ado.SqlQuerySingleAsync<decimal>(
$@"SELECT COALESCE(SUM(d.F_DueAmount), 0)
FROM lq_contract_rent_detail d
INNER JOIN lq_contract c ON d.F_ContractId = c.F_Id
WHERE c.F_StoreId = '1649328471923847168'
AND c.F_Category = '总部'
AND DATE_FORMAT(d.F_PaymentMonth, '%Y%m') = @StatisticsMonth
AND c.F_IsEffective = 1
AND d.F_IsEffective = 1",
new { StatisticsMonth = statisticsMonth });
entity.CostHQRent = hqRent;
}
/// <summary>
/// 计算利润
/// </summary>
private void CalculateProfit(LqShareStatisticsHqEntity entity)
{
// 总部运营利润 = 收入(门店9%) + 收入(科技部9%) - 成本(报销) - 成本(人工) - 成本(房租)
var totalIncome = entity.IncomeGeneral + entity.IncomeTechDept;
var totalCost = entity.CostReimbursement
+ entity.CostLabor
+ entity.CostEducationRent
+ entity.CostWarehouseRent
+ entity.CostHQRent;
entity.OperationalProfit = totalIncome - totalCost;
}
#endregion
}
}