netcore/src/Modularity/Extend/NCC.Extend/AttendanceMonthOnJobUserQuery.cs

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;
        }
    }
}