<%@ 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' ), ( ) ) ) 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 入库数_三部全部 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 拼接日期.时间 = 次品未判缺陷数.时间 ORDER BY 拼接日期.时间 ".Replace("YYYYMM",totalMaster) .Replace("{GOODSID}",goodsId) .Replace("{GROUTINGLINEID}",groutinglineId) .Replace("{DATESTR}",datestr); //获取查询条件 List sqlPara = new List(); 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 ex.Field("TIME") == dt.Rows[i]["时间"].ToString()).Select(ex => ex.Field("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 sqlPara = new List(); //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 sqlPara = new List(); //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; } } }