Wallos: Open-source, self-hostable personal subscription tracker. Visualize your recurring expenses, manage your budget, and save money.
<?php
require_once __DIR__ . '/budget_period_calculations.php';
require_once __DIR__ . '/currency_rates.php';
require_once __DIR__ . '/default_names.php';
require_once __DIR__ . '/subscription_payers.php';
require_once __DIR__ . '/subscription_categories.php';
function getPricePerMonth($cycle, $frequency, $price)
{
switch ($cycle) {
case 1:
$numberOfPaymentsPerMonth = (30 / $frequency);
return $price * $numberOfPaymentsPerMonth;
case 2:
$numberOfPaymentsPerMonth = (4.35 / $frequency);
return $price * $numberOfPaymentsPerMonth;
case 3:
$numberOfPaymentsPerMonth = (1 / $frequency);
return $price * $numberOfPaymentsPerMonth;
case 4:
$numberOfMonths = (12 * $frequency);
return $price / $numberOfMonths;
case 5:
return 0;
}
}
function getPriceConverted($price, $currency, $database, $userId)
{
return wallos_convert_price($price, $currency, $database, $userId);
}
// Get categories
$categories = array();
$query = "SELECT * FROM categories WHERE user_id = :userId ORDER BY `order` ASC";
$stmt = $db->prepare($query);
$stmt->bindValue(':userId', $userId, SQLITE3_INTEGER);
$result = $stmt->execute();
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$categoryId = $row['id'];
$categories[$categoryId] = $row;
$categories[$categoryId]['count'] = 0;
$categoryCost[$categoryId]['cost'] = 0;
$categoryCost[$categoryId]['name'] = $row['name'];
}
// Get payment methods
$paymentMethods = array();
$query = "SELECT * FROM payment_methods WHERE user_id = :userId AND enabled = 1";
$stmt = $db->prepare($query);
$stmt->bindValue(':userId', $userId, SQLITE3_INTEGER);
$result = $stmt->execute();
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$paymentMethodId = $row['id'];
$paymentMethods[$paymentMethodId] = $row;
$paymentMethods[$paymentMethodId]['count'] = 0;
$paymentMethodsCount[$paymentMethodId]['count'] = 0;
$paymentMethodsCount[$paymentMethodId]['name'] = $row['name'];
}
//Get household members
$members = array();
$query = "SELECT * FROM household WHERE user_id = :userId";
$stmt = $db->prepare($query);
$stmt->bindValue(':userId', $userId, SQLITE3_INTEGER);
$result = $stmt->execute();
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$memberId = $row['id'];
$members[$memberId] = $row;
$members[$memberId]['count'] = 0;
$memberCost[$memberId]['cost'] = 0;
$memberCost[$memberId]['name'] = $row['name'];
}
// Unfiltered counts for filter menu display (so non-selected items remain visible when a filter is active)
$stmt = $db->prepare("SELECT sc.category_id AS category_id, COUNT(*) as cnt FROM subscription_categories sc JOIN subscriptions s ON s.id = sc.subscription_id WHERE s.user_id = :userId GROUP BY sc.category_id");
$stmt->bindValue(':userId', $userId, SQLITE3_INTEGER);
$r = $stmt->execute();
$menuCategoryCounts = [];
while ($row = $r->fetchArray(SQLITE3_ASSOC)) {
$menuCategoryCounts[$row['category_id']] = $row['cnt'];
}
$stmt = $db->prepare("SELECT sp.payer_id AS payer_user_id, COUNT(*) as cnt FROM subscription_payers sp JOIN subscriptions s ON s.id = sp.subscription_id WHERE s.user_id = :userId GROUP BY sp.payer_id");
$stmt->bindValue(':userId', $userId, SQLITE3_INTEGER);
$r = $stmt->execute();
$menuMemberCounts = [];
while ($row = $r->fetchArray(SQLITE3_ASSOC)) {
$menuMemberCounts[$row['payer_user_id']] = $row['cnt'];
}
$stmt = $db->prepare("SELECT payment_method_id, COUNT(*) as cnt FROM subscriptions WHERE user_id = :userId AND inactive = 0 GROUP BY payment_method_id");
$stmt->bindValue(':userId', $userId, SQLITE3_INTEGER);
$r = $stmt->execute();
$menuPaymentCounts = [];
while ($row = $r->fetchArray(SQLITE3_ASSOC)) {
$menuPaymentCounts[$row['payment_method_id']] = $row['cnt'];
}
$activeSubscriptions = 0;
$inactiveSubscriptions = 0;
// Calculate total monthly price
$mostExpensiveSubscription = array();
$mostExpensiveSubscription['price'] = 0;
$amountDueThisMonth = 0;
$totalCostPerMonth = 0;
$totalSavingsPerMonth = 0;
$totalCostsInReplacementsPerMonth = 0;
$statsSubtitleParts = [];
$query = "SELECT id, name, price, logo, logo_text_color, logo_variant, frequency, cycle, currency_id, next_payment, payer_user_id, category_id, payment_method_id, inactive, replacement_subscription_id, start_date, auto_renew FROM subscriptions";
$conditions = [];
$params = [];
$filteredMemberIds = [];
$filteredCategoryIds = [];
if (isset($_GET['member']) && $_GET['member'] !== '') {
$memberIds = array_map('intval', explode(',', $_GET['member']));
$filteredMemberIds = $memberIds;
$conditions[] = payerFilterCondition($memberIds, $params);
foreach ($memberIds as $mid) {
if (isset($members[$mid])) {
$statsSubtitleParts[] = $members[$mid]['name'];
}
}
}
if (isset($_GET['category']) && $_GET['category'] !== '') {
$categoryIds = array_map('intval', explode(',', $_GET['category']));
$filteredCategoryIds = $categoryIds;
$conditions[] = categoryFilterCondition($categoryIds, $params);
// A category still carrying its untouched default name is recognised
// against the account's own creation-language default, not a literal
// English string - #1216 gives every language its own default, so an
// account created in German never had an English name to match here.
$noCategoryDefaultName = default_no_category_name($userData['language'] ?? 'en');
foreach ($categoryIds as $cid) {
if (isset($categories[$cid])) {
$statsSubtitleParts[] = $categories[$cid]['name'] === $noCategoryDefaultName ? translate("no_category", $i18n) : $categories[$cid]['name'];
}
}
}
if (isset($_GET['payment']) && $_GET['payment'] !== '') {
$paymentIds = array_map('intval', explode(',', $_GET['payment']));
$conditions[] = "payment_method_id IN (" . implode(',', $paymentIds) . ")";
foreach ($paymentIds as $pid) {
if (isset($paymentMethodsCount[$pid])) {
$statsSubtitleParts[] = $paymentMethodsCount[$pid]['name'];
}
}
}
$conditions[] = "user_id = :userId";
$params[':userId'] = $userId;
if (!empty($conditions)) {
$query .= " WHERE " . implode(' AND ', $conditions);
}
$stmt = $db->prepare($query);
$statsSubtitle = !empty($statsSubtitleParts) ? '(' . implode(', ', $statsSubtitleParts) . ')' : "";
foreach ($params as $key => $value) {
$stmt->bindValue($key, $value, SQLITE3_INTEGER);
}
$result = $stmt->execute();
$usesMultipleCurrencies = false;
$subscriptions = [];
if ($result) {
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$subscriptions[] = $row;
}
if (isset($subscriptions)) {
$replacementSubscriptions = array();
$payersBySubscription = getPayersBySubscription($db, $subscriptions);
$categoriesBySubscription = getCategoriesBySubscription($db, $subscriptions);
foreach ($subscriptions as $subscription) {
$name = $subscription['name'];
$price = $subscription['price'];
$logo = $subscription['logo'];
$frequency = $subscription['frequency'];
$cycle = $subscription['cycle'];
$currency = $subscription['currency_id'];
if ($currency != $userData['main_currency']) {
$usesMultipleCurrencies = true;
}
$next_payment = $subscription['next_payment'];
$subscriptionPayerIds = $payersBySubscription[$subscription['id']] ?? [];
foreach ($subscriptionPayerIds as $payerId) {
if (isset($members[$payerId])) {
$members[$payerId]['count'] += 1;
}
}
$subscriptionCategoryIds = $categoriesBySubscription[$subscription['id']] ?? [];
foreach ($subscriptionCategoryIds as $categoryId) {
if (isset($categories[$categoryId])) {
$categories[$categoryId]['count'] += 1;
}
}
$paymentMethodId = $subscription['payment_method_id'];
if (isset($paymentMethods[$paymentMethodId])) {
$paymentMethods[$paymentMethodId]['count'] += 1;
}
$inactive = $subscription['inactive'];
$replacementSubscriptionId = $subscription['replacement_subscription_id'];
$originalSubscriptionPrice = getPriceConverted($price, $currency, $db, $userId);
$fullPrice = getPricePerMonth($cycle, $frequency, $originalSubscriptionPrice);
// Filtering by member counts only that member's share of a shared
// subscription, the same share the per-member breakdown uses.
$memberShare = 1;
if (!empty($filteredMemberIds) && count($subscriptionPayerIds) > 0) {
$memberShare = count(array_intersect($subscriptionPayerIds, $filteredMemberIds)) / count($subscriptionPayerIds);
}
// The same goes for a subscription in several categories
if (!empty($filteredCategoryIds) && count($subscriptionCategoryIds) > 0) {
$memberShare *= count(array_intersect($subscriptionCategoryIds, $filteredCategoryIds)) / count($subscriptionCategoryIds);
}
$originalSubscriptionPrice *= $memberShare;
$price = $fullPrice * $memberShare;
if ($inactive == 0) {
if ($cycle != 5) {
$activeSubscriptions++;
if (isset($paymentMethodsCount[$paymentMethodId])) {
$paymentMethodsCount[$paymentMethodId]['count'] += 1;
}
}
$totalCostPerMonth += $price;
// The price is split evenly between the people paying for it
$payerShare = count($subscriptionPayerIds) > 0 ? $fullPrice / count($subscriptionPayerIds) : 0;
foreach ($subscriptionPayerIds as $payerId) {
if (isset($memberCost[$payerId])) {
$memberCost[$payerId]['cost'] += $payerShare;
}
}
// The price is split evenly between its categories too, so the
// category costs add up to the total instead of exceeding it
$categoryShare = count($subscriptionCategoryIds) > 0 ? $fullPrice / count($subscriptionCategoryIds) : 0;
foreach ($subscriptionCategoryIds as $categoryId) {
if (isset($categoryCost[$categoryId])) {
$categoryCost[$categoryId]['cost'] += $categoryShare;
}
}
if ($price > $mostExpensiveSubscription['price']) {
$mostExpensiveSubscription['price'] = $price;
$mostExpensiveSubscription['name'] = $name;
$mostExpensiveSubscription['logo'] = $logo;
$mostExpensiveSubscription['logo_text_color'] = $subscription['logo_text_color'] ?? null;
$mostExpensiveSubscription['logo_variant'] = $subscription['logo_variant'] ?? null;
}
if ($cycle != 5) {
// Calculate ammount due this month
$nextPaymentDate = DateTime::createFromFormat('Y-m-d', trim($next_payment));
$todayVal = new DateTime('today');
$endOfMonth = new DateTime('last day of this month');
if ($nextPaymentDate >= $todayVal && $nextPaymentDate <= $endOfMonth) {
$timesToPay = 1;
$daysInMonth = $endOfMonth->diff($todayVal)->days + 1;
$daysRemaining = $endOfMonth->diff($nextPaymentDate)->days + 1;
if ($cycle == 1) {
$timesToPay = $daysRemaining / $frequency;
}
if ($cycle == 2) {
$weeksInMonth = ceil($daysInMonth / 7);
$weeksRemaining = ceil($daysRemaining / 7);
$timesToPay = $weeksRemaining / $frequency;
}
$amountDueThisMonth += $originalSubscriptionPrice * $timesToPay;
}
}
} else {
$inactiveSubscriptions++;
$totalSavingsPerMonth += $price;
// Check if it has a replacement subscription and if it was not already counted
if ($replacementSubscriptionId && !in_array($replacementSubscriptionId, $replacementSubscriptions)) {
$query = "SELECT price, currency_id, cycle, frequency FROM subscriptions WHERE id = :replacementSubscriptionId AND user_id = :userId";
$stmt = $db->prepare($query);
$stmt->bindValue(':replacementSubscriptionId', $replacementSubscriptionId, SQLITE3_INTEGER);
$stmt->bindValue(':userId', $userId, SQLITE3_INTEGER);
$result = $stmt->execute();
$replacementSubscription = $result->fetchArray(SQLITE3_ASSOC);
if ($replacementSubscription) {
$replacementSubscriptionPrice = getPriceConverted($replacementSubscription['price'], $replacementSubscription['currency_id'], $db, $userId);
$replacementSubscriptionPrice = getPricePerMonth($replacementSubscription['cycle'], $replacementSubscription['frequency'], $replacementSubscriptionPrice);
$totalCostsInReplacementsPerMonth += $replacementSubscriptionPrice;
}
}
$replacementSubscriptions[] = $replacementSubscriptionId;
}
}
// Subtract the total cost of replacement subscriptions from the total savings
$totalSavingsPerMonth -= $totalCostsInReplacementsPerMonth;
// Calculate yearly price
$totalCostPerYear = $totalCostPerMonth * 12;
// Calculate average subscription monthly cost
if ($activeSubscriptions > 0) {
$averageSubscriptionCost = $totalCostPerMonth / $activeSubscriptions;
} else {
$totalCostPerYear = 0;
$averageSubscriptionCost = 0;
}
} else {
$totalCostPerYear = 0;
$averageSubscriptionCost = 0;
}
}
$today = new DateTime('now');
$budgetPeriodType = sanitizeBudgetPeriodType($userData['budget_period_type'] ?? 'monthly');
$budgetPeriodAnchorDate = sanitizeBudgetAnchorDate($userData['budget_period_anchor_date'] ?? getDefaultBudgetAnchorDate());
$activeBudgetPeriod = getActiveBudgetPeriod($today, $budgetPeriodType, $budgetPeriodAnchorDate);
$budgetPeriodStart = $activeBudgetPeriod['start'];
$budgetPeriodEnd = $activeBudgetPeriod['end'];
$budgetPeriodLabel = $activeBudgetPeriod['label'];
// A monthly period whose anchor lands on the calendar month's boundaries is
// identical to the plain monthly budget, so there's nothing distinct to show.
$calendarMonthStart = new DateTime($today->format('Y-m-01'));
$calendarMonthEnd = new DateTime($today->format('Y-m-t'));
$periodDiffersFromCalendarMonth = $budgetPeriodStart->format('Y-m-d') !== $calendarMonthStart->format('Y-m-d')
|| $budgetPeriodEnd->format('Y-m-d') !== $calendarMonthEnd->format('Y-m-d');
$amountNeededThisPeriod = computeAmountNeededInPeriod($subscriptions ?? [], $today, $budgetPeriodEnd, $db, $userId);
$showVsMonthlyBudgetGraph = false;
$vsMonthlyBudgetDataPoints = [];
if (isset($userData['budget']) && $userData['budget'] > 0) {
$monthlyBudget = $userData['budget'];
$monthlyBudgetLeft = max(0, $monthlyBudget - $totalCostPerMonth);
$monthlyBudgetUsed = min(100, ($totalCostPerMonth / $monthlyBudget) * 100);
if ($totalCostPerMonth > $monthlyBudget) {
$monthlyOverBudgetAmount = $totalCostPerMonth - $monthlyBudget;
}
$showVsMonthlyBudgetGraph = true;
$vsMonthlyBudgetDataPoints = [
[
"label" => translate('budget_remaining', $i18n),
"y" => $monthlyBudgetLeft,
],
[
"label" => translate('monthly_cost', $i18n),
"y" => $totalCostPerMonth,
],
];
// Backwards compatibility for pages still referencing legacy monthly budget variables.
$budget = $monthlyBudget;
$budgetLeft = $monthlyBudgetLeft;
$budgetUsed = $monthlyBudgetUsed;
$overBudgetAmount = $monthlyOverBudgetAmount ?? 0;
}
$showVsPeriodBudgetGraph = false;
$vsPeriodBudgetDataPoints = [];
if ($periodDiffersFromCalendarMonth && isset($userData['period_budget']) && $userData['period_budget'] > 0) {
$periodBudget = $userData['period_budget'];
$periodBudgetLeft = max(0, $periodBudget - $amountNeededThisPeriod);
$periodBudgetUsed = min(100, ($amountNeededThisPeriod / $periodBudget) * 100);
if ($amountNeededThisPeriod > $periodBudget) {
$periodOverBudgetAmount = $amountNeededThisPeriod - $periodBudget;
}
$showVsPeriodBudgetGraph = true;
$vsPeriodBudgetDataPoints = [
[
"label" => translate('budget_remaining', $i18n),
"y" => $periodBudgetLeft,
],
[
"label" => translate('amount_needed_this_period', $i18n),
"y" => $amountNeededThisPeriod,
],
];
}
// Backwards compatibility for pages still referencing legacy graph variables.
$showVsBudgetGraph = $showVsMonthlyBudgetGraph;
$vsBudgetDataPoints = $vsMonthlyBudgetDataPoints;
$showCantConverErrorMessage = false;
if ($usesMultipleCurrencies) {
$query = "SELECT api_key FROM fixer WHERE user_id = :userId";
$stmt = $db->prepare($query);
$stmt->bindValue(':userId', $userId, SQLITE3_INTEGER);
$result = $stmt->execute();
if ($result->fetchArray(SQLITE3_ASSOC) === false) {
$showCantConverErrorMessage = true;
}
}
$query = "SELECT * FROM total_yearly_cost WHERE user_id = :userId";
$stmt = $db->prepare($query);
$stmt->bindValue(':userId', $userId, SQLITE3_INTEGER);
$result = $stmt->execute();
$totalMonthlyCostDataPoints = [];
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$totalMonthlyCostDataPoints[] = [
"label" => html_entity_decode($row['date']),
"y" => round($row['cost'] / 12, 2),
];
}
$showTotalMonthlyCostGraph = count($totalMonthlyCostDataPoints) > 1;
?>