JW/EMiS.DataLogic/Common/TeachingMaterial/InventoryDAL.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.ViewModel.Cultureplan;
using EMIS.DataLogic.Common.Cultureplan;


namespace EMIS.DataLogic.Common.TeachingMaterial
{
    //库存
    public class InventoryDAL
    {
        #region --0.0 定义--
        public TeachingMaterialPoolRepository TeachingMaterialPoolRepository { get; set; }

        public TeachingMateriaInventoryRepository TeachingMateriaInventoryRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public PublishRepository PublishRepository { get; set; }
        public InventoryCollectRepository InventoryCollectRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }

        public StockOutRepository StockOutRepository { get; set; }
        public StockOutDetailRepository StockOutDetailRepository { get; set; }

        public StockInRepository StockInRepository { get; set; }
        public StockInDetailRepository StockInDetailRepository { get; set; }

        public StudentDistributeRepository StudentDistributeRepository { get; set; }

        public Lazy<CoursematerialDAL> CoursematerialDAL { get; set; }
        #endregion

        #region 1.0 获取教材库存信息
        /// <summary>
        /// 获取教材库存信息列表
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<InventoryView> GetTeachingMaterialInventoryGridView(Expression<Func<ET_TeachingMateriaInventory, bool>> exp, Expression<Func<CF_TeachingMaterialPool, bool>> poolexp)
        {

            var view = (from l in TeachingMaterialPoolRepository.GetList(poolexp)
                        join t in TeachingMateriaInventoryRepository.GetList(exp) on l.TeachingMaterialPoolID equals t.TeachingMaterialPoolID into tsct
                        from tm in tsct.DefaultIfEmpty()
                        //join d in DictionaryItemRepository.Entities
                        //on new { l.TeachingMaterialTypeID, DictionaryCode = typeof(CF_TeachingMaterialType).Name }
                        //equals new { TeachingMaterialTypeID = d.Value, d.DictionaryCode } into dd
                        //from ed in dd.DefaultIfEmpty()
                        join u in UserRepository.Entities on l.CreateUserID equals u.UserID
                        into uTmp
                        from UserTabel in uTmp.DefaultIfEmpty()
                        join p in PublishRepository.Entities on l.PublishID equals p.PublishID
                        into pp
                        from ppp in pp.DefaultIfEmpty()//改为左联
                        group new 
                        { 
                            TeachingMaterial = l,
                            Inventory = tm,
                            //TeachingMaterialType = ed,
                            User = UserTabel,
                            Publish = ppp
                        } by new 
                        {
                            Author = l.Author,
                            ISBN = l.ISBN,
                            Price = l.Price,
                            TeachingMaterialCode = l.TeachingMaterialCode,
                            TeachingMaterialName = l.TeachingMaterialName,
                            TeachingMaterialShorName = l.TeachingMaterialShorName,
                            TeachingMaterialPoolID = l.TeachingMaterialPoolID,
                            TeachingMaterialTypeID = l.TeachingMaterialTypeID,
                            //TeachingMaterialTypeName = ed.Name,
                            PublishID = l.PublishID,
                            PublishName = ppp.UnitName,
                            PublishTime = l.PublishTime,
                            CreateTime = l.CreateTime,
                            CreateUserName = UserTabel.Name,
                            Desc = l.Desc,
                            IsLate = l.IsLate.Value,
                            IsLateName = l.IsLate == true ? "是" : "否",
                            MinInventory = l.MinInventory == null ? 1 : l.MinInventory,
                            //ModifyTime = tm.ModifyTime == null ? l.ModifyTime : tm.ModifyTime
                        } into g 
                        join tm in TeachingMaterialPoolRepository.Entities on g.Key.TeachingMaterialPoolID equals tm.TeachingMaterialPoolID
                        select new InventoryView
                        {
                            Author = g.Key.Author,
                            ISBN = g.Key.ISBN,
                            Price = g.Key.Price,
                            TeachingMaterialCode = g.Key.TeachingMaterialCode,
                            TeachingMaterialName = g.Key.TeachingMaterialName,
                            TeachingMaterialShorName = g.Key.TeachingMaterialShorName,
                            TeachingMaterialPoolID = g.Key.TeachingMaterialPoolID,
                            TeachingMaterialTypeID = g.Key.TeachingMaterialTypeID,
                            //TeachingMaterialTypeName = g.Key.TeachingMaterialTypeName,
                            PublishID = g.Key.PublishID,
                            PublishName = g.Key.PublishName,
                            PublishTime = g.Key.PublishTime,
                            CreateTime = g.Key.CreateTime,
                            CreateUserName = g.Key.CreateUserName,
                            Desc = g.Key.Desc,
                            IsLate = g.Key.IsLate,
                            IsLateName = g.Key.IsLateName,
                            OrderQty = 1,
                            CoursematerialEntityList = tm.EM_Coursematerial,
                            MinInventory = g.Key.MinInventory,
                            PresentInventory = g.Sum(x => x.Inventory.PresentInventory ?? 0),
                            Total = g.Sum(x => x.Inventory.PresentInventory ?? 0) * g.Key.Price,//（码洋=单价*当前库存量）
                            ModifyTime = g.Max(x => x.Inventory.ModifyTime),
                        });
            return view;
        }

