HEGII.ss.mes.web/wwwroot/mes/rpt2/rpt4001/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())
        {
            string procedureId = context.Request["procedureidMaster[]"] is object ? context.Request["procedureidMaster[]"].ToString() : "0";
			if (procedureId == "0" && context.Request["procedureidMaster"] is object) procedureId = context.Request["procedureidMaster"].ToString();
            if (procedureId == "") procedureId = "0";
			string goodstypeCode = context.Request["goodstypecodeMaster"] is object ? context.Request["goodstypecodeMaster"].ToString() : "";
            string goodsidMaster = context.Request["goodsidMaster[]"] is object ? context.Request["goodsidMaster[]"] : "0";
			if (goodsidMaster == "0" && context.Request["goodsidMaster"] is object) goodsidMaster = context.Request["goodsidMaster"].ToString();
            if (goodsidMaster == "") goodsidMaster = "0";
			string groutinglineCode = context.Request["groutinglineMaster[]"] is object ? context.Request["groutinglineMaster[]"].ToString() : "0";
			if (groutinglineCode == "0" && context.Request["groutinglineMaster"] is object) groutinglineCode = context.Request["groutinglineMaster"].ToString();
            if (groutinglineCode == "") groutinglineCode = "0";
			//商标id
			string logoId = context.Request["LOGONAME"] is object ? context.Request["LOGONAME"].ToString() : "";
			logoId = logoId==""?string.Empty:"AND INP.LOGOID=" + logoId;

			//主表
            if(context.Request["m"].ToString()=="0")
            {
                //DateTime dateBegin = Convert.ToDateTime(context.Request["datebeginMaster"]);
                //DateTime dateEnd = Convert.ToDateTime(context.Request["dateendMaster"]).AddDays(1);

                //拼合产品型号
                string sqlStr = @"
                    SELECT DISTINCT
	                    INP.GOODSCODE 
                    FROM
	                    TP_PM_INPRODUCTION INP 
						LEFT JOIN TP_MST_GOODS G ON INP.GOODSID = G.GOODSID
						LEFT JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID
                    WHERE
	                    INP.ACCOUNTID = 1 
	                    AND INP.VALUEFLAG = '1' 
						AND (INP.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0' )
						AND (INSTR(GT.GOODSTYPECODE,'{GOODSTYPECODE}') = 1 OR '{GOODSTYPECODE}' IS NULL)
						AND (INP.GROUTINGLINEID IN ({GROUTINGLINECODE}) OR '{GROUTINGLINECODE}' = '0') 
                    ORDER BY
	                    INP.GOODSCODE
                ".Replace("{GOODSID}", goodsidMaster)
                .Replace("{GOODSTYPECODE}", goodstypeCode)
				.Replace("{GROUTINGLINECODE}",groutinglineCode);

                DataTable dt = conn.ExecuteDatatable(sqlStr);
                //String goodscodeStr = "'H0966M' AS H0966M,'H0967L' AS H0967L,'合计' AS 合计";
                String goodscodeStr = "'合计' AS 合计";
                for(int i = 0; i < dt.Rows.Count; i++)
                {
                    goodscodeStr += ",'"+ dt.Rows[i]["GOODSCODE"].ToString() +"' AS " + dt.Rows[i]["GOODSCODE"].ToString().Replace("-","_");
                }


                //读取报表数据
                sqlStr = @"
                    SELECT * FROM
                    (
                    SELECT
	                    T.DISPLAYNO AS 序号,
	                    DECODE(T.GID,3,'0',2,'0',T.PROCEDUREID) AS 工序编号,
	                    DECODE(T.GID,3,'合计',2,'合计',T.PROCEDURENAME) AS 工序名称,
	                    DECODE(T.GID,1,'合计',3,'合计',T.GOODSCODE) AS 产品编码,
	                    T.INCOUNT AS 产量 
                    FROM
	                    (
	                    SELECT
		                    GROUPING_ID ( P.PROCEDURENAME, G.GOODSCODE ) GID,
		                    P.DISPLAYNO,
                            P.PROCEDUREID,
		                    P.PROCEDURENAME,
		                    G.GOODSCODE,
		                    COUNT( BARCODE ) AS INCOUNT 
	                    FROM
		                    TP_PM_INPRODUCTION INP
		                    INNER JOIN TP_PC_PROCEDURE P ON INP.PROCEDUREID = P.PROCEDUREID
		                    INNER JOIN TP_PC_PRODUCTIONLINE PLINE ON PLINE.PRODUCTIONLINEID = INP.PRODUCTIONLINEID
		                    INNER JOIN TP_MST_USER U ON INP.USERID = U.USERID
		                    INNER JOIN TP_MST_GOODS G ON INP.GOODSID = G.GOODSID
		                    INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
							AND (INSTR(GT.GOODSTYPECODE,'{GOODSTYPECODE}') = 1 OR '{GOODSTYPECODE}' IS NULL)
	                    WHERE
		                    INP.ACCOUNTID = 1 
		                    AND INP.VALUEFLAG = '1' 
							AND (INP.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0' )
							AND (P.PROCEDUREID IN ({PROCEDUREID}) OR '{PROCEDUREID}' = '0' )
							AND (INP.GROUTINGLINEID IN ({GROUTINGLINECODE}) OR '{GROUTINGLINECODE}' = '0') 
							{LOGOID}
	                    GROUP BY
		                    GROUPING SETS (
			                    (P.DISPLAYNO,P.PROCEDUREID,P.PROCEDURENAME,G.GOODSCODE ),
			                    (P.DISPLAYNO,P.PROCEDUREID,P.PROCEDURENAME),
			                    (G.GOODSCODE),
			                    ( ) 
		                    ) 
	                    ) T 
                    ORDER BY
	                    T.PROCEDURENAME,
	                    T.GOODSCODE
                    )
                    PIVOT 
	                    ( 
		                    SUM(产量) FOR 产品编码 IN ({GOODSCODESTR}) 
	                    )
                    ORDER BY 序号
                ".Replace("{GOODSCODESTR}",goodscodeStr)
                .Replace("{GOODSID}",goodsidMaster)
                .Replace("{GOODSTYPECODE}", goodstypeCode)
				.Replace("{PROCEDUREID}",procedureId)
				.Replace("{GROUTINGLINECODE}",groutinglineCode)
				.Replace("{LOGOID}", logoId);

                //获取查询条件
                List<CDAParameter> sqlPara = new List<CDAParameter>();
                //sqlPara.Add(new CDAParameter("DATEBEGIN", dateBegin, DataType.DateTime));
                //sqlPara.Add(new CDAParameter("DATEEND", dateEnd, DataType.DateTime));
                //sqlPara.Add(new CDAParameter("PROCEDUREID", context.Request["procedureidMaster"]));

                JsonResult data = Easyui.ExecuteJsonResult(conn, sqlStr, sqlPara);
                context.Response.Write(data.ToJson());
            }

            //子表1
            if(context.Request["m"].ToString()=="1")
            {
                //读取报表数据
                string sqlStr = @"
					SELECT
						P.PROCEDURENAME AS 生产工序,
						U.USERCODE AS 生产工号,
						INP.PROCEDURETIME AS 完成时间,
						ROUND( TO_NUMBER( SYSDATE - INP.PROCEDURETIME ), 2 ) AS 停留天数,
						ROUND( TO_NUMBER( SYSDATE - INP.PROCEDURETIME ) * 24 ) AS 停留小时,
-- 					CASE
-- 		
-- 							WHEN PAU.AUTOSCRAP = '0' THEN
-- 							NVL(PAU.AUTOSCRAPDAYS,0)  ELSE 0 
-- 						END AS 限制停留天数,
					  NVL(PAU.AUTOSCRAPDAYS,0) AS 限制停留天数,
					CASE
	
						WHEN PAU.AUTOSCRAP = '0' 
						THEN
						(CASE 
								WHEN 
								 ROUND( TO_NUMBER( SYSDATE - INP.PROCEDURETIME ) - NVL(PAU.AUTOSCRAPDAYS,0) , 2 ) > 0
						    THEN
		             ROUND( TO_NUMBER( SYSDATE - INP.PROCEDURETIME ) - NVL(PAU.AUTOSCRAPDAYS,0) , 2 )
	              ELSE
		             0
                END) 
						 ELSE 0 
						END AS 超限天数,
					-- NVL( ROUND( TO_NUMBER( SYSDATE - INP.PROCEDURETIME, 0 ) - PAU.AUTOSCRAPDAYS ), 2 ) AS 超限天数,
					CASE
		
							WHEN INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN
							'大件' 
							WHEN INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN
							'小件' 
						END AS 产品类型,
						INP.BARCODE AS 产品条码,
						INP.GOODSCODE AS 产品编码,
						LG.LOGONAME AS 产品商标,
						--INP.PRODUCTIONLINECODE AS 生产线,
						PLINE.PRODUCTIONLINECODE AS 生产线, --生产线名称显示 xcm  2023-3-28
						INP.GOODSLEVELTYPEID AS 产品分级编号,
						GLT.GOODSLEVELTYPENAME AS 产品分级,
						INP.GROUTINGLINECODE AS 成型线编码,
						MT.GMOULDTYPENAME AS 成型线类型,
						INP.GROUTINGMOULDCODE AS 模具编号,
						GD.GROUTINGDATE AS 注浆日期,
						GD.SCRAPFLAG AS 损坯标识,
						GD.USERCODE AS 成型工号,
						GD.DELIVERTIME AS 交坯时间 
					FROM
						TP_PM_INPRODUCTION INP
						INNER JOIN TP_PC_PROCEDURE P ON INP.PROCEDUREID = P.PROCEDUREID
						INNER JOIN TP_PC_PRODUCTIONLINE PLINE ON PLINE.PRODUCTIONLINEID = INP.PRODUCTIONLINEID
						INNER JOIN TP_MST_USER U ON INP.USERID = U.USERID
						INNER JOIN TP_MST_GOODS G ON INP.GOODSID = G.GOODSID
						INNER JOIN TP_MST_LOGO LG ON INP.LOGOID = LG.LOGOID
						INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID
						LEFT JOIN TP_MST_GMOULDTYPE MT ON INP.GMOULDTYPEID = MT.GMOULDTYPEID
						LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GD ON INP.GROUTINGDAILYDETAILID = GD.GROUTINGDAILYDETAILID
						LEFT JOIN TP_SYS_GOODSLEVELTYPE GLT ON INP.GOODSLEVELTYPEID = GLT.GOODSLEVELTYPEID
						LEFT JOIN TP_PC_PROCEDURE_AUTOSCRAP PAU ON P.PROCEDUREID = PAU.PROCEDUREID 
					WHERE
						INP.ACCOUNTID = 1 
						AND INP.VALUEFLAG = '1'	
						AND ( INP.PROCEDUREID = @PROCEDUREID@ OR @PROCEDUREID@ IS NULL OR @PROCEDUREID@ = 0 ) 
						AND ( INP.GOODSID IN ( {GOODSID} ) OR '{GOODSID}' = '0' ) 
						AND ( INSTR( GT.GOODSTYPECODE, '{GOODSTYPECODE}' ) = 1 OR '{GOODSTYPECODE}' IS NULL ) 
						AND ( INP.GROUTINGLINEID IN ( {GROUTINGLINECODE} ) OR '{GROUTINGLINECODE}' = '0' )
						{LOGOID}
                ".Replace("{GOODSID}", goodsidMaster)
				.Replace("{GOODSTYPECODE}", goodstypeCode)
				.Replace("{GROUTINGLINECODE}",groutinglineCode)
				.Replace("{LOGOID}", logoId);

                List<CDAParameter> sqlPara = new List<CDAParameter>();
                sqlPara.Add(new CDAParameter("PROCEDUREID", context.Request["id"]));

                JsonResult data = Easyui.ExecuteJsonResult(conn, sqlStr, sqlPara);
                context.Response.Write(data.ToJson());
            }

        }

    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}