LIOT/EMiS.DataLogic/StudentSystem/StudentMaterial/StudentRecordDAL.cs

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

namespace EMIS.DataLogic.StudentSystem.StudentMaterial
{
    public class StudentRecordDAL
    {
        public StudentRepository StudentRepository { get; set; }
        public StudentProfileRepository ProfileRepository { get; set; }
        public StudentContactRepository ContactRepository { get; set; }
        public RecruitstudentsRepository RecruitsRepository { get; set; }
        public ClassmajorRepository ClassRepository { get; set; }
        public GrademajorRepository GradeRepository { get; set; }
        public FacultymajorRepository FacultyRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }

        public StudentRecordView GetStudentByStudentNo(Guid StudentID)
        {
            var query = from student in StudentRepository.Entities
                        join profile in ProfileRepository.Entities on student.UserID equals profile.UserID
                        join contact in ContactRepository.Entities on profile.UserID equals contact.UserID
                        join recruits in RecruitsRepository.Entities on contact.UserID equals recruits.UserID
                        join cla in ClassRepository.Entities on student.ClassmajorID equals cla.ClassmajorID
                        join grade in GradeRepository.Entities on cla.GrademajorID equals grade.GrademajorID
                        join faculty in FacultyRepository.Entities on grade.FacultymajorID equals faculty.FacultymajorID
                        join college in CollegeRepository.Entities on faculty.CollegeID equals college.CollegeID
                        join schoolyear in SchoolyearRepository.Entities
                            on new { Years = grade.SchoolyearID, grade.SchoolcodeID }
                            equals new { Years = (int?)schoolyear.Years, SchoolcodeID = (int?)schoolyear.SchoolcodeID } into dschoolyear 
                        from schoolyear in dschoolyear.DefaultIfEmpty() 
                        where student.UserID == StudentID
                        select new StudentRecordView()
                        {
                            IsDreamProject=student.IsDreamProject==true,
                            Career=student.Career,
                            CardNo=student.CF_StudentAccount.CardNo,
                            BankName=student.CF_StudentAccount.BankName,
                            UserID = student.UserID,
                            LoginID = student.Sys_User.LoginID,
                            StudentNo = student.StudentCardNo,
                            UserName = student.Sys_User.Name,
                            PhotoUrl = student.PhotoUrl,
                            UsedName = profile.UsedName,
                            Sex = student.Sex.Value,
                            BirthDate = profile.BirthDate,
                            Nation = profile.Nation,
                            Politics = profile.Politics,
                            CertificatesType = student.CertificatesType,
                            IDNumber = student.IDNumber,
                            CultureModel = student.CultureModel,
                            StudentType = student.StudentType,
                            InSchoolStatusID=student.InSchoolStatusID,
                            StudentStatus = student.StudentStatus,
                            EntranceDate = recruits.EntranceDate,
                            EntranceWay = recruits.EntranceWay,
                            ExamineeNum = recruits.ExamineeNum,
                            ExamineeType = recruits.ExamineeType,
                            Features = recruits.Features,
                            CollegeName = college.Name,
                            Score = recruits.Score,
                            Territorial = recruits.Territorial,
                            Area = student.CF_Recruitstudents.Area,
                            Place = profile.Place,
                            BornPlace =student.CF_Recruitstudents.PlaceBirth,
                            Healthy = profile.Healthy,
                            Specialty = profile.Specialty,
                            Height = profile.Height,
                            Weight = profile.Weight,
                            Email = contact.Email,
                            QQ = contact.QQ,
                            Dormitory = contact.Dormitory,
                            Telephone = contact.Telephone,
                            Mobile = contact.Mobile,
                            HomeAddress = contact.HomeAddress,
                            WorkUnit = contact.WorkUnit,
                            Zipcode = contact.Zipcode,
                            Recipient = contact.Recipient,
                            PlanningGraduateDate = student.PlanningGraduateDate,
                            GradeName = grade.Name,
                            GradeCode = grade.Code,
                            StandardID = grade.CF_Facultymajor.StandardID,
                            ClassID=cla.ClassmajorID,
                            ClassName = cla.Name,
                            LearnSystem = faculty.LearnSystem,
                            No = cla.No,
                            EducationID = faculty.EducationID,
                            LearningformID = faculty.LearningformID,
                            LearningstyleID = faculty.LearningstyleID,
                            Remarks = profile.Remarks,
                            FacultymajorID = grade.FacultymajorID,
                            StartSchoolcodeID = grade.SchoolcodeID,
                            StartSchoolyearValue = schoolyear.Value
                        };
            return query.SingleOrDefault();
        }
    }
}