YBEE.EQM/YBEE.EQM.Application/Exam/ExamReporting/Services/ExamReportingAvgRangeService.cs

using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System.Data;
using YBEE.EQM.Core;

namespace YBEE.EQM.Application;

/// <summary>
/// 统计报表之分数段报表服务
/// </summary>
public class ExamReportingAvgRangeService : IExamReportingAvgRangeService, ITransient
{
    private readonly IRepository _rep;
    private readonly ISqlRepository _sqlRep;
    private readonly IExamScoreRangeService _examScoreRangeService;
    private readonly IExamGradeService _examGradeService;
    private readonly IExamCourseService _examCourseService;
    private readonly IExportExcelService _exportExcelService;

    public ExamReportingAvgRangeService(IRepository rep, ISqlRepository sqlRep, IExamScoreRangeService examScoreRangeService, IExamGradeService examGradeService, IExamCourseService examCourseService, ExportExcelService exportExcelService)
    {
        _rep = rep;
        _sqlRep = sqlRep;
        _examScoreRangeService = examScoreRangeService;
        _examGradeService = examGradeService;
        _examCourseService = examCourseService;
        _exportExcelService = exportExcelService;
    }

    /// <summary>
    /// 导出分数段统计表
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task<(string, byte[])> Export(int examPlanId)
    {
        var examPlan = await _rep.Change<ExamPlan>().DetachedEntities.ProjectToType<ExamPlanOutput>().FirstOrDefaultAsync(t => t.Id == examPlanId) ?? throw Oops.Oh(ErrorCode.E2001, "监测计划");
        var scoreRanges = await _examScoreRangeService.GetList();
        var examGrades = await _examGradeService.GetListByExamPlanId(examPlanId);
        var examCourses = await _examCourseService.GetListByExamPlanId(examPlanId);

        // 临时存放目录
        string fileRoot = Path.Combine(FileUtil.GetTempFileRoot(), $"{Guid.NewGuid()}");
        Directory.CreateDirectory(fileRoot);
        string filePath = Path.Combine(fileRoot, $"{examPlan.Name}-分数段平均分统计");
        Directory.CreateDirectory(filePath);
        try
        {
            foreach (var examGrade in examGrades)
            {
                string titlePrefix = $"{examPlan.Semester.Name}{examGrade.Grade.Name}期末";
                for (int i = 0; i < 2; i++)
                {
                    IWorkbook wb = new XSSFWorkbook();
                    var cellStyles = _exportExcelService.GetCellStyle(wb);

                    ExamSampleType? sampleType = i == 1 ? ExamSampleType.DISTRICT : null;

                    await ExportTotalRange(titlePrefix, examPlanId, sampleType, examGrade.GradeId, scoreRanges.Where(t => t.Type == examGrade.ExamScoreRangeType).ToList(), wb, cellStyles);
                    await ExportCourseRange(titlePrefix, examPlanId, sampleType, examGrade.GradeId, scoreRanges, examCourses.Where(t => t.GradeId == examGrade.GradeId).ToList(), wb, cellStyles);
                    await ExportCourseAvgScore(titlePrefix, examPlanId, sampleType, examGrade.GradeId, examCourses.Where(t => t.GradeId == examGrade.GradeId).ToList(), wb, cellStyles);

                    MemoryStream ms = new();
                    wb.Write(ms, false);
                    ms.Flush();
                    string fn = i == 0 ? "全员" : "抽测";
                    await File.WriteAllBytesAsync(Path.Combine(filePath, $"{examGrade.Grade.Name}-{fn}-分数段统计.xlsx"), ms.ToArray());

                    if (!examGrade.IsRequiredSample)
                    {
                        break;
                    }
                }
            }

            string outFileName = $"{examPlan.Name}-分数段平均分统计-{DateTime.Now.Ticks}.zip";
            string outFilePath = Path.Combine(fileRoot, outFileName);
            ICSharpCode.SharpZipLib.Zip.FastZip zip = new();
            zip.CreateZip(outFilePath, filePath, true, string.Empty);

            var retBytes = await File.ReadAllBytesAsync(outFilePath);
            return (outFileName, retBytes);
        }
        catch (Exception ex)
        {
            throw new Exception("导出错误", ex);
        }
        finally
        {
            Directory.Delete(fileRoot, true);
        }
    }

