YBEE.EQM/YBEE.EQM.Application/Esa/Services/EsaProcessingService.cs

using YBEE.EQM.Core;

namespace YBEE.EQM.Application;

/// <summary>
/// 有效分分析处理服务
/// </summary>
public class EsaProcessingService(IRepository<EsaPlan> rep) : IEsaProcessingService, ITransient
{
    /// <summary>
    /// 分析处理
    /// </summary>
    /// <param name="esaPlanId"></param>
    /// <returns></returns>
    public async Task Execute(int esaPlanId)
    {
        var plan = await rep.DetachedEntities
                            .Include(t => t.EsaBaseLines).ThenInclude(t => t.EsaBaseLineCourses)
                            .FirstOrDefaultAsync(t => t.Id == esaPlanId) 
                            ?? throw Oops.Oh(ErrorCode.E2001);
        // 更新状态
        await rep.SqlNonQueryAsync($"UPDATE esa_plan SET status = {(short)ProcessingStatus.RUNNING} WHERE id = {esaPlanId}");
        try
        {
            foreach (var esaBaseLine in plan.EsaBaseLines)
            {
                try
                {
                    // 更新状态
                    await rep.SqlNonQueryAsync($"UPDATE esa_base_line SET status = {(short)ProcessingStatus.RUNNING} WHERE id = {esaBaseLine.Id}");

                    // 构造分析学科
                    var courseIds = string.Join(", ", esaBaseLine.EsaBaseLineCourses.OrderBy(t => t.CourseId).Select(t => t.CourseId));
                    // 构造取数条件
                    var whereBaseSql = $"WHERE T1.exam_plan_id = {plan.ExamPlanId} AND exam_sample_type = 1 AND T1.grade_id = {esaBaseLine.GradeId} AND T1.score > 0";
                    var whereSql = $"{whereBaseSql} AND T1.course_id IN ({courseIds})";

                    // 删除数据
                    var deleteWhereSql = $"WHERE esa_plan_id = {esaPlanId} AND esa_base_line_id = {esaBaseLine.Id} AND grade_id = {esaBaseLine.GradeId}";
                    await rep.SqlNonQueryAsync(@$"
DELETE FROM esa_line_total {deleteWhereSql};
DELETE FROM esa_line_course {deleteWhereSql};
DELETE FROM esa_line_course_score {deleteWhereSql};
");

                    #region 区级分析
                    #region 总分上线
                    // 总分上线分
                    var totalLineScore = await rep.SqlScalarAsync<decimal>(@$"
SELECT MIN(T1.total_score) AS line_score
FROM
(
    SELECT total_score
    FROM
    (
        SELECT T1.exam_student_id, SUM(T1.score) AS total_score
        FROM exam_score AS T1
        {whereSql}
        GROUP BY T1.exam_student_id
    ) AS T1
    ORDER BY T1.total_score DESC
    LIMIT {esaBaseLine.LineCount}
) AS T1;
");

                    // 总分上线均分
                    var totalLineAvgScore = Math.Round(await rep.SqlScalarAsync<decimal>(@$"
SELECT AVG(T1.total_score) AS line_avg_score
FROM
(
    SELECT T1.exam_student_id, SUM(T1.score) AS total_score
    FROM exam_score AS T1
    {whereSql}
    GROUP BY T1.exam_student_id
) AS T1
WHERE T1.total_score >= {totalLineScore};
"), 2);

                    // 更新有效系数
                    var factor = Math.Round(totalLineScore / totalLineAvgScore, 8);
                    await rep.SqlNonQueryAsync($"UPDATE esa_base_line SET line_score = {totalLineScore}, factor = {factor} WHERE id = {esaBaseLine.Id}");

                    // 实际上线人数
                    var factLineCount = await rep.SqlScalarAsync($@"
SELECT COUNT(1) AS line_count
FROM
(
    SELECT T1.exam_student_id, SUM(T1.score) AS total_score
    FROM exam_score AS T1
    {whereSql}
    GROUP BY T1.exam_student_id
) AS T1
WHERE T1.total_score >= {totalLineScore};
");

                    // 总均分
                    var totalAvgScore = Math.Round(await rep.SqlScalarAsync<decimal>(@$"
SELECT AVG(total_score) AS avg_score
FROM
(
    SELECT T1.exam_student_id, SUM(T1.score) AS total_score
    FROM exam_score AS T1
    {whereSql}
    GROUP BY T1.exam_student_id
) AS T1;
"), 2);

                    // 学科有效分
                    var totalRelativeDiff = Math.Round((totalLineScore - totalAvgScore) / totalLineScore, 8);
                    await rep.SqlNonQueryAsync(@$"
-- 总分
INSERT INTO esa_line_course_score(esa_level, esa_line_level, esa_plan_id, esa_base_line_id, grade_id, course_id, avg_score, line_avg_score, line_score, relative_diff)
VALUES({(short)EsaLevel.DISTRICT}, {(short)esaBaseLine.EsaLineLevel}, {esaPlanId}, {esaBaseLine.Id}, {esaBaseLine.GradeId}, {(short)MultCourse.TOTAL}, {totalAvgScore}, {totalLineAvgScore}, {totalLineScore}, {totalRelativeDiff});

-- 单科
INSERT INTO esa_line_course_score(esa_level, esa_line_level, esa_plan_id, esa_base_line_id, grade_id, course_id, avg_score, line_avg_score, line_score, relative_diff)
SELECT {(short)EsaLevel.DISTRICT}, {(short)esaBaseLine.EsaLineLevel}, {esaPlanId}, {esaBaseLine.Id}, {esaBaseLine.GradeId}, T1.course_id, T2.avg_course_score, T1.avg_line_course_score, T1.course_line_score, ROUND((T1.course_line_score - T2.avg_course_score) / T1.course_line_score, 8) AS relative_diff
FROM
(
    SELECT T1.course_id, ROUND(AVG(T1.score), 2) AS avg_line_course_score, ROUND(ROUND(AVG(T1.score), 2) * {factor}, 2) AS course_line_score
    FROM
    (
        SELECT T1.exam_student_id, T1.course_id, T1.score
        FROM exam_score AS T1
        {whereSql}
    ) AS T1
    JOIN
    (
        SELECT T1.exam_student_id
        FROM
        (
            SELECT T1.exam_student_id, SUM(T1.score) AS total_score
            FROM exam_score AS T1
            {whereSql}
            GROUP BY T1.exam_student_id
        ) AS T1
        WHERE T1.total_score >= {totalLineScore}
    ) AS T2 ON T1.exam_student_id = T2.exam_student_id
    GROUP BY T1.course_id
) AS T1
JOIN
(
    SELECT T1.course_id, ROUND(AVG(T1.score), 2) AS avg_course_score
    FROM exam_score AS T1
    {whereSql}
    GROUP BY T1.course_id
) AS T2 ON T1.course_id = T2.course_id;
");

                    // 全区
                    await rep.SqlNonQueryAsync(@$"
INSERT INTO esa_line_total(esa_level, esa_data_scope_type, esa_line_level, esa_plan_id, esa_base_line_id, grade_id, line_count, total_count, line_rate, factor)
SELECT {(short)EsaLevel.DISTRICT}, {(short)EsaDataScopeType.TOTAL}, {(short)esaBaseLine.EsaLineLevel}, {esaPlanId}, {esaBaseLine.Id}, {esaBaseLine.GradeId}, COUNT(1) AS line_count, T2.total_count, COUNT(1) / T2.total_count, 0
FROM
(
    SELECT T1.exam_student_id, SUM(T1.score) AS total_score
    FROM exam_score AS T1
    {whereSql}
    GROUP BY T1.exam_student_id
) AS T1
JOIN 
(
    SELECT COUNT(1) as total_count
    FROM
    (
        SELECT T1.exam_student_id, SUM(T1.score) AS total_score
        FROM exam_score AS T1
        {whereSql}
        GROUP BY T1.exam_student_id
    ) AS T1
) AS T2 ON 1 = 1
WHERE T1.total_score >= {totalLineScore};
");

                    // 学校
                    await rep.SqlNonQueryAsync(@$"
INSERT INTO esa_line_total(esa_level, esa_data_scope_type, esa_line_level, esa_plan_id, esa_base_line_id, grade_id, sys_org_id, line_count, total_count, line_rate, factor)
SELECT {(short)EsaLevel.DISTRICT}, {(short)EsaDataScopeType.ORG}, {(short)esaBaseLine.EsaLineLevel}, {esaPlanId}, {esaBaseLine.Id}, {esaBaseLine.GradeId}, T1.sys_org_id, COUNT(1) AS line_count, T2.total_count, COUNT(1) / T2.total_count, 0
FROM
(
    SELECT T1.sys_org_id, T1.exam_student_id, SUM(T1.score) AS total_score
    FROM exam_score AS T1
    {whereSql}
    GROUP BY T1.sys_org_id, T1.exam_student_id
) AS T1
JOIN 
(
    SELECT T1.sys_org_id, COUNT(1) as total_count
    FROM
    (
        SELECT T1.sys_org_id, T1.exam_student_id, SUM(T1.score) AS total_score
        FROM exam_score AS T1
        {whereSql}
        GROUP BY T1.sys_org_id, T1.exam_student_id
    ) AS T1
    GROUP BY T1.sys_org_id
) AS T2 ON T1.sys_org_id = T2.sys_org_id
WHERE T1.total_score >= {totalLineScore}
GROUP BY T1.sys_org_id;
");

                    // 班级
                    await rep.SqlNonQueryAsync(@$"
INSERT INTO esa_line_total(esa_level, esa_data_scope_type, esa_line_level, esa_plan_id, esa_base_line_id, grade_id, sys_org_id, school_class_id, class_number, line_count, total_count, line_rate, factor)
SELECT {(short)EsaLevel.DISTRICT}, {(short)EsaDataScopeType.CLASS}, {(short)esaBaseLine.EsaLineLevel}, {esaPlanId}, {esaBaseLine.Id}, {esaBaseLine.GradeId}, T1.sys_org_id, T1.school_class_id, T1.class_number, COUNT(1) AS line_count, T2.total_count, COUNT(1) / T2.total_count, 0
FROM
(
    SELECT T1.sys_org_id, T1.school_class_id, T1.class_number, T1.exam_student_id, SUM(T1.score) AS total_score
    FROM exam_score AS T1
    {whereSql}
    GROUP BY T1.sys_org_id, T1.school_class_id, T1.class_number, T1.exam_student_id
) AS T1
JOIN 
(
    SELECT T1.sys_org_id, T1.school_class_id, T1.class_number, COUNT(1) as total_count
    FROM
    (
        SELECT T1.sys_org_id, T1.school_class_id, T1.class_number, T1.exam_student_id, SUM(T1.score) AS total_score
        FROM exam_score AS T1
        {whereSql}
        GROUP BY T1.sys_org_id, T1.school_class_id, T1.class_number, T1.exam_student_id
    ) AS T1
    GROUP BY T1.sys_org_id, T1.school_class_id, T1.class_number
) AS T2 ON T1.sys_org_id = T2.sys_org_id AND T1.school_class_id = T2.school_class_id AND T1.class_number = T2.class_number
WHERE T1.total_score >= {totalLineScore}
GROUP BY T1.sys_org_id, T1.school_class_id, T1.class_number;
");
                    #endregion

                    #region 学科上线
                    var courseLines = await rep.Change<EsaLineCourseScore>().DetachedEntities
                                                                            .Where(t => t.EsaPlanId == esaPlanId &&
                                                                                        t.EsaBaseLineId == esaBaseLine.Id &&
                                                                                        t.EsaLevel == EsaLevel.DISTRICT &&
                                                                                        t.EsaLineLevel == esaBaseLine.EsaLineLevel &&
                                                                                        t.GradeId == esaBaseLine.GradeId &&
                                                                                        t.CourseId != (short)MultCourse.TOTAL
                                                                             ).ToListAsync();
                    foreach (var courseLine in courseLines)
                    {
                        // 单科条件
                        var courseWhereSql = $"{whereBaseSql} AND T1.course_id = {courseLine.CourseId}";

                        // 全区 - 单上线
                        await rep.SqlNonQueryAsync(@$"
INSERT INTO esa_line_course(esa_level, esa_data_scope_type, esa_line_level, esa_plan_id, esa_base_line_id, course_id, grade_id, line_count, total_count, line_rate, is_double_line)
SELECT {(short)EsaLevel.DISTRICT}, {(short)EsaDataScopeType.TOTAL}, {(short)esaBaseLine.EsaLineLevel}, {esaPlanId}, {esaBaseLine.Id}, {courseLine.CourseId}, {esaBaseLine.GradeId}, T1.line_count, T2.total_count, T1.line_count / T2.total_count, 0
FROM
(
    SELECT COUNT(1) AS line_count
    FROM exam_score AS T1
    {courseWhereSql} AND T1.score >= {courseLine.LineScore}
) AS T1
JOIN 
(
    SELECT COUNT(1) as total_count
    FROM exam_score AS T1
    {courseWhereSql}
) AS T2 ON 1 = 1;
");

                        // 全区 - 双上线
                        await rep.SqlNonQueryAsync(@$"
INSERT INTO esa_line_course(esa_level, esa_data_scope_type, esa_line_level, esa_plan_id, esa_base_line_id, course_id, grade_id, line_count, total_count, line_rate, is_double_line)
SELECT {(short)EsaLevel.DISTRICT}, {(short)EsaDataScopeType.TOTAL}, {(short)esaBaseLine.EsaLineLevel}, {esaPlanId}, {esaBaseLine.Id}, {courseLine.CourseId}, {esaBaseLine.GradeId}, T1.line_count, T2.total_count, T1.line_count / T2.total_count, 1
FROM
(
    SELECT COUNT(1) AS line_count
    FROM
    (
        SELECT T1.exam_student_id, T1.score
        FROM exam_score AS T1
        {courseWhereSql}
    ) AS T1
    JOIN
    (
        SELECT T1.exam_student_id, SUM(T1.score) AS total_score
        FROM exam_score AS T1
        {whereSql}
        GROUP BY T1.exam_student_id
    ) AS T2 ON T1.exam_student_id = T2.exam_student_id
    WHERE T1.score >= {courseLine.LineScore} AND T2.total_score >= {totalLineScore}
) AS T1
JOIN 
(
    SELECT COUNT(1) as total_count
    FROM exam_score AS T1
    {courseWhereSql}
) AS T2 ON 1 = 1;
");

                        // 学校 - 单上线
                        await rep.SqlNonQueryAsync(@$"
INSERT INTO esa_line_course(esa_level, esa_data_scope_type, esa_line_level, esa_plan_id, esa_base_line_id, course_id, grade_id, sys_org_id, line_count, total_count, line_rate, is_double_line)
SELECT {(short)EsaLevel.DISTRICT}, {(short)EsaDataScopeType.ORG}, {(short)esaBaseLine.EsaLineLevel}, {esaPlanId}, {esaBaseLine.Id}, {courseLine.CourseId}, {esaBaseLine.GradeId}, T1.sys_org_id, T1.line_count, T2.total_count, T1.line_count / T2.total_count, 0
FROM
(
    SELECT T1.sys_org_id, COUNT(1) AS line_count
    FROM exam_score AS T1
    {courseWhereSql} AND T1.score >= {courseLine.LineScore}
    GROUP BY T1.sys_org_id
) AS T1
JOIN 
(
    SELECT T1.sys_org_id, COUNT(1) as total_count
    FROM exam_score AS T1
    {courseWhereSql}
    GROUP BY T1.sys_org_id
) AS T2 ON T1.sys_org_id = T2.sys_org_id;
");

                        // 学校 - 双上线
                        await rep.SqlNonQueryAsync(@$"
INSERT INTO esa_line_course(esa_level, esa_data_scope_type, esa_line_level, esa_plan_id, esa_base_line_id, course_id, grade_id, sys_org_id, line_count, total_count, line_rate, is_double_line)
SELECT {(short)EsaLevel.DISTRICT}, {(short)EsaDataScopeType.ORG}, {(short)esaBaseLine.EsaLineLevel}, {esaPlanId}, {esaBaseLine.Id}, {courseLine.CourseId}, {esaBaseLine.GradeId}, T1.sys_org_id, T1.line_count, T2.total_count, T1.line_count / T2.total_count, 1
FROM
(
    SELECT T1.sys_org_id, COUNT(1) AS line_count
    FROM
    (
        SELECT T1.sys_org_id, T1.exam_student_id, T1.score
        FROM exam_score AS T1
        {courseWhereSql}
    ) AS T1
    JOIN
    (
        SELECT T1.sys_org_id, T1.exam_student_id, SUM(T1.score) AS total_score
        FROM exam_score AS T1
        {whereSql}
        GROUP BY T1.sys_org_id, T1.exam_student_id
    ) AS T2 ON T1.sys_org_id = T2.sys_org_id AND T1.exam_student_id = T2.exam_student_id
    WHERE T1.score >= {courseLine.LineScore} AND T2.total_score >= {totalLineScore}
    GROUP BY T1.sys_org_id
) AS T1
JOIN 
(
    SELECT T1.sys_org_id, COUNT(1) as total_count
    FROM exam_score AS T1
    {courseWhereSql}
    GROUP BY T1.sys_org_id
) AS T2 ON T1.sys_org_id = T2.sys_org_id;
");

                        // 班级 - 单上线
                        await rep.SqlNonQueryAsync(@$"
INSERT INTO esa_line_course(esa_level, esa_data_scope_type, esa_line_level, esa_plan_id, esa_base_line_id, course_id, grade_id, sys_org_id, school_class_id, class_number, line_count, total_count, line_rate, is_double_line)
SELECT {(short)EsaLevel.DISTRICT}, {(short)EsaDataScopeType.CLASS}, {(short)esaBaseLine.EsaLineLevel}, {esaPlanId}, {esaBaseLine.Id}, {courseLine.CourseId}, {esaBaseLine.GradeId}, T1.sys_org_id, T1.school_class_id, T1.class_number, T1.line_count, T2.total_count, T1.line_count / T2.total_count, 0
FROM
(
    SELECT T1.sys_org_id, T1.school_class_id, T1.class_number, COUNT(1) AS line_count
    FROM exam_score AS T1
    {courseWhereSql} AND T1.score >= {courseLine.LineScore}
    GROUP BY T1.sys_org_id, T1.school_class_id, T1.class_number
) AS T1
JOIN 
(
    SELECT T1.sys_org_id, T1.school_class_id, T1.class_number, COUNT(1) as total_count
    FROM exam_score AS T1
    {courseWhereSql}
    GROUP BY T1.sys_org_id, T1.school_class_id, T1.class_number
) AS T2 ON T1.sys_org_id = T2.sys_org_id AND T1.school_class_id = T2.school_class_id AND T1.class_number = T2.class_number;
");

                        // 班级 - 双上线
                        await rep.SqlNonQueryAsync(@$"
INSERT INTO esa_line_course(esa_level, esa_data_scope_type, esa_line_level, esa_plan_id, esa_base_line_id, course_id, grade_id, sys_org_id, school_class_id, class_number, line_count, total_count, line_rate, is_double_line)
SELECT {(short)EsaLevel.DISTRICT}, {(short)EsaDataScopeType.CLASS}, {(short)esaBaseLine.EsaLineLevel}, {esaPlanId}, {esaBaseLine.Id}, {courseLine.CourseId}, {esaBaseLine.GradeId}, T1.sys_org_id, T1.school_class_id, T1.class_number, T1.line_count, T2.total_count, T1.line_count / T2.total_count, 1
FROM
(
    SELECT T1.sys_org_id, T1.school_class_id, T1.class_number, COUNT(1) AS line_count
    FROM
    (
        SELECT T1.sys_org_id, T1.school_class_id, T1.class_number, T1.exam_student_id, T1.score
        FROM exam_score AS T1
        {courseWhereSql}
    ) AS T1
    JOIN
    (
        SELECT T1.sys_org_id, T1.school_class_id, T1.class_number, T1.exam_student_id, SUM(T1.score) AS total_score
        FROM exam_score AS T1
        {whereSql}
        GROUP BY T1.sys_org_id, T1.school_class_id, T1.class_number, T1.exam_student_id
    ) AS T2 ON T1.sys_org_id = T2.sys_org_id AND T1.school_class_id = T2.school_class_id AND T1.class_number = T2.class_number AND T1.exam_student_id = T2.exam_student_id
    WHERE T1.score >= {courseLine.LineScore} AND T2.total_score >= {totalLineScore}
    GROUP BY T1.sys_org_id, T1.school_class_id, T1.class_number
) AS T1
JOIN 
(
    SELECT T1.sys_org_id, T1.school_class_id, T1.class_number, COUNT(1) as total_count
    FROM exam_score AS T1
    {courseWhereSql}
    GROUP BY T1.sys_org_id, T1.school_class_id, T1.class_number
) AS T2 ON T1.sys_org_id = T2.sys_org_id AND T1.school_class_id = T2.school_class_id AND T1.class_number = T2.class_number;
");
                    }
                    #endregion
                    #endregion

                    #region 学校分析
                    var orgTotalLines = await rep.Change<EsaLineTotal>().DetachedEntities
                                                                        .Where(t => t.EsaLevel == EsaLevel.DISTRICT &&
                                                                                    t.EsaLineLevel == esaBaseLine.EsaLineLevel &&
                                                                                    t.EsaDataScopeType == EsaDataScopeType.ORG &&
                                                                                    t.GradeId == esaBaseLine.GradeId
                                                                         ).OrderBy(t => t.SysOrgId).ToListAsync();
                    foreach (var orgTotalLine in orgTotalLines)
                    {
                        // 构造取数条件
                        var orgWhereBaseSql = $"WHERE T1.exam_plan_id = {plan.ExamPlanId} AND T1.grade_id = {esaBaseLine.GradeId} AND T1.score > 0 AND T1.sys_org_id = {orgTotalLine.SysOrgId}";
                        var orgWhereSql = $"{orgWhereBaseSql} AND T1.course_id IN ({courseIds})";

                        #region 总分划线
                        // 总学生数量
                        var orgTotalCount = await rep.SqlScalarAsync<int>($@"
SELECT COUNT(1) AS total_count
FROM
(
    SELECT T1.exam_student_id
    FROM exam_score AS T1
    {orgWhereSql}
    GROUP BY T1.exam_student_id
) AS T1;
");

                        // 总分上线分
                        var orgTotalLineScore = await rep.SqlScalarAsync<decimal>($@"
SELECT MIN(total_score) AS line_score
FROM
(
    SELECT ROW_NUMBER() OVER(ORDER BY total_score DESC) AS rn, total_score
    FROM
    (
        SELECT T1.exam_student_id, SUM(T1.score) AS total_score
        FROM exam_score AS T1
        {orgWhereSql}
        GROUP BY T1.exam_student_id
    ) AS T1
) AS T1
WHERE T1.rn <= ROUND({orgTotalCount} * {orgTotalLine.LineRate}, 0);
");

                        // 总分上线均分
                        var orgTotalLineAvgScore = Math.Round(await rep.SqlScalarAsync<decimal>(@$"
SELECT AVG(T1.total_score) AS line_avg_score
FROM
(
    SELECT T1.exam_student_id, SUM(T1.score) AS total_score
    FROM exam_score AS T1
    {orgWhereSql}
    GROUP BY T1.exam_student_id
) AS T1
WHERE T1.total_score >= {orgTotalLineScore};
"), 2);

                        // 更新有效系数
                        var orgFactor = Math.Round(orgTotalLineScore / orgTotalLineAvgScore, 8);
                        await rep.SqlNonQueryAsync($"UPDATE esa_line_total SET factor = {orgFactor} WHERE id = {orgTotalLine.Id}");

                        // 实际上线人数
                        var orgFactLineCount = await rep.SqlScalarAsync($@"
SELECT COUNT(1) AS line_count
FROM
(
    SELECT T1.exam_student_id, SUM(T1.score) AS total_score
    FROM exam_score AS T1
    {orgWhereSql}
    GROUP BY T1.exam_student_id
) AS T1
WHERE T1.total_score >= {orgTotalLineScore};
");

                        // 总均分
                        var orgTotalAvgScore = Math.Round(await rep.SqlScalarAsync<decimal>(@$"
SELECT AVG(total_score) AS avg_score
FROM
(
    SELECT T1.exam_student_id, SUM(T1.score) AS total_score
    FROM exam_score AS T1
    {orgWhereSql}
    GROUP BY T1.exam_student_id
) AS T1;
"), 2);

                        // 学科有效分
                        var orgTotalRelativeDiff = Math.Round((orgTotalLineScore - orgTotalAvgScore) / orgTotalLineScore, 8);
                        await rep.SqlNonQueryAsync(@$"
-- 总分
INSERT INTO esa_line_course_score(esa_level, esa_line_level, esa_plan_id, esa_base_line_id, grade_id, sys_org_id, course_id, avg_score, line_avg_score, line_score, relative_diff)
VALUES({(short)EsaLevel.ORG}, {(short)esaBaseLine.EsaLineLevel}, {esaPlanId}, {esaBaseLine.Id}, {esaBaseLine.GradeId}, {orgTotalLine.SysOrgId}, {(short)MultCourse.TOTAL}, {orgTotalAvgScore}, {orgTotalLineAvgScore}, {orgTotalLineScore}, {orgTotalRelativeDiff});

-- 单科
INSERT INTO esa_line_course_score(esa_level, esa_line_level, esa_plan_id, esa_base_line_id, grade_id, sys_org_id, course_id, avg_score, line_avg_score, line_score, relative_diff)
SELECT {(short)EsaLevel.ORG}, {(short)esaBaseLine.EsaLineLevel}, {esaPlanId}, {esaBaseLine.Id}, {esaBaseLine.GradeId}, {orgTotalLine.SysOrgId}, T1.course_id, T2.avg_course_score, T1.avg_line_course_score, T1.course_line_score, ROUND((T1.course_line_score - T2.avg_course_score) / T1.course_line_score, 8) AS relative_diff
FROM
(
    SELECT T1.course_id, ROUND(AVG(T1.score), 2) AS avg_line_course_score, ROUND(ROUND(AVG(T1.score), 2) * {orgFactor}, 2) AS course_line_score
    FROM
    (
        SELECT T1.exam_student_id, T1.course_id, T1.score
        FROM exam_score AS T1
        {orgWhereSql}
    ) AS T1
    JOIN
    (
        SELECT T1.exam_student_id
        FROM
        (
            SELECT T1.exam_student_id, SUM(T1.score) AS total_score
            FROM exam_score AS T1
            {orgWhereSql}
            GROUP BY T1.exam_student_id
        ) AS T1
        WHERE T1.total_score >= {orgTotalLineScore}
    ) AS T2 ON T1.exam_student_id = T2.exam_student_id
    GROUP BY T1.course_id
) AS T1
JOIN
(
    SELECT T1.course_id, ROUND(AVG(T1.score), 2) AS avg_course_score
    FROM exam_score AS T1
    {orgWhereSql}
    GROUP BY T1.course_id
) AS T2 ON T1.course_id = T2.course_id;
");

                        // 学校
                        await rep.SqlNonQueryAsync(@$"
INSERT INTO esa_line_total(esa_level, esa_data_scope_type, esa_line_level, esa_plan_id, esa_base_line_id, grade_id, sys_org_id, line_count, total_count, line_rate, factor)
SELECT {(short)EsaLevel.ORG}, {(short)EsaDataScopeType.ORG}, {(short)esaBaseLine.EsaLineLevel}, {esaPlanId}, {esaBaseLine.Id}, {esaBaseLine.GradeId}, {orgTotalLine.SysOrgId}, COUNT(1) AS line_count, T2.total_count, COUNT(1) / T2.total_count, 0
FROM
(
    SELECT T1.exam_student_id, SUM(T1.score) AS total_score
    FROM exam_score AS T1
    {orgWhereSql}
    GROUP BY T1.exam_student_id
) AS T1
JOIN 
(
    SELECT COUNT(1) as total_count
    FROM
    (
        SELECT T1.exam_student_id, SUM(T1.score) AS total_score
        FROM exam_score AS T1
        {orgWhereSql}
        GROUP BY T1.exam_student_id
    ) AS T1
) AS T2 ON 1 = 1
WHERE T1.total_score >= {orgTotalLineScore};
");

                        // 班级
                        await rep.SqlNonQueryAsync(@$"
INSERT INTO esa_line_total(esa_level, esa_data_scope_type, esa_line_level, esa_plan_id, esa_base_line_id, grade_id, sys_org_id, school_class_id, class_number, line_count, total_count, line_rate, factor)
SELECT {(short)EsaLevel.ORG}, {(short)EsaDataScopeType.CLASS}, {(short)esaBaseLine.EsaLineLevel}, {esaPlanId}, {esaBaseLine.Id}, {esaBaseLine.GradeId}, {orgTotalLine.SysOrgId}, T1.school_class_id, T1.class_number, COUNT(1) AS line_count, T2.total_count, COUNT(1) / T2.total_count, 0
FROM
(
    SELECT T1.school_class_id, T1.class_number, T1.exam_student_id, SUM(T1.score) AS total_score
    FROM exam_score AS T1
    {orgWhereSql}
    GROUP BY T1.school_class_id, T1.class_number, T1.exam_student_id
) AS T1
JOIN 
(
    SELECT T1.school_class_id, T1.class_number, COUNT(1) as total_count
    FROM
    (
        SELECT T1.school_class_id, T1.class_number, T1.exam_student_id, SUM(T1.score) AS total_score
        FROM exam_score AS T1
        {orgWhereSql}
        GROUP BY T1.school_class_id, T1.class_number, T1.exam_student_id
    ) AS T1
    GROUP BY T1.school_class_id, T1.class_number
) AS T2 ON T1.school_class_id = T2.school_class_id AND T1.class_number = T2.class_number
WHERE T1.total_score >= {orgTotalLineScore}
GROUP BY T1.school_class_id, T1.class_number;
");
                        #endregion

                        #region 学科划线
                        var orgCourseLines = await rep.Change<EsaLineCourseScore>().DetachedEntities
                                                                                   .Where(t => t.EsaPlanId == esaPlanId &&
                                                                                               t.EsaBaseLineId == esaBaseLine.Id &&
                                                                                               t.EsaLevel == EsaLevel.ORG &&
                                                                                               t.EsaLineLevel == esaBaseLine.EsaLineLevel &&
                                                                                               t.GradeId == esaBaseLine.GradeId &&
                                                                                               t.SysOrgId == orgTotalLine.SysOrgId &&
                                                                                               t.CourseId != (short)MultCourse.TOTAL
                                                                                    ).ToListAsync();
                        foreach (var orgCourseLine in orgCourseLines)
                        {
                            // 单科条件
                            var orgCourseWhereSql = $"{orgWhereBaseSql} AND T1.course_id = {orgCourseLine.CourseId}";

                            // 学校 - 单上线
                            await rep.SqlNonQueryAsync(@$"
INSERT INTO esa_line_course(esa_level, esa_data_scope_type, esa_line_level, esa_plan_id, esa_base_line_id, course_id, grade_id, sys_org_id, line_count, total_count, line_rate, is_double_line)
SELECT {(short)EsaLevel.ORG}, {(short)EsaDataScopeType.ORG}, {(short)esaBaseLine.EsaLineLevel}, {esaPlanId}, {esaBaseLine.Id}, {orgCourseLine.CourseId}, {esaBaseLine.GradeId}, {orgTotalLine.SysOrgId}, T1.line_count, T2.total_count, T1.line_count / T2.total_count, 0
FROM
(
    SELECT COUNT(1) AS line_count
    FROM exam_score AS T1
    {orgCourseWhereSql} AND T1.score >= {orgCourseLine.LineScore}
) AS T1
JOIN 
(
    SELECT COUNT(1) as total_count
    FROM exam_score AS T1
    {orgCourseWhereSql}
) AS T2 ON 1 = 1;
");

                            // 学校 - 双上线
                            await rep.SqlNonQueryAsync(@$"
INSERT INTO esa_line_course(esa_level, esa_data_scope_type, esa_line_level, esa_plan_id, esa_base_line_id, course_id, grade_id, sys_org_id, line_count, total_count, line_rate, is_double_line)
SELECT {(short)EsaLevel.ORG}, {(short)EsaDataScopeType.ORG}, {(short)esaBaseLine.EsaLineLevel}, {esaPlanId}, {esaBaseLine.Id}, {orgCourseLine.CourseId}, {esaBaseLine.GradeId}, {orgTotalLine.SysOrgId}, T1.line_count, T2.total_count, T1.line_count / T2.total_count, 1
FROM
(
    SELECT COUNT(1) AS line_count
    FROM
    (
        SELECT T1.exam_student_id, T1.score
        FROM exam_score AS T1
        {orgCourseWhereSql}
    ) AS T1
    JOIN
    (
        SELECT T1.exam_student_id, SUM(T1.score) AS total_score
        FROM exam_score AS T1
        {orgWhereSql}
        GROUP BY T1.exam_student_id
    ) AS T2 ON T1.exam_student_id = T2.exam_student_id
    WHERE T1.score >= {orgCourseLine.LineScore} AND T2.total_score >= {orgTotalLineScore}
) AS T1
JOIN 
(
    SELECT COUNT(1) as total_count
    FROM exam_score AS T1
    {orgCourseWhereSql}
) AS T2 ON 1 = 1;
");

                            // 班级 - 单上线
                            await rep.SqlNonQueryAsync(@$"
INSERT INTO esa_line_course(esa_level, esa_data_scope_type, esa_line_level, esa_plan_id, esa_base_line_id, course_id, grade_id, sys_org_id, school_class_id, class_number, line_count, total_count, line_rate, is_double_line)
SELECT {(short)EsaLevel.ORG}, {(short)EsaDataScopeType.CLASS}, {(short)esaBaseLine.EsaLineLevel}, {esaPlanId}, {esaBaseLine.Id}, {orgCourseLine.CourseId}, {esaBaseLine.GradeId}, {orgTotalLine.SysOrgId}, T1.school_class_id, T1.class_number, T1.line_count, T2.total_count, T1.line_count / T2.total_count, 0
FROM
(
    SELECT T1.school_class_id, T1.class_number, COUNT(1) AS line_count
    FROM exam_score AS T1
    {orgCourseWhereSql} AND T1.score >= {orgCourseLine.LineScore}
    GROUP BY T1.school_class_id, T1.class_number
) AS T1
JOIN 
(
    SELECT T1.school_class_id, T1.class_number, COUNT(1) as total_count
    FROM exam_score AS T1
    {orgCourseWhereSql}
    GROUP BY T1.school_class_id, T1.class_number
) AS T2 ON T1.school_class_id = T2.school_class_id AND T1.class_number = T2.class_number;
");

                            // 班级 - 双上线
                            await rep.SqlNonQueryAsync(@$"
INSERT INTO esa_line_course(esa_level, esa_data_scope_type, esa_line_level, esa_plan_id, esa_base_line_id, course_id, grade_id, sys_org_id, school_class_id, class_number, line_count, total_count, line_rate, is_double_line)
SELECT {(short)EsaLevel.ORG}, {(short)EsaDataScopeType.CLASS}, {(short)esaBaseLine.EsaLineLevel}, {esaPlanId}, {esaBaseLine.Id}, {orgCourseLine.CourseId}, {esaBaseLine.GradeId}, {orgTotalLine.SysOrgId}, T1.school_class_id, T1.class_number, T1.line_count, T2.total_count, T1.line_count / T2.total_count, 1
FROM
(
    SELECT T1.school_class_id, T1.class_number, COUNT(1) AS line_count
    FROM
    (
        SELECT T1.school_class_id, T1.class_number, T1.exam_student_id, T1.score
        FROM exam_score AS T1
        {orgCourseWhereSql}
    ) AS T1
    JOIN
    (
        SELECT T1.school_class_id, T1.class_number, T1.exam_student_id, SUM(T1.score) AS total_score
        FROM exam_score AS T1
        {orgWhereSql}
        GROUP BY T1.school_class_id, T1.class_number, T1.exam_student_id
    ) AS T2 ON T1.school_class_id = T2.school_class_id AND T1.class_number = T2.class_number AND T1.exam_student_id = T2.exam_student_id
    WHERE T1.score >= {orgCourseLine.LineScore} AND T2.total_score >= {orgTotalLineScore}
    GROUP BY T1.school_class_id, T1.class_number
) AS T1
JOIN 
(
    SELECT T1.school_class_id, T1.class_number, COUNT(1) as total_count
    FROM exam_score AS T1
    {orgCourseWhereSql}
    GROUP BY T1.school_class_id, T1.class_number
) AS T2 ON T1.school_class_id = T2.school_class_id AND T1.class_number = T2.class_number;
");
                        }
                        #endregion
                    }
                    #endregion

                    // 更新状态
                    await rep.SqlNonQueryAsync($"UPDATE esa_base_line SET status = {(short)ProcessingStatus.SUCCESSFUL} WHERE id = {esaBaseLine.Id}");
                }
                catch (Exception)
                {
                    // 更新状态
                    await rep.SqlNonQueryAsync($"UPDATE esa_base_line SET status = {(short)ProcessingStatus.FAILED} WHERE id = {esaBaseLine.Id}");
                    throw;
                }
            }

            // 更新状态
            await rep.SqlNonQueryAsync($"UPDATE esa_plan SET status = {(short)ProcessingStatus.SUCCESSFUL} WHERE id = {esaPlanId}");
        }
        catch (Exception)
        {
            // 更新状态
            await rep.SqlNonQueryAsync($"UPDATE esa_plan SET status = {(short)ProcessingStatus.FAILED} WHERE id = {esaPlanId}");
            throw;
        }
    }
}