YBEE.EQM/YBEE.EQM.Application/Exam/ExamAbsentReplace/Services/ExamAbsentReplaceCenterService.cs

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

namespace YBEE.EQM.Application;

/// <summary>
/// 缺测替补管理服务（用于中心管理）
/// </summary>
public class ExamAbsentReplaceCenterService(
    IRepository<ExamAbsentReplace> rep,
    IExportExcelService exportExcelService,
    ICourseService courseService,
    IExamPlanService examPlanService
) : IExamAbsentReplaceCenterService, ITransient
{
    /// <summary>
    /// 分页查询监测缺测替补列表
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task<PageResult<ExamAbsentReplaceFullOutput>> QueryPageList(ExamAbsentReplacePageInput input)
    {
        var query = GetQueryBase(input);
        var ret = await query.OrderBy(t => t.SysOrgId).ThenBy(t => t.GradeId).ThenBy(t => t.ClassNumber).ThenBy(t => t.Id)
                             .ProjectToType<ExamAbsentReplaceFullOutput>()
                             .ToADPagedListAsync(input.PageIndex, input.PageSize);
        return ret;
    }
    /// <summary>
    /// 获取状态数量
    /// </summary>
    /// <returns></returns>
    public async Task<List<StatusCount>> QueryStatusCount(ExamAbsentReplacePageInput input)
    {
        var query = GetQueryBase(input);
        if (query == null)
        {
            return [];
        }
        var counts = await query.GroupBy(t => t.Status).Select(t => new StatusCount { Status = (int)t.Key, Count = t.Count() }).ToListAsync();
        return counts;
    }
    /// <summary>
    /// 导出数据表格（简表)
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task<(string fileName, byte[] fileBytes)> ExportSimple(ExamAbsentReplacePageInput input)
    {
        var examPlan = await examPlanService.GetById(input.ExamPlanId);

        var query = GetQueryBase(input);
        var items = await query.OrderBy(t => t.SysOrgId).ThenBy(t => t.GradeId).ThenBy(t => t.ClassNumber).ThenBy(t => t.Id)
                               .ProjectToType<ExamAbsentReplaceFullOutput>()
                               .ToListAsync();

        // 科目列表
        var courses = await courseService.GetAllLiteList();
        // 科目字典
        var courseDicts = courses.ToDictionary(t => t.Name);

        XSSFWorkbook wb = new();
        ISheet sheet = wb.CreateSheet("缺测替补名单");
        sheet.DisplayGridlines = false;

        var cellStyle = exportExcelService.GetCellStyle(wb);

        // 行索引号
        int rowNum = 0;

        #region 列头
        IRow headerRow = sheet.CreateRow(rowNum++);
        int ci = 0;
        exportExcelService.AddCell("学校", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 20);
        exportExcelService.AddCell("年级", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 8);
        exportExcelService.AddCell("班级", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 8);
        exportExcelService.AddCell("缺测姓名", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 12);
        exportExcelService.AddCell("缺测考号", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 16);
        exportExcelService.AddCell("有无替补", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
        exportExcelService.AddCell("替补姓名", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 12);
        exportExcelService.AddCell("替补考号", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 16);
        exportExcelService.AddCell("缺测替补科目", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 50);
        exportExcelService.AddCell("状态", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 8);
        sheet.CreateFreezePane(0, rowNum);
        #endregion

        #region 数据
        foreach (var item in items)
        {
            IRow row = sheet.CreateRow(rowNum++);
            int rci = 0;
            exportExcelService.AddCell(item.SysOrg.Name, row, rci++, cellStyle.LeftCellStyle);
            exportExcelService.AddCell(item.ExamGrade.Grade.Name, row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.ClassNumber, row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.AbsentName, row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.AbsentExamNumber, row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.IsReplaced ? "是" : "否", row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.ReplaceName, row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.ReplaceExamNumber, row, rci++, cellStyle.CenterCellStyle);
            var cs = JSON.Deserialize<List<CourseMiniOutput>>(item.AbsentCourses);
            var cns = string.Join("、", cs.Select(t => t.Name));
            exportExcelService.AddCell(cns, row, rci++, cellStyle.LeftCellStyle);
            exportExcelService.AddCell(item.Status.GetDescription(), row, rci++, cellStyle.CenterCellStyle);
        }
        #endregion

        MemoryStream ms = new();
        wb.Write(ms, false);
        ms.Flush();

        return ($"{examPlan.Name}-缺测替补名单.xlsx", ms.ToArray());
    }
    /// <summary>
    /// 导出数据表格（完整)
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task<(string fileName, byte[] fileBytes)> ExportFull(ExamAbsentReplacePageInput input)
    {
        var examPlan = await examPlanService.GetById(input.ExamPlanId);

        var query = GetQueryBase(input);
        var items = await query.OrderBy(t => t.SysOrgId).ThenBy(t => t.GradeId).ThenBy(t => t.ClassNumber).ThenBy(t => t.Id)
                               .ProjectToType<ExamAbsentReplaceFullOutput>()
                               .ToListAsync();

        // 科目列表
        var courses = await courseService.GetAllLiteList();
        // 科目字典
        var courseDicts = courses.ToDictionary(t => t.Name);

        XSSFWorkbook wb = new();
        ISheet sheet = wb.CreateSheet("缺测替补名单");
        sheet.DisplayGridlines = false;

        var cellStyle = exportExcelService.GetCellStyle(wb);

        // 行索引号
        int rowNum = 0;

        #region 列头
        IRow headerRow = sheet.CreateRow(rowNum++);
        int ci = 0;
        exportExcelService.AddCell("学校", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 20);
        exportExcelService.AddCell("年级", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 8);
        exportExcelService.AddCell("班级", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 8);
        exportExcelService.AddCell("缺测姓名", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 12);
        exportExcelService.AddCell("缺测考号", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 16);
        exportExcelService.AddCell("有无替补", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
        exportExcelService.AddCell("替补姓名", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 12);
        exportExcelService.AddCell("替补考号", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 16);
        exportExcelService.AddCell("缺测替补科目", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 50);
        exportExcelService.AddCell("状态", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 8);
        exportExcelService.AddCell("缺测原因", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 40);
        exportExcelService.AddCell("家长电话", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 12);
        sheet.CreateFreezePane(0, rowNum);
        #endregion

        #region 数据
        foreach (var item in items)
        {
            IRow row = sheet.CreateRow(rowNum++);
            int rci = 0;
            exportExcelService.AddCell(item.SysOrg.Name, row, rci++, cellStyle.LeftCellStyle);
            exportExcelService.AddCell(item.ExamGrade.Grade.Name, row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.ClassNumber, row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.AbsentName, row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.AbsentExamNumber, row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.IsReplaced ? "是" : "否", row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.ReplaceName, row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.ReplaceExamNumber, row, rci++, cellStyle.CenterCellStyle);
            var cs = JSON.Deserialize<List<CourseMiniOutput>>(item.AbsentCourses);
            var cns = string.Join("、", cs.Select(t => t.Name));
            exportExcelService.AddCell(cns, row, rci++, cellStyle.LeftCellStyle);
            exportExcelService.AddCell(item.Status.GetDescription(), row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.AbsentReason, row, rci++, cellStyle.LeftWrapCellStyle);
            exportExcelService.AddCell(item.PatriarchTel, row, rci++, cellStyle.CenterCellStyle);
        }
        #endregion

        MemoryStream ms = new();
        wb.Write(ms, false);
        ms.Flush();

        return ($"{examPlan.Name}-缺测替补名单.xlsx", ms.ToArray());
    }

    #region 私有方法
    /// <summary>
    /// 构建查询
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    private IQueryable<ExamAbsentReplace> GetQueryBase(ExamAbsentReplacePageInput input)
    {
        var absentName = !string.IsNullOrEmpty(input.AbsentName?.Trim());
        var absentExamNumber = !string.IsNullOrEmpty(input.AbsentExamNumber?.Trim());
        var absentReason = !string.IsNullOrEmpty(input.AbsentReason?.Trim());
        var replaceName = !string.IsNullOrEmpty(input.ReplaceName?.Trim());
        var replaceExamNumber = !string.IsNullOrEmpty(input.ReplaceExamNumber?.Trim());
        var patriarchTel = !string.IsNullOrEmpty(input.PatriarchTel?.Trim());
        var remark = !string.IsNullOrEmpty(input.Remark?.Trim());

        var query = rep.DetachedEntities.Where(t => t.ExamPlanId == input.ExamPlanId)
                                        .Where((absentName, u => EF.Functions.Like(u.AbsentName, $"%{input.AbsentName.Trim()}%")))
                                        .Where((absentExamNumber, u => EF.Functions.Like(u.AbsentExamNumber, $"%{input.AbsentExamNumber.Trim()}%")))
                                        .Where((absentReason, u => EF.Functions.Like(u.AbsentReason, $"%{input.AbsentReason.Trim()}%")))
                                        .Where((replaceName, u => EF.Functions.Like(u.ReplaceName, $"%{input.ReplaceName.Trim()}%")))
                                        .Where((replaceExamNumber, u => EF.Functions.Like(u.ReplaceExamNumber, $"%{input.ReplaceExamNumber.Trim()}%")))
                                        .Where((patriarchTel, u => EF.Functions.Like(u.PatriarchTel, $"%{input.PatriarchTel.Trim()}%")))
                                        .Where((remark, u => EF.Functions.Like(u.Remark, $"%{input.Remark.Trim()}%")))
                                        .Where((input.AbentCourseId.HasValue, u => EF.Functions.Like(u.AbsentCourses, $"%{input.AbentCourseId}%")))
                                        .Where(input.Status.HasValue, t => t.Status == input.Status)
                                        .Where(input.IsReplaced.HasValue, t => t.IsReplaced == input.IsReplaced)
                                        .Where(input.GradeId.HasValue, t => t.GradeId == input.GradeId)
                                        .Where(input.ClassNumber.HasValue, t => t.ClassNumber == input.ClassNumber)
                                        .Where(input.SysOrgId.HasValue, t => t.SysOrgId == input.SysOrgId)
                                        .Where(input.SysOrgBranchId.HasValue, t => t.SchoolClass.SysOrgBranchId == input.SysOrgBranchId);
        return query;
    }
    #endregion
}