Skip to content

Wallos: Open-source, self-hostable personal subscription tracker. Visualize your recurring expenses, manage your budget, and save money.

9.1 KB
Raw Blame
<?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;
}