JW/EMiS.DataLogic/Common/EvaluationManage/EvaluationTeacherScoreDAL.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 Bowin.Common.Linq;
using EMIS.ViewModel.EvaluationManage.EvaluationEnter;
using EMIS.Utility;
using EMIS.DataLogic.Common.Students;


namespace EMIS.DataLogic.Common.EvaluationManage
{
    public class EvaluationTeacherScoreDAL
    {

        #region --0.0定义--
        public StaffRepository StaffPepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public MissionClassTeacherRepository MissionClassTeacherRepository { get; set; }
        public EducationMissionClassRepository EducationMissionClassRepository { get; set; }
        public EvaluationEnter_EM_EvaluationProjectRepository EvaluationEnter_EM_EvaluationProjectRepository { get; set; }
        public EvaluationEnterRepository EvaluationEnterRepository { get; set; }
        public EvaluationProjectRepository EvaluationProjectRepository { get; set; }
        public EvaluationTargetRepository EvaluationTargetRepository { get; set; }
        public EvaluationTableRepository EvaluationTableRepository { get; set; }
        public VWTotalScore_StudentRankRepository viewStudentRepository { get; set; }
        public VWTotalScore_TeacherRankRepository viewTeacherRepository { get; set; }
        #endregion

        #region 之前的尝试，貌似不太理想
        //var lastEvaluation = (from ee in EvaluationEnter_EM_EvaluationProjectRepository.GetList(x => x.EM_EvaluationEnter.StudentUserID != null)
        //                          group ee by new { ee.EM_EvaluationEnter.EducationMissionClassID } into g
        //                          join mmee in EvaluationEnter_EM_EvaluationProjectRepository.GetList(x => x.EM_EvaluationEnter.StudentUserID != null)
        //                            on new { g.Key.EducationMissionClassID, ModifyTime = g.Max(x => x.ModifyTime) } equals new { mmee.EM_EvaluationEnter.EducationMissionClassID, mmee.ModifyTime }
        //                          group mmee by new { mmee.EM_EvaluationEnter.EducationMissionClassID, mmee.ModifyTime } into g
        //                          join miee in EvaluationEnter_EM_EvaluationProjectRepository.GetList(x => x.EM_EvaluationEnter.StudentUserID != null)
        //                              on g.Max(x => x.ID.ToString()) equals miee.ID.ToString() 
        //                          select miee
        //                          );