        public IQueryable<CoursematerialView> GetTeachingMaterialInventoryCoursematerialView(Expression<Func<ET_TeachingMateriaInventory, bool>> exp)
        {
            var query = (from l in TeachingMaterialPoolRepository.Entities
                         from lc in l.EM_Coursematerial
                         join c in CoursematerialDAL.Value.GetCoursematerialViewQueryable(x => true)
                            on lc.CoursematerialID equals c.CoursematerialID
                         select c
                             );

            return query;
        }
        #endregion

        public IQueryable<InventoryView> GetInventoryManagementGridOnlyNumView(Guid? schoolyearID)
        {
            int? yearValue = 0;
            string YearCode = SchoolyearRepository.GetSingle(x => x.SchoolyearID == schoolyearID).Code;
            if (schoolyearID != null)
            {
                yearValue = SchoolyearRepository.GetSingle(x => x.SchoolyearID == schoolyearID).Value;
            }


            #region 0.0 找出当前学年的出入库数量
            var oldStockIns = from si in StockInRepository.GetList(x => x.SchoolyearID == schoolyearID && x.RecordStatus == (int)CF_YesOrNoStatus.Yes)
                           join sid in StockInDetailRepository.Entities on si.StockInID equals sid.StockInID
                           group sid by sid.TeachingMaterialPoolID
                               into gsid
                               select new
                               {
                                   TeachingMaterialPoolID = (Guid)gsid.Key,
                                   InQty = (int?)gsid.Sum(x => x.Quantity),
                                   OutQty = (int?)0,
                               };
            var oldStockOuts = from a in StockOutRepository.Entities.Where(x => x.SchoolyearID == schoolyearID && x.RecordStatus == (int)CF_YesOrNoStatus.Yes)
                               join b in StockOutDetailRepository.Entities on a.StockOutID equals b.StockOutID
                               group b by b.TeachingMaterialPoolID
                                   into stockout
                                   select new
                                   {
                                       TeachingMaterialPoolID = stockout.Key,
                                       InQty = (int?)0,
                                       OutQty = (int?)stockout.Sum(x => x.StockOutQuantity),
                                   };


            var oldStockInQry = from b in oldStockIns
                                join c in oldStockOuts on b.TeachingMaterialPoolID equals c.TeachingMaterialPoolID
                                into cc
                                from ccc in cc.DefaultIfEmpty()
                                select new
                                {
                                    TeachingMaterialPoolID = (Guid)b.TeachingMaterialPoolID,
                                    oldInQty = b.InQty,
                                    oldOutQty = ccc.OutQty ?? 0,
                                };
            var oldInAndOut = from b in oldStockIns.Concat(oldStockOuts)
                              group b by b.TeachingMaterialPoolID
                                  into aa
                                  select new
                                  {
                                      TeachingMaterialPoolID = (Guid)aa.Key,
                                      oldInQty = aa.Sum(x => x.InQty),
                                      oldOutQty = aa.Sum(x => x.OutQty),
                                  };

            #endregion

            #region 0.1 找出当前学年之后的出入库数量汇总(包含当前学年) 列： >=2015-201601   之后
            #region 1.0 入库
            var StockInview = from a in StockInRepository.Entities.Where(x => x.CF_Schoolyear.Value >= yearValue && x.RecordStatus == (int)CF_YesOrNoStatus.Yes)
                              join b in StockInDetailRepository.Entities on a.StockInID equals b.StockInID
                              group b by b.TeachingMaterialPoolID
                                  into stockin
                                  select new
                                  {
                                      TeachingMaterialPoolID = (Guid)stockin.Key,
                                      InQty = stockin.Sum(x => x.Quantity),
                                      OutQty = (int?)0
                                  };


            #endregion

            #region 2.0 出库

            var StockOutview = from a in StockOutRepository.Entities.Where(x => x.CF_Schoolyear.Value >= yearValue && x.RecordStatus == (int)CF_YesOrNoStatus.Yes)
                               join b in StockOutDetailRepository.Entities on a.StockOutID equals b.StockOutID
                               group b by b.TeachingMaterialPoolID
                                   into stockout
                                   select new
                                   {
                                       TeachingMaterialPoolID = stockout.Key,
                                       InQty = (int?)0,
                                       OutQty = stockout.Sum(x => x.StockOutQuantity),
                                   };

            #endregion


            #region 3.0 合并出入库数据进行分组
            var InByOutStockInfoGroup = from b in oldStockIns
                                        join c in oldStockOuts on b.TeachingMaterialPoolID equals c.TeachingMaterialPoolID
                                        into cc
                                        from ccc in cc.DefaultIfEmpty()
                                        select new
                                        {
                                            TeachingMaterialPoolID = (Guid)b.TeachingMaterialPoolID,
                                            InQty = b.InQty,
                                            OutQty = ccc.OutQty ?? 0,
                                        };
            var InAndOut = from b in StockInview.Concat(StockOutview)
                           group b by b.TeachingMaterialPoolID
                               into aa
                               select new
                               {
                                   TeachingMaterialPoolID = (Guid)aa.Key,
                                   // SchoolyearID = h.Key.SchoolyearID,
                                   InQty = aa.Sum(x => x.InQty),
                                   OutQty = aa.Sum(x => x.OutQty),
                               };
            #endregion
            #endregion

            #region 4.0 对分组后的出入库数据跟教材库存做统计输出~
            var InventoryViews = from a in TeachingMaterialPoolRepository.Entities.Where(x => x.IsLate == false)
                                 join t in TeachingMateriaInventoryRepository.Entities on a.TeachingMaterialPoolID equals t.TeachingMaterialPoolID
                                 group t by t.TeachingMaterialPoolID
                                 into tea
                                 join InByOut in InAndOut on tea.Key equals InByOut.TeachingMaterialPoolID
                                 into InByOutdata
                                 from InByOuttb in InByOutdata.DefaultIfEmpty()
                                 join oldstin in oldInAndOut on tea.Key equals oldstin.TeachingMaterialPoolID
                                 into toldstin
                                 from ctoldstin in toldstin.DefaultIfEmpty()
                                 join p in PublishRepository.Entities on tea.FirstOrDefault().CF_TeachingMaterialPool.PublishID equals p.PublishID
                                 into pp
                                 from ppp in pp.DefaultIfEmpty()
                                 select new InventoryView
                                 {
                                     SchoolYearName = YearCode,
                                     TeachingMaterialPoolID = tea.Key,
                                     // SchoolYearID = InByOuttb.SchoolyearID == null ? (Guid)schoolyearID : InByOuttb.SchoolyearID,
                                     PublishID = ppp.PublishID,
                                     TeachingMaterialTypeID = tea.FirstOrDefault().CF_TeachingMaterialPool.TeachingMaterialTypeID,
                                     TeachingMaterialCode = tea.FirstOrDefault().CF_TeachingMaterialPool.TeachingMaterialCode,
                                     TeachingMaterialName = tea.FirstOrDefault().CF_TeachingMaterialPool.TeachingMaterialName,
                                     CoursematerialEntityList = tea.FirstOrDefault().CF_TeachingMaterialPool.EM_Coursematerial,
                                     ISBN = tea.FirstOrDefault().CF_TeachingMaterialPool.ISBN,
                                     //TeachingMaterialTypeName = ed.Name,
                                     PublishTime = tea.FirstOrDefault().CF_TeachingMaterialPool.PublishTime,
                                     PublishName = ppp.UnitName,
                                     Author = tea.FirstOrDefault().CF_TeachingMaterialPool.Author,
                                     InventoryCollectQty = (tea.Sum(x => x.PresentInventory) ?? 0) - (InByOuttb.InQty ?? 0) + (InByOuttb.OutQty ?? 0),
                                     ////StockInQty = InByOuttb.InQty == null ? 0 : InByOuttb.InQty,
                                     ////StockOutQty = InByOuttb.OutQty == null ? 0 : InByOuttb.OutQty,
                                     StockInQty = ctoldstin.oldInQty ?? 0,
                                     StockOutQty = ctoldstin.oldOutQty ?? 0,
                                     PresentInventory = tea.Sum(x => x.PresentInventory) ?? 0,
                                     MinInventory = tea.FirstOrDefault().CF_TeachingMaterialPool.MinInventory == null ? 1 : tea.FirstOrDefault().CF_TeachingMaterialPool.MinInventory,
                                     Price = tea.FirstOrDefault().CF_TeachingMaterialPool.Price,
                                 };
            #endregion

            return InventoryViews;

        }


