JW/EMiS.DataLogic/ExaminationApply/ExaminationSubjectDAL.cs

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

namespace EMIS.DataLogic.ExaminationApply
{
    public class ExaminationSubjectDAL
    {
        public ExaminationSubjectRepository ExaminationSubjectRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public ExaminationRegistrationRepository ExaminationRegistrationRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public ClassmajorRepository classmajorRepository { get; set; }
        public GrademajorRepository grademajorRepository { get; set; }
        public StudentRepository studentRepository { get; set; }
        public IQueryable<ExaminationSubjectView> GetExaminationSubjectView(Expression<Func<EX_ExaminationSubject, bool>> examinationSubjectExpression)
        {
            var q = (from es in ExaminationSubjectRepository.GetList(examinationSubjectExpression)
                     join pes in ExaminationSubjectRepository.Entities on es.PreposeSubjectID equals pes.ExaminationSubjectID into dpes
                     from epes in dpes.DefaultIfEmpty()
                     orderby es.Name
                     select new ExaminationSubjectView
                     {
                         ExaminationSubjectID = es.ExaminationSubjectID,
                         ExaminationTypeID = es.ExaminationTypeID,
                         ExaminationTypeName = es.EX_ExaminationType.Name,
                         SchoolyearID = es.SchoolyearID,
                         SchoolyearCode = es.CF_Schoolyear.Code,
                         ExaminationFormID = es.ExaminationFormID,
                         ExaminationFormsID=es.ExaminationFormID,
                         Name = es.Name,
                         PreposeSubjectID = es.PreposeSubjectID,
                         PreposeSubjectName = epes.Name,
                         PreposeScoreLimit = es.PreposeScoreLimit,
                         ExaminationDate = es.ExaminationDate,
                         PeopleNumLimit = es.PeopleNumLimit,
                         Cost = es.Cost,
                         Remark = es.Remark,
                         RecordStatus = es.RecordStatus,
                         CreateUserID = es.CreateUserID,
                         CreateTime = es.CreateTime,
                         ModifyUserID = es.ModifyUserID,
                         ModifyTime = es.ModifyTime
                     });

            return q;
        }

