LIOT/EMiS.DataLogic/Common/Students/StudentsDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Bowin.Common.Linq;
using EMIS.Entities;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel;
using EMIS.ViewModel.SystemView;
using System.Linq.Expressions;
using EMIS.ViewModel.Students;
using System.Data.Entity;
using EMIS.DataLogic.SystemDAL;




namespace EMIS.DataLogic.Common.Students
{
    public class StudentsDAL
    {
        public StudentRepository StudentRepository { get; set; }
        public StudentContactRepository StudentContactRepository { get; set; }
        public StudentAccountRepository StudentAccountRepository { get; set; }
        public DictionaryDAL DictionaryDAL { get; set; }
        public UserRepository UserRepository { get; set; }
        //public EducationMissionRepository EducationMissionRepository { get; set; }//教学任务班主表
        //public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        //public EducationSchedulingClassRepository EducationSchedulingClassRepository { get; set; }
        public InSchoolSettingRepository InSchoolSettingRepository { get; set; }
        public RecruitstudentsRepository RecruitstudentsRepository { get; set; }
        public StudentProfileRepository StudentProfileRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public UniversityRepository universityRepository { get; set; }
        public UniversityProfileRepository universityProfileRepository { get; set; }

        /// <summary>
        /// 获取当前学年学期所有学生信息
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public IQueryable<StudentsView> GetStudentQueryable(Expression<Func<CF_Student, bool>> filter,
            Expression<Func<CF_Classmajor, bool>> classmajorExp = null,
            Expression<Func<CF_Grademajor, bool>> grademajorExp = null)
        {
            if (classmajorExp == null) 
            {
                classmajorExp = (x => true);
            }
            if (grademajorExp == null)
            {
                grademajorExp = (x => true);
            }
            var query = from c in StudentRepository.GetList(filter)
                        join classmajor in ClassmajorRepository.GetList(classmajorExp) on c.ClassmajorID equals classmajor.ClassmajorID 
                        join grade in GrademajorRepository.GetList(grademajorExp) on classmajor.GrademajorID equals grade.GrademajorID 
                        join studentProfile in StudentProfileRepository.Entities on c.UserID equals studentProfile.UserID
                        into cstudentProfile
                        from tcstudentProfile in cstudentProfile.DefaultIfEmpty()
                        join recruitStudent in RecruitstudentsRepository.Entities on c.UserID equals recruitStudent.UserID
                        into crecruitStudent
                        from tcrecruitStudent in crecruitStudent.DefaultIfEmpty()
                        select new StudentsView
                        {
                            UserID = c.UserID,
                            LoginID = c.Sys_User.LoginID,
                            StudentNo = c.StudentCardNo,
                            UserName = c.Sys_User.Name,
                            IDNumber = c.IDNumber,
                            PhotoUrl = c.PhotoUrl,
                            IsProofread = c.IsProofread,
                            IsPhoto = c.PhotoUrl == null ? (int)CF_YesOrNoStatus.No : (int)CF_YesOrNoStatus.Yes,
                            IsPhotoUrl = c.PhotoUrl == null ? "否" : "是",
                            StudentCardNo = c.StudentCardNo,
                            PlanningGraduateDate = c.PlanningGraduateDate,//预计毕业时间
                            EntranceDate = c.CF_Recruitstudents.EntranceDate,
                            EnteringSchoolYearID = c.CF_Recruitstudents.EnteringSchoolYearID,
                            EnteringSchoolYearCode = c.CF_Recruitstudents.CF_Schoolyear.Code,
                            Score = c.CF_Recruitstudents.Score,
                            ExamineeNum = c.CF_Recruitstudents.ExamineeNum,
                            AdmissionTicketNo = c.CF_Recruitstudents.AdmissionTicketNo,
                            Area = c.CF_Recruitstudents.Area,
                            Placebirth = c.CF_Recruitstudents.PlaceBirth,
                            CardNo = c.CF_StudentAccount.CardNo,
                            BankName = c.CF_StudentAccount.BankName,
                            Territorial = tcrecruitStudent.Territorial,
                            Sex = c.Sex,
                            ClassMajorID = c.ClassmajorID,
                            ClassMajorCode = classmajor.No,
                            ClassMajorName = classmajor.Name,
                            Grade = grade.SchoolyearID,
                            StudentStatus = c.StudentStatus,
                            InSchoolStatusID = c.InSchoolStatusID,
                            UsedName = c.CF_StudentProfile.UsedName,
                            DirectorName = c.CF_StudentProfile.DirectorName,
                            BirthDate = c.CF_StudentProfile.BirthDate,
                            Country = c.CF_StudentProfile.Country,
                            Politics = c.CF_StudentProfile.Politics,
                            Place = c.CF_StudentProfile.Place,
                            //BornPlace = c.CF_StudentProfile.BornPlace,
                            Specialty = c.CF_StudentProfile.Specialty,
                            Height = c.CF_StudentProfile.Height,
                            Weight = c.CF_StudentProfile.Weight,
                            Remarks = c.CF_StudentProfile.Remarks,
                            GraduationPictureUrl = c.CF_StudentProfile.GraduationPictureUrl,
                            Email = c.CF_StudentContact.Email,
                            QQ = c.CF_StudentContact.QQ,
                            Mobile = c.CF_StudentContact.Mobile,
                            Telephone = c.CF_StudentContact.Telephone,
                            MicroMsgNo = c.CF_StudentContact.MicroMsgNo,
                            ZipCode = c.CF_StudentContact.Zipcode,
                            Address = c.CF_StudentContact.Address,
                            WorkUnit = c.CF_StudentContact.WorkUnit,
                            HomeAddress = c.CF_StudentContact.HomeAddress,
                            Recipient = c.CF_StudentContact.Recipient,
                            Dormitory = c.CF_StudentContact.Dormitory,
                            LearnSystem = grade.CF_Facultymajor.LearnSystem,
                            CampusID = grade.CF_Facultymajor.CF_College.CampusID,
                            SchoolTypeID = grade.CF_Facultymajor.CF_College.CF_Campus.CF_University.CF_UniversityProfile.SchoolTypeID,
                            CampusName = grade.CF_Facultymajor.CF_College.CF_Campus.Name,
                            CollegeID = grade.CF_Facultymajor.CollegeID,
                            CollegeCode = grade.CF_Facultymajor.CF_College.No,
                            CollegeName = grade.CF_Facultymajor.CF_College.Name,
                            EducationID = grade.CF_Facultymajor.EducationID,
                            StandardID = grade.CF_Facultymajor.StandardID,
                            LearningformID = grade.CF_Facultymajor.LearningformID,
                            GradeMajorID = classmajor.GrademajorID,
                            GradeMajorName = grade.Name,
                            GradeMajorCode = grade.Code,
                            StudentType = c.StudentType,
                            CultureModel = c.CultureModel,
                            BloodGroup = tcstudentProfile.BloodGroup,
                            CertificatesType = c.CertificatesType,
                            EntranceWay = tcrecruitStudent.EntranceWay,
                            ExamineeType = tcrecruitStudent.ExamineeType,
                            Features = tcrecruitStudent.Features,
                            Healthy = tcstudentProfile.Healthy,
                            Nation = tcstudentProfile.Nation,
                            FacultymajorName = grade.CF_Facultymajor.Name,//院系所专业
                            GraduateCardNo=c.GraduateCardNo,
                            GraduateDate = c.GraduateDate,
                            IsDreamProject = c.IsDreamProject == true,
                            Career=c.Career,
                            ReplaceGraduateNo=c.ReplaceGraduateNo,
                            LiteracyLevels = c.CF_StudentProfile.LiteracyLevels,
                            Years = c.CF_Classmajor.CF_Grademajor.SchoolyearID,
                            SchoolAreaID = grade.SchoolAreaID,
                        };
            return query;
        }