        //    var sql = (from us in UserRepository.Entities
        //               join mctr in MissionClassTeacherRepository.Entities
        //                     on us.UserID equals mctr.UserID
        //               join emcr in EducationMissionClassRepository.Entities
        //                     on mctr.MissionClassID equals emcr.EducationMissionClassID 
        //               join eer in lastEvaluation
        //                     on emcr.EducationMissionClassID equals eer.EM_EvaluationEnter.EducationMissionClassID
        //               group eer by new { 
        //                   us.UserID, 
        //                   us.LoginID, 
        //                   UserName = us.Name, 
        //                   emcr.EducationMissionClassID, 
        //                   ClassName = emcr.Name,
        //                   emcr.EM_EducationMission.CF_Department.CollegeID,
        //                   CollegeName = emcr.EM_EducationMission.CF_Department.CF_College.Name,
        //                   emcr.EM_EducationMission.DepartmentID,
        //                   DepartmentName = emcr.EM_EducationMission.CF_Department.Name,
        //                   emcr.CoursematerialID,
        //                   emcr.EM_Coursematerial.CourseName
        //               } into na
        //               select new
        //               {
        //                   UserID = na.Key.UserID,
        //                   TeacherNo = na.Key.LoginID,
        //                   TeacherName = na.Key.UserName,
        //                   EducationMissionClassID = na.Key.EducationMissionClassID,
        //                   EducationMissionClassName = na.Key.ClassName,
        //                   CollegeID = na.Key.CollegeID,
        //                   CollegeName = na.Key.CollegeName,
        //                   DepartmentID = na.Key.DepartmentID,
        //                   DepartmentName = na.Key.DepartmentName,
        //                   CourseID = na.Key.CoursematerialID,
        //                   CourseName = na.Key.CourseName,
        //                   Count = na.Count(),
        //                   TotalScore = na.GroupBy(x => new { x.EM_EvaluationProject.EM_EvaluationTarget.TableID, x.EM_EvaluationProject.EM_EvaluationTarget.EM_EvaluationTable.Weight })
        //                                   .Select(x => x.Key.Weight * (x.GroupBy(y => new { y.EM_EvaluationProject.TargetID, y.EM_EvaluationProject.EM_EvaluationTarget.Weight })
        //                                       .Select(z => z.Key.Weight * z.Sum(w => (w.EvaluationScore / 100 * w.EM_EvaluationProject.Weight) / z.Key.Weight))
        //                                       .Sum()))
        //                                   .Sum()
        //               } into gg 
        //                select new
        //                {
        //                    TeacherNo = gg.TeacherNo,
        //                    TeacherName = gg.TeacherName,
        //                    EducationMissionClassID = gg.EducationMissionClassID,
        //                    EducationMissionClassName = gg.EducationMissionClassName,
        //                    CollegeID = gg.CollegeID,
        //                    CollegeName = gg.CollegeName,
        //                    DepartmentID = gg.DepartmentID,
        //                    DepartmentName = gg.DepartmentName,
        //                    CourseID = gg.CourseID,
        //                    CourseName = gg.CourseName,
        //                    CommentCount = gg.Count,
        //                    Average = Math.Round((decimal)gg.TotalScore / gg.Count, 2)
        //                }
        //        );
        //    var query = from g in sql
        //                select new EvaluationTeacherScoreView
        //                {
        //                    TeacherNo = g.TeacherNo,
        //                    TeacherName = g.TeacherName,
        //                    EducationMissionClassID = g.EducationMissionClassID,
        //                    EducationMissionClassName = g.EducationMissionClassName,
        //                    CollegeID = g.CollegeID,
        //                    CollegeName = g.CollegeName,
        //                    DepartmentID = g.DepartmentID,
        //                    DepartmentName = g.DepartmentName,
        //                    CourseID = g.CourseID,
        //                    CourseName = g.CourseName,
        //                    CommentCount = g.CommentCount,
        //                    Average = g.Average,
        //                    //DepartmentRanking = (from o in sql
        //                    //                     where o.Average > g.Average
        //                    //                     where o.DepartmentID == g.DepartmentID
        //                    //                     select o).Count() + 1,
        //                    //CollegeRanking = (from o in sql
        //                    //                  where o.Average > g.Average
        //                    //                  where o.CollegeID == g.CollegeID
        //                    //                  select o).Count() + 1
        //                };

        //    return query;
        #endregion


