YBEE.EQM/YBEE.EQM.Application/Exam/ExamScore/Services/ExamScoreImportService.cs

using Furion.ClayObject.Extensions;
using Furion.DatabaseAccessor.Extensions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO.Compression;
using YBEE.EQM.Core;

namespace YBEE.EQM.Application;

/// <summary>
/// 学生成绩导入服务
/// </summary>
public class ExamScoreImportService(IRepository<ExamScore> rep, ISchoolClassService schoolClassService, IExamGradeService examGradeService, IExportExcelService exportExcelService) : IExamScoreImportService, ITransient
{
    private readonly IRepository<ExamScore> _rep = rep;
    private readonly ISchoolClassService _schoolClassService = schoolClassService;
    private readonly IExamGradeService _examGradeService = examGradeService;
    private readonly IExportExcelService _exportExcelService = exportExcelService;

    /// <summary>
    /// 批量导入前期未上报学生名单的各科成绩（初始化）
    /// </summary>
    /// <param name="filePath"></param>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task UploadImportWithoutStudentTotalScore(string filePath, int examPlanId)
    {
        /* ----------------------------------------------------------
         * 处理步骤：
         * 1.验证表头
         * 2.读取数据
         * 3.处理班级
         * 4.处理学生
         * 5.批量插入
        ---------------------------------------------------------- */

        try
        {
            #region 1.验证表结构
            using FileStream fs = new(filePath, FileMode.Open, FileAccess.Read);
            IWorkbook workbook = ExcelUtil.GetWorkbook(filePath, fs);
            var sheet = workbook.GetSheetAt(0);
            var rows = sheet.GetRowEnumerator();

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

            // 读取表头
            rows.MoveNext();
            IRow headerRow = (IRow)rows.Current;
            int index = 0;
            int SAMPLE_TYPE_INDEX = index++;
            int SCHOOL_ID_INDEX = index++;
            int SCHOOL_NAME_INDEX = index++;
            int SCHOOL_BID_INDEX = index++;
            int SCHOOL_BNAME_INDEX = index++;
            int NAME_INDEX = index++;
            int CERT_TYPE_INDEX = index++;
            int ID_NUM_INDEX = index++;
            int EXAM_NUMBER_INDEX = index++;
            int COURSE_COMB_INDEX = index++;
            int GRADE_INDEX = index++;
            int CLASS_INDEX = index++;
            int COURSE_START_INDEX = index;
            Dictionary<int, string> headers = new()
            {
                { SAMPLE_TYPE_INDEX, "抽样类型" },
                { SCHOOL_ID_INDEX, "学校ID" },
                { SCHOOL_NAME_INDEX, "学校" },
                { SCHOOL_BID_INDEX, "校区ID" },
                { SCHOOL_BNAME_INDEX, "校区" },
                { NAME_INDEX, "姓名" },
                { CERT_TYPE_INDEX, "证件类型" },
                { ID_NUM_INDEX, "证件号码" },
                { EXAM_NUMBER_INDEX, "考号" },
                { COURSE_COMB_INDEX, "选科组合" },
                { GRADE_INDEX, "年级" },
                { CLASS_INDEX, "班级" },
            };
            List<string> headerErrors = new();
            for (int i = 0; i < COURSE_START_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($"第1行标题行{columnErrors}列名错误。从A列开始依次应为抽样类型、学校ID、学校、姓名、证件类型、证件号码、考号、年级、班级。");
                //return result;
                return;
            }
            //result.StructureCorrect = true;
            #endregion

            #region 2.读取数据
            var examPlan = await _rep.Change<ExamPlan>().DetachedEntities.FirstOrDefaultAsync(t => t.Id == examPlanId) ?? throw Oops.Oh(ErrorCode.E2001, "监测计划");
            var examGradeDict = (await _examGradeService.GetListByExamPlanId(examPlanId)).ToDictionary(t => t.Grade.GradeNumber);
            var courseDict = (await _rep.Change<Course>().DetachedEntities.ToListAsync()).ToDictionary(t => t.Name);

            // 获取需要导入的科目列表
            Dictionary<int, Course> courses = new();
            for (int gi = COURSE_START_INDEX; gi < headerRow.LastCellNum; gi++)
            {
                var cn = headerRow.GetCell(gi).ToString() ?? "";
                if (string.IsNullOrEmpty(cn))
                {
                    continue;
                }
                courses.Add(gi, courseDict[cn]);
            }

            int rn = 1;
            List<ExamScoreImportDto> sourceItems = new();
            while (rows.MoveNext())
            {
                rn++;
                IRow row = (IRow)rows.Current;
                string rv = row.GetCell(0)?.ToString().Trim() ?? "";
                if (rv == "")
                {
                    break;
                }

                for (int gi = COURSE_START_INDEX; gi < headerRow.LastCellNum; gi++)
                {
                    var course = courses[gi];
                    var sbid = row.GetCell(SCHOOL_BID_INDEX)?.NumericCellValue;
                    var ccid = row.GetCell(COURSE_COMB_INDEX)?.NumericCellValue;
                    ExamScoreImportDto item = new()
                    {
                        RowNumber = rn,
                        SampleType = (ExamSampleType)row.GetCell(SAMPLE_TYPE_INDEX).NumericCellValue,
                        SysOrgId = (short)row.GetCell(SCHOOL_ID_INDEX).NumericCellValue,
                        SysOrgBranchId = sbid > 0 ? (short)sbid : null,
                        StudentName = (row.GetCell(NAME_INDEX)?.ToString() ?? "").ClearWhitespace(),
                        CertificateType = (CertificateType)row.GetCell(CERT_TYPE_INDEX).NumericCellValue,
                        IdNumber = (row.GetCell(ID_NUM_INDEX)?.ToString() ?? "").ClearWhitespace(),
                        ExamNumber = row.GetCell(EXAM_NUMBER_INDEX)?.ToString() ?? "",
                        GradeNumber = (short)row.GetCell(GRADE_INDEX).NumericCellValue,
                        ClassNumber = (short)row.GetCell(CLASS_INDEX).NumericCellValue,
                        CourseId = course.Id,
                        NceeCourseCombId = ccid > 0 ? (short)ccid : null,
                    };
                    string scoreStr = row.GetCell(gi)?.ToString() ?? "";
                    if (decimal.TryParse(scoreStr, out decimal score))
                    {
                        item.Score = score;
                    }
                    else if (scoreStr.Contains('特') || scoreStr.Contains('缺'))
                    {
                        item.IsExcluded = true;
                    }

                    if (item.IdNumber.Length == 18)
                    {
                        if (int.TryParse(item.IdNumber.AsSpan(16, 1), out int gn))
                        {
                            item.Gender = (short)(gn % 2 == 0 ? 2 : 1);
                        }
                    }

                    var eg = examGradeDict[item.GradeNumber];
                    item.ExamGradeId = eg.Id;
                    item.GradeId = eg.GradeId;
                    item.GradeBeginYear = eg.GradeBeginYear;
                    item.GradeEndYear = eg.GradeEndYear;
                    item.SchoolClassId = _schoolClassService.GetSchoolClassId(examPlan.EducationStage, item.SysOrgId, eg.GradeBeginYear, item.ClassNumber);

                    sourceItems.Add(item);
                }
            }
            workbook.Close();
            fs.Close();
            #endregion

            #region 3.处理班级
            var scs = sourceItems.GroupBy(t => t.SchoolClassId).Select(t => new BatchInsertSchoolClassInput
            {
                Id = t.Key,
                SysOrgId = t.FirstOrDefault().SysOrgId,
                SysOrgBranchId = t.FirstOrDefault().SysOrgBranchId,
                EducationStage = examPlan.EducationStage,
                EducationYears = examPlan.EducationYears,
                ClassNumber = t.FirstOrDefault().ClassNumber,
                GradeBeginYear = t.FirstOrDefault().GradeBeginYear,
                GradeEndYear = t.FirstOrDefault().GradeEndYear,
            }).Distinct().ToList();
            // 插入没有的班级
            await _schoolClassService.BatchInsert(scs);
            #endregion

            #region 4.处理学生
            // 清理学生
            string deleteStudentSql = $"DELETE FROM exam_student WHERE exam_plan_id = {examPlanId};";
            await _rep.SqlNonQueryAsync(deleteStudentSql);

            var uid = CurrentSysUserInfo.SysUserId;
            if (uid == 0)
            {
                uid = 1;
            }

            var stus = sourceItems.GroupBy(t => t.RowNumber).Select(t => t.FirstOrDefault()).ToList();
            List<string> insertValues = new();
            int scount = stus.Count;
            for (int i = 0; i < scount; i++)
            {
                var t = stus[i];

                string sobid = "NULL";
                if (t.SysOrgBranchId.HasValue)
                {
                    sobid = t.SysOrgBranchId.ToString();
                }

                insertValues.Add($"({examPlanId}, {t.SysOrgId}, {sobid}, {t.ExamGradeId}, {t.GradeId}, {t.SchoolClassId}, {t.ClassNumber}, '{t.ExamNumber}', '{t.StudentName}', {(short)t.CertificateType}, '{t.IdNumber}', {t.Gender}, {uid}, CURRENT_TIMESTAMP, 0)");
                if ((i + 1) % 1000 == 0 || i == scount - 1)
                {
                    string insertSql = $@"
INSERT INTO exam_student(exam_plan_id, sys_org_id, sys_org_branch_id, exam_grade_id, grade_id, school_class_id, class_number, exam_number, `name`, certificate_type, id_number, gender, create_sys_user_id, create_time, is_deleted) VALUES 
{string.Join(",", insertValues)}
";
                    await _rep.SqlNonQueryAsync(insertSql);
                    insertValues.Clear();
                }
            }
            #endregion

            #region 5.批量导入
            // 清理成绩
            string deleteScoreSql = $"DELETE FROM exam_score WHERE exam_plan_id = {examPlanId};";
            await _rep.SqlNonQueryAsync(deleteScoreSql);

            List<string> selects = new();
            scount = sourceItems.Count;
            for (int i = 0; i < scount; i++)
            {
                var t = sourceItems[i];

                string sobid = "NULL";
                if (t.SysOrgBranchId.HasValue)
                {
                    sobid = t.SysOrgBranchId.ToString();
                }
                string ccid = "NULL";
                if (t.NceeCourseCombId.HasValue)
                {
                    ccid = t.NceeCourseCombId.ToString();
                }
                var ied = t.IsExcluded ? 1 : 0;

                selects.Add($@"
SELECT {(short)t.SampleType} est, {t.SysOrgId} soid, {sobid} as sobid, {t.GradeId} gid, {examPlan.SemesterId} smid, {t.SchoolClassId} scid, {t.ClassNumber} cn, '{t.ExamNumber}' en, {ccid} ccid, {t.CourseId} cid, {t.Score} s, {ied} ied ");

                if ((i + 1) % 500 == 0 || i == scount - 1)
                {
                    string insertSql = $@"
INSERT INTO exam_score(exam_plan_id, education_stage, exam_sample_type, sys_org_id, sys_org_branch_id, grade_id, semester_id, school_class_id, class_number, exam_student_id, ncee_course_comb_id, course_id, score, is_excluded)
SELECT {examPlanId}, {(short)examPlan.EducationStage}, T1.est, T1.soid, T1.sobid, T1.gid, T1.smid, T1.scid, T1.cn, T2.id, T1.ccid, T1.cid, T1.s, T1.ied
FROM
({string.Join("UNION ALL", selects)}
) AS T1
JOIN (SELECT id, school_class_id, exam_number FROM exam_student WHERE exam_plan_id = {examPlanId}) AS T2
ON T1.scid = T2.school_class_id AND T1.en = T2.exam_number
";
                    await _rep.SqlNonQueryAsync(insertSql);
                    selects.Clear();
                }
            }
            #endregion
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            File.Delete(filePath);
        }
    }

    /// <summary>
    /// 批量导入学生总成绩
    /// </summary>
    /// <param name="filePath"></param>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task UploadImportStudentTotalScore(string filePath, int examPlanId)
    {
        /* ----------------------------------------------------------
         * 处理步骤：
         * 1.验证表头
         * 2.读取数据
         * 3.处理班级
         * 4.批量插入
        ---------------------------------------------------------- */

        try
        {
            #region 1.验证表结构
            using FileStream fs = new(filePath, FileMode.Open, FileAccess.Read);
            IWorkbook workbook = ExcelUtil.GetWorkbook(filePath, fs);
            var sheet = workbook.GetSheetAt(0);
            var rows = sheet.GetRowEnumerator();

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

            // 读取表头
            rows.MoveNext();
            IRow headerRow = (IRow)rows.Current;
            int index = 0;
            int SAMPLE_TYPE_INDEX = index++;
            int SCHOOL_ID_INDEX = index++;
            int SCHOOL_NAME_INDEX = index++;
            int SCHOOL_BID_INDEX = index++;
            int SCHOOL_BNAME_INDEX = index++;
            int NAME_INDEX = index++;
            int CERT_TYPE_INDEX = index++;
            int ID_NUM_INDEX = index++;
            int EXAM_NUMBER_INDEX = index++;
            int COURSE_COMB_INDEX = index++;
            int GRADE_INDEX = index++;
            int CLASS_INDEX = index++;
            int SP_INDEX = index++;
            int ABSENT_REPLACE_INDEX = index++;
            int COURSE_START_INDEX = index;
            Dictionary<int, string> headers = new()
            {
                { SAMPLE_TYPE_INDEX, "抽样类型" },
                { SCHOOL_ID_INDEX, "学校ID" },
                { SCHOOL_NAME_INDEX, "学校" },
                { SCHOOL_BID_INDEX, "校区ID" },
                { SCHOOL_BNAME_INDEX, "校区" },
                { NAME_INDEX, "姓名" },
                { CERT_TYPE_INDEX, "证件类型" },
                { ID_NUM_INDEX, "证件号码" },
                { EXAM_NUMBER_INDEX, "考号" },
                { COURSE_COMB_INDEX, "选科组合" },
                { GRADE_INDEX, "年级" },
                { CLASS_INDEX, "班级" },
                { SP_INDEX, "特殊学生" },
                { ABSENT_REPLACE_INDEX, "缺测替补" },
            };
            List<string> headerErrors = [];
            for (int i = 0; i < COURSE_START_INDEX; i++)
            {
                if (headerRow.GetCell(i)?.ToString() != headers[i])
                {
                    char letter = (char)('A' + i);
                    headerErrors.Add(letter.ToString());
                }
            }
            if (headerErrors.Count != 0)
            {
                string columnErrors = string.Join("、", headerErrors);
                //result.ErrorMessage.Add($"第1行标题行{columnErrors}列名错误。从A列开始依次应为抽样类型、学校ID、学校、姓名、证件类型、证件号码、考号、年级、班级。");
                //return result;
                return;
            }
            //result.StructureCorrect = true;
            #endregion

            #region 2.读取数据
            var examPlan = await _rep.Change<ExamPlan>().DetachedEntities.FirstOrDefaultAsync(t => t.Id == examPlanId) ?? throw Oops.Oh(ErrorCode.E2001, "监测计划");
            var examGradeDict = (await _examGradeService.GetListByExamPlanId(examPlanId)).ToDictionary(t => t.Grade.GradeNumber);
            var courseDict = (await _rep.Change<Course>().DetachedEntities.ToListAsync()).ToDictionary(t => t.Name);
            var examCourses = await _rep.Change<ExamCourse>().DetachedEntities.Where(t => t.ExamPlanId == examPlanId).Select(t => new { t.GradeId, t.CourseId }).ToListAsync();

            // 获取需要导入的科目列表
            Dictionary<int, Course> courses = [];
            for (int gi = COURSE_START_INDEX; gi < headerRow.LastCellNum; gi++)
            {
                var cn = headerRow.GetCell(gi).ToString() ?? "";
                if (string.IsNullOrEmpty(cn))
                {
                    continue;
                }
                courses.Add(gi, courseDict[cn]);
            }

            int rn = 1;
            List<ExamScoreImportDto> sourceItems = [];
            while (rows.MoveNext())
            {
                rn++;
                IRow row = (IRow)rows.Current;
                string rv = row.GetCell(0)?.ToString().Trim() ?? "";
                if (rv == "")
                {
                    break;
                }

                var sampleType = (ExamSampleType)row.GetCell(SAMPLE_TYPE_INDEX).NumericCellValue;
                var sysOrgId = (short)row.GetCell(SCHOOL_ID_INDEX).NumericCellValue;
                var gradeNumber = (short)row.GetCell(GRADE_INDEX).NumericCellValue;
                var classNumber = (short)row.GetCell(CLASS_INDEX).NumericCellValue;
                var spStr = row.GetCell(SP_INDEX)?.ToString()?.Trim() ?? "";
                var isSp = spStr.Contains("特殊");
                var absentStr = row.GetCell(ABSENT_REPLACE_INDEX)?.ToString()?.Trim() ?? "";
                string remark = isSp ? spStr : "";
                if (absentStr.Length > 0)
                {
                    if (remark.Length == 0)
                    {
                        remark = absentStr;
                    }
                    else
                    {
                        remark = $"{remark}，{absentStr}";
                    }
                }

                for (int gi = COURSE_START_INDEX; gi < headerRow.LastCellNum; gi++)
                {
                    var course = courses[gi];
                    var eg = examGradeDict[gradeNumber];

                    // 跳过未监测科目
                    if (!examCourses.Any(t => t.GradeId == eg.GradeId && t.CourseId == course.Id))
                    {
                        continue;
                    }

                    var sbid = row.GetCell(SCHOOL_BID_INDEX)?.NumericCellValue;
                    var ccid = row.GetCell(COURSE_COMB_INDEX)?.NumericCellValue;
                    ExamScoreImportDto item = new()
                    {
                        RowNumber = rn,
                        SampleType = sampleType,
                        SysOrgId = sysOrgId,
                        SysOrgBranchId = sbid > 0 ? (short)sbid : null,
                        StudentName = (row.GetCell(NAME_INDEX)?.ToString() ?? "").ClearWhitespace(),
                        CertificateType = (CertificateType)row.GetCell(CERT_TYPE_INDEX).NumericCellValue,
                        IdNumber = (row.GetCell(ID_NUM_INDEX)?.ToString() ?? "").ClearWhitespace(),
                        ExamNumber = (row.GetCell(EXAM_NUMBER_INDEX)?.ToString() ?? "").ClearWhitespace(),
                        GradeNumber = gradeNumber,
                        ClassNumber = classNumber,
                        CourseId = course.Id,
                        NceeCourseCombId = ccid > 0 ? (short)ccid : null,
                        Remark = remark,
                    };
                    string scoreStr = row.GetCell(gi)?.ToString() ?? "";
                    if (decimal.TryParse(scoreStr, out decimal score))
                    {
                        item.Score = score;
                    }
                    else if (scoreStr.Contains('特') || scoreStr.Contains('缺'))
                    {
                        item.IsExcluded = true;
                        item.IsAbsent = scoreStr.Contains('缺');
                    }
                    if (isSp)
                    {
                        item.IsSpecial = true;
                        item.IsExcluded = true;
                    }

                    if (item.IdNumber.Length == 18)
                    {
                        if (int.TryParse(item.IdNumber.AsSpan(16, 1), out int gn))
                        {
                            item.Gender = (short)(gn % 2 == 0 ? 2 : 1);
                        }
                    }

                    item.ExamGradeId = eg.Id;
                    item.GradeId = eg.GradeId;
                    item.GradeBeginYear = eg.GradeBeginYear;
                    item.GradeEndYear = eg.GradeEndYear;
                    item.SchoolClassId = _schoolClassService.GetSchoolClassId(examPlan.EducationStage, item.SysOrgId, eg.GradeBeginYear, item.ClassNumber);

                    sourceItems.Add(item);
                }
            }
            workbook.Close();
            fs.Close();
            #endregion

            #region 3.处理班级
            var scs = sourceItems.GroupBy(t => t.SchoolClassId).Select(t => new BatchInsertSchoolClassInput
            {
                Id = t.Key,
                SysOrgId = t.FirstOrDefault().SysOrgId,
                SysOrgBranchId = t.FirstOrDefault().SysOrgBranchId,
                EducationStage = examPlan.EducationStage,
                EducationYears = examPlan.EducationYears,
                ClassNumber = t.FirstOrDefault().ClassNumber,
                GradeBeginYear = t.FirstOrDefault().GradeBeginYear,
                GradeEndYear = t.FirstOrDefault().GradeEndYear,
            }).Distinct().ToList();
            // 插入没有的班级
            await _schoolClassService.BatchInsert(scs);
            #endregion

            #region 4.批量导入
            // 清理成绩
            string deleteScoreSql = $"DELETE FROM exam_score WHERE exam_plan_id = {examPlanId};";
            await _rep.SqlNonQueryAsync(deleteScoreSql);

            List<string> selects = [];
            var scount = sourceItems.Count;
            for (int i = 0; i < scount; i++)
            {
                var t = sourceItems[i];

                string sobid = "NULL";
                if (t.SysOrgBranchId.HasValue)
                {
                    sobid = t.SysOrgBranchId.ToString();
                }
                string ccid = "NULL";
                if (t.NceeCourseCombId.HasValue)
                {
                    ccid = t.NceeCourseCombId.ToString();
                }
                var ied = t.IsExcluded ? 1 : 0;
                var isp = t.IsSpecial ? 1 : 0;
                var isa = t.IsAbsent ? 1 : 0;

                selects.Add($@"
SELECT {(short)t.SampleType} est, {t.SysOrgId} soid, {sobid} as sobid, {t.GradeId} gid, {examPlan.SemesterId} smid, {t.SchoolClassId} scid, {t.ClassNumber} cn, '{t.ExamNumber}' en, {ccid} ccid, {t.CourseId} cid, {t.Score} s, {ied} ied, {isp} isp, {isa} isa, '{t.Remark}' r ");

                if ((i + 1) % 500 == 0 || i == scount - 1)
                {
                    string insertSql = $@"
SET @examSampleId = (SELECT MAX(id) FROM exam_sample WHERE exam_plan_id = {examPlanId} AND is_selected = 1);
INSERT INTO exam_score(exam_plan_id, education_stage, exam_sample_type, sys_org_id, sys_org_branch_id, grade_id, semester_id, school_class_id, class_number, exam_student_id, ncee_course_comb_id, course_id, score, is_excluded, is_special, is_absent, remark, exam_number)
SELECT {examPlanId}, {(short)examPlan.EducationStage}, T1.est, T1.soid, T1.sobid, T1.gid, T1.smid, T1.scid, T1.cn, T2.id, T1.ccid, T1.cid, T1.s, T1.ied, T1.isp, T1.isa, T1.r, T1.en
FROM
({string.Join("UNION ALL", selects)}
) AS T1
JOIN (SELECT exam_number, exam_student_id AS id FROM exam_sample_student WHERE exam_sample_id = @examSampleId) AS T2
ON T1.en = T2.exam_number;
";
                    //await _rep.SqlNonQueryAsync(insertSql);
                    await insertSql.SetCommandTimeout(60000).SqlNonQueryAsync();
                    selects.Clear();
                }
            }
            #endregion

            #region 5.计算总分和分段
            await $@"
-- 更新各科成绩分段
UPDATE exam_score AS T1 
JOIN 
(
    SELECT T1.id, T1.grade_id, T1.course_id, get_exam_score_range_id(T2.exam_score_range_type, T1.score) AS exam_score_range_id
    FROM exam_score AS T1
    JOIN exam_course AS T2 ON T1.exam_plan_id = T2.exam_plan_id AND T1.grade_id = T2.grade_id AND T1.course_id = T2.course_id
    WHERE T1.exam_plan_id = {examPlanId}
) AS T2 ON T1.id = T2.id
SET T1.exam_score_range_id = T2.exam_score_range_id
WHERE T1.exam_plan_id = {examPlanId};

-- 更新总成绩和分段
DELETE FROM exam_score_total WHERE exam_plan_id = {examPlanId};
INSERT INTO exam_score_total
(
    exam_plan_id,
    education_stage, 
    exam_sample_type, 
    sys_org_id, 
    sys_org_branch_id, 
    grade_id, semester_id, 
    school_class_id, 
    class_number, 
    exam_student_id, 
    ncee_course_comb_id, 
    course_count, 
    score,
    remark,
    exam_number,
    is_special,
    absent_count,
    is_excluded,
    exam_score_range_id
)
SELECT 
    {examPlanId}, 
    T1.education_stage, 
    T1.exam_sample_type, 
    T1.sys_org_id, 
    T1.sys_org_branch_id, 
    T1.grade_id, 
    T1.semester_id, 
    T1.school_class_id, 
    T1.class_number, 
    T1.exam_student_id, 
    T1.ncee_course_comb_id, 
    T1.course_count, 
    T1.score,
    T1.remark,
    T1.exam_number,
    T1.is_special,
    T1.absent_count,
    CASE WHEN T1.is_excluded > 0 THEN 1 ELSE 0 END AS is_excluded,
    get_exam_score_range_id(T2.exam_score_range_type, T1.score) AS exam_score_range_id
FROM
(
	SELECT 
		education_stage, 
		exam_sample_type, 
		sys_org_id, 
		sys_org_branch_id, 
		grade_id, semester_id, 
		school_class_id, 
		class_number, 
		exam_student_id, 
		ncee_course_comb_id, 
		COUNT(1) AS course_count, 
		SUM(IFNULL(score, 0)) AS score,
        SUM(IFNULL(is_excluded, 0)) AS is_excluded,
        MAX(remark) AS remark,
        MAX(exam_number) AS exam_number,
        MAX(is_special) AS is_special,
        SUM(is_absent) AS absent_count
	FROM exam_score
	WHERE exam_plan_id = {examPlanId}
	GROUP BY education_stage, exam_sample_type, sys_org_id, sys_org_branch_id, grade_id, semester_id, school_class_id, class_number, exam_student_id, ncee_course_comb_id
) AS T1
JOIN
(
    SELECT grade_id, exam_score_range_type FROM exam_grade WHERE exam_plan_id = {examPlanId}
) AS T2 ON T1.grade_id = T2.grade_id;
".SetCommandTimeout(600000).SqlNonQueryAsync();
            #endregion
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            File.Delete(filePath);
        }
    }

    /// <summary>
    /// 上传并合并小题成绩导出
    /// </summary>
    /// <param name="filePath"></param>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    /// <exception cref="Exception"></exception>
    public async Task<(string, byte[])> UploadImportStudentMinorScore(string filePath, int examPlanId)
    {
        var examPlan = await _rep.Change<ExamPlan>().DetachedEntities.FirstOrDefaultAsync(t => t.Id == examPlanId) ?? throw Oops.Oh(ErrorCode.E2001, "监测计划");

        // 解压输出目录
        string unzipPath = Path.Combine(FileUtil.GetTempFileRoot(), Path.GetFileNameWithoutExtension(filePath));
        Directory.CreateDirectory(unzipPath);

        // 临时存放目录
        string outputRoot = Path.Combine(FileUtil.GetTempFileRoot(), $"{Guid.NewGuid()}");
        Directory.CreateDirectory(outputRoot);
        string outputFilePath = Path.Combine(outputRoot, $"{examPlan.Name}-区校合并-小题成绩");
        Directory.CreateDirectory(outputFilePath);

        try
        {
            List<ExamScoreMinorFileInfo> fileInfos = [];

            var gradeDict = (await _rep.Change<Grade>().DetachedEntities.Where(t => t.EducationStage == examPlan.EducationStage).ToListAsync()).ToDictionary(t => t.Name);
            var courseDict = (await _rep.Change<Course>().DetachedEntities.ToListAsync()).ToDictionary(t => t.Name);

            #region 1.读取文件信息
            ZipFile.ExtractToDirectory(filePath, unzipPath, System.Text.Encoding.GetEncoding("GBK"), true);
            var sampleTypePaths = Directory.GetDirectories(unzipPath);
            foreach (var stpath in sampleTypePaths)
            {
                var tn = Path.GetFileNameWithoutExtension(stpath).Split("-");
                ExamSampleType est;
                if (int.TryParse(tn[0], out int estInt))
                {
                    est = (ExamSampleType)estInt;
                }
                else
                {
                    throw new ArgumentException("监测抽样类型错误");
                }

                var scoreFiles = Directory.GetFiles(stpath).Where(t => t.EndsWith(".xlsx") || t.EndsWith(".xls")).Where(t => !t.StartsWith("~"));
                foreach (var scoreFile in scoreFiles)
                {
                    var fn = Path.GetFileNameWithoutExtension(scoreFile).Split("-");
                    var gradeName = fn[1];
                    var grade = gradeDict[gradeName];
                    var courseName = fn[2];
                    var course = courseDict[courseName];
                    fileInfos.Add(new()
                    {
                        ExamSampleType = est,
                        FilePath = scoreFile,
                        GradeId = grade.Id,
                        Grade = grade,
                        CourseId = course.Id,
                        Course = course,
                    });
                }
            }
            #endregion

            #region 2.合并文件
            var cfiles = fileInfos.Where(t => t.ExamSampleType == ExamSampleType.DISTRICT);
            foreach (var cfile in cfiles)
            {
                var qminors = await _rep.Change<ExamPaperQuestionMinor>().DetachedEntities.Where(t => t.ExamPaper.ExamPlanId == examPlanId && t.ExamPaper.CourseId == cfile.CourseId && t.ExamPaper.GradeId == cfile.GradeId && t.IsLeaf == true).ToListAsync();
                var examSampleStudents = await _rep.Change<ExamSampleStudent>().DetachedEntities
                                                                               .Where(t => t.ExamSample.ExamPlanId == examPlanId && t.ExamSample.IsSelected == true && t.ExamStudent.GradeId == cfile.GradeId)
                                                                               .Select(t => new { t.ExamNumber, t.ExamStudentId, t.ExamStudent.SysOrgId })
                                                                               .ToListAsync();
                var sfile = fileInfos.FirstOrDefault(t => t.ExamSampleType == ExamSampleType.SCHOOL_EXAM && t.GradeId == cfile.GradeId && t.CourseId == cfile.CourseId) ?? throw new ArgumentNullException($"{cfile}对应校考小题成绩未找到");

                // 区文件
                using FileStream c_fs = new(cfile.FilePath, FileMode.Open, FileAccess.Read);
                IWorkbook c_workbook = ExcelUtil.GetWorkbook(filePath, c_fs);
                var c_sheet = c_workbook.GetSheetAt(0);
                var c_rows = c_sheet.GetRowEnumerator();

                // 输出文件
                XSSFWorkbook o_workbook = new();
                ISheet o_sheet = o_workbook.CreateSheet();
                var o_cellStyles = _exportExcelService.GetCellStyle(o_workbook);
                int o_rowNum = 0;
                IRow o_headerRow = o_sheet.CreateRow(o_rowNum++);

                // 标题行
                Dictionary<int, ExamPaperQuestionMinor> colMinor = [];
                c_rows.MoveNext();
                var c_headerRow = (IRow)c_rows.Current;
                int colNum = c_headerRow.LastCellNum;
                for (int ci = 0; ci < colNum; ci++)
                {
                    string colName = c_headerRow.GetCell(ci)?.ToString() ?? "";
                    _exportExcelService.AddCell(c_headerRow.GetCell(ci)?.ToString(), o_headerRow, ci, o_cellStyles.CenterCellStyle, o_sheet, width: ci == 1 ? 12 : ci == 2 ? 30 : null);
                    var qm = qminors.FirstOrDefault(t => t.ColumnName == colName);
                    if (qm != null)
                    {
                        colMinor.Add(ci, qm);
                    }
                }

                // 所有小题成绩列表
                List<ExamScoreMinorImportDto> items = [];

                // 区文件写入
                while (c_rows.MoveNext())
                {
                    var row = (IRow)c_rows.Current;
                    var c0 = row.GetCell(0);
                    if (c0 == null || c0.ToString() == "")
                    {
                        break;
                    }

                    ExamScoreMinorImportDto item = new();
                    IRow o_row = o_sheet.CreateRow(o_rowNum++);
                    for (int tci = 0; tci < colNum; tci++)
                    {
                        var cell = row.GetCell(tci);
                        if (cell == null)
                        {
                            continue;
                        }
                        if (cell.CellType == CellType.Numeric)
                        {
                            _exportExcelService.AddCell(cell.NumericCellValue, o_row, tci, o_cellStyles.CenterCellStyle, o_sheet);
                        }
                        else
                        {
                            _exportExcelService.AddCell(cell.ToString(), o_row, tci, o_cellStyles.CenterCellStyle, o_sheet);
                        }

                        if (tci == 1)
                        {
                            var examNumber = cell.ToString();
                            var stu = examSampleStudents.FirstOrDefault(t => t.ExamNumber == examNumber);
                            if (stu != null)
                            {
                                item.ExamStudentId = stu.ExamStudentId;
                                item.SysOrgId = stu.SysOrgId;
                            }
                        }
                        else if (tci >= 7 && colMinor.TryGetValue(tci, out ExamPaperQuestionMinor cm))
                        {
                            if (cell.CellType == CellType.Numeric)
                            {
                                item.Score = (decimal)cell.NumericCellValue;
                            }
                            else
                            {
                                if (decimal.TryParse(cell.ToString(), out decimal s))
                                {
                                    item.Score = s;
                                }
                            }
                            item.StarScore = cm.Score == 0 ? 0 : item.Score / cm.Score;
                            if (item.ExamStudentId != 0)
                            {
                                items.Add(new()
                                {
                                    ExamStudentId = item.ExamStudentId,
                                    SysOrgId = item.SysOrgId,
                                    ExamPaperQuestionMinorId = cm.Id,
                                    Score = item.Score,
                                    StarScore = item.StarScore
                                });
                            }
                        }
                    }
                }

                // 校文件
                using FileStream s_fs = new(sfile.FilePath, FileMode.Open, FileAccess.Read);
                IWorkbook s_workbook = ExcelUtil.GetWorkbook(sfile.FilePath, s_fs);
                var s_sheet = s_workbook.GetSheetAt(0);
                var s_rows = s_sheet.GetRowEnumerator();

                s_rows.MoveNext();
                // 校文件写入
                while (s_rows.MoveNext())
                {
                    var row = (IRow)s_rows.Current;
                    var c0 = row.GetCell(0);
                    if (c0 == null || c0.ToString() == "")
                    {
                        break;
                    }

                    ExamScoreMinorImportDto item = new();
                    IRow o_row = o_sheet.CreateRow(o_rowNum++);
                    for (int tci = 0; tci < colNum; tci++)
                    {
                        var cell = row.GetCell(tci);
                        if (cell == null)
                        {
                            continue;
                        }
                        if (cell.CellType == CellType.Numeric)
                        {
                            _exportExcelService.AddCell(cell.NumericCellValue, o_row, tci, o_cellStyles.CenterCellStyle, o_sheet);
                        }
                        else
                        {
                            _exportExcelService.AddCell(cell.ToString(), o_row, tci, o_cellStyles.CenterCellStyle, o_sheet);
                        }

                        if (tci == 1)
                        {
                            var examNumber = cell.ToString();
                            var stu = examSampleStudents.FirstOrDefault(t => t.ExamNumber == examNumber);
                            if (stu != null)
                            {
                                item.ExamStudentId = stu.ExamStudentId;
                                item.SysOrgId = stu.SysOrgId;
                            }
                        }
                        else if (tci >= 7 && colMinor.TryGetValue(tci, out ExamPaperQuestionMinor cm))
                        {
                            if (cell.CellType == CellType.Numeric)
                            {
                                item.Score = (decimal)cell.NumericCellValue;
                            }
                            else
                            {
                                if (decimal.TryParse(cell.ToString(), out decimal s))
                                {
                                    item.Score = s;
                                }
                            }
                            item.StarScore = cm.Score == 0 ? 0 : item.Score / cm.Score;
                            if (item.ExamStudentId != 0)
                            {
                                items.Add(new()
                                {
                                    ExamStudentId = item.ExamStudentId,
                                    SysOrgId = item.SysOrgId,
                                    ExamPaperQuestionMinorId = cm.Id,
                                    Score = item.Score,
                                    StarScore = item.StarScore
                                });
                            }
                        }
                    }
                }

                o_sheet.CreateFreezePane(0, 1);
                MemoryStream o_ms = new();
                o_workbook.Write(o_ms, false);
                o_ms.Flush();
                await File.WriteAllBytesAsync(Path.Combine(outputFilePath, $"{examPlan.EducationStage.GetDescription()}-{cfile.Grade.Name}-{cfile.Course.Name}-小题成绩.xlsx"), o_ms.ToArray());

                #region 批量导入
                // 清理成绩
                string deleteScoreSql = $"DELETE FROM exam_score_minor WHERE exam_plan_id = {examPlanId} AND grade_id = {cfile.GradeId} AND course_id = {cfile.CourseId};";
                //await _rep.SqlNonQueryAsync(deleteScoreSql);
                await deleteScoreSql.SetCommandTimeout(60000).SqlNonQueryAsync();

                List<string> values = [];
                var scount = items.Count;
                for (int i = 0; i < scount; i++)
                {
                    var item = items[i];
                    values.Add($"({examPlanId},{item.SysOrgId},{item.ExamStudentId},{cfile.GradeId},{cfile.CourseId},{item.ExamPaperQuestionMinorId},{item.Score},{item.StarScore})");
                    if ((i + 1) % 2000 == 0 || i == scount - 1)
                    {
                        string insertSql = $"INSERT INTO exam_score_minor(exam_plan_id,sys_org_id,exam_student_id,grade_id,course_id,exam_paper_question_minor_id,score,star_score) VALUES {string.Join(",", values)}";
                        //await _rep.SqlNonQueryAsync(insertSql);
                        await insertSql.SetCommandTimeout(60000).SqlNonQueryAsync();
                        values.Clear();
                    }
                }
                #endregion
            }
            #endregion

            string outFileName = $"{examPlan.Name}-区校合并-小题成绩.zip";
            string oFilePath = Path.Combine(outputRoot, outFileName);
            ICSharpCode.SharpZipLib.Zip.FastZip zip = new();
            zip.CreateZip(oFilePath, outputFilePath, true, string.Empty);

            var retBytes = await File.ReadAllBytesAsync(oFilePath);
            return (outFileName, retBytes);
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            File.Delete(filePath);
            Directory.Delete(unzipPath, true);
            Directory.Delete(outputRoot, true);
        }
    }
}