JW/EMiS.DataLogic/DQPSystem/SOCDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.ViewModel.DQPSystem;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.EducationManagement;
using EMIS.ViewModel;
using EMIS.ViewModel.EducationManage;
using EMIS.ViewModel.Students;
using Bowin.Common.Linq;
using EMIS.DataLogic.UniversityManage.AdministrativeOrgan;
using EMIS.ViewModel.UniversityManage.SpecialtyClassManage;
using EMIS.ViewModel.UniversityManage.AdministrativeOrgan;

namespace EMIS.DataLogic.DQPSystem
{
    public partial class SOCDAL
    {
        public SOCRepository SOCRepository { get; set; }
        public SOCDetailRepository SOCDetailRepository { get; set; }
        public SOCDetailAttachmentRepository SOCDetailAttachmentRepository { get; set; }
        public SOCTemplateRepository SOCTemplateRepository { get; set; }
        public SchoolyearRepository schoolyearRepository { get; set; }
        public CoursematerialRepository coursematerialRepository { get; set; }
        public EducationMissionClassRepository educationMissionClassRepository { get; set; }
        public EducationMissionRepository educationMissionRepository { get; set; }
        public ClassmajorRepository classmajorRepository { get; set; }
        public GrademajorRepository grademajorRepository { get; set; }
        public StudentRepository studentRepository { get; set; }
        public EducationSchedulingClassRepository educationSchedulingClassRepository { get; set; }
        public CollegeDAL CollegeDAL { get; set; }
        public CollegeRepository collegeRepository { get; set; }

        public IQueryable<SOCView> GetSOCViewQueryable(Expression<Func<DQP_SOC, bool>> exp, Guid? userID)
        {
            var query = from soc in SOCRepository.GetList(exp)
                        join year in schoolyearRepository.Entities
                        on soc.SchoolyearID equals year.SchoolyearID
                        into sch
                        from gsch in sch.DefaultIfEmpty()
                        join cou in coursematerialRepository.Entities
                        on soc.CoursematerialID equals cou.CoursematerialID
                        into cmr
                        from gcmr in cmr.DefaultIfEmpty()
                        join edu in educationMissionRepository.Entities
                        on soc.EducationMissionID equals edu.EducationMissionID
                        into gedu
                        from edu in gedu.DefaultIfEmpty()
                        select new SOCView
                        {
                            SOCID = soc.SOCID,
                            EducationMissionID = soc.EducationMissionID,
                            EducationMissionName = edu.ClassName,
                            SchoolyearID = soc.SchoolyearID,
                            SchoolyearCode = gsch.Code,
                            CoursematerialID = gcmr.CoursematerialID,
                            CourseCode = gcmr.CourseCode,
                            CourseName = gcmr.CourseName,
                            StudentCount = soc.CF_Student.Count,
                            //soc.DQP_SOCStaff.FirstOrDefault(x => x.UserID == userID).CF_Student.Count,
                            HandleModeID = soc.HandleModeID,
                            DepartmentID = soc.DepartmentID,
                            Credit = soc.Credit,
                            OptionalCourseTypeID = soc.OptionalCourseTypeID,
                        };
            if (userID != null)
            {
                query = from soc in SOCRepository.GetList(exp).Where(x => x.DQP_SOCStaff.Any(y => y.UserID == userID))
                            join year in schoolyearRepository.Entities
                            on soc.SchoolyearID equals year.SchoolyearID
                            into sch
                            from gsch in sch.DefaultIfEmpty()
                            join cou in coursematerialRepository.Entities
                            on soc.CoursematerialID equals cou.CoursematerialID
                            into cmr
                            from gcmr in cmr.DefaultIfEmpty()
                            join edu in educationMissionRepository.Entities
                            on soc.EducationMissionID equals edu.EducationMissionID
                            into gedu
                            from edu in gedu.DefaultIfEmpty()
                            select new SOCView
                            {
                                SOCID = soc.SOCID,
                                EducationMissionID = soc.EducationMissionID,
                                EducationMissionName = edu.ClassName,
                                SchoolyearID = soc.SchoolyearID,
                                SchoolyearCode = gsch.Code,
                                CoursematerialID = gcmr.CoursematerialID,
                                CourseCode = gcmr.CourseCode,
                                CourseName = gcmr.CourseName,
                                StudentCount = soc.CF_Student.Count,
                                //soc.DQP_SOCStaff.FirstOrDefault(x => x.UserID == userID).CF_Student.Count,
                                HandleModeID = soc.HandleModeID,
                                DepartmentID = soc.DepartmentID,
                                Credit = soc.Credit,
                                OptionalCourseTypeID = soc.OptionalCourseTypeID,
                            };
            }
            return query;
        }

