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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.DataLogic.Repositories;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.ViewModel.EvaluationManage;
using EMIS.DataLogic.SystemDAL;
using EMIS.ViewModel;
using EMIS.ViewModel.EducationManage;
using EMIS.ViewModel.EvaluationManage.EvaluationEnter;
using EMIS.Utility;
using EMIS.DataLogic.Common.Students;
using Bowin.Common.Linq;

namespace EMIS.DataLogic.Common.EvaluationManage
{
    public class StudentEvaluationCountDAL
    {
        public StudentRepository StudentRepository { get; set; }
        public EvaluationSettingRepository EvaluationSettingRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        //public StudentEvaluationRepository StudentEvaluationRepository { get; set; }
        public EducationMissionRepository EducationMissionRepository { get; set; }
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public EM_EvaluationSetting EvaluationSetting { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public EvaluationEnterRepository EvaluationEnterRepository { get; set; }
        public IQueryable<StudentEvaluationCountView> GetStudentView(Expression<Func<EMIS.Entities.CF_Schoolyear, bool>> exp
           ) 
            // Expression<Func<EM_EducationMissionClass, bool>> exp =null
        {
            //var query = from esr in EvaluationSettingRepository.Entities
            //            join em in EducationMissionClassRepository.Entities on esr.EducationMissionClassID equals em.EducationMissionClassID into emcr
            //            from sr in emcr.DefaultIfEmpty()
            //            from s in esr.CF_Student
            //            select new StudentEvaluationCountView
            //            {
            //                CollegeID = s.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
            //                //StartSchoolyearValue = (s.CF_Classmajor.CF_Grademajor.SchoolyearID.Value * 2) - 1 + (s.CF_Classmajor.CF_Grademajor.SchoolcodeID.Value - 1),
            //                //EndSchoolyearValue = s.CF_Classmajor.CF_Grademajor.CF_Schoolyear.Value,
            //                SchoolyearNumID = //esr.EM_EducationMissionClass.EM_EducationMission.CF_Schoolyear.SchoolcodeID,
            //                (int?)Math.Ceiling((decimal)(((s.CF_Classmajor.CF_Grademajor.SchoolyearID.Value * 2) - 1 + (s.CF_Classmajor.CF_Grademajor.SchoolcodeID.Value - 1)) - (s.CF_Classmajor.CF_Grademajor.CF_Schoolyear.Value) + 1) / 2),
            //                StandardID = s.CF_Classmajor.CF_Grademajor.CF_Facultymajor.StandardID,
            //                StudentNo = s.Sys_User.LoginID,
            //                Name = s.Sys_User.Name,
            //                ClassmajorID = s.ClassmajorID,
            //                ClassmajorName = s.CF_Classmajor.Name,
            //                SchoolyearID = esr.EM_EducationMissionClass.EM_EducationMission.CF_Schoolyear.SchoolyearID,//sr.EM_EducationMission.CF_Schoolyear.SchoolyearID,
            //                SchoolyearCode = esr.EM_EducationMissionClass.EM_EducationMission.CF_Schoolyear.Code,
            //                CompleteCount = s.EM_EvaluationSetting.Where(x => x.EM_EducationMissionClass.EM_EvaluationEnter.Any(z => z.CF_Student == s)).Count(),
            //                NeedEvaluationCount = s.EM_EvaluationSetting.Where(x => x.OpenStatus == 0).Count(),
            //                UncompletedCount = s.EM_EvaluationSetting.Where(x => x.OpenStatus == 0).Count() - s.EM_EvaluationSetting.Where(x => x.EM_EducationMissionClass.EM_EvaluationEnter.Any(z => z.CF_Student == s)).Count(),
            //            };

            //return query.Distinct();

            //var query = from s in StudentRepository.Entities
            //            from sc in s.EM_EducationSchedulingClass
            //            join ms in EducationMissionClassRepository.Entities on sc.EducationMissionClassID equals ms.EducationMissionClassID
            //            from set in s.EM_EvaluationSetting.Where(x => x.EducationMissionClassID == sc.EM_EducationMissionClass.EducationMissionClassID)
            //            //group s by new 
            //            //{
            //            //    s.Sys_User.LoginID,
            //            //    s.Sys_User.UserID
            //            //}
            //            select new StudentEvaluationCountView
            //            { 
            //                CollegeID = s.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
            //                SchoolyearNumID = s.CF_Classmajor.CF_Grademajor.SchoolyearID,
            //                StandardID = s.CF_Classmajor.CF_Grademajor.CF_Facultymajor.StandardID,
            //                StudentNo = s.Sys_User.LoginID,
            //                Name = s.Sys_User.Name,
            //                ClassmajorID = s.ClassmajorID,
            //                ClassmajorName = s.CF_Classmajor.Name,
            //                SchoolyearID = sc.EM_EducationMissionClass.EM_EducationMission.CF_Schoolyear.SchoolyearID,
            //                SchoolyearCode = sc.EM_EducationMissionClass.EM_EducationMission.CF_Schoolyear.Code,
            //                CompleteCount = s.EM_EvaluationSetting.Where(x => x.EM_EducationMissionClass.EM_EvaluationEnter.Any(z => z.CF_Student == s)).Count(),
            //                NeedEvaluationCount = s.EM_EvaluationSetting.Where(x => x.OpenStatus == 0).Count(),
            //                UncompletedCount = s.EM_EvaluationSetting.Where(x => x.OpenStatus == 0).Count() - s.EM_EvaluationSetting.Where(x => x.EM_EducationMissionClass.EM_EvaluationEnter.Any(z => z.CF_Student == s)).Count(),
            //            };

            //return query;//.GroupBy(x => x.StudentNo).Select(y => y.FirstOrDefault());

            //var query1 = from stu in StudentRepository.Entities
            //             from es in stu.EM_EvaluationSetting
            //             group stu by stu.UserID into ns
            //             select new 
            //             {
                             
            //                 NeedEvaluationCount = ns.Count()
            //             };

            //var query2 = from stu in StudentRepository.Entities
            //             from est in stu.EM_EvaluationSetting
            //             join et in EvaluationEnterRepository.Entities on new { est.EducationMissionClassID, UserID=(Guid?)stu.UserID } equals new { et.EducationMissionClassID, UserID=et.StudentUserID } into es_et
            //             group stu by stu.UserID into nst
            //             select new
            //             {
                             
            //                 CompleteCount = nst.Count()
            //             };




            var query3 = from s in StudentRepository.Entities
                         join nses in
                             (from stu in StudentRepository.Entities
                              from es in stu.EM_EvaluationSetting.Where(x => x.OpenStatus == 1)
                              join em in EducationMissionClassRepository.Entities on es.EducationMissionClassID equals em.EducationMissionClassID
                              join emc in EducationMissionRepository.Entities on em.EducationMissionID equals emc.EducationMissionID
                              join sy in SchoolyearRepository.GetList(exp) on emc.SchoolyearID equals sy.SchoolyearID
                              group stu by new { stu.UserID, sy.SchoolyearID, sy.Code } into ns
                              //from sy in SchoolyearRepository.Entities
                              //from stu in StudentRepository.Entities
                              select new
                              {

                                  SchoolCode = ns.Key.Code,
                                  SchoolyearID = ns.Key.SchoolyearID,
                                  UserID = ns.Key.UserID,
                                  NeedEvaluationCount = ns.Count()
                              }) on s.UserID equals nses.UserID 
                         join set in
                             (from stu in StudentRepository.Entities
                              from est in stu.EM_EvaluationSetting.Where(x => x.OpenStatus == 1)
                              join em in EducationMissionClassRepository.Entities on est.EducationMissionClassID equals em.EducationMissionClassID
                              join emc in EducationMissionRepository.Entities on em.EducationMissionID equals emc.EducationMissionID
                              join sy in SchoolyearRepository.GetList(exp) on emc.SchoolyearID equals sy.SchoolyearID
                              //join ee in EvaluationEnterRepository.Entities on est.EducationMissionClassID equals ee.EducationMissionClassID
                              //where ee.StudentUserID == stu.UserID
                              where (from et in EvaluationEnterRepository.Entities.Where(x => x.StudentUserID == stu.UserID) select et.EducationMissionClassID).Contains(est.EducationMissionClassID)
                              //join et in EvaluationEnterRepository.Entities.DefaultIfEmpty() on new { est.EducationMissionClassID, UserID = (Guid?)stu.UserID } equals new { et.EducationMissionClassID, UserID = et.StudentUserID } into es_et
                              group stu by new { stu.UserID, sy.SchoolyearID, sy.Code } into nst
                              //from nt in nst.Distinct()
                              //from stu in StudentRepository.Entities
                              select new
                              {
                                  SchoolCode = nst.Key.Code,
                                  SchoolyearID = nst.Key.SchoolyearID,
                                  UserID = nst.Key.UserID,
                                  CompleteCount = nst.Count(),
                              }) on s.UserID equals set.UserID
                              into g
                         from n_set in g.DefaultIfEmpty()
                         select new StudentEvaluationCountView
                         {
                             CollegeID = s.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
                             SchoolyearNumID = s.CF_Classmajor.CF_Grademajor.GradeID,
                             StandardID = s.CF_Classmajor.CF_Grademajor.CF_Facultymajor.StandardID,
                             StudentNo = s.Sys_User.LoginID,
                             Name = s.Sys_User.Name,
                             ClassmajorID = s.ClassmajorID,
                             ClassmajorName = s.CF_Classmajor.Name,
                             SchoolyearID = n_set.SchoolyearID,//s.CF_Classmajor.CF_Grademajor.CF_Schoolyear.SchoolyearID,
                             SchoolyearCode = nses.SchoolCode,//s.CF_Classmajor.CF_Grademajor.CF_Schoolyear.Code,
                             CompleteCount = n_set.CompleteCount == null ? 0 : n_set.CompleteCount,
                             NeedEvaluationCount = nses.NeedEvaluationCount,
                             UncompletedCount = nses.NeedEvaluationCount - (n_set.CompleteCount == null ? 0 : n_set.CompleteCount),
                         };

            return query3;
        }

    }

}