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

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

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 SecondaryCheckTwo : IHttpHandler, IRequiresSessionState
{
    public void ProcessRequest(HttpContext context)
    {
        using (IDataAccess conn = DataAccess.Create())
        {
            #region 样式二 sql

			        //总产量
            if (context.Request["m"].ToString() == "sumjs") {
                string sqlStr = @"	SELECT COUNT(*) 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') AND TRUNC(CREATETIME ) = trunc(sysdate) 
												AND  ACCOUNTID=1 AND VALUEFLAG=1 AND PROCEDUREID = 35)";
                object JS2SUM = conn.ExecuteScalar(sqlStr, null);
                JObject json = new JObject(
                      new JProperty("success", true),
                      new JProperty("sumjs", Convert.ToInt32(JS2SUM))
                    );
                context.Response.Write(json.ToString());
            }
            //合格率
            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
							TRUNC( 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')
							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
						 		TRUNC( 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')
								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 TRUNC( 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')
							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
							TRUNC( 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')
							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
											TRUNC( 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')
											--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
											TRUNC( 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')
											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( * ) 成检交接数量,
								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 TRUNC( 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','D25','D26','D28','D32','D30','D34','D35','D38','D99')
								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() == "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());
            }

			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')
												WHERE 
												 TRUNC( 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')
												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')
												WHERE 
												 TRUNC( 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')
												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
												WHERE 
												 TRUNC( 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')";
                               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')
												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')

												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;
        }
    }
}