added on local at 2026-07-01 15:03:00
| 1 | #!/usr/bin/perl | |
| 2 | #====================================================================== | |
| 3 | # ContactForge - Reports: a handful of summary charts rendered as | |
| 4 | # inline SVG / HTML bars. No chart library. | |
| 5 | # | |
| 6 | # Reports: | |
| 7 | # (a) Deals won/lost by month, last 6 months | |
| 8 | # (b) Pipeline value by stage (default pipeline) | |
| 9 | # (c) Activities per user, last 30 days | |
| 10 | # (d) Conversion rate by lead source | |
| 11 | # (e) Average deal size by owner | |
| 12 | #====================================================================== | |
| 13 | use strict; | |
| 14 | use warnings; | |
| 15 | ||
| 16 | use lib '/var/www/vhosts/3dshawn.com/crm.3dshawn.com'; | |
| 17 | use CGI; | |
| 18 | use MODS::Template; | |
| 19 | use MODS::DBConnect; | |
| 20 | use MODS::Login; | |
| 21 | use MODS::ContactForge::Config; | |
| 22 | use MODS::ContactForge::Wrapper; | |
| 23 | ||
| 24 | my $q = CGI->new; | |
| 25 | my $auth = MODS::Login->new; | |
| 26 | my $wrap = MODS::ContactForge::Wrapper->new; | |
| 27 | my $db = MODS::DBConnect->new; | |
| 28 | my $cfg = MODS::ContactForge::Config->new; | |
| 29 | my $DB = $cfg->settings('database_name'); | |
| 30 | ||
| 31 | $|=1; | |
| 32 | my $userinfo = $auth->login_verify(); | |
| 33 | if (!$userinfo) { print "Status: 302 Found\nLocation: /login.cgi\n\n"; exit; } | |
| 34 | my $uid = $userinfo->{user_id}; $uid =~ s/[^0-9]//g; | |
| 35 | ||
| 36 | eval { | |
| 37 | require MODS::ContactForge::Permissions; | |
| 38 | MODS::ContactForge::Permissions->new->require_feature($userinfo, 'reports_view'); | |
| 39 | }; | |
| 40 | ||
| 41 | sub _esc { my $s = shift; return '' unless defined $s; | |
| 42 | $s =~ s/&/&/g; $s =~ s/</</g; $s =~ s/>/>/g; $s =~ s/"/"/g; return $s; } | |
| 43 | sub _money { my $c = shift || 0; return '$' . sprintf('%.0f', $c/100); } | |
| 44 | ||
| 45 | my $dbh = $db->db_connect(); | |
| 46 | ||
| 47 | # ---- (a) Deals won/lost by month, last 6 months --------------------- | |
| 48 | my @months = $db->db_readwrite_multiple($dbh, qq~ | |
| 49 | SELECT DATE_FORMAT(d.actual_close_date, '%Y-%m') AS ym, | |
| 50 | SUM(CASE WHEN s.is_won=1 THEN 1 ELSE 0 END) AS won, | |
| 51 | SUM(CASE WHEN s.is_lost=1 THEN 1 ELSE 0 END) AS lost, | |
| 52 | SUM(CASE WHEN s.is_won=1 THEN d.amount_cents ELSE 0 END) AS won_amt | |
| 53 | FROM `${DB}`.deals d | |
| 54 | JOIN `${DB}`.deal_stages s ON s.id=d.stage_id | |
| 55 | WHERE d.owner_user_id='$uid' | |
| 56 | AND d.actual_close_date IS NOT NULL | |
| 57 | AND d.actual_close_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) | |
| 58 | GROUP BY ym | |
| 59 | ORDER BY ym ASC | |
| 60 | ~, $ENV{SCRIPT_NAME}, __LINE__); | |
| 61 | ||
| 62 | # ---- (b) Pipeline value by stage (default pipeline) ----------------- | |
| 63 | my $pipe = $db->db_readwrite($dbh, | |
| 64 | "SELECT id, name FROM `${DB}`.pipelines WHERE is_default=1 ORDER BY id ASC LIMIT 1", | |
| 65 | $ENV{SCRIPT_NAME}, __LINE__); | |
| 66 | $pipe ||= $db->db_readwrite($dbh, | |
| 67 | "SELECT id, name FROM `${DB}`.pipelines ORDER BY id ASC LIMIT 1", | |
| 68 | $ENV{SCRIPT_NAME}, __LINE__); | |
| 69 | my $pid = $pipe ? $pipe->{id} : 0; | |
| 70 | ||
| 71 | my @stages = $db->db_readwrite_multiple($dbh, qq~ | |
| 72 | SELECT s.id, s.name, s.color, s.is_won, s.is_lost, | |
| 73 | COUNT(d.id) AS n, | |
| 74 | COALESCE(SUM(d.amount_cents),0) AS total_cents | |
| 75 | FROM `${DB}`.deal_stages s | |
| 76 | LEFT JOIN `${DB}`.deals d ON d.stage_id=s.id AND d.owner_user_id='$uid' AND d.is_archived=0 | |
| 77 | WHERE s.pipeline_id='$pid' | |
| 78 | GROUP BY s.id | |
| 79 | ORDER BY s.sort_order ASC | |
| 80 | ~, $ENV{SCRIPT_NAME}, __LINE__); | |
| 81 | ||
| 82 | # ---- (c) Activities per user, last 30 days -------------------------- | |
| 83 | my @users = $db->db_readwrite_multiple($dbh, qq~ | |
| 84 | SELECT u.id, u.display_name, u.email, COUNT(a.id) AS n | |
| 85 | FROM `${DB}`.users u | |
| 86 | LEFT JOIN `${DB}`.activities a ON a.owner_user_id=u.id | |
| 87 | AND a.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) | |
| 88 | WHERE u.id='$uid' OR u.owner_user_id='$uid' | |
| 89 | GROUP BY u.id | |
| 90 | ORDER BY n DESC | |
| 91 | LIMIT 20 | |
| 92 | ~, $ENV{SCRIPT_NAME}, __LINE__); | |
| 93 | ||
| 94 | # Fallback: just self if owner_user_id column not present / no teammates. | |
| 95 | if (!@users) { | |
| 96 | @users = $db->db_readwrite_multiple($dbh, qq~ | |
| 97 | SELECT u.id, u.display_name, u.email, COUNT(a.id) AS n | |
| 98 | FROM `${DB}`.users u | |
| 99 | LEFT JOIN `${DB}`.activities a ON a.owner_user_id=u.id | |
| 100 | AND a.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) | |
| 101 | WHERE u.id='$uid' | |
| 102 | GROUP BY u.id | |
| 103 | ~, $ENV{SCRIPT_NAME}, __LINE__); | |
| 104 | } | |
| 105 | ||
| 106 | # ---- (d) Conversion rate by lead source ----------------------------- | |
| 107 | my @sources = $db->db_readwrite_multiple($dbh, qq~ | |
| 108 | SELECT ls.name, | |
| 109 | COUNT(c.id) AS total_contacts, | |
| 110 | SUM(CASE WHEN c.lifecycle_stage='customer' THEN 1 ELSE 0 END) AS customers | |
| 111 | FROM `${DB}`.lead_sources ls | |
| 112 | LEFT JOIN `${DB}`.contacts c ON c.lead_source_id=ls.id AND c.owner_user_id='$uid' | |
| 113 | WHERE ls.is_active=1 | |
| 114 | GROUP BY ls.id | |
| 115 | ORDER BY total_contacts DESC | |
| 116 | ~, $ENV{SCRIPT_NAME}, __LINE__); | |
| 117 | ||
| 118 | # ---- (e) Average deal size by owner --------------------------------- | |
| 119 | my @avg_owners = $db->db_readwrite_multiple($dbh, qq~ | |
| 120 | SELECT u.id, u.display_name, u.email, | |
| 121 | COUNT(d.id) AS n, | |
| 122 | COALESCE(AVG(d.amount_cents),0) AS avg_cents | |
| 123 | FROM `${DB}`.deals d | |
| 124 | JOIN `${DB}`.users u ON u.id=d.owner_user_id | |
| 125 | WHERE d.is_archived=0 AND (u.id='$uid' OR u.owner_user_id='$uid') | |
| 126 | GROUP BY u.id | |
| 127 | ORDER BY avg_cents DESC | |
| 128 | LIMIT 20 | |
| 129 | ~, $ENV{SCRIPT_NAME}, __LINE__); | |
| 130 | if (!@avg_owners) { | |
| 131 | @avg_owners = $db->db_readwrite_multiple($dbh, qq~ | |
| 132 | SELECT u.id, u.display_name, u.email, | |
| 133 | COUNT(d.id) AS n, | |
| 134 | COALESCE(AVG(d.amount_cents),0) AS avg_cents | |
| 135 | FROM `${DB}`.deals d | |
| 136 | JOIN `${DB}`.users u ON u.id=d.owner_user_id | |
| 137 | WHERE d.is_archived=0 AND u.id='$uid' | |
| 138 | GROUP BY u.id | |
| 139 | ~, $ENV{SCRIPT_NAME}, __LINE__); | |
| 140 | } | |
| 141 | ||
| 142 | $db->db_disconnect($dbh); | |
| 143 | ||
| 144 | # ---- Build chart (a): grouped bars per month ------------------------ | |
| 145 | my $a_html = '<p style="color:#9ca3af">No closed deals in the last 6 months yet.</p>'; | |
| 146 | if (@months) { | |
| 147 | my $max = 1; | |
| 148 | foreach my $m (@months) { $max = $m->{won} if $m->{won} > $max; $max = $m->{lost} if $m->{lost} > $max; } | |
| 149 | my $w = 90; # px per month column | |
| 150 | my $h = 180; | |
| 151 | my $cols = ''; | |
| 152 | foreach my $m (@months) { | |
| 153 | my $wh = int(($m->{won} / $max) * ($h - 30)); | |
| 154 | my $lh = int(($m->{lost} / $max) * ($h - 30)); | |
| 155 | $cols .= qq~ | |
| 156 | <div style="text-align:center;width:${w}px;display:inline-block;vertical-align:bottom"> | |
| 157 | <div style="height:${h}px;display:flex;align-items:flex-end;justify-content:center;gap:4px"> | |
| 158 | <div title="Won: $m->{won}" style="width:24px;height:${wh}px;background:#10b981;border-radius:3px 3px 0 0"></div> | |
| 159 | <div title="Lost: $m->{lost}" style="width:24px;height:${lh}px;background:#ef4444;border-radius:3px 3px 0 0"></div> | |
| 160 | </div> | |
| 161 | <div style="color:#9ca3af;font-size:11px;margin-top:4px">$m->{ym}</div> | |
| 162 | <div style="color:#10b981;font-size:11px">~ . _money($m->{won_amt}) . qq~</div> | |
| 163 | </div>~; | |
| 164 | } | |
| 165 | $a_html = qq~<div style="overflow-x:auto">$cols</div> | |
| 166 | <div style="margin-top:8px;color:#9ca3af;font-size:12px"> | |
| 167 | <span style="color:#10b981">■</span> Won | |
| 168 | <span style="color:#ef4444">■</span> Lost | |
| 169 | </div>~; | |
| 170 | } | |
| 171 | ||
| 172 | # ---- Chart (b): horizontal stage bars ------------------------------- | |
| 173 | my $b_html = '<p style="color:#9ca3af">No deals yet.</p>'; | |
| 174 | if (@stages) { | |
| 175 | my $max = 1; | |
| 176 | foreach my $s (@stages) { $max = $s->{total_cents} if $s->{total_cents} > $max; } | |
| 177 | $b_html = ''; | |
| 178 | foreach my $s (@stages) { | |
| 179 | next if $s->{is_lost}; # skip noise | |
| 180 | my $pct = int(($s->{total_cents} / $max) * 100); | |
| 181 | $pct = 1 if $pct < 1 && $s->{total_cents} > 0; | |
| 182 | my $color = $s->{color} || '#06b6d4'; | |
| 183 | $b_html .= qq~ | |
| 184 | <div style="margin-bottom:8px"> | |
| 185 | <div style="display:flex;justify-content:space-between;color:#9ca3af;font-size:12px"> | |
| 186 | <span>~ . _esc($s->{name}) . qq~ (${\$s->{n}})</span> | |
| 187 | <span>~ . _money($s->{total_cents}) . qq~</span> | |
| 188 | </div> | |
| 189 | <div style="background:#0f172a;border-radius:3px;height:14px;margin-top:2px;overflow:hidden"> | |
| 190 | <div style="width:${pct}%;height:100%;background:$color"></div> | |
| 191 | </div> | |
| 192 | </div>~; | |
| 193 | } | |
| 194 | } | |
| 195 | ||
| 196 | # ---- Chart (c): activities per user --------------------------------- | |
| 197 | my $c_html = '<p style="color:#9ca3af">No activities in the last 30 days.</p>'; | |
| 198 | if (@users) { | |
| 199 | my $max = 1; foreach my $u (@users) { $max = $u->{n} if ($u->{n}||0) > $max; } | |
| 200 | $c_html = ''; | |
| 201 | foreach my $u (@users) { | |
| 202 | my $name = _esc($u->{display_name} || $u->{email} || ('User #' . $u->{id})); | |
| 203 | my $pct = int((($u->{n}||0) / $max) * 100); | |
| 204 | $c_html .= qq~ | |
| 205 | <div style="margin-bottom:8px"> | |
| 206 | <div style="display:flex;justify-content:space-between;color:#9ca3af;font-size:12px"> | |
| 207 | <span>$name</span><span>~ . ($u->{n}||0) . qq~</span> | |
| 208 | </div> | |
| 209 | <div style="background:#0f172a;border-radius:3px;height:14px;margin-top:2px;overflow:hidden"> | |
| 210 | <div style="width:${pct}%;height:100%;background:#2dd4bf"></div> | |
| 211 | </div> | |
| 212 | </div>~; | |
| 213 | } | |
| 214 | } | |
| 215 | ||
| 216 | # ---- Chart (d): conversion by source -------------------------------- | |
| 217 | my $d_html = '<p style="color:#9ca3af">No lead sources tracked yet.</p>'; | |
| 218 | if (@sources) { | |
| 219 | $d_html = '<table class="table"><thead><tr><th>Source</th><th style="text-align:right">Contacts</th><th style="text-align:right">Customers</th><th>Conversion</th></tr></thead><tbody>'; | |
| 220 | foreach my $s (@sources) { | |
| 221 | my $rate = ($s->{total_contacts}||0) > 0 ? int((($s->{customers}||0) / $s->{total_contacts}) * 100) : 0; | |
| 222 | $d_html .= qq~<tr> | |
| 223 | <td>~ . _esc($s->{name}) . qq~</td> | |
| 224 | <td style="text-align:right">~ . ($s->{total_contacts}||0) . qq~</td> | |
| 225 | <td style="text-align:right">~ . ($s->{customers}||0) . qq~</td> | |
| 226 | <td> | |
| 227 | <div style="display:flex;align-items:center;gap:8px"> | |
| 228 | <div style="background:#0f172a;border-radius:3px;height:10px;flex:1;overflow:hidden"> | |
| 229 | <div style="width:${rate}%;height:100%;background:#10b981"></div> | |
| 230 | </div> | |
| 231 | <span style="color:#9ca3af;font-size:12px;min-width:36px;text-align:right">${rate}%</span> | |
| 232 | </div> | |
| 233 | </td> | |
| 234 | </tr>~; | |
| 235 | } | |
| 236 | $d_html .= '</tbody></table>'; | |
| 237 | } | |
| 238 | ||
| 239 | # ---- Chart (e): avg deal size by owner ------------------------------ | |
| 240 | my $e_html = '<p style="color:#9ca3af">No deals yet.</p>'; | |
| 241 | if (@avg_owners) { | |
| 242 | my $max = 1; foreach my $u (@avg_owners) { $max = $u->{avg_cents} if ($u->{avg_cents}||0) > $max; } | |
| 243 | $e_html = '<table class="table"><thead><tr><th>Owner</th><th style="text-align:right">Deals</th><th style="text-align:right">Avg size</th><th></th></tr></thead><tbody>'; | |
| 244 | foreach my $u (@avg_owners) { | |
| 245 | my $name = _esc($u->{display_name} || $u->{email} || ('User #' . $u->{id})); | |
| 246 | my $pct = int((($u->{avg_cents}||0) / $max) * 100); | |
| 247 | $e_html .= qq~<tr> | |
| 248 | <td>$name</td> | |
| 249 | <td style="text-align:right">~ . ($u->{n}||0) . qq~</td> | |
| 250 | <td style="text-align:right">~ . _money($u->{avg_cents}) . qq~</td> | |
| 251 | <td style="width:30%"> | |
| 252 | <div style="background:#0f172a;border-radius:3px;height:10px;overflow:hidden"> | |
| 253 | <div style="width:${pct}%;height:100%;background:#0d9488"></div> | |
| 254 | </div> | |
| 255 | </td> | |
| 256 | </tr>~; | |
| 257 | } | |
| 258 | $e_html .= '</tbody></table>'; | |
| 259 | } | |
| 260 | ||
| 261 | my $body = qq~ | |
| 262 | <div class="page-header" style="margin-bottom:16px"> | |
| 263 | <h1 style="margin:0">Reports</h1> | |
| 264 | <div style="color:#9ca3af;margin-top:4px">Pipeline health, conversion, and team activity.</div> | |
| 265 | </div> | |
| 266 | ||
| 267 | <div style="display:grid;grid-template-columns:repeat(auto-fit,minmax(420px,1fr));gap:16px"> | |
| 268 | <div class="card" style="background:#111827;border:1px solid #1f2937;border-radius:8px;padding:16px"> | |
| 269 | <h3 style="margin-top:0">Deals won & lost, last 6 months</h3> | |
| 270 | $a_html | |
| 271 | </div> | |
| 272 | <div class="card" style="background:#111827;border:1px solid #1f2937;border-radius:8px;padding:16px"> | |
| 273 | <h3 style="margin-top:0">Pipeline value by stage</h3> | |
| 274 | $b_html | |
| 275 | </div> | |
| 276 | <div class="card" style="background:#111827;border:1px solid #1f2937;border-radius:8px;padding:16px"> | |
| 277 | <h3 style="margin-top:0">Activities per user (last 30 days)</h3> | |
| 278 | $c_html | |
| 279 | </div> | |
| 280 | <div class="card" style="background:#111827;border:1px solid #1f2937;border-radius:8px;padding:16px"> | |
| 281 | <h3 style="margin-top:0">Conversion rate by lead source</h3> | |
| 282 | $d_html | |
| 283 | </div> | |
| 284 | <div class="card" style="background:#111827;border:1px solid #1f2937;border-radius:8px;padding:16px;grid-column:1/-1"> | |
| 285 | <h3 style="margin-top:0">Average deal size by owner</h3> | |
| 286 | $e_html | |
| 287 | </div> | |
| 288 | </div> | |
| 289 | ||
| 290 | <style> | |
| 291 | .table{width:100%;border-collapse:collapse;font-size:13px} | |
| 292 | .table th{text-align:left;color:#9ca3af;font-weight:500;border-bottom:1px solid #1f2937;padding:6px 8px} | |
| 293 | .table td{padding:6px 8px;border-bottom:1px solid #1f2937} | |
| 294 | </style> | |
| 295 | ~; | |
| 296 | ||
| 297 | print "Content-Type: text/html; charset=utf-8\nCache-Control: no-store, no-cache, must-revalidate, max-age=0\nPragma: no-cache\nExpires: 0\n\n"; | |
| 298 | $wrap->render({ userinfo => $userinfo, page_key => 'reports', | |
| 299 | title => 'Reports · ContactForge', body => $body }); |