using System; using System.Collections.Generic; using System.Data; using System.ServiceModel.Security; using Dongke.IBOSS.PRD.Basics.BaseResources; using Dongke.IBOSS.PRD.Basics.DataAccess; using Dongke.IBOSS.PRD.Basics.Library; using Dongke.IBOSS.PRD.Service.SAPHegiiDataService.HGSAPDK_ZPPFM008; using Dongke.IBOSS.PRD.Service.SAPHegiiDataService.HGSAPDK_ZPPFM010; using Dongke.IBOSS.PRD.WCF.DataModels; using Newtonsoft.Json; using Newtonsoft.Json.Linq; using Oracle.ManagedDataAccess.Client; namespace Dongke.IBOSS.PRD.Service.SAPHegiiDataService { public partial class SAPDataLogic { /// /// 获取同步最后一次同步成功的日期 /// /// public static ServiceResultEntity GetLastDateByCode(string dataCode) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity sre = new ServiceResultEntity(); try { string sqlString = "select max(t.yyyymmdd) yyyymmdd\n" + " from tsap_hegii_datalog t\n" + " where t.logtype = '2'\n" + " and t.datastuts = 'S'\n" + " and t.datacode = :dataCode"; OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter(":dataCode",OracleDbType.Varchar2, dataCode, ParameterDirection.Input), }; string lastDate = oracleConn.GetSqlResultToStr(sqlString, oracleParameter); sre.Result = lastDate; return sre; } catch (Exception ex) { throw ex; } finally { if (oracleConn != null) { oracleConn.Close(); } } } /// /// 获取成品交接同步数据 /// /// public static ServiceResultEntity GetWorkData60(DateTime date) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity sre = new ServiceResultEntity(); try { string yyyymmdd = date.ToString("yyyyMMdd"); //string sqlString = // "select t.logid\n" + // " from tsap_hegii_datalog t\n" + // " where t.logtype = '2'\n" + // " and t.datastuts = 'S'\n" + // " and t.yyyymmdd = :yyyymmdd\n" + // " and t.datacode = '60'"; //OracleParameter[] oracleParameter = new OracleParameter[] //{ // new OracleParameter(":yyyymmdd",OracleDbType.Varchar2, yyyymmdd, ParameterDirection.Input), //}; //string logid = oracleConn.GetSqlResultToStr(sqlString, oracleParameter); //if (!string.IsNullOrEmpty(logid)) //{ // sqlString = "select t.yyyymmdd\n" + // " ,t.goodscode\n" + // " ,t.sapcode\n" + // " ,t.usercode\n" + // " ,t.ordercode\n" + // " ,t.orderitem\n" + // " ,t.outputnum\n" + // " ,t.recoverynum\n" + // " ,t.createtime\n" + // " ,t.ztype\n" + // " ,t.zmsg\n" + // " ,t.ZTIME\n" + // " from tsap_hegii_workdata t\n" + // //" where t.yyyymmdd = :yyyymmdd\n" + // //" and t.datacode = '60'\n" + // " where t.logid = :logid\n" + // " order by t.goodscode,t.sapcode,t.usercode,t.ordercode,t.orderitem"; // oracleParameter = new OracleParameter[] // { // new OracleParameter(":logid",OracleDbType.Int32, logid, ParameterDirection.Input), // }; // sre.Data = oracleConn.GetSqlResultToDs(sqlString, oracleParameter); // sre.Result = 1; // return sre; //} string sqlString = "select :yyyymmdd yyyymmdd, sysdate createtime\n" + " ,tt.GoodsCode\n" + " ,tt.SAPCode\n" + " ,tt.usercode\n" + " ,tt.ordercode\n" + " ,tt.orderitem\n" + " ,to_char(tt.OutputNum) OutputNum\n" + " ,to_char(tt.RecoveryNum) RecoveryNum\n" + " from (\n" + "select t.GoodsCode\n" + " ,t.SAPCode\n" + " ,t.usercode\n" + " ,t.ordercode\n" + " ,t.orderitem\n" + " ,sum(decode(t.RecyclingFlag\n" + " ,'1'\n" + " ,0\n" + " ,decode(t.DataType, 51, 1, -1))) OutputNum\n" + " ,sum(decode(t.RecyclingFlag\n" + " ,'0'\n" + " ,0\n" + " ,decode(t.DataType, 51, 1, -1))) RecoveryNum\n" + " from ( select gh.GoodsCode,gh.SAPCode,u.usercode,o.orderno,gdd.RecyclingFlag,gh.DataType \n" + " ,case\n" + " when o.orderid is null or o.orderno like 'HEGII%' then\n" + " ' '\n" + " when instr(o.orderno, '/') = 0 then\n" + " to_char(o.orderno)\n" + " else\n" + " to_char(substr(o.orderno, 1, instr(o.orderno, '/') - 1))\n" + " end ordercode -- 销售凭证\n" + " ,case\n" + " when o.orderid is null or o.orderno like 'HEGII%' or\n" + " instr(o.orderno, '/') = 0 then\n" + " '0'\n" + " when instr(o.orderno, '#') = 0 then\n" + " to_char(substr(o.orderno, instr(o.orderno, '/') + 1))\n" + " else\n" + " to_char(substr(o.orderno, instr(o.orderno, '/') + 1, instr(o.orderno, '#')-instr(o.orderno, '/')-1))\n" + " end orderitem -- 销售凭证项目\n" + " from (\n" + " select gch.GoodsCode,gch.SAPCode,decode(gch.DataType,53,51,gch.DataType) DataType,gch.userid,gch.groutingdailydetailid,gch.OTHERID from tp_pm_goodschangehistory gch\n" + " where gch.createtime >= :datebegin\n" + " and gch.createtime < :dateend\n" + " and gch.DataType in (51, 52, 53)\n" + " union all\n" + " select gch.GoodsCode,gch.SAPCode,52 DataType,gch.userid,gch.groutingdailydetailid,gch.GoodsIDAfter from tp_pm_goodschangehistory gch\n" + " where gch.createtime >= :datebegin\n" + " and gch.createtime < :dateend\n" + " and gch.DataType =53\n" + " ) gh\n" + " inner join tp_mst_user u\n" + " on u.userid = gh.userid\n" + " inner join tp_pm_groutingdailydetail gdd\n" + " on gdd.groutingdailydetailid = gh.groutingdailydetailid\n" + " and gdd.issync is null\n" + //" inner join tp_pm_finishedproduct fp\n" + //" on fp.barcode = gdd.barcode\n" + " left join tp_pm_order o\n" + //" on o.orderid = fp.fhorderid\n" + " on o.orderid = gh.OTHERID\n" + //" where gh.createtime >= :datebegin\n" + //" and gh.createtime < :dateend\n" + //" and gh.DataType in (51, 52)\n" + " ) t \n" + //" group by gh.GoodsCode, gh.SAPCode, u.usercode, o.orderno"; " group by t.GoodsCode, t.SAPCode, t.usercode, t.ordercode, t.orderitem\n" + " ) tt where tt.OutputNum <> 0 or tt.RecoveryNum <> 0 \n" + " order by tt.GoodsCode, tt.SAPCode, tt.usercode, tt.ordercode, tt.orderitem\n"; // TODO 订单变更 DateTime datebegin = date.Date; DateTime dateend = date.Date.AddDays(1); OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter(":yyyymmdd",OracleDbType.Varchar2, yyyymmdd, ParameterDirection.Input), new OracleParameter(":datebegin",OracleDbType.Date, datebegin, ParameterDirection.Input), new OracleParameter(":dateend",OracleDbType.Date, dateend, ParameterDirection.Input), }; sre.Data = oracleConn.GetSqlResultToDs(sqlString, oracleParameter); sre.Result = 0; return sre; } catch (Exception ex) { throw ex; } finally { if (oracleConn != null) { oracleConn.Close(); } } } /// /// 同步成品交接数据（手动） /// /// public static ServiceResultEntity SetWorkData60(DateTime date, DataTable workData, int userid) { IDBTransaction oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity sre = new ServiceResultEntity(); try { string yyyymmdd = date.ToString("yyyyMMdd"); string sqlString = "select t.logid\n" + " from tsap_hegii_datalog t\n" + " where t.logtype = '2'\n" + " and t.datastuts = 'S'\n" + " and t.yyyymmdd = :yyyymmdd\n" + " and t.datacode = '60'"; OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter(":yyyymmdd",OracleDbType.Varchar2, yyyymmdd, ParameterDirection.Input), }; string logid = oracleConn.GetSqlResultToStr(sqlString, oracleParameter); if (!string.IsNullOrEmpty(logid)) { sre.Status = Constant.ServiceResultStatus.Other; sre.Message = "日期【" + yyyymmdd + "】的数据已经同步过，不能重复同步。"; return sre; } sqlString = "select workcode from tp_mst_account where rownum = 1"; string workcode = oracleConn.GetSqlResultToStr(sqlString); DateTime datebegin = date.Date; DateTime dateend = date.Date.AddDays(1); logid = oracleConn.GetSqlResultToStr("select SEQ_SAP_HEGII_DataLog_ID.Nextval from dual"); string msg = "data:" + datebegin.ToString("yyyyMMdd-HHmm") + "~" + dateend.ToString("yyyyMMdd-HHmm"); sqlString = "insert into tsap_hegii_datalog\n" + " (LogID\n" + " ,LogType\n" + " ,BeginTime\n" + " ,YYYYMMDD\n" + " ,WorkCode\n" + " ,DataCode\n" + " ,DataStuts\n" + " ,DataMSG\n" + " ,CreateUserID\n" + " ,DataLogID)\n" + "values\n" + " (:LogID\n" + " ,'2'\n" + " ,sysdate\n" + " ,:YYYYMMDD\n" + " ,:WorkCode\n" + " ,'60'\n" + " ,'S'\n" + " ,:DataMSG\n" + " ,:CreateUserID\n" + " ,:LogID)"; oracleParameter = new OracleParameter[] { new OracleParameter(":LogID",OracleDbType.Int32, logid, ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userid, ParameterDirection.Input), new OracleParameter(":YYYYMMDD",OracleDbType.Varchar2, yyyymmdd, ParameterDirection.Input), new OracleParameter(":WorkCode",OracleDbType.Varchar2, workcode, ParameterDirection.Input), new OracleParameter(":DataMSG",OracleDbType.Varchar2, msg, ParameterDirection.Input), }; int r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); //r = oracleConn.ExecuteNonQuery("savepoint datalog"); //oracleConn.SavePoint("datalog"); Zppfm008 sapParameter = new Zppfm008(); if (workData != null && workData.Rows.Count > 0) { sapParameter.ZSUM = workData.Rows.Count; sapParameter.TABLE_IN = new ZSPP100[sapParameter.ZSUM]; sqlString = "insert into TSAP_HEGII_WorkData\n" + " (YYYYMMDD\n" + " ,WorkCode\n" + " ,DataCode\n" + " ,GoodsCode\n" + " ,SAPCode\n" + " ,UserCode\n" + " ,OutputNum\n" + " ,RECOVERYNUM\n" + " ,ORDERCODE\n" + " ,ORDERITEM\n" + " ,LogID,createtime)\n" + "values\n" + " ('" + yyyymmdd + "'\n" + " ,'" + workcode + "'\n" + " ,'60'\n" + " ,:GoodsCode\n" + " ,:SAPCode\n" + " ,:UserCode\n" + " ,:OutputNum\n" + " ,:RECOVERYNUM\n" + " ,:ORDERCODE\n" + " ,:ORDERITEM\n" + " ," + logid + ",:createtime)"; int index = 0; foreach (DataRow item in workData.Rows) { ZSPP100 info100 = new ZSPP100(); // 工厂 info100.WERKS = workcode; // 型号 info100.GROES = item["GoodsCode"].ToString(); // 物料编号 info100.MATNR = item["SAPCode"].ToString(); // 生产工号 info100.ZGHNU = item["UserCode"].ToString(); // 数据节点 info100.ZJDNU = "60"; // 时间戳 info100.ZSCNU = yyyymmdd; // 销售凭证 info100.VBELN = item["ORDERCODE"].ToString().Trim(); // 销售凭证项目 info100.POSNR = item["ORDERITEM"].ToString(); // 产量 info100.ZCLNG = item["OutputNum"].ToString(); // 损坯 info100.ZSPNG = "0"; // 清除 info100.ZQCNG = "0"; // 回收 info100.ZHSNG = item["RECOVERYNUM"].ToString(); // 干补 info100.ZGBNG = "0"; info100.ZKSSJ = "000000"; info100.ZJSRQ = Convert.ToDecimal(yyyymmdd) + 1; oracleParameter = new OracleParameter[] { new OracleParameter(":GoodsCode",OracleDbType.Varchar2, item["GoodsCode"], ParameterDirection.Input), new OracleParameter(":SAPCode",OracleDbType.Varchar2, item["SAPCode"], ParameterDirection.Input), new OracleParameter(":UserCode",OracleDbType.Varchar2, item["UserCode"], ParameterDirection.Input), new OracleParameter(":OutputNum",OracleDbType.Varchar2, item["OutputNum"], ParameterDirection.Input), new OracleParameter(":RECOVERYNUM",OracleDbType.Varchar2, item["RECOVERYNUM"], ParameterDirection.Input), new OracleParameter(":ORDERCODE",OracleDbType.Varchar2, item["ORDERCODE"], ParameterDirection.Input), new OracleParameter(":ORDERITEM",OracleDbType.Varchar2, item["ORDERITEM"], ParameterDirection.Input), new OracleParameter(":createtime",OracleDbType.Date, item["createtime"], ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); sapParameter.TABLE_IN[index++] = info100; } } else { sapParameter.ZSUM = 0; sapParameter.TABLE_IN = new ZSPP100[sapParameter.ZSUM]; } ZPPFM008Response result = HGSAPDK_ZPPFM008(sapParameter, yyyymmdd, "60"); //if (result.Ztype == "E") //{ // sqlString = "update tsap_hegii_datalog t set t.EndTime = sysdate, DataStuts = 'E', DataMSG = DataMSG||:msg where logid = :logid"; // oracleParameter = new OracleParameter[] // { // new OracleParameter(":msg",OracleDbType.Varchar2, result.Zmsg, ParameterDirection.Input), // new OracleParameter(":logid",OracleDbType.Int32, logid, ParameterDirection.Input), // }; // r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); //} //else { if (result.TABLE_OUT != null && result.TABLE_OUT.Length > 0) { sqlString = "update TSAP_HEGII_WorkData t set t.ZTime =sysdate, ZTYPE = :ZTYPE, ZMSG = :ZMSG where logid = " + logid + " \n and YYYYMMDD='" + yyyymmdd + "' and WorkCode='" + workcode + "' and DataCode='60' and GoodsCode=:GoodsCode and SAPCode=:SAPCode and UserCode=:UserCode \n" + "and OrderCode=:OrderCode and OrderItem=:OrderItem"; foreach (ZSPP100 item in result.TABLE_OUT) { string posnr = item.POSNR.TrimStart('0'); oracleParameter = new OracleParameter[] { new OracleParameter(":ZTYPE",OracleDbType.Varchar2, item.ZTYPE, ParameterDirection.Input), new OracleParameter(":ZMSG",OracleDbType.Varchar2, item.ZMSG, ParameterDirection.Input), new OracleParameter(":GoodsCode",OracleDbType.Varchar2, item.GROES, ParameterDirection.Input), new OracleParameter(":SAPCode",OracleDbType.Varchar2, item.MATNR, ParameterDirection.Input), new OracleParameter(":UserCode",OracleDbType.Varchar2, item.ZGHNU, ParameterDirection.Input), new OracleParameter(":OrderCode",OracleDbType.Varchar2, (string.IsNullOrEmpty(item.VBELN) ? " " : item.VBELN), ParameterDirection.Input), new OracleParameter(":OrderItem",OracleDbType.Varchar2, (string.IsNullOrEmpty(posnr) ? "0" : posnr), ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); } } //:msg||chr(13)||DataMSG result.ZMSG = $"{result.ZMSG}({sapParameter.ZSUM})"; sqlString = "update tsap_hegii_datalog t set t.EndTime = sysdate, DataStuts = :DataStuts, DataMSG =nvl2(:msg,:msg||' '||chr(13)||DataMSG,DataMSG) where logid = :logid"; oracleParameter = new OracleParameter[] { new OracleParameter(":DataStuts",OracleDbType.Varchar2, result.ZTYPE, ParameterDirection.Input), new OracleParameter(":msg",OracleDbType.Varchar2, result.ZMSG, ParameterDirection.Input), new OracleParameter(":logid",OracleDbType.Int32, logid, ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); } oracleConn.Commit(); sre.Message = result.ZMSG; sre.Result = result.ZTYPE; return sre; } catch (Exception ex) { throw ex; } finally { if (oracleConn != null && oracleConn.ConnState == ConnectionState.Open) { oracleConn.Disconnect(); } } } /// /// 查询同步日志 /// /// /// /// public static ServiceResultEntity GetDataLog(ClientRequestEntity cre) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity sre = new ServiceResultEntity(); try { string sqlString = "select dl.logid\n" + " ,dl.logtype\n" + " ,dl.begintime\n" + " ,dl.endtime\n" + " ,dl.yyyymmdd\n" + " ,dl.workcode\n" + " ,dl.datacode\n" + " ,dc.datacodename\n" + " ,dl.datastuts\n" + " ,dl.datamsg\n" + " ,dl.datalogid\n" + " ,dl.executedatebegin\n" + " ,dl.executedateend\n" + " ,u.usercode synusercode\n" + " from tsap_hegii_datalog dl\n" + " inner join tsap_hegii_datacode dc\n" + " on dc.datacode = dl.datacode\n" + " left join tp_mst_user u\n" + " on u.userid = dl.createuserid\n" + " where dl.logtype IN('2','3')\n" + " and dl.yyyymmdd >= :datebegin\n" + " and dl.yyyymmdd <= :dateend\n"; OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter(":datebegin",OracleDbType.Varchar2, cre.Properties["datebegin"], ParameterDirection.Input), new OracleParameter(":dateend",OracleDbType.Varchar2, cre.Properties["dateend"], ParameterDirection.Input), }; string datastuts = cre.Properties["datastuts"] + ""; if (!string.IsNullOrEmpty(datastuts)) { sqlString += " and dl.datastuts in (" + datastuts + ")\n"; } string datacode = cre.Properties["datacode"] + ""; if (!string.IsNullOrEmpty(datacode)) { sqlString += " and dl.datacode = '" + datacode + "'\n"; //sqlString += " and dl.datacode in (" + datacode + ")\n"; } sqlString += " order by dl.datacode, dl.endtime desc"; sre.Data = oracleConn.GetSqlResultToDs(sqlString, oracleParameter); return sre; } catch (Exception ex) { throw ex; } finally { if (oracleConn != null) { oracleConn.Close(); } } } /// /// 查询产量同步明细 /// /// /// /// public static ServiceResultEntity GetWorkDataByDataLog(int logid) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity sre = new ServiceResultEntity(); try { string sqlString = "\n" + "select wd.yyyymmdd\n" + " ,wd.workcode\n" + " ,wd.datacode\n" + " ,dc.datacodename\n" + " ,wd.goodscode\n" + " ,wd.sapcode\n" + " ,wd.usercode\n" + " ,wd.ordercode\n" + " ,wd.orderitem\n" + " ,to_number(wd.outputnum) outputnum\n" + " ,to_number(wd.scrapnum) scrapnum\n" + " ,to_number(wd.cleanupnum) cleanupnum\n" + " ,to_number(wd.recoverynum) recoverynum\n" + " ,to_number(wd.repairnum) repairnum\n" + " ,wd.createtime\n" + " ,wd.ztype\n" + " ,wd.zmsg\n" + " ,wd.ztime\n" + " ,wd.testmouldflag\n" + " ,wd.zscs\n" + " ,wd.logid\n" + " from tsap_hegii_workdata wd\n" + " inner join tsap_hegii_datacode dc\n" + " on dc.datacode = wd.datacode\n" + " where wd.logid = :logid \n" + " order by wd.goodscode,wd.sapcode,wd.usercode,wd.ordercode,wd.outputnum \n"; OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter(":logid",OracleDbType.Int32, logid, ParameterDirection.Input), }; sre.Data = oracleConn.GetSqlResultToDs(sqlString, oracleParameter); //sqlString = "\n" + //"select wd.logid\n" + //" ,wd.goodscode\n" + //" ,wd.sapcode\n" + //" ,wd.procedureName\n" + //" ,wd.scrapnum\n" + //" ,wd.unscrapnum\n" + //" ,wd.scrapnum-wd.unscrapnum num\n" + //" from TSAP_HEGII_WORKDATA_SNum wd\n" + ////" inner join tsap_hegii_datacode dc\n" + ////" on dc.datacode = wd.datacode\n" + //" where wd.logid = :logid \n" + //" order by wd.goodscode,wd.sapcode,wd.procedureName \n"; sqlString = "select decode(gid\n" + " ,3\n" + " ,'合计'\n" + " ,1\n" + " ,'小计[' || procedureName || ']'\n" + " ,procedureName) procedureName\n" + " ,decode(gid, 0, goodscode, '--') goodscode\n" + " ,decode(gid, 0, sapcode, '--') sapcode\n" + " ,decode(gid, 0, usercode, '--') usercode\n" + " ,scrapnum\n" + " ,unscrapnum\n" + " ,scrapnum - unscrapnum scrapsum\n" + " ,cleanupnum\n" + " ,uncleanupnum\n" + " ,cleanupnum - uncleanupnum cleanupsum\n" + " ,repairnum\n" + " ,recoverynum\n" + " from (select grouping_id(wd.procedureName, wd.goodscode) gid\n" + " ,wd.procedureName\n" + " ,wd.goodscode\n" + " ,wd.sapcode\n" + " ,wd.usercode\n" + " ,sum(wd.scrapnum) scrapnum\n" + " ,sum(wd.unscrapnum) unscrapnum\n" + " ,sum(wd.cleanupnum) cleanupnum\n" + " ,sum(wd.uncleanupnum) uncleanupnum\n" + " ,sum(wd.repairnum) repairnum\n" + " ,sum(wd.recoverynum) recoverynum\n" + " from TSAP_HEGII_WORKDATA_SNum wd\n" + " where wd.logid = :logid \n" + " group by grouping sets((wd.procedureName, wd.goodscode, wd.sapcode,wd.usercode),(wd.procedureName),())\n" + " order by wd.procedureName, wd.goodscode, wd.sapcode)"; sre.Data.Tables.Add(oracleConn.GetSqlResultToDt(sqlString, oracleParameter)); return sre; } catch (Exception ex) { throw ex; } finally { if (oracleConn != null) { oracleConn.Close(); } } } /// /// 查询SAP同步工序损坯明细 /// /// /// /// public static ServiceResultEntity GetWorkDataSPSumByDate(ClientRequestEntity cre) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity sre = new ServiceResultEntity(); try { string sqlString = "select decode(gid\n" + " ,3\n" + " ,'合计'\n" + " ,1\n" + " ,'小计[' || procedureName || ']'\n" + " ,procedureName) procedureName\n" + " ,decode(gid, 0, goodscode, '--') goodscode\n" + " ,decode(gid, 0, sapcode, '--') sapcode\n" + " ,decode(gid, 0, usercode, '--') usercode\n" + " ,scrapnum\n" + " ,unscrapnum\n" + " ,scrapnum - unscrapnum scrapsum\n" + " ,cleanupnum\n" + " ,uncleanupnum\n" + " ,cleanupnum - uncleanupnum cleanupsum\n" + " ,repairnum\n" + " ,recoverynum\n" + " from (select grouping_id(wd.procedureName, wd.goodscode) gid\n" + " ,wd.procedureName\n" + " ,wd.goodscode\n" + " ,wd.sapcode\n" + " ,wd.usercode\n" + " ,sum(wd.scrapnum) scrapnum\n" + " ,sum(wd.unscrapnum) unscrapnum\n" + " ,sum(wd.cleanupnum) cleanupnum\n" + " ,sum(wd.uncleanupnum) uncleanupnum\n" + " ,sum(wd.repairnum) repairnum\n" + " ,sum(wd.recoverynum) recoverynum\n" + " from TSAP_HEGII_DATALOG dl\n" + " inner join TSAP_HEGII_WORKDATA_SNum wd\n" + " on wd.logid = dl.logid \n" + " where dl.logtype = '1' \n" + " and dl.yyyymmdd >= :datebegin\n" + " and dl.yyyymmdd <= :dateend\n" + " group by grouping sets((wd.procedureName, wd.goodscode, wd.sapcode,wd.usercode),(wd.procedureName),())\n" + " order by wd.procedureName, wd.goodscode, wd.sapcode)"; OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter(":datebegin",OracleDbType.Varchar2, cre.Properties["datebegin"], ParameterDirection.Input), new OracleParameter(":dateend",OracleDbType.Varchar2, cre.Properties["dateend"], ParameterDirection.Input), }; sre.Data = oracleConn.GetSqlResultToDs(sqlString, oracleParameter); return sre; } catch (Exception ex) { throw ex; } finally { if (oracleConn != null) { oracleConn.Close(); } } } /// /// 查询成品条码同步明细 /// /// /// /// public static ServiceResultEntity GetFinishedProductByDataLog(int logid) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity sre = new ServiceResultEntity(); try { string sqlString = "select fp.yyyymmdd\n" + " ,fp.workcode\n" + " ,fp.barcode\n" + " ,fp.outcode\n" + " ,fp.goodscode\n" + " ,fp.sapcode\n" + " ,fp.sapflbatchno\n" + " ,fp.sapfhundoflag\n" + " ,fp.createtime\n" + " ,fp.ztype\n" + " ,fp.zmsg\n" + " ,fp.ztime\n" + " ,fp.logid\n" + " ,fp.ordercode\n" + " ,fp.orderitem\n" + " ,fp.securitycode\n" + //2021年12月9日10:31:42 by fy modify 增加防伪码 " from tsap_hegii_finishedproduct fp\n" + " where fp.logid = :logid\n" + " order by fp.ztime, fp.barcode\n"; OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter(":logid",OracleDbType.Int32, logid, ParameterDirection.Input), }; sre.Data = oracleConn.GetSqlResultToDs(sqlString, oracleParameter); return sre; } catch (Exception ex) { throw ex; } finally { if (oracleConn != null) { oracleConn.Close(); } } } /// /// 同步产量报工（手动） /// /// /// /// /// public static ServiceResultEntity SetWorkData10_50(DateTime date, string datacode, int userid) { IDBTransaction oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity sre = new ServiceResultEntity(); try { string yyyymmdd = date.ToString("yyyyMMdd"); string sqlString = "select t.logid\n" + " from tsap_hegii_datalog t\n" + " where t.logtype = '2'\n" + " and t.datastuts = 'S'\n" + " and t.yyyymmdd = :yyyymmdd\n" + " and t.datacode = :datacode"; OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter(":yyyymmdd",OracleDbType.Varchar2, yyyymmdd, ParameterDirection.Input), new OracleParameter(":datacode",OracleDbType.Varchar2, datacode, ParameterDirection.Input), }; string logid = oracleConn.GetSqlResultToStr(sqlString, oracleParameter); if (!string.IsNullOrEmpty(logid)) { sre.Status = Constant.ServiceResultStatus.Other; sre.Message = "日期【" + yyyymmdd + "】的数据已经同步过，不能重复同步。"; return sre; } sqlString = "select t.logid\n" + " from tsap_hegii_datalog t\n" + " where t.logtype = '1'\n" + " and t.datastuts = 'S'\n" + " and t.yyyymmdd = :yyyymmdd\n" + " and t.datacode = :datacode" + " and t.createuserid is null for update"; string datalogid = oracleConn.GetSqlResultToStr(sqlString, oracleParameter); sqlString = "select workcode from tp_mst_account where rownum = 1"; string workcode = oracleConn.GetSqlResultToStr(sqlString); //DateTime datebegin = date.Date; //DateTime dateend = date.Date.AddDays(1); logid = oracleConn.GetSqlResultToStr("select SEQ_SAP_HEGII_DataLog_ID.Nextval from dual"); string msg = null; //string msg = "data:" + datebegin.ToString("yyyyMMdd-HHmm") + "~" + // dateend.ToString("yyyyMMdd-HHmm"); sqlString = "insert into tsap_hegii_datalog\n" + " (LogID\n" + " ,LogType\n" + " ,BeginTime\n" + " ,YYYYMMDD\n" + " ,WorkCode\n" + " ,DataCode\n" + " ,DataStuts\n" + " ,DataMSG\n" + " ,CreateUserID\n" + " ,DataLogID)\n" + "values\n" + " (:LogID\n" + " ,'2'\n" + " ,sysdate\n" + " ,:YYYYMMDD\n" + " ,:WorkCode\n" + " ,:DataCode\n" + " ,:DataStuts\n" + " ,:DataMSG\n" + " ,:CreateUserID\n" + " ,:DataLogID)"; int r = 0; if (string.IsNullOrEmpty(datalogid)) { oracleParameter = new OracleParameter[] { new OracleParameter(":LogID",OracleDbType.Int32, logid, ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userid, ParameterDirection.Input), new OracleParameter(":YYYYMMDD",OracleDbType.Varchar2, yyyymmdd, ParameterDirection.Input), new OracleParameter(":WorkCode",OracleDbType.Varchar2, workcode, ParameterDirection.Input), new OracleParameter(":DataCode",OracleDbType.Varchar2, datacode, ParameterDirection.Input), new OracleParameter(":DataMSG",OracleDbType.Varchar2, "DKMES-ERROR:NO DATA " + msg, ParameterDirection.Input), new OracleParameter(":DataStuts",OracleDbType.Varchar2, "E", ParameterDirection.Input), new OracleParameter(":DataLogID",OracleDbType.Int32, 0, ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); oracleConn.Commit(); sre.Status = Constant.ServiceResultStatus.Other; sre.Message = "没有同步数据"; sre.Result = "E"; return sre; } oracleParameter = new OracleParameter[] { new OracleParameter(":LogID",OracleDbType.Int32, logid, ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userid, ParameterDirection.Input), new OracleParameter(":YYYYMMDD",OracleDbType.Varchar2, yyyymmdd, ParameterDirection.Input), new OracleParameter(":WorkCode",OracleDbType.Varchar2, workcode, ParameterDirection.Input), new OracleParameter(":DataCode",OracleDbType.Varchar2, datacode, ParameterDirection.Input), new OracleParameter(":DataMSG",OracleDbType.Varchar2, msg, ParameterDirection.Input), new OracleParameter(":DataStuts",OracleDbType.Varchar2, "S", ParameterDirection.Input), new OracleParameter(":DataLogID",OracleDbType.Int32, datalogid, ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); sqlString = "select wd.yyyymmdd\n" + " ,wd.workcode\n" + " ,wd.DataCode\n" + " ,wd.GoodsCode\n" + " ,wd.SAPCode\n" + " ,wd.UserCode\n" + " ,wd.ORDERCODE\n" + " ,wd.ORDERITEM\n" + " ,wd.OutputNum\n" + " ,wd.ScrapNum\n" + " ,wd.CleanupNum\n" + " ,wd.RecoveryNum\n" + " ,wd.RepairNum\n" + " from tsap_hegii_workdata wd where wd.logid = :datalogid"; oracleParameter = new OracleParameter[] { new OracleParameter(":datalogid",OracleDbType.Int32, datalogid, ParameterDirection.Input), }; DataTable workData = oracleConn.GetSqlResultToDt(sqlString, oracleParameter); Zppfm008 sapParameter = new Zppfm008(); if (workData != null && workData.Rows.Count > 0) { sapParameter.ZSUM = workData.Rows.Count; sapParameter.TABLE_IN = new ZSPP100[sapParameter.ZSUM]; int index = 0; foreach (DataRow item in workData.Rows) { ZSPP100 info100 = new ZSPP100(); // 工厂 info100.WERKS = workcode; // 型号 info100.GROES = item["GoodsCode"].ToString(); // 物料编号 info100.MATNR = item["SAPCode"].ToString(); // 生产工号 info100.ZGHNU = item["UserCode"].ToString(); // 数据节点 info100.ZJDNU = item["DataCode"].ToString(); // 时间戳 info100.ZSCNU = yyyymmdd; // 销售凭证 info100.VBELN = item["ORDERCODE"].ToString().Trim(); // 销售凭证项目 info100.POSNR = item["ORDERITEM"].ToString(); // 产量 info100.ZCLNG = item["OutputNum"].ToString(); // 损坯 info100.ZSPNG = item["SCRAPNUM"].ToString(); // 清除 info100.ZQCNG = item["CLEANUPNUM"].ToString(); // 回收 info100.ZHSNG = item["RECOVERYNUM"].ToString(); // 干补 info100.ZGBNG = item["REPAIRNUM"].ToString(); info100.ZKSSJ = "000000"; info100.ZJSRQ = Convert.ToDecimal(yyyymmdd) + 1; sapParameter.TABLE_IN[index++] = info100; } } else { sapParameter.ZSUM = 0; sapParameter.TABLE_IN = new ZSPP100[sapParameter.ZSUM]; } ZPPFM008Response result = HGSAPDK_ZPPFM008(sapParameter, yyyymmdd, datacode); //if (result.Ztype == "E") //{ // sqlString = "update tsap_hegii_datalog t set t.EndTime = sysdate, DataStuts = 'E', DataMSG = :msg||chr(13)||DataMSG where logid = :logid"; // oracleParameter = new OracleParameter[] // { // new OracleParameter(":msg",OracleDbType.Varchar2, result.Zmsg, ParameterDirection.Input), // new OracleParameter(":logid",OracleDbType.Int32, logid, ParameterDirection.Input), // }; // r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); //} //else { if (result.TABLE_OUT != null && result.TABLE_OUT.Length > 0) { sqlString = "update TSAP_HEGII_WorkData t set t.ZTime =sysdate, ZTYPE = :ZTYPE, ZMSG = :ZMSG where logid = " + datalogid + " \n and YYYYMMDD='" + yyyymmdd + "' and WorkCode='" + workcode + "' and DataCode=:DataCode and GoodsCode=:GoodsCode and SAPCode=:SAPCode and UserCode=:UserCode \n" + "and OrderCode=:OrderCode and OrderItem=:OrderItem"; foreach (ZSPP100 item in result.TABLE_OUT) { string posnr = item.POSNR.TrimStart('0'); oracleParameter = new OracleParameter[] { new OracleParameter(":ZTYPE",OracleDbType.Varchar2, item.ZTYPE, ParameterDirection.Input), new OracleParameter(":ZMSG",OracleDbType.Varchar2, item.ZMSG, ParameterDirection.Input), new OracleParameter(":DataCode",OracleDbType.Varchar2, datacode, ParameterDirection.Input), new OracleParameter(":GoodsCode",OracleDbType.Varchar2, item.GROES, ParameterDirection.Input), new OracleParameter(":SAPCode",OracleDbType.Varchar2, item.MATNR, ParameterDirection.Input), new OracleParameter(":UserCode",OracleDbType.Varchar2, item.ZGHNU, ParameterDirection.Input), new OracleParameter(":OrderCode",OracleDbType.Varchar2, (string.IsNullOrEmpty(item.VBELN) ? " " : item.VBELN), ParameterDirection.Input), new OracleParameter(":OrderItem",OracleDbType.Varchar2, ((string.IsNullOrEmpty(posnr)) ? "0" : posnr), ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); } } //:msg||chr(13)||DataMSG nvl2(:msg,:msg||chr(13)||DataMS,DataMS) result.ZMSG = $"{result.ZMSG}({sapParameter.ZSUM})"; sqlString = "update tsap_hegii_datalog t set t.EndTime = sysdate, DataStuts = :DataStuts, DataMSG =:msg where logid = :logid"; oracleParameter = new OracleParameter[] { new OracleParameter(":DataStuts",OracleDbType.Varchar2, result.ZTYPE, ParameterDirection.Input), new OracleParameter(":msg",OracleDbType.Varchar2, result.ZMSG, ParameterDirection.Input), new OracleParameter(":logid",OracleDbType.Int32, logid, ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); if (result.ZTYPE == "S") { sqlString = "update tsap_hegii_datalog t set t.createuserid = :userid where logid = :datalogid"; oracleParameter = new OracleParameter[] { new OracleParameter(":datalogid",OracleDbType.Int32, datalogid, ParameterDirection.Input), new OracleParameter(":userid",OracleDbType.Int32, userid, ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); } } oracleConn.Commit(); sre.Message = result.ZMSG; sre.Result = result.ZTYPE; return sre; } catch (Exception ex) { throw ex; } finally { if (oracleConn != null && oracleConn.ConnState == ConnectionState.Open) { oracleConn.Disconnect(); } } } /// /// 同步产量报工（手动）重载 /// /// /// /// /// /// public static ServiceResultEntity SetWorkData10_50(string datacode, int userid, DateTime ndate) { ServiceResultEntity sre = new ServiceResultEntity(); IDBTransaction oracleConn = null; try { #region 事务1，执行存储过程 OracleParameter[] paras = null; int logid = 0; string message = string.Empty; // 10 模具 if ("10".Equals(datacode)) { paras = new OracleParameter[] { new OracleParameter("in_dateend", OracleDbType.Date, ndate, ParameterDirection.Input), new OracleParameter("out_logid", OracleDbType.Int32, null, ParameterDirection.Output), new OracleParameter("out_msg", OracleDbType.NVarchar2, 500, null, ParameterDirection.Output) }; oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); DataSet ds = oracleConn.ExecStoredProcedure("pro_sap_hegii_workdata_10_1", paras); int.TryParse(paras[1].Value + "", out logid); message = paras[2].Value + ""; oracleConn.Commit(); } // 20 湿坯 else if ("20".Equals(datacode)) { paras = new OracleParameter[] { new OracleParameter("in_dateend", OracleDbType.Date, ndate, ParameterDirection.Input), new OracleParameter("out_logid", OracleDbType.Int32, null, ParameterDirection.Output), new OracleParameter("out_msg", OracleDbType.NVarchar2, 500, null, ParameterDirection.Output) }; oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); DataSet ds = oracleConn.ExecStoredProcedure("pro_sap_hegii_workdata_20_1", paras); int.TryParse(paras[1].Value + "", out logid); message = paras[2].Value + ""; oracleConn.Commit(); } // 30 精坯、40 釉坯、50 烧成 else if ("30".Equals(datacode) || "40".Equals(datacode) || "50".Equals(datacode)) { paras = new OracleParameter[] { new OracleParameter("in_dateend", OracleDbType.Date, ndate, ParameterDirection.Input), new OracleParameter("in_datacode", OracleDbType.Varchar2, datacode, ParameterDirection.Input), new OracleParameter("out_logid", OracleDbType.Int32, null, ParameterDirection.Output), new OracleParameter("out_msg", OracleDbType.NVarchar2, 500, null, ParameterDirection.Output) }; oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); DataSet ds = oracleConn.ExecStoredProcedure("pro_sap_hegii_workdata_30_1", paras); int.TryParse(paras[2].Value + "", out logid); message = paras[3].Value + ""; oracleConn.Commit(); } // 如果logid为0，则数据没有生成 if (logid == 0) { sre.Status = Constant.ServiceResultStatus.Other; sre.Message = message; return sre; } #endregion // 事物2，同步SAP接口 sre = SyncSap(ndate, datacode, userid, logid); return sre; } catch (Exception ex) { throw ex; } finally { if (oracleConn != null && oracleConn.ConnState == ConnectionState.Open) { oracleConn.Disconnect(); } } } public static ServiceResultEntity SyncSap5000_60_BSJ(DateTime date) { ServiceResultEntity sre = new ServiceResultEntity(); IDBTransaction oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { string yyyymmdd = date.ToString("yyyyMMdd"); string sqlString = "SELECT TO_CHAR(wd.yyyymmdd) yyyymmdd\n" + " ,TO_CHAR(5000) WERKS\n" + " ,TO_CHAR(wd.goodscode) GROES\n" + " ,TO_CHAR(wd.sapcode) MATNR\n" + " ,TO_CHAR(wd.usercode) ZGHNU\n" + " ,TO_CHAR(wd.datacode) ZJDNU\n" + " ,to_char(DL.EXECUTEDATEBEGIN, 'YYYYMMDD' ) ZSCNU\n" + " ,to_char(DL.EXECUTEDATEBEGIN, 'HH24MISS' ) ZKSSJ\n" + " ,to_char(DL.EXECUTEDATEEND, 'YYYYMMDDHH24MISS' ) ZJSRQ\n" + " ,TO_CHAR(wd.ordercode) VBELN\n" + " ,TO_CHAR(wd.orderitem) POSNR\n" + " ,TO_CHAR(wd.outputnum) ZCLNG\n" + " ,TO_CHAR(wd.scrapnum) ZSPNG\n" + " ,TO_CHAR(wd.cleanupnum) ZQCNG\n" + " ,TO_CHAR(wd.recoverynum) ZHSNG\n" + " ,TO_CHAR(wd.repairnum) ZGBNG\n" + " ,TO_CHAR(wd.testmouldflag) ZSCMS\n" + " ,'T' AS zscs\n" + " ,TO_CHAR(wd.WORKSHOP) ZSCCJ\n" + " ,TO_CHAR( :yyyymmdd) CHARG\n" + " ,'60' datacode\n" + " FROM tsap_hegii_workdata_bg wd\n" + " INNER JOIN TSAP_HEGII_DATALOG_BG DL\n" + " ON wd.LOGID = DL.LOGID \n" + " WHERE DL.DataStuts = 'F' AND wd.datacode = '60' and DL.EXECUTEDATEEND < :ndate"; OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":yyyymmdd",yyyymmdd), new OracleParameter(":ndate",date), }; DataTable workData = oracleConn.GetSqlResultToDt(sqlString, paras); OutputLog.TraceLog(LogPriority.Information, "BGToSAP60", "报工60" + date.ToString("yyyy-MM-dd HH:mm:ss"), workData.Rows.Count + "", LocalPath.LogExePath + "SAP_HEGII\\Error_"); int num = workData.Rows.Count; // 调用SAP接口 if (workData != null && workData.Rows.Count > 0) { string postString = "{\"ZSUM\":" + num.ToString() + ",\"TABLE_IN\":{\"item\":" + JsonHelper.ToJson(ModelConvertHelper.ConvertToModel(workData)) + "}}"; OutputLog.TraceLog(LogPriority.Information, "BGToSAP60", "报工60" + date.ToString("yyyy-MM-dd HH:mm:ss"), postString, LocalPath.LogExePath + "SAP_HEGII\\Error_"); //测试 string url030 = "http://hgs4podev.hegii.com:50200/RESTAdapter/DKMES/ZPPFM030"; //正式 //string url030 = "http://hgs4powd1.hegii.com:8000/RESTAdapter/DKMES/ZPPFM030"; string result = PostData(url030, postString, "POST"); OutputLog.TraceLog(LogPriority.Information, "BGToSAP60", "报工60" + date.ToString("yyyy-MM-dd HH:mm:ss"), result, LocalPath.LogExePath + "SAP_HEGII\\Error_"); sre.Message = JObject.Parse(result)["ZMSG"].ToString(); sre.Result = JObject.Parse(result)["ZTYPE"].ToString(); } return sre; } catch (Exception ex) { OutputLog.TraceLog(LogPriority.Error, "BGToSAP", "报工" + date.ToString("yyyy-MM-dd HH:mm:ss"), ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); throw ex; } finally { if (oracleConn != null && oracleConn.ConnState == ConnectionState.Open) { oracleConn.Disconnect(); } } } public static ServiceResultEntity SyncSap5000_test(DateTime date, string datacode, DateTime ndate) { ServiceResultEntity sre = new ServiceResultEntity(); IDBTransaction oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { OracleParameter[] paras = null; string workcode = "5000"; string yyyymmdd = ndate.ToString("yyyyMMdd"); string yyyymmddhh24miss = ndate.ToString("yyyyMMddHH24miss"); string sqlString = ""; if ("10".Equals(datacode)) { sqlString = @"SELECT to_char(:IN_DATEEND, 'yyyymmdd') yyyymmdd, TO_CHAR(5000) WERKS,to_char(g.goodscode) GROES,to_char(g.mouldmaterialcode) MATNR,to_char(m.usercode) ZGHNU, to_char(:IN_DATACODE) ZJDNU, to_char(:V_DATEBEGIN, 'YYYYMMDD' ) ZSCNU, to_char(:V_DATEBEGIN, 'HH24MISS' ) ZKSSJ, to_char(:IN_DATEEND, 'YYYYMMDDHH24MISS' ) ZJSRQ, '' VBELN, '' POSNR, to_char(SUM(decode(mhh.operationtype, 1, m.standardgroutingsum, 0))) ZCLNG, to_char(SUM(CASE WHEN mhh.operationtype IN (2, 9, 11, -1) THEN greatest(m.standardgroutingsum - mhh.groutingnum, 0) WHEN mhh.operationtype IN (3, 0) THEN least(mhh.groutingnum - m.standardgroutingsum, 0) ELSE 0 END)) ZSPNG, '0' ZQCNG, '0' ZHSNG, '0' ZGBNG, to_char(m.testmouldflag) ZSCMS, DECODE( :IN_DATACODE,20,TO_CHAR(g.goods_line_code),'T') AS zscs, to_char(0) ZSCCJ, TO_CHAR( :yyyymmdd) CHARG, TO_CHAR( :IN_DATACODE) datacode FROM (SELECT mh.goodsid, mh.mouldid, mh.operationtype, mh.groutingnum FROM tp_pc_mouldchangehistory mh WHERE mh.operationtype IN (1, 2, 3, 9, 11, -1) AND mh.createtime >= :V_DATEBEGIN AND mh.createtime < :IN_DATEEND UNION ALL SELECT mh.goodsidafter, mh.mouldid, 0, mh.groutingnum FROM tp_pc_mouldchangehistory mh WHERE mh.operationtype = -1 AND mh.createtime >= :V_DATEBEGIN AND mh.createtime < :IN_DATEEND) mhh INNER JOIN tp_mst_goods g ON mhh.goodsid = g.goodsid INNER JOIN tp_pc_mould m ON m.mouldid = mhh.mouldid GROUP BY g.goodscode, g.mouldmaterialcode, m.usercode,m.testmouldflag,g.goods_line_code"; } // 20 湿坯 else if ("20".Equals(datacode)) { sqlString = @" SELECT to_char(:IN_DATEEND, 'yyyymmdd') yyyymmdd, TO_CHAR(5000) WERKS,TO_CHAR(SPP.GOODSCODE) GROES,TO_CHAR(SPP.SAPCODE) MATNR,TO_CHAR(SPP.usercode) ZGHNU, TO_CHAR(:IN_DATACODE) ZJDNU, to_char(:V_DATEBEGIN, 'YYYYMMDD' ) ZSCNU, to_char(:V_DATEBEGIN, 'HH24MISS' ) ZKSSJ, to_char(:IN_DATEEND, 'YYYYMMDDHH24MISS' ) ZJSRQ, '' VBELN, '' POSNR, TO_CHAR(SUM(SPP.OUTPUTNUM)) ZCLNG, TO_CHAR(SUM(SPP.SCRAPNUM)) ZSPNG, TO_CHAR(SUM(SPP.CLEANUPNUM)) ZQCNG,'0' ZHSNG, TO_CHAR(SUM(SPP.REPAIRNUM)) ZGBNG, DECODE( SPP.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-' ) AS ZSCMS, DECODE( :IN_DATACODE,20,TO_CHAR(SPP.goods_line_code),'T') AS zscs, TO_CHAR(SPP.WORKSHOP) ZSCCJ, TO_CHAR( :yyyymmdd) CHARG, TO_CHAR( :IN_DATACODE) datacode FROM ( -- 成型产量 SELECT GH.GOODSCODE, GH.SAPCODE, U.USERCODE, SUM(DECODE(GH.DATATYPE, 1, 1, -1)) OUTPUTNUM, 0 SCRAPNUM, 0 CLEANUPNUM, 0 REPAIRNUM, CASE WHEN (INSTR(GDD.GROUTINGLINECODE,'A') = 1 OR INSTR(GDD.GROUTINGLINECODE,'D') = 1) AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN 1 WHEN (INSTR(GDD.GROUTINGLINECODE,'B') = 1 OR INSTR(GDD.GROUTINGLINECODE,'D') = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN 2 WHEN INSTR(GDD.GROUTINGLINECODE,'C') = 1 THEN 3 ELSE 0 END AS WORKSHOP, GDD.TESTMOULDFLAG, G.GOODS_LINE_CODE FROM TP_PM_GOODSCHANGEHISTORY GH INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = GH.GROUTINGDAILYDETAILID INNER JOIN TP_MST_USER U ON U.USERID = GH.USERID /*INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = GH.GROUTINGDAILYDETAILID INNER JOIN TP_PC_GROUTINGLINE GL ON GL.GROUTINGLINEID = GDD.GROUTINGLINEID*/ INNER JOIN TP_MST_GOODS G ON G.GOODSID = GH.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID /*INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE = :IN_DATACODE AND HGDI.ITEMTYPE = 3 AND HGDI.ITEMID = GL.GMOULDTYPEID*/ WHERE GH.CREATETIME >= :V_DATEBEGIN AND GH.CREATETIME < :IN_DATEEND AND GH.DATATYPE IN (1, 2) AND G.SCRAPSUMFLAG = '1' GROUP BY GH.SAPCODE, GH.GOODSCODE, U.USERCODE,GDD.GROUTINGLINECODE,GT.GOODSTYPECODE,GDD.TESTMOULDFLAG, G.GOODS_LINE_CODE /*UNION ALL -- 成型产量 高压 SELECT GH.GOODSCODE ,GH.SAPCODE ,U.USERCODE ,SUM(DECODE(GH.DATATYPE, 1, 1, -1)) OUTPUTNUM ,0 SCRAPNUM ,0 CLEANUPNUM ,0 REPAIRNUM FROM TP_PM_GOODSCHANGEHISTORY GH INNER JOIN TP_MST_USER U ON U.USERID = GH.USERID INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = GH.GROUTINGDAILYDETAILID INNER JOIN TP_PC_GROUTINGLINE GL ON GL.GROUTINGLINEID = GDD.GROUTINGLINEID INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE = :IN_DATACODE AND HGDI.ITEMTYPE = 3 AND HGDI.ITEMID <> GL.GMOULDTYPEID WHERE GH.CREATETIME >= :V_DATEBEGIN AND GH.CREATETIME < :IN_DATEEND AND GH.DATATYPE IN (1, 2) GROUP BY GH.SAPCODE ,GH.GOODSCODE ,U.USERCODE*/ UNION ALL -- 成型报损 SELECT GH.GOODSCODE, GH.SAPCODE, U.USERCODE, 0 OUTPUTNUM, SUM(DECODE(GH.DATATYPE, 3, 1, -1)) SCRAPNUM, 0 CLEANUPNUM, 0 REPAIRNUM, CASE WHEN (INSTR(GDD.GROUTINGLINECODE,'A') = 1 OR INSTR(GDD.GROUTINGLINECODE,'D') = 1) AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN 1 WHEN (INSTR(GDD.GROUTINGLINECODE,'B') = 1 OR INSTR(GDD.GROUTINGLINECODE,'D') = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN 2 WHEN INSTR(GDD.GROUTINGLINECODE,'C') = 1 THEN 3 ELSE 0 END AS WORKSHOP, GDD.TESTMOULDFLAG, G.GOODS_LINE_CODE FROM TP_PM_GOODSCHANGEHISTORY GH INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = GH.GROUTINGDAILYDETAILID INNER JOIN TP_MST_USER U ON U.USERID = GH.USERID INNER JOIN TP_MST_GOODS G ON G.GOODSID = GH.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID WHERE GH.CREATETIME >= :V_DATEBEGIN AND GH.CREATETIME < :IN_DATEEND AND GH.DATATYPE IN (3, 4) AND G.SCRAPSUMFLAG = '1' GROUP BY GH.SAPCODE, GH.GOODSCODE, U.USERCODE,GDD.GROUTINGLINECODE,GT.GOODSTYPECODE,GDD.TESTMOULDFLAG, G.GOODS_LINE_CODE UNION ALL -- 盘点清除 SELECT GH.GOODSCODE, GH.SAPCODE, U.USERCODE, 0 OUTPUTNUM, 0 SCRAPNUM, SUM(DECODE(GH.DATATYPE, 11, 1, -1)) CLEANUPNUM, 0 REPAIRNUM, CASE WHEN (INSTR(GDD.GROUTINGLINECODE,'A') = 1 OR INSTR(GDD.GROUTINGLINECODE,'D') = 1) AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN 1 WHEN (INSTR(GDD.GROUTINGLINECODE,'B') = 1 OR INSTR(GDD.GROUTINGLINECODE,'D') = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN 2 WHEN INSTR(GDD.GROUTINGLINECODE,'C') = 1 THEN 3 ELSE 0 END AS WORKSHOP, GDD.TESTMOULDFLAG, G.GOODS_LINE_CODE FROM TP_PM_GOODSCHANGEHISTORY GH INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = GH.GROUTINGDAILYDETAILID INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE = :IN_DATACODE AND HGDI.ITEMTYPE = 2 AND HGDI.ITEMID = GH.OTHERID INNER JOIN TP_MST_USER U ON U.USERID = GH.USERID INNER JOIN TP_MST_GOODS G ON G.GOODSID = GH.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID WHERE GH.CREATETIME >= :V_DATEBEGIN AND GH.CREATETIME < :IN_DATEEND AND GH.DATATYPE IN (11, 12) GROUP BY GH.SAPCODE, GH.GOODSCODE, U.USERCODE,GDD.GROUTINGLINECODE,GT.GOODSTYPECODE,GDD.TESTMOULDFLAG, G.GOODS_LINE_CODE UNION ALL -- 工序报损 SELECT SP.GOODSCODE --,G.GROUTMATERIALCODE , GDD.MATERIALCODE SAPCODE, PD.USERCODE, 0 OUTPUTNUM, COUNT(*) SCRAPNUM, 0 CLEANUPNUM, 0 REPAIRNUM, CASE WHEN (INSTR(GDD.GROUTINGLINECODE,'A') = 1 OR INSTR(GDD.GROUTINGLINECODE,'D') = 1) AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN 1 WHEN (INSTR(GDD.GROUTINGLINECODE,'B') = 1 OR INSTR(GDD.GROUTINGLINECODE,'D') = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN 2 WHEN INSTR(GDD.GROUTINGLINECODE,'C') = 1 THEN 3 ELSE 0 END AS WORKSHOP, GDD.TESTMOULDFLAG, G.GOODS_LINE_CODE FROM TP_PM_SCRAPPRODUCT SP INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE = :IN_DATACODE AND HGDI.ITEMTYPE = 2 AND HGDI.ITEMID = SP.PROCEDUREID --INNER JOIN TP_MST_GOODS G -- ON G.GOODSID = SP.GOODSID INNER JOIN TP_PM_PRODUCTIONDATA PD ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID WHERE SP.AUDITSTATUS = 1 AND SP.AUDITDATE >= :V_DATEBEGIN AND SP.AUDITDATE < :IN_DATEEND AND SP.SCRAPTYPE IN (0, 2, 3) GROUP BY SP.GOODSCODE, GDD.MATERIALCODE, PD.USERCODE,GDD.GROUTINGLINECODE,GT.GOODSTYPECODE,GDD.TESTMOULDFLAG, G.GOODS_LINE_CODE UNION ALL -- 工序报损撤销 SELECT SP.GOODSCODE --,G.GROUTMATERIALCODE , GDD.MATERIALCODE SAPCODE, PD.USERCODE, 0 OUTPUTNUM, 0 - COUNT(*) SCRAPNUM, 0 CLEANUPNUM, 0 REPAIRNUM, CASE WHEN (INSTR(GDD.GROUTINGLINECODE,'A') = 1 OR INSTR(GDD.GROUTINGLINECODE,'D') = 1) AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN 1 WHEN (INSTR(GDD.GROUTINGLINECODE,'B') = 1 OR INSTR(GDD.GROUTINGLINECODE,'D') = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN 2 WHEN INSTR(GDD.GROUTINGLINECODE,'C') = 1 THEN 3 ELSE 0 END AS WORKSHOP, GDD.TESTMOULDFLAG, G.GOODS_LINE_CODE FROM TP_PM_SCRAPPRODUCT SP INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE = :IN_DATACODE AND HGDI.ITEMTYPE = 2 AND HGDI.ITEMID = SP.PROCEDUREID --INNER JOIN TP_MST_GOODS G -- ON G.GOODSID = SP.GOODSID INNER JOIN TP_PM_PRODUCTIONDATA PD ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID WHERE SP.AUDITSTATUS = 1 AND SP.VALUEFLAG = '0' AND SP.BACKOUTTIME >= :V_DATEBEGIN AND SP.BACKOUTTIME < :IN_DATEEND AND SP.SCRAPTYPE IN (0, 2, 3) GROUP BY SP.GOODSCODE, GDD.MATERIALCODE, PD.USERCODE,GDD.GROUTINGLINECODE,GT.GOODSTYPECODE,GDD.TESTMOULDFLAG, G.GOODS_LINE_CODE UNION ALL -- 干补 SELECT SP.GOODSCODE --,G.GROUTMATERIALCODE , GDD.MATERIALCODE SAPCODE, SP.SPECIALREPAIRUSERCODE USERCODE, 0 OUTPUTNUM, 0 SCRAPNUM, 0 CLEANUPNUM, COUNT(*) REPAIRNUM, CASE WHEN (INSTR(GDD.GROUTINGLINECODE,'A') = 1 OR INSTR(GDD.GROUTINGLINECODE,'D') = 1) AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN 1 WHEN (INSTR(GDD.GROUTINGLINECODE,'B') = 1 OR INSTR(GDD.GROUTINGLINECODE,'D') = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN 2 WHEN INSTR(GDD.GROUTINGLINECODE,'C') = 1 THEN 3 ELSE 0 END AS WORKSHOP, GDD.TESTMOULDFLAG, G.GOODS_LINE_CODE FROM TP_PM_SCRAPPRODUCT SP INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE = :IN_DATACODE AND HGDI.ITEMTYPE = 2 AND HGDI.ITEMID = SP.PROCEDUREID --INNER JOIN TP_MST_GOODS G -- ON G.GOODSID = SP.GOODSID INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID WHERE SP.AUDITSTATUS = 1 AND SP.VALUEFLAG = '1' AND SP.GOODSLEVELTYPEID = 9 AND SP.SPECIALREPAIRTIME >= :V_DATEBEGIN AND SP.SPECIALREPAIRTIME < :IN_DATEEND GROUP BY SP.GOODSCODE --,G.GROUTMATERIALCODE , GDD.MATERIALCODE, SP.SPECIALREPAIRUSERCODE,GDD.GROUTINGLINECODE,GT.GOODSTYPECODE,GDD.TESTMOULDFLAG, G.GOODS_LINE_CODE ) SPP GROUP BY SPP.GOODSCODE, SPP.SAPCODE, SPP.USERCODE,SPP.WORKSHOP,SPP.GOODS_LINE_CODE,SPP.TESTMOULDFLAG "; } // 30 精坯、40 釉坯、50 烧成 else if ("30".Equals(datacode) || "40".Equals(datacode) || "50".Equals(datacode)) { sqlString = @"SELECT TO_CHAR(:IN_DATEEND, 'YYYYMMDD') yyyymmdd , TO_CHAR(5000) WERKS,SPP.GOODSCODE GROES, SPP.SAPCODE MATNR,SPP.USERCODE ZGHNU,TO_CHAR(:IN_DATACODE) ZJDNU, to_char(:V_DATEBEGIN, 'YYYYMMDD' ) ZSCNU, to_char(:V_DATEBEGIN, 'HH24MISS' ) ZKSSJ, to_char(:IN_DATEEND, 'YYYYMMDDHH24MISS' ) ZJSRQ, '' VBELN, '' POSNR, TO_CHAR(SUM(SPP.OUTPUTNUM)) ZCLNG, TO_CHAR(SUM(SPP.SCRAPNUM)) ZSPNG, TO_CHAR(SUM(SPP.CLEANUPNUM)) ZQCNG, TO_CHAR(0) ZHSNG, TO_CHAR(SUM(SPP.REPAIRNUM)) ZGBNG, SPP.TESTMOULDFLAG ZSCMS, DECODE( :IN_DATACODE,20,TO_CHAR(SPP.GOODS_LINE_CODE),'T') AS zscs, TO_CHAR(SPP.WORKSHOP) ZSCCJ, TO_CHAR( :yyyymmdd) CHARG, TO_CHAR(:IN_DATACODE) datacode FROM ( -- 产量 ------------------------------------------ SELECT GOODSCODE, SAPCODE, USERCODE, COUNT( * ) OUTPUTNUM, 0 SCRAPNUM, 0 CLEANUPNUM, 0 REPAIRNUM, WORKSHOP, -- GROUTINGSHOP, TESTMOULDFLAG, GOODS_LINE_CODE FROM ( SELECT PD.GOODSCODE, GDD.MATERIALCODE SAPCODE, PD.USERCODE, CASE WHEN HGDI.WORKSHOP = 2 AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN 1 WHEN HGDI.WORKSHOP = 2 AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN 2 WHEN HGDI.WORKSHOP = 3 THEN 3 ELSE 0 END AS WORKSHOP, -- CASE -- -- WHEN INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN -- 1 -- WHEN INSTR( GT.GOODSTYPECODE, '001001' ) = 1 -- AND ( INSTR( GDD.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( GDD.GROUTINGLINECODE, 'D' ) = 1 ) THEN -- 2 -- WHEN INSTR( GDD.GROUTINGLINECODE, 'C' ) = 1 -- AND INSTR( GDD.GROUTINGLINECODE, 'A' ) = 4 -- OR INSTR( GDD.GROUTINGLINECODE, 'C06B' ) = 1 THEN -- 3 ELSE 4 -- END AS GROUTINGSHOP, DECODE( GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-' ) AS TESTMOULDFLAG, G.GOODS_LINE_CODE FROM TP_PM_PRODUCTIONDATA PD INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE = :IN_DATACODE AND HGDI.ITEMTYPE = 1 AND HGDI.ITEMID = PD.PROCEDUREID -- 3#成检交接（本烧重烧共用，且可以改判） AND ( HGDI.ITEMID <> 104 OR ( PD.ISREFIRE = '0' AND PD.CHECKFLAG = '1' ) ) INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = PD.GROUTINGDAILYDETAILID INNER JOIN TP_MST_GOODS G ON G.GOODSID = PD.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID WHERE PD.CREATETIME >= :V_DATEBEGIN AND PD.CREATETIME < :IN_DATEEND ) GROUP BY GOODSCODE, SAPCODE, USERCODE, WORKSHOP, -- GROUTINGSHOP, TESTMOULDFLAG, GOODS_LINE_CODE UNION ALL -- 产量 撤销 SELECT GOODSCODE, SAPCODE, USERCODE, 0 - COUNT( * ) OUTPUTNUM, 0 SCRAPNUM, 0 CLEANUPNUM, 0 REPAIRNUM, WORKSHOP, TESTMOULDFLAG, GOODS_LINE_CODE FROM ( SELECT PD.GOODSCODE, GDD.MATERIALCODE SAPCODE, PD.USERCODE, CASE WHEN HGDI.WORKSHOP = 2 AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN 1 WHEN HGDI.WORKSHOP = 2 AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN 2 WHEN HGDI.WORKSHOP = 3 THEN 3 ELSE 4 END AS WORKSHOP, DECODE( GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-' ) AS TESTMOULDFLAG, G.GOODS_LINE_CODE FROM TP_PM_PRODUCTIONDATA PD INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE = :IN_DATACODE AND HGDI.ITEMTYPE = 1 AND HGDI.ITEMID = PD.PROCEDUREID -- 3#成检交接（本烧重烧共用，且可以改判） AND ( HGDI.ITEMID <> 104 OR ( PD.ISREFIRE = '0' AND PD.CHECKFLAG = '1' ) ) INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = PD.GROUTINGDAILYDETAILID INNER JOIN TP_MST_GOODS G ON G.GOODSID = PD.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID WHERE PD.VALUEFLAG = '0' AND PD.BACKOUTTIME >= :V_DATEBEGIN AND PD.BACKOUTTIME < :IN_DATEEND ) GROUP BY GOODSCODE, SAPCODE, USERCODE, WORKSHOP, TESTMOULDFLAG, GOODS_LINE_CODE UNION ALL -- 盘点清除 SELECT GOODSCODE, SAPCODE, USERCODE, 0 OUTPUTNUM, 0 SCRAPNUM, SUM(DECODE(DATATYPE, 11, 1, -1)) CLEANUPNUM, 0 REPAIRNUM, WORKSHOP, TESTMOULDFLAG, GOODS_LINE_CODE FROM ( SELECT GH.GOODSCODE, GH.SAPCODE, U.USERCODE, CASE WHEN HGDI.WORKSHOP = 2 AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN 1 WHEN HGDI.WORKSHOP = 2 AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN 2 WHEN HGDI.WORKSHOP = 3 THEN 3 ELSE 4 END AS WORKSHOP, GH.DATATYPE, DECODE( GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-' ) AS TESTMOULDFLAG, G.GOODS_LINE_CODE FROM TP_PM_GOODSCHANGEHISTORY GH INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = GH.GROUTINGDAILYDETAILID INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE = :IN_DATACODE AND HGDI.ITEMTYPE = 2 AND HGDI.ITEMID = GH.OTHERID INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID INNER JOIN TP_MST_USER U ON U.USERID = GH.USERID WHERE GH.CREATETIME >= :V_DATEBEGIN AND GH.CREATETIME < :IN_DATEEND AND GH.DATATYPE IN (11, 12) ) GROUP BY GOODSCODE, SAPCODE, USERCODE, WORKSHOP, TESTMOULDFLAG, GOODS_LINE_CODE UNION ALL -- 工序报损 SELECT GOODSCODE, SAPCODE, USERCODE, 0 OUTPUTNUM, COUNT(*) SCRAPNUM, 0 CLEANUPNUM, 0 REPAIRNUM, WORKSHOP, TESTMOULDFLAG, GOODS_LINE_CODE FROM ( SELECT SP.GOODSCODE --,G.GROUTMATERIALCODE SAPCODE , GDD.MATERIALCODE SAPCODE, PD.USERCODE, CASE WHEN HGDI.WORKSHOP = 2 AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN 1 WHEN HGDI.WORKSHOP = 2 AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN 2 WHEN HGDI.WORKSHOP = 3 THEN 3 ELSE 4 END AS WORKSHOP, DECODE( GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-' ) AS TESTMOULDFLAG, G.GOODS_LINE_CODE FROM TP_PM_SCRAPPRODUCT SP INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE = :IN_DATACODE AND HGDI.ITEMTYPE = 2 AND HGDI.ITEMID = SP.PROCEDUREID INNER JOIN TP_PM_PRODUCTIONDATA PD ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID INNER JOIN TP_MST_GOODS G ON G.GOODSID = PD.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID WHERE SP.AUDITSTATUS = 1 AND SP.AUDITDATE >= :V_DATEBEGIN AND SP.AUDITDATE < :IN_DATEEND --AND SP.SCRAPTYPE IN (0, 2, 3) ) GROUP BY GOODSCODE, SAPCODE, USERCODE, WORKSHOP, TESTMOULDFLAG, GOODS_LINE_CODE UNION ALL -- 工序报损撤销 SELECT GOODSCODE, SAPCODE, USERCODE, 0 OUTPUTNUM, 0 - COUNT(*) SCRAPNUM, 0 CLEANUPNUM, 0 REPAIRNUM, WORKSHOP, TESTMOULDFLAG, GOODS_LINE_CODE FROM ( SELECT SP.GOODSCODE --,G.GROUTMATERIALCODE SAPCODE , GDD.MATERIALCODE SAPCODE, PD.USERCODE, CASE WHEN HGDI.WORKSHOP = 2 AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN 1 WHEN HGDI.WORKSHOP = 2 AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN 2 WHEN HGDI.WORKSHOP = 3 THEN 3 ELSE 4 END AS WORKSHOP, DECODE( GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-' ) AS TESTMOULDFLAG, G.GOODS_LINE_CODE FROM TP_PM_SCRAPPRODUCT SP INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE = :IN_DATACODE AND HGDI.ITEMTYPE = 2 AND HGDI.ITEMID = SP.PROCEDUREID INNER JOIN TP_PM_PRODUCTIONDATA PD ON PD.PRODUCTIONDATAID = SP.PRODUCTIONDATAID INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID INNER JOIN TP_MST_GOODS G ON G.GOODSID = PD.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID WHERE SP.AUDITSTATUS = 1 AND SP.BACKOUTTIME >= :V_DATEBEGIN AND SP.BACKOUTTIME < :IN_DATEEND AND SP.VALUEFLAG = '0' --AND SP.SCRAPTYPE IN (0, 2, 3) ) GROUP BY GOODSCODE, SAPCODE, USERCODE, WORKSHOP, TESTMOULDFLAG, GOODS_LINE_CODE UNION ALL SELECT GOODSCODE, SAPCODE, USERCODE, 0 OUTPUTNUM, 0 SCRAPNUM, 0 CLEANUPNUM, COUNT(*) REPAIRNUM, WORKSHOP, TESTMOULDFLAG, GOODS_LINE_CODE FROM ( SELECT SP.GOODSCODE, GDD.MATERIALCODE SAPCODE, SP.SPECIALREPAIRUSERCODE USERCODE, CASE WHEN HGDI.WORKSHOP = 2 AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN 1 WHEN HGDI.WORKSHOP = 2 AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN 2 WHEN HGDI.WORKSHOP = 3 THEN 3 ELSE 4 END AS WORKSHOP, DECODE( GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-' ) AS TESTMOULDFLAG, G.GOODS_LINE_CODE FROM TP_PM_SCRAPPRODUCT SP INNER JOIN TSAP_HEGII_DATAITEM HGDI ON HGDI.DATACODE = :IN_DATACODE AND HGDI.ITEMTYPE = 2 AND HGDI.ITEMID = SP.PROCEDUREID INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID WHERE SP.AUDITSTATUS = 1 AND SP.VALUEFLAG = '1' AND SP.GOODSLEVELTYPEID = 9 AND SP.SPECIALREPAIRTIME >= :V_DATEBEGIN AND SP.SPECIALREPAIRTIME < :IN_DATEEND ) GROUP BY GOODSCODE, SAPCODE, USERCODE, WORKSHOP, TESTMOULDFLAG, GOODS_LINE_CODE ) SPP WHERE SPP.OUTPUTNUM > '0' AND SPP.SCRAPNUM > '0' AND SPP.CLEANUPNUM > '0' AND SPP.REPAIRNUM > '0' GROUP BY SPP.GOODSCODE, SPP.SAPCODE, SPP.USERCODE,SPP.WORKSHOP,SPP.GOODS_LINE_CODE,SPP.TESTMOULDFLAG"; } else if ("60".Equals(datacode)) { sqlString = @"SELECT :yyyymmdd AS yyyymmdd ,TO_CHAR(5000) WERKS , tt.goodscode GROES , tt.sapcode MATNR , tt.usercode ZGHNU , tt.ordercode VBELN , tt.orderitem POSNR , decode(tt.testmouldflag,0,'C',1,'Y','') ZSCMS -- ,tt.zscs ,'T' AS zscs , to_char(tt.outputnum) AS ZCLNG , to_char(tt.recoverynum) AS ZHSNG , '60' ZJDNU ,to_char(SYSDATE, 'YYYYMMDD') ZSCNU ,to_char(SYSDATE, 'HH24MISS') ZKSSJ ,to_char(SYSDATE, 'YYYYMMDDHH24MISS') ZJSRQ ,'0' ZSPNG ,'0' ZQCNG ,'0' ZGBNG ,tt.WORKSHOP ZSCCJ ,TO_CHAR( :yyyymmdd) CHARG ,'60' datacode FROM(SELECT t.goodscode , t.sapcode , t.usercode , t.ordercode , t.orderitem , t.testmouldflag , t.zscs , SUM(decode(t.recyclingflag, '1', 0, 1)) outputnum , SUM(decode(t.recyclingflag, '0', 0, 1)) recoverynum , t.WORKSHOP FROM(SELECT bar.goodscode , bar.materialcode sapcode , gh.fhusercode usercode , o.orderno , bar.recyclingflag , bar.testmouldflag , decode(bar.testmouldflag, '0', '', decode(g.goods_line_code, 'G', 'A', 'M', 'A', 'L', 'B', '')) as zscs , CASE WHEN o.orderid IS NULL OR o.orderno LIKE 'HEGII%' THEN ' ' WHEN instr(o.orderno, '/') = 0 THEN to_char(o.orderno) ELSE to_char(substr(o.orderno, 1, instr(o.orderno, '/') - 1)) END ordercode-- 销售凭证 , CASE WHEN o.orderid IS NULL OR o.orderno LIKE 'HEGII%' OR instr(o.orderno, '/') = 0 THEN '0' WHEN instr(o.orderno, '#') = 0 THEN to_char(substr(o.orderno, instr(o.orderno, '/') + 1)) ELSE to_char(substr(o.orderno , instr(o.orderno, '/') + 1 , instr(o.orderno, '#') - instr(o.orderno, '/') - 1)) END orderitem-- 销售凭证项目 ,CASE WHEN INSTR(GT.GOODSTYPECODE, '001001') = 1 AND (INSTR(bar.GROUTINGLINECODE, 'B') = 1 OR INSTR(bar.GROUTINGLINECODE, 'D') = 1) THEN 2 WHEN INSTR(GT.GOODSTYPECODE, '001002') = 1 AND (INSTR(bar.GROUTINGLINECODE, 'A') = 1 OR INSTR(bar.GROUTINGLINECODE, 'D') = 1) THEN 1 WHEN INSTR(bar.GROUTINGLINECODE, 'C') = 1 THEN 3 ELSE 0 END WORKSHOP FROM tp_pm_groutingdailydetail bar INNER JOIN tp_pm_finishedproduct gh ON bar.groutingdailydetailid = gh.groutingdailydetailid INNER JOIN tp_mst_goods g ON g.goodsid = bar.goodsid INNER JOIN TP_MST_GOODSTYPE GT ON G.GOODSTYPEID = GT.GOODSTYPEID LEFT JOIN tp_pm_order o ON o.orderid = gh.fhorderid where 1 = 1 AND gh.fhtime >= :V_DATEBEGIN and gh.fhtime < :IN_DATEEND ) t GROUP BY t.goodscode , t.sapcode , t.usercode , t.ordercode , t.orderitem, t.testmouldflag, t.zscs,t.WORKSHOP) tt WHERE tt.outputnum <> 0 OR tt.recoverynum <> 0 ORDER BY tt.goodscode ,tt.sapcode ,tt.usercode ,tt.ordercode ,tt.orderitem,tt.zscs,tt.WORKSHOP"; } paras = new OracleParameter[] { new OracleParameter(":IN_DATACODE",datacode), new OracleParameter(":yyyymmdd",yyyymmdd), new OracleParameter(":V_DATEBEGIN",OracleDbType.Date, date, ParameterDirection.Input), new OracleParameter(":IN_DATEEND",OracleDbType.Date, ndate, ParameterDirection.Input), }; DataTable workData = oracleConn.GetSqlResultToDt(sqlString, paras); int num = workData.Rows.Count; // 调用SAP接口 string postString = "{\"ZSUM\":" + num.ToString() + ",\"TABLE_IN\":{\"item\":" + JsonHelper.ToJson(ModelConvertHelper.ConvertToModel(workData)) + "}}"; //INIUtility ini = INIUtility.Instance(INIUtility.IniFile.SAP_HEGII); //string url030 = ini.ReadIniData("SAP_NEW_INFO", "Url030"); //正式 //string url030 = "http://hgs4powd1.hegii.com:8000/RESTAdapter/DKMES/ZPPFM030"; //测试 string url030 = "http://hgs4podev.hegii.com:50200/RESTAdapter/DKMES/ZPPFM030"; string result = PostData(url030, postString, "POST"); oracleConn.Commit(); sre.Message = JObject.Parse(result)["ZMSG"].ToString(); sre.Result = JObject.Parse(result)["ZTYPE"].ToString(); return sre; } catch (Exception ex) { OutputLog.TraceLog(LogPriority.Error, "BGToSAP", "报工" + date.ToString("yyyy-MM-dd HH:mm:ss"), ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); throw ex; } finally { if (oracleConn != null && oracleConn.ConnState == ConnectionState.Open) { oracleConn.Disconnect(); } } } public static ServiceResultEntity SyncSap5000_test_cx(DateTime date, string datacode, DateTime ndate) { ServiceResultEntity sre = new ServiceResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); //string fifter = "AND (bar.barcode = '10015967073')"; string fifter = "AND (gh.createtime >= :begindate or gh.createtime < :enddate)"; string yyyymmdd = date.ToString("yyyyMMdd"); string workcode = "5000"; string sqlString = @"SELECT :yyyymmdd AS yyyymmdd ,SYSDATE AS createtime ,tt.goodscode GROES , tt.sapcode MATNR , tt.usercode ZGHNU , tt.ordercode VBELN , tt.orderitem POSNR , decode(tt.testmouldflag, 0, 'C', 1, 'Y', '') ZSCMS -- ,tt.zscs ,'T' AS zscs , to_char(tt.outputnum) AS ZCLNG , to_char(tt.recoverynum) AS ZHSNG , TO_CHAR(5000) WERKS , '60' ZJDNU ,to_char(SYSDATE, 'YYYYMMDD') ZSCNU ,to_char(SYSDATE, 'HH24MISS') ZKSSJ ,to_char(SYSDATE, 'YYYYMMDDHH24MISS') ZJSRQ ,'0' ZSPNG ,'0' ZQCNG ,'0' ZGBNG ,to_char(tt.WORKSHOP) ZSCCJ ,TO_CHAR( :yyyymmdd) CHARG ,'0' datacode FROM(SELECT t.goodscode , t.sapcode , t.usercode , t.ordercode , t.orderitem , t.testmouldflag , t.zscs , t.WORKSHOP , 0 - SUM(decode(t.recyclingflag, '1', 0, 1)) outputnum , 0 - SUM(decode(t.recyclingflag, '0', 0, 1)) recoverynum FROM(SELECT bar.goodscode , bar.materialcode sapcode , gh.fhusercode usercode , o.orderno , bar.recyclingflag , bar.testmouldflag , decode(bar.testmouldflag, '0', '', decode(g.goods_line_code, 'G', 'A', 'M', 'A', 'L', 'B', '')) as zscs , CASE WHEN o.orderid IS NULL OR o.orderno LIKE 'HEGII%' THEN ' ' WHEN instr(o.orderno, '/') = 0 THEN to_char(o.orderno) ELSE to_char(substr(o.orderno, 1, instr(o.orderno, '/') - 1)) END ordercode-- 销售凭证 , CASE WHEN o.orderid IS NULL OR o.orderno LIKE 'HEGII%' OR instr(o.orderno, '/') = 0 THEN '0' WHEN instr(o.orderno, '#') = 0 THEN to_char(substr(o.orderno, instr(o.orderno, '/') + 1)) ELSE to_char(substr(o.orderno , instr(o.orderno, '/') + 1 , instr(o.orderno, '#') - instr(o.orderno, '/') - 1)) END orderitem , CASE WHEN (INSTR(bar.GROUTINGLINECODE,'A') = 1 OR INSTR(bar.GROUTINGLINECODE,'D') = 1) AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN 1 WHEN (INSTR(bar.GROUTINGLINECODE,'B') = 1 OR INSTR(bar.GROUTINGLINECODE,'D') = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN 2 WHEN INSTR(bar.GROUTINGLINECODE,'C') = 1 THEN 3 ELSE 0 END AS WORKSHOP FROM tp_pm_groutingdailydetail bar INNER JOIN tp_pm_finishedproduct gh ON bar.groutingdailydetailid = gh.groutingdailydetailid INNER JOIN tp_mst_goods g ON g.goodsid = bar.goodsid INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID LEFT JOIN tp_pm_order o ON o.orderid = gh.fhorderid where 1 = 1 " + fifter + @") t GROUP BY t.goodscode , t.sapcode , t.usercode , t.ordercode , t.orderitem, t.testmouldflag, t.zscs,t.WORKSHOP) tt WHERE tt.outputnum <> 0 OR tt.recoverynum <> 0 ORDER BY tt.goodscode ,tt.sapcode ,tt.usercode ,tt.ordercode ,tt.orderitem,tt.zscs,tt.WORKSHOP"; OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter(":yyyymmdd", yyyymmdd), new OracleParameter(":begindate", date), new OracleParameter(":enddate", ndate) }; DataTable workData5000 = oracleTrConn.GetSqlResultToDt(sqlString, oracleParameter); string logid_bg = oracleTrConn.GetSqlResultToStr("select SEQ_TSAP_DATALOG_BG.Nextval from dual"); sqlString = "insert into tsap_hegii_datalog_bg\n" + " (LogID\n" + " ,LogType\n" + " ,BeginTime\n" + " ,YYYYMMDD\n" + " ,WorkCode\n" + " ,DataCode\n" + " ,DataStuts\n" + " ,DataMSG\n" + " ,DATALOGID\n" + " ,EXECUTEDATEBEGIN\n" + " ,EXECUTEDATEEND)\n" + "values\n" + " (:LogID\n" + " ,'2'\n" + " ,sysdate\n" + " ,:YYYYMMDD\n" + " ,'5000'\n" + " ,'60'\n" + " ,'S'\n" + " ,:DataMSG\n" + " ,:LogID\n" + " ,sysdate\n" + " ,sysdate)"; oracleParameter = new OracleParameter[] { new OracleParameter(":LogID",OracleDbType.Int32, logid_bg, ParameterDirection.Input), new OracleParameter(":YYYYMMDD",OracleDbType.Varchar2, yyyymmdd, ParameterDirection.Input), new OracleParameter(":DataMSG",OracleDbType.Varchar2, "", ParameterDirection.Input), }; int r = oracleTrConn.ExecuteNonQuery(sqlString, oracleParameter); sqlString = "insert into TSAP_HEGII_WORKDATA_BG\n" + " (YYYYMMDD\n" + " ,WorkCode\n" + " ,DataCode\n" + " ,GoodsCode\n" + " ,SAPCode\n" + " ,UserCode\n" + " ,WORKSHOP\n" + " ,OutputNum\n" + " ,RECOVERYNUM\n" + " ,ORDERCODE\n" + " ,ORDERITEM\n" + " ,testmouldflag\n" + " ,zscs\n" + " ,LogID,createtime)\n" + "values\n" + " ('" + yyyymmdd + "'\n" + " ,'5000'\n" + " ,'60'\n" + " ,:GoodsCode\n" + " ,:SAPCode\n" + " ,:UserCode\n" + " ,'0'\n" + " ,:OutputNum\n" + " ,:RECOVERYNUM\n" + " ,:ORDERCODE\n" + " ,:ORDERITEM\n" + " ,:testmouldflag\n" + " ,:zscs\n" + " ," + logid_bg + ",:createtime)"; DateTime now = DateTime.Now; foreach (DataRow item in workData5000.Rows) { oracleParameter = new OracleParameter[] { new OracleParameter(":GoodsCode", item["GROES"].ToString()), new OracleParameter(":SAPCode", item["MATNR"].ToString()), new OracleParameter(":UserCode", item["ZGHNU"].ToString()), new OracleParameter(":OutputNum", item["ZCLNG"].ToString()), new OracleParameter(":RECOVERYNUM", item["ZHSNG"].ToString()), new OracleParameter(":ORDERCODE", item["VBELN"].ToString()), new OracleParameter(":ORDERITEM", item["POSNR"].ToString()), new OracleParameter(":testmouldflag", item["ZSCMS"].ToString()), new OracleParameter(":zscs", item["ZSCS"].ToString()), new OracleParameter(":createtime", OracleDbType.Date,item["createtime"], ParameterDirection.Input), }; r = oracleTrConn.ExecuteNonQuery(sqlString, oracleParameter); sqlString = "SELECT TO_CHAR(wd.yyyymmdd) yyyymmdd\n" + " ,TO_CHAR(5000) WERKS\n" + " ,TO_CHAR(wd.goodscode) GROES\n" + " ,TO_CHAR(wd.sapcode) MATNR\n" + " ,TO_CHAR(wd.usercode) ZGHNU\n" + " ,TO_CHAR(wd.datacode) ZJDNU\n" + " ,to_char(sysdate, 'YYYYMMDD' ) ZSCNU\n" + " ,to_char(sysdate, 'HH24MISS' ) ZKSSJ\n" + " ,to_char(sysdate, 'YYYYMMDDHH24MISS' ) ZJSRQ\n" + " ,TO_CHAR(wd.ordercode) VBELN\n" + " ,TO_CHAR(wd.orderitem) POSNR\n" + " ,TO_CHAR(wd.outputnum) ZCLNG\n" + " ,TO_CHAR(wd.scrapnum) ZSPNG\n" + " ,TO_CHAR(wd.cleanupnum) ZQCNG\n" + " ,TO_CHAR(wd.recoverynum) ZHSNG\n" + " ,TO_CHAR(wd.repairnum) ZGBNG\n" + " ,TO_CHAR(wd.testmouldflag) ZSCMS\n" + " -- ,TO_CHAR(wd.zscs) zscs\n" + " ,'T' AS zscs\n" + " ,TO_CHAR(wd.WORKSHOP) ZSCCJ\n" + " ,TO_CHAR( :yyyymmdd) CHARG\n" + " ,TO_CHAR(60) datacode\n" + " FROM tsap_hegii_workdata_bg wd\n" + " INNER JOIN TSAP_HEGII_DATALOG_BG DL\n" + " ON wd.LOGID = DL.LOGID \n" + " WHERE wd.logid = :logid"; OracleParameter[] par = null; par = new OracleParameter[] { new OracleParameter(":logid", OracleDbType.Int32, logid_bg, ParameterDirection.Input), new OracleParameter(":yyyymmdd",yyyymmdd), }; DataTable dtt = oracleTrConn.GetSqlResultToDt(sqlString, par); int num = workData5000.Rows.Count; string sq = "select SAP_INI_BG from TP_SYS_SAPCONFIG"; string SAP_ING_NEW = oracleTrConn.GetSqlResultToStr(sq); if (SAP_ING_NEW == "1") { // 调用SAP接口 string postString = "{\"ZSUM\":" + num.ToString() + ",\"TABLE_IN\":{\"item\":" + JsonHelper.ToJson(ModelConvertHelper.ConvertToModel(workData5000)) + "}}"; //INIUtility ini = INIUtility.Instance(INIUtility.IniFile.SAP_HEGII); //string url030 = ini.ReadIniData("SAP_NEW_INFO", "Url030"); string url030 = "http://hgs4podev.hegii.com:50200/RESTAdapter/DKMES/ZPPFM030"; string result = PostData(url030, postString, "POST"); if (JObject.Parse(result)["TABLE_OUT"] != null && JObject.Parse(result)["TABLE_OUT"].ToString().Length > 0) { sqlString = "update TSAP_HEGII_WorkData_bg t set t.ZTime =sysdate, ZTYPE = :ZTYPE, ZMSG = :ZMSG where logid = " + logid_bg + " \n and YYYYMMDD='" + yyyymmdd + "' and WorkCode='" + workcode + "' and DataCode='60' and GoodsCode=:GoodsCode and SAPCode=:SAPCode and UserCode=:UserCode \n" + "and OrderCode=:OrderCode and OrderItem=:OrderItem"; Dictionary obj = JsonConvert.DeserializeObject>(result); object TABLE_OUT; obj.TryGetValue("TABLE_OUT", out TABLE_OUT); obj = JsonConvert.DeserializeObject>(TABLE_OUT + ""); object item1; obj.TryGetValue("item", out item1); JArray arr = JArray.FromObject(item1); foreach (JObject item60 in arr) { string posnr = item60["POSNR"].ToString().TrimStart('0'); oracleParameter = new OracleParameter[] { new OracleParameter(":ZTYPE",OracleDbType.Varchar2, item60["ZTYPE"].ToString(), ParameterDirection.Input), new OracleParameter(":ZMSG",OracleDbType.Varchar2, item60["ZMSG"].ToString(), ParameterDirection.Input), new OracleParameter(":GoodsCode",OracleDbType.Varchar2, item60["GROES"].ToString(), ParameterDirection.Input), new OracleParameter(":SAPCode",OracleDbType.Varchar2, item60["MATNR"].ToString(), ParameterDirection.Input), new OracleParameter(":UserCode",OracleDbType.Varchar2, item60["ZGHNU"].ToString(), ParameterDirection.Input), new OracleParameter(":OrderCode",OracleDbType.Varchar2, (string.IsNullOrEmpty(item60["VBELN"].ToString()) ? " " : item60["VBELN"].ToString()), ParameterDirection.Input), new OracleParameter(":Orderitem",OracleDbType.Varchar2, ((string.IsNullOrEmpty(posnr)) ? "0" : posnr), ParameterDirection.Input), }; r = oracleTrConn.ExecuteNonQuery(sqlString, oracleParameter); } //:msg||chr(13)||DataMSG JObject.Parse(result)["ZMSG"] = $"{JObject.Parse(result)["ZMSG"]}({num})"; sqlString = "update tsap_hegii_datalog_bg t set t.EndTime = sysdate, DataStuts = :DataStuts, DataMSG =nvl2(:msg,:msg||' '||chr(13)||DataMSG,DataMSG) where logid = :logid"; oracleParameter = new OracleParameter[] { new OracleParameter(":DataStuts",OracleDbType.Varchar2, JObject.Parse(result)["ZTYPE"].ToString(), ParameterDirection.Input), new OracleParameter(":msg",OracleDbType.Varchar2, JObject.Parse(result)["ZMSG"].ToString(), ParameterDirection.Input), new OracleParameter(":logid",OracleDbType.Int32, logid_bg, ParameterDirection.Input), }; r = oracleTrConn.ExecuteNonQuery(sqlString, oracleParameter); } if (JObject.Parse(result)["ZTYPE"].ToString() != "S") { sre.Result = -2; sre.Message = "同步产量失败，" + JObject.Parse(result)["ZMSG"].ToString(); return sre; } //sqlString = //"UPDATE tp_pm_groutingdailydetail bar\n" + //" SET bar.issync = '1'\n" + //" WHERE 1 = 1" + fifter; //r = oracleTrConn.ExecuteNonQuery(sqlString); } } oracleTrConn.Close(); return sre; } public static ServiceResultEntity SyncSap5000_test_jj(DateTime date, string datacode, DateTime ndate) { ServiceResultEntity sre = new ServiceResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); //string fifter = "AND (bar.barcode = '10015967073')"; string fifter = "AND (gh.FHTime is not null and gh.FHTime >= :begindate and gh.FHTime < :enddate)"; string yyyymmdd = date.ToString("yyyyMMdd"); #region string workcode = "5000"; string sqlString = @" SELECT :yyyymmdd AS yyyymmdd ,SYSDATE AS createtime ,tt.goodscode GROES , tt.sapcode MATNR , tt.usercode ZGHNU , tt.ordercode VBELN , tt.orderitem POSNR , decode(tt.testmouldflag,0,'C',1,'Y','') ZSCMS -- ,tt.zscs ,'T' AS zscs , to_char(tt.outputnum) AS ZCLNG , to_char(tt.recoverynum) AS ZHSNG , TO_CHAR(5000) WERKS , '60' ZJDNU ,to_char(SYSDATE, 'YYYYMMDD') ZSCNU ,to_char(SYSDATE, 'HH24MISS') ZKSSJ ,to_char(SYSDATE, 'YYYYMMDDHH24MISS') ZJSRQ ,'0' ZSPNG ,'0' ZQCNG ,'0' ZGBNG ,to_char(tt.WORKSHOP) ZSCCJ ,TO_CHAR( :yyyymmdd) CHARG ,'60' datacode FROM(SELECT t.goodscode , t.sapcode , t.usercode , t.ordercode , t.orderitem , t.testmouldflag , t.zscs , t.WORKSHOP , SUM(decode(t.recyclingflag, '1', 0, 1)) outputnum , SUM(decode(t.recyclingflag, '0', 0, 1)) recoverynum FROM(SELECT bar.goodscode , bar.materialcode sapcode , gh.fhusercode usercode , o.orderno , bar.recyclingflag , bar.testmouldflag , decode(bar.testmouldflag, '0', '', decode(g.goods_line_code, 'G', 'A', 'M', 'A', 'L', 'B', '')) as zscs , CASE WHEN o.orderid IS NULL OR o.orderno LIKE 'HEGII%' THEN ' ' WHEN instr(o.orderno, '/') = 0 THEN to_char(o.orderno) ELSE to_char(substr(o.orderno, 1, instr(o.orderno, '/') - 1)) END ordercode-- 销售凭证 , CASE WHEN o.orderid IS NULL OR o.orderno LIKE 'HEGII%' OR instr(o.orderno, '/') = 0 THEN '0' WHEN instr(o.orderno, '#') = 0 THEN to_char(substr(o.orderno, instr(o.orderno, '/') + 1)) ELSE to_char(substr(o.orderno , instr(o.orderno, '/') + 1 , instr(o.orderno, '#') - instr(o.orderno, '/') - 1)) END orderitem, CASE WHEN (INSTR(bar.GROUTINGLINECODE,'A') = 1 OR INSTR(bar.GROUTINGLINECODE,'D') = 1) AND INSTR( GT.GOODSTYPECODE, '001002' ) = 1 THEN 1 WHEN (INSTR(bar.GROUTINGLINECODE,'B') = 1 OR INSTR(bar.GROUTINGLINECODE,'D') = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1 THEN 2 WHEN INSTR(bar.GROUTINGLINECODE,'C') = 1 THEN 3 ELSE 0 END AS WORKSHOP FROM tp_pm_groutingdailydetail bar INNER JOIN tp_pm_finishedproduct gh ON bar.groutingdailydetailid = gh.groutingdailydetailid INNER JOIN tp_mst_goods g ON g.goodsid = bar.goodsid INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID LEFT JOIN tp_pm_order o ON o.orderid = gh.fhorderid where 1 = 1 " + fifter + @" and (o.orderid IS NULL OR o.orderno LIKE 'HEGII%')) t GROUP BY t.goodscode , t.sapcode , t.usercode , t.ordercode , t.orderitem, t.testmouldflag, t.zscs, t.WORKSHOP) tt WHERE tt.outputnum <> 0 OR tt.recoverynum <> 0 ORDER BY tt.goodscode ,tt.sapcode ,tt.usercode ,tt.ordercode ,tt.orderitem,tt.zscs, tt.WORKSHOP"; OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter(":yyyymmdd", yyyymmdd), new OracleParameter(":begindate", date), new OracleParameter(":enddate", ndate) }; DataTable workData5000 = oracleTrConn.GetSqlResultToDt(sqlString, oracleParameter); string logid_bg = oracleTrConn.GetSqlResultToStr("select SEQ_TSAP_DATALOG_BG.Nextval from dual"); sqlString = "insert into TSAP_HEGII_DATALOG_BG\n" + " (LogID\n" + " ,LogType\n" + " ,BeginTime\n" + " ,YYYYMMDD\n" + " ,WorkCode\n" + " ,DataCode\n" + " ,DataStuts\n" + " ,DataMSG\n" + " ,DATALOGID\n" + " ,EXECUTEDATEBEGIN\n" + " ,EXECUTEDATEEND)\n" + "values\n" + " (:LogID\n" + " ,'2'\n" + " ,sysdate\n" + " ,:YYYYMMDD\n" + " ,'5000'\n" + " ,'60'\n" + " ,'S'\n" + " ,:DataMSG\n" + " ,:LogID\n" + " ,sysdate\n" + " ,sysdate)"; oracleParameter = new OracleParameter[] { new OracleParameter(":LogID",OracleDbType.Int32, logid_bg, ParameterDirection.Input), new OracleParameter(":YYYYMMDD",OracleDbType.Varchar2, yyyymmdd, ParameterDirection.Input), new OracleParameter(":DataMSG",OracleDbType.Varchar2, "", ParameterDirection.Input), }; int r = oracleTrConn.ExecuteNonQuery(sqlString, oracleParameter); sqlString = "insert into TSAP_HEGII_WORKDATA_BG\n" + " (YYYYMMDD\n" + " ,WorkCode\n" + " ,DataCode\n" + " ,GoodsCode\n" + " ,SAPCode\n" + " ,UserCode\n" + " ,WORKSHOP\n" + " ,OutputNum\n" + " ,RECOVERYNUM\n" + " ,ORDERCODE\n" + " ,ORDERITEM\n" + " ,testmouldflag\n" + " ,zscs\n" + " ,LogID,createtime)\n" + "values\n" + " ('" + yyyymmdd + "'\n" + " ,'5000'\n" + " ,'60'\n" + " ,:GoodsCode\n" + " ,:SAPCode\n" + " ,:UserCode\n" + " ,'0'\n" + " ,:OutputNum\n" + " ,:RECOVERYNUM\n" + " ,:ORDERCODE\n" + " ,:ORDERITEM\n" + " ,:testmouldflag\n" + " ,:zscs\n" + " ," + logid_bg + ",:createtime)"; DateTime now = DateTime.Now; foreach (DataRow item in workData5000.Rows) { oracleParameter = new OracleParameter[] { new OracleParameter(":GoodsCode", item["GROES"].ToString()), new OracleParameter(":SAPCode", item["MATNR"].ToString()), new OracleParameter(":UserCode", item["ZGHNU"].ToString()), new OracleParameter(":OutputNum", item["ZCLNG"].ToString()), new OracleParameter(":RECOVERYNUM", item["ZHSNG"].ToString()), new OracleParameter(":ORDERCODE", item["VBELN"].ToString()), new OracleParameter(":ORDERITEM", item["POSNR"].ToString()), new OracleParameter(":testmouldflag", item["ZSCMS"].ToString()), new OracleParameter(":zscs", item["ZSCS"].ToString()), new OracleParameter(":createtime", OracleDbType.Date,item["createtime"], ParameterDirection.Input), }; r = oracleTrConn.ExecuteNonQuery(sqlString, oracleParameter); sqlString = "SELECT TO_CHAR(wd.yyyymmdd) yyyymmdd\n" + " ,TO_CHAR(5000) WERKS\n" + " ,TO_CHAR(wd.goodscode) GROES\n" + " ,TO_CHAR(wd.sapcode) MATNR\n" + " ,TO_CHAR(wd.usercode) ZGHNU\n" + " ,TO_CHAR(wd.datacode) ZJDNU\n" + " ,to_char(sysdate, 'YYYYMMDD' ) ZSCNU\n" + " ,to_char(sysdate, 'HH24MISS' ) ZKSSJ\n" + " ,to_char(sysdate, 'YYYYMMDDHH24MISS' ) ZJSRQ\n" + " ,TO_CHAR(wd.ordercode) VBELN\n" + " ,TO_CHAR(wd.orderitem) POSNR\n" + " ,TO_CHAR(wd.outputnum) ZCLNG\n" + " ,TO_CHAR(wd.scrapnum) ZSPNG\n" + " ,TO_CHAR(wd.cleanupnum) ZQCNG\n" + " ,TO_CHAR(wd.recoverynum) ZHSNG\n" + " ,TO_CHAR(wd.repairnum) ZGBNG\n" + " ,TO_CHAR(wd.testmouldflag) ZSCMS\n" + " -- ,TO_CHAR(wd.zscs) zscs\n" + " ,'T' AS zscs\n" + " ,TO_CHAR(wd.WORKSHOP) ZSCCJ\n" + " ,TO_CHAR( :yyyymmdd) CHARG\n" + " ,TO_CHAR(60) datacode\n" + " FROM tsap_hegii_workdata_bg wd\n" + " INNER JOIN TSAP_HEGII_DATALOG_BG DL\n" + " ON wd.LOGID = DL.LOGID \n" + " WHERE wd.logid = :logid"; OracleParameter[] par = null; par = new OracleParameter[] { new OracleParameter(":logid", OracleDbType.Int32, logid_bg, ParameterDirection.Input), new OracleParameter(":yyyymmdd",yyyymmdd), }; DataTable dtt = oracleTrConn.GetSqlResultToDt(sqlString, par); int num = workData5000.Rows.Count; string sq = "select SAP_INI_BG from TP_SYS_SAPCONFIG"; string SAP_ING_NEW = oracleTrConn.GetSqlResultToStr(sq); if (SAP_ING_NEW == "1") { // 调用SAP接口 string postString = "{\"ZSUM\":" + num.ToString() + ",\"TABLE_IN\":{\"item\":" + JsonHelper.ToJson(ModelConvertHelper.ConvertToModel(workData5000)) + "}}"; //INIUtility ini = INIUtility.Instance(INIUtility.IniFile.SAP_HEGII); //string url030 = ini.ReadIniData("SAP_NEW_INFO", "Url030"); string url030 = "http://hgs4podev.hegii.com:50200/RESTAdapter/DKMES/ZPPFM030"; string result = PostData(url030, postString, "POST"); if (JObject.Parse(result)["TABLE_OUT"] != null && JObject.Parse(result)["TABLE_OUT"].ToString().Length > 0) { sqlString = "update TSAP_HEGII_WorkData_bg t set t.ZTime =sysdate, ZTYPE = :ZTYPE, ZMSG = :ZMSG where logid = " + logid_bg + " \n and YYYYMMDD='" + yyyymmdd + "' and WorkCode='" + workcode + "' and DataCode='60' and GoodsCode=:GoodsCode and SAPCode=:SAPCode and UserCode=:UserCode \n" + "and OrderCode=:OrderCode and OrderItem=:OrderItem"; Dictionary obj = JsonConvert.DeserializeObject>(result); object TABLE_OUT; obj.TryGetValue("TABLE_OUT", out TABLE_OUT); obj = JsonConvert.DeserializeObject>(TABLE_OUT + ""); object item1; obj.TryGetValue("item", out item1); JArray arr = JArray.FromObject(item1); foreach (JObject item60 in arr) { string posnr = item60["POSNR"].ToString().TrimStart('0'); oracleParameter = new OracleParameter[] { new OracleParameter(":ZTYPE",OracleDbType.Varchar2, item60["ZTYPE"].ToString(), ParameterDirection.Input), new OracleParameter(":ZMSG",OracleDbType.Varchar2, item60["ZMSG"].ToString(), ParameterDirection.Input), new OracleParameter(":GoodsCode",OracleDbType.Varchar2, item60["GROES"].ToString(), ParameterDirection.Input), new OracleParameter(":SAPCode",OracleDbType.Varchar2, item60["MATNR"].ToString(), ParameterDirection.Input), new OracleParameter(":UserCode",OracleDbType.Varchar2, item60["ZGHNU"].ToString(), ParameterDirection.Input), new OracleParameter(":OrderCode",OracleDbType.Varchar2, (string.IsNullOrEmpty(item60["VBELN"].ToString()) ? " " : item60["VBELN"].ToString()), ParameterDirection.Input), new OracleParameter(":Orderitem",OracleDbType.Varchar2, ((string.IsNullOrEmpty(posnr)) ? "0" : posnr), ParameterDirection.Input), }; r = oracleTrConn.ExecuteNonQuery(sqlString, oracleParameter); } //:msg||chr(13)||DataMSG JObject.Parse(result)["ZMSG"] = $"{JObject.Parse(result)["ZMSG"]}(" + num + ")"; sqlString = "update tsap_hegii_datalog_bg t set t.EndTime = sysdate, DataStuts = :DataStuts, DataMSG =nvl2(:msg,:msg||' '||chr(13)||DataMSG,DataMSG) where logid = :logid"; oracleParameter = new OracleParameter[] { new OracleParameter(":DataStuts",OracleDbType.Varchar2, JObject.Parse(result)["ZTYPE"].ToString(), ParameterDirection.Input), new OracleParameter(":msg",OracleDbType.Varchar2, JObject.Parse(result)["ZMSG"].ToString(), ParameterDirection.Input), new OracleParameter(":logid",OracleDbType.Int32, logid_bg, ParameterDirection.Input), }; r = oracleTrConn.ExecuteNonQuery(sqlString, oracleParameter); } if (JObject.Parse(result)["ZTYPE"].ToString() != "S") { sre.Result = -2; sre.Message = "同步产量失败，" + JObject.Parse(result)["ZMSG"].ToString(); return sre; } //sqlString = //"UPDATE tp_pm_groutingdailydetail bar\n" + //" SET bar.issync = '1'\n" + //" WHERE 1 = 1" + fifter; //r = oracleTrConn.ExecuteNonQuery(sqlString); } } #endregion oracleTrConn.Close(); return sre; } /// /// 同步SAP接口 /// /// /// /// /// /// public static ServiceResultEntity SyncSap(DateTime date, string datacode, int userid, int logid) { ServiceResultEntity sre = new ServiceResultEntity(); IDBTransaction oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { OracleParameter[] paras = null; string sqlString = string.Empty; sqlString = "select workcode from tp_mst_account where rownum = 1"; string workcode = oracleConn.GetSqlResultToStr(sqlString); string yyyymmdd = date.ToString("yyyyMMdd"); int r = 0; // 查询出当前日志外，最新的一次日志 sqlString = "SELECT t.executedateend\n" + " FROM (SELECT dl.executedateend\n" + " FROM tsap_hegii_datalog dl\n" + " WHERE dl.logtype = '3'\n" + " AND dl.datastuts = 'S'\n" + " AND dl.datacode = :datacode\n" + " AND dl.logid <> :logid\n" + " ORDER BY dl.executedateend DESC) t\n" + " WHERE rownum = 1"; paras = new OracleParameter[] { new OracleParameter(":datacode", datacode), new OracleParameter(":logid", logid) }; object executedateend = oracleConn.GetSqlResultToObj(sqlString, paras); if (executedateend == null || Convert.ToDateTime(executedateend) >= date) { sre.Result = "W"; sre.Message = "当前日志的结束时间小于最新日志的结束时间，不能同步"; return sre; } sqlString = "SELECT wd.yyyymmdd\n" + " ,wd.workcode\n" + " ,wd.datacode\n" + " ,wd.goodscode\n" + " ,wd.sapcode\n" + " ,wd.usercode\n" + " ,wd.ordercode\n" + " ,wd.orderitem\n" + " ,wd.outputnum\n" + " ,wd.scrapnum\n" + " ,wd.cleanupnum\n" + " ,wd.recoverynum\n" + " ,wd.repairnum\n" + " ,wd.testmouldflag\n" + " ,wd.zscs\n" + " FROM tsap_hegii_workdata wd\n" + " WHERE wd.logid = :logid"; paras = new OracleParameter[] { new OracleParameter(":logid", OracleDbType.Int32, logid, ParameterDirection.Input), }; DataTable workData = oracleConn.GetSqlResultToDt(sqlString, paras); Zppfm008 sapParameter = new Zppfm008(); if (workData != null && workData.Rows.Count > 0) { sapParameter.ZSUM = workData.Rows.Count; sapParameter.TABLE_IN = new ZSPP100[sapParameter.ZSUM]; int index = 0; foreach (DataRow item in workData.Rows) { ZSPP100 info100 = new ZSPP100(); // 工厂 info100.WERKS = workcode; // 型号 info100.GROES = item["GoodsCode"].ToString(); // 物料编号 info100.MATNR = item["SAPCode"].ToString(); // 生产工号 info100.ZGHNU = item["UserCode"].ToString(); // 数据节点 info100.ZJDNU = item["DataCode"].ToString(); // 时间戳 info100.ZSCNU = yyyymmdd; // 销售凭证 info100.VBELN = item["ORDERCODE"].ToString().Trim(); // 销售凭证项目 info100.POSNR = item["ORDERITEM"].ToString(); // 产量 info100.ZCLNG = item["OutputNum"].ToString(); // 损坯 info100.ZSPNG = item["SCRAPNUM"].ToString(); // 清除 info100.ZQCNG = item["CLEANUPNUM"].ToString(); // 回收 info100.ZHSNG = item["RECOVERYNUM"].ToString(); // 干补 info100.ZGBNG = item["REPAIRNUM"].ToString(); // 注浆类型 G高压 L普通 info100.ZSCS = item["ZSCS"].ToString(); info100.ZKSSJ = Convert.ToDateTime(executedateend).ToString("HHmmss"); info100.ZJSRQ = Convert.ToDecimal(date.ToString("yyyyMMddHHmmss")); sapParameter.TABLE_IN[index++] = info100; } } else { sapParameter.ZSUM = 0; sapParameter.TABLE_IN = new ZSPP100[sapParameter.ZSUM]; } // 调用SAP接口 ZPPFM008Response result = HGSAPDK_ZPPFM008(sapParameter, yyyymmdd, datacode); if (result.TABLE_OUT != null && result.TABLE_OUT.Length > 0) { sqlString = "update TSAP_HEGII_WorkData t set t.ZTime =sysdate, ZTYPE = :ZTYPE, ZMSG = :ZMSG where logid = " + logid + " \n and YYYYMMDD='" + yyyymmdd + "' and WorkCode='" + workcode + "' and DataCode=:DataCode and GoodsCode=:GoodsCode and SAPCode=:SAPCode and UserCode=:UserCode \n" + "and OrderCode=:OrderCode and OrderItem=:OrderItem"; foreach (ZSPP100 item in result.TABLE_OUT) { string posnr = item.POSNR.TrimStart('0'); paras = new OracleParameter[] { new OracleParameter(":ZTYPE",OracleDbType.Varchar2, item.ZTYPE, ParameterDirection.Input), new OracleParameter(":ZMSG",OracleDbType.Varchar2, item.ZMSG, ParameterDirection.Input), new OracleParameter(":DataCode",OracleDbType.Varchar2, datacode, ParameterDirection.Input), new OracleParameter(":GoodsCode",OracleDbType.Varchar2, item.GROES, ParameterDirection.Input), new OracleParameter(":SAPCode",OracleDbType.Varchar2, item.MATNR, ParameterDirection.Input), new OracleParameter(":UserCode",OracleDbType.Varchar2, item.ZGHNU, ParameterDirection.Input), new OracleParameter(":OrderCode",OracleDbType.Varchar2, (string.IsNullOrEmpty(item.VBELN) ? " " : item.VBELN), ParameterDirection.Input), new OracleParameter(":OrderItem",OracleDbType.Varchar2, ((string.IsNullOrEmpty(posnr)) ? "0" : posnr), ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, paras); } } result.ZMSG = $"{result.ZMSG}({sapParameter.ZSUM})"; sqlString = "update tsap_hegii_datalog t set t.EndTime = sysdate, DataStuts = :DataStuts, DataMSG =:msg where logid = :logid"; paras = new OracleParameter[] { new OracleParameter(":DataStuts",OracleDbType.Varchar2, result.ZTYPE, ParameterDirection.Input), new OracleParameter(":msg",OracleDbType.Varchar2, result.ZMSG, ParameterDirection.Input), new OracleParameter(":logid",OracleDbType.Int32, logid, ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, paras); if (result.ZTYPE == "S") { sqlString = "update tsap_hegii_datalog t set t.createuserid = :userid where logid = :logid"; paras = new OracleParameter[] { new OracleParameter(":logid",OracleDbType.Int32, logid, ParameterDirection.Input), new OracleParameter(":userid",OracleDbType.Int32, userid, ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, paras); } oracleConn.Commit(); sre.Message = result.ZMSG; sre.Result = result.ZTYPE; return sre; } catch (Exception ex) { throw ex; } finally { if (oracleConn != null && oracleConn.ConnState == ConnectionState.Open) { oracleConn.Disconnect(); } } } /// /// 同步成品条码（手动） /// /// /// /// //public static ServiceResultEntity SetFP6001(DateTime date, int userid) //{ // IDBTransaction oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); // ServiceResultEntity sre = new ServiceResultEntity(); // try // { // string yyyymmdd = date.ToString("yyyyMMdd"); // string sqlString = // "select t.logid\n" + // " from tsap_hegii_datalog t\n" + // " where t.logtype = '2'\n" + // " and t.datastuts = 'S'\n" + // " and t.yyyymmdd = :yyyymmdd\n" + // " and t.datacode = :datacode"; // OracleParameter[] oracleParameter = new OracleParameter[] // { // new OracleParameter(":yyyymmdd",OracleDbType.Varchar2, yyyymmdd, ParameterDirection.Input), // new OracleParameter(":datacode",OracleDbType.Varchar2, "6001", ParameterDirection.Input), // }; // string logid = oracleConn.GetSqlResultToStr(sqlString, oracleParameter); // if (!string.IsNullOrEmpty(logid)) // { // sre.Status = Constant.ServiceResultStatus.Other; // sre.Message = "日期【" + yyyymmdd + "】的数据已经同步过，不能重复同步。"; // return sre; // } // sqlString = // "select t.logid\n" + // " from tsap_hegii_datalog t\n" + // " where t.logtype = '1'\n" + // " and t.datastuts = 'S'\n" + // " and t.yyyymmdd = :yyyymmdd\n" + // " and t.datacode = :datacode" + // " and t.createuserid is null for update"; // string datalogid = oracleConn.GetSqlResultToStr(sqlString, oracleParameter); // sqlString = "select workcode from tp_mst_account where rownum = 1"; // string workcode = oracleConn.GetSqlResultToStr(sqlString); // //DateTime datebegin = date.Date; // //DateTime dateend = date.Date.AddDays(1); // logid = oracleConn.GetSqlResultToStr("select SEQ_SAP_HEGII_DataLog_ID.Nextval from dual"); // string msg = null; // //string msg = "data:" + datebegin.ToString("yyyyMMdd-HHmm") + "~" + // // dateend.ToString("yyyyMMdd-HHmm"); // sqlString = "insert into tsap_hegii_datalog\n" + // " (LogID\n" + // " ,LogType\n" + // " ,BeginTime\n" + // " ,YYYYMMDD\n" + // " ,WorkCode\n" + // " ,DataCode\n" + // " ,DataStuts\n" + // " ,DataMSG\n" + // " ,CreateUserID\n" + // " ,DataLogID)\n" + // "values\n" + // " (:LogID\n" + // " ,'2'\n" + // " ,sysdate\n" + // " ,:YYYYMMDD\n" + // " ,:WorkCode\n" + // " ,:DataCode\n" + // " ,:DataStuts\n" + // " ,:DataMSG\n" + // " ,:CreateUserID\n" + // " ,:DataLogID)"; // int r = 0; // if (string.IsNullOrEmpty(datalogid)) // { // oracleParameter = new OracleParameter[] // { // new OracleParameter(":LogID",OracleDbType.Int32, logid, ParameterDirection.Input), // new OracleParameter(":CreateUserID",OracleDbType.Int32, userid, ParameterDirection.Input), // new OracleParameter(":YYYYMMDD",OracleDbType.Varchar2, yyyymmdd, ParameterDirection.Input), // new OracleParameter(":WorkCode",OracleDbType.Varchar2, workcode, ParameterDirection.Input), // new OracleParameter(":DataCode",OracleDbType.Varchar2, "6001", ParameterDirection.Input), // new OracleParameter(":DataMSG",OracleDbType.Varchar2, "DKMES-ERROR:NO DATA " + msg, ParameterDirection.Input), // new OracleParameter(":DataStuts",OracleDbType.Varchar2, "E", ParameterDirection.Input), // new OracleParameter(":DataLogID",OracleDbType.Int32, 0, ParameterDirection.Input), // }; // r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); // oracleConn.Commit(); // sre.Status = Constant.ServiceResultStatus.Other; // sre.Message = "没有同步数据"; // sre.Result = "E"; // return sre; // } // oracleParameter = new OracleParameter[] // { // new OracleParameter(":LogID",OracleDbType.Int32, logid, ParameterDirection.Input), // new OracleParameter(":CreateUserID",OracleDbType.Int32, userid, ParameterDirection.Input), // new OracleParameter(":YYYYMMDD",OracleDbType.Varchar2, yyyymmdd, ParameterDirection.Input), // new OracleParameter(":WorkCode",OracleDbType.Varchar2, workcode, ParameterDirection.Input), // new OracleParameter(":DataCode",OracleDbType.Varchar2, "6001", ParameterDirection.Input), // new OracleParameter(":DataMSG",OracleDbType.Varchar2, msg, ParameterDirection.Input), // new OracleParameter(":DataStuts",OracleDbType.Varchar2, "S", ParameterDirection.Input), // new OracleParameter(":DataLogID",OracleDbType.Int32, datalogid, ParameterDirection.Input), // }; // r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); // sqlString = // "select fp.yyyymmdd\n" + // " ,fp.workcode\n" + // " ,fp.barcode\n" + // " ,fp.outcode\n" + // " ,fp.goodscode\n" + // " ,fp.sapcode\n" + // " ,fp.sapflbatchno\n" + // " ,fp.sapfhundoflag\n" + // " ,fp.ordercode\n" + // " ,fp.orderitem\n" + // " from tsap_hegii_finishedproduct fp where fp.logid = :datalogid\n" + // " order by fp.sapflbatchno, fp.barcode"; // oracleParameter = new OracleParameter[] // { // new OracleParameter(":datalogid",OracleDbType.Int32, datalogid, ParameterDirection.Input), // }; // DataTable fpData = oracleConn.GetSqlResultToDt(sqlString, oracleParameter); // // 单次传输最大条数 // int maxCount = 100000; // List sapParameterList = new List(); // if (fpData != null && fpData.Rows.Count > 0) // { // int index = 0; // //Zppfm010 sapParameter = new Zppfm010(); // //sapParameter.Zsum = maxCount; // //sapParameter.TableIn = new Zspp110[sapParameter.Zsum]; // List tableInList = new List(); // foreach (DataRow item in fpData.Rows) // { // if (index >= maxCount) // { // Zppfm010 sapItem = new Zppfm010(); // sapItem.ZSUM = tableInList.Count; // sapItem.TABLE_IN = tableInList.ToArray(); // sapParameterList.Add(sapItem); // index = 0; // tableInList.Clear(); // } // ZSPP110 info110 = new ZSPP110(); // // 时间戳 // info110.ZSCNU = yyyymmdd; // // 工厂 // info110.WERKS = workcode; // // 生产条码 // info110.ZSCTM = item["barcode"].ToString(); // // 包装条码 // info110.ZBZTM = item["outcode"].ToString(); // // 产品编码 // info110.ZCPBM = item["goodscode"].ToString(); // // 物料编号 // info110.MATNR = item["sapcode"].ToString(); // // 包装整板标识 // info110.ZBZBS = item["sapflbatchno"].ToString(); // // 重新绑定标识 // info110.ZCXBD = item["sapfhundoflag"].ToString(); // // 销售凭证 // info110.KDAUF = item["ordercode"].ToString(); // // 销售凭证项目 // info110.KDPOS = item["orderitem"].ToString(); // info110.ZCODEN = ""; // info110.ZCODEYZM = ""; // if (info110.WERKS == "5011" && info110.ZCPBM == "K047L") // { // continue; // } // tableInList.Add(info110); // index++; // } // Zppfm010 sapParameter = new Zppfm010(); // sapParameter.ZSUM = tableInList.Count; // sapParameter.TABLE_IN = tableInList.ToArray(); // sapParameterList.Add(sapParameter); // index = 0; // tableInList.Clear(); // } // else // { // Zppfm010 sapParameter = new Zppfm010(); // sapParameter.ZSUM = 0; // sapParameter.TABLE_IN = new ZSPP110[sapParameter.ZSUM]; // sapParameterList.Add(sapParameter); // } // foreach (Zppfm010 sapParameter in sapParameterList) // { // ZPPFM010Response result = HGSAPDK_ZPPFM010(sapParameter, yyyymmdd); // //sre.Message = result.ZMSG; // sre.Message = $"{result.ZMSG}({sapParameter.ZSUM})"; // sre.Result = result.ZTYPE; // sqlString = "update tsap_hegii_datalog t set t.EndTime = sysdate, DataStuts = :DataStuts, DataMSG =:msg where logid = :logid"; // oracleParameter = new OracleParameter[] // { // new OracleParameter(":DataStuts",OracleDbType.Varchar2, result.ZTYPE, ParameterDirection.Input), // //new OracleParameter(":msg",OracleDbType.Varchar2, result.ZMSG, ParameterDirection.Input), // new OracleParameter(":msg",OracleDbType.Varchar2, sre.Message, ParameterDirection.Input), // new OracleParameter(":logid",OracleDbType.Int32, logid, ParameterDirection.Input), // }; // r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); // if (result.TABLE_OUT != null && result.TABLE_OUT.Length > 0) // { // sqlString = "update tsap_hegii_finishedproduct t set t.ZTime =sysdate, ZTYPE = :ZTYPE, ZMSG = :ZMSG where logid = " + datalogid + // " \n and YYYYMMDD='" + yyyymmdd + "' and WorkCode='" + workcode + "' and barcode=:barcode"; // foreach (ZSPP110 item in result.TABLE_OUT) // { // oracleParameter = new OracleParameter[] // { // new OracleParameter(":ZTYPE",OracleDbType.Varchar2, item.ZTYPE, ParameterDirection.Input), // new OracleParameter(":ZMSG",OracleDbType.Varchar2, item.ZMSG, ParameterDirection.Input), // new OracleParameter(":barcode",OracleDbType.Varchar2, item.ZSCTM, ParameterDirection.Input), // }; // r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); // } // } // if (result.ZTYPE != "S") // { // oracleConn.Commit(); // //sre.Message = result.Zmsg; // //sre.Result = result.Ztype; // return sre; // } // } // //if (result.Ztype == "S") // { // sqlString = "update tsap_hegii_datalog t set t.createuserid = :userid where logid = :datalogid"; // oracleParameter = new OracleParameter[] // { // new OracleParameter(":datalogid",OracleDbType.Int32, datalogid, ParameterDirection.Input), // new OracleParameter(":userid",OracleDbType.Int32, userid, ParameterDirection.Input), // }; // r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); // } // oracleConn.Commit(); // //sre.Message = result.Zmsg; // //sre.Result = result.Ztype; // return sre; // } // catch (Exception ex) // { // throw ex; // } // finally // { // if (oracleConn != null && // oracleConn.ConnState == ConnectionState.Open) // { // oracleConn.Disconnect(); // } // } //} // /// // /// 同步成品条码（手动） // /// // /// // /// // /// // public static ServiceResultEntity SetFP60_T(DateTime date, int userid) // { // IDBTransaction oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); // ServiceResultEntity sre = new ServiceResultEntity(); // try // { // string yyyymmdd = date.ToString("yyyyMMdd"); // string sqlString = null; // sqlString = // "select fp.yyyymmdd\n" + // " ,fp.workcode\n" + // " ,fp.barcode\n" + // " ,fp.outcode\n" + // " ,fp.goodscode\n" + // " ,fp.sapcode\n" + // " ,fp.sapflbatchno\n" + // " ,fp.sapfhundoflag\n" + // " ,fp.ordercode\n" + // " ,fp.orderitem\n" + // " from tsap_hegii_finishedproduct fp where fp.logid=10339 and fp.sapflbatchno <> '575bd97d-5e3a-496f-8703-9e63be8016f0'\n" + // @" and fp.sapflbatchno <> '191204091310-46e7be7a-ea51-43a0-bd46-cb0866cef296' // and fp.sapflbatchno <> '191203234110-093d2c42-1842-4532-b7d6-39fe768ff35a' // and fp.sapflbatchno <> '191203234607-b242c5ef-2a42-4e4b-b02f-9a03798ee662' // and fp.sapflbatchno <> '191203234854-6218b6d8-4f14-4ab8-9be8-b86ce35f9a62' // and fp.sapflbatchno <> '191203234912-bfa6843d-767d-4ca7-8477-7b7242b7f900' // and fp.sapflbatchno <> '191204000111-18741887-f47b-4da7-9f0a-a970a30f79cc' //" + // " order by fp.sapflbatchno, fp.barcode"; // OracleParameter[] oracleParameter = new OracleParameter[] // { // new OracleParameter(":sapflbatchno",OracleDbType.Varchar2, "191204091310-46e7be7a-ea51-43a0-bd46-cb0866cef296", ParameterDirection.Input), // }; // DataTable fpData = oracleConn.GetSqlResultToDt(sqlString, oracleParameter); // // 单次传输最大条数 // int maxCount = 100000; // List sapParameterList = new List(); // if (fpData != null && fpData.Rows.Count > 0) // { // int index = 0; // //Zppfm010 sapParameter = new Zppfm010(); // //sapParameter.Zsum = maxCount; // //sapParameter.TableIn = new Zspp110[sapParameter.Zsum]; // List tableInList = new List(); // foreach (DataRow item in fpData.Rows) // { // if (index >= maxCount) // { // Zppfm010 sapItem = new Zppfm010(); // sapItem.ZSUM = tableInList.Count; // sapItem.TABLE_IN = tableInList.ToArray(); // sapParameterList.Add(sapItem); // index = 0; // tableInList.Clear(); // } // ZSPP110 info110 = new ZSPP110(); // // 时间戳 // info110.ZSCNU = yyyymmdd; // // 工厂 // info110.WERKS = "5020"; // // 生产条码 // info110.ZSCTM = item["barcode"].ToString(); // // 包装条码 // info110.ZBZTM = item["outcode"].ToString(); // // 产品编码 // info110.ZCPBM = item["goodscode"].ToString(); // // 物料编号 // info110.MATNR = item["sapcode"].ToString(); // // 包装整板标识 // info110.ZBZBS = item["sapflbatchno"].ToString(); // // 重新绑定标识 // info110.ZCXBD = item["sapfhundoflag"].ToString(); // // 销售凭证 // info110.KDAUF = item["ordercode"].ToString(); // // 销售凭证项目 // info110.KDPOS = item["orderitem"].ToString(); // info110.ZCODEN = ""; // info110.ZCODEYZM = ""; // tableInList.Add(info110); // index++; // } // Zppfm010 sapParameter = new Zppfm010(); // sapParameter.ZSUM = tableInList.Count; // sapParameter.TABLE_IN = tableInList.ToArray(); // sapParameterList.Add(sapParameter); // index = 0; // tableInList.Clear(); // } // else // { // Zppfm010 sapParameter = new Zppfm010(); // sapParameter.ZSUM = 0; // sapParameter.TABLE_IN = new ZSPP110[sapParameter.ZSUM]; // sapParameterList.Add(sapParameter); // } // foreach (Zppfm010 sapParameter in sapParameterList) // { // ZPPFM010Response result = HGSAPDK_ZPPFM010(sapParameter, yyyymmdd); // //sre.Message = result.ZMSG; // sre.Message = $"{result.ZMSG}({sapParameter.ZSUM})"; // sre.Result = result.ZTYPE; // } // return sre; // } // catch (Exception ex) // { // throw ex; // } // finally // { // if (oracleConn != null && // oracleConn.ConnState == ConnectionState.Open) // { // oracleConn.Disconnect(); // } // } // } ///// ///// 同步成品条码（手动） ///// ///// ///// ///// //public static ServiceResultEntity SetFP6002(DateTime date, int userid) //{ // IDBTransaction oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); // ServiceResultEntity sre = new ServiceResultEntity(); // try // { // string yyyymmdd = date.ToString("yyyyMMdd"); // string sqlString = // "select t.logid\n" + // " from tsap_hegii_datalog t\n" + // " where t.logtype = '2'\n" + // " and t.datastuts = 'S'\n" + // " and t.yyyymmdd = :yyyymmdd\n" + // " and t.datacode = :datacode"; // OracleParameter[] oracleParameter = new OracleParameter[] // { // new OracleParameter(":yyyymmdd",OracleDbType.Varchar2, yyyymmdd, ParameterDirection.Input), // new OracleParameter(":datacode",OracleDbType.Varchar2, "6002", ParameterDirection.Input), // }; // string logid = oracleConn.GetSqlResultToStr(sqlString, oracleParameter); // if (!string.IsNullOrEmpty(logid)) // { // sre.Status = Constant.ServiceResultStatus.Other; // sre.Message = "日期【" + yyyymmdd + "】的数据已经同步过，不能重复同步。"; // return sre; // } // sqlString = // "select t.logid, t.begintime, t.endtime\n" + // " from tsap_hegii_datalog t\n" + // " where t.logtype = '1'\n" + // " and t.datastuts = 'S'\n" + // " and t.yyyymmdd = :yyyymmdd\n" + // " and t.datacode = :datacode" + // " and t.createuserid is null for update"; // //string datalogid = oracleConn.GetSqlResultToStr(sqlString, oracleParameter); // DataTable dt = oracleConn.GetSqlResultToDt(sqlString, oracleParameter); // string datalogid = null; // string msg = null; // if (dt.Rows.Count > 0) // { // datalogid = dt.Rows[0]["logid"] + ""; // msg = Convert.ToDateTime(dt.Rows[0]["endtime"]).ToString("HH:mm "); // } // sqlString = "select workcode from tp_mst_account where rownum = 1"; // string workcode = oracleConn.GetSqlResultToStr(sqlString); // //DateTime datebegin = date.Date; // //DateTime dateend = date.Date.AddDays(1); // logid = oracleConn.GetSqlResultToStr("select SEQ_SAP_HEGII_DataLog_ID.Nextval from dual"); // //string msg = "data:" + datebegin.ToString("yyyyMMdd-HHmm") + "~" + // // dateend.ToString("yyyyMMdd-HHmm"); // sqlString = "insert into tsap_hegii_datalog\n" + // " (LogID\n" + // " ,LogType\n" + // " ,BeginTime\n" + // " ,YYYYMMDD\n" + // " ,WorkCode\n" + // " ,DataCode\n" + // " ,DataStuts\n" + // " ,DataMSG\n" + // " ,CreateUserID\n" + // " ,DataLogID)\n" + // "values\n" + // " (:LogID\n" + // " ,'2'\n" + // " ,sysdate\n" + // " ,:YYYYMMDD\n" + // " ,:WorkCode\n" + // " ,:DataCode\n" + // " ,:DataStuts\n" + // " ,:DataMSG\n" + // " ,:CreateUserID\n" + // " ,:DataLogID)"; // int r = 0; // if (string.IsNullOrEmpty(datalogid)) // { // oracleParameter = new OracleParameter[] // { // new OracleParameter(":LogID",OracleDbType.Int32, logid, ParameterDirection.Input), // new OracleParameter(":CreateUserID",OracleDbType.Int32, userid, ParameterDirection.Input), // new OracleParameter(":YYYYMMDD",OracleDbType.Varchar2, yyyymmdd, ParameterDirection.Input), // new OracleParameter(":WorkCode",OracleDbType.Varchar2, workcode, ParameterDirection.Input), // new OracleParameter(":DataCode",OracleDbType.Varchar2, "6002", ParameterDirection.Input), // new OracleParameter(":DataMSG",OracleDbType.Varchar2, msg + "DKMES-ERROR:NO DATA " , ParameterDirection.Input), // new OracleParameter(":DataStuts",OracleDbType.Varchar2, "E", ParameterDirection.Input), // new OracleParameter(":DataLogID",OracleDbType.Int32, 0, ParameterDirection.Input), // }; // r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); // oracleConn.Commit(); // sre.Status = Constant.ServiceResultStatus.Other; // sre.Message = "没有同步数据"; // sre.Result = "E"; // return sre; // } // oracleParameter = new OracleParameter[] // { // new OracleParameter(":LogID",OracleDbType.Int32, logid, ParameterDirection.Input), // new OracleParameter(":CreateUserID",OracleDbType.Int32, userid, ParameterDirection.Input), // new OracleParameter(":YYYYMMDD",OracleDbType.Varchar2, yyyymmdd, ParameterDirection.Input), // new OracleParameter(":WorkCode",OracleDbType.Varchar2, workcode, ParameterDirection.Input), // new OracleParameter(":DataCode",OracleDbType.Varchar2, "6002", ParameterDirection.Input), // new OracleParameter(":DataMSG",OracleDbType.Varchar2, msg, ParameterDirection.Input), // new OracleParameter(":DataStuts",OracleDbType.Varchar2, "S", ParameterDirection.Input), // new OracleParameter(":DataLogID",OracleDbType.Int32, datalogid, ParameterDirection.Input), // }; // r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); // sqlString = // "select fp.yyyymmdd\n" + // " ,fp.workcode\n" + // " ,fp.barcode\n" + // " ,fp.outcode\n" + // " ,fp.goodscode\n" + // " ,fp.sapcode\n" + // " ,fp.sapflbatchno\n" + // " ,fp.sapfhundoflag\n" + // " ,fp.ordercode\n" + // " ,fp.orderitem\n" + // //" from tsap_hegii_finishedproduct fp where fp.logid = :datalogid and (ZTYPE is null or ZTYPE <> 'S')\n" + // " from tsap_hegii_finishedproduct fp where fp.logid = :datalogid and (ZTYPE is null)\n" + // " order by fp.sapflbatchno, fp.barcode"; // oracleParameter = new OracleParameter[] // { // new OracleParameter(":datalogid",OracleDbType.Int32, datalogid, ParameterDirection.Input), // }; // DataTable fpData = oracleConn.GetSqlResultToDt(sqlString, oracleParameter); // // 单次传输最大条数 // int maxCount = 100000; // List sapParameterList = new List(); // if (fpData != null && fpData.Rows.Count > 0) // { // int index = 0; // //Zppfm010 sapParameter = new Zppfm010(); // //sapParameter.Zsum = maxCount; // //sapParameter.TableIn = new Zspp110[sapParameter.Zsum]; // List tableInList = new List(); // foreach (DataRow item in fpData.Rows) // { // if (index >= maxCount) // { // Zppfm010 sapItem = new Zppfm010(); // sapItem.ZSUM = tableInList.Count; // sapItem.TABLE_IN = tableInList.ToArray(); // sapParameterList.Add(sapItem); // index = 0; // tableInList.Clear(); // } // ZSPP110 info110 = new ZSPP110(); // // 时间戳 // info110.ZSCNU = yyyymmdd; // // 工厂 // info110.WERKS = workcode; // // 生产条码 // info110.ZSCTM = item["barcode"].ToString(); // // 包装条码 // info110.ZBZTM = item["outcode"].ToString(); // // 产品编码 // info110.ZCPBM = item["goodscode"].ToString(); // // 物料编号 // info110.MATNR = item["sapcode"].ToString(); // // 包装整板标识 // info110.ZBZBS = item["sapflbatchno"].ToString(); // // 重新绑定标识 // info110.ZCXBD = item["sapfhundoflag"].ToString(); // // 销售凭证 // info110.KDAUF = item["ordercode"].ToString(); // // 销售凭证项目 // info110.KDPOS = item["orderitem"].ToString(); // info110.ZCODEN = ""; // info110.ZCODEYZM = ""; // tableInList.Add(info110); // index++; // } // Zppfm010 sapParameter = new Zppfm010(); // sapParameter.ZSUM = tableInList.Count; // sapParameter.TABLE_IN = tableInList.ToArray(); // sapParameterList.Add(sapParameter); // index = 0; // tableInList.Clear(); // } // else // { // Zppfm010 sapParameter = new Zppfm010(); // sapParameter.ZSUM = 0; // sapParameter.TABLE_IN = new ZSPP110[sapParameter.ZSUM]; // sapParameterList.Add(sapParameter); // } // foreach (Zppfm010 sapParameter in sapParameterList) // { // ZPPFM010Response result = HGSAPDK_ZPPFM010(sapParameter, yyyymmdd); // //sre.Message = result.ZMSG; // sre.Message = $"{result.ZMSG}({sapParameter.ZSUM})"; // sre.Result = result.ZTYPE; // sqlString = "update tsap_hegii_datalog t set t.EndTime = sysdate, DataStuts = :DataStuts, DataMSG =DataMSG||:msg where logid = :logid"; // oracleParameter = new OracleParameter[] // { // new OracleParameter(":DataStuts",OracleDbType.Varchar2, result.ZTYPE, ParameterDirection.Input), // //new OracleParameter(":msg",OracleDbType.Varchar2, result.ZMSG, ParameterDirection.Input), // new OracleParameter(":msg",OracleDbType.Varchar2, sre.Message, ParameterDirection.Input), // new OracleParameter(":logid",OracleDbType.Int32, logid, ParameterDirection.Input), // }; // r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); // if (result.TABLE_OUT != null && result.TABLE_OUT.Length > 0) // { // sqlString = "update tsap_hegii_finishedproduct t set t.ZTime =sysdate, ZTYPE = nvl(:ZTYPE,'S'), ZMSG = :ZMSG where logid = " + datalogid + // " \n and YYYYMMDD='" + yyyymmdd + "' and WorkCode='" + workcode + "' and barcode=:barcode and sapflbatchno=:sapflbatchno"; // foreach (ZSPP110 item in result.TABLE_OUT) // { // oracleParameter = new OracleParameter[] // { // new OracleParameter(":ZTYPE",OracleDbType.Varchar2, item.ZTYPE, ParameterDirection.Input), // new OracleParameter(":ZMSG",OracleDbType.Varchar2, item.ZMSG, ParameterDirection.Input), // new OracleParameter(":barcode",OracleDbType.Varchar2, item.ZSCTM, ParameterDirection.Input), // new OracleParameter(":sapflbatchno",OracleDbType.Varchar2, item.ZBZBS, ParameterDirection.Input), // }; // r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); // } // } // if (result.ZTYPE != "S") // { // oracleConn.Commit(); // //sre.Message = result.Zmsg; // //sre.Result = result.Ztype; // return sre; // } // } // //if (result.Ztype == "S") // { // sqlString = "update tsap_hegii_datalog t set t.createuserid = :userid where logid = :datalogid"; // oracleParameter = new OracleParameter[] // { // new OracleParameter(":datalogid",OracleDbType.Int32, datalogid, ParameterDirection.Input), // new OracleParameter(":userid",OracleDbType.Int32, userid, ParameterDirection.Input), // }; // r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); // } // oracleConn.Commit(); // //sre.Message = result.Zmsg; // //sre.Result = result.Ztype; // return sre; // } // catch (Exception ex) // { // throw ex; // } // finally // { // if (oracleConn != null && // oracleConn.ConnState == ConnectionState.Open) // { // oracleConn.Disconnect(); // } // } //} /// /// 产量接口 /// /// /// public static ZPPFM008Response HGSAPDK_ZPPFM008(Zppfm008 sapParameter, string yyyymmdd, string datacode, string SPANAME = "") { ZPPFM008Response sapResult = null; try { using (ZPPFM008Client sapClient = new ZPPFM008Client("ZPPFM008_BIND" + SPANAME)) { // 登录 UserNamePasswordClientCredential credential = sapClient.ClientCredentials.UserName; //credential.UserName = "hgsapdk"; //credential.Password = "Sapdk#240"; INIUtility ini = INIUtility.Instance(INIUtility.IniFile.SAP_HEGII); credential.UserName = ini.ReadIniData("SAP_HEGII", "UserName"); credential.Password = ini.ReadIniData("SAP_HEGII", "Password"); // 参数 sapParameter.ZSUMSpecified = true; sapParameter.TABLE_OUT = new ZSPP100[0]; // 接口 sapResult = sapClient.ZPPFM008(sapParameter); if (sapResult.ZTYPE == "S") { // 结果 OutputLog.TraceLog(LogPriority.Information, "HGSAPDK_ZPPFM008", "yyyymmdd:" + yyyymmdd + " datacode:" + datacode + " Zsum:" + sapParameter.ZSUM, " Ztype:" + sapResult.ZTYPE + " Zmsg:" + sapResult.ZMSG, LocalPath.LogExePath + "SAP_HEGII\\Info_"); } else { foreach (ZSPP100 item in sapResult.TABLE_OUT) { if (!string.IsNullOrWhiteSpace(item.ZMSG)) { sapResult.ZMSG += $"[{item.ZMSG}]"; break; } } // 结果 OutputLog.TraceLog(LogPriority.Information, "HGSAPDK_ZPPFM008", "yyyymmdd:" + yyyymmdd + " datacode:" + datacode + " Zsum:" + sapParameter.ZSUM + " Ztype:" + sapResult.ZTYPE + " Zmsg:" + sapResult.ZMSG, JsonHelper.ToJson(sapResult), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } } } catch (Exception ex) { sapResult = new ZPPFM008Response(); sapResult.ZTYPE = "E"; sapResult.ZMSG = ex.Message; //if (ex is System.ServiceModel.FaultException) //{ // sapResult.ZMSG = ex.Message; //} //else //{ // sapResult.ZMSG = "DKMES-ERROR:" + Guid.NewGuid(); //} OutputLog.TraceLog(LogPriority.Error, "HGSAPDK_ZPPFM008", sapResult.ZMSG, ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } return sapResult; } ///// ///// 成品明细(条码接口) ///// ///// //public static ZPPFM010Response HGSAPDK_ZPPFM010(Zppfm010 sapParameter, string yyyymmdd, string SPANAME = "") //{ // ZPPFM010Response sapResult = null; // try // { // using (ZPPFM010Client sapClient = new ZPPFM010Client("ZPPFM010_BIND" + SPANAME)) // { // // 登录 // UserNamePasswordClientCredential credential = // sapClient.ClientCredentials.UserName; // //credential.UserName = "hgsapdk"; // //credential.Password = "Sapdk#240"; // INIUtility ini = INIUtility.Instance(INIUtility.IniFile.SAP_HEGII); // credential.UserName = ini.ReadIniData("SAP_HEGII", "UserName"); // credential.Password = ini.ReadIniData("SAP_HEGII", "Password"); // // 参数 // sapParameter.ZSUMSpecified = true; // sapParameter.TABLE_OUT = new ZSPP110[0]; // // 接口 // sapResult = sapClient.ZPPFM010(sapParameter); // if (sapResult.ZTYPE == "S") // { // // 结果 // OutputLog.TraceLog(LogPriority.Information, // "HGSAPDK_ZPPFM010", // "yyyymmdd:" + yyyymmdd + " Zsum:" + sapParameter.ZSUM, // " Ztype:" + sapResult.ZTYPE + " Zmsg:" + sapResult.ZMSG, // LocalPath.LogExePath + "SAP_HEGII\\Info_"); // } // else // { // foreach (ZSPP110 item in sapResult.TABLE_OUT) // { // if (!string.IsNullOrWhiteSpace(item.ZMSG)) // { // sapResult.ZMSG += $"[{item.ZMSG}]"; // break; // } // } // // 结果 // OutputLog.TraceLog(LogPriority.Information, // "HGSAPDK_ZPPFM010", // "yyyymmdd:" + yyyymmdd + " Zsum:" + sapParameter.ZSUM + " Ztype:" + sapResult.ZTYPE + " Zmsg:" + sapResult.ZMSG, // JsonHelper.ToJson(sapResult), // LocalPath.LogExePath + "SAP_HEGII\\Error_"); // } // } // } // catch (Exception ex) // { // sapResult = new ZPPFM010Response(); // sapResult.ZTYPE = "E"; // sapResult.ZMSG = ex.Message; // //if (ex is System.ServiceModel.FaultException) // //{ // // sapResult.ZMSG = ex.Message; // //} // //else // //{ // // sapResult.ZMSG = "DKMES-ERROR:" + Guid.NewGuid(); // //} // OutputLog.TraceLog(LogPriority.Error, // "HGSAPDK_ZPPFM010", // sapResult.ZMSG, // ex.ToString(), // LocalPath.LogExePath + "SAP_HEGII\\Error_"); // } // return sapResult; //} #region 测试240 /// /// 同步成品条码（手动）-测试 /// /// /// /// public static ServiceResultEntity SetFP60_240(DateTime date, int userid) { /* Zppfm008 sapParameter1 = new Zppfm008(); sapParameter1.Zsum = 0; sapParameter1.TableIn = new Zspp100[0]; Zppfm010 sapParameter2 = new Zppfm010(); sapParameter2.ZSUM = 30; sapParameter2.TABLE_IN = new ZSPP110[30]; for (int i = 0; i < 10; i++) { ZSPP110 info110 = new ZSPP110(); // 时间戳 info110.ZSCNU = "20170101"; // 工厂 info110.WERKS = "5020"; // 生产条码 info110.ZSCTM = "20170101" + i.ToString().PadLeft(3, '0'); // 包装条码 info110.ZBZTM = "CT127PD2110B0150" + info110.ZSCTM + "1"; // 产品编码 info110.ZCPBM = "H0112L"; // 物料编号 info110.MATNR = "CT127PD2110B01"; // 包装整板标识 info110.ZBZBS = "1-1-1"; // 重新绑定标识 info110.ZCXBD = "X"; // 销售凭证 info110.KDAUF = "1200032630"; // 销售凭证项目 info110.KDPOS = "10"; sapParameter2.TABLE_IN[i] = info110; } for (int i = 10; i < 20; i++) { ZSPP110 info110 = new ZSPP110(); // 时间戳 info110.ZSCNU = "20170101"; // 工厂 info110.WERKS = "5020"; // 生产条码 info110.ZSCTM = "20170101" + i.ToString().PadLeft(3, '0'); // 包装条码 info110.ZBZTM = "CT127PD2110B0150" + info110.ZSCTM + "1"; // 产品编码 info110.ZCPBM = "H0112L"; // 物料编号 info110.MATNR = "CT127PD2110B01"; // 包装整板标识 info110.ZBZBS = "1-1-1"; // 重新绑定标识 info110.ZCXBD = ""; // 销售凭证 info110.KDAUF = "1200030151"; // 销售凭证项目 info110.KDPOS = "10"; sapParameter2.TABLE_IN[i] = info110; } for (int i = 20; i < 30; i++) { ZSPP110 info110 = new ZSPP110(); // 时间戳 info110.ZSCNU = "20170101"; // 工厂 info110.WERKS = "5020"; // 生产条码 info110.ZSCTM = "20170101" + i.ToString().PadLeft(3, '0'); // 包装条码 info110.ZBZTM = "CT967ES2210N0150" + info110.ZSCTM + "1"; // 产品编码 info110.ZCPBM = "H0112L"; // 物料编号 info110.MATNR = "CT967ES2210N01"; // 包装整板标识 info110.ZBZBS = "1-1-1"; // 重新绑定标识 info110.ZCXBD = ""; // 销售凭证 info110.KDAUF = "1200032678"; // 销售凭证项目 info110.KDPOS = "10"; sapParameter2.TABLE_IN[i] = info110; } */ //HGSAPDK_ZPPFM008(sapParameter1, date.ToString("yyyyMMdd"), "800"); //HGSAPDK_ZPPFM008_240(sapParameter1, date.ToString("yyyyMMdd"), "240"); //HGSAPDK_ZPPFM010(sapParameter2, date.ToString("yyyyMMdd")); //ZPPFM010Response result1 = HGSAPDK_ZPPFM010_240(sapParameter2, date.ToString("yyyyMMdd")); //foreach (ZSPP110 item in result1.TABLE_OUT) //{ // if (item.ZTYPE != "S" && !string.IsNullOrWhiteSpace(item.ZMSG)) // { // OutputLog.TraceLog(LogPriority.Information, // "HGSAPDK_ZPPFM010_240", JsonHelper.ToJson(item), // "Ztype:" + item.ZTYPE + " Zmsg:" + item.ZMSG, // LocalPath.LogExePath + "SAP_HEGII\\Info_"); // } //} //return new ServiceResultEntity(); IDBTransaction oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity sre = new ServiceResultEntity(); date = new DateTime(2021, 12, 8); DateTime dateend = date; INIUtility ini = INIUtility.Instance(INIUtility.IniFile.Config); string EndDate6001 = ini.ReadIniData("SAP_HEGII", "EndDate6001"); DateTime end = DateTime.Now.Date.AddDays(-1); if (!string.IsNullOrWhiteSpace(EndDate6001)) { end = DateTime.ParseExact(EndDate6001, "yyyy-MM-dd", null); } try { //string sqlString = // "select fp.yyyymmdd\n" + // " ,fp.workcode\n" + // " ,fp.barcode\n" + // " ,fp.outcode\n" + // " ,fp.goodscode\n" + // " ,fp.sapcode\n" + // " ,fp.sapflbatchno\n" + // " ,fp.sapfhundoflag\n" + // " from tsap_hegii_finishedproduct fp where fp.logid = :datalogid"; string sqlString = "select to_char(fp.fhtime, 'yyyymmdd') yyyymmdd\n" + " ,aa.workcode workcode\n" + " ,fp.barcode\n" + " ,case when length(gdd.onlycode) = 7 then gdd.materialcode || 'FF' || gdd.onlycode else nvl(gdd.outlabelcode\n" + " ,(gdd.materialcode || aa.workcode || gdd.onlycode)) end outcode\n" + " ,gdd.goodscode\n" + " ,nvl(gdd.materialcode, nvl(g.materialcode, g.goodscode)) sapcode\n" + " ,nvl(fp.finishedloadbatchno, fp.fhbatchno) sapflbatchno\n" + " ,' ' sapfhundoflag\n" + " ,gdd.securitycode\n" + " from tp_pm_finishedproduct fp\n" + " inner join tp_pm_groutingdailydetail gdd\n" + " on gdd.barcode = fp.barcode\n" + " inner join tp_mst_goods g\n" + " on g.goodsid = gdd.goodsid\n" + " inner join tp_mst_account aa\n" + " on aa.accountid = gdd.accountid\n" + " where fp.fhtime >= :v_datebegin\n" + " and fp.fhtime < :v_dateend\n" + " and gdd.securitycode is not null\n" + " and rownum < 101\n" + " order by fp.fhtime,fp.barcode"; while (date <= end) { dateend = date.AddDays(1); OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter(":v_datebegin",OracleDbType.Date, date, ParameterDirection.Input), new OracleParameter(":v_dateend",OracleDbType.Date, dateend, ParameterDirection.Input), }; DataTable fpData = oracleConn.GetSqlResultToDt(sqlString, oracleParameter); // 单次传输最大条数 int maxCount = 10000; List sapParameterList = new List(); if (fpData != null && fpData.Rows.Count > 0) { int index = 0; List tableInList = new List(); foreach (DataRow item in fpData.Rows) { if (index >= maxCount) { Zppfm010 sapItem = new Zppfm010(); sapItem.ZSUM = tableInList.Count; sapItem.TABLE_IN = tableInList.ToArray(); sapParameterList.Add(sapItem); index = 0; tableInList.Clear(); } ZSPP110 info110 = new ZSPP110(); // 时间戳 info110.ZSCNU = item["yyyymmdd"].ToString(); // 工厂 info110.WERKS = item["workcode"].ToString(); // 生产条码 info110.ZSCTM = item["barcode"].ToString(); // 包装条码 info110.ZBZTM = item["outcode"].ToString(); //生产防伪码 xuwei add 2021-12-09 info110.SECURITYCODE = item["securitycode"].ToString(); // 产品编码 info110.ZCPBM = item["goodscode"].ToString(); // 物料编号 info110.MATNR = item["sapcode"].ToString(); // 包装整板标识 info110.ZBZBS = item["sapflbatchno"].ToString(); // 重新绑定标识 info110.ZCXBD = item["sapfhundoflag"].ToString(); if (info110.WERKS == "5011" && info110.ZCPBM == "K047L") { continue; } tableInList.Add(info110); index++; } Zppfm010 sapParameter = new Zppfm010(); sapParameter.ZSUM = tableInList.Count; sapParameter.TABLE_IN = tableInList.ToArray(); sapParameterList.Add(sapParameter); index = 0; tableInList.Clear(); } else { Zppfm010 sapParameter = new Zppfm010(); sapParameter.ZSUM = 0; sapParameter.TABLE_IN = new ZSPP110[sapParameter.ZSUM]; sapParameterList.Add(sapParameter); } int indexP = 0; int ccc = sapParameterList.Count; foreach (Zppfm010 sapParameter in sapParameterList) //for (int i = 80; i < ccc; i++) { //Zppfm010 sapParameter = sapParameterList[i]; ZPPFM010Response result = HGSAPDK_ZPPFM010_240(sapParameter, date.ToString("yyyyMMdd") + " " + (++indexP) + "/" + ccc); //Zppfm010Response result = HGSAPDK_ZPPFM010(sapParameter, "2018 " + (i) + "/" + ccc); sre.Message = result.ZMSG; sre.Result = result.ZTYPE; if (result.ZTYPE != "S") { foreach (ZSPP110 item in result.TABLE_OUT) { if (item.ZTYPE != "S" && !string.IsNullOrWhiteSpace(item.ZMSG)) { OutputLog.TraceLog(LogPriority.Information, "HGSAPDK_ZPPFM010", JsonHelper.ToJson(item), "Ztype:" + item.ZTYPE + " Zmsg:" + item.ZMSG, LocalPath.LogExePath + "SAP_HEGII\\Info_"); } } return sre; } } date = dateend; } oracleConn.Commit(); //sre.Message = result.Zmsg; //sre.Result = result.Ztype; return sre; } catch (Exception ex) { throw ex; } finally { if (oracleConn != null && oracleConn.ConnState == ConnectionState.Open) { oracleConn.Disconnect(); } } } /// /// 产量接口-测试 /// /// /// public static ZPPFM008Response HGSAPDK_ZPPFM008_240(Zppfm008 sapParameter, string yyyymmdd, string datacode, string SPANAME = "") { ZPPFM008Response sapResult = null; try { using (ZPPFM008Client sapClient = new ZPPFM008Client("ZPPFM008_BIND_240" + SPANAME)) { // 登录 UserNamePasswordClientCredential credential = sapClient.ClientCredentials.UserName; credential.UserName = "hgsapdk"; credential.Password = "Sapdk#240"; // 参数 sapParameter.ZSUMSpecified = true; sapParameter.TABLE_OUT = new ZSPP100[0]; // 接口 sapResult = sapClient.ZPPFM008(sapParameter); // 结果 OutputLog.TraceLog(LogPriority.Information, "HGSAPDK_ZPPFM008_240", "yyyymmdd:" + yyyymmdd + " datacode:" + datacode + " Zsum:" + sapParameter.ZSUM, "Ztype:" + sapResult.ZTYPE + " Zmsg:" + sapResult.ZMSG, LocalPath.LogExePath + "SAP_HEGII\\Info_"); } } catch (Exception ex) { sapResult = new ZPPFM008Response(); sapResult.ZTYPE = "E"; if (ex is System.ServiceModel.FaultException) { sapResult.ZMSG = ex.Message; } else { sapResult.ZMSG = "DKMES-ERROR:" + Guid.NewGuid(); } OutputLog.TraceLog(LogPriority.Error, "HGSAPDK_ZPPFM008_240", sapResult.ZMSG, ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } return sapResult; } /// /// 成品明细(条码接口)-测试 /// /// public static ZPPFM010Response HGSAPDK_ZPPFM010_240(Zppfm010 sapParameter, string yyyymmdd, string SPANAME = "") { ZPPFM010Response sapResult = null; try { using (ZPPFM010Client sapClient = new ZPPFM010Client("ZPPFM010_BIND_240" + SPANAME)) { // 登录 UserNamePasswordClientCredential credential = sapClient.ClientCredentials.UserName; credential.UserName = "hgsapdk"; credential.Password = "Sapdk#240"; // 参数 sapParameter.ZSUMSpecified = true; sapParameter.TABLE_OUT = new ZSPP110[0]; // 接口 sapResult = sapClient.ZPPFM010(sapParameter); // 结果 OutputLog.TraceLog(LogPriority.Information, "HGSAPDK_ZPPFM010_240", "yyyymmdd:" + yyyymmdd + " Zsum:" + sapParameter.ZSUM, "Ztype:" + sapResult.ZTYPE + " Zmsg:" + sapResult.ZMSG, LocalPath.LogExePath + "SAP_HEGII\\Info_"); } } catch (Exception ex) { sapResult = new ZPPFM010Response(); sapResult.ZTYPE = "E"; if (ex is System.ServiceModel.FaultException) { sapResult.ZMSG = ex.Message; } else { sapResult.ZMSG = "DKMES-ERROR:" + Guid.NewGuid(); } OutputLog.TraceLog(LogPriority.Error, "HGSAPDK_ZPPFM010_240", sapResult.ZMSG, ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } return sapResult; } #endregion #region 测试800 /// /// 同步成品条码（手动）-测试 /// /// /// /// public static ServiceResultEntity SetFP60_800(DateTime date, int userid) { IDBTransaction oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity sre = new ServiceResultEntity(); date = new DateTime(2018, 7, 1); DateTime dateend = date; //INIUtility ini = INIUtility.Instance(INIUtility.IniFile.Config); //string EndDate6001 = ini.ReadIniData("SAP_HEGII", "EndDate6001"); DateTime end = new DateTime(2018, 12, 10); //if (!string.IsNullOrWhiteSpace(EndDate6001)) //{ // end = DateTime.ParseExact(EndDate6001, "yyyy-MM-dd", null); //} try { string sqlString = //"INSERT INTO tsap_hegii_finishedproduct\n" + //" (yyyymmdd\n" + //" ,workcode\n" + //" ,barcode\n" + //" ,outcode\n" + //" ,goodscode\n" + //" ,sapcode\n" + //" ,sapflbatchno\n" + //" ,sapfhundoflag\n" + //" ,ordercode\n" + //" ,orderitem, LOGID)\n" + " SELECT :yyyymmdd yyyymmdd\n" + " ,:workcode workcode\n" + " ,bar.barcode\n" + " ,case when length(gdd.onlycode) = 7 then gdd.materialcode || 'FF' || gdd.onlycode else nvl(gdd.outlabelcode\n" + " ,(gdd.materialcode || :workcode || gdd.onlycode)) end outcode\n" + //" ,nvl(gdd.outlabelcode, (g.materialcode || :workcode || gdd.onlycode)) outcode\n" + " ,gdd.goodscode\n" + " ,nvl(gdd.materialcode, nvl(g.materialcode, g.goodscode)) sapcode\n" + " ,nvl(bar.finishedloadbatchno, bar.fhbatchno) sapflbatchno\n" + " ,' ' sapfhundoflag\n" + " ,CASE\n" + " WHEN o.orderid IS NULL\n" + " OR o.orderno LIKE 'HEGII%' THEN\n" + " NULL\n" + " WHEN instr(o.orderno, '/') = 0 THEN\n" + " to_char(o.orderno)\n" + " ELSE\n" + " to_char(substr(o.orderno, 1, instr(o.orderno, '/') - 1))\n" + " END ordercode -- 销售凭证\n" + " ,CASE\n" + " WHEN o.orderid IS NULL\n" + " OR o.orderno LIKE 'HEGII%'\n" + " OR instr(o.orderno, '/') = 0 THEN\n" + " '0'\n" + " WHEN instr(o.orderno, '#') = 0 THEN\n" + " to_char(substr(o.orderno, instr(o.orderno, '/') + 1))\n" + " ELSE\n" + " to_char(substr(o.orderno\n" + " ,instr(o.orderno, '/') + 1\n" + " ,instr(o.orderno, '#') - instr(o.orderno, '/') - 1))\n" + " END orderitem -- 销售凭证项目\n" + " ,:LOGID\n" + " FROM tp_pm_finishedproduct bar\n" + " INNER JOIN tp_pm_groutingdailydetail gdd\n" + " ON gdd.barcode = bar.barcode\n" + " INNER JOIN tp_mst_goods g\n" + " ON g.goodsid = gdd.goodsid\n" + " LEFT JOIN tp_pm_order o\n" + " ON o.orderid = bar.fhorderid\n" + " where bar.fhtime >= :v_datebegin\n" + " and bar.fhtime < :v_dateend\n" + " and nvl(gdd.materialcode, g.materialcode) in ( 'CT968EN4100B01','CT968EN2100B01')" + " order by bar.fhtime,bar.finishedloadbatchno, bar.fhbatchno,bar.barcode"; //OracleParameter [] oracleParameter = new OracleParameter[] //{ // new OracleParameter(":yyyymmdd", date.ToString("yyyyMMdd")), // new OracleParameter(":workcode", "5020"), // new OracleParameter(":v_datebegin", date), // new OracleParameter(":v_dateend", end), // new OracleParameter(":LOGID", 0) //}; //DataTable fhData = oracleConn.GetSqlResultToDt(sqlString, oracleParameter); while (date < end) { dateend = date.AddMonths(1); if (dateend > end) { dateend = end; } OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter(":yyyymmdd",OracleDbType.Varchar2, date.ToString("yyyyMMdd"), ParameterDirection.Input), new OracleParameter(":workcode",OracleDbType.Varchar2, "5000", ParameterDirection.Input), new OracleParameter(":LOGID",OracleDbType.Int32, 0, ParameterDirection.Input), new OracleParameter(":v_datebegin",OracleDbType.Date, date, ParameterDirection.Input), new OracleParameter(":v_dateend",OracleDbType.Date, dateend, ParameterDirection.Input), }; DataTable fpData = oracleConn.GetSqlResultToDt(sqlString, oracleParameter); // 单次传输最大条数 int maxCount = 10000; List sapParameterList = new List(); if (fpData != null && fpData.Rows.Count > 0) { int index = 0; List tableInList = new List(); foreach (DataRow item in fpData.Rows) { if (index >= maxCount) { Zppfm010 sapItem = new Zppfm010(); sapItem.ZSUM = tableInList.Count; sapItem.TABLE_IN = tableInList.ToArray(); sapParameterList.Add(sapItem); index = 0; tableInList.Clear(); } ZSPP110 info110 = new ZSPP110(); // 时间戳 info110.ZSCNU = item["yyyymmdd"].ToString(); // 工厂 info110.WERKS = item["workcode"].ToString(); // 生产条码 info110.ZSCTM = item["barcode"].ToString(); // 包装条码 info110.ZBZTM = item["outcode"].ToString(); // 产品编码 info110.ZCPBM = item["goodscode"].ToString(); // 物料编号 info110.MATNR = item["sapcode"].ToString(); // 包装整板标识 info110.ZBZBS = item["sapflbatchno"].ToString(); // 重新绑定标识 info110.ZCXBD = item["sapfhundoflag"].ToString(); // 销售凭证 //info110.KDAUF = item["ordercode"].ToString(); // 销售凭证项目 info110.KDPOS = "0";//item["orderitem"].ToString(); info110.UZEIT = "00:00:00";//date.ToString("HH:mm:ss"); //info110.ZCODEN = ""; //info110.ZCODEYZM = ""; tableInList.Add(info110); index++; } Zppfm010 sapParameter = new Zppfm010(); sapParameter.ZSUM = tableInList.Count; sapParameter.TABLE_IN = tableInList.ToArray(); sapParameterList.Add(sapParameter); index = 0; tableInList.Clear(); } else { Zppfm010 sapParameter = new Zppfm010(); sapParameter.ZSUM = 0; sapParameter.TABLE_IN = new ZSPP110[sapParameter.ZSUM]; sapParameterList.Add(sapParameter); } int indexP = 0; int ccc = sapParameterList.Count; foreach (Zppfm010 sapParameter in sapParameterList) //for (int i = 80; i < ccc; i++) { //Zppfm010 sapParameter = sapParameterList[i]; ZPPFM010Response result = HGSAPDK_ZPPFM010_800(sapParameter, date.ToString("yyyyMMdd") + " " + (++indexP) + "/" + ccc); //Zppfm010Response result = HGSAPDK_ZPPFM010(sapParameter, "2018 " + (i) + "/" + ccc); sre.Message = result.ZMSG; sre.Result = result.ZTYPE; if (result.ZTYPE != "S") { //foreach (ZSPP110 item in result.TABLE_OUT) //{ // if (item.ZTYPE != "S" && !string.IsNullOrWhiteSpace(item.ZMSG)) // { // OutputLog.TraceLog(LogPriority.Information, // "HGSAPDK_ZPPFM010", JsonHelper.ToJson(item), // "Ztype:" + item.ZTYPE + " Zmsg:" + item.ZMSG, // LocalPath.LogExePath + "SAP_HEGII\\Info_"); // } //} return sre; } } date = dateend; } oracleConn.Commit(); //sre.Message = result.Zmsg; //sre.Result = result.Ztype; return sre; } catch (Exception ex) { throw ex; } finally { if (oracleConn != null && oracleConn.ConnState == ConnectionState.Open) { oracleConn.Disconnect(); } } } /// /// 产量接口-测试 /// /// /// private static ZPPFM008Response HGSAPDK_ZPPFM008_800(Zppfm008 sapParameter, string yyyymmdd, string datacode) { ZPPFM008Response sapResult = null; try { using (ZPPFM008Client sapClient = new ZPPFM008Client("ZPPFM008_BIND_800")) { // 登录 UserNamePasswordClientCredential credential = sapClient.ClientCredentials.UserName; credential.UserName = "hgsapdk"; credential.Password = "Prddk209"; // 参数 sapParameter.ZSUMSpecified = true; sapParameter.TABLE_OUT = new ZSPP100[0]; // 接口 sapResult = sapClient.ZPPFM008(sapParameter); // 结果 OutputLog.TraceLog(LogPriority.Information, "HGSAPDK_ZPPFM008_800", "yyyymmdd:" + yyyymmdd + " datacode:" + datacode + " Zsum:" + sapParameter.ZSUM, "Ztype:" + sapResult.ZTYPE + " Zmsg:" + sapResult.ZMSG, LocalPath.LogExePath + "SAP_HEGII\\Info_"); } } catch (Exception ex) { sapResult = new ZPPFM008Response(); sapResult.ZTYPE = "E"; if (ex is System.ServiceModel.FaultException) { sapResult.ZMSG = ex.Message; } else { sapResult.ZMSG = "DKMES-ERROR:" + Guid.NewGuid(); } OutputLog.TraceLog(LogPriority.Error, "HGSAPDK_ZPPFM008_800", sapResult.ZMSG, ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } return sapResult; } /// /// 成品明细(条码接口)-测试 /// /// private static ZPPFM010Response HGSAPDK_ZPPFM010_800(Zppfm010 sapParameter, string yyyymmdd) { ZPPFM010Response sapResult = null; try { using (ZPPFM010Client sapClient = new ZPPFM010Client("ZPPFM010_BIND_800")) { // 登录 UserNamePasswordClientCredential credential = sapClient.ClientCredentials.UserName; credential.UserName = "hgsapdk"; credential.Password = "Prddk209"; // 参数 sapParameter.ZSUMSpecified = true; sapParameter.TABLE_OUT = new ZSPP110[0]; // 接口 sapResult = sapClient.ZPPFM010(sapParameter); // 结果 OutputLog.TraceLog(LogPriority.Information, "HGSAPDK_ZPPFM010_800", "yyyymmdd:" + yyyymmdd + " Zsum:" + sapParameter.ZSUM, "Ztype:" + sapResult.ZTYPE + " Zmsg:" + sapResult.ZMSG, LocalPath.LogExePath + "SAP_HEGII\\Info_"); if (sapResult.ZTYPE == "S") { // 结果 //OutputLog.TraceLog(LogPriority.Information, // "HGSAPDK_ZPPFM010_800", // "yyyymmdd:" + yyyymmdd + " Zsum:" + sapParameter.ZSUM, // " Ztype:" + sapResult.ZTYPE + " Zmsg:" + sapResult.ZMSG, // LocalPath.LogExePath + "SAP_HEGII\\Info_"); } else { // 结果 OutputLog.TraceLog(LogPriority.Information, "HGSAPDK_ZPPFM010_800", "yyyymmdd:" + yyyymmdd + " Zsum:" + sapParameter.ZSUM + " Ztype:" + sapResult.ZTYPE + " Zmsg:" + sapResult.ZMSG, JsonHelper.ToJson(sapResult), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } } } catch (Exception ex) { sapResult = new ZPPFM010Response(); sapResult.ZTYPE = "E"; if (ex is System.ServiceModel.FaultException) { sapResult.ZMSG = ex.Message; } else { sapResult.ZMSG = "DKMES-ERROR:" + Guid.NewGuid(); } OutputLog.TraceLog(LogPriority.Error, "HGSAPDK_ZPPFM010_800", sapResult.ZMSG, ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } return sapResult; } /// /// 产量接口 /// /// /// public static ZPPFM008Response HGSAPDK_HGSAPDK_ZPPFM008(Zppfm008 sapParameter, string yyyymmdd, string datacode) { ZPPFM008Response sapResult = null; try { using (ZPPFM008Client sapClient = new ZPPFM008Client("ZPPFM008_BIND")) { // 登录 UserNamePasswordClientCredential credential = sapClient.ClientCredentials.UserName; credential.UserName = "hgsapdk"; credential.Password = "Prddk209"; // INIUtility ini = INIUtility.Instance(INIUtility.IniFile.SAP_HEGII); //credential.UserName = ini.ReadIniData("SAP_HEGII", "UserName"); //credential.Password = ini.ReadIniData("SAP_HEGII", "Password"); // 参数 sapParameter.ZSUMSpecified = true; sapParameter.TABLE_OUT = new ZSPP100[0]; // 接口 sapResult = sapClient.ZPPFM008(sapParameter); if (sapResult.ZTYPE == "S") { // 结果 OutputLog.TraceLog(LogPriority.Information, "HGSAPDK_ZPPFM008", "yyyymmdd:" + yyyymmdd + " datacode:" + datacode + " Zsum:" + sapParameter.ZSUM, " Ztype:" + sapResult.ZTYPE + " Zmsg:" + sapResult.ZMSG, LocalPath.LogExePath + "SAP_HEGII\\Info_"); } else { foreach (ZSPP100 item in sapResult.TABLE_OUT) { if (!string.IsNullOrWhiteSpace(item.ZMSG)) { sapResult.ZMSG += $"[{item.ZMSG}]"; break; } } // 结果 OutputLog.TraceLog(LogPriority.Information, "HGSAPDK_ZPPFM008", "yyyymmdd:" + yyyymmdd + " datacode:" + datacode + " Zsum:" + sapParameter.ZSUM + " Ztype:" + sapResult.ZTYPE + " Zmsg:" + sapResult.ZMSG, JsonHelper.ToJson(sapResult), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } } } catch (Exception ex) { sapResult = new ZPPFM008Response(); sapResult.ZTYPE = "E"; if (ex is System.ServiceModel.FaultException) { sapResult.ZMSG = ex.Message; } else { sapResult.ZMSG = "DKMES-ERROR:" + Guid.NewGuid(); } OutputLog.TraceLog(LogPriority.Error, "HGSAPDK_ZPPFM008", sapResult.ZMSG, ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } return sapResult; } /// /// 同步SAP接口 /// /// /// /// /// /// public static ServiceResultEntity HGSAPDK_SyncSap(DateTime date, string datacode, int userid, int logid) { ServiceResultEntity sre = new ServiceResultEntity(); IDBTransaction oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { OracleParameter[] paras = null; string sqlString = string.Empty; sqlString = "select workcode from tp_mst_account where rownum = 1"; string workcode = oracleConn.GetSqlResultToStr(sqlString); string yyyymmdd = "20211130";//date.ToString("yyyyMMdd"); int r = 0; //// 查询出当前日志外，最新的一次日志 //sqlString = "SELECT t.executedateend\n" + // " FROM (SELECT dl.executedateend\n" + // " FROM tsap_hegii_datalog dl\n" + // " WHERE dl.logtype = '3'\n" + // " AND dl.datastuts = 'S'\n" + // " AND dl.datacode = :datacode\n" + // " AND dl.logid <> :logid\n" + // " ORDER BY dl.executedateend DESC) t\n" + // " WHERE rownum = 1"; //paras = new OracleParameter[] //{ // new OracleParameter(":datacode", datacode), // new OracleParameter(":logid", logid) //}; //object executedateend = oracleConn.GetSqlResultToObj(sqlString, paras); //if (executedateend == null || Convert.ToDateTime(executedateend) >= date) //{ // sre.Result = "W"; // sre.Message = "当前日志的结束时间小于最新日志的结束时间，不能同步"; // return sre; //} sqlString = "SELECT wd.yyyymmdd\n" + " ,wd.workcode\n" + " ,wd.datacode\n" + " ,wd.goodscode\n" + " ,wd.sapcode\n" + " ,wd.usercode\n" + " ,wd.ordercode\n" + " ,wd.orderitem\n" + " ,wd.outputnum\n" + " ,wd.scrapnum\n" + " ,wd.cleanupnum\n" + " ,wd.recoverynum\n" + " ,wd.repairnum\n" + " ,wd.testmouldflag\n" + " ,wd.zscs\n" + " FROM tsap_hegii_workdata wd\n" + " WHERE wd.logid = :logid"; paras = new OracleParameter[] { new OracleParameter(":logid", OracleDbType.Int32, logid, ParameterDirection.Input), }; DataTable workData = oracleConn.GetSqlResultToDt(sqlString, paras); Zppfm008 sapParameter = new Zppfm008(); if (workData != null && workData.Rows.Count > 0) { sapParameter.ZSUM = workData.Rows.Count; sapParameter.TABLE_IN = new ZSPP100[sapParameter.ZSUM]; int index = 0; foreach (DataRow item in workData.Rows) { ZSPP100 info100 = new ZSPP100(); // 工厂 info100.WERKS = workcode; // 型号 info100.GROES = item["GoodsCode"].ToString(); // 物料编号 info100.MATNR = item["SAPCode"].ToString(); // 生产工号 info100.ZGHNU = item["UserCode"].ToString(); // 数据节点 info100.ZJDNU = item["DataCode"].ToString(); // 时间戳 info100.ZSCNU = yyyymmdd; // 销售凭证 info100.VBELN = item["ORDERCODE"].ToString().Trim(); // 销售凭证项目 info100.POSNR = item["ORDERITEM"].ToString(); // 产量 info100.ZCLNG = item["OutputNum"].ToString(); // 损坯 info100.ZSPNG = item["SCRAPNUM"].ToString(); // 清除 info100.ZQCNG = item["CLEANUPNUM"].ToString(); // 回收 info100.ZHSNG = item["RECOVERYNUM"].ToString(); // 干补 info100.ZGBNG = item["REPAIRNUM"].ToString(); // 注浆类型 G高压 L普通 info100.ZSCS = item["ZSCS"].ToString(); info100.ZKSSJ = Convert.ToDateTime("2021-12-01 08:00:00").ToString("HHmmss"); info100.ZJSRQ = Convert.ToDecimal("20211201080000"); sapParameter.TABLE_IN[index++] = info100; } } else { sapParameter.ZSUM = 0; sapParameter.TABLE_IN = new ZSPP100[sapParameter.ZSUM]; } // 调用SAP接口(调试用) ZPPFM008Response result = HGSAPDK_HGSAPDK_ZPPFM008(sapParameter, yyyymmdd, datacode); if (result.TABLE_OUT != null && result.TABLE_OUT.Length > 0) { sqlString = "update TSAP_HEGII_WorkData t set t.ZTime =sysdate, ZTYPE = :ZTYPE, ZMSG = :ZMSG where logid = " + logid + " \n and YYYYMMDD='" + yyyymmdd + "' and WorkCode='" + workcode + "' and DataCode=:DataCode and GoodsCode=:GoodsCode and SAPCode=:SAPCode and UserCode=:UserCode \n" + "and OrderCode=:OrderCode and OrderItem=:OrderItem"; foreach (ZSPP100 item in result.TABLE_OUT) { string posnr = item.POSNR.TrimStart('0'); paras = new OracleParameter[] { new OracleParameter(":ZTYPE",OracleDbType.Varchar2, item.ZTYPE, ParameterDirection.Input), new OracleParameter(":ZMSG",OracleDbType.Varchar2, item.ZMSG, ParameterDirection.Input), new OracleParameter(":DataCode",OracleDbType.Varchar2, datacode, ParameterDirection.Input), new OracleParameter(":GoodsCode",OracleDbType.Varchar2, item.GROES, ParameterDirection.Input), new OracleParameter(":SAPCode",OracleDbType.Varchar2, item.MATNR, ParameterDirection.Input), new OracleParameter(":UserCode",OracleDbType.Varchar2, item.ZGHNU, ParameterDirection.Input), new OracleParameter(":OrderCode",OracleDbType.Varchar2, (string.IsNullOrEmpty(item.VBELN) ? " " : item.VBELN), ParameterDirection.Input), new OracleParameter(":OrderItem",OracleDbType.Varchar2, ((string.IsNullOrEmpty(posnr)) ? "0" : posnr), ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, paras); } } result.ZMSG = $"{result.ZMSG}({sapParameter.ZSUM})"; sqlString = "update tsap_hegii_datalog t set t.EndTime = sysdate, DataStuts = :DataStuts, DataMSG =:msg where logid = :logid"; paras = new OracleParameter[] { new OracleParameter(":DataStuts",OracleDbType.Varchar2, result.ZTYPE, ParameterDirection.Input), new OracleParameter(":msg",OracleDbType.Varchar2, result.ZMSG, ParameterDirection.Input), new OracleParameter(":logid",OracleDbType.Int32, logid, ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, paras); if (result.ZTYPE == "S") { sqlString = "update tsap_hegii_datalog t set t.createuserid = :userid where logid = :logid"; paras = new OracleParameter[] { new OracleParameter(":logid",OracleDbType.Int32, logid, ParameterDirection.Input), new OracleParameter(":userid",OracleDbType.Int32, userid, ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, paras); } oracleConn.Commit(); sre.Message = result.ZMSG; sre.Result = result.ZTYPE; return sre; } catch (Exception ex) { throw ex; } finally { if (oracleConn != null && oracleConn.ConnState == ConnectionState.Open) { oracleConn.Disconnect(); } } } #endregion /// /// 同步SAP数据（自动） /// /// public static void AutoWorkDataToSAP(DateTime date, string funCode) { if (string.IsNullOrWhiteSpace(funCode)) { //return; funCode = "ALL"; } funCode = "," + funCode + ","; ServiceResultEntity sre = null; // 10 模具 if (funCode == ",ALL," || funCode.Contains(",10,")) { try { sre = SetWorkData10_50(date, "10", 0); if (sre.Status != Constant.ServiceResultStatus.Success || "S" != sre.Result + "") { OutputLog.TraceLog(LogPriority.Warning, "AutoWorkDataToSAP", "10 模具 " + date.ToString("yyyy-MM-dd HH:mm:ss"), JsonHelper.ToJson(sre), LocalPath.LogExePath + "SAP_HEGII\\Warn_"); } } catch (Exception ex) { OutputLog.TraceLog(LogPriority.Error, "AutoWorkDataToSAP", "10 模具 " + date.ToString("yyyy-MM-dd HH:mm:ss"), ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } } // 20 湿坯 if (funCode == ",ALL," || funCode.Contains(",20,")) { try { sre = SetWorkData10_50(date, "20", 0); if (sre.Status != Constant.ServiceResultStatus.Success || "S" != sre.Result + "") { OutputLog.TraceLog(LogPriority.Warning, "AutoWorkDataToSAP", "20 湿坯 " + date.ToString("yyyy-MM-dd HH:mm:ss"), JsonHelper.ToJson(sre), LocalPath.LogExePath + "SAP_HEGII\\Warn_"); } } catch (Exception ex) { OutputLog.TraceLog(LogPriority.Error, "AutoWorkDataToSAP", "20 湿坯 " + date.ToString("yyyy-MM-dd HH:mm:ss"), ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } } // 30 精坯 if (funCode == ",ALL," || funCode.Contains(",30,")) { try { sre = SetWorkData10_50(date, "30", 0); if (sre.Status != Constant.ServiceResultStatus.Success || "S" != sre.Result + "") { OutputLog.TraceLog(LogPriority.Warning, "AutoWorkDataToSAP", "30 精坯 " + date.ToString("yyyy-MM-dd HH:mm:ss"), JsonHelper.ToJson(sre), LocalPath.LogExePath + "SAP_HEGII\\Warn_"); } } catch (Exception ex) { OutputLog.TraceLog(LogPriority.Error, "AutoWorkDataToSAP", "30 精坯 " + date.ToString("yyyy-MM-dd HH:mm:ss"), ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } } // 40 釉坯 if (funCode == ",ALL," || funCode.Contains(",40,")) { try { sre = SetWorkData10_50(date, "40", 0); if (sre.Status != Constant.ServiceResultStatus.Success || "S" != sre.Result + "") { OutputLog.TraceLog(LogPriority.Warning, "AutoWorkDataToSAP", "40 釉坯 " + date.ToString("yyyy-MM-dd HH:mm:ss"), JsonHelper.ToJson(sre), LocalPath.LogExePath + "SAP_HEGII\\Warn_"); } } catch (Exception ex) { OutputLog.TraceLog(LogPriority.Error, "AutoWorkDataToSAP", "40 釉坯 " + date.ToString("yyyy-MM-dd HH:mm:ss"), ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } } // 50 烧成 if (funCode == ",ALL," || funCode.Contains(",50,")) { try { sre = SetWorkData10_50(date, "50", 0); if (sre.Status != Constant.ServiceResultStatus.Success || "S" != sre.Result + "") { OutputLog.TraceLog(LogPriority.Warning, "AutoWorkDataToSAP", "50 烧成 " + date.ToString("yyyy-MM-dd HH:mm:ss"), JsonHelper.ToJson(sre), LocalPath.LogExePath + "SAP_HEGII\\Warn_"); } } catch (Exception ex) { OutputLog.TraceLog(LogPriority.Error, "AutoWorkDataToSAP", "50 烧成 " + date.ToString("yyyy-MM-dd HH:mm:ss"), ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } } //// 6001 成品明细 //if (funCode == ",ALL," || funCode.Contains(",6001,")) //{ // try // { // sre = SetFP6001(date, 0); // if (sre.Status != Constant.ServiceResultStatus.Success || // "S" != sre.Result + "") // { // OutputLog.TraceLog(LogPriority.Warning, // "AutoWorkDataToSAP", // "6001 成品明细 " + date.ToString("yyyy-MM-dd HH:mm:ss"), // JsonHelper.ToJson(sre), // LocalPath.LogExePath + "SAP_HEGII\\Warn_"); // } // } // catch (Exception ex) // { // OutputLog.TraceLog(LogPriority.Error, // "AutoWorkDataToSAP", // "6001 成品明细 " + date.ToString("yyyy-MM-dd HH:mm:ss"), // ex.ToString(), // LocalPath.LogExePath + "SAP_HEGII\\Error_"); // } //} //// 6001 成品明细(小时)-20分钟 //if (funCode == ",6002,") //{ // try // { // sre = SetFP6002(date, 0); // if (sre.Status != Constant.ServiceResultStatus.Success || // "S" != sre.Result + "") // { // OutputLog.TraceLog(LogPriority.Warning, // "AutoWorkDataToSAP", // "6002 成品明细(小时) " + date.ToString("yyyy-MM-dd HH:mm:ss"), // JsonHelper.ToJson(sre), // LocalPath.LogExePath + "SAP_HEGII\\Warn_"); // } // } // catch (Exception ex) // { // OutputLog.TraceLog(LogPriority.Error, // "AutoWorkDataToSAP", // "6002 成品明细(小时) " + date.ToString("yyyy-MM-dd HH:mm:ss"), // ex.ToString(), // LocalPath.LogExePath + "SAP_HEGII\\Error_"); // } //} } /// /// 同步SAP数据（自动）(重载) /// /// 当前时间 /// 工序码 /// 本次要执行到的时间 public static void AutoWorkDataToSAP(DateTime date, string funCode, DateTime ndate) { if (string.IsNullOrWhiteSpace(funCode)) { //return; funCode = "ALL"; } funCode = "," + funCode + ","; ServiceResultEntity sre = null; // 10 模具 if (funCode == ",ALL," || funCode.Contains(",10,")) { try { sre = SetWorkData10_50("10", 0, ndate); if (sre.Status != Constant.ServiceResultStatus.Success || "S" != sre.Result + "") { OutputLog.TraceLog(LogPriority.Warning, "AutoWorkDataToSAP", "10 模具 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), JsonHelper.ToJson(sre), LocalPath.LogExePath + "SAP_HEGII\\Warn_"); } } catch (Exception ex) { OutputLog.TraceLog(LogPriority.Error, "AutoWorkDataToSAP", "10 模具 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } } // 20 湿坯 if (funCode == ",ALL," || funCode.Contains(",20,")) { try { sre = SetWorkData10_50("20", 0, ndate); if (sre.Status != Constant.ServiceResultStatus.Success || "S" != sre.Result + "") { OutputLog.TraceLog(LogPriority.Warning, "AutoWorkDataToSAP", "20 湿坯 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), JsonHelper.ToJson(sre), LocalPath.LogExePath + "SAP_HEGII\\Warn_"); } } catch (Exception ex) { OutputLog.TraceLog(LogPriority.Error, "AutoWorkDataToSAP", "20 湿坯 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } } // 30 精坯 if (funCode == ",ALL," || funCode.Contains(",30,")) { try { sre = SetWorkData10_50("30", 0, ndate); if (sre.Status != Constant.ServiceResultStatus.Success || "S" != sre.Result + "") { OutputLog.TraceLog(LogPriority.Warning, "AutoWorkDataToSAP", "30 精坯 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), JsonHelper.ToJson(sre), LocalPath.LogExePath + "SAP_HEGII\\Warn_"); } } catch (Exception ex) { OutputLog.TraceLog(LogPriority.Error, "AutoWorkDataToSAP", "30 精坯 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } } // 40 釉坯 if (funCode == ",ALL," || funCode.Contains(",40,")) { try { sre = SetWorkData10_50("40", 0, ndate); if (sre.Status != Constant.ServiceResultStatus.Success || "S" != sre.Result + "") { OutputLog.TraceLog(LogPriority.Warning, "AutoWorkDataToSAP", "40 釉坯 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), JsonHelper.ToJson(sre), LocalPath.LogExePath + "SAP_HEGII\\Warn_"); } } catch (Exception ex) { OutputLog.TraceLog(LogPriority.Error, "AutoWorkDataToSAP", "40 釉坯 " + date.ToString("yyyy-MM-dd HH:mm:ss"), ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } } // 50 烧成 if (funCode == ",ALL," || funCode.Contains(",50,")) { try { sre = SetWorkData10_50("50", 0, ndate); if (sre.Status != Constant.ServiceResultStatus.Success || "S" != sre.Result + "") { OutputLog.TraceLog(LogPriority.Warning, "AutoWorkDataToSAP", "50 烧成 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), JsonHelper.ToJson(sre), LocalPath.LogExePath + "SAP_HEGII\\Warn_"); } } catch (Exception ex) { OutputLog.TraceLog(LogPriority.Error, "AutoWorkDataToSAP", "50 烧成 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } } //// 6001 成品明细 //if (funCode == ",ALL," || funCode.Contains(",6001,")) //{ // try // { // sre = SetFP6001(date, 0); // if (sre.Status != Constant.ServiceResultStatus.Success || // "S" != sre.Result + "") // { // OutputLog.TraceLog(LogPriority.Warning, // "AutoWorkDataToSAP", // "6001 成品明细 " + date.ToString("yyyy-MM-dd HH:mm:ss"), // JsonHelper.ToJson(sre), // LocalPath.LogExePath + "SAP_HEGII\\Warn_"); // } // } // catch (Exception ex) // { // OutputLog.TraceLog(LogPriority.Error, // "AutoWorkDataToSAP", // "6001 成品明细 " + date.ToString("yyyy-MM-dd HH:mm:ss"), // ex.ToString(), // LocalPath.LogExePath + "SAP_HEGII\\Error_"); // } //} //// 6001 成品明细(小时)-20分钟 //if (funCode == ",6002,") //{ // try // { // sre = SetFP6002(date, 0); // if (sre.Status != Constant.ServiceResultStatus.Success || // "S" != sre.Result + "") // { // OutputLog.TraceLog(LogPriority.Warning, // "AutoWorkDataToSAP", // "6002 成品明细(小时) " + date.ToString("yyyy-MM-dd HH:mm:ss"), // JsonHelper.ToJson(sre), // LocalPath.LogExePath + "SAP_HEGII\\Warn_"); // } // } // catch (Exception ex) // { // OutputLog.TraceLog(LogPriority.Error, // "AutoWorkDataToSAP", // "6002 成品明细(小时) " + date.ToString("yyyy-MM-dd HH:mm:ss"), // ex.ToString(), // LocalPath.LogExePath + "SAP_HEGII\\Error_"); // } //} } public static void AutoWorkDataToSAP_Test(string funCode, DateTime ndate) { if (string.IsNullOrWhiteSpace(funCode)) { //return; funCode = "ALL"; } funCode = "," + funCode + ","; ServiceResultEntity sre = null; // 10 模具 //if (funCode == ",ALL," || funCode.Contains(",10,")) //{ // try // { // sre = SetWorkData10_50_test("10", 0, ndate); // if (sre.Status != Constant.ServiceResultStatus.Success || // "S" != sre.Result + "") // { // OutputLog.TraceLog(LogPriority.Warning, // "AutoWorkDataToSAP", // "10 模具 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), // JsonHelper.ToJson(sre), // LocalPath.LogExePath + "SAP_HEGII\\Warn_"); // } // } // catch (Exception ex) // { // OutputLog.TraceLog(LogPriority.Error, // "AutoWorkDataToSAP", // "10 模具 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), // ex.ToString(), // LocalPath.LogExePath + "SAP_HEGII\\Error_"); // } //} // 20 湿坯 if (funCode == ",ALL," || funCode.Contains(",20,")) { try { sre = SetWorkData10_50_test("20", 0, ndate); if (sre.Status != Constant.ServiceResultStatus.Success || "S" != sre.Result + "") { OutputLog.TraceLog(LogPriority.Warning, "AutoWorkDataToSAP", "20 湿坯 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), JsonHelper.ToJson(sre), LocalPath.LogExePath + "SAP_HEGII\\Warn_"); } } catch (Exception ex) { OutputLog.TraceLog(LogPriority.Error, "AutoWorkDataToSAP", "20 湿坯 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } } // 30 精坯 if (funCode == ",ALL," || funCode.Contains(",30,")) { try { sre = SetWorkData10_50_test("30", 0, ndate); if (sre.Status != Constant.ServiceResultStatus.Success || "S" != sre.Result + "") { OutputLog.TraceLog(LogPriority.Warning, "AutoWorkDataToSAP", "30 精坯 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), JsonHelper.ToJson(sre), LocalPath.LogExePath + "SAP_HEGII\\Warn_"); } } catch (Exception ex) { OutputLog.TraceLog(LogPriority.Error, "AutoWorkDataToSAP", "30 精坯 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } } // 40 釉坯 if (funCode == ",ALL," || funCode.Contains(",40,")) { try { sre = SetWorkData10_50_test("40", 0, ndate); if (sre.Status != Constant.ServiceResultStatus.Success || "S" != sre.Result + "") { OutputLog.TraceLog(LogPriority.Warning, "AutoWorkDataToSAP", "40 釉坯 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), JsonHelper.ToJson(sre), LocalPath.LogExePath + "SAP_HEGII\\Warn_"); } } catch (Exception ex) { OutputLog.TraceLog(LogPriority.Error, "AutoWorkDataToSAP", "40 釉坯 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } } // 50 烧成 if (funCode == ",ALL," || funCode.Contains(",50,")) { try { sre = SetWorkData10_50_test("50", 0, ndate); if (sre.Status != Constant.ServiceResultStatus.Success || "S" != sre.Result + "") { OutputLog.TraceLog(LogPriority.Warning, "AutoWorkDataToSAP", "50 烧成 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), JsonHelper.ToJson(sre), LocalPath.LogExePath + "SAP_HEGII\\Warn_"); } } catch (Exception ex) { OutputLog.TraceLog(LogPriority.Error, "AutoWorkDataToSAP", "50 烧成 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), ex.ToString(), LocalPath.LogExePath + "SAP_HEGII\\Error_"); } } // 6001 成品明细 //if (funCode == ",ALL," || funCode.Contains(",6001,")) //{ // try // { // sre = SetFP6001(ndate, 0); // if (sre.Status != Constant.ServiceResultStatus.Success || // "S" != sre.Result + "") // { // OutputLog.TraceLog(LogPriority.Warning, // "AutoWorkDataToSAP", // "6001 成品明细 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), // JsonHelper.ToJson(sre), // LocalPath.LogExePath + "SAP_HEGII\\Warn_"); // } // } // catch (Exception ex) // { // OutputLog.TraceLog(LogPriority.Error, // "AutoWorkDataToSAP", // "6001 成品明细 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), // ex.ToString(), // LocalPath.LogExePath + "SAP_HEGII\\Error_"); // } //} // 6001 成品明细(小时)-20分钟 //if (funCode == ",6002,") //{ // try // { // sre = SetFP6002(ndate, 0); // if (sre.Status != Constant.ServiceResultStatus.Success || // "S" != sre.Result + "") // { // OutputLog.TraceLog(LogPriority.Warning, // "AutoWorkDataToSAP", // "6002 成品明细(小时) " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), // JsonHelper.ToJson(sre), // LocalPath.LogExePath + "SAP_HEGII\\Warn_"); // } // } // catch (Exception ex) // { // OutputLog.TraceLog(LogPriority.Error, // "AutoWorkDataToSAP", // "6002 成品明细(小时) " + ndate.ToString("yyyy-MM-dd HH:mm:ss"), // ex.ToString(), // LocalPath.LogExePath + "SAP_HEGII\\Error_"); // } //} } public static ServiceResultEntity SetWorkData10_50_test(string datacode, int userid, DateTime ndate) { ServiceResultEntity sre = new ServiceResultEntity(); IDBTransaction oracleConn = null; try { #region 事务1，执行存储过程 OracleParameter[] paras = null; int logid = 0; string message = string.Empty; // 10 模具 //if ("10".Equals(datacode)) //{ // paras = new OracleParameter[] // { // new OracleParameter("in_dateend", OracleDbType.Date, ndate, ParameterDirection.Input), // new OracleParameter("out_logid", OracleDbType.Int32, null, ParameterDirection.Output), // new OracleParameter("out_msg", OracleDbType.NVarchar2, 500, null, ParameterDirection.Output) // }; // oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); // DataSet ds = oracleConn.ExecStoredProcedure("pro_sap_hegii_workdata_10_1", paras); // int.TryParse(paras[1].Value + "", out logid); // message = paras[2].Value + ""; // oracleConn.Commit(); //} // 20 湿坯 if ("20".Equals(datacode)) { paras = new OracleParameter[] { new OracleParameter("in_dateend", OracleDbType.Date, ndate, ParameterDirection.Input), new OracleParameter("out_logid", OracleDbType.Int32, null, ParameterDirection.Output), new OracleParameter("out_msg", OracleDbType.NVarchar2, 500, null, ParameterDirection.Output) }; oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); DataSet ds = oracleConn.ExecStoredProcedure("pro_sap_hegii_workdata_20_1_test", paras); int.TryParse(paras[1].Value + "", out logid); message = paras[2].Value + ""; oracleConn.Commit(); } // 30 精坯、40 釉坯、50 烧成 else if ("30".Equals(datacode) || "40".Equals(datacode) || "50".Equals(datacode)) { paras = new OracleParameter[] { new OracleParameter("in_dateend", OracleDbType.Date, ndate, ParameterDirection.Input), new OracleParameter("in_datacode", OracleDbType.Varchar2, datacode, ParameterDirection.Input), new OracleParameter("out_logid", OracleDbType.Int32, null, ParameterDirection.Output), new OracleParameter("out_msg", OracleDbType.NVarchar2, 500, null, ParameterDirection.Output) }; oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); DataSet ds = oracleConn.ExecStoredProcedure("pro_sap_hegii_workdata_30_1_test", paras); int.TryParse(paras[2].Value + "", out logid); message = paras[3].Value + ""; oracleConn.Commit(); } // 如果logid为0，则数据没有生成 if (logid == 0) { sre.Status = Constant.ServiceResultStatus.Other; sre.Message = message; return sre; } #endregion // 事物2，同步SAP接口 sre = SyncSap_test(ndate, datacode, userid, logid); return sre; } catch (Exception ex) { throw ex; } finally { if (oracleConn != null && oracleConn.ConnState == ConnectionState.Open) { oracleConn.Disconnect(); } } } public static ServiceResultEntity SyncSap_test(DateTime date, string datacode, int userid, int logid) { ServiceResultEntity sre = new ServiceResultEntity(); IDBTransaction oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { OracleParameter[] paras = null; string sqlString = string.Empty; sqlString = "select workcode from tp_mst_account where rownum = 1"; string workcode = oracleConn.GetSqlResultToStr(sqlString); string yyyymmdd = date.ToString("yyyyMMdd"); int r = 0; // 查询出当前日志外，最新的一次日志 sqlString = "SELECT t.executedateend\n" + " FROM (SELECT dl.executedateend\n" + " FROM tsap_hegii_datalog dl\n" + " WHERE dl.logtype = '3'\n" + " AND dl.datastuts = 'S'\n" + " AND dl.datacode = :datacode\n" + " AND dl.logid <> :logid\n" + " ORDER BY dl.executedateend DESC) t\n" + " WHERE rownum = 1"; paras = new OracleParameter[] { new OracleParameter(":datacode", datacode), new OracleParameter(":logid", logid) }; object executedateend = oracleConn.GetSqlResultToObj(sqlString, paras); //if (executedateend == null || Convert.ToDateTime(executedateend) >= date) //{ // sre.Result = "W"; // sre.Message = "当前日志的结束时间小于最新日志的结束时间，不能同步"; // return sre; //} sqlString = "SELECT wd.yyyymmdd\n" + " ,wd.workcode\n" + " ,wd.datacode\n" + " ,wd.goodscode\n" + " ,wd.sapcode\n" + " ,wd.usercode\n" + " ,wd.ordercode\n" + " ,wd.orderitem\n" + " ,wd.outputnum\n" + " ,wd.scrapnum\n" + " ,wd.cleanupnum\n" + " ,wd.recoverynum\n" + " ,wd.repairnum\n" + " ,wd.testmouldflag\n" + " ,wd.zscs\n" + " FROM tsap_hegii_workdata wd\n" + " WHERE wd.logid = :logid"; paras = new OracleParameter[] { new OracleParameter(":logid", OracleDbType.Int32, logid, ParameterDirection.Input), }; DataTable workData = oracleConn.GetSqlResultToDt(sqlString, paras); Zppfm008 sapParameter = new Zppfm008(); if (workData != null && workData.Rows.Count > 0) { sapParameter.ZSUM = workData.Rows.Count; sapParameter.TABLE_IN = new ZSPP100[sapParameter.ZSUM]; int index = 0; foreach (DataRow item in workData.Rows) { ZSPP100 info100 = new ZSPP100(); // 工厂 info100.WERKS = workcode; // 型号 info100.GROES = item["GoodsCode"].ToString(); // 物料编号 info100.MATNR = item["SAPCode"].ToString(); // 生产工号 info100.ZGHNU = item["UserCode"].ToString(); // 数据节点 info100.ZJDNU = item["DataCode"].ToString(); // 时间戳 info100.ZSCNU = yyyymmdd; // 销售凭证 info100.VBELN = item["ORDERCODE"].ToString().Trim(); // 销售凭证项目 info100.POSNR = item["ORDERITEM"].ToString(); // 产量 info100.ZCLNG = item["OutputNum"].ToString(); // 损坯 info100.ZSPNG = item["SCRAPNUM"].ToString(); // 清除 info100.ZQCNG = item["CLEANUPNUM"].ToString(); // 回收 info100.ZHSNG = item["RECOVERYNUM"].ToString(); // 干补 info100.ZGBNG = item["REPAIRNUM"].ToString(); // 注浆类型 G高压 L普通 info100.ZSCS = item["ZSCS"].ToString(); //info100.ZKSSJ = Convert.ToDateTime(executedateend).ToString("HHmmss"); info100.ZKSSJ = Convert.ToDateTime("2022-06-24 12:00:00").ToString("HHmmss"); info100.ZJSRQ = Convert.ToDecimal(date.ToString("yyyyMMddHHmmss")); sapParameter.TABLE_IN[index++] = info100; } } else { sapParameter.ZSUM = 0; sapParameter.TABLE_IN = new ZSPP100[sapParameter.ZSUM]; } // 调用SAP接口 ZPPFM008Response result = HGSAPDK_ZPPFM008(sapParameter, yyyymmdd, datacode); if (result.TABLE_OUT != null && result.TABLE_OUT.Length > 0) { sqlString = "update TSAP_HEGII_WorkData t set t.ZTime =sysdate, ZTYPE = :ZTYPE, ZMSG = :ZMSG where logid = " + logid + " \n and YYYYMMDD='" + yyyymmdd + "' and WorkCode='" + workcode + "' and DataCode=:DataCode and GoodsCode=:GoodsCode and SAPCode=:SAPCode and UserCode=:UserCode \n" + "and OrderCode=:OrderCode and OrderItem=:OrderItem"; foreach (ZSPP100 item in result.TABLE_OUT) { string posnr = item.POSNR.TrimStart('0'); paras = new OracleParameter[] { new OracleParameter(":ZTYPE",OracleDbType.Varchar2, item.ZTYPE, ParameterDirection.Input), new OracleParameter(":ZMSG",OracleDbType.Varchar2, item.ZMSG, ParameterDirection.Input), new OracleParameter(":DataCode",OracleDbType.Varchar2, datacode, ParameterDirection.Input), new OracleParameter(":GoodsCode",OracleDbType.Varchar2, item.GROES, ParameterDirection.Input), new OracleParameter(":SAPCode",OracleDbType.Varchar2, item.MATNR, ParameterDirection.Input), new OracleParameter(":UserCode",OracleDbType.Varchar2, item.ZGHNU, ParameterDirection.Input), new OracleParameter(":OrderCode",OracleDbType.Varchar2, (string.IsNullOrEmpty(item.VBELN) ? " " : item.VBELN), ParameterDirection.Input), new OracleParameter(":OrderItem",OracleDbType.Varchar2, ((string.IsNullOrEmpty(posnr)) ? "0" : posnr), ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, paras); } } result.ZMSG = $"{result.ZMSG}({sapParameter.ZSUM})"; sqlString = "update tsap_hegii_datalog t set t.EndTime = sysdate, DataStuts = :DataStuts, DataMSG =:msg where logid = :logid"; paras = new OracleParameter[] { new OracleParameter(":DataStuts",OracleDbType.Varchar2, result.ZTYPE, ParameterDirection.Input), new OracleParameter(":msg",OracleDbType.Varchar2, result.ZMSG, ParameterDirection.Input), new OracleParameter(":logid",OracleDbType.Int32, logid, ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, paras); if (result.ZTYPE == "S") { sqlString = "update tsap_hegii_datalog t set t.createuserid = :userid where logid = :logid"; paras = new OracleParameter[] { new OracleParameter(":logid",OracleDbType.Int32, logid, ParameterDirection.Input), new OracleParameter(":userid",OracleDbType.Int32, userid, ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, paras); } oracleConn.Commit(); sre.Message = result.ZMSG; sre.Result = result.ZTYPE; return sre; } catch (Exception ex) { throw ex; } finally { if (oracleConn != null && oracleConn.ConnState == ConnectionState.Open) { oracleConn.Disconnect(); } } } /* public static ServiceResultEntity AutoWorkData(DateTime date) { IDBTransaction oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity sre = new ServiceResultEntity(); try { string yyyymmdd = date.ToString("yyyyMMdd"); string sqlString = "select t.logid, t.datacode\n" + " from tsap_hegii_datalog t\n" + " where t.logtype = '1'\n" + " and t.datastuts = 'S'\n" + " and t.yyyymmdd = :yyyymmdd\n" + " and t.createuserid is null for update"; OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter(":yyyymmdd",OracleDbType.Varchar2, yyyymmdd, ParameterDirection.Input), }; DataTable datalog = oracleConn.GetSqlResultToDt(sqlString, oracleParameter); if (datalog == null || datalog.Rows.Count == 0) { sre.Result = 0; return sre; } sqlString = "select workcode from tp_mst_account where rownum = 1"; string workcode = oracleConn.GetSqlResultToStr(sqlString); Zppfm008 sapParameter = new Zppfm008(); List info = new List(); foreach (DataRow item in datalog.Rows) { string datacode = item["datacode"].ToString(); logid = oracleConn.GetSqlResultToStr("select SEQ_SAP_HEGII_DataLog_ID.Nextval from dual"); string msg = null; //string msg = "data:" + datebegin.ToString("yyyyMMdd-HHm") + "~" + // dateend.ToString("yyyyMMdd-HHmm"); sqlString = "insert into tsap_hegii_datalog\n" + " (LogID\n" + " ,LogType\n" + " ,BeginTime\n" + " ,YYYYMMDD\n" + " ,WorkCode\n" + " ,DataCode\n" + " ,DataStuts\n" + " ,DataMSG\n" + " ,CreateUserID\n" + " ,DataLogID)\n" + "values\n" + " (:LogID\n" + " ,'2'\n" + " ,sysdate\n" + " ,:YYYYMMDD\n" + " ,:WorkCode\n" + " ,:DataCode\n" + " ,:DataStuts\n" + " ,:DataMSG\n" + " ,:CreateUserID\n" + " ,:DataLogID)"; int r = 0; if (string.IsNullOrEmpty(datalogid)) { oracleParameter = new OracleParameter[] { new OracleParameter(":LogID",OracleDbType.Int32, logid, ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userid, ParameterDirection.Input), new OracleParameter(":YYYYMMDD",OracleDbType.Varchar2, yyyymmdd, ParameterDirection.Input), new OracleParameter(":WorkCode",OracleDbType.Varchar2, workcode, ParameterDirection.Input), new OracleParameter(":DataCode",OracleDbType.Varchar2, datacode, ParameterDirection.Input), new OracleParameter(":DataMSG",OracleDbType.Varchar2, msg + " DKMES-ERROR：NO DATA", ParameterDirection.Input), new OracleParameter(":DataStuts",OracleDbType.Varchar2, "E", ParameterDirection.Input), new OracleParameter(":DataLogID",OracleDbType.Int32, 0, ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); sre.Status = Constant.ServiceResultStatus.Other; sre.Message = "没有同步数据"; sre.Result = "E"; return sre; } oracleParameter = new OracleParameter[] { new OracleParameter(":LogID",OracleDbType.Int32, logid, ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userid, ParameterDirection.Input), new OracleParameter(":YYYYMMDD",OracleDbType.Varchar2, yyyymmdd, ParameterDirection.Input), new OracleParameter(":WorkCode",OracleDbType.Varchar2, workcode, ParameterDirection.Input), new OracleParameter(":DataCode",OracleDbType.Varchar2, datacode, ParameterDirection.Input), new OracleParameter(":DataMSG",OracleDbType.Varchar2, msg, ParameterDirection.Input), new OracleParameter(":DataStuts",OracleDbType.Varchar2, "S", ParameterDirection.Input), new OracleParameter(":DataLogID",OracleDbType.Int32, datalogid, ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); sqlString = "select fp.yyyymmdd\n" + " ,fp.workcode\n" + " ,fp.DataCode\n" + " ,fp.GoodsCode\n" + " ,fp.SAPCode\n" + " ,fp.UserCode\n" + " ,fp.ORDERCODE\n" + " ,fp.ORDERITEM\n" + " ,fp.OutputNum\n" + " ,fp.ScrapNum\n" + " ,fp.CleanupNum\n" + " ,fp.RecoveryNum\n" + " ,fp.RepairNum\n" + " from tsap_hegii_workdata wd where wd.logid = :datalogid"; oracleParameter = new OracleParameter[] { new OracleParameter(":datalogid",OracleDbType.Int32, datalogid, ParameterDirection.Input), }; DataTable workData = oracleConn.GetSqlResultToDt(sqlString, oracleParameter); if (workData != null && workData.Rows.Count > 0) { sapParameter.Zsum = workData.Rows.Count; sapParameter.TableIn = new Zspp100[sapParameter.Zsum]; int index = 0; foreach (DataRow item in workData.Rows) { Zspp100 info100 = new Zspp100(); // 工厂 info100.Werks = workcode; // 型号 info100.Groes = item["GoodsCode"].ToString(); // 物料编号 info100.Matnr = item["SAPCode"].ToString(); // 生产工号 info100.Zghnu = item["UserCode"].ToString(); // 数据节点 info100.Zjdnu = item["DataCode"].ToString(); // 时间戳 info100.Zscnu = yyyymmdd; // 销售凭证 info100.Vbeln = item["ORDERCODE"].ToString(); // 销售凭证项目 info100.Posnr = item["ORDERITEM"].ToString(); // 产量 info100.Zclng = item["OutputNum"].ToString(); // 损坯 info100.Zspng = item["SCRAPNUM"].ToString(); // 清除 info100.Zqcng = item["CLEANUPNUM"].ToString(); // 回收 info100.Zhsng = item["RECOVERYNUM"].ToString(); // 干补 info100.Zgbng = item["REPAIRNUM"].ToString(); sapParameter.TableIn[index++] = info100; } } else { sapParameter.Zsum = 0; sapParameter.TableIn = new Zspp100[sapParameter.Zsum]; } Zppfm008Response result = HGSAPDK_ZPPFM008(sapParameter, yyyymmdd, datacode); //if (result.Ztype == "E") //{ // sqlString = "update tsap_hegii_datalog t set t.EndTime = sysdate, DataStuts = 'E', DataMSG = DataMSG||:msg where logid = :logid"; // oracleParameter = new OracleParameter[] // { // new OracleParameter(":msg",OracleDbType.Varchar2, result.Zmsg, ParameterDirection.Input), // new OracleParameter(":logid",OracleDbType.Int32, logid, ParameterDirection.Input), // }; // r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); //} //else { sqlString = "update tsap_hegii_datalog t set t.EndTime = sysdate, DataStuts = :DataStuts, DataMSG = DataMSG||:msg where logid = :logid"; oracleParameter = new OracleParameter[] { new OracleParameter(":DataStuts",OracleDbType.Varchar2, result.Ztype, ParameterDirection.Input), new OracleParameter(":msg",OracleDbType.Varchar2, result.Zmsg, ParameterDirection.Input), new OracleParameter(":logid",OracleDbType.Int32, logid, ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); if (result.Ztype == "S") { sqlString = "update tsap_hegii_datalog t set t.createtime = :userid where logid = :datalogid"; oracleParameter = new OracleParameter[] { new OracleParameter(":datalogid",OracleDbType.Int32, datalogid, ParameterDirection.Input), new OracleParameter(":userid",OracleDbType.Int32, userid, ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); } if (result.TableOut != null && result.TableOut.Length > 0) { sqlString = "update TSAP_HEGII_WorkData t set t.ZTime =sysdate, ZTYPE = :ZTYPE, ZMSG = :ZMSG where logid = " + logid + " \n and YYYYMMDD='" + yyyymmdd + "' and WorkCode='" + workcode + "' and DataCode=:DataCode and GoodsCode=:GoodsCode and SAPCode=:SAPCode and UserCode=:UserCode \n" + "and OrderCode=:OrderCode and OrderItem=:OrderItem"; foreach (Zspp100 item in result.TableOut) { oracleParameter = new OracleParameter[] { new OracleParameter(":ZTYPE",OracleDbType.Varchar2, item.Ztype, ParameterDirection.Input), new OracleParameter(":ZMSG",OracleDbType.Varchar2, item.Zmsg, ParameterDirection.Input), new OracleParameter(":DataCode",OracleDbType.Varchar2, datacode, ParameterDirection.Input), new OracleParameter(":GoodsCode",OracleDbType.Varchar2, item.Groes, ParameterDirection.Input), new OracleParameter(":SAPCode",OracleDbType.Varchar2, item.Matnr, ParameterDirection.Input), new OracleParameter(":UserCode",OracleDbType.Varchar2, item.Zghnu, ParameterDirection.Input), new OracleParameter(":OrderCode",OracleDbType.Varchar2, (string.IsNullOrEmpty(item.Vbeln)? " " : item.Vbeln), ParameterDirection.Input), new OracleParameter(":OrderItem",OracleDbType.Varchar2, (string.IsNullOrEmpty(item.Posnr)? " " : item.Posnr), ParameterDirection.Input), }; r = oracleConn.ExecuteNonQuery(sqlString, oracleParameter); } } } oracleConn.Commit(); sre.Message = result.Zmsg; sre.Result = result.Ztype; return sre; } catch (Exception ex) { throw ex; } } */ } }