JW/EMiS.DataLogic/SupervisionManage/ProjectRecordDAL.cs

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

namespace EMIS.DataLogic.SupervisionManage
{
    public class ProjectRecordDAL
    {
        public ProjectRecordRepository ProjectRecordRepository { get; set; }
        public SupervisionCollegeRepository SupervisionCollegeRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public CoursesTimeRepository CoursesTimeRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public StaffRepository StaffRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }

        public IQueryable<ProjectRecordView> GetProjectRecordViewQueryable(Expression<Func<SUP_ProjectRecord, bool>> exp, Expression<Func<CF_Staff, bool>> supervisorExp, Guid? UserID, Guid? CollegeID)
        {
            if (UserID.HasValue)
            {
                if (CollegeID.HasValue)
                {
                    var sql = (from record in ProjectRecordRepository.GetList(exp)
                               join schoolyear in SchoolyearRepository.Entities
                               on record.SchoolyearID equals schoolyear.SchoolyearID
                               join supcollege in SupervisionCollegeRepository.Entities
                               on record.SupervisionCollegeID equals supcollege.SupervisionCollegeID
                               join time in CoursesTimeRepository.Entities
                               on record.CoursesTimeID equals time.CoursesTimeID
                               join creator in UserRepository.Entities
                               on record.CreateUserID equals creator.UserID
                               from cresta in StaffRepository.Entities.Where(x => x.UserID == creator.UserID).DefaultIfEmpty().Where(supervisorExp)
                               join staff in StaffRepository.Entities
                               on record.UserID equals staff.UserID
                               into dsta
                               from sta in dsta.DefaultIfEmpty()
                               where record.SUP_ProjectRecord_CF_Staff.Where(w => w.UserID == UserID).Select(x => x.UserID).ToList().Contains((Guid)UserID)
                                     || record.SUP_ProjectRecord_CF_Staff.Where(w => w.CollegeID == CollegeID).Select(x => x.CollegeID).ToList().Contains((Guid)CollegeID)
                               select new ProjectRecordView
                               {
                                   ProjectRecordID = record.ProjectRecordID,
                                   ProjectDate = record.ProjectDate,
                                   Location = record.Location,
                                   SchoolyearID = schoolyear.SchoolyearID,
                                   SchoolyearCode = schoolyear.Code,
                                   CollegeID = cresta.CollegeID,
                                   CoursematerialID = record.CoursematerialID,
                                   TotalScore = record.TotalScore,
                                   CollegeName = cresta.CF_College.Name,
                                   SupervisionCollegeID = record.SupervisionCollegeID,
                                   SupervisionCollegeName = supcollege.Name,
                                   SupervisionTypeID = record.SupervisionTypeID,
                                   Weekday = record.Weekday,
                                   OtherTarget = record.OtherTarget,
                                   UserID = record.UserID,
                                   UserName = sta.Sys_User.Name,
                                   CoursesTimeID = record.CoursesTimeID,
                                   CreateTime = record.CreateTime,
                                   StartTimes = time.StartTimes,
                                   EndTimes = time.EndTimes,
                                   Content = record.Content,
                                   Advise = record.Advise,
                                   CreateUserID = record.CreateUserID,
                                   CreateUserName = creator.Name,
                                   userList = record.SUP_ProjectRecord_CF_Staff.Select(x => x.Sys_User),
                                   ProjectRecordAttachmentList = record.SUP_ProjectRecordAttachment,
                               });
                    return sql;
                }
                else 
                {
                    var sql = (from record in ProjectRecordRepository.GetList(exp)
                               join schoolyear in SchoolyearRepository.Entities
                               on record.SchoolyearID equals schoolyear.SchoolyearID
                               join supcollege in SupervisionCollegeRepository.Entities
                               on record.SupervisionCollegeID equals supcollege.SupervisionCollegeID
                               join time in CoursesTimeRepository.Entities
                               on record.CoursesTimeID equals time.CoursesTimeID
                               join creator in UserRepository.Entities
                               on record.CreateUserID equals creator.UserID
                               from cresta in StaffRepository.Entities.Where(x => x.UserID == creator.UserID).DefaultIfEmpty().Where(supervisorExp)
                               join staff in StaffRepository.Entities
                               on record.UserID equals staff.UserID
                               into dsta
                               from sta in dsta.DefaultIfEmpty()
                               where record.SUP_ProjectRecord_CF_Staff.Where(w => w.UserID == UserID).Select(x => x.UserID).ToList().Contains((Guid)UserID)
                               select new ProjectRecordView
                               {
                                   ProjectRecordID = record.ProjectRecordID,
                                   ProjectDate = record.ProjectDate,
                                   Location = record.Location,
                                   SchoolyearID = schoolyear.SchoolyearID,
                                   SchoolyearCode = schoolyear.Code,
                                   CollegeID = cresta.CollegeID,
                                   CoursematerialID = record.CoursematerialID,
                                   TotalScore = record.TotalScore,
                                   CollegeName = cresta.CF_College.Name,
                                   SupervisionCollegeID = record.SupervisionCollegeID,
                                   SupervisionCollegeName = supcollege.Name,
                                   SupervisionTypeID = record.SupervisionTypeID,
                                   Weekday = record.Weekday,
                                   OtherTarget = record.OtherTarget,
                                   UserID = record.UserID,
                                   UserName = sta.Sys_User.Name,
                                   CoursesTimeID = record.CoursesTimeID,
                                   CreateTime = record.CreateTime,
                                   StartTimes = time.StartTimes,
                                   EndTimes = time.EndTimes,
                                   Content = record.Content,
                                   Advise = record.Advise,
                                   CreateUserID = record.CreateUserID,
                                   CreateUserName = creator.Name,
                                   userList = record.SUP_ProjectRecord_CF_Staff.Select(x => x.Sys_User),
                                   ProjectRecordAttachmentList = record.SUP_ProjectRecordAttachment,
                               });
                    return sql;
                }
            }
            else 
            {
                var sql = (from record in ProjectRecordRepository.GetList(exp)
                           join schoolyear in SchoolyearRepository.Entities
                           on record.SchoolyearID equals schoolyear.SchoolyearID
                           join supcollege in SupervisionCollegeRepository.Entities
                           on record.SupervisionCollegeID equals supcollege.SupervisionCollegeID
                           join time in CoursesTimeRepository.Entities
                           on record.CoursesTimeID equals time.CoursesTimeID
                           join creator in UserRepository.Entities
                           on record.CreateUserID equals creator.UserID
                           from cresta in StaffRepository.Entities.Where(x => x.UserID == creator.UserID).DefaultIfEmpty().Where(supervisorExp)
                           join staff in StaffRepository.Entities
                           on record.UserID equals staff.UserID
                           into dsta
                           from sta in dsta.DefaultIfEmpty()
                           select new ProjectRecordView
                           {
                               ProjectRecordID = record.ProjectRecordID,
                               ProjectDate = record.ProjectDate,
                               Location = record.Location,
                               SchoolyearID = schoolyear.SchoolyearID,
                               SchoolyearCode = schoolyear.Code,
                               CollegeID = creator.CF_Staff.CollegeID,
                               CoursematerialID = record.CoursematerialID,
                               TotalScore = record.TotalScore,
                               CollegeName = cresta.CF_College.Name,
                               SupervisionCollegeID = record.SupervisionCollegeID,
                               SupervisionCollegeName = supcollege.Name,
                               SupervisionTypeID = record.SupervisionTypeID,
                               Weekday = record.Weekday,
                               OtherTarget = record.OtherTarget,
                               UserID = record.UserID,
                               UserName = sta.Sys_User.Name,
                               CoursesTimeID = record.CoursesTimeID,
                               CreateTime = record.CreateTime,
                               StartTimes = time.StartTimes,
                               EndTimes = time.EndTimes,
                               Content = record.Content,
                               Advise = record.Advise,
                               CreateUserID = record.CreateUserID,
                               CreateUserName = creator.Name,
                               userList = record.SUP_ProjectRecord_CF_Staff.Select(x => x.Sys_User),
                               ProjectRecordAttachmentList = record.SUP_ProjectRecordAttachment,
                           });

                return sql;
            }
        }

