HEGII.ss.mes.dashboard/wwwroot/main/Procedure/test10/rpt2.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() == "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 TMD.DEFECTTYPEID IN (1,13)
									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() == "defect")
            {
                string sqlStr = @"SELECT T.name as 缺陷名称,T.count as 数量 FROM(
												SELECT
													TMD.S_name name,
													COUNT( * ) count
												FROM
													TP_PM_DEFECT TPD
													LEFT JOIN TP_MST_DEFECT TMD ON TPD.DEFECTCODE = TMD.DEFECTCODE 
												WHERE
													TPD.CHECKTIME >= TRUNC( SYSDATE ) 
													AND TPD.VALUEFLAG = 1 
													AND TMD.DEFECTTYPEID IN (1,13)
												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() == "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(ROUND( T4.本烧合格 / T4.本烧数量,4) * 100, '9,999.00' ) || '%' 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 (
										SELECT
											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 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 
									) 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);
            }
            //主表
            if(context.Request["m"].ToString()=="tk")
            {
                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 KK.*
                          ,KCG.BARCODE
                          ,G.GOODSCODE
                          ,D.DICTIONARYVALUE KILNCARPOSITION
                      FROM (SELECT ROWNUM KNO
                                  ,K.*
                              FROM (SELECT KCS.KILNCARID
                                          ,KC.KILNCARCODE
                                          ,KC.KILNCARNUM
                                          ,KCS.UPDATETIME INTIME
                                      FROM TP_PM_KILNCARSTATUS KCS
                                     INNER JOIN TP_MST_KILNCAR KC
                                        ON KC.KILNCARID = KCS.KILNCARID
                                     WHERE KCS.MODELTYPE = 2
                                       AND KCS.KILNID = 5
                                     ORDER BY KCS.UPDATETIME DESC) K
                             WHERE ROWNUM <= 93) KK
                     INNER JOIN TP_PM_KILNCARGOODS KCG
                        ON KCG.KILNCARID = KK.KILNCARID
                     INNER JOIN TP_MST_GOODS G
                        ON G.GOODSID = KCG.GOODSID
                     INNER JOIN TP_MST_DATADICTIONARY D
                        ON D.DICTIONARYID = KCG.KILNCARPOSITION
                     ORDER BY KK.KNO
                             ,D.DICTIONARYVALUE
                             ,G.GOODSCODE
                ";

                //获取查询条件
                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));


                //获取分页参数
                //int page = HttpContext.Current.Request["page"] is object ? Convert.ToInt32(HttpContext.Current.Request["page"]) : 1;
                //int rows = HttpContext.Current.Request["rows"] is object ? Convert.ToInt32(HttpContext.Current.Request["rows"]) : 10;
                //string sort = HttpContext.Current.Request["sort"] is object ? HttpContext.Current.Request["sort"] : "";
                //string order = HttpContext.Current.Request["order"] is object ? HttpContext.Current.Request["order"] : "";

                //获取分页数据    
                //int total = 0;
                //DataTable dt = conn.SelectPages(page, rows, out total, sqlStr, sqlPara.ToArray());
                //string jsonStr = new JsonResult(dt) { total = total }.ToJson();

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

                string jsonStr = new JsonResult(dt).ToJson();


                context.Response.Write(jsonStr);
            }
            if(context.Request["m"].ToString()=="hg1")
            {
                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 KK.*
                          ,KCG.BARCODE
                          ,G.GOODSCODE
                          ,D.DICTIONARYVALUE KILNCARPOSITION
                      FROM (SELECT ROWNUM KNO
                                  ,K.*
                              FROM (SELECT T.KILNCARID
                                          ,KC.KILNCARCODE
                                          ,KC.KILNCARNUM
                                          ,T.UPDATETIME INTIME
                                      FROM TP_PM_KILNCARSTATUS T
                                     INNER JOIN TP_MST_KILNCAR KC
                                        ON KC.KILNCARID = T.KILNCARID
                                     WHERE T.MODELTYPE = 1
                                       AND T.KILNID = 5
                                    AND (T.UPDATEUSERID = 1512 OR T.UPDATEUSERID = 1557)
                                     ORDER BY T.UPDATETIME DESC) K
                             WHERE ROWNUM <= 44) KK
                     INNER JOIN TP_PM_KILNCARGOODS KCG
                        ON KCG.KILNCARID = KK.KILNCARID
                     INNER JOIN TP_MST_GOODS G
                        ON G.GOODSID = KCG.GOODSID
                     INNER JOIN TP_MST_DATADICTIONARY D
                        ON D.DICTIONARYID = KCG.KILNCARPOSITION
                     ORDER BY KK.KNO 
                             ,D.DICTIONARYVALUE
                             ,G.GOODSCODE
                ";

                //获取查询条件
                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));


                //获取分页参数
                //int page = HttpContext.Current.Request["page"] is object ? Convert.ToInt32(HttpContext.Current.Request["page"]) : 1;
                //int rows = HttpContext.Current.Request["rows"] is object ? Convert.ToInt32(HttpContext.Current.Request["rows"]) : 10;
                //string sort = HttpContext.Current.Request["sort"] is object ? HttpContext.Current.Request["sort"] : "";
                //string order = HttpContext.Current.Request["order"] is object ? HttpContext.Current.Request["order"] : "";

                //获取分页数据    
                //int total = 0;
                //DataTable dt = conn.SelectPages(page, rows, out total, sqlStr, sqlPara.ToArray());
                //string jsonStr = new JsonResult(dt) { total = total }.ToJson();

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

                string jsonStr = new JsonResult(dt).ToJson();


                context.Response.Write(jsonStr);
            }
            if(context.Request["m"].ToString()=="hg2")
            {
                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 KK.*
                          ,KCG.BARCODE
                          ,G.GOODSCODE
                          ,D.DICTIONARYVALUE KILNCARPOSITION
                      FROM (SELECT ROWNUM KNO
                                  ,K.*
                              FROM (SELECT T.KILNCARID
                                          ,KC.KILNCARCODE
                                          ,KC.KILNCARNUM
                                          ,T.UPDATETIME INTIME
                                      FROM TP_PM_KILNCARSTATUS T
                                     INNER JOIN TP_MST_KILNCAR KC
                                        ON KC.KILNCARID = T.KILNCARID
                                     WHERE T.MODELTYPE = 1
                                       AND T.KILNID = 5
                                    AND (T.UPDATEUSERID = 1513 OR T.UPDATEUSERID = 1558)
                                     ORDER BY T.UPDATETIME DESC) K
                             WHERE ROWNUM <= 44) KK
                     INNER JOIN TP_PM_KILNCARGOODS KCG
                        ON KCG.KILNCARID = KK.KILNCARID
                     INNER JOIN TP_MST_GOODS G
                        ON G.GOODSID = KCG.GOODSID
                     INNER JOIN TP_MST_DATADICTIONARY D
                        ON D.DICTIONARYID = KCG.KILNCARPOSITION
                     ORDER BY KK.KNO
                             ,D.DICTIONARYVALUE
                             ,G.GOODSCODE
                ";

                //获取查询条件
                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));


                //获取分页参数
                //int page = HttpContext.Current.Request["page"] is object ? Convert.ToInt32(HttpContext.Current.Request["page"]) : 1;
                //int rows = HttpContext.Current.Request["rows"] is object ? Convert.ToInt32(HttpContext.Current.Request["rows"]) : 10;
                //string sort = HttpContext.Current.Request["sort"] is object ? HttpContext.Current.Request["sort"] : "";
                //string order = HttpContext.Current.Request["order"] is object ? HttpContext.Current.Request["order"] : "";

                //获取分页数据    
                //int total = 0;
                //DataTable dt = conn.SelectPages(page, rows, out total, sqlStr, sqlPara.ToArray());
                //string jsonStr = new JsonResult(dt) { total = total }.ToJson();

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

}