HEGII.ss.mes.dashboard/wwwroot/main/DashBoard/rpt603/defect.ashx

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

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 defect : IHttpHandler, IReadOnlySessionState
{
    public void ProcessRequest(HttpContext context)
    {
        context.Response.ContentType = "text/plain";

        string dashBoardName = "改洗（质量）";
        int planType = 11;

        using(IDataAccess conn = DataAccess.Create())
        {
            string dateBegin = DateTime.Now.AddDays(0).ToString("yyyy-MM-dd 00:00:00");
            string dateEnd = DateTime.Now.AddDays(0).ToString("yyyy-MM-dd 23:59:59");

            string sqlStr = @"
            WITH 
            MES AS (
			SELECT
				pass.时间,
				NVL( ROUND( pass.不合格数 / DECODE( pass.检验数, 0, 1, pass.检验数 ), 2 ), 0 ) AS 不合格率半检
			FROM
				(
				SELECT
					TO_NUMBER( TO_CHAR( P.CREATETIME, 'HH24' ) ) AS 时间,
					SUM( CASE WHEN ( P.PROCEDUREID =117 ) THEN 1 ELSE 0 END ) AS 检验数,
							SUM(
							CASE						
									WHEN ( S.PROCEDUREID = 93 OR S.PROCEDUREID = 94 ) 
									AND S.SEMICHECKTYPE = 2 
									AND S.GOODSLEVELTYPEID = 13 --非必填但是有数据可以查
									AND S.VALUEFLAG = 1 
									AND S.BACKOUTFLAG = 0 THEN
										1 ELSE 0 
									END 
									) AS 不合格数 
								FROM
									TP_PM_PRODUCTIONDATA P 
									LEFT JOIN TP_PM_SEMICHECK S ON P.PRODUCTIONDATAID = S.PRODUCTIONDATAID
								WHERE
									P.VALUEFLAG = 1
									AND P.CREATETIME >= @DATEBEGIN@
									AND P.CREATETIME < @DATEEND@   
								GROUP BY
									TO_NUMBER( TO_CHAR( P.CREATETIME, 'HH24' ) )
								) pass 
						ORDER BY 时间
            ),
            PLAN AS (
                SELECT
	                P.PLANNAME AS 项目,
	                P.PLANHOUR AS 时间,
	                P.PLANNUM AS 不良率指标
                FROM
	                TP_MST_DASHBOARD_PLAN P
	                INNER JOIN TP_MST_DASHBOARD B ON P.DASHBOARDID = B.DASHBOARDID 
	                AND B.DASHBOARDNAME = @DASHBOARDNAME@  
	                AND P.PLANTYPE = @PLANTYPE@
					AND TO_NUMBER( TO_CHAR( SYSDATE, 'YYYY' ) ) = P.PLANYEAR 
					AND TO_NUMBER( TO_CHAR( SYSDATE, 'MM' ) ) = P.PLANMONTH 
					AND TO_NUMBER( TO_CHAR( SYSDATE, 'DD' ) ) = P.PLANDAY 
            )
            SELECT 
	            PLAN.项目,
	            PLAN.时间,
	            NVL(PLAN.不良率指标,0) AS 不良率指标,
	            NVL(MES.不合格率半检,0) AS 不良率
            FROM
	            PLAN LEFT JOIN MES ON PLAN.时间 = MES.时间
            ORDER BY  时间
            ";

            //获取查询条件
            List<CDAParameter> sqlPara = new List<CDAParameter>();
            sqlPara.Add(new CDAParameter("DATEBEGIN", Convert.ToDateTime(dateBegin), DataType.DateTime));
            sqlPara.Add(new CDAParameter("DATEEND", Convert.ToDateTime(dateEnd), DataType.DateTime));
            sqlPara.Add(new CDAParameter("DASHBOARDNAME", dashBoardName.ToString()));
            sqlPara.Add(new CDAParameter("PLANTYPE", Convert.ToInt32(planType)));

            //直接获取不分页数据
            DataTable dt = conn.ExecuteDatatable(sqlStr, sqlPara.ToArray());
            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;
        }
    }

}