        public IQueryable<ExaminationSubjectView> GetExaminationSubjectView1(Expression<Func<EX_ExaminationSubject, bool>> examinationSubjectExpression)
        {
            var q = (from es in ExaminationSubjectRepository.GetList(examinationSubjectExpression).Select(x=>x.Name).Distinct()
                     
                     //orderby es.Name
                     select new ExaminationSubjectView
                     {
                         
                         Name = es
                         
                     });

            return q;
        }
        public IQueryable<StudentListView> GetRegistedListView(Expression<Func<EX_ExaminationSubject, bool>> exp,
           Expression<Func<CF_Student, bool>> studentExp, Expression<Func<EX_ExaminationRegistration, bool>> exr)
        {
            var q = (from es in ExaminationSubjectRepository.GetList(exp)
                     join er in ExaminationRegistrationRepository.GetList(exr) on es.ExaminationSubjectID equals er.ExaminationSubjectID
                     join year in SchoolyearRepository.Entities on er.ExaminationSchoolyearID equals year.SchoolyearID//换成考试学年学期
                     join s in StudentRepository.GetList(studentExp) on er.UserID equals s.UserID
                     group s by new
                     {
                         er.ExaminationRegistrationID,
                         s.UserID,
                         StudentNo = s.Sys_User.LoginID,
                         Name = s.Sys_User.Name,
                         s.SexID,
                         s.ClassmajorID,
                         ClassmajorNo = s.CF_Classmajor.No,
                         ClassmajorName = s.CF_Classmajor.Name,
                         s.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
                         CollegeName = s.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CF_College.Name,
                         SubjectName = es.Name,
                         er.CertificatesType,
                         er.IDNumber,
                         //exSchoolyearID = er.ExaminationSchoolyearID,
                         SchoolyearCode = year.Code,
                         s.CF_Classmajor.CF_Grademajor.CF_Facultymajor.EducationID,
                         s.CF_Classmajor.CF_Grademajor.CF_Facultymajor.LearnSystem,
                         s.CF_Classmajor.CF_Grademajor.CF_Facultymajor.StandardID,
                         s.CF_Classmajor.CF_Grademajor.GradeID,
                         StartSchoolyearValue = (s.CF_Classmajor.CF_Grademajor.GradeID.Value * 2) - 1 + (s.CF_Classmajor.CF_Grademajor.SemesterID.Value - 1),
                         EndSchoolyearValue = es.CF_Schoolyear.Value,
                         PhotoUrl = s.PhotoUrl,
                         CardNo = s.CF_StudentAccount.CardNo,
                         Cost = es.Cost,
                         IsPay = er.IsPay,
                         SchoolyearIDGuid = year.SchoolyearID,
                         ExaminationSubjectID = es.ExaminationSubjectID,
                     } into g
                     select new StudentListView
                     {
                         ExaminationRegistrationID = g.Key.ExaminationRegistrationID,
                         CardNo = g.Key.CardNo,
                         UserID = g.Key.UserID,
                         StudentNo = g.Key.StudentNo,
                         Name = g.Key.Name,
                         Sex = g.Key.SexID,
                         ClassmajorID = g.Key.ClassmajorID,
                         ClassmajorNo = g.Key.ClassmajorNo,
                         ClassmajorName = g.Key.ClassmajorName,
                         CollegeID = g.Key.CollegeID,
                         CollegeName = g.Key.CollegeName,
                         SubjectName = g.Key.SubjectName,
                         CertificatesType = g.Key.CertificatesType,
                         IDNumber = g.Key.IDNumber,
                         EducationID = g.Key.EducationID,
                         LearnSystem = g.Key.LearnSystem,
                         StandardID = g.Key.StandardID,
                         Year = g.Key.GradeID,
                         SchoolyearNumID = (int?)Math.Ceiling((decimal)(g.Key.EndSchoolyearValue - g.Key.StartSchoolyearValue + 1) / 2),
                         PhotoUrl = g.Key.PhotoUrl,
                         RegistedCount = g.Count(),
                         SchoolyearCode = g.Key.SchoolyearCode,
                         Cost = g.Key.Cost,
                         IsPay = g.Key.IsPay == true ? "是" : "否",
                         SchoolyearID = g.Key.SchoolyearIDGuid,
                         ExaminationSubjectID = g.Key.ExaminationSubjectID,
                     }
                );

            return q;
        }
        public IQueryable<StudentListView> GetRegistedStudentListView(Expression<Func<EX_ExaminationSubject, bool>> exp,
            Expression<Func<CF_Student, bool>> studentExp,
            Expression<Func<EX_ExaminationRegistration, bool>> exr)
        {
            var q = (from es in ExaminationSubjectRepository.GetList(exp)
                     join er in ExaminationRegistrationRepository.GetList(exr) on es.ExaminationSubjectID equals er.ExaminationSubjectID
                     join year in SchoolyearRepository.Entities on er.ExaminationSchoolyearID equals year.SchoolyearID//换成考试学年学期
                     join s in StudentRepository.GetList(studentExp) on er.UserID equals s.UserID
                     group s by new
                     {
                         er.ExaminationRegistrationID,
                         s.UserID,
                         StudentNo = s.Sys_User.LoginID,
                         Name = s.Sys_User.Name,
                         s.SexID,
                         s.ClassmajorID,
                         ClassmajorNo = s.CF_Classmajor.No,
                         ClassmajorName = s.CF_Classmajor.Name,
                         s.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
                         CollegeName = s.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CF_College.Name,
                         SubjectName = es.Name,
                         er.CertificatesType,
                         er.IDNumber,
                         SchoolyearIDGuid = er.SchoolyearID,
                         SchoolyearCode = year.Code,
                         s.CF_Classmajor.CF_Grademajor.CF_Facultymajor.EducationID,
                         s.CF_Classmajor.CF_Grademajor.CF_Facultymajor.LearnSystem,
                         s.CF_Classmajor.CF_Grademajor.CF_Facultymajor.StandardID,
                         s.CF_Classmajor.CF_Grademajor.GradeID,
                         StartSchoolyearValue = (s.CF_Classmajor.CF_Grademajor.GradeID.Value * 2) - 1 + (s.CF_Classmajor.CF_Grademajor.SemesterID.Value - 1),
                         EndSchoolyearValue = es.CF_Schoolyear.Value,
                         PhotoUrl = s.PhotoUrl,
                         CardNo = s.CF_StudentAccount.CardNo,
                         Cost = es.Cost,
                         IsPay = er.IsPay,
                         ExaminationSchoolyearID = year.SchoolyearID,
                         ExaminationSubjectID = es.ExaminationSubjectID,
                     } into g
                     select new StudentListView
                     {
                         ExaminationRegistrationID=g.Key.ExaminationRegistrationID,
                         CardNo = g.Key.CardNo,
                         UserID = g.Key.UserID,
                         StudentNo = g.Key.StudentNo,
                         Name = g.Key.Name,
                         Sex = g.Key.SexID,
                         ClassmajorID = g.Key.ClassmajorID,
                         ClassmajorNo = g.Key.ClassmajorNo,
                         ClassmajorName = g.Key.ClassmajorName,
                         CollegeID = g.Key.CollegeID,
                         CollegeName = g.Key.CollegeName,
                         SubjectName = g.Key.SubjectName,
                         CertificatesType = g.Key.CertificatesType,
                         IDNumber = g.Key.IDNumber,
                         EducationID = g.Key.EducationID,
                         LearnSystem = g.Key.LearnSystem,
                         StandardID = g.Key.StandardID,
                         Year = g.Key.GradeID,
                         SchoolyearNumID = (int?)Math.Ceiling((decimal)(g.Key.EndSchoolyearValue - g.Key.StartSchoolyearValue + 1) / 2),
                         PhotoUrl = g.Key.PhotoUrl,
                         RegistedCount = g.Count(),
                         SchoolyearCode = g.Key.SchoolyearCode,
                         Cost = g.Key.Cost,
                         IsPay = g.Key.IsPay == true ? "是":"否",
                         SchoolyearID = g.Key.SchoolyearIDGuid,
                         ExaminationSchoolyearID = g.Key.ExaminationSchoolyearID,
                         ExaminationSubjectID = g.Key.ExaminationSubjectID,
                     }
                );

            return q;
        }

