HEGII.ss.mes.dashboard/wwwroot/main/Procedure/test2/rpt.ashx

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

using System;
using System.Web;
using System.Web.SessionState;
using System.Data;

using Curtain.DataAccess;
using DK.XuWei.WebMes;

public class rpt : IHttpHandler, IReadOnlySessionState
{
    public void ProcessRequest(HttpContext context)
    {
        context.Response.ContentType = "text/plain";
        using (IDataAccess conn = DataAccess.Create())
        {
            if (context.Request["m"].ToString() == "rate")
            {
                string sqlStr = @"
                SELECT T2.SJ,
                       NVL(T1.NUM1, 0) AS NUM1,
                       NVL(T2.NUM2, 0) AS NUM2
                  FROM (SELECT TO_CHAR(DP.ACCOUNTDATE, 'MM/DD') AS ACCOUNTDATE,
                               SUM(DPD.PLANQUANTITY) AS NUM1
                          FROM TP_PC_DAILYPLAN DP
                          LEFT JOIN TP_PC_DAILYPLANDETAIL DPD
                            ON DP.DAILYPLANID = DPD.DAILYPLANID
                         WHERE DP.VALUEFLAG = 1
                           AND DPD.VALUEFLAG = 1
                           AND DP.ACCOUNTDATE >= TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE), -1) + 1)
                           AND DP.ACCOUNTDATE < TRUNC(LAST_DAY(SYSDATE) + 1)
                         GROUP BY TO_CHAR(DP.ACCOUNTDATE, 'MM/DD')) T1
                 RIGHT JOIN (SELECT TO_CHAR(TRUNC(T.CREATETIME), 'MM/DD') AS SJ,
                                    NVL(SUM(T.GOODSLEVELTYPEID), 0) NUM2
                               FROM (SELECT 1 AS GOODSLEVELTYPEID,
                                            PD.GOODSID,
                                            PD.CREATETIME
                                       FROM TP_PM_FINISHEDPRODUCT PD
                                      WHERE PD.VALUEFLAG = '1'
                                        AND PD.ACCOUNTID = 1
                                        AND PD.CREATETIME >= TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE), -1) + 1)
                                        AND PD.CREATETIME < TRUNC(LAST_DAY(SYSDATE) + 1)) T
                              INNER JOIN TP_MST_GOODS MSTGOODS
                                 ON MSTGOODS.GOODSID = T.GOODSID
                              INNER JOIN TP_MST_GOODSTYPE MSTGOODSTYPE
                                 ON MSTGOODSTYPE.GOODSTYPEID = MSTGOODS.GOODSTYPEID
                              WHERE MSTGOODSTYPE.GOODSTYPECODE LIKE '001001%'
                              GROUP BY TO_CHAR(TRUNC(T.CREATETIME), 'MM/DD')) T2
                    ON T2.SJ = T1.ACCOUNTDATE
                 ORDER BY T2.SJ ";

                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
            else if (context.Request["m"].ToString() == "table1")
            {
                string sqlStr = @"
                SELECT * FROM (
				SELECT T1.MATNR,
                       DECODE(INSTR(G.GOODSCODE, '-')
                             ,0
                             ,G.GOODSCODE
                             ,SUBSTR(G.GOODSCODE, 0, LENGTH(G.GOODSCODE) - 2)) AS GOODSCODE,
                       NVL(T1.NUM1, 0) AS NUM1,
                       NVL(T2.NUM2, 0) AS NUM2,
                       --NVL(T1.NUM1, 0) - NVL(T2.NUM2, 0) AS NUM3,
                       CASE WHEN NVL(T1.NUM1, 0) < NVL(T2.NUM2, 0) THEN 0 ELSE NVL(T1.NUM1, 0) - NVL(T2.NUM2, 0) END  AS NUM3,
                       DECODE(NVL(T1.NUM1, 0)
                             ,0
                             ,'-'
                             ,ROUND((NVL(T2.NUM2, 0) / DECODE(NVL(T1.NUM1, 1), 0, 1, NVL(T1.NUM1, 1))) * 100, 1) || '%') AS RATE
                  FROM (SELECT DPD.MATNR,
                               SUM(DPD.OUTQUANTITY) AS NUM1
                          FROM TP_PC_DAILYPLAN DP
                          LEFT JOIN TP_PC_DAILYPLANDETAIL DPD
                            ON DP.DAILYPLANID = DPD.DAILYPLANID
                         WHERE DP.VALUEFLAG = 1
                           AND DPD.VALUEFLAG = 1
                           AND DP.ACCOUNTDATE = TRUNC(SYSDATE)
                         GROUP BY DPD.MATNR) T1
                  LEFT JOIN (SELECT T.MATERIALCODE,
                                    NVL(SUM(T.GOODSLEVELTYPEID), 0) NUM2
                               FROM (SELECT 1 AS GOODSLEVELTYPEID,
                                            GD.MATERIALCODE
                                       FROM TP_PM_FINISHEDPRODUCT PD
                                      INNER JOIN TP_PM_GROUTINGDAILYDETAIL GD
                                         ON GD.GROUTINGDAILYDETAILID = PD.GROUTINGDAILYDETAILID
                                      WHERE PD.VALUEFLAG = '1'
                                        AND PD.ACCOUNTID = 1
                                        AND PD.CREATETIME >= TRUNC(SYSDATE)
                                        AND PD.CREATETIME < TRUNC(SYSDATE) + 1) T
                              GROUP BY T.MATERIALCODE) T2
                    ON T1.MATNR = T2.MATERIALCODE
                  LEFT JOIN (SELECT T.MATERIALCODE,
                                    MIN(T.GOODSID) AS GOODSID
                               FROM (SELECT G.GOODSID,
                                            G.MATERIALCODE
                                       FROM TP_MST_GOODS G
                                      WHERE G.VALUEFLAG = '1'
                                        AND G.PLANFLAG = '1'
                                     UNION ALL
                                     SELECT S.GOODSID,
                                            S.MATERIALCODE
                                       FROM TP_MST_GOODSLOGOSAP S
                                      INNER JOIN TP_MST_GOODS G
                                         ON G.GOODSID = S.GOODSID
                                      WHERE G.VALUEFLAG = '1'
                                        AND G.PLANFLAG = '1') T
                              GROUP BY T.MATERIALCODE) T3
                    ON T3.MATERIALCODE = T1.MATNR
                  LEFT JOIN TP_MST_GOODS G
                    ON G.GOODSID = T3.GOODSID
                 --ORDER BY ROUND((NVL(T2.NUM2, 0) / DECODE(NVL(T1.NUM1, 1), 0, 1, NVL(T1.NUM1, 1))) * 100, 1) DESC,
                 --         T1.NUM1 DESC,
                 --         T2.NUM2 DESC,
                 --         G.GOODSCODE 
                 )  WHERE NUM1 > 0 AND NUM2 > 0 ORDER BY TO_NUMBER(SUBSTR(RATE, 0, LENGTH(RATE)-1)) DESC";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
            else if (context.Request["m"].ToString() == "table2")
            {
                string sqlStr = @"
                SELECT * FROM (
				SELECT T1.MATNR,
                       DECODE(INSTR(G.GOODSCODE, '-')
                             ,0
                             ,G.GOODSCODE
                             ,SUBSTR(G.GOODSCODE, 0, LENGTH(G.GOODSCODE) - 2)) AS GOODSCODE,
                       NVL(T1.NUM1, 0) AS NUM1,
                       NVL(T2.NUM2, 0) AS NUM2,
                       NVL(T1.NUM1, 0) - NVL(T2.NUM2, 0) AS NUM3,
                       DECODE(NVL(T1.NUM1, 0)
                             ,0
                             ,'-'
                             ,DECODE(NVL(T2.NUM2, 0)
                                    ,0
                                    ,'0%'
                                    ,TO_CHAR((NVL(T2.NUM2, 0) / DECODE(NVL(T1.NUM1, 1), 0, 1, NVL(T1.NUM1, 1))) * 100
                                            ,'FM99990.0') || '%')) AS RATE
                  FROM (SELECT DPD.MATNR,
                               SUM(DPD.MENGE) AS NUM1
                          FROM TP_PC_MONTHPLAN DP
                          LEFT JOIN TP_PC_MONTHPLANDETAIL DPD
                            ON DP.MONTHPLANID = DPD.MONTHPLANID
                         WHERE DP.VALUEFLAG = 1
                           AND DPD.VALUEFLAG = 1
                           AND DP.PROCEDUREID = 104
                           AND DP.ACCOUNTMONTH >= TO_CHAR(SYSDATE, 'YYYY-MM')
                         GROUP BY DPD.MATNR) T1
                  LEFT JOIN (SELECT T.MATERIALCODE,
                                    NVL(SUM(T.GOODSLEVELTYPEID), 0) NUM2
                               FROM (SELECT 1 AS GOODSLEVELTYPEID,
                                            GD.MATERIALCODE
                                       FROM TP_PM_FINISHEDPRODUCT PD
                                      INNER JOIN TP_PM_GROUTINGDAILYDETAIL GD
                                         ON GD.GROUTINGDAILYDETAILID = PD.GROUTINGDAILYDETAILID
                                      WHERE PD.VALUEFLAG = '1'
                                        AND PD.ACCOUNTID = 1
                                        AND PD.CREATETIME >= TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE), -1) + 1)
                                        AND PD.CREATETIME < TRUNC(LAST_DAY(SYSDATE) + 1)) T
                              GROUP BY T.MATERIALCODE) T2
                    ON T1.MATNR = T2.MATERIALCODE
                  LEFT JOIN (SELECT T.MATERIALCODE,
                                    MIN(T.GOODSID) AS GOODSID
                               FROM (SELECT G.GOODSID,
                                            G.MATERIALCODE
                                       FROM TP_MST_GOODS G
                                      WHERE G.VALUEFLAG = '1'
                                        AND G.PLANFLAG = '1'
                                     UNION ALL
                                     SELECT S.GOODSID,
                                            S.MATERIALCODE
                                       FROM TP_MST_GOODSLOGOSAP S
                                      INNER JOIN TP_MST_GOODS G
                                         ON G.GOODSID = S.GOODSID
                                      WHERE G.VALUEFLAG = '1'
                                        AND G.PLANFLAG = '1') T
                              GROUP BY T.MATERIALCODE) T3
                    ON T3.MATERIALCODE = T1.MATNR
                  LEFT JOIN TP_MST_GOODS G
                    ON G.GOODSID = T3.GOODSID
                 --ORDER BY ROUND((NVL(T2.NUM2, 0) / DECODE(NVL(T1.NUM1, 1), 0, 1, NVL(T1.NUM1, 1))) * 100, 1) DESC,
                 --         T1.NUM1 DESC,
                 --        T2.NUM2 DESC,
                 --         G.GOODSCODE 
                 )  WHERE NUM1 > 0 AND NUM2 > 0 ORDER BY TO_NUMBER(SUBSTR(RATE, 0, LENGTH(RATE)-1)) DESC";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
        }
    }

    public decimal toNumber(object o)
    {
        if (o != DBNull.Value)
            return Convert.ToDecimal(o);
        else
            return 0;
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}