using Furion.DatabaseAccessor.Extensions;
using NPOI.SS.UserModel;
using System.Dynamic;
using YBEE.EQM.Core;

namespace YBEE.EQM.Application;

/// <summary>
/// 监测学生管理服务
/// </summary>
public class ExamStudentService : IExamStudentService, ITransient
{
    private readonly IRepository<ExamStudent> _rep;
    private readonly IExamGradeService _examGradeService;
    private readonly ISysDictDataService _sysDictDataService;
    private readonly INceeCourseCombService _nceeCourseCombService;
    private readonly ISchoolClassService _schoolClassService;

    public ExamStudentService(IRepository<ExamStudent> rep, IExamGradeService examGradeService, ISysDictDataService sysDictDataService, INceeCourseCombService nceeCourseCombService, ISchoolClassService schoolClassService)
    {
        _rep = rep;
        _examGradeService = examGradeService;
        _sysDictDataService = sysDictDataService;
        _nceeCourseCombService = nceeCourseCombService;
        _schoolClassService = schoolClassService;
    }

    #region 批量导入
    /// <summary>
    /// 上传批量导入文件
    /// </summary>
    /// <param name="filePath"></param>
    /// <param name="examPlanId"></param>
    /// <param name="examGradeId"></param>
    /// <returns></returns>
    public async Task<UploadExamDataOutput<UploadExamStudentOutput>> Upload(string filePath, int examPlanId, int examGradeId)
    {
        var examGrade = await _examGradeService.GetById(examGradeId);

        UploadExamDataOutput<UploadExamStudentOutput> result = new();
        try
        {
            using FileStream fs = new(filePath, FileMode.Open, FileAccess.Read);
            IWorkbook workbook = ExcelUtil.GetWorkbook(filePath, fs);
            var sheet = workbook.GetSheetAt(0);
            var rows = sheet.GetRowEnumerator();

            #region 验证表结构
            // 少于2行验证
            if (sheet.LastRowNum < 2)
            {
                result.ErrorMessage.Add("第一行应为填写说明，第二行应为标题行，请勿修改模板结构。");
                return result;
            }

            // 跳过第一行
            rows.MoveNext();

            // 读取表头
            rows.MoveNext();
            IRow headerRow = (IRow)rows.Current;
            int index = 0;
            int CLASS_INDEX = index++;
            int NAME_INDEX = index++;
            int CERT_TYPE_INDEX = index++;
            int ID_NUM_INDEX = index++;
            int GENDER_INDEX = index++;
            int EXAM_NUM_INDEX = index++;
            int COURSE_COMB_INDEX = index++;
            int REMARK_INDEX = index++;
            int ROOM_INDEX = index++;
            int SEAT_INDEX = index;
            Dictionary<int, string> headers = new()
            {
                { CLASS_INDEX, "班级" },
                { NAME_INDEX, "姓名" },
                { CERT_TYPE_INDEX, "证件类型" },
                { ID_NUM_INDEX, "证件号码" },
                { GENDER_INDEX, "性别" },
                { EXAM_NUM_INDEX, "自编监测号" },
                { COURSE_COMB_INDEX, "选科组合" },
                { REMARK_INDEX, "备注" },
                { ROOM_INDEX, "考场号" },
                { SEAT_INDEX, "座位号" },
            };
            List<string> headerErrors = new();
            for (int i = 0; i <= index; i++)
            {
                if (headerRow.GetCell(i)?.ToString() != headers[i])
                {
                    char letter = (char)('A' + i);
                    headerErrors.Add(letter.ToString());
                }
            }
            if (headerErrors.Count != 0)
            {
                string columnErrors = string.Join("、", headerErrors);
                result.ErrorMessage.Add($"第2行标题行{columnErrors}列名错误。从A列开始依次应为班级、姓名、证件类型、证件号码、性别、自编监测号、选科组合、备注、考场号和座位号。");
                return result;
            }
            result.StructureCorrect = true;
            #endregion

            #region 处理数据
            // 获取证件类型
            var cts = await _sysDictDataService.GetListByDictTypeId(304);
            var certificateTypes = cts.ToDictionary(x => x.Name, y => y.Value);
            // 获取选科组全
            var ccs = await _nceeCourseCombService.GetAllList();
            var courseCombs = ccs.ToDictionary(x => x.ShortName, y => y.Id);

            // 读取数据
            List<UploadExamStudentOutput> data = new();
            int rn = 0;
            while (rows.MoveNext())
            {
                IRow row = (IRow)rows.Current;
                string rv = row.GetCell(0)?.ToString().Trim() ?? "";
                rv += row.GetCell(1)?.ToString().Trim() ?? "";
                if (rv == "")
                {
                    break;
                }

                UploadExamStudentOutput item = new() { RowNumber = ++rn };

                // 班级
                if (short.TryParse(row.GetCell(CLASS_INDEX)?.ToString(), out short classNumber))
                {
                    item.ClassNumber = classNumber;
                    if (item.ClassNumber < 1 || item.ClassNumber > 35)
                    {
                        item.ErrorMessage.Add($"{headers[CLASS_INDEX]}超限");
                    }
                }
                else
                {
                    item.ErrorMessage.Add(headers[CLASS_INDEX]);
                }

                // 姓名
                item.Name = (row.GetCell(NAME_INDEX)?.ToString() ?? "").ClearWhitespace();
                if (item.Name == "" || item.Name.Length > 100)
                {
                    item.ErrorMessage.Add(headers[NAME_INDEX]);
                }
                if (item.Name.Length > 100) { item.Name = item.Name[..100]; }

                // 证件类型
                item.CertificateTypeName = (row.GetCell(CERT_TYPE_INDEX)?.ToString() ?? "").ClearWhitespace();
                if (!(item.CertificateTypeName != "" && certificateTypes.ContainsKey(item.CertificateTypeName)))
                {
                    item.ErrorMessage.Add(headers[CERT_TYPE_INDEX]);
                }
                else
                {
                    item.CertificateType = (CertificateType)certificateTypes[item.CertificateTypeName];
                }

                // 证件号码
                item.IdNumber = (row.GetCell(ID_NUM_INDEX)?.ToString() ?? "").ToUpper().ClearWhitespace();
                if (item.CertificateType == CertificateType.ID_CARD)
                {
                    var idNumberValidate = CertificateNumberValidator.ValidateIdCard(item.IdNumber);
                    if (!idNumberValidate.Success)
                    {
                        item.ErrorMessage.Add($"{item.CertificateTypeName}{idNumberValidate.ErrorMessage}");
                    }
                    else
                    {
                        item.GenderName = idNumberValidate.Gender == Gender.MALE ? "男" : "女";
                        item.Gender = idNumberValidate.Gender;
                    }
                }
                else
                {
                    // 性别
                    item.GenderName = (row.GetCell(GENDER_INDEX)?.ToString() ?? "").ClearWhitespace();
                    if (item.CertificateType != CertificateType.ID_CARD)
                    {
                        item.Gender = item.GenderName == "男" ? Gender.MALE : item.GenderName == "女" ? Gender.FEMALE : Gender.UNKNOWN;
                    }
                }
                if (item.IdNumber.Length > 50) { item.IdNumber = item.IdNumber[..50]; }

                // 自编监测号
                item.ExamNumber = (row.GetCell(EXAM_NUM_INDEX)?.ToString() ?? "").ClearWhitespace();
                if (item.ExamNumber.Length > 50) { item.ExamNumber = item.ExamNumber[..50]; }
                // 根据配置验证自编号
                if (examGrade.IsRequiredSelfExamNumber && item.ExamNumber.Length != examGrade.SelfExamNumberLength)
                {
                    item.ErrorMessage.Add($"{headers[EXAM_NUM_INDEX]}（长度{examGrade.SelfExamNumberLength}位）");
                }

                // 选科组合
                item.NceeCourseCombName = (row.GetCell(COURSE_COMB_INDEX)?.ToString() ?? "").ClearWhitespace();
                // TODO 高中需要处理选科
                if (item.NceeCourseCombName != "")
                {
                    if (courseCombs.TryGetValue(item.NceeCourseCombName, out short value))
                    {
                        item.NceeCourseCombId = value;
                    }
                    else
                    {
                        item.ErrorMessage.Add(headers[COURSE_COMB_INDEX]);
                    }
                }

                // 备注
                item.Remark = (row.GetCell(REMARK_INDEX)?.ToString() ?? "").ClearWhitespace();
                if (item.Remark.Length > 200) { item.Remark = item.Remark[..200]; }

                // 考场号
                item.RoomNumber = (row.GetCell(ROOM_INDEX)?.ToString() ?? "").ClearWhitespace();
                if (item.RoomNumber.Length > 20) { item.RoomNumber = item.RoomNumber[..20]; }

                // 座位号
                item.SeatNumber = (row.GetCell(SEAT_INDEX)?.ToString() ?? "").ClearWhitespace();
                if (item.RoomNumber.Length > 20) { item.SeatNumber = item.SeatNumber[..20]; }

                // 行是否验证通过
                item.IsSuccess = item.ErrorMessage.Count == 0;
                data.Add(item);

                result.TotalRowCount++;
                if (!item.IsSuccess)
                {
                    result.ErrorRowCount++;
                }
            }
            result.Rows = data;
            #endregion

            workbook.Close();
            fs.Close();
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            File.Delete(filePath);
        }
        return result;
    }
    /// <summary>
    /// 批量导入监测学生
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Import(ImportExamStudentInput input)
    {
        var orgId = input.SysOrgId ?? CurrentSysUserInfo.SysOrgId;

        var examPlan = await _rep.Change<ExamPlan>().FirstOrDefaultAsync(t => t.Id == input.ExamPlanId);
        var examGrade = await _examGradeService.GetById(input.ExamGradeId);
        var classNumbers = input.Items.Select(t => t.ClassNumber).Distinct().ToList();
        var classDict = await _schoolClassService.GetImportSchoolClassList(new()
        {
            SysOrgId = orgId,
            SysOrgBranchId = input.SysOrgBranchId,
            ExamGrade = examGrade,
            ClassNumberList = classNumbers,
        });

        // 删除年级下所有数据
        await _rep.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId && t.GradeId == input.GradeId && t.SysOrgBranchId == input.SysOrgBranchId).ExecuteDeleteAsync();

