JW/EMiS.DataLogic/Common/TeachingMaterial/StudentsOrderDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.TeachingMaterial;
using System.Linq.Expressions;
using EMIS.Entities;
using EMIS.ViewModel;
using EMIS.DataLogic.CultureplanManage.PlanManagement;
using Bowin.Common.Linq;
using EMIS.DataLogic.SystemDAL;
using EMIS.Utility;
using EMIS.DataLogic.StudentManage.StudentStatistics;

namespace EMIS.DataLogic.Common.TeachingMaterial
{
    public class StudentsOrderDAL
    {
        public StudentsOrderRepository StudentsOrderRepository { get; set; }
        public TeachingMaterialPoolRepository TeachingMaterialPoolRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public SpecialtyPlanRepository SpecialtyPlanRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public SpecialtyCourseRepository SpecialtyCourseRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public PublishRepository PublishRepository { get; set; }
        public StaffRepository StaffRepository { get; set; }
        public DataRangeDAL DataRangeDAL { get; set; }
        public InSchoolSettingDAL InSchoolSettingDAL { get; set; }

        /// <summary>
        /// 获取学生征订信息列表
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<StudentsOrderView> GetStudentsOrderGridView(Expression<Func<ET_StudentsOrder, bool>> exporder)
        {
            var inSchoolStatusIDList = InSchoolSettingDAL.InSchoolSettingRepository.GetList(x => x.IsSelected == true).Select(w => w.InSchoolStatusID).ToList();//获取选中的在校设定状态、

            var view = (from sp in SpecialtyPlanRepository.GetList(x => true, (x => x.ET_StudentsOrder.Select(w => w.CF_TeachingMaterialPool)))
                        join so in StudentsOrderRepository.GetList(exporder) on sp.SpecialtyPlanID equals so.SpecialtyPlanID
                        join u in UserRepository.Entities on so.CreateUserID equals u.UserID
                        join fdi in DictionaryItemRepository.Entities on new { sp.CF_Grademajor.CF_Facultymajor.StandardID, DictionaryCode = DictionaryItem.CF_Standard.ToString() }
                        equals new { StandardID = fdi.Value, fdi.DictionaryCode } into tfdi
                        from cfdi in tfdi.DefaultIfEmpty()
                        join sct in DictionaryItemRepository.Entities on new { sp.CourseCategoryID, DictionaryCode = DictionaryItem.CF_CourseCategory.ToString() }
                        equals new { CourseCategoryID = sct.Value, sct.DictionaryCode } into tsct
                        from csct in tsct.DefaultIfEmpty()
                        join sctt in DictionaryItemRepository.Entities on new { sp.CourseTypeID, DictionaryCode = DictionaryItem.CF_CourseType.ToString() }
                        equals new { CourseTypeID = sctt.Value, sctt.DictionaryCode } into tsctt
                        from csctt in tsctt.DefaultIfEmpty()
                        join scq in DictionaryItemRepository.Entities on new { sp.CourseQualityID, DictionaryCode = DictionaryItem.CF_CourseQuality.ToString() }
                        equals new { CourseQualityID = scq.Value, scq.DictionaryCode } into tscq
                        from cscq in tscq.DefaultIfEmpty()
                        select new StudentsOrderView
                        {
                            // StudentsOrderID = so.StudentsOrderID,
                            SpecialtyPlanID = so.SpecialtyPlanID,
                            SchoolyearID = sp.CF_Schoolyear.SchoolyearID,
                            SchoolyearCode = sp.CF_Schoolyear.Code,
                            CollegeID = sp.CF_Grademajor.CF_Facultymajor.CF_College.CollegeID,
                            CollegeName = sp.CF_Grademajor.CF_Facultymajor.CF_College.Name,
                            CollegeNo = sp.CF_Grademajor.CF_Facultymajor.CF_College.No,
                            Years = sp.CF_Grademajor.GradeID,
                            GradeID = sp.CF_Grademajor.GradeID,
                            GrademajorCode = sp.CF_Grademajor.Code,
                            GrademajorName = sp.CF_Grademajor.Name,
                            GrademajorID = sp.GrademajorID,
                            StandardID = sp.CF_Grademajor.CF_Facultymajor.StandardID,
                            StandardName = cfdi.Name,
                            //ClassNum = so.OrderQty,
                            ClassNum = sp.CF_Grademajor.CF_Classmajor.Sum(x => x.CF_Student.Where(e => inSchoolStatusIDList.Contains(e.InSchoolStatusID)).Count()),//修改学生人数查询
                            OrderQty = so.OrderQty,
                            CourseCode = sp.EM_Coursematerial.CourseCode,
                            CourseName = sp.EM_Coursematerial.CourseName,
                            CourseCategoryID = sp.CourseCategoryID,
                            CourseCategoryName = csct.Name,
                            CourseTypeID = sp.CourseTypeID,
                            CourseTypeName = csctt.Name,
                            CourseQualityID = sp.CourseQualityID,
                            CourseQualityName = cscq.Name,
                            PreIncreaseQty = so.PreIncreaseQty == null ? 0 : so.PreIncreaseQty,
                            CampusID = sp.CF_Grademajor.CF_Facultymajor.CF_College.CampusID,
                            IsOrdered = so.IsOrdered,
                            IsOrderedName = so.IsOrdered == true ? "是" : "否",
                            TeachingMaterialPoolID = so.TeachingMaterialPoolID,
                            CoursematerialID = sp.CoursematerialID,
                            CF_TeachingMaterialPool = so.CF_TeachingMaterialPool,
                            CreateTime = so.CreateTime,
                            CreateUserNo = u.LoginID,
                            CreateUserName = u.Name,
                            Count = so.OrderQty + (so.PreIncreaseQty == null ? 0 : so.PreIncreaseQty)
                        });
            return view.GroupBy(x => x.SpecialtyPlanID).Select(x => x.FirstOrDefault());
            
        }

