Files
itflow/agent/recurring_invoices.php

324 lines
22 KiB
PHP

<?php
/*
* Recurring Invoices - one client's agreements, or every one the agent can
* see.
*
* Along the top, the recurring revenue: what the running ones bring in a
* month (quarterly ones counted as a third, yearly as a twelfth), what bills over the next 30
* days, how many are running, and how many need looking at - running, but
* with a next date that has passed, so they won't bill until it moves on.
* Pills: Active (the default - ?status=active), Next 7 days, Late, Paused
* (?status=inactive, as before) and All.
*/
// Default Column Sortby/Order Filter
$sort = "recurring_invoice_next_date";
$order = "ASC";
// If client_id is in URI then show client Side Bar and client header
if (isset($_GET['client_id'])) {
require_once "includes/inc_all_client.php";
$client_query = "AND recurring_invoice_client_id = $client_id";
$client_url = "client_id=$client_id&";
} else {
require_once "includes/inc_all.php";
$client_query = '';
$client_url = '';
}
// Perms
enforceUserPermission('module_sales');
$can_edit = lookupUserPermission('module_sales') >= 2;
$can_delete = lookupUserPermission('module_sales') >= 3;
// Client Filter (all clients)
$client = '';
if (!$client_url && !empty($_GET['client'])) {
$client = intval($_GET['client']);
$client_query = "AND recurring_invoice_client_id = $client";
}
// Category Filter
$category_filter = intval($_GET['category'] ?? 0);
$category_query = $category_filter ? "AND recurring_invoice_category_id = $category_filter" : '';
$statuses = [
'active' => ['label' => 'Active', 'icon' => 'fa-play', 'where' => 'recurring_invoice_status = 1'],
'soon' => ['label' => 'Next 7 days', 'icon' => 'fa-calendar-day', 'where' => 'recurring_invoice_status = 1 AND recurring_invoice_next_date BETWEEN CURDATE() AND CURDATE() + INTERVAL 7 DAY',
'title' => 'Running, and billing in the next week'],
'late' => ['label' => 'Late', 'icon' => 'fa-exclamation-triangle', 'where' => 'recurring_invoice_status = 1 AND recurring_invoice_next_date < CURDATE()',
'title' => "Running, but the next date has passed - they won't bill until it moves on"],
'inactive' => ['label' => 'Paused', 'icon' => 'fa-pause', 'where' => 'recurring_invoice_status = 0'],
'all' => ['label' => 'All', 'where' => ''],
];
$status = isset($statuses[$_GET['status'] ?? '']) ? ($_GET['status'] ?? '') : 'active';
$status_query = $statuses[$status]['where'] ? 'AND ' . $statuses[$status]['where'] : '';
$sortable_columns = ['recurring_invoice_number' => 'recurring_invoice_number', 'recurring_invoice_scope' => 'recurring_invoice_scope', 'client_name' => 'client_name',
'recurring_invoice_frequency' => 'recurring_invoice_frequency', 'recurring_invoice_amount' => 'recurring_invoice_amount', 'monthly' => 'monthly',
'recurring_invoice_next_date' => 'recurring_invoice_next_date', 'recurring_invoice_last_sent' => 'recurring_invoice_last_sent', 'category_name' => 'category_name'];
if (!isset($sortable_columns[$sort])) {
$sort = 'recurring_invoice_next_date';
}
$monthly_sql = recurringInvoiceMonthlySql();
$scope_query = clientScopeSql('recurring_invoice_client_id');
$search_query = "AND (CONCAT(recurring_invoice_prefix, recurring_invoice_number) LIKE '%$q%' OR recurring_invoice_frequency LIKE '%$q%' OR recurring_invoice_scope LIKE '%$q%' OR client_name LIKE '%$q%' OR category_name LIKE '%$q%')";
$filters = "AND recurring_invoice_archived_at IS NULL $scope_query $client_query $category_query $search_query";
$joins = "LEFT JOIN clients ON recurring_invoice_client_id = client_id LEFT JOIN categories ON recurring_invoice_category_id = category_id";
// The contract each bills, when one is linked (functions/contract_billing.php)
$contract_sql = contractBillingReady() ? "(SELECT contract_name FROM contracts WHERE contract_recurring_invoice_id = recurring_invoice_id AND contract_archived_at IS NULL ORDER BY contract_id DESC LIMIT 1)" : 'NULL';
$sql = mysqli_query($mysqli, "SELECT SQL_CALC_FOUND_ROWS recurring_invoices.*, client_name, category_name, recurring_payment_id, $monthly_sql AS monthly, $contract_sql AS contract_name
FROM recurring_invoices $joins
LEFT JOIN recurring_payments ON recurring_payment_recurring_invoice_id = recurring_invoice_id
WHERE 1 $filters $status_query
ORDER BY {$sortable_columns[$sort]} $order, recurring_invoice_id ASC LIMIT $record_from, $record_to");
$num_rows = mysqli_fetch_row(mysqli_query($mysqli, "SELECT FOUND_ROWS()"));
$recurring_invoices = [];
while ($row = mysqli_fetch_assoc($sql)) {
$recurring_invoices[] = $row;
}
$count_sql = [];
foreach ($statuses as $key => $s) {
$count_sql[] = $s['where'] === '' ? 'COUNT(*)' : "COALESCE(SUM({$s['where']}), 0)";
}
$row = mysqli_fetch_row(mysqli_query($mysqli, "SELECT " . implode(', ', $count_sql) . " FROM recurring_invoices $joins WHERE 1 $filters"));
$counts = [];
foreach (array_keys($statuses) as $i => $key) {
$counts[$key] = intval($row[$i]);
}
// The recurring revenue along the top - everything in this scope
$revenue = mysqli_fetch_assoc(mysqli_query($mysqli, "SELECT
COALESCE(SUM(CASE WHEN recurring_invoice_status = 1 THEN $monthly_sql END), 0) AS mrr,
COALESCE(SUM(CASE WHEN recurring_invoice_status = 1 AND recurring_invoice_next_date BETWEEN CURDATE() AND CURDATE() + INTERVAL 30 DAY THEN recurring_invoice_amount END), 0) AS next30,
COALESCE(SUM(CASE WHEN recurring_invoice_status = 1 AND recurring_invoice_next_date BETWEEN CURDATE() AND CURDATE() + INTERVAL 30 DAY THEN 1 END), 0) AS next30_count,
COALESCE(SUM(recurring_invoice_status = 1), 0) AS active_count,
COALESCE(SUM(recurring_invoice_status = 0), 0) AS paused_count,
COALESCE(SUM(recurring_invoice_status = 1 AND recurring_invoice_next_date < CURDATE()), 0) AS late_count
FROM recurring_invoices WHERE recurring_invoice_archived_at IS NULL $scope_query $client_query"));
$filter_url = function ($changes) {
$params = array_merge($_GET, $changes);
unset($params['page']);
return '?' . http_build_query(array_filter($params, function ($v) { return $v !== '' && $v !== null; }));
};
$stat_money = function ($amount) use ($session_company_currency) {
return escapeHtml(billingMoney($amount, $session_company_currency));
};
?>
<!-- The recurring revenue -->
<div class="row g-3 mb-3">
<div class="col-sm-6 col-xl-3">
<a class="itflow-billing-stat" href="<?= escapeHtml($filter_url(['status' => 'active'])) ?>">
<div class="itflow-billing-stat-label"><i class="fas fa-sync-alt me-1"></i>Monthly recurring</div>
<div class="itflow-billing-stat-value text-success-emphasis"><?= $stat_money($revenue['mrr']) ?></div>
<div class="itflow-billing-stat-note"><?= $stat_money($revenue['mrr'] * 12) ?> a year</div>
</a>
</div>
<div class="col-sm-6 col-xl-3">
<div class="itflow-billing-stat">
<div class="itflow-billing-stat-label"><i class="fas fa-calendar-alt me-1"></i>Bills in 30 days</div>
<div class="itflow-billing-stat-value"><?= $stat_money($revenue['next30']) ?></div>
<div class="itflow-billing-stat-note"><?= intval($revenue['next30_count']) ?> invoice<?= intval($revenue['next30_count']) === 1 ? '' : 's' ?> to be made</div>
</div>
</div>
<div class="col-sm-6 col-xl-3">
<a class="itflow-billing-stat <?= $status === 'active' ? 'active' : '' ?>" href="<?= escapeHtml($filter_url(['status' => 'active'])) ?>">
<div class="itflow-billing-stat-label"><i class="fas fa-play me-1"></i>Running</div>
<div class="itflow-billing-stat-value"><?= intval($revenue['active_count']) ?></div>
<div class="itflow-billing-stat-note"><?= intval($revenue['paused_count']) ?> paused</div>
</a>
</div>
<div class="col-sm-6 col-xl-3">
<a class="itflow-billing-stat <?= $status === 'late' ? 'active' : '' ?>" href="<?= escapeHtml($filter_url(['status' => 'late'])) ?>">
<div class="itflow-billing-stat-label"><i class="fas fa-exclamation-triangle me-1"></i>Need a look</div>
<div class="itflow-billing-stat-value <?= $revenue['late_count'] ? 'text-danger-emphasis' : '' ?>"><?= intval($revenue['late_count']) ?></div>
<div class="itflow-billing-stat-note"><?= $revenue['late_count'] ? "Next date passed - won't bill" : 'All on schedule' ?></div>
</a>
</div>
</div>
<div class="card">
<div class="card-header bg-dark py-2">
<h3 class="card-title mt-2"><i class="fa fa-fw fa-redo-alt me-2"></i>Recurring Invoices</h3>
<div class="card-tools">
<div class="btn-group">
<?php if ($can_edit) { ?>
<button type="button" class="btn btn-primary ajax-modal" data-modal-url="modals/recurring_invoice/recurring_invoice_add.php?<?= $client_url ?>"><i class="fas fa-plus me-2"></i>New Recurring Invoice</button>
<?php } ?>
<?php if ($num_rows[0] > 0) { ?>
<button type="button" class="btn btn-primary dropdown-toggle <?= $can_edit ? 'dropdown-toggle-split' : '' ?>" data-bs-toggle="dropdown"><?= $can_edit ? '' : '<i class="fas fa-ellipsis-h"></i>' ?></button>
<div class="dropdown-menu dropdown-menu-end">
<a class="dropdown-item ajax-modal" href="#" data-modal-url="<?= buildExportModalUrl('modals/recurring_invoice/recurring_invoice_export.php', ['client_id', 'category', 'q'], ['status' => in_array($status, ['active', 'inactive'], true) ? $status : '', 'client_id' => $client ?: '']) ?>">
<i class="fa fa-fw fa-download text-secondary me-2"></i>Export
</a>
</div>
<?php } ?>
</div>
</div>
</div>
<div class="card-header py-3">
<form autocomplete="off">
<?php if ($client_url) { ?>
<input type="hidden" name="client_id" value="<?= $client_id ?>">
<?php } ?>
<input type="hidden" name="status" value="<?= escapeHtml($status) ?>">
<div class="row g-2 align-items-center">
<div class="<?= $client_url ? 'col-lg-8' : 'col-lg-6' ?> col-md-6">
<div class="input-group">
<input type="search" class="form-control" name="q" value="<?php if (isset($q)) { echo stripslashes(escapeHtml($q)); } ?>" placeholder="Search number, scope, client, category...">
<button class="btn btn-dark" title="Search"><i class="fa fa-search"></i></button>
</div>
</div>
<div class="<?= $client_url ? 'col-lg-4' : 'col-lg-3' ?> col-md-6">
<select class="form-select select2" name="category" onchange="this.form.submit()" aria-label="Category">
<option value="">- Any category -</option>
<?= categoryFilterOptionsHtml('Income', $category_filter, "SELECT DISTINCT recurring_invoice_category_id FROM recurring_invoices WHERE recurring_invoice_archived_at IS NULL") ?>
</select>
</div>
<?php if (!$client_url) { ?>
<div class="col-lg-3 col-md-6">
<select class="form-select select2" name="client" onchange="this.form.submit()">
<option value="">- All Clients -</option>
<?php
$sql_clients_filter = mysqli_query($mysqli, "SELECT DISTINCT client_id, client_name, client_abbreviation FROM clients
JOIN recurring_invoices ON recurring_invoice_client_id = client_id WHERE 1 " . clientScopeSql('clients.client_id') . " ORDER BY client_name ASC");
while ($row = mysqli_fetch_assoc($sql_clients_filter)) {
$client_option_id = intval($row['client_id']); ?>
<option <?= $client === $client_option_id ? 'selected' : '' ?> value="<?= $client_option_id ?>"<?= selectOptionClient($row['client_id'], $row['client_name'], $row['client_abbreviation'] ?? '') ?>><?= escapeHtml($row['client_name']) ?></option>
<?php } ?>
</select>
</div>
<?php } ?>
</div>
</form>
<nav class="nav nav-pills itflow-asset-tabs mt-2 flex-nowrap overflow-auto">
<?php foreach ($statuses as $key => $s) {
$alarm = $key === 'late' && $counts[$key]; ?>
<a class="nav-link text-nowrap <?= $status === $key ? 'active' : '' ?>" href="<?= escapeHtml($filter_url(['status' => $key])) ?>"<?= !empty($s['title']) ? ' title="' . escapeHtml($s['title']) . '"' : '' ?>>
<?php if (!empty($s['icon'])) { ?><i class="fas fa-fw <?= $s['icon'] ?> me-1"></i><?php } ?><?= $s['label'] ?>
<span class="badge rounded-pill ms-1 <?= $alarm ? 'text-bg-danger' : '' ?>"><?= $counts[$key] ?></span>
</a>
<?php } ?>
</nav>
</div>
<div class="table-responsive">
<table class="table table-hover align-middle mb-0 itflow-billing-table">
<thead class="<?= $num_rows[0] ? '' : 'd-none' ?> text-nowrap table-light">
<tr>
<th class="ps-3"><a class="text-reset text-decoration-none" href="?<?= $url_query_strings_sort ?>&sort=recurring_invoice_number&order=<?= $disp ?>">Recurring invoice <?php if ($sort == 'recurring_invoice_number') { echo $order_icon; } ?></a></th>
<?php if (!$client_url) { ?>
<th><a class="text-reset text-decoration-none" href="?<?= $url_query_strings_sort ?>&sort=client_name&order=<?= $disp ?>">Client <?php if ($sort == 'client_name') { echo $order_icon; } ?></a></th>
<?php } ?>
<th class="text-end"><a class="text-reset text-decoration-none" href="?<?= $url_query_strings_sort ?>&sort=recurring_invoice_amount&order=<?= $disp ?>">Amount <?php if ($sort == 'recurring_invoice_amount') { echo $order_icon; } ?></a></th>
<th class="text-end"><a class="text-reset text-decoration-none" href="?<?= $url_query_strings_sort ?>&sort=monthly&order=<?= $disp ?>">A month <?php if ($sort == 'monthly') { echo $order_icon; } ?></a></th>
<th><a class="text-reset text-decoration-none" href="?<?= $url_query_strings_sort ?>&sort=recurring_invoice_next_date&order=<?= $disp ?>">Next <?php if ($sort == 'recurring_invoice_next_date') { echo $order_icon; } ?></a></th>
<th><a class="text-reset text-decoration-none" href="?<?= $url_query_strings_sort ?>&sort=recurring_invoice_last_sent&order=<?= $disp ?>">Last <?php if ($sort == 'recurring_invoice_last_sent') { echo $order_icon; } ?></a></th>
<th></th>
</tr>
</thead>
<tbody>
<?php foreach ($recurring_invoices as $row) {
$recurring_invoice_id = intval($row['recurring_invoice_id']);
$row_client_id = intval($row['recurring_invoice_client_id']);
$row_number = billingNumber('recurring', $row);
$row_active = intval($row['recurring_invoice_status']) === 1;
$row_currency = (string) $row['recurring_invoice_currency_code'];
$row_url = "recurring_invoice.php?{$client_url}recurring_invoice_id=$recurring_invoice_id";
$row_frequency = recurringInvoiceFrequencies()[$row['recurring_invoice_frequency']] ?? ucfirst((string) $row['recurring_invoice_frequency']);
?>
<tr class="<?= $row_active ? '' : 'text-body-secondary' ?>">
<td class="ps-3 itflow-billing-number">
<a class="fw-semibold" href="<?= $row_url ?>"><?= escapeHtml($row_number) ?></a>
<?php if (intval($row['recurring_payment_id'])) { ?><i class="fas fa-wallet text-success small ms-1" title="Autopay - charged to a card on file"></i><?php } ?>
<?php $row_seats = recurringSeatLines($recurring_invoice_id, $row_client_id);
if ($row_seats) {
$row_drift = count(array_filter($row_seats, function ($line) { return $line['seat_drift']; })); ?>
<i class="fas fa-users small ms-1 <?= $row_drift ? 'text-warning' : 'text-primary' ?>" title="<?= $row_drift ? 'Seats changed - the next invoice bills the new counts' : 'Bills per user or per device - counted for each invoice' ?>"></i>
<?php } ?>
<div class="small text-body-secondary text-truncate" style="max-width: 24rem;"><?php if ($row['contract_name'] !== null) { ?><span class="me-1" title="It starts and stops with the contract" data-recurring-contract><i class="fas fa-file-contract me-1"></i><?= escapeHtml($row['contract_name']) ?> &middot;</span><?php } ?><?= escapeHtml(implode(' · ', array_filter([$row['recurring_invoice_scope'], $row['category_name']]))) ?: '&nbsp;' ?></div>
</td>
<?php if (!$client_url) { ?>
<td><a class="text-reset" href="recurring_invoices.php?client_id=<?= $row_client_id ?>"><?= escapeHtml($row['client_name']) ?></a></td>
<?php } ?>
<td class="text-end itflow-money">
<span class="fw-semibold"><?= escapeHtml(billingMoney($row['recurring_invoice_amount'], $row_currency)) ?></span>
<div class="small text-body-secondary"><?= escapeHtml($row_frequency) ?></div>
</td>
<td class="text-end itflow-money"><?= escapeHtml(billingMoney($row['monthly'], $row_currency)) ?></td>
<td class="text-nowrap">
<?= $row_active ? escapeHtml($row['recurring_invoice_next_date']) : '' ?>
<div><?= recurringInvoiceNextPill($row) ?></div>
</td>
<td class="text-nowrap small"><?= $row['recurring_invoice_last_sent'] ? escapeHtml($row['recurring_invoice_last_sent']) : '<span class="text-body-secondary">Not yet</span>' ?></td>
<td class="text-end">
<div class="dropdown dropstart">
<button class="btn btn-sm btn-light" title="More actions" type="button" data-bs-toggle="dropdown"><i class="fas fa-fw fa-ellipsis-v"></i></button>
<div class="dropdown-menu">
<a class="dropdown-item" href="<?= $row_url ?>"><i class="fas fa-fw fa-eye text-secondary me-2"></i>Open</a>
<a class="dropdown-item" href="invoices.php?client_id=<?= $row_client_id ?>&recurring_invoice_id=<?= $recurring_invoice_id ?>"><i class="fas fa-fw fa-file-invoice-dollar text-secondary me-2"></i>Invoices Made</a>
<?php if ($can_edit) { ?>
<div class="dropdown-divider"></div>
<?php if ($row_active && $row['recurring_invoice_next_date'] < date('Y-m-d')) { ?>
<a class="dropdown-item confirm-link" href="post.php?recurring_invoice_catch_up=<?= $recurring_invoice_id ?>&csrf_token=<?= $_SESSION['csrf_token'] ?>"
data-confirm-title="Skip the missed runs?" data-confirm-text="The next date moves on to <?= escapeHtml(recurringInvoiceCatchUp($row)) ?>. Nothing is billed for the runs it missed." data-confirm-button="Move on">
<i class="fas fa-fw fa-forward text-secondary me-2"></i>Move On to <?= escapeHtml(recurringInvoiceCatchUp($row)) ?>
</a>
<?php } ?>
<?php if ($row_active) { ?>
<a class="dropdown-item confirm-link" href="post.php?recurring_invoice_set_status=<?= $recurring_invoice_id ?>&status=0&csrf_token=<?= $_SESSION['csrf_token'] ?>"
data-confirm-title="Pause <?= escapeHtml($row_number) ?>?" data-confirm-text="No invoices are made from it until it is resumed." data-confirm-button="Pause">
<i class="fas fa-fw fa-pause text-secondary me-2"></i>Pause
</a>
<?php } else { ?>
<a class="dropdown-item" href="post.php?recurring_invoice_set_status=<?= $recurring_invoice_id ?>&status=1&csrf_token=<?= $_SESSION['csrf_token'] ?>">
<i class="fas fa-fw fa-play text-secondary me-2"></i>Resume
</a>
<?php } ?>
<a class="dropdown-item ajax-modal" href="#" data-modal-url="modals/recurring_invoice/recurring_invoice_edit.php?id=<?= $recurring_invoice_id ?>">
<i class="fas fa-fw fa-edit text-secondary me-2"></i>Edit
</a>
<?php } ?>
<?php if ($can_delete) { ?>
<div class="dropdown-divider"></div>
<a class="dropdown-item text-danger text-bold confirm-link" href="post.php?delete_recurring_invoice=<?= $recurring_invoice_id ?>&csrf_token=<?= $_SESSION['csrf_token'] ?>"
data-confirm-text="<?= escapeHtml($row_number) ?> goes for good, with its lines, autopay and history. The invoices it made stay.">
<i class="fas fa-fw fa-trash me-2"></i>Delete
</a>
<?php } ?>
</div>
</div>
</td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
<?php
$empty_state = [
'thing' => 'recurring invoices',
'icon' => 'fa-redo-alt',
'filtered' => (bool) $q,
'views' => ['status'],
'view' => emptyStatePill($statuses, $status, 'active', ['late']),
'text' => 'Monthly agreements and subscriptions - each bills itself when it comes round.',
'actions' => $can_edit ? [['label' => 'New Recurring Invoice', 'modal' => "modals/recurring_invoice/recurring_invoice_add.php?$client_url"]] : [],
];
require_once "../includes/filter_footer.php";
?>
</div>
<?php require_once "../includes/footer.php";