Wallos: Open-source, self-hostable personal subscription tracker. Visualize your recurring expenses, manage your budget, and save money.
<?php
/*
This API Endpoint accepts both POST and GET requests.
It receives the following parameters:
- member: comma-separated IDs of the members to filter (integer) default null.
- category: comma-separated IDs of the categories to filter (integer) default null.
- payment: comma-separated IDs of the payment methods to filter (integer) default null.
- state: the state of the subscription to filter (integer) default null [0 - active, 1 - inactive].
- disabled_to_bottom: whether to sort the inactive subscriptions to the bottom (string) default false. Must be the literal string "true" to take effect.
- sort: the sorting method (string) default next_payment ['name', 'id', 'next_payment', 'price', 'payer_user_id', 'category_id', 'payment_method_id', 'inactive', 'alphanumeric'].
- convert_currency: whether to convert to the main currency (string) default false. Must be the literal string "true" to take effect.
- all-user-subscription: whether to return subscriptions for every user instead of just the caller (string) default null. Only honored when api_key belongs to user id 1; adds the "users" field to the response.
- api_key: the API key of the user.
It returns a JSON object with the following properties:
- success: whether the request was successful (boolean).
- title: the title of the response (string).
- subscriptions: an array of subscriptions.
- users: an array of all users, only present when all-user-subscription is used by user id 1.
- notes: reserved for warning messages or additional information (array); currently always empty.
Example response:
{
"success": true,
"title": "subscriptions",
"subscriptions": [
{
"id": 1,
"name": "Example Subscription",
"logo": "example.png",
"price": 10.00,
"currency_id": 1,
"start_date": "2024-09-01",
"next_payment": "2024-09-01",
"cycle": 1,
"frequency": 1,
"auto_renew": 1,
"notes": "Example note",
"payment_method_id": 1,
"payer_user_id": 1,
"category_id": 1,
"notify": 1,
"url": "https://example.com",
"inactive": 0,
"notify_days_before": 1,
"user_id": 1,
"cancelation_date": null,
"cancellation_date": "",
"category_name": "General",
"category_ids": [1, 2],
"category_names": ["General", "Entertainment"],
"payer_user_name": "John Doe",
"payer_user_ids": [1, 2],
"payer_user_names": ["John Doe", "Jane Doe"],
"payment_method_name": "PayPal"
},
{
"id": 2,
"name": "Another Subscription",
"logo": "another.png",
"price": 15.00,
"currency_id": 2,
"start_date": "2024-09-02",
"next_payment": "2024-09-02",
"cycle": 1,
"frequency": 1,
"auto_renew": 0,
"notes": "",
"payment_method_id": 2,
"payer_user_id": 2,
"category_id": 2,
"notify": 0,
"url": "",
"inactive": 1,
"notify_days_before": null,
"user_id": 2,
"cancelation_date": null,
"cancellation_date": "",
"category_name": "Entertainment",
"payer_user_name": "Jane Doe",
"payment_method_name": "Credit Card",
"replacement_subscription_id": 1
}
],
"users": [
{
"id": 1,
"name": "admin",
"email": "[email protected]"
},
{
"id": 2,
"name": "user",
"email": "[email protected]"
}
],
"notes": []
}
*/
require_once '../../includes/connect_endpoint.php';
require_once '../../includes/currency_rates.php';
require_once '../../includes/subscription_payers.php';
require_once '../../includes/subscription_categories.php';
header('Content-Type: application/json; charset=UTF-8');
if ($_SERVER["REQUEST_METHOD"] === "POST" || $_SERVER["REQUEST_METHOD"] === "GET") {
// if the parameters are not set, return an error
$apiKey = $_REQUEST['api_key'] ?? $_REQUEST['apiKey'] ?? null;
if (!$apiKey) {
$response = [
"success" => false,
"title" => "Missing parameters"
];
echo json_encode($response);
exit;
}
function getPriceConverted($price, $currency, $database)
{
return wallos_convert_price($price, $currency, $database);
}
// Get user from API key
$sql = "SELECT * FROM user WHERE api_key = :apiKey";
$stmt = $db->prepare($sql);
$stmt->bindValue(':apiKey', $apiKey);
$result = $stmt->execute();
$user = $result->fetchArray(SQLITE3_ASSOC);
// If the user is not found, return an error
if (!$user) {
$response = [
"success" => false,
"title" => "Invalid API key"
];
echo json_encode($response);
exit;
}
$userId = $user['id'];
$userCurrencyId = $user['main_currency'];
$allUserSubscription = isset($_REQUEST['all-user-subscription']) ? $_REQUEST['all-user-subscription'] : null;
if ($allUserSubscription == 1 && $userId != 1) {
$response = [
"success" => false,
"title" => "Denied. Not admin user"
];
echo json_encode($response);
exit;
}
// Get last exchange update date for user
$sql = "SELECT * FROM last_exchange_update WHERE user_id = :userId";
$stmt = $db->prepare($sql);
$stmt->bindValue(':userId', $userId);
$result = $stmt->execute();
$lastExchangeUpdate = $result->fetchArray(SQLITE3_ASSOC);
$canConvertCurrency = empty($lastExchangeUpdate['date']) ? false : true;
// Get currencies for user
$sql = "SELECT * FROM currencies WHERE user_id = :userId";
$stmt = $db->prepare($sql);
$stmt->bindValue(':userId', $userId);
$result = $stmt->execute();
$currencies = [];
while ($currency = $result->fetchArray(SQLITE3_ASSOC)) {
$currencies[$currency['id']] = $currency;
}
// Get categories for user
$sql = "SELECT * FROM categories WHERE user_id = :userId";
$stmt = $db->prepare($sql);
$stmt->bindValue(':userId', $userId);
$result = $stmt->execute();
$categories = [];
while ($category = $result->fetchArray(SQLITE3_ASSOC)) {
$categories[$category['id']] = $category['name'];
}
// Get members for user
$sql = "SELECT * FROM household WHERE user_id = :userId";
$stmt = $db->prepare($sql);
$stmt->bindValue(':userId', $userId);
$result = $stmt->execute();
$members = [];
while ($member = $result->fetchArray(SQLITE3_ASSOC)) {
$members[$member['id']] = $member['name'];
}
// Get payment methods for user
$sql = "SELECT * FROM payment_methods WHERE user_id = :userId";
$stmt = $db->prepare($sql);
$stmt->bindValue(':userId', $userId);
$result = $stmt->execute();
$paymentMethods = [];
while ($paymentMethod = $result->fetchArray(SQLITE3_ASSOC)) {
$paymentMethods[$paymentMethod['id']] = $paymentMethod['name'];
}
$sort = "next_payment";
if (isset($_REQUEST['sort'])) {
$sort = $_REQUEST['sort'];
}
$sortOrder = $sort;
$allowedSortCriteria = ['name', 'id', 'next_payment', 'price', 'payer_user_id', 'category_id', 'payment_method_id', 'inactive', 'alphanumeric'];
$order = ($sort == "price" || $sort == "id") ? "DESC" : "ASC";
if ($sort == "alphanumeric") {
$sort = "name";
}
if (!in_array($sort, $allowedSortCriteria)) {
$sort = "next_payment";
}
// Construction of the main SQL Query
$params = [];
if ($allUserSubscription == 1 && $userId == 1) {
$sql = "SELECT * FROM subscriptions WHERE 1 = 1";
} else {
$sql = "SELECT * FROM subscriptions WHERE user_id = :userId";
$params[':userId'] = $userId;
}
if (isset($_REQUEST['member'])) {
$memberIds = explode(',', $_REQUEST['member']);
$sql .= " AND " . payerFilterCondition($memberIds, $params);
}
if (isset($_REQUEST['category'])) {
$categoryIds = explode(',', $_REQUEST['category']);
$sql .= " AND " . categoryFilterCondition($categoryIds, $params);
}
if (isset($_REQUEST['payment'])) {
$paymentIds = explode(',', $_REQUEST['payment']);
$placeholders = array_map(function ($key) {
return ":payment{$key}";
}, array_keys($paymentIds));
$sql .= " AND payment_method_id IN (" . implode(',', $placeholders) . ")";
foreach ($paymentIds as $key => $paymentId) {
$params[":payment{$key}"] = $paymentId;
}
}
if (isset($_REQUEST['state']) && $_REQUEST['state'] != "") {
$sql .= " AND inactive = :inactive";
$params[':inactive'] = $_REQUEST['state'];
}
$orderByClauses = [];
if (isset($_REQUEST['disabled_to_bottom']) && $_REQUEST['disabled_to_bottom'] === 'true') {
if (in_array($sort, ["payer_user_id", "category_id", "payment_method_id"])) {
$orderByClauses[] = "$sort $order";
$orderByClauses[] = "inactive ASC";
} else {
$orderByClauses[] = "inactive ASC";
$orderByClauses[] = "$sort $order";
}
} else {
$orderByClauses[] = "$sort $order";
if ($sort != "inactive") {
$orderByClauses[] = "inactive ASC";
}
}
if ($sort != "next_payment") {
$orderByClauses[] = "next_payment ASC";
}
$sql .= " ORDER BY " . implode(", ", $orderByClauses);
$stmt = $db->prepare($sql);
if ($stmt === false) {
$response = [
"success" => false,
"title" => "Invalid query parameters"
];
echo json_encode($response);
exit;
}
if (!empty($params)) {
foreach ($params as $key => $value) {
$stmt->bindValue($key, $value, SQLITE3_INTEGER);
}
}
$result = $stmt->execute();
if ($result) {
$subscriptions = array();
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$subscriptions[] = $row;
}
}
$subscriptionsToReturn = array();
$payersBySubscription = getPayersBySubscription($db, $subscriptions);
$categoriesBySubscription = getCategoriesBySubscription($db, $subscriptions);
foreach ($subscriptions as $subscription) {
$subscriptionToReturn = $subscription;
if (isset($_REQUEST['convert_currency']) && $_REQUEST['convert_currency'] === 'true' && $canConvertCurrency && $subscription['currency_id'] != $userCurrencyId) {
$subscriptionToReturn['price'] = getPriceConverted($subscription['price'], $subscription['currency_id'], $db);
} else {
$subscriptionToReturn['price'] = $subscription['price'];
}
$categoryIdsForSubscription = $categoriesBySubscription[$subscription['id']] ?? [];
$subscriptionToReturn['category_ids'] = $categoryIdsForSubscription;
$subscriptionToReturn['category_names'] = array_map(fn($categoryId) => $categories[$categoryId] ?? 'No category', $categoryIdsForSubscription);
$subscriptionToReturn['category_name'] = isset($categories[$subscription['category_id']]) ? $categories[$subscription['category_id']] : 'No category';
$payerIdsForSubscription = $payersBySubscription[$subscription['id']] ?? [];
$subscriptionToReturn['payer_user_ids'] = $payerIdsForSubscription;
$subscriptionToReturn['payer_user_names'] = array_map(fn($payerId) => $members[$payerId] ?? 'Unknown member', $payerIdsForSubscription);
$subscriptionToReturn['payer_user_name'] = isset($members[$subscription['payer_user_id']]) ? $members[$subscription['payer_user_id']] : 'Unknown member';
$subscriptionToReturn['payment_method_name'] = isset($paymentMethods[$subscription['payment_method_id']]) ? $paymentMethods[$subscription['payment_method_id']] : 'Unknown payment method';
$subscriptionsToReturn[] = $subscriptionToReturn;
}
$response = [
"success" => true,
"title" => "subscriptions",
"subscriptions" => $subscriptionsToReturn,
"notes" => []
];
if ($allUserSubscription == 1 && $userId == 1) {
$sql = "PRAGMA table_info(user)";
$stmt = $db->prepare($sql);
$result = $stmt->execute();
$userColumns = array();
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$userColumns[] = $row['name'];
}
$userNameCol = in_array('username', $userColumns) ? 'username' : null;
$userEmailCol = in_array('email', $userColumns) ? 'email' : null;
if ($userNameCol && $userEmailCol) {
$sql = "SELECT id, $userNameCol as name, $userEmailCol as email FROM user";
} elseif ($userNameCol) {
$sql = "SELECT id, $userNameCol as name FROM user";
} elseif ($userEmailCol) {
$sql = "SELECT id, $userEmailCol as email FROM user";
} else {
$sql = "SELECT id FROM user";
}
$stmt = $db->prepare($sql);
$result = $stmt->execute();
$users = array();
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$users[] = $row;
}
$response['users'] = $users;
}
echo json_encode($response);
$db->close();
exit;
} else {
$response = [
"success" => false,
"title" => "Invalid request method"
];
echo json_encode($response);
exit;
}