    /// <summary>
    /// 导出总分分数段
    /// </summary>
    /// <param name="titlePrefix"></param>
    /// <param name="examPlanId"></param>
    /// <param name="sampleType"></param>
    /// <param name="gradeId"></param>
    /// <param name="ranges"></param>
    /// <param name="wb"></param>
    /// <param name="cellStyles"></param>
    /// <returns></returns>
    private async Task ExportTotalRange(string titlePrefix, int examPlanId, ExamSampleType? sampleType, short gradeId, List<ExamScoreRangeOutput> ranges, IWorkbook wb, ExportExcelCellStyle cellStyles)
    {
        ISheet sheet = wb.CreateSheet("总分分数段");
        sheet.DisplayGridlines = false;
        sheet.CreateFreezePane(0, 2);

        int rowNum = 0;

        #region 标题
        IRow titleRow = sheet.CreateRow(rowNum++);
        titleRow.HeightInPoints = 40;
        int ci = 0;
        _exportExcelService.AddCell($"{titlePrefix}总分分数段统计", titleRow, ci++, cellStyles.TitleStyle);
        _exportExcelService.AddCell("", titleRow, ci++, cellStyles.TitleStyle);
        _exportExcelService.AddCell("", titleRow, ci++, cellStyles.TitleStyle);
        foreach (var r in ranges)
        {
            _exportExcelService.AddCell("", titleRow, ci++, cellStyles.TitleStyle);
        }
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));
        #endregion

        #region 列头
        IRow headerRow = sheet.CreateRow(rowNum++);
        headerRow.HeightInPoints = 30;
        ci = 0;
        _exportExcelService.AddCell("学校", headerRow, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 20);
        _exportExcelService.AddCell("类别", headerRow, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 6);
        _exportExcelService.AddCell("实考\r\n人数", headerRow, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 6);
        foreach (var r in ranges)
        {
            _exportExcelService.AddCell(r.NickName, headerRow, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 6);
        }
        #endregion

        #region 数据
        var list = await GetTotalRangeList(examPlanId, gradeId, sampleType);
        var urGroup = list.GroupBy(t => t.UrbanRuralType).OrderBy(t => t.Key).ToList();
        foreach (var urg in urGroup)
        {
            var orgGroup = urg.GroupBy(t => new { t.SysOrgId, t.TotalCount }).ToList();//.OrderByDescending(t => t.Key.TotalCount).ToList();
            foreach (var orgg in orgGroup)
            {
                IRow row = sheet.CreateRow(rowNum++);
                row.HeightInPoints = 20;
                ci = 0;
                var forg = orgg.First();
                _exportExcelService.AddCell(forg.SysOrgName, row, ci++, cellStyles.LeftCellStyle);
                _exportExcelService.AddCell(forg.UrbanRuralType.GetDescription(), row, ci++, cellStyles.CenterCellStyle);
                _exportExcelService.AddCell(forg.TotalCount, row, ci++, cellStyles.CenterCellStyle);
                foreach (var r in ranges)
                {
                    var sr = orgg.FirstOrDefault(t => t.ExamScoreRangeId == r.Id);
                    _exportExcelService.AddCell(sr?.RangeCount, row, ci++, cellStyles.CenterCellStyle);
                }
            }
        }
        #endregion

        #region 合计
        IRow totalRow = sheet.CreateRow(rowNum++);
        totalRow.HeightInPoints = 20;
        ci = 0;
        _exportExcelService.AddCell("合计", totalRow, ci++, cellStyles.ColumnHeaderStyle);
        _exportExcelService.AddCell("", totalRow, ci++, cellStyles.ColumnHeaderStyle);
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, 1));
        _exportExcelService.AddCell($"SUM(C3:C{rowNum - 1})", totalRow, ci++, cellStyles.ColumnHeaderStyle, cellType: CellType.Formula);
        foreach (var r in ranges)
        {
            var cn = ExcelUtil.GetColumnNameByIndex(ci);
            _exportExcelService.AddCell($"SUM({cn}3:{cn}{rowNum - 1})", totalRow, ci++, cellStyles.ColumnHeaderStyle, cellType: CellType.Formula);
        }
        #endregion
    }
    /// <summary>
    /// 导出学科分数段
    /// </summary>
    /// <param name="titlePrefix"></param>
    /// <param name="examPlanId"></param>
    /// <param name="sampleType"></param>
    /// <param name="gradeId"></param>
    /// <param name="ranges"></param>
    /// <param name="examCourses"></param>
    /// <param name="wb"></param>
    /// <param name="cellStyles"></param>
    /// <returns></returns>
    private async Task ExportCourseRange(string titlePrefix, int examPlanId, ExamSampleType? sampleType, short gradeId, List<ExamScoreRangeOutput> ranges, List<ExamCourseOutput> examCourses, IWorkbook wb, ExportExcelCellStyle cellStyles)
    {
        ISheet sheet = wb.CreateSheet("学科分数段");
        sheet.DisplayGridlines = false;

        int rowNum = 0;

        foreach (var examCourse in examCourses)
        {
            var list = await GetCourseRangeList(examPlanId, gradeId, examCourse.CourseId, sampleType);
            if (!list.Any())
            {
                continue;
            }

            var courseRanges = ranges.Where(t => t.Type == examCourse.ExamScoreRangeType).ToList();

            #region 标题
            IRow titleRow = sheet.CreateRow(rowNum++);
            titleRow.HeightInPoints = 40;
            int ci = 0;
            _exportExcelService.AddCell($"{titlePrefix}{examCourse.Course.Name}分数段统计", titleRow, ci++, cellStyles.TitleStyle);
            _exportExcelService.AddCell("", titleRow, ci++, cellStyles.TitleStyle);
            _exportExcelService.AddCell("", titleRow, ci++, cellStyles.TitleStyle);
            foreach (var r in courseRanges)
            {
                _exportExcelService.AddCell("", titleRow, ci++, cellStyles.TitleStyle);
            }
            sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));
            #endregion

            #region 列头
            IRow headerRow = sheet.CreateRow(rowNum++);
            headerRow.HeightInPoints = 20;
            ci = 0;
            _exportExcelService.AddCell("学校", headerRow, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 20);
            _exportExcelService.AddCell("类别", headerRow, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 6);
            _exportExcelService.AddCell("实考人数", headerRow, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 10);
            foreach (var r in courseRanges)
            {
                _exportExcelService.AddCell(r.NickName, headerRow, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 10);
            }
            #endregion

            #region 数据
            int rc = 0;
            var urGroup = list.GroupBy(t => t.UrbanRuralType).OrderBy(t => t.Key).ToList();
            foreach (var urg in urGroup)
            {
                var orgGroup = urg.GroupBy(t => new { t.SysOrgId, t.TotalCount }).ToList();//.OrderByDescending(t => t.Key.TotalCount).ToList();
                foreach (var orgg in orgGroup)
                {
                    rc++;
                    IRow row = sheet.CreateRow(rowNum++);
                    row.HeightInPoints = 20;
                    ci = 0;
                    var forg = orgg.First();
                    _exportExcelService.AddCell(forg.SysOrgName, row, ci++, cellStyles.LeftCellStyle);
                    _exportExcelService.AddCell(forg.UrbanRuralType.GetDescription(), row, ci++, cellStyles.CenterCellStyle);
                    _exportExcelService.AddCell(forg.TotalCount, row, ci++, cellStyles.CenterCellStyle);
                    foreach (var r in courseRanges)
                    {
                        var sr = orgg.FirstOrDefault(t => t.ExamScoreRangeId == r.Id);
                        _exportExcelService.AddCell(sr?.RangeCount, row, ci++, cellStyles.CenterCellStyle);
                    }
                }
            }
            #endregion

            #region 合计
            IRow totalRow = sheet.CreateRow(rowNum++);
            totalRow.HeightInPoints = 20;
            ci = 0;
            _exportExcelService.AddCell("合计", totalRow, ci++, cellStyles.ColumnHeaderStyle);
            _exportExcelService.AddCell("", totalRow, ci++, cellStyles.ColumnHeaderStyle);
            sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, 1));
            _exportExcelService.AddCell($"SUM(C{rowNum - rc}:C{rowNum - 1})", totalRow, ci++, cellStyles.ColumnHeaderStyle, cellType: CellType.Formula);
            foreach (var r in courseRanges)
            {
                var cn = ExcelUtil.GetColumnNameByIndex(ci);
                _exportExcelService.AddCell($"SUM({cn}{rowNum - rc}:{cn}{rowNum - 1})", totalRow, ci++, cellStyles.ColumnHeaderStyle, cellType: CellType.Formula);
            }
            #endregion

            rowNum += 2;
        }
    }
    /// <summary>
    /// 导出学科平均分
    /// </summary>
    /// <param name="titlePrefix"></param>
    /// <param name="examPlanId"></param>
    /// <param name="sampleType"></param>
    /// <param name="gradeId"></param>
    /// <param name="examCourses"></param>
    /// <param name="wb"></param>
    /// <param name="cellStyles"></param>
    /// <returns></returns>
    private async Task ExportCourseAvgScore(string titlePrefix, int examPlanId, ExamSampleType? sampleType, short gradeId, List<ExamCourseOutput> examCourses, IWorkbook wb, ExportExcelCellStyle cellStyles)
    {
        var courses = examCourses.Select(t => t.Course).ToList();
        courses.Insert(0, new CourseLiteOutput() { Id = 0, Name = "总分" });

        ISheet sheet = wb.CreateSheet("学科平均分");
        sheet.DisplayGridlines = false;
        //sheet.CreateFreezePane(0, 2);

        var cellStyleNumberP2Blod = wb.CreateCellStyle();
        cellStyleNumberP2Blod.CloneStyleFrom(cellStyles.NumberCellStyleP2);
        //var font = cellStyleNumberP2Blod.GetFont(wb);
        //font.IsBold = true;
        IFont bFont = wb.CreateFont();
        bFont.IsBold = true;
        bFont.FontName = cellStyles.TitleFontName;
        bFont.FontHeightInPoints = 10;
        cellStyleNumberP2Blod.SetFont(bFont);

        int rowNum = 0;

        #region 标题
        IRow titleRow = sheet.CreateRow(rowNum++);
        titleRow.HeightInPoints = 40;
        int ci = 0;
        _exportExcelService.AddCell($"{titlePrefix}各学科均分统计", titleRow, ci++, cellStyles.TitleStyle);
        _exportExcelService.AddCell("", titleRow, ci++, cellStyles.TitleStyle);
        _exportExcelService.AddCell("", titleRow, ci++, cellStyles.TitleStyle);
        foreach (var r in courses)
        {
            _exportExcelService.AddCell("", titleRow, ci++, cellStyles.TitleStyle);
            _exportExcelService.AddCell("", titleRow, ci++, cellStyles.TitleStyle);
            _exportExcelService.AddCell("", titleRow, ci++, cellStyles.TitleStyle);
        }
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));
        #endregion

        #region 列头
        IRow headerRow1 = sheet.CreateRow(rowNum++);
        headerRow1.HeightInPoints = 20;
        ci = 0;
        _exportExcelService.AddCell("学校", headerRow1, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 20);
        _exportExcelService.AddCell("类别", headerRow1, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 8);
        _exportExcelService.AddCell("实考\r\n人数", headerRow1, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 8);
        foreach (var r in courses)
        {
            _exportExcelService.AddCell(r.Name, headerRow1, ci++, cellStyles.ColumnFillHeaderStyle);
            _exportExcelService.AddCell("", headerRow1, ci++, cellStyles.ColumnFillHeaderStyle);
            _exportExcelService.AddCell("", headerRow1, ci++, cellStyles.ColumnFillHeaderStyle);
            sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 3, ci - 1));
        }

        IRow headerRow2 = sheet.CreateRow(rowNum++);
        headerRow2.HeightInPoints = 30;
        ci = 0;
        _exportExcelService.AddCell("", headerRow2, ci++, cellStyles.ColumnFillHeaderStyle);
        _exportExcelService.AddCell("", headerRow2, ci++, cellStyles.ColumnFillHeaderStyle);
        _exportExcelService.AddCell("", headerRow2, ci++, cellStyles.ColumnFillHeaderStyle);
        foreach (var r in courses)
        {
            _exportExcelService.AddCell("平均分", headerRow2, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 8);
            _exportExcelService.AddCell("名次", headerRow2, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 5);
            _exportExcelService.AddCell("与第一\r\n名分差", headerRow2, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 8);
        }
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 0, 0));
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 1, 1));
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 2, rowNum - 1, 2, 2));
        #endregion

        #region 数据
        var list = await GetAvgScoreList(examPlanId, gradeId, sampleType);
        // 城乡分组
        var urGroup = list.GroupBy(t => t.UrbanRuralType).OrderBy(t => t.Key == UrbanRuralType.NONE ? 99 : (short)t.Key).ToList();
        foreach (var urg in urGroup)
        {
            // 机构
            var orgGroup = urg.GroupBy(t => t.SysOrgId).OrderBy(t => t.FirstOrDefault()?.DataScopeType).ThenByDescending(t => t.FirstOrDefault(c => c.CourseId == 0)?.TotalCount).ToList();
            foreach (var orgg in orgGroup)
            {
                IRow row = sheet.CreateRow(rowNum++);
                row.HeightInPoints = 20;
                ci = 0;
                var forg = orgg.FirstOrDefault(t => t.CourseId == 0);
                if (forg.DataScopeType > 1)
                {
                    var txt = forg?.SysOrgName ?? "";
                    if (forg?.UrbanRuralType != UrbanRuralType.NONE)
                    {
                        txt = $"{forg?.UrbanRuralType?.GetDescription()}{txt}";
                    }
                    _exportExcelService.AddCell(txt, row, ci++, cellStyles.ColumnHeaderStyle);
                    _exportExcelService.AddCell("", row, ci++, cellStyles.ColumnHeaderStyle);
                    sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
                }
                else
                {
                    _exportExcelService.AddCell(forg?.SysOrgName, row, ci++, cellStyles.LeftCellStyle);
                    _exportExcelService.AddCell(forg?.UrbanRuralType.GetDescription(), row, ci++, cellStyles.CenterCellStyle);
                }
                _exportExcelService.AddCell(forg?.TotalCount, row, ci++, forg?.DataScopeType != 1 ? cellStyles.ColumnHeaderStyle : cellStyles.CenterCellStyle);
                foreach (var r in courses)
                {
                    var sr = orgg.FirstOrDefault(t => t.CourseId == r.Id);
                    _exportExcelService.AddCell(sr?.AvgScore, row, ci++, sr?.DataScopeType != 1 ? cellStyleNumberP2Blod : cellStyles.NumberCellStyleP2);
                    _exportExcelService.AddCell(sr?.DataScopeType != 1 ? null : sr?.OrderInTotal, row, ci++, cellStyles.CenterCellStyle);
                    _exportExcelService.AddCell(sr?.AvgScoreDiff, row, ci++, sr?.DataScopeType != 1 ? cellStyleNumberP2Blod : cellStyles.NumberCellStyleP2);
                }
            }
        }
        #endregion

        //#region 合计
        //IRow totalRow = sheet.CreateRow(rowNum++);
        //totalRow.HeightInPoints = 20;
        //ci = 0;
        //_exportExcelService.AddCell("合计", totalRow, ci++, cellStyles.ColumnHeaderStyle);
        //_exportExcelService.AddCell("", totalRow, ci++, cellStyles.ColumnHeaderStyle);
        //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowNum - 1, rowNum - 1, 0, 1));
        //_exportExcelService.AddCell($"SUM(C3:C{rowNum - 1})", totalRow, ci++, cellStyles.ColumnHeaderStyle, cellType: CellType.Formula);
        //foreach (var r in ranges)
        //{
        //    var cn = ExcelUtil.GetColumnNameByIndex(ci);
        //    _exportExcelService.AddCell($"SUM({cn}3:{cn}{rowNum - 1})", totalRow, ci++, cellStyles.ColumnHeaderStyle, cellType: CellType.Formula);
        //}
        //#endregion
    }

    #region 数据获取
    /// <summary>
    /// 获取总分分数段数据
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <param name="gradeId"></param>
    /// <param name="sampleType"></param>
    /// <returns></returns>
    private async Task<List<ExamScoreRangeExportDto>> GetTotalRangeList(int examPlanId, short gradeId, ExamSampleType? sampleType)
    {
        var items = await _sqlRep.SqlQueriesAsync<ExamScoreRangeExportDto>($@"
SELECT T2.urban_rural_type, T2.`name` AS sys_org_name, T1.*
FROM
(
	SELECT T1.sys_org_id, T1.exam_score_range_id, COUNT(T1.exam_student_id) AS range_count, MAX(T1.total_count) AS total_count
	FROM
	(
		SELECT T1.sys_org_id, T1.exam_score_range_id, T1.exam_student_id, COUNT(T1.exam_student_id) OVER (PARTITION BY T1.sys_org_id) AS total_count
		FROM exam_score_total AS T1
		WHERE exam_plan_id = @examPlanId AND T1.grade_id = @gradeId AND T1.score > 0 AND (T1.exam_sample_type = @examSampleType OR @examSampleType = 0)
	) AS T1
	GROUP BY T1.sys_org_id, T1.exam_score_range_id
) AS T1
JOIN sys_org AS T2 ON T1.sys_org_id = T2.id
", new { ExamPlanId = examPlanId, GradeId = gradeId, ExamSampleType = sampleType.HasValue ? (short)sampleType : 0 });

        return items;
    }
    /// <summary>
    /// 获取单科分数段数据
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <param name="gradeId"></param>
    /// <param name="courseId"></param>
    /// <param name="sampleType"></param>
    /// <returns></returns>
    private async Task<List<ExamScoreRangeExportDto>> GetCourseRangeList(int examPlanId, short gradeId, short courseId, ExamSampleType? sampleType)
    {
        var items = await _sqlRep.SqlQueriesAsync<ExamScoreRangeExportDto>($@"
SELECT T2.urban_rural_type, T2.`name` AS sys_org_name, T1.*
FROM
(
	SELECT T1.sys_org_id, T1.exam_score_range_id, COUNT(T1.exam_student_id) AS range_count, MAX(T1.total_count) AS total_count
	FROM
	(
		SELECT T1.sys_org_id, T1.exam_score_range_id, T1.exam_student_id, COUNT(T1.exam_student_id) OVER (PARTITION BY T1.sys_org_id) AS total_count
		FROM exam_score AS T1
		WHERE exam_plan_id = @examPlanId AND T1.grade_id = @gradeId AND T1.course_id = @courseId AND T1.score > 0 AND (T1.exam_sample_type = @examSampleType OR @examSampleType = 0)
	) AS T1
	GROUP BY T1.sys_org_id, T1.exam_score_range_id
) AS T1
JOIN sys_org AS T2 ON T1.sys_org_id = T2.id
", new { ExamPlanId = examPlanId, GradeId = gradeId, CourseId = courseId, ExamSampleType = sampleType.HasValue ? (short)sampleType : 0 });

        return items;
    }
    /// <summary>
    /// 获取平均分数据
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <param name="gradeId"></param>
    /// <param name="sampleType"></param>
    /// <returns></returns>
    private async Task<List<ExamScoreAvgExportDto>> GetAvgScoreList(int examPlanId, short gradeId, ExamSampleType? sampleType)
    {
        var items = await _sqlRep.SqlQueriesAsync<ExamScoreAvgExportDto>($@"
SELECT T1.*
FROM
(
	-- 总分
	SELECT 1 AS data_scope_type, T2.urban_rural_type, T2.`name` AS sys_org_name, T1.*, 
		RANK() OVER(ORDER BY T1.avg_score DESC) AS order_in_total,
		RANK() OVER(PARTITION BY T2.urban_rural_type ORDER BY T1.avg_score DESC) AS order_in_same,
		T3.score_max - T1.avg_score AS avg_score_diff,
		T3.score_max
	FROM
	(
		SELECT sys_org_id, 0 AS course_id, COUNT(exam_student_id) AS total_count, AVG(score) AS avg_score
		FROM exam_score_total
		WHERE exam_plan_id = @examPlanId AND grade_id = @gradeId AND score > 0 AND (exam_sample_type = @examSampleType OR @examSampleType = 0)
		GROUP BY sys_org_id
	) AS T1
	JOIN sys_org AS T2 ON T1.sys_org_id = T2.id
	JOIN 
    (
		SELECT MAX(T.avg_score) AS score_max 
		FROM
		(
			SELECT sys_org_id, AVG(score) AS avg_score
			FROM exam_score_total
			WHERE exam_plan_id = @examPlanId AND grade_id = @gradeId AND score > 0 AND (exam_sample_type = @examSampleType OR @examSampleType = 0)
			GROUP BY sys_org_id
		) AS T
    ) AS T3

    -- 单科	
    UNION ALL
	SELECT 1 AS data_scope_type, T2.urban_rural_type, T2.`name` AS sys_org_name, T1.*, 
		RANK() OVER(PARTITION BY T1.course_id ORDER BY T1.avg_score DESC) AS order_in_total,
		RANK() OVER(PARTITION BY T1.course_id, T2.urban_rural_type ORDER BY T1.avg_score DESC) AS order_in_same,
		T3.score_max - T1.avg_score AS avg_score_diff,
		T3.score_max
	FROM
	(
		SELECT sys_org_id, course_id, COUNT(exam_student_id) AS total_count, AVG(score) AS avg_score
		FROM exam_score
		WHERE exam_plan_id = @examPlanId AND grade_id = @gradeId AND score > 0 AND (exam_sample_type = @examSampleType OR @examSampleType = 0)
		GROUP BY sys_org_id, course_id
	) AS T1
	JOIN sys_org AS T2 ON T1.sys_org_id = T2.id
	JOIN 
    (
		SELECT T.course_id, MAX(T.avg_score) AS score_max
		FROM
		(
			SELECT sys_org_id, course_id, AVG(score) AS avg_score
			FROM exam_score
			WHERE exam_plan_id = @examPlanId AND grade_id = @gradeId AND score > 0 AND (exam_sample_type = @examSampleType OR @examSampleType = 0)
			GROUP BY sys_org_id, course_id
		) AS T
		GROUP BY T.course_id
	) AS T3 ON T1.course_id = T3.course_id

    -- 同类小计
	UNION ALL
	SELECT 2 AS data_scope_type, T1.*, 
		NULL AS order_in_total,
		NULL AS order_in_same,
		T3.score_max - T1.avg_score AS avg_score_diff,
		T3.score_max
	FROM
	(
		SELECT T2.urban_rural_type, '小计' AS sys_org_name, 99999998 AS sys_org_id, 0 AS course_id, COUNT(T1.exam_student_id) AS total_count, AVG(T1.score) AS avg_score
		FROM exam_score_total AS T1
		JOIN sys_org AS T2 ON T1.sys_org_id = T2.id
		WHERE T1.exam_plan_id = @examPlanId AND T1.grade_id = @gradeId AND T1.score > 0 AND (T1.exam_sample_type = @examSampleType OR @examSampleType = 0)
		GROUP BY T2.urban_rural_type
	) AS T1
	JOIN 
	(
		SELECT MAX(score) AS score_max FROM exam_score_total WHERE exam_plan_id = @examPlanId AND grade_id = @gradeId AND score > 0 AND (exam_sample_type = @examSampleType OR @examSampleType = 0)
	) AS T3
	
	-- 单科同类小计
	UNION ALL
	SELECT 2 AS data_scope_type, T1.*, 
		NULL AS order_in_total,
		NULL AS order_in_same,
		T3.score_max - T1.avg_score AS avg_score_diff,
		T3.score_max
	FROM
	(
		SELECT T2.urban_rural_type, '小计' AS sys_org_name, 99999998 AS sys_org_id, T1.course_id, COUNT(T1.exam_student_id) AS total_count, AVG(T1.score) AS avg_score
		FROM exam_score AS T1
		JOIN sys_org AS T2 ON T1.sys_org_id = T2.id
		WHERE T1.exam_plan_id = @examPlanId AND T1.grade_id = @gradeId AND T1.score > 0 AND (T1.exam_sample_type = @examSampleType OR @examSampleType = 0)
		GROUP BY T2.urban_rural_type, T1.course_id
	) AS T1
	JOIN (
		SELECT course_id, MAX(score) AS score_max 
		FROM exam_score 
		WHERE exam_plan_id = @examPlanId AND grade_id = @gradeId AND score > 0 AND (exam_sample_type = @examSampleType OR @examSampleType = 0)
		GROUP BY course_id
	) AS T3 ON T1.course_id = T3.course_id
	
	-- 全部合计
	UNION ALL
	SELECT 3 AS data_scope_type, T1.*, 
		NULL AS order_in_total,
		NULL AS order_in_same,
		T3.score_max - T1.avg_score AS avg_score_diff,
		T3.score_max
	FROM
	(
		SELECT 0 AS urban_rural_type, '合计' AS sys_org_name, 99999999 AS sys_org_id, 0 AS course_id, COUNT(T1.exam_student_id) AS total_count, AVG(T1.score) AS avg_score
		FROM exam_score_total AS T1
		WHERE T1.exam_plan_id = @examPlanId AND T1.grade_id = @gradeId AND T1.score > 0 AND (exam_sample_type = @examSampleType OR @examSampleType = 0)
	) AS T1
	JOIN 
	(
		SELECT MAX(score) AS score_max FROM exam_score_total WHERE exam_plan_id = @examPlanId AND grade_id = @gradeId AND score > 0 AND (exam_sample_type = @examSampleType OR @examSampleType = 0)
	) AS T3
	
	-- 单科全部合计
	UNION ALL
	SELECT 3 AS data_scope_type, T1.*, 
		NULL AS order_in_total,
		NULL AS order_in_same,
		T3.score_max - T1.avg_score AS avg_score_diff,
		T3.score_max
	FROM
	(
		SELECT 0 AS urban_rural_type, '合计' AS sys_org_name, 99999999 AS sys_org_id, T1.course_id, COUNT(T1.exam_student_id) AS total_count, AVG(T1.score) AS avg_score
		FROM exam_score AS T1
		WHERE T1.exam_plan_id = @examPlanId AND T1.grade_id = @gradeId AND T1.score > 0 AND (exam_sample_type = @examSampleType OR @examSampleType = 0)
		GROUP BY T1.course_id
	) AS T1
	JOIN (
		SELECT course_id, MAX(score) AS score_max 
		FROM exam_score 
		WHERE exam_plan_id = @examPlanId AND grade_id = @gradeId AND score > 0 AND (exam_sample_type = @examSampleType OR @examSampleType = 0)
		GROUP BY course_id
	) AS T3 ON T1.course_id = T3.course_id
) AS T1
ORDER BY T1.urban_rural_type, T1.data_scope_type, T1.sys_org_id, T1.total_count;
", new { ExamPlanId = examPlanId, GradeId = gradeId, ExamSampleType = sampleType.HasValue ? (short)sampleType : 0 });
        return items;
    }
    #endregion
}