        public IQueryable<ExaminationClassStudentStatisticsView> GetRegistedClassStudentStatisticsListView(Expression<Func<EX_ExaminationRegistration, bool>> exp,
            Expression<Func<CF_Student, bool>> studentExp)
        {
            //var q = (from es in ExaminationSubjectRepository.GetList(exp)
            //         from er in es.EX_ExaminationRegistration.GroupBy(x => new { x.ExaminationSubjectID,x.UserID}).Select(x=>x.FirstOrDefault())
            //         join s in StudentRepository.GetList(studentExp) on er.UserID equals s.UserID
            //         group s by new
            //         {
            //             SubjectID = es.ExaminationSubjectID,
            //             SubjectName = es.Name,
            //             s.ClassmajorID,
            //             ClassmajorName = s.CF_Classmajor.Name,
            //             SchoolyearCode = es.CF_Schoolyear.Code,
            //             s.CF_Classmajor.CF_Grademajor.GrademajorID,
            //             GrademajorName = s.CF_Classmajor.CF_Grademajor.Name,
            //             s.CF_Classmajor.CF_Grademajor.SchoolyearID,
            //             es.Cost,

            //         } into g
            //         select new ExaminationClassStudentStatisticsView
            //         {
            //             SubjectID=g.Key.SubjectID,
            //             SubjectName = g.Key.SubjectName,
            //             ClassmajorID = g.Key.ClassmajorID,
            //             ClassmajorName = g.Key.ClassmajorName,
            //             GrademajorName = g.Key.GrademajorName,
            //             Year = g.Key.SchoolyearID,
            //             StudentCount = g.Count(),
            //             SchoolyearCode = g.Key.SchoolyearCode,
            //             Cost = g.Key.Cost,
            //         }
            //    );
            var query = from er in ExaminationRegistrationRepository.GetList(exp)
                        join ss in SchoolyearRepository.Entities
                        on er.SchoolyearID equals ss.SchoolyearID
                        join esch in SchoolyearRepository.Entities
                        on er.ExaminationSchoolyearID equals esch.SchoolyearID
                        join stu in studentRepository.GetList(studentExp)
                         on er.UserID equals stu.UserID
                         join cla in classmajorRepository.Entities
                         on stu.ClassmajorID equals cla.ClassmajorID
                         join gra in grademajorRepository.Entities
                         on cla.GrademajorID equals gra.GrademajorID
                         group er by new
                         {
                             SchoolyearID = ss.SchoolyearID,
                             SchoolyearCode = ss.Code,
                             ExaminationSchoolyearID = esch.SchoolyearID,
                             ExaminationSchoolyearCode = esch.Code,
                             stu.ClassmajorID,
                             ClassName = cla.Name,
                             cla.GrademajorID,
                             GrademajorName = gra.Name,
                             er.ExaminationSubjectID,
                             SubjectName = er.EX_ExaminationSubject.Name,
                             Year = gra.GradeID,
                             er.EX_ExaminationSubject.Cost,
                         } into g
                         select new ExaminationClassStudentStatisticsView
                         {
                             SubjectID = g.Key.ExaminationSubjectID,
                             SubjectName = g.Key.SubjectName,
                             ClassmajorID = g.Key.ClassmajorID,
                             ClassmajorName = g.Key.ClassName,
                             GrademajorID = g.Key.GrademajorID,
                             GrademajorName = g.Key.GrademajorName,
                             SchoolyearID = g.Key.SchoolyearID,
                             Year = g.Key.Year,
                             SchoolyearCode = g.Key.SchoolyearCode,
                             ExaminationSchoolyearID = g.Key.ExaminationSchoolyearID,
                             ExaminationSchoolyearCode = g.Key.ExaminationSchoolyearCode,
                             Cost = g.Key.Cost,
                             StudentCount = g.Count(),
                         };


            return query;
        }

