Пример #1
0
        public static void SAPLoadData(ClsSAPDataParameter p_para)
        {
            bool   Result = true;
            string dldate = p_para.Sap_AEDAT;

            m_Conn = ClsUtility.GetConn();
            string sqlDelete = "begin DELETE FROM CONVERT_SWKCDETAILCKJE;commit; end;";

            if (m_Conn.ExecuteSql(sqlDelete))
            {
                string sql = "BEGIN  INSERT INTO CONVERT_SWKCDETAILCKJE(WERKS,WERKS_NAME,LGORT,MATNR,GESME,ZCKTZD,ZITEM,DLNAME,PMNAME,DLDATE,CKJE) ";
                sql   += "select A.WERKS,D.DW_NAME,A.LGORT,A.MATNR,A.ZFHSL,A.ZCKTZD,A.ZCITEM,C.DLNAME,C.PMNAME,'" + DateTime.Now.ToString("yyyyMMdd") + "',ROUND(E.SALK3/LBKUM*A.ZFHSL,2)";
                sql   += @" from  ZC10MMDG078 A 
                    JOIN WZ_DW D ON A.WERKS = D.DW_CODE
                    JOIN MBEW E ON E.BWKEY = A.WERKS AND E.MATNR = A.MATNR AND E.SALK3 > 0
                    JOIN WZ_WLZ C ON C.PMCODE = A.MATKL
                    WHERE A.ZSTATUS = '03'; COMMIT; END ;";
                Result = m_Conn.ExecuteSql(sql);
                if (Result)
                {
                    ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKCDETAILCKJE", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "插入CONVERT_SWKCDETAILCKJE表成功");
                }
                else
                {
                    ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKCDETAILCKJE", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "插入CONVERT_SWKCDETAILCKJE表失败");
                }
            }
            else
            {
                ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKCDETAILCKJE", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "删除CONVERT_SWKCDETAILCKJE表失败");
            }
        }
Пример #2
0
        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);
        }
Пример #3
0
        public bool SAPLoadData(ClsSAPDataParameter p_para)
        {
            bool Result = true;



            return(Result);
        }
Пример #4
0
        public bool SAPLoadData(ClsSAPDataParameter p_para)
        {
            bool Result = true;

            m_Conn = ClsUtility.GetConn();
            string sqldelete = "begin delete from CONVERT_RKJE ;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_RKJE(WERKS,WERKS_NAME,LGORT,LGORT_NAME,ZDHTZD,ZITEM,MBLNR,ZEILE,JE,BUDAT_MKPF,MENGE,DKCODE,MEINS,DLDATE)";
                    sql += "  SELECT A.WERKS, C.DW_NAME ,A.LGORT, D.KCDD_NAME,A.ZDHTZD,A.ZITEM,A.MBLNR, A.ZEILE,B.DMBTR,B.BUDAT_MKPF,B.MENGE,D.CKH,F.JBJLDW,'" + DateTime.Now.ToString("yyyyMMdd") + "'";
                    sql += "  FROM ZC10MMDG072 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", "RKJE", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "模型转换-插入CONVERT_RKJE表成功");
                    }
                    else
                    {
                        ClsErrorLogInfo.WriteSapLog("1", "RKJE", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "模型转换-插入CONVERT_RKJE表失败");
                    }
                }
                catch (Exception ex)
                {
                    ClsErrorLogInfo.WriteSapLog("1", "RKJE", "ALL", p_para.Sap_AEDAT, "模型转换-插入CONVERT_RKJE表发生异常:" + ex.Message);
                    return(false);
                }
            }
            else
            {
                ClsErrorLogInfo.WriteSapLog("1", "RKJE", "ALL", p_para.Sap_AEDAT, "模型转换-删除CONVERT_RKJE表失败");
            }
            return(Result);
        }
Пример #5
0
        /// <summary>
        /// 获取SAP的参数数据信息
        /// </summary>
        /// <returns> 返回SAP参数类 </returns>
        public static ClsSAPDataParameter GetSapConParameter(string[] p_SapPara)
        {
            //数据库连接
            //ClsDBConnection Conn = GetConn();
            //string strSaoConn = Conn.GetSqlResultToStr(" SELECT T.SYS_VALUE FROM SAP_SYSCONFIG T WHERE T.SYS_CODE = 'ServicesSoap' ");
            //Conn.Dispose();

            //参数数据
            ClsSAPDataParameter m_conParamet = new ClsSAPDataParameter();

            //m_conParamet.Sap_Conn = strSaoConn;
            m_conParamet.Sap_AEDAT = p_SapPara.Length == 0 ? "" : p_SapPara[0];

            //op接口添加单位代码参数
            return(m_conParamet);
        }
