Diff -- /var/www/vhosts/3dshawn.com/affiliate.3dshawn.com/admin_chat.cgi

O Operator
Diff

/var/www/vhosts/3dshawn.com/affiliate.3dshawn.com/admin_chat.cgi

added on local at 2026-07-11 18:38:28

Added
+421
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to 44a24c851a76
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# WebSTLs - Admin Chat
4#
5# Dedicated chat console: list of conversations on the left, thread on
6# the right, reply box at the bottom. Online/offline toggle controls
7# whether the chat widget appears on visitor-facing storefronts.
8#======================================================================
9use strict;
10use warnings;
11
12use lib '/var/www/vhosts/3dshawn.com/affiliate.3dshawn.com';
13use CGI;
14use MODS::Template;
15use MODS::DBConnect;
16use MODS::Login;
17use MODS::AffSoft::Config;
18use MODS::AffSoft::Wrapper;
19use MODS::AffSoft::Admin;
20use MODS::AffSoft::Tracking;
21
22my $q = CGI->new;
23my $form = $q->Vars;
24my $auth = MODS::Login->new;
25my $wrap = MODS::AffSoft::Wrapper->new;
26my $tfile = MODS::Template->new;
27my $db = MODS::DBConnect->new;
28my $cfg = MODS::AffSoft::Config->new;
29my $admin = MODS::AffSoft::Admin->new;
30my $tr = MODS::AffSoft::Tracking->new;
31my $DB = $cfg->settings('database_name');
32
33$|=1;
34my $userinfo = $auth->login_verify();
35if (!$userinfo) {
36 print "Status: 302 Found\nLocation: /login.cgi\n\n";
37 exit;
38}
39$admin->require_admin($userinfo);
40require MODS::AffSoft::Permissions;
41MODS::AffSoft::Permissions->new->require_feature($userinfo, 'admin_chat');
42
43my $tutorial_mode = ($form->{tutorial} && $form->{tutorial} eq '1') ? 1 : 0;
44
45my $dbh = $db->db_connect();
46my $schema_ready = $tr->schema_ready($db, $dbh, $DB);
47
48my $admin_online = $schema_ready ? $tr->admin_online($db, $dbh, $DB) : 0;
49
50# View filter + search
51my %ok_view = (active=>1, closed=>1, archived=>1, all=>1);
52my $view = $form->{view} || 'active';
53$view = 'active' unless $ok_view{$view};
54my $search = defined $form->{q} ? $form->{q} : '';
55$search =~ s/^\s+|\s+$//g;
56my $search_trim = substr($search, 0, 100);
57
58# Conversation list
59my @chats_raw = $schema_ready ? $tr->admin_chat_list($db, $dbh, $DB,
60 filter => $view, search => $search_trim, limit => 100) : ();
61my $counts = $schema_ready ? $tr->chat_counts_by_status($db, $dbh, $DB)
62 : { active=>0, closed=>0, archived=>0, all=>0 };
63
64sub _h { my $s = shift; $s //= ''; $s =~ s/&/&amp;/g; $s =~ s/</&lt;/g; $s =~ s/>/&gt;/g; $s =~ s/"/&quot;/g; return $s; }
65# Epoch seconds for a MySQL DATETIME (or DATE). Used to feed data-ts= on
66# the tzLocalize spans so the viewer sees their own timezone.
67sub _ach_epoch {
68 my $s = shift; return 0 unless defined $s && length $s;
69 if ($s =~ /^(\d{4})-(\d{2})-(\d{2})[T ](\d{2}):(\d{2}):(\d{2})/) {
70 require Time::Local;
71 return eval { Time::Local::timelocal($6, $5, $4, $3, $2 - 1, $1 - 1900) } || 0;
72 }
73 if ($s =~ /^(\d{4})-(\d{2})-(\d{2})$/) {
74 require Time::Local;
75 return eval { Time::Local::timelocal(0, 0, 0, $3, $2 - 1, $1 - 1900) } || 0;
76 }
77 return 0;
78}
79sub _qs { my $s = shift; $s //= ''; $s =~ s/([^A-Za-z0-9\-_.~])/sprintf('%%%02X', ord($1))/eg; return $s; }
80
81# Build the ?view=X&q=... fragment so list links keep filter state.
82my $view_qs = '&view=' . $view;
83$view_qs .= '&q=' . _qs($search_trim) if length $search_trim;
84
85my @chats;
86my $selected_chat_id = $form->{chat} || 0;
87$selected_chat_id =~ s/[^0-9]//g;
88
89foreach my $c (@chats_raw) {
90 my $is_selected = ($selected_chat_id && $c->{chat_id} eq $selected_chat_id) ? 1 : 0;
91 $selected_chat_id ||= $c->{chat_id} if !$selected_chat_id;
92
93 # Pull session fingerprint for classification.
94 my $full = $db->db_readwrite($dbh, qq~
95 SELECT id, buyer_account_id FROM `${DB}`.tracking_sessions WHERE id='$c->{session_id}' LIMIT 1
96 ~, $ENV{SCRIPT_NAME}, __LINE__);
97 my $cls = $tr->classify_session($db, $dbh, $DB, $full);
98
99 my $chat_status = $c->{status} || 'open';
100 my $idle = $c->{idle_seconds} || 0;
101 push @chats, {
102 chat_id => $c->{chat_id},
103 session_id => $c->{session_id},
104 label => _h($c->{visitor_label} || ('Visitor #' . $c->{session_id})),
105 platform_meta => _h(($c->{os_name} || '') . ' / ' . ($c->{browser_name} || '')),
106 device => _h($c->{device_type} || ''),
107 current_page => _h($c->{current_page_path} || ''),
108 last_message_at => $c->{last_message_at} || '',
109 last_message_at_epoch => _ach_epoch($c->{last_message_at}),
110 last_body => _h(substr($c->{last_body} || '', 0, 60)),
111 last_from => $c->{last_from} || '',
112 unread => $c->{unread_for_admin} || 0,
113 href => '/admin_chat.cgi?chat=' . $c->{chat_id} . $view_qs,
114 is_selected => $is_selected,
115 # Buyer classification (guest/lead/customer)
116 status => $cls->{status},
117 status_label => $cls->{status_label},
118 is_customer => $cls->{status} eq 'customer' ? 1 : 0,
119 is_lead => $cls->{status} eq 'lead' ? 1 : 0,
120 is_guest => $cls->{status} eq 'guest' ? 1 : 0,
121 email => _h($cls->{email} || ''),
122 has_email => $cls->{email} ? 1 : 0,
123 total_orders => $cls->{total_orders},
124 ltv_display => '$' . sprintf('%.2f', ($cls->{ltv_cents} || 0) / 100),
125 # Conversation status (open/closed/archived) for visual treatment
126 chat_status => $chat_status,
127 is_open => $chat_status eq 'open' ? 1 : 0,
128 is_closed => $chat_status eq 'closed' ? 1 : 0,
129 is_archived => $chat_status eq 'archived' ? 1 : 0,
130 visitor_online => ($c->{is_online} && $idle < 60) ? 1 : 0,
131 awaiting_reply => (($c->{last_from} || '') eq 'visitor' && $c->{unread_for_admin}) ? 1 : 0,
132 };
133}
134
135# Selected thread + visitor details
136my @thread_rows;
137my $thread = {};
138if ($schema_ready && $selected_chat_id) {
139 # Mark read on view
140 $tr->mark_chat_read($db, $dbh, $DB, $selected_chat_id, 'admin');
141
142 my @msgs = $tr->chat_messages($db, $dbh, $DB, $selected_chat_id, 200);
143 foreach my $m (@msgs) {
144 # HTML-escape first, then linkify URLs. Order matters: escaping
145 # an already-built <a> tag would mangle it; URLs themselves
146 # don't contain HTML-special chars normally, and any that do
147 # (& in querystrings) are correctly &amp;-escaped inside the
148 # href attribute, which is what HTML attribute parsing expects.
149 my $safe = _h($m->{body});
150 $safe =~ s{(https?://[^\s<]+)}{<a href="$1" target="_blank" rel="noopener" style="color:#60a5fa;text-decoration:underline">$1</a>}g;
151 push @thread_rows, {
152 id => $m->{id},
153 from_role => $m->{from_role},
154 body => $safe,
155 sent_at => $m->{sent_at} || '',
156 sent_at_epoch => _ach_epoch($m->{sent_at}),
157 is_admin => ($m->{from_role} eq 'admin') ? 1 : 0,
158 is_visitor => ($m->{from_role} eq 'visitor') ? 1 : 0,
159 is_system => ($m->{from_role} eq 'system') ? 1 : 0,
160 };
161 }
162
163 # Visitor info for the right rail + chat row for status-driven actions
164 my $sess = $db->db_readwrite($dbh, qq~
165 SELECT s.*, c.status AS chat_status FROM `${DB}`.support_chats c
166 JOIN `${DB}`.tracking_sessions s ON s.id = c.session_id
167 WHERE c.id='$selected_chat_id' LIMIT 1
168 ~, $ENV{SCRIPT_NAME}, __LINE__);
169 if ($sess && $sess->{id}) {
170 my $cls = $tr->classify_session($db, $dbh, $DB, $sess);
171 my $chat_status = $sess->{chat_status} || 'open';
172 $thread = {
173 session_id => $sess->{id},
174 label => _h($sess->{visitor_label} || ''),
175 os => _h(($sess->{os_name} || '') . ' ' . ($sess->{os_version} || '')),
176 browser => _h(($sess->{browser_name} || '') . ' ' . ($sess->{browser_version} || '')),
177 device => _h($sess->{device_type} || ''),
178 screen => ($sess->{screen_width} && $sess->{screen_height}) ? ($sess->{screen_width} . 'x' . $sess->{screen_height}) : '',
179 language => _h($sess->{language} || ''),
180 timezone => _h($sess->{timezone} || ''),
181 current_page => _h($sess->{current_page_path} || ''),
182 status => $cls->{status},
183 status_label => $cls->{status_label},
184 is_customer => $cls->{status} eq 'customer' ? 1 : 0,
185 is_lead => $cls->{status} eq 'lead' ? 1 : 0,
186 is_guest => $cls->{status} eq 'guest' ? 1 : 0,
187 email => _h($cls->{email} || ''),
188 has_email => $cls->{email} ? 1 : 0,
189 total_orders => $cls->{total_orders},
190 ltv_display => '$' . sprintf('%.2f', ($cls->{ltv_cents} || 0) / 100),
191 # Conversation status drives which header buttons render.
192 chat_status => $chat_status,
193 is_open => $chat_status eq 'open' ? 1 : 0,
194 is_closed => $chat_status eq 'closed' ? 1 : 0,
195 is_archived => $chat_status eq 'archived' ? 1 : 0,
196 };
197 }
198}
199
200# ---- Concluded-chats summary (last 30 days + today) -----------------
201# Counts every chat that hit status='closed' OR 'archived' (both count
202# as "resolved" -- archive is just an old close). Today's bar always
203# renders even when zero closures so the trend reads honestly. Also
204# pulls the individual chats so the bar drilldown overlay can list
205# them without a follow-up request.
206my @resolved_raw = $schema_ready ? $tr->chats_closed_by_day($db, $dbh, $DB, 30) : ();
207my @resolved_list = $schema_ready ? $tr->chats_closed_list($db, $dbh, $DB, 30) : ();
208my %resolved_by_d = map { $_->{d} => $_ } @resolved_raw;
209my %chats_by_d;
210foreach my $cc (@resolved_list) {
211 my $d = $cc->{d} || '';
212 push @{ $chats_by_d{$d} }, {
213 id => $cc->{id} + 0,
214 hm => $cc->{hm} || '',
215 label => $cc->{label} || ('Visitor #' . $cc->{id}),
216 };
217}
218
219# Build the trailing 30 days from oldest -> newest so the chart reads
220# left-to-right as time-moves-forward.
221my @resolved_days;
222my $resolved_max = 0;
223my $today_count = 0;
224my @short_months = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
225# Today's date as a reference point. We walk back 29 days and stop at today.
226my ($t_sec,$t_min,$t_hour,$t_mday,$t_mon,$t_year) = localtime(time);
227require POSIX;
228my $today_epoch = POSIX::mktime(0,0,12,$t_mday,$t_mon,$t_year);
229for (my $i = 29; $i >= 0; $i--) {
230 my $epoch = $today_epoch - ($i * 86400);
231 my @t = localtime($epoch);
232 my $d_iso = sprintf('%04d-%02d-%02d', $t[5]+1900, $t[4]+1, $t[3]);
233 my $d_short = sprintf('%s %d', $short_months[$t[4]], $t[3]); # "May 17"
234 my $n = ($resolved_by_d{$d_iso} && $resolved_by_d{$d_iso}->{n}) || 0;
235 $resolved_max = $n if $n > $resolved_max;
236 $today_count = $n if $i == 0;
237 # Serialise the per-day chat list so the drilldown can read it
238 # from a data-* attribute. Embeds visitor label + close time +
239 # link to the conversation. Strict JSON (double-quoted keys/values
240 # with JSON-escaping) so the JS can JSON.parse it cleanly.
241 my $chats_for_day = $chats_by_d{$d_iso} || [];
242 my @items_json;
243 for my $cc (@$chats_for_day) {
244 my $lbl = $cc->{label};
245 # JSON-escape: backslash, double-quote, control chars
246 $lbl =~ s/\\/\\\\/g;
247 $lbl =~ s/"/\\"/g;
248 $lbl =~ s/\r/\\r/g;
249 $lbl =~ s/\n/\\n/g;
250 $lbl =~ s/\t/\\t/g;
251 my $hm = $cc->{hm} || '';
252 $hm =~ s/[^0-9:]//g;
253 my $cid = $cc->{id} + 0;
254 # Build with sprintf instead of qq~ so a literal '~' in the
255 # visitor label cannot accidentally terminate the heredoc.
256 push @items_json, sprintf('{"id":%d,"hm":"%s","label":"%s"}', $cid, $hm, $lbl);
257 }
258 my $chats_json = '[' . join(',', @items_json) . ']';
259 # HTML-attribute escape the JSON so it's safe inside data-chats='...'.
260 # Single-quote attribute means we only need to escape the apostrophe.
261 $chats_json =~ s/&/&amp;/g;
262 $chats_json =~ s/'/&#39;/g;
263 push @resolved_days, {
264 d_iso => $d_iso,
265 d_short => $d_short,
266 d_label => $d_short,
267 n => $n,
268 is_current => $i == 0 ? 1 : 0,
269 chats_json => $chats_json,
270 };
271}
272# Add a relative-height percent for the bar visual.
273foreach my $r (@resolved_days) {
274 $r->{bar_pct} = $resolved_max ? sprintf('%.1f', ($r->{n} / $resolved_max) * 100) : '0.0';
275}
276my $resolved_total_30d = 0;
277$resolved_total_30d += $_->{n} for @resolved_days;
278# Preserve the old var names so unrelated template branches keep working.
279my $mtd_resolved = $today_count;
280my @resolved_months = @resolved_days;
281my $resolved_total_6mo = $resolved_total_30d;
282
283$db->db_disconnect($dbh);
284
285my $tvars = {
286 user_id => $userinfo->{user_id},
287 schema_ready => $schema_ready ? 1 : 0,
288 schema_missing => $schema_ready ? 0 : 1,
289 admin_online => $admin_online,
290
291 # Monthly resolved-chats summary
292 resolved_months => \@resolved_months,
293 has_resolved_months => scalar(@resolved_months) ? 1 : 0,
294 resolved_total_6mo => $resolved_total_6mo,
295 resolved_mtd => $mtd_resolved,
296 chats => \@chats,
297 has_chats => scalar(@chats) ? 1 : 0,
298 chat_count => scalar(@chats),
299 selected_chat_id => $selected_chat_id || 0,
300 has_selected => $selected_chat_id ? 1 : 0,
301 thread => $thread,
302 has_thread => %$thread ? 1 : 0,
303 thread_rows => \@thread_rows,
304 has_thread_rows => scalar(@thread_rows) ? 1 : 0,
305 tutorial_mode => $tutorial_mode,
306 not_tutorial_mode=> $tutorial_mode ? 0 : 1,
307 # Filter + search state
308 view => $view,
309 is_view_active => $view eq 'active' ? 1 : 0,
310 is_view_closed => $view eq 'closed' ? 1 : 0,
311 is_view_archived => $view eq 'archived' ? 1 : 0,
312 is_view_all => $view eq 'all' ? 1 : 0,
313 search_q => _h($search_trim),
314 has_search => length $search_trim ? 1 : 0,
315 count_active => $counts->{active},
316 count_closed => $counts->{closed},
317 count_archived => $counts->{archived},
318 count_all => $counts->{all},
319};
320
321# Tutorial sample
322if ($tutorial_mode) {
323 $tvars->{admin_online} = 1;
324 $tvars->{count_active} = 3;
325 $tvars->{count_closed} = 12;
326 $tvars->{count_archived} = 48;
327 $tvars->{count_all} = 63;
328 # Sample daily resolved-chats trend (30 trailing days). Numbers
329 # picked to look organic (busier weekdays, quieter weekends) without
330 # spiking off-chart so the bar visual reads naturally in tutorial.
331 my @sample_pattern = (4,6,5,7,3,2,1, 5,8,9,7,6,2,3, 6,7,10,8,9,4,2, 5,9,11,8,7,3,1, 6,12);
332 my @sample_days;
333 my @short_months_tut = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
334 my ($s_sec,$s_min,$s_hour,$s_mday,$s_mon,$s_year) = localtime(time);
335 require POSIX;
336 my $s_today_epoch = POSIX::mktime(0,0,12,$s_mday,$s_mon,$s_year);
337 my $sample_total = 0;
338 my $sample_today = 0;
339 my $sample_max = 0;
340 for (my $i = 29; $i >= 0; $i--) {
341 my $n = $sample_pattern[29 - $i] || 0;
342 my @t = localtime($s_today_epoch - ($i * 86400));
343 my $d_iso = sprintf('%04d-%02d-%02d', $t[5]+1900, $t[4]+1, $t[3]);
344 my $d_short = sprintf('%s %d', $short_months_tut[$t[4]], $t[3]);
345 $sample_total += $n;
346 $sample_today = $n if $i == 0;
347 $sample_max = $n if $n > $sample_max;
348 push @sample_days, {
349 d_iso => $d_iso,
350 d_short => $d_short,
351 d_label => $d_short,
352 n => $n,
353 is_current => $i == 0 ? 1 : 0,
354 chats_json => '[]',
355 };
356 }
357 foreach my $m (@sample_days) {
358 $m->{bar_pct} = $sample_max ? sprintf('%.1f', ($m->{n} / $sample_max) * 100) : '0.0';
359 }
360 $tvars->{resolved_months} = \@sample_days;
361 $tvars->{has_resolved_months} = 1;
362 $tvars->{resolved_total_6mo} = $sample_total;
363 $tvars->{resolved_mtd} = $sample_today;
364 $tvars->{chats} = [
365 { chat_id=>1, session_id=>842, label=>'Visitor #842', platform_meta=>'macOS / Safari', device=>'desktop',
366 current_page=>'/store.cgi?id=1', last_message_at=>'2 min ago',
367 last_body=>'Do these prints come with supports?', last_from=>'visitor',
368 unread=>2, href=>'/admin_chat.cgi?tutorial=1&chat=1', is_selected=>1,
369 status=>'customer', status_label=>'Customer', is_customer=>1, is_lead=>0, is_guest=>0,
370 email=>'rune.caster@example.com', has_email=>1, total_orders=>4, ltv_display=>'$96.00',
371 chat_status=>'open', is_open=>1, is_closed=>0, is_archived=>0, visitor_online=>1, awaiting_reply=>1 },
372 { chat_id=>2, session_id=>841, label=>'Visitor #841', platform_meta=>'Windows / Chrome', device=>'desktop',
373 current_page=>'/store.cgi?id=1&page=catalog', last_message_at=>'18 min ago',
374 last_body=>'Thanks, that worked!', last_from=>'visitor',
375 unread=>0, href=>'/admin_chat.cgi?tutorial=1&chat=2', is_selected=>0,
376 status=>'guest', status_label=>'Guest', is_customer=>0, is_lead=>0, is_guest=>1,
377 email=>'', has_email=>0, total_orders=>0, ltv_display=>'$0.00',
378 chat_status=>'open', is_open=>1, is_closed=>0, is_archived=>0, visitor_online=>0, awaiting_reply=>0 },
379 { chat_id=>3, session_id=>840, label=>'Visitor #840', platform_meta=>'iOS / Safari', device=>'mobile',
380 current_page=>'/store.cgi?id=1&page=about', last_message_at=>'1 h ago',
381 last_body=>'Glad you could help!', last_from=>'admin',
382 unread=>0, href=>'/admin_chat.cgi?tutorial=1&chat=3', is_selected=>0,
383 status=>'lead', status_label=>'Lead', is_customer=>0, is_lead=>1, is_guest=>0,
384 email=>'bench.hammer@example.com', has_email=>1, total_orders=>0, ltv_display=>'$0.00',
385 chat_status=>'open', is_open=>1, is_closed=>0, is_archived=>0, visitor_online=>0, awaiting_reply=>0 },
386 ];
387 $tvars->{has_chats} = 1;
388 $tvars->{chat_count} = 3;
389 $tvars->{selected_chat_id} = 1;
390 $tvars->{has_selected} = 1;
391 $tvars->{thread} = {
392 session_id=>842, label=>'Visitor #842',
393 os=>'macOS 14.4', browser=>'Safari 17.4', device=>'desktop',
394 screen=>'2560x1440', language=>'en-US', timezone=>'America/Denver',
395 current_page=>'/store.cgi?id=1',
396 status=>'customer', status_label=>'Customer', is_customer=>1, is_lead=>0, is_guest=>0,
397 email=>'rune.caster@example.com', has_email=>1, total_orders=>4, ltv_display=>'$96.00',
398 chat_status=>'open', is_open=>1, is_closed=>0, is_archived=>0,
399 };
400 $tvars->{has_thread} = 1;
401 $tvars->{thread_rows} = [
402 { id=>1, from_role=>'visitor', body=>'Hi! Quick question about the Dragon Lord MK-II.', sent_at=>'14:18', is_admin=>0, is_visitor=>1, is_system=>0 },
403 { id=>2, from_role=>'admin', body=>'Hey! Happy to help -- what is the question?', sent_at=>'14:19', is_admin=>1, is_visitor=>0, is_system=>0 },
404 { id=>3, from_role=>'visitor', body=>'Do these prints come with supports?', sent_at=>'14:20', is_admin=>0, is_visitor=>1, is_system=>0 },
405 { id=>4, from_role=>'visitor', body=>'And what layer height did you test at?', sent_at=>'14:20', is_admin=>0, is_visitor=>1, is_system=>0 },
406 ];
407 $tvars->{has_thread_rows} = 1;
408 $tvars->{schema_ready} = 1;
409 $tvars->{schema_missing} = 0;
410}
411
412print "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";
413
414my $body = join('', $tfile->template('webstls_admin_chat.html', $tvars, $userinfo));
415
416$wrap->render({
417 userinfo => $userinfo,
418 page_key => 'admin_chat',
419 title => 'Admin &middot; Chat',
420 body => $body,
421});