HEGII.ss.mes.dashboard/wwwroot/main/Split/ScreenM4/rpt.ashx

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

using System;
using System.Web;
using System.Web.SessionState;
using System.Data;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
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()=="upph")
            {
                //string dateBegin = DateTime.Now.AddDays(0).ToString("yyyy-MM-dd 00:00:00");
                //string dateEnd = DateTime.Now.AddDays(0).ToString("yyyy-MM-dd 23:59:59");

                //读取报表数据
                string sqlStr = @"
SELECT YY.WS_ID
      ,YY.SHIFT_TYPE
      ,YY.USERID
      ,NVL(YY.USERCODE, ' ') AS USERCODE
      ,NVL(YY.USERNAME, ' ') AS USERNAME
      ,YY.KB_WS_NAME
      ,YY.STANDARDCAPACITY
      ,YY.CLASSSETTINGSTAFFCOUNT
      ,YY.YIELD
      ,ROUND(YY.UPPH, 2) UPPH
      ,ROUND(YY.COMP) COMP
      ,TO_CHAR(ROUND(YY.COMP)) || '%' COMP_TEST
      ,RANK() OVER(ORDER BY YY.UPPH) UPPH_RANK
      ,RANK() OVER(ORDER BY YY.COMP) COMP_RANK
  FROM (SELECT Y.*
              ,DECODE(Y.CLASSSETTINGSTAFFCOUNT, 0, 0, Y.YIELD / Y.CLASSSETTINGSTAFFCOUNT) UPPH
              ,DECODE(Y.STANDARDCAPACITY, 0, 100, Y.YIELD / Y.STANDARDCAPACITY * 100) COMP
          FROM (SELECT T.WS_ID
                      ,T.SHIFT_TYPE
                      ,T.USERID
                      ,T.USERCODE
                      ,U.USERNAME
                      ,T.KB_WS_NAME
                      ,T.STANDARDCAPACITY
                      ,T.CLASSSETTINGSTAFFCOUNT
                      ,COUNT(PD.PRODUCTIONDATAID) YIELD
                --,COUNT(gdd.groutingdailydetailid) YIELD
                  FROM TP_MST_HGWS_USERSINFO T
                  LEFT JOIN TP_MST_USER U
                    ON U.USERID = T.USERID
                 INNER JOIN TP_MST_WORKSTATION WS
                    ON WS.WORKSTATIONID = T.WS_ID
                /*LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                 ON GDD.CREATETIME >= T.BEGINTIME
                AND GDD.GROUTINGLINEID = WS.GROUTINGLINEID
                AND GDD.USERID = T.USERID
                AND gdd.groutingflag = '1'
                AND gdd.scrapflag = '0'*/
                  LEFT JOIN TP_PM_PRODUCTIONDATA PD
                    ON PD.USERID = T.USERID
                   AND PD.VALUEFLAG = '1'
                   AND PD.PROCEDUREID = 82
                   AND PD.GROUTINGLINEID = WS.GROUTINGLINEID
                   AND PD.CREATETIME >= T.BEGINTIME
                 GROUP BY T.WS_ID
                         ,T.SHIFT_TYPE
                         ,T.USERID
                         ,T.USERCODE
                         ,U.USERNAME
                         ,T.KB_WS_NAME
                         ,T.STANDARDCAPACITY
                         ,T.CLASSSETTINGSTAFFCOUNT
                 ORDER BY T.WS_ID
                         ,T.SHIFT_TYPE) Y) YY
 ORDER BY WS_ID
         ,SHIFT_TYPE
                ";

                //获取查询条件
                //List<CDAParameter> sqlPara = new List<CDAParameter>();

                //sqlPara.Add(new CDAParameter("DATEBEGIN", Convert.ToDateTime(dateBegin), DataType.DateTime));
                //sqlPara.Add(new CDAParameter("DATEEND", Convert.ToDateTime(dateEnd), DataType.DateTime));

                //直接获取不分页数据
                //DataTable dt = conn.ExecuteDatatable(sqlStr, sqlPara.ToArray());

                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();


                context.Response.Write(jsonStr);
            }

            if(context.Request["m"].ToString()=="goods")
            {
                string dateBegin = DateTime.Now.ToString("yyyy-MM-01");
                //string dateEnd = DateTime.Now.AddMonths(1).ToString("yyyy-MM-01");
   				DateTime date = Convert.ToDateTime(dateBegin);

                //读取报表数据
                string sqlStr = @"
SELECT YY.GOODSCODE
      ,YY.PLANNEDOUTPUT
      ,YY.YIELD
      ,ROUND(YY.COMP) COMP
      ,TO_CHAR(ROUND(YY.COMP)) || '%' COMP_TEST
      ,RANK() OVER(ORDER BY COMP_R) - 1 COMP_RANK
  FROM (SELECT Y.*
              ,Y.YIELD / Y.PLANNEDOUTPUT * 100 COMP
              ,DECODE(GOODSCODE, NULL, -1, Y.YIELD / Y.PLANNEDOUTPUT * 100) COMP_R
          FROM (SELECT T.GOODSCODE
                      ,SUM(T.PLANNEDOUTPUT) PLANNEDOUTPUT
                      ,NVL(SUM(PDD.YIELD), 0) YIELD
                  FROM TP_MST_HGWS_GOODS_PLAN T
                  LEFT JOIN (SELECT PD.GOODSID
                                  ,COUNT(PD.PRODUCTIONDATAID) YIELD
                              FROM TP_PM_PRODUCTIONDATA PD
                             WHERE PD.VALUEFLAG = '1'
                               AND PD.PROCEDUREID = 82
                               AND PD.CREATETIME >= @DATEBEGIN@
                               AND PD.CREATETIME < @DATEEND@
                             GROUP BY PD.GOODSID) PDD
                /*LEFT JOIN (SELECT pd.goodsid
                     ,COUNT(pd.groutingdailydetailid) yield
                 FROM tp_pm_groutingdailydetail pd
                INNER JOIN tp_pc_groutingline gl
                   ON gl.groutinglineid = pd.groutinglineid
                WHERE pd.valueflag = '1'
                  AND pd.groutingflag = '1'
                  AND pd.scrapflag = '0'
                  AND gl.buildingno = 'C'
                  AND gl.floorno = 5
                  AND gl.h_lineindex = 1
                  AND pd.createtime >= @DATEBEGIN@
                  AND pd.createtime < @DATEEND@
                GROUP BY pd.goodsid) pdd*/
                    ON PDD.GOODSID = T.GOODSID
                 WHERE T.PLANTYPE = 1
                   AND T.PLANNEDOUTPUT > 0
                 GROUP BY GROUPING SETS((T.GOODSCODE),())) Y) YY
 ORDER BY GOODSCODE
                ";

                //获取查询条件
                List<CDAParameter> sqlPara = new List<CDAParameter>();
                sqlPara.Add(new CDAParameter("DATEBEGIN", date, DataType.DateTime));
                sqlPara.Add(new CDAParameter("DATEEND", date.AddMonths(1), DataType.DateTime));

                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr, sqlPara.ToArray());
                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;
        }
    }

}