YBEE.EQM/YBEE.EQM.Application/Ncee/NceeScore/Services/NceeScoreService.cs

using Furion.JsonSerialization;
using NPOI.SS.UserModel;
using YBEE.EQM.Core;

namespace YBEE.EQM.Application;

/// <summary>
/// 高中成绩管理服务
/// </summary>
public class NceeScoreService(IRepository<NceeScore> rep) : INceeScoreService, ITransient
{
    private readonly IRepository<NceeScore> _rep = rep;
    private readonly List<short> _chooseCourses = [5, 6, 7, 9];

    /// <summary>
    /// 上传成绩（仅原始分，适用于五区联考或本区独立赋分）
    /// </summary>
    /// <param name="filePath"></param>
    /// <param name="nceePlanId"></param>
    /// <returns></returns>
    public async Task UploadOnlyRawScore(string filePath, int nceePlanId)
    {
        /* ----------------------------------------------------------
         * 处理步骤：
         * 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();
            var evaluator = ExcelUtil.GetEvaluator(workbook);

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

            // 读取表头
            rows.MoveNext();
            IRow headerRow = (IRow)rows.Current;
            int index = 0;

            int ORG_ID = index++;
            int ORG_NAME = index++;
            int NAME_INDEX = index++;
            int EXAM_NUMBER_INDEX = index++;
            int CLASS_INDEX = index++;
            int DIRECTION_INDEX = index++;
            int DIRECTION_ID_INDEX = index++;
            int COURSE_COMB_INDEX = index++;
            int COURSE_COMB_ID_INDEX = index++;

            int COURSE_START_INDEX = index;
            Dictionary<int, string> headers = new()
            {
                { ORG_ID, "机构ID" },
                { ORG_NAME, "机构" },
                { NAME_INDEX, "姓名" },
                { EXAM_NUMBER_INDEX, "考号" },
                { CLASS_INDEX, "班级号" },
                { DIRECTION_INDEX, "选择方向" },
                { DIRECTION_ID_INDEX, "选择方向科目ID" },
                { COURSE_COMB_INDEX, "选科组合" },
                { COURSE_COMB_ID_INDEX, "选科组合ID" },
            };
            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;
                throw new Exception("列头错误");
            }
            //result.StructureCorrect = true;
            #endregion

            #region 2.读取数据
            var plan = await _rep.Change<NceePlan>().DetachedEntities.FirstOrDefaultAsync(t => t.Id == nceePlanId) ?? throw Oops.Oh(ErrorCode.E2001, "计划");
            var courseDict = (await _rep.Change<Course>().DetachedEntities.ToListAsync()).ToDictionary(t => t.Name);
            //var orgs = await _rep.Change<SysOrg>().DetachedEntities.Where(t => t.EducationStage == EducationStage.SENIOR_HIGH_SCHOOL_STAGE).ToListAsync();
            var courseCombs = await _rep.Change<NceeCourseComb>().DetachedEntities.ToListAsync();
            var courseCombDict = courseCombs.ToDictionary(t => t.Id, t => t);

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

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


                NceeStudentImportDto stu = new();
                // 机构ID
                if (short.TryParse(row.GetCell(ORG_ID)?.ToString(), out short orgId))
                {
                    stu.SysOrgId = orgId;
                }
                else
                {
                    throw new Exception($"[{rn}]机构ID错误");
                }
                // 姓名
                stu.Name = row.GetCell(NAME_INDEX)?.ToString();
                // 考号
                stu.ExamNumber = row.GetCell(EXAM_NUMBER_INDEX)?.ToString();
                // 班号
                if (short.TryParse(row.GetCell(CLASS_INDEX)?.ToString(), out short cn))
                {
                    stu.ClassNumber = cn;
                }
                else
                {
                    throw new Exception($"[{rn}]班号错误");
                }
                // 方向ID
                if (short.TryParse(row.GetCell(DIRECTION_ID_INDEX)?.ToString(), out short directionId))
                {
                    stu.DirectionCourseId = directionId;
                }
                //else
                //{
                //    throw new Exception($"[{rn}]方向ID错误");
                //}
                // 组合ID
                if (short.TryParse(row.GetCell(COURSE_COMB_ID_INDEX)?.ToString(), out short courseCombId))
                {
                    stu.NceeCourseCombId = courseCombId;
                }
                //else
                //{
                //    throw new Exception($"[{rn}]组合ID错误");
                //}

                // 取各科成绩
                int sc = 0;
                for (int gi = COURSE_START_INDEX; gi < validCellNum; gi++)
                {
                    var cell = row.GetCell(gi);
                    if (cell == null || cell.ToString().Trim() == "")
                    {
                        continue;
                    }

                    if (!courses.ContainsKey(gi)) { continue; }
                    var course = courses[gi];
                    NceeScoreImportDto item = new()
                    {
                        NceeStudentId = stu.Id,
                        CourseId = course.Id,
                    };

                    if (cell.CellType == CellType.Numeric)
                    {
                        item.Score = (decimal)cell.NumericCellValue;
                    }
                    else if (cell.CellType == CellType.Formula)
                    {
                        var cv = evaluator.Evaluate(cell);
                        if (cv.CellType == CellType.Numeric)
                        {
                            item.Score = (decimal)cv.NumberValue;
                        }
                        else
                        {
                            continue;
                        }
                    }

                    if (item.Score > 0)
                    {
                        if (!_chooseCourses.Any(t => t == course.Id))
                        {
                            item.ScoreX = item.Score;
                        }
                        scores.Add(item);
                        sc++;
                    }
                }
                if (sc > 0)
                {
                    students.Add(stu);
                }
            }
            workbook.Close();
            fs.Close();
            #endregion

            #region 3.处理学生
            // 清理学生
            string deleteStudentSql = $"DELETE FROM ncee_student WHERE ncee_plan_id = {nceePlanId};";
            await _rep.SqlNonQueryAsync(deleteStudentSql);

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

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

                string dcid = "NULL";
                if (t.DirectionCourseId.HasValue)
                {
                    dcid = t.DirectionCourseId.Value.ToString();
                }
                string nccid = "NULL";
                if (t.NceeCourseCombId.HasValue)
                {
                    nccid = t.NceeCourseCombId.Value.ToString();
                }

                insertValues.Add($"({t.Id}, {nceePlanId}, {t.SysOrgId}, {plan.GradeId}, {t.ClassNumber}, '{t.ExamNumber}', '{t.Name}', {dcid}, {nccid}, 0, 0)");
                if ((i + 1) % 1000 == 0 || i == scount - 1)
                {
                    string insertSql = $@"
INSERT INTO ncee_student(id, ncee_plan_id, sys_org_id, grade_id, class_number, exam_number, `name`, direction_course_id, ncee_course_comb_id, score, score_x) VALUES 
{string.Join(",", insertValues)}
";
                    await _rep.SqlNonQueryAsync(insertSql);
                    insertValues.Clear();
                }
            }
            #endregion

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

            insertValues.Clear();
            scount = scores.Count;
            for (int i = 0; i < scount; i++)
            {
                var t = scores[i];

                insertValues.Add($"({t.Id}, {nceePlanId}, {t.NceeStudentId}, {t.CourseId}, {t.Score}, {t.ScoreX})");
                if ((i + 1) % 1000 == 0 || i == scount - 1)
                {
                    string insertSql = $@"
INSERT INTO ncee_score(id, ncee_plan_id, ncee_student_id, course_id, score, score_x) VALUES
{string.Join(",", insertValues)}
";
                    await _rep.SqlNonQueryAsync(insertSql);
                    insertValues.Clear();
                }
            }
            #endregion
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            File.Delete(filePath);
        }
    }
    /// <summary>
    /// 上传成绩（带转换分和等级，适用于六校联考）
    /// </summary>
    /// <param name="filePath"></param>
    /// <param name="nceePlanId"></param>
    /// <returns></returns>
    /// <exception cref="Exception"></exception>
    public async Task UploadWithConvertScore(string filePath, int nceePlanId)
    {
        /* ----------------------------------------------------------
         * 处理步骤：
         * 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();
            var evaluator = ExcelUtil.GetEvaluator(workbook);

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

            // 读取表头
            rows.MoveNext();
            IRow headerRow = (IRow)rows.Current;
            int index = 0;

            int ORG_ID = index++;
            int ORG_NAME = index++;
            int NAME_INDEX = index++;
            int EXAM_NUMBER_INDEX = index++;
            int CLASS_INDEX = index++;
            int DIRECTION_INDEX = index++;
            int DIRECTION_ID_INDEX = index++;
            int COURSE_COMB_INDEX = index++;
            int COURSE_COMB_ID_INDEX = index++;

            int COURSE_START_INDEX = index;
            Dictionary<int, string> headers = new()
            {
                { ORG_ID, "机构ID" },
                { ORG_NAME, "机构" },
                { NAME_INDEX, "姓名" },
                { EXAM_NUMBER_INDEX, "考号" },
                { CLASS_INDEX, "班级号" },
                { DIRECTION_INDEX, "选择方向" },
                { DIRECTION_ID_INDEX, "选择方向科目ID" },
                { COURSE_COMB_INDEX, "选科组合" },
                { COURSE_COMB_ID_INDEX, "选科组合ID" },
            };
            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;
                throw new Exception("列头错误");
            }
            //result.StructureCorrect = true;
            #endregion

            #region 2.读取数据
            var plan = await _rep.Change<NceePlan>().DetachedEntities.FirstOrDefaultAsync(t => t.Id == nceePlanId) ?? throw Oops.Oh(ErrorCode.E2001, "计划");
            var courseDict = (await _rep.Change<Course>().DetachedEntities.ToListAsync()).ToDictionary(t => t.Name);
            //var orgs = await _rep.Change<SysOrg>().DetachedEntities.Where(t => t.EducationStage == EducationStage.SENIOR_HIGH_SCHOOL_STAGE).ToListAsync();
            var courseCombs = await _rep.Change<NceeCourseComb>().DetachedEntities.ToListAsync();
            var courseCombDict = courseCombs.ToDictionary(t => t.Id, t => t);
            var convertGrades = await _rep.Change<NceeConvertGrade>().DetachedEntities.ToListAsync();
            var convertGradeDict = convertGrades.ToDictionary(t => t.Name);

            // 获取需要导入的科目列表
            Dictionary<int, NceeScoreImportCourseDto> courses = new();
            int validCellNum = COURSE_START_INDEX;
            for (int gi = COURSE_START_INDEX; gi < headerRow.LastCellNum; gi++)
            {
                var cn = headerRow.GetCell(gi)?.ToString()?.Trim() ?? "";
                if (string.IsNullOrEmpty(cn))
                {
                    continue;
                }
                string tm = cn[^1..].ToUpper();
                var cnn = cn;
                if (tm == "X" || tm == "G")
                {
                    cnn = cn[..^1];
                }
                courses.Add(gi, new()
                {
                    CourseName = cn,
                    Course = courseDict[cnn],
                    IsRawScore = tm != "X" && tm != "G",
                    IsConvertGrade = tm == "G",
                    IsConvertScore = tm == "X",
                });
                validCellNum = gi + 1;
            }

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


                NceeStudentImportDto stu = new();
                // 机构ID
                if (short.TryParse(row.GetCell(ORG_ID)?.ToString(), out short orgId))
                {
                    stu.SysOrgId = orgId;
                }
                else
                {
                    throw new Exception($"[{rn}]机构ID错误");
                }
                // 姓名
                stu.Name = row.GetCell(NAME_INDEX)?.ToString();
                // 考号
                stu.ExamNumber = row.GetCell(EXAM_NUMBER_INDEX)?.ToString();
                // 班号
                if (short.TryParse(row.GetCell(CLASS_INDEX)?.ToString(), out short cn))
                {
                    stu.ClassNumber = cn;
                }
                else
                {
                    throw new Exception($"[{rn}]班号错误");
                }
                // 方向ID
                if (short.TryParse(row.GetCell(DIRECTION_ID_INDEX)?.ToString(), out short directionId))
                {
                    stu.DirectionCourseId = directionId;
                }
                //else
                //{
                //    throw new Exception($"[{rn}]方向ID错误");
                //}
                // 组合ID
                if (short.TryParse(row.GetCell(COURSE_COMB_ID_INDEX)?.ToString(), out short courseCombId))
                {
                    stu.NceeCourseCombId = courseCombId;
                }
                //else
                //{
                //    throw new Exception($"[{rn}]组合ID错误");
                //}

                // 取各科成绩
                List<NceeScoreImportDto> courseScores = [];
                for (int gi = COURSE_START_INDEX; gi < validCellNum; gi++)
                {
                    var cell = row.GetCell(gi);
                    if (cell == null || cell.ToString().Trim() == "")
                    {
                        continue;
                    }

                    if (!courses.ContainsKey(gi)) { continue; }
                    var course = courses[gi];
                    NceeScoreImportDto item;
                    if (course.IsConvertScore || course.IsConvertGrade)
                    {
                        item = courseScores.FirstOrDefault(t => t.CourseId == course.Course.Id);
                    }
                    else
                    {
                        item = new()
                        {
                            NceeStudentId = stu.Id,
                            CourseId = course.Course.Id,
                        };
                    }

                    if (course.IsConvertGrade == false)
                    {
                        decimal score = 0;
                        if (cell.CellType == CellType.Numeric)
                        {
                            score = (decimal)cell.NumericCellValue;
                        }
                        else if (cell.CellType == CellType.Formula)
                        {
                            var cv = evaluator.Evaluate(cell);
                            if (cv.CellType == CellType.Numeric)
                            {
                                score = (decimal)cv.NumberValue;
                            }
                            else
                            {
                                continue;
                            }
                        }

                        if (score > 0)
                        {
                            if (course.IsRawScore == true)
                            {
                                item.Score = score;
                                if (!_chooseCourses.Any(t => t == course.Course.Id))
                                {
                                    item.ScoreX = score;
                                }
                                courseScores.Add(item);
                            }
                            else if (course.IsConvertScore == true)
                            {
                                item.ScoreX = score;
                            }
                        }
                    }
                    else
                    {
                        var gn = cell.ToString().Trim().ToUpper();
                        var g = convertGradeDict[gn];
                        item.NceeConvertGradeId = g.Id;
                        item.NceeConvertGradeName = g.Name;
                    }
                }

                if (courseScores.Count > 0)
                {
                    stu.Score = courseScores.Sum(t => t.Score);
                    stu.ScoreX = courseScores.Sum(t => t.ScoreX);
                    students.Add(stu);
                    scores.AddRange(courseScores);
                }
            }
            workbook.Close();
            fs.Close();
            #endregion

            #region 3.处理学生
            // 清理学生
            string deleteStudentSql = $"DELETE FROM ncee_student WHERE ncee_plan_id = {nceePlanId};";
            await _rep.SqlNonQueryAsync(deleteStudentSql);

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

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

                string dcid = "NULL";
                if (t.DirectionCourseId.HasValue)
                {
                    dcid = t.DirectionCourseId.Value.ToString();
                }
                string nccid = "NULL";
                if (t.NceeCourseCombId.HasValue)
                {
                    nccid = t.NceeCourseCombId.Value.ToString();
                }

                insertValues.Add($"({t.Id}, {nceePlanId}, {t.SysOrgId}, {plan.GradeId}, {t.ClassNumber}, '{t.ExamNumber}', '{t.Name}', {dcid}, {nccid}, {t.Score}, {t.ScoreX})");
                if ((i + 1) % 1000 == 0 || i == scount - 1)
                {
                    string insertSql = $@"
INSERT INTO ncee_student(id, ncee_plan_id, sys_org_id, grade_id, class_number, exam_number, `name`, direction_course_id, ncee_course_comb_id, score, score_x) VALUES 
{string.Join(",", insertValues)}
";
                    await _rep.SqlNonQueryAsync(insertSql);
                    insertValues.Clear();
                }
            }
            #endregion

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

            insertValues.Clear();
            scount = scores.Count;
            for (int i = 0; i < scount; i++)
            {
                var t = scores[i];

                string ncgid = "NULL";
                if (t.NceeConvertGradeId.HasValue)
                {
                    ncgid = t.NceeConvertGradeId.Value.ToString();
                }

                insertValues.Add($"({t.Id}, {nceePlanId}, {t.NceeStudentId}, {t.CourseId}, {ncgid}, '{t.NceeConvertGradeName}', {t.Score}, {t.ScoreX})");
                if ((i + 1) % 1000 == 0 || i == scount - 1)
                {
                    string insertSql = $@"
INSERT INTO ncee_score(id, ncee_plan_id, ncee_student_id, course_id, ncee_convert_grade_id, ncee_convert_grade_name, score, score_x) VALUES
{string.Join(",", insertValues)}
";
                    await _rep.SqlNonQueryAsync(insertSql);
                    insertValues.Clear();
                }
            }
            #endregion
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            File.Delete(filePath);
        }
    }
    /// <summary>
    /// 上传未选科原始成绩（适用于高一未选科）
    /// </summary>
    /// <param name="filePath"></param>
    /// <param name="nceePlanId"></param>
    /// <returns></returns>
    /// <exception cref="Exception"></exception>
    public async Task UploadNoDirectionCourse(string filePath, int nceePlanId)
    {
        /* ----------------------------------------------------------
         * 处理步骤：
         * 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();
            var evaluator = ExcelUtil.GetEvaluator(workbook);

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

            // 读取表头
            rows.MoveNext();
            IRow headerRow = (IRow)rows.Current;
            int index = 0;

            int ORG_ID = index++;
            int ORG_NAME = index++;
            int NAME_INDEX = index++;
            int EXAM_NUMBER_INDEX = index++;
            int CLASS_INDEX = index++;

            int COURSE_START_INDEX = index;
            Dictionary<int, string> headers = new()
            {
                { ORG_ID, "机构ID" },
                { ORG_NAME, "机构" },
                { NAME_INDEX, "姓名" },
                { EXAM_NUMBER_INDEX, "考号" },
                { CLASS_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;
                throw new Exception("列头错误");
            }
            //result.StructureCorrect = true;
            #endregion

            #region 2.读取数据
            var plan = await _rep.Change<NceePlan>().DetachedEntities.FirstOrDefaultAsync(t => t.Id == nceePlanId) ?? throw Oops.Oh(ErrorCode.E2001, "计划");
            var courseDict = (await _rep.Change<Course>().DetachedEntities.ToListAsync()).ToDictionary(t => t.Name);

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

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


                NceeStudentImportDto stu = new();
                // 机构ID
                if (short.TryParse(row.GetCell(ORG_ID)?.ToString(), out short orgId))
                {
                    stu.SysOrgId = orgId;
                }
                else
                {
                    throw new Exception($"[{rn}]机构ID错误");
                }
                // 姓名
                stu.Name = row.GetCell(NAME_INDEX)?.ToString();
                // 考号
                stu.ExamNumber = row.GetCell(EXAM_NUMBER_INDEX)?.ToString();
                // 班号
                if (short.TryParse(row.GetCell(CLASS_INDEX)?.ToString(), out short cn))
                {
                    stu.ClassNumber = cn;
                }
                else
                {
                    throw new Exception($"[{rn}]班号错误");
                }

                // 取各科成绩
                List<NceeScoreImportDto> courseScores = [];
                for (int gi = COURSE_START_INDEX; gi < validCellNum; gi++)
                {
                    var cell = row.GetCell(gi);
                    if (cell == null || cell.ToString().Trim() == "")
                    {
                        continue;
                    }

                    if (!courses.ContainsKey(gi)) { continue; }
                    var course = courses[gi];
                    NceeScoreImportDto item = new()
                    {
                        NceeStudentId = stu.Id,
                        CourseId = course.Id,
                    };

                    decimal score = 0;
                    if (cell.CellType == CellType.Numeric)
                    {
                        score = (decimal)cell.NumericCellValue;
                    }
                    else if (cell.CellType == CellType.Formula)
                    {
                        var cv = evaluator.Evaluate(cell);
                        if (cv.CellType == CellType.Numeric)
                        {
                            score = (decimal)cv.NumberValue;
                        }
                        else
                        {
                            continue;
                        }
                    }

                    if (score > 0)
                    {
                        item.Score = score;
                        item.ScoreX = score;
                        courseScores.Add(item);
                    }
                }

                if (courseScores.Count > 0)
                {
                    stu.Score = courseScores.Sum(t => t.Score);
                    stu.ScoreX = courseScores.Sum(t => t.ScoreX);
                    students.Add(stu);
                    scores.AddRange(courseScores);
                }
            }
            workbook.Close();
            fs.Close();
            #endregion

            #region 3.处理学生
            // 清理学生
            string deleteStudentSql = $"DELETE FROM ncee_student WHERE ncee_plan_id = {nceePlanId};";
            await _rep.SqlNonQueryAsync(deleteStudentSql);

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

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

                insertValues.Add($"({t.Id}, {nceePlanId}, {t.SysOrgId}, {plan.GradeId}, {t.ClassNumber}, '{t.ExamNumber}', '{t.Name}', 99, 0, {t.Score}, {t.ScoreX})");
                if ((i + 1) % 1000 == 0 || i == scount - 1)
                {
                    string insertSql = $@"
INSERT INTO ncee_student(id, ncee_plan_id, sys_org_id, grade_id, class_number, exam_number, `name`, direction_course_id, ncee_course_comb_id, score, score_x) VALUES 
{string.Join(",", insertValues)}
";
                    await _rep.SqlNonQueryAsync(insertSql);
                    insertValues.Clear();
                }
            }
            #endregion

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

            insertValues.Clear();
            scount = scores.Count;
            for (int i = 0; i < scount; i++)
            {
                var t = scores[i];

                insertValues.Add($"({t.Id}, {nceePlanId}, {t.NceeStudentId}, {t.CourseId}, NULL, '', {t.Score}, {t.ScoreX})");
                if ((i + 1) % 1000 == 0 || i == scount - 1)
                {
                    string insertSql = $@"
INSERT INTO ncee_score(id, ncee_plan_id, ncee_student_id, course_id, ncee_convert_grade_id, ncee_convert_grade_name, score, score_x) VALUES
{string.Join(",", insertValues)}
";
                    await _rep.SqlNonQueryAsync(insertSql);
                    insertValues.Clear();
                }
            }
            #endregion
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            File.Delete(filePath);
        }
    }
    /// <summary>
    /// 执行模拟划线
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <returns></returns>
    public async Task Execute(int nceePlanId)
    {
        var nceePlan = await _rep.Change<NceePlan>().FirstOrDefaultAsync(t => t.Id == nceePlanId) ?? throw Oops.Oh(ErrorCode.E2001);
        var nceePlanConfig = JSON.Deserialize<NceePlanConfig>(nceePlan.Config);

        // 执行分数转换
        if (nceePlanConfig.ConvertEnabled)
        {
            await ExecuteScoreConvert(nceePlanId);
        }
        // 更新学生排名
        await ExecuteUpdateOrder(nceePlanId);
        // 执行划线
        await ExecuteLine(nceePlanId, nceePlanConfig);
    }

    #region 私有方法
    /// <summary>
    /// 执行划线
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="config"></param>
    /// <returns></returns>
    private async Task ExecuteLine(int nceePlanId, NceePlanConfig config)
    {
        var baseLines = await _rep.Change<NceeBaseLine>().Where(t => t.NceePlanId == nceePlanId).OrderBy(t => t.NceeLineLevel).ToListAsync();
        // 单科有效分
        List<NceeCourseLineScore> courseLineScores = [];


        #region 计算总有效分（通过比例计算）
        if (config.CalcTotalLineScoreEnabled)
        {
            foreach (var line in baseLines)
            {
                // 计算总有效分
                var lineScoreX = await _rep.SqlScalarAsync<decimal>(@$"
SELECT MIN(score_x) AS score_x
FROM
(
    SELECT ROW_NUMBER() OVER(ORDER BY score_x DESC) AS rn, score_x
    FROM ncee_student
    WHERE ncee_plan_id = @nceePlanId AND direction_course_id = @directionCourseId
) AS T1
WHERE T1.rn <= (SELECT COUNT(1) FROM ncee_student WHERE ncee_plan_id = @nceePlanId AND direction_course_id = @directionCourseId) * @lineRate
", new { NceePlanId = nceePlanId, line.DirectionCourseId, line.LineRate });
                line.LineScoreX = lineScoreX;
            }
            await _rep.Change<NceeBaseLine>().UpdateNowAsync(baseLines);
        }
        #endregion


        #region 计算单科有效分
        if (config.CalcCourseLineScoreEnabled)
        {
            // 1.总分排序，合并单科独立排序
            // 2.取总分上线分对应行的单科成绩为有效分

            foreach (var line in baseLines)
            {
                // 计算单科有效分
                var courseLineScoreX = await _rep.SqlQueryAsync<NceeCourseLineScoreCalcDto>($@"
SELECT 
    MIN(T1.yuwen_score_x) AS yuwen_score_x,             -- 语文
    MIN(T1.shuxue_score_x) AS shuxue_score_x,           -- 数学
    MIN(T1.yingyu_score_x) AS yingyu_score_x,           -- 英语
    MIN(T1.fangxiang_score_x) AS fangxiang_score_x,     -- 方向（物理或历史）
    MIN(T1.zonghe_score_x) AS zonghe_score_x            -- 综合
FROM
(
    SELECT T1.rn, 
        T2.score_x AS yuwen_score_x, 
        T3.score_x AS shuxue_score_x, 
        T4.score_x AS yingyu_score_x, 
        T5.score_x AS fangxiang_score_x, 
        T6.score_x AS zonghe_score_x,
        IFNULL(T2.score_x, 0) + IFNULL(T3.score_x, 0) + IFNULL(T4.score_x, 0) + IFNULL(T5.score_x, 0) + IFNULL(T6.score_x, 0) AS score_x
    FROM
    (
        SELECT ROW_NUMBER() OVER(ORDER BY score_x DESC) AS rn
        FROM ncee_student AS T1
        WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId
    ) AS T1
    LEFT JOIN
    (
        SELECT ROW_NUMBER() OVER(ORDER BY T2.score_x DESC) AS rn, T2.score_x
        FROM ncee_student AS T1
        JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
        WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.course_id = 1
    ) AS T2 ON T1.rn = T2.rn
    LEFT JOIN
    (
        SELECT ROW_NUMBER() OVER(ORDER BY T2.score_x DESC) AS rn, T2.score_x
        FROM ncee_student AS T1
        JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
        WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.course_id = 2
    ) AS T3 ON T1.rn = T3.rn
    LEFT JOIN
    (
        SELECT ROW_NUMBER() OVER(ORDER BY T2.score_x DESC) AS rn, T2.score_x
        FROM ncee_student AS T1
        JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
        WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.course_id = 3
    ) AS T4 ON T1.rn = T4.rn
    LEFT JOIN
    (
        SELECT ROW_NUMBER() OVER(ORDER BY T2.score_x DESC) AS rn, T2.score_x
        FROM ncee_student AS T1
        JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
        WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.course_id = @directionCourseId
    ) AS T5 ON T1.rn = T5.rn
    LEFT JOIN
    (
        SELECT ROW_NUMBER() OVER(ORDER BY T1.score_x DESC) AS rn, t1.score_x
        FROM
        (
            SELECT T2.ncee_student_id, SUM(T2.score_x) AS score_x
            FROM ncee_student AS T1
            JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
            WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.course_id IN(5, 6, 7, 9)
            GROUP BY T2.ncee_student_id
        ) AS T1
    ) AS T6 ON T1.rn = T6.rn
) AS T1
WHERE T1.score_x >= @totalLineScoreX
", new { NceePlanId = nceePlanId, line.DirectionCourseId, TotalLineScoreX = line.LineScoreX });

                var cline = courseLineScoreX.FirstOrDefault();
                if (cline != null)
                {
                    courseLineScores.Add(new() { NceePlanId = nceePlanId, DirectionCourseId = line.DirectionCourseId, NceeLineLevel = line.NceeLineLevel, CourseId = 1, LineScoreX = cline.YuwenScoreX });
                    courseLineScores.Add(new() { NceePlanId = nceePlanId, DirectionCourseId = line.DirectionCourseId, NceeLineLevel = line.NceeLineLevel, CourseId = 2, LineScoreX = cline.ShuxueScoreX });
                    courseLineScores.Add(new() { NceePlanId = nceePlanId, DirectionCourseId = line.DirectionCourseId, NceeLineLevel = line.NceeLineLevel, CourseId = 3, LineScoreX = cline.YingyuScoreX });
                    courseLineScores.Add(new() { NceePlanId = nceePlanId, DirectionCourseId = line.DirectionCourseId, NceeLineLevel = line.NceeLineLevel, CourseId = line.DirectionCourseId, LineScoreX = cline.FangxiangScoreX });
                    courseLineScores.Add(new() { NceePlanId = nceePlanId, DirectionCourseId = line.DirectionCourseId, NceeLineLevel = line.NceeLineLevel, CourseId = 101, LineScoreX = cline.ZongheScoreX });
                }
            }
            await _rep.Change<NceeCourseLineScore>().Where(t => t.NceePlanId == nceePlanId).ExecuteDeleteAsync();
            List<NceeCourseLineScore> totalLines = [];
            foreach (var line in baseLines)
            {
                totalLines.Add(new() { NceePlanId = nceePlanId, DirectionCourseId = line.DirectionCourseId, NceeLineLevel = line.NceeLineLevel, CourseId = 100, LineScoreX = line.LineScoreX });
            }
            await _rep.Change<NceeCourseLineScore>().InsertNowAsync(totalLines);
            await _rep.Change<NceeCourseLineScore>().InsertNowAsync(courseLineScores);
        }
        else
        {
            courseLineScores = await _rep.Change<NceeCourseLineScore>().Where(t => t.NceePlanId == nceePlanId).ToListAsync();
        }
        #endregion


        #region 计算总分上线
        // 清除数据
        await _rep.Change<NceeLineTotal>().Where(t => t.NceePlanId == nceePlanId).ExecuteDeleteAsync();
        // 总分划线
        foreach (var line in baseLines)
        {
            // 整体 - 方向
            await _rep.SqlNonQueryAsync($@"
INSERT INTO ncee_line_total(type, ncee_plan_id, ncee_line_level, direction_course_id, grade_id, line_count, total_count, line_rate)
SELECT  {(short)NceeDataScopeType.TOTAL}, @nceePlanId, @nceeLineLevel, T1.*, T1.line_count / T1.total_count AS line_rate
FROM
(
    SELECT 
        T1.direction_course_id, 
        T1.grade_id,
        COUNT(1) AS line_count,
        (
            SELECT COUNT(1) AS total_count
            FROM ncee_student 
            WHERE ncee_plan_id = @nceePlanId AND direction_course_id = @directionCourseId AND score_x > 0
        ) AS total_count
    FROM ncee_student AS T1
    WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T1.score_x >= @lineScoreX
    GROUP BY T1.direction_course_id, T1.grade_id
) AS T1
", new { NceePlanId = nceePlanId, line.NceeLineLevel, line.DirectionCourseId, line.LineScoreX });

            // 机构 - 方向
            await _rep.SqlNonQueryAsync($@"
INSERT INTO ncee_line_total(type, ncee_plan_id, ncee_line_level, direction_course_id, sys_org_id, grade_id, line_count, total_count, line_rate)
SELECT {(short)NceeDataScopeType.ORG}, @nceePlanId, @nceeLineLevel, T1.*, T1.line_count / T1.total_count AS line_rate
FROM
(
    SELECT 
        T1.direction_course_id, 
        T1.sys_org_id, 
        T1.grade_id,
        COUNT(1) AS line_count,
        T2.total_count
    FROM ncee_student AS T1
    JOIN 
    (
        SELECT sys_org_id, COUNT(1) AS total_count
        FROM ncee_student 
        WHERE ncee_plan_id = @nceePlanId AND direction_course_id = @directionCourseId AND score_x > 0
        GROUP BY sys_org_id
    ) AS T2 ON T1.sys_org_id = T2.sys_org_id
    WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T1.score_x >= @lineScoreX
    GROUP BY T1.direction_course_id, T1.sys_org_id, T1.grade_id
) AS T1
", new { NceePlanId = nceePlanId, line.NceeLineLevel, line.DirectionCourseId, line.LineScoreX });

            // 机构 - 方向 - 组合
            await _rep.SqlNonQueryAsync($@"
INSERT INTO ncee_line_total(type, ncee_plan_id, ncee_line_level, direction_course_id, ncee_course_comb_id, sys_org_id, grade_id, line_count, total_count, line_rate)
SELECT {(short)NceeDataScopeType.COMB}, @nceePlanId, @nceeLineLevel, T1.*, T1.line_count / T1.total_count AS line_rate
FROM
(
    SELECT 
        T1.direction_course_id,
        T1.ncee_course_comb_id,
        T1.sys_org_id, 
        T1.grade_id,
        COUNT(1) AS line_count,
        T2.total_count
    FROM ncee_student AS T1
    JOIN 
    (
        SELECT ncee_course_comb_id, sys_org_id, COUNT(1) AS total_count
        FROM ncee_student 
        WHERE ncee_plan_id = @nceePlanId AND direction_course_id = @directionCourseId AND score_x > 0
        GROUP BY ncee_course_comb_id, sys_org_id
    ) AS T2 ON T1.sys_org_id = T2.sys_org_id
    WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T1.ncee_course_comb_id = T2.ncee_course_comb_id AND T1.score_x >= @lineScoreX
    GROUP BY T1.direction_course_id, T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id
) AS T1
", new { NceePlanId = nceePlanId, line.NceeLineLevel, line.DirectionCourseId, line.LineScoreX });

            // 机构 - 班级 - 方向 - 组合
            await _rep.SqlNonQueryAsync($@"
INSERT INTO ncee_line_total(type, ncee_plan_id, ncee_line_level, direction_course_id, ncee_course_comb_id, sys_org_id, grade_id, class_number, line_count, total_count, line_rate)
SELECT {(short)NceeDataScopeType.CLASS}, @nceePlanId, @nceeLineLevel, T1.*, T1.line_count / T1.total_count AS line_rate
FROM
(
    SELECT 
        T1.direction_course_id,
        T1.ncee_course_comb_id,
        T1.sys_org_id, 
        T1.grade_id,
        T1.class_number,
        COUNT(1) AS line_count,
        T2.total_count
    FROM ncee_student AS T1
    JOIN 
    (
        SELECT ncee_course_comb_id, sys_org_id, class_number, COUNT(1) AS total_count
        FROM ncee_student 
        WHERE ncee_plan_id = @nceePlanId AND direction_course_id = @directionCourseId AND score_x > 0
        GROUP BY ncee_course_comb_id, sys_org_id, class_number
    ) AS T2 ON T1.sys_org_id = T2.sys_org_id
    WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T1.ncee_course_comb_id = T2.ncee_course_comb_id AND T1.class_number = T2.class_number AND T1.score_x >= @lineScoreX
    GROUP BY T1.direction_course_id, T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id, T1.class_number
) AS T1
", new { NceePlanId = nceePlanId, line.NceeLineLevel, line.DirectionCourseId, line.LineScoreX });

        }
        #endregion


        #region 计算科目有效分上线
        // 清除数据
        await _rep.Change<NceeLineCourse>().Where(t => t.NceePlanId == nceePlanId).ExecuteDeleteAsync();
        // 总分划线
        foreach (var line in baseLines)
        {
            var courseLines = courseLineScores.Where(t => t.DirectionCourseId == line.DirectionCourseId && t.NceeLineLevel == line.NceeLineLevel).ToList();
            foreach (var courseLine in courseLines)
            {
                string inCourses = courseLine.CourseId.ToString();
                if (courseLine.CourseId == 101)
                {
                    inCourses = "5, 6, 7, 9";
                }

                // 单上线：整体 - 方向
                await _rep.SqlNonQueryAsync($@"
INSERT INTO ncee_line_course(type, ncee_plan_id, ncee_line_level, direction_course_id, course_id, grade_id, line_count, total_count, line_rate, is_double_line)
SELECT {(short)NceeDataScopeType.TOTAL}, @nceePlanId, @nceeLineLevel, @directionCourseId, @courseId, T1.*, T1.line_count / T1.total_count AS line_rate, 0
FROM
(
    SELECT
        T1.grade_id, 
        COUNT(1) AS line_count,
        (
            SELECT COUNT(1) AS total_count
            FROM
            (
                SELECT DISTINCT T1.grade_id, T2.ncee_student_id
                FROM ncee_student AS T1
                LEFT JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
                WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.score > 0 AND T2.course_id IN({inCourses})
                GROUP BY T1.grade_id, T2.ncee_student_id
            ) AS T1
        ) AS total_count
    FROM
    (
        SELECT T1.grade_id, T2.ncee_student_id, SUM(T2.score_x) AS course_score_x
        FROM ncee_student AS T1
        LEFT JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
        WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.score > 0 AND T2.course_id IN({inCourses})
        GROUP BY T1.grade_id, T2.ncee_student_id
    ) AS T1
    WHERE T1.course_score_x >= @courseLineScoreX
    GROUP BY T1.grade_id
) AS T1
", new { NceePlanId = nceePlanId, line.NceeLineLevel, line.DirectionCourseId, courseLine.CourseId, CourseLineScoreX = courseLine.LineScoreX });

                // 双上线：整体 - 方向
                await _rep.SqlNonQueryAsync($@"
INSERT INTO ncee_line_course(type, ncee_plan_id, ncee_line_level, direction_course_id, course_id, grade_id, line_count, total_count, line_rate, is_double_line)
SELECT {(short)NceeDataScopeType.TOTAL}, @nceePlanId, @nceeLineLevel, @directionCourseId, @courseId, T1.*, T1.line_count / T1.total_count AS line_rate, 1
FROM
(
    SELECT
        T1.grade_id, 
        COUNT(1) AS line_count,
        (
            SELECT COUNT(1) AS total_count
            FROM
            (
                SELECT DISTINCT T1.grade_id, T2.ncee_student_id
                FROM ncee_student AS T1
                LEFT JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
                WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.score > 0 AND T2.course_id IN({inCourses})
                GROUP BY T1.grade_id, T2.ncee_student_id
            ) AS T1
        ) AS total_count
    FROM
    (
        SELECT T1.grade_id, T2.ncee_student_id, MAX(T1.score_x) AS score_x, SUM(T2.score_x) AS course_score_x
        FROM ncee_student AS T1
        LEFT JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
        WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.score > 0 AND T2.course_id IN({inCourses})
        GROUP BY T1.grade_id, T2.ncee_student_id
    ) AS T1
    WHERE T1.score_x >= @lineScoreX AND T1.course_score_x >= @courseLineScoreX
    GROUP BY T1.grade_id
) AS T1
", new { NceePlanId = nceePlanId, line.NceeLineLevel, line.DirectionCourseId, line.LineScoreX, courseLine.CourseId, CourseLineScoreX = courseLine.LineScoreX });


                // 单上线：机构 - 方向
                await _rep.SqlNonQueryAsync($@"
INSERT INTO ncee_line_course(type, ncee_plan_id, ncee_line_level, direction_course_id, course_id, sys_org_id, grade_id, line_count, total_count, line_rate, is_double_line)
SELECT {(short)NceeDataScopeType.ORG}, @nceePlanId, @nceeLineLevel, @directionCourseId, @courseId, T1.*, T1.line_count / T1.total_count AS line_rate, 0
FROM
(
    SELECT
        T1.sys_org_id, 
        T1.grade_id, 
        COUNT(1) AS line_count,
        T2.total_count
    FROM
    (
        SELECT T1.sys_org_id, T1.grade_id, T2.ncee_student_id, SUM(T2.score_x) AS course_score_x
        FROM ncee_student AS T1
        LEFT JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
        WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.score > 0 AND T2.course_id IN({inCourses})
        GROUP BY T1.sys_org_id, T1.grade_id, T2.ncee_student_id
    ) AS T1
    JOIN
    (
        SELECT T1.sys_org_id, COUNT(1) AS total_count
        FROM
        (
            SELECT DISTINCT T1.sys_org_id, T1.grade_id, T2.ncee_student_id
            FROM ncee_student AS T1
            LEFT JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
            WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.score > 0 AND T2.course_id IN({inCourses})
            GROUP BY T1.sys_org_id, T1.grade_id, T2.ncee_student_id
        ) AS T1
        GROUP BY T1.sys_org_id
    ) AS T2 ON T1.sys_org_id = T2.sys_org_id
    WHERE T1.course_score_x >= @courseLineScoreX
    GROUP BY T1.sys_org_id, T1.grade_id
) AS T1
", new { NceePlanId = nceePlanId, line.NceeLineLevel, line.DirectionCourseId, courseLine.CourseId, CourseLineScoreX = courseLine.LineScoreX });

                // 双上线：机构 - 方向
                await _rep.SqlNonQueryAsync($@"
INSERT INTO ncee_line_course(type, ncee_plan_id, ncee_line_level, direction_course_id, course_id, sys_org_id, grade_id, line_count, total_count, line_rate, is_double_line)
SELECT {(short)NceeDataScopeType.ORG}, @nceePlanId, @nceeLineLevel, @directionCourseId, @courseId, T1.*, T1.line_count / T1.total_count AS line_rate, 1
FROM
(
    SELECT
        T1.sys_org_id, 
        T1.grade_id, 
        COUNT(1) AS line_count,
        T2.total_count
    FROM
    (
        SELECT T1.sys_org_id, T1.grade_id, T2.ncee_student_id, MAX(T1.score_x) AS score_x, SUM(T2.score_x) AS course_score_x
        FROM ncee_student AS T1
        LEFT JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
        WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.score > 0 AND T2.course_id IN({inCourses})
        GROUP BY T1.sys_org_id, T1.grade_id, T2.ncee_student_id
    ) AS T1
    JOIN
    (
        SELECT T1.sys_org_id, COUNT(1) AS total_count
        FROM
        (
            SELECT DISTINCT T1.sys_org_id, T1.grade_id, T2.ncee_student_id
            FROM ncee_student AS T1
            LEFT JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
            WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.score > 0 AND T2.course_id IN({inCourses})
            GROUP BY T1.sys_org_id, T1.grade_id, T2.ncee_student_id
        ) AS T1
        GROUP BY T1.sys_org_id
    ) AS T2 ON T1.sys_org_id = T2.sys_org_id
    WHERE T1.score_x >= @lineScoreX AND T1.course_score_x >= @courseLineScoreX
    GROUP BY T1.sys_org_id, T1.grade_id
) AS T1
", new { NceePlanId = nceePlanId, line.NceeLineLevel, line.DirectionCourseId, line.LineScoreX, courseLine.CourseId, CourseLineScoreX = courseLine.LineScoreX });


                // 单上线：机构 - 方向 - 组合
                await _rep.SqlNonQueryAsync($@"
INSERT INTO ncee_line_course(type, ncee_plan_id, ncee_line_level, direction_course_id, course_id, ncee_course_comb_id, sys_org_id, grade_id, line_count, total_count, line_rate, is_double_line)
SELECT {(short)NceeDataScopeType.COMB}, @nceePlanId, @nceeLineLevel, @directionCourseId, @courseId, T1.*, T1.line_count / T1.total_count AS line_rate, 0
FROM
(
    SELECT
        T1.ncee_course_comb_id,
        T1.sys_org_id, 
        T1.grade_id, 
        COUNT(1) AS line_count,
        T2.total_count
    FROM
    (
        SELECT T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id, T2.ncee_student_id, SUM(T2.score_x) AS course_score_x
        FROM ncee_student AS T1
        LEFT JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
        WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.score > 0 AND T2.course_id IN({inCourses})
        GROUP BY T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id, T2.ncee_student_id
    ) AS T1
    JOIN
    (
        SELECT T1.ncee_course_comb_id, T1.sys_org_id, COUNT(1) AS total_count
        FROM
        (
            SELECT DISTINCT T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id, T2.ncee_student_id
            FROM ncee_student AS T1
            LEFT JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
            WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.score > 0 AND T2.course_id IN({inCourses})
            GROUP BY T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id, T2.ncee_student_id
        ) AS T1
        GROUP BY T1.ncee_course_comb_id, T1.sys_org_id
    ) AS T2 ON T1.sys_org_id = T2.sys_org_id AND T1.ncee_course_comb_id = T2.ncee_course_comb_id
    WHERE T1.course_score_x >= @courseLineScoreX
    GROUP BY T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id
) AS T1
", new { NceePlanId = nceePlanId, line.NceeLineLevel, line.DirectionCourseId, courseLine.CourseId, CourseLineScoreX = courseLine.LineScoreX });

                // 双上线：机构 - 方向 - 组合
                await _rep.SqlNonQueryAsync($@"
INSERT INTO ncee_line_course(type, ncee_plan_id, ncee_line_level, direction_course_id, course_id, ncee_course_comb_id, sys_org_id, grade_id, line_count, total_count, line_rate, is_double_line)
SELECT {(short)NceeDataScopeType.COMB}, @nceePlanId, @nceeLineLevel, @directionCourseId, @courseId, T1.*, T1.line_count / T1.total_count AS line_rate, 1
FROM
(
    SELECT
        T1.ncee_course_comb_id,
        T1.sys_org_id, 
        T1.grade_id, 
        COUNT(1) AS line_count,
        T2.total_count
    FROM
    (
        SELECT T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id, T2.ncee_student_id, MAX(T1.score_x) AS score_x, SUM(T2.score_x) AS course_score_x
        FROM ncee_student AS T1
        LEFT JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
        WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.score > 0 AND T2.course_id IN({inCourses})
        GROUP BY T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id, T2.ncee_student_id
    ) AS T1
    JOIN
    (
        SELECT T1.ncee_course_comb_id, T1.sys_org_id, COUNT(1) AS total_count
        FROM
        (
            SELECT DISTINCT T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id, T2.ncee_student_id
            FROM ncee_student AS T1
            LEFT JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
            WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.score > 0 AND T2.course_id IN({inCourses})
            GROUP BY T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id, T2.ncee_student_id
        ) AS T1
        GROUP BY T1.ncee_course_comb_id, T1.sys_org_id
    ) AS T2 ON T1.sys_org_id = T2.sys_org_id AND T1.ncee_course_comb_id = T2.ncee_course_comb_id
    WHERE T1.score_x >= @lineScoreX AND T1.course_score_x >= @courseLineScoreX
    GROUP BY T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id
) AS T1
", new { NceePlanId = nceePlanId, line.NceeLineLevel, line.DirectionCourseId, line.LineScoreX, courseLine.CourseId, CourseLineScoreX = courseLine.LineScoreX });


                // 机构 - 班级 - 方向 - 组合
                await _rep.SqlNonQueryAsync($@"
INSERT INTO ncee_line_course(type, ncee_plan_id, ncee_line_level, direction_course_id, course_id, ncee_course_comb_id, sys_org_id, grade_id, class_number, line_count, total_count, line_rate, is_double_line)
SELECT {(short)NceeDataScopeType.CLASS}, @nceePlanId, @nceeLineLevel, @directionCourseId, @courseId, T1.*, T1.line_count / T1.total_count AS line_rate, 0
FROM
(
    SELECT
        T1.ncee_course_comb_id,
        T1.sys_org_id, 
        T1.grade_id, 
        T1.class_number,
        COUNT(1) AS line_count,
        T2.total_count
    FROM
    (
        SELECT T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id, T1.class_number, T2.ncee_student_id, SUM(T2.score_x) AS course_score_x
        FROM ncee_student AS T1
        LEFT JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
        WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.score > 0 AND T2.course_id IN({inCourses})
        GROUP BY T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id, T1.class_number, T2.ncee_student_id
    ) AS T1
    JOIN
    (
        SELECT T1.ncee_course_comb_id, T1.sys_org_id, T1.class_number, COUNT(1) AS total_count
        FROM
        (
            SELECT DISTINCT T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id, T1.class_number, T2.ncee_student_id
            FROM ncee_student AS T1
            LEFT JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
            WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.score > 0 AND T2.course_id IN({inCourses})
            GROUP BY T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id, T1.class_number, T2.ncee_student_id
        ) AS T1
        GROUP BY T1.ncee_course_comb_id, T1.sys_org_id, T1.class_number
    ) AS T2 ON T1.sys_org_id = T2.sys_org_id AND T1.ncee_course_comb_id = T2.ncee_course_comb_id AND T1.class_number = T2.class_number
    WHERE T1.course_score_x >= @courseLineScoreX
    GROUP BY T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id, T1.class_number
) AS T1
", new { NceePlanId = nceePlanId, line.NceeLineLevel, line.DirectionCourseId, courseLine.CourseId, CourseLineScoreX = courseLine.LineScoreX });

                // 机构 - 班级 - 方向 - 组合
                await _rep.SqlNonQueryAsync($@"
INSERT INTO ncee_line_course(type, ncee_plan_id, ncee_line_level, direction_course_id, course_id, ncee_course_comb_id, sys_org_id, grade_id, class_number, line_count, total_count, line_rate, is_double_line)
SELECT {(short)NceeDataScopeType.CLASS}, @nceePlanId, @nceeLineLevel, @directionCourseId, @courseId, T1.*, T1.line_count / T1.total_count AS line_rate, 1
FROM
(
    SELECT
        T1.ncee_course_comb_id,
        T1.sys_org_id, 
        T1.grade_id, 
        T1.class_number,
        COUNT(1) AS line_count,
        T2.total_count
    FROM
    (
        SELECT T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id, T1.class_number, T2.ncee_student_id, MAX(T1.score_x) AS score_x, SUM(T2.score_x) AS course_score_x
        FROM ncee_student AS T1
        LEFT JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
        WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.score > 0 AND T2.course_id IN({inCourses})
        GROUP BY T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id, T1.class_number, T2.ncee_student_id
    ) AS T1
    JOIN
    (
        SELECT T1.ncee_course_comb_id, T1.sys_org_id, T1.class_number, COUNT(1) AS total_count
        FROM
        (
            SELECT DISTINCT T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id, T1.class_number, T2.ncee_student_id
            FROM ncee_student AS T1
            LEFT JOIN ncee_score AS T2 ON T1.id = T2.ncee_student_id
            WHERE T1.ncee_plan_id = @nceePlanId AND T1.direction_course_id = @directionCourseId AND T2.score > 0 AND T2.course_id IN({inCourses})
            GROUP BY T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id, T1.class_number, T2.ncee_student_id
        ) AS T1
        GROUP BY T1.ncee_course_comb_id, T1.sys_org_id, T1.class_number
    ) AS T2 ON T1.sys_org_id = T2.sys_org_id AND T1.ncee_course_comb_id = T2.ncee_course_comb_id AND T1.class_number = T2.class_number
    WHERE T1.score_x >= @lineScoreX AND T1.course_score_x >= @courseLineScoreX
    GROUP BY T1.ncee_course_comb_id, T1.sys_org_id, T1.grade_id, T1.class_number
) AS T1
", new { NceePlanId = nceePlanId, line.NceeLineLevel, line.DirectionCourseId, line.LineScoreX, courseLine.CourseId, CourseLineScoreX = courseLine.LineScoreX });

            }
        }
        #endregion
    }
    /// <summary>
    /// 执行分数转换
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <returns></returns>
    private async Task ExecuteScoreConvert(int nceePlanId)
    {
        await _rep.Change<NceeConvertRange>().Where(t => t.NceePlanId == nceePlanId).ExecuteDeleteAsync();

        var convertGrades = await _rep.Change<NceeConvertGrade>().DetachedEntities.OrderBy(t => t.Sequence).ToListAsync();
        var convertGradeDict = convertGrades.ToDictionary(t => t.Id, t => t);

        // 分科转换
        foreach (var courseId in _chooseCourses)
        {
            var scores = await _rep.DetachedEntities.Where(t => t.NceeStudent.NceePlanId == nceePlanId && t.CourseId == courseId && t.Score > 0).OrderByDescending(t => t.Score).ToListAsync();
            if (scores.Count == 0)
            {
                continue;
            }
            var convertRanges = GetConvertRange(nceePlanId, courseId, convertGrades, scores);

            // 更新转换区间
            await _rep.Change<NceeConvertRange>().Where(t => t.NceePlanId == nceePlanId && t.CourseId == courseId).ExecuteDeleteAsync();
            await _rep.Change<NceeConvertRange>().InsertNowAsync(convertRanges);

            // 更新转换分
            List<string> replaceValues = [];
            int cscount = scores.Count;
            for (int i = 0; i < cscount; i++)
            {
                var s = scores[i];
                var cr = convertRanges.FirstOrDefault(t => s.Score >= t.MinScore && s.Score <= t.MaxScore);
                var cg = convertGradeDict[cr.NceeConvertGradeId];
                var scoreX = GetScoreX(s.Score, cg, cr);

                replaceValues.Add($"({s.Id}, {nceePlanId}, {s.NceeStudentId}, {s.CourseId}, {s.Score}, {scoreX}, {cr.NceeConvertGradeId}, '{cg.Name}')");
                if ((i + 1) % 1000 == 0 || i == cscount - 1)
                {
                    string replaceSql = $@"
REPLACE INTO ncee_score(id, ncee_plan_id, ncee_student_id, course_id, score, score_x, ncee_convert_grade_id, ncee_convert_grade_name) VALUES
{string.Join(",", replaceValues)}
";
                    await _rep.SqlNonQueryAsync(replaceSql);
                    replaceValues.Clear();
                }
            }
        }

        // 更新总分
        await _rep.SqlNonQueryAsync($@"
-- 更新总分
UPDATE ncee_student AS T1
JOIN
(
    SELECT ncee_student_id, SUM(score) AS score, SUM(score_x) AS score_x
    FROM ncee_score 
    WHERE ncee_plan_id = @nceePlanId
    GROUP BY ncee_student_id
) AS T2 ON T1.id = T2.ncee_student_id
SET T1.score = T2.score, T1.score_x = T2.score_x
WHERE T1.ncee_plan_id = @nceePlanId
;", new { NceePlanId = nceePlanId });
    }
    /// <summary>
    /// 更新排名（在整体中的排名和在机构内的排名）
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <returns></returns>
    private async Task ExecuteUpdateOrder(int nceePlanId)
    {
        await _rep.SqlNonQueryAsync($@"
UPDATE ncee_student AS T1
JOIN
(
    SELECT id, 
        RANK() OVER(PARTITION BY direction_course_id ORDER BY score DESC) AS order_in_total,
        RANK() OVER(PARTITION BY direction_course_id ORDER BY score_x DESC) AS order_in_total_x,
        RANK() OVER(PARTITION BY direction_course_id, sys_org_id ORDER BY score DESC) AS order_in_org,
        RANK() OVER(PARTITION BY direction_course_id, sys_org_id ORDER BY score_x DESC) AS order_in_org_x
    FROM ncee_student 
    WHERE ncee_plan_id = @nceePlanId
) AS T2 ON T1.id = T2.id
SET T1.order_in_total = T2.order_in_total, 
    T1.order_in_total_x = T2.order_in_total_x,
    T1.order_in_org = T2.order_in_org,
    T1.order_in_org_x = T2.order_in_org_x
;", new { NceePlanId = nceePlanId });
    }
    /// <summary>
    /// 获取转换区间
    /// </summary>
    /// <param name="nceePlanId"></param>
    /// <param name="courseId"></param>
    /// <param name="convertGrades"></param>
    /// <param name="nceeScores"></param>
    /// <returns></returns>
    private static List<NceeConvertRange> GetConvertRange(int nceePlanId, short courseId, List<NceeConvertGrade> convertGrades, List<NceeScore> nceeScores)
    {
        int scount = nceeScores.Count;
        var totalMaxScore = nceeScores.Select(t => t.Score).Max();
        var totalMinScore = nceeScores.Select(t => t.Score).Min();

        var cgs = convertGrades.OrderBy(t => t.Id).ToList();
        var scores = nceeScores.OrderByDescending(t => t.Score).ToList();

        List<NceeConvertRange> ncrs = [];
        decimal lastMinScore = decimal.MaxValue;
        foreach (var cg in cgs)
        {
            NceeConvertRange range = new()
            {
                NceePlanId = nceePlanId,
                NceeConvertGradeId = cg.Id,
                CourseId = courseId,
            };

            var i = (int)Math.Min(Math.Floor(cg.EndRate * scount), scount - 1);
            if (cg.BeginRate == 0)
            {
                range.MaxScore = totalMaxScore;
            }
            else
            {
                range.MaxScore = scores.Where(t => t.Score < lastMinScore).Select(t => t.Score).Max();
            }

            if (cg.EndRate == 1)
            {
                range.MinScore = totalMinScore;
            }
            else
            {
                range.MinScore = scores[i].Score;
            }

            lastMinScore = range.MinScore;
            ncrs.Add(range);
        }

        return ncrs;
    }
    /// <summary>
    /// 获取转换分
    /// </summary>
    /// <param name="score"></param>
    /// <param name="convertGrade"></param>
    /// <param name="convertRange"></param>
    /// <returns></returns>
    private static decimal GetScoreX(decimal score, NceeConvertGrade convertGrade, NceeConvertRange convertRange)
    {
        // (Y2 - Y) / (Y- Y1) = (X2 - X) / (X - X1)
        // Y1,Y2为原始分区间下限和上限
        // X1,X2为转换分区间下限和上限
        // Y为原始分
        // X为转换分


        // a = Y2 - Y
        var a = (convertRange.MaxScore - score);
        // b = Y - Y1
        var b = (score - convertRange.MinScore);
        return Math.Round((b * convertGrade.MaxScore + a * convertGrade.MinScore) / (a + b));
    }


    #endregion
}