Пример #6
0
        bool ISAPInterface.GetSAPData(ClsSAPDataParameter p_para)
        {
            bool Result = true;

            m_para = p_para;

            try
            {
            }
            catch (Exception exception)
            {
                Result = false;
                ClsErrorLogInfo.WriteSapLog("0", "ZP10PSIF013_PRPS", "ALL", m_para.Sap_AEDAT, "异步连接接口发生异常:" + exception);
            }

            return(Result);
        }
Пример #7
0
        /// <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);
        }
Пример #8
0
        /// <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);
        }
Пример #9
0
        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);
        }
Пример #10
0
        public bool SAPLoadData(ClsSAPDataParameter p_para)
        {
            bool Result = true;

            m_Conn = ClsUtility.GetConn();

            #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,");
                sbSqlZC025.Append(" to_number(b.RSPOS) as XMH,b.MATNR,'' MAKTX,'' MATKL,'' WGBEZ,'' ERDAT,'' ERZET");
                sbSqlZC025.Append(" from ZC10MMDG025 a ,RESB b where a.RSNUM=b.RSNUM and a.DLDATE='" + p_para.Sap_AEDAT + "'");
                sbSqlZC025.Append(" union all ");
                sbSqlZC025.Append(" select");
                sbSqlZC025.Append(" a.RSNUM,a.RSDAT,a.CPUTM,a.ZJHLX0,a.ZJHLX0T,a.WERKS,");
                sbSqlZC025.Append(" to_number(b.POSNR) as XMH,b.MATNR,b.ARKTX MAKTX,'' MATKL,'' WGBEZ,b.ERDAT,b.ERZET");
                sbSqlZC025.Append(" from ZC10MMDG025 a ,VBAP b where a.RSNUM=b.vbeln and a.DLDATE='" + p_para.Sap_AEDAT + "'");
                sbSqlZC025.Append(" union ");
                sbSqlZC025.Append(" select");
                sbSqlZC025.Append(" a.RSNUM,a.RSDAT,a.CPUTM,a.ZJHLX0,a.ZJHLX0T,a.WERKS,");
                sbSqlZC025.Append(" to_number(b.RSPOS)as XMH,b.MATNR,'' MAKTX,'' MATKL,'' WGBEZ,'' ERDAT,'' ERZET");
                sbSqlZC025.Append(" from ZC10MMDG025 a ,ZC10MMDG026 b where a.RSNUM=b.RSNUM and a.DLDATE='" + p_para.Sap_AEDAT + "'");
                sbSqlZC025.Append(" union ");
                sbSqlZC025.Append(" select");
                sbSqlZC025.Append(" a.RSNUM,a.RSDAT,a.CPUTM,a.ZJHLX0,a.ZJHLX0T,a.WERKS,");
                sbSqlZC025.Append(" to_number(b.RSPOS) as XMH,b.MATNR,'' MAKTX,'' MATKL,'' WGBEZ,'' ERDAT,'' ERZET");
                sbSqlZC025.Append(" from ZC10MMDG025 a ,ZP10MMDG203_HZ b where a.RSNUM=b.RSNUM and a.DLDATE='" + p_para.Sap_AEDAT + "'");
                dtZC025 = m_Conn.GetSqlResultToDt(sbSqlZC025.ToString());
            }
            catch (Exception exception)
            {
                Result = false;
                ClsErrorLogInfo.WriteSapLog("1", "cgsj", "ALL", p_para.Sap_AEDAT, "插入hb_wz表过程中查询ZC10MMDG025表发生异常:\t\n" + exception);
                return(Result);
            }

            DataTable dtZC025B = new DataTable();
            try
            {
                string sbSqlZC025B = " select";
                sbSqlZC025B += " REQ_NUM,BDTER,CPUTM,REQ_ITEM,MATNR,ZJHLX0,WERKS,MAKTX,MATKL,WGBEZ,ZJHLX1,ZJHLX2,BWART";
                sbSqlZC025B += " from ZC10MMDG025B WHERE REQ_NUM IS NOT NULL and DLDATE='" + p_para.Sap_AEDAT + "'";

                dtZC025B = m_Conn.GetSqlResultToDt(sbSqlZC025B);
            }
            catch (Exception exception)
            {
                Result = false;
                ClsErrorLogInfo.WriteSapLog("1", "cgsj", "ALL", p_para.Sap_AEDAT, "插入hb_wz表过程中查询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["RSDAT"]   = subRowZC025B["BDTER"].ToString();    //需求计划提报日期
                drZC025["CPUTM"]   = subRowZC025B["CPUTM"].ToString();    //需求计划提报时间
                drZC025["ZJHLX0"]  = subRowZC025B["ZJHLX0"].ToString();   //需求计划类型
                drZC025["ZJHLX0T"] = string.Empty;                        //需求计划文本
                drZC025["WERKS"]   = subRowZC025B["WERKS"].ToString();    //工厂
                drZC025["XMH"]     = subRowZC025B["REQ_ITEM"].ToString(); //需求项目号
                drZC025["MATNR"]   = subRowZC025B["MATNR"].ToString();    //物料号
                drZC025["MAKTX"]   = subRowZC025B["MAKTX"].ToString();    //物料描述
                drZC025["MATKL"]   = subRowZC025B["MATKL"].ToString();    //物料组
                drZC025["WGBEZ"]   = subRowZC025B["WGBEZ"].ToString();    //物料组描述
                drZC025["ERDAT"]   = string.Empty;                        //创建日期
                drZC025["ERZET"]   = string.Empty;                        //创建时间

                dtZC025.Rows.Add(drZC025);
            }

            #endregion

            int commitcount = 0;
            int commitMax   = 500;
            strBuilder.Length = 0;
            strBuilder.Append(" Begin "); //开始执行SQL
            List <string> ls = new List <string>();

            foreach (DataRow subRowZC025 in dtZC025.Rows)
            {
                if (!ls.Contains(subRowZC025["RSNUM"].ToString()))
                {
                    strBuilder.Append(" DELETE FROM HB_WZ WHERE WL_JHH='" + subRowZC025["RSNUM"].ToString() + "';");
                    ls.Add(subRowZC025["RSNUM"].ToString());
                }

                strZC025.strRSNUM   = subRowZC025["RSNUM"].ToString();                  //需求计划号
                strZC025.strRSDAT   = subRowZC025["RSDAT"].ToString().Replace(".", ""); //需求计划提报日期
                strZC025.strCPUTM   = subRowZC025["CPUTM"].ToString();                  //需求计划提报时间
                strZC025.strZJHLXO  = subRowZC025["ZJHLX0"].ToString();                 //需求计划类型
                strZC025.strZJHLX0T = subRowZC025["ZJHLX0T"].ToString();                //需求计划文本
                strZC025.strWERKS   = subRowZC025["WERKS"].ToString();                  //工厂
                strZC025.strRSPOS   = subRowZC025["XMH"].ToString();                    //需求项目号
                strZC025.strMATNR   = subRowZC025["MATNR"].ToString();                  //物料号
                strZC025.strARKTX   = subRowZC025["MAKTX"].ToString();                  //物料描述
                strZC025.strMATKL   = subRowZC025["MATKL"].ToString();                  //物料组
                strZC025.strWGBEZ   = subRowZC025["WGBEZ"].ToString();                  //物料组描述
                strZC025.strERDAT   = subRowZC025["ERDAT"].ToString();                  //创建日期
                strZC025.strERZET   = subRowZC025["ERZET"].ToString();                  //创建时间
                //工厂名称
                DataTable dtGC = new DataTable();
                try
                {
                    string sbGC = " select name1 from  T001W where WERKS='" + strZC025.strWERKS + "'";
                    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);
                }
                if (dtGC != null && dtGC.Rows.Count > 0)
                {
                    strZC025.strWNAME = (dtGC.Rows[0]["NAME1"] ?? string.Empty).ToString();
                }

                #region 采购

                DataTable dtZC026 = new DataTable();
                try
                {
                    string strSqlZC026 = "select RSNUM,RSPOS,AEDAT,MATNR,MAKTX,MATKL,";
                    strSqlZC026 += " WGBEZ,ZEBELN,ZEBELP";
                    strSqlZC026 += " from ZC10MMDG026";
                    strSqlZC026 += " where  RSNUM='" + strZC025.strRSNUM + "' and TO_NUMBER(RSPOS)=" + Convert.ToInt16(strZC025.strRSPOS).ToString();
                    dtZC026      = m_Conn.GetSqlResultToDt(strSqlZC026);
                }
                catch (Exception exception2)
                {
                    Result = false;
                    ClsErrorLogInfo.WriteSapLog("1", "cgsj", "ALL", p_para.Sap_AEDAT, "插入hb_wz表过程中查询ZC10MMDG026表发生异常:\t\n" + exception2);
                    return(Result);
                }

                //二级单位
                DataTable dtZP203_HZ = new DataTable();
                try
                {
                    string strSqlZP203_HZ = "select BANFN,BNFPO,AEDAT,MATNR,MATKL,EBELN,EBELP";
                    strSqlZP203_HZ += " from ZP10MMDG203_HZ";
                    strSqlZP203_HZ += " where RSNUM='" + strZC025.strRSNUM + "' and TO_NUMBER(RSPOS)=" + Convert.ToInt16(strZC025.strRSPOS);
                    strSqlZP203_HZ += " and  MATNR='" + strZC025.strMATNR + "'";
                    dtZP203_HZ      = m_Conn.GetSqlResultToDt(strSqlZP203_HZ);
                }
                catch (Exception exception2)
                {
                    Result = false;
                    ClsErrorLogInfo.WriteSapLog("1", "cgsj", "ALL", p_para.Sap_AEDAT, "插入hb_wz表过程中查询ZP10MMDG203_HZ表发生异常:\t\n" + exception2);
                    return(Result);
                }

                //二级单位和公司级合并数据
                if (dtZP203_HZ != null && dtZP203_HZ.Rows.Count > 0)
                {
                    DataRow drZC026 = null;
                    foreach (DataRow subRowZP203 in dtZP203_HZ.Rows)
                    {
                        drZC026 = dtZC026.NewRow();

                        drZC026["RSNUM"]  = subRowZP203["BANFN"].ToString(); //采购申请号
                        drZC026["RSPOS"]  = subRowZP203["BNFPO"].ToString(); //采购申请行项目号
                        drZC026["AEDAT"]  = subRowZP203["AEDAT"].ToString(); //采购申请生成日期
                        drZC026["MATNR"]  = subRowZP203["MATNR"].ToString(); //采购物料号
                        drZC026["MAKTX"]  = string.Empty;                    //采购物料描述
                        drZC026["MATKL"]  = subRowZP203["MATKL"].ToString(); //采购物料组
                        drZC026["WGBEZ"]  = string.Empty;                    //采购物料组描述
                        drZC026["ZEBELN"] = subRowZP203["EBELN"].ToString(); //采购订单
                        drZC026["ZEBELP"] = subRowZP203["EBELP"].ToString(); //采购订单项目号
                        dtZC026.Rows.Add(drZC026);
                    }
                }

                #endregion

                ZC026 strZC026 = new ZC026();
                foreach (DataRow subRowZC026 in dtZC026.Rows)
                {
                    strZC026.strZCGJHNUM = subRowZC026["RSNUM"].ToString();  //采购计划号
                    strZC026.strZCGJHPOS = subRowZC026["RSPOS"].ToString();  //采购计划项目
                    strZC026.strAEDAT    = subRowZC026["AEDAT"].ToString();  //采购申请生成日期
                    strZC026.strMATNR    = subRowZC026["MATNR"].ToString();  //采购物料号
                    strZC026.strMAKTX    = subRowZC026["MAKTX"].ToString();  //采购物料描述
                    strZC026.strMATKL    = subRowZC026["MATKL"].ToString();  //采购物料组
                    strZC026.strWGBEZ    = subRowZC026["WGBEZ"].ToString();  //采购物料组描述
                    strZC026.strZEBELN   = subRowZC026["ZEBELN"].ToString(); //采购订单
                    strZC026.strZEBELP   = subRowZC026["ZEBELP"].ToString(); //采购订单项目号
                    DataTable dtEKPO = new DataTable();
                    if (strZC026.strZEBELN != null && strZC026.strZEBELN != "")
                    {
                        try
                        {
                            string strSqlEKPO = "select EBELN,EBELP,BUKRS,AEDAT,NETPR,MENGE,NETWR,MEINS  from EKPO";
                            strSqlEKPO += " where EBELN='" + strZC026.strZEBELN + "' and EBELP ='" + strZC026.strZEBELP + "'";
                            strSqlEKPO += " and (LOEKZ<>'L' or LOEKZ IS NULL)";

                            dtEKPO = m_Conn.GetSqlResultToDt(strSqlEKPO);
                        }
                        catch (Exception exception2)
                        {
                            Result = false;
                            ClsErrorLogInfo.WriteSapLog("1", "cgsj", "ALL", p_para.Sap_AEDAT, "插入hb_wz表过程中查询EKPO表发生异常:\t\n" + exception2);
                            return(Result);
                        }
                    }
                    else
                    {
                        try
                        {
                            string strSqlEKPO = "select EBELN,EBELP,BUKRS,AEDAT,NETPR,MENGE,NETWR,MEINS from EKPO";
                            strSqlEKPO += " where BANFN='" + strZC026.strZCGJHNUM + "' and TO_NUMBER(BNFPO)=" + Convert.ToInt16(strZC026.strZCGJHPOS);
                            strSqlEKPO += " and (LOEKZ<>'L' or LOEKZ IS NULL)";

                            dtEKPO = m_Conn.GetSqlResultToDt(strSqlEKPO);
                        }
                        catch (Exception exception2)
                        {
                            Result = false;
                            ClsErrorLogInfo.WriteSapLog("1", "cgsj", "ALL", p_para.Sap_AEDAT, "插入hb_wz表过程中查询EKPO表发生异常:\t\n" + exception2);
                        }
                    }
                    if (dtEKPO != null && dtEKPO.Rows.Count > 0)
                    {
                        strZC026.strZEBELN = (dtEKPO.Rows[0]["EBELN"] ?? string.Empty).ToString(); //采购订单
                        strZC026.strZEBELP = (dtEKPO.Rows[0]["EBELP"] ?? string.Empty).ToString(); //采购订单项目号
                    }
                    #region 采购订单生成


                    if (dtEKPO != null && dtEKPO.Rows.Count > 0)
                    {
                        foreach (DataRow subRowEKPO in dtEKPO.Rows)
                        {
                            #region EKKO数据查询
                            EKKPO strEKKPO = new EKKPO();
                            strEKKPO.strMENGE = subRowEKPO["MENGE"].ToString();
                            strEKKPO.strNETWR = subRowEKPO["NETWR"].ToString();
                            strEKKPO.strNETPR = subRowEKPO["NETPR"].ToString();
                            strEKKPO.strMEINS = subRowEKPO["MEINS"].ToString();

                            DataTable dtEKKO = new DataTable();
                            try
                            {
                                string strSqlEKKO = "select a.AEDAT,a.LIFNR,b.NAME1 from EKKO a,LFA1 b ";//ERP1.0取数方式
                                strSqlEKKO += " where a.EBELN='" + subRowEKPO["EBELN"].ToString() + "'";
                                strSqlEKKO += " and a.LIFNR=b.LIFNR";
                                dtEKKO      = m_Conn.GetSqlResultToDt(strSqlEKKO);
                            }
                            catch (Exception exception2)
                            {
                                Result = false;
                                ClsErrorLogInfo.WriteSapLog("1", "cgsj", "ALL", p_para.Sap_AEDAT, "插入hb_wz表过程中查询EKPO表发生异常:\t\n" + exception2);
                                return(Result);
                            }
                            if (dtEKKO != null && dtEKKO.Rows.Count > 0)
                            {
                                strEKKPO.strAEDAT = (dtEKKO.Rows[0]["AEDAT"] ?? string.Empty).ToString();
                                strEKKPO.strLIFNR = (dtEKKO.Rows[0]["LIFNR"] ?? string.Empty).ToString();
                                strEKKPO.strNAME1 = (dtEKKO.Rows[0]["NAME1"] ?? string.Empty).ToString();
                            }
                            #endregion

                            #region 插入sql
                            strBuilder.Append(" INSERT INTO HB_WZ");
                            strBuilder.Append(" (WL_ID,WL_JHH,WL_JHLX,WL_JHLXWB,WL_GC,WL_GCMC,WL_XMH");
                            strBuilder.Append(",WL_WL,WL_WLMS,WL_WLZ,WL_WLZMS,WL_JHCJRQ,WL_CGDD,WL_CGDDXMH");
                            strBuilder.Append(",WL_GYS,WL_GYSMC,WL_CGSL,WL_JLDW,WL_CGDJ,WL_CGZJ,WL_CGDDRQ)");
                            strBuilder.Append(" VALUES(");
                            strBuilder.Append("SQ_WZCGSJ.NEXTVAL,");
                            strBuilder.Append("'" + strZC025.strRSNUM + "',");   //需求计划号
                            strBuilder.Append("'" + strZC025.strZJHLXO + "',");  //需求计划类型
                            strBuilder.Append("'" + strZC025.strZJHLX0T + "',"); //需求计划文本
                            strBuilder.Append("'" + strZC025.strWERKS + "',");   //工厂
                            strBuilder.Append("'" + strZC025.strWNAME + "',");   //工厂名称
                            strBuilder.Append("'" + strZC025.strRSPOS + "',");   //需求项目号
                            strBuilder.Append("'" + strZC026.strMATNR + "',");   //采购物料号
                            strBuilder.Append("'" + strZC026.strMAKTX + "',");   //采购物料描述
                            strBuilder.Append("'" + strZC026.strMATKL + "',");   //采购物料组
                            strBuilder.Append("'" + strZC026.strWGBEZ + "',");   //采购物料组描述
                            strBuilder.Append("'" + strZC025.strRSDAT + "',");   //需求计划提报日期
                            strBuilder.Append("'" + strZC026.strZEBELN + "',");  //采购订单
                            strBuilder.Append("'" + strZC026.strZEBELP + "',");  //采购订单项目号
                            strBuilder.Append("'" + strEKKPO.strLIFNR + "',");   //供应商
                            strBuilder.Append("'" + strEKKPO.strNAME1 + "',");   //供应商名称
                            strBuilder.Append("'" + strEKKPO.strMENGE + "',");   //采购数量
                            strBuilder.Append("'" + strEKKPO.strMEINS + "',");   //采购数量
                            strBuilder.Append("'" + strEKKPO.strNETPR + "',");   //采购单价
                            strBuilder.Append("'" + strEKKPO.strNETWR + "',");   //采购总价
                            strBuilder.Append("'" + strEKKPO.strAEDAT + "'");    //采购订单创建日期
                            strBuilder.Append(");");

                            #endregion

                            #region 提交sql
                            commitcount++;
                            if (commitcount % commitMax == 0)
                            {
                                strBuilder.Append(" End;");  //SQL完成
                                try
                                {
                                    if (strBuilder.ToString().Length < 14)
                                    {
                                        strBuilder.Length = 0;
                                        strBuilder.Append(" Begin "); //开始执行SQL
                                        continue;
                                    }
                                    //数据提交
                                    Result            = ClsUtility.ExecuteSqlToDb(strBuilder.ToString());
                                    strBuilder.Length = 0;
                                    strBuilder.Append(" Begin "); //开始执行SQL
                                }
                                catch (Exception exception5)
                                {
                                    Result = false;
                                    ClsErrorLogInfo.WriteSapLog("1", "cgsj", "ALL", p_para.Sap_AEDAT, "插入hb_wz表发生异常:" + exception5);
                                }
                            }
                            #endregion
                            #endregion
                        }
                    }
                }
            }

            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", "cgsj", "ALL", p_para.Sap_AEDAT, "插入hb_wz表发生异常:" + exception5);
            }

            return(Result);
        }
