HEGII.ss.mes.dashboard/wwwroot/main/Split/Screen09/rpt_today.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() == "efficiency")
            {
                int pid1 = 0;
                int pid2 = 0;
                string procedurename = "";
                string procedurecount = "";
                if (context.Request["procedure"].ToString() == "成型") {
                    //读取报表数据
                    string sqlStr = @"
                    SELECT
	                 '成型' AS 工序,
                    T1.日实际生产数,
                    T2.CXCOUNT AS 人数,
                    ROUND(T1.日实际生产数/DECODE(T2.CXCOUNT,0,1,T2.CXCOUNT),0) AS 生产效率
                    FROM
                    (
                    SELECT
	                    1 AS WJ,
	                    COUNT( DISTINCT BARCODE ) AS 日实际生产数
                    FROM
	                    TP_PM_GROUTINGDAILYDETAIL 
                    WHERE
	                    VALUEFLAG = 1 
	                    AND GROUTINGFLAG = 1 
	                    AND SCRAPFLAG = 0 
	                    AND INSTR( GROUTINGLINECODE, 'C' ) = 1 
	                    AND CREATETIME >= TRUNC( SYSDATE ) 
	                    AND CREATETIME < TRUNC( SYSDATE ) + 1 
	                    AND GOODSCODE IN ( 'H0962L-A', 'H0967L-W', 'H0966M-W', 'H0967M-W', 'H0966L-W', 'H0962M-A' )
	                    )T1
	                    INNER JOIN
	                    (
                    SELECT
	                    1 AS WJ,
	                    CXCOUNT
                    FROM
	                    TP_RPT_PROCEDURESTAFFSCOUNT
	                    )T2
	                    ON T1.WJ = T2.WJ
	
                    ";
                    DataTable dt0 = conn.ExecuteDatatable(sqlStr);
                    string jsonStr = new JsonResult(dt0).ToJson();
                    context.Response.Write(jsonStr);
                }
                else
                {
                    if (context.Request["procedure"].ToString() == "一检")
                    {
                        pid1 = 88; pid2 = 92;
                        //读取报表数据
                        string sqlStr = @"
                        SELECT
	                     '一检' AS 工序,
                        T1.日实际生产数,
                        T2.YJCOUNT AS 人数,
                        ROUND(T1.日实际生产数/DECODE(T2.YJCOUNT,0,1,T2.YJCOUNT),0) AS 生产效率
                        FROM
                        (
                            SELECT
	                        1 AS WJ,
                            COUNT(DISTINCT BARCODE) AS 日实际生产数
                            FROM
                            TP_PM_PRODUCTIONDATA
                            WHERE
                            VALUEFLAG = 1
                            AND
                            (PROCEDUREID = @PID1@ OR PROCEDUREID = @PID2@)
                            AND
                            CREATETIME  >= TRUNC(SYSDATE)
                            AND
                            CREATETIME < TRUNC(SYSDATE)+1
                            AND
                            GOODSCODE IN ('H0962L-A','H0967L-W','H0966M-W','H0967M-W','H0966L-W','H0962M-A')
	                        )T1
	                        INNER JOIN
	                        (
                        SELECT
	                        1 AS WJ,
	                        YJCOUNT
                        FROM
	                        TP_RPT_PROCEDURESTAFFSCOUNT
	                        )T2
	                        ON T1.WJ = T2.WJ
                        ";
                        //获取查询条件
                        List<CDAParameter> sqlPara = new List<CDAParameter>();
                        sqlPara.Add(new CDAParameter("PID1", pid1));
                        sqlPara.Add(new CDAParameter("PID2", pid2));

                        //直接获取不分页数据
                        DataTable dt = conn.ExecuteDatatable(sqlStr, sqlPara.ToArray());
                        string jsonStr = new JsonResult(dt).ToJson();
                        context.Response.Write(jsonStr);
                    }
                    else {

                        if (context.Request["procedure"].ToString() == "改洗") { pid1 = 94; procedurename = "改洗"; procedurecount = "GXCOUNT"; }
                        if (context.Request["procedure"].ToString() == "二检") { pid1 = 117; procedurename = "二检"; procedurecount = "EJCOUNT"; }
                        if (context.Request["procedure"].ToString() == "施釉") { pid1 = 98; procedurename = "施釉"; procedurecount = "SYCOUNT"; }
                        if (context.Request["procedure"].ToString() == "烧成") { pid1 = 103; procedurename = "烧成"; procedurecount = "SCCOUNT"; }
                        if (context.Request["procedure"].ToString() == "成检") { pid1 = 104; procedurename = "成检"; procedurecount = "CJCOUNT"; }
                        string sqlStr = @"
                        SELECT
                        TO_CHAR(@PROCEDURENAME@) AS 工序,
                        T1.日实际生产数,
                        T2.{PROCEDURECOUNT} AS 人数,
                        ROUND(T1.日实际生产数/DECODE(T2.{PROCEDURECOUNT},0,1,T2.{PROCEDURECOUNT}),0) AS 生产效率
                        FROM
                        (
                            SELECT
	                        1 AS WJ,
                            COUNT(DISTINCT BARCODE) AS 日实际生产数
                            FROM
                            TP_PM_PRODUCTIONDATA
                            WHERE
                            VALUEFLAG = 1
                            AND
                            PROCEDUREID = @PID1@ 
                            AND
                            CREATETIME  >= TRUNC(SYSDATE)
                            AND
                            CREATETIME < TRUNC(SYSDATE)+1
                            AND
                            GOODSCODE IN ('H0962L-A','H0967L-W','H0966M-W','H0967M-W','H0966L-W','H0962M-A')
	                        )T1
	                        INNER JOIN
	                        (
                        SELECT
	                        1 AS WJ,
	                        {PROCEDURECOUNT}
                        FROM
	                        TP_RPT_PROCEDURESTAFFSCOUNT
	                        )T2
	                        ON T1.WJ = T2.WJ
                        ".Replace("{PROCEDURECOUNT}",procedurecount+"");
                        //获取查询条件
                        List<CDAParameter> sqlPara = new List<CDAParameter>();
                        sqlPara.Add(new CDAParameter("PID1", pid1));
                        sqlPara.Add(new CDAParameter("PROCEDURENAME", procedurename));
                        //sqlPara.Add(new CDAParameter("PROCEDURECOUNT", procedurecount));

                        //直接获取不分页数据
                        DataTable dt = conn.ExecuteDatatable(sqlStr, sqlPara.ToArray());
                        string jsonStr = new JsonResult(dt).ToJson();
                        context.Response.Write(jsonStr);
                    }
                }
            }
            else if (context.Request["m"].ToString() == "people")
            {
                string sqlStr = @"
                SELECT
                CXCOUNT AS 成型,
                YJCOUNT AS 一检,
                GXCOUNT AS 改洗,
                EJCOUNT AS 二检,
                SYCOUNT AS 施釉,
                SCCOUNT AS 烧成,
                CJCOUNT AS 成检,
                CXCOUNT+YJCOUNT+GXCOUNT+EJCOUNT+SYCOUNT+SCCOUNT+CJCOUNT AS 总人数
                FROM
                TP_RPT_PROCEDURESTAFFSCOUNT
                ";
                //获取查询条件

                //直接获取不分页数据
                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;
        }
    }

}