LIOT/EMiS.DataLogic/CerificateManage/CertisfierDistributeDAL.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;
using EMIS.ViewModel.CerificateManage;

namespace EMIS.DataLogic.CerificateManage
{
    public class CertisfierDistributeDAL
    {
        public CertisfierDistributeRepository DistributeRepository { get; set; }
        public ProjectScoreRepository ProjectScoreRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public ExaminationProjectRepository ExaminationProjectRepository { get; set; }
        public ExaminationProjectSubjectRepository ExaminationProjectSubjectRepository { get; set; }
        public ExaminationBatchProjectRepository ExaminationBatchProjectRepository { get; set; }
        public ExaminationScoreRepository ExaminationScoreRepository { get; set; }
        public LevelSettingRepository LevelSettingRepository { get; set; }
        public ExaminationTypeRepository TypeRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }

        //public IQueryable<EMIS.ViewModel.CerificateView> GetList(Expression<Func<ER_CertisfierDistribute, bool>> expDistribute, Expression<Func<CF_College, bool>> expcol, 
        //    Expression<Func<CF_Grademajor, bool>> expgra, Expression<Func<CF_Classmajor, bool>> expcla, Expression<Func<CF_Facultymajor, bool>> expfac)
        //{
        //    var query = from a in ProjectScoreRepository.Entities
        //                join b in DistributeRepository.GetList(expDistribute) on new { a.ExaminationProjectID, a.UserID } equals new { b.ExaminationProjectID, b.UserID } into bt
        //                from br in bt.DefaultIfEmpty()
        //                join dc in DictionaryItemRepository.Entities
        //                on new { br.RecordStatus, DictionaryCode = typeof(ER_CertisfierDistributeStatus).Name } equals new { RecordStatus = dc.Value, dc.DictionaryCode } into dct
        //                from dcr in dct.DefaultIfEmpty()
        //                join c in StudentRepository.Entities on a.UserID equals c.UserID
        //                join cl in ClassmajorRepository.Entities.Where(expcla) on c.ClassmajorID equals cl.ClassmajorID 
        //                join gra in GrademajorRepository.Entities.Where(expgra) on cl.GrademajorID equals gra.GrademajorID 
        //                join fac in FacultymajorRepository.Entities.Where(expfac) on gra.FacultymajorID equals fac.FacultymajorID 
        //                join col in CollegeRepository.Entities.Where(expcol) on fac.CollegeID equals col.CollegeID
        //                where a.ER_LevelSetting.IsPassed == true
        //                select new EMIS.ViewModel.CerificateView
        //                {
        //                    CertisfierDistributeID = br.CertisfierDistributeID,
        //                    ProjectScoreID = a.ProjectScoreID,
        //                    Schoolyear = a.CF_Schoolyear.Code,
        //                    SchoolYearCode = a.CF_Schoolyear.SchoolyearID,
        //                    ClassName = cl.Name,
        //                    UserID = a.UserID,
        //                    ProjectName = a.EX_ExaminationProject.Name,
        //                    ExaminationProjectID = a.ExaminationProjectID,
        //                    CollegeID = c.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CollegeID,
        //                    UserName = c.Sys_User.Name,
        //                    CertificatesType = c.CertificatesType,
        //                    Sex = c.Sex,
        //                    IDNumber = c.IDNumber,
        //                    ClassmajorName = c.CF_Classmajor.Name,
        //                    ClassmajorID = c.ClassmajorID,
        //                    GrademajorName = c.CF_Classmajor.CF_Grademajor.Name,
        //                    CollegeName = c.CF_Classmajor.CF_Grademajor.CF_Facultymajor.CF_College.Name,
        //                    Years = c.CF_Classmajor.CF_Grademajor.SchoolyearID.ToString(),
        //                    StandardID = c.CF_Classmajor.CF_Grademajor.CF_Facultymajor.StandardID,
        //                    Address = br.Address,
        //                    ContactIDNo = br.ContactIDNo,
        //                    ContactNo = br.ContactNo,
        //                    MailContactNo = br.ContactNo,
        //                    //Distributer = br.ContactPerson,
        //                    EntrustDistributer = br.ContactPerson,
        //                    ContactPerson = br.ContactPerson,
        //                    DeliveryNoteNo = br.DeliveryNoteNo,
        //                    Distributer = br.Distributer,
        //                    DistributeTime = br.DistributeTime,
        //                    DistributeTypeID = br.DistributeTypeID,
        //                    No = br.No,
        //                    RecordStatus = br.RecordStatus,
        //                    DistributeStateName = dcr == null ? "制证中" : dcr.Name,
        //                    SchoolAreaID = c.CF_Classmajor.CF_Grademajor.SchoolAreaID,
        //                    SaveNo = br.SaveNo,
        //                };

        //    return query;
        //}

