HEGII.gt.mes.dashboard/wwwroot/main/Procedure/test13/GradingData.ashx

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

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

public class SmallKanban : IHttpHandler {

    public void ProcessRequest (HttpContext context) {
        using (IDataAccess conn = DataAccess.Create())
        {
            //服务器时间
            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() == "分级数据")
            {
                string sqlStr = @"
									SELECT
TT2.USERCODE,
TT2.countj,
NVL(TT2.countcg,0) countcg,
NVL(TT2.countyz,0) countyz,
NVL(TT2.countlj,0) countlj,
TT2.lv
FROM(
SELECT
									TT.USERCODE,
									TT.NUM,
									TT.countj,
									TT.countcg,
									TT.countyz,
									TT.countlj,
									CASE WHEN TT.countlj = 0 THEN
                    '0%'
                  ELSE
                    DECODE( NVL( TT.countlj, 0 ), 0, '0%', TO_CHAR( ( NVL( TT.countlj, 0 ) / DECODE( NVL( TT.countj, 1 ), 0, 1, NVL( TT.countj, 1 ) ) ) * 100, 'fm99990.00' ) || '%' )end lv
									FROM(
SELECT
'Z16' usercode,
0 num,
MAX((SELECT COUNT(DISTINCT BARCODE)  FROM TP_PM_PRODUCTIONDATA TPPD  WHERE TPPD.PROCEDUREID IN (9, 15)  AND TPPD.USERCODE = 'Z16' AND TPPD.CREATETIME >= TRUNC( SYSDATE ) and tppd.valueflag = 1) ) AS countj,
sum(T.countcg) countcg,
sum(T.countyz) countyz,
sum(T.countlj) countlj
FROM(
SELECT
	TPPD1.BARCODE countj,
	CASE WHEN TPD.DEFECTCODE = 0148 THEN 1 ELSE 0 END countcg,
	CASE WHEN TPD.DEFECTCODE = 0149 THEN 1 ELSE 0 END countyz,
	CASE WHEN TPD.DEFECTCODE IN (0148,0149) THEN 1 ELSE 0 END countlj
FROM
	TP_PM_PRODUCTIONDATA TPPD
	INNER JOIN (
	SELECT
				DISTINCT BARCODE 
	FROM
		TP_PM_PRODUCTIONDATA TPPD 
	WHERE
		TPPD.PROCEDUREID IN ( 9, 15,10,16 ) 
		--AND TPPD.USERCODE = 'Z16' 
		AND TPPD.CREATETIME >= TRUNC( SYSDATE ) 
	) TPPD1 ON TPPD.BARCODE = TPPD1.BARCODE
	LEFT JOIN TP_PM_DEFECT TPD ON TPD.PRODUCTIONDATAID = TPPD.PRODUCTIONDATAID AND TPD.DEFECTUSERCODE = 'Z16' AND TPD.VALUEFLAG = 1
WHERE
	TPPD.UPDATETIME >= TRUNC( SYSDATE ) 
	AND TPPD.VALUEFLAG = 1 
	AND TPPD.PROCEDUREID IN ( 10, 16 )) T
	UNION ALL
	SELECT
'Z17' usercode,
0 num,
MAX((SELECT COUNT(DISTINCT BARCODE)  FROM TP_PM_PRODUCTIONDATA TPPD  WHERE TPPD.PROCEDUREID IN (9, 15)  AND TPPD.USERCODE = 'Z17' AND TPPD.CREATETIME >= TRUNC( SYSDATE ) and tppd.valueflag = 1) ) AS countj,
sum(T.countcg),
sum(T.countyz),
sum(T.countlj)
FROM(
SELECT
	TPPD1.BARCODE countj,
	CASE WHEN TPD.DEFECTCODE = 0148 THEN 1 ELSE 0 END countcg,
	CASE WHEN TPD.DEFECTCODE = 0149 THEN 1 ELSE 0 END countyz,
	CASE WHEN TPD.DEFECTCODE IN (0148,0149) THEN 1 ELSE 0 END countlj
FROM
	TP_PM_PRODUCTIONDATA TPPD
	INNER JOIN (
	SELECT
		DISTINCT BARCODE 
	FROM
		TP_PM_PRODUCTIONDATA TPPD 
	WHERE
		TPPD.PROCEDUREID IN ( 9, 15,10,16 ) 
		--AND TPPD.USERCODE = 'Z17' 
		AND TPPD.CREATETIME >= TRUNC( SYSDATE ) 
	) TPPD1 ON TPPD.BARCODE = TPPD1.BARCODE
	LEFT JOIN TP_PM_DEFECT TPD ON TPD.PRODUCTIONDATAID = TPPD.PRODUCTIONDATAID AND TPD.DEFECTUSERCODE = 'Z17' AND TPD.VALUEFLAG = 1
WHERE
	TPPD.UPDATETIME >= TRUNC( SYSDATE ) 
	AND TPPD.VALUEFLAG = 1 
	AND TPPD.PROCEDUREID IN ( 10, 16 )) T
	UNION ALL
	SELECT
'Z18' usercode,
0 num,
MAX((SELECT COUNT(DISTINCT BARCODE)  FROM TP_PM_PRODUCTIONDATA TPPD  WHERE TPPD.PROCEDUREID IN (9, 15)  AND TPPD.USERCODE = 'Z18' AND TPPD.CREATETIME >= TRUNC( SYSDATE ) and tppd.valueflag = 1) ) AS countj,
sum(T.countcg),
sum(T.countyz),
sum(T.countlj)
FROM(
SELECT
	TPPD1.BARCODE countj,
	CASE WHEN TPD.DEFECTCODE = 0148 THEN 1 ELSE 0 END countcg,
	CASE WHEN TPD.DEFECTCODE = 0149 THEN 1 ELSE 0 END countyz,
	CASE WHEN TPD.DEFECTCODE IN (0148,0149) THEN 1 ELSE 0 END countlj
FROM
	TP_PM_PRODUCTIONDATA TPPD
	INNER JOIN (
	SELECT
			DISTINCT BARCODE 
	FROM
		TP_PM_PRODUCTIONDATA TPPD 
	WHERE
		TPPD.PROCEDUREID IN ( 9, 15,10,16 ) 
		--AND TPPD.USERCODE = 'Z18' 
		AND TPPD.CREATETIME >= TRUNC( SYSDATE ) 
	) TPPD1 ON TPPD.BARCODE = TPPD1.BARCODE
	LEFT JOIN TP_PM_DEFECT TPD ON TPD.PRODUCTIONDATAID = TPPD.PRODUCTIONDATAID AND TPD.DEFECTUSERCODE = 'Z18' AND TPD.VALUEFLAG = 1
WHERE
	TPPD.UPDATETIME >= TRUNC( SYSDATE ) 
	AND TPPD.VALUEFLAG = 1 
	AND TPPD.PROCEDUREID IN ( 10, 16 )) T
	UNION ALL
SELECT
'Z19' usercode,
0 num,
MAX((SELECT COUNT(DISTINCT BARCODE)  FROM TP_PM_PRODUCTIONDATA TPPD  WHERE TPPD.PROCEDUREID IN (9, 15)  AND TPPD.USERCODE = 'Z19' AND TPPD.CREATETIME >= TRUNC( SYSDATE ) and tppd.valueflag = 1) ) AS countj,
sum(T.countcg),
sum(T.countyz),
sum(T.countlj)
FROM(
SELECT
	TPPD1.BARCODE countj,
	CASE WHEN TPD.DEFECTCODE = 0148 THEN 1 ELSE 0 END countcg,
	CASE WHEN TPD.DEFECTCODE = 0149 THEN 1 ELSE 0 END countyz,
	CASE WHEN TPD.DEFECTCODE IN (0148,0149) THEN 1 ELSE 0 END countlj
FROM
	TP_PM_PRODUCTIONDATA TPPD
	INNER JOIN (
	SELECT
				DISTINCT BARCODE 
	FROM
		TP_PM_PRODUCTIONDATA TPPD 
	WHERE
		TPPD.PROCEDUREID IN ( 9, 15,10,16 ) 
		--AND TPPD.USERCODE = 'Z19' 
		AND TPPD.CREATETIME >= TRUNC( SYSDATE ) 
	) TPPD1 ON TPPD.BARCODE = TPPD1.BARCODE
	LEFT JOIN TP_PM_DEFECT TPD ON TPD.PRODUCTIONDATAID = TPPD.PRODUCTIONDATAID AND TPD.DEFECTUSERCODE = 'Z19' AND TPD.VALUEFLAG = 1
WHERE
	TPPD.UPDATETIME >= TRUNC( SYSDATE ) 
	AND TPPD.VALUEFLAG = 1 
	AND TPPD.PROCEDUREID IN ( 10, 16 )) T
	UNION ALL
SELECT
'Z20' usercode,
0 num,
MAX((SELECT COUNT(DISTINCT BARCODE)  FROM TP_PM_PRODUCTIONDATA TPPD  WHERE TPPD.PROCEDUREID IN (9, 15)  AND TPPD.USERCODE = 'Z20' AND TPPD.CREATETIME >= TRUNC( SYSDATE ) and tppd.valueflag = 1) ) AS countj,
sum(T.countcg),
sum(T.countyz),
sum(T.countlj)
FROM(
SELECT
	TPPD1.BARCODE countj,
	CASE WHEN TPD.DEFECTCODE = 0148 THEN 1 ELSE 0 END countcg,
	CASE WHEN TPD.DEFECTCODE = 0149 THEN 1 ELSE 0 END countyz,
	CASE WHEN TPD.DEFECTCODE IN (0148,0149) THEN 1 ELSE 0 END countlj
FROM
	TP_PM_PRODUCTIONDATA TPPD
	INNER JOIN (
	SELECT
		DISTINCT BARCODE 
	FROM
		TP_PM_PRODUCTIONDATA TPPD 
	WHERE
		TPPD.PROCEDUREID IN ( 9, 15,10,16 ) 
		--AND TPPD.USERCODE = 'Z20' 
		AND TPPD.CREATETIME >= TRUNC( SYSDATE ) 
	) TPPD1 ON TPPD.BARCODE = TPPD1.BARCODE
	LEFT JOIN TP_PM_DEFECT TPD ON TPD.PRODUCTIONDATAID = TPPD.PRODUCTIONDATAID AND TPD.DEFECTUSERCODE = 'Z20' AND TPD.VALUEFLAG = 1
WHERE
	TPPD.UPDATETIME >= TRUNC( SYSDATE ) 
	AND TPPD.VALUEFLAG = 1 
	AND TPPD.PROCEDUREID IN ( 10, 16 )) T
	UNION ALL
SELECT
'Z21' usercode,
0 num,
MAX((SELECT COUNT(DISTINCT BARCODE)  FROM TP_PM_PRODUCTIONDATA TPPD  WHERE TPPD.PROCEDUREID IN (9, 15)  AND TPPD.USERCODE = 'Z21' AND TPPD.CREATETIME >= TRUNC( SYSDATE ) and tppd.valueflag = 1) ) AS countj,
sum(T.countcg),
sum(T.countyz),
sum(T.countlj)
FROM(
SELECT
	TPPD1.BARCODE countj,
	CASE WHEN TPD.DEFECTCODE = 0148 THEN 1 ELSE 0 END countcg,
	CASE WHEN TPD.DEFECTCODE = 0149 THEN 1 ELSE 0 END countyz,
	CASE WHEN TPD.DEFECTCODE IN (0148,0149) THEN 1 ELSE 0 END countlj
FROM
	TP_PM_PRODUCTIONDATA TPPD
	INNER JOIN (
	SELECT
				DISTINCT BARCODE 
	FROM
		TP_PM_PRODUCTIONDATA TPPD 
	WHERE
		TPPD.PROCEDUREID IN ( 9, 15,10,16 ) 
		--AND TPPD.USERCODE = 'Z21' 
		AND TPPD.CREATETIME >= TRUNC( SYSDATE ) 
	) TPPD1 ON TPPD.BARCODE = TPPD1.BARCODE
	LEFT JOIN TP_PM_DEFECT TPD ON TPD.PRODUCTIONDATAID = TPPD.PRODUCTIONDATAID AND TPD.DEFECTUSERCODE = 'Z21' AND TPD.VALUEFLAG = 1
WHERE
	TPPD.UPDATETIME >= TRUNC( SYSDATE ) 
	AND TPPD.VALUEFLAG = 1 
	AND TPPD.PROCEDUREID IN ( 10, 16 )) T
	UNION ALL
SELECT
'Z23' usercode,
0 num,
MAX((SELECT COUNT(DISTINCT BARCODE)  FROM TP_PM_PRODUCTIONDATA TPPD  WHERE TPPD.PROCEDUREID IN (9, 15)  AND TPPD.USERCODE = 'Z23' AND TPPD.CREATETIME >= TRUNC( SYSDATE ) and tppd.valueflag = 1) ) AS countj,
sum(T.countcg),
sum(T.countyz),
sum(T.countlj)
FROM(
SELECT
	TPPD1.BARCODE countj,
	CASE WHEN TPD.DEFECTCODE = 0148 THEN 1 ELSE 0 END countcg,
	CASE WHEN TPD.DEFECTCODE = 0149 THEN 1 ELSE 0 END countyz,
	CASE WHEN TPD.DEFECTCODE IN (0148,0149) THEN 1 ELSE 0 END countlj
FROM
	TP_PM_PRODUCTIONDATA TPPD
	INNER JOIN (
	SELECT
				DISTINCT BARCODE 
	FROM
		TP_PM_PRODUCTIONDATA TPPD 
	WHERE
		TPPD.PROCEDUREID IN ( 9, 15,10,16 ) 
		--AND TPPD.USERCODE = 'Z23' 
		AND TPPD.CREATETIME >= TRUNC( SYSDATE ) 
	) TPPD1 ON TPPD.BARCODE = TPPD1.BARCODE
	LEFT JOIN TP_PM_DEFECT TPD ON TPD.PRODUCTIONDATAID = TPPD.PRODUCTIONDATAID AND TPD.DEFECTUSERCODE = 'Z23' AND TPD.VALUEFLAG = 1
WHERE
	TPPD.UPDATETIME >= TRUNC( SYSDATE ) 
	AND TPPD.VALUEFLAG = 1 
	AND TPPD.PROCEDUREID IN ( 10, 16 )) T
	UNION ALL
  SELECT
'汇总' usercode,
1 num,
MAX((SELECT COUNT(DISTINCT BARCODE)  FROM TP_PM_PRODUCTIONDATA TPPD  WHERE TPPD.PROCEDUREID IN (9, 15)  AND TPPD.USERCODE IN ('Z23','Z20','Z21','Z19','Z18','Z17','Z16') 
 AND TPPD.CREATETIME >= TRUNC( SYSDATE ) and tppd.valueflag = 1) ) AS countj,
sum(T.countcg),
sum(T.countyz),
sum(T.countlj)
FROM(
SELECT
	TPPD1.BARCODE countj,
	CASE WHEN TPD.DEFECTCODE = 0148 THEN 1 ELSE 0 END countcg,
	CASE WHEN TPD.DEFECTCODE = 0149 THEN 1 ELSE 0 END countyz,
	CASE WHEN TPD.DEFECTCODE IN (0148,0149) THEN 1 ELSE 0 END countlj
FROM
	TP_PM_PRODUCTIONDATA TPPD
	INNER JOIN (
	SELECT
		DISTINCT BARCODE 
	FROM
		TP_PM_PRODUCTIONDATA TPPD 
	WHERE
		TPPD.PROCEDUREID IN ( 9, 15,10,16 ) 
		--AND TPPD.USERCODE IN ('Z23','Z20','Z21','Z19','Z18','Z17','Z16') 
		AND TPPD.CREATETIME >= TRUNC( SYSDATE ) 
	) TPPD1 ON TPPD.BARCODE = TPPD1.BARCODE
	LEFT JOIN TP_PM_DEFECT TPD ON TPD.PRODUCTIONDATAID = TPPD.PRODUCTIONDATAID AND TPD.DEFECTUSERCODE IN ('Z23','Z21','Z20','Z19','Z18','Z17','Z16') AND TPD.VALUEFLAG = 1
WHERE
	TPPD.UPDATETIME >= TRUNC( SYSDATE ) 
	AND TPPD.VALUEFLAG = 1 
	AND TPPD.PROCEDUREID IN ( 10, 16 )) T
	)TT )TT2 ORDER BY TT2.NUM,TT2.lv ASC";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                context.Response.Write(new JsonResult(dt).ToJson());
            }

            //    if (context.Request["m"].ToString() == "合计")
            //    {
            //        string sqlStr = @"
            //SELECT
            //	'汇总' usercode,
            //	TT.countj,
            //	TT.countcg,
            //	TT.countyz,
            //	TT.countlj,
            //	CASE WHEN TT.countlj = 0 THEN
            //            '0%'
            //          ELSE
            //            DECODE( NVL( TT.countlj, 0 ), 0, '0%', TO_CHAR( ( NVL( TT.countlj, 0 ) / DECODE( NVL( TT.countj, 1 ), 0, 1, NVL( TT.countj, 1 ) ) ) * 100, 'fm99990.00' ) || 	'%' )end lv
            //	FROM(
            //	SELECT
            //	count(T.countj) countj,
            //	sum(T.countcg) countcg,
            //	sum(T.countyz) countyz,
            //	sum(T.countlj) countlj
            //	FROM(
            //	SELECT
            //		TPPD1.BARCODE countj,
            //		CASE WHEN TPD.DEFECTCODE = 0148 THEN 1 ELSE 0 END countcg,
            //		CASE WHEN TPD.DEFECTCODE = 0149 THEN 1 ELSE 0 END countyz,
            //		CASE WHEN TPD.DEFECTCODE IN (0148,0149) THEN 1 ELSE 0 END countlj
            //	FROM
            //		TP_PM_PRODUCTIONDATA TPPD
            //		LEFT JOIN (
            //		SELECT
            //			BARCODE 
            //		FROM
            //			TP_PM_PRODUCTIONDATA TPPD 
            //		WHERE
            //			TPPD.PROCEDUREID IN ( 9, 15 ) 
            //			AND TPPD.USERCODE IN ('Z16','Z17','Z18','Z19','Z20','Z21') 
            //			AND TPPD.CREATETIME >= TRUNC( SYSDATE ) 
            //			AND TPPD.VALUEFLAG = 1 
            //		) TPPD1 ON TPPD.BARCODE = TPPD1.BARCODE
            //		LEFT JOIN TP_PM_DEFECT TPD ON TPD.BARCODE = TPPD.BARCODE AND TPD.DEFECTUSERCODE IN ('Z16','Z17','Z18','Z19','Z20','Z21') 
            //	WHERE
            //		TPPD.UPDATETIME >= TRUNC( SYSDATE ) 
            //		AND TPPD.VALUEFLAG = 1 
            //		AND TPPD.PROCEDUREID IN ( 10, 16 )) T ）TT
            //";
            //        //直接获取不分页数据
            //        DataTable dt = conn.ExecuteDatatable(sqlStr);
            //        context.Response.Write(new JsonResult(dt).ToJson());
            //    }

        }
    }

    public bool IsReusable {
        get {
            return false;
        }
    }

}