HEGII.ss.mes.dashboard/wwwroot/main/Procedure/test7/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() == "kc")
            {
                string sqlStr = @"SELECT
									CASE WHEN T.GOODSTYPEID = 3 THEN '连体'
									 WHEN T.GOODSTYPEID = 18 THEN '智能'
										ELSE '其他' END AS 产品类别,
									T.count 数量
									FROM (
									SELECT
										GOODSTYPEID,
										COUNT( * ) count
									FROM
										TP_PM_INPRODUCTION TPI
										LEFT JOIN TP_MST_GOODS TMG ON TPI.GOODSCODE = TMG.GOODSCODE 
									WHERE
										TPI.PROCEDUREID = 91 
									GROUP BY
										TMG.GOODSTYPEID) T";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
				//毛坯库库存
				if (context.Request["m"].ToString() == "gb")
            {
                string sqlStr = @"SELECT
TO_CHAR((SUM(T.返工品数)/SUM(T.返工合格数))*100,'9,990.00') || '%' AS 返工合格率
FROM(
SELECT
											B.GOODSCODE AS 产品型号,
											B.返工 AS 返工品数,
											B.返工 - B.返工不合格 AS 返工合格数
										FROM
											(
											SELECT
												TPPD.GOODSCODE,
												TPGL.GROUTINGLINECODE,
												COUNT( TPPD.BARCODE ) 检验,
											CASE
				
													WHEN BJFG.count IS NULL THEN
													0 ELSE BJFG.count 
												END 返工,
									CASE
	
										WHEN BJFP.count IS NULL THEN
										0 ELSE BJFP.count 
										END 一次不合格,
									CASE
	
										WHEN BJFGBH.count IS NULL THEN
										0 ELSE BJFGBH.count 
										END 返工不合格,
									CASE
	
										WHEN BJFFP.count IS NULL THEN
										0 ELSE BJFFP.count 
										END 二次不合格 
									FROM
										TP_PM_PRODUCTIONDATA TPPD
										LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPPD.GROUTINGLINEID = TPGL.GROUTINGLINEID
										LEFT JOIN (--半检一检返工数
										SELECT
											T.GOODSCODE,
											T.GROUTINGLINECODE,
											COUNT( T.BARCODE ) count 
										FROM
											(
											SELECT DISTINCT
												TPPD.GOODSCODE,
												TPGL.GROUTINGLINECODE,
												TPS.BARCODE 
											FROM
												TP_PM_SEMICHECK TPS
												LEFT JOIN TP_PM_PRODUCTIONDATA TPPD ON TPS.BARCODE = TPPD.BARCODE
												LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPPD.GROUTINGLINEID = TPGL.GROUTINGLINEID 
											WHERE
												TPS.SEMICHECKTYPE = 1 
												AND TPPD.PROCEDUREID IN ( 118, 92, 88 ) 
												AND TPS.CREATETIME >= TRUNC( SYSDATE ) 
												AND TPGL.TESTFLAG = 0 
												AND ( SUBSTR( TPGL.GROUTINGLINENAME, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
												AND TPS.VALUEFLAG = 1 
											) T 
										GROUP BY
											T.GOODSCODE,
											T.GROUTINGLINECODE 
										) BJFG ON BJFG.GOODSCODE = TPPD.GOODSCODE 
										AND BJFG.GROUTINGLINECODE = TPGL.GROUTINGLINECODE
										LEFT JOIN (--半检一检返工不合格数
										SELECT
											T.GOODSCODE,
											T.GROUTINGLINECODE,
											COUNT( T.BARCODE ) count 
										FROM
											(
											SELECT DISTINCT
												TPPD.GOODSCODE,
												TPGL.GROUTINGLINECODE,
												TPS.BARCODE 
											FROM
												TP_PM_SEMICHECK TPS
												LEFT JOIN TP_PM_PRODUCTIONDATA TPPD ON TPS.BARCODE = TPPD.BARCODE
												LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPPD.GROUTINGLINEID = TPGL.GROUTINGLINEID 
											WHERE
												TPS.RESEMICHECKTYPE IN (2) 
												AND TPPD.PROCEDUREID IN ( 118, 92, 88 ) 
												AND TPS.CREATETIME >= TRUNC( SYSDATE ) 
												AND TPGL.TESTFLAG = 0 
												AND ( SUBSTR( TPGL.GROUTINGLINENAME, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
											) T 
										GROUP BY
											T.GOODSCODE,
											T.GROUTINGLINECODE 
										ORDER BY
											T.GOODSCODE 
										) BJFGBH ON BJFGBH.GOODSCODE = TPPD.GOODSCODE 
										AND BJFGBH.GROUTINGLINECODE = TPGL.GROUTINGLINECODE
										LEFT JOIN (--半检一次不合格
										SELECT
											T.GOODSCODE,
											T.GROUTINGLINECODE,
											COUNT( T.BARCODE ) count 
										FROM
											(
											SELECT DISTINCT
												TPPD.GOODSCODE,
												TPGL.GROUTINGLINECODE,
												TPS.BARCODE 
											FROM
												TP_PM_SEMICHECK TPS
												FULL JOIN TP_PM_PRODUCTIONDATA TPPD ON TPS.BARCODE = TPPD.BARCODE
												LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPPD.GROUTINGLINEID = TPGL.GROUTINGLINEID 
											WHERE
												TPS.SEMICHECKTYPE = 2 
												AND TPPD.PROCEDUREID IN ( 118, 92, 88 ) 
												AND TPS.CREATETIME >= TRUNC( SYSDATE ) 
												AND TPGL.TESTFLAG = 0 
												AND ( SUBSTR( TPGL.GROUTINGLINENAME, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
												AND TPS.VALUEFLAG = 1 
											) T 
										GROUP BY
											T.GOODSCODE,
											T.GROUTINGLINECODE 
										) BJFP ON BJFP.GOODSCODE = TPPD.GOODSCODE 
										AND BJFP.GROUTINGLINECODE = TPGL.GROUTINGLINECODE
										LEFT JOIN (--半检二次不合格
										SELECT
											T.GOODSCODE,
											T.GROUTINGLINECODE,
											COUNT( T.BARCODE ) count 
										FROM
											(
											SELECT DISTINCT
												TPPD.GOODSCODE,
												TPGL.GROUTINGLINECODE,
												TPS.BARCODE 
											FROM
												TP_PM_SEMICHECK TPS
												FULL JOIN TP_PM_PRODUCTIONDATA TPPD ON TPS.BARCODE = TPPD.BARCODE
												LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPPD.GROUTINGLINEID = TPGL.GROUTINGLINEID 
												WHERE--TPS.GOODSLEVELID IN (13)
												TPS.RESEMICHECKTYPE = 2 
												AND TPPD.PROCEDUREID IN ( 118, 92, 88 ) 
												AND TPS.CREATETIME >= TRUNC( SYSDATE ) 
												AND TPGL.TESTFLAG = 0 
												AND ( SUBSTR( TPGL.GROUTINGLINENAME, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
												AND TPS.VALUEFLAG = 1 
											) T 
										GROUP BY
											T.GOODSCODE,
											T.GROUTINGLINECODE 
										) BJFFP ON BJFP.GOODSCODE = TPPD.GOODSCODE 
										AND BJFFP.GROUTINGLINECODE = TPGL.GROUTINGLINECODE 
									WHERE
										TPPD.CREATETIME >= TRUNC( SYSDATE ) 
										AND TPPD.PROCEDUREID IN ( 118, 92, 88 ) 
										AND TPGL.TESTFLAG = 0 
										AND TPPD.VALUEFLAG = 1 
										AND ( SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
									GROUP BY
										TPPD.GOODSCODE,
										TPGL.GROUTINGLINECODE,
										BJFG.count,
										BJFP.count,
										BJFGBH.count,
										BJFFP.count 
									ORDER BY
										TPPD.GOODSCODE 
										) B )T";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
				//每小时产量折线图
				if (context.Request["m"].ToString() == "h")
            {
                string sqlStr = @"SELECT					 T.CREATETIME 日期,
									CASE
									WHEN TT.一检数量 IS NULL THEN
										0
									ELSE
										TT.一检数量 
								END  AS 一检产量,
								TT2.预期产量
								FROM
									(
									SELECT
										0 半检数量,
										'06' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 半检数量,
										'07' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 半检数量,
										'08' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 半检数量,
										'09' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 半检数量,
										'10' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 半检数量,
										'11' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 半检数量,
										'12' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 半检数量,
										'13' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 半检数量,
										'14' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 半检数量,
										'15' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 半检数量,
										'16' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 半检数量,
										'17' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 半检数量,
										'18' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 半检数量,
										'19' AS CREATETIME 
									FROM
										DUAL 
									) T
									FULL JOIN (
									SELECT
										T2.CREATETIME,
										COUNT( * ) 一检数量 
									FROM
										(
										SELECT
											SUBSTR( TO_CHAR( TPP.CREATETIME, 'yyyy-mm-dd hh24:mi:ss' ), 12, 2 ) AS CREATETIME 
										FROM
											TP_PM_PRODUCTIONDATA TPP 
										WHERE
											TPP.PROCEDUREID IN ( 118, 92, 88 ) 
											AND TPP.VALUEFLAG = 1 
											AND TPP.CREATETIME >= trunc( SYSDATE ) 
										) T2 
									GROUP BY
										T2.CREATETIME 
									ORDER BY
									T2.CREATETIME 
									) TT ON T.CREATETIME = TT.CREATETIME
									FULL JOIN (
									SELECT
										0 预期产量,
										'06' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										200 预期产量,
										'07' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										200 预期产量,
										'08' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										200 预期产量,
										'09' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										200 预期产量,
										'10' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										60 预期产量,
										'11' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										200 预期产量,
										'12' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										200 预期产量,
										'13' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										200 预期产量,
										'14' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										200 预期产量,
										'15' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										60 预期产量,
										'16' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										200 预期产量,
										'17' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										200 预期产量,
										'18' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										200 预期产量,
										'19' AS CREATETIME 
									FROM
										DUAL 
									) TT2 ON T.CREATETIME = TT2.CREATETIME ORDER BY 日期";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
				//工号产量柱状图
				if (context.Request["m"].ToString() == "cl")
            {
                string sqlStr = @"SELECT
										TMU.USERCODE 工号,
										COUNT( * ) 产量 
									FROM
										TP_PM_PRODUCTIONDATA TPP
										LEFT JOIN TP_MST_USER TMU ON TPP.CREATEUSERID = TMU.USERID 
									WHERE
										TPP.PROCEDUREID IN ( 118, 92, 88 ) 
										AND TPP.CREATETIME >= trunc( SYSDATE ) 
										AND TPP.VALUEFLAG = 1 
									GROUP BY
										TMU.USERCODE";
                //直接获取不分页数据
                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.name AS 缺陷名称,
										T.count  AS 数量
									FROM
									(SELECT
										TMD.S_name name,
										COUNT( * ) count
									FROM
										TP_PM_SEMICHECKDEFECT TPSD
										INNER JOIN TP_PM_SEMICHECK TPS ON TPSD.SEMICHECKID = TPS.SEMICHECKID
										LEFT JOIN TP_MST_DEFECT TMD ON TPSD.DEFECTCODE = TMD.DEFECTCODE 
										--LEFT JOIN TP_MST_DEFECT TMD ON TPSD.DEFECTID = TMD.DEFECTID
										LEFT JOIN TP_PM_GROUTINGDAILYDETAIL PGD ON PGD.BARCODE = TPS.BARCODE
										LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPGL.GROUTINGLINEID = PGD.GROUTINGLINEID 
									WHERE
										TPS.UPDATETIME >= TRUNC( SYSDATE) 
										AND ( SUBSTR( TPGL.GROUTINGLINENAME, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
										--AND TPS.PROCEDUREID IN (83,88,92,118,93)
										AND TPS.RESEMICHECKTYPE = 2
									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.数量 
                                    FROM
	                                    (
	                                   SELECT
																		 DF.S_NAME as S_NAME,
																		 COUNT(DISTINCT sed.BARCODE) as 数量
																			FROM TP_PM_SEMICHECK  sed
																			left JOIN TP_PM_SEMICHECKDEFECT se on se.SEMICHECKID=sed.SEMICHECKID
																			LEFT JOIN TP_MST_DEFECT DF ON DF.DEFECTCODE=SE.DEFECTCODE
																			WHERE
																			  sed.CREATETIME >= TRUNC( SYSDATE ) AND SED.SEMICHECKTYPE=2
																				AND sed.GROUTINGLINECODE LIKE 'C0%'
																				 GROUP BY
		                                     DF.S_NAME
	                                    ORDER BY
		                                    COUNT(DISTINCT sed.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() == "zx")
            {
                string sqlStr = @"SELECT
CASE
		
	WHEN
		T4.半检数量 = 0 THEN
			'100%' ELSE TO_CHAR( TRUNC( T4.半检合格 / T4.半检数量, 4 ) * 100, '990.00' ) || '%' 
			END AS 一检合格率,
-- 	CASE
-- 			WHEN T4.二检数量 = 0 THEN
-- 			'100%' ELSE TO_CHAR( TRUNC( T4.二检合格 / T4.二检数量, 4 ) * 100, '990.00' ) || '%' 
-- 		END AS 二检合格率,
		T4.半检数量,
		SUBSTR( T4.CREATETIME, 5, 2 )||'.'||SUBSTR( T4.CREATETIME, 7, 2 ) AS 日期 
	FROM
		(
		SELECT
			T.半检数量,
			T.半检数量 - ( CASE WHEN T2.半检不合格数量 IS NULL THEN 0 ELSE T2.半检不合格数量 END ) AS 半检合格,
			T1.二检数量,
--T2.二检不合格数量,
			T1.二检数量 - T2.二检不合格数量 AS 二检合格,
			T.CREATETIME 
		FROM
			(--半检数量
			SELECT
			CASE
					
				WHEN
					T2.半检数量 IS NULL THEN
						0 ELSE T2.半检数量 
					END 半检数量,
	T2.CREATETIME CREATETIME 
FROM
	(
		SELECT
			半检数量,
			CREATETIME 
		FROM
			(
			SELECT DISTINCT
			CASE
					
				WHEN
					COUNT( TPPD.BARCODE ) IS NULL THEN
						0 ELSE COUNT( TPPD.BARCODE )
					END 半检数量,
		to_char( TPPD.CREATETIME, 'yyyymmdd' ) AS CREATETIME 
	FROM
		TP_PM_PRODUCTIONDATA TPPD
		LEFT 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 ( 118, 92, 88 ) 
		AND PGD.TESTFLAG = 0 
		AND ( SUBSTR( TPGL.GROUTINGLINENAME, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
		AND TPPD.CREATETIME >= trunc( SYSDATE - 10 ) 
		AND TPPD.VALUEFLAG = 1 
	GROUP BY
		to_char( TPPD.CREATETIME, 'yyyymmdd' ) 
	ORDER BY
		to_char( TPPD.CREATETIME, 'yyyymmdd' ) DESC 
		) WHERE ROWNUM < 8
	) T2 
ORDER BY
	T2.CREATETIME DESC 
	) T
	LEFT JOIN (
	SELECT
	CASE
			
		WHEN
			T2.半检不合格数量 IS NULL THEN
				0 ELSE T2.半检不合格数量 
			END 半检不合格数量,
	T2.CREATETIME CREATETIME 
FROM
	(
	SELECT
		半检不合格数量,
		CREATETIME 
	FROM
		(--半检不合格
		SELECT DISTINCT
			NVL( COUNT( TPS.BARCODE ), 0 ) 半检不合格数量,
			to_char( TPS.CREATETIME, 'yyyymmdd' ) AS CREATETIME 
		FROM
			TP_PM_SEMICHECK TPS
			LEFT JOIN TP_PM_GROUTINGDAILYDETAIL PGD ON PGD.BARCODE = TPS.BARCODE
			LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPGL.GROUTINGLINEID = PGD.GROUTINGLINEID 
		WHERE
			TPS.SEMICHECKTYPE = 2 
			AND TPS.PROCEDUREID IN ( 118, 92, 88 ) 
			AND TPS.CREATETIME >= trunc( SYSDATE - 10 ) 
			AND TPGL.TESTFLAG = 0 
			AND ( SUBSTR( TPGL.GROUTINGLINENAME, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
			AND TPS.VALUEFLAG = 1 
		GROUP BY
			to_char( TPS.CREATETIME, 'yyyymmdd' ) 
		ORDER BY
			to_char( TPS.CREATETIME, 'yyyymmdd' ) DESC 
		) WHERE ROWNUM < 8
	) T2 
ORDER BY
	T2.CREATETIME DESC 
	) T2 ON T2.CREATETIME = T.CREATETIME
	LEFT JOIN (
	SELECT
	CASE
			
		WHEN
			T2.二检数量 IS NULL THEN
				0 ELSE T2.二检数量 
			END 二检数量,
	T1.CREATETIME CREATETIME 
FROM
	(
	SELECT
		0 二检数量,
		to_char( ( TRUNC( SYSDATE - 6 ) + ROWNUM - 1 ), 'yyyymmdd' ) AS CREATETIME 
	FROM
		DUAL CONNECT BY ROWNUM <= 7 
	ORDER BY
		to_char( ( TRUNC( SYSDATE - 6 ) + ROWNUM - 1 ), 'yyyymmdd' ) DESC 
	) T1
	LEFT JOIN (
	SELECT
		二检数量,
		CREATETIME 
	FROM
		(
		SELECT DISTINCT
		CASE
				
			WHEN
				COUNT( TPPD.BARCODE ) IS NULL THEN
					0 ELSE COUNT( TPPD.BARCODE ) 
				END 二检数量,
	to_char( TPPD.CREATETIME, 'yyyymmdd' ) AS CREATETIME 
FROM
	TP_PM_PRODUCTIONDATA TPPD
	LEFT 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 ( 117 ) 
	AND PGD.TESTFLAG = 0 
	AND ( SUBSTR( TPGL.GROUTINGLINENAME, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
	AND TPPD.CREATETIME >= trunc( SYSDATE - 6 ) 
	AND TPPD.VALUEFLAG = 1 
GROUP BY
	to_char( TPPD.CREATETIME, 'yyyymmdd' ) 
ORDER BY
	to_char( TPPD.CREATETIME, 'yyyymmdd' ) DESC 
	) 
	) T2 ON T1.CREATETIME = T2.CREATETIME 
ORDER BY
	T1.CREATETIME DESC 
	) T1 ON T.CREATETIME = T1.CREATETIME
	LEFT JOIN (
	SELECT
	CASE
			
		WHEN
			T2.二检不合格数量 IS NULL THEN
				0 ELSE T2.二检不合格数量 
			END 二检不合格数量,
	T2.CREATETIME CREATETIME 
FROM
	(
	SELECT
		二检不合格数量,
		CREATETIME 
	FROM
		(--半检不合格
		SELECT DISTINCT
			NVL( COUNT( TPS.BARCODE ), 0 ) 二检不合格数量,
			to_char( TPS.CREATETIME, 'yyyymmdd' ) AS CREATETIME 
		FROM
			TP_PM_SEMICHECK TPS
			LEFT JOIN TP_PM_GROUTINGDAILYDETAIL PGD ON PGD.BARCODE = TPS.BARCODE
			LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPGL.GROUTINGLINEID = PGD.GROUTINGLINEID 
		WHERE
			TPS.RESEMICHECKTYPE = 2 
			AND TPS.PROCEDUREID IN ( 117 ) 
			AND TPS.CREATETIME >= trunc( SYSDATE - 6 ) 
			AND TPGL.TESTFLAG = 0 
			AND ( SUBSTR( TPGL.GROUTINGLINENAME, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
			AND TPS.VALUEFLAG = 1 
		GROUP BY
			to_char( TPS.CREATETIME, 'yyyymmdd' ) 
		ORDER BY
			to_char( TPS.CREATETIME, 'yyyymmdd' ) DESC 
		) 
	) T2 
ORDER BY
	T2.CREATETIME DESC 
	) T2 ON T1.CREATETIME = T2.CREATETIME 
ORDER BY
	T1.CREATETIME DESC --) T3 ON T3.CREATETIME = T.CREATETIME
	
	) T4 --ON T4.CREATETIME = T.CREATETIME
	
ORDER BY
	CREATETIME";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }

                //产线信息

                if (context.Request["m"].ToString() == "MonthTable")
            {
                string sqlStr = @"				SELECT
												B.产品型号 AS 产品型号,
												B.成型产线 AS 成型产线,
												B.检验数量 AS 检验数量,
												B.合格品数 AS 合格品数,
												B.返工品数 AS 返工品数,
												B.返工合格数 AS 返工合格数,
												B.综合合格数 AS 综合合格数,
												TO_CHAR((B.合格品数/B.检验数量)* 100,'9,990.00') || '%' AS 一次合格率,
												CASE WHEN B.返工合格数 = 0 AND B.返工品数 > 0 THEN '0%'
														 WHEN B.返工合格数 > 0 AND B.返工品数 = 0 THEN '100%' 
														 WHEN B.返工合格数 > 0 AND B.返工品数 > 0 THEN  TO_CHAR((B.返工合格数/B.返工品数)* 100,'9,990.00') || '%' ELSE '0%'  END AS 返工合格率,
												TO_CHAR((B.综合合格数/B.检验数量)* 100,'9,990.00') || '%' AS 综合合格率
												FROM(
									SELECT
										--decode( H.gid, 7, '总计', 3, '合计【' || H.产品型号 || '】', 0, H.产品型号, '--' ) 产品型号,
										decode( H.gid, 1, '小计', 0, H.产品型号, '总计' ) 产品型号,
										decode( H.gid, 1, '--', 0, H.成型产线, '--' ) 成型产线,
										decode( H.gid, 1, H.检验数量, 0, H.检验数量, H.检验数量 ) 检验数量,
										decode( H.gid, 1, H.合格品数, 0, H.合格品数, H.合格品数 ) 合格品数,
										decode( H.gid, 1, H.返工品数, 0, H.返工品数, H.返工品数 ) 返工品数,
										decode( H.gid, 1, H.返工合格数, 0, H.返工合格数, H.返工合格数 ) 返工合格数,
										decode( H.gid, 1, H.综合合格数, 0, H.综合合格数, H.综合合格数 ) 综合合格数
									FROM
										(
									SELECT
										grouping_id(Z.产品型号, Z.成型产线) gid,
										Z.产品型号,
										Z.成型产线,
										SUM(Z.检验数量) 检验数量,
										SUM(Z.合格品数) 合格品数,
										SUM(Z.返工品数) 返工品数,
										SUM(Z.返工合格数) 返工合格数,
										SUM(Z.综合合格数) 综合合格数
									FROM
										(
										SELECT
											B.GOODSCODE AS 产品型号,
											B.GROUTINGLINECODE AS 成型产线,
											B.检验 AS 检验数量,
											B.检验 - B.返工 - B.一次不合格 AS 合格品数,
											B.返工 AS 返工品数,
											B.返工不合格 AS 返工不合格数,
											B.返工 - B.返工不合格 AS 返工合格数,
											B.检验 - B.一次不合格 - B.二次不合格 AS 综合合格数 
										FROM
											(
											SELECT
												TPPD.GOODSCODE,
												TPGL.GROUTINGLINECODE,
												COUNT( TPPD.BARCODE ) 检验,
											CASE
				
													WHEN BJFG.count IS NULL THEN
													0 ELSE BJFG.count 
												END 返工,
									CASE
	
										WHEN BJFP.count IS NULL THEN
										0 ELSE BJFP.count 
										END 一次不合格,
									CASE
	
										WHEN BJFGBH.count IS NULL THEN
										0 ELSE BJFGBH.count 
										END 返工不合格,
									CASE
	
										WHEN BJFFP.count IS NULL THEN
										0 ELSE BJFFP.count 
										END 二次不合格 
									FROM
										TP_PM_PRODUCTIONDATA TPPD
										LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPPD.GROUTINGLINEID = TPGL.GROUTINGLINEID
										LEFT JOIN (--半检一检返工数
										SELECT
											T.GOODSCODE,
											T.GROUTINGLINECODE,
											COUNT( T.BARCODE ) count 
										FROM
											(
											SELECT DISTINCT
												TPPD.GOODSCODE,
												TPGL.GROUTINGLINECODE,
												TPS.BARCODE 
											FROM
												TP_PM_SEMICHECK TPS
												LEFT JOIN TP_PM_PRODUCTIONDATA TPPD ON TPS.BARCODE = TPPD.BARCODE
												LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPPD.GROUTINGLINEID = TPGL.GROUTINGLINEID 
											WHERE
												TPS.SEMICHECKTYPE = 1 
												AND TPPD.PROCEDUREID IN ( 118, 92, 88 ) 
												AND TPS.CREATETIME >= TRUNC( SYSDATE ) 
												AND TPGL.TESTFLAG = 0 
												AND ( SUBSTR( TPGL.GROUTINGLINENAME, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
												AND TPS.VALUEFLAG = 1 
											) T 
										GROUP BY
											T.GOODSCODE,
											T.GROUTINGLINECODE 
										) BJFG ON BJFG.GOODSCODE = TPPD.GOODSCODE 
										AND BJFG.GROUTINGLINECODE = TPGL.GROUTINGLINECODE
										LEFT JOIN (--半检一检返工不合格数
										SELECT
											T.GOODSCODE,
											T.GROUTINGLINECODE,
											COUNT( T.BARCODE ) count 
										FROM
											(
											SELECT DISTINCT
												TPPD.GOODSCODE,
												TPGL.GROUTINGLINECODE,
												TPS.BARCODE 
											FROM
												TP_PM_SEMICHECK TPS
												LEFT JOIN TP_PM_PRODUCTIONDATA TPPD ON TPS.BARCODE = TPPD.BARCODE
												LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPPD.GROUTINGLINEID = TPGL.GROUTINGLINEID 
											WHERE
												TPS.RESEMICHECKTYPE IN (2) 
												AND TPPD.PROCEDUREID IN ( 118, 92, 88 ) 
												AND TPS.CREATETIME >= TRUNC( SYSDATE ) 
												AND TPGL.TESTFLAG = 0 
												AND ( SUBSTR( TPGL.GROUTINGLINENAME, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
											) T 
										GROUP BY
											T.GOODSCODE,
											T.GROUTINGLINECODE 
										ORDER BY
											T.GOODSCODE 
										) BJFGBH ON BJFGBH.GOODSCODE = TPPD.GOODSCODE 
										AND BJFGBH.GROUTINGLINECODE = TPGL.GROUTINGLINECODE
										LEFT JOIN (--半检一次不合格
										SELECT
											T.GOODSCODE,
											T.GROUTINGLINECODE,
											COUNT( T.BARCODE ) count 
										FROM
											(
											SELECT DISTINCT
												TPPD.GOODSCODE,
												TPGL.GROUTINGLINECODE,
												TPS.BARCODE 
											FROM
												TP_PM_SEMICHECK TPS
												FULL JOIN TP_PM_PRODUCTIONDATA TPPD ON TPS.BARCODE = TPPD.BARCODE
												LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPPD.GROUTINGLINEID = TPGL.GROUTINGLINEID 
											WHERE
												TPS.SEMICHECKTYPE = 2 
												AND TPPD.PROCEDUREID IN ( 118, 92, 88 ) 
												AND TPS.CREATETIME >= TRUNC( SYSDATE ) 
												AND TPGL.TESTFLAG = 0 
												AND ( SUBSTR( TPGL.GROUTINGLINENAME, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
												AND TPS.VALUEFLAG = 1 
											) T 
										GROUP BY
											T.GOODSCODE,
											T.GROUTINGLINECODE 
										) BJFP ON BJFP.GOODSCODE = TPPD.GOODSCODE 
										AND BJFP.GROUTINGLINECODE = TPGL.GROUTINGLINECODE
										LEFT JOIN (--半检二次不合格
										SELECT
											T.GOODSCODE,
											T.GROUTINGLINECODE,
											COUNT( T.BARCODE ) count 
										FROM
											(
											SELECT DISTINCT
												TPPD.GOODSCODE,
												TPGL.GROUTINGLINECODE,
												TPS.BARCODE 
											FROM
												TP_PM_SEMICHECK TPS
												FULL JOIN TP_PM_PRODUCTIONDATA TPPD ON TPS.BARCODE = TPPD.BARCODE
												LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPPD.GROUTINGLINEID = TPGL.GROUTINGLINEID 
												WHERE--TPS.GOODSLEVELID IN (13)
												TPS.RESEMICHECKTYPE = 2 
												AND TPPD.PROCEDUREID IN ( 118, 92, 88 ) 
												AND TPS.CREATETIME >= TRUNC( SYSDATE ) 
												AND TPGL.TESTFLAG = 0 
												AND ( SUBSTR( TPGL.GROUTINGLINENAME, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
												AND TPS.VALUEFLAG = 1 
											) T 
										GROUP BY
											T.GOODSCODE,
											T.GROUTINGLINECODE 
										) BJFFP ON BJFP.GOODSCODE = TPPD.GOODSCODE 
										AND BJFFP.GROUTINGLINECODE = TPGL.GROUTINGLINECODE 
									WHERE
										TPPD.CREATETIME >= TRUNC( SYSDATE ) 
										AND TPPD.PROCEDUREID IN ( 118, 92, 88 ) 
										AND TPGL.TESTFLAG = 0 
										AND TPPD.VALUEFLAG = 1 
										AND ( SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
									GROUP BY
										TPPD.GOODSCODE,
										TPGL.GROUTINGLINECODE,
										BJFG.count,
										BJFP.count,
										BJFGBH.count,
										BJFFP.count 
									ORDER BY
										TPPD.GOODSCODE 
										) B 
										) Z
										GROUP BY GROUPING SETS((Z.产品型号),(Z.产品型号,Z.成型产线, Z.检验数量,Z.合格品数, Z.返工品数, Z.返工合格数,Z.综合合格数),())
										)H) B WHERE B.合格品数 >0
											 ";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                   int a = 1;
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }

				//二检产线信息
				 if (context.Request["m"].ToString() == "MonthTabletwo")
            {
                string sqlStr = @"				SELECT
															B.产品型号 AS 产品型号,
															B.成型产线 AS 成型产线,
															B.检验数量 AS 检验数量,
															B.合格品数 AS 合格品数,
															B.返工品数 AS 返工品数,
															B.返工合格数 AS 返工合格数,
															B.综合合格数 AS 综合合格数,
															TO_CHAR((B.合格品数/B.检验数量)* 100,'9,990.00') || '%' AS 一次合格率,
															CASE WHEN B.返工合格数 = 0 AND B.返工品数 > 0 THEN '0%'
																	 WHEN B.返工合格数 > 0 AND B.返工品数 = 0 THEN '100%' 
																	 WHEN B.返工合格数 > 0 AND B.返工品数 > 0 THEN  TO_CHAR((B.返工合格数/B.返工品数)* 100,'9,990.00') || '%' ELSE '0%'  END AS 返工合格率,
															TO_CHAR((B.综合合格数/B.检验数量)* 100,'9,990.00') || '%' AS 综合合格率
															FROM(
												SELECT
													--decode( H.gid, 7, '总计', 3, '合计【' || H.产品型号 || '】', 0, H.产品型号, '--' ) 产品型号,
													decode( H.gid, 1, '小计', 0, H.产品型号, '总计' ) 产品型号,
													decode( H.gid, 1, '--', 0, H.成型产线, '--' ) 成型产线,
													decode( H.gid, 1, H.检验数量, 0, H.检验数量, H.检验数量 ) 检验数量,
													decode( H.gid, 1, H.合格品数, 0, H.合格品数, H.合格品数 ) 合格品数,
													decode( H.gid, 1, H.返工品数, 0, H.返工品数, H.返工品数 ) 返工品数,
													decode( H.gid, 1, H.返工合格数, 0, H.返工合格数, H.返工合格数 ) 返工合格数,
													decode( H.gid, 1, H.综合合格数, 0, H.综合合格数, H.综合合格数 ) 综合合格数
												FROM
													(
												SELECT
													grouping_id(Z.产品型号, Z.成型产线) gid,
													Z.产品型号,
													Z.成型产线,
													SUM(Z.检验数量) 检验数量,
													SUM(Z.合格品数) 合格品数,
													SUM(Z.返工品数) 返工品数,
													SUM(Z.返工合格数) 返工合格数,
													SUM(Z.综合合格数) 综合合格数
												FROM
													(
													SELECT
														B.GOODSCODE AS 产品型号,
														B.GROUTINGLINECODE AS 成型产线,
														B.检验 AS 检验数量,
														B.检验 - B.返工 - B.一次不合格 AS 合格品数,
														B.返工 AS 返工品数,
														B.返工 - B.二次不合格 AS 返工合格数,
														B.检验 - B.一次不合格 - B.二次不合格 AS 综合合格数 
													FROM
														(
														SELECT
															TPPD.GOODSCODE,
															TPGL.GROUTINGLINECODE,
															COUNT( TPPD.BARCODE ) 检验,
														CASE
				
																WHEN BJFG.count IS NULL THEN
																0 ELSE BJFG.count 
															END 返工,
												CASE
	
													WHEN BJFP.count IS NULL THEN
													0 ELSE BJFP.count 
													END 一次不合格,
												CASE
	
													WHEN BJFGH.count IS NULL THEN
													0 ELSE BJFGH.count 
													END 返工合格,
												CASE
	
													WHEN BJFFP.count IS NULL THEN
													0 ELSE BJFFP.count 
													END 二次不合格 
												FROM
													TP_PM_PRODUCTIONDATA TPPD
													LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPPD.GROUTINGLINEID = TPGL.GROUTINGLINEID
													LEFT JOIN (--半检一检返工数
													SELECT
														T.GOODSCODE,
														T.GROUTINGLINECODE,
														COUNT( T.BARCODE ) count 
													FROM
														(
														SELECT DISTINCT
															TPPD.GOODSCODE,
															TPGL.GROUTINGLINECODE,
															TPS.BARCODE 
														FROM
															TP_PM_SEMICHECK TPS
															LEFT JOIN TP_PM_PRODUCTIONDATA TPPD ON TPS.BARCODE = TPPD.BARCODE
															LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPPD.GROUTINGLINEID = TPGL.GROUTINGLINEID 
														WHERE
															TPS.SEMICHECKTYPE = 1 
															AND TPS.PROCEDUREID IN ( 117 ) 
															AND TPS.CREATETIME >= TRUNC( SYSDATE ) 
															AND TPGL.TESTFLAG = 0 
															AND ( SUBSTR( TPGL.GROUTINGLINENAME, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
															AND TPS.VALUEFLAG = 1 
														) T 
													GROUP BY
														T.GOODSCODE,
														T.GROUTINGLINECODE 
													) BJFG ON BJFG.GOODSCODE = TPPD.GOODSCODE 
													AND BJFG.GROUTINGLINECODE = TPGL.GROUTINGLINECODE
													LEFT JOIN (--半检二检返工合格数
													SELECT
														T.GOODSCODE,
														T.GROUTINGLINECODE,
														COUNT( T.BARCODE ) count 
													FROM
														(
														SELECT DISTINCT
															TPPD.GOODSCODE,
															TPGL.GROUTINGLINECODE,
															TPS.BARCODE 
														FROM
															TP_PM_SEMICHECK TPS
															LEFT JOIN TP_PM_PRODUCTIONDATA TPPD ON TPS.BARCODE = TPPD.BARCODE
															LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPPD.GROUTINGLINEID = TPGL.GROUTINGLINEID 
														WHERE
															TPS.RESEMICHECKTYPE = 1 
															AND TPS.PROCEDUREID IN ( 117 ) 
															AND TPS.CREATETIME >= TRUNC( SYSDATE ) 
															AND TPGL.TESTFLAG = 0 
															AND ( SUBSTR( TPGL.GROUTINGLINENAME, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
														) T 
													GROUP BY
														T.GOODSCODE,
														T.GROUTINGLINECODE 
													ORDER BY
														T.GOODSCODE 
													) BJFGH ON BJFGH.GOODSCODE = TPPD.GOODSCODE 
													AND BJFGH.GROUTINGLINECODE = TPGL.GROUTINGLINECODE
													LEFT JOIN (--半检一次不合格
													SELECT
														T.GOODSCODE,
														T.GROUTINGLINECODE,
														COUNT( T.BARCODE ) count 
													FROM
														(
														SELECT DISTINCT
															TPPD.GOODSCODE,
															TPGL.GROUTINGLINECODE,
															TPS.BARCODE 
														FROM
															TP_PM_SEMICHECK TPS
															FULL JOIN TP_PM_PRODUCTIONDATA TPPD ON TPS.BARCODE = TPPD.BARCODE
															LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPPD.GROUTINGLINEID = TPGL.GROUTINGLINEID 
														WHERE
															TPS.SEMICHECKTYPE =2
															AND TPS.PROCEDUREID IN ( 117 ) 
															AND TPS.CREATETIME >= TRUNC( SYSDATE ) 
															AND TPGL.TESTFLAG = 0 
															AND ( SUBSTR( TPGL.GROUTINGLINENAME, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
															AND TPS.VALUEFLAG = 1 
														) T 
													GROUP BY
														T.GOODSCODE,
														T.GROUTINGLINECODE 
													) BJFP ON BJFP.GOODSCODE = TPPD.GOODSCODE 
													AND BJFP.GROUTINGLINECODE = TPGL.GROUTINGLINECODE
													LEFT JOIN (--半检二次不合格
													SELECT
														T.GOODSCODE,
														T.GROUTINGLINECODE,
														COUNT( T.BARCODE ) count 
													FROM
														(
														SELECT DISTINCT
															TPPD.GOODSCODE,
															TPGL.GROUTINGLINECODE,
															TPS.BARCODE 
														FROM
															TP_PM_SEMICHECK TPS
															FULL JOIN TP_PM_PRODUCTIONDATA TPPD ON TPS.BARCODE = TPPD.BARCODE
															LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPPD.GROUTINGLINEID = TPGL.GROUTINGLINEID 
															WHERE--TPS.GOODSLEVELID IN (13)
															TPS.RESEMICHECKTYPE = 2 
															AND TPS.PROCEDUREID IN ( 117 ) 
															AND TPS.CREATETIME >= TRUNC( SYSDATE ) 
															AND TPGL.TESTFLAG = 0 
															AND ( SUBSTR( TPGL.GROUTINGLINENAME, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
															AND TPS.VALUEFLAG = 1 
														) T 
													GROUP BY
														T.GOODSCODE,
														T.GROUTINGLINECODE 
													) BJFFP ON BJFP.GOODSCODE = TPPD.GOODSCODE 
													AND BJFFP.GROUTINGLINECODE = TPGL.GROUTINGLINECODE 
													WHERE
													TPPD.CREATETIME >= TRUNC( SYSDATE ) 
																									AND TPPD.PROCEDUREID IN ( 117 ) 
																									AND TPGL.TESTFLAG = 0 
																									AND TPPD.VALUEFLAG = 1 
																									AND ( SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C05' OR SUBSTR( TPGL.GROUTINGLINECODE, 1, 3 ) = 'C06' ) 
												GROUP BY
													TPPD.GOODSCODE,
													TPGL.GROUTINGLINECODE,
													BJFG.count,
													BJFP.count,
													BJFGH.count,
													BJFFP.count 
												ORDER BY
													TPPD.GOODSCODE 
													) B 
													) Z
													GROUP BY GROUPING SETS((Z.产品型号),(Z.产品型号,Z.成型产线, Z.检验数量,Z.合格品数, Z.返工品数, Z.返工合格数,Z.综合合格数),())
													)H) B
											 ";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
        }
    }
 
    public bool IsReusable {
        get {
            return false;
        }
    }

}