        #region 2.0 获取库存统计信息
        /// <summary>
        /// 获取教材库存信息列表
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<InventoryView> GetInventoryManagementGridView(Guid? schoolyearID)
        {
            int? yearValue = 0;
            string YearCode = SchoolyearRepository.GetSingle(x => x.SchoolyearID == schoolyearID).Code;
            if (schoolyearID != null)
            {
                yearValue = SchoolyearRepository.GetSingle(x => x.SchoolyearID == schoolyearID).Value;
            }

            #region 0.0 找出当前学年的出入库数量
            var oldStockIns = from a in StockInRepository.Entities.Where(x => x.SchoolyearID == schoolyearID && x.RecordStatus == (int)CF_YesOrNoStatus.Yes)
                              join b in StockInDetailRepository.Entities on a.StockInID equals b.StockInID
                              group b by new {b.TeachingMaterialPoolID,b.DiscountPrice}
                              into stockin
                              select new
                              {
                                  TeachingMaterialPoolID = (Guid)stockin.Key.TeachingMaterialPoolID,
                                  DiscountPrice = stockin.Key.DiscountPrice,//折合价
                                  InQty = stockin.Sum(x => x.Quantity),
                                  OutQty = (int?)0,
                              };
            var oldStockOuts = from a in StockOutRepository.Entities.Where(x => x.SchoolyearID == schoolyearID && x.RecordStatus == (int)CF_YesOrNoStatus.Yes)
                               join b in StockOutDetailRepository.Entities on a.StockOutID equals b.StockOutID
                               group b by new { b.TeachingMaterialPoolID, b.Discount }
                               into stockout
                               select new
                               {
                                   TeachingMaterialPoolID = stockout.Key.TeachingMaterialPoolID,
                                   DiscountPrice = stockout.Key.Discount,//折合价
                                   InQty = (int?)0,
                                   OutQty = (int?)stockout.Sum(x => x.StockOutQuantity),
                               };

            //var oldStudentDistributes = from a in StudentDistributeRepository.Entities.Where(x => x.EM_SpecialtyPlan.SchoolyearID == schoolyearID && x.RecordStatus == (int)CF_YesOrNoStatus.Yes)
            //                            join b in TeachingMateriaInventoryRepository.Entities on a.TeachingMaterialPoolID equals b.TeachingMaterialPoolID
            //                            into tea
            //                            from ter in tea.DefaultIfEmpty()
            //                            select new
            //                            {
            //                                TeachingMaterialPoolID = (Guid)a.TeachingMaterialPoolID,
            //                                DiscountPrice = ter.Discount,
            //                                // SchoolyearID = a.SchoolyearID,
            //                                OutQty = (int)a.DistributeQty - a.CF_Student.Count()
            //                            } into c
            //                            group c by new { c.TeachingMaterialPoolID, c.DiscountPrice }
            //                                into d
            //                                select new
            //                                {
            //                                    TeachingMaterialPoolID = d.Key.TeachingMaterialPoolID,
            //                                    DiscountPrice = d.Key.DiscountPrice,
            //                                    // SchoolyearID = a.SchoolyearID,
            //                                    InQty = (int?)0,
            //                                    OutQty = (int?)d.Sum(x => x == null ? 0 : x.OutQty),
            //                                };

            var oldStockInQry = //from b in oldStockIns.Concat(oldStockOuts).Concat(oldStudentDistributes)
                                from b in oldStockIns
                                join c in oldStockOuts on new { b.TeachingMaterialPoolID, b.DiscountPrice } equals new { c.TeachingMaterialPoolID, c.DiscountPrice }
                                into cc
                                from ccc in cc.DefaultIfEmpty()
                                select new
                                {
                                    TeachingMaterialPoolID = (Guid)b.TeachingMaterialPoolID,
                                    DiscountPrice = b.DiscountPrice,
                                    // SchoolyearID = h.Key.SchoolyearID,
                                    oldInQty = b.InQty,
                                    oldOutQty = ccc.OutQty ?? 0,
                                };
            var oldInAndOut = from b in oldStockIns.Concat(oldStockOuts)
                               group b by new { b.TeachingMaterialPoolID, b.DiscountPrice }
                               into aa
                               select new
                               {
                                   TeachingMaterialPoolID = (Guid)aa.Key.TeachingMaterialPoolID,
                                   DiscountPrice = aa.Key.DiscountPrice,
                                   // SchoolyearID = h.Key.SchoolyearID,
                                   oldInQty = aa.Sum(x => x.InQty),
                                   oldOutQty = aa.Sum(x => x.OutQty),
                               };

            #endregion

            #region 0.1 找出当前学年之后的出入库数量汇总(包含当前学年) 列： >=2015-201601   之后
            #region 1.0 入库
            var StockInview = from a in StockInRepository.Entities.Where(x => x.CF_Schoolyear.Value >= yearValue && x.RecordStatus == (int)CF_YesOrNoStatus.Yes)
                              join b in StockInDetailRepository.Entities on a.StockInID equals b.StockInID
                              group b by new { b.TeachingMaterialPoolID, b.DiscountPrice }
                                  into stockin
                                  select new
                                  {
                                      TeachingMaterialPoolID = (Guid)stockin.Key.TeachingMaterialPoolID,
                                      DiscountPrice = stockin.Key.DiscountPrice,//折合价
                                      InQty = stockin.Sum(x => x.Quantity),
                                      OutQty = (int?)0
                                  };


            #endregion

            #region 2.0 出库

            var StockOutview = from a in StockOutRepository.Entities.Where(x => x.CF_Schoolyear.Value >= yearValue && x.RecordStatus == (int)CF_YesOrNoStatus.Yes)
                               join b in StockOutDetailRepository.Entities on a.StockOutID equals b.StockOutID
                               group b by new { b.TeachingMaterialPoolID, b.Discount }
                                   into stockout
                                   select new 
                                   {
                                       TeachingMaterialPoolID = stockout.Key.TeachingMaterialPoolID,
                                       DiscountPrice = stockout.Key.Discount,//折合价
                                       InQty = (int?)0,
                                       OutQty = stockout.Sum(x => x.StockOutQuantity),
                                   };

            #endregion

            #region 2.0 学生发放

            //var StudentDistributeview = from a in StudentDistributeRepository.Entities.Where(x => x.EM_SpecialtyPlan.CF_Schoolyear.Value.Value >= yearValue && x.RecordStatus == (int)CF_YesOrNoStatus.Yes)
            //                            join b in TeachingMateriaInventoryRepository.Entities on a.TeachingMaterialPoolID equals b.TeachingMaterialPoolID
            //                            into tea
            //                            from ter in tea.DefaultIfEmpty()
            //                            select new
            //                            {
            //                                TeachingMaterialPoolID = (Guid)a.TeachingMaterialPoolID,
            //                                DiscountPrice = ter.Discount,
            //                                // SchoolyearID = a.SchoolyearID,
            //                                OutQty = (int)a.DistributeQty - a.CF_Student.Count()
            //                            } into c
            //                            group c by new { c.TeachingMaterialPoolID, c.DiscountPrice }
            //                                into d
            //                                select new
            //                                {
            //                                    TeachingMaterialPoolID = d.Key.TeachingMaterialPoolID,
            //                                    DiscountPrice = d.Key.DiscountPrice,
            //                                    // SchoolyearID = a.SchoolyearID,
            //                                    OutQty = d.Sum(x => x == null ? 0 : x.OutQty)
            //                                };

            #endregion

            #region 3.0 合并出入库数据进行分组
            var InByOutStockInfoGroup = from b in oldStockIns
                                        //join d in oldStudentDistributes on new { b.TeachingMaterialPoolID, b.DiscountPrice } equals new { d.TeachingMaterialPoolID, d.DiscountPrice }
                                        //into dd
                                        //from ddd in dd.DefaultIfEmpty()
                                        join c in oldStockOuts on new { b.TeachingMaterialPoolID, b.DiscountPrice } equals new { c.TeachingMaterialPoolID, c.DiscountPrice }
                                        into cc
                                        from ccc in cc.DefaultIfEmpty()
                                        select new
                                        {
                                            TeachingMaterialPoolID = (Guid)b.TeachingMaterialPoolID,
                                            DiscountPrice = b.DiscountPrice,
                                            // SchoolyearID = h.Key.SchoolyearID,
                                            InQty = b.InQty,
                                            OutQty = ccc.OutQty ?? 0 ,
                                        };
            var InAndOut = from b in StockInview.Concat(StockOutview)
                           group b by new { b.TeachingMaterialPoolID, b.DiscountPrice }
                           into aa
                           select new
                           {
                               TeachingMaterialPoolID = (Guid)aa.Key.TeachingMaterialPoolID,
                               DiscountPrice = aa.Key.DiscountPrice,
                               // SchoolyearID = h.Key.SchoolyearID,
                               InQty = aa.Sum(x => x.InQty),
                               OutQty = aa.Sum(x => x.OutQty),
                           };
            #endregion
            #endregion

            #region 4.0 对分组后的出入库数据跟教材库存做统计输出~
            var InventoryViews = from a in TeachingMaterialPoolRepository.Entities.Where(x => x.IsLate == false)
                                 join t in TeachingMateriaInventoryRepository.Entities on a.TeachingMaterialPoolID equals t.TeachingMaterialPoolID
                                 into tsct
                                 from tm in tsct.DefaultIfEmpty()
                                 join InByOut in InAndOut on new { tm.TeachingMaterialPoolID, DiscountPrice = tm.Discount } equals new { InByOut.TeachingMaterialPoolID, DiscountPrice = InByOut.DiscountPrice }
                                 into InByOutdata
                                 from InByOuttb in InByOutdata.DefaultIfEmpty()
                                 join oldstin in oldInAndOut on new { tm.TeachingMaterialPoolID, DiscountPrice = tm.Discount } equals new { oldstin.TeachingMaterialPoolID, DiscountPrice = oldstin.DiscountPrice }
                                 into toldstin
                                 from ctoldstin in toldstin.DefaultIfEmpty()
                                 //join oldin in oldStockIns on new { tm.TeachingMaterialPoolID, DiscountPrice = tm.Discount } equals new { oldin.TeachingMaterialPoolID, DiscountPrice = oldin.DiscountPrice }
                                 //join outs in oldStockOuts on new { tm.TeachingMaterialPoolID, DiscountPrice = tm.Discount } equals new { outs.TeachingMaterialPoolID, DiscountPrice = outs.DiscountPrice }
                                 //into sout 
                                 //from st in sout.DefaultIfEmpty()
                                 join p in PublishRepository.Entities on a.PublishID equals p.PublishID
                                 into pp
                                 from ppp in pp.DefaultIfEmpty()
                                 //join c in CoursematerialRepository.Entities on a.CoursematerialID equals c.CoursematerialID
                                 //join d in DictionaryItemRepository.Entities
                                 // on new { a.TeachingMaterialTypeID, DictionaryCode = DictionaryItem.CF_TeachingMaterialType.ToString() } equals new { TeachingMaterialTypeID = d.Value, d.DictionaryCode }
                                 //into dd
                                 //from ed in dd.DefaultIfEmpty()

                                 //join oldStockin in oldStockIns on tm.TeachingMaterialPoolID equals oldStockin.TeachingMaterialPoolID
                                 //into d
                                 //from dd in d.DefaultIfEmpty()
                                 //join oldStockout in oldStockOuts on tm.TeachingMaterialPoolID equals oldStockout.TeachingMaterialPoolID
                                 //into f
                                 //from ff in f.DefaultIfEmpty()

                                 // join oldout in oldStockOut.Concat(oldStudentDistribute) on tm.TeachingMaterialPoolID equals oldout.TeachingMaterialPoolID
                                 //into toldout
                                 // from ctoldout in toldout.DefaultIfEmpty()
                                 select new InventoryView
                                 {
                                     SchoolYearName = YearCode,
                                     TeachingMaterialPoolID = a.TeachingMaterialPoolID,
                                     // SchoolYearID = InByOuttb.SchoolyearID == null ? (Guid)schoolyearID : InByOuttb.SchoolyearID,
                                     PublishID = a.PublishID,
                                     TeachingMaterialTypeID = a.TeachingMaterialTypeID,
                                     TeachingMaterialCode = a.TeachingMaterialCode,
                                     TeachingMaterialName = a.TeachingMaterialName,
                                     CoursematerialEntityList = a.EM_Coursematerial,
                                     ISBN = a.ISBN,
                                     //TeachingMaterialTypeName = ed.Name,
                                     PublishTime = a.PublishTime,
                                     PublishName = ppp.UnitName,
                                     Author = a.Author,
                                     InventoryCollectQty = (tm.PresentInventory ?? 0) - (InByOuttb.InQty ?? 0) + (InByOuttb.OutQty ?? 0),
                                     ////StockInQty = InByOuttb.InQty == null ? 0 : InByOuttb.InQty,
                                     ////StockOutQty = InByOuttb.OutQty == null ? 0 : InByOuttb.OutQty,
                                     StockInQty = ctoldstin.oldInQty ?? 0,
                                     StockOutQty = ctoldstin.oldOutQty ?? 0,
                                     PresentInventory = tm.PresentInventory ?? 0,
                                     MinInventory = a.MinInventory == null ? 1 : a.MinInventory,
                                     Price = a.Price,
                                     Discount = (tm.Discount / a.Price) == null ? 1 : Math.Round((decimal)(tm.Discount / a.Price), 2),
                                     DiscountPrice = tm.Discount ?? 0,
                                     Total = (a.Price * tm.PresentInventory) == null ? 0 : (a.Price * tm.PresentInventory),
                                     TotalDollar = (tm.Discount * tm.PresentInventory) == null ? 0 : (tm.Discount * tm.PresentInventory)
                                 };
            #endregion

            return InventoryViews;

        }
        #endregion