        public IQueryable<SOCDetailView> GetSOCDetailViewQueryable(Expression<Func<DQP_SOCDetail, bool>> exp)
        {
            var query = from detail in SOCDetailRepository.GetList(exp)
                        join edu in educationMissionRepository.Entities
                        on detail.DQP_SOC.EducationMissionID equals edu.EducationMissionID
                        into dedu
                        from edu in dedu.DefaultIfEmpty()
                        select new SOCDetailView
                        {
                            SOCDetailID = detail.SOCDetailID,
                            EducationMissionID = detail.DQP_SOC.EducationMissionID,
                            EducationMissionName = edu.ClassName,
                            SOCID = detail.SOCID,
                            SchoolyearID = detail.DQP_SOC.SchoolyearID,
                            SchoolyearCode = detail.DQP_SOC.CF_Schoolyear.Code,
                            CoursematerialID = detail.DQP_SOC.CoursematerialID,
                            CourseCode = detail.DQP_SOC.EM_Coursematerial.CourseCode,
                            CourseName = detail.DQP_SOC.EM_Coursematerial.CourseName,
                            Name = detail.Name,
                            Weight = detail.Weight,
                            Credit = detail.Credit,
                            Description = detail.Description,
                            IsGroup = detail.IsGroup,
                            IsGroupin = detail.IsGroup.Value,
                            StartTime = detail.StartTime,
                            EndTime = detail.EndTime,
                            Attachment = detail.DQP_SOCDetailAttachment,
                            GroupCount = detail.DQP_SOCDetailGroup.Count(),
                            StudentCount = detail.DQP_SOC.CF_Student.Count,
                        };
            return query;
        }

