using System; using System.Collections.Generic; using System.Linq; using System.Web; using Warehouse.DAL.StockIn; using Warehouse.Models.whMS; using Z.EntityFramework.Plus; namespace Warehouse.DAL.ReturnStock { public class ReturnStockMSDal { #region 托盘号退库 public int ReturnStockPallet(string Pallet) { using (var context = new whMSModel()) { long MaxId = context.tstockinlogs.Where(a => a.palletno == Pallet).Max(a => a.id); return context.tstockinlogs.Where(a => a.palletno == Pallet && a.id == MaxId).Update(x => new tstockinlog() { status = -1 }); } } #endregion public int SetStockCondictionStatus(int StockLoaction, string Status) { using (var context = new whMSModel()) { return context.tlocationconditions.Where(a => a.storagelocationid == StockLoaction).Update(x => new tlocationcondition() { status = Status }); } } public int QueryLocationQty(int StockLocation) { using (var context = new whMSModel()) { return context.tstockinlogs.Where(a => a.storagelocationid == StockLocation && a.status == 2).Count(); } } //退库记录 public void ReturnStockLog(List ListLog) { using (var context = new whMSModel()) { //entity extension 有过期问题 放弃使用 //context.BulkInsert(ListLog); context.Configuration.AutoDetectChangesEnabled = false; context.treturnstocklogs.AddRange(ListLog); context.SaveChanges(); } } //托盘入库信息 public IEnumerable QueryStockInfoByPallet(string pallet) { using (var context = new whMSModel()) { // string sql = @"select a.palletno,a.storagelocationid ,a.createtime,b.locationname,a.storagelocationconditionid,a.status from wh.dbo.tstockinlog a left join wh.dbo.tstoragelocation b on a.storagelocationid = b.id //where a.id = (select max(id) from tstockinlog where palletno = '" + pallet + "' )"; //context.Database.SqlQuery(" call prGetPalletOfContainer('" + container + "') ").ToList(); //var res1 = context.DynamicListFromSql(sql, null); var res = from a in context.tstockinlogs join b in context.tstoragelocations on a.storagelocationid equals b.id from c in context.tcontainerpallets.Where(c => c.palletno == a.palletno).DefaultIfEmpty() where (a.id == context.tstockinlogs.Where(a => a.palletno == pallet).Max(a => a.id)) select new { a.palletno, a.storagelocationid, a.createtime, b.locationname, a.storagelocationconditionid, a.status, container = c.containerno ?? "" }; if (res != null) { return res.ToList(); } else { return null; } } } public IEnumerable QueryStockInfoByContainer(string Container) { using (var context = new whMSModel()) { var res = from a in context.tstockinlogs join b in context.tstoragelocations on a.storagelocationid equals b.id from c in context.tcontainerpallets.Where(c => c.palletno == a.palletno).DefaultIfEmpty() where (a.id == context.tstockinlogs.Where(a => a.palletno == c.palletno && c.containerno == Container).Max(a => a.id)) select new { a.palletno, a.storagelocationid, a.createtime, b.locationname, a.storagelocationconditionid, a.status, container = c.containerno ?? "" }; if (res != null) { return res.ToList(); } else { return null; } } } public IEnumerable QueryLocationInfo(int StockLocation) { using (var context = new whMSModel()) { var res = from a in context.tstockinlogs join b in context.tstoragelocations on a.storagelocationid equals b.id //join c in context.tcontainerpallets on a.palletno equals c.palletno from c in context.tcontainerpallets.Where(cp => cp.palletno == a.palletno).DefaultIfEmpty() where (a.storagelocationid == StockLocation && a.status == 2) select new { a.palletno, a.storagelocationid, a.createtime, b.locationname, a.storagelocationconditionid, a.status, container = c.containerno ?? "" }; return res.ToList(); } } //柜号移库 //1、新库位库存为0时条件为旧库位条件 //2、更改托盘库位id //3、判断旧库位条件是否需要清空 //4、移库日志 public void MoveContainer(string Container, int NewLocation) { using (var context = new whMSModel()) { //柜号入库信息 var res = from a in context.tcontainerpallets from b in context.tstockinlogs.Where(b => b.palletno == a.palletno).DefaultIfEmpty() from c in context.tstoragelocations.Where(c => c.id == b.storagelocationid).DefaultIfEmpty() where (b.id == context.tstockinlogs.Where(d => d.palletno == a.palletno).Max(d => d.id) && a.containerno == Container) select b; int OldLocation = res.First().storagelocationid; //res.Update(x => new tstockinlog { storagelocationid = 999 }); //1 if (!context.tstockinlogs.Any(a => a.storagelocationid == NewLocation && a.status == 2)) { var OldLocationCondiction = context.tlocationconditions.Where(a => a.storagelocationid == OldLocation && a.status == "Y"); var OC = OldLocationCondiction.First(); OC.storagelocationid = NewLocation; context.tlocationconditions.Add(OC); context.SaveChanges(); } //2 long CondictionId = context.tlocationconditions.Where(a => a.storagelocationid == NewLocation && a.status == "Y").FirstOrDefault().id; res.Update(x => new tstockinlog() { storagelocationid = NewLocation, storagelocationconditionid = CondictionId }); //3 if (!context.tstockinlogs.Any(a => a.storagelocationid == OldLocation && a.status == 2)) { context.tlocationconditions.Where(a => a.storagelocationid == OldLocation).Update(x => new tlocationcondition() { status = "N" }); } //4 List LogList = new List(); foreach (var item in res.ToList()) { ///item.palletno tmovestocklog MoveLog = new tmovestocklog(); MoveLog.pallet = item.palletno; MoveLog.oldstoragelocationid = OldLocation; MoveLog.newstoragelocationid = NewLocation; MoveLog.createtime = System.DateTime.Now; MoveLog.createuser = System.Web.HttpContext.Current.Request.Cookies["userid"].Value.ToString(); //入库记录id MoveLog.stockinlogid = item.id; LogList.Add(MoveLog); } //context.tmovestocklogs.BulkInsert(LogList); context.Configuration.AutoDetectChangesEnabled = false; context.tmovestocklogs.AddRange(LogList); context.SaveChanges(); } } //托盘移库 //1、新库位库存为0时条件为旧库位条件 //2、更改托盘库位id 从旧库位出库,入库到新的库位 modify by xue lei on 2019-10-31 //3、判断旧库位条件是否需要清空 //4、移库日志 public void MovePallet(string Pallet, int NewLocation) { using (var context = new whMSModel()) { //1 long CondictionId = 0; var PalletStockInfo = context.tstockinlogs.Where(a => a.palletno == Pallet && a.id == context.tstockinlogs.Where(b => b.palletno == Pallet).Max(c => c.id)); int OldLocation = PalletStockInfo.Select(a => a.storagelocationid).First(); if (context.tstockinlogs.Where(a => a.storagelocationid == NewLocation && a.status == 2).Count() == 0) { var OldLocationCondiction = context.tlocationconditions.Where(a => a.storagelocationid == OldLocation && a.status == "Y"); var OC = OldLocationCondiction.First(); OC.storagelocationid = NewLocation; context.tlocationconditions.Add(OC); context.SaveChanges(); } CondictionId = context.tlocationconditions.Where(a => a.storagelocationid == NewLocation && a.status == "Y").FirstOrDefault().id; //2 //PalletStockInfo.Update(x => new tstockinlog() { storagelocationid = NewLocation, storagelocationconditionid = CondictionId }); //2.1入库记录改为退库状态 PalletStockInfo.Update(x => new tstockinlog() { status = -1 }); //2.2退库记录 treturnstocklog ReturnLog = new treturnstocklog { pallet = Pallet, storagelocationid = OldLocation, createtime = System.DateTime.Now, createuser = System.Web.HttpContext.Current.Request.Cookies["userid"].Value, stockinlogid = PalletStockInfo.FirstOrDefault().id }; context.treturnstocklogs.Add(ReturnLog); //2.3退库出库记录 tstockoutlog StockOutLog = new tstockoutlog { palletno = Pallet, createtime = System.DateTime.Now, createuser = System.Web.HttpContext.Current.Request.Cookies["userid"].Value, stockinlogid = PalletStockInfo.FirstOrDefault().id, stockouttype = 3,//移库出库 storagelocationid = PalletStockInfo.FirstOrDefault().storagelocationid }; context.tstockoutlogs.Add(StockOutLog); //2.4入库 Models.whMS.tstockinlog stockinloginfo = new Models.whMS.tstockinlog(); stockinloginfo.palletno = Pallet; stockinloginfo.status = 2;//2代表已经入库 1代表已经申请入库 stockinloginfo.storagelocationid = NewLocation; stockinloginfo.storagelocationconditionid = CondictionId; stockinloginfo.createtime = System.DateTime.Now; stockinloginfo.createuser = System.Web.HttpContext.Current.Request.Cookies["userid"].Value.ToString(); context.tstockinlogs.Add(stockinloginfo); //3 if (context.tstockinlogs.Where(a => a.storagelocationid == OldLocation && a.status == 2).Count() == 0) { context.tlocationconditions.Where(a => a.storagelocationid == OldLocation).Update(x => new tlocationcondition() { status = "N" }); } //4 tmovestocklog MoveLog = new tmovestocklog(); MoveLog.pallet = Pallet; MoveLog.oldstoragelocationid = OldLocation; MoveLog.newstoragelocationid = NewLocation; MoveLog.createtime = System.DateTime.Now; MoveLog.createuser = System.Web.HttpContext.Current.Request.Cookies["userid"].Value.ToString(); //入库记录id MoveLog.stockinlogid = PalletStockInfo.First().id; context.tmovestocklogs.Add(MoveLog); context.SaveChanges(); } } //新旧库位条件是否一致 public string CheckOldLocationMoveToNewLocation(int OldLocationCondictionId, int NewLocation) { using (var context = new whMSModel()) { var Old = context.tlocationconditions.Where(a => a.id == OldLocationCondictionId).FirstOrDefault(); var New = context.tlocationconditions.Where(a => a.storagelocationid == NewLocation && a.status == "Y").FirstOrDefault(); if (Old == null) { return "旧库位没有入库条件"; } if (New == null) { return "success"; } if (Old.powergrade != New.powergrade) return "旧库位与新库位的功率不一致"; if (Old.igrade != New.igrade) return "旧库位与新库位的电流不一致"; if (Old.color != New.color) return "旧库位与新库位的颜色不一致"; if (Old.finalgrade != New.finalgrade) return "旧库位与新库位的最终等级不一致"; if (Old.cellqty != New.cellqty) return "旧库位与新库位的电池数量不一致"; if (Old.bartype != New.bartype) return "旧库位与新库位的晶体类型不一致"; if (Old.modulespec != New.modulespec) return "旧库位与新库位的组件规格不一致"; return "success"; } } //出库记录 add by xue lei on 2019-10-31 public void StockOutLog(List ListLog) { using (var context = new whMSModel()) { //entity extension 有过期问题 放弃使用 //context.BulkInsert(ListLog); context.Configuration.AutoDetectChangesEnabled = false; context.tstockoutlogs.AddRange(ListLog); context.SaveChanges(); } } } }