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();
		//当前时间 
		string datetime = DateTime.Now.ToShortDateString().ToString();

		//读取看板数据
		using (IDataAccess conn = DataAccess.Create())
		{
			//一线明细
			oneDt = conn.ExecuteDatatable(@"
				SELECT
					sj,
					CL 
				FROM
					(
					SELECT
						'07-10' 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 IN ('Z26','Z101','Z98','Z90') 
							AND pd.CREATETIME >= to_date( @DATETIMENOW@, 'yyyy-mm-dd hh24:mi:ss' ) 
							AND pd.VALUEFLAG = 1 
							AND pd.PROCEDUREID IN (2,82) 
						GROUP BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						ORDER BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						) 
					WHERE
						sj IN ( '07', '08','09' ) 
						
						
						
						
						UNION ALL
					SELECT
						'10-12' AS SJ,
						SUM(ct ) AS CL
					FROM
						(
						SELECT
							TO_CHAR( pd.CREATETIME, 'hh24' ) sj,
							COUNT( pd.BARCODE ) ct 
						FROM
							TP_PM_PRODUCTIONDATA pd 
						WHERE
							pd.USERCODE IN ('Z26','Z101','Z98','Z90') 
							AND pd.CREATETIME >= to_date( @DATETIMENOW@, 'yyyy-mm-dd hh24:mi:ss' ) 
							AND pd.VALUEFLAG = 1 
							AND pd.PROCEDUREID IN (2,82) 
						GROUP BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						ORDER BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						) 
					WHERE
						sj  IN ( '10', '11' ) 
						UNION ALL
					SELECT
						'13-16' AS sj,
						SUM( ct ) 
					FROM
						(
						SELECT
							TO_CHAR( pd.CREATETIME, 'hh24' ) sj,
							COUNT( pd.BARCODE ) ct 
						FROM
							TP_PM_PRODUCTIONDATA pd 
						WHERE
							pd.USERCODE IN ('Z26','Z101','Z98','Z90') 
							AND pd.CREATETIME >= to_date( @DATETIMENOW@, 'yyyy-mm-dd hh24:mi:ss' ) 
							AND pd.VALUEFLAG = 1 
							AND pd.PROCEDUREID IN (2,82) 
						GROUP BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						ORDER BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						) 
					WHERE
						sj IN ( '12', '13','14','15' ) 
						
				UNION ALL
					SELECT
						'16-24' AS sj,
						SUM( ct ) 
					FROM
						(
						SELECT
							TO_CHAR( pd.CREATETIME, 'hh24' ) sj,
							COUNT( pd.BARCODE ) ct 
						FROM
							TP_PM_PRODUCTIONDATA pd 
						WHERE
							pd.USERCODE IN ('Z26','Z101','Z98','Z90') 
							AND pd.CREATETIME >= to_date( @DATETIMENOW@, 'yyyy-mm-dd hh24:mi:ss' ) 
							AND pd.VALUEFLAG = 1 
							AND pd.PROCEDUREID IN (2,82) 
						GROUP BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						ORDER BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						) 
					WHERE
						sj IN ( '16', '17','18','19','20','21','22','23' ) 
					) 
				ORDER BY
					sj
				",
			   new CDAParameter("USERID", ""), new CDAParameter("DATETIMENOW", datetime)
		   );

			//二线明细
			twoDt = conn.ExecuteDatatable(@"
				SELECT
					sj,
					CL 
				FROM
					(
					SELECT
						'07-10' 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 IN ('Z28','Z88','Z80')  
							AND pd.CREATETIME >= to_date( @DATETIMENOW@, 'yyyy-mm-dd hh24:mi:ss' ) 
							AND pd.VALUEFLAG = 1 
							AND pd.PROCEDUREID IN (2,82) 
						GROUP BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						ORDER BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						) 
					WHERE
						sj IN ( '07', '08','09' ) 
						
						
						
						
						UNION ALL
					SELECT
						'10-12' AS SJ,
						SUM(ct ) AS CL
					FROM
						(
						SELECT
							TO_CHAR( pd.CREATETIME, 'hh24' ) sj,
							COUNT( pd.BARCODE ) ct 
						FROM
							TP_PM_PRODUCTIONDATA pd 
						WHERE
							pd.USERCODE IN ('Z28','Z88','Z80') 
							AND pd.CREATETIME >= to_date( @DATETIMENOW@, 'yyyy-mm-dd hh24:mi:ss' ) 
							AND pd.VALUEFLAG = 1 
							AND pd.PROCEDUREID IN (2,82) 
						GROUP BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						ORDER BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						) 
					WHERE
						sj  IN ( '10', '11' ) 
						UNION ALL
					SELECT
						'13-16' AS sj,
						SUM( ct ) 
					FROM
						(
						SELECT
							TO_CHAR( pd.CREATETIME, 'hh24' ) sj,
							COUNT( pd.BARCODE ) ct 
						FROM
							TP_PM_PRODUCTIONDATA pd 
						WHERE
							pd.USERCODE IN ('Z28','Z88','Z80') 
							AND pd.CREATETIME >= to_date( @DATETIMENOW@, 'yyyy-mm-dd hh24:mi:ss' ) 
							AND pd.VALUEFLAG = 1 
							AND pd.PROCEDUREID IN (2,82)  
						GROUP BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						ORDER BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						) 
					WHERE
						sj IN ( '12', '13','14','15' ) 
						
				UNION ALL
					SELECT
						'16-24' AS sj,
						SUM( ct ) 
					FROM
						(
						SELECT
							TO_CHAR( pd.CREATETIME, 'hh24' ) sj,
							COUNT( pd.BARCODE ) ct 
						FROM
							TP_PM_PRODUCTIONDATA pd 
						WHERE
							pd.USERCODE IN ('Z28','Z88','Z80')  
							AND pd.CREATETIME >= to_date( @DATETIMENOW@, 'yyyy-mm-dd hh24:mi:ss' ) 
							AND pd.VALUEFLAG = 1 
							AND pd.PROCEDUREID IN (2,82) 
						GROUP BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						ORDER BY
							TO_CHAR( pd.CREATETIME, 'hh24' ) 
						) 
					WHERE
						sj IN ( '16', '17','18','19','20','21','22','23' ) 
					) 
				ORDER BY
					sj	
				",
			   new CDAParameter("USERID", ""), new CDAParameter("DATETIMENOW", datetime)
		   );

			//总计
			infoDt = conn.ExecuteDatatable(@"
				WITH 一线 AS (
										SELECT
											COUNT( DISTINCT pd.BARCODE ) 一线产量 
										FROM
											TP_PM_PRODUCTIONDATA pd 
										WHERE
											pd.VALUEFLAG = 1 
											AND pd.PROCEDUREID IN (2,82) 
											AND pd.USERCODE IN ('Z26','Z101','Z98','Z90') 
											AND pd.CREATETIME >= to_date( @DATETIMENOW@, 'yyyy-mm-dd hh24:mi:ss' ) 
										),
										二线 AS (
										SELECT
											COUNT( DISTINCT pd.BARCODE ) 二线产量 
										FROM
											TP_PM_PRODUCTIONDATA pd 
										WHERE
											pd.VALUEFLAG = 1 
											AND pd.PROCEDUREID IN (2,82)  
											AND pd.USERCODE IN ('Z28','Z88','Z80') 
											AND pd.CREATETIME >= to_date( @DATETIMENOW@, 'yyyy-mm-dd hh24:mi:ss' ) 
										) SELECT
										一线.一线产量,
										二线.二线产量,
										( 一线.一线产量 +二线.二线产量 ) 总计 
									FROM
										一线
										LEFT JOIN 二线 ON 1 =1
				",
			   new CDAParameter("USERID", ""), new CDAParameter("DATETIMENOW", datetime)
		   );
		}
		Logger.Debug("读取看板数据完成！");
		string jsonStr = new JsonResult(oneDt).ToJson();
		string jsonStr2 = new JsonResult(twoDt).ToJson();
		Logger.Debug("明细数据:" + "   一线:" + jsonStr + "     二线:" + jsonStr2);

		//发送数据到LED
		//SiemensS7.Open("172.19.2.71", 102);
		SiemensS7.Open("172.19.26.82", 102);
		#region 总产量
		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]["一线产量"].ToString() != "")
		{
			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]["二线产量"].ToString() != "")
		{
			SiemensS7.Write<short>("30.4", Convert.ToInt16(infoDt.Rows[0]["二线产量"]));//二线包装产量
		}
		else
		{
			SiemensS7.Write<short>("30.4", Convert.ToInt16(0));//二线包装产量
		}
		#endregion

		#region 一线产量
		if (oneDt != null && oneDt.Rows.Count > 0 && oneDt.Rows[0] != null && oneDt.Rows[0]["CL"].ToString() != "")
		{
			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"].ToString() != "")
		{
			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 > 2 && oneDt.Rows[2] != null && oneDt.Rows[2]["CL"].ToString() != "")
		{
			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 > 3 && oneDt.Rows[3] != null && oneDt.Rows[3]["CL"].ToString() != "")
		{
			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 > 4 && oneDt.Rows[4] != null && oneDt.Rows[4]["CL"].ToString() != "")
		//{
		//	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 > 5 && oneDt.Rows[5] != null && oneDt.Rows[5]["CL"].ToString() != "")
		//{
		//	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 > 6 && oneDt.Rows[6] != null && oneDt.Rows[6]["CL"].ToString() != "")
		//{
		//	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 > 7 && oneDt.Rows[7] != null && oneDt.Rows[7]["CL"].ToString() != "")
		//{
		//	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产量
		//}
		#endregion

		#region 二线产量
		if (twoDt != null && twoDt.Rows.Count > 0 && twoDt.Rows[0] != null && twoDt.Rows[0]["CL"].ToString() != "")
		{
			SiemensS7.Write<short>("30.14", Convert.ToInt16(twoDt.Rows[0]["CL"]));//二线7:00-9:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.14", Convert.ToInt16(0));//二线17:00-18:00产量
		}
		if (twoDt != null && twoDt.Rows.Count > 1 && twoDt.Rows[1] != null && twoDt.Rows[1]["CL"].ToString() != "")
		{
			SiemensS7.Write<short>("30.16", Convert.ToInt16(twoDt.Rows[1]["CL"]));//二线9:00-10:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.16", Convert.ToInt16(0));//二线9:00-10:00产量
		}
		if (twoDt != null && twoDt.Rows.Count > 2 && twoDt.Rows[2] != null && twoDt.Rows[2]["CL"].ToString() != "")
		{
			SiemensS7.Write<short>("30.18", Convert.ToInt16(twoDt.Rows[2]["CL"]));//二线10:00-11:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.18", Convert.ToInt16(0));//二线10:00-11:00产量
		}
		if (twoDt != null && twoDt.Rows.Count > 3 && twoDt.Rows[3] != null && twoDt.Rows[3]["CL"].ToString() != "")
		{
			SiemensS7.Write<short>("30.20", Convert.ToInt16(twoDt.Rows[3]["CL"]));//二线12:00-14:00产量
		}
		else
		{
			SiemensS7.Write<short>("30.20", Convert.ToInt16(0));//二线12:00-14:00产量
		}
		//if (twoDt != null && twoDt.Rows.Count > 4 && twoDt.Rows[4] != null && twoDt.Rows[4]["CL"].ToString() != "")
		//{
		//	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 > 5 && twoDt.Rows[5] != null && twoDt.Rows[5]["CL"].ToString() != "")
		//{
		//	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 > 6 && twoDt.Rows[6] != null && twoDt.Rows[6]["CL"].ToString() != "")
		//{
		//	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 > 7 && twoDt.Rows[7] != null && twoDt.Rows[7]["CL"].ToString() != "")
		//{
		//	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产量
		//}
		#endregion
		Logger.Debug("读取一线数据:" + SiemensS7.Read<short>("30.2", 0));

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

	}
}