AttendanceMonthOnJobUserQuery.cs
7.6 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
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();
}
}
}