        #region 3.0 出库明细信息列表
        public IQueryable<StockOutView> GetStockOutDetailGridView(Guid? schoolyearID, Guid? teachingMaterialPoolID)
        {
            int? yearValue = 0;
            if (schoolyearID != null)
            {
                yearValue = SchoolyearRepository.GetSingle(x => x.SchoolyearID == schoolyearID).Value;
            }
            string code = SchoolyearRepository.GetSingle(x => x.SchoolyearID == schoolyearID).Code;
            #region 1.0 出库

            var StockOutview = from a in StockOutRepository.Entities.Where(x => x.CF_Schoolyear.SchoolyearID == schoolyearID && x.RecordStatus == (int)CF_YesOrNoStatus.Yes)
                               join b in StockOutDetailRepository.Entities.Where(x => x.TeachingMaterialPoolID == teachingMaterialPoolID) on a.StockOutID equals b.StockOutID
                               select new
                               {
                                   TeachingMaterialPoolID = (Guid)b.TeachingMaterialPoolID,
                                   StockOutNo = a.StockOutNo,
                                   OutQty = (int)b.StockOutQuantity,
                                   StockOutTime = a.StockOutTime,
                                   StockOutType = (int)a.StockOutType,
                                   RecipientUserID = a.RecipientUserID == null ? Guid.Empty : a.RecipientUserID,
                                   QianShouRen = a.GetBookNo,
                                   DiscountPrice = b.Discount,//折合价
                                   Discount = b.DiscountPrice,//折扣率
                                   Desc = a.Desc,
                               };
            #endregion

            //#region 2.0 学生发放

            //var StudentDistributeview = from a in StudentDistributeRepository.Entities.Where(x => x.EM_SpecialtyPlan.CF_Schoolyear.SchoolyearID == schoolyearID && x.RecordStatus == (int)CF_YesOrNoStatus.Yes && x.TeachingMaterialPoolID == teachingMaterialPoolID)
            //                            select new
            //                            {
            //                                TeachingMaterialPoolID = (Guid)a.TeachingMaterialPoolID,
            //                                StockOutNo = a.StudentDistributeNo,
            //                                OutQty = (int)a.DistributeQty - a.CF_Student.Count(),
            //                                StockOutTime = a.ModifyTime,
            //                                StockOutType = (int)a.StockOutType,
            //                                RecipientUserID = a.ModifyUserID,
            //                                QianShouRen = a.RecipientUser,
            //                                Desc = a.Remark,
            //                            };
            //#endregion
            var query = from a in StockOutview
                        join c in UserRepository.Entities on a.RecipientUserID equals c.UserID
                             into cuser
                        from tcuser in cuser.DefaultIfEmpty()
                        join t in TeachingMaterialPoolRepository.Entities on a.TeachingMaterialPoolID equals t.TeachingMaterialPoolID
                        //join tp in TeachingMateriaInventoryRepository.Entities on a.TeachingMaterialPoolID equals  tp.TeachingMaterialPoolID
                        select new StockOutView
                        {
                            SchoolyearName = code,
                            StockOutNo = a.StockOutNo,
                            GetBookNo = a.QianShouRen.ToString(),
                            TeachingMaterialCode = t.TeachingMaterialCode,
                            TeachingMaterialName = t.TeachingMaterialName,
                            TeachingMaterialPoolID = t.TeachingMaterialPoolID,
                            PublishName = t.CF_Publish.UnitName,
                            PublishTime = t.PublishTime,
                            Author = t.Author,
                            Quantity = a.OutQty,
                            StockOutTypeID = a.StockOutType,//出库类型
                            Price = t.Price,
                            Discount = (a.Discount == null || a.Discount == 0) ? (a.DiscountPrice / t.Price) : a.Discount,
                            DiscountPrice = a.DiscountPrice,
                            TotalPrice = t.Price * a.OutQty,
                            TotalDollar = a.DiscountPrice * a.OutQty,
                            RecipientUserName = tcuser.Name == null ? a.QianShouRen : tcuser.Name,
                            StockOutTime = a.StockOutTime,
                            Desc = a.Desc
                        };

            return query;
        }

