antissoft / lvqianmeiye_ERP

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Mapster;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using NCC.Common.Core.Manager;
using NCC.Common.Enum;
using NCC.Common.Helper;
using NCC.Common.Filter;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.Extend.Entitys.Dto.LqAttendanceSummary;
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_rest_group;
using NCC.Extend.Entitys.lq_attendance_summary;
using NCC.Extend.Entitys.lq_mdxx;
using NCC.Extend.Interfaces.LqAttendanceRecord;
using NCC.Extend.Interfaces.LqAttendanceSummary;
using NCC.FriendlyException;
using NCC.System.Entitys.Permission;
using SqlSugar;
using Yitter.IdGenerator;

namespace NCC.Extend
{
    /// <summary>
    /// 考勤汇总服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "绿纤考勤汇总服务", Name = "LqAttendanceSummary", Order = 200)]
    [Route("api/Extend/[controller]")]
    public class LqAttendanceSummaryService : ILqAttendanceSummaryService, IDynamicApiController, ITransient
    {
        private readonly ISqlSugarClient _db;
        private readonly IUserManager _userManager;
        private readonly ILogger<LqAttendanceSummaryService> _logger;
        private readonly ILqAttendanceRecordService _attendanceRecordService;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="db">数据库客户端</param>
        /// <param name="userManager">用户管理器</param>
        /// <param name="logger">日志记录器</param>
        /// <param name="attendanceRecordService">考勤打卡服务（从打卡记录汇总写入汇总表）</param>
        public LqAttendanceSummaryService(
            ISqlSugarClient db,
            IUserManager userManager,
            ILogger<LqAttendanceSummaryService> logger,
            ILqAttendanceRecordService attendanceRecordService)
        {
            _db = db;
            _userManager = userManager;
            _logger = logger;
            _attendanceRecordService = attendanceRecordService;
        }

        #region 从考勤打卡记录同步汇总
        /// <summary>
        /// 从考勤打卡记录按自然月重新汇总到 lq_attendance_summary（覆盖该月已有数据）
        /// </summary>
        /// <remarks>
        /// 人选与月度考勤矩阵、员工花名册「当月在职」一致（见 Skill historical-on-job-inference）：
        /// 有当月有效汇总且员工状态为在职则入选；无汇总时按主档入职/离职边界推断。
        /// 出勤/请假/休息天数按每日一条打卡记录（同日取最新一条）的状态统计：正常+迟到→出勤，请假+病假→请假，休息→休息。
        /// 未来月不允许调用（仅认已有汇总）。
        /// </remarks>
        /// <param name="year">年</param>
        /// <param name="month">月</param>
        /// <param name="keyword">可选，与月度矩阵相同（姓名/账号/门店/分组）</param>
        /// <param name="attendanceGroupId">可选考勤分组 ID</param>
        /// <returns>同步结果</returns>
        [HttpPost("SyncFromAttendanceRecords/{year}/{month}")]
        public Task<dynamic> SyncFromAttendanceRecords(
            int year,
            int month,
            [FromQuery] string keyword = null,
            [FromQuery] string attendanceGroupId = null)
        {
            return _attendanceRecordService.SyncMonthSummaryFromAttendanceRecordsAsync(year, month, keyword, attendanceGroupId);
        }
        #endregion

