0 AND $range GROUP BY expense_vendor_id, vendor_name, vendor_client_id ORDER BY v DESC"); while ($row = mysqli_fetch_assoc($sql)) { $vendors[intval($row['vendor_id'])] = [ 'vendor_id' => intval($row['vendor_id']), 'vendor' => $row['vendor_name'] ?? 'Deleted vendor', 'exists' => $row['vendor_name'] !== null, 'count' => intval($row['n']), 'total' => floatval($row['v']), 'last' => $row['last_paid'], 'prev' => 0.0, 'category' => '', 'recurring' => 0.0, ]; } $sql = mysqli_query($mysqli, "SELECT expense_vendor_id AS vendor_id, SUM(expense_amount) AS v FROM expenses WHERE expense_vendor_id > 0 AND $prev_range GROUP BY expense_vendor_id"); $prev_total = 0.0; while ($row = mysqli_fetch_assoc($sql)) { $prev_total += floatval($row['v']); if (isset($vendors[intval($row['vendor_id'])])) { $vendors[intval($row['vendor_id'])]['prev'] = floatval($row['v']); } } // The category most of each vendor's money went under $sql = mysqli_query($mysqli, "SELECT expense_vendor_id AS vendor_id, category_name, SUM(expense_amount) AS v FROM expenses LEFT JOIN categories ON expense_category_id = category_id WHERE expense_vendor_id > 0 AND $range GROUP BY expense_vendor_id, category_name ORDER BY v DESC"); while ($row = mysqli_fetch_assoc($sql)) { $id = intval($row['vendor_id']); if (isset($vendors[$id]) && $vendors[$id]['category'] === '') { $vendors[$id]['category'] = $row['category_name'] ?? ''; } } // What each is owed every month from now on $sql = mysqli_query($mysqli, "SELECT recurring_expense_vendor_id AS vendor_id, SUM(" . expenseRecurringMonthlySql() . ") AS v FROM recurring_expenses WHERE " . recurringExpenseRunningSql() . " GROUP BY recurring_expense_vendor_id"); while ($row = mysqli_fetch_assoc($sql)) { if (isset($vendors[intval($row['vendor_id'])])) { $vendors[intval($row['vendor_id'])]['recurring'] = floatval($row['v']); } } $total = array_sum(array_column($vendors, 'total')); $count_1099 = count(array_filter($vendors, function ($v) { return $v['total'] >= 600; })); if ($only_1099) { $vendors = array_filter($vendors, function ($v) { return $v['total'] >= 600; }); } foreach ($vendors as &$vendor) { $vendor['share'] = reportShare($vendor['total'], $total, 1); $vendor['change'] = reportChange($vendor['total'], $vendor['prev']); } unset($vendor); $columns = [ 'vendor' => ['label' => 'Vendor', 'html' => function ($row) { $name = escapeHtml($row['vendor']); return $row['exists'] ? '' . $name . '' : '' . $name . ''; }], 'category' => ['label' => 'Mostly', 'class' => 'small text-body-secondary'], 'count' => ['label' => 'Expenses', 'type' => 'count'], 'total' => ['label' => 'Paid', 'type' => 'money', 'class' => 'fw-semibold', 'html' => function ($row) use ($period) { return '' . reportMoney($row['total']) . ''; }], 'share' => ['label' => 'Share', 'type' => 'percent', 'html' => function ($row) { return '

' . '' . reportPercent($row['share']) . '

'; }], 'prev' => ['label' => 'Period before', 'type' => 'money', 'class' => 'text-body-secondary'], 'change' => ['label' => 'Change', 'type' => 'percent', 'csv' => false, 'html' => function ($row) { return $row['change'] === null ? 'New' : '' . reportChangeHtml($row['total'], $row['prev'], 'down') . ''; }], 'recurring' => ['label' => 'Recurring / month', 'type' => 'money', 'title' => 'Active recurring expenses to this vendor, per month', 'html' => function ($row) { return $row['recurring'] > 0 ? reportMoney($row['recurring']) : '–'; }], 'last' => ['label' => 'Last paid', 'type' => 'date', 'class' => 'text-nowrap'], ]; if (reportExporting()) { reportCsv('Spend by Vendor ' . $period['start'] . ' to ' . $period['end'], $columns, array_values($vendors), ['vendor' => 'Total', 'total' => $only_1099 ? array_sum(array_column($vendors, 'total')) : $total]); } require_once "includes/inc_report_layout.php"; $top = array_slice(array_values($vendors), 0, 12); $biggest = $top[0] ?? null; ?>

$period, 'years' => reportPastYears("SELECT DISTINCT YEAR(expense_date) AS y FROM expenses WHERE expense_vendor_id > 0"), 'controls' => reportSwitchControl('over600', $only_1099, 'Only vendors paid 600 or more'), ]); ?> 'Paid to vendors', 'value' => reportMoney($total), 'sub' => reportChangeHtml($total, $prev_total, 'down', $period['compare']), 'url' => '/agent/expenses.php?canned_date=custom&dtf=' . $period['start'] . '&dtt=' . $period['end']], ['label' => 'Vendors paid', 'value' => reportCount(count($vendors)), 'sub' => '' . ($only_1099 ? 'Paid 600 or more' : 'In the period') . ''], ['label' => 'Biggest', 'value' => $biggest ? $biggest['vendor'] : '-', 'sub' => $biggest ? reportMoney($biggest['total']) . ' · ' . reportPercent($biggest['share']) . ' of spending' : ''], ['label' => 'Paid 600 or more', 'value' => reportCount($count_1099), 'sub' => 'Vendors to check for a 1099', 'url' => reportUrl(['over600' => 1])], ]) ?>

'bar', 'horizontal' => true, 'labels' => array_map(function ($v) { return $v['vendor']; }, $top), 'series' => [['label' => 'Paid', 'data' => array_map(function ($v) { return round($v['total'], 2); }, $top), 'tone' => 'accent']], 'format' => 'money', 'legend' => false, ], '', 'What was paid to each of the biggest vendors') ?>

'No vendor was paid in this period.', 'totals' => ['vendor' => 'Total', 'count' => array_sum(array_column($vendors, 'count')), 'total' => array_sum(array_column($vendors, 'total')), 'prev' => array_sum(array_column($vendors, 'prev')), 'recurring' => array_sum(array_column($vendors, 'recurring'))], ]) ?>

Expenses with a vendor, by the date they were paid. "Period before" is . A 1099 is filed per calendar year - pick This year, Last year or a year above, and check with your accountant which vendors need one.