JW/EMiS.DataLogic/Common/ProcurementPlan/PurchasingStatisticsDAL.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.ProcurementPlan
{
    public class PurchasingStatisticsDAL
    {
        public TeachersOrderRepository TeachersOrderRepository { get; set; }//教师征订
        public StudentsOrderRepository StudentsOrderRepository { get; set; }//学生征订 
        public TeachersPreOrderRepository TeachersPreOrderRepository { get; set; }//预加设置
        public TeachingMaterialPoolRepository TeachingMaterialPoolRepository { 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; }

        /// <summary>
        /// 查询采购统计列表页面
        /// </summary>
        /// <param name="exporder"></param>
        /// <returns></returns>
        public IQueryable<PurchasingStatisticsView> GetPurchasingStatisticsGridView(Expression<Func<ET_TeachersOrder, bool>> exporder,
            Expression<Func<ET_StudentsOrder, bool>> stuExporder)
        {
            //1.0 教师征订数据
            var teachersOrderList = from a in TeachersOrderRepository.GetList(exporder)
                                    join b in TeachersPreOrderRepository.Entities on new { a.TeachingMaterialPoolID, a.SchoolyearID } equals new { b.TeachingMaterialPoolID, b.SchoolyearID }
                                    into tcls
                                    from tacls in tcls.DefaultIfEmpty()
                                    group new { a, tacls } by new
                                    {
                                        a.TeachingMaterialPoolID,
                                        a.SchoolyearID,
                                        tacls.PreQty
                                    } into h
                                    select new
                                    {
                                        TeachingMaterialPoolID = h.Key.TeachingMaterialPoolID,
                                        SchoolyearID = h.Key.SchoolyearID,
                                        OrderQty = h.Sum(x => x.a.OrderQty) + (h.Key.PreQty == null ? 0 : h.Key.PreQty)  //教师征订有预加值、
                                    };
            //2.0 学生征订数据
            var studentsOrderList = from a in StudentsOrderRepository.GetList(stuExporder).Where(x => x.IsOrdered == true)
                                    select new
                                    {
                                        TeachingMaterialPoolID = (Guid)a.TeachingMaterialPoolID,
                                        SchoolyearID = (Guid)a.EM_SpecialtyPlan.SchoolyearID,
                                        OrderQty = a.OrderQty + (int)(a.PreIncreaseQty == null ? 0 : a.PreIncreaseQty)
                                    };
            //3.0 合并统计输出
            var view = (from to in teachersOrderList.Concat(studentsOrderList)
                        join tm in TeachingMaterialPoolRepository.Entities on to.TeachingMaterialPoolID equals tm.TeachingMaterialPoolID
                        join p in PublishRepository.Entities on tm.PublishID equals (Guid)p.PublishID
                        join sl in SchoolyearRepository.Entities on to.SchoolyearID equals sl.SchoolyearID
                        group new { to, tm, sl, p } by new
                        {
                            SchoolyearID = to.SchoolyearID,
                            TeachingMaterialPoolID = to.TeachingMaterialPoolID,
                            PublishID = tm.PublishID,
                            Code = sl.Code,
                            TeachingMaterialCode = tm.TeachingMaterialCode,
                            TeachingMaterialTypeID = tm.TeachingMaterialTypeID,
                            ISBN = tm.ISBN,
                            TeachingMaterialName = tm.TeachingMaterialName,
                            PublishTime = tm.PublishTime,
                            UnitName = p.UnitName,
                            Author = tm.Author,
                            Price = tm.Price
                        } into h
                        join tm in TeachingMaterialPoolRepository.Entities on h.Key.TeachingMaterialPoolID equals tm.TeachingMaterialPoolID
                        select new PurchasingStatisticsView
                        {
                            SchoolyearID = h.Key.SchoolyearID,
                            TeachingMaterialPoolID = h.Key.TeachingMaterialPoolID,
                            CoursematerialEntityList = tm.EM_Coursematerial,
                            TeachingMaterialTypeID = h.Key.TeachingMaterialTypeID,
                            PublishID = h.Key.PublishID,
                            SchoolyearName = h.Key.Code,
                            TeachingMaterialCode = h.Key.TeachingMaterialCode,
                            ISBN = h.Key.ISBN,
                            TeachingMaterialName = h.Key.TeachingMaterialName,
                            PublishTime = h.Key.PublishTime,
                            PublishName = h.Key.UnitName,
                            Author = h.Key.Author,
                            Price = h.Key.Price,
                            CountNumber = h.Sum(x => x.to.OrderQty)//累计同一教材征订数量
                        });//教材ID、为关联信息

            return view;
        }
    }
}