        #region 第二次尝试，查询速度不理想
        //    var sql = (
        //        from mct in MissionClassTeacherRepository.Entities
        //        join ee in (
        //            from etarget in (
        //                from ee in
        //                    (
        //                        from ee in EvaluationEnterRepository.GetList(expEe)
        //                        join mee in
        //                            (
        //                                from ee in EvaluationEnterRepository.GetList(expEe)
        //                                join mee in
        //                                    (
        //                                        from ee in EvaluationEnterRepository.GetList(expEe)
        //                                        group ee by new { ee.EducationMissionClassID, ee.StaffUserID, ee.StudentUserID } into g
        //                                        select new { g.Key.EducationMissionClassID, g.Key.StaffUserID, g.Key.StudentUserID, ModifyTime = g.Max(x => x.ModifyTime) }
        //                                        )
        //                                    on new { ee.EducationMissionClassID, ee.StaffUserID, ee.StudentUserID, ee.ModifyTime }
        //                                    equals new { mee.EducationMissionClassID, mee.StaffUserID, mee.StudentUserID, mee.ModifyTime }
        //                                group ee by new { mee.EducationMissionClassID, mee.StaffUserID, mee.StudentUserID, mee.ModifyTime }
        //                                    into g
        //                                    select new
        //                                    {
        //                                        g.Key.EducationMissionClassID,
        //                                        g.Key.StaffUserID,
        //                                        g.Key.StudentUserID,
        //                                        g.Key.ModifyTime,
        //                                        EvaluationEnterID = g.Max(x => x.EvaluationEnterID.ToString())
        //                                    })
        //                            on ee.EvaluationEnterID.ToString() equals mee.EvaluationEnterID
        //                        join eeep in
        //                            (
        //                                from eeep in EvaluationEnter_EM_EvaluationProjectRepository.Entities
        //                                join ee in EvaluationEnterRepository.GetList(expEe) on eeep.EvaluationEnterID equals ee.EvaluationEnterID 
        //                                join meeep in
        //                                    (
        //                                        from eeep in EvaluationEnter_EM_EvaluationProjectRepository.Entities
        //                                        join ee in EvaluationEnterRepository.GetList(expEe) on eeep.EvaluationEnterID equals ee.EvaluationEnterID 
        //                                        join meeep in
        //                                            (
        //                                                from eeep in EvaluationEnter_EM_EvaluationProjectRepository.Entities 
        //                                                join ee in EvaluationEnterRepository.GetList(expEe) on eeep.EvaluationEnterID equals ee.EvaluationEnterID 
        //                                                group eeep by new { eeep.EvaluationEnterID, eeep.ProjectID } into g
        //                                                select new { g.Key.EvaluationEnterID, g.Key.ProjectID, ModifyTime = g.Max(x => x.ModifyTime) }
        //                                                ) on new { eeep.EvaluationEnterID, eeep.ProjectID, eeep.ModifyTime }
        //                                            equals new { meeep.EvaluationEnterID, meeep.ProjectID, meeep.ModifyTime }
        //                                        group eeep by new { eeep.EvaluationEnterID, eeep.ProjectID, eeep.ModifyTime } into g
        //                                        select new { g.Key.EvaluationEnterID, g.Key.ProjectID, g.Key.ModifyTime, ID = g.Max(x => x.ID.ToString()) }
        //                                        ) on eeep.ID.ToString() equals meeep.ID
        //                                select eeep
        //                                ) on ee.EvaluationEnterID equals eeep.EvaluationEnterID
        //                        select new { EvaluationEnter = ee, eeep.ProjectID, eeep.EvaluationScore })
        //                join ep in EvaluationProjectRepository.Entities
        //                    on ee.ProjectID equals ep.ProjectID
        //                join et in EvaluationTargetRepository.Entities
        //                    on ep.TargetID equals et.TargetID
        //                group new { ee, ep, et } by new { 
        //                    TableTypeName=ee.EvaluationEnter.EM_EvaluationTable.EM_EvaluationType.Name,IntTypeName=ee.EvaluationEnter.EM_EvaluationTable.EM_EvaluationIntType.Name, 
        //                    ee.EvaluationEnter.EducationMissionClassID, ee.EvaluationEnter.StaffUserID, ee.EvaluationEnter.StudentUserID, ep.TargetID, et.TableID, et.Weight }
        //                    into g
        //                    select new
        //                    {
        //                        g.Key.TableTypeName,
        //                        g.Key.IntTypeName,
        //                        g.Key.EducationMissionClassID,
        //                        g.Key.StaffUserID,
        //                        g.Key.StudentUserID,
        //                        g.Key.TargetID,
        //                        g.Key.TableID,
        //                        g.Key.Weight,
        //                        TargetScore = g.Sum(x => x.ee.EvaluationScore / 100 * x.ep.Weight) / g.Sum(x => x.ep.Weight) * g.Key.Weight
        //                    }
        //                )
        //            join etable in EvaluationTableRepository.GetList(expTable) on etarget.TableID equals etable.TableID
        //            group etarget by new { etarget.TableTypeName, etarget.IntTypeName, etarget.EducationMissionClassID, etarget.StaffUserID, etarget.StudentUserID, etarget.TableID, etable.Weight }
        //                into g
        //                select new { g.Key.TableTypeName, g.Key.IntTypeName, g.Key.EducationMissionClassID, g.Key.StaffUserID, g.Key.StudentUserID, g.Key.TableID, TotalScore = g.Sum(x => x.TargetScore) / g.Sum(x => x.Weight) * g.Key.Weight }
        //        ) on mct.EM_EducationMissionClass.EducationMissionClassID equals ee.EducationMissionClassID 
        //        group ee by new { 
        //            TableTypeName = ee.TableTypeName,
        //            IntTypeName = ee.IntTypeName,
        //            TeacherNo = mct.CF_Staff.Sys_User.LoginID,
        //            TeacherName = mct.CF_Staff.Sys_User.Name,
        //            EducationMissionClassID = mct.EM_EducationMissionClass.EducationMissionClassID,
        //            EducationMissionClassName = mct.EM_EducationMissionClass.Name,
        //            CollegeID = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.CollegeID,
        //            CollegeName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.CF_College.Name,
        //            DepartmentID = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.DepartmentID,
        //            DepartmentName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.Name,
        //            CourseID = mct.EM_EducationMissionClass.EM_Coursematerial.CoursematerialID,
        //            CourseName = mct.EM_EducationMissionClass.EM_Coursematerial.CourseName,
        //            CourseCode = mct.EM_EducationMissionClass.EM_Coursematerial.CourseCode
        //        } into g 
        //        select new EvaluationTeacherScoreView 
        //        {
        //            TeacherNo = g.Key.TeacherNo,
        //            TeacherName = g.Key.TeacherName,
        //            EducationMissionClassID = g.Key.EducationMissionClassID,
        //            EducationMissionClassName = g.Key.EducationMissionClassName,
        //            CollegeID = g.Key.CollegeID,
        //            CollegeName = g.Key.CollegeName,
        //            DepartmentID = g.Key.DepartmentID,
        //            DepartmentName = g.Key.DepartmentName,
        //            CourseID = g.Key.CourseID,
        //            CourseName = g.Key.CourseName,
        //            CourseCode = g.Key.CourseCode,
        //            EvaluationIntTypeName = g.Key.IntTypeName,
        //            EvaluationTableTypeName = g.Key.TableTypeName,
        //            CommentCount = g.Count(),
        //            Average = Math.Round(g.Average(x => x.TotalScore ?? 0), 2),
        //            DepartmentRanking = 0,
        //            CollegeRanking = 0
        //        }
        //    );