        public IQueryable<EMIS.ViewModel.CerificateView> GetCerificateViewListOnlyGenerated(Expression<Func<ER_CertisfierDistribute, bool>> expDistribute, Expression<Func<CF_College, bool>> expcol,
            Expression<Func<CF_Grademajor, bool>> expgra, Expression<Func<CF_Classmajor, bool>> expcla, Expression<Func<CF_Facultymajor, bool>> expfac)
        {
            var query = from b in DistributeRepository.GetList(expDistribute)
                        from pro in ExaminationProjectRepository.Entities.Where(x => x.ExaminationProjectID == b.ExaminationProjectID)
                        from c in StudentRepository.Entities.Where(x => x.UserID == b.UserID)
                        from cla in ClassmajorRepository.Entities.Where(expcla).Where(x => x.ClassmajorID == c.ClassmajorID)
                        from gra in GrademajorRepository.Entities.Where(expgra).Where(x => x.GrademajorID == cla.GrademajorID)
                        from fac in FacultymajorRepository.Entities.Where(expfac).Where(x => x.FacultymajorID == gra.FacultymajorID)
                        from col in CollegeRepository.Entities.Where(expcol).Where(x => x.CollegeID == fac.CollegeID)
                        select new EMIS.ViewModel.CerificateView
                        {
                            CertisfierDistributeID = b.CertisfierDistributeID,
                            //ProjectScoreID = a.ProjectScoreID,
                            Schoolyear = b.CF_Schoolyear.Code,
                            SchoolYearCode = b.CF_Schoolyear.SchoolyearID,
                            ClassName = cla.Name,
                            UserID = b.UserID,
                            ProjectName = pro.Name,
                            ExaminationProjectID = b.ExaminationProjectID,
                            CollegeID = col.CollegeID,
                            UserName = c.Sys_User.Name,
                            CertificatesType = c.CertificatesType,
                            Sex = c.Sex,
                            IDNumber = c.IDNumber,
                            ClassmajorName = c.CF_Classmajor.Name,
                            ClassmajorID = c.ClassmajorID,
                            GrademajorName = gra.Name,
                            CollegeName = col.Name,
                            Years = gra.SchoolyearID.ToString(),
                            StandardID = fac.StandardID,
                            Address = b.Address,
                            ContactIDNo = b.ContactIDNo,
                            ContactNo = b.ContactNo,
                            MailContactNo = b.ContactNo,
                            //Distributer = br.ContactPerson,
                            EntrustDistributer = b.ContactPerson,
                            ContactPerson = b.ContactPerson,
                            DeliveryNoteNo = b.DeliveryNoteNo,
                            Distributer = b.Distributer,
                            DistributeTime = b.DistributeTime,
                            DistributeTypeID = b.DistributeTypeID,
                            No = b.No,
                            RecordStatus = b.RecordStatus,
                            SchoolAreaID = gra.SchoolAreaID,
                            SaveNo = b.SaveNo,
                        };

            return query;
        }

        public IQueryable<CertificateMobileView> GetCertificateMobileViewQueryable(Expression<Func<ER_ProjectScore, bool>> exp)
        {
            var sql = (
                from projectScore in ProjectScoreRepository.GetList(exp) 
                from project in ExaminationProjectRepository.Entities.Where(x => x.ExaminationProjectID == projectScore.ExaminationProjectID) 
                from level in LevelSettingRepository.Entities.Where(x => x.LevelSettingID == projectScore.LevelSettingID) 
                from distribute in DistributeRepository.Entities.Where(x => x.ExaminationProjectID == project.ExaminationProjectID && x.UserID == projectScore.UserID)
                    .DefaultIfEmpty() 
                select new CertificateMobileView
                {
                    ProjectScoreID = projectScore.ProjectScoreID,
                    ExaminationProjectID = projectScore.ExaminationProjectID,
                    ExaminationProjectName = project.Name,
                    LevelSettingID = projectScore.LevelSettingID,
                    LevelName = level.LevelName,
                    IsPassed = level.IsPassed,
                    CertisfierDistributeID = distribute.CertisfierDistributeID,
                    DistributeTypeID = distribute.DistributeTypeID,
                    DistributeStatusID = distribute.RecordStatus,
                    ContactPerson = distribute.ContactPerson,
                    ContactNo = distribute.ContactNo,
                    ContactIDNo = distribute.ContactIDNo,
                    Address = distribute.Address,
                    DeliveryNoteNo = distribute.DeliveryNoteNo
                }
                );

            return sql;
        }

        public IQueryable<CertificateMobileScoreView> GetCertificateMobileScoreViewQueryable(Expression<Func<ER_ProjectScore, bool>> exp)
        {
            var curDate = DateTime.Today;
            var sql = (
                from projectScore in ProjectScoreRepository.GetList(exp) 
                from subject in ExaminationProjectSubjectRepository.Entities.Where(x => x.ExaminationProjectID == projectScore.ExaminationProjectID) 
                from score in (
                    from batchProject in ExaminationBatchProjectRepository.Entities.Where(x => x.ExaminationProjectID == projectScore.ExaminationProjectID)  
                    from score in ExaminationScoreRepository.Entities.Where(x => x.ExaminationBatchProjectID == batchProject.ExaminationBatchProjectID 
                        && x.UserID == projectScore.UserID && x.ExaminationSubjectID == subject.ExaminationSubjectID
                        && x.RecordStatus > (int)SYS_STATUS.UNUSABLE)
                    where !score.Expire.HasValue || score.Expire >= curDate
                    orderby score.Score descending
                    select new { batchProject.ExaminationProjectID, score.ExaminationScoreID, score.ExaminationSubjectID, score.Score }
                    ).Take(1) 
                select new CertificateMobileScoreView
                {
                    ExaminationScoreID = score.ExaminationScoreID,
                    ExaminationProjectID = score.ExaminationProjectID,
                    ExaminationSubjectID = score.ExaminationSubjectID,
                    Score = score.Score
                }
                );

            return sql;
        }

        public IQueryable<ER_CertisfierDistribute> GetCertisfierDistributByProjectScore(Expression<Func<ER_ProjectScore, bool>> exp)
        {
            var sql = (from score in ProjectScoreRepository.GetList(exp) 
                       from distribute in DistributeRepository.Entities.Where(x => x.ExaminationProjectID == score.ExaminationProjectID && x.UserID == score.UserID) 
                       select distribute);

            return sql;
        }
    }
}