function xchange796_query($appid, $apikey, $secretkey, $url)
{
// from https://796.com/wiki.html
$timestamp = time();
$params = array('appid' => $appid, 'apikey' => $apikey, 'secretkey' => $secretkey, 'timestamp' => $timestamp);
ksort($params);
// "be careful that the sequence is quite important"
$param_uri = http_build_query($params, '', '&');
$sig = base64_encode(hash_hmac('sha1', $param_uri, $secretkey));
$token_url = url_add("https://796.com/oauth/token", array('appid' => $appid, 'timestamp' => $timestamp, 'apikey' => $apikey, 'secretkey' => $secretkey, 'sig' => $sig));
// our curl handle (initialize if required)
$ch = crypto_curl_init();
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_USERAGENT, 'Mozilla/4.0 (compatible; 796 PHP client; ' . php_uname('s') . '; PHP/' . phpversion() . ')');
curl_setopt($ch, CURLOPT_URL, crypto_wrap_url($token_url));
// curl_setopt($ch, CURLOPT_POSTFIELDS, $post_data);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 1);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
// run the query
$res = curl_exec($ch);
if ($res === false) {
throw new ExternalAPIException('Could not get reply: ' . curl_error($ch));
}
$dec = crypto_json_decode($res, "in authentication");
if (isset($dec['errno']) && $dec['errno']) {
throw new ExternalAPIException("Could not get OAuth Token: " . htmlspecialchars($dec['msg']));
}
if (!isset($dec['data']['access_token'])) {
throw new ExternalAPIException("No access token provided");
}
$token = $dec['data']['access_token'];
crypto_log("Obtained OAuth token");
// now, call the given URL
// 796 has a bug where the token can't be urlencoded again, so we can't use url_add() (though we should)
$destination_url = $url . "?access_token=" . $token;
// our curl handle (initialize if required)
$ch = crypto_curl_init();
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_USERAGENT, 'Mozilla/4.0 (compatible; 796 PHP client; ' . php_uname('s') . '; PHP/' . phpversion() . ')');
curl_setopt($ch, CURLOPT_URL, crypto_wrap_url($destination_url));
// curl_setopt($ch, CURLOPT_POSTFIELDS, $post_data);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 1);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
// run the query
$res = curl_exec($ch);
if ($res === false) {
throw new ExternalAPIException('Could not get reply: ' . curl_error($ch));
}
$dec = crypto_json_decode($res, "in request");
if (isset($dec['errno']) && $dec['errno']) {
throw new ExternalAPIException("Error in reply: " . htmlspecialchars($dec['msg']));
}
if (!isset($dec['data'])) {
throw new ExternalAPIException("No data in reply");
}
return $dec['data'];
}
function callback($data)
{
$table = $this->table;
$query = array();
$args = array();
foreach ($data as $key => $value) {
$query[] = $key . " = :" . $key;
$args[$key] = $value;
}
$args["id"] = $this->account['id'];
crypto_log("Self-updating table '{$table}'");
$q = db()->prepare("UPDATE {$table} SET " . implode(", ", $query) . " WHERE id=:id");
$q->execute($args);
}
function delete_user($id)
{
$user = get_user($id);
if (!$user) {
throw new Exception("No such user {$id}");
}
crypto_log("Deleting user " . ($user ? htmlspecialchars(print_r($user, true)) : "<i>(phantom)</i>"));
// go through all accounts
$already_done = array();
foreach (account_data_grouped() as $label => $accounts) {
foreach ($accounts as $key => $account) {
if ($account['table'] != 'graphs' && !isset($already_done[$account['table']])) {
delete_from($account['table']);
$already_done[$account['table']] = 1;
}
}
}
delete_from('balances');
delete_from('address_balances');
delete_from('hashrates');
delete_from('securities');
delete_from('offsets');
delete_from('summary_instances');
delete_from('summaries');
delete_from('graph_data_summary');
delete_from('graph_data_balances');
delete_from('pending_subscriptions');
// delete graphs
crypto_log("Deleting graphs...");
$q = db()->prepare("SELECT * FROM graph_pages WHERE user_id=?");
$q->execute(array($user['id']));
$pages = $q->fetchAll();
foreach ($pages as $page) {
$q = db()->prepare("DELETE FROM graphs WHERE page_id=?");
$q->execute(array($page['id']));
crypto_log("(" . number_format($q->rowCount()) . " rows deleted)");
}
delete_from('graph_pages');
delete_from('managed_graphs');
crypto_log("Deleting user_properties...");
$q = db()->prepare("DELETE FROM user_properties WHERE id=?");
$q->execute(array($user['id']));
crypto_log("(" . number_format($q->rowCount()) . " rows deleted)");
// finally delete the user object
crypto_log("Deleting user...");
$user = Users\User::findUser(db(), $user['id']);
$user->delete(db());
}
<?php
/**
* An existing free user has not logged in within X days and we
* now need to disable their account.
*/
// get the relevant user info
$user = get_user($job['arg_id']);
if (!$user) {
throw new JobException("Cannot find user ID " . $job['arg_id']);
}
// check that they're not a premium user etc - this should never happen
if ($user['is_premium']) {
throw new JobException("Premium user was requested to be disabled - this should not happen");
}
// update user (before sending email)
$q = db()->prepare("UPDATE user_properties SET is_disabled=1,disabled_at=NOW() WHERE id=? LIMIT 1");
$q->execute(array($user['id']));
// construct email
if ($user['email']) {
$disables_at = strtotime(($user['last_login'] ? $user['last_login'] : $user['created_at']) . " +" . get_site_config('user_expiry_days') . " day");
send_user_email($user, "disable", array("name" => $user['name'] ? $user['name'] : $user['email'], "days" => number_format(get_site_config('user_expiry_days')), "disables" => iso_date($disables_at), "disables_text" => recent_format($disables_at, false, ""), "url" => absolute_url(url_for("user#user_premium")), "login" => absolute_url(url_for("login")), "profile" => absolute_url(url_for("profile"))));
crypto_log("Sent disabled account e-mail to " . htmlspecialchars($user['email']) . ".");
} else {
crypto_log("User had no valid e-mail address.");
}
<?php
/**
* Generic API job.
*/
$exchange = "generic";
// get the relevant address
$q = db()->prepare("SELECT * FROM accounts_generic WHERE user_id=? AND id=?");
$q->execute(array($job['user_id'], $job['arg_id']));
$account = $q->fetch();
if (!$account) {
throw new JobException("Cannot find a {$exchange} account " . $job['arg_id'] . " for user " . $job['user_id']);
}
$balance = crypto_get_contents(crypto_wrap_url($account['api_url']));
if (!is_numeric($balance)) {
crypto_log("{$exchange} balance for " . htmlspecialchars($account['api_url']) . " is non-numeric: " . htmlspecialchars($balance));
throw new ExternalAPIException("Generic API returned non-numeric balance: " . htmlspecialchars(substr($balance, 0, 100)));
} else {
// issue #11: add editable multiplier
crypto_log("{$exchange} balance: {$balance} * " . $account['multiplier']);
$balance = $balance * $account['multiplier'];
crypto_log("{$exchange} balance for " . htmlspecialchars($account['api_url']) . ": " . $balance);
}
insert_new_balance($job, $account, $exchange, $account['currency'], $balance);
}
require __DIR__ . "/_havelock.php";
$content = havelock_query("https://www.havelockinvestments.com/r/balance", array('key' => $account['api_key']));
// balance, balanceavailable, balanceescrow
$wallet = $content['balance']['balance'];
$balance = 0;
crypto_log("{$exchange} wallet balance for " . $job['user_id'] . ": " . $wallet);
// set is_recent=0 for all old security instances for this user
$q = db()->prepare("UPDATE securities SET is_recent=0 WHERE user_id=? AND exchange=? AND account_id=?");
$q->execute(array($job['user_id'], $exchange, $account['id']));
// assume we don't need to delay
$content = havelock_query("https://www.havelockinvestments.com/r/portfolio", array('key' => $account['api_key']));
if ($content['portfolio'] && is_array($content['portfolio'])) {
foreach ($content['portfolio'] as $entry) {
// the API returns the marketvalue, so we can just use that rather than calculate it from previous jobs (like btct)
crypto_log("{$exchange} security balance for " . htmlspecialchars($entry['symbol']) . ": " . $entry['quantity'] . '*' . $entry['lastprice'] . "=" . $entry['marketvalue']);
$balance += $entry['marketvalue'];
// find the security ID, if there is one
$q = db()->prepare("SELECT * FROM securities_havelock WHERE name=?");
$q->execute(array($entry['symbol']));
$security_def = $q->fetch();
if ($security_def) {
// insert security instance
$q = db()->prepare("INSERT INTO securities SET user_id=:user_id, exchange=:exchange, security_id=:security_id, quantity=:quantity, account_id=:account_id, is_recent=1");
$q->execute(array('user_id' => $job['user_id'], 'exchange' => $exchange, 'security_id' => $security_def['id'], 'quantity' => $entry['quantity'], 'account_id' => $account['id']));
}
}
}
// we've now calculated both the wallet balance + the value of all securities
insert_new_balance($job, $account, $exchange . "_wallet", $currency, $wallet);
insert_new_balance($job, $account, $exchange . "_securities", $currency, $balance);
* so we will have historical data even if no user has the security yet.
*/
$exchange = "securities_litecoininvest";
$currency = 'ltc';
// get the API data
$json = crypto_json_decode(crypto_get_contents(crypto_wrap_url('https://www.litecoininvest.com/api/ticker')));
foreach ($json as $security => $data) {
crypto_log("Parsing security '" . htmlspecialchars($security) . "'");
// we now have a new value
$balance = $data['bid'];
// also available: ask, latest, outstanding, 24h_vol, etc
// if this security has a balance of 0, then it's worthless and it's not really
// worth saving into the database
if ($balance == 0) {
crypto_log("Security '" . htmlspecialchars($security) . "' had a bid of 0: ignoring");
continue;
}
$q = db()->prepare("SELECT * FROM securities_litecoininvest WHERE name=?");
$q->execute(array($security));
$security_def = $q->fetch();
if (!$security_def) {
// need to insert a new security definition, so we can later get its value
// we can't calculate the value of this security yet
crypto_log("No securities_litecoininvest definition existed for '" . htmlspecialchars($security) . "': adding in new definition");
$q = db()->prepare("INSERT INTO securities_litecoininvest SET name=?");
$q->execute(array($security));
$security_def = array('name' => $security, 'id' => db()->lastInsertId());
}
// since we already have bid data here, we might as well save it for free
insert_new_balance($job, $security_def, $exchange, $currency, $balance);
}
$wizard = get_wizard_account_type($account['wizard']);
if (!$wizard['transaction_creation']) {
continue;
}
$q = db()->prepare("SELECT * FROM " . $account['table'] . " WHERE user_id=?" . (isset($account['query']) ? $account['query'] : false));
$q->execute(array($job['user_id']));
while ($a = $q->fetch()) {
$a['exchange'] = $exchange;
$current_accounts[$exchange . " " . $a['id']] = $a;
}
}
}
crypto_log("User " . $job['user_id'] . " has " . count($current_accounts) . " accounts to parse.");
// are there any creators that need to be deleted for this user?
$q = db()->prepare("SELECT * FROM transaction_creators WHERE user_id=?");
$q->execute(array($job['user_id']));
$to_delete = array();
while ($a = $q->fetch()) {
if (!isset($current_accounts[$a['exchange'] . " " . $a['account_id']])) {
$to_delete[] = $a['id'];
} else {
unset($current_accounts[$a['exchange'] . " " . $a['account_id']]);
}
}
if ($to_delete) {
crypto_log("Need to delete " . count($to_delete) . " old transaction creators");
$q = db()->prepare("DELETE FROM transaction_creators WHERE user_id=? AND id IN (" . implode(",", $to_delete) . ")");
$q->execute(array($job['user_id']));
}
crypto_log("Complete.");
$args += array("label" => "total " . get_currency_abbr($currency));
} else {
if (substr($account['summary_type'], 0, strlen('all2')) == 'all2') {
$summary_type = substr($account['summary_type'], strlen('all2'));
$summary_types = get_total_conversion_summary_types();
$args += array("label" => "converted " . $summary_types[$summary_type]['short_title']);
} else {
throw new JobException("Unknown summary_instance summary_type '" . htmlspecialchars($account['summary_type']) . "'");
}
}
}
$args['label_uc'] = capitalize($args['label']);
break;
default:
throw new JobException("Unknown notification type for email '" . $notification['notification_type'] . "'");
}
send_user_email($user, $email_template, $args);
crypto_log("Sent notification e-mail to " . htmlspecialchars($user['email']) . ".");
// update user stats
$q = db()->prepare("UPDATE user_properties SET notifications_sent=notifications_sent+1 WHERE id=?");
$q->execute(array($user['id']));
}
// update the notification
$q = db()->prepare("UPDATE notifications SET is_notified=1,last_notification=NOW(),last_value=?,notifications_sent=notifications_sent+1 WHERE id=?");
$q->execute(array($current_value, $notification['id']));
} else {
crypto_log("Trigger not successful.");
// update the notification
$q = db()->prepare("UPDATE notifications SET is_notified=0,last_value=? WHERE id=?");
$q->execute(array($current_value, $notification['id']));
}
/**
* @param $block may be {@code null}
* @throws {@link BalanceException} if something happened and the balance could not be obtained.
*/
function getBalanceAtBlock($address, $block = null, Logger $logger, $is_received = false)
{
$code = $this->currency->getCode();
if ($is_received) {
$logger->info("We are looking for received balance.");
}
// do we have a block count?
if ($this->currency instanceof BlockCurrency && !$block) {
// TODO this needs to be cacheable between requests, otherwise we're going to end
// up spamming services for block counts!
$logger->info("Finding most recent block count...");
$block = $this->currency->getBlockCount($logger) - $this->confirmations;
}
$logger->info("Ignoring blocks after block " . number_format($block));
// we can now request the HTML page
$url = sprintf($this->url, $address);
$logger->info($url);
try {
$html = Fetch::get($url);
} catch (\Apis\FetchHttpException $e) {
// don't return raw HTML if we can find a valid error message
if (strpos($e->getContent(), "Not a valid address")) {
throw new BalanceException("Not a valid address", $e);
}
if (strpos($e->getContent(), "Address not seen on the network")) {
throw new BalanceException("Address not seen on the network", $e);
}
// otherwise, throw HTML as normal
throw new BalanceException($e->getContent(), $e);
}
$html = $this->stripHTML($html);
// assumes that the page format will not change
if (!$is_received && preg_match('#(<p>|<tr><th>|<tr><td>)Balance:?( |</th><td>|</td><td>)([0-9,\\.]+) ' . $this->currency->getAbbr() . '#im', $html, $matches)) {
$balance = str_replace(",", "", $matches[3]);
$logger->info("Address balance before removing unconfirmed: " . $balance);
// transaction, block, date, amount, [balance,] currency
if (preg_match_all('#<tr><td>.+</td><td><a href=[^>]+>([0-9]+)</a></td><td>.+?</td><td>(- |\\+ |)([0-9,\\.\\(\\)]+)</td>(|<td>([0-9\\.]+)</td>)<td>' . $this->currency->getAbbr() . '</td></tr>#im', $html, $matches, PREG_SET_ORDER)) {
foreach ($matches as $match) {
if ($match[1] >= $block) {
// too recent
$amount = str_replace(",", "", $match[3]);
if (substr($amount, 0, 1) == "(" && substr($amount, -1) == ")") {
// convert (1.23) into -1.23
$amount = -substr($amount, 1, strlen($amount) - 2);
}
if ($match[2] == "+ ") {
$amount = +$amount;
} else {
if ($match[2] == "- ") {
$amount = -$amount;
}
}
$logger->info("Removing " . $amount . " from balance: unconfirmed (block " . $match[1] . " >= " . $block . ")");
$balance -= $amount;
}
}
$logger->info("Confirmed balance after " . $this->confirmations . " confirmations: " . $balance);
} else {
$this->foundNoTransactions($logger);
}
} else {
if ($is_received && preg_match('#(|<tr><th>|<tr><td>)Received:?( |</th><td>|</td><td>)([0-9,\\.]+) ' . $this->currency->getAbbr() . '#i', $html, $matches)) {
$balance = str_replace(",", "", $matches[3]);
$logger->info("Address received before removing unconfirmed: " . $balance);
// transaction, block, date, amount, [balance,] currency
if (preg_match_all('#<tr><td>.+</td><td><a href=[^>]+>([0-9]+)</a></td><td>.+?</td><td>(- |\\+ |)([0-9,\\.\\(\\)]+)</td>(|<td>([0-9\\.]+)</td>)<td>' . $this->currency->getAbbr() . '</td></tr>#im', $html, $matches, PREG_SET_ORDER)) {
foreach ($matches as $match) {
if ($match[1] >= $block) {
// too recent
$amount = str_replace(",", "", $match[3]);
if (substr($amount, 0, 1) == "(" && substr($amount, -1) == ")") {
// convert (1.23) into -1.23
$amount = -substr($amount, 1, strlen($amount) - 2);
}
if ($match[2] == "+ ") {
$amount = +$amount;
} else {
if ($match[2] == "- ") {
$amount = -$amount;
}
}
// only consider received
if ($amount > 0) {
$logger->info("Removing " . $amount . " from received: unconfirmed (block " . $match[1] . " >= " . $block . ")");
$balance -= $amount;
}
}
}
$logger->info("Confirmed received after " . $this->confirmations . " confirmations: " . $balance);
} else {
$this->foundNoTransactions($logger);
}
} else {
if (strpos($html, "Address not seen on the network.") !== false) {
// the address is valid, it just doesn't have a balance
$balance = 0;
$logger->info("Address is valid, but not yet seen on network");
} else {
if (strpos($html, "Not a valid address.") !== false || strpos($html, "Please enter search terms") !== false) {
// the address is NOT valid
throw new BalanceException("Not a valid address");
} else {
if (strpos($html, "this address has too many records to display") !== false) {
// this address is valid, and it has a balance, but it has too many records for this Abe instance
crypto_log("Address is valid, but has too many records to display");
throw new BalanceException("Address has too many transactions");
} else {
if (strpos(strtolower($html), "500 internal server error") !== false) {
crypto_log("Server returned 500 Internal Server Error");
throw new BalanceException("Server returned 500 Internal Server Error");
} else {
throw new BalanceException("Could not find balance on page");
}
}
}
}
}
}
return $balance;
}
// update as necessary
$stored[$date][$user_id][$exchange][$account_id][$currency]['min'] = min($balance['balance'], $stored[$date][$user_id][$exchange][$account_id][$currency]['min']);
$stored[$date][$user_id][$exchange][$account_id][$currency]['max'] = max($balance['balance'], $stored[$date][$user_id][$exchange][$account_id][$currency]['max']);
$stored[$date][$user_id][$exchange][$account_id][$currency]['close'] = $balance['balance'];
$stored[$date][$user_id][$exchange][$account_id][$currency]['samples']++;
$stored[$date][$user_id][$exchange][$account_id][$currency]['values'][] = $balance['balance'];
}
crypto_log("Processed " . number_format($count) . " balances entries");
// we now have lots of data; insert it
// danger! danger! five nested loops!
$insert_count = 0;
foreach ($stored as $date => $a) {
foreach ($a as $user_id => $b) {
foreach ($b as $exchange => $c) {
foreach ($c as $account_id => $d) {
foreach ($d as $currency => $summary) {
$q = db_master()->prepare("INSERT INTO graph_data_balances SET\n user_id=:user_id, exchange=:exchange, account_id=:account_id, currency=:currency, data_date=:data_date, samples=:samples,\n balance_min=:min, balance_opening=:open, balance_closing=:close, balance_max=:max, balance_stdev=:stdev");
$q->execute(array('user_id' => $user_id, 'exchange' => $exchange, 'account_id' => $account_id, 'currency' => $currency, 'data_date' => $date, 'samples' => $summary['samples'], 'min' => $summary['min'], 'open' => $summary['open'], 'close' => $summary['close'], 'max' => $summary['max'], 'stdev' => stdev($summary['values'])));
$insert_count++;
}
}
}
}
}
crypto_log("Inserted " . number_format($insert_count) . " balances entries into graph_data_balances");
// finally, delete all the old data
// we've exhausted over everything so this should be safe
$q = db_master()->prepare("DELETE FROM balances WHERE created_at <= :date ORDER BY created_at ASC");
$q->execute(array("date" => $cutoff_date));
crypto_log("Deleted " . number_format($count) . " summary entries");
batch_footer();
add_summary_instance($job, 'crypto2' . $currency, $total);
}
}
crypto_log("</ul>");
}
// update last_sum_job
$q = db()->prepare("UPDATE user_properties SET last_sum_job=NOW() WHERE id=?");
$q->execute(array($job['user_id']));
// and now that we have added summary instances, check for first_report
// (this is so that first_report jobs don't block up the job queue)
/**
* Send an e-mail to new users once their first non-zero summary reports have been compiled.
*/
// reload user in case multiple summary jobs for the same user are all blocked at once
$user = get_user($job['user_id']);
if (!$user['is_first_report_sent']) {
// is there a non-zero summary instance?
$q = db()->prepare("SELECT * FROM summary_instances WHERE user_id=? AND is_recent=1 AND balance > 0 LIMIT 1");
$q->execute(array($user['id']));
if ($instance = $q->fetch()) {
crypto_log("User has a non-zero summary instance.");
// update that we've reported now
$q = db()->prepare("UPDATE user_properties SET is_first_report_sent=1,first_report_sent=NOW() WHERE id=?");
$q->execute(array($user['id']));
// send email
if ($user['email']) {
send_user_email($user, "first_report", array("name" => $user['name'] ? $user['name'] : $user['email'], "url" => absolute_url(url_for("profile")), "login" => absolute_url(url_for("login")), "wizard_currencies" => absolute_url(url_for("wizard_currencies")), "wizard_addresses" => absolute_url(url_for("wizard_accounts_addresses")), "wizard_accounts" => absolute_url(url_for("wizard_accounts")), "wizard_notifications" => absolute_url(url_for("wizard_notifications")), "reports" => absolute_url(url_for("profile")), "premium" => absolute_url(url_for("premium"))));
crypto_log("Sent first report e-mail to " . htmlspecialchars($user['email']) . ".");
}
}
}
<?php
/**
* A batch script to clean up old jobs.
* This always executes (no job framework) so it should be used sparingly or as necessary.
*
* Arguments (in command line, use "-" for no argument):
* $key/1 required the automated key
*/
define('USE_MASTER_DB', true);
// always use the master database for selects!
require __DIR__ . "/../inc/global.php";
require __DIR__ . "/_batch.php";
require_batch_key();
batch_header("Batch cleanup jobs", "batch_cleanup_jobs");
crypto_log("Current time: " . date('r'));
// simply delete all jobs that haven't executed and are over three months old
$q = db_master()->prepare("DELETE FROM jobs WHERE is_executed=1 AND executed_at < DATE_SUB(NOW(), INTERVAL 1 MONTH)");
$q->execute(array());
crypto_log("Deleted old jobs.");
batch_footer();
<?php
/**
* Openclerk version check job.
*/
$exchange = "version_check";
crypto_log("Local version: " . get_site_config('openclerk_version'));
// call cryptfolio.com to find the latest openclerk.org version
$version = crypto_get_contents(crypto_wrap_url(url_add('https://cryptfolio.com/version', array('absolute_url' => get_site_config('absolute_url'), 'openclerk_version' => get_site_config('openclerk_version')))));
crypto_log("Remote version: " . $version);
if (!$version) {
throw new ExternalAPIException("Could not retrieve remote Openclerk version");
}
// compare
if (version_compare($version, get_site_config('openclerk_version')) > 0) {
// delete any unread messages
$q = db()->prepare("DELETE FROM admin_messages WHERE message_type=? AND is_read=0");
$q->execute(array('version_check'));
// and insert a new one
$q = db()->prepare("INSERT INTO admin_messages SET message_type=?, message=?");
$q->execute(array('version_check', '<a href="http://openclerk.org">A new version</a> of Openclerk is available: ' . $version));
crypto_log("Inserted new admin_message.");
}
<?php
/**
* Restore market average data for a particular day (#457).
*/
crypto_log("Restoring market average data for day " . $job['arg_id']);
$q = db()->prepare("SELECT * FROM ticker WHERE created_at_day=? AND is_daily_data=1 AND exchange <> 'average'");
$q->execute(array($job['arg_id']));
$recents = $q->fetchAll();
crypto_log("Found " . number_format(count($recents)) . " ticker instances to recreate average data");
$exchange = array('name' => 'average');
require __DIR__ . "/_average.php";
// we can now create ticker values as necessary
foreach ($pairs as $pair) {
if ($pair['total_volume'] > 0) {
// delete any old average data
$q = db()->prepare("DELETE FROM ticker WHERE exchange=:exchange AND currency1=:currency1 AND currency2=:currency2 AND created_at_day=TO_DAYS(:date)");
$q->execute(array("date" => $recents[0]['created_at'], "exchange" => $exchange['name'], "currency1" => $pair['currency1'], "currency2" => $pair['currency2']));
crypto_log("Deleted any old average data");
// insert in new ticker value
$q = db()->prepare("INSERT INTO ticker SET exchange=:exchange, currency1=:currency1, currency2=:currency2, last_trade=:last_trade, bid=:bid, ask=:ask, volume=:volume, job_id=:job_id, is_daily_data=1, created_at=:date, created_at_day=TO_DAYS(:date)");
$q->execute(array("date" => $recents[0]['created_at'], "exchange" => $exchange['name'], "currency1" => $pair['currency1'], "currency2" => $pair['currency2'], "last_trade" => $pair['total_last_trade'] / $pair['total_volume'], "bid" => $pair['total_volume_bid'] > 0 ? $pair['total_bid'] / $pair['total_volume_bid'] : 0, "ask" => $pair['total_volume_ask'] > 0 ? $pair['total_ask'] / $pair['total_volume_ask'] : 0, "volume" => $pair['total_volume'], "job_id" => $job['id']));
crypto_log("Inserted in new ticker ID " . db()->lastInsertId());
}
// no need to track average market count: this isn't used in historical data
}
foreach ($data as $row) {
$security = $row['ticker'];
$bid = $row['bid'];
// also available: avg_buy_price
// make sure that a security definition exists
$q = db()->prepare("SELECT * FROM securities_litecoininvest WHERE name=?");
$q->execute(array($security));
$security_def = $q->fetch();
if (!$security_def) {
// need to insert a new security definition, so we can later get its value
// we can't calculate the value of this security yet
crypto_log("No securities_litecoininvest definition existed for '" . htmlspecialchars($security) . "': adding in new definition");
$q = db()->prepare("INSERT INTO securities_litecoininvest SET name=?");
$q->execute(array($security));
$security_def = array('id' => db()->lastInsertId());
}
// insert in a new balance
$job2 = $job;
$job2['user_id'] = get_site_config('system_user_id');
/* need to insert security values as system user, or else they won't be displayed in a graph! */
insert_new_balance($job2, $security_def, "securities_" . $exchange, $currency, $bid);
$calculated = $bid * $row['quantity'];
crypto_log(htmlspecialchars($security) . " @ " . htmlspecialchars($bid) . " x " . number_format($row['quantity']) . " = " . htmlspecialchars($calculated));
$balance += $calculated;
// insert security instance
$q = db()->prepare("INSERT INTO securities SET user_id=:user_id, exchange=:exchange, security_id=:security_id, quantity=:quantity, account_id=:account_id, is_recent=1");
$q->execute(array('user_id' => $job['user_id'], 'exchange' => $exchange, 'security_id' => $security_def['id'], 'quantity' => $row['quantity'], 'account_id' => $account['id']));
}
// we've now calculated both the wallet balance + the value of all securities
insert_new_balance($job, $account, $exchange . '_wallet', $currency, $wallet);
insert_new_balance($job, $account, $exchange . '_securities', $currency, $balance);
<?php
/**
* 796 security value job.
* Retrieves the current 'bid' value for a particular security.
*/
$exchange = "securities_796";
$currency = 'btc';
// get the relevant address
$q = db()->prepare("SELECT * FROM securities_796 WHERE id=?");
$q->execute(array($job['arg_id']));
$account = $q->fetch();
if (!$account) {
throw new JobException("Cannot find a {$exchange} account " . $job['arg_id']);
}
$content = crypto_get_contents(crypto_wrap_url('http://api.796.com/v3/stock/ticker.html?type=' . urlencode($account['api_name'])));
if (!$content) {
throw new ExternalAPIException("API returned empty data");
}
$data = crypto_json_decode($content);
// also available: last, high, low, vol, buy, sell
if (!isset($data['ticker']['last'])) {
throw new ExternalAPIException("External API returned no last price");
}
$balance = $data['ticker']['last'];
crypto_log("Last price for " . htmlspecialchars($account['name']) . ": " . $balance);
insert_new_balance($job, $account, $exchange, $currency, $balance);
function handle(array $record)
{
$message = $record['message'];
if (is_valid_url($message)) {
return crypto_wrap_url($message);
}
if ($record['level'] >= Logger::WARNING) {
if ($record['level'] >= Logger::ERROR) {
$message = "[ERROR] " . $message;
} else {
$message = "[Warning] " . $message;
}
}
crypto_log($message);
}
crypto_log("Summary instance type: " . $account['summary_type'] . " for user " . $notification['user_id']);
// get the most recent value
$q = db()->prepare("SELECT * FROM summary_instances WHERE user_id=:user_id AND summary_type=:summary_type AND is_recent=1 LIMIT 1");
$q->execute(array("user_id" => $notification['user_id'], "summary_type" => $account['summary_type']));
$summary_instance = $q->fetch();
if (!$summary_instance) {
// TODO maybe support failing for notifications, to disable notifications for e.g. accounts that no longer exist?
// probably better to make sure that we can never *have* a referenced account that never exists
throw new JobException("Could not find any recent summary instance values for " . $account['summary_type'] . " for user " . $notification['user_id']);
}
$current_value = $summary_instance['balance'];
// what was the last value?
// may need to generate this if no value exists, but hopefully this only occurs very rarely,
// since this may be a very heavy query
if ($notification['last_value'] === null) {
crypto_log("No last value found: retrieving");
// get the query string for this interval
$periods = get_permitted_notification_periods();
if (!isset($periods[$notification['period']]['interval'])) {
throw new JobException("Unknown job period '" . $notification['period'] . "'");
}
$period = $periods[$notification['period']]['interval'];
$q = db()->prepare("SELECT * FROM summary_instances WHERE user_id=:user_id AND summary_type=:summary_type AND created_at <= DATE_SUB(NOW(), {$period}) ORDER BY id DESC LIMIT 1");
$q->execute(array("user_id" => $notification['user_id'], "summary_type" => $account['summary_type']));
$last = $q->fetch();
if (!$last) {
throw new JobException("Could not find any last values for " . $account['summary_type'] . " for user " . $notification['user_id']);
}
$notification['last_value'] = $last['balance'];
}
// other parameters
$security_def = array('id' => db()->lastInsertId());
} else {
if (!isset($balances[$security_def['currency']])) {
// this allows us to safely ignore securities in other currencies
crypto_log("Security {$security} is not a currently recognised currency: " . $security_def['currency']);
} else {
// the 'balance' for this security is the 'bid'
$q = db()->prepare("SELECT * FROM balances WHERE exchange=:exchange AND account_id=:account_id AND is_recent=1 LIMIT 1");
$q->execute(array("exchange" => "securities_cryptostocks", "account_id" => $security_def['id']));
$security_value = $q->fetch();
if (!$security_value) {
// we can't calculate the value of this security yet
crypto_log("Security " . htmlspecialchars($security) . " does not yet have a calculated value");
} else {
$calculated = $security_value['balance'] * $shares;
crypto_log(htmlspecialchars($security) . " @ " . htmlspecialchars($security_value['balance']) . " x " . number_format($shares) . " = " . htmlspecialchars($calculated) . " " . strtoupper($security_def['currency']));
$balances[$security_def['currency']] += $calculated;
}
}
}
// insert security instance
// but only if we actually have a quantity
if ($shares != 0) {
$q = db()->prepare("INSERT INTO securities SET user_id=:user_id, exchange=:exchange, security_id=:security_id, quantity=:quantity, account_id=:account_id, is_recent=1");
$q->execute(array('user_id' => $job['user_id'], 'exchange' => $exchange, 'security_id' => $security_def['id'], 'quantity' => $shares, 'account_id' => $account['id']));
}
}
// we've now calculated both the wallet balance + the value of all securities
foreach ($wallets as $currency => $balance) {
insert_new_balance($job, $account, $exchange . '_wallet', $currency, $balance);
}
// "How long does it take for a page to be generated?"
// "What pages have the most database queries?"
// "What pages spend the most time in PHP as opposed to the database?"
// "How many jobs are running per hour?"
// "What jobs have the most database queries?"
// "Which jobs time out the most?"
// "How many blockchain requests fail?"
// "What jobs take the longest requesting URLs?"
// "How many jobs are being queued at once?"
// "Which queue types take the longest?"
// "What are the most common graph types?"
// "How many ticker graphs are being requested?"
// we've processed all the data we want; delete old metrics data
$q = db_master()->prepare("DELETE FROM performance_metrics_slow_queries");
$q->execute();
$q = db_master()->prepare("DELETE FROM performance_metrics_slow_urls");
$q->execute();
$q = db_master()->prepare("DELETE FROM performance_metrics_repeated_queries");
$q->execute();
$q = db_master()->prepare("DELETE FROM performance_metrics_repeated_urls");
$q->execute();
$q = db_master()->prepare("DELETE FROM performance_metrics_pages");
$q->execute();
$q = db_master()->prepare("DELETE FROM performance_metrics_graphs");
$q->execute();
$q = db_master()->prepare("DELETE FROM performance_metrics_jobs");
$q->execute();
$q = db_master()->prepare("DELETE FROM performance_metrics_queues");
$q->execute();
crypto_log("Deleted old metric data.");
batch_footer();
$data[$key] = 0;
}
}
// calculate MySQL statistics
// must request the master database manually!
$q = db_master()->prepare("SHOW GLOBAL STATUS");
$q->execute();
$mysql_mapping = array('mysql_uptime' => 'Uptime', 'mysql_threads' => 'Threads_running', 'mysql_questions' => 'Questions', 'mysql_slow_queries' => 'Slow_queries', 'mysql_opens' => 'Opened_tables', 'mysql_flush_tables' => 'Flush_commands', 'mysql_open_tables' => 'Open_tables', 'mysql_locks_immediate' => 'Table_locks_immediate', 'mysql_locks_blocked' => 'Table_locks_waited');
while ($s = $q->fetch()) {
if (($pos = array_search($s['Variable_name'], $mysql_mapping)) !== false) {
$data[$pos] = $s['Value'];
}
}
$data['disk_free_space'] = disk_free_space('/');
// get system statistics if defined (i.e. not Windows)
$query_extra = "";
if (function_exists('sys_getloadavg')) {
$top = sys_getloadavg();
$data['system_load_1min'] = $top[0];
$data['system_load_5min'] = $top[1];
$data['system_load_15min'] = $top[2];
$query_extra = "\n system_load_1min = :system_load_1min,\n system_load_5min = :system_load_5min,\n system_load_15min = :system_load_15min,\n ";
}
crypto_log(print_r($data, true));
$q = db()->prepare("UPDATE site_statistics SET is_recent=0 WHERE is_recent=1");
$q->execute();
$q = db()->prepare("INSERT INTO site_statistics SET\n total_users = :total_users,\n disabled_users = :disabled_users,\n premium_users = :premium_users,\n total_emails_sent = :total_emails_sent,\n\n free_delay_minutes = :free_delay_minutes,\n premium_delay_minutes = :premium_delay_minutes,\n outstanding_jobs = :outstanding_jobs,\n external_status_job_count = :external_status_job_count,\n external_status_job_errors = :external_status_job_errors,\n\n mysql_uptime = :mysql_uptime,\n mysql_threads = :mysql_threads,\n mysql_questions = :mysql_questions,\n mysql_slow_queries = :mysql_slow_queries,\n mysql_opens = :mysql_opens,\n mysql_flush_tables = :mysql_flush_tables,\n mysql_open_tables = :mysql_open_tables,\n mysql_locks_immediate = :mysql_locks_immediate,\n mysql_locks_blocked = :mysql_locks_blocked,\n\n users_graphs_managed_none = :users_graphs_managed_none,\n users_graphs_managed_managed = :users_graphs_managed_managed,\n users_graphs_managed_auto = :users_graphs_managed_auto,\n users_graphs_need_update = :users_graphs_need_update,\n users_subscribe_announcements = :users_subscribe_announcements,\n pending_subscriptions = :pending_subscriptions,\n pending_unsubscriptions = :pending_unsubscriptions,\n\n user_logins_after_warned = :user_logins_after_warned,\n users_login_after_warned = :users_login_after_warned,\n user_logins_after_disabled = :user_logins_after_disabled,\n users_login_after_disabled = :users_login_after_disabled,\n\n notifications_sent = :notifications_sent,\n max_notifications_sent = :max_notifications_sent,\n\n jobs_tests = :jobs_tests,\n jobs_timeout = :jobs_timeout,\n\n disk_free_space = :disk_free_space,\n\n {$query_extra}\n\n is_recent=1\n ");
$q->execute($data);
$insert_id = db()->lastInsertId();
crypto_log("Inserted new site statistics ID {$insert_id}");
batch_footer();
<?php
/**
* Individual Crypto-Trade securities job.
*/
$exchange = "individual_crypto-trade";
// get the relevant address
$q = db()->prepare("SELECT * FROM accounts_individual_cryptotrade WHERE user_id=? AND id=?");
$q->execute(array($job['user_id'], $job['arg_id']));
$account = $q->fetch();
if (!$account) {
throw new JobException("Cannot find a {$exchange} account " . $job['arg_id'] . " for user " . $job['user_id']);
}
// get the most recent ticker balance for this security
$q = db()->prepare("SELECT * FROM balances WHERE exchange=? AND account_id=? AND is_recent=1 LIMIT 1");
$q->execute(array('securities_crypto-trade', $account['security_id']));
$ticker = $q->fetch();
if (!$ticker) {
throw new ExternalAPIException("Could not find any recent ticker balance for securities_crypto-trade ID=" . htmlspecialchars($account['security_id']));
} else {
$calculated = $ticker['balance'] * $account['quantity'];
crypto_log('security ' . htmlspecialchars($account['security_id']) . " @ " . htmlspecialchars($ticker['balance']) . " x " . number_format($account['quantity']) . " = " . htmlspecialchars($calculated));
insert_new_balance($job, $account, $exchange, $ticker['currency'], $calculated);
}
<?php
/**
* Crypto-Trade securities ticker job.
*/
$exchange = "securities_crypto-trade";
// get the relevant security
$q = db()->prepare("SELECT * FROM securities_cryptotrade WHERE id=?");
$q->execute(array($job['arg_id']));
$security = $q->fetch();
if (!$security) {
throw new JobException("Cannot find a {$exchange} security " . $job['arg_id'] . " for user " . $job['user_id']);
}
$cur1 = $security['currency'];
$cur2 = strtolower($security['name']);
$rates = crypto_json_decode(crypto_get_contents(crypto_wrap_url("https://crypto-trade.com/api/1/ticker/" . $cur2 . "_" . $cur1)));
if (!isset($rates['data']['max_bid'])) {
if (isset($rates['error'])) {
throw new ExternalAPIException("Could not find {$cur1}/{$cur2} rate for {$exchange}: " . htmlspecialchars($rates['error']));
}
throw new ExternalAPIException("No {$cur1}/{$cur2} rate for {$exchange}");
}
crypto_log("Security {$cur1}/{$cur2} balance: " . $rates['data']['max_bid']);
// insert new balance
insert_new_balance($job, $security, 'securities_crypto-trade', $security['currency'], $rates['data']['max_bid']);
/**
* @param $currency
* @param $value a number
*/
function insert_new_difficulty($job, $currency, $value)
{
$block_table = "difficulty_" . $currency;
// disable old instances
$q = db()->prepare("UPDATE {$block_table} SET is_recent=0 WHERE is_recent=1");
$q->execute();
// we have a balance; update the database
$q = db()->prepare("INSERT INTO {$block_table} SET difficulty=:value,is_recent=1");
$q->execute(array("value" => $value));
crypto_log("Inserted new {$block_table} id=" . db()->lastInsertId());
}
<?php
/**
* Havelock Investments security value job.
* Retrieves the current 'bid' value for a particular security.
*/
$exchange = "securities_havelock";
$currency = 'btc';
// get the relevant address
$q = db()->prepare("SELECT * FROM securities_havelock WHERE id=?");
$q->execute(array($job['arg_id']));
$account = $q->fetch();
if (!$account) {
throw new JobException("Cannot find a {$exchange} account " . $job['arg_id']);
}
require __DIR__ . "/_havelock.php";
$content = havelock_query("https://www.havelockinvestments.com/r/tickerfull", array('symbol' => $account['name']));
crypto_log("Last price for " . htmlspecialchars($account['name']) . ": " . $content[$account['name']]['last']);
$balance = $content[$account['name']]['last'];
insert_new_balance($job, $account, $exchange, $currency, $balance);
* is disabled.
*/
// get the relevant user info
$user = get_user($job['arg_id']);
if (!$user) {
throw new JobException("Cannot find user ID " . $job['arg_id']);
}
// check that they're not a premium user etc - this should never happen
if ($user['is_premium']) {
throw new JobException("Premium user was requested to be warned of disabled - this should not happen");
} else {
if ($user['is_disabled']) {
throw new JobException("Disabled user was requested to be warned of disabled - this should not happen");
}
}
$disables_at = strtotime(($user['last_login'] ? $user['last_login'] : $user['created_at']) . " +" . get_site_config('user_expiry_days') . " day");
// update user (before sending email)
$q = db()->prepare("UPDATE user_properties SET is_disable_warned=1,disable_warned_at=NOW() WHERE id=? LIMIT 1");
$q->execute(array($user['id']));
if ($disables_at > time()) {
// there's no point in sending an email if it's going to be disabled in the past; it will be disabled on our very next run anyway
// construct email
if ($user['email']) {
send_user_email($user, "disable_warning", array("name" => $user['name'] ? $user['name'] : $user['email'], "days" => number_format(get_site_config('user_expiry_days')), "disables" => iso_date($disables_at), "disables_text" => recent_format($disables_at, false, ""), "url" => absolute_url(url_for("user#user_premium")), "login" => absolute_url(url_for("login"))));
crypto_log("Sent disable warning soon e-mail to " . htmlspecialchars($user['email']) . ".");
} else {
crypto_log("User had no valid e-mail address.");
}
} else {
crypto_log("Did not send any disable warning: disable time is set into the past (" . iso_date($disables_at) . ")");
}
<?php
/**
* Securities count job - count how many securities a user currently has. This value is
* displayed on the Profile tabs as Your Securities (X).
*/
// construct a query using
$accounts = account_data_grouped();
$unions = array();
$args = array('user_id' => $job['user_id']);
$count = 0;
foreach ($accounts['Individual Securities'] as $key => $data) {
$unique = "u" . $count++;
$unions[] = "(SELECT :" . $unique . "_name AS exchange, security_id FROM " . $data['table'] . " WHERE user_id=:user_id)\n";
// doesn't directly required an alias
$args[$unique . '_name'] = $data['exchange'];
}
$query = "SELECT COUNT(*) AS c FROM\n\t(SELECT exchange, security_id FROM (\n\t\t(SELECT exchange, security_id FROM securities WHERE user_id=:user_id AND is_recent=1)\n\t\tUNION " . implode(" UNION ", $unions) . "\n\t) u GROUP BY exchange, security_id) t";
crypto_log("<pre>" . $query . "</pre>");
crypto_log(print_r($args, true));
// execute
$q = db()->prepare($query);
$q->execute($args);
$security_count = $q->fetch();
crypto_log("Securities found for user " . $job['user_id'] . ": " . number_format($security_count['c']));
$q = db()->prepare("UPDATE user_properties SET securities_count=? WHERE id=?");
$q->execute(array($security_count['c'], $job['user_id']));
/**
* Implements failing tables; if an account type fails multiple times,
* then send the user an email and disable the account.
* @see OpenclerkJobQueuer#getStandardJobs()
*/
function failed(\Exception $runtime_exception, Connection $db, Logger $logger)
{
// is this a standard job?
$standard = $this->findStandardJob();
if ($standard) {
$logger->info("Using standard job " . print_r($standard, true));
if (!$standard['failure']) {
$logger->info("Not a failure standard job");
return;
}
} else {
return;
}
$failing_table = $standard['table'];
$job = $this->job;
// find the relevant account_data for this standard job
$account_data = false;
foreach (account_data_grouped() as $label => $group) {
foreach ($group as $exchange => $data) {
if (isset($data['job_type']) && $job['job_type'] == $data['job_type']) {
$account_data = $data;
$account_data['exchange'] = $exchange;
break;
}
}
}
if (!$account_data) {
$logger->warn("Could not find any account data for job type '" . $job['job_type'] . "'");
}
$logger->info("Using account data " . print_r($account_data, true));
// don't count CloudFlare as a failure
if ($runtime_exception instanceof CloudFlareException || $runtime_exception instanceof \Openclerk\Apis\CloudFlareException) {
$logger->info("Not increasing failure count: was a CloudFlareException");
} else {
if ($runtime_exception instanceof IncapsulaException || $runtime_exception instanceof \Openclerk\Apis\IncapsulaException) {
$logger->info("Not increasing failure count: was a IncapsulaException");
} else {
if ($runtime_exception instanceof BlockchainException || $runtime_exception instanceof \Core\BlockchainException) {
$logger->info("Not increasing failure count: was a BlockchainException");
} else {
$q = $db->prepare("UPDATE {$failing_table} SET failures=failures+1,first_failure=IF(ISNULL(first_failure), NOW(), first_failure) WHERE id=?");
$q->execute(array($job['arg_id']));
$logger->info("Increasing account failure count");
}
}
}
$user = get_user($job['user_id']);
if (!$user) {
$logger->info("Warning: No user " . $job['user_id'] . " found");
} else {
// failed too many times?
$q = $db->prepare("SELECT * FROM {$failing_table} WHERE id=? LIMIT 1");
$q->execute(array($job['arg_id']));
$account = $q->fetch();
$logger->info("Current account failure count: " . number_format($account['failures']));
if ($account['failures'] >= get_premium_value($user, 'max_failures')) {
// disable it and send an email
$q = $db->prepare("UPDATE {$failing_table} SET is_disabled=1 WHERE id=?");
$q->execute(array($job['arg_id']));
crypto_log(print_r($account_data, true));
if ($user['email'] && !$account['is_disabled']) {
$email_type = $job['job_type'] == "notification" ? "failure_notification" : "failure";
send_user_email($user, $email_type, array("name" => $user['name'] ? $user['name'] : $user['email'], "exchange" => get_exchange_name($account_data['exchange']), "label" => $account_data['label'], "labels" => $account_data['labels'], "failures" => number_format($account['failures']), "message" => $runtime_exception->getMessage(), "length" => recent_format(strtotime($account['first_failure']), "", ""), "title" => isset($account['title']) && $account['title'] ? "\"" . $account['title'] . "\"" : "untitled", "url" => absolute_url(url_for("wizard_accounts"))));
$logger->info("Sent failure e-mail to " . htmlspecialchars($user['email']) . ".");
}
}
}
}
* A batch script to get all current Havelock securities and queue them up for ticker values,
* so we will have historical data even if no user has the security yet.
*/
$exchange = "securities_havelock";
$currency = 'btc';
// get the API data
$content = crypto_get_contents(crypto_wrap_url('https://www.havelockinvestments.com/r/tickerfull'));
if (!$content) {
throw new ExternalAPIException("API returned empty data");
}
$json = json_decode($content, true);
if (!$json) {
throw new ExternalAPIException("JSON was invalid");
}
foreach ($json as $security => $data) {
// $data only has last price, so we'll let securities_havelock job deal with the bid/ask
$q = db()->prepare("SELECT * FROM securities_havelock WHERE name=?");
$q->execute(array($security));
$security_def = $q->fetch();
if (!$security_def) {
// need to insert a new security definition, so we can later get its value
// we can't calculate the value of this security yet
crypto_log("No securities_havelock definition existed for '" . htmlspecialchars($security) . "': adding in new definition");
$q = db()->prepare("INSERT INTO securities_havelock SET name=?");
$q->execute(array($security));
$security_def = array('name' => $security, 'id' => db()->lastInsertId());
}
$balance = $data['last'];
// since we already have last price here, we might as well save it for free
insert_new_balance($job, $security_def, $exchange, $currency, $balance);
}