LIOT/EMiS.DataLogic/UniversityInformationManage/Specialtyclass/ClassmajorDAL.cs

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

namespace EMIS.DataLogic.Common.Specialtyclass
{
    public class ClassmajorDAL
    {
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public SocialClassmajorRepository SocialClassmajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public UserRepository UserRepository { get; set; }

        /// <summary>
        /// 查询对应的班级信息View
        /// </summary>
        /// <param name="expClassmajor"></param>
        /// <returns></returns>
        public IQueryable<ClassmajorView> GetClassmajorViewQueryable(Expression<Func<CF_Classmajor, bool>> expClassmajor)
        {
            var query = from cl in ClassmajorRepository.GetList(expClassmajor)
                        join c in UserRepository.Entities
                        on cl.UserID equals c.UserID into gu
                        from guser in gu.DefaultIfEmpty()
                        join d in UserRepository.Entities
                        on cl.AssistantUserID equals d.UserID into ga
                        from gassis in ga.DefaultIfEmpty()
                        select new ClassmajorView
                        {
                            ClassmajorID = cl.ClassmajorID,
                            No = cl.No,
                            Name = cl.Name,
                            Abbreviation = cl.Abbreviation,
                            EnglishName = cl.EnglishName,
                            ClassNum = cl.ClassNum,
                            FacultymajorID = cl.CF_Grademajor.CF_Facultymajor.FacultymajorID,
                            FacultymajorCode = cl.CF_Grademajor.CF_Facultymajor.Code,
                            FacultymajorName = cl.CF_Grademajor.CF_Facultymajor.Name,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = cl.CF_Grademajor.Code,
                            GrademajorName = cl.CF_Grademajor.Name,
                            CampusID = cl.CF_Grademajor.CF_Facultymajor.CF_College.CampusID,
                            CampusNo = cl.CF_Grademajor.CF_Facultymajor.CF_College.CF_Campus.No,
                            CampusName = cl.CF_Grademajor.CF_Facultymajor.CF_College.CF_Campus.Name,
                            SchoolAreaID = cl.CF_Grademajor.SchoolAreaID,
                            CollegeID = cl.CF_Grademajor.CF_Facultymajor.CollegeID,
                            CollegeCode = cl.CF_Grademajor.CF_Facultymajor.CF_College.No,
                            CollegeName = cl.CF_Grademajor.CF_Facultymajor.CF_College.Name,
                            SchoolyearID = cl.CF_Grademajor.SchoolyearID,
                            SchoolcodeID = cl.CF_Grademajor.SchoolcodeID,
                            StandardID = cl.CF_Grademajor.CF_Facultymajor.StandardID,
                            EducationID = cl.CF_Grademajor.CF_Facultymajor.EducationID,
                            LearningformID = cl.CF_Grademajor.CF_Facultymajor.LearningformID,
                            LearnSystem = cl.CF_Grademajor.CF_Facultymajor.LearnSystem,
                            GraduatingSemesterID = cl.CF_Grademajor.GraduatingSemesterID,
                            Value = cl.CF_Grademajor.CF_Schoolyear.Value,
                            GraduatingSemesterCode = cl.CF_Grademajor.CF_Schoolyear.Code,
                            UserID = cl.UserID,
                            UserName = guser.Name,
                            AssistantUserID = cl.AssistantUserID,
                            AssistantUserName = gassis.Name,
                            Fixedclassroom = cl.Fixedclassroom,
                            Remark = cl.Remark,
                            RecordStatus = cl.RecordStatus,
                            CreateUserID = cl.CreateUserID,
                            CreateTime = cl.CreateTime,
                            ModifyUserID = cl.ModifyUserID,
                            ModifyTime = cl.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的班级信息View(统计对应的学生人数)
        /// </summary>
        /// <param name="expClassmajor"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<ClassmajorView> GetClassmajorViewQueryable(Expression<Func<CF_Classmajor, bool>> expClassmajor,
                Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from cl in ClassmajorRepository.GetList(expClassmajor)
                        join sc in
                            (
                            from sc in StudentRepository.GetList(expStudent)
                            group sc by sc.CF_Classmajor.ClassmajorID into g
                            select new
                            {
                                ClassmajorID = g.Key,
                                StudentCount = g.Count()
                            })
                        on cl.ClassmajorID equals sc.ClassmajorID into dsc
                        from esc in dsc.DefaultIfEmpty()
                        join c in UserRepository.Entities 
                        on cl.UserID equals c.UserID into gu
                        from guser in gu.DefaultIfEmpty()
                        join d in UserRepository.Entities 
                        on cl.AssistantUserID equals d.UserID into ga
                        from gassis in ga.DefaultIfEmpty()
                        select new ClassmajorView
                        {
                            ClassmajorID = cl.ClassmajorID,
                            No = cl.No,
                            Name = cl.Name,
                            Abbreviation = cl.Abbreviation,
                            EnglishName = cl.EnglishName,
                            ClassNum = cl.ClassNum,
                            FacultymajorID = cl.CF_Grademajor.CF_Facultymajor.FacultymajorID,
                            FacultymajorCode = cl.CF_Grademajor.CF_Facultymajor.Code,
                            FacultymajorName = cl.CF_Grademajor.CF_Facultymajor.Name,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = cl.CF_Grademajor.Code,
                            GrademajorName = cl.CF_Grademajor.Name,
                            CampusID = cl.CF_Grademajor.CF_Facultymajor.CF_College.CampusID,
                            CampusNo = cl.CF_Grademajor.CF_Facultymajor.CF_College.CF_Campus.No,
                            CampusName = cl.CF_Grademajor.CF_Facultymajor.CF_College.CF_Campus.Name,
                            SchoolAreaID = cl.CF_Grademajor.SchoolAreaID,
                            CollegeID = cl.CF_Grademajor.CF_Facultymajor.CollegeID,
                            CollegeCode = cl.CF_Grademajor.CF_Facultymajor.CF_College.No,
                            CollegeName = cl.CF_Grademajor.CF_Facultymajor.CF_College.Name,
                            SchoolyearID = cl.CF_Grademajor.SchoolyearID,
                            SchoolcodeID = cl.CF_Grademajor.SchoolcodeID,
                            StandardID = cl.CF_Grademajor.CF_Facultymajor.StandardID,
                            EducationID = cl.CF_Grademajor.CF_Facultymajor.EducationID,
                            LearningformID = cl.CF_Grademajor.CF_Facultymajor.LearningformID,
                            LearnSystem = cl.CF_Grademajor.CF_Facultymajor.LearnSystem,
                            GraduatingSemesterID = cl.CF_Grademajor.GraduatingSemesterID,
                            Value = cl.CF_Grademajor.CF_Schoolyear.Value,
                            GraduatingSemesterCode = cl.CF_Grademajor.CF_Schoolyear.Code,
                            UserID = cl.UserID,
                            UserName = guser.Name,
                            AssistantUserID = cl.AssistantUserID,
                            AssistantUserName = gassis.Name,
                            Fixedclassroom = cl.Fixedclassroom,
                            StudentCount = esc.StudentCount == null ? 0 : esc.StudentCount,
                            Remark = cl.Remark,
                            RecordStatus = cl.RecordStatus,
                            CreateUserID = cl.CreateUserID,
                            CreateTime = cl.CreateTime,
                            ModifyUserID = cl.ModifyUserID,
                            ModifyTime = cl.ModifyTime
                        };

            return query;
        }
        
        /// <summary>
        /// 查询班级信息对应的学生信息BaseStudentView
        /// </summary>
        /// <param name="expClassmajor"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<BaseStudentView> GetBaseStudentViewQueryable(Expression<Func<CF_Classmajor, bool>> expClassmajor, 
                Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from cl in ClassmajorRepository.GetList(expClassmajor)
                        join stu in StudentRepository.GetList(expStudent) 
                        on cl.ClassmajorID equals stu.ClassmajorID
                        join gr in GrademajorRepository.Entities
                        on cl.GrademajorID equals gr.GrademajorID
                        join fa in FacultymajorRepository.Entities
                        on gr.FacultymajorID equals fa.FacultymajorID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        select new BaseStudentView
                        {
                            UserID = stu.UserID,
                            LoginID = us.LoginID,
                            StudentNo = stu.StudentCardNo,
                            UserName = us.Name,
                            Sex = stu.Sex,
                            IDNumber = stu.IDNumber,
                            StudentStatus = stu.StudentStatus,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorCode = cl.No,
                            ClassmajorName = cl.Name,
                            ClassNum = cl.ClassNum == null ? 1 : cl.ClassNum,
                            SchoolyearID = gr.SchoolyearID,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name
                        };

            return query;
        }

        public IQueryable<ClassmajorView> GetSocialClassmajor(Expression<Func<CF_SocialClassmajor, bool>> exp)
        {
            var socialClassmajorIDList = this.SocialClassmajorRepository.GetList(exp).Select(x => x.ClassmajorID).ToList();

            return this.GetClassmajorViewQueryable(x => socialClassmajorIDList.Contains(x.ClassmajorID));
        }
    }
}