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
  

      }
  }