HEGII.ss.mes.dashboard/wwwroot/main/Procedure/test5/rpt.ashx

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

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 rpt : IHttpHandler, IReadOnlySessionState
{
    public void ProcessRequest(HttpContext context)
    {
        context.Response.ContentType = "text/plain";
        using(IDataAccess conn = DataAccess.Create())
        {
            if (context.Request["m"].ToString() == "defect")
            {
                string sqlStr = @"
                WITH MES AS (
	                SELECT
		                DEFECTCODE,
		                DEFECTNAME,
										NUM,
		                RN 
	                FROM
		                (
		                SELECT
			                DEFECTCODE,
			                DEFECTNAME,
											NUM,
			                ROW_NUMBER ( ) OVER ( ORDER BY NUM DESC, DEFECTCODE ) AS RN 
		                FROM
			                (
			                SELECT
				                D.DEFECTCODE,
				                DEFECT.S_NAME AS DEFECTNAME,
				                COUNT( DISTINCT D.BARCODE ) AS NUM 
			                FROM
				                TP_PM_DEFECT D
								LEFT JOIN TP_MST_GOODS G ON D.GOODSID = G.GOODSID
								LEFT JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID
				                INNER JOIN TP_PM_PRODUCTIONDATA PD ON D.PRODUCTIONDATAID = PD.PRODUCTIONDATAID 
								INNER JOIN TP_MST_DEFECTFINERELATION DFR ON D.DEFECTID = DFR.DEFECTID  
								INNER JOIN TP_MST_DEFECTFINE DF ON DF.DEFECTFINEID = DFR.DEFECTFINEID  
								INNER JOIN TP_MST_DEFECTDEDUCTIONRELATION DDR ON D.DEFECTID = DDR.DEFECTID  
								INNER JOIN TP_MST_DEFECTDEDUCTION DD ON DD.DEFECTDEDUCTIONID = DDR.DEFECTDEDUCTIONID  
								INNER JOIN TP_MST_DEFECT DEFECT ON D.DEFECTID = DEFECT.DEFECTID
			                WHERE
				                D.VALUEFLAG = 1 
				                AND PD.VALUEFLAG = 1 
				                AND PD.GOODSLEVELTYPEID IN(6, 7) 
				                AND PD.KILNCODE IN('SK3', 'TK3') 
				                AND D.CREATETIME >= trunc(sysdate) + 7/24
				                AND D.CREATETIME < trunc(sysdate) + 7/24 + 1 
								-- AND (INSTR( PD.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( PD.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( PD.GROUTINGLINECODE, 'C06B' ) = 1)
								AND INSTR (PD.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( PD.GROUTINGLINECODE, 'A' ) = 4 
			                GROUP BY
				                D.DEFECTCODE,
				                DEFECT.S_NAME
			                ) 
		                ) 
	                WHERE
		                RN <= 6 
	                )


                SELECT
	                DEFECTCODE 缺陷编码,
	                'TOP'||RN 缺陷排行,
	                DEFECTNAME 缺陷名称,
	                -- DEFECTPOSITIONCODE,
	                DEFECTPOSITIONNAME 缺陷位置,
	                'TOP'||RN2 位置排行,
	                NUM 数量,
									n,
	                ROUND( 100 * RATIO_TO_REPORT ( NUM ) OVER ( PARTITION BY  RN ), 1 )||'%' AS 占比 
                FROM
	                (
	                SELECT
		                DEFECTCODE,
		                DEFECTNAME,
										n,
		                RN,
		                DEFECTPOSITIONCODE,
		                DEFECTPOSITIONNAME,
		                NUM,
		                RN2 
	                FROM
		                (
		                SELECT
			                DEFECTCODE,
			                DEFECTNAME,
											n,
			                RN,
			                DEFECTPOSITIONCODE,
			                DEFECTPOSITIONNAME,
			                NUM,
			                ROW_NUMBER ( ) OVER ( PARTITION BY  DEFECTCODE ORDER BY  RN, NUM DESC ) AS RN2 
		                FROM
			                (
			                SELECT
				                M.DEFECTCODE,
				                M.DEFECTNAME,
												m.NUM as n,
				                M.RN,
				                D.DEFECTPOSITIONCODE,
				                DEFECTPOSITION.S_NAME AS DEFECTPOSITIONNAME,
				                COUNT( DISTINCT D.BARCODE ) AS NUM 
			                FROM
				                MES M
				                LEFT JOIN TP_PM_DEFECT D ON M.DEFECTCODE = D.DEFECTCODE
								LEFT JOIN TP_MST_GOODS G ON D.GOODSID = G.GOODSID
								LEFT JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID
				                LEFT JOIN TP_PM_PRODUCTIONDATA PD ON D.PRODUCTIONDATAID = PD.PRODUCTIONDATAID 
								INNER JOIN TP_MST_DEFECTPOSITION DEFECTPOSITION  ON DEFECTPOSITION .DEFECTPOSITIONID = D.DEFECTPOSITIONID
			                WHERE
				                D.VALUEFLAG = 1 
				                AND PD.VALUEFLAG = 1 
				                AND D.CREATETIME >= trunc(sysdate) + 7/24
				                AND D.CREATETIME < trunc(sysdate) + 7/24 + 1 
								-- AND (INSTR( PD.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( PD.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( PD.GROUTINGLINECODE, 'C06B' ) = 1)
								AND INSTR (PD.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( PD.GROUTINGLINECODE, 'A' ) = 4 
				                AND PD.GOODSLEVELTYPEID IN(6, 7) 
				                AND PD.KILNCODE IN('SK3', 'TK3') 
			                GROUP BY
				                M.DEFECTCODE,
				                M.DEFECTNAME,
												m.num,
				                M.RN,
				                D.DEFECTPOSITIONCODE,
				                DEFECTPOSITION.S_NAME 
			                ) 
		                ) 
	                WHERE
		                RN2 <= 3 
	                )
	                ORDER BY
		                LPAD(RN,3,'0'),
		                LPAD(RN2,3,'0')
                ";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
            else if (context.Request["m"].ToString() == "nm")
            {
                string sqlStr = @"
-- 				SELECT
-- 					SUM( CASE WHEN pd.CREATETIME >= TRUNC( SYSDATE ) 
-- 					AND pd.CREATETIME < TRUNC( SYSDATE ) + 1 
-- 					AND GL.HIGHPRESSUREFLAG = 1 THEN 1 ELSE 0 END ) AS NUM1,
-- 					SUM( CASE WHEN pd.CREATETIME >= TRUNC( SYSDATE ) 
-- 					AND pd.CREATETIME < TRUNC( SYSDATE ) + 1 
-- 					THEN 1 ELSE 0 END ) AS NUM2,
-- 					SUM( CASE WHEN pd.CREATETIME >= TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE), -1)+1 )
-- 					AND pd.CREATETIME < TRUNC( SYSDATE ) + 1 
-- 					AND GL.HIGHPRESSUREFLAG = 1 THEN 1 ELSE 0 END ) AS NUM3,
-- 					SUM( CASE WHEN pd.CREATETIME >= TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE), -1)+1 )
-- 					AND pd.CREATETIME < TRUNC( SYSDATE ) + 1 
-- 					THEN 1 ELSE 0 END ) AS NUM4
-- 				FROM
-- 					TP_PM_PRODUCTIONDATA pd
-- 					INNER JOIN TP_PC_GROUTINGLINE GL ON pd.GROUTINGLINEID = GL.GROUTINGLINEID 
-- 				WHERE
-- 					 (
-- 						pd.PROCEDUREID = 104 
-- 						AND pd.ISREFIRE = 0 
-- 						AND pd.CHECKFLAG = 1 
-- 						OR pd.PROCEDUREID = 11 
-- 						AND pd.VALUEFLAG = 1 
-- 					) 
-- 					AND (
-- 						INSTR( pd.GROUTINGLINECODE, 'C' ) = 1 
-- 						AND INSTR( pd.GROUTINGLINECODE, 'A' ) = 4 
-- 					OR INSTR( pd.GROUTINGLINECODE, 'C06B' ) = 1 
-- 					)

				SELECT
					SUM( CASE WHEN GL.HIGHPRESSUREFLAG = 1 THEN 1 ELSE 0 END ) AS NUM1,
					COUNT(DISTINCT PD.BARCODE) AS NUM2
				FROM
					TP_PM_PRODUCTIONDATA pd
					INNER JOIN TP_PC_GROUTINGLINE GL ON pd.GROUTINGLINEID = GL.GROUTINGLINEID 
				WHERE
					pd.CREATETIME >= TRUNC( SYSDATE ) + 7/24 
					AND pd.CREATETIME < TRUNC( SYSDATE ) + 7/24 + 1 
					AND
					 (
						pd.PROCEDUREID = 104 
						AND pd.ISREFIRE = 0 
						AND pd.CHECKFLAG = 1 
						OR pd.PROCEDUREID = 11 
						AND pd.VALUEFLAG = 1 
					) 
					AND (
						INSTR( pd.GROUTINGLINECODE, 'C' ) = 1 
						AND INSTR( pd.GROUTINGLINECODE, 'A' ) = 4 
					OR INSTR( pd.GROUTINGLINECODE, 'C06B' ) = 1 
					)
                ";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
            else if (context.Request["m"].ToString() == "pass")
            {
                //                string sqlStr = @"
                //				SELECT
                //					SJ,
                //-- 					ROUND( ( NUM1 - NUM2 ) / DECODE( NUM1, 0, 1, NUM1 )*100, 1 ) AS 本烧,
                //-- 					ROUND( ( NUM3 - NUM4 ) / DECODE( NUM1, 0, 1, NUM1 )*100, 1 ) AS 整体 
                //					NUM1 - NUM2  AS 本烧,
                //					NUM3 - NUM4  AS 整体 
                //				FROM
                //					(
                //					SELECT
                //						TO_CHAR( TRUNC( CREATETIME - 7/24 ), 'YY-MM-DD' ) AS SJ,
                //						-- SUM( CASE WHEN ( PROCEDUREID = 104 AND ISREFIRE = 0 AND CHECKFLAG = 1 OR PROCEDUREID = 11 AND VALUEFLAG = 1  OR PROCEDUREID = 58 AND VALUEFLAG = 1 ) THEN 1 ELSE 0 END ) AS NUM1,
                //						SUM( CASE WHEN PROCEDUREID = 104 AND ISREFIRE = 0 AND CHECKFLAG = 1  THEN 1 ELSE 0 END ) AS NUM1,
                //						SUM(
                //						CASE

                //								WHEN 
                //								 ( PROCEDUREID = 105 AND ISREFIRE = 0 OR PROCEDUREID = 12 )
                //								 -- PROCEDUREID = 105 AND ISREFIRE = 0 
                //								AND GOODSLEVELTYPEID IN ( 6, 7, 8 ) 
                //								AND VALUEFLAG = 1 THEN
                //									1 ELSE 0 
                //								END 
                //								) AS NUM2,
                //								SUM( CASE WHEN PROCEDUREID = 104 AND CHECKFLAG = 1  THEN 1 ELSE 0 END ) AS NUM3,
                //								-- SUM( CASE WHEN ( PROCEDUREID = 104 AND CHECKFLAG = 1 OR PROCEDUREID = 11 AND VALUEFLAG = 1 OR PROCEDUREID = 58 AND VALUEFLAG = 1 ) THEN 1 ELSE 0 END ) AS NUM3,
                //								-- SUM( CASE WHEN ( PROCEDUREID = 105 OR PROCEDUREID = 12 OR PROCEDUREID = 59 ) AND GOODSLEVELTYPEID IN ( 6, 7, 8 ) AND VALUEFLAG = 1 THEN 1 ELSE 0 END ) AS NUM4 
                //								SUM( CASE WHEN  PROCEDUREID = 105  AND GOODSLEVELTYPEID IN ( 6, 7, 8 ) AND VALUEFLAG = 1 THEN 1 ELSE 0 END ) AS NUM4 
                //							FROM
                //								TP_PM_PRODUCTIONDATA 
                //							WHERE
                //								CREATETIME >= TRUNC( SYSDATE ) - 6  + 7/24
                //								AND CREATETIME < TRUNC( SYSDATE ) + 1  + 7/24
                //				                -- AND KILNCODE IN('SK3', 'TK3') 
                //								-- AND (INSTR( GROUTINGLINECODE, 'C' ) = 1 AND INSTR( GROUTINGLINECODE, 'A' ) = 4 OR INSTR( GROUTINGLINECODE, 'C06B' ) = 1)
                //								-- AND INSTR( GROUTINGLINECODE, 'C' ) = 1 AND INSTR( GROUTINGLINECODE, 'A' ) = 4
                //							GROUP BY
                //							TO_CHAR( TRUNC( CREATETIME - 7/24  ), 'YY-MM-DD' ) 
                //					)
                //					ORDER BY
                //						SJ -- DESC ";
                string sqlStr = @"SELECT
							TO_CHAR( TRUNC( T4.本烧合格 / T4.本烧数量,4) * 100, '9,999.99' ) || '%' AS 合格率,
							T4.本烧合格 as 烧成合格数, 
							SUBSTR(T4.CREATETIME,5,2 ) ||'.'|| SUBSTR(T4.CREATETIME,7,2 ) AS 月日
						FROM
							(
							SELECT
								T1.本烧数量,
								T2.本烧不合格数量,
								T1.本烧数量 - T2.本烧不合格数量 AS 本烧合格,
								T1.CREATETIME 
							FROM
								(
									SELECT DISTINCT--本烧数量
									COUNT( TPPD.BARCODE ) 本烧数量,
									to_char( TPPD.CREATETIME, 'yyyymmdd' ) AS CREATETIME
								FROM
									TP_PM_PRODUCTIONDATA TPPD
									INNER JOIN TP_PM_GROUTINGDAILYDETAIL PGD ON PGD.BARCODE = TPPD.BARCODE
									LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPGL.GROUTINGLINEID = PGD.GROUTINGLINEID 
								WHERE
									TPPD.PROCEDUREID IN ( 104 ) 
									AND TPPD.ISREFIRE = 0 
									AND PGD.TESTFLAG = 0 
									AND TPPD.VALUEFLAG = 1 
									--AND TPPD.checkflag = '1' 
									AND TPPD.CREATETIME >= trunc( SYSDATE - 6 ) 
								GROUP BY
									to_char( TPPD.CREATETIME, 'yyyymmdd' ) 
								ORDER BY
									to_char( TPPD.CREATETIME, 'yyyymmdd' ) DESC 
								) T1
								LEFT JOIN (
								SELECT
									to_char( T1.CREATETIME, 'yyyymmdd' ) AS CREATETIME,
									COUNT( T1.BARCODE ) AS 本烧不合格数量 
								FROM
									(
									SELECT DISTINCT
										T.BARCODE,
										TPPD.CREATETIME 
									FROM
										TP_PM_PRODUCTIONDATA TPPD
										LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPPD.GROUTINGLINEID = TPGL.GROUTINGLINEID
										LEFT JOIN TP_PM_DEFECT TPD ON TPPD.BARCODE = TPD.BARCODE
										LEFT JOIN (
										SELECT
											DISTINCT
											TPPD.BARCODE 
										FROM
											TP_PM_PRODUCTIONDATA TPPD
											LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPPD.GROUTINGLINEID = TPGL.GROUTINGLINEID 
											LEFT JOIN TP_PM_DEFECT TPD ON TPPD.BARCODE = TPD.BARCODE
											LEFT JOIN TP_MST_DEFECT TMD ON TPD.DEFECTCODE = TMD.DEFECTCODE 
										WHERE
											TPPD.CHECKTIME >= trunc( SYSDATE - 6 ) 
											AND TPPD.GOODSLEVELID IN ( 6, 7 ) 
											AND TPPD.CHECKBATCHNO = 1 
											AND TPGL.TESTFLAG = 0 
											AND TMD.DEFECTTYPEID <> 14 
											AND TPPD.VALUEFLAG = 1 
											AND TPD.DEFECTNAME IS NOT NULL
											--AND TPPD.ISREFIRE = 0 
										) T ON T.BARCODE = TPPD.BARCODE 
									WHERE
										TPPD.CHECKTIME >= trunc( SYSDATE - 6 ) 
										AND TPPD.PROCEDUREID IN ( 104 ) 
										AND length( TPPD.kilncarbatchno ) > 0 
										AND TPGL.TESTFLAG = 0 
										AND TPPD.VALUEFLAG = 1 
										AND TPPD.ISREFIRE = 0 
										AND TPD.DEFECTNAME IS NOT NULL
									) T1 
								GROUP BY
									to_char( T1.CREATETIME, 'yyyymmdd' ) 
								ORDER BY
									to_char( T1.CREATETIME, 'yyyymmdd' ) DESC 
								) T2 ON T2.CREATETIME = T1.CREATETIME
						ORDER BY
							CREATETIME DESC)T4 ORDER BY T4.CREATETIME";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
            else if (context.Request["m"].ToString() == "scrap")
            {
                string sqlStr = @"
				SELECT
					RN,
					DC,
					-- DN||'('||DC||')' DN,
					DN||'_'||OFFICENAME DN,
					NM,
					ROUND( 100 * RATIO_TO_REPORT ( NM ) OVER ( PARTITION BY 1 ), 1 ) || '%' AS PERCENTAGE  
				FROM
					(
					SELECT
						D.DEFECTCODE AS DC,
						DEFECT.S_NAME AS DN,
						-- ,s.GOODSLEVELTYPEID
						DOL.OFFICENAME,
						COUNT( DISTINCT S.BARCODE ) AS NM,
						ROW_NUMBER ( ) OVER ( ORDER BY COUNT( DISTINCT S.BARCODE ) DESC ) AS RN 
					FROM
						TP_PM_SCRAPPRODUCT S
						INNER JOIN TP_PM_DEFECT D ON S.BARCODE = D.BARCODE 
						INNER JOIN TP_DSB_DEFECTOFFICELINKED DOL ON DOL.DEFECTCODE = D.DEFECTCODE 	
						INNER JOIN TP_MST_DEFECT DEFECT ON D.DEFECTID = DEFECT.DEFECTID
					WHERE
						S.VALUEFLAG = 1 
						AND S.GOODSLEVELTYPEID IN ( 7 ) 
						AND D.VALUEFLAG = 1 
						AND instr( D.DEFECTCODE, '0' ) <> 1 
						AND S.SCRAPTYPE = 1 
						AND S.AUDITSTATUS = 1 
						AND S.ISREFIRE = 0 
						AND S.CREATETIME >= TRUNC( SYSDATE ) + 7/24
					GROUP BY
						DOL.OFFICENAME,
						D.DEFECTCODE,
						DEFECT.S_NAME 
					) 
-- 				WHERE
-- 					RN < 11
                ";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
            else if (context.Request["m"].ToString() == "officedefect")
            {
                string sqlStr = @"
				SELECT
					DOL.OFFICENAME,
				-- 	D.DEFECTCODE,
				-- 	D.DEFECTNAME,
					COUNT( DISTINCT D.BARCODE ) AS NUM,
					ROUND( 100 * RATIO_TO_REPORT ( COUNT( DISTINCT D.BARCODE ) ) OVER ( PARTITION BY 1 ), 1 ) || '%' AS PERCENTAGE 
				FROM
					TP_PM_DEFECT D
					LEFT JOIN TP_MST_GOODS G ON D.GOODSID = G.GOODSID
					LEFT JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID
					INNER JOIN TP_PM_PRODUCTIONDATA PD ON D.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
					INNER JOIN TP_DSB_DEFECTOFFICELINKED DOL ON D.DEFECTCODE = DOL.DEFECTCODE 
				WHERE
					D.VALUEFLAG = 1 
					AND PD.VALUEFLAG = 1 
					AND PD.GOODSLEVELTYPEID IN ( 6, 7 ) 
					AND PD.KILNCODE IN ( 'SK3', 'TK3' ) 
					AND D.CREATETIME >= TRUNC( SYSDATE )  + 7/24
					AND D.CREATETIME < TRUNC( SYSDATE ) + 7/24 + 1 
					-- AND (INSTR( PD.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( PD.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( PD.GROUTINGLINECODE, 'C06B' ) = 1)
	
					AND INSTR( PD.GROUTINGLINECODE, 'C' ) = 1 
					AND INSTR( PD.GROUTINGLINECODE, 'A' ) = 4 
				GROUP BY
					DOL.OFFICENAME -- 	,
				-- 	D.DEFECTCODE,
				-- 	D.DEFECTNAME
	
				ORDER BY
					NUM DESC
                ";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
            else if (context.Request["m"].ToString() == "goodsdefect")
            {
                string sqlStr = @"
				SELECT
					D.GOODSCODE,
					D.DEFECTCODE,
				-- D.DEFECTNAME||'('||D.DEFECTCODE||')' DEFECTNAME,
					DEFECT.S_NAME || '_' || DOL.OFFICENAME DEFECTNAME,
				-- D.DEFECTNAME,
					COUNT( DISTINCT D.BARCODE ) AS NUM,
					ROUND( 100 * RATIO_TO_REPORT ( COUNT( DISTINCT D.BARCODE ) ) OVER ( PARTITION BY 1 ), 1 ) || '%' AS PERCENTAGE 
				FROM
					TP_PM_DEFECT D
					LEFT JOIN TP_MST_GOODS G ON D.GOODSID = G.GOODSID
					LEFT JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID
					INNER JOIN TP_PM_PRODUCTIONDATA PD ON D.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
					INNER JOIN TP_DSB_DEFECTOFFICELINKED DOL ON DOL.DEFECTCODE = D.DEFECTCODE 	
					INNER JOIN TP_MST_DEFECT DEFECT ON D.DEFECTID = DEFECT.DEFECTID	
				WHERE
					D.VALUEFLAG = 1 
					AND PD.VALUEFLAG = 1 
					AND PD.GOODSLEVELTYPEID IN ( 6, 7 ) 
					AND PD.KILNCODE IN ( 'SK3', 'TK3' ) 
					AND D.CREATETIME >= trunc( SYSDATE )  + 7/24
					AND D.CREATETIME < trunc( SYSDATE ) + 7/24 + 1 -- AND (INSTR( PD.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( PD.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( PD.GROUTINGLINECODE, 'C06B' ) = 1)
	
					AND INSTR( PD.GROUTINGLINECODE, 'C' ) = 1 
					AND INSTR( PD.GROUTINGLINECODE, 'A' ) = 4 
				GROUP BY
					DOL.OFFICENAME,
					D.GOODSCODE,
					D.DEFECTCODE,
					DEFECT.S_NAME
				ORDER BY
					NUM DESC
                ";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
            else if (context.Request["m"].ToString() == "count")
            {
                string sqlStr = @"
					SELECT
						TO_CHAR( TRUNC( CREATETIME -7/24 ), 'YY-MM-DD' ) AS SJ,
						-- SUM( CASE WHEN ( PROCEDUREID = 104 AND ISREFIRE = 0 AND CHECKFLAG = 1 OR PROCEDUREID = 11 AND VALUEFLAG = 1 ) THEN 1 ELSE 0 END ) AS NUM1,
						SUM( CASE WHEN  PROCEDUREID = 104 AND ISREFIRE = 0 AND CHECKFLAG = 1  THEN 1 ELSE 0 END ) AS NUM1,
						SUM(
						CASE
				
								WHEN 
								( PROCEDUREID = 105 AND ISREFIRE = 0 OR PROCEDUREID = 12 )
								-- PROCEDUREID = 105 AND ISREFIRE = 0 
								AND GOODSLEVELTYPEID IN ( 6, 7, 8 ) 
								AND VALUEFLAG = 1 THEN
									1 ELSE 0 
								END 
								) AS NUM2,
								SUM( CASE WHEN PROCEDUREID = 104 AND CHECKFLAG = 1 THEN 1 ELSE 0 END ) AS NUM3,
								-- SUM( CASE WHEN ( PROCEDUREID = 104 AND CHECKFLAG = 1 OR PROCEDUREID = 11 AND VALUEFLAG = 1 OR PROCEDUREID = 58 AND VALUEFLAG = 1 ) THEN 1 ELSE 0 END ) AS NUM3,
								SUM( CASE WHEN ( PROCEDUREID = 105 OR PROCEDUREID = 12 OR PROCEDUREID = 59 ) AND GOODSLEVELTYPEID IN ( 6, 7, 8 ) AND VALUEFLAG = 1 THEN 1 ELSE 0 END ) AS NUM4 
								-- SUM( CASE WHEN  PROCEDUREID = 105  AND GOODSLEVELTYPEID IN ( 6, 7, 8 ) AND VALUEFLAG = 1 THEN 1 ELSE 0 END ) AS NUM4 
							FROM
								TP_PM_PRODUCTIONDATA 
							WHERE
								CREATETIME >= TRUNC( SYSDATE ) - 6  + 7/24
								AND CREATETIME < TRUNC( SYSDATE ) + 1  + 7/24
				                -- AND KILNCODE IN('SK3', 'TK3') 
								-- AND (INSTR( GROUTINGLINECODE, 'C' ) = 1 AND INSTR( GROUTINGLINECODE, 'A' ) = 4 OR INSTR( GROUTINGLINECODE, 'C06B' ) = 1)
								-- AND INSTR( GROUTINGLINECODE, 'C' ) = 1 AND INSTR( GROUTINGLINECODE, 'A' ) = 4 
					GROUP BY
							TO_CHAR( TRUNC( CREATETIME -7/24 ), 'YY-MM-DD' ) 
					ORDER BY
						TO_CHAR( TRUNC( CREATETIME -7/24 ), 'YY-MM-DD' ) -- DESC
                ";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
            else if (context.Request["m"].ToString() == "high")
            {
                string sqlStr = @"
				SELECT
					GOODSCODE,
					NVL( NUM1, 0 ) AS N1,
					NVL( NUM1, 0 ) - NVL( NUM2, 0 ) AS N2,
					ROUND(
						( NVL( NUM1, 0 ) - NVL( NUM2, 0 ) ) / DECODE( NVL( NUM1, 1 ), 0, 1, NVL( NUM1, 1 ) ) * 100,
						1 
					) || '%' AS GP 
				FROM
					(
					SELECT
						PD.GOODSCODE,
						SUM(
						CASE
				
								WHEN (
									PD.PROCEDUREID = 104 
									AND PD.ISREFIRE = 0 
									AND PD.CHECKFLAG = 1 
									OR PD.PROCEDUREID = 11 
									AND PD.VALUEFLAG = 1 
									) THEN
									1 ELSE 0 
								END 
								) AS NUM1,
								SUM(
								CASE
						
										WHEN ( PD.PROCEDUREID = 105 AND PD.ISREFIRE = 0 OR PD.PROCEDUREID = 12 ) 
										AND PD.GOODSLEVELTYPEID IN ( 6, 7, 8 ) 
										AND PD.VALUEFLAG = 1 THEN
											1 ELSE 0 
										END 
										) AS NUM2 
									FROM
										TP_PM_PRODUCTIONDATA PD
										INNER JOIN TP_PC_GROUTINGLINE GL ON PD.GROUTINGLINEID = GL.GROUTINGLINEID 
									WHERE
										PD.CREATETIME >= TRUNC( SYSDATE )  + 7/24
										AND PD.CREATETIME < TRUNC( SYSDATE ) + 1  + 7/24
										AND GL.HIGHPRESSUREFLAG = 1 
										AND (
											INSTR( PD.GROUTINGLINECODE, 'C' ) = 1 
											AND INSTR( PD.GROUTINGLINECODE, 'A' ) = 4 
											OR INSTR( PD.GROUTINGLINECODE, 'C06B' ) = 1 
										) 
									GROUP BY
									GROUPING SETS
										(PD.GOODSCODE ,())
									) 
								ORDER BY
								N1 DESC,
					N2 DESC
                ";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
            //        else if (context.Request["m"].ToString() == "pass2")
            //        {
            //string sqlStr = @"
            //SELECT
            //	SJ,
            //	round( ( num1 - num2 ) / decode( num1, 0, 1, num1 )*100, 1 ) AS 本烧,
            //	round( ( num3 - num4 ) / decode( num1, 0, 1, num1 )*100, 1 ) AS 整体 
            //FROM
            //	(
            //	SELECT
            //		TO_CHAR( TRUNC( CREATETIME ), 'YY-MM-DD' ) AS SJ,
            //		SUM( CASE WHEN ( PROCEDUREID = 104 AND ISREFIRE = 0 AND CHECKFLAG = 1 OR PROCEDUREID = 11 AND VALUEFLAG = 1 ) THEN 1 ELSE 0 END ) AS NUM1,
            //		SUM(
            //		CASE
            //				WHEN 
            //				( PROCEDUREID = 105 AND ISREFIRE = 0 OR PROCEDUREID = 12 )
            //				AND GOODSLEVELTYPEID IN ( 6, 7, 8 ) 
            //				AND VALUEFLAG = 1 THEN
            //					1 ELSE 0 
            //				END 
            //				) AS NUM2,
            //				SUM( CASE WHEN ( PROCEDUREID = 104 AND CHECKFLAG = 1 OR PROCEDUREID = 11 AND VALUEFLAG = 1 ) THEN 1 ELSE 0 END ) AS NUM3,
            //				SUM( CASE WHEN ( PROCEDUREID = 105 OR PROCEDUREID = 12 ) AND GOODSLEVELTYPEID IN ( 6, 7, 8 ) AND VALUEFLAG = 1 THEN 1 ELSE 0 END ) AS NUM4 
            //			FROM
            //				TP_PM_PRODUCTIONDATA 
            //			WHERE
            //				CREATETIME >= TRUNC( SYSDATE ) - 13 
            //				AND CREATETIME < TRUNC( SYSDATE ) - 6
            //                -- AND KILNCODE IN('SK3', 'TK3') 
            //				-- AND (INSTR( GROUTINGLINECODE, 'C' ) = 1 AND INSTR( GROUTINGLINECODE, 'A' ) = 4 OR INSTR( GROUTINGLINECODE, 'C06B' ) = 1)
            //				-- AND INSTR( GROUTINGLINECODE, 'C' ) = 1 AND INSTR( GROUTINGLINECODE, 'A' ) = 4 
            //			GROUP BY
            //				TO_CHAR( TRUNC( CREATETIME ), 'YY-MM-DD' ) 
            //	)
            //	ORDER BY
            //		SJ -- DESC
            //            ";
            //            //直接获取不分页数据
            //            DataTable dt = conn.ExecuteDatatable(sqlStr);
            //            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;
        }
    }

}