~tjx/NewPdaSqlServer.git

			@@ -1,11 +1,11 @@
			using System.Data;
			using System.Data.SqlClient;
			using Masuit.Tools;
			using MES.Service.Modes;
			using Microsoft.SqlServer.Server;
			using NewPdaSqlServer.DB;
			using NewPdaSqlServer.Dto.service;
			using NewPdaSqlServer.entity;
			using NewPdaSqlServer.entity.Base;
			using SqlSugar;

			namespace NewPdaSqlServer.service.Wom;
			@@ -109,6 +109,7 @@

			var _strMsg = "";
			var _intSum = "";
			var _cfBar = "";//拆分后条码
			using (var conn = new SqlConnection(DbHelperSQL.strConn))
			{
			using (var cmd = new SqlCommand("[prc_pda_SCLL_CF]", conn))
			@@ -119,8 +120,9 @@
			cmd.CommandType = CommandType.StoredProcedure;
			SqlParameter[] parameters =
			{
			new("@outMsg", SqlDbType.NVarChar, 300),
			new("@outMsg", SqlDbType.NVarChar, 2000),
			new("@outSum", SqlDbType.NVarChar, 300),
			new("@outCfBar", SqlDbType.NVarChar, 300),
			new("@c_User", query.userName),
			new("@p_biLL_no", query.daa001),
			new("@p_item_barcode", query.barcode),
			@@ -128,11 +130,13 @@
			};
			parameters[0].Direction = ParameterDirection.Output;
			parameters[1].Direction = ParameterDirection.Output;
			parameters[2].Direction = ParameterDirection.Output;
			foreach (var parameter in parameters)
			cmd.Parameters.Add(parameter);
			cmd.ExecuteNonQuery();
			_strMsg = parameters[0].Value.ToString();
			_intSum = parameters[1].Value.ToString();
			_cfBar = parameters[2].Value.ToString();


			var result = Convert.ToInt32(_intSum);
			@@ -141,7 +145,8 @@
			var dto = new ProductionPickDto
			{
			daa001 = query.daa001,
			barcode = query.barcode
			barcode = query.barcode,//原条码
			cfBarcode = _cfBar//拆分后条码
			};

			return dto;
			@@ -251,4 +256,572 @@

			return dto;
			}

			/// <summary>
			/// 获取工单条码信息
			/// </summary>
			/// <param name="query"></param>
			/// <returns></returns>
			public dynamic getZsBarInfo(dynamic query)
			{

			var sql = string.Format(@"SELECT TOP 1 A.barCode AS zsBarcode,
			D.name AS lineName,
			D.line_no,
			B.daa001,
			C.item_no,
			C.item_name,
			C.item_model,
			A.quantity as barQty,
			B.daa008 as sumQty,
			isnull((SELECT count(1) FROM WORK_COLLECT WHERE processNo = '{0}' AND ABOUT_GUID = A.ABOUT_GUID),0) AS finQty,
			isnull((SELECT TOP 1 processNo FROM WORK_COLLECT WHERE WORK_COLLECT.barCode = '{1}' AND checkResult = '√' ORDER BY WORK_COLLECT.createDate DESC ),'') AS lastGx
			FROM WORK_TRAC_CODE A
			LEFT JOIN WOMDAA B ON A.ABOUT_GUID = B.guid
			LEFT JOIN MES_ITEMS C ON B.daa002 = C.item_id
			LEFT JOIN MES_WORKSHOP_LINE D ON A.lineId = D.id
			WHERE A.barCode = '{1}'", query.GX, query.Zsbarcode);

			var ZsBarInfo = Db.Ado.SqlQuery<dynamic>(sql);

			if (ZsBarInfo.Count < 1)
			{
			throw new Exception($"该追溯码{query.Zsbarcode}不存在不存在");
			}

			return ZsBarInfo;
			}

			//获取生产
			public dynamic getTraceability(dynamic query)
			{
			var sql = string.Format(@"SELECT processNo, A.barCode, B.lineId, line_no, name AS lineName, checkResult, C.USER_NAME
			FROM WORK_COLLECT A
			LEFT JOIN WORK_TRAC_CODE B ON A.barCode = B.barCode
			LEFT JOIN SYS_USER C ON C.ACCOUNT = A.createBy
			LEFT JOIN MES_WORKSHOP_LINE D ON B.lineId = D.id
			WHERE A.barCode = '{0}'", query.Zsbarcode);

			var Traceability = Db.Ado.SqlQuery<dynamic>(sql);

			return Traceability;
			}

			public dynamic ProductBinding(dynamic query)
			{
			if (query == null) throw new ArgumentNullException(nameof(query), "参数对象不能为 null");

			// 2. 使用 string.IsNullOrEmpty 直接判断字符串属性（避免 NullReferenceException）
			if (string.IsNullOrEmpty(query.userName?.ToString()))
			throw new ArgumentException("用户名不允许为空", nameof(query.userName));

			if (string.IsNullOrEmpty(query.ZsBar?.ToString()))
			throw new ArgumentException("追溯码不允许为空", nameof(query.ZsBar));

			if (string.IsNullOrEmpty(query.LsBar?.ToString()))
			throw new ArgumentException("后盖码不允许为空", nameof(query.LsBar));

			var _strMsg = "";
			var _intSum = "";
			using (var conn = new SqlConnection(DbHelperSQL.strConn))
			{
			using (var cmd = new SqlCommand("prc_pda_product_binding", conn))
			{
			try
			{
			conn.Open();
			cmd.CommandType = CommandType.StoredProcedure;
			SqlParameter[] parameters =
			{
			new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userName },
			new("@pi_trac_barcode", SqlDbType.NVarChar, 100) { Value = query.ZsBar },
			new("@pi_ls_barcode", SqlDbType.NVarChar, 100) { Value = query.LsBar },
			new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
			new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output }
			};

			foreach (var parameter in parameters)
			cmd.Parameters.Add(parameter);

			cmd.ExecuteNonQuery();

			_strMsg = parameters[3].Value?.ToString() ?? "";
			_intSum = parameters[4].Value?.ToString() ?? "-1";

			var result = Convert.ToInt32(_intSum);
			if (result <= 0) throw new Exception(_strMsg);

			var dto = new
			{
			message = _strMsg,
			status = result,
			tracBarcode = query.ZsBar
			};

			return dto;
			}
			catch (Exception ex)
			{
			throw new Exception(ex.Message);
			}
			finally
			{
			conn.Close();
			}
			}
			}
			}


			/// <summary>
			/// 获取卡板信息
			/// </summary>
			/// <param name="query"></param>
			/// <returns></returns>
			public dynamic getKbBarInfo(dynamic query)
			{
			if (string.IsNullOrEmpty(query.Kbbarcode?.ToString()))
			throw new ArgumentException("卡板条码不允许为空", nameof(query.userName));

			var sql = string.Format(@"SELECT OLDQTY,
			ISNULL((SELECT COUNT(1) FROM MES_INV_ITEM_BARCODES_TBMX WHERE ABOUT_TB_BARCODE = '{0}'), 0) AS YSum,
			DAA001
			FROM MES_INV_ITEM_BARCODES A LEFT JOIN WOMDAA B ON A.ABOUT_GUID = B.guid
			WHERE ITEM_BARCODE = '{0}' ", query.Kbbarcode);

			var ZsBarInfo = Db.Ado.SqlQuery<dynamic>(sql);

			if (ZsBarInfo.Count < 1)
			{
			throw new Exception($"该卡板码{query.Zsbarcode}不存在！");
			}

			return ZsBarInfo;
			}

			/// <summary>
			/// 获取卡板明细信息
			/// </summary>
			/// <param name="query"></param>
			/// <returns></returns>
			public dynamic getKbBarMxInfo(dynamic query)
			{

			var sql = string.Format(@"SELECT ITEM_BARCODE, QUANTITY,item_no,item_name,item_model
			FROM MES_INV_ITEM_BARCODES_TBMX A
			LEFT JOIN WOMDAA B ON B.daa001 = A.DAA_001
			LEFT JOIN MES_ITEMS C ON C.item_id = B.daa002
			WHERE ABOUT_TB_BARCODE = '{0}' ", query.Kbbarcode);

			var ZsBarInfo = Db.Ado.SqlQuery<dynamic>(sql);


			return ZsBarInfo;
			}

			/// <summary>
			/// 获取箱明细信息
			/// </summary>
			/// <param name="query"></param>
			/// <returns></returns>
			public dynamic getXBarInfo(dynamic query)
			{
			//var sql1 = string.Format(@"SELECT 1 FROM MES_INV_ITEM_BARCODES_TBMX WHERE ITEM_BARCODE = '{0}' ", query.Xbarcode);

			//var IS_CZ = Db.Ado.SqlQuery<dynamic>(sql1);

			//if (IS_CZ.Count < 1)
			//{
			// throw new Exception($"该箱条码[{query.Xbarcode}]不存在！");
			//}


			// 校验存储过程
			if (string.IsNullOrEmpty(query.KbBar?.ToString()))
			throw new ArgumentException("卡板条码不允许为空", nameof(query.KbBar));
			// 校验存储过程
			if (string.IsNullOrEmpty(query.Xbarcode?.ToString()))
			throw new ArgumentException("箱条码不允许为空", nameof(query.Xbarcode));

			var successRecords = new List<dynamic>();
			using (var conn = new SqlConnection(DbHelperSQL.strConn))
			{
			conn.Open();
			using (var transaction = conn.BeginTransaction()) // 开启事务
			{
			try
			{

			using (var cmd = new SqlCommand("prc_pda_Xbar_binding_JY", conn, transaction))
			{
			cmd.CommandType = CommandType.StoredProcedure;
			SqlParameter[] parameters =
			{
			new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userName },
			new("@pi_kb_barcode", SqlDbType.NVarChar, 100) { Value = query.KbBar },
			new("@pi_ls_barcode", SqlDbType.NVarChar, 100) { Value = query.Xbarcode },
			new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
			new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output }
			};

			foreach (var parameter in parameters)
			cmd.Parameters.Add(parameter);

			cmd.ExecuteNonQuery();

			var _strMsg = parameters[3].Value?.ToString() ?? "";
			var _intSum = parameters[4].Value?.ToString() ?? "-1";

			var result = Convert.ToInt32(_intSum);
			if (result <= 0)
			{
			//transaction.Rollback();
			throw new Exception(_strMsg);
			//return new {
			// status = result,
			// message = $"操作回滚：{_strMsg}",
			// failedBarcode = xBar.iteM_BARCODE,
			// successCount = successRecords.Count
			//};
			}

			}

			transaction.Commit(); // 全部成功提交事务
			}
			catch (Exception ex)
			{
			transaction.Rollback();
			throw new Exception($"异常信息：{ex.Message}");
			}
			finally
			{
			conn.Close();
			}
			}
			}

			var sql2 = string.Format(@"SELECT ITEM_BARCODE, QUANTITY,item_no,item_name,item_model,'0' as is_hedui
			FROM MES_INV_ITEM_BARCODES_TBMX A
			LEFT JOIN WOMDAA B ON B.daa001 = A.DAA_001
			LEFT JOIN MES_ITEMS C ON C.item_id = B.daa002
			WHERE DAA_001 = '{0}'
			AND ITEM_BARCODE = '{1}'
			AND ISNULL(ABOUT_TB_BARCODE, '') = '' ", query.DAA001, query.Xbarcode);

			var ZsBarInfo = Db.Ado.SqlQuery<dynamic>(sql2);

			if (ZsBarInfo.Count < 1)
			{
			throw new Exception($"该箱条码[{query.Xbarcode}]对应工单与托码工单[{query.DAA001}]不匹配或已绑定托码！");
			}

			return ZsBarInfo;
			}

			public dynamic submitAllChecked(dynamic query)
			{
			if (query == null) throw new ArgumentNullException(nameof(query), "参数对象不能为 null");

			// 参数校验强化
			if (string.IsNullOrEmpty(query.userName?.ToString()))
			throw new ArgumentException("用户名不允许为空", nameof(query.userName));

			if (string.IsNullOrEmpty(query.KbBar?.ToString()))
			throw new ArgumentException("卡板条码不允许为空", nameof(query.KbBar));

			if (query.XbarInfo == null \|\| !((IEnumerable<dynamic>)query.XbarInfo).GetEnumerator().MoveNext())
			throw new ArgumentException("箱码列表不能为空", nameof(query.XbarInfo));

			var successRecords = new List<dynamic>();
			using (var conn = new SqlConnection(DbHelperSQL.strConn))
			{
			conn.Open();
			using (var transaction = conn.BeginTransaction()) // 开启事务
			{
			try
			{
			foreach (var xBar in query.XbarInfo) // 遍历箱码集合
			{
			using (var cmd = new SqlCommand("prc_pda_Xbar_binding", conn, transaction))
			{
			cmd.CommandType = CommandType.StoredProcedure;
			SqlParameter[] parameters =
			{
			new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userName },
			new("@pi_kb_barcode", SqlDbType.NVarChar, 100) { Value = query.KbBar },
			new("@pi_ls_barcode", SqlDbType.NVarChar, 100) { Value = xBar.iteM_BARCODE },
			new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
			new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output }
			};

			foreach (var parameter in parameters)
			cmd.Parameters.Add(parameter);

			cmd.ExecuteNonQuery();

			var _strMsg = parameters[3].Value?.ToString() ?? "";
			var _intSum = parameters[4].Value?.ToString() ?? "-1";

			var result = Convert.ToInt32(_intSum);
			if (result <= 0)
			{
			//transaction.Rollback();
			throw new Exception(_strMsg);
			//return new {
			// status = result,
			// message = $"操作回滚：{_strMsg}",
			// failedBarcode = xBar.iteM_BARCODE,
			// successCount = successRecords.Count
			//};
			}

			successRecords.Add(new {
			kbBarcode = query.KbBar,
			xBarcode = xBar.iteM_BARCODE,
			processTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
			});
			}
			}
			transaction.Commit(); // 全部成功提交事务
			return new {
			status = 1,
			message = "批量绑定成功",
			successCount = successRecords.Count,
			details = successRecords
			};
			}
			catch (Exception ex)
			{
			transaction.Rollback();
			throw new Exception($"异常信息：{ex.Message}");
			}
			finally
			{
			conn.Close();
			}
			}
			}
			}

			public dynamic SubmitKbInspection(dynamic query)
			{
			if (query == null) throw new ArgumentNullException(nameof(query), "参数对象不能为 null");

			// 参数校验
			if (string.IsNullOrEmpty(query.userAccount?.ToString()))
			throw new ArgumentException("用户名不允许为空", nameof(query.userAccount));

			if (string.IsNullOrEmpty(query.KbBar?.ToString()))
			throw new ArgumentException("卡板条码不允许为空", nameof(query.KbBar));

			var _strMsg = "";
			var _status = -1;
			using (var conn = new SqlConnection(DbHelperSQL.strConn))
			{
			using (var cmd = new SqlCommand("prc_pda_KBbar_submitInspection", conn))
			{
			try
			{
			conn.Open();
			cmd.CommandType = CommandType.StoredProcedure;
			SqlParameter[] parameters =
			{
			new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userAccount },
			new("@pi_kb_barcode", SqlDbType.NVarChar, 100) { Value = query.KbBar },
			new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
			new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output }
			};

			foreach (var parameter in parameters)
			cmd.Parameters.Add(parameter);

			cmd.ExecuteNonQuery();

			_strMsg = parameters[2].Value?.ToString() ?? "";
			_status = Convert.ToInt32(parameters[3].Value ?? -1);

			if (_status <= 0) throw new Exception(_strMsg);

			return new {
			message = _strMsg,
			status = _status,
			kbBarcode = query.KbBar
			};
			}
			catch (Exception ex)
			{
			throw new Exception($"卡板送检失败：{ex.Message}");
			}
			finally
			{
			conn.Close();
			}
			}
			}
			}



			//根据工单号返回产品型号和待领物料
			public XcslResultDto GetItemsXcsl(WarehouseQuery query)
			{
			if (string.IsNullOrEmpty(query.daa001)) throw new Exception("工单号为空");

			var womdaa = Db.Queryable<Womdaa, MesItems>((a, i) =>
			new JoinQueryInfos(JoinType.Left,
			a.Daa002 == i.ItemId.ToString()))
			.Where((a, i) => a.Daa001 == query.daa001
			&& a.Fstatus == 1)
			.Select((a, i) => new
			{
			a.Daa001,
			a.CaaGuid,
			a.Daa008
			}).First();

			if (womdaa?.Daa001 == null) throw new Exception("工单号不存在");

			// 使用参数化查询防止SQL注入
			var sqlParams = new List<SugarParameter> { new("@daa001", query.daa001) };

			var sql1 = @"SELECT A.item_out_no,
			SUM(B.QUANTITY) AS BL_Num,
			ISNULL(Sub.JS_Sum, 0) AS JS_Num, -- 使用LEFT JOIN子查询的结果
			C.item_no,
			C.item_name,
			D.USER_NAME
			FROM MES_INV_ITEM_OUTS A
			LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B ON A.GUID = B.ITEM_OUT_ID
			LEFT JOIN MES_ITEMS C ON B.ITEM_ID = C.item_id
			LEFT JOIN SYS_USER D ON A.create_by = D.ACCOUNT
			-- 新增LEFT JOIN聚合子查询
			LEFT JOIN (SELECT B1.ITEM_ID,
			SUM(B1.QUANTITY) AS JS_Sum
			FROM MES_INV_ITEM_OUTS A1
			LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B1 ON A1.GUID = B1.ITEM_OUT_ID
			WHERE A1.out_type = '生产领料'
			AND A1.task_no = @daa001
			AND ISNULL(B1.IS_XCSL, '0') = 1
			GROUP BY B1.ITEM_ID -- 按ITEM_ID提前聚合
			) AS Sub ON Sub.ITEM_ID = C.item_id
			WHERE A.out_type = '生产领料'
			AND A.task_no = @daa001
			GROUP BY A.item_out_no,
			C.item_no,
			C.item_name,
			D.USER_NAME,
			C.item_id,
			ISNULL(Sub.JS_Sum, 0);";

			var XcslItem = Db.Ado.SqlQuery<dynamic>(sql1, sqlParams);

			var sql2 = @"SELECT B.ITEM_BARCODE ,B.QUANTITY,C.item_no,C.item_name,D.USER_NAME,A.create_date
			FROM MES_INV_ITEM_OUTS A
			LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B ON A.GUID = B.ITEM_OUT_ID
			LEFT JOIN MES_ITEMS C ON B.ITEM_ID = C.item_id
			LEFT JOIN SYS_USER D ON A.create_by = D.ACCOUNT
			WHERE out_type = '生产领料'
			AND A.task_no = @daa001
			AND ISNULL(IS_XCSL,'0') = 0";

			var XcslWjsBar = Db.Ado.SqlQuery<dynamic>(sql2, sqlParams);

			var sql3 = @"SELECT B.ITEM_BARCODE ,B.QUANTITY,C.item_no,C.item_name,D.USER_NAME,B.XCSL_CREATE_DATE
			FROM MES_INV_ITEM_OUTS A
			LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B ON A.GUID = B.ITEM_OUT_ID
			LEFT JOIN MES_ITEMS C ON B.ITEM_ID = C.item_id
			LEFT JOIN SYS_USER D ON B.XCSL_CREATE_BY = D.ACCOUNT
			WHERE out_type = '生产领料'
			AND A.task_no = @daa001
			AND ISNULL(IS_XCSL,'0') = 1";

			var XcslYjsBar = Db.Ado.SqlQuery<dynamic>(sql3, sqlParams);


			var dto = new XcslResultDto
			{
			GD_Num = womdaa.Daa008,
			XcslItemList = XcslItem,
			XcslWjsBarList = XcslWjsBar,
			XcslYjsBarList = XcslYjsBar

			};

			return dto;
			}

			// 现场收料展示列表
			public class XcslResultDto
			{
			public int? GD_Num { get; set; }
			public List<dynamic> XcslItemList { get; set; }
			public List<dynamic> XcslWjsBarList { get; set; }
			public List<dynamic> XcslYjsBarList { get; set; }
			}


			public dynamic ScanXcsl(dynamic query)
			{
			if (query == null) throw new ArgumentNullException(nameof(query), "参数对象不能为 null");

			// 参数校验（根据存储过程新增参数）
			if (string.IsNullOrEmpty(query.userAccount?.ToString()))
			throw new ArgumentException("用户名不允许为空", nameof(query.userAccount));

			if (string.IsNullOrEmpty(query.Bar?.ToString()))
			throw new ArgumentException("物料条码不允许为空", nameof(query.Bar));

			if (string.IsNullOrEmpty(query.DAA001?.ToString()))
			throw new ArgumentException("工单单号不允许为空", nameof(query.DAA001));

			var _strMsg = "";
			var _status = -1;
			using (var conn = new SqlConnection(DbHelperSQL.strConn))
			{
			using (var cmd = new SqlCommand("prc_pda_ScanXcsl", conn))
			{
			try
			{
			conn.Open();
			cmd.CommandType = CommandType.StoredProcedure;
			// 根据存储过程调整参数顺序和命名
			SqlParameter[] parameters =
			{
			new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userAccount },
			new("@pi_barcode", SqlDbType.NVarChar, 100) { Value = query.Bar },
			new("@pi_daa001", SqlDbType.NVarChar, 100) { Value = query.DAA001 },
			new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
			new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output }
			};

			foreach (var parameter in parameters)
			cmd.Parameters.Add(parameter);

			cmd.ExecuteNonQuery();

			_strMsg = parameters[3].Value?.ToString() ?? "";
			_status = Convert.ToInt32(parameters[4].Value ?? -1);

			if (_status <= 0) throw new Exception(_strMsg);

			return new
			{
			message = _strMsg,
			status = _status,
			daa001 = query.DAA001,
			barCode = query.Bar
			};
			}
			catch (Exception ex)
			{
			throw new Exception($"产线收料失败：{ex.Message}");
			}
			finally
			{
			conn.Close();
			}
			}
			}
			}
			}