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); }