        List<ExamStudent> items = new();
        foreach (var ni in input.Items)
        {
            var item = ni.Adapt<ExamStudent>();
            item.ExamPlanId = input.ExamPlanId;
            item.SysOrgId = orgId;
            item.SysOrgBranchId = input.SysOrgBranchId;
            item.SchoolClassId = classDict[ni.ClassNumber];
            item.Name = item.Name.ClearWhitespace();
            item.IdNumber = item.IdNumber.IdNumberStdProcessing();
            items.Add(item);
        }
        await _rep.InsertAsync(items);
    }
    #endregion

    #region 创建编辑
    /// <summary>
    /// 添加监测学生
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Add(AddExamStudentInput input)
    {
        var orgId = input.SysOrgId ?? CurrentSysUserInfo.SysOrgId;

        // 检测同一监测计划中同机构内是否有相同证件号码的学生
        var sameItems = await _rep.DetachedEntities.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId && t.CertificateType == input.CertificateType && t.IdNumber == input.IdNumber).ProjectToType<ExamStudentOutput>().ToListAsync();
        if (sameItems.Any())
        {
            throw Oops.Oh(ErrorCode.E2003, string.Join("、", sameItems.Select(t => $"{t.ExamGrade.Grade.Name}{t.ClassNumber}班{t.Name}")), "证件号码");
        }

        var examGrade = await _examGradeService.GetById(input.ExamGradeId);
        var schoolClass = await _schoolClassService.GetSchoolClass(orgId, input.SysOrgBranchId, examGrade, input.ClassNumber);

        var item = input.Adapt<ExamStudent>();
        item.SysOrgId = orgId;
        item.SchoolClassId = schoolClass.Id;
        item.Name = item.Name.ClearWhitespace();
        item.IdNumber = item.IdNumber.IdNumberStdProcessing();

        await item.InsertAsync();
    }
    /// <summary>
    /// 更新监测学生
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Update(UpdateExamStudentInput input)
    {
        var oitem = await _rep.DetachedEntities.FirstOrDefaultAsync(t => t.Id == input.Id) ?? throw Oops.Oh(ErrorCode.E2001);

        var examGrade = await _examGradeService.GetById(oitem.ExamGradeId);
        var schoolClass = await _schoolClassService.GetSchoolClass(oitem.SysOrgId, input.SysOrgBranchId, examGrade, input.ClassNumber);

        var item = input.Adapt<ExamStudent>();
        item.SchoolClassId = schoolClass.Id;
        item.Name = item.Name.ClearWhitespace();
        item.IdNumber = item.IdNumber.IdNumberStdProcessing();

        await item.UpdateIncludeAsync(new[] {
            nameof(item.SchoolClassId),
            nameof(item.ClassNumber),
            nameof(item.Name),
            nameof(item.CertificateType),
            nameof(item.IdNumber),
            nameof(item.Gender),
            nameof(item.BirthDate),
            nameof(item.ExamNumber),
            nameof(item.StudentNumber),
            nameof(item.Remark),
            nameof(item.NceeCourseCombId),
            nameof(item.SysOrgBranchId),
            nameof(item.RoomNumber),
            nameof(item.SeatNumber),
        });
    }
    /// <summary>
    /// 删除监测学生
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Del(BaseId<long> input)
    {
        var item = await _rep.FirstOrDefaultAsync(t => t.Id == input.Id) ?? throw Oops.Oh(ErrorCode.E2001);
        await item.DeleteAsync();
    }
    /// <summary>
    /// 清空监测学生
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task Clear(ClearExamStudentInput input)
    {
        var orgId = CurrentSysUserInfo.SysOrgId;
        await _rep.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == orgId).ExecuteDeleteAsync();
    }
    #endregion

    #region 查询统计
    /// <summary>
    /// 分页查询监测学生列表
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task<PageResult<ExamStudentOutput>> QueryPageList(ExamStudentPageInput input)
    {
        var query = GetQueryBase(input);
        query = query.Where(t => t.ExamPlanId == input.ExamPlanId && t.SysOrgId == CurrentSysUserInfo.SysOrgId);
        var ret = await query.OrderBy(t => t.GradeId).ThenBy(t => t.ClassNumber).ThenBy(t => t.Id).ProjectToType<ExamStudentOutput>().ToADPagedListAsync(input.PageIndex, input.PageSize);
        return ret;
    }
    /// <summary>
    /// 获取机构班级上报人数统计列表
    /// </summary>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task<ExamStudentGradeClassStudentCountOutput> GetOrgGradeClassStudentCount(int examPlanId)
    {
        var orgId = CurrentSysUserInfo.SysOrgId;
        var items = await _rep.DetachedEntities.Where(t => t.ExamPlanId == examPlanId && t.SysOrgId == orgId)
                                               .GroupBy(t => new { t.GradeId, t.ClassNumber })
                                               .Select(t => new
                                               {
                                                   t.Key.GradeId,
                                                   t.Key.ClassNumber,
                                                   t.FirstOrDefault().Grade,
                                                   Count = t.Count(),
                                               })
                                               .ToListAsync();
        var cols = items.Select(t => t.ClassNumber).Distinct().ToList();
        var retItems = items.ToPivotList(c => c.ClassNumber, r => r.GradeId, d => d.Any() ? d.Sum(x => x.Count) : 0);
        foreach (var item in retItems)
        {
            item.Grade = items.FirstOrDefault(t => t.GradeId == item.GradeId).Grade;
            item.GradeTotal = items.Where(t => t.GradeId == item.GradeId).Sum(x => x.Count);
        }
        int total = retItems.Sum(x => x.GradeTotal);
        IDictionary<string, object> totalItem = new ExpandoObject();
        totalItem.Add("GradeId", 9999);
        totalItem.Add("Grade", new { Id = 9999, Name = "合计" });
        totalItem.Add("GradeTotal", total);
        retItems.Add(totalItem);
        return new()
        {
            ClassNumberList = cols,
            Items = retItems,
            Total = total,
        };
    }
    /// <summary>
    /// 分页查询班级学生人数
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    public async Task<PageResult<ExamStudentCountItem>> QueryStudentCountPageList(ExamStudentCountPageInput input)
    {
        List<string> whereCause = new();
        if (!string.IsNullOrEmpty(input.SysOrgName?.Trim()))
        {
            whereCause.Add("T1.sys_org_full_name LIKE '%@sysOrgName%'");
        }
        if (!string.IsNullOrEmpty(input.SysOrgCode?.Trim()))
        {
            whereCause.Add("T1.sys_org_code LIKE '%@sysOrgCode%'");
        }
        if (input.UrbanRuralType.HasValue)
        {
            whereCause.Add("T1.urban_rural_type = @urbanRuralType");
        }
        if (input.GradeId.HasValue)
        {
            whereCause.Add("T1.grade_id = @gradeId");
        }
        if (input.ClassNumber.HasValue)
        {
            whereCause.Add("T1.class_number = @classNumber");
        }
        if (input.ClassCountMin.HasValue)
        {
            whereCause.Add("T2.class_count >= @classCountMin");
        }
        if (input.ClassCountMax.HasValue)
        {
            whereCause.Add("T2.class_count <= @classCountMax");
        }
        if (input.StudentCountMin.HasValue)
        {
            whereCause.Add("T1.student_count >= @studentCountMin");
        }
        if (input.StudentCountMax.HasValue)
        {
            whereCause.Add("T1.student_count <= @studentCountMax");
        }
        if (input.ExcludeSchoolClassIds != null && input.ExcludeSchoolClassIds.Any())
        {
            string excludeScids = string.Join(", ", input.ExcludeSchoolClassIds);
            whereCause.Add($"T1.school_class_id NOT IN ({excludeScids})");
        }
        if (input.IncludeSchoolClassIds != null)
        {
            if (input.IncludeSchoolClassIds.Count == 0)
            {
                whereCause.Add($"T1.school_class_id IN (0)");
            }
            else
            {
                string includeScids = string.Join(", ", input.IncludeSchoolClassIds);
                whereCause.Add($"T1.school_class_id IN ({includeScids})");
            }
        }

        string whereSql = $"WHERE T1.exam_plan_id = @examPlanId";
        if (whereCause.Count > 0)
        {
            whereSql = $"{whereSql} AND {string.Join(" AND ", whereCause)}";
        }

        var p = new
        {
            input.PageSize,
            PageOffset = (input.PageIndex - 1) * input.PageSize,
            input.SysOrgName,
            input.ExamPlanId,
            input.GradeId,
            input.ClassCountMin,
            input.ClassCountMax,
            input.ClassNumber,
            input.StudentCountMin,
            input.StudentCountMax,
            input.UrbanRuralType,
        };

        var totalCount = await _rep.SqlScalarAsync<int>($@"
SELECT COUNT(T1.school_class_id) AS total_count
FROM
(
	SELECT 
        T1.exam_plan_id,
		T1.sys_org_id, 
		T4.full_name AS sys_org_full_name, 
		T4.`name` AS sys_org_name, 
		T4.`code` AS sys_org_code, 
        T4.urban_rural_type, 
		T1.school_class_id, 
		T1.grade_id, 
		T5.`name` AS grade_name, 
		T1.class_number, 
		COUNT(1) AS student_count
	FROM exam_student AS T1
	JOIN school_class AS T3 ON T1.school_class_id = T3.id
	JOIN sys_org AS T4 ON T1.sys_org_id = T4.id
	JOIN base_grade AS T5 ON T1.grade_id = T5.id
	WHERE T1.exam_plan_id = @examPlanId
	GROUP BY T1.exam_plan_id, T1.sys_org_id, T4.full_name, T4.`name`, T4.`code`, T4.urban_rural_type, T1.school_class_id, T1.grade_id, T5.`name`, T1.class_number
) AS T1
JOIN
(
	SELECT T1.sys_org_id, T1.grade_id, COUNT(DISTINCT T1.school_class_id) AS class_count 
	FROM exam_student AS T1
	WHERE T1.exam_plan_id = @examPlanId
	GROUP BY T1.sys_org_id, T1.grade_id
) AS T2 ON T1.sys_org_id = T2.sys_org_id AND T1.grade_id = T2.grade_id
{whereSql}
", p);


        var items = await _rep.SqlQueriesAsync<ExamStudentCountItem>($@"
SELECT T1.*, T2.class_count, T3.grade_count
FROM
(
	SELECT 
        T1.exam_plan_id,
		T1.sys_org_id, 
		T4.full_name AS sys_org_full_name, 
		T4.`name` AS sys_org_name, 
		T4.`code` AS sys_org_code, 
		T4.urban_rural_type, 
		T1.sys_org_branch_id, 
		T6.`name` AS sys_org_branch_name, 
		T1.school_class_id, 
		T3.grade_begin_year, 
		T1.grade_id, 
		T5.`name` AS grade_name, 
		T5.grade_number, 
		T1.class_number, 
		COUNT(1) AS student_count
	FROM exam_student AS T1
	JOIN school_class AS T3 ON T1.school_class_id = T3.id
	JOIN sys_org AS T4 ON T1.sys_org_id = T4.id
	JOIN base_grade AS T5 ON T1.grade_id = T5.id
	LEFT JOIN sys_org AS T6 ON T1.sys_org_branch_id = T6.id
	WHERE T1.exam_plan_id = @examPlanId
	GROUP BY T1.exam_plan_id, T1.sys_org_id, T4.full_name, T4.`name`, T4.`code`, T4.urban_rural_type, T1.sys_org_branch_id, T6.`name`, T1.school_class_id, T1.grade_id, T3.grade_begin_year, T5.`name`, T5.grade_number, T1.class_number
) AS T1
JOIN
(
	SELECT T1.sys_org_id, T1.grade_id, COUNT(DISTINCT T1.school_class_id) AS class_count 
	FROM exam_student AS T1
	WHERE T1.exam_plan_id = @examPlanId
	GROUP BY T1.sys_org_id, T1.grade_id
) AS T2 ON T1.sys_org_id = T2.sys_org_id AND T1.grade_id = T2.grade_id
JOIN
(
    SELECT T1.sys_org_id, COUNT(DISTINCT T1.grade_id) AS grade_count 
	FROM exam_student AS T1
	WHERE T1.exam_plan_id = @examPlanId
	GROUP BY T1.sys_org_id
) AS T3 ON T1.sys_org_id = T3.sys_org_id
{whereSql}
ORDER BY T1.sys_org_code, T1.grade_id, T1.class_number
LIMIT @pageSize OFFSET @pageOffset;
", p);

        PageResult<ExamStudentCountItem> ret = new()
        {
            PageIndex = input.PageIndex,
            PageSize = input.PageSize,
            TotalCount = totalCount,
            Items = items
        };
        return ret;
    }
    #endregion

    #region 私有方法
    /// <summary>
    /// 构建查询
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    private IQueryable<ExamStudent> GetQueryBase(ExamStudentPageInput input)
    {
        var name = !string.IsNullOrEmpty(input.Name?.Trim());
        var idNumber = !string.IsNullOrEmpty(input.IdNumber?.Trim());
        var examNumber = !string.IsNullOrEmpty(input.ExamNumber?.Trim());
        var studentNumber = !string.IsNullOrEmpty(input.StudentNumber?.Trim());
        var roomNumber = !string.IsNullOrEmpty(input.RoomNumber?.Trim());
        var seatNumber = !string.IsNullOrEmpty(input.SeatNumber?.Trim());

        var query = _rep.DetachedEntities.Where((name, u => EF.Functions.Like(u.Name, $"%{input.Name.Trim()}%")))
                                         .Where((idNumber, u => EF.Functions.Like(u.IdNumber, $"%{input.IdNumber.Trim()}%")))
                                         .Where((examNumber, u => EF.Functions.Like(u.ExamNumber, $"%{input.ExamNumber.Trim()}%")))
                                         .Where((studentNumber, u => EF.Functions.Like(u.StudentNumber, $"%{input.StudentNumber.Trim()}%")))
                                         .Where((roomNumber, u => EF.Functions.Like(u.RoomNumber, $"%{input.RoomNumber.Trim()}%")))
                                         .Where((seatNumber, u => EF.Functions.Like(u.SeatNumber, $"%{input.SeatNumber.Trim()}%")))
                                         .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.NceeCourseCombId.HasValue, t => t.NceeCourseCombId == input.NceeCourseCombId)
                                         .Where(input.ClassNumber.HasValue, t => t.ClassNumber == input.ClassNumber)
                                         .Where(input.SysOrgBranchId.HasValue, t => t.SchoolClass.SysOrgBranchId == input.SysOrgBranchId);
        return query;
    }
    #endregion
}