using SqlSugar; using System; using System.Collections.Generic; using System.Linq; using wms.sqlsugar.model.sx; using Wms.Screen.DataService.Interface; using Wms.Screen.Dto.ZhongTian; using Wms.Screen.Dto.ZhongTian.Request; using Wms.Screen.Dto.ZhongTian.Response; using Wms.Screen.SqlSugar.ZhongTian; using Wms.Screen.SqlSugar.ZhongTian.SX; using BillInvnow = wms.sqlsugar.model.sx.BillInvnow; using WCS_TaskOld = wms.sqlsugar.model.sx.WCS_TaskOld; namespace Wms.Screen.DataService.Impl { public class ZhongTianSxDataService : IZhongTianSxDataService { public static string[] Grades = new string[] { "C", "D" }; private SqlSugarClient _db; public ZhongTianSxDataService(SqlSugarClient db) { _db = db; } public List GetReverseErrorInfo() { throw new NotImplementedException(); } public List GetSxEquips(GetEquipsRequest reqEntity) { var list = _db.Queryable().With(SqlWith.NoLock).Where(p => p.Status != (int)TaskStatus.Finish && p.Status != (int)TaskStatus.Cancel && !string.IsNullOrEmpty(p.Device)) .SplitTable(tabs => tabs.Take(2))?.ToList(); return list ?? new List(); } public List GetSxLocationUsageReportList() { int _sort = 1; var list = _db.Queryable((warecell, warehouse) => new object[] { JoinType.Left,warecell.WarehouseId == warehouse.Id }) .GroupBy((warecell, warehouse) => new { warecell.StateNum, warecell.IsStop, warecell.Tunnel, warecell.Floor, warehouse.Id, warehouse.Name }).Select((warecell, warehouse) => new { Status = warecell.StateNum, warecell.Tunnel, warecell.IsStop, HouseId = warehouse.Id, Floor = warecell.Floor, warehouse.Name, Total = SqlFunc.AggregateCount(warecell.Tunnel), }) .MergeTable().ToList().OrderBy(o => o.Tunnel); //基表：巷道、货架标识和仓库名称为维度查找总货位 var baseTemp = list.GroupBy(s => new { s.Tunnel, s.HouseId, s.Name, s.Floor }).Select(m => new { m.Key.Name, m.Key.Tunnel, m.Key.Floor, Total = m.Sum(n => n.Total) }).ToList(); //关联表：巷道和仓库名称为维度，查找可用货位：未停用.已锁定也算有效货位 var useLocation = list.Where(s => s.IsStop == 0).GroupBy(s => new { s.Tunnel, s.HouseId, s.Name, s.Floor }).Select(m => new { m.Key.Name, m.Key.Tunnel, m.Key.Floor, Total = m.Sum(n => n.Total) }).ToList(); //关联表：巷道和仓库名称为维度，查找锁定货位 var lockLocation = list.Where(s => s.Status == (int)SXLocationState.LocationState_StockIn || s.Status == (int)SXLocationState.LocationState_StockOut || s.Status == (int)SXLocationState.LocationState_StockMove).GroupBy(s => new { s.Tunnel, s.HouseId, s.Name, s.Floor }).Select(m => new { m.Key.Name, m.Key.Tunnel, m.Key.Floor, Total = m.Sum(n => n.Total) }).ToList(); //关联表：巷道和仓库名称为维度，查找停用货位 var stopLocation = list.Where(s => s.IsStop == 1).GroupBy(s => new { s.Tunnel, s.HouseId, s.Name, s.Floor }).Select(m => new { m.Key.Name, m.Key.Tunnel, m.Key.Floor, Total = m.Sum(n => n.Total) }).ToList(); //关联表：巷道和仓库名称为维度，查找物料货位 var materialLocation = list.Where(s => s.Status == (int)SXLocationState.LocationState_Full).GroupBy(s => new { s.Tunnel, s.HouseId, s.Name, s.Floor }).Select(m => new { m.Key.Name, m.Key.Tunnel, m.Key.Floor, Total = m.Sum(n => n.Total) }).ToList(); var locationUsageReportViewDtos = from basetemp in baseTemp join uselocation in useLocation on new { basetemp.Tunnel, basetemp.Name, basetemp.Floor } equals new { uselocation.Tunnel, uselocation.Name, uselocation.Floor } into useinfo join locklocation in lockLocation on new { basetemp.Tunnel, basetemp.Name, basetemp.Floor } equals new { locklocation.Tunnel, locklocation.Name, locklocation.Floor } into lockinfo from lockinfoif in lockinfo.DefaultIfEmpty() join stoplocation in stopLocation on new { basetemp.Tunnel, basetemp.Name, basetemp.Floor } equals new { stoplocation.Tunnel, stoplocation.Name, stoplocation.Floor } into stopinfo from stopinfoif in stopinfo.DefaultIfEmpty() join materiallocation in materialLocation on new { basetemp.Tunnel, basetemp.Name, basetemp.Floor } equals new { materiallocation.Tunnel, materiallocation.Name, materiallocation.Floor } into materialinfo from materialinfoif in materialinfo.DefaultIfEmpty() orderby basetemp.Name select new LocationUsageReportViewDto() { Sort = _sort++, Tunnel = basetemp.Tunnel.ToString(), Floor = basetemp.Floor.ToString(), WarehouseName = basetemp.Name, AllLocationTotal = basetemp.Total, CanUseLocation = useinfo.FirstOrDefault() == null ? 0 : useinfo.First().Total, LockLocation = lockinfoif == null ? 0 : lockinfoif.Total, StopLocation = stopinfoif == null ? 0 : stopinfoif.Total, MaterilLocation = materialinfoif == null ? 0 : materialinfoif.Total }; return locationUsageReportViewDtos.OrderBy(p => p.WarehouseName).ToList(); } public List GetSxProductPackRule(GetWorkPlanBillListRequest reqEntity) { var list = _db.Queryable().Where(p => p.TorsChkChordIsGood == "NG" || p.TorsChkFlatnessIsGood == "NG" || p.TorsChkValueIsGood == "NG") .Select((p) => new ProductPackRule() { BoxNo = p.CLBarCode, HWSpec = p.HWTypeCode, //EnchaseState = p.ZXStateCode, //PackRule = p.PackRule, //FullCount = product.FullCountQty, //EmptyCount = product.EmptyCountQty, //IsSolder = product.IsSolder.ToString(), }).ToList(); return list; } public List GetSxRunWcsTaskInfo(GetWcsTaskInfoRequest request) { var listin = _db.Queryable((wcstask, stock, math) => new object[] { JoinType.Left, wcstask.BarCode == stock.ContGrpBarCode, JoinType.Left, wcstask.BarCode == stock.ContGrpBarCode, JoinType.Left, wcstask.BarCode == math.ContBarCode, }).With(SqlWith.NoLock) .Where((wcstask, stock, math) => wcstask.Status < TaskStatus.Finish && wcstask.Status >= TaskStatus.帘线扫码 && wcstask.Type == TaskType.EnterDepot) .OrderBy((wcstask, stock, math) => wcstask.AddTime, OrderByType.Desc) .Select((wcstask, stock, math) => new SXTaskInfoDto { TaskCode = wcstask.ID.ToString(), TaskType = wcstask.Type == TaskType.EnterDepot ? "入库" : wcstask.Type == TaskType.OutDepot ? "出库" : wcstask.Type == TaskType.TransferDepot ? "移库" : "移动", TaskStateEnum = wcstask.Status, HWBarCode = wcstask.BarCode, HWSpec = stock.HWTypeCode, SKU = math.SkuCode, Layer = math.Layer, BeginPosition = wcstask.AddrFrom, EndPosition = wcstask.AddrTo, DocCode = stock.InDocsNo, CreatedTime = wcstask.AddTime, AddTime = wcstask.AddTime.ToString(), BoxBarCode = "", BusType = wcstask.BusType, Floor = wcstask.Floor, Grade = stock.Grade, MatCode = wcstask.MaterialCode, MaterialCode = wcstask.MaterialCode, Tunnel = wcstask.Tunnel, NextPosition = wcstask.AddrNext, }).Distinct().ToList(); var listout = _db.Queryable((wcstask, stock, math) => new object[] { JoinType.Left, wcstask.BarCode == stock.ContGrpBarCode, JoinType.Left, wcstask.BarCode == stock.ContGrpBarCode, JoinType.Left, wcstask.BarCode == math.ContBarCode, }).With(SqlWith.NoLock) .Where((wcstask, stock, math) => wcstask.Status < TaskStatus.Finish && wcstask.Type == TaskType.OutDepot) .OrderBy((wcstask, stock, math) => wcstask.AddTime, OrderByType.Desc) .Select((wcstask, stock, math) => new SXTaskInfoDto { TaskCode = wcstask.ID.ToString(), TaskType = wcstask.Type == TaskType.EnterDepot ? "入库" : wcstask.Type == TaskType.OutDepot ? "出库" : wcstask.Type == TaskType.TransferDepot ? "移库" : "移动", TaskStateEnum = wcstask.Status, HWBarCode = wcstask.BarCode, HWSpec = stock.HWTypeCode, SKU = math.SkuCode, Layer = math.Layer, BeginPosition = wcstask.AddrFrom, EndPosition = wcstask.AddrTo, DocCode = stock.InDocsNo, CreatedTime = wcstask.AddTime, AddTime = wcstask.AddTime.ToString(), BoxBarCode = "", BusType = wcstask.BusType, Floor = wcstask.Floor, Grade = stock.Grade, MatCode = wcstask.MaterialCode, MaterialCode = wcstask.MaterialCode, Tunnel = wcstask.Tunnel, NextPosition = wcstask.AddrNext }).Distinct().ToList(); var list = listin.Union(listout).ToList(); foreach (var item in list) { item.TaskState = Enum.GetName(typeof(TaskStatus), item.TaskStateEnum); } return list.ToList(); } public List GetExceptionStockInfo() { var lists = _db.Queryable((billInvnow, warecell, warehouse) => new object[] { JoinType.Left,billInvnow.ContGrpId == warecell.ContGrpId && billInvnow.WarehouseId ==warecell.WarehouseId , JoinType.Left, billInvnow.WarehouseId==warehouse.Id}) .Where((billInvnow, warecell, warehouse) => billInvnow.TorsChkChordIsGood.Equals("NG") || billInvnow.TorsChkFlatnessIsGood.Equals("NG") || billInvnow.TorsChkValueIsGood.Equals("NG")) .Where((billInvnow, warecell, warehouse) => Grades.Contains(billInvnow.Grade)) .Select((billInvnow, warecell, warehouse) => new SXStockDto { ContGrpBarCode = billInvnow.ContGrpBarCode, Code = warecell.Code, Tunnel = warecell.Tunnel, BoxBarCode = billInvnow.BoxBarCode, ContGrpType = billInvnow.ContGrpType, SkuCode = billInvnow.SkuCode, InvStateCode = billInvnow.InvStateCode, MatCode = billInvnow.MatCode, Grade = billInvnow.Grade, HWTypeCode = billInvnow.HWTypeCode, }).Take(200).ToList(); return lists; } public List GetPalletizingTasks() { var predicate = Expressionable.Create(); predicate = predicate.And(m => m.Status < 30); predicate = predicate.And(m => SqlFunc.Subqueryable().Where(s => s.Id == m.PalletizingId && s.PalletizState != 1).Any()); var list = _db.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()).ToList(); return list; } /// /// 获取扭转统计信息 /// /// public TorStaticsInfo GetTorStaticsInfo() { var result = new TorStaticsInfo(); //获取库中扭转不合格盘 result.TorNoGood = _db.Queryable((billInvnow, warecell) => new object[] { JoinType.Inner,billInvnow.ContGrpId == warecell.ContGrpId }).With(SqlWith.NoLock) .Where((billInvnow, warecell) => billInvnow.TorsChkChordIsGood.Equals("NG") || billInvnow.TorsChkFlatnessIsGood.Equals("NG") || billInvnow.TorsChkValueIsGood.Equals("NG")) .Where((billInvnow, warecell) => billInvnow.InvStateCode == "InvEcecState_In" && !billInvnow.AddWho.Contains("测试")) .Count(); var TorschkDate = _db.Queryable().Where(p => p.Code == "TorsChkDateCount").First(); var time1 = decimal.Parse(TorschkDate.SContent); var time2 = decimal.Parse(TorschkDate.Default1); var startTime = DateTime.Now.Date.AddDays(-1).AddMilliseconds(-1); //库中满足时效待检测盘 result.TorTobe = _db.Queryable((billInvnow, warecell) => new object[] { JoinType.Inner,billInvnow.ContGrpId == warecell.ContGrpId }).With(SqlWith.NoLock) .Where((billInvnow, warecell) => billInvnow.IsTorsChk == true) .Where((billInvnow, warecell) => billInvnow.InvStateCode == "InvEcecState_In" && !billInvnow.ProductMachCode.Contains("R") && billInvnow.ContUsageQty <= 0 && billInvnow.TorsChkQty < 1).ToList() .Where((billInvnow, warecell) => billInvnow.ProductTime <= startTime) .Count(); //库中未满足时效待检测盘 result.TorNoTobe = _db.Queryable((billInvnow, warecell) => new object[] { JoinType.Inner,billInvnow.ContGrpId == warecell.ContGrpId }).With(SqlWith.NoLock) .Where((billInvnow, warecell) => billInvnow.IsTorsChk == true) .Where((billInvnow, warecell) => billInvnow.InvStateCode == "InvEcecState_In" && !billInvnow.ProductMachCode.Contains("R") && billInvnow.ContUsageQty <= 0 && billInvnow.TorsChkQty < 1).ToList() .Where((billInvnow, warecell) => billInvnow.ProductTime > startTime) .Count(); //库中未满足时效待检测盘（今天出库检测） result.TodayTorNoTobe = _db.Queryable((billInvnow, warecell) => new object[] { JoinType.Inner,billInvnow.ContGrpId == warecell.ContGrpId }).With(SqlWith.NoLock) .Where((billInvnow, warecell) => billInvnow.IsTorsChk == true) .Where((billInvnow, warecell) => billInvnow.InvStateCode == "InvEcecState_In" && !billInvnow.ProductMachCode.Contains("R") && billInvnow.ContUsageQty <= 0 && billInvnow.TorsChkQty < 1).ToList() .Where((billInvnow, warecell) => billInvnow.ProductTime <= startTime) .Count(); result.ToroOuting = _db.Queryable().With(SqlWith.NoLock).Where(p => p.Status != (int)TaskStatus.Finish && p.Status != (int)TaskStatus.Cancel && p.BusType == "扭转检测") .SplitTable(tabs => tabs.Take(2)).Count() - _db.Queryable().With(SqlWith.NoLock).Sum(p => p.WbCount); if (result.ToroOuting < 0) { result.ToroOuting = 0; } result.ToroIning = _db.Queryable().With(SqlWith.NoLock).Where(p => p.Memo == "扭转回库" && p.InvStateCode == "InvEcecState_BuildUp").Count(); result.TimeOuthw = _db.Queryable((inv, cell) => new object[] { JoinType.Left,inv.ContGrpBarCode == cell.ContGrpBarCode }).With(SqlWith.NoLock) .Where((inv, cell) => cell.IsStop == 0 && cell.StateNum == 2 && ((inv.ProductMachCode.Contains("R") && inv.OneInTime < DateTime.Now.AddHours(-72)) || (!inv.ProductMachCode.Contains("R") && inv.ProductTime < DateTime.Now.AddHours(-72)))) .ToList().Count; return result; } public List GetTunnelStopList() { return _db.Queryable().Where(p => p.SType == "InStop" || p.SType == "OutStop").Select(m => new TunnelDto() { Id = m.Id.ToString(), WareHouseName = "时效库", Code = m.Code, Name = m.Name, Tunnel = m.Default1, Floor = m.Default2, Statu = string.IsNullOrEmpty(m.SContent) ? "0" : "1", StatuName = string.IsNullOrEmpty(m.SContent) ? "启用" : "禁用", SType = m.SType, Memo = m.Memo, AddTime = m.AddTime, EditTime = m.EditTime, }).ToList(); } public SysConfig GetSysConfig(string code) { return _db.Queryable().Where(p => p.Code == code).First(); } public List GetTorStaticsInfo(string maccode) { var result = new List(); result = _db.Queryable().With(SqlWith.NoLock).Where(p => p.Machine.Contains(maccode)).ToList(); return result; } public TorschWbCount GetTorschWbCount(string maccode) { var result = new TorschWbCount(); result = _db.Queryable().With(SqlWith.NoLock).Where(p => p.Code.Contains(maccode)).First(); return result == null ? new TorschWbCount() : result; } /// /// 时效当前库存分布(非控制盘) /// /// public List GetCurStockInfo() { var lists = _db.Queryable() .With(SqlWith.NoLock) .Where((billInvnow) => billInvnow.InvStateCode == SXInvState.InvEcecState_In.ToString() && string.IsNullOrEmpty(billInvnow.PreStock) && ((billInvnow.IsControlpanel == false) || (billInvnow.IsControlpanel == true && billInvnow.InDocsNo.StartsWith("CHA")))) .Select((billInvnow) => new CurStockInfo { MatName = billInvnow.MatName, MatCode = billInvnow.MatCode, DocNo = billInvnow.InDocsNo, WbGroupCode = billInvnow.WbGroupCode, Wind = billInvnow.Wind, HWType = billInvnow.HWTypeCode, //IsTorsChk = billInvnow.IsTorsChk.ToString(), IsBlack = billInvnow.IsBlack.ToString(), //Rule = boxrule.PackRule, Grade = billInvnow.Grade, ContGrpBarCode = billInvnow.ContGrpBarCode }).MergeTable().ToList().Distinct(new CompareStock()); var doclist = _db.Context.Queryable().With(SqlWith.NoLock).Select(p => new CurStockInfo() { DocNo = p.DocsNo, SKU = p.SkuCode }).Distinct().ToList(); var newCurStockInfoLists = from st in lists join doc in doclist on st.DocNo equals doc.DocNo into tempstock from nst in tempstock.DefaultIfEmpty() select new CurStockInfo { MatName = st.MatName, MatCode = st.MatCode, DocNo = nst == null ? "" : nst.DocNo, WbGroupCode = st.WbGroupCode, Wind = st.Wind, HWType = st.HWType, //IsTorsChk = billInvnow.IsTorsChk.ToString(), IsBlack = st.IsBlack.ToString(), //Rule = boxrule.PackRule, Grade = st.Grade, ContGrpBarCode = st.ContGrpBarCode, SKU = nst == null ? "" : nst.DocNo, }; var list = new List(); var boxlist = _db.Queryable().With(SqlWith.NoLock).ToList(); foreach (var item in newCurStockInfoLists.GroupBy(p => new { p.SKU, p.MatCode, p.DocNo, p.WbGroupCode, p.Grade }).ToList()) { list.Add(new CurStockInfo() { SKU = item.Key.SKU ?? "", MatName = item.First().MatName, DocNo = item.Key.DocNo, WbGroupCode = item.Key.WbGroupCode ?? "", NoBCount = new List() { item.Count(p => p.IsBlack == "0" && p.Wind == "R") + "R", item.Count(p => p.IsBlack == "0" && p.Wind == "L") + "L" }, BCount = new List() { item.Count(p => p.IsBlack == "1" && p.Wind == "R") + "R", item.Count(p => p.IsBlack == "1" && p.Wind == "L") + "L" }, Grade = item.Key.Grade ?? "", //Rule = item.Key.Rule ?? "", FinCount = boxlist.Where(p => p.DocsNo == item.Key.DocNo && p.ZXStateCode == "2").Count(), NoFinCount = boxlist.Where(p => p.DocsNo == item.Key.DocNo && p.ZXStateCode != "2").Count(), Count = item.Count(p => !string.IsNullOrEmpty(p.ContGrpBarCode)) }); } return list.OrderBy(o => o.SKU).ThenBy(o => o.WbGroupCode).ThenBy(o => o.Wind).ThenBy(o => o.HWType).ThenBy(o => o.Grade).ToList(); } /// /// 时效当前库存分布（控制盘） /// /// public List GetCurStockInfoControl() { var lists = _db.Queryable((billInvnow, boxrule) => new object[] { JoinType.Left,billInvnow.InDocsNo == boxrule.DocsNo,JoinType.Left}) .With(SqlWith.NoLock) .Where((billInvnow, boxrule) => billInvnow.InvStateCode == SXInvState.InvEcecState_In.ToString() && string.IsNullOrEmpty(billInvnow.PreStock) && billInvnow.IsControlpanel == true && billInvnow.Grade == "A") .Select((billInvnow, boxrule) => new CurStockInfo { SKU = boxrule.SkuCode, MatName = billInvnow.MatName, MatCode = billInvnow.MatCode, DocNo = billInvnow.InDocsNo, WbGroupCode = billInvnow.WbGroupCode, Wind = billInvnow.Wind, HWType = billInvnow.HWTypeCode, //IsTorsChk = billInvnow.IsTorsChk.ToString(), IsBlack = billInvnow.IsBlack.ToString(), //Rule = boxrule.PackRule, Grade = billInvnow.Grade, ContGrpBarCode = billInvnow.ContGrpBarCode, ProductTime = billInvnow.ProductTime, }).MergeTable().ToList().Distinct(new CompareStock()); var list = new List(); var boxlist = _db.Queryable().With(SqlWith.NoLock).ToList(); foreach (var item in lists.GroupBy(p => new { p.SKU, p.MatCode, p.WbGroupCode, p.Grade }).ToList()) { list.Add(new CurStockInfo() { SKU = item.Key.SKU ?? "", MatName = item.First().MatName, WbGroupCode = item.Key.WbGroupCode ?? "", HaveBlack = new List() { "("+ item.Count(p => p.IsBlack == "1" && p.Wind == "R" && p.ProductTime >= DateTime.Now.Date.AddDays(-2).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddDays(-1).AddHours(8)) + "R"+"/"+ item.Count(p => p.IsBlack == "1" && p.Wind == "L" && p.ProductTime >= DateTime.Now.Date.AddDays(-2).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddDays(-1).AddHours(8)) + "L" +")", "("+ item.Count(p => p.IsBlack == "1" && p.Wind == "R" && p.ProductTime >= DateTime.Now.Date.AddDays(-1).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddHours(8)) + "R"+"/"+ item.Count(p => p.IsBlack == "1" && p.Wind == "L" && p.ProductTime >= DateTime.Now.Date.AddDays(-1).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddHours(8)) + "L" +")", "("+ item.Count(p => p.IsBlack == "1" && p.Wind == "R" && p.ProductTime >= DateTime.Now.Date.AddHours(13)) + "R"+"/"+ item.Count(p => p.IsBlack == "1" && p.Wind == "L" && p.ProductTime >= DateTime.Now.Date.AddHours(13)) + "L" +")", }, Grade = item.Key.Grade ?? "", //Rule = item.Key.Rule ?? "", Count = item.Count(p => !string.IsNullOrEmpty(p.ContGrpBarCode)), TodayQty = new List() { item.Count(p => p.IsBlack == "0" && p.Wind == "R" && p.ProductTime >= DateTime.Now.Date.AddHours(13)) + "R", item.Count(p => p.IsBlack == "0" && p.Wind == "L" && p.ProductTime >= DateTime.Now.Date.AddHours(13)) + "L" }, YesterdayQty = new List() { item.Count(p => p.IsBlack == "0" && p.Wind == "R" && p.ProductTime >= DateTime.Now.Date.AddDays(-1).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddHours(8)) + "R", item.Count(p => p.IsBlack == "0" && p.Wind == "L" && p.ProductTime >= DateTime.Now.Date.AddDays(-1).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddHours(8)) + "L" }, BeforeYesterdayQty = new List() { item.Count(p => p.IsBlack == "0" && p.Wind == "R" && p.ProductTime >= DateTime.Now.Date.AddDays(-2).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddDays(-1).AddHours(8)) + "R", item.Count(p => p.IsBlack == "0" && p.Wind == "L" && p.ProductTime >= DateTime.Now.Date.AddDays(-2).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddDays(-1).AddHours(8)) + "L" }, }); } return list.OrderBy(o => o.SKU).ThenBy(o => o.WbGroupCode).ThenBy(o => o.Wind).ThenBy(o => o.HWType).ThenBy(o => o.Grade).ToList(); } } }