JW/EMiS.DataLogic/DQPSystem/SOCDAL_heyw.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.ViewModel.Cultureplan;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.DataLogic.Common.Cultureplan;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.DQPSystem;
using EMIS.ViewModel;

namespace EMIS.DataLogic.DQPSystem
{
    public partial class SOCDAL
    {
        public Lazy<CoursematerialDAL> CoursematerialDAL { get; set; }
        public StaffRepository StaffRepository { get; set; }
        public SOCDetailStudentRepository SOCDetailStudentRepository { get; set; }
        public SOCDetailGroupRepository SOCDetailGroupRepository { get; set; }

        public IQueryable<CoursematerialView> GetStaffRelateCoursematerialViewQueryable(Expression<Func<DQP_SOC, bool>> exp, 
            Expression<Func<CF_Staff, bool>> staffExp)
        {
            var sql = (from course in CoursematerialDAL.Value.GetCoursematerialViewQueryable(x => true)
                       from soc in SOCRepository.Entities.Where(exp).Where(x => x.CoursematerialID == course.CoursematerialID) 
                       from teacher in soc.DQP_SOCStaff
                       from staff in StaffRepository.Entities.Where(staffExp).Where(x => x.UserID == teacher.UserID)
                       group course by course into g
                       select g.Key);

            return sql;
        }

        public IQueryable<CoursematerialView> GetStudentRelateCoursematerialViewQueryable(Expression<Func<DQP_SOC, bool>> exp,
            Expression<Func<CF_Student, bool>> studentExp)
        {
            var sql = (from course in CoursematerialDAL.Value.GetCoursematerialViewQueryable(x => true)
                       from soc in SOCRepository.Entities.Where(exp).Where(x => x.CoursematerialID == course.CoursematerialID)
                       from student in soc.CF_Student.AsQueryable().Where(studentExp)
                       group course by course into g
                       select g.Key);

            return sql;
        }

        public IQueryable<SOCStudentView> GetSOCStudentViewQueryable(Expression<Func<DQP_SOC, bool>> exp, Expression<Func<CF_Student, bool>> studentExp)
        {
            var sql = (from soc in SOCRepository.GetList(exp)
                       from schoolyear in schoolyearRepository.Entities.Where(x => x.SchoolyearID == soc.SchoolyearID)
                       from course in coursematerialRepository.Entities.Where(x => x.CoursematerialID == soc.CoursematerialID)
                       from student in soc.CF_Student.AsQueryable().Where(studentExp)
                       from detail in SOCDetailRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE && soc.SOCID == x.SOCID && x.IsGroup == true)
                       from groupDetail in SOCDetailGroupRepository.Entities.Where(x => x.SOCDetailID == detail.SOCDetailID) 
                       from groupStudent in groupDetail.CF_Student.AsQueryable().Where(x => x.UserID == student.UserID)
                       select new SOCStudentView
                       {
                           SOCDetailID = detail.SOCDetailID,
                           SOCID = soc.SOCID,
                           SOCDetailGroupID = groupDetail.SOCDetailGroupID,
                           SchoolyearID = soc.SchoolyearID,
                           SchoolyearCode = schoolyear.Code,
                           CoursematerialID = soc.CoursematerialID,
                           CourseCode = course.CourseCode,
                           CourseName = course.CourseName,
                           Name = detail.Name,
                           Credit = detail.Credit,
                           Weight = detail.Weight,
                           StartTime = detail.StartTime,
                           EndTime = detail.EndTime,
                           Remark = groupDetail.Remark,
                           RecordStatus = (groupDetail.RecordStatus ?? (int)DQP_SOCDetailSubmitStatus.NotSubmit)
                       })
                       .Concat(
                       from soc in SOCRepository.GetList(exp) 
                       from schoolyear in schoolyearRepository.Entities.Where(x => x.SchoolyearID == soc.SchoolyearID) 
                       from course in coursematerialRepository.Entities.Where(x => x.CoursematerialID == soc.CoursematerialID) 
                       from student in soc.CF_Student.AsQueryable().Where(studentExp)
                       from detail in SOCDetailRepository.Entities.Where(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE && soc.SOCID == x.SOCID && x.IsGroup != true)
                       from studentDetail in SOCDetailStudentRepository.Entities.Where(x => x.SOCDetailID == detail.SOCDetailID && x.UserID == student.UserID).DefaultIfEmpty()
                       select new SOCStudentView
                       {
                           SOCDetailID = detail.SOCDetailID,
                           SOCID = soc.SOCID,
                           SOCDetailGroupID = null,
                           SchoolyearID = soc.SchoolyearID,
                           SchoolyearCode = schoolyear.Code,
                           CoursematerialID = soc.CoursematerialID,
                           CourseCode = course.CourseCode,
                           CourseName = course.CourseName,
                           Name = detail.Name,
                           Credit = detail.Credit,
                           Weight = detail.Weight,
                           StartTime = detail.StartTime,
                           EndTime = detail.EndTime,
                           Remark = studentDetail.Remark,
                           RecordStatus = (studentDetail.RecordStatus ?? (int)DQP_SOCDetailSubmitStatus.NotSubmit)
                       });

            return sql;
        }
    }
}