        public IQueryable<StockOutView> GetStudentDistributeDetailGridView(Expression<Func<ET_StudentDistribute, bool>> exporder)
        {
            var viewList = (from a in StudentDistributeRepository.GetList(exporder)

                            join t in TeachingMaterialPoolRepository.Entities on a.TeachingMaterialPoolID equals t.TeachingMaterialPoolID
                            into ct
                            from sct in ct.DefaultIfEmpty()
                            join tp in TeachingMateriaInventoryRepository.Entities on a.TeachingMaterialPoolID equals tp.TeachingMaterialPoolID
                            into ctp
                            from sctp in ctp.DefaultIfEmpty()
                            select new StockOutView
                            {
                                TeachingMaterialPoolID = (Guid)a.TeachingMaterialPoolID,
                                StockOutDetailID = Guid.Empty,
                                SchoolyearName = a.EM_SpecialtyPlan.CF_Schoolyear.Code,
                                SchoolyearID = a.EM_SpecialtyPlan.CF_Schoolyear.SchoolyearID,
                                StockOutNo = a.StudentDistributeNo,
                                GetBookNo = a.RecipientUser.ToString(),
                                TeachingMaterialCode = sct.TeachingMaterialCode,
                                TeachingMaterialName = sct.TeachingMaterialName,
                                PublishName = sct.CF_Publish.UnitName,
                                PublishTime = sct.PublishTime,
                                Author = sct.Author,
                                Quantity = a.DistributeQty,
                                Price = sct.Price,
                                Discount = sctp.Discount / sct.Price,
                                DiscountPrice = sctp.Discount,
                                TotalPrice = sct.Price * a.DistributeQty,
                                TotalDollar = (sct.Price * (sctp.Discount / sct.Price)) * a.DistributeQty,
                                RecipientUserName = a.RecipientUser.ToString(),
                                StockOutID = a.StudentDistributeID,
                                StockOutTime = a.ModifyTime,
                                Desc = a.Remark

                            });
            return viewList;
        }
        #endregion

