private StringBuilder insertsql(EKKO ekko, YWCOUNT yw, string str_date)
{
StringBuilder sb = new StringBuilder();
sb.Append(" INSERT INTO HB_USERACTION");
sb.Append("(UA_ID,UA_DATE,UA_GSBM,UA_GSMC,UA_YHBM,UA_YHMC,UA_YWBM,UA_YEMC,UA_DJZL,UA_DJYXL,UA_DJCXL)");
sb.Append(" VALUES(");
sb.Append("SQ_USERACTION.NEXTVAL,");
sb.Append("'" + str_date + "',");
sb.Append("'" + ekko.strBUKRS + "',");
sb.Append("'" + ekko.strBUKRSMC + "',");
sb.Append("'" + ekko.strERNAM + "',");
sb.Append("'" + ekko.strERNAMMC + "',");
sb.Append("'" + yw.str_lxbm + "',");
sb.Append("'" + yw.str_lxmc + "',");
sb.Append(yw.intQBCount + ",");
sb.Append(yw.intYXCount + ",");
sb.Append(yw.intCXCount + "");
sb.Append(");");
return(sb);
}
bool ISAPLoadInterface.SAPLoadData(ClsSAPDataParameter p_para)
{
bool Result = true;
m_Conn = ClsUtility.GetConn();
//暂时注销，正式时根据时间查出数据
//DataTable dtEkkoDate = new DataTable();
//try
//{
// string strSqlEkko = "SELECT AEDAT,EBELN FROM EKKO WHERE DLDATE='"+p_para.Sap_AEDAT+"'AND AEDAT like '" ;
// dtEkkoDate = m_Conn.GetSqlResultToDt(strSqlEkko);
//}
//catch (Exception exception)
//{
// Result = false;
// ClsErrorLogInfo.WriteSapLog("1", "xmfw", "ALL", p_para.Sap_AEDAT, "插入hb_xmfw表过程中查询EKKO表发生异常:\t\n" + exception);
// return Result;
//}
EKKO structEKKO = new EKKO();
DataTable dtEkko = new DataTable();
try
{
//杜杨ERP2.0
string stedate = p_para.Sap_AEDAT.Substring(0, 6);
//查询EKKO数据
//string strSqlEkko = "SELECT * FROM EKKO WHERE BSART='Z012'AND AEDAT like '" + stedate + "%' and EBELN='7000231271' order by AEDAT asc"//单条测试
string strSqlEkko = "SELECT * FROM EKKO WHERE BSART='X009'AND AEDAT like '" + stedate + "%' order by AEDAT asc";
dtEkko = m_Conn.GetSqlResultToDt(strSqlEkko);
}
catch (Exception exception)
{
Result = false;
ClsErrorLogInfo.WriteSapLog("1", "xmfw", "ALL", p_para.Sap_AEDAT, "插入hb_xmfw表过程中查询EKKO表发生异常:\t\n" + exception);
return Result;
}
strBuilder.Length = 0;
strBuilder.Append(" Begin "); //开始执行SQL
//开始执行SQL
foreach (DataRow subRowEkko in dtEkko.Rows)
{
//赋值EKKO表值
structEKKO.strLOEKZ = subRowEkko["LOEKZ"].ToString();
structEKKO.strAEDAT = subRowEkko["AEDAT"].ToString();
structEKKO.strEBELN = subRowEkko["EBELN"].ToString();
structEKKO.strFRGKE = subRowEkko["FRGKE"].ToString();
structEKKO.strFRGKE = string.IsNullOrEmpty(structEKKO.strFRGKE) ? "0" : structEKKO.strFRGKE;
structEKKO.strBUKRS = subRowEkko["BUKRS"].ToString();
structEKKO.strLIFNR = subRowEkko["LIFNR"].ToString();
structEKKO.strERNAM = subRowEkko["ERNAM"].ToString();
//服务商名称
try
{
structEKKO.strLIFNRMC = m_Conn.GetSqlResultToStr("SELECT NAME1 FROM LFA1 WHERE LIFNR='" + structEKKO.strLIFNR + "'");
}
catch (Exception exception1)
{
Result = false;
ClsErrorLogInfo.WriteSapLog("1", "xmfw", "ALL", p_para.Sap_AEDAT, "插入hb_xmfw表过程中查询LFA1表发生异常:\t\n" + exception1);
return Result;
}
//甲方单位名称
try
{
structEKKO.strBUKRSMC = m_Conn.GetSqlResultToStr("SELECT T.BUTXT FROM T001 T WHERE T.BUKRS='" + structEKKO.strBUKRS + "'");
}
catch (Exception exception1)
{
Result = false;
ClsErrorLogInfo.WriteSapLog("1", "xmfw", "ALL", p_para.Sap_AEDAT, "插入hb_xmfw表过程中查询T001表发生异常:\t\n" + exception1);
return Result;
}
if (string.IsNullOrEmpty(structEKKO.strBUKRSMC))
{
continue;
}
//采购订单创建人员名称
try
{
structEKKO.strERNAMMC = m_Conn.GetSqlResultToStr("SELECT NAME_LAST FROM USR02 WHERE BNAME='" + structEKKO.strERNAM + "'");
}
catch (Exception exception1)
{
Result = false;
ClsErrorLogInfo.WriteSapLog("1", "xmfw", "ALL", p_para.Sap_AEDAT, "插入hb_xmfw表过程中查询USR02表发生异常:\t\n" + exception1);
return Result;
}
//采购订单创建人员账号
//EKKO字段FRGKE值为1，获取审批人，为0不获取
if (structEKKO.strFRGKE == "1")
{
//当查询结果存在多行时，取最近日期的USERNAME
//当查询结果存在多行时，取最近时间的USERNAME
string strSqlCDHDR = "SELECT T.USERNAME,MAX(T.UDATE) AS UDATE,T.UTIME FROM CDHDR T WHERE T.TCODE='ME29N' AND T.OBJECTID='" + structEKKO.strEBELN + "' GROUP BY T.USERNAME,T.UTIME";
DataTable dtCDHDR = m_Conn.GetSqlResultToDt(strSqlCDHDR);
if (dtCDHDR != null && dtCDHDR.Rows.Count > 0)
{
string str_MaxTime = string.Empty;
for (int i = 0; i < dtCDHDR.Rows.Count; i++)
{
if (i == 0)
{
structEKKO.strUSERNAME = dtCDHDR.Rows[i]["USERNAME"].ToString();
str_MaxTime = dtCDHDR.Rows[i]["UTIME"].ToString();
continue;
}
string str_time = dtCDHDR.Rows[i]["UTIME"].ToString();
string[] str_times = str_time.Split(':');
if (str_times.Length < 2)
{
str_time = "0" + str_time;
}
if (String.Compare(str_MaxTime, str_time) < 0)
{
str_MaxTime = str_time;
structEKKO.strUSERNAME = dtCDHDR.Rows[i]["USERNAME"].ToString();
}
}
}
//采购订单创建人员名称
if (!string.IsNullOrEmpty(structEKKO.strUSERNAME))
{
try
{
structEKKO.strUSERNAMEMC = m_Conn.GetSqlResultToStr("SELECT NAME_LAST FROM USR02 WHERE BNAME='" + structEKKO.strUSERNAME + "'");
}
catch (Exception exception1)
{
Result = false;
ClsErrorLogInfo.WriteSapLog("1", "xmfw", "ALL", p_para.Sap_AEDAT, "插入hb_xmfw表过程中查询USR02表发生异常:\t\n" + exception1);
return Result;
}
}
}
EKPO structEKPO = new EKPO();
DataTable dtEKPO = new DataTable();
try
{
//查询EKPO未删除的数据
string strSqlEKPO = " SELECT * FROM EKPO WHERE EBELN = '" + structEKKO.strEBELN + "' AND (LOEKZ <>'X' OR LOEKZ IS NULL)";
dtEKPO = m_Conn.GetSqlResultToDt(strSqlEKPO);
}
catch (Exception exception1)
{
Result = false;
ClsErrorLogInfo.WriteSapLog("1", "xmfw", "ALL", p_para.Sap_AEDAT, "插入hb_xmfw表过程中查询EKPO或LFA1表发生异常:\t\n" + exception1);
return Result;
}
foreach (DataRow subRowEKPO in dtEKPO.Rows)
{
//赋值EKPO表值
structEKPO.strEBELP = subRowEKPO["EBELP"].ToString();
structEKPO.strTXZ01 = subRowEKPO["TXZ01"].ToString().Replace("'", "‘");
structEKPO.strEBELN = subRowEKPO["EBELN"].ToString();
structEKPO.strBRTWR = subRowEKPO["BRTWR"].ToString();
structEKPO.strBRTWR = string.IsNullOrEmpty(structEKPO.strBRTWR) ? "0" : structEKPO.strBRTWR;
structEKPO.strBRTWR = (Convert.ToDecimal(structEKPO.strBRTWR) / 10000).ToString("0.00");
structEKPO.strBUKRS = subRowEKPO["BUKRS"].ToString();
structEKPO.strPACKNO = subRowEKPO["PACKNO"].ToString();
//变量
string strSUB_PACKNO;
try
{
//取出 ESLL-SUB_PACKNO
strSUB_PACKNO = m_Conn.GetSqlResultToStr("SELECT SUB_PACKNO FROM ESLL WHERE PACKNO='" + structEKPO.strPACKNO + "' AND DEL IS NULL ");
}
catch (Exception exception2)
{
Result = false;
ClsErrorLogInfo.WriteSapLog("1", "xmfw", "ALL", p_para.Sap_AEDAT, "插入hb_xmfw表过程中查询ESLL表发生异常:\t\n" + exception2);
return Result;
}
OTHER structOther = new OTHER();
ESLL structESll = new ESLL();
if (!string.IsNullOrEmpty(strSUB_PACKNO))
{
//ESLL structESll = new ESLL();
DataTable dtESLL = new DataTable();
//变量
try
{
dtESLL = m_Conn.GetSqlResultToDt("SELECT * FROM ESLL WHERE PACKNO='" + strSUB_PACKNO + "' AND DEL IS NULL ");
}
catch (Exception exception2)
{
Result = false;
ClsErrorLogInfo.WriteSapLog("1", "xmfw", "ALL", p_para.Sap_AEDAT, "插入hb_xmfw表过程中查询ESLL表发生异常:\t\n" + exception2);
return Result;
}
if (dtESLL != null && dtESLL.Rows.Count > 0)
{
foreach (DataRow drESLL in dtESLL.Rows)
{
structESll.strPACKNO = drESLL["PACKNO"].ToString();
structESll.strINTROW = drESLL["INTROW"].ToString();
structESll.strASNUM = drESLL["SRVPOS"].ToString();
structESll.strKTEXT1 = drESLL["KTEXT1"].ToString();
structESll.strPACKNO = drESLL["PACKNO"].ToString();
structESll.strBRTWR = drESLL["BRTWR"].ToString();
//OTHER structOther = new OTHER();
try
{
structOther.strZEKKN = m_Conn.GetSqlResultToStr("select t.ZEKKN from ESKL t where t.PACKNO='" + structESll.strPACKNO + "' and t.introw='" + structESll.strINTROW + "'");
}
catch (Exception exception2)
{
Result = false;
ClsErrorLogInfo.WriteSapLog("1", "xmfw", "ALL", p_para.Sap_AEDAT, "插入hb_xmfw表过程中查询ESKL表发生异常:\t\n" + exception2);
return Result;
}
if (!string.IsNullOrEmpty(structOther.strZEKKN))
{
try
{
structOther.strPSPSPPNR = m_Conn.GetSqlResultToStr("select t.ps_psp_pnr from EKKN t where t.ebeln='" + structEKPO.strEBELN + "' and t.ebelp='" + structEKPO.strEBELP + "' and t.ZEKKN='" + structOther.strZEKKN + "'");
}
catch (Exception exception2)
{
Result = false;
ClsErrorLogInfo.WriteSapLog("1", "xmfw", "ALL", p_para.Sap_AEDAT, "插入hb_xmfw表过程中查询EKKN表发生异常:\t\n" + exception2);
return Result;
}
if (!string.IsNullOrEmpty(structOther.strPSPSPPNR))
{
try
{
//structOther.strPOSID = m_Conn.GetSqlResultToStr("select t.POSID from PRPS t,EKKN a where trim(t.poski) =trim(a.'" + structOther.strPSPSPPNR + "')");
//structOther.strPOST1 = m_Conn.GetSqlResultToStr("select t.POST1 from PRPS t,EKKN a where trim(t.poski) =trim(a.'" + structOther.strPSPSPPNR + "')");
structOther.strPOSID = m_Conn.GetSqlResultToStr("select t.POSID from PRPS t where POSID ='" + structOther.strPSPSPPNR.Replace("-", "").Replace(".", "").Trim() + "'");
structOther.strPOST1 = m_Conn.GetSqlResultToStr("select t.POST1 from PRPS t where POSID ='" + structOther.strPSPSPPNR.Replace("-", "").Replace(".", "").Trim() + "'");
}
catch (Exception exception2)
{
Result = false;
ClsErrorLogInfo.WriteSapLog("1", "xmfw", "ALL", p_para.Sap_AEDAT, "插入hb_xmfw表过程中查询PRPS表发生异常:\t\n" + exception2);
return Result;
}
}
}
strBuilder.Append(" DELETE FROM HB_XMFW WHERE XMFW_DDH='" + subRowEkko["EBELN"].ToString() + "' AND XMFW_XMH='" + subRowEKPO["EBELP"].ToString() + "' AND XMFW_MXBH='" + structOther.strZEKKN + "';");
//添加数据
strBuilder.Append(" INSERT INTO HB_XMFW");
strBuilder.Append("(XMFW_ID,XMFW_DATE,XMFW_DDH,XMFW_CJRYBM,XMFW_CJRYMC,XMFW_SPRYBM,XMFW_SPRYMC,XMFW_SP,");
strBuilder.Append("XMFW_DDSC,XMFW_JFDWBM,XMFW_JFDWMC,XMFW_XMH,XMFW_CGFUMC,XMFW_FUSBM,XMFW_FUSMC,XMFW_JE,");
strBuilder.Append("XMFW_MXBH,XMFW_GZBH,XMFW_GZNR,XMFW_WBSBM,XMFW_WBSMC,XMFW_DJ,XMFW_DJJE,XMFW_HT)");
strBuilder.Append(" VALUES(");
strBuilder.Append("SQ_XMFW.NEXTVAL,");
strBuilder.Append("'" + p_para.Sap_AEDAT.Substring(0, 6) + "',");
strBuilder.Append("'" + structEKKO.strEBELN + "',");
strBuilder.Append("'" + structEKKO.strERNAM + "',");
strBuilder.Append("'" + structEKKO.strERNAMMC + "',");
strBuilder.Append("'" + structEKKO.strUSERNAME + "',");
strBuilder.Append("'" + structEKKO.strUSERNAMEMC + "',");
strBuilder.Append("'" + structEKKO.strFRGKE + "',");
strBuilder.Append("'" + structEKKO.strLOEKZ + "',");
strBuilder.Append("'" + structEKKO.strBUKRS + "',");
strBuilder.Append("'" + structEKKO.strBUKRSMC + "',");
strBuilder.Append("'" + structEKPO.strEBELP + "',");
strBuilder.Append("'" + structEKPO.strTXZ01 + "',");
strBuilder.Append("'" + structEKKO.strLIFNR + "',");
strBuilder.Append("'" + structEKKO.strLIFNRMC + "',");
strBuilder.Append("'" + structEKPO.strBRTWR + "',");
strBuilder.Append("'" + structOther.strZEKKN + "',");
strBuilder.Append("'" + structESll.strASNUM + "',");
strBuilder.Append("'" + structESll.strKTEXT1 + "',");
strBuilder.Append("'" + structOther.strPOSID + "',");
strBuilder.Append("'" + structOther.strPOST1 + "',");
strBuilder.Append("'" + (string.IsNullOrEmpty(structESll.strBRTWR) ? "0" : (Convert.ToDecimal(structESll.strBRTWR) / 10000).ToString("F2")) + "',");
strBuilder.Append("'" + structESll.strBRTWR + "',");
strBuilder.Append("'" + structEKPO.strBRTWR + "'");
strBuilder.Append(");");
}
}
}
}
}
strBuilder.Append(" End;"); //SQL完成
try
{
if (strBuilder.ToString().Length < 14)
{
return true;
}
//数据提交
Result = ClsUtility.ExecuteSqlToDb(strBuilder.ToString());
m_Conn.Dispose();
}
catch (Exception exception5)
{
Result = false;
ClsErrorLogInfo.WriteSapLog("1", "xmfw", "ALL", p_para.Sap_AEDAT, "插入hb_xmfw表发生异常:" + exception5);
}
return Result;
}
public bool SAPLoadData(ClsSAPDataParameter p_para)
{
bool Result = true;
m_Conn = ClsUtility.GetConn();
DataTable dtEKKODate = new DataTable();
try
{
//查询EKKO的数据
string strSqlIMPR = "select distinct AEDAT from EKKO t where dldate='" + p_para.Sap_AEDAT + "' ";
dtEKKODate = m_Conn.GetSqlResultToDt(strSqlIMPR);
}
catch (Exception exception)
{
Result = false;
ClsErrorLogInfo.WriteSapLog("1", "userAction", "ALL", p_para.Sap_AEDAT, "插入hb_useraction表过程中查询EKKO表发生异常:\t\n" + exception);
return(Result);
}
foreach (DataRow subRowEKKODate in dtEKKODate.Rows)
{
if (subRowEKKODate["AEDAT"] == null || string.IsNullOrEmpty(subRowEKKODate["AEDAT"].ToString()))
{
continue;
}
string strDate = subRowEKKODate["AEDAT"].ToString().Substring(0, 6);
EKKO strEKKO = new EKKO();
DataTable dtEKKO = new DataTable();
try
{
//查询EKKO的数据
string strSqlIMPR = "select distinct t.ERNAM from EKKO t where substr(aedat,0,6)='" + strDate + "'";
dtEKKO = m_Conn.GetSqlResultToDt(strSqlIMPR);
}
catch (Exception exception)
{
Result = false;
ClsErrorLogInfo.WriteSapLog("1", "userAction", "ALL", p_para.Sap_AEDAT, "插入hb_useraction表过程中查询EKKO表发生异常:\t\n" + exception);
return(Result);
}
if (dtEKKO != null)
{
}
else
{
string sss = "";
}
strBuilder.Length = 0;
strBuilder.Append(" Begin "); //开始执行SQL
foreach (DataRow subRowEKKO in dtEKKO.Rows)
{
strEKKO.strERNAM = subRowEKKO["ERNAM"].ToString();
strBuilder.Append(" DELETE FROM HB_USERACTION WHERE UA_DATE='" + strDate + "' AND UA_YHBM='" + strEKKO.strERNAM + "';");
try
{
//用户名
strEKKO.strERNAMMC = m_Conn.GetSqlResultToStr("select distinct t.name_last from USR02 t where t.bname='" + strEKKO.strERNAM + "'");
}
catch (Exception exception1)
{
Result = false;
ClsErrorLogInfo.WriteSapLog("1", "userAction", "ALL", p_para.Sap_AEDAT, "插入hb_useraction表过程中查询USER_ADDR表发生异常:\t\n" + exception1);
return(Result);
}
try
{
string dwsql = "select t.department from USR02 t where t.bname='" + strEKKO.strERNAM + "'";
//公司编码 //公司名称
strEKKO.strBUKRSMC = m_Conn.GetSqlResultToStr(dwsql);
}
catch (Exception exception1)
{
Result = false;
ClsErrorLogInfo.WriteSapLog("1", "userAction", "ALL", p_para.Sap_AEDAT, "插入hb_useraction表过程中查询USR02表发生异常:\t\n" + exception1);
return(Result);
}
try
{
string strcountsql = " select LX,LOEKZ, count(1) as LXCOUNT from (";
strcountsql += " select trim(t.bsart) as LX, t.loekz as LOEKZ from EKKO t";
strcountsql += " where t.ernam='" + strEKKO.strERNAM + "' )";
strcountsql += " group by lx, loekz";
//业务单据量
DataTable dtcount = m_Conn.GetSqlResultToDt(strcountsql);
if (dtcount != null && dtcount.Rows.Count > 0)
{
YWCOUNT ywAll = new YWCOUNT();
ywAll.str_lxbm = "ALL";
ywAll.str_lxmc = getLXMC(ywAll.str_lxbm);
ywAll.intQBCount = 0;
ywAll.intYXCount = 0;
ywAll.intCXCount = 0;
YWCOUNT yw70 = SetYWCount("X008", dtcount);
if (!string.IsNullOrEmpty(yw70.str_lxbm))
{
ywAll.intQBCount = yw70.intQBCount;
ywAll.intYXCount = yw70.intYXCount;
ywAll.intCXCount = yw70.intCXCount;
strBuilder.Append(insertsql(strEKKO, yw70, strDate));
}
YWCOUNT yw71 = SetYWCount("X009", dtcount);
if (!string.IsNullOrEmpty(yw71.str_lxbm))
{
ywAll.intQBCount = yw71.intQBCount;
ywAll.intYXCount = yw71.intYXCount;
ywAll.intCXCount = yw71.intCXCount;
strBuilder.Append(insertsql(strEKKO, yw71, strDate));
}
YWCOUNT yw45 = SetYWCount(dtcount);
if (!string.IsNullOrEmpty(yw45.str_lxbm))
{
ywAll.intQBCount = yw45.intQBCount;
ywAll.intYXCount = yw45.intYXCount;
ywAll.intCXCount = yw45.intCXCount;
strBuilder.Append(insertsql(strEKKO, yw45, strDate));
}
if (ywAll.intQBCount > 0)
{
strBuilder.Append(insertsql(strEKKO, ywAll, strDate));
}
}
}
catch (Exception exception1)
{
Result = false;
ClsErrorLogInfo.WriteSapLog("1", "userAction", "ALL", p_para.Sap_AEDAT, "插入hb_useraction表过程中查询EKKO表业务量发生异常:\t\n" + exception1);
return(Result);
}
}
strBuilder.Append(" End;"); //SQL完成
try
{
if (strBuilder.ToString().Length < 14)
{
return(true);
}
//数据提交
Result = ClsUtility.ExecuteSqlToDb(strBuilder.ToString());
}
catch (Exception exception5)
{
Result = false;
ClsErrorLogInfo.WriteSapLog("1", "userAction", "ALL", p_para.Sap_AEDAT, "插入hb_useraction表发生异常:" + exception5);
}
}
return(Result);
}
