YBEE.EQM/YBEE.EQM.Application/Exam/ExamStudent/Services/ExamStudentService.cs

using Furion.DatabaseAccessor.Extensions;
using NPOI.SS.UserModel;
using System.Dynamic;
using YBEE.EQM.Core;

namespace YBEE.EQM.Application;

/// <summary>
/// 监测学生管理服务
/// </summary>
public class ExamStudentService : IExamStudentService, ITransient
{
    private readonly IRepository<ExamStudent> _rep;
    private readonly IExamGradeService _examGradeService;
    private readonly ISysDictDataService _sysDictDataService;
    private readonly ICourseCombService _courseCombService;
    private readonly ISchoolClassService _schoolClassService;

    public ExamStudentService(IRepository<ExamStudent> rep, IExamGradeService examGradeService, ISysDictDataService sysDictDataService, ICourseCombService courseCombService, ISchoolClassService schoolClassService)
    {
        _rep = rep;
        _examGradeService = examGradeService;
        _sysDictDataService = sysDictDataService;
        _courseCombService = courseCombService;
        _schoolClassService = schoolClassService;
    }

    #region 批量导入
    /// <summary>
    /// 上传批量导入文件
    /// </summary>
    /// <param name="filePath"></param>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task<UploadExamDataOutput<UploadExamStudentOutput>> Upload(string filePath, int examPlanId)
    {
        UploadExamDataOutput<UploadExamStudentOutput> result = new();
        try
        {
            using FileStream fs = new(filePath, FileMode.Open, FileAccess.Read);
            IWorkbook workbook = ExcelUtil.GetWorkbook(filePath, fs);
            var sheet = workbook.GetSheetAt(0);
            var rows = sheet.GetRowEnumerator();

            #region 验证表结构
            // 少于2行验证
            if (sheet.LastRowNum < 2)
            {
                result.ErrorMessage.Add("第一行应为填写说明，第二行应为标题行，请勿修改模板结构。");
                return result;
            }

            // 跳过第一行
            rows.MoveNext();

            // 读取表头
            rows.MoveNext();
            IRow headerRow = (IRow)rows.Current;
            int index = 0;
            int CLASS_INDEX = index++;
            int NAME_INDEX = index++;
            int CERT_TYPE_INDEX = index++;
            int ID_NUM_INDEX = index++;
            int GENDER_INDEX = index++;
            int EXAM_NUM_INDEX = index++;
            int COURSE_COMB_INDEX = index++;
            int REMARK_INDEX = index++;
            int ROOM_INDEX = index++;
            int SEAT_INDEX = index;
            Dictionary<int, string> headers = new()
            {
                { CLASS_INDEX, "班级" },
                { NAME_INDEX, "姓名" },
                { CERT_TYPE_INDEX, "证件类型" },
                { ID_NUM_INDEX, "证件号码" },
                { GENDER_INDEX, "性别" },
                { EXAM_NUM_INDEX, "自编监测号" },
                { COURSE_COMB_INDEX, "选科组合" },
                { REMARK_INDEX, "备注" },
                { ROOM_INDEX, "考场号" },
                { SEAT_INDEX, "座位号" },
            };
            List<string> headerErrors = new();
            for (int i = 0; i <= index; i++)
            {
                if (headerRow.GetCell(i)?.ToString() != headers[i])
                {
                    char letter = (char)('A' + i);
                    headerErrors.Add(letter.ToString());
                }
            }
            if (headerErrors.Any())
            {
                string columnErrors = string.Join("、", headerErrors);
                result.ErrorMessage.Add($"第2行标题行{columnErrors}列名错误。从A列开始依次应为班级、姓名、证件类型、证件号码、性别、自编监测号、选科组合、备注、考场号和座位号。");
                return result;
            }
            result.StructureCorrect = true;
            #endregion

            #region 处理数据
            // 获取证件类型
            var cts = await _sysDictDataService.GetListByDictTypeId(304);
            var certificateTypes = cts.ToDictionary(x => x.Name, y => y.Value);
            // 获取选科组全
            var ccs = await _courseCombService.GetAllList();
            var courseCombs = ccs.ToDictionary(x => x.ShortName, y => y.Id);

            // 读取数据
            List<UploadExamStudentOutput> data = new();
            int rn = 0;
            while (rows.MoveNext())
            {
                IRow row = (IRow)rows.Current;
                string rv = row.GetCell(0)?.ToString().Trim() ?? "";
                rv += row.GetCell(1)?.ToString().Trim() ?? "";
                if (rv == "")
                {
                    break;
                }

                UploadExamStudentOutput item = new() { RowNumber = ++rn };

                // 班级
                if (short.TryParse(row.GetCell(CLASS_INDEX)?.ToString(), out short classNumber))
                {
                    item.ClassNumber = classNumber;
                    if (item.ClassNumber < 1 || item.ClassNumber > 35)
                    {
                        item.ErrorMessage.Add($"{headers[CLASS_INDEX]}超限");
                    }
                }
                else
                {
                    item.ErrorMessage.Add(headers[CLASS_INDEX]);
                }

                // 姓名
                item.Name = StringUtil.ClearWhite(row.GetCell(NAME_INDEX)?.ToString() ?? "");
                if (item.Name == "" || item.Name.Length > 100)
                {
                    item.ErrorMessage.Add(headers[NAME_INDEX]);
                }
                if (item.Name.Length > 100) { item.Name = item.Name[..100]; }

                // 证件类型
                item.CertificateTypeName = StringUtil.ClearWhite(row.GetCell(CERT_TYPE_INDEX)?.ToString() ?? "");
                if (!(item.CertificateTypeName != "" && certificateTypes.ContainsKey(item.CertificateTypeName)))
                {
                    item.ErrorMessage.Add(headers[CERT_TYPE_INDEX]);
                }
                else
                {
                    item.CertificateType = (CertificateType)certificateTypes[item.CertificateTypeName];
                }

                // 证件号码
                item.IdNumber = StringUtil.ClearWhite(row.GetCell(ID_NUM_INDEX)?.ToString() ?? "").ToUpper();
                if (item.CertificateType == CertificateType.ID_CARD)
                {
                    var idNumberValidate = CertificateNumberValidator.ValidateIdCard(item.IdNumber);
                    if (!idNumberValidate.Success)
                    {
                        item.ErrorMessage.Add($"{item.CertificateTypeName}{idNumberValidate.ErrorMessage}");
                    }
                    else
                    {
                        item.GenderName = idNumberValidate.Gender == Gender.MALE ? "男" : "女";
                        item.Gender = idNumberValidate.Gender;
                    }
                }
                else
                {
                    // 性别
                    item.GenderName = StringUtil.ClearWhite(row.GetCell(GENDER_INDEX)?.ToString() ?? "");
                    if (item.CertificateType != CertificateType.ID_CARD)
                    {
                        item.Gender = item.GenderName == "男" ? Gender.MALE : item.GenderName == "女" ? Gender.FEMALE : Gender.UNKNOWN;
                    }
                }
                if (item.IdNumber.Length > 50) { item.IdNumber = item.IdNumber[..50]; }

                // 自编监测号
                item.ExamNumber = StringUtil.ClearWhite(row.GetCell(EXAM_NUM_INDEX)?.ToString() ?? "");
                if (item.ExamNumber.Length > 50) { item.ExamNumber = item.ExamNumber[..50]; }
                // TODO 根据配置检测是否需要自编监测号

                // 选科组合
                item.CourseCombName = StringUtil.ClearWhite(row.GetCell(COURSE_COMB_INDEX)?.ToString() ?? "");
                // TODO 高中需要处理选科
                if (item.CourseCombName != "")
                {
                    if (courseCombs.TryGetValue(item.CourseCombName, out short value))
                    {
                        item.CourseCombId = value;
                    }
                    else
                    {
                        item.ErrorMessage.Add(headers[COURSE_COMB_INDEX]);
                    }
                }

                // 备注
                item.Remark = StringUtil.ClearWhite(row.GetCell(REMARK_INDEX)?.ToString() ?? "");
                if (item.Remark.Length > 200) { item.Remark = item.Remark[..200]; }

                // 考场号
                item.RoomNumber = StringUtil.ClearWhite(row.GetCell(ROOM_INDEX)?.ToString() ?? "");
                if (item.RoomNumber.Length > 20) { item.RoomNumber = item.RoomNumber[..20]; }

                // 座位号
                item.SeatNumber = StringUtil.ClearWhite(row.GetCell(SEAT_INDEX)?.ToString() ?? "");
                if (item.RoomNumber.Length > 20) { item.SeatNumber = item.SeatNumber[..20]; }

                // 行是否验证通过
                item.IsSuccess = item.ErrorMessage.Count == 0;
                data.Add(item);

                result.TotalRowCount++;
                if (!item.IsSuccess)
                {
                    result.ErrorRowCount++;
                }
            }
            result.Rows = data;
            #endregion

            workbook.Close();
            fs.Close();
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            File.Delete(filePath);
        }
        return result;
    }
    /// <summary>
    /// 批量导入监测学生
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Import(ImportExamStudentInput input)
    {
        var orgId = input.SysOrgId ?? CurrentSysUserInfo.SysOrgId;

        var examPlan = await _rep.Change<ExamPlan>().FirstOrDefaultAsync(t => t.Id == input.ExamPlanId);
        var examGrade = await _examGradeService.GetById(input.ExamGradeId);
        var classNumbers = input.Items.Select(t => t.ClassNumber).Distinct().ToList();
        var classDict = await _schoolClassService.GetImportSchoolClassList(new()
        {
            SysOrgId = orgId,
            SysOrgBranchId = input.SysOrgBranchId,
            ExamGrade = examGrade,
            ClassNumberList = classNumbers,
        });

        // 删除年级下所有数据
        await _rep.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId && t.GradeId == input.GradeId && t.SysOrgBranchId == input.SysOrgBranchId).ExecuteDeleteAsync();

