Diff -- /var/www/vhosts/3dshawn.com/crm.3dshawn.com/dashboard.cgi
Diff

/var/www/vhosts/3dshawn.com/crm.3dshawn.com/dashboard.cgi

added on local at 2026-07-01 15:02:42

Added
+311
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to 1a44093f23d8
Restore this content →
Unified diff
Naive LCS-based line diff. Additions in emerald, deletions in rose. Both sides decompressed live from BLOB_STORE.
1#!/usr/bin/perl
2#======================================================================
3# ContactForge - CRM dashboard.
4#
5# KPI tiles + sections: my upcoming tasks (next 7d), recently active
6# contacts, top in-progress deals by amount, recent activities
7# timeline. Every tile is a deep-link into the relevant page.
8#======================================================================
9use strict;
10use warnings;
11
12use lib '/var/www/vhosts/3dshawn.com/crm.3dshawn.com';
13use CGI;
14use MODS::Template;
15use MODS::DBConnect;
16use MODS::Login;
17use MODS::ContactForge::Config;
18use MODS::ContactForge::Wrapper;
19
20my $q = CGI->new;
21my $auth = MODS::Login->new;
22my $wrap = MODS::ContactForge::Wrapper->new;
23my $db = MODS::DBConnect->new;
24my $cfg = MODS::ContactForge::Config->new;
25my $DB = $cfg->settings('database_name');
26
27$|=1;
28my $userinfo = $auth->login_verify();
29if (!$userinfo) {
30 print "Status: 302 Found\nLocation: /login.cgi\n\n";
31 exit;
32}
33my $uid = $userinfo->{user_id}; $uid =~ s/[^0-9]//g;
34
35my $dbh = $db->db_connect();
36
37sub _scalar {
38 my ($sql) = @_;
39 my $r = $db->db_readwrite($dbh, $sql, $ENV{SCRIPT_NAME}, __LINE__);
40 return ($r && defined $r->{n}) ? $r->{n} : 0;
41}
42sub _esc {
43 my $s = shift; return '' unless defined $s;
44 $s =~ s/&/&amp;/g; $s =~ s/</&lt;/g; $s =~ s/>/&gt;/g; $s =~ s/"/&quot;/g;
45 return $s;
46}
47sub _money { my $c = shift || 0; return '$' . sprintf('%.0f', $c/100); }
48sub _badge {
49 my $s = shift || 'lead';
50 my %c = (lead=>'#6b7280', marketing_qualified=>'#3b82f6', opportunity=>'#06b6d4',
51 customer=>'#10b981', evangelist=>'#a855f7', other=>'#64748b');
52 my $col = $c{$s} || '#64748b';
53 return qq~<span class="badge" style="background:${col}22;color:${col};border:1px solid ${col}55;padding:2px 8px;border-radius:9px;font-size:11px;text-transform:uppercase;letter-spacing:.5px">~ . _esc($s) . qq~</span>~;
54}
55sub _rel {
56 my $t = shift; return '-' unless $t;
57 my $r = $db->db_readwrite($dbh, "SELECT TIMESTAMPDIFF(MINUTE, '$t', NOW()) AS n", $ENV{SCRIPT_NAME}, __LINE__);
58 my $m = ($r && defined $r->{n}) ? $r->{n} : 0;
59 return 'just now' if $m < 1;
60 return "${m}m ago" if $m < 60;
61 my $h = int($m/60); return "${h}h ago" if $h < 24;
62 my $d = int($h/24); return "${d}d ago" if $d < 30;
63 return $t;
64}
65
66# ---- KPI tiles -------------------------------------------------------
67my $open_deals = _scalar(qq~
68 SELECT COUNT(*) AS n FROM `${DB}`.deals d
69 JOIN `${DB}`.deal_stages s ON s.id=d.stage_id
70 WHERE d.owner_user_id='$uid' AND d.is_archived=0 AND s.is_won=0 AND s.is_lost=0
71~);
72my $open_deals_amt = _scalar(qq~
73 SELECT COALESCE(SUM(d.amount_cents),0) AS n FROM `${DB}`.deals d
74 JOIN `${DB}`.deal_stages s ON s.id=d.stage_id
75 WHERE d.owner_user_id='$uid' AND d.is_archived=0 AND s.is_won=0 AND s.is_lost=0
76~);
77my $closing_week = _scalar(qq~
78 SELECT COUNT(*) AS n FROM `${DB}`.deals d
79 JOIN `${DB}`.deal_stages s ON s.id=d.stage_id
80 WHERE d.owner_user_id='$uid' AND d.is_archived=0 AND s.is_won=0 AND s.is_lost=0
81 AND d.expected_close_date IS NOT NULL
82 AND d.expected_close_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
83~);
84my $hot_contacts = _scalar(qq~
85 SELECT COUNT(*) AS n FROM `${DB}`.contacts
86 WHERE owner_user_id='$uid' AND is_archived=0
87 AND (lifecycle_stage='opportunity' OR lead_score>=50)
88~);
89my $acts_today = _scalar(qq~
90 SELECT COUNT(*) AS n FROM `${DB}`.activities
91 WHERE owner_user_id='$uid' AND DATE(happened_at)=CURDATE()
92~);
93my $tasks_today = _scalar(qq~
94 SELECT COUNT(*) AS n FROM `${DB}`.tasks
95 WHERE (assigned_to_user_id='$uid' OR owner_user_id='$uid')
96 AND status IN ('open','in_progress')
97 AND DATE(due_at)=CURDATE()
98~);
99my $tasks_overdue = _scalar(qq~
100 SELECT COUNT(*) AS n FROM `${DB}`.tasks
101 WHERE (assigned_to_user_id='$uid' OR owner_user_id='$uid')
102 AND status IN ('open','in_progress')
103 AND due_at IS NOT NULL AND due_at < NOW()
104~);
105
106# ---- Upcoming tasks (next 7d) ---------------------------------------
107my @tasks = $db->db_readwrite_multiple($dbh, qq~
108 SELECT t.id, t.title, t.priority, t.status, t.due_at,
109 c.first_name, c.last_name
110 FROM `${DB}`.tasks t
111 LEFT JOIN `${DB}`.contacts c ON c.id=t.contact_id
112 WHERE (t.assigned_to_user_id='$uid' OR t.owner_user_id='$uid')
113 AND t.status IN ('open','in_progress')
114 AND t.due_at IS NOT NULL
115 AND t.due_at BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 7 DAY)
116 ORDER BY t.due_at ASC
117 LIMIT 8
118~, $ENV{SCRIPT_NAME}, __LINE__);
119
120# ---- Recently active contacts ---------------------------------------
121my @hot = $db->db_readwrite_multiple($dbh, qq~
122 SELECT c.id, c.first_name, c.last_name, c.email, c.lifecycle_stage,
123 c.lead_score, c.last_activity_at, co.name AS company_name
124 FROM `${DB}`.contacts c
125 LEFT JOIN `${DB}`.companies co ON co.id=c.company_id
126 WHERE c.owner_user_id='$uid' AND c.is_archived=0
127 AND c.last_activity_at IS NOT NULL
128 ORDER BY c.last_activity_at DESC
129 LIMIT 8
130~, $ENV{SCRIPT_NAME}, __LINE__);
131
132# ---- Top deals by amount --------------------------------------------
133my @top_deals = $db->db_readwrite_multiple($dbh, qq~
134 SELECT d.id, d.title, d.amount_cents, d.expected_close_date,
135 s.name AS stage_name, s.color AS stage_color,
136 co.name AS company_name
137 FROM `${DB}`.deals d
138 JOIN `${DB}`.deal_stages s ON s.id=d.stage_id
139 LEFT JOIN `${DB}`.companies co ON co.id=d.company_id
140 WHERE d.owner_user_id='$uid' AND d.is_archived=0
141 AND s.is_won=0 AND s.is_lost=0
142 ORDER BY d.amount_cents DESC
143 LIMIT 8
144~, $ENV{SCRIPT_NAME}, __LINE__);
145
146# ---- Activity timeline (last 10) ------------------------------------
147my @timeline = $db->db_readwrite_multiple($dbh, qq~
148 SELECT a.id, a.kind, a.subject, a.happened_at, a.outcome,
149 c.first_name, c.last_name, d.title AS deal_title
150 FROM `${DB}`.activities a
151 LEFT JOIN `${DB}`.contacts c ON c.id=a.contact_id
152 LEFT JOIN `${DB}`.deals d ON d.id=a.deal_id
153 WHERE a.owner_user_id='$uid'
154 ORDER BY COALESCE(a.happened_at, a.created_at) DESC
155 LIMIT 10
156~, $ENV{SCRIPT_NAME}, __LINE__);
157
158$db->db_disconnect($dbh);
159
160# ---- Build body ------------------------------------------------------
161my %icon = (call=>'&#9742;', meeting=>'&#128197;', email=>'&#9993;',
162 sms=>'&#128241;', demo=>'&#127908;', note=>'&#9998;', other=>'&#8226;');
163
164my $tasks_html = '';
165if (@tasks) {
166 $tasks_html .= '<table class="table"><thead><tr><th>Task</th><th>Contact</th><th>Priority</th><th>Due</th></tr></thead><tbody>';
167 foreach my $t (@tasks) {
168 my $name = join(' ', grep {$_} ($t->{first_name}, $t->{last_name})) || '-';
169 $tasks_html .= '<tr>'
170 . qq~<td><a href="/tasks.cgi?id=$t->{id}">~ . _esc($t->{title}) . '</a></td>'
171 . '<td>' . _esc($name) . '</td>'
172 . '<td>' . _esc($t->{priority}) . '</td>'
173 . '<td>' . _esc($t->{due_at}) . '</td>'
174 . '</tr>';
175 }
176 $tasks_html .= '</tbody></table>';
177} else {
178 $tasks_html = '<p style="color:#9ca3af">No tasks due in the next 7 days.</p>';
179}
180
181my $hot_html = '';
182if (@hot) {
183 $hot_html .= '<table class="table"><thead><tr><th>Contact</th><th>Company</th><th>Stage</th><th>Score</th><th>Last activity</th></tr></thead><tbody>';
184 foreach my $c (@hot) {
185 my $name = join(' ', grep {$_} ($c->{first_name}, $c->{last_name})) || '(no name)';
186 $hot_html .= '<tr>'
187 . qq~<td><a href="/contact_detail.cgi?id=$c->{id}">~ . _esc($name) . '</a></td>'
188 . '<td>' . _esc($c->{company_name} || '-') . '</td>'
189 . '<td>' . _badge($c->{lifecycle_stage}) . '</td>'
190 . '<td>' . _esc($c->{lead_score}) . '</td>'
191 . '<td>' . _esc(_rel($c->{last_activity_at})) . '</td>'
192 . '</tr>';
193 }
194 $hot_html .= '</tbody></table>';
195} else {
196 $hot_html = '<p style="color:#9ca3af">No recently active contacts.</p>';
197}
198
199my $deals_html = '';
200if (@top_deals) {
201 $deals_html .= '<table class="table"><thead><tr><th>Deal</th><th>Company</th><th>Stage</th><th>Close</th><th style="text-align:right">Amount</th></tr></thead><tbody>';
202 foreach my $d (@top_deals) {
203 my $color = $d->{stage_color} || '#06b6d4';
204 $deals_html .= '<tr>'
205 . qq~<td><a href="/deal_detail.cgi?id=$d->{id}">~ . _esc($d->{title}) . '</a></td>'
206 . '<td>' . _esc($d->{company_name} || '-') . '</td>'
207 . qq~<td><span style="color:$color">~ . _esc($d->{stage_name}) . '</span></td>'
208 . '<td>' . _esc($d->{expected_close_date} || '-') . '</td>'
209 . '<td style="text-align:right">' . _money($d->{amount_cents}) . '</td>'
210 . '</tr>';
211 }
212 $deals_html .= '</tbody></table>';
213} else {
214 $deals_html = '<p style="color:#9ca3af">No in-progress deals yet.</p>';
215}
216
217my $tl_html = '';
218if (@timeline) {
219 $tl_html .= '<ul style="list-style:none;padding:0;margin:0">';
220 foreach my $a (@timeline) {
221 my $ico = $icon{$a->{kind}} || '&#8226;';
222 my $who = join(' ', grep {$_} ($a->{first_name}, $a->{last_name}));
223 $tl_html .= '<li style="padding:8px 0;border-bottom:1px solid #1f2937">'
224 . qq~<span style="display:inline-block;width:28px;text-align:center;font-size:16px">$ico</span>~
225 . '<strong>' . _esc($a->{kind}) . '</strong> &middot; '
226 . _esc($a->{subject} || '(no subject)')
227 . ($who ? ' &middot; <span style="color:#9ca3af">with ' . _esc($who) . '</span>' : '')
228 . ($a->{deal_title} ? ' &middot; <span style="color:#9ca3af">re: ' . _esc($a->{deal_title}) . '</span>' : '')
229 . '<span style="float:right;color:#6b7280;font-size:12px">' . _esc(_rel($a->{happened_at})) . '</span>'
230 . '</li>';
231 }
232 $tl_html .= '</ul>';
233} else {
234 $tl_html = '<p style="color:#9ca3af">No activities logged yet. Log a call, meeting, or email to start the timeline.</p>';
235}
236
237my $body = qq~
238<div class="page-header" style="display:flex;align-items:center;justify-content:space-between;margin-bottom:16px">
239 <div>
240 <h1 style="margin:0">Dashboard</h1>
241 <div style="color:#9ca3af;margin-top:4px">Your pipeline at a glance.</div>
242 </div>
243 <div>
244 <a class="btn btn-primary" href="/contacts.cgi" style="background:#0d9488;color:#fff;padding:8px 14px;border-radius:6px;text-decoration:none;margin-right:6px">View Contacts</a>
245 <a class="btn" href="/deals.cgi" style="background:#1f2937;color:#fff;padding:8px 14px;border-radius:6px;text-decoration:none">Open Pipeline</a>
246 </div>
247</div>
248
249<div class="kpi-grid" style="display:grid;grid-template-columns:repeat(auto-fit,minmax(180px,1fr));gap:12px;margin-bottom:20px">
250 <a class="kpi" href="/deals.cgi" style="background:#111827;border:1px solid #1f2937;border-radius:8px;padding:14px;text-decoration:none;color:inherit;display:block">
251 <div class="kpi-label" style="color:#9ca3af;font-size:12px;text-transform:uppercase;letter-spacing:.5px">Open deals</div>
252 <div class="kpi-value" style="font-size:22px;font-weight:600;margin-top:4px;color:#2dd4bf">$open_deals <span style="color:#6b7280;font-size:14px;font-weight:400">/ ~ . _money($open_deals_amt) . qq~</span></div>
253 </a>
254 <a class="kpi" href="/deals.cgi" style="background:#111827;border:1px solid #1f2937;border-radius:8px;padding:14px;text-decoration:none;color:inherit;display:block">
255 <div class="kpi-label" style="color:#9ca3af;font-size:12px;text-transform:uppercase;letter-spacing:.5px">Closing this week</div>
256 <div class="kpi-value" style="font-size:22px;font-weight:600;margin-top:4px">$closing_week</div>
257 </a>
258 <a class="kpi" href="/contacts.cgi?stage=opportunity" style="background:#111827;border:1px solid #1f2937;border-radius:8px;padding:14px;text-decoration:none;color:inherit;display:block">
259 <div class="kpi-label" style="color:#9ca3af;font-size:12px;text-transform:uppercase;letter-spacing:.5px">Hot contacts</div>
260 <div class="kpi-value" style="font-size:22px;font-weight:600;margin-top:4px">$hot_contacts</div>
261 </a>
262 <a class="kpi" href="/activities.cgi" style="background:#111827;border:1px solid #1f2937;border-radius:8px;padding:14px;text-decoration:none;color:inherit;display:block">
263 <div class="kpi-label" style="color:#9ca3af;font-size:12px;text-transform:uppercase;letter-spacing:.5px">Activities today</div>
264 <div class="kpi-value" style="font-size:22px;font-weight:600;margin-top:4px">$acts_today</div>
265 </a>
266 <a class="kpi" href="/tasks.cgi" style="background:#111827;border:1px solid #1f2937;border-radius:8px;padding:14px;text-decoration:none;color:inherit;display:block">
267 <div class="kpi-label" style="color:#9ca3af;font-size:12px;text-transform:uppercase;letter-spacing:.5px">Tasks today</div>
268 <div class="kpi-value" style="font-size:22px;font-weight:600;margin-top:4px">$tasks_today</div>
269 </a>
270 <a class="kpi" href="/tasks.cgi?filter=overdue" style="background:#111827;border:1px solid #dc262633;border-radius:8px;padding:14px;text-decoration:none;color:inherit;display:block">
271 <div class="kpi-label" style="color:#9ca3af;font-size:12px;text-transform:uppercase;letter-spacing:.5px">Overdue</div>
272 <div class="kpi-value" style="font-size:22px;font-weight:600;margin-top:4px;color:#f87171">$tasks_overdue</div>
273 </a>
274</div>
275
276<div style="display:grid;grid-template-columns:repeat(auto-fit,minmax(420px,1fr));gap:16px">
277 <div class="card" style="background:#111827;border:1px solid #1f2937;border-radius:8px;padding:16px">
278 <h3 style="margin-top:0;margin-bottom:12px">Upcoming tasks (next 7 days)</h3>
279 $tasks_html
280 </div>
281 <div class="card" style="background:#111827;border:1px solid #1f2937;border-radius:8px;padding:16px">
282 <h3 style="margin-top:0;margin-bottom:12px">Recently active contacts</h3>
283 $hot_html
284 </div>
285 <div class="card" style="background:#111827;border:1px solid #1f2937;border-radius:8px;padding:16px">
286 <h3 style="margin-top:0;margin-bottom:12px">Top in-progress deals</h3>
287 $deals_html
288 </div>
289 <div class="card" style="background:#111827;border:1px solid #1f2937;border-radius:8px;padding:16px">
290 <h3 style="margin-top:0;margin-bottom:12px">Recent activity</h3>
291 $tl_html
292 </div>
293</div>
294
295<style>
296.table{width:100%;border-collapse:collapse;font-size:13px}
297.table th{text-align:left;color:#9ca3af;font-weight:500;border-bottom:1px solid #1f2937;padding:6px 8px}
298.table td{padding:6px 8px;border-bottom:1px solid #1f2937}
299.table a{color:#2dd4bf;text-decoration:none}
300.table a:hover{text-decoration:underline}
301</style>
302~;
303
304print "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";
305
306$wrap->render({
307 userinfo => $userinfo,
308 page_key => 'dashboard',
309 title => 'Dashboard',
310 body => $body,
311});
Keyboard: j next diff k previous diff g top G bottom r restore c compile-check ? help