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

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using YBEE.EQM.Core;

namespace YBEE.EQM.Application;

/// <summary>
/// 导出成绩服务
/// </summary>
/// <param name="rep"></param>
/// <param name="sqlRep"></param>
/// <param name="examGradeService"></param>
/// <param name="examCourseService"></param>
/// <param name="exportExcelService"></param>
public class ExamScoreExportService(IRepository<ExamScore> rep, ISqlRepository sqlRep, IExamGradeService examGradeService, IExamCourseService examCourseService, IExportExcelService exportExcelService) : IExamScoreExportService, ITransient
{
    /// <summary>
    /// 导出TQES输入文件
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    /// <exception cref="Exception"></exception>
    public async Task<(string, byte[])> ExportTqesFile(ExamScoreExportTqesFileInput input)
    {
        var examPlan = await rep.Change<ExamPlan>().DetachedEntities.ProjectToType<ExamPlanOutput>().FirstOrDefaultAsync(t => t.Id == input.ExamPlanId) ?? throw Oops.Oh(ErrorCode.E2001, "监测计划");
        var examGrades = await examGradeService.GetListByExamPlanId(input.ExamPlanId);
        var examCourses = await examCourseService.GetListByExamPlanId(input.ExamPlanId);

        // 临时存放目录
        string fileRoot = Path.Combine(FileUtil.GetTempFileRoot(), $"{Guid.NewGuid()}");
        Directory.CreateDirectory(fileRoot);
        string filePath = Path.Combine(fileRoot, $"{examPlan.Name}-TQES导入数据");
        Directory.CreateDirectory(filePath);
        try
        {
            foreach (var examGrade in examGrades)
            {
                for (int i = 0; i < 2; i++)
                {
                    HSSFWorkbook wb = new();
                    var cellStyles = exportExcelService.GetCellStyle(wb);

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

                    var ecs = examCourses.Where(t => t.ExamGradeId == examGrade.Id).OrderBy(t => t.CourseId).ToList();
                    await ExportToTqesSheet(input, examGrade.GradeId, examSampleType, ecs, wb, cellStyles);

                    MemoryStream ms = new();
                    wb.Write(ms, false);
                    ms.Flush();
                    string fn = i == 0 ? "全员" : "抽测";
                    await File.WriteAllBytesAsync(Path.Combine(filePath, $"{examGrade.EducationStage.GetDescription()}-{examGrade.Grade.Name2}-监测成绩-{fn}.xls"), ms.ToArray());

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

            string outFileName = $"{examPlan.Name}-TQES导入数据-{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);
        }
    }

    #region 导出方法
    /// <summary>
    /// 导出到表格
    /// </summary>
    /// <param name="input"></param>
    /// <param name="gradeId"></param>
    /// <param name="examSampleType"></param>
    /// <param name="examCourses"></param>
    /// <param name="wb"></param>
    /// <param name="cellStyles"></param>
    /// <returns></returns>
    private async Task ExportToTqesSheet(ExamScoreExportTqesFileInput input, short gradeId, ExamSampleType? examSampleType, List<ExamCourseOutput> examCourses, HSSFWorkbook wb, ExportExcelCellStyle cellStyles)
    {
        ISheet sheet = wb.CreateSheet("监测成绩");
        sheet.DisplayGridlines = false;
        sheet.CreateFreezePane(0, 1);

        int rowNum = 0;

        #region 列头
        IRow headerRow = sheet.CreateRow(rowNum++);
        int ci = 0;
        exportExcelService.AddCell("学校", headerRow, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 20);
        exportExcelService.AddCell("学校ID", headerRow, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 10);
        exportExcelService.AddCell("考号", headerRow, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 13);
        exportExcelService.AddCell("年级", headerRow, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 8);
        exportExcelService.AddCell("班级", headerRow, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 8);
        exportExcelService.AddCell("身份证号码", headerRow, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 20);
        exportExcelService.AddCell("姓名", headerRow, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 18);
        foreach (var c in examCourses)
        {
            exportExcelService.AddCell(c.Course.Name, headerRow, ci++, cellStyles.ColumnFillHeaderStyle, sheet, 8);
        }
        #endregion

        #region 数据
        var totalList = await GetTotalList(input.ExamPlanId, gradeId, examSampleType);
        var scoreList = await GetScoreList(input.ExamPlanId, gradeId, examSampleType);
        foreach (var stu in totalList)
        {
            IRow row = sheet.CreateRow(rowNum++);
            ci = 0;
            exportExcelService.AddCell(stu.SysOrgName, row, ci++, cellStyles.LeftCellStyle);
            exportExcelService.AddCell(stu.TqesId, row, ci++, cellStyles.CenterCellStyle);
            exportExcelService.AddCell(stu.ExamNumber, row, ci++, cellStyles.CenterCellStyle);
            exportExcelService.AddCell(stu.GradeNumber, row, ci++, cellStyles.CenterCellStyle);
            exportExcelService.AddCell(stu.ClassNumber, row, ci++, cellStyles.CenterCellStyle);
            exportExcelService.AddCell(stu.IdNumber, row, ci++, cellStyles.CenterCellStyle);
            exportExcelService.AddCell(stu.Name, row, ci++, cellStyles.CenterCellStyle);
            var scores = scoreList.Where(t => t.ExamStudentId == stu.ExamStudentId).GroupBy(d => d.CourseId).Select(d => new ExamScoreExportTqesDto
            {
                CourseId = d.Key,
                Score = d.Max(s => s.Score),
                IsSpecial = d.FirstOrDefault()?.IsSpecial ?? false,
                IsAbsent = d.FirstOrDefault()?.IsAbsent ?? false,
            }).ToDictionary(d => d.CourseId);
            foreach (var c in examCourses)
            {
                if (stu.IsSpecial)
                {
                    exportExcelService.AddCell("特殊", row, ci++, cellStyles.CenterCellStyle);
                    continue;
                }
                if (scores.TryGetValue(c.CourseId, out ExamScoreExportTqesDto score))
                {
                    if (score.IsSpecial)
                    {
                        exportExcelService.AddCell("特殊", row, ci++, cellStyles.CenterCellStyle);
                    }
                    else if ((score.IsAbsent || input.IsZeroToAbsent) && score.Score == 0)
                    {
                        exportExcelService.AddCell("缺考", row, ci++, cellStyles.CenterCellStyle);
                    }
                    else
                    {
                        exportExcelService.AddCell(score.Score, row, ci++, cellStyles.CenterCellStyle);
                    }
                }
                else
                {
                    exportExcelService.AddCell(null, row, ci++, cellStyles.CenterCellStyle);
                }
            }
        }
        #endregion
    }
    #endregion

    #region 获取数据
    /// <summary>
    /// 获取学生列表
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <param name="gradeId"></param>
    /// <param name="examSampleType"></param>
    /// <returns></returns>
    private async Task<List<ExamScoreTotalExportTqesDto>> GetTotalList(int examPlanId, short gradeId, ExamSampleType? examSampleType)
    {
        var items = await sqlRep.SqlQueriesAsync<ExamScoreTotalExportTqesDto>($@"
SELECT 
    T1.sys_org_id, 
    T2.`name` AS sys_org_name, 
    T2.tqes_id, 
    T1.exam_number, 
    T3.grade_number, 
    T1.class_number, 
    CASE WHEN T4.id_number = '' OR T4.id_number IS NULL THEN RIGHT(CONCAT('00000000', T1.exam_plan_id, '-', T1.exam_number), 18) ELSE T4.id_number END AS id_number, 
    T4.`name`, 
    T1.exam_student_id, 
    T1.exam_sample_type, 
    T1.is_special
FROM exam_score_total AS T1
JOIN sys_org AS T2 ON T1.sys_org_id = T2.id
JOIN base_grade AS T3 ON T1.grade_id = T3.id
JOIN exam_student AS T4 ON T1.exam_student_id = T4.id
WHERE T1.exam_plan_id = @examPlanId AND T1.grade_id = @gradeId AND (T1.exam_sample_type = @examSampleType OR @examSampleType = 0)
ORDER BY T1.sys_org_id, T1.grade_id, T1.class_number
;", new { ExamPlanId = examPlanId, GradeId = gradeId, ExamSampleType = examSampleType.HasValue ? (short)examSampleType : 0 });

        return items;
    }
    /// <summary>
    /// 获取成绩列表
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <param name="gradeId"></param>
    /// <param name="examSampleType"></param>
    /// <returns></returns>
    private async Task<List<ExamScoreExportTqesDto>> GetScoreList(int examPlanId, short gradeId, ExamSampleType? examSampleType)
    {
        var items = await sqlRep.SqlQueriesAsync<ExamScoreExportTqesDto>($@"
SELECT T1.exam_student_id, T1.course_id, T1.is_absent, T1.is_special, T1.score 
FROM exam_score AS T1
WHERE T1.exam_plan_id = @examPlanId AND T1.grade_id = @gradeId AND (T1.exam_sample_type = @examSampleType OR @examSampleType = 0)
;", new { ExamPlanId = examPlanId, GradeId = gradeId, ExamSampleType = examSampleType.HasValue ? (short)examSampleType : 0 });

        return items;
    }
    #endregion
}