        //public IQueryable<ExaminationClassStudentStatisticsView> GetRegistedClassStudentStatisticsListView(Expression<Func<EX_ExaminationSubject, bool>> exp,
        //    Expression<Func<CF_Student, bool>> studentExp)
        //{
        //    var q = (from es in ExaminationSubjectRepository.GetList(exp)
        //             from er in es.EX_ExaminationRegistration
        //             join s in StudentRepository.GetList(studentExp) on er.UserID equals s.UserID
        //             group s by new
        //             {
        //                 SubjectID=es.ExaminationSubjectID,
        //                 SubjectName = es.Name,
        //                 s.ClassmajorID,
        //                 ClassmajorName = s.CF_Classmajor.Name,     
        //                 SchoolyearCode = es.CF_Schoolyear.Code,
        //                 s.CF_Classmajor.CF_Grademajor.GrademajorID,
        //                 GrademajorName=s.CF_Classmajor.CF_Grademajor.Name,
        //                 s.CF_Classmajor.CF_Grademajor.SchoolyearID,
        //                 es.Cost,
                         
        //             } into g
        //             select new ExaminationClassStudentStatisticsView
        //             {

        //                 SubjectName = g.Key.SubjectName,
        //                 ClassmajorID = g.Key.ClassmajorID,
        //                 ClassmajorName = g.Key.ClassmajorName,
        //                 GrademajorName=g.Key.GrademajorName,
        //                 Year = g.Key.SchoolyearID,
        //                 StudentCount = g.Count(),
        //                 SchoolyearCode = g.Key.SchoolyearCode,
        //                 Cost=g.Key.Cost,
        //             }
        //        );

        //    return q;
        //}

        public IQueryable<EX_ExaminationRegistration> GetExaminationRegistrationByID(Expression<Func<EX_ExaminationRegistration,bool>> exp)
        {
            var query = from ex in ExaminationRegistrationRepository.GetList(exp)
                        select new EX_ExaminationRegistration
                        {
                            ExaminationRegistrationID = ex.ExaminationRegistrationID,
                            ExaminationSubjectID = ex.ExaminationSubjectID,
                            SchoolyearID = ex.SchoolyearID,
                            UserID = ex.UserID,
                            CertificatesType = ex.CertificatesType,
                            IDNumber = ex.IDNumber,
                            IsPay = ex.IsPay
                        };
            return query;
        }
    }
}