HEGII.ss.mes.dashboard/wwwroot/main/Procedure/test13/demo2.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() == "MonthTableDetail")
            {
                string sqlStr = @"SELECT
									H.产品名称,
									H.出窑数,
									H.次品数,
									H.次品占比,
								CASE
		
										WHEN H.同月比 = '%' THEN
										'--' ELSE H.同月比 
									END 同月比,
									H.上月比,
								CASE
	
									WHEN H.上月比 > H.次品占比 THEN
									'下降↓' 
									WHEN H.上月比 = '%' THEN
									'趋同' ELSE '上升↑' 
									END 对比趋势 
								FROM
									(
									SELECT
										TT.GOODSCODE 产品名称,
										(
										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' ) 
											--按照产品型号分组(SKU)
											AND TPPD.GOODSCODE=TT.GOODSCODE
										) 出窑数,
										TT.COUNT 次品数,
										TO_CHAR(
											(
												TT.COUNT / (
												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' ) 
												) 
											) * 100,
											'9,990.00' 
										) || '%' AS 次品占比,
										TT3.COUNT 去年缺陷数量,
										TT4.COUNT 上个月缺陷数量,
										(
										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 ) ) ) 
										) 去年出窑数,
										(
										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 ) ) 
										) 上个月出窑数,
										TO_CHAR(
											(
												TT3.COUNT / (
												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 ) ) ) 
												) 
											) * 100,
											'9,990.00' 
										) || '%' AS 同月比,
										TO_CHAR(
											(
												TT4.COUNT / (
												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 ) ) 
												) 
											) * 100,
											'9,990.00' 
										) || '%' AS 上月比 
									FROM
										(
										SELECT ROWNUM
											id,
											T.GOODSCODE,
											T.count 
										FROM
											(
											SELECT
												TPPD.GOODSCODE,
												COUNT( DISTINCT BARCODE ) count 
											FROM
												TP_PM_PRODUCTIONDATA TPPD 
											WHERE
												TPPD.PROCEDUREID = 125 
												AND TPPD.GOODSLEVELTYPEID = 7 
												AND TPPD.VALUEFLAG = 1 
												AND TPPD.KILNCODE = 'TK3' 
												AND TPPD.CREATETIME >= TO_DATE( TO_CHAR( SYSDATE, 'YYYY-MM' ), 'YYYY-MM' ) 
											GROUP BY
												TPPD.GOODSCODE 
											ORDER BY
												count DESC 
											) T 
										WHERE
											ROWNUM < 11 
										) TT
										FULL JOIN (
										SELECT
											T3.GOODSCODE,
											T3.count 
										FROM
											(
											SELECT
												TPPD.GOODSCODE GOODSCODE,
												COUNT( * ) count 
											FROM
												TP_PM_PRODUCTIONDATA TPPD 
											WHERE
												TPPD.PROCEDUREID IN ( 125, 105 ) 
												AND TPPD.GOODSLEVELTYPEID = 7 
												AND TPPD.VALUEFLAG = 1 
												AND TPPD.KILNCODE = 'TK3' 
												AND TPPD.CREATETIME >= trunc( add_months( SYSDATE, - 12 ), 'month' ) 
												AND TPPD.CREATETIME <= trunc( last_day( add_months( SYSDATE, - 12 ) ) ) 
											GROUP BY
												TPPD.GOODSCODE 
											) T3 
										) TT3 ON TT.GOODSCODE = TT3.GOODSCODE
										FULL JOIN (
										SELECT ROWNUM
											id,
											T4.GOODSCODE,
											T4.count 
										FROM
											(
											SELECT
												TPPD.GOODSCODE GOODSCODE,
												COUNT( * ) count 
											FROM
												TP_PM_PRODUCTIONDATA TPPD 
											WHERE
												TPPD.PROCEDUREID IN ( 125, 105 ) 
												AND TPPD.GOODSLEVELTYPEID = 7 
												AND TPPD.KILNCODE = 'TK3' 
												AND TPPD.VALUEFLAG = 1 
												AND TPPD.CREATETIME >= trunc( add_months( SYSDATE,- 1 ), 'mm' ) 
												AND TPPD.CREATETIME <= last_day( add_months( SYSDATE,- 1 ) ) 
											GROUP BY
												TPPD.GOODSCODE 
											ORDER BY
												count DESC 
											) T4 
										WHERE
											ROWNUM < 11 
										) TT4 ON TT.id = TT4.id 
									) H 
								WHERE
									H.次品数 IS NOT NULL ORDER BY H.次品数 DESC";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }

                //近30日次品折线图
                if (context.Request["m"].ToString() == "zx")
            {
                string sqlStr = @"SELECT
*
FROM(
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 - 29) 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 TPPD.CHECKBATCHNO = 1
									AND TPD.DEFECTNAME IS NOT NULL
									AND TPPD.CREATETIME >= TO_CHAR( SYSDATE - 29 )
								GROUP BY to_char( TPPD.CREATETIME, 'yyyymmdd' ) ORDER BY to_char( TPPD.CREATETIME, 'yyyymmdd' ) ) T1
								)TT1 ON TT.CREATETIME = TT1.CREATETIME
								) ORDER BY  日期";
                //直接获取不分页数据
                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;
        }
    }

}