Thursday, April 12, 2012

Changing prices in OpenCart according to script

I'm setting up a retail business to sell gold over the internet, using opencart. The difference is that I will only accept bitcoins (BTC), a new cryptographic currency.

The problem is that precious metal prices and the BTC/GBP exchange are very volatile. Thus I need my opencart prices to automatically update at a fairly frequent rate to avoid being caught out via moving exchange rates and selling at a loss.

I've written a script that is attached that pulls the gold, silver, and platinum price, then denominates the prices in bitcoins.

So, for each metallic item on the website, I'd like the prices of all products within the catalogue to change automatically (every 15 minutes would be ideal).

My question is: How do I update prices in the opencart database, according to a script?

The price has to change based on four things:
the type of precious metal the item contains.
the percentage markup for that item
the weight of precious metal the item contains.
the type of coin the item is.

I'd like these things to be attributes editable within opencart. For every item, there would be four (extra) attributes

Metal type
=> Gold
=> Silver
=> Platinum
=> Not a metal (for other items, it would then ignore all weight, price changes)
Percentage Markup
Percentage (integer?)
Coin Markup
So, let's say I'm selling a quarter ounce gold coin, of the Krugerrand type:

a quarter ounce gold coin weighs 7.77 grams, so on the product page the weight would be listed with that figure. The base price would be set at 7.77 grams * Gold price in grams (this figure is fetched using the attached php script).

My default markup from the wholesale gold price would be 10%, the 'base' price would be increased by 10%, this would also be listed on the product page.

Krugerrands, for historical reasons tend to include a value over and above the gold wholesale price. so the coin mark up would be around 5 bitcoins, the price would be increased to reflect this.
None of these price fiddles should be seen by the buyer, they should just see the price changes to reflect all of these

Every hour the price of all the products would change, to reflect the changes in the gold price, and the BTC GBP exchange rates.

Below is a PHP script to pull a gold price, a silver price, and a platinum price, then denominate the price of a gram of metal in bitcoins. It's the first thing I've ever programmed, so might be a little verbose, or not follow given conventions. The answer is likely to be very complicated, so I don't expect a full one. It'd be nice to receive some pointers though!

/* This is a script that ultimately displays the price of gold in grams, denominated in Bitcoins (BTC), a non fiat cryptographic currency. This is also the first thing I have ever programmed, so be nice */

// line break variable
$br = "<br> <br>";

// CURL begin
//curl session 1: grab metal prices
$ch1 = curl_init();
curl_setopt( $ch1, CURLOPT_URL, '' );
curl_setopt( $ch1, CURLOPT_RETURNTRANSFER, 1 );
$metalsdata = curl_exec( $ch1 );

// curl session 2: Grab exchange rate for BTC/GBP
$ch = curl_init();
curl_setopt( $ch, CURLOPT_URL, '' );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1 );
curl_setopt( $ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt( $ch, CURLOPT_SSL_VERIFYHOST, 0);
curl_setopt( $ch, CURLOPT_FRESH_CONNECT, 1);
curl_setopt( $ch, CURLOPT_TIMEOUT_MS, 1000);
curl_setopt( $ch, CURLOPT_USERAGENT, 'useragent');
$btcgbpdata = curl_exec( $ch );
curl_close( $ch );

// curl session 3: Grab British pounds to USD exchange rates
$ch1 = curl_init();
curl_setopt( $ch1, CURLOPT_URL, '' );
curl_setopt( $ch1, CURLOPT_Rcheck if my code isETURNTRANSFER, 1 );
$currencydata = curl_exec( $ch1 );

//gold digest: turn JSON data into readable php, and extract the gold price
$metals = json_decode( $metalsdata, true, 512 );
$goldprice = $metals['gold']['quote'];
$silverprice = $metals['silver']['quote'];
$platprice = $metals['platinum']['quote'];

//BTC digest of JSON

$btcgbp = json_decode( $btcgbpdata, true, 512 );
$btcgbpvwap = ($btcgbp['return']['vwap']['value']);
echo "$btcgbpvwap pounds per BTC";
echo $br;

//currency digest of JSON
$currency = json_decode( $currencydata, true, 512 );
$usdgbp = ($currency['rates']['GBP']);
echo "$usdgbp pence per USD dollar";
echo $br;
echo $br;
// convert metals into price per ounce in pounds

$goldpounds = $goldprice * $usdgbp;
$silverpounds = $silverprice * $usdgbp;
$platpounds = $platprice * $usdgbp;
echo "GBP $goldpounds per ounce of gold";
echo $br;
echo "GBP $silverpounds per ounce of silver";
echo $br;
echo "GBP $platpounds per ounce of platinum";
echo $br;
echo $br;

//metals prices in grams
$goldpoundsgram = $goldpounds / 31.1034768;
$silverpoundsgram = $silverpounds / 31.1034768;
$platpoundsgram = $platpounds / 31.1034768;
echo "$goldpoundsgram pounds per gram of gold";
echo $br;
echo "$silverpoundsgram pounds per gram of silver";
echo $br;
echo "$platpoundsgram pounds per gram of platinum";
echo $br;
echo $br;
// metal prices denominated in BTC
$btcgoldgram = ($goldpoundsgram / $btcgbpvwap);
$btcsilvergram = ($silverpoundsgram / $btcgbpvwap);
$btcplatgram = ($platpoundsgram / $btcgbpvwap);
echo "<b>";
echo $btcgoldgram;
echo " bitcoins per gram of gold";
echo $br;
echo $btcsilvergram;
echo " bitcoins per gram of silver";
echo $br;
echo $btcplatgram;
echo " bitcoins per gram of platinum";


1 comment:

  1. actually price is just a numeric field in table product, you can update it with a simple update statement based on product_id.

    There is also a column cost in the same table, used just to report income in administration panel. You could update that as well, if you are interested in such report.

    To recover the product_id you can use product' name from product_description (beware names are localized). Say your DB contains products named gram of silver then your update could be

    update product p,product_description d set price=$btcsilvergram
    where p.product_id=d.product_id and name='gram of silver'

    Values are tax free.