HEGII.ss.mes.dashboard/wwwroot/main/DashBoard/rpt401/groutingCount.ashx

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

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

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

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

        //成型累计
        if (context.Request["m"] == "total") { 
                string dateBegin = DateTime.Now.ToString("yyyy-MM-01 00:00:00");
                string dateEnd = DateTime.Now.AddMonths(1).ToString("yyyy-MM-01 00:00:00");

                string sqlStr = @"
				SELECT
					T1.不良数 AS NUM2 ,
					T2.总数 - T1.不良数 - (T3.报废数 + T4.开模损 + T5.干燥损) AS NUM1 ,
					T3.报废数 + T4.开模损 + T5.干燥损 AS NUM3 ,
					ROUND(T1.不良数 /	DECODE(NVL(T2.总数,0),0,1,T2.总数),2) * 100 AS UNQUALIFIED,
					ROUND((T3.报废数 + T4.开模损 + T5.干燥损) /	DECODE(NVL(T2.总数,0),0,1,T2.总数),2) * 100 AS SCRAP,
					ROUND((T2.总数 - T1.不良数 - (T3.报废数 + T4.开模损 + T5.干燥损)) / DECODE(NVL(T2.总数,0),0,1,T2.总数),2) * 100  AS QUALIFIED 
				FROM
					(
					SELECT
						'1' AS 外键,
						count( distinct barcode ) AS 不良数 
					FROM
						(
						SELECT
							distinct barcode
						FROM
							TP_PM_SEMICHECK s
							LEFT JOIN TP_PM_SEMICHECKDEFECT sd ON s.SEMICHECKID = sd.SEMICHECKID 
						WHERE
							s.BACKOUTFLAG = 0	 
							AND s.VALUEFLAG = 1 
							AND sd.DEFECTPROCEDUREID IN ( 88, 92, 118 ) 
							AND instr( s.GROUTINGLINENAME, 'C' ) = 1
							AND instr( s.GROUTINGLINENAME, 'A' ) = 4
							AND S.GOODSLEVELTYPEID NOT IN(13,14)
  							AND s.CREATETIME >= @DATEBEGIN@
							AND s.CREATETIME < @DATEEND@
						UNION
						SELECT
							distinct pd.barcode 
						FROM
							TP_PM_PRODUCTIONDATA pd
							LEFT JOIN TP_PM_DEFECT d ON pd.PRODUCTIONDATAID = d.PRODUCTIONDATAID 
							WHERE
							pd.CREATETIME >= @DATEBEGIN@
							AND pd.CREATETIME < @DATEEND@
							AND d.VALUEFLAG = 1 
							AND d.DEFECTPROCEDUREID IN ( 88, 92, 118 ) 
							AND PD.GOODSLEVELTYPEID NOT IN(7,8)
							AND instr( pd.GROUTINGLINENAME, 'C' ) = 1 
							AND instr( pd.GROUTINGLINENAME, 'A' ) = 4 
							AND pd.PROCEDUREID = 105 
						) 
					) T1
					INNER JOIN ( 
					SELECT
						'1' AS 外键,
						COUNT( DISTINCT GD.BARCODE ) AS 总数 
					FROM
						TP_PM_GROUTINGDAILYDETAIL GD
					WHERE
						GD.GROUTINGFLAG = '1' --注浆
						AND GD.VALUEFLAG = '1'
						AND INSTR( GD.GROUTINGLINECODE, 'C' ) = 1
						AND INSTR( GD.GROUTINGLINECODE, 'A' ) = 4
						AND GD.CREATETIME > @DATEBEGIN@ 
						AND GD.CREATETIME < @DATEEND@ 
					) T2 ON T1.外键 = T2.外键
				    INNER JOIN 
					(
					SELECT
						'1' AS 外键,
						count( distinct barcode ) AS 报废数 
					FROM
						(
						SELECT
							distinct barcode
						FROM
							TP_PM_SEMICHECK s
							LEFT JOIN TP_PM_SEMICHECKDEFECT sd ON s.SEMICHECKID = sd.SEMICHECKID 
						WHERE
							s.BACKOUTFLAG = 0	 
							AND s.VALUEFLAG = 1 
							AND sd.DEFECTPROCEDUREID IN ( 88, 92, 118 ) 
							AND instr( s.GROUTINGLINENAME, 'C' ) = 1
							AND instr( s.GROUTINGLINENAME, 'A' ) = 4
							AND S.GOODSLEVELTYPEID IN(13,14)
  							AND s.CREATETIME >= @DATEBEGIN@
							AND s.CREATETIME < @DATEEND@
						UNION
						SELECT
							distinct pd.barcode 
						FROM
							TP_PM_PRODUCTIONDATA pd
							LEFT JOIN TP_PM_DEFECT d ON pd.PRODUCTIONDATAID = d.PRODUCTIONDATAID 
							WHERE
							pd.CREATETIME >= @DATEBEGIN@
							AND pd.CREATETIME < @DATEEND@
							AND d.VALUEFLAG = 1 
							AND d.DEFECTPROCEDUREID IN ( 88, 92, 118 ) 
							AND PD.GOODSLEVELTYPEID IN(7,8)
							AND instr( pd.GROUTINGLINENAME, 'C' ) = 1 
							AND instr( pd.GROUTINGLINENAME, 'A' ) = 4 
							AND pd.PROCEDUREID = 105 
						) 
					) T3 ON T1.外键 = T3.外键
				    INNER JOIN 
					(
					SELECT
					'1' AS 外键,
					COUNT(DISTINCT BARCODE) AS 开模损
					from
					TP_PM_GROUTINGSCRAPPRODUCT
					WHERE
					AUDITSTATUS = 1
					and
					BACKOUTFLAG = 0
					and 
					SCRAPTYPE in (0,1)
					and 
					VALUEFLAG = 1
					and
					CREATETIME >= @DATEBEGIN@
					and
					CREATETIME < @DATEEND@ 
					) T4 ON T1.外键 = T4.外键
				    INNER JOIN 
					(
					SELECT
					'1' AS 外键,
					COUNT(DISTINCT S.BARCODE) AS 干燥损
					from
					TP_PM_SCRAPPRODUCT S
					WHERE
					 S.AUDITSTATUS = 1
					 and 
					-- S.SCRAPTYPE = 0
					-- AND
					S.VALUEFLAG = 1
					and
					S.CREATETIME >= @DATEBEGIN@
					and
					S.CREATETIME < @DATEEND@
					and
					s.PROCEDUREID in (82, 83, 86, 87)
					) T5 ON T1.外键 = T5.外键
                ";

                //获取查询条件
                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-01 00:00:00");
                string dateEnd = DateTime.Now.AddMonths(1).ToString("yyyy-MM-01 00:00:00");

                string sqlStr = @"
                SELECT
	                t.*,
                ROWNUM 
                FROM
	                (
	                SELECT
		                DEFECTNAME,
		                sum( total ) as Dtotal 
	                FROM
		                (
		                SELECT
			                sd.DEFECTNAME,
			                count( DISTINCT s.barcode ) AS total 
		                FROM
			                TP_PM_SEMICHECK s
			                LEFT JOIN TP_PM_SEMICHECKDEFECT sd ON s.SEMICHECKID = sd.SEMICHECKID 
		                WHERE

								s.BACKOUTFLAG = 0 
			                AND s.VALUEFLAG = 1 
			                AND sd.DEFECTPROCEDUREID IN ( 88, 92, 118 ) 
			                AND instr( s.GROUTINGLINENAME, 'C' ) = 1 
			                AND s.CREATETIME >= @DATEBEGIN@ 
			                AND s.CREATETIME < @DATEEND@
		                GROUP BY
			                sd.DEFECTNAME UNION
		                SELECT
			                d.DEFECTNAME,
			                count( DISTINCT d.barcode ) AS total 
		                FROM
			                TP_PM_PRODUCTIONDATA pd
			                LEFT JOIN TP_PM_DEFECT d ON pd.PRODUCTIONDATAID = d.PRODUCTIONDATAID 
		                WHERE
			                pd.CREATETIME >= @DATEBEGIN@ 
			                AND pd.CREATETIME < @DATEEND@ 
			                AND d.DEFECTPROCEDUREID IN ( 88, 92, 118 ) 
			                AND instr( pd.GROUTINGLINENAME, 'C' ) = 1 
			                AND pd.PROCEDUREID = 105 
							AND d.VALUEFLAG = 1 
		                GROUP BY
			                d.DEFECTNAME 
		                ) 
	                GROUP BY
		                DEFECTNAME 
	                ORDER BY
		                sum( total ) 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-01 00:00:00");
                string dateEnd = DateTime.Now.AddMonths(1).ToString("yyyy-MM-01 00:00:00");

                string sqlStr = @"
				SELECT
					t.*,
				ROWNUM 
				FROM
					(
					SELECT
						DEFECTPOSITIONNAME,
						sum( total ) as PTOTAL 
					FROM
						(
						SELECT
							sd.DEFECTPOSITIONNAME,
							count( DISTINCT s.barcode ) AS total 
						FROM
							TP_PM_SEMICHECK s
							LEFT JOIN TP_PM_SEMICHECKDEFECT sd ON s.SEMICHECKID = sd.SEMICHECKID 
						WHERE
							    s.BACKOUTFLAG = 0 
							AND s.VALUEFLAG = 1 
							AND sd.DEFECTPROCEDUREID IN ( 88, 92, 118 ) 
							AND instr( s.GROUTINGLINENAME, 'C' ) = 1 
							AND s.CREATETIME >= @DATEBEGIN@ 
							AND s.CREATETIME < @DATEEND@ 
						GROUP BY
							sd.DEFECTPOSITIONNAME UNION
						SELECT
							d.DEFECTPOSITIONNAME,
							count( DISTINCT d.barcode ) AS total 
						FROM
							TP_PM_PRODUCTIONDATA pd
							LEFT JOIN TP_PM_DEFECT d ON pd.PRODUCTIONDATAID = d.PRODUCTIONDATAID 
						WHERE
							pd.CREATETIME >= @DATEBEGIN@ 
							AND pd.CREATETIME < @DATEEND@ 
							AND d.DEFECTPROCEDUREID IN ( 88, 92, 118 ) 
							AND instr( pd.GROUTINGLINENAME, 'C' ) = 1 
							AND pd.PROCEDUREID = 105 
							AND d.VALUEFLAG = 1 
						GROUP BY
							d.DEFECTPOSITIONNAME 
						) 
					GROUP BY
						DEFECTPOSITIONNAME 
					ORDER BY
						sum( total ) 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-01 00:00:00");
                string dateEnd = DateTime.Now.AddMonths(1).ToString("yyyy-MM-01 00:00:00");

                string sqlStr = @"
				SELECT
					t.*,
				ROWNUM 
				FROM
					(
					SELECT
						GOODSCODE,
						sum( total ) as GTOTAL
					FROM
						(
						SELECT
							s.GOODSCODE,
							count( DISTINCT s.barcode ) AS total 
						FROM
							TP_PM_SEMICHECK s
							LEFT JOIN TP_PM_SEMICHECKDEFECT sd ON s.SEMICHECKID = sd.SEMICHECKID 
						WHERE
							s.BACKOUTFLAG = 0 
							AND s.VALUEFLAG = 1 
							AND sd.DEFECTPROCEDUREID IN ( 88, 92, 118 ) 
							AND instr( s.GROUTINGLINENAME, 'C' ) = 1 
							AND s.CREATETIME >= @DATEBEGIN@ 
							AND s.CREATETIME < @DATEEND@ 
						GROUP BY
							s.GOODSCODE UNION
						SELECT
							pd.GOODSCODE,
							count( DISTINCT d.barcode ) AS total 
						FROM
							TP_PM_PRODUCTIONDATA pd
							LEFT JOIN TP_PM_DEFECT d ON pd.PRODUCTIONDATAID = d.PRODUCTIONDATAID 
						WHERE
							pd.CREATETIME >= @DATEBEGIN@ 
							AND pd.CREATETIME < @DATEEND@ 
							AND d.DEFECTPROCEDUREID IN ( 88, 92, 118 ) 
							AND instr( pd.GROUTINGLINENAME, 'C' ) = 1 
							AND pd.PROCEDUREID = 105 
							AND d.VALUEFLAG = 1 
						GROUP BY
							pd.GOODSCODE 
						) 
					GROUP BY
						GOODSCODE 
					ORDER BY
						sum( total ) 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-01 00:00:00");
                string dateEnd = DateTime.Now.AddMonths(1).ToString("yyyy-MM-01 00:00:00");

                string sqlStr = @"
				SELECT
					t.*,
				ROWNUM 
				FROM
					(
					SELECT
						GROUTINGLINENAME,
						sum( total ) as LTOTAL
					FROM
						(
						SELECT
							s.GROUTINGLINENAME,
							count( DISTINCT s.barcode ) AS total 
						FROM
							TP_PM_SEMICHECK s
							LEFT JOIN TP_PM_SEMICHECKDEFECT sd ON s.SEMICHECKID = sd.SEMICHECKID 
						WHERE
							s.BACKOUTFLAG = 0 
							AND s.VALUEFLAG = 1 
							AND sd.DEFECTPROCEDUREID IN ( 88, 92, 118 ) 
							AND instr( s.GROUTINGLINENAME, 'C' ) = 1 
							AND s.CREATETIME >= @DATEBEGIN@ 
							AND s.CREATETIME < @DATEEND@ 
						GROUP BY
							s.GROUTINGLINENAME UNION
						SELECT
							pd.GROUTINGLINENAME,
							count( DISTINCT d.barcode ) AS total 
						FROM
							TP_PM_PRODUCTIONDATA pd
							LEFT JOIN TP_PM_DEFECT d ON pd.PRODUCTIONDATAID = d.PRODUCTIONDATAID 
						WHERE
							pd.CREATETIME >= @DATEBEGIN@ 
							AND pd.CREATETIME < @DATEEND@ 
							AND d.DEFECTPROCEDUREID IN ( 88, 92, 118 ) 
							AND instr( pd.GROUTINGLINENAME, 'C' ) = 1 
							AND pd.PROCEDUREID = 105 
							AND d.VALUEFLAG = 1 
						GROUP BY
							pd.GROUTINGLINENAME 
						) 
					GROUP BY
						GROUTINGLINENAME 
					ORDER BY
						sum( total ) 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;
        }
    }

}