Diff -- /var/www/vhosts/3dshawn.com/ptmatrix.3dshawn.com/support.cgi

O Operator
Diff

/var/www/vhosts/3dshawn.com/ptmatrix.3dshawn.com/support.cgi

added on local at 2026-07-11 18:33:20

Added
+575
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to 75414c41029f
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##################################################################################################################################
4# ______ ____ ____ ______ ______ ____ ___ __ ___ ______ _ __ ____ ____ __ __ _ __ _____ ____
5# / ____// __ \ / __ \ / ____/ / ____// __ \ / | / |/ // ____/| | / // __ \ / __ \ / //_/ | | / /|__ / / __ \
6# / / / / / // / / // __/ / /_ / /_/ // /| | / /|_/ // __/ | | /| / // / / // /_/ // ,< | | / / /_ < / / / /
7# / /___ / /_/ // /_/ // /___ / __/ / _, _// ___ | / / / // /___ | |/ |/ // /_/ // _, _// /| | | |/ / ___/ /_ / /_/ /
8# \____/ \____//_____//_____/ /_/ /_/ |_|/_/ |_|/_/ /_//_____/ |__/|__/ \____//_/ |_|/_/ |_| |___/ /____/(_)\____/
9#
10# - Written by: Shawn Pickering
11# - shawn@shawnpickering.com
12##################################################################################################################################
13use strict;
14use lib '/var/www/vhosts/3dshawn.com/ptmatrix.3dshawn.com';
15
16
17#############################################################
18## Variables
19#############################################################
20
21
22
23############################################################
24# Modules
25############################################################
26use CGI; my $query = new CGI; my $form = $query->Vars;
27use MODS::Login; my $login = new MODS::Login;
28use MODS::PTMatrix::Urls; my $getlist = new MODS::PTMatrix::Urls; my $url = $getlist->urls();
29use MODS::PTMatrix::Wrapper; my $load = new MODS::PTMatrix::Wrapper;
30use MODS::DBConnect; my $db = new MODS::DBConnect;
31use MODS::PTMatrix::Config; my $config = new MODS::PTMatrix::Config; my $database_name = $config->settings('database_name');
32use MODS::PTMatrix::PM;
33
34
35
36#############################################################
37## Get form data
38#############################################################
39#Preserve raw body/comment/reply text for the thread reply + CSAT paths
40my %rawform = %$form;
41foreach my $line(keys %$form){
42 next if $line eq 'body' || $line eq 'comment';
43 $form->{$line} =~ s/[^!-~\s]//g;
44 $form->{$line} = quotemeta("$form->{$line}");
45}
46
47
48
49############################################################
50# Program Controls
51############################################################
52$|=1;
53my $userinfo = $login->login_verify();
54my $entry = ($ENV{SCRIPT_NAME} || '') =~ m{/([^/]+)\.cgi$} ? $1 : 'support';
55
56if(!$userinfo){
57 if($entry eq 'support_thread'){print qq~Status: 302 Found\nLocation: $url->{login}\n\n~; exit;}
58 print qq~Content-type:text/html; Cache-Control:no-cache;\n\n<script>window.location="$url->{login}";</script>~;
59 exit;
60}
61
62if($entry eq 'support_thread'){
63 my $tid = MODS::PTMatrix::PM::safe_int($form->{id});
64 if(!$tid){print qq~Status: 302 Found\nLocation: /support.cgi\n\n~; exit;}
65 &support_thread($tid);
66}elsif($entry eq 'admin_support'){
67 if(!($userinfo->{is_super_admin} || ($userinfo->{role} || '') eq 'company_admin')){
68 print qq~Status: 403 Forbidden\nContent-Type: text/plain\n\nAdmin only.\n~;
69 exit;
70 }
71 print qq~Content-Type: text/html; charset=utf-8\nCache-Control: no-store\n\n~;
72 &admin_support_queue;
73}else{
74 print qq~Content-type:text/html; Cache-Control:no-cache;\n\n~;
75 if(($ENV{REQUEST_METHOD} || '') eq 'POST' && ($form->{act} || '') eq 'new'){&open_thread;}
76 else{&support_inbox;}
77}
78
79
80############################################################
81# Subroutines
82############################################################
83sub open_thread{
84 my $uid = MODS::PTMatrix::PM::get_user_id($userinfo);
85 my $cid = MODS::PTMatrix::PM::safe_int($userinfo->{company_id} || 0);
86
87 my $subject = $rawform{subject} || ''; $subject =~ s/^\s+|\s+$//g;
88 my $msg = $rawform{body} || ''; $msg =~ s/^\s+|\s+$//g;
89
90 if(!length($subject) || !length($msg) || length($subject) > 200 || length($msg) > 4000){
91 &support_inbox;
92 return;
93 }
94
95 my $es = $subject; $es =~ s/'/''/g; $es = substr($es, 0, 200);
96 my $em = $msg; $em =~ s/'/''/g;
97
98 my $dbh = $db->db_connect();
99
100 my $sql_t = qq~INSERT INTO `${database_name}`.support_threads (company_id, opened_by_user_id, subject, status, last_message_at, last_message_by, user_unread, admin_unread, created_at) VALUES ('$cid', '$uid', '$es', 'open', NOW(), 'user', 0, 1, NOW())~;
101 $db->db_readwrite($dbh, $sql_t, $ENV{SCRIPT_NAME}, __LINE__);
102
103 my $r = $db->db_readwrite($dbh, qq~SELECT LAST_INSERT_ID() AS id~, $ENV{SCRIPT_NAME}, __LINE__);
104 my $tid = $r->{id};
105
106 my $sql_m = qq~INSERT INTO `${database_name}`.support_messages (thread_id, sender_user_id, sender_kind, body, created_at) VALUES ('$tid','$uid','user','$em', NOW())~;
107 $db->db_readwrite($dbh, $sql_m, $ENV{SCRIPT_NAME}, __LINE__);
108
109 $db->db_disconnect($dbh);
110
111 print qq~<script>window.location="$url->{support_thread}&id=$tid&new=1";</script>~;
112}
113
114sub support_inbox{
115 my $uid = MODS::PTMatrix::PM::get_user_id($userinfo);
116
117 my $dbh = $db->db_connect();
118
119 #------------------------------------------------------------------------------------------------------
120 #This user's own threads, most recent activity first
121 my $sql = qq~SELECT t.id, t.subject, t.status, t.last_message_at, t.last_message_by, t.user_unread, t.created_at, UNIX_TIMESTAMP(t.last_message_at) AS last_message_epoch, UNIX_TIMESTAMP(t.created_at) AS created_epoch, (SELECT COUNT(*) FROM `${database_name}`.support_messages sm WHERE sm.thread_id=t.id) AS msg_count FROM `${database_name}`.support_threads t WHERE t.opened_by_user_id='$uid' ORDER BY t.last_message_at DESC, t.id DESC LIMIT 100~;
122 my @threads = $db->db_readwrite_multiple($dbh, $sql, $ENV{SCRIPT_NAME}, __LINE__);
123 #------------------------------------------------------------------------------------------------------
124
125 $db->db_disconnect($dbh);
126
127 foreach my $t(@threads){
128 $t->{subject_h} = MODS::PTMatrix::PM::h($t->{subject});
129 $t->{status_color} = $t->{status} eq 'open' ? '#5aa9ff' : $t->{status} eq 'answered' ? '#22c55e' : '#888';
130 $t->{status_label} = ucfirst($t->{status});
131 }
132
133 my $threads_html = '';
134 if(!@threads){
135 $threads_html = qq~<div style="padding:32px;text-align:center;color:var(--col-text-dim);border:1px dashed var(--col-border);border-radius:8px">No support threads yet. Open one below.</div>~;
136 }else{
137 $threads_html = qq~<div style="border:1px solid var(--col-border);border-radius:8px;overflow:hidden;background:var(--col-bg-2)">~;
138 foreach my $t(@threads){
139 my $unread_dot = $t->{user_unread} ? qq~<span style="display:inline-block;width:8px;height:8px;border-radius:50%;background:#b94854;margin-right:6px"></span>~ : '';
140 my $plural = $t->{msg_count} == 1 ? '' : 's';
141 $threads_html .= qq~
142 <a href="$url->{support_thread}&id=$t->{id}" style="display:flex;align-items:center;gap:14px;padding:14px 18px;border-bottom:1px solid var(--col-border);text-decoration:none;color:var(--col-text);transition:background .15s">
143 <div style="flex:1;min-width:0">
144 <div style="font-weight:600;font-size:14px">$unread_dot$t->{subject_h}</div>
145 <div style="font-size:11.5px;color:var(--col-text-dim);margin-top:2px">$t->{msg_count} message$plural &middot; last activity <span class="ts" data-ts="$t->{last_message_epoch}" data-fmt="relative">$t->{last_message_at}</span></div>
146 </div>
147 <span style="font-size:11px;color:$t->{status_color};text-transform:uppercase;letter-spacing:.06em;font-weight:700">$t->{status_label}</span>
148 </a>
149 ~;
150 }
151 $threads_html .= qq~</div>~;
152 }
153
154 my $body = qq~
155<div class="page-header"><div>
156 <h1>Contact support</h1>
157 <p class="subtitle">Send a message to the PTMatrix team. We respond in-app &mdash; your replies show up here.</p>
158</div></div>
159
160<div style="display:grid;grid-template-columns:1fr 380px;gap:24px;align-items:start">
161 <div>
162 <h2 style="font-size:15px;margin:0 0 12px">Your threads</h2>
163 $threads_html
164 </div>
165 <aside>
166 <div style="background:var(--col-bg-2);border:1px solid var(--col-border);border-radius:8px;padding:18px">
167 <h2 style="font-size:15px;margin:0 0 12px">Open a new thread</h2>
168 <form method="POST" action="$url->{support}">
169 <input type="hidden" name="act" value="new">
170 <label style="display:block;font-size:12px;color:var(--col-text-dim);margin-bottom:5px">Subject</label>
171 <input type="text" name="subject" required maxlength="200" placeholder="What's going on?" style="width:100%;padding:9px 11px;background:var(--col-bg-1);border:1px solid var(--col-border);color:var(--col-text);border-radius:6px;font-size:13px;margin-bottom:12px">
172 <label style="display:block;font-size:12px;color:var(--col-text-dim);margin-bottom:5px">Message</label>
173 <textarea name="body" required maxlength="4000" rows="6" placeholder="Describe what you need help with&hellip;" style="width:100%;padding:9px 11px;background:var(--col-bg-1);border:1px solid var(--col-border);color:var(--col-text);border-radius:6px;font-size:13px;resize:vertical;margin-bottom:14px"></textarea>
174 <button type="submit" class="btn primary" style="width:100%">Send to support</button>
175 </form>
176 </div>
177 </aside>
178</div>~;
179
180 $load->render({
181 userinfo => $userinfo,
182 page_key => 'support',
183 title => 'Contact support',
184 body => $body,
185 });
186}
187
188#============================================================
189# support_thread entry
190#============================================================
191sub esq{
192 my $s = shift;
193 $s = '' unless defined $s;
194 $s =~ s/'/''/g;
195 return $s;
196}
197
198sub redirect_thread{
199 my($tid, $extra) = @_;
200 print qq~Status: 302 Found\nLocation: /support_thread.cgi?id=$tid~ . ($extra || '') . qq~\n\n~;
201 exit;
202}
203
204sub handle_reply{
205 my($dbh, $tid, $thread, $uid, $is_admin) = @_;
206 my $msg = $rawform{body} || '';
207 $msg =~ s/^\s+|\s+$//g;
208 if(length($msg) && length($msg) <= 4000){
209 my $em = &esq($msg);
210 my $kind = $is_admin ? 'admin' : 'user';
211 my $other_unread = $is_admin ? 'user_unread=1, admin_unread=0' : 'admin_unread=1, user_unread=0';
212 my $new_status = $is_admin ? "'answered'" : ($thread->{status} eq 'closed' ? "'open'" : "'$thread->{status}'");
213 $db->db_readwrite($dbh, qq~INSERT INTO `${database_name}`.support_messages (thread_id, sender_user_id, sender_kind, body, created_at) VALUES ('$tid','$uid','$kind','$em', NOW())~, $ENV{SCRIPT_NAME}, __LINE__);
214 $db->db_readwrite($dbh, qq~UPDATE `${database_name}`.support_threads SET last_message_at = NOW(), last_message_by = '$kind', status = $new_status, $other_unread WHERE id='$tid'~, $ENV{SCRIPT_NAME}, __LINE__);
215 }
216 $db->db_disconnect($dbh);
217 &redirect_thread($tid);
218}
219
220sub handle_close_thread{
221 my($dbh, $tid) = @_;
222 $db->db_readwrite($dbh, qq~UPDATE `${database_name}`.support_threads SET status='closed', user_unread=1 WHERE id='$tid'~, $ENV{SCRIPT_NAME}, __LINE__);
223 $db->db_disconnect($dbh);
224 &redirect_thread($tid);
225}
226
227sub handle_reopen_thread{
228 my($dbh, $tid) = @_;
229 $db->db_readwrite($dbh, qq~UPDATE `${database_name}`.support_threads SET status='open' WHERE id='$tid'~, $ENV{SCRIPT_NAME}, __LINE__);
230 $db->db_disconnect($dbh);
231 &redirect_thread($tid);
232}
233
234sub handle_rate{
235 #User CSAT: 1-5 rating + optional comment on a closed thread
236 my($dbh, $tid, $thread, $uid) = @_;
237 my $rating = $rawform{rating};
238 $rating =~ s/[^0-9]//g if defined $rating;
239 $rating = 0 unless $rating;
240 $rating = 5 if $rating > 5;
241 my $comment = substr($rawform{comment} || '', 0, 1000);
242 $comment =~ s/'/''/g;
243
244 if($rating >= 1 && $rating <= 5){
245 my $exists = $db->db_readwrite($dbh, qq~SELECT id FROM `${database_name}`.support_satisfaction WHERE thread_id='$tid' LIMIT 1~, $ENV{SCRIPT_NAME}, __LINE__);
246 if($exists && $exists->{id}){
247 $db->db_readwrite($dbh, qq~UPDATE `${database_name}`.support_satisfaction SET rating='$rating', comment='$comment' WHERE thread_id='$tid'~, $ENV{SCRIPT_NAME}, __LINE__);
248 }else{
249 my $thread_cid = $thread->{company_id};
250 $db->db_readwrite($dbh, qq~INSERT INTO `${database_name}`.support_satisfaction (thread_id, company_id, user_id, rating, comment) VALUES ('$tid', '$thread_cid', '$uid', '$rating', '$comment')~, $ENV{SCRIPT_NAME}, __LINE__);
251 }
252 }
253 $db->db_disconnect($dbh);
254 &redirect_thread($tid, '&rated=1');
255}
256
257sub render_messages_html{
258 my($messages) = @_;
259 my $html = '';
260 for my $m(@$messages){
261 my $align = $m->{is_me} ? 'flex-direction:row-reverse' : '';
262 my $bg = $m->{is_admin} ? 'background:rgba(167,139,250,0.10);border:1px solid rgba(167,139,250,0.30)'
263 : 'background:var(--col-bg-2);border:1px solid var(--col-border)';
264 my $kind_tag = $m->{is_admin} ? '<span style="font-size:9.5px;background:rgba(167,139,250,0.30);color:#c4b5fd;padding:1px 6px;border-radius:999px;margin-left:6px;text-transform:uppercase;letter-spacing:.06em;font-weight:700">PTMatrix</span>' : '';
265 $html .= qq~
266 <div style="display:flex;gap:10px;align-items:flex-start;$align">
267 <div style="width:32px;height:32px;border-radius:50%;background:$m->{sender_color};display:grid;place-items:center;color:#fff;font-weight:700;font-size:11px;flex-shrink:0">$m->{initials}</div>
268 <div style="max-width:75%;padding:10px 14px;border-radius:10px;$bg">
269 <div style="font-size:11.5px;color:var(--col-text-dim);margin-bottom:3px"><span style="color:var(--col-text);font-weight:600">$m->{name_h}</span>$kind_tag <span style="margin-left:6px"><span class="ts" data-ts="$m->{created_epoch}" data-fmt="datetime">$m->{created_at}</span></span></div>
270 <div style="font-size:13.5px;line-height:1.5;white-space:pre-wrap;word-wrap:break-word">$m->{body_h}</div>
271 </div>
272 </div>
273 ~;
274 }
275 return $html;
276}
277
278sub render_csat_panel{
279 my($csat_row, $thread, $is_admin, $uid, $rated, $tid) = @_;
280
281 if($csat_row && $csat_row->{rating}){
282 my $r = int($csat_row->{rating});
283 my $stars = ('&#9733;' x $r) . ('&#9734;' x (5 - $r));
284 my $colour = $r >= 4 ? '#22c55e' : $r == 3 ? '#f59e0b' : '#ef4444';
285 my $comment_h = MODS::PTMatrix::PM::h($csat_row->{comment} || '');
286 my $comment_blk = length($comment_h)
287 ? '<div style="margin-top:8px;font-size:13px;color:var(--col-text);font-style:italic">&ldquo;' . $comment_h . '&rdquo;</div>'
288 : '';
289 my $heading = $is_admin ? 'Customer satisfaction' : 'You rated this support session';
290 my $rating_created = $csat_row->{created_at} || '';
291 my $rating_epoch = $csat_row->{created_epoch} || '';
292 my $html = '<div style="padding:16px;background:var(--col-bg-2);border:1px solid var(--col-border);border-radius:8px;margin-bottom:16px">';
293 $html .= '<div style="font-size:11px;text-transform:uppercase;letter-spacing:1px;color:var(--col-text-dim);margin-bottom:6px">' . $heading . '</div>';
294 $html .= '<div style="font-size:22px;color:' . $colour . ';letter-spacing:2px">' . $stars . '</div>';
295 $html .= '<div style="font-size:12px;color:var(--col-text-dim);margin-top:4px">' . $r . ' out of 5 &middot; submitted <span class="ts" data-ts="' . $rating_epoch . '" data-fmt="datetime">' . $rating_created . '</span></div>';
296 $html .= $comment_blk;
297 $html .= '</div>';
298 return $html;
299 }
300
301 if($thread->{status} eq 'closed' && !$is_admin && $thread->{opened_by_user_id} == $uid){
302 my $thanks = $rated
303 ? '<div style="font-size:12px;color:#22c55e;margin-bottom:8px">Thanks for the feedback!</div>'
304 : '';
305 my $stars_html = '';
306 for my $n(1..5){
307 $stars_html .= '<button type="submit" name="rating" value="' . $n . '" title="' . $n . ' out of 5" style="background:none;border:1px solid var(--col-border);border-radius:8px;width:48px;height:48px;font-size:18px;color:#f59e0b;cursor:pointer;font-weight:700">' . $n . '</button>';
308 }
309 my $html = '<form method="POST" action="/support_thread.cgi?id=' . $tid . '" style="padding:18px;background:var(--col-bg-2);border:1px solid var(--col-border);border-radius:8px;margin-bottom:16px">';
310 $html .= '<input type="hidden" name="act" value="rate">';
311 $html .= $thanks;
312 $html .= '<div style="font-size:14px;font-weight:600;margin-bottom:4px">How was this support session?</div>';
313 $html .= '<div style="font-size:12px;color:var(--col-text-dim);margin-bottom:12px">Rate your experience so we know where to keep improving.</div>';
314 $html .= '<div class="csat-stars" style="display:flex;gap:6px;margin-bottom:12px">' . $stars_html . '</div>';
315 $html .= '<label style="display:block;font-size:11px;color:var(--col-text-dim);text-transform:uppercase;letter-spacing:.5px;margin-bottom:4px">Optional comment</label>';
316 $html .= '<textarea name="comment" maxlength="1000" rows="2" placeholder="Anything you want our team to know?" style="width:100%;padding:9px 11px;background:var(--col-bg-1);border:1px solid var(--col-border);color:var(--col-text);border-radius:6px;font-size:13px;resize:vertical"></textarea>';
317 $html .= '<div style="font-size:11px;color:var(--col-text-dim);margin-top:6px">Click a star above to submit your rating (and comment).</div>';
318 $html .= '</form>';
319 return $html;
320 }
321
322 return '';
323}
324
325sub support_thread{
326 my($tid) = @_;
327 my $uid = MODS::PTMatrix::PM::get_user_id($userinfo);
328 my $is_admin = ($userinfo->{is_super_admin} || $userinfo->{is_admin}) ? 1 : 0;
329
330 my $dbh = $db->db_connect();
331
332 my $thread = $db->db_readwrite($dbh, qq~SELECT t.id, t.company_id, t.opened_by_user_id, t.subject, t.status, t.created_at, t.last_message_at, UNIX_TIMESTAMP(t.created_at) AS created_epoch, UNIX_TIMESTAMP(t.last_message_at) AS last_message_epoch, u.display_name AS opener_name, u.email AS opener_email, c.name AS company_name FROM `${database_name}`.support_threads t JOIN `${database_name}`.users u ON u.id = t.opened_by_user_id LEFT JOIN `${database_name}`.companies c ON c.id = t.company_id WHERE t.id='$tid' LIMIT 1~, $ENV{SCRIPT_NAME}, __LINE__);
333
334 if(!$thread || !$thread->{id}){
335 $db->db_disconnect($dbh);
336 print qq~Status: 404 Not Found\nContent-Type: text/plain\n\nThread not found.\n~;
337 exit;
338 }
339 if(!$is_admin && $thread->{opened_by_user_id} != $uid){
340 $db->db_disconnect($dbh);
341 print qq~Status: 403 Forbidden\nContent-Type: text/plain\n\nNot your thread.\n~;
342 exit;
343 }
344
345 #Dispatch POST actions - each exits after redirecting
346 if(($ENV{REQUEST_METHOD} || '') eq 'POST'){
347 my $act = $rawform{act} || '';
348 if($act eq 'reply') {&handle_reply($dbh, $tid, $thread, $uid, $is_admin);}
349 elsif($act eq 'close' && $is_admin) {&handle_close_thread($dbh, $tid);}
350 elsif($act eq 'reopen' && $is_admin) {&handle_reopen_thread($dbh, $tid);}
351 elsif($act eq 'rate' && !$is_admin && $thread->{opened_by_user_id} == $uid){&handle_rate($dbh, $tid, $thread, $uid);}
352 }
353
354 #Clear my-side unread flag on view
355 if($is_admin){
356 $db->db_readwrite($dbh, qq~UPDATE `${database_name}`.support_threads SET admin_unread=0 WHERE id='$tid'~, $ENV{SCRIPT_NAME}, __LINE__);
357 }elsif($thread->{opened_by_user_id} == $uid){
358 $db->db_readwrite($dbh, qq~UPDATE `${database_name}`.support_threads SET user_unread=0 WHERE id='$tid'~, $ENV{SCRIPT_NAME}, __LINE__);
359 }
360
361 my @messages = $db->db_readwrite_multiple($dbh, qq~SELECT m.id, m.sender_user_id, m.sender_kind, m.body, m.created_at, UNIX_TIMESTAMP(m.created_at) AS created_epoch, u.display_name AS sender_name, u.avatar_color AS sender_color FROM `${database_name}`.support_messages m JOIN `${database_name}`.users u ON u.id = m.sender_user_id WHERE m.thread_id='$tid' ORDER BY m.id ASC~, $ENV{SCRIPT_NAME}, __LINE__);
362
363 my $csat_row = $db->db_readwrite($dbh, qq~SELECT rating, comment, created_at, UNIX_TIMESTAMP(created_at) AS created_epoch FROM `${database_name}`.support_satisfaction WHERE thread_id='$tid' LIMIT 1~, $ENV{SCRIPT_NAME}, __LINE__);
364 my $rated = $form->{rated} ? 1 : 0;
365
366 for my $m(@messages){
367 $m->{body_h} = MODS::PTMatrix::PM::h($m->{body});
368 $m->{name_h} = MODS::PTMatrix::PM::h($m->{sender_name});
369 $m->{initials} = MODS::PTMatrix::PM::initials($m->{sender_name});
370 $m->{is_admin} = ($m->{sender_kind} eq 'admin') ? 1 : 0;
371 $m->{is_me} = ($m->{sender_user_id} == $uid) ? 1 : 0;
372 $m->{sender_color} ||= '#5aa9ff';
373 }
374
375 $db->db_disconnect($dbh);
376
377 print qq~Content-Type: text/html; charset=utf-8\nCache-Control: no-store\n\n~;
378
379 my $subject_h = MODS::PTMatrix::PM::h($thread->{subject});
380 my $opener_h = MODS::PTMatrix::PM::h($thread->{opener_name});
381 my $company_h = MODS::PTMatrix::PM::h($thread->{company_name} || '');
382 my $status_color = $thread->{status} eq 'open' ? '#5aa9ff' : $thread->{status} eq 'answered' ? '#22c55e' : '#888';
383 my $status_label = ucfirst($thread->{status});
384
385 my $messages_html = &render_messages_html(\@messages);
386 my $csat_panel = &render_csat_panel($csat_row, $thread, $is_admin, $uid, $rated, $tid);
387
388 my $admin_actions = '';
389 if($is_admin){
390 my $btn = $thread->{status} eq 'closed'
391 ? qq~<form method="POST" style="display:inline"><input type="hidden" name="act" value="reopen"><button class="btn">Reopen thread</button></form>~
392 : qq~<form method="POST" style="display:inline"><input type="hidden" name="act" value="close"><button class="btn">Mark closed</button></form>~;
393 $admin_actions = qq~<div style="margin-top:14px">$btn</div>~;
394 }
395
396 my $back = $is_admin ? '/admin_support.cgi' : '/support.cgi';
397 my $back_label = $is_admin ? 'Admin queue' : 'Your threads';
398
399 my $company_clause = length($company_h) ? " at <strong>$company_h</strong>" : '';
400 my $created_at = $thread->{created_at};
401 my $created_epoch = $thread->{created_epoch} || '';
402
403 my $reply_or_closed;
404 if($thread->{status} eq 'closed' && !$is_admin){
405 $reply_or_closed = '<div style="padding:18px;border:1px dashed var(--col-border);border-radius:8px;color:var(--col-text-dim);text-align:center">This thread is closed.</div>';
406 }else{
407 my $reply_label = $is_admin ? 'Reply as PTMatrix support' : 'Add a reply';
408 $reply_or_closed =
409 '<form method="POST" action="/support_thread.cgi?id=' . $tid . '" style="background:var(--col-bg-2);border:1px solid var(--col-border);border-radius:8px;padding:14px">'
410 . '<input type="hidden" name="act" value="reply">'
411 . '<label style="display:block;font-size:12px;color:var(--col-text-dim);margin-bottom:5px">' . $reply_label . '</label>'
412 . '<textarea name="body" required maxlength="4000" rows="4" placeholder="Type your reply..." style="width:100%;padding:9px 11px;background:var(--col-bg-1);border:1px solid var(--col-border);color:var(--col-text);border-radius:6px;font-size:13px;resize:vertical;margin-bottom:10px"></textarea>'
413 . '<button type="submit" class="btn primary">Send reply</button>'
414 . '</form>';
415 }
416
417 my $body =
418 '<div class="page-header" style="margin-bottom:14px"><div>'
419 . '<a href="' . $back . '" style="color:var(--col-accent,#5aa9ff);text-decoration:none;font-size:12px">&larr; ' . $back_label . '</a>'
420 . '<h1 style="margin:6px 0 4px">' . $subject_h . '</h1>'
421 . '<p class="subtitle">From <strong>' . $opener_h . '</strong>' . $company_clause . ' &middot; opened <span class="ts" data-ts="' . $created_epoch . '" data-fmt="datetime">' . $created_at . '</span> &middot; <span style="color:' . $status_color . '">' . $status_label . '</span></p>'
422 . '</div></div>'
423 . '<div style="display:flex;flex-direction:column;gap:14px;padding:18px;background:var(--col-bg-1);border:1px solid var(--col-border);border-radius:8px;margin-bottom:16px">'
424 . $messages_html
425 . '</div>'
426 . $csat_panel
427 . $reply_or_closed
428 . $admin_actions;
429
430 $load->render({
431 userinfo => $userinfo,
432 page_key => 'support',
433 title => $subject_h,
434 body => $body,
435 });
436}
437
438#============================================================
439# admin_support queue entry
440#============================================================
441sub filter_tab{
442 my($key, $label, $count, $active) = @_;
443 my $cls = $active ? 'active' : '';
444 return qq~<a href="$url->{admin_support}&filter=$key" class="filter-tab $cls">$label <span class="tab-count">$count</span></a>~;
445}
446
447sub admin_support_queue{
448 my $filter = $form->{filter} || 'open';
449 my $where = '';
450 if ($filter eq 'open') {$where = "WHERE t.status <> 'closed'";}
451 elsif($filter eq 'unread') {$where = "WHERE t.admin_unread=1 AND t.status <> 'closed'";}
452 elsif($filter eq 'answered') {$where = "WHERE t.status='answered'";}
453 elsif($filter eq 'closed') {$where = "WHERE t.status='closed'";}
454
455 my $dbh = $db->db_connect();
456
457 my $sql_t = qq~SELECT t.id, t.subject, t.status, t.last_message_at, t.last_message_by, t.admin_unread, t.created_at, UNIX_TIMESTAMP(t.last_message_at) AS last_message_epoch, UNIX_TIMESTAMP(t.created_at) AS created_epoch, u.display_name AS opener_name, u.email AS opener_email, c.name AS company_name, (SELECT COUNT(*) FROM `${database_name}`.support_messages sm WHERE sm.thread_id=t.id) AS msg_count FROM `${database_name}`.support_threads t JOIN `${database_name}`.users u ON u.id = t.opened_by_user_id LEFT JOIN `${database_name}`.companies c ON c.id = t.company_id $where ORDER BY t.admin_unread DESC, t.last_message_at DESC LIMIT 200~;
458 my @threads = $db->db_readwrite_multiple($dbh, $sql_t, $ENV{SCRIPT_NAME}, __LINE__);
459
460 foreach my $t(@threads){
461 $t->{subject_h} = MODS::PTMatrix::PM::h($t->{subject});
462 $t->{opener_h} = MODS::PTMatrix::PM::h($t->{opener_name});
463 $t->{company_h} = MODS::PTMatrix::PM::h($t->{company_name} || '&mdash;');
464 $t->{status_color} = $t->{status} eq 'open' ? '#5aa9ff' : $t->{status} eq 'answered' ? '#22c55e' : '#888';
465 $t->{status_label} = ucfirst($t->{status});
466 }
467
468 my $counts = $db->db_readwrite($dbh, qq~SELECT SUM(CASE WHEN status <> 'closed' THEN 1 ELSE 0 END) AS n_open, SUM(CASE WHEN admin_unread=1 AND status <> 'closed' THEN 1 ELSE 0 END) AS n_unread, SUM(CASE WHEN status='answered' THEN 1 ELSE 0 END) AS n_answered, SUM(CASE WHEN status='closed' THEN 1 ELSE 0 END) AS n_closed FROM `${database_name}`.support_threads~, $ENV{SCRIPT_NAME}, __LINE__) // {};
469
470 #CSAT 90-day summary - guard if the migration hasn't been applied yet
471 my $csat = { n => 0, avg => '0.0', p4 => 0, recent => [] };
472 my $csat_ready = $db->db_readwrite($dbh, qq~SELECT COUNT(*) AS n FROM information_schema.TABLES WHERE TABLE_SCHEMA='${database_name}' AND TABLE_NAME='support_satisfaction'~, $ENV{SCRIPT_NAME}, __LINE__);
473 if($csat_ready && $csat_ready->{n}){
474 my $r = $db->db_readwrite($dbh, qq~SELECT COUNT(*) AS n, AVG(rating) AS avg_rating, SUM(CASE WHEN rating >= 4 THEN 1 ELSE 0 END) AS n_pos FROM `${database_name}`.support_satisfaction WHERE created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)~, $ENV{SCRIPT_NAME}, __LINE__);
475 if($r && $r->{n}){
476 $csat->{n} = int($r->{n});
477 $csat->{avg} = sprintf('%.1f', $r->{avg_rating} || 0);
478 $csat->{p4} = $r->{n} ? int(100 * ($r->{n_pos} || 0) / $r->{n}) : 0;
479 }
480 my @recent = $db->db_readwrite_multiple($dbh, qq~SELECT s.rating, s.comment, s.created_at, UNIX_TIMESTAMP(s.created_at) AS created_epoch, t.subject, u.display_name FROM `${database_name}`.support_satisfaction s JOIN `${database_name}`.support_threads t ON t.id = s.thread_id JOIN `${database_name}`.users u ON u.id = s.user_id WHERE s.comment IS NOT NULL AND s.comment <> '' ORDER BY s.id DESC LIMIT 5~, $ENV{SCRIPT_NAME}, __LINE__);
481 $csat->{recent} = \@recent;
482 }
483
484 $db->db_disconnect($dbh);
485
486 my $csat_panel_html = '';
487 if($csat->{n}){
488 my $avg_colour = $csat->{avg} >= 4 ? '#22c55e' : $csat->{avg} >= 3 ? '#f59e0b' : '#ef4444';
489 my $recent_html = '';
490 for my $r(@{ $csat->{recent} }){
491 my $r_stars = ('&#9733;' x int($r->{rating})) . ('&#9734;' x (5 - int($r->{rating})));
492 my $r_name = MODS::PTMatrix::PM::h($r->{display_name} || '');
493 my $r_subj = MODS::PTMatrix::PM::h($r->{subject} || '');
494 my $r_cmt = MODS::PTMatrix::PM::h(substr($r->{comment} || '', 0, 280));
495 my $r_when = $r->{created_at} || '';
496 my $r_epoch = $r->{created_epoch} || '';
497 $recent_html .= '<div style="padding:10px 12px;border-top:1px solid var(--col-border)">';
498 $recent_html .= '<div style="font-size:11px;color:#f59e0b;letter-spacing:1.5px">' . $r_stars . '</div>';
499 $recent_html .= '<div style="font-size:13px;color:var(--col-text);margin:4px 0 2px;font-style:italic">&ldquo;' . $r_cmt . '&rdquo;</div>';
500 $recent_html .= '<div style="font-size:11px;color:var(--col-text-dim)">&mdash; ' . $r_name . ' &middot; ' . $r_subj . ' &middot; <span class="ts" data-ts="' . $r_epoch . '" data-fmt="datetime">' . $r_when . '</span></div>';
501 $recent_html .= '</div>';
502 }
503 $csat_panel_html = '<div class="csat-grid" style="display:grid;grid-template-columns:repeat(auto-fit,minmax(160px,1fr));gap:12px;margin:14px 0 4px">';
504 $csat_panel_html .= '<div class="kpi"><div class="kpi-label">CSAT avg (90d)</div><div class="kpi-num" style="color:' . $avg_colour . '">' . $csat->{avg} . ' <small style="font-size:13px;color:var(--col-text-dim)">/ 5</small></div></div>';
505 $csat_panel_html .= '<div class="kpi"><div class="kpi-label">Responses (90d)</div><div class="kpi-num">' . $csat->{n} . '</div></div>';
506 $csat_panel_html .= '<div class="kpi"><div class="kpi-label">% 4+ stars</div><div class="kpi-num">' . $csat->{p4} . '%</div></div>';
507 $csat_panel_html .= '</div>';
508 if(length $recent_html){
509 $csat_panel_html .= '<details class="csat-recent" style="background:var(--col-bg-2);border:1px solid var(--col-border);border-radius:10px;margin-top:8px"><summary style="padding:10px 14px;cursor:pointer;font-size:12px;font-weight:600;color:var(--col-text-dim)">Recent comments (' . $csat->{n} . ' ratings logged)</summary>' . $recent_html . '</details>';
510 }
511 }
512
513 my $tabs = &filter_tab('open', 'Open', ($counts->{n_open} || 0), $filter eq 'open')
514 . &filter_tab('unread', 'Unread', ($counts->{n_unread} || 0), $filter eq 'unread')
515 . &filter_tab('answered', 'Answered', ($counts->{n_answered}|| 0), $filter eq 'answered')
516 . &filter_tab('closed', 'Closed', ($counts->{n_closed} || 0), $filter eq 'closed');
517
518 my $rows_html = '';
519 if(!@threads){
520 $rows_html = qq~<tr><td colspan="6" style="text-align:center;padding:32px;color:var(--col-text-dim)">No threads in this view.</td></tr>~;
521 }else{
522 for my $t(@threads){
523 my $unread_dot = $t->{admin_unread} ? qq~<span style="display:inline-block;width:8px;height:8px;border-radius:50%;background:#b94854;margin-right:8px;vertical-align:middle"></span>~ : '';
524 $rows_html .= qq~
525 <tr>
526 <td><a href="$url->{support_thread}&id=$t->{id}" style="color:var(--col-text);text-decoration:none;font-weight:600">$unread_dot$t->{subject_h}</a></td>
527 <td>$t->{opener_h}</td>
528 <td>$t->{company_h}</td>
529 <td>$t->{msg_count}</td>
530 <td><span style="color:$t->{status_color};font-weight:600">$t->{status_label}</span></td>
531 <td style="color:var(--col-text-dim);font-size:11.5px"><span class="ts" data-ts="$t->{last_message_epoch}" data-fmt="relative">$t->{last_message_at}</span></td>
532 </tr>
533 ~;
534 }
535 }
536
537 my $body = qq~
538<style>
539.filter-tabs { display:flex; gap:6px; margin:14px 0 18px; }
540.filter-tab { padding:7px 14px; border:1px solid var(--col-border); border-radius:999px; text-decoration:none; color:var(--col-text-dim); font-size:12.5px; font-weight:600; display:inline-flex; gap:7px; align-items:center; transition:all .15s; }
541.filter-tab:hover { color:var(--col-text); border-color:rgba(90,169,255,0.4); }
542.filter-tab.active { color:#fff; border-color:rgba(90,169,255,0.6); background:rgba(90,169,255,0.10); }
543.filter-tab .tab-count { background:var(--col-bg-1); padding:1px 7px; border-radius:999px; font-size:10.5px; color:var(--col-text-dim); }
544.admin-tbl { width:100%; border-collapse:collapse; background:var(--col-bg-2); border:1px solid var(--col-border); border-radius:8px; overflow:hidden; }
545.admin-tbl th { padding:12px 14px; background:rgba(255,255,255,0.03); font-size:11px; text-transform:uppercase; letter-spacing:.06em; color:var(--col-text-dim); text-align:left; }
546.admin-tbl td { padding:12px 14px; border-top:1px solid var(--col-border); font-size:13px; vertical-align:middle; }
547.kpi { background:var(--col-bg-2); border:1px solid var(--col-border); border-radius:10px; padding:12px 14px; }
548.kpi-label { font-size:10.5px; text-transform:uppercase; letter-spacing:.08em; color:var(--col-text-dim); }
549.kpi-num { font-size:24px; font-weight:700; margin-top:4px; }
550</style>
551
552<div class="page-header"><div>
553 <h1>Support queue</h1>
554 <p class="subtitle">All customer-initiated support threads. Click a row to read and reply.</p>
555</div></div>
556
557$csat_panel_html
558
559<div class="filter-tabs">$tabs</div>
560
561<table class="admin-tbl">
562 <thead><tr>
563 <th>Subject</th><th>From</th><th>Company</th><th>Msgs</th><th>Status</th><th>Last activity</th>
564 </tr></thead>
565 <tbody>$rows_html</tbody>
566</table>
567~;
568
569 $load->render({
570 userinfo => $userinfo,
571 page_key => 'admin_support',
572 title => 'Support queue',
573 body => $body,
574 });
575}