HEGII.ss.mes.dashboard/wwwroot/main/Split/ScreenM404/rpt.ashx

<%@ WebHandler Language="C#" Class="rpt" %>

using System;
using System.Web;
using System.Web.SessionState;
using System.Data;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Curtain.DataAccess;
using DK.XuWei.WebMes;

public class rpt : IHttpHandler, IReadOnlySessionState
{
    public void ProcessRequest(HttpContext context)
    {
        context.Response.ContentType = "text/plain";
        using(IDataAccess conn = DataAccess.Create())
        {
            if (context.Request["m"].ToString() == "master")
            {
                    //读取报表数据
                    string sqlStr = @"
					WITH hour AS (
						SELECT
							00 AS h 
						FROM
							dual UNION
						SELECT
							01 AS h 
						FROM
							dual UNION
						SELECT
							02 AS h 
						FROM
							dual UNION
						SELECT
							03 AS h 
						FROM
							dual UNION
						SELECT
							04 AS h 
						FROM
							dual UNION
						SELECT
							05 AS h 
						FROM
							dual UNION
						SELECT
							06 AS h 
						FROM
							dual UNION
						SELECT
							07 AS h 
						FROM
							dual UNION
						SELECT
							08 AS h 
						FROM
							dual UNION
						SELECT
							09 AS h 
						FROM
							dual UNION
						SELECT
							10 AS h 
						FROM
							dual UNION
						SELECT
							11 AS h 
						FROM
							dual UNION
						SELECT
							12 AS h 
						FROM
							dual UNION
						SELECT
							13 AS h 
						FROM
							dual UNION
						SELECT
							14 AS h 
						FROM
							dual UNION
						SELECT
							15 AS h 
						FROM
							dual UNION
						SELECT
							16 AS h 
						FROM
							dual UNION
						SELECT
							17 AS h 
						FROM
							dual UNION
						SELECT
							18 AS h 
						FROM
							dual UNION
						SELECT
							19 AS h 
						FROM
							dual UNION
						SELECT
							20 AS h 
						FROM
							dual UNION
						SELECT
							21 AS h 
						FROM
							dual UNION
						SELECT
							22 AS h 
						FROM
							dual UNION
						SELECT
							23 AS h 
						FROM
							dual
						)

						SELECT
						h.H AS TIME,
						'4楼干燥房1' AS NAME1,
						DECODE( NVL( t.设定温度, 0 ), 0, 0,NVL( t.房内温度, 0 )) AS WD1,
						NVL( t.设定温度, 0 ) AS WD1_SD,
						DECODE( NVL( t.设定湿度, 0 ), 0, 0, NVL( t.房内湿度, 0 )) AS SD1,
						NVL( t.设定湿度, 0 ) AS SD1_SD,
						'4楼干燥房2' AS NAME2,
						DECODE( NVL( t2.设定温度, 0 ), 0, 0,NVL( t2.房内温度, 0 )) AS WD2,
						NVL( t2.设定温度, 0 ) AS WD2_SD,
						DECODE( NVL( t2.设定湿度, 0 ), 0, 0, NVL( t2.房内湿度, 0 )) AS SD2,
						NVL( t2.设定湿度, 0 ) AS SD2_SD,
						'4楼干燥房3' AS NAME3,
						DECODE( NVL( t3.设定温度, 0 ), 0, 0,NVL( t3.房内温度, 0 )) AS WD3,
						NVL( t3.设定温度, 0 ) AS WD3_SD,
						DECODE( NVL( t3.设定湿度, 0 ), 0, 0, NVL( t3.房内湿度, 0 )) AS SD3,
						NVL( t3.设定湿度, 0 ) AS SD3_SD,
						'4楼干燥房4' AS NAME1,
						DECODE( NVL( t4.设定温度, 0 ), 0, 0,NVL( t4.房内温度, 0 )) AS WD4,
						NVL( t4.设定温度, 0 ) AS WD4_SD,
						DECODE( NVL( t4.设定湿度, 0 ), 0, 0, NVL( t4.房内湿度, 0 )) AS SD4,
						NVL( t4.设定湿度, 0 ) AS SD4_SD
					FROM
						(
						SELECT
							TO_CHAR( V.CREATETIME, 'HH24' ) AS 读取时间,
							ROUND( AVG( V.V100 ), 2 ) AS 房内温度,
							ROUND( AVG( V.V104 ), 2 ) AS 设定温度,
							ROUND( AVG( V.V108 ), 2 ) AS 房内湿度, 
							ROUND( AVG( V.V112 ), 2 ) AS 设定湿度 
						FROM
							T_XT_PLC_V V
							LEFT JOIN T_XT_PLC P ON P.PLC_ID = V.PLC_ID 
						WHERE
							V.CREATETIME >= TRUNC( date'2021-11-21' ) 
							AND V.CREATETIME < TRUNC( date'2021-11-21' ) + 1 
							AND P.PLC_IP = '172.18.40.51' 
						GROUP BY
							TO_CHAR( V.CREATETIME, 'HH24' ) 
						) t
						RIGHT JOIN ( SELECT h FROM hour ) h ON t.读取时间 = h.h
						LEFT JOIN (
						SELECT
							TO_CHAR( V.CREATETIME, 'HH24' ) AS 读取时间,
							ROUND( AVG( V.V100 ), 2 ) AS 房内温度,
							ROUND( AVG( V.V104 ), 2 ) AS 设定温度,
							ROUND( AVG( V.V108 ), 2 ) AS 房内湿度, 
							ROUND( AVG( V.V112 ), 2 ) AS 设定湿度 
						FROM
							T_XT_PLC_V V
							LEFT JOIN T_XT_PLC P ON P.PLC_ID = V.PLC_ID 
						WHERE
							V.CREATETIME >= TRUNC( date'2021-11-21' ) 
							AND V.CREATETIME < TRUNC( date'2021-11-21' ) + 1 
							AND P.PLC_IP = '172.18.40.52' 
						GROUP BY
							TO_CHAR( V.CREATETIME, 'HH24' ) 
						) t2 ON t2.读取时间 = t.读取时间
						LEFT JOIN (
						SELECT
							TO_CHAR( V.CREATETIME, 'HH24' ) AS 读取时间,
							ROUND( AVG( V.V100 ), 2 ) AS 房内温度,
							ROUND( AVG( V.V104 ), 2 ) AS 设定温度,
							ROUND( AVG( V.V108 ), 2 ) AS 房内湿度, 
							ROUND( AVG( V.V112 ), 2 ) AS 设定湿度 
						FROM
							T_XT_PLC_V V
							LEFT JOIN T_XT_PLC P ON P.PLC_ID = V.PLC_ID 
						WHERE
							V.CREATETIME >= TRUNC( date'2021-11-21' ) 
							AND V.CREATETIME < TRUNC( date'2021-11-21' ) + 1 
							AND P.PLC_IP = '172.18.40.53' 
						GROUP BY
							TO_CHAR( V.CREATETIME, 'HH24' ) 
						) t3 ON t3.读取时间 = t.读取时间
						LEFT JOIN (
						SELECT
							TO_CHAR( V.CREATETIME, 'HH24' ) AS 读取时间,
							ROUND( AVG( V.V100 ), 2 ) AS 房内温度,
							ROUND( AVG( V.V104 ), 2 ) AS 设定温度,
							ROUND( AVG( V.V108 ), 2 ) AS 房内湿度, 
							ROUND( AVG( V.V112 ), 2 ) AS 设定湿度 
						FROM
							T_XT_PLC_V V
							LEFT JOIN T_XT_PLC P ON P.PLC_ID = V.PLC_ID 
						WHERE
							V.CREATETIME >= TRUNC( date'2021-11-21' ) 
							AND V.CREATETIME < TRUNC( date'2021-11-21' ) + 1 
							AND P.PLC_IP = '172.18.40.54' 
						GROUP BY
							TO_CHAR( V.CREATETIME, 'HH24' ) 
						) t4 ON t4.读取时间 = t.读取时间
					ORDER BY h.H
                    ";
                    //直接获取不分页数据
                    DataTable dt = conn.ExecuteDatatable(sqlStr);
                    string jsonStr = new JsonResult(dt).ToJson();
                    context.Response.Write(jsonStr);
            }
        }
    }

    public decimal toNumber(object o)
    {
        if (o != DBNull.Value)
            return Convert.ToDecimal(o);
        else
            return 0;
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}