JW/EMiS.DataLogic/Common/TeachingMaterial/StockOutDAL.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 StockOutDAL
    {
        #region --0.0 定义--
        public TeachersOrderRepository TeachersOrderRepository { get; set; }
        public StockOutRepository StockOutRepository { get; set; }
        public StockOutDetailRepository StockOutDetailRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public UserRepository UserRepository { get; set; }

        public TeachingMaterialPoolRepository TeachingMaterialPoolRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public PublishRepository PublishRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public DictionaryItemRepository DictionaryItemRepository { get; set; }
        public TeachingMateriaInventoryRepository TeachingMateriaInventoryRepository { get; set; }

        #endregion

        #region 2.0 查询信息列表
        public IQueryable<StockOutView> GetStockOutGridView(Expression<Func<ET_StockOut, bool>> exporder)
        {
            var viewList = (from a in StockOutRepository.Entities.Where(exporder)
                            join b in SchoolyearRepository.Entities on a.SchoolyearID equals b.SchoolyearID
                            join c in UserRepository.Entities on a.RecipientUserID equals c.UserID
                            into tuser
                            from ctuser in tuser.DefaultIfEmpty()
                            join f in UserRepository.Entities on a.ModifyUserID equals f.UserID
                            join e in StockOutDetailRepository.Entities on a.StockOutID equals e.StockOutID
                            join ct in DictionaryItemRepository.Entities on new { a.StockOutType, DictionaryCode = DictionaryItem.CF_StockOutType.ToString() }
                            equals new { StockOutType = (int)ct.Value, ct.DictionaryCode } into gbsta
                            from ctCourseType in gbsta.DefaultIfEmpty()
                            group new { a, b, ctuser, e, ctCourseType, f } by new
                            {
                                TypeName = ctCourseType.Name,
                                a.StockOutID,
                                a.SchoolyearID,
                                a.OutNumber,
                                b.Code,
                                a.GetBookNo,
                                a.StockOutType,
                                StockOutUserName = f.Name,
                                a.StockOutTime,
                                a.StockOutNo,
                                a.RecordStatus,
                                RecipientUserName = a.StockOutType == 5 ? a.GetBookNo : ctuser.Name,
                                a.Desc
                            } into h
                            select new StockOutView
                            {
                                StockOutID = h.Key.StockOutID,
                                SchoolyearID = h.Key.SchoolyearID,
                                GetBookNo = h.Key.GetBookNo,
                                StockOutNo = h.Key.StockOutNo,
                                SchoolyearName = h.Key.Code,
                                StockOutTypeID = h.Key.StockOutType,
                                StockOutSumMoney = h.Sum(x => x.e.Discount * x.e.StockOutQuantity),// * h.Sum(x => x.e.StockOutQuantity),
                                RecipientUserName = h.Key.RecipientUserName == null ? h.Key.GetBookNo : h.Key.RecipientUserName,
                                StockOutTime = h.Key.StockOutTime,
                                StockOutTypeName = h.Key.TypeName,
                                RecordStatus = h.Key.RecordStatus,
                                RecordStatusName = h.Key.RecordStatus == 1 ? "是" : "否",
                                StockOutUserName = h.Key.StockOutUserName,
                                Desc = h.Key.Desc
                            });
            return viewList;
        }
        #endregion

        #region 4.0 获取出库可编辑信息
        public StockOutView GetSingleStockOut(Guid stockOutID)
        {
            var stockOutDetail = StockOutDetailRepository.GetList(x => x.StockOutID == stockOutID).ToList();
            var query = from c in stockOutDetail
                        group c by new
                        {
                            c.StockOutID
                        }
                            into s
                            select new
                            {
                                Qty = s.Sum(x => x.StockOutQuantity)
                            };
            int qty = query.Select(x => x.Qty).FirstOrDefault().Value;

            var view = (from a in StockOutRepository.Entities.Where(x => x.StockOutID == stockOutID)
                        join b in SchoolyearRepository.Entities on a.SchoolyearID equals b.SchoolyearID
                        //join c in UserRepository.Entities on a.RecipientUserID equals c.UserID
                        //into tc
                        //from ctc in tc.DefaultIfEmpty()
                        select new StockOutView
                        {
                            RecordStatus = a.RecordStatus,
                            StockOutID = a.StockOutID,
                            StockOutTime = a.StockOutTime,
                            StockOutNo = a.StockOutNo,
                            StockOutTypeID = a.StockOutType,
                            GetBookNo = a.GetBookNo,
                            OutNumber = (int)a.OutNumber == null ? 1 : qty,
                            SchoolyearID = a.SchoolyearID,
                            RecipientUserID = a.RecipientUserID == null ? Guid.Empty : a.RecipientUserID,
                            Desc = a.Desc
                        }).FirstOrDefault();
            return view;
        }
        #endregion

        #region 5.0 验证出库单据号是否存在
        public bool IsExistStockOutNo(string stockOutNo)
        {
            bool IsExist = false;
            var Endt = StockOutRepository.GetSingle(x => x.StockOutNo == stockOutNo);
            if (Endt != null)
            {
                IsExist = true;
            }
            return IsExist;
        }
        #endregion

        #region 8.0 根据ID、加载出库信息
        public IList<TeachingMaterialPoolView> GetTeachingMaterialView(Guid stockOutID)
        {
            var view = (from a in StockOutRepository.Entities.Where(x => x.StockOutID == stockOutID)
                        join sr in StockOutDetailRepository.Entities on a.StockOutID equals sr.StockOutID
                        join l in TeachingMaterialPoolRepository.Entities on sr.TeachingMaterialPoolID equals l.TeachingMaterialPoolID
                        //join d in DictionaryItemRepository.Entities on l.TeachingMaterialTypeID equals d.Value
                        //into td
                        //from ctd in td.DefaultIfEmpty()
                        join u in UserRepository.Entities on l.CreateUserID equals u.UserID
                        join p in PublishRepository.Entities on l.PublishID equals p.PublishID
                        join tm in TeachingMateriaInventoryRepository.Entities on sr.TeachingMaterialPoolID equals tm.TeachingMaterialPoolID
                        select new TeachingMaterialPoolView
                        {
                            Author = l.Author,
                            ISBN = l.ISBN,
                            CoursematerialEntityList = l.EM_Coursematerial,
                            Price = l.Price,
                            TeachingMaterialCode = l.TeachingMaterialCode,
                            TeachingMaterialName = l.TeachingMaterialName,
                            TeachingMaterialShorName = l.TeachingMaterialShorName,
                            TeachingMaterialPoolID = l.TeachingMaterialPoolID,
                            TeachingMaterialTypeID = l.TeachingMaterialTypeID,
                            //TeachingMaterialTypeName = ctd.Name,
                            PublishID = l.PublishID,
                            PublishName = p.UnitName,
                            PublishTime = l.PublishTime,
                            CreateTime = l.CreateTime,
                            CreateUserName = u.Name,
                            Desc = l.Desc,
                            IsLate = l.IsLate.Value,
                            ModifyTime = l.ModifyTime,
                            IsLateName = l.IsLate == true ? "是" : "否",
                            OrderQty = sr.StockOutQuantity,
                            PresentInventory = tm.PresentInventory,
                            Discount = sr.DiscountPrice,
                            DiscountPrice = sr.Discount
                        }).ToList();
            return view;

        }
        #endregion

        #region 10.0 出库明细信息列表
        public IQueryable<StockOutView> GetStockInDetailGridView(Expression<Func<ET_StockOut, bool>> exporder)
        {
            var viewList = (from a in StockOutRepository.Entities.Where(exporder)
                            join b in SchoolyearRepository.Entities on a.SchoolyearID equals b.SchoolyearID
                            join c in UserRepository.Entities on a.CreateUserID equals c.UserID
                            join e in StockOutDetailRepository.Entities on a.StockOutID equals e.StockOutID
                            join t in TeachingMaterialPoolRepository.Entities on e.TeachingMaterialPoolID equals t.TeachingMaterialPoolID
                            select new StockOutView
                            {
                                StockOutDetailID = e.StockOutDetailID,
                                SchoolyearName = b.Code,
                                SchoolyearID = (Guid)a.SchoolyearID,
                                StockOutNo = a.StockOutNo,
                                GetBookNo = a.GetBookNo.ToString(),
                                TeachingMaterialCode = t.TeachingMaterialCode,
                                TeachingMaterialName = t.TeachingMaterialName,
                                PublishName = t.CF_Publish.UnitName,
                                PublishTime = t.PublishTime,
                                Author = t.Author,
                                Quantity = e.StockOutQuantity,
                                Price = t.Price,
                                Discount = e.Discount,
                                DiscountPrice = t.Price * e.Discount,
                                TotalDollar = (t.Price * e.Discount) * e.StockOutQuantity,
                                RecipientUserName = a.GetBookNo,
                                StockOutUserName = c.Name,
                                StockOutID = a.StockOutID,
                                StockOutTime = a.StockOutTime,
                                TotalPrice = t.Price * e.StockOutQuantity,
                                Desc = a.Desc

                            });
            return viewList;
        }
        #endregion

        #region 11.0 获取教材信息

        /// <summary>
        /// 获取教材信息
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<TeachingMaterialPoolView> GetTeachersOrderByTeachingMaterial(Expression<Func<CF_TeachingMaterialPool, bool>> exp)
        {
            //TeachingMateriaInventoryRepository
            var view = (from a in TeachingMateriaInventoryRepository.Entities
                        join l in TeachingMaterialPoolRepository.GetList(exp) on a.TeachingMaterialPoolID equals l.TeachingMaterialPoolID
                        //join d in DictionaryItemRepository.Entities on l.TeachingMaterialTypeID equals d.Value
                        //  into td
                        //from ctd in td.DefaultIfEmpty()
                        join u in UserRepository.Entities on l.CreateUserID equals u.UserID
                        join p in PublishRepository.Entities on l.PublishID equals p.PublishID 
                        group new {
                            TeachingMaterialPool = l,
                            //TeachingMaterialType = ctd,
                            TeachingMaterialInventory = a
                        } 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 = ctd.Name,
                            PublishID = l.PublishID,
                            PublishName = p.UnitName,
                            PublishTime = l.PublishTime,
                            CreateTime = l.CreateTime,
                            CreateUserName = u.Name,
                            Desc = l.Desc,
                            IsLate = l.IsLate.Value,
                            ModifyTime = l.ModifyTime,
                            IsLateName = l.IsLate == true ? "是" : "否",
                            //DiscountPrice = a.Discount,//折合价
                            //Discount = Math.Round((decimal)(a.Discount/l.Price),2),//折扣率
                        } into g
                        join tmp in TeachingMaterialPoolRepository.Entities on g.Key.TeachingMaterialPoolID equals tmp.TeachingMaterialPoolID
                        select new TeachingMaterialPoolView
                        {
                            Author = g.Key.Author,
                            ISBN = g.Key.ISBN,
                            CoursematerialEntityList = tmp.EM_Coursematerial,
                            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,
                            ModifyTime = g.Key.ModifyTime,
                            IsLateName = g.Key.IsLateName,
                            OrderQty = 1,
                            //Discount = 1,
                            DiscountPrice = g.Key.Price * g.OrderByDescending(x => x.TeachingMaterialInventory.CreateTime).FirstOrDefault().TeachingMaterialInventory.Discount,
                            //g.OrderByDescending(x => x.TeachingMaterialInventory.CreateTime).FirstOrDefault().TeachingMaterialInventory.DiscountPrice,//折合价
                            Discount = g.OrderByDescending(x => x.TeachingMaterialInventory.CreateTime).FirstOrDefault().TeachingMaterialInventory.Discount,
                            //Math.Round((decimal)((g.OrderByDescending(x => x.TeachingMaterialInventory.CreateTime).FirstOrDefault().TeachingMaterialPool.Price) == 0 ? 0 : ((g.OrderByDescending(x => x.TeachingMaterialInventory.CreateTime).FirstOrDefault().TeachingMaterialInventory.Discount) / (g.OrderByDescending(x => x.TeachingMaterialInventory.CreateTime).FirstOrDefault().TeachingMaterialPool.Price))), 2),//折扣率
                            PresentInventory = g.Sum(w => w.TeachingMaterialInventory.PresentInventory)
                        });
            return view;
        }
        #endregion

        #region 12.0 获取出库对应明细的需要的教材信息

        /// <summary>
        /// 获取出库对应明细的需要的教材信息
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IQueryable<TeachingMaterialPoolView> GetStockOutByTeachingMaterial(Expression<Func<CF_TeachingMaterialPool, bool>> exp)
        {
            //TeachingMateriaInventoryRepository
            var view = (from a in StockOutDetailRepository.Entities
                        join l in TeachingMaterialPoolRepository.Entities on a.TeachingMaterialPoolID equals l.TeachingMaterialPoolID
                        //join d in DictionaryItemRepository.Entities on l.TeachingMaterialTypeID equals d.Value
                        // into td
                        //from ctd in td.DefaultIfEmpty()
                        join u in UserRepository.Entities on l.CreateUserID equals u.UserID
                        join p in PublishRepository.Entities on l.PublishID equals p.PublishID
                        select new TeachingMaterialPoolView
                        {
                            Author = l.Author,
                            ISBN = l.ISBN,
                            CoursematerialEntityList = l.EM_Coursematerial,
                            Price = l.Price,
                            TeachingMaterialCode = l.TeachingMaterialCode,
                            TeachingMaterialName = l.TeachingMaterialName,
                            TeachingMaterialShorName = l.TeachingMaterialShorName,
                            TeachingMaterialPoolID = l.TeachingMaterialPoolID,
                            TeachingMaterialTypeID = l.TeachingMaterialTypeID,
                            //TeachingMaterialTypeName = ctd.Name,
                            PublishID = l.PublishID,
                            PublishName = p.UnitName,
                            PublishTime = l.PublishTime,
                            CreateTime = l.CreateTime,
                            CreateUserName = u.Name,
                            Desc = l.Desc,
                            IsLate = l.IsLate.Value,
                            ModifyTime = l.ModifyTime,
                            IsLateName = l.IsLate == true ? "是" : "否",
                            OrderQty = 1,
                            Discount = 1
                        });
            return view;
        }
        #endregion

        #region 13.0 验证输入的出库数量是否大于当前库存量
        public bool CheckInventoryQty(int Qty, Guid teachingMaterialPoolID)
        {
            bool IsExist = false;
            int? presentInventory = TeachingMateriaInventoryRepository.GetSingle(x => x.TeachingMaterialPoolID == teachingMaterialPoolID).PresentInventory;
            if (Qty > presentInventory)
            {
                IsExist = true;
            }
            return IsExist;
        }
        #endregion
    }
}