HEGII.gt.mes.pushdata/wwwroot/App_Code/Led.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DK.XuWei.WebMes;
using Curtain.DataAccess;
using Curtain.Log;
using System.Data;

/// <summary>
/// 发送LED看板数据到PLC xuwei add 2023-05-12
/// </summary>
public static class Led
{
	public static void SendData()
	{
		//一线产量明细
		DataTable oneDt = new DataTable();
		//二线产量明细
		DataTable twoDt = new DataTable();
		//总计产量
		DataTable infoDt = new DataTable();
		//读取看板数据
		using (IDataAccess conn = DataAccess.Create())
		{
			//一线明细
			oneDt = conn.ExecuteDatatable(@"
				SELECT
					sj,
					CL 
				FROM
					(
					SELECT
						'07-09' AS sj,
						SUM( ct ) CL 
					FROM
						(
						SELECT
							TO_CHAR( pd.CREATETIME, 'hh24' ) sj,
							COUNT( pd.BARCODE ) ct 
						FROM
							TP_PM_PRODUCTIONDATA pd 
						WHERE
							pd.USERCODE = 'Z26' 
							AND TRUNC( pd.CREATETIME ) = trunc( SYSDATE ) 
							AND pd.VALUEFLAG = 1 
							AND pd.PROCEDUREID = 2 
						GROUP BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						ORDER BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						) 
					WHERE
						sj IN ( '07', '08' ) UNION ALL
					SELECT
						sj,
						ct 
					FROM
						(
						SELECT
							TO_CHAR( pd.CREATETIME, 'hh24' ) sj,
							COUNT( pd.BARCODE ) ct 
						FROM
							TP_PM_PRODUCTIONDATA pd 
						WHERE
							pd.USERCODE = 'Z26' 
							AND TRUNC( pd.CREATETIME ) = trunc( SYSDATE ) 
							AND pd.VALUEFLAG = 1 
							AND pd.PROCEDUREID = 2 
						GROUP BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						ORDER BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						) 
					WHERE
						sj NOT IN ( '07', '08', '12', '13' ) UNION ALL
					SELECT
						'12-14' AS sj,
						SUM( ct ) 
					FROM
						(
						SELECT
							TO_CHAR( pd.CREATETIME, 'hh24' ) sj,
							COUNT( pd.BARCODE ) ct 
						FROM
							TP_PM_PRODUCTIONDATA pd 
						WHERE
							pd.USERCODE = 'Z26' 
							AND TRUNC( pd.CREATETIME ) = trunc( SYSDATE ) 
							AND pd.VALUEFLAG = 1 
							AND pd.PROCEDUREID = 2 
						GROUP BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						ORDER BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						) 
					WHERE
						sj IN ( '12', '13' ) 
					) 
				ORDER BY
					sj
				",
			   new CDAParameter("USERID", "")
		   );

			//二线明细
			twoDt = conn.ExecuteDatatable(@"
				SELECT
					sj,
					CL 
				FROM
					(
					SELECT
									'07-09' AS sj,
									SUM( ct ) CL
								FROM
									(
									SELECT
										TO_CHAR( pd.CREATETIME, 'hh24' ) sj,
										COUNT( pd.BARCODE ) ct 
									FROM
										TP_PM_PRODUCTIONDATA pd 
									WHERE
										pd.USERCODE = 'Z28' 
										AND TRUNC( pd.CREATETIME ) = trunc( SYSDATE ) 
										AND pd.VALUEFLAG = 1 
										AND pd.PROCEDUREID = 2 
									GROUP BY
										TO_CHAR( pd.CREATETIME, 'hh24' ) 
									ORDER BY
										TO_CHAR( pd.CREATETIME, 'hh24' ) 
									) 
								WHERE
									sj IN ( '07', '08' ) UNION ALL
								SELECT
									sj,
									ct 
								FROM
									(
									SELECT
										TO_CHAR( pd.CREATETIME, 'hh24' ) sj,
										COUNT( pd.BARCODE ) ct 
									FROM
										TP_PM_PRODUCTIONDATA pd 
									WHERE
										pd.USERCODE = 'Z28' 
										AND TRUNC( pd.CREATETIME ) = trunc( SYSDATE ) 
										AND pd.VALUEFLAG = 1 
										AND pd.PROCEDUREID = 2 
									GROUP BY
										TO_CHAR( pd.CREATETIME, 'hh24' ) 
									ORDER BY
										TO_CHAR( pd.CREATETIME, 'hh24' ) 
									) 
								WHERE
									sj NOT IN ( '07', '08', '12', '13' ) UNION ALL
								SELECT
									'12-14' AS sj,
									SUM( ct ) 
								FROM
									(
									SELECT
										TO_CHAR( pd.CREATETIME, 'hh24' ) sj,
										COUNT( pd.BARCODE ) ct 
									FROM
										TP_PM_PRODUCTIONDATA pd 
									WHERE
										pd.USERCODE = 'Z28' 
										AND TRUNC( pd.CREATETIME ) = trunc( SYSDATE ) 
										AND pd.VALUEFLAG = 1 
										AND pd.PROCEDUREID = 2 
									GROUP BY
										TO_CHAR( pd.CREATETIME, 'hh24' ) 
									ORDER BY
										TO_CHAR( pd.CREATETIME, 'hh24' ) 
									) 
								WHERE
									sj IN ( '12', '13' )
					) 
				ORDER BY
					sj	
				",
			   new CDAParameter("USERID", "")
		   );

			//总计
			infoDt = conn.ExecuteDatatable(@"
				WITH 一线 AS (
										SELECT
											COUNT( DISTINCT pd.BARCODE ) 一线产量 
										FROM
											TP_PM_PRODUCTIONDATA pd 
										WHERE
											pd.VALUEFLAG = 1 
											AND pd.PROCEDUREID = 2 
											AND pd.USERCODE = 'Z26' 
											AND TRUNC( pd.CREATETIME ) = trunc( SYSDATE ) 
										),
										二线 AS (
										SELECT
											COUNT( DISTINCT pd.BARCODE ) 二线产量 
										FROM
											TP_PM_PRODUCTIONDATA pd 
										WHERE
											pd.VALUEFLAG = 1 
											AND pd.PROCEDUREID = 2 
											AND pd.USERCODE = 'Z28' 
											AND TRUNC( pd.CREATETIME ) = trunc( SYSDATE ) 
										) SELECT
										一线.一线产量,
										二线.二线产量,
										( 一线.一线产量 +二线.二线产量 ) 总计 
									FROM
										一线
										LEFT JOIN 二线 ON 1 =1
				",
			   new CDAParameter("USERID", "")
		   );
		}
		Logger.Debug("读取看板数据完成！");

		//发送数据到LED
		//SiemensS7.Open("172.19.2.71", 102);
		SiemensS7.Open("172.19.26.82", 102);
		if (infoDt != null && infoDt.Rows.Count > 0 && infoDt.Rows[0] != null && infoDt.Rows[0]["总计"] != null)
		{
			SiemensS7.Write<short>("30.0", Convert.ToInt16(infoDt.Rows[0]["总计"]));//包装总量
		}
		else
		{
			SiemensS7.Write<short>("30.0", Convert.ToInt16(0));//包装总量
		}
		if (infoDt != null && infoDt.Rows.Count > 0 && infoDt.Rows[0] != null && infoDt.Rows[0]["一线产量"] != null)
		{
			SiemensS7.Write<short>("30.2", Convert.ToInt16(infoDt.Rows[0]["一线产量"]));//一线包装产量
		}
		else
		{
			SiemensS7.Write<short>("30.2", Convert.ToInt16(0));//一线包装产量
		}
		if (infoDt != null && infoDt.Rows.Count > 0 && infoDt.Rows[0] != null && infoDt.Rows[0]["二线产量"] != null)
		{
			SiemensS7.Write<short>("30.4", Convert.ToInt16(infoDt.Rows[0]["二线产量"]));//二线包装产量
		}
		else
		{
			SiemensS7.Write<short>("30.4", Convert.ToInt16(0));//二线包装产量
		}

		if (oneDt != null && oneDt.Rows.Count > 0 && oneDt.Rows[0] != null && oneDt.Rows[0]["CL"] != null)
		{
			SiemensS7.Write<short>("30.6", Convert.ToInt16(oneDt.Rows[0]["CL"]));//一线7:00-9:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.6", Convert.ToInt16(0));//一线7:00-9:00产量
		}
		if (oneDt != null && oneDt.Rows.Count > 0 && oneDt.Rows[1] != null && oneDt.Rows[1]["CL"] != null)
		{
			SiemensS7.Write<short>("30.8", Convert.ToInt16(oneDt.Rows[1]["CL"]));//一线9:00-10:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.8", Convert.ToInt16(0));//一线9:00-10:00产量
		}
		if (oneDt != null && oneDt.Rows.Count > 0 && oneDt.Rows[2] != null && oneDt.Rows[2]["CL"] != null)
		{
			SiemensS7.Write<short>("30.10", Convert.ToInt16(oneDt.Rows[2]["CL"]));//一线10:00-11:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.10", Convert.ToInt16(0));//一线10:00-11:00产量
		}
		if (oneDt != null && oneDt.Rows.Count > 0 && oneDt.Rows[3] != null && oneDt.Rows[3]["CL"] != null)
		{
			SiemensS7.Write<short>("30.12", Convert.ToInt16(oneDt.Rows[3]["CL"]));//一线12:00-14:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.12", Convert.ToInt16(0));//一线12:00-14:00产量
		}
		if (oneDt != null && oneDt.Rows.Count > 0 && oneDt.Rows[4] != null && oneDt.Rows[4]["CL"] != null)
		{
			SiemensS7.Write<short>("30.14", Convert.ToInt16(oneDt.Rows[4]["CL"]));//一线14:00-15:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.14", Convert.ToInt16(0));//一线14:00-15:00产量
		}
		if (oneDt != null && oneDt.Rows.Count > 0 && oneDt.Rows[5] != null && oneDt.Rows[5]["CL"] != null)
		{
			SiemensS7.Write<short>("30.16", Convert.ToInt16(oneDt.Rows[5]["CL"]));//一线15:00-16:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.16", Convert.ToInt16(0));//一线15:00-16:00产量
		}
		if (oneDt != null && oneDt.Rows.Count > 0 && oneDt.Rows[6] != null && oneDt.Rows[6]["CL"] != null)
		{
			SiemensS7.Write<short>("30.18", Convert.ToInt16(oneDt.Rows[6]["CL"]));//一线16:00-17:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.18", Convert.ToInt16(0));//一线16:00-17:00产量
		}
		if (oneDt != null && oneDt.Rows.Count > 0 && oneDt.Rows[7] != null && oneDt.Rows[7]["CL"] != null)
		{
			SiemensS7.Write<short>("30.20", Convert.ToInt16(oneDt.Rows[7]["CL"]));//一线17:00-18:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.20", Convert.ToInt16(0));//一线17:00-18:00产量
		}


		if (twoDt != null && twoDt.Rows.Count > 0 && twoDt.Rows[0] != null && twoDt.Rows[0]["CL"] != null)
		{
			SiemensS7.Write<short>("30.22", Convert.ToInt16(twoDt.Rows[0]["CL"]));//二线7:00-9:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.22", Convert.ToInt16(0));//二线17:00-18:00产量
		}
		if (twoDt != null && twoDt.Rows.Count > 0 && twoDt.Rows[1] != null && twoDt.Rows[1]["CL"] != null)
		{
			SiemensS7.Write<short>("30.24", Convert.ToInt16(twoDt.Rows[1]["CL"]));//二线9:00-10:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.24", Convert.ToInt16(0));//二线9:00-10:00产量
		}
		if (twoDt != null && twoDt.Rows.Count > 0 && twoDt.Rows[2] != null && twoDt.Rows[2]["CL"] != null)
		{
			SiemensS7.Write<short>("30.26", Convert.ToInt16(twoDt.Rows[2]["CL"]));//二线10:00-11:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.26", Convert.ToInt16(0));//二线10:00-11:00产量
		}
		if (twoDt != null && twoDt.Rows.Count > 0 && twoDt.Rows[3] != null && twoDt.Rows[3]["CL"] != null)
		{
			SiemensS7.Write<short>("30.28", Convert.ToInt16(twoDt.Rows[3]["CL"]));//二线12:00-14:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.28", Convert.ToInt16(0));//二线12:00-14:00产量
		}
		if (twoDt != null && twoDt.Rows.Count > 0 && twoDt.Rows[4] != null && twoDt.Rows[4]["CL"] != null)
		{
			SiemensS7.Write<short>("30.30", Convert.ToInt16(twoDt.Rows[4]["CL"]));//二线14:00-15:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.30", Convert.ToInt16(0));//二线14:00-15:00产量
		}
		if (twoDt != null && twoDt.Rows.Count > 0 && twoDt.Rows[5] != null && twoDt.Rows[5]["CL"] != null)
		{
			SiemensS7.Write<short>("30.32", Convert.ToInt16(twoDt.Rows[5]["CL"]));//二线15:00-16:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.32", Convert.ToInt16(0));//二线15:00-16:00产量
		}
		if (twoDt != null && twoDt.Rows.Count > 0 && twoDt.Rows[6] != null && twoDt.Rows[6]["CL"] != null)
		{
			SiemensS7.Write<short>("30.34", Convert.ToInt16(twoDt.Rows[6]["CL"]));//二线16:00-17:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.34", Convert.ToInt16(0));//二线16:00-17:00产量
		}
		if (twoDt != null && twoDt.Rows.Count > 0 && twoDt.Rows[7] != null && twoDt.Rows[7]["CL"] != null)
		{
			SiemensS7.Write<short>("30.36", Convert.ToInt16(twoDt.Rows[7]["CL"]));//二线17:00-18:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.36", Convert.ToInt16(0));//二线17:00-18:00产量
		}

		SiemensS7.Close();
		Logger.Debug("发送LED数据完成！");

	}
}