Diff -- /var/www/vhosts/3dshawn.com/shop.3dshawn.com/admin_messages.cgi

O Operator
Diff

/var/www/vhosts/3dshawn.com/shop.3dshawn.com/admin_messages.cgi

added on local at 2026-07-11 18:36:22

Added
+438
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to cb7627d7533c
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# ShopCart -- admin support inbox.
4#
5# Every customer support thread in one place. Sister to support.cgi
6# but viewed from the admin side: same two-pane layout, but the list
7# shows EVERY customer's threads (not just yours), and the right pane
8# has extra controls (invite to chat, mark resolved, change priority).
9#
10# Routes:
11# GET /admin_messages.cgi -- list of all threads
12# GET /admin_messages.cgi?thread_id=N -- list + open thread N
13# GET /admin_messages.cgi?status=resolved -- filter list
14# POST /admin_messages.cgi action=set_status -- mark resolved/closed/etc
15# (Replies go through /support_send.cgi same as the customer side.)
16#
17# Admin-only: non-admin users get a 403.
18#
19# Note: live-chat actions were removed -- async messaging only. The
20# Support.pm helpers for chat (invite_to_chat / end_chat) still
21# exist but are unreachable from the UI.
22#======================================================================
23use strict;
24use warnings;
25
26use lib '/var/www/vhosts/3dshawn.com/shop.3dshawn.com';
27use CGI;
28use MODS::Template;
29use MODS::DBConnect;
30use MODS::Login;
31use MODS::ShopCart::Config;
32use MODS::ShopCart::Wrapper;
33use MODS::ShopCart::Support;
34
35my $q = CGI->new;
36my $form = $q->Vars;
37my $tfile = MODS::Template->new;
38my $db = MODS::DBConnect->new;
39my $auth = MODS::Login->new;
40my $config = MODS::ShopCart::Config->new;
41my $wrap = MODS::ShopCart::Wrapper->new;
42my $sup = MODS::ShopCart::Support->new;
43my $DB = $config->settings('database_name');
44
45$| = 1;
46
47my $userinfo = $auth->login_verify();
48unless ($userinfo) {
49 print "Status: 302 Found\nLocation: /login.cgi?return=/admin_messages.cgi\n\n";
50 exit;
51}
52unless ($userinfo->{is_admin}) {
53 print "Status: 403 Forbidden\nContent-Type: text/plain\n\nAdmin access required.\n";
54 exit;
55}
56require MODS::ShopCart::Permissions;
57MODS::ShopCart::Permissions->new->require_feature($userinfo, 'admin_view_messages');
58
59my $uid = $userinfo->{user_id};
60$uid =~ s/[^0-9]//g;
61
62my $dbh = $db->db_connect();
63
64# ---- POST handlers (PRG pattern) -----------------------------------
65if (($ENV{REQUEST_METHOD} || '') eq 'POST') {
66 my $action = $form->{action} || '';
67 my $tid = $form->{thread_id} || 0; $tid =~ s/[^0-9]//g;
68 if ($action eq 'set_status' && $tid) {
69 my $new = $form->{status} || '';
70 $sup->set_status($db, $dbh, $DB, $tid, $new);
71 $db->db_disconnect($dbh);
72 print "Status: 302 Found\nLocation: /admin_messages.cgi?thread_id=$tid\n\n";
73 exit;
74 }
75}
76
77# ---- GET render ----------------------------------------------------
78my $tab = lc($form->{tab} || 'inbox');
79$tab =~ s/[^a-z]//g;
80$tab = 'inbox' unless $tab =~ /^(inbox|reports)$/;
81
82my $filter = lc($form->{status} || '');
83$filter =~ s/[^a-z_]//g;
84$filter = '' unless $filter =~ /^(open|waiting_admin|waiting_customer|resolved|closed)$/;
85
86my $threads = ($tab eq 'inbox') ? $sup->list_threads_for_admin($db, $dbh, $DB, { status => $filter }) : [];
87
88# Reports-tab metrics: support volume + response time + resolved counts.
89my ($rep_total, $rep_open, $rep_waiting, $rep_resolved_7d, $rep_resolved_30d,
90 $rep_new_7d, $rep_new_30d, $rep_avg_resp_min);
91if ($tab eq 'reports') {
92 sub _n { my ($sql) = @_; my $r; eval { $r = $db->db_readwrite($dbh, $sql, $ENV{SCRIPT_NAME}, __LINE__); 1 }; return ($r && defined $r->{n}) ? $r->{n} : 0; }
93 $rep_total = _n("SELECT COUNT(*) AS n FROM `${DB}`.support_threads");
94 $rep_open = _n("SELECT COUNT(*) AS n FROM `${DB}`.support_threads WHERE status='open' OR status='waiting_admin'");
95 $rep_waiting = _n("SELECT COUNT(*) AS n FROM `${DB}`.support_threads WHERE status='waiting_customer'");
96 $rep_resolved_7d = _n("SELECT COUNT(*) AS n FROM `${DB}`.support_threads WHERE status='resolved' AND resolved_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)");
97 $rep_resolved_30d= _n("SELECT COUNT(*) AS n FROM `${DB}`.support_threads WHERE status='resolved' AND resolved_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)");
98 $rep_new_7d = _n("SELECT COUNT(*) AS n FROM `${DB}`.support_threads WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)");
99 $rep_new_30d = _n("SELECT COUNT(*) AS n FROM `${DB}`.support_threads WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)");
100 # Average first-response time: minutes between thread.created_at
101 # and the first admin reply in that thread. Skips threads where the
102 # admin hasn't replied yet.
103 $rep_avg_resp_min = _n(qq~
104 SELECT COALESCE(AVG(TIMESTAMPDIFF(MINUTE, t.created_at, first_admin.first_at)), 0) AS n
105 FROM `${DB}`.support_threads t
106 JOIN (
107 SELECT thread_id, MIN(created_at) AS first_at
108 FROM `${DB}`.support_messages
109 WHERE sender_role='admin'
110 GROUP BY thread_id
111 ) first_admin ON first_admin.thread_id = t.id
112 WHERE t.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
113 ~);
114 $rep_avg_resp_min = int($rep_avg_resp_min);
115}
116
117# Daily time-series for the Reports tab area charts. Two parallel
118# 30-day arrays: one for new threads, one for resolved threads. SQL
119# returns only days with at least one row; we fill in zeros for
120# missing days so the chart axis is continuous.
121my $chart_new_html = '';
122my $chart_resolved_html = '';
123if ($tab eq 'reports') {
124 my %new_by_day;
125 eval {
126 my @rows = $db->db_readwrite_multiple($dbh, qq~
127 SELECT DATE(created_at) AS d, COUNT(*) AS n
128 FROM `${DB}`.support_threads
129 WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
130 GROUP BY DATE(created_at)
131 ~, $ENV{SCRIPT_NAME}, __LINE__);
132 $new_by_day{$_->{d}} = $_->{n} + 0 for @rows;
133 };
134 my %res_by_day;
135 eval {
136 my @rows = $db->db_readwrite_multiple($dbh, qq~
137 SELECT DATE(resolved_at) AS d, COUNT(*) AS n
138 FROM `${DB}`.support_threads
139 WHERE status='resolved'
140 AND resolved_at IS NOT NULL
141 AND resolved_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
142 GROUP BY DATE(resolved_at)
143 ~, $ENV{SCRIPT_NAME}, __LINE__);
144 $res_by_day{$_->{d}} = $_->{n} + 0 for @rows;
145 };
146
147 require POSIX;
148 my ($s,$m,$h,$mday,$mon,$year) = localtime(time);
149 my $today_e = POSIX::mktime(0,0,12,$mday,$mon,$year);
150 my @months = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
151 my (@new_series, @res_series);
152 for (my $i = 29; $i >= 0; $i--) {
153 my @t = localtime($today_e - $i*86400);
154 my $iso = sprintf('%04d-%02d-%02d', $t[5]+1900, $t[4]+1, $t[3]);
155 my $label = sprintf('%s %d', $months[$t[4]], $t[3]);
156 push @new_series, { iso => $iso, label => $label, value => ($new_by_day{$iso} || 0) };
157 push @res_series, { iso => $iso, label => $label, value => ($res_by_day{$iso} || 0) };
158 }
159 $chart_new_html = _build_area_chart(id => 'new', color => '#3b82f6', data => \@new_series, title => 'New tickets');
160 $chart_resolved_html = _build_area_chart(id => 'res', color => '#10b981', data => \@res_series, title => 'Resolved');
161}
162
163# ----------------------------------------------------------------------
164# Area chart builder. Returns inline SVG with hotspot rects the JS
165# uses for hover tooltip + click drilldown. Vanilla SVG, no libs.
166# ----------------------------------------------------------------------
167sub _build_area_chart {
168 my (%o) = @_;
169 my $id = $o{id};
170 my $color = $o{color} || '#3b82f6';
171 my @data = @{ $o{data} || [] };
172 my $title = $o{title} || '';
173 return '' unless @data;
174
175 my $W = 760; my $H = 200;
176 my ($PT, $PB, $PL, $PR) = (16, 28, 36, 16);
177 my $plot_w = $W - $PL - $PR;
178 my $plot_h = $H - $PT - $PB;
179
180 my $max = 0;
181 for my $d (@data) { $max = $d->{value} if $d->{value} > $max; }
182 my $y_max = $max > 0 ? $max : 1;
183
184 my $n = scalar(@data);
185 my @points;
186 for (my $i = 0; $i < $n; $i++) {
187 my $x = $PL + ($n > 1 ? ($i / ($n - 1)) * $plot_w : 0);
188 my $y = $PT + $plot_h - ($data[$i]->{value} / $y_max) * $plot_h;
189 push @points, { x => sprintf('%.2f', $x), y => sprintf('%.2f', $y), %{ $data[$i] } };
190 }
191
192 my $base_y = $PT + $plot_h;
193 my $fill_d = "M $points[0]->{x} $base_y";
194 $fill_d .= " L $_->{x} $_->{y}" for @points;
195 $fill_d .= " L $points[-1]->{x} $base_y Z";
196
197 my $stroke_d = "M $points[0]->{x} $points[0]->{y}";
198 $stroke_d .= " L $points[$_]->{x} $points[$_]->{y}" for 1..$#points;
199
200 my $axis_html = '';
201 my @idx = (0, int($n*0.25), int($n*0.5), int($n*0.75), $n - 1);
202 my %seen;
203 for my $i (@idx) {
204 next if $seen{$i}++;
205 my $p = $points[$i];
206 my $ty = $H - 8;
207 $axis_html .= qq~<text x="$p->{x}" y="$ty" text-anchor="middle" fill="rgba(255,255,255,0.40)" font-size="10" font-family="ui-monospace,monospace">$p->{label}</text>~;
208 }
209
210 my $hover_html = '';
211 my $col_w = $n > 1 ? $plot_w / ($n - 1) : $plot_w;
212 for my $i (0..$#points) {
213 my $p = $points[$i];
214 my $hx = sprintf('%.2f', $PL + $i*$col_w - $col_w/2);
215 my $hw = sprintf('%.2f', $col_w);
216 $hover_html .= qq~<rect class="ac-hot" x="$hx" y="$PT" width="$hw" height="$plot_h" fill="transparent" data-x="$p->{x}" data-y="$p->{y}" data-iso="$p->{iso}" data-label="$p->{label}" data-value="$p->{value}"></rect>~;
217 }
218
219 my $ymax_y = $PT - 4;
220 my $base_lbl_y = $H - 8;
221 return qq~
222<svg class="ac-svg" viewBox="0 0 $W $H" preserveAspectRatio="none" style="width:100%;height:200px;display:block;overflow:visible">
223 <defs>
224 <linearGradient id="ac-grad-$id" x1="0" x2="0" y1="0" y2="1">
225 <stop offset="0%" stop-color="$color" stop-opacity="0.55"/>
226 <stop offset="100%" stop-color="$color" stop-opacity="0"/>
227 </linearGradient>
228 </defs>
229 <line x1="$PL" y1="$base_y" x2="${\ ($W - $PR)}" y2="$base_y" stroke="rgba(255,255,255,0.12)" stroke-width="1"/>
230 <path d="$fill_d" fill="url(#ac-grad-$id)"/>
231 <path d="$stroke_d" fill="none" stroke="$color" stroke-width="2" stroke-linejoin="round" stroke-linecap="round"/>
232 <line class="ac-guide" x1="0" y1="$PT" x2="0" y2="$base_y" stroke="$color" stroke-width="1" stroke-dasharray="3 3" opacity="0"/>
233 <circle class="ac-dot" cx="0" cy="0" r="4" fill="#fff" stroke="$color" stroke-width="2" opacity="0"/>
234 $axis_html
235 <text x="${\ ($PL - 6)}" y="$ymax_y" text-anchor="end" fill="rgba(255,255,255,0.45)" font-size="10" font-family="ui-monospace,monospace">$y_max</text>
236 $hover_html
237</svg>~;
238}
239
240my $active_tid = $form->{thread_id} || 0;
241$active_tid =~ s/[^0-9]//g;
242my $active_thread;
243my $messages = [];
244if ($active_tid) {
245 $active_thread = $sup->load_thread($db, $dbh, $DB, $active_tid);
246 if ($active_thread) {
247 $messages = $sup->load_messages($db, $dbh, $DB, $active_tid);
248 # Viewing zeros the admin-side unread counter for this thread.
249 $sup->mark_read($db, $dbh, $DB, $active_tid, 'admin') if $active_thread->{admin_unread};
250 }
251}
252
253# Customer email + name for the active thread header.
254my ($active_cust_name, $active_cust_email);
255if ($active_thread) {
256 my $cu = $db->db_readwrite($dbh,
257 qq~SELECT display_name, email FROM `${DB}`.users WHERE id='$active_thread->{customer_user_id}' LIMIT 1~,
258 $ENV{SCRIPT_NAME}, __LINE__);
259 $active_cust_name = $cu->{display_name} if $cu;
260 $active_cust_email = $cu->{email} if $cu;
261}
262
263$db->db_disconnect($dbh);
264
265# ---- Build template vars -------------------------------------------
266my @thread_tiles;
267foreach my $t (@$threads) {
268 my $is_active = ($active_tid && $t->{id} eq $active_tid) ? 1 : 0;
269 push @thread_tiles, {
270 id => $t->{id},
271 subject => _h($t->{subject}),
272 customer_name => _h($t->{customer_name} || $t->{customer_email} || 'Anonymous'),
273 customer_email=> _h($t->{customer_email}),
274 status => $t->{status},
275 status_label => _status_label($t->{status}),
276 status_class => _status_class($t->{status}),
277 priority => $t->{priority},
278 unread => $t->{admin_unread} || 0,
279 has_unread => ($t->{admin_unread} && !$is_active) ? 1 : 0,
280 is_active => $is_active,
281 last_at => _humanize_ts($t->{last_message_at}),
282 };
283}
284
285# Fetch attachments for the active thread in one pass so we can zip
286# them into per-message lists without N+1 lookups. Guarded against
287# the table not yet existing (pre-migration deploys).
288my %atts_by_msg;
289if ($active_thread) {
290 my $have_at = $db->db_readwrite($dbh, qq~
291 SELECT COUNT(*) AS n FROM information_schema.tables
292 WHERE table_schema='$DB' AND table_name='support_attachments'
293 ~, $ENV{SCRIPT_NAME}, __LINE__);
294 if ($have_at && $have_at->{n}) {
295 my @arows = $db->db_readwrite_multiple($dbh, qq~
296 SELECT id, message_id, filename
297 FROM `${DB}`.support_attachments
298 WHERE thread_id='$active_tid'
299 ORDER BY uploaded_at ASC
300 ~, $ENV{SCRIPT_NAME}, __LINE__);
301 for my $a (@arows) {
302 push @{ $atts_by_msg{ $a->{message_id} } }, {
303 id => $a->{id},
304 url => "/support_file.cgi?id=$a->{id}",
305 name => $a->{filename},
306 };
307 }
308 }
309}
310
311my @message_rows;
312if ($active_thread) {
313 foreach my $m (@$messages) {
314 my $role = $m->{sender_role};
315 my $atts = $atts_by_msg{ $m->{id} } || [];
316 push @message_rows, {
317 id => $m->{id},
318 body_html => _body_html($m->{body}),
319 ts => _humanize_ts($m->{created_at}),
320 ts_iso => $m->{created_at},
321 sender_name => _h($m->{sender_name} || ($role eq 'system' ? 'ShopCart' : 'Anonymous')),
322 sender_initial => uc(substr($m->{sender_name} || ($role eq 'admin' ? 'W' : 'C'), 0, 1)),
323 is_customer => ($role eq 'customer') ? 1 : 0,
324 is_admin => ($role eq 'admin') ? 1 : 0,
325 is_system => ($role eq 'system') ? 1 : 0,
326 attachments => $atts,
327 has_attach => scalar(@$atts) ? 1 : 0,
328 };
329 }
330}
331
332my $tvars = {
333 tab_inbox => ($tab eq 'inbox') ? 1 : 0,
334 tab_reports => ($tab eq 'reports') ? 1 : 0,
335
336 filter => $filter,
337 threads => \@thread_tiles,
338 has_threads => scalar(@thread_tiles) ? 1 : 0,
339 thread_count => scalar(@thread_tiles),
340
341 # Reports tab metrics
342 rep_total => $rep_total || 0,
343 rep_open => $rep_open || 0,
344 rep_waiting => $rep_waiting || 0,
345 rep_resolved_7d => $rep_resolved_7d || 0,
346 rep_resolved_30d => $rep_resolved_30d || 0,
347 rep_new_7d => $rep_new_7d || 0,
348 rep_new_30d => $rep_new_30d || 0,
349 rep_avg_resp_min => $rep_avg_resp_min || 0,
350 rep_avg_resp_hours=> ($rep_avg_resp_min && $rep_avg_resp_min >= 60) ? sprintf('%.1f', $rep_avg_resp_min/60) : 0,
351 rep_avg_resp_is_minutes => ($rep_avg_resp_min && $rep_avg_resp_min < 60) ? 1 : 0,
352 rep_avg_resp_is_hours => ($rep_avg_resp_min && $rep_avg_resp_min >= 60) ? 1 : 0,
353 rep_avg_resp_unknown => ($rep_avg_resp_min) ? 0 : 1,
354 chart_new_html => $chart_new_html,
355 chart_resolved_html => $chart_resolved_html,
356 has_charts => ($chart_new_html ne '') ? 1 : 0,
357
358 has_active => $active_thread ? 1 : 0,
359 active_id => $active_thread ? $active_thread->{id} : 0,
360 active_subject => $active_thread ? _h($active_thread->{subject}) : '',
361 active_status => $active_thread ? _status_label($active_thread->{status}) : '',
362 active_status_raw => $active_thread ? $active_thread->{status} : '',
363 active_status_class => $active_thread ? _status_class($active_thread->{status}) : '',
364 active_priority => $active_thread ? $active_thread->{priority} : '',
365 active_cust_name => _h($active_cust_name || ''),
366 active_cust_email => _h($active_cust_email || ''),
367 messages => \@message_rows,
368 has_messages => scalar(@message_rows) ? 1 : 0,
369 last_message_id => scalar(@message_rows) ? $message_rows[-1]->{id} : 0,
370};
371
372my $body = join('', $tfile->template('shopcart_admin_messages.html', $tvars, $userinfo));
373
374print "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";
375
376$wrap->render({
377 userinfo => $userinfo,
378 page_key => 'admin_messages',
379 title => 'Support inbox',
380 body => $body,
381});
382
383exit;
384
385#======================================================================
386sub _h {
387 my $s = shift; $s = '' unless defined $s;
388 $s =~ s/&/&amp;/g; $s =~ s/</&lt;/g; $s =~ s/>/&gt;/g;
389 $s =~ s/"/&quot;/g; $s =~ s/'/&#39;/g;
390 return $s;
391}
392sub _body_html {
393 my $s = shift; $s = '' unless defined $s;
394 $s = _h($s);
395 $s =~ s/\$/\\\$/g;
396 $s =~ s/\@/\\\@/g;
397 $s =~ s/\r//g;
398 $s =~ s/\n/<br>/g;
399 return $s;
400}
401sub _status_label {
402 my $s = shift || '';
403 return 'Open' if $s eq 'open';
404 return 'Needs reply' if $s eq 'waiting_admin';
405 return 'Waiting on buyer' if $s eq 'waiting_customer';
406 return 'Resolved' if $s eq 'resolved';
407 return 'Closed' if $s eq 'closed';
408 return $s;
409}
410sub _status_class {
411 my $s = shift || '';
412 return 'st-needs' if $s eq 'waiting_admin';
413 return 'st-open' if $s eq 'open';
414 return 'st-waiting' if $s eq 'waiting_customer';
415 return 'st-resolved' if $s eq 'resolved';
416 return 'st-closed' if $s eq 'closed';
417 return 'st-open';
418}
419sub _humanize_ts {
420 my $t = shift; $t = '' unless defined $t;
421 return '' unless $t =~ /^(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})/;
422 my ($y, $mo, $d, $hh, $mm, $ss) = ($1, $2, $3, $4, $5, $6);
423 require Time::Local;
424 my $then = eval { Time::Local::timelocal($ss, $mm, $hh, $d, $mo - 1, $y - 1900) } || 0;
425 my $diff = time - $then;
426 my $human;
427 if ($diff < 60) { $human = 'Just now'; }
428 elsif ($diff < 3600) { $human = int($diff/60) . 'm ago'; }
429 elsif ($diff < 86400) { $human = int($diff/3600) . 'h ago'; }
430 elsif ($diff < 172800) { $human = 'Yesterday'; }
431 elsif ($diff < 604800) { $human = int($diff/86400) . 'd ago'; }
432 else {
433 my @mn = ('','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');
434 $human = "$mn[int($mo)] $d";
435 }
436 return $human unless $then;
437 return qq~<span class="ts" data-ts="$then" data-fmt="relative">$human</span>~;
438}