Roland's Blog The ramblings of Roland Quast.

Automatically update vTiger currencies

Have been toying around with vTiger CRM lately. Have been spending a lot of time customizing it and getting it to work the way I want for our business. One of the things I required was multiple currencies, but I found out that vTiger doesn't have a built in currency updater. I took the liberty upon myself to figure out how to fix this, so here is my solution:

1. Go to http://apptools.com/phptools/xml/currency/howto.php and download the currencyconverter.zip file.

2. Unzip the contents to a directory on your server.

3. Create a php file (let's say, vtiger_currency_updater.php).

4. Put the following in the file:

<?php
//error_reporting(0);
$path=pathinfo($_SERVER['PHP_SELF']);
$path=$_SERVER['DOCUMENT_ROOT'].$path['dirname'];
require_once("$path/currencyexchange_class.php");
$cx=new currencyExchange();
$cx->getData();
$link = mysql_connect('localhost', 'yourdbusername', 'yourdbpassword');
if (!$link) {
 die('Could not connect: ' . mysql_error());
}
mysql_select_db('vtiger', $link);
$sql = "SELECT currency_code FROM vtiger_currency_info";
$result = mysql_query($sql, $link);
while ($row = mysql_fetch_assoc($result)) {
 $value=$cx->Convert("USD", $row["currency_code"], 1);
 $newValue = number_format($value, 3);
 $sql2 = "UPDATE vtiger_currency_info SET conversion_rate = " . $newValue . " WHERE currency_code = '" . $row["currency_code"] . "'";
 mysql_query($sql2, $link);
}
mysql_free_result($result);
mysql_close($link);
?>

5. Create a cron job to run the script like this (crontab -e to launch the crontab editor)...

0 2 * * * /path/to/php/bin/php -q /path/to/the/script/you/created/vtiger_currency_updater.php

This will run the script daily at 2am in the morning. I think the xml file used updates once a day anyway.

Done!