using Masuit.Tools; using Masuit.Tools.Models; using NewPdaSqlServer.DB; using NewPdaSqlServer.Dto.service; using NewPdaSqlServer.entity; using NewPdaSqlServer.entity.Base; using NewPdaSqlServer.service.@base; using NewPdaSqlServer.util; using Newtonsoft.Json; using SqlSugar; using System.Data; using System.Data.SqlClient; using System.Security.Cryptography; using static Microsoft.EntityFrameworkCore.DbLoggerCategory; namespace NewPdaSqlServer.service.Warehouse; public class MesInvItemInCDetailsManager : Repository { //当前类已经继承了 Repository 增、删、查、改的方法 public ItemInBaseModel SaveBarCodes(WarehouseQuery entity) { ItemInBaseModel res = new ItemInBaseModel(); string connectionString = DbHelperSQL.strConn; // 参数验证 if (string.IsNullOrEmpty(entity.userName)) throw new ArgumentNullException(nameof(entity.userName), "用户名不允许为空"); if (string.IsNullOrEmpty(entity.sectionCode)) throw new ArgumentNullException(nameof(entity.sectionCode), "库位编号不允许为空"); if (string.IsNullOrEmpty(entity.barcode)) throw new ArgumentNullException(nameof(entity.barcode), "条码不允许为空"); if (string.IsNullOrEmpty(entity.cgrkType)) throw new ArgumentNullException(nameof(entity.cgrkType), "采购入库类型不允许为空"); using (var conn = new SqlConnection(connectionString)) { using (var cmd = new SqlCommand("[prc_pda_inv_cgrk]", conn)) { try { conn.Open(); cmd.CommandType = CommandType.StoredProcedure; // 设置存储过程参数 SqlParameter[] parameters = { new SqlParameter("@pi_user", SqlDbType.NVarChar, 100) { Value = entity.userName }, new SqlParameter("@pi_barcode", SqlDbType.NVarChar, 100) { Value = entity.barcode.Trim().ToLower() }, new SqlParameter("@pi_sectionCode", SqlDbType.NVarChar, 30) { Value = entity.sectionCode }, new SqlParameter("@pi_cgrkType", SqlDbType.NVarChar, 30) { Value = entity.cgrkType }, new SqlParameter("@po_outMsg", SqlDbType.NVarChar, 300) { Direction = ParameterDirection.Output }, new SqlParameter("@po_outSum", SqlDbType.NVarChar, 300) { Direction = ParameterDirection.Output } }; cmd.Parameters.AddRange(parameters); cmd.ExecuteNonQuery(); // 获取输出参数 string? outMessage = parameters[4].Value?.ToString(); string? outSum = parameters[5].Value?.ToString(); if (string.IsNullOrEmpty(outMessage) || string.IsNullOrEmpty(outSum)) throw new InvalidOperationException("存储过程返回参数异常"); // 验证结果 if (!int.TryParse(outSum, out int result) || result <= 0) throw new InvalidOperationException(outMessage); // 调试输出 Console.WriteLine($"查询的条码值: '{entity.barcode}' (长度: {entity.barcode.Length})"); // 使用参数化查询并忽略大小写（适用于支持的数据库） var itemBarcodeDetails = Db.Queryable() .Where(it => it.ItemBarcode.Trim().ToLower() == entity.barcode.Trim().ToLower()) .First() ?? throw new InvalidOperationException($"未找到条码 '{entity.barcode}' 的匹配信息"); // 调试输出（检查值和长度） Console.WriteLine($"查询的条码值: '{entity.barcode}' (长度: {entity.barcode.Length})"); // 优化查询（去除空格并忽略大小写） var MesInvItemInCDetails = Db.Queryable() .Where(it => it.ItemBarcode.Trim().ToLower() == entity.barcode.Trim().ToLower()) .First() ?? throw new InvalidOperationException($"未找到条码 '{entity.barcode}' 的匹配的入库信息"); // 从入库条码中获取有效的ItemInId var cId = MesInvItemInCDetails.ItemInId; // 假设存在ItemInId属性 // 查询物料汇总明细 var sql = string.Format(@" SELECT b.Item_No AS ItemNo, b.Item_Name AS ItemName, b.Item_Model AS ItemModel, a.Quantity AS FQty, b.Item_Id AS FMaterialId, CONVERT(VARCHAR(36), b.Guid) AS Id FROM MES_INV_ITEM_IN_C_ITEMS a LEFT JOIN Mes_Items b ON a.Item_Id = b.Item_Id WHERE a.Item_In_Id = '{0}' AND a.Item_Id = '{1}'", cId, itemBarcodeDetails.ItemId); res.ItemInDetails = Db.Ado.SqlQuery(sql); // 查询入库条码明细 var sql2 = string.Format(@" SELECT b.Item_No AS ItemNo, b.Item_Name AS ItemName, b.Item_Model AS ItemModel, a.Quantity AS FQty, b.Item_Id AS FMaterialId, CONVERT(VARCHAR(36), a.Guid) AS Id, a.Depot_Section_Code AS kw, a.Item_Barcode AS barcode FROM Mes_Inv_Item_In_C_Details a LEFT JOIN Mes_Items b ON a.Item_Id = b.Item_Id WHERE a.Item_In_Id = '{0}' AND a.Item_Id = '{1}'",cId, itemBarcodeDetails.ItemId); res.ItemBarCDetails = Db.Ado.SqlQuery(sql2); // 设置返回信息 res.Message = outMessage; res.SumQuantity = result; return res; } catch (Exception ex) { // 记录异常日志（建议添加日志记录） res.Message = $"操作失败: {ex.Message}"; res.SumQuantity = -1; return res; } } } } //public ItemInBaseModel SaveBarCodes(WarehouseQuery entity) //{ // string functionName = "采购入库", fieldName = null, inFieldVal = null; // string user = entity.userName, // sectionCode = entity.sectionCode, // itemBarcode = entity.barcode; // var transactionNo = "101"; // int? billTypeId = 100, num = 0, num2 = 0; // var freeze = 0; // decimal cSyQty = 0; // if (string.IsNullOrEmpty(sectionCode)) // throw new Exception("请扫库位条码！"); // var itemBarcodeDetails = Db.Queryable() // .Where(it => it.ItemBarcode == itemBarcode) // .First(); // if (itemBarcodeDetails == null) // throw new Exception("条码不存在，请核对！"); // if (string.IsNullOrEmpty(itemBarcodeDetails.Memo) || itemBarcodeDetails.Memo != "采购入库") // throw new Exception("此条码不属于到货条码，无法用采购入库！"); // var inventory = Db.Queryable() // .Where(it => it.BillNo == itemBarcodeDetails.BillNo && it.Fstatus == true) // .First(); // if (inventory == null) // throw new Exception("此条码找不到对应收货单或未审核！"); // // var inventoryDetails = Db.Queryable() // // .Where(it => it.ParentGuid == inventory.Guid // // && it.ItemId == itemBarcodeDetails.ItemId // // && it.EbelnLine == itemBarcodeDetails.WorkLine // // && it.Ebeln == itemBarcodeDetails.WorkNo) // // .First(); // var inventoryDetails = Db.Queryable() // .Where(it => it.Guid == itemBarcodeDetails.AboutGuid) // .First(); // var cgddDetails = Db.Queryable() // .Where(it => it.EbelnK3id == inventoryDetails.LineK3id) // .First(); // if (inventoryDetails == null) // throw new Exception("此条码找不到对应收货单明细！"); // //获取到货检验明细 // var sqlParams = new List { new("@dhmxGuid", itemBarcodeDetails.AboutGuid) }; // var sql1 = @"SELECT *FROM v_dhmx WHERE dhmxGuid = @dhmxGuid "; // var dhjymx = Db.Ado.SqlQuery(sql1, sqlParams).First(); // //判定检验能否入库 // if (dhjymx.CanStore != 1) // { // throw new Exception($"该条码对应的收料单检验结果:【{dhjymx.InspectionResult}】,判定结果：【{dhjymx.JudgmentResult}】，处理意见：【{dhjymx.HandlingSuggestion}】,无法入库"); // } // var depotCode = Db.Queryable() // .Where(it => it.DepotSectionCode == sectionCode) // .Select(it => it.DepotGuid) // .First(); // var depotCode2 = Db.Queryable() // .Where(it => it.BillNo == itemBarcodeDetails.BillNo) // .Select(it => it.DepotsId) // .First(); // MesDepots mesDepost = null; // var checkGuid = UtilityHelper.CheckGuid(depotCode); // if (checkGuid && depotCode != null) // { // mesDepost = Db.Queryable() // .Where(s => s.DepotId.ToString() == depotCode).First(); // // if (depotCode != mesDepost.Guid) // // throw new Exception("扫描库位与采购入库库位不一致！"); // } // else // { // throw new Exception("库位编码 " + sectionCode + " 不存在，请确认！"); // } // //num = Db.Queryable() // // .Where(it => it.ItemBarcode == itemBarcode) // // .Count(); // //if (num > 0) // // throw new Exception("此条码已扫码入库完成，请核对！"); // //num = Db.Queryable() // // .Where(it => it.ItemBarcode == itemBarcode) // // .Count(); // //if (num > 0) // // throw new Exception("此条码已扫码入库，请核对！"); // var wmsManager = new WmsBaseMangeer(); // // 新增入库校验（调用存储过程） // var checkResult = wmsManager.pdaInvJY( // db: Db, // edtUserNo: entity.userName, // barcode: entity.barcode, // sectionCode: sectionCode, // stockId: mesDepost.DepotId.ToString(), // stockOrgId: mesDepost.FSubsidiary, // billNo: "", // 根据实际单据号传值 // transactionNo: "101" // 事务类型与存储过程匹配 // ); // if (Convert.ToInt32(checkResult.result) < 1) // throw new Exception($"入库校验失败：{checkResult.strMsg}"); // //num = Db.Queryable() // // .Where(a => a.BillNo == itemBarcodeDetails.BillNo) // // .InnerJoin((a, b) => // // b.ParentGuid == a.Guid && // // b.ItemId == itemBarcodeDetails.ItemId && b.Ischeck == 1) // // .Count(); // //num2 = Db.Queryable() // // .Where(a => a.BillNo == itemBarcodeDetails.BillNo) // // .InnerJoin((a, b) => b.ParentGuid == a.Guid // // && b.ItemId == itemBarcodeDetails.ItemId // // && (b.CheckRes == "合格" || b.CheckStates == "特采直接使用")) // // .Count(); // //switch (num) // //{ // // case 0 when num2 == 0: // // case > 0 when num2 == 0: // // freeze = 1; // // break; // // case 0 when num2 > 0: // // throw new Exception("入库失败,到货单据有问题！"); // //} // // 判断货主类型 // var owner_type = wmsManager.GetOwnerType(cgddDetails.DemandOrg); // // if (Db.Queryable().Any(x => x.Fid == cgddDetails.DemandOrg)) // // { // // owner_type = "BD_OwnerOrg"; // // } // // else // // { // // // 第二层判断：检查 MES_CUSTOMER // // if (Db.Queryable().Any(x => x.Id == Convert.ToInt32(cgddDetails.DemandOrg))) // // { // // owner_type = "BD_Customer"; // // } // // else // // { // // // 第三层判断：检查 MES_SUPPLIER // // if (Db.Queryable().Any(x => x.Id == Convert.ToInt32(cgddDetails.DemandOrg))) // // { // // owner_type = "BD_Supplier"; // // } // // else // // { // // // 第四层判断：再次检查 SYS_ORGANIZATION // // if (Db.Queryable().Any(x => x.Fid == cgddDetails.ReceivingOrg)) // // { // // owner_type = "BD_OwnerOrg"; // // } // // else // // { // // throw new Exception("入库失败,到货单据对应的需求组织有问题！"); // // } // // } // // } // //} // var result = new ItemInBaseModel(); // UseTransaction(db => // { // var existingRecord = db.Queryable() // .Where(it => it.InsDate.Value.ToString("yyyy-MM-dd") == DateTime.Now.ToString("yyyy-MM-dd") // && it.TaskNo == itemBarcodeDetails.BillNo // && it.Status == 0 // && it.TransctionNo == transactionNo // && it.CreateBy == entity.userName // && it.BillTypeId == billTypeId // && it.DepotsId == mesDepost.DepotId) // .Select(it => new // { // it.Guid, // it.BillNo // }) // .First(); // var cId = Guid.Empty; // string cBillNo = null; // if (existingRecord != null) // { // // If record exists, assign values // cId = existingRecord.Guid; // cBillNo = existingRecord.BillNo; // } // else // { // cId = Guid.NewGuid(); // cBillNo = BillNo.GetBillNo("CGRK(采购入库)"); // var suppNo = db.Queryable() // .Where(s => s.Id.ToString() == inventory.SuppId).First(); // db.Insertable(new MesInvItemIns // { // Guid = cId, // BillNo = cBillNo, // BillTypeId = billTypeId, // InsDate = DateTime.Now, // DepotsId = mesDepost.DepotId, // DepotsCode = mesDepost.DepotCode, // TransctionNo = transactionNo, // SuppId = suppNo.Id.ToString(), // SuppNo = suppNo.SuppNo, // CreateBy = user, // CreateDate = DateTime.Now, // LastupdateBy = user, // LastupdateDate = DateTime.Now, // UrgentFlag = itemBarcodeDetails.UrgentFlag.Value // ? "1" // : "0", // CbillNo = itemBarcodeDetails.BillNo, // Fstatus = 0, // Status = 0, // ReceiveOrgId = inventory.ReceiveOrgId, // InType = "采购入库", // TaskNo = itemBarcodeDetails.BillNo // //WorkNo = itemBarcodeDetails.WorkNo // }).IgnoreColumns(true).ExecuteCommand(); // } // cSyQty = itemBarcodeDetails.Quantity.Value; // entity.id = cId.ToString(); // entity.PageIndex = 1; // entity.Limit = 1; // itemBarcodeDetails.Hbdy ??= 0; // if (itemBarcodeDetails.Hbdy == 1) // { // var details = db.Queryable() // .Where(d => // d.CbillNo == itemBarcodeDetails.BillNo && // d.Quantity != d.OkRkqty && // d.ItemId == itemBarcodeDetails.ItemId) // .ToList(); // foreach (var detail in details) // { // if (cSyQty == 0) break; // var remainingQty = detail.Quantity - (detail.OkRkqty ?? 0); // if (remainingQty <= cSyQty) // { // // Update detail // db.Updateable() // .SetColumns(d => new MesInvItemArnDetail // { // OkRkqty = (int)d.Quantity, // IsdepsIn = true // }) // .Where(d => d.Guid == detail.Guid) // .IgnoreColumns(true) // .ExecuteCommand(); // // Check if already inserted // var count = db.Queryable() // .Where(it => // it.ItemInId == cId && // it.ItemId == detail.ItemId && // it.DepotId == depotCode2 && // it.EbelnK3id == detail.EbelnK3id && // it.LineK3id == detail.LineK3id) // .Count(); // cSyQty -= remainingQty.Value; // if (count == 0) // // Insert new item into MES_INV_ITEM_IN_C_ITEMS // db.Insertable(new MesInvItemInCItems // { // ItemInId = cId, // Quantity = remainingQty, // CreateBy = user, // CreateDate = DateTime.Now, // ItemId = detail.ItemId, // DepotCode = mesDepost.DepotCode, // ItemSname = itemBarcodeDetails.ItemSname, // Unit = itemBarcodeDetails.Unit, // Ebeln = detail.Ebeln, // BillNo = cBillNo, // WorkNo = detail.WorkNo, // CbillNo = detail.CbillNo, // WorkLine = detail.WorkLine, // SuppId = itemBarcodeDetails.SuppId, // SuppNo = itemBarcodeDetails.SuppNo, // Remark = itemBarcodeDetails.Memo, // EbelnK3id = detail.EbelnK3id, // LineK3id = detail.LineK3id, // DepotId = depotCode2, // itemDabid = itemBarcodeDetails.AboutGuid.ToString() // }).IgnoreColumns(true).ExecuteCommand(); // else // db.Updateable() // .SetColumns(it => // it.Quantity == it.Quantity + remainingQty) // .Where(it => // it.ItemInId == cId && // it.ItemId == detail.ItemId && // it.DepotId == depotCode2 && // it.EbelnK3id == detail.EbelnK3id && // it.LineK3id == detail.LineK3id) // .IgnoreColumns(true) // .ExecuteCommand(); // // Insert new detail into MES_INV_ITEM_IN_C_DETAILS // db.Insertable(new MesInvItemInCDetails // { // ItemInId = cId, // BillNo = cBillNo, // ItemBarcode = itemBarcode, // Quantity = remainingQty, // BarcodeFlag = true, // EpFlag = true, // WorkType = 1, // ItemNo = detail.ItemNo, // SuppId = itemBarcodeDetails.SuppId, // SuppNo = itemBarcodeDetails.SuppNo, // DepotCode = mesDepost.DepotCode, // DepotSectionCode = sectionCode, // ItemSname = itemBarcodeDetails.ItemSname, // Unit = itemBarcodeDetails.Unit, // CreateBy = user, // CreateDate = DateTime.Now, // LastupdateBy = user, // LastupdateDate = DateTime.Now, // Remark = itemBarcodeDetails.Memo, // Ebeln = detail.WorkNo, // WorkNo = detail.WorkNo, // WorkLine = detail.WorkLine, // CbillNo = itemBarcodeDetails.BillNo, // UrgentFlag = detail.UrgentFlag, // BoardStyle = detail.BoardStyle, // TaskNo = detail.WorkNo, // EbelnK3id = detail.EbelnK3id, // LineK3id = detail.LineK3id, // ItemId = detail.ItemId, // ReceiveOrgId = inventory.ReceiveOrgId, // LotNo = cgddDetails.BatchNumber // }).IgnoreColumns(true).ExecuteCommand(); // } // else // { // // Partially fulfill remaining quantity // db.Updateable() // .SetColumns(d => new MesInvItemArnDetail // { // OkRkqty = (int)((d.OkRkqty ?? 0) + cSyQty) // }) // .Where(d => d.Guid == detail.Guid) // .IgnoreColumns(true) // .ExecuteCommand(); // var count = db.Queryable() // .Where(it => // it.ItemInId == cId && // it.ItemId == detail.ItemId && // it.DepotId == depotCode2 && // it.EbelnK3id == detail.EbelnK3id && // it.LineK3id == detail.LineK3id) // .Count(); // if (count == 0) // db.Insertable(new MesInvItemInCItems // { // ItemInId = cId, // Quantity = cSyQty, // CreateBy = user, // CreateDate = DateTime.Now, // ItemNo = detail.ItemNo, // DepotCode = mesDepost.DepotCode, // ItemSname = itemBarcodeDetails.ItemSname, // Unit = itemBarcodeDetails.Unit, // Ebeln = detail.WorkNo, // BillNo = cBillNo, // WorkNo = detail.WorkNo, // EbelnLineNo = detail.EbelnLine, // CbillNo = detail.CbillNo, // WorkLine = detail.WorkLine, // SuppId = itemBarcodeDetails.SuppId, // SuppNo = itemBarcodeDetails.SuppNo, // Remark = itemBarcodeDetails.Memo, // EbelnK3id = detail.EbelnK3id, // LineK3id = detail.LineK3id, // ItemId = detail.ItemId, // DepotId = depotCode2 // }).IgnoreColumns(true).ExecuteCommand(); // else // db.Updateable() // .SetColumns(it => // it.Quantity == it.Quantity + cSyQty) // .Where(it => // it.ItemInId == cId && // it.ItemId == detail.ItemId && // it.DepotId == depotCode2 && // it.EbelnK3id == detail.EbelnK3id && // it.LineK3id == detail.LineK3id) // .IgnoreColumns(true) // .ExecuteCommand(); // db.Insertable(new MesInvItemInCDetails // { // ItemInId = cId, // BillNo = cBillNo, // ItemBarcode = itemBarcode, // Quantity = cSyQty, // BarcodeFlag = true, // EpFlag = true, // WorkType = 1, // ItemNo = detail.ItemNo, // SuppId = itemBarcodeDetails.SuppId, // SuppNo = itemBarcodeDetails.SuppNo, // DepotCode = mesDepost.DepotCode, // DepotSectionCode = sectionCode, // ItemSname = itemBarcodeDetails.ItemSname, // Unit = itemBarcodeDetails.Unit, // CreateBy = user, // CreateDate = DateTime.Now, // LastupdateBy = user, // LastupdateDate = DateTime.Now, // Remark = itemBarcodeDetails.Memo, // Ebeln = detail.WorkNo, // EbelnLineNo = detail.EbelnLine, // WorkNo = detail.WorkNo, // WorkLine = detail.WorkLine, // CbillNo = itemBarcodeDetails.BillNo, // UrgentFlag = detail.UrgentFlag, // BoardStyle = detail.BoardStyle, // TaskNo = detail.WorkNo, // EbelnK3id = detail.EbelnK3id, // LineK3id = detail.LineK3id, // ItemId = detail.ItemId, // ReceiveOrgId = inventory.ReceiveOrgId, // LotNo = cgddDetails.BatchNumber // }).IgnoreColumns(true).ExecuteCommand(); // cSyQty = 0; // Remaining quantity fulfilled // } // } // db.Insertable(new MesInvBusiness2 // { // Status = 1, // BillTypeId = billTypeId, // TransactionCode = transactionNo, // BusinessType = 1, // ItemBarcode = itemBarcode, // ItemNo = itemBarcodeDetails.ItemNo, // LotNo = cgddDetails.BatchNumber, // EpFlag = true, // Quantity = itemBarcodeDetails.Quantity, // ToInvDepotsCode = mesDepost.DepotCode, // ToInvDepotSectionsCode = sectionCode, // Description = "采购入库", // CreateBy = user, // CreateDate = DateTime.Now, // LastupdateBy = user, // LastupdateDate = DateTime.Now, // TaskNo = itemBarcodeDetails.BillNo, // BillNo = cBillNo, // WorkNo = itemBarcodeDetails.WorkNo, // WorkLine = itemBarcodeDetails.WorkLine, // SuppId = itemBarcodeDetails.SuppId, // SuppNo = itemBarcodeDetails.SuppNo, // EbelnK3id = itemBarcodeDetails.EbelnK3id, // LineK3id = itemBarcodeDetails.LineK3id, // ItemId = itemBarcodeDetails.ItemId // }).IgnoreColumns(true).ExecuteCommand(); // // Insert into mes_inv_item_stocks // } // else // { // var detailone = db.Queryable() // .Where(d => // d.Guid == itemBarcodeDetails.AboutGuid) // .First(); // // 检查是否存在于 MES_INV_ITEM_IN_C_ITEMS 表 // var existingCount = db.Queryable() // .Where(it => // it.ItemInId == cId && // it.ItemId == detailone.ItemId && // it.DepotId == depotCode2 && // it.itemDabid == itemBarcodeDetails.AboutGuid.ToString()) // .Count(); // if (existingCount == 0) // // 不存在时插入新记录 // db.Insertable(new MesInvItemInCItems // { // ItemInId = cId, // Quantity = itemBarcodeDetails.Quantity, // CreateBy = user, // CreateDate = DateTime.Now, // ItemNo = itemBarcodeDetails.ItemNo, // DepotCode = mesDepost.DepotCode, // ItemSname = itemBarcodeDetails.ItemSname, // Unit = itemBarcodeDetails.Unit, // Ebeln = itemBarcodeDetails.WorkNo, // BillNo = cBillNo, // WorkNo = itemBarcodeDetails.WorkNo, // EbelnLineNo = itemBarcodeDetails.WorkLine, // CbillNo = itemBarcodeDetails.BillNo, // WorkLine = itemBarcodeDetails.WorkLine, // SuppId = itemBarcodeDetails.SuppId, // SuppNo = itemBarcodeDetails.SuppNo, // Remark = itemBarcodeDetails.Memo, // EbelnK3id = itemBarcodeDetails.EbelnK3id, // LineK3id = itemBarcodeDetails.LineK3id, // ItemId = itemBarcodeDetails.ItemId, // DepotId = depotCode2, // itemDabid = itemBarcodeDetails.AboutGuid.ToString() // }).IgnoreColumns(true).ExecuteCommand(); // else // // 存在时更新数量 // db.Updateable() // .SetColumns(it => new MesInvItemInCItems // { // Quantity = SqlFunc.IsNull(it.Quantity, 0) + itemBarcodeDetails.Quantity // 确保 Quantity 不为 null // }) // .Where(it => // it.ItemInId == cId && // it.ItemId == detailone.ItemId && // it.DepotId == depotCode2 && // it.itemDabid == itemBarcodeDetails.AboutGuid.ToString()) // //.IgnoreColumns(true) // 保留 IgnoreColumns // .ExecuteCommand(); // // 插入 mes_inv_item_in_c_details 表 // db.Insertable(new MesInvItemInCDetails // { // ItemInId = cId, // BillNo = cBillNo, // ItemBarcode = itemBarcode, // Quantity = itemBarcodeDetails.Quantity, // BarcodeFlag = true, // EpFlag = true, // WorkType = 1, // ItemNo = itemBarcodeDetails.ItemNo, // LotNo = cgddDetails.BatchNumber, // SuppId = itemBarcodeDetails.SuppId, // SuppNo = itemBarcodeDetails.SuppNo, // DepotCode = mesDepost.DepotCode, // DepotSectionCode = sectionCode, // ItemSname = itemBarcodeDetails.ItemSname, // Unit = itemBarcodeDetails.Unit, // CreateBy = user, // CreateDate = DateTime.Now, // LastupdateBy = user, // LastupdateDate = DateTime.Now, // Remark = itemBarcodeDetails.Memo, // Ebeln = itemBarcodeDetails.Mblnr, // EbelnLineNo = itemBarcodeDetails.Zeile, // WorkNo = itemBarcodeDetails.WorkNo, // WorkLine = itemBarcodeDetails.WorkLine, // CbillNo = itemBarcodeDetails.BillNo, // UrgentFlag = itemBarcodeDetails.UrgentFlag, // BoardStyle = itemBarcodeDetails.BoardStyle, // TaskNo = itemBarcodeDetails.TaskNo, // EbelnK3id = itemBarcodeDetails.EbelnK3id, // LineK3id = itemBarcodeDetails.LineK3id, // ItemId = itemBarcodeDetails.ItemId, // Ischeck = true, // CheckDate = inventoryDetails.CheckDate, // CheckRes = inventoryDetails.CheckRes, // CheckStates = inventoryDetails.CheckStates, // ReceiveOrgId = inventory.ReceiveOrgId, // DepotId = Convert.ToInt64(depotCode2) // }).IgnoreColumns(true).ExecuteCommand(); // // 插入 mes_inv_business2 表 // db.Insertable(new MesInvBusiness2 // { // Status = 1, // BillTypeId = billTypeId, // TransactionCode = transactionNo, // BusinessType = 1, // ItemBarcode = itemBarcode, // ItemNo = itemBarcodeDetails.ItemNo, // LotNo = itemBarcodeDetails.LotNo, // EpFlag = true, // Quantity = itemBarcodeDetails.Quantity, // FromInvDepotsCode = null, // FromInvDepotSectionsCode = null, // ToInvDepotsCode = mesDepost.DepotCode, // ToInvDepotSectionsCode = sectionCode, // Description = "采购入库", // CreateBy = user, // CreateDate = DateTime.Now, // LastupdateBy = user, // LastupdateDate = DateTime.Now, // TaskNo = itemBarcodeDetails.TaskNo, // BillNo = cBillNo, // WorkNo = itemBarcodeDetails.WorkNo, // WorkLine = itemBarcodeDetails.WorkLine, // SuppId = itemBarcodeDetails.SuppId, // SuppNo = itemBarcodeDetails.SuppNo, // EbelnK3id = itemBarcodeDetails.EbelnK3id, // LineK3id = itemBarcodeDetails.LineK3id, // ItemId = itemBarcodeDetails.ItemId // }).IgnoreColumns(true).ExecuteCommand(); // // 插入 mes_inv_item_stocks 表 // } // db.Insertable(new MesInvItemStocks // { // TaskNo = itemBarcodeDetails.TaskNo, // ItemBarcode = itemBarcode, // ItemNo = cgddDetails.BatchNumber, // LotNo = itemBarcodeDetails.LotNo, // Quantity = itemBarcodeDetails.Quantity, // DepotsCode = mesDepost.DepotCode, // DepotSectionsCode = sectionCode, // CheckDate = inventoryDetails.CheckDate, // IndepDate = DateTime.Now, // IqcStatus = inventoryDetails.CheckStates, // BoardStyle = itemBarcodeDetails.BoardStyle, // WorkNo = itemBarcodeDetails.WorkNo, // WorkLine = itemBarcodeDetails.WorkLine, // SuppId = itemBarcodeDetails.SuppId, // SuppNo = itemBarcodeDetails.SuppNo, // EbelnK3id = itemBarcodeDetails.EbelnK3id, // LineK3id = itemBarcodeDetails.LineK3id, // ItemId = itemBarcodeDetails.ItemId, // BillNo = itemBarcodeDetails.BillNo, // DepotId = Convert.ToInt32(depotCode2), // OwnerId = cgddDetails.DemandOrg, // OwnerType = owner_type, // StockOrgId = mesDepost.FSubsidiary, // IndepUserCode = user // }).IgnoreColumns(true).ExecuteCommand(); // itemBarcodeDetails.Hbdy ??= 0; // if (itemBarcodeDetails.Hbdy != 1) // { // //更新收料单明细 // db.Updateable() // .SetColumns(d => new MesInvItemArnDetail // { // OkRkqty = (int)((d.OkRkqty ?? 0) + cSyQty) // }) // .Where(d => d.Guid == itemBarcodeDetails.AboutGuid) // .ExecuteCommand(); // var first = db // .Queryable() // .Where(b => // b.Guid == itemBarcodeDetails.AboutGuid) // .Select(b => new // { // TotalQuantity = // SqlFunc.AggregateSum(b.Quantity), // TotalOkRkQty = SqlFunc.AggregateSum(b.OkRkqty) // }) // .First(); // var TotalQuantity = first.TotalQuantity ?? 0; // var TotalOkRkQty = first.TotalOkRkQty ?? 0; // if (TotalQuantity == TotalOkRkQty) // db.Updateable() // .SetColumns(s => s.IsdepsIn == true) // .Where(s => s.Guid == itemBarcodeDetails.AboutGuid) // .ExecuteCommand(); // var totalSummary = db.Queryable() // .InnerJoin((b, a) => a.Guid == b.ParentGuid) // .Where((b, a) => // a.BillNo == inventory.BillNo && b.ReturnFlag == 0) // .Select((b, a) => new // { // TotalQuantity = SqlFunc.AggregateSum(b.Quantity), // TotalOkRkQty = SqlFunc.AggregateSum(b.OkRkqty) // }) // .First(); // if ((totalSummary.TotalQuantity ?? 0) == // (totalSummary.TotalOkRkQty ?? 0)) // db.Updateable() // .SetColumns(it => it.Status == 1) // .Where(it => it.BillNo == itemBarcodeDetails.BillNo) // .ExecuteCommand(); // var totalCDetailsQuantity = db.Queryable() // .Where(it => it.ItemId == itemBarcodeDetails.ItemId && // it.WorkNo == itemBarcodeDetails.WorkNo && // it.ItemInId == cId) // .Sum(it => it.Quantity); // var detailSummary = db.Queryable() // .Where(it => it.Guid == inventoryDetails.Guid) // .Select(it => new // { // TotalComeQty = SqlFunc.AggregateSum(it.Quantity), // TotalInvQty = SqlFunc.AggregateSum(it.OkRkqty) // }) // .First(); // var comeQty = detailSummary.TotalComeQty ?? 0; // var invQty = detailSummary.TotalInvQty ?? 0; // var diffQty = comeQty - invQty; // if (detailSummary == null) // throw new Exception("此条码找不到对应收货单明细！"); // var mesItems = db.Queryable() // .Where(s => s.Id == itemBarcodeDetails.ItemId).First(); // //result.Message = // // $"条码数量 {itemBarcodeDetails.Quantity},采购订单 {itemBarcodeDetails.WorkNo} 项次 {itemBarcodeDetails.WorkLine} 物料 {mesItems.ItemNo} 本次入库总数：{totalCDetailsQuantity} 总到 {comeQty} 已入 {invQty} 欠 {diffQty}"; // result.Message = $"物料{mesItems.ItemNo}入库成功数量{itemBarcodeDetails.Quantity.ToString()}"; // result.itemDetail = mesItems; // result.ItemInDetails = db.Queryable( // (a, b) => new JoinQueryInfos( // JoinType.Left, // a.ItemId == b.ItemId) // ) // .Where(a => a.ItemInId == cId && a.ItemId == itemBarcodeDetails.ItemId) // .Select((a, b) => new // { // ItemNo = b.ItemNo, // ItemName = b.ItemName, // ItemModel = b.ItemModel, // FQty = a.Quantity, // 申请数量 // FMaterialId = b.ItemId, // Id = b.Guid.ToString() // }) // .ToList(); // result.ItemBarCDetails = db.Queryable( // (a, b) => new JoinQueryInfos( // JoinType.Left, // a.ItemId == b.ItemId) // ) // .Where(a => a.ItemInId == cId && a.ItemId == itemBarcodeDetails.ItemId) // .Select((a, b) => new // { // ItemNo = b.ItemNo, // ItemName = b.ItemName, // ItemModel = b.ItemModel, // FQty = a.Quantity, // 申请数量 // FMaterialId = b.ItemId, // Id = a.Guid.ToString(), // kw = a.DepotSectionCode, // barcode = a.ItemBarcode // }) // .ToList(); // //result.ItemNo = mesItems.ItemNo; // result.SumQuantity = Convert.ToDecimal(invQty); // return 1; // } // else // { // // Step 1: Check if bill needs to be closed (status update) // var totalSummary = db.Queryable() // .LeftJoin((b, a) => a.Guid == b.ParentGuid) // .Where((b, a) => // a.BillNo == inventory.BillNo && b.ReturnFlag == 0) // .Select((b, a) => new // { // TotalQuantity = SqlFunc.AggregateSum(b.Quantity), // TotalOkRkQty = SqlFunc.AggregateSum(b.OkRkqty) // }) // .First(); // if ((totalSummary.TotalQuantity ?? 0) == // (totalSummary.TotalOkRkQty ?? 0)) // db.Updateable() // .SetColumns(it => it.Status == 1) // .Where(it => it.BillNo == itemBarcodeDetails.BillNo) // .ExecuteCommand(); // // Step 2: Calculate total sum from `mes_inv_item_in_c_details` // var totalCDetailsQuantity = db.Queryable() // .Where(it => it.Guid == itemBarcodeDetails.AboutGuid) // .Sum(it => it.Quantity); // // Step 3: Calculate comeQty and invQty from `mes_inv_item_arn_detail` // var detailSummary = db.Queryable() // .Where(it => it.Guid == itemBarcodeDetails.AboutGuid) // .Select(it => new // { // TotalComeQty = SqlFunc.AggregateSum(it.Quantity), // TotalInvQty = SqlFunc.AggregateSum(it.OkRkqty) // }) // .First(); // if (detailSummary == null) // throw new Exception("此条码找不到对应收货单明细！"); // var comeQty = detailSummary.TotalComeQty ?? 0; // var invQty = detailSummary.TotalInvQty ?? 0; // var diffQty = comeQty - invQty; // var mesItems = db.Queryable() // .Where(s => s.Id == itemBarcodeDetails.ItemId).First(); // // Step 5: Combine final result // result.Message = // $" 条码数量:{itemBarcodeDetails.Quantity},物料 {mesItems.ItemNo} 本次入库总数：{totalCDetailsQuantity} 总到 {comeQty} 已入 {invQty} 欠 {diffQty}"; // //result.ItemNo = mesItems.ItemNo; // result.SumQuantity = Convert.ToDecimal(totalCDetailsQuantity); // } // var mesInvItemInCDetails = // base.GetSingle(it => it.ItemBarcode == entity.barcode); // if (mesInvItemInCDetails == null) // throw new Exception("物料入库条码明细不存在"); // 抛出异常以供前台处理 // // 创建 插入日志 // var logService = new LogService(); // var LogMsg = "【PDA】采购入库。条码【" + entity.barcode + "】 入库单号【" + cBillNo + "】"; // logService.CreateLog(db, entity.userName, inventory.Guid.ToString(), "MES_INV_ITEM_ARN", LogMsg, inventory.BillNo); // return 1; // }); // var purchaseInventory = getPurchaseInventory(entity); // //result.ItemInDetails = purchaseInventory.ItemInDetails; // //result.InvItemInCDetails = purchaseInventory.InvItemInCDetails; // return result; //} public ItemInBaseModel getPurchaseInventory(WarehouseQuery query) { return new ItemInBaseModel { //ItemIns = GetInvItemInsList(query).Items[0], //ItemInDetails = GetItemInDetails(query.id), //InvItemInCDetails = GetInvItemInCDetails(query.id) }; } public (List Items, int TotalCount) GetInvItemInsList( WarehouseQuery query) { var parsedGuid = Guid.Empty; if (!string.IsNullOrEmpty(query.id)) { var isValid = Guid.TryParse(query.id, out parsedGuid); if (!isValid) throw new ApplicationException("GUID转换错误"); } var totalCount = 0; var result = Db.Queryable( (a, b, c) => new JoinQueryInfos(JoinType.Left, a.DepotsId == b.DepotId, JoinType.Left, a.SuppId == c.Id.ToString())) .WhereIF(UtilityHelper.CheckGuid(parsedGuid), (a, b, c) => a.Guid == parsedGuid) .Select((a, b, c) => new MesInvItemIns { Guid = a.Guid, SuppNo = a.SuppNo, InsDate = a.InsDate, PaperBillNo = a.PaperBillNo, Remark = a.Remark, DepotsCode = a.DepotsCode, CbillNo = a.CbillNo, Status = a.Status, BillNo = a.BillNo, CreateDate = a.CreateDate, CreateBy = a.CreateBy, DepotName = b.DepotName, SuppName = c.SuppName }).ToPageList(query.PageIndex, query.Limit, ref totalCount); return (result, totalCount); } public List GetItemInDetails(string? pid) { //string转guid var parsedGuid = Guid.Empty; if (string.IsNullOrEmpty(pid)) return []; var isValid = Guid.TryParse(pid, out parsedGuid); if (!isValid) throw new ApplicationException("GUID转换错误"); var result = Db.Queryable( (g, c, d) => new JoinQueryInfos( JoinType.Left, g.ItemId == c.Id, JoinType.Inner, d.Id == Convert.ToDecimal(g.Unit) ) ).WhereIF(UtilityHelper.CheckGuid(parsedGuid), (g, c, d) => g.ItemInId == parsedGuid) .Select((g, c, d) => new MesInvItemInCItems { Guid = g.Guid, WorkNo = g.WorkNo, ItemNo = c.ItemNo, ItemSname = g.ItemSname, Quantity = g.Quantity, Unit = d.Fname }) .ToList(); return result; } public List GetInvItemInCDetails(string? pid) { //string转guid var parsedGuid = Guid.Empty; if (string.IsNullOrEmpty(pid)) return []; var isValid = Guid.TryParse(pid, out parsedGuid); if (!isValid) throw new ApplicationException("GUID转换错误"); var result = Db.Queryable( (g, c, d) => new JoinQueryInfos( JoinType.Left, g.ItemId == c.Id, JoinType.Inner, d.Id.ToString() == c.Fpurchaseunitid ) ).WhereIF(UtilityHelper.CheckGuid(parsedGuid), (g, c, d) => g.ItemInId == parsedGuid) .Select((g, c, d) => new MesInvItemInCDetails { Guid = g.Guid, ItemName = c.ItemName, ItemModel = c.ItemModel, ItemUnit = d.Fname, DepotSectionCode = g.DepotSectionCode, CbillNo = g.CbillNo, ItemBarcode = g.ItemBarcode, UrgentFlag = g.UrgentFlag, ItemNo = c.ItemNo, WorkNo = g.WorkNo, Quantity = g.Quantity }) .ToList(); return result; } public MessageCenter MesToErpParam(WarehouseQuery query) { var erpParameters = ""; var title = ""; var tableName = "MES_INV_ITEM_INS_" + query.Type; if ("A".Equals(query.Type)) { erpParameters = GetErpParameters(query.billNo); title = "采购入库单" + query.billNo + "审核"; } else if ("B".Equals(query.Type)) { erpParameters = GetDeApprovePam(query.id); title = "采购入库单" + query.billNo + "反审核"; } var ErpUrl = AppsettingsUtility.Settings.ProductionErpUrl; var newGuid = Guid.NewGuid(); var message = new MessageCenter { Guid = newGuid, //Pid = newGuid, TableName = tableName, Url = ErpUrl, Status = 1, CreateBy = query.userName, Route = query.billNo, Title = title, PageName = "Warehouse/PurchaseInventory/Add?id=" + query.id + "&billNo=" + query.billNo, CreateDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), Method = "POST", Seq = 1, Data = erpParameters, IsMessage = 0, ContentType = "application/x-www-form-urlencoded" }; return message; } public MessageCenter SaveMessageCenter(WarehouseQuery query) { var message = MesToErpParam(query); var executeReturnIdentity = Db.Insertable(message).ExecuteCommand(); if (executeReturnIdentity > 0) { message.Pid = message.Guid; return message; } throw new Exception("获取数据失败"); } //audit public bool audit(WarehouseQuery entity) { entity.date = DateTime.Now; entity.status = 1; return Update(entity); } public bool deApprove(WarehouseQuery entity) { entity.date = null; entity.status = 0; return Update(entity); } private bool Update(WarehouseQuery entity) { return Db.Updateable() .SetColumns(x => x.Status == entity.status) .SetColumns(x => x.InsDate == entity.date) .Where(x => x.BillNo == entity.billNo) .IgnoreColumns(true).ExecuteCommand() > 0; } private string GetErpParameters(string? billNo) { var invItemIns = Db.Queryable() .Single(x => x.BillNo == billNo); if (invItemIns == null) throw new Exception("入库单号不存在"); if (invItemIns.Status == 1) throw new Exception("入库单已审核，不能重复推送"); var materials = Db.Queryable( (g, c, d, a) => new JoinQueryInfos( JoinType.Left, g.ItemId == c.Id, JoinType.Inner, d.Id.ToString() == c.Fpurchaseunitid, JoinType.Inner, a.Ebeln == g.WorkNo && g.CbillNo == a.CbillNo ) ).Where((g, c, d, a) => g.BillNo == billNo).Select( (g, c, d, a) => new Material { FMaterialId = c.ItemNo, FRealQty = g.Quantity.ToString(), FStockId = g.DepotCode, FLot = a.CbillNo, FUnitID = d.Fnumber, FsrcEntryId = a.Guid.ToString(), FMesEntryid = g.Guid.ToString() }).ToList(); if (materials == null || materials.Count == 0) throw new Exception("没有找到相关数据"); // 抛出异常以供前台处理 // 构造 JSON var jsonEntries = materials.Select(d => new { d.FMaterialId, d.FRealQty, d.FStockId, d.FLot, FUintId = d.FUnitID, FSRCENTRYID = d.FsrcEntryId, F_MES_ENTRYID = d.FMesEntryid }).ToList(); var fdate = DateTime.Now.ToString("yyyy-MM-dd"); var jsonString = JsonConvert.SerializeObject(jsonEntries); var encodedUrl = "taskname=CGRK&mesid=" + invItemIns.Guid + "&optype=create&datajson={\"F_MES_ID\":\"" + invItemIns.Guid + "\",\"FDate\":\"" + fdate + "\",\"cgrkentry\":" + jsonString + "}"; return encodedUrl; } private string GetDeApprovePam(string? id) { var encodedUrl = "taskname=CGRK&mesid=" + id + "&optype=delete&datajson={}"; return encodedUrl; } }