        #region 4.0 入库明细信息列表
        public IQueryable<StockInView> GetStockInDetailGridView(Expression<Func<ET_StockIn, bool>> exporder)
        {
            var viewList = (from a in StockInRepository.GetList(exporder)
                            join b in SchoolyearRepository.Entities on a.SchoolyearID equals b.SchoolyearID
                            join c in UserRepository.Entities on a.HandlerUserID equals c.UserID
                            into cuser
                            from tcuser in cuser.DefaultIfEmpty()
                            join d in PublishRepository.Entities on a.SupplierID equals d.PublishID
                            into dTmp
                            from publishTab in dTmp.DefaultIfEmpty()
                            join e in StockInDetailRepository.Entities on a.StockInID equals e.StockInID
                            join t in TeachingMaterialPoolRepository.Entities on e.TeachingMaterialPoolID equals t.TeachingMaterialPoolID
                            select new StockInView
                            {
                                TeachingMaterialPoolID = (Guid)e.TeachingMaterialPoolID,
                                StockInDetailID = e.StockInDetailID,
                                SchoolyearName = b.Code,
                                SchoolyearID = (Guid)a.SchoolyearID,
                                SupplierID = a.SupplierID,
                                StockInDocumentNo = a.StockInDocumentNo,
                                StockInNumber = a.StockInNumber.ToString(),
                                TeachingMaterialCode = t.TeachingMaterialCode,
                                TeachingMaterialName = t.TeachingMaterialName,
                                PublishName = t.CF_Publish.UnitName,
                                PublishTime = t.PublishTime,
                                Author = t.Author,
                                Quantity = e.Quantity,
                                Price = t.Price,
                                Discount = e.Discount,
                                DiscountPrice = e.DiscountPrice,
                                TotalPrice = t.Price * e.Quantity,
                                TotalDollar = e.DiscountPrice * e.Quantity,
                                HandlerUserName = tcuser.Name == null ? "" : tcuser.Name,
                                StockInUserName = tcuser.Name == null ? "" : tcuser.Name,
                                StockInID = a.StockInID,
                                StockInTime = a.StockInTime
                            });
            return viewList;
        }
        #endregion

    }
}