YBEE.EQM/YBEE.EQM.Application/Exam/ExamSample/Services/ExamSampleService.cs

using Furion.DatabaseAccessor.Extensions;
using Furion.JsonSerialization;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using YBEE.EQM.Core;

namespace YBEE.EQM.Application;

/// <summary>
/// 监测抽样方案管理服务
/// </summary>
public class ExamSampleService : IExamSampleService, ITransient
{
    private readonly IRepository<ExamSample> _rep;
    private readonly IExportExcelService _exportExcelService;
    private readonly ISysDictDataService _sysDictDataService;

    public ExamSampleService(IRepository<ExamSample> rep, IExportExcelService exportExcelService, ISysDictDataService sysDictDataService)
    {
        _rep = rep;
        _exportExcelService = exportExcelService;
        _sysDictDataService = sysDictDataService;
    }

    #region 方案管理
    /// <summary>
    /// 添加监测抽样方案
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Add(AddExamSampleInput input)
    {
        var examPlan = await _rep.Change<ExamPlan>().DetachedEntities.FirstOrDefaultAsync(t => t.Id == input.ExamPlanId) ?? throw Oops.Oh(ErrorCode.E2001, "监测计划");
        var maxSeq = await _rep.DetachedEntities.Where(t => t.ExamPlanId == input.ExamPlanId).MaxAsync(t => (short?)t.Sequence);

        var item = input.Adapt<ExamSample>();
        item.Sequence = (short)(maxSeq.HasValue ? (maxSeq + 1) : 1);
        string sampleName = $"学生抽样方案{ConvertUtil.ConvertToChinese(item.Sequence)}";
        item.FullName = $"{examPlan.FullName}{sampleName}";
        item.Name = $"{examPlan.Name}{sampleName}";
        item.ShortName = $"{examPlan.ShortName}{sampleName}";
        await item.InsertAsync();
    }
    /// <summary>
    /// 更新监测抽样方案
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Update(UpdateExamSampleInput input)
    {
        if (!await _rep.AnyAsync(t => t.Id == input.Id))
        {
            throw Oops.Oh(ErrorCode.E2001);
        }
        var item = input.Adapt<ExamSample>();
        await item.UpdateIncludeAsync(new[] {
            nameof(item.Name),
            nameof(item.FullName),
            nameof(item.ShortName),
            nameof(item.Remark),
            nameof(item.Config)
        });
    }
    /// <summary>
    /// 复制抽样方案信息
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Duplicate(BaseId input)
    {
        var item = await _rep.DetachedEntities.FirstOrDefaultAsync(t => t.Id == input.Id) ?? throw Oops.Oh(ErrorCode.E2001);
        var examPlan = await _rep.Change<ExamPlan>().DetachedEntities.FirstOrDefaultAsync(t => t.Id == item.ExamPlanId) ?? throw Oops.Oh(ErrorCode.E2001, "监测计划");
        var maxSeq = await _rep.DetachedEntities.Where(t => t.ExamPlanId == item.ExamPlanId).MaxAsync(t => (short?)t.Sequence);
        short sequence = (short)(maxSeq.HasValue ? (maxSeq + 1) : 1);
        string sampleName = $"学生抽样方案{ConvertUtil.ConvertToChinese(sequence)}";
        ExamSample newItem = new()
        {
            ExamPlanId = item.ExamPlanId,
            Sequence = sequence,
            FullName = $"{examPlan.FullName}{sampleName}",
            Name = $"{examPlan.Name}{sampleName}",
            ShortName = $"{examPlan.ShortName}{sampleName}",
            Status = ExamSampleStatus.INITIAL,
            Config = item.Config,
            Remark = item.Remark,
        };
        await newItem.InsertAsync();
    }
    /// <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 examPlan = await _rep.Change<ExamPlan>().DetachedEntities.FirstOrDefaultAsync(t => t.Id == item.ExamPlanId) ?? throw Oops.Oh(ErrorCode.E2001, "监测计划");
        if (examPlan.IsFixedExamSample || item.Status == ExamSampleStatus.RUNNING || item.IsSelected == true)
        {
            throw Oops.Oh(ErrorCode.E3001);
        }

