HEGII.gt.mes.dashboard/wwwroot/main/Procedure/test8/SecondaryCheck.ashx

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

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

public class Get_HG5010_CHECK_DEFECT : IHttpHandler, IRequiresSessionState
{
    public void ProcessRequest(HttpContext context)
    {
        using (IDataAccess conn = DataAccess.Create())
        {
            #region 第一样式
            #region 二检一期总数据
            if (context.Request["m"].ToString() == "TK1Big")
            {
                string sqlStr = @"
			WITH
				工序废品数 AS
						(
						SELECT
							TO_CHAR( DEFECT.CREATETIME, 'yyyy-mm-dd' ) 时间,
							SUM(CASE WHEN DEFECT.DEFECTCODE IN ( '62', '64', '63' ) THEN 1 ELSE 0 END) 二检数量
						FROM
							TP_PM_DEFECT DEFECT
							LEFT JOIN TP_PM_PRODUCTIONDATA PD ON DEFECT.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
							LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
							LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
							LEFT JOIN TP_MST_DEFECT MSTDEFECECT ON DEFECT.DEFECTID = MSTDEFECECT.DEFECTID
							LEFT JOIN TP_MST_DEFECTTYPE MSTDEFECECTTYPE ON MSTDEFECECT.DEFECTTYPEID = MSTDEFECECTTYPE.DEFECTTYPEID
						WHERE
							DEFECT.CREATETIME  >= trunc(sysdate) 
							--DEFECT.CREATETIME >= date'2022-08-12'
							--AND DEFECT.CREATETIME < date'2022-08-13'
							AND PD.VALUEFLAG = '1' --有效'64'
							AND DEFECT.VALUEFLAG = '1' --有效
							AND PD.ISREFIRE = 0 --非重烧
							AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
							AND GDT.GOODSTYPEID IN (4,6)
							AND DEFECTUSERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D22','D27','D29','D30','D99','D39','D40','D88','D51')
							AND PD.PROCEDUREID IN ( 49, 10 ) 
							AND PD.GOODSLEVELTYPEID=7--次品
							AND PD.ACCOUNTID=1
							AND PD.CHECKBATCHNO=1
						GROUP BY
							TO_CHAR( DEFECT.CREATETIME, 'yyyy-mm-dd' )
					),
					 数量 AS 
						(
							SELECT
								TO_CHAR( PD.CREATETIME, 'yyyy-mm-dd' ) 时间,
								SUM( CASE WHEN DEFECT.DEFECTCODE IN ( '62' ) THEN 1 ELSE 0 END ) 上水,
								SUM( CASE WHEN DEFECT.DEFECTCODE IN ( '63','64' ) THEN 1 ELSE 0 END ) 吹风
							FROM
								TP_PM_DEFECT DEFECT
								LEFT JOIN TP_PM_PRODUCTIONDATA PD ON DEFECT.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
								LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
								LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
							WHERE
						 		DEFECT.CREATETIME >= trunc(sysdate) 
								AND DEFECTUSERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D22','D27','D29','D30','D99','D39','D40','D88','D51')
								AND PD.VALUEFLAG = '1' --有效
								AND DEFECT.VALUEFLAG = '1' --有效
								AND PD.ISREFIRE = 0 --非重烧
								AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
								AND GDT.GOODSTYPEID IN (4,6)
							GROUP BY
								TO_CHAR( PD.CREATETIME, 'yyyy-mm-dd' ) 
						),
						检验数 AS (
							SELECT
								COUNT( * ) 成检交接数量,
								TO_CHAR( PD.CREATETIME, 'yyyy-mm-dd' ) 时间 
							FROM
								TP_PM_PRODUCTIONDATA PD
								LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
								LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
								INNER JOIN TP_PM_PRODUCTIONDATA PPD ON PD.BARCODE = PPD.BARCODE
							WHERE
								PD.VALUEFLAG = '1' 
						 		AND PD.CREATETIME >= trunc(sysdate) 
								AND PD.PROCEDUREID IN ( 48,9 )
								AND PPD.USERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D22','D27','D29','D11','D20','D21','D25','D26','D28','D30','D32','D34','D35','D38','D99','D33','D36','D37','D39','D40','D88','D51','D50')
								AND PD.ISREFIRE = 0 --非重烧
								AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
								AND GDT.GOODSTYPEID IN (4,6)
							GROUP BY
								TO_CHAR( PD.CREATETIME, 'yyyy-mm-dd' ) 
						),
						工序缺陷数量 AS (
						SELECT
							TO_CHAR( DEFECT.CREATETIME, 'yyyy-mm-dd' ) 时间,
							SUM(CASE WHEN DEFECT.DEFECTCODE IN ( '62', '64', '63' ) THEN 1 ELSE 0 END) 二检数量
						FROM
							TP_PM_DEFECT DEFECT
							LEFT JOIN TP_PM_PRODUCTIONDATA PD ON DEFECT.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
							LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
							LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
							LEFT JOIN TP_MST_DEFECT MSTDEFECECT ON DEFECT.DEFECTID = MSTDEFECECT.DEFECTID
							LEFT JOIN TP_MST_DEFECTTYPE MSTDEFECECTTYPE ON MSTDEFECECT.DEFECTTYPEID = MSTDEFECECTTYPE.DEFECTTYPEID
						WHERE
							 DEFECT.CREATETIME >= trunc(sysdate) 
							AND DEFECTUSERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D22','D27','D29','D30','D99','D39','D40','D88','D51')
							AND PD.VALUEFLAG = '1' --有效
							AND DEFECT.VALUEFLAG = '1' --有效
						GROUP BY
							TO_CHAR( DEFECT.CREATETIME, 'yyyy-mm-dd' )
						),
						前三部位 AS (
							SELECT
								TT9.上水,
								TT10.吹风
							FROM 
							--二检上水
							(
								SELECT
									LISTAGG ( T9.前三部位 ) within GROUP ( ORDER BY T9.数量 DESC ) 上水 
								FROM
								(
									SELECT
										T.位置 || '(' || T.数量 || ')' AS 前三部位,
										T.数量
									FROM
									(
										SELECT
											TMDPI.DEFECTPOSITIONNAME AS 位置,
											COUNT( * ) AS 数量 
										FROM
											TP_PM_DEFECT TMDP
											LEFT JOIN TP_MST_DEFECTPOSITION TMDPI ON TMDP.DEFECTPOSITIONID = TMDPI.DEFECTPOSITIONID
											LEFT JOIN TP_PM_PRODUCTIONDATA PD ON TMDP.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
											LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
											LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
										WHERE
											 TMDP.CREATETIME >= trunc(sysdate) 
											AND DEFECTUSERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D22','D27','D29','D30','D99','D39','D40','D88','D51')
											--TMDP.CREATETIME >= DATE'2022-08-12'
											--AND TMDP.CREATETIME < DATE'2022-08-13'
											AND TMDP.DEFECTCODE IN ( '62' )
											AND PD.ISREFIRE = 0 --非重烧
											AND PD.VALUEFLAG = '1' --有效
											AND TMDP.VALUEFLAG = '1' --有效
											AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
											AND GDT.GOODSTYPEID IN (4,6)
										GROUP BY
											TMDPI.DEFECTPOSITIONID,
											TMDPI.DEFECTPOSITIONNAME 
										ORDER BY
											COUNT( * ) DESC 
									) T 
									WHERE
									ROWNUM <= 3 
								) T9 
							) TT9 
							LEFT JOIN 
							--二检吹风
							(
								SELECT
									LISTAGG ( T10.前三部位 ) within GROUP ( ORDER BY T10.数量 DESC ) 吹风 
								FROM
								(
									SELECT
										T.位置 || '(' || T.数量 || ')' AS 前三部位,
										T.数量
									FROM
									(
										SELECT
											TMDPI.DEFECTPOSITIONNAME AS 位置,
											COUNT( * ) AS 数量 
										FROM
											TP_PM_DEFECT TMDP
											LEFT JOIN TP_MST_DEFECTPOSITION TMDPI ON TMDP.DEFECTPOSITIONID = TMDPI.DEFECTPOSITIONID
											LEFT JOIN TP_PM_PRODUCTIONDATA PD ON TMDP.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
											LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
											LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
										WHERE
											 TMDP.CREATETIME >= trunc(sysdate) 
											--TMDP.CREATETIME >= DATE'2022-08-12'
											--AND TMDP.CREATETIME < DATE'2022-08-13'
											AND DEFECTUSERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D22','D27','D29','D30','D99','D39','D40','D88','D51')
											AND TMDP.DEFECTCODE IN ( '63', '64' )
											AND PD.ISREFIRE = 0 --非重烧
											AND PD.VALUEFLAG = '1' --有效
											AND TMDP.VALUEFLAG = '1' --有效
											AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
											AND GDT.GOODSTYPEID IN (4,6)
										GROUP BY
											TMDPI.DEFECTPOSITIONID,
											TMDPI.DEFECTPOSITIONNAME 
										ORDER BY
											COUNT( * ) DESC 
									) T 
									WHERE
									ROWNUM <= 3 
								) T10 
							) TT10 ON 1 = 1
						
						)
						SELECT

							TO_CHAR( 数量.上水 ) 上水,
							TO_CHAR( 数量.吹风 ) 吹风
						FROM
							数量 
						UNION ALL
						SELECT			
							DECODE( NVL( 数量.上水, 0 ), 0, '0%', TO_CHAR( ( NVL( 数量.上水, 0 ) / DECODE( NVL( 检验数.成检交接数量, 1 ), 0, 1, NVL( 检验数.成检交接数量, 1 ) ) ) * 100, 'fm99990.0' ) || '%' ) ,
							DECODE( NVL( 数量.吹风, 0 ), 0, '0%', TO_CHAR( ( NVL( 数量.吹风, 0 ) / DECODE( NVL( 检验数.成检交接数量, 1 ), 0, 1, NVL( 检验数.成检交接数量, 1 ) ) ) * 100, 'fm99990.0' ) || '%' ) 
						FROM
							数量
						INNER JOIN 检验数 ON 数量.时间 = 检验数.时间
						UNION ALL
						SELECT
					
							NVL(前三部位.上水,'-'),
							NVL(前三部位.吹风,'-')
						FROM
							前三部位
						UNION ALL
-- 						SELECT
-- 							'',
-- 							--DECODE(检验数.成检交接数量, 0, '0%', TO_CHAR( ROUND( (数量.上水 + 数量.吹风) / 检验数.成检交接数量, 4 ) * 100.00, 'FM900.09' ) || '%') ,
-- 							DECODE( NVL( 工序缺陷数量.二检数量, 0 ), 0, '0%', TO_CHAR( ( NVL( 工序缺陷数量.二检数量, 0 ) / DECODE( NVL( 检验数.成检交接数量, 1 ), 0, 1, NVL( 检验数.成检交接数量, 1 ) ) ) * 100, 'fm99990.00' ) || '%' ) 
-- 						FROM
-- 							工序缺陷数量
-- 						INNER JOIN 检验数 ON 工序缺陷数量.时间 = 检验数.时间
-- 						UNION ALL
						SELECT
							'',
							--DECODE(检验数.成检交接数量, 0, '0%', TO_CHAR( ROUND( (数量.上水 + 数量.吹风) / 检验数.成检交接数量, 4 ) * 100.00, 'FM900.09' ) || '%') ,
							DECODE( NVL( 工序废品数.二检数量, 0 ), 0, '0%', TO_CHAR( ( NVL( 工序废品数.二检数量, 0 ) / DECODE( NVL( 检验数.成检交接数量, 1 ), 0, 1, NVL( 检验数.成检交接数量, 1 ) ) ) * 100, 'fm99990.00' ) || '%' ) 
						FROM
							工序废品数
						INNER JOIN 检验数 ON 工序废品数.时间 = 检验数.时间
						 ";

                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                context.Response.Write(new JsonResult(dt).ToJson());
            }
            #endregion
            //二检二期
            if (context.Request["m"].ToString() == "TK1QualifiedRate")
            {
                string sqlStr = @"
					WITH
				工序废品数 AS
						(
						SELECT
							TO_CHAR( DEFECT.CREATETIME, 'yyyy-mm-dd' ) 时间,
							SUM(CASE WHEN DEFECT.DEFECTCODE IN ( '62', '64', '63' ) THEN 1 ELSE 0 END) 二检数量
						FROM
							TP_PM_DEFECT DEFECT
							LEFT JOIN TP_PM_PRODUCTIONDATA PD ON DEFECT.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
							LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
							LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
							LEFT JOIN TP_MST_DEFECT MSTDEFECECT ON DEFECT.DEFECTID = MSTDEFECECT.DEFECTID
							LEFT JOIN TP_MST_DEFECTTYPE MSTDEFECECTTYPE ON MSTDEFECECT.DEFECTTYPEID = MSTDEFECECTTYPE.DEFECTTYPEID
						WHERE
							 DEFECT.CREATETIME  >= trunc(sysdate)
							--DEFECT.CREATETIME >= date'2022-08-12'
							--AND DEFECT.CREATETIME < date'2022-08-13'
							AND PD.VALUEFLAG = '1' --有效
							AND DEFECT.VALUEFLAG = '1' --有效
							AND PD.ISREFIRE = 0 --非重烧
							AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
							AND GDT.GOODSTYPEID IN (4,6)
							AND DEFECTUSERCODE IN ('D11','D20','D21','D25','D26','D28','D32','D34','D35','D38','D33','D36','D37','D50')
							AND PD.PROCEDUREID IN ( 49, 10 ) 
							AND PD.GOODSLEVELTYPEID=7--次品
							AND PD.ACCOUNTID=1
							AND PD.CHECKBATCHNO=1
						GROUP BY
							TO_CHAR( DEFECT.CREATETIME, 'yyyy-mm-dd' )
					),
					 数量 AS 
						(
							SELECT
								TO_CHAR( PD.CREATETIME, 'yyyy-mm-dd' ) 时间,
								SUM( CASE WHEN DEFECT.DEFECTCODE IN ( '62' ) THEN 1 ELSE 0 END ) 上水,
								SUM( CASE WHEN DEFECT.DEFECTCODE IN ( '63','64' ) THEN 1 ELSE 0 END ) 吹风
							FROM
								TP_PM_DEFECT DEFECT
								LEFT JOIN TP_PM_PRODUCTIONDATA PD ON DEFECT.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
								LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
								LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
							WHERE
						 		 DEFECT.CREATETIME >= trunc(sysdate) 
								AND DEFECTUSERCODE IN ('D11','D20','D21','D25','D26','D28','D32','D34','D35','D38','D33','D36','D37','D50')
								AND PD.VALUEFLAG = '1' --有效
								AND DEFECT.VALUEFLAG = '1' --有效
								AND PD.ISREFIRE = 0 --非重烧
								AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
								AND GDT.GOODSTYPEID IN (4,6)
							GROUP BY
								TO_CHAR( PD.CREATETIME, 'yyyy-mm-dd' ) 
						),
						检验数 AS (
							SELECT
								COUNT( * ) 成检交接数量,
								TO_CHAR( PD.CREATETIME, 'yyyy-mm-dd' ) 时间 
							FROM
								TP_PM_PRODUCTIONDATA PD
								LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
								LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
								INNER JOIN TP_PM_PRODUCTIONDATA PPD ON PD.BARCODE = PPD.BARCODE
							WHERE
								PD.VALUEFLAG = '1' 
						 		AND  PD.CREATETIME >= trunc(sysdate) 
								AND PD.PROCEDUREID IN ( 48,9 )
								AND PPD.USERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D22','D27','D29','D11','D20','D21','D25','D26','D28','D30','D32','D34','D35','D38','D99','D33','D36','D37','D39','D40','D88','D51','D50')
								AND PD.ISREFIRE = 0 --非重烧
								AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
								AND GDT.GOODSTYPEID IN (4,6)
							GROUP BY
								TO_CHAR( PD.CREATETIME, 'yyyy-mm-dd' ) 
						),
						工序缺陷数量 AS (
						SELECT
							TO_CHAR( DEFECT.CREATETIME, 'yyyy-mm-dd' ) 时间,
							SUM(CASE WHEN DEFECT.DEFECTCODE IN ( '62', '64', '63' ) THEN 1 ELSE 0 END) 二检数量
						FROM
							TP_PM_DEFECT DEFECT
							LEFT JOIN TP_PM_PRODUCTIONDATA PD ON DEFECT.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
							LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
							LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
							LEFT JOIN TP_MST_DEFECT MSTDEFECECT ON DEFECT.DEFECTID = MSTDEFECECT.DEFECTID
							LEFT JOIN TP_MST_DEFECTTYPE MSTDEFECECTTYPE ON MSTDEFECECT.DEFECTTYPEID = MSTDEFECECTTYPE.DEFECTTYPEID
						WHERE
							 DEFECT.CREATETIME >= trunc(sysdate)
							--DEFECT.CREATETIME >= date'2022-08-12'
							--AND DEFECT.CREATETIME < date'2022-08-13'
							AND DEFECTUSERCODE IN ('D11','D20','D21','D25','D26','D28','D32','D34','D35','D38','D33','D36','D37','D50')
							AND PD.VALUEFLAG = '1' --有效
							AND DEFECT.VALUEFLAG = '1' --有效
						GROUP BY
							TO_CHAR( DEFECT.CREATETIME, 'yyyy-mm-dd' )
						),
						前三部位 AS (
							SELECT
								TT9.上水,
								TT10.吹风
							FROM 
							--二检上水
							(
								SELECT
									LISTAGG ( T9.前三部位 ) within GROUP ( ORDER BY T9.数量 DESC ) 上水 
								FROM
								(
									SELECT
										T.位置 || '(' || T.数量 || ')' AS 前三部位,
										T.数量
									FROM
									(
										SELECT
											TMDPI.DEFECTPOSITIONNAME AS 位置,
											COUNT( * ) AS 数量 
										FROM
											TP_PM_DEFECT TMDP
											LEFT JOIN TP_MST_DEFECTPOSITION TMDPI ON TMDP.DEFECTPOSITIONID = TMDPI.DEFECTPOSITIONID
											LEFT JOIN TP_PM_PRODUCTIONDATA PD ON TMDP.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
											LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
											LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
										WHERE
											 TMDP.CREATETIME >= trunc(sysdate) 
											AND DEFECTUSERCODE IN ('D11','D20','D21','D25','D26','D28','D32','D34','D35','D38','D33','D36','D37','D50')
											--TMDP.CREATETIME >= DATE'2022-08-12'
											--AND TMDP.CREATETIME < DATE'2022-08-13'
											AND TMDP.DEFECTCODE IN ( '62' )
											AND PD.ISREFIRE = 0 --非重烧
											AND PD.VALUEFLAG = '1' --有效
											AND TMDP.VALUEFLAG = '1' --有效
											AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
											AND GDT.GOODSTYPEID IN (4,6)
										GROUP BY
											TMDPI.DEFECTPOSITIONID,
											TMDPI.DEFECTPOSITIONNAME 
										ORDER BY
											COUNT( * ) DESC 
									) T 
									WHERE
									ROWNUM <= 3 
								) T9 
							) TT9 
							LEFT JOIN 
							--二检吹风
							(
								SELECT
									LISTAGG ( T10.前三部位 ) within GROUP ( ORDER BY T10.数量 DESC ) 吹风 
								FROM
								(
									SELECT
										T.位置 || '(' || T.数量 || ')' AS 前三部位,
										T.数量
									FROM
									(
										SELECT
											TMDPI.DEFECTPOSITIONNAME AS 位置,
											COUNT( * ) AS 数量 
										FROM
											TP_PM_DEFECT TMDP
											LEFT JOIN TP_MST_DEFECTPOSITION TMDPI ON TMDP.DEFECTPOSITIONID = TMDPI.DEFECTPOSITIONID
											LEFT JOIN TP_PM_PRODUCTIONDATA PD ON TMDP.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
											LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
											LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
										WHERE
											 TMDP.CREATETIME>= trunc(sysdate) 
											--TMDP.CREATETIME >= DATE'2022-08-12'
											--AND TMDP.CREATETIME < DATE'2022-08-13'
											AND DEFECTUSERCODE IN ('D11','D20','D21','D25','D26','D28','D32','D34','D35','D38','D33','D36','D37','D50')
											AND TMDP.DEFECTCODE IN ( '63', '64' )
											AND PD.ISREFIRE = 0 --非重烧
											AND PD.VALUEFLAG = '1' --有效
											AND TMDP.VALUEFLAG = '1' --有效
											AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
											AND GDT.GOODSTYPEID IN (4,6)
										GROUP BY
											TMDPI.DEFECTPOSITIONID,
											TMDPI.DEFECTPOSITIONNAME 
										ORDER BY
											COUNT( * ) DESC 
									) T 
									WHERE
									ROWNUM <= 3 
								) T10 
							) TT10 ON 1 = 1
						
						)
						SELECT

							TO_CHAR( 数量.上水 ) 上水,
							TO_CHAR( 数量.吹风 ) 吹风
						FROM
							数量 
						UNION ALL
						SELECT			
							DECODE( NVL( 数量.上水, 0 ), 0, '0%', TO_CHAR( ( NVL( 数量.上水, 0 ) / DECODE( NVL( 检验数.成检交接数量, 1 ), 0, 1, NVL( 检验数.成检交接数量, 1 ) ) ) * 100, 'fm99990.0' ) || '%' ) ,
							DECODE( NVL( 数量.吹风, 0 ), 0, '0%', TO_CHAR( ( NVL( 数量.吹风, 0 ) / DECODE( NVL( 检验数.成检交接数量, 1 ), 0, 1, NVL( 检验数.成检交接数量, 1 ) ) ) * 100, 'fm99990.0' ) || '%' ) 
						FROM
							数量
						INNER JOIN 检验数 ON 数量.时间 = 检验数.时间
						UNION ALL
						SELECT
					
							NVL(前三部位.上水,'-'),
							NVL(前三部位.吹风,'-')
						FROM
							前三部位
						UNION ALL
-- 						SELECT
-- 							'',
-- 							--DECODE(检验数.成检交接数量, 0, '0%', TO_CHAR( ROUND( (数量.上水 + 数量.吹风) / 检验数.成检交接数量, 4 ) * 100.00, 'FM900.09' ) || '%') ,
-- 							DECODE( NVL( 工序缺陷数量.二检数量, 0 ), 0, '0%', TO_CHAR( ( NVL( 工序缺陷数量.二检数量, 0 ) / DECODE( NVL( 检验数.成检交接数量, 1 ), 0, 1, NVL( 检验数.成检交接数量, 1 ) ) ) * 100, 'fm99990.00' ) || '%' ) 
-- 						FROM
-- 							工序缺陷数量
-- 						INNER JOIN 检验数 ON 工序缺陷数量.时间 = 检验数.时间
-- 						UNION ALL
						SELECT
							'',
							--DECODE(检验数.成检交接数量, 0, '0%', TO_CHAR( ROUND( (数量.上水 + 数量.吹风) / 检验数.成检交接数量, 4 ) * 100.00, 'FM900.09' ) || '%') ,
							DECODE( NVL( 工序废品数.二检数量, 0 ), 0, '0%', TO_CHAR( ( NVL( 工序废品数.二检数量, 0 ) / DECODE( NVL( 检验数.成检交接数量, 1 ), 0, 1, NVL( 检验数.成检交接数量, 1 ) ) ) * 100, 'fm99990.00' ) || '%' ) 
						FROM
							工序废品数
						INNER JOIN 检验数 ON 工序废品数.时间 = 检验数.时间
				";

                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                context.Response.Write(new JsonResult(dt).ToJson());
            }
            //二检二线缺陷明细
            if (context.Request["m"].ToString() == "TK1smallTableTitle")
            {
                string sqlStr = @"
						WITH 基础数据 AS (
						SELECT DISTINCT
										TMDP.DEFECTUSERCODE 责任工号,
										TMDP.DEFECTUSERNAME 责任人,
										TMDP.BARCODE 条码,
										TMDP.DEFECTPOSITIONNAME 缺陷位置,
										TMDP.DEFECTNAME 缺陷名称,
										TMG.GOODSLEVELNAME 产品分级
										FROM TP_PM_DEFECT TMDP
										LEFT JOIN TP_MST_DEFECTPOSITION TMDPI ON TMDP.DEFECTPOSITIONID = TMDPI.DEFECTPOSITIONID
										LEFT JOIN TP_PM_PRODUCTIONDATA PD ON TMDP.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
										LEFT JOIN TP_MST_GOODSLEVEL TMG ON TMG.GOODSLEVELID = PD.GOODSLEVELID
										--LEFT JOIN TP_PC_PROCEDURE TPP ON PD.PROCEDUREID = TPP.PROCEDUREID
										LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
										LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
																				--INNER JOIN TP_PM_PRODUCTIONDATA PDD ON PD.BARCODE = PDD.BARCODE
										WHERE  TMDP.CREATETIME >= trunc(sysdate)
										AND TMDP.DEFECTUSERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D22','D27','D29','D99','D11','D20','D21','D25','D26','D28','D30','D32','D34','D35','D38','D33','D36','D37','D39','D40','D88','D51','D50')
										AND PD.VALUEFLAG = '1' --有效
										AND TMDP.VALUEFLAG = '1' --有效 
										AND TMDP.DEFECTCODE IN ('62','63','64' )
										AND PD.ISREFIRE = 0 --非重烧
										AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
										AND GDT.GOODSTYPEID IN (4,6)
										ORDER BY TMDP.DEFECTUSERCODE
						),
						责任工号出现次数 AS(
						SELECT
						*
						FROM(
						SELECT
						责任工号 AS 工号,
						COUNT(责任工号)as 出现次数
						FROM(
						SELECT DISTINCT
											TMDP.DEFECTUSERCODE 责任工号,
											TMDP.DEFECTUSERNAME 责任人,
											TMDP.BARCODE 条码,
											TMDP.DEFECTPOSITIONNAME 缺陷位置,
											TMDP.DEFECTNAME 缺陷名称,
											TMG.GOODSLEVELNAME 产品分级
											FROM TP_PM_DEFECT TMDP
												LEFT JOIN TP_MST_DEFECTPOSITION TMDPI ON TMDP.DEFECTPOSITIONID = TMDPI.DEFECTPOSITIONID
												LEFT JOIN TP_PM_PRODUCTIONDATA PD ON TMDP.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
												LEFT JOIN TP_MST_GOODSLEVEL TMG ON TMG.GOODSLEVELID = PD.GOODSLEVELID
												--LEFT JOIN TP_PC_PROCEDURE TPP ON PD.PROCEDUREID = TPP.PROCEDUREID
												LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
												LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
											
												--INNER JOIN TP_PM_PRODUCTIONDATA PDD ON PD.BARCODE = PDD.BARCODE
												WHERE  TMDP.CREATETIME >= trunc(sysdate)
												AND TMDP.DEFECTUSERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D22','D27','D29','D99','D11','D20','D21','D25','D26','D28','D30','D32','D34','D35','D38','D33','D36','D37','D39','D40','D88','D51','D50')
												AND PD.VALUEFLAG = '1' --有效
												AND TMDP.VALUEFLAG = '1' --有效 
												AND TMDP.DEFECTCODE IN ('62','63','64' )
												AND PD.ISREFIRE = 0 --非重烧
												AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
												AND GDT.GOODSTYPEID IN (4,6)
												ORDER BY TMDP.DEFECTUSERCODE
												)
												GROUP BY 责任工号
												)
											)
											SELECT
											基础数据.责任工号,
											基础数据.责任人,
											基础数据.条码,
											基础数据.缺陷位置,
											基础数据.缺陷名称,
											基础数据.产品分级,
											责任工号出现次数.出现次数
											FROM 基础数据
											LEFT JOIN 责任工号出现次数 on 基础数据.责任工号=责任工号出现次数.工号
											ORDER BY 基础数据.责任工号
											";
                //获取分页参数
                int page = 1;
                if (Convert.ToInt32(context.Request["page"].ToString()) != 1)
                {
                    page = Convert.ToInt32(context.Request["page"].ToString());
                }
                int rows = HttpContext.Current.Request["rows"] is object ? Convert.ToInt32(HttpContext.Current.Request["rows"]) : 7;
                //获取分页数据
                int total = 0;
                DataTable dt = conn.SelectPages(page, rows, out total, sqlStr);
                context.Response.Write( new JsonResult(dt) { total = total }.ToJson());

                //DataTable dt = conn.ExecuteDatatable(sqlStr);
                //context.Response.Write(new JsonResult(dt).ToJson());
            }
            //二检一线产量明细总条数
            if (context.Request["m"].ToString() == "count1"){
                string sqlStr = @"			SELECT COUNT(*) AS counts FROM(	SELECT TPP.USERCODE FROM(SELECT DISTINCT BARCODE,USERCODE FROM TP_PM_PRODUCTIONDATA
										where  USERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D22','D27','D29','D30','D99','D39','D40','D88','D51') AND
										 CREATETIME >= trunc(sysdate)
											AND  ACCOUNTID=1 AND VALUEFLAG=1 AND PROCEDUREID = 35 ) TPP GROUP BY TPP.USERCODE ORDER BY TPP.USERCODE)";
                object count = conn.ExecuteScalar(sqlStr,null);
                JObject json = new JObject(
                      new JProperty("success",true),
                      new JProperty("counts", Convert.ToInt32(count))
                    );
                context.Response.Write(json.ToString());
            }
            //二检二线产量明细
            if (context.Request["m"].ToString() == "DD1")
            {
                string sqlStr = @"SELECT TPP.USERCODE 工号,COUNT(TPP.USERCODE) 产量 FROM(SELECT DISTINCT BARCODE,USERCODE FROM TP_PM_PRODUCTIONDATA
										where  USERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D22','D27','D29','D30','D99','D39','D40','D88','D51') AND
										 CREATETIME >= trunc(sysdate)
											AND  ACCOUNTID=1 AND VALUEFLAG=1 AND PROCEDUREID = 35 ) TPP GROUP BY TPP.USERCODE ORDER BY TPP.USERCODE";
                //获取分页参数
                int page = 1;
                if (Convert.ToInt32(context.Request["page"].ToString()) != 1)
                {
                    page = Convert.ToInt32(context.Request["page"].ToString());
                }
                int rows = HttpContext.Current.Request["rows"] is object ? Convert.ToInt32(HttpContext.Current.Request["rows"]) : 4;
                //获取分页数据
                int total = 0;
                DataTable dt = conn.SelectPages(page, rows, out total, sqlStr);
                context.Response.Write( new JsonResult(dt) { total = total }.ToJson());
            }
            //二检二线产量明细总条数
            if (context.Request["m"].ToString() == "count2"){
                string sqlStr = @"SELECT COUNT(*) AS counts FROM(	SELECT TPP.USERCODE FROM(SELECT DISTINCT BARCODE,USERCODE FROM TP_PM_PRODUCTIONDATA
										where  USERCODE IN ('D11','D20','D21','D25','D26','D28','D32','D34','D35','D38','D33','D36','D37','D50') AND
										 CREATETIME >= trunc(sysdate)
											AND  ACCOUNTID=1 AND VALUEFLAG=1 AND PROCEDUREID = 35 ) TPP GROUP BY TPP.USERCODE ORDER BY TPP.USERCODE)";
                object count = conn.ExecuteScalar(sqlStr,null);
                JObject json = new JObject(
                      new JProperty("success",true),
                      new JProperty("counts", Convert.ToInt32(count))
                    );
                context.Response.Write(json.ToString());
            }
            //二检二线产量明细
            if (context.Request["m"].ToString() == "DD2")
            {
                string sqlStr = @"SELECT TPP.USERCODE 工号,COUNT(TPP.USERCODE) 产量 FROM(SELECT DISTINCT BARCODE,USERCODE FROM TP_PM_PRODUCTIONDATA
										where  USERCODE IN ('D11','D20','D21','D25','D26','D28','D32','D34','D35','D38','D33','D36','D37','D50') AND
										 CREATETIME >= trunc(sysdate)
											AND  ACCOUNTID=1 AND VALUEFLAG=1 AND PROCEDUREID = 35 ) TPP GROUP BY TPP.USERCODE ORDER BY TPP.USERCODE";
                //获取分页参数
                int page = 1;
                if (Convert.ToInt32(context.Request["page"].ToString()) != 1)
                {
                    page = Convert.ToInt32(context.Request["page"].ToString());
                }
                int rows = HttpContext.Current.Request["rows"] is object ? Convert.ToInt32(HttpContext.Current.Request["rows"]) : 4;
                //获取分页数据
                int total = 0;
                DataTable dt = conn.SelectPages(page, rows, out total, sqlStr);
                context.Response.Write( new JsonResult(dt) { total = total }.ToJson());
            }
            //缺陷明细总条数
            if (context.Request["m"].ToString() == "count"){
                string sqlStr = @"SELECT COUNT(*)
											FROM TP_PM_DEFECT TMDP
												LEFT JOIN TP_MST_DEFECTPOSITION TMDPI ON TMDP.DEFECTPOSITIONID = TMDPI.DEFECTPOSITIONID
												LEFT JOIN TP_PM_PRODUCTIONDATA PD ON TMDP.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
												LEFT JOIN TP_MST_GOODSLEVEL TMG ON TMG.GOODSLEVELID = PD.GOODSLEVELID
												--LEFT JOIN TP_PC_PROCEDURE TPP ON PD.PROCEDUREID = TPP.PROCEDUREID
												LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
												LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
												--INNER JOIN TP_PM_PRODUCTIONDATA PDD ON PD.BARCODE = PDD.BARCODE
												WHERE  TMDP.CREATETIME >= trunc(sysdate)
												AND TMDP.DEFECTUSERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D22','D27','D29','D99','D11','D20','D21','D25','D26','D28','D30','D32','D34','D35','D38','D33','D36','D37','D39','D40','D88','D51','D50')
												AND PD.VALUEFLAG = '1' --有效
												AND TMDP.VALUEFLAG = '1' --有效 
												AND TMDP.DEFECTCODE IN ('62','63','64' )
												AND PD.ISREFIRE = 0 --非重烧
												AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
												AND GDT.GOODSTYPEID IN (4,6)
												ORDER BY TMDP.DEFECTUSERCODE";
                object count = conn.ExecuteScalar(sqlStr,null);
                JObject json = new JObject(
                      new JProperty("success",true),
                      new JProperty("counts", Convert.ToInt32(count))
                    );
                context.Response.Write(json.ToString());
            }

            if (context.Request["m"].ToString() == "NumberStatistics") { 
				    string sqlStr = @"WITH 一线产量 AS (
									SELECT
										COUNT( * ) AS 一线 
									FROM
										(
										SELECT DISTINCT
											BARCODE 
										FROM
											TP_PM_PRODUCTIONDATA 
										WHERE
											USERCODE IN ( 'D12', 'D13', 'D14', 'D15', 'D16', 'D17', 'D18', 'D19', 'D22', 'D27', 'D29', 'D30', 'D99','D39','D40','D88','D51' ) 
											AND  CREATETIME >= trunc( SYSDATE ) 
											AND ACCOUNTID = 1 
											AND VALUEFLAG = 1 
											AND PROCEDUREID = 35 
										) 
									),
									二线产量 AS (
									SELECT
										COUNT( * ) AS 二线 
									FROM
										(
										SELECT DISTINCT
											BARCODE 
										FROM
											TP_PM_PRODUCTIONDATA 
										WHERE
											USERCODE IN ( 'D11', 'D20', 'D21', 'D25', 'D26', 'D28', 'D32', 'D34', 'D35', 'D38','D33','D36','D37','D50' ) 
											AND  CREATETIME >= trunc( SYSDATE ) 
											AND ACCOUNTID = 1 
											AND VALUEFLAG = 1 
											AND PROCEDUREID = 35 
										) 
									),
									总产量 AS (
									SELECT
										COUNT( * ) AS 总产量 
									FROM
										(
										SELECT DISTINCT
											BARCODE 
										FROM
											TP_PM_PRODUCTIONDATA 
											WHERE	USERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D22','D27','D29','D11','D20','D21','D25','D26','D28','D30','D32','D34','D35','D38','D99','D33','D36','D37','D39','D40','D88','D51','D50') 
											AND CREATETIME  >= trunc(sysdate) 
											AND ACCOUNTID = 1 
											AND VALUEFLAG = 1 
											AND PROCEDUREID = 35 
										) 
									) 
									SELECT 
									一线,
									二线,
									总产量 
									FROM 一线产量 
									INNER JOIN 二线产量 ON 1=1
									INNER JOIN 总产量 ON 1=1";
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                context.Response.Write(new JsonResult(dt).ToJson());
				
				}
            //合格率
            if (context.Request["m"].ToString() == "HGL") {
                string sqlStr = @"
					WITH
				工序废品数 AS
						(
						SELECT
							TO_CHAR( DEFECT.CREATETIME, 'yyyy-mm-dd' ) 时间,
							SUM(CASE WHEN DEFECT.DEFECTCODE IN ( '62', '64', '63' ) THEN 1 ELSE 0 END) 二检数量
						FROM
							TP_PM_DEFECT DEFECT
							LEFT JOIN TP_PM_PRODUCTIONDATA PD ON DEFECT.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
							LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
							LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
							LEFT JOIN TP_MST_DEFECT MSTDEFECECT ON DEFECT.DEFECTID = MSTDEFECECT.DEFECTID
							LEFT JOIN TP_MST_DEFECTTYPE MSTDEFECECTTYPE ON MSTDEFECECT.DEFECTTYPEID = MSTDEFECECTTYPE.DEFECTTYPEID
						WHERE
							 DEFECT.CREATETIME >= trunc(sysdate) 
							--DEFECT.CREATETIME >= date'2022-08-12'
							--AND DEFECT.CREATETIME < date'2022-08-13'
							AND PD.VALUEFLAG = '1' --有效
							AND DEFECT.VALUEFLAG = '1' --有效
							AND PD.ISREFIRE = 0 --非重烧
							AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
							AND GDT.GOODSTYPEID IN (4,6)
							AND DEFECTUSERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D27','D29','D11','D20','D21','D22','D25','D26','D28','D30','D32','D34','D35','D38','D99','D33','D36','D37','D39','D40','D88','D51','D50') 
							AND PD.PROCEDUREID IN ( 49, 10 ) 
							AND PD.GOODSLEVELTYPEID=7--次品
							AND PD.ACCOUNTID=1 
							AND PD.CHECKBATCHNO=1
						GROUP BY
							TO_CHAR( DEFECT.CREATETIME, 'yyyy-mm-dd' )
					),
					 数量 AS 
						(
							SELECT
								TO_CHAR( PD.CREATETIME, 'yyyy-mm-dd' ) 时间,
								SUM( CASE WHEN DEFECT.DEFECTCODE IN ( '62' ) THEN 1 ELSE 0 END ) 上水,
								SUM( CASE WHEN DEFECT.DEFECTCODE IN ( '63','64','65' ) THEN 1 ELSE 0 END ) 吹风
							FROM
								TP_PM_DEFECT DEFECT
								LEFT JOIN TP_PM_PRODUCTIONDATA PD ON DEFECT.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
								LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
								LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
							WHERE
						 		 DEFECT.CREATETIME >= trunc(sysdate) 
								AND DEFECTUSERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D22','D27','D29','D11','D20','D21','D25','D26','D28','D30','D32','D34','D35','D38','D99','E4','E5','E9','E10','D33','D36','D37','D39','D40','D88','D51','D50') 
								AND PD.VALUEFLAG = '1' --有效
								AND DEFECT.VALUEFLAG = '1' --有效
								AND PD.ISREFIRE = 0 --非重烧
								AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
								AND GDT.GOODSTYPEID IN (4,6)
							GROUP BY
								TO_CHAR( PD.CREATETIME, 'yyyy-mm-dd' ) 
						),
						检验数 AS (
							SELECT
								COUNT( * ) 成检交接数量,
								TO_CHAR( PD.CREATETIME, 'yyyy-mm-dd' ) 时间 
							FROM
								TP_PM_PRODUCTIONDATA PD
								LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
								LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
								INNER JOIN TP_PM_PRODUCTIONDATA PPD ON PD.BARCODE = PPD.BARCODE
							WHERE
								PD.VALUEFLAG = '1' 
						 		AND  PD.CREATETIME >= trunc(sysdate) 
								AND PD.PROCEDUREID IN ( 48,9 )
								AND PPD.USERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D22','D27','D29','D11','D20','D21','D25','D26','D28','D30','D32','D34','D35','D38','D99','D33','D36','D37','D39','D40','D88','D51','D50') 
							GROUP BY
								TO_CHAR( PD.CREATETIME, 'yyyy-mm-dd' ) 
						),
						工序缺陷数量 AS (
						SELECT
							TO_CHAR( DEFECT.CREATETIME, 'yyyy-mm-dd' ) 时间,
							SUM(CASE WHEN DEFECT.DEFECTCODE IN ( '62', '64', '63' ) THEN 1 ELSE 0 END) 二检数量
						FROM
							TP_PM_DEFECT DEFECT
							LEFT JOIN TP_PM_PRODUCTIONDATA PD ON DEFECT.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
							LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
							LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
							LEFT JOIN TP_MST_DEFECT MSTDEFECECT ON DEFECT.DEFECTID = MSTDEFECECT.DEFECTID
							LEFT JOIN TP_MST_DEFECTTYPE MSTDEFECECTTYPE ON MSTDEFECECT.DEFECTTYPEID = MSTDEFECECTTYPE.DEFECTTYPEID
						WHERE
							 DEFECT.CREATETIME >= trunc(sysdate) 
							--DEFECT.CREATETIME >= date'2022-08-12'
							--AND DEFECT.CREATETIME < date'2022-08-13'
							AND DEFECTUSERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D22','D27','D29','D11','D20','D21','D25','D26','D28','D30','D32','D34','D35','D38','D99','D33','D36','D37','D39','D40','D88','D51','D50') 
							AND PD.VALUEFLAG = '1' --有效
							AND DEFECT.VALUEFLAG = '1' --有效
						GROUP BY
							TO_CHAR( DEFECT.CREATETIME, 'yyyy-mm-dd' )
						),
						前三部位 AS (
							SELECT
								TT9.上水,
								TT10.吹风
							FROM 
							--二检上水
							(
								SELECT
									LISTAGG ( T9.前三部位 ) within GROUP ( ORDER BY T9.数量 DESC ) 上水 
								FROM
								(
									SELECT
										T.位置 || '(' || T.数量 || ')' AS 前三部位,
										T.数量
									FROM
									(
										SELECT
											TMDPI.DEFECTPOSITIONNAME AS 位置,
											COUNT( * ) AS 数量 
										FROM
											TP_PM_DEFECT TMDP
											LEFT JOIN TP_MST_DEFECTPOSITION TMDPI ON TMDP.DEFECTPOSITIONID = TMDPI.DEFECTPOSITIONID
											LEFT JOIN TP_PM_PRODUCTIONDATA PD ON TMDP.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
											LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
											LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
										WHERE
											 TMDP.CREATETIME >= trunc(sysdate) 
											AND DEFECTUSERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D22','D27','D29','D11','D20','D21','D25','D26','D28','D30','D32','D34','D35','D38','D99','D33','D36','D37','D39','D40','D88','D51','D50') 
											--TMDP.CREATETIME >= DATE'2022-08-12'
											--AND TMDP.CREATETIME < DATE'2022-08-13'
											AND TMDP.DEFECTCODE IN ( '62' )
											AND PD.ISREFIRE = 0 --非重烧
											AND PD.VALUEFLAG = '1' --有效
											AND TMDP.VALUEFLAG = '1' --有效
											AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
											AND GDT.GOODSTYPEID IN (4,6)
										GROUP BY
											TMDPI.DEFECTPOSITIONID,
											TMDPI.DEFECTPOSITIONNAME 
										ORDER BY
											COUNT( * ) DESC 
									) T 
									WHERE
									ROWNUM <= 3 
								) T9 
							) TT9 
							LEFT JOIN 
							--二检吹风
							(
								SELECT
									LISTAGG ( T10.前三部位 ) within GROUP ( ORDER BY T10.数量 DESC ) 吹风 
								FROM
								(
									SELECT
										T.位置 || '(' || T.数量 || ')' AS 前三部位,
										T.数量
									FROM
									(
										SELECT
											TMDPI.DEFECTPOSITIONNAME AS 位置,
											COUNT( * ) AS 数量 
										FROM
											TP_PM_DEFECT TMDP
											LEFT JOIN TP_MST_DEFECTPOSITION TMDPI ON TMDP.DEFECTPOSITIONID = TMDPI.DEFECTPOSITIONID
											LEFT JOIN TP_PM_PRODUCTIONDATA PD ON TMDP.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
											LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
											LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
										WHERE
											 TMDP.CREATETIME >= trunc(sysdate) 
											--TMDP.CREATETIME >= DATE'2022-08-12'
											--AND TMDP.CREATETIME < DATE'2022-08-13'
											AND DEFECTUSERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D22','D27','D29','D11','D20','D21','D25','D26','D28','D30','D32','D34','D35','D38','D99','D33','D36','D37','D39','D40','D88','D51','D50') 
											AND TMDP.DEFECTCODE IN ( '63', '64' )
											AND PD.ISREFIRE = 0 --非重烧
											AND PD.VALUEFLAG = '1' --有效
											AND TMDP.VALUEFLAG = '1' --有效
											AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
											AND GDT.GOODSTYPEID IN (4,6)
										GROUP BY
											TMDPI.DEFECTPOSITIONID,
											TMDPI.DEFECTPOSITIONNAME 
										ORDER BY
											COUNT( * ) DESC 
									) T 
									WHERE
									ROWNUM <= 3 
								) T10 
							) TT10 ON 1 = 1
						
						)
						SELECT

							TO_CHAR( 数量.上水 ) 上水,
							TO_CHAR( 数量.吹风 ) 吹风
						FROM
							数量 
						UNION ALL
						SELECT			
							DECODE( NVL( 数量.上水, 0 ), 0, '0%', TO_CHAR( ( NVL( 数量.上水, 0 ) / DECODE( NVL( 检验数.成检交接数量, 1 ), 0, 1, NVL( 检验数.成检交接数量, 1 ) ) ) * 100, 'fm99990.0' ) || '%' ) ,
							DECODE( NVL( 数量.吹风, 0 ), 0, '0%', TO_CHAR( ( NVL( 数量.吹风, 0 ) / DECODE( NVL( 检验数.成检交接数量, 1 ), 0, 1, NVL( 检验数.成检交接数量, 1 ) ) ) * 100, 'fm99990.0' ) || '%' ) 
						FROM
							数量
						INNER JOIN 检验数 ON 数量.时间 = 检验数.时间
						UNION ALL
						SELECT
					
							NVL(前三部位.上水,'-'),
							NVL(前三部位.吹风,'-')
						FROM
							前三部位
						UNION ALL
						SELECT
							'',
							--DECODE(检验数.成检交接数量, 0, '0%', TO_CHAR( ROUND( (数量.上水 + 数量.吹风) / 检验数.成检交接数量, 4 ) * 100.00, 'FM900.09' ) || '%') ,
							DECODE( NVL( 工序缺陷数量.二检数量, 0 ), 0, '0%', TO_CHAR( ( NVL( 工序缺陷数量.二检数量, 0 ) / DECODE( NVL( 检验数.成检交接数量, 1 ), 0, 1, NVL( 检验数.成检交接数量, 1 ) ) ) * 100, 'fm99990.00' ) || '%' ) 
						FROM
							工序缺陷数量
						INNER JOIN 检验数 ON 工序缺陷数量.时间 = 检验数.时间
						UNION ALL
						SELECT
							'',
							--DECODE(检验数.成检交接数量, 0, '0%', TO_CHAR( ROUND( (数量.上水 + 数量.吹风) / 检验数.成检交接数量, 4 ) * 100.00, 'FM900.09' ) || '%') ,
							DECODE( NVL( 工序废品数.二检数量, 0 ), 0, '0%', TO_CHAR( ( NVL( 工序废品数.二检数量, 0 ) / DECODE( NVL( 检验数.成检交接数量, 1 ), 0, 1, NVL( 检验数.成检交接数量, 1 ) ) ) * 100, 'fm99990.00' ) || '%' ) 
						FROM
							工序废品数
						INNER JOIN 检验数 ON 工序废品数.时间 = 检验数.时间
				";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                int sumjs2 = (Convert.ToInt32( dt.Rows[0]["上水"])+Convert.ToInt32( dt.Rows[0]["吹风"]));
                string sqlStr2 = @"SELECT
								COUNT( DISTINCT PD.BARCODE ) 成检交接数量,
								TO_CHAR( PD.CREATETIME, 'yyyy-mm-dd' ) 时间 
							FROM
								TP_PM_PRODUCTIONDATA PD
								LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
								LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
								INNER JOIN TP_PM_PRODUCTIONDATA PPD ON PD.BARCODE = PPD.BARCODE
							WHERE
								PD.VALUEFLAG = '1' 
						 		AND  PD.CREATETIME >= trunc(sysdate) 
								AND PD.PROCEDUREID IN ( 48,9 )
								AND PPD.USERCODE IN ('D12','D13','D14','D15','D16','D17','D18','D19','D27','D29','D11','D20','D21','D22','D25','D26','D28','D32','D30','D34','D35','D38','D99','E4','E5','E9','E10','D33','D36','D37','D39','D40','D88','D51','D50') 
								AND PD.ISREFIRE = 0 --非重烧
								AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
								AND GDT.GOODSTYPEID IN (4,6)
							GROUP BY
								TO_CHAR( PD.CREATETIME, 'yyyy-mm-dd' )";
                object DSUM = conn.ExecuteScalar(sqlStr2, null);
                double sumjs = Convert.ToInt32(DSUM);
                double hgsum = (sumjs - sumjs2);
                double HGL = (hgsum/sumjs)*100;
                JObject json = new JObject(
                      new JProperty("success", true),
                      new JProperty("sumhgl", Convert.ToDouble(HGL))
                    );
                context.Response.Write(json.ToString());
            }

            //一期合格率
            if (context.Request["m"].ToString() == "HGL1") {
                string sqlStr = @"
					SELECT
						SUM( CASE WHEN DEFECT.DEFECTCODE IN ( '62', '64', '63' ) THEN 1 ELSE 0 END ) 二检缺陷数量,
						TO_CHAR( DEFECT.CREATETIME, 'yyyy-mm-dd' ) 时间 
					FROM
						TP_PM_DEFECT DEFECT
						LEFT JOIN TP_PM_PRODUCTIONDATA PD ON DEFECT.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
						LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
						LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
					WHERE
						 DEFECT.CREATETIME >= trunc( SYSDATE ) 
						AND DEFECT.DEFECTUSERCODE IN ( 'D12', 'D13', 'D14', 'D15', 'D16', 'D17', 'D18', 'D19','D22', 'D27', 'D29','D30', 'D99','D39','D40','D88','D51' ) 
						AND PD.VALUEFLAG = '1' --有效
	
						AND DEFECT.VALUEFLAG = '1' --有效
	
					GROUP BY
						TO_CHAR( DEFECT.CREATETIME, 'yyyy-mm-dd' ) 
				";
                //直接获取不分页数据
                object QSUM = conn.ExecuteScalar(sqlStr);
                double qsum = Convert.ToInt32(QSUM);
                string sqlStr2 = @"	SELECT
										COUNT( * ) 成检交接数量,
										TO_CHAR( PD.CREATETIME, 'yyyy-mm-dd' ) 时间 
									FROM
										TP_PM_PRODUCTIONDATA PD
										LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
										LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID
										LEFT JOIN TP_PM_PRODUCTIONDATA PPD ON PD.BARCODE = PPD.BARCODE 
									WHERE
										PD.VALUEFLAG = '1' 
										AND  PD.CREATETIME >= trunc( SYSDATE ) 
										AND PD.PROCEDUREID IN ( 48, 9 ) 
										AND PPD.USERCODE IN ( 'D12', 'D13', 'D14', 'D15', 'D16', 'D17', 'D18', 'D19','D22','D27', 'D29', 'D30','D99','D39','D40','D88','D51' ) 
										AND PD.ISREFIRE = 0 --非重烧
										AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
										AND GDT.GOODSTYPEID IN ( 4, 6 ) 
									GROUP BY
										TO_CHAR( PD.CREATETIME, 'yyyy-mm-dd' )";
                object DSUM = conn.ExecuteScalar(sqlStr2, null);
                double dsum = Convert.ToInt32(DSUM);
                double zsum = dsum - qsum;
                double HGL = (zsum/dsum)*100;
                JObject json = new JObject(
                      new JProperty("success", true),
                      new JProperty("sumhgl", Convert.ToDouble(HGL))
                    );
                context.Response.Write(json.ToString());
            }

            //二期合格率
            if (context.Request["m"].ToString() == "HGL2") {
                string sqlStr = @"
					SELECT
						SUM( CASE WHEN DEFECT.DEFECTCODE IN ( '62', '64', '63' ) THEN 1 ELSE 0 END ) 二检缺陷数量,
						TO_CHAR( DEFECT.CREATETIME, 'yyyy-mm-dd' ) 时间 
					FROM
						TP_PM_DEFECT DEFECT
						LEFT JOIN TP_PM_PRODUCTIONDATA PD ON DEFECT.PRODUCTIONDATAID = PD.PRODUCTIONDATAID
						LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
						LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID 
					WHERE
						 DEFECT.CREATETIME >= trunc( SYSDATE ) 
						AND DEFECT.DEFECTUSERCODE IN ( 'D11', 'D20', 'D21', 'D25', 'D26', 'D28', 'D32', 'D34', 'D35', 'D38','D33','D36','D37','D50' ) 
						AND PD.VALUEFLAG = '1' --有效
	
						AND DEFECT.VALUEFLAG = '1' --有效
	
					GROUP BY
						TO_CHAR( DEFECT.CREATETIME, 'yyyy-mm-dd' )
				";
                //直接获取不分页数据
                object QSUM = conn.ExecuteScalar(sqlStr, null);
                double qsum =  Convert.ToInt32(QSUM);
                string sqlStr2 = @"SELECT
										COUNT( * ) 成检交接数量,
										TO_CHAR( PD.CREATETIME, 'yyyy-mm-dd' ) 时间 
									FROM
										TP_PM_PRODUCTIONDATA PD
										LEFT JOIN TP_MST_GOODS GD ON PD.GOODSID = GD.GOODSID
										LEFT JOIN TP_MST_GOODSTYPE GDT ON GD.GOODSTYPEID = GDT.GOODSTYPEID
										LEFT JOIN TP_PM_PRODUCTIONDATA PPD ON PD.BARCODE = PPD.BARCODE 
									WHERE
										PD.VALUEFLAG = '1' 
										AND  PD.CREATETIME >= trunc( SYSDATE ) 
										AND PD.PROCEDUREID IN ( 48, 9 ) 
										AND PPD.USERCODE IN ('D11','D20','D21','D25','D26','D28','D32','D34','D35','D38','D33','D36','D37','D50')
										AND PD.ISREFIRE = 0 --非重烧
										AND SUBSTR( GDT.GOODSTYPECODE, 0, 6 ) = '001001' --大件
										AND GDT.GOODSTYPEID IN ( 4, 6 ) 
									GROUP BY
										TO_CHAR( PD.CREATETIME, 'yyyy-mm-dd' )";
                object DSUM = conn.ExecuteScalar(sqlStr2, null);
                double dsum = Convert.ToInt32(DSUM);
                double zsum = dsum - qsum;
                double HGL = (zsum/dsum)*100;
                JObject json = new JObject(
                      new JProperty("success", true),
                      new JProperty("sumhgl", Convert.ToDouble(HGL))
                    );
                context.Response.Write(json.ToString());
            }

            //服务器时间
            if (context.Request["m"].ToString() == "fwq")
            {
                string time = DateTime.Now.ToString("yyyy年MM月dd日 HH:mm ");
                Dictionary<string, string> d = new Dictionary<string, string>();
                JObject json = new JObject(
                          new JProperty("success", true),
                          new JProperty("sj", time)
                        );
                context.Response.Write(json.ToString());
            }
            #endregion

            #region 样式二 sql

            if (context.Request["m"].ToString() == "SumCrack") {
                string sqlStr = @"SELECT
												COUNT(DISTINCT DF.BARCODE) 一线
												FROM TP_PM_DEFECT df
												LEFT JOIN TP_PM_PRODUCTIONDATA pd on pd.BARCODE =df.BARCODE
												INNER  JOIN  TP_PM_PRODUCTIONDATA pda on pda.BARCODE = pd.BARCODE
												AND pda.USERCODE in ('D12','D13','D14','D15','D16','D22','D17','D18','D19','D27','D29','D30','D99','D39','D40','D88','D51')
												WHERE 
												  df.CREATETIME >= trunc(sysdate) 
												AND df.VALUEFLAG=1
												AND PD.PROCEDUREID IN ( 48,9 )
												AND PD.VALUEFLAG=1
												AND df.DEFECTCODE='1' AND df.DEFECTPOSITIONCODE IN('1','2','3','6','8')
												UNION ALL

												SELECT
												COUNT(DISTINCT DF.BARCODE) 二线
												FROM TP_PM_DEFECT df
												LEFT JOIN TP_PM_PRODUCTIONDATA pd on pd.BARCODE =df.BARCODE
												INNER JOIN  TP_PM_PRODUCTIONDATA pda on pda.BARCODE = pd.BARCODE
												AND pda.USERCODE in ('D11','D20','D21','D25','D26','D28','D32','D34','D35','D38','D33','D36','D37','D50')
												WHERE 
												  df.CREATETIME >= trunc(sysdate) 
												 AND PD.PROCEDUREID IN ( 48,9 )
												AND df.VALUEFLAG=1
												AND PD.VALUEFLAG=1
												AND df.DEFECTCODE='1' AND df.DEFECTPOSITIONCODE IN('1','2','3','6','8')
												UNION ALL
												SELECT
												COUNT(DISTINCT DF.BARCODE) 总计
												FROM TP_PM_DEFECT df
												LEFT JOIN TP_PM_PRODUCTIONDATA pd on pd.BARCODE =df.BARCODE
												INNER  JOIN  TP_PM_PRODUCTIONDATA pda on pda.BARCODE = pd.BARCODE
												AND pda.USERCODE in ('D12','D13','D14','D15','D16','D22','D17','D18','D19','D27','D29','D30','D99','D11','D20','D21','D25','D26','D28','D32','D34','D35','D38','D33','D36','D37','D39','D40','D88','D51','D50')
												WHERE 
												  df.CREATETIME >= trunc(sysdate) 
												AND df.VALUEFLAG=1
												AND PD.PROCEDUREID IN ( 48,9 )
												AND PD.VALUEFLAG=1
												AND df.DEFECTCODE='1' AND df.DEFECTPOSITIONCODE IN('1','2','3','6','8')";
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                context.Response.Write(new JsonResult(dt).ToJson());
            }


            if (context.Request["m"].ToString() == "firstTable") {
                string sqlStr = @"SELECT
											code,
											LISTAGG ( 缺陷 ) within GROUP ( ORDER BY 一线 DESC ) 位置统计,
											SUM(一线) 一线产量
											FROM(
											SELECT
											code,
											缺陷位置 || '(' || 一线|| ')' AS 缺陷,
											一线
											FROM(

										SELECT								
												pda.USERCODE code,
												df.DEFECTPOSITIONNAME 缺陷位置,
												COUNT(DISTINCT DF.BARCODE) 一线
												FROM TP_PM_DEFECT df
												LEFT JOIN TP_PM_PRODUCTIONDATA pd on pd.BARCODE =df.BARCODE
												INNER  JOIN  TP_PM_PRODUCTIONDATA pda on pda.BARCODE = pd.BARCODE
												AND pda.USERCODE in ('D12','D13','D14','D15','D16','D22','D17','D18','D19','D27','D29','D30','D99','D39','D40','D88','D51')
												WHERE 
												  df.CREATETIME >= trunc(sysdate) 
												AND df.VALUEFLAG=1
												AND PD.PROCEDUREID IN ( 48,9 )
												AND PD.VALUEFLAG=1
												
												AND df.DEFECTCODE='1' AND df.DEFECTPOSITIONCODE IN('1','2','3','6','8')
												GROUP BY pda.USERCODE,
												df.DEFECTPOSITIONNAME
												)
												)
												GROUP BY code ";
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                context.Response.Write(new JsonResult(dt).ToJson());
            }
            if (context.Request["m"].ToString() == "SecondTable") {
                string sqlStr = @"SELECT
									code,
									LISTAGG ( 缺陷 ) within GROUP ( ORDER BY 二线 DESC ) 位置统计,
									SUM(二线) 二线产量
									FROM(
									SELECT
									code,
									缺陷位置 || '(' || 二线|| ')' AS 缺陷,
									二线
									FROM(

									SELECT									pda.USERCODE code,
												df.DEFECTPOSITIONNAME 缺陷位置,
												COUNT(DISTINCT DF.BARCODE) 二线
												FROM TP_PM_DEFECT df
												LEFT JOIN TP_PM_PRODUCTIONDATA pd on pd.BARCODE =df.BARCODE
												INNER  JOIN  TP_PM_PRODUCTIONDATA pda on pda.BARCODE = pd.BARCODE
												AND pda.USERCODE in ('D11','D20','D21','D25','D26','D28','D32','D34','D35','D38','D33','D36','D37','D50')
												WHERE 
												  df.CREATETIME >= trunc(sysdate) 
												AND df.VALUEFLAG=1
												AND PD.PROCEDUREID IN ( 48,9 )
												AND PD.VALUEFLAG=1
												AND df.DEFECTCODE='1' AND df.DEFECTPOSITIONCODE IN('1','2','3','6','8')

												GROUP BY pda.USERCODE,
												df.DEFECTPOSITIONNAME
												)
												)
												GROUP BY code
												";
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                context.Response.Write(new JsonResult(dt).ToJson());
            }
            #endregion
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}