['n' => 0, 'v' => 0.0], 'lost' => ['n' => 0, 'v' => 0.0], 'open' => ['n' => 0, 'v' => 0.0], 'draft' => ['n' => 0, 'v' => 0.0]]; $sql = mysqli_query($mysqli, "SELECT $outcome_sql AS outcome, COUNT(quote_id) AS n, SUM($value_sql) AS v FROM quotes WHERE quote_archived_at IS NULL AND " . reportRangeSql('quote_date', $period) . " $client_sql GROUP BY outcome"); while ($row = mysqli_fetch_assoc($sql)) { $by[$row['outcome']] = ['n' => intval($row['n']), 'v' => floatval($row['v'])]; } $quoted_n = $by['won']['n'] + $by['lost']['n'] + $by['open']['n']; $quoted_v = $by['won']['v'] + $by['lost']['v'] + $by['open']['v']; $win_rate = reportShare($by['won']['n'], $by['won']['n'] + $by['lost']['n']); $win_rate_value = reportShare($by['won']['v'], $by['won']['v'] + $by['lost']['v']); $prev_won = dashboardSum("SELECT SUM($value_sql) FROM quotes WHERE quote_archived_at IS NULL AND quote_status IN ('Accepted', 'Invoiced') AND " . reportRangeSql('quote_date', $period, true) . " $client_sql"); // Open right now, whenever quoted $open = []; $sql = mysqli_query($mysqli, "SELECT quote_id, quote_prefix, quote_number, quote_scope, quote_status, quote_date, quote_expire, quote_amount, quote_monthly_amount, $value_sql AS value, quote_client_id, client_name, client_abbreviation, DATEDIFF(quote_expire, CURDATE()) AS days_left, DATEDIFF(CURDATE(), quote_date) AS age FROM quotes JOIN clients ON quote_client_id = client_id WHERE quote_archived_at IS NULL AND quote_status IN ('Sent', 'Viewed') $client_sql ORDER BY quote_expire IS NULL, quote_expire ASC"); while ($row = mysqli_fetch_assoc($sql)) { $open[] = $row; } $open_live = array_filter($open, function ($q) { return $q['quote_expire'] === null || intval($q['days_left']) >= 0; }); $open_expired = array_filter($open, function ($q) { return $q['quote_expire'] !== null && intval($q['days_left']) < 0; }); $expiring = array_filter($open_live, function ($q) { return $q['quote_expire'] !== null && intval($q['days_left']) <= 14; }); $open_monthly = (bool) array_filter($open, function ($q) { return floatval($q['quote_monthly_amount']) != 0; }); // By client, in the period $clients = []; $sql = mysqli_query($mysqli, "SELECT quote_client_id AS client_id, client_name, client_abbreviation, $outcome_sql AS outcome, COUNT(quote_id) AS n, SUM($value_sql) AS v FROM quotes JOIN clients ON quote_client_id = client_id WHERE quote_archived_at IS NULL AND quote_status <> 'Draft' AND " . reportRangeSql('quote_date', $period) . " $client_sql GROUP BY quote_client_id, client_name, client_abbreviation, outcome"); while ($row = mysqli_fetch_assoc($sql)) { $id = intval($row['client_id']); $clients[$id] = $clients[$id] ?? ['client_id' => $id, 'client' => $row['client_name'], 'abbr' => $row['client_abbreviation'], 'quotes' => 0, 'quoted' => 0.0, 'won_n' => 0, 'won' => 0.0, 'lost_n' => 0, 'lost' => 0.0, 'open' => 0.0]; $clients[$id]['quotes'] += intval($row['n']); $clients[$id]['quoted'] += floatval($row['v']); if ($row['outcome'] === 'won') { $clients[$id]['won_n'] += intval($row['n']); $clients[$id]['won'] += floatval($row['v']); } elseif ($row['outcome'] === 'lost') { $clients[$id]['lost_n'] += intval($row['n']); $clients[$id]['lost'] += floatval($row['v']); } else { $clients[$id]['open'] += floatval($row['v']); } } foreach ($clients as &$row) { $row['rate'] = reportShare($row['won_n'], $row['won_n'] + $row['lost_n']); } unset($row); uasort($clients, function ($a, $b) { return $b['quoted'] <=> $a['quoted']; }); $clients = array_values($clients); $quote_link = function ($row) { return '' . escapeHtml($row['quote_prefix'] . $row['quote_number']) . ''; }; $open_columns = [ 'quote' => ['label' => 'Quote', 'csv' => function ($row) { return $row['quote_prefix'] . $row['quote_number']; }, 'html' => $quote_link], 'client_name' => ['label' => 'Client', 'html' => function ($row) { return reportClientCell($row['quote_client_id'], $row['client_name'], $row['client_abbreviation'], 'quotes.php'); }], 'quote_scope' => ['label' => 'Scope', 'class' => 'small itflow-report-text'], 'quote_status' => ['label' => 'Status', 'html' => function ($row) { return '' . escapeHtml($row['quote_status']) . ''; }], 'quote_date' => ['label' => 'Quoted', 'type' => 'date', 'class' => 'text-nowrap'], 'quote_expire' => ['label' => 'Expires', 'type' => 'date', 'class' => 'text-nowrap', 'html' => function ($row) { if ($row['quote_expire'] === null) { return 'No date'; } $left = intval($row['days_left']); $date = escapeHtml(date('M j, Y', strtotime($row['quote_expire']))); if ($left < 0) { return 'Expired ' . (-$left) . ' day' . ($left === -1 ? '' : 's') . ' ago'; } if ($left <= 14) { return '' . ($left === 0 ? 'Today' : "In $left day" . ($left === 1 ? '' : 's')) . ''; } return $date; }], 'age' => ['label' => 'Waiting', 'type' => 'count', 'csv_label' => 'Days waiting', 'html' => function ($row) { return intval($row['age']) . ' day' . (intval($row['age']) === 1 ? '' : 's'); }], // Up front and monthly are in the CSV; on screen, one column - what it comes to in its first year, broken down under it 'quote_amount' => ['label' => 'Up front', 'type' => 'money', 'screen' => false], 'quote_monthly_amount' => ['label' => 'Monthly', 'type' => 'money', 'screen' => false], 'value' => ['label' => $open_monthly ? 'First year' : 'Amount', 'csv_label' => 'First year', 'type' => 'money', 'class' => 'fw-semibold', 'title' => $open_monthly ? 'Up front, and a year of its recurring lines' : '', 'html' => function ($row) { $html = escapeHtml(reportMoney($row['value'])); if (floatval($row['quote_monthly_amount']) != 0) { $html .= '
' . escapeHtml(reportMoney($row['quote_amount'])) . ' + ' . escapeHtml(reportMoney($row['quote_monthly_amount'])) . '/mo
'; } return $html; }], ]; $client_columns = [ 'client' => ['label' => 'Client', 'html' => function ($row) { return reportClientCell($row['client_id'], $row['client'], $row['abbr'], 'quotes.php'); }], 'quotes' => ['label' => 'Quotes', 'type' => 'count'], 'quoted' => ['label' => 'Quoted', 'type' => 'money'], 'won' => ['label' => 'Won', 'type' => 'money', 'class' => 'text-success-emphasis'], 'lost' => ['label' => 'Lost', 'type' => 'money', 'class' => 'text-danger-emphasis'], 'open' => ['label' => 'Still open', 'type' => 'money', 'class' => 'text-body-secondary'], 'rate' => ['label' => 'Win rate', 'type' => 'percent', 'html' => function ($row) { return reportRateHtml($row['rate'], 60, 35); }], ]; if (reportExporting('clients')) { reportCsv('Quotes by Client ' . $period['start'] . ' to ' . $period['end'], $client_columns, $clients); } if (reportExporting()) { reportCsv('Open Quotes', $open_columns, $open, ['quote' => 'Total', 'quote_amount' => array_sum(array_column($open, 'quote_amount')), 'quote_monthly_amount' => array_sum(array_column($open, 'quote_monthly_amount')), 'value' => array_sum(array_column($open, 'value'))]); } $buckets = reportBuckets($period); $series = []; foreach (['won' => ['Won', 'success'], 'lost' => ['Lost', 'danger'], 'open' => ['Still open', 'info']] as $key => [$label, $tone]) { $series[] = ['label' => $label, 'tone' => $tone, 'stack' => 'q', 'data' => reportSeries("SELECT " . reportBucketSql('quote_date', $buckets['unit']) . " AS b, SUM($value_sql) AS v FROM quotes WHERE quote_archived_at IS NULL AND $outcome_sql = '$key' AND " . reportRangeSql('quote_date', $period) . " $client_sql GROUP BY b", $buckets['keys'])]; } require_once "includes/inc_report_layout.php"; ?>
$period, 'years' => reportPastYears("SELECT DISTINCT YEAR(quote_date) AS y FROM quotes"), 'client' => $client_id, 'export' => ['open' => 'Open quotes', 'clients' => 'By client'], ]); ?> 'Quoted', 'value' => reportMoney($quoted_v), 'sub' => reportCount($quoted_n) . ' quote' . ($quoted_n === 1 ? '' : 's') . ' sent' . ($by['draft']['n'] ? ', ' . $by['draft']['n'] . ' still drafts' : '') . ''], ['label' => 'Won', 'value' => reportMoney($by['won']['v']), 'tone' => $by['won']['v'] > 0 ? 'success' : '', 'sub' => reportChangeHtml($by['won']['v'], $prev_won, 'up', $period['compare'])], ['label' => 'Win rate', 'value' => reportPercent($win_rate), 'sub' => $win_rate_value !== null ? escapeHtml(reportPercent($win_rate_value)) . ' by value, of quotes decided' : 'No quote decided yet'], ['label' => 'Open now', 'value' => reportMoney(array_sum(array_column($open_live, 'value'))), 'url' => '/agent/quotes.php', 'sub' => count($expiring) ? '' . count($expiring) . ' expire within 14 days' : reportCount(count($open_live)) . ' waiting for an answer'], ]) ?>
'bar', 'labels' => $buckets['labels'], 'series' => $series, 'stacked' => true, 'format' => 'money'], '', 'Value quoted over the period, won, lost and still open') ?>
' . count($open_expired) . ' past their expiry date - follow up or close them' : 'Soonest to expire first') ?> reportEmpty('No quote is waiting for an answer.'), 'limit' => 300, 'export_table' => 'open', 'totals' => ['quote' => 'Total', 'value' => array_sum(array_column($open, 'value'))], ]) ?> 'No quotes went out in this period.', 'totals' => ['client' => 'Total', 'quotes' => $quoted_n, 'quoted' => $quoted_v, 'won' => $by['won']['v'], 'lost' => $by['lost']['v'], 'open' => $by['open']['v'], 'rate' => $win_rate], 'totals_html' => ['rate' => reportRateHtml($win_rate, 60, 35)], ]) ?>
By quote date. Won is accepted or invoiced, lost is declined, open is sent or viewed and not yet answered. The win rate counts only quotes that have been decided.