        /// <summary>
        /// 教材信息
        /// </summary>
        /// <param name="specialtyPlanID"></param>
        /// <returns></returns>
        public IQueryable<SpecialtyPlanTeachingMaterPoolView> GetSpecialtyPlanTeachingMaterPoolView(Expression<Func<ET_StudentsOrder, bool>> exp, Expression<Func<EM_SpecialtyPlan, bool>> expSp)
        {
            var view = (from so in StudentsOrderRepository.GetList(exp)
                        join sp in SpecialtyPlanRepository.GetList(expSp)
                        on so.SpecialtyPlanID equals sp.SpecialtyPlanID
                        select new SpecialtyPlanTeachingMaterPoolView
                        {
                            TeachingMaterialCode = so.CF_TeachingMaterialPool.TeachingMaterialCode,
                            TeachingMaterialName = so.CF_TeachingMaterialPool.TeachingMaterialName,
                            SpecialtyPlanID = so.SpecialtyPlanID

                        });
            return view;
        }

        public IList<StudentOrderPrePlanView> GetStudentsOrderIDsBySpecialtyPlan(Guid schoolyearID)
        {
            var planList = (from sd in StudentsOrderRepository.Entities.Where(x => x.IsOrdered == false && x.TeachingMaterialPoolID == null)
                            join sy in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == schoolyearID) on sd.EM_SpecialtyPlan.SchoolyearID equals sy.SchoolyearID
                            select new StudentOrderPrePlanView
                            {
                                SpecialtyPlanID = sd.SpecialtyPlanID
                            });


            return planList.ToList();
        }

