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

O Operator
Diff

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

added on local at 2026-07-01 12:34:32

Added
+396
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to 1c2f7554450a
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; $CGI::POST_MAX = 2 * 1024 * 1024; 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::Template; my $tfile = new MODS::Template;
30use MODS::PTMatrix::Wrapper; my $load = new MODS::PTMatrix::Wrapper;
31use MODS::DBConnect; my $db = new MODS::DBConnect;
32use MODS::PTMatrix::Config; my $config = new MODS::PTMatrix::Config; my $database_name = $config->settings('database_name');
33use MODS::PTMatrix::PM;
34use Digest::SHA qw(sha256_hex);
35use MIME::Base64 qw(encode_base64url decode_base64url);
36
37
38
39#############################################################
40## Get form data
41#############################################################
42#Preserve the raw values before quotemeta - the token, email body, cookie decode
43#and welcome text all need their original characters intact.
44my %rawform = %$form;
45
46foreach my $line(keys %$form){
47 $form->{$line} =~ s/[^!-~\s]//g;
48 $form->{$line} = quotemeta("$form->{$line}");
49}
50
51
52
53############################################################
54# Program Controls
55############################################################
56$|=1;
57my $entry = ($ENV{SCRIPT_NAME} || '') =~ m{/([^/]+)\.cgi$} ? $1 : 'portal';
58
59if($entry eq 'portal_settings'){
60 my $userinfo = $login->login_verify();
61 if(!$userinfo){print qq~Content-type:text/html; Cache-Control:no-cache;\n\n<script>window.location="$url->{login}";</script>~; exit;}
62
63 my $is_admin = ($userinfo->{role} && ($userinfo->{role} eq 'company_admin' || $userinfo->{role} eq 'company_owner')) || $userinfo->{is_super_admin};
64 if(!$is_admin){print qq~Status: 403 Forbidden\nContent-Type: text/plain\n\nAdmin only.\n~; exit;}
65
66 if(($ENV{REQUEST_METHOD} || '') eq 'POST'){&save_portal($userinfo);}
67 else{&portal_form($userinfo);}
68 exit;
69}
70
71my $slug = $rawform{c} || $query->url_param('c') || '';
72$slug =~ s/[^a-z0-9_\-]//g;
73$slug = substr($slug, 0, 64);
74if(!length $slug){print qq~Status: 404 Not Found\nContent-Type: text/plain\n\nMissing company slug.\n~; exit;}
75
76&portal;
77
78
79############################################################
80# Subroutines
81############################################################
82sub b64_encode{
83 my($s) = @_;
84 return encode_base64url($s);
85}
86
87sub b64_decode{
88 my($s) = @_;
89 return decode_base64url($s);
90}
91
92sub portal_secret{
93 #Per-company HMAC secret - derived from platform email crypto key + company id
94 my($dbh, $cid) = @_;
95 my $r = $db->db_readwrite($dbh, qq~SELECT setting_value FROM `${database_name}`.platform_settings WHERE setting_key='email.crypto_key' LIMIT 1~, $ENV{SCRIPT_NAME}, __LINE__);
96 my $k = ($r && $r->{setting_value}) ? $r->{setting_value} : 'fallback-secret';
97 return sha256_hex("portal:$cid:$k");
98}
99
100sub send_magic_link{
101 my($dbh, $cid, $to, $link, $brand) = @_;
102 my $email_active = $db->db_readwrite($dbh, qq~SELECT id FROM `${database_name}`.email_inboxes WHERE company_id='$cid' AND is_active=1 AND smtp_host IS NOT NULL ORDER BY id LIMIT 1~, $ENV{SCRIPT_NAME}, __LINE__);
103 return unless $email_active && $email_active->{id};
104
105 my $subj = "Your $brand sign-in link";
106 my $body = "Click to view your tickets:\n\n$link\n\nThis link expires in 30 minutes.";
107 my $to_q = $to; $to_q =~ s/'/''/g;
108 my $subj_q = $subj; $subj_q =~ s/'/''/g;
109 my $body_q = $body; $body_q =~ s/'/''/g;
110 $db->db_readwrite($dbh, qq~INSERT INTO `${database_name}`.email_outbound_queue SET inbox_id='$email_active->{id}', task_id=NULL, to_addr='$to_q', subject='$subj_q', body_text='$body_q', status='queued'~, $ENV{SCRIPT_NAME}, __LINE__);
111}
112
113sub consume_magic_token{
114 #A ?t=<token> hit lands here - if valid, mint the tf_portal cookie
115 my($dbh, $cid, $secret) = @_;
116 my $token = $rawform{t} || '';
117 return ('', '', '', '') unless length $token;
118
119 my $tq = $token; $tq =~ s/'/''/g;
120 my $r = $db->db_readwrite($dbh, qq~SELECT * FROM `${database_name}`.customer_portal_tokens WHERE token='$tq' AND company_id='$cid' AND expires_at > NOW() AND used_at IS NULL LIMIT 1~, $ENV{SCRIPT_NAME}, __LINE__);
121 if($r && $r->{id}){
122 $db->db_readwrite($dbh, qq~UPDATE `${database_name}`.customer_portal_tokens SET used_at=NOW() WHERE id='$r->{id}'~, $ENV{SCRIPT_NAME}, __LINE__);
123 my $sig = sha256_hex($r->{customer_email} . ':' . $cid . ':' . $secret);
124 my $cookie_val = &b64_encode($r->{customer_email} . ':' . $sig);
125 my $cookie_set = "Set-Cookie: tf_portal=$cookie_val; Path=/portal.cgi; Max-Age=1209600; HttpOnly; Secure; SameSite=Lax\n";
126 return ($cookie_set, $r->{customer_email}, 'Signed in.', 'ok');
127 }
128 return ('', '', 'Sign-in link expired or already used. Request a new one below.', 'err');
129}
130
131sub cookie_email{
132 #Read tf_portal cookie, verify HMAC, return the signed-in email or empty
133 my($cid, $secret) = @_;
134 my $cookie = '';
135 foreach my $c (split /;\s*/, $ENV{HTTP_COOKIE} || ''){
136 if($c =~ /^tf_portal=(.+)$/){$cookie = $1; last;}
137 }
138 return '' unless $cookie;
139
140 my $dec = &b64_decode($cookie);
141 if($dec =~ /^(.+):([0-9a-f]{64})$/){
142 my($em, $sig) = ($1, $2);
143 my $expected = sha256_hex($em . ':' . $cid . ':' . $secret);
144 if($expected eq $sig){return $em;}
145 }
146 return '';
147}
148
149sub handle_send_link{
150 #POST act=send_link - mint a 30 minute token + queue the magic-link email
151 my($dbh, $cid, $brand) = @_;
152 my $email = lc($rawform{email} || '');
153 $email =~ s/^\s+|\s+$//g;
154 if($email !~ /\@/){return ('Enter a valid email address.', 'err');}
155
156 my $tok;
157 if(open my $fh, '<', '/dev/urandom'){
158 my $buf; read($fh, $buf, 24); close $fh;
159 $tok = unpack('H*', $buf);
160 }else{
161 $tok = sprintf('%016x%016x%016x', int(rand(2**32)), int(rand(2**32)), int(rand(2**32)));
162 }
163 my $em_q = $email; $em_q =~ s/'/''/g;
164 $db->db_readwrite($dbh, qq~INSERT INTO `${database_name}`.customer_portal_tokens SET company_id='$cid', customer_email='$em_q', token='$tok', expires_at=DATE_ADD(NOW(), INTERVAL 30 MINUTE)~, $ENV{SCRIPT_NAME}, __LINE__);
165
166 my $link = "https://ptmatrix.3dshawn.com/portal.cgi?c=$slug&t=$tok";
167 &send_magic_link($dbh, $cid, $email, $link, $brand);
168 return ('Check your email for a sign-in link. It expires in 30 minutes.', 'ok');
169}
170
171sub handle_new_ticket{
172 #POST act=new_ticket - authed portal users file a ticket into the default project
173 my($dbh, $cid, $company, $brand, $logged_email) = @_;
174
175 my $title = substr($rawform{title} || '', 0, 280);
176 $title =~ s/^\s+|\s+$//g;
177 my $body = substr($rawform{body} || '', 0, 32000);
178 my $pid = $company->{portal_default_project_id} || 0;
179
180 if(!$pid){return ('Portal is missing a default project. Contact ' . $brand . '.', 'err');}
181 if(!length $title){return ('Title is required.', 'err');}
182
183 my $col = $db->db_readwrite($dbh, qq~SELECT id FROM `${database_name}`.project_kanban_columns WHERE project_id='$pid' ORDER BY position LIMIT 1~, $ENV{SCRIPT_NAME}, __LINE__);
184 my $col_id = ($col && $col->{id}) ? $col->{id} : 0;
185 return ('', '') unless $col_id;
186
187 my $tn = $db->db_readwrite($dbh, qq~SELECT COALESCE(MAX(ticket_number),0)+1 AS n FROM `${database_name}`.tasks WHERE project_id='$pid'~, $ENV{SCRIPT_NAME}, __LINE__);
188 my $ticket_n = ($tn && $tn->{n}) ? $tn->{n} : 1;
189
190 my $rep = $db->db_readwrite($dbh, qq~SELECT id FROM `${database_name}`.users WHERE company_id='$cid' AND account_status='active' ORDER BY is_super_admin DESC, id ASC LIMIT 1~, $ENV{SCRIPT_NAME}, __LINE__);
191 my $reporter = ($rep && $rep->{id}) ? $rep->{id} : 1;
192
193 my $t_q = $title; $t_q =~ s/'/''/g;
194 my $b_q = $body; $b_q =~ s/'/''/g;
195 my $em_q = $logged_email; $em_q =~ s/'/''/g;
196 $db->db_readwrite($dbh, qq~INSERT INTO `${database_name}`.tasks SET company_id='$cid', project_id='$pid', ticket_number='$ticket_n', title='$t_q', description='$b_q', column_id='$col_id', position_in_column=0, status='open', priority='medium', reporter_user_id='$reporter', work_item_type='ticket', requester_type='external', source='form', customer_email='$em_q'~, $ENV{SCRIPT_NAME}, __LINE__);
197
198 return ('Your request was submitted. We\'ll reply by email.', 'ok');
199}
200
201sub load_tickets{
202 my($dbh, $cid, $logged_email) = @_;
203 my $em_q = $logged_email; $em_q =~ s/'/''/g;
204 my @tickets = $db->db_readwrite_multiple($dbh, qq~SELECT t.id, t.title, t.status, t.priority, t.created_at, t.updated_at, p.key_prefix, t.ticket_number FROM `${database_name}`.tasks t JOIN `${database_name}`.projects p ON p.id=t.project_id WHERE t.company_id='$cid' AND t.customer_email='$em_q' ORDER BY t.updated_at DESC LIMIT 50~, $ENV{SCRIPT_NAME}, __LINE__);
205
206 my @rows;
207 foreach my $t(@tickets){
208 my $body_safe = $t->{title}; $body_safe =~ s/</&lt;/g;
209 push @rows, {
210 id => $t->{id},
211 ref => ($t->{key_prefix} ? "$t->{key_prefix}-$t->{ticket_number}" : "#$t->{id}"),
212 title => $body_safe,
213 status => $t->{status},
214 status_label => ucfirst($t->{status}),
215 is_open => ($t->{status} =~ /^(open|in_progress|blocked|review)$/) ? 1 : 0,
216 updated_at => $t->{updated_at} || $t->{created_at},
217 };
218 }
219 return @rows;
220}
221
222sub portal{
223 my $dbh = $db->db_connect();
224
225 #Guard: portal columns exist? Bail cleanly if the schema hasn't been migrated.
226 my $sr = $db->db_readwrite($dbh, qq~SELECT COUNT(*) AS n FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='$database_name' AND TABLE_NAME='companies' AND COLUMN_NAME='portal_slug'~, $ENV{SCRIPT_NAME}, __LINE__);
227 if(!$sr || !$sr->{n}){
228 $db->db_disconnect($dbh);
229 print qq~Status: 503 Service Unavailable\nContent-Type: text/plain\n\nPortal not provisioned.\n~;
230 exit;
231 }
232
233 #Load the company by slug
234 my $slug_q = $slug; $slug_q =~ s/'/''/g;
235 my $company = $db->db_readwrite($dbh, qq~SELECT id, name, portal_slug, portal_enabled, portal_brand_name, portal_welcome, portal_default_project_id FROM `${database_name}`.companies WHERE portal_slug='$slug_q' AND portal_enabled=1 LIMIT 1~, $ENV{SCRIPT_NAME}, __LINE__);
236 if(!$company || !$company->{id}){
237 $db->db_disconnect($dbh);
238 print qq~Status: 404 Not Found\nContent-Type: text/plain\n\nPortal not found or disabled.\n~;
239 exit;
240 }
241
242 my $cid = $company->{id};
243 my $brand = $company->{portal_brand_name} || $company->{name};
244 my $secret = &portal_secret($dbh, $cid);
245
246 #Step 1: magic-link consumption
247 my($cookie_set, $logged_email, $flash, $flash_kind) = &consume_magic_token($dbh, $cid, $secret);
248
249 #Step 2: existing cookie if no fresh token
250 if(!$logged_email){
251 $logged_email = &cookie_email($cid, $secret);
252 }
253
254 #Step 3: handle POST actions
255 if(($ENV{REQUEST_METHOD} || '') eq 'POST'){
256 my $act = $rawform{act} || '';
257 if($act eq 'send_link'){
258 ($flash, $flash_kind) = &handle_send_link($dbh, $cid, $brand);
259 }elsif($act eq 'new_ticket' && $logged_email){
260 my($msg, $kind) = &handle_new_ticket($dbh, $cid, $company, $brand, $logged_email);
261 if(length $msg){$flash = $msg; $flash_kind = $kind;}
262 }
263 }
264
265 my @ticket_rows = $logged_email ? &load_tickets($dbh, $cid, $logged_email) : ();
266
267 $db->db_disconnect($dbh);
268
269 #Render
270 my $brand_safe = $brand; $brand_safe =~ s/</&lt;/g;
271 my $welcome_safe = $company->{portal_welcome} || ''; $welcome_safe =~ s/</&lt;/g;
272
273 my $tvars;
274 $tvars->{brand_name} = $brand_safe;
275 $tvars->{welcome} = $welcome_safe;
276 $tvars->{has_welcome} = length($welcome_safe) ? 1 : 0;
277 $tvars->{flash} = $flash;
278 $tvars->{has_flash} = length($flash) ? 1 : 0;
279 $tvars->{flash_kind} = $flash_kind;
280 $tvars->{logged_email} = $logged_email;
281 $tvars->{is_logged} = length($logged_email) ? 1 : 0;
282 $tvars->{tickets} = \@ticket_rows;
283 $tvars->{has_tickets} = scalar(@ticket_rows) ? 1 : 0;
284 $tvars->{slug} = $slug;
285
286 print qq~Content-Type: text/html; charset=utf-8\nCache-Control: no-store\n~;
287 print $cookie_set if length $cookie_set;
288 print qq~\n~;
289 print join('', $tfile->template('tf_portal.html', $tvars, undef));
290}
291
292#============================================================
293# portal_settings entry: admin config for the portal
294#============================================================
295sub save_portal{
296 my($userinfo) = @_;
297 my $cid = MODS::PTMatrix::PM::get_company_id($userinfo);
298 my $dbh = $db->db_connect();
299
300 my $sr = $db->db_readwrite($dbh, qq~SELECT COUNT(*) AS n FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='${database_name}' AND TABLE_NAME='companies' AND COLUMN_NAME='portal_slug'~, $ENV{SCRIPT_NAME}, __LINE__);
301 my $schema_ready = ($sr && $sr->{n}) ? 1 : 0;
302
303 if(!$schema_ready){
304 $db->db_disconnect($dbh);
305 &portal_form($userinfo);
306 return;
307 }
308
309 my $slug = lc($form->{portal_slug} || '');
310 $slug =~ s/[^a-z0-9_\-]/-/g; $slug =~ s/-+/-/g; $slug =~ s/^-|-$//g;
311 $slug = substr($slug, 0, 64);
312
313 my $enabled = ($form->{portal_enabled} && $form->{portal_enabled} eq '1') ? 1 : 0;
314 my $brand = substr($form->{portal_brand_name} || '', 0, 120);
315 my $welcome = substr($form->{portal_welcome} || '', 0, 500);
316 my $pid = $form->{portal_default_project_id};
317 $pid =~ s/[^0-9]//g if defined $pid;
318
319 if($enabled && !length $slug){
320 $db->db_disconnect($dbh);
321 &portal_form($userinfo, 'err', 'Choose a portal slug (used in the public URL).');
322 return;
323 }
324
325 my @sets;
326 push @sets, "portal_enabled='$enabled'";
327 push @sets, "portal_slug=" . (length $slug ? "'" . &ps_q($slug) . "'" : 'NULL');
328 push @sets, "portal_brand_name=" . (length $brand ? "'" . &ps_q($brand) . "'" : 'NULL');
329 push @sets, "portal_welcome=" . (length $welcome ? "'" . &ps_q($welcome) . "'" : 'NULL');
330 push @sets, "portal_default_project_id=" . (length($pid||'') ? "'$pid'" : 'NULL');
331
332 my $set = join(',', @sets);
333 $db->db_readwrite($dbh, qq~UPDATE `${database_name}`.companies SET $set WHERE id='$cid'~, $ENV{SCRIPT_NAME}, __LINE__);
334 $db->db_disconnect($dbh);
335
336 &portal_form($userinfo, 'ok', 'Portal settings saved.');
337}
338
339sub portal_form{
340 my($userinfo, $flash_kind, $flash_msg) = @_;
341 $flash_kind ||= '';
342 $flash_msg ||= '';
343
344 my $cid = MODS::PTMatrix::PM::get_company_id($userinfo);
345 my $dbh = $db->db_connect();
346
347 my $sr = $db->db_readwrite($dbh, qq~SELECT COUNT(*) AS n FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='${database_name}' AND TABLE_NAME='companies' AND COLUMN_NAME='portal_slug'~, $ENV{SCRIPT_NAME}, __LINE__);
348 my $schema_ready = ($sr && $sr->{n}) ? 1 : 0;
349
350 my $row = $db->db_readwrite($dbh, qq~SELECT portal_enabled, portal_slug, portal_brand_name, portal_welcome, portal_default_project_id, name FROM `${database_name}`.companies WHERE id='$cid' LIMIT 1~, $ENV{SCRIPT_NAME}, __LINE__);
351 my @projects = $db->db_readwrite_multiple($dbh, qq~SELECT id, name FROM `${database_name}`.projects WHERE company_id='$cid' ORDER BY name~, $ENV{SCRIPT_NAME}, __LINE__);
352
353 $db->db_disconnect($dbh);
354
355 my $proj_opts = '<option value="">&mdash; pick one &mdash;</option>';
356 foreach my $p(@projects){
357 my $sel = (($row->{portal_default_project_id} || '') eq $p->{id}) ? ' selected' : '';
358 my $n = $p->{name}; $n =~ s/</&lt;/g;
359 $proj_opts .= qq~<option value="$p->{id}"$sel>$n</option>~;
360 }
361
362 my $portal_url = $row->{portal_slug} ? "https://ptmatrix.3dshawn.com/portal.cgi?c=$row->{portal_slug}" : '';
363
364 print qq~Content-Type: text/html; charset=utf-8\nCache-Control: no-store\n\n~;
365
366 my $tvars;
367 $tvars->{schema_ready} = $schema_ready ? 1 : 0;
368 $tvars->{schema_missing} = $schema_ready ? 0 : 1;
369 $tvars->{flash_kind} = $flash_kind;
370 $tvars->{flash_msg} = MODS::PTMatrix::PM::h($flash_msg);
371 $tvars->{has_flash} = $flash_msg ne '' ? 1 : 0;
372 $tvars->{portal_enabled_attr} = $row->{portal_enabled} ? 'checked' : '';
373 $tvars->{portal_enabled} = $row->{portal_enabled} ? 1 : 0;
374 $tvars->{portal_slug} = MODS::PTMatrix::PM::h($row->{portal_slug} || '');
375 $tvars->{portal_brand_name} = MODS::PTMatrix::PM::h($row->{portal_brand_name} || $row->{name});
376 $tvars->{portal_welcome} = MODS::PTMatrix::PM::h($row->{portal_welcome} || '');
377 $tvars->{portal_url} = $portal_url;
378 $tvars->{has_portal_url} = length($portal_url) ? 1 : 0;
379 $tvars->{proj_opts} = $proj_opts;
380 $tvars->{has_projects} = scalar(@projects) ? 1 : 0;
381
382 my $body = join('', $tfile->template('tf_portal_settings.html', $tvars, $userinfo));
383
384 $load->render({
385 userinfo => $userinfo,
386 page_key => 'portal_settings',
387 title => 'Customer Portal',
388 body => $body,
389 });
390}
391
392sub ps_q{
393 my $s = shift // '';
394 $s =~ s/'/''/g;
395 return $s;
396}