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

using YBEE.EQM.Core;

namespace YBEE.EQM.Application;

/// <summary>
/// TQES报表服务
/// </summary>
/// <param name="tqesSqlRep"></param>
/// <param name="examPlanRep"></param>
/// <param name="exportExcelService"></param>
public class ExamReportingTqesService(ISqlRepository<TqesDbContextLocator> tqesSqlRep, IRepository<ExamPlan> examPlanRep, IExportExcelService exportExcelService) : IExamReportingTqesService, ITransient
{
    /// <summary>
    /// 根据监测计划ID导出全区优势薄弱
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task<(string, byte[])> ExportTotalAdvWeak(int examPlanId)
    {
        var examPlan = examPlanRep.Include(t => t.Semester).FirstOrDefault(t => t.Id == examPlanId) ?? throw Oops.Oh(ErrorCode.E2001, "监测计划");

        // 临时存放目录
        string fileRoot = Path.Combine(FileUtil.GetTempFileRoot(), $"{Guid.NewGuid()}");
        Directory.CreateDirectory(fileRoot);
        string filePath = Path.Combine(fileRoot, $"{examPlan.Name}-优势薄弱");
        Directory.CreateDirectory(filePath);
        try
        {
            var semester = examPlan.Semester;
            // 存储过程参数
            var p = new
            {
                startYear = semester.BeginYear,
                endYear = semester.EndYear,
                semesterTypeID = (int)semester.SemesterType,
                schoolTypeID = (int)examPlan.EducationStage - 1
            };

            #region 优势薄弱
            // 优势薄弱
            var items = await tqesSqlRep.SqlQueriesAsync<ExamReportingTqesAdvWeakDto>("EXECUTE [ir].[USP_RS_PJS_School_AdvWeak] @startYear, @endYear, @semesterTypeID, @schoolTypeID", p);
            // 定义EXCEL列
            List<ExportExcelColDto<ExamReportingTqesAdvWeakDto>> cols1 =
            [
                new() { Name = "序号", Width = 6, GetCellValue = (r) => r.RowNum },
                new() { Name = "城乡类别", Width = 10, GetCellValue = (r) => r.UrbanRuralTypeName },
                new() { Name = "学校名称", Width = 20, GetCellValue = (r) => r.SchoolName, Align = ExportExcelCellAlign.LEFT },
                new() { Name = "优势学科", Width = 38, GetCellValue = (r) => r.AdvCourseName, Align = ExportExcelCellAlign.LEFT, WrapText = true },
                new() { Name = "数量", Width = 6, GetCellValue = (r) => r.AdvCourseCount },
                new() { Name = "优势班级", Width = 55, GetCellValue = (r) => r.AdvClassCourseName, Align = ExportExcelCellAlign.LEFT, WrapText = true },
                new() { Name = "数量", Width = 6, GetCellValue = (r) => r.AdvClassCourseCount },
                new() { Name = "薄弱学科", Width = 38, GetCellValue = (r) => r.WeakCourseName, Align = ExportExcelCellAlign.LEFT, WrapText = true },
                new() { Name = "数量", Width = 6, GetCellValue = (r) => r.WeakCourseCount },
                new() { Name = "薄弱班级", Width = 55, GetCellValue = (r) => r.WeakClassCourseName, Align = ExportExcelCellAlign.LEFT, WrapText = true },
                new() { Name = "数量", Width = 6, GetCellValue = (r) => r.WeakClassCourseCount },
            ];

            var e1 = exportExcelService.ExportExcel(new ExportExcelDto<ExamReportingTqesAdvWeakDto>()
            {
                IsXlsx = true,
                Title = $"{examPlan.FullName}优势薄弱学科",
                Columns = cols1,
                Items = items,
                IncludeExportTime = false,
                NotSetRowHeight = true,
            });
            await File.WriteAllBytesAsync(Path.Combine(filePath, $"{examPlan.EducationStage.GetDescription()}-优势薄弱学科.xlsx"), e1);
            #endregion

            #region 优势薄弱（近两期对比）
            // 优势薄弱（近两期对比）
            var citems = await tqesSqlRep.SqlQueriesAsync<ExamReportingTqesAdvWeakCompareDto>("EXECUTE [ir].[USP_RS_PJS_School_DE_AdvWeak] @startYear, @endYear, @semesterTypeID, @schoolTypeID", p);
            // 定义EXCEL列
            List<ExportExcelColDto<ExamReportingTqesAdvWeakCompareDto>> cols2 =
            [
                new() { Name = "序号", Width = 6, GetCellValue = (r) => r.RowNum },
                new() { Name = "城乡类别", Width = 10, GetCellValue = (r) => r.UrbanRuralTypeName },
                new() { Name = "学校名称", Width = 20, GetCellValue = (r) => r.SchoolName, Align = ExportExcelCellAlign.LEFT },
                new() { Name = "年级学科获得改进", Width = 38, GetCellValue = (r) => r.GradeImpr, Align = ExportExcelCellAlign.LEFT, WrapText = true },
                new() { Name = "年级学科继续关注", Width = 38, GetCellValue = (r) => r.GradeAttn, Align = ExportExcelCellAlign.LEFT, WrapText = true },
                new() { Name = "班级学科获得改进", Width = 55, GetCellValue = (r) => r.ClassImpr, Align = ExportExcelCellAlign.LEFT, WrapText = true },
                new() { Name = "班级学科继续关注", Width = 55, GetCellValue = (r) => r.ClassAttn, Align = ExportExcelCellAlign.LEFT, WrapText = true },
            ];

            var e2 = exportExcelService.ExportExcel(new ExportExcelDto<ExamReportingTqesAdvWeakCompareDto>()
            {
                IsXlsx = true,
                Title = $"{examPlan.FullName}优势薄弱学科（最近两期对比）",
                Columns = cols2,
                Items = citems,
                IncludeExportTime = false,
                NotSetRowHeight = true,
            });
            await File.WriteAllBytesAsync(Path.Combine(filePath, $"{examPlan.EducationStage.GetDescription()}-优势薄弱学科（最近两期对比）.xlsx"), e2);
            #endregion

            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);
        }
    }
}