<?php
/*
Plugin Name: Currency Exchanger
Plugin URI: [URL unfurl="true"]http://rathercurious.net[/URL]
Description: currency exchange rate class
Version: 0.1.0
Author: Justin Adie
Author URI: [URL unfurl="true"]http://rathercurious.net[/URL]
*/
class exchangeRates{
private $fields = array('from', 'to', 'date', 'amount');
private $table = 'currency';
private $dbFile = './currencyExchangeRates2.sqlite';
private $firstSupportedDay = 1167606000 ; //1st Jan 2007
public function __construct(){
if (!file_exists($this->dbFile)){
$this->pdo = new PDO ("sqlite:{$this->dbFile}");
$this->init();
} else {
$this->pdo = new PDO ("sqlite:{$this->dbFile}");
}
$this->today = strtotime('today');
if (empty($this->date)){
$this->date = $this->today;
}
$this->addRates();
if (isset($this->statement)) $this->statement= null;
}
public function convert($from, $to, $amount, $date=null, $format=false){
global $wpdb;
if(empty($date)){
$date = $this->today;
}
foreach (array($from, $to) as $symbol){
$sql = "Select xDate as xDate from $this->table where symbol=? and xDate <= ? order by xDate desc limit 5";
$statement = $this->pdo->prepare($sql);
if ($statement === false){
die (print_r($this->pdo->errorinfo(), true));
}
$result = $statement->execute(array($symbol, $date));
if ($result === false){
die (print_r($result->errorinfo(), true));
}
while ($row = $statement->fetchObject()){
$results[$symbol][] = $row->xDate;
}
}
$dates = array_intersect($results[$from], $results[$to]);
if (count($dates) == 0){
die ('We do not have a currency exchange rate pair listed for the requested day nor for any day close thereto');
}
//will be the first that has the latest date
$sql = "select rate, symbol from $this->table where symbol in (?, ?) and xDate=?";
$statement = $this->pdo->prepare($sql);
$statement->execute(array($to, $from, $dates[0]));
$results = $statement->fetchAll(PDO::FETCH_OBJ);
//convert to USD
foreach($results as $pair){
if ($pair->symbol == $from){
$this->fromRate = $pair->rate;
} else {
$this->toRate = $pair->rate;
}
}
$usd = $amount / $this->fromRate;
$conversion = $usd * $this->toRate;
if ($format) {
return $this->getSymbol($to) . $this->formatted($conversion);
} else {
return $conversion;
}
}
private function getSymbol($iso){
//will perform lookup of currency symbols. for the time being return the ISO symbol
return $iso;
}
private function formatted ($number){
return number_format($number, $this->decPlaces, $this->decSeparator, $this->kSeparator);
}
public function setFormat($decPlaces=2, $decSeparator='.', $kSeparator=','){
list($this->decPlaces, $this->decSeparator, $this->kSeparator) = func_get_args();
}
private function incrementDay(){
$this->day = strtotime('+1 day', $this->day);
}
private function addRates(){
global $wpdb;
$query = "select max(xdate) as mD from {$this->table}";
$s = $this->pdo->query($query);
$obj = $s->fetchObject();
$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);
}
}
}
private 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);
}
private 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;
}
private function writeDB($obj, $date){
if (empty($obj->symbol)) return;
if (empty($this->insertStatement )){
$this->prepareInsertStatement();
}
$result = $this->insertStatement->execute(array($date, $obj->price, $obj->symbol));
if ($result === false){
die (print_r($this->insertStatement->errorInfo(), true));
}
}
private 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;
}
}
}
private 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;
}
private function init(){
$query[] = <<<SQL
CREATE TABLE if not exists $this->table (
symbol text ,
rate float ,
xdate int,
PRIMARY KEY (symbol, xdate) on conflict REPLACE)
SQL;
foreach ($query as $q) {
$result = $this->pdo->exec($q);
if ($result === false){
die (print_r($this->pdo->errorinfo(), true));
}
}
}
}
class timer{
public function start(){
$this->start = microtime(true);
}
private function stop(){
$this->stop = microtime(true);
}
public function report($format = true){
$this->stop();
$this->diff();
printf ("<hr/>Script took %s milliseconds", number_format($this->diff/1000, 8));
}
private function diff(){
$this->diff = $this->stop - $this->start;
}
}
$timer = new timer();
$timer->start();
$xR = new exchangeRates();
$xR ->setFormat(2,'.', ',');
echo "100 GBP is " . $xR->convert('GBP', 'EUR', '100', null, true) ;
$timer->report();
?>
by way of benchmarking, with the database fully loaded i get execution times of about 0.6 microseconds. three years of data took about 10 minutes to load up.
