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 GetStockOutDetailGridView(Expression> 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 GetStockOutStatisticsGridView(Expression> 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 } }