added on local at 2026-07-01 12:34:39
| 1 | #!/usr/bin/perl | |
| 2 | ||
| 3 | ################################################################################################################################## | |
| 4 | # ______ ____ ____ ______ ______ ____ ___ __ ___ ______ _ __ ____ ____ __ __ _ __ _____ ____ | |
| 5 | # / ____// __ \ / __ \ / ____/ / ____// __ \ / | / |/ // ____/| | / // __ \ / __ \ / //_/ | | / /|__ / / __ \ | |
| 6 | # / / / / / // / / // __/ / /_ / /_/ // /| | / /|_/ // __/ | | /| / // / / // /_/ // ,< | | / / /_ < / / / / | |
| 7 | # / /___ / /_/ // /_/ // /___ / __/ / _, _// ___ | / / / // /___ | |/ |/ // /_/ // _, _// /| | | |/ / ___/ /_ / /_/ / | |
| 8 | # \____/ \____//_____//_____/ /_/ /_/ |_|/_/ |_|/_/ /_//_____/ |__/|__/ \____//_/ |_|/_/ |_| |___/ /____/(_)\____/ | |
| 9 | # | |
| 10 | # - Written by: Shawn Pickering | |
| 11 | # - shawn@shawnpickering.com | |
| 12 | ################################################################################################################################## | |
| 13 | use strict; | |
| 14 | use lib '/var/www/vhosts/3dshawn.com/ptmatrix.3dshawn.com'; | |
| 15 | ||
| 16 | ||
| 17 | ############################################################# | |
| 18 | ## Variables | |
| 19 | ############################################################# | |
| 20 | ||
| 21 | ||
| 22 | ||
| 23 | ############################################################ | |
| 24 | # Modules | |
| 25 | ############################################################ | |
| 26 | #Load the CGI module | |
| 27 | use CGI; | |
| 28 | my $query = new CGI; | |
| 29 | my $form = $query->Vars; | |
| 30 | ||
| 31 | #Load the login module | |
| 32 | use MODS::Login; | |
| 33 | my $login = new MODS::Login; | |
| 34 | ||
| 35 | #Load Urls | |
| 36 | use MODS::PTMatrix::Urls; | |
| 37 | my $getlist = new MODS::PTMatrix::Urls; | |
| 38 | my $url = $getlist->urls(); | |
| 39 | ||
| 40 | #Load the template module | |
| 41 | use MODS::Template; | |
| 42 | my $tfile = new MODS::Template; | |
| 43 | ||
| 44 | #Page Wrapper Module | |
| 45 | use MODS::PTMatrix::Wrapper; | |
| 46 | my $load = new MODS::PTMatrix::Wrapper; | |
| 47 | ||
| 48 | #Load the database connect module | |
| 49 | use MODS::DBConnect; | |
| 50 | my $db = new MODS::DBConnect; | |
| 51 | ||
| 52 | #Load the configuration file | |
| 53 | use MODS::PTMatrix::Config; | |
| 54 | my $config = new MODS::PTMatrix::Config; | |
| 55 | my $database_name = $config->settings('database_name'); | |
| 56 | ||
| 57 | #Shared PM helpers | |
| 58 | use MODS::PTMatrix::PM; | |
| 59 | ||
| 60 | ||
| 61 | ||
| 62 | ############################################################# | |
| 63 | ## Get form data | |
| 64 | ############################################################# | |
| 65 | #NB: don't quotemeta the q parameter - search needs the literal term. | |
| 66 | #Strip non-ASCII / control chars but keep the spaces and punctuation | |
| 67 | $form->{q} ||= ''; | |
| 68 | $form->{q} =~ s/[^\x20-\x7e]//g; | |
| 69 | $form->{q} =~ s/^\s+|\s+$//g; | |
| 70 | ||
| 71 | ||
| 72 | ||
| 73 | ############################################################ | |
| 74 | # Program Controls | |
| 75 | ############################################################ | |
| 76 | $|=1; | |
| 77 | my $userinfo = $login->login_verify(); | |
| 78 | my $is_json = ($form->{json} && $form->{json} eq '1') ? 1 : 0; | |
| 79 | if(!$userinfo){ | |
| 80 | if($is_json){ | |
| 81 | print qq~Content-Type: application/json\nCache-Control: no-store\n\n{"ok":false,"error":"auth"}~; | |
| 82 | }else{ | |
| 83 | print qq~Content-type:text/html; Cache-Control:no-cache;\n\n<script>window.location="$url->{login}";</script>~; | |
| 84 | } | |
| 85 | exit; | |
| 86 | } | |
| 87 | ||
| 88 | #Empty query - fast-path with no DB hit | |
| 89 | if(!length $form->{q}){&empty_query; exit;} | |
| 90 | ||
| 91 | if($is_json){&search_json;} | |
| 92 | else{&search_html;} | |
| 93 | ||
| 94 | ||
| 95 | ############################################################ | |
| 96 | # Subroutines | |
| 97 | ############################################################ | |
| 98 | sub empty_query{ | |
| 99 | if($is_json){ | |
| 100 | print qq~Content-Type: application/json\nCache-Control: no-store\n\n~; | |
| 101 | print '{"ok":true,"hits":{"tickets":[],"projects":[],"users":[],"custom":[]},"total":0}'; | |
| 102 | return; | |
| 103 | } | |
| 104 | print qq~Content-Type: text/html; charset=utf-8\n\n~; | |
| 105 | my $body = join('', $tfile->template('tf_search.html', { q_term => '', has_hits => 0, total => 0 }, $userinfo)); | |
| 106 | $load->render({ userinfo => $userinfo, page_key => 'search', title => 'Search', body => $body }); | |
| 107 | } | |
| 108 | ||
| 109 | sub search_json{ | |
| 110 | my $hits = &run_search(6); | |
| 111 | ||
| 112 | print qq~Content-Type: application/json\nCache-Control: no-store\n\n~; | |
| 113 | my $total = scalar(@{$hits->{tickets}}) + scalar(@{$hits->{projects}}) + scalar(@{$hits->{users}}) + scalar(@{$hits->{custom}}); | |
| 114 | my $j = '{"ok":true,"hits":{' | |
| 115 | . '"tickets":' . &to_json($hits->{tickets}) . ',' | |
| 116 | . '"projects":' . &to_json($hits->{projects}) . ',' | |
| 117 | . '"users":' . &to_json($hits->{users}) . ',' | |
| 118 | . '"custom":' . &to_json($hits->{custom}) | |
| 119 | . '},"total":' . $total . '}'; | |
| 120 | print $j; | |
| 121 | } | |
| 122 | ||
| 123 | sub search_html{ | |
| 124 | my $hits = &run_search(50); | |
| 125 | my $total = scalar(@{$hits->{tickets}}) + scalar(@{$hits->{projects}}) + scalar(@{$hits->{users}}) + scalar(@{$hits->{custom}}); | |
| 126 | ||
| 127 | print qq~Content-Type: text/html; charset=utf-8\nCache-Control: no-store\n\n~; | |
| 128 | ||
| 129 | #Create all template variables | |
| 130 | my $tvars; | |
| 131 | $tvars->{q_term} = MODS::PTMatrix::PM::h($form->{q}); | |
| 132 | $tvars->{tickets} = $hits->{tickets}; | |
| 133 | $tvars->{n_tickets} = scalar(@{$hits->{tickets}}); | |
| 134 | $tvars->{has_tickets} = scalar(@{$hits->{tickets}}) ? 1 : 0; | |
| 135 | $tvars->{projects} = $hits->{projects}; | |
| 136 | $tvars->{n_projects} = scalar(@{$hits->{projects}}); | |
| 137 | $tvars->{has_projects} = scalar(@{$hits->{projects}}) ? 1 : 0; | |
| 138 | $tvars->{users} = $hits->{users}; | |
| 139 | $tvars->{n_users} = scalar(@{$hits->{users}}); | |
| 140 | $tvars->{has_users} = scalar(@{$hits->{users}}) ? 1 : 0; | |
| 141 | $tvars->{custom} = $hits->{custom}; | |
| 142 | $tvars->{n_custom} = scalar(@{$hits->{custom}}); | |
| 143 | $tvars->{has_custom} = scalar(@{$hits->{custom}}) ? 1 : 0; | |
| 144 | $tvars->{has_hits} = $total ? 1 : 0; | |
| 145 | $tvars->{total} = $total; | |
| 146 | ||
| 147 | my $body = join('', $tfile->template('tf_search.html', $tvars, $userinfo)); | |
| 148 | ||
| 149 | $load->render({ | |
| 150 | userinfo => $userinfo, | |
| 151 | page_key => 'search', | |
| 152 | title => 'Search', | |
| 153 | body => $body, | |
| 154 | }); | |
| 155 | } | |
| 156 | ||
| 157 | #Run the actual queries (tickets / projects / users / custom field values). | |
| 158 | #Returns a hashref of { tickets => [...], projects => [...], users => [...], custom => [...] }. | |
| 159 | sub run_search{ | |
| 160 | my($cap) = @_; | |
| 161 | my $cid = MODS::PTMatrix::PM::get_company_id($userinfo); | |
| 162 | my $term = $form->{q}; | |
| 163 | ||
| 164 | my $dbh = $db->db_connect(); | |
| 165 | ||
| 166 | #Sanitize for LIKE - escape % and _ + backslash, then SQL-escape outer | |
| 167 | my $like = $term; | |
| 168 | $like =~ s/\\/\\\\/g; $like =~ s/'/''/g; | |
| 169 | $like =~ s/%/\\%/g; $like =~ s/_/\\_/g; | |
| 170 | my $like_param = "%$like%"; | |
| 171 | ||
| 172 | #Detect "PFX-123" style: split on '-' and pull a project prefix + number | |
| 173 | my ($q_prefix, $q_number) = ('', ''); | |
| 174 | if($term =~ /^([A-Za-z][A-Za-z0-9]{0,7})-(\d+)$/){ | |
| 175 | ($q_prefix, $q_number) = (uc $1, $2); | |
| 176 | }elsif($term =~ /^(\d+)$/){ | |
| 177 | $q_number = $1; | |
| 178 | } | |
| 179 | ||
| 180 | #------------------------------------------------------------------------------------------------------ | |
| 181 | #Tickets / tasks | |
| 182 | my @ticket_where; | |
| 183 | if(length $q_prefix && length $q_number){push @ticket_where, "(p.key_prefix='$q_prefix' AND t.ticket_number='$q_number')";} | |
| 184 | if(length $q_number){push @ticket_where, "t.ticket_number='$q_number'";} | |
| 185 | push @ticket_where, "t.title LIKE '$like_param'"; | |
| 186 | my $tw = '(' . join(' OR ', @ticket_where) . ')'; | |
| 187 | my $sql_t = qq~SELECT t.id, t.ticket_number, t.title, t.status, t.priority, p.key_prefix, p.name AS project_name, p.color AS project_color, CASE WHEN p.key_prefix='$q_prefix' AND t.ticket_number='$q_number' THEN 100 WHEN t.ticket_number='$q_number' THEN 80 WHEN t.title LIKE '$like%' THEN 60 WHEN t.title LIKE '$like_param' THEN 40 ELSE 10 END AS score FROM `${database_name}`.tasks t JOIN `${database_name}`.projects p ON p.id = t.project_id WHERE t.company_id='$cid' AND $tw ORDER BY score DESC, t.updated_at DESC LIMIT $cap~; | |
| 188 | my @tickets = $db->db_readwrite_multiple($dbh, $sql_t, $ENV{SCRIPT_NAME}, __LINE__); | |
| 189 | #------------------------------------------------------------------------------------------------------ | |
| 190 | ||
| 191 | #------------------------------------------------------------------------------------------------------ | |
| 192 | #Projects | |
| 193 | my $sql_p = qq~SELECT id, name, key_prefix, color, status FROM `${database_name}`.projects WHERE company_id='$cid' AND (name LIKE '$like_param' OR key_prefix LIKE '$like%') ORDER BY (name LIKE '$like%') DESC, name ASC LIMIT $cap~; | |
| 194 | my @projects = $db->db_readwrite_multiple($dbh, $sql_p, $ENV{SCRIPT_NAME}, __LINE__); | |
| 195 | #------------------------------------------------------------------------------------------------------ | |
| 196 | ||
| 197 | #------------------------------------------------------------------------------------------------------ | |
| 198 | #Users | |
| 199 | my $sql_u = qq~SELECT id, display_name, email, avatar_color FROM `${database_name}`.users WHERE company_id='$cid' AND account_status='active' AND (display_name LIKE '$like_param' OR email LIKE '$like_param') ORDER BY (display_name LIKE '$like%') DESC, display_name ASC LIMIT $cap~; | |
| 200 | my @users = $db->db_readwrite_multiple($dbh, $sql_u, $ENV{SCRIPT_NAME}, __LINE__); | |
| 201 | #------------------------------------------------------------------------------------------------------ | |
| 202 | ||
| 203 | #------------------------------------------------------------------------------------------------------ | |
| 204 | #Custom field values - only if the table exists | |
| 205 | my @custom; | |
| 206 | my $has_cf = $db->db_readwrite($dbh, qq~SELECT COUNT(*) AS n FROM information_schema.tables WHERE table_schema='${database_name}' AND table_name='task_custom_field_values'~, $ENV{SCRIPT_NAME}, __LINE__); | |
| 207 | if($has_cf && $has_cf->{n}){ | |
| 208 | my $sql_c = qq~SELECT v.task_id, v.value_text, d.label AS field_label, d.field_key, t.title AS task_title, t.ticket_number, p.key_prefix, p.name AS project_name FROM `${database_name}`.task_custom_field_values v JOIN `${database_name}`.custom_field_definitions d ON d.id = v.field_id JOIN `${database_name}`.tasks t ON t.id = v.task_id JOIN `${database_name}`.projects p ON p.id = t.project_id WHERE d.company_id='$cid' AND v.value_text LIKE '$like_param' ORDER BY t.updated_at DESC LIMIT $cap~; | |
| 209 | @custom = $db->db_readwrite_multiple($dbh, $sql_c, $ENV{SCRIPT_NAME}, __LINE__); | |
| 210 | } | |
| 211 | #------------------------------------------------------------------------------------------------------ | |
| 212 | ||
| 213 | $db->db_disconnect($dbh); | |
| 214 | ||
| 215 | #Shape each list into the {kind, title, sub, href, badge, color} hits format | |
| 216 | my @ticket_hits; | |
| 217 | foreach my $r(@tickets){ | |
| 218 | my $disp = MODS::PTMatrix::PM::ticket_label($r->{key_prefix}, $r->{ticket_number}); | |
| 219 | push @ticket_hits, { | |
| 220 | kind => 'ticket', | |
| 221 | title => MODS::PTMatrix::PM::h($r->{title} || ''), | |
| 222 | sub => "$disp · " . MODS::PTMatrix::PM::h($r->{project_name} || '') . " · $r->{status}", | |
| 223 | href => "/task.cgi?id=$r->{id}", | |
| 224 | badge => $disp, | |
| 225 | color => $r->{project_color} || '#5aa9ff', | |
| 226 | ticket => $disp, | |
| 227 | }; | |
| 228 | } | |
| 229 | ||
| 230 | my @project_hits; | |
| 231 | foreach my $r(@projects){ | |
| 232 | push @project_hits, { | |
| 233 | kind => 'project', | |
| 234 | title => MODS::PTMatrix::PM::h($r->{name}), | |
| 235 | sub => "Project · " . MODS::PTMatrix::PM::h($r->{key_prefix} || '') . " · $r->{status}", | |
| 236 | href => "/kanban.cgi?project=$r->{id}", | |
| 237 | badge => MODS::PTMatrix::PM::h($r->{key_prefix} || ''), | |
| 238 | color => $r->{color} || '#5aa9ff', | |
| 239 | }; | |
| 240 | } | |
| 241 | ||
| 242 | my @user_hits; | |
| 243 | foreach my $r(@users){ | |
| 244 | push @user_hits, { | |
| 245 | kind => 'user', | |
| 246 | title => MODS::PTMatrix::PM::h($r->{display_name} || $r->{email}), | |
| 247 | sub => "Team member · " . MODS::PTMatrix::PM::h($r->{email} || ''), | |
| 248 | href => "/profile.cgi?id=$r->{id}", | |
| 249 | badge => &initials($r->{display_name} || $r->{email}), | |
| 250 | color => $r->{avatar_color} || '#5aa9ff', | |
| 251 | }; | |
| 252 | } | |
| 253 | ||
| 254 | my @custom_hits; | |
| 255 | foreach my $r(@custom){ | |
| 256 | my $disp = MODS::PTMatrix::PM::ticket_label($r->{key_prefix}, $r->{ticket_number}); | |
| 257 | my $val = $r->{value_text} || ''; | |
| 258 | $val = substr($val, 0, 80); | |
| 259 | push @custom_hits, { | |
| 260 | kind => 'custom', | |
| 261 | title => MODS::PTMatrix::PM::h($r->{field_label}) . ': ' . MODS::PTMatrix::PM::h($val), | |
| 262 | sub => "On $disp · " . MODS::PTMatrix::PM::h($r->{task_title} || ''), | |
| 263 | href => "/task.cgi?id=$r->{task_id}", | |
| 264 | badge => $disp, | |
| 265 | color => '#8b5cf6', | |
| 266 | }; | |
| 267 | } | |
| 268 | ||
| 269 | return { | |
| 270 | tickets => \@ticket_hits, | |
| 271 | projects => \@project_hits, | |
| 272 | users => \@user_hits, | |
| 273 | custom => \@custom_hits, | |
| 274 | }; | |
| 275 | } | |
| 276 | ||
| 277 | sub initials{ | |
| 278 | my($n) = @_; | |
| 279 | return '?' unless $n; | |
| 280 | my @parts = split /[\s\@.]+/, $n; | |
| 281 | my $i = ''; | |
| 282 | $i .= uc(substr($parts[0], 0, 1)) if $parts[0]; | |
| 283 | $i .= uc(substr($parts[1], 0, 1)) if defined $parts[1]; | |
| 284 | return $i || uc(substr($n, 0, 1)); | |
| 285 | } | |
| 286 | ||
| 287 | #Stringify an arrayref of hashrefs into JSON (just enough for the typeahead). | |
| 288 | #We avoid pulling in JSON modules so this works on every Plesk box. | |
| 289 | sub to_json{ | |
| 290 | my($a) = @_; | |
| 291 | my @out; | |
| 292 | foreach my $r(@$a){ | |
| 293 | my @pairs; | |
| 294 | foreach my $k(sort keys %$r){ | |
| 295 | my $v = $r->{$k}; | |
| 296 | $v = '' unless defined $v; | |
| 297 | $v =~ s/\\/\\\\/g; $v =~ s/"/\\"/g; $v =~ s/\n/\\n/g; $v =~ s/\r//g; $v =~ s|</|<\\/|g; | |
| 298 | push @pairs, "\"$k\":\"$v\""; | |
| 299 | } | |
| 300 | push @out, '{' . join(',', @pairs) . '}'; | |
| 301 | } | |
| 302 | return '[' . join(',', @out) . ']'; | |
| 303 | } |