~cnj/ZMZ_MES_Api.git

			@@ -47,7 +47,7 @@
			// " 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'\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);
			}

			@@ -222,25 +222,38 @@
			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,
			:ticketNo,
			:snNo,
			:workstationNo,
			:snState,
			:loginId,
			:turnoverCode)";
			// 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("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);
			@@ -264,11 +277,11 @@

			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)
			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);
			@@ -289,7 +302,7 @@
			{
			// 校验
			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 checkParams = new[] { new OracleParameter("partSn", partSn ?? (object)DBNull.Value) };
			var exists = Convert.ToInt32(sqlHelper.ExecuteScalarWithTransaction(connection, transaction, checkSql, checkParams));

			if (exists > 0)
			@@ -299,24 +312,25 @@

			// 插入绑定
			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,
			:user)";
			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(":user", user ?? (object)DBNull.Value)
			};
			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 = @"
			@@ -327,7 +341,7 @@
			:partSn,
			:partName,
			SYSDATE,
			:user,
			:createUser,
			0)";

			sqlHelper.ExecuteNonQueryWithTransaction(sqlLog, bindParams, connection, transaction);
			@@ -342,8 +356,8 @@

			var unbindParams = new[]
			{
			new OracleParameter(":snNo", snNo ?? (object)DBNull.Value),
			new OracleParameter(":partSn", partSn ?? (object)DBNull.Value)
			new OracleParameter("snNo", snNo ?? (object)DBNull.Value),
			new OracleParameter("partSn", partSn ?? (object)DBNull.Value)
			};

			sqlHelper.ExecuteNonQueryWithTransaction(sqlUnbind, unbindParams, connection, transaction);
			@@ -357,15 +371,15 @@
			:partSn,
			:partName,
			SYSDATE,
			:user,
			: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(":user", user ?? (object)DBNull.Value)
			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);
			@@ -388,6 +402,7 @@

			return result;
			}



			/// <summary>
			@@ -725,5 +740,183 @@
			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;
			}



			}
			}