        /// <summary>
        /// 生成当前学年学期，登录人是主讲的
        /// </summary>
        /// <param name="schoolyearID"></param>
        /// <param name="userID"></param>
        /// <returns></returns>
        public IQueryable<SOCGenerateView> GetSOCGenerateViewQueryble(Guid? schoolyearID, Guid? userID)
        {
            var query = from emr in educationMissionRepository.GetList(x => x.SchoolyearID == schoolyearID)
                        join emcr in educationMissionClassRepository.GetList(x => x.EM_MissionClassTeacher.Any(y => y.UserID == userID && y.TeachType == (int)EM_TeachingMethod.Lecturer) && x.RecordStatus != (int)EM_EducationMissionClassStatus.NotSubmitted)
                        on emr.EducationMissionID equals emcr.EducationMissionID
                        from cla in emcr.CF_Classmajor
                        join gra in grademajorRepository.Entities
                        on cla.GrademajorID equals gra.GrademajorID
                        into ggra
                        from gra in ggra.DefaultIfEmpty()
                        join sch in schoolyearRepository.Entities
                        on emr.SchoolyearID equals sch.SchoolyearID
                        into gsch
                        from sch in gsch.DefaultIfEmpty()
                        group emr
                        by new
                        {
                            emr.EducationMissionID,
                            emcr.HandleModeID,
                            emcr.OptionalCourseTypeID,
                            emcr.CoursematerialID,
                            emr.DepartmentID,
                            emr.SchoolyearID,
                            sch.Value,
                            yearID = gra.GradeID,
                            gra.SemesterID,
                            emr.EM_EducationMissionClass.FirstOrDefault().EM_EducationMissionClassTeachingSetting.Credit,
                        }
                            into gemr
                        //join emc in educationMissionClassRepository.GetList(x => x.RecordStatus != (int)EM_EducationMissionClassStatus.NotSubmitted)
                        //on gemr.Key.EducationMissionID equals emc.EducationMissionID
                        //join sche in educationSchedulingClassRepository.Entities
                        //on emc.EducationMissionClassID equals sche.EducationMissionClassID
                            select new SOCGenerateView
                            {
                                EducationMissionID = gemr.Key.EducationMissionID,
                                HandleModeID = gemr.Key.HandleModeID,
                                OptionalCourseTypeID = gemr.Key.OptionalCourseTypeID,
                                SchoolyearNumID = (gemr.Key.Value - ((gemr.Key.yearID * 2 - 1) + gemr.Key.SemesterID - 1))/2 + 1,
                                SchoolcodeID = gemr.Key.SemesterID,
                                SOCView = new SOCView
                                {
                                    SchoolyearID = gemr.Key.SchoolyearID,
                                    DepartmentID = gemr.Key.DepartmentID,
                                    CoursematerialID = gemr.Key.CoursematerialID,
                                    Credit = gemr.Key.Credit,
                                },

                            };
            //var query = from emr in educationMissionRepository.GetList(x => x.SchoolyearID == schoolyearID)
            //            join emcr in educationMissionClassRepository.GetList(x => x.EM_MissionClassTeacher.Any(y => y.UserID == userID) && x.RecordStatus != (int)EM_EducationMissionClassStatus.NotSubmitted)
            //            on emr.EducationMissionID equals emcr.EducationMissionID
            //            from cla in emcr.CF_Classmajor
            //            join gra in grademajorRepository.Entities
            //            on cla.GrademajorID equals gra.GrademajorID
            //            join sch in schoolyearRepository.Entities
            //            on emr.SchoolyearID equals sch.SchoolyearID
            //            //group emr
            //            //by new { emr.EducationMissionID, emcr.HandleModeID, emcr.OptionalCourseTypeID, gra.FacultymajorID, emcr.CoursematerialID, 
            //            //    emr.DepartmentID, emr.SchoolyearID, sch.Value, yearID = gra.SchoolyearID, gra.SchoolcodeID  }
            //            //    into gemr
            //                select new {
            //                    emr.EducationMissionID,
            //                    emcr.CoursematerialID,
            //                    emr.DepartmentID,
            //                    gra.FacultymajorID,
            //                    emcr.HandleModeID,
            //                    emcr.OptionalCourseTypeID,
            //                    gra.SchoolcodeID,
            //                    SchoolyearNumID = (sch.Value - ((gra.SchoolyearID * 2 - 1) + gra.SchoolcodeID - 1)) / 2 + 1,
            //                    emr.SchoolyearID,
            //                }into ggemr
            //                join soctem in SOCTemplateRepository.GetList(x => x.RecordStatus == (int)SYS_STATUS.USABLE)
            //                on new { ggemr.CoursematerialID, ggemr.DepartmentID }
            //                //, ggemr.SchoolcodeID, ggemr.SchoolyearNumID, ggemr.FacultymajorID, ggemr.HandleModeID
            //                equals new { soctem.CoursematerialID, soctem.DepartmentID }
            //                //, soctem.SchoolcodeID, soctem.SchoolyearNumID, soctem.FacultymajorID, soctem.HandleModeID
            //                where (ggemr.SchoolcodeID == soctem.SchoolcodeID || soctem.SchoolcodeID == null) 
            //                && (ggemr.SchoolyearNumID == soctem.SchoolyearNumID || soctem.SchoolyearNumID == null)
            //                && (ggemr.FacultymajorID == soctem.FacultymajorID || soctem.FacultymajorID == null)
            //                && (ggemr.HandleModeID == soctem.HandleModeID || soctem.HandleModeID == null)
            //                group ggemr 
            //                by new {ggemr.SchoolyearID, soctem.SOCTemplateID }
            //                into gsoc
            //                join soc in SOCTemplateRepository.GetList(x => x.RecordStatus == (int)SYS_STATUS.USABLE)
            //                on gsoc.Key.SOCTemplateID equals soc.SOCTemplateID
            //                //join edumr in educationMissionRepository.Entities
            //                //on ggemr.EducationMissionID equals edumr.EducationMissionID
            //                //join edumcr in educationMissionClassRepository.Entities
            //                //on edumr.EducationMissionID equals edumcr.EducationMissionID
            //                //join sche in educationSchedulingClassRepository.Entities
            //                //on edumcr.EducationMissionClassID equals sche.EducationMissionClassID
            //                select new SOCGenerateView
            //                {

            //                    HandleModeID = soc.HandleModeID,
            //                    OptionalCourseTypeID = soc.OptionalCourseTypeID,
            //                    FacultymajorID = soc.FacultymajorID,
            //                    SOCTemplateID = soc.SOCTemplateID,
            //                    SchoolyearNumID = soc.SchoolyearNumID,
            //                    SchoolcodeID = soc.SchoolcodeID,
            //                    SOCView = new SOCView
            //                    {
            //                        SchoolyearID = gsoc.Key.SchoolyearID,
            //                        DepartmentID = soc.DepartmentID,
            //                        CoursematerialID = soc.CoursematerialID,
            //                        Credit = soc.Credit
            //                    },
            //                    //SOCDetailViewList = soctem.DQP_SOCTemplateDetail.Select(x => new SOCDetailView
            //                    //{
            //                    //    Name = x.Name,
            //                    //    Weight = x.Weight,
            //                    //    Credit = x.Credit,
            //                    //    Description = x.Description,
            //                    //    IsGroup = x.IsGroup,
            //                    //    AttachmentList = x.DQP_SOCTemplateDetailAttachment.Select(y => new SOCDetailAttachmentView
            //                    //    {
            //                    //        Name = y.Name,
            //                    //        Url = y.Url,
            //                    //    })
            //                    //}),
            //                    //MissionClassTeacherViewList = edumcr.EM_MissionClassTeacher.Select(x => new MissionClassTeacherView
            //                    //{
            //                    //    UserID = x.UserID,
            //                    //    TeachingMethod = x.TeachType,
            //                    //}),
            //                    //StudentList = sche.CF_Student,
            //                    //ClassmajorList = edumcr.CF_Classmajor,
                                
            //                };
            return query;
        }

