JW/EMiS.DataLogic/ScoreManage/ScoreParameterCollegeSettingDAL.cs

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 ScoreParameterCollegeSettingDAL
    {
        public ScoreParameterCollegeSettingRepository ScoreParameterCollegeSettingRepository { get; set; }
        public ScoreParameterCollegeSettingDetailRepository ScoreParameterCollegeSettingDetailRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }

        public IQueryable<ScoreParameterCollegeSettingView> GetScoreParameterCollegeSettingViewQueryable(Expression<Func<ER_ScoreParameterCollegeSetting, bool>> exp)
        {
            var sql = (from setting in ScoreParameterCollegeSettingRepository.GetList(exp)
                       join college in CollegeRepository.Entities on setting.CollegeID equals college.CollegeID into dcollege 
                       from college in dcollege.DefaultIfEmpty() 
                       select new ScoreParameterCollegeSettingView 
                       { 
                           ScoreParameterCollegeSettingID = setting.ScoreParameterCollegeSettingID,
                           CollegeID = setting.CollegeID,
                           CollegeNo = college.No,
                           CollegeName = college.Name,
                           Years = setting.Years,
                           ScoreTypeID = setting.ScoreTypeID
                       });

            return sql;
        }

        public IQueryable<ScoreParameterSettingView> GetScoreParameterSettingViewQueryable(Expression<Func<ER_ScoreParameterCollegeSettingDetail, bool>> exp)
        {
            var sql = (from detail in ScoreParameterCollegeSettingDetailRepository.GetList(exp)
                       where detail.ExamsCategoryID == null && detail.CourseTypeID == null
                       select new ScoreParameterSettingView
                       {
                           ExamsTypeID = null,
                           ExamsTypeName = null,
                           CourseTypeID = null,
                           CourseTypeName = null,
                           ScoreFormulaID = detail.ScoreFormulaID,
                           IsTotalFormula = detail.IsTotalFormula ?? true,
                           CreditFormulaID = detail.CreditFormulaID,
                           IsCreditFormula = detail.IsCreditFormula ?? true,
                           GradePointFormulaID = detail.GradePointFormulaID,
                           IsGradePointFormula = detail.IsGradePointFormula ?? true,
                           OrderNo = (detail.ExamsCategoryID ?? 0) * (detail.CourseTypeID ?? 0)
                       }).Concat(
                       from category in DictionaryItemRepository.GetList(x => x.DictionaryCode == DictionaryItem.CF_ExamsCategory.ToString())
                       join detail in ScoreParameterCollegeSettingDetailRepository.GetList(exp)
                        on new { ExamsCategoryID = category.Value, CourseTypeID = (int?)null }
                        equals new { detail.ExamsCategoryID, detail.CourseTypeID } into ddetail
                       from detail in ddetail.DefaultIfEmpty()
                       select new ScoreParameterSettingView
                       {
                           ExamsTypeID = category.Value,
                           ExamsTypeName = category.Name,
                           CourseTypeID = null,
                           CourseTypeName = null,
                           ScoreFormulaID = detail.ScoreFormulaID,
                           IsTotalFormula = detail.IsTotalFormula ?? true,
                           CreditFormulaID = detail.CreditFormulaID,
                           IsCreditFormula = detail.IsCreditFormula ?? true,
                           GradePointFormulaID = detail.GradePointFormulaID,
                           IsGradePointFormula = detail.IsGradePointFormula ?? true,
                           OrderNo = (detail.ExamsCategoryID ?? 0) * (detail.CourseTypeID ?? 0)
                       }
                       ).Concat(
                       from courseType in DictionaryItemRepository.GetList(x => x.DictionaryCode == DictionaryItem.CF_CourseType.ToString())
                       join detail in ScoreParameterCollegeSettingDetailRepository.GetList(exp)
                        on new { ExamsCategoryID = (int?)null, CourseTypeID = courseType.Value }
                        equals new { detail.ExamsCategoryID, detail.CourseTypeID } into ddetail
                       from detail in ddetail.DefaultIfEmpty()
                       select new ScoreParameterSettingView
                       {
                           ExamsTypeID = null,
                           ExamsTypeName = null,
                           CourseTypeID = courseType.Value,
                           CourseTypeName = courseType.Name,
                           ScoreFormulaID = detail.ScoreFormulaID,
                           IsTotalFormula = detail.IsTotalFormula ?? true,
                           CreditFormulaID = detail.CreditFormulaID,
                           IsCreditFormula = detail.IsCreditFormula ?? true,
                           GradePointFormulaID = detail.GradePointFormulaID,
                           IsGradePointFormula = detail.IsGradePointFormula ?? true,
                           OrderNo = (detail.ExamsCategoryID ?? 0) * (detail.CourseTypeID ?? 0)
                       }
                       ).Concat(
                       from category in DictionaryItemRepository.GetList(x => x.DictionaryCode == DictionaryItem.CF_ExamsCategory.ToString())
                       join courseType in DictionaryItemRepository.GetList(x => x.DictionaryCode == DictionaryItem.CF_CourseType.ToString()) 
                        on true equals true 
                       join detail in ScoreParameterCollegeSettingDetailRepository.GetList(exp)
                        on new { ExamsCategoryID = category.Value, CourseTypeID = courseType.Value }
                        equals new { detail.ExamsCategoryID, detail.CourseTypeID } into ddetail 
                       from detail in ddetail.DefaultIfEmpty()
                       select new ScoreParameterSettingView
                       {
                           ExamsTypeID = category.Value,
                           ExamsTypeName = category.Name,
                           CourseTypeID = courseType.Value,
                           CourseTypeName = courseType.Name,
                           ScoreFormulaID = detail.ScoreFormulaID,
                           IsTotalFormula = detail.IsTotalFormula ?? true,
                           CreditFormulaID = detail.CreditFormulaID,
                           IsCreditFormula = detail.IsCreditFormula ?? true,
                           GradePointFormulaID = detail.GradePointFormulaID,
                           IsGradePointFormula = detail.IsGradePointFormula ?? true,
                           OrderNo = (detail.ExamsCategoryID ?? 0) * (detail.CourseTypeID ?? 0)
                       });

            return sql;
        }
    }
}