Пример #11
0
        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);
        }
Пример #12
0
        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;
        }
Пример #13
0
        public bool SAPLoadData(ClsSAPDataParameter p_para)
        {
            string dldate = p_para.Sap_AEDAT;
            bool   Result = true;

            m_Conn = ClsUtility.GetConn();
            strBuilder.Append(" Begin ");
            strDetail.Append(" Begin ");
            try
            {
                string sqlLQUA = @"select SUM(t.GESME) GESME,t.MATNR,t.WERKS,t.LGORT,substr(t.LGPLA,1,4) LGPLA,b.DW_NAME,c.KCDD_NAME
                                    from LQUA t 
                                    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
                                    where  regexp_like(lgpla,'^[0-9]+[0-9]$')  and substr(t.LGTYP,1,1)<>'9' and matnr  in (select matnr from ZC10MMDG085B) 
                                     GROUP BY t.MATNR,t.WERKS,t.LGORT,substr(t.LGPLA,1,4),b.DW_NAME,c.KCDD_NAME  "; //查询lqua表库存,以上架后的(lgpla为数字的,并且matnr 在085b里有的)
                //regexp_like(lgpla,'^[0-9]+[0-9]$')过滤不是数字的
                DataTable dtLQUA = m_Conn.GetSqlResultToDt(sqlLQUA);
                if (dtLQUA != null && dtLQUA.Rows.Count > 0)
                {
                    string    sqlWLZ  = @"  select  d.MATNR,d.MATKL,d.MAKTX,e.PMNAME,e.JBJLDW ,e.DLNAME
                                    FROM   MARA d 
                                    join WZ_WLZ e on e.PMCODE=d.MATKL ";                                  //查询物料组基本计量单位,物料描述
                    DataTable dtWLZ   = m_Conn.GetSqlResultToDt(sqlWLZ);
                    string    sql085B = @"select MATNR,WERKS,LGORT,substr(LGPLA,1,4)LGPLA,GESME,ERDAT,ZDHTZD ,ZITEM 
                                          from ZC10MMDG085B A
                                         WHERE GESME>0 and ZDHTZD is not null
                                          order by  MATNR,WERKS,LGORT,substr(LGPLA,1,4) ASC,ERDAT  DESC"; //查询085B 因为这个表有仓位//倒查是否有积压
                    DataTable dt085B  = m_Conn.GetSqlResultToDt(sql085B);
                    int       a       = 0;
                    foreach (DataRow rowLQUA in dtLQUA.Rows)
                    {
                        double GESME = double.Parse(rowLQUA["GESME"].ToString());
                        //按工厂,库存地点,仓位前四位,物料编码 分组查询085b
                        DataRow[] arr085B = dt085B.Select("MATNR='" + rowLQUA["MATNR"] + "' and WERKS='" + rowLQUA["WERKS"] + "' and  LGORT='" + rowLQUA["LGORT"] + "' and LGPLA='" + rowLQUA["LGPLA"] + "' ");
                        //按物料查询物料描述,基本计量单位等。
                        DataRow[] arrWLZ = dtWLZ.Select(" MATNR='" + rowLQUA["MATNR"] + "'  ");
                        if (arr085B.Length > 0)
                        {
                            double totalGESME = 0;
                            foreach (var item in arr085B)
                            {
                                totalGESME = totalGESME + double.Parse(item["GESME"].ToString());
                                if (GESME > totalGESME)
                                {
                                    if (arrWLZ.Length > 0)
                                    {
                                        strDetail.Append(" INSERT INTO CONVERT_SWKCDETAIL(WERKS,WERKS_NAME,LGORT,MATNR,GESME,ERDATE,DLNAME,PMNAME,DLDATE) VALUES(  ");//插入明细表,为后面算账务金额做基础
                                        strDetail.Append(" '" + rowLQUA["WERKS"].ToString() + "', ");
                                        strDetail.Append(" '" + rowLQUA["DW_NAME"]?.ToString() + "', ");
                                        strDetail.Append(" '" + rowLQUA["LGORT"]?.ToString() + "', ");
                                        strDetail.Append(" '" + item["MATNR"]?.ToString() + "', ");
                                        strDetail.Append(" '" + item["GESME"]?.ToString() + "', ");
                                        strDetail.Append(" '" + item["ERDAT"]?.ToString() + "', ");
                                        strDetail.Append(" '" + arrWLZ[0]["DLNAME"]?.ToString() + "', ");
                                        strDetail.Append(" '" + arrWLZ[0]["PMNAME"]?.ToString() + "', ");
                                        strDetail.Append(" '" + dldate + "'); ");
                                    }
                                    continue;//如果当前累计数量和小于库存,继续循环
                                }
                                else
                                {
                                    //插入明细表,为后面算账务金额做基础
                                    if (arrWLZ.Length > 0)
                                    {
                                        strDetail.Append(" INSERT INTO CONVERT_SWKCDETAIL(WERKS,WERKS_NAME,LGORT,MATNR,GESME,ERDATE,DLNAME,PMNAME,DLDATE) VALUES(  ");//插入明细表,为后面算账务金额做基础
                                        strDetail.Append(" '" + rowLQUA["WERKS"].ToString() + "', ");
                                        strDetail.Append(" '" + rowLQUA["DW_NAME"]?.ToString() + "', ");
                                        strDetail.Append(" '" + rowLQUA["LGORT"]?.ToString() + "', ");
                                        strDetail.Append(" '" + item["MATNR"]?.ToString() + "', ");
                                        strDetail.Append(" '" + item["GESME"]?.ToString() + "', ");
                                        strDetail.Append(" '" + item["ERDAT"]?.ToString() + "', ");
                                        strDetail.Append(" '" + arrWLZ[0]["DLNAME"]?.ToString() + "', ");
                                        strDetail.Append(" '" + arrWLZ[0]["PMNAME"]?.ToString() + "', ");
                                        strDetail.Append(" '" + dldate + "'); ");
                                    }


                                    //根据mard库存按工厂、物料码、库存地点,分组,回找入库通知单,直到入库通知单数量和大于或等于mard数量
                                    //将近期入库通知单收货数量小于库存的都插入实物库存表CONVERT_SWKC
                                    strBuilder.Append("  INSERT INTO CONVERT_SWKC (WERKS,ZDHTZD,ZITEM,MATKL,MATNR,MAKTX," +
                                                      "MEINS,GESME,LGORT,LGPLA,ERDAT,WERKS_NAME,LGORT_NAME,ZSTATUS,YXQ,DLDATE,KCTYPE) VALUES(");
                                    strBuilder.Append(" '" + rowLQUA["WERKS"].ToString() + "',");
                                    strBuilder.Append(" '" + item["ZDHTZD"] + "',");
                                    strBuilder.Append(" '" + item["ZITEM"] + "',");
                                    if (arrWLZ.Length > 0)
                                    {
                                        strBuilder.Append(" '" + arrWLZ[0]["MATKL"]?.ToString().Trim());
                                        strBuilder.Append("',");
                                    }
                                    else
                                    {
                                        strBuilder.Append(" '',");
                                    }
                                    strBuilder.Append(" '" + item["MATNR"] + "',");
                                    if (arrWLZ.Length > 0)
                                    {
                                        strBuilder.Append(" '" + arrWLZ[0]["MAKTX"]?.ToString().Trim());
                                        strBuilder.Append("',");
                                        strBuilder.Append(" '" + arrWLZ[0]["JBJLDW"]?.ToString().Trim().Replace('\'', ' ').Replace('(', ' ').Replace(')', ' ').Replace(')', ' ').Replace('(', ' '));
                                        strBuilder.Append("',");
                                    }
                                    else
                                    {
                                        strBuilder.Append(" '','',");
                                    }

                                    strBuilder.Append(" '" + GESME.ToString() + "',");
                                    strBuilder.Append(" '" + rowLQUA["LGORT"] + "',");
                                    strBuilder.Append(" '" + rowLQUA["LGPLA"] + "',");//仓位

                                    strBuilder.Append(" '" + item["ERDAT"]?.ToString() + "',");
                                    strBuilder.Append(" '" + rowLQUA["DW_NAME"] + "',");
                                    strBuilder.AppendLine(" '" + rowLQUA["KCDD_NAME"].ToString().Replace('(', ' ').Replace(')', ' ').Replace(')', ' ').Replace('(', ' ').Replace('\'', ' ') +
                                                          "','04','','" + dldate + "',0);  ");
                                }
                            }
                        }
                    }
                    if (strBuilder.Length > 8)
                    {
                        strBuilder.Append(" COMMIT; end ; ");
                        strDetail.Append(" COMMIT; end ; ");
                        if (m_Conn.ExecuteSql(" begin delete from CONVERT_SWKC where KCTYPE=0;DELETE FROM CONVERT_SWKCDETAIL; commit; end; "))
                        {
                            Result = m_Conn.ExecuteSql(strDetail.ToString());//执行失败竟然也返回true
                            if (Result)
                            {
                                ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKCDETAIL", "ALL", p_para.Sap_AEDAT, "插入CONVERT_SWKCDETAIL失败,应该是sql有问题,可能含特殊字符了,");
                            }
                            else
                            {
                                ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKCDETAIL", "ALL", p_para.Sap_AEDAT, "插入CONVERT_SWKCDETAIL失败,应该是sql有问题,可能含特殊字符了,");
                            }
                            Result = ClsUtility.ExecuteSqlToDb(strBuilder.ToString());//执行失败竟然也返回true
                            if (Result)
                            {
                                DataTable dt = m_Conn.GetSqlResultToDt("select 1 from CONVERT_SWKC ");//要重新判断是否插入成功
                                if (dt == null || dt.Rows.Count == 0)
                                {
                                    ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", p_para.Sap_AEDAT, "插入CONVERT_SWKC表上架部分失败,应该是sql有问题,可能含特殊字符了,");
                                    Result = false;
                                }
                                else
                                {
                                    ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", p_para.Sap_AEDAT, "插入CONVERT_SWKC表上架部分成功");
                                    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,d.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'; commit; end; ");//插入质检状态的入库单作为另一部分库存
                                    Result = m_Conn.ExecuteSql(strBuilder.ToString());
                                    if (Result)
                                    {
                                        ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", p_para.Sap_AEDAT, "插入CONVERT_SWKC表质检部分成功");
                                    }
                                    else
                                    {
                                        ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", p_para.Sap_AEDAT, "插入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", p_para.Sap_AEDAT, "更新CONVERT_SWKCDETAIL表金额失败");
                                }
                            }
                            else
                            {
                                ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", p_para.Sap_AEDAT, "插入CONVERT_SWKC上架部分表失败");
                            }
                        }
                    }
                }
                ClsDataLoadBAOBIAO.SAPLoadData(p_para);//下架未过账库存金额模型
            }
            catch (Exception ex)
            {
                strBuilder.Length = 0;
                ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", p_para.Sap_AEDAT, "插入CONVERT_SWKC表发生异常:" + ex.Message);
                return(false);
            }
            return(Result);
        }
Пример #14
0
        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);
        }
Пример #15
0
        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)LGPLA2,A.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 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(lgpla,'^0\d{9}$') 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,B.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\d{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\d{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["LGPLA2"].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["LGPLA2"].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["LGPLA2"].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);
        }
Пример #16
0
        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);
        }
Пример #17
0
        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);
        }