        public IQueryable<SOCGenerateView> GetEducationMissionMassageByID(Expression<Func<EM_EducationMission, bool>> exp)
        {
            var query = from em in educationMissionRepository.GetList(exp)
                        join emc in educationMissionClassRepository.GetList(x => x.RecordStatus != (int)EM_EducationMissionClassStatus.NotSubmitted)
                        on em.EducationMissionID equals emc.EducationMissionID
                        join sche in educationSchedulingClassRepository.Entities
                        on emc.EducationMissionClassID equals sche.EducationMissionClassID
                        select new SOCGenerateView
                        {
                            HandleModeID = emc.HandleModeID,
                            OptionalCourseTypeID = emc.OptionalCourseTypeID,
                            EducationMissionID = emc.EducationMissionID,
                            SOCView = new SOCView
                            {
                                SchoolyearID = em.SchoolyearID,
                                DepartmentID = em.DepartmentID,
                                CoursematerialID = emc.CoursematerialID,
                            },
                            MissionClassTeacherViewList = emc.EM_MissionClassTeacher.Where(x => x.TeachType == (int)EM_TeachingMethod.Lecturer).Select(x => new MissionClassTeacherView
                            {
                                UserID = x.UserID,
                                TeachingMethod = x.TeachType,
                            }),
                            ClassmajorList = emc.CF_Classmajor,
                            StudentList = sche.CF_Student,

                        };
            return query;
        }

        public List<CF_Student> GetStudentByTeacher(Expression<Func<EM_EducationMission, bool>> exp, Guid? userID, Guid? coursematerialID)
        {
            var query = from em in educationMissionRepository.GetList(exp)
                        join emc in educationMissionClassRepository.GetList(x => x.EM_MissionClassTeacher.Any(y => y.UserID == userID) 
                            && x.RecordStatus != (int)EM_EducationMissionClassStatus.NotSubmitted && x.CoursematerialID == coursematerialID)
                        on em.EducationMissionID equals emc.EducationMissionID
                        join sche in educationSchedulingClassRepository.Entities
                        on emc.EducationMissionClassID equals sche.EducationMissionClassID
                        select new SOCGenerateView
                        {
                            StudentList = sche.CF_Student,
                        };
            return query.SelectMany(x => x.StudentList).ToList();
        }

        public IQueryable<SOCGenerateView> GetSOCTemplateForSOCGenerate(Expression<Func<DQP_SOCTemplate, bool>> exp)
        {
            var query = from soc in SOCTemplateRepository.GetList(exp)
                        select new SOCGenerateView
                        {
                            SOCTemplateID = soc.SOCTemplateID,
                            SOCDetailViewList = soc.DQP_SOCTemplateDetail.Select(x => new SOCDetailView
                            {
                                Name = x.Name,
                                Weight = x.Weight,
                                Credit = x.Credit,
                                Description = x.Description,
                                IsGroup = x.IsGroup,
                                AttachmentList = x.DQP_SOCTemplateDetailAttachment.Select(y => new SOCDetailAttachmentView
                                {
                                    Name = y.Name,
                                    Url = y.Url,
                                })
                            }),
                        };
            return query;
        }

