public bool SAPLoadData(ClsSAPDataParameter p_para) { bool Result = true; m_Conn = ClsUtility.GetConn(); DataTable dtSWKC = new DataTable(); try { string stedate = p_para.Sap_AEDAT.Substring(0, 6); //是第一次转换,先删除操作 ,再insert // 插入CONVERT_ZWKC表模型 string strSqlEkko = @" begin delete from CONVERT_ZWKC; INSERT INTO CONVERT_ZWKC (BWKEY,BWKEY_NAME,MATNR,SALK3,DLCODE,DLNAME,ZLCODE,ZLNAME,XLCODE,XLNAME,MATKL,PMNAME,LBKUM,DANJIA,DLDATE) SELECT A.BWKEY,D.DW_NAME,A.MATNR,A.SALK3,C.DLCODE,C.DLNAME,C.ZLCODE,C.ZLNAME,C.XLCODE,C.XLNAME,B.MATKL,C.PMNAME,A.LBKUM,A.SALK3/A.LBKUM,'" + DateTime.Now.ToString("yyyyMMdd") + "'"; strSqlEkko += @" FROM MBEW A JOIN MARA B ON A.MATNR=B.MATNR JOIN WZ_WLZ C ON C.PMCODE=B.MATKL JOIN WZ_DW D ON D.DW_CODE=A.BWKEY where A.SALK3>0 ; commit;end ;"; Result = m_Conn.ExecuteSql(strSqlEkko); } catch (Exception exception) { Result = false; ClsErrorLogInfo.WriteSapLog("1", "CONVERT_ZWKC", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "插入CONVERT_ZWKC表过程中发生异常:\t\n" + exception); return(Result); } return(Result); }
public bool SAPLoadData(ClsSAPDataParameter p_para) { bool Result = true; m_Conn = ClsUtility.GetConn(); string sqldelete = "BEGIN delete from CONVERT_CKJE;COMMIT;END; "; try { Result = m_Conn.ExecuteSql(sqldelete); } catch (Exception ex) { ClsErrorLogInfo.WriteSapLog("1", "RKJE", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "模型转换-删除CONVERT_RKJE表发生异常:" + ex.Message); return(false); } if (Result) { try { string sql = " begin INSERT INTO CONVERT_CKJE(WERKS,WERKS_NAME,LGORT,LGORT_NAME,ZCKTZD,ZCITEM,MBLNR,ZEILE,JE,BUDAT_MKPF,MENGE,DKCODE,MEINS,DLDATE)"; sql += " SELECT A.WERKS, C.DW_NAME ,A.LGORT, D.KCDD_NAME,A.ZCKTZD,A.ZCITEM,A.MBLNR, A.ZEILE,B.DMBTR,B.BUDAT_MKPF,B.MENGE,D.CKH,F.JBJLDW,'" + DateTime.Now.ToString("yyyyMMdd") + "'"; sql += " FROM ZC10MMDG078 A"; sql += " JOIN MSEG B ON A.MBLNR=B.MBLNR AND A.ZEILE=B.ZEILE"; sql += " JOIN WZ_DW C ON A.WERKS=C.DW_CODE"; sql += " JOIN WZ_KCDD D ON A.WERKS=D.DWCODE AND A.LGORT=D.KCDD_CODE" + " JOIN MARA E ON A.MATNR=E.MATNR JOIN WZ_WLZ F ON F.PMCODE = E.MATKL " + " ; commit;end;"; Result = m_Conn.ExecuteSql(sql); if (Result) { ClsErrorLogInfo.WriteSapLog("1", "CKJE", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "模型转换-插入CONVERT_CKJE表成功"); } else { ClsErrorLogInfo.WriteSapLog("1", "CKJE", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "模型转换-插入CONVERT_CKJE表失败"); } } catch (Exception ex) { ClsErrorLogInfo.WriteSapLog("1", "CKJE", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "模型转换-插入CONVERT_CKJE表发生异常:" + ex.Message); return(false); } } else { ClsErrorLogInfo.WriteSapLog("1", "CKJE", "ALL", p_para.Sap_AEDAT, "模型转换-删除CONVERT_CKJE表失败"); } return(Result); }
/// <summary> /// 数据 /// </summary> /// <param name="p_SapName">接口类型名称</param> /// <param name="p_SapPara">参数数据0.创建时间,1.单位</param> public static bool SapLoadExecute(string p_SapName, string[] p_SapPara) { bool Result = true; //生成对象 ISAPLoadInterface sap = ClsUtility.GetLoadInter(p_SapName); ClsSAPDataParameter Sap_Para = ClsUtility.GetSapConParameter(p_SapPara); //完成日志 if (sap.SAPLoadData(Sap_Para)) { ClsLogInfo.WriteSapLog("1", p_SapName, Sap_Para.Sap_AEDAT, "申请开始加载!"); } else { Result = false; ClsLogInfo.WriteSapLog("1", p_SapName, Sap_Para.Sap_AEDAT, "申请加载失败!"); } return(Result); }
/// <summary> /// 数据获取执行 /// </summary> /// <param name="p_SapName">执行接口名称</param> /// <param name="p_SapPara">参数数据0.创建时间,1.单位</param> /// <param name="p_SapPara">选择接口内的数据,如果为NULL 说明不是选择具体接口,如果存在说明需要获取具体接口信息</param> public static bool SapOperateExecute(string p_SapName, string[] p_SapPara, string [] p_SapSelect) { bool Result = true; //生成对象 ISAPInterface sap = ClsUtility.GetInter(p_SapName); ClsSAPDataParameter Sap_Para = ClsUtility.GetSapConParameter(p_SapPara); Sap_Para.Sap_Select = p_SapSelect; //完成日志 if (sap.GetSAPData(Sap_Para)) { ClsLogInfo.WriteSapLog("0", p_SapName, Sap_Para.Sap_AEDAT, "申请开始下载!"); } else { Result = false; ClsLogInfo.WriteSapLog("0", p_SapName, Sap_Para.Sap_AEDAT, "申请下载失败!"); } 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; try { string dldate = p_para.Sap_AEDAT; m_Conn = ClsUtility.GetConn(); strBuilder.Append("BEGIN "); strBuilder.Append(" DELETE FROM CONVERT_BGYGZL ;");//先按选择日期删除数据 strBuilder.Append(" COMMIT ;END;"); if (m_Conn.ExecuteSql(strBuilder.ToString())) { strBuilder.Length = 0; strBuilder.Append(" begin "); strBuilder.Append(@" INSERT INTO CONVERT_BGYGZL(WERKS,WERKS_NAME,TZD,ITEMS,MATNR,MATKL,PMNAME,MAKTX, JBJLDW,NSOLM,ERNAME,WORKER_NAME,TZDTYPE,DLDATE,ERDAT) select A.WERKS,D.DW_NAME,A.ZDHTZD,A.ZITEM,A.MATNR,E.MATKL,F.PMNAME,E.MAKTX, F.JBJLDW,B.NSOLM,B.ERNAM,C.WORKER_NAME,1,'" + DateTime.Now.ToString("yyyyMMdd") + "',ERDAT"); strBuilder.Append(@" from ZC10MMDG072 A join ZC10MMDG085A B on A.ZDHTZD=B.ZDHTZD and A.ZITEM=B.ZITEM join WZ_BGY C on C.WORKER_CODE=B.ERNAM join WZ_DW D ON D.DW_CODE=A.WERKS JOIN MARA E ON E.MATNR=A.MATNR JOIN WZ_WLZ F ON F.PMCODE=E.MATKL ;");//根据日期插入入库单相关保管员信息 strBuilder.Append(" commit; end; "); if (m_Conn.ExecuteSql(strBuilder.ToString())) { ClsErrorLogInfo.WriteSapLog("1", "RKJE", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "模型转换-删除CONVERT_BGYGZL表入库通知单保管员信息成功"); strBuilder.Length = 0; strBuilder.Append(" begin "); strBuilder.Append(@" INSERT INTO CONVERT_BGYGZL(WERKS,WERKS_NAME,TZD,ITEMS,MATNR,MATKL,PMNAME,MAKTX, JBJLDW,NSOLM,ERNAME,WORKER_NAME,TZDTYPE,DLDATE,ERDAT) select A.WERKS,D.DW_NAME,A.ZCKTZD,A.ZCITEM,A.MATNR,E.MATKL,F.PMNAME,E.MAKTX, F.JBJLDW,B.NSOLM,B.ERNAM,C.WORKER_NAME,2,'" + DateTime.Now.ToString("yyyyMMdd") + "',ERDAT"); strBuilder.Append(@" from ZC10MMDG078 A join ZC10MMDG085A B on A.ZCKTZD=B.ZCKTZD and A.ZCITEM=B.ZCITEM join WZ_BGY C on C.WORKER_CODE=B.ERNAM join WZ_DW D ON D.DW_CODE=A.WERKS JOIN MARA E ON E.MATNR=A.MATNR JOIN WZ_WLZ F ON F.PMCODE=E.MATKL ;");//根据日期插入出库单相关保管员信息 strBuilder.Append(" commit; end; "); if (m_Conn.ExecuteSql(strBuilder.ToString())) { Result = true; ClsErrorLogInfo.WriteSapLog("1", "RKJE", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "模型转换-删除CONVERT_BGYGZL表出库通知单保管员信息成功"); } else { Result = false; ClsErrorLogInfo.WriteSapLog("1", "RKJE", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "模型转换-删除CONVERT_BGYGZL表出库通知单保管员信息失败"); } } else { Result = false; ClsErrorLogInfo.WriteSapLog("1", "RKJE", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "模型转换-删除CONVERT_BGYGZL表入库通知单保管员信息失败"); } } else { Result = false; ClsErrorLogInfo.WriteSapLog("1", "RKJE", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "模型转换-删除CONVERT_BGYGZL表失败"); } } catch (Exception ex) { ClsErrorLogInfo.WriteSapLog("1", "RKJE", "ALL", p_para.Sap_AEDAT, "模型转换-插入CONVERT_BGYGZL表发生异常:" + ex.Message); return(false); } return(Result); }
public bool SAPLoadData(ClsSAPDataParameter p_para) { string dldate = p_para.Sap_AEDAT; bool Result = true; m_Conn = ClsUtility.GetConn(); try { string week = DateTime.Today.DayOfWeek.ToString(); if (week == "Thursday") { string sql = " begin insert into CONVERT_SWKC_RECORD SELECT * FROM CONVERT_SWKC ;COMMIT; END; "; m_Conn.ExecuteSql(sql); } //string sqlLQUA = @"begin delete from CONVERT_SWKC where KCTYPE=0; // INSERT INTO CONVERT_SWKC (WERKS,MATKL,MATNR,MAKTX, // MEINS,GESME,LGORT,LGPLA,ERDAT,WERKS_NAME,LGORT_NAME,ZSTATUS,YXQ,DLDATE,KCTYPE) // SELECT A.WERKS,C.MATKL,A.MATNR,C.MAKTX, // F.JBJLDW,A.GESME,A.LGORT,substr(A.LGPLA,0,4),case when SUBSTR(A.WDATU, 0, 8)='00000000' then A.BDATU ELSE A.WDATU END,D.DW_NAME,E.KCDD_NAME,'04','',to_char(sysdate,'yyyymmdd'),0 // FROM LQUA A JOIN( // select WERKS,MATNR,substr(lgpla,0,2) DKCODE,LGORT,max(BDATU) BDATU,max(LQNUM) LQNUM // from LQUA where regexp_like(lgpla,'^[0-9]+[0-9]$') // group by werks,matnr,LGORT,substr(lgpla,0,2)) B ON A.WERKS=B.WERKS AND A.MATNR=B.MATNR AND substr(A.lgpla,0,2)=B.DKCODE AND A.LGORT=B.LGORT AND A.BDATU=B.BDATU AND A.LQNUM=B.LQNUM // JOIN MARA C ON A.MATNR=C.MATNR // join WZ_DW D ON D.DW_CODE=A.Werks // LEFT join WZ_KCDD E ON E.DWCODE=A.WERKS AND E.KCDD_CODE=A.LGORT // JOIN WZ_WLZ F ON F.PMCODE=C.MATKL ; COMMIT; end;"; //查询lqua表库存,lapla 为数字得,子查询是按物料编码取最新一条数据,主要取里面得BDATU,WDATU //regexp_like(lgpla,'^[0-9]+[0-9]$')过滤lapla不是数字的 string sqlLQUA = @" SELECT A.WERKS,C.MATKL,A.MATNR,trim(C.MAKTX)MAKTX, F.JBJLDW MEINS,nvl(A.GESME,0)GESME,A.LGORT,substr(A.LGPLA,0,4)LGPLA,case when SUBSTR(A.WDATU, 0, 8)='00000000' then A.BDATU ELSE A.WDATU END ERDAT, D.DW_NAME WERKS_NAME,E.KCDD_NAME,'04' ZSTATUS,''YXQ,to_char(sysdate,'yyyymmdd')DLDATE,0 KCTYPE FROM LQUA A JOIN( select WERKS,MATNR,substr(lgpla,0,4) DKCODE,LGORT,max(LQNUM) LQNUM,max(BDATU)BDATU from LQUA where regexp_like(lgpla,'^[0-9]+[0-9]$') group by werks,matnr,LGORT,substr(lgpla,0,4)) B ON A.WERKS=B.WERKS AND A.MATNR=B.MATNR AND substr(A.lgpla,0,4)=B.DKCODE AND A.LGORT=B.LGORT AND A.BDATU=B.BDATU AND A.LQNUM=B.LQNUM JOIN MARA C ON A.MATNR=C.MATNR join WZ_DW D ON D.DW_CODE=A.Werks LEFT join WZ_KCDD E ON E.DWCODE=A.WERKS AND E.KCDD_CODE=A.LGORT JOIN WZ_WLZ F ON F.PMCODE=C.MATKL where regexp_like(A.LGPLA,'^[0-9]+[0-9]$') and substr(LGPLA,0,1)='0' AND substr(A.LGTYP,0,1)<>'9' "; //查询lqua表库存,lapla 为数字得,子查询是按物料编码取最新一条数据,主要取里面得BDATU,WDATU //regexp_like(lgpla,'^[0-9]+[0-9]$')过滤lapla不是数字的 DataTable dt = m_Conn.GetSqlResultToDt(sqlLQUA); if (dt != null && dt.Rows.Count > 0) { string sqlruku = @"select nvl(C.MENGE,0)MENGE,C.BUDAT_MKPF,A.MATNR,A.ZDHTZD, A.ZITEM,substr(B.LGPLA,0,4)LGPLA,A.WERKS,A.LGORT from ZC10MMDG072 A JOIN ZC10MMDG085B B ON A.ZDHTZD=B.ZDHTZD AND A.ZITEM=B.ZITEM and regexp_like(B.lgpla,'^[0-9]+[0-9]$') JOIN MSEG C ON C.MBLNR=A.MBLNR AND C.ZEILE=A.ZEILE and C.BWART IN('105','101') WHERE exists (SELECT 1 FROM LQUA d WHERE d.MATNR=A.MATNR and regexp_like(d.lgpla,'^[0-9]+[0-9]$') ) order by C.BUDAT_MKPF desc,a.matnr,a.zdhtzd,a.zitem "; DataTable dtruku = m_Conn.GetSqlResultToDt(sqlruku); StringBuilder sb = new StringBuilder(); string sqlinsert = "INSERT INTO CONVERT_SWKC (WERKS,MATKL,MATNR,MAKTX,MEINS,GESME,LGORT,LGPLA,ERDAT,WERKS_NAME,LGORT_NAME,ZSTATUS,YXQ,DLDATE,KCTYPE)values ('"; foreach (DataRow row in dt.Rows) { DataRow[] rowsRK = dtruku.Select("MATNR='" + row["MATNR"] + "' and WERKS='" + row["WERKS"] + "' and LGORT='" + row["LGORT"] + "' and LGPLA='" + row["LGPLA"] + "' "); if (rowsRK != null && rowsRK.Length > 0) { double sumSL = 0; int flag = 0; foreach (DataRow rowRK in rowsRK) { sumSL = sumSL + double.Parse(rowRK["MENGE"].ToString()); if (sumSL >= double.Parse(row["GESME"].ToString())) { sb.AppendLine(""); sb.Append(sqlinsert); sb.Append(row["WERKS"].ToString() + "','"); sb.Append(row["MATKL"].ToString() + "','"); sb.Append(row["MATNR"].ToString() + "','"); sb.Append(row["MAKTX"].ToString().Replace("/*", "*").Replace("&", " ").Replace("'", "").Trim() + "','"); sb.Append(row["MEINS"].ToString() + "','"); sb.Append(row["GESME"].ToString() + "','"); sb.Append(row["LGORT"].ToString() + "','"); sb.Append(row["LGPLA"].ToString() + "','"); sb.Append(rowRK["BUDAT_MKPF"].ToString() + "','"); sb.Append(row["WERKS_NAME"].ToString() + "','"); sb.Append(row["KCDD_NAME"].ToString() + "','"); sb.Append(row["ZSTATUS"].ToString() + "','"); sb.Append(row["YXQ"].ToString() + "','"); sb.Append(row["DLDATE"].ToString() + "',"); sb.Append(row["KCTYPE"].ToString() + ");"); flag = 1; break; } } if (flag == 0) { sb.AppendLine(""); sb.Append(sqlinsert); sb.Append(row["WERKS"].ToString() + "','"); sb.Append(row["MATKL"].ToString() + "','"); sb.Append(row["MATNR"].ToString() + "','"); sb.Append(row["MAKTX"].ToString().Replace("/*", "*").Replace("&", " ").Replace("'", "").Trim() + "','"); sb.Append(row["MEINS"].ToString() + "','"); sb.Append(row["GESME"].ToString() + "','"); sb.Append(row["LGORT"].ToString() + "','"); sb.Append(row["LGPLA"].ToString() + "','"); sb.Append(row["ERDAT"].ToString() + "','"); sb.Append(row["WERKS_NAME"].ToString() + "','"); sb.Append(row["KCDD_NAME"].ToString() + "','"); sb.Append(row["ZSTATUS"].ToString() + "','"); sb.Append(row["YXQ"].ToString() + "','"); sb.Append(row["DLDATE"].ToString() + "',"); sb.Append(row["KCTYPE"].ToString() + ");"); } } else { sb.AppendLine(""); sb.Append(sqlinsert); sb.Append(row["WERKS"].ToString() + "','"); sb.Append(row["MATKL"].ToString() + "','"); sb.Append(row["MATNR"].ToString() + "','"); sb.Append(row["MAKTX"].ToString().Replace("/*", "*").Replace("&", " ").Replace("'", "").Trim() + "','"); sb.Append(row["MEINS"].ToString() + "','"); sb.Append(row["GESME"].ToString() + "','"); sb.Append(row["LGORT"].ToString() + "','"); sb.Append(row["LGPLA"].ToString() + "','"); sb.Append(row["ERDAT"].ToString() + "','"); sb.Append(row["WERKS_NAME"].ToString() + "','"); sb.Append(row["KCDD_NAME"].ToString() + "','"); sb.Append(row["ZSTATUS"].ToString() + "','"); sb.Append(row["YXQ"].ToString() + "','"); sb.Append(row["DLDATE"].ToString() + "',"); sb.Append(row["KCTYPE"].ToString() + ");"); } } string insertSWKC = @"begin delete from CONVERT_SWKC where KCTYPE=0;"; insertSWKC += " COMMIT; end;"; Result = m_Conn.ExecuteSql(insertSWKC); insertSWKC = " begin "; insertSWKC += sb.ToString(); insertSWKC += " COMMIT; end;"; Result = m_Conn.ExecuteSql(insertSWKC); if (Result) { ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "插入CONVERT_SWKC表上架部分成功"); } else { ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "插入CONVERT_SWKC表上架部分失"); } } else { ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "插入CONVERT_SWKC表上架部分失败--没数据"); } string sqlSWKCDetail = @" begin DELETE FROM CONVERT_SWKCDETAIL; INSERT INTO CONVERT_SWKCDETAIL(WERKS,WERKS_NAME,LGORT,MATNR,GESME,ERDATE,DLNAME,PMNAME,DLDATE) SELECT A.WERKS,D.DW_NAME,A.LGORT,A.MATNR,A.GESME ,case when SUBSTR(A.WDATU, 0, 8)='00000000' then A.BDATU ELSE A.WDATU END,F.DLNAME,F.PMNAME,to_char(sysdate,'yyyymmdd') FROM LQUA A JOIN( select WERKS,MATNR,substr(lgpla,0,2) DKCODE,LGORT,max(BDATU) BDATU,max(LQNUM) LQNUM from LQUA where regexp_like(lgpla,'^[0-9]+[0-9]$') --AND matnr='000000011000683835' group by werks,matnr,LGORT,substr(lgpla,0,2)) B ON A.WERKS=B.WERKS AND A.MATNR=B.MATNR AND substr(A.lgpla,0,2)=B.DKCODE AND A.LGORT=B.LGORT AND A.BDATU=B.BDATU AND A.LQNUM=B.LQNUM JOIN MARA C ON A.MATNR=C.MATNR join WZ_DW D ON D.DW_CODE=A.Werks JOIN WZ_WLZ F ON F.PMCODE=C.MATKL ;COMMIT;end;"; Result = m_Conn.ExecuteSql(sqlSWKCDetail); if (Result) { ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKCDETAIL", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "插入CONVERT_SWKCDETAIL表上架部分成功"); } else { ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKCDETAIL", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "插入CONVERT_SWKCDETAIL表上架部分失败"); } strBuilder.Length = 0; strBuilder.Append(@" begin INSERT INTO CONVERT_SWKC (WERKS,ZDHTZD,ZITEM,MATKL,MATNR,MAKTX, MEINS,GESME,LGORT,LGPLA,ERDAT,WERKS_NAME,LGORT_NAME,ZSTATUS,YXQ,DLDATE,KCTYPE) select t.WERKS,t.ZDHTZD,t.ZITEM,d.MATKL, t.MATNR,trim(d.MAKTX)MAKTX, e.JBJLDW,k.VMENGE01,t.LGORT,'' LGPLA ,t.ZCJRQ, b.DW_NAME,c.KCDD_NAME,'03','','" + dldate + "',0 "); strBuilder.Append(@" from ZC10MMDG072 t join ZC10MMDG076 k on t.ZDHTZD =k.ZDHTZD and t.ZITEM=k.ZITEM join WZ_DW b ON b.DW_CODE=t.Werks join WZ_KCDD c ON c.DWCODE=t.WERKS AND c.KCDD_CODE=t.LGORT join MARA d on d.matnr=t.matnr join WZ_WLZ e on e.PMCODE=d.MATKL where ZSTATUS ='03' and k.ZJYRQ between TO_CHAR(trunc(sysdate)-14,'yyyymmdd') and TO_CHAR(sysdate,'yyyymmdd') and k.ZJYRQ<>'00000000' ; commit; end; ");//插入质检状态的入库单作为另一部分库存 Result = m_Conn.ExecuteSql(strBuilder.ToString()); if (Result) { ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "插入CONVERT_SWKC表质检部分成功"); } else { ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "插入CONVERT_SWKC表质检部分失败"); } string sqlupdateje = @"UPDATE CONVERT_SWKCDETAIL A SET A.SCJE=(select ROUND(B.SALK3/LBKUM*A.GESME,2) FROM MBEW B WHERE B.SALK3>0 AND A.MATNR=B.MATNR AND A.WERKS=B.BWKEY)"; if (!m_Conn.ExecuteSql(sqlupdateje)) { ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKCDETAIL", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "更新CONVERT_SWKCDETAIL表金额失败"); } ClsDataLoadBAOBIAO.SAPLoadData(p_para);//下架未过账库存金额模型 } catch (Exception ex) { strBuilder.Length = 0; ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "插入CONVERT_SWKC表发生异常:" + ex.Message); return(false); } return(Result); }