        // 批量删除已抽测学生
        await _rep.Change<ExamSampleStudent>().Where(t => t.ExamSampleId == item.Id).ExecuteDeleteAsync();
        // 删除抽样方案
        await item.DeleteNowAsync();
    }
    /// <summary>
    /// 保存全抽班级ID列表
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task SaveExamSampleAllClasses(SaveExamSampleAllClasses input)
    {
        var item = await _rep.FirstOrDefaultAsync(t => t.Id == input.Id) ?? throw Oops.Oh(ErrorCode.E2001);
        var config = JSON.Deserialize<ExamSampleConfig>(item.Config);
        config.SampleAllSchoolClassIds = input.ClassIds;
        item.Config = JSON.Serialize(config);
        await item.UpdateIncludeAsync(new[] { nameof(item.Config) });
    }
    /// <summary>
    /// 切换全抽班级
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task SwitchExamSampleAllClass(SwitchExamSampleAllClassInput input)
    {
        var item = await _rep.FirstOrDefaultAsync(t => t.Id == input.Id) ?? throw Oops.Oh(ErrorCode.E2001);
        var config = JSON.Deserialize<ExamSampleConfig>(item.Config);
        if (input.IsAdd && !config.SampleAllSchoolClassIds.Any(t => t == input.SchoolClassId))
        {
            config.SampleAllSchoolClassIds.Add(input.SchoolClassId);
        }
        else if (!input.IsAdd && config.SampleAllSchoolClassIds.Any(t => t == input.SchoolClassId))
        {
            config.SampleAllSchoolClassIds.Remove(input.SchoolClassId);
        }
        item.Config = JSON.Serialize(config);
        await item.UpdateIncludeAsync(new[] { nameof(item.Config) });
    }
    /// <summary>
    /// 选定方案
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task SelectSample(BaseId input)
    {
        var item = await _rep.FirstOrDefaultAsync(t => t.Id == input.Id) ?? throw Oops.Oh(ErrorCode.E2001);
        var examPlan = await _rep.Change<ExamPlan>().FirstOrDefaultAsync(t => t.Id == item.ExamPlanId) ?? throw Oops.Oh(ErrorCode.E2001, "监测计划");
        item.IsSelected = true;
        item.SelectedTime = DateTime.Now;
        item.SelectedSysUserId = CurrentSysUserInfo.SysUserId;
        examPlan.IsFixedExamSample = true;
        await item.UpdateAsync();
        await examPlan.UpdateAsync();
    }
    /// <summary>
    /// 执行抽样
    /// </summary>
    /// <param name="input">抽样方案ID</param>
    /// <returns></returns>
    /// <exception cref="Exception"></exception>
    public async Task ExecuteSample(BaseId input)
    {
        // 抽样方案
        var item = await _rep.FirstOrDefaultAsync(t => t.Id == input.Id) ?? throw Oops.Oh(ErrorCode.E2001, "抽样方案");
        // 监测计划
        var plan = await _rep.Change<ExamPlan>().DetachedEntities
                                                .ProjectToType<ExamPlanOutput>()
                                                .FirstOrDefaultAsync(t => t.Id == item.ExamPlanId) ?? throw Oops.Oh(ErrorCode.E2001, "监测计划");
        // 监测年级字典
        var examGradeDict = plan.ExamGrades.ToDictionary(t => t.GradeId);
        var examSample = item.Adapt<ExamSampleOutput>();

        try
        {
            var sampleItem = item.Adapt<ExamSampleOutput>();

            // 更新状态为【生成中】
            item.Status = ExamSampleStatus.RUNNING;
            await item.UpdateIncludeNowAsync(new[] { nameof(item.Status) });

            // 获取所有学生信息
            var stus = await GetStudentScoreList(sampleItem.Config, item.ExamPlanId, plan.ExamGrades.Select(t => t.GradeId).Distinct().ToList());
            // 获取抽样学生信息
            var sampleStus = GetSampleList(sampleItem.Config, examGradeDict, stus);
            // 获取最终抽样学生列表
            var finalSampleStus = GetFinalSampleList(examSample, examGradeDict, sampleStus);

            // 删除已存在数据
            string deleteSql = $"DELETE FROM exam_sample_student WHERE exam_sample_id = {input.Id}";
            await _rep.SqlNonQueryAsync(deleteSql);

            #region 批量写入
            int si = 0;
            int stuCount = finalSampleStus.Count;
            List<string> insertValues = new();
            foreach (var stu in finalSampleStus)
            {
                si++;
                var iss = stu.IsSpecialStudent ? 1 : 0;
                string valueSql = $"({stu.ExamSampleId}, {stu.ExamStudentId}, '{stu.ExamNumber}', {stu.Sequence}, {(short)stu.ExamSampleType}, {iss}, {stu.PreTotalScore})";
                insertValues.Add(valueSql);
                // 一次写入2000行
                if (si % 2000 == 0 || si == stuCount)
                {
                    string insertSql = $"INSERT INTO exam_sample_student(exam_sample_id, exam_student_id, exam_number, sequence, exam_sample_type, is_special_student, pre_total_score) VALUES {string.Join(", ", insertValues)}";
                    await _rep.SqlNonQueryAsync(insertSql);
                    insertValues.Clear();
                }
            }
            #endregion

            // 更新状态为【已生成】
            item.Status = ExamSampleStatus.SUCCESSFUL;
            await item.UpdateIncludeNowAsync(new[] { nameof(item.Status) });
        }
        catch (Exception ex)
        {
            // 更新状态为【已失败】
            item.Status = ExamSampleStatus.FAILED;
            await item.UpdateIncludeNowAsync(new[] { nameof(item.Status) });
            throw new Exception(ex.Message);
        }
    }
    #endregion

    #region 导出抽样数据
    /// <summary>
    /// 导出抽样方案存档文件
    /// </summary>
    /// <param name="id"></param>
    /// <param name="hideIdNumber"></param>
    /// <returns></returns>
    public async Task<(string fileName, byte[] fileBytes)> ExportToArchived(int id, bool hideIdNumber = false)
    {
        // 抽样方案
        var examSample = await _rep.DetachedEntities.FirstOrDefaultAsync(t => t.Id == id) ?? throw Oops.Oh(ErrorCode.E2001, "抽样方案");
        // 监测计划
        var plan = await _rep.Change<ExamPlan>().DetachedEntities
                                                .ProjectToType<ExamPlanOutput>()
                                                .FirstOrDefaultAsync(t => t.Id == examSample.ExamPlanId) ?? throw Oops.Oh(ErrorCode.E2001, "监测计划");
        // 监测年级字典
        var examGradeDict = plan.ExamGrades.ToDictionary(t => t.GradeId);
        // 获取证件类型
        var cts = await _sysDictDataService.GetListByDictTypeId(304);
        var certificateTypes = cts.ToDictionary(x => (Core.CertificateType)x.Value, y => y.Name);

        // 临时存放目录
        string fileRoot = Path.Combine(FileUtil.GetTempFileRoot(), $"{Guid.NewGuid()}");
        Directory.CreateDirectory(fileRoot);
        // 抽样文件目录
        string sampleFileRoot = Path.Combine(fileRoot, examSample.FullName);

        string selectStusSql = @$"
SELECT 
	T2.sys_org_id, 
	T4.full_name AS sys_org_full_name, 
	T4.`name` AS sys_org_name,
	T4.`code` AS sys_org_code,
	T2.sys_org_branch_id,
	T5.`name` AS sys_org_branch_name,
	T2.grade_id,
	T3.grade_number,
	T3.`name` AS grade_name,
	T2.school_class_id,
	T2.class_number,
	T1.exam_student_id,
	T2.`name` AS exam_student_name,
	T2.certificate_type,
	T2.id_number,
	T1.exam_number,
	T1.exam_sample_type
FROM exam_sample_student AS T1
JOIN exam_student AS T2 ON T1.exam_student_id = T2.id
JOIN base_grade AS T3 ON T2.grade_id = T3.id
JOIN sys_org AS T4 ON T2.sys_org_id = T4.id
LEFT JOIN sys_org AS T5 ON T2.sys_org_branch_id = T5.id
WHERE T1.exam_sample_id = {id}
";
        // 所有学生
        var stus = await _rep.SqlQueriesAsync<ExamSampleStudentExportDto>(selectStusSql);


        // 按年级生成考生文件
        var ws = stus.Select(t => new { t.SysOrgId, t.SysOrgFullName, t.SysOrgCode, t.SysOrgBranchId, t.SysOrgBranchName, t.GradeId })
                     .OrderBy(t => t.SysOrgId).ThenBy(t => t.SysOrgBranchId).ThenBy(t => t.GradeId).Distinct().ToList();
        foreach (var w in ws)
        {
            // 监测年级
            var eg = examGradeDict[w.GradeId];

            // 学校目录
            string orgDir = Path.Combine(sampleFileRoot, $"{w.SysOrgCode}-{w.SysOrgFullName}");
            if (!Directory.Exists(orgDir))
            {
                Directory.CreateDirectory(orgDir);
            }

            // 校区名称
            string branchName = "";
            if (w.SysOrgBranchId != null)
            {
                branchName = $"@{w.SysOrgBranchName}";
            }

            #region 导出抽中学生
            string sampleFileName = $"{w.SysOrgCode}-{w.SysOrgFullName}{branchName}-{eg.Grade.Name}-{ExamSampleType.DISTRICT.GetDescription()}-学生检录表";
            // 抽中学生列表
            var sampleStus = stus.Where(t => t.SysOrgId == w.SysOrgId && t.SysOrgBranchId == w.SysOrgBranchId && t.GradeId == w.GradeId && t.ExamSampleType == ExamSampleType.DISTRICT)
                                 .OrderBy(t => t.ExamNumber?[^4..]).ToList();
            // 自编号按导入顺序            
            if (eg.IsRequiredSelfExamNumber)
            {
                sampleStus = sampleStus.OrderBy(t => t.ExamStudentId).ToList();
            }

            // 定义EXCEL列
            List<ExportExcelColDto<ExamSampleStudentExportDto>> cols = new()
            {
                new() { Name = "年级号", Width = 8, GetCellValue = (r) => r.GradeNumber },
                new() { Name = "班级号", Width = 8, GetCellValue = (r) => r.ClassNumber },
                new() { Name = "监测号", Width = 14, GetCellValue = (r) => r.ExamNumber },
                new() { Name = "姓名", Width = 20, GetCellValue = (r) => r.ExamStudentName },
            };
            if (w.SysOrgBranchId != null)
            {
                cols.Insert(0, new() { Name = "校区", Width = 10, GetCellValue = (r) => r.SysOrgBranchName });
            }
            if (!hideIdNumber)
            {
                cols.Add(new() { Name = "证件类型", Width = 16, GetCellValue = (r) => certificateTypes[r.CertificateType] });
                cols.Add(new() { Name = "证件号码", Width = 20, GetCellValue = (r) => r.IdNumber });
            }
            cols.Add(new() { Name = "名单类型", Width = 10, GetCellValue = (r) => r.ExamSampleType.GetDescription() });

            // 导出EXCEL文件
            var ret = _exportExcelService.ExportExcel(new ExportExcelDto<ExamSampleStudentExportDto>()
            {
                Title = sampleFileName,
                Columns = cols,
                Items = sampleStus,
                IncludeExportTime = false,
            });
            // 写入文件
            await File.WriteAllBytesAsync(Path.Combine(orgDir, $"{sampleFileName}.xlsx"), ret);
            #endregion

            #region 导出未抽中学生
            string noSampleFileName = $"{w.SysOrgCode}-{w.SysOrgFullName}{branchName}-{eg.Grade.Name}-{ExamSampleType.SCHOOL_EXAM.GetDescription()}-学生检录表";
            // 抽中学生列表
            var noSampleStus = stus.Where(t => t.SysOrgId == w.SysOrgId && t.SysOrgBranchId == w.SysOrgBranchId && t.GradeId == w.GradeId && t.ExamSampleType == ExamSampleType.SCHOOL_EXAM)
                                 .OrderBy(t => t.ExamNumber?[^4..]).ToList();
            // 自编号按导入顺序
            if (eg.IsRequiredSelfExamNumber)
            {
                noSampleStus = noSampleStus.OrderBy(t => t.ExamStudentId).ToList();
            }

            // 定义EXCEL列
            List<ExportExcelColDto<ExamSampleStudentExportDto>> noCols = new()
            {
                new() { Name = "年级号", Width = 8, GetCellValue = (r) => r.GradeNumber },
                new() { Name = "班级号", Width = 8, GetCellValue = (r) => r.ClassNumber },
                new() { Name = "考号", Width = 14, GetCellValue = (r) => r.ExamNumber },
                new() { Name = "姓名", Width = 20, GetCellValue = (r) => r.ExamStudentName },
            };
            if (w.SysOrgBranchId != null)
            {
                noCols.Insert(0, new() { Name = "校区", Width = 10, GetCellValue = (r) => r.SysOrgBranchName });
            }
            if (!hideIdNumber)
            {
                noCols.Add(new() { Name = "证件类型", Width = 16, GetCellValue = (r) => certificateTypes[r.CertificateType] });
                noCols.Add(new() { Name = "证件号码", Width = 20, GetCellValue = (r) => r.IdNumber });
            }
            noCols.Add(new() { Name = "名单类型", Width = 10, GetCellValue = (r) => r.ExamSampleType.GetDescription() });

            // 导出EXCEL文件
            var noRet = _exportExcelService.ExportExcel(new ExportExcelDto<ExamSampleStudentExportDto>()
            {
                Title = noSampleFileName,
                Columns = noCols,
                Items = noSampleStus,
                IncludeExportTime = false,
            });
            // 写入文件
            await File.WriteAllBytesAsync(Path.Combine(orgDir, $"{noSampleFileName}.xlsx"), noRet);
            #endregion
        }

        // 统计表文件
        var countFile = await ExportSampleCount(id);
        await File.WriteAllBytesAsync(Path.Combine(sampleFileRoot, countFile.fileName), countFile.fileBytes);

        string outFileName = $"{examSample.FullName}.zip";
        string outFilePath = Path.Combine(fileRoot, outFileName);
        ICSharpCode.SharpZipLib.Zip.FastZip zip = new();
        zip.CreateZip(outFilePath, sampleFileRoot, true, string.Empty);

        var retBytes = await File.ReadAllBytesAsync(outFilePath);
        Directory.Delete(fileRoot, true);
        return (outFileName, retBytes);
    }
    /// <summary>
    /// 导出给印刷厂和网阅机构文件
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public async Task<(string fileName, byte[] fileBytes)> ExportToPrintshop(int id)
    {
        var item = await _rep.DetachedEntities.FirstOrDefaultAsync(t => t.Id == id && t.Status == ExamSampleStatus.SUCCESSFUL && t.IsSelected == true);
        return item == null ? throw Oops.Oh(ErrorCode.E2006) : await ExportToArchived(id, true);
    }
    /// <summary>
    /// 导出给学校
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public async Task<(string fileName, byte[] fileBytes)> ExportToOrg(int id)
    {
        // 抽样方案
        var examSample = await _rep.DetachedEntities.FirstOrDefaultAsync(t => t.Id == id && t.ExamPlan.IsFixedExamSample == true && t.Status == ExamSampleStatus.SUCCESSFUL && t.IsSelected == true) ?? throw Oops.Oh(ErrorCode.E2001, "抽样方案");
        // 监测计划
        var plan = await _rep.Change<ExamPlan>().DetachedEntities
                                                .ProjectToType<ExamPlanOutput>()
                                                .FirstOrDefaultAsync(t => t.Id == examSample.ExamPlanId) ?? throw Oops.Oh(ErrorCode.E2001, "监测计划");

        // 必须发布了的才能下载
        var pb = await _rep.Change<ExamDataPublish>().DetachedEntities.AnyAsync(t => t.ExamPlanId == plan.Id && t.Type == DataPublishType.STUDENT_SAMPLE_LIST && t.Status == PublishStatus.PUBLISHED);
        if (!pb)
        {
            throw Oops.Oh(ErrorCode.E2006);
        }

        // 监测年级字典
        var examGradeDict = plan.ExamGrades.ToDictionary(t => t.GradeId);
        // 获取证件类型
        var cts = await _sysDictDataService.GetListByDictTypeId(304);
        var certificateTypes = cts.ToDictionary(x => (Core.CertificateType)x.Value, y => y.Name);

        // 临时存放目录
        string fileRoot = Path.Combine(FileUtil.GetTempFileRoot(), $"{Guid.NewGuid()}");
        Directory.CreateDirectory(fileRoot);

        string selectStusSql = @$"
SELECT 
	T2.sys_org_id, 
	T4.full_name AS sys_org_full_name, 
	T4.`name` AS sys_org_name,
	T4.`code` AS sys_org_code,
	T2.sys_org_branch_id,
	T5.`name` AS sys_org_branch_name,
	T2.grade_id,
	T3.grade_number,
	T3.`name` AS grade_name,
	T2.school_class_id,
	T2.class_number,
	T1.exam_student_id,
	T2.`name` AS exam_student_name,
	T2.certificate_type,
	T2.id_number,
	T1.exam_number,
	T1.exam_sample_type
FROM exam_sample_student AS T1
JOIN exam_student AS T2 ON T1.exam_student_id = T2.id
JOIN base_grade AS T3 ON T2.grade_id = T3.id
JOIN sys_org AS T4 ON T2.sys_org_id = T4.id
LEFT JOIN sys_org AS T5 ON T2.sys_org_branch_id = T5.id
WHERE T1.exam_sample_id = {id} AND T2.sys_org_id = {CurrentSysUserInfo.SysOrgId}
";
        // 所有学生
        var stus = await _rep.SqlQueriesAsync<ExamSampleStudentExportDto>(selectStusSql);


        // 按年级生成考生文件
        var ws = stus.Select(t => new { t.SysOrgId, t.SysOrgFullName, t.SysOrgCode, t.SysOrgBranchId, t.SysOrgBranchName, t.GradeId })
                     .OrderBy(t => t.SysOrgId).ThenBy(t => t.SysOrgBranchId).ThenBy(t => t.GradeId).Distinct().ToList();
        string fileName = "";
        foreach (var w in ws)
        {
            // 监测年级
            var eg = examGradeDict[w.GradeId];

            // 学校目录
            if (fileName == "")
            {
                fileName = $"{w.SysOrgCode}-{w.SysOrgFullName}";
            }
            string orgDir = Path.Combine(fileRoot, fileName);
            if (!Directory.Exists(orgDir))
            {
                Directory.CreateDirectory(orgDir);
            }

            // 校区名称
            string branchName = "";
            if (w.SysOrgBranchId != null)
            {
                branchName = $"@{w.SysOrgBranchName}";
            }

            #region 导出抽中学生
            string sampleFileName = $"{w.SysOrgCode}-{w.SysOrgFullName}{branchName}-{eg.Grade.Name}-{ExamSampleType.DISTRICT.GetDescription()}-学生检录表";
            // 抽中学生列表
            var sampleStus = stus.Where(t => t.SysOrgId == w.SysOrgId && t.SysOrgBranchId == w.SysOrgBranchId && t.GradeId == w.GradeId && t.ExamSampleType == ExamSampleType.DISTRICT)
                                 .OrderBy(t => t.ExamNumber?[^4..]).ToList();
            // 自编号按导入顺序            
            if (eg.IsRequiredSelfExamNumber)
            {
                sampleStus = sampleStus.OrderBy(t => t.ExamStudentId).ToList();
            }

            // 定义EXCEL列
            List<ExportExcelColDto<ExamSampleStudentExportDto>> cols = new()
            {
                new() { Name = "年级号", Width = 8, GetCellValue = (r) => r.GradeNumber },
                new() { Name = "班级号", Width = 8, GetCellValue = (r) => r.ClassNumber },
                new() { Name = "监测号", Width = 14, GetCellValue = (r) => r.ExamNumber },
                new() { Name = "姓名", Width = 20, GetCellValue = (r) => r.ExamStudentName },
                new() { Name = "证件类型", Width = 16, GetCellValue = (r) => certificateTypes[r.CertificateType] },
                new() { Name = "证件号码", Width = 20, GetCellValue = (r) => r.IdNumber },
                new() { Name = "名单类型", Width = 10, GetCellValue = (r) => r.ExamSampleType.GetDescription() },
            };
            if (w.SysOrgBranchId != null)
            {
                cols.Insert(0, new() { Name = "校区", Width = 10, GetCellValue = (r) => r.SysOrgBranchName });
            }

            // 导出EXCEL文件
            var ret = _exportExcelService.ExportExcel(new ExportExcelDto<ExamSampleStudentExportDto>()
            {
                Title = sampleFileName,
                Columns = cols,
                Items = sampleStus,
                IncludeExportTime = false,
            });
            // 写入文件
            await File.WriteAllBytesAsync(Path.Combine(orgDir, $"{sampleFileName}.xlsx"), ret);
            #endregion

            #region 导出未抽中学生
            string noSampleFileName = $"{w.SysOrgCode}-{w.SysOrgFullName}{branchName}-{eg.Grade.Name}-{ExamSampleType.SCHOOL_EXAM.GetDescription()}-学生检录表";
            // 抽中学生列表
            var noSampleStus = stus.Where(t => t.SysOrgId == w.SysOrgId && t.SysOrgBranchId == w.SysOrgBranchId && t.GradeId == w.GradeId && t.ExamSampleType == ExamSampleType.SCHOOL_EXAM)
                                 .OrderBy(t => t.ExamNumber?[^4..]).ToList();
            // 自编号按导入顺序
            if (eg.IsRequiredSelfExamNumber)
            {
                noSampleStus = noSampleStus.OrderBy(t => t.ExamStudentId).ToList();
            }

            // 定义EXCEL列
            List<ExportExcelColDto<ExamSampleStudentExportDto>> noCols = new()
            {
                new() { Name = "年级号", Width = 8, GetCellValue = (r) => r.GradeNumber },
                new() { Name = "班级号", Width = 8, GetCellValue = (r) => r.ClassNumber },
                new() { Name = "考号", Width = 14, GetCellValue = (r) => r.ExamNumber },
                new() { Name = "姓名", Width = 20, GetCellValue = (r) => r.ExamStudentName },
                new() { Name = "证件类型", Width = 16, GetCellValue = (r) => certificateTypes[r.CertificateType] },
                new() { Name = "证件号码", Width = 20, GetCellValue = (r) => r.IdNumber },
                new() { Name = "名单类型", Width = 10, GetCellValue = (r) => r.ExamSampleType.GetDescription() },
            };
            if (w.SysOrgBranchId != null)
            {
                noCols.Insert(0, new() { Name = "校区", Width = 10, GetCellValue = (r) => r.SysOrgBranchName });
            }

            // 导出EXCEL文件
            var noRet = _exportExcelService.ExportExcel(new ExportExcelDto<ExamSampleStudentExportDto>()
            {
                Title = noSampleFileName,
                Columns = noCols,
                Items = noSampleStus,
                IncludeExportTime = false,
            });
            // 写入文件
            await File.WriteAllBytesAsync(Path.Combine(orgDir, $"{noSampleFileName}.xlsx"), noRet);
            #endregion
        }

        // 学生目录
        var schoolFilePath = Path.Combine(fileRoot, fileName);
        // 统计表文件
        var countFile = await ExportSampleCountToOrg(id);
        await File.WriteAllBytesAsync(Path.Combine(schoolFilePath, $"{fileName}-监测抽样统计表.xlsx"), countFile.fileBytes);

        string outFileName = $"{plan.FullName}-{fileName}-监测抽样学生检录表.zip";
        string outFilePath = Path.Combine(fileRoot, outFileName);
        ICSharpCode.SharpZipLib.Zip.FastZip zip = new();
        zip.CreateZip(outFilePath, schoolFilePath, true, string.Empty);

        var retBytes = await File.ReadAllBytesAsync(outFilePath);
        Directory.Delete(fileRoot, true);
        return (outFileName, retBytes);
    }
    /// <summary>
    /// 导出抽样统计表
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public async Task<(string fileName, byte[] fileBytes)> ExportSampleCount(int id)
    {
        var examSample = await _rep.DetachedEntities.FirstOrDefaultAsync(t => t.Id == id) ?? throw Oops.Oh(ErrorCode.E2001);

        var items = await GetSampleCountListById(id);

        IWorkbook wb = new XSSFWorkbook();
        ISheet sheet = wb.CreateSheet();
        sheet.DisplayGridlines = false;

        // 获取样式
        var cellStyle = _exportExcelService.GetCellStyle(wb);

        #region 表头
        int rowNum = 0;

        IRow headerRow = sheet.CreateRow(rowNum++);
        headerRow.Height = ExportExcelCellStyle.DefaultRowHeight;
        int ci = 0;
        _exportExcelService.AddCell("数据类型", headerRow, ci++, cellStyle.ColumnHeaderStyle, sheet, 12);
        _exportExcelService.AddCell("学校代码", headerRow, ci++, cellStyle.ColumnHeaderStyle, sheet, 10);
        _exportExcelService.AddCell("学校名称", headerRow, ci++, cellStyle.ColumnHeaderStyle, sheet, 20);
        _exportExcelService.AddCell("年级", headerRow, ci++, cellStyle.ColumnHeaderStyle, sheet, 10);
        _exportExcelService.AddCell("班级", headerRow, ci++, cellStyle.ColumnHeaderStyle, sheet, 10);
        _exportExcelService.AddCell(ExamSampleType.DISTRICT.GetDescription(), headerRow, ci++, cellStyle.ColumnHeaderStyle, sheet, 12);
        _exportExcelService.AddCell(ExamSampleType.SCHOOL_EXAM.GetDescription(), headerRow, ci++, cellStyle.ColumnHeaderStyle, sheet, 12);
        _exportExcelService.AddCell("合计", headerRow, ci++, cellStyle.ColumnHeaderStyle, sheet, 12);
        sheet.CreateFreezePane(0, 1);
        #endregion

        foreach (var item in items)
        {
            IRow row = sheet.CreateRow(rowNum);
            row.Height = ExportExcelCellStyle.DefaultRowHeight;

            int si = 0;
            string schoolCode = item.SysOrgCode ?? "";
            string schoolName = item.SysOrgName ?? "";
            string gradeName = item.GradeName ?? "";
            long classNumber = item.ClassNumber;
            ICellStyle cstyle = cellStyle.CenterCellStyle;
            switch (item.TypeId)
            {
                case 2:
                    si = 3;
                    break;
                case 3:
                    si = 2;
                    break;
                case 4:
                    si = 3;
                    schoolCode = "";
                    break;
                case 5:
                    si = 2;
                    schoolCode = "";
                    break;
            }
            if (si > 0)
            {
                cstyle = cellStyle.FillCellStyle;
                sheet.AddMergedRegion(new CellRangeAddress(rowNum, rowNum, si, 4));
            }

            int rci = 0;
            _exportExcelService.AddCell(item.TypeName, row, rci++, cstyle);
            _exportExcelService.AddCell(schoolCode, row, rci++, cstyle);
            _exportExcelService.AddCell(schoolName, row, rci++, cstyle);
            _exportExcelService.AddCell(gradeName, row, rci++, cstyle);
            _exportExcelService.AddCell(classNumber, row, rci++, cstyle);
            _exportExcelService.AddCell(item.CenterStudentCount, row, rci++, cstyle);
            _exportExcelService.AddCell(item.SchoolStudentCount, row, rci++, cstyle);
            _exportExcelService.AddCell(item.TotalStudentCount, row, rci++, cstyle);

            rowNum++;
        }

        MemoryStream ms = new();
        wb.Write(ms, false);
        ms.Flush();

        return ($"{examSample.FullName}-监测抽样统计表.xlsx", ms.ToArray());
    }
    /// <summary>
    /// 导出学校抽样统计表
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public async Task<(string fileName, byte[] fileBytes)> ExportSampleCountToOrg(int id)
    {
        var examSample = await _rep.DetachedEntities.Include(t => t.ExamPlan).FirstOrDefaultAsync(t => t.Id == id) ?? throw Oops.Oh(ErrorCode.E2001);

        var items = await GetSampleCountListById(id);
        items = items.Where(t => t.TypeId < 4 && t.SysOrgId == CurrentSysUserInfo.SysOrgId).ToList();

        IWorkbook wb = new XSSFWorkbook();
        ISheet sheet = wb.CreateSheet();
        sheet.DisplayGridlines = false;

        // 获取样式
        var cellStyle = _exportExcelService.GetCellStyle(wb);

        #region 表头
        int rowNum = 0;

        IRow headerRow = sheet.CreateRow(rowNum++);
        headerRow.Height = ExportExcelCellStyle.DefaultRowHeight;
        int ci = 0;
        _exportExcelService.AddCell("数据类型", headerRow, ci++, cellStyle.ColumnHeaderStyle, sheet, 12);
        _exportExcelService.AddCell("学校代码", headerRow, ci++, cellStyle.ColumnHeaderStyle, sheet, 10);
        _exportExcelService.AddCell("学校名称", headerRow, ci++, cellStyle.ColumnHeaderStyle, sheet, 20);
        _exportExcelService.AddCell("年级", headerRow, ci++, cellStyle.ColumnHeaderStyle, sheet, 10);
        _exportExcelService.AddCell("班级", headerRow, ci++, cellStyle.ColumnHeaderStyle, sheet, 10);
        _exportExcelService.AddCell(ExamSampleType.DISTRICT.GetDescription(), headerRow, ci++, cellStyle.ColumnHeaderStyle, sheet, 12);
        _exportExcelService.AddCell(ExamSampleType.SCHOOL_EXAM.GetDescription(), headerRow, ci++, cellStyle.ColumnHeaderStyle, sheet, 12);
        _exportExcelService.AddCell("合计", headerRow, ci++, cellStyle.ColumnHeaderStyle, sheet, 12);
        sheet.CreateFreezePane(0, 1);
        #endregion

        foreach (var item in items)
        {
            IRow row = sheet.CreateRow(rowNum);
            row.Height = ExportExcelCellStyle.DefaultRowHeight;

            int si = 0;
            string schoolCode = item.SysOrgCode ?? "";
            string schoolName = item.SysOrgName ?? "";
            string gradeName = item.GradeName ?? "";
            long classNumber = item.ClassNumber;
            ICellStyle cstyle = cellStyle.CenterCellStyle;
            switch (item.TypeId)
            {
                case 2:
                    si = 3;
                    break;
                case 3:
                    si = 2;
                    break;
                case 4:
                    si = 3;
                    schoolCode = "";
                    break;
                case 5:
                    si = 2;
                    schoolCode = "";
                    break;
            }
            if (si > 0)
            {
                cstyle = cellStyle.FillCellStyle;
                sheet.AddMergedRegion(new CellRangeAddress(rowNum, rowNum, si, 4));
            }

            int rci = 0;
            _exportExcelService.AddCell(item.TypeName, row, rci++, cstyle);
            _exportExcelService.AddCell(schoolCode, row, rci++, cstyle);
            _exportExcelService.AddCell(schoolName, row, rci++, cstyle);
            _exportExcelService.AddCell(gradeName, row, rci++, cstyle);
            _exportExcelService.AddCell(classNumber, row, rci++, cstyle);
            _exportExcelService.AddCell(item.CenterStudentCount, row, rci++, cstyle);
            _exportExcelService.AddCell(item.SchoolStudentCount, row, rci++, cstyle);
            _exportExcelService.AddCell(item.TotalStudentCount, row, rci++, cstyle);

            rowNum++;
        }

        MemoryStream ms = new();
        wb.Write(ms, false);
        ms.Flush();

        return ($"{examSample.ExamPlan.FullName}-{CurrentSysUserInfo.SysOrgName}-监测抽样统计表.xlsx", ms.ToArray());
    }
    #endregion

    #region 方案查询
    /// <summary>
    /// 根据ID获取抽样方案
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public async Task<ExamSampleOutput> GetById(int id)
    {
        var item = await _rep.DetachedEntities.ProjectToType<ExamSampleOutput>().FirstOrDefaultAsync(t => t.Id == id);
        return item.Adapt<ExamSampleOutput>();
    }
    /// <summary>
    /// 根据监测计划ID获取全部抽样方案
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task<List<ExamSampleOutput>> GetListByExamPlanId(int examPlanId)
    {
        var items = await _rep.DetachedEntities.Where(t => t.ExamPlanId == examPlanId).ProjectToType<ExamSampleOutput>().ToListAsync();
        return items;
    }
    /// <summary>
    /// 获取抽样统计表
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public async Task<List<ExamSampleCountOutput>> GetSampleCountListById(int id)
    {
        var examSample = await _rep.DetachedEntities.FirstOrDefaultAsync(t => t.Id == id) ?? throw Oops.Oh(ErrorCode.E2001);

        string whereSql = $"WHERE T1.exam_plan_id = {examSample.ExamPlanId} AND T2.exam_sample_id = {id}";
        string querySql = @$"
SELECT 
	T1.type_id,
	T3.`name` AS type_name, 
	T1.sys_org_id,
	T2.`code` AS sys_org_code, 
	T2.`name` AS sys_org_name, 
	T2.full_name AS sys_org_full_name, 
	T1.grade_id,
	T4.`name` AS grade_name, 
	T1.school_class_id,
	T1.class_number, 
	T1.total_student_count, 
	T1.center_student_count, 
	T1.school_student_count
FROM
(
	-- 班级汇总
	SELECT 1 AS type_id, T.sys_org_id, T.grade_id, T.school_class_id, T.class_number,
		SUM(CASE WHEN T.exam_sample_type = @district THEN T.student_count ELSE 0 END) AS center_student_count,
		SUM(CASE WHEN T.exam_sample_type = @schoolExam THEN T.student_count ELSE 0 END) AS school_student_count,
		SUM(T.student_count) AS total_student_count
	FROM
	(
		SELECT T1.sys_org_id, T1.grade_id, T1.school_class_id, T1.class_number, T2.exam_sample_type, COUNT(1) AS student_count
		FROM exam_student AS T1
		JOIN exam_sample_student AS T2 ON T1.id = T2.exam_student_id
		{whereSql}
		GROUP BY T1.sys_org_id, T1.grade_id, T1.school_class_id, T1.class_number, T2.exam_sample_type
	) AS T
	GROUP BY T.sys_org_id, T.grade_id, T.school_class_id, T.class_number
	UNION ALL
	-- 年级汇总
	SELECT 2 AS type_id, T.sys_org_id, T.grade_id, NULL AS school_class_id, 9999 AS class_number,
		SUM(CASE WHEN T.exam_sample_type = @district THEN T.student_count ELSE 0 END) AS center_student_count,
		SUM(CASE WHEN T.exam_sample_type = @schoolExam THEN T.student_count ELSE 0 END) AS school_student_count,
		SUM(T.student_count) AS total_student_count
	FROM
	(
		SELECT T1.sys_org_id, T1.grade_id, T2.exam_sample_type, COUNT(1) AS student_count
		FROM exam_student AS T1
		JOIN exam_sample_student AS T2 ON T1.id = T2.exam_student_id
		{whereSql}
		GROUP BY T1.sys_org_id, T1.grade_id, T2.exam_sample_type
	) AS T
	GROUP BY T.sys_org_id, T.grade_id
	UNION ALL
	-- 学校汇总
	SELECT 3 AS type_id, T.sys_org_id, 9999 AS grade_id, NULL AS school_class_id, 9999 AS class_number,
		SUM(CASE WHEN T.exam_sample_type = @district THEN T.student_count ELSE 0 END) AS center_student_count,
		SUM(CASE WHEN T.exam_sample_type = @schoolExam THEN T.student_count ELSE 0 END) AS school_student_count,
		SUM(T.student_count) AS total_student_count
	FROM
	(
		SELECT T1.sys_org_id, T2.exam_sample_type, COUNT(1) AS student_count
		FROM exam_student AS T1
		JOIN exam_sample_student AS T2 ON T1.id = T2.exam_student_id
		{whereSql}
		GROUP BY T1.sys_org_id, T2.exam_sample_type
	) AS T
	GROUP BY T.sys_org_id
	UNION ALL
	-- 全区年级汇总
	SELECT 4 AS type_id, 9999 AS sys_org_id, T.grade_id, NULL AS school_class_id, 9999 AS class_number,
		SUM(CASE WHEN T.exam_sample_type = @district THEN T.student_count ELSE 0 END) AS center_student_count,
		SUM(CASE WHEN T.exam_sample_type = @schoolExam THEN T.student_count ELSE 0 END) AS school_student_count,
		SUM(T.student_count) AS total_student_count
	FROM
	(
		SELECT T1.grade_id, T2.exam_sample_type, COUNT(1) AS student_count
		FROM exam_student AS T1
		JOIN exam_sample_student AS T2 ON T1.id = T2.exam_student_id
		{whereSql}
		GROUP BY T1.grade_id, T2.exam_sample_type
	) AS T
	GROUP BY T.grade_id
	UNION ALL
	-- 全区汇总
	SELECT 5 AS type_id, 9999 AS sys_org_id, 9999 AS grade_id, NULL AS school_class_id, 9999 AS class_number,
		SUM(CASE WHEN T.exam_sample_type = @district THEN T.student_count ELSE 0 END) AS center_student_count,
		SUM(CASE WHEN T.exam_sample_type = @schoolExam THEN T.student_count ELSE 0 END) AS school_student_count,
		SUM(T.student_count) AS total_student_count
	FROM
	(
		SELECT T2.exam_sample_type, COUNT(1) AS student_count
		FROM exam_student AS T1
		JOIN exam_sample_student AS T2 ON T1.id = T2.exam_student_id
		{whereSql}
		GROUP BY T2.exam_sample_type
	) AS T
) AS T1
LEFT JOIN
(
	SELECT id, `code`, `name`, full_name FROM sys_org WHERE org_type = 3
	UNION ALL
	SELECT 9999 AS id, '999' AS `code`, '全区' AS `name`, '全区' AS full_name 
) AS T2 ON T1.sys_org_id = T2.id
JOIN 
(
	SELECT 1 AS id, '学校班级' AS `name` UNION ALL
	SELECT 2 AS id, '学校年级' AS `name` UNION ALL
	SELECT 3 AS id, '学校' AS `name` UNION ALL
	SELECT 4 AS id, '全区年级' AS `name` UNION ALL
	SELECT 5 AS id, '全区' AS `name`
) AS T3 ON T1.type_id = T3.id
LEFT JOIN base_grade AS T4 ON T1.grade_id = T4.id
ORDER BY T2.`code`, T1.grade_id, T1.class_number, T1.type_id
";
        var items = await _rep.SqlQueriesAsync<ExamSampleCountOutput>(querySql, new
        {
            District = (short)ExamSampleType.DISTRICT,
            SchoolExam = (short)ExamSampleType.SCHOOL_EXAM
        });
        return items;
    }
    /// <summary>
    /// 获取学校抽样统计表
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public async Task<List<ExamSampleCountOutput>> GetOrgSampleCountListById(int id)
    {
        var items = await GetSampleCountListById(id);
        items = items.Where(t => t.TypeId < 4 && t.SysOrgId == CurrentSysUserInfo.SysOrgId).ToList();
        return items;
    }
    /// <summary>
    /// 查询已发布抽样
    /// </summary>
    /// <param name="examDataPublishId">监测发布内容ID</param>
    /// <param name="type">抽样数据发布类型</param>
    /// <returns></returns>
    public async Task<ExamSamplePlanOutput> GetByExamDataPublishId(int examDataPublishId, DataPublishType type)
    {
        if(type != DataPublishType.STUDENT_SAMPLE_LIST && type != DataPublishType.STUDENT_SAMPLE_COUNT_LIST)
        {
            throw Oops.Oh(ErrorCode.E1014, "数据发布");
        }
        var pub = await _rep.Change<ExamDataPublish>().DetachedEntities.FirstOrDefaultAsync(t => t.Id == examDataPublishId && t.Type == type) ?? throw Oops.Oh(ErrorCode.E2001, "反馈内容");
        var item = await _rep.DetachedEntities.Include(t => t.ExamPlan)
                                              .FirstOrDefaultAsync(t => t.ExamPlanId == pub.ExamPlanId && t.ExamPlan.IsFixedExamSample == true && t.Status == ExamSampleStatus.SUCCESSFUL && t.IsSelected == true)
                                              ?? throw Oops.Oh(ErrorCode.E2001, "抽样方案");

        return item.Adapt<ExamSamplePlanOutput>();
    }
    #endregion

    #region 机构查询

    #endregion

    #region 私有方法
    /// <summary>
    /// 获取最终抽样学生列表
    /// </summary>
    /// <param name="examSample"></param>
    /// <param name="examGradeDict"></param>
    /// <param name="stus"></param>
    /// <returns></returns>
    private static List<AddExamSampleStudentInput> GetFinalSampleList(ExamSampleOutput examSample, Dictionary<short, ExamGradeOutput> examGradeDict, List<ExamSampleDto> stus)
    {
        // 抽样配置
        var config = examSample.Config;

        // 返回结果集
        List<AddExamSampleStudentInput> retItems = new();

        // 遍历集合
        var ws = stus.Select(t => new { t.SysOrgId, t.SysOrgBranchId, t.GradeId, t.SchoolClassId }).Distinct().ToList();

        // 学校
        var orgs = ws.Select(t => t.SysOrgId).Distinct().ToList();
        foreach (var orgId in orgs)
        {
            // 跳过不参与监测的学校
            if (config.ExcludeSysOrgIds.Contains(orgId))
            {
                continue;
            }

            // 校区
            var branches = ws.Where(t => t.SysOrgId == orgId).Select(t => t.SysOrgBranchId).Distinct().ToList();
            foreach (var branchId in branches)
            {
                // 年级
                var grades = ws.Where(t => t.SysOrgId == orgId && t.SysOrgBranchId == branchId).Select(t => t.GradeId).Distinct().ToList();
                foreach (var gradeId in grades)
                {
                    var eg = examGradeDict[gradeId];

                    // 自编号直接写入
                    if (eg.IsRequiredSelfExamNumber)
                    {
                        var selfList = stus.Where(t => t.SysOrgId == orgId && t.SysOrgBranchId == branchId && t.GradeId == gradeId && t.IsSelfExamNumber == true).OrderBy(t => t.ExamNumber?[^4..]).ToList();
                        retItems.AddRange(selfList.Select((t, i) => new AddExamSampleStudentInput
                        {
                            ExamSampleId = examSample.Id,
                            ExamStudentId = t.ExamStudentId,
                            ExamNumber = t.ExamNumber,
                            ExamSampleType = t.ExamSampleType ?? ExamSampleType.DISTRICT,
                            IsSpecialStudent = t.IsSpecialStudent,
                            PreTotalScore = t.TotalScore ?? 0,
                            Sequence = i + 1,
                        }));
                        continue;
                    }

                    // 区测学生列表
                    var sampleStus = stus.Where(t => t.SysOrgId == orgId && t.SysOrgBranchId == branchId && t.GradeId == gradeId && t.ExamSampleType == ExamSampleType.DISTRICT)
                                         .OrderBy(t => t.ClassNumber).ThenBy(t => t.Sequence).ToList();
                    // 区测学生数量
                    var sampleCount = sampleStus.Count;

                    // 是否在年级内随机打乱顺序
                    if (config.IsGradeSeatNumberRandom)
                    {
                        Utils.ListRandom(sampleStus);
                    }
                    // 生成区测学生监测号并插入
                    retItems.AddRange(sampleStus.Select((t, i) => new AddExamSampleStudentInput
                    {
                        ExamSampleId = examSample.Id,
                        ExamStudentId = t.ExamStudentId,
                        ExamNumber = BuildExamNumber(t.SysOrgCode, eg.Grade.GradeNumber, t.ClassNumber, i + 1),
                        ExamSampleType = ExamSampleType.DISTRICT,
                        IsSpecialStudent = t.IsSpecialStudent,
                        PreTotalScore = t.TotalScore ?? 0,
                        Sequence = i + 1,
                    }));

                    // 校测学生列表
                    var noSampleStus = stus.Where(t => t.SysOrgId == orgId && t.SysOrgBranchId == branchId && t.GradeId == gradeId && t.ExamSampleType == ExamSampleType.SCHOOL_EXAM)
                                           .OrderBy(t => t.ClassNumber).ThenBy(t => t.Sequence).ToList();

                    // 是否在年级内随机打乱顺序
                    if (config.IsGradeSeatNumberRandom)
                    {
                        Utils.ListRandom(noSampleStus);
                    }
                    // 生成校测学生监测号并插入
                    retItems.AddRange(noSampleStus.Select((t, i) => new AddExamSampleStudentInput
                    {
                        ExamSampleId = examSample.Id,
                        ExamStudentId = t.ExamStudentId,
                        ExamNumber = BuildExamNumber(t.SysOrgCode, eg.Grade.GradeNumber, t.ClassNumber, sampleCount + 100 + i + 1),
                        ExamSampleType = ExamSampleType.SCHOOL_EXAM,
                        IsSpecialStudent = t.IsSpecialStudent,
                        PreTotalScore = t.TotalScore ?? 0,
                        Sequence = i + 1,
                    }));
                }
            }
        }

        return retItems;
    }
    /// <summary>
    /// 生成监测号
    /// </summary>
    /// <param name="sysOrgCode">学校代码</param>
    /// <param name="gradeNumber">年级序号</param>
    /// <param name="classNumber">班级序号</param>
    /// <param name="sequence">顺序号</param>
    /// <returns></returns>
    private static string BuildExamNumber(string sysOrgCode, short gradeNumber, short classNumber, int sequence)
    {
        return $"{sysOrgCode.PadLeft(3, '0')}{gradeNumber.ToString().PadLeft(2, '0')}{classNumber.ToString().PadLeft(2, '0')}{sequence.ToString().PadLeft(4, '0')}";
    }
    /// <summary>
    /// 获取抽样名单
    /// </summary>
    /// <param name="config">抽样配置</param>
    /// <param name="examGradeDict">监测年级字典，键为年级ID</param>
    /// <param name="stus">学生列表</param>
    /// <returns></returns>
    private static List<ExamSampleDto> GetSampleList(ExamSampleConfig config, Dictionary<short, ExamGradeOutput> examGradeDict, List<ExamSampleDto> stus)
    {
        // 抽样比例
        var sampleRate = config.Percent / 100.0;

        // 返回结果集
        List<ExamSampleDto> retItems = new();

        // 遍历集合
        var ws = stus.Select(t => new { t.SysOrgId, t.SysOrgBranchId, t.GradeId, t.SchoolClassId }).Distinct().ToList();

        // 学校
        var orgs = ws.Select(t => t.SysOrgId).Distinct().ToList();
        foreach (var orgId in orgs)
        {
            // 跳过不参与监测的学校
            if (config.ExcludeSysOrgIds.Contains(orgId))
            {
                continue;
            }

            // 校区
            var branches = ws.Where(t => t.SysOrgId == orgId).Select(t => t.SysOrgBranchId).Distinct().ToList();
            foreach (var branchId in branches)
            {
                // 年级
                var grades = ws.Where(t => t.SysOrgId == orgId && t.SysOrgBranchId == branchId).Select(t => t.GradeId).Distinct().ToList();

                foreach (var gradeId in grades)
                {
                    // 班级
                    var classes = ws.Where(t => t.SysOrgId == orgId && t.SysOrgBranchId == branchId && t.GradeId == gradeId).Select(t => t.SchoolClassId).Distinct().Order().ToList();
                    var classCount = classes.Count;

                    // 1.年级自编号直接写入
                    var eg = examGradeDict[gradeId];
                    if (eg != null && eg.IsRequiredSelfExamNumber)
                    {
                        var items = stus.Where(t => t.SysOrgId == orgId && t.SysOrgBranchId == branchId && t.GradeId == gradeId).ToList();
                        foreach (var item in items)
                        {
                            item.ExamSampleType = ExamSampleType.DISTRICT;
                            item.IsSelfExamNumber = true;
                            item.ExamNumber = item.ExamNumber.Trim().Replace(" ", "");
                            //if (item.ExamNumber.Length != eg.SelfExamNumberLength)
                            //{
                            //    throw Oops.Oh(ErrorCode.E1013, $"{item.ExamStudentId},{item.ExamStudentName},{item.ExamNumber}");
                            //}
                        }
                        retItems.AddRange(items.Where(t => t.ExamNumber.Length >= 4));
                        continue;
                    }

                    // 2.年级只有一个班，处理最低人数限制，满足条件该年级全抽
                    if (config.IsEnabledOnlyOneClassStudentMin && classCount == 1)
                    {
                        var stuCount = stus.Count(t => t.SysOrgId == orgId && t.SysOrgBranchId == branchId && t.GradeId == gradeId);
                        if (stuCount <= config.OnlyOneClassStudentMin)
                        {
                            var items = stus.Where(t => t.SysOrgId == orgId && t.SysOrgBranchId == branchId && t.GradeId == gradeId).ToList();
                            for (int i = 0; i < items.Count; i++)
                            {
                                items[i].ExamSampleType = ExamSampleType.DISTRICT;
                                items[i].Sequence = i + 1;
                            }
                            retItems.AddRange(items);
                            continue;
                        }
                    }

                    // 3.年级多于一个班，处理年级未抽样最低限制，满足条件该年级全抽
                    if (config.IsEnabledGradeNoSampleStudentMin && classCount > 1)
                    {
                        var classStuCount = stus.Where(t => t.SysOrgId == orgId && t.SysOrgBranchId == branchId && t.GradeId == gradeId).GroupBy(t => t.SchoolClassId).Select(t => new { t.Key, Count = t.Count() }).ToList();
                        if (classStuCount.Sum(t => Math.Ceiling(t.Count * (1 - sampleRate))) <= config.GradeNoSampleStudentMin)
                        {
                            var items = stus.Where(t => t.SysOrgId == orgId && t.SysOrgBranchId == branchId && t.GradeId == gradeId).ToList();
                            for (int i = 0; i < items.Count; i++)
                            {
                                items[i].ExamSampleType = ExamSampleType.DISTRICT;
                                items[i].Sequence = i + 1;
                            }
                            retItems.AddRange(items);
                            continue;
                        }
                    }

                    // 遍历处理各班级
                    foreach (var classId in classes)
                    {
                        // 班级学生数量
                        var classStuCount = stus.Count(t => t.SysOrgId == orgId && t.SysOrgBranchId == branchId && t.GradeId == gradeId && t.SchoolClassId == classId);
                        if (classStuCount == 0)
                        {
                            continue;
                        }

                        // 4.班级设置为全抽的直接写入
                        // 5.班级学生人数低于限制
                        if (config.SampleAllSchoolClassIds.Contains(classId) || (config.IsEnabledClassStudentMin && classStuCount <= config.ClassStudentMin))
                        {
                            var items = stus.Where(t => t.SysOrgId == orgId && t.SysOrgBranchId == branchId && t.GradeId == gradeId && t.SchoolClassId == classId).ToList();
                            for (int i = 0; i < items.Count; i++)
                            {
                                items[i].ExamSampleType = ExamSampleType.DISTRICT;
                                items[i].Sequence = i + 1;
                            }
                            retItems.AddRange(items);
                            continue;
                        }

                        // -------------------------
                        // 以下是需要抽样的
                        // -------------------------

                        // 班级学生列表
                        var classStus = stus.Where(t => t.SysOrgId == orgId && t.SysOrgBranchId == branchId && t.GradeId == gradeId && t.SchoolClassId == classId).ToList();
                        // 抽中学生列表
                        List<ExamSampleDto> sampleStus = new();

                        // 该班是否有成绩
                        var hasScore = classStus.Any(t => t.TotalScore != null && t.TotalScore > 0);
                        // 最大抽样数量
                        var maxSampleStuCount = (int)Math.Ceiling(classStuCount * sampleRate);

                        // 排除特殊学生
                        if (config.IsExcludeSpecialStudent)
                        {
                            // 非特殊学生列表
                            var normalStus = stus.Where(t => t.SysOrgId == orgId && t.SysOrgBranchId == branchId && t.GradeId == gradeId && t.SchoolClassId == classId && t.IsSpecialStudent == false).ToList();
                            // 特殊学生数量
                            var classSpecialStuCount = stus.Count(t => t.SysOrgId == orgId && t.SysOrgBranchId == branchId && t.GradeId == gradeId && t.SchoolClassId == classId && t.IsSpecialStudent == true);
                            // 特殊学生数量占比大于等于 1-抽样比例，非特殊学生全抽 
                            if (classSpecialStuCount / classStuCount >= (1 - sampleRate))
                            {
                                // 非特殊学生
                                for (int i = 0; i < normalStus.Count; i++)
                                {
                                    normalStus[i].ExamSampleType = ExamSampleType.DISTRICT;
                                    normalStus[i].Sequence = i + 1;
                                }
                                retItems.AddRange(normalStus);
                            }
                            // 仅非特殊学生参与抽样
                            else
                            {
                                // 有成绩的循环抽样
                                if (hasScore)
                                {
                                    int sq = 1;
                                    CyclicSampling(config, normalStus, sampleStus, maxSampleStuCount, sq);
                                    retItems.AddRange(sampleStus);
                                }
                                // 无成绩随机抽样
                                else
                                {
                                    sampleStus = RandomSampling(normalStus, classStuCount, sampleRate);
                                    retItems.AddRange(sampleStus);
                                }
                            }
                        }
                        // 不排除特殊学生
                        else
                        {
                            // 有成绩的循环抽样
                            if (hasScore)
                            {
                                int sq = 1;
                                CyclicSampling(config, classStus, sampleStus, maxSampleStuCount, sq);
                                retItems.AddRange(sampleStus);
                            }
                            // 无成绩随机抽样
                            else
                            {
                                sampleStus = RandomSampling(classStus, classStuCount, sampleRate);
                                retItems.AddRange(sampleStus);
                            }
                        }

                        // 未抽中学生列表
                        var noSampleStus = (from s in classStus where !(from n in sampleStus select n.ExamStudentId).Contains(s.ExamStudentId) select s).ToList();
                        for (int i = 0; i < noSampleStus.Count; i++)
                        {
                            noSampleStus[i].ExamSampleType = ExamSampleType.SCHOOL_EXAM;
                            noSampleStus[i].Sequence = i + 1;
                        }
                        retItems.AddRange(noSampleStus);
                    }
                }
            }
        }

        return retItems;
    }
    /// <summary>
    /// 循环抽样
    /// </summary>
    /// <param name="config"></param>
    /// <param name="stus"></param>
    /// <param name="sampleStus"></param>
    /// <param name="maxStuCount"></param>
    /// <param name="sequence"></param>
    private static void CyclicSampling(ExamSampleConfig config, List<ExamSampleDto> stus, List<ExamSampleDto> sampleStus, int maxStuCount, int sequence)
    {
        for (int i = config.StartPosition >= stus.Count ? 0 : config.StartPosition - 1; i < stus.Count; i += config.Interval)
        {
            var stu = stus[i];
            var nstus = stu.Adapt<ExamSampleDto>();
            nstus.Sequence = sequence++;
            nstus.ExamSampleType = ExamSampleType.DISTRICT;
            sampleStus.Add(nstus);

            // 如果已抽满结束抽样
            if (sampleStus.Count >= maxStuCount)
            {
                break;
            }
        }
        if (sampleStus.Count < maxStuCount)
        {
            var noSampleStus = (from s in stus where !(from n in sampleStus select n.ExamStudentId).Contains(s.ExamStudentId) select s).ToList();
            CyclicSampling(config, noSampleStus, sampleStus, maxStuCount, sequence);
        }
    }
    /// <summary>
    /// 随机抽样
    /// </summary>
    /// <param name="stus"></param>
    /// <param name="classStuCount"></param>
    /// <param name="sampleRate"></param>
    /// <returns></returns>
    private static List<ExamSampleDto> RandomSampling(List<ExamSampleDto> stus, int classStuCount, double sampleRate)
    {
        List<ExamSampleDto> sampleStus = new();
        var sampleIndexList = GetRandomIndex(classStuCount, sampleRate);
        int sq = 1;
        for (int i = 0; i < stus.Count; i++)
        {
            if (sampleIndexList.Contains(i))
            {
                var item = stus[i].Adapt<ExamSampleDto>();
                item.ExamSampleType = ExamSampleType.DISTRICT;
                item.Sequence = sq++;
                sampleStus.Add(item);
            }
        }
        return sampleStus;
    }
    /// <summary>
    /// 获取随机位置列表
    /// </summary>
    /// <param name="totalCount">总数量</param>
    /// <param name="sampleRate">抽样比例</param>
    /// <returns></returns>
    private static List<int> GetRandomIndex(int totalCount, double sampleRate)
    {
        Random random = new();
        List<int> samples = new();

        int i = 0;
        var sc = Math.Ceiling(totalCount * sampleRate);
        while (i < sc)
        {
            int s = random.Next(totalCount);
            if (samples.Any(t => t == s))
            {
                continue;
            }
            samples.Add(s);
            i++;
        }

        return samples.OrderBy(t => t).ToList();
    }
    /// <summary>
    /// 获取学生并带往期成绩
    /// </summary>
    /// <param name="config">抽样配置</param>
    /// <param name="examPlanId">抽样监测计划ID</param>
    /// <param name="grades">年级ID列表</param>
    /// <returns></returns>
    private async Task<List<ExamSampleDto>> GetStudentScoreList(ExamSampleConfig config, int examPlanId, List<short> grades)
    {
        string gradeWhere = string.Join(" OR ", grades.Select(t => $"T1.grade_id = {t}"));

        string selectSql = $@"
SELECT 
    T1.id AS exam_student_id,
    T1.`name` AS exam_student_name,
    T1.certificate_type,
    UPPER(T1.id_number) AS id_number,
    T1.sys_org_id,
    ORG.`code` AS sys_org_code,
    T1.sys_org_branch_id, 
    T1.grade_id, 
    T1.school_class_id, 
    T1.class_number, 
    T1.exam_number,
    T2.course_count,
    T2.total_score, 
    CASE WHEN ISNULL(T3.id) THEN 0 ELSE 1 END as is_special_student
FROM exam_student AS T1
LEFT JOIN
(
    -- 往期总分
    SELECT T1.sys_org_id, T1.school_class_id, T2.certificate_type, UPPER(T2.id_number) AS id_number, COUNT(T1.id) AS course_count, SUM(T1.score) AS total_score
    FROM exam_score AS T1
    JOIN exam_student AS T2 ON T1.exam_plan_id = T2.exam_plan_id AND T1.exam_student_id = T2.id
    WHERE T1.exam_plan_id = @examSampleRefExamPlanId
    GROUP BY T1.sys_org_id, T1.school_class_id, T2.certificate_type, T2.id_number
) AS T2 
-- ON T1.sys_org_id = T2.sys_org_id AND T1.school_class_id = T2.school_class_id AND T1.certificate_type = T2.certificate_type AND UPPER(T1.id_number) = T2.id_number
ON T1.sys_org_id = T2.sys_org_id AND T1.school_class_id = T2.school_class_id AND UPPER(T1.id_number) = T2.id_number
JOIN 
(
    SELECT DISTINCT exam_plan_id, sys_org_id 
    FROM exam_org 
    WHERE exam_plan_id = @examPlanId AND is_required_exam = 1
) AS EO ON T1.exam_plan_id = EO.exam_plan_id AND T1.sys_org_id = EO.sys_org_id
JOIN sys_org AS ORG ON T1.sys_org_id = ORG.id
-- LEFT JOIN special_student AS T3 ON T1.sys_org_id = T3.sys_org_id AND T1.certificate_type = T3.certificate_type AND UPPER(T1.id_number) = UPPER(T3.id_number)
LEFT JOIN 
(
	SELECT id, exam_plan_id, sys_org_id, certificate_type, id_number 
    FROM exam_special_student
	WHERE exam_plan_id = @examPlanId AND `status` = @specialStudentStatus
-- ) AS T3 ON T1.exam_plan_id = T3.exam_plan_id AND T1.sys_org_id = T3.sys_org_id AND T1.certificate_type = T3.certificate_type AND UPPER(T1.id_number) = UPPER(T3.id_number)
) AS T3 ON T1.exam_plan_id = T3.exam_plan_id AND T1.sys_org_id = T3.sys_org_id AND UPPER(T1.id_number) = UPPER(T3.id_number)
WHERE T1.exam_plan_id = @examPlanId AND ({gradeWhere})
ORDER BY T1.sys_org_id, T1.grade_id, T1.school_class_id, T2.total_score DESC, T1.id
";

        var items = await _rep.SqlQueriesAsync<ExamSampleDto>(selectSql, new
        {
            ExamSampleRefExamPlanId = config.ExamSampleRefExamPlanId ?? 0,
            ExamPlanId = examPlanId,
            SpecialStudentStatus = (short)AuditStatus.APPROVED,
        });
        return items;
    }
    #endregion
}