Skip to content

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

8.9 KB
Raw Blame
<?php
require_once 'validate.php';
require_once __DIR__ . '/../../includes/connect_endpoint_crontabs.php';
require_once __DIR__ . '/../../includes/exchange_rate_freshness.php';
require_once __DIR__ . '/../../includes/frankfurter.php';

require 'settimezone.php';

// Get all user ids

if (php_sapi_name() == 'cli') {
    $date = new DateTime('now');
    echo "\n" . $date->format('Y-m-d') . " " . $date->format('H:i:s') . "<br />\n";
}

$query = "SELECT id, username FROM user";
$stmt = $db->prepare($query);
$usersToUpdateExchange = $stmt->execute();

while ($userToUpdateExchange = $usersToUpdateExchange->fetchArray(SQLITE3_ASSOC)) {
    $userId = $userToUpdateExchange['id'];
    echo "For user: " . $userToUpdateExchange['username'] . "<br />";

    // Asked before anything is read or fetched. This job also runs on every
    // container start, so without this a deploy costs one provider request per
    // account, and a free plan's monthly allowance goes on refreshing rates
    // that were already current.
    if (wallos_rates_refreshed_today($db, $userId)) {
        echo "Rates are already current today.<br />";
        continue;
    }

    $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);
            } 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'])) {
                // One user's rates and their 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');

                // Every user has their own currency rows, converted against their own
                // main currency, so the write must be scoped to the user being refreshed.
                $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 <br />";
                        $updateFailed = true;
                        break;
                    }
                }

                if ($updateFailed) {
                    $db->exec('ROLLBACK');
                    echo "Exchange rates update rolled back for this user.<br />";
                } else {
                    $currentDate = new DateTime();
                    $formattedDate = $currentDate->format('Y-m-d');

                    $deleteQuery = "DELETE FROM last_exchange_update WHERE user_id = :userId";
                    $deleteStmt = $db->prepare($deleteQuery);
                    $deleteStmt->bindParam(':userId', $userId, SQLITE3_INTEGER);
                    $deleteResult = $deleteStmt->execute();

                    $query = "INSERT INTO last_exchange_update (date, user_id) VALUES (:formattedDate, :userId)";
                    $stmt = $db->prepare($query);
                    $stmt->bindParam(':formattedDate', $formattedDate, SQLITE3_TEXT);
                    $stmt->bindParam(':userId', $userId, SQLITE3_INTEGER);
                    $result = $stmt->execute();

                    $db->exec('COMMIT');

                    // 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)) . ".") . "<br />";
                }
            } 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 moved on to the next user 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 . "<br />";
            }
        } else {
            echo "Exchange rates update skipped. No fixer.io api key provided<br />";
            $apiKey = null;
        }
    } else {
        echo "Exchange rates update skipped. No fixer.io api key provided<br />";
        $apiKey = null;
    }
}
$db->close();

?>