= 2; $can_delete = lookupUserPermission('module_sales') >= 3; // Taking payments and sending statements are accounting's too (billingMoneyCan()) $can_pay = billingMoneyCan('take'); $can_statement = billingMoneyCan('statement'); $mail_ready = !empty($config_smtp_provider); // Client Filter (all clients) $client = ''; if (!$client_url && !empty($_GET['client'])) { $client = intval($_GET['client']); $client_query = "AND invoice_client_id = $client"; } // From a recurring invoice $recurring_filter = intval($_GET['recurring_invoice_id'] ?? 0); $recurring_query = $recurring_filter ? "AND invoice_recurring_invoice_id = $recurring_filter" : ''; $recurring_filter_row = $recurring_filter ? billingRow('recurring', $recurring_filter) : null; // Hardware sold on them that isn't on the client's assets yet (functions/products.php) $hardware_filter = !empty($_GET['hardware']); $hardware_query = $hardware_filter ? "AND " . productHardwareInvoiceSql() . " AND invoices.invoice_id IN (SELECT invoice_items.item_invoice_id FROM invoice_items JOIN products ON products.product_id = invoice_items.item_product_id WHERE " . productHardwareShortSql() . ")" : ''; // Category Filter $category_filter = intval($_GET['category'] ?? 0); $category_query = $category_filter ? "AND invoice_category_id = $category_filter" : ''; $open_sql = "invoice_status IN ('Sent', 'Viewed', 'Partial')"; $statuses = [ '' => ['label' => 'All', 'where' => ''], 'Draft' => ['label' => 'Draft', 'icon' => 'fa-pencil-ruler', 'where' => "invoice_status = 'Draft'", 'title' => 'Not sent yet - the client can\'t see them'], 'Unpaid' => ['label' => 'Unpaid', 'icon' => 'fa-hand-holding-usd', 'where' => $open_sql, 'title' => 'Sent, and not paid in full'], 'Overdue' => ['label' => 'Overdue', 'icon' => 'fa-exclamation-circle', 'where' => "$open_sql AND invoice_due < CURDATE()", 'title' => 'Past their due date and not paid in full'], 'Paid' => ['label' => 'Paid', 'icon' => 'fa-check-circle', 'where' => "invoice_status = 'Paid'"], 'Cancelled' => ['label' => 'Cancelled', 'icon' => 'fa-ban', 'where' => "invoice_status IN ('Cancelled', 'Non-Billable')", 'title' => 'Cancelled, or closed off as non-billable'], ]; $status = isset($statuses[$_GET['status'] ?? '']) ? ($_GET['status'] ?? '') : ''; $status_query = $statuses[$status]['where'] ? 'AND ' . $statuses[$status]['where'] : ''; // Only these sort - filter_header.php hands through any lowercase word $sortable_columns = ['invoice_number' => 'invoice_number', 'invoice_scope' => 'invoice_scope', 'client_name' => 'client_name', 'invoice_date' => 'invoice_date', 'invoice_due' => 'invoice_due', 'invoice_amount' => 'invoice_amount', 'balance' => 'balance', 'invoice_status' => 'invoice_status', 'category_name' => 'category_name']; if (!isset($sortable_columns[$sort])) { $sort = 'invoice_number'; } $paid_join = "LEFT JOIN (SELECT payment_invoice_id, SUM(payment_amount) AS paid FROM payments GROUP BY payment_invoice_id) AS invoice_payments ON payment_invoice_id = invoice_id"; $scope_query = clientScopeSql('invoice_client_id'); $search_query = "AND (CONCAT(invoice_prefix, invoice_number) LIKE '%$q%' OR invoice_scope LIKE '%$q%' OR client_name LIKE '%$q%' OR invoice_status LIKE '%$q%' OR invoice_amount LIKE '%$q%' OR category_name LIKE '%$q%')"; $date_query = "AND DATE(invoice_date) BETWEEN '$dtf' AND '$dtt'"; $filters = "$scope_query $client_query $recurring_query $hardware_query $category_query $date_query $search_query"; $sql = mysqli_query($mysqli, "SELECT SQL_CALC_FOUND_ROWS invoices.*, client_name, category_name, category_color, recurring_invoice_prefix, recurring_invoice_number, COALESCE(invoice_payments.paid, 0) AS paid, invoice_amount - COALESCE(invoice_payments.paid, 0) AS balance FROM invoices LEFT JOIN clients ON invoice_client_id = client_id LEFT JOIN categories ON invoice_category_id = category_id LEFT JOIN recurring_invoices ON invoice_recurring_invoice_id = recurring_invoice_id $paid_join WHERE 1 $filters $status_query ORDER BY {$sortable_columns[$sort]} $order, invoice_id DESC LIMIT $record_from, $record_to"); $num_rows = mysqli_fetch_row(mysqli_query($mysqli, "SELECT FOUND_ROWS()")); $invoices = []; while ($row = mysqli_fetch_assoc($sql)) { $invoices[] = $row; } // How many of each, for the pills - what the filters above leave, before the pill $count_sql = []; foreach ($statuses as $key => $s) { $count_sql[] = $key === '' ? 'COUNT(*)' : "COALESCE(SUM({$s['where']}), 0)"; } $row = mysqli_fetch_row(mysqli_query($mysqli, "SELECT " . implode(', ', $count_sql) . " FROM invoices LEFT JOIN clients ON invoice_client_id = client_id LEFT JOIN categories ON invoice_category_id = category_id WHERE 1 $filters")); $counts = []; foreach (array_keys($statuses) as $i => $key) { $counts[$key] = intval($row[$i]); } // The money along the top - everything in this scope, not narrowed by search, date or pill $money_scope = "$scope_query $client_query $recurring_query"; $money = mysqli_fetch_assoc(mysqli_query($mysqli, "SELECT COALESCE(SUM(CASE WHEN $open_sql AND balance > 0 THEN balance END), 0) AS owed, COALESCE(SUM(CASE WHEN $open_sql AND balance > 0 THEN 1 END), 0) AS owed_count, COALESCE(SUM(CASE WHEN $open_sql AND balance > 0 AND invoice_due < CURDATE() THEN balance END), 0) AS overdue, COALESCE(SUM(CASE WHEN $open_sql AND balance > 0 AND invoice_due < CURDATE() THEN 1 END), 0) AS overdue_count, COALESCE(SUM(CASE WHEN invoice_status = 'Draft' THEN invoice_amount END), 0) AS draft, COALESCE(SUM(CASE WHEN invoice_status = 'Draft' THEN 1 END), 0) AS draft_count FROM (SELECT invoice_status, invoice_due, invoice_amount, invoice_amount - COALESCE(invoice_payments.paid, 0) AS balance FROM invoices $paid_join WHERE 1 $money_scope) AS scoped")); $collected = mysqli_fetch_assoc(mysqli_query($mysqli, "SELECT COALESCE(SUM(payment_amount), 0) AS amount, COUNT(*) AS payments FROM payments JOIN invoices ON payment_invoice_id = invoice_id WHERE payment_date >= CURDATE() - INTERVAL 30 DAY $money_scope")); $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; })); }; // Clients with a card on file, for Charge Saved Card - one query, not one a row $saved_card_clients = []; $sql_saved = mysqli_query($mysqli, "SELECT DISTINCT saved_payment_client_id FROM client_saved_payment_methods JOIN payment_providers ON saved_payment_provider_id = payment_provider_id WHERE payment_provider_active = 1"); while ($row = mysqli_fetch_row($sql_saved)) { $saved_card_clients[intval($row[0])] = true; } $stat_money = function ($amount) use ($session_company_currency) { return escapeHtml(billingMoney($amount, $session_company_currency)); }; ?>
Owed to you
unpaid invoice
Overdue
past due 0 ? ' · ' . round($money['overdue'] / $money['owed'] * 100) . '% of what is owed' : '' ?>
In drafts
not sent yet
Collected, 30 days
payment

Invoices

Export Send Statement Statement PDF 0) { ?>
Batch Payment
Made by ' . escapeHtml(billingNumber('recurring', $recurring_filter_row)) . '' : 'a recurring invoice' ?> Clear
Hardware sold on them isn't on the client's assets yet - open one and use Add to Assets Clear
Invoice Client Date Due Total Balance Status
0) { ?>
paid
- 		0) { ?>
Open Download PDF
Add Payment Charge Saved Card
Send Email... Mark Sent... Edit Copy
Delete
'invoices', 'icon' => 'fa-file-invoice-dollar', 'filtered' => $q || $category_filter || $recurring_filter || $hardware_filter || $dtf !== '1970-01-01', 'views' => ['status'], 'view' => emptyStatePill($statuses, $status, '', ['Unpaid', 'Overdue']), 'text' => 'Bill a client by hand, or from an accepted quote or their unbilled time.', 'actions' => $can_edit ? [['label' => 'New Invoice', 'modal' => "modals/invoice/invoice_add.php?$client_url"]] : [], ]; require_once "../includes/filter_footer.php"; ?>