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 { get; set; } #endregion #region 1.0 获取教材库存信息 /// /// 获取教材库存信息列表 /// /// /// public IQueryable GetTeachingMaterialInventoryGridView(Expression> exp, Expression> 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 GetTeachingMaterialInventoryCoursematerialView(Expression> 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 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 获取库存统计信息 /// /// 获取教材库存信息列表 /// /// /// public IQueryable 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 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 GetStudentDistributeDetailGridView(Expression> 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 GetStockInDetailGridView(Expression> 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 } }