HEGII.ss.mes.dashboard/wwwroot/main/DashBoard/rpt404/semi2dayCount.ashx

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

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

        //成型（质量）、半检一检（质量）、改洗（质量）、半检二检（质量）、施釉（质量）、烧成（质量）、分级一检（质量）、功能检测（质量）
        string dashBoardName = "半检二检（质量）";
        //11:小时质量 21:日质量 31:月质量 41:年质量
        int planType;

        using(IDataAccess conn = DataAccess.Create())
        {

        //半检二检累计
        if (context.Request["m"] == "total") { 
                string dateBegin = DateTime.Now.ToString("yyyy-MM-dd 00:00:00");
                string dateEnd = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd 00:00:00");

                string sqlStr = @"
				SELECT
					T1.不良数 AS NUM2 ,
					T3.次品数 AS NUM3 ,
					T2.总数 - T1.不良数 - T3.次品数 AS NUM1 ,
					ROUND(T1.不良数 /	DECODE(NVL(T2.总数,0),0,1,T2.总数),2) * 100 AS UNQUALIFIED,
					ROUND(T3.次品数 /	DECODE(NVL(T2.总数,0),0,1,T2.总数),2) * 100 AS SCRAP,
					ROUND((T2.总数 - T1.不良数 - T3.次品数) / DECODE(NVL(T2.总数,0),0,1,T2.总数),2) * 100  AS QUALIFIED 
				FROM
					(
						SELECT
							'1' AS 外键,
							count( DISTINCT s.barcode ) AS 不良数 
							FROM
								TP_PM_SEMICHECK s
							WHERE
								s.BACKOUTFLAG = 0 
								AND instr( s.GROUTINGLINENAME, 'C' ) = 1
								AND s.PROCEDUREID IN ( 117 )
								AND s.GOODSLEVELTYPEID NOT IN(7,8)
								AND s.VALUEFLAG = 1 
								AND s.CREATETIME >= @DATEBEGIN@
								AND s.CREATETIME < @DATEEND@
					) T1
				INNER JOIN 
					( 
					  SELECT '1' AS 外键, COUNT( DISTINCT BARCODE ) AS 总数 
					  FROM TP_PM_PRODUCTIONDATA WHERE PROCEDUREID IN ( 117 ) AND VALUEFLAG = 1 AND CREATETIME >= @DATEBEGIN@ AND CREATETIME < @DATEEND@ AND instr(GROUTINGLINENAME, 'C' ) = 1 
					) T2 
				ON T1.外键 = T2.外键
				INNER JOIN 
					( 
						SELECT
							'1' AS 外键,
							count( DISTINCT s.barcode ) AS 次品数 
							FROM
								TP_PM_SEMICHECK s
								LEFT JOIN TP_PM_SEMICHECKDEFECT sd ON s.SEMICHECKID = sd.SEMICHECKID 
							WHERE
								s.BACKOUTFLAG = 0 
								AND instr( s.GROUTINGLINENAME, 'C' ) = 1
								AND s.PROCEDUREID IN ( 117 )
								AND s.GOODSLEVELTYPEID IN(7,8)
								AND s.VALUEFLAG = 1 
								AND s.CREATETIME >= @DATEBEGIN@
								AND s.CREATETIME < @DATEEND@
					) T3 
				ON T1.外键 = T3.外键
                ";

                //获取查询条件
                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));

                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr, sqlPara.ToArray());
                string jsonStr = new JsonResult(dt).ToJson();

                context.Response.Write(jsonStr);         
         };
        //半检二检缺陷统计
        if (context.Request["m"] == "defectname") { 
                string dateBegin = DateTime.Now.ToString("yyyy-MM-dd 00:00:00");
                string dateEnd = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd 00:00:00");

                string sqlStr = @"
				SELECT
					t.*,
				ROWNUM 
				FROM
					(
					SELECT
						sd.DEFECTNAME,
						count( DISTINCT s.barcode ) as DTOTAL
					FROM
						TP_PM_SEMICHECK s
						LEFT JOIN TP_PM_SEMICHECKDEFECT sd ON s.SEMICHECKID = sd.SEMICHECKID 
					WHERE
						s.PROCEDUREID IN ( 117 )
						AND s.BACKOUTFLAG = 0 
						AND s.VALUEFLAG = 1 
						AND instr( s.GROUTINGLINENAME, 'C' ) = 1 
						AND s.CREATETIME >= @DATEBEGIN@
						AND s.CREATETIME < @DATEEND@ 
					GROUP BY
						sd.DEFECTNAME 
					ORDER BY
						DTOTAL DESC 
					) t 
				WHERE
					ROWNUM < 6
                ";

                //获取查询条件
                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));

                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr, sqlPara.ToArray());
                string jsonStr = new JsonResult(dt).ToJson();

                context.Response.Write(jsonStr);           
         };
        //半检二检位置累计
        if (context.Request["m"] == "defectposition") {
                string dateBegin = DateTime.Now.ToString("yyyy-MM-dd 00:00:00");
                string dateEnd = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd 00:00:00");

                string sqlStr = @"
				SELECT
					t.*,
				ROWNUM 
				FROM
					(
					SELECT
						sd.DEFECTPOSITIONNAME,
						count( DISTINCT s.barcode ) AS PTOTAL
					FROM
						TP_PM_SEMICHECK s
						LEFT JOIN TP_PM_SEMICHECKDEFECT sd ON s.SEMICHECKID = sd.SEMICHECKID 
					WHERE
						s.PROCEDUREID IN ( 117 )
						AND s.BACKOUTFLAG = 0 
						AND s.VALUEFLAG = 1 
						AND instr( s.GROUTINGLINENAME, 'C' ) = 1 
						AND s.CREATETIME >= @DATEBEGIN@ 
						AND s.CREATETIME < @DATEEND@ 
					GROUP BY
						sd.DEFECTPOSITIONNAME 
					ORDER BY
						PTOTAL DESC 
					) t 
				WHERE
					ROWNUM < 6
                ";

                //获取查询条件
                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));

                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr, sqlPara.ToArray());
                string jsonStr = new JsonResult(dt).ToJson();

                context.Response.Write(jsonStr);        
            };
        //半检二检产品累计
        if (context.Request["m"] == "goodscode") {
                string dateBegin = DateTime.Now.ToString("yyyy-MM-dd 00:00:00");
                string dateEnd = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd 00:00:00");

                string sqlStr = @"
				SELECT
					t.*,
				ROWNUM 
				FROM
					(
					SELECT
						s.GOODSCODE,
						count( DISTINCT s.barcode ) AS GTOTAL
					FROM
						TP_PM_SEMICHECK s
						LEFT JOIN TP_PM_SEMICHECKDEFECT sd ON s.SEMICHECKID = sd.SEMICHECKID 
					WHERE
						s.PROCEDUREID IN ( 117 ) 
						AND s.BACKOUTFLAG = 0 
						AND s.VALUEFLAG = 1 
						AND instr( s.GROUTINGLINENAME, 'C' ) = 1 
						AND s.CREATETIME >= @DATEBEGIN@
						AND s.CREATETIME < @DATEEND@
					GROUP BY
						s.goodscode 
					ORDER BY
						GTOTAL DESC 
					) t 
				WHERE
					ROWNUM < 6
                ";

                //获取查询条件
                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));

                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr, sqlPara.ToArray());
                string jsonStr = new JsonResult(dt).ToJson();

                context.Response.Write(jsonStr);		
			};
        //半检二检线号累计            
        if (context.Request["m"] == "groutingline") {
                string dateBegin = DateTime.Now.ToString("yyyy-MM-dd 00:00:00");
                string dateEnd = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd 00:00:00");

                string sqlStr = @"
				SELECT
					t.*,
				ROWNUM 
				FROM
					(
					SELECT
						s.GROUTINGLINENAME,
						count( DISTINCT s.barcode ) AS LTOTAL
					FROM
						TP_PM_SEMICHECK s
						LEFT JOIN TP_PM_SEMICHECKDEFECT sd ON s.SEMICHECKID = sd.SEMICHECKID 
					WHERE
						s.PROCEDUREID IN ( 117 ) 
						AND s.BACKOUTFLAG = 0 
						AND s.VALUEFLAG = 1 
						AND instr( s.GROUTINGLINENAME, 'C' ) = 1 
						AND s.CREATETIME >= @DATEBEGIN@ 
						AND s.CREATETIME < @DATEEND@ 
					GROUP BY
						s.GROUTINGLINENAME 
					ORDER BY
						LTOTAL DESC 
					) t 
				WHERE
					ROWNUM < 6
                ";

                //获取查询条件
                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));

                //直接获取不分页数据
                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;
        }
    }

}