HEGII.ss.mes.web/wwwroot/mes/rpt2/rpt8009/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())
        {
            int nodetype1;
            //主表
            if(context.Request["m"].ToString()=="0")
            {
                DateTime dateBegin = Convert.ToDateTime(context.Request["datebeginMaster"]);
                DateTime dateEnd = Convert.ToDateTime(context.Request["dateendMaster"]).AddDays(1);
                DateTime dbegin = dateBegin;
                string totalMaster = context.Request["totalMaster"].ToString();
                string datestr = "";
                if (totalMaster == "YYYYMMDD") {
                    datestr += " SELECT " + "'" + dateBegin.Year + "'||'" + (dateBegin.Month + "").PadLeft(2, '0') + "'||'" + (dateBegin.Day + "").PadLeft(2, '0') + "' AS 时间 FROM DUAL ";
                    while (dbegin < dateEnd.AddDays(-1)) {
                        dbegin = dbegin.AddDays(1);
                        datestr += " UNION SELECT " + "'" + dbegin.Year + "'||'" + (dbegin.Month + "").PadLeft(2, '0') + "'||'" + (dbegin.Day + "").PadLeft(2, '0') + "' AS 时间 FROM DUAL ";
                    }
                    datestr += " UNION SELECT '合计' AS 时间 FROM DUAL ";
                } else if (totalMaster == "YYYYMM") {
                    datestr += " SELECT " + "'" + dateBegin.Year + "'||'" + (dateBegin.Month + "").PadLeft(2, '0') + "' AS 时间 FROM DUAL ";
                    //while (dbegin.Month < dateEnd.AddDays(-1).Month) {
                    //    dbegin = dbegin.AddMonths(1);
                    //    datestr += " UNION SELECT " + "'" + dbegin.Year + "'||'" + (dbegin.Month + "").PadLeft(2, '0') + "' AS 时间 FROM DUAL ";
                    //}
                    DateTime start = Convert.ToDateTime(dateBegin.ToShortDateString());
                    DateTime end = Convert.ToDateTime(dateEnd.ToShortDateString());
                    TimeSpan sp = end.Subtract(start);
                    while (DateTime.DaysInMonth(dateEnd.AddDays(-1).Year,dateEnd.AddDays(-1).Month)< sp.Days + dateBegin.Day - 1) {
                        dbegin = dbegin.AddMonths(1);
                        sp = end.Subtract(Convert.ToDateTime(dbegin.ToShortDateString()));
                        datestr += " UNION SELECT " + "'" + dbegin.Year + "'||'" + (dbegin.Month + "").PadLeft(2, '0') + "' AS 时间 FROM DUAL ";
                    }
                    datestr += " UNION SELECT '合计' AS 时间 FROM DUAL ";
                }
                string groutinglineId = context.Request["groutinglineMaster[]"] is object ? context.Request["groutinglineMaster[]"].ToString() : "0,0";
                if (groutinglineId == "0,0" && (context.Request["groutinglineMaster"] is object)) groutinglineId = context.Request["groutinglineMaster"].ToString();
                if (groutinglineId == "") groutinglineId = "0,0";

                string goodsId = context.Request["goodsMaster[]"] is object ? context.Request["goodsMaster[]"].ToString() : "0,0";
                if (goodsId == "0,0" && (context.Request["goodsMaster"] is object)) goodsId = context.Request["goodsMaster"].ToString();
                if (goodsId == "") goodsId = "0,0";

                string highFlag = context.Request["highFlagMaster"];
                if (highFlag == "") highFlag = "-1";

                string testType = context.Request["testMaster"];
                if (testType == "2") testType = "";

                string testFlagMaster = context.Request["testFlagMaster"];
                if (testFlagMaster == "2") testFlagMaster = "";

                string testmouldflag = " ";
                if (testType == "0")
                {
                    testmouldflag = " AND GDD.TESTMOULDFLAG = '0' ";
                }
                else if (testType == "1")
                {
                    testmouldflag = " AND GDD.TESTMOULDFLAG = '1' ";
                }

                string testflagmaster = " ";
                if (testFlagMaster == "0")
                {
                    testflagmaster = " AND GDD.TESTFLAG = '0' ";
                }
                else if (testFlagMaster == "1")
                {
                    testflagmaster = " AND GDD.TESTFLAG = '1' ";
                }

                //读取报表数据
                string sqlStr = @"
                        
                        WITH
                        拼接日期 AS 
                        ({DATESTR}),
                        注浆数 AS
                        (
                        SELECT
						GROUPING_ID (TO_CHAR(GH.CREATETIME, 'YYYYMM')) AS GID,
						NVL(TO_CHAR(GH.CREATETIME, 'YYYYMM'),'合计') AS 时间,
            SUM(CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001002') = 1 AND GH.DATATYPE = 1 THEN 1 ELSE 
								 CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001002') = 1 AND GH.DATATYPE != 1 THEN -1 END
								 END) AS 注浆数_一部全部,
 					  SUM(CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
								AND INSTR(GT.GOODSTYPECODE, '001001') = 1 AND GH.DATATYPE = 1 THEN 1 ELSE
								CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
								AND INSTR(GT.GOODSTYPECODE, '001001') = 1 AND GH.DATATYPE != 1 THEN -1 END
								END ) AS 注浆数_二部全部,
            SUM(CASE WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 AND GH.DATATYPE = 1 THEN 1 ELSE
								CASE WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 AND GH.DATATYPE != 1 THEN -1 END
						    END ) 注浆数_三部全部
       FROM TP_PM_GOODSCHANGEHISTORY GH
      INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
         ON GDD.GROUTINGDAILYDETAILID = GH.GROUTINGDAILYDETAILID
      INNER JOIN TP_MST_USER U
         ON U.USERID = GH.USERID
      INNER JOIN TP_MST_GOODS G
         ON G.GOODSID = GH.GOODSID
      INNER JOIN TP_MST_GOODSTYPE GT
         ON G.GOODSTYPEID = GT.GOODSTYPEID
      INNER JOIN TP_PC_GROUTINGLINE GL 
		 ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
      WHERE GH.CREATETIME >= @DATEBEGIN@
        AND GH.CREATETIME < @DATEEND@
				AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
			AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
			AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
			{TESTMOULDFLAG}
			{TESTFLAG}
        AND GH.DATATYPE IN (1, 2)
        AND G.SCRAPSUMFLAG = '1'
      GROUP BY GROUPING SETS (TO_CHAR(GH.CREATETIME, 'YYYYMM'),())
 ),
 成型报损 AS
 (
				SELECT
						GROUPING_ID (TO_CHAR(GH.CREATETIME, 'YYYYMM')) AS GID,
						NVL(TO_CHAR(GH.CREATETIME, 'YYYYMM'),'合计') AS 时间,
            SUM(CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001002') = 1 AND GH.DATATYPE = 3 THEN 1 ELSE 
								 CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001002') = 1 AND GH.DATATYPE != 3 THEN -1 END
								 END) AS 成型报损_一部全部,
 					  SUM(CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
								AND INSTR(GT.GOODSTYPECODE, '001001') = 1 AND GH.DATATYPE = 3 THEN 1 ELSE
								CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
								AND INSTR(GT.GOODSTYPECODE, '001001') = 1 AND GH.DATATYPE != 3 THEN -1 END
								END ) AS 成型报损_二部全部,
            SUM(CASE WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 AND GH.DATATYPE = 3 THEN 1 ELSE
								CASE WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 AND GH.DATATYPE != 3 THEN -1 END
						    END ) 成型报损_三部全部
       FROM
			TP_PM_GOODSCHANGEHISTORY GH
			INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = GH.GROUTINGDAILYDETAILID
			INNER JOIN TP_MST_USER U ON U.USERID = GH.USERID
			INNER JOIN TP_MST_GOODS G ON G.GOODSID = GH.GOODSID
			INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID
			INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
		WHERE
			GH.CREATETIME >= @DATEBEGIN@
			AND GH.CREATETIME < @DATEEND@
            AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
			AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
			AND (@HIGHPRESSUREFLAG@ = '-1' OR GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ )
			{TESTMOULDFLAG}
			{TESTFLAG}
			AND GH.DATATYPE IN ( 3, 4 ) 
			AND G.SCRAPSUMFLAG = '1' 
      GROUP BY GROUPING SETS (TO_CHAR(GH.CREATETIME, 'YYYYMM'),())
 ),
 半检不合格 AS
 (
	SELECT
GROUPING_ID (T.时间) AS GID,
NVL(T.时间,'合计') AS 时间,
SUM(T.一车间) 半检不合格_一部全部,
SUM(T.二车间) 半检不合格_二部全部,
SUM(T.三车间) 半检不合格_三部全部
FROM(
SELECT
			SUM(CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001002') = 1  THEN 1 ELSE 0
								 END) AS 一车间,
 					  SUM(CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
								AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN 1 ELSE 0
								END ) AS 二车间,
            SUM(CASE WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 THEN 1 ELSE 0
						    END ) 三车间,
						TO_CHAR(SP.AUDITDATE, 'YYYYMM') 时间
				FROM
					TP_PM_SCRAPPRODUCT SP
					INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE IN ( 20,30,40 ) 
					AND HGDI.ITEMTYPE = 2 
					AND HGDI.ITEMID = SP.PROCEDUREID
					INNER JOIN TP_PM_PRODUCTIONDATA PD ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
					INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
					INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
					INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID
					INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
				WHERE
					SP.AUDITSTATUS = 1 
					AND SP.AUDITDATE >= @DATEBEGIN@
					AND SP.AUDITDATE < @DATEEND@
					AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
			AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
			AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
			{TESTMOULDFLAG}
			{TESTFLAG}
					AND SP.SCRAPTYPE IN (2)
					GROUP BY TO_CHAR(SP.AUDITDATE, 'YYYYMM')
					UNION ALL-- 工序报损撤销
				SELECT
					SUM(CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001002') = 1  THEN -1 ELSE 0
								 END) AS 一车间,
 					  SUM(CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
								AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN -1 ELSE 0
								END ) AS 二车间,
            SUM(CASE WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 THEN -1 ELSE 0
						    END ) 三车间,
								TO_CHAR(SP.BACKOUTTIME, 'YYYYMM') 时间
						FROM
							TP_PM_SCRAPPRODUCT SP
							INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE IN ( 20,30,40 ) 
							AND HGDI.ITEMTYPE = 2 
							AND HGDI.ITEMID = SP.PROCEDUREID
							INNER JOIN TP_PM_PRODUCTIONDATA PD ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
							INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
							INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
							INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID
							INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
						WHERE
							SP.AUDITSTATUS = 1 
							AND SP.VALUEFLAG = '0' 
							AND SP.SCRAPTYPE IN (2)
							AND SP.BACKOUTTIME >= @DATEBEGIN@ 
							AND SP.BACKOUTTIME < @DATEEND@
							AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							{TESTMOULDFLAG}
							{TESTFLAG}
							GROUP BY TO_CHAR(SP.BACKOUTTIME, 'YYYYMM')
							--  AND SP.SCRAPTYPE IN (0, 2, 3)
							UNION ALL
							-- 干补报损
---------------------------------------------------
						SELECT
						COUNT(CASE WHEN WORKSHOP = 1 THEN 1 ELSE 0 END) 一车间,
						COUNT(CASE WHEN WORKSHOP = 2 THEN 1 ELSE 0 END) 二车间,
						COUNT(CASE WHEN WORKSHOP = 3 THEN 1 ELSE 0 END) 三车间,
						TO_CHAR(AUDITDATE, 'YYYYMM') 时间
							--COUNT( * ) SCRAPNUM
						FROM
							(
							SELECT
								SP.GOODSCODE,
								GDD.MATERIALCODE SAPCODE,
								PD.USERCODE,
							CASE
									
									WHEN HGDI.WORKSHOP = 2 
									AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN
										1 
										WHEN HGDI.WORKSHOP = 2 
										AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN
											2 
											WHEN HGDI.WORKSHOP = 3 THEN
											3 ELSE 4 
										END AS WORKSHOP,
										GDD.TESTMOULDFLAG,
										G.GOODS_LINE_CODE,
										SP.AUDITDATE,
										TO_CHAR( SP.AUDITDATE, 'yymm' ) || '5000E' AS CHARG --TO_CHAR(SP.AUDITDATE, 'yyyymm') AS CHARG
									FROM
										(
										SELECT
											T.* 
										FROM
											(
											SELECT
												SP.BARCODE,
												SP.GOODSCODE,
												SP.GROUTINGDAILYDETAILID,
												PD.PROCEDUREID,
												PD.PRODUCTIONDATAID,
												SP.AUDITDATE,
												RANK ( ) OVER ( PARTITION BY SP.BARCODE ORDER BY PD.PRODUCTIONDATAID DESC ) AS RK 
											FROM
												TP_PM_SCRAPPRODUCT SP
												INNER JOIN TP_PM_PRODUCTIONDATA PD ON SP.BARCODE = PD.BARCODE -- 不包含干补工序
												
												AND PD.PROCEDUREID <> 18 -- 要找干补之前的工序
												
												AND PD.PRODUCTIONDATAID < SP.PRODUCTIONDATAID 
												AND PD.VALUEFLAG = '1' 
												INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
							INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
							INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID
							INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
											WHERE
												SP.AUDITSTATUS = 1 
												AND SP.PROCEDUREID = 18 
												AND SP.SCRAPTYPE IN (2)
												AND SP.AUDITDATE >= @DATEBEGIN@
												AND SP.AUDITDATE < @DATEEND@
												AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
												AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
												AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
												{TESTMOULDFLAG}
												{TESTFLAG}
											) T 
										WHERE
											T.RK <= 1 
										) SP
										INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE IN ( 20,30,40 ) 
										AND HGDI.ITEMTYPE = 2 
										AND HGDI.ITEMID = SP.PROCEDUREID
										INNER JOIN TP_PM_PRODUCTIONDATA PD ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
										INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
										INNER JOIN TP_MST_GOODS G ON G.GOODSID = PD.GOODSID
										INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
									) GROUP BY TO_CHAR(AUDITDATE, 'YYYYMM') )T GROUP BY
		GROUPING SETS ( T.时间, ( ) )
 ),
复检不合格 AS 
 (
 	SELECT
GROUPING_ID (T.时间) AS GID,
NVL(T.时间,'合计') AS 时间,
SUM(T.一车间) 复检不合格_一部全部,
SUM(T.二车间) 复检不合格_二部全部,
SUM(T.三车间) 复检不合格_三部全部
FROM(
SELECT
			SUM(CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001002') = 1  THEN 1 ELSE 0
								 END) AS 一车间,
 					  SUM(CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
								AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN 1 ELSE 0
								END ) AS 二车间,
            SUM(CASE WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 THEN 1 ELSE 0
						    END ) 三车间,
						TO_CHAR(SP.AUDITDATE, 'YYYYMM') 时间
				FROM
					TP_PM_SCRAPPRODUCT SP
					INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE IN ( 20,30,40 ) 
					AND HGDI.ITEMTYPE = 2 
					AND HGDI.ITEMID = SP.PROCEDUREID
					INNER JOIN TP_PM_PRODUCTIONDATA PD ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
					INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
					INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
					INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
                    INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
				WHERE
					SP.AUDITSTATUS = 1 
					AND SP.AUDITDATE >= @DATEBEGIN@ 
					AND SP.AUDITDATE < @DATEEND@
                    AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
					AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
					AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
					{TESTMOULDFLAG}
					{TESTFLAG}
					AND SP.SCRAPTYPE IN (3)
					GROUP BY TO_CHAR(SP.AUDITDATE, 'YYYYMM')
					UNION ALL-- 工序报损撤销
				SELECT
					SUM(CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001002') = 1  THEN -1 ELSE 0
								 END) AS 一车间,
 					  SUM(CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
								AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN -1 ELSE 0
								END ) AS 二车间,
            SUM(CASE WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 THEN -1 ELSE 0
						    END ) 三车间,
								TO_CHAR(SP.BACKOUTTIME, 'YYYYMM') 时间
						FROM
							TP_PM_SCRAPPRODUCT SP
							INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE IN ( 20,30,40 ) 
							AND HGDI.ITEMTYPE = 2 
							AND HGDI.ITEMID = SP.PROCEDUREID
							INNER JOIN TP_PM_PRODUCTIONDATA PD ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
							INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
							INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
							INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
                            INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
						WHERE
							SP.AUDITSTATUS = 1 
							AND SP.VALUEFLAG = '0' 
							AND SP.SCRAPTYPE IN (3)
							AND SP.BACKOUTTIME >= @DATEBEGIN@ 
							AND SP.BACKOUTTIME < @DATEEND@
                            AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							{TESTMOULDFLAG}
							{TESTFLAG}
							GROUP BY TO_CHAR(SP.BACKOUTTIME, 'YYYYMM')
							--  AND SP.SCRAPTYPE IN (0, 2, 3)
							 UNION ALL
							-- 干补报损
---------------------------------------------------
						SELECT
						COUNT(CASE WHEN WORKSHOP = 1 THEN 1 ELSE 0 END) 一车间,
						COUNT(CASE WHEN WORKSHOP = 2 THEN 1 ELSE 0 END) 二车间,
						COUNT(CASE WHEN WORKSHOP = 3 THEN 1 ELSE 0 END) 三车间,
						TO_CHAR(AUDITDATE, 'YYYYMM') 时间
						FROM
							(
							SELECT
								SP.GOODSCODE,
								GDD.MATERIALCODE SAPCODE,
								PD.USERCODE,
							CASE
									WHEN HGDI.WORKSHOP = 2 
									AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN
										1 
										WHEN HGDI.WORKSHOP = 2 
										AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN
											2 
											WHEN HGDI.WORKSHOP = 3 THEN
											3 ELSE 4 
										END AS WORKSHOP,
										GDD.TESTMOULDFLAG,
										G.GOODS_LINE_CODE,
										SP.AUDITDATE
										
									FROM
										(
										SELECT
											T.* 
										FROM
											(
											SELECT
												SP.BARCODE,
												SP.GOODSCODE,
												SP.GROUTINGDAILYDETAILID,
												PD.PROCEDUREID,
												PD.PRODUCTIONDATAID,
												SP.AUDITDATE,
												RANK ( ) OVER ( PARTITION BY SP.BARCODE ORDER BY PD.PRODUCTIONDATAID DESC ) AS RK 
											FROM
												TP_PM_SCRAPPRODUCT SP
												INNER JOIN TP_PM_PRODUCTIONDATA PD ON SP.BARCODE = PD.BARCODE -- 不包含干补工序
												
												AND PD.PROCEDUREID <> 18 -- 要找干补之前的工序
												
												AND PD.PRODUCTIONDATAID < SP.PRODUCTIONDATAID 
												AND PD.VALUEFLAG = '1' 
                                                INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
							INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
							INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID
							INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
											WHERE
												SP.AUDITSTATUS = 1 
												AND SP.PROCEDUREID = 18 
												AND SP.SCRAPTYPE IN (3)
												AND SP.AUDITDATE >= @DATEBEGIN@ 
												AND SP.AUDITDATE < @DATEEND@
                                                AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							                    AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							                    AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							                    {TESTMOULDFLAG}
							                    {TESTFLAG}
											) T 
										WHERE
											T.RK <= 1 
										) SP
										INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE IN ( 20,30,40 ) 
										AND HGDI.ITEMTYPE = 2 
										AND HGDI.ITEMID = SP.PROCEDUREID
										INNER JOIN TP_PM_PRODUCTIONDATA PD ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
										INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
										INNER JOIN TP_MST_GOODS G ON G.GOODSID = PD.GOODSID
										INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
									) GROUP BY TO_CHAR(AUDITDATE, 'YYYYMM')
									)T GROUP BY
		GROUPING SETS ( T.时间, ( ) )
 ),
 半成品损坯数 AS
 (
		SELECT
GROUPING_ID (T.时间) AS GID,
NVL(T.时间,'合计') AS 时间,
SUM(T.一车间) 半成品损坯数_一部全部,
SUM(T.二车间) 半成品损坯数_二部全部,
SUM(T.三车间) 半成品损坯数_三部全部
FROM(
SELECT
			SUM( CASE WHEN HGDI.WORKSHOP = 2
                                             AND INSTR(GT.GOODSTYPECODE, '001002') = 1 THEN
                                         1 ELSE 0
								 END) AS 一车间,
 					  SUM(CASE WHEN HGDI.WORKSHOP = 2
                                             AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN
                                         1 ELSE 0
								END ) AS 二车间,
            SUM(CASE WHEN HGDI.WORKSHOP = 3 THEN   1 ELSE 0
						    END ) 三车间,
						TO_CHAR(SP.AUDITDATE, 'YYYYMM') 时间
				FROM
					TP_PM_SCRAPPRODUCT SP
					INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE IN ( 20,30,40 ) 
					AND HGDI.ITEMTYPE = 2 
					AND HGDI.ITEMID = SP.PROCEDUREID
					INNER JOIN TP_PM_PRODUCTIONDATA PD ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
					INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
					INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
					INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
                    INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
				WHERE
					SP.AUDITSTATUS = 1 
					AND SP.AUDITDATE >= @DATEBEGIN@ 
					AND SP.AUDITDATE < @DATEEND@ 
                     AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							{TESTMOULDFLAG}
							{TESTFLAG}
					AND SP.SCRAPTYPE IN (0)
					GROUP BY TO_CHAR(SP.AUDITDATE, 'YYYYMM')
					UNION ALL-- 工序报损撤销
				SELECT
					SUM( CASE WHEN HGDI.WORKSHOP = 2
                                             AND INSTR(GT.GOODSTYPECODE, '001002') = 1 THEN
                                         -1 ELSE 0
								 END) AS 一车间,
 					  SUM(CASE WHEN HGDI.WORKSHOP = 2
                                             AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN
                                         -1 ELSE 0
								END ) AS 二车间,
            SUM(CASE WHEN HGDI.WORKSHOP = 3 THEN   -1 ELSE 0
						    END ) 三车间,
								TO_CHAR(SP.BACKOUTTIME, 'YYYYMM') 时间
						FROM
							TP_PM_SCRAPPRODUCT SP
							INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE IN ( 20,30,40 ) 
							AND HGDI.ITEMTYPE = 2 
							AND HGDI.ITEMID = SP.PROCEDUREID
							INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
							INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
							INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
                            INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
						WHERE
							SP.AUDITSTATUS = 1 
							AND SP.VALUEFLAG = '0' 
							AND SP.SCRAPTYPE IN (0)
							AND SP.BACKOUTTIME >= @DATEBEGIN@ 
							AND SP.BACKOUTTIME < @DATEEND@ --  AND SP.SCRAPTYPE IN (0, 2, 3)
                             AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							{TESTMOULDFLAG}
							{TESTFLAG}
						GROUP BY TO_CHAR(SP.BACKOUTTIME, 'YYYYMM') UNION ALL
							-- 干补报损
---------------------------------------------------
						SELECT
						SUM(CASE WHEN WORKSHOP = 1 THEN 1 ELSE 0 END) 一车间,
						SUM(CASE WHEN WORKSHOP = 2 THEN 1 ELSE 0 END) 二车间,
						SUM(CASE WHEN WORKSHOP = 3 THEN 1 ELSE 0 END) 三车间,
						TO_CHAR(AUDITDATE, 'YYYYMM') 时间
						FROM
							(
							SELECT
								SP.GOODSCODE,
								GDD.MATERIALCODE SAPCODE,
								PD.USERCODE,
							CASE
									
									WHEN HGDI.WORKSHOP = 2 
									AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN
										1 
										WHEN HGDI.WORKSHOP = 2 
										AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN
											2 
											WHEN HGDI.WORKSHOP = 3 THEN
											3 ELSE 4 
										END AS WORKSHOP,
										GDD.TESTMOULDFLAG,
										G.GOODS_LINE_CODE,
										SP.AUDITDATE
										
									FROM
										(
										SELECT
											T.* 
										FROM
											(
											SELECT
												SP.BARCODE,
												SP.GOODSCODE,
												SP.GROUTINGDAILYDETAILID,
												PD.PROCEDUREID,
												PD.PRODUCTIONDATAID,
												SP.AUDITDATE,
												RANK ( ) OVER ( PARTITION BY SP.BARCODE ORDER BY PD.PRODUCTIONDATAID DESC ) AS RK 
											FROM
												TP_PM_SCRAPPRODUCT SP
												INNER JOIN TP_PM_PRODUCTIONDATA PD ON SP.BARCODE = PD.BARCODE -- 不包含干补工序
												
												AND PD.PROCEDUREID <> 18 -- 要找干补之前的工序
												
												AND PD.PRODUCTIONDATAID < SP.PRODUCTIONDATAID 
												AND PD.VALUEFLAG = '1' 
                                                INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
							                    INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
							                    INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
                                                INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
											WHERE
												SP.AUDITSTATUS = 1 
												AND SP.PROCEDUREID = 18 
												AND SP.SCRAPTYPE IN (0)
												AND SP.AUDITDATE >= @DATEBEGIN@ 
												AND SP.AUDITDATE < @DATEEND@ 
                                                AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							                    AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							                    AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							                    {TESTMOULDFLAG}
							                    {TESTFLAG}
											) T 
										WHERE
											T.RK <= 1 
										) SP
										INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE IN ( 20,30,40 ) 
										AND HGDI.ITEMTYPE = 2 
										AND HGDI.ITEMID = SP.PROCEDUREID
										INNER JOIN TP_PM_PRODUCTIONDATA PD ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
										INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
										INNER JOIN TP_MST_GOODS G ON G.GOODSID = PD.GOODSID
										INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
									)  GROUP BY TO_CHAR(AUDITDATE, 'YYYYMM')
									)T GROUP BY
		GROUPING SETS ( T.时间, ( ) )
 ),
 干补回收数 AS
 (
	SELECT
GROUPING_ID (T.时间) AS GID,
NVL(T.时间,'合计') AS 时间,
SUM(T.一车间) 干补回收数_一部全部,
SUM(T.二车间) 干补回收数_二部全部,
SUM(T.三车间) 干补回收数_三部全部
FROM(
SELECT
			SUM(CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001002') = 1  THEN 1 ELSE 0
								 END) AS 一车间,
 					  SUM(CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
								AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN 1 ELSE 0
								END ) AS 二车间,
            SUM(CASE WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 THEN 1 ELSE 0
						    END ) 三车间,
						TO_CHAR(SP.SPECIALREPAIRTIME, 'YYYYMM') 时间
			FROM
				TP_PM_SCRAPPRODUCT SP
				INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE IN ( '20', '30', '40' ) 
				AND HGDI.ITEMTYPE = 2 
				AND HGDI.ITEMID = SP.PROCEDUREID
				INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
				INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
				INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
                INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
			WHERE
				SP.AUDITSTATUS = 1 
				AND SP.GOODSLEVELTYPEID = 9 
				AND SP.SPECIALREPAIRTIME >= @DATEBEGIN@ 
				AND SP.SPECIALREPAIRTIME < @DATEEND@ 
                AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
				AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
				AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
				{TESTMOULDFLAG}
				{TESTFLAG}
			GROUP BY TO_CHAR(SP.SPECIALREPAIRTIME, 'YYYYMM'))T GROUP BY
		GROUPING SETS ( T.时间, ( ) )
 ),
 出窑数 AS
 (
								SELECT
					GROUPING_ID (TT2.时间) AS GID,
					NVL(TT2.时间,'合计') AS 时间,
					SUM(TT2.出窑数_一部全部) 出窑数_一部全部,
					SUM(TT2.出窑数_二部全部) 出窑数_二部全部,
					SUM(TT2.出窑数_三部全部) 出窑数_三部全部
					FROM(
 									SELECT
										TO_CHAR(TT.CREATETIME, 'YYYYMM') AS 时间,
										SUM(CASE WHEN TT.WORKSHOP = 1 THEN 1 ELSE 0 END) 出窑数_一部全部,
										SUM(CASE WHEN TT.WORKSHOP = 2 THEN 1 ELSE 0 END) 出窑数_二部全部,
										SUM(CASE WHEN TT.WORKSHOP = 3 THEN 1 ELSE 0 END) 出窑数_三部全部
										FROM(
															SELECT CASE
                                        WHEN T.IS3 > 0 THEN
                                         3
                                        WHEN INSTR(T.GOODSTYPECODE, '001002') = 1 THEN
                                         1
                                        WHEN INSTR(T.GOODSTYPECODE, '001001') = 1 THEN
                                         2
                                        ELSE
                                         2
                                    END AS WORKSHOP,
                                    T.*
                               FROM (SELECT DISTINCT PD.BARCODE,
                                                     PD.GOODSCODE,
																										 PD.CREATETIME,
                                                     GDD.MATERIALCODE SAPCODE,
                                                     PD.USERCODE,
                                                     DECODE(GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-') AS TESTMOULDFLAG,
                                                     G.GOODS_LINE_CODE,
                                                     TO_CHAR(PD.CREATETIME, 'yymm') || '5000E' AS CHARG,
                                                     --TO_CHAR(PD.CREATETIME, 'yyyymm') AS CHARG,
                                                     HGDI.WORKSHOP AS HGDIWORKSHOP,
                                                     GT.GOODSTYPECODE,
                                                     -- 如果3#卸窑（103），就是3车间的
                                                     (SELECT COUNT(1)
                                                        FROM TP_PM_PRODUCTIONDATA PD2
                                                       WHERE PD2.BARCODE = PD.BARCODE
                                                         AND PD2.PROCEDUREID = 103) AS IS3
                                       FROM TP_PM_PRODUCTIONDATA PD
                                      INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                         ON HGDI.DATACODE = '50'
                                        AND HGDI.ITEMTYPE = 1
                                        AND HGDI.ITEMID = PD.PROCEDUREID -- 3#成检交接（本烧重烧共用，且可以改判）
                                      INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                         ON GDD.GROUTINGDAILYDETAILID = PD.GROUTINGDAILYDETAILID
                                      INNER JOIN TP_MST_GOODS G
                                         ON G.GOODSID = PD.GOODSID
                                      INNER JOIN TP_MST_GOODSTYPE GT
                                         ON G.GOODSTYPEID = GT.GOODSTYPEID
                                       INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
                                      WHERE PD.CREATETIME >= @DATEBEGIN@
                                        AND PD.CREATETIME < @DATEEND@
                                         AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							            AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							            AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							            {TESTMOULDFLAG}
							            {TESTFLAG}
                                        AND PD.ISREFIRE = '0'
                                        AND PD.Islengbu = '0'
                                        --AND PD.KILNID IN(1,2,5)-- 取本烧的窑炉
                                        --AND ((HGDI.ITEMID <> 104 AND PD.VALUEFLAG = '1') OR
                                        --    (HGDI.ITEMID = 104 AND PD.ISREFIRE = '0' AND
                                        --    PD.CHECKFLAG = '1'))) T
                                         AND ((HGDI.ITEMID = 11 AND (PD.CHECKFLAG = '1' OR PD.CHECKFLAG IS NULL)) 
                                          OR (HGDI.ITEMID = 104 AND PD.CHECKFLAG = '1')) ) T )TT
                     GROUP BY TO_CHAR(TT.CREATETIME, 'YYYYMM') )TT2 GROUP BY
		GROUPING SETS ( TT2.时间, ( ) )
 ),
 回收数 AS
 (
 SELECT
					GROUPING_ID (TT2.时间) AS GID,
					NVL(TT2.时间,'合计') AS 时间,
					SUM(TT2.回收数_一部全部) 回收数_一部全部,
					SUM(TT2.回收数_二部全部) 回收数_二部全部,
					SUM(TT2.回收数_三部全部) 回收数_三部全部
					FROM(
SELECT
										TT.CHARG AS 时间,
										SUM(CASE WHEN TT.WORKSHOP = 1 AND TT.RECYCLINGFLAG = '1' THEN 1 ELSE 0 END) 回收数_一部全部,
										SUM(CASE WHEN TT.WORKSHOP = 2 AND TT.RECYCLINGFLAG = '1' THEN 1 ELSE 0 END) 回收数_二部全部,
										SUM(CASE WHEN TT.WORKSHOP = 3 AND TT.RECYCLINGFLAG = '1' THEN 1 ELSE 0 END) 回收数_三部全部
FROM(
SELECT BAR.GOODSCODE,
                                   BAR.MATERIALCODE SAPCODE,
                                   GH.FHUSERCODE USERCODE,
                                   O.ORDERNO,
                                   BAR.RECYCLINGFLAG,
                                   BAR.TESTMOULDFLAG,
                                   DECODE(BAR.TESTMOULDFLAG
                                         ,'0'
                                         ,''
                                         ,DECODE(G.GOODS_LINE_CODE, 'G', 'A', 'M', 'A', 'L', 'B', '')) AS ZSCS,
                                   CASE
                                       WHEN O.ORDERID IS NULL
                                            OR O.ORDERNO LIKE 'HEGII%' THEN
                                        ' '
                                       WHEN INSTR(O.ORDERNO, '/') = 0 THEN
                                        TO_CHAR(O.ORDERNO)
                                       ELSE
                                        TO_CHAR(SUBSTR(O.ORDERNO, 1, INSTR(O.ORDERNO, '/') - 1))
                                   END ORDERCODE -- 销售凭证
                                  ,
                                   CASE
                                       WHEN O.ORDERID IS NULL
                                            OR O.ORDERNO LIKE 'HEGII%'
                                            OR INSTR(O.ORDERNO, '/') = 0 THEN
                                        '0'
                                       WHEN INSTR(O.ORDERNO, '#') = 0 THEN
                                        TO_CHAR(SUBSTR(O.ORDERNO, INSTR(O.ORDERNO, '/') + 1))
                                       ELSE
                                        TO_CHAR(SUBSTR(O.ORDERNO
                                                      ,INSTR(O.ORDERNO, '/') + 1
                                                      ,INSTR(O.ORDERNO, '#') - INSTR(O.ORDERNO, '/') - 1))
                                   END ORDERITEM,
                                   CASE
                                       WHEN (INSTR(BAR.GROUTINGLINECODE, 'A') = 1 OR
                                            INSTR(BAR.GROUTINGLINECODE, 'D') = 1)
                                            AND INSTR(GT.GOODSTYPECODE, '001002') = 1 THEN
                                        1
                                       WHEN (INSTR(BAR.GROUTINGLINECODE, 'B') = 1 OR
                                            INSTR(BAR.GROUTINGLINECODE, 'D') = 1)
                                            AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN
                                        2
                                       WHEN INSTR(BAR.GROUTINGLINECODE, 'C') = 1 THEN
                                        3
                                       ELSE
                                        0
                                   END AS WORKSHOP,
                                   TO_CHAR(GH.FHTIME, 'YYYYMM') AS CHARG
                              FROM TP_PM_GROUTINGDAILYDETAIL BAR
                             INNER JOIN TP_PM_FINISHEDPRODUCT GH
                                ON BAR.GROUTINGDAILYDETAILID = GH.GROUTINGDAILYDETAILID
                             INNER JOIN TP_MST_GOODS G
                                ON G.GOODSID = BAR.GOODSID
                             INNER JOIN TP_MST_GOODSTYPE GT
                                ON GT.GOODSTYPEID = G.GOODSTYPEID
                              LEFT JOIN TP_PM_ORDER O
                                ON O.ORDERID = GH.FHORDERID
                             INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                         ON GDD.GROUTINGDAILYDETAILID = BAR.GROUTINGDAILYDETAILID
                             INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
                             WHERE GH.FHUSERID IS NOT NULL
                               AND GH.FHTIME >= @DATEBEGIN@
                                AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							{TESTMOULDFLAG}
							{TESTFLAG}
                               AND GH.FHTIME < @DATEEND@ )TT  GROUP BY TT.CHARG )TT2 GROUP BY
		GROUPING SETS ( TT2.时间, ( ) )
 ),
 入库数 AS
 (
 SELECT
					GROUPING_ID (TT2.时间) AS GID,
					NVL(TT2.时间,'合计') AS 时间,
					SUM(TT2.入库数_一部全部) 入库数_一部全部,
					SUM(TT2.入库数_二部全部) 入库数_二部全部,
					SUM(TT2.入库数_三部全部) 入库数_三部全部
					FROM(
SELECT
										TT.CHARG AS 时间,
										SUM(CASE WHEN TT.WORKSHOP = 1 AND TT.RECYCLINGFLAG = '0' THEN 1 ELSE 0 END) 入库数_一部全部,
										SUM(CASE WHEN TT.WORKSHOP = 2 AND TT.RECYCLINGFLAG = '0' THEN 1 ELSE 0 END) 入库数_二部全部,
										SUM(CASE WHEN TT.WORKSHOP = 3 AND TT.RECYCLINGFLAG = '0' THEN 1 ELSE 0 END) 入库数_三部全部
FROM(
SELECT BAR.GOODSCODE,
                                   BAR.MATERIALCODE SAPCODE,
                                   GH.FHUSERCODE USERCODE,
                                   O.ORDERNO,
                                   BAR.RECYCLINGFLAG,
                                   BAR.TESTMOULDFLAG,
                                   DECODE(BAR.TESTMOULDFLAG
                                         ,'0'
                                         ,''
                                         ,DECODE(G.GOODS_LINE_CODE, 'G', 'A', 'M', 'A', 'L', 'B', '')) AS ZSCS,
                                   CASE
                                       WHEN O.ORDERID IS NULL
                                            OR O.ORDERNO LIKE 'HEGII%' THEN
                                        ' '
                                       WHEN INSTR(O.ORDERNO, '/') = 0 THEN
                                        TO_CHAR(O.ORDERNO)
                                       ELSE
                                        TO_CHAR(SUBSTR(O.ORDERNO, 1, INSTR(O.ORDERNO, '/') - 1))
                                   END ORDERCODE -- 销售凭证
                                  ,
                                   CASE
                                       WHEN O.ORDERID IS NULL
                                            OR O.ORDERNO LIKE 'HEGII%'
                                            OR INSTR(O.ORDERNO, '/') = 0 THEN
                                        '0'
                                       WHEN INSTR(O.ORDERNO, '#') = 0 THEN
                                        TO_CHAR(SUBSTR(O.ORDERNO, INSTR(O.ORDERNO, '/') + 1))
                                       ELSE
                                        TO_CHAR(SUBSTR(O.ORDERNO
                                                      ,INSTR(O.ORDERNO, '/') + 1
                                                      ,INSTR(O.ORDERNO, '#') - INSTR(O.ORDERNO, '/') - 1))
                                   END ORDERITEM,
                                   CASE
                                       WHEN (INSTR(BAR.GROUTINGLINECODE, 'A') = 1 OR
                                            INSTR(BAR.GROUTINGLINECODE, 'D') = 1)
                                            AND INSTR(GT.GOODSTYPECODE, '001002') = 1 THEN
                                        1
                                       WHEN (INSTR(BAR.GROUTINGLINECODE, 'B') = 1 OR
                                            INSTR(BAR.GROUTINGLINECODE, 'D') = 1)
                                            AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN
                                        2
                                       WHEN INSTR(BAR.GROUTINGLINECODE, 'C') = 1 THEN
                                        3
                                       ELSE
                                        0
                                   END AS WORKSHOP,
                                   TO_CHAR(GH.FHTIME, 'YYYYMM') AS CHARG
                              FROM TP_PM_GROUTINGDAILYDETAIL BAR
                             INNER JOIN TP_PM_FINISHEDPRODUCT GH
                                ON BAR.GROUTINGDAILYDETAILID = GH.GROUTINGDAILYDETAILID
                             INNER JOIN TP_MST_GOODS G
                                ON G.GOODSID = BAR.GOODSID
                             INNER JOIN TP_MST_GOODSTYPE GT
                                ON GT.GOODSTYPEID = G.GOODSTYPEID
                              LEFT JOIN TP_PM_ORDER O
                                ON O.ORDERID = GH.FHORDERID
                             INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                         ON GDD.GROUTINGDAILYDETAILID = BAR.GROUTINGDAILYDETAILID
                             INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
                             WHERE GH.FHUSERID IS NOT NULL
                               AND GH.FHTIME >= @DATEBEGIN@
                               AND GH.FHTIME < @DATEEND@ 
                                AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							{TESTMOULDFLAG}
							{TESTFLAG}
                                )TT  GROUP BY TT.CHARG)TT2 GROUP BY
		GROUPING SETS ( TT2.时间, ( ) )
 ),
 质量登记次品数 AS
 (
 SELECT
					GROUPING_ID (TT2.时间) AS GID,
					NVL(TT2.时间,'合计') AS 时间,
					SUM(TT2.质量登记次品数_一部全部) 质量登记次品数_一部全部,
					SUM(TT2.质量登记次品数_二部全部) 质量登记次品数_二部全部,
					SUM(TT2.质量登记次品数_三部全部) 质量登记次品数_三部全部
					FROM(
SELECT
										TT.CHARG AS 时间,
										SUM(CASE WHEN TT.WORKSHOP = 1  THEN SCRAPNUM ELSE 0 END) 质量登记次品数_一部全部,
										SUM(CASE WHEN TT.WORKSHOP = 2  THEN SCRAPNUM ELSE 0 END) 质量登记次品数_二部全部,
										SUM(CASE WHEN TT.WORKSHOP = 3  THEN SCRAPNUM ELSE 0 END) 质量登记次品数_三部全部
										FROM(
SELECT GOODSCODE,
                            SAPCODE,
                            USERCODE,
                            0 OUTPUTNUM,
                            COUNT(*) SCRAPNUM,
                            0 CLEANUPNUM,
                            0 REPAIRNUM,
                            WORKSHOP,
                            TESTMOULDFLAG,
                            GOODS_LINE_CODE,
                            CHARG
                      FROM (SELECT CASE
                                        WHEN T.IS3 > 0 THEN
                                         3
                                        WHEN T.HGDIWORKSHOP = 2
                                             AND INSTR(T.GOODSTYPECODE, '001002') = 1 THEN
                                         1
                                        WHEN T.HGDIWORKSHOP = 2
                                             AND INSTR(T.GOODSTYPECODE, '001001') = 1 THEN
                                         2
                                        WHEN T.HGDIWORKSHOP = 3 THEN
                                         3
                                        ELSE
                                         2
                                    END AS WORKSHOP,
                                    T.*
                               FROM (SELECT SP.BARCODE,
                                            SP.GOODSCODE,
                                            GDD.MATERIALCODE SAPCODE,
                                            PD.USERCODE,
                                            GT.GOODSTYPECODE,
                                            DECODE(GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-') AS TESTMOULDFLAG,
                                            G.GOODS_LINE_CODE,
                                            TO_CHAR(SP.AUDITDATE, 'YYYYMM')  AS CHARG,
                                            --TO_CHAR(SP.AUDITDATE, 'yyyymm') AS CHARG,
                                            HGDI.WORKSHOP AS HGDIWORKSHOP,
                                            -- 如果参与了重烧，则判断这个条码是否走过3#卸窑
                                            (SELECT COUNT(1)
                                               FROM TP_PM_PRODUCTIONDATA PD2
                                              WHERE PD2.BARCODE = PD.BARCODE
                                                AND PD2.PROCEDUREID = 103
                                                   -- 重烧相关的ID
                                                AND SP.PROCEDUREID IN (14, 16, 57, 58, 59, 60，113)) AS IS3
                                       FROM TP_PM_SCRAPPRODUCT SP
                                      INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                         ON HGDI.DATACODE = '50'
                                        AND HGDI.ITEMTYPE = 2
                                        AND HGDI.ITEMID = SP.PROCEDUREID
                                      INNER JOIN TP_PM_PRODUCTIONDATA PD
                                         ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
                                      INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                         ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                                      INNER JOIN TP_MST_GOODS G
                                         ON G.GOODSID = PD.GOODSID
                                      INNER JOIN TP_MST_GOODSTYPE GT
                                         ON G.GOODSTYPEID = GT.GOODSTYPEID
                                      INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
                                      WHERE SP.AUDITSTATUS = 1
                                        AND SP.AUDITDATE >= @DATEBEGIN@
                                        AND SP.AUDITDATE < @DATEEND@
                                         AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							{TESTMOULDFLAG}
							{TESTFLAG}
																				AND PD.ISREFIRE = 0
																			  AND SP.GOODSLEVELTYPEID in (7)
																				--AND SP.SCRAPTYPE = 1
                                     --AND SP.SCRAPTYPE IN (0, 2, 3) 
                                     ) T)
                     GROUP BY GOODSCODE,
                               SAPCODE,
                               USERCODE,
                               WORKSHOP,
                               TESTMOULDFLAG,
                               GOODS_LINE_CODE,
                               CHARG
                    ---------------------------------------------------                      
                    UNION ALL
                    -- 工序报损撤销
                    ---------------------------------------------------                      
                    SELECT GOODSCODE,
                            SAPCODE,
                            USERCODE,
                            0 OUTPUTNUM,
                            0 - COUNT(*) SCRAPNUM,
                            0 CLEANUPNUM,
                            0 REPAIRNUM,
                            WORKSHOP,
                            TESTMOULDFLAG,
                            GOODS_LINE_CODE,
                            CHARG
                      FROM (SELECT CASE
                                        WHEN T.IS3 > 0 THEN
                                         3
                                        WHEN T.HGDIWORKSHOP = 2
                                             AND INSTR(T.GOODSTYPECODE, '001002') = 1 THEN
                                         1
                                        WHEN T.HGDIWORKSHOP = 2
                                             AND INSTR(T.GOODSTYPECODE, '001001') = 1 THEN
                                         2
                                        WHEN T.HGDIWORKSHOP = 3 THEN
                                         3
                                        ELSE
                                         2
                                    END AS WORKSHOP,
                                    T.*
                               FROM (SELECT SP.BARCODE,
                                            SP.GOODSCODE,
                                            GDD.MATERIALCODE SAPCODE,
                                            PD.USERCODE,
                                            GT.GOODSTYPECODE,
                                            DECODE(GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-') AS TESTMOULDFLAG,
                                            G.GOODS_LINE_CODE,
                                            TO_CHAR(SP.BACKOUTTIME, 'YYYYMM')  AS CHARG,
                                            --TO_CHAR(SP.AUDITDATE, 'yyyymm') AS CHARG,
                                            HGDI.WORKSHOP AS HGDIWORKSHOP,
                                            -- 如果参与了重烧，则判断这个条码是否走过3#卸窑
                                            (SELECT COUNT(1)
                                               FROM TP_PM_PRODUCTIONDATA PD2
                                              WHERE PD2.BARCODE = PD.BARCODE
                                                AND PD2.PROCEDUREID = 103
                                                   -- 重烧相关的ID
                                                AND SP.PROCEDUREID IN (14, 16, 57, 58, 59, 60，113)) AS IS3
                                       FROM TP_PM_SCRAPPRODUCT SP
                                      INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                         ON HGDI.DATACODE = '50'
                                        AND HGDI.ITEMTYPE = 2
                                        AND HGDI.ITEMID = SP.PROCEDUREID
                                      INNER JOIN TP_PM_PRODUCTIONDATA PD
                                         ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
                                      INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                         ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                                      INNER JOIN TP_MST_GOODS G
                                         ON G.GOODSID = PD.GOODSID
                                      INNER JOIN TP_MST_GOODSTYPE GT
                                         ON G.GOODSTYPEID = GT.GOODSTYPEID
                                      INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
                                      WHERE SP.AUDITSTATUS = 1
                                        AND SP.BACKOUTTIME >= @DATEBEGIN@
                                        AND SP.BACKOUTTIME < @DATEEND@
                                         AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							{TESTMOULDFLAG}
							{TESTFLAG}
																				AND PD.ISREFIRE = 0
																				AND SP.GOODSLEVELTYPEID in (7)
																				--AND SP.SCRAPTYPE = 1
                                     --AND SP.SCRAPTYPE IN (0, 2, 3) 
                                     ) T)
                     GROUP BY GOODSCODE,
                               SAPCODE,
                               USERCODE,
                               WORKSHOP,
                               TESTMOULDFLAG,
                               GOODS_LINE_CODE,
                               CHARG
                    
                    ---------------------------------------------------         
                    UNION ALL
                    -- 干补报损
                    ---------------------------------------------------       
                    SELECT GOODSCODE,
                            SAPCODE,
                            USERCODE,
                            0 OUTPUTNUM,
                            COUNT(*) SCRAPNUM,
                            0 CLEANUPNUM,
                            0 REPAIRNUM,
                            WORKSHOP,
                            TESTMOULDFLAG,
                            GOODS_LINE_CODE,
                            CHARG
                      FROM (SELECT SP.GOODSCODE,
                                    GDD.MATERIALCODE SAPCODE,
                                    PD.USERCODE,
                                    CASE
                                        WHEN HGDI.WORKSHOP = 2
                                             AND INSTR(GT.GOODSTYPECODE, '001002') = 1 THEN
                                         1
                                        WHEN HGDI.WORKSHOP = 2
                                             AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN
                                         2
                                        WHEN HGDI.WORKSHOP = 3 THEN
                                         3
                                        ELSE
                                         4
                                    END AS WORKSHOP,
                                    DECODE(GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-') AS TESTMOULDFLAG,
                                    G.GOODS_LINE_CODE,
                                    TO_CHAR(SP.AUDITDATE, 'YYYYMM')  AS CHARG
                                    --TO_CHAR(SP.AUDITDATE, 'yyyymm') AS CHARG
                               FROM (SELECT T.*
                                       FROM (SELECT SP.BARCODE,
                                                    SP.GOODSCODE,
                                                    SP.GROUTINGDAILYDETAILID,
                                                    PD.PROCEDUREID,
                                                    PD.PRODUCTIONDATAID,
                                                    SP.AUDITDATE,
                                                    RANK() OVER(PARTITION BY SP.BARCODE ORDER BY PD.PRODUCTIONDATAID DESC) AS RK
                                               FROM TP_PM_SCRAPPRODUCT SP
                                              INNER JOIN TP_PM_PRODUCTIONDATA PD ON SP.BARCODE = PD.BARCODE
                                                   -- 不包含干补工序
                                                AND PD.PROCEDUREID <> 18
                                                   -- 要找干补之前的工序
                                                AND PD.PRODUCTIONDATAID < SP.PRODUCTIONDATAID
                                                AND PD.VALUEFLAG = '1'
                                              INNER JOIN TP_MST_GOODS G
                                                ON G.GOODSID = PD.GOODSID
                                              INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                                 ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                                              INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
                                              WHERE SP.AUDITSTATUS = 1
                                                AND SP.PROCEDUREID = 18
																				AND PD.ISREFIRE = 0
																				AND SP.GOODSLEVELTYPEID in (7)
																				--AND SP.SCRAPTYPE = 1
                                                AND SP.AUDITDATE >= @DATEBEGIN@
                                                AND SP.AUDITDATE < @DATEEND@
                                                 AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							{TESTMOULDFLAG}
							{TESTFLAG}
                                                ) T
                                      WHERE T.RK <= 1) SP
                              INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                 ON HGDI.DATACODE = '50'
                                AND HGDI.ITEMTYPE = 2
                                AND HGDI.ITEMID = SP.PROCEDUREID
                              INNER JOIN TP_PM_PRODUCTIONDATA PD
                                 ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
                              INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                 ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                              INNER JOIN TP_MST_GOODS G
                                 ON G.GOODSID = PD.GOODSID
                              INNER JOIN TP_MST_GOODSTYPE GT
                                 ON G.GOODSTYPEID = GT.GOODSTYPEID)
                     GROUP BY GOODSCODE,
                               SAPCODE,
                               USERCODE,
                               WORKSHOP,
                               TESTMOULDFLAG,
                               GOODS_LINE_CODE,
                               CHARG)TT
                     GROUP BY TT.CHARG )TT2 GROUP BY GROUPING SETS ( TT2.时间, ( ) )
 ),
 质量登记损坏数 AS
 (
 SELECT
					GROUPING_ID (TT2.时间) AS GID,
					NVL(TT2.时间,'合计') AS 时间,
					SUM(TT2.质量登记损坏数_一部全部) 质量登记损坏数_一部全部,
					SUM(TT2.质量登记损坏数_二部全部) 质量登记损坏数_二部全部,
					SUM(TT2.质量登记损坏数_三部全部) 质量登记损坏数_三部全部
					FROM(
SELECT
										TT.CHARG AS 时间,
										SUM(CASE WHEN TT.WORKSHOP = 1  THEN SCRAPNUM ELSE 0 END) 质量登记损坏数_一部全部,
										SUM(CASE WHEN TT.WORKSHOP = 2  THEN SCRAPNUM ELSE 0 END) 质量登记损坏数_二部全部,
										SUM(CASE WHEN TT.WORKSHOP = 3  THEN SCRAPNUM ELSE 0 END) 质量登记损坏数_三部全部
										FROM(
SELECT GOODSCODE,
                            SAPCODE,
                            USERCODE,
                            0 OUTPUTNUM,
                            COUNT(*) SCRAPNUM,
                            0 CLEANUPNUM,
                            0 REPAIRNUM,
                            WORKSHOP,
                            TESTMOULDFLAG,
                            GOODS_LINE_CODE,
                            CHARG
                      FROM (SELECT CASE
                                        WHEN T.IS3 > 0 THEN
                                         3
                                        WHEN T.HGDIWORKSHOP = 2
                                             AND INSTR(T.GOODSTYPECODE, '001002') = 1 THEN
                                         1
                                        WHEN T.HGDIWORKSHOP = 2
                                             AND INSTR(T.GOODSTYPECODE, '001001') = 1 THEN
                                         2
                                        WHEN T.HGDIWORKSHOP = 3 THEN
                                         3
                                        ELSE
                                         2
                                    END AS WORKSHOP,
                                    T.*
                               FROM (SELECT SP.BARCODE,
                                            SP.GOODSCODE,
                                            GDD.MATERIALCODE SAPCODE,
                                            PD.USERCODE,
                                            GT.GOODSTYPECODE,
                                            DECODE(GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-') AS TESTMOULDFLAG,
                                            G.GOODS_LINE_CODE,
                                            TO_CHAR(SP.AUDITDATE, 'YYYYMM')  AS CHARG,
                                            --TO_CHAR(SP.AUDITDATE, 'yyyymm') AS CHARG,
                                            HGDI.WORKSHOP AS HGDIWORKSHOP,
                                            -- 如果参与了重烧，则判断这个条码是否走过3#卸窑
                                            (SELECT COUNT(1)
                                               FROM TP_PM_PRODUCTIONDATA PD2
                                              WHERE PD2.BARCODE = PD.BARCODE
                                                AND PD2.PROCEDUREID = 103
                                                   -- 重烧相关的ID
                                                AND SP.PROCEDUREID IN (14, 16, 57, 58, 59, 60，113)) AS IS3
                                       FROM TP_PM_SCRAPPRODUCT SP
                                      INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                         ON HGDI.DATACODE = '50'
                                        AND HGDI.ITEMTYPE = 2
                                        AND HGDI.ITEMID = SP.PROCEDUREID
                                      INNER JOIN TP_PM_PRODUCTIONDATA PD
                                         ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
                                      INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                         ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                                      INNER JOIN TP_MST_GOODS G
                                         ON G.GOODSID = PD.GOODSID
                                      INNER JOIN TP_MST_GOODSTYPE GT
                                         ON G.GOODSTYPEID = GT.GOODSTYPEID
                                              INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
                                      WHERE SP.AUDITSTATUS = 1
                                        AND SP.AUDITDATE >= @DATEBEGIN@
                                        AND SP.AUDITDATE < @DATEEND@
                                         AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							{TESTMOULDFLAG}
							{TESTFLAG}
																				AND PD.ISREFIRE = 0
																			  AND SP.GOODSLEVELTYPEID in (8,9)
																				--AND SP.SCRAPTYPE = 1
                                     --AND SP.SCRAPTYPE IN (0, 2, 3) 
                                     ) T)
                     GROUP BY GOODSCODE,
                               SAPCODE,
                               USERCODE,
                               WORKSHOP,
                               TESTMOULDFLAG,
                               GOODS_LINE_CODE,
                               CHARG
                    ---------------------------------------------------                      
                    UNION ALL
                    -- 工序报损撤销
                    ---------------------------------------------------                      
                    SELECT GOODSCODE,
                            SAPCODE,
                            USERCODE,
                            0 OUTPUTNUM,
                            0 - COUNT(*) SCRAPNUM,
                            0 CLEANUPNUM,
                            0 REPAIRNUM,
                            WORKSHOP,
                            TESTMOULDFLAG,
                            GOODS_LINE_CODE,
                            CHARG
                      FROM (SELECT CASE
                                        WHEN T.IS3 > 0 THEN
                                         3
                                        WHEN T.HGDIWORKSHOP = 2
                                             AND INSTR(T.GOODSTYPECODE, '001002') = 1 THEN
                                         1
                                        WHEN T.HGDIWORKSHOP = 2
                                             AND INSTR(T.GOODSTYPECODE, '001001') = 1 THEN
                                         2
                                        WHEN T.HGDIWORKSHOP = 3 THEN
                                         3
                                        ELSE
                                         2
                                    END AS WORKSHOP,
                                    T.*
                               FROM (SELECT SP.BARCODE,
                                            SP.GOODSCODE,
                                            GDD.MATERIALCODE SAPCODE,
                                            PD.USERCODE,
                                            GT.GOODSTYPECODE,
                                            DECODE(GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-') AS TESTMOULDFLAG,
                                            G.GOODS_LINE_CODE,
                                            TO_CHAR(SP.BACKOUTTIME, 'YYYYMM')  AS CHARG,
                                            --TO_CHAR(SP.AUDITDATE, 'yyyymm') AS CHARG,
                                            HGDI.WORKSHOP AS HGDIWORKSHOP,
                                            -- 如果参与了重烧，则判断这个条码是否走过3#卸窑
                                            (SELECT COUNT(1)
                                               FROM TP_PM_PRODUCTIONDATA PD2
                                              WHERE PD2.BARCODE = PD.BARCODE
                                                AND PD2.PROCEDUREID = 103
                                                   -- 重烧相关的ID
                                                AND SP.PROCEDUREID IN (14, 16, 57, 58, 59, 60，113)) AS IS3
                                       FROM TP_PM_SCRAPPRODUCT SP
                                      INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                         ON HGDI.DATACODE = '50'
                                        AND HGDI.ITEMTYPE = 2
                                        AND HGDI.ITEMID = SP.PROCEDUREID
                                      INNER JOIN TP_PM_PRODUCTIONDATA PD
                                         ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
                                      INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                         ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                                      INNER JOIN TP_MST_GOODS G
                                         ON G.GOODSID = PD.GOODSID
                                      INNER JOIN TP_MST_GOODSTYPE GT
                                         ON G.GOODSTYPEID = GT.GOODSTYPEID
                                      INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
                                      WHERE SP.AUDITSTATUS = 1
                                        AND SP.BACKOUTTIME >= @DATEBEGIN@
                                        AND SP.BACKOUTTIME < @DATEEND@
                                         AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							{TESTMOULDFLAG}
							{TESTFLAG}
																				AND PD.ISREFIRE = 0
																				AND SP.GOODSLEVELTYPEID in (8,9)
																				--AND SP.SCRAPTYPE = 1
                                     --AND SP.SCRAPTYPE IN (0, 2, 3) 
                                     ) T)
                     GROUP BY GOODSCODE,
                               SAPCODE,
                               USERCODE,
                               WORKSHOP,
                               TESTMOULDFLAG,
                               GOODS_LINE_CODE,
                               CHARG
                    
                    ---------------------------------------------------         
                    UNION ALL
                    -- 干补报损
                    ---------------------------------------------------       
                    SELECT GOODSCODE,
                            SAPCODE,
                            USERCODE,
                            0 OUTPUTNUM,
                            COUNT(*) SCRAPNUM,
                            0 CLEANUPNUM,
                            0 REPAIRNUM,
                            WORKSHOP,
                            TESTMOULDFLAG,
                            GOODS_LINE_CODE,
                            CHARG
                      FROM (SELECT SP.GOODSCODE,
                                    GDD.MATERIALCODE SAPCODE,
                                    PD.USERCODE,
                                    CASE
                                        WHEN HGDI.WORKSHOP = 2
                                             AND INSTR(GT.GOODSTYPECODE, '001002') = 1 THEN
                                         1
                                        WHEN HGDI.WORKSHOP = 2
                                             AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN
                                         2
                                        WHEN HGDI.WORKSHOP = 3 THEN
                                         3
                                        ELSE
                                         4
                                    END AS WORKSHOP,
                                    DECODE(GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-') AS TESTMOULDFLAG,
                                    G.GOODS_LINE_CODE,
                                    TO_CHAR(SP.AUDITDATE, 'YYYYMM') AS CHARG
                                    --TO_CHAR(SP.AUDITDATE, 'yyyymm') AS CHARG
                               FROM (SELECT T.*
                                       FROM (SELECT SP.BARCODE,
                                                    SP.GOODSCODE,
                                                    SP.GROUTINGDAILYDETAILID,
                                                    PD.PROCEDUREID,
                                                    PD.PRODUCTIONDATAID,
                                                    SP.AUDITDATE,
                                                    RANK() OVER(PARTITION BY SP.BARCODE ORDER BY PD.PRODUCTIONDATAID DESC) AS RK
                                               FROM TP_PM_SCRAPPRODUCT SP
                                              INNER JOIN TP_PM_PRODUCTIONDATA PD ON SP.BARCODE = PD.BARCODE
                                                   -- 不包含干补工序
                                                AND PD.PROCEDUREID <> 18
                                                   -- 要找干补之前的工序
                                                AND PD.PRODUCTIONDATAID < SP.PRODUCTIONDATAID
                                                AND PD.VALUEFLAG = '1'
                                              INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                                 ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                                              INNER JOIN TP_MST_GOODS G
                                                 ON G.GOODSID = PD.GOODSID
                                              INNER JOIN TP_MST_GOODSTYPE GT
                                                 ON G.GOODSTYPEID = GT.GOODSTYPEID
                                              INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
                                              WHERE SP.AUDITSTATUS = 1
                                                AND SP.PROCEDUREID = 18
																				AND PD.ISREFIRE = 0
																				AND SP.GOODSLEVELTYPEID in (8,9)
																				--AND SP.SCRAPTYPE = 1
                                                AND SP.AUDITDATE >= @DATEBEGIN@
                                                AND SP.AUDITDATE < @DATEEND@
                                                 AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							{TESTMOULDFLAG}
							{TESTFLAG}
                                                ) T
                                      WHERE T.RK <= 1) SP
                              INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                 ON HGDI.DATACODE = '50'
                                AND HGDI.ITEMTYPE = 2
                                AND HGDI.ITEMID = SP.PROCEDUREID
                              INNER JOIN TP_PM_PRODUCTIONDATA PD
                                 ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
                              INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                 ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                              INNER JOIN TP_MST_GOODS G
                                 ON G.GOODSID = PD.GOODSID
                              INNER JOIN TP_MST_GOODSTYPE GT
                                 ON G.GOODSTYPEID = GT.GOODSTYPEID)
                     GROUP BY GOODSCODE,
                               SAPCODE,
                               USERCODE,
                               WORKSHOP,
                               TESTMOULDFLAG,
                               GOODS_LINE_CODE,
                               CHARG
															 )TT
                     GROUP BY TT.CHARG )TT2 GROUP BY GROUPING SETS ( TT2.时间, ( ) )
 ),
 重烧登记次品数 AS
 (
 SELECT
					GROUPING_ID (TT2.时间) AS GID,
					NVL(TT2.时间,'合计') AS 时间,
					SUM(TT2.重烧登记次品数_一部全部) 重烧登记次品数_一部全部,
					SUM(TT2.重烧登记次品数_二部全部) 重烧登记次品数_二部全部,
					SUM(TT2.重烧登记次品数_三部全部) 重烧登记次品数_三部全部
					FROM(
SELECT
										TT.CHARG AS 时间,
										SUM(CASE WHEN TT.WORKSHOP = 1  THEN SCRAPNUM ELSE 0 END) 重烧登记次品数_一部全部,
										SUM(CASE WHEN TT.WORKSHOP = 2  THEN SCRAPNUM ELSE 0 END) 重烧登记次品数_二部全部,
										SUM(CASE WHEN TT.WORKSHOP = 3  THEN SCRAPNUM ELSE 0 END) 重烧登记次品数_三部全部
										FROM(
SELECT GOODSCODE,
                            SAPCODE,
                            USERCODE,
                            0 OUTPUTNUM,
                            COUNT(*) SCRAPNUM,
                            0 CLEANUPNUM,
                            0 REPAIRNUM,
                            WORKSHOP,
                            TESTMOULDFLAG,
                            GOODS_LINE_CODE,
                            CHARG
                      FROM (SELECT CASE
                                        WHEN T.IS3 > 0 THEN
                                         3
                                        WHEN T.HGDIWORKSHOP = 2
                                             AND INSTR(T.GOODSTYPECODE, '001002') = 1 THEN
                                         1
                                        WHEN T.HGDIWORKSHOP = 2
                                             AND INSTR(T.GOODSTYPECODE, '001001') = 1 THEN
                                         2
                                        WHEN T.HGDIWORKSHOP = 3 THEN
                                         3
                                        ELSE
                                         2
                                    END AS WORKSHOP,
                                    T.*
                               FROM (SELECT SP.BARCODE,
                                            SP.GOODSCODE,
                                            GDD.MATERIALCODE SAPCODE,
                                            PD.USERCODE,
                                            GT.GOODSTYPECODE,
                                            DECODE(GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-') AS TESTMOULDFLAG,
                                            G.GOODS_LINE_CODE,
                                            TO_CHAR(SP.AUDITDATE, 'YYYYMM')  AS CHARG,
                                            --TO_CHAR(SP.AUDITDATE, 'yyyymm') AS CHARG,
                                            HGDI.WORKSHOP AS HGDIWORKSHOP,
                                            -- 如果参与了重烧，则判断这个条码是否走过3#卸窑
                                            (SELECT COUNT(1)
                                               FROM TP_PM_PRODUCTIONDATA PD2
                                              WHERE PD2.BARCODE = PD.BARCODE
                                                AND PD2.PROCEDUREID = 103
                                                   -- 重烧相关的ID
                                                AND SP.PROCEDUREID IN (14, 16, 57, 58, 59, 60，113)) AS IS3
                                       FROM TP_PM_SCRAPPRODUCT SP
                                      INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                         ON HGDI.DATACODE = '50'
                                        AND HGDI.ITEMTYPE = 2
                                        AND HGDI.ITEMID = SP.PROCEDUREID
                                      INNER JOIN TP_PM_PRODUCTIONDATA PD
                                         ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
                                      INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                         ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                                      INNER JOIN TP_MST_GOODS G
                                         ON G.GOODSID = PD.GOODSID
                                      INNER JOIN TP_MST_GOODSTYPE GT
                                         ON G.GOODSTYPEID = GT.GOODSTYPEID
                                      INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
                                      WHERE SP.AUDITSTATUS = 1
                                        AND SP.AUDITDATE >= @DATEBEGIN@
                                        AND SP.AUDITDATE < @DATEEND@
                                         AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							{TESTMOULDFLAG}
							{TESTFLAG}
																				AND PD.ISREFIRE = 6
																			  AND SP.GOODSLEVELTYPEID in (7)
																				--AND SP.SCRAPTYPE = 1
                                     --AND SP.SCRAPTYPE IN (0, 2, 3) 
                                     ) T)
                     GROUP BY GOODSCODE,
                               SAPCODE,
                               USERCODE,
                               WORKSHOP,
                               TESTMOULDFLAG,
                               GOODS_LINE_CODE,
                               CHARG
                    ---------------------------------------------------                      
                    UNION ALL
                    -- 工序报损撤销
                    ---------------------------------------------------                      
                    SELECT GOODSCODE,
                            SAPCODE,
                            USERCODE,
                            0 OUTPUTNUM,
                            0 - COUNT(*) SCRAPNUM,
                            0 CLEANUPNUM,
                            0 REPAIRNUM,
                            WORKSHOP,
                            TESTMOULDFLAG,
                            GOODS_LINE_CODE,
                            CHARG
                      FROM (SELECT CASE
                                        WHEN T.IS3 > 0 THEN
                                         3
                                        WHEN T.HGDIWORKSHOP = 2
                                             AND INSTR(T.GOODSTYPECODE, '001002') = 1 THEN
                                         1
                                        WHEN T.HGDIWORKSHOP = 2
                                             AND INSTR(T.GOODSTYPECODE, '001001') = 1 THEN
                                         2
                                        WHEN T.HGDIWORKSHOP = 3 THEN
                                         3
                                        ELSE
                                         2
                                    END AS WORKSHOP,
                                    T.*
                               FROM (SELECT SP.BARCODE,
                                            SP.GOODSCODE,
                                            GDD.MATERIALCODE SAPCODE,
                                            PD.USERCODE,
                                            GT.GOODSTYPECODE,
                                            DECODE(GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-') AS TESTMOULDFLAG,
                                            G.GOODS_LINE_CODE,
                                            TO_CHAR(SP.BACKOUTTIME, 'YYYYMM')  AS CHARG,
                                            --TO_CHAR(SP.AUDITDATE, 'yyyymm') AS CHARG,
                                            HGDI.WORKSHOP AS HGDIWORKSHOP,
                                            -- 如果参与了重烧，则判断这个条码是否走过3#卸窑
                                            (SELECT COUNT(1)
                                               FROM TP_PM_PRODUCTIONDATA PD2
                                              WHERE PD2.BARCODE = PD.BARCODE
                                                AND PD2.PROCEDUREID = 103
                                                   -- 重烧相关的ID
                                                AND SP.PROCEDUREID IN (14, 16, 57, 58, 59, 60，113)) AS IS3
                                       FROM TP_PM_SCRAPPRODUCT SP
                                      INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                         ON HGDI.DATACODE = '50'
                                        AND HGDI.ITEMTYPE = 2
                                        AND HGDI.ITEMID = SP.PROCEDUREID
                                      INNER JOIN TP_PM_PRODUCTIONDATA PD
                                         ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
                                      INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                         ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                                      INNER JOIN TP_MST_GOODS G
                                         ON G.GOODSID = PD.GOODSID
                                      INNER JOIN TP_MST_GOODSTYPE GT
                                         ON G.GOODSTYPEID = GT.GOODSTYPEID
                                      INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
                                      WHERE SP.AUDITSTATUS = 1
                                        AND SP.BACKOUTTIME >= @DATEBEGIN@
                                        AND SP.BACKOUTTIME < @DATEEND@
                                         AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							{TESTMOULDFLAG}
							{TESTFLAG}
																				AND PD.ISREFIRE = 6
																				AND SP.GOODSLEVELTYPEID in (7)
																				--AND SP.SCRAPTYPE = 1
                                     --AND SP.SCRAPTYPE IN (0, 2, 3) 
                                     ) T)
                     GROUP BY GOODSCODE,
                               SAPCODE,
                               USERCODE,
                               WORKSHOP,
                               TESTMOULDFLAG,
                               GOODS_LINE_CODE,
                               CHARG
                    
                    ---------------------------------------------------         
                    UNION ALL
                    -- 干补报损
                    ---------------------------------------------------       
                    SELECT GOODSCODE,
                            SAPCODE,
                            USERCODE,
                            0 OUTPUTNUM,
                            COUNT(*) SCRAPNUM,
                            0 CLEANUPNUM,
                            0 REPAIRNUM,
                            WORKSHOP,
                            TESTMOULDFLAG,
                            GOODS_LINE_CODE,
                            CHARG
                      FROM (SELECT SP.GOODSCODE,
                                    GDD.MATERIALCODE SAPCODE,
                                    PD.USERCODE,
                                    CASE
                                        WHEN HGDI.WORKSHOP = 2
                                             AND INSTR(GT.GOODSTYPECODE, '001002') = 1 THEN
                                         1
                                        WHEN HGDI.WORKSHOP = 2
                                             AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN
                                         2
                                        WHEN HGDI.WORKSHOP = 3 THEN
                                         3
                                        ELSE
                                         4
                                    END AS WORKSHOP,
                                    DECODE(GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-') AS TESTMOULDFLAG,
                                    G.GOODS_LINE_CODE,
                                    TO_CHAR(SP.AUDITDATE, 'YYYYMM')  AS CHARG
                                    --TO_CHAR(SP.AUDITDATE, 'yyyymm') AS CHARG
                               FROM (SELECT T.*
                                       FROM (SELECT SP.BARCODE,
                                                    SP.GOODSCODE,
                                                    SP.GROUTINGDAILYDETAILID,
                                                    PD.PROCEDUREID,
                                                    PD.PRODUCTIONDATAID,
                                                    SP.AUDITDATE,
                                                    RANK() OVER(PARTITION BY SP.BARCODE ORDER BY PD.PRODUCTIONDATAID DESC) AS RK
                                               FROM TP_PM_SCRAPPRODUCT SP
                                              INNER JOIN TP_PM_PRODUCTIONDATA PD ON SP.BARCODE = PD.BARCODE
                                                   -- 不包含干补工序
                                                AND PD.PROCEDUREID <> 18
                                                   -- 要找干补之前的工序
                                                AND PD.PRODUCTIONDATAID < SP.PRODUCTIONDATAID
                                                AND PD.VALUEFLAG = '1'
                                              INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                                 ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                                              INNER JOIN TP_MST_GOODS G
                                                 ON G.GOODSID = PD.GOODSID
                                              INNER JOIN TP_MST_GOODSTYPE GT
                                                 ON G.GOODSTYPEID = GT.GOODSTYPEID
                                              INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
                                              WHERE SP.AUDITSTATUS = 1
                                                AND SP.PROCEDUREID = 18
																				AND PD.ISREFIRE = 6
																				AND SP.GOODSLEVELTYPEID in (7)
																				--AND SP.SCRAPTYPE = 1
                                                AND SP.AUDITDATE >= @DATEBEGIN@
                                                AND SP.AUDITDATE < @DATEEND@
                                                 AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							{TESTMOULDFLAG}
							{TESTFLAG}                                                
                            ) T
                                      WHERE T.RK <= 1) SP
                              INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                 ON HGDI.DATACODE = '50'
                                AND HGDI.ITEMTYPE = 2
                                AND HGDI.ITEMID = SP.PROCEDUREID
                              INNER JOIN TP_PM_PRODUCTIONDATA PD
                                 ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
                              INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                 ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                              INNER JOIN TP_MST_GOODS G
                                 ON G.GOODSID = PD.GOODSID
                              INNER JOIN TP_MST_GOODSTYPE GT
                                 ON G.GOODSTYPEID = GT.GOODSTYPEID)
                     GROUP BY GOODSCODE,
                               SAPCODE,
                               USERCODE,
                               WORKSHOP,
                               TESTMOULDFLAG,
                               GOODS_LINE_CODE,
                               CHARG )TT
                     GROUP BY TT.CHARG )TT2 GROUP BY GROUPING SETS ( TT2.时间, ( ) )
 ),
 重烧登记损坯数 AS
 (
 SELECT
					GROUPING_ID (TT2.时间) AS GID,
					NVL(TT2.时间,'合计') AS 时间,
					SUM(TT2.重烧登记损坯数_一部全部) 重烧登记损坯数_一部全部,
					SUM(TT2.重烧登记损坯数_二部全部) 重烧登记损坯数_二部全部,
					SUM(TT2.重烧登记损坯数_三部全部) 重烧登记损坯数_三部全部
					FROM(
SELECT
										TT.CHARG AS 时间,
										SUM(CASE WHEN TT.WORKSHOP = 1  THEN SCRAPNUM ELSE 0 END) 重烧登记损坯数_一部全部,
										SUM(CASE WHEN TT.WORKSHOP = 2  THEN SCRAPNUM ELSE 0 END) 重烧登记损坯数_二部全部,
										SUM(CASE WHEN TT.WORKSHOP = 3  THEN SCRAPNUM ELSE 0 END) 重烧登记损坯数_三部全部
										FROM(
SELECT GOODSCODE,
                            SAPCODE,
                            USERCODE,
                            0 OUTPUTNUM,
                            COUNT(*) SCRAPNUM,
                            0 CLEANUPNUM,
                            0 REPAIRNUM,
                            WORKSHOP,
                            TESTMOULDFLAG,
                            GOODS_LINE_CODE,
                            CHARG
                      FROM (SELECT CASE
                                        WHEN T.IS3 > 0 THEN
                                         3
                                        WHEN T.HGDIWORKSHOP = 2
                                             AND INSTR(T.GOODSTYPECODE, '001002') = 1 THEN
                                         1
                                        WHEN T.HGDIWORKSHOP = 2
                                             AND INSTR(T.GOODSTYPECODE, '001001') = 1 THEN
                                         2
                                        WHEN T.HGDIWORKSHOP = 3 THEN
                                         3
                                        ELSE
                                         2
                                    END AS WORKSHOP,
                                    T.*
                               FROM (SELECT SP.BARCODE,
                                            SP.GOODSCODE,
                                            GDD.MATERIALCODE SAPCODE,
                                            PD.USERCODE,
                                            GT.GOODSTYPECODE,
                                            DECODE(GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-') AS TESTMOULDFLAG,
                                            G.GOODS_LINE_CODE,
                                            TO_CHAR(SP.AUDITDATE, 'YYYYMM')  AS CHARG,
                                            --TO_CHAR(SP.AUDITDATE, 'yyyymm') AS CHARG,
                                            HGDI.WORKSHOP AS HGDIWORKSHOP,
                                            -- 如果参与了重烧，则判断这个条码是否走过3#卸窑
                                            (SELECT COUNT(1)
                                               FROM TP_PM_PRODUCTIONDATA PD2
                                              WHERE PD2.BARCODE = PD.BARCODE
                                                AND PD2.PROCEDUREID = 103
                                                   -- 重烧相关的ID
                                                AND SP.PROCEDUREID IN (14, 16, 57, 58, 59, 60，113)) AS IS3
                                       FROM TP_PM_SCRAPPRODUCT SP
                                      INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                         ON HGDI.DATACODE = '50'
                                        AND HGDI.ITEMTYPE = 2
                                        AND HGDI.ITEMID = SP.PROCEDUREID
                                      INNER JOIN TP_PM_PRODUCTIONDATA PD
                                         ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
                                      INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                         ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                                      INNER JOIN TP_MST_GOODS G
                                         ON G.GOODSID = PD.GOODSID
                                      INNER JOIN TP_MST_GOODSTYPE GT
                                         ON G.GOODSTYPEID = GT.GOODSTYPEID
                                      INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
                                      WHERE SP.AUDITSTATUS = 1
                                        AND SP.AUDITDATE >= @DATEBEGIN@
                                        AND SP.AUDITDATE < @DATEEND@
                                         AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							{TESTMOULDFLAG}
							{TESTFLAG}
																				AND PD.ISREFIRE = 6
																			  AND SP.GOODSLEVELTYPEID in (8,9)
																				--AND SP.SCRAPTYPE = 1
                                     --AND SP.SCRAPTYPE IN (0, 2, 3) 
                                     ) T)
                     GROUP BY GOODSCODE,
                               SAPCODE,
                               USERCODE,
                               WORKSHOP,
                               TESTMOULDFLAG,
                               GOODS_LINE_CODE,
                               CHARG
                    ---------------------------------------------------                      
                    UNION ALL
                    -- 工序报损撤销
                    ---------------------------------------------------                      
                    SELECT GOODSCODE,
                            SAPCODE,
                            USERCODE,
                            0 OUTPUTNUM,
                            0 - COUNT(*) SCRAPNUM,
                            0 CLEANUPNUM,
                            0 REPAIRNUM,
                            WORKSHOP,
                            TESTMOULDFLAG,
                            GOODS_LINE_CODE,
                            CHARG
                      FROM (SELECT CASE
                                        WHEN T.IS3 > 0 THEN
                                         3
                                        WHEN T.HGDIWORKSHOP = 2
                                             AND INSTR(T.GOODSTYPECODE, '001002') = 1 THEN
                                         1
                                        WHEN T.HGDIWORKSHOP = 2
                                             AND INSTR(T.GOODSTYPECODE, '001001') = 1 THEN
                                         2
                                        WHEN T.HGDIWORKSHOP = 3 THEN
                                         3
                                        ELSE
                                         2
                                    END AS WORKSHOP,
                                    T.*
                               FROM (SELECT SP.BARCODE,
                                            SP.GOODSCODE,
                                            GDD.MATERIALCODE SAPCODE,
                                            PD.USERCODE,
                                            GT.GOODSTYPECODE,
                                            DECODE(GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-') AS TESTMOULDFLAG,
                                            G.GOODS_LINE_CODE,
                                            TO_CHAR(SP.AUDITDATE, 'YYYYMM')  AS CHARG,
                                            --TO_CHAR(SP.AUDITDATE, 'yyyymm') AS CHARG,
                                            HGDI.WORKSHOP AS HGDIWORKSHOP,
                                            -- 如果参与了重烧，则判断这个条码是否走过3#卸窑
                                            (SELECT COUNT(1)
                                               FROM TP_PM_PRODUCTIONDATA PD2
                                              WHERE PD2.BARCODE = PD.BARCODE
                                                AND PD2.PROCEDUREID = 103
                                                   -- 重烧相关的ID
                                                AND SP.PROCEDUREID IN (14, 16, 57, 58, 59, 60，113)) AS IS3
                                       FROM TP_PM_SCRAPPRODUCT SP
                                      INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                         ON HGDI.DATACODE = '50'
                                        AND HGDI.ITEMTYPE = 2
                                        AND HGDI.ITEMID = SP.PROCEDUREID
                                      INNER JOIN TP_PM_PRODUCTIONDATA PD
                                         ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
                                      INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                         ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                                      INNER JOIN TP_MST_GOODS G
                                         ON G.GOODSID = PD.GOODSID
                                      INNER JOIN TP_MST_GOODSTYPE GT
                                         ON G.GOODSTYPEID = GT.GOODSTYPEID
                                      INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
                                      WHERE SP.AUDITSTATUS = 1
                                        AND SP.BACKOUTTIME >= @DATEBEGIN@
                                        AND SP.BACKOUTTIME < @DATEEND@
                                         AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							{TESTMOULDFLAG}
							{TESTFLAG}
																				AND PD.ISREFIRE = 6
																				AND SP.GOODSLEVELTYPEID in (8,9)
																				--AND SP.SCRAPTYPE = 1
                                     --AND SP.SCRAPTYPE IN (0, 2, 3) 
                                     ) T)
                     GROUP BY GOODSCODE,
                               SAPCODE,
                               USERCODE,
                               WORKSHOP,
                               TESTMOULDFLAG,
                               GOODS_LINE_CODE,
                               CHARG
                    
                    ---------------------------------------------------         
                    UNION ALL
                    -- 干补报损
                    ---------------------------------------------------       
                    SELECT GOODSCODE,
                            SAPCODE,
                            USERCODE,
                            0 OUTPUTNUM,
                            COUNT(*) SCRAPNUM,
                            0 CLEANUPNUM,
                            0 REPAIRNUM,
                            WORKSHOP,
                            TESTMOULDFLAG,
                            GOODS_LINE_CODE,
                            CHARG
                      FROM (SELECT SP.GOODSCODE,
                                    GDD.MATERIALCODE SAPCODE,
                                    PD.USERCODE,
                                    CASE
                                        WHEN HGDI.WORKSHOP = 2
                                             AND INSTR(GT.GOODSTYPECODE, '001002') = 1 THEN
                                         1
                                        WHEN HGDI.WORKSHOP = 2
                                             AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN
                                         2
                                        WHEN HGDI.WORKSHOP = 3 THEN
                                         3
                                        ELSE
                                         4
                                    END AS WORKSHOP,
                                    DECODE(GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-') AS TESTMOULDFLAG,
                                    G.GOODS_LINE_CODE,
                                    TO_CHAR(SP.AUDITDATE, 'YYYYMM') AS CHARG
                                    --TO_CHAR(SP.AUDITDATE, 'yyyymm') AS CHARG
                               FROM (SELECT T.*
                                       FROM (SELECT SP.BARCODE,
                                                    SP.GOODSCODE,
                                                    SP.GROUTINGDAILYDETAILID,
                                                    PD.PROCEDUREID,
                                                    PD.PRODUCTIONDATAID,
                                                    SP.AUDITDATE,
                                                    RANK() OVER(PARTITION BY SP.BARCODE ORDER BY PD.PRODUCTIONDATAID DESC) AS RK
                                               FROM TP_PM_SCRAPPRODUCT SP
                                              INNER JOIN TP_PM_PRODUCTIONDATA PD ON SP.BARCODE = PD.BARCODE
                                                   -- 不包含干补工序
                                                AND PD.PROCEDUREID <> 18
                                                   -- 要找干补之前的工序
                                                AND PD.PRODUCTIONDATAID < SP.PRODUCTIONDATAID
                                                AND PD.VALUEFLAG = '1'
                                              INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                         ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                                      INNER JOIN TP_MST_GOODS G
                                         ON G.GOODSID = PD.GOODSID
                                      INNER JOIN TP_MST_GOODSTYPE GT
                                         ON G.GOODSTYPEID = GT.GOODSTYPEID
                                      INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
                                              WHERE SP.AUDITSTATUS = 1
                                                AND SP.PROCEDUREID = 18
																				AND PD.ISREFIRE = 6
																				AND SP.GOODSLEVELTYPEID in (8,9)
																				--AND SP.SCRAPTYPE = 1
                                                AND SP.AUDITDATE >= @DATEBEGIN@
                                                AND SP.AUDITDATE < @DATEEND@
                                                 AND (G.GOODSID IN (0,0) OR '0,0' = '0,0') 
							AND (GDD.GROUTINGLINEID IN (0,0) OR '0,0' = '0,0') 
							AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
							{TESTMOULDFLAG}
							{TESTFLAG}
                                                ) T
                                      WHERE T.RK <= 1) SP
                              INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                 ON HGDI.DATACODE = '50'
                                AND HGDI.ITEMTYPE = 2
                                AND HGDI.ITEMID = SP.PROCEDUREID
                              INNER JOIN TP_PM_PRODUCTIONDATA PD
                                 ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
                              INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                 ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                              INNER JOIN TP_MST_GOODS G
                                 ON G.GOODSID = PD.GOODSID
                              INNER JOIN TP_MST_GOODSTYPE GT
                                 ON G.GOODSTYPEID = GT.GOODSTYPEID)
                     GROUP BY GOODSCODE,
                               SAPCODE,
                               USERCODE,
                               WORKSHOP,
                               TESTMOULDFLAG,
                               GOODS_LINE_CODE,
                               CHARG)TT
                     GROUP BY TT.CHARG )TT2 GROUP BY GROUPING SETS ( TT2.时间, ( ) )
 )

    SELECT
		拼接日期.时间,
	NVL(注浆数.注浆数_一部全部,0) 注浆数_一部全部,
	NVL(注浆数.注浆数_二部全部,0) 注浆数_二部全部,
	NVL(注浆数.注浆数_三部全部,0) 注浆数_三部全部,
    NVL(成型报损.成型报损_一部全部,0) 成型报损_一部全部,
	NVL(成型报损.成型报损_二部全部,0) 成型报损_二部全部,
	NVL(成型报损.成型报损_三部全部,0) 成型报损_三部全部,
	NVL(半检不合格.半检不合格_一部全部,0) 半检不合格_一部全部,
	NVL(半检不合格.半检不合格_二部全部,0) 半检不合格_二部全部,
	NVL(半检不合格.半检不合格_三部全部,0) 半检不合格_三部全部,
	NVL(复检不合格.复检不合格_一部全部,0) 复检不合格_一部全部,
	NVL(复检不合格.复检不合格_二部全部,0) 复检不合格_二部全部,
	NVL(复检不合格.复检不合格_三部全部,0) 复检不合格_三部全部,
	NVL(半成品损坯数.半成品损坯数_一部全部,0) 半成品损坯数_一部全部,
	NVL(半成品损坯数.半成品损坯数_二部全部,0) 半成品损坯数_二部全部,
	NVL(半成品损坯数.半成品损坯数_三部全部,0) 半成品损坯数_三部全部,
	NVL(干补回收数.干补回收数_一部全部,0) 干补回收数_一部全部,
	NVL(干补回收数.干补回收数_二部全部,0) 干补回收数_二部全部,
	NVL(干补回收数.干补回收数_三部全部,0) 干补回收数_三部全部,
	NVL(出窑数.出窑数_一部全部,0) 出窑数_一部全部,
	NVL(出窑数.出窑数_二部全部,0) 出窑数_二部全部,
	NVL(出窑数.出窑数_三部全部,0) 出窑数_三部全部,
	NVL(质量登记次品数.质量登记次品数_一部全部,0) 质量登记次品数_一部全部,
	NVL(质量登记次品数.质量登记次品数_二部全部,0) 质量登记次品数_二部全部,
	NVL(质量登记次品数.质量登记次品数_三部全部,0) 质量登记次品数_三部全部,
	NVL(重烧登记次品数.重烧登记次品数_一部全部,0) 重烧登记次品数_一部全部,
	NVL(重烧登记次品数.重烧登记次品数_二部全部,0) 重烧登记次品数_二部全部,
	NVL(重烧登记次品数.重烧登记次品数_三部全部,0) 重烧登记次品数_三部全部,
	NVL(质量登记损坏数.质量登记损坏数_一部全部,0) 质量登记损坏数_一部全部,
	NVL(质量登记损坏数.质量登记损坏数_二部全部,0) 质量登记损坏数_二部全部,
	NVL(质量登记损坏数.质量登记损坏数_三部全部,0) 质量登记损坏数_三部全部,
	NVL(重烧登记损坯数.重烧登记损坯数_一部全部,0) 重烧登记损坯数_一部全部,
	NVL(重烧登记损坯数.重烧登记损坯数_二部全部,0) 重烧登记损坯数_二部全部,
	NVL(重烧登记损坯数.重烧登记损坯数_三部全部,0) 重烧登记损坯数_三部全部,
	NVL(回收数.回收数_一部全部,0) 回收数_一部全部,
	NVL(回收数.回收数_二部全部,0) 回收数_二部全部,
	NVL(回收数.回收数_三部全部,0) 回收数_三部全部,
	NVL(入库数.入库数_一部全部,0) 入库数_一部全部,
	NVL(入库数.入库数_二部全部,0) 入库数_二部全部,
	NVL(入库数.入库数_三部全部,0) 入库数_三部全部
    FROM 拼接日期
	LEFT JOIN 注浆数 ON 拼接日期.时间 = 注浆数.时间
	LEFT JOIN 成型报损 ON 拼接日期.时间 = 成型报损.时间
	LEFT JOIN 半检不合格 ON 拼接日期.时间 = 半检不合格.时间
	LEFT JOIN 复检不合格 ON 拼接日期.时间 = 复检不合格.时间
	LEFT JOIN 半成品损坯数 ON 拼接日期.时间 = 半成品损坯数.时间
	LEFT JOIN 干补回收数 ON 拼接日期.时间 = 干补回收数.时间
	LEFT JOIN 出窑数 ON 拼接日期.时间 = 出窑数.时间
	LEFT JOIN 回收数 ON 拼接日期.时间 = 回收数.时间
	LEFT JOIN 入库数 ON 拼接日期.时间 = 入库数.时间
	LEFT JOIN 质量登记次品数 ON 拼接日期.时间 = 质量登记次品数.时间
	LEFT JOIN 质量登记损坏数 ON 拼接日期.时间 = 质量登记损坏数.时间
	LEFT JOIN 重烧登记次品数 ON 拼接日期.时间 = 重烧登记次品数.时间
	LEFT JOIN 重烧登记损坯数 ON 拼接日期.时间 = 重烧登记损坯数.时间
                ".Replace("YYYYMM",totalMaster)
                 .Replace("{GOODSID}",goodsId)
                 .Replace("{GROUTINGLINEID}",groutinglineId)
                 .Replace("{DATESTR}",datestr)
                 .Replace("{TESTMOULDFLAG}", testmouldflag)
                 .Replace("{TESTFLAG}",testflagmaster);

                     
                //获取查询条件
                if (context.Request["checke"].ToString()=="false")
                {
                    //highFlag ="null";
                    testType = "-1";
                    testFlagMaster = "-1";
                }
                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("HIGHPRESSUREFLAG",highFlag));
                sqlPara.Add(new CDAParameter("TESTMOULDFLAG",testType));
                sqlPara.Add(new CDAParameter("TESTFLAG",testFlagMaster));

                //sqlPara.Add(new CDAParameter("PROCEDUREID", context.Request["procedureidMaster"]));

                //行列互换
                //===============================================================================
                DataTable dt = conn.ExecuteDatatable(sqlStr, sqlPara.ToArray());
                DataTable dt2 = new DataTable();
                dt2.Columns.Add("部门序号");
                dt2.Columns.Add("部门");
                dt2.Columns.Add("工序");
                for (int i = 0; i < dt.Rows.Count; i++) dt2.Columns.Add("日期" + dt.Rows[i]["时间"].ToString());
                for (int j = 1; j < dt.Columns.Count; j++)
                {
                    DataRow dr = dt2.NewRow();
                    dr["部门序号"] = dt.Columns[j].ColumnName.Substring(dt.Columns[j].ColumnName.IndexOf("_")+1)
                            .Replace("一部全部","1").Replace("二部全部","2").Replace("三部全部","3");
                    dr["部门"] = dt.Columns[j].ColumnName.Substring(dt.Columns[j].ColumnName.IndexOf("_")+1);
                    dr["工序"] = dt.Columns[j].ColumnName.Substring(0, dt.Columns[j].ColumnName.IndexOf("_"));
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        dr["日期" + dt.Rows[i]["时间"].ToString()] = dt.Rows[i][dt.Columns[j].ColumnName];
                    }
                    dt2.Rows.Add(dr);
                }
                DataView dv = dt2.DefaultView;
                dv.Sort = "部门序号";
                dt2.Columns.Remove("部门序号");
                dt2 = dv.ToTable();
                context.Response.Write(new JsonResult(dt2) { total = dt.Rows.Count }.ToJson());
            }

            //子表1
            if(context.Request["m"].ToString()=="1")
            {
                //读取报表数据
                string sqlStr = @"
                    SELECT
TT1.BARCODE 条码
--TT1.车间
FROM(
SELECT
TT.BARCODE,
CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001002') = 1 THEN 1
		 WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN 2
		 WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 THEN 3 ELSE 0 END 车间
FROM(
SELECT GDD.BARCODE,'1' nodetype,GDD.groutingdailydetailid FROM(
SELECT s.groutingdailydetailid,s.outputnum,NVL(s.counts,0),NVL(s.countt,0)
  FROM (SELECT a.groutingdailydetailid,a.outputnum,a.counts,tt.countt
          FROM (SELECT groutingdailydetailid,outputnum,COUNT(*) counts
                  FROM (SELECT decode(gh.datatype, 1, 1, -1) outputnum,gdd.groutingdailydetailid
                          FROM tp_pm_goodschangehistory gh
                         INNER JOIN tp_pm_groutingdailydetail gdd
                            ON gdd.groutingdailydetailid = gh.groutingdailydetailid
                         INNER JOIN tp_mst_goods g
                            ON g.goodsid = gh.goodsid
                         INNER JOIN tp_mst_goodstype gt
                            ON g.goodstypeid = gt.goodstypeid
                         WHERE gh.createtime >= @DATEBEGIN@
                           AND gh.createtime < @DATEEND@
                           AND gh.datatype IN (1, 2)
                           AND g.scrapsumflag = '1')
                 GROUP BY groutingdailydetailid,outputnum) a
          LEFT JOIN (SELECT groutingdailydetailid,outputnum,COUNT(*) countt
                      FROM tsap_hegii_datalog_bg_detail THDBD
											LEFT JOIN TSAP_HEGII_DATALOG_BG THDB ON THDBD.LOGID = THDB.LOGID
                     WHERE THDBD.outputnum NOT IN (0)
										 AND THDB.DATACODE = 20
										 AND THDB.YYYYMMDD>=TO_CHAR(@DATEBEGIN@,'YYYYMMDD') AND THDB.YYYYMMDD<TO_CHAR(@DATEEND@,'YYYYMMDD')
                     GROUP BY groutingdailydetailid,outputnum) tt
            ON tt.groutingdailydetailid = a.groutingdailydetailid
           AND tt.outputnum = a.outputnum ) s
 WHERE NVL(s.counts,0)!=NVL(s.countt,0))T
 LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON T.groutingdailydetailid = GDD.groutingdailydetailid
 UNION ALL
 SELECT GDD.BARCODE,'2' nodetype,gdd.groutingdailydetailid FROM(
 SELECT s.groutingdailydetailid,s.SCRAPNUM,NVL(s.counts,0),NVL(s.countt,0)
  FROM (SELECT a.groutingdailydetailid,a.SCRAPNUM,a.counts,tt.countt
          FROM (SELECT
TT.GROUTINGDAILYDETAILID,
TT.SCRAPNUM,
COUNT(*) counts
FROM(
SELECT
	gdd.groutingdailydetailid,
	 DECODE( GH.DATATYPE, 3, 1, - 1 )SCRAPNUM
		FROM
			TP_PM_GOODSCHANGEHISTORY GH
			INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = GH.GROUTINGDAILYDETAILID
			INNER JOIN TP_MST_USER U ON U.USERID = GH.USERID
			INNER JOIN TP_MST_GOODS G ON G.GOODSID = GH.GOODSID
			INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
		WHERE
			GH.CREATETIME >= @DATEBEGIN@ 
			AND GH.CREATETIME < @DATEEND@
			AND GH.DATATYPE IN ( 3, 4 ) 
			AND G.SCRAPSUMFLAG = '1' 
		 UNION ALL-- 工序报损
		SELECT
		gdd.groutingdailydetailid,
			1  SCRAPNUM
				FROM
					TP_PM_SCRAPPRODUCT SP
					INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE IN ( 20,30,40 ) 
					AND HGDI.ITEMTYPE = 2 
					AND HGDI.ITEMID = SP.PROCEDUREID
					INNER JOIN TP_PM_PRODUCTIONDATA PD ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
					INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
					INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
					INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
				WHERE
					SP.AUDITSTATUS = 1 
					AND SP.AUDITDATE >= @DATEBEGIN@ 
					AND SP.AUDITDATE < @DATEEND@ -- AND SP.SCRAPTYPE IN (0, 2, 3)
					UNION ALL-- 工序报损撤销
				SELECT
				gdd.groutingdailydetailid,
					-1 SCRAPNUM
						FROM
							TP_PM_SCRAPPRODUCT SP
							INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE IN ( 20,30,40 ) 
							AND HGDI.ITEMTYPE = 2 
							AND HGDI.ITEMID = SP.PROCEDUREID
							INNER JOIN TP_PM_PRODUCTIONDATA PD ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
							INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
							INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
							INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
						WHERE
							SP.AUDITSTATUS = 1 
							AND SP.VALUEFLAG = '0' 
							AND SP.BACKOUTTIME >= @DATEBEGIN@
							AND SP.BACKOUTTIME < @DATEEND@ --  AND SP.SCRAPTYPE IN (0, 2, 3)
							UNION ALL-- 干补报损
---------------------------------------------------
						SELECT
							groutingdailydetailid,
							1
						FROM
							(
							SELECT
								SP.GOODSCODE,
								GDD.MATERIALCODE SAPCODE,
								PD.USERCODE,
								gdd.groutingdailydetailid,
							CASE
									
									WHEN HGDI.WORKSHOP = 2 
									AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN
										1 
										WHEN HGDI.WORKSHOP = 2 
										AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN
											2 
											WHEN HGDI.WORKSHOP = 3 THEN
											3 ELSE 4 
										END AS WORKSHOP,
										GDD.TESTMOULDFLAG,
										G.GOODS_LINE_CODE,
										TO_CHAR( SP.AUDITDATE, 'yymm' ) || '5000E' AS CHARG --TO_CHAR(SP.AUDITDATE, 'yyyymm') AS CHARG
										
									FROM
										(
										SELECT
											T.* 
										FROM
											(
											SELECT
												SP.BARCODE,
												SP.GOODSCODE,
												SP.GROUTINGDAILYDETAILID,
												PD.PROCEDUREID,
												PD.PRODUCTIONDATAID,
												SP.AUDITDATE,
												RANK ( ) OVER ( PARTITION BY SP.BARCODE ORDER BY PD.PRODUCTIONDATAID DESC ) AS RK 
											FROM
												TP_PM_SCRAPPRODUCT SP
												INNER JOIN TP_PM_PRODUCTIONDATA PD ON SP.BARCODE = PD.BARCODE -- 不包含干补工序
												
												AND PD.PROCEDUREID <> 18 -- 要找干补之前的工序
												
												AND PD.PRODUCTIONDATAID < SP.PRODUCTIONDATAID 
												AND PD.VALUEFLAG = '1' 
											WHERE
												SP.AUDITSTATUS = 1 
												AND SP.PROCEDUREID = 18 
												AND SP.AUDITDATE >= @DATEBEGIN@
												AND SP.AUDITDATE < @DATEEND@
											) T 
										WHERE
											T.RK <= 1 
										) SP
										INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE IN ( 20,30,40 ) 
										AND HGDI.ITEMTYPE = 2 
										AND HGDI.ITEMID = SP.PROCEDUREID
										INNER JOIN TP_PM_PRODUCTIONDATA PD ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
										INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
										INNER JOIN TP_MST_GOODS G ON G.GOODSID = PD.GOODSID
										INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
									) )TT GROUP BY TT.GROUTINGDAILYDETAILID,TT.SCRAPNUM) a
          LEFT JOIN (SELECT groutingdailydetailid,SCRAPNUM,COUNT(*) countt
                      FROM tsap_hegii_datalog_bg_detail THDBD
											LEFT JOIN TSAP_HEGII_DATALOG_BG THDB ON THDBD.LOGID = THDB.LOGID
                     WHERE THDBD.SCRAPNUM NOT IN (0)
										 AND THDB.DATACODE IN (20,30,40)
										 AND THDB.YYYYMMDD>=TO_CHAR(@DATEBEGIN@,'YYYYMMDD') AND THDB.YYYYMMDD<TO_CHAR(@DATEEND@,'YYYYMMDD')
                     GROUP BY groutingdailydetailid,SCRAPNUM) tt
            ON tt.groutingdailydetailid = a.groutingdailydetailid
           AND tt.SCRAPNUM = a.SCRAPNUM ) s
 WHERE NVL(s.counts,0)!=NVL(s.countt,0))T
 LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON T.groutingdailydetailid = GDD.groutingdailydetailid
 UNION ALL
 SELECT GDD.BARCODE,'3' nodetype,gdd.groutingdailydetailid FROM(
 SELECT s.groutingdailydetailid,s.outputnum,NVL(s.counts,0),NVL(s.countt,0)
  FROM (SELECT a.groutingdailydetailid,a.outputnum,a.counts,tt.countt
          FROM (SELECT groutingdailydetailid,outputnum,COUNT(*) counts
                  FROM (--20、30、40干补
									SELECT
										1 outputnum,
										groutingdailydetailid
									FROM
										(
										SELECT
											SP.GOODSCODE,
											GDD.MATERIALCODE SAPCODE,
											SP.SPECIALREPAIRUSERCODE USERCODE,
											gdd.groutingdailydetailid,
										CASE
												
												WHEN HGDI.WORKSHOP = 2 
												AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN
													1 
													WHEN HGDI.WORKSHOP = 2 
													AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN
														2 
														WHEN HGDI.WORKSHOP = 3 THEN
														3 ELSE 4 
													END AS WORKSHOP,
													DECODE( GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-' ) AS TESTMOULDFLAG,
													G.GOODS_LINE_CODE,
													TO_CHAR( SP.SPECIALREPAIRTIME, 'yymm' ) || '5000E' AS CHARG --TO_CHAR(SP.SPECIALREPAIRTIME, 'yyyymm') AS CHARG
													
												FROM
													TP_PM_SCRAPPRODUCT SP
													INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE IN ( '20','30','40') 
													AND HGDI.ITEMTYPE = 2 
													AND HGDI.ITEMID = SP.PROCEDUREID
													INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
													INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
													INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
												WHERE
													SP.AUDITSTATUS = 1 
													AND SP.GOODSLEVELTYPEID = 9 
													AND SP.SPECIALREPAIRTIME >= @DATEBEGIN@
													AND SP.SPECIALREPAIRTIME < @DATEEND@											) )
                 GROUP BY groutingdailydetailid,outputnum) a
          LEFT JOIN (SELECT groutingdailydetailid,REPAIRNUM outputnum,COUNT(*) countt
                      FROM tsap_hegii_datalog_bg_detail THDBD
											LEFT JOIN TSAP_HEGII_DATALOG_BG THDB ON THDBD.LOGID = THDB.LOGID
                     WHERE THDBD.REPAIRNUM NOT IN (0)
										 AND THDB.DATACODE IN ('20','30','40')
										 AND THDB.YYYYMMDD>=TO_CHAR(@DATEBEGIN@,'YYYYMMDD') AND THDB.YYYYMMDD<TO_CHAR(@DATEEND@,'YYYYMMDD')
                     GROUP BY groutingdailydetailid,REPAIRNUM) tt
            ON tt.groutingdailydetailid = a.groutingdailydetailid
           AND tt.outputnum = a.outputnum ) s
 WHERE NVL(s.counts,0)!=NVL(s.countt,0))T
 LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON T.groutingdailydetailid = GDD.groutingdailydetailid
 UNION ALL
SELECT GDD.BARCODE,'4' nodetype,gdd.groutingdailydetailid FROM(
SELECT s.groutingdailydetailid,s.outputnum,NVL(s.counts,0),NVL(s.countt,0)
  FROM (SELECT a.groutingdailydetailid,a.outputnum,a.counts,tt.countt
          FROM (SELECT groutingdailydetailid,outputnum,COUNT(*) counts
                  FROM (SELECT 											GROUTINGDAILYDETAILID,
                            1 OUTPUTNUM
                      FROM (SELECT CASE
                                        WHEN T.IS3 > 0 THEN
                                         3
                                        WHEN INSTR(T.GOODSTYPECODE, '001002') = 1 THEN
                                         1
                                        WHEN INSTR(T.GOODSTYPECODE, '001001') = 1 THEN
                                         2
                                        ELSE
                                         2
                                    END AS WORKSHOP,
                                    T.GROUTINGDAILYDETAILID
                               FROM (SELECT DISTINCT PD.BARCODE,
                                                     PD.GOODSCODE,
																										 GDD.GROUTINGDAILYDETAILID,
                                                     GDD.MATERIALCODE SAPCODE,
                                                     PD.USERCODE,
                                                     DECODE(GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-') AS TESTMOULDFLAG,
                                                     G.GOODS_LINE_CODE,
                                                     TO_CHAR(PD.CREATETIME, 'yymm') || '5000E' AS CHARG,
                                                     --TO_CHAR(PD.CREATETIME, 'yyyymm') AS CHARG,
                                                     HGDI.WORKSHOP AS HGDIWORKSHOP,
                                                     GT.GOODSTYPECODE,
                                                     -- 如果3#卸窑（103），就是3车间的
                                                     (SELECT COUNT(1)
                                                        FROM TP_PM_PRODUCTIONDATA PD2
                                                       WHERE PD2.BARCODE = PD.BARCODE
                                                         AND PD2.PROCEDUREID = 103) AS IS3
                                       FROM TP_PM_PRODUCTIONDATA PD
                                      INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                         ON HGDI.DATACODE = '50'
                                        AND HGDI.ITEMTYPE = 1
                                        AND HGDI.ITEMID = PD.PROCEDUREID -- 3#成检交接（本烧重烧共用，且可以改判）
                                      INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                         ON GDD.GROUTINGDAILYDETAILID = PD.GROUTINGDAILYDETAILID
                                      INNER JOIN TP_MST_GOODS G
                                         ON G.GOODSID = PD.GOODSID
                                      INNER JOIN TP_MST_GOODSTYPE GT
                                         ON G.GOODSTYPEID = GT.GOODSTYPEID
                                      WHERE PD.CREATETIME >= @DATEBEGIN@
                                        AND PD.CREATETIME < @DATEEND@
                                        AND PD.ISREFIRE = '0'
                                        AND PD.Islengbu = '0'
                                        --AND PD.KILNID IN(1,2,5)-- 取本烧的窑炉
                                        --AND ((HGDI.ITEMID <> 104 AND PD.VALUEFLAG = '1') OR
                                        --    (HGDI.ITEMID = 104 AND PD.ISREFIRE = '0' AND
                                        --    PD.CHECKFLAG = '1'))) T
                                         AND ((HGDI.ITEMID = 11 AND (PD.CHECKFLAG = '1' OR PD.CHECKFLAG IS NULL)) 
                                          OR (HGDI.ITEMID = 104 AND PD.CHECKFLAG = '1')) ) T
                             
                             ))
                 GROUP BY groutingdailydetailid,outputnum) a
          LEFT JOIN (SELECT groutingdailydetailid,outputnum,COUNT(*) countt
                      FROM tsap_hegii_datalog_bg_detail THDBD
											LEFT JOIN TSAP_HEGII_DATALOG_BG THDB ON THDBD.LOGID = THDB.LOGID
                     WHERE THDBD.outputnum NOT IN (0)
										 AND THDB.DATACODE = 50
										 AND THDB.YYYYMMDD>=TO_CHAR(@DATEBEGIN@,'YYYYMMDD') AND THDB.YYYYMMDD<TO_CHAR(@DATEEND@,'YYYYMMDD')
                     GROUP BY groutingdailydetailid,outputnum) tt
            ON tt.groutingdailydetailid = a.groutingdailydetailid
           AND tt.outputnum = a.outputnum ) s
 WHERE NVL(s.counts,0)!=NVL(s.countt,0)
															 )T
 LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON T.groutingdailydetailid = GDD.groutingdailydetailid
 UNION ALL
 SELECT GDD.BARCODE,'5' nodetype,gdd.groutingdailydetailid FROM(
 SELECT s.groutingdailydetailid,s.outputnum,NVL(s.counts,0),NVL(s.countt,0)
  FROM (SELECT a.groutingdailydetailid,a.outputnum,a.counts,tt.countt
          FROM (SELECT groutingdailydetailid,outputnum,COUNT(*) counts
                  FROM (SELECT 	groutingdailydetailid,
                            1 outputnum
                      FROM (SELECT
                                    T.groutingdailydetailid
                               FROM (SELECT GDD.groutingdailydetailid
                                       FROM TP_PM_SCRAPPRODUCT SP
                                      INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                         ON HGDI.DATACODE = '50'
                                        AND HGDI.ITEMTYPE = 2
                                        AND HGDI.ITEMID = SP.PROCEDUREID
                                      INNER JOIN TP_PM_PRODUCTIONDATA PD
                                         ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
                                      INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                         ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                                      INNER JOIN TP_MST_GOODS G
                                         ON G.GOODSID = PD.GOODSID
                                      INNER JOIN TP_MST_GOODSTYPE GT
                                         ON G.GOODSTYPEID = GT.GOODSTYPEID
                                      WHERE SP.AUDITSTATUS = 1
                                        AND SP.AUDITDATE >= @DATEBEGIN@
                                        AND SP.AUDITDATE < @DATEEND@
                                     --AND SP.SCRAPTYPE IN (0, 2, 3)
                                     ) T)
                    ---------------------------------------------------                      
                    UNION ALL
                    -- 工序报损撤销
                    ---------------------------------------------------                      
                    SELECT  groutingdailydetailid,
                            -1 SCRAPNUM
                      FROM (SELECT 
                                    T.groutingdailydetailid
                               FROM (SELECT gdd.groutingdailydetailid
                                       FROM TP_PM_SCRAPPRODUCT SP
                                      INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                         ON HGDI.DATACODE = '50'
                                        AND HGDI.ITEMTYPE = 2
                                        AND HGDI.ITEMID = SP.PROCEDUREID
                                      INNER JOIN TP_PM_PRODUCTIONDATA PD
                                         ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
                                      INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                         ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                                      INNER JOIN TP_MST_GOODS G
                                         ON G.GOODSID = PD.GOODSID
                                      INNER JOIN TP_MST_GOODSTYPE GT
                                         ON G.GOODSTYPEID = GT.GOODSTYPEID
                                      WHERE SP.AUDITSTATUS = 1
                                        AND SP.BACKOUTTIME >= @DATEBEGIN@
                                        AND SP.BACKOUTTIME < @DATEEND@
                                     --AND SP.SCRAPTYPE IN (0, 2, 3) 
                                     ) T)
                    
                    ---------------------------------------------------         
                    UNION ALL
                    -- 干补报损
                    ---------------------------------------------------       
                    SELECT groutingdailydetailid,
                            1
                      FROM (SELECT SP.groutingdailydetailid
                               FROM (SELECT T.*
                                       FROM (SELECT SP.BARCODE,
                                                    SP.GOODSCODE,
                                                    SP.groutingdailydetailid,
                                                    PD.PROCEDUREID,
                                                    PD.PRODUCTIONDATAID,
                                                    SP.AUDITDATE,
                                                    RANK() OVER(PARTITION BY SP.BARCODE ORDER BY PD.PRODUCTIONDATAID DESC) AS RK
                                               FROM TP_PM_SCRAPPRODUCT SP
                                              INNER JOIN TP_PM_PRODUCTIONDATA PD
                                                 ON SP.BARCODE = PD.BARCODE
                                                   -- 不包含干补工序
                                                AND PD.PROCEDUREID <> 18
                                                   -- 要找干补之前的工序
                                                AND PD.PRODUCTIONDATAID < SP.PRODUCTIONDATAID
                                                AND PD.VALUEFLAG = '1'
                                              WHERE SP.AUDITSTATUS = 1
                                                AND SP.PROCEDUREID = 18
                                                AND SP.AUDITDATE >= @DATEBEGIN@
                                                AND SP.AUDITDATE < @DATEEND@) T
                                      WHERE T.RK <= 1) SP
                              INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                 ON HGDI.DATACODE = '50'
                                AND HGDI.ITEMTYPE = 2
                                AND HGDI.ITEMID = SP.PROCEDUREID
                              INNER JOIN TP_PM_PRODUCTIONDATA PD
                                 ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
                              INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                 ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                              INNER JOIN TP_MST_GOODS G
                                 ON G.GOODSID = PD.GOODSID
                              INNER JOIN TP_MST_GOODSTYPE GT
                                 ON G.GOODSTYPEID = GT.GOODSTYPEID))
                 GROUP BY groutingdailydetailid,outputnum) a
          LEFT JOIN (SELECT groutingdailydetailid,SCRAPNUM outputnum,COUNT(*) countt
                      FROM tsap_hegii_datalog_bg_detail THDBD
											LEFT JOIN TSAP_HEGII_DATALOG_BG THDB ON THDBD.LOGID = THDB.LOGID
                     WHERE THDBD.SCRAPNUM NOT IN (0)
										 AND THDB.DATACODE IN (50)
										 AND THDB.YYYYMMDD>=TO_CHAR(@DATEBEGIN@,'YYYYMMDD') AND THDB.YYYYMMDD<TO_CHAR(@DATEEND@,'YYYYMMDD')
                     GROUP BY groutingdailydetailid,SCRAPNUM) tt
            ON tt.groutingdailydetailid = a.groutingdailydetailid
           AND tt.outputnum = a.outputnum ) s
 WHERE NVL(s.counts,0)!=NVL(s.countt,0) )T
 LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON T.groutingdailydetailid = GDD.groutingdailydetailid
 UNION ALL
 SELECT GDD.BARCODE,'6' nodetype,gdd.groutingdailydetailid FROM(
 SELECT s.groutingdailydetailid,s.outputnum,NVL(s.counts,0),NVL(s.countt,0)
  FROM (SELECT a.groutingdailydetailid,a.outputnum,a.counts,tt.countt
          FROM (SELECT groutingdailydetailid,outputnum,COUNT(*) counts
                  FROM (SELECT 										 T.GROUTINGDAILYDETAILID,
                           DECODE(T.RECYCLINGFLAG, '0', 0, 1) outputnum
                      FROM (SELECT 
                                   BAR.RECYCLINGFLAG,
																	 BAR.GROUTINGDAILYDETAILID,
                                   TO_CHAR(GH.FHTIME, 'yyyymm') AS CHARG
                              FROM TP_PM_GROUTINGDAILYDETAIL BAR
                             INNER JOIN TP_PM_FINISHEDPRODUCT GH
                                ON BAR.GROUTINGDAILYDETAILID = GH.GROUTINGDAILYDETAILID
                             INNER JOIN TP_MST_GOODS G
                                ON G.GOODSID = BAR.GOODSID
                             INNER JOIN TP_MST_GOODSTYPE GT
                                ON GT.GOODSTYPEID = G.GOODSTYPEID
                              LEFT JOIN TP_PM_ORDER O
                                ON O.ORDERID = GH.FHORDERID
                             WHERE GH.FHUSERID IS NOT NULL
                               AND GH.FHTIME >= @DATEBEGIN@
                               AND GH.FHTIME < @DATEEND@) T)
                 GROUP BY groutingdailydetailid,outputnum) a
          LEFT JOIN ( SELECT T1.groutingdailydetailid,T1.OUTPUTNUM,COUNT(*) countt FROM (
										 SELECT DISTINCT TPF.groutingdailydetailid,DECODE(BAR.RECYCLINGFLAG, '0', 0, 1) outputnum FROM TSAP_HEGII_WORKDATA_BG TSH
										 INNER JOIN TP_PM_FINISHEDPRODUCT TPF ON TSH.LOGID = TPF.BGLOGID
										 INNER JOIN TP_PM_GROUTINGDAILYDETAIL BAR ON TPF.groutingdailydetailid = BAR.groutingdailydetailid
										 WHERE 
										 TSH.CREATETIME>=@DATEBEGIN@
										 AND TSH.CREATETIME<@DATEEND@
										 AND TPF.CREATETIME>=@DATEBEGINONE@
										 AND TPF.CREATETIME<@DATEEND@
										 AND TPF.VALUEFLAG = 1
										 and TSH.DATACODE = 60
										 AND TSH.outputnum NOT IN (0))T1 GROUP BY T1.groutingdailydetailid,T1.OUTPUTNUM) tt
            ON tt.groutingdailydetailid = a.groutingdailydetailid
           AND tt.outputnum = a.outputnum ) s
 WHERE NVL(s.counts,0)!=NVL(s.countt,0))T
 LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON T.groutingdailydetailid = GDD.groutingdailydetailid
 UNION ALL
  SELECT GDD.BARCODE,'7' nodetype,gdd.groutingdailydetailid FROM(
 SELECT s.groutingdailydetailid,s.outputnum,NVL(s.counts,0),NVL(s.countt,0)
  FROM (SELECT a.groutingdailydetailid,a.outputnum,a.counts,tt.countt
          FROM (SELECT groutingdailydetailid,outputnum,COUNT(*) counts
                  FROM (SELECT 										 T1.GROUTINGDAILYDETAILID,
                           DECODE(T1.RECYCLINGFLAG, '1', 0, 1) OUTPUTNUM
                      FROM (SELECT 
                                   BAR.RECYCLINGFLAG,
								   BAR.GROUTINGDAILYDETAILID,
                                   TO_CHAR(GH.FHTIME, 'yyyymm') AS CHARG
                              FROM TP_PM_GROUTINGDAILYDETAIL BAR
                             INNER JOIN TP_PM_FINISHEDPRODUCT GH
                                ON BAR.GROUTINGDAILYDETAILID = GH.GROUTINGDAILYDETAILID
                             INNER JOIN TP_MST_GOODS G
                                ON G.GOODSID = BAR.GOODSID
                             INNER JOIN TP_MST_GOODSTYPE GT
                                ON GT.GOODSTYPEID = G.GOODSTYPEID
                              LEFT JOIN TP_PM_ORDER O
                                ON O.ORDERID = GH.FHORDERID
                             WHERE GH.FHUSERID IS NOT NULL
                               AND GH.FHTIME >= @DATEBEGIN@
                               AND GH.FHTIME < @DATEEND@) T1)
                 GROUP BY groutingdailydetailid,outputnum) a
          LEFT JOIN ( SELECT T1.groutingdailydetailid,T1.OUTPUTNUM,COUNT(*) countt FROM (
										 SELECT DISTINCT TPF.groutingdailydetailid,DECODE(BAR.RECYCLINGFLAG, '1', 0, 1) OUTPUTNUM FROM TSAP_HEGII_WORKDATA_BG TSH
										 INNER JOIN TP_PM_FINISHEDPRODUCT TPF ON TSH.LOGID = TPF.BGLOGID
										 INNER JOIN TP_PM_GROUTINGDAILYDETAIL BAR ON TPF.groutingdailydetailid = BAR.groutingdailydetailid
										 WHERE 
										 TSH.CREATETIME>=@DATEBEGIN@
										 AND TSH.CREATETIME<@DATEEND@
										 AND TPF.CREATETIME>=@DATEBEGINONE@
										 AND TPF.CREATETIME<@DATEEND@
										 AND TPF.VALUEFLAG = 1
										 and TSH.DATACODE = 60
										 AND TSH.outputnum NOT IN (0))T1 GROUP BY T1.groutingdailydetailid,T1.OUTPUTNUM) tt
            ON tt.groutingdailydetailid = a.groutingdailydetailid
           AND tt.outputnum = a.outputnum ) s
 WHERE NVL(s.counts,0)!=NVL(s.countt,0))T
 LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON T.groutingdailydetailid = GDD.groutingdailydetailid) TT
 INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON TT.groutingdailydetailid = GDD.groutingdailydetailid
INNER JOIN tp_mst_goods g ON g.goodsid = GDD.goodsid
INNER JOIN tp_mst_goodstype gt ON g.goodstypeid = gt.goodstypeid WHERE TT.nodetype = @nodetype@ )TT1
 WHERE  TT1.车间 = @WORKSHOP@ and TT1.BARCODE is not null
                ";
                if (context.Request["shorname"].ToString() == "注浆数")
                {
                    nodetype1 = 1;
                }
                else if (context.Request["shorname"].ToString() == "损坯数")
                {
                    nodetype1 = 2;
                }
                else if (context.Request["shorname"].ToString() == "干补数")
                {
                    nodetype1 = 3;
                }
                else if (context.Request["shorname"].ToString() == "出窑数")
                {
                    nodetype1 = 4;
                }
                else if (context.Request["shorname"].ToString() == "质量登记数")
                {
                    nodetype1 = 5;
                }
                else if (context.Request["shorname"].ToString() == "回收数")
                {
                    nodetype1 = 6;
                }
                else
                {
                    nodetype1 = 7;
                }
                DateTime dateend = Convert.ToDateTime(context.Request["dateendMaster"]).AddDays(1);
                DateTime datebeginone = Convert.ToDateTime(context.Request["datebeginMaster"]).AddDays(-1);
                List<CDAParameter> sqlPara = new List<CDAParameter>();
                sqlPara.Add(new CDAParameter("nodetype", nodetype1));
                sqlPara.Add(new CDAParameter("WORKSHOP",  context.Request["WORKSHOP"].ToString()));
                sqlPara.Add(new CDAParameter("DATEBEGIN", context.Request["datebeginMaster"].ToString(), DataType.DateTime));
                sqlPara.Add(new CDAParameter("DATEBEGINONE", datebeginone, DataType.DateTime));
                sqlPara.Add(new CDAParameter("DATEEND", dateend, DataType.DateTime));

                JsonResult data = Easyui.ExecuteJsonResult(conn, sqlStr, sqlPara);
                context.Response.Write(data.ToJson());
            }
            if (context.Request["m"].ToString() == "cy")
            {
                //读取报表数据
                string sqlStr = @"
                    SELECT
--Q.注浆数_车间 车间,
Q.注浆数_差异量 差异量
FROM(
SELECT O.注浆数_车间,'注浆数_'||NVL(O.注浆数_差异量,0)注浆数_差异量,1 NUM  FROM(
SELECT A.注浆数_车间,B.注浆数_差异量 FROM(
SELECT 1 注浆数_车间,NULL 注浆数_差异量 FROM dual UNION ALL
SELECT 2 注浆数_车间,NULL 注浆数_差异量 FROM dual UNION ALL
SELECT 3 注浆数_车间,NULL 注浆数_差异量 FROM dual )A
LEFT JOIN(
SELECT
TT.注浆数_车间,
TO_CHAR(NVL(SUM(TT.注浆数_差异量),0)) 注浆数_差异量
FROM(
SELECT
CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001002') = 1 THEN 1
		 WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN 2
		 WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 THEN 3 ELSE 0 END 注浆数_车间,
CASE WHEN T.outputnum * T.counts = NULL THEN 0 ELSE T.outputnum * T.counts END 注浆数_差异量
FROM(
SELECT s.groutingdailydetailid,s.outputnum,NVL(s.counts,0) counts,NVL(s.countt,0) countt
  FROM (SELECT a.groutingdailydetailid,a.outputnum,a.counts,tt.countt
          FROM (SELECT groutingdailydetailid,outputnum,COUNT(*) counts
                  FROM (SELECT decode(gh.datatype, 1, 1, -1) outputnum,gdd.groutingdailydetailid
                          FROM tp_pm_goodschangehistory gh
                         INNER JOIN tp_pm_groutingdailydetail gdd
                            ON gdd.groutingdailydetailid = gh.groutingdailydetailid
                         INNER JOIN tp_mst_goods g
                            ON g.goodsid = gh.goodsid
                         INNER JOIN tp_mst_goodstype gt
                            ON g.goodstypeid = gt.goodstypeid
                         WHERE gh.createtime >= @DATEBEGIN@
                           AND gh.createtime < @DATEEND@
                           AND gh.datatype IN (1, 2)
                           AND g.scrapsumflag = '1')
                 GROUP BY groutingdailydetailid,outputnum) a
          LEFT JOIN (SELECT groutingdailydetailid,outputnum,COUNT(*) countt
                      FROM tsap_hegii_datalog_bg_detail THDBD
											LEFT JOIN TSAP_HEGII_DATALOG_BG THDB ON THDBD.LOGID = THDB.LOGID
                     WHERE THDBD.outputnum NOT IN (0)
										 AND THDB.DATACODE = 20
										 AND THDB.YYYYMMDD>=TO_CHAR(@DATEBEGIN@,'YYYYMMDD') AND THDB.YYYYMMDD<TO_CHAR(@DATEEND@,'YYYYMMDD')
                     GROUP BY groutingdailydetailid,outputnum) tt
            ON tt.groutingdailydetailid = a.groutingdailydetailid
           AND tt.outputnum = a.outputnum ) s
 WHERE NVL(s.counts,0)!=NVL(s.countt,0) )T
 LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON T.groutingdailydetailid = GDD.groutingdailydetailid
 INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
)TT GROUP BY TT.注浆数_车间)B ON B.注浆数_车间 = A.注浆数_车间 
)O

UNION ALL

SELECT O.出窑数_车间,'出窑数_'||NVL(O.出窑数_差异量,0),4 NUM FROM(
SELECT A.出窑数_车间,B.出窑数_差异量 FROM(
SELECT 1 出窑数_车间,NULL 出窑数_差异量 FROM dual UNION ALL
SELECT 2 出窑数_车间,NULL 出窑数_差异量 FROM dual UNION ALL
SELECT 3 出窑数_车间,NULL 出窑数_差异量 FROM dual) A
LEFT JOIN(
SELECT
TT.出窑数_车间,
SUM(TT.出窑数_差异量) 出窑数_差异量
FROM(
SELECT
CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001002') = 1 THEN 1
		 WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN 2
		 WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 THEN 3 ELSE 0 END 出窑数_车间,
CASE WHEN T.outputnum * T.counts = NULL THEN 0 ELSE T.outputnum * T.counts END 出窑数_差异量
FROM(
SELECT s.groutingdailydetailid,s.outputnum,NVL(s.counts,0) counts,NVL(s.countt,0) countt
  FROM (SELECT a.groutingdailydetailid,a.outputnum,a.counts,tt.countt
          FROM (SELECT groutingdailydetailid,outputnum,COUNT(*) counts
                  FROM (SELECT 											GROUTINGDAILYDETAILID,
                            1 OUTPUTNUM
                      FROM (SELECT CASE
                                        WHEN T.IS3 > 0 THEN
                                         3
                                        WHEN INSTR(T.GOODSTYPECODE, '001002') = 1 THEN
                                         1
                                        WHEN INSTR(T.GOODSTYPECODE, '001001') = 1 THEN
                                         2
                                        ELSE
                                         2
                                    END AS WORKSHOP,
                                    T.GROUTINGDAILYDETAILID
                               FROM (SELECT DISTINCT PD.BARCODE,
                                                     PD.GOODSCODE,
																										 GDD.GROUTINGDAILYDETAILID,
                                                     GDD.MATERIALCODE SAPCODE,
                                                     PD.USERCODE,
                                                     DECODE(GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-') AS TESTMOULDFLAG,
                                                     G.GOODS_LINE_CODE,
                                                     TO_CHAR(PD.CREATETIME, 'yymm') || '5000E' AS CHARG,
                                                     --TO_CHAR(PD.CREATETIME, 'yyyymm') AS CHARG,
                                                     HGDI.WORKSHOP AS HGDIWORKSHOP,
                                                     GT.GOODSTYPECODE,
                                                     -- 如果3#卸窑（103），就是3车间的
                                                     (SELECT COUNT(1)
                                                        FROM TP_PM_PRODUCTIONDATA PD2
                                                       WHERE PD2.BARCODE = PD.BARCODE
                                                         AND PD2.PROCEDUREID = 103) AS IS3
                                       FROM TP_PM_PRODUCTIONDATA PD
                                      INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                         ON HGDI.DATACODE = '50'
                                        AND HGDI.ITEMTYPE = 1
                                        AND HGDI.ITEMID = PD.PROCEDUREID -- 3#成检交接（本烧重烧共用，且可以改判）
                                      INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                         ON GDD.GROUTINGDAILYDETAILID = PD.GROUTINGDAILYDETAILID
                                      INNER JOIN TP_MST_GOODS G
                                         ON G.GOODSID = PD.GOODSID
                                      INNER JOIN TP_MST_GOODSTYPE GT
                                         ON G.GOODSTYPEID = GT.GOODSTYPEID
                                      WHERE PD.CREATETIME >= @DATEBEGIN@
                                        AND PD.CREATETIME < @DATEEND@
                                        AND PD.ISREFIRE = '0'
                                        AND PD.Islengbu = '0'
                                        --AND PD.KILNID IN(1,2,5)-- 取本烧的窑炉
                                        --AND ((HGDI.ITEMID <> 104 AND PD.VALUEFLAG = '1') OR
                                        --    (HGDI.ITEMID = 104 AND PD.ISREFIRE = '0' AND
                                        --    PD.CHECKFLAG = '1'))) T
                                         AND ((HGDI.ITEMID = 11 AND (PD.CHECKFLAG = '1' OR PD.CHECKFLAG IS NULL)) 
                                          OR (HGDI.ITEMID = 104 AND PD.CHECKFLAG = '1')) ) T
                             
                             ))
                 GROUP BY groutingdailydetailid,outputnum) a
          LEFT JOIN (SELECT groutingdailydetailid,outputnum,COUNT(*) countt
                      FROM tsap_hegii_datalog_bg_detail THDBD
											LEFT JOIN TSAP_HEGII_DATALOG_BG THDB ON THDBD.LOGID = THDB.LOGID
                     WHERE THDBD.outputnum NOT IN (0)
										 AND THDB.DATACODE = 50
										 AND THDB.YYYYMMDD>=TO_CHAR(@DATEBEGIN@,'YYYYMMDD') AND THDB.YYYYMMDD<TO_CHAR(@DATEEND@,'YYYYMMDD')
                     GROUP BY groutingdailydetailid,outputnum) tt
            ON tt.groutingdailydetailid = a.groutingdailydetailid
           AND tt.outputnum = a.outputnum ) s
 WHERE NVL(s.counts,0)!=NVL(s.countt,0))T
 LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON T.groutingdailydetailid = GDD.groutingdailydetailid
 INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID)TT GROUP BY TT.出窑数_车间 )B ON A.出窑数_车间 = B.出窑数_车间
)O


UNION ALL


SELECT O.回收数_车间,'回收数_'||NVL(O.回收数_差异量,0),6 NUM FROM(
SELECT A.回收数_车间,B.回收数_差异量 FROM(
SELECT 1 回收数_车间,NULL 回收数_差异量 FROM dual UNION ALL
SELECT 2 回收数_车间,NULL 回收数_差异量 FROM dual UNION ALL
SELECT 3 回收数_车间,NULL 回收数_差异量 FROM dual) A
LEFT JOIN(
SELECT
TT.回收数_车间,
SUM(TT.回收数_差异量) 回收数_差异量
FROM(
SELECT
CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001002') = 1 THEN 1
		 WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN 2
		 WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 THEN 3 ELSE 0 END 回收数_车间,
CASE WHEN T.outputnum * T.counts = NULL THEN 0 ELSE T.outputnum * T.counts END 回收数_差异量
FROM(
SELECT s.groutingdailydetailid,s.outputnum,NVL(s.counts,0) counts,NVL(s.countt,0) countt
  FROM (SELECT a.groutingdailydetailid,a.outputnum,a.counts,tt.countt
          FROM (SELECT groutingdailydetailid,outputnum,COUNT(*) counts
                  FROM (SELECT 										 T.GROUTINGDAILYDETAILID,
                           DECODE(T.RECYCLINGFLAG, '0', 0, 1) outputnum
                      FROM (SELECT 
                                   BAR.RECYCLINGFLAG,
																	 BAR.GROUTINGDAILYDETAILID,
                                   TO_CHAR(GH.FHTIME, 'yyyymm') AS CHARG
                              FROM TP_PM_GROUTINGDAILYDETAIL BAR
                             INNER JOIN TP_PM_FINISHEDPRODUCT GH
                                ON BAR.GROUTINGDAILYDETAILID = GH.GROUTINGDAILYDETAILID
                             INNER JOIN TP_MST_GOODS G
                                ON G.GOODSID = BAR.GOODSID
                             INNER JOIN TP_MST_GOODSTYPE GT
                                ON GT.GOODSTYPEID = G.GOODSTYPEID
                              LEFT JOIN TP_PM_ORDER O
                                ON O.ORDERID = GH.FHORDERID
                             WHERE GH.FHUSERID IS NOT NULL
                               AND GH.FHTIME >= @DATEBEGIN@
                               AND GH.FHTIME < @DATEEND@) T)
                 GROUP BY groutingdailydetailid,outputnum) a
          LEFT JOIN ( SELECT T1.groutingdailydetailid,T1.OUTPUTNUM,COUNT(*) countt FROM (
										 SELECT DISTINCT TPF.groutingdailydetailid,CASE WHEN BAR.RECYCLINGFLAG='0' THEN  0 ELSE 1 END outputnum FROM TSAP_HEGII_WORKDATA_BG TSH
										 INNER JOIN TP_PM_FINISHEDPRODUCT TPF ON TSH.LOGID = TPF.BGLOGID
										 INNER JOIN TP_PM_GROUTINGDAILYDETAIL BAR ON TPF.groutingdailydetailid = BAR.groutingdailydetailid
										 WHERE 
										 TSH.CREATETIME>=@DATEBEGIN@
										 AND TSH.CREATETIME<@DATEEND@
										 AND TPF.CREATETIME>=@DATEBEGINONE@
										 AND TPF.CREATETIME<@DATEEND@
										 AND TPF.VALUEFLAG = 1
										 and TSH.DATACODE = 60
										 AND TSH.outputnum != 0 )T1 GROUP BY T1.groutingdailydetailid,T1.OUTPUTNUM) tt
            ON tt.groutingdailydetailid = a.groutingdailydetailid
           AND tt.outputnum = a.outputnum ) s
 WHERE NVL(s.counts,0)!=NVL(s.countt,0))T
 LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON T.groutingdailydetailid = GDD.groutingdailydetailid
 INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID)TT GROUP BY TT.回收数_车间 )B ON A.回收数_车间 = B.回收数_车间
)O


UNION ALL


SELECT O.入库数_车间,'入库数_'||NVL(O.入库数_差异量,0),7 NUM FROM(
SELECT A.入库数_车间,B.入库数_差异量 FROM(
SELECT 1 入库数_车间,NULL 入库数_差异量 FROM dual UNION ALL
SELECT 2 入库数_车间,NULL 入库数_差异量 FROM dual UNION ALL
SELECT 3 入库数_车间,NULL 入库数_差异量 FROM dual) A
LEFT JOIN(
SELECT
TT.入库数_车间,
SUM(TT.入库数_差异量) 入库数_差异量
FROM(
SELECT
CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001002') = 1 THEN 1
		 WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN 2
		 WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 THEN 3 ELSE 0 END 入库数_车间,
CASE WHEN T.outputnum * T.counts = NULL THEN 0 ELSE T.outputnum * T.counts END 入库数_差异量
FROM(
SELECT s.groutingdailydetailid,s.outputnum,NVL(s.counts,0) counts,NVL(s.countt,0) countt
  FROM (SELECT a.groutingdailydetailid,a.outputnum,a.counts,tt.countt
          FROM (SELECT groutingdailydetailid,outputnum,COUNT(*) counts
                  FROM (SELECT 										 T1.GROUTINGDAILYDETAILID,
                           DECODE(T1.RECYCLINGFLAG, '1', 0, 1) OUTPUTNUM
                      FROM (SELECT 
                                   BAR.RECYCLINGFLAG,
																	 BAR.GROUTINGDAILYDETAILID,
                                   TO_CHAR(GH.FHTIME, 'yyyymm') AS CHARG
                              FROM TP_PM_GROUTINGDAILYDETAIL BAR
                             INNER JOIN TP_PM_FINISHEDPRODUCT GH
                                ON BAR.GROUTINGDAILYDETAILID = GH.GROUTINGDAILYDETAILID
                             INNER JOIN TP_MST_GOODS G
                                ON G.GOODSID = BAR.GOODSID
                             INNER JOIN TP_MST_GOODSTYPE GT
                                ON GT.GOODSTYPEID = G.GOODSTYPEID
                              LEFT JOIN TP_PM_ORDER O
                                ON O.ORDERID = GH.FHORDERID
                             WHERE GH.FHUSERID IS NOT NULL
                               AND GH.FHTIME >= @DATEBEGIN@
                               AND GH.FHTIME < @DATEEND@) T1)
                 GROUP BY groutingdailydetailid,outputnum) a
          LEFT JOIN ( SELECT T1.groutingdailydetailid,T1.OUTPUTNUM,COUNT(*) countt FROM (
										 SELECT DISTINCT TPF.groutingdailydetailid,DECODE(BAR.RECYCLINGFLAG, '1', 0, 1) OUTPUTNUM FROM TSAP_HEGII_WORKDATA_BG TSH
										 INNER JOIN TP_PM_FINISHEDPRODUCT TPF ON TSH.LOGID = TPF.BGLOGID
										 INNER JOIN TP_PM_GROUTINGDAILYDETAIL BAR ON TPF.groutingdailydetailid = BAR.groutingdailydetailid
										 WHERE 
										 TSH.CREATETIME>=@DATEBEGIN@
										 AND TSH.CREATETIME<@DATEEND@
										 AND TPF.CREATETIME>=@DATEBEGINONE@
										 AND TPF.CREATETIME<@DATEEND@
										 AND TPF.VALUEFLAG = 1
										 and TSH.DATACODE = 60
										 AND TSH.outputnum NOT IN (0))T1 GROUP BY T1.groutingdailydetailid,T1.OUTPUTNUM) tt
            ON tt.groutingdailydetailid = a.groutingdailydetailid
           AND tt.outputnum = a.outputnum ) s
 WHERE NVL(s.counts,0)!=NVL(s.countt,0))T
 LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON T.groutingdailydetailid = GDD.groutingdailydetailid
 INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID)TT GROUP BY TT.入库数_车间 )B ON A.入库数_车间 = B.入库数_车间
)O


UNION ALL


SELECT O.干补数_车间,'干补数_'||NVL(O.干补数_差异量,0),3 NUM FROM(
SELECT A.干补数_车间,B.干补数_差异量 FROM(
SELECT 1 干补数_车间,NULL 干补数_差异量 FROM dual UNION ALL
SELECT 2 干补数_车间,NULL 干补数_差异量 FROM dual UNION ALL
SELECT 3 干补数_车间,NULL 干补数_差异量 FROM dual) A
LEFT JOIN(
SELECT
TT.干补数_车间,
SUM(TT.干补数_差异量) 干补数_差异量
FROM(
SELECT
CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001002') = 1 THEN 1
		 WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN 2
		 WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 THEN 3 ELSE 0 END 干补数_车间,
CASE WHEN T.outputnum * T.counts = NULL THEN 0 ELSE T.outputnum * T.counts END 干补数_差异量
FROM(
SELECT s.groutingdailydetailid,s.outputnum,NVL(s.counts,0) counts,NVL(s.countt,0) countt
  FROM (SELECT a.groutingdailydetailid,a.outputnum,a.counts,tt.countt
          FROM (SELECT groutingdailydetailid,outputnum,COUNT(*) counts
                  FROM (--20、30、40干补
									SELECT
										1 outputnum,
										groutingdailydetailid
									FROM
										(
										SELECT
											SP.GOODSCODE,
											GDD.MATERIALCODE SAPCODE,
											SP.SPECIALREPAIRUSERCODE USERCODE,
											gdd.groutingdailydetailid,
										CASE
												
												WHEN HGDI.WORKSHOP = 2 
												AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN
													1 
													WHEN HGDI.WORKSHOP = 2 
													AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN
														2 
														WHEN HGDI.WORKSHOP = 3 THEN
														3 ELSE 4 
													END AS WORKSHOP,
													DECODE( GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-' ) AS TESTMOULDFLAG,
													G.GOODS_LINE_CODE,
													TO_CHAR( SP.SPECIALREPAIRTIME, 'yymm' ) || '5000E' AS CHARG --TO_CHAR(SP.SPECIALREPAIRTIME, 'yyyymm') AS CHARG
													
												FROM
													TP_PM_SCRAPPRODUCT SP
													INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE IN ( '20','30','40') 
													AND HGDI.ITEMTYPE = 2 
													AND HGDI.ITEMID = SP.PROCEDUREID
													INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
													INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
													INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
												WHERE
													SP.AUDITSTATUS = 1 
													AND SP.GOODSLEVELTYPEID = 9 
													AND SP.SPECIALREPAIRTIME >= @DATEBEGIN@
													AND SP.SPECIALREPAIRTIME < @DATEEND@
												) )
                 GROUP BY groutingdailydetailid,outputnum) a
          LEFT JOIN (SELECT groutingdailydetailid,REPAIRNUM outputnum,COUNT(*) countt
                      FROM tsap_hegii_datalog_bg_detail THDBD
											LEFT JOIN TSAP_HEGII_DATALOG_BG THDB ON THDBD.LOGID = THDB.LOGID
                     WHERE THDBD.REPAIRNUM NOT IN (0)
										 AND THDB.DATACODE IN ('20','30','40')
										 AND THDB.YYYYMMDD>=TO_CHAR(@DATEBEGIN@,'YYYYMMDD') AND THDB.YYYYMMDD<TO_CHAR(@DATEEND@,'YYYYMMDD')
                     GROUP BY groutingdailydetailid,REPAIRNUM) tt
            ON tt.groutingdailydetailid = a.groutingdailydetailid
           AND tt.outputnum = a.outputnum ) s
 WHERE NVL(s.counts,0)!=NVL(s.countt,0))T
 LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON T.groutingdailydetailid = GDD.groutingdailydetailid
 INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID)TT GROUP BY TT.干补数_车间 )B ON A.干补数_车间 = B.干补数_车间
)O


UNION ALL


SELECT O.损坯数_车间,'损坯数_'||NVL(O.损坯数_差异量,0),2 NUM FROM(
SELECT A.损坯数_车间,B.损坯数_差异量 FROM(
SELECT 1 损坯数_车间,NULL 损坯数_差异量 FROM dual UNION ALL
SELECT 1 损坯数_车间,NULL 损坯数_差异量 FROM dual UNION ALL
SELECT 1 损坯数_车间,NULL 损坯数_差异量 FROM dual UNION ALL
SELECT 1 损坯数_车间,NULL 损坯数_差异量 FROM dual UNION ALL
SELECT 2 损坯数_车间,NULL 损坯数_差异量 FROM dual UNION ALL
SELECT 2 损坯数_车间,NULL 损坯数_差异量 FROM dual UNION ALL
SELECT 2 损坯数_车间,NULL 损坯数_差异量 FROM dual UNION ALL
SELECT 2 损坯数_车间,NULL 损坯数_差异量 FROM dual UNION ALL
SELECT 3 损坯数_车间,NULL 损坯数_差异量 FROM dual UNION ALL
SELECT 3 损坯数_车间,NULL 损坯数_差异量 FROM dual UNION ALL
SELECT 3 损坯数_车间,NULL 损坯数_差异量 FROM dual UNION ALL
SELECT 3 损坯数_车间,NULL 损坯数_差异量 FROM dual) A
LEFT JOIN(
SELECT
TT.损坯数_车间,
SUM(TT.损坯数_差异量) 损坯数_差异量
FROM(
SELECT
CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001002') = 1 THEN 1
		 WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN 2
		 WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 THEN 3 ELSE 0 END 损坯数_车间,
CASE WHEN T.outputnum * T.counts = NULL THEN 0 ELSE T.outputnum * T.counts END 损坯数_差异量
FROM(
SELECT s.groutingdailydetailid,s.SCRAPNUM outputnum,NVL(s.counts,0) counts,NVL(s.countt,0) countt
  FROM (SELECT a.groutingdailydetailid,a.SCRAPNUM,a.counts,tt.countt
          FROM (SELECT
TT.GROUTINGDAILYDETAILID,
TT.SCRAPNUM,
COUNT(*) counts
FROM(
SELECT
	gdd.groutingdailydetailid,
	 DECODE( GH.DATATYPE, 3, 1, - 1 )SCRAPNUM
		FROM
			TP_PM_GOODSCHANGEHISTORY GH
			INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = GH.GROUTINGDAILYDETAILID
			INNER JOIN TP_MST_USER U ON U.USERID = GH.USERID
			INNER JOIN TP_MST_GOODS G ON G.GOODSID = GH.GOODSID
			INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
		WHERE
			GH.CREATETIME >= @DATEBEGIN@
			AND GH.CREATETIME < @DATEEND@
			AND GH.DATATYPE IN ( 3, 4 ) 
			AND G.SCRAPSUMFLAG = '1' 
		 UNION ALL-- 工序报损
		SELECT
		gdd.groutingdailydetailid,
			1  SCRAPNUM
				FROM
					TP_PM_SCRAPPRODUCT SP
					INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE IN ( 20,30,40 ) 
					AND HGDI.ITEMTYPE = 2 
					AND HGDI.ITEMID = SP.PROCEDUREID
					INNER JOIN TP_PM_PRODUCTIONDATA PD ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
					INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
					INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
					INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
				WHERE
					SP.AUDITSTATUS = 1 
					AND SP.AUDITDATE >= @DATEBEGIN@
					AND SP.AUDITDATE < @DATEEND@ -- AND SP.SCRAPTYPE IN (0, 2, 3)
					UNION ALL-- 工序报损撤销
				SELECT
				gdd.groutingdailydetailid,
					-1 SCRAPNUM
						FROM
							TP_PM_SCRAPPRODUCT SP
							INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE IN ( 20,30,40 ) 
							AND HGDI.ITEMTYPE = 2 
							AND HGDI.ITEMID = SP.PROCEDUREID
							INNER JOIN TP_PM_PRODUCTIONDATA PD ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
							INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
							INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
							INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
						WHERE
							SP.AUDITSTATUS = 1 
							AND SP.VALUEFLAG = '0' 
							AND SP.BACKOUTTIME >= @DATEBEGIN@
							AND SP.BACKOUTTIME < @DATEEND@ --  AND SP.SCRAPTYPE IN (0, 2, 3)
							UNION ALL-- 干补报损
---------------------------------------------------
						SELECT
							groutingdailydetailid,
							1
						FROM
							(
							SELECT
								SP.GOODSCODE,
								GDD.MATERIALCODE SAPCODE,
								PD.USERCODE,
								gdd.groutingdailydetailid,
							CASE
									
									WHEN HGDI.WORKSHOP = 2 
									AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN
										1 
										WHEN HGDI.WORKSHOP = 2 
										AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN
											2 
											WHEN HGDI.WORKSHOP = 3 THEN
											3 ELSE 4 
										END AS WORKSHOP,
										GDD.TESTMOULDFLAG,
										G.GOODS_LINE_CODE,
										TO_CHAR( SP.AUDITDATE, 'yymm' ) || '5000E' AS CHARG --TO_CHAR(SP.AUDITDATE, 'yyyymm') AS CHARG
										
									FROM
										(
										SELECT
											T.* 
										FROM
											(
											SELECT
												SP.BARCODE,
												SP.GOODSCODE,
												SP.GROUTINGDAILYDETAILID,
												PD.PROCEDUREID,
												PD.PRODUCTIONDATAID,
												SP.AUDITDATE,
												RANK ( ) OVER ( PARTITION BY SP.BARCODE ORDER BY PD.PRODUCTIONDATAID DESC ) AS RK 
											FROM
												TP_PM_SCRAPPRODUCT SP
												INNER JOIN TP_PM_PRODUCTIONDATA PD ON SP.BARCODE = PD.BARCODE -- 不包含干补工序
												
												AND PD.PROCEDUREID <> 18 -- 要找干补之前的工序
												
												AND PD.PRODUCTIONDATAID < SP.PRODUCTIONDATAID 
												AND PD.VALUEFLAG = '1' 
											WHERE
												SP.AUDITSTATUS = 1 
												AND SP.PROCEDUREID = 18 
												AND SP.AUDITDATE >= @DATEBEGIN@
												AND SP.AUDITDATE < @DATEEND@
											) T 
										WHERE
											T.RK <= 1 
										) SP
										INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE IN ( 20,30,40 ) 
										AND HGDI.ITEMTYPE = 2 
										AND HGDI.ITEMID = SP.PROCEDUREID
										INNER JOIN TP_PM_PRODUCTIONDATA PD ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
										INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
										INNER JOIN TP_MST_GOODS G ON G.GOODSID = PD.GOODSID
										INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID 
									) )TT GROUP BY TT.GROUTINGDAILYDETAILID,TT.SCRAPNUM) a
          LEFT JOIN (SELECT groutingdailydetailid,SCRAPNUM,COUNT(*) countt
                      FROM tsap_hegii_datalog_bg_detail THDBD
											LEFT JOIN TSAP_HEGII_DATALOG_BG THDB ON THDBD.LOGID = THDB.LOGID
                     WHERE THDBD.SCRAPNUM NOT IN (0)
										 AND THDB.DATACODE IN (20,30,40)
										 AND THDB.YYYYMMDD>=TO_CHAR(@DATEBEGIN@,'YYYYMMDD') AND THDB.YYYYMMDD<TO_CHAR(@DATEEND@,'YYYYMMDD')
                     GROUP BY groutingdailydetailid,SCRAPNUM) tt
            ON tt.groutingdailydetailid = a.groutingdailydetailid
           AND tt.SCRAPNUM = a.SCRAPNUM ) s
 WHERE NVL(s.counts,0)!=NVL(s.countt,0))T
 LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON T.groutingdailydetailid = GDD.groutingdailydetailid
 INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID)TT GROUP BY TT.损坯数_车间 )B ON A.损坯数_车间 = B.损坯数_车间
)O


UNION ALL


SELECT O.质量登记数_车间,'质量登记数_'||NVL(O.质量登记数_差异量,0),5 NUM FROM(
SELECT A.质量登记数_车间,B.质量登记数_差异量 FROM(
SELECT 1 质量登记数_车间,NULL 质量登记数_差异量 FROM dual UNION ALL
SELECT 1 质量登记数_车间,NULL 质量登记数_差异量 FROM dual UNION ALL
SELECT 1 质量登记数_车间,NULL 质量登记数_差异量 FROM dual UNION ALL
SELECT 1 质量登记数_车间,NULL 质量登记数_差异量 FROM dual UNION ALL
SELECT 2 质量登记数_车间,NULL 质量登记数_差异量 FROM dual UNION ALL
SELECT 2 质量登记数_车间,NULL 质量登记数_差异量 FROM dual UNION ALL
SELECT 2 质量登记数_车间,NULL 质量登记数_差异量 FROM dual UNION ALL
SELECT 2 质量登记数_车间,NULL 质量登记数_差异量 FROM dual UNION ALL
SELECT 3 质量登记数_车间,NULL 质量登记数_差异量 FROM dual UNION ALL
SELECT 3 质量登记数_车间,NULL 质量登记数_差异量 FROM dual UNION ALL
SELECT 3 质量登记数_车间,NULL 质量登记数_差异量 FROM dual UNION ALL
SELECT 3 质量登记数_车间,NULL 质量登记数_差异量 FROM dual) A
LEFT JOIN(
SELECT
TT.质量登记数_车间,
SUM(TT.质量登记数_差异量) 质量登记数_差异量
FROM(
SELECT
CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001002') = 1 THEN 1
		 WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                  AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN 2
		 WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 THEN 3 ELSE 0 END 质量登记数_车间,
CASE WHEN T.outputnum * T.counts = NULL THEN 0 ELSE T.outputnum * T.counts END 质量登记数_差异量
FROM(
SELECT s.groutingdailydetailid,s.outputnum,NVL(s.counts,0) counts,NVL(s.countt,0) countt
  FROM (SELECT a.groutingdailydetailid,a.outputnum,a.counts,tt.countt
          FROM (SELECT groutingdailydetailid,outputnum,COUNT(*) counts
                  FROM (SELECT 										 	groutingdailydetailid,
                            1 outputnum
                      FROM (SELECT 
                                    T.groutingdailydetailid
                               FROM (SELECT GDD.groutingdailydetailid
                                       FROM TP_PM_SCRAPPRODUCT SP
                                      INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                         ON HGDI.DATACODE = '50'
                                        AND HGDI.ITEMTYPE = 2
                                        AND HGDI.ITEMID = SP.PROCEDUREID
                                      INNER JOIN TP_PM_PRODUCTIONDATA PD
                                         ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
                                      INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                         ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                                      INNER JOIN TP_MST_GOODS G
                                         ON G.GOODSID = PD.GOODSID
                                      INNER JOIN TP_MST_GOODSTYPE GT
                                         ON G.GOODSTYPEID = GT.GOODSTYPEID
                                      WHERE SP.AUDITSTATUS = 1
                                        AND SP.AUDITDATE >= @DATEBEGIN@
                                        AND SP.AUDITDATE < @DATEEND@
                                     --AND SP.SCRAPTYPE IN (0, 2, 3) 
                                     ) T)
                    ---------------------------------------------------                      
                    UNION ALL
                    -- 工序报损撤销
                    ---------------------------------------------------                      
                    SELECT  groutingdailydetailid,
                            -1 SCRAPNUM
                      FROM (SELECT 
                                    T.groutingdailydetailid
                               FROM (SELECT gdd.groutingdailydetailid
                                       FROM TP_PM_SCRAPPRODUCT SP
                                      INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                         ON HGDI.DATACODE = '50'
                                        AND HGDI.ITEMTYPE = 2
                                        AND HGDI.ITEMID = SP.PROCEDUREID
                                      INNER JOIN TP_PM_PRODUCTIONDATA PD
                                         ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
                                      INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                         ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                                      INNER JOIN TP_MST_GOODS G
                                         ON G.GOODSID = PD.GOODSID
                                      INNER JOIN TP_MST_GOODSTYPE GT
                                         ON G.GOODSTYPEID = GT.GOODSTYPEID
                                      WHERE SP.AUDITSTATUS = 1
                                        AND SP.BACKOUTTIME >= @DATEBEGIN@
                                        AND SP.BACKOUTTIME < @DATEEND@
                                     --AND SP.SCRAPTYPE IN (0, 2, 3) 
                                     ) T)
                    
                    ---------------------------------------------------         
                    UNION ALL
                    -- 干补报损
                    ---------------------------------------------------       
                    SELECT groutingdailydetailid,
                            1
                      FROM (SELECT SP.groutingdailydetailid
                               FROM (SELECT T.*
                                       FROM (SELECT SP.BARCODE,
                                                    SP.GOODSCODE,
                                                    SP.groutingdailydetailid,
                                                    PD.PROCEDUREID,
                                                    PD.PRODUCTIONDATAID,
                                                    SP.AUDITDATE,
                                                    RANK() OVER(PARTITION BY SP.BARCODE ORDER BY PD.PRODUCTIONDATAID DESC) AS RK
                                               FROM TP_PM_SCRAPPRODUCT SP
                                              INNER JOIN TP_PM_PRODUCTIONDATA PD
                                                 ON SP.BARCODE = PD.BARCODE
                                                   -- 不包含干补工序
                                                AND PD.PROCEDUREID <> 18
                                                   -- 要找干补之前的工序
                                                AND PD.PRODUCTIONDATAID < SP.PRODUCTIONDATAID
                                                AND PD.VALUEFLAG = '1'
                                              WHERE SP.AUDITSTATUS = 1
                                                AND SP.PROCEDUREID = 18
                                                AND SP.AUDITDATE >= @DATEBEGIN@
                                                AND SP.AUDITDATE < @DATEEND@) T
                                      WHERE T.RK <= 1) SP
                              INNER JOIN TSAP_HEGII_DATAITEM HGDI
                                 ON HGDI.DATACODE = '50'
                                AND HGDI.ITEMTYPE = 2
                                AND HGDI.ITEMID = SP.PROCEDUREID
                              INNER JOIN TP_PM_PRODUCTIONDATA PD
                                 ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID
                              INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                                 ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID
                              INNER JOIN TP_MST_GOODS G
                                 ON G.GOODSID = PD.GOODSID
                              INNER JOIN TP_MST_GOODSTYPE GT
                                 ON G.GOODSTYPEID = GT.GOODSTYPEID))
                 GROUP BY groutingdailydetailid,outputnum) a
          LEFT JOIN (SELECT groutingdailydetailid,SCRAPNUM outputnum,COUNT(*) countt
                      FROM tsap_hegii_datalog_bg_detail THDBD
											LEFT JOIN TSAP_HEGII_DATALOG_BG THDB ON THDBD.LOGID = THDB.LOGID
                     WHERE THDBD.SCRAPNUM NOT IN (0)
										 AND THDB.DATACODE IN (50)
										 AND THDB.YYYYMMDD>=TO_CHAR(@DATEBEGIN@,'YYYYMMDD') AND THDB.YYYYMMDD<TO_CHAR(@DATEEND@,'YYYYMMDD')
                     GROUP BY groutingdailydetailid,SCRAPNUM) tt
            ON tt.groutingdailydetailid = a.groutingdailydetailid
           AND tt.outputnum = a.outputnum ) s
 WHERE NVL(s.counts,0)!=NVL(s.countt,0))T
 LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON T.groutingdailydetailid = GDD.groutingdailydetailid
 INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID)TT GROUP BY TT.质量登记数_车间 )B ON A.质量登记数_车间 = B.质量登记数_车间
)O


)Q ORDER BY Q.注浆数_车间,Q.NUM
                ";
                DateTime dateend = Convert.ToDateTime(context.Request["dateendMaster"]).AddDays(1);
                DateTime datebeginone = Convert.ToDateTime(context.Request["datebeginMaster"]).AddDays(-1);
                List<CDAParameter> sqlPara = new List<CDAParameter>();
                sqlPara.Add(new CDAParameter("DATEBEGIN", context.Request["datebeginMaster"].ToString(), DataType.DateTime));
                sqlPara.Add(new CDAParameter("DATEBEGINONE", datebeginone, 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());
            }


            //子表2
            if(context.Request["m"].ToString()=="2")
            {
                //读取报表数据
                string sqlStr = @"
                    SELECT 
                        '测试子表2' AS 测试列1,
                        '测试子表2' AS 测试列2,
                        '测试子表2' AS 测试列3,
                        '测试子表2' AS 测试列4,
                        '测试子表2' AS 测试列5
                    FROM 
                        DUAL
                ";

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

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


        }

    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}