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;

namespace EMIS.DataLogic.ScoreManage
{
    public class ResitDAL
    {
        public ResitRepository ResitRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public ExaminationScoreRepository ExaminationScoreRepository { get; set; }
        public ExaminationBatchProjectRepository ExaminationBatchProjectRepository { get; set; }
        public ExaminationBatchRepository ExaminationBatchRepository { get; set; }
        public ExaminationTypeRepository ExaminationTypeRepository { get; set; }
        public ExaminationProjectRepository ExaminationProjectRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }

        public IQueryable<ResitView> GetResitViewQueryable(Expression<Func<EX_ExaminationBatch, bool>> batchExp,
            Expression<Func<EX_ExaminationProject, bool>> projectExp, Expression<Func<ER_ExaminationScore, bool>> scoreExp,
            Expression<Func<CF_Facultymajor, bool>> facutyExp, Expression<Func<CF_Grademajor, bool>> gradeExp,
            Expression<Func<CF_Student, bool>> studentExp)
        {
            var sql = (from resit in ResitRepository.Entities 
                       from score in ExaminationScoreRepository.Entities.Where(scoreExp).Where(x => x.ExaminationScoreID == resit.ExaminationScoreID) 
                       from batchProject in ExaminationBatchProjectRepository.Entities.Where(x => x.ExaminationBatchProjectID == score.ExaminationBatchProjectID) 
                       from batch in ExaminationBatchRepository.Entities.Where(batchExp).Where(x => x.ExaminationBatchID == batchProject.ExaminationBatchID)
                       from schoolyear in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == batch.SchoolyearID) 
                       from project in ExaminationProjectRepository.Entities.Where(projectExp).Where(x => x.ExaminationProjectID == batchProject.ExaminationProjectID)
                       from type in ExaminationTypeRepository.Entities.Where(x => x.ExaminationTypeID == project.ExaminationTypeID) 
                       from student in StudentRepository.Entities.Where(studentExp).Where(x => x.UserID == score.UserID) 
                       from user in UserRepository.Entities.Where(x => x.UserID == student.UserID) 
                       from classmajor in ClassmajorRepository.Entities.Where(x => x.ClassmajorID == student.ClassmajorID) 
                       from grade in GrademajorRepository.Entities.Where(gradeExp).Where(x => x.GrademajorID == classmajor.GrademajorID) 
                       from faculty in FacultymajorRepository.Entities.Where(facutyExp).Where(x => x.FacultymajorID == grade.FacultymajorID) 
                       from college in CollegeRepository.Entities.Where(x => x.CollegeID == faculty.CollegeID) 
                       select new ResitView
                       {
                           ResitID = resit.ResitID,
                           ExaminationScoreID = resit.ExaminationScoreID,
                           SchoolyearID = schoolyear.SchoolyearID,
                           SchoolyearCode = schoolyear.Code,
                           ExaminationBatchID = batch.ExaminationBatchID,
                           ExaminationBatchName = batch.Name,
                           ExaminationTypeID = type.ExaminationTypeID,
                           ExaminationTypeName = type.Name,
                           ExaminationProjectID = project.ExaminationProjectID,
                           ExaminationProjectName = project.Name,
                           ExaminationSubjectID = score.ExaminationSubjectID,
                           CollegeID = college.CollegeID,
                           CollegeName = college.Name,
                           Year = grade.SchoolyearID,
                           StandardID = faculty.StandardID,
                           ClassmajorID = classmajor.ClassmajorID,
                           ClassmajorName = classmajor.Name,
                           UserID = user.UserID,
                           UserName = user.Name,
                           CertificatesType = student.CertificatesType,
                           IDNumber = student.IDNumber,
                           Score = score.Score,
                           Expire = score.Expire,
                           SchoolAreaID = grade.SchoolAreaID,
                       });

            return sql;
        }
    }
}