        public IOrderedQueryable<FileUploadView> GetSOCDetailAttachmentView(Expression<Func<DQP_SOCDetail, bool>> exp)
        {
            var tableName = typeof(DQP_SOCDetailAttachment).Name;
            var sql = (from @group in SOCDetailRepository.GetList(exp)
                       from attachment in @group.DQP_SOCDetailAttachment
                       orderby attachment.CreateTime descending
                       select new FileUploadView
                       {
                           FileID = attachment.SOCDetailAttachmentID,
                           FileName = attachment.Name,
                           FileUrl = attachment.Url,
                           FormID = attachment.SOCDetailID,
                           TableName = tableName
                       });

            return (IOrderedQueryable<FileUploadView>)sql;
        }

        /// <summary>
        /// 获取学生信息
        /// </summary>
        /// <param name="finalExaminationID"></param>
        /// <returns></returns>
        public IQueryable<BaseStudentView> GetSOCStudentList(Guid? SOCID)
        {
            var curUser = EMIS.Utility.FormValidate.CustomPrincipal.Current;
            var query = from a in SOCRepository.GetList(x => x.SOCID == SOCID)
                        from c in a.DQP_SOCStaff.FirstOrDefault(x => x.UserID == curUser.UserID).CF_Student
                        select new BaseStudentView
                        {
                            UserID = c.UserID,
                            LoginID = c.Sys_User.LoginID,
                            UserName = c.Sys_User.Name,
                            SexID = c.SexID,
                            IDNumber = c.IDNumber,
                            InSchoolStatusID = c.InSchoolStatusID,
                            StudentStatus = c.StudentStatus,
                            ClassmajorCode = c.CF_Classmajor.No,
                            ClassmajorName = c.CF_Classmajor.Name,
                        };
            return query;
        }

        public IQueryable<CollegeView> GetCollegeViewList(Expression<Func<DQP_SOC, bool>> exp, Expression<Func<CF_Staff, bool>> staffExp)
        {
            var query = from soc in SOCRepository.Entities.Where(exp)
                        from teacher in soc.DQP_SOCStaff
                        from staff in StaffRepository.Entities.Where(staffExp).Where(x => x.UserID == teacher.UserID)
                        from em in educationMissionRepository.Entities.Where(x => x.EducationMissionID == soc.EducationMissionID)
                        from col in collegeRepository.Entities.Where(x => x.CollegeID == em.CollegeID)
                        select new CollegeView
                        {
                            CollegeID = col.CollegeID,
                            No = col.No,
                            Name = col.Name,
                            SimpleName = col.SimpleName,
                            EnglishName = col.EnglishName,
                            CampusID = col.CampusID,
                            Remark = col.Remark,
                            RecordStatus = col.RecordStatus,
                            CreateUserID = col.CreateUserID,
                            CreateTime = col.CreateTime,
                            ModifyUserID = col.ModifyUserID,
                            ModifyTime = col.ModifyTime
                        };
            return query.Distinct();
        }

        public IQueryable<ClassmajorView> GetClassmajorViewList(Expression<Func<DQP_SOC, bool>> exp, Expression<Func<CF_Staff, bool>> staffExp, Expression<Func<CF_Classmajor, bool>> classExp)
        {
            var query = from soc in SOCRepository.Entities.Where(exp)
                        from teacher in soc.DQP_SOCStaff
                        from staff in StaffRepository.Entities.Where(staffExp).Where(x => x.UserID == teacher.UserID)
                        from em in educationMissionRepository.Entities.Where(x => x.EducationMissionID == soc.EducationMissionID)
                        from emc in educationMissionClassRepository.Entities.Where(x => x.EducationMissionID == em.EducationMissionID)
                        from cl in classmajorRepository.Entities.Where(classExp).Where(x => x.ClassmajorID == emc.MainScheduleClassID)
                        select new ClassmajorView
                        {
                            ClassmajorID = cl.ClassmajorID,
                            No = cl.No,
                            Name = cl.Name,
                            Abbreviation = cl.Abbreviation,
                            EnglishName = cl.EnglishName,
                            ClassNum = cl.ClassNum,
                            UserID = cl.UserID,
                            Fixedclassroom = cl.Fixedclassroom,
                            Remark = cl.Remark,
                            RecordStatus = cl.RecordStatus,
                            CreateUserID = cl.CreateUserID,
                            CreateTime = cl.CreateTime,
                            ModifyUserID = cl.ModifyUserID,
                            ModifyTime = cl.ModifyTime
                        };
            return query.Distinct();
        }
    }
}