HEGII.ss.mes.dashboard/wwwroot/main/Procedure/test11/warehouse.ashx

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

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 warehouse : IHttpHandler, IReadOnlySessionState
{
    public void ProcessRequest(HttpContext context)
    {
        context.Response.ContentType = "text/plain";
			int h = Convert.ToInt32(DateTime.Now.ToString("HH"));
        string StartTime;
		string EndTime;
        if (h > 7)
        {
            int d = Convert.ToInt32(DateTime.Now.ToString("dd"));
            StartTime = DateTime.Now.ToString("yyyy-MM-dd") +" "+"07:00:00";
            EndTime = DateTime.Now.ToString("yyyy-MM") +"-"+(d + 1).ToString() +" "+ "06:59:59";
        }
        else
        {
            int d = Convert.ToInt32(DateTime.Now.ToString("dd"));
            StartTime = DateTime.Now.ToString("yyyy-MM") +"-"+(d - 1) +" "+ "07:00:00";
            EndTime = DateTime.Now.ToString("yyyy-MM-dd") +" "+"06:59:59";
        }
        using (IDataAccess conn = DataAccess.Create())
        {

				if (context.Request["m"].ToString() == "hx")
            {
                string sqlStr = @"SELECT
									T.CREATETIME AS 日期,
									CASE
									WHEN TT.包装数量 IS NULL THEN
										0
									ELSE
										TT.包装数量 
								END  AS 包装数量
								FROM
									(
									SELECT
									ROWNUM id,
									C.包装数量,
									C.CREATETIME
									FROM(
									SELECT
										0 包装数量,
										'19' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'20' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'21' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'22' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'23' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'00' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'01' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'02' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'03' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'04' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'05' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'06' AS CREATETIME 
									FROM
										DUAL )C
									) T
									FULL JOIN (
									SELECT
										T2.CREATETIME,
										COUNT( * ) 包装数量 
									FROM
										(
										SELECT
											SUBSTR( TO_CHAR( TPP.CREATETIME, 'yyyy-mm-dd hh24:mi:ss' ), 12, 2 ) AS CREATETIME 
										FROM
											TP_PM_PRODUCTIONDATA TPP 
										WHERE
											TPP.PROCEDUREID IN ( 107 ) 
											AND TPP.VALUEFLAG = 1 
											AND TPP.CREATETIME >= trunc(sysdate)-1+18/24
											AND TPP.CREATETIME <= trunc(sysdate)+7/24
										) T2 
									GROUP BY
										T2.CREATETIME 
									ORDER BY
									T2.CREATETIME 
									) TT ON T.CREATETIME = TT.CREATETIME WHERE T.CREATETIME IS NOT NULL ORDER BY T.id ASC";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }

				if (context.Request["m"].ToString() == "h")
            {
                string sqlStr = @"SELECT
									T.CREATETIME AS 日期,
									CASE
									WHEN TT.包装数量 IS NULL THEN
										0
									ELSE
										TT.包装数量 
								END AS 包装数量
								FROM
									(
									SELECT
										0 包装数量,
										'07' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'08' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'09' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'10' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'11' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'12' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'13' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'14' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'15' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'16' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'17' AS CREATETIME 
									FROM
										DUAL UNION ALL
									SELECT
										0 包装数量,
										'18' AS CREATETIME 
									FROM
										DUAL 
									) T
									FULL JOIN (
									SELECT
										T2.CREATETIME,
										COUNT( * ) 包装数量 
									FROM
										(
										SELECT
											SUBSTR( TO_CHAR( TPP.CREATETIME, 'yyyy-mm-dd hh24:mi:ss' ), 12, 2 ) AS CREATETIME 
										FROM
											TP_PM_PRODUCTIONDATA TPP 
										WHERE
											TPP.PROCEDUREID IN ( 107 ) 
											AND TPP.VALUEFLAG = 1 
											AND TPP.CREATETIME >= trunc(sysdate)+7/24
											AND TPP.CREATETIME <= trunc(sysdate)+18/24
										) T2 
									GROUP BY
										T2.CREATETIME 
									ORDER BY
									T2.CREATETIME 
									) TT ON T.CREATETIME = TT.CREATETIME WHERE T.CREATETIME IS NOT NULL ORDER BY 日期 ASC";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }

				if (context.Request["m"].ToString() == "defectw")
            {
                string sqlStr = @"SELECT T.name AS 缺陷位置,T.count AS 缺陷数量 FROM(
									SELECT
										TMDP.S_NAME name,
										COUNT( * ) count
									FROM
										TP_PM_DEFECT TPD
										LEFT JOIN TP_MST_DEFECT TMD ON TPD.DEFECTCODE = TMD.DEFECTCODE 
										LEFT JOIN TP_MST_DEFECTPOSITION TMDP ON TPD.DEFECTPOSITIONID = TMDP.DEFECTPOSITIONID
									WHERE
										TPD.CREATETIME >= TRUNC( SYSDATE ) 
										AND TPD.VALUEFLAG = 1 
										AND TPD.PROCEDUREID IN (125)
									GROUP BY
										TMDP.S_NAME
									ORDER BY
										COUNT( * ) DESC)T WHERE ROWNUM < 4";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
				//成检缺陷位置扇形图
            if (context.Request["m"].ToString() == "defectw")
            {
                string sqlStr = @"SELECT T.name AS 缺陷位置,T.count AS 缺陷数量 FROM(
									SELECT
										TMDP.S_NAME name,
										COUNT( * ) count
									FROM
										TP_PM_DEFECT TPD
										LEFT JOIN TP_MST_DEFECT TMD ON TPD.DEFECTCODE = TMD.DEFECTCODE 
										LEFT JOIN TP_MST_DEFECTPOSITION TMDP ON TPD.DEFECTPOSITIONID = TMDP.DEFECTPOSITIONID
									WHERE
										TPD.CREATETIME >= TRUNC( SYSDATE ) 
										AND TPD.VALUEFLAG = 1 
										AND TPD.PROCEDUREID IN (125)
									GROUP BY
										TMDP.S_NAME
									ORDER BY
										COUNT( * ) DESC)T WHERE ROWNUM < 4";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
			//包装工单
			if (context.Request["m"].ToString() == "gd")
            {
                string sqlStr = @"										SELECT
					SUBSTR( T.CREATETIME, 5, 2 )||'.'||SUBSTR( T.CREATETIME, 7, 2 ) 时间,
					SUM(T.实际生产数量) 实际生产量,
					TO_CHAR(SUM(T.实际生产数量)/SUM(T.计划包装数) * 100,'9,990.00') || '%' 完成率
					FROM(
					SELECT
					to_char( dp.plandate, 'yyyymmdd' ) CREATETIME,
								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_pack_dailyplan dp ON dpd.planid = dp.planid
								WHERE
									dpd.bz_plan_qty > 0
									AND dpd.MAKE_QTY > 0
									AND (dpd.plan_qty * 1.2) >= dpd.make_qty
									AND dpd.planid IN (
									SELECT
										dp.planid AS sid 
									FROM
										tp_pack_dailyplan dp 
									WHERE
										dp.valueflag = 1 
										AND dp.accountid = 1 
										AND dp.plandate >= TRUNC( SYSDATE-6 ) 
									) 
									 GROUP BY to_char( dp.plandate, 'yyyymmdd' ),dpd.out_qty,dpd.plan_qty,dpd.MAKE_QTY
								ORDER BY
									round( dpd.make_qty / dpd.plan_qty, 4 ) DESC )T GROUP BY T.CREATETIME ORDER BY T.CREATETIME ASC";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }

            //成检缺陷扇形图
            if (context.Request["m"].ToString() == "defect")
            {
                string sqlStr = @"SELECT
								T.PACKINGDEFECT 缺陷名称,
								T.count 数量
								FROM(
								SELECT
									PACKINGDEFECT,
									COUNT( * ) count 
								FROM
									TP_PM_PINGUANINSPECTION 
								WHERE CREATETIME >= TRUNC( SYSDATE )  and 
										PACKINGDEFECT IS NOT NULL 
								GROUP BY
									PACKINGDEFECT 
								ORDER BY
									count DESC) T WHERE ROWNUM < 4";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
            //车间设备状态扇形图
            if (context.Request["m"].ToString() == "defectb")
            {
                string sqlStr = @"SELECT
	                                    T.S_NAME AS 缺陷名称,
	                                    T.数量 
                                    FROM
	                                    (
	                                    SELECT
		                                    TMD.S_NAME,
		                                    count( * ) AS 数量 
	                                    FROM
		                                    TP_PM_DEFECT TPD
		                                    LEFT JOIN TP_MST_DEFECT TMD ON TPD.DEFECTID = TMD.DEFECTID 
	                                    WHERE
		                                    TPD.CREATETIME >= TRUNC( SYSDATE )
		                                    AND TMD.DEFECTTYPEID IN ( 15,16,18,4 ) 
	                                    GROUP BY
		                                    TMD.S_NAME 
	                                    ORDER BY
		                                    count( * ) DESC 
	                                    ) T 
                                    WHERE
	                                    ROWNUM < 4";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
            //本烧合格率折线图
            if (context.Request["m"].ToString() == "zx")
            {
                string sqlStr = @"SELECT
							TO_CHAR( TRUNC( T4.本烧合格 / T4.本烧数量,4) * 100, '9,999.99' ) || '%' AS 合格率,
							T4.本烧合格 as 本烧合格数, 
							SUBSTR(T4.CREATETIME,5,2 )||'.'||SUBSTR(T4.CREATETIME,7,2 ) AS 日期  
						FROM
							(
							SELECT
								T1.本烧数量,
								T2.本烧不合格数量,
								T1.本烧数量 - T2.本烧不合格数量 AS 本烧合格,
								T1.CREATETIME 
							FROM
								(
									SELECT DISTINCT--本烧数量
									COUNT( TPPD.BARCODE ) 本烧数量,
									to_char( TPPD.CREATETIME, 'yyyymmdd' ) AS CREATETIME
								FROM
									TP_PM_PRODUCTIONDATA TPPD
									INNER JOIN TP_PM_GROUTINGDAILYDETAIL PGD ON PGD.BARCODE = TPPD.BARCODE
									LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPGL.GROUTINGLINEID = PGD.GROUTINGLINEID 
								WHERE
									TPPD.PROCEDUREID IN ( 104 ) 
									AND TPPD.ISREFIRE = 0 
									AND PGD.TESTFLAG = 0 
									AND TPPD.VALUEFLAG = 1 
									--AND TPPD.checkflag = '1' 
									AND TPPD.CREATETIME >= trunc( SYSDATE - 6 ) 
								GROUP BY
									to_char( TPPD.CREATETIME, 'yyyymmdd' ) 
								ORDER BY
									to_char( TPPD.CREATETIME, 'yyyymmdd' ) DESC 
								) T1
								LEFT JOIN (
								SELECT
									to_char( T1.CREATETIME, 'yyyymmdd' ) AS CREATETIME,
									COUNT( T1.BARCODE ) AS 本烧不合格数量 
								FROM
									(
									SELECT DISTINCT
										T.BARCODE,
										TPPD.CREATETIME 
									FROM
										TP_PM_PRODUCTIONDATA TPPD
										LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPPD.GROUTINGLINEID = TPGL.GROUTINGLINEID
										LEFT JOIN TP_PM_DEFECT TPD ON TPPD.BARCODE = TPD.BARCODE
										LEFT JOIN (
										SELECT
											DISTINCT
											TPPD.BARCODE 
										FROM
											TP_PM_PRODUCTIONDATA TPPD
											LEFT JOIN TP_PC_GROUTINGLINE TPGL ON TPPD.GROUTINGLINEID = TPGL.GROUTINGLINEID 
											LEFT JOIN TP_PM_DEFECT TPD ON TPPD.BARCODE = TPD.BARCODE
											LEFT JOIN TP_MST_DEFECT TMD ON TPD.DEFECTCODE = TMD.DEFECTCODE 
										WHERE
											TPPD.CHECKTIME >= trunc( SYSDATE - 6 ) 
											AND TPPD.GOODSLEVELID IN ( 6, 7 ) 
											AND TPPD.CHECKBATCHNO = 1 
											AND TPGL.TESTFLAG = 0 
											AND TMD.DEFECTTYPEID <> 14 
											AND TPPD.VALUEFLAG = 1 
											AND TPD.DEFECTNAME IS NOT NULL
											--AND TPPD.ISREFIRE = 0 
										) T ON T.BARCODE = TPPD.BARCODE 
									WHERE
										TPPD.CHECKTIME >= trunc( SYSDATE - 6 ) 
										AND TPPD.PROCEDUREID IN ( 104 ) 
										AND length( TPPD.kilncarbatchno ) > 0 
										AND TPGL.TESTFLAG = 0 
										AND TPPD.VALUEFLAG = 1 
										AND TPPD.ISREFIRE = 0 
										AND TPD.DEFECTNAME IS NOT NULL
									) T1 
								GROUP BY
									to_char( T1.CREATETIME, 'yyyymmdd' ) 
								ORDER BY
									to_char( T1.CREATETIME, 'yyyymmdd' ) DESC 
								) T2 ON T2.CREATETIME = T1.CREATETIME
						ORDER BY
							CREATETIME DESC)T4 ORDER BY T4.CREATETIME";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
			//包装产线table
			if (context.Request["m"].ToString() == "MonthTable")
            {
                string sqlStr = @"SELECT
										TMG.MATERIALREMARK 物料描述,
										COUNT( * ) 产量 
									FROM
										TP_PM_PRODUCTIONDATA TPP
										LEFT JOIN TP_MST_GOODS TMG ON TPP.GOODSCODE = TMG.GOODSCODE 
									WHERE
										TPP.CREATETIME >= TRUNC( SYSDATE ) 
										AND TPP.PROCEDUREID = 125 
									GROUP BY
										TMG.MATERIALREMARK 
									ORDER BY
										产量 DESC
											 ";
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr);
                string jsonStr = new JsonResult(dt).ToJson();
                context.Response.Write(jsonStr);
            }
			}
        

        //取产品库存列表
        if(context.Request["m"]=="isfull" && context.Request["isfull"] is object)
        {
            string jsonStr = QianRunApi.GetGoodsCode("","",context.Request["isfull"]);
            jsonStr = jsonStr.Replace("\"GoodsType\"", "\"产品型号\"");
            jsonStr = jsonStr.Replace("\"GoodsSeries\"", "\"产品类型\"");
            jsonStr = jsonStr.Replace("\"GoodsCount\"", "\"产品数量\"");
            context.Response.Write(jsonStr);
        }

        //取产品库位列表
        if(context.Request["m"]=="all")
        {
            int isFull = context.Request["allFull"] is object ? Convert.ToInt32(context.Request["allFull"].ToString()) : 0;
            string jsonStr = QianRunApi.GetStorageStatus(isFull);
            context.Response.Write(jsonStr);
        }

        //获取正在出库的产品
        if(context.Request["m"] == "outing")
        {
            using (IDataAccess conn = DataAccess.Create())
            {
                DataTable dt = conn.ExecuteDatatable(@"
                    SELECT
	                    G.GOODSCODE,
	                    B.MATNR,
                        F.STATUS
                    FROM
	                    TP_MST_PACKINGFORM F
	                    LEFT JOIN TP_MST_PACKINGBOM B ON B.PACKINGBOMID = F.PACKINGBOMID
	                    LEFT JOIN TP_MST_GOODS G ON G.GOODSID = B.GOODSID
                    WHERE
	                    STATUS = '1'
                        OR STATUS = '0'
                    ORDER BY
                        STATUS ASC
				");
                context.Response.Write(new JsonResult(dt).ToJson());
            }
        }
		
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}