<%@ WebHandler Language="C#" Class="rpt" %> using System; using System.Web; using System.Web.SessionState; using System.Data; using System.Text; using System.Collections; using System.Collections.Generic; using Newtonsoft.Json; using Newtonsoft.Json.Linq; using Curtain.DataAccess; using DK.XuWei.WebMes; public class rpt : IHttpHandler, IReadOnlySessionState { public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/plain"; using (IDataAccess conn = DataAccess.Create()) { //主表 if (context.Request["m"].ToString() == "0") { DateTime dateBegin = Convert.ToDateTime(context.Request["datebeginMaster"]); DateTime dateEnd = Convert.ToDateTime(context.Request["dateendMaster"]); //查询开始日期至结束日期期间所有日期 fenglinyong add 2022-04-28 string dateStart = context.Request["datebeginMaster"].ToString().Substring(0, 10).Replace("-", ""); string dateFinish = context.Request["dateendMaster"].ToString().Substring(0, 10).Replace("-", ""); string totalMaster = context.Request["totalMaster"].ToString(); if (totalMaster == "YYYYMM") { dateStart = context.Request["datebeginMaster"].ToString().Substring(0, 8).Replace("-", ""); dateFinish = context.Request["dateendMaster"].ToString().Substring(0, 8).Replace("-", ""); } string groutinglineId = context.Request["groutinglineMaster[]"] is object ? context.Request["groutinglineMaster[]"].ToString() : "0"; if (groutinglineId == "0" && (context.Request["groutinglineMaster"] is object)) groutinglineId = context.Request["groutinglineMaster"].ToString(); if (groutinglineId == "") groutinglineId = "0"; string goodsId = context.Request["goodsMaster[]"] is object ? context.Request["goodsMaster[]"].ToString() : "0"; if (goodsId == "0" && (context.Request["goodsMaster"] is object)) goodsId = context.Request["goodsMaster"].ToString(); if (goodsId == "") goodsId = "0"; string highFlag = context.Request["highFlagMaster"]; if (highFlag == "") highFlag = "-1"; //商标id string logoId = context.Request["LOGONAME"] is object ? context.Request["LOGONAME"].ToString() : ""; logoId = logoId==""?string.Empty:"AND GDD.LOGOID=" + logoId; string inn=logoId==""?string.Empty:"INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID"; string procedureId = context.Request["procedureidMaster[]"] is object ? context.Request["procedureidMaster[]"].ToString() : "0"; if (procedureId == "0" && context.Request["procedureidMaster"] is object) procedureId = context.Request["procedureidMaster"].ToString(); if (procedureId == "") procedureId = "0"; string goodstypeCode = context.Request["goodstypecodeMaster"] is object ? context.Request["goodstypecodeMaster"].ToString() : ""; string flag = context.Request["workshopcodeMaster"].ToString(); string workshopcodeMaster = context.Request["workshopcodeMaster"] is object ? context.Request["workshopcodeMaster"].ToString() : ""; //读取报表数据 string sqlStr = @" WITH 注浆 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 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 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 注浆撤销数_合计 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 G.SCRAPSUMFLAG = '1' AND H.CREATETIME >= @DATEBEGIN@ AND H.CREATETIME < @DATEEND@ AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0') AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0') AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1') AND ( INSTR( GT.GOODSTYPECODE, '{GOODSTYPECODE}' ) = 1 OR '{GOODSTYPECODE}' IS NULL OR '{GOODSTYPECODE}' = '') {LOGOID} GROUP BY GROUPING SETS ( TO_CHAR( H.CREATETIME, 'YYYYMM' ), ( ) ) ), --1-1交坯 交坯 2#-交坯 1#-交坯(1,88,92,118) --包装 9-0包装(107,13) 工序产量 AS ( SELECT GROUPING_ID ( TO_CHAR( P.CREATETIME, 'YYYYMM' ) ) AS GID, NVL(TO_CHAR( P.CREATETIME, 'YYYYMM' ),'合计') AS 时间, SUM(CASE WHEN P.PROCEDUREID in (1,88,92,118) and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 交坯_一车间, SUM(CASE WHEN P.PROCEDUREID = 17 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 自改交坯_一车间, SUM(CASE WHEN P.PROCEDUREID = 55 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 毛坯入库_一车间, SUM(CASE WHEN P.PROCEDUREID = 56 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 毛坯出库_一车间, SUM(CASE WHEN P.PROCEDUREID = 2 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 改洗_一车间, SUM(CASE WHEN P.PROCEDUREID = 61 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 二检_一车间, SUM(CASE WHEN P.PROCEDUREID = 53 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 精坯入库_一车间, SUM(CASE WHEN P.PROCEDUREID = 54 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 精坯出库_一车间, SUM(CASE WHEN P.PROCEDUREID = 81 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 线上施釉交接_一车间, SUM(CASE WHEN P.PROCEDUREID = 62 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 吹风_一车间, SUM(CASE WHEN P.PROCEDUREID = 64 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 施釉交接_一车间, SUM(CASE WHEN P.PROCEDUREID = 3 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 线上施釉_一车间, SUM(CASE WHEN P.PROCEDUREID = 4 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 线下施釉_一车间, SUM(CASE WHEN P.PROCEDUREID = 65 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 刮登交接_一车间, SUM(CASE WHEN P.PROCEDUREID = 5 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 登窑_一车间, SUM(CASE WHEN P.PROCEDUREID = 9 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 入窑_一车间, SUM(CASE WHEN P.PROCEDUREID = 10 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 卸窑_一车间, SUM(CASE WHEN P.PROCEDUREID = 11 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 成检出窑交接_一车间, SUM(CASE WHEN P.PROCEDUREID = 57 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 重烧交接_一车间, SUM(CASE WHEN P.PROCEDUREID = 60 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 重烧补釉_一车间, SUM(CASE WHEN P.PROCEDUREID = 14 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 重烧登窑_一车间, SUM(CASE WHEN P.PROCEDUREID = 58 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 重烧出窑交接_一车间, SUM(CASE WHEN P.PROCEDUREID = 78 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 条码打印_一车间, SUM(CASE WHEN P.PROCEDUREID = 13 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 包装_一车间, SUM(CASE WHEN P.PROCEDUREID in (1,88,92,118) 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.PROCEDUREID = 2 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.PROCEDUREID = 61 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.PROCEDUREID = 53 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.PROCEDUREID = 54 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.PROCEDUREID = 62 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.PROCEDUREID = 64 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.PROCEDUREID = 3 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.PROCEDUREID = 65 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.PROCEDUREID = 5 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.PROCEDUREID = 9 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.PROCEDUREID = 10 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.PROCEDUREID = 11 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.PROCEDUREID = 57 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.PROCEDUREID = 60 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.PROCEDUREID = 14 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.PROCEDUREID = 58 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.PROCEDUREID = 78 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.PROCEDUREID = 13 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.PROCEDUREID in (1,88,92,118) 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(CASE WHEN P.PROCEDUREID = 91 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(CASE WHEN P.PROCEDUREID = 93 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(CASE WHEN P.PROCEDUREID = 94 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(CASE WHEN P.PROCEDUREID = 117 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(CASE WHEN P.PROCEDUREID = 97 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(CASE WHEN P.PROCEDUREID = 119 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(CASE WHEN P.PROCEDUREID = 115 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(CASE WHEN P.PROCEDUREID = 98 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(CASE WHEN P.PROCEDUREID = 99 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(CASE WHEN P.PROCEDUREID = 100 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(CASE WHEN P.PROCEDUREID = 101 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(CASE WHEN P.PROCEDUREID = 102 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(CASE WHEN P.PROCEDUREID = 103 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(CASE WHEN P.PROCEDUREID = 104 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(CASE WHEN P.PROCEDUREID = 106 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(CASE WHEN P.PROCEDUREID = 57 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(CASE WHEN P.PROCEDUREID = 60 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(CASE WHEN P.PROCEDUREID = 14 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(CASE WHEN P.PROCEDUREID = 58 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(CASE WHEN P.PROCEDUREID = 78 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(CASE WHEN P.PROCEDUREID = 107 and (INSTR( P.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( P.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( P.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 包装_三车间 FROM TP_PM_PRODUCTIONDATA P 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 {INN} WHERE P.CREATETIME >= @DATEBEGIN@ AND P.CREATETIME < @DATEEND@ AND (('{PROCEDUREID}' = '0' AND P.PROCEDUREID IN ( 1,2,3,4,5,9,10,11,13,14,17,53,54,55,56,57,58,60,61,62,64,65,78,81,88,91,92,93,94,97,98,99,100,101,102,103,104,106,107,115,117,118,119 )) OR P.PROCEDUREID in ({PROCEDUREID})) AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0') AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0') AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1') --AND ( P.PROCEDUREID in ({PROCEDUREID}) OR '{PROCEDUREID}' = '0' ) AND ( INSTR( GT.GOODSTYPECODE, '{GOODSTYPECODE}' ) = 1 OR '{GOODSTYPECODE}' IS NULL OR '{GOODSTYPECODE}' = '') {LOGOID} 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.PROCEDUREID in (1,88,92,118) and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 交坯撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 17 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 自改交坯撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 55 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 毛坯入库撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 56 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 毛坯出库撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 2 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 改洗撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 61 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 二检撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 53 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 精坯入库撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 54 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 精坯出库撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 81 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 线上施釉交接撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 62 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 吹风撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 64 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 施釉交接撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 3 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 线上施釉撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 4 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 线下施釉撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 65 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 刮登交接撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 5 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 登窑撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 9 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 入窑撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 10 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 卸窑撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 11 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 成检出窑交接撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 57 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 重烧交接撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 60 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 重烧补釉撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 14 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 重烧登窑撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 58 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 重烧出窑交接撤销_一车间, SUM(CASE WHEN P.PROCEDUREID = 78 and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 条码打印撤销_一车间, SUM(CASE WHEN P.PROCEDUREID in (107,13) and (INSTR( P.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1 ) THEN 1 ELSE 0 END ) AS 包装撤销_一车间, SUM(CASE WHEN P.PROCEDUREID in (1,88,92,118) 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.PROCEDUREID = 2 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.PROCEDUREID = 61 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.PROCEDUREID = 53 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.PROCEDUREID = 54 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.PROCEDUREID = 62 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.PROCEDUREID = 64 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.PROCEDUREID = 3 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.PROCEDUREID = 65 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.PROCEDUREID = 5 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.PROCEDUREID = 9 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.PROCEDUREID = 10 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.PROCEDUREID = 11 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.PROCEDUREID = 57 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.PROCEDUREID = 60 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.PROCEDUREID = 14 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.PROCEDUREID = 58 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.PROCEDUREID = 78 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.PROCEDUREID in (107,13) 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.PROCEDUREID in (1,88,92,118) 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(CASE WHEN P.PROCEDUREID = 91 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(CASE WHEN P.PROCEDUREID = 93 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(CASE WHEN P.PROCEDUREID = 94 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(CASE WHEN P.PROCEDUREID = 117 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(CASE WHEN P.PROCEDUREID = 97 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(CASE WHEN P.PROCEDUREID = 119 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(CASE WHEN P.PROCEDUREID = 115 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(CASE WHEN P.PROCEDUREID = 98 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(CASE WHEN P.PROCEDUREID = 99 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(CASE WHEN P.PROCEDUREID = 100 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(CASE WHEN P.PROCEDUREID = 101 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(CASE WHEN P.PROCEDUREID = 102 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(CASE WHEN P.PROCEDUREID = 103 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(CASE WHEN P.PROCEDUREID = 104 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(CASE WHEN P.PROCEDUREID = 106 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(CASE WHEN P.PROCEDUREID = 57 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(CASE WHEN P.PROCEDUREID = 60 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(CASE WHEN P.PROCEDUREID = 14 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(CASE WHEN P.PROCEDUREID = 58 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(CASE WHEN P.PROCEDUREID = 78 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(CASE WHEN P.PROCEDUREID in (107,13) and (INSTR( P.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( P.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( P.GROUTINGLINECODE, 'C06B' ) = 1) THEN 1 ELSE 0 END ) AS 包装撤销_三车间 FROM TP_PM_PRODUCTIONDATA P 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 {INN} WHERE P.BACKOUTTIME >= @DATEBEGIN@ AND P.BACKOUTTIME < @DATEEND@ AND (('{PROCEDUREID}' = '0' AND P.PROCEDUREID IN ( 1,2,3,4,5,9,10,11,13,14,17,53,54,55,56,57,58,60,61,62,64,65,78,81,88,91,92,93,94,97,98,99,100,101,102,103,104,106,107,115,117,118,119 )) OR P.PROCEDUREID in ({PROCEDUREID})) AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0') AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0') AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1') --AND ( P.PROCEDUREID in ({PROCEDUREID}) OR '{PROCEDUREID}' = '0' ) AND ( INSTR( GT.GOODSTYPECODE, '{GOODSTYPECODE}' ) = 1 OR '{GOODSTYPECODE}' IS NULL OR '{GOODSTYPECODE}' = '') {LOGOID} 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.PROCEDUREID = 99 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND PP.PROCEDUREID = 3 )> 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.PROCEDUREID = 100 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND PP.PROCEDUREID = 3 )> 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.PROCEDUREID = 101 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND PP.PROCEDUREID = 3 )> 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.PROCEDUREID = 102 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND PP.PROCEDUREID = 3 )> 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.PROCEDUREID = 103 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND PP.PROCEDUREID = 3 )> 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.PROCEDUREID = 104 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND PP.PROCEDUREID = 3 )> 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.PROCEDUREID = 106 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND PP.PROCEDUREID = 3 )> 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.PROCEDUREID = 58 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND PP.PROCEDUREID = 3 )> 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.PROCEDUREID = 107 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND PP.PROCEDUREID = 3 )> 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 包装_三车间 FROM TP_PM_PRODUCTIONDATA P 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 {INN} WHERE P.CREATETIME >= @DATEBEGIN@ AND P.CREATETIME < @DATEEND@ AND ('{PROCEDUREID}' = '0' AND P.PROCEDUREID IN ( 99,100,101,102,103,104,106,58 ) OR P.PROCEDUREID in ({PROCEDUREID})) AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0') AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0') AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1') --AND ( P.PROCEDUREID in ({PROCEDUREID}) OR '{PROCEDUREID}' = '0' ) AND ( INSTR( GT.GOODSTYPECODE, '{GOODSTYPECODE}' ) = 1 OR '{GOODSTYPECODE}' IS NULL OR '{GOODSTYPECODE}' = '') {LOGOID} 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.PROCEDUREID = 99 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND PP.PROCEDUREID = 3 )> 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.PROCEDUREID = 100 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND PP.PROCEDUREID = 3 )> 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.PROCEDUREID = 101 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND PP.PROCEDUREID = 3 )> 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.PROCEDUREID = 102 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND PP.PROCEDUREID = 3 )> 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.PROCEDUREID = 103 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND PP.PROCEDUREID = 3 )> 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.PROCEDUREID = 104 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND (PP.PROCEDUREID = 3 or PP.PROCEDUREID = 10) )> 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.PROCEDUREID = 106 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND (PP.PROCEDUREID = 3 or PP.PROCEDUREID = 10) )> 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.PROCEDUREID = 58 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND (PP.PROCEDUREID = 3 or PP.PROCEDUREID = 10) )> 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.PROCEDUREID = 107 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND (PP.PROCEDUREID = 3 or PP.PROCEDUREID = 10) )> 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 包装撤销_三车间 FROM TP_PM_PRODUCTIONDATA P 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 {INN} WHERE P.BACKOUTTIME >= @DATEBEGIN@ AND P.BACKOUTTIME < @DATEEND@ AND ('{PROCEDUREID}' = '0' AND P.PROCEDUREID IN ( 99,100,101,102,103,104,106,58 ) OR P.PROCEDUREID in ({PROCEDUREID})) AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0') AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0') AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1') --AND ( P.PROCEDUREID in ({PROCEDUREID}) OR '{PROCEDUREID}' = '0' ) AND ( INSTR( GT.GOODSTYPECODE, '{GOODSTYPECODE}' ) = 1 OR '{GOODSTYPECODE}' IS NULL OR '{GOODSTYPECODE}' = '') {LOGOID} 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.PROCEDUREID = 11 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND PP.PROCEDUREID = 103 )> 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(CASE WHEN P.PROCEDUREID = 13 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND PP.PROCEDUREID = 103 )> 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 包装_二车间 FROM TP_PM_PRODUCTIONDATA P 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 {INN} WHERE P.BACKOUTTIME >= @DATEBEGIN@ AND P.BACKOUTTIME < @DATEEND@ AND (('{PROCEDUREID}' = '0' AND P.PROCEDUREID in (11,13)) OR P.PROCEDUREID in ({PROCEDUREID})) AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0') AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0') AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1') --AND ( P.PROCEDUREID in ({PROCEDUREID}) OR '{PROCEDUREID}' = '0' ) AND ( INSTR( GT.GOODSTYPECODE, '{GOODSTYPECODE}' ) = 1 OR '{GOODSTYPECODE}' IS NULL OR '{GOODSTYPECODE}' = '') {LOGOID} 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.PROCEDUREID = 11 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND PP.PROCEDUREID = 103 )> 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(CASE WHEN P.PROCEDUREID = 13 AND (SELECT COUNT(1) FROM TP_PM_PRODUCTIONDATA PP WHERE PP.BARCODE = P.BARCODE AND PP.PROCEDUREID = 103 )> 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 包装撤销_二车间 FROM TP_PM_PRODUCTIONDATA P 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 {INN} WHERE P.BACKOUTTIME >= @DATEBEGIN@ AND P.BACKOUTTIME < @DATEEND@ AND (('{PROCEDUREID}' = '0' AND P.PROCEDUREID in (11,13)) OR P.PROCEDUREID in ({PROCEDUREID})) AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0') AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0') AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1') --AND ( P.PROCEDUREID in ({PROCEDUREID}) OR '{PROCEDUREID}' = '0' ) AND ( INSTR( GT.GOODSTYPECODE, '{GOODSTYPECODE}' ) = 1 OR '{GOODSTYPECODE}' IS NULL OR '{GOODSTYPECODE}' = '') {LOGOID} GROUP BY GROUPING SETS ( TO_CHAR( P.CREATETIME, 'YYYYMM' ), ( ) ) ) SELECT 时间范围.时间, 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) - 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) + 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) + 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) + nvl(二车间转三车间.刮登_三车间,0) - nvl(二车间转三车间撤销.刮登撤销_三车间,0) as 刮登_三车间, nvl(工序产量.釉坯库入_三车间,0) - nvl(工序产量撤销.釉坯库入撤销_三车间,0) + nvl(二车间转三车间.釉坯库入_三车间,0) - nvl(二车间转三车间撤销.釉坯库入撤销_三车间,0) as 釉坯库入_三车间, nvl(工序产量.登窑_三车间,0) - nvl(工序产量撤销.登窑撤销_三车间,0) + nvl(二车间转三车间.登窑_三车间,0) - nvl(二车间转三车间撤销.登窑撤销_三车间,0) as 登窑_三车间, nvl(工序产量.入窑_三车间,0) - nvl(工序产量撤销.入窑撤销_三车间,0) + nvl(二车间转三车间.入窑_三车间,0) - nvl(二车间转三车间撤销.入窑撤销_三车间,0) as 入窑_三车间, nvl(工序产量.卸窑_三车间,0) - nvl(工序产量撤销.卸窑撤销_三车间,0) + nvl(二车间转三车间.卸窑_三车间,0) - nvl(二车间转三车间撤销.卸窑撤销_三车间,0) as 卸窑_三车间, nvl(工序产量.成检交接_三车间,0) - nvl(工序产量撤销.成检交接撤销_三车间,0) + nvl(二车间转三车间.成检交接_三车间,0) - nvl(二车间转三车间撤销.成检交接撤销_三车间,0) as 成检交接_三车间, nvl(工序产量.成瓷库_三车间,0) - nvl(工序产量撤销.成瓷库撤销_三车间,0) + 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) + nvl(二车间转三车间.包装_三车间,0) - nvl(二车间转三车间撤销.包装撤销_三车间,0) as 包装_三车间 FROM (SELECT TO_CHAR( TO_DATE( @DATEFINISH@, 'yyyymmdd' ) + 1 - LEVEL, 'yyyymmdd' ) 时间 FROM DUAL CONNECT BY LEVEL <= TO_NUMBER( TO_DATE( @DATEFINISH@, 'yyyymmdd' ) - TO_DATE( @DATESTART@, 'yyyymmdd' ) ) + 1) 时间范围 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("yyyymmdd", totalMaster) .Replace("{GOODSID}", goodsId) .Replace("{GROUTINGLINEID}", groutinglineId) .Replace("{PROCEDUREID}", procedureId) .Replace("{GOODSTYPECODE}", goodstypeCode) .Replace("{LOGOID}", logoId).Replace("{INN}", inn); //获取查询条件 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("DATEFINISH", dateFinish)); sqlPara.Add(new CDAParameter("DATESTART", dateStart)); //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("工序"); int sumRow = 0; List sumRows = new List(); 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++) { if (workshopcodeMaster != "" && workshopcodeMaster.Length > 0 && dt.Columns[j].ColumnName.Substring(dt.Columns[j].ColumnName.IndexOf("_") + 1) != workshopcodeMaster) { dt.Columns.Remove(dt.Columns[j].ColumnName); } else { if (dt.Columns[j].ColumnName.Equals("交坯_三车间")) { dt.Columns[j].ColumnName = "3#交坯_三车间"; } else if (dt.Columns[j].ColumnName.Equals("毛坯库入_三车间")) { dt.Columns[j].ColumnName = "3#毛坯库（入）_三车间"; } else if (dt.Columns[j].ColumnName.Equals("打磨_三车间")) { dt.Columns[j].ColumnName = "3#打磨_三车间"; } else if (dt.Columns[j].ColumnName.Equals("上水_三车间")) { dt.Columns[j].ColumnName = "3#上水_三车间"; } else if (dt.Columns[j].ColumnName.Equals("二检_三车间")) { dt.Columns[j].ColumnName = "3#二检_三车间"; } else if (dt.Columns[j].ColumnName.Equals("精坯库出_三车间")) { dt.Columns[j].ColumnName = "3#精坯库（出）_三车间"; } else if (dt.Columns[j].ColumnName.Equals("精坯库入_三车间")) { dt.Columns[j].ColumnName = "3#精坯库（入）_三车间"; } else if (dt.Columns[j].ColumnName.Equals("吹风_三车间")) { dt.Columns[j].ColumnName = "3#吹风_三车间"; } else if (dt.Columns[j].ColumnName.Equals("施釉_三车间")) { dt.Columns[j].ColumnName = "3#施釉_三车间"; } else if (dt.Columns[j].ColumnName.Equals("刮登_三车间")) { dt.Columns[j].ColumnName = "3#刮登_三车间"; } else if (dt.Columns[j].ColumnName.Equals("釉坯库入_三车间")) { dt.Columns[j].ColumnName = "3#釉坯库（入）_三车间"; } else if (dt.Columns[j].ColumnName.Equals("登窑_三车间")) { dt.Columns[j].ColumnName = "3#登窑_三车间"; } else if (dt.Columns[j].ColumnName.Equals("入窑_三车间")) { dt.Columns[j].ColumnName = "3#入窑_三车间"; } else if (dt.Columns[j].ColumnName.Equals("卸窑_三车间")) { dt.Columns[j].ColumnName = "3#卸窑_三车间"; } else if (dt.Columns[j].ColumnName.Equals("成检交接_三车间")) { dt.Columns[j].ColumnName = "3#成检交接_三车间"; } else if (dt.Columns[j].ColumnName.Equals("成瓷库_三车间")) { dt.Columns[j].ColumnName = "3#成瓷库_三车间"; } DataRow dr = dt2.NewRow(); dr["部门序号"] = dt.Columns[j].ColumnName.Substring(dt.Columns[j].ColumnName.IndexOf("_") + 1) .Replace("一车间", "1").Replace("二车间", "2").Replace("三车间", "3"); dr["部门"] = dt.Columns[j].ColumnName.Substring(dt.Columns[j].ColumnName.IndexOf("_") + 1); dr["工序"] = dt.Columns[j].ColumnName.Substring(0, dt.Columns[j].ColumnName.IndexOf("_")); for (int i = 0; i < dt.Rows.Count; i++) { if (!procedureId.Equals("0") && (dr["工序"].Equals("注浆撤销数") || dr["工序"].Equals("注浆数") || dr["工序"].Equals("成坯数"))) { dr["日期" + dt.Rows[i]["时间"].ToString()] = 0; } else { dr["日期" + dt.Rows[i]["时间"].ToString()] = dt.Rows[i][dt.Columns[j].ColumnName]; } } if (procedureId.Equals("0")) { dt2.Rows.Add(dr); } else { if (("," + procedureId + ",").Contains(",1,")) { if (dr["工序"].Equals("交坯")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",99998,")) { if (dr["工序"].Equals("注浆数")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",99999,")) { if (dr["工序"].Equals("注浆撤销数")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",100000,")) { if (dr["工序"].Equals("成坯数") && dr["部门"].Equals("一车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",99999,")) { if (dr["工序"].Equals("注浆撤销")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",118,")) { if (dr["工序"].Equals("交坯") && dr["部门"].Equals("一车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",17,")) { if (dr["工序"].Equals("自改交坯") && dr["部门"].Equals("一车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",55,")) { if (dr["工序"].Equals("毛坯入库") && dr["部门"].Equals("一车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",56,")) { if (dr["工序"].Equals("毛坯出库") && dr["部门"].Equals("一车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",2,")) { if (dr["工序"].Equals("改洗")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",61,")) { if (dr["工序"].Equals("二检")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",53,")) { if (dr["工序"].Equals("精坯入库")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",54,")) { if (dr["工序"].Equals("精坯出库")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",81,")) { if (dr["工序"].Equals("线上施釉交接") && dr["部门"].Equals("一车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",62,")) { if (dr["工序"].Equals("吹风")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",64,")) { if (dr["工序"].Equals("施釉交接")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",3,")) { if (dr["工序"].Equals("线上施釉")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",4,")) { if (dr["工序"].Equals("线下施釉") && dr["部门"].Equals("一车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",65,")) { if (dr["工序"].Equals("刮登交接")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",5,")) { if (dr["工序"].Equals("登窑")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",9,")) { if (dr["工序"].Equals("入窑")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",10,")) { if (dr["工序"].Equals("卸窑")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",11,")) { if (dr["工序"].Equals("成检出窑交接")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",57,")) { if (dr["工序"].Equals("重烧交接")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",60,")) { if (dr["工序"].Equals("重烧补釉")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",14,")) { if (dr["工序"].Equals("重烧登窑")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",58,")) { if (dr["工序"].Equals("重烧出窑交接")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",78,")) { if (dr["工序"].Equals("条码打印")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",13,")) { if (dr["工序"].Equals("包装")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",92,")) { if (dr["工序"].Equals("交坯") && dr["部门"].Equals("二车间")) { dt2.Rows.Add(dr); } } //if (("," + procedureId + ",").Contains(",99998,")) //{ // if (dr["工序"].Equals("注浆数") && dr["部门"].Equals("二车间")) // { // dt2.Rows.Add(dr); // } //} //if (("," + procedureId + ",").Contains(",99999,")) //{ // if (dr["工序"].Equals("注浆撤销数") && dr["部门"].Equals("二车间")) // { // dt2.Rows.Add(dr); // } //} if (("," + procedureId + ",").Contains(",100000,")) { if (dr["工序"].Equals("成坯数") && dr["部门"].Equals("二车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",91,")) { if (dr["工序"].Equals("3#毛坯库（入）") && dr["部门"].Equals("三车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",93,")) { if (dr["工序"].Equals("3#打磨") && dr["部门"].Equals("三车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",94,")) { if (dr["工序"].Equals("3#上水") && dr["部门"].Equals("三车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",117,")) { if (dr["工序"].Equals("3#二检") && dr["部门"].Equals("三车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",97,")) { if (dr["工序"].Equals("3#精坯库（入") && dr["部门"].Equals("三车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",119,")) { if (dr["工序"].Equals("3#精坯库（出）") && dr["部门"].Equals("三车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",115,")) { if (dr["工序"].Equals("3#吹风") && dr["部门"].Equals("三车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",98,")) { if (dr["工序"].Equals("3#施釉") && dr["部门"].Equals("三车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",99,")) { if (dr["工序"].Equals("3#刮登") && dr["部门"].Equals("三车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",100,")) { if (dr["工序"].Equals("3#釉坯库（入）") && dr["部门"].Equals("三车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",101,")) { if (dr["工序"].Equals("3#登窑") && dr["部门"].Equals("三车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",102,")) { if (dr["工序"].Equals("3#入窑") && dr["部门"].Equals("三车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",103,")) { if (dr["工序"].Equals("3#卸窑") && dr["部门"].Equals("三车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",104,")) { if (dr["工序"].Equals("3#成检交接") && dr["部门"].Equals("三车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",106,")) { if (dr["工序"].Equals("3#成瓷库") && dr["部门"].Equals("三车间")) { dt2.Rows.Add(dr); } } if (("," + procedureId + ",").Contains(",107,")) { if (dr["工序"].Equals("包装") && dr["部门"].Equals("三车间")) { dt2.Rows.Add(dr); } } //if (("," + procedureId + ",").Contains(",99998,")) //{ // if (dr["工序"].Equals("注浆数") && dr["部门"].Equals("三车间")) // { // dt2.Rows.Add(dr); // } //} //if (("," + procedureId + ",").Contains(",99999,")) //{ // if (dr["工序"].Equals("注浆撤销数") && dr["部门"].Equals("三车间")) // { // dt2.Rows.Add(dr); // } //} if (("," + procedureId + ",").Contains(",100000,")) { if (dr["工序"].Equals("成坯数") && dr["部门"].Equals("三车间")) { dt2.Rows.Add(dr); } } } } } DataView dv = dt2.DefaultView; dv.Sort = "部门序号"; dt2.Columns.Remove("部门序号"); dt2 = dv.ToTable(); //生产车间产量报表添加合计行，fenglinyong add 2022-04-26 //循环处理：获取表格每个单元格的值。 for (int i = 2; i < dt2.Columns.Count; i++) { for (int j = 0; j < dt2.Rows.Count; j++) { //计算当前行 从J列开始相加的值 sumRow += Convert.ToInt32(dt2.Rows[j].ItemArray[i]); } //放入容器中 sumRows.Add(sumRow); //合计清零 sumRow = 0; } //新建行 DataRow drNew = dt2.NewRow(); drNew[0] = "合计"; drNew[1] = ""; //循环添加合计列的值 for (int i = 0; i < sumRows.Count; i++) { drNew[i + 2] = sumRows[i]; } dt2.Rows.Add(drNew); //生产车间产量报表添加合计行，fenglinyong add 2022-04-26 context.Response.Write(new JsonResult(dt2) { total = dt.Rows.Count }.ToJson()); //=============================================================================== //JsonResult data = Easyui.ExecuteJsonResult(conn, sqlStr, sqlPara); //context.Response.Write(data.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; } } }