HEGII.ss.mes.dashboard/wwwroot/main/Split/Screen14/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() == "cx")
            {
                string sqlStr = @"
			SELECT
            CASE WHEN T.GOODSTYPEID = 18 THEN CAST('智能座便器' AS nvarchar2(20))
		            WHEN T.GOODSTYPEID = 12 THEN CAST('小便器(挂)' AS nvarchar2(20))
	            ELSE
		            T.GOODSTYPENAME
            END 产品类别,
            SUM(T.plan_qty) 成型计划数,
            sum(T.make_qty) 成型达成数,
            to_char(round(sum(T.make_qty) / SUM(T.plan_qty),4)* 100, 'fm9999999990.00' ) 达成率
            FROM(
            SELECT
	            mg.GOODSTYPEID,
	            TMGP.GOODSTYPENAME,
	            mdd.plan_qty,
	            mdd.make_qty 
            FROM
	            tp_mould_dailyplandetail mdd
	            LEFT JOIN tp_mst_goods mg ON mdd.goodsid = mg.goodsid
	            LEFT JOIN TP_MST_GOODSTYPE TMGP ON mg.GOODSTYPEID = TMGP.GOODSTYPEID 
            WHERE
	            mdd.valueflag = 1
	            AND mg.GOODSTYPEID IN (3)
	            AND mdd.MAKE_QTY > 0
	            AND mdd.plandate < TRUNC( SYSDATE )
	            AND mdd.plandate >= TRUNC( SYSDATE -30 )
	            )T GROUP BY T.GOODSTYPEID,T.GOODSTYPENAME UNION ALL
							SELECT
            CASE WHEN T.GOODSTYPEID = 18 THEN CAST('智能座便器' AS nvarchar2(20))
		            WHEN T.GOODSTYPEID = 12 THEN CAST('小便器(挂)' AS nvarchar2(20))
	            ELSE
		            T.GOODSTYPENAME
            END 产品类别,
            SUM(T.plan_qty) 成型计划数,
            sum(T.make_qty) 成型达成数,
            to_char(round(sum(T.make_qty) / SUM(T.plan_qty),4)* 100, 'fm9999999990.00' ) 达成率
            FROM(
            SELECT
	            mg.GOODSTYPEID,
	            TMGP.GOODSTYPENAME,
	            mdd.plan_qty,
	            mdd.make_qty 
            FROM
	            tp_mould_dailyplandetail mdd
	            LEFT JOIN tp_mst_goods mg ON mdd.goodsid = mg.goodsid
	            LEFT JOIN TP_MST_GOODSTYPE TMGP ON mg.GOODSTYPEID = TMGP.GOODSTYPEID 
            WHERE
	            mdd.valueflag = 1
	            AND mg.GOODSTYPEID IN (18)
	            AND mdd.MAKE_QTY > 0
	            AND mdd.plandate < TRUNC( SYSDATE )
	            AND mdd.plandate >= TRUNC( SYSDATE -30 )
	            )T GROUP BY T.GOODSTYPEID,T.GOODSTYPENAME UNION ALL
							SELECT
            CASE WHEN T.GOODSTYPEID = 18 THEN CAST('智能座便器' AS nvarchar2(20))
		            WHEN T.GOODSTYPEID = 12 THEN CAST('小便器(挂)' AS nvarchar2(20))
	            ELSE
		            T.GOODSTYPENAME
            END 产品类别,
            SUM(T.plan_qty) 成型计划数,
            sum(T.make_qty) 成型达成数,
            to_char(round(sum(T.make_qty) / SUM(T.plan_qty),4)* 100, 'fm9999999990.00' ) 达成率
            FROM(
            SELECT
	            mg.GOODSTYPEID,
	            TMGP.GOODSTYPENAME,
	            mdd.plan_qty,
	            mdd.make_qty 
            FROM
	            tp_mould_dailyplandetail mdd
	            LEFT JOIN tp_mst_goods mg ON mdd.goodsid = mg.goodsid
	            LEFT JOIN TP_MST_GOODSTYPE TMGP ON mg.GOODSTYPEID = TMGP.GOODSTYPEID 
            WHERE
	            mdd.valueflag = 1
	            AND mg.GOODSTYPEID IN (7)
	            AND mdd.MAKE_QTY > 0
	            AND mdd.plandate < TRUNC( SYSDATE )
	            AND mdd.plandate >= TRUNC( SYSDATE -30 )
	            )T GROUP BY T.GOODSTYPEID,T.GOODSTYPENAME UNION ALL
							SELECT
            CASE WHEN T.GOODSTYPEID = 18 THEN CAST('智能座便器' AS nvarchar2(20))
		            WHEN T.GOODSTYPEID = 12 THEN CAST('小便器(挂)' AS nvarchar2(20))
	            ELSE
		            T.GOODSTYPENAME
            END 产品类别,
            SUM(T.plan_qty) 成型计划数,
            sum(T.make_qty) 成型达成数,
            to_char(round(sum(T.make_qty) / SUM(T.plan_qty),4)* 100, 'fm9999999990.00' ) 达成率
            FROM(
            SELECT
	            mg.GOODSTYPEID,
	            TMGP.GOODSTYPENAME,
	            mdd.plan_qty,
	            mdd.make_qty 
            FROM
	            tp_mould_dailyplandetail mdd
	            LEFT JOIN tp_mst_goods mg ON mdd.goodsid = mg.goodsid
	            LEFT JOIN TP_MST_GOODSTYPE TMGP ON mg.GOODSTYPEID = TMGP.GOODSTYPEID 
            WHERE
	            mdd.valueflag = 1
	            AND mg.GOODSTYPEID IN (6)
	            AND mdd.MAKE_QTY > 0
	            AND mdd.plandate < TRUNC( SYSDATE )
	            AND mdd.plandate >= TRUNC( SYSDATE -30 )
	            )T GROUP BY T.GOODSTYPEID,T.GOODSTYPENAME UNION ALL
							SELECT
            CASE WHEN T.GOODSTYPEID = 18 THEN CAST('智能座便器' AS nvarchar2(20))
		            WHEN T.GOODSTYPEID = 12 THEN CAST('小便器(挂)' AS nvarchar2(20))
	            ELSE
		            T.GOODSTYPENAME
            END 产品类别,
            SUM(T.plan_qty) 成型计划数,
            sum(T.make_qty) 成型达成数,
            to_char(round(sum(T.make_qty) / SUM(T.plan_qty),4)* 100, 'fm9999999990.00' ) 达成率
            FROM(
            SELECT
	            mg.GOODSTYPEID,
	            TMGP.GOODSTYPENAME,
	            mdd.plan_qty,
	            mdd.make_qty 
            FROM
	            tp_mould_dailyplandetail mdd
	            LEFT JOIN tp_mst_goods mg ON mdd.goodsid = mg.goodsid
	            LEFT JOIN TP_MST_GOODSTYPE TMGP ON mg.GOODSTYPEID = TMGP.GOODSTYPEID 
            WHERE
	            mdd.valueflag = 1
	            AND mg.GOODSTYPEID IN (12)
	            AND mdd.MAKE_QTY > 0
	            AND mdd.plandate < TRUNC( SYSDATE )
	            AND mdd.plandate >= TRUNC( SYSDATE -30 )
	            )T GROUP BY T.GOODSTYPEID,T.GOODSTYPENAME UNION ALL
							SELECT
            CASE WHEN T.GOODSTYPEID = 18 THEN CAST('智能座便器' AS nvarchar2(20))
		            WHEN T.GOODSTYPEID = 12 THEN CAST('小便器(挂)' AS nvarchar2(20))
	            ELSE
		            T.GOODSTYPENAME
            END 产品类别,
            SUM(T.plan_qty) 成型计划数,
            sum(T.make_qty) 成型达成数,
            to_char(round(sum(T.make_qty) / SUM(T.plan_qty),4)* 100, 'fm9999999990.00' ) 达成率
            FROM(
            SELECT
	            mg.GOODSTYPEID,
	            TMGP.GOODSTYPENAME,
	            mdd.plan_qty,
	            mdd.make_qty 
            FROM
	            tp_mould_dailyplandetail mdd
	            LEFT JOIN tp_mst_goods mg ON mdd.goodsid = mg.goodsid
	            LEFT JOIN TP_MST_GOODSTYPE TMGP ON mg.GOODSTYPEID = TMGP.GOODSTYPEID 
            WHERE
	            mdd.valueflag = 1
	            AND mg.GOODSTYPEID IN (13)
	            AND mdd.MAKE_QTY > 0
	            AND mdd.plandate < TRUNC( SYSDATE )
	            AND mdd.plandate >= TRUNC( SYSDATE -30 )
	            )T GROUP BY T.GOODSTYPEID,T.GOODSTYPENAME";
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }

            //包装月度计划
             if (context.Request["m"].ToString() == "finish")
            {
                string sqlStr = @"
                SELECT
                CASE WHEN T.GOODSTYPEID = 18 THEN CAST('智能座便器' AS nvarchar2(20))
		                 WHEN T.GOODSTYPEID = 12 THEN CAST('小便器(挂)' AS nvarchar2(20))
                ELSE
		                T.产品类别
                END 产品类别,
	                SUM(T.计划包装数) 包装计划数,
	                SUM(T.实际生产数量) 包装达成数
                FROM
	                (
                SELECT
	                mg.GOODSTYPEID,
	                TMGP.GOODSTYPENAME 产品类别,
                CASE WHEN dpd.out_qty = 0 THEN NULL ELSE dpd.plan_qty END 计划包装数,
	                dpd.MAKE_QTY 实际生产数量 
                FROM
	                tp_pack_dailyplandetail dpd
	                LEFT JOIN tp_mst_goods mg ON dpd.goodsid = mg.goodsid
	                LEFT JOIN TP_MST_GOODSTYPE TMGP ON mg.GOODSTYPEID = TMGP.GOODSTYPEID 
                WHERE
	                dpd.bz_plan_qty > 0 
	                AND mg.GOODSTYPEID IN (3 ) 
	                AND dpd.VALUEFLAG = 1
	                AND dpd.accountid = 1 
	                AND dpd.plandate < TRUNC( SYSDATE )
	                AND dpd.plandate >= TRUNC( SYSDATE -30 )
	                AND dpd.MAKE_QTY > 0 
	                )T GROUP BY T.GOODSTYPEID,T.产品类别 UNION ALL
									SELECT
                CASE WHEN T.GOODSTYPEID = 18 THEN CAST('智能座便器' AS nvarchar2(20))
		                 WHEN T.GOODSTYPEID = 12 THEN CAST('小便器(挂)' AS nvarchar2(20))
                ELSE
		                T.产品类别
                END 产品类别,
	                SUM(T.计划包装数) 包装计划数,
	                SUM(T.实际生产数量) 包装达成数
                FROM
	                (
                SELECT
	                mg.GOODSTYPEID,
	                TMGP.GOODSTYPENAME 产品类别,
                CASE WHEN dpd.out_qty = 0 THEN NULL ELSE dpd.plan_qty END 计划包装数,
	                dpd.MAKE_QTY 实际生产数量 
                FROM
	                tp_pack_dailyplandetail dpd
	                LEFT JOIN tp_mst_goods mg ON dpd.goodsid = mg.goodsid
	                LEFT JOIN TP_MST_GOODSTYPE TMGP ON mg.GOODSTYPEID = TMGP.GOODSTYPEID 
                WHERE
	                dpd.bz_plan_qty > 0 
	                AND mg.GOODSTYPEID IN (18) 
	                AND dpd.VALUEFLAG = 1
	                AND dpd.accountid = 1 
	                AND dpd.plandate < TRUNC( SYSDATE )
	                AND dpd.plandate >= TRUNC( SYSDATE -30 )
	                AND dpd.MAKE_QTY > 0 
	                )T GROUP BY T.GOODSTYPEID,T.产品类别 UNION ALL
									SELECT
                CASE WHEN T.GOODSTYPEID = 18 THEN CAST('智能座便器' AS nvarchar2(20))
		                 WHEN T.GOODSTYPEID = 12 THEN CAST('小便器(挂)' AS nvarchar2(20))
                ELSE
		                T.产品类别
                END 产品类别,
	                SUM(T.计划包装数) 包装计划数,
	                SUM(T.实际生产数量) 包装达成数
                FROM
	                (
                SELECT
	                mg.GOODSTYPEID,
	                TMGP.GOODSTYPENAME 产品类别,
                CASE WHEN dpd.out_qty = 0 THEN NULL ELSE dpd.plan_qty END 计划包装数,
	                dpd.MAKE_QTY 实际生产数量 
                FROM
	                tp_pack_dailyplandetail dpd
	                LEFT JOIN tp_mst_goods mg ON dpd.goodsid = mg.goodsid
	                LEFT JOIN TP_MST_GOODSTYPE TMGP ON mg.GOODSTYPEID = TMGP.GOODSTYPEID 
                WHERE
	                dpd.bz_plan_qty > 0 
	                AND mg.GOODSTYPEID IN (7) 
	                AND dpd.VALUEFLAG = 1
	                AND dpd.accountid = 1 
	                AND dpd.plandate < TRUNC( SYSDATE )
	                AND dpd.plandate >= TRUNC( SYSDATE -30 )
	                AND dpd.MAKE_QTY > 0 
	                )T GROUP BY T.GOODSTYPEID,T.产品类别 UNION ALL
									SELECT
                CASE WHEN T.GOODSTYPEID = 18 THEN CAST('智能座便器' AS nvarchar2(20))
		                 WHEN T.GOODSTYPEID = 12 THEN CAST('小便器(挂)' AS nvarchar2(20))
                ELSE
		                T.产品类别
                END 产品类别,
	                SUM(T.计划包装数) 包装计划数,
	                SUM(T.实际生产数量) 包装达成数
                FROM
	                (
                SELECT
	                mg.GOODSTYPEID,
	                TMGP.GOODSTYPENAME 产品类别,
                CASE WHEN dpd.out_qty = 0 THEN NULL ELSE dpd.plan_qty END 计划包装数,
	                dpd.MAKE_QTY 实际生产数量 
                FROM
	                tp_pack_dailyplandetail dpd
	                LEFT JOIN tp_mst_goods mg ON dpd.goodsid = mg.goodsid
	                LEFT JOIN TP_MST_GOODSTYPE TMGP ON mg.GOODSTYPEID = TMGP.GOODSTYPEID 
                WHERE
	                dpd.bz_plan_qty > 0 
	                AND mg.GOODSTYPEID IN (6) 
	                AND dpd.VALUEFLAG = 1
	                AND dpd.accountid = 1 
	                AND dpd.plandate < TRUNC( SYSDATE )
	                AND dpd.plandate >= TRUNC( SYSDATE -30 )
	                AND dpd.MAKE_QTY > 0 
	                )T GROUP BY T.GOODSTYPEID,T.产品类别 UNION ALL
									SELECT
                CASE WHEN T.GOODSTYPEID = 18 THEN CAST('智能座便器' AS nvarchar2(20))
		                 WHEN T.GOODSTYPEID = 12 THEN CAST('小便器(挂)' AS nvarchar2(20))
                ELSE
		                T.产品类别
                END 产品类别,
	                SUM(T.计划包装数) 包装计划数,
	                SUM(T.实际生产数量) 包装达成数
                FROM
	                (
                SELECT
	                mg.GOODSTYPEID,
	                TMGP.GOODSTYPENAME 产品类别,
                CASE WHEN dpd.out_qty = 0 THEN NULL ELSE dpd.plan_qty END 计划包装数,
	                dpd.MAKE_QTY 实际生产数量 
                FROM
	                tp_pack_dailyplandetail dpd
	                LEFT JOIN tp_mst_goods mg ON dpd.goodsid = mg.goodsid
	                LEFT JOIN TP_MST_GOODSTYPE TMGP ON mg.GOODSTYPEID = TMGP.GOODSTYPEID 
                WHERE
	                dpd.bz_plan_qty > 0 
	                AND mg.GOODSTYPEID IN (12) 
	                AND dpd.VALUEFLAG = 1
	                AND dpd.accountid = 1 
	                AND dpd.plandate < TRUNC( SYSDATE )
	                AND dpd.plandate >= TRUNC( SYSDATE -30 )
	                AND dpd.MAKE_QTY > 0 
	                )T GROUP BY T.GOODSTYPEID,T.产品类别 UNION ALL
									SELECT
                CASE WHEN T.GOODSTYPEID = 18 THEN CAST('智能座便器' AS nvarchar2(20))
		                 WHEN T.GOODSTYPEID = 12 THEN CAST('小便器(挂)' AS nvarchar2(20))
                ELSE
		                T.产品类别
                END 产品类别,
	                SUM(T.计划包装数) 包装计划数,
	                SUM(T.实际生产数量) 包装达成数
                FROM
	                (
                SELECT
	                mg.GOODSTYPEID,
	                TMGP.GOODSTYPENAME 产品类别,
                CASE WHEN dpd.out_qty = 0 THEN NULL ELSE dpd.plan_qty END 计划包装数,
	                dpd.MAKE_QTY 实际生产数量 
                FROM
	                tp_pack_dailyplandetail dpd
	                LEFT JOIN tp_mst_goods mg ON dpd.goodsid = mg.goodsid
	                LEFT JOIN TP_MST_GOODSTYPE TMGP ON mg.GOODSTYPEID = TMGP.GOODSTYPEID 
                WHERE
	                dpd.bz_plan_qty > 0 
	                AND mg.GOODSTYPEID IN (13) 
	                AND dpd.VALUEFLAG = 1
	                AND dpd.accountid = 1 
	                AND dpd.plandate < TRUNC( SYSDATE )
	                AND dpd.plandate >= TRUNC( SYSDATE -30 )
	                AND dpd.MAKE_QTY > 0 
	                )T GROUP BY T.GOODSTYPEID,T.产品类别
                ";
                //获取查询条件

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

             //包装表格数据
             if (context.Request["m"].ToString() == "MonthTable")
            {
                string sqlStr = @"
			            SELECT
	                    CASE WHEN T.GOODSTYPEID = 18 THEN CAST('智能座便器' AS nvarchar2(20))
		                                WHEN T.GOODSTYPEID = 12 THEN CAST('小便器(挂)' AS nvarchar2(20))
	                                ELSE
		                                T.产品类别
                                END 产品类别,
	                    SUM(T.计划包装数) 包装计划数,
	                    SUM(T.实际生产数量) 实际生产数,
	                    CASE WHEN SUM(T.计划包装数) = 0 THEN
 	                    '100.00%' ELSE to_char( round( SUM(T.实际生产数量) / SUM(T.计划包装数), 4 ) * 100, 'fm9999999990.00' ) || '%' 
 	                    END AS 计划达成率
                    FROM
	                    (
                    SELECT
	                    TMGP.GOODSTYPEID,
	                    TMGP.GOODSTYPENAME 产品类别,
                    CASE WHEN dpd.out_qty = 0 THEN NULL ELSE dpd.plan_qty END 计划包装数,
	                    dpd.MAKE_QTY 实际生产数量
                    FROM
	                    tp_pack_dailyplandetail dpd
	                    LEFT JOIN tp_mst_goods mg ON dpd.goodsid = mg.goodsid
	                    LEFT JOIN TP_MST_GOODSTYPE TMGP ON mg.GOODSTYPEID = TMGP.GOODSTYPEID 
                    WHERE
	                    dpd.bz_plan_qty > 0 
	                    AND mg.GOODSTYPEID IN ( 12, 6, 18, 3, 7, 13 ) 
	                    AND dpd.MAKE_QTY > 0 
	                    AND ( dpd.plan_qty * 1.8 ) >= dpd.make_qty 
	                    AND dpd.VALUEFLAG = 1
	                    AND dpd.accountid = 1 
	                    --AND dpd.plandate >= trunc( SYSDATE ) 
	                    AND dpd.plandate < TRUNC( SYSDATE )
	                    AND dpd.plandate >= TRUNC( SYSDATE -30 )
                    ORDER BY
	                    round( dpd.make_qty / dpd.plan_qty, 4 ) DESC
	                    )T GROUP BY T.产品类别,T.GOODSTYPEID ORDER BY round( SUM(T.实际生产数量) / SUM(T.计划包装数), 4 ) 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;
        }
    }

}