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

namespace EMIS.DataLogic.ScoreManage
{
    public class ImportScoreDAL
    {
        public ImportScoreRepository ImportScoreRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public UserRepository UserRepository { 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 FinalExaminationRepository FinalExaminationRepository { get; set; }
        public ScoreRepository ScoreRepository { get; set; }

        /// <summary>
        /// 查询对应的导入成绩（平时成绩）信息View
        /// </summary>
        /// <param name="expImportScore"></param>
        /// <param name="expStudent"></param>
        /// <returns></returns>
        public IQueryable<ImportScoreView> GetImportScoreViewQueryable(Expression<Func<ER_ImportScore, bool>> expImportScore, 
                Expression<Func<CF_Student, bool>> expStudent)
        {
            var query = from sco in ImportScoreRepository.GetList(expImportScore)
                        join sy in SchoolyearRepository.Entities
                        on sco.SchoolyearID equals sy.SchoolyearID
                        join cou in CoursematerialRepository.Entities
                        on sco.CoursematerialID equals cou.CoursematerialID
                        join stu in StudentRepository.GetList(expStudent)
                        on sco.UserID equals stu.UserID
                        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 us in UserRepository.Entities
                        on stu.UserID equals us.UserID
                        join mus in UserRepository.Entities
                        on sco.ModifyUserID equals mus.UserID
                        into tempmus
                        from scomus in tempmus.DefaultIfEmpty()
                        select new ImportScoreView
                        {
                            ImportScoreID = sco.ImportScoreID,
                            SchoolyearID = sco.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            UserID = sco.UserID,
                            StudentNo = us.LoginID,
                            UserName = us.Name,
                            Sex = stu.SexID,
                            InSchoolStatusID = stu.InSchoolStatusID,
                            StudentStatusID = stu.StudentStatus,
                            ClassID = stu.ClassmajorID,
                            ClassNo = cl.No,
                            ClassName = cl.Name,
                            GrademajorID = cl.GrademajorID,
                            GrademajorCode = gr.Code,
                            GrademajorName = gr.Name,
                            FacultymajorID = gr.FacultymajorID,
                            FacultymajorNo = fa.Code,
                            FacultymajorName = fa.Name,
                            CollegeID = fa.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            GradeID = gr.GradeID,
                            StandardID = fa.StandardID,
                            EducationID = fa.EducationID,
                            LearningformID = fa.LearningformID,
                            LearnSystem = fa.LearnSystem,
                            CoursematerialID = sco.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            Score = sco.Score,
                            Remark = sco.Remark,
                            CreateUserID = sco.CreateUserID,
                            CreateTime = sco.CreateTime,
                            ModifyUserID = sco.ModifyUserID,
                            ModifyUserNo = scomus.LoginID,
                            ModifyUserName = scomus.Name,
                            ModifyTime = sco.ModifyTime
                        };

            return query;
        }

        /// <summary>
        /// 查询对应的导入成绩（平时成绩）信息View（根据期末设定ID）
        /// </summary>
        /// <param name="finalExaminationID"></param>
        /// <returns></returns>
        public IQueryable<ImportScoreView> GetImportScoreViewQueryableByFinalExaminationID(Guid finalExaminationID)
        {
            var scoreQueryable = this.GetImportScoreViewQueryable(x => true, x => true);
            var sql = (from exam in FinalExaminationRepository.GetList(x => x.FinalExaminationID == finalExaminationID)
                       from student in exam.ER_FinalExaminationStudent
                       join score in scoreQueryable on new { exam.SchoolyearID, exam.CoursematerialID, student.UserID }
                           equals new { score.SchoolyearID, score.CoursematerialID, score.UserID }
                       select score);

            return sql;
        }
    }
}