JW/EMiS.DataLogic/Common/TeachingMaterial/TeachersConfirmOrderDAL.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;

namespace EMIS.DataLogic.Common.TeachingMaterial
{
    public class TeachersConfirmOrderDAL
    {
        #region 0.0 --定义--
        public TeachersOrderRepository TeachersOrderRepository { get; set; }
        public TeachersConfirmOrderRepository TeachersConfirmOrderRepository { get; set; }
        public TeachingMaterialPoolRepository TeachingMaterialPoolRepository { get; set; }
        public TeachersPreOrderRepository TeachersPreOrderRepository { get; set; }



        public UserRepository UserRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public PublishRepository PublishRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        #endregion

        #region  2.0 提取征订清单数据列表
        /// <summary>
        /// 获取征订清单数据列表
        /// 以学年跟教材编号分组
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<TeachersConfirmOrderView> GetTeachersConfirmOrderGridView(Expression<Func<ET_TeachersOrder, bool>> exporder)
        {
            var query = from nto in
                            (from to in TeachersOrderRepository.Entities.Where(exporder)
                             group to by new { to.SchoolyearID, to.TeachingMaterialPoolID }
                                 into a
                                 select new
                                 {
                                     a.Key.SchoolyearID,
                                     a.Key.TeachingMaterialPoolID,
                                     sumOrderQty = a.Sum(x => x.OrderQty),
                                     DateTime = a.Max(x => x.OrderDate),
                                 })
                        join tpo in TeachersPreOrderRepository.Entities
                        on new { nto.SchoolyearID, nto.TeachingMaterialPoolID } equals new { tpo.SchoolyearID, tpo.TeachingMaterialPoolID }
                        into b
                        from ntpo in b.DefaultIfEmpty()
                        join tmp in TeachingMaterialPoolRepository.Entities on nto.TeachingMaterialPoolID equals tmp.TeachingMaterialPoolID
                        join tto in TeachersOrderRepository.Entities.Where(exporder)
                        on new { nto.SchoolyearID, nto.TeachingMaterialPoolID, nto.DateTime.Value } equals new { tto.SchoolyearID, tto.TeachingMaterialPoolID, tto.OrderDate.Value }
                        join u in UserRepository.Entities on tto.OrderUserID equals u.UserID
                        join p in PublishRepository.Entities on tmp.PublishID equals p.PublishID
                        join sl in SchoolyearRepository.Entities on tto.SchoolyearID equals sl.SchoolyearID
                        join cr in CollegeRepository.Entities on tto.CollegeID equals cr.CollegeID
                        //join tpo in TeachersPreOrderRepository.Entities
                        //on new { nto.SchoolyearID, nto.TeachingMaterialPoolID } equals new { tpo.SchoolyearID, tpo.TeachingMaterialPoolID }
                        //into b
                        //from ntpo in b.DefaultIfEmpty()
                        select new TeachersConfirmOrderView
                        {
                            OrderUserName = u.Name,
                            SchoolyearID = tto.SchoolyearID,
                            SchoolyearName = sl.Code,
                            TeachingMaterialCode = tmp.TeachingMaterialCode,
                            Author = tmp.Author,
                            OrderDate = nto.DateTime,
                            TeachingMaterialName = tmp.TeachingMaterialName,
                            PublishTime = tmp.PublishTime,
                            TeachingMaterialPoolID = nto.TeachingMaterialPoolID,
                            PublishName = p.UnitName,
                            OrderQty = nto.sumOrderQty,
                            CoursematerialEntityList = tmp.EM_Coursematerial,
                            PublishID = p.PublishID,
                            ISBN = tmp.ISBN,
                            PreAddedValue = ntpo.PreQty == null ? 0 : ntpo.PreQty,
                            Price = tmp.Price,
                            CountNumber = ntpo.PreQty == null ? (nto.sumOrderQty + 0) : (nto.sumOrderQty + ntpo.PreQty),
                        };

            var view = (from to in TeachersOrderRepository.Entities.Where(exporder)
                        join tm in TeachingMaterialPoolRepository.Entities on to.TeachingMaterialPoolID equals tm.TeachingMaterialPoolID
                        join u in UserRepository.Entities on to.OrderUserID equals u.UserID
                        join p in PublishRepository.Entities on tm.PublishID equals (Guid)p.PublishID
                        join sl in SchoolyearRepository.Entities on to.SchoolyearID equals sl.SchoolyearID
                        join cr in CollegeRepository.Entities on to.CollegeID equals cr.CollegeID
                        join per in TeachersPreOrderRepository.Entities on new { to.TeachingMaterialPoolID, to.SchoolyearID } equals new
                        {
                            per.TeachingMaterialPoolID,
                            per.SchoolyearID
                        }
                        into pers
                        from perstb in pers.DefaultIfEmpty()
                        group new { to, tm, sl, p, perstb } by new
                        {
                            u.Name,
                            sl.SchoolyearID,
                            sl.Code,
                            tm.TeachingMaterialCode,
                            tm.Author,
                            tm.TeachingMaterialName,
                            tm.PublishTime,
                            to.TeachingMaterialPoolID,
                            to.OrderDate,
                            perstb.PreQty,
                            p.UnitName,
                            p.PublishID,
                            tm.ISBN,
                            tm.Price
                        } into h 
                        join tm in TeachingMaterialPoolRepository.Entities on h.Key.TeachingMaterialPoolID equals tm.TeachingMaterialPoolID
                        select new TeachersConfirmOrderView
                        {
                            OrderUserName = h.Key.Name,
                            SchoolyearID = h.Key.SchoolyearID,
                            SchoolyearName = h.Key.Code,
                            TeachingMaterialCode = h.Key.TeachingMaterialCode,
                            Author = h.Key.Author,
                            OrderDate=h.Key.OrderDate,
                            TeachingMaterialName = h.Key.TeachingMaterialName,
                            PublishTime = h.Key.PublishTime,
                            TeachingMaterialPoolID = h.Key.TeachingMaterialPoolID,
                            PublishName = h.Key.UnitName,
                            OrderQty = h.Sum(x => x.to.OrderQty) == null ? 0 : h.Sum(x => x.to.OrderQty),
                            CoursematerialEntityList = tm.EM_Coursematerial,
                            PublishID = h.Key.PublishID,
                            ISBN = h.Key.ISBN,
                            PreAddedValue = h.Key.PreQty == null ? 0 : h.Key.PreQty,
                            Price = h.Key.Price,
                            //  CollegeID = h.Key.CollegeID,
                            CountNumber = h.Key.PreQty == null ? (h.Sum(x => x.to.OrderQty) + 0) : ((h.Sum(x => x.to.OrderQty) + h.Key.PreQty))
                        });//以教材编号及学年进行分组 

            return query;
        }
        #endregion

