<?php
class exchangeRates{
var $fields = array('from', 'to', 'date', 'amount');
var $table = 'currency';
var $firstSupportedDay = 1167606000 ; //1st Jan 2007
var $hostName = '';
var $dbName = '';
var $user = '';
var $pass = '';
/**
* constructor function
* @return void
*/
function __construct(){
mysql_connect($this->hostName, $this->user, $this->pass) or $this->bail(__LINE__);
mysql_select_db($this->dbName) or $this->bail(__LINE__);
$this->init();
$this->today = strtotime('today');
if (empty($this->date)){
$this->date = $this->today;
}
$this->addRates();
if (isset($this->statement)) $this->statement= null;
}
/**
* for php4 compatibility
*/
function exchangeRates(){
$this->__construct();
}
/**
* main API for the conversion
* @return a [formatted] string for the currency conversion
* @param string $from the ISO code of the currency you want to convert from
* @param string $to the ISO code of the currency you want to convert tp
* @param float $amount[optional] the amount of the from currency you wish to convert. defaults to 1
* @param int $date[optional] the day for the conversion expressed as a unix time stamp. defaults to today's date. M
* @param bool $format[optional] whether to format the returned information. defaults to false (unformatted
*/
function convert($from, $to, $amount, $date=null, $format=false){
global $wpdb;
if(empty($date)){
$date = $this->today;
}
$sql = <<<SQL
SELECT t.rate,t.symbol,t.xDate
FROM currency t
INNER JOIN (SELECT symbol, max(xDate) as maxDate
FROM currency
WHERE xDate <= %d
AND symbol in ('%s', '%s')
GROUP BY symbol) AS m
ON (m.symbol = t.symbol
AND m.maxdate = t.xDate)
WHERE t.symbol in ('%s', '%s')
SQL;
$query = sprintf($sql, $date, $from, $to, $from, $to);
$result = @mysql_query($query) or $this->bail(__LINE__, $query);
while($pair = mysql_fetch_object($result)){
if ($pair->symbol == $from){
$this->fromRate = $pair->rate;
} else {
$this->toRate = $pair->rate;
}
}
//convert to USD
$usd = $amount / $this->fromRate;
$conversion = $usd * $this->toRate;
if ($format) {
return $this->getSymbol($to) . $this->formatted($conversion);
} else {
return $conversion;
}
}
function getSymbol($iso){
//will perform lookup of currency symbols. for the time being return the ISO symbol
return $iso;
}
function formatted ($number){
return number_format($number, $this->decPlaces, $this->decSeparator, $this->kSeparator);
}
function setFormat($decPlaces=2, $decSeparator='.', $kSeparator=','){
list($this->decPlaces, $this->decSeparator, $this->kSeparator) = func_get_args();
}
function incrementDay(){
$this->day = strtotime('+1 day', $this->day);
}
function addRates(){
$query = "select max(xdate) as mD from {$this->table}";
$s = mysql_query($query) or $this->bail(__LINE__, $query);
$obj = mysql_fetch_object($s);
$day = $obj->mD;
unset($s);
if ($day < $this->firstSupportedDay){
$day = $this->firstSupportedDay;
}
$this->day = $day;
if ($this->day < $this->today){
while ($this->day <= $this->today){
if ($this->day !== $this->firstSupportedDay){ //increment the day to avoid duplication
$this->incrementDay();
}
set_time_limit(30);
$this->getDailyRates($this->day);
$this->incrementDay();
}
if (!empty($this->parser)){
xml_parser_free($this->parser);
unset($this->parser);
}
}
}
function getDailyRates($day){
$date = date('Ymd', $day);
$url = "[URL unfurl="true"]http://finance.yahoo.com/webservice/v1/symbols/allcurrencies/quote;date=$date;currency=true?view=basic&format=xml&callback=currencyConverter.addConversionRates";[/URL]
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_HEADER, false);
curl_setopt($ch, CURLOPT_FRESH_CONNECT, true);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 30);
$response = curl_exec($ch);
$tdb = $this->getRatePairs($response);
$this->writeDB($tdb, $day);
}
function prepareInsertStatement(){
$sql = "insert into $this->table (xdate, rate, symbol) values (?, ?, ?)";
$statement = $this->pdo->prepare($sql);
if ($statement === false){
die (print_r($this->pdo->errorinfo(), true));
}
$this->insertStatement = $statement;
}
function writeDB($obj, $date){
if (empty($obj->symbol)) return;
$sql = "insert into $this->table (xdate, rate, symbol) values (%d, '%s', '%s')";
$data = array_map('mysql_real_escape_string', array($date, $obj->price, $obj->symbol) );
$query = vpsrintf($sql, $data);
@mysql_query($query) or $this->bail(__LINE__, $query);
}
function getRatePairs($xml){
if (empty($this->parser)){
$this->parser = xml_parser_create();
xml_parser_set_option($this->parser, XML_OPTION_CASE_FOLDING, 0);
xml_parser_set_option($this->parser, XML_OPTION_SKIP_WHITE, 1);
}
xml_parse_into_struct($this->parser, $xml, $values, $tags);
// loop through the structures
foreach ($tags as $key=>$val) {
if ($key == "resource") {
$dataRanges = $val;
for ($i=0; $i < count($dataRanges); $i+=2) {
$offset = $dataRanges[$i] + 1;
$len = $dataRanges[$i + 1] - $offset;
$obj = $this->parseFields(array_slice($values, $offset, $len));
$this->writeDB($obj, $this->day);
}
} else {
continue;
}
}
}
function parseFields($fields) {
$d = array('symbol', 'price');
$obj = new stdClass();
foreach ($fields as $r){
//$item = array();
if ($r['type'] == 'complete'){
if (in_array($r['attributes']['name'], $d)){
$obj->$r['attributes']['name'] = $r['value'];
}
}
}
if (isset($obj->symbol)){
$obj->symbol = str_replace('=X', '', $obj->symbol);
}
return $obj;
}
function init(){
$query = <<<SQL
CREATE TABLE if not exists $this->table (
symbol varchar(10) ,
rate float(10,4) ,
xdate int(15),
PRIMARY KEY (symbol, xdate))
SQL;
@mysql_query($query) or $this->bail(__LINE__, $query);
}
function bail($line, $query = null){
$error = mysql_error();
$query = empty($query) ? '' : <<<HTML
<p>Query was:<br/>
<pre>
$query
</pre>
</p>
HTML;
echo <<<HTML
<h1>Database Error</h1>
$query
<p>Error occurred at line $line. <br/>
Error message was: <span style="color:red">$error</span></p>
HTML;
exit();
}
}
$xR = new exchangeRates();
$xR ->setFormat(2,'.', ',');
echo "100 GBP is " . $xR->convert('EUR', 'GBP', '1399', null, true) ;
?>
