Wallos: Open-source, self-hostable personal subscription tracker. Visualize your recurring expenses, manage your budget, and save money.
<?php
require_once '../../includes/connect_endpoint.php';
require_once '../../includes/validate_endpoint.php';
require_once '../../includes/exchange_rate_freshness.php';
require_once '../../includes/frankfurter.php';
$shouldUpdate = true;
if (isset($_POST['force']) && $_POST['force'] === "true") {
$shouldUpdate = true;
} else {
// This branch could not run. It built a DateTime out of the SQLite3Result
// rather than out of a value fetched from it, which on PHP 8 is a
// TypeError and a fatal, and it went unnoticed because the interface only
// ever posts force=true, so nothing has reached it.
$shouldUpdate = !wallos_rates_refreshed_today($db, $userId);
if (!$shouldUpdate) {
echo "Rates are current, no need to update.";
exit;
}
}
$query = "SELECT api_key, provider FROM fixer WHERE user_id = :userId";
$stmt = $db->prepare($query);
$stmt->bindParam(':userId', $userId, SQLITE3_INTEGER);
$result = $stmt->execute();
if ($result) {
$row = $result->fetchArray(SQLITE3_ASSOC);
if ($row) {
$apiKey = $row['api_key'];
$provider = $row['provider'];
$codes = "";
$query = "SELECT id, name, symbol, code FROM currencies WHERE user_id = :userId";
$stmt = $db->prepare($query);
$stmt->bindParam(':userId', $userId, SQLITE3_INTEGER);
$result = $stmt->execute();
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$codes .= $row['code'] . ",";
}
$codes = rtrim($codes, ',');
$query = "SELECT u.main_currency, c.code FROM user u LEFT JOIN currencies c ON u.main_currency = c.id WHERE u.id = :userId";
$stmt = $db->prepare($query);
$stmt->bindParam(':userId', $userId, SQLITE3_INTEGER);
$result = $stmt->execute();
$row = $result->fetchArray(SQLITE3_ASSOC);
$mainCurrencyCode = $row['code'];
$mainCurrencyId = $row['main_currency'];
if ((int) $provider === 2) {
// frankfurter.dev publishes the ECB reference rates without an
// account, and prices in any currency it lists, so it is asked in
// the user's own main currency and the conversion below has nothing
// left to do. No key, no header, and https, because there is no
// account behind it to authenticate.
//
// The helper answers in the same {"rates": ...} shape the two
// providers below do, so everything after this branch is unchanged.
$apiData = frankfurter_latest_rates($mainCurrencyCode, $codes);
} elseif ($provider === 1) {
$api_url = "https://api.apilayer.com/fixer/latest?base=EUR&symbols=" . $codes;
$context = stream_context_create([
'http' => [
'method' => 'GET',
'header' => 'apikey: ' . $apiKey,
]
]);
$response = file_get_contents($api_url, false, $context);
$apiData = json_decode($response, true);
// Piggyback on this request to record the monthly quota apilayer
// reports in its response headers (shown on the settings page).
if (isset($http_response_header)) {
$usageLimit = null;
$usageRemaining = null;
foreach ($http_response_header as $header) {
if (stripos($header, 'x-ratelimit-limit-month:') === 0) {
$usageLimit = (int) trim(substr($header, strlen('x-ratelimit-limit-month:')));
} elseif (stripos($header, 'x-ratelimit-remaining-month:') === 0) {
$usageRemaining = (int) trim(substr($header, strlen('x-ratelimit-remaining-month:')));
}
}
if ($usageLimit !== null && $usageRemaining !== null
&& $db->querySingle("SELECT COUNT(*) FROM pragma_table_info('fixer') WHERE name='usage_used'") > 0) {
$usageStmt = $db->prepare("UPDATE fixer SET usage_used = :used, usage_limit = :limit, usage_updated_at = :updatedAt WHERE user_id = :userId");
$usageStmt->bindValue(':used', $usageLimit - $usageRemaining, SQLITE3_INTEGER);
$usageStmt->bindValue(':limit', $usageLimit, SQLITE3_INTEGER);
$usageStmt->bindValue(':updatedAt', date('Y-m-d H:i:s'), SQLITE3_TEXT);
$usageStmt->bindValue(':userId', $userId, SQLITE3_INTEGER);
$usageStmt->execute();
}
}
} else {
$api_url = "http://data.fixer.io/api/latest?access_key=" . $apiKey . "&base=EUR&symbols=" . $codes;
$response = file_get_contents($api_url);
$apiData = json_decode($response, true);
}
if ((int) $provider === 2) {
// The answer already is in the main currency, so there is nothing
// to divide through by. The loop below still writes the main
// currency's own row as 1.0, which is a rule of this application
// rather than a number read out of a response.
$mainCurrencyToEUR = 1.0;
} else {
$mainCurrencyToEUR = $apiData['rates'][$mainCurrencyCode];
}
if ($apiData !== null && isset($apiData['rates'])) {
// The rates and the refresh date are one unit of work: a failure
// halfway through would otherwise leave some rows converted against
// the new base and some against the old one.
$db->exec('BEGIN');
$updateQuery = "UPDATE currencies SET rate = :rate WHERE code = :code AND user_id = :userId";
$updateStmt = $db->prepare($updateQuery);
$updateFailed = false;
foreach ($apiData['rates'] as $currencyCode => $rate) {
if ($currencyCode === $mainCurrencyCode) {
$exchangeRate = 1.0;
} else {
$exchangeRate = $rate / $mainCurrencyToEUR;
}
$updateStmt->bindValue(':rate', $exchangeRate, SQLITE3_TEXT);
$updateStmt->bindValue(':code', $currencyCode, SQLITE3_TEXT);
$updateStmt->bindValue(':userId', $userId, SQLITE3_INTEGER);
$updateResult = $updateStmt->execute();
$updateStmt->reset();
if (!$updateResult) {
echo "Error updating rate for currency: $currencyCode";
$updateFailed = true;
break;
}
}
if ($updateFailed) {
$db->exec('ROLLBACK');
$db->close();
echo "Exchange rates update rolled back.";
} else {
$currentDate = new DateTime();
$formattedDate = $currentDate->format('Y-m-d');
$updateQuery = "UPDATE last_exchange_update SET date = :formattedDate WHERE user_id = :userId";
$updateStmt = $db->prepare($updateQuery);
$updateStmt->bindParam(':formattedDate', $formattedDate, SQLITE3_TEXT);
$updateStmt->bindParam(':userId', $userId, SQLITE3_INTEGER);
$updateResult = $updateStmt->execute();
$db->exec('COMMIT');
$db->close();
// A currency the provider would not price keeps the rate it had, and
// saying which one is the difference between a total somebody can
// trust and one they cannot. Only the Frankfurter path fills this.
$held = isset($apiData['held']) && is_array($apiData['held']) ? $apiData['held'] : [];
echo "Rates updated successfully!" . ($held === []
? ""
: " Not priced, so left unchanged: " . htmlspecialchars(implode(', ', $held)) . ".") . "";
}
} else {
// A refresh that stored nothing must not pass for one that worked.
// This branch had no else at all, so a provider answering with no
// rates ended the request without a word, and the only trace left
// was that the stored rates had not moved.
$failureMessage = "Exchange rates update failed. The currency provider returned no rates.";
if (is_array($apiData) && isset($apiData['message']) && is_string($apiData['message'])) {
// frankfurter.dev explains itself in the body - it answers 422
// with {"message":"invalid currency: XYZ"} and names the one
// code it objected to - and its own wording says more than a
// guess made here would.
$failureMessage .= " " . htmlspecialchars($apiData['message']);
}
echo $failureMessage;
}
} else {
echo "Exchange rates update skipped. No fixer.io api key provided";
$apiKey = null;
}
} else {
echo "Exchange rates update skipped. No fixer.io api key provided";
$apiKey = null;
}