AttendanceMonthOnJobUserQuery.cs
15 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
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using NCC.Extend.Entitys.Enum;
using NCC.Extend.Entitys.lq_attendance_group;
using NCC.Extend.Entitys.lq_attendance_record;
using NCC.Extend.Entitys.lq_attendance_summary;
using NCC.Extend.Entitys.lq_mdxx;
using NCC.System.Entitys.Permission;
using SqlSugar;
namespace NCC.Extend
{
/// <summary>
/// 月度考勤报表 / 考勤汇总共用的「当月在职」人员行（与花名册、Skill historical-on-job-inference 一致）。
/// </summary>
public sealed class AttendanceMonthEligibleUserLine
{
public string UserId { get; set; }
public string EmployeeName { get; set; }
public string StoreId { get; set; }
public string StoreName { get; set; }
public string AttendanceGroupId { get; set; }
public string GroupName { get; set; }
}
/// <summary>
/// 按自然月查询「当月在职口径」人员（与 <c>LqEmployeeRosterService</c>、月度考勤矩阵相同 JOIN 条件）。
/// </summary>
/// <remarks>
/// 规则：该月存在有效考勤汇总且 <c>F_EmployeeStatus = 1</c> 则入选；无汇总且非未来月时按主档
/// 入职不晚于月末、离职为空或≥月初；未来月仅认汇总（仅第一分支成立）。
/// </remarks>
public static class AttendanceMonthOnJobUserQuery
{
/// <summary>
/// 查询指定年月的在职口径人员列表。
/// </summary>
/// <param name="db">数据库客户端</param>
/// <param name="year">年</param>
/// <param name="month">月</param>
/// <param name="monthStart">该月 1 日 00:00:00</param>
/// <param name="monthLast">该月最后一天 23:59:59</param>
/// <param name="isFutureMonth">是否为未来月（相对当前系统日期）</param>
/// <param name="keyword">可选，姓名/账号/门店/分组模糊</param>
/// <param name="attendanceGroupId">可选考勤分组</param>
public static async Task<List<AttendanceMonthEligibleUserLine>> QueryEligibleUsersAsync(
ISqlSugarClient db,
int year,
int month,
DateTime monthStart,
DateTime monthLast,
bool isFutureMonth,
string keyword,
string attendanceGroupId)
{
var kw = keyword?.Trim();
var lines = await db.Queryable<UserEntity, LqMdxxEntity, LqAttendanceGroupEntity, LqAttendanceSummaryEntity>(
(u, s, g, summ) => new JoinQueryInfos(
JoinType.Left, s.Id == u.Mdid,
JoinType.Left, g.Id == s.AttendanceGroupId,
JoinType.Left,
summ.UserId == u.Id && summ.Year == year && summ.Month == month &&
summ.IsEffective == StatusEnum.有效.GetHashCode()))
.Where((u, s, g, summ) => u.DeleteMark == null)
.Where((u, s, g, summ) =>
(!SqlFunc.IsNullOrEmpty(summ.Id) && summ.EmployeeStatus == 1)
|| (!isFutureMonth
&& SqlFunc.IsNullOrEmpty(summ.Id)
&& u.EntryDate != null
&& u.EntryDate <= monthLast
&& (u.LeaveDate == null || u.LeaveDate >= monthStart)))
.WhereIF(!string.IsNullOrWhiteSpace(attendanceGroupId),
(u, s, g, summ) => s.AttendanceGroupId == attendanceGroupId)
.WhereIF(!string.IsNullOrWhiteSpace(kw), (u, s, g, summ) =>
SqlFunc.Contains(SqlFunc.IsNull(u.RealName, ""), kw) ||
SqlFunc.Contains(SqlFunc.IsNull(u.Account, ""), kw) ||
SqlFunc.Contains(SqlFunc.IsNull(s.Dm, ""), kw) ||
SqlFunc.Contains(SqlFunc.IsNull(g.GroupName, ""), kw))
.Select((u, s, g, summ) => new AttendanceMonthEligibleUserLine
{
UserId = u.Id,
EmployeeName = u.RealName,
StoreId = u.Mdid,
StoreName = s.Dm,
AttendanceGroupId = s.AttendanceGroupId,
GroupName = g.GroupName
})
.ToListAsync();
return lines
.OrderBy(x => x.StoreName ?? "")
.ThenBy(x => x.GroupName ?? "")
.ThenBy(x => x.EmployeeName ?? "")
.ToList();
}
/// <summary>
/// 月度矩阵展示用：在「在职口径」名单之外，把该月已在 <c>lq_attendance_record</c> 中有有效行，
/// 但未被 <see cref="QueryEligibleUsersAsync"/> 选中的员工补进列表（常见：主档入职日为未填、与花名册口径不一致等），
/// 避免定时补 INSERT 后管理端仍显示无数据。
/// </summary>
public static async Task<List<AttendanceMonthEligibleUserLine>> MergeWithRecordOnlyHoldersAsync(
ISqlSugarClient db,
DateTime monthStart,
DateTime monthEnd,
IReadOnlyCollection<AttendanceMonthEligibleUserLine> baseEligible,
string keyword,
string attendanceGroupId)
{
var baseIds = baseEligible
.Select(x => x.UserId)
.Where(x => !string.IsNullOrWhiteSpace(x))
.Distinct()
.ToList();
var orphanIds = await db.Queryable<LqAttendanceRecordEntity>()
.Where(x => x.IsEffective == StatusEnum.有效.GetHashCode())
.Where(x => x.AttendanceDate >= monthStart && x.AttendanceDate < monthEnd)
.Where(x => !string.IsNullOrWhiteSpace(x.UserId))
.Where(baseIds.Count > 0 ? (x => !baseIds.Contains(x.UserId)) : (x => true))
.Select(x => x.UserId)
.Distinct()
.ToListAsync();
if (orphanIds == null || orphanIds.Count == 0)
{
return baseEligible.ToList();
}
var kw = keyword?.Trim();
var extra = await db.Queryable<UserEntity, LqMdxxEntity, LqAttendanceGroupEntity>(
(u, s, g) => new JoinQueryInfos(
JoinType.Left, s.Id == u.Mdid,
JoinType.Left, g.Id == s.AttendanceGroupId))
.Where((u, s, g) => u.DeleteMark == null)
.Where((u, s, g) => orphanIds.Contains(u.Id))
.WhereIF(!string.IsNullOrWhiteSpace(attendanceGroupId),
(u, s, g) => s.AttendanceGroupId == attendanceGroupId)
.WhereIF(!string.IsNullOrWhiteSpace(kw), (u, s, g) =>
SqlFunc.Contains(SqlFunc.IsNull(u.RealName, ""), kw) ||
SqlFunc.Contains(SqlFunc.IsNull(u.Account, ""), kw) ||
SqlFunc.Contains(SqlFunc.IsNull(s.Dm, ""), kw) ||
SqlFunc.Contains(SqlFunc.IsNull(g.GroupName, ""), kw))
.Select((u, s, g) => new AttendanceMonthEligibleUserLine
{
UserId = u.Id,
EmployeeName = u.RealName,
StoreId = u.Mdid,
StoreName = s.Dm,
AttendanceGroupId = s.AttendanceGroupId,
GroupName = g.GroupName
})
.ToListAsync();
return baseEligible
.Concat(extra)
.OrderBy(x => x.StoreName ?? "")
.ThenBy(x => x.GroupName ?? "")
.ThenBy(x => x.EmployeeName ?? "")
.ToList();
}
/// <summary>
/// 算薪、导入模板、打卡同步共用人员口径（historical-on-job-inference + 有打卡/有汇总）。
/// </summary>
/// <remarks>
/// 并集包含：主档当月在职（含中途离职）、花名册/汇总在职口径、当月有有效打卡、当月已有汇总行（含已标离职）。
/// </remarks>
public static async Task<List<AttendanceMonthEligibleUserLine>> QuerySalaryMonthUsersAsync(
ISqlSugarClient db,
int year,
int month,
DateTime monthStart,
DateTime monthLast,
bool isFutureMonth,
string keyword,
string attendanceGroupId)
{
if (isFutureMonth)
{
return await QueryEligibleUsersAsync(
db, year, month, monthStart, monthLast, true, keyword, attendanceGroupId);
}
var monthEnd = monthStart.AddMonths(1);
var effective = StatusEnum.有效.GetHashCode();
var kw = keyword?.Trim();
var fromMain = await db.Queryable<UserEntity, LqMdxxEntity, LqAttendanceGroupEntity>(
(u, s, g) => new JoinQueryInfos(
JoinType.Left, s.Id == u.Mdid,
JoinType.Left, g.Id == s.AttendanceGroupId))
.Where((u, s, g) => u.DeleteMark == null)
.Where((u, s, g) => u.EntryDate != null && u.EntryDate <= monthLast)
.Where((u, s, g) => u.LeaveDate == null || u.LeaveDate >= monthStart)
.WhereIF(!string.IsNullOrWhiteSpace(attendanceGroupId),
(u, s, g) => s.AttendanceGroupId == attendanceGroupId)
.WhereIF(!string.IsNullOrWhiteSpace(kw), (u, s, g) =>
SqlFunc.Contains(SqlFunc.IsNull(u.RealName, ""), kw) ||
SqlFunc.Contains(SqlFunc.IsNull(u.Account, ""), kw) ||
SqlFunc.Contains(SqlFunc.IsNull(s.Dm, ""), kw) ||
SqlFunc.Contains(SqlFunc.IsNull(g.GroupName, ""), kw))
.Select((u, s, g) => new AttendanceMonthEligibleUserLine
{
UserId = u.Id,
EmployeeName = u.RealName,
StoreId = u.Mdid,
StoreName = s.Dm,
AttendanceGroupId = s.AttendanceGroupId,
GroupName = g.GroupName
})
.ToListAsync();
// 离职日落在当月但主档缺入职日：仍可能需算薪（常见有打卡无入职日）
var leaversInMonth = await db.Queryable<UserEntity, LqMdxxEntity, LqAttendanceGroupEntity>(
(u, s, g) => new JoinQueryInfos(
JoinType.Left, s.Id == u.Mdid,
JoinType.Left, g.Id == s.AttendanceGroupId))
.Where((u, s, g) => u.DeleteMark == null)
.Where((u, s, g) => u.LeaveDate != null && u.LeaveDate >= monthStart && u.LeaveDate <= monthLast)
.WhereIF(!string.IsNullOrWhiteSpace(attendanceGroupId),
(u, s, g) => s.AttendanceGroupId == attendanceGroupId)
.WhereIF(!string.IsNullOrWhiteSpace(kw), (u, s, g) =>
SqlFunc.Contains(SqlFunc.IsNull(u.RealName, ""), kw) ||
SqlFunc.Contains(SqlFunc.IsNull(u.Account, ""), kw) ||
SqlFunc.Contains(SqlFunc.IsNull(s.Dm, ""), kw) ||
SqlFunc.Contains(SqlFunc.IsNull(g.GroupName, ""), kw))
.Select((u, s, g) => new AttendanceMonthEligibleUserLine
{
UserId = u.Id,
EmployeeName = u.RealName,
StoreId = u.Mdid,
StoreName = s.Dm,
AttendanceGroupId = s.AttendanceGroupId,
GroupName = g.GroupName
})
.ToListAsync();
var fromEligible = await QueryEligibleUsersAsync(
db, year, month, monthStart, monthLast, false, keyword, attendanceGroupId);
var merged = fromMain
.Concat(leaversInMonth)
.Concat(fromEligible)
.GroupBy(x => x.UserId)
.Select(g => g.First())
.ToList();
merged = await MergeWithRecordOnlyHoldersAsync(
db, monthStart, monthEnd, merged, keyword, attendanceGroupId);
var summaryUserIds = await db.Queryable<LqAttendanceSummaryEntity>()
.Where(x => x.Year == year && x.Month == month && x.IsEffective == effective)
.Where(x => !SqlFunc.IsNullOrEmpty(x.UserId))
.Select(x => x.UserId)
.Distinct()
.ToListAsync();
var existingIds = new HashSet<string>(
merged.Select(x => x.UserId).Where(x => !string.IsNullOrWhiteSpace(x)));
var orphanSummaryIds = summaryUserIds
.Where(id => !string.IsNullOrWhiteSpace(id) && !existingIds.Contains(id))
.ToList();
if (orphanSummaryIds.Count > 0)
{
var extra = await db.Queryable<UserEntity, LqMdxxEntity, LqAttendanceGroupEntity>(
(u, s, g) => new JoinQueryInfos(
JoinType.Left, s.Id == u.Mdid,
JoinType.Left, g.Id == s.AttendanceGroupId))
.Where((u, s, g) => u.DeleteMark == null)
.Where((u, s, g) => orphanSummaryIds.Contains(u.Id))
.WhereIF(!string.IsNullOrWhiteSpace(attendanceGroupId),
(u, s, g) => s.AttendanceGroupId == attendanceGroupId)
.WhereIF(!string.IsNullOrWhiteSpace(kw), (u, s, g) =>
SqlFunc.Contains(SqlFunc.IsNull(u.RealName, ""), kw) ||
SqlFunc.Contains(SqlFunc.IsNull(u.Account, ""), kw) ||
SqlFunc.Contains(SqlFunc.IsNull(s.Dm, ""), kw) ||
SqlFunc.Contains(SqlFunc.IsNull(g.GroupName, ""), kw))
.Select((u, s, g) => new AttendanceMonthEligibleUserLine
{
UserId = u.Id,
EmployeeName = u.RealName,
StoreId = u.Mdid,
StoreName = s.Dm,
AttendanceGroupId = s.AttendanceGroupId,
GroupName = g.GroupName
})
.ToListAsync();
merged = merged.Concat(extra).ToList();
}
return merged
.OrderBy(x => x.StoreName ?? "")
.ThenBy(x => x.GroupName ?? "")
.ThenBy(x => x.EmployeeName ?? "")
.ToList();
}
/// <summary>
/// 按自然月解析汇总表员工状态：离职日落在该月内为 2（中途离职仍算薪），否则 1。
/// </summary>
public static int ResolveEmployeeStatusForMonth(DateTime? leaveDate, DateTime monthStart, DateTime monthLast)
{
if (!leaveDate.HasValue)
{
return 1;
}
var leave = leaveDate.Value.Date;
if (leave < monthStart.Date)
{
return 2;
}
if (leave >= monthStart.Date && leave <= monthLast.Date)
{
return 2;
}
return 1;
}
}
}