YBEE.EQM/YBEE.EQM.Application/Exam/ExamSpecialStudent/Services/ExamSpecialStudentService.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 class ExamSpecialStudentService : IExamSpecialStudentService, ITransient
{
    private readonly IRepository<ExamSpecialStudent> _rep;
    private readonly IExamGradeService _examGradeService;
    private readonly ISysDictDataService _sysDictDataService;
    private readonly ISchoolClassService _schoolClassService;
    private readonly IResourceFileService _resourceFileService;
    private readonly ICourseService _courseService;

    public ExamSpecialStudentService(IRepository<ExamSpecialStudent> rep, IExamGradeService examGradeService, ISysDictDataService sysDictDataService, ISchoolClassService schoolClassService, IResourceFileService resourceFileService, ICourseService courseService)
    {
        _rep = rep;
        _examGradeService = examGradeService;
        _sysDictDataService = sysDictDataService;
        _schoolClassService = schoolClassService;
        _resourceFileService = resourceFileService;
        _courseService = courseService;
    }

    #region 批量导入
    /// <summary>
    /// 上传监测特殊学生批量导入文件
    /// </summary>
    /// <param name="filePath"></param>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task<UploadExamDataOutput<UploadExamSpecialStudentOutput>> Upload(string filePath, int examPlanId)
    {
        UploadExamDataOutput<UploadExamSpecialStudentOutput> 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 CERT_TYPE_INDEX = index++;
            int ID_NUM_INDEX = index++;
            int GENDER_INDEX = index++;
            int REASON_INDEX = index++;
            int TEL_INDEX = index++;
            int REMARK_INDEX = index;
            Dictionary<int, string> headers = new()
            {
                { GRADE_INDEX, "年级号" },
                { CLASS_INDEX, "班级号" },
                { NAME_INDEX, "姓名" },
                { CERT_TYPE_INDEX, "证件类型" },
                { ID_NUM_INDEX, "证件号码" },
                { GENDER_INDEX, "性别" },
                { REASON_INDEX, "特殊原因" },
                { TEL_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 cts = await _sysDictDataService.GetListByDictTypeId(304);
            var certificateTypes = cts.ToDictionary(x => x.Name, y => y.Value);

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

                UploadExamSpecialStudentOutput 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.Name = StringUtil.ClearWhite(row.GetCell(NAME_INDEX)?.ToString() ?? "");
                if (item.Name == "" || item.Name.Length > 100)
                {
                    item.ErrorMessage.Add($"{headers[NAME_INDEX]}未填或超出100字");
                }
                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.ApplyReason = StringUtil.ClearWhite(row.GetCell(REASON_INDEX)?.ToString() ?? "");
                if (item.ApplyReason == "" || item.ApplyReason.Length > 2000)
                {
                    item.ErrorMessage.Add($"{headers[REASON_INDEX]}未填或超出2000字");
                }
                if (item.ApplyReason.Length > 2000) { item.ApplyReason = item.ApplyReason[..2000]; }

                // 家长电话
                item.PatriarchTel = StringUtil.ClearWhite(row.GetCell(TEL_INDEX)?.ToString() ?? "");
                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.Remark = StringUtil.ClearWhite(row.GetCell(REMARK_INDEX)?.ToString() ?? "");
                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(ImportExamSpecialStudentInput input)
    {
        var orgId = input.SysOrgId ?? CurrentSysUserInfo.SysOrgId;

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

        //// 删除同一监测计划中监测机构内所有数据
        //await _rep.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId && t.SysOrgBranchId == input.SysOrgBranchId).ExecuteDeleteAsync();
        List<ExamSpecialStudent> items = new();

        var spStus = input.Items.Where(t => !existsIdNumberList.Contains(t.IdNumber)).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();

        // 往期已认定记录
        var preItems = await _rep.Change<SpecialStudent>().DetachedEntities.Where(t => t.SysOrgId == orgId).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<ExamSpecialStudent>();
                item.ExamPlanId = input.ExamPlanId;
                item.SysOrgId = orgId;
                item.SysOrgBranchId = input.SysOrgBranchId;
                item.SchoolClassId = classDict[ni.ClassNumber];
                item.Name = StringUtil.ClearWhite(item.Name);
                item.IdNumber = StringUtil.ClearIdNumber(item.IdNumber);
                item.IsPreIdentified = preItems.Any(t => t.CertificateType == item.CertificateType && t.IdNumber.ToUpper() == item.IdNumber);
                items.Add(item);
                c++;
            }
        }

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

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

        // 检测同一监测计划中同机构内是否有相同证件号码的学生
        var sameItems = await _rep.DetachedEntities.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId && t.CertificateType == input.CertificateType && t.IdNumber.ToUpper() == input.IdNumber.ToUpper()).ProjectToType<ExamSpecialStudentOutput>().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<ExamSpecialStudent>();
        item.SysOrgId = orgId;
        item.SchoolClassId = schoolClass.Id;
        item.Name = StringUtil.ClearWhite(item.Name);
        item.IdNumber = StringUtil.ClearIdNumber(item.IdNumber);
        item.IsPreIdentified = await _rep.Change<SpecialStudent>().DetachedEntities.AnyAsync(t => t.SysOrgId == orgId && t.CertificateType == item.CertificateType && t.IdNumber.ToUpper() == item.IdNumber);

        ////item.Attachments = new List<AttachmentItem>() { new () { FileId = 1, FileName = "a.png", FileExtName = ".png" } }.ToJson();
        //var k = new List<AttachmentItem>() { new() { FileId = 1, FileName = "a.png", FileExtName = ".png" } };
        //item.Attachments = JSON.Serialize(k);

        await item.InsertAsync();
    }
    /// <summary>
    /// 更新监测特殊学生
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Update(UpdateExamSpecialStudentInput 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<ExamSpecialStudent>();
        item.SchoolClassId = schoolClass.Id;
        item.Name = StringUtil.ClearWhite(item.Name);
        item.IdNumber = StringUtil.ClearIdNumber(item.IdNumber);
        item.IsPreIdentified = await _rep.Change<SpecialStudent>().DetachedEntities.AnyAsync(t => t.SysOrgId == item.SysOrgId && t.CertificateType == item.CertificateType && t.IdNumber.ToUpper() == item.IdNumber);

        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.StudentNumber),
            nameof(item.Remark),
            nameof(item.SysOrgBranchId),
            nameof(item.ApplyReason),
            nameof(item.PatriarchName),
            nameof(item.PatriarchTel),
            nameof(item.IsPreIdentified),
        });
    }
    /// <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);
        //var attachments = JSON.Deserialize<List<AttachmentItem>>(item.Attachments);
        //attachments.Add(input.Adapt<AttachmentItem>());
        //item.Attachments = JSON.Serialize(attachments);
        item.Attachments = AttachmentUtil.InsertInto(item.Attachments, input.Adapt<AttachmentItem>());
        await item.UpdateIncludeAsync(new[] { 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<ExamSpecialStudentOutput>().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.Any())
        {
            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(ClearExamSpecialStudentInput 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.Any())
        {
            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.portrait;
        sectPr.pgSz.w = 11906;
        sectPr.pgSz.h = 16838;
        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("重庆市", "")}特殊学生明细表（{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 = 7;
            XWPFTable table = doc.CreateTable(gitem.Count() + 1, 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, 580);
            table.SetColumnWidth(4, 1070);
            table.SetColumnWidth(5, 1660);
            table.SetColumnWidth(6, 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));
            for (int ci = 0; ci < COLUMN_COUNT; ci++)
            {
                table.GetRow(ri).GetCell(ci).GetCTTc().AddNewTcPr().AddNewVAlign().val = ST_VerticalJc.center;
            }
            int tableRowNumber = 1;
            foreach (var item in gitem)
            {
                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.Name, ParagraphAlignment.CENTER));
                table.GetRow(ri).GetCell(3).SetParagraph(SetCellText(table, certificateTypes[item.CertificateType], ParagraphAlignment.CENTER));
                table.GetRow(ri).GetCell(4).SetParagraph(SetCellText(table, item.IdNumber, ParagraphAlignment.CENTER));
                table.GetRow(ri).GetCell(5).SetParagraph(SetCellText(table, item.ApplyReason, ParagraphAlignment.LEFT));
                table.GetRow(ri).GetCell(6).SetParagraph(SetCellText(table, item.PatriarchTel, ParagraphAlignment.CENTER));

                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<ExamSpecialStudentOutput>> QueryPageList(ExamSpecialStudentPageInput input)
    {
        var orgId = input.SysOrgId ?? CurrentSysUserInfo.SysOrgId;

        var query = GetQueryBase(input);
        //query = query.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId);
        var ret = await query.OrderBy(t => t.GradeId).ThenBy(t => t.ClassNumber).ThenBy(t => t.Id).ProjectToType<ExamSpecialStudentOutput>().ToADPagedListAsync(input.PageIndex, input.PageSize);
        return ret;
    }
    /// <summary>
    /// 分页查询监测特殊学生列表
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task<PageResult<ExamSpecialStudentOutput>> QueryAuditPageList(ExamSpecialStudentPageInput input)
    {
        var orgId = input.SysOrgId ?? CurrentSysUserInfo.SysOrgId;

        var query = GetQueryBase(input);
        //query = query.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId);
        var ret = await query.OrderBy(t => t.GradeId).ThenBy(t => t.ClassNumber).ThenBy(t => t.Id).ProjectToType<ExamSpecialStudentOutput>().ToADPagedListAsync(input.PageIndex, input.PageSize);
        return ret;
    }
    /// <summary>
    /// 获取机构班级特殊学生上报人数统计列表
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <param name="sysOrgId"></param>
    /// <returns></returns>
    public async Task<ExamSpecialStudentCountOutput> 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(ExamSpecialStudentPageInput input)
    {
        var query = GetQueryBase(input);
        if (query == null)
        {
            return new List<StatusCount>();
        }
        var counts = await query.GroupBy(t => t.Status).Select(t => new StatusCount { Status = (int)t.Key, Count = t.Count() }).ToListAsync();
        return counts;
    }
    #endregion

    public async Task RefreshFileSize()
    {
        var items = await _rep.Entities.ToListAsync();
        foreach (var item in items)
        {
            var ats = AttachmentUtil.GetList(item.Attachments);
            foreach (var at in ats)
            {
                var file = await _rep.Change<ResourceFile>().DetachedEntities.FirstOrDefaultAsync(t => t.Id == at.FileId) ?? throw new Exception("fuckfuck");
                at.FileSize = file.FileSize;
            }
            item.Attachments = JSON.Serialize(ats);
        }
        await _rep.UpdateAsync(items);
    }

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

        var name = !string.IsNullOrEmpty(input.Name?.Trim());
        var idNumber = !string.IsNullOrEmpty(input.IdNumber?.Trim());
        var applyReason = !string.IsNullOrEmpty(input.ApplyReason?.Trim());
        var patriarchTel = !string.IsNullOrEmpty(input.PatriarchTel?.Trim());
        var studentNumber = !string.IsNullOrEmpty(input.StudentNumber?.Trim());

        var query = _rep.DetachedEntities.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId)
                                         .Where((name, u => EF.Functions.Like(u.Name, $"%{input.Name.Trim()}%")))
                                         .Where((idNumber, u => EF.Functions.Like(u.IdNumber, $"%{input.IdNumber.Trim()}%")))
                                         .Where((studentNumber, u => EF.Functions.Like(u.StudentNumber, $"%{input.StudentNumber.Trim()}%")))
                                         .Where((applyReason, u => EF.Functions.Like(u.ApplyReason, $"%{input.ApplyReason.Trim()}%")))
                                         .Where((patriarchTel, u => EF.Functions.Like(u.PatriarchTel, $"%{input.PatriarchTel.Trim()}%")))
                                         .Where(input.Status.HasValue, t => t.Status == input.Status)
                                         .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.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,
            //SpacingLineRule = LineSpacingRule.AUTO,
        };
        //pCell.setSpacingBetween(1.5, LineSpacingRule.AUTO);

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

        //r1c1.SetTextPosition(20);//设置高度 
        return pCell;
    }
    #endregion
}