YBEE.EQM/YBEE.EQM.Application/Exam/ExamSpecialStudent/Services/ExamSpecialStudentCenterService.cs

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

namespace YBEE.EQM.Application;

/// <summary>
/// 特殊学生管理服务（用于中心管理）
/// </summary>
public class ExamSpecialStudentCenterService(
    IRepository<ExamSpecialStudent> rep,
    IExamPlanService examPlanService,
    IExportExcelService exportExcelService
) : IExamSpecialStudentCenterService, ITransient
{
    /// <summary>
    /// 分页查询列表
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task<PageResult<ExamSpecialStudentFullOutput>> QueryPageList(ExamSpecialStudentPageInput 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<ExamSpecialStudentFullOutput>()
                             .ToADPagedListAsync(input.PageIndex, input.PageSize);
        return ret;
    }
    /// <summary>
    /// 获取状态数量
    /// </summary>
    /// <returns></returns>
    public async Task<List<StatusCount>> QueryStatusCount(ExamSpecialStudentPageInput 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(ExamSpecialStudentPageInput 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<ExamSpecialStudentFullOutput>()
                               .ToListAsync();

        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, 12);
        exportExcelService.AddCell("证件号码", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 18);
        exportExcelService.AddCell("状态", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 8);
        exportExcelService.AddCell("往期认定", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
        exportExcelService.AddCell("前测科数", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
        exportExcelService.AddCell("前测成绩", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
        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.Name, row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.CertificateType.GetDescription(), row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.IdNumber, row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.Status.GetDescription(), row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.IsPreIdentified ? "是" : "否", row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.PreTotalCourse, row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.PreTotalScore, 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(ExamSpecialStudentPageInput 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<ExamSpecialStudentFullOutput>()
                               .ToListAsync();

        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, 12);
        exportExcelService.AddCell("证件号码", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 18);
        exportExcelService.AddCell("状态", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 8);
        exportExcelService.AddCell("往期认定", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
        exportExcelService.AddCell("前测科数", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
        exportExcelService.AddCell("前测成绩", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 10);
        exportExcelService.AddCell("特殊原因", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 40);
        exportExcelService.AddCell("家长电话", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 16);
        exportExcelService.AddCell("备注", headerRow, ci++, cellStyle.ColumnFillHeaderStyle, sheet, 40);

        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.Name, row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.CertificateType.GetDescription(), row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.IdNumber, row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.Status.GetDescription(), row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.IsPreIdentified ? "是" : "否", row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.PreTotalCourse, row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.PreTotalScore, row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.ApplyReason, row, rci++, cellStyle.LeftWrapCellStyle);
            exportExcelService.AddCell(item.PatriarchTel, row, rci++, cellStyle.CenterCellStyle);
            exportExcelService.AddCell(item.Remark, row, rci++, cellStyle.LeftWrapCellStyle);
        }
        #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<ExamSpecialStudent> GetQueryBase(ExamSpecialStudentPageInput input)
    {
        var name = !string.IsNullOrEmpty(input.Name?.Trim());
        var idNumber = !string.IsNullOrEmpty(input.IdNumber?.Trim());
        var applyReason = !string.IsNullOrEmpty(input.ApplyReason?.Trim());
        var patriarchTel = !string.IsNullOrEmpty(input.PatriarchTel?.Trim());
        var studentNumber = !string.IsNullOrEmpty(input.StudentNumber?.Trim());

        var query = rep.DetachedEntities.Where(t => t.ExamPlanId == input.ExamPlanId)
                                        .Where((name, u => EF.Functions.Like(u.Name, $"%{input.Name.Trim()}%")))
                                        .Where((idNumber, u => EF.Functions.Like(u.IdNumber, $"%{input.IdNumber.Trim()}%")))
                                        .Where((studentNumber, u => EF.Functions.Like(u.StudentNumber, $"%{input.StudentNumber.Trim()}%")))
                                        .Where((applyReason, u => EF.Functions.Like(u.ApplyReason, $"%{input.ApplyReason.Trim()}%")))
                                        .Where((patriarchTel, u => EF.Functions.Like(u.PatriarchTel, $"%{input.PatriarchTel.Trim()}%")))
                                        .Where(input.Status.HasValue, t => t.Status == input.Status)
                                        .Where(input.CertificateType.HasValue, t => t.CertificateType == input.CertificateType)
                                        .Where(input.Gender.HasValue, t => t.Gender == input.Gender)
                                        .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
}