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
					'07-09' 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 ( '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' )
				",
			   new CDAParameter("USERID", "")
		   );

			//二线明细
			twoDt = conn.ExecuteDatatable(@"
				SELECT
					'07-09' 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 ( '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' )
				",
			   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);
		SiemensS7.Write<short>("5.0", (Int16)infoDt.Rows[0]["总计"]);//包装总量
		SiemensS7.Write<short>("5.2", (Int16)infoDt.Rows[0]["一线产量"]);//一线包装产量
		SiemensS7.Write<short>("5.4", (Int16)infoDt.Rows[0]["二线产量"]);//二线包装产量

		SiemensS7.Write<short>("5.6", (Int16)oneDt.Rows[0]["SUM( ct )"]);//一线7:00-9:00产量
		SiemensS7.Write<short>("5.8", (Int16)oneDt.Rows[1]["SUM( ct )"]);//一线9:00-10:00产量
		SiemensS7.Write<short>("5.10", (Int16)oneDt.Rows[2]["SUM( ct )"]);//一线10:00-11:00产量
		SiemensS7.Write<short>("5.12", (Int16)oneDt.Rows[3]["SUM( ct )"]);//一线12:00-14:00产量
		SiemensS7.Write<short>("5.14", (Int16)oneDt.Rows[4]["SUM( ct )"]);//一线14:00-15:00产量
		SiemensS7.Write<short>("5.16", (Int16)oneDt.Rows[5]["SUM( ct )"]);//一线15:00-16:00产量
		SiemensS7.Write<short>("5.18", (Int16)oneDt.Rows[6]["SUM( ct )"]);//一线16:00-17:00产量
		SiemensS7.Write<short>("5.20", (Int16)oneDt.Rows[7]["SUM( ct )"]);//一线17:00-18:00产量

		SiemensS7.Write<short>("5.22", (Int16)twoDt.Rows[0]["SUM( ct )"]);//二线7:00-9:00产量
		SiemensS7.Write<short>("5.24", (Int16)twoDt.Rows[1]["SUM( ct )"]);//二线9:00-10:00产量
		SiemensS7.Write<short>("5.26", (Int16)twoDt.Rows[2]["SUM( ct )"]);//二线10:00-11:00产量
		SiemensS7.Write<short>("5.28", (Int16)twoDt.Rows[3]["SUM( ct )"]);//二线12:00-14:00产量
		SiemensS7.Write<short>("5.30", (Int16)twoDt.Rows[4]["SUM( ct )"]);//二线14:00-15:00产量
		SiemensS7.Write<short>("5.32", (Int16)twoDt.Rows[5]["SUM( ct )"]);//二线15:00-16:00产量
		SiemensS7.Write<short>("5.34", (Int16)twoDt.Rows[6]["SUM( ct )"]);//二线16:00-17:00产量
		SiemensS7.Write<short>("5.36", (Int16)twoDt.Rows[7]["SUM( ct )"]);//二线17:00-18:00产量
		SiemensS7.Close();
		Logger.Debug("发送LED数据完成！");

	}
}