public bool MailConnect(string p_User, string p_Pass) { //判断系统是否注册此用户 ClsDBConnection m_oDb = new ClsDBConnection(); m_oDb.Open(); string strUserCountSql = "SELECT COUNT(*) FROM TBREGUSERINFO WHERE USER_CODE = '" + p_User + "'"; string strCount = m_oDb.GetSqlResultToStr(strUserCountSql); if (strCount == "0" || strCount == "") { return(false); } //注册验证邮件 bool Result = true; //中油信箱 string strMailSql = "SELECT SYS_VALUE FROM SAP_SYSCONFIG WHERE SYS_CODE = 'MailAddress'"; string strPopServer = m_oDb.GetSqlResultToStr(strMailSql); string strUser = p_User; string strPass = p_Pass; try { //用110端口新建POP3服务器连接 Server = new TcpClient(strPopServer, 110); //初始化 NetStrm = Server.GetStream(); RdStrm = new StreamReader(Server.GetStream()); string strMegage = RdStrm.ReadLine(); Result = CheckMailResult(strMegage); //登录服务器过程 Data = "USER " + strUser + CRLF; szData = System.Text.Encoding.ASCII.GetBytes(Data.ToCharArray()); NetStrm.Write(szData, 0, szData.Length); strMegage = RdStrm.ReadLine(); Result = CheckMailResult(strMegage); Data = "PASS " + strPass + CRLF; szData = System.Text.Encoding.ASCII.GetBytes(Data.ToCharArray()); NetStrm.Write(szData, 0, szData.Length); strMegage = RdStrm.ReadLine(); Result = CheckMailResult(strMegage); } catch (InvalidOperationException err) { Result = false; } m_oDb.Dispose(); return(Result); }
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(); string m_time = p_para.Sap_AEDAT.Substring(0, 6); #region 查询主表信息 ZC025 strZC025 = new ZC025(); DataTable dtZC025 = new DataTable(); try { StringBuilder sbSqlZC025 = new StringBuilder(); sbSqlZC025.Append(" select"); sbSqlZC025.Append(" a.RSNUM,a.RSDAT,a.CPUTM,a.ZJHLX0,a.ZJHLX0T,a.WERKS,a.ZJHLX1,a.ZJHLX1T,a.ZJHLX2,a.ZJHLX2T,"); sbSqlZC025.Append(" b.XMH,'' as MATNR,'' as MAKTX"); sbSqlZC025.Append(" from ZC10MMDG025 a ,(select count(RSPOS) XMH,RSNUM from RESB group by RSNUM) b"); sbSqlZC025.Append(" where b.RSNUM = a.RSNUM and substr(replace(a.rsdat,'.',''),0,6)='" + m_time + "'"); sbSqlZC025.Append(" union all "); sbSqlZC025.Append(" select"); sbSqlZC025.Append(" a.RSNUM,a.RSDAT,a.CPUTM,a.ZJHLX0,a.ZJHLX0T,a.WERKS,a.ZJHLX1,a.ZJHLX1T,a.ZJHLX2,a.ZJHLX2T,"); sbSqlZC025.Append(" b.XMH,'' as MATNR,'' as MAKTX"); sbSqlZC025.Append(" from ZC10MMDG025 a ,(select count(POSNR) XMH,vbeln from VBAP group by vbeln) b"); sbSqlZC025.Append(" where b.vbeln = a.RSNUM and substr(replace(a.rsdat,'.',''),0,6)='" + m_time + "'"); dtZC025 = m_Conn.GetSqlResultToDt(sbSqlZC025.ToString()); } catch (Exception exception) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "cg", "ALL", p_para.Sap_AEDAT, "插入hb_wzcg表过程中查询ZC10MMDG025表发生异常:\t\n" + exception); return(Result); } DataTable dtZC025B = new DataTable(); try { string sbSqlZC025B = " select"; sbSqlZC025B += " REQ_NUM,BDTER,CPUTM,count(REQ_ITEM) as REQ_ITEM,ZJHLX0,WERKS,ZJHLX1,ZJHLX2 "; sbSqlZC025B += " from ZC10MMDG025B WHERE (XLOEK<>'X' or XLOEK IS NULL) and substr(replace(BDTER, '.', ''), 0, 6) = '" + m_time + "'"; sbSqlZC025B += " group by REQ_NUM,BDTER,CPUTM,ZJHLX0,WERKS,ZJHLX1,ZJHLX2"; dtZC025B = m_Conn.GetSqlResultToDt(sbSqlZC025B); } catch (Exception exception) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "cg", "ALL", p_para.Sap_AEDAT, "插入hb_wzcg表过程中查询ZC10MMDG025B表发生异常:\t\n" + exception); return(Result); } DataRow drZC025 = null; foreach (DataRow subRowZC025B in dtZC025B.Rows) { drZC025 = dtZC025.NewRow(); drZC025["RSNUM"] = subRowZC025B["REQ_NUM"].ToString(); //需求计划号 drZC025["XMH"] = subRowZC025B["REQ_ITEM"].ToString(); //需求项目号 drZC025["ZJHLX0"] = subRowZC025B["ZJHLX0"].ToString(); //需求计划类型 drZC025["ZJHLX0T"] = string.Empty; //需求计划文本 drZC025["ZJHLX1"] = subRowZC025B["ZJHLX1"].ToString(); //需求计划类型1 drZC025["ZJHLX1T"] = string.Empty; //需求计划文本1 drZC025["ZJHLX2"] = subRowZC025B["ZJHLX2"].ToString(); //需求计划类型2 drZC025["ZJHLX2T"] = string.Empty; //需求计划文本2 drZC025["RSDAT"] = subRowZC025B["BDTER"].ToString(); //需求计划提报日期 drZC025["CPUTM"] = subRowZC025B["CPUTM"].ToString(); //需求计划提报时间 drZC025["WERKS"] = subRowZC025B["WERKS"].ToString(); //工厂 drZC025["MATNR"] = string.Empty; //物料号 drZC025["MAKTX"] = string.Empty; //物料描述 dtZC025.Rows.Add(drZC025); } #endregion DataTable dtGC = new DataTable(); try { string sbGC = " select * from T001W"; dtGC = m_Conn.GetSqlResultToDt(sbGC); } catch (Exception exception) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "cg", "ALL", p_para.Sap_AEDAT, "插入hb_wzcg表过程中查询ZC10MMDG025B表发生异常:\t\n" + exception); return(Result); } int commitcount = 0; int commitMax = 10; strBuilder.Length = 0; strBuilder.Append(" Begin "); //开始执行SQL foreach (DataRow subRowZC025 in dtZC025.Rows) { strBuilder.Append(" DELETE FROM HB_WZCG WHERE WL_JHH='" + subRowZC025["RSNUM"].ToString() + "';"); strZC025.strRSNUM = subRowZC025["RSNUM"].ToString(); //需求计划号 strZC025.strRSPOS = subRowZC025["XMH"].ToString(); //需求项目号 strZC025.strZJHLXO = subRowZC025["ZJHLX0"].ToString(); //需求计划类型 strZC025.strZJHLX0T = subRowZC025["ZJHLX0T"].ToString(); //需求计划文本 strZC025.strZJHLX1 = subRowZC025["ZJHLX1"].ToString(); //需求计划类型1 strZC025.strZJHLX1T = subRowZC025["ZJHLX1T"].ToString(); //需求计划文本1 strZC025.strZJHLX2 = subRowZC025["ZJHLX2"].ToString(); //需求计划类型2 strZC025.strZJHLX2T = subRowZC025["ZJHLX2T"].ToString(); //需求计划文本2 strZC025.strRSDAT = subRowZC025["RSDAT"].ToString(); //需求计划提报日期 strZC025.strCPUTM = subRowZC025["CPUTM"].ToString(); //需求计划提报时间 strZC025.strWERKS = subRowZC025["WERKS"].ToString(); //工厂 strZC025.strWERKSTXT = dtGC.Select("WERKS='" + subRowZC025["WERKS"].ToString() + "'")[0]["NAME1"].ToString(); //工厂 strZC025.strMATNR = subRowZC025["MATNR"].ToString(); //物料号 strZC025.strMAKTX = subRowZC025["MAKTX"].ToString(); //物料描述 //if (!string.IsNullOrEmpty(strZC025.strWERKS)) //{ // try // { // //按RSNUM汇总预计采购金额 // string strSqlT001 = "SELECT T.BUTXT FROM T001 T WHERE T.BUKRS='" + strZC025.strWERKS + "' "; // strZC025.strWERKSTXT = m_Conn.GetSqlResultToStr(strSqlT001); // } // catch (Exception exception2) // { // Result = false; // ClsErrorLogInfo.WriteSapLog("1", "cg", "ALL", p_para.Sap_AEDAT, "插入hb_wzcg表过程中查询T001表发生异常:\t\n" + exception2); // return Result; // } //} #region 026公司级取数 OtherData otherdata026 = new OtherData(); string strSqlZC026 = string.Empty; try { //按RSNUM汇总预计采购金额 strSqlZC026 = "select sum(Z_BRTWR) as Z_BRTWR from ZC10MMDG026 "; strSqlZC026 += " where RSNUM='" + strZC025.strRSNUM + "'"; otherdata026.strYBRTWR = m_Conn.GetSqlResultToStr(strSqlZC026); } catch (Exception exception2) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "cg", "ALL", p_para.Sap_AEDAT, "插入hb_wzcg表过程中查询ZC10MMDG026表发生异常:\t\n" + exception2); return(Result); } try { //完成采购条数 strSqlZC026 = "select count(1) Z_BRTWR from ZC10MMDG026 "; strSqlZC026 += " where RSNUM='" + strZC025.strRSNUM + "' and zebeln is not null"; otherdata026.strEBELNCount = m_Conn.GetSqlResultToStr(strSqlZC026); } catch (Exception exception2) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "cg", "ALL", p_para.Sap_AEDAT, "插入hb_wzcg表过程中查询ZC10MMDG026表发生异常:\t\n" + exception2); return(Result); } try { //汇总BRTWR,输出(完成采购金额) string strSqlEKPO = "SELECT SUM(B.BRTWR) FROM ZC10MMDG026 A ,EKPO B WHERE A.ZEBELN=B.EBELN AND A.ZEBELP=B.EBELP"; strSqlEKPO += " AND A.RSNUM='" + strZC025.strRSNUM + "'"; otherdata026.strBRTWR = m_Conn.GetSqlResultToStr(strSqlEKPO); } catch (Exception exception2) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "cg", "ALL", p_para.Sap_AEDAT, "插入hb_wzcg表过程中查询ZC10MMDG026表发生异常:\t\n" + exception2); return(Result); } #endregion #region 203_HZ二级单位 OtherData otherdata203 = new OtherData(); try { //汇总PREIS 输出(预计采购金额) string strSqlZP203_HZ = "select SUM(B.PREIS* MENGE)"; strSqlZP203_HZ += " from ZP10MMDG203_HZ A ,EBAN B"; strSqlZP203_HZ += " WHERE A.BANFN=B.BANFN AND A.BNFPO=B.BNFPO "; strSqlZP203_HZ += " and A.RSNUM='" + strZC025.strRSNUM + "'"; otherdata203.strYBRTWR = m_Conn.GetSqlResultToStr(strSqlZP203_HZ); } catch (Exception exception2) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "cg", "ALL", p_para.Sap_AEDAT, "插入hb_wzcg表过程中查询EBAN表发生异常:\t\n" + exception2); return(Result); } try { //汇总BRTWR,输出(完成采购金额) string strSqlEKPO = "select SUM(B.BRTWR)"; strSqlEKPO += " from ZP10MMDG203_HZ A ,EKPO B"; strSqlEKPO += " WHERE A.BNFPO=B.BNFPO AND A.BANFN=B.BANFN"; strSqlEKPO += " and A.RSNUM='" + strZC025.strRSNUM + "'"; otherdata203.strBRTWR = m_Conn.GetSqlResultToStr(strSqlEKPO); } catch (Exception exception2) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "cg", "ALL", p_para.Sap_AEDAT, "插入hb_wzcg表过程中查询EKPO表发生异常:\t\n" + exception2); return(Result); } try { //汇总EBELP条数,输出(完成采购条数) string strSqlEKPO = "select COUNT(1)"; strSqlEKPO += " from ZP10MMDG203_HZ A ,EKPO B"; strSqlEKPO += " WHERE A.BNFPO=B.BNFPO AND A.BANFN=B.BANFN"; strSqlEKPO += " and A.RSNUM='" + strZC025.strRSNUM + "'"; otherdata203.strEBELNCount = m_Conn.GetSqlResultToStr(strSqlEKPO); } catch (Exception exception2) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "cg", "ALL", p_para.Sap_AEDAT, "插入hb_wzcg表过程中查询EKPO表发生异常:\t\n" + exception2); return(Result); } #endregion OtherData otherdata = new OtherData(); DataTable dtOther = new DataTable(); try { //汇总EBELP条数,输出(完成采购条数) StringBuilder sb = new StringBuilder(); sb.Append(" SELECT SUM(TS) TS, SUM(DMBTR) DMBTR"); sb.Append(" FROM (select COUNT(1) AS TS, SUM(C.DMBTR) AS DMBTR"); sb.Append(" from ZP10MMDG203_HZ A, EKPO B, MSEG C"); sb.Append(" WHERE A.BNFPO = B.BNFPO"); sb.Append(" AND A.BANFN = B.BANFN"); sb.Append(" AND B.EBELN = C.EBELN"); sb.Append(" AND B.EBELP = C.EBELP"); sb.Append(" AND (C.BWART='101' OR C.BWART='105') "); sb.Append(" AND A.RSNUM='" + strZC025.strRSNUM + "'"); sb.Append(" UNION ALL"); sb.Append(" SELECT COUNT(1) AS TS, SUM(B.DMBTR) AS DMBTR"); sb.Append(" FROM ZC10MMDG026 A, MSEG B "); sb.Append(" WHERE A.ZEBELN = B.EBELN"); sb.Append(" AND A.ZEBELP = B.EBELP"); sb.Append(" AND (B.BWART ='101' OR B.BWART ='105')"); sb.Append(" AND A.RSNUM='" + strZC025.strRSNUM + "')"); dtOther = m_Conn.GetSqlResultToDt(sb.ToString()); } catch (Exception exception2) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "cg", "ALL", p_para.Sap_AEDAT, "插入hb_wzcg表过程中查询MSEG表发生异常:\t\n" + exception2); return(Result); } decimal YBRTWR026 = Convert.ToDecimal(string.IsNullOrEmpty(otherdata026.strYBRTWR) ? "0" : otherdata026.strYBRTWR); decimal YBRTWR203 = Convert.ToDecimal(string.IsNullOrEmpty(otherdata203.strYBRTWR) ? "0" : otherdata203.strYBRTWR); otherdata.strYBRTWR = (YBRTWR026 + YBRTWR203).ToString("F2"); //预计采购金额 decimal EBELNCount026 = Convert.ToDecimal(string.IsNullOrEmpty(otherdata026.strEBELNCount) ? "0" : otherdata026.strEBELNCount); decimal EBELNCount203 = Convert.ToDecimal(string.IsNullOrEmpty(otherdata203.strEBELNCount) ? "0" : otherdata203.strEBELNCount); otherdata.strEBELNCount = (EBELNCount026 + EBELNCount203).ToString("F0"); //采购凭证条数 decimal BRTWR026 = Convert.ToDecimal(string.IsNullOrEmpty(otherdata026.strBRTWR) ? "0" : otherdata026.strBRTWR); decimal BRTWR203 = Convert.ToDecimal(string.IsNullOrEmpty(otherdata203.strBRTWR) ? "0" : otherdata203.strBRTWR); otherdata.strBRTWR = (BRTWR026 + BRTWR203).ToString("F2"); //采购订单总价 if (dtOther != null && dtOther.Rows.Count > 0) { otherdata.strEBELPCount = (dtOther.Rows[0]["TS"] ?? "0").ToString(); //采购订单行项目条数 otherdata.strDMBTR = (dtOther.Rows[0]["DMBTR"] ?? "0").ToString(); //本位币金额 } #region 插入sql strBuilder.Append(" INSERT INTO HB_WZCG"); strBuilder.Append(" (WL_ID,WL_JHH,WL_XMH,WL_LX ,WL_WB ,WL_LX1,WL_WB1,WL_LX2,WL_WB2,"); strBuilder.Append(" WL_JHTBRQ,WL_JHTBSJ,WL_GC,WL_GCMC,WL_WL,WL_WLMS,WL_YJCGJE,"); strBuilder.Append(" WL_CGPZTS,WL_CGDDZJ,WL_BWBJE,WL_CGDDTS"); strBuilder.Append(" ) VALUES("); strBuilder.Append("SQ_WZCG.NEXTVAL,"); strBuilder.Append("'" + strZC025.strRSNUM + "',"); //需求计划号 strBuilder.Append("'" + strZC025.strRSPOS + "',"); //需求项目号 strBuilder.Append("'" + strZC025.strZJHLXO + "',"); //需求计划类型 strBuilder.Append("'" + strZC025.strZJHLX0T + "',"); //需求计划文本 strBuilder.Append("'" + strZC025.strZJHLX1 + "',"); //需求计划类型1 strBuilder.Append("'" + strZC025.strZJHLX1T + "',"); //需求计划文本1 strBuilder.Append("'" + strZC025.strZJHLX2 + "',"); //需求计划类型2 strBuilder.Append("'" + strZC025.strZJHLX2T + "',"); //需求计划文本2 strBuilder.Append("'" + strZC025.strRSDAT.Replace(".", "") + "',"); //需求计划提报日期 strBuilder.Append("'" + strZC025.strCPUTM + "',"); //需求计划提报时间 strBuilder.Append("'" + strZC025.strWERKS + "',"); //工厂 strBuilder.Append("'" + strZC025.strWERKSTXT + "',"); //工厂 strBuilder.Append("'" + strZC025.strMATNR + "',"); //物料号 strBuilder.Append("'" + strZC025.strMAKTX + "',"); //物料描述 strBuilder.Append("'" + otherdata.strYBRTWR + "',"); //预计采购金额 strBuilder.Append("'" + otherdata.strEBELNCount + "',"); //采购凭证条数 strBuilder.Append("'" + otherdata.strBRTWR + "',"); //采购订单总价 strBuilder.Append("'" + otherdata.strDMBTR + "',"); //本位币金额 strBuilder.Append("'" + otherdata.strEBELPCount + "'"); //采购订单行项目条数 strBuilder.Append(");"); #endregion commitcount++; if (commitcount % commitMax == 0) { strBuilder.Append(" End;"); //SQL完成 try { //数据提交 Result = ClsUtility.ExecuteSqlToDb(strBuilder.ToString()); strBuilder.Clear(); strBuilder.Append(" Begin "); //开始执行SQL } catch (Exception exception5) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "cgsj", "ALL", p_para.Sap_AEDAT, "插入hb_wzcgsj表发生异常:" + exception5); } } } //SQL完成 try { if (strBuilder.ToString().Length < 14) { return(true); } strBuilder.Append(" End;"); //数据提交 Result = ClsUtility.ExecuteSqlToDb(strBuilder.ToString()); } catch (Exception exception5) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "cgsj", "ALL", p_para.Sap_AEDAT, "插入hb_wzcgsj表发生异常:" + exception5); } return(Result); }
public bool SAPLoadData(ClsSAPDataParameter p_para) { bool Result = true; m_Conn = ClsUtility.GetConn(); DataTable dtIMPRDate = new DataTable(); try { //查询IMPR的数据 string strSqlIMPR = "select distinct GJAHR from IMPR t where dldate='" + p_para.Sap_AEDAT + "'"; dtIMPRDate = m_Conn.GetSqlResultToDt(strSqlIMPR); } catch (Exception exception) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "xmtz", "ALL", p_para.Sap_AEDAT, "插入hb_xmtz表过程中查询IMPR表发生异常:\t\n" + exception); return(Result); } foreach (DataRow subRowIMPRdate in dtIMPRDate.Rows) { string strDate = subRowIMPRdate["GJAHR"].ToString(); if (string.IsNullOrEmpty(strDate)) { continue; } IMPR strIMPR = new IMPR(); DataTable dtIMPR = new DataTable(); try { //查询IMPR的数据 string strSqlIMPR = "select * from IMPR t where t.gjahr='" + strDate + "'"; dtIMPR = m_Conn.GetSqlResultToDt(strSqlIMPR); } catch (Exception exception) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "xmtz", "ALL", p_para.Sap_AEDAT, "插入hb_xmtz表过程中查询IMPR表发生异常:\t\n" + exception); return(Result); } strBuilder.Clear(); strBuilder.Append(" Begin "); //开始执行SQL strBuilder.Append(" DELETE FROM HB_XMTZ WHERE XMTZ_YEAR='" + strDate + "';"); foreach (DataRow subRowIMPR in dtIMPR.Rows) { string strPOST1 = string.Empty; //投资节点名称 string strWTGES = string.Empty; //投资节点金额 int intJC = 0; strIMPR.strPRNAM = subRowIMPR["PRNAM"].ToString(); strIMPR.strPOSID = subRowIMPR["POSID"].ToString(); strIMPR.strGJAHR = subRowIMPR["GJAHR"].ToString(); strIMPR.strOBJNR = subRowIMPR["OBJNR"].ToString(); try { //投资节点名称 strPOST1 = m_Conn.GetSqlResultToStr("select distinct t.post1 from IMPU t where Trim(t.posnr)='" + strIMPR.strPOSID + "' and t.gjahr='" + strDate + "'"); } catch (Exception exception1) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "xmtz", "ALL", p_para.Sap_AEDAT, "插入hb_xmtz表过程中查询IMPU表发生异常:\t\n" + exception1); return(Result); } try { //投资节点金额 strWTGES = m_Conn.GetSqlResultToStr("select sum(WTGES) from BPGE t where t.OBJNR='" + strIMPR.strOBJNR + "' and t.WRTTP='47' "); } catch (Exception exception1) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "xmtz", "ALL", p_para.Sap_AEDAT, "插入hb_xmtz表过程中查询BPGE表发生异常:\t\n" + exception1); return(Result); } //string[] strjc = strIMPR.strPOSID.Split('-'); //intJC = strjc.Length; intJC = strIMPR.strPOSID.Length / 2; //添加数据 strBuilder.Append(" INSERT INTO HB_XMTZ"); strBuilder.Append("(XMTZ_ID,XMTZ_TZCXMC,XMTZ_DWBS,XMTZ_YEAR,XMTZ_TZCXDXH,XMTZ_TZJDMC,XMTZ_TZJDJE,XMTZ_CCDJ)"); strBuilder.Append(" VALUES("); strBuilder.Append("SQ_XMTZ.NEXTVAL,"); strBuilder.Append("'" + strIMPR.strPRNAM + "',"); strBuilder.Append("'" + strIMPR.strPOSID + "',"); strBuilder.Append("'" + strIMPR.strGJAHR + "',"); strBuilder.Append("'" + strIMPR.strOBJNR + "',"); strBuilder.Append("'" + strPOST1 + "',"); strBuilder.Append("'" + (string.IsNullOrEmpty(strWTGES) ? "0.00" : ((Convert.ToDecimal(strWTGES) / 10000).ToString("F2"))) + "',"); strBuilder.Append("'" + intJC.ToString() + "'"); strBuilder.Append(");"); } strBuilder.Append(" End;"); //SQL完成 try { //数据提交 Result = ClsUtility.ExecuteSqlToDb(strBuilder.ToString()); } catch (Exception exception5) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "xmtz", "ALL", p_para.Sap_AEDAT, "插入hb_xmtz表发生异常:" + exception5); } } return(Result); }
public bool SAPLoadData(ClsSAPDataParameter p_para) { bool Result = true; m_Conn = ClsUtility.GetConn(); string m_dat = p_para.Sap_AEDAT.Substring(0, 6); string m_year = p_para.Sap_AEDAT.Substring(0, 4); string strCovpSQL = "select b.OBJNR, POST1, POSID, PRART, STUFE, PBUKR, b.WTGBTR from (select T.OBJNR, sum(T.WTGBTR) as WTGBTR from COVP T WHERE (T.WRTTP = '04' OR T.WRTTP = '11' )AND T.KSTAR NOT IN (SELECT KSTAR FROM TBPFK) and substr(BUDAT, 0, 6) = '" + m_dat + "' group by T.OBJNR) b , PRPS p where b.OBJNR=p.OBJNR"; DataTable dtCOVP = m_Conn.GetSqlResultToDt(strCovpSQL); int commitcount = 0; strBuilder.Length = 0; strBuilder.Append(" Begin "); //开始执行SQL foreach (DataRow _cdr in dtCOVP.Rows) { XMZJ strXMZJ = new XMZJ(); strXMZJ.strOBJNR = _cdr["OBJNR"].ToString(); strXMZJ.strCB = _cdr["WTGBTR"].ToString(); strXMZJ.strDATE = m_dat; #region PRPS 项目基础信息 strXMZJ.strOBJNR = _cdr["OBJNR"].ToString(); strXMZJ.strPOSID = _cdr["POSID"].ToString(); strXMZJ.strPOST1 = _cdr["POST1"].ToString(); strXMZJ.strPRART = _cdr["PRART"].ToString(); strXMZJ.strSTUFE = _cdr["STUFE"].ToString(); strXMZJ.strPBUKR = _cdr["PBUKR"].ToString(); try { //所属单位 strXMZJ.strPBUKRMC = m_Conn.GetSqlResultToStr("select BUTXT from T001 where bukrs='" + strXMZJ.strPBUKR + "'"); if (strXMZJ.strPBUKR.Substring(0, 2) == "16") { strXMZJ.strPBUKRSX = "上市"; } else if (strXMZJ.strPBUKR.Substring(0, 2) == "C3") { strXMZJ.strPBUKRSX = "未上市"; } else if (strXMZJ.strPBUKR.Substring(0, 2) == "M4") { strXMZJ.strPBUKRSX = "矿区"; } } catch (Exception exception1) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "xmzj", "ALL", p_para.Sap_AEDAT, "插入hb_xmzj表过程中查询T001表发生异常:\t\n" + exception1); return(Result); } try { //状态 string strSTAT = m_Conn.GetSqlResultToStr("select STAT from JEST t where STAT like 'E%' and (INACT='' or INACT is null) and OBJNR='" + strXMZJ.strOBJNR + "'"); strXMZJ.strTXT04 = m_Conn.GetSqlResultToStr("select TXT04 from TJ30T t where STSMA like 'ZPSHB%' and t.estat = '" + strSTAT + "'"); } catch (Exception exception2) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "xmzj", "ALL", p_para.Sap_AEDAT, "插入hb_xmzj表过程中查询JEST或TJ30T表发生异常:\t\n" + exception2); return(Result); } #endregion #region cpov70 合同成本 //杜杨ERP2.0 string strCOVPsql70 = "select sum(T.WTGBTR) as WTGBTR from COVP T WHERE (T.WRTTP = '04' OR T.WRTTP = '11' ) AND T.EBELN LIKE '73%' AND T.KSTAR NOT IN (SELECT KSTAR FROM TBPFK) and substr(BUDAT, 0, 6) = '" + m_dat + "' and T.OBJNR='" + strXMZJ.strOBJNR + "'"; strXMZJ.strHTCB = m_Conn.GetSqlResultToStr(strCOVPsql70); #endregion #region ekkn 合同金额 string strEKKNsql = "select sum(netwr) from ekkn where replace(replace(replace(ps_psp_pnr,'-',''),'.',''),' ','' )='" + strXMZJ.strPOSID + "' and substr(aedat, 0, 6)='" + m_dat + "'"; strXMZJ.strHTJE = m_Conn.GetSqlResultToStr(strEKKNsql); #endregion #region 承诺 if (string.IsNullOrEmpty(strXMZJ.strHTJE)) { strXMZJ.strHTJE = "0.00"; } if (string.IsNullOrEmpty(strXMZJ.strHTCB)) { strXMZJ.strHTCB = "0.00"; } //承诺=合同金额-合同成本 strXMZJ.strCN = (Convert.ToDecimal(strXMZJ.strHTJE) - Convert.ToDecimal(strXMZJ.strHTCB)).ToString(); #endregion //添加数据 strBuilder.Append(" INSERT INTO HB_XMZJ"); strBuilder.Append("(XMZJ_ID,ZMZJ_YEAR,XMZJ_DATE,XMZJ_WBSBM,XMZJ_WBSMC,XMZJ_LX,XMZJ_CC,XMZJ_GSBM,"); strBuilder.Append("XMZJ_GSMC,XMZJ_GSSX,XMZJ_ZT,XMZJ_TZ,XMZJ_CB,XMZJ_HTCB,XMZJ_HTJE,XMZJ_CN)"); strBuilder.Append(" VALUES("); strBuilder.Append("SQ_XMZJ.NEXTVAL,"); strBuilder.Append("'" + "" + "',"); strBuilder.Append("'" + m_dat + "',"); strBuilder.Append("'" + strXMZJ.strPOSID + "',"); strBuilder.Append("'" + strXMZJ.strPOST1 + "',"); strBuilder.Append("'" + strXMZJ.strPRART + "',"); strBuilder.Append("'" + strXMZJ.strSTUFE + "',"); strBuilder.Append("'" + strXMZJ.strPBUKR + "',"); strBuilder.Append("'" + strXMZJ.strPBUKRMC + "',"); strBuilder.Append("'" + strXMZJ.strPBUKRSX + "',"); strBuilder.Append("'" + strXMZJ.strTXT04 + "',"); strBuilder.Append("'" + (string.IsNullOrEmpty(strXMZJ.strTZ) ? "0.00" : (Convert.ToDecimal(strXMZJ.strTZ) / 10000).ToString("F2")) + "',"); strBuilder.Append("'" + (string.IsNullOrEmpty(strXMZJ.strCB) ? "0.00" : (Convert.ToDecimal(strXMZJ.strCB) / 10000).ToString("F2")) + "',"); strBuilder.Append("'" + (string.IsNullOrEmpty(strXMZJ.strHTCB) ? "0.00" : (Convert.ToDecimal(strXMZJ.strHTCB) / 10000).ToString("F2")) + "',"); strBuilder.Append("'" + (string.IsNullOrEmpty(strXMZJ.strHTJE) ? "0.00" : (Convert.ToDecimal(strXMZJ.strHTJE) / 10000).ToString("F2")) + "',"); strBuilder.Append("'" + (string.IsNullOrEmpty(strXMZJ.strCN) ? "0.00" : (Convert.ToDecimal(strXMZJ.strCN) / 10000).ToString("F2")) + "'"); strBuilder.Append(");"); if (commitcount == 0) { strBuilder.Insert(8, " DELETE FROM HB_XMZJ WHERE xmzj_date='" + m_dat + "' ;"); } commitcount++; if (commitcount % 2000 == 0) { strBuilder.Append(" End;"); //SQL完成 try { //数据提交 Result = ClsUtility.ExecuteSqlToDb(strBuilder.ToString()); strBuilder.Clear(); strBuilder.Append(" Begin "); //开始执行SQL } catch (Exception exception5) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "xmtz", "ALL", p_para.Sap_AEDAT, "插入hb_xmtz表发生异常:" + exception5); } } } if (strBuilder.ToString().Length > 9) { strBuilder.Append(" End;"); //SQL完成 Result = ClsUtility.ExecuteSqlToDb(strBuilder.ToString()); strBuilder.Clear(); } string strBPJASQL = "select b.OBJNR, POST1, POSID, PRART, STUFE, PBUKR, b.WTJHR from (select T.OBJNR, sum(T.WTJHR) as WTJHR from BPJA T where T.WRTTP = '41'and T.GJAHR = '" + m_year + "' group by T.OBJNR) b,PRPS p where p.OBJNR = b.OBJNR"; DataTable dtBPJA = m_Conn.GetSqlResultToDt(strBPJASQL); strBuilder.Clear(); commitcount = 0; strBuilder.Append(" Begin "); //开始执行SQL foreach (DataRow _cdr in dtBPJA.Rows) { string strOBJNR = _cdr["OBJNR"].ToString(); XMZJ strXMZJ = new XMZJ(); strXMZJ.strTZ = _cdr["WTJHR"].ToString(); strXMZJ.strOBJNR = _cdr["OBJNR"].ToString(); strXMZJ.strPOSID = _cdr["POSID"].ToString(); strXMZJ.strPOST1 = _cdr["POST1"].ToString(); strXMZJ.strPRART = _cdr["PRART"].ToString(); strXMZJ.strSTUFE = _cdr["STUFE"].ToString(); strXMZJ.strPBUKR = _cdr["PBUKR"].ToString(); try { //所属单位 strXMZJ.strPBUKRMC = m_Conn.GetSqlResultToStr("select BUTXT from T001 where bukrs='" + strXMZJ.strPBUKR + "'"); if (strXMZJ.strPBUKR.Substring(0, 2) == "16") { strXMZJ.strPBUKRSX = "上市"; } else if (strXMZJ.strPBUKR.Substring(0, 2) == "C3") { strXMZJ.strPBUKRSX = "未上市"; } else if (strXMZJ.strPBUKR.Substring(0, 2) == "M4") { strXMZJ.strPBUKRSX = "矿区"; } } catch (Exception exception1) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "xmzj", "ALL", p_para.Sap_AEDAT, "插入hb_xmzj表过程中查询T001表发生异常:\t\n" + exception1); return(Result); } try { //状态 string strSTAT = m_Conn.GetSqlResultToStr("select STAT from JEST t where STAT like 'E%' and (INACT='' or INACT is null) and OBJNR='" + strXMZJ.strOBJNR + "'"); strXMZJ.strTXT04 = m_Conn.GetSqlResultToStr("select TXT04 from TJ30T t where STSMA like 'ZPSHB%' and t.estat = '" + strSTAT + "'"); } catch (Exception exception2) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "xmzj", "ALL", p_para.Sap_AEDAT, "插入hb_xmzj表过程中查询JEST或TJ30T表发生异常:\t\n" + exception2); return(Result); } //添加数据 strBuilder.Append(" INSERT INTO HB_XMZJ_TZ"); strBuilder.Append("(XMZJ_ID,ZMZJ_YEAR,XMZJ_DATE,XMZJ_WBSBM,XMZJ_WBSMC,XMZJ_LX,XMZJ_CC,XMZJ_GSBM,"); strBuilder.Append("XMZJ_GSMC,XMZJ_GSSX,XMZJ_ZT,XMZJ_TZ,XMZJ_CB,XMZJ_HTCB,XMZJ_HTJE,XMZJ_CN)"); strBuilder.Append(" VALUES("); strBuilder.Append("SQ_XMZJ.NEXTVAL,"); strBuilder.Append("'" + m_year + "',"); strBuilder.Append("'" + m_dat + "',"); strBuilder.Append("'" + strXMZJ.strPOSID + "',"); strBuilder.Append("'" + strXMZJ.strPOST1 + "',"); strBuilder.Append("'" + strXMZJ.strPRART + "',"); strBuilder.Append("'" + strXMZJ.strSTUFE + "',"); strBuilder.Append("'" + strXMZJ.strPBUKR + "',"); strBuilder.Append("'" + strXMZJ.strPBUKRMC + "',"); strBuilder.Append("'" + strXMZJ.strPBUKRSX + "',"); strBuilder.Append("'" + strXMZJ.strTXT04 + "',"); strBuilder.Append("'" + (string.IsNullOrEmpty(strXMZJ.strTZ) ? "0.00" : (Convert.ToDecimal(strXMZJ.strTZ) / 10000).ToString("F2")) + "',"); strBuilder.Append("'" + (string.IsNullOrEmpty(strXMZJ.strCB) ? "0.00" : (Convert.ToDecimal(strXMZJ.strCB) / 10000).ToString("F2")) + "',"); strBuilder.Append("'" + (string.IsNullOrEmpty(strXMZJ.strHTCB) ? "0.00" : (Convert.ToDecimal(strXMZJ.strHTCB) / 10000).ToString("F2")) + "',"); strBuilder.Append("'" + (string.IsNullOrEmpty(strXMZJ.strHTJE) ? "0.00" : (Convert.ToDecimal(strXMZJ.strHTJE) / 10000).ToString("F2")) + "',"); strBuilder.Append("'" + (string.IsNullOrEmpty(strXMZJ.strCN) ? "0.00" : (Convert.ToDecimal(strXMZJ.strCN) / 10000).ToString("F2")) + "'"); strBuilder.Append(");"); if (commitcount == 0) { strBuilder.Insert(8, " DELETE FROM HB_XMZJ_TZ WHERE zmzj_year='" + m_year + "' ;"); } commitcount++; if (commitcount % 2000 == 0) { strBuilder.Append(" End;"); //SQL完成 try { //数据提交 Result = ClsUtility.ExecuteSqlToDb(strBuilder.ToString()); strBuilder.Clear(); strBuilder.Append(" Begin "); //开始执行SQL } catch (Exception exception5) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "xmtz", "ALL", p_para.Sap_AEDAT, "插入hb_xmtz表发生异常:" + exception5); } } } if (strBuilder.ToString().Length > 9) { strBuilder.Append(" End;"); //SQL完成 Result = ClsUtility.ExecuteSqlToDb(strBuilder.ToString()); strBuilder.Clear(); } 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); }