From 3534ee4112b1ad9a0b5d95aadfc29aaf3add2c40 Mon Sep 17 00:00:00 2001
From: 南骏 池 <chiffly@163.com>
Date: 星期五, 05 九月 2025 23:28:30 +0800
Subject: [PATCH] 1.巡检查询优化存储过程 2.生产补料bug优化存储过程
---
service/Warehouse/MesItemBlManager.cs | 921 +++++++-----------------------------------------------
service/QC/IpqcService.cs | 77 ----
2 files changed, 121 insertions(+), 877 deletions(-)
diff --git a/service/QC/IpqcService.cs b/service/QC/IpqcService.cs
index d477004..b210b18 100644
--- a/service/QC/IpqcService.cs
+++ b/service/QC/IpqcService.cs
@@ -104,83 +104,10 @@
if (!isValid)
throw new ApplicationException("GUID杞崲閿欒");
}
- //var pageList = Db.Queryable<VIpqcXj>()
- // .WhereIF(!string.IsNullOrWhiteSpace(queryObj.keyword), a =>
- // a.ItemNo.Contains(queryObj.keyword) ||
- // a.ItemName.Contains(queryObj.keyword) ||
- // a.Daa001.Contains(queryObj.keyword) ||
- // a.ReleaseNo.Contains(queryObj.keyword) ||
- // a.DepartmentName.Contains(queryObj.keyword) ||
- // a.xtName.Contains(queryObj.keyword)
- // )
- // .WhereIF(UtilityHelper.CheckGuid(parsedGuid),
- // a => a.Guid == parsedGuid.ToString())
- // .Where(a => (a.FSubmit ?? 0) == 0)
- // .OrderByDescending(a => a.CreateDate)
- // .OrderBy(a => a.checkTimeSlot)
- // .ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);
- var sql = string.Format(@"SELECT [guid],
- [fsubmit],
- [MODIFY1_FLAG],
- [LOT_NO],
- [FNG_HANDLE],
- [FVERSION],
- [LOT_NO1],
- [FCOVERT_QTY],
- [FCHECK_RESU],
- [RELEASE_NO],
- [DAA001],
- [FBATCH_QTY],
- [ITEM_NO],
- [FCHECK_BY],
- [FNG_DESC],
- [CREATE_BY],
- [DAA013],
- [sj_xt],
- [departmentname],
- [name],
- [ITEM_NAME],
- [remarks],
- [DATE_01],
- [ITEM_MODEL],
- [CREATE_DATE],
- [line_id],
- [check_time_slot],
- ROW_NUMBER() OVER (ORDER BY [CREATE_DATE] DESC, [check_time_slot] ASC) AS RowIndex
-FROM [V_IPQC_XJ]
-WHERE
- ISNULL([fsubmit], 0) = 0
- AND (
- -- 澶勭悊22瀛楃鐨勬爣鍑嗘椂闂存 (e.g. '2025-07-29 08:00-10:00')
- (
- LEN([check_time_slot]) = 22
- AND TRY_CONVERT(date, SUBSTRING([check_time_slot], 1, 10)) = CAST(GETDATE() AS date)
- AND CAST(GETDATE() AS time) BETWEEN
- TRY_CAST(SUBSTRING([check_time_slot], 12, 5) + ':00' AS time)
- AND TRY_CAST(SUBSTRING([check_time_slot], 18, 5) + ':00' AS time)
- )
- OR
- -- 澶勭悊13瀛楃鐨勫姞鐝牸寮� (e.g. '2025-07-29 鍔犵彮')
- (
- LEN([check_time_slot]) = 13
- AND [check_time_slot] LIKE '%鍔犵彮'
- AND TRY_CONVERT(date, SUBSTRING([check_time_slot], 1, 10)) = CAST(GETDATE() AS date)
- AND CAST(GETDATE() AS time) >= '17:00:00'
- )
- )
- {0}",
- parsedGuid != Guid.Empty ? "AND guid = @parsedGuid" : ""); // 鏂板GUID杩囨护鏉′欢
+ var sql = string.Format(@"EXEC prc_pda_ipqc_GetXjList ");
-
-
-
-
- var sqlParams = new List<SugarParameter> {
- new("@parsedGuid", parsedGuid) // 鏂板鍙傛暟
- };
-
- var pageList = Db.Ado.SqlQuery<VIpqcXj>(sql,sqlParams).ToList();
+ var pageList = Db.Ado.SqlQuery<VIpqcXj>(sql).ToList();
return (pageList, totalCount);
}
diff --git a/service/Warehouse/MesItemBlManager.cs b/service/Warehouse/MesItemBlManager.cs
index 8c7ffaf..5d749cb 100644
--- a/service/Warehouse/MesItemBlManager.cs
+++ b/service/Warehouse/MesItemBlManager.cs
@@ -5,7 +5,9 @@
using NewPdaSqlServer.entity.Base;
using NewPdaSqlServer.util;
using SqlSugar;
+using System.Data;
using System.Text;
+using System.Data.SqlClient;
namespace NewPdaSqlServer.service.Warehouse;
@@ -119,9 +121,6 @@
ScblScanBarcode(
WarehouseQuery query)
{
- //if (string.IsNullOrEmpty(query.billNo))
- // throw new Exception("璇烽�夊彇鍗曟嵁鍙凤紒");
-
if (string.IsNullOrEmpty(query.barcode))
throw new Exception("璇锋壂鎻忔潯鐮侊紒");
@@ -131,355 +130,107 @@
if (string.IsNullOrEmpty(query.blNo))
throw new Exception("鐢宠鍗曞彿涓嶈兘涓虹┖锛�");
+ // 鍑嗗璋冪敤瀛樺偍杩囩▼鐨勫弬鏁�
+ string outMsg = "";
+ int outSum = -1;
+ decimal barcodeNum = 0;
+ decimal splitNum = 0;
- // 妫�楠屾槸鍚﹂噸澶嶆壂鎻�
- var exists = Db.Queryable<MesInvItemOutCDetails>()
- .Where(b => b.ItemBarcode == query.barcode)
- .Any();
-
- if (exists)
- throw new Exception("姝ゆ潯鐮佸凡鎵弿,鍕块噸澶嶆壂鐮侊紒");
-
- // 鏌ヨ鏉＄爜搴撳瓨淇℃伅
- var stockBarcode = Db.Queryable<MesInvItemStocks>()
- .Where(t => t.ItemBarcode == query.barcode && t.Quantity > 0)
- .First();
-
- if (stockBarcode == null)
- throw new Exception($"搴撳瓨涓棤姝ゆ潯鐮�,璇锋牳瀵癸紒{query.barcode}");
-
- var depots = Db.Queryable<MesDepots>()
- .Where(t => t.DepotId == stockBarcode.DepotId)
- .First();
-
- // 妫�鏌ヨˉ鏂欏崟鐘舵��
- var mesItemBl = Db.Queryable<MesItemBl>()
- .Where(a => a.BlNo == query.blNo)
- .First();
-
- if (mesItemBl == null)
- throw new Exception($"鐢宠鍗� {query.blNo} 涓嶅瓨鍦紒");
-
- if (mesItemBl.Bl018 != true)
- throw new Exception($"鐢宠鍗� {query.blNo} 鏈鏍革紒");
-
- if (mesItemBl.Bl019 == true)
- throw new Exception($"鐢宠鍗� {query.blNo} 宸插畬缁擄紒");
-
- // 鑾峰彇琛ユ枡鍗曟槑缁嗗苟鏍￠獙
- var blDetail = Db.Queryable<MesItemBlDetail>()
- .Where(b =>
- b.Mid == mesItemBl.Id && b.Bld012 == stockBarcode.ItemId)
- .First();
-
- if (blDetail == null)
- throw new Exception($"鐢宠鍗曚笉瀛樺湪姝ょ墿鏂� {stockBarcode.ItemNo} 璇风‘璁わ紒");
-
- // 妫�鏌ュ緟琛ユ暟閲�
- var quantity = (blDetail.Bld007 ?? 0) - (blDetail.Bld008 ?? 0);
- if (quantity == 0)
- throw new Exception("鐗╂枡宸叉壂鐮佸畬鎴愶紝璇锋牳瀵癸紒");
-
-
- if(stockBarcode.Quantity > quantity)
+ try
{
- query.status = 2;
- query.message = "鏉＄爜搴撳瓨澶т簬闇�棰嗭紝璇锋媶鍒嗭紒";
- query.CfNum = quantity;
- query.Num = stockBarcode.Quantity;
- return (query, []);
- }
-
- // 妫�鏌ュ伐鍗曚俊鎭�
- var womdaa = Db.Queryable<Womdaa>()
- .Where(a => a.Daa001 == query.billNo)
- .First();
-
- if (womdaa == null)
- throw new Exception($"宸ュ崟 {query.billNo} 涓嶅瓨鍦紝璇风‘璁わ紒");
-
- // 妫�鏌ュ鏂欐槑缁�
- var womdab = Db.Queryable<Womdab>()
- .Where(b => b.Dab001 == query.billNo && b.Erpid == blDetail.Bld014)
- .First();
-
- if (womdab == null)
- throw new Exception($"澶囨枡鏄庣粏涓嶅瓨鍦ㄦ鐗╂枡 {stockBarcode.ItemNo} 璇风‘璁わ紒");
-
- if (stockBarcode.Quantity > quantity)
- {
- // 鑾峰彇寰呭彂鏂欐槑缁嗗垪琛�
- var pendingList = Db.Queryable<MesItemBl, MesItemBlDetail>((a, b) =>
- new JoinQueryInfos(JoinType.Left, a.Id == b.Mid))
- .Where((a, b) => a.BlNo == query.blNo
- && (b.Bld007 ?? 0) - (b.Bld008 ?? 0) > 0)
- .Select((a, b) => new MesItemBlDetail
- {
- Bld012 = b.Bld012,
- Bld002 = b.Bld002,
- Bld003 = b.Bld003,
- Bld004 = b.Bld004,
- Bld007 = b.Bld007,
- Bld008 = b.Bld008
- })
- .ToList();
-
-
- query.Num = stockBarcode.Quantity;
- query.Fum = quantity;
-
- return (query, pendingList);
- }
-
- // 寮�鍚簨鍔″鐞�
- var success = UseTransaction(db =>
- {
- var outNoType = "";
- if(query.Type == "鐢熶骇琛ユ枡")
+ // 璋冪敤瀛樺偍杩囩▼
+ var parameters = new SqlParameter[]
{
- outNoType = "SCBL(鐢熶骇琛ユ枡)";
- }else
+ new SqlParameter("@C_USER", SqlDbType.NVarChar, 100) { Value = query.userName ?? string.Empty },
+ new SqlParameter("@P_BILL_NO", SqlDbType.NVarChar, 50) { Value = query.billNo ?? string.Empty },
+ new SqlParameter("@P_ITEM_BARCODE", SqlDbType.NVarChar, 100) { Value = query.barcode ?? string.Empty },
+ new SqlParameter("@P_BL_NO", SqlDbType.NVarChar, 100) { Value = query.blNo ?? string.Empty },
+ new SqlParameter("@P_TYPE", SqlDbType.NVarChar, 20) { Value = query.Type ?? string.Empty },
+ new SqlParameter("@OUT_MSG", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
+ new SqlParameter("@OUT_SUM", SqlDbType.Int) { Direction = ParameterDirection.Output },
+ new SqlParameter("@BARCODE_NUM", SqlDbType.Decimal) {
+ Direction = ParameterDirection.Output,
+ Precision = 18,
+ Scale = 10
+ },
+ new SqlParameter("@SPLIT_NUM", SqlDbType.Decimal) {
+ Direction = ParameterDirection.Output,
+ Precision = 18,
+ Scale = 10
+ }
+ };
+
+ // 鎵ц瀛樺偍杩囩▼
+ string procedureName = "PRC_PDA_SCBLCL";
+ int res = DbHelperSQL.RunProcedure_NonQuery(procedureName, parameters);
+
+ // 鑾峰彇杈撳嚭鍙傛暟
+ outMsg = parameters[5].Value?.ToString() ?? "";
+ outSum = parameters[6].Value as int? ?? 0; // 榛樿鍊兼牴鎹渶姹傝皟鏁�
+ barcodeNum = parameters[7].Value as decimal? ?? 0m;
+ splitNum = parameters[8].Value as decimal? ?? 0m;
+
+ // 澶勭悊瀛樺偍杩囩▼杩斿洖鐨勭粨鏋�
+ if (outSum == 2) // 闇�瑕佹媶鍒�
{
- outNoType = "SCCL(鐢熶骇瓒呴)";
- }
- // 鑾峰彇鎴栧垱寤哄嚭搴撳崟
- var outId = Guid.NewGuid();
- var outNo = BillNo.GetBillNo(outNoType);
+ query.status = 2;
+ query.message = outMsg;
+ query.CfNum = splitNum;
+ query.Num = barcodeNum;
- var existingOut = db.Queryable<MesInvItemOuts>()
- .Where(a => a.TaskNo == query.blNo
- && a.DepotId == stockBarcode.DepotId
- && a.OutDate.Value.Date.ToString("yyyy-MM-dd") ==
- DateTime.Now.Date.ToString("yyyy-MM-dd")
- && a.BillTypeId == 200
- && a.TransactionNo == 209
- && a.Status == 0)
- .First();
-
- if (existingOut != null)
- {
- outId = existingOut.Guid;
- outNo = existingOut.ItemOutNo;
- }
- else
- {
- // 鎻掑叆鍑哄簱鍗曚富琛�
- db.Insertable(new MesInvItemOuts
- {
- Guid = outId,
- ItemOutNo = outNo,
- TaskNo = query.blNo,
- CreateBy = query.userName,
- CreateDate = DateTime.Now,
- LastupdateBy = query.userName,
- LastupdateDate = DateTime.Now,
- BillTypeId = 200,
- TransactionNo = 209,
- Remark = mesItemBl.Bl007,
- DepotCode = depots.DepotCode,
- OutPart = womdaa.Daa013,
- OutType = query.Type,
- FType = 0,
- Factory = stockBarcode.Factory,
- Company = stockBarcode.Company,
- WorkNo = womdaa.Daa021,
- BoardItem = womdaa.Daa002,
- PbillNo = womdaa.Daa001,
- OutDate = DateTime.Now,
- Status = 0,
- DepotId = stockBarcode.DepotId,
- THORGID = stockBarcode.StockOrgId,
- //BbillNo = query.billNo
- }).IgnoreColumns(true).ExecuteCommand();
- }
-
- // 妫�鏌ュ苟鏇存柊鍑哄簱鍗曠墿鏂欐槑缁�
- var itemCount = db.Queryable<MesInvItemOutItems>()
- .Where(i =>
- i.ItemOutId == outId && i.ItemDabid == blDetail.Id && i.ItemId == stockBarcode.ItemId && i.DepotId == stockBarcode.DepotId.ToString())
- .Count();
-
- if (itemCount > 0)
- // 鏇存柊宸插瓨鍦ㄧ殑鐗╂枡鏄庣粏鏁伴噺
- db.Updateable<MesInvItemOutItems>()
- .SetColumns(i =>
- i.TlQty == i.TlQty + stockBarcode.Quantity)
- .Where(i =>
- i.ItemOutId == outId && i.ItemDabid == blDetail.Id && i.ItemId == stockBarcode.ItemId && i.DepotId == stockBarcode.DepotId.ToString())
- .ExecuteCommand();
- else
- // 鎻掑叆鏂扮殑鐗╂枡鏄庣粏璁板綍
- db.Insertable(new MesInvItemOutItems
- {
- Guid = Guid.NewGuid(),
- ItemOutId = outId,
- ItemNo = blDetail.Bld002,
- Quantity = blDetail.Bld007,
- TlQty = stockBarcode.Quantity,
- CreateBy = query.userName,
- CreateDate = DateTime.Now,
- LastupdateBy = query.userName,
- LastupdateDate = DateTime.Now,
- Factory = stockBarcode.Factory,
- Company = stockBarcode.Company,
- DepotCode = depots.DepotCode,
- TaskNo = query.blNo,
- WorkNo = womdaa.Daa021,
- WorkLine = blDetail.Bld013,
- ErpItemNo = womdab.Dab003,
- ErpId = womdab.Eid,
- ErpAutoid = womdab.Erpid,
- PbillNo = query.billNo,
- ItemId = blDetail.Bld012,
- DepotId = stockBarcode.DepotId.ToString(),
- ItemDabid = blDetail.Id,
- // Unit = blDetail.Bld009,
- // DepotId = (int)stockBarcode.DepotsId
- }).IgnoreColumns(true).ExecuteCommand();
-
- // 鎻掑叆鍑哄簱鍗曟潯鐮佹槑缁�
- db.Insertable(new MesInvItemOutCDetails
- {
- Guid = Guid.NewGuid(),
- ItemOutId = outId,
- ItemBarcode = stockBarcode.ItemBarcode,
- ItemNo = stockBarcode.ItemNo,
- LotNo = stockBarcode.LotNo,
- Quantity = stockBarcode.Quantity,
- ForceOutFlag = 0,
- CreateBy = query.userName,
- CreateDate = DateTime.Now,
- LastupdateBy = query.userName,
- LastupdateDate = DateTime.Now,
- DepotCode = depots.DepotCode,
- DepotSectionCode = stockBarcode.DepotSectionsCode,
- Remark = blDetail.Bld010,
- Factory = stockBarcode.Factory,
- Company = stockBarcode.Company,
- TaskNoy = mesItemBl.Bl013,
- BoardStyle = mesItemBl.Bl002,
- TaskNo = query.blNo,
- WorkNo = blDetail.Bld001,
- WorkLine = blDetail.Bld013,
- SuppNo = stockBarcode.SuppNo,
- PbillNo = query.billNo,
- ItemId = blDetail.Bld012,
- Unit = blDetail.Bld009,
- DepotId = (int)stockBarcode.DepotId,
- EbelnK3id = womdab.Eid,
- LineK3id = womdab.Erpid
- }).IgnoreColumns(true).ExecuteCommand();
-
- // 鎻掑叆涓氬姟浜ゆ槗璁板綍
- db.Insertable(new MesInvBusiness2
- {
- Guid = Guid.NewGuid(),
- Status = 1,
- BillTypeId = 200, // p_bill_type_id
- TransactionCode = "209", // p_transaction_no
- BusinessType = -1,
- ItemBarcode = stockBarcode.ItemBarcode,
- ItemNo = stockBarcode.ItemNo,
- LotNo = stockBarcode.LotNo,
- EpFlag = true,
- Quantity = stockBarcode.Quantity,
- FromInvDepotsCode = stockBarcode.DepotsCode,
- FromInvDepotSectionsCode = stockBarcode.DepotSectionsCode,
- CreateBy = query.userName,
- CreateDate = DateTime.Now,
- LastupdateBy = query.userName,
- LastupdateDate = DateTime.Now,
- Factory = stockBarcode.Factory,
- Company = stockBarcode.Company,
- TaskNo = mesItemBl.Bl012, // Matches C_QTCK.Bl012
- BillNo = query.blNo,
- WorkNo = blDetail.Bld001, // Matches C_QTCK_D.Bld001
- WorkLine = blDetail.Bld013, // Matches C_QTCK_D.Bld013
- SuppNo = stockBarcode.SuppNo,
- ItemId = stockBarcode.ItemId
- // CkDepot = stockBarcode.DepotsId
- }).IgnoreColumns(true).ExecuteCommand();
-
-
- // 鏇存柊宸ュ崟琛ㄦ暟閲�
- db.Updateable<Womdab>()
- .SetColumns(it => new Womdab
- {
- Dab007 = (it.Dab007 ?? 0) + stockBarcode.Quantity,
- Dab020 = (it.Dab020 ?? 0) + stockBarcode.Quantity,
- Dab021 = (it.Dab021 ?? 0) + stockBarcode.Quantity
- })
- .Where(it => it.Guid == womdab.DaaGuid && it.Dab003 == womdab.Dab003)
- .ExecuteCommand();
-
- // 鏇存柊琛ユ枡鍗曟槑缁嗗凡琛ユ暟閲�
- db.Updateable<MesItemBlDetail>()
- .SetColumns(it => new MesItemBlDetail
- {
- Bld008 = (it.Bld008 ?? 0) + (int)stockBarcode.Quantity
- })
- .Where(it => it.Id == blDetail.Id)
- .ExecuteCommand();
-
- // 妫�鏌ヨˉ鏂欏崟鏄庣粏鏄惁瀹屾垚
- var blDetail1 = db.Queryable<MesItemBlDetail>()
- .Where(it => it.Id == blDetail.Id)
- .First();
-
- if ((blDetail1.Bld007 ?? 0) <= (blDetail1.Bld008 ?? 0))
- // 鏇存柊鏄庣粏瀹屾垚鐘舵��
- db.Updateable<MesItemBlDetail>()
- .SetColumns(it => it.Bld011 == 1)
- .Where(it => it.Id == blDetail1.Id)
- .ExecuteCommand();
-
- // 鏇存柊搴撳瓨鏁伴噺涓�0
- db.Updateable<MesInvItemStocks>()
- .SetColumns(it => it.Quantity == 0)
- .Where(it => it.Guid == stockBarcode.Guid)
- .ExecuteCommand();
-
-
- // 妫�鏌ユ槸鍚︽墍鏈夋槑缁嗛兘宸插畬鎴�
- var unfinishedDetail = db.Queryable<MesItemBlDetail>()
- .LeftJoin<MesItemBl>((b, a) => a.Id == b.Mid)
- .Where((b, a) => a.BlNo == query.blNo && (b.Bld011 ?? 0) == 0)
- .Select((b, a) => b)
- .First();
-
- if (unfinishedDetail == null)
- // 濡傛灉娌℃湁鏈畬鎴愮殑鏄庣粏锛屾洿鏂拌ˉ鏂欏崟鐘舵�佷负宸插畬鎴�
- db.Updateable<MesItemBl>()
- .SetColumns(it => new MesItemBl
+ // 鑾峰彇寰呭彂鏂欐槑缁嗗垪琛�
+ var pendingList = Db.Queryable<MesItemBl, MesItemBlDetail>((a, b) =>
+ new JoinQueryInfos(JoinType.Left, a.Id == b.Mid))
+ .Where((a, b) => a.BlNo == query.blNo
+ && (b.Bld007 ?? 0) - (b.Bld008 ?? 0) > 0)
+ .Select((a, b) => new MesItemBlDetail
{
- Bl019 = true,
- WcUser = query.userName,
- WcTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
+ Bld012 = b.Bld012,
+ Bld002 = b.Bld002,
+ Bld003 = b.Bld003,
+ Bld004 = b.Bld004,
+ Bld007 = b.Bld007,
+ Bld008 = b.Bld008
})
- .Where(it => it.Id == mesItemBl.Id)
- .ExecuteCommand();
+ .ToList();
- return 1;
- });
-
- // 鑾峰彇鏈�缁堢殑寰呭彂鏂欐槑缁嗗垪琛�
- var finalPendingList = Db.Queryable<MesItemBl, MesItemBlDetail>(
- (a, b) =>
- new JoinQueryInfos(JoinType.Left, a.Id == b.Mid))
- .Where((a, b) => a.BlNo == query.blNo
- && (b.Bld007 ?? 0) - (b.Bld008 ?? 0) > 0)
- .Select((a, b) => new MesItemBlDetail
+ return (query, pendingList);
+ }
+ else if (outSum == -1) // 澶勭悊澶辫触
{
- Bld012 = b.Bld012,
- Bld002 = b.Bld002,
- Bld003 = b.Bld003,
- Bld004 = b.Bld004,
- Bld007 = b.Bld007,
- Bld008 = b.Bld008
- })
- .ToList();
+ throw new Exception(outMsg);
+ }
+ else if (outSum == 1) // 澶勭悊鎴愬姛
+ {
+ // 鑾峰彇鏈�缁堢殑寰呭彂鏂欐槑缁嗗垪琛�
+ var finalPendingList = Db.Queryable<MesItemBl, MesItemBlDetail>(
+ (a, b) => new JoinQueryInfos(JoinType.Left, a.Id == b.Mid))
+ .Where((a, b) => a.BlNo == query.blNo
+ && (b.Bld007 ?? 0) - (b.Bld008 ?? 0) > 0)
+ .Select((a, b) => new MesItemBlDetail
+ {
+ Bld012 = b.Bld012,
+ Bld002 = b.Bld002,
+ Bld003 = b.Bld003,
+ Bld004 = b.Bld004,
+ Bld007 = b.Bld007,
+ Bld008 = b.Bld008
+ })
+ .ToList();
- return (query, finalPendingList);
+ query.status = 1;
+ query.message = outMsg;
+ return (query, finalPendingList);
+ }
+ else
+ {
+ throw new Exception("鏈煡鐨勫鐞嗙粨鏋滅姸鎬�");
+ }
+ }
+ catch (Exception ex)
+ {
+ throw new Exception($"璋冪敤瀛樺偍杩囩▼PRC_PDA_SCBLCL澶辫触: {ex.Message}");
+ }
}
/// <summary>
@@ -498,6 +249,8 @@
public ProductionPickDto SplitBarcode(
WarehouseQuery query)
{
+
+ // Validate input parameters
if (string.IsNullOrEmpty(query.userName))
throw new Exception("鐢ㄦ埛鍚嶄笉鑳戒负绌猴紒");
@@ -513,471 +266,35 @@
if (string.IsNullOrEmpty(query.blNo))
throw new Exception("琛ユ枡鍗曞彿涓嶈兘涓虹┖锛�");
+ // Prepare parameters for the stored procedure
+ var parameters = new List<SugarParameter>
+{
+ new SugarParameter("@C_USER", query.userName),
+ new SugarParameter("@P_BILL_NO", query.billNo),
+ new SugarParameter("@P_ITEM_BARCODE", query.barcode),
+ new SugarParameter("@P_BL_NO", query.blNo),
+ new SugarParameter("@P_TYPE", query.Type),
+ new SugarParameter("@NUM", query.Num),
+ new SugarParameter("@OUT_MSG", "", System.Data.DbType.String, ParameterDirection.Output),
+ new SugarParameter("@OUT_SUM", 0, System.Data.DbType.Int32, ParameterDirection.Output),
+ new SugarParameter("@OUT_CF_BAR", "", System.Data.DbType.String, ParameterDirection.Output)
+};
- // 妫�楠屾槸鍚﹂噸澶嶆壂鎻�
- var exists = Db.Queryable<MesInvItemOutCDetails>()
- .Where(b => b.ItemBarcode == query.barcode)
- .Any();
+ // Execute the stored procedure
+ Db.Ado.UseStoredProcedure().ExecuteCommand("PRC_PDA_SCBLCL_CF", parameters);
- if (exists)
- throw new Exception("姝ゆ潯鐮佸凡鎵弿,鍕块噸澶嶆壂鐮侊紒");
+ // Get output parameters
+ var outMsg = parameters.FirstOrDefault(p => p.ParameterName == "@OUT_MSG")?.Value?.ToString();
+ var outSum = Convert.ToInt32(parameters.FirstOrDefault(p => p.ParameterName == "@OUT_SUM")?.Value ?? -1);
+ var outCfBar = parameters.FirstOrDefault(p => p.ParameterName == "@OUT_CF_BAR")?.Value?.ToString();
- // 鏌ヨ鏉＄爜搴撳瓨淇℃伅
- var stockBarcode = Db.Queryable<MesInvItemStocks>()
- .Where(t => t.ItemBarcode == query.barcode && t.Quantity > 0)
- .First();
-
- if (stockBarcode == null)
- throw new Exception($"搴撳瓨涓棤姝ゆ潯鐮�,璇锋牳瀵癸紒{query.barcode}");
-
- var totalQty = stockBarcode.Quantity;
- string newBarcode = null;
-
- var outNoType = "";
- if (query.Type == "鐢熶骇琛ユ枡")
+ // Handle the result
+ if (outSum == -1)
{
- outNoType = "SCBL(鐢熶骇琛ユ枡)";
- }
- else
- {
- outNoType = "SCCL(鐢熶骇瓒呴)";
+ throw new Exception(outMsg ?? "鎿嶄綔澶辫触");
}
- // 寮�鍚簨鍔″鐞�
- var success = UseTransaction(db =>
- {
- var executeCommand = 0;
-
- // 鎷嗗垎鏉＄爜
- if (totalQty > query.Num)
- {
- newBarcode = BillNo.ExecuteBarcodeSplit(query.userName, query.barcode, query.Num);
- //var mesItems = db.Queryable<MesItems>()
- // .Where(s => s.Id == stockBarcode.ItemId).First();
-
- //// 鐢熸垚鏂版潯鐮佸彿
- //newBarcode = BillNo.GetBillNo("TM(鏉＄爜)", mesItems.ItemNo);
-
- //// 鍐欏叆鏂版潯鐮�
- //executeCommand += db.Insertable(new MesInvItemBarcodes
- //{
- // Guid = Guid.NewGuid(),
- // ItemBarcode = newBarcode,
- // CustNo = stockBarcode.CustomerNo,
- // // ProductCode = stockBarcode.ProductCode,
- // // ItemBarcode2 = stockBarcode.ItemBarcode2,
- // // ItemCode = stockBarcode.ItemCode,
- // ItemNo = stockBarcode.ItemNo,
- // LotNo = stockBarcode.LotNo,
- // Quantity = query.Num,
- // EpFlag = true,
- // TaskNo = stockBarcode.TaskNo,
- // CreateBy = query.userName,
- // CreateDate = DateTime.Now,
- // LastupdateBy = query.userName,
- // LastupdateDate = DateTime.Now,
- // OldItemBarcode = query.barcode,
- // // Mblnr = stockBarcode.Mblnr,
- // // Zeile = stockBarcode.Zeile,
- // // RohInId = stockBarcode.RohInId,
- // Barcodestatus = false,
- // Oldqty = query.Num as long?,
- // // Unit = stockBarcode.Unit,
- // // WeightUnit = stockBarcode.WeightUnit,
- // Factory = stockBarcode.Factory,
- // Company = stockBarcode.Company,
- // BillNo = stockBarcode.BillNo,
- // BoardStyle = stockBarcode.BoardStyle,
- // // ColorName = stockBarcode.ColorName,
- // WorkNo = stockBarcode.WorkNo,
- // WorkLine = stockBarcode.WorkLine,
- // // MemoBad = stockBarcode.MemoBad,
- // ComeFlg = 5,
- // // Memo = stockBarcode.Memo,
- // SuppId = stockBarcode.SuppId,
- // SuppNo = stockBarcode.SuppNo,
- // InsDate = stockBarcode.IndepDate, // Added InsDate
- // ItemId = stockBarcode.ItemId
- // // ItemUnit = stockBarcode.ItemUnit // Added ItemUnit
- //}).IgnoreColumns(true).ExecuteCommand();
-
- //// 鏇存柊鍘熸潯鐮佹暟閲�
- //executeCommand += db.Updateable<MesInvItemBarcodes>()
- // .SetColumns(it => it.Quantity == it.Quantity - query.Num)
- // .Where(it => it.ItemBarcode == query.barcode)
- // .ExecuteCommand();
-
- //// 鏇存柊鍘熸潯鐮佹暟閲�
- //executeCommand += db.Updateable<MesInvItemStocks>()
- // .SetColumns(it => it.Quantity == it.Quantity - query.Num)
- // .Where(it => it.ItemBarcode == query.barcode)
- // .ExecuteCommand();
-
- ////// 鍒犻櫎鍘熸潯鐮佸簱瀛樿褰�
- ////executeCommand += db.Deleteable<MesInvItemStocks>()
- //// .Where(it => it.ItemBarcode == query.barcode)
- //// .ExecuteCommand();
-
- //// 鎻掑叆鍓╀綑鏉＄爜鏁伴噺鐨勬柊搴撳瓨璁板綍
- //executeCommand += db.Insertable(new MesInvItemStocks
- //{
- // Guid = Guid.NewGuid(),
- // TaskNo = stockBarcode.TaskNo,
- // ItemBarcode = newBarcode,
- // ItemNo = stockBarcode.ItemNo,
- // LotNo = stockBarcode.LotNo,
- // Quantity = query.Num,
- // EpFlag = stockBarcode.EpFlag,
- // CustomerNo = stockBarcode.CustomerNo,
- // ItemWt = stockBarcode.ItemWt,
- // DepotsCode = stockBarcode.DepotsCode,
- // DepotsId = stockBarcode.DepotsId,
- // DepotSectionsCode = stockBarcode.DepotSectionsCode,
- // CheckDate = stockBarcode.CheckDate,
- // ItemType = stockBarcode.ItemType,
- // IndepDate = stockBarcode.IndepDate,
- // Factory = stockBarcode.Factory,
- // Company = stockBarcode.Company,
- // IqcStatus = stockBarcode.IqcStatus,
- // BoardStyle = stockBarcode.BoardStyle,
- // WorkNo = stockBarcode.WorkNo,
- // WorkLine = stockBarcode.WorkLine,
- // SuppNo = stockBarcode.SuppNo,
- // ItemId = stockBarcode.ItemId
- // // UnitId = stockBarcode.ItemUnit
- //}).IgnoreColumns(true).ExecuteCommand();
-
- //// 鍐欏叆鏂版潯鐮佺殑浜ゆ槗璁板綍
- //executeCommand += db.Insertable(new MesInvBusiness2
- //{
- // Guid = Guid.NewGuid(),
- // Status = 1,
- // BillTypeId = 200, // p_bill_type_id
- // TransactionCode = "209", // p_transaction_no
- // BusinessType = 0,
- // ItemBarcode = newBarcode,
- // ItemNo = stockBarcode.ItemNo,
- // LotNo = stockBarcode.LotNo,
- // EpFlag = true,
- // Quantity = query.Num,
- // FromInvDepotsCode = null,
- // FromInvDepotSectionsCode = null,
- // ToInvDepotsCode = stockBarcode.DepotsCode,
- // ToInvDepotSectionsCode = stockBarcode.DepotSectionsCode,
- // Description = query.Type + "鎷嗗垎鐢熸垚",
- // CreateBy = query.userName,
- // CreateDate = DateTime.Now,
- // LastupdateBy = query.userName,
- // LastupdateDate = DateTime.Now,
- // Factory = stockBarcode.Factory,
- // Company = stockBarcode.Company,
- // TaskNo = stockBarcode.TaskNo,
- // BillNo = stockBarcode.BillNo,
- // WorkNo = stockBarcode.WorkNo,
- // WorkLine = stockBarcode.WorkLine,
- // SuppNo = stockBarcode.SuppNo,
- // SuppId = stockBarcode.SuppId,
- // ItemId = stockBarcode.ItemId
- // // CkDepot = stockBarcode.DepotsId
- //}).IgnoreColumns(true).ExecuteCommand();
- }
- else if (totalQty < query.Num)
- {
- throw new Exception("鍙戞枡鏁伴噺澶т簬鏉＄爜鏁�,璇锋牳瀵癸紒");
- }
-
- //if (string.IsNullOrEmpty(newBarcode)) newBarcode = query.barcode;
-
- // 妫�鏌ヨˉ鏂欏崟鐘舵��
- var mesItemBl = Db.Queryable<MesItemBl>()
- .Where(a => a.BlNo == query.blNo && (a.Bl018 ?? false) == true)
- .First();
-
- if (mesItemBl == null)
- throw new Exception($"鐢宠鍗� {query.blNo} 宸叉挙鍥烇紒");
-
- if (mesItemBl.Bl018 != true)
- throw new Exception($"鐢宠鍗� {query.blNo} 鏈鏍革紒");
-
- if (mesItemBl.Bl019 == true)
- throw new Exception($"鐢宠鍗� {query.blNo} 宸插畬缁擄紒");
-
- // 鑾峰彇琛ユ枡鍗曟槑缁嗗苟鏍￠獙
- var blDetail = Db.Queryable<MesItemBlDetail>()
- .Where(b =>
- b.Mid == mesItemBl.Id && b.Bld012 == stockBarcode.ItemId)
- .First();
-
- if (blDetail == null)
- throw new Exception($"鐢宠鍗曚笉瀛樺湪姝ょ墿鏂� {stockBarcode.ItemNo} 璇风‘璁わ紒");
-
- var remainingQty = (blDetail.Bld007 ?? 0) - (blDetail.Bld008 ?? 0);
- if (remainingQty == 0)
- throw new Exception("鐗╂枡宸叉壂鐮佸畬鎴愶紝璇锋牳瀵癸紒");
-
- if (query.Num > remainingQty)
- throw new Exception(
- $"鎷嗗垎鏁伴噺锛歿query.Num} 澶т簬寰呭彂鏂欐暟閲忥細{remainingQty}锛岃鏍稿锛�");
-
- // 妫�鏌ュ伐鍗曚俊鎭�
- var womdaa = Db.Queryable<Womdaa>()
- .Where(a => a.Daa001 == query.billNo)
- .First();
-
- if (womdaa == null)
- throw new Exception($"宸ュ崟 {query.billNo} 涓嶅瓨鍦紝璇风‘璁わ紒");
-
- var womdab = Db.Queryable<Womdab>()
- .Where(b =>
- b.Dab001 == query.billNo && b.Erpid == blDetail.Bld014)
- .First();
-
- if (womdab == null)
- throw new Exception($"澶囨枡鏄庣粏涓嶅瓨鍦ㄦ鐗╂枡 {stockBarcode.ItemNo} 璇风‘璁わ紒");
-
- // 妫�鏌ュ凡鍙戞枡鏁伴噺鏄惁瓒呰繃寰呭彂鏂欐暟閲�
- var sumQty = db.Queryable<MesInvItemOutCDetails>()
- .Where(it =>
- it.TaskNo == query.blNo && it.ItemId == stockBarcode.ItemId)
- .Sum(it => it.Quantity);
-
- //if (sumQty > remainingQty)
- // throw new Exception(
- // $"鎷嗗垎鏁伴噺锛歿sumQty} 澶т簬寰呭彂鏂欐暟閲忥細{remainingQty}锛岃鏍稿锛�");
-
-
- var depots = Db.Queryable<MesDepots>()
- .Where(t => t.DepotId == stockBarcode.DepotId)
- .First();
-
- // 鑾峰彇鎴栧垱寤哄嚭搴撳崟
- var itemOut = db.Queryable<MesInvItemOuts>()
- .Where(a => a.TaskNo == query.blNo
- && a.DepotId == stockBarcode.DepotId
- && a.OutDate.Value.Date.ToString("yyyy-MM-dd") ==
- DateTime.Now.Date.ToString("yyyy-MM-dd")
- && a.BillTypeId == 200
- && a.TransactionNo == 209
- && a.Status == 0)
- .First();
-
- var outId = new Guid();
- var outNo = "";
- if (itemOut == null)
- {
- // 鍒涘缓鏂扮殑鍑哄簱鍗�
- outId = Guid.NewGuid();
- outNo = BillNo.GetBillNo(outNoType);
-
- // 鎻掑叆鍑哄簱鍗曚富琛�
- executeCommand += db.Insertable(new MesInvItemOuts
- {
- Guid = outId,
- ItemOutNo = outNo,
- TaskNo = query.blNo,
- CreateBy = query.userName,
- CreateDate = DateTime.Now,
- LastupdateBy = query.userName,
- LastupdateDate = DateTime.Now,
- BillTypeId = 200,
- TransactionNo = 209,
- Remark = mesItemBl.Bl007,
- DepotCode = depots.DepotCode,
- OutPart = womdaa.Daa013,
- FType = 0,
- Factory = stockBarcode.Factory,
- Company = stockBarcode.Company,
- WorkNo = womdaa.Daa021,
- BoardItem = womdaa.Daa002,
- PbillNo = womdaa.Daa001,
- OutDate = DateTime.Now,
- Status = 0,
- DepotId = stockBarcode.DepotId,
- THORGID = stockBarcode.StockOrgId,
- OutType = query.Type,
- //BbillNo = query.blNo
- }).IgnoreColumns(true).ExecuteCommand();
- }
- else
- {
- outId = itemOut.Guid;
- outNo = itemOut.ItemOutNo;
- }
-
- // 妫�鏌ユ槸鍚﹀凡瀛樺湪鍑哄簱鍗曟槑缁�
- var itemOutItemCount = db.Queryable<MesInvItemOutItems>()
- .Where(it =>
- it.ItemOutId == outId &&
- it.ItemId == stockBarcode.ItemId &&
- it.DepotId == stockBarcode.DepotId.ToString())
- .Count();
-
- if (itemOutItemCount == 0)
- // 鎻掑叆鏂扮殑鍑哄簱鍗曟槑缁�
- executeCommand += db.Insertable(new MesInvItemOutItems
- {
- Guid = Guid.NewGuid(),
- ItemOutId = outId,
- ItemNo = blDetail.Bld002,
- Quantity = query.Num,
- TlQty = query.Num,
- CreateBy = query.userName,
- CreateDate = DateTime.Now,
- LastupdateBy = query.userName,
- LastupdateDate = DateTime.Now,
- Factory = stockBarcode.Factory,
- Company = stockBarcode.Company,
- DepotCode = depots.DepotCode,
- TaskNo = query.blNo,
- WorkNo = womdaa.Daa021,
- WorkLine = blDetail.Bld013,
- ErpItemNo = womdab.Dab003,
- ErpId = womdab.Eid,
- ErpAutoid = womdab.Erpid,
- PbillNo = query.billNo,
- ItemId = blDetail.Bld012,
- DepotId = stockBarcode.DepotId.ToString(),
- ItemDabid = blDetail.Id,
- // Unit = blDetail.Bld009,
- // DepotId = (int)stockBarcode.DepotsId
- }).IgnoreColumns(true).ExecuteCommand();
- else
- // 鏇存柊宸叉湁鍑哄簱鍗曟槑缁嗘暟閲�
- executeCommand += db.Updateable<MesInvItemOutItems>()
- .SetColumns(it => it.TlQty == (it.TlQty ?? 0) + query.Num)
- .Where(it =>
- it.ItemOutId == outId &&
- it.ItemId == stockBarcode.ItemId &&
- it.DepotId == stockBarcode.DepotId.ToString())
- .ExecuteCommand();
-
- // 鎻掑叆鍑哄簱鏉＄爜鏄庣粏
- executeCommand += db.Insertable(new MesInvItemOutCDetails
- {
- Guid = Guid.NewGuid(),
- ItemOutId = outId,
- ItemBarcode = newBarcode ,
- ItemNo = stockBarcode.ItemNo,
- LotNo = stockBarcode.LotNo,
- Quantity = query.Num,
- ForceOutFlag = 0,
- CreateBy = query.userName,
- CreateDate = DateTime.Now,
- LastupdateBy = query.userName,
- LastupdateDate = DateTime.Now,
- DepotCode = depots.DepotCode,
- DepotSectionCode = stockBarcode.DepotSectionsCode,
- Remark = blDetail.Bld010,
- Factory = stockBarcode.Factory,
- Company = stockBarcode.Company,
- TaskNoy = mesItemBl.Bl013,
- BoardStyle = mesItemBl.Bl002,
- TaskNo = query.blNo,
- WorkNo = blDetail.Bld001,
- WorkLine = blDetail.Bld013,
- SuppNo = stockBarcode.SuppNo,
- PbillNo = query.billNo,
- ItemId = blDetail.Bld012,
- Unit = blDetail.Bld009,
- DepotId = (int)stockBarcode.DepotId,
- Dabid = womdab.Guid,
- }).IgnoreColumns(true).ExecuteCommand();
-
- // 鎻掑叆涓氬姟娴佹按
- executeCommand += db.Insertable(new MesInvBusiness2
- {
- Guid = Guid.NewGuid(),
- Status = 1,
- BillTypeId = 200, // p_bill_type_id
- TransactionCode = "209", // p_transaction_no
- BusinessType = 1,
- ItemBarcode = newBarcode ,
- ItemNo = stockBarcode.ItemNo,
- LotNo = stockBarcode.LotNo,
- EpFlag = true,
- Quantity = query.Num,
- FromInvDepotsCode = stockBarcode.DepotsCode,
- FromInvDepotSectionsCode = stockBarcode.DepotSectionsCode,
- Description = query.Type,
- CreateBy = query.userName,
- CreateDate = DateTime.Now,
- LastupdateBy = query.userName,
- LastupdateDate = DateTime.Now,
- Factory = stockBarcode.Factory,
- Company = stockBarcode.Company,
- TaskNo = mesItemBl.Bl012,
- BillNo = query.blNo,
- WorkNo = blDetail.Bld001,
- WorkLine = blDetail.Bld013,
- SuppNo = stockBarcode.SuppNo,
- ItemId = stockBarcode.ItemId
- // CkDepot = stockBarcode.DepotsId
- }).IgnoreColumns(true).ExecuteCommand();
-
- // 鏇存柊宸ュ崟琛ㄦ暟閲�
- executeCommand += db.Updateable<Womdab>()
- .SetColumns(it => new Womdab
- {
- Dab007 = (it.Dab007 ?? 0) + query.Num, // 宸ュ崟鏁伴噺
- Dab020 = (it.Dab020 ?? 0) + query.Num, // 宸插彂鏂欐暟閲�
- Dab021 = (it.Dab021 ?? 0) + query.Num // 宸插彂鏂欐暟閲�
- })
- .Where(it => it.Guid == womdab.DaaGuid && it.Dab003 == womdab.Dab003)
- .ExecuteCommand();
-
- // 鏇存柊琛ユ枡鍗曟槑缁嗗凡琛ユ暟閲�
- executeCommand += db.Updateable<MesItemBlDetail>()
- .SetColumns(it => new MesItemBlDetail
- {
- Bld008 = (it.Bld008 ?? 0) + (int)query.Num
- })
- .Where(it => it.Id == blDetail.Id)
- .ExecuteCommand();
-
- // 鑾峰彇鏇存柊鍚庣殑琛ユ枡鍗曟槑缁嗘暟閲�
- var updatedDetail = db.Queryable<MesItemBlDetail>()
- .Where(it => it.Id == blDetail.Id)
- .Select(it => new { it.Bld007, it.Bld008 })
- .First();
-
- if ((updatedDetail.Bld007 ?? 0) <= (updatedDetail.Bld008 ?? 0))
- // 鏇存柊鏄庣粏瀹屾垚鐘舵��
- executeCommand += db.Updateable<MesItemBlDetail>()
- .SetColumns(it => new MesItemBlDetail { Bld011 = 1 })
- .Where(it => it.Id == blDetail.Id)
- .ExecuteCommand();
-
- // 妫�鏌ユ槸鍚﹁繕鏈夋湭瀹屾垚鐨勬槑缁�
-
- var unfinishedDetail = db.Queryable<MesItemBlDetail>()
- .Where(it => it.Mid == mesItemBl.Id && (it.Bld011 ?? 0) == 0)
- .First();
-
- if (unfinishedDetail == null)
- // 濡傛灉娌℃湁鎵惧埌鏈畬鎴愭槑缁嗭紝鍒欐洿鏂拌ˉ鏂欏崟鐘舵�佷负宸插畬鎴�
- executeCommand += db.Updateable<MesItemBl>()
- .SetColumns(it => new MesItemBl
- {
- Bl019 = true,
- WcUser = query.userName,
- WcTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
- })
- .Where(it => it.Id == mesItemBl.Id)
- .ExecuteCommand();
-
- if (executeCommand <= 1) throw new Exception("鏇存柊澶辫触");
-
-
- // 鍒涘缓 鎻掑叆鏃ュ織
- var logService = new LogService();
- var LogMsg = "[PDA]" + query.Type + "銆傛潯鐮併��" + newBarcode + "銆� 鍑哄簱鍗曞彿銆�" + outNo + "銆�";
- logService.CreateLog(db, query.userName, womdaa.Guid.ToString(), "WOMDAA", LogMsg, womdaa.Daa001);
-
-
- return executeCommand;
- }) > 0;
-
- // 鑾峰彇鏈�缁堢殑寰呭彂鏂欐槑缁嗗垪琛�
+ // Get pending list for the supplement order
var pendingList = Db.Queryable<MesItemBl, MesItemBlDetail>((a, b) =>
new JoinQueryInfos(JoinType.Left, a.Id == b.Mid))
.Where((a, b) => a.BlNo == query.blNo
@@ -997,7 +314,7 @@
{
daa001 = query.billNo,
barcode = query.barcode,//鍘熸潯鐮�
- cfBarcode = newBarcode//鎷嗗垎鍚庢潯鐮�
+ cfBarcode = outCfBar//鎷嗗垎鍚庢潯鐮�
};
return dto;
--
Gitblit v1.9.3