~cnj/ZMZ_MES_Api.git

			@@ -2,12 +2,13 @@
			using MES.Service.DB;
			using MES.Service.Dto.service;
			using MES.Service.Modes;
			using MES.Service.service.BasicData;
			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
			{
			@@ -22,11 +23,11 @@
			{
			OracleSQLHelper SQLHelper = new();
			//查工单信息
			var sql1 = @"select DAA001 tickeNo,s.ITEM_NO itemNo,s.ITEM_NAME itemName,s.ITEM_MODEL itemModel,DAA008 plannedNum from MESLINE_LIKE_CCLOGIN A
			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";
			//var sql1 = @"select DAA001 tickeNo,s.ITEM_NO itemNo,s.ITEM_NAME itemName,s.ITEM_MODEL itemModel,DAA008 plannedNum from MESLINE_LIKE_CCLOGIN A
			// 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";

			//改为按站位号反查产线，再通过产线查出工单信息
			//var sql1 = @"select DAA001 tickeNo,s.ITEM_NO itemNo,s.ITEM_NAME itemName,s.ITEM_MODEL itemModel,DAA008 plannedNum from MESLINE_LIKE_CCLOGIN A
			@@ -34,8 +35,16 @@
			// 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";
			// 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";
			return SQLHelper.ExecuteQuery(sql1);
			}

			@@ -45,19 +54,265 @@
			/// </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)
			{
			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
			{
			// 主表插入
			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}')";

			int i = sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sql1);

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

			// 全部成功提交
			transaction.Commit();
			}
			catch (Exception ex)
			{
			transaction.Rollback();
			result = false;
			throw; // 建议记录日志
			}
			finally
			{
			sqlHelper.CloseConnection(connection);
			}

			return result;
			}

			/// <summary>
			/// 设置中箱码
			/// </summary>
			/// <param name="mesMiddleBox"></param>
			/// <returns></returns>
			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)
			{
			string sql1 = "select count(*) count from MES_MIDDLE_BOX where MEDIUMBOXCODE='"+ mesMiddleBox.MediumBoxCode + "'";
			DataTable data= sqlHelper.ExecuteQuery(sql1);
			if (data.Rows[0]["COUNT"].ToString() != "0") {
			msg[0] = "false";
			msg[1] = "该中箱码已经绑定SN数据，如需再次绑定请先解绑！中箱码：" + mesMiddleBox.MediumBoxCode;
			transaction.Rollback();

			return msg;
			}
			foreach (var item in mesMiddleBox.SnList)
			{
			var sql2 = $@"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.ToString()}', -- 中箱码
			'{mesMiddleBox.LoginId}', -- 登录号
			'{item.SnNo}', -- SN号
			'{item.TicketNo}', -- 工单单号
			SYSDATE, -- 创建时间
			'{mesMiddleBox.LoginId}' -- 创建人
			)
			";

			sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sql2);
			}
			}
			else
			{
			msg[0] = "false";
			}
			} else if(mesMiddleBox.type.ToUpper() == "F") {
			//解绑，根据中箱码全部删除
			var sql2 = $@"delete MES_MIDDLE_BOX where MEDIUMBOXCODE='{mesMiddleBox.MediumBoxCode}'";

			sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sql2);
			}
			else
			{
			msg[1] = "SN明细为空，请确认！";
			msg[0] = "false";
			}


			// 全部成功提交
			transaction.Commit();
			}
			catch (Exception ex)
			{
			transaction.Rollback();
			msg[0] = "false";
			msg[1] = ex.Message;
			throw; // 建议记录日志
			}
			finally
			{
			sqlHelper.CloseConnection(connection);
			}

			return msg;
			}


			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)
			{
			string sql1 = "select count(*) count from MES_PALLET_BINDING where STACKCODE='" + mesPalletBinding.StackCode + "'";
			DataTable data = sqlHelper.ExecuteQuery(sql1);
			if (data.Rows[0]["COUNT"].ToString() != "0")
			{
			msg[0] = "false";
			msg[1] = "该栈板码已经绑定SN数据，如需再次绑定请先解绑！栈板码：" + mesPalletBinding.StackCode;
			transaction.Rollback();

			return msg;
			}
			foreach (var item in mesPalletBinding.SnList)
			{
			var sql2 = $@"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}', -- SN号
			'{item.TicketNo}', -- 工单单号
			'{item.MediumBoxCode}', -- 中箱码
			SYSDATE, -- 创建时间
			'{mesPalletBinding.LoginId}' -- 创建人
			)

			";

			sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sql2);
			}
			}
			else
			{
			msg[1] = "SN明细为空，请确认！";
			msg[0] = "false";
			}
			}
			//解绑
			else if(mesPalletBinding.Type.ToUpper() == "F")
			{
			//解绑，根据中箱码全部删除
			var sql2 = $@"delete MES_PALLET_BINDING where STACKCODE='" + mesPalletBinding.StackCode + "'";

			sqlHelper.ExecuteNonQueryWithTransaction(connection, transaction, sql2);
			}


			// 全部成功提交
			transaction.Commit();
			}
			catch (Exception ex)
			{
			transaction.Rollback();
			msg[0] = "false";
			msg[1] = ex.Message;
			throw; // 建议记录日志
			}
			finally
			{
			sqlHelper.CloseConnection(connection);
			}

			return msg;
			}

			}
			}