HEGII.ss.mes.dashboard/wwwroot/main/Split/Screen05/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() == "procedure")
            {
                int pid1 = 0;
                int pid2 = 0;
                String name = "";
                String limit = "";
                if (context.Request["procedure"].ToString() == "成型") {
                    //读取报表数据
                    string sqlStr = @"
                    SELECT
	                    工序,日实际生产数,计划数,
	                    ROUND( 日实际生产数/ DECODE( 计划数, 0, 1,计划数 ), 2 ) * 100 AS 完成率,
	                    ( SELECT CXLIMIT FROM TP_RPT_PROCEDURERATEWARN ) AS 预警 
                    FROM
	                    (
	                    SELECT
		                    T.工序,
		                    T.日实际生产数,
		                    (
		                    SELECT
			                    ROUND( PLANNUM / 30 ) 
		                    FROM
			                    TP_MST_DASHBOARD_PLAN DP
			                    INNER JOIN TP_MST_DASHBOARD D ON DP.DASHBOARDID = D.DASHBOARDID 
		                    WHERE
			                    DP.PLANTYPE = 3 
			                    AND DP.PLANYEAR = TO_NUMBER( TO_CHAR( SYSDATE, 'YYYY' ) ) 
			                    AND DP.PLANMONTH = TO_NUMBER( TO_CHAR( SYSDATE, 'MM' ) ) 
			                    AND D.DASHBOARDNAME = '成型' 
		                    ) AS 计划数 
	                    FROM
		                    (
		                    SELECT
			                    '成型' AS 工序,
			                    COUNT( DISTINCT GDD.BARCODE ) AS 日实际生产数 
		                    FROM
			                    TP_PM_GROUTINGDAILYDETAIL GDD
		                    INNER JOIN TP_PC_GROUTINGLINE GL 
			                    ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID			
		                    WHERE
			                    GDD.VALUEFLAG = 1 
			                    AND GDD.GROUTINGFLAG = 1 
			                    AND GDD.SCRAPFLAG = 0 
			                    AND GL.WORKSHOP = 3 
                    -- 			AND INSTR( GROUTINGLINECODE, 'C' ) = 1 
			                    AND GDD.CREATETIME >= TRUNC( SYSDATE ) 
			                    AND GDD.CREATETIME < TRUNC( SYSDATE ) + 1 
                    -- 			AND GDD.GOODSCODE IN ( 'H0962L-A', 'H0967L-W', 'H0966M-W', 'H0967M-W', 'H0966L-W', 'H0962M-A' ) 
		                    ) T 
	                    )
                    ";
                    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
	                        工序,日实际生产数,计划数,
	                        ROUND( 日实际生产数/ DECODE( 计划数, 0, 1,计划数 ), 2 ) * 100 AS 完成率,
	                        ( SELECT YJLIMIT FROM TP_RPT_PROCEDURERATEWARN ) AS 预警 
                        FROM
	                        (
	                        SELECT
		                        T.工序,
		                        T.日实际生产数,
		                        (
		                        SELECT
			                        ROUND( PLANNUM / 30 ) 
		                        FROM
			                        TP_MST_DASHBOARD_PLAN DP
			                        INNER JOIN TP_MST_DASHBOARD D ON DP.DASHBOARDID = D.DASHBOARDID 
		                        WHERE
			                        DP.PLANTYPE = 3 
			                        AND DP.PLANYEAR = TO_NUMBER( TO_CHAR( SYSDATE, 'YYYY' ) ) 
			                        AND DP.PLANMONTH = TO_NUMBER( TO_CHAR( SYSDATE, 'MM' ) ) 
			                        AND D.DASHBOARDNAME = '半检一检' 
		                        ) AS 计划数 
	                        FROM
		                        (
		                        SELECT
			                        '一检' AS 工序,
			                        COUNT( DISTINCT PD.BARCODE ) AS 日实际生产数 
		                        FROM
			                        TP_PM_PRODUCTIONDATA PD
			                        INNER JOIN TP_PC_GROUTINGLINE GL ON PD.GROUTINGLINEID = GL.GROUTINGLINEID 
		                        WHERE
			                        PD.VALUEFLAG = 1 
			                        AND ( PD.PROCEDUREID = @PID1@ OR PD.PROCEDUREID = @PID2@) 
			                        AND PD.CREATETIME >= TRUNC( SYSDATE ) 
			                        AND PD.CREATETIME < TRUNC( SYSDATE ) + 1 
			                        AND GL.WORKSHOP = 3
         --                         AND
         --                         GOODSCODE IN ('H0962L-A','H0967L-W','H0966M-W','H0967M-W','H0966L-W','H0962M-A')			
		                        ) T 
	                        )
                        ";
                        //获取查询条件
                        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 = 104;
                        //读取报表数据
                        string sqlStr = @"
					    SELECT
	                    工序,日实际生产数,计划数,
	                    ROUND( 日实际生产数/ DECODE( 计划数, 0, 1,计划数 ), 2 ) * 100 AS 完成率,
                        (SELECT CJLIMIT FROM TP_RPT_PROCEDURERATEWARN) AS 预警
                        FROM
                        (
					    SELECT
					    T.工序,T.日实际生产数,
                        (
                        SELECT
			             ROUND( PLANNUM / 30 ) 
                        FROM
                        TP_MST_DASHBOARD_PLAN DP
                        INNER JOIN
                        TP_MST_DASHBOARD D
                        ON DP.DASHBOARDID = D.DASHBOARDID
                        WHERE
                        DP.PLANTYPE = 3
                        AND
                        DP.PLANYEAR = TO_NUMBER(TO_CHAR(sysdate, 'YYYY'))
                        AND
                        DP.PLANMONTH = TO_NUMBER(TO_CHAR(sysdate, 'MM'))
                        AND
                        D.DASHBOARDNAME = '分级一检'
                        ) AS 计划数
					    FROM
					    (
                        SELECT
                        '成检' AS 工序,
                        COUNT(DISTINCT PD.BARCODE) AS 日实际生产数
                        FROM
                        TP_PM_PRODUCTIONDATA PD
                        WHERE
                        PD.VALUEFLAG = 1
                        AND
                        PD.PROCEDUREID = @PID1@
                        AND
                        PD.CREATETIME >= TRUNC( SYSDATE ) 
                        AND
                        PD.CREATETIME < TRUNC(SYSDATE)+1
                        )T
                        )
                        ";
                        //获取查询条件
                        List<CDAParameter> sqlPara = new List<CDAParameter>();
                        sqlPara.Add(new CDAParameter("PID1", pid1));

                        //直接获取不分页数据
                        DataTable dt = conn.ExecuteDatatable(sqlStr, sqlPara.ToArray());
                        string jsonStr = new JsonResult(dt).ToJson();
                        context.Response.Write(jsonStr);
                    }else if (context.Request["procedure"].ToString() == "烧成"){
                        pid1 = 102;
                        //读取报表数据
                        string sqlStr = @"
					    SELECT
	                    工序,日实际生产数,计划数,
	                    ROUND( 日实际生产数/ DECODE( 计划数, 0, 1,计划数 ), 2 ) * 100 AS 完成率,
                        (SELECT SCLIMIT FROM TP_RPT_PROCEDURERATEWARN) AS 预警
                        FROM
                        (
					    SELECT
					    T.工序,T.日实际生产数,
                        (
                        SELECT
			             ROUND( PLANNUM / 30 ) 
                        FROM
                        TP_MST_DASHBOARD_PLAN DP
                        INNER JOIN
                        TP_MST_DASHBOARD D
                        ON DP.DASHBOARDID = D.DASHBOARDID
                        WHERE
                        DP.PLANTYPE = 3
                        AND
                        DP.PLANYEAR = TO_NUMBER(TO_CHAR(sysdate, 'YYYY'))
                        AND
                        DP.PLANMONTH = TO_NUMBER(TO_CHAR(sysdate, 'MM'))
                        AND
                        D.DASHBOARDNAME = '烧成'
                        ) AS 计划数
					    FROM
					    (
                        SELECT
                        '烧成' AS 工序,
                        COUNT(DISTINCT PD.BARCODE) AS 日实际生产数
                        FROM
                        TP_PM_PRODUCTIONDATA PD
                        WHERE
                        PD.VALUEFLAG = 1
                        AND
                        PD.PROCEDUREID = @PID1@
                        AND
                        PD.CREATETIME >= TRUNC( SYSDATE ) 
                        AND
                        PD.CREATETIME < TRUNC(SYSDATE)+1
                        )T
                        )
                        ";
                        //获取查询条件
                        List<CDAParameter> sqlPara = new List<CDAParameter>();
                        sqlPara.Add(new CDAParameter("PID1", pid1));

                        //直接获取不分页数据
                        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; name = "改洗"; limit = "SELECT GXLIMIT FROM TP_RPT_PROCEDURERATEWARN"; }
                        else if (context.Request["procedure"].ToString() == "二检") { pid1 = 117; name = "半检二检"; limit = "SELECT EJLIMIT FROM TP_RPT_PROCEDURERATEWARN"; }
                        else if (context.Request["procedure"].ToString() == "施釉") { pid1 = 98; name = "施釉"; limit = "SELECT SYLIMIT FROM TP_RPT_PROCEDURERATEWARN"; }
                        //else if (context.Request["procedure"].ToString() == "烧成") { pid1 = 103; name = "烧成"; limit = "SELECT SCLIMIT FROM TP_RPT_PROCEDURERATEWARN"; }
                        //else if (context.Request["procedure"].ToString() == "成检") { pid1 = 104; name = "成检"; limit = "SELECT BZLIMIT FROM TP_RPT_PROCEDURERATEWARN"; }
                        string sqlStr = @"
						SELECT
							工序,日实际生产数,计划数,
							ROUND( 日实际生产数/ DECODE( 计划数, 0, 1,计划数 ), 2 ) * 100 AS 完成率,
							( {LIMIT} ) AS 预警 
						FROM
							(
							SELECT
								DECODE( T.工序, '半检二检', '二检', T.工序 ) AS 工序,
								T.日实际生产数,
								(
								SELECT
									ROUND( PLANNUM / 30 ) 
								FROM
									TP_MST_DASHBOARD_PLAN DP
									INNER JOIN TP_MST_DASHBOARD D ON DP.DASHBOARDID = D.DASHBOARDID 
								WHERE
									DP.PLANTYPE = 3 
									AND DP.PLANYEAR = TO_NUMBER( TO_CHAR( SYSDATE, 'YYYY' ) ) 
									AND DP.PLANMONTH = TO_NUMBER( TO_CHAR( SYSDATE, 'MM' ) ) 
									AND D.DASHBOARDNAME = @NAME@
								) AS 计划数 
							FROM
								(
								SELECT
									@NAME@ AS 工序,
									COUNT( DISTINCT BARCODE ) AS 日实际生产数 
								FROM
									TP_PM_PRODUCTIONDATA PD
									INNER JOIN
									TP_PC_GROUTINGLINE GL
									ON PD.GROUTINGLINEID = GL.GROUTINGLINEID		
								WHERE
									PD.VALUEFLAG = 1 
									AND PD.PROCEDUREID = @PID1@
									AND PD.CREATETIME >= TRUNC( SYSDATE ) 
									AND PD.CREATETIME < TRUNC( SYSDATE ) + 1
									AND	GL.WORKSHOP = 3
						--                         AND
						--                         GOODSCODE IN ('H0962L-A','H0967L-W','H0966M-W','H0967M-W','H0966L-W','H0962M-A')
			
								) T 
							)
                        ".Replace("{LIMIT}",limit);
                        //获取查询条件
                        List<CDAParameter> sqlPara = new List<CDAParameter>();
                        sqlPara.Add(new CDAParameter("PID1", pid1));
                        sqlPara.Add(new CDAParameter("NAME", name));
                        sqlPara.Add(new CDAParameter("LIMIT", limit));
                        //直接获取不分页数据
                        DataTable dt = conn.ExecuteDatatable(sqlStr, sqlPara.ToArray());
                        string jsonStr = new JsonResult(dt).ToJson();
                        context.Response.Write(jsonStr);
                    }
                }
            }
            else if (context.Request["m"].ToString() == "finish")
            {
                string sqlStr = @"
                SELECT
	                t.型号,
	                t.达成数,
	                ROUND(NVL(plan.PLANFINISH,0)/30,0) AS 本日计划数,
	                TO_CHAR(
		                ROUND(
			                t.达成数 / DECODE( NVL( plan.PLANFINISH, 1 ), 0, 1, NVL( plan.PLANFINISH, 1 ) ),
			                4
		                ) * 30 * 100 
	                ) || '%' AS 目标达成率 
                FROM
	                (
	                SELECT
		                GOODSCODE AS 型号,
		                COUNT( BARCODE ) AS 达成数 
	                FROM
		                TP_PM_FINISHEDPRODUCT 
	                WHERE
		                VALUEFLAG = 1 
		                AND GOODSCODE IN ( 'H0962L-A', 'H0967L-W', 'H0966M-W', 'H0967M-W', 'H0966L-W', 'H0962M-A' ) 
		                AND FHTIME >= TRUNC(SYSDATE)
		                AND FHTIME < SYSDATE + 1 
	                GROUP BY
		                GOODSCODE 
	                ORDER BY
		                GOODSCODE 
	                ) t
	                LEFT JOIN TP_MST_HGWS_GOODS_PLAN plan ON t.型号 = plan.GOODSCODE
                ";
                //获取查询条件

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

}