Creating Exchange Rate Providers in Microsoft Dynamics AX 2012 for JSON Rates

Microsoft Dynamics AX 2012 has the frame work for integrating currency exchange rates from exchange rate providers. Please reference Creating Exchange Rate Providers for Microsoft Dynamics AX 2012. The referenced document from Microsoft shows how to customize the frame work to work with Oanda Exchange Rates .

There are other providers, some which offer a fee based API and others which offer a free API. Doing research on these providers led me to JSON Rates. They offer a free API, which has the functionality needed for basic currencyPair conversion rate retrieval.

This blog entry uses the referenced document from Microsoft as a guide to create a provider class in Dynamics AX specifically for use with the JSON Rates API.

The provider class we will create will support the JSON Rates API.

Create the class as ExchangeRateProviderJSONRates and have it extend the ExchangeRateProvider as shown in the class declaration below.

Note: The ExchangeRateProviderIdAttribute is a unique ID that you will need to supply. You can obtain a unique ID from http://createguid.com

	[ExchangeRateProviderAttribute,
	ExchangeRateProviderIdAttribute('DC8FC177-B377-448E-A18D-FE6B13F94DD9')]
	class ExchangeRateProviderJSONRates extends ExchangeRateProvider
	{
	str serviceUrl;
	str serviceUrlForDateRange;
	str serviceClient;
	int serviceTimeout;
	List rates;
	List dates;
	//ApiKey for account identification when sending requests to JSONRates
	#define.APIKey("jr-cd4e735cf7cd8c8b07d7a8fb1491b8ca")
	#define.selectNodes("//root/rates/str_")
	#define.ServiceTimeout("serviceTimeout")
	#define.ServiceURL("ServiceUrl")
	#define.OPENEXCHRATESDateFormat("yyyy-MM-dd")
	}

view raw gistfile1.cs hosted with ❤ by GitHub

Now create a method called getConfigurationDefaults. This method will provide the default URL used to make requests to the provider.

	public ExchangeRateProviderConfigDefaults getConfigurationDefaults()
	{
	ExchangeRateProviderConfigDefaults configurationDefaults = ExchangeRateProviderConfigDefaults::construct();
	configurationDefaults.addNameValueConfigurationPair(#ServiceTimeout, '5000');
	configurationDefaults.addNameValueConfigurationPair(#ServiceURL, 'http://jsonrates.com/historical/?from=%1&to=%2&date=%3&apiKey=%4');
	return configurationDefaults;
	}

view raw gistfile1.cs hosted with ❤ by GitHub

Next create the getExchangeRates method. This method is responsible for constructing the URL to request the currency information from the provider, and store the results in the currency exchange rate tables of Dynamics AX.

	public ExchangeRateResponse getExchangeRates(ExchangeRateRequest _exchangeRateRequest)
	{
	ExchangeRateResponse response = ExchangeRateResponse::construct();
	ExchangeRateResponseCurrencyPair currencyPairResponse;
	ExchangeRateResponseExchangeRate exchangeRateResponse;
	ExchangeRateRequestCurrencyPair currencyPairRequest;
	ExchangeRateProviderConfig config = ExchangeRateProviderConfig::construct();
	RetailCommonWebAPI webApi;
	RetailWebRequest webRequest;
	RetailWebResponse webResponse;
	str jsonRequestString;
	date currentDate;
	CurrencyExchangeRate exchangeRate;
	ListEnumerator rateEnumerator, dateEnumerator;
	System.DateTime fromDate, fromUTCDate;
	System.TimeZone localTimeZone;
	int compareResult;
	str JSONOut;
	str dateForRequest;
	rates = new List(Types::Real);
	dates = new List(Types::Date);
	localTimeZone = System.TimeZone::get_CurrentTimeZone();
	// Iterate over the requested currency pairs. This is only required for providers
	// that support specific currency pairs.
	_exchangeRateRequest.initializeCurrencyPairEnumerator();
	while(_exchangeRateRequest.moveNextCurrencyPair())
	{
	serviceTimeout = str2int(config.getPropertyValue(this.getProviderId(), #ServiceTimeout));
	serviceUrl = config.getPropertyValue(this.getProviderId(), #ServiceURL);
	// Process each date separately.
	fromDate = _exchangeRateRequest.parmFromDate();
	compareResult = fromDate.CompareTo(_exchangeRateRequest.parmToDate());
	while (compareResult <= 0)
	{
	currencyPairRequest = _exchangeRateRequest.getCurrentCurrencyPair();
	currencyPairResponse = ExchangeRateResponseCurrencyPair::construct();
	currencyPairResponse.parmFromCurrency(currencyPairRequest.parmFromCurrency());
	currencyPairResponse.parmToCurrency(currencyPairRequest.parmToCurrency());
	// All rates are requested with a display factor of 1 for this provider. If the
	// rates internally are represented using a different exchange rate display
	// factor, the framework will make the necessary adjustments when saving the
	// exchange rates.
	currencyPairResponse.parmExchangeRateDisplayFactor(ExchangeRateDisplayFactor::One);
	// convert to UTC which is required by JSONRATES
	fromUTCDate = localTimeZone.ToUniversalTime(fromDate);
	dateForRequest = fromUTCDate.ToString(#OPENEXCHRATESDateFormat);
	// Build the request URL.
	jsonRequestString = strFmt(serviceUrl,
	currencyPairRequest.parmFromCurrency(),
	currencyPairRequest.parmToCurrency(),
	dateForRequest,
	#APIKey);
	// Configure the request for JSONRATES.
	webApi = RetailCommonWebAPI::construct();
	webRequest = RetailWebRequest::newUrl(jsonRequestString);
	try
	{
	// Invoke the service
	webResponse = webApi.getResponse(webRequest);
	JSONOut = webResponse.parmData();
	// Parse the XML to retrieve the rate and date.
	this.readRate(JSONOut,currencyPairRequest,dateForRequest,DateTimeUtil::date(Global::CLRSystemDateTime2UtcDateTime(fromDate)));
	rateEnumerator = rates.getEnumerator();
	rateEnumerator.moveNext();
	dateEnumerator = dates.getEnumerator();
	// Create the Exchange Rate Provider Response.
	dateEnumerator.moveNext();
	exchangeRate = rateEnumerator.current();
	currentDate = dateEnumerator.current();
	if (currentDate != dateNull() && exchangeRate)
	{
	exchangeRateResponse = ExchangeRateResponseExchangeRate::construct();
	exchangeRateResponse.parmValidFrom(currentDate);
	exchangeRateResponse.parmExchangeRate(exchangeRate);
	currencyPairResponse.addExchangeRate(exchangeRateResponse);
	currentDate = dateNull();
	exchangeRate = 0;
	}
	}
	catch (Exception::CLRError)
	{
	// The service call did not complete. Swallow the exception and try the next
	// currency pair. The framework will be able to determine which currency
	// pairs were successfully retrieved and will display the appropriate
	// warnings to the user.
	}
	response.addOrUpdateCurrencyPair(currencyPairResponse);
	rates = new List(Types::Real);
	dates = new List(Types::Date);
	fromDate = fromDate.AddDays(1);
	compareResult = fromDate.CompareTo(_exchangeRateRequest.parmToDate());
	}
	}
	return response;
	}

view raw gistfile1.cs hosted with ❤ by GitHub

Next create the getName method. This method is used by Dynamics AX to populate the list of providers that Dynamics AX has been modified to support.

	public ExchangeRateProviderName getName()
	{
	return "JSON Rates";
	}

view raw gistfile1.cs hosted with ❤ by GitHub

Once the getName method is created, create the getProviderId method. This method simply returns the unique GUID that was assigned in the class declaration.

	public ExchangeRateProviderId getProviderId()
	{
	return 'DC8FC177-B377-448E-A18D-FE6B13F94DD9';
	}

view raw gistfile1.cs hosted with ❤ by GitHub

Next create the getSupportedOptions method.

	public ExchangeRateProviderSupportedOptions getSupportedOptions()
	{
	ExchangeRateProviderSupportedOptions supportedOptions = ExchangeRateProviderSupportedOptions::construct();
	supportedOptions.parmDoesSupportSpecificCurrencyPairs(true);
	supportedOptions.parmDoesSupportSpecificDates(false);
	supportedOptions.parmFixedBaseIsoCurrency('');
	return supportedOptions;
	}

view raw gistfile1.cs hosted with ❤ by GitHub

The last method that needs created is the readRate method. It is responsible for parsing the XML response string from the provider, and storing the exchange rate and currency information in the Lists.

	private void readRate(str _jsonString,ExchangeRateRequestCurrencyPair _currencyPairRequest,str _currentDate,date _listDate)
	{
	XmlDocument xmlDocument;
	XmlNode node;
	date currentDate;
	JSON2Xml json2xml = new JSON2Xml();
	str xml = json2xml.convert2xml(_jsonString);
	str singleNodeStr = strFmt("%1%2/rate",#selectNodes,_currentDate);
	;
	xmlDocument = new xmlDocument();
	xmlDocument.loadXml(xml);
	node = xmlDocument.selectSingleNode(singleNodeStr);
	rates.addEnd(any2real(node.text()));
	currentDate = str2Date(_currentDate,312);
	dates.addEnd(_listDate);
	}

view raw gistfile1.cs hosted with ❤ by GitHub

A JSON to XML converter class will also need to be added as a dependency, which is coded as follows:

classdeclaration

	class JSON2XML
	{
	BinData binData;
	}

view raw gistfile1.cs hosted with ❤ by GitHub

loadFromFile

	//load the JSON data from a file
	public str loadFromFile(FilenameOpen _file)
	{
	FileIOPermission _perm = new FileIOPermission(_file,"r");
	binData = new binData();
	_perm.assert();
	binData.loadFile(_file);
	return binData.getStrData();
	}

view raw gistfile1.cs hosted with ❤ by GitHub

convert2xml

	//rudimentry method for converting JSON to XML
	public str convert2xml(str _json)
	{
	#define.SPACE(32)
	//remove whitespace from JSON string
	str stripSpace(str _str)
	{
	int i;
	str tmpStr;
	boolean inquote;
	for(i=1;i<=strLen(_str);i++)
	{
	if (subStr(_str,i,1) == '"')
	{
	inquote=true;
	}else
	{
	inquote = false;
	}
	if (char2num(_str,i) <= #SPACE && !inquote)
	{
	continue;
	}
	tmpStr = tmpStr + subStr(_str,i,1);
	}
	return tmpStr;
	}
	//add header to xml
	xml = '<?xml version="1.0" encoding="UTF-8"?>';
	xml = xml + "<root>";
	//convert
	this.convert("root",stripSpace(_json));
	return xml;
	}

view raw gistfile1.cs hosted with ❤ by GitHub

convert

	//recursive method to parse JSON string and construct XML.
	container convert(str _tag,str _str)
	{
	int i = strLen(_str);
	str tmpStr = _str;
	str tagStr,origTagStr;
	str convStr;
	int pos;
	int quotePos;
	int retval;
	boolean expand;
	boolean inquote;
	boolean invalue;
	pos = 1;
	while (pos<=i)
	{
	switch(subStr(tmpStr,pos,1))
	{
	//assign end tag and pop if not in quotes
	case ',':
	if (!inquote)
	{
	if (invalue)
	{
	xml = xml + subStr(tmpStr,quotePos,pos- quotePos);
	invalue = false;
	}
	xml = xml + "</" + _tag + ">";
	return [pos,true];
	}
	pos +=1;
	break;
	//beginning of a tag if not in quotes, push json element, value
	case '{':
	if (invalue)
	{
	xml = xml + subStr(tmpStr,quotePos,pos - quotePos);
	inValue = false;
	}
	pos +=1;
	if (!inquote)
	{
	[tagStr,origTagStr] = this.getTag(subStr(tmpStr,pos,strLen(tmpStr)-pos));
	xml = xml + "<" + tagStr + ">";
	pos += strLen(origTagStr) + 2;
	convStr = subStr(tmpStr,pos,strLen(tmpStr)-(pos-1));
	[retval,expand] = this.convert(tagStr,convStr);
	pos += retval;
	//if we are going to expand, then poke a '{' to force another nesting tag
	if (expand)
	{
	pos -=1;
	tmpStr = strPoke(tmpStr,"{",pos);
	}
	}
	break;
	//ending of the tag if not in quotes, pop json element
	case '}':
	if (invalue)
	{
	xml = xml + subStr(tmpStr,quotePos,pos -quotePos);
	invalue = false;
	}
	//pos +=1;
	if (!inquote)
	{
	xml = xml + "</" + _tag + ">";
	}
	return [pos,false];
	//begin array if not in quote and push json element, value pairs
	case '[':
	if (invalue)
	{
	xml = xml + subStr(tmpStr, quotePos, pos - quotePos);
	invalue = false;
	}
	pos +=1;
	if (!inquote)
	{
	tagStr = 'element';
	xml = xml + "<" + tagStr + ">";
	convStr = subStr(tmpStr,pos,strLen(tmpStr)-(pos-1));
	[retval,expand] = this.convert(tagStr,convStr);
	pos += retval;
	if (expand)
	{
	pos -=1;
	tmpStr = strPoke(tmpStr,"[",pos);
	}
	}
	break;
	//end array, and pop json element
	case ']':
	if (invalue)
	{
	xml = xml + subStr(tmpStr,quotePos, pos - quotePos);
	invalue = false;
	}
	pos +=1;
	if (!inquote)
	{
	xml = xml + "</element>";
	}
	return [pos, false];
	//begin string type
	case '"':
	pos +=1;
	if (inquote)
	{
	inquote = false;
	//add value without quotation marks if they exist
	xml = xml + strReplace(subStr(tmpStr,quotePos,pos-quotePos),'"','');
	quotePos=0;
	}else
	{
	inquote = true;
	quotePos = pos-1;
	}
	break;
	//parse the value of the element,value pair
	case ':':
	pos +=1;
	if (inquote || invalue)
	{
	break;
	}
	//we might have a value other than a string, we will capture all the characters
	//and store it in the xml string as a string without quotes.
	if (subStr(tmpStr,pos,1) !='"')
	{
	quotePos = pos;
	invalue = true;
	}
	break;
	default:
	pos +=1;
	break;
	}
	}
	if (invalue)
	{
	xml = xml + subStr(tmpStr,quotePos,pos -quotePos);
	}
	xml = xml + "</" + _tag + ">";
	return [pos-1,false];
	}

view raw gistfile1.cs hosted with ❤ by GitHub

getTag

	//get tag from JSON string
	container getTag(str _tagStr)
	{
	str tmpStr = subStr(_tagStr,1,strScan(_tagStr,":",1,strLen(_tagStr))-1);
	str retStr = strReplace(tmpStr,'"','');
	//XML does not allow the first characer of the tag name to be anything other than a alphabetic character
	if (!strFind(retStr,"ABCDEFGHIJKLMNOPQRSTUVWXYZ",1,1))
	{
	return ["str_" + retStr,retStr];
	}
	return [retStr,retStr];
	}

view raw gistfile1.cs hosted with ❤ by GitHub

Now that the class has been created, it can be used in currency configuration and setup within Dynamics AX.