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

namespace EMIS.DataLogic.ScoreManage
{
    public class ScoreConvertByApplyDAL
    {
        public ScoreConvertByApplyRepository scoreConvertByApplyRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public GradePointFormulaRepository gradePointFormulaRepository { get; set; }
        public ScoreParameterSettingRepository scoreParameterSettingRepository { get; set; }

        public IQueryable<ScoreConvertByApplyView> GetScoreConvertByApplyViewQueryable(Expression<Func<ER_ScoreConvertByApply, bool>> exp,
            Expression<Func<CF_Facultymajor, bool>> facultyExp,
            Expression<Func<CF_Grademajor, bool>> gradeExp,
            Expression<Func<CF_Classmajor, bool>> classExp)
        {
            var query = from scbyapp in scoreConvertByApplyRepository.GetList(exp)
                        join user in UserRepository.Entities on scbyapp.UserID equals user.UserID
                        join stu in StudentRepository.Entities on user.UserID equals stu.UserID
                        join cla in ClassmajorRepository.GetList(classExp) on stu.ClassmajorID equals cla.ClassmajorID
                        join gra in GrademajorRepository.GetList(gradeExp) on cla.GrademajorID equals gra.GrademajorID
                        join fac in FacultymajorRepository.GetList(facultyExp) on gra.FacultymajorID equals fac.FacultymajorID
                        join col in CollegeRepository.Entities on fac.CollegeID equals col.CollegeID
                        join sch in SchoolyearRepository.Entities on scbyapp.SchoolyearID equals sch.SchoolyearID
                        join cou in CoursematerialRepository.Entities on scbyapp.CoursematerialID equals cou.CoursematerialID
                        select new ScoreConvertByApplyView
                        {
                            ScoreConvertByApplyID = scbyapp.ScoreConvertByApplyID,
                            SchoolyearID = scbyapp.SchoolyearID,
                            SchoolyearCode = sch.Code,
                            CollegeID = col.CollegeID,
                            CollegeNo = col.No,
                            CollegeName = col.Name,
                            StandardID = fac.StandardID,
                            ClassmajorID = cla.ClassmajorID,
                            ClassmajorNo = cla.No,
                            ClassmajorName = cla.Name,
                            UserID = scbyapp.UserID,
                            LoginID = user.LoginID,
                            Name = user.Name,
                            CoursematerialID = cou.CoursematerialID,
                            CourseCode = cou.CourseCode,
                            CourseName = cou.CourseName,
                            TotalScore = scbyapp.TotalScore,
                            RecordStatus = scbyapp.RecordStatus,
                            Reason = scbyapp.Reason,
                            AttachmentList = scbyapp.ER_ScoreConvertByApplyAttachment,
                        };
            return query;
        }

        public IOrderedQueryable<FileUploadView> GetScoreConvertByApplyAttachmentView(Expression<Func<ER_ScoreConvertByApply, bool>> exp)
        {
            var tableName = typeof(ER_ScoreConvertByApplyAttachment).Name;
            var sql = (from @group in scoreConvertByApplyRepository.GetList(exp)
                       from attachment in @group.ER_ScoreConvertByApplyAttachment
                       orderby attachment.CreateTime descending
                       select new FileUploadView
                       {
                           FileID = attachment.ScoreConvertByApplyAttachmentID,
                           FileName = attachment.Name,
                           FileUrl = attachment.Url,
                           FormID = attachment.ScoreConvertByApplyID,
                           TableName = tableName
                       });

            return (IOrderedQueryable<FileUploadView>)sql;
        }


        public IQueryable<GradePointFormulaView> GetDefaultGradePointFormula() 
        {
            var query = from par in scoreParameterSettingRepository.Entities.Where(x => x.ExamsTypeID == (int)SYS_ExamsType.CommonSettings)
                        join gra in gradePointFormulaRepository.Entities
                        on par.GradePointFormulaID equals gra.GradePointFormulaID
                        select new GradePointFormulaView
                        {
                            GradePointFormulaID = gra.GradePointFormulaID,
                            GradePointlimit = gra.GradePointlimit,
                            GradePointFloor = gra.GradePointFloor,
                            Name = gra.Name
                        };
            return query;
        }
    }
}