Payment Providers > Saved Cards - every card a client has saved to
* pay by (in the client portal, or added for them), with its provider: whose
* it is, the card and when it runs out, the recurring invoices that autopay
* with it and when each next does, and when it was saved. The tabs pick out
* the cards on autopay and the ones that have run out - an expired card on
* autopay is flagged, as its next autopay will be declined. Filtered by
* provider (the Payment Providers list links here so) and by client. Remove
* takes a card off at the provider and in ITFlow, its autopay with it
* (paymentCardRemove(), functions/payment_provider.php,
* admin/post/saved_payment_method.php).
*/
// Default Column Sortby Filter
$sort = "client_name";
$order = "ASC";
require_once "includes/inc_all_admin.php";
$expiry_sql = paymentCardExpirySql();
$this_month = intval(date('Ym'));
// Only these sort - an unknown column would take the page down
$sortable_columns = [
'client_name' => "client_name $order, saved_payment_id ASC",
'saved_payment_description' => "saved_payment_description $order, client_name ASC",
'expires' => "expires IS NULL, expires $order, client_name ASC",
'autopay' => "autopay $order, client_name ASC",
'saved_payment_created_at' => "saved_payment_created_at $order",
];
if (!isset($sortable_columns[$sort])) {
$sort = 'client_name';
}
// '' all, 'autopay' the ones recurring invoices pay with, 'expired' the ones that have run out
$show = in_array($_GET['show'] ?? '', ['autopay', 'expired'], true) ? $_GET['show'] : '';
$provider_filter = intval($_GET['provider'] ?? 0);
$client_filter = intval($_GET['client'] ?? 0);
$autopay_sql = "(SELECT COUNT(*) FROM recurring_payments WHERE recurring_payment_saved_payment_id = saved_payment_id)";
$search = "(client_name LIKE '%$q%' OR saved_payment_description LIKE '%$q%' OR payment_provider_client LIKE '%$q%' OR saved_payment_provider_method LIKE '%$q%' OR payment_provider_name LIKE '%$q%')";
$filters = $search . ($provider_filter ? " AND saved_payment_provider_id = $provider_filter" : '') . ($client_filter ? " AND saved_payment_client_id = $client_filter" : '');
$show_where = [
'' => '1 = 1',
'autopay' => "$autopay_sql > 0",
'expired' => "$expiry_sql < $this_month",
][$show];
$from = "FROM client_saved_payment_methods
LEFT JOIN payment_providers ON payment_provider_id = saved_payment_provider_id
LEFT JOIN clients ON client_id = saved_payment_client_id
LEFT JOIN client_payment_provider ON client_payment_provider.client_id = saved_payment_client_id AND client_payment_provider.payment_provider_id = saved_payment_provider_id";
$sql = mysqli_query($mysqli, "SELECT SQL_CALC_FOUND_ROWS saved_payment_id, saved_payment_client_id, saved_payment_provider_id, saved_payment_provider_method, saved_payment_description,
saved_payment_created_at, payment_provider_name, payment_provider_active, client_name, client_archived_at, payment_provider_client,
$expiry_sql AS expires, $autopay_sql AS autopay
$from
WHERE $filters AND $show_where
ORDER BY {$sortable_columns[$sort]}
LIMIT $record_from, $record_to");
$num_rows = mysqli_fetch_row(mysqli_query($mysqli, "SELECT FOUND_ROWS()"));
$rows = [];
while ($row = mysqli_fetch_assoc($sql)) {
$rows[intval($row['saved_payment_id'])] = $row;
}
$autopay = paymentCardAutopay(array_keys($rows));
// How many in each tab, with the search and filters
$counts = mysqli_fetch_row(mysqli_query($mysqli, "SELECT COUNT(*), COALESCE(SUM($autopay_sql > 0), 0), COALESCE(SUM($expiry_sql < $this_month), 0),
COALESCE(SUM($expiry_sql < $this_month AND $autopay_sql > 0), 0)
$from WHERE $filters"));
// The providers and clients to filter by - those with cards
$providers = mysqli_query($mysqli, "SELECT payment_provider_id, payment_provider_name FROM payment_providers
WHERE payment_provider_id IN (SELECT saved_payment_provider_id FROM client_saved_payment_methods) OR payment_provider_id = $provider_filter ORDER BY payment_provider_name ASC");
$clients = mysqli_query($mysqli, "SELECT client_id, client_name FROM clients
WHERE client_id IN (SELECT saved_payment_client_id FROM client_saved_payment_methods) OR client_id = $client_filter ORDER BY client_name ASC");
$provider_count = mysqli_num_rows($providers);
$sort_link = function ($column, $label) use ($url_query_strings_sort, $sort, $disp, $order_icon) {
return ''
. $label . ($sort === $column ? " $order_icon" : '') . '';
};
$pill_url = function ($show) {
return '?' . http_build_query(array_filter(['q' => $_GET['q'] ?? '', 'provider' => $_GET['provider'] ?? '', 'client' => $_GET['client'] ?? '', 'show' => $show]));
};
$csrf = $_SESSION['csrf_token'];
$page_title = 'Saved Cards';
?>
'saved cards',
'icon' => 'fa-wallet',
'text' => 'The cards clients save to pay by - in the client portal, or added for them - show here, with the recurring invoices that autopay with each.',
'filtered' => $q !== '' || $provider_filter || $client_filter || $show !== '',
];
?>