YBEE.EQM/YBEE.EQM.Application/Exam/ExamAbsentReplace/Services/ExamAbsentReplaceService.cs

using Furion.DatabaseAccessor.Extensions;
using Furion.JsonSerialization;
using NPOI.OpenXmlFormats.Wordprocessing;
using NPOI.SS.UserModel;
using NPOI.XWPF.UserModel;
using System.Dynamic;
using YBEE.EQM.Core;

namespace YBEE.EQM.Application;

/// <summary>
/// 缺测替补管理服务
/// </summary>
public partial class ExamAbsentReplaceService(
    IRepository<ExamAbsentReplace> rep,
    IExamGradeService examGradeService,
    ISysDictDataService sysDictDataService,
    ISchoolClassService schoolClassService,
    IResourceFileService resourceFileService,
    ICourseService courseService
) : IExamAbsentReplaceService, ITransient
{
    #region 批量导入
    /// <summary>
    /// 上传监测缺测替补批量导入文件
    /// </summary>
    /// <param name="filePath"></param>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task<UploadExamDataOutput<UploadExamAbsentReplaceOutput>> Upload(string filePath, int examPlanId)
    {
        UploadExamDataOutput<UploadExamAbsentReplaceOutput> 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 GRADE_INDEX = index++;
            int CLASS_INDEX = index++;
            int NAME_INDEX = index++;
            int EXAM_NUM_INDEX = index++;
            int REASON_INDEX = index++;
            int COURSES_INDEX = index++;
            int TEL_INDEX = index++;
            int REPLACE_NAME_INDEX = index++;
            int REPLACE_EXAM_NUM_INDEX = index++;
            int REMARK_INDEX = index;
            Dictionary<int, string> headers = new()
            {
                { GRADE_INDEX, "年级号" },
                { CLASS_INDEX, "班级号" },
                { NAME_INDEX, "缺测学生姓名" },
                { EXAM_NUM_INDEX, "缺测学生监测号" },
                { REASON_INDEX, "缺测原因" },
                { COURSES_INDEX, "缺测科目" },
                { TEL_INDEX, "缺测学生家长电话" },
                { REPLACE_NAME_INDEX, "替补学生姓名" },
                { REPLACE_EXAM_NUM_INDEX, "替补学生监测号" },
                { REMARK_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 examGrades = await examGradeService.GetListByExamPlanId(examPlanId);
            // 科目列表
            var courses = await courseService.GetAllLiteList();
            // 科目字典
            var courseDicts = courses.ToDictionary(t => t.Name);

            // 读取数据
            List<UploadExamAbsentReplaceOutput> 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;
                }

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

                // 年级
                if (short.TryParse(row.GetCell(GRADE_INDEX)?.ToString(), out short gradeNumber))
                {
                    var g = examGrades.FirstOrDefault(t => t.Grade.GradeNumber == gradeNumber);
                    if (g == null)
                    {
                        item.ErrorMessage.Add($"{headers[GRADE_INDEX]}与监测年级不符");
                    }
                    else
                    {
                        item.ExamGradeId = g.Id;
                        item.GradeId = g.GradeId;
                    }
                }
                else
                {
                    item.ErrorMessage.Add(headers[CLASS_INDEX]);
                }

                // 班级
                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.AbsentName = (row.GetCell(NAME_INDEX)?.ToString() ?? "").ClearWhitespace();
                if (item.AbsentName == "" || item.AbsentName.Length > 100)
                {
                    item.ErrorMessage.Add($"{headers[NAME_INDEX]}未填或超出100字");
                }
                if (item.AbsentName.Length > 100) { item.AbsentName = item.AbsentName[..100]; }

                // 缺测学生监测号
                item.AbsentExamNumber = (row.GetCell(EXAM_NUM_INDEX)?.ToString() ?? "").ClearWhitespace().ToUpper();
                if (item.AbsentExamNumber == "" || item.AbsentExamNumber.Length > 20)
                {
                    item.ErrorMessage.Add($"{headers[EXAM_NUM_INDEX]}格式错误");
                }
                if (item.AbsentExamNumber.Length > 20) { item.AbsentExamNumber = item.AbsentExamNumber[..20]; }

                // 缺测原因
                item.AbsentReason = (row.GetCell(REASON_INDEX)?.ToString() ?? "").ClearWhitespace();
                if (item.AbsentReason == "" || item.AbsentReason.Length > 200)
                {
                    item.ErrorMessage.Add($"{headers[REASON_INDEX]}未填或超出200字");
                }
                if (item.AbsentReason.Length > 200) { item.AbsentReason = item.AbsentReason[..200]; }

                // 缺测科目
                item.AbsentCourseText = (row.GetCell(COURSES_INDEX)?.ToString() ?? "").ClearWhitespace();
                if (item.AbsentCourseText == "")
                {
                    item.ErrorMessage.Add($"{headers[COURSES_INDEX]}未填");
                }
                else
                {
                    var acourses = GetCourses(courseDicts, item.AbsentCourseText);
                    item.AbsentCourseList = acourses.courses;
                    if (acourses.errorMessage.Count > 0)
                    {
                        item.ErrorMessage.Add($"{headers[COURSES_INDEX]}（{string.Join("、", acourses.errorMessage)}）名称错误");
                    }
                }

                // 缺测学生家长电话
                item.PatriarchTel = (row.GetCell(TEL_INDEX)?.ToString() ?? "").ClearWhitespace();
                if (item.PatriarchTel == "" || item.PatriarchTel.Length > 50)
                {
                    item.ErrorMessage.Add($"{headers[TEL_INDEX]}未填或超出50字");
                }
                if (item.PatriarchTel.Length > 50) { item.PatriarchTel = item.PatriarchTel[..50]; }


                // 替补学生姓名
                item.ReplaceName = (row.GetCell(REPLACE_NAME_INDEX)?.ToString() ?? "").ClearWhitespace();
                if (item.ReplaceName.Length > 100) { item.ReplaceName = item.ReplaceName[..100]; }
                // 替补学生监测号
                item.ReplaceExamNumber = (row.GetCell(REPLACE_EXAM_NUM_INDEX)?.ToString() ?? "").ClearWhitespace().ToUpper();
                if (item.ReplaceExamNumber.Length > 20) { item.ReplaceExamNumber = item.ReplaceExamNumber[..20]; }
                if (item.ReplaceName.Length == 0 && item.ReplaceExamNumber.Length != 0 || item.ReplaceName.Length != 0 && item.ReplaceExamNumber.Length == 0)
                {
                    item.ErrorMessage.Add($"有替补时替补学生姓名和监测号必须都填写");
                }


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

                // 行是否验证通过
                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<int> Import(ImportExamAbsentReplaceInput input)
    {
        var orgId = input.SysOrgId ?? CurrentSysUserInfo.SysOrgId;

        var existsIdNumberList = await rep.DetachedEntities.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId).Select(t => t.AbsentExamNumber).Distinct().ToListAsync() ?? new List<string>();

        List<ExamAbsentReplace> items = new();

        var spStus = input.Items.Where(t => !existsIdNumberList.Contains(t.AbsentExamNumber)).ToList();
        var gs = spStus.Select(t => t.ExamGradeId).Distinct().ToList();
        var examGrades = await rep.Change<ExamGrade>().DetachedEntities.Where(t => gs.Contains(t.Id)).Select(t => t.Adapt<ExamGradeOutput>()).ToListAsync();

        int c = 0;
        foreach (var eg in examGrades)
        {
            var classNumbers = spStus.Where(t => t.ExamGradeId == eg.Id).Select(t => t.ClassNumber).Distinct().ToList();
            var classDict = await schoolClassService.GetImportSchoolClassList(new()
            {
                SysOrgId = orgId,
                SysOrgBranchId = input.SysOrgBranchId,
                ExamGrade = eg,
                ClassNumberList = classNumbers,
            });
            var citems = spStus.Where(t => t.ExamGradeId == eg.Id).ToList();
            foreach (var ni in citems)
            {
                var item = ni.Adapt<ExamAbsentReplace>();
                item.ExamPlanId = input.ExamPlanId;
                item.SysOrgId = orgId;
                item.SysOrgBranchId = input.SysOrgBranchId;
                item.SchoolClassId = classDict[ni.ClassNumber];
                item.AbsentName = item.AbsentName?.ClearWhitespace();
                item.AbsentExamNumber = item.AbsentExamNumber?.ClearWhitespace();
                item.ReplaceName = item.ReplaceName?.ClearWhitespace();
                item.ReplaceExamNumber = item.ReplaceExamNumber?.ClearWhitespace();
                items.Add(item);
                c++;
            }
        }

        await rep.InsertAsync(items);
        return c;
    }
    #endregion

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

        // 检测同一监测计划中同机构内是否有相同监测号的学生
        var sameItems = await rep.DetachedEntities.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId && t.AbsentExamNumber.ToUpper() == input.AbsentExamNumber.ToUpper()).ProjectToType<ExamAbsentReplaceOutput>().ToListAsync();
        if (sameItems.Count != 0)
        {
            throw Oops.Oh(ErrorCode.E2003, string.Join("、", sameItems.Select(t => $"{t.ExamGrade.Grade.Name}{t.ClassNumber}班{t.AbsentName}")), "监测号");
        }

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

        var item = input.Adapt<ExamAbsentReplace>();
        item.SysOrgId = orgId;
        item.SchoolClassId = schoolClass.Id;
        item.AbsentName = item.AbsentName?.ClearWhitespace();
        item.AbsentExamNumber = item.AbsentExamNumber?.ClearWhitespace();
        item.ReplaceName = item.ReplaceName?.ClearWhitespace();
        item.ReplaceExamNumber = item.ReplaceExamNumber?.ClearWhitespace();

        await item.InsertAsync();
    }
    /// <summary>
    /// 更新监测缺测替补
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Update(UpdateExamAbsentReplaceInput 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<ExamAbsentReplace>();
        item.SchoolClassId = schoolClass.Id;
        item.AbsentName = item.AbsentName?.ClearWhitespace();
        item.AbsentExamNumber = item.AbsentExamNumber?.ClearWhitespace();
        item.ReplaceName = item.ReplaceName?.ClearWhitespace();
        item.ReplaceExamNumber = item.ReplaceExamNumber?.ClearWhitespace();

        await item.UpdateIncludeAsync(new[] {
            nameof(item.SysOrgBranchId),
            nameof(item.SchoolClassId),
            nameof(item.ClassNumber),
            nameof(item.AbsentName),
            nameof(item.AbsentExamNumber),
            nameof(item.AbsentReason),
            nameof(item.AbsentCourses),
            nameof(item.IsReplaced),
            nameof(item.ReplaceName),
            nameof(item.ReplaceExamNumber),
            nameof(item.Remark),
            nameof(item.PatriarchName),
            nameof(item.PatriarchTel),
        });
    }
    /// <summary>
    /// 添加缺测替补佐证材料
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task AddAttachment(AddAttachmentInput input)
    {
        var item = await rep.FirstOrDefaultAsync(t => t.Id == input.SourceId) ?? throw Oops.Oh(ErrorCode.E2001);
        item.Attachments = AttachmentUtil.InsertInto(item.Attachments, input.Adapt<AttachmentItem>());
        await item.UpdateIncludeAsync([nameof(item.Attachments)]);
    }
    /// <summary>
    /// 删除缺测替补佐证材料
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task DelAttachment(DeleteAttachmentInput input)
    {
        var item = await rep.FirstOrDefaultAsync(t => t.Id == input.SourceId) ?? throw Oops.Oh(ErrorCode.E2001);
        var attachments = AttachmentUtil.GetList(item.Attachments);
        var a = attachments.FirstOrDefault(t => t.FileId == input.FileId);
        if (a != null)
        {
            attachments.Remove(a);
            item.Attachments = JSON.Serialize(attachments);
            await item.UpdateIncludeAsync(new[] { nameof(item.Attachments) });

            await resourceFileService.Del(new() { Id = a.FileId });
            if (a.ThumbFileId.HasValue && a.ThumbFileId > 0)
            {
                await resourceFileService.Del(new() { Id = a.ThumbFileId.Value });
            }
        }
    }
    /// <summary>
    /// 验证缺测替补佐证材料
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task<bool> VerifyAttachment(int examPlanId)
    {
        var items = await rep.DetachedEntities.Where(t => t.ExamPlanId == examPlanId && t.SysOrgId == CurrentSysUserInfo.SysOrgId).ProjectToType<ExamAbsentReplaceOutput>().ToListAsync();
        if (items.Any(t => t.AttachmentList.Count == 0))
        {
            return false;
        }
        return true;
    }
    /// <summary>
    /// 删除监测缺测替补
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Del(BaseId input)
    {
        var item = await rep.FirstOrDefaultAsync(t => t.Id == input.Id) ?? throw Oops.Oh(ErrorCode.E2001);
        var attachments = JSON.Deserialize<List<AttachmentItem>>(item.Attachments);
        if (attachments != null && attachments.Count != 0)
        {
            foreach (var attachment in attachments)
            {
                await resourceFileService.Del(new() { Id = attachment.FileId });
                if (attachment.ThumbFileId.HasValue && attachment.ThumbFileId > 0)
                {
                    await resourceFileService.Del(new() { Id = attachment.ThumbFileId.Value });
                }
            }
        }
        await item.DeleteAsync();
    }
    /// <summary>
    /// 清空监测缺测替补
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Clear(ClearExamAbsentReplaceInput input)
    {
        var orgId = CurrentSysUserInfo.SysOrgId;
        await rep.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId).ExecuteDeleteAsync();
    }
    /// <summary>
    /// 导出监测缺测替补上报打印表格
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task<byte[]> ExportPrintTable(int examPlanId)
    {
        var items = await rep.DetachedEntities.Include(t => t.Grade).Include(t => t.SysOrg).Where(t => t.ExamPlanId == examPlanId && t.SysOrgId == CurrentSysUserInfo.SysOrgId).ToListAsync();
        if (items.Count == 0)
        {
            throw Oops.Oh(ErrorCode.E2001);
        }

        // 获取证件类型
        var cts = await sysDictDataService.GetListByDictTypeId(304);
        var certificateTypes = cts.ToDictionary(x => (CertificateType)x.Value, y => y.Name);

        XWPFDocument doc = new();
        doc.Document.body.sectPr = new();
        var sectPr = doc.Document.body.sectPr;
        sectPr.pgSz.orient = ST_PageOrientation.landscape;
        sectPr.pgSz.w = 16838;
        sectPr.pgSz.h = 11906;
        sectPr.pgMar.left = sectPr.pgMar.right = 1080;
        sectPr.pgMar.top = sectPr.pgMar.bottom = 1440;

        var gradeItems = items.GroupBy(t => t.GradeId).OrderBy(t => t.Key).ToList();
        int pi = 0;
        foreach (var gitem in gradeItems)
        {
            var gradeFirst = gitem.First();

            // 多于一个年级分多页
            if (0 < pi++)
            {
                doc.CreateParagraph().IsPageBreak = true;
            }

            // 标题行
            XWPFParagraph pHeader = doc.CreateParagraph();
            pHeader.Alignment = ParagraphAlignment.CENTER;
            pHeader.SpacingLineRule = LineSpacingRule.AUTO;
            pHeader.SetSpacingBetween(1.5, LineSpacingRule.AUTO);
            var pHeaderRun = pHeader.CreateRun();
            pHeaderRun.SetText($"{gradeFirst.SysOrg.FullName.Replace("重庆市", "").Replace("重庆", "")}缺测替补明细表（{gradeFirst.Grade.Name}）");
            pHeaderRun.FontSize = 18;
            pHeaderRun.IsBold = true;

            // 空行
            CreateBlanParagraph();

            // 签字行
            XWPFParagraph pSign = doc.CreateParagraph();
            pSign.SpacingLineRule = LineSpacingRule.AUTO;
            pSign.SetSpacingBetween(2, LineSpacingRule.AUTO);
            var pSignRun = pSign.CreateRun();
            pSignRun.SetText("学校盖章：                                       纪检负责人：                                       校长签字：");
            pSignRun.FontSize = 12;

            // 签字行
            XWPFParagraph pCount = doc.CreateParagraph();
            pCount.SpacingLineRule = LineSpacingRule.AUTO;
            pCount.SetSpacingBetween(2, LineSpacingRule.AUTO);
            var dt = DateTime.Now;
            var pCountRun = pCount.CreateRun();
            pCountRun.SetText($"填表人：                                           联系电话：                                            督考签字：                                        缺测替补计（{gitem.Count()}）人          {dt.Year}年{dt.Month}月{dt.Day}日");
            pCountRun.FontSize = 12;

            // 空行
            CreateBlanParagraph(1);

            #region 生成表格
            const int COLUMN_COUNT = 10;
            XWPFTable table = doc.CreateTable(gitem.Count() + 2, COLUMN_COUNT);
            var tableLayout = table.GetCTTbl().tblPr.AddNewTblLayout();
            tableLayout.type = ST_TblLayoutType.@fixed;
            table.Width = 5080;
            table.SetColumnWidth(0, 280);
            table.SetColumnWidth(1, 280);
            table.SetColumnWidth(2, 540);
            table.SetColumnWidth(3, 640);
            table.SetColumnWidth(4, 1000);
            table.SetColumnWidth(5, 1200);
            table.SetColumnWidth(6, 660);
            table.SetColumnWidth(7, 540);
            table.SetColumnWidth(8, 640);
            table.SetColumnWidth(9, 660);

            int ri = 0;
            table.GetRow(ri).GetCTRow().AddNewTrPr().AddNewTrHeight().val = 480;
            table.GetRow(ri).GetCell(0).SetParagraph(SetCellText(table, "序号", ParagraphAlignment.CENTER, true));
            table.GetRow(ri).GetCell(1).SetParagraph(SetCellText(table, "班级", ParagraphAlignment.CENTER, true));
            table.GetRow(ri).GetCell(2).SetParagraph(SetCellText(table, "学生姓名", ParagraphAlignment.CENTER, true));
            table.GetRow(ri).GetCell(3).SetParagraph(SetCellText(table, "监测号", ParagraphAlignment.CENTER, true));
            table.GetRow(ri).GetCell(4).SetParagraph(SetCellText(table, "缺测原因", ParagraphAlignment.CENTER, true));
            table.GetRow(ri).GetCell(5).SetParagraph(SetCellText(table, "缺测科目", ParagraphAlignment.CENTER, true));
            table.GetRow(ri).GetCell(6).SetParagraph(SetCellText(table, "家长电话", ParagraphAlignment.CENTER, true));
            table.GetRow(ri).GetCell(7).SetParagraph(SetCellText(table, "有替补学生", ParagraphAlignment.CENTER, true));
            table.GetRow(ri).GetCell(8).SetParagraph(SetCellText(table, null, ParagraphAlignment.CENTER, true));
            table.GetRow(ri).GetCell(9).SetParagraph(SetCellText(table, "备注", ParagraphAlignment.CENTER, true));
            for (int ci = 0; ci < COLUMN_COUNT; ci++)
            {
                table.GetRow(ri).GetCell(ci).GetCTTc().AddNewTcPr().AddNewVAlign().val = ST_VerticalJc.center;
            }

            ri++;
            table.GetRow(ri).GetCTRow().AddNewTrPr().AddNewTrHeight().val = 480;
            table.GetRow(ri).GetCell(7).SetParagraph(SetCellText(table, "姓名", ParagraphAlignment.CENTER, true));
            table.GetRow(ri).GetCell(7).GetCTTc().AddNewTcPr().AddNewVAlign().val = ST_VerticalJc.center;
            table.GetRow(ri).GetCell(8).SetParagraph(SetCellText(table, "监测号", ParagraphAlignment.CENTER, true));
            table.GetRow(ri).GetCell(8).GetCTTc().AddNewTcPr().AddNewVAlign().val = ST_VerticalJc.center;

            MergeCells(table, 9, 9, ri - 1, ri);
            MergeCells(table, 7, 8, ri - 1, ri - 1);

            for (int i = 0; i < 7; i++)
            {
                MergeCells(table, i, i, ri - 1, ri);
            }

            int tableRowNumber = 1;
            foreach (var item in gitem)
            {
                var courses = JSON.Deserialize<List<CourseMiniOutput>>(item.AbsentCourses);
                var courseText = string.Join("、", courses.Select(t => t.Name));

                ri++;
                table.GetRow(ri).GetCTRow().AddNewTrPr().AddNewTrHeight().val = 480;
                table.GetRow(ri).GetCell(0).SetParagraph(SetCellText(table, $"{tableRowNumber++}", ParagraphAlignment.CENTER));
                table.GetRow(ri).GetCell(1).SetParagraph(SetCellText(table, $"{item.ClassNumber}", ParagraphAlignment.CENTER));
                table.GetRow(ri).GetCell(2).SetParagraph(SetCellText(table, item.AbsentName, ParagraphAlignment.CENTER));
                table.GetRow(ri).GetCell(3).SetParagraph(SetCellText(table, item.AbsentExamNumber, ParagraphAlignment.CENTER));
                table.GetRow(ri).GetCell(4).SetParagraph(SetCellText(table, item.AbsentReason, ParagraphAlignment.LEFT));
                table.GetRow(ri).GetCell(5).SetParagraph(SetCellText(table, courseText, ParagraphAlignment.LEFT));
                table.GetRow(ri).GetCell(6).SetParagraph(SetCellText(table, item.PatriarchTel, ParagraphAlignment.CENTER));
                table.GetRow(ri).GetCell(7).SetParagraph(SetCellText(table, item.ReplaceName, ParagraphAlignment.CENTER));
                table.GetRow(ri).GetCell(8).SetParagraph(SetCellText(table, item.ReplaceExamNumber, ParagraphAlignment.CENTER));
                table.GetRow(ri).GetCell(9).SetParagraph(SetCellText(table, item.Remark, ParagraphAlignment.LEFT));

                for (int ci = 0; ci < COLUMN_COUNT; ci++)
                {
                    table.GetRow(ri).GetCell(ci).GetCTTc().AddNewTcPr().AddNewVAlign().val = ST_VerticalJc.center;
                }
            }
            // 空行
            CreateBlanParagraph();
            #endregion
        }

        // 生成流
        using MemoryStream ms = new();
        doc.Write(ms);
        doc.Close();

        var retDoc = ms.ToArray();
        ms.Close();
        return retDoc;

        // 创建空行
        void CreateBlanParagraph(double lineSpacing = 1.5)
        {
            var p = doc.CreateParagraph();
            p.SpacingLineRule = LineSpacingRule.AUTO;
            p.SetSpacingBetween(lineSpacing, LineSpacingRule.AUTO);
            p.CreateRun().FontSize = 12;
        }
    }
    #endregion

    #region 查询统计
    /// <summary>
    /// 分页查询监测缺测替补列表
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task<PageResult<ExamAbsentReplaceOutput>> QueryPageList(ExamAbsentReplacePageInput input)
    {
        var query = GetQueryBase(input);
        var ret = await query.OrderBy(t => t.GradeId).ThenBy(t => t.ClassNumber).ThenBy(t => t.Id)
                             .ProjectToType<ExamAbsentReplaceOutput>()
                             .ToADPagedListAsync(input.PageIndex, input.PageSize);
        return ret;
    }
    /// <summary>
    /// 获取机构班级缺测替补上报人数统计列表
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <param name="sysOrgId"></param>
    /// <returns></returns>
    public async Task<ExamAbsentReplaceCountOutput> GetOrgGradeClassStudentCount(int examPlanId, short? sysOrgId = null)
    {
        var orgId = sysOrgId ?? 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,
        };
    }
    /// <summary>
    /// 获取状态数量
    /// </summary>
    /// <returns></returns>
    public async Task<List<StatusCount>> QueryStatusCount(ExamAbsentReplacePageInput input)
    {
        var query = GetQueryBase(input);
        if (query == null)
        {
            return [];
        }
        var counts = await query.GroupBy(t => t.Status).Select(t => new StatusCount { Status = (int)t.Key, Count = t.Count() }).ToListAsync();
        return counts;
    }
    #endregion

    #region 公有静态方法
    /// <summary>
    /// 根据科目字符串组合获取科目列表
    /// </summary>
    /// <param name="courseDict">科目字典</param>
    /// <param name="courses">科目组合</param>
    /// <returns></returns>
    public static (List<string> errorMessage, List<CourseMiniOutput> courses) GetCourses(Dictionary<string, CourseLiteOutput> courseDict, string courses)
    {
        List<CourseMiniOutput> ret = [];
        List<string> errs = [];

        var cns = courses.ClearWhitespace().Split(['、', '，', ',', ';', '；']);
        foreach (var cn in cns)
        {
            if (courseDict.TryGetValue(cn, out CourseLiteOutput c))
            {
                if (ret.Any(t => t.Id != c.Id))
                {
                    ret.Add(c.Adapt<CourseMiniOutput>());
                }
            }
            else
            {
                errs.Add(cn);
            }
        }
        return (errs, ret);
    }
    #endregion

    #region 私有方法
    /// <summary>
    /// 构建查询
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    private IQueryable<ExamAbsentReplace> GetQueryBase(ExamAbsentReplacePageInput input)
    {
        var orgId = input.SysOrgId ?? CurrentSysUserInfo.SysOrgId;

        var absentName = !string.IsNullOrEmpty(input.AbsentName?.Trim());
        var absentExamNumber = !string.IsNullOrEmpty(input.AbsentExamNumber?.Trim());
        var absentReason = !string.IsNullOrEmpty(input.AbsentReason?.Trim());
        var replaceName = !string.IsNullOrEmpty(input.ReplaceName?.Trim());
        var replaceExamNumber = !string.IsNullOrEmpty(input.ReplaceExamNumber?.Trim());
        var patriarchTel = !string.IsNullOrEmpty(input.PatriarchTel?.Trim());
        var remark = !string.IsNullOrEmpty(input.Remark?.Trim());

        var query = rep.DetachedEntities.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId)
                                         .Where((absentName, u => EF.Functions.Like(u.AbsentName, $"%{input.AbsentName.Trim()}%")))
                                         .Where((absentExamNumber, u => EF.Functions.Like(u.AbsentExamNumber, $"%{input.AbsentExamNumber.Trim()}%")))
                                         .Where((absentReason, u => EF.Functions.Like(u.AbsentReason, $"%{input.AbsentReason.Trim()}%")))
                                         .Where((replaceName, u => EF.Functions.Like(u.ReplaceName, $"%{input.ReplaceName.Trim()}%")))
                                         .Where((replaceExamNumber, u => EF.Functions.Like(u.ReplaceExamNumber, $"%{input.ReplaceExamNumber.Trim()}%")))
                                         .Where((patriarchTel, u => EF.Functions.Like(u.PatriarchTel, $"%{input.PatriarchTel.Trim()}%")))
                                         .Where((remark, u => EF.Functions.Like(u.Remark, $"%{input.Remark.Trim()}%")))
                                         .Where((input.AbentCourseId.HasValue, u => EF.Functions.Like(u.AbsentCourses, $"%{input.AbentCourseId}%")))
                                         .Where(input.Status.HasValue, t => t.Status == input.Status)
                                         .Where(input.IsReplaced.HasValue, t => t.IsReplaced == input.IsReplaced)
                                         .Where(input.GradeId.HasValue, t => t.GradeId == input.GradeId)
                                         .Where(input.ClassNumber.HasValue, t => t.ClassNumber == input.ClassNumber)
                                         .Where(input.SysOrgBranchId.HasValue, t => t.SchoolClass.SysOrgBranchId == input.SysOrgBranchId);
        return query;
    }
    /// <summary>
    /// 设置单元格文本
    /// </summary>
    /// <param name="table"></param>
    /// <param name="text"></param>
    /// <param name="align"></param>
    /// <param name="isBold"></param>
    /// <returns></returns>
    private static XWPFParagraph SetCellText(XWPFTable table, string text, ParagraphAlignment align = ParagraphAlignment.CENTER, bool isBold = false)
    {
        CT_P para = new();
        XWPFParagraph pCell = new(para, table.Body)
        {
            Alignment = align,
            VerticalAlignment = TextAlignment.CENTER,
        };

        XWPFRun r1 = pCell.CreateRun();
        r1.SetText(text);
        r1.FontSize = 11;
        r1.FontFamily = "宋体";
        r1.IsBold = isBold;

        return pCell;
    }
    /// <summary>
    /// 合并单元格
    /// </summary>
    /// <param name="table"></param>
    /// <param name="fromCol"></param>
    /// <param name="toCol"></param>
    /// <param name="fromRow"></param>
    /// <param name="toRow"></param>
    /// <returns></returns>
    private static XWPFTableCell MergeCells(XWPFTable table, int fromCol, int toCol, int fromRow, int toRow)
    {
        for (int rowIndex = fromRow; rowIndex <= toRow; rowIndex++)
        {
            if (fromCol < toCol)
            {
                table.GetRow(rowIndex).MergeCells(fromCol, toCol);
            }
            XWPFTableCell rowcell = table.GetRow(rowIndex).GetCell(fromCol);
            CT_Tc cttc = rowcell.GetCTTc();
            if (cttc.tcPr == null)
            {
                cttc.AddNewTcPr();
            }
            if (rowIndex == fromRow)
            {
                // The first merged cell is set with RESTART merge value  
                rowcell.GetCTTc().tcPr.AddNewVMerge().val = ST_Merge.restart;
            }
            else
            {
                // Cells which join (merge) the first one, are set with CONTINUE  
                rowcell.GetCTTc().tcPr.AddNewVMerge().val = ST_Merge.@continue;
            }
        }
        table.GetRow(fromRow).GetCell(fromCol).SetVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);
        //table.GetRow(fromRow).GetCell(fromCol).Paragraphs[0].Alignment = ParagraphAlignment.CENTER;
        table.GetRow(fromRow).GetCell(fromCol).Paragraphs[0].Alignment = ParagraphAlignment.LEFT;
        return table.GetRow(fromRow).GetCell(fromCol);
    }
    #endregion
}