AttendanceMonthOnJobUserQuery.cs
4.43 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
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_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 == u.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) => u.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 = u.AttendanceGroupId,
GroupName = g.GroupName
})
.ToListAsync();
return lines
.OrderBy(x => x.StoreName ?? "")
.ThenBy(x => x.GroupName ?? "")
.ThenBy(x => x.EmployeeName ?? "")
.ToList();
}
}
}