Roberto Stefanetti NAV Blog


...from NAV 3.60 to NAV 2013
Archivio Posts
Anno 2016

Anno 2015

Anno 2014

Anno 2013

Anno 2012

Anno 2011

Anno 2010

Anno 2009

Anno 2008

Anno 2007

Import Exchange Rates from Central European Bank

Codeunit Import Exchange Rates

A very simply codeunit to import daily exchange rates directly from Central European Bank with XML Filestream IN


Documentation Section

//*** Import "New Change Rate" from  "ECB.EUROPA.EU"    --> FIX TIME >= 2.00 AM
//*** Import XML File "Change Rate" from "ECB.EUROPA.EU" --> "eurofxref-daily.xml"
//*** XmlTextReader Global Dim  DotNet  System.Xml.XmlTextReader.'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
            
Global Variables
XmlTextReader DotNet  System.Xml.XmlTextReader.'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' 
codCurrency Code 20
decRate  Decimal  
datValueDate  Date  
intYear  Integer  
intMonth  Integer  
intDay  Integer  
intNodeCounter  Integer  
Currency Record  Currency 
CurrencyExchangeRate Record  Currency Exchange Rate 


OnRun() Section

//Direct XML Text Reader (XML Stream IN)
XmlTextReader := XmlTextReader.XmlTextReader('http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml');
 
//LOOP XML STREAMING IN
WHILE XmlTextReader.Read DO BEGIN
  IF XmlTextReader.Name = 'Cube' THEN BEGIN
    IF XmlTextReader.AttributeCount = 1 THEN BEGIN
      XmlTextReader.MoveToAttribute('time');  //Time
      EVALUATE(intYear,COPYSTR(XmlTextReader.Value,1,4));   //Year
      EVALUATE(intMonth,COPYSTR(XmlTextReader.Value,6,2));  //Month
      EVALUATE(intDay,COPYSTR(XmlTextReader.Value,9,2));    //Day
      datValueDate := DMY2DATE(intDay,intMonth,intYear);    //Value Date
    END;
 
    IF XmlTextReader.AttributeCount = 2 THEN BEGIN
      XmlTextReader.MoveToAttribute('currency');  // Currency
      codCurrency := XmlTextReader.Value;     
      XmlTextReader.MoveToAttribute('rate');   // Rate
      EVALUATE(decRate,CONVERTSTR(XmlTextReader.Value,'.',',')); 

      //  MESSAGE('%1 - %2',codCurrency,decRate); > TEST MESSAGE
           
      // INSERT\MODIFY New Fix Exchange Rate
      IF Currency.GET(codCurrency) THEN BEGIN
        CurrencyExchangeRate."Currency Code" := codCurrency;
        CurrencyExchangeRate."Starting Date" := WORKDATE;
        CurrencyExchangeRate."Exchange Rate Amount" :=  decRate;
        CurrencyExchangeRate."Relational Exch. Rate Amount" :=1;
        CurrencyExchangeRate."Fix Exchange Rate Amount" := CurrencyExchangeRate."Fix Exchange Rate Amount"::"Relational Currency";
        IF NOT CurrencyExchangeRate.INSERT THEN
           CurrencyExchangeRate.MODIFY;
      END;    
    END;  
  END;
END;

Based and rivisited on original Mibuso solution.

Categoria: Dynamics NAV 2015
sabato, 07 mar 2015 Ore. 15.05

Messaggi collegati


DYNAMICS NAV MVP 2016



FOLLOW ME! ON SOCIALS

Statistiche
  • Views Home Page: 264.063
  • Views Posts: 580.440
  • Views Gallerie: 661
  • n° Posts: 347
  • n° Commenti: 26
Mappa
Meteo
Copyright © 2002-2007 - Blogs 2.0
dotNetHell.it | Home Page Blogs
ASP.NET 2.0 Windows 2003