using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Configuration; using Curtain.DataAccess; using Curtain.Log; using System.Data; using System.Drawing; using Newtonsoft.Json.Linq; using Newtonsoft.Json; ///

/// Export 的摘要说明 ///

public static class Export { public static Boolean getRunningFlag() { using (IDataAccess conn = DataAccess.Create()) { String sqlStr = @" SELECT SETTINGVALUE FROM TP_MST_SYSTEMSETTING WHERE SETTINGCODE = 'S_PM_025' "; object obj = conn.ExecuteScalar(sqlStr); if (obj + "" == "0" ) { return true; } else { return false; } } } public static void setRunning() { using (IDataAccess conn = DataAccess.Create()) { String sqlStr = @" UPDATE TP_MST_SYSTEMSETTING SET SETTINGVALUE = '1' WHERE SETTINGCODE = 'S_PM_025' "; int num = conn.ExecuteNonQuery(sqlStr); if (num > 0) { Curtain.Log.Logger.Debug("设置结转运行中！"); } } } public static void setSleeping() { using (IDataAccess conn = DataAccess.Create()) { String sqlStr = @" UPDATE TP_MST_SYSTEMSETTING SET SETTINGVALUE = '0' WHERE SETTINGCODE = 'S_PM_025' "; int num = conn.ExecuteNonQuery(sqlStr); if (num > 0) { Curtain.Log.Logger.Debug("设置结转等待中！"); } } } #region 结转整理 public static void export001() { using (IDataAccess conn = DataAccess.Create()) { try { Curtain.Log.Logger.Debug("export001注浆产品存坯汇总表结转开始！"); conn.Open(); conn.BeginTransaction(); //2021-07-12 dongyan 修正什么问题 int result = 0; int result1 = 0; int m = DateTime.Now.AddMonths(-1).Month; string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); String sqlStr = @"delete AJZ_RPT001_M WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1).AddMilliseconds(-1); String sqlStr1 = @" INSERT INTO AJZ_RPT001_M SELECT * FROM ( SELECT T.GOODSID AS 产品ID, DECODE(GROUPING_ID ( T.GOODSTYPECODE2, T.GOODSID ),3,'总计',1,'合计[' || T.GOODSTYPENAME2 || ']',T.GOODSTYPENAME2 ) AS 产品大类, T.GOODSTYPENAME AS 产品类别, T.GOODSCODE AS 产品编码, '0' AS 工号, SUM( T.RDCOUNT ) AS 存坯数, T.GLDCOUNT AS 模具数, T.RDDAYS AS 存坯天数, T.RESERVEDDAYS AS 应存天数, SUM( T.PROFITNUM ) AS 盈亏坯数 , '0' AS 产品小类, to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM ( SELECT GT2.GOODSTYPECODE GOODSTYPECODE2, GT2.GOODSTYPENAME GOODSTYPENAME2, GDRD.GOODSID, GDRD.GOODSCODE, GDRD.GOODSTYPENAME, GDRD.GDDC RDCOUNT, -- 存坯数 RDD.GLDCOUNT, -- 模具数 CASE WHEN RDD.GLDCOUNT IS NOT NULL AND RDD.GLDCOUNT > 0 THEN ROUND( GDRD.GDDC / RDD.GLDCOUNT, 2 ) ELSE NULL END RDDAYS, -- 存坯天数 GDRD.RESERVEDDAYS, -- 应存天数 ( GDRD.GDDC - GDRD.RESERVEDDAYS * RDD.GLDCOUNT ) PROFITNUM --盈亏坯数 FROM ( SELECT GDD.GOODSID, GDD.GOODSCODE, G.RESERVEDDAYS, SUBSTR( GT.GOODSTYPECODE, 1, 6 ) GOODSTYPECODE2, GT.GOODSTYPENAME, COUNT( * ) GDDC FROM TP_PM_GROUTINGDAILYDETAIL GDD INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID WHERE GDD.ACCOUNTID = 1 AND GDD.VALUEFLAG = '1' AND GDD.GROUTINGFLAG = '1' AND GDD.BARCODE IS NOT NULL AND GDD.SCRAPFLAG = '0' AND GDD.DELIVERFLAG = '0' --查询条件 -- AND (GDD.GROUTINGDATE BETWEEN to_date(@DATEBEGIN@,'yyyy-mm-dd') AND to_date(@DATEEND@,'yyyy-mm-dd')) AND GDD.GROUTINGDATE >= @DATEBEGIN@ AND GDD.GROUTINGDATE <= @DATEEND@ GROUP BY GT.GOODSTYPECODE, GT.GOODSTYPENAME, GDD.GOODSID, GDD.GOODSCODE, G.RESERVEDDAYS ) GDRD LEFT JOIN ( SELECT GLD.GOODSID, COUNT( * ) GLDCOUNT FROM TP_PC_GROUTINGLINEDETAIL GLD INNER JOIN TP_PC_GROUTINGLINE GL ON GL.GROUTINGLINEID = GLD.GROUTINGLINEID WHERE GLD.VALUEFLAG = '1' AND GLD.GMOULDSTATUS = 1 AND GL.VALUEFLAG = '1' AND GL.GMOULDSTATUS = 1 GROUP BY GLD.GOODSID ) RDD ON RDD.GOODSID = GDRD.GOODSID INNER JOIN TP_MST_GOODSTYPE GT2 ON GT2.ACCOUNTID = 1 AND GT2.GOODSTYPECODE = GDRD.GOODSTYPECODE2 ORDER BY GT2.GOODSTYPECODE, GDRD.GOODSCODE ) T GROUP BY GROUPING SETS ( (T.GOODSTYPECODE2,T.GOODSTYPENAME2,T.GOODSTYPENAME,T.GOODSID,T.GOODSCODE,T.GLDCOUNT,T.RDDAYS,T.RESERVEDDAYS), (T.GOODSTYPECODE2, T.GOODSTYPENAME2), ( ) ) ) D WHERE D.产品编码 IS NOT NULL "; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result = conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); String Str = @"delete AJZ_RPT001_D WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(Str, new CDAParameter("C", c)); String sqlStr2 = @" INSERT INTO AJZ_RPT001_D SELECT GDD.BARCODE AS 产品条码, GDD.GROUTINGLINECODE AS 成型线号, GDD.GROUTINGMOULDCODE AS 模具编号, GDD.GOODSCODE AS 产品编码, GDD.USERCODE AS 成型工号, TO_CHAR(GDD.GROUTINGDATE,'yyyy-mm-dd') AS 注浆日期, to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM TP_PM_GROUTINGDAILYDETAIL GDD INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID WHERE GDD.VALUEFLAG = '1' AND GDD.GROUTINGFLAG = '1' AND GDD.BARCODE IS NOT NULL AND GDD.SCRAPFLAG = '0' AND GDD.DELIVERFLAG = '0' AND GDD.GROUTINGDATE >= @DATEBEGIN@ AND GDD.GROUTINGDATE <= @DATEEND@ "; List sqlPara1 = new List(); sqlPara1.Add(new CDAParameter("I", i)); sqlPara1.Add(new CDAParameter("YEAR", b)); sqlPara1.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara1.Add(new CDAParameter("DATEBEGIN", dayBegin)); result1 = conn.ExecuteNonQuery(sqlStr2, sqlPara1.ToArray()); } conn.Commit(); if (result > 0 && result1 > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export001注浆产品存坯汇总表结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export001注浆产品存坯汇总表结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export001注浆产品存坯汇总表结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export002() { using (IDataAccess conn = DataAccess.Create()) { try { Curtain.Log.Logger.Debug("export002成型工号存坯汇总表结转开始！"); conn.Open(); conn.BeginTransaction(); //2021-07-12 dongyan 修正什么问题 int result = 0; int result1 = 0; int m = DateTime.Now.AddMonths(-1).Month; string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); String sqlStr = @"delete AJZ_RPT002_M WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); String Str = @"delete AJZ_RPT002_D WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(Str, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1).AddMilliseconds(-1); String sqlStr1 = @" INSERT INTO AJZ_RPT002_M SELECT * FROM (SELECT --GROUPING_ID ( T.GOODSTYPECODE2, T.GOODSID ) GID, --T.GOODSTYPECODE2, T.GOODSID AS 产品ID, DECODE(GROUPING_ID ( T.GOODSTYPECODE2, T.GOODSID ),3,'总计',1,'合计[' || T.GOODSTYPENAME2 || ']',T.GOODSTYPENAME2 ) AS 产品类别, T.GOODSCODE AS 产品编码, --T.GROUTINGLINEID AS 成型线ID, T.GROUTINGLINECODE AS 成型线号, --T.USERID AS 用户ID, T.USERCODE AS 成型工号, SUM( T.RDCOUNT ) AS 存坯数, T.GLDCOUNT AS 模具数, T.RDDAYS AS 存坯天数, T.RESERVEDDAYS AS 应存天数, SUM( T.PROFITNUM ) AS 盈亏坯数 , '0' as GOODSTYPECODE, to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM ( SELECT GT2.GOODSTYPECODE GOODSTYPECODE2, GT2.GOODSTYPENAME GOODSTYPENAME2, GDRD.GOODSID, GDRD.GOODSCODE, GDRD.GROUTINGLINEID, GDRD.GROUTINGLINECODE, GDRD.USERID, GDRD.USERCODE, GDRD.GDDC RDCOUNT, -- 存坯数 RDD.GLDCOUNT, -- 模具数 CASE WHEN RDD.GLDCOUNT IS NOT NULL AND RDD.GLDCOUNT > 0 THEN ROUND( GDRD.GDDC / RDD.GLDCOUNT, 2 ) ELSE NULL END RDDAYS, -- 存坯天数 GDRD.RESERVEDDAYS, -- 应存天数 ( GDRD.GDDC - GDRD.RESERVEDDAYS * RDD.GLDCOUNT ) PROFITNUM --盈亏坯数 FROM ( SELECT GDD.GOODSID, GDD.GOODSCODE, GDD.GROUTINGLINEID, GDD.GROUTINGLINECODE, GDD.USERID, GDD.USERCODE, G.RESERVEDDAYS, SUBSTR( GT.GOODSTYPECODE, 1, 6 ) GOODSTYPECODE2, COUNT( * ) GDDC FROM TP_PM_GROUTINGDAILYDETAIL GDD INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID WHERE GDD.VALUEFLAG = '1' AND GDD.GROUTINGFLAG = '1' AND GDD.BARCODE IS NOT NULL AND GDD.SCRAPFLAG = '0' AND GDD.DELIVERFLAG = '0' -- AND (@DATEBEGIN@ IS NULL OR GDD.GROUTINGDATE BETWEEN to_date(@DATEBEGIN@,'yyyy-mm-dd') AND to_date(@DATEEND@,'yyyy-mm-dd')) AND GDD.GROUTINGDATE >= @DATEBEGIN@ AND GDD.GROUTINGDATE <= @DATEEND@ GROUP BY GT.GOODSTYPECODE, GDD.GOODSID, GDD.GOODSCODE, GDD.GROUTINGLINEID, GDD.GROUTINGLINECODE, GDD.USERID, GDD.USERCODE, G.RESERVEDDAYS ) GDRD LEFT JOIN ( SELECT GLD.GOODSID, GLD.GROUTINGLINEID, COUNT( * ) GLDCOUNT FROM TP_PC_GROUTINGLINEDETAIL GLD INNER JOIN TP_PC_GROUTINGLINE GL ON GL.GROUTINGLINEID = GLD.GROUTINGLINEID WHERE GLD.VALUEFLAG = '1' AND GLD.GMOULDSTATUS = 1 AND GL.ACCOUNTID = 1 AND GL.VALUEFLAG = '1' AND GL.GMOULDSTATUS = 1 GROUP BY GLD.GOODSID, GLD.GROUTINGLINEID ) RDD ON RDD.GOODSID = GDRD.GOODSID AND RDD.GROUTINGLINEID = GDRD.GROUTINGLINEID INNER JOIN TP_MST_GOODSTYPE GT2 ON GT2.ACCOUNTID = 1 AND GT2.GOODSTYPECODE = GDRD.GOODSTYPECODE2 ORDER BY GT2.GOODSTYPECODE, GDRD.GOODSCODE, GDRD.GROUTINGLINECODE, GDRD.USERCODE ) T GROUP BY GROUPING SETS ( (T.GOODSTYPECODE2,T.GOODSTYPENAME2,T.GOODSID,T.GOODSCODE,T.GROUTINGLINEID,T.GROUTINGLINECODE,T.USERID,T.USERCODE,T.GLDCOUNT,T.RDDAYS,T.RESERVEDDAYS), (T.GOODSTYPECODE2, T.GOODSTYPENAME2), ( ) ) )D WHERE D.产品编码 IS not null "; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result = conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); String sqlStr2 = @"INSERT INTO AJZ_RPT002_D SELECT GDD.BARCODE AS 产品条码, GDD.GROUTINGLINECODE AS 成型线号, GDD.GROUTINGMOULDCODE AS 模具编号, GDD.GOODSCODE AS 产品编码, GDD.USERCODE AS 成型工号, TO_CHAR(GDD.GROUTINGDATE,'yyyy-mm-dd') AS 注浆日期, to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM TP_PM_GROUTINGDAILYDETAIL GDD INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID WHERE GDD.VALUEFLAG = '1' AND GDD.GROUTINGFLAG = '1' AND GDD.BARCODE IS NOT NULL AND GDD.SCRAPFLAG = '0' AND GDD.DELIVERFLAG = '0' -- AND (@DATEBEGIN@ IS NULL OR GDD.GROUTINGDATE BETWEEN @DATEBEGIN@ AND @DATEEND@) AND GDD.GROUTINGDATE >= @DATEBEGIN@ AND GDD.GROUTINGDATE <= @DATEEND@ "; List sqlPara1 = new List(); sqlPara1.Add(new CDAParameter("I", i)); sqlPara1.Add(new CDAParameter("YEAR", b)); sqlPara1.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara1.Add(new CDAParameter("DATEBEGIN", dayBegin)); result1 = conn.ExecuteNonQuery(sqlStr2, sqlPara1.ToArray()); } conn.Commit(); if (result > 0 && result1 > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export002成型工号存坯汇总表结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export002成型工号存坯汇总表结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export002成型工号存坯汇总表结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export006() { using (IDataAccess conn = DataAccess.Create()) { //2021-07-12 dongyan 修正什么问题 Curtain.Log.Logger.Debug("export006员工出勤汇总表结转开始！"); int result = 0; int result1 = 0; int m = DateTime.Now.AddMonths(-1).Month; DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); conn.Open(); conn.BeginTransaction(); try { String sqlStr = @"delete AJZ_RPT006_M WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); String Str = @"delete AJZ_RPT006_D WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(Str, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1); String sqlStr1 = @" insert into AJZ_RPT006_M SELECT U.USERID, F.STAFFID, O.ORGANIZATIONNAME AS 部门, U.USERCODE AS 工号, F.STAFFCODE AS 员工编码, F.STAFFNAME AS 员工姓名, CQ.CQTS AS 出勤天数, ZCL.ZCLS AS 总产量, HGL.CYS AS 出窑数, HGL.BHGL AS 不合格率 , O.ORGANIZATIONCODE, to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')), 0 FROM ( -- 统计出勤 SELECT T.STAFFID, T.USERID, COUNT( * ) AS CQTS FROM ( SELECT DISTINCT CD.USERID, CD.STAFFID, C.ACCOUNTDATE FROM TP_PC_CLASSESDETAIL CD INNER JOIN TP_PC_CLASSESSETTING C ON C.CLASSESSETTINGID = CD.CLASSESSETTINGID INNER JOIN TP_MST_USER U ON U.USERID = CD.USERID INNER JOIN TP_MST_ORGANIZATION ORG ON ORG.ORGANIZATIONID = U.ORGANIZATIONID WHERE C.ACCOUNTDATE >= @DATEBEGIN@ AND C.ACCOUNTDATE < @DATEEND@ ) T GROUP BY T.USERID, T.STAFFID ) CQ LEFT JOIN ( --总产量 SELECT CD.STAFFID, CD.USERID, COUNT( * ) AS ZCLS FROM TP_PM_PRODUCTIONDATA PD INNER JOIN TP_PC_CLASSESDETAIL CD ON CD.CLASSESSETTINGID = PD.CLASSESSETTINGID WHERE PD.VALUEFLAG = '1' AND PD.CREATETIME >= @DATEBEGIN@ AND PD.CREATETIME < @DATEEND@ GROUP BY CD.USERID, CD.STAFFID ) ZCL ON CQ.USERID = ZCL.USERID AND CQ.STAFFID = ZCL.STAFFID FULL JOIN ( --不合格率 SELECT P.STAFFID, P.USERID, COUNT( * ) AS CYS, ROUND( SUM( DECODE( P.GOODSLEVELTYPEID, 6, 1, 7, 1, 0 ) ) / COUNT( * ), 4 ) AS BHGL FROM ( SELECT DISTINCT CD.STAFFID, CD.USERID, T.GOODSLEVELTYPEID, T.BARCODE FROM ( SELECT BARCODE, GOODSLEVELTYPEID FROM TP_PM_PRODUCTIONDATA WHERE PROCEDUREID = 13 AND VALUEFLAG = '1' AND CHECKBATCHNO = 1 AND CHECKTIME >= @DATEBEGIN@ AND CHECKTIME < @DATEEND@ ) T INNER JOIN TP_PM_PRODUCTIONDATA PD ON T.BARCODE = PD.BARCODE INNER JOIN TP_PC_CLASSESDETAIL CD ON CD.CLASSESSETTINGID = PD.CLASSESSETTINGID INNER JOIN TP_MST_USER U ON U.USERID = CD.USERID INNER JOIN TP_MST_ORGANIZATION ORG ON ORG.ORGANIZATIONID = U.ORGANIZATIONID WHERE PD.KILNCARBATCHNO IS NULL AND PD.VALUEFLAG = '1' ) P GROUP BY P.STAFFID, P.USERID ) HGL ON HGL.USERID = CQ.USERID AND HGL.STAFFID = CQ.STAFFID INNER JOIN TP_HR_STAFF F ON F.STAFFID = NVL( CQ.STAFFID, HGL.STAFFID ) INNER JOIN TP_MST_USER U ON U.USERID = NVL( CQ.USERID, HGL.USERID ) INNER JOIN TP_MST_ORGANIZATION O ON U.ORGANIZATIONID = O.ORGANIZATIONID "; List sqlPara1 = new List(); sqlPara1.Add(new CDAParameter("I", i)); sqlPara1.Add(new CDAParameter("YEAR", b)); sqlPara1.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara1.Add(new CDAParameter("DATEBEGIN", dayBegin)); result += conn.ExecuteNonQuery(sqlStr1, sqlPara1.ToArray()); String sqlStr2 = @"INSERT INTO AJZ_RPT006_D select * from ( SELECT --TO_CHAR(CQ.日期,'yyyy-mm-dd') as 日期, O.ORGANIZATIONNAME as 部门, U.USERCODE 工号, F.STAFFCODE 员工编码, F.STAFFNAME 员工姓名, ZCL.产品, ZCL.产量, ZCL.产量出窑数, ZCL.产量合格数, '0' as STAFFID, '0', to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM /*( -- 统计出勤 SELECT DISTINCT S.USERID AS 用户ID, CD.STAFFID AS 员工ID --,TRUNC(S.ACCOUNTDATE) AS 日期 FROM TP_PC_CLASSESSETTING S INNER JOIN TP_PC_CLASSESDETAIL CD ON CD.CLASSESSETTINGID = S.CLASSESSETTINGID WHERE S.ACCOUNTDATE >= @DATEBEGIN@ AND S.ACCOUNTDATE < @DATEEND@ ) CQ LEFT JOIN */ ( --产量 SELECT --DISTINCT --TRUNC(PD.CREATETIME) AS 日期, CD.STAFFID AS 员工ID, CD.USERID AS 用户ID, PD.GOODSCODE AS 产品, COUNT(DISTINCT PD.PRODUCTIONDATAID) AS 产量, SUM(DECODE(OUTK.PRODUCTIONDATAID, NULL ,0 ,1)) 产量出窑数, SUM(DECODE(OUTK.GOODSLEVELTYPEID, 4 ,1 ,5, 1, 0)) 产量合格数 FROM TP_PM_PRODUCTIONDATA PD INNER JOIN TP_PC_CLASSESDETAIL CD ON CD.CLASSESSETTINGID = PD.CLASSESSETTINGID --INNER JOIN TP_PM_PRODUCTIONDATA PD ON PD.CLASSESSETTINGID = S.CLASSESSETTINGID LEFT JOIN TP_PM_PRODUCTIONDATA OUTK ON OUTK.GROUTINGDAILYDETAILID = PD.GROUTINGDAILYDETAILID AND OUTK.VALUEFLAG = '1' AND OUTK.PROCEDUREID = 13 AND OUTK.ISREFIRE = '0' WHERE PD.VALUEFLAG = '1' AND PD.CREATETIME >= @DATEBEGIN@ AND PD.CREATETIME < @DATEEND@ GROUP BY --TRUNC(PD.CREATETIME), CD.USERID, CD.STAFFID, PD.GOODSCODE ) ZCL --ON CQ.员工ID =ZCL.员工ID AND CQ.用户ID = ZCL.用户ID --AND TRUNC(CQ.日期) = TRUNC(ZCL.日期) INNER JOIN TP_HR_STAFF F ON F.STAFFID = ZCL.员工ID INNER JOIN TP_MST_USER U ON U.USERID = ZCL.用户ID INNER JOIN TP_MST_ORGANIZATION O ON U.ORGANIZATIONID = O.ORGANIZATIONID )"; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result1 += conn.ExecuteNonQuery(sqlStr2, sqlPara.ToArray()); } conn.Commit(); } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export006员工出勤汇总表结转失败！"); } finally { conn.Close(); } if (result > 0 && result1 > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export006员工出勤汇总表结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export006员工出勤汇总表结转0条！"); } } } public static void export008() { using (IDataAccess conn = DataAccess.Create()) { try { Curtain.Log.Logger.Debug("export008 产品合格数统计表结转开始！"); conn.Open(); conn.BeginTransaction(); //2021-07-12 dongyan 修正什么问题 int result = 0; int m = DateTime.Now.AddMonths(-1).Month; string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); String sqlStr = @"delete AJZ_RPT008 WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1).AddMilliseconds(-1); String sqlStr1 = @" INSERT INTO AJZ_RPT008 SELECT BUILDINGNO, GOODSCODE, GOODSNAME , GOODSMODEL , DICTIONARYVALUE , LOGONAME , OUT_K_B , OUT_K_R , OK_B , OK_R , OK_H , OK_ALL , OK_COUNT , OUT_K_ALL , END_B , END_R , END_H , END_ALL , to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')), GLAZETYPEID, LOGOID FROM ( SELECT DECODE(GID, 3, T.楼号 || '合计' ， 15, T.楼号 || '合计', T.楼号) AS BUILDINGNO ,DECODE(GID, 15, '--', T.产品编码) AS GOODSCODE ,DECODE(GID, 15, '--', T.产品名称) AS GOODSNAME ,DECODE(GID, 15, '--', T.产品型号) AS GOODSMODEL ,DECODE(GID, 3, '--' ， 15, '--', T.釉料) AS DICTIONARYVALUE ,DECODE(GID, 3, '--' ， 15, '--', T.商标) AS LOGONAME ,T.出窑数_本烧 AS OUT_K_B ,T.出窑数_重烧 AS OUT_K_R ,T.合格数_本烧 AS OK_B ,T.合格数_重烧 AS OK_R ,T.合格数_回收 AS OK_H ,T.合格数_合计 AS OK_ALL ,NVL(ROUND(T.合格数_合计 / DECODE(T.出窑数, 0, 1, T.出窑数), 4) * 100, 0) || '%' AS OK_COUNT ,T.包装数_本烧 AS END_B ,T.包装数_重烧 AS END_R ,T.包装数_回收 AS END_H ,T.包装数_合计 AS END_ALL ,T.出窑数 AS OUT_K_ALL ,T.釉料ID AS GLAZETYPEID ,T.商标ID AS LOGOID FROM (SELECT GROUPING_ID(L.BUILDINGNO, M.GOODSID, D.DICTIONARYVALUE, O.LOGONAME) AS GID ,L.BUILDINGNO AS 楼号 ,M.GOODSID AS 产品ID ,M.GOODSCODE AS 产品编码 ,M.GOODSNAME AS 产品名称 ,M.GOODSMODEL AS 产品型号 ,G.GLAZETYPEID AS 釉料ID ,D.DICTIONARYVALUE AS 釉料 ,G.LOGOID AS 商标ID ,O.LOGONAME AS 商标 ,SUM(TF.OUT_K_B) AS 出窑数_本烧 ,SUM(TF.OUT_K_R) AS 出窑数_重烧 ,SUM(TF.OUT_K_ALL) AS 出窑数 ,SUM(TF.OK_B) AS 合格数_本烧 ,SUM(TF.OK_R) AS 合格数_重烧 ,SUM(TF.OK_H) AS 合格数_回收 ,SUM(TF.OK_ALL) AS 合格数_合计 ,SUM(TF.END_B) 包装数_本烧 ,SUM(TF.END_R) 包装数_重烧 ,SUM(TF.END_H) 包装数_回收 ,SUM(TF.END_ALL) 包装数_合计 FROM (SELECT 1 FLAG ,TP.GROUTINGDAILYDETAILID ,decode(TP.PROCEDUREID, 13, 1, 0) OUT_K_B ,decode(TP.PROCEDUREID, 35, 1, 0) OUT_K_R ,1 OUT_K_ALL ,0 OK_B ,0 OK_R ,0 OK_H ,0 OK_ALL ,0 END_B ,0 END_R ,0 END_H ,0 END_ALL FROM TP_PM_PRODUCTIONDATA TP WHERE TP.PROCEDUREID IN (13, 35) AND TP.Checkflag = 1 AND TP.Createtime >= @DATEBEGIN@ AND TP.Createtime <= @DATEEND@ UNION ALL SELECT 2 FLAG ,P.GROUTINGDAILYDETAILID ,0 OUT_K_B ,0 OUT_K_R ,0 OUT_K_ALL ,CASE WHEN P.PROCEDUREID = 13 AND RPD.Productiondataid IS NULL THEN 1 ELSE 0 END OK_B ,CASE WHEN P.PROCEDUREID = 35 AND RPD.Productiondataid IS NULL THEN 1 ELSE 0 END OK_R ,CASE WHEN RPD.Productiondataid IS NOT NULL THEN 1 ELSE 0 END OK_H ,1 OK_ALL ,0 END_B ,0 END_R ,0 END_H ,0 END_ALL FROM (SELECT TP.PRODUCTIONDATAID ,TP.GROUTINGDAILYDETAILID ,TP.PROCEDUREID ,MAX(PMAX.PRODUCTIONDATAID) AS MAXPRODUCTIONDATAID FROM TP_PM_PRODUCTIONDATA TP LEFT JOIN TP_PM_PRODUCTIONDATA PMAX ON PMAX.GROUTINGDAILYDETAILID = TP.GROUTINGDAILYDETAILID AND PMAX.PRODUCTIONDATAID < TP.PRODUCTIONDATAID AND PMAX.VALUEFLAG = '1' WHERE TP.CHECKBATCHNO = 1 AND TP.GOODSLEVELTYPEID IN (4, 5) AND TP.PROCEDUREID IN (13, 35) AND TP.CREATETIME >= @DATEBEGIN@ AND TP.CREATETIME <= @DATEEND@ GROUP BY TP.PRODUCTIONDATAID ,TP.GROUTINGDAILYDETAILID ,TP.PROCEDUREID) P LEFT JOIN TP_PM_PRODUCTIONDATA RPD ON RPD.PRODUCTIONDATAID = P.MAXPRODUCTIONDATAID AND RPD.Procedureid IN (20, 22, 23, 37) UNION ALL SELECT 3 FLAG ,fp.groutingdailydetailid ,0 OUT_K_B ,0 OUT_K_R ,0 OUT_K_ALL ,0 OK_B ,0 OK_R ,0 OK_H ,0 OK_ALL ,CASE WHEN SUM(CASE WHEN fp.isrefire = '6' OR p.productiondataid IS NOT NULL THEN 0 ELSE 1 END) > 0 THEN 1 ELSE 0 END END_B ,CASE WHEN SUM(CASE WHEN fp.isrefire = '0' OR p.productiondataid IS NOT NULL THEN 0 ELSE 1 END) > 0 THEN 1 ELSE 0 END END_R ,CASE WHEN SUM(CASE WHEN p.productiondataid IS NULL THEN 0 ELSE 1 END) > 0 THEN 1 ELSE 0 END END_H ,1 END_ALL FROM tp_pm_finishedproduct fp LEFT JOIN tp_pm_productiondata p ON p.groutingdailydetailid = fp.groutingdailydetailid AND p.procedureid IN (20, 22, 23, 37) WHERE fp.createtime >= @DATEBEGIN@ AND fp.createtime <= @DATEEND@ GROUP BY fp.groutingdailydetailid) TF LEFT JOIN TP_PM_GROUTINGDAILYDETAIL G ON TF.GROUTINGDAILYDETAILID = G.GROUTINGDAILYDETAILID LEFT JOIN TP_PC_GROUTINGLINE L ON G.GROUTINGLINEID = L.GROUTINGLINEID LEFT JOIN TP_MST_DATADICTIONARY D ON G.GLAZETYPEID = D.DICTIONARYID LEFT JOIN TP_MST_LOGO O ON G.LOGOID = O.LOGOID LEFT JOIN TP_MST_GOODS M ON G.GOODSID = M.GOODSID GROUP BY GROUPING SETS((L.BUILDINGNO, M.GOODSID, M.GOODSCODE, M.GOODSNAME, M.GOODSMODEL, G.GLAZETYPEID, G.LOGOID, D.DICTIONARYVALUE, O.LOGONAME),(L.BUILDINGNO, M.GOODSID, M.GOODSCODE, M.GOODSNAME, M.GOODSMODEL),()) ORDER BY L.BUILDINGNO ,M.GOODSCODE ,GID) T ) "; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result = conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); } conn.Commit(); if (result > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export008产品合格数统计表结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export008产品合格数统计表结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export008产品合格数统计表结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export009() { using (IDataAccess conn = DataAccess.Create()) { try { Curtain.Log.Logger.Debug("export009 产品质量统计表结转开始！"); conn.Open(); conn.BeginTransaction(); //2021-07-12 dongyan 修正什么问题 int result = 0; int m = DateTime.Now.AddMonths(-1).Month; DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); string sqlStr = @"delete AJZ_RPT009 WHERE FORWARD_TIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1).AddMilliseconds(-1); string sqlStr1 = @" INSERT INTO AJZ_RPT009 SELECT * FROM ( SELECT A.日期, A.楼号, NVL(A.本烧隧道窑_出窑数,0) AS 本烧隧道窑_出窑数, --NVL(A.本烧隧道窑_合格量 - A.本烧隧道窑_AA合格,0) AS 本烧隧道窑_合格量123, NVL(A.本烧隧道窑_合格量 - A.本烧隧道窑_AA合格 - A.本烧隧道窑_漏气研磨刷洗,0) AS 本烧隧道窑_合格量, NVL(A.本烧隧道窑_漏气合格,0) AS 本烧隧道窑_漏气合格, NVL(A.本烧隧道窑_研磨合格,0) AS 本烧隧道窑_研磨合格, NVL(A.本烧隧道窑_刷洗合格,0) AS 本烧隧道窑_刷洗合格, NVL(A.本烧隧道窑_AA合格,0) AS 本烧隧道窑_AA合格, NVL(ROUND((A.本烧隧道窑_合格量) / DECODE(A.本烧隧道窑_出窑数,0,1,A.本烧隧道窑_出窑数),4) * 100,0) AS 本烧隧道窑_合格率, NVL(A.本烧梭式窑_出窑数,0) AS 本烧梭式窑_出窑数, --NVL(A.本烧梭式窑_合格量 - A.本烧梭式窑_AA合格,0) AS 本烧梭式窑_合格量123, NVL(A.本烧梭式窑_合格量 - A.本烧梭式窑_AA合格 - A.本烧梭式窑_漏气研磨刷洗,0) AS 本烧梭式窑_合格量, NVL(A.本烧梭式窑_漏气合格,0) AS 本烧梭式窑_漏气合格, NVL(A.本烧梭式窑_研磨合格,0) AS 本烧梭式窑_研磨合格, NVL(A.本烧梭式窑_刷洗合格,0) AS 本烧梭式窑_刷洗合格, NVL(A.本烧梭式窑_AA合格,0) AS 本烧梭式窑_AA合格, NVL(ROUND((A.本烧梭式窑_合格量) / DECODE(A.本烧梭式窑_出窑数,0,1,A.本烧梭式窑_出窑数),4) * 100,0) AS 本烧梭式窑_合格率, NVL(A.本烧合计_出窑数,0) AS 本烧合计_出窑数, --NVL(A.本烧合计_合格量 - A.本烧合计_AA合格,0) AS 本烧合计_合格量123, NVL(A.本烧合计_合格量 - A.本烧合计_AA合格 - A.本烧合计_漏气研磨刷洗,0) AS 本烧合计_合格量, NVL(A.本烧合计_漏气合格,0) AS 本烧合计_漏气合格, NVL(A.本烧合计_研磨合格,0) AS 本烧合计_研磨合格, NVL(A.本烧合计_刷洗合格,0) AS 本烧合计_刷洗合格, NVL(A.本烧合计_AA合格,0) AS 本烧合计_AA合格, NVL(ROUND((A.本烧合计_合格量) / DECODE(A.本烧合计_出窑数,0,1,A.本烧合计_出窑数),4) * 100,0) AS 本烧合计_合格率, NVL(A.重烧_出窑数,0) AS 重烧_出窑数, --NVL(A.重烧_合格量 - A.重烧_AA合格,0) AS 重烧_合格量123, NVL(A.重烧_合格量 - A.重烧_AA合格 - A.重烧_漏气研磨刷洗,0) AS 重烧_合格量, NVL(A.重烧_漏气合格,0) AS 重烧_漏气合格, NVL(A.重烧_研磨合格,0) AS 重烧_研磨合格, NVL(A.重烧_刷洗合格,0) AS 重烧_刷洗合格, NVL(A.重烧_AA合格,0) AS 重烧_AA合格, NVL(ROUND((A.重烧_合格量) / DECODE(A.重烧_出窑数,0,1,A.重烧_出窑数),4) * 100,0) AS 重烧_合格率, NVL(A.综合_出窑数,0) - NVL(A.重烧_出窑数,0) AS 综合_出窑数, NVL(A.综合_合格量 - A.综合_AA合格 - A.综合_漏气研磨刷洗,0) AS 综合_合格量, NVL(A.综合_漏气合格,0) AS 综合_漏气合格, NVL(A.综合_研磨合格,0) AS 综合_研磨合格, NVL(A.综合_刷洗合格,0) AS 综合_刷洗合格, NVL(A.综合_AA合格,0) AS 综合_AA合格, NVL(ROUND((A.综合_合格量) / DECODE(A.综合_出窑数,0,1,A.综合_出窑数 - A.重烧_出窑数),4) * 100,0) AS 综合_合格率, NVL(A.本烧隧道窑_合格量,0) AS 本烧隧道窑_合格数1, NVL(A.本烧梭式窑_合格量,0) AS 本烧梭式窑_合格量1, NVL(A.本烧合计_合格量,0) AS 本烧合计_合格量1, NVL(A.重烧_合格量,0) AS 重烧_合格量1, NVL(A.综合_合格量,0) AS 综合_合格量1, to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM ( SELECT DECODE(GROUPING_ID(C.CDATE, C.BUILDINGNO),0,TO_CHAR(C.CDATE, 'YYYY-MM-DD'),'合计') AS 日期, DECODE(GROUPING_ID(C.CDATE, C.BUILDINGNO), 0, C.BUILDINGNO, '--') AS 楼号, SUM(CASE WHEN C.MAXPRODUCTIONDATAID = -1 AND C.PROCEDUREID = 13 AND C.KILNID IN (2, 4) THEN C.CNUM ELSE 0 END) AS 本烧隧道窑_出窑数, SUM(CASE WHEN C.PROCEDUREID = 13 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.KILNID IN (2, 4) THEN C.CNUM ELSE 0 END) AS 本烧隧道窑_合格量, SUM(CASE WHEN C.PROCEDUREID = 13 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.KILNID IN (2, 4) AND C.P2 IN (22,23) THEN C.CNUM ELSE 0 END) AS 本烧隧道窑_AA合格, SUM(CASE WHEN C.PROCEDUREID = 13 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.KILNID IN (2, 4) AND C.P2 IN (37,20,41) THEN C.CNUM ELSE 0 END) AS 本烧隧道窑_漏气研磨刷洗, SUM(CASE WHEN C.PROCEDUREID = 13 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.KILNID IN (2, 4) AND C.P2 = 37 THEN C.CNUM ELSE 0 END) AS 本烧隧道窑_漏气合格, SUM(CASE WHEN C.PROCEDUREID = 13 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.KILNID IN (2, 4) AND C.P2 = 20 THEN C.CNUM ELSE 0 END) AS 本烧隧道窑_研磨合格, SUM(CASE WHEN C.PROCEDUREID = 13 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.KILNID IN (2, 4) AND C.P2 = 41 THEN C.CNUM ELSE 0 END) AS 本烧隧道窑_刷洗合格, SUM(CASE WHEN C.MAXPRODUCTIONDATAID = -1 AND C.PROCEDUREID = 13 AND C.KILNID IN (1, 3) THEN C.CNUM ELSE 0 END) AS 本烧梭式窑_出窑数, SUM(CASE WHEN C.PROCEDUREID = 13 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.KILNID IN (1, 3) THEN C.CNUM ELSE 0 END) AS 本烧梭式窑_合格量, SUM(CASE WHEN C.PROCEDUREID = 13 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.KILNID IN (1, 3) AND C.P2 IN (22,23) THEN C.CNUM ELSE 0 END) AS 本烧梭式窑_AA合格, SUM(CASE WHEN C.PROCEDUREID = 13 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.KILNID IN (1, 3) AND C.P2 IN (37,20,41) THEN C.CNUM ELSE 0 END) AS 本烧梭式窑_漏气研磨刷洗, SUM(CASE WHEN C.PROCEDUREID = 13 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.KILNID IN (1, 3) AND C.P2 = 37 THEN C.CNUM ELSE 0 END) AS 本烧梭式窑_漏气合格, SUM(CASE WHEN C.PROCEDUREID = 13 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.KILNID IN (1, 3) AND C.P2 = 20 THEN C.CNUM ELSE 0 END) AS 本烧梭式窑_研磨合格, SUM(CASE WHEN C.PROCEDUREID = 13 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.KILNID IN (1, 3) AND C.P2 = 41 THEN C.CNUM ELSE 0 END) AS 本烧梭式窑_刷洗合格, SUM(CASE WHEN C.MAXPRODUCTIONDATAID = -1 AND C.PROCEDUREID = 13 THEN C.CNUM ELSE 0 END) AS 本烧合计_出窑数, SUM(CASE WHEN C.PROCEDUREID = 13 AND C.GOODSLEVELTYPEID IN (4, 5) THEN C.CNUM ELSE 0 END) AS 本烧合计_合格量, SUM(CASE WHEN C.PROCEDUREID = 13 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.P2 IN (22,23) THEN C.CNUM ELSE 0 END) AS 本烧合计_AA合格, SUM(CASE WHEN C.PROCEDUREID = 13 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.P2 IN (37,20,41) THEN C.CNUM ELSE 0 END) AS 本烧合计_漏气研磨刷洗, SUM(CASE WHEN C.PROCEDUREID = 13 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.P2 = 37 THEN C.CNUM ELSE 0 END) AS 本烧合计_漏气合格, SUM(CASE WHEN C.PROCEDUREID = 13 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.P2 = 20 THEN C.CNUM ELSE 0 END) AS 本烧合计_研磨合格, SUM(CASE WHEN C.PROCEDUREID = 13 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.P2 = 41 THEN C.CNUM ELSE 0 END) AS 本烧合计_刷洗合格, SUM(CASE WHEN C.MAXPRODUCTIONDATAID = -1 AND C.PROCEDUREID = 35 THEN C.CNUM ELSE 0 END) AS 重烧_出窑数, SUM(CASE WHEN C.PROCEDUREID = 35 AND C.GOODSLEVELTYPEID IN (4, 5) THEN C.CNUM ELSE 0 END) AS 重烧_合格量 , SUM(CASE WHEN C.PROCEDUREID = 35 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.P2 IN (22,23) THEN C.CNUM ELSE 0 END) AS 重烧_AA合格, SUM(CASE WHEN C.PROCEDUREID = 35 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.P2 IN (37,20,41) THEN C.CNUM ELSE 0 END) AS 重烧_漏气研磨刷洗, SUM(CASE WHEN C.PROCEDUREID = 35 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.P2 = 37 THEN C.CNUM ELSE 0 END) AS 重烧_漏气合格, SUM(CASE WHEN C.PROCEDUREID = 35 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.P2 = 20 THEN C.CNUM ELSE 0 END) AS 重烧_研磨合格, SUM(CASE WHEN C.PROCEDUREID = 35 AND C.GOODSLEVELTYPEID IN (4, 5) AND C.P2 = 41 THEN C.CNUM ELSE 0 END) AS 重烧_刷洗合格, SUM(CASE WHEN C.MAXPRODUCTIONDATAID = -1 AND C.PROCEDUREID IN (13, 35) THEN C.CNUM ELSE 0 END) AS 综合_出窑数, SUM(CASE WHEN C.PROCEDUREID IN (13, 35) AND C.GOODSLEVELTYPEID IN (4, 5) THEN C.CNUM ELSE 0 END) AS 综合_合格量, SUM(CASE WHEN C.PROCEDUREID IN (13, 35) AND C.GOODSLEVELTYPEID IN (4, 5) AND C.P2 IN (22,23) THEN C.CNUM ELSE 0 END) AS 综合_AA合格, SUM(CASE WHEN C.PROCEDUREID IN (13, 35) AND C.GOODSLEVELTYPEID IN (4, 5) AND C.P2 IN (37,20,41) THEN C.CNUM ELSE 0 END) AS 综合_漏气研磨刷洗, SUM(CASE WHEN C.PROCEDUREID IN (13, 35) AND C.GOODSLEVELTYPEID IN (4, 5) AND C.P2 = 37 THEN C.CNUM ELSE 0 END) AS 综合_漏气合格, SUM(CASE WHEN C.PROCEDUREID IN (13, 35) AND C.GOODSLEVELTYPEID IN (4, 5) AND C.P2 = 20 THEN C.CNUM ELSE 0 END) AS 综合_研磨合格, SUM(CASE WHEN C.PROCEDUREID IN (13, 35) AND C.GOODSLEVELTYPEID IN (4, 5) AND C.P2 = 41 THEN C.CNUM ELSE 0 END) AS 综合_刷洗合格 FROM ( SELECT T.CDATE, T.GROUTINGLINEID, T.KILNID, T.PROCEDUREID, T.GOODSLEVELTYPEID, T.PRODUCTIONDATAID, T.MAXPRODUCTIONDATAID, T.CNUM, L.BUILDINGNO, P2.PROCEDUREID P2 FROM ( SELECT TRUNC( P.CREATETIME ) AS CDATE, P.GROUTINGLINEID, P.KILNID, P.PROCEDUREID, P.GOODSLEVELTYPEID, P.PRODUCTIONDATAID, MAX( PMAX.PRODUCTIONDATAID ) MAXPRODUCTIONDATAID, 1 AS CNUM FROM TP_PM_PRODUCTIONDATA P LEFT JOIN TP_PM_PRODUCTIONDATA PMAX ON PMAX.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID AND PMAX.PRODUCTIONDATAID < P.PRODUCTIONDATAID AND PMAX.VALUEFLAG = '1' AND PMAX.PROCEDUREMODEL = '1' /*20 研磨 22 直接冷补 23 研磨后冷补（非回收） 37 堵漏气 41 刷洗不良*/ AND PMAX.PROCEDUREID IN (20, 22, 23, 37, 41) WHERE P.VALUEFLAG = '1' AND P.PROCEDUREID IN ( 13, 35 ) AND P.CHECKBATCHNO = 1 AND P.CREATETIME >= @DATEBEGIN@ AND P.CREATETIME <= @DATEEND@ GROUP BY P.CREATETIME, P.GROUTINGLINEID, P.KILNID, P.PROCEDUREID, P.GOODSLEVELTYPEID, P.PRODUCTIONDATAID union all SELECT TRUNC( P.CREATETIME ) AS CDATE, P.GROUTINGLINEID, P.KILNID, P.PROCEDUREID, 0 GOODSLEVELTYPEID, P.PRODUCTIONDATAID, -1 MAXPRODUCTIONDATAID, 1 AS CNUM FROM TP_PM_PRODUCTIONDATA P WHERE P.PROCEDUREID IN ( 13, 35 ) AND P.CHECKFLAG = 1 AND P.CREATETIME >= @DATEBEGIN@ AND P.CREATETIME <= @DATEEND@ ) T LEFT JOIN TP_PC_GROUTINGLINE L ON T.GROUTINGLINEID = L.GROUTINGLINEID LEFT JOIN TP_PM_PRODUCTIONDATA P2 ON T.MAXPRODUCTIONDATAID > 0 AND ( P2.PRODUCTIONDATAID = T.MAXPRODUCTIONDATAID AND P2.PROCEDUREID IN ( 22, 23, 37, 20 ) ) ) C GROUP BY GROUPING SETS ( ( C.CDATE, C.BUILDINGNO ), ( ) ) )A )DD WHERE DD.楼号<>'--' "; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result = conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); } conn.Commit(); if (result > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export009产品质量统计表结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export009产品质量统计表结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export009产品质量统计表结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export010() { using (IDataAccess conn = DataAccess.Create()) { try { Curtain.Log.Logger.Debug("export010 各工序缺陷统计表结转开始！"); conn.BeginTransaction(); //2021-07-12 dongyan 修正什么问题 int result = 0; int m = DateTime.Now.AddMonths(-1).Month; DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); String sqlStr = @"delete JZ_RPT010 WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); //dayEnd = dayBegin.AddMonths(1).AddMilliseconds(-1); dayEnd = dayBegin.AddMonths(1); String sqlStr1 = @" INSERT INTO JZ_RPT010 SELECT * FROM (SELECT KNUM.日期 AS 日期, KNUM.出窑量, '0' AS K_BUILDINGNO, '0' AS K_KILNID, to_number( to_char( @YEAR@ ) || lpad( to_char( @I@ ), 2, '0' ) ), DEFECT.* FROM ( SELECT * FROM ( SELECT DECODE( GID, 7, '合计', 6, '合计', 5, '合计', CDATE ) AS 缺陷日期, DECODE( GID, 1, DEFECTTYPENAME || '_合计', 3, '合计', 7, '合计', 6, DEFECTTYPENAME || '_' || DEFECTNAME, 5, DEFECTTYPENAME || '_合计', DEFECT ) AS DEFECT, CNUM FROM ( SELECT GROUPING_ID ( TRUNC( P.CHECKTIME ), T.DEFECTTYPEID, D.DEFECTID ) AS GID, TO_CHAR( TRUNC( P.CHECKTIME ), 'YYYY-MM-DD' ) AS CDATE, L.BUILDINGNO, P.KILNID, D.DEFECTID, M.DEFECTNAME, T.DEFECTTYPENAME, T.DEFECTTYPENAME || '_' || M.DEFECTNAME AS DEFECT, COUNT( * ) AS CNUM FROM TP_PM_PRODUCTIONDATA P LEFT JOIN TP_PC_GROUTINGLINE L ON P.GROUTINGLINEID = L.GROUTINGLINEID LEFT JOIN TP_PM_DEFECT D ON P.PRODUCTIONDATAID = D.PRODUCTIONDATAID LEFT JOIN TP_MST_DEFECT M ON ( M.DEFECTID = D.DEFECTID AND M.DEFECTID > 0 ) LEFT JOIN TP_MST_DEFECTTYPE T ON T.DEFECTTYPEID = M.DEFECTTYPEID WHERE P.VALUEFLAG = '1' AND P.PROCEDUREID = 13 AND P.CHECKBATCHNO = 1 AND P.GOODSLEVELTYPEID <> 4 AND P.CHECKTIME >= @DATEBEGIN@ AND P.CHECKTIME < @DATEEND@ GROUP BY GROUPING SETS ( ( TRUNC( P.CHECKTIME ), L.BUILDINGNO, P.KILNID, T.DEFECTTYPEID, D.DEFECTID, M.DEFECTNAME, T.DEFECTTYPENAME ), ( TRUNC( P.CHECKTIME ), T.DEFECTTYPEID, T.DEFECTTYPENAME ), ( TRUNC( P.CHECKTIME ) ), ( D.DEFECTID, T.DEFECTTYPENAME, M.DEFECTNAME ), ( T.DEFECTTYPEID, T.DEFECTTYPENAME ), ( ) ) ORDER BY CDATE, T.DEFECTTYPEID, D.DEFECTID ) ) PIVOT ( SUM( CNUM ) FOR DEFECT IN ( '成型缺陷_糙活', '成型缺陷_成裂', '成型缺陷_成脏', '成型缺陷_成走', '成型缺陷_孔不良', '成型缺陷_泥绺', '成型缺陷_坯脏', '成型缺陷_注泡', '成型缺陷_棕眼', '成型缺陷_漏气', '成型缺陷_卡球', '成型缺陷_炸圈', '成型缺陷_排水不良', '成型缺陷_合计', '施釉缺陷_爆釉', '施釉缺陷_脱釉', '施釉缺陷_釉磕', '施釉缺陷_标不良', '施釉缺陷_釉薄', '施釉缺陷_釉绺', '施釉缺陷_釉脏', '施釉缺陷_釉粘', '施釉缺陷_波纹', '施釉缺陷_釉泡', '施釉缺陷_氯化镁', '施釉缺陷_灌釉不良', '施釉缺陷_合计', '出装缺陷_蹭釉', '出装缺陷_落脏', '出装缺陷_装磕', '出装缺陷_装脏', '出装缺陷_装粘', '出装缺陷_装走', '出装缺陷_合计', '烧窑缺陷_桔釉', '烧窑缺陷_烧裂', '烧窑缺陷_烧生', '烧窑缺陷_烧炸', '烧窑缺陷_桔柚', '烧窑缺陷_过火泡', '烧窑缺陷_针孔', '烧窑缺陷_麻坑', '烧窑缺陷_窑脏', '烧窑缺陷_烟熏', '烧窑缺陷_风惊', '烧窑缺陷_水滴', '烧窑缺陷_合计', '原料缺陷_铁脏', '原料缺陷_铜脏', '原料缺陷_合计', '其他缺陷_冷补', '其他缺陷_坯磕', '其他缺陷_研磨', '其他缺陷_崩脏', '其他缺陷_漏气合格', '其他缺陷_复烧修补不良', '其他缺陷_B级品', '其他缺陷_实验', '其他缺陷_合计', '出窑缺陷_划釉', '出窑缺陷_出磕', '出窑缺陷_合计', '窑缺陷_合计', '点釉缺陷_点釉', '点釉缺陷_合计', '冷补缺陷_补不良', '冷补缺陷_磕划', '冷补缺陷_漏气', '冷补缺陷_成检漏验', '冷补缺陷_研磨不良', '冷补缺陷_合计', '研磨缺陷_眼', '研磨缺陷_底', '研磨缺陷_底和面', '研磨缺陷_磕划', '研磨缺陷_研磨不良', '研磨缺陷_成检漏验', '研磨缺陷_面', '研磨缺陷_边', '研磨缺陷_研磨配盖', '研磨缺陷_合计' , '合计' AS 合计 ) ) ) DEFECT RIGHT JOIN ( SELECT DECODE( GROUPING_ID ( TRUNC( P.CHECKTIME ) ), 1, '合计', TO_CHAR( TRUNC( P.CHECKTIME ), 'YYYY-MM-DD' ) ) AS 日期, COUNT( * ) AS 出窑量 FROM TP_PM_PRODUCTIONDATA P LEFT JOIN TP_PC_GROUTINGLINE L ON P.GROUTINGLINEID = L.GROUTINGLINEID WHERE P.VALUEFLAG = '1' AND P.PROCEDUREID = 13 AND P.CHECKBATCHNO = 1 AND P.CHECKTIME >= @DATEBEGIN@ AND P.CHECKTIME < @DATEEND@ GROUP BY GROUPING SETS ( ( TRUNC( P.CHECKTIME ) ), ( ) ) ) KNUM ON DEFECT.缺陷日期 = KNUM.日期 )DD WHERE DD.日期<> '合计'"; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result = conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); } conn.Commit(); if (result > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export010各工序缺陷统计表结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export010各工序缺陷统计表结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export010各工序缺陷统计表结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export011() { using (IDataAccess conn = DataAccess.Create()) { try { Curtain.Log.Logger.Debug("export011 成型半月品月报结转开始！"); conn.Open(); conn.BeginTransaction(); //2021-07-12 dongyan 修正什么问题 int result = 0; int m = DateTime.Now.AddMonths(-1).Month; DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); string sqlStr = @"delete AJZ_RPT011 WHERE FORWARD_TIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1).AddMilliseconds(-1); string sqlStr1 = @" INSERT INTO AJZ_RPT011 SELECT 组别, 成型工号, 物料名称, 成型, 成型线号, 接收数量, 实收数量, 返修数量, '0' AS BUILDINGNO, to_number( to_char( @YEAR@ ) || lpad( to_char( @I@ ), 2, '0' ) ), '0' AS MONITORCODE, '0' AS GOODSCODE FROM ( SELECT DECODE( GROUPING_ID ( T.MONITORCODE, T.GOODSCODE ), 1, '合计', 3, '合计', T.MONITORNAME ) AS 组别, DECODE( GROUPING_ID ( T.MONITORCODE, T.GOODSCODE ), 1, '--', 3, '--', T.USERCODE ) AS 成型工号, DECODE( GROUPING_ID ( T.MONITORCODE, T.GOODSCODE ), 1, '--', 3, '--', T.GOODSNAME ) AS 物料名称, DECODE( GROUPING_ID ( T.MONITORCODE, T.GOODSCODE ), 1, '--', 3, '--', T.USERNAME ) AS 成型, DECODE( GROUPING_ID ( T.MONITORCODE, T.GOODSCODE ), 1, '--', 3, '--', T.GROUTINGLINENO ) AS 成型线号, SUM( CASE WHEN T.PROCEDUREID IN ( 25, 2 ) AND T.VALUEFLAG = '1' THEN T.CNUM ELSE 0 END ) AS 接收数量, SUM( CASE WHEN T.PROCEDUREID IN ( 26, 3 ) AND T.VALUEFLAG = '1' THEN T.CNUM ELSE 0 END ) AS 实收数量, SUM( CASE WHEN T.VALUEFLAG = '0' THEN T.CNUM ELSE 0 END ) AS 返修数量 FROM ( SELECT MU.USERCODE AS MONITORCODE, MU.USERNAME AS MONITORNAME, U.USERCODE, P.VALUEFLAG, P.GOODSCODE, P.GOODSNAME, U.USERNAME, L.GROUTINGLINENO, P.PROCEDUREID, count( * ) AS CNUM FROM TP_PM_PRODUCTIONDATA P LEFT JOIN TP_PM_GROUTINGDAILY G ON G.GROUTINGDAILYID = P.GROUTINGDAILYID LEFT JOIN TP_PC_GROUTINGLINE L ON L.GROUTINGLINEID = G.GROUTINGLINEID --LEFT JOIN TP_MST_USER MU ON MU.USERCODE = G.MONITORCODE LEFT JOIN TP_MST_USER MU ON MU.USERID = L.MONITORID LEFT JOIN TP_MST_USER U ON U.USERID = G.USERID WHERE ( ( P.VALUEFLAG = '1' AND P.CREATETIME >= @DATEBEGIN@ AND P.CREATETIME <= @DATEEND@) OR ( P.VALUEFLAG = '0' AND P.BACKOUTTIME >= @DATEBEGIN@ AND P.BACKOUTTIME <= @DATEEND@) ) -- AND ( L.BUILDINGNO = @BUILDINGNO @OR @BUILDINGNO @IS NULL ) AND P.PROCEDUREID IN ( 25, 26, 2, 3 ) -- 一期还是二期 -- AND ( P.GOODSCODE = @GOODSCODE @OR @GOODSCODE @IS NULL ) -- AND ( U.USERCODE = @USERCODE @OR @USERCODE @IS NULL ) -- AND ( MU.USERNAME = @USERNAME @OR @USERNAME @IS NULL ) GROUP BY MU.USERCODE, MU.USERNAME, U.USERCODE, U.USERNAME, P.VALUEFLAG, P.PROCEDUREID, P.GOODSCODE, P.GOODSNAME, L.GROUTINGLINENO ORDER BY G.MONITORCODE, G.USERCODE ) T GROUP BY GROUPING SETS ( ( T.MONITORCODE, T.MONITORNAME, T.USERNAME, T.USERCODE, T.USERNAME, T.GOODSCODE, T.GOODSNAME, T.GROUTINGLINENO ), ( T.MONITORCODE ), ( ) ) ) WHERE 组别 <> '合计' "; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result = conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); } conn.Commit(); if (result > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export011成型半月品月报结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export011成型半月品月报结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export011成型半月品月报结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export012() { using (IDataAccess conn = DataAccess.Create()) { try { Curtain.Log.Logger.Debug("export012 成型缺陷统计表结转开始！"); conn.Open(); conn.BeginTransaction(); //2021-07-12 dongyan 修正什么问题 int result = 0; int m = DateTime.Now.AddMonths(-1).Month; //DateTime dayBegin = new DateTime(); //DateTime dayEnd = new DateTime(); DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); string sqlStr = @"delete AJZ_RPT012 WHERE FORWARD_TIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { //dayBegin =Convert.ToDateTime( b + "-" + i.ToString().PadLeft(2, '0') + "-01"); //dayEnd =Convert.ToDateTime( b + "-" + (i + 1).ToString().PadLeft(2, '0') + "-01"); dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1).AddMilliseconds(-1); string sql2 = @" INSERT INTO AJZ_RPT012 SELECT PC.组别, PC.工号, PC.产品型号, PC.姓名, PC.出窑数, PC.出窑数 - NVL( PD.合计, 0 ) AS 合格数, 0 as BUILDINGNO, 2 as PROCEDUREID, TO_CHAR( ROUND( ( PC.出窑数 - NVL( PD.合计, 0 ) ) / DECODE( PC.出窑数, 0, 1, PC.出窑数 ), 4 ) * 100 ) || '%' AS 合格率, TO_CHAR( 100 - ROUND( ( PC.出窑数 - NVL( PD.合计, 0 ) ) / DECODE( PC.出窑数, 0, 1, PC.出窑数 ), 4 ) * 100 ) || '%' AS 缺陷率, PD.组别, PD.MONITORCODE, PD.工号, PD.姓名, PD.产品型号, '0' as NO, 2 as PRO, PD.糙活, PD.成裂, PD.成脏, PD.成走, PD.孔不良, PD.泥绺, PD.坯脏, PD.注泡, PD.棕眼, PD.漏气, PD.卡球, PD.炸圈, PD.排水不良, PD.合计, to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM ( SELECT * FROM ( SELECT --GID, DECODE( GID, 6, MONITORNAME || '[合计]', 7, MONITORNAME || '[合计]',14,'合计',15,'合计', MONITORNAME ) AS 组别, DECODE( GID, 6, MONITORCODE || '[合计]', 7, MONITORCODE || '[合计]',14,'ZZZ',15,'ZZZ', MONITORCODE ) AS MONITORCODE, USERCODE AS 工号, USERNAME AS 姓名, GOODSMODEL AS 产品型号, DECODE( GID, 1, '合计', 7, '合计',15,'合计', DEFECT ) AS DEFECT, CNUM FROM ( SELECT GROUPING_ID ( MU.USERCODE, U.USERCODE, GS.GOODSMODEL, D.DEFECTID ) AS GID, NVL( MU.USERCODE, ' ' ) AS MONITORCODE, NVL( MU.USERNAME, '无' ) AS MONITORNAME, U.USERCODE, U.USERNAME, GS.GOODSMODEL, D.DEFECTID, M.DEFECTNAME AS DEFECT, --COUNT( * ) AS CNUM SUM(CASE WHEN D.DEFECTID = 11 AND (P.GOODSLEVELTYPEID = 4 OR P.GOODSLEVELTYPEID = 5) THEN 0 ELSE 1 END) AS CNUM FROM TP_PM_PRODUCTIONDATA P INNER JOIN TP_PC_GROUTINGLINE L ON P.GROUTINGLINEID = L.GROUTINGLINEID INNER JOIN TP_PM_GROUTINGDAILY G ON G.GROUTINGDAILYID = P.GROUTINGDAILYID LEFT JOIN TP_MST_USER MU ON MU.USERID = L.MONITORID INNER JOIN TP_PM_DEFECT D ON P.PRODUCTIONDATAID = D.PRODUCTIONDATAID INNER JOIN TP_MST_DEFECT M ON ( M.DEFECTID = D.DEFECTID AND M.DEFECTID > 0 ) LEFT JOIN TP_MST_DEFECTTYPE T ON T.DEFECTTYPEID = M.DEFECTTYPEID --LEFT JOIN TP_MST_USER U ON U.USERCODE = G.USERCODE INNER JOIN TP_MST_USER U ON U.USERID = D.DEFECTUSERID LEFT JOIN TP_MST_GOODS GS ON GS.GOODSID = P.GOODSID WHERE P.VALUEFLAG = '1' AND P.PROCEDUREID = 13 AND P.CHECKBATCHNO = 1 AND P.GOODSLEVELTYPEID <> 4 AND P.CHECKTIME >= @DATEBEGIN@ AND P.CHECKTIME <= @DATEEND@ AND P.ISREFIRE = '0' AND D.DEFECTPROCEDUREID = 2 GROUP BY GROUPING SETS ( ( MU.USERCODE,MU.USERNAME,U.USERCODE,U.USERNAME,GS.GOODSMODEL,D.DEFECTID,M.DEFECTNAME ), ( MU.USERCODE, MU.USERNAME, U.USERCODE, U.USERNAME, GS.GOODSMODEL ), ( MU.USERCODE, MU.USERNAME, D.DEFECTID, M.DEFECTNAME ), ( MU.USERCODE, MU.USERNAME ), ( D.DEFECTID, M.DEFECTNAME ), () ) ORDER BY MONITORCODE, USERCODE, GROUPING_ID ( MU.USERCODE, U.USERCODE, GS.GOODSMODEL, D.DEFECTID ) ) ) PIVOT ( SUM( CNUM ) FOR DEFECT IN ( '糙活' AS 糙活, '成裂' AS 成裂, '成脏' AS 成脏, '成走' AS 成走, '孔不良' AS 孔不良, '泥绺' AS 泥绺, '坯脏' AS 坯脏, '注泡' AS 注泡, '棕眼' AS 棕眼, '漏气' AS 漏气, '卡球' AS 卡球, '炸圈' AS 炸圈, '排水不良' AS 排水不良, '合计' AS 合计 ) ) ) PD RIGHT JOIN ( SELECT GID, DECODE( GID, 3, T.MONITORCODE || '[合计]',7,'ZZZ', T.MONITORCODE ) AS MONITORCODE, DECODE( GID, 3, T.组别 || '[合计]',7,'合计', T.组别 ) AS 组别, T.工号, T.姓名, T.产品型号, T.出窑数 FROM ( SELECT GROUPING_ID ( MU.USERCODE, U.USERCODE, GS.GOODSMODEL ) AS GID, NVL( MU.USERCODE, ' ' ) MONITORCODE, NVL( MU.USERNAME, '无' ) AS 组别, U.USERCODE AS 工号, U.USERNAME AS 姓名, GS.GOODSMODEL AS 产品型号, COUNT( * ) AS 出窑数 FROM TP_PM_PRODUCTIONDATA P INNER JOIN TP_PC_GROUTINGLINE L ON P.GROUTINGLINEID = L.GROUTINGLINEID INNER JOIN TP_PM_GROUTINGDAILY G ON G.GROUTINGDAILYID = P.GROUTINGDAILYID LEFT JOIN TP_MST_USER MU ON MU.USERID = L.MONITORID --LEFT JOIN TP_PM_DEFECT D ON P.PRODUCTIONDATAID = D.PRODUCTIONDATAID LEFT JOIN TP_PM_PRODUCTIONDATA PB ON P.BARCODE = PB.BARCODE AND PB.MODELTYPE = 5 LEFT JOIN TP_MST_USER U ON U.USERID = PB.USERID LEFT JOIN TP_MST_GOODS GS ON P.GOODSID = GS.GOODSID WHERE P.VALUEFLAG = '1' AND P.PROCEDUREID = 13 AND P.CHECKBATCHNO = 1 AND P.CHECKTIME >= @DATEBEGIN@ AND P.CHECKTIME <= @DATEEND@ AND PB.PROCEDUREID = 2 AND P.ISREFIRE = '0' AND PB.VALUEFLAG='1' GROUP BY GROUPING SETS ( ( MU.USERCODE, MU.USERNAME, U.USERCODE, U.USERNAME, GS.GOODSMODEL ), ( MU.USERCODE, MU.USERNAME ), () ) ) T ) PC ON PD.MONITORCODE = PC.MONITORCODE AND ( PC.GID = 3 OR PC.GID=7 OR ( PD.工号 = PC.工号 AND PD.产品型号 = PC.产品型号 ) ) "; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result = conn.ExecuteNonQuery(sql2, sqlPara.ToArray()); string sql3 = @" INSERT INTO AJZ_RPT012 SELECT PC.组别, PC.工号, PC.产品型号, PC.姓名, PC.出窑数, PC.出窑数 - NVL( PD.合计, 0 ) AS 合格数, 0 as BUILDINGNO, 25 as PROCEDUREID, TO_CHAR( ROUND( ( PC.出窑数 - NVL( PD.合计, 0 ) ) / DECODE( PC.出窑数, 0, 1, PC.出窑数 ), 4 ) * 100 ) || '%' AS 合格率, TO_CHAR( 100 - ROUND( ( PC.出窑数 - NVL( PD.合计, 0 ) ) / DECODE( PC.出窑数, 0, 1, PC.出窑数 ), 4 ) * 100 ) || '%' AS 缺陷率, PD.组别, PD.MONITORCODE, PD.工号, PD.姓名, PD.产品型号, 0, 25 as PRO, PD.糙活, PD.成裂, PD.成脏, PD.成走, PD.孔不良, PD.泥绺, PD.坯脏, PD.注泡, PD.棕眼, PD.漏气, PD.卡球, PD.炸圈, PD.排水不良, PD.合计, to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM ( SELECT * FROM ( SELECT --GID, DECODE( GID, 6, MONITORNAME || '[合计]', 7, MONITORNAME || '[合计]',14,'合计',15,'合计', MONITORNAME ) AS 组别, DECODE( GID, 6, MONITORCODE || '[合计]', 7, MONITORCODE || '[合计]',14,'ZZZ',15,'ZZZ', MONITORCODE ) AS MONITORCODE, USERCODE AS 工号, USERNAME AS 姓名, GOODSMODEL AS 产品型号, DECODE( GID, 1, '合计', 7, '合计',15,'合计', DEFECT ) AS DEFECT, CNUM FROM ( SELECT GROUPING_ID ( MU.USERCODE, U.USERCODE, GS.GOODSMODEL, D.DEFECTID ) AS GID, NVL( MU.USERCODE, ' ' ) AS MONITORCODE, NVL( MU.USERNAME, '无' ) AS MONITORNAME, U.USERCODE, U.USERNAME, GS.GOODSMODEL, D.DEFECTID, M.DEFECTNAME AS DEFECT, --COUNT( * ) AS CNUM SUM(CASE WHEN D.DEFECTID = 11 AND (P.GOODSLEVELTYPEID = 4 OR P.GOODSLEVELTYPEID = 5) THEN 0 ELSE 1 END) AS CNUM FROM TP_PM_PRODUCTIONDATA P INNER JOIN TP_PC_GROUTINGLINE L ON P.GROUTINGLINEID = L.GROUTINGLINEID INNER JOIN TP_PM_GROUTINGDAILY G ON G.GROUTINGDAILYID = P.GROUTINGDAILYID LEFT JOIN TP_MST_USER MU ON MU.USERID = L.MONITORID INNER JOIN TP_PM_DEFECT D ON P.PRODUCTIONDATAID = D.PRODUCTIONDATAID INNER JOIN TP_MST_DEFECT M ON ( M.DEFECTID = D.DEFECTID AND M.DEFECTID > 0 ) LEFT JOIN TP_MST_DEFECTTYPE T ON T.DEFECTTYPEID = M.DEFECTTYPEID --LEFT JOIN TP_MST_USER U ON U.USERCODE = G.USERCODE INNER JOIN TP_MST_USER U ON U.USERID = D.DEFECTUSERID LEFT JOIN TP_MST_GOODS GS ON GS.GOODSID = P.GOODSID WHERE P.VALUEFLAG = '1' AND P.PROCEDUREID = 13 AND P.CHECKBATCHNO = 1 AND P.GOODSLEVELTYPEID <> 4 AND P.CHECKTIME >= @DATEBEGIN@ AND P.CHECKTIME <= @DATEEND@ AND P.ISREFIRE = '0' AND D.DEFECTPROCEDUREID = 25 GROUP BY GROUPING SETS ( ( MU.USERCODE,MU.USERNAME,U.USERCODE,U.USERNAME,GS.GOODSMODEL,D.DEFECTID,M.DEFECTNAME ), ( MU.USERCODE, MU.USERNAME, U.USERCODE, U.USERNAME, GS.GOODSMODEL ), ( MU.USERCODE, MU.USERNAME, D.DEFECTID, M.DEFECTNAME ), ( MU.USERCODE, MU.USERNAME ), ( D.DEFECTID, M.DEFECTNAME ), () ) ORDER BY MONITORCODE, USERCODE, GROUPING_ID ( MU.USERCODE, U.USERCODE, GS.GOODSMODEL, D.DEFECTID ) ) ) PIVOT ( SUM( CNUM ) FOR DEFECT IN ( '糙活' AS 糙活, '成裂' AS 成裂, '成脏' AS 成脏, '成走' AS 成走, '孔不良' AS 孔不良, '泥绺' AS 泥绺, '坯脏' AS 坯脏, '注泡' AS 注泡, '棕眼' AS 棕眼, '漏气' AS 漏气, '卡球' AS 卡球, '炸圈' AS 炸圈, '排水不良' AS 排水不良, '合计' AS 合计 ) ) ) PD RIGHT JOIN ( SELECT GID, DECODE( GID, 3, T.MONITORCODE || '[合计]',7,'ZZZ', T.MONITORCODE ) AS MONITORCODE, DECODE( GID, 3, T.组别 || '[合计]',7,'合计', T.组别 ) AS 组别, T.工号, T.姓名, T.产品型号, T.出窑数 FROM ( SELECT GROUPING_ID ( MU.USERCODE, U.USERCODE, GS.GOODSMODEL ) AS GID, NVL( MU.USERCODE, ' ' ) MONITORCODE, NVL( MU.USERNAME, '无' ) AS 组别, U.USERCODE AS 工号, U.USERNAME AS 姓名, GS.GOODSMODEL AS 产品型号, COUNT( * ) AS 出窑数 FROM TP_PM_PRODUCTIONDATA P INNER JOIN TP_PC_GROUTINGLINE L ON P.GROUTINGLINEID = L.GROUTINGLINEID INNER JOIN TP_PM_GROUTINGDAILY G ON G.GROUTINGDAILYID = P.GROUTINGDAILYID LEFT JOIN TP_MST_USER MU ON MU.USERID = L.MONITORID --LEFT JOIN TP_PM_DEFECT D ON P.PRODUCTIONDATAID = D.PRODUCTIONDATAID LEFT JOIN TP_PM_PRODUCTIONDATA PB ON P.BARCODE = PB.BARCODE AND PB.MODELTYPE = 5 LEFT JOIN TP_MST_USER U ON U.USERID = PB.USERID LEFT JOIN TP_MST_GOODS GS ON P.GOODSID = GS.GOODSID WHERE P.VALUEFLAG = '1' AND P.PROCEDUREID = 13 AND P.CHECKBATCHNO = 1 AND P.CHECKTIME >= @DATEBEGIN@ AND P.CHECKTIME <= @DATEEND@ AND PB.PROCEDUREID = 25 AND P.ISREFIRE = '0' AND PB.VALUEFLAG='1' GROUP BY GROUPING SETS ( ( MU.USERCODE, MU.USERNAME, U.USERCODE, U.USERNAME, GS.GOODSMODEL ), ( MU.USERCODE, MU.USERNAME ), () ) ) T ) PC ON PD.MONITORCODE = PC.MONITORCODE AND ( PC.GID = 3 OR PC.GID=7 OR ( PD.工号 = PC.工号 AND PD.产品型号 = PC.产品型号 ) ) "; List sqlPara2 = new List(); sqlPara2.Add(new CDAParameter("I", i)); sqlPara2.Add(new CDAParameter("YEAR", b)); sqlPara2.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara2.Add(new CDAParameter("DATEBEGIN", dayBegin)); result += conn.ExecuteNonQuery(sql3, sqlPara2.ToArray()); } conn.Commit(); if (result > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export012成型缺陷统计表结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export012成型缺陷统计表结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export012成型缺陷统计表结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export013() { using (IDataAccess conn = DataAccess.Create()) { try { Curtain.Log.Logger.Debug("export013 其他缺陷统计表结转开始！"); conn.Open(); conn.BeginTransaction(); //string t = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); //string z = DateTime.Now.AddMonths(-1).Year.ToString(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(a + b); String sqlStr = @"delete JZ_RPT013_22 where FORWARDTIME = @C@"; int result = 0; int m = DateTime.Now.AddMonths(-1).Month; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); //string dayBegin = null; //string dayEnd = null; DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); int[] procedUserIDs = { 3, 4, 5, 6, 7, 8, 9, 10, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 26, 27, 28, 29, 30, 31, 32, 33, 34, 36, 37, 38, 39, 40, 41, 42 }; for (int i = m; i <= m; i++) { //dayBegin = z+"-" + i.ToString().PadLeft(2, '0') + "-01"; //dayEnd = z+"-" + i.ToString().PadLeft(2, '0') + "-"+Convert.ToDateTime( dayBegin).AddMonths(1).AddDay(-1).Day; dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1).AddMilliseconds(-1); for (int p = 0; p < procedUserIDs.Length; p++) { for (int j = 0; j <= 2; j++) { String str = j + "#"; String sqlStr1 = @" INSERT INTO JZ_RPT013_22 SELECT * FROM ( SELECT PC.工号, PC.姓名, NVL( PC.产品型号, PD.产品型号复 ) AS 产品型号, PC.出窑数, PC.出窑数 - NVL( PD.合计, 0 ) AS 合格数, --ROUND( (PC.出窑数 - NVL( PD.合计, 0 )) / DECODE( PC.出窑数, 0, 1, PC.出窑数 ), 4 ) * 100 AS 合格率, TO_CHAR( 100 - ROUND( ( PC.出窑数 - NVL( PD.合计, 0 ) ) / DECODE( PC.出窑数, 0, 1, PC.出窑数 ), 4 ) * 100 ) || '%' AS 缺陷率, to_number(@YEAR@ || lpad(to_char(@I@),2,'0')), @BUILDINGNO@, @PROCEDUREID@, PD.* FROM ( SELECT * FROM ( SELECT--GID, DECODE( GID, 6, USERCODE || '合计', 7, USERCODE || '合计', USERCODE ) AS 工号复, USERNAME AS 姓名复, GOODSMODEL AS 产品型号复, DECODE( GID, 1, '合计', 3, '合计', 7, '合计', DEFECT ) AS DEFECT, CNUM FROM ( SELECT GROUPING_ID ( U.USERCODE, GS.GOODSMODEL, D.DEFECTID ) AS GID, U.USERCODE, U.USERNAME, GS.GOODSMODEL, D.DEFECTID, M.DEFECTNAME AS DEFECT, COUNT( * ) AS CNUM FROM TP_PM_PRODUCTIONDATA P INNER JOIN TP_PC_GROUTINGLINE L ON P.GROUTINGLINEID = L.GROUTINGLINEID INNER JOIN TP_PM_GROUTINGDAILY G ON G.GROUTINGDAILYID = P.GROUTINGDAILYID --LEFT JOIN TP_MST_USER MU ON MU.USERID = L.MONITORID INNER JOIN TP_PM_DEFECT D ON P.PRODUCTIONDATAID = D.PRODUCTIONDATAID INNER JOIN TP_MST_DEFECT M ON ( M.DEFECTID = D.DEFECTID AND M.DEFECTID > 0 ) LEFT JOIN TP_MST_DEFECTTYPE T ON T.DEFECTTYPEID = M.DEFECTTYPEID INNER JOIN TP_MST_USER U ON U.USERID = D.DEFECTUSERID LEFT JOIN TP_MST_GOODS GS ON GS.GOODSID = P.GOODSID WHERE P.VALUEFLAG = '1' AND P.PROCEDUREID = @CHECKPROCEDUREID@ AND P.CHECKBATCHNO = 1 AND P.GOODSLEVELTYPEID <> 4 AND P.CHECKTIME >= @DATEBEGIN@ AND P.CHECKTIME <= @DATEEND@ --AND P.CHECKTIME >= to_date(@DATEBEGIN@,'yyyy-mm-dd') --AND P.CHECKTIME <= to_date(@DATEEND@,'yyyy-mm-dd') AND P.ISREFIRE = @ISREFIRE@ AND ( L.BUILDINGNO = @BUILDINGNO@ OR @BUILDINGNO@ IS NULL ) AND ( D.DEFECTPROCEDUREID = @PROCEDUREID@ OR @PROCEDUREID@ IS NULL ) AND ( GS.GOODSMODEL = '' OR '' IS NULL ) AND ( U.USERCODE = '' OR '' IS NULL ) GROUP BY GROUPING SETS ( ( U.USERCODE, U.USERNAME, GS.GOODSMODEL, D.DEFECTID, M.DEFECTNAME ), ( U.USERCODE, U.USERNAME, GS.GOODSMODEL ), ( U.USERCODE, U.USERNAME, D.DEFECTID, M.DEFECTNAME ), ( U.USERCODE, U.USERNAME ), ( D.DEFECTID, M.DEFECTNAME ), ( ) ) ) ) PIVOT ( SUM( CNUM ) FOR DEFECT IN ('桔釉' AS 桔釉, '划釉' AS 划釉, '出磕' AS 出磕, '烧裂' AS 烧裂, '烧生' AS 烧生, '冷补' AS 冷补, '烧炸' AS 烧炸, '眼' AS 眼, '桔柚' AS 桔柚, '底' AS 底, '过火泡' AS 过火泡, '底和面' AS 底和面, '点釉' AS 点釉, '针孔' AS 针孔, '补不良' AS 补不良, '麻坑' AS 麻坑, '窑脏' AS 窑脏, '磕划' AS 磕划, '烟熏' AS 烟熏, '漏气' AS 漏气, '成检漏验' AS 成检漏验, '风惊' AS 风惊, '铁脏' AS 铁脏, '研磨不良' AS 研磨不良1, '铜脏' AS 铜脏, '磕划' AS 磕划1, '坯磕' AS 坯磕, '研磨不良' AS 研磨不良, '研磨' AS 研磨, '成检漏验' AS 成检漏验1, '崩脏' AS 崩脏, '水滴' AS 水滴, '复烧修补不良' AS 复烧修补不良, 'B级品' AS B级品, '面' AS 面, '边' AS 边, '研磨配盖' AS 研磨配盖, '糙活' AS 糙活, '成裂' AS 成裂, '成脏' AS 成脏, '成走' AS 成走, '孔不良' AS 孔不良, '泥绺' AS 泥绺, '坯脏' AS 坯脏, '注泡' AS 注泡, '棕眼' AS 棕眼, '漏气' AS 漏气1, '卡球' AS 卡球, '炸圈' AS 炸圈, '排水不良' AS 排水不良, '爆釉' AS 爆釉, '标不良' AS 标不良, '脱釉' AS 脱釉, '釉薄' AS 釉薄, '釉磕' AS 釉磕, '釉绺' AS 釉绺, '釉脏' AS 釉脏, '釉粘' AS 釉粘, '波纹' AS 波纹, '釉泡' AS 釉泡, '氯化镁' AS 氯化镁, '灌釉不良' AS 灌釉不良, '蹭釉' AS 蹭釉, '落脏' AS 落脏, '装磕' AS 装磕, '装脏' AS 装脏, '装粘' AS 装粘, '装走' AS 装走, '合计' AS 合计 ) ) ) PD RIGHT JOIN ( SELECT GID, DECODE( GID, 3, '合计',工号 ) AS 工号, T.姓名, T.产品型号, T.出窑数 FROM ( SELECT GROUPING_ID ( U.USERCODE, GS.GOODSMODEL ) AS GID, U.USERCODE AS 工号, U.USERNAME AS 姓名, GS.GOODSMODEL AS 产品型号, COUNT( * ) AS 出窑数 FROM TP_PM_PRODUCTIONDATA P INNER JOIN TP_PC_GROUTINGLINE L ON P.GROUTINGLINEID = L.GROUTINGLINEID INNER JOIN TP_PM_GROUTINGDAILY G ON G.GROUTINGDAILYID = P.GROUTINGDAILYID --LEFT JOIN TP_MST_USER MU ON MU.USERID = L.MONITORID INNER JOIN TP_PM_PRODUCTIONDATA PB ON P.BARCODE = PB.BARCODE INNER JOIN TP_MST_USER U ON U.USERID = PB.USERID LEFT JOIN TP_MST_GOODS GS ON GS.GOODSID = P.GOODSID WHERE P.VALUEFLAG = '1' AND P.PROCEDUREID = @CHECKPROCEDUREID@ AND P.CHECKBATCHNO = 1 AND P.CHECKTIME >= @DATEBEGIN@ AND P.CHECKTIME <= @DATEEND@ --AND P.CHECKTIME >= to_date(@DATEBEGIN@,'yyyy-mm-dd') --AND P.CHECKTIME <= to_date(@DATEEND@,'yyyy-mm-dd') AND ( L.BUILDINGNO = @BUILDINGNO@ OR @BUILDINGNO@ IS NULL ) AND ( @PROCEDUREID@ IS NULL OR PB.PROCEDUREID = @PROCEDUREID@ ) AND PB.VALUEFLAG = '1' AND ( GS.GOODSMODEL = '' OR '' IS NULL ) AND ( U.USERCODE = '' OR '' IS NULL ) GROUP BY GROUPING SETS ( ( U.USERCODE, U.USERNAME, GS.GOODSMODEL ), ( U.USERCODE, U.USERNAME ), ( ) ) ) T ) PC ON ( ( PC.GID = 3 OR PC.GID = 1 ) AND PD.工号复 = PC.工号 AND PD.产品型号复 IS NULL ) OR ( PD.工号复 = PC.工号 AND PD.产品型号复 = PC.产品型号 ) ) WHERE 产品型号 IS NOT NULL "; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); sqlPara.Add(new CDAParameter("BUILDINGNO", str)); sqlPara.Add(new CDAParameter("PROCEDUREID", procedUserIDs[p])); sqlPara.Add(new CDAParameter("CHECKPROCEDUREID", (procedUserIDs[p] == 19 || procedUserIDs[p] == 36) ? 35 : 13)); sqlPara.Add(new CDAParameter("ISREFIRE", (procedUserIDs[p] == 19 || procedUserIDs[p] == 36) ? 6 : 0)); result += conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); } } } conn.Commit(); if (result > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export013其他缺陷统计表结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export013其他缺陷统计表结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export013其他缺陷统计表结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export014() { using (IDataAccess conn = DataAccess.Create()) { try { Curtain.Log.Logger.Debug("export014 每窑质量总缺陷统计表结转开始！"); conn.Open(); conn.BeginTransaction(); string t = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string z = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(z + t); //2021-07-12 dongyan 修正什么问题 String sqlStr = @"delete JZ_RPT014 WHERE FORWARDTIME = @C@"; int result = 0; int m = DateTime.Now.AddMonths(-1).Month; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); String[] arr = { "13", "35" }; for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(z + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1).AddDays(-1); //for (int a = 0; a < arr.Length; a++) //{ // String s = arr[a]; // for (int j = 0; j <= 2; j++) // { // String str = j + "#"; // for (int b = 0; b <= 4; b++) // { // String br = b.ToString(); String sqlStr1 = @" INSERT INTO JZ_RPT014 SELECT to_number(@YEAR@ || lpad(to_char(@I@),2,'0')), GOODSTYPENAME, GOODSNAME, OUT_K, COUNT_U4, COUNT_AB, COUNT_AA, COUNT_B, COUNT_R, LB_NUM, COUNT_L, COUNT_U7, COUNT_Z, D_GID, D_GOODSTYPECODE, D_GOODSCODE, CNUM1, CNUM2, CNUM3, CNUM4, CNUM5, CNUM6, CNUM7, CNUM8, CNUM9, CNUM10, CNUM11, CNUM12, CNUM13, CNUM14, CNUM15, CNUM16, CNUM17, CNUM18, CNUM19, CNUM20, CNUM21, CNUM22, CNUM23, CNUM24, CNUM25, CNUM26, CNUM27, CNUM28, CNUM29, CNUM30, CNUM31, CNUM32, CNUM33, CNUM34, CNUM35, CNUM36, CNUM37, CNUM38, CNUM39, CNUM40, CNUM41, CNUM42, CNUM43, CNUM44, CNUM45, CNUM46, CNUM47, CNUM48, CNUM49, CNUM50, CNUM51, CNUM52, CNUM53, CNUM54, CNUM55, CNUM56, CNUM57, CNUM58, CNUM59, CNUM60, CNUM61, CNUM62, CNUM63, CNUM64, CNUM65, CNUM66, CNUM67, CNUM68, CNUM69, CNUM70, CNUM71, CNUM72, CNUM73, -- decode(BUILDINGNO,NULL,@BUILDINGNO@,BUILDINGNO) AS BUILDINGNO, '0' AS BUILDINGNO, -- decode(PROCEDUREID,NULL,@PROCEDUREID@,PROCEDUREID) AS PROCEDUREID, '0' AS PROCEDUREID, GOOD, '0' KILNID, COUNT_UA FROM ( SELECT DECODE( TA.GID, 3, '合计', TA.GOODSTYPENAME ) AS GOODSTYPENAME, TA.GOODSNAME AS GOODSNAME, TA.BUILDINGNO AS BUILDINGNO, TA.PROCEDUREID AS PROCEDUREID, DECODE( TA.OUT_K, 0, NULL, TA.OUT_K ) AS OUT_K, DECODE( TA.COUNT_U4, 0, NULL, TA.COUNT_U4 ) AS COUNT_U4, DECODE( TA.COUNT_U5 + TA.COUNT_AB, 0, NULL, TA.COUNT_U5 + TA.COUNT_AB ) COUNT_AB, DECODE( TA.COUNT_AA, 0, NULL, TA.COUNT_AA ) AS COUNT_AA, DECODE( TA.COUNT_B, 0, NULL, TA.COUNT_B ) AS COUNT_B, DECODE( TA.COUNT_R, 0, NULL, TA.COUNT_R ) AS COUNT_R, DECODE( TA.LB_NUM, 0, NULL, TA.LB_NUM ) AS LB_NUM, DECODE( TA.COUNT_L, 0, NULL, TA.COUNT_L ) AS COUNT_L, DECODE( TA.COUNT_U7 - TA.COUNT_B + TA.COUNT_H + TA.COUNT_F, 0, NULL, TA.COUNT_U7 - TA.COUNT_B + TA.COUNT_H + TA.COUNT_F ) AS COUNT_U7, DECODE( TA.COUNT_Z, 0, NULL, TA.COUNT_Z ) AS COUNT_Z, CASE WHEN TA.OUT_K IS NULL OR TA.OUT_K = 0 THEN NULL ELSE ROUND( ( TA.COUNT_U4 + TA.COUNT_U5 + TA.COUNT_AB + TA.COUNT_AA ) / TA.OUT_K, 4 ) * 100 END AS COUNT_UA, TD.* ,DECODE (GOODSTYPENAME,'妇洗'，'1','座','2','挂座','3','柱','4','盆','5','箱','6','连体','7' ,'8') AS GOOD FROM ( SELECT GROUPING_ID ( GT.GOODSTYPECODE, G.GOODSCODE ) GID, GT.GOODSTYPECODE, GT.GOODSTYPENAME, G.GOODSCODE, G.GOODSNAME, SUM(LB_NUM) LB_NUM, T.BUILDINGNO, T.PROCEDUREID, SUM( T.OUT_K ) OUT_K, SUM( CASE WHEN T.H_FLAG = 0 AND T.GOODSLEVELTYPEID = 4 THEN 1 ELSE 0 END ) COUNT_U4, SUM( CASE WHEN T.H_FLAG = 0 AND T.GOODSLEVELTYPEID = 5 THEN 1 ELSE 0 END ) COUNT_U5, SUM( CASE WHEN T.H_FLAG = 0 AND T.GOODSLEVELTYPEID = 7 THEN 1 ELSE 0 END ) COUNT_U7, SUM( CASE WHEN T.H_FLAG = 0 AND T.GOODSLEVELTYPEID = 6 AND T.REWORKPROCEDUREID = 19 THEN 1 ELSE 0 END ) AS COUNT_R, SUM( CASE WHEN T.H_FLAG = 0 AND T.GOODSLEVELTYPEID = 6 AND T.REWORKPROCEDUREID = 36 THEN 1 ELSE 0 END ) AS COUNT_Z, SUM( T.B_NUM ) AS COUNT_B, SUM( CASE WHEN T.H_FLAG = 1 THEN 1 ELSE 0 END ) AS COUNT_H, SUM( CASE WHEN RPD.PROCEDUREID IN ( 22, 23, 37, 20, 41 ) THEN - 1 ELSE 0 END ) COUNT_L, SUM( CASE WHEN T.GOODSLEVELTYPEID IN ( 4, 5 ) AND RPD.PROCEDUREID IN ( 22, 23 ) THEN 1 ELSE 0 END ) COUNT_AA, SUM( CASE WHEN T.GOODSLEVELTYPEID IN ( 4, 5 ) AND RPD.PROCEDUREID IN ( 37, 20, 41 ) THEN 1 ELSE 0 END ) COUNT_AB, SUM( CASE WHEN T.GOODSLEVELTYPEID NOT IN ( 4, 5 ) AND RPD.PROCEDUREID IN ( 22, 23, 37, 20, 41 ) THEN 1 ELSE 0 END ) COUNT_F FROM ( SELECT PD.PRODUCTIONDATAID, NULL MPDID, 0 GOODSLEVELTYPEID, PD.GOODSID, 0 REWORKPROCEDUREID, DECODE(DL.PRODUCTIONDATAID,null,0,1) LB_NUM, 0 B_NUM, 1 OUT_K, -1 H_FLAG, PD.PROCEDUREID, GL.BUILDINGNO FROM TP_PM_PRODUCTIONDATA PD INNER JOIN TP_PC_GROUTINGLINE GL ON GL.GROUTINGLINEID = PD.GROUTINGLINEID LEFT JOIN TP_PM_DEFECT DL ON DL.PRODUCTIONDATAID = PD.PRODUCTIONDATAID AND DL.DEFECTID = 67 AND DL.VALUEFLAG = '1' WHERE -- (PD.PROCEDUREID = @PROCEDUREID@) -- AND PD.CHECKFLAG = 1 AND PD.CREATETIME >=@DATEBEGIN@ AND PD.CREATETIME <= @DATEEND@ -- AND (PD.KILNID =@KILNID@ OR @KILNID@ IS NULL) AND (INSTR((NULL),PD.GOODSCODE)>0 OR NULL IS NULL) -- AND (GL.BUILDINGNO = @BUILDINGNO@ ) UNION ALL SELECT PD.PRODUCTIONDATAID, MAX( PMAX.PRODUCTIONDATAID ) MPDID, PD.GOODSLEVELTYPEID, PD.GOODSID, PD.REWORKPROCEDUREID, 0 LB_NUM, CASE WHEN PD.GOODSLEVELTYPEID = 7 AND sum( D.PRODUCTIONDEFECTID ) > 0 THEN 1 ELSE 0 END B_NUM, 0 OUT_K, CASE WHEN sum( PMAX.PRODUCTIONDATAID ) > 0 THEN 1 ELSE 0 END H_FLAG, PD.PROCEDUREID, GL.BUILDINGNO FROM TP_PM_PRODUCTIONDATA PD LEFT JOIN TP_PM_PRODUCTIONDATA PMAX ON PMAX.GROUTINGDAILYDETAILID = PD.GROUTINGDAILYDETAILID AND PMAX.PRODUCTIONDATAID < PD.PRODUCTIONDATAID AND PMAX.VALUEFLAG = '1' AND PMAX.PROCEDUREMODEL = '1' AND PMAX.PROCEDUREID IN (20, 22, 23, 37, 41) LEFT JOIN TP_PM_DEFECT D ON D.PRODUCTIONDATAID = PD.PRODUCTIONDATAID AND D.DEFECTID = 92 AND D.VALUEFLAG = '1' INNER JOIN TP_PC_GROUTINGLINE GL ON GL.GROUTINGLINEID = PD.GROUTINGLINEID WHERE PD.VALUEFLAG = '1' -- AND (PD.PROCEDUREID = @PROCEDUREID@ ) AND PD.CHECKBATCHNO = 1 AND PD.CREATETIME >= @DATEBEGIN@ AND PD.CREATETIME <= @DATEEND@ -- AND (PD.KILNID = @KILNID@ OR @KILNID@ IS NULL) AND (INSTR((NULL),PD.GOODSCODE)>0 OR NULL IS NULL) -- AND (GL.BUILDINGNO = @BUILDINGNO@ ) GROUP BY PD.PRODUCTIONDATAID, PD.GOODSLEVELTYPEID, PD.GOODSID, PD.REWORKPROCEDUREID, PD.PROCEDUREID, GL.BUILDINGNO ) T LEFT JOIN TP_PM_PRODUCTIONDATA RPD ON RPD.PRODUCTIONDATAID = T.MPDID INNER JOIN TP_MST_GOODS G ON G.GOODSID = T.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID GROUP BY GROUPING SETS ( ( GT.GOODSTYPECODE, GT.GOODSTYPENAME, G.GOODSCODE, G.GOODSNAME,T.BUILDINGNO,T.PROCEDUREID ), ( GT.GOODSTYPECODE, GT.GOODSTYPENAME ), ( ) ) ) TA LEFT JOIN ( SELECT * FROM ( SELECT CASE WHEN TT.GID IN ( 12, 13, 15 ) THEN 3 WHEN TT.GID IN ( 4, 5, 7 ) THEN 1 ELSE 0 END AS D_GID, TT.GOODSTYPECODE D_GOODSTYPECODE, TT.GOODSCODE D_GOODSCODE, CASE WHEN TT.GID IN ( 1, 5, 13 ) THEN TT.DEFECT || '合计' WHEN TT.GID IN ( 3, 7, 15 ) THEN '合计' ELSE TT.DEFECT END AS DEFECT, TT.CNUM FROM ( SELECT GROUPING_ID ( GT.GOODSTYPECODE, G.GOODSCODE, PDP.DEFECTGROUPNAME, M.DEFECTNAME ) AS GID, GT.GOODSTYPECODE, GT.GOODSTYPENAME, G.GOODSCODE, PDP.DEFECTGROUPNAME, TO_CHAR( PDP.DEFECTGROUPNAME || '_' || M.DEFECTNAME ) AS DEFECT, SUM(CASE WHEN D.DEFECTID = 11 AND (P.GOODSLEVELTYPEID = 4 OR P.GOODSLEVELTYPEID = 5) THEN 0 ELSE 1 END) AS CNUM 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 L ON P.GROUTINGLINEID = L.GROUTINGLINEID INNER JOIN TP_PM_DEFECT D ON P.PRODUCTIONDATAID = D.PRODUCTIONDATAID INNER JOIN TP_MST_DEFECT M ON ( M.DEFECTID = D.DEFECTID AND M.DEFECTID > 0 ) LEFT JOIN TP_PC_PROCEDURE PR ON PR.PROCEDUREID = D.DEFECTPROCEDUREID INNER JOIN TP_MST_DEFECTGROUP PDP ON PDP.DEFECTID = D.DEFECTID AND PDP.DEFECTGROUPTYPE LIKE '每窑%' AND ( ( PDP.PROCEDUREID1 = PR.PROCEDUREID OR PDP.PROCEDUREID2 = PR.PROCEDUREID ) OR '' = 35 ) WHERE P.VALUEFLAG = '1' -- AND (P.PROCEDUREID = @PROCEDUREID@ ) AND P.CHECKBATCHNO = 1 AND P.CREATETIME >= @DATEBEGIN@ AND P.CREATETIME <= @DATEEND@ AND P.GOODSLEVELTYPEID <> 4 -- AND (P.KILNID =@KILNID@ OR @KILNID@ IS NULL) AND (INSTR((NULL),P.GOODSCODE)>0 OR NULL IS NULL) -- AND (L.BUILDINGNO = @BUILDINGNO@ ) GROUP BY GROUPING SETS ( ( GT.GOODSTYPECODE, GT.GOODSTYPENAME, G.GOODSCODE, PDP.DEFECTGROUPNAME, M.DEFECTNAME ), ( GT.GOODSTYPECODE, GT.GOODSTYPENAME, G.GOODSCODE, PDP.DEFECTGROUPNAME ), ( GT.GOODSTYPECODE, GT.GOODSTYPENAME, G.GOODSCODE ), ( GT.GOODSTYPECODE, GT.GOODSTYPENAME, PDP.DEFECTGROUPNAME, M.DEFECTNAME ), ( GT.GOODSTYPECODE, GT.GOODSTYPENAME, PDP.DEFECTGROUPNAME ), ( GT.GOODSTYPECODE, GT.GOODSTYPENAME ), ( PDP.DEFECTGROUPNAME, M.DEFECTNAME ), ( PDP.DEFECTGROUPNAME ), ( ) ) ) TT ) PIVOT ( SUM( CNUM ) FOR DEFECT IN ('原料缺陷_铁脏' AS CNUM1, '原料缺陷_铜脏' AS CNUM2, '原料缺陷_合计' AS CNUM3, '成型缺陷_糙活' AS CNUM4, '成型缺陷_成裂' AS CNUM5, '成型缺陷_成脏' AS CNUM6, '成型缺陷_成走' AS CNUM7, '成型缺陷_孔不良' AS CNUM8, '成型缺陷_漏气' AS CNUM9, '成型缺陷_泥绺' AS CNUM10, '成型缺陷_排水不良' AS CNUM11, '成型缺陷_坯脏' AS CNUM12, '成型缺陷_注泡' AS CNUM13, '成型缺陷_棕眼' AS CNUM14, '成型缺陷_炸圈' AS CNUM15, '成型缺陷_卡球' AS CNUM16, '成型缺陷_合计' AS CNUM17, '施釉缺陷_爆釉' AS CNUM18, '施釉缺陷_标不良' AS CNUM19, '施釉缺陷_脱釉' AS CNUM20, '施釉缺陷_釉薄' AS CNUM21, '施釉缺陷_釉磕' AS CNUM22, '施釉缺陷_釉绺' AS CNUM23, '施釉缺陷_釉脏' AS CNUM24, '施釉缺陷_釉粘' AS CNUM25, '施釉缺陷_釉泡' AS CNUM26, '施釉缺陷_波纹' AS CNUM27, '施釉缺陷_合计' AS CNUM28, '循环线施釉缺陷_爆釉' AS CNUM29, '循环线施釉缺陷_标不良' AS CNUM30, '循环线施釉缺陷_脱釉' AS CNUM31, '循环线施釉缺陷_釉薄' AS CNUM32, '循环线施釉缺陷_釉磕' AS CNUM33, '循环线施釉缺陷_釉绺' AS CNUM34, '循环线施釉缺陷_釉脏' AS CNUM35, '循环线施釉缺陷_釉粘' AS CNUM36, '循环线施釉缺陷_釉泡' AS CNUM37, '循环线施釉缺陷_波纹' AS CNUM38, '循环线施釉缺陷_合计' AS CNUM39, '出装窑缺陷_蹭釉' AS CNUM40, '出装窑缺陷_出磕' AS CNUM41, '出装窑缺陷_划釉' AS CNUM42, '出装窑缺陷_落脏' AS CNUM43, '出装窑缺陷_装磕' AS CNUM44, '出装窑缺陷_装粘' AS CNUM45, '出装窑缺陷_装脏' AS CNUM46, '出装窑缺陷_装走' AS CNUM47, '出装窑缺陷_合计' AS CNUM48, '烧窑缺陷_过火泡' AS CNUM49, '烧窑缺陷_桔釉' AS CNUM50, '烧窑缺陷_麻坑' AS CNUM51, '烧窑缺陷_烧裂' AS CNUM52, '烧窑缺陷_烧生' AS CNUM53, '烧窑缺陷_窑脏' AS CNUM54, '烧窑缺陷_烧炸' AS CNUM55, '烧窑缺陷_烟熏' AS CNUM56, '烧窑缺陷_水滴' AS CNUM57, '烧窑缺陷_风惊' AS CNUM58, '烧窑缺陷_针孔' AS CNUM59, '烧窑缺陷_合计' AS CNUM60, '其他缺陷_崩脏' AS CNUM61, '其他缺陷_坯磕' AS CNUM62, '其他缺陷_研磨' AS CNUM63, '其他缺陷_复烧修补不良' AS CNUM64, '其他缺陷_合计' AS CNUM65, '研磨缺陷_眼' AS CNUM66, '研磨缺陷_底' AS CNUM67, '研磨缺陷_底和面' AS CNUM68, '研磨缺陷_面' AS CNUM69, '研磨缺陷_边' AS CNUM70, '研磨缺陷_研磨配盖' AS CNUM71, '研磨缺陷_合计' AS CNUM72, '合计' AS CNUM73 ) ) ) TD ON TA.GID = TD.D_GID AND ( ( TA.GID = 0 AND TA.GOODSTYPECODE = TD.D_GOODSTYPECODE AND TA.GOODSCODE = TD.D_GOODSCODE ) OR ( TA.GID = 1 AND TA.GOODSTYPECODE = TD.D_GOODSTYPECODE ) OR ( TA.GID = 3 ) ) ) "; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", z)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); //sqlPara.Add(new CDAParameter("BUILDINGNO", str)); //sqlPara.Add(new CDAParameter("KILNID", br)); //sqlPara.Add(new CDAParameter("PROCEDUREID", s)); result += conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); // } //} // } } conn.Commit(); if (result > 0) { Curtain.Log.Logger.Debug(z + "年" + t + "月:export014每窑质量总缺陷统计表结转成功！"); } else { Curtain.Log.Logger.Debug(z + "年" + t + "月:export014每窑质量总缺陷统计表结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export014每窑质量总缺陷统计表结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export015() { using (IDataAccess conn = DataAccess.Create()) { try { Curtain.Log.Logger.Debug("export015 成型废品统计表结转开始！"); conn.Open(); conn.BeginTransaction(); //2021-07-12 dongyan 修正什么问题 int result = 0; int m = DateTime.Now.AddMonths(-1).Month; DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); string sqlStr = @"delete JZ_RPT015 WHERE FORWARD_TIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1).AddMilliseconds(-1); string sqlStr1 = @" INSERT INTO JZ_RPT015 SELECT * FROM (SELECT DECODE(GROUPING_ID(MU.USERCODE, U.USERCODE, P.GOODSCODE) ,7 ,'合计' ,MU.USERCODE) AS 组号 ,MU.USERNAME AS 组名 ,U.USERCODE||decode(PP.USERCODE,null,null,'['|| PP.USERCODE ||']') AS 工号 ,U.USERNAME||decode(PP.USERCODE,null,null,'['|| PU.USERNAME ||']') AS 姓名 ,P.GOODSCODE AS 产品编码 ,COUNT(*) AS 废品数, '0' as BUILDINGNO, '0' as DEFECTPROCEDUREID, to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM TP_PM_PRODUCTIONDATA P LEFT JOIN TP_PM_PRODUCTIONDATA PP ON PP.groutingdailydetailid = P.groutingdailydetailid AND PP.procedureID = 1 LEFT JOIN TP_MST_USER PU ON PU.USERID = PP.USERID INNER JOIN TP_MST_USER U ON U.USERID = P.GROUTINGUSERID INNER JOIN TP_PC_GROUTINGLINE L ON P.GROUTINGLINEID = L.GROUTINGLINEID LEFT JOIN TP_MST_USER MU ON MU.USERID = L.MONITORID WHERE P.VALUEFLAG = '1' AND P.CHECKBATCHNO = 1 AND P.PROCEDUREID = 13 AND P.GOODSLEVELTYPEID <> 4 AND P.CHECKTIME >= @DATEBEGIN@ AND P.CHECKTIME <= @DATEEND@ AND P.ISREFIRE = '0' AND EXISTS (SELECT D.DEFECTID FROM TP_PM_DEFECT D INNER JOIN TP_MST_DEFECTFINE F ON D.DEFECTFINE = F.DEFECTFINEID WHERE P.PRODUCTIONDATAID = D.PRODUCTIONDATAID AND D.DEFECTID = 1 AND F.DEFECTFINECODE = '1.5' AND D.DEFECTUSERID = P.GROUTINGUSERID ) GROUP BY GROUPING SETS((MU.USERCODE, MU.USERNAME, U.USERCODE, U.USERNAME, PP.USERCODE, PU.USERNAME, P.GOODSCODE),(MU.USERCODE, MU.USERNAME),()) ORDER BY 组号, 工号, GROUPING_ID(MU.USERCODE, U.USERCODE, PP.USERCODE, P.GOODSCODE) ) WHERE 产品编码 IS NOT NULL "; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result = conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); } conn.Commit(); if (result > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export015成型废品统计表结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export015成型废品统计表结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export015成型废品统计表结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export016() { using (IDataAccess conn = DataAccess.Create()) { //2021-08-2 dongyan 修正什么问题 Curtain.Log.Logger.Debug("export016 回收合格汇总表结转开始！"); int result = 0; int result1 = 0; int result2 = 0; int result3 = 0; int m = DateTime.Now.AddMonths(-1).Month; DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); conn.Open(); conn.BeginTransaction(); try { string sqlStr = @"delete AJZ_RPT016_YM WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); string Str = @"delete AJZ_RPT016_QT WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(Str, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1); string sqlStr1 = @" INSERT INTO AJZ_RPT016_YM SELECT * FROM(SELECT UPD.USERCODE AS 工号, CDU.STAFFNAME AS 姓名, J.JOBSNAME AS 工种, G.GOODSMODEL AS 产品型号, RU.USERCODE AS 生产工号, RU.USERNAME AS 生产姓名, COUNT( DISTINCT TT.UPDID ) AS 回收检验数, COUNT( DISTINCT TT.UPDID ) - COUNT( DISTINCT TT.dnum ) AS 回收合格数, SUM( DECODE( TT.DEFECTID, 68, 1, NULL ) ) AS 研磨缺陷_眼, SUM( DECODE( TT.DEFECTID, 69, 1, NULL ) ) AS 研磨缺陷_底, SUM( DECODE( TT.DEFECTID, 70, 1, NULL ) ) AS 研磨缺陷_底和面, SUM( DECODE( TT.DEFECTID, 94, 1, NULL ) ) AS 研磨缺陷_面, SUM( DECODE( TT.DEFECTID, 95, 1, NULL ) ) AS 研磨缺陷_边, SUM( DECODE( TT.DEFECTID, 96, 1, NULL ) ) AS 研磨缺陷_研磨配盖, '0' as JOBSID, '0' as BUILDINGNO, to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM (SELECT T.PRODUCTIONDATAID, D.DEFECTID, ( select case when count(d.PRODUCTIONDEFECTID) > 0 then T.PRODUCTIONDATAID else null end dnum from TP_PM_DEFECT D WHERE D.PRODUCTIONDATAID = T.PRODUCTIONDATAID AND D.DEFECTID = 78) dnum, MIN(CASE WHEN T.PRODUCTIONDATAID < PD.PRODUCTIONDATAID THEN PD.PRODUCTIONDATAID ELSE NULL END) NEXTPDID, MAX(CASE WHEN T.PRODUCTIONDATAID > PD.PRODUCTIONDATAID AND PD.MODELTYPE <> -1 THEN PD.PRODUCTIONDATAID ELSE 0 END) REPDID, MAX(CASE WHEN T.PRODUCTIONDATAID >= PD.PRODUCTIONDATAID AND PD.CHECKFLAG = 3 THEN PD.PRODUCTIONDATAID ELSE 0 END) UPDID FROM TP_PM_PRODUCTIONDATA T LEFT JOIN TP_PM_DEFECT D ON D.PRODUCTIONDATAID = T.PRODUCTIONDATAID AND D.DEFECTID IN (68, 69, 70, 94, 95, 96) INNER JOIN TP_PM_PRODUCTIONDATA PD ON T.BARCODE = PD.BARCODE AND T.KILNCARBATCHNO = PD.KILNCARBATCHNO AND (PD.MODELTYPE = -1 OR PD.VALUEFLAG = '1') WHERE T.CREATETIME >= @DATEBEGIN@ AND T.CREATETIME < @DATEEND@ AND T.MODELTYPE = -1 GROUP BY T.PRODUCTIONDATAID, T.GOODSLEVELTYPEID ,D.DEFECTID) TT LEFT JOIN TP_PM_PRODUCTIONDATA NPD ON NPD.PRODUCTIONDATAID = TT.NEXTPDID INNER JOIN TP_PM_PRODUCTIONDATA RPD ON RPD.PRODUCTIONDATAID = TT.REPDID INNER JOIN TP_MST_USER RU ON RU.USERID = (case when RPD.PROCEDUREID = 23 then--生产工号姓名 FUN_RPT_GetPrevPD_USERID_ALL(RPD.PRODUCTIONDATAID, RPD.barcode) else RPD.USERID end) INNER JOIN TP_PM_PRODUCTIONDATA UPD ON UPD.PRODUCTIONDATAID = TT.UPDID --工号 INNER JOIN TP_PC_GROUTINGLINE GL ON GL.GROUTINGLINEID = RPD.GROUTINGLINEID --楼号查询提哦啊见 INNER JOIN TP_MST_GOODS G ON RPD.GOODSID = G.GOODSID --产品型号 LEFT JOIN TP_PC_CLASSESDETAIL CD ON UPD.CLASSESSETTINGID =CD.CLASSESSETTINGID LEFT JOIN TP_MST_JOBS J ON J.JOBSID = CD.UJOBSID --工种 LEFT JOIN TP_HR_STAFF CDU ON CDU.STAFFID = CD.STAFFID --姓名 -- LEFT JOIN TP_PM_DEFECT D ON D.PRODUCTIONDATAID = TT.PRODUCTIONDATAID -- AND TT.dnum IS NULL -- AND D.DEFECTID IN (68, 69, 70, 94, 95, 96) WHERE (NPD.PRODUCTIONDATAID IS NULL OR NPD.MODELTYPE > -1) AND RPD.PROCEDUREID IN (20, 23) GROUP BY GROUPING SETS((UPD.USERCODE, CDU.STAFFNAME, G.GOODSMODEL, RU.USERCODE, RU.USERNAME, J.JOBSNAME),(UPD.USERCODE, CDU.STAFFNAME, J.JOBSNAME)) ORDER BY UPD.USERCODE, CDU.STAFFNAME, J.JOBSNAME, G.GOODSMODEL )D WHERE D.产品型号 is not null"; string sqlStr2 = @"INSERT INTO AJZ_RPT016_QT SELECT * FROM (SELECT UPD.USERCODE AS 工号, CDU.STAFFNAME AS 姓名, J.JOBSNAME AS 工种, G.GOODSMODEL AS 产品型号, RU.USERCODE AS 生产工号, RU.USERNAME AS 生产姓名, COUNT( DISTINCT TT.UPDID ) AS 回收检验数, COUNT( DISTINCT TT.UPDID )-SUM( dnum ) AS 回收合格数, '0' as JOBSID, '0' as BUILDINGNO, '1', to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM ( SELECT T.PRODUCTIONDATAID, (select case when count(d.PRODUCTIONDEFECTID) > 0 then 1 else 0 end dnum from TP_PM_DEFECT D WHERE D.PRODUCTIONDATAID = T.PRODUCTIONDATAID AND D.DEFECTID = 72) dnum, MIN( CASE WHEN T.PRODUCTIONDATAID < PD.PRODUCTIONDATAID THEN PD.PRODUCTIONDATAID ELSE NULL END ) NEXTPDID, MAX( CASE WHEN T.PRODUCTIONDATAID > PD.PRODUCTIONDATAID AND PD.MODELTYPE <> -1 THEN PD.PRODUCTIONDATAID ELSE 0 END ) REPDID, MAX( CASE WHEN T.PRODUCTIONDATAID >= PD.PRODUCTIONDATAID AND PD.CHECKFLAG = 3 THEN PD.PRODUCTIONDATAID ELSE 0 END ) UPDID FROM TP_PM_PRODUCTIONDATA T INNER JOIN TP_PM_PRODUCTIONDATA PD ON T.BARCODE = PD.BARCODE AND T.KILNCARBATCHNO = PD.KILNCARBATCHNO AND (PD.MODELTYPE = -1 OR PD.VALUEFLAG='1') WHERE T.CREATETIME >= @DATEBEGIN@ AND T.CREATETIME < @DATEEND@ AND T.MODELTYPE = -1 GROUP BY T.PRODUCTIONDATAID ) TT LEFT JOIN TP_PM_PRODUCTIONDATA NPD ON NPD.PRODUCTIONDATAID = TT.NEXTPDID INNER JOIN TP_PM_PRODUCTIONDATA RPD ON RPD.PRODUCTIONDATAID = TT.REPDID INNER JOIN TP_MST_USER RU ON RU.USERID = RPD.USERID INNER JOIN TP_PM_PRODUCTIONDATA UPD ON UPD.PRODUCTIONDATAID = TT.UPDID INNER JOIN TP_PC_GROUTINGLINE GL ON GL.GROUTINGLINEID = RPD.GROUTINGLINEID INNER JOIN TP_MST_GOODS G ON RPD.GOODSID = G.GOODSID LEFT JOIN TP_PC_CLASSESDETAIL CD ON UPD.CLASSESSETTINGID = CD.CLASSESSETTINGID LEFT JOIN TP_HR_STAFF CDU ON CDU.STAFFID = CD.STAFFID LEFT JOIN TP_MST_JOBS J ON J.JOBSID = CD.UJOBSID WHERE ( NPD.PRODUCTIONDATAID IS NULL OR NPD.MODELTYPE > -1 ) AND RPD.PROCEDUREID = 22 GROUP BY GROUPING SETS ( ( UPD.USERCODE, CDU.STAFFNAME, G.GOODSMODEL, RU.USERCODE, RU.USERNAME, J.JOBSNAME ), ( UPD.USERCODE, CDU.STAFFNAME, J.JOBSNAME ) ) )D WHERE D.产品型号 IS NOT NULL"; string sqlStr3 = @"INSERT INTO AJZ_RPT016_QT SELECT * FROM (SELECT UPD.USERCODE AS 工号, CDU.STAFFNAME AS 姓名, J.JOBSNAME AS 工种, G.GOODSMODEL AS 产品型号, RU.USERCODE AS 生产工号, RU.USERNAME AS 生产姓名, COUNT( DISTINCT TT.UPDID ) AS 回收检验数, COUNT( DISTINCT TT.UPDID )-SUM( dnum ) AS 回收合格数, '0' as JOBSID, '0' as BUILDINGNO, '2' , to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM ( SELECT T.PRODUCTIONDATAID, (select case when count(d.PRODUCTIONDEFECTID) > 0 then 1 else 0 end dnum from TP_PM_DEFECT D WHERE D.PRODUCTIONDATAID = T.PRODUCTIONDATAID AND D.DEFECTID = 72) dnum, MIN( CASE WHEN T.PRODUCTIONDATAID < PD.PRODUCTIONDATAID THEN PD.PRODUCTIONDATAID ELSE NULL END ) NEXTPDID, MAX( CASE WHEN T.PRODUCTIONDATAID > PD.PRODUCTIONDATAID AND PD.MODELTYPE <> -1 THEN PD.PRODUCTIONDATAID ELSE 0 END ) REPDID, MAX( CASE WHEN T.PRODUCTIONDATAID >= PD.PRODUCTIONDATAID AND PD.CHECKFLAG = 3 THEN PD.PRODUCTIONDATAID ELSE 0 END ) UPDID FROM TP_PM_PRODUCTIONDATA T INNER JOIN TP_PM_PRODUCTIONDATA PD ON T.BARCODE = PD.BARCODE AND T.KILNCARBATCHNO = PD.KILNCARBATCHNO AND (PD.MODELTYPE = -1 OR PD.VALUEFLAG='1') WHERE T.CREATETIME >= @DATEBEGIN@ AND T.CREATETIME < @DATEEND@ AND T.MODELTYPE = -1 GROUP BY T.PRODUCTIONDATAID ) TT LEFT JOIN TP_PM_PRODUCTIONDATA NPD ON NPD.PRODUCTIONDATAID = TT.NEXTPDID INNER JOIN TP_PM_PRODUCTIONDATA RPD ON RPD.PRODUCTIONDATAID = TT.REPDID INNER JOIN TP_MST_USER RU ON RU.USERID = RPD.USERID INNER JOIN TP_PM_PRODUCTIONDATA UPD ON UPD.PRODUCTIONDATAID = TT.UPDID INNER JOIN TP_PC_GROUTINGLINE GL ON GL.GROUTINGLINEID = RPD.GROUTINGLINEID INNER JOIN TP_MST_GOODS G ON RPD.GOODSID = G.GOODSID LEFT JOIN TP_PC_CLASSESDETAIL CD ON UPD.CLASSESSETTINGID = CD.CLASSESSETTINGID LEFT JOIN TP_HR_STAFF CDU ON CDU.STAFFID = CD.STAFFID LEFT JOIN TP_MST_JOBS J ON J.JOBSID = CD.UJOBSID WHERE ( NPD.PRODUCTIONDATAID IS NULL OR NPD.MODELTYPE > -1 ) AND RPD.PROCEDUREID = 23 GROUP BY GROUPING SETS ( ( UPD.USERCODE, CDU.STAFFNAME, G.GOODSMODEL, RU.USERCODE, RU.USERNAME, J.JOBSNAME ), ( UPD.USERCODE, CDU.STAFFNAME, J.JOBSNAME ) ) )D WHERE D.产品型号 IS NOT NULL"; string sqlStr4 = @"INSERT INTO AJZ_RPT016_QT SELECT * FROM (SELECT UPD.USERCODE AS 工号, CDU.STAFFNAME AS 姓名, J.JOBSNAME AS 工种, G.GOODSMODEL AS 产品型号, RU.USERCODE AS 生产工号, RU.USERNAME AS 生产姓名, COUNT( TT.UPDID ) AS 回收检验数, SUM( decode(TT.GOODSLEVELTYPEID,4,1,5,1,0) ) AS 回收合格数, '0' as JOBSID, '0' as BUILDINGNO, '3', to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM ( SELECT T.PRODUCTIONDATAID, T.GOODSLEVELTYPEID, MIN( CASE WHEN T.PRODUCTIONDATAID < PD.PRODUCTIONDATAID THEN PD.PRODUCTIONDATAID ELSE NULL END ) NEXTPDID, MAX( CASE WHEN T.PRODUCTIONDATAID > PD.PRODUCTIONDATAID AND PD.MODELTYPE <> -1 THEN PD.PRODUCTIONDATAID ELSE 0 END ) REPDID, MAX( CASE WHEN T.PRODUCTIONDATAID >= PD.PRODUCTIONDATAID AND PD.CHECKFLAG = 3 THEN PD.PRODUCTIONDATAID ELSE 0 END ) UPDID FROM TP_PM_PRODUCTIONDATA T INNER JOIN TP_PM_PRODUCTIONDATA PD ON T.BARCODE = PD.BARCODE AND T.KILNCARBATCHNO = PD.KILNCARBATCHNO AND (PD.MODELTYPE = -1 OR PD.VALUEFLAG='1') WHERE T.CREATETIME >= @DATEBEGIN@ AND T.CREATETIME < @DATEEND@ AND T.MODELTYPE = -1 GROUP BY T.PRODUCTIONDATAID, T.GOODSLEVELTYPEID ) TT LEFT JOIN TP_PM_PRODUCTIONDATA NPD ON NPD.PRODUCTIONDATAID = TT.NEXTPDID INNER JOIN TP_PM_PRODUCTIONDATA RPD ON RPD.PRODUCTIONDATAID = TT.REPDID INNER JOIN TP_MST_USER RU ON RU.USERID = RPD.USERID INNER JOIN TP_PM_PRODUCTIONDATA UPD ON UPD.PRODUCTIONDATAID = TT.UPDID INNER JOIN TP_PC_GROUTINGLINE GL ON GL.GROUTINGLINEID = RPD.GROUTINGLINEID INNER JOIN TP_MST_GOODS G ON RPD.GOODSID = G.GOODSID LEFT JOIN TP_PC_CLASSESDETAIL CD ON UPD.CLASSESSETTINGID = CD.CLASSESSETTINGID LEFT JOIN TP_HR_STAFF CDU ON CDU.STAFFID = CD.STAFFID LEFT JOIN TP_MST_JOBS J ON J.JOBSID = CD.UJOBSID WHERE ( NPD.PRODUCTIONDATAID IS NULL OR NPD.MODELTYPE > -1 ) AND RPD.PROCEDUREID = 37 GROUP BY GROUPING SETS ( ( UPD.USERCODE, CDU.STAFFNAME, G.GOODSMODEL, RU.USERCODE, RU.USERNAME, J.JOBSNAME ), ( UPD.USERCODE, CDU.STAFFNAME, J.JOBSNAME ) ) ) D WHERE D.产品型号 IS NOT NULL"; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result += conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); result1 += conn.ExecuteNonQuery(sqlStr2, sqlPara.ToArray()); result2 += conn.ExecuteNonQuery(sqlStr3, sqlPara.ToArray()); result3 += conn.ExecuteNonQuery(sqlStr4, sqlPara.ToArray()); } conn.Commit(); } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export016回收合格汇总表结转失败！"); } finally { conn.Close(); } if (result > 0 && result1 > 0 && result2 > 0 && result3 > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export016回收合格汇总表结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export016回收合格汇总表结转0条！"); } } } public static void export017() { using (IDataAccess conn = DataAccess.Create()) { try { Curtain.Log.Logger.Debug("export017 成检产量统计表结转开始！"); conn.Open(); conn.BeginTransaction(); //2021-07-12 dongyan 修正什么问题 int result = 0; int m = DateTime.Now.AddMonths(-1).Month; string mouth = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string year = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(year + mouth); DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string sqlStr = @"DELETE FROM AJZ_RPT017 WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(year + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1); //for (int j = 0; j <= 2; j++) //{ //String str = j + "#"; String sqlStr1 = @" INSERT INTO AJZ_RPT017 SELECT to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')), DECODE( GROUPING_ID ( U.USERCODE, PP.GOODSMODEL, CDU.STAFFNAME), 2, U.USERCODE || CDU.STAFFNAME || '[合计]', 3, '总计', U.USERCODE ) AS 工号, --U.USERNAME AS 姓名, CDU.STAFFNAME AS 姓名, J.JOBSNAME AS 工种, PP.GOODSMODEL AS 产品型号, SUM( PP.CHECKNUM1 ) AS 检验数_本烧, SUM( PP.CHECKNUM2 ) AS 检验数_重烧, SUM( PP.CHECKNUM3 ) AS 首检数_本烧, SUM( PP.CHECKNUM4 ) AS 首检数_重烧, SUM( PP.OK_B ) AS 合格数_本烧, SUM( PP.OK_R ) AS 合格数_重烧, SUM( PP.OK_L ) AS 合格数_冷补, SUM( PP.OK_ALL ) AS 合格数_合计, SUM( PP.OK_P ) AS 包装入仓, --CASE WHEN SUM( PP.OUT_K ) = 0 THEN NULL ELSE ROUND( SUM( PP.OK_ALL ) / SUM( PP.OUT_K ), 4 ) * 100 END AS 合格率, SUM( PP.DNUM1 ) AS 本烧缺陷数_排水不良, SUM( PP.DNUM2 ) AS 本烧缺陷数_漏气, SUM( PP.DNUM3 ) AS 重烧缺陷数_排水不良, SUM( PP.DNUM4 ) AS 重烧缺陷数_漏气, '0','0' FROM ( SELECT CASE WHEN PC.USERID IS NOT NULL THEN PC.USERID ELSE PM.USERID END USERID, CASE WHEN PC.CDUSERID IS NOT NULL THEN PC.CDUSERID ELSE PM.CDUSERID END CDUSERID, NVL(PC.JOBSID,PM.JOBSID) JOBSID, NVL( PC.GOODSID, PM.GOODSID ) GOODSID, NVL( PC.GOODSMODEL, PM.GOODSMODEL ) GOODSMODEL, NVL( PM.OK_P,0) OK_P, NVL( PC.CHECKNUM1, 0 ) CHECKNUM1, NVL( PC.CHECKNUM2, 0 ) CHECKNUM2, NVL( PC.CHECKNUM3, 0 ) CHECKNUM3, NVL( PC.CHECKNUM4, 0 ) CHECKNUM4, NVL( PM.DNUM1, 0 ) DNUM1, NVL( PM.DNUM2, 0 ) DNUM2, NVL( PM.DNUM3, 0 ) DNUM3, NVL( PM.DNUM4, 0 ) DNUM4, --NVL( PM.OUT_K, 0 ) OUT_K, NVL( PM.OK_B, 0 ) OK_B, NVL( PM.OK_R, 0 ) OK_R, NVL( PM.OK_L, 0 ) OK_L, NVL( PM.OK_ALL, 0 ) OK_ALL FROM --取用户和产品 ( SELECT P.USERID, -- 员工ID CD.STAFFID AS CDUSERID, CD.SJOBSID AS JOBSID, P.GOODSID, G.GOODSMODEL, SUM( DECODE( P.PROCEDUREID, 13, 1, 0 ) ) CHECKNUM1, SUM( DECODE( P.PROCEDUREID, 35, 1, 0 ) ) CHECKNUM2, --xuwei add SUM( CASE WHEN P.PROCEDUREID = 13 AND P.CHECKFLAG = 1 THEN 1 ELSE 0 END) CHECKNUM3, SUM( CASE WHEN P.PROCEDUREID = 35 AND P.CHECKFLAG = 1 THEN 1 ELSE 0 END) CHECKNUM4 FROM TP_PM_PRODUCTIONDATA P INNER JOIN TP_PC_GROUTINGLINE L ON P.GROUTINGLINEID = L.GROUTINGLINEID --关联班次配置 LEFT JOIN TP_PC_CLASSESDETAIL CD ON P.CLASSESSETTINGID = CD.CLASSESSETTINGID LEFT JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID WHERE P.CHECKFLAG IN ( 1,3 ) AND P.PROCEDUREID IN ( 13, 35 ) AND P.CREATETIME >= @DATEBEGIN@ AND P.CREATETIME < @DATEEND@ GROUP BY P.USERID, CD.SJOBSID, -- 员工ID CD.STAFFID, P.GOODSID, G.GOODSMODEL ) PC FULL JOIN --统计检验数合格数 ( SELECT P.USERID, -- 员工ID CD.STAFFID AS CDUSERID, CD.SJOBSID AS JOBSID, P.GOODSID, P.GOODSMODEL, COUNT(distinct CASE WHEN ( P.GOODSLEVELTYPEID = 4 OR P.GOODSLEVELTYPEID = 5 ) AND P.PROCEDUREID IN (13,35) AND NOT PDP.BARCODE IS NULL THEN P.BARCODE ELSE NULL END) AS OK_P, COUNT(distinct CASE WHEN ( P.GOODSLEVELTYPEID = 4 OR P.GOODSLEVELTYPEID = 5 ) AND P.PROCEDUREID = 13 AND PDD.PROCEDUREID IS NULL THEN P.PRODUCTIONDATAID ELSE NULL END ) AS OK_B, COUNT(distinct CASE WHEN ( P.GOODSLEVELTYPEID = 4 OR P.GOODSLEVELTYPEID = 5 ) AND P.PROCEDUREID = 35 AND PDD.PROCEDUREID IS NULL THEN P.PRODUCTIONDATAID ELSE NULL END ) AS OK_R, COUNT(distinct CASE WHEN ( P.GOODSLEVELTYPEID = 4 OR P.GOODSLEVELTYPEID = 5 ) AND NOT PDD.PROCEDUREID IS NULL THEN P.PRODUCTIONDATAID ELSE NULL END ) AS OK_L, COUNT(distinct DECODE( P.GOODSLEVELTYPEID, 4, P.PRODUCTIONDATAID, 5, P.PRODUCTIONDATAID, NULL ) ) AS OK_ALL , SUM( CASE WHEN P.GOODSLEVELTYPEID NOT IN (4,5) AND D.DEFECTID IN(9) AND P.PROCEDUREID = 13 THEN 1 ELSE 0 END) AS DNUM1, SUM( CASE WHEN P.GOODSLEVELTYPEID NOT IN (4,5) AND D.DEFECTID IN(11) AND P.PROCEDUREID = 13 THEN 1 ELSE 0 END) AS DNUM2, SUM( CASE WHEN P.GOODSLEVELTYPEID NOT IN (4,5) AND D.DEFECTID IN(9) AND P.PROCEDUREID = 35 THEN 1 ELSE 0 END) AS DNUM3, SUM( CASE WHEN P.GOODSLEVELTYPEID NOT IN (4,5) AND D.DEFECTID IN(11) AND P.PROCEDUREID = 35 THEN 1 ELSE 0 END) AS DNUM4 FROM ( SELECT TP.PRODUCTIONDATAID, TP.BARCODE, TP.USERID, TP.GOODSID, G.GOODSMODEL, TP.GROUTINGLINEID, TP.GOODSLEVELTYPEID, TP.PROCEDUREID, MAX( PMAX.PRODUCTIONDATAID ) AS MAXPRODUCTIONDATAID, TP.CLASSESSETTINGID FROM TP_PM_PRODUCTIONDATA TP INNER JOIN TP_PC_GROUTINGLINE L ON TP.GROUTINGLINEID = L.GROUTINGLINEID LEFT JOIN TP_PM_PRODUCTIONDATA PMAX ON PMAX.GROUTINGDAILYDETAILID = TP.GROUTINGDAILYDETAILID AND PMAX.PRODUCTIONDATAID < TP.PRODUCTIONDATAID AND PMAX.VALUEFLAG = '1' LEFT JOIN TP_MST_GOODS G ON G.GOODSID = TP.GOODSID WHERE TP.CHECKBATCHNO = 1 AND TP.PROCEDUREID IN ( 13, 35 ) AND TP.CREATETIME >= @DATEBEGIN@ AND TP.CREATETIME < @DATEEND@ GROUP BY TP.PRODUCTIONDATAID, TP.BARCODE, TP.USERID, TP.GOODSID, G.GOODSMODEL, TP.GROUTINGLINEID, TP.GOODSLEVELTYPEID, TP.PROCEDUREID, TP.CLASSESSETTINGID ) P --xuwei add 2020-11-13 取包装入库数据 LEFT JOIN TP_PM_PRODUCTIONDATA PDP ON P.BARCODE = PDP.BARCODE AND PDP.PROCEDUREID = 15 AND PDP.VALUEFLAG = '1' LEFT JOIN TP_PM_PRODUCTIONDATA PDD ON P.MAXPRODUCTIONDATAID = PDD.PRODUCTIONDATAID AND PDD.PROCEDUREID IN ( 22, 23 ) --统计排水不良9 和漏气11 数量 LEFT JOIN TP_PM_DEFECT D ON D.PRODUCTIONDATAID = P.PRODUCTIONDATAID AND D.DEFECTID IN(9,11) --关联班次配置 LEFT JOIN TP_PC_CLASSESDETAIL CD ON P.CLASSESSETTINGID = CD.CLASSESSETTINGID GROUP BY P.USERID, -- 员工ID CD.STAFFID, CD.SJOBSID, P.GOODSID, P.GOODSMODEL ) PM ON PM.USERID = PC.USERID AND PM.GOODSID = PC.GOODSID AND PM.CDUSERID = PC.CDUSERID ) PP LEFT JOIN TP_MST_USER U ON U.USERID = PP.USERID -- 员工ID LEFT JOIN TP_HR_STAFF CDU ON CDU.STAFFID = PP.CDUSERID LEFT JOIN TP_MST_JOBS J ON J.JOBSID = PP.JOBSID GROUP BY GROUPING SETS ( ( U.USERCODE,CDU.STAFFNAME, U.USERNAME, PP.GOODSMODEL,J.JOBSNAME ), ( U.USERCODE,CDU.STAFFNAME, U.USERNAME ) ) "; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", year)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result = conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); //} } conn.Commit(); if (result > 0) { Curtain.Log.Logger.Debug(year + "年" + mouth + "月:export017成检产量统计表结转成功！"); } else { Curtain.Log.Logger.Debug(year + "年" + mouth + "月:export017成检产量统计表结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export017成检产量统计表结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export018() { using (IDataAccess conn = DataAccess.Create()) { //2021-08-2 dongyan 修正什么问题 int result = 0; int result1 = 0; int m = DateTime.Now.AddMonths(-1).Month; DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); string dayOut = b + "-" + a + "-" + DateTime.DaysInMonth(Convert.ToInt32(b), m).ToString(); conn.Open(); conn.BeginTransaction(); try { String sqlStr = @"delete JZ_RPT018_D WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); String Str = @"delete JZ_RPT018_M WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(Str, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1).AddMilliseconds(-1); String sqlStr1 = @" INSERT INTO JZ_RPT018_M SELECT @M@, @M@ ,'张迪' ,'' ,'张迪' ,CASE WHEN SUM(T.DNUM) < 0 THEN '1' ELSE '0' END ,'' ,T.FNAME_CODE ,T.FNAME ,'' ,'01.094' ,'张迪' ,'01.094' ,'张迪' ,'01.001' ,'唐山中陶实业有限公司' ,'' ,T.FNAME_FLAG ,to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM (SELECT 1 AS DNUM ,CASE WHEN TS.PROCEDUREID IN (26, 27, 38) THEN '精坯库（一期）' WHEN TS.PROCEDUREID IN (3, 39) THEN '精坯库（一期）' WHEN TS.PROCEDUREID IN (28, 29, 30, 32, 33) THEN '釉坯库（一期）' WHEN TS.PROCEDUREID IN (24, 4, 5, 6, 10, 16, 34) THEN '釉坯库（一期）' WHEN TS.PROCEDUREID = 31 OR (TS.PROCEDUREID = 9 AND PD.KILNID IN (1, 2)) THEN '窑坯（一期）' WHEN TS.PROCEDUREID = 21 OR (TS.PROCEDUREID = 9 AND PD.KILNID IN (3, 4)) THEN '窑坯（一期）' ELSE NULL END FNAME_FLAG ,CASE WHEN TS.PROCEDUREID IN (26, 27, 38) THEN '009' WHEN TS.PROCEDUREID IN (3, 39) THEN '009' WHEN TS.PROCEDUREID IN (28, 29, 30, 32, 33) THEN '010' WHEN TS.PROCEDUREID IN (24, 4, 5, 6, 10, 16, 34) THEN '010' WHEN TS.PROCEDUREID = 31 OR (TS.PROCEDUREID = 9 AND PD.KILNID IN (1, 2)) THEN '011' WHEN TS.PROCEDUREID = 21 OR (TS.PROCEDUREID = 9 AND PD.KILNID IN (3, 4)) THEN '011' ELSE NULL END FNAME_CODE ,CASE WHEN TS.PROCEDUREID IN (26, 27, 38) THEN '一期成型车间' WHEN TS.PROCEDUREID IN (3, 39) THEN '一期成型车间' WHEN TS.PROCEDUREID IN (28, 29, 30, 32, 33) THEN '一期施釉车间' WHEN TS.PROCEDUREID IN (24, 4, 5, 6, 10, 16, 34) THEN '一期施釉车间' WHEN TS.PROCEDUREID = 31 OR (TS.PROCEDUREID = 9 AND PD.KILNID IN (1, 2)) THEN '一期烧成车间' WHEN TS.PROCEDUREID = 21 OR (TS.PROCEDUREID = 9 AND PD.KILNID IN (3, 4)) THEN '一期烧成车间' ELSE NULL END FNAME FROM TP_PM_SCRAPPRODUCT TS INNER JOIN TP_PM_PRODUCTIONDATA PD ON TS.PRODUCTIONDATAID = PD.PRODUCTIONDATAID WHERE TS.AUDITSTATUS = 1 AND TS.GOODSLEVELTYPEID IN (8, 9) AND TS.AUDITDATE >=@DATEBEGIN@ AND TS.AUDITDATE <= @DATEEND@ AND TS.PROCEDUREID IN (3 ,26 ,27 ,38 ,39 ,28 ,29 ,30 ,32 ,33 ,24 ,4, 5 ,6 ,10 ,16, 34 ,21 ,31 ,9) UNION ALL SELECT 1 AS DNUM ,CASE WHEN TS.OTHERID IN (26, 27, 38) THEN '精坯库（一期）' WHEN TS.OTHERID IN (3, 39) THEN '精坯库（一期）' WHEN TS.OTHERID IN (28, 29, 30, 32, 33) THEN '釉坯库（一期）' WHEN TS.OTHERID IN (24, 4, 5, 6, 10, 16, 34) THEN '釉坯库（一期）' WHEN TS.OTHERID = 31 OR (TS.OTHERID = 9 AND PD.KILNID IN (1, 2)) THEN '窑坯（一期）' WHEN TS.OTHERID = 21 OR (TS.OTHERID = 9 AND PD.KILNID IN (3, 4)) THEN '窑坯（一期）' ELSE NULL END FNAME_FLAG ,CASE WHEN TS.OTHERID IN (26, 27, 38) THEN '009' WHEN TS.OTHERID IN (3, 39) THEN '009' WHEN TS.OTHERID IN (28, 29, 30, 32, 33) THEN '010' WHEN TS.OTHERID IN (24, 4, 5, 6, 10, 16, 34) THEN '010' WHEN TS.OTHERID = 31 OR (TS.OTHERID = 9 AND PD.KILNID IN (1, 2)) THEN '011' WHEN TS.OTHERID = 21 OR (TS.OTHERID = 9 AND PD.KILNID IN (3, 4)) THEN '011' ELSE NULL END FNAME_CODE ,CASE WHEN TS.OTHERID IN (26, 27, 38) THEN '一期成型车间' WHEN TS.OTHERID IN (3, 39) THEN '一期成型车间' WHEN TS.OTHERID IN (28, 29, 30, 32, 33) THEN '一期施釉车间' WHEN TS.OTHERID IN (24, 4, 5, 6, 10, 16, 34) THEN '一期施釉车间' WHEN TS.OTHERID = 31 OR (TS.OTHERID = 9 AND PD.KILNID IN (1, 2)) THEN '一期烧成车间' WHEN TS.OTHERID = 21 OR (TS.OTHERID = 9 AND PD.KILNID IN (3, 4)) THEN '一期烧成车间' ELSE NULL END FNAME FROM TP_PM_GOODSCHANGEHISTORY TS LEFT JOIN TP_PM_PRODUCTIONDATA PD ON TS.GROUTINGDAILYDETAILID = PD.GROUTINGDAILYDETAILID AND TS.OTHERID = 9 AND PD.PROCEDUREID=9 WHERE TS.DATATYPE = 11 AND TS.CREATETIME >=@DATEBEGIN@ AND TS.CREATETIME <=@DATEEND@ AND TS.OTHERID IN (3 ,26 ,27 ,38 ,39 ,28 ,29 ,30 ,32 ,33 ,24 ,4, 5 ,6 ,10 ,16, 34 ,21 ,31 ,9)) T WHERE ('' IS NULL OR T.FNAME_FLAG = '') GROUP BY T.FNAME_CODE, T.FNAME, T.FNAME_FLAG ORDER BY T.FNAME_CODE, T.FNAME"; List sqlPara1 = new List(); sqlPara1.Add(new CDAParameter("I", i)); sqlPara1.Add(new CDAParameter("YEAR", b)); sqlPara1.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara1.Add(new CDAParameter("DATEBEGIN", dayBegin)); sqlPara1.Add(new CDAParameter("M", dayOut)); result += conn.ExecuteNonQuery(sqlStr1, sqlPara1.ToArray()); String sqlStr2 = @"INSERT INTO JZ_RPT018_D SELECT '' ,'' ,'' ,K.K_CODE ,T.GOODS_CODE ,'' ,'' ,'' ,'*' ,'件' ,'件' ,0 ,SUM(T.DNUM) ,0 ,'破损' ,SUM(T.DNUM) ,'' ,'' ,T.FNAME ,'' ,'' ,to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM (SELECT CASE WHEN TS.PROCEDUREID IN (3, 26, 27, 38, 39) THEN 'J' WHEN TS.PROCEDUREID IN (21, 31, 9) THEN 'S' ELSE 'Y' END || G.GOODSMODEL AS GOODS_CODE ,1 AS DNUM ,CASE WHEN TS.PROCEDUREID IN (26, 27, 38) THEN '精坯库（一期）' WHEN TS.PROCEDUREID IN (3, 39) THEN '精坯库（一期）' WHEN TS.PROCEDUREID IN (28, 29, 30, 32, 33) THEN '釉坯库（一期）' WHEN TS.PROCEDUREID IN (24, 4, 5, 6, 10, 16, 34) THEN '釉坯库（一期）' WHEN TS.PROCEDUREID = 31 OR (TS.PROCEDUREID = 9 AND PD.KILNID IN (1, 2)) THEN '窑坯（一期）' WHEN TS.PROCEDUREID = 21 OR (TS.PROCEDUREID = 9 AND PD.KILNID IN (3, 4)) THEN '窑坯（一期）' ELSE NULL END FNAME FROM TP_PM_SCRAPPRODUCT TS INNER JOIN TP_MST_GOODS G ON TS.GOODSID = G.GOODSID INNER JOIN TP_PM_PRODUCTIONDATA PD ON TS.PRODUCTIONDATAID = PD.PRODUCTIONDATAID WHERE TS.AUDITSTATUS = 1 AND TS.GOODSLEVELTYPEID IN (8, 9) AND TS.AUDITDATE >= @DATEBEGIN@ AND TS.AUDITDATE <= @DATEEND@ AND TS.PROCEDUREID IN (3 ,26 ,27 ,38 ,39 ,28 ,29 ,30 ,32 ,33 ,24 ,4, 5 ,6 ,10 ,16, 34 ,21 ,31 ,9) UNION ALL SELECT CASE WHEN TS.OTHERID IN (3, 26, 27, 38, 39) THEN 'J' WHEN TS.OTHERID IN (21, 31, 9) THEN 'S' ELSE 'Y' END || G.GOODSMODEL AS GOODS_CODE ,1 AS DNUM ,CASE WHEN TS.OTHERID IN (26, 27, 38) THEN '精坯库（一期）' WHEN TS.OTHERID IN (3, 39) THEN '精坯库（一期）' WHEN TS.OTHERID IN (28, 29, 30, 32, 33) THEN '釉坯库（一期）' WHEN TS.OTHERID IN (24, 4, 5, 6, 10, 16, 34) THEN '釉坯库（一期）' WHEN TS.OTHERID = 31 OR (TS.OTHERID = 9 AND PD.KILNID IN (1, 2)) THEN '窑坯（一期）' WHEN TS.OTHERID = 21 OR (TS.OTHERID = 9 AND PD.KILNID IN (3, 4)) THEN '窑坯（一期）' ELSE NULL END FNAME FROM TP_PM_GOODSCHANGEHISTORY TS INNER JOIN TP_MST_GOODS G ON TS.GOODSID = G.GOODSID LEFT JOIN TP_PM_PRODUCTIONDATA PD ON TS.GROUTINGDAILYDETAILID = PD.GROUTINGDAILYDETAILID AND TS.OTHERID = 9 AND PD.PROCEDUREID=9 WHERE TS.DATATYPE = 11 AND TS.CREATETIME >= @DATEBEGIN@ AND TS.CREATETIME <= @DATEEND@ AND TS.OTHERID IN (3 ,26 ,27 ,38 ,39 ,28 ,29 ,30 ,32 ,33 ,24 ,4, 5 ,6 ,10 ,16, 34 ,21 ,31 ,9) ) T LEFT JOIN TA_IMEX_kingdee_Goodscode K ON T.GOODS_CODE = K.K_NAME AND T.FNAME = K.K_WAREHOUSE WHERE ('' IS NULL OR T.FNAME = '') GROUP BY K.K_CODE, T.GOODS_CODE, T.FNAME " ; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result1 += conn.ExecuteNonQuery(sqlStr2, sqlPara.ToArray()); ; } conn.Commit(); } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(b + "年" + a + "月 :export018其他出库单结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } if (result > 0 && result1 > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export018其他出库单结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export018其他出库单结转0条！"); } } } public static void export019() { using (IDataAccess conn = DataAccess.Create()) { //2021-08-2 dongyan 修正什么问题 int result = 0; int result1 = 0; int m = DateTime.Now.AddMonths(-1).Month; DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); string dayOut = b + "-" + a + "-" + DateTime.DaysInMonth(Convert.ToInt32(b), m).ToString(); conn.Open(); conn.BeginTransaction(); try { String sqlStr = @"delete JZ_RPT019_D WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); String Str = @"delete JZ_RPT019_M WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(Str, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1).AddMilliseconds(-1); String sqlStr1 = @" INSERT INTO JZ_RPT019_D SELECT * FROM( SELECT '' AS 序列号内码, '' AS 行号, '' AS 单据号_FBillno, '' AS 单据号_FTrantype, K.K_CODE AS 物料代码_FNumber, T.GOODS_CODE AS 物料代码_FName, '' AS 物料代码_FModel, '' AS 辅助属性_FNumber, '' AS 辅助属性_FName, '*' AS 辅助属性_FClassName, K2.K_CODE AS 成本对象_FNumber, T.GOODS_CODE2 AS 成本对象_FName, '' AS 成本对象_FItemClassID, '*' AS 成本对象组_FNumber, '*' AS 成本对象组_FName, '0' AS 成本对象组_FItemClassID, '件' AS 单位_FNumber, '件' AS 单位_FName, 0 AS 单价, '' AS 批号, 0 AS 金额, 0 AS 客户BOM, SUM( T.DNUM ) AS 实发数量, '' AS 备注, SUM( T.DNUM ) AS 基本单位实发数量, 0 AS 保质期, '' AS 有效期至, '' AS 发料仓库_FNumber, T.FNAME AS 发料仓库_FName, NULL AS 仓位_FName, NULL AS 仓位_FGroupName, 0 AS 辅助数量, to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM ( SELECT CASE WHEN TS.PROCEDUREID IN ( 28, 24, 34 ) THEN 'J' WHEN TS.PROCEDUREID IN ( 13 ) THEN 'S' ELSE 'Y' END || G.GOODSMODEL AS GOODS_CODE, CASE WHEN TS.PROCEDUREID IN ( 28, 24, 34 ) THEN 'Y' WHEN TS.PROCEDUREID IN ( 13 ) THEN '' ELSE 'S' END || G.GOODSMODEL AS GOODS_CODE2, 1 AS DNUM, CASE WHEN TS.PROCEDUREID = 28 THEN '精坯库（一期）' WHEN TS.PROCEDUREID IN ( 24, 34 ) THEN '精坯库（一期）' WHEN TS.PROCEDUREID = 31 THEN '釉坯库（一期）' WHEN TS.PROCEDUREID = 21 THEN '釉坯库（一期）' WHEN TS.PROCEDUREID = 13 AND TS.Kilnid IN ( 1, 2 ) THEN '窑坯（一期）' WHEN TS.PROCEDUREID = 13 AND TS.Kilnid IN ( 3, 4 ) THEN '窑坯（一期）' ELSE NULL END FNAME, CASE WHEN TS.PROCEDUREID = 28 THEN '一期施釉车间' WHEN TS.PROCEDUREID IN ( 24, 34 ) THEN '一期施釉车间' WHEN TS.PROCEDUREID = 31 THEN '一期烧成车间' WHEN TS.PROCEDUREID = 21 THEN '一期烧成车间' WHEN TS.PROCEDUREID = 13 AND GL.BUILDINGNO = '1#' THEN '一期成检组' WHEN TS.PROCEDUREID = 13 AND GL.BUILDINGNO = '2#' THEN '一期成检组' ELSE NULL END FNAME_F FROM TP_PM_PRODUCTIONDATA TS INNER JOIN TP_MST_GOODS G ON TS.GOODSID = G.GOODSID INNER JOIN TP_PC_GROUTINGLINE GL ON GL.GROUTINGLINEID = TS.GROUTINGLINEID WHERE --2022年1月4日10:11:10 by fy modify begin --TS.CREATETIME >= @DATEEND@ --AND TS.CREATETIME < @DATEBEGIN@ TS.CREATETIME >= @DATEBEGIN@ AND TS.CREATETIME <= @DATEEND@ --end AND ( TS.PROCEDUREID IN ( 28, 24, 34, 21, 31 ) OR ( TS.PROCEDUREID = 13 AND TS.CHECKFLAG = 1 ) ) ) T LEFT JOIN TA_IMEX_kingdee_Goodscode K ON T.GOODS_CODE = K.K_NAME AND T.FNAME = K.K_WAREHOUSE LEFT JOIN TA_IMEX_kingdee_Goodscode K2 ON K2.K_NAME = T.GOODS_CODE2 AND K2.buildingno = K.buildingno GROUP BY K.K_CODE, K2.K_CODE, T.GOODS_CODE, T.GOODS_CODE2, T.FNAME, T.FNAME_F )"; List sqlPara1 = new List(); sqlPara1.Add(new CDAParameter("I", i)); sqlPara1.Add(new CDAParameter("YEAR", b)); sqlPara1.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara1.Add(new CDAParameter("DATEBEGIN", dayBegin)); result1 += conn.ExecuteNonQuery(sqlStr1, sqlPara1.ToArray()); } conn.Commit(); } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(b + "年" + a + "月:export019生产领料表自动结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } if (result1 > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export019生产领料表自动结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export019生产领料表自动结转0条！"); } } } public static void export020() { using (IDataAccess conn = DataAccess.Create()) { //2021-08-2 dongyan 修正什么问题 int result = 0; int result1 = 0; int m = DateTime.Now.AddMonths(-1).Month; DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); string dayOut = b + "-" + a + "-" + DateTime.DaysInMonth(Convert.ToInt32(b), m).ToString(); conn.Open(); conn.BeginTransaction(); try { String sqlStr = @"delete JZ_RPT020_D WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); String Str = @"delete JZ_RPT020_M WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(Str, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1); String sqlStr2 = @"INSERT INTO JZ_RPT020_D SELECT * from (" + "SELECT '' AS 行号\n" + " ,'' AS \"单据号_FBillno\"\n" + " ,'' AS \"单据号_FTrantype\"\n" + " ,K.K_CODE AS \"物料编码_FNumber\"\n" + " ,T.GOODS_CODE AS \"物料编码_FName\"\n" + " ,'' AS \"物料编码_FModel\"\n" + " ,'' AS \"辅助属性_FNumber\"\n" + " ,'' AS \"辅助属性_FName\"\n" + " ,'*' AS \"辅助属性_FClassName\"\n" + " ,'件' AS \"单位_FNumber\"\n" + " ,'件' AS \"单位_FName\"\n" + " ,SUM(T.DNUM) AS \"实收数量\"\n" + " ,0 AS \"单价\"\n" + " ,0 AS \"金额\"\n" + " ,'' AS \"备注\"\n" + " ,SUM(T.DNUM) AS \"基本单位实收数量\"\n" + " ,'' AS \"生产_采购日期\"\n" + " ,'' AS \"有效期至\"\n" + " ,'' AS \"收货仓库_FNumber\"\n" + " ,T.FNAME AS \"收货仓库_FName\"\n" + " ,NULL AS \"仓位_FName\"\n" + " ,NULL AS \"仓位_FGroupName\"\n" + " ,0 AS \"辅助数量\"\n" + " ,'0'" + ",to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0'))" + " FROM (SELECT CASE\n" + " WHEN TS.PROCEDUREID IN (3, 26) THEN\n" + " 'J'\n" + " WHEN TS.PROCEDUREID IN (21, 31) THEN\n" + " 'S'\n" + " ELSE\n" + " 'Y'\n" + " END || G.GOODSMODEL AS GOODS_CODE\n" + " ,1 AS DNUM\n" + " ,CASE\n" + " WHEN TS.PROCEDUREID = 26 THEN\n" + " '精坯库（一期）'\n" + " WHEN TS.PROCEDUREID = 3 THEN\n" + //" '精坯库（二期）'\n" + " '精坯库（一期）'\n" + " WHEN TS.PROCEDUREID = 28 THEN\n" + " '釉坯库（一期）'\n" + " WHEN TS.PROCEDUREID IN( 24,34) THEN\n" + //" '釉坯库（二期）'\n" + " '釉坯库（一期）'\n" + " WHEN TS.PROCEDUREID = 31 THEN\n" + " '窑坯（一期）'\n" + " WHEN TS.PROCEDUREID = 21 THEN\n" + //" '窑坯（二期）'\n" + " '窑坯（一期）'\n" + " ELSE\n" + " NULL\n" + " END FNAME\n" + " FROM TP_PM_PRODUCTIONDATA TS\n" + " INNER JOIN TP_MST_GOODS G\n" + " ON TS.GOODSID = G.GOODSID\n" + " WHERE TS.CREATETIME >= @DATEBEGIN@\n" + " AND TS.CREATETIME < @DATEEND@\n" + " AND TS.PROCEDUREID IN (3, 26, 28, 24,34, 21, 31)\n" + ") T\n" + " LEFT JOIN TA_IMEX_kingdee_Goodscode K ON T.GOODS_CODE = K.K_NAME AND T.FNAME = K.K_WAREHOUSE\n" + // " WHERE (@FNAME_FLAG@ IS NULL OR T.FNAME = @FNAME_FLAG@) \n" + " GROUP BY K.K_CODE, T.GOODS_CODE, T.FNAME\n" + " )"; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result1 += conn.ExecuteNonQuery(sqlStr2, sqlPara.ToArray()); } conn.Commit(); } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(b + "年" + a + "月:export020产品入库单结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } if (result1 > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export020产品入库单结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export020产品入库单结转0条！"); } } } public static void export021() { using (IDataAccess conn = DataAccess.Create()) { try { conn.Open(); conn.BeginTransaction(); //2021-07-29 陈强 String sqlStr = @"delete AJZ_RPT021 where FORWARD_TIME = @c@"; int result = 0; int m = DateTime.Now.AddMonths(-1).Month; string mouth = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string year = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(year + mouth); conn.ExecuteNonQuery(sqlStr, new CDAParameter("c", c)); DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(year + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1).AddMilliseconds(-1); //for (int j = 0; j <= 2; j++) //{ // String str = j + "#"; String sqlStr1 = @"INSERT INTO AJZ_RPT021 SELECT * FROM ( SELECT DECODE(GID ,7 ,TT.组别 || '[小计]' ,15 ,TT.组别 || '[合计]' ,TT.组别) 组别 ,TT.线号 ,TT.产品型号 ,DECODE(GID,15, null, TT.标准模型次数) 标准模型次数 ,DECODE(GID,15, null, TT.模型次数) 模型次数 ,TT.实摆模型 ,TT.实际注浆数 ,DECODE(GID,15, null, TT.成型工号) 成型工号 ,DECODE(GID,15, null, TT.姓名) 姓名 ,TT.交坯合格数 ,CASE WHEN TT.实际注浆数 IS NULL OR TT.实际注浆数 = 0 THEN NULL WHEN TT.交坯合格数 IS NULL OR TT.交坯合格数 = 0 THEN '0%' ELSE TO_CHAR(ROUND(TT.交坯合格数 / TT.实际注浆数, 4) * 100) || '%' END 实际出坯率 --实际出坯率【交坯合格/注浆数】 ,DECODE(GID,15, null, @DAYS@) 计划出勤 ,DECODE(GID,15, null, TT.实际出勤) 实际出勤 ,DECODE(GID,15, null, TT.实际模具总数) 实际模具总数 ,DECODE(GID,15, null, TT.计划批次) 计划批次 ,DECODE(GID,15, null, @DAYS@ * TT.实摆模型 * TT.计划批次) 计划模具总数 -- 计划出勤 * 实摆模型数 * 计划批次 ,DECODE(GID,15, null, CASE WHEN @DAYS@ * TT.实摆模型 * TT.计划批次 IS NULL OR @DAYS@ * TT.实摆模型 * TT.计划批次 = 0 THEN NULL WHEN TT.交坯合格数 IS NULL OR TT.交坯合格数 = 0 THEN '0%' ELSE TO_CHAR(ROUND(TT.交坯合格数 / (@DAYS@ * TT.实摆模型 * TT.计划批次), 4) * 100) || '%' END) 实摆出坯率 ,--实摆出坯率【交坯合格/实际模具数】改为交坯合格数 / 计划模具总数, to_number( to_char( @YEAR@ ) || lpad( to_char(@I@ ), 2, '0' ) ), '0' AS BUILDINGNO, 0 AS GL_MOULDQUANTITY, 0 AS gid, '0' AS GROUTINGLINEID, '0' AS HIGHPRESSUREFLAG, '0' AS GOODSTYPECODE FROM ( SELECT GROUPING_ID ( PT.M_USERNAME, PT.LINE_NO,PT.GROUTINGLINEID, PT.GOODS_CODE, PT.G_USERCODE ) gid ,PT.M_USERNAME 组别 ,PT.LINE_NO 线号 ,PT.GOODS_CODE 产品型号 ,MAX(PT.STANDARDGROUTINGCOUNT) 标准模型次数 ,MAX(PT.MAX_MCOUNT) 模型次数 ,DECODE(PT.GOODS_CODE, null, max(PT.GL_MOULDQUANTITY), SUM(PT.MOULDQUANTITY)) 实摆模型 ,SUM(PT.GROUTINGNUM) 实际注浆数 ,REGEXP_REPLACE(LISTAGG(PT.G_USERCODE, '/') WITHIN GROUP(ORDER BY PT.G_USERCODE) ,'([^/]+)(/\1)*(/|$)' ,'\1\3') 成型工号 ,REGEXP_REPLACE(LISTAGG(PT.G_USERNAME, '/') WITHIN GROUP(ORDER BY PT.G_USERCODE) ,'([^/]+)(/\1)*(/|$)' ,'\1\3') 姓名 ,SUM(PT.D_OKCOUNT) 交坯合格数 ,MAX(PT.GROUTINGDATES) 实际出勤 ,SUM(PT.S_MOULDQUANTITY) 实际模具总数 ,CASE WHEN PT.HIGHPRESSUREFLAG = '1' AND --INSTR(PT.GOODSTYPECODE, '001001') = 1 MAX(PT.GOODSTYPECODE2) = '001001' THEN 15 WHEN PT.HIGHPRESSUREFLAG = '1' AND --INSTR(PT.GOODSTYPECODE, '001002') = 1 MAX(PT.GOODSTYPECODE2) = '001002' THEN 25 WHEN PT.HIGHPRESSUREFLAG = '0' THEN 1 ELSE NULL END AS 计划批次 FROM (SELECT TO_CHAR(MU.USERNAME) AS M_USERNAME ,GL.GROUTINGLINEID ,GL.GROUTINGLINENAME AS LINE_NO ,COALESCE(G.GOODSMODEL, P.GOODSMODEL, GL2.GOODSMODEL) AS GOODS_CODE ,COALESCE(G.STANDARDGROUTINGCOUNT, GL2.STANDARDGROUTINGCOUNT) AS STANDARDGROUTINGCOUNT ,COALESCE(G.MAX_MCOUNT, GL2.MAX_MCOUNT) MAX_MCOUNT ,COALESCE(G.MOULDQUANTITY, GL2.MOULDQUANTITY) AS MOULDQUANTITY ,COALESCE(G.GROUTINGNUM, 0) AS GROUTINGNUM ,TO_CHAR(GU.USERCODE) AS G_USERCODE ,TO_CHAR(GU.USERNAME) AS G_USERNAME ,COALESCE(P.D_OKCOUNT, 0) AS D_OKCOUNT ,COALESCE(G.GROUTINGDATES, 0) AS GROUTINGDATES ,COALESCE(G.GROUTINGDATES, 0) * COALESCE(G.MOULDQUANTITY, 0) S_MOULDQUANTITY --实际模具数【（计划出勤-核减）*成型线的模具数】 ,GL.HIGHPRESSUREFLAG,GL.MOULDQUANTITY GL_MOULDQUANTITY ,COALESCE(G.GOODSTYPECODE2 ,P.GOODSTYPECODE2 ,GL2.GOODSTYPECODE2) AS GOODSTYPECODE2 FROM (SELECT GD.GROUTINGLINEID ,GD.GOODSMODEL ,MAX(GD.STANDARDGROUTINGCOUNT) STANDARDGROUTINGCOUNT ,MAX(GD.MAX_MCOUNT) MAX_MCOUNT ,MAX(GD.MOULDQUANTITY) MOULDQUANTITY ,GD.USERCODE ,GD.USERID ,SUM(GD.GROUTINGNUM) GROUTINGNUM ,COUNT(DISTINCT GD.GROUTINGDATE) GROUTINGDATES ,GD.GOODSTYPECODE2 FROM (SELECT GGDD.GROUTINGDAILYID ,GGDD.GROUTINGDATE ,COUNT(GGDD.GROUTINGDAILYDETAILID) GROUTINGNUM ,GOODS.GOODSMODEL ,GGDD.USERCODE ,GGDD.USERID ,GGDD.GROUTINGLINEID ,SUBSTR(GT.GOODSTYPECODE, 1, 6) GOODSTYPECODE2 ,COUNT(GGDD.GROUTINGLINEDETAILID) MOULDQUANTITY ,MAX(GLD.STANDARDGROUTINGCOUNT) STANDARDGROUTINGCOUNT ,MAX(GGDD.GROUTINGCOUNT) MAX_MCOUNT FROM TP_PM_GROUTINGDAILYDETAIL GGDD INNER JOIN TP_MST_GOODS GOODS ON GOODS.GOODSID = GGDD.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = GOODS.GOODSTYPEID INNER JOIN TP_PC_GROUTINGLINEDETAIL GLD ON GGDD.GROUTINGLINEDETAILID = GLD.GROUTINGLINEDETAILID WHERE GGDD.VALUEFLAG = '1' AND GGDD.GROUTINGFLAG = '1' AND GGDD.GROUTINGDATE >= @DATEBEGIN@ AND GGDD.GROUTINGDATE < @DATEEND@ -- AND ( GOODS.GOODSMODEL = @GOODSMODEL@ OR @GOODSMODEL@ IS NULL ) GROUP BY GGDD.GROUTINGDAILYID ,GGDD.GROUTINGDATE ,GOODS.GOODSMODEL ,GGDD.USERCODE ,GGDD.USERID ,GGDD.GROUTINGLINEID ,SUBSTR(GT.GOODSTYPECODE, 1, 6)) GD GROUP BY GD.GROUTINGLINEID ,GD.GOODSMODEL --,GD.MOULDQUANTITY ,GD.USERCODE ,GD.USERID ,GD.GOODSTYPECODE2) G FULL JOIN (SELECT PP.GROUTINGLINEID ,PP.GROUTINGUSERCODE USERCODE ,PP.GROUTINGUSERID USERID ,GOODS.GOODSMODEL ,SUBSTR(GT.GOODSTYPECODE, 1, 6) GOODSTYPECODE2 ,COUNT(*) D_OKCOUNT FROM TP_PM_PRODUCTIONDATA PP INNER JOIN TP_MST_GOODS GOODS ON GOODS.GOODSID = PP.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = GOODS.GOODSTYPEID WHERE PP.VALUEFLAG = '1' AND PP.PROCEDUREID IN (3, 26) AND PP.CREATETIME >= @DATEBEGIN@ AND PP.CREATETIME < @DATEEND@ -- AND ( GOODS.GOODSMODEL = @GOODSMODEL@ OR @GOODSMODEL@ IS NULL ) GROUP BY PP.GROUTINGLINEID ,GOODS.GOODSMODEL ,PP.GROUTINGUSERCODE ,PP.GROUTINGUSERID ,SUBSTR(GT.GOODSTYPECODE, 1, 6)) P ON P.GROUTINGLINEID = G.GROUTINGLINEID AND P.GOODSMODEL = G.GOODSMODEL AND P.GOODSTYPECODE2 = G.GOODSTYPECODE2 AND P.USERID = G.USERID FULL JOIN (SELECT GLL.GROUTINGLINEID ,GOODS.GOODSMODEL ,MAX(GLD.STANDARDGROUTINGCOUNT) STANDARDGROUTINGCOUNT ,MAX(GLD.GROUTINGCOUNT) MAX_MCOUNT ,COUNT(*) MOULDQUANTITY ,SUBSTR(GT.GOODSTYPECODE, 1, 6) GOODSTYPECODE2 FROM TP_PC_GROUTINGLINE GLL INNER JOIN TP_PC_GROUTINGLINEDETAIL GLD ON GLD.GROUTINGLINEID = GLL.GROUTINGLINEID INNER JOIN TP_MST_GOODS GOODS ON GOODS.GOODSID = GLD.GOODSID LEFT JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = GOODS.GOODSTYPEID WHERE GLD.VALUEFLAG = '1' AND GLL.VALUEFLAG = '1' AND GLL.GMOULDSTATUS = 1 -- AND GLD.GMOULDSTATUS = 1 -- AND (GOODS.GOODSMODEL = @GOODSMODEL@ OR @GOODSMODEL@ IS NULL) GROUP BY GLL.GROUTINGLINEID ,GOODS.GOODSMODEL ,SUBSTR(GT.GOODSTYPECODE, 1, 6)) GL2 ON GL2.GROUTINGLINEID = NVL(G.GROUTINGLINEID, P.GROUTINGLINEID) AND GL2.GOODSMODEL = NVL(G.GOODSMODEL, P.GOODSMODEL) AND GL2.GOODSTYPECODE2 = NVL(G.GOODSTYPECODE2, P.GOODSTYPECODE2) INNER JOIN TP_PC_GROUTINGLINE GL ON GL.GROUTINGLINEID = COALESCE(G.GROUTINGLINEID ,P.GROUTINGLINEID ,GL2.GROUTINGLINEID) LEFT JOIN TP_MST_USER GU ON (GU.USERID = P.USERID OR GU.USERID = G.USERID) LEFT JOIN TP_MST_USER MU ON MU.USERID = GL.MONITORID -- WHERE (GL.BUILDINGNO = @BUILDINGNO@ OR @BUILDINGNO@ IS NULL) -- AND (MU.USERNAME = @USERNAME@ OR @USERNAME@ IS NULL) ORDER BY MU.USERNAME ,GL.GROUTINGLINENAME ,COALESCE(G.GOODSMODEL, P.GOODSMODEL, GL2.GOODSMODEL) ,NVL(G.USERCODE, P.USERCODE)) PT GROUP BY GROUPING SETS((PT.M_USERNAME, PT.LINE_NO,PT.GROUTINGLINEID,PT.GL_MOULDQUANTITY, PT.GOODS_CODE, PT.G_USERCODE, PT.G_USERNAME, PT.HIGHPRESSUREFLAG, PT.GOODSTYPECODE2),(PT.M_USERNAME, PT.LINE_NO, PT.HIGHPRESSUREFLAG),(PT.M_USERNAME)) ORDER BY PT.M_USERNAME, PT.LINE_NO, PT.G_USERCODE, PT.GOODS_CODE) TT)"; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", year)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); int days = (dayEnd.Date.AddSeconds(1) - dayBegin.Date).Days + 1; days -= DayOfWeeksInDays(dayBegin, days, DayOfWeek.Sunday); sqlPara.Add(new CDAParameter("DAYS", days)); result += conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); //} } conn.Commit(); if (result > 0) { Curtain.Log.Logger.Debug(year + "年" + mouth + "月:export021模具使用表结转成功！"); } else { Curtain.Log.Logger.Debug(year + "年" + mouth + "月:export021模具使用表结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export021模具使用表结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export022() { using (IDataAccess conn = DataAccess.Create()) { try { conn.Open(); conn.BeginTransaction(); //2021-07-12 dongyan 修正什么问题 int result = 0; int m = DateTime.Now.AddMonths(-1).Month; DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); String sqlStr = @"delete AJZ_RPT022 WHERE FORWARD_TIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1).AddMilliseconds(-1); String sqlStr1 = @" INSERT INTO AJZ_RPT022 SELECT * FROM (SELECT PC.工号 AS 工号1 , PC.姓名 AS 姓名1, NVL(PC.产品编码,PD.产品编码) AS 产品编码1, PC.出窑数, PC.出窑数 - NVL( PD.合计, 0 ) AS 合格数, --ROUND( (PC.出窑数 - NVL( PD.合计, 0 )) / DECODE( PC.出窑数, 0, 1, PC.出窑数 ), 4 ) * 100 AS 合格率, TO_CHAR(100 - ROUND( (PC.出窑数 - NVL( PD.合计, 0 )) / DECODE( PC.出窑数, 0, 1, PC.出窑数 ), 4 ) * 100) || '%' AS 缺陷率, PD.* , to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM ( SELECT * FROM ( SELECT --GID, DECODE( GID, 6, USERCODE || '合计', 7, USERCODE || '合计', USERCODE ) AS 工号, USERNAME AS 姓名, GOODSCODE AS 产品编码, DECODE( GID, 1, '合计', 3, '合计', 7, '合计', DEFECT ) AS DEFECT, CNUM FROM ( SELECT GROUPING_ID ( U.USERCODE, P.GOODSCODE, D.DEFECTID ) AS GID, U.USERCODE, U.USERNAME, P.GOODSCODE, D.DEFECTID, M.DEFECTNAME AS DEFECT, COUNT( * ) AS CNUM FROM TP_PM_PRODUCTIONDATA P INNER JOIN TP_PC_GROUTINGLINE L ON P.GROUTINGLINEID = L.GROUTINGLINEID INNER JOIN TP_PM_GROUTINGDAILY G ON G.GROUTINGDAILYID = P.GROUTINGDAILYID --LEFT JOIN TP_MST_USER MU ON MU.USERID = L.MONITORID INNER JOIN TP_PM_DEFECT D ON P.PRODUCTIONDATAID = D.PRODUCTIONDATAID INNER JOIN TP_MST_DEFECT M ON ( M.DEFECTID = D.DEFECTID AND M.DEFECTID > 0 ) LEFT JOIN TP_MST_DEFECTTYPE T ON T.DEFECTTYPEID = M.DEFECTTYPEID INNER JOIN TP_PM_PRODUCTIONDATA PB ON P.BARCODE = PB.BARCODE INNER JOIN TP_MST_USER U ON U.USERID = PB.USERID WHERE P.VALUEFLAG = '1' AND P.CHECKBATCHNO = 1 AND P.PROCEDUREID = 13 AND P.GOODSLEVELTYPEID <> 4 AND P.CHECKTIME >= @DATEBEGIN@ AND P.CHECKTIME <= @DATEEND@ AND P.ISREFIRE = '0' --AND L.BUILDINGNO = @BUILDINGNO@ AND ( PB.PROCEDUREID = 1 ) AND (D.DEFECTPROCEDUREID IN (2,25)) AND (P.GOODSCODE = NULL OR NULL IS NULL) AND (U.USERCODE = '' OR '' IS NULL) GROUP BY GROUPING SETS ( ( U.USERCODE, U.USERNAME, P.GOODSCODE, D.DEFECTID, M.DEFECTNAME ), ( U.USERCODE, U.USERNAME, P.GOODSCODE ), ( D.DEFECTID, M.DEFECTNAME ), ( ) ) ORDER BY USERCODE, GROUPING_ID ( U.USERCODE, P.GOODSCODE, D.DEFECTID ) ) ) PIVOT ( SUM( CNUM ) FOR DEFECT IN ( '糙活' AS 糙活,'成裂' AS 成裂,'成脏' AS 成脏,'成走' AS 成走,'孔不良' AS 孔不良,'泥绺' AS 泥绺,'坯脏' AS 坯脏,'注泡' AS 注泡,'棕眼' AS 棕眼,'漏气' AS 漏气,'卡球' AS 卡球,'炸圈' AS 炸圈,'排水不良' AS 排水不良, '合计' AS 合计 ) ) ORDER BY 工号, 产品编码 ) PD RIGHT JOIN ( SELECT GID, DECODE(GID,3,'合计',工号) AS 工号, T.姓名, T.产品编码, T.出窑数 FROM ( SELECT GROUPING_ID ( U.USERCODE, P.GOODSCODE ) AS GID, U.USERCODE AS 工号, U.USERNAME AS 姓名, P.GOODSCODE AS 产品编码, COUNT( * ) AS 出窑数 FROM TP_PM_PRODUCTIONDATA P INNER JOIN TP_PC_GROUTINGLINE L ON P.GROUTINGLINEID = L.GROUTINGLINEID INNER JOIN TP_PM_GROUTINGDAILY G ON G.GROUTINGDAILYID = P.GROUTINGDAILYID --LEFT JOIN TP_MST_USER MU ON MU.USERID = L.MONITORID INNER JOIN TP_PM_PRODUCTIONDATA PB ON P.BARCODE = PB.BARCODE INNER JOIN TP_MST_USER U ON U.USERID = PB.USERID WHERE P.VALUEFLAG = '1' AND P.PROCEDUREID = 13 AND P.CHECKBATCHNO = 1 AND P.CHECKTIME >= @DATEBEGIN@ AND P.CHECKTIME <= @DATEEND@ --AND L.BUILDINGNO = @BUILDINGNO@ AND ( PB.PROCEDUREID = 1 ) AND P.ISREFIRE = '0' AND PB.VALUEFLAG='1' AND (P.GOODSCODE = NULL OR NULL IS NULL) AND (U.USERCODE = '' OR '' IS NULL) GROUP BY GROUPING SETS ( ( U.USERCODE, U.USERNAME, P.GOODSCODE ),() ) ) T ORDER BY 工号, 产品编码 ) PC ON (PC.GID = 3 AND PD.工号 = PC.工号 AND PD.产品编码 IS NULL ) OR ( PD.工号 = PC.工号 AND PD.产品编码 = PC.产品编码 )) A WHERE A.姓名1 IS NOT NULL "; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result = conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); } conn.Commit(); if (result > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export022修干坯缺陷统计表结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export022修干坯缺陷统计表结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export022修干坯缺陷统计表结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export024() { using (IDataAccess conn = DataAccess.Create()) { try { conn.Open(); conn.BeginTransaction(); //2021-07-12 dongyan 修正什么问题 int result = 0; int m = DateTime.Now.AddMonths(-1).Month; DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); String sqlStr = @"delete AJZ_RPT024 WHERE FORWARD_TIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1).AddMilliseconds(-1); String sqlStr1 = @" INSERT INTO AJZ_RPT024 -- SELECT * FROM ( SELECT NVL(PN.日期,PN2.日期) AS 日期, NVL(PN.楼号,PN2.楼号) AS 楼号, DECODE(PN.工序,13,'本烧',35,'重烧','') AS 工序, NVL(PN.车数,0) AS 车数, NVL(PN.出窑数量,0) AS 出窑数量, NVL((PN.合格数量 + PN2.回收合格),0) AS 合格数量, NVL(ROUND(( PN.合格数量 + PN2.回收合格)/DECODE(PN.出窑数量,0,1,PN.出窑数量)*100,2),0)||'%' AS 合格率, NVL(ROUND(PN.总产值/DECODE(PN.车数,0,1,PN.车数),2),0) AS 单车产值, NVL(PN.双A数量,0) AS 双A数量, NVL(PN.出窑数量 - (PN.合格数量 + PN2.回收合格),0) AS 废品数量, NVL(ROUND((PN.座便_合格数量+PN2.座便_回收合格)/DECODE(PN.合格数量+PN2.回收合格,0,1,DECODE(PN.合格数量,0,1,PN.合格数量)),2),0) AS 座便比例, NVL(PN.连体_出窑数量,0) AS 连体_出窑数量, NVL(PN.连体_合格数量 + PN2.连体_回收合格,0) AS 连体_回收合格, NVL(ROUND((PN.连体_合格数量 + PN2.连体_回收合格)/DECODE(PN.连体_出窑数量,0,1,PN.连体_出窑数量)*100,2),0)||'%' AS 连体_合格率, NVL(PN.座便_出窑数量,0) AS 座便_出窑数量, NVL(PN.座便_合格数量 + PN2.座便_回收合格,0) AS 座便_合格数量, NVL(ROUND((PN.座便_合格数量+PN2.座便_回收合格)/DECODE(PN.座便_出窑数量,0,1,PN.座便_出窑数量)*100,2),0)||'%' AS 座便_合格率, to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')), NVL(PN.总产值,0) AS 总产值, NVL(ROUND(PN.总产值/DECODE(PN.合格数量+PN2.回收合格,0,1,PN.合格数量+PN2.回收合格),2),0) AS 平均单价 FROM ( SELECT DECODE( GROUPING_ID ( C.CDATE, C.BUILDINGNO ), 0, TO_CHAR( C.CDATE, 'YYYY-MM-DD' ), '合计' ) AS 日期, DECODE( GROUPING_ID ( C.CDATE, C.BUILDINGNO ), 0, C.BUILDINGNO, '--' ) AS 楼号, NVL(C.PROCEDUREID,0) AS 工序, COUNT(DISTINCT C.KILNCARBATCHNO) AS 车数, SUM( CASE WHEN C.PROCEDUREID IN ( 13,35 ) THEN C.CNUM ELSE 0 END ) AS 出窑数量, SUM( CASE WHEN C.PROCEDUREID IN ( 13,35 ) AND C.GOODSLEVELTYPEID IN ( 4, 5 ) AND C.P2 IS NULL THEN C.CNUM ELSE 0 END ) AS 合格数量, SUM( CASE WHEN C.PROCEDUREID IN ( 13,35 ) AND C.GOODSLEVELTYPEID IN ( 5 ) AND C.P2 IS NOT NULL THEN C.CNUM ELSE 0 END ) AS 双A数量, SUM(C.UNITPRICE) AS 总产值, SUM( CASE WHEN C.PROCEDUREID IN ( 13,35 ) AND C.GOODSTYPEID = 6 THEN C.CNUM ELSE 0 END ) AS 连体_出窑数量, SUM( CASE WHEN C.PROCEDUREID IN ( 13,35 ) AND C.GOODSTYPEID = 6 AND C.GOODSLEVELTYPEID IN ( 4, 5 ) AND C.P2 IS NULL THEN C.CNUM ELSE 0 END ) AS 连体_合格数量, SUM( CASE WHEN C.PROCEDUREID IN ( 13,35 ) AND C.GOODSTYPEID = 5 THEN C.CNUM ELSE 0 END ) AS 座便_出窑数量, SUM( CASE WHEN C.PROCEDUREID IN ( 13,35 ) AND C.GOODSTYPEID = 5 AND C.GOODSLEVELTYPEID IN ( 4, 5 ) AND C.P2 IS NULL THEN C.CNUM ELSE 0 END ) AS 座便_合格数量 FROM ( SELECT T.CDATE, T.GROUTINGLINEID, T.PROCEDUREID, T.GOODSLEVELTYPEID, T.PRODUCTIONDATAID, T.MAXPRODUCTIONDATAID, T.CNUM, L.BUILDINGNO, P2.PROCEDUREID P2, G.UNITPRICE, G.GOODSTYPEID, T.KILNCARID, T.KILNCARBATCHNO FROM ( SELECT TRUNC( P.CHECKTIME ) AS CDATE, P.GROUTINGLINEID, P.PROCEDUREID, P.GOODSLEVELTYPEID, P.PRODUCTIONDATAID, MAX( PMAX.PRODUCTIONDATAID ) MAXPRODUCTIONDATAID, P.GOODSID, P.KILNCARID, P.KILNCARBATCHNO, 1 AS CNUM FROM TP_PM_PRODUCTIONDATA P LEFT JOIN TP_PM_PRODUCTIONDATA PMAX ON PMAX.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID AND PMAX.PRODUCTIONDATAID < P.PRODUCTIONDATAID AND PMAX.VALUEFLAG = '1' WHERE P.VALUEFLAG = '1' AND P.CHECKBATCHNO = 1 AND P.PROCEDUREID IN ( 13,35 ) AND P.CHECKTIME >= @DATEBEGIN@ AND P.CHECKTIME <= @DATEEND@ GROUP BY P.CHECKTIME, P.GROUTINGLINEID, P.PROCEDUREID, P.GOODSLEVELTYPEID, P.PRODUCTIONDATAID, P.GOODSID, P.KILNCARID, P.KILNCARBATCHNO ) T LEFT JOIN TP_PC_GROUTINGLINE L ON T.GROUTINGLINEID = L.GROUTINGLINEID LEFT JOIN TP_PM_PRODUCTIONDATA P2 ON ( P2.PRODUCTIONDATAID = T.MAXPRODUCTIONDATAID AND P2.PROCEDUREID IN ( 22, 23 ) ) LEFT JOIN TP_MST_GOODS G ON T.GOODSID = G.GOODSID LEFT JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID WHERE ( L.BUILDINGNO = '' OR '' IS NULL) ) C GROUP BY GROUPING SETS ( ( C.CDATE, C.BUILDINGNO, C.PROCEDUREID ), ( ) ) ) PN FULL JOIN ( SELECT DECODE( GROUPING_ID ( C.CDATE, C.BUILDINGNO ), 0, TO_CHAR( C.CDATE, 'YYYY-MM-DD' ), '合计' ) AS 日期, DECODE( GROUPING_ID ( C.CDATE, C.BUILDINGNO ), 0, C.BUILDINGNO, '--' ) AS 楼号, NVL(C.PROCEDUREID,0) AS 工序, SUM( CASE WHEN C.PROCEDUREID IN (13,35) AND C.GOODSLEVELTYPEID IN (4, 5) AND C.P2 IN (22,23) THEN C.CNUM ELSE 0 END) AS 回收合格, SUM( CASE WHEN C.PROCEDUREID IN (13,35) AND C.GOODSTYPEID = 6 AND C.GOODSLEVELTYPEID IN ( 4, 5 ) AND C.P2 IN(22,23) THEN C.CNUM ELSE 0 END ) AS 连体_回收合格, SUM( CASE WHEN C.PROCEDUREID IN (13,35) AND C.GOODSTYPEID = 5 AND C.GOODSLEVELTYPEID IN ( 4, 5 ) AND C.P2 IN(22,23) THEN C.CNUM ELSE 0 END ) AS 座便_回收合格 FROM ( SELECT T.CDATE, T.GROUTINGLINEID, T.PROCEDUREID, T.GOODSLEVELTYPEID, T.PRODUCTIONDATAID, T.MAXPRODUCTIONDATAID, T.CNUM, L.BUILDINGNO, P2.PROCEDUREID P2, G.UNITPRICE, G.GOODSTYPEID, T.KILNCARID, T.KILNCARBATCHNO FROM ( SELECT TRUNC( P.CREATETIME ) AS CDATE, P.GROUTINGLINEID, P.PROCEDUREID, P.GOODSLEVELTYPEID, P.PRODUCTIONDATAID, MAX( PMAX.PRODUCTIONDATAID ) MAXPRODUCTIONDATAID, P.GOODSID, P.KILNCARID, P.KILNCARBATCHNO, 1 AS CNUM FROM TP_PM_PRODUCTIONDATA P LEFT JOIN TP_PM_PRODUCTIONDATA PMAX ON PMAX.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID AND PMAX.PRODUCTIONDATAID < P.PRODUCTIONDATAID AND PMAX.VALUEFLAG = '1' WHERE P.VALUEFLAG = '1' AND P.CHECKBATCHNO = 1 AND P.PROCEDUREID IN ( 13,35 ) AND P.CREATETIME >= @DATEBEGIN@ AND P.CREATETIME <= @DATEEND@ GROUP BY P.CREATETIME, P.GROUTINGLINEID, P.PROCEDUREID, P.GOODSLEVELTYPEID, P.PRODUCTIONDATAID, P.GOODSID, P.KILNCARID, P.KILNCARBATCHNO ) T LEFT JOIN TP_PC_GROUTINGLINE L ON T.GROUTINGLINEID = L.GROUTINGLINEID LEFT JOIN TP_PM_PRODUCTIONDATA P2 ON ( P2.PRODUCTIONDATAID = T.MAXPRODUCTIONDATAID AND P2.PROCEDUREID IN ( 22, 23 ) ) LEFT JOIN TP_MST_GOODS G ON T.GOODSID = G.GOODSID LEFT JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID WHERE ( L.BUILDINGNO = '' OR '' IS NULL) ) C GROUP BY GROUPING SETS ( ( C.CDATE, C.BUILDINGNO, C.PROCEDUREID ), ( ) ) ) PN2 ON PN.日期 = PN2.日期 AND PN.楼号 = PN2.楼号 AND PN.工序 = PN2.工序 ORDER BY 日期 ASC -- ) A WHERE A.工序 IS NOT NULL) "; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result += conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); } conn.Commit(); if (result > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export024生产进度表结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export024生产进度表结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export024生产进度表结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export025() { using (IDataAccess conn = DataAccess.Create()) { try { conn.Open(); conn.BeginTransaction(); //2021-07-12 dongyan 修正什么问题 int result = 0; int m = DateTime.Now.AddMonths(-1).Month; DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); string sqlStr = @"delete JZ_RPT025 WHERE FORWARD_TIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1).AddMilliseconds(-1); string sqlStr1 = @" insert INTO JZ_RPT025 SELECT MU.USERNAME 组别, GL.GROUTINGLINENAME 线号, LISTAGG ( TO_CHAR( GP.GOODSMODEL ), '/' ) WITHIN GROUP ( ORDER BY GP.GOODSMODEL ) 产品型号, LISTAGG ( TO_CHAR( GU.USERCODE ), '/' ) WITHIN GROUP ( ORDER BY GU.USERCODE ) 成型工号, LISTAGG ( TO_CHAR( GU.USERNAME ), '/' ) WITHIN GROUP ( ORDER BY GU.USERCODE ) 姓名, MAX( GP.MG_COUNT ) 注浆次数 , to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM ( SELECT T.GROUTINGLINEID, G.GOODSMODEL, GDD.USERID, MAX( GLD.GROUTINGCOUNT ) MG_COUNT FROM TP_PC_GMOULDRECORD T LEFT JOIN TP_PC_GROUTINGLINEDETAIL GLD ON ( T.GMOULDRECORDTYPE = 6 AND GLD.LASTGMOULDRECORDID = T.GMOULDRECORDID ) OR ( T.GMOULDRECORDTYPE <> 6 AND T.GROUTINGLINEDETAILID = GLD.GROUTINGLINEDETAILID ) LEFT JOIN TP_MST_GOODS G1 ON G1.GOODSID = T.GOODSID LEFT JOIN TP_MST_GOODS G2 ON G2.GOODSID = T.CHANGEDGOODSID INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGLINEDETAILID = GLD.GROUTINGLINEDETAILID AND GDD.GROUTINGCOUNT = GLD.GROUTINGCOUNT LEFT JOIN TP_MST_GOODS G ON G.GOODSID = GLD.GOODSID WHERE T.CREATETIME >= @DATEBEGIN@ AND T.CREATETIME <= @DATEEND@ AND GLD.GROUTINGCOUNT > 0 AND T.GMOULDRECORDTYPE IN ( 3, 5, 6, 7 ) AND ( T.GMOULDRECORDTYPE <> 5 OR ( G1.GOODSMODEL <> G2.GOODSMODEL ) ) GROUP BY T.GROUTINGLINEID, G.GOODSMODEL, GDD.USERID ) GP INNER JOIN TP_PC_GROUTINGLINE GL ON GL.GROUTINGLINEID = GP.GROUTINGLINEID LEFT JOIN TP_MST_USER MU ON MU.USERID = GL.MONITORID --INNER JOIN TP_MST_GOODS G ON G.GOODSID = GP.GOODSID LEFT JOIN TP_MST_USER GU ON GU.USERID = GP.USERID WHERE GP.MG_COUNT >= 70 GROUP BY MU.USERNAME, GL.GROUTINGLINENAME "; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result = conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); } conn.Commit(); if (result > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export025历史注浆次数表结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export025历史注浆次数表结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export025历史注浆次数表结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export027() { using (IDataAccess conn = DataAccess.Create()) { try { conn.Open(); conn.BeginTransaction(); //2021-8-2 陈强 string sqlStrc = @"delete JZ_RPT027 where FORWARDTIME = @c@ "; string sqlStra = @" delete JZ_RPT027_D where FORWARDTIME = @a@ "; int result = 0; int m = DateTime.Now.AddMonths(-1).Month; string mouth = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string year = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(year + m.ToString().PadLeft(2, '0')); conn.ExecuteNonQuery(sqlStrc, new CDAParameter("c", c)); conn.ExecuteNonQuery(sqlStra, new CDAParameter("a", c)); DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(year + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1); //总单 String sqlStr1 = @" INSERT INTO JZ_RPT027 SELECT * FROM (" + "SELECT to_char(@DATEOUT@, 'yyyy-mm-dd') AS \"审核日期\"\n" + " ,to_char(@DATEOUT@, 'yyyy-mm-dd') AS \"日期\"\n" + " ,to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0'))" + " ,'张迪' AS \"制单人_FName\"\n" + " ,'' AS \"编号\"\n" + " ,'张迪' AS \"审核人_FName\"\n" + " ,CASE\n" + " WHEN SUM(T.DNUM) < 0 THEN\n" + " '1'\n" + " ELSE\n" + " '0'\n" + " END AS \"红蓝字\"\n" + " ,'' AS \"单据号\"\n" + " ,T.FNAME_CODE AS \"领料部门_FNumber\"\n" + " ,T.FNAME AS \"领料部门_FName\"\n" + " ,'' AS \"用途\"\n" + " ,'01.094' AS \"领料_FNumber\"\n" + " ,'张迪' AS \"领料_FName\"\n" + " ,'01.094' AS \"发货_FNumber\"\n" + " ,'张迪' AS \"发货_FName\"\n" + " ,'01.001' AS \"客户_FNumber\"\n" + " ,'唐山中陶实业有限公司' AS \"客户_FName\"\n" + " ,'' AS \"出库类型_FNumber\"\n" + " ,'0'" + " FROM (\n" + " SELECT -1 AS DNUM\n" + " ,CASE\n" + " WHEN TS.PROCEDUREID IN (26, 27, 38) THEN\n" + " '精坯库（一期）'\n" + " WHEN TS.PROCEDUREID IN (3, 39) THEN\n" + //" '精坯库（二期）'\n" + " '精坯库（一期）'\n" + " WHEN TS.PROCEDUREID IN (28, 29, 30, 32, 33) THEN\n" + " '釉坯库（一期）'\n" + " WHEN TS.PROCEDUREID IN (24, 4,5, 6, 10, 16, 34) THEN\n" + //" '釉坯库（二期）'\n" + " '釉坯库（一期）'\n" + " WHEN TS.PROCEDUREID = 31 OR\n" + " (TS.PROCEDUREID = 9 AND PD.KILNID IN (1, 2)) THEN\n" + " '窑坯（一期）'\n" + " WHEN TS.PROCEDUREID = 21 OR\n" + " (TS.PROCEDUREID = 9 AND PD.KILNID IN (3, 4)) THEN\n" + //" '窑坯（二期）'\n" + " '窑坯（一期）'\n" + " ELSE\n" + " NULL\n" + " END FNAME_FLAG\n" + " ,CASE\n" + " WHEN TS.PROCEDUREID IN (26, 27, 38) THEN\n" + " '009'\n" + " WHEN TS.PROCEDUREID IN (3, 39) THEN\n" + //" '035'\n" + " '009'\n" + " WHEN TS.PROCEDUREID IN (28, 29, 30, 32, 33) THEN\n" + " '010'\n" + " WHEN TS.PROCEDUREID IN (24, 4,5, 6, 10, 16, 34) THEN\n" + //" '039'\n" + " '010'\n" + " WHEN TS.PROCEDUREID = 31 OR\n" + " (TS.PROCEDUREID = 9 AND PD.KILNID IN (1, 2)) THEN\n" + " '011'\n" + " WHEN TS.PROCEDUREID = 21 OR\n" + " (TS.PROCEDUREID = 9 AND PD.KILNID IN (3, 4)) THEN\n" + //" '038'\n" + " '011'\n" + " ELSE\n" + " NULL\n" + " END FNAME_CODE\n" + " ,CASE\n" + " WHEN TS.PROCEDUREID IN (26, 27, 38) THEN\n" + " '一期成型车间'\n" + " WHEN TS.PROCEDUREID IN (3, 39) THEN\n" + //" '二期成型车间'\n" + " '一期成型车间'\n" + " WHEN TS.PROCEDUREID IN (28, 29, 30, 32, 33) THEN\n" + " '一期施釉车间'\n" + " WHEN TS.PROCEDUREID IN (24, 4,5, 6, 10, 16, 34) THEN\n" + //" '二期施釉车间'\n" + " '一期施釉车间'\n" + " WHEN TS.PROCEDUREID = 31 OR\n" + " (TS.PROCEDUREID = 9 AND PD.KILNID IN (1, 2)) THEN\n" + " '一期烧成车间'\n" + " WHEN TS.PROCEDUREID = 21 OR\n" + " (TS.PROCEDUREID = 9 AND PD.KILNID IN (3, 4)) THEN\n" + //" '二期烧成车间'\n" + " '一期烧成车间'\n" + " ELSE\n" + " NULL\n" + " END FNAME\n" + " FROM TP_PM_SCRAPPRODUCT TS\n" + " INNER JOIN TP_PM_PRODUCTIONDATA PD\n" + " ON TS.PRODUCTIONDATAID = PD.PRODUCTIONDATAID\n" + " WHERE TS.AUDITSTATUS = 1\n" + " AND TS.GOODSLEVELTYPEID = 8\n" + " AND TS.BACKOUTTIME >= @DATEBEGIN@\n" + " AND TS.BACKOUTTIME < @DATEEND@\n" + " AND TS.PROCEDUREID IN (3\n" + " ,26\n" + " ,27\n" + " ,38\n" + " ,39\n" + " ,28\n" + " ,29\n" + " ,30\n" + " ,32\n" + " ,33\n" + " ,24\n" + " ,4,5\n" + " ,6\n" + " ,10\n" + " ,16, 34\n" + " ,21\n" + " ,31\n" + //" ,9)) T\n" + " ,9)\n" + " UNION ALL\n" + // 产品清除 " SELECT -1 AS DNUM\n" + " ,CASE\n" + " WHEN TS.OTHERID IN (26, 27, 38) THEN\n" + " '精坯库（一期）'\n" + " WHEN TS.OTHERID IN (3, 39) THEN\n" + //" '精坯库（二期）'\n" + " '精坯库（一期）'\n" + " WHEN TS.OTHERID IN (28, 29, 30, 32, 33) THEN\n" + " '釉坯库（一期）'\n" + " WHEN TS.OTHERID IN (24, 4,5, 6, 10, 16, 34) THEN\n" + //" '釉坯库（二期）'\n" + " '釉坯库（一期）'\n" + " WHEN TS.OTHERID = 31 OR\n" + " (TS.OTHERID = 9 AND PD.KILNID IN (1, 2)) THEN\n" + " '窑坯（一期）'\n" + " WHEN TS.OTHERID = 21 OR\n" + " (TS.OTHERID = 9 AND PD.KILNID IN (3, 4)) THEN\n" + //" '窑坯（二期）'\n" + " '窑坯（一期）'\n" + " ELSE\n" + " NULL\n" + " END FNAME_FLAG\n" + " ,CASE\n" + " WHEN TS.OTHERID IN (26, 27, 38) THEN\n" + " '009'\n" + " WHEN TS.OTHERID IN (3, 39) THEN\n" + //" '035'\n" + " '009'\n" + " WHEN TS.OTHERID IN (28, 29, 30, 32, 33) THEN\n" + " '010'\n" + " WHEN TS.OTHERID IN (24, 4,5, 6, 10, 16, 34) THEN\n" + //" '039'\n" + " '010'\n" + " WHEN TS.OTHERID = 31 OR\n" + " (TS.OTHERID = 9 AND PD.KILNID IN (1, 2)) THEN\n" + " '011'\n" + " WHEN TS.OTHERID = 21 OR\n" + " (TS.OTHERID = 9 AND PD.KILNID IN (3, 4)) THEN\n" + //" '038'\n" + " '011'\n" + " ELSE\n" + " NULL\n" + " END FNAME_CODE\n" + " ,CASE\n" + " WHEN TS.OTHERID IN (26, 27, 38) THEN\n" + " '一期成型车间'\n" + " WHEN TS.OTHERID IN (3, 39) THEN\n" + //" '二期成型车间'\n" + " '一期成型车间'\n" + " WHEN TS.OTHERID IN (28, 29, 30, 32, 33) THEN\n" + " '一期施釉车间'\n" + " WHEN TS.OTHERID IN (24, 4,5, 6, 10, 16, 34) THEN\n" + //" '二期施釉车间'\n" + " '一期施釉车间'\n" + " WHEN TS.OTHERID = 31 OR\n" + " (TS.OTHERID = 9 AND PD.KILNID IN (1, 2)) THEN\n" + " '一期烧成车间'\n" + " WHEN TS.OTHERID = 21 OR\n" + " (TS.OTHERID = 9 AND PD.KILNID IN (3, 4)) THEN\n" + //" '二期烧成车间'\n" + " '一期烧成车间'\n" + " ELSE\n" + " NULL\n" + " END FNAME\n" + " FROM TP_PM_GOODSCHANGEHISTORY TS\n" + " LEFT JOIN TP_PM_PRODUCTIONDATA PD\n" + " ON TS.GROUTINGDAILYDETAILID = PD.GROUTINGDAILYDETAILID AND TS.OTHERID = 9 AND PD.PROCEDUREID=9\n" + " WHERE TS.DATATYPE = 12\n" + " AND TS.CREATETIME >= @DATEBEGIN@\n" + " AND TS.CREATETIME < @DATEEND@\n" + " AND TS.OTHERID IN (3\n" + " ,26\n" + " ,27\n" + " ,38\n" + " ,39\n" + " ,28\n" + " ,29\n" + " ,30\n" + " ,32\n" + " ,33\n" + " ,24\n" + " ,4,5\n" + " ,6\n" + " ,10\n" + " ,16, 34\n" + " ,21\n" + " ,31\n" + " ,9)) T\n" + // " WHERE (@FNAME_FLAG@ IS NULL OR T.FNAME_FLAG = @FNAME_FLAG@) \n" + " GROUP BY T.FNAME_CODE, T.FNAME, T.FNAME_FLAG\n" + " )"; List sqlPara1 = new List(); sqlPara1.Add(new CDAParameter("DATEOUT", dayEnd.AddDays(-1))); sqlPara1.Add(new CDAParameter("I", i)); sqlPara1.Add(new CDAParameter("YEAR", year)); sqlPara1.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara1.Add(new CDAParameter("DATEBEGIN", dayBegin)); result += conn.ExecuteNonQuery(sqlStr1, sqlPara1.ToArray()); //明细 string sqlStr2 = @" INSERT INTO JZ_RPT027_D SELECT * FROM (" + "SELECT '' AS 行号\n" + " ,'' AS \"单据号_FBillno\"\n" + " ,'' AS \"单据号_FTrantype\"\n" + " ,K.K_CODE AS \"产品代码_FNumber\"\n" + " ,T.GOODS_CODE AS \"产品代码_FName\"\n" + " ,'' AS \"产品代码_FModel\"\n" + " ,'' AS \"辅助属性_FNumber\"\n" + " ,'' AS \"辅助属性_FName\"\n" + " ,'*' AS \"辅助属性_FClassName\"\n" + " ,'件' AS \"单位_FNumber\"\n" + " ,'件' AS \"单位_FName\"\n" + " ,0 AS \"单价\"\n" + " ,SUM(T.DNUM) AS \"数量\"\n" + " ,0 AS \"金额\"\n" + " ,'破损' AS \"备注\"\n" + " ,SUM(T.DNUM) AS \"基本单位数量\"\n" + " ,'' AS \"生产_采购日期\"\n" + " ,'' AS \"发货仓库_FNumber\"\n" + " ,T.FNAME AS \"发货仓库_FName\"\n" + " ,'' AS \"仓位_FName\"\n" + " ,'' AS \"仓位_FGroupName\"\n" + " ,to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0'))" + " ,'0'" + " FROM (\n" + " SELECT CASE\n" + " WHEN TS.PROCEDUREID IN (3, 26, 27, 38, 39) THEN\n" + " 'J'\n" + " WHEN TS.PROCEDUREID IN (21, 31, 9) THEN\n" + " 'S'\n" + " ELSE\n" + " 'Y'\n" + " END || G.GOODSMODEL AS GOODS_CODE\n" + " ,-1 AS DNUM\n" + " ,CASE\n" + " WHEN TS.PROCEDUREID IN (26, 27, 38) THEN\n" + " '精坯库（一期）'\n" + " WHEN TS.PROCEDUREID IN (3, 39) THEN\n" + //" '精坯库（二期）'\n" + " '精坯库（一期）'\n" + " WHEN TS.PROCEDUREID IN (28, 29, 30, 32, 33) THEN\n" + " '釉坯库（一期）'\n" + " WHEN TS.PROCEDUREID IN (24, 4,5, 6, 10, 16, 34) THEN\n" + //" '釉坯库（二期）'\n" + " '釉坯库（一期）'\n" + " WHEN TS.PROCEDUREID = 31 OR\n" + " (TS.PROCEDUREID = 9 AND PD.KILNID IN (1, 2)) THEN\n" + " '窑坯（一期）'\n" + " WHEN TS.PROCEDUREID = 21 OR\n" + " (TS.PROCEDUREID = 9 AND PD.KILNID IN (3, 4)) THEN\n" + //" '窑坯（二期）'\n" + " '窑坯（一期）'\n" + " ELSE\n" + " NULL\n" + " END FNAME\n" + " FROM TP_PM_SCRAPPRODUCT TS\n" + " INNER JOIN TP_MST_GOODS G\n" + " ON TS.GOODSID = G.GOODSID\n" + " INNER JOIN TP_PM_PRODUCTIONDATA PD\n" + " ON TS.PRODUCTIONDATAID = PD.PRODUCTIONDATAID\n" + " WHERE TS.AUDITSTATUS = 1\n" + " AND TS.GOODSLEVELTYPEID = 8\n" + " AND TS.BACKOUTTIME >= @DATEBEGIN@\n" + " AND TS.BACKOUTTIME < @DATEEND@\n" + " AND TS.PROCEDUREID IN (3\n" + " ,26\n" + " ,27\n" + " ,38\n" + " ,39\n" + " ,28\n" + " ,29\n" + " ,30\n" + " ,32\n" + " ,33\n" + " ,24\n" + " ,4,5\n" + " ,6\n" + " ,10\n" + " ,16, 34\n" + " ,21\n" + " ,31\n" + " ,9)\n" + " UNION ALL\n" + // 产品清除 " SELECT CASE\n" + " WHEN TS.OTHERID IN (3, 26, 27, 38, 39) THEN\n" + " 'J'\n" + " WHEN TS.OTHERID IN (21, 31, 9) THEN\n" + " 'S'\n" + " ELSE\n" + " 'Y'\n" + " END || G.GOODSMODEL AS GOODS_CODE\n" + " ,-1 AS DNUM\n" + " ,CASE\n" + " WHEN TS.OTHERID IN (26, 27, 38) THEN\n" + " '精坯库（一期）'\n" + " WHEN TS.OTHERID IN (3, 39) THEN\n" + //" '精坯库（二期）'\n" + " '精坯库（一期）'\n" + " WHEN TS.OTHERID IN (28, 29, 30, 32, 33) THEN\n" + " '釉坯库（一期）'\n" + " WHEN TS.OTHERID IN (24, 4,5, 6, 10, 16, 34) THEN\n" + //" '釉坯库（二期）'\n" + " '釉坯库（一期）'\n" + " WHEN TS.OTHERID = 31 OR\n" + " (TS.OTHERID = 9 AND PD.KILNID IN (1, 2)) THEN\n" + " '窑坯（一期）'\n" + " WHEN TS.OTHERID = 21 OR\n" + " (TS.OTHERID = 9 AND PD.KILNID IN (3, 4)) THEN\n" + //" '窑坯（二期）'\n" + " '窑坯（一期）'\n" + " ELSE\n" + " NULL\n" + " END FNAME\n" + " FROM TP_PM_GOODSCHANGEHISTORY TS\n" + " INNER JOIN TP_MST_GOODS G\n" + " ON TS.GOODSID = G.GOODSID\n" + " LEFT JOIN TP_PM_PRODUCTIONDATA PD\n" + " ON TS.GROUTINGDAILYDETAILID = PD.GROUTINGDAILYDETAILID AND TS.OTHERID = 9 AND PD.PROCEDUREID=9\n" + " WHERE TS.DATATYPE = 12\n" + " AND TS.CREATETIME >= @DATEBEGIN@\n" + " AND TS.CREATETIME < @DATEEND@\n" + " AND TS.OTHERID IN (3\n" + " ,26\n" + " ,27\n" + " ,38\n" + " ,39\n" + " ,28\n" + " ,29\n" + " ,30\n" + " ,32\n" + " ,33\n" + " ,24\n" + " ,4,5\n" + " ,6\n" + " ,10\n" + " ,16, 34\n" + " ,21\n" + " ,31\n" + " ,9)" + " ) T\n" + " LEFT JOIN TA_IMEX_kingdee_Goodscode K ON T.GOODS_CODE = K.K_NAME AND T.FNAME = K.K_WAREHOUSE\n" + // " WHERE (@FNAME_FLAG@ IS NULL OR T.FNAME = @FNAME_FLAG@) \n" + " GROUP BY K.K_CODE, T.GOODS_CODE, T.FNAME\n" + " )"; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", year)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result += conn.ExecuteNonQuery(sqlStr2, sqlPara.ToArray()); } conn.Commit(); if (result > 0) { Curtain.Log.Logger.Debug(year + "年" + mouth + "月:export027其他出库退单结转成功！"); } else { Curtain.Log.Logger.Debug(year + "年" + mouth + "月:export027其他出库退单结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export027其他出库退单结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export028() { using (IDataAccess conn = DataAccess.Create()) { //2021-08-2 dongyan 修正什么问题 int result = 0; int result1 = 0; int m = DateTime.Now.AddMonths(-1).Month; DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + m.ToString().PadLeft(2, '0')); string dayOut = b + "-" + a + "-" + DateTime.DaysInMonth(Convert.ToInt32(b), m).ToString(); conn.Open(); conn.BeginTransaction(); try { String sqlStr = @"delete JZ_RPT028_M WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); String Str = @"delete JZ_RPT028_D WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(Str, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1); String sqlStr2 = @"INSERT INTO JZ_RPT028_D " + "SELECT * FROM ( SELECT '' AS 序列号内码\n" + " ,'' AS 行号\n" + " ,'' AS \"单据号_FBillno\"\n" + " ,'' AS \"单据号_FTrantype\"\n" + " ,K.K_CODE AS \"物料代码_FNumber\"\n" + " ,T.GOODS_CODE AS \"物料代码_FName\"\n" + " ,'' AS \"物料代码_FModel\"\n" + " ,'' AS \"辅助属性_FNumber\"\n" + " ,'' AS \"辅助属性_FName\"\n" + " ,'*' AS \"辅助属性_FClassName\"\n" + " ,K2.K_CODE AS \"成本对象_FNumber\"\n" + //" ,decode(T.P_CODE,'J','Y','Y','S') || T.GOODS_CODE AS \"成本对象_FName\"\n" + " ,T.GOODS_CODE2 AS \"成本对象_FName\"\n" + " ,'' AS \"成本对象_FItemClassID\"\n" + " ,'*' AS \"成本对象组_FNumber\"\n" + " ,'*' AS \"成本对象组_FName\"\n" + " ,'0' AS \"成本对象组_FItemClassID\"\n" + " ,'件' AS \"单位_FNumber\"\n" + " ,'件' AS \"单位_FName\"\n" + " ,0 AS \"单价\"\n" + " ,'' AS \"批号\"\n" + " ,0 AS \"金额\"\n" + " ,0 AS \"客户BOM\"\n" + " ,SUM(T.DNUM) AS \"实发数量\"\n" + " ,'' AS \"备注\"\n" + " ,SUM(T.DNUM) AS \"基本单位实发数量\"\n" + " ,0 AS \" 保质期（天）\"\n" + " ,'' AS \"有效期至\"\n" + " ,'' AS \"发料仓库_FNumber\"\n" + " ,T.FNAME AS \"发料仓库_FName\"\n" + " ,NULL AS \"仓位_FName\"\n" + " ,NULL AS \"仓位_FGroupName\"\n" + " ,0 AS \"辅助数量\"\n" + ",to_number(@YEAR@|| lpad(to_char(@I@),2,'0'))\n" + " FROM (SELECT CASE\n" + " WHEN TS.PROCEDUREID IN (28, 24,34) THEN\n" + " 'J'\n" + " WHEN TS.PROCEDUREID IN (13) THEN\n" + " 'S'\n" + " ELSE\n" + " 'Y'\n" + " END || G.GOODSMODEL AS GOODS_CODE\n" + " ,CASE\n" + " WHEN TS.PROCEDUREID IN (28, 24,34) THEN\n" + " 'Y'\n" + " WHEN TS.PROCEDUREID IN (13) THEN\n" + " ''\n" + " ELSE\n" + " 'S'\n" + " END || G.GOODSMODEL AS GOODS_CODE2\n" + " ,-1 AS DNUM\n" + " ,CASE\n" + " WHEN TS.PROCEDUREID = 28 THEN\n" + " '精坯库（一期）'\n" + " WHEN TS.PROCEDUREID IN( 24,34) THEN\n" + //" '精坯库（二期）'\n" + " '精坯库（一期）'\n" + " WHEN TS.PROCEDUREID = 31 THEN\n" + " '釉坯库（一期）'\n" + " WHEN TS.PROCEDUREID = 21 THEN\n" + //" '釉坯库（二期）'\n" + " '釉坯库（一期）'\n" + " WHEN TS.PROCEDUREID = 13 AND\n" + " TS.Kilnid IN (1, 2) THEN\n" + " '窑坯（一期）'\n" + " WHEN TS.PROCEDUREID = 13 AND\n" + " TS.Kilnid IN (3, 4) THEN\n" + //" '窑坯（二期）'\n" + " '窑坯（一期）'\n" + " ELSE\n" + " NULL\n" + " END FNAME\n" + " FROM TP_PM_PRODUCTIONDATA TS\n" + " INNER JOIN TP_MST_GOODS G\n" + " ON TS.GOODSID = G.GOODSID\n" + " WHERE TS.BACKOUTTIME >= @DATEBEGIN@\n" + " AND TS.BACKOUTTIME < @DATEEND@\n" + " AND TS.PROCEDUREID IN (28, 24,34, 21, 31)) T\n" + //13 质量登记不能撤销 " LEFT JOIN TA_IMEX_kingdee_Goodscode K ON T.GOODS_CODE = K.K_NAME AND T.FNAME = K.K_WAREHOUSE\n" + " LEFT JOIN TA_IMEX_kingdee_Goodscode K2 ON K2.K_NAME = T.GOODS_CODE2 AND K2.buildingno = K.buildingno\n" + // " WHERE (@FNAME_FLAG@ IS NULL OR T.FNAME = @FNAME_FLAG@) \n" + " GROUP BY K.K_CODE, K2.K_CODE, T.GOODS_CODE,T.GOODS_CODE2, T.FNAME\n" + " )"; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result1 += conn.ExecuteNonQuery(sqlStr2, sqlPara.ToArray()); } conn.Commit(); } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export028生产领料退单结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } if (result1 > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export028生产领料退单结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export028生产领料退单结转0条！"); } } } public static void export029() { using (IDataAccess conn = DataAccess.Create()) { //2021-08-2 dongyan 修正什么问题 int result = 0; int result1 = 0; int m = DateTime.Now.AddMonths(-1).Month; DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); string dayOut = b + "-" + a + "-" + DateTime.DaysInMonth(Convert.ToInt32(b), m).ToString(); conn.Open(); conn.BeginTransaction(); try { String sqlStr = @"delete JZ_RPT020_D WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); String Str = @"delete JZ_RPT020_M WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(Str, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); //dayEnd = dayBegin.AddMonths(1).AddMilliseconds(-1); dayEnd = dayBegin.AddMonths(1); String sqlStr2 = @"INSERT INTO JZ_RPT029 select * from ( SELECT '' AS 行号 ,'' AS 单据号_FBillno ,'' AS 单据号_FTrantype ,K.K_CODE AS 物料编码_FNumber ,T.GOODS_CODE AS 物料编码_FName ,'' AS 物料编码_FModel ,'' AS 辅助属性_FNumber ,'' AS 辅助属性_FName ,'*' AS 辅助属性_FClassName ,'件' AS 单位_FNumber ,'件' AS 单位_FName ,SUM(T.DNUM) AS 实收数量 ,0 AS 单价 ,0 AS 金额 ,'' AS 备注 ,SUM(T.DNUM) AS 基本单位实收数量 ,'' AS 生产_采购日期 ,'' AS 有效期至 ,'' AS 收货仓库_FNumber ,T.FNAME AS 收货仓库_FName ,NULL AS 仓位_FName ,NULL AS 仓位_FGroupName ,0 AS 辅助数量 ,to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM ( SELECT CASE WHEN TS.PROCEDUREID IN (3, 26) THEN 'J' WHEN TS.PROCEDUREID IN (21, 31) THEN 'S' ELSE 'Y' END || G.GOODSMODEL AS GOODS_CODE ,-1 AS DNUM ,CASE WHEN TS.PROCEDUREID = 26 THEN '精坯库（一期）' WHEN TS.PROCEDUREID = 3 THEN '精坯库（一期）' WHEN TS.PROCEDUREID = 28 THEN '釉坯库（一期）' WHEN TS.PROCEDUREID IN( 24,34) THEN '釉坯库（一期）' WHEN TS.PROCEDUREID = 31 THEN '窑坯（一期）' WHEN TS.PROCEDUREID = 21 THEN '窑坯（一期）' ELSE NULL END FNAME FROM TP_PM_PRODUCTIONDATA TS INNER JOIN TP_MST_GOODS G ON TS.GOODSID = G.GOODSID WHERE TS.BACKOUTTIME >= @DATEBEGIN@ AND TS.BACKOUTTIME < @DATEEND@ AND TS.PROCEDUREID IN (3, 26, 28, 24,34, 21, 31) ) T LEFT JOIN TA_IMEX_kingdee_Goodscode K ON T.GOODS_CODE = K.K_NAME AND T.FNAME = K.K_WAREHOUSE GROUP BY K.K_CODE, T.GOODS_CODE, T.FNAME)"; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result1 += conn.ExecuteNonQuery(sqlStr2, sqlPara.ToArray()); ; } conn.Commit(); } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export029产品入库退单结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } //if (result > 0 && result1 > 0) if (result1 > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export029产品入库退单结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export029产品入库退单结转0条！"); } } } public static void export030() { using (IDataAccess conn = DataAccess.Create()) { try { conn.Open(); conn.BeginTransaction(); //2021-8-4 陈强 String sqlStrc = @"delete JZ_RPT030 where FORWARDTIME = @c@ "; int result = 0; int m = DateTime.Now.AddMonths(-1).Month; string mouth = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string year = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(year + mouth); conn.ExecuteNonQuery(sqlStrc, new CDAParameter("c", c)); DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); DateTime T = DateTime.Now.AddMonths(-1); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(year + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1).AddMilliseconds(-1); //明细 String sqlStr1 = @" insert into JZ_RPT030 select * from ( SELECT to_number(@YEAR@|| lpad(to_char(@I@),2,'0')), to_char(rownum) AS 行号 , TT.* FROM ( SELECT '' AS ""单据号_FBillno"" ,'' AS ""单据号_FTrantype"" ,K.K_CODE AS ""物料代码_FNumber"" ,T.GOODS_CODE AS ""物料代码_FName"" ,'' AS ""物料代码_FModel"" ,'' AS ""辅助属性_FNumber"" ,'' AS ""辅助属性_FName"" ,'*' AS ""辅助属性_FClassName"" ,0 AS ""基本缺省调拨数量"" ,0 AS ""基本调入仓库存量"" ,'' AS ""批号"" ,'个' AS ""单位_FNumber"" ,'个' AS ""单位_FName"" ,0 AS ""缺省调拨数量"" ,0 AS ""调入仓库存量"" ,SUM(T.DNUM) AS ""数量"" ,0 AS ""单位成本"" ,SUM(T.DNUM) AS ""基本单位数量"" ,0 AS ""成本"" ,0 AS ""调拨单价"" ,0 AS ""调拨金额"" ,'' AS ""备注"" ,'' AS ""生产_采购日期"" ,0 AS ""保质期（天）"" ,'' AS ""有效期至"" ,'' AS ""调出仓库_FNumber"" ,FNAME_OUT AS ""调出仓库_FName"" ,'' AS ""调出仓位_FName"" ,'' AS ""调入仓库_FNumber"" ,FNAME_IN AS ""调入仓库_FName"" ,0 AS ""辅助数量"" FROM (SELECT 'J' || G.GOODSMODEL AS GOODS_CODE ,1 AS DNUM ,CASE WHEN TS.PROCEDUREID = 39 THEN '精坯库（一期）' WHEN TS.PROCEDUREID = 38 THEN '精坯库（二期）' ELSE NULL END FNAME_OUT ,CASE WHEN TS.PROCEDUREID = 39 THEN '010' WHEN TS.PROCEDUREID = 38 THEN '039' ELSE NULL END FNAME_CODE_OUT ,CASE WHEN TS.PROCEDUREID = 38 THEN '精坯库（一期）' WHEN TS.PROCEDUREID = 39 THEN '精坯库（二期）' ELSE NULL END FNAME_IN ,CASE WHEN TS.PROCEDUREID = 38 THEN '010' WHEN TS.PROCEDUREID = 39 THEN '039' ELSE NULL END FNAME_CODE_IN FROM TP_PM_PRODUCTIONDATA TS INNER JOIN TP_MST_GOODS G ON TS.GOODSID = G.GOODSID WHERE TS.CREATETIME >= @DATEBEGIN@ AND TS.CREATETIME <= @DATEEND@ AND TS.PROCEDUREID IN (38,39) ) T LEFT JOIN TA_IMEX_kingdee_Goodscode K ON T.GOODS_CODE = K.K_NAME AND T.FNAME_OUT = K.K_WAREHOUSE GROUP BY K.K_CODE, T.GOODS_CODE, T.FNAME_OUT, FNAME_CODE_OUT,T.FNAME_IN, FNAME_CODE_IN ORDER BY K.K_CODE, T.GOODS_CODE, T.FNAME_OUT ) TT)"; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", year)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); sqlPara.Add(new CDAParameter("T", T)); sqlPara.Add(new CDAParameter("D", DateTime.Now.AddDays(1 - DateTime.Now.Day).AddDays(-1))); result += conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); } conn.Commit(); if (result > 0) { Curtain.Log.Logger.Debug(year + "年" + mouth + "月:export030产品调拨单结转成功！"); } else { Curtain.Log.Logger.Debug(year + "年" + mouth + "月:export030产品调拨单结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export030产品调拨单结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export031() { using (IDataAccess conn = DataAccess.Create()) { try { conn.Open(); conn.BeginTransaction(); //2021-07-12 dongyan 修正什么问题 int result = 0; int m = DateTime.Now.AddMonths(-1).Month; DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); String sqlStr = @"delete JZ_RPT031 WHERE FORWARD_TIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1).AddMilliseconds(-1); String sqlStr1 = @"INSERT INTO JZ_RPT031 SELECT * FROM (SELECT DECODE(T.GROUTINGUSERCODE ,NULL ,MU.USERNAME || '[合计]' ,MU.USERNAME) AS 组别 ,T.GROUTINGUSERCODE AS 成型工号 ,T.GOODSCODE AS 产品编码 ,T.USERCODE AS 生产工号 ,T.USERNAME AS 姓名 ,COUNT(distinct T.MPID) AS 回收合格数 ,'0'AS BUILDINGNO ,to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM (SELECT PD.PRODUCTIONDATAID ,U.USERCODE ,U.USERNAME ,PD.GOODSCODE ,PD.GROUTINGUSERCODE ,PD.GROUTINGLINEID ,MIN(MPD.PRODUCTIONDATAID) MPID FROM TP_PM_PRODUCTIONDATA PD INNER JOIN TP_PM_PRODUCTIONDATA MPD ON MPD.BARCODE = PD.BARCODE LEFT JOIN TP_PM_PRODUCTIONDATA RPD ON RPD.BARCODE = PD.BARCODE AND RPD.MODELTYPE = 9 AND RPD.PRODUCTIONDATAID > PD.PRODUCTIONDATAID AND RPD.PRODUCTIONDATAID < MPD.PRODUCTIONDATAID INNER JOIN TP_MST_USER U ON U.USERID = PD.USERID WHERE PD.PROCEDUREID = 37 AND PD.VALUEFLAG = '1' AND PD.CREATETIME >= @DATEBEGIN@ AND PD.CREATETIME <= @DATEEND@ AND MPD.PRODUCTIONDATAID > PD.PRODUCTIONDATAID AND MPD.MODELTYPE = -1 AND MPD.VALUEFLAG = '1' AND RPD.PRODUCTIONDATAID IS NULL GROUP BY PD.PRODUCTIONDATAID ,U.USERCODE ,U.USERNAME ,PD.GOODSCODE ,PD.GROUTINGUSERCODE ,PD.GROUTINGLINEID) T INNER JOIN TP_PM_PRODUCTIONDATA P ON P.PRODUCTIONDATAID = T.MPID LEFT JOIN TP_PM_DEFECT D ON D.PRODUCTIONDATAID = T.MPID INNER JOIN TP_PC_GROUTINGLINE GL ON GL.GROUTINGLINEID = T.GROUTINGLINEID LEFT JOIN TP_MST_USER MU ON MU.USERID = GL.MONITORID WHERE P.GOODSLEVELTYPEID IN (4, 5) AND (GL.BUILDINGNO = '' OR '' IS NULL) GROUP BY GROUPING SETS((MU.USERNAME, T.GROUTINGUSERCODE, T.USERCODE, T.USERNAME, T.GOODSCODE,GL.BUILDINGNO),(MU.USERNAME,GL.BUILDINGNO)) ORDER BY MU.USERNAME, T.GROUTINGUSERCODE) DD WHERE DD .产品编码 IS NOT NULL"; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result = conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); } conn.Commit(); if (result > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export031堵漏气回收合格汇总表结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export031堵漏气回收合格汇总表结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export031堵漏气回收合格汇总表结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export032() { using (IDataAccess conn = DataAccess.Create()) { try { conn.Open(); conn.BeginTransaction(); //2021-07-29 陈强 String sqlStr = @"delete AJZ_RPT032 where FORWARDTIME = @c@"; int result = 0; int m = DateTime.Now.AddMonths(-1).Month; string mouth = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string year = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(year + mouth); string result1 = ""; conn.ExecuteNonQuery(sqlStr, new CDAParameter("c", c)); DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); //String[] arr = { "13", "35" }; for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(year + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1); //for (int a = 0; a < arr.Length; a++) //{ // String s = arr[a]; // for (int j = 0; j <= 2; j++) // { // String str = j + "#"; // for (int b = 0; b <= 4; b++) // { // String br = b.ToString(); String sqlStr1 = @" INSERT INTO AJZ_RPT032 SELECT * FROM ( SELECT DECODE( TA.GID, 3, '合计', TA.GOODSTYPENAME ) AS 产品类别, TA.GOODSNAME AS 产品名称, DECODE( TA.OUT_K, 0, NULL, TA.OUT_K ) AS 出窑数, DECODE( TA.COUNT_U4, 0, NULL, TA.COUNT_U4 ) AS U级, DECODE( TA.COUNT_U5 + TA.COUNT_AB, 0, NULL, TA.COUNT_U5 + TA.COUNT_AB ) A级, DECODE( TA.COUNT_AA, 0, NULL, TA.COUNT_AA ) AS AA级, DECODE( TA.COUNT_B, 0, NULL, TA.COUNT_B ) AS B级, DECODE( TA.COUNT_R, 0, NULL, TA.COUNT_R ) AS R级, DECODE( TA.COUNT_L, 0, NULL, TA.COUNT_L ) AS L级, DECODE( TA.COUNT_U7 - TA.COUNT_B + TA.COUNT_H + TA.COUNT_F, 0, NULL, TA.COUNT_U7 - TA.COUNT_B + TA.COUNT_H + TA.COUNT_F ) AS F级, DECODE( TA.COUNT_Z, 0, NULL, TA.COUNT_Z ) AS Z级, CASE WHEN TA.OUT_K IS NULL OR TA.OUT_K = 0 THEN NULL ELSE ROUND( ( TA.COUNT_U4 + TA.COUNT_U5 + TA.COUNT_AB + TA.COUNT_AA ) / TA.OUT_K, 4 ) * 100 END AS UA率, to_number( to_char( @YEAR@ ) || lpad( to_char( @I@), 2, '0' ) ), TA.BUILDINGNO , 0 as KILNID, TA.PROCEDUREID, TD.成型缺陷_糙活, TD.成型缺陷_成裂, TD.成型缺陷_成脏, TD.成型缺陷_成走, TD.成型缺陷_孔不良, TD.成型缺陷_泥绺, TD.成型缺陷_坯脏, TD.成型缺陷_注泡, TD.成型缺陷_棕眼, TD.成型缺陷_合计, TD.施釉缺陷_爆釉, TD.施釉缺陷_标不良, TD.施釉缺陷_脱釉, TD.施釉缺陷_釉薄, TD.施釉缺陷_釉磕, TD.施釉缺陷_釉绺, TD.施釉缺陷_釉脏, TD.施釉缺陷_釉粘, TD.施釉缺陷_釉泡, TD.施釉缺陷_合计, TD.合计 FROM ( SELECT GROUPING_ID ( T.PROCEDUREID,T.BUILDINGNO,GT.GOODSTYPECODE, G.GOODSCODE ) GID, GT.GOODSTYPECODE, GT.GOODSTYPENAME, G.GOODSCODE, G.GOODSNAME, T.BUILDINGNO, T.PROCEDUREID, SUM( T.OUT_K ) OUT_K, SUM( CASE WHEN T.H_NUM = 0 AND T.GOODSLEVELTYPEID = 4 THEN OUT_K ELSE 0 END ) COUNT_U4, -- 直接正品 SUM( CASE WHEN T.H_NUM = 0 AND T.GOODSLEVELTYPEID = 5 THEN OUT_K ELSE 0 END ) COUNT_U5, -- 直接副品 SUM( CASE WHEN T.H_NUM = 0 AND T.GOODSLEVELTYPEID = 7 THEN OUT_K ELSE 0 END ) COUNT_U7, -- 直接次品 SUM( CASE WHEN T.H_NUM = 0 AND T.GOODSLEVELTYPEID = 6 AND T.REWORKPROCEDUREID = 19 THEN OUT_K ELSE 0 END ) AS COUNT_R, SUM( CASE WHEN T.H_NUM = 0 AND T.GOODSLEVELTYPEID = 6 AND T.REWORKPROCEDUREID = 36 THEN OUT_K ELSE 0 END ) AS COUNT_Z, SUM( T.B_NUM ) AS COUNT_B, SUM( CASE WHEN T.H_NUM = 1 THEN OUT_K ELSE 0 END ) AS COUNT_H, SUM( CASE WHEN RPD.PROCEDUREID IN ( 22, 23, 37, 20, 41 ) THEN - 1 ELSE 0 END ) COUNT_L, -- 冷补、堵漏气检验数 + 研磨+刷洗 SUM( CASE WHEN T.GOODSLEVELTYPEID IN ( 4, 5 ) AND RPD.PROCEDUREID IN ( 22, 23 ) THEN 1 ELSE 0 END ) COUNT_AA, -- 冷补合格数 SUM( CASE WHEN T.GOODSLEVELTYPEID IN ( 4, 5 ) AND RPD.PROCEDUREID IN ( 37, 20, 41 ) THEN 1 ELSE 0 END ) COUNT_AB, -- 堵漏气合格数 + 研磨+刷洗 SUM( CASE WHEN T.GOODSLEVELTYPEID NOT IN ( 4, 5 ) AND RPD.PROCEDUREID IN ( 22, 23, 37, 20, 41 ) THEN 1 ELSE 0 END ) COUNT_F -- 冷补、堵漏气不合格数 + 研磨+刷洗 FROM ( SELECT PD.PRODUCTIONDATAID, NULL MPDID, CPD.GOODSLEVELTYPEID, PD.GOODSID, CPD.REWORKPROCEDUREID, CASE WHEN CPD.GOODSLEVELTYPEID = 7 AND COUNT( D.PRODUCTIONDEFECTID ) > 0 THEN 1 ELSE 0 END B_NUM, --B级品 1 OUT_K, CASE WHEN COUNT( MPD.PRODUCTIONDATAID ) > 0 THEN 1 ELSE 0 END H_NUM, GL.BUILDINGNO as BUILDINGNO, pd.PROCEDUREID as PROCEDUREID FROM TP_PM_PRODUCTIONDATA PD INNER JOIN TP_PM_PRODUCTIONDATA CPD ON PD.GROUTINGDAILYDETAILID = CPD.GROUTINGDAILYDETAILID AND CPD.CHECKBATCHNO = 1 AND CPD.KILNCARBATCHNO = PD.KILNCARBATCHNO LEFT JOIN TP_PM_PRODUCTIONDATA MPD ON PD.CHECKBATCHNO <> 1 AND PD.GROUTINGDAILYDETAILID = MPD.GROUTINGDAILYDETAILID AND MPD.VALUEFLAG = '1' AND MPD.PRODUCTIONDATAID < CPD.PRODUCTIONDATAID AND MPD.PRODUCTIONDATAID > PD.PRODUCTIONDATAID AND MPD.PROCEDUREMODEL = '1' LEFT JOIN TP_PM_DEFECT D ON D.PRODUCTIONDATAID = CPD.PRODUCTIONDATAID AND D.DEFECTID = 92 AND D.VALUEFLAG = '1' INNER JOIN TP_PC_GROUTINGLINE GL ON GL.GROUTINGLINEID = PD.GROUTINGLINEID /*20 研磨 22 直接冷补 23 研磨后冷补（非回收） 37 堵漏气 41 刷洗不良*/ --AND MPD.PROCEDUREID IN (20, 22, 23, 37, 41) LEFT JOIN TP_MST_GOODS G ON G.GOODSID = PD.GOODSID WHERE PD.PROCEDUREID IN(13,35) AND PD.CHECKFLAG = 1 AND PD.CREATETIME >= @DATEBEGIN@ AND PD.CREATETIME < @DATEEND@ -- AND (PD.KILNID = @KILNID@ OR @KILNID@ IS NULL) -- AND (PD.GOODSCODE = @GOODSCODE@ OR @GOODSCODE@ IS NULL) -- AND GL.BUILDINGNO = @BUILDINGNO@ AND G.GOODSTYPEID = 8 GROUP BY PD.PRODUCTIONDATAID, CPD.GOODSLEVELTYPEID, PD.GOODSID, CPD.REWORKPROCEDUREID, GL.BUILDINGNO, PD.PROCEDUREID UNION ALL SELECT PD.PRODUCTIONDATAID, MAX( MPD.PRODUCTIONDATAID ) MPDID, PD.GOODSLEVELTYPEID, PD.GOODSID, PD.REWORKPROCEDUREID, 0 B_NUM, 0 OUT_K, 1 H_NUM, GL.BUILDINGNO as BUILDINGNO, PD.PROCEDUREID as PROCEDUREID FROM TP_PM_PRODUCTIONDATA PD INNER JOIN TP_PM_PRODUCTIONDATA MPD ON PD.GROUTINGDAILYDETAILID = MPD.GROUTINGDAILYDETAILID AND MPD.VALUEFLAG = '1' AND MPD.PRODUCTIONDATAID < PD.PRODUCTIONDATAID AND MPD.PROCEDUREMODEL = '1' INNER JOIN TP_PC_GROUTINGLINE GL ON GL.GROUTINGLINEID = PD.GROUTINGLINEID /* 20 研磨 22 直接冷补 23 研磨后冷补（非回收）37 堵漏气 41 刷洗不良 */ --AND MPD.PROCEDUREID IN (20, 22, 23, 37, 41) LEFT JOIN TP_MST_GOODS G ON G.GOODSID = PD.GOODSID WHERE PD.VALUEFLAG = '1' AND PD.PROCEDUREID IN(13,35) AND PD.CHECKBATCHNO = 1 AND PD.CREATETIME >= @DATEBEGIN@ AND PD.CREATETIME < @DATEEND@ -- AND (PD.KILNID = @KILNID@ OR @KILNID@ IS NULL) -- AND (PD.GOODSCODE = @GOODSCODE@ OR @GOODSCODE@ IS NULL) -- AND GL.BUILDINGNO = @BUILDINGNO@ AND G.GOODSTYPEID = 8 GROUP BY PD.PRODUCTIONDATAID, PD.GOODSLEVELTYPEID, PD.GOODSID, PD.REWORKPROCEDUREID , GL.BUILDINGNO, PD.PROCEDUREID ) T LEFT JOIN TP_PM_PRODUCTIONDATA RPD ON RPD.PRODUCTIONDATAID = T.MPDID INNER JOIN TP_MST_GOODS G ON G.GOODSID = T.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID GROUP BY GROUPING SETS ( ( T.BUILDINGNO,T.PROCEDUREID,GT.GOODSTYPECODE, GT.GOODSTYPENAME, G.GOODSCODE, G.GOODSNAME ), ( T.BUILDINGNO,T.PROCEDUREID,GT.GOODSTYPECODE, GT.GOODSTYPENAME ), ( ) ) ) TA LEFT JOIN ( SELECT * FROM ( SELECT CASE WHEN TT.GID IN ( 12, 13, 15 ) THEN 3 WHEN TT.GID IN ( 4, 5, 7 ) THEN 1 ELSE 0 END AS D_GID, TT.GOODSTYPECODE D_GOODSTYPECODE, --,TT.GOODSTYPENAME D_ TT.GOODSCODE D_GOODSCODE, CASE WHEN TT.GID IN ( 1, 5, 13 ) THEN TT.DEFECT || '合计' WHEN TT.GID IN ( 3, 7, 15 ) THEN '合计' ELSE TT.DEFECT END AS DEFECT, TT.CNUM, TT.BUILDINGNO, TT.PROCEDUREID FROM ( SELECT GROUPING_ID ( L.BUILDINGNO,p.PROCEDUREID,GT.GOODSTYPECODE, G.GOODSCODE, PDP.DEFECTGROUPNAME, M.DEFECTNAME ) AS GID, GT.GOODSTYPECODE, GT.GOODSTYPENAME, G.GOODSCODE, PDP.DEFECTGROUPNAME, TO_CHAR( PDP.DEFECTGROUPNAME || '_' || M.DEFECTNAME ) AS DEFECT, COUNT( * ) AS CNUM, L.BUILDINGNO AS BUILDINGNO, P.PROCEDUREID AS PROCEDUREID 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 L ON P.GROUTINGLINEID = L.GROUTINGLINEID INNER JOIN TP_PM_DEFECT D ON P.PRODUCTIONDATAID = D.PRODUCTIONDATAID --只取缺陷位置defectpositionid=8 注修工defectjobs=37 AND D.DEFECTPOSITIONID = 8 --AND D.DEFECTJOBS = 37 INNER JOIN TP_MST_DEFECT M ON ( M.DEFECTID = D.DEFECTID AND M.DEFECTID > 0 ) LEFT JOIN TP_PC_PROCEDURE PR ON PR.PROCEDUREID = D.DEFECTPROCEDUREID INNER JOIN TP_MST_DEFECTGROUP PDP ON PDP.DEFECTID = D.DEFECTID AND PDP.DEFECTGROUPTYPE LIKE '水箱盖%' WHERE P.VALUEFLAG = '1' AND P.PROCEDUREID IN(13,35) AND P.CHECKBATCHNO = 1 AND P.CREATETIME >= @DATEBEGIN@ AND P.CREATETIME < @DATEEND@ AND P.GOODSLEVELTYPEID <> 4 -- AND (P.KILNID = @KILNID@ OR @KILNID@ IS NULL) -- AND (P.GOODSCODE = @GOODSCODE@ OR @GOODSCODE@ IS NULL) -- AND L.BUILDINGNO = @BUILDINGNO@ --XUWEI 2020-07-10 ADD --AND (NOT D.DEFECTPROCEDUREID IS NULL) AND G.GOODSTYPEID = 8 GROUP BY GROUPING SETS ( ( L.BUILDINGNO,P.PROCEDUREID,GT.GOODSTYPECODE, GT.GOODSTYPENAME, G.GOODSCODE, PDP.DEFECTGROUPNAME, M.DEFECTNAME ), ( L.BUILDINGNO,P.PROCEDUREID,GT.GOODSTYPECODE, GT.GOODSTYPENAME, G.GOODSCODE, PDP.DEFECTGROUPNAME ), ( L.BUILDINGNO,P.PROCEDUREID,GT.GOODSTYPECODE, GT.GOODSTYPENAME, G.GOODSCODE ), ( L.BUILDINGNO,P.PROCEDUREID,GT.GOODSTYPECODE, GT.GOODSTYPENAME, PDP.DEFECTGROUPNAME, M.DEFECTNAME ), ( L.BUILDINGNO,P.PROCEDUREID,GT.GOODSTYPECODE, GT.GOODSTYPENAME, PDP.DEFECTGROUPNAME ), ( L.BUILDINGNO,P.PROCEDUREID,GT.GOODSTYPECODE, GT.GOODSTYPENAME ), ( L.BUILDINGNO,P.PROCEDUREID,PDP.DEFECTGROUPNAME, M.DEFECTNAME ), ( L.BUILDINGNO,P.PROCEDUREID,PDP.DEFECTGROUPNAME ), ( ) ) -- ORDER BY -- GT.GOODSTYPECODE, -- G.GOODSCODE, -- PDP.DEFECTGROUPNAME, -- M.DEFECTNAME ) TT ) PIVOT ( SUM( CNUM ) FOR DEFECT IN ( '成型缺陷_糙活' AS 成型缺陷_糙活, '成型缺陷_成裂' AS 成型缺陷_成裂, '成型缺陷_成脏' AS 成型缺陷_成脏, '成型缺陷_成走' AS 成型缺陷_成走, '成型缺陷_孔不良' AS 成型缺陷_孔不良, '成型缺陷_泥绺' AS 成型缺陷_泥绺, '成型缺陷_坯脏' AS 成型缺陷_坯脏, '成型缺陷_注泡' AS 成型缺陷_注泡, '成型缺陷_棕眼' AS 成型缺陷_棕眼, '成型缺陷_合计' AS 成型缺陷_合计, '施釉缺陷_爆釉' AS 施釉缺陷_爆釉, '施釉缺陷_标不良' AS 施釉缺陷_标不良, '施釉缺陷_脱釉' AS 施釉缺陷_脱釉, '施釉缺陷_釉薄' AS 施釉缺陷_釉薄, '施釉缺陷_釉磕' AS 施釉缺陷_釉磕, '施釉缺陷_釉绺' AS 施釉缺陷_釉绺, '施釉缺陷_釉脏' AS 施釉缺陷_釉脏, '施釉缺陷_釉粘' AS 施釉缺陷_釉粘, '施釉缺陷_釉泡' AS 施釉缺陷_釉泡, '施釉缺陷_合计' AS 施釉缺陷_合计, '合计' AS 合计 ) ) ) TD ON TA.GID = TD.D_GID AND TA.BUILDINGNO = TD.BUILDINGNO AND TA.PROCEDUREID = TD.PROCEDUREID AND ( ( TA.GID = 0 AND TA.GOODSTYPECODE = TD.D_GOODSTYPECODE AND TA.GOODSCODE = TD.D_GOODSCODE ) OR ( TA.GID = 1 AND TA.GOODSTYPECODE = TD.D_GOODSTYPECODE ) OR ( TA.GID = 3 ) ) ) t WHERE t.产品名称 IS NOT NULL"; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", year)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result = conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); // } //} // } } conn.Commit(); if (result > 0) { Curtain.Log.Logger.Debug(year + "年" + mouth + "月:export032水箱盖每窑质量统计表结转成功！"); } else { Curtain.Log.Logger.Debug(year + "年" + mouth + "月:export032水箱盖每窑质量统计表结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export032水箱盖每窑质量统计表结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export033() { using (IDataAccess conn = DataAccess.Create()) { try { conn.Open(); conn.BeginTransaction(); //2021-07-12 dongyan 修正什么问题 int result = 0; int m = DateTime.Now.AddMonths(-1).Month; ; DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); String sqlStr = @"delete AJZ_RPT033 WHERE FORWARD_TIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1); String sqlStr1 = @" INSERT INTO AJZ_RPT033 SELECT 工号, 工种, 姓名, FORWARD_TIME，挂座, 座便，连体, 水箱, 盆, 柱, 合计 FROM ( SELECT DECODE( GID, 14, '[合计]', 15, '[合计]',工号 ) AS 工号, 工种, 姓名, to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) as FORWARD_TIME, DECODE( GID, 1, '合计', 15, '合计',产品 ) AS 产品, 数量 as 数量 FROM ( SELECT GROUPING_ID ( U.USERCODE, H.STAFFNAME, J.JOBSNAME, T.GOODSTYPENAME ) AS GID, U.USERCODE AS 工号, J.JOBSNAME AS 工种, H.STAFFNAME AS 姓名, T.GOODSTYPENAME AS 产品, COUNT( * ) AS 数量 FROM TP_PM_PRODUCTIONDATA P LEFT JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID INNER JOIN TP_MST_GOODSTYPE T ON T.GOODSTYPEID = G.GOODSTYPEID AND ( T.GOODSTYPEID NOT IN ( 9, 11 ) ) LEFT JOIN TP_PC_CLASSESDETAIL C ON C.CLASSESSETTINGID = P.CLASSESSETTINGID LEFT JOIN TP_MST_USER U ON U.USERID = C.USERID LEFT JOIN TP_HR_STAFF H ON H.STAFFID = C.STAFFID INNER JOIN TP_MST_JOBS J ON J.JOBSID = C.UJOBSID WHERE P.VALUEFLAG = '1' AND PROCEDUREID = 15 AND P.CREATETIME >= @DATEBEGIN@ AND P.CREATETIME < @DATEEND@ GROUP BY GROUPING SETS ( ( U.USERCODE, H.STAFFNAME, J.JOBSNAME, T.GOODSTYPENAME ), ( U.USERCODE, H.STAFFNAME, J.JOBSNAME ), ( T.GOODSTYPENAME ), ( ) ) ) ) PIVOT ( SUM( 数量 ) FOR 产品 IN ( '挂座' AS 挂座, '座' AS 座便, '连体' AS 连体, '箱' AS 水箱, '盆' AS 盆, '柱' AS 柱, '合计' AS 合计 ) ) "; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result = conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); } conn.Commit(); if (result > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export033包装入仓产量统计表结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export033包装入仓产量统计表结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export033包装入仓产量统计表结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export034() { using (IDataAccess conn = DataAccess.Create()) { try { conn.Open(); conn.BeginTransaction(); //2021-07-12 dongyan 修正什么问题 int result = 0; int result2 = 0; int m = DateTime.Now.AddMonths(-1).Month; ; DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); String sqlStr = @"delete AJZ_RPT034 WHERE FORWARDTIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1); String sqlStr1 = @" INSERT INTO AJZ_RPT034 SELECT P1.工号, P1.姓名, P1.产品, P1.日期, P1.出窑数量, NVL(P2.缺陷数量s,0) AS 缺陷数量, ROUND( NVL(P2.缺陷数量s,0)/ DECODE( P1.出窑数量 , 0, 1,P1.出窑数量 ), 4 ) * 100 || '%' AS 缺陷率, '1#' AS BUILDINGNO, '0' AS USERCODE1, '0' AS STAFFNAME1, '0' AS CHECKTIME1, '0' AS GOODSNAME1, 0 AS QX_COUNT1, P2.合格数_上坯工, P2.合格数_擦坯工, P2.合格数_施釉工, P2.合格数_找枪工, P2.合格数_擦釉工, P2.合格数_检修工, P2.合格数_刷氯化镁, P2.合格数_巡检工, P2.合格数_下坯工, P2.合格数_循环线施釉, P2.合格数_循环线擦坯, P2.合格数_合计, to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM ( SELECT U.USERCODE AS 工号, H.STAFFNAME AS 姓名, P.GOODSNAME AS 产品, TO_CHAR( TRUNC( P.CHECKTIME ), 'YYYY-MM-DD' ) AS 日期, COUNT( * ) AS 出窑数量 FROM TP_PM_PRODUCTIONDATA P INNER JOIN TP_PM_PRODUCTIONDATA XHX ON XHX.BARCODE = P.BARCODE AND XHX.PROCEDUREID = 33 AND XHX.VALUEFLAG = '1' LEFT JOIN TP_PC_CLASSESDETAIL C ON C.CLASSESSETTINGID = XHX.CLASSESSETTINGID LEFT JOIN TP_MST_USER U ON U.USERID = C.USERID LEFT JOIN TP_HR_STAFF H ON H.STAFFID = C.STAFFID INNER JOIN TP_MST_JOBS J ON J.JOBSID = C.UJOBSID AND J.JOBSID IN ( 59, 43, 42, 62, 63, 71, 73, 70, 60, 67, 68 ) WHERE P.VALUEFLAG = '1' AND P.PROCEDUREID = 13 AND P.CHECKBATCHNO = '1' AND P.CHECKTIME >= @DATEBEGIN@ AND P.CHECKTIME < @DATEEND@ GROUP BY U.USERCODE, H.STAFFNAME, P.GOODSNAME, TRUNC( P.CHECKTIME ) ) P1 LEFT JOIN ( SELECT * FROM ( SELECT--GID, 工号 AS 工号s, DECODE( GID, 6, 姓名 || '[合计]', 7, 姓名 || '[合计]', 姓名 ) AS 姓名s, TO_CHAR( 日期, 'YYYY-MM-DD' ) AS 日期s, DECODE( GID, 1, '合计', 7, '合计', 工种 ) AS 工种, 产品 AS 产品s, 合格数量, 缺陷数量 AS 缺陷数量s FROM ( SELECT GROUPING_ID ( U.USERCODE, H.STAFFNAME, TRUNC( P.CHECKTIME ), P.GOODSNAME, J.JOBSNAME ) AS GID, U.USERCODE AS 工号, H.STAFFNAME AS 姓名, TRUNC( P.CHECKTIME ) AS 日期, J.JOBSNAME AS 工种, P.GOODSNAME AS 产品, SUM( CASE WHEN P.GOODSLEVELTYPEID IN ( 4, 5 ) OR D.DEFECTPROCEDUREID <> 16 THEN 1 ELSE 0 END ) AS 合格数量, SUM( CASE WHEN P.GOODSLEVELTYPEID NOT IN ( 4, 5 ) AND D.DEFECTPROCEDUREID = 16 THEN 1 ELSE 0 END ) AS 缺陷数量 FROM TP_PM_PRODUCTIONDATA P INNER JOIN TP_PM_PRODUCTIONDATA XHX ON XHX.BARCODE = P.BARCODE AND XHX.PROCEDUREID = 33 AND XHX.VALUEFLAG = '1' LEFT JOIN TP_PC_CLASSESDETAIL C ON C.CLASSESSETTINGID = XHX.CLASSESSETTINGID LEFT JOIN TP_MST_USER U ON U.USERID = C.USERID LEFT JOIN TP_HR_STAFF H ON H.STAFFID = C.STAFFID INNER JOIN TP_MST_JOBS J ON J.JOBSID = C.UJOBSID AND J.JOBSID IN ( 59, 43, 42, 62, 63, 71, 73, 70, 60, 67, 68 ) LEFT JOIN ( SELECT PRODUCTIONDATAID, DEFECTPROCEDUREID FROM TP_PM_DEFECT D WHERE PRODUCTIONDEFECTID IN ( SELECT MAX( PRODUCTIONDEFECTID ) FROM TP_PM_DEFECT T WHERE T.PRODUCTIONDATAID = D.PRODUCTIONDATAID ) ) D ON D.PRODUCTIONDATAID = P.PRODUCTIONDATAID WHERE P.VALUEFLAG = '1' AND P.PROCEDUREID = 13 AND P.CHECKBATCHNO = '1' AND P.CHECKTIME >= @DATEBEGIN@ AND P.CHECKTIME < @DATEEND@ GROUP BY GROUPING SETS ( ( U.USERCODE, H.STAFFNAME, TRUNC( P.CHECKTIME ), P.GOODSNAME, J.JOBSNAME ), ( U.USERCODE, H.STAFFNAME, TRUNC( P.CHECKTIME ), P.GOODSNAME ), ( U.USERCODE, H.STAFFNAME, J.JOBSNAME ), ( U.USERCODE, H.STAFFNAME ) ) ) ) PIVOT ( SUM( 合格数量 ) FOR 工种 IN ( '上坯工' AS 合格数_上坯工, '擦坯工' AS 合格数_擦坯工, '施釉工' AS 合格数_施釉工, '找枪工' AS 合格数_找枪工, '擦釉工' AS 合格数_擦釉工, '检修工' AS 合格数_检修工, '刷氯化镁' AS 合格数_刷氯化镁, '巡检工' AS 合格数_巡检工, '下坯工' AS 合格数_下坯工, '循环线施釉' AS 合格数_循环线施釉, '循环线擦坯' AS 合格数_循环线擦坯, '合计' AS 合格数_合计 ) ) ORDER BY 工号s,姓名s ) P2 ON P1.工号 = P2.工号s AND P1.姓名 = P2.姓名s AND P1.产品 = P2.产品s AND P1.日期 = P2.日期s "; String sqlStr2 = @" INSERT INTO AJZ_RPT034 SELECT P1.工号, P1.姓名, P1.产品, P1.日期, P1.出窑数量, NVL(P2.缺陷数量s,0) AS 缺陷数量, ROUND( NVL(P2.缺陷数量s,0)/ DECODE( P1.出窑数量 , 0, 1,P1.出窑数量 ), 4 ) * 100 || '%' AS 缺陷率, '2#' AS BUILDINGNO, '0' AS USERCODE1, '0' AS STAFFNAME1, '0' AS CHECKTIME1, '0' AS GOODSNAME1, 0 AS QX_COUNT1, P2.合格数_上坯工, P2.合格数_擦坯工, P2.合格数_施釉工, P2.合格数_找枪工, P2.合格数_擦釉工, P2.合格数_检修工, P2.合格数_刷氯化镁, P2.合格数_巡检工, P2.合格数_下坯工, P2.合格数_循环线施釉, P2.合格数_循环线擦坯, P2.合格数_合计, to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM ( SELECT U.USERCODE AS 工号, H.STAFFNAME AS 姓名, P.GOODSNAME AS 产品, TO_CHAR( TRUNC( P.CHECKTIME ), 'YYYY-MM-DD' ) AS 日期, COUNT( * ) AS 出窑数量 FROM TP_PM_PRODUCTIONDATA P INNER JOIN TP_PM_PRODUCTIONDATA XHX ON XHX.BARCODE = P.BARCODE AND XHX.PROCEDUREID = 16 AND XHX.VALUEFLAG = '1' LEFT JOIN TP_PC_CLASSESDETAIL C ON C.CLASSESSETTINGID = XHX.CLASSESSETTINGID LEFT JOIN TP_MST_USER U ON U.USERID = C.USERID LEFT JOIN TP_HR_STAFF H ON H.STAFFID = C.STAFFID INNER JOIN TP_MST_JOBS J ON J.JOBSID = C.UJOBSID AND J.JOBSID IN ( 59, 43, 42, 62, 63, 71, 73, 70, 60, 67, 68 ) WHERE P.VALUEFLAG = '1' AND P.PROCEDUREID = 13 AND P.CHECKBATCHNO = '1' AND P.CHECKTIME >= @DATEBEGIN@ AND P.CHECKTIME < @DATEEND@ GROUP BY U.USERCODE, H.STAFFNAME, P.GOODSNAME, TRUNC( P.CHECKTIME ) ) P1 LEFT JOIN ( SELECT * FROM ( SELECT--GID, 工号 AS 工号s, DECODE( GID, 6, 姓名 || '[合计]', 7, 姓名 || '[合计]', 姓名 ) AS 姓名s, TO_CHAR( 日期, 'YYYY-MM-DD' ) AS 日期s, DECODE( GID, 1, '合计', 7, '合计', 工种 ) AS 工种, 产品 AS 产品s, 合格数量, 缺陷数量 AS 缺陷数量s FROM ( SELECT GROUPING_ID ( U.USERCODE, H.STAFFNAME, TRUNC( P.CHECKTIME ), P.GOODSNAME, J.JOBSNAME ) AS GID, U.USERCODE AS 工号, H.STAFFNAME AS 姓名, TRUNC( P.CHECKTIME ) AS 日期, J.JOBSNAME AS 工种, P.GOODSNAME AS 产品, SUM( CASE WHEN P.GOODSLEVELTYPEID IN ( 4, 5 ) OR D.DEFECTPROCEDUREID <> 16 THEN 1 ELSE 0 END ) AS 合格数量, SUM( CASE WHEN P.GOODSLEVELTYPEID NOT IN ( 4, 5 ) AND D.DEFECTPROCEDUREID = 16 THEN 1 ELSE 0 END ) AS 缺陷数量 FROM TP_PM_PRODUCTIONDATA P INNER JOIN TP_PM_PRODUCTIONDATA XHX ON XHX.BARCODE = P.BARCODE AND XHX.PROCEDUREID = 16 AND XHX.VALUEFLAG = '1' LEFT JOIN TP_PC_CLASSESDETAIL C ON C.CLASSESSETTINGID = XHX.CLASSESSETTINGID LEFT JOIN TP_MST_USER U ON U.USERID = C.USERID LEFT JOIN TP_HR_STAFF H ON H.STAFFID = C.STAFFID INNER JOIN TP_MST_JOBS J ON J.JOBSID = C.UJOBSID AND J.JOBSID IN ( 59, 43, 42, 62, 63, 71, 73, 70, 60, 67, 68 ) LEFT JOIN ( SELECT PRODUCTIONDATAID, DEFECTPROCEDUREID FROM TP_PM_DEFECT D WHERE PRODUCTIONDEFECTID IN ( SELECT MAX( PRODUCTIONDEFECTID ) FROM TP_PM_DEFECT T WHERE T.PRODUCTIONDATAID = D.PRODUCTIONDATAID ) ) D ON D.PRODUCTIONDATAID = P.PRODUCTIONDATAID WHERE P.VALUEFLAG = '1' AND P.PROCEDUREID = 13 AND P.CHECKBATCHNO = '1' AND P.CHECKTIME >= @DATEBEGIN@ AND P.CHECKTIME < @DATEEND@ GROUP BY GROUPING SETS ( ( U.USERCODE, H.STAFFNAME, TRUNC( P.CHECKTIME ), P.GOODSNAME, J.JOBSNAME ), ( U.USERCODE, H.STAFFNAME, TRUNC( P.CHECKTIME ), P.GOODSNAME ), ( U.USERCODE, H.STAFFNAME, J.JOBSNAME ), ( U.USERCODE, H.STAFFNAME ) ) ) ) PIVOT ( SUM( 合格数量 ) FOR 工种 IN ( '上坯工' AS 合格数_上坯工, '擦坯工' AS 合格数_擦坯工, '施釉工' AS 合格数_施釉工, '找枪工' AS 合格数_找枪工, '擦釉工' AS 合格数_擦釉工, '检修工' AS 合格数_检修工, '刷氯化镁' AS 合格数_刷氯化镁, '巡检工' AS 合格数_巡检工, '下坯工' AS 合格数_下坯工, '循环线施釉' AS 合格数_循环线施釉, '循环线擦坯' AS 合格数_循环线擦坯, '合计' AS 合格数_合计 ) ) ORDER BY 工号s,姓名s ) P2 ON P1.工号 = P2.工号s AND P1.姓名 = P2.姓名s AND P1.产品 = P2.产品s AND P1.日期 = P2.日期s "; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result = conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); result2 = conn.ExecuteNonQuery(sqlStr2, sqlPara.ToArray()); } conn.Commit(); if (result > 0 && result2 > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export034循环线产量统计表结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export034循环线产量统计表结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export034循环线产量统计表结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } public static void export035() { using (IDataAccess conn = DataAccess.Create()) { try { conn.Open(); conn.BeginTransaction(); //2021-07-12 dongyan 修正什么问题 int result = 0; int result1 = 0; int result2 = 0; int result3 = 0; int result4 = 0; int result5 = 0; int m = DateTime.Now.AddMonths(-1).Month; DateTime dayBegin = new DateTime(); DateTime dayEnd = new DateTime(); string a = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, '0'); string b = DateTime.Now.AddMonths(-1).Year.ToString(); int c = Convert.ToInt32(b + a); String sqlStr = @"delete JZ_RPT035_01 WHERE FORWARD_TIME = @C@"; conn.ExecuteNonQuery(sqlStr, new CDAParameter("C", c)); String Str1 = @"delete JZ_RPT035_02 WHERE FORWARD_TIME = @C@"; conn.ExecuteNonQuery(Str1, new CDAParameter("C", c)); String Str2 = @"delete JZ_RPT035_03 WHERE FORWARD_TIME = @C@"; conn.ExecuteNonQuery(Str2, new CDAParameter("C", c)); String Str3 = @"delete JZ_RPT035_04 WHERE FORWARD_TIME = @C@"; conn.ExecuteNonQuery(Str3, new CDAParameter("C", c)); String Str4 = @"delete JZ_RPT035_05 WHERE FORWARD_TIME = @C@"; conn.ExecuteNonQuery(Str4, new CDAParameter("C", c)); String Str5 = @"delete JZ_RPT035_06 WHERE FORWARD_TIME = @C@"; conn.ExecuteNonQuery(Str5, new CDAParameter("C", c)); for (int i = m; i <= m; i++) { dayBegin = Convert.ToDateTime(b + "-" + i.ToString().PadLeft(2, '0') + "-01"); dayEnd = dayBegin.AddMonths(1).AddMilliseconds(-1); String sqlStr1 = @" INSERT INTO JZ_RPT035_01 SELECT sys_guid(), USERCODE_CHECK_FRONT , OUT_COUNT , PROCEDUREID_CHECK_FRONT , USERCODE_FRONT , GOODSNAME_FRONT , GOODSNAME_BEHIND , USERCODE_BEHIND , PROCEDUREID_CHECK_BEHIND , USERCODE_CHECK_BEHIND , CNUM1 , CNUM2 , CNUM3 , CNUM4 , CNUM5 , CNUM6 , CNUM7 , CNUM8 , CNUM9 , CNUM10 , CNUM11 , C_COUNT , to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM(SELECT CY.*, QX.* FROM ( SELECT --GROUPING_ID(P.GOODSNAME,U.USERCODE) AS GID, DECODE( GROUPING_ID ( P.GOODSNAME, U.USERCODE ), 3, '[合计]', P.GOODSNAME ) AS GOODSNAME_FRONT, U.USERCODE AS USERCODE_FRONT, COUNT( * ) AS OUT_COUNT, P.PROCEDUREID AS PROCEDUREID_CHECK_FRONT, P.USERCODE AS USERCODE_CHECK_FRONT FROM TP_PM_PRODUCTIONDATA P INNER JOIN TP_PM_PRODUCTIONDATA U ON U.BARCODE = P.BARCODE AND U.VALUEFLAG = '1' AND U.PROCEDUREID IN ( 25,2 ) WHERE P.CHECKFLAG = 1 AND P.CREATETIME >= @DATEBEGIN@ AND P.CREATETIME <= @DATEEND@ GROUP BY GROUPING SETS ( ( P.GOODSNAME, U.USERCODE,P.PROCEDUREID,P.USERCODE ), (P.PROCEDUREID,P.USERCODE) ) ) CY LEFT JOIN ( SELECT * FROM ( SELECT DECODE( GID, 14, '[合计]', 15, '[合计]',产品 ) AS GOODSNAME_BEHIND, 工号 AS USERCODE_BEHIND, DECODE( GID, 3, '合计', 15, '合计',缺陷名称 ) 缺陷名称, 缺陷数 , pro AS PROCEDUREID_CHECK_BEHIND, us AS USERCODE_CHECK_BEHIND FROM ( SELECT GROUPING_ID ( P.GOODSNAME, U.USERCODE, D.DEFECTPROCEDUREID, D.DEFECTNAME ) AS GID, P.GOODSNAME AS 产品, U.USERCODE AS 工号, D.DEFECTPROCEDUREID AS 责任工序, D.DEFECTNAME AS 缺陷名称, COUNT( * ) AS 缺陷数 , P.PROCEDUREID AS pro, P.USERCODE AS us FROM TP_PM_PRODUCTIONDATA P LEFT JOIN TP_PM_DEFECT D ON D.PRODUCTIONDATAID = P.PRODUCTIONDATAID INNER JOIN TP_MST_DEFECTGROUP G ON INSTR( G.DEFECTGROUPTYPE, '大帐' ) = 1 AND G.DEFECTGROUPNAME = '成型缺陷' AND D.DEFECTID = G.DEFECTID AND ( D.DEFECTPROCEDUREID IN ( 25,2 ) ) INNER JOIN TP_PM_PRODUCTIONDATA U ON U.BARCODE = P.BARCODE AND U.VALUEFLAG = '1' AND ( U.PROCEDUREID IN ( 25,2 ) ) WHERE P.VALUEFLAG = '1' AND P.CREATETIME >= @DATEBEGIN@ AND P.CREATETIME <= @DATEEND@ GROUP BY GROUPING SETS ( ( P.GOODSNAME, U.USERCODE, D.DEFECTPROCEDUREID, D.DEFECTNAME , P.PROCEDUREID ,P.USERCODE), ( P.GOODSNAME, U.USERCODE, P.PROCEDUREID ,P.USERCODE ), ( D.DEFECTNAME, P.PROCEDUREID ,P.USERCODE ), ( P.PROCEDUREID ,P.USERCODE ) ) ) ) PIVOT ( SUM( 缺陷数 ) FOR 缺陷名称 IN ( '成裂' AS CNUM1,'棕眼' AS CNUM2,'糙活' AS CNUM3,'成脏' AS CNUM4,'成走' AS CNUM5,'注泡' AS CNUM6,'坯脏' AS CNUM7,'孔不良' AS CNUM8,'排水不良' AS CNUM9,'泥绺' AS CNUM10,'漏气' AS CNUM11,'合计' AS C_COUNT ) ) ) QX ON CY.GOODSNAME_FRONT = QX.GOODSNAME_BEHIND AND ( CY.USERCODE_FRONT = QX.USERCODE_BEHIND OR CY.USERCODE_FRONT IS NULL ) AND CY.PROCEDUREID_CHECK_FRONT = QX.PROCEDUREID_CHECK_BEHIND And CY.USERCODE_CHECK_FRONT = QX.USERCODE_CHECK_BEHIND ) "; String sqlStr2 = @" INSERT INTO JZ_RPT035_02 SELECT sys_guid(), USERCODE_CHECK_FRONT , OUT_COUNT , PROCEDUREID_CHECK_FRONT , USERCODE_FRONT , GOODSNAME_FRONT , GOODSNAME_BEHIND , USERCODE_BEHIND , PROCEDUREID_CHECK_BEHIND , USERCODE_CHECK_BEHIND , CNUM1 , CNUM2 , CNUM3 , CNUM4 , CNUM5 , CNUM6 , C_COUNT , to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM(SELECT CY.*, QX.* FROM ( SELECT --GROUPING_ID(P.GOODSNAME,U.USERCODE) AS GID, DECODE( GROUPING_ID ( P.GOODSNAME, U.USERCODE ), 3, '[合计]', P.GOODSNAME ) AS GOODSNAME_FRONT, U.USERCODE AS USERCODE_FRONT, COUNT( * ) AS OUT_COUNT , P.PROCEDUREID AS PROCEDUREID_CHECK_FRONT, P.USERCODE AS USERCODE_CHECK_FRONT FROM TP_PM_PRODUCTIONDATA P INNER JOIN TP_PM_PRODUCTIONDATA U ON U.BARCODE = P.BARCODE AND U.VALUEFLAG = '1' AND U.PROCEDUREID IN ( 7,0 ) WHERE P.CHECKFLAG = 1 AND P.CREATETIME >= @DATEBEGIN@ AND P.CREATETIME <= @DATEEND@ GROUP BY GROUPING SETS ( ( P.GOODSNAME, U.USERCODE,P.PROCEDUREID,P.USERCODE ), (P.PROCEDUREID,P.USERCODE) ) ) CY LEFT JOIN ( SELECT * FROM ( SELECT DECODE( GID, 14, '[合计]', 15, '[合计]',产品 ) AS GOODSNAME_BEHIND, 工号 AS USERCODE_BEHIND, DECODE( GID, 3, '合计', 15, '合计',缺陷名称 ) 缺陷名称, 缺陷数 , pro AS PROCEDUREID_CHECK_BEHIND, us AS USERCODE_CHECK_BEHIND FROM ( SELECT GROUPING_ID ( P.GOODSNAME, U.USERCODE, D.DEFECTPROCEDUREID, D.DEFECTNAME ) AS GID, P.GOODSNAME AS 产品, U.USERCODE AS 工号, D.DEFECTPROCEDUREID AS 责任工序, D.DEFECTNAME AS 缺陷名称, COUNT( * ) AS 缺陷数 , P.PROCEDUREID AS pro, P.USERCODE AS us FROM TP_PM_PRODUCTIONDATA P LEFT JOIN TP_PM_DEFECT D ON D.PRODUCTIONDATAID = P.PRODUCTIONDATAID INNER JOIN TP_MST_DEFECTGROUP G ON INSTR( G.DEFECTGROUPTYPE, '大帐' ) = 1 AND G.DEFECTGROUPNAME = '登窑缺陷' AND D.DEFECTID = G.DEFECTID AND ( D.DEFECTPROCEDUREID IN ( 7,0 ) ) INNER JOIN TP_PM_PRODUCTIONDATA U ON U.BARCODE = P.BARCODE AND U.VALUEFLAG = '1' AND ( U.PROCEDUREID IN ( 7,0 ) ) WHERE P.VALUEFLAG = '1' AND P.CREATETIME >= @DATEBEGIN@ AND P.CREATETIME <= @DATEEND@ GROUP BY GROUPING SETS ( ( P.GOODSNAME, U.USERCODE, D.DEFECTPROCEDUREID, D.DEFECTNAME , P.PROCEDUREID ,P.USERCODE), ( P.GOODSNAME, U.USERCODE, P.PROCEDUREID ,P.USERCODE ), ( D.DEFECTNAME, P.PROCEDUREID ,P.USERCODE ), ( P.PROCEDUREID ,P.USERCODE ) ) ) ) PIVOT ( SUM( 缺陷数 ) FOR 缺陷名称 IN ( '装磕' AS CNUM1,'装粘' AS CNUM2,'装走' AS CNUM3,'蹭釉' AS CNUM4,'落脏' AS CNUM5,'装脏' AS CNUM6,'合计' AS C_COUNT ) ) ) QX ON CY.GOODSNAME_FRONT = QX.GOODSNAME_BEHIND AND ( CY.USERCODE_FRONT = QX.USERCODE_BEHIND OR CY.USERCODE_FRONT IS NULL ) AND CY.PROCEDUREID_CHECK_FRONT = QX.PROCEDUREID_CHECK_BEHIND And CY.USERCODE_CHECK_FRONT = QX.USERCODE_CHECK_BEHIND ) "; string sqlStr3 = @" INSERT INTO JZ_RPT035_05 SELECT sys_guid(), USERCODE_CHECK_FRONT , OUT_COUNT , PROCEDUREID_CHECK_FRONT , USERCODE_FRONT , GOODSNAME_FRONT , GOODSNAME_BEHIND , USERCODE_BEHIND , PROCEDUREID_CHECK_BEHIND , USERCODE_CHECK_BEHIND , CNUM1 , CNUM2 , CNUM3 , CNUM4 , CNUM5 , CNUM6 , CNUM7 , CNUM8 , CNUM9 , CNUM10 , CNUM11 , C_COUNT , to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM( SELECT CY.*, QX.* FROM ( SELECT --GROUPING_ID(P.GOODSNAME,U.USERCODE) AS GID, DECODE( GROUPING_ID ( P.GOODSNAME, U.USERCODE ), 3, '[合计]', P.GOODSNAME ) AS GOODSNAME_FRONT, U.USERCODE AS USERCODE_FRONT, COUNT( * ) AS OUT_COUNT , P.PROCEDUREID AS PROCEDUREID_CHECK_FRONT, P.USERCODE AS USERCODE_CHECK_FRONT FROM TP_PM_PRODUCTIONDATA P INNER JOIN TP_PM_PRODUCTIONDATA U ON U.BARCODE = P.BARCODE AND U.VALUEFLAG = '1' AND U.PROCEDUREID IN ( 8,0 ) WHERE P.CHECKFLAG = 1 AND P.CREATETIME >= @DATEBEGIN@ AND P.CREATETIME <=@DATEEND@ GROUP BY GROUPING SETS ( ( P.GOODSNAME, U.USERCODE,P.PROCEDUREID,P.USERCODE ), (P.PROCEDUREID,P.USERCODE) ) ) CY LEFT JOIN ( SELECT * FROM ( SELECT DECODE( GID, 14, '[合计]', 15, '[合计]',产品 ) AS GOODSNAME_BEHIND, 工号 AS USERCODE_BEHIND, DECODE( GID, 3, '合计', 15, '合计',缺陷名称 ) 缺陷名称, 缺陷数 , pro AS PROCEDUREID_CHECK_BEHIND, us AS USERCODE_CHECK_BEHIND FROM ( SELECT GROUPING_ID ( P.GOODSNAME, U.USERCODE, D.DEFECTPROCEDUREID, D.DEFECTNAME ) AS GID, P.GOODSNAME AS 产品, U.USERCODE AS 工号, D.DEFECTPROCEDUREID AS 责任工序, D.DEFECTNAME AS 缺陷名称, COUNT( * ) AS 缺陷数 , P.PROCEDUREID AS pro, P.USERCODE AS us FROM TP_PM_PRODUCTIONDATA P LEFT JOIN TP_PM_DEFECT D ON D.PRODUCTIONDATAID = P.PRODUCTIONDATAID INNER JOIN TP_MST_DEFECTGROUP G ON INSTR( G.DEFECTGROUPTYPE, '大帐' ) = 1 AND G.DEFECTGROUPNAME = '烧窑缺陷' AND D.DEFECTID = G.DEFECTID AND ( D.DEFECTPROCEDUREID IN ( 8,0 ) ) INNER JOIN TP_PM_PRODUCTIONDATA U ON U.BARCODE = P.BARCODE AND U.VALUEFLAG = '1' AND ( U.PROCEDUREID IN ( 8,0 ) ) WHERE P.VALUEFLAG = '1' AND P.CREATETIME >= @DATEBEGIN@ AND P.CREATETIME <=@DATEEND@ GROUP BY GROUPING SETS ( ( P.GOODSNAME, U.USERCODE, D.DEFECTPROCEDUREID, D.DEFECTNAME , P.PROCEDUREID ,P.USERCODE), ( P.GOODSNAME, U.USERCODE, P.PROCEDUREID ,P.USERCODE ), ( D.DEFECTNAME, P.PROCEDUREID ,P.USERCODE ), ( P.PROCEDUREID ,P.USERCODE ) ) ) ) PIVOT ( SUM( 缺陷数 ) FOR 缺陷名称 IN ( '桔釉' AS CNUM1,'烧裂' AS CNUM2,'烧生' AS CNUM3,'烧炸' AS CNUM4,'过火泡' AS CNUM5,'针孔' AS CNUM6,'麻坑' AS CNUM7,'窑脏' AS CNUM8,'烟熏' AS CNUM9,'风惊' AS CNUM10,'水滴' AS CNUM11,'合计' AS C_COUNT ) ) ) QX ON CY.GOODSNAME_FRONT = QX.GOODSNAME_BEHIND AND ( CY.USERCODE_FRONT = QX.USERCODE_BEHIND OR CY.USERCODE_FRONT IS NULL ) AND CY.PROCEDUREID_CHECK_FRONT = QX.PROCEDUREID_CHECK_BEHIND And CY.USERCODE_CHECK_FRONT = QX.USERCODE_CHECK_BEHIND ) "; string sqlStr4 = @" INSERT INTO JZ_RPT035_04 SELECT sys_guid(), USERCODE_CHECK_FRONT , OUT_COUNT , PROCEDUREID_CHECK_FRONT , USERCODE_FRONT , GOODSNAME_FRONT , GOODSNAME_BEHIND , USERCODE_BEHIND , PROCEDUREID_CHECK_BEHIND , USERCODE_CHECK_BEHIND , CNUM1 , CNUM2 , CNUM3 , CNUM4 , CNUM5 , CNUM6 , CNUM7 , CNUM8 , CNUM9 , CNUM10 , C_COUNT , to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM( SELECT CY.*, QX.* FROM ( SELECT --GROUPING_ID(P.GOODSNAME,U.USERCODE) AS GID, DECODE( GROUPING_ID ( P.GOODSNAME, U.USERCODE ), 3, '[合计]', P.GOODSNAME ) AS GOODSNAME_FRONT, U.USERCODE AS USERCODE_FRONT, COUNT( * ) AS OUT_COUNT , P.PROCEDUREID AS PROCEDUREID_CHECK_FRONT, P.USERCODE AS USERCODE_CHECK_FRONT FROM TP_PM_PRODUCTIONDATA P INNER JOIN TP_PM_PRODUCTIONDATA U ON U.BARCODE = P.BARCODE AND U.VALUEFLAG = '1' AND U.PROCEDUREID IN ( 30,6 ) WHERE P.CHECKFLAG = 1 AND P.CREATETIME >=@DATEBEGIN@ AND P.CREATETIME <= @DATEEND@ GROUP BY GROUPING SETS ( ( P.GOODSNAME, U.USERCODE,P.PROCEDUREID,P.USERCODE ), (P.PROCEDUREID,P.USERCODE) ) ) CY LEFT JOIN ( SELECT * FROM ( SELECT DECODE( GID, 14, '[合计]', 15, '[合计]',产品 ) AS GOODSNAME_BEHIND, 工号 AS USERCODE_BEHIND, DECODE( GID, 3, '合计', 15, '合计',缺陷名称 ) 缺陷名称, 缺陷数 , pro AS PROCEDUREID_CHECK_BEHIND, us AS USERCODE_CHECK_BEHIND FROM ( SELECT GROUPING_ID ( P.GOODSNAME, U.USERCODE, D.DEFECTPROCEDUREID, D.DEFECTNAME ) AS GID, P.GOODSNAME AS 产品, U.USERCODE AS 工号, D.DEFECTPROCEDUREID AS 责任工序, D.DEFECTNAME AS 缺陷名称, COUNT( * ) AS 缺陷数 , P.PROCEDUREID AS pro, P.USERCODE AS us FROM TP_PM_PRODUCTIONDATA P LEFT JOIN TP_PM_DEFECT D ON D.PRODUCTIONDATAID = P.PRODUCTIONDATAID INNER JOIN TP_MST_DEFECTGROUP G ON INSTR( G.DEFECTGROUPTYPE, '大帐' ) = 1 AND G.DEFECTGROUPNAME = '施釉缺陷' AND D.DEFECTID = G.DEFECTID AND ( D.DEFECTPROCEDUREID IN ( 30,6 ) ) INNER JOIN TP_PM_PRODUCTIONDATA U ON U.BARCODE = P.BARCODE AND U.VALUEFLAG = '1' AND ( U.PROCEDUREID IN ( 30,6 ) ) WHERE P.VALUEFLAG = '1' AND P.CREATETIME >= @DATEBEGIN@ AND P.CREATETIME <= @DATEEND@ GROUP BY GROUPING SETS ( ( P.GOODSNAME, U.USERCODE, D.DEFECTPROCEDUREID, D.DEFECTNAME , P.PROCEDUREID ,P.USERCODE), ( P.GOODSNAME, U.USERCODE, P.PROCEDUREID ,P.USERCODE ), ( D.DEFECTNAME, P.PROCEDUREID ,P.USERCODE ), ( P.PROCEDUREID ,P.USERCODE ) ) ) ) PIVOT ( SUM( 缺陷数 ) FOR 缺陷名称 IN ( '爆釉' AS CNUM1,'脱釉' AS CNUM2,'釉磕' AS CNUM3,'釉粘' AS CNUM4,'釉薄' AS CNUM5,'波纹' AS CNUM6,'釉脏' AS CNUM7,'标不良' AS CNUM8,'釉绺' AS CNUM9,'釉泡' AS CNUM10,'合计' AS C_COUNT ) ) ) QX ON CY.GOODSNAME_FRONT = QX.GOODSNAME_BEHIND AND ( CY.USERCODE_FRONT = QX.USERCODE_BEHIND OR CY.USERCODE_FRONT IS NULL ) AND CY.PROCEDUREID_CHECK_FRONT = QX.PROCEDUREID_CHECK_BEHIND And CY.USERCODE_CHECK_FRONT = QX.USERCODE_CHECK_BEHIND ) "; string sqlStr5 = @" INSERT INTO JZ_RPT035_06 SELECT sys_guid(), USERCODE_CHECK_FRONT , OUT_COUNT , PROCEDUREID_CHECK_FRONT , USERCODE_FRONT , GOODSNAME_FRONT , GOODSNAME_BEHIND , USERCODE_BEHIND , PROCEDUREID_CHECK_BEHIND , USERCODE_CHECK_BEHIND , CNUM1 , CNUM2 , C_COUNT , to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM( SELECT CY.*, QX.* FROM ( SELECT --GROUPING_ID(P.GOODSNAME,U.USERCODE) AS GID, DECODE( GROUPING_ID ( P.GOODSNAME, U.USERCODE ), 3, '[合计]', P.GOODSNAME ) AS GOODSNAME_FRONT, U.USERCODE AS USERCODE_FRONT, COUNT( * ) AS OUT_COUNT , P.PROCEDUREID AS PROCEDUREID_CHECK_FRONT, P.USERCODE AS USERCODE_CHECK_FRONT FROM TP_PM_PRODUCTIONDATA P INNER JOIN TP_PM_PRODUCTIONDATA U ON U.BARCODE = P.BARCODE AND U.VALUEFLAG = '1' AND U.PROCEDUREID IN ( 9,0 ) WHERE P.CHECKFLAG = 1 AND P.CREATETIME >=@DATEBEGIN@ AND P.CREATETIME <= @DATEEND@ GROUP BY GROUPING SETS ( ( P.GOODSNAME, U.USERCODE,P.PROCEDUREID,P.USERCODE ), (P.PROCEDUREID,P.USERCODE) ) ) CY LEFT JOIN ( SELECT * FROM ( SELECT DECODE( GID, 14, '[合计]', 15, '[合计]',产品 ) AS GOODSNAME_BEHIND, 工号 AS USERCODE_BEHIND, DECODE( GID, 3, '合计', 15, '合计',缺陷名称 ) 缺陷名称, 缺陷数 , pro AS PROCEDUREID_CHECK_BEHIND, us AS USERCODE_CHECK_BEHIND FROM ( SELECT GROUPING_ID ( P.GOODSNAME, U.USERCODE, D.DEFECTPROCEDUREID, D.DEFECTNAME ) AS GID, P.GOODSNAME AS 产品, U.USERCODE AS 工号, D.DEFECTPROCEDUREID AS 责任工序, D.DEFECTNAME AS 缺陷名称, COUNT( * ) AS 缺陷数 , P.PROCEDUREID AS pro, P.USERCODE AS us FROM TP_PM_PRODUCTIONDATA P LEFT JOIN TP_PM_DEFECT D ON D.PRODUCTIONDATAID = P.PRODUCTIONDATAID INNER JOIN TP_MST_DEFECTGROUP G ON INSTR( G.DEFECTGROUPTYPE, '大帐' ) = 1 AND G.DEFECTGROUPNAME = '卸窑缺陷' AND D.DEFECTID = G.DEFECTID AND ( D.DEFECTPROCEDUREID IN ( 9,0 ) ) INNER JOIN TP_PM_PRODUCTIONDATA U ON U.BARCODE = P.BARCODE AND U.VALUEFLAG = '1' AND ( U.PROCEDUREID IN ( 9,0 ) ) WHERE P.VALUEFLAG = '1' AND P.CREATETIME >=@DATEBEGIN@ AND P.CREATETIME <= @DATEEND@ GROUP BY GROUPING SETS ( ( P.GOODSNAME, U.USERCODE, D.DEFECTPROCEDUREID, D.DEFECTNAME , P.PROCEDUREID ,P.USERCODE), ( P.GOODSNAME, U.USERCODE, P.PROCEDUREID ,P.USERCODE ), ( D.DEFECTNAME, P.PROCEDUREID ,P.USERCODE ), ( P.PROCEDUREID ,P.USERCODE ) ) ) ) PIVOT ( SUM( 缺陷数 ) FOR 缺陷名称 IN ( '出磕' AS CNUM1,'划釉' AS CNUM2,'合计' AS C_COUNT ) ) ) QX ON CY.GOODSNAME_FRONT = QX.GOODSNAME_BEHIND AND ( CY.USERCODE_FRONT = QX.USERCODE_BEHIND OR CY.USERCODE_FRONT IS NULL ) AND CY.PROCEDUREID_CHECK_FRONT = QX.PROCEDUREID_CHECK_BEHIND And CY.USERCODE_CHECK_FRONT = QX.USERCODE_CHECK_BEHIND ) "; string sqlStr6 = @" INSERT INTO JZ_RPT035_03 SELECT sys_guid(), USERCODE_CHECK_FRONT , OUT_COUNT , PROCEDUREID_CHECK_FRONT , USERCODE_FRONT , GOODSNAME_FRONT , GOODSNAME_BEHIND , USERCODE_BEHIND , PROCEDUREID_CHECK_BEHIND , USERCODE_CHECK_BEHIND , CNUM1 , CNUM2 , CNUM3 , CNUM4 , CNUM5 , CNUM6 , CNUM7 , CNUM8 , CNUM9 , CNUM10 , C_COUNT , to_number(to_char(@YEAR@) || lpad(to_char(@I@),2,'0')) FROM(SELECT CY.*, QX.* FROM ( SELECT --GROUPING_ID(P.GOODSNAME,U.USERCODE) AS GID, DECODE( GROUPING_ID ( P.GOODSNAME, U.USERCODE ), 3, '[合计]', P.GOODSNAME ) AS GOODSNAME_FRONT, U.USERCODE AS USERCODE_FRONT, COUNT( * ) AS OUT_COUNT , P.PROCEDUREID AS PROCEDUREID_CHECK_FRONT, P.USERCODE AS USERCODE_CHECK_FRONT FROM TP_PM_PRODUCTIONDATA P INNER JOIN TP_PM_PRODUCTIONDATA U ON U.BARCODE = P.BARCODE AND U.VALUEFLAG = '1' AND U.PROCEDUREID IN ( 33,16 ) WHERE P.CHECKFLAG = 1 AND P.CREATETIME >=@DATEBEGIN@ AND P.CREATETIME <= @DATEEND@ GROUP BY GROUPING SETS ( ( P.GOODSNAME, U.USERCODE,P.PROCEDUREID,P.USERCODE ), (P.PROCEDUREID,P.USERCODE) ) ) CY LEFT JOIN ( SELECT * FROM ( SELECT DECODE( GID, 14, '[合计]', 15, '[合计]',产品 ) AS GOODSNAME_BEHIND, 工号 AS USERCODE_BEHIND, DECODE( GID, 3, '合计', 15, '合计',缺陷名称 ) 缺陷名称, 缺陷数 , pro AS PROCEDUREID_CHECK_BEHIND, us AS USERCODE_CHECK_BEHIND FROM ( SELECT GROUPING_ID ( P.GOODSNAME, U.USERCODE, D.DEFECTPROCEDUREID, D.DEFECTNAME ) AS GID, P.GOODSNAME AS 产品, U.USERCODE AS 工号, D.DEFECTPROCEDUREID AS 责任工序, D.DEFECTNAME AS 缺陷名称, COUNT( * ) AS 缺陷数 , P.PROCEDUREID AS pro, P.USERCODE AS us FROM TP_PM_PRODUCTIONDATA P LEFT JOIN TP_PM_DEFECT D ON D.PRODUCTIONDATAID = P.PRODUCTIONDATAID INNER JOIN TP_MST_DEFECTGROUP G ON INSTR( G.DEFECTGROUPTYPE, '大帐' ) = 1 AND G.DEFECTGROUPNAME = '循环线施釉缺陷' AND D.DEFECTID = G.DEFECTID AND ( D.DEFECTPROCEDUREID IN ( 33,16 ) ) INNER JOIN TP_PM_PRODUCTIONDATA U ON U.BARCODE = P.BARCODE AND U.VALUEFLAG = '1' AND ( U.PROCEDUREID IN ( 33,16 ) ) WHERE P.VALUEFLAG = '1' AND P.CREATETIME >=@DATEBEGIN@ AND P.CREATETIME <= @DATEEND@ GROUP BY GROUPING SETS ( ( P.GOODSNAME, U.USERCODE, D.DEFECTPROCEDUREID, D.DEFECTNAME , P.PROCEDUREID ,P.USERCODE), ( P.GOODSNAME, U.USERCODE, P.PROCEDUREID ,P.USERCODE ), ( D.DEFECTNAME, P.PROCEDUREID ,P.USERCODE ), ( P.PROCEDUREID ,P.USERCODE ) ) ) ) PIVOT ( SUM( 缺陷数 ) FOR 缺陷名称 IN ( '爆釉' AS CNUM1,'脱釉' AS CNUM2,'釉磕' AS CNUM3,'釉粘' AS CNUM4,'釉薄' AS CNUM5,'波纹' AS CNUM6,'釉脏' AS CNUM7,'标不良' AS CNUM8,'釉绺' AS CNUM9,'釉泡' AS CNUM10,'合计' AS C_COUNT ) ) ) QX ON CY.GOODSNAME_FRONT = QX.GOODSNAME_BEHIND AND ( CY.USERCODE_FRONT = QX.USERCODE_BEHIND OR CY.USERCODE_FRONT IS NULL ) AND CY.PROCEDUREID_CHECK_FRONT = QX.PROCEDUREID_CHECK_BEHIND And CY.USERCODE_CHECK_FRONT = QX.USERCODE_CHECK_BEHIND )"; List sqlPara = new List(); sqlPara.Add(new CDAParameter("I", i)); sqlPara.Add(new CDAParameter("YEAR", b)); sqlPara.Add(new CDAParameter("DATEEND", dayEnd)); sqlPara.Add(new CDAParameter("DATEBEGIN", dayBegin)); result = conn.ExecuteNonQuery(sqlStr1, sqlPara.ToArray()); result1 = conn.ExecuteNonQuery(sqlStr2, sqlPara.ToArray()); result2 = conn.ExecuteNonQuery(sqlStr3, sqlPara.ToArray()); result3 = conn.ExecuteNonQuery(sqlStr4, sqlPara.ToArray()); result4 = conn.ExecuteNonQuery(sqlStr5, sqlPara.ToArray()); result5 = conn.ExecuteNonQuery(sqlStr6, sqlPara.ToArray()); } conn.Commit(); if (result > 0 || result1 > 0 || result2 > 0 || result3 > 0 || result4 > 0 || result5 > 0) { Curtain.Log.Logger.Debug(b + "年" + a + "月:export035成检大帐结转成功！"); } else { Curtain.Log.Logger.Debug(b + "年" + a + "月:export035成检大帐结转0条！"); } } catch (Exception ex) { conn.Rollback(); Curtain.Log.Logger.Debug(DateTime.Now.AddMonths(-1).Year.ToString() + "年" + DateTime.Now.AddMonths(-1).Month + "月:export035成检大帐结转失败！"); Curtain.Log.Logger.Error(ex); } finally { conn.Close(); } } } #endregion #region 新增结转 #endregion ///

/// 在指定日期内有几个星期几 /// days 小于1时，返回 -1 /// weeks 未指定时，默认星期日 ///

/// /// /// /// public static int DayOfWeeksInDays(DateTime beginDate, int days, DayOfWeek week) { if (days < 1) { return -1; } DayOfWeek dw = beginDate.DayOfWeek; int ws = (days + (int)dw - (int)week - 1) / 7; if (dw <= week) { ws += 1; } return ws; } ///

/// 在指定日期内有几个星期几 /// endDate 小于 beginDate 时，返回 -1 /// weeks 未指定时，默认星期日 ///

/// /// /// /// public static int DayOfWeeksInDays(DateTime beginDate, DateTime endDate, DayOfWeek week) { return DayOfWeeksInDays(beginDate, (endDate.Date - beginDate.Date).Days + 1, week); } }