JW/EMiS.DataLogic/Common/EvaluationManage/SatisfyRateDAL.cs

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

namespace EMIS.DataLogic.Common.EvaluationManage
{
    public class SatisfyRateDAL
    {
        public StaffRepository StaffRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public EvaluationEnterRepository EvaluationEnterRepository { get; set; }
        public EvaluationEnter_EM_EvaluationProjectRepository EvaluationEnter_EM_EvaluationProjectRepository { get; set; }
        public EvaluationProjectRepository EvaluationProjectRepository { get; set; }
        public EvaluationTargetRepository EvaluationTargetRepository { get; set; }
        public EvaluationTableRepository EvaluationTableRepository { get; set; }
        public EvaluationTypeRepository EvaluationTypeRepository { get; set; }
        public EvaluationNormRepository EvaluationNormRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public UserRepository UserRepository { get; set; }

        public IQueryable<SatisfyRateView> GetUserSatisfyRateView(Expression<Func<CF_Staff, bool>> exp, Expression<Func<EM_EvaluationType, bool>> typeExp)
        {
            var query = (from staff in StaffRepository.GetList(exp)
                         join college in CollegeRepository.Entities on staff.CollegeID equals college.CollegeID
                         join user in UserRepository.Entities on staff.UserID equals user.UserID
                         from missionClassTeacher in staff.EM_MissionClassTeacher
                         join missionClass in EducationMissionClassRepository.Entities on missionClassTeacher.MissionClassID equals missionClass.EducationMissionClassID
                         join mission in EducationMissionRepository.Entities on missionClass.EducationMissionID equals mission.EducationMissionID
                         join score in EvaluationEnterRepository.Entities on missionClassTeacher.MissionClassID equals score.EducationMissionClassID
                         join scoreDetail in EvaluationEnter_EM_EvaluationProjectRepository.Entities on score.EvaluationEnterID equals scoreDetail.EvaluationEnterID 
                         join project in EvaluationProjectRepository.Entities on scoreDetail.ProjectID equals project.EvaluationProjectID
                         join target in EvaluationTargetRepository.Entities on project.EvaluationTargetID equals target.EvaluationTargetID
                         join table in EvaluationTableRepository.Entities on target.EvaluationTableID equals table.EvaluationTableID
                         join type in EvaluationTypeRepository.GetList(typeExp) on table.EvaluationTypeID equals type.EvaluationTypeID
                         join norm in EvaluationNormRepository.Entities on target.EvaluationNormID equals norm.EvaluationNormID 
                         join schoolyear in SchoolyearRepository.GetList(x => x.IsCurrent == true) on mission.SchoolyearID equals schoolyear.SchoolyearID
                         where score.StudentUserID != null
                         group new { score, scoreDetail, norm } by new { user.UserID, user.LoginID, user.Name, staff.TeacherTypeID, 
                             college.CollegeID, CollegeName = college.Name, TypeID = type.EvaluationTypeID, TypeName = type.Name } into g
                         select new SatisfyRateView
                         {
                             UserID = g.Key.UserID,
                             LoginID = g.Key.LoginID,
                             Name = g.Key.Name,
                             TeacherType = g.Key.TeacherTypeID,
                             CollegeID = g.Key.CollegeID,
                             CollegeName = g.Key.Name,
                             EvaluationTypeID = g.Key.TypeID,
                             EvaluationTypeName = g.Key.TypeName,
                             SatisfyRate = ((decimal?)g.Sum(x => (x.scoreDetail.EvaluationScore >= x.norm.SatisfyWeight) ? 1 : 0) / g.Sum(x => 1) * 100)
                         }).Concat(
                         (from staff in StaffRepository.GetList(exp)
                          join college in CollegeRepository.Entities on staff.CollegeID equals college.CollegeID
                          join user in UserRepository.Entities on staff.UserID equals user.UserID
                          from missionClassTeacher in staff.EM_MissionClassTeacher
                          join missionClass in EducationMissionClassRepository.Entities on missionClassTeacher.MissionClassID equals missionClass.EducationMissionClassID
                          join mission in EducationMissionRepository.Entities on missionClass.EducationMissionID equals mission.EducationMissionID
                          join score in EvaluationEnterRepository.Entities on missionClassTeacher.MissionClassID equals score.EducationMissionClassID
                          join scoreDetail in EvaluationEnter_EM_EvaluationProjectRepository.Entities on score.EvaluationEnterID equals scoreDetail.EvaluationEnterID
                          join project in EvaluationProjectRepository.Entities on scoreDetail.ProjectID equals project.EvaluationProjectID
                          join target in EvaluationTargetRepository.Entities on project.EvaluationTargetID equals target.EvaluationTargetID
                          join table in EvaluationTableRepository.Entities on target.EvaluationTableID equals table.EvaluationTableID
                          join type in EvaluationTypeRepository.GetList(typeExp) on table.EvaluationTypeID equals type.EvaluationTypeID
                          join norm in EvaluationNormRepository.Entities on target.EvaluationNormID equals norm.EvaluationNormID
                          join schoolyear in SchoolyearRepository.GetList(x => x.IsCurrent == true) on mission.SchoolyearID equals schoolyear.SchoolyearID
                          where score.StaffUserID != null
                          group new { score, scoreDetail, norm } by new { user.UserID, user.LoginID, user.Name, staff.TeacherTypeID,
                                                                          college.CollegeID,
                                                                          CollegeName = college.Name,
                                                                          TypeID = type.EvaluationTypeID,
                                                                          TypeName = type.Name
                          } into g
                          select new SatisfyRateView
                          {
                              UserID = g.Key.UserID,
                              LoginID = g.Key.LoginID,
                              Name = g.Key.Name,
                              TeacherType = g.Key.TeacherTypeID,
                              CollegeID = g.Key.CollegeID,
                              CollegeName = g.Key.Name,
                              EvaluationTypeID = g.Key.TypeID,
                              EvaluationTypeName = g.Key.TypeName,
                              SatisfyRate = ((decimal?)g.Sum(x => (x.scoreDetail.EvaluationScore >= x.norm.SatisfyWeight) ? 1 : 0) / g.Sum(x => 1) * 100)
                          })
                         );

            return query;
        }
    }
}