        #region 3.0 查询征订信息列表
        /// <summary>
        /// 获取征订信息列表
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<TeachersConfirmOrderView> GetTeachingOrderDetail(Expression<Func<ET_TeachersOrder, bool>> exporder)
        {
            var view = (from to in TeachersOrderRepository.Entities.Where(exporder)
                        join tm in TeachingMaterialPoolRepository.Entities on to.TeachingMaterialPoolID equals tm.TeachingMaterialPoolID
                        join u in UserRepository.Entities on to.OrderUserID equals u.UserID
                        join p in PublishRepository.Entities on tm.PublishID equals (Guid)p.PublishID
                        join sl in SchoolyearRepository.Entities on to.SchoolyearID equals sl.SchoolyearID
                        join cr in CollegeRepository.Entities on to.CollegeID equals cr.CollegeID

                        select new TeachersConfirmOrderView
                       {
                           SchoolyearID = sl.SchoolyearID,
                           SchoolyearName = sl.Code,
                           TeachingMaterialCode = tm.TeachingMaterialCode,
                           Author = tm.Author,
                           CollegeID = cr.CollegeID,
                           CollegeName = cr.Name,
                           TeachingMaterialName = tm.TeachingMaterialName,
                           PublishTime = tm.PublishTime,
                           TeachersOrderID = to.TeachersOrderID,
                           TeachingMaterialPoolID = tm.TeachingMaterialPoolID,
                           PublishName = p.UnitName,
                           OrderUserName = u.Name,
                           OrderDate = to.OrderDate,
                           OrderQty = to.OrderQty,
                           OrderDesc = to.OrderDesc,
                           CoursematerialEntityList = tm.EM_Coursematerial,
                           PublishID = p.PublishID,
                           CreateTime = to.CreateTime,
                           ISBN = tm.ISBN,
                           Price = tm.Price,
                           Desc = tm.Desc
                       });
            return view;
        }

        #endregion


    }
}