        public IList<StudentOrderPrePlanView> GetSpecialtyPlanBySchool(Guid schoolyearID)
        {
            var planList = from s in SpecialtyPlanRepository.Entities.Where(x => x.SchoolyearID == schoolyearID)
                           select new StudentOrderPrePlanView
                           {
                               SpecialtyPlanID = s.SpecialtyPlanID
                           };
            return planList.ToList();
        }
        /// <summary>
        /// 获取专业计划对应的需要征订的信息
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<StudentOrderPrePlanView> GetStudentsOrderBySpecialtyPlan(Guid schoolyearID)
        {
            var inSchoolStatusIDList = InSchoolSettingDAL.InSchoolSettingRepository.GetList(x => x.IsSelected == true).Select(w => w.InSchoolStatusID).ToList();//获取选中的在校设定状态、
            #region 0.0 废弃
            //var planList = (from sp in SpecialtyPlanRepository.Entities.Where(x => x.IsNeedMaterial == true)
            //                join sy in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == schoolyearID) on sp.SchoolyearID equals sy.SchoolyearID
            //                join gj in GrademajorRepository.Entities on sp.GrademajorID equals gj.GrademajorID
            //                join sc in SpecialtyCourseRepository.Entities on sp.SpecialtyCourseID equals sc.SpecialtyCourseID
            //                join cj in ClassmajorRepository.Entities on gj.GrademajorID equals cj.GrademajorID into tcj
            //                from ccj in tcj.DefaultIfEmpty()
            //                join tlpv in
            //                    (
            //                        from tlp in
            //                            (
            //                               from gtlp in TeachingMaterialPoolRepository.Entities.Where(x => x.IsLate != true)
            //                               group gtlp by gtlp.CoursematerialID into g
            //                               select new { CoursematerialID = g.Key, MaxDate = g.Max(x => x.CreateTime) }
            //                            )
            //                        join tlpo in TeachingMaterialPoolRepository.Entities.Where(x => x.IsLate != true)
            //                        on new { tlp.CoursematerialID, tlp.MaxDate } equals new { tlpo.CoursematerialID, MaxDate = tlpo.CreateTime }
            //                        select tlpo
            //                        ) on sc.CoursematerialID equals tlpv.CoursematerialID into ttlpv
            //                from ctlpv in ttlpv.DefaultIfEmpty()
            //                join so in StudentsOrderRepository.Entities on sp.SpecialtyPlanID equals so.SpecialtyPlanID into tso
            //                from cso in tso.DefaultIfEmpty()
            //                where cso == null  // || cso.IsOrdered==true //如果已经提交征订、那么可以再次征订
            //                group new { sp, sd = ccj.CF_Student, ctlpv }
            //                by new { sp.SpecialtyPlanID, ctlpv.TeachingMaterialPoolID } into g
            //                select new StudentOrderPrePlanView
            //                {
            //                    SpecialtyPlanID = g.Key.SpecialtyPlanID,
            //                    OrderQty = g.Sum(x => x.sd.Count),
            //                    TeachingMaterialPoolID = g.Key.TeachingMaterialPoolID
            //                });
            //var planListinfo = planList;
            //return planList.ToList(); ; 
            #endregion
            // var curUser = EMIS.Utility.FormValidate.CustomPrincipal.Current;
            Expression<Func<EM_SpecialtyPlan, bool>> exp = x => x.IsNeedMaterial == true;

            var planList = (from sp in SpecialtyPlanRepository.GetList(exp)
                            join sy in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == schoolyearID) on sp.SchoolyearID equals sy.SchoolyearID
                            join gj in GrademajorRepository.Entities on sp.GrademajorID equals gj.GrademajorID
                            join cj in ClassmajorRepository.Entities on sp.GrademajorID equals cj.GrademajorID into tcj
                            from ccj in tcj.DefaultIfEmpty()
                            from ctlpv in sp.EM_Coursematerial.CF_TeachingMaterialPool.Where(x => x.IsLate == false).DefaultIfEmpty()//只获取没过期的教材
                            join so in StudentsOrderRepository.Entities on sp.SpecialtyPlanID equals so.SpecialtyPlanID into tso
                            from cso in tso.DefaultIfEmpty()
                            where cso == null
                            group new { sp, ccj, ctlpv }
                            by new { sp.SpecialtyPlanID, ctlpv.TeachingMaterialPoolID, sp.CF_Grademajor.CF_Facultymajor.CollegeID } into g
                            select new StudentOrderPrePlanView
                            {
                                CollegeID = g.Key.CollegeID,
                                SpecialtyPlanID = g.Key.SpecialtyPlanID,
                                OrderQty = g.Sum(x => x.ccj.CF_Student.Where(e => inSchoolStatusIDList.Contains(e.InSchoolStatusID)).Count()),//只获取在校学生
                                TeachingMaterialPoolID = g.Key.TeachingMaterialPoolID
                            });
            return planList;
        }




        /// <summary>
        /// 根据征订ID获取征订数据
        /// </summary>
        /// <param name="studentsOrderIDs"></param>
        /// <returns></returns>
        public IList<ET_StudentsOrder> GetStudentsOrderListByStudentsOrderIDs(List<Guid> studentsOrderIDs)
        {
            return StudentsOrderRepository.GetList(x => studentsOrderIDs.Contains(x.StudentsOrderID)).ToList();
        }

        /// <summary>
        /// 学生征订统计
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<StudentsOrderDataReportView> GetStudentsOrderStatisticalView(Expression<Func<ET_StudentsOrder, bool>> exporder)
        {
            var view = (from so in StudentsOrderRepository.GetList(exporder)
                        join sp in SpecialtyPlanRepository.Entities on so.SpecialtyPlanID equals sp.SpecialtyPlanID
                        join sy in SchoolyearRepository.Entities on sp.SchoolyearID equals sy.SchoolyearID
                        join gj in GrademajorRepository.Entities on sp.GrademajorID equals gj.GrademajorID
                        join fj in FacultymajorRepository.Entities on gj.FacultymajorID equals fj.FacultymajorID
                        join co in CollegeRepository.Entities on fj.CollegeID equals co.CollegeID
                        join cos in CoursematerialRepository.Entities on sp.CoursematerialID equals cos.CoursematerialID
                        join u in UserRepository.Entities on so.CreateUserID equals u.UserID
                        join tmp in TeachingMaterialPoolRepository.Entities on so.TeachingMaterialPoolID equals tmp.TeachingMaterialPoolID
                        into teps
                        from Tep in teps.DefaultIfEmpty()
                        join p in PublishRepository.Entities on Tep.PublishID equals p.PublishID
                        into TepPublish
                        from pb in TepPublish.DefaultIfEmpty()
                        join cj in ClassmajorRepository.Entities on gj.GrademajorID equals cj.GrademajorID into tcj
                        from ccj in tcj.DefaultIfEmpty()
                        select new StudentsOrderDataReportView
                        {
                            SchoolyearID = sy.SchoolyearID,
                            SchoolyearCode = sy.Code,
                            CollegeID = co.CollegeID,
                            CollegeName = co.Name,
                            GrademajorCode = gj.Code,
                            GrademajorName = gj.Name,
                            GrademajorID = gj.GrademajorID,
                            GradeID = gj.GradeID,
                            SpecialtyValue = gj.CF_Facultymajor.StandardID,
                            ClassNum = so.OrderQty,
                            OrderQty = so.OrderQty,
                            PreIncreaseQty = so.PreIncreaseQty,
                            CourseCode = cos.CourseCode,
                            CourseName = cos.CourseName,
                            CampusID = co.CampusID,
                            CoursematerialID = cos.CoursematerialID,
                            TeachingMaterialCode = Tep.TeachingMaterialCode,
                            TeachingMaterialName = Tep.TeachingMaterialName,
                            Author = Tep.Author,
                            ISBN = Tep.ISBN,
                            TeachingMaterialPoolID = Tep.TeachingMaterialPoolID,
                            PublishName = pb.UnitName,
                            PublishTime = Tep.PublishTime,
                            PublishID = Tep.PublishID,
                            CreateTime = so.CreateTime,
                            CreateUserName = u.Name,
                            IsOrdered = so.IsOrdered,
                            Price = Tep.Price ?? 0,
                            Years = sp.CF_Grademajor.GradeID,
                            TeachingMaterialTypeID = Tep.TeachingMaterialTypeID ?? 0,
                            CourseCategory = sp.CourseCategoryID,
                            Count = so.OrderQty + (so.PreIncreaseQty == null ? 0 : so.PreIncreaseQty)
                        });

            return view.Distinct();
        }

    }
}