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.DataModels;
using Dongke.IBOSS.PRD.Service.SAPHegiiDataService.HGSAPDK_ZPPFM008;
using Dongke.IBOSS.PRD.Service.SAPHegiiDataService.HGSAPDK_ZPPFM010;
using Dongke.IBOSS.PRD.WCF.DataModels;
using Oracle.ManagedDataAccess.Client;
namespace Dongke.IBOSS.PRD.Service.SAPHegiiDataService
{
public 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;
}
}
///
/// 获取成品交接同步数据
///
///
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;
}
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" +
//" 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 = 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;
}
}
///
/// 同步成品交接数据（手动）
///
///
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.TableIn = 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";
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.TableIn[index++] = info100;
}
}
else
{
sapParameter.Zsum = 0;
sapParameter.TableIn = 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.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='60' and GoodsCode=:GoodsCode and SAPCode=:SAPCode and UserCode=:UserCode \n" +
"and OrderCode=:OrderCode and OrderItem=:OrderItem";
foreach (Zspp100 item in result.TableOut)
{
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
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" +
" ,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 = '2'\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.yyyymmdd desc, dl.datacode, dl.begintime desc";
sre.Data = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
return sre;
}
catch (Exception ex)
{
throw ex;
}
}
///
/// 查询产量同步明细
///
///
///
///
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.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;
}
}
///
/// 查询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;
}
}
///
/// 查询成品条码同步明细
///
///
///
///
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" +
" 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;
}
}
///
/// 同步产量报工（手动）
///
///
///
///
///
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.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().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();
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 = :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.TableOut != null && result.TableOut.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.TableOut)
{
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)
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 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();
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.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(":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 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" +
" 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();
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 =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();
}
}
}
///
/// 产量接口
///
///
///
private static Zppfm008Response 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 = "Sapdk#240";
INIUtility ini = INIUtility.Instance(INIUtility.IniFile.Config);
credential.UserName = ini.ReadIniData("SAP_HEGII", "UserName");
credential.Password = ini.ReadIniData("SAP_HEGII", "Password");
// 参数
sapParameter.ZsumSpecified = true;
sapParameter.TableOut = new Zspp100[0];
// 接口
sapResult = sapClient.Zppfm008(sapParameter);
// 结果
OutputLog.TraceLog(LogPriority.Information,
"HGSAPDK_ZPPFM008",
"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",
sapResult.Zmsg,
ex.ToString(),
LocalPath.LogExePath + "SAP_HEGII\\Error_");
}
return sapResult;
}
///
/// 成品明细(条码接口)
///
///
private static ZPPFM010Response HGSAPDK_ZPPFM010(Zppfm010 sapParameter, string yyyymmdd)
{
ZPPFM010Response sapResult = null;
try
{
using (ZPPFM010Client sapClient = new ZPPFM010Client("ZPPFM010_BIND"))
{
// 登录
UserNamePasswordClientCredential credential =
sapClient.ClientCredentials.UserName;
//credential.UserName = "hgsapdk";
//credential.Password = "Sapdk#240";
INIUtility ini = INIUtility.Instance(INIUtility.IniFile.Config);
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);
// 结果
OutputLog.TraceLog(LogPriority.Information,
"HGSAPDK_ZPPFM010",
"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",
sapResult.ZMSG,
ex.ToString(),
LocalPath.LogExePath + "SAP_HEGII\\Error_");
}
return sapResult;
}
#region 测试
///
/// 同步成品条码（手动）-测试
///
///
///
///
public static ServiceResultEntity SetFP6001_1(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(2019, 1, 11);
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" +
" 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" +
" 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();
// 产品编码
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(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_240(Zppfm008 sapParameter, string yyyymmdd, string datacode)
{
Zppfm008Response sapResult = null;
try
{
using (ZPPFM008Client sapClient = new ZPPFM008Client("ZPPFM008_BIND_240"))
{
// 登录
UserNamePasswordClientCredential credential =
sapClient.ClientCredentials.UserName;
credential.UserName = "hgsapdk";
credential.Password = "Sapdk#240";
// 参数
sapParameter.ZsumSpecified = true;
sapParameter.TableOut = 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;
}
///
/// 成品明细(条码接口)-测试
///
///
private static ZPPFM010Response HGSAPDK_ZPPFM010_240(Zppfm010 sapParameter, string yyyymmdd)
{
ZPPFM010Response sapResult = null;
try
{
using (ZPPFM010Client sapClient = new ZPPFM010Client("ZPPFM010_BIND_240"))
{
// 登录
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
///
/// 同步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_");
}
}
}
/*
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;
}
}
*/
}
}