        List<ExamStudent> items = new();
        foreach (var ni in input.Items)
        {
            var item = ni.Adapt<ExamStudent>();
            item.ExamPlanId = input.ExamPlanId;
            item.SysOrgId = orgId;
            item.SysOrgBranchId = input.SysOrgBranchId;
            item.SchoolClassId = classDict[ni.ClassNumber];
            items.Add(item);
        }
        await _rep.InsertAsync(items);
    }
    #endregion

    #region 创建编辑
    /// <summary>
    /// 添加监测学生
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Add(AddExamStudentInput input)
    {
        var orgId = input.SysOrgId ?? CurrentSysUserInfo.SysOrgId;

        // 检测同一监测计划中同机构内是否有相同证件号码的学生
        var sameItems = await _rep.DetachedEntities.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId && t.CertificateType == input.CertificateType && t.IdNumber == input.IdNumber).ProjectToType<ExamStudentOutput>().ToListAsync();
        if (sameItems.Any())
        {
            throw Oops.Oh(ErrorCode.E2003, string.Join("、", sameItems.Select(t => $"{t.ExamGrade.Grade.Name}{t.ClassNumber}班{t.Name}")), "证件号码");
        }

        var examGrade = await _examGradeService.GetById(input.ExamGradeId);
        var schoolClass = await _schoolClassService.GetSchoolClass(orgId, input.SysOrgBranchId, examGrade, input.ClassNumber);

        var item = input.Adapt<ExamStudent>();
        item.SysOrgId = orgId;
        item.SchoolClassId = schoolClass.Id;

        await item.InsertAsync();
    }
    /// <summary>
    /// 更新监测学生
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Update(UpdateExamStudentInput input)
    {
        var oitem = await _rep.DetachedEntities.FirstOrDefaultAsync(t => t.Id == input.Id) ?? throw Oops.Oh(ErrorCode.E2001);

        var examGrade = await _examGradeService.GetById(oitem.ExamGradeId);
        var schoolClass = await _schoolClassService.GetSchoolClass(oitem.SysOrgId, input.SysOrgBranchId, examGrade, input.ClassNumber);

        var item = input.Adapt<ExamStudent>();
        item.SchoolClassId = schoolClass.Id;

        await item.UpdateIncludeAsync(new[] {
            nameof(item.SchoolClassId),
            nameof(item.ClassNumber),
            nameof(item.Name),
            nameof(item.CertificateType),
            nameof(item.IdNumber),
            nameof(item.Gender),
            nameof(item.BirthDate),
            nameof(item.ExamNumber),
            nameof(item.StudentNumber),
            nameof(item.Remark),
            nameof(item.CourseCombId),
            nameof(item.SysOrgBranchId),
            nameof(item.RoomNumber),
            nameof(item.SeatNumber),
        });
    }
    /// <summary>
    /// 删除监测学生
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Del(BaseId<long> input)
    {
        var item = await _rep.FirstOrDefaultAsync(t => t.Id == input.Id) ?? throw Oops.Oh(ErrorCode.E2001);
        await item.DeleteAsync();
    }
    /// <summary>
    /// 清空监测学生
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Clear(ClearExamStudentInput input)
    {
        var orgId = CurrentSysUserInfo.SysOrgId;
        await _rep.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId).ExecuteDeleteAsync();
    }
    #endregion

    #region 查询统计
    /// <summary>
    /// 分页查询监测学生列表
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task<PageResult<ExamStudentOutput>> QueryPageList(ExamStudentPageInput input)
    {
        var query = GetQueryBase(input);
        query = query.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == CurrentSysUserInfo.SysOrgId);
        var ret = await query.OrderBy(t => t.GradeId).ThenBy(t => t.ClassNumber).ThenBy(t => t.Id).ProjectToType<ExamStudentOutput>().ToADPagedListAsync(input.PageIndex, input.PageSize);
        return ret;
    }
    /// <summary>
    /// 获取机构班级上报人数统计列表
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task<ExamStudentCountOutput> GetOrgGradeClassStudentCount(int examPlanId)
    {
        var orgId = CurrentSysUserInfo.SysOrgId;
        var items = await _rep.DetachedEntities.Where(t => t.ExamPlanId == examPlanId && t.SysOrgId == orgId)
                                               .GroupBy(t => new { t.GradeId, t.ClassNumber })
                                               .Select(t => new
                                               {
                                                   t.Key.GradeId,
                                                   t.Key.ClassNumber,
                                                   t.FirstOrDefault().Grade,
                                                   Count = t.Count(),
                                               })
                                               .ToListAsync();
        var cols = items.Select(t => t.ClassNumber).Distinct().ToList();
        var retItems = items.ToPivotList(c => c.ClassNumber, r => r.GradeId, d => d.Any() ? d.Sum(x => x.Count) : 0);
        foreach (var item in retItems)
        {
            item.Grade = items.FirstOrDefault(t => t.GradeId == item.GradeId).Grade;
            item.GradeTotal = items.Where(t => t.GradeId == item.GradeId).Sum(x => x.Count);
        }
        int total = retItems.Sum(x => x.GradeTotal);
        IDictionary<string, object> totalItem = new ExpandoObject();
        totalItem.Add("GradeId", 9999);
        totalItem.Add("Grade", new { Id = 9999, Name = "合计" });
        totalItem.Add("GradeTotal", total);
        retItems.Add(totalItem);
        return new()
        {
            ClassNumberList = cols,
            Items = retItems,
            Total = total,
        };
    }
    #endregion

    #region 私有方法
    /// <summary>
    /// 构建查询
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    private IQueryable<ExamStudent> GetQueryBase(ExamStudentPageInput input)
    {
        var name = !string.IsNullOrEmpty(input.Name?.Trim());
        var idNumber = !string.IsNullOrEmpty(input.IdNumber?.Trim());
        var examNumber = !string.IsNullOrEmpty(input.ExamNumber?.Trim());
        var studentNumber = !string.IsNullOrEmpty(input.StudentNumber?.Trim());
        var roomNumber = !string.IsNullOrEmpty(input.RoomNumber?.Trim());
        var seatNumber = !string.IsNullOrEmpty(input.SeatNumber?.Trim());

        var query = _rep.DetachedEntities.Where((name, u => EF.Functions.Like(u.Name, $"%{input.Name.Trim()}%")))
                                         .Where((idNumber, u => EF.Functions.Like(u.IdNumber, $"%{input.IdNumber.Trim()}%")))
                                         .Where((examNumber, u => EF.Functions.Like(u.ExamNumber, $"%{input.ExamNumber.Trim()}%")))
                                         .Where((studentNumber, u => EF.Functions.Like(u.StudentNumber, $"%{input.StudentNumber.Trim()}%")))
                                         .Where((roomNumber, u => EF.Functions.Like(u.RoomNumber, $"%{input.RoomNumber.Trim()}%")))
                                         .Where((seatNumber, u => EF.Functions.Like(u.SeatNumber, $"%{input.SeatNumber.Trim()}%")))
                                         .Where(input.CertificateType.HasValue, t => t.CertificateType == input.CertificateType)
                                         .Where(input.Gender.HasValue, t => t.Gender == input.Gender)
                                         .Where(input.GradeId.HasValue, t => t.GradeId == input.GradeId)
                                         .Where(input.CourseCombId.HasValue, t => t.CourseCombId == input.CourseCombId)
                                         .Where(input.ClassNumber.HasValue, t => t.ClassNumber == input.ClassNumber)
                                         .Where(input.SysOrgBranchId.HasValue, t => t.SchoolClass.SysOrgBranchId == input.SysOrgBranchId);
        return query;
    }
    #endregion
}