        #region 上传Excel文件导入考勤汇总数据
        /// <summary>
        /// 上传Excel文件导入考勤汇总数据
        /// </summary>
        /// <remarks>
        /// 上传Excel文件批量导入考勤汇总数据
        ///
        /// Excel文件格式要求：
        /// - 第一行必须是标题行
        /// - 列顺序：员工姓名、员工电话、年份、月份、出勤天数、请假天数、休息天数、备注
        /// - 支持.xlsx和.xls格式
        ///
        /// 示例请求：
        /// POST /api/Extend/LqAttendanceSummary/ImportAttendanceDataFromExcel
        /// Content-Type: multipart/form-data
        ///
        /// 参数说明：
        /// - file: Excel文件（必填）
        /// </remarks>
        /// <param name="file">Excel文件</param>
        /// <returns>导入结果</returns>
        /// <response code="200">导入成功</response>
        /// <response code="400">文件格式错误</response>
        /// <response code="500">服务器错误</response>
        [HttpPost("ImportAttendanceDataFromExcel")]
        public async Task<dynamic> ImportAttendanceDataFromExcel(IFormFile file)
        {
            try
            {
                if (file == null || file.Length == 0)
                {
                    throw NCCException.Oh("请选择要上传的Excel文件");
                }

                // 检查文件格式
                var allowedExtensions = new[] { ".xlsx", ".xls" };
                var fileExtension = Path.GetExtension(file.FileName).ToLowerInvariant();
                if (!allowedExtensions.Contains(fileExtension))
                {
                    throw NCCException.Oh("只支持.xlsx和.xls格式的Excel文件");
                }

                var importData = new List<LqAttendanceSummaryImportInput>();

                // 保存临时文件
                var tempFilePath = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString() + Path.GetExtension(file.FileName));
                try
                {
                    using (var stream = new FileStream(tempFilePath, FileMode.Create))
                    {
                        await file.CopyToAsync(stream);
                    }

                    // 使用ExcelImportHelper读取Excel文件
                    var dataTable = ExcelImportHelper.ToDataTable(tempFilePath, 0, 0);

                    if (dataTable.Rows.Count == 0)
                    {
                        throw NCCException.Oh("Excel文件中没有数据行");
                    }

                    // 根据列名查找列索引（支持多种可能的列名）
                    int GetColumnIndex(string[] possibleNames)
                    {
                        foreach (var name in possibleNames)
                        {
                            for (int col = 0; col < dataTable.Columns.Count; col++)
                            {
                                var columnName = dataTable.Columns[col].ColumnName?.Trim();
                                if (columnName == name || columnName?.Contains(name) == true)
                                {
                                    return col;
                                }
                            }
                        }
                        return -1;
                    }

                    var idColIndex = GetColumnIndex(new[] { "员工ID", "id", "用户ID", "员工id", "用户id" });
                    var nameColIndex = GetColumnIndex(new[] { "员工姓名", "姓名", "员工", "姓名" });
                    var phoneColIndex = GetColumnIndex(new[] { "员工电话", "电话", "手机", "手机号", "联系电话" });
                    var yearColIndex = GetColumnIndex(new[] { "年份", "年" });
                    var monthColIndex = GetColumnIndex(new[] { "月份", "月" });
                    var workDaysColIndex = GetColumnIndex(new[] { "出勤天数(算薪在店天数)", "出勤天数", "出勤", "上班天数" });
                    var leaveDaysColIndex = GetColumnIndex(new[] { "请假天数", "请假", "事假天数" });
                    var restDaysColIndex = GetColumnIndex(new[] { "休息天数", "休息", "休假天数" });
                    var remarkColIndex = GetColumnIndex(new[] { "备注", "说明", "备注信息" });

                    // 验证必需的列是否存在
                    if (nameColIndex == -1) throw NCCException.Oh("Excel文件中未找到'员工姓名'列");
                    if (phoneColIndex == -1) throw NCCException.Oh("Excel文件中未找到'员工电话'列");
                    if (yearColIndex == -1) throw NCCException.Oh("Excel文件中未找到'年份'列");
                    if (monthColIndex == -1) throw NCCException.Oh("Excel文件中未找到'月份'列");

                    // DataTable 首行已是 Excel 第 2 行数据（标题行由 ExcelImportHelper 解析为列名）
                    for (int i = 0; i < dataTable.Rows.Count; i++)
                    {
                        try
                        {
                            var row = dataTable.Rows[i];
                            var userId = idColIndex >= 0 ? row[idColIndex]?.ToString()?.Trim() : null;
                            var employeeName = row[nameColIndex]?.ToString()?.Trim();
                            var employeePhone = row[phoneColIndex]?.ToString()?.Trim();
                            if (employeePhone == "无")
                            {
                                employeePhone = null;
                            }

                            // 处理年份：可能是数字、日期或字符串
                            string yearText = null;
                            if (yearColIndex >= 0 && row[yearColIndex] != null && row[yearColIndex] != DBNull.Value)
                            {
                                if (row[yearColIndex] is DateTime dt)
                                {
                                    yearText = dt.Year.ToString();
                                }
                                else if (row[yearColIndex] is double d)
                                {
                                    yearText = ((int)d).ToString();
                                }
                                else
                                {
                                    yearText = row[yearColIndex].ToString()?.Trim();
                                }
                            }

                            // 处理月份：可能是数字、日期或字符串
                            string monthText = null;
                            if (monthColIndex >= 0 && row[monthColIndex] != null && row[monthColIndex] != DBNull.Value)
                            {
                                if (row[monthColIndex] is DateTime dt)
                                {
                                    monthText = dt.Month.ToString();
                                }
                                else if (row[monthColIndex] is double d)
                                {
                                    monthText = ((int)d).ToString();
                                }
                                else
                                {
                                    monthText = row[monthColIndex].ToString()?.Trim();
                                }
                            }

                            var workDaysText = workDaysColIndex >= 0 ? row[workDaysColIndex]?.ToString()?.Trim() : null;
                            var leaveDaysText = leaveDaysColIndex >= 0 ? row[leaveDaysColIndex]?.ToString()?.Trim() : null;
                            var restDaysText = restDaysColIndex >= 0 ? row[restDaysColIndex]?.ToString()?.Trim() : null;
                            var remark = remarkColIndex >= 0 ? row[remarkColIndex]?.ToString()?.Trim() : null;

                            // 跳过空行
                            if (string.IsNullOrEmpty(employeeName) && string.IsNullOrEmpty(employeePhone))
                            {
                                continue;
                            }

                            // 验证必填字段
                            if (string.IsNullOrEmpty(employeeName))
                            {
                                throw new Exception($"第{i + 2}行：员工姓名不能为空");
                            }
                            if (string.IsNullOrEmpty(employeePhone) && string.IsNullOrEmpty(userId))
                            {
                                throw new Exception($"第{i + 2}行：员工电话不能为空（无员工ID时）");
                            }

                            // 解析数值字段
                            int year = 0;
                            int month = 0;

                            // 解析年份：支持纯数字、日期格式、中文格式
                            if (string.IsNullOrEmpty(yearText))
                            {
                                throw new Exception($"第{i + 2}行：年份不能为空");
                            }

                            // 尝试直接解析为整数
                            if (int.TryParse(yearText, out year))
                            {
                                // 成功解析
                            }
                            // 尝试解析日期格式（如：2025-11-01 或 2025/11/01）
                            else if (DateTime.TryParse(yearText, out DateTime yearDate))
                            {
                                year = yearDate.Year;
                            }
                            // 尝试解析中文格式（如：2025年）
                            else if (yearText.Contains("年"))
                            {
                                var yearMatch = Regex.Match(yearText, @"(\d{4})年");
                                if (yearMatch.Success && int.TryParse(yearMatch.Groups[1].Value, out year))
                                {
                                    // 成功解析
                                }
                                else
                                {
                                    throw new Exception($"第{i + 2}行：年份格式错误，无法解析：{yearText}");
                                }
                            }
                            else
                            {
                                throw new Exception($"第{i + 2}行：年份格式错误，无法解析。实际值：\"{yearText}\"（类型：{yearText?.GetType().Name}）");
                            }

                            // 验证年份范围
                            if (year < 2020 || year > 2030)
                            {
                                throw new Exception($"第{i + 2}行：年份必须在2020-2030之间");
                            }

                            // 解析月份：支持纯数字、日期格式、中文格式
                            if (string.IsNullOrEmpty(monthText))
                            {
                                throw new Exception($"第{i + 2}行：月份不能为空");
                            }

                            // 尝试直接解析为整数
                            if (int.TryParse(monthText, out month))
                            {
                                // 成功解析
                            }
                            // 尝试解析日期格式（如：2025-11-01 或 2025/11/01）
                            else if (DateTime.TryParse(monthText, out DateTime monthDate))
                            {
                                month = monthDate.Month;
                            }
                            // 尝试解析中文格式（如：11月）
                            else if (monthText.Contains("月"))
                            {
                                var monthMatch = Regex.Match(monthText, @"(\d{1,2})月");
                                if (monthMatch.Success && int.TryParse(monthMatch.Groups[1].Value, out month))
                                {
                                    // 成功解析
                                }
                                else
                                {
                                    throw new Exception($"第{i + 2}行：月份格式错误，无法解析：{monthText}");
                                }
                            }
                            // 尝试解析"年-月"格式（如：2025-11）
                            else if (monthText.Contains("-") || monthText.Contains("/"))
                            {
                                var parts = monthText.Split(new[] { "-", "/" }, StringSplitOptions.RemoveEmptyEntries);
                                if (parts.Length >= 2 && int.TryParse(parts[1], out month))
                                {
                                    // 成功解析
                                }
                                else
                                {
                                    throw new Exception($"第{i + 2}行：月份格式错误，无法解析：{monthText}");
                                }
                            }
                            else
                            {
                                throw new Exception($"第{i + 2}行：月份格式错误，无法解析。实际值：\"{monthText}\"（类型：{monthText?.GetType().Name}）");
                            }

                            // 验证月份范围
                            if (month < 1 || month > 12)
                            {
                                throw new Exception($"第{i + 2}行：月份必须在1-12之间");
                            }

                            var daysInMonth = DateTime.DaysInMonth(year, month);
                            decimal.TryParse(workDaysText, out decimal workDays);
                            if (workDays > daysInMonth)
                            {
                                throw new Exception($"第{i + 2}行：出勤天数不能超过当月自然日天数（{daysInMonth}天）");
                            }
                            decimal.TryParse(leaveDaysText, out decimal leaveDays);
                            decimal.TryParse(restDaysText, out decimal restDays);

                            var item = new LqAttendanceSummaryImportInput
                            {
                                UserId = userId,
                                EmployeeName = employeeName,
                                EmployeePhone = employeePhone,
                                Year = year,
                                Month = month,
                                WorkDays = workDays,
                                LeaveDays = leaveDays,
                                RestDays = restDays,
                                Remark = remark
                            };

                            importData.Add(item);
                        }
                        catch (Exception ex)
                        {
                            throw new Exception($"第{i + 2}行数据解析失败: {ex.Message}");
                        }
                    }
                }
                finally
                {
                    // 清理临时文件
                    if (File.Exists(tempFilePath))
                    {
                        File.Delete(tempFilePath);
                    }
                }

                if (!importData.Any())
                {
                    throw NCCException.Oh("Excel文件中没有有效的数据行");
                }

                // 处理导入数据
                return await ProcessImportData(importData);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "上传Excel文件导入考勤汇总数据失败");
                throw NCCException.Oh($"上传Excel文件导入考勤汇总数据失败: {ex.Message}");
            }
        }
        #endregion

        #region 处理导入数据
        /// <summary>
        /// 处理导入数据
        /// </summary>
        /// <param name="importData">导入数据列表</param>
        /// <returns>导入结果</returns>
        private async Task<dynamic> ProcessImportData(List<LqAttendanceSummaryImportInput> importData)
        {
            var successCount = 0;
            var failCount = 0;
            var errorMessages = new List<string>();
            var entitiesToInsert = new List<LqAttendanceSummaryEntity>();
            var entitiesToUpdate = new List<LqAttendanceSummaryEntity>();
            var importedUserKeys = new List<(int Year, int Month, string UserId)>();

            foreach (var item in importData)
            {
                try
                {
                    // 1. 优先按模板 id 列匹配，其次姓名+电话，无电话时仅按姓名
                    UserEntity user = null;
                    if (!string.IsNullOrEmpty(item.UserId))
                    {
                        user = await _db.Queryable<UserEntity>()
                            .Where(u => u.Id == item.UserId && u.DeleteMark == null)
                            .FirstAsync();
                    }

                    if (user == null && !string.IsNullOrEmpty(item.EmployeePhone))
                    {
                        user = await _db.Queryable<UserEntity>()
                            .Where(u => u.RealName == item.EmployeeName && u.MobilePhone == item.EmployeePhone && u.DeleteMark == null)
                            .FirstAsync();
                    }

                    if (user == null && !string.IsNullOrEmpty(item.EmployeeName))
                    {
                        user = await _db.Queryable<UserEntity>()
                            .Where(u => u.RealName == item.EmployeeName && u.DeleteMark == null)
                            .FirstAsync();
                    }

                    if (user == null)
                    {
                        var phoneLabel = string.IsNullOrEmpty(item.EmployeePhone) ? "无电话" : item.EmployeePhone;
                        errorMessages.Add($"员工 {item.EmployeeName}({phoneLabel}) 不存在");
                        failCount++;
                        continue;
                    }

                    // 2. 检查是否已存在相同记录
                    var existingRecord = await _db.Queryable<LqAttendanceSummaryEntity>()
                        .Where(a => a.UserId == user.Id && a.Year == item.Year && a.Month == item.Month)
                        .FirstAsync();

                    var monthStartForStatus = new DateTime(item.Year, item.Month, 1);
                    var monthLastForStatus = new DateTime(
                        item.Year, item.Month, DateTime.DaysInMonth(item.Year, item.Month), 23, 59, 59);
                    var employeeStatus = AttendanceMonthOnJobUserQuery.ResolveEmployeeStatusForMonth(
                        user.LeaveDate, monthStartForStatus, monthLastForStatus);

                    if (existingRecord != null)
                    {
                        // 更新现有记录
                        existingRecord.EmployeeStatus = employeeStatus;
                        existingRecord.WorkDays = item.WorkDays;
                        existingRecord.LeaveDays = item.LeaveDays;
                        existingRecord.RestDays = item.RestDays;
                        existingRecord.Remark = AttendanceSummarySourceHelper.BuildImportRemark(item.Remark);
                        existingRecord.UpdateUser = _userManager.UserId;
                        existingRecord.UpdateTime = DateTime.Now;
                        entitiesToUpdate.Add(existingRecord);
                    }
                    else
                    {
                        // 创建新记录
                        var entity = new LqAttendanceSummaryEntity
                        {
                            Id = YitIdHelper.NextId().ToString(),
                            UserId = user.Id,
                            Year = item.Year,
                            Month = item.Month,
                            EmployeeStatus = employeeStatus,
                            WorkDays = item.WorkDays,
                            LeaveDays = item.LeaveDays,
                            RestDays = item.RestDays,
                            Remark = AttendanceSummarySourceHelper.BuildImportRemark(item.Remark),
                            CreateUser = _userManager.UserId,
                            CreateTime = DateTime.Now,
                            UpdateUser = _userManager.UserId,
                            UpdateTime = DateTime.Now,
                            IsEffective = 1
                        };
                        entitiesToInsert.Add(entity);
                    }

                    importedUserKeys.Add((item.Year, item.Month, user.Id));
                    successCount++;
                }
                catch (Exception ex)
                {
                    errorMessages.Add($"处理员工 {item.EmployeeName} 数据失败: {ex.Message}");
                    failCount++;
                    _logger.LogError(ex, $"导入考勤数据失败 - 员工: {item.EmployeeName}");
                }
            }

            // 批量插入和更新
            if (entitiesToInsert.Any())
            {
                await _db.Insertable(entitiesToInsert).ExecuteCommandAsync();
            }

            if (entitiesToUpdate.Any())
            {
                await _db.Updateable(entitiesToUpdate).ExecuteCommandAsync();
            }

            // Excel 导入：仅移除同月未出现在本次导入清单中的「Excel导入」汇总行，不影响打卡同步数据
            foreach (var monthGroup in importedUserKeys.GroupBy(x => (x.Year, x.Month)))
            {
                var importedUserIds = monthGroup.Select(x => x.UserId).Distinct().ToHashSet(StringComparer.Ordinal);
                var summariesInMonth = await _db.Queryable<LqAttendanceSummaryEntity>()
                    .Where(a => a.Year == monthGroup.Key.Year && a.Month == monthGroup.Key.Month)
                    .ToListAsync();
                var excelIdsToDelete = summariesInMonth
                    .Where(s => AttendanceSummarySourceHelper.IsExcelImport(s) && !importedUserIds.Contains(s.UserId))
                    .Select(s => s.Id)
                    .Where(id => !string.IsNullOrWhiteSpace(id))
                    .ToList();
                if (excelIdsToDelete.Count > 0)
                {
                    await _db.Deleteable<LqAttendanceSummaryEntity>()
                        .Where(a => excelIdsToDelete.Contains(a.Id))
                        .ExecuteCommandAsync();
                }
            }

            if (successCount == 0)
            {
                var preview = errorMessages.Any()
                    ? string.Join("；", errorMessages.Take(5))
                    : "请检查员工姓名、电话或 id 是否与系统一致";
                throw NCCException.Oh($"导入失败：{preview}");
            }

            return new
            {
                success = true,
                message = failCount > 0 ? $"导入完成，成功 {successCount} 条，失败 {failCount} 条" : "导入完成",
                data = new
                {
                    totalCount = importData.Count,
                    successCount = successCount,
                    failCount = failCount,
                    insertCount = entitiesToInsert.Count,
                    updateCount = entitiesToUpdate.Count,
                    errorMessages = errorMessages
                }
            };
        }
        #endregion

        #region 导入模板人员清单
        /// <summary>
        /// 获取指定年月考勤汇总 Excel 导入模板人员清单
        /// </summary>
        /// <remarks>
        /// 口径与员工花名册、从打卡同步汇总一致（historical-on-job-inference）：
        /// - 入职不晚于该月末，且（无离职日或离职日≥该月1日）→ 含当月中途离职，不含更早离职
        /// - 该月有有效汇总且员工状态为在职时亦入选
        ///
        /// 示例请求：
        /// `GET /api/Extend/LqAttendanceSummary/ImportTemplateUsers?year=2026&amp;month=5`
        /// </remarks>
        /// <param name="year">年份</param>
        /// <param name="month">月份（1-12）</param>
        /// <returns>人员清单</returns>
        /// <response code="200">成功返回人员列表</response>
        [HttpGet("ImportTemplateUsers")]
        public async Task<dynamic> GetImportTemplateUsers([FromQuery] int year, [FromQuery] int month)
        {
            if (year < 1990 || year > 2100 || month < 1 || month > 12)
            {
                throw NCCException.Oh("请先选择有效的年份和月份");
            }

            var now = DateTime.Now;
            var isFutureMonth = year > now.Year || (year == now.Year && month > now.Month);
            var monthStart = new DateTime(year, month, 1);
            var monthLast = new DateTime(year, month, DateTime.DaysInMonth(year, month), 23, 59, 59);

            var eligibleLines = await AttendanceMonthOnJobUserQuery.QuerySalaryMonthUsersAsync(
                _db, year, month, monthStart, monthLast, isFutureMonth, null, null);
            var userIds = eligibleLines
                .Select(x => x.UserId)
                .Where(x => !string.IsNullOrWhiteSpace(x))
                .Distinct()
                .ToList();

            var users = userIds.Count == 0
                ? new List<UserEntity>()
                : await _db.Queryable<UserEntity>()
                    .Where(u => userIds.Contains(u.Id) && u.DeleteMark == null)
                    .ToListAsync();
            var userById = users.GroupBy(u => u.Id).ToDictionary(g => g.Key, g => g.First());

            var summaries = userIds.Count == 0
                ? new List<LqAttendanceSummaryEntity>()
                : await _db.Queryable<LqAttendanceSummaryEntity>()
                    .Where(s => s.Year == year && s.Month == month && s.IsEffective == StatusEnum.有效.GetHashCode())
                    .Where(s => userIds.Contains(s.UserId))
                    .ToListAsync();
            var summaryByUserId = summaries
                .Where(s => !string.IsNullOrWhiteSpace(s.UserId))
                .GroupBy(s => s.UserId)
                .ToDictionary(g => g.Key, g => g.First());

            var monthEndDate = monthLast.Date;
            var list = eligibleLines
                .Where(line => !string.IsNullOrWhiteSpace(line.UserId) && userById.ContainsKey(line.UserId))
                .Select(line =>
                {
                    var u = userById[line.UserId];
                    var leftInMonth = u.LeaveDate.HasValue
                        && u.LeaveDate.Value.Date >= monthStart.Date
                        && u.LeaveDate.Value.Date <= monthEndDate;
                    summaryByUserId.TryGetValue(line.UserId, out var summary);
                    return new LqAttendanceSummaryImportTemplateUserOutput
                    {
                        id = u.Id,
                        realName = u.RealName,
                        mobilePhone = u.MobilePhone,
                        leftInMonth = leftInMonth,
                        workDays = summary?.WorkDays,
                        leaveDays = summary?.LeaveDays,
                        restDays = summary?.RestDays
                    };
                })
                .ToList();

            return new
            {
                year,
                month,
                total = list.Count,
                list
            };
        }
        #endregion

        #region 获取考勤汇总列表
        /// <summary>
        /// 获取考勤汇总列表
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("")]
        public async Task<dynamic> GetList([FromQuery] LqAttendanceSummaryListQueryInput input)
        {
            var sidx = input.sidx == null ? "id" : input.sidx;
            var sord = input.sort == null ? "desc" : input.sort;
            var data = await _db.Queryable<LqAttendanceSummaryEntity>()
                .Where(a => a.IsEffective == StatusEnum.有效.GetHashCode())
                .WhereIF(!string.IsNullOrEmpty(input.UserId), a => a.UserId == input.UserId)
                .WhereIF(input.Year.HasValue, a => a.Year == input.Year)
                .WhereIF(input.Month.HasValue, a => a.Month == input.Month)
                .WhereIF(input.EmployeeStatus.HasValue, a => a.EmployeeStatus == input.EmployeeStatus)

                .Select(a => new LqAttendanceSummaryListOutput
                {
                    id = a.Id,
                    userId = a.UserId,
                    userName = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == a.UserId).Select(u => u.RealName),
                    year = a.Year,
                    month = a.Month,
                    employeeStatus = a.EmployeeStatus,
                    workDays = a.WorkDays,
                    leaveDays = a.LeaveDays,
                    restDays = a.RestDays,
                    remark = a.Remark,
                    createUser = a.CreateUser,
                    createUserName = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == a.CreateUser).Select(u => u.RealName),
                    createTime = a.CreateTime,
                    updateUser = a.UpdateUser,
                    updateUserName = SqlFunc.Subqueryable<UserEntity>().Where(u => u.Id == a.UpdateUser).Select(u => u.RealName),
                    updateTime = a.UpdateTime,
                    isEffective = a.IsEffective,
                })
                .MergeTable()
                .OrderBy(sidx + " " + input.sort)
                .ToPagedListAsync(input.currentPage, input.pageSize);
            var pageRows = data.list == null
                ? new List<LqAttendanceSummaryListOutput>()
                : data.list.ToList();
            await FillSummaryListDerivedFromRecordsAsync(pageRows);
            return PageResult<LqAttendanceSummaryListOutput>.SqlSugarPageResult(data);
        }

        /// <summary>
        /// 按分页结果从打卡记录补全：当月考勤组文案 + 应休/到岗/请假/迟到早退/旷工及可算扣款。
        /// </summary>
        private async Task FillSummaryListDerivedFromRecordsAsync(List<LqAttendanceSummaryListOutput> items)
        {
            if (items == null || items.Count == 0)
            {
                return;
            }

            var userIds = items.Select(x => x.userId).Where(x => !string.IsNullOrWhiteSpace(x)).Distinct().ToList();
            if (userIds.Count == 0)
            {
                return;
            }

            var monthBounds = items.Select(x => (x.year, x.month)).Distinct().ToList();
            var minStart = monthBounds.Min(m => new DateTime(m.year, m.month, 1));
            var maxEnd = monthBounds.Max(m => new DateTime(m.year, m.month, 1).AddMonths(1));

            var records = await _db.Queryable<LqAttendanceRecordEntity>()
                .Where(x => x.IsEffective == StatusEnum.有效.GetHashCode())
                .Where(x => x.AttendanceDate >= minStart && x.AttendanceDate < maxEnd)
                .Where(x => userIds.Contains(x.UserId))
                .ToListAsync();

            var users = await _db.Queryable<UserEntity>()
                .Where(u => userIds.Contains(u.Id) && u.DeleteMark == null)
                .Select(u => new { u.Id, u.Mdid, u.AttendanceRestGroupId })
                .ToListAsync();
            var mdids = users.Select(u => u.Mdid).Where(x => !string.IsNullOrWhiteSpace(x)).Distinct().ToList();
            var stores = mdids.Count == 0
                ? new List<LqMdxxEntity>()
                : await _db.Queryable<LqMdxxEntity>()
                    .Where(s => mdids.Contains(s.Id))
                    .ToListAsync();
            var storeById = stores.Where(x => !string.IsNullOrEmpty(x.Id)).GroupBy(x => x.Id).ToDictionary(x => x.Key, x => x.First());

            var groupIds = stores.Select(s => s.AttendanceGroupId).Where(x => !string.IsNullOrWhiteSpace(x)).Distinct().ToList();
            var groups = groupIds.Count == 0
                ? new List<LqAttendanceGroupEntity>()
                : await _db.Queryable<LqAttendanceGroupEntity>()
                    .Where(g => groupIds.Contains(g.Id) && SqlFunc.IsNull(g.DeleteMark, 0) == 0)
                    .ToListAsync();
            var groupById = groups.GroupBy(x => x.Id).ToDictionary(x => x.Key, x => x.First());
            var groupNameById = groupById.ToDictionary(x => x.Key, x => x.Value.GroupName ?? string.Empty);

            var defaultRestIdsFromShift = groups
                .Select(g => g.DefaultRestGroupId)
                .Where(x => !string.IsNullOrWhiteSpace(x))
                .Distinct()
                .ToList();
            var defaultRestGroups = defaultRestIdsFromShift.Count == 0
                ? new List<LqAttendanceRestGroupEntity>()
                : await _db.Queryable<LqAttendanceRestGroupEntity>()
                    .Where(r => defaultRestIdsFromShift.Contains(r.Id) && SqlFunc.IsNull(r.DeleteMark, 0) == 0)
                    .ToListAsync();
            var monthlyRestByDefaultRestId = defaultRestGroups
                .Where(x => !string.IsNullOrEmpty(x.Id))
                .GroupBy(x => x.Id)
                .ToDictionary(x => x.Key, x => x.First().MonthlyRestDays);

            var monthlyRestByGroupId = groupById.ToDictionary(
                x => x.Key,
                x =>
                {
                    var g = x.Value;
                    if (!string.IsNullOrWhiteSpace(g.DefaultRestGroupId)
                        && monthlyRestByDefaultRestId.TryGetValue(g.DefaultRestGroupId, out var mr))
                    {
                        return mr;
                    }

                    return 0;
                });

            var restGroupIds = users.Select(u => u.AttendanceRestGroupId).Where(x => !string.IsNullOrWhiteSpace(x)).Distinct().ToList();
            var restGroups = restGroupIds.Count == 0
                ? new List<LqAttendanceRestGroupEntity>()
                : await _db.Queryable<LqAttendanceRestGroupEntity>()
                    .Where(r => restGroupIds.Contains(r.Id) && SqlFunc.IsNull(r.DeleteMark, 0) == 0)
                    .ToListAsync();
            var restGroupById = restGroups.GroupBy(x => x.Id).ToDictionary(x => x.Key, x => x.First());
            var monthlyRestByRestGroupId = restGroupById.ToDictionary(x => x.Key, x => x.Value.MonthlyRestDays);

            var profileNameByUserId = users.ToDictionary(
                u => u.Id,
                u =>
                {
                    var gid = !string.IsNullOrWhiteSpace(u.Mdid) && storeById.TryGetValue(u.Mdid.Trim(), out var st)
                        ? st.AttendanceGroupId
                        : null;
                    return !string.IsNullOrWhiteSpace(gid) && groupNameById.TryGetValue(gid, out var gn)
                        ? gn
                        : string.Empty;
                });
            var profileMonthlyRestByUserId = users.ToDictionary(
                u => u.Id,
                u =>
                {
                    if (!string.IsNullOrWhiteSpace(u.AttendanceRestGroupId)
                        && monthlyRestByRestGroupId.TryGetValue(u.AttendanceRestGroupId, out var mrr))
                    {
                        return (int)mrr;
                    }

                    var gid = !string.IsNullOrWhiteSpace(u.Mdid) && storeById.TryGetValue(u.Mdid.Trim(), out var st)
                        ? st.AttendanceGroupId
                        : null;
                    if (string.IsNullOrWhiteSpace(gid)
                        || !monthlyRestByGroupId.TryGetValue(gid, out var mr))
                    {
                        return 0;
                    }

                    return (int)mr;
                });

            var recordsByUserMonth = records
                .GroupBy(r => (r.UserId, r.AttendanceDate.Year, r.AttendanceDate.Month))
                .ToDictionary(g => g.Key, g => g.ToList());

            foreach (var item in items)
            {
                if (string.IsNullOrWhiteSpace(item.userId))
                {
                    item.attendanceGroupDisplay = "无";
                    continue;
                }

                profileNameByUserId.TryGetValue(item.userId, out var profileName);
                item.dataSource = AttendanceSummarySourceHelper.ResolveDataSource(item.remark, item.dataSource);

                // Excel 导入：仅展示导入字段，不计算打卡扩展指标
                if (AttendanceSummarySourceHelper.IsExcelImport(item.remark, item.dataSource))
                {
                    item.attendanceGroupDisplay = string.IsNullOrWhiteSpace(profileName) ? "无" : profileName;
                    continue;
                }

                recordsByUserMonth.TryGetValue((item.userId, item.year, item.month), out var monthRecs);
                item.attendanceGroupDisplay =
                    AttendanceMonthGroupDisplayHelper.BuildDisplay(monthRecs, profileName ?? string.Empty);

                profileMonthlyRestByUserId.TryGetValue(item.userId, out var fallbackRest);
                var perDay = AttendanceSummaryMonthMetricsHelper.CollapseToLatestPerDay(monthRecs);
                var metrics = AttendanceSummaryMonthMetricsHelper.Compute(perDay, fallbackRest, null);
                item.monthEntitledRestDays = metrics.EntitledRestDays;
                item.monthOnDutyDays = metrics.OnDutyDays;
                item.monthCompositeWorkDays = metrics.CompositeWorkDays;
                item.monthPaidLeaveDays = metrics.PaidLeaveDays;
                item.monthLeaveDaysTotal = metrics.LeaveDaysTotal;
                item.monthLateEarlyDays = metrics.LateEarlyDays;
                item.monthAbsenteeismDays = metrics.AbsenteeismDays;
                item.monthLateDeductionAmount = metrics.LateDeductionAmount;
                item.monthLeaveDeductionAmount = metrics.LeaveDeductionAmount;
                item.monthAbsenteeismDeductionAmount = metrics.AbsenteeismDeductionAmount;
            }
        }
        #endregion

        #region 清空某一个月的数据
        /// <summary>
        ///  清空某一个月的数据
        /// </summary>
        /// <param name="year">年份</param>
        /// <param name="month">月份</param>
        /// <returns></returns>
        [HttpDelete("DeleteByMonth/{year}/{month}")]
        public async Task<dynamic> DeleteByMonth(string year, string month)
        {
            int yearInt = int.Parse(year);
            int monthInt = int.Parse(month);
            await _db.Deleteable<LqAttendanceSummaryEntity>().Where(p => p.Year == yearInt && p.Month == monthInt).ExecuteCommandAsync();
            return new
            {
                success = true,
                message = "清空成功"
            };
        }
        #endregion

        #region 生成健康师考勤模拟数据
        /// <summary>
        /// 生成健康师考勤模拟数据
        /// </summary>
        /// <remarks>
        /// 为所有健康师生成指定年月的考勤模拟数据
        /// 
        /// 规则：
        /// - 80%以上的健康师出勤天数 >= 21天
        /// - 其他健康师出勤天数随机生成（0-20天）
        /// - 员工状态默认为1（在职）
        /// 
        /// 示例请求：
        /// ```json
        /// {
        ///   "year": 2025,
        ///   "month": 11
        /// }
        /// ```
        /// </remarks>
        /// <param name="year">年份</param>
        /// <param name="month">月份</param>
        /// <returns>生成结果</returns>
        /// <response code="200">成功生成考勤模拟数据</response>
        /// <response code="400">参数错误</response>
        /// <response code="500">生成失败</response>
        [HttpPost("GenerateMockData/{year}/{month}")]
        public async Task<dynamic> GenerateMockData(int year, int month)
        {
            try
            {
                // 验证参数
                if (year < 2000 || year > 3000)
                {
                    throw NCCException.Oh("年份参数无效");
                }
                if (month < 1 || month > 12)
                {
                    throw NCCException.Oh("月份参数无效，应在1-12之间");
                }

                // 查询所有健康师
                var healthCoaches = await _db.Queryable<UserEntity>()
                    .Where(u => u.Gw == "健康师" && u.EnabledMark == 1 && (u.DeleteMark == null || u.DeleteMark == 0))
                    .Select(u => new { u.Id, u.RealName })
                    .ToListAsync();

                if (!healthCoaches.Any())
                {
                    throw NCCException.Oh("未找到健康师用户");
                }

                // 检查是否已存在该年月的考勤数据
                var existingRecords = await _db.Queryable<LqAttendanceSummaryEntity>()
                    .Where(a => a.Year == year && a.Month == month)
                    .CountAsync();

                if (existingRecords > 0)
                {
                    throw NCCException.Oh($"已存在{year}年{month}月的考勤数据，请先清空后再生成");
                }

                var totalCount = healthCoaches.Count;
                var highAttendanceCount = (int)Math.Ceiling(totalCount * 0.8); // 80%以上，向上取整
                var lowAttendanceCount = totalCount - highAttendanceCount;

                var random = new Random();
                var entitiesToInsert = new List<LqAttendanceSummaryEntity>();
                var now = DateTime.Now;

                // 生成考勤数据
                for (int i = 0; i < totalCount; i++)
                {
                    var healthCoach = healthCoaches[i];
                    decimal workDays;
                    decimal leaveDays;
                    decimal restDays;

                    if (i < highAttendanceCount)
                    {
                        // 80%以上的健康师：出勤天数 >= 21天
                        workDays = random.Next(21, 31); // 21-30天
                        var remainingDays = 30 - workDays;
                        // 剩余天数分配给请假和休息
                        if (remainingDays > 0)
                        {
                            leaveDays = random.Next(0, (int)remainingDays + 1);
                            restDays = remainingDays - leaveDays;
                        }
                        else
                        {
                            leaveDays = 0;
                            restDays = 0;
                        }
                    }
                    else
                    {
                        // 其他20%的健康师：出勤天数 < 21天
                        workDays = random.Next(0, 21); // 0-20天
                        var remainingDays = 30 - workDays;
                        // 剩余天数分配给请假和休息
                        if (remainingDays > 0)
                        {
                            leaveDays = random.Next(0, (int)remainingDays + 1);
                            restDays = remainingDays - leaveDays;
                        }
                        else
                        {
                            leaveDays = 0;
                            restDays = 0;
                        }
                    }

                    var entity = new LqAttendanceSummaryEntity
                    {
                        Id = YitIdHelper.NextId().ToString(),
                        UserId = healthCoach.Id,
                        Year = year,
                        Month = month,
                        EmployeeStatus = 1, // 在职
                        WorkDays = workDays,
                        LeaveDays = leaveDays,
                        RestDays = restDays,
                        Remark = "模拟数据",
                        CreateUser = _userManager.UserId ?? "system",
                        CreateTime = now,
                        UpdateUser = _userManager.UserId ?? "system",
                        UpdateTime = now,
                        IsEffective = 1
                    };
                    entitiesToInsert.Add(entity);
                }

                // 批量插入
                await _db.Insertable(entitiesToInsert).ExecuteCommandAsync();

                // 统计结果
                var highAttendanceActual = entitiesToInsert.Count(e => e.WorkDays >= 21);
                var highAttendancePercentage = (decimal)highAttendanceActual / totalCount * 100;

                return new
                {
                    success = true,
                    message = "生成考勤模拟数据成功",
                    data = new
                    {
                        year = year,
                        month = month,
                        totalCount = totalCount,
                        highAttendanceCount = highAttendanceActual,
                        highAttendancePercentage = Math.Round(highAttendancePercentage, 2),
                        generatedCount = entitiesToInsert.Count
                    }
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"生成健康师考勤模拟数据失败 - 年份: {year}, 月份: {month}");
                throw NCCException.Oh($"生成健康师考勤模拟数据失败：{ex.Message}");
            }
        }
        #endregion


    }
}