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 'Z24' usercode
                      ,0 num
                      ,MAX((SELECT COUNT(DISTINCT barcode)
                             FROM tp_pm_productiondata tppd
                            WHERE tppd.procedureid IN (9, 15)
                              AND tppd.usercode = 'Z24'
                              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 = 'Z24'
                           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 ('Z24','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 ('Z24','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;
        }
    }

}