HEGII.ss.mes.web/wwwroot/mes/rpt2/rpt8001/rpt.ashx

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

using System;
using System.Web;
using System.Web.SessionState;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using Curtain.DataAccess;
using DK.XuWei.WebMes;

public class rpt : IHttpHandler, IReadOnlySessionState
{
    public void ProcessRequest(HttpContext context)
    {
        context.Response.ContentType = "text/plain";
        using(IDataAccess conn = DataAccess.Create())
        {
            //主表
            if(context.Request["m"].ToString()=="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 sqlStr = @"
WITH
拼接日期 AS 
({DATESTR}),
注浆和成型报损和入库 AS 
(
	SELECT
		GROUPING_ID ( TO_CHAR( H.CREATETIME, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( H.CREATETIME, 'YYYYMM' ),'合计') AS 时间,
		
		SUM(CASE WHEN ( H.DATATYPE = 1 AND (INSTR( GDD.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1)  THEN 1 ELSE 0 END ) AS 注浆数_一部大件,
		SUM(CASE WHEN ( H.DATATYPE = 1 AND (INSTR( GT.GOODSTYPECODE, '001002' ) = 1))  THEN 1 ELSE 0 END ) AS 注浆数_一部小件,
		SUM(CASE WHEN ( H.DATATYPE = 1 AND (INSTR( GDD.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1)) THEN 1 ELSE 0 END ) AS 注浆数_一部全部,
		SUM(CASE WHEN ( H.DATATYPE = 1 AND ((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 ( H.DATATYPE = 1 AND (INSTR( GDD.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( gdd.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( GDD.GROUTINGLINECODE, 'C06B' ) = 1)) THEN 1 ELSE 0 END ) AS 注浆数_三部全部,
		SUM(DECODE(H.DATATYPE,1,1,0)) AS 注浆数_合计,
		
		SUM(CASE WHEN ( H.DATATYPE = 2 AND (INSTR( GDD.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1)  THEN 1 ELSE 0 END ) AS 注浆数撤销_一部大件,
		SUM(CASE WHEN ( H.DATATYPE = 2 AND (INSTR( GT.GOODSTYPECODE, '001002' ) = 1))  THEN 1 ELSE 0 END ) AS 注浆数撤销_一部小件,
		SUM(CASE WHEN ( H.DATATYPE = 2 AND (INSTR( GDD.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1)) THEN 1 ELSE 0 END ) AS 注浆数撤销_一部全部,
		SUM(CASE WHEN ( H.DATATYPE = 2 AND ((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 ( H.DATATYPE = 2 AND (INSTR( GDD.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( gdd.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( GDD.GROUTINGLINECODE, 'C06B' ) = 1)) THEN 1 ELSE 0 END ) AS 注浆数撤销_三部全部,
		SUM(DECODE(H.DATATYPE,2,1,0)) AS 注浆数撤销_合计,
		
		SUM(CASE WHEN ( H.DATATYPE = 3 AND (INSTR( GDD.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1)  THEN 1 ELSE 0 END ) AS 成型报损_一部大件,
		SUM(CASE WHEN ( H.DATATYPE = 3 AND (INSTR( GT.GOODSTYPECODE, '001002' ) = 1))  THEN 1 ELSE 0 END ) AS 成型报损_一部小件,
		SUM(CASE WHEN ( H.DATATYPE = 3 AND (INSTR( GDD.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1)) THEN 1 ELSE 0 END ) AS 成型报损_一部全部,
		SUM(CASE WHEN ( H.DATATYPE = 3 AND ((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 ( H.DATATYPE = 3 AND (INSTR( GDD.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( gdd.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( GDD.GROUTINGLINECODE, 'C06B' ) = 1)) THEN 1 ELSE 0 END ) AS 成型报损_三部全部,
		COUNT(DECODE(H.DATATYPE,3,1,0)) AS 成型报损_合计,
		
		SUM(CASE WHEN ( H.DATATYPE = 4 AND (INSTR( GDD.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1)  THEN 1 ELSE 0 END ) AS 成型报损撤销_一部大件,
		SUM(CASE WHEN ( H.DATATYPE = 4 AND (INSTR( GT.GOODSTYPECODE, '001002' ) = 1))  THEN 1 ELSE 0 END ) AS 成型报损撤销_一部小件,
		SUM(CASE WHEN ( H.DATATYPE = 4 AND (INSTR( GDD.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1)) THEN 1 ELSE 0 END ) AS 成型报损撤销_一部全部,
		SUM(CASE WHEN ( H.DATATYPE = 4 AND ((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 ( H.DATATYPE = 4 AND (INSTR( GDD.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( gdd.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( GDD.GROUTINGLINECODE, 'C06B' ) = 1)) THEN 1 ELSE 0 END ) AS 成型报损撤销_三部全部,
		SUM(DECODE(H.DATATYPE,4,1,0)) AS 成型报损撤销_合计,
		
		SUM(CASE WHEN ( H.DATATYPE = 51 AND (INSTR( GDD.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1)  THEN 1 ELSE 0 END ) AS 入库数_一部大件,
		SUM(CASE WHEN ( H.DATATYPE = 51 AND (INSTR( GT.GOODSTYPECODE, '001002' ) = 1))  THEN 1 ELSE 0 END ) AS 入库数_一部小件,
		SUM(CASE WHEN ( H.DATATYPE = 51 AND (INSTR( GDD.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1)) THEN 1 ELSE 0 END ) AS 入库数_一部全部,
		SUM(CASE WHEN ( H.DATATYPE = 51 AND ((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 ( H.DATATYPE = 51 AND (INSTR( GDD.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( gdd.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( GDD.GROUTINGLINECODE, 'C06B' ) = 1)) THEN 1 ELSE 0 END ) AS 入库数_三部全部,
		SUM(DECODE(H.DATATYPE,51,1,0)) AS 入库数_合计,
		
		SUM(CASE WHEN ( H.DATATYPE = 52 AND (INSTR( GDD.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1)  THEN 1 ELSE 0 END ) AS 入库数撤销_一部大件,
		SUM(CASE WHEN ( H.DATATYPE = 52 AND (INSTR( GT.GOODSTYPECODE, '001002' ) = 1))  THEN 1 ELSE 0 END ) AS 入库数撤销_一部小件,
		SUM(CASE WHEN ( H.DATATYPE = 52 AND (INSTR( GDD.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1)) THEN 1 ELSE 0 END ) AS 入库数撤销_一部全部,
		SUM(CASE WHEN ( H.DATATYPE = 52 AND ((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 ( H.DATATYPE = 52 AND (INSTR( GDD.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( gdd.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( GDD.GROUTINGLINECODE, 'C06B' ) = 1)) THEN 1 ELSE 0 END ) AS 入库数撤销_三部全部,
		SUM(DECODE(H.DATATYPE,52,1,0)) AS 入库数撤销_合计
		
	FROM
		TP_PM_GOODSCHANGEHISTORY H 
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = H.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON GDD.GROUTINGLINEID = GL.GROUTINGLINEID
	WHERE
		--H.DATATYPE = 2 --数据类型 (0：产品编码或SAP编码变更1：成型注浆 2：取消注浆3：成型损坯 4：撤销损坯11：在产清除 12：恢复清除51：成品交接 52：撤销交接)
		G.SCRAPSUMFLAG = '1'
		AND H.CREATETIME >= @DATEBEGIN@
		AND H.CREATETIME < @DATEEND@
		AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
		AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
		AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
		--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
		AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
	GROUP BY
		GROUPING SETS ( TO_CHAR( H.CREATETIME, 'YYYYMM' ), ( ) )
),
半检不合格 AS 
(
	SELECT
		GROUPING_ID ( TO_CHAR( S.SEMICHECKTIME, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( S.SEMICHECKTIME, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN (INSTR( S.GROUTINGLINECODE, 'A' ) = 1 AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1)  THEN 1 ELSE 0 END ) AS 半检不合格_一部大件,
		SUM(CASE WHEN (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 半检不合格_一部小件,
		SUM(CASE WHEN (INSTR( S.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 半检不合格_一部全部,
		SUM(CASE WHEN ((INSTR( S.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( S.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 半检不合格_二部全部,
		SUM(CASE WHEN (INSTR( S.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( S.GROUTINGLINECODE, 'A' ) = 4  OR INSTR( S.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 半检不合格_三部全部,
		COUNT ( * ) AS 半检不合格_合计
	FROM
		TP_PM_SEMICHECK S
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = S.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = S.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON S.GROUTINGLINEID = GL.GROUTINGLINEID
	WHERE
		S.SEMICHECKTYPE = 2
		AND S.SEMICHECKTIME >= @DATEBEGIN@
		AND S.SEMICHECKTIME < @DATEEND@
		AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
		AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
		AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
		--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
		AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
		--AND S.SEMICHECKTYPE = 2 --1 返工 2 不合格
		--AND S.RESEMICHECKTYPE = 0 --半检复检状态 (0：未复检、1：合格（返）、2：不合格（返）)
	GROUP BY
		GROUPING SETS ( TO_CHAR( S.SEMICHECKTIME, 'YYYYMM' ), ( ) )
),
半检不合格撤销 AS
(
	SELECT
		GROUPING_ID ( TO_CHAR( S.SEMICHECKTIME, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( S.SEMICHECKTIME, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN (INSTR( S.GROUTINGLINECODE, 'A' ) = 1 AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1)  THEN 1 ELSE 0 END ) AS 半检不合格_一部大件,
		SUM(CASE WHEN (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 半检不合格_一部小件,
		SUM(CASE WHEN (INSTR( S.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 半检不合格_一部全部,
		SUM(CASE WHEN ((INSTR( S.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( S.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 半检不合格_二部全部,
		SUM(CASE WHEN (INSTR( S.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( S.GROUTINGLINECODE, 'A' ) = 4  OR INSTR( S.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 半检不合格_三部全部,
		COUNT( * ) AS 半检不合格_合计 
	FROM
		TP_PM_SEMICHECK S
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = S.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = S.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON S.GROUTINGLINEID = GL.GROUTINGLINEID
	WHERE
		S.BACKOUTTIME >= @DATEBEGIN@
		AND S.BACKOUTTIME < @DATEEND@
		AND S.SEMICHECKTYPE = 2 --1 返工 2 不合格
		AND S.RESEMICHECKTYPE = 0 --半检复检状态 (0：未复检、1：合格（返）、2：不合格（返）)
		AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
		AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
		AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
		--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
		AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
	GROUP BY
		GROUPING SETS ( TO_CHAR( S.SEMICHECKTIME, 'YYYYMM' ), ( ) )
),
复检不合格 AS 
(
	SELECT
		GROUPING_ID ( TO_CHAR( S.RESEMICHECKTIME, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( S.RESEMICHECKTIME, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN (INSTR( S.GROUTINGLINECODE, 'A' ) = 1 AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1)  THEN 1 ELSE 0 END ) AS 复检不合格_一部大件,
		SUM(CASE WHEN (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 复检不合格_一部小件,
		SUM(CASE WHEN (INSTR( S.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 复检不合格_一部全部,
		SUM(CASE WHEN ((INSTR( S.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( S.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 复检不合格_二部全部,
		SUM(CASE WHEN (INSTR( S.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( S.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( S.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 复检不合格_三部全部,
		COUNT ( * ) AS 复检不合格_合计
	FROM
		TP_PM_SEMICHECK S
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = S.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = S.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON S.GROUTINGLINEID = GL.GROUTINGLINEID
	WHERE
		S.RESEMICHECKTYPE = 2
		AND S.RESEMICHECKTIME >= @DATEBEGIN@
		AND S.RESEMICHECKTIME < @DATEEND@
		AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
		AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
		AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
		--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
		AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
		--AND S.SEMICHECKTYPE = 2 --1 返工 2 不合格
		--AND S.RESEMICHECKTYPE = 0 --半检复检状态 (0：未复检、1：合格（返）、2：不合格（返）)
	GROUP BY
		GROUPING SETS ( TO_CHAR( S.RESEMICHECKTIME, 'YYYYMM' ), ( ) )
),
复检不合格撤销 AS 
(
	SELECT
		GROUPING_ID ( TO_CHAR( S.RESEMICHECKTIME, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( S.RESEMICHECKTIME, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN (INSTR( S.GROUTINGLINECODE, 'A' ) = 1 AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1)  THEN 1 ELSE 0 END ) AS 复检不合格_一部大件,
		SUM(CASE WHEN (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 复检不合格_一部小件,
		SUM(CASE WHEN (INSTR( S.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 复检不合格_一部全部,
		SUM(CASE WHEN ((INSTR( S.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( S.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 复检不合格_二部全部,
		SUM(CASE WHEN (INSTR( S.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( S.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( S.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 复检不合格_三部全部,
		COUNT ( * ) AS 复检不合格_合计
	FROM
		TP_PM_SEMICHECK S
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = S.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = S.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON S.GROUTINGLINEID = GL.GROUTINGLINEID
	WHERE
		S.RESEMICHECKTYPE = 2
		AND S.BACKOUTTIME >= @DATEBEGIN@
		AND S.BACKOUTTIME < @DATEEND@
		AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
		AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
		AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
		--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
		AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
		--AND S.SEMICHECKTYPE = 2 --1 返工 2 不合格
		--AND S.RESEMICHECKTYPE = 0 --半检复检状态 (0：未复检、1：合格（返）、2：不合格（返）)
	GROUP BY
		GROUPING SETS ( TO_CHAR( S.RESEMICHECKTIME, 'YYYYMM' ), ( ) )
),
半成品损坯数 AS
(
	SELECT
		GROUPING_ID ( TO_CHAR( P.AUDITDATE, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( P.AUDITDATE, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1  THEN 1 ELSE 0 END ) AS 半成品损坯数_一部大件,
		SUM(CASE WHEN (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 半成品损坯数_一部小件,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 半成品损坯数_一部全部,
		SUM(CASE WHEN ((INSTR( P.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( P.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 半成品损坯数_二部全部,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( P.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( P.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 半成品损坯数_三部全部,
		COUNT( * ) AS 合计 
	FROM
		TP_PM_SCRAPPRODUCT P
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON P.GROUTINGLINEID = GL.GROUTINGLINEID
	WHERE
		P.AUDITSTATUS = '1' 
		AND P.GOODSLEVELTYPEID IN ( 8, 9 ) -- 3废品、7次品、8损坯、9干补、10丢失、13不合格、14不合格（返）
		AND P.ISREFIRE <> '6' --非重烧品
		AND P.SCRAPTYPE = '0' --废弃类型 (0：损坯；1：质量登记；2：半检；3：半检复检)
		AND P.AUDITDATE >= @DATEBEGIN@
		AND P.AUDITDATE < @DATEEND@
		AND P.PROCEDUREID NOT IN ( 11, 128, 141, 130, 142,132,129,133,131,104, 120,143,123,140,121,122,124,125,106,105,127,12,66,79,107,113,126,78,80,134,13) 
		AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
		AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
		AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
		--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
		AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
	GROUP BY
		GROUPING SETS ( TO_CHAR( P.AUDITDATE, 'YYYYMM' ), ( ) )
),
半成品损坯数撤销 AS
(
	SELECT
		GROUPING_ID ( TO_CHAR( P.BACKOUTTIME, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( P.BACKOUTTIME, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1  THEN 1 ELSE 0 END ) AS 半成品损坯数_一部大件,
		SUM(CASE WHEN (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 半成品损坯数_一部小件,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 半成品损坯数_一部全部,
		SUM(CASE WHEN ((INSTR( P.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( P.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 半成品损坯数_二部全部,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( P.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( P.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 半成品损坯数_三部全部,
		COUNT( * ) AS 合计 
	FROM
		TP_PM_SCRAPPRODUCT P
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON P.GROUTINGLINEID = GL.GROUTINGLINEID
	WHERE
		P.AUDITSTATUS = '1' 
		AND P.GOODSLEVELTYPEID IN ( 8, 9 ) -- 3废品、7次品、8损坯、9干补、10丢失、13不合格、14不合格（返）
		AND P.ISREFIRE <> '6' --非重烧品
		AND P.SCRAPTYPE = '0' --废弃类型 (0：损坯；1：质量登记；2：半检；3：半检复检)
		AND P.BACKOUTTIME >= @DATEBEGIN@
		AND P.BACKOUTTIME < @DATEEND@
		AND P.PROCEDUREID NOT IN ( 11, 128, 141, 130, 142,132,129,133,131,104, 120,143,123,140,121,122,124,125,106,105,127,12,66,79,107,113,126,78,80,134,13) 
		AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
		AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
		AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
		--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
		AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
	GROUP BY
		GROUPING SETS ( TO_CHAR( P.BACKOUTTIME, 'YYYYMM' ), ( ) )
),
干补回收数 AS
(
	SELECT
		GROUPING_ID ( TO_CHAR( P.CREATETIME, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( P.CREATETIME, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1  THEN 1 ELSE 0 END ) AS 干补回收数_一部大件,
		SUM(CASE WHEN (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 干补回收数_一部小件,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 干补回收数_一部全部,
		SUM(CASE WHEN ((INSTR( P.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( P.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 干补回收数_二部全部,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( P.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( P.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 干补回收数_三部全部,
		COUNT( * ) AS 干补回收数_合计
	FROM
		TP_PM_PRODUCTIONDATA P
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON P.GROUTINGLINEID = GL.GROUTINGLINEID
	WHERE
		P.CREATETIME >= @DATEBEGIN@
		AND P.CREATETIME < @DATEEND@
		AND P.PROCEDUREID IN ( 18 ) -- 18 10-0 干补
		AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
		AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
		AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
		--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
		AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
	GROUP BY
		GROUPING SETS ( TO_CHAR( P.CREATETIME, 'YYYYMM' ), ( ) )
),
干补回收数撤销 AS
(
	SELECT
		GROUPING_ID ( TO_CHAR( P.CREATETIME, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( P.CREATETIME, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1  THEN 1 ELSE 0 END ) AS 干补回收数_一部大件,
		SUM(CASE WHEN (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 干补回收数_一部小件,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 干补回收数_一部全部,
		SUM(CASE WHEN ((INSTR( P.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( P.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 干补回收数_二部全部,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( P.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( P.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 干补回收数_三部全部,
		COUNT( * ) AS 干补回收数_合计
	FROM
		TP_PM_PRODUCTIONDATA P
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON P.GROUTINGLINEID = GL.GROUTINGLINEID
	WHERE
		P.BACKOUTTIME >= @DATEBEGIN@
		AND P.BACKOUTTIME < @DATEEND@
		AND P.PROCEDUREID IN ( 18 ) -- 18 10-0 干补
		AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
		AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
		AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
		--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
		AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
	GROUP BY
		GROUPING SETS ( TO_CHAR( P.CREATETIME, 'YYYYMM' ), ( ) )
),
出窑数 AS
(
	SELECT
		GROUPING_ID ( TO_CHAR( P.CREATETIME, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( P.CREATETIME, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1  THEN 1 ELSE 0 END ) AS 出窑数_一部大件,
		SUM(CASE WHEN (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 出窑数_一部小件,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 出窑数_一部全部,
		SUM(CASE WHEN ((INSTR( P.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( P.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 出窑数_二部全部,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( P.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( P.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 出窑数_三部全部,
		COUNT( * ) AS 出窑数_合计 
	FROM
		TP_PM_PRODUCTIONDATA P
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON P.GROUTINGLINEID = GL.GROUTINGLINEID
	WHERE
		P.CREATETIME >= @DATEBEGIN@
		AND P.CREATETIME < @DATEEND@
		AND P.ISREFIRE = '0'
		AND P.islengbu = '0'
		AND (
		 ( P.PROCEDUREID = 11  AND (P.CHECKFLAG = '1' or P.CHECKFLAG is null) ) -- 7-1成检出窑交接（用于一车间二车间）
			 OR ( P.PROCEDUREID = 104 AND P.CHECKFLAG = '1' )  --3#成检交接（用于三车间）
		) 
		AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
		AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
		AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
		--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
		AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
	GROUP BY
		GROUPING SETS ( TO_CHAR( P.CREATETIME, 'YYYYMM' ), ( ) )
),
质量登记次品数 AS
(
		SELECT
		GROUPING_ID ( TO_CHAR( P.CREATETIME, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( P.CREATETIME, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN P.ISREFIRE = 0 AND (INSTR( P.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( P.GOODSTYPECODE, '001001' ) = 1  THEN 1 ELSE 0 END ) AS 质量登记次品数_一部大件,
		SUM(CASE WHEN P.ISREFIRE = 0 AND (INSTR( P.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 质量登记次品数_一部小件,
		SUM(CASE WHEN P.ISREFIRE = 0 AND (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( P.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 质量登记次品数_一部全部,
		SUM(CASE WHEN P.ISREFIRE = 0 AND ((INSTR( P.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( P.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( P.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 质量登记次品数_二部全部,
		SUM(CASE WHEN P.ISREFIRE = 0 AND (INSTR( P.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( P.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( P.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 质量登记次品数_三部全部,
		SUM( DECODE(P.ISREFIRE,0,1,0) ) AS 质量登记次品数_合计,
		SUM(CASE WHEN P.ISREFIRE = 6 AND (INSTR( P.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( P.GOODSTYPECODE, '001001' ) = 1  THEN 1 ELSE 0 END ) AS 重烧登记次品数_一部大件,
		SUM(CASE WHEN P.ISREFIRE = 6 AND (INSTR( P.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 重烧登记次品数_一部小件,
		SUM(CASE WHEN P.ISREFIRE = 6 AND (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( P.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 重烧登记次品数_一部全部,
		SUM(CASE WHEN P.ISREFIRE = 6 AND ((INSTR( P.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( P.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( P.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 重烧登记次品数_二部全部,
		SUM(CASE WHEN P.ISREFIRE = 6 AND (INSTR( P.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( P.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( P.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 重烧登记次品数_三部全部,
		SUM( DECODE(P.ISREFIRE,6,1,0) ) AS 重烧登记次品数_合计
	  FROM (
	  SELECT 
	  distinct P.CREATETIME,
	  P.ISREFIRE,
	  P.GROUTINGLINECODE,
	  P.barcode, 
	  GT.GOODSTYPECODE
	   FROM  
	  TP_PM_PRODUCTIONDATA P
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON P.GROUTINGLINEID = GL.GROUTINGLINEID
		INNER JOIN TP_PM_DEFECT dfc on dfc.productiondataid=p.productiondataid
	  WHERE P.MODELTYPE IN (-1, -4, -5)
			AND P.CHECKBATCHNO = 1
			AND P.VALUEFLAG = '1'
		--AND P.ISREFIRE = '0'
		AND P.GOODSLEVELTYPEID = 7
		AND P.CREATETIME >= @DATEBEGIN@
		AND P.CREATETIME < @DATEEND@
		AND length(p.kilncarbatchno) > 0
		-- AND P.PROCEDUREID IN ( 12, 59, 105 ) --8-1质量登记 8-2重烧质量登记 3#质量登记
		AND P.GOODSLEVELTYPEID = 7 --产品分级类别 (1无缺陷、2有缺陷、3废品、4正品、5副品、6重烧、7次品、9干补)
		AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
		AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
		AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
		--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
		AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)) P
	  GROUP BY
		GROUPING SETS ( TO_CHAR( P.CREATETIME, 'YYYYMM' ), ( ) )
),
次品未判缺陷数 AS
(
	SELECT
		GROUPING_ID ( TO_CHAR( P.CREATETIME, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( P.CREATETIME, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1  THEN 1 ELSE 0 END ) AS 次品未判缺陷数_一部大件,
		SUM(CASE WHEN (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 次品未判缺陷数_一部小件,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 次品未判缺陷数_一部全部,
		SUM(CASE WHEN ((INSTR( P.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( P.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 次品未判缺陷数_二部全部,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( P.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( P.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 次品未判缺陷数_三部全部,
		COUNT( * ) AS 次品未判缺陷数_合计
	FROM
		TP_PM_PRODUCTIONDATA P
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON P.GROUTINGLINEID = GL.GROUTINGLINEID
		LEFT JOIN TP_PM_DEFECT TPD ON TPD.BARCODE = P.BARCODE
	WHERE
	P.CREATETIME >= @DATEBEGIN@ 
	AND P.CREATETIME < @DATEEND@
	AND P.GOODSLEVELTYPEID = 7 
	AND P.VALUEFLAG = 1 
	AND P.CHECKBATCHNO = 1 
	AND TPD.defectname is null
		AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
		AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
		AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
		--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
		AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
	GROUP BY
		GROUPING SETS ( TO_CHAR( P.CREATETIME, 'YYYYMM' ), ( ) )
),
质量登记次品数改判 AS
(
	SELECT
		GROUPING_ID ( TO_CHAR( P.BACKOUTTIME, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( P.BACKOUTTIME, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN P.ISREFIRE = 0 AND (INSTR( P.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1  THEN 1 ELSE 0 END ) AS 质量登记次品数_一部大件,
		SUM(CASE WHEN P.ISREFIRE = 0 AND (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 质量登记次品数_一部小件,
		SUM(CASE WHEN P.ISREFIRE = 0 AND (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 质量登记次品数_一部全部,
		SUM(CASE WHEN P.ISREFIRE = 0 AND ((INSTR( P.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( P.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 质量登记次品数_二部全部,
		SUM(CASE WHEN P.ISREFIRE = 0 AND (INSTR( P.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( P.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( P.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 质量登记次品数_三部全部,
		SUM( DECODE(P.ISREFIRE,0,1,0) ) AS 质量登记次品数_合计,
		SUM(CASE WHEN P.ISREFIRE = 6 AND (INSTR( P.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1  THEN 1 ELSE 0 END ) AS 重烧登记次品数_一部大件,
		SUM(CASE WHEN P.ISREFIRE = 6 AND (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 重烧登记次品数_一部小件,
		SUM(CASE WHEN P.ISREFIRE = 6 AND (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR (INSTR( P.GROUTINGLINECODE, 'D' ) = 1 AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1) ) THEN 1 ELSE 0 END ) AS 重烧登记次品数_一部全部,
		SUM(CASE WHEN P.ISREFIRE = 6 AND ((INSTR( P.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( P.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 重烧登记次品数_二部全部,
		SUM(CASE WHEN P.ISREFIRE = 6 AND (INSTR( P.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( P.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( P.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 重烧登记次品数_三部全部,
		SUM( DECODE(P.ISREFIRE,6,1,0) ) AS 重烧登记次品数_合计
	FROM TP_PM_PRODUCTIONDATA P
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON P.GROUTINGLINEID = GL.GROUTINGLINEID
	WHERE P.MODELTYPE IN (-1, -4, -5)
        AND P.CHECKBATCHNO = 1
        AND P.VALUEFLAG = '1'
		--AND P.ISREFIRE = '0'
		AND P.GOODSLEVELTYPEID = 7
		AND P.BACKOUTTIME >= @DATEBEGIN@
		AND P.BACKOUTTIME < @DATEEND@
		-- AND P.PROCEDUREID IN ( 12, 59, 105 ) --8-1质量登记 8-2重烧质量登记 3#质量登记
		AND P.GOODSLEVELTYPEID = 7 --产品分级类别 (1无缺陷、2有缺陷、3废品、4正品、5副品、6重烧、7次品、9干补)
		AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
		AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
		AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
		--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
		AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
	GROUP BY
		GROUPING SETS ( TO_CHAR( P.BACKOUTTIME, 'YYYYMM' ), ( ) )
),
质量登记损坯数 AS
(
	SELECT
		GROUPING_ID ( TO_CHAR( P.AUDITDATE, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( P.AUDITDATE, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1  THEN 1 ELSE 0 END ) AS 质量登记损坯数_一部大件,
		SUM(CASE WHEN (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 质量登记损坯数_一部小件,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 质量登记损坯数_一部全部,
		SUM(CASE WHEN ((INSTR( P.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( P.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 质量登记损坯数_二部全部,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( P.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( P.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 质量登记损坯数_三部全部,
		COUNT( * ) AS 质量登记损坯数_数量 
	FROM
		TP_PM_SCRAPPRODUCT P
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON P.GROUTINGLINEID = GL.GROUTINGLINEID
	WHERE
		P.AUDITSTATUS = '1' 
		AND P.GOODSLEVELTYPEID IN ( 8, 9 ) -- 3废品、7次品、8损坯、9干补、10丢失、13不合格、14不合格（返）
		AND P.SCRAPTYPE = '0' --废弃类型 (0：损坯；1：质量登记；2：半检；3：半检复检)
		AND P.AUDITDATE >= @DATEBEGIN@
		AND P.AUDITDATE < @DATEEND@
		AND P.PROCEDUREID IN (11, 128, 141, 130, 142,132,129,133,131,104, 120,143,123,140,121,122,124,125,106,105,127,12,66,79,107,113,126,78,80,134,13) 
		AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
		AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
		AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
		--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
		AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
	GROUP BY
		GROUPING SETS ( TO_CHAR( P.AUDITDATE, 'YYYYMM' ), ( ) )
),
质量登记损坯数撤销 AS
(
	SELECT
		GROUPING_ID ( TO_CHAR( P.BACKOUTTIME, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( P.BACKOUTTIME, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1  THEN 1 ELSE 0 END ) AS 质量登记损坯数_一部大件,
		SUM(CASE WHEN (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 质量登记损坯数_一部小件,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 质量登记损坯数_一部全部,
		SUM(CASE WHEN ((INSTR( P.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( P.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 质量登记损坯数_二部全部,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( P.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( P.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 质量登记损坯数_三部全部,
		COUNT( * ) AS 质量登记损坯数_数量 
	FROM
		TP_PM_SCRAPPRODUCT P
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON P.GROUTINGLINEID = GL.GROUTINGLINEID
	WHERE
		P.AUDITSTATUS = '1' 
		AND P.GOODSLEVELTYPEID IN ( 8, 9 ) -- 3废品、7次品、8损坯、9干补、10丢失、13不合格、14不合格（返）
		AND P.SCRAPTYPE = '0' --废弃类型 (0：损坯；1：质量登记；2：半检；3：半检复检)
		AND P.BACKOUTTIME >= @DATEBEGIN@
		AND P.BACKOUTTIME < @DATEEND@
		AND P.PROCEDUREID IN ( 11, 128, 141, 130, 142,132,129,133,131,104, 120,143,123,140,121,122,124,125,106,105,127,12,66,79,107,113,126,78,80,134,13) 
		AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
		AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
		AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
		--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
		AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
	GROUP BY
		GROUPING SETS ( TO_CHAR( P.BACKOUTTIME, 'YYYYMM' ), ( ) )
),
重烧损坯数 AS
(
	SELECT
		GROUPING_ID ( TO_CHAR( P.AUDITDATE, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( P.AUDITDATE, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1  THEN 1 ELSE 0 END ) AS 重烧损坯数_一部大件,
		SUM(CASE WHEN (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 重烧损坯数_一部小件,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 重烧损坯数_一部全部,
		SUM(CASE WHEN ((INSTR( P.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( P.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 重烧损坯数_二部全部,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( P.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( P.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 重烧损坯数_三部全部,
		COUNT( * ) AS 重烧损坯数_数量 
	FROM
		TP_PM_SCRAPPRODUCT P
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON P.GROUTINGLINEID = GL.GROUTINGLINEID
	WHERE
		P.AUDITSTATUS = '1' 
		AND P.GOODSLEVELTYPEID IN ( 8, 9 ) -- 3废品、7次品、8损坯、9干补、10丢失、13不合格、14不合格（返）
		AND P.SCRAPTYPE = '0' --废弃类型 (0：损坯；1：质量登记；2：半检；3：半检复检)
		AND P.AUDITDATE >= @DATEBEGIN@
		AND P.AUDITDATE < @DATEEND@
		AND P.PROCEDUREID IN ( 57, 60, 14, 16, 113 ) --57 4-5重烧交接 60 4-6重烧补釉 14 4-7重烧登窑 16 6-2重烧卸窑 113 3#冷补
		AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
		AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
		AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
		--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
		AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
	GROUP BY
		GROUPING SETS ( TO_CHAR( P.AUDITDATE, 'YYYYMM' ), ( ) )
),
重烧损坯数撤销 AS
(
	SELECT
		GROUPING_ID ( TO_CHAR( P.BACKOUTTIME, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( P.BACKOUTTIME, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1  THEN 1 ELSE 0 END ) AS 重烧损坯数_一部大件,
		SUM(CASE WHEN (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 重烧损坯数_一部小件,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 重烧损坯数_一部全部,
		SUM(CASE WHEN ((INSTR( P.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( P.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 重烧损坯数_二部全部,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( P.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( P.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 重烧损坯数_三部全部,
		COUNT( * ) AS 重烧损坯数_数量 
	FROM
		TP_PM_SCRAPPRODUCT P
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON P.GROUTINGLINEID = GL.GROUTINGLINEID
	WHERE
		P.AUDITSTATUS = '1' 
		AND P.GOODSLEVELTYPEID IN ( 8, 9 ) -- 3废品、7次品、8损坯、9干补、10丢失、13不合格、14不合格（返）
		AND P.SCRAPTYPE = '0' --废弃类型 (0：损坯；1：质量登记；2：半检；3：半检复检)
		AND P.BACKOUTTIME >= @DATEBEGIN@
		AND P.BACKOUTTIME < @DATEEND@
		AND P.PROCEDUREID IN ( 57, 60, 14, 16, 113 ) --57 4-5重烧交接 60 4-6重烧补釉 14 4-7重烧登窑 16 6-2重烧卸窑 113 3#冷补
		AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
		AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
		AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
		--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
		AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
	GROUP BY
		GROUPING SETS ( TO_CHAR( P.BACKOUTTIME, 'YYYYMM' ), ( ) )
),
回收数 AS
(
	SELECT
		GROUPING_ID ( TO_CHAR( P.CREATETIME, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( P.CREATETIME, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1  THEN 1 ELSE 0 END ) AS 回收数_一部大件,
		SUM(CASE WHEN (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 回收数_一部小件,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 回收数_一部全部,
		SUM(CASE WHEN ((INSTR( P.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( P.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 回收数_二部全部,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( P.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( P.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 回收数_三部全部,
		COUNT( * ) AS 回收数_合计
	FROM
		TP_PM_PRODUCTIONDATA P
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON P.GROUTINGLINEID = GL.GROUTINGLINEID
	WHERE
		P.CREATETIME >= @DATEBEGIN@
		AND P.CREATETIME < @DATEEND@
		AND P.PROCEDUREID IN ( 80 ) -- 80 10-2回收
		AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
		AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
		AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
		--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
		AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
	GROUP BY
		GROUPING SETS ( TO_CHAR( P.CREATETIME, 'YYYYMM' ), ( ) )
),
回收数撤销 AS
(
	SELECT
		GROUPING_ID ( TO_CHAR( P.CREATETIME, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( P.CREATETIME, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1  THEN 1 ELSE 0 END ) AS 回收数_一部大件,
		SUM(CASE WHEN (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 回收数_一部小件,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 回收数_一部全部,
		SUM(CASE WHEN ((INSTR( P.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( P.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 回收数_二部全部,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( P.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( P.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 回收数_三部全部,
		COUNT( * ) AS 回收数_合计
	FROM
		TP_PM_PRODUCTIONDATA P
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON P.GROUTINGLINEID = GL.GROUTINGLINEID
	WHERE
		P.BACKOUTTIME >= @DATEBEGIN@
		AND P.BACKOUTTIME < @DATEEND@
		AND P.PROCEDUREID IN ( 80 ) -- 80 10-2回收
		AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
		AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
		AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
		--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
		AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
	GROUP BY
		GROUPING SETS ( TO_CHAR( P.CREATETIME, 'YYYYMM' ), ( ) )
),
回收次品数 AS
(
	SELECT
		GROUPING_ID ( TO_CHAR( P.CREATETIME, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( P.CREATETIME, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1  THEN 1 ELSE 0 END ) AS 回收次品数_一部大件,
		SUM(CASE WHEN (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 回收次品数_一部小件,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 回收次品数_一部全部,
		SUM(CASE WHEN ((INSTR( P.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( P.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 )  THEN 1 ELSE 0 END ) AS 回收次品数_二部全部,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( P.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( P.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 回收次品数_三部全部,
		COUNT( * ) AS 回收次品数_合计
	FROM
		TP_PM_PRODUCTIONDATA P
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON P.GROUTINGLINEID = GL.GROUTINGLINEID
	WHERE
	P.CREATETIME >= @DATEBEGIN@
	AND P.CREATETIME < @DATEEND@
	AND P.GOODSLEVELTYPEID = 7 
	AND P.VALUEFLAG = 1 
	AND P.CHECKBATCHNO = 1 
	AND GDD.RECYCLINGFLAG = 1
	AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
	AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
	AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
	--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
	AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
	GROUP BY
		GROUPING SETS ( TO_CHAR( P.CREATETIME, 'YYYYMM' ), ( ) )
),
裸瓷包装数 AS
(
	SELECT
		GROUPING_ID ( TO_CHAR( P.CREATETIME, 'YYYYMM' ) ) AS GID,
		NVL(TO_CHAR( P.CREATETIME, 'YYYYMM' ),'合计') AS 时间,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1  THEN 1 ELSE 0 END ) AS 裸瓷包装数_一部大件,
		SUM(CASE WHEN (INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  THEN 1 ELSE 0 END ) AS 裸瓷包装数_一部小件,
		SUM(CASE WHEN (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 裸瓷包装数_一部全部,
        SUM(CASE WHEN ((instr(p.groutinglinecode, 'B') = 1 OR instr(p.groutinglinecode, 'D') = 1) AND instr(gt.goodstypecode, '001001') = 1) THEN 1 ELSE  0 END) AS 裸瓷包装数_二部全部,
        SUM(CASE WHEN (instr(p.groutinglinecode, 'C') = 1 AND instr(p.groutinglinecode, 'A') = 4 OR instr(p.groutinglinecode, 'C06B') = 1) THEN 1 ELSE 0 END) AS 裸瓷包装数_三部全部,
        COUNT(*) AS 裸瓷包装数_合计
	FROM
		TP_PM_PRODUCTIONDATA P
		INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID
		INNER JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID
		INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID 
		INNER JOIN TP_PC_GROUTINGLINE GL ON P.GROUTINGLINEID = GL.GROUTINGLINEID
	WHERE
	P.CREATETIME >= @DATEBEGIN@
	AND P.CREATETIME < @DATEEND@
	AND P.PROCEDUREID = 159
	AND P.VALUEFLAG = 1  
	AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0,0') 
	AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0,0') 
	AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
	--AND (GDD.TESTMOULDFLAG = @TESTMOULDFLAG@ OR @TESTMOULDFLAG@ IS NULL)
		AND (((@TESTMOULDFLAG@ = 0 AND GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@ AND (GDD.TESTFORMFLAG IN(0,1) OR GDD.TESTFORMFLAG IS NULL ) )) OR
             (@TESTMOULDFLAG@ = 1 AND (GDD.TESTFORMFLAG = 2 OR GDD.TESTMOULDFLAG  = @TESTMOULDFLAG@))  OR @TESTMOULDFLAG@ IS NULL  OR @TESTMOULDFLAG@ = '') 
	AND (GDD.TESTFLAG = @TESTFLAG@ OR @TESTFLAG@ IS NULL)
	GROUP BY
		GROUPING SETS ( TO_CHAR( P.CREATETIME, 'YYYYMM' ), ( ) )
)

SELECT
	拼接日期.时间,
	NVL(注浆和成型报损和入库.注浆数_一部小件,0) - NVL(注浆和成型报损和入库.注浆数撤销_一部小件,0) AS 注浆数_一部小件,
	NVL(注浆和成型报损和入库.注浆数_一部大件,0) - NVL(注浆和成型报损和入库.注浆数撤销_一部大件,0) AS 注浆数_一部大件,
	NVL(注浆和成型报损和入库.注浆数_一部全部,0) - NVL(注浆和成型报损和入库.注浆数撤销_一部全部,0) AS 注浆数_一部全部,
	NVL(注浆和成型报损和入库.注浆数_二部全部,0) - NVL(注浆和成型报损和入库.注浆数撤销_二部全部,0) AS 注浆数_二部全部,
	NVL(注浆和成型报损和入库.注浆数_三部全部,0) - NVL(注浆和成型报损和入库.注浆数撤销_三部全部,0) AS 注浆数_三部全部,
	NVL(注浆和成型报损和入库.成型报损_一部小件,0) - NVL(注浆和成型报损和入库.成型报损撤销_一部小件,0) AS 成型报损_一部小件,
	NVL(注浆和成型报损和入库.成型报损_一部大件,0) - NVL(注浆和成型报损和入库.成型报损撤销_一部大件,0) AS 成型报损_一部大件,
	NVL(注浆和成型报损和入库.成型报损_一部全部,0) - NVL(注浆和成型报损和入库.成型报损撤销_一部全部,0) AS 成型报损_一部全部,
	NVL(注浆和成型报损和入库.成型报损_二部全部,0) - NVL(注浆和成型报损和入库.成型报损撤销_二部全部,0) AS 成型报损_二部全部,
	NVL(注浆和成型报损和入库.成型报损_三部全部,0) - NVL(注浆和成型报损和入库.成型报损撤销_三部全部,0) AS 成型报损_三部全部,
	
	NVL(半检不合格.半检不合格_一部小件,0) - NVL(半检不合格撤销.半检不合格_一部小件,0) AS 半检不合格_一部小件,
	NVL(半检不合格.半检不合格_一部大件,0) - NVL(半检不合格撤销.半检不合格_一部大件,0) AS 半检不合格_一部大件,
	NVL(半检不合格.半检不合格_一部全部,0) - NVL(半检不合格撤销.半检不合格_一部全部,0) AS 半检不合格_一部全部,
	NVL(半检不合格.半检不合格_二部全部,0) - NVL(半检不合格撤销.半检不合格_二部全部,0) AS 半检不合格_二部全部,
	NVL(半检不合格.半检不合格_三部全部,0) - NVL(半检不合格撤销.半检不合格_三部全部,0) AS 半检不合格_三部全部,
	
	NVL(复检不合格.复检不合格_一部小件,0) - NVL(复检不合格撤销.复检不合格_一部小件,0) AS 复检不合格_一部小件,
	NVL(复检不合格.复检不合格_一部大件,0) - NVL(复检不合格撤销.复检不合格_一部大件,0) AS 复检不合格_一部大件,
	NVL(复检不合格.复检不合格_一部全部,0) - NVL(复检不合格撤销.复检不合格_一部全部,0) AS 复检不合格_一部全部,
	NVL(复检不合格.复检不合格_二部全部,0) - NVL(复检不合格撤销.复检不合格_二部全部,0) AS 复检不合格_二部全部,
	NVL(复检不合格.复检不合格_三部全部,0) - NVL(复检不合格撤销.复检不合格_三部全部,0) AS 复检不合格_三部全部,

	NVL(半成品损坯数.半成品损坯数_一部小件,0) - NVL(半成品损坯数撤销.半成品损坯数_一部小件,0) AS 半成品损坯数_一部小件,
	NVL(半成品损坯数.半成品损坯数_一部大件,0) - NVL(半成品损坯数撤销.半成品损坯数_一部大件,0) AS 半成品损坯数_一部大件,
	NVL(半成品损坯数.半成品损坯数_一部全部,0) - NVL(半成品损坯数撤销.半成品损坯数_一部全部,0) AS 半成品损坯数_一部全部,
	NVL(半成品损坯数.半成品损坯数_二部全部,0) - NVL(半成品损坯数撤销.半成品损坯数_二部全部,0) AS 半成品损坯数_二部全部,
	NVL(半成品损坯数.半成品损坯数_三部全部,0) - NVL(半成品损坯数撤销.半成品损坯数_三部全部,0) AS 半成品损坯数_三部全部,
	
	NVL(干补回收数.干补回收数_一部小件,0) - NVL(干补回收数撤销.干补回收数_一部小件,0) AS 干补回收数_一部小件,
	NVL(干补回收数.干补回收数_一部大件,0) - NVL(干补回收数撤销.干补回收数_一部大件,0) AS 干补回收数_一部大件,
	NVL(干补回收数.干补回收数_一部全部,0) - NVL(干补回收数撤销.干补回收数_一部全部,0) AS 干补回收数_一部全部,
	NVL(干补回收数.干补回收数_二部全部,0) - NVL(干补回收数撤销.干补回收数_二部全部,0) AS 干补回收数_二部全部,
	NVL(干补回收数.干补回收数_三部全部,0) - NVL(干补回收数撤销.干补回收数_三部全部,0) AS 干补回收数_三部全部,
	
	--不做冲减
	NVL( 出窑数.出窑数_一部小件, 0 ) AS 出窑数_一部小件,
	NVL( 出窑数.出窑数_一部大件, 0 ) AS 出窑数_一部大件,
	NVL( 出窑数.出窑数_一部全部, 0 ) AS 出窑数_一部全部,
	NVL( 出窑数.出窑数_二部全部, 0 ) AS 出窑数_二部全部,
	NVL( 出窑数.出窑数_三部全部, 0 ) AS 出窑数_三部全部,
	
	NVL(质量登记次品数.质量登记次品数_一部小件,0) - NVL(质量登记次品数改判.质量登记次品数_一部小件,0) AS 质量登记次品数_一部小件,
	NVL(质量登记次品数.质量登记次品数_一部大件,0) - NVL(质量登记次品数改判.质量登记次品数_一部大件,0) AS 质量登记次品数_一部大件,
	NVL(质量登记次品数.质量登记次品数_一部全部,0) - NVL(质量登记次品数改判.质量登记次品数_一部全部,0) AS 质量登记次品数_一部全部,
	NVL(质量登记次品数.质量登记次品数_二部全部,0) - NVL(质量登记次品数改判.质量登记次品数_二部全部,0) AS 质量登记次品数_二部全部,
	NVL(质量登记次品数.质量登记次品数_三部全部,0) - NVL(质量登记次品数改判.质量登记次品数_三部全部,0) AS 质量登记次品数_三部全部,
	NVL(质量登记次品数.重烧登记次品数_一部小件,0) - NVL(质量登记次品数改判.重烧登记次品数_一部小件,0) AS 重烧登记次品数_一部小件,
	NVL(质量登记次品数.重烧登记次品数_一部大件,0) - NVL(质量登记次品数改判.重烧登记次品数_一部大件,0) AS 重烧登记次品数_一部大件,
	NVL(质量登记次品数.重烧登记次品数_一部全部,0) - NVL(质量登记次品数改判.重烧登记次品数_一部全部,0) AS 重烧登记次品数_一部全部,
	NVL(质量登记次品数.重烧登记次品数_二部全部,0) - NVL(质量登记次品数改判.重烧登记次品数_二部全部,0) AS 重烧登记次品数_二部全部,
	NVL(质量登记次品数.重烧登记次品数_三部全部,0) - NVL(质量登记次品数改判.重烧登记次品数_三部全部,0) AS 重烧登记次品数_三部全部,

	NVL(次品未判缺陷数.次品未判缺陷数_一部小件,0)  AS 次品未判缺陷数_一部小件,
	NVL(次品未判缺陷数.次品未判缺陷数_一部大件,0)  AS 次品未判缺陷数_一部大件,
	NVL(次品未判缺陷数.次品未判缺陷数_一部全部,0)  AS 次品未判缺陷数_一部全部,
	NVL(次品未判缺陷数.次品未判缺陷数_二部全部,0)  AS 次品未判缺陷数_二部全部,
	NVL(次品未判缺陷数.次品未判缺陷数_三部全部,0)  AS 次品未判缺陷数_三部全部,

	NVL(质量登记损坯数.质量登记损坯数_一部小件,0) - NVL(质量登记损坯数撤销.质量登记损坯数_一部小件,0) AS 质量登记损坯数_一部小件,
	NVL(质量登记损坯数.质量登记损坯数_一部大件,0) - NVL(质量登记损坯数撤销.质量登记损坯数_一部大件,0) AS 质量登记损坯数_一部大件,
	NVL(质量登记损坯数.质量登记损坯数_一部全部,0) - NVL(质量登记损坯数撤销.质量登记损坯数_一部全部,0) AS 质量登记损坯数_一部全部,
	NVL(质量登记损坯数.质量登记损坯数_二部全部,0) - NVL(质量登记损坯数撤销.质量登记损坯数_二部全部,0) AS 质量登记损坯数_二部全部,
	NVL(质量登记损坯数.质量登记损坯数_三部全部,0) - NVL(质量登记损坯数撤销.质量登记损坯数_三部全部,0) AS 质量登记损坯数_三部全部,
	
	NVL(重烧损坯数.重烧损坯数_一部小件,0) - NVL(重烧损坯数撤销.重烧损坯数_一部小件,0) AS 重烧损坯数_一部小件,
	NVL(重烧损坯数.重烧损坯数_一部大件,0) - NVL(重烧损坯数撤销.重烧损坯数_一部大件,0) AS 重烧损坯数_一部大件,
	NVL(重烧损坯数.重烧损坯数_一部全部,0) - NVL(重烧损坯数撤销.重烧损坯数_一部全部,0) AS 重烧损坯数_一部全部,
	NVL(重烧损坯数.重烧损坯数_二部全部,0) - NVL(重烧损坯数撤销.重烧损坯数_二部全部,0) AS 重烧损坯数_二部全部,
	NVL(重烧损坯数.重烧损坯数_三部全部,0) - NVL(重烧损坯数撤销.重烧损坯数_三部全部,0) AS 重烧损坯数_三部全部,
	
	NVL(回收数.回收数_一部小件,0) - NVL(回收数撤销.回收数_一部小件,0) AS 回收数_一部小件,
	NVL(回收数.回收数_一部大件,0) - NVL(回收数撤销.回收数_一部大件,0) AS 回收数_一部大件,
	NVL(回收数.回收数_一部全部,0) - NVL(回收数撤销.回收数_一部全部,0) AS 回收数_一部全部,
	NVL(回收数.回收数_二部全部,0) - NVL(回收数撤销.回收数_二部全部,0) AS 回收数_二部全部,
	NVL(回收数.回收数_三部全部,0) - NVL(回收数撤销.回收数_三部全部,0) AS 回收数_三部全部,

	NVL(回收次品数.回收次品数_一部小件,0)  AS 回收次品数_一部小件,
	NVL(回收次品数.回收次品数_一部大件,0)  AS 回收次品数_一部大件,
	NVL(回收次品数.回收次品数_一部全部,0)  AS 回收次品数_一部全部,
	NVL(回收次品数.回收次品数_二部全部,0)  AS 回收次品数_二部全部,
	NVL(回收次品数.回收次品数_三部全部,0)  AS 回收次品数_三部全部,

	NVL(注浆和成型报损和入库.入库数_一部小件,0) - NVL(注浆和成型报损和入库.入库数撤销_一部小件,0) AS 入库数_一部小件,
	NVL(注浆和成型报损和入库.入库数_一部大件,0) - NVL(注浆和成型报损和入库.入库数撤销_一部大件,0) AS 入库数_一部大件,
	NVL(注浆和成型报损和入库.入库数_一部全部,0) - NVL(注浆和成型报损和入库.入库数撤销_一部全部,0) AS 入库数_一部全部,
	NVL(注浆和成型报损和入库.入库数_二部全部,0) - NVL(注浆和成型报损和入库.入库数撤销_二部全部,0) AS 入库数_二部全部,
	NVL(注浆和成型报损和入库.入库数_三部全部,0) - NVL(注浆和成型报损和入库.入库数撤销_三部全部,0) AS 入库数_三部全部,

	NVL(裸瓷包装数.裸瓷包装数_二部全部,0)  AS 裸瓷包装数_二部全部,
	NVL(裸瓷包装数.裸瓷包装数_三部全部,0)  AS 裸瓷包装数_三部全部
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 拼接日期.时间 = 质量登记损坯数.时间
	LEFT JOIN 质量登记损坯数撤销 ON 拼接日期.时间 = 质量登记损坯数撤销.时间
	LEFT JOIN 重烧损坯数 ON 拼接日期.时间 = 重烧损坯数.时间
	LEFT JOIN 重烧损坯数撤销 ON 拼接日期.时间 = 重烧损坯数撤销.时间
	LEFT JOIN 回收数 ON 拼接日期.时间 = 回收数.时间
	LEFT JOIN 回收数撤销 ON 拼接日期.时间 = 回收数撤销.时间
	LEFT JOIN 回收次品数 ON 拼接日期.时间 = 回收次品数.时间
	LEFT JOIN 次品未判缺陷数 ON 拼接日期.时间 = 次品未判缺陷数.时间
    LEFT JOIN 裸瓷包装数 ON 拼接日期.时间 = 裸瓷包装数.时间
ORDER BY 
	拼接日期.时间
                ".Replace("YYYYMM",totalMaster)
                 .Replace("{GOODSID}",goodsId)
                 .Replace("{GROUTINGLINEID}",groutinglineId)
                 .Replace("{DATESTR}",datestr);

                //获取查询条件
                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")
                            .Replace("二部全部","4").Replace("三部全部","5");
                    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);
                }
                ////裸瓷包装数
                //   string strsql3 = @"SELECT  COUNT(DISTINCT P.BARCODE) as count ,TO_CHAR( TRUNC( P.CREATETIME ) , 'YYYYMMDD' ) as time
                //                                FROM TP_PM_PRODUCTIONDATA P
                //                                WHERE P.PROCEDUREID =159
                //                    AND p.VALUEFLAG=1
                //                                AND P.CREATETIME >= to_date('"+dateBegin+"','YYYY-MM-DD HH24:MI:SS')"+
                //                    "AND P.CREATETIME < to_date('"+dateEnd+"','YYYY-MM-DD HH24:MI:SS')"+
                //                       "GROUP BY TRUNC(P.CREATETIME) ORDER BY time";
                //            DataTable dt3 = conn.ExecuteDatatable(strsql3);
                //            DataRow dr1 = dt2.NewRow();
                //            dr1["部门序号"] = "6";
                //            dr1["部门"] = "裸瓷包装数";
                //            dr1["工序"] = "裸瓷包装";
                //            decimal count = 0;
                //            for (int i = 0; i <dt3.Rows.Count; i++)
                //            {
                //                var num = dt3.AsEnumerable().Where(ex => ex.Field<string>("TIME") == dt.Rows[i]["时间"].ToString()).Select(ex => ex.Field<decimal>("COUNT")).FirstOrDefault();
                //                dr1["日期" + dt.Rows[i]["时间"].ToString()] = num;
                //                count += num;
                //            }
                //            dr1["日期合计"] =count.ToString();
                //            dt2.Rows.Add(dr1);
                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
                        '测试子表1' AS 测试列1,
                        '测试子表1' AS 测试列2,
                        '测试子表1' AS 测试列3,
                        '测试子表1' AS 测试列4,
                        '测试子表1' 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());
            }

            //子表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;
        }
    }

}