using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using EMIS.Entities;
using EMIS.ViewModel.ScoreManage.LevelScoreManage;
using EMIS.DataLogic.Repositories;

namespace EMIS.DataLogic.ScoreManage.LevelScoreManage
{
    public class LevelScoreDAL
    {
        public LevelScoreRepository LevelScoreRepository { get; set; }
        public ExaminationSubjectRepository ExaminationSubjectRepository { get; set; }
        public LevelSettingRepository LevelSettingRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public StudentRepository StudentRepository { 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 UserRepository UserRepository { get; set; }

        /// <summary>
        /// 查询对应的等级成绩信息LevelScoreView
        /// </summary>
        /// <param name="expLevelScore"></param>
        /// <returns></returns>
        public IQueryable<LevelScoreView> GetLevelScoreViewQueryable(Expression<Func<ER_LevelScore, bool>> expLevelScore)
        {
            var query = from lvsco in LevelScoreRepository.GetList(expLevelScore)
                        join stu in StudentRepository.Entities
                        on lvsco.UserID equals stu.UserID
                        join exsub in ExaminationSubjectRepository.Entities
                        on lvsco.ExaminationSubjectID equals exsub.ExaminationSubjectID
                        join sy in SchoolyearRepository.Entities
                        on lvsco.SchoolyearID equals sy.SchoolyearID
                        join lvst in LevelSettingRepository.Entities
                        on exsub.ExaminationSubjectID equals lvst.ExaminationSubjectID into templvst
                        from exsubst in templvst.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 us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join usc in UserRepository.Entities
                        on lvsco.CreateUserID equals usc.UserID into tempusc
                        from uscu in tempusc.DefaultIfEmpty()
                        join usm in UserRepository.Entities
                        on lvsco.ModifyUserID equals usm.UserID into tempusm
                        from usmu in tempusm.DefaultIfEmpty()
                        where exsubst.LevelSettingID == null || (exsubst.LevelSettingID != null 
                        && ((exsubst.MinScore < lvsco.TotalScore && exsubst.MinScoreOperator == "<" && lvsco.TotalScore < exsubst.MaxScore && exsubst.MaxScoreOperator == "<") 
                        || (exsubst.MinScore < lvsco.TotalScore && exsubst.MinScoreOperator == "<" && lvsco.TotalScore <= exsubst.MaxScore && exsubst.MaxScoreOperator == "<=") 
                        || (exsubst.MinScore <= lvsco.TotalScore && exsubst.MinScoreOperator == "<=" && lvsco.TotalScore < exsubst.MaxScore && exsubst.MaxScoreOperator == "<") 
                        || (exsubst.MinScore <= lvsco.TotalScore && exsubst.MinScoreOperator == "<=" && exsubst.MaxScoreOperator == "<=" && lvsco.TotalScore <= exsubst.MaxScore)))
                        select new LevelScoreView
                        {
                            LevelScoreID = lvsco.LevelScoreID,
                            UserID = lvsco.UserID,
                            StudentNo = us.LoginID,
                            Name = us.Name,
                            SexID = stu.SexID,
                            BirthDate = stu.BirthDate,
                            NationID = stu.NationID,
                            PoliticsID = stu.PoliticsID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatus = stu.StudentStatus,
                            GradeID = gr.GradeID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            ClassNum = cl.ClassNum,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorNo = cl.No,
                            ClassmajorName = cl.Name,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            CampusID = col.CampusID,
                            CampusCode = cam.No,
                            CampusName = cam.Name,
                            ExaminationSubjectID = lvsco.ExaminationSubjectID,
                            ExaminationSubjectName = exsub.Name,
                            SchoolyearID = lvsco.SchoolyearID,
                            SchoolyearValue = sy.Value,
                            SchoolyearCode = sy.Code,
                            ExaminationDate = lvsco.ExaminationDate,
                            ScoreNo = lvsco.ScoreNo,
                            TotalScore = lvsco.TotalScore,
                            LevelSettingID = exsubst.LevelSettingID,
                            LevelName = exsubst.LevelName,
                            Remark = lvsco.Remark,
                            ApprovalStatus = lvsco.ApprovalStatus,
                            RecordStatus = lvsco.RecordStatus,
                            CreateUserID = lvsco.CreateUserID,
                            CreateUserName = uscu.Name,
                            CreateTime = lvsco.CreateTime,
                            ModifyUserID = lvsco.ModifyUserID,
                            ModifyUserName = usmu.Name,
                            ModifyTime = lvsco.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的等级成绩信息LevelScoreView
        /// </summary>
        /// <param name="expLevelScore"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<LevelScoreView> GetLevelScoreViewQueryable(Expression<Func<ER_LevelScore, bool>> expLevelScore, Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from lvsco in LevelScoreRepository.GetList(expLevelScore)
                        join stu in StudentRepository.GetList(expStudent)
                        on lvsco.UserID equals stu.UserID
                        join exsub in ExaminationSubjectRepository.Entities
                        on lvsco.ExaminationSubjectID equals exsub.ExaminationSubjectID
                        join sy in SchoolyearRepository.Entities
                        on lvsco.SchoolyearID equals sy.SchoolyearID
                        join lvst in LevelSettingRepository.Entities
                        on lvsco.ExaminationSubjectID equals lvst.ExaminationSubjectID into templvst
                        from lvscost in templvst.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 us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join usc in UserRepository.Entities
                        on lvsco.CreateUserID equals usc.UserID into tempusc
                        from uscu in tempusc.DefaultIfEmpty()
                        join usm in UserRepository.Entities
                        on lvsco.ModifyUserID equals usm.UserID into tempusm
                        from usmu in tempusm.DefaultIfEmpty()
                        where lvscost.LevelSettingID == null || (lvscost.LevelSettingID != null
                        && ((lvscost.MinScore < lvsco.TotalScore && lvscost.MinScoreOperator == "<" && lvsco.TotalScore < lvscost.MaxScore && lvscost.MaxScoreOperator == "<")
                        || (lvscost.MinScore < lvsco.TotalScore && lvscost.MinScoreOperator == "<" && lvsco.TotalScore <= lvscost.MaxScore && lvscost.MaxScoreOperator == "<=")
                        || (lvscost.MinScore <= lvsco.TotalScore && lvscost.MinScoreOperator == "<=" && lvsco.TotalScore < lvscost.MaxScore && lvscost.MaxScoreOperator == "<")
                        || (lvscost.MinScore <= lvsco.TotalScore && lvscost.MinScoreOperator == "<=" && lvsco.TotalScore <= lvscost.MaxScore && lvscost.MaxScoreOperator == "<=")))
                        select new LevelScoreView
                        {
                            LevelScoreID = lvsco.LevelScoreID,
                            UserID = lvsco.UserID,
                            StudentNo = us.LoginID,
                            Name = us.Name,
                            SexID = stu.SexID,
                            BirthDate = stu.BirthDate,
                            NationID = stu.NationID,
                            PoliticsID = stu.PoliticsID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatus = stu.StudentStatus,
                            GradeID = gr.GradeID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            ClassNum = cl.ClassNum,
                            ClassmajorID = stu.ClassmajorID,
                            ClassmajorNo = cl.No,
                            ClassmajorName = cl.Name,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorCode = fa.Code,
                            FacultymajorName = fa.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            CampusID = col.CampusID,
                            CampusCode = cam.No,
                            CampusName = cam.Name,
                            ExaminationSubjectID = lvsco.ExaminationSubjectID,
                            ExaminationSubjectName = exsub.Name,
                            SchoolyearID = lvsco.SchoolyearID,
                            SchoolyearValue = sy.Value,
                            SchoolyearCode = sy.Code,
                            ExaminationDate = lvsco.ExaminationDate,
                            ScoreNo = lvsco.ScoreNo,
                            TotalScore = lvsco.TotalScore,
                            LevelSettingID = lvscost.LevelSettingID,
                            LevelName = lvscost.LevelName,
                            Remark = lvsco.Remark,
                            ApprovalStatus = lvsco.ApprovalStatus,
                            RecordStatus = lvsco.RecordStatus,
                            CreateUserID = lvsco.CreateUserID,
                            CreateUserName = uscu.Name,
                            CreateTime = lvsco.CreateTime,
                            ModifyUserID = lvsco.ModifyUserID,
                            ModifyUserName = usmu.Name,
                            ModifyTime = lvsco.ModifyTime
                        };

            return query;
        }

    }
}