JW/EMiS.DataLogic/ScoreManage/ExaminationExemptionDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.ScoreManage;
using EMIS.Entities;
using EMIS.ViewModel;

namespace EMIS.DataLogic.ScoreManage
{
    public class ExaminationExemptionDAL
    {
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }

        public CoursematerialRepository CoursematerialRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FinalExaminationRepository FinalExaminationRepository { get; set; }
        public FinalExaminationStudentRepository FinalExaminationStudentRepository { get; set; }
        public ExecutablePlanRepository ExecutablePlanRepository { get; set; }
        public ExaminationExemptionRepository ExaminationExemptionRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }

        public IQueryable<ExaminationExemptionView> GetExaminationExemptionViewQueryable(Expression<Func<ER_ExaminationExemption, bool>> exp)
        {
            var query = (from ms in ExaminationExemptionRepository.GetList(exp)
                         select new ExaminationExemptionView
                         {
                             ExaminationExemptionID = ms.ExaminationExemptionID,
                             UserID = ms.UserID,
                             LoginID = ms.CF_Student.Sys_User.LoginID,
                             Name = ms.CF_Student.Sys_User.Name,
                             ClassmajorName = ms.CF_Student.CF_Classmajor.Name,
                             CollegeID = ms.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
                             Year = ms.CF_Student.CF_Classmajor.CF_Grademajor.GradeID,
                             StandardID = ms.CF_Student.CF_Classmajor.CF_Grademajor.CF_Facultymajor.StandardID,
                             SchoolyearID = ms.SchoolyearID,
                             SchoolyearCode = ms.CF_Schoolyear.Code,
                             CoursematerialID = ms.CoursematerialID,
                             CourseCode = ms.EM_Coursematerial.CourseCode,
                             CourseName = ms.EM_Coursematerial.CourseName,
                             ExamsCategoryID = ms.ExamsCategoryID,
                             Reason = ms.Reason,
                             RecordStatus = ms.RecordStatus,
                             CreateTime = ms.CreateTime
                         });

            return query;
        }

        public IQueryable<ExaminationExemptionCoursematerialView> GetExaminationExemptionCoursematerialViewQueryable(
            Expression<Func<EMIS.Entities.CF_Schoolyear, bool>> schoolyearExp, Expression<Func<CF_Student, bool>> studentExp)
        {
            var query = (
                from emc in EducationMissionClassRepository.Entities
                join em in EducationMissionRepository.Entities on emc.EducationMissionID equals em.EducationMissionID
                join sy in SchoolyearRepository.GetList(schoolyearExp)
                    on em.SchoolyearID equals sy.SchoolyearID
                join cm in CoursematerialRepository.Entities on emc.CoursematerialID equals cm.CoursematerialID
                from esc in emc.EM_EducationSchedulingClass
                from esccs in esc.CF_Student
                join s in StudentRepository.GetList(studentExp) on esccs.UserID equals s.UserID
                group emc by new ExaminationExemptionCoursematerialView
                {
                    CoursematerialID = cm.CoursematerialID,
                    CourseCode = cm.CourseCode,
                    CourseName = cm.CourseName
                } into g
                select g.Key
                )
                .Union(
                    from exp in ExecutablePlanRepository.Entities
                    join cm in CoursematerialRepository.Entities on exp.CoursematerialID equals cm.CoursematerialID
                    join sy in SchoolyearRepository.GetList(schoolyearExp) on exp.SchoolyearID equals sy.SchoolyearID
                    join gread in GrademajorRepository.Entities on exp.GrademajorID equals gread.GrademajorID
                    join gs in
                        (
                            from g in GrademajorRepository.Entities
                            from c in g.CF_Classmajor
                            from s in c.CF_Student
                            join user in StudentRepository.GetList(studentExp) on s.UserID equals user.UserID
                            group s by g.GrademajorID into g
                            select new
                            {
                                GrademajorID = g.Key,
                                StudentCount = g.Count()
                            }
                        ) on exp.GrademajorID equals gs.GrademajorID
                    group exp by new ExaminationExemptionCoursematerialView 
                    {
                        CoursematerialID=cm.CoursematerialID,
                        CourseCode=cm.CourseCode,
                        CourseName=cm.CourseName
                    }into g
                    select g.Key
                    )
                .Union(
                from fe in FinalExaminationRepository.Entities
                join cm in CoursematerialRepository.Entities on fe.CoursematerialID equals cm.CoursematerialID
                join sy in SchoolyearRepository.GetList(schoolyearExp) on fe.SchoolyearID equals sy.SchoolyearID
                from fes in fe.ER_FinalExaminationStudent
                join s in StudentRepository.GetList(studentExp) on fes.UserID equals s.UserID
                group fe by new ExaminationExemptionCoursematerialView
                {
                    CoursematerialID = cm.CoursematerialID,
                    CourseCode = cm.CourseCode,
                    CourseName = cm.CourseName
                } into g
                select g.Key
                );

            return query;
        }
    }
}