        public IQueryable<BaseStudentView> GetBaseStudentViewQueryable(Expression<Func<CF_Student, bool>> filter)
        {
            var query = from c in StudentRepository.GetList(filter)
                        join studentProfile in StudentProfileRepository.Entities on c.UserID equals studentProfile.UserID
                        join recruitStudent in RecruitstudentsRepository.Entities on c.UserID equals recruitStudent.UserID
                        select new BaseStudentView
                        {
                            UserID = c.UserID,
                            LoginID = c.Sys_User.LoginID,
                            StudentNo = c.StudentCardNo,
                            UserName = c.Sys_User.Name,
                            Sex = c.Sex,
                            IDNumber = c.IDNumber,
                            InSchoolStatusID = c.InSchoolStatusID,
                            StudentStatus = c.StudentStatus,
                            ClassmajorID = c.CF_Classmajor.ClassmajorID,
                            ClassmajorCode = c.CF_Classmajor.No,
                            ClassmajorName = c.CF_Classmajor.Name,
                            GrademajorID = c.CF_Classmajor.GrademajorID,
                            GrademajorName = c.CF_Classmajor.CF_Grademajor.Name,
                            CollegeID = c.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
                            Years = c.CF_Classmajor.CF_Grademajor.SchoolyearID,
                            // GraduateCardNo=c.GraduateCardNo
                        };
            return query;
        }

