using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.ViewModel.EnrollManage.SpecialtyManage;
using EMIS.ViewModel.Students;
using EMIS.DataLogic.Common.Students;
using EMIS.ViewModel;
using EMIS.ViewModel.UniversityManage.SpecialtyClassManage;
using EMIS.ViewModel.EducationManagement;
using EMIS.ViewModel.CultureplanManage.PlanManagement;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.GraduationManage.GraduationSetting;

namespace EMIS.DataLogic.GraduationManage.GraduationSetting
{
    public class GraduationStandardDAL
    {
        public GraduationStandardRepository GraduationStandardRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public DepartmentRepository DepartmentRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public CampusRepository CampusRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public SpecialtyPlanRepository SpecialtyPlanRepository { get; set; }
        public SpecialtyPlanTeachingSettingRepository SpecialtyPlanTeachingSettingRepository { get; set; }
        public ExecutablePlanRepository ExecutablePlanRepository { get; set; }
        public ExecutablePlanTeachingSettingRepository ExecutablePlanTeachingSettingRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public CourseTypeSelectCourseTypeRepository CourseTypeSelectCourseTypeRepository { get; set; }
        public UserRepository UserRepository { get; set; }

        /// <summary>
        /// 查询对应的毕业标准信息View
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<GraduationStandardView> GetGraduationStandardViewQueryable(Expression<Func<ER_GraduationStandard, bool>> exp)
        {
            var query = from gs in GraduationStandardRepository.GetList(exp)
                        join gr in GrademajorRepository.Entities
                        on gs.GrademajorID equals gr.GrademajorID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        join sye in SchoolyearRepository.Entities
                        on
                        new { SchoolyearID = gr.GradeID.Value, SchoolcodeID = gr.SemesterID.Value }
                        equals
                        new { SchoolyearID = sye.Years, SchoolcodeID = sye.SchoolcodeID }
                        into tempsyenter
                        from syenter in tempsyenter.DefaultIfEmpty()
                        select new GraduationStandardView
                        {
                            GraduationStandardID = gs.GraduationStandardID,
                            GrademajorID = gs.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            FacultymajorID = fa.FacultymajorID,
                            FacultymajorNo = fa.Code,
                            FacultymajorName = fa.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            CampusID = col.CampusID,
                            CampusNo = cam.No,
                            CampusName = cam.Name,
                            SchoolyearID = gr.GradeID,
                            SchoolcodeID = gr.SemesterID,
                            EnteringSchoolYearID = syenter.SchoolyearID,
                            EnteringSchoolYearCode = syenter.Code,
                            GraduatingSemesterID = gr.CF_Schoolyear.SchoolyearID,
                            GraduatingSemesterCode = gr.CF_Schoolyear.Code,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            RequireCourseCount = gs.RequireCourseCount,
                            GraduationCredit = gs.GraduationCredit,
                            RecordStatus = gs.RecordStatus,
                            CreateUserID = gs.CreateUserID,
                            CreateTime = gs.CreateTime,
                            ModifyUserID = gs.ModifyUserID,
                            ModifyTime = gs.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的毕业标准信息View（门数、各类型学分统计：专业计划、执行计划）
        /// 注：由于学分制实行的程度不同，目前只对专业计划进行统计
        /// 默认为普教版本
        /// </summary>
        /// <param name="expGraduationStandard"></param>
        /// <param name="expExecutablePlan"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public virtual IQueryable<GraduationStandardView> GetGraduationStandardViewQueryable(Expression<Func<ER_GraduationStandard, bool>> expGraduationStandard,
                Expression<Func<EM_ExecutablePlan, bool>> expExecutablePlan, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from gs in GraduationStandardRepository.GetList(expGraduationStandard)
                        join gr in GrademajorRepository.Entities
                        on gs.GrademajorID equals gr.GrademajorID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        join sye in SchoolyearRepository.Entities
                        on new { SchoolyearID = gr.GradeID.Value, SchoolcodeID = gr.SemesterID.Value }
                        equals new { SchoolyearID = sye.Years, SchoolcodeID = sye.SchoolcodeID } into tempsyenter
                        from syenter in tempsyenter.DefaultIfEmpty()
                        join stugr in
                            (
                                //年级专业对应的学生人数（区分在校、非在校）
                                from stu in StudentRepository.GetList(expStudent)
                                join cl in ClassmajorRepository.Entities
                                on stu.ClassmajorID equals cl.ClassmajorID
                                group cl by cl.GrademajorID into g
                                select new
                                {
                                    GrademajorID = g.Key,
                                    StudentCount = g.Count()
                                }
                            )
                        on gr.GrademajorID equals stugr.GrademajorID into tempstugr
                        from gsstugr in tempstugr.DefaultIfEmpty()
                        join spgr in
                            (
                                //专业计划(计划门数、计划学分、必修、限选、任选学分统计)
                                from spct in
                                    (
                                        from sp in SpecialtyPlanRepository.Entities
                                        join ct in CourseTypeSelectCourseTypeRepository.Entities
                                        on sp.CourseTypeID equals ct.CourseTypeID into tempct
                                        from spct in tempct.DefaultIfEmpty()
                                        select new
                                        {
                                            GrademajorID = sp.GrademajorID,
                                            CourseTypeID = sp.CourseTypeID,
                                            Credit = sp.EM_SpecialtyPlanTeachingSetting.Credit ?? 0,
                                            IsRequired = spct.IsRequired ?? true,
                                            IsOptionalCourse = spct.IsOptionalCourse ?? false,
                                            IsFreeSelectionCourse = spct.IsFreeSelectionCourse ?? false,
                                        }
                                    )
                                group spct by spct.GrademajorID into g
                                select new
                                {
                                    GrademajorID = g.Key,
                                    PlanCourseCount = g.Count(),
                                    SpecialtyRequireCreditTotal = g.Sum(x => x.IsRequired ? x.Credit : 0),
                                    OptionalCreditTotal = g.Sum(x => x.IsOptionalCourse ? x.Credit : 0),
                                    FreeSelectionCreditTotal = g.Sum(x => x.IsFreeSelectionCourse ? x.Credit : 0),
                                    SpecialtyCreditTotal = g.Sum(x => x.Credit)
                                }
                            )
                        on gr.GrademajorID equals spgr.GrademajorID into tempspgr
                        from gsspgr in tempspgr.DefaultIfEmpty()
                        join epgr in
                            (
                                //执行计划(执行门数、执行学分)
                                from ep in ExecutablePlanRepository.GetList(expExecutablePlan)
                                join ct in CourseTypeSelectCourseTypeRepository.Entities
                                on ep.CourseTypeID equals ct.CourseTypeID into tempct
                                from epct in tempct.DefaultIfEmpty()
                                group ep by ep.GrademajorID into g
                                select new
                                {
                                    GrademajorID = g.Key,
                                    ExecCourseCount = g.Count(),
                                    ExecutableCreditTotal = g.Sum(x => x.EM_ExecutablePlanTeachingSetting.Credit ?? 0)
                                }
                            )
                        on gr.GrademajorID equals epgr.GrademajorID into tempepgr
                        from gsepgr in tempepgr.DefaultIfEmpty()
                        select new GraduationStandardView
                        {
                            GraduationStandardID = gs.GraduationStandardID,
                            GrademajorID = gs.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            FacultymajorID = fa.FacultymajorID,
                            FacultymajorNo = fa.Code,
                            FacultymajorName = fa.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            CampusID = col.CampusID,
                            CampusNo = cam.No,
                            CampusName = cam.Name,
                            SchoolyearID = gr.GradeID,
                            SchoolcodeID = gr.SemesterID,
                            EnteringSchoolYearID = syenter.SchoolyearID,
                            EnteringSchoolYearCode = syenter.Code,
                            GraduatingSemesterID = gr.CF_Schoolyear.SchoolyearID,
                            GraduatingSemesterCode = gr.CF_Schoolyear.Code,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            StudentCount = gsstugr.StudentCount == null ? 0 : gsstugr.StudentCount,
                            PlanCourseCount = gsspgr.PlanCourseCount == null ? 0 : gsspgr.PlanCourseCount,
                            SpecialtyRequireCreditTotal = gsspgr.SpecialtyRequireCreditTotal == null ? 0 : gsspgr.SpecialtyRequireCreditTotal,
                            OptionalCreditTotal = gsspgr.OptionalCreditTotal == null ? 0 : gsspgr.OptionalCreditTotal,
                            FreeSelectionCreditTotal = gsspgr.FreeSelectionCreditTotal == null ? 0 : gsspgr.FreeSelectionCreditTotal,
                            SpecialtyCreditTotal = gsspgr.SpecialtyCreditTotal == null ? 0 : gsspgr.SpecialtyCreditTotal,
                            ExecCourseCount = gsepgr.ExecCourseCount == null ? 0 : gsepgr.ExecCourseCount,
                            ExecutableCreditTotal = gsepgr.ExecutableCreditTotal == null ? 0 : gsepgr.ExecutableCreditTotal,
                            RequireCourseCount = gs.RequireCourseCount,
                            GraduationCredit = gs.GraduationCredit,
                            RecordStatus = gs.RecordStatus,
                            CreateUserID = gs.CreateUserID,
                            CreateTime = gs.CreateTime,
                            ModifyUserID = gs.ModifyUserID,
                            ModifyTime = gs.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的年级专业信息View
        /// </summary>
        /// <param name="expGrademajor"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<GrademajorView> GetGrademajorViewQueryable(Expression<Func<CF_Grademajor, bool>> expGrademajor, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from gr in GrademajorRepository.GetList(expGrademajor)
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join cam in CampusRepository.Entities
                        on col.CampusID equals cam.CampusID
                        join grsy in SchoolyearRepository.Entities
                        on gr.GraduateSchoolyearID equals grsy.SchoolyearID
                        join stugr in
                            (
                                //年级专业对应的学生人数
                                from stu in StudentRepository.GetList(expStudent)
                                join cl in ClassmajorRepository.Entities
                                on stu.ClassmajorID equals cl.ClassmajorID
                                group cl by cl.GrademajorID into g
                                select new
                                {
                                    GrademajorID = g.Key,
                                    StudentCount = g.Count()
                                }
                            )
                        on gr.GrademajorID equals stugr.GrademajorID into tempstugr
                        from gsstugr in tempstugr.DefaultIfEmpty()
                        select new GrademajorView
                        {
                            GrademajorID = gr.GrademajorID,
                            Code = gr.Code,
                            Name = gr.Name,
                            Abbreviation = gr.Abbreviation,
                            GradeID = gr.GradeID,
                            SemesterID = gr.SemesterID,
                            GraduateSchoolyearID = gr.GraduateSchoolyearID,
                            GraduateSchoolyearCode = grsy.Code,
                            GraduateValue = grsy.Value,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            StudentCount = gsstugr.StudentCount == null ? 0 : gsstugr.StudentCount,
                            StandardID = fa.StandardID,
                            CollegeID = fa.CollegeID,
                            CollegeCode = col.No,
                            CollegeName = col.Name,
                            CampusID = col.CampusID,
                            CampusNo = cam.No,
                            CampusName = cam.Name
                        };

            return query;
        }

        /// <summary>
        /// 查询未新增的毕业标准对应的年级专业信息View
        /// </summary>
        /// <param name="expGrademajor"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<GrademajorView> GetGraduationStandardViewNoAddQueryable(Expression<Func<CF_Grademajor, bool>> expGrademajor, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from gr in this.GetGrademajorViewQueryable(expGrademajor, expStudent)
                        join gs in GraduationStandardRepository.Entities
                        on gr.GrademajorID equals gs.GrademajorID into tempgs
                        from grgs in tempgs.DefaultIfEmpty()
                        where grgs.GraduationStandardID == null
                        select gr;

            return query;
        }

        /// <summary>
        /// 查询毕业标准对应的专业计划信息
        /// 注：默认为普教版本
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public virtual IQueryable<SpecialtyPlanView> GetSpecialtyPlanViewQueryable(Expression<Func<ER_GraduationStandard, bool>> exp)
        {
            var query = from gs in GraduationStandardRepository.GetList(exp)
                        join gr in GrademajorRepository.Entities
                        on gs.GrademajorID equals gr.GrademajorID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join sp in SpecialtyPlanRepository.Entities
                        on gr.GrademajorID equals sp.GrademajorID
                        join sy in SchoolyearRepository.Entities
                        on sp.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on sp.CoursematerialID equals cou.CoursematerialID
                        join depcou in DepartmentRepository.Entities
                        on sp.DepartmentID equals depcou.DepartmentID into tempdepcou
                        from spdepcou in tempdepcou.DefaultIfEmpty()
                        select new SpecialtyPlanView
                        {
                            SpecialtyPlanID = sp.SpecialtyPlanID,
                            SchoolyearID = sp.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            GrademajorID = sp.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            CollegeID = fa.CollegeID,
                            CollegeCode = col.No,
                            CollegeName = col.Name,
                            CoursematerialID = sp.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            CourseStructureID = sp.CourseStructureID,
                            CourseCategoryID = sp.CourseCategoryID,
                            CourseTypeID = sp.CourseTypeID,
                            CourseQualityID = sp.CourseQualityID,
                            ResultTypeID = sp.ResultTypeID,
                            PracticeTypeID = sp.PracticeTypeID,
                            ExaminationModeID = sp.ExaminationModeID,
                            TeachinglanguageID = sp.TeachinglanguageID,
                            CourseFineID = sp.CourseFineID,
                            IsSpecialtycore = sp.IsSpecialtycore ?? false,
                            IsCooperation = sp.IsCooperation ?? false,
                            IsRequired = sp.IsRequired ?? false,
                            IsElective = sp.IsElective ?? false,
                            IsNetworkCourse = sp.IsNetworkCourse ?? false,
                            IsMainCourse = sp.IsMainCourse ?? false,
                            Credit = sp.EM_SpecialtyPlanTeachingSetting.Credit ?? 0,
                            TheoryCourse = sp.EM_SpecialtyPlanTeachingSetting.TheoryCourse ?? 0,
                            Practicehours = sp.EM_SpecialtyPlanTeachingSetting.Practicehours ?? 0,
                            Trialhours = sp.EM_SpecialtyPlanTeachingSetting.Trialhours ?? 0,
                            Totalhours = (sp.EM_SpecialtyPlanTeachingSetting.TheoryCourse ?? 0) + (sp.EM_SpecialtyPlanTeachingSetting.Practicehours ?? 0),
                            SchoolyearNumID = (sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1)) / 2 + 1,
                            SchoolcodeID = sy.SchoolcodeID,
                            StarttermID = sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1) + 1,
                            DepartmentID = sp.DepartmentID,
                            DepartmentCode = spdepcou.No,
                            DepartmentName = spdepcou.Name,
                            HandleModeID = sp.HandleModeID,
                            Remark = sp.Remark,
                            RecordStatus = sp.RecordStatus,
                            CreateUserID = sp.CreateUserID,
                            CreateTime = sp.CreateTime,
                            ModifyUserID = sp.ModifyUserID,
                            ModifyTime = sp.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询毕业标准对应的执行计划信息
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<ExecutablePlanView> GetExecutablePlanViewQueryable(Expression<Func<ER_GraduationStandard, bool>> exp)
        {
            var query = from gs in GraduationStandardRepository.GetList(exp)
                        join gr in GrademajorRepository.Entities
                        on gs.GrademajorID equals gr.GrademajorID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join col in CollegeRepository.Entities
                        on fa.CollegeID equals col.CollegeID
                        join ep in ExecutablePlanRepository.Entities
                        on gr.GrademajorID equals ep.GrademajorID
                        join sy in SchoolyearRepository.Entities
                        on ep.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on ep.CoursematerialID equals cou.CoursematerialID
                        join depcou in DepartmentRepository.Entities
                        on ep.DepartmentID equals depcou.DepartmentID into tempdepcou
                        from epdepcou in tempdepcou.DefaultIfEmpty()
                        select new ExecutablePlanView 
                        { 
                            ExecutablePlanID = ep.ExecutablePlanID,
                            SpecialtyPlanID = ep.SpecialtyPlanID,
                            SchoolyearID = ep.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            GrademajorID = ep.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            CollegeID = fa.CollegeID,
                            CollegeCode = col.No,
                            CollegeName = col.Name,
                            CoursematerialID = ep.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            CourseStructureID = ep.CourseStructureID,
                            CourseCategoryID = ep.CourseCategoryID,
                            CourseTypeID = ep.CourseTypeID,
                            CourseQualityID = ep.CourseQualityID,
                            ResultTypeID = ep.ResultTypeID,
                            PracticeTypeID = ep.EM_ExecutablePlanProfile.PracticeTypeID,
                            ExaminationModeID = ep.EM_ExecutablePlanProfile.ExaminationModeID,
                            TeachinglanguageID = ep.EM_ExecutablePlanProfile.TeachinglanguageID,
                            CourseFineID = ep.EM_ExecutablePlanProfile.CourseFineID,
                            IsSpecialtycore = ep.EM_ExecutablePlanProfile.IsSpecialtycore ?? false,
                            IsCooperation = ep.EM_ExecutablePlanProfile.IsCooperation ?? false,
                            IsRequired = ep.EM_ExecutablePlanProfile.IsRequired ?? false,
                            IsElective = ep.EM_ExecutablePlanProfile.IsElective ?? false,
                            IsNetworkCourse = ep.EM_ExecutablePlanProfile.IsNetworkCourse ?? false,
                            IsMainCourse = ep.EM_ExecutablePlanProfile.IsMainCourse ?? false,
                            Credit = ep.EM_ExecutablePlanTeachingSetting.Credit ?? 0,
                            TheoryCourse = ep.EM_ExecutablePlanTeachingSetting.TheoryCourse ?? 0,
                            Practicehours = ep.EM_ExecutablePlanTeachingSetting.Practicehours ?? 0,
                            Trialhours = ep.EM_ExecutablePlanTeachingSetting.Trialhours ?? 0,
                            Totalhours = (ep.EM_ExecutablePlanTeachingSetting.TheoryCourse ?? 0) + (ep.EM_ExecutablePlanTeachingSetting.Practicehours ?? 0),
                            SchoolyearNumID = (sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1)) / 2 + 1,
                            SchoolcodeID = sy.SchoolcodeID,
                            StarttermID = sy.Value - ((gr.GradeID * 2 - 1) + gr.SemesterID - 1) + 1,
                            DepartmentID = ep.DepartmentID,
                            DepartmentCode = epdepcou.No,
                            DepartmentName = epdepcou.Name,
                            HandleModeID = ep.HandleModeID,
                            ExecuteStatus = ep.RecordStatus,
                            Remark = ep.Remark,
                            RecordStatus = ep.RecordStatus,
                            CreateUserID = ep.CreateUserID,
                            CreateTime = ep.CreateTime,
                            ModifyUserID = ep.ModifyUserID,
                            ModifyTime = ep.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询年级专业对应的各在校状态学生信息
        /// </summary>
        /// <param name="expGrademajor"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<BaseStudentView> GetGradeMajorStudentViewQueryAble(Expression<Func<CF_Grademajor, bool>> expGrademajor, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from gr in GrademajorRepository.GetList(expGrademajor)
                        join cl in ClassmajorRepository.Entities
                        on gr.GrademajorID equals cl.GrademajorID
                        join stu in StudentRepository.GetList(expStudent)
                        on cl.ClassmajorID equals stu.ClassmajorID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        select new BaseStudentView
                        {
                            UserID = stu.UserID,
                            LoginID = us.LoginID,
                            UserName = us.Name,
                            SexID = stu.SexID,
                            IDNumber = stu.IDNumber,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatus = stu.StudentStatus,
                            SchoolyearID = gr.GradeID,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorCode = cl.No,
                            ClassmajorName = cl.Name
                        };

            return query;
        }

    }
}