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(
    IRepository rep,
    ISqlRepository sqlRep,
    IExamScoreRangeService examScoreRangeService,
    IExamGradeService examGradeService,
    IExamOrgService examOrgService,
    IExamCourseService examCourseService,
    IExportExcelService exportExcelService) : IExamReportingAvgRangeService, ITransient
{
    #region 导出
    /// <summary>
    /// 导出全区分数段统计表
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task<(string, byte[])> ExportTotal(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)
            {
                for (int i = 0; i < 2; i++)
                {
                    string fn = i == 0 ? "区校合并" : "区级监测";
                    string titlePrefix = $"{examPlan.Name}{fn}{examGrade.Grade.Name}";

                    XSSFWorkbook wb = new();
                    var cellStyles = exportExcelService.GetCellStyle(wb);

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

                    await ExportTotalTotalRange(titlePrefix, examPlanId, sampleType, examGrade.GradeId, scoreRanges.Where(t => t.Type == examGrade.ExamScoreRangeType).ToList(), wb, cellStyles);
                    await ExportTotalCourseRange(titlePrefix, examPlanId, sampleType, examGrade.GradeId, scoreRanges, examCourses.Where(t => t.GradeId == examGrade.GradeId).ToList(), wb, cellStyles);
                    await ExportTotalCourseAvgScore(titlePrefix, examPlanId, sampleType, examGrade.GradeId, examCourses.Where(t => t.GradeId == examGrade.GradeId).ToList(), wb, cellStyles);

                    MemoryStream ms = new();
                    wb.Write(ms, false);
                    ms.Flush();
                    
                    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="examPlanId"></param>
    /// <returns></returns>
    /// <exception cref="Exception"></exception>
    public async Task<(string, byte[])> ExportOrg(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);
        var examOrgs = (await examOrgService.GetListByExamPlanId(examPlanId)).Where(t => t.IsRequiredExam).ToList();

        // 临时存放目录
        string fileRoot = Path.Combine(FileUtil.GetTempFileRoot(), $"{Guid.NewGuid()}");
        Directory.CreateDirectory(fileRoot);
        string filePath = Path.Combine(fileRoot, $"{examPlan.Name}-学校报告-平均分及分数段统计");
        Directory.CreateDirectory(filePath);
        // 学段目录
        string eduStageFilePath = Path.Combine(filePath, examPlan.EducationStage.GetDescription());
        Directory.CreateDirectory(eduStageFilePath);
        try
        {
            // 年级
            foreach (var examGrade in examGrades)
            {
                var ecourses = examCourses.Where(t => t.GradeId == examGrade.GradeId).OrderBy(t => t.CourseId).ToList();
                for (int i = 0; i < 2; i++)
                {
                    string fn = i == 0 ? "区校合并" : "区级监测";
                    string titlePrefix = $"{fn}{examGrade.Grade.Name}";

                    ExamSampleType? sampleType = i == 1 ? ExamSampleType.DISTRICT : null;
                    // 平均分
                    var listAvgScore = await GetAvgScoreList(examPlanId, examGrade.GradeId, sampleType);
                    // 总分分数段
                    var listTotalScoreRange = await GetTotalRangeList(examPlanId, examGrade.GradeId, sampleType);
                    // 学科分数段
                    var listCourseScoreRange = await GetCourseRangeList(examPlanId, examGrade.GradeId, sampleType);

                    // 学校
                    foreach (var org in examOrgs)
                    {
                        // 学校目录
                        string orgFilePath = Path.Combine(eduStageFilePath, org.SysOrg.FullName, "学校报告", "1-6 平均分及分数段统计");
                        if (!Directory.Exists(orgFilePath))
                        {
                            Directory.CreateDirectory(orgFilePath);
                        }

                        XSSFWorkbook wb = new();
                        var cellStyles = exportExcelService.GetCellStyle(wb);

                        // 平均分
                        var orgAvgScoreList = listAvgScore.Where(t => t.SysOrgId == org.SysOrgId || t.DataScopeType > 1 || (t.DataScopeType == 1 && t.UrbanRuralType == UrbanRuralType.NONE))
                                                          .OrderBy(t => t.DataScopeType).ThenBy(t => t.SysOrgId).ThenBy(t => t.UrbanRuralType).ThenBy(t => t.CourseId)
                                                          .ToList();
                        ExportOrgCourseAvgScore(titlePrefix, org.SysOrg.Name, orgAvgScoreList, ecourses, wb, cellStyles);

                        // 总分分数段
                        var orgTotalScoreRangeList = listTotalScoreRange.Where(t => t.SysOrgId == org.SysOrgId).OrderByDescending(t => t.ExamScoreRangeId).ToList();
                        ExportOrgScoreRange(titlePrefix, org.SysOrg.Name, "总分", orgTotalScoreRangeList, scoreRanges.Where(t => t.Type == examGrade.ExamScoreRangeType).ToList(), wb, cellStyles);

                        // 学科分数段
                        foreach (var ecourse in ecourses)
                        {
                            var orgCourseScoreRangeList = listCourseScoreRange.Where(t => t.SysOrgId == org.SysOrgId && t.CourseId == ecourse.CourseId).ToList();
                            ExportOrgScoreRange(titlePrefix, org.SysOrg.Name, ecourse.Course.Name, orgCourseScoreRangeList, scoreRanges.Where(t => t.Type == ecourse.ExamScoreRangeType).ToList(), wb, cellStyles);
                        }

                        MemoryStream ms = new();
                        wb.Write(ms, false);
                        ms.Flush();
                        await File.WriteAllBytesAsync(Path.Combine(orgFilePath, $"{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);
        }
    }
    #endregion

    #region 导出全区表格私有方法
    /// <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 ExportTotalTotalRange(string titlePrefix, int examPlanId, ExamSampleType? sampleType, short gradeId, List<ExamScoreRangeOutput> ranges, XSSFWorkbook 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 ExportTotalCourseRange(string titlePrefix, int examPlanId, ExamSampleType? sampleType, short gradeId, List<ExamScoreRangeOutput> ranges, List<ExamCourseOutput> examCourses, XSSFWorkbook 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.Count == 0)
            {
                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 ExportTotalCourseAvgScore(string titlePrefix, int examPlanId, ExamSampleType? sampleType, short gradeId, List<ExamCourseOutput> examCourses, XSSFWorkbook 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);
        // 过滤掉最高分
        list = list.Where(t => !(t.DataScopeType == 1 && t.UrbanRuralType == UrbanRuralType.NONE)).ToList();
        // 城乡分组
        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();
            var orgGroup = urg.GroupBy(t => t.SysOrgId).OrderBy(t => t.FirstOrDefault()?.DataScopeType).ThenBy(t => t.FirstOrDefault(c => c.CourseId == 0)?.OrderInTotal).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 || sr.AvgScoreDiff == 0) ? 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
    }
    #endregion

    #region 导出学校表格私有方法
    /// <summary>
    /// 导出学校学科平均分统计表
    /// </summary>
    /// <param name="titlePrefix"></param>
    /// <param name="orgName"></param>
    /// <param name="list"></param>
    /// <param name="examCourses"></param>
    /// <param name="wb"></param>
    /// <param name="cellStyles"></param>
    /// <returns></returns>
    private void ExportOrgCourseAvgScore(string titlePrefix, string orgName, List<ExamScoreAvgExportDto> list, List<ExamCourseOutput> examCourses, XSSFWorkbook 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;

        var cellStyleNumberP2Blod = wb.CreateCellStyle();
        cellStyleNumberP2Blod.CloneStyleFrom(cellStyles.NumberCellStyleP2);
        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 = 60;
        int ci = 0;
        exportExcelService.AddCell($"{orgName}{titlePrefix}各学科均分统计", 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);
        }
        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("实考\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);
            sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, ci - 2, ci - 1));
        }

        IRow headerRow2 = sheet.CreateRow(rowNum++);
        headerRow2.HeightInPoints = 30;
        ci = 0;
        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("与最高\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));
        #endregion

        #region 数据
        // 城乡分组
        var urGroup = list.GroupBy(t => t.UrbanRuralType).OrderBy(t => t.Key).ToList();
        foreach (var urg in urGroup)
        {
            // 机构
            var orgGroup = urg.GroupBy(t => t.SysOrgId).OrderBy(t => t.FirstOrDefault()?.DataScopeType).ThenBy(t => t.Key).ThenBy(t => t.FirstOrDefault().UrbanRuralType).ToList();
            foreach (var orgg in orgGroup)
            {
                IRow row = sheet.CreateRow(rowNum++);
                row.HeightInPoints = 20;
                ci = 0;
                var forg = orgg.FirstOrDefault(t => t.CourseId == 0);
                var isMax = forg.DataScopeType == 1 && forg.UrbanRuralType == UrbanRuralType.NONE;
                if (forg.DataScopeType > 1)
                {
                    var txt = forg?.SysOrgName ?? "";
                    if (forg?.UrbanRuralType != UrbanRuralType.NONE)
                    {
                        txt = $"{forg?.UrbanRuralType?.GetDescription()}学校";
                    }
                    exportExcelService.AddCell(txt, row, ci++, cellStyles.LeftCellStyle);
                }
                else
                {
                    exportExcelService.AddCell(forg?.SysOrgName, row, ci++, isMax ? cellStyles.ColumnHeaderStyle : cellStyles.LeftCellStyle);
                }
                exportExcelService.AddCell(isMax ? null : forg?.TotalCount, row, ci++, isMax ? cellStyles.ColumnHeaderStyle : cellStyles.CenterCellStyle);
                foreach (var r in courses)
                {
                    var sr = orgg.FirstOrDefault(t => t.CourseId == r.Id);
                    exportExcelService.AddCell(sr?.AvgScore, row, ci++, isMax ? cellStyleNumberP2Blod : cellStyles.NumberCellStyleP2);
                    exportExcelService.AddCell((isMax || forg.DataScopeType > 1) ? null : sr?.AvgScoreDiff, row, ci++, isMax ? cellStyleNumberP2Blod : cellStyles.NumberCellStyleP2);
                }
            }
        }
        #endregion
    }
    /// <summary>
    /// 导出学校学科分数段统计图表
    /// </summary>
    /// <param name="titlePrefix"></param>
    /// <param name="orgName"></param>
    /// <param name="courseName"></param>
    /// <param name="list"></param>
    /// <param name="ranges"></param>
    /// <param name="wb"></param>
    /// <param name="cellStyles"></param>
    private void ExportOrgScoreRange(string titlePrefix, string orgName, string courseName, List<ExamScoreRangeExportDto> list, List<ExamScoreRangeOutput> ranges, XSSFWorkbook wb, ExportExcelCellStyle cellStyles)
    {
        ISheet sheet = wb.CreateSheet(courseName);
        sheet.DisplayGridlines = false;

        int rowNum = 0;

        #region 标题
        IRow titleRow = sheet.CreateRow(rowNum++);
        titleRow.HeightInPoints = 60;
        int ci = 0;
        exportExcelService.AddCell($"{orgName}{titlePrefix}{courseName}分数段统计", titleRow, ci++, cellStyles.TitleStyle);
        for (; ci < 15; ci++)
        {
            exportExcelService.AddCell("", titleRow, ci++, cellStyles.TitleStyle);
        }
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, ci - 1));
        #endregion

        #region 列头
        IRow headerRow = sheet.CreateRow(rowNum++);
        ci = 0;
        exportExcelService.AddCell("分数段", headerRow, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 14);
        exportExcelService.AddCell("本段人数", headerRow, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 10);
        exportExcelService.AddCell("累计人数", headerRow, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 10);
        #endregion

        #region 数据
        decimal scount = 0;
        foreach (var r in ranges)
        {
            var sr = list.FirstOrDefault(t => t.ExamScoreRangeId == r.Id);
            IRow row = sheet.CreateRow(rowNum++);
            int rci = 0;
            exportExcelService.AddCell(r.NickName, row, rci++, cellStyles.CenterCellStyle);
            scount += sr?.RangeCount ?? 0;
            exportExcelService.AddCell(sr?.RangeCount ?? 0, row, rci++, cellStyles.CenterCellStyle);
            exportExcelService.AddCell(scount, row, rci++, cellStyles.CenterCellStyle);
        }
        #endregion

        #region 图表
        const int CHART_ROW_COUNT = 18;
        XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
        int startRow = 1;
        int endRow = startRow + CHART_ROW_COUNT;
        XSSFClientAnchor anchor = (XSSFClientAnchor)drawing.CreateAnchor(0, 0, 0, 0, ci + 1, startRow, ci + 12, endRow);
        exportExcelService.CreateBarChart(sheet, drawing, anchor, 2, ranges.Count + 1, 1, $"{titlePrefix}{courseName}分数段统计", orgName, "人数", "分数段");
        #endregion
    }
    #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, T3.`name` AS exam_score_range_name, T3.nick_name AS exam_score_range_nick_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.is_excluded = 0 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
JOIN exam_score_range AS T3 ON T1.exam_score_range_id = T3.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, T3.`name` AS exam_score_range_name, T3.nick_name AS exam_score_range_nick_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.is_excluded = 0 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
JOIN exam_score_range AS T3 ON T1.exam_score_range_id = T3.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<ExamScoreRangeExportDto>> GetCourseRangeList(int examPlanId, short gradeId, ExamSampleType? sampleType)
    {
        var items = await sqlRep.SqlQueriesAsync<ExamScoreRangeExportDto>($@"
SELECT T2.urban_rural_type, T2.`name` AS sys_org_name, T3.`name` AS exam_score_range_name, T3.nick_name AS exam_score_range_nick_name, T1.*
FROM
(
    SELECT T1.sys_org_id, T1.course_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.course_id, T1.exam_score_range_id, T1.exam_student_id, COUNT(T1.exam_student_id) OVER (PARTITION BY T1.sys_org_id, T1.course_id) AS total_count
        FROM exam_score AS T1
        WHERE exam_plan_id = @examPlanId AND T1.grade_id = @gradeId AND T1.is_excluded = 0 AND T1.score > 0 AND (T1.exam_sample_type = @examSampleType OR @examSampleType = 0)
    ) AS T1
    GROUP BY T1.sys_org_id, T1.course_id, T1.exam_score_range_id
) AS T1
JOIN sys_org AS T2 ON T1.sys_org_id = T2.id
JOIN exam_score_range AS T3 ON T1.exam_score_range_id = T3.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="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,
        T1.avg_score - T3.score_max 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 is_excluded = 0 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 is_excluded = 0 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,
        T1.avg_score - T3.score_max 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 is_excluded = 0 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 is_excluded = 0 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,
        T1.avg_score - T3.score_max 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.is_excluded = 0 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 is_excluded = 0 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,
        T1.avg_score - T3.score_max 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.is_excluded = 0 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 is_excluded = 0 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,
        T1.avg_score - T3.score_max 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.is_excluded = 0 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 is_excluded = 0 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,
        T1.avg_score - T3.score_max 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.is_excluded = 0 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 is_excluded = 0 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 1 AS data_scope_type, 
        0 AS urban_rural_type, 
        '全区最高分' AS sys_org_name, 
        99999999 AS sys_org_id, 
        0 AS course_id, 
        NULL AS total_count,
        MAX(T.avg_score) AS avg_score,
        NULL AS order_in_total,
        NULL AS order_in_same,
        NULL AS avg_score_diff,
        NULL 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 is_excluded = 0 AND score > 0 AND (exam_sample_type = @examSampleType OR @examSampleType = 0)
        GROUP BY sys_org_id
    ) AS T
    
    -- 单科最高平均分
    UNION ALL
    SELECT 1 AS data_scope_type, 
        0 AS urban_rural_type, 
        '全区最高分' AS sys_org_name, 
        99999999 AS sys_org_id, 
        T.course_id, 
        NULL AS total_count,
        MAX(T.avg_score) AS avg_score,
        NULL AS order_in_total,
        NULL AS order_in_same,
        NULL AS avg_score_diff,
        NULL 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 is_excluded = 0 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 T1
ORDER BY T1.data_scope_type, T1.urban_rural_type, T1.course_id, T1.order_in_total, T1.sys_org_id, T1.total_count
;
", new { ExamPlanId = examPlanId, GradeId = gradeId, ExamSampleType = sampleType.HasValue ? (short)sampleType : 0 });
        return items;
    }
    #endregion
}