        //    var query = (from ee in sql
        //                 select new EvaluationTeacherScoreView
        //                 {
        //                     TeacherNo = ee.TeacherNo,
        //                     TeacherName = ee.TeacherName,
        //                     EducationMissionClassID = ee.EducationMissionClassID,
        //                     EducationMissionClassName = ee.EducationMissionClassName,
        //                     CollegeID = ee.CollegeID,
        //                     CollegeName = ee.CollegeName,
        //                     DepartmentID = ee.DepartmentID,
        //                     DepartmentName = ee.DepartmentName,
        //                     CourseID = ee.CourseID,
        //                     CourseName = ee.CourseName,
        //                     CourseCode = ee.CourseCode,
        //                     EvaluationIntTypeName = ee.EvaluationIntTypeName,
        //                     EvaluationTableTypeName = ee.EvaluationTableTypeName,
        //                     CommentCount = ee.CommentCount,
        //                     Average = ee.Average,
        //                     DepartmentRanking = sql.Where(x => x.Average > ee.Average && x.DepartmentID == ee.DepartmentID).Count() + 1,
        //                     CollegeRanking = sql.Where(x => x.Average > ee.Average && x.CollegeID == ee.CollegeID).Count() + 1
        //                 });

        //return query;
        #endregion
        //1.0查询基本信息
        #region 第三次尝试，查询速度不理想
        //有了总分字段之后还是查询效率低
        //var sql =
        //          from ee in EvaluationEnterRepository.GetList(expEe)
        //          join mee in
        //              (from ee in EvaluationEnterRepository.GetList(expEe)
        //               join mee in
        //                   (
        //                       from ee in EvaluationEnterRepository.GetList(expEe)
        //                       group ee by new { ee.EducationMissionClassID, ee.StaffUserID, ee.StudentUserID } into g
        //                       select new { g.Key.EducationMissionClassID, g.Key.StaffUserID, g.Key.StudentUserID, ModifyTime = g.Max(x => x.ModifyTime) }
        //                   )
        //               on new { ee.EducationMissionClassID, ee.StaffUserID, ee.StudentUserID, ee.ModifyTime }
        //               equals new { mee.EducationMissionClassID, mee.StaffUserID, mee.StudentUserID, mee.ModifyTime }
        //               group ee by new { mee.EducationMissionClassID, mee.StaffUserID, mee.StudentUserID, mee.ModifyTime }
        //                   into g
        //                   select new
        //                   {
        //                       g.Key.EducationMissionClassID,
        //                       g.Key.StaffUserID,
        //                       g.Key.StudentUserID,
        //                       EvaluationEnterID = g.Max(x => x.EvaluationEnterID.ToString())
        //                   })
        //                  on ee.EvaluationEnterID.ToString() equals mee.EvaluationEnterID
        //          join mct in MissionClassTeacherRepository.Entities
        //          on mee.EducationMissionClassID equals mct.EM_EducationMissionClass.EducationMissionClassID
        //          group ee by new
        //            {
        //                TableTypeName = ee.EM_EvaluationTable.EM_EvaluationType.Name,
        //                IntTypeName = ee.EM_EvaluationTable.EM_EvaluationIntType.Name,
        //                TeacherNo = mct.CF_Staff.Sys_User.LoginID,
        //                TeacherName = mct.CF_Staff.Sys_User.Name,
        //                EducationMissionClassID = mct.EM_EducationMissionClass.EducationMissionClassID,
        //                EducationMissionClassName = mct.EM_EducationMissionClass.Name,
        //                CollegeID = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.CollegeID,
        //                CollegeName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.CF_College.Name,
        //                DepartmentID = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.DepartmentID,
        //                DepartmentName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.Name,
        //                CourseID = mct.EM_EducationMissionClass.EM_Coursematerial.CoursematerialID,
        //                CourseName = mct.EM_EducationMissionClass.EM_Coursematerial.CourseName,
        //                CourseCode = mct.EM_EducationMissionClass.EM_Coursematerial.CourseCode
        //            } into g
        //          select new EvaluationTeacherScoreView
        //            {
        //                TeacherNo = g.Key.TeacherNo,
        //                TeacherName = g.Key.TeacherName,
        //                EducationMissionClassID = g.Key.EducationMissionClassID,
        //                EducationMissionClassName = g.Key.EducationMissionClassName,
        //                CollegeID = g.Key.CollegeID,
        //                CollegeName = g.Key.CollegeName,
        //                DepartmentID = g.Key.DepartmentID,
        //                DepartmentName = g.Key.DepartmentName,
        //                CourseID = g.Key.CourseID,
        //                CourseName = g.Key.CourseName,
        //                CourseCode = g.Key.CourseCode,
        //                EvaluationIntTypeName = g.Key.IntTypeName,
        //                EvaluationTableTypeName = g.Key.TableTypeName,
        //                CommentCount = g.Count(),
        //                Average = Math.Round(g.Average(x => x.TotalScore ?? 0), 2),
        //                DepartmentRanking = 0,
        //                CollegeRanking = 0
        //            };
        //var query = (from ee in sql
        //             select new EvaluationTeacherScoreView
        //             {
        //                 TeacherNo = ee.TeacherNo,
        //                 TeacherName = ee.TeacherName,
        //                 EducationMissionClassID = ee.EducationMissionClassID,
        //                 EducationMissionClassName = ee.EducationMissionClassName,
        //                 CollegeID = ee.CollegeID,
        //                 CollegeName = ee.CollegeName,
        //                 DepartmentID = ee.DepartmentID,
        //                 DepartmentName = ee.DepartmentName,
        //                 CourseID = ee.CourseID,
        //                 CourseName = ee.CourseName,
        //                 CourseCode = ee.CourseCode,
        //                 EvaluationIntTypeName = ee.EvaluationIntTypeName,
        //                 EvaluationTableTypeName = ee.EvaluationTableTypeName,
        //                 CommentCount = ee.CommentCount,
        //                 Average = (decimal)(ee.Average == null ? 0 : ee.Average),
        //                 DepartmentRanking = sql.Where(x => x.Average > ee.Average && x.DepartmentID == ee.DepartmentID).Count() + 1,
        //                 CollegeRanking = sql.Where(x => x.Average > ee.Average && x.CollegeID == ee.CollegeID).Count() + 1
        //             });
        #endregion