        public IQueryable<BaseStudentView> GetInSchoolStudentViewQueryable(Expression<Func<CF_Student, bool>> filter)
        {
            var query = from c in StudentRepository.GetList(filter)
                        select new BaseStudentView
                        {
                            UserID = c.UserID,
                            LoginID = c.Sys_User.LoginID,
                            UserName = c.Sys_User.Name,
                            Sex = c.Sex,
                            StudentNo = c.StudentCardNo,
                            IDNumber = c.IDNumber,
                            InSchoolStatusID = c.InSchoolStatusID,
                            StudentStatus = c.StudentStatus,
                            ClassmajorID = c.CF_Classmajor.ClassmajorID,
                            ClassmajorCode = c.CF_Classmajor.No,
                            ClassmajorName = c.CF_Classmajor.Name,
                            GrademajorID = c.CF_Classmajor.GrademajorID,
                            GrademajorName = c.CF_Classmajor.CF_Grademajor.Name,
                            CollegeID = c.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
                            CollegeName = c.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CF_College.Name,
                            Years = c.CF_Classmajor.CF_Grademajor.SchoolyearID,
                            SchoolyearID = c.CF_Classmajor.CF_Grademajor.SchoolyearID,
                            //GraduateCardNo=c.GraduateCardNo
                        };
            return query;
        }

        public IQueryable<BaseStudentView> GetBaseStudentViewQueryable(Expression<Func<CF_Student, bool>> studentExp,
            Expression<Func<CF_Grademajor, bool>> gradeExp, Expression<Func<CF_Facultymajor, bool>> facultyExp)
        {
            var q = from student in this.GetBaseStudentViewQueryable(studentExp)
                    join classmajor in ClassmajorRepository.Entities on student.ClassmajorID equals classmajor.ClassmajorID
                    join grade in GrademajorRepository.Entities.Where(gradeExp) on classmajor.GrademajorID equals grade.GrademajorID
                    join faculty in FacultymajorRepository.Entities.Where(facultyExp) on grade.FacultymajorID equals faculty.FacultymajorID
                    select student;

            return q;
        }

        //计算预计毕业设计时间
        public StudentsView GetPlanningGraduateDate(Guid GrademajorID)
        {
            var query = from g in GrademajorRepository.Entities.Where(x => x.GrademajorID == GrademajorID && x.GraduatingSemesterID != null)
                        join f in FacultymajorRepository.Entities on g.FacultymajorID equals f.FacultymajorID
                        join sn in SchoolyearRepository.Entities on new { Value = (int)((g.SchoolyearID * 2 - 1) + (g.SchoolcodeID - 1) + (f.LearnSystem * 2 - 1)) } equals new { Value = (int)sn.Value }
                        into tsn
                        from ctsn in tsn.DefaultIfEmpty()
                        select new StudentsView { PlanningGraduateDate = (DateTime)ctsn.FirstWeek, WeeksNum = ctsn.WeeksNum * 7 };
            return query.FirstOrDefault();
        }

        //计算预计毕业设计时间 方案二 2016年10月25日14:19:25
        //public StudentsView GetPlanningGraduate(Guid GrademajorID)
        //{
        //    var query = from g in GrademajorRepository.Entities.Where(x => x.GrademajorID == GrademajorID && x.GraduatingSemesterID != null)
        //                join f in FacultymajorRepository.Entities on g.FacultymajorID equals f.FacultymajorID
        //                select new StudentsView { LearnSystem = f.LearnSystem, Years=g.SchoolyearID };
        //    return query.FirstOrDefault();
        //}
        public StudentsView GetPlanningGraduate(Guid GrademajorID)
        {
            var query = from g in GrademajorRepository.Entities.Where(x => x.GrademajorID == GrademajorID && x.GraduatingSemesterID != null)
                        join f in FacultymajorRepository.Entities on g.FacultymajorID equals f.FacultymajorID
                        join s in SchoolyearRepository.Entities on g.GraduatingSemesterID equals s.SchoolyearID
                        join d in DictionaryDAL.GetDictionaryItemQuery(typeof(CF_Schoolcode).Name) on s.SchoolcodeID equals d.Value into dv
                        from d in dv.DefaultIfEmpty()
                        select new StudentsView { 
                            LearnSystem = f.LearnSystem,
                            Years = g.SchoolyearID, 
                            Grade = g.SchoolyearID, 
                            PlanningGraduateYear=s.Years,
                            PlanningGraduateSchoolYearID=g.GraduatingSemesterID,
                            PlanningGraduateSchoolCodeID=s.SchoolcodeID,
                            PlanningGraduateSchoolCode = d.Value,
                            PlanningGraduateSchoolCodeName = d.Name,
                        };
            return query.FirstOrDefault();
        }

        public IQueryable<StudentsView> GetPlanningGraduateDictionary()
        {
            var inSchoolStatusIDList = InSchoolSettingRepository.GetList(x => x.IsSelected == true).Select(w => w.InSchoolStatusID).ToList();//获取选中的在校设定状态、
            var query = from a in StudentRepository.GetList(x => inSchoolStatusIDList.Contains(x.InSchoolStatusID))
                        group a by a.PlanningGraduateDate
                        into b
                        select new StudentsView
                        {
                            PlanningGraduateDate = b.Key,
                            //PlanningGraduateStr = b.Key.Value,   
                        };
            return query;
        }


        /// <summary>
        /// 获取当前学年学期所有学生信息
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public IQueryable<StudentsView> GetStudentForExcel(Expression<Func<CF_Student, bool>> filter,
            Expression<Func<CF_Classmajor, bool>> classmajorExp = null,
            Expression<Func<CF_Grademajor, bool>> grademajorExp = null)
            {
                if (classmajorExp == null)
                {
                    classmajorExp = (x => true);
                }
                if (grademajorExp == null)
                {
                    grademajorExp = (x => true);
                }
                var schoolquery = from uni in universityRepository.Entities
                                  join unip in universityProfileRepository.Entities
                                  on uni.UniversityID equals unip.UniversityID
                                  select new
                                  {
                                      SchoolCode = uni.Code,
                                      SchoolName = uni.Name,
                                      HeadMasterName = unip.HeadMasterName,
                                  };
                var query = from c in StudentRepository.GetList(filter)
                            join classmajor in ClassmajorRepository.GetList(classmajorExp) on c.ClassmajorID equals classmajor.ClassmajorID
                            join grade in GrademajorRepository.GetList(grademajorExp) on classmajor.GrademajorID equals grade.GrademajorID
                            join studentProfile in StudentProfileRepository.Entities on c.UserID equals studentProfile.UserID
                            into cstudentProfile
                            from tcstudentProfile in cstudentProfile.DefaultIfEmpty()
                            join recruitStudent in RecruitstudentsRepository.Entities on c.UserID equals recruitStudent.UserID
                            into crecruitStudent
                            from tcrecruitStudent in crecruitStudent.DefaultIfEmpty()
                            select new StudentsView
                            {
                                UserID = c.UserID,
                                LoginID = c.Sys_User.LoginID,
                                StudentNo = c.StudentCardNo,
                                UserName = c.Sys_User.Name,
                                IDNumber = c.IDNumber,
                                PhotoUrl = c.PhotoUrl,
                                IsProofread = c.IsProofread,
                                IsPhoto = c.PhotoUrl == null ? (int)CF_YesOrNoStatus.No : (int)CF_YesOrNoStatus.Yes,
                                IsPhotoUrl = c.PhotoUrl == null ? "否" : "是",
                                StudentCardNo = c.StudentCardNo,
                                PlanningGraduateDate = c.PlanningGraduateDate,//预计毕业时间
                                EntranceDate = c.CF_Recruitstudents.EntranceDate,
                                EnteringSchoolYearID = c.CF_Recruitstudents.EnteringSchoolYearID,
                                EnteringSchoolYearCode = c.CF_Recruitstudents.CF_Schoolyear.Code,
                                Score = c.CF_Recruitstudents.Score,
                                ExamineeNum = c.CF_Recruitstudents.ExamineeNum,
                                AdmissionTicketNo = c.CF_Recruitstudents.AdmissionTicketNo,
                                Area = c.CF_Recruitstudents.Area,
                                Placebirth = c.CF_Recruitstudents.PlaceBirth,
                                CardNo = c.CF_StudentAccount.CardNo,
                                BankName = c.CF_StudentAccount.BankName,
                                Territorial = tcrecruitStudent.Territorial,
                                Sex = c.Sex,
                                ClassMajorID = c.ClassmajorID,
                                ClassMajorCode = classmajor.No,
                                ClassMajorName = classmajor.Name,
                                ClassNum = classmajor.ClassNum,
                                Grade = grade.SchoolyearID,
                                StudentStatus = c.StudentStatus,
                                InSchoolStatusID = c.InSchoolStatusID,
                                UsedName = c.CF_StudentProfile.UsedName,
                                DirectorName = c.CF_StudentProfile.DirectorName,
                                BirthDate = c.CF_StudentProfile.BirthDate,
                                Country = c.CF_StudentProfile.Country,
                                Politics = c.CF_StudentProfile.Politics,
                                Place = c.CF_StudentProfile.Place,
                                //BornPlace = c.CF_StudentProfile.BornPlace,
                                Specialty = c.CF_StudentProfile.Specialty,
                                Height = c.CF_StudentProfile.Height,
                                Weight = c.CF_StudentProfile.Weight,
                                Remarks = c.CF_StudentProfile.Remarks,
                                GraduationPictureUrl = c.CF_StudentProfile.GraduationPictureUrl,
                                Email = c.CF_StudentContact.Email,
                                QQ = c.CF_StudentContact.QQ,
                                Mobile = c.CF_StudentContact.Mobile,
                                Telephone = c.CF_StudentContact.Telephone,
                                MicroMsgNo = c.CF_StudentContact.MicroMsgNo,
                                ZipCode = c.CF_StudentContact.Zipcode,
                                Address = c.CF_StudentContact.Address,
                                WorkUnit = c.CF_StudentContact.WorkUnit,
                                HomeAddress = c.CF_StudentContact.HomeAddress,
                                Recipient = c.CF_StudentContact.Recipient,
                                Dormitory = c.CF_StudentContact.Dormitory,
                                LearnSystem = grade.CF_Facultymajor.LearnSystem,
                                CampusID = grade.CF_Facultymajor.CF_College.CampusID,
                                SchoolTypeID = grade.CF_Facultymajor.CF_College.CF_Campus.CF_University.CF_UniversityProfile.SchoolTypeID,
                                CampusName = grade.CF_Facultymajor.CF_College.CF_Campus.Name,
                                CollegeID = grade.CF_Facultymajor.CollegeID,
                                CollegeCode = grade.CF_Facultymajor.CF_College.No,
                                CollegeName = grade.CF_Facultymajor.CF_College.Name,
                                EducationID = grade.CF_Facultymajor.EducationID,
                                StandardID = grade.CF_Facultymajor.StandardID,
                                LearningformID = grade.CF_Facultymajor.LearningformID,
                                GradeMajorID = classmajor.GrademajorID,
                                GradeMajorName = grade.Name,
                                GradeMajorCode = grade.Code,
                                StudentType = c.StudentType,
                                CultureModel = c.CultureModel,
                                BloodGroup = tcstudentProfile.BloodGroup,
                                CertificatesType = c.CertificatesType,
                                EntranceWay = tcrecruitStudent.EntranceWay,
                                ExamineeType = tcrecruitStudent.ExamineeType,
                                Features = tcrecruitStudent.Features,
                                Healthy = tcstudentProfile.Healthy,
                                Nation = tcstudentProfile.Nation,
                                FacultymajorName = grade.CF_Facultymajor.Name,//院系所专业
                                GraduateCardNo = c.GraduateCardNo,
                                IsDreamProject = c.IsDreamProject == true,
                                Career = c.Career,
                                SchoolCode = schoolquery.FirstOrDefault().SchoolCode,
                                SchoolName = schoolquery.FirstOrDefault().SchoolName,
                                HeadMasterName = schoolquery.FirstOrDefault().HeadMasterName
                            };
                return query;
            }

        public IQueryable<RegistView> GetRegustViewQueryable(Expression<Func<CF_Student, bool>> exp)
        {
            var query = (from student in StudentRepository.GetList(exp)
                         from contact in StudentContactRepository.Entities.Where(x => x.UserID == student.UserID)
                         from user in UserRepository.Entities.Where(x => x.UserID == student.UserID)
                         select new RegistView
                         {
                             UserID = user.UserID,
                             LoginID = student.IDNumber,
                             CertificatesType = student.CertificatesType,
                             UserName = user.Name,
                             Mobile = contact.Mobile,
                             Email = contact.Email,
                             PhotoUrl = student.PhotoUrl,
                             Sex = student.Sex,
                             BirthDate = student.CF_StudentProfile.BirthDate,
                         });

            return query;
        }
    }
}