HEGII.ss.mes.dashboard/wwwroot/main/Procedure/test17/rpt.ashx

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

using System;
using System.Web;
using System.Web.SessionState;
using System.Data;
using Newtonsoft.Json.Linq;
using Curtain.Net.Sockets.PLC;
using Curtain.Net.Sockets.PLC.Model.Siemens;

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() == "packingDataNumber") {
                string sqlStr=@"
			SELECT
						COUNT(1)AS counts
			FROM(
				SELECT
					M.PACKINGLINE AS 线号,
					G.GOODSCODE AS 产品型号,
					GM.MATERIALCODE AS 物料编码,
					L.LOGONAME AS 商标,
					NVL( ML.LOGONAME, '' ) AS 商标变更,
					G.OUTLETDISTANCE AS 出水距,
					M.PLATENUM AS 任务数量,
					M.OUTNUM AS 出库数量,
					CASE WHEN M.STATUS = '-1' THEN '未确认' 
						   WHEN M.STATUS = '0'  THEN '队列中' 
							 WHEN M.STATUS = '1'  THEN '执行中' 
						   WHEN M.STATUS = '2'  THEN '已完成' 
							 WHEN M.STATUS = '3'  THEN '异常终止' 
					END AS 状态,
					M.CREATETIME AS 下单时间 
				FROM
					TP_MST_PACKINGFORM M
					LEFT JOIN V_GOODS_MATERIALCODE GM ON GM.GOODSID = M.GOODSID 
					AND GM.MATERIALCODE = M.MATERIALCODE
					LEFT JOIN TP_MST_GOODS G ON G.GOODSID = M.GOODSID
					LEFT JOIN TP_MST_LOGO L ON L.LOGOID = G.LOGOID
					LEFT JOIN TP_MST_LOGO ML ON ML.LOGOID = M.LOGOID 
				WHERE
					M.VALUEFLAG = '1' 
					AND M.STATUS < 2 ) ";
                object count = conn.ExecuteScalar(sqlStr,null);
                JObject json = new JObject(
                      new JProperty("success",true),
                      new JProperty("counts", Convert.ToInt32(count))
                    );
                context.Response.Write(json.ToString());
            }
            //成型产量 月
            if (context.Request["m"].ToString() == "packingData")
            {
                string sqlStr1 = @"
				SELECT
					ROWNUM AS 序号,
					M.PACKINGLINE AS 线号,
					G.GOODSCODE AS 产品型号,
					GM.MATERIALCODE AS 物料编码,
					L.LOGONAME AS 商标,
					NVL( ML.LOGONAME, '' ) AS 商标变更,
					G.OUTLETDISTANCE AS 出水距,
					M.PLATENUM AS 任务数量,
					M.OUTNUM AS 出库数量,
					CASE WHEN M.STATUS = '-1' THEN '未确认' 
						   WHEN M.STATUS = '0'  THEN '队列中' 
							 WHEN M.STATUS = '1'  THEN '执行中' 
						   WHEN M.STATUS = '2'  THEN '已完成' 
							 WHEN M.STATUS = '3'  THEN '异常终止' 
					END AS 状态,
					M.CREATETIME AS 下单时间 
				FROM
					TP_MST_PACKINGFORM M
					LEFT JOIN V_GOODS_MATERIALCODE GM ON GM.GOODSID = M.GOODSID 
					AND GM.MATERIALCODE = M.MATERIALCODE
					LEFT JOIN TP_MST_GOODS G ON G.GOODSID = M.GOODSID
					LEFT JOIN TP_MST_LOGO L ON L.LOGOID = G.LOGOID
					LEFT JOIN TP_MST_LOGO ML ON ML.LOGOID = M.LOGOID 
				WHERE
					M.VALUEFLAG = '1' 
					AND M.STATUS < 2 
					--AND TRUNC( M.CREATETIME ) = TRUNC( SYSDATE )
				ORDER BY
					ROWNUM,
					M.STATUS,
					M.PACKINGLINE,
					M.begintime
				";
                int page = 1;
                if (Convert.ToInt32(context.Request["page"].ToString()) != 1)
                {
                    page = Convert.ToInt32(context.Request["page"].ToString());
                }
                int rows = HttpContext.Current.Request["rows"] is object ? Convert.ToInt32(HttpContext.Current.Request["rows"]) : 3;
                //获取分页数据
                int total = 0;
                DataTable dt = conn.SelectPages(page, rows, out total, sqlStr1);
                context.Response.Write(new JsonResult(dt) { total = total }.ToJson());
            }
            else if (context.Request["m"].ToString() == "canvasDayCL3")
            {
                DataTable dt = conn.ExecuteDatatable(@"
				SELECT
					TO_CHAR(CREATETIME,'dd') AS SJ,
					SUM( SBZ001 ) SBZ001,
					SUM( SBZ002 ) SBZ002 
				FROM
					(
					SELECT
						CREATETIME,
						CASE WHEN USERCODE = 'SBZ001' THEN 1 ELSE 0 END SBZ001,
						CASE WHEN USERCODE = 'SBZ002' THEN 1 ELSE 0 END SBZ002 
					FROM
						TP_PM_PRODUCTIONDATA 
					WHERE
						USERCODE IN ( 'SBZ001', 'SBZ002' ) 
					AND CREATETIME >= trunc(sysdate)+7/24
					AND TO_CHAR(CREATETIME,'yyyymmddhh24miss') <= to_char(sysdate+1,'yyyyMMdd')||'055959'
					ORDER BY CREATETIME
					)
				GROUP BY TO_CHAR(CREATETIME,'dd')
				ORDER BY TO_CHAR(CREATETIME,'dd')
				"
    );
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
            else if (context.Request["m"].ToString() == "pack")
            {
                try
                {



                    //string url = "http://api-iothub-iotsuite-ensaas.leando.cloud/v1/devices/b3d314fd19584f59b7da1f4a898d8dff/deviceshadow";



                    //string result = JsonClient.Get(url);

                    //JObject json = JObject.Parse(result);




                    //包装机器人
                    JArray dataArray = new JArray();
                    //自动装箱01
                    dataArray.Add(new JObject(new JProperty("data", plcData("172.18.36.37", "240.0").ToString())));
                    //自动码垛01
                    dataArray.Add(new JObject(new JProperty("data", plcData("172.18.36.15", "240.0").ToString())));
                    //自动装箱02
                    dataArray.Add(new JObject(new JProperty("data", plcData("172.18.36.35", "240.0").ToString())));
                    //自动码垛02
                    dataArray.Add(new JObject(new JProperty("data", plcData("172.18.36.95", "240.0").ToString())));

                    //输出数据
                    context.Response.Write(dataArray.ToString());
                }
                catch (Exception ex)
                {
                    context.Response.Write("[]");
                    Curtain.Log.Logger.Error(ex);
                }
            } else if (context.Request["m"].ToString() == "packHour") {

                DataTable dt = conn.ExecuteDatatable(@"
				--包装产量日统计(小时)
								SELECT
									TO_CHAR(CREATETIME,'HH24') AS SJ,
									SUM( SBZ001 ) SBZ001,
									SUM( SBZ002 ) SBZ002 
								FROM
									(
									SELECT
										CREATETIME,
										CASE WHEN USERCODE = 'SBZ001' THEN 1 ELSE 0 END SBZ001,
										CASE WHEN USERCODE = 'SBZ002' THEN 1 ELSE 0 END SBZ002 
									FROM
										TP_PM_PRODUCTIONDATA 
									WHERE
										USERCODE IN ( 'SBZ001', 'SBZ002' ) 
									AND PROCEDUREID = 107
									AND CREATETIME >= TRUNC(SYSDATE) + INTERVAL '7' HOUR
									AND CREATETIME < TRUNC(SYSDATE) + 1 + INTERVAL '6' HOUR
									)
								GROUP BY TO_CHAR(CREATETIME,'HH24')
								ORDER BY TO_CHAR(CREATETIME,'HH24')");
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }else if (context.Request["m"].ToString() == "packDay") {

                DataTable dt = conn.ExecuteDatatable(@"
							WITH  数据 AS (
SELECT					
								TO_CHAR(CREATETIME,'DD') AS SJ,
								SUM( SBZ001 ) SBZ001,
								SUM( SBZ002 ) SBZ002 
							FROM
								(
								SELECT
									 CREATETIME ,
									CASE WHEN USERCODE = 'SBZ001' THEN 1 ELSE 0 END SBZ001,
									CASE WHEN USERCODE = 'SBZ002' THEN 1 ELSE 0 END SBZ002 
								FROM
									TP_PM_PRODUCTIONDATA 
								WHERE
									USERCODE IN ( 'SBZ001', 'SBZ002' ) 
								AND PROCEDUREID = 107
								AND CREATETIME  >= trunc(sysdate,'MM')
								AND CREATETIME  <= trunc(sysdate,'DD')
								)
							GROUP BY TO_CHAR(CREATETIME,'DD')
							ORDER BY TO_CHAR(CREATETIME,'DD') 
							),
							日期 AS (
							SELECT
							TO_CHAR(DAY,'DD') AS SJ
							FROM(
							SELECT TRUNC(SYSDATE,'MM')+ROWNUM-1 AS DAY
								FROM dual connect by ROWNUM<=to_char(last_day(SYSDATE),'dd')
							)
							)
							SELECT
							日期.SJ,
							NVL(数据.SBZ001, 0) AS SBZ001,
							NVL(数据.SBZ002, 0) AS SBZ002
							FROM 日期
							LEFT JOIN 数据 ON 数据.SJ=日期.SJ
							ORDER BY  日期.SJ ASC ");
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
            else if (context.Request["m"].ToString() == "pg")
            {
                string sqlStr = @"
				WITH  基础 AS(
          SELECT
          FD.INSPECTTYPE,
          FD.INSPECTITEM 检验项目,
          COUNT(DISTINCT FD.BARCODE) 检验数量,
          SUM(CASE WHEN FD.ISQUALIFIED = 0 THEN 1 ELSE 0 END) 不合格数量
          FROM TP_PM_FQCITEMSDATA FD
          left join TP_PM_FQCITEMS F ON F.ITEMSID = FD.ITEMSID
          WHERE FD.VALUEFLAG=1
          AND F.inspectname in ('包装检验通用版','功能检验通用版','外观检验标准') and F.valueflag = 1
          AND TRUNC( FD.CREATETIME ) = TRUNC(SYSDATE)
          GROUP BY FD.INSPECTITEM,FD.INSPECTTYPE
        ),
        总不合格数 AS (
          SELECT
          SUM(CASE WHEN FD.ISQUALIFIED = 0 THEN 1 ELSE 0 END) 总不合格数
          FROM TP_PM_FQCITEMSDATA FD
          left join TP_PM_FQCITEMS F ON F.ITEMSID = FD.ITEMSID
          WHERE FD.VALUEFLAG=1
          AND TRUNC( FD.CREATETIME ) = TRUNC(SYSDATE)
           AND F.inspectname in ('包装检验通用版','功能检验通用版','外观检验标准') and F.valueflag = 1
        )
        SELECT
          CASE WHEN INSPECTTYPE = 617 THEN '包装-合格'
             WHEN INSPECTTYPE = 647 THEN '功能-合格'
               WHEN INSPECTTYPE = 616 THEN '外观-合格'
          ELSE '' END AS 类型,
          SUM(检验数量) - SUM(不合格数量) AS 合格
        FROM 基础
        LEFT JOIN 总不合格数 ON 1 = 1 
        GROUP BY INSPECTTYPE
        UNION ALL
        SELECT
          CASE WHEN INSPECTTYPE = 617 THEN '包装-不合格'
             WHEN INSPECTTYPE = 647 THEN '功能-不合格'
               WHEN INSPECTTYPE = 616 THEN '外观-不合格'
          ELSE '' END AS 类型,
          SUM(不合格数量) AS 不合格
        FROM 基础
        LEFT JOIN 总不合格数 ON 1 = 1 
        GROUP BY INSPECTTYPE
 
					";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
        }
    }

    public string plcData(string ip, string db)
    {
        PLCResult<Int16> result = SiemensS7.PlcRead<Int16>(ip, db);
        return result.Data.ToString();
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}