~cnj/ZMZ_MES_Api.git

			@@ -2,12 +2,14 @@
			using MES.Service.DB;
			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;
			using System.Linq;
			using System.Text;
			using System.Threading.Tasks;
			using System.Data;

			namespace MES.Service.service.Warehouse
			{
			@@ -18,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();
			//查工单信息
			@@ -26,16 +28,26 @@
			// join MES_LINE b on A.LINEID=b.ID
			// left join WOMDAA C on C.DAA015=B.LINE_NO
			// left join mes_items s on s.id = C.daa002
			// where LOGINID='" + id+ "' and DAA018='已开工' order by DAA016 asc";
			// where LOGINID='" + 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 A
			left join MESLINE_LIKE_CCLOGIN_WORKS A1 on A.id=a1.LOGINID
			join MES_LINE b on A.LINEID=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 A
			// left join MESLINE_LIKE_CCLOGIN_WORKS A1 on A.id=a1.LOGINID
			// join MES_LINE b on A.LINEID=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);
			}

			@@ -45,19 +57,866 @@
			/// </summary>
			/// <param name="id"></param>
			/// <returns></returns>
			//public bool AddAsnInfo(AsnInfo asnInfo)
			//{
			// OracleSQLHelper SQLHelper = new();
			// //记录产测过站信息
			// 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+"')";
			// bool result = true;
			// int i= SQLHelper.ExecuteNonQuery(sql1);
			// //主表信息插入成功，继续插入项目
			// if (i > 0) {
			// //判断项目是否为空，为空不插入
			// if (asnInfo.failItems.Count>0)
			// {
			// //插入逻辑

			// }
			// else
			// {
			// result = true;
			// }


			// } else { result = false; }

			// 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")
			// {
			// // ====== 校验：同一个配件SN是否已绑定 ======
			// 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)
			{
			OracleSQLHelper SQLHelper = new();
			//记录产测过站信息
			var sql1 = @"INSERT INTO MES_SN_STATUS_DETAILS (ID,TICKET_NO,SN_NO,WORKSTATION_NO,SN_STATE) VALUES (SEQ_MES_SN_STATUS_DETAILS_ID.NEXTVAL,'"+ asnInfo.tickeNo+ "','"+ asnInfo.snNo+ "','"+ asnInfo.workstationNo+ "','"+ asnInfo.snState+ "')";
			var sqlHelper = new OracleSQLHelper();
			bool result = true;
			int i= SQLHelper.ExecuteNonQuery(sql1);
			if (i > 0) {result = true;} else { result = false; }


			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,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)";

			// 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,
			:defectIssue,
			:rootCause,
			:repairMethod,
			:materialHandling,
			SYSDATE,
			:loginId)";

			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)
			{
			transaction.Rollback();
			result = false;
			throw;
			}
			finally
			{
			sqlHelper.CloseConnection(connection);
			}

			return result;
			}



			/// <summary>
			/// 设置中箱码（绑定/解绑 SN）
			/// </summary>
			public string[] SetMediumBoxCode(MesMiddleBox mesMiddleBox)
			{
			var sqlHelper = new OracleSQLHelper();
			string[] msg = new string[2];
			msg[0] = "true";

			var (connection, transaction) = sqlHelper.BeginTransaction();

			try
			{
			if (mesMiddleBox.type.ToUpper() == "A") // 绑定
			{
			if (mesMiddleBox.SnList.Count > 0)
			{
			// 检查是否已存在
			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 (count > 0)
			{
			msg[0] = "false";
			msg[1] = $"该中箱码已经绑定SN数据，如需再次绑定请先解绑！中箱码：{mesMiddleBox.MediumBoxCode}";
			transaction.Rollback();
			return msg;
			}

			foreach (var item in mesMiddleBox.SnList)
			{
			// 插入绑定
			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,
			: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);

			// 插入日志
			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,
			: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
			{
			msg[0] = "false";
			msg[1] = "SN明细为空，无法绑定！";
			}
			}
			else if (mesMiddleBox.type.ToUpper() == "F") // 解绑
			{
			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)
			{
			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,
			: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);
			}

			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
			{
			msg[0] = "false";
			msg[1] = "无效的操作类型！";
			}

			transaction.Commit();
			}
			catch (Exception ex)
			{
			transaction.Rollback();
			msg[0] = "false";
			msg[1] = ex.Message;
			throw;
			}
			finally
			{
			sqlHelper.CloseConnection(connection);
			}

			return msg;
			}

			/// <summary>
			/// 设置栈板码（绑定/解绑 SN）
			/// </summary>
			public string[] SetStackCode(MesPalletBinding mesPalletBinding)
			{
			var sqlHelper = new OracleSQLHelper();
			string[] msg = new string[2];
			msg[0] = "true";

			var (connection, transaction) = sqlHelper.BeginTransaction();

			try
			{
			if (mesPalletBinding.Type.ToUpper() == "A") // 绑定
			{
			if (mesPalletBinding.SnList.Count > 0)
			{
			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 (count > 0)
			{
			msg[0] = "false";
			msg[1] = $"该栈板码已经绑定SN数据，如需再次绑定请先解绑！栈板码：{mesPalletBinding.StackCode}";
			transaction.Rollback();
			return msg;
			}

			foreach (var item in mesPalletBinding.SnList)
			{
			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,
			: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);

			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,
			:stackCode, :loginId, :snNo, :ticketNo, :mediumBoxCode, SYSDATE, :createdBy, 0)";
			sqlHelper.ExecuteNonQueryWithTransaction(sqlLog, insertParams, connection, transaction);
			}
			}
			else
			{
			msg[0] = "false";
			msg[1] = "SN明细为空，请确认！";
			}
			}
			else if (mesPalletBinding.Type.ToUpper() == "F") // 解绑
			{
			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] = $"该栈板码已经在MES入库，不允许解绑！栈板码：{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)
			{
			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,
			: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);
			}

			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)
			{
			transaction.Rollback();
			msg[0] = "false";
			msg[1] = ex.Message;
			throw;
			}
			finally
			{
			sqlHelper.CloseConnection(connection);
			}

			return msg;
			}


			/// <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));

			// 关键：null 会自动存为 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;
			}



			}
			}