From a619299816f2f632c7c6ba3a1bb869e2c212797d Mon Sep 17 00:00:00 2001
From: fcx <2246384483@qq.com>
Date: 星期五, 19 十二月 2025 14:12:33 +0800
Subject: [PATCH] 对添加巡检单物料编码进行去重
---
StandardPda/MES.Service/service/Warehouse/WomdaaManager.cs | 802 +++++++++++++++++++++++++++++++++++++++++++++++---------
1 files changed, 666 insertions(+), 136 deletions(-)
diff --git a/StandardPda/MES.Service/service/Warehouse/WomdaaManager.cs b/StandardPda/MES.Service/service/Warehouse/WomdaaManager.cs
index b85f6cd..4aca56d 100644
--- a/StandardPda/MES.Service/service/Warehouse/WomdaaManager.cs
+++ b/StandardPda/MES.Service/service/Warehouse/WomdaaManager.cs
@@ -3,6 +3,7 @@
using MES.Service.Dto.service;
using MES.Service.Modes;
using MES.Service.service.BasicData;
+using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
@@ -19,7 +20,7 @@
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
- public DataTable GetTickeInfo(string id)
+ public DataTable GetTickeInfo(string LineId,string LoginId)
{
OracleSQLHelper SQLHelper = new();
//鏌ュ伐鍗曚俊鎭�
@@ -39,12 +40,14 @@
//鍙栫洿鎺ュ叧鑱旂嚎浣撶殑浜ф祴鐧诲綍鍙锋暟鎹紝鍘诲彇鑾峰彇宸ュ崟
- var sql1 = "select DAA001 tickeNo,s.ITEM_NO itemNo,s.ITEM_NAME itemName,s.ITEM_MODEL itemModel,DAA008 plannedNum from " +
- "MESLINE_LIKE_CCLOGIN_WORKS A1" +
- " join MES_LINE b on A1.LOGINID=b.ID" +
- " left join WOMDAA C on C.DAA015=B.LINE_NO" +
- " left join mes_items s on s.id = C.daa002" +
- " where WORKSNO='" + id + "' and DAA018='宸插紑宸�' order by DAA016 asc";
+ //var sql1 = "select DAA001 tickeNo,s.ITEM_NO itemNo,s.ITEM_NAME itemName,s.ITEM_MODEL itemModel,DAA008 plannedNum from " +
+ // "MESLINE_LIKE_CCLOGIN_WORKS A1" +
+ // " join MES_LINE b on A1.LOGINID=b.ID" +
+ // " left join WOMDAA C on C.DAA015=B.LINE_NO" +
+ // " left join mes_items s on s.id = C.daa002" +
+ // " where WORKSNO='" + id + "' and DAA018='宸插紑宸�' order by DAA016 asc";
+ //var sql1 = "select DAA001 tickeNo, s.ITEM_NO itemNo, s.ITEM_NAME itemName, s.ITEM_MODEL itemModel, DAA008 plannedNum\r\nfrom WOMDAA C\r\n left join mes_items s on s.id = C.daa002\r\nleft join womcaa A on A.caa001=C.daa021\r\nwhere C.DAA015 = '" + LineId + "'\r\n and DAA018 = '宸插紑宸�' and A.CAA023='2'\r\norder by DAA016 asc";
+ var sql1 = "select DAA001 tickeNo, s.ITEM_NO itemNo, s.ITEM_NAME itemName, s.ITEM_MODEL itemModel, DAA008 plannedNum\r\nfrom WOMDAA C\r\n left join mes_items s on s.id = C.daa002\r\nleft join womcaa A on A.caa001=C.daa021\r\nleft join MESPROJECT_LIKE_CCLOGIN pr1 on A.PROJECT=pr1.PROJECT_ID\r\nleft join MESLINE_LIKE_CCLOGIN_WORKS2 pr2 on pr1.id=pr2.LOGINID\r\nwhere C.DAA015 = '" + LineId + "' and pr2.WORKSNO='"+ LoginId + "'\r\n and DAA018 = '宸插紑宸�' and A.CAA023='2' and nvl(a.ISCANCEL,0) <>1 \r\norder by DAA016 asc";
return SQLHelper.ExecuteQuery(sql1);
}
@@ -79,54 +82,318 @@
// return result;
//}
+ //public bool AddAsnInfo(AsnInfo asnInfo)
+ //{
+ // var sqlHelper = new OracleSQLHelper();
+ // bool result = true;
+
+ // var (connection, transaction) = sqlHelper.BeginTransaction();
+
+ // try
+ // {
+ // // 1. 涓昏〃鎻掑叆
+ // var sql1 = $@"
+ // INSERT INTO MES_SN_STATUS_DETAILS
+ // (ID, TICKET_NO, SN_NO, WORKSTATION_NO, SN_STATE, LOGIN_ID,TURNOVER_CODE)
+ // VALUES (SEQ_MES_SN_STATUS_DETAILS_ID.NEXTVAL,
+ // '{asnInfo.tickeNo}',
+ // '{asnInfo.snNo}',
+ // '{asnInfo.workstationNo}',
+ // '{asnInfo.snState}',
+ // '{asnInfo.loginId}','{asnInfo.turnoverCode}')";
+ // int i = sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sql1);
+
+ // // 2. 澶辫触椤规彃鍏�
+ // if (i > 0 && asnInfo.failItems?.Count > 0)
+ // {
+ // foreach (var item in asnInfo.failItems)
+ // {
+ // var sql2 = $@"
+ // INSERT INTO CC_FAIL_ITEMS
+ // (ID, MID, DEFECT_ISSUE, ROOT_CAUSE, REPAIR_METHOD, MATERIAL_HANDLING, CREATE_DATE, CREATED_BY)
+ // VALUES (SEQ_CC_FAIL_ITEMS.NEXTVAL,
+ // SEQ_MES_SN_STATUS_DETAILS_ID.CURRVAL,
+ // '{item.DefectIssue}',
+ // '{item.RootCause}',
+ // '{item.RepairMethod}',
+ // '{item.MaterialHandling}',
+ // SYSDATE,
+ // '{asnInfo.loginId}')";
+ // sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sql2);
+ // }
+ // }
+
+ // // 3. 閰嶄欢缁戝畾/瑙ｇ粦 & 鏃ュ織璁板綍
+ // if (asnInfo.accessoryList?.Count > 0)
+ // {
+ // foreach (var accessory in asnInfo.accessoryList)
+ // {
+ // var now = "SYSDATE";
+ // var user = asnInfo.loginId;
+ // var snNo = asnInfo.snNo;
+ // var partSn = accessory.accessorySn;
+ // var partName = accessory.accessoryName;
+
+ // if (accessory.bindingState == "0")
+ // {
+ // // ====== 鏍￠獙锛氬悓涓�涓厤浠禨N鏄惁宸茬粦瀹� ======
+ // var checkSql = $@"
+ // SELECT COUNT(1) FROM MES_SN_PART_BINDING
+ // WHERE PART_SN_NO = '{partSn}'";
+ // var exists = Convert.ToInt32(sqlHelper.ExecuteScalarWithTransaction(connection, transaction, checkSql));
+ // if (exists > 0)
+ // {
+ // throw new Exception($"閰嶄欢SN[{partSn}]宸茬粦瀹氾紝璇峰厛瑙ｇ粦鍐嶇粦瀹氾紒");
+ // }
+
+ // // 缁戝畾
+ // var sqlBind = $@"
+ // INSERT INTO MES_SN_PART_BINDING
+ // (ID, SN_NO, PART_SN_NO, PART_NAME_CN, CREATE_DATE, CREATE_USER)
+ // VALUES (SEQ_MES_SN_PART_BINDING.NEXTVAL,
+ // '{snNo}',
+ // '{partSn}',
+ // '{partName}',
+ // {now},
+ // '{user}')";
+ // sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlBind);
+
+ // // 鏃ュ織
+ // var sqlLog = $@"
+ // INSERT INTO MES_SN_PART_BINDING_LOG
+ // (ID, SN_NO, PART_SN_NO, PART_NAME_CN, CREATE_DATE, CREATE_USER, OP_TYPE)
+ // VALUES (SEQ_MES_SN_PART_BINDING_LOG.NEXTVAL,
+ // '{snNo}',
+ // '{partSn}',
+ // '{partName}',
+ // {now},
+ // '{user}',
+ // 0)";
+ // sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlLog);
+ // }
+ // else if (accessory.bindingState == "1")
+ // {
+ // // 瑙ｇ粦
+ // var sqlUnbind = $@"
+ // DELETE FROM MES_SN_PART_BINDING
+ // WHERE SN_NO = '{snNo}'
+ // AND PART_SN_NO = '{partSn}'";
+ // sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlUnbind);
+
+ // // 鏃ュ織
+ // var sqlLog = $@"
+ // INSERT INTO MES_SN_PART_BINDING_LOG
+ // (ID, SN_NO, PART_SN_NO, PART_NAME_CN, CREATE_DATE, CREATE_USER, OP_TYPE)
+ // VALUES (SEQ_MES_SN_PART_BINDING_LOG.NEXTVAL,
+ // '{snNo}',
+ // '{partSn}',
+ // '{partName}',
+ // {now},
+ // '{user}',
+ // 1)";
+ // sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlLog);
+ // }
+ // }
+ // }
+
+ // transaction.Commit();
+ // }
+ // catch (Exception)
+ // {
+ // transaction.Rollback();
+ // result = false;
+ // throw;
+ // }
+ // finally
+ // {
+ // sqlHelper.CloseConnection(connection);
+ // }
+
+ // return result;
+ //}
+
public bool AddAsnInfo(AsnInfo asnInfo)
{
var sqlHelper = new OracleSQLHelper();
bool result = true;
- //
+
var (connection, transaction) = sqlHelper.BeginTransaction();
try
{
- // 涓昏〃鎻掑叆
- var sql1 = $@"INSERT INTO MES_SN_STATUS_DETAILS
- (ID, TICKET_NO, SN_NO, WORKSTATION_NO, SN_STATE, LOGIN_ID)
- VALUES (SEQ_MES_SN_STATUS_DETAILS_ID.NEXTVAL,
- '{asnInfo.tickeNo}',
- '{asnInfo.snNo}',
- '{asnInfo.workstationNo}',
- '{asnInfo.snState}',
- '{asnInfo.loginId}')";
+ // 1. 涓昏〃鎻掑叆
+ // var sql1 = @"
+ //INSERT INTO MES_SN_STATUS_DETAILS
+ // (ID, TICKET_NO, SN_NO, WORKSTATION_NO, SN_STATE, LOGIN_ID, TURNOVER_CODE,LINE_NO)
+ // VALUES (SEQ_MES_SN_STATUS_DETAILS_ID.NEXTVAL,
+ // :ticketNo,
+ // :snNo,
+ // :workstationNo,
+ // :snState,
+ // :loginId,
+ // :turnoverCode,select nvl(DAA015,'') from WOMDAA where DAA001 = ':ticketNo' and ROWNUM=1)";
- int i = sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sql1);
+ // 1. 涓昏〃鎻掑叆
+ var sql1 = @"
+ INSERT INTO MES_SN_STATUS_DETAILS
+ (ID, TICKET_NO, SN_NO, WORKSTATION_NO, SN_STATE, LOGIN_ID, TURNOVER_CODE, LINE_NO) values (
+ SEQ_MES_SN_STATUS_DETAILS_ID.NEXTVAL,
+ :ticketNo,
+ :snNo,
+ :workstationNo,
+ :snState,
+ :loginId,
+ :turnoverCode,
+ :LINE_NO)";
+ var parameters1 = new[]
+ {
+ new OracleParameter("ticketNo", asnInfo.tickeNo ?? (object)DBNull.Value),
+ new OracleParameter("snNo", asnInfo.snNo ?? (object)DBNull.Value),
+ new OracleParameter("workstationNo", asnInfo.workstationNo ?? (object)DBNull.Value),
+ new OracleParameter("snState", asnInfo.snState ?? (object)DBNull.Value),
+ new OracleParameter("loginId", asnInfo.loginId ?? (object)DBNull.Value),
+ new OracleParameter("turnoverCode", asnInfo.turnoverCode ?? (object)DBNull.Value),
+ new OracleParameter("LINE_NO", asnInfo.lineNo ?? (object)DBNull.Value)
+ };
+ int i = sqlHelper.ExecuteNonQueryWithTransaction(sql1, parameters1, connection, transaction);
+
+ // 2. 澶辫触椤规彃鍏�
if (i > 0 && asnInfo.failItems?.Count > 0)
{
foreach (var item in asnInfo.failItems)
{
- var sql2 = $@"INSERT INTO CC_FAIL_ITEMS
- (ID, MID, DEFECT_ISSUE, ROOT_CAUSE, REPAIR_METHOD, MATERIAL_HANDLING, CREATE_DATE, CREATED_BY)
- VALUES (SEQ_CC_FAIL_ITEMS.NEXTVAL,
- SEQ_MES_SN_STATUS_DETAILS_ID.CURRVAL,
- '{item.DefectIssue}',
- '{item.RootCause}',
- '{item.RepairMethod}',
- '{item.MaterialHandling}',
- SYSDATE,
- '{asnInfo.loginId}')";
+ var sql2 = @"
+ INSERT INTO CC_FAIL_ITEMS
+ (ID, MID, DEFECT_ISSUE, ROOT_CAUSE, REPAIR_METHOD, MATERIAL_HANDLING, CREATE_DATE, CREATED_BY)
+ VALUES (SEQ_CC_FAIL_ITEMS.NEXTVAL,
+ SEQ_MES_SN_STATUS_DETAILS_ID.CURRVAL,
+ :defectIssue,
+ :rootCause,
+ :repairMethod,
+ :materialHandling,
+ SYSDATE,
+ :loginId)";
- sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sql2);
+ var parameters2 = new[]
+ {
+ new OracleParameter("defectIssue", item.DefectIssue ?? (object)DBNull.Value),
+ new OracleParameter("rootCause", item.RootCause ?? (object)DBNull.Value),
+ new OracleParameter("repairMethod", item.RepairMethod ?? (object)DBNull.Value),
+ new OracleParameter("materialHandling", item.MaterialHandling ?? (object)DBNull.Value),
+ new OracleParameter("loginId", asnInfo.loginId ?? (object)DBNull.Value)
+ };
+
+ sqlHelper.ExecuteNonQueryWithTransaction(sql2, parameters2, connection, transaction);
}
}
- // 鍏ㄩ儴鎴愬姛鎻愪氦
+ // 3. 閰嶄欢缁戝畾/瑙ｇ粦 & 鏃ュ織璁板綍
+ if (asnInfo.accessoryList?.Count > 0)
+ {
+ foreach (var accessory in asnInfo.accessoryList)
+ {
+ var snNo = asnInfo.snNo;
+ var partSn = accessory.accessorySn;
+ var partName = accessory.accessoryName;
+ var user = asnInfo.loginId;
+
+ if (accessory.bindingState == "0") // 缁戝畾
+ {
+ // 鏍￠獙
+ var checkSql = "SELECT COUNT(1) FROM MES_SN_PART_BINDING WHERE PART_SN_NO = :partSn";
+ var checkParams = new[] { new OracleParameter("partSn", partSn ?? (object)DBNull.Value) };
+ var exists = Convert.ToInt32(sqlHelper.ExecuteScalarWithTransaction(connection, transaction, checkSql, checkParams));
+
+ if (exists > 0)
+ {
+ throw new Exception($"閰嶄欢SN[{partSn}]宸茬粦瀹氾紝璇峰厛瑙ｇ粦鍐嶇粦瀹氾紒");
+ }
+
+ // 鎻掑叆缁戝畾
+ var sqlBind = @"
+ INSERT INTO MES_SN_PART_BINDING
+ (ID, SN_NO, PART_SN_NO, PART_NAME_CN, CREATE_DATE, CREATE_USER)
+ VALUES (SEQ_MES_SN_PART_BINDING.NEXTVAL,
+ :snNo,
+ :partSn,
+ :partName,
+ SYSDATE,
+ :createUser)";
+
+ var bindParams = new[]
+ {
+ new OracleParameter("snNo", snNo ?? (object)DBNull.Value),
+ new OracleParameter("partSn", partSn ?? (object)DBNull.Value),
+ new OracleParameter("partName", partName ?? (object)DBNull.Value),
+ new OracleParameter("createUser", user ?? (object)DBNull.Value)
+};
+
+ sqlHelper.ExecuteNonQueryWithTransaction(sqlBind, bindParams, connection, transaction);
+
+
+ // 鎻掑叆鏃ュ織
+ var sqlLog = @"
+ INSERT INTO MES_SN_PART_BINDING_LOG
+ (ID, SN_NO, PART_SN_NO, PART_NAME_CN, CREATE_DATE, CREATE_USER, OP_TYPE)
+ VALUES (SEQ_MES_SN_PART_BINDING_LOG.NEXTVAL,
+ :snNo,
+ :partSn,
+ :partName,
+ SYSDATE,
+ :createUser,
+ 0)";
+
+ sqlHelper.ExecuteNonQueryWithTransaction(sqlLog, bindParams, connection, transaction);
+ }
+ else if (accessory.bindingState == "1") // 瑙ｇ粦
+ {
+ // 鍒犻櫎缁戝畾
+ var sqlUnbind = @"
+ DELETE FROM MES_SN_PART_BINDING
+ WHERE SN_NO = :snNo
+ AND PART_SN_NO = :partSn";
+
+ var unbindParams = new[]
+ {
+ new OracleParameter("snNo", snNo ?? (object)DBNull.Value),
+ new OracleParameter("partSn", partSn ?? (object)DBNull.Value)
+ };
+
+ sqlHelper.ExecuteNonQueryWithTransaction(sqlUnbind, unbindParams, connection, transaction);
+
+ // 鎻掑叆鏃ュ織
+ var sqlLog = @"
+ INSERT INTO MES_SN_PART_BINDING_LOG
+ (ID, SN_NO, PART_SN_NO, PART_NAME_CN, CREATE_DATE, CREATE_USER, OP_TYPE)
+ VALUES (SEQ_MES_SN_PART_BINDING_LOG.NEXTVAL,
+ :snNo,
+ :partSn,
+ :partName,
+ SYSDATE,
+ :Puser,
+ 1)";
+
+ var logParams = new[]
+ {
+ new OracleParameter("snNo", snNo ?? (object)DBNull.Value),
+ new OracleParameter("partSn", partSn ?? (object)DBNull.Value),
+ new OracleParameter("partName", partName ?? (object)DBNull.Value),
+ new OracleParameter("Puser", user ?? (object)DBNull.Value)
+ };
+
+ sqlHelper.ExecuteNonQueryWithTransaction(sqlLog, logParams, connection, transaction);
+ }
+ }
+ }
+
transaction.Commit();
}
- catch (Exception ex)
+ catch (Exception)
{
transaction.Rollback();
result = false;
- throw; // 寤鸿璁板綍鏃ュ織
+ throw;
}
finally
{
@@ -136,11 +403,11 @@
return result;
}
+
+
/// <summary>
/// 璁剧疆涓鐮侊紙缁戝畾/瑙ｇ粦 SN锛�
/// </summary>
- /// <param name="mesMiddleBox">涓鐮佸強SN鏁版嵁</param>
- /// <returns>杩斿洖鎵ц鐘舵��</returns>
public string[] SetMediumBoxCode(MesMiddleBox mesMiddleBox)
{
var sqlHelper = new OracleSQLHelper();
@@ -155,49 +422,54 @@
{
if (mesMiddleBox.SnList.Count > 0)
{
- string sql1 = $"select count(*) count from MES_MIDDLE_BOX where MEDIUMBOXCODE = '{mesMiddleBox.MediumBoxCode}'";
- DataTable data = sqlHelper.ExecuteQuery(sql1);
+ // 妫�鏌ユ槸鍚﹀凡瀛樺湪
+ var sqlCheck = "SELECT COUNT(*) FROM MES_MIDDLE_BOX WHERE MEDIUMBOXCODE = :code";
+ var checkParams = new[] { new OracleParameter(":code", mesMiddleBox.MediumBoxCode) };
+ var count = Convert.ToInt32(sqlHelper.ExecuteScalarWithTransaction(connection, transaction, sqlCheck, checkParams));
- if (data.Rows[0]["COUNT"].ToString() != "0")
+ if (count > 0)
{
msg[0] = "false";
- msg[1] = "璇ヤ腑绠辩爜宸茬粡缁戝畾SN鏁版嵁锛屽闇�鍐嶆缁戝畾璇峰厛瑙ｇ粦锛佷腑绠辩爜锛�" + mesMiddleBox.MediumBoxCode;
+ msg[1] = $"璇ヤ腑绠辩爜宸茬粡缁戝畾SN鏁版嵁锛屽闇�鍐嶆缁戝畾璇峰厛瑙ｇ粦锛佷腑绠辩爜锛歿mesMiddleBox.MediumBoxCode}";
transaction.Rollback();
return msg;
}
foreach (var item in mesMiddleBox.SnList)
{
- string sqlInsert = $@"
+ // 鎻掑叆缁戝畾
+ var sqlInsert = @"
INSERT INTO MES_MIDDLE_BOX (
ID, MEDIUMBOXCODE, LOGIN_ID, SN_NO, TICKET_NO, CREATE_DATE, CREATED_BY
) VALUES (
SEQ_MES_MIDDLE_BOX_ID.NEXTVAL,
- '{mesMiddleBox.MediumBoxCode}',
- '{mesMiddleBox.LoginId}',
- '{item.SnNo}',
- '{item.TicketNo}',
- SYSDATE,
- '{mesMiddleBox.LoginId}'
- )";
+ :code, :loginId, :snNo, :ticketNo, SYSDATE, :createdBy)";
+ var insertParams = new[]
+ {
+ new OracleParameter(":code", mesMiddleBox.MediumBoxCode),
+ new OracleParameter(":loginId", mesMiddleBox.LoginId),
+ new OracleParameter(":snNo", item.SnNo),
+ new OracleParameter(":ticketNo", item.TicketNo),
+ new OracleParameter(":createdBy", mesMiddleBox.LoginId)
+ };
+ sqlHelper.ExecuteNonQueryWithTransaction(sqlInsert, insertParams, connection, transaction);
- sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlInsert);
-
- // 鏃ュ織璁板綍鎻掑叆锛氭搷浣滅被鍨� 0锛堢粦瀹氾級
- string logInsert = $@"
+ // 鎻掑叆鏃ュ織
+ var sqlLog = @"
INSERT INTO MES_MIDDLE_BOX_LOG (
ID, MEDIUMBOXCODE, LOGIN_ID, SN_NO, TICKET_NO, CREATE_DATE, CREATED_BY, OPERATION_TYPE
) VALUES (
MES_MIDDLE_BOX_LOG_ID.NEXTVAL,
- '{mesMiddleBox.MediumBoxCode}',
- '{mesMiddleBox.LoginId}',
- '{item.SnNo}',
- '{item.TicketNo}',
- SYSDATE,
- '{mesMiddleBox.LoginId}',
- '0'
- )";
- sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, logInsert);
+ :code, :loginId, :snNo, :ticketNo, SYSDATE, :createdBy, 0)";
+ var logParams = new[]
+ {
+ new OracleParameter(":code", mesMiddleBox.MediumBoxCode),
+ new OracleParameter(":loginId", mesMiddleBox.LoginId),
+ new OracleParameter(":snNo", item.SnNo),
+ new OracleParameter(":ticketNo", item.TicketNo),
+ new OracleParameter(":createdBy", mesMiddleBox.LoginId)
+ };
+ sqlHelper.ExecuteNonQueryWithTransaction(sqlLog, logParams, connection, transaction);
}
}
else
@@ -208,34 +480,32 @@
}
else if (mesMiddleBox.type.ToUpper() == "F") // 瑙ｇ粦
{
- // 鏌ヨ瑕佽В缁戠殑璁板綍
- string sqlSelect = $@"
- SELECT * FROM MES_MIDDLE_BOX WHERE MEDIUMBOXCODE = '{mesMiddleBox.MediumBoxCode}'";
-
- DataTable dt = sqlHelper.ExecuteQuery(sqlSelect);
+ var sqlSelect = "SELECT * FROM MES_MIDDLE_BOX WHERE MEDIUMBOXCODE = :code";
+ var selectParams = new[] { new OracleParameter(":code", mesMiddleBox.MediumBoxCode) };
+ var dt = sqlHelper.ExecuteDataTable(sqlSelect, CommandType.Text, selectParams);
foreach (DataRow row in dt.Rows)
{
- // 鎻掑叆瑙ｇ粦鏃ュ織锛氭搷浣滅被鍨� 1锛堣В缁戯級
- string logInsert = $@"
+ var sqlLog = @"
INSERT INTO MES_MIDDLE_BOX_LOG (
ID, MEDIUMBOXCODE, LOGIN_ID, SN_NO, TICKET_NO, CREATE_DATE, CREATED_BY, OPERATION_TYPE
) VALUES (
MES_MIDDLE_BOX_LOG_ID.NEXTVAL,
- '{row["MEDIUMBOXCODE"]}',
- '{row["LOGIN_ID"]}',
- '{row["SN_NO"]}',
- '{row["TICKET_NO"]}',
- SYSDATE,
- '{mesMiddleBox.LoginId}',
- '1'
- )";
- sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, logInsert);
+ :code, :loginId, :snNo, :ticketNo, SYSDATE, :createdBy, 1)";
+ var logParams = new[]
+ {
+ new OracleParameter(":code", row["MEDIUMBOXCODE"]),
+ new OracleParameter(":loginId", row["LOGIN_ID"]),
+ new OracleParameter(":snNo", row["SN_NO"]),
+ new OracleParameter(":ticketNo", row["TICKET_NO"]),
+ new OracleParameter(":createdBy", mesMiddleBox.LoginId)
+ };
+ sqlHelper.ExecuteNonQueryWithTransaction(sqlLog, logParams, connection, transaction);
}
- // 鎵ц瑙ｇ粦锛堝垹闄わ級
- string sqlDelete = $@"DELETE FROM MES_MIDDLE_BOX WHERE MEDIUMBOXCODE = '{mesMiddleBox.MediumBoxCode}'";
- sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlDelete);
+ var sqlDelete = "DELETE FROM MES_MIDDLE_BOX WHERE MEDIUMBOXCODE = :code";
+ var delParams = new[] { new OracleParameter(":code", mesMiddleBox.MediumBoxCode) };
+ sqlHelper.ExecuteNonQueryWithTransaction(sqlDelete, delParams, connection, transaction);
}
else
{
@@ -250,7 +520,7 @@
transaction.Rollback();
msg[0] = "false";
msg[1] = ex.Message;
- throw; // 寤鸿璁板綍鏃ュ織
+ throw;
}
finally
{
@@ -260,13 +530,9 @@
return msg;
}
-
-
/// <summary>
/// 璁剧疆鏍堟澘鐮侊紙缁戝畾/瑙ｇ粦 SN锛�
/// </summary>
- /// <param name="mesPalletBinding">缁戝畾鍙傛暟</param>
- /// <returns>杩斿洖缁戝畾缁撴灉</returns>
public string[] SetStackCode(MesPalletBinding mesPalletBinding)
{
var sqlHelper = new OracleSQLHelper();
@@ -277,57 +543,48 @@
try
{
- // 缁戝畾
- if (mesPalletBinding.Type.ToUpper() == "A")
+ if (mesPalletBinding.Type.ToUpper() == "A") // 缁戝畾
{
if (mesPalletBinding.SnList.Count > 0)
{
- string sql1 = $"SELECT COUNT(*) COUNT FROM MES_PALLET_BINDING WHERE STACKCODE = '{mesPalletBinding.StackCode}'";
- DataTable data = sqlHelper.ExecuteQuery(sql1);
+ var sqlCheck = "SELECT COUNT(*) FROM MES_PALLET_BINDING WHERE STACKCODE = :stackCode";
+ var checkParams = new[] { new OracleParameter(":stackCode", mesPalletBinding.StackCode) };
+ var count = Convert.ToInt32(sqlHelper.ExecuteScalarWithTransaction(connection, transaction, sqlCheck, checkParams));
- if (data.Rows[0]["COUNT"].ToString() != "0")
+ if (count > 0)
{
msg[0] = "false";
- msg[1] = "璇ユ爤鏉跨爜宸茬粡缁戝畾SN鏁版嵁锛屽闇�鍐嶆缁戝畾璇峰厛瑙ｇ粦锛佹爤鏉跨爜锛�" + mesPalletBinding.StackCode;
+ msg[1] = $"璇ユ爤鏉跨爜宸茬粡缁戝畾SN鏁版嵁锛屽闇�鍐嶆缁戝畾璇峰厛瑙ｇ粦锛佹爤鏉跨爜锛歿mesPalletBinding.StackCode}";
transaction.Rollback();
return msg;
}
foreach (var item in mesPalletBinding.SnList)
{
- string sqlInsert = $@"
+ var sqlInsert = @"
INSERT INTO MES_PALLET_BINDING (
ID, STACKCODE, LOGIN_ID, SN_NO, TICKET_NO, MEDIUMBOXCODE, CREATE_DATE, CREATED_BY
) VALUES (
SEQ_MES_PALLET_BINDING_ID.NEXTVAL,
- '{mesPalletBinding.StackCode}',
- '{mesPalletBinding.LoginId}',
- '{item.SnNo}',
- '{item.TicketNo}',
- '{item.MediumBoxCode}',
- SYSDATE,
- '{mesPalletBinding.LoginId}'
- )";
+ :stackCode, :loginId, :snNo, :ticketNo, :mediumBoxCode, SYSDATE, :createdBy)";
+ var insertParams = new[]
+ {
+ new OracleParameter(":stackCode", mesPalletBinding.StackCode),
+ new OracleParameter(":loginId", mesPalletBinding.LoginId),
+ new OracleParameter(":snNo", item.SnNo),
+ new OracleParameter(":ticketNo", item.TicketNo),
+ new OracleParameter(":mediumBoxCode", item.MediumBoxCode),
+ new OracleParameter(":createdBy", mesPalletBinding.LoginId)
+ };
+ sqlHelper.ExecuteNonQueryWithTransaction(sqlInsert, insertParams, connection, transaction);
- sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlInsert);
-
- // 鎻掑叆缁戝畾鏃ュ織锛堟搷浣滅被鍨� 0锛�
- string logInsert = $@"
+ var sqlLog = @"
INSERT INTO MES_PALLET_BINDING_LOG (
ID, STACKCODE, LOGIN_ID, SN_NO, TICKET_NO, MEDIUMBOXCODE, CREATE_DATE, CREATED_BY, OPERATION_TYPE
) VALUES (
MES_PALLET_BINDING_LOG_ID.NEXTVAL,
- '{mesPalletBinding.StackCode}',
- '{mesPalletBinding.LoginId}',
- '{item.SnNo}',
- '{item.TicketNo}',
- '{item.MediumBoxCode}',
- SYSDATE,
- '{mesPalletBinding.LoginId}',
- '0'
- )";
-
- sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, logInsert);
+ :stackCode, :loginId, :snNo, :ticketNo, :mediumBoxCode, SYSDATE, :createdBy, 0)";
+ sqlHelper.ExecuteNonQueryWithTransaction(sqlLog, insertParams, connection, transaction);
}
}
else
@@ -336,40 +593,49 @@
msg[1] = "SN鏄庣粏涓虹┖锛岃纭锛�";
}
}
- // 瑙ｇ粦
- else if (mesPalletBinding.Type.ToUpper() == "F")
+ else if (mesPalletBinding.Type.ToUpper() == "F") // 瑙ｇ粦
{
- // 鍏堟煡璇㈢粦瀹氱殑璁板綍
- string sqlSelect = $"SELECT * FROM MES_PALLET_BINDING WHERE STACKCODE = '{mesPalletBinding.StackCode}'";
- DataTable dt = sqlHelper.ExecuteQuery( sqlSelect);
+ var sqlCheck = "SELECT COUNT(*) FROM MES_PALLET_BINDING WHERE STACKCODE = :stackCode AND IS_INBOUND = 1";
+ var checkParams = new[] { new OracleParameter(":stackCode", mesPalletBinding.StackCode) };
+ var count = Convert.ToInt32(sqlHelper.ExecuteScalarWithTransaction(connection, transaction, sqlCheck, checkParams));
+
+ if (count > 0)
+ {
+ msg[0] = "false";
+ msg[1] = $"璇ユ爤鏉跨爜宸茬粡鍦∕ES鍏ュ簱锛屼笉鍏佽瑙ｇ粦锛佹爤鏉跨爜锛歿mesPalletBinding.StackCode}";
+ transaction.Rollback();
+ return msg;
+ }
+
+ var sqlSelect = "SELECT * FROM MES_PALLET_BINDING WHERE STACKCODE = :stackCode";
+ var selectParams = new[] { new OracleParameter(":stackCode", mesPalletBinding.StackCode) };
+ var dt = sqlHelper.ExecuteDataTable(sqlSelect, CommandType.Text, selectParams);
foreach (DataRow row in dt.Rows)
{
- // 鎻掑叆瑙ｇ粦鏃ュ織锛堟搷浣滅被鍨� 1锛�
- string logInsert = $@"
+ var sqlLog = @"
INSERT INTO MES_PALLET_BINDING_LOG (
ID, STACKCODE, LOGIN_ID, SN_NO, TICKET_NO, MEDIUMBOXCODE, CREATE_DATE, CREATED_BY, OPERATION_TYPE
) VALUES (
MES_PALLET_BINDING_LOG_ID.NEXTVAL,
- '{row["STACKCODE"]}',
- '{row["LOGIN_ID"]}',
- '{row["SN_NO"]}',
- '{row["TICKET_NO"]}',
- '{row["MEDIUMBOXCODE"]}',
- SYSDATE,
- '{mesPalletBinding.LoginId}',
- '1'
- )";
-
- sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, logInsert);
+ :stackCode, :loginId, :snNo, :ticketNo, :mediumBoxCode, SYSDATE, :createdBy, 1)";
+ var logParams = new[]
+ {
+ new OracleParameter(":stackCode", row["STACKCODE"]),
+ new OracleParameter(":loginId", row["LOGIN_ID"]),
+ new OracleParameter(":snNo", row["SN_NO"]),
+ new OracleParameter(":ticketNo", row["TICKET_NO"]),
+ new OracleParameter(":mediumBoxCode", row["MEDIUMBOXCODE"]),
+ new OracleParameter(":createdBy", mesPalletBinding.LoginId)
+ };
+ sqlHelper.ExecuteNonQueryWithTransaction(sqlLog, logParams, connection, transaction);
}
- // 鍒犻櫎缁戝畾璁板綍
- string sqlDelete = $"DELETE FROM MES_PALLET_BINDING WHERE STACKCODE = '{mesPalletBinding.StackCode}'";
- sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sqlDelete);
+ var sqlDelete = "DELETE FROM MES_PALLET_BINDING WHERE STACKCODE = :stackCode";
+ var delParams = new[] { new OracleParameter(":stackCode", mesPalletBinding.StackCode) };
+ sqlHelper.ExecuteNonQueryWithTransaction(sqlDelete, delParams, connection, transaction);
}
- // 鎻愪氦浜嬪姟
transaction.Commit();
}
catch (Exception ex)
@@ -377,7 +643,7 @@
transaction.Rollback();
msg[0] = "false";
msg[1] = ex.Message;
- throw; // 寤鸿璁板綍鏃ュ織
+ throw;
}
finally
{
@@ -388,5 +654,269 @@
}
+ /// <summary>
+ /// 澶勭悊瑙ｇ粦 / 鎾ら攢瑙ｇ粦
+ /// </summary>
+ public bool ProcessSn(UnbindAsnInfoDto dto)
+ {
+ if (dto == null || dto.snList == null || dto.snList.Count == 0)
+ throw new ArgumentException("杈撳叆鍙傛暟鏃犳晥");
+
+ var sqlHelper = new OracleSQLHelper();
+ bool result = true;
+ var (connection, transaction) = sqlHelper.BeginTransaction();
+
+ try
+ {
+ foreach (var sn in dto.snList)
+ {
+ if (dto.ActionType == 0) // 瑙ｇ粦
+ {
+ string sql = @"
+ UPDATE MES_SN_STATUS_DETAILS
+ SET IS_UNBIND = 1,
+ UNBIND_DATE = SYSDATE,
+ UNBIND_USER = :LoginId
+ WHERE TICKET_NO = :TicketNo
+ AND SN_NO = :SnNo
+ AND NVL(IS_UNBIND, 0) = 0";
+
+ var parameters = new[]
+ {
+ new OracleParameter(":LoginId", dto.loginId),
+ new OracleParameter(":TicketNo", sn.tickeNo),
+ new OracleParameter(":SnNo", sn.snNo)
+ };
+
+ int rows = sqlHelper.ExecuteNonQueryWithTransaction(sql, parameters, connection, transaction);
+ if (rows == 0)
+ {
+ throw new Exception($"瑙ｇ粦澶辫触: 宸ュ崟[{sn.tickeNo}], SN[{sn.snNo}] 鏈壘鍒板彲瑙ｇ粦璁板綍鎴栧凡瑙ｇ粦锛�");
+ }
+ }
+ else if (dto.ActionType == 1) // 鎾ら攢瑙ｇ粦
+ {
+ string sql = @"
+ UPDATE MES_SN_STATUS_DETAILS
+ SET IS_UNBIND = 0,
+ UNBIND_DATE = NULL,
+ UNBIND_USER = NULL
+ WHERE TICKET_NO = :TicketNo
+ AND SN_NO = :SnNo
+ AND NVL(IS_UNBIND, 0) = 1";
+
+ var parameters = new[]
+ {
+ new OracleParameter(":TicketNo", sn.tickeNo),
+ new OracleParameter(":SnNo", sn.snNo)
+ };
+
+ int rows = sqlHelper.ExecuteNonQueryWithTransaction(sql, parameters, connection, transaction);
+ if (rows == 0)
+ {
+ throw new Exception($"鎾ら攢瑙ｇ粦澶辫触: 宸ュ崟[{sn.tickeNo}], SN[{sn.snNo}] 鏈壘鍒板凡瑙ｇ粦璁板綍锛�");
+ }
+ }
+ else
+ {
+ throw new Exception("鏃犳晥鐨勬搷浣滅被鍨嬶紒鍙兘鏄� 0=瑙ｇ粦 鎴� 1=鎾ら攢瑙ｇ粦");
+ }
+ }
+
+ transaction.Commit();
+ }
+ catch (Exception ex)
+ {
+ transaction.Rollback();
+ result = false;
+ // 寤鸿杩欓噷鍐欐棩蹇� ex.Message
+ throw;
+ }
+ finally
+ {
+ connection.Close();
+ }
+
+ return result;
+ }
+
+
+ public string[] SyncStackCodeFromApi(string stackCode, string loginId)
+ {
+ string[] msg = new string[2];
+ msg[0] = "true";
+
+ var sqlHelper = new OracleSQLHelper();
+ var (conn, tran) = sqlHelper.BeginTransaction();
+
+ try
+ {
+ // ---------------------------
+ // 1. 鍒ゆ柇鏄惁宸插叆搴擄紙IS_INBOUND = 1锛�
+ // ---------------------------
+ string sqlCheck = @"
+ SELECT COUNT(*)
+ FROM MES_PALLET_BINDING
+ WHERE STACKCODE = :stackCode
+ AND IS_INBOUND = 1";
+
+ int count = Convert.ToInt32(sqlHelper.ExecuteScalarWithTransaction(
+ conn,
+ tran,
+ sqlCheck,
+ new[] { new OracleParameter(":stackCode", stackCode) }
+ ));
+
+ if (count > 0)
+ {
+ msg[0] = "false";
+ msg[1] = $"璇ユ爤鏉垮凡鍏ュ簱锛屼笉鍏佽閲嶆柊缁戝畾锛佹爤鏉跨爜锛歿stackCode}";
+ tran.Rollback();
+ return msg;
+ }
+
+
+ // ---------------------------
+ // 2. 璋冪敤鎺ュ彛鑾峰彇鏈�鏂� SN 鍒楄〃
+ // ---------------------------
+ var apiUrl = "http://no2api.dream-maker.com/storage/getTransferList";
+ var reqObj = new { stackCode = stackCode };
+ string jsonReq = Newtonsoft.Json.JsonConvert.SerializeObject(reqObj);
+
+ string apiResult = HttpHelper.HttpPost(apiUrl, jsonReq);
+ var obj = Newtonsoft.Json.JsonConvert.DeserializeObject<dynamic>(apiResult);
+
+ if ((int)obj.code != 0)
+ {
+ msg[0] = "false";
+ msg[1] = "鎺ュ彛杩斿洖閿欒锛�" + (string)obj.msg;
+ tran.Rollback();
+ return msg;
+ }
+
+ var snList = obj.data.snList;
+ if (snList == null || snList.Count == 0)
+ {
+ msg[0] = "false";
+ msg[1] = "鎺ュ彛杩斿洖SN鍒楄〃涓虹┖锛�";
+ tran.Rollback();
+ return msg;
+ }
+
+
+ // ---------------------------
+ // 3. 鍒犻櫎鏃х粦瀹�
+ // ---------------------------
+ string sqlDelete = @"DELETE FROM MES_PALLET_BINDING WHERE STACKCODE = :stackCode";
+
+ sqlHelper.ExecuteNonQueryWithTransaction(
+ sqlDelete,
+ new[] { new OracleParameter(":stackCode", stackCode) },
+ conn,
+ tran
+ );
+
+
+ // ---------------------------
+ // 4. 鎵归噺鎻掑叆锛堟柟寮� 1锛氭彁鍓嶅鐞� transitTime锛�
+ // ---------------------------
+ int rowCount = snList.Count;
+
+ string[] arrSnNo = new string[rowCount];
+ string[] arrStackCode = new string[rowCount];
+ string[] arrLoginId = new string[rowCount];
+ string[] arrCreatedBy = new string[rowCount];
+ DateTime?[] arrTransitTime = new DateTime?[rowCount];
+ string[] arrLineNo = new string[rowCount];
+
+ for (int i = 0; i < rowCount; i++)
+ {
+ arrSnNo[i] = (string)snList[i].snNo;
+ arrStackCode[i] = stackCode;
+ arrLoginId[i] = loginId;
+ arrCreatedBy[i] = loginId;
+ arrLineNo[i] = (string)snList[i].lineNo;
+
+ // ------------------------
+ // transitTime 杞崲锛堝叧閿級
+ // ------------------------
+ string timeStr = (string)snList[i].transitTime;
+
+ if (string.IsNullOrWhiteSpace(timeStr))
+ {
+ arrTransitTime[i] = null;
+ }
+ else
+ {
+ DateTime dt;
+ if (DateTime.TryParse(timeStr, out dt))
+ arrTransitTime[i] = dt;
+ else
+ arrTransitTime[i] = null; // 闈炴硶鏃堕棿鐩存帴缃┖
+ }
+ }
+
+ string sqlBatchInsert = @"
+ INSERT INTO MES_PALLET_BINDING
+ (ID, STACKCODE, LOGIN_ID, SN_NO, TICKET_NO, MEDIUMBOXCODE, CREATE_DATE, CREATED_BY, BIND_TRANSIT_TIME, LINE_NO)
+ VALUES
+ (
+ SEQ_MES_PALLET_BINDING_ID.NEXTVAL,
+ :STACKCODE,
+ :LOGIN_ID,
+ :SN_NO,
+ NULL,
+ NULL,
+ SYSDATE,
+ :CREATED_BY,
+ :BIND_TRANSIT_TIME,
+ :LINE_NO
+ )";
+
+ using (OracleCommand cmd = (OracleCommand)conn.CreateCommand())
+ {
+ cmd.Transaction = (OracleTransaction)tran;
+ cmd.CommandText = sqlBatchInsert;
+ cmd.ArrayBindCount = rowCount;
+
+ cmd.Parameters.Add(new OracleParameter(":STACKCODE", arrStackCode));
+ cmd.Parameters.Add(new OracleParameter(":LOGIN_ID", arrLoginId));
+ cmd.Parameters.Add(new OracleParameter(":SN_NO", arrSnNo));
+ cmd.Parameters.Add(new OracleParameter(":CREATED_BY", arrCreatedBy));
+
+ // 鍏抽敭锛歯ull 浼氳嚜鍔ㄥ瓨涓� Oracle NULL
+ var pTransit = new OracleParameter(":BIND_TRANSIT_TIME", OracleDbType.Date);
+ pTransit.Value = arrTransitTime;
+ cmd.Parameters.Add(pTransit);
+
+ cmd.Parameters.Add(new OracleParameter(":LINE_NO", arrLineNo));
+
+ cmd.ExecuteNonQuery();
+ }
+
+
+ // ---------------------------
+ // 5. 鎻愪氦浜嬪姟
+ // ---------------------------
+ tran.Commit();
+ msg[1] = "鍚屾鎴愬姛锛�";
+ }
+ catch (Exception ex)
+ {
+ tran.Rollback();
+ msg[0] = "false";
+ msg[1] = ex.Message;
+ throw;
+ }
+ finally
+ {
+ sqlHelper.CloseConnection(conn);
+ }
+
+ return msg;
+ }
+
+
+
}
}
--
Gitblit v1.9.3