using NewPdaSqlServer.DB; using NewPdaSqlServer.Dto.service; using NewPdaSqlServer.entity; using NewPdaSqlServer.entity.Base; using NewPdaSqlServer.util; using SqlSugar; using System.Text.RegularExpressions; namespace NewPdaSqlServer.service.Warehouse; public class MesItemQtrkManager : Repository { public List GetQtckList() { //return Db.Queryable() // .Where(x => (x.Qt015 ?? 0) == 1 && (x.Qt014 ?? 0) == 0) // .Select(x => x.Qtck) // .ToList(); const string sql = @"SELECT qtck FROM MES_ITEM_QTRK WHERE QT015 = 1 AND QT028 = 1 AND QT032 = 1 AND QT014 = 0 "; return Db.Ado.SqlQuery(sql); } public ProductionPickDto GetQtckDetailList(WarehouseQuery query) { // 关联查询物料表、物料明细表和物料基础信息表 if (string.IsNullOrEmpty(query.billNo)) throw new Exception("申请单号为空"); var mesInvItemOuts = base.GetSingle(it => it.Qtck == query.billNo); if (mesInvItemOuts == null) throw new Exception("其他入库申请单不存在"); var sql = @"SELECT c.item_no ItemNo,c.item_name ItemName,c.item_model ItemModel, ISNULL(A.qd007,0) FQty,ISNULL(A.qd008,0) SQty,ISNULL(A.qd007,0) - ISNULL(A.qd008,0) DSQty FROM MES_ITEM_QTRR_DETAIL A LEFT JOIN MES_ITEM_QTRK B ON A.qtrkGuid = B.guid LEFT JOIN MES_ITEMS C ON A.itemId = C.item_id WHERE B.qtck = @billNo AND QT015 = 1 AND QT028 = 1 AND QT032 = 1 AND QT014 = 0 "; var sqlParams = new List { new("@billNo", query.billNo) }; var womdabs = Db.Ado.SqlQuery(sql, sqlParams); if (womdabs.Count < 1) throw new Exception($"该其他入库申请单 {query.billNo} 未全部审核或已完结，请确认！"); var DS_list = womdabs.Where(s => s.DSQty > 0).ToList(); var YS_list = womdabs.Where(s => s.SQty > 0).ToList(); var dto = new ProductionPickDto { //daa001 = womdaa.Daa001, //PlanNo = womcaa.Caa020, items = DS_list, Ysitems = YS_list }; return dto; } public string ScanInDepotsQT(WarehouseQuery query) { var sectionCode = query.sectionCode; var billNo = query.billNo; // 1. 验证库位条码是否为空 if (string.IsNullOrEmpty(sectionCode)) throw new Exception("请扫库位条码！"); // 2. 查询库位对应的仓库编码 var depotId = Db.Queryable( (a, b) => new JoinQueryInfos( JoinType.Inner, a.DepotGuid == b.Guid)) .Where((a, b) => a.DepotSectionCode == sectionCode) .Select((a, b) => b.DepotId) .First(); if (depotId == null) throw new Exception($"库位编码 {sectionCode} 不存在，请确认！"); // 3. 查询申请单对应的仓库 var qtrk = Db.Queryable() .Where(x => x.Qtck == billNo) .Select(x => x.Qt008) .First(); if (qtrk == null) throw new Exception($"库位编码 {sectionCode} 不存在，请确认！"); // 4. 验证库位是否属于申请仓库 if (depotId != Convert.ToInt32(qtrk)) throw new Exception($"扫码库位 {sectionCode} 不属于申请仓库!"); // 5. 返回成功信息 return $"仓库：{qtrk} 库位：{sectionCode}"; } public (WarehouseQuery form, List item) ScanInBcodeQtrk(WarehouseQuery query) { var c_user = query.userName; var p_section_code = query.sectionCode; var p_item_barcode = query.barcode; var p_bill_no = query.billNo; var billTypeId = 100; var transactionNo = 102; // 1. 验证库位条码是否为空 if (string.IsNullOrEmpty(p_section_code)) throw new Exception("请扫库位条码！"); // 2. 查询库位对应的仓库编码和仓库ID var depotInfo = Db.Queryable( (a, b) => new JoinQueryInfos(JoinType.Inner, a.DepotGuid == b.Guid)) .Where((a, b) => a.DepotSectionCode == p_section_code) .Select((a, b) => new { b.DepotCode, b.DepotId }) .First(); if (depotInfo == null) throw new Exception($"库位编码 {p_section_code} 不存在，请确认！"); var c_depot_code = depotInfo.DepotCode; var c_depot_id = depotInfo.DepotId; var mesDepost = Db.Queryable() .Where(s => s.DepotId == c_depot_id).First(); // 3. 验证条码是否已入库 var existsInStock = Db.Queryable() .Any(x => x.ItemBarcode == p_item_barcode); if (existsInStock) throw new Exception("此条码已扫码入库完成,请核对！"); // 4. 查询条码信息 var barcode = Db.Queryable() .Where(x => x.ItemBarcode == p_item_barcode) .First(); if (barcode == null) throw new Exception("条码不存在,请核对！"); //if (barcode.ComeFlg != 3) throw new Exception("条码不是其他入库条码,无法用其他入库！"); // 验证条码是否已在库存中 var stockCount = Db.Queryable() .Where(x => x.ItemBarcode == p_item_barcode) .Count(); if (stockCount > 0) throw new Exception("此条码已扫码入库完成,请核对！"); // 5. 查询其他入库申请单 var qtrk = Db.Queryable() .Where(x => x.Qtck == p_bill_no) .First(); if (qtrk == null) throw new Exception("其他入库申请单不存在！"); // 验证库位是否属于申请仓库 if (c_depot_id != Convert.ToInt32(qtrk.Qt008)) throw new Exception($"扫码库位 {p_section_code} 不属于申请仓库!"); // 6. 查询申请单明细 var detail = Db.Queryable() .Where(x => x.QtrkGuid == qtrk.Guid && x.ItemId == barcode.ItemId.ToString()) .First(); if (detail == null) throw new Exception($"其他入库申请单{p_bill_no}无此物料{barcode.ItemNo} 请核对！"); // 7. 验证数量 if (barcode.Quantity > (detail.Qd007 ?? 0) - (detail.Qd008 ?? 0)) throw new Exception("条码数量超过申请数量,请核对！"); var details = new List(); string pattern = @"\(([^)]+)\)"; Match match = Regex.Match(qtrk.Qt023, pattern); var owner_type = ""; // 8.获取货主类型 if (match.Success) { owner_type = match.Groups[1].Value; } else { throw new Exception("其他入库申请单货主类型有误,请核对！"); } // 10. 执行入库事务 UseTransaction(db => { var res = 0; // 查询是否存在未入库的入库单 var existingInv = db.Queryable() .Where(x => x.Status == 0 && x.InsDate.Value.ToString("yyyy-MM-dd") == DateTime.Now.ToString("yyyy-MM-dd") && x.TransctionNo == transactionNo.ToString() && x.TaskNo == p_bill_no //&& x.CbillNo == p_bill_no && x.BillTypeId == billTypeId && x.DepotsId == Convert.ToInt64(c_depot_id)) // && x.DepotsCode == c_depot_code) //&& x.SuppNo == barcode.SuppNo) .First(); var newId = Guid.Empty; var billNo = ""; // 如果不存在则创建新入库单 if (existingInv == null) { newId = Guid.NewGuid(); billNo = BillNo.GetBillNo("QTRK(其他入库)"); barcode.UrgentFlag ??= false; res += db.Insertable(new MesInvItemIns { Guid = newId, BillNo = billNo, BillTypeId = billTypeId, InsDate = DateTime.Now, DepotsCode = c_depot_code, TransctionNo = transactionNo.ToString(), SuppNo = barcode.SuppNo, CreateBy = c_user, CreateDate = DateTime.Now, LastupdateBy = c_user, LastupdateDate = DateTime.Now, UrgentFlag = barcode.UrgentFlag.Value ? "1" : "0", //CbillNo = p_bill_no, Fstatus = 0, Status = 0, Reason = qtrk.Qt010, TaskNo = p_bill_no, DepotsId = Convert.ToInt64(c_depot_id) , InType = "其他入库", ReceiveOrgId = qtrk.Qt022 }).IgnoreColumns(true).ExecuteCommand(); } else { newId = existingInv.Guid; billNo = existingInv.BillNo; } // 查询是否存在相同物料和工单行的入库明细 var existingItem = db.Queryable() .Where(x => x.ItemInId == newId && x.ItemId == barcode.ItemId && x.DepotId == c_depot_id.ToString() ) //&& x.DepotCode = .First(); if (existingItem == null) // 不存在则新增入库明细 res += db.Insertable(new MesInvItemInCItems { Guid = Guid.NewGuid(), ItemInId = newId, Quantity = barcode.Quantity, CreateBy = c_user, CreateDate = DateTime.Now, ItemNo = barcode.ItemNo, DepotCode = c_depot_code, ItemSname = barcode.ItemSname, Unit = barcode.Unit, Ebeln = barcode.WorkNo, BillNo = billNo, Factory = barcode.Factory, Company = barcode.Company, WorkNo = barcode.WorkNo, EbelnLineNo = barcode.WorkLine, CbillNo = barcode.BillNo, WorkLine = barcode.WorkLine, SuppNo = barcode.SuppNo, Remark = barcode.Memo, EbelnK3id = barcode.EbelnK3id, LineK3id = barcode.LineK3id, ItemId = barcode.ItemId, DepotId = c_depot_id.ToString(), itemDabid = barcode.AboutGuid.ToString(), }).IgnoreColumns(true).ExecuteCommand(); else // 存在则更新数量 res += db.Updateable() .SetColumns( x => x.Quantity == x.Quantity + barcode.Quantity) .Where(x => x.ItemInId == newId && x.ItemId == barcode.ItemId && x.DepotId == c_depot_id.ToString()) .ExecuteCommand(); // 插入入库明细记录 res += db.Insertable(new MesInvItemInCDetails { ItemInId = newId, BillNo = billNo, ItemBarcode = barcode.ItemBarcode, Quantity = barcode.Quantity, BarcodeFlag = true, EpFlag = true, WorkType = 1, ItemNo = barcode.ItemNo, LotNo = barcode.LotNo, SuppId = barcode.SuppId, SuppNo = barcode.SuppNo, DepotId = Convert.ToInt64(c_depot_id), DepotCode = c_depot_code, DepotSectionCode = p_section_code, ItemSname = barcode.ItemSname, Unit = barcode.Unit, CreateBy = c_user, CreateDate = DateTime.Now, LastupdateBy = c_user, LastupdateDate = DateTime.Now, Remark = barcode.Memo, Factory = barcode.Factory, Company = barcode.Company, Ebeln = barcode.Mblnr, EbelnLineNo = barcode.Zeile, WorkNo = barcode.WorkNo, WorkLine = barcode.WorkLine, CbillNo = barcode.BillNo, UrgentFlag = barcode.UrgentFlag, BoardStyle = barcode.BoardStyle, TaskNo = barcode.TaskNo, EbelnK3id = barcode.EbelnK3id, LineK3id = barcode.LineK3id, ItemId = barcode.ItemId, Ischeck = true }).IgnoreColumns(true).ExecuteCommand(); // 插入库存业务流水记录 res += db.Insertable(new MesInvBusiness2 { Guid = Guid.NewGuid(), Status = 1, BillTypeId = billTypeId, TransactionCode = transactionNo.ToString(), BusinessType = 1, ItemBarcode = barcode.ItemBarcode, ItemNo = barcode.ItemNo, LotNo = barcode.LotNo, EpFlag = true, Quantity = barcode.Quantity, InvDepotId = c_depot_id, ToInvDepotsCode = c_depot_code, ToInvDepotSectionsCode = p_section_code, Description = "其他入库", CreateBy = c_user, CreateDate = DateTime.Now, LastupdateBy = c_user, LastupdateDate = DateTime.Now, TaskNo = barcode.BillNo, BillNo = billNo, WorkNo = barcode.WorkNo, WorkLine = barcode.WorkLine, SuppId = barcode.SuppId, SuppNo = barcode.SuppNo, EbelnK3id = barcode.EbelnK3id, LineK3id = barcode.LineK3id, ItemId = barcode.ItemId // SalesOrder = barcode.SalesOrder, // IsZy = barcode.IsZy, // OuterBarcode = barcode.OuterBarcode }).IgnoreColumns(true).ExecuteCommand(); barcode.EpFlag ??= false; // 插入库存记录 res += db.Insertable(new MesInvItemStocks { TaskNo = barcode.TaskNo, ItemBarcode = p_item_barcode, ItemNo = barcode.ItemNo, LotNo = barcode.LotNo, Quantity = barcode.Quantity, EpFlag = barcode.EpFlag.Value ? (byte)1 : (byte)0, DepotId = c_depot_id, DepotsCode = c_depot_code, DepotSectionsCode = p_section_code, CheckDate = DateTime.Now, IndepDate = DateTime.Now, Factory = barcode.Factory, Company = barcode.Company, BoardStyle = barcode.BoardStyle, WorkNo = barcode.WorkNo, WorkLine = barcode.WorkLine, SuppId = barcode.SuppId, SuppNo = barcode.SuppNo, EbelnK3id = barcode.EbelnK3id, LineK3id = barcode.LineK3id, ItemId = barcode.ItemId, BillNo = barcode.BillNo, OwnerId = qtrk.Qt024, OwnerType = owner_type, StockOrgId = qtrk.Qt022, IndepUserCode = c_user // SalesOrder = barcode.SalesOrder, // IsZy = barcode.IsZy, // Visable = 0, // OuterBarcode = barcode.OuterBarcode }).IgnoreColumns(true).ExecuteCommand(); // 更新条码入库标志 // db.Updateable() // .SetColumns(x => x.rkf == 1) // .Where(x => x.ItemBarcode == p_item_barcode) // .ExecuteCommand(); // 更新申请单明细已入库数量 res += db.Updateable() .SetColumns(x => x.Qd008 == (x.Qd008 ?? 0) + barcode.Quantity) .Where(x => x.QtrkGuid == qtrk.Guid && x.ItemId == barcode.ItemId.ToString()) .ExecuteCommand(); // 检查是否完全入库并更新状态 var detail1 = db.Queryable() .Where(x => x.Guid == detail.Guid) .First(); if ((detail1.Qd007 ?? 0) - (detail1.Qd008 ?? 0) == barcode.Quantity) res += db.Updateable() .SetColumns(x => x.Qd011 == 1) .Where(x => x.Guid == detail1.Guid) .ExecuteCommand(); details = Db.Queryable( (a, b, c) => new JoinQueryInfos( JoinType.Left, a.Guid == b.QtrkGuid, JoinType.Left, c.Id.ToString() == b.ItemId)) .Where((a, b, c) => a.Qtck == p_bill_no && (b.Qd007 ?? 0) - (b.Qd008 ?? 0) > 0) .OrderBy((a, b, c) => b.Qd002) .Select((a, b, c) => new MesItemQtrrDetail { Qtck = a.Qtck, ItemNo = c.ItemNo, ItemName = c.ItemName, Qd007 = b.Qd007 ?? 0, Qd008 = b.Qd008 ?? 0 }) .ToList(); if (CollectionUtil.IsNullOrEmpty(details)) res += db.Updateable() .SetColumns(s => s.Qt014 == 1) .Where(x => x.Qtck == p_bill_no) .ExecuteCommand(); // 创建 插入日志 var logService = new LogService(); var LogMsg = "【PDA】其他入库。条码【" + query.barcode + "】数量【" + barcode.Quantity.ToString() + "】 入库单号【" + billNo + "】"; logService.CreateLog(db, query.userName, qtrk.Guid.ToString(), "MES_ITEM_QTRK", LogMsg, qtrk.Qtck); if (res < 5) throw new Exception("插入或更新失败"); return res; }); query.itemNo = barcode.ItemNo; query.Num = barcode.Quantity; return (query, details); } // End of Selection }