using Masuit.Tools; using NewPdaSqlServer.DB; using NewPdaSqlServer.Dto.service; using NewPdaSqlServer.entity; using NewPdaSqlServer.entity.Base; using NewPdaSqlServer.util; using SqlSugar; using System.Security.Cryptography; namespace NewPdaSqlServer.service.Warehouse; /// /// MES物料表管理类 /// public class MesItemTblManager : Repository { /// /// 获取生产退料单号列表 /// /// 退料单号列表 public List GetSCTLBillNo(WarehouseQuery query) { var list = Db.Queryable() .Where(s => (s.Tbl013 ?? 0) == 1 // 审核通过 && (s.Tbl020 ?? 0) == 0 && s.Tbl008 == query.Type) .Select(s => s.BillNo).ToList(); return list; } /// /// 根据单号获取MES物料表明细 /// /// 仓库查询参数 /// 物料明细列表 public ProductionPickDto GetMesItemTblDetailByBillNo( WarehouseQuery query) { // 关联查询物料表、物料明细表和物料基础信息表 var mesItemTblDetails = Db .Queryable( (a, b, c) => new JoinQueryInfos( JoinType.Left, a.Id == b.Tlmid, JoinType.Left, b.Tld009 == c.Id) ).Where((a, b, c) => a.BillNo == query.billNo && (a.Tbl013 ?? 0) == 1) .Select((a, b, c) => new ItemDetailModel { FQty = b.Tld005 ?? 0, // 待退数量 SQty = b.Tld006 ?? 0, // 已退数量 DSQty = (b.Tld005 ?? 0) - (b.Tld006 ?? 0), ItemNo = c.ItemNo, // 物料编号 ItemName = c.ItemName, // 物料名称 ItemModel = c.ItemModel // 物料型号 }).ToList(); if (mesItemTblDetails.Count<1) throw new Exception("该申请单【" + query.billNo + "】 明细不存在,请确认!"); var DS_list = mesItemTblDetails.Where(s => s.DSQty > 0).ToList(); var YS_list = mesItemTblDetails.Where(s => s.SQty > 0).ToList(); var itemTblDetails = new ProductionPickDto { items = DS_list, Ysitems = YS_list // yisao = mesInvItemOutCDetailsList }; return itemTblDetails; } /// /// 生产退料扫描库位 /// /// 仓库查询参数 /// 库位信息 public WarehouseQuery SctlScanDepots(WarehouseQuery query) { if (query.DepotCode.IsNullOrEmpty()) throw new NullReferenceException("请扫库位条码"); // 关联查询库位分区和库位信息 var warehouseQuery = Db.Queryable((a, b) => new JoinQueryInfos(JoinType.Inner, a.DepotGuid == b.Guid)) .Where((a, b) => a.DepotSectionCode == query.DepotCode) .Select((a, b) => new WarehouseQuery { DepotSectionName = a.DepotSectionName, DepotName = b.DepotName, DepotCode = b.DepotCode }).First(); if (warehouseQuery == null) throw new Exception("库位编码" + query.DepotCode + " 不存在,请确认!"); return warehouseQuery; } /// /// 生产退料扫描条码 /// /// 仓库查询参数 /// 处理结果 public WarehouseQuery SctlScanBarcode(WarehouseQuery query) { var p_item_barcode = query.barcode; // 物料条码 var p_bill_no = query.billNo; // 单据号 var p_section_code = query.DepotCode; // 库位编码 var c_user = query.userName; // 用户名 var p_bill_type_id = 100; // 单据类型ID var p_transction_no = 104; // 交易编号 // 验证库位条码 if (p_section_code.IsNullOrEmpty()) throw new Exception("请扫库位条码!"); // 获取库位信息 var c_depot_code = Db.Queryable((a, b) => new JoinQueryInfos(JoinType.Inner, a.DepotGuid == b.Guid)) .Where((a, b) => a.DepotSectionCode == p_section_code) .Select((a, b) => b.DepotId).First(); if (!c_depot_code.HasValue) throw new Exception("库位编码" + p_section_code + " 不存在,请确认!"); // 获取库位分区信息 var mesDepotSections = Db.Queryable() .Where(a => a.DepotSectionCode == p_section_code).First(); if (mesDepotSections == null) throw new Exception("库位编码" + p_section_code + " 不存在,请确认!"); // 获取库位基础信息 var c_mes_depots = Db.Queryable() .Where(b => b.Guid == mesDepotSections.DepotGuid).First(); if (c_mes_depots == null) throw new Exception("库位编码" + p_section_code + " 不存在,请确认!"); // 检查条码是否已入库 var c_num = Db.Queryable((a, b) => new JoinQueryInfos(JoinType.Inner, a.Guid == b.ItemInId)) .Where((a, b) => b.ItemBarcode == p_item_barcode && a.BillTypeId == p_bill_type_id && a.TransctionNo == p_transction_no.ToString()) .Count(); if (c_num > 0) throw new Exception("此条码已扫入库,勿重复扫描!"); c_num = Db.Queryable() .Where(t => t.ItemBarcode == p_item_barcode).Count(); if (c_num > 0) throw new Exception("此条码已扫入库,勿重复扫描!"); // 获取条码信息 var c_mes_inv_item_barcodes = Db.Queryable() .Where(t => t.ItemBarcode == p_item_barcode).First(); if (c_mes_inv_item_barcodes == null) throw new Exception("此条码不属于该退料单,请核对!"); if (c_mes_inv_item_barcodes.Memo != "生产退料") throw new Exception("此条码不是生产退料条码,不可使用生产退料模块!"); // 使用 String.Equals 方法进行字符串比较,指定比较规则为忽略大小写,提高比较的灵活性 if (!string.Equals(c_mes_inv_item_barcodes.BillNo, p_bill_no, StringComparison.OrdinalIgnoreCase)) throw new Exception($"该条码对应的申请号【{c_mes_inv_item_barcodes.BillNo}】与 当前申请单号【{p_bill_no}】不一致,请核对!"); // 获取退料单信息 var C_MES_ITEM_TBL = Db.Queryable() .Where(a => a.BillNo == p_bill_no && (a.Tbl013 ?? 0) == 1).First(); // 获取退料单信息 var tbWOMDAA = Db.Queryable() .Where(a => a.Guid.ToString() == C_MES_ITEM_TBL.Tbl002).First(); if (C_MES_ITEM_TBL == null) throw new Exception("申请单已撤回,无法扫码!"); if (C_MES_ITEM_TBL.Tbl020 == 1) throw new Exception("扫码完成,申请单已完结!"); // 获取退料单明细 var C_MES_ITEM_TBL_DETAIL = Db.Queryable() .Where(a => a.Tlid == c_mes_inv_item_barcodes.AboutGuid) .First(); if (C_MES_ITEM_TBL_DETAIL == null) throw new Exception("条码不属于该申请单明细,无法扫码!"); var CABerpid = Db.Queryable().Where(womdab => womdab.Guid.ToString() == C_MES_ITEM_TBL_DETAIL.Tld013).Select(womdab => womdab.Erpid).First(); var ownerId = Db.Queryable().Where(womcab => womcab.Erpid == CABerpid.Value).Select(womcab => womcab.OwnerId).First(); if (ownerId == null) throw new Exception("用料清单货主信息不存在,无法扫码,请联系管理员!"); var owner_type = ""; if (Db.Queryable().Any(x => x.Fid == ownerId)) { owner_type = "BD_OwnerOrg"; } else { // 第二层判断:检查 MES_CUSTOMER if (Db.Queryable().Any(x => x.Id == Convert.ToInt32(ownerId))) { owner_type = "BD_Customer"; } else { // 第三层判断:检查 MES_SUPPLIER if (Db.Queryable().Any(x => x.Id == Convert.ToInt32(ownerId))) { owner_type = "BD_Supplier"; } else { throw new Exception("入库失败,用料清单货主信息存在问题,请联系管理员解决!"); } } } var c_quantity = c_mes_inv_item_barcodes.Quantity; var c_bill_no = ""; var c_id = Guid.Empty; // 使用事务处理数据更新 UseTransaction(db => { // 查询入库单 var mesInvItemIns = db.Queryable() .Where(d => d.Status == 0 && d.TransctionNo == p_transction_no.ToString() && d.TaskNo == c_mes_inv_item_barcodes.BillNo && d.DepotsId == c_depot_code && d.InsDate.Value.ToString("yyyyMMdd") == DateTime.Now.ToString("yyyyMMdd")).First(); var totalResult = 0; // 如果入库单不存在则创建新的入库单 if (mesInvItemIns == null) { c_bill_no = BillNo.GetBillNo("SCTL(生产退料)"); c_id = Guid.NewGuid(); totalResult += db.Insertable(new MesInvItemIns { Guid = c_id, BillNo = c_bill_no, BillTypeId = p_bill_type_id, InsDate = DateTime.Now, DepotsId = c_depot_code, UserNoBack = c_user, Reason = C_MES_ITEM_TBL.Tbl005, Remark = C_MES_ITEM_TBL.Tbl006, //InsDate = DateTime.Now, DepotsCode = c_mes_depots.DepotCode, TaskNo = c_mes_inv_item_barcodes.BillNo, //DepotsId = c_depot_code, TransctionNo = p_transction_no.ToString(), CreateBy = c_user, CreateDate = DateTime.Now, LastupdateBy = c_user, LastupdateDate = DateTime.Now, CbillNo = tbWOMDAA.Daa001, InType = "生产退料", ReceiveOrgId = c_mes_depots.FSubsidiary, Fstatus = 0, Status = 0, WorkNo = tbWOMDAA.Daa021 }).IgnoreColumns(true).ExecuteCommand(); } else { c_id = mesInvItemIns.Guid; c_bill_no = mesInvItemIns.BillNo; } // 检查是否为合并打印条码 var hbdy = c_mes_inv_item_barcodes.Hbdy ?? 0; if (hbdy == 1) throw new Exception("不支持合并打印的条码:" + p_item_barcode); // 检查是否存在于 MES_INV_ITEM_IN_C_ITEMS 表 var existingCount = db.Queryable() .Where(it => it.ItemInId == c_id && it.ItemId == C_MES_ITEM_TBL_DETAIL.Tld009 && it.DepotId == c_depot_code.ToString()) .Count(); if (existingCount == 0) // 不存在时插入新记录 db.Insertable(new MesInvItemInCItems { ItemInId = c_id, Quantity = c_mes_inv_item_barcodes.Quantity, CreateBy = c_user, CreateDate = DateTime.Now, ItemNo = c_mes_inv_item_barcodes.ItemNo, //DepotCode = mesDepost.DepotCode, ItemSname = c_mes_inv_item_barcodes.ItemSname, Unit = c_mes_inv_item_barcodes.Unit, Ebeln = c_mes_inv_item_barcodes.WorkNo, BillNo = c_bill_no, WorkNo = c_mes_inv_item_barcodes.WorkNo, EbelnLineNo = c_mes_inv_item_barcodes.WorkLine, CbillNo = c_mes_inv_item_barcodes.BillNo, WorkLine = c_mes_inv_item_barcodes.WorkLine, SuppId = c_mes_inv_item_barcodes.SuppId, SuppNo = c_mes_inv_item_barcodes.SuppNo, Remark = c_mes_inv_item_barcodes.Memo, EbelnK3id = c_mes_inv_item_barcodes.EbelnK3id, LineK3id = c_mes_inv_item_barcodes.LineK3id, ItemId = c_mes_inv_item_barcodes.ItemId, DepotCode = c_mes_depots.DepotCode, DepotId = c_depot_code.ToString(), itemDabid = C_MES_ITEM_TBL_DETAIL.Tlid.ToString() }).IgnoreColumns(true).ExecuteCommand(); else // 存在时更新数量 db.Updateable() .SetColumns(it => new MesInvItemInCItems { Quantity = SqlFunc.IsNull(it.Quantity, 0) + c_mes_inv_item_barcodes.Quantity // 确保 Quantity 不为 null }) .Where(it => it.ItemInId == c_id && it.ItemId == C_MES_ITEM_TBL_DETAIL.Tld009 && it.DepotId == c_depot_code.ToString()) //.IgnoreColumns(true) // 保留 IgnoreColumns .ExecuteCommand(); // 插入 mes_inv_item_in_c_details 表 totalResult += db.Insertable(new MesInvItemInCDetails { ItemInId = c_id, BillNo = c_bill_no, ItemBarcode = p_item_barcode, Quantity = c_mes_inv_item_barcodes.Quantity, BarcodeFlag = true, EpFlag = true, WorkType = 1, ItemNo = c_mes_inv_item_barcodes.ItemNo, //LotNo = cgddDetails.BatchNumber,--批号,退料不设置批号 SuppId = c_mes_inv_item_barcodes.SuppId, SuppNo = c_mes_inv_item_barcodes.SuppNo, //DepotCode = mesDepost.DepotCode, DepotSectionCode = p_section_code, ItemSname = c_mes_inv_item_barcodes.ItemSname, Unit = c_mes_inv_item_barcodes.Unit, CreateBy = c_user, CreateDate = DateTime.Now, LastupdateBy = c_user, LastupdateDate = DateTime.Now, Remark = c_mes_inv_item_barcodes.Memo, Ebeln = c_mes_inv_item_barcodes.Mblnr, EbelnLineNo = c_mes_inv_item_barcodes.Zeile, WorkNo = c_mes_inv_item_barcodes.WorkNo, WorkLine = c_mes_inv_item_barcodes.WorkLine, CbillNo = c_mes_inv_item_barcodes.BillNo, UrgentFlag = c_mes_inv_item_barcodes.UrgentFlag, BoardStyle = c_mes_inv_item_barcodes.BoardStyle, TaskNo = c_mes_inv_item_barcodes.TaskNo, EbelnK3id = c_mes_inv_item_barcodes.EbelnK3id, LineK3id = c_mes_inv_item_barcodes.LineK3id, ItemId = c_mes_inv_item_barcodes.ItemId, Ischeck = true, //CheckDate = C_MES_ITEM_TBL_DETAIL.CheckDate, //CheckRes = C_MES_ITEM_TBL_DETAIL.CheckRes, //CheckStates = C_MES_ITEM_TBL_DETAIL.CheckStates,--检验信息 ReceiveOrgId = c_mes_depots.FSubsidiary, DepotCode = c_mes_depots.DepotCode, DepotId = Convert.ToInt64(c_depot_code) }).IgnoreColumns(true).ExecuteCommand(); // 插入业务记录 totalResult += db.Insertable(new MesInvBusiness2 { Guid = Guid.NewGuid(), Status = 1, BillTypeId = p_bill_type_id, TransactionCode = p_transction_no.ToString(), BusinessType = 1, ItemBarcode = p_item_barcode, ItemNo = c_mes_inv_item_barcodes.ItemNo, LotNo = c_mes_inv_item_barcodes.LotNo, EpFlag = true, Quantity = c_mes_inv_item_barcodes.Quantity, ToInvDepotsCode = c_mes_depots.DepotCode, InvDepotId = c_depot_code, ToInvDepotSectionsCode = p_section_code, Description = "生产退料", CreateBy = c_user, CreateDate = DateTime.Now, LastupdateBy = c_user, LastupdateDate = DateTime.Now, TaskNo = c_mes_inv_item_barcodes.TaskNo, BillNo = c_bill_no, WorkNo = c_mes_inv_item_barcodes.WorkNo, WorkLine = c_mes_inv_item_barcodes.WorkLine, SuppId = c_mes_inv_item_barcodes.SuppId, SuppNo = c_mes_inv_item_barcodes.SuppNo, ItemId = c_mes_inv_item_barcodes.ItemId }).IgnoreColumns(true).ExecuteCommand(); // 插入库存记录 totalResult += db.Insertable(new MesInvItemStocks { Guid = Guid.NewGuid(), TaskNo = c_mes_inv_item_barcodes.TaskNo, ItemBarcode = p_item_barcode, ItemNo = c_mes_inv_item_barcodes.ItemNo, LotNo = c_mes_inv_item_barcodes.LotNo, Quantity = c_mes_inv_item_barcodes.Quantity, //EpFlag = c_mes_inv_item_barcodes.EpFlag.Value // ? (byte)1 // : (byte)0, DepotId = c_mes_depots.DepotId, DepotsCode = c_mes_depots.DepotCode, DepotSectionsCode = p_section_code, CheckDate = c_mes_inv_item_barcodes.CreateDate, IndepDate = DateTime.Now, BoardStyle = c_mes_inv_item_barcodes.BoardStyle, WorkNo = c_mes_inv_item_barcodes.WorkNo, WorkLine = c_mes_inv_item_barcodes.WorkLine, SuppNo = c_mes_inv_item_barcodes.SuppNo, ItemId = c_mes_inv_item_barcodes.ItemId, BillNo = c_mes_inv_item_barcodes.BillNo, //DepotId = Convert.ToInt32(c_depot_code), OwnerId = ownerId, OwnerType = owner_type, StockOrgId = c_mes_depots.FSubsidiary, IndepUserCode = c_user }).IgnoreColumns(true).ExecuteCommand(); // 根据退料类型(良品退料、来料不良退料、作业不良退料)更新相关数据 if (C_MES_ITEM_TBL.Tbl005 is "良品退料" or "来料不良退料") { // 良品退料 - 更新工单表(WOMDAB)相关数量 if (C_MES_ITEM_TBL.Tbl005 == "良品退料") totalResult += Db.Updateable() .SetColumns(it => new Womdab { Dab007 = it.Dab007 - c_mes_inv_item_barcodes.Quantity, // 减少工单数量 Dab022 = (it.Dab022 ?? 0) + c_mes_inv_item_barcodes.Quantity, // 增加退料数量 LpTl = (it.LpTl ?? 0) + (int)c_mes_inv_item_barcodes .Quantity, // 增加良品退料数量 Dab020 = (it.Dab020 ?? 0) - c_mes_inv_item_barcodes.Quantity // 减少已发料数量 }) .Where(it => it.Dab001 == c_mes_inv_item_barcodes.WorkNo && it.Dab002 == c_mes_inv_item_barcodes .WorkLine && it.Dab003 == c_mes_inv_item_barcodes .ItemId.ToString()) .ExecuteCommand(); // 来料不良退料 - 更新工单表(WOMDAB)相关数量 else if (C_MES_ITEM_TBL.Tbl005 == "来料不良退料") totalResult += Db.Updateable() .SetColumns(it => new Womdab { Dab007 = it.Dab007 - c_mes_inv_item_barcodes.Quantity, // 减少工单数量 Dab022 = (it.Dab022 ?? 0) + c_mes_inv_item_barcodes.Quantity, // 增加退料数量 LlBl = (it.LlBl ?? 0) + (int)c_mes_inv_item_barcodes .Quantity, // 增加来料不良数量 Dab020 = (it.Dab020 ?? 0) - c_mes_inv_item_barcodes.Quantity // 减少已发料数量 }) .Where(it => it.Dab001 == c_mes_inv_item_barcodes.WorkNo && it.Dab002 == c_mes_inv_item_barcodes .WorkLine && it.Dab003 == c_mes_inv_item_barcodes .ItemId.ToString()) .ExecuteCommand(); // 更新退料单明细表已退数量 totalResult += Db.Updateable() .SetColumns(it => new MesItemTblDetail { Tld006 = (it.Tld006 ?? 0) + (int)c_mes_inv_item_barcodes.Quantity // 增加已退数量 }) .Where(it => it.Tlmid == C_MES_ITEM_TBL.Id && it.Tlid == c_mes_inv_item_barcodes.AboutGuid) .ExecuteCommand(); } // 作业不良退料 - 更新工单表和退料单明细表 else if (C_MES_ITEM_TBL.Tbl005 == "作业不良退料") { totalResult += Db.Updateable() .SetColumns(it => new Womdab { Dab022 = (it.Dab022 ?? 0) + c_mes_inv_item_barcodes.Quantity, // 增加退料数量 ZyBl = (it.ZyBl ?? 0) + (int)c_mes_inv_item_barcodes .Quantity, // 增加作业不良数量 Dab020 = (it.Dab020 ?? 0) - c_mes_inv_item_barcodes.Quantity // 减少已发料数量 }) .Where(it => it.Dab001 == c_mes_inv_item_barcodes.WorkNo && it.Dab002 == c_mes_inv_item_barcodes.WorkLine && it.Dab003 == c_mes_inv_item_barcodes.ItemId .ToString()) .ExecuteCommand(); // 更新退料单明细表已退数量 totalResult += Db.Updateable() .SetColumns(it => new MesItemTblDetail { Tld006 = (it.Tld006 ?? 0) + (int)c_mes_inv_item_barcodes.Quantity // 增加已退数量 }) .Where(it => it.Tlmid == C_MES_ITEM_TBL.Id && it.Tlid == c_mes_inv_item_barcodes.AboutGuid) .ExecuteCommand(); } // 如果待退数量等于本次退料数量,则更新明细完成状态 if ((C_MES_ITEM_TBL_DETAIL.Tld005 ?? 0) - (C_MES_ITEM_TBL_DETAIL.Tld006 ?? 0) == c_mes_inv_item_barcodes.Quantity) totalResult += Db.Updateable() .SetColumns(it => new MesItemTblDetail { Tld008 = 1 }) // 设置完成标志 .Where(it => it.Tlid == C_MES_ITEM_TBL_DETAIL.Tlid) .ExecuteCommand(); // 检查退料单是否所有明细都已完成 var remainingCount = Db.Queryable( (a, b) => new JoinQueryInfos(JoinType.Left, a.Id == b.Tlmid)) .Where((a, b) => a.BillNo == p_bill_no && (b.Tld005 ?? 0) - (b.Tld006 ?? 0) > 0) .Count(); // 如果所有明细都已完成,则更新退料单状态为已完成 if (remainingCount < 1) totalResult += Db.Updateable() .SetColumns(it => it.Tbl020 == 1) // 设置完成标志 .Where(it => it.BillNo == p_bill_no) .ExecuteCommand(); // 检查必要的插入操作是否都成功执行 var minimumExpectedOperations = 3; // 至少需要执行的插入操作数 if (totalResult < minimumExpectedOperations) throw new Exception( $"关键数据插入失败,预期至少{minimumExpectedOperations}个操作,实际执行{totalResult}个操作"); // 创建 插入日志 var logService = new LogService(); var LogMsg = "[PDA]生产退料。条码【" + query.barcode + "】 退料单号【" + c_bill_no + "】"; logService.CreateLog(db, query.userName, tbWOMDAA.Guid.ToString(), "WOMDAA", LogMsg, tbWOMDAA.Daa001); return totalResult; }); query.itemNo = c_mes_inv_item_barcodes.ItemNo; query.Num = c_quantity.Value; return query; } }