        public IQueryable<EvaluationTeacherScoreView> GetEvaluationTeacherScoreQuery(Expression<Func<EM_MissionClassTeacher, bool>> expmct, Expression<Func<EM_EvaluationTable, bool>> expTable, Guid? schoolyaerID)
        {
            var stuquery = from v in viewStudentRepository.Entities
                           join tbl in EvaluationTableRepository.GetList(expTable)
                           on v.tableID equals tbl.EvaluationTableID
                           join mct in MissionClassTeacherRepository.GetList(expmct)
                           on v.MissionClassTeacherID equals mct.MissionClassTeacherID
                           select new EvaluationTeacherScoreView
                           {
                               UserID = mct.UserID.Value,
                               TeacherNo = mct.CF_Staff.Sys_User.LoginID,
                               TeacherName = mct.CF_Staff.Sys_User.Name,
                               EducationMissionClassID = v.EducationMissionClassID,
                               EducationMissionClassName = mct.EM_EducationMissionClass.Name,
                               CollegeID = v.CollegeID,
                               CollegeName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.CF_College.Name,
                               DepartmentID = v.DepartmentID,
                               DepartmentName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.Name,
                               CourseID = mct.EM_EducationMissionClass.EM_Coursematerial.CoursematerialID,
                               CourseName = mct.EM_EducationMissionClass.EM_Coursematerial.CourseName,
                               CourseCode = mct.EM_EducationMissionClass.EM_Coursematerial.CourseCode,
                               EvaluationIntTypeName = tbl.EM_EvaluationIntType.Name,
                               EvaluationTableTypeName = tbl.EM_EvaluationType.Name,
                               CommentCount = (int)v.stuCount,
                               Average = Math.Round((decimal)v.allavg, 2),
                               DepartmentRanking = (int)v.DepartmentRanking,
                               CollegeRanking = (int)v.CollegeRanking
                           };
            var teaquery = from v in viewTeacherRepository.Entities
                           join tbl in EvaluationTableRepository.GetList(expTable)
                           on v.tableID equals tbl.EvaluationTableID
                           join mct in MissionClassTeacherRepository.GetList(expmct)
                           on v.MissionClassTeacherID equals mct.MissionClassTeacherID
                           //on v.EducationMissionClassID equals mct.EM_EducationMissionClass.EducationMissionClassID
                           select new EvaluationTeacherScoreView
                           {
                               UserID = mct.UserID.Value,
                               TeacherNo = mct.CF_Staff.Sys_User.LoginID,
                               TeacherName = mct.CF_Staff.Sys_User.Name,
                               EducationMissionClassID = v.EducationMissionClassID,
                               EducationMissionClassName = mct.EM_EducationMissionClass.Name,
                               CollegeID = v.CollegeID,
                               CollegeName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.CF_College.Name,
                               DepartmentID = v.DepartmentID,
                               DepartmentName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.Name,
                               CourseID = mct.EM_EducationMissionClass.EM_Coursematerial.CoursematerialID,
                               CourseName = mct.EM_EducationMissionClass.EM_Coursematerial.CourseName,
                               CourseCode = mct.EM_EducationMissionClass.EM_Coursematerial.CourseCode,
                               EvaluationIntTypeName = tbl.EM_EvaluationIntType.Name,
                               EvaluationTableTypeName = tbl.EM_EvaluationType.Name,
                               CommentCount = (int)v.teaCount,
                               Average = Math.Round((decimal)v.allavg, 2),
                               DepartmentRanking = (int)v.DepartmentRanking,
                               CollegeRanking = (int)v.CollegeRanking
                           };
            var query = from tbl in EvaluationTableRepository.GetList(expTable)
                        select new EvaluationTableView
                        {
                            Type = tbl.EM_EvaluationType.Name,
                            EvaluationIntTypeName = tbl.EM_EvaluationIntType.Name
                        };
            if (schoolyaerID != null)//选择学年学期
            {
                stuquery = from v in viewStudentRepository.Entities
                           join tbl in EvaluationTableRepository.GetList(expTable)
                           on v.tableID equals tbl.EvaluationTableID
                           join mct in MissionClassTeacherRepository.GetList(expmct)
                           on v.MissionClassTeacherID equals mct.MissionClassTeacherID
                           //on v.EducationMissionClassID equals mct.EM_EducationMissionClass.EducationMissionClassID
                           select new EvaluationTeacherScoreView
                           {
                               UserID = mct.UserID.Value,
                               TeacherNo = mct.CF_Staff.Sys_User.LoginID,
                               TeacherName = mct.CF_Staff.Sys_User.Name,
                               EducationMissionClassID = v.EducationMissionClassID,
                               EducationMissionClassName = mct.EM_EducationMissionClass.Name,
                               CollegeID = v.CollegeID,
                               CollegeName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.CF_College.Name,
                               DepartmentID = v.DepartmentID,
                               DepartmentName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.Name,
                               CourseID = mct.EM_EducationMissionClass.EM_Coursematerial.CoursematerialID,
                               CourseName = mct.EM_EducationMissionClass.EM_Coursematerial.CourseName,
                               CourseCode = mct.EM_EducationMissionClass.EM_Coursematerial.CourseCode,
                               EvaluationIntTypeName = tbl.EM_EvaluationIntType.Name,
                               EvaluationTableTypeName = tbl.EM_EvaluationType.Name,
                               CommentCount = (int)v.stuCount,
                               Average = Math.Round((decimal)v.allavg, 2),
                               DepartmentRanking = (int)v.YearDepartmentRanking,
                               CollegeRanking = (int)v.YearCollegeRanking
                           };
                teaquery = from v in viewTeacherRepository.Entities
                           join tbl in EvaluationTableRepository.GetList(expTable)
                           on v.tableID equals tbl.EvaluationTableID
                           join mct in MissionClassTeacherRepository.GetList(expmct)
                           on v.MissionClassTeacherID equals mct.MissionClassTeacherID
                           //on v.EducationMissionClassID equals mct.EM_EducationMissionClass.EducationMissionClassID
                           select new EvaluationTeacherScoreView
                           {
                               UserID = mct.UserID.Value,
                               TeacherNo = mct.CF_Staff.Sys_User.LoginID,
                               TeacherName = mct.CF_Staff.Sys_User.Name,
                               EducationMissionClassID = v.EducationMissionClassID,
                               EducationMissionClassName = mct.EM_EducationMissionClass.Name,
                               CollegeID = v.CollegeID,
                               CollegeName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.CF_College.Name,
                               DepartmentID = v.DepartmentID,
                               DepartmentName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.Name,
                               CourseID = mct.EM_EducationMissionClass.EM_Coursematerial.CoursematerialID,
                               CourseName = mct.EM_EducationMissionClass.EM_Coursematerial.CourseName,
                               CourseCode = mct.EM_EducationMissionClass.EM_Coursematerial.CourseCode,
                               EvaluationIntTypeName = tbl.EM_EvaluationIntType.Name,
                               EvaluationTableTypeName = tbl.EM_EvaluationType.Name,
                               CommentCount = (int)v.teaCount,
                               Average = Math.Round((decimal)v.allavg, 2),
                               DepartmentRanking = (int)v.YearDepartmentRanking,
                               CollegeRanking = (int)v.YearCollegeRanking
                           };
            }
            if (query.ToList().Count() == 1)//选择参评类型
            {
                stuquery = from v in viewStudentRepository.Entities
                           join tbl in EvaluationTableRepository.GetList(expTable)
                           on v.tableID equals tbl.EvaluationTableID
                           join mct in MissionClassTeacherRepository.GetList(expmct)
                           on v.MissionClassTeacherID equals mct.MissionClassTeacherID
                           //on v.EducationMissionClassID equals mct.EM_EducationMissionClass.EducationMissionClassID
                           select new EvaluationTeacherScoreView
                           {
                               UserID = mct.UserID.Value,
                               TeacherNo = mct.CF_Staff.Sys_User.LoginID,
                               TeacherName = mct.CF_Staff.Sys_User.Name,
                               EducationMissionClassID = v.EducationMissionClassID,
                               EducationMissionClassName = mct.EM_EducationMissionClass.Name,
                               CollegeID = v.CollegeID,
                               CollegeName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.CF_College.Name,
                               DepartmentID = v.DepartmentID,
                               DepartmentName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.Name,
                               CourseID = mct.EM_EducationMissionClass.EM_Coursematerial.CoursematerialID,
                               CourseName = mct.EM_EducationMissionClass.EM_Coursematerial.CourseName,
                               CourseCode = mct.EM_EducationMissionClass.EM_Coursematerial.CourseCode,
                               EvaluationIntTypeName = tbl.EM_EvaluationIntType.Name,
                               EvaluationTableTypeName = tbl.EM_EvaluationType.Name,
                               CommentCount = (int)v.stuCount,
                               Average = Math.Round((decimal)v.allavg, 2),
                               DepartmentRanking = (int)v.DepartmentIntTypeRanking,
                               CollegeRanking = (int)v.CollegeIntTypeRanking
                           };
                teaquery = from v in viewTeacherRepository.Entities
                           join tbl in EvaluationTableRepository.GetList(expTable)
                           on v.tableID equals tbl.EvaluationTableID
                           join mct in MissionClassTeacherRepository.GetList(expmct)
                           on v.MissionClassTeacherID equals mct.MissionClassTeacherID
                           //on v.EducationMissionClassID equals mct.EM_EducationMissionClass.EducationMissionClassID
                           select new EvaluationTeacherScoreView
                           {
                               UserID = mct.UserID.Value,
                               TeacherNo = mct.CF_Staff.Sys_User.LoginID,
                               TeacherName = mct.CF_Staff.Sys_User.Name,
                               EducationMissionClassID = v.EducationMissionClassID,
                               EducationMissionClassName = mct.EM_EducationMissionClass.Name,
                               CollegeID = v.CollegeID,
                               CollegeName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.CF_College.Name,
                               DepartmentID = v.DepartmentID,
                               DepartmentName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.Name,
                               CourseID = mct.EM_EducationMissionClass.EM_Coursematerial.CoursematerialID,
                               CourseName = mct.EM_EducationMissionClass.EM_Coursematerial.CourseName,
                               CourseCode = mct.EM_EducationMissionClass.EM_Coursematerial.CourseCode,
                               EvaluationIntTypeName = tbl.EM_EvaluationIntType.Name,
                               EvaluationTableTypeName = tbl.EM_EvaluationType.Name,
                               CommentCount = (int)v.teaCount,
                               Average = Math.Round((decimal)v.allavg, 2),
                               DepartmentRanking = (int)v.DepartmentIntTypeRanking,
                               CollegeRanking = (int)v.CollegeIntTypeRanking
                           };
            }
            if (schoolyaerID != null && query.ToList().Count() == 1)
            {
                stuquery = from v in viewStudentRepository.Entities
                           join tbl in EvaluationTableRepository.GetList(expTable)
                           on v.tableID equals tbl.EvaluationTableID
                           join mct in MissionClassTeacherRepository.GetList(expmct)
                           on v.MissionClassTeacherID equals mct.MissionClassTeacherID
                           //on v.EducationMissionClassID equals mct.EM_EducationMissionClass.EducationMissionClassID
                           select new EvaluationTeacherScoreView
                           {
                               UserID = mct.UserID.Value,
                               TeacherNo = mct.CF_Staff.Sys_User.LoginID,
                               TeacherName = mct.CF_Staff.Sys_User.Name,
                               EducationMissionClassID = v.EducationMissionClassID,
                               EducationMissionClassName = mct.EM_EducationMissionClass.Name,
                               CollegeID = v.CollegeID,
                               CollegeName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.CF_College.Name,
                               DepartmentID = v.DepartmentID,
                               DepartmentName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.Name,
                               CourseID = mct.EM_EducationMissionClass.EM_Coursematerial.CoursematerialID,
                               CourseName = mct.EM_EducationMissionClass.EM_Coursematerial.CourseName,
                               CourseCode = mct.EM_EducationMissionClass.EM_Coursematerial.CourseCode,
                               EvaluationIntTypeName = tbl.EM_EvaluationIntType.Name,
                               EvaluationTableTypeName = tbl.EM_EvaluationType.Name,
                               CommentCount = (int)v.stuCount,
                               Average = Math.Round((decimal)v.allavg, 2),
                               DepartmentRanking = (int)v.YearDepartmentIntRanking,
                               CollegeRanking = (int)v.YearCollegeIntRanking
                           };
                teaquery = from v in viewTeacherRepository.Entities
                           join tbl in EvaluationTableRepository.GetList(expTable)
                           on v.tableID equals tbl.EvaluationTableID
                           join mct in MissionClassTeacherRepository.GetList(expmct)
                           on v.MissionClassTeacherID equals mct.MissionClassTeacherID
                           //on v.EducationMissionClassID equals mct.EM_EducationMissionClass.EducationMissionClassID
                           select new EvaluationTeacherScoreView
                           {
                               UserID = mct.UserID.Value,
                               TeacherNo = mct.CF_Staff.Sys_User.LoginID,
                               TeacherName = mct.CF_Staff.Sys_User.Name,
                               EducationMissionClassID = v.EducationMissionClassID,
                               EducationMissionClassName = mct.EM_EducationMissionClass.Name,
                               CollegeID = v.CollegeID,
                               CollegeName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.CF_College.Name,
                               DepartmentID = v.DepartmentID,
                               DepartmentName = mct.EM_EducationMissionClass.EM_EducationMission.CF_Department.Name,
                               CourseID = mct.EM_EducationMissionClass.EM_Coursematerial.CoursematerialID,
                               CourseName = mct.EM_EducationMissionClass.EM_Coursematerial.CourseName,
                               CourseCode = mct.EM_EducationMissionClass.EM_Coursematerial.CourseCode,
                               EvaluationIntTypeName = tbl.EM_EvaluationIntType.Name,
                               EvaluationTableTypeName = tbl.EM_EvaluationType.Name,
                               CommentCount = (int)v.teaCount,
                               Average = Math.Round((decimal)v.allavg, 2),
                               DepartmentRanking = (int)v.YearDepartmentIntRanking,
                               CollegeRanking = (int)v.YearCollegeIntRanking
                           };
            }
            if (query.FirstOrDefault().Type.Equals("学生评") || query.FirstOrDefault().Type == "学生评")
            {
                return stuquery;
            }

            return teaquery;
        }
    }
}