HEGII.ss.mes.dashboard/wwwroot/main/Procedure/test13/demo.ashx

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

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

public class demo : IHttpHandler {
    
    public void ProcessRequest (HttpContext context){
        context.Response.ContentType = "text/plain";
        context.Response.ContentType = "text/plain";
        using (IDataAccess conn = DataAccess.Create())
        {
				//回收次品统计
				if (context.Request["m"].ToString() == "cp")
            {
                string sqlStr = @"
									SELECT
									T1.CREATETIME || '月' 日期,
								CASE WHEN T2.数量 IS NULL THEN 0 ELSE T2.数量 END 数量 
								FROM
									(
									SELECT
										0 数量,
										substr(TO_CHAR(add_months( to_date( to_char( trunc( SYSDATE, 'mm' ) + 1, 'yyyy-mm' ), 'yyyy-mm' ), - ( ROWNUM - 1 ) ),'yyyy-mm' ),6,2 ) AS CREATETIME 
									FROM DUAL CONNECT BY ROWNUM <= 6 ORDER BY
										substr(TO_CHAR(add_months( to_date( to_char( trunc( SYSDATE, 'mm' ) + 1, 'yyyy-mm' ), 'yyyy-mm' ), - ( ROWNUM - 1 ) ),'yyyy-mm' ),6,2 ) 
									) T1
									LEFT JOIN (
									SELECT
										substr( T.CREATETIME, 5, 2 ) AS CREATETIME,
										T.count AS 数量 
									FROM
										(
										SELECT
											to_char( TPPD.CREATETIME, 'yyyymm' ) CREATETIME,
											COUNT( * ) count 
										FROM
											TP_PM_PRODUCTIONDATA TPPD 
										WHERE
											TPPD.CREATETIME >= ADD_MONTHS( SYSDATE,- 6 ) 
											AND TPPD.GOODSLEVELTYPEID = 7 
											AND TPPD.VALUEFLAG = 1 
											AND TPPD.PROCEDUREID = 80 
										GROUP BY
											to_char( TPPD.CREATETIME, 'yyyymm' ) 
										ORDER BY
											COUNT( * ) DESC 
										) T 
									WHERE
										ROWNUM < 7 
									ORDER BY
									substr( T.CREATETIME, 5, 2 ) 
									) T2 ON T1.CREATETIME = T2.CREATETIME
									";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }

				//今日次品明细数据
				if (context.Request["m"].ToString() == "MonthTableDetail")
            {
                string sqlStr = @"SELECT
									TPGL.BARCODE 产品条码,
									TPGL.GOODSCODE 产品编码,
									to_char(TPGL.GROUTINGDATE,'yyyy-mm-dd') 注浆日期,
									TPGL.GROUTINGMOULDCODE 成型线模具,
									TPGL.GLAZINGROOM 施釉产线,
									TPPD.KILNCARCODE 窑炉编号,
									TPD.USERCODE 责任工号,
									TMDT.DEFECTTYPENAME 缺陷类别,
									TMD.S_NAME 缺陷名称,
									TPD.DEFECTPOSITIONNAME 缺陷位置
								FROM
									TP_PM_GROUTINGDAILYDETAIL TPGL 
									LEFT JOIN TP_PM_PRODUCTIONDATA TPPD ON TPGL.BARCODE = TPPD.BARCODE
									LEFT JOIN TP_PM_DEFECT TPD ON TPGL.BARCODE =  TPD.BARCODE
									LEFT JOIN TP_MST_DEFECT TMD ON TPD.DEFECTID = TMD.DEFECTID
									LEFT JOIN TP_MST_DEFECTTYPE TMDT ON TMD.DEFECTTYPEID = TMDT.DEFECTTYPEID
								WHERE
									TPPD.CREATETIME >= TRUNC( SYSDATE)
									AND TPPD.PROCEDUREID IN (125) 
									AND TPPD.KILNCODE = 'TK3' 
									AND TPPD.VALUEFLAG = 1
									AND TPPD.GOODSLEVELTYPEID = 7 
									AND TPD.USERCODE IS NOT NULL";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }

				//工号产量柱状图
				if (context.Request["m"].ToString() == "Ydefect")
            {
                string sqlStr = @"SELECT
									T.缺陷名称,
									T.数量
									FROM(
									SELECT
										TMD.S_NAME 缺陷名称,
										COUNT(DISTINCT TPD.BARCODE) 数量
									FROM
										TP_PM_PRODUCTIONDATA TPPD
										LEFT JOIN TP_PM_DEFECT TPD ON TPPD.BARCODE = TPD.BARCODE 
										LEFT JOIN TP_MST_DEFECT TMD ON TPD.defectid = TMD.defectid
									WHERE
										--TPPD.CHECKBATCHNO = 1 
										--AND TPPD.ISREFIRE = 0
										TPPD.CREATETIME >= TRUNC( SYSDATE-6 )
										--AND TPPD.GOODSLEVELID IN (6,7)
										AND TPD.DEFECTDEDUCTIONNUM >= 1
										and TPPD.PROCEDUREID = 123
										GROUP BY TMD.S_NAME
										ORDER BY COUNT(DISTINCT TPD.BARCODE) DESC)T WHERE ROWNUM < 4";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
                //七日次品缺陷扇形图
                if (context.Request["m"].ToString() == "defect")
            {
                string sqlStr = @"SELECT
										T.S_NAME AS 缺陷名称,
										T.count  AS 数量
									FROM
									(
									SELECT
										TMD.S_NAME,
										COUNT( TPPD.BARCODE ) count
									FROM
										TP_PM_DEFECT TPD
										LEFT JOIN TP_PM_PRODUCTIONDATA TPPD ON TPPD.BARCODE = TPD.BARCODE
										LEFT JOIN TP_MST_DEFECT TMD ON TPD.defectcode = TMD.defectcode 
									WHERE
										TPPD.CREATETIME >= TRUNC( SYSDATE) 
										AND TPPD.GOODSLEVELTYPEID = 7
										AND TPPD.VALUEFLAG = 1
										AND TPPD.KILNCODE = 'TK3' 
										AND TPPD.CHECKBATCHNO = 1
									GROUP BY
										TMD.S_NAME 
									ORDER BY
										COUNT( * ) DESC
										)T
										WHERE ROWNUM < 4";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
				//缺陷位置扇形图
                if (context.Request["m"].ToString() == "defectb")
            {
                string sqlStr = @"SELECT
										T.S_NAME AS 缺陷名称,
										T.count  AS 数量
									FROM
									(
									SELECT
										TMD.S_NAME,
										COUNT( TMD.S_NAME ) count
									FROM
										TP_PM_PRODUCTIONDATA TPPD
										LEFT JOIN TP_PM_DEFECT TPD ON TPPD.BARCODE = TPD.BARCODE
										LEFT JOIN TP_MST_DEFECTPOSITION TMD ON TPD.DEFECTPOSITIONID = TMD.DEFECTPOSITIONID 
									WHERE
										TPPD.CREATETIME >= TRUNC( SYSDATE) 
										AND TPPD.GOODSLEVELTYPEID = 7
										AND TPPD.VALUEFLAG = 1	
										AND TPPD.KILNCODE = 'TK3' 
										AND TPPD.CHECKBATCHNO = 1
									GROUP BY
										TMD.S_NAME 
									ORDER BY
										COUNT( TMD.S_NAME ) DESC
										)T
										WHERE ROWNUM < 4";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
                //七日次品折线图
                if (context.Request["m"].ToString() == "zx")
            {
                string sqlStr = @"SELECT TT1.count AS 次品数量,TO_CHAR((TT1.COUNT/TT.COUNT)* 100,'9,990.00') || '%' AS 次品率,SUBSTR(TT1.CREATETIME, 5, 2 )||'.'||SUBSTR(TT1.CREATETIME, 7, 2 ) AS 日期 FROM(
								SELECT T.CREATETIME,T.count FROM(
								SELECT
									to_char( TPPD.CREATETIME, 'yyyymmdd' ) AS CREATETIME,
									COUNT( * ) count 
								FROM
									TP_PM_PRODUCTIONDATA TPPD 
								WHERE
									TPPD.PROCEDUREID = 104 
									AND TPPD.ISREFIRE = 0 
									AND TPPD.KILNCODE = 'TK3' 
									AND TPPD.VALUEFLAG = 1 
									AND TPPD.CREATETIME >= TO_CHAR( SYSDATE -6) GROUP BY to_char( TPPD.CREATETIME, 'yyyymmdd' ) ORDER BY to_char( TPPD.CREATETIME, 'yyyymmdd' ) DESC  )T)TT
								FULL JOIN
								(
								SELECT T1.CREATETIME,T1.count FROM(
								SELECT
									to_char( TPPD.CREATETIME, 'yyyymmdd' ) AS CREATETIME,
									COUNT( DISTINCT TPPD.BARCODE ) count 
								FROM
									TP_PM_PRODUCTIONDATA TPPD
									LEFT JOIN TP_PM_DEFECT TPD ON TPPD.BARCODE = TPD.BARCODE
								WHERE
									TPPD.PROCEDUREID = 125 
									AND TPPD.GOODSLEVELTYPEID = 7
									AND TPPD.KILNCODE = 'TK3' 
									AND TPPD.VALUEFLAG = 1
									AND TPD.DEFECTDEDUCTIONNUM >= 1
									AND TPPD.CHECKBATCHNO = 1
									AND TPD.DEFECTNAME IS NOT NULL
									AND TPPD.CREATETIME >= TO_CHAR( SYSDATE -6 )
								GROUP BY to_char( TPPD.CREATETIME, 'yyyymmdd' ) ORDER BY to_char( TPPD.CREATETIME, 'yyyymmdd' ) ) T1
								)TT1 ON TT.CREATETIME = TT1.CREATETIME";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }

				//二检次品缺陷信息
				 if (context.Request["m"].ToString() == "MonthTabletwo")
            {
                string sqlStr = @"				SELECT
												H.缺陷名称,
												H.出窑数,
												H.缺陷数,
												H.缺陷占比,
												H.同月比,
												H.上月比,
												CASE WHEN H.上月比 > H.缺陷占比 THEN '下降↓'
													   ELSE '上升↑'
												END 对比趋势
												FROM(
												SELECT
												TT.DEFECTNAME 缺陷名称,
												TT2.COUNT 出窑数,
												TT.COUNT 缺陷数,
												TO_CHAR((TT.COUNT/TT2.COUNT)* 100,'9,990.00') || '%' AS 缺陷占比,
												TT3.COUNT 去年缺陷数量,
												TT4.COUNT 上个月缺陷数量,
												TT5.COUNT 去年出窑数,
												TT6.COUNT 上个月出窑数,
												TO_CHAR((TT3.COUNT/TT5.COUNT)* 100,'9,990.00') || '%' AS 同月比,
												TO_CHAR((TT4.COUNT/TT6.COUNT)* 100,'9,990.00') || '%' AS 上月比
												FROM(
												SELECT ROWNUM id,T.DEFECTID,T.DEFECTNAME,T.count FROM (
												SELECT
													TMD.S_NAME DEFECTNAME,
													TMD.DEFECTID,
													COUNT( * ) count 
												FROM
													TP_PM_PRODUCTIONDATA TPPD
													LEFT JOIN TP_PM_DEFECT TPD ON TPPD.BARCODE = TPD.BARCODE 
													LEFT JOIN TP_MST_DEFECT TMD ON TPD.DEFECTID = TMD.DEFECTID
												WHERE
													TPPD.PROCEDUREID = 125 
													AND TPPD.GOODSLEVELTYPEID = 7 
													AND TPD.VALUEFLAG = 1
													AND TPPD.VALUEFLAG = 1
													AND TPPD.KILNCODE = 'TK3'
													AND TMD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= TO_DATE( TO_CHAR( SYSDATE, 'YYYY-MM' ), 'YYYY-MM' ) 
												GROUP BY
													TMD.S_NAME,TMD.DEFECTID
												ORDER BY
													count DESC)T WHERE ROWNUM < 6)TT
													FULL JOIN(
												SELECT
												ROWNUM id,
												T2.count
												FROM(
												SELECT
													COUNT(*) count
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= TO_DATE( TO_CHAR( SYSDATE, 'YYYY-MM' ), 'YYYY-MM' )
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.CREATETIME >= TO_DATE( TO_CHAR( SYSDATE, 'YYYY-MM' ), 'YYYY-MM' )
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.CREATETIME >= TO_DATE( TO_CHAR( SYSDATE, 'YYYY-MM' ), 'YYYY-MM' )
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND TPPD.VALUEFLAG = 1
													AND KILNCODE = 'TK3' 
													AND TPPD.CREATETIME >= TO_DATE( TO_CHAR( SYSDATE, 'YYYY-MM' ), 'YYYY-MM' )
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= TO_DATE( TO_CHAR( SYSDATE, 'YYYY-MM' ), 'YYYY-MM' ))T2)TT2 ON TT.id = TT2.id
													FULL JOIN(
													SELECT T3.DEFECTID,T3.DEFECTNAME,T3.count FROM (
												SELECT
													TMD.S_NAME DEFECTNAME,
													TMD.DEFECTID,
													COUNT( * ) count
												FROM
													TP_PM_PRODUCTIONDATA TPPD
													LEFT JOIN TP_PM_DEFECT TPD ON TPPD.BARCODE = TPD.BARCODE 
													LEFT JOIN TP_MST_DEFECT TMD ON TPD.DEFECTID = TMD.DEFECTID
												WHERE
													TPPD.PROCEDUREID IN (125,105) 
													AND TPPD.GOODSLEVELTYPEID = 7
													AND TPD.VALUEFLAG = 1
													AND TPPD.VALUEFLAG = 1
													AND TPPD.KILNCODE = 'TK3'
													AND TMD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate, -12), 'month') 
													AND TPPD.CREATETIME <= trunc(last_day(add_months(sysdate, -12)))
												GROUP BY
													TMD.S_NAME,TMD.DEFECTID
												)T3 )TT3 ON TT.DEFECTID = TT3.DEFECTID
													FULL JOIN(
													SELECT ROWNUM id,T4.DEFECTNAME,T4.count FROM (
												SELECT
													TMD.S_NAME DEFECTNAME,
													COUNT( * ) count 
												FROM
													TP_PM_PRODUCTIONDATA TPPD
													LEFT JOIN TP_PM_DEFECT TPD ON TPPD.BARCODE = TPD.BARCODE 
													LEFT JOIN TP_MST_DEFECT TMD ON TPD.DEFECTID = TMD.DEFECTID
												WHERE
													TPPD.PROCEDUREID IN (125,105) 
													AND TPPD.GOODSLEVELTYPEID = 7 
													AND TPPD.KILNCODE = 'TK3'
													AND TPD.VALUEFLAG = 1
													AND TPPD.VALUEFLAG = 1
													AND TMD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate,-1),'mm')
													AND TPPD.CREATETIME <= last_day(add_months(sysdate,-1))
												GROUP BY
													TMD.S_NAME
												ORDER BY
													count DESC)T4 WHERE ROWNUM < 6)TT4 ON TT.id = TT4.id
														FULL JOIN(
												SELECT
												ROWNUM id,
												T5.count
												FROM(
												SELECT
													COUNT(*) count
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate, -12), 'month') 
													AND TPPD.CREATETIME <= trunc(last_day(add_months(sysdate, -12)))
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate, -12), 'month') 
													AND TPPD.CREATETIME <= trunc(last_day(add_months(sysdate, -12)))
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate, -12), 'month') 
													AND TPPD.CREATETIME <= trunc(last_day(add_months(sysdate, -12)))
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate, -12), 'month') 
													AND TPPD.CREATETIME <= trunc(last_day(add_months(sysdate, -12)))
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate, -12), 'month') 
													AND TPPD.CREATETIME <= trunc(last_day(add_months(sysdate, -12)))
													)T5)TT5 ON TT.id = TT5.id
													FULL JOIN(
												SELECT
												ROWNUM id,
												T6.count
												FROM(
												SELECT
													COUNT(*) count
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate,-1),'mm')
													AND TPPD.CREATETIME <= last_day(add_months(sysdate,-1))
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate,-1),'mm')
													AND TPPD.CREATETIME <= last_day(add_months(sysdate,-1))
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate,-1),'mm')
													AND TPPD.CREATETIME <= last_day(add_months(sysdate,-1))
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate,-1),'mm')
													AND TPPD.CREATETIME <= last_day(add_months(sysdate,-1))
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate,-1),'mm')
													AND TPPD.CREATETIME <= last_day(add_months(sysdate,-1))
													)T6)TT6 ON TT.id = TT6.id )H WHERE H.缺陷数 IS NOT NULL
											 ";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }

		//二检次品缺陷位置信息
				 if (context.Request["m"].ToString() == "MonthTableD")
            {
                string sqlStr = @"				SELECT
												H.缺陷位置名称,
												H.出窑数,
												H.缺陷数,
												H.缺陷占比,
												H.同月比,
												H.上月比,
												CASE WHEN H.上月比 > H.缺陷占比 THEN '下降↓'
													   ELSE '上升↑'
												END 对比趋势
												FROM(
												SELECT
												TT.DEFECTNAME 缺陷位置名称,
												TT2.COUNT 出窑数,
												TT.COUNT 缺陷数,
												TO_CHAR((TT.COUNT/TT2.COUNT)* 100,'9,990.00') || '%' AS 缺陷占比,
												TT3.COUNT 去年缺陷数量,
												TT4.COUNT 上个月缺陷数量,
												TT5.COUNT 去年出窑数,
												TT6.COUNT 上个月出窑数,
												TO_CHAR((TT3.COUNT/TT5.COUNT)* 100,'9,990.00') || '%' AS 同月比,
												TO_CHAR((TT4.COUNT/TT6.COUNT)* 100,'9,990.00') || '%' AS 上月比
												FROM(
												SELECT ROWNUM id,T.DEFECTID,T.DEFECTNAME,T.count FROM (
												SELECT
													TPD.DEFECTPOSITIONNAME DEFECTNAME,
													TPD.DEFECTPOSITIONID DEFECTID,
													COUNT( * ) count 
												FROM
													TP_PM_PRODUCTIONDATA TPPD
													LEFT JOIN TP_PM_DEFECT TPD ON TPPD.BARCODE = TPD.BARCODE 
													LEFT JOIN TP_MST_DEFECT TMD ON TPD.DEFECTID = TMD.DEFECTID
												WHERE
													TPPD.PROCEDUREID = 125 
													AND TPPD.GOODSLEVELTYPEID = 7 
													AND TPPD.KILNCODE = 'TK3'
													AND TPD.VALUEFLAG = 1
													AND TPPD.VALUEFLAG = 1
													AND TMD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= TO_DATE( TO_CHAR( SYSDATE, 'YYYY-MM' ), 'YYYY-MM' ) 
												GROUP BY
													TPD.DEFECTPOSITIONNAME,TPD.DEFECTPOSITIONID
												ORDER BY
													count DESC)T WHERE ROWNUM < 6)TT
													FULL JOIN(
												SELECT
												ROWNUM id,
												T2.count
												FROM(
												SELECT
													COUNT(*) count
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= TO_DATE( TO_CHAR( SYSDATE, 'YYYY-MM' ), 'YYYY-MM' )
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.CREATETIME >= TO_DATE( TO_CHAR( SYSDATE, 'YYYY-MM' ), 'YYYY-MM' )
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.CREATETIME >= TO_DATE( TO_CHAR( SYSDATE, 'YYYY-MM' ), 'YYYY-MM' )
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND TPPD.VALUEFLAG = 1
													AND KILNCODE = 'TK3' 
													AND TPPD.CREATETIME >= TO_DATE( TO_CHAR( SYSDATE, 'YYYY-MM' ), 'YYYY-MM' )
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= TO_DATE( TO_CHAR( SYSDATE, 'YYYY-MM' ), 'YYYY-MM' ))T2)TT2 ON TT.id = TT2.id
													FULL JOIN(
													SELECT T3.DEFECTID,T3.DEFECTNAME,T3.count FROM (
												SELECT
													TPD.DEFECTPOSITIONNAME DEFECTNAME,
													TPD.DEFECTPOSITIONID DEFECTID,
													COUNT( * ) count
												FROM
													TP_PM_PRODUCTIONDATA TPPD
													LEFT JOIN TP_PM_DEFECT TPD ON TPPD.BARCODE = TPD.BARCODE 
													LEFT JOIN TP_MST_DEFECT TMD ON TPD.DEFECTID = TMD.DEFECTID
												WHERE
													TPPD.PROCEDUREID IN (125,105) 
													AND TPPD.GOODSLEVELTYPEID = 7
													AND TPPD.KILNCODE = 'TK3'
													AND TPD.VALUEFLAG = 1
													AND TPPD.VALUEFLAG = 1
													AND TMD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate, -12), 'month') 
													AND TPPD.CREATETIME <= trunc(last_day(add_months(sysdate, -12)))
												GROUP BY
													TPD.DEFECTPOSITIONNAME,TPD.DEFECTPOSITIONID
												)T3 )TT3 ON TT.DEFECTID = TT3.DEFECTID
													FULL JOIN(
													SELECT ROWNUM id,T4.DEFECTNAME,T4.count FROM (
												SELECT
													TPD.DEFECTPOSITIONNAME DEFECTNAME,
													COUNT( * ) count 
												FROM
													TP_PM_PRODUCTIONDATA TPPD
													LEFT JOIN TP_PM_DEFECT TPD ON TPPD.BARCODE = TPD.BARCODE 
													LEFT JOIN TP_MST_DEFECT TMD ON TPD.DEFECTID = TMD.DEFECTID
												WHERE
													TPPD.PROCEDUREID IN (125,105) 
													AND TPPD.GOODSLEVELTYPEID = 7 
													AND TPPD.KILNCODE = 'TK3'
													AND TPD.VALUEFLAG = 1
													AND TPPD.VALUEFLAG = 1
													AND TMD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate,-1),'mm')
													AND TPPD.CREATETIME <= last_day(add_months(sysdate,-1))
												GROUP BY
													TPD.DEFECTPOSITIONNAME
												ORDER BY
													count DESC)T4 WHERE ROWNUM < 6)TT4 ON TT.id = TT4.id
														FULL JOIN(
												SELECT
												ROWNUM id,
												T5.count
												FROM(
												SELECT
													COUNT(*) count
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate, -12), 'month') 
													AND TPPD.CREATETIME <= trunc(last_day(add_months(sysdate, -12)))
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate, -12), 'month') 
													AND TPPD.CREATETIME <= trunc(last_day(add_months(sysdate, -12)))
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate, -12), 'month') 
													AND TPPD.CREATETIME <= trunc(last_day(add_months(sysdate, -12)))
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate, -12), 'month') 
													AND TPPD.CREATETIME <= trunc(last_day(add_months(sysdate, -12)))
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate, -12), 'month') 
													AND TPPD.CREATETIME <= trunc(last_day(add_months(sysdate, -12)))
													)T5)TT5 ON TT.id = TT5.id
													FULL JOIN(
												SELECT
												ROWNUM id,
												T6.count
												FROM(
												SELECT
													COUNT(*) count
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate,-1),'mm')
													AND TPPD.CREATETIME <= last_day(add_months(sysdate,-1))
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate,-1),'mm')
													AND TPPD.CREATETIME <= last_day(add_months(sysdate,-1))
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate,-1),'mm')
													AND TPPD.CREATETIME <= last_day(add_months(sysdate,-1))
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate,-1),'mm')
													AND TPPD.CREATETIME <= last_day(add_months(sysdate,-1))
													UNION ALL
													SELECT
													COUNT(*)
												FROM
													TP_PM_PRODUCTIONDATA TPPD 
												WHERE
													TPPD.PROCEDUREID = 104 
													AND TPPD.ISREFIRE = 0 
													AND KILNCODE = 'TK3' 
													AND TPPD.VALUEFLAG = 1
													AND TPPD.CREATETIME >= trunc(add_months(sysdate,-1),'mm')
													AND TPPD.CREATETIME <= last_day(add_months(sysdate,-1))
													)T6)TT6 ON TT.id = TT6.id )H WHERE H.缺陷数 IS NOT NULL
											 ";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
        }
    }
 
    public bool IsReusable {
        get {
            return false;
        }
    }

}