        public IQueryable<ProjectRecordTeacherView> GetDefaultTeacherView(Guid? userID)
        {
            var query = from sta in StaffRepository.GetList(x => x.UserID == userID)
                        select new ProjectRecordTeacherView
                        {
                            //ProjectRecordID = pro.ProjectRecordID,
                            //TeacherID = staff.UserID,
                            UserID = sta.UserID,
                            LoginID = sta.Sys_User.LoginID,
                            Name = sta.Sys_User.Name,
                            CollegeID = sta.CF_College.CollegeID,
                            CollegeName = sta.CF_College.Name,
                            Sex = sta.SexID,
                            DepartmentID = sta.DepartmentID,
                            DepartmentName = sta.CF_Department.Name,
                            BirthDate = sta.BirthDate,
                            TeacherType = sta.TeacherTypeID,
                            IncumbencyState = sta.IncumbencyState,
                            Title = sta.TitleID,
                        };
            return query;
        }

        public IQueryable<ProjectRecordTeacherView> GetProjectRecordTeacherView(Guid? projectRecordID)
        {
            var query = from pro in ProjectRecordRepository.GetList(x => x.ProjectRecordID == projectRecordID)
                        from staff in pro.SUP_ProjectRecord_CF_Staff
                        join sta in StaffRepository.Entities
                        on staff.UserID equals sta.UserID
                        select new ProjectRecordTeacherView
                        {
                            ProjectRecordID = pro.ProjectRecordID,
                            TeacherID = staff.UserID,
                            UserID = sta.UserID,
                            LoginID = sta.Sys_User.LoginID,
                            Name = sta.Sys_User.Name,
                            CollegeID = sta.CF_College.CollegeID,
                            CollegeName = sta.CF_College.Name,
                            Sex = sta.SexID,
                            DepartmentID = sta.DepartmentID,
                            DepartmentName = sta.CF_Department.Name,
                            BirthDate = sta.BirthDate,
                            TeacherType = sta.TeacherTypeID,
                            IncumbencyState = sta.IncumbencyState,
                            Title = sta.TitleID,
                        };
            return query;
        }

        public IOrderedQueryable<FileUploadView> GetProjectRecordAttachmentView(Expression<Func<SUP_ProjectRecord, bool>> exp)
        {
            var tableName = typeof(SUP_ProjectRecordAttachment).Name;
            var sql = (from @group in ProjectRecordRepository.GetList(exp)
                       from attachment in @group.SUP_ProjectRecordAttachment
                       orderby attachment.CreateTime descending
                       select new FileUploadView
                       {
                           FileID = attachment.ProjectRecordAttachmentID,
                           FileName = attachment.Name,
                           FileUrl = attachment.Url,
                           FormID = attachment.ProjectRecordID,
                           TableName = tableName
                       });

            return (IOrderedQueryable<FileUploadView>)sql;
        }
    }
}