JW/EMiS.DataLogic/Common/TeachingMaterial/StockOutDetailDAL.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 StockOutDetailDAL
    {
        #region --0.0 定义--
        public StockOutRepository StockOutRepository { get; set; }
        public StockOutDetailRepository StockOutDetailRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public StudentDistributeDAL StudentDistributeDAL { 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; }
        public StudentDistributeRepository StudentDistributeRepository { get; set; }
        public LibraryRepository LibraryRepository { get; set; }
        #endregion
        #region 1.0 信息列表
        public IQueryable<StockOutView> GetStockOutDetailGridView(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 sd in UserRepository.Entities on a.RecipientUserID equals sd.UserID
                            into cuser
                            from tcuser in cuser.DefaultIfEmpty()
                            join e in StockOutDetailRepository.Entities on a.StockOutID equals e.StockOutID
                            //出库详情的书库ID均为空用作关联条件导致查询结果为空
                            //join lib in LibraryRepository.Entities on e.LibraryID equals lib.LibraryID into libName
                            //from name in libName
                            join t in TeachingMaterialPoolRepository.Entities on e.TeachingMaterialPoolID equals t.TeachingMaterialPoolID
                            join sct in DictionaryItemRepository.Entities on new { StockInType = a.StockOutType, DictionaryCode = DictionaryItem.CF_StockOutType.ToString() }
                            equals new { StockInType = (int)sct.Value, sct.DictionaryCode } into tsct
                            from csct in tsct.DefaultIfEmpty()   //教材  折合价相同  即为一条
                            join tm in TeachingMateriaInventoryRepository.Entities on new { e.TeachingMaterialPoolID, DiscountPrice = e.Discount } equals new { tm.TeachingMaterialPoolID, DiscountPrice = tm.DiscountPrice }
                            into sttm
                            from csttm in sttm.DefaultIfEmpty()
                            select new StockOutView
                            {
                                //书库名称并没有用到，直接删除
                                //LibraryName = name.LibraryName,
                                StockOutDetailID = e.StockOutDetailID,
                                SchoolyearName = b.Code,
                                SchoolyearID = (Guid)a.SchoolyearID,
                                StockOutNo = a.StockOutNo + "",
                                TeachingMaterialCode = t.TeachingMaterialCode,
                                TeachingMaterialName = t.TeachingMaterialName,
                                PublishName = t.CF_Publish.UnitName,
                                PublishTime = t.PublishTime,
                                Author = t.Author,
                                RecordStatusName = csct.Name,
                                Quantity = e.StockOutQuantity,
                                Price = t.Price,
                                Discount = e.DiscountPrice,//折扣率
                                DiscountPrice = e.Discount,//折合价
                                TotalPrice = t.Price * e.StockOutQuantity,
                                TotalDollar = e.Discount * e.StockOutQuantity,
                                RecipientUserName = a.StockOutType == (int)CF_StockOutType.StudentBook ? tcuser.Name : a.GetBookNo,
                                StockOutUserName = c.Name,
                                StockOutID = a.StockOutID,
                                StockOutTime = a.StockOutTime,
                                GetBookNo = a.GetBookNo == null ? tcuser.Name : a.GetBookNo,
                                RecordStatus = a.RecordStatus,
                                Desc = a.Desc
                            });
            return viewList;
        }
        #endregion

        #region 2.0 加载出库统计列表
        public IQueryable<StockOutView> GetStockOutStatisticsGridView(Expression<Func<ET_StockOut, bool>> exporder)
        {
            //var studentList = from a in StudentDistributeRepository.Entities.Where(x => x.StockOutType == (int)CF_StockOutType.StudentPutOut && x.RecordStatus == (int)CF_GeneralPurpose.IsYes)
            //                  join kc in TeachingMateriaInventoryRepository.Entities on a.TeachingMaterialPoolID equals kc.TeachingMaterialPoolID into kctmp
            //                  from kcmp in kctmp.DefaultIfEmpty()
            //                  select new
            //                  {
            //                      TeachingMaterialPoolID = (Guid)a.TeachingMaterialPoolID,
            //                      SchoolyearID = (Guid)a.EM_SpecialtyPlan.SchoolyearID,
            //                      Quantity = (int)a.DistributeQty - a.CF_Student.Count(),
            //                      StockOutType = (int)a.StockOutType
            //                  };

            var stockOutList = from a in StockOutRepository.Entities.Where(x => x.RecordStatus == (int)CF_GeneralPurpose.IsYes)
                               join e in StockOutDetailRepository.Entities on a.StockOutID equals e.StockOutID
                               select new
                               {
                                   TeachingMaterialPoolID = (Guid)e.TeachingMaterialPoolID,
                                   SchoolyearID = (Guid)a.SchoolyearID,
                                   Quantity = (int)e.StockOutQuantity,
                                   StockOutType = (int)a.StockOutType,
                                   DiscountPrice = e.Discount,
                                   Discount = e.DiscountPrice
                               };
            var viewList = (from a in stockOutList
                            join b in SchoolyearRepository.Entities on a.SchoolyearID equals b.SchoolyearID
                            //   join c in UserRepository.Entities on a.RecipientUserID equals c.UserID
                            join t in TeachingMaterialPoolRepository.Entities on a.TeachingMaterialPoolID equals t.TeachingMaterialPoolID
                            join d in PublishRepository.Entities on t.PublishID equals d.PublishID
                            join sct in DictionaryItemRepository.Entities on new { StockInType = a.StockOutType, DictionaryCode = DictionaryItem.CF_StockOutType.ToString() }
                            equals new { StockInType = (int)sct.Value, sct.DictionaryCode } into tsct
                            from csct in tsct.DefaultIfEmpty()
                            //join kc in TeachingMateriaInventoryRepository.Entities on a.TeachingMaterialPoolID equals kc.TeachingMaterialPoolID into kctmp
                            //from kcmp in kctmp.DefaultIfEmpty()
                            group new { b, t, a, d, csct } by
                          new
                          {
                              //kcmp.Discount,//折合价
                              a.StockOutType,
                              a.Discount,//折扣率
                              a.DiscountPrice,//折合价
                              StockOutTypeName = csct.Name,
                              a.SchoolyearID,
                              b.Code,
                              d.UnitName,
                              t.ISBN,
                              t.PublishID,
                              t.PublishTime,
                              t.Author,
                              t.TeachingMaterialCode,
                              t.TeachingMaterialName,
                              t.TeachingMaterialTypeID,
                              t.TeachingMaterialPoolID,
                              t.Price,
                          } into h
                            select new StockOutView
                            {
                                StockOutTypeName = h.Key.StockOutTypeName,
                                StockOutTypeID = h.Key.StockOutType,
                                //RecordStatusName = h.Key.Name,
                                SchoolyearName = h.Key.Code,
                                SchoolyearID = h.Key.SchoolyearID,
                                TeachingMaterialPoolID = h.Key.TeachingMaterialPoolID,
                                SupplierID = h.Key.PublishID,
                                TeachingMaterialCode = h.Key.TeachingMaterialCode,
                                TeachingMaterialName = h.Key.TeachingMaterialName,
                                ISBN = h.Key.ISBN,
                                PublishName = h.Key.UnitName,
                                SupplierName = h.Key.UnitName,
                                PublishTime = h.Key.PublishTime,
                                Author = h.Key.Author,
                                Quantity = h.Sum(x => x.a.Quantity),
                                Price = h.Key.Price,
                                Discount = h.Key.Discount, // h.Key.Price,//折扣率=折合价/单价
                                DiscountPrice = h.Key.DiscountPrice,
                                TotalPrice = h.Key.Price * h.Sum(x => x.a.Quantity),
                                TotalDollar = h.Key.DiscountPrice * h.Sum(x => x.a.Quantity) == null ? 0 : h.Key.DiscountPrice * h.Sum(x => x.a.Quantity)
                                //StockOutUserName = 
                                //StockOutTime =
                            });
            return viewList;
        }
        #endregion
    }
}