JW/EMiS.DataLogic/StudentManage/StudentProfile/StudentDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel;
using EMIS.ViewModel.StudentManage.StudentProfile;

namespace EMIS.DataLogic.StudentManage.StudentProfile
{
    public class StudentDAL
    {
        public StudentRepository StudentRepository { get; set; }
        public StudentProfileRepository StudentProfileRepository { get; set; }
        public StudentContactRepository StudentContactRepository { get; set; }
        public StudentAccountRepository StudentAccountRepository { get; set; }
        public RecruitstudentsRepository RecruitstudentsRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public CampusRepository CampusRepository { get; set; }
        public UniversityRepository UniversityRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public DifferentDynamicRepository DifferentDynamicRepository { get; set; }
        public StudentReportRepository StudentReportRepository { get; set; }
        public StudentConductRepository StudentConductRepository { get; set; }
        public StudentPostPracticeRepository StudentPostPracticeRepository { get; set; }
        public StaffRepository StaffRepository { get; set; }
        public NewStudentRepository NewStudentRepository { get; set; }
        public ParameterRepository ParameterRepository { get; set; }
        public RoleRepository RoleRepository { get; set; }
        public GraduateSchoolyearRepository GraduateSchoolyearRepository { get; set; }

        /// <summary>
        /// 查询对应的学生信息StudentBaseView（根据对应的学生信息）
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<StudentBaseView> GetStudentBaseViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from stu in StudentRepository.GetList(expStudent)
                        join cl in ClassmajorRepository.Entities
                        on stu.ClassmajorID equals cl.ClassmajorID
                        join gr in GrademajorRepository.Entities
                        on cl.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 us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join stsy in SchoolyearRepository.Entities
                        on new { SchoolyearID = gr.GradeID.Value, SchoolcodeID = gr.SemesterID.Value }
                        equals new { SchoolyearID = stsy.Years, SchoolcodeID = stsy.SchoolcodeID } into tempstsy
                        from startsy in tempstsy.DefaultIfEmpty()
                        join grsy in SchoolyearRepository.Entities
                        on gr.GraduateSchoolyearID equals grsy.SchoolyearID into tempgrsy
                        from grasy in tempgrsy.DefaultIfEmpty()
                        select new StudentBaseView
                        {
                            UserID = stu.UserID,
                            StudentNo = us.LoginID,
                            LoginID = us.LoginID,
                            Name = us.Name,
                            UserName = us.Name,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorNo = cl.No,
                            ClassmajorName = cl.Name,
                            ClassNum = cl.ClassNum,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            GradeID = gr.GradeID,
                            SemesterID = gr.SemesterID,
                            StartSchoolyearID = startsy.SchoolyearID,
                            StartSchoolyearValue = startsy.Value,
                            StartSchoolyearCode = startsy.Code,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            GraduateSchoolyearID = gr.GraduateSchoolyearID,
                            GraduateSchoolyearValue = grasy.Value,
                            GraduateSchoolyearCode = grasy.Code,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            CampusID = col.CampusID,
                            CampusCode = cam.No,
                            CampusName = cam.Name,
                            SexID = stu.SexID,
                            BirthDate = stu.BirthDate,
                            NationID = stu.NationID,
                            PoliticsID = stu.PoliticsID,
                            CertificatesType = stu.CertificatesType,
                            IDNumber = stu.IDNumber,
                            StudentType = stu.StudentType,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatus = stu.StudentStatus,
                            PlanningGraduateDate = stu.PlanningGraduateDate,
                            GraduateDate = stu.GraduateDate
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的学生信息View（根据对应的学生信息）
        /// </summary>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<StudentView> GetStudentViewQueryable(Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from stu in StudentRepository.GetList(expStudent)
                        join pf in StudentProfileRepository.Entities
                        on stu.UserID equals pf.UserID into temppf
                        from stupf in temppf.DefaultIfEmpty()
                        join ct in StudentContactRepository.Entities
                        on stu.UserID equals ct.UserID into tempct
                        from stuct in tempct.DefaultIfEmpty()
                        join ac in StudentAccountRepository.Entities
                        on stu.UserID equals ac.UserID into tempac
                        from stuac in tempac.DefaultIfEmpty()
                        join rt in RecruitstudentsRepository.Entities
                        on stu.UserID equals rt.UserID into temprt
                        from sturt in temprt.DefaultIfEmpty()
                        join cl in ClassmajorRepository.Entities
                        on stu.ClassmajorID equals cl.ClassmajorID
                        join gr in GrademajorRepository.Entities
                        on cl.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 un in UniversityRepository.Entities
                        on cam.UniversityID equals un.UniversityID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join stsy in SchoolyearRepository.Entities
                        on new { SchoolyearID = gr.GradeID.Value, SchoolcodeID = gr.SemesterID.Value }
                        equals new { SchoolyearID = stsy.Years, SchoolcodeID = stsy.SchoolcodeID } into tempstsy
                        from startsy in tempstsy.DefaultIfEmpty()
                        join grsy in SchoolyearRepository.Entities
                        on gr.GraduateSchoolyearID equals grsy.SchoolyearID into tempgrsy
                        from grasy in tempgrsy.DefaultIfEmpty()
                        from stureport in stu.EM_StudentReport.AsQueryable().Where(x => x.CF_Schoolyear.IsCurrent).DefaultIfEmpty()
                        select new StudentView
                        {
                            UserID = stu.UserID,
                            StudentNo = us.LoginID,
                            LoginID = us.LoginID,
                            ExamineeNum = sturt.ExamineeNum,
                            AdmissionTicketNo = sturt.AdmissionTicketNo,
                            Name = us.Name,
                            UserName = us.Name,
                            UsedName = stupf.UsedName,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorNo = cl.No,
                            ClassmajorName = cl.Name,
                            ClassNum = cl.ClassNum,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            YearID = gr.GradeID,
                            GradeID = gr.GradeID,
                            SemesterID = gr.SemesterID,
                            StartSchoolyearID = startsy.SchoolyearID,
                            StartSchoolyearValue = startsy.Value,
                            StartSchoolyearCode = startsy.Code,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            GraduateSchoolyearID = gr.GraduateSchoolyearID,
                            GraduateSchoolyearValue = grasy.Value,
                            GraduateSchoolyearCode = grasy.Code,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            CampusID = col.CampusID,
                            CampusCode = cam.No,
                            CampusName = cam.Name,
                            UniversityID = cam.UniversityID,
                            UniversityCode = un.Code,
                            UniversityName = un.Name,
                            HeadMasterName = un.CF_UniversityProfile.HeadMasterName,
                            Country = stupf.Country,
                            Place = stupf.Place,
                            BornPlace = stupf.BornPlace,
                            SexID = stu.SexID,
                            BirthDate = stu.BirthDate,
                            NationID = stu.NationID,
                            PoliticsID = stu.PoliticsID,
                            CertificatesType = stu.CertificatesType,
                            IDNumber = stu.IDNumber,
                            StudentType = stu.StudentType,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatus = stu.StudentStatus,
                            ExamineeType = sturt.ExamineeType,
                            EntranceDate = sturt.EntranceDate,
                            LiteracyLevelID = stupf.LiteracyLevelID,
                            SchoolTypeID = un.CF_UniversityProfile.SchoolTypeID,
                            CultureModelID = stupf.CultureModelID,
                            IsDreamProject = stupf.IsDreamProject ?? false,
                            Email = stuct.Email,
                            Telephone = stuct.Telephone,
                            Mobile = stuct.Mobile,
                            ZIPCode = stuct.ZIPCode,
                            WeChatNum = stuct.WeChatNum,
                            QQ = stuct.QQ,
                            HealthStateID = stupf.HealthStateID,
                            BloodGroup = stupf.BloodGroup,
                            Specialty = stupf.Specialty,
                            Height = stupf.Height,
                            Weight = stupf.Weight,
                            DirectorName = stupf.DirectorName,
                            Score = sturt.Score,
                            EntranceWayID = sturt.EntranceWayID,
                            FeaturesID = sturt.FeaturesID,
                            TerritorialID = sturt.TerritorialID,
                            Area = sturt.Area,
                            HomeAddress = stuct.HomeAddress,
                            WorkUnit = stuct.WorkUnit,
                            Address = stuct.Address,
                            Recipient = stuct.Recipient,
                            Dormitory = stuct.Dormitory,
                            BankName = stuac.BankName,
                            CardNo = stuac.CardNo,
                            RecruitPictureUrl = sturt.RecruitPictureUrl,
                            RecruitPictureHasValue = (sturt.RecruitPictureUrl == null || sturt.RecruitPictureUrl == "") ? false : true,
                            StudentCardNo = stu.StudentCardNo,
                            PhotoUrl = stu.PhotoUrl,
                            PhotoHasValue = (stu.PhotoUrl == null || stu.PhotoUrl == "") ? false : true,
                            PlanningGraduateDate = stu.PlanningGraduateDate,
                            GraduateDate = stu.GraduateDate,
                            GraduationResult = stu.InSchoolStatusID == (int)EMIS.ViewModel.CF_InschoolStatus.Graduation ? 
                                (int)EMIS.ViewModel.ER_GraduationResult.Graduation : (int)EMIS.ViewModel.ER_GraduationResult.Completion,
                            GraduateCardNo = stu.GraduateCardNo,
                            ReplaceGraduateNo = stu.ReplaceGraduateNo,
                            DegreeStatus = stu.DegreeStatus ?? false,
                            GraduationPictureUrl = stu.GraduationPictureUrl,
                            GraduationPictureHasValue = (stu.GraduationPictureUrl == null || stu.GraduationPictureUrl == "") ? false : true,
                            Career = stu.Career,
                            IsPhotoComparison = stu.IsPhotoComparison ?? false,
                            IsProofread = stu.IsProofread ?? false,
                            ReportStatus = stureport.ReportStatus == null ?
                                (stu.InSchoolStatusID == (int)EMIS.ViewModel.CF_InschoolStatus.Graduation ?
                                (int)EMIS.ViewModel.CF_ReportStatus.Graduation : (int)EMIS.ViewModel.CF_ReportStatus.WaitReport) :
                                stureport.ReportStatus,
                            AccountStatus = us.RecordStatus,
                            Remark = stu.Remark,
                            RecordStatus = stu.RecordStatus,
                            CreateUserID = stu.CreateUserID,
                            CreateTime = stu.CreateTime,
                            ModifyUserID = stu.ModifyUserID,
                            ModifyTime = stu.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的学生信息View（根据对应的学生信息、异动申请信息,统计相应的异动数）
        /// </summary>
        /// <param name="expStudent"></param>
        /// <param name="expChange"></param>
        /// <returns></returns>
        public IQueryable<StudentView> GetStudentViewQueryable(Expression<Func<CF_Student, bool>> expStudent, Expression<Func<CF_DifferentDynamic, bool>> expChange)
        {
            var query = from stu in StudentRepository.GetList(expStudent)
                        join pf in StudentProfileRepository.Entities
                        on stu.UserID equals pf.UserID into temppf
                        from stupf in temppf.DefaultIfEmpty()
                        join ct in StudentContactRepository.Entities
                        on stu.UserID equals ct.UserID into tempct
                        from stuct in tempct.DefaultIfEmpty()
                        join ac in StudentAccountRepository.Entities
                        on stu.UserID equals ac.UserID into tempac
                        from stuac in tempac.DefaultIfEmpty()
                        join rt in RecruitstudentsRepository.Entities
                        on stu.UserID equals rt.UserID into temprt
                        from sturt in temprt.DefaultIfEmpty()
                        join cl in ClassmajorRepository.Entities
                        on stu.ClassmajorID equals cl.ClassmajorID
                        join gr in GrademajorRepository.Entities
                        on cl.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 un in UniversityRepository.Entities
                        on cam.UniversityID equals un.UniversityID
                        join us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join stsy in SchoolyearRepository.Entities
                        on new { SchoolyearID = gr.GradeID.Value, SchoolcodeID = gr.SemesterID.Value }
                        equals new { SchoolyearID = stsy.Years, SchoolcodeID = stsy.SchoolcodeID } into tempstsy
                        from startsy in tempstsy.DefaultIfEmpty()
                        join grsy in SchoolyearRepository.Entities
                        on gr.GraduateSchoolyearID equals grsy.SchoolyearID into tempgrsy
                        from grasy in tempgrsy.DefaultIfEmpty()
                        join cegr in
                            (
                                from ce in DifferentDynamicRepository.GetList(expChange)
                                group ce by ce.UserID into g
                                select new
                                {
                                    UserID = g.Key,
                                    ChangeCount = g.Count()
                                }
                            ) on stu.UserID equals cegr.UserID into tempce
                        from stuce in tempce.DefaultIfEmpty()
                        from stureport in stu.EM_StudentReport.AsQueryable()
                            .Where(x => x.CF_Schoolyear.IsCurrent).DefaultIfEmpty()
                        select new StudentView
                        {
                            UserID = stu.UserID,
                            StudentNo = us.LoginID,
                            LoginID = us.LoginID,
                            ExamineeNum = sturt.ExamineeNum,
                            AdmissionTicketNo = sturt.AdmissionTicketNo,
                            Name = us.Name,
                            UserName = us.Name,
                            UsedName = stupf.UsedName,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorNo = cl.No,
                            ClassmajorName = cl.Name,
                            ClassNum = cl.ClassNum,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            YearID = gr.GradeID,
                            GradeID = gr.GradeID,
                            SemesterID = gr.SemesterID,
                            StartSchoolyearID = startsy.SchoolyearID,
                            StartSchoolyearValue = startsy.Value,
                            StartSchoolyearCode = startsy.Code,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            GraduateSchoolyearID = gr.GraduateSchoolyearID,
                            GraduateSchoolyearValue = grasy.Value,
                            GraduateSchoolyearCode = grasy.Code,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            CampusID = col.CampusID,
                            CampusCode = cam.No,
                            CampusName = cam.Name,
                            UniversityID = cam.UniversityID,
                            UniversityCode = un.Code,
                            UniversityName = un.Name,
                            HeadMasterName = un.CF_UniversityProfile.HeadMasterName,
                            Country = stupf.Country,
                            Place = stupf.Place,
                            BornPlace = stupf.BornPlace,
                            SexID = stu.SexID,
                            BirthDate = stu.BirthDate,
                            NationID = stu.NationID,
                            PoliticsID = stu.PoliticsID,
                            CertificatesType = stu.CertificatesType,
                            IDNumber = stu.IDNumber,
                            StudentType = stu.StudentType,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatus = stu.StudentStatus,
                            ExamineeType = sturt.ExamineeType,
                            EntranceDate = sturt.EntranceDate,
                            LiteracyLevelID = stupf.LiteracyLevelID,
                            SchoolTypeID = un.CF_UniversityProfile.SchoolTypeID,
                            CultureModelID = stupf.CultureModelID,
                            IsDreamProject = stupf.IsDreamProject ?? false,
                            Email = stuct.Email,
                            Telephone = stuct.Telephone,
                            Mobile = stuct.Mobile,
                            ZIPCode = stuct.ZIPCode,
                            WeChatNum = stuct.WeChatNum,
                            QQ = stuct.QQ,
                            HealthStateID = stupf.HealthStateID,
                            BloodGroup = stupf.BloodGroup,
                            Specialty = stupf.Specialty,
                            Height = stupf.Height,
                            Weight = stupf.Weight,
                            DirectorName = stupf.DirectorName,
                            Score = sturt.Score,
                            EntranceWayID = sturt.EntranceWayID,
                            FeaturesID = sturt.FeaturesID,
                            TerritorialID = sturt.TerritorialID,
                            Area = sturt.Area,
                            HomeAddress = stuct.HomeAddress,
                            WorkUnit = stuct.WorkUnit,
                            Address = stuct.Address,
                            Recipient = stuct.Recipient,
                            Dormitory = stuct.Dormitory,
                            BankName = stuac.BankName,
                            CardNo = stuac.CardNo,
                            RecruitPictureUrl = sturt.RecruitPictureUrl,
                            RecruitPictureHasValue = (sturt.RecruitPictureUrl == null || sturt.RecruitPictureUrl == "") ? false : true,
                            StudentCardNo = stu.StudentCardNo,
                            PhotoUrl = stu.PhotoUrl,
                            PhotoHasValue = (stu.PhotoUrl == null || stu.PhotoUrl == "") ? false : true,
                            PlanningGraduateDate = stu.PlanningGraduateDate,
                            GraduateDate = stu.GraduateDate,
                            GraduationResult = stu.InSchoolStatusID == (int)EMIS.ViewModel.CF_InschoolStatus.Graduation ?
                                (int)EMIS.ViewModel.ER_GraduationResult.Graduation : (int)EMIS.ViewModel.ER_GraduationResult.Completion,
                            GraduateCardNo = stu.GraduateCardNo,
                            ReplaceGraduateNo = stu.ReplaceGraduateNo,
                            DegreeStatus = stu.DegreeStatus ?? false,
                            GraduationPictureUrl = stu.GraduationPictureUrl,
                            GraduationPictureHasValue = (stu.GraduationPictureUrl == null || stu.GraduationPictureUrl == "") ? false : true,
                            Career = stu.Career,
                            IsPhotoComparison = stu.IsPhotoComparison ?? false,
                            IsProofread = stu.IsProofread ?? false,
                            ChangeCount = stuce.ChangeCount == null ? 0 : stuce.ChangeCount,
                            ReportStatus = stureport.ReportStatus == null ? 
                                (stu.InSchoolStatusID == (int)EMIS.ViewModel.CF_InschoolStatus.Graduation ? 
                                (int)EMIS.ViewModel.CF_ReportStatus.Graduation : (int)EMIS.ViewModel.CF_ReportStatus.WaitReport) : 
                                stureport.ReportStatus,
                            AccountStatus = us.RecordStatus,
                            Remark = stu.Remark,
                            RecordStatus = stu.RecordStatus,
                            CreateUserID = stu.CreateUserID,
                            CreateTime = stu.CreateTime,
                            ModifyUserID = stu.ModifyUserID,
                            ModifyTime = stu.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的学生预计毕业时间（根据对应的学生信息）
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<StudentView> GetStudentPlanningGraduateDateQueryable(Expression<Func<CF_Student, bool>> exp)
        {
            var query = from stu in StudentRepository.GetList(exp)
                        group stu by stu.PlanningGraduateDate into g
                        select new StudentView
                        {
                            PlanningGraduateDate = g.Key
                        };

            return query;
        }

        /// <summary>
        /// 查询必填字段为空的记录行
        /// </summary>
        /// <param name="dataSource"></param>
        /// <param name="dic"></param>
        /// <returns></returns>
        public List<ImportErrorRowView> GetExceptEmptyDataRow(DataTable dataSource, Dictionary<string, string> dic)
        {
            var query = (from ds in dataSource.AsEnumerable()
                         from pair in dic
                         where string.IsNullOrEmpty(ds.Field<string>(pair.Key))
                         group pair by ds into g
                         select new ImportErrorRowView { Row = g.Key, Error = string.Join(",", g.Select(x => x.Value)) }).ToList();

            return query;
        }

    }
}