vTigress CookBook Recipe #1 – Currency rate synchronization


This recipe synchronizes currency exchange rates on an as needed basis or on a periodic basis. This is extremely useful for enterprises that deal with multiple currencies need to track the exchange rates for the purpose of their commercial documentation involving money. Vtiger by default provides a currency management module, where the present implementation limitations are

  • No change of data stored for trend analysis of currency rate fluctuations
  • Requires updating of exchange rates manually

The present vtiger currency management module will be modified to realize the additional requirements. Yahoo Finance provides an API which is used for the purpose of this implementation. Other services exist, including the paid for real time rate queries provided by financial information agencies. While Yahoo’s rates are not real time, it is available for free and has a popularly used API.

Implementation and Features

  1. Independent script that would be executable either by a cron job or by a call from Vtiger currency module.
  2. Vtiger currency module would need to have an additional button, which when clicked, would call the current rates from Yahoo Finance and update the rates.
  3. A new exchange rate change table to be added to the database.
  4. Above features to be packed into a module and made installable via vtiger module manager installable zip file.

Present vTiger system features vtiger menu- Settings

vtiger settings

The currency management module of vtiger is accessible from the Settings menu link. Selecting this module brings up the currency management screen where once can manage the list of currencies that are to be configured in the system. In our example, we have setup a typical situation of a enterprise that deals with vendors, suppliers and customers, channel partners from many countries, and in this case, we have selected 21 different currencies. Figure 1 : New Currency setup form Figure1_New Currency setup form Figure 2 : Multiple currencies setup for testing Figure2_Multiple currrencies setup for testing Figure 3 : Updates to user interface for exchange rate update Figure3_Updates to user interface for exchange rate update

  1. Scripts modified – Smarty/templates/CurrencyListView.tpl, vtigercron.php
  2. Scripts added – getxrates.php
  3. Tables modified – vtiger_currency_info
  4. Tables added – vtiger_currencies_rates
  5. Other changes – Permissions on directories, Smarty/templates

Cheat sheet using Yahoo! Finance API

  1. http://www.gummy-stuff.org/Yahoo-data.htm
  2. http://allurcode.com/2010/11/08/get-live-exchange-rates-in-php/

A premium real time forex rate service

  1. http://www.exchangerate-api.com/

Further enhancements

  1. Historical exchange rates log table storage parameters ( no of days, no of records ). See UI concept in figure
  2. Link to other / alternative exchange rate sources
  3. Store rates from more than one source

https://raw.github.com/currencybot/open-exchange-rates/master/latest.json vtiger tables’ details

  • vtiger_currencies                                    
  • vtiger_currencies_seq                             
  • vtiger_currency                                      
  • vtiger_currency_decimal_separator          
  • vtiger_currency_decimal_separator_seq   
  • vtiger_currency_grouping_pattern            
  • vtiger_currency_grouping_pattern_seq     
  • vtiger_currency_grouping_separator        
  • vtiger_currency_grouping_separator_seq 
  • vtiger_currency_info                                
  • vtiger_currency_info_seq                         
  • vtiger_currency_symbol_placement
  • vtiger_currency_symbol_placement_seq    

  

table_vtiger_currencies table_vtiger_currency table_vtiger_currency_info

 

</pre>
<?php
//
//CREATE TABLE 'vtiger_currencies_rates' ( 'Id' int(11) NOT NULL auto_increment, PRIMARY KEY ('Id') ) ENGINE=MyISAM COMMENT='Stores the daily exchange rates against the default currency for all other currencies found in the vtiger_currency_info table' CHARACTER SET='utf8';
//ALTER TABLE vtiger_currencies_rates ADD (currency_code VARCHAR(10), exchange_rate DECIMAL(12,6), rate_date DATE, rate_time TIME);
//ALTER TABLE vtiger_currency_info ADD xr_date DATETIME;

require_once("config.php");
require_once('include/logging.php');
require_once('include/utils/CommonUtils.php');
require_once('include/utils/VtlibUtils.php');

$log = &LoggerManager::getLogger('Get Currencies Exchange rates');
global $adb,$log;
$tdt=date('Y-m-d');
$action = $_REQUEST['action'];
if ($action == 'List' || $action == 'list')
{
$sql_sel="select * from vtiger_currencies_rates order by id desc ";
$dresult = $adb->pquery($sql_sel, array());
while($row = $adb->fetch_array($dresult))
 {
 echo $row['id'] .', '. $row['rate_date'] .', '. $row['rate_time'] .', '. $row['currency_code'] .', '. $row['exchange_rate'] .'<br>' ;
 }
}
else
{
$sql_sel="select currency_code from vtiger_currency_info where currency_name = ? ";
$dresult = $adb->pquery($sql_sel, array($currency_name));
$from = $adb->query_result($dresult,0,"currency_code");

$sql_sel="select currency_code from vtiger_currency_info where currency_name <> ? ";
$result = $adb->pquery($sql_sel, array($currency_name));

while($row = $adb->fetch_array($result))
// loop thru all currencies in the table, obtain the information from Yahoo Finance and insert new records into the table with a datetimestamp of loading
{
$to= $row['currency_code'];

$xrate = getYahooXrate($from,$to);
sleep (1);
$xval=$xrate[0];
if ($xrate && $xrate[0] > 0 )
{
 $ins_sql= "INSERT INTO vtiger_currencies_rates (currency_code, exchange_rate, rate_date, rate_time) VALUES ('$to', $xval, CURDATE(), CURTIME() )";
 $upd_sql= "UPDATE vtiger_currency_info set conversion_rate = ?, xr_date=NOW() where currency_code= ?";
 $uresult= $adb->pquery($upd_sql, array($xval,$to) );
}
else
{
 $to .= "-FAILED";
 $ins_sql= "INSERT INTO vtiger_currencies_rates (currency_code, exchange_rate, rate_date, rate_time) VALUES ('$to', 0, CURDATE(), CURTIME() ) ;";
}
 $iresult = $adb->pquery($ins_sql, array() );
}
}

function getYahooXrate($cur_from,$cur_to)
{
$yf_url = 'http://finance.yahoo.com/d/quotes.csv?f=l1d1t1&s='.$cur_from.$cur_to.'=X';
$handle = fopen($yf_url, 'r');

if ($handle)
 {
 $result = fgetcsv($handle);
 return $result;
 fclose($handle);
 }
else
 {
 return false;
 }
}
?>

Template file Smarty/templates/ CurrencyListView.tpl, near line # 52

This is the orginal code


<input class="crmButton create small" type="submit" value="{$MOD.LBL_NEW_CURRENCY}" />

 

This has to be like this  


<a href="getxrates.php" target="_blank">Refresh Rates</a>
<input class="crmButton create small" onclick="”alert(“Cookbook" type="submit" value="value=" />
<input class="crmButton create small" onclick="”href:" type="submit" value="value=" />”>
<input class="crmButton create small" type="submit" value="{$MOD.LBL_NEW_CURRENCY}" /> 

—————————————————————————————————–
Check http://www.vtigress.com for the latest release of vtDZiner.
Use Promo Code vtDZinerWP and avail a discount of 5% on the sale price of vtDZiner
—————————————————————————————————–

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: