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

O Operator
Diff

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

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

Added
+203
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to 4091c5366b05
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############################################################
26#Load the CGI module
27use CGI;
28my $query = new CGI;
29my $form = $query->Vars;
30
31#Load the login module
32use MODS::Login;
33my $login = new MODS::Login;
34
35#Load Urls
36use MODS::PTMatrix::Urls;
37my $getlist = new MODS::PTMatrix::Urls;
38my $url = $getlist->urls();
39
40#Load the template module
41use MODS::Template;
42my $tfile = new MODS::Template;
43
44#Page Wrapper Module
45use MODS::PTMatrix::Wrapper;
46my $load = new MODS::PTMatrix::Wrapper;
47
48#Load the database connect module
49use MODS::DBConnect;
50my $db = new MODS::DBConnect;
51
52#Load the configuration file
53use MODS::PTMatrix::Config;
54my $config = new MODS::PTMatrix::Config;
55my $database_name = $config->settings('database_name');
56
57#Shared PM helpers
58use MODS::PTMatrix::PM;
59
60
61
62#############################################################
63## Get form data
64#############################################################
65#Escapes bad characters
66foreach my $line(keys %$form){
67 $form->{$line} =~ s/[^!-~\s]//g; #Remove non ascii chars
68 $form->{$line} = quotemeta("$form->{$line}");
69}
70
71
72
73############################################################
74# Program Controls
75############################################################
76$|=1;
77my $userinfo = $login->login_verify();
78print qq~Content-type:text/html; Cache-Control:no-cache;\n\n~;
79if(!$userinfo){print qq~<script>window.location="$url->{login}";</script>~;}
80else{&dashboard_view;}
81
82
83############################################################
84# Subroutines
85############################################################
86sub dashboard_view{
87 my $uid = ($userinfo->{user_id} || 0) + 0;
88 my $cid = ($userinfo->{company_id} || 0) + 0;
89
90 my $dbh = $db->db_connect();
91
92 #------------------------------------------------------------------------------------------------------
93 #KPI tiles - my open, due-this-week, overdue, completed-this-week
94 my $kpi_my_open = $db->db_readwrite($dbh, qq~SELECT COUNT(*) AS n FROM `${database_name}`.tasks WHERE company_id='$cid' AND assigned_user_id='$uid' AND status IN ('open','in_progress','review','blocked')~, $ENV{SCRIPT_NAME}, __LINE__);
95 my $kpi_due_week = $db->db_readwrite($dbh, qq~SELECT COUNT(*) AS n FROM `${database_name}`.tasks WHERE company_id='$cid' AND assigned_user_id='$uid' AND status IN ('open','in_progress','review','blocked') AND due_date IS NOT NULL AND due_date <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)~, $ENV{SCRIPT_NAME}, __LINE__);
96 my $kpi_overdue = $db->db_readwrite($dbh, qq~SELECT COUNT(*) AS n FROM `${database_name}`.tasks WHERE company_id='$cid' AND assigned_user_id='$uid' AND status IN ('open','in_progress','review','blocked') AND due_date IS NOT NULL AND due_date < CURDATE()~, $ENV{SCRIPT_NAME}, __LINE__);
97 my $kpi_completed_week = $db->db_readwrite($dbh, qq~SELECT COUNT(*) AS n FROM `${database_name}`.tasks WHERE company_id='$cid' AND assigned_user_id='$uid' AND completed_at IS NOT NULL AND completed_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)~, $ENV{SCRIPT_NAME}, __LINE__);
98 #------------------------------------------------------------------------------------------------------
99
100 #------------------------------------------------------------------------------------------------------
101 #Active projects (12 most-recently-updated)
102 my $sql_p = qq~SELECT p.id, p.name, p.slug, p.key_prefix, p.color, p.icon, p.status, p.priority, p.target_due_date, (SELECT COUNT(*) FROM `${database_name}`.tasks t WHERE t.project_id=p.id AND t.status IN ('open','in_progress','review','blocked')) AS open_count, (SELECT COUNT(*) FROM `${database_name}`.tasks t WHERE t.project_id=p.id) AS total_count FROM `${database_name}`.projects p WHERE p.company_id='$cid' AND p.status IN ('active','planning') AND p.deleted_at IS NULL ORDER BY p.updated_at DESC LIMIT 12~;
103 my @projects = $db->db_readwrite_multiple($dbh, $sql_p, $ENV{SCRIPT_NAME}, __LINE__);
104 foreach my $p(@projects){
105 my $total = ($p->{total_count} || 0) + 0;
106 my $open = ($p->{open_count} || 0) + 0;
107 my $done = $total - $open;
108 $p->{progress_pct} = $total > 0 ? int($done * 100 / $total) : 0;
109 $p->{color} ||= '#5aa9ff';
110 }
111 #------------------------------------------------------------------------------------------------------
112
113 #------------------------------------------------------------------------------------------------------
114 #My next tasks (top 8, sorted by due date then priority)
115 my $sql_mt = qq~SELECT t.id, t.title, t.ticket_number, t.priority, t.due_date, t.status, p.name AS project_name, p.slug AS project_slug, p.key_prefix, p.color AS project_color FROM `${database_name}`.tasks t JOIN `${database_name}`.projects p ON p.id = t.project_id AND p.deleted_at IS NULL WHERE t.company_id='$cid' AND t.assigned_user_id='$uid' AND t.status IN ('open','in_progress','review','blocked') ORDER BY (t.due_date IS NULL), t.due_date ASC, FIELD(t.priority,'critical','urgent','high','medium','low') LIMIT 8~;
116 my @my_tasks = $db->db_readwrite_multiple($dbh, $sql_mt, $ENV{SCRIPT_NAME}, __LINE__);
117 foreach my $t(@my_tasks){
118 $t->{display_ticket} = ($t->{key_prefix} ? "$t->{key_prefix}-$t->{ticket_number}" : "#$t->{ticket_number}");
119 $t->{due_label} = $t->{due_date} ? $t->{due_date} : '';
120 }
121 #------------------------------------------------------------------------------------------------------
122
123 #------------------------------------------------------------------------------------------------------
124 #"What changed today?" per-project digest (top 6 ranked by activity)
125 my $sql_dg = qq~SELECT p.id, p.name, p.color, p.key_prefix, (SELECT COUNT(*) FROM `${database_name}`.tasks t WHERE t.project_id=p.id AND DATE(t.created_at) = CURDATE()) AS new_today, (SELECT COUNT(*) FROM `${database_name}`.tasks t WHERE t.project_id=p.id AND DATE(t.completed_at) = CURDATE() AND t.status='done') AS done_today, (SELECT COUNT(*) FROM `${database_name}`.tasks t WHERE t.project_id=p.id AND t.due_date < CURDATE() AND t.status IN ('open','in_progress','review','blocked')) AS overdue_n, (SELECT COUNT(*) FROM `${database_name}`.tasks t WHERE t.project_id=p.id AND t.status='blocked') AS blocked_n FROM `${database_name}`.projects p WHERE p.company_id='$cid' AND p.status IN ('active','planning') AND p.deleted_at IS NULL ORDER BY ((SELECT COUNT(*) FROM `${database_name}`.tasks t WHERE t.project_id=p.id AND DATE(t.created_at)=CURDATE()) + (SELECT COUNT(*) FROM `${database_name}`.tasks t WHERE t.project_id=p.id AND DATE(t.completed_at)=CURDATE() AND t.status='done') + (SELECT COUNT(*) FROM `${database_name}`.tasks t WHERE t.project_id=p.id AND t.due_date < CURDATE() AND t.status IN ('open','in_progress','review','blocked'))) DESC, p.updated_at DESC LIMIT 6~;
126 my @digest = $db->db_readwrite_multiple($dbh, $sql_dg, $ENV{SCRIPT_NAME}, __LINE__);
127 foreach my $d(@digest){
128 $d->{name} = MODS::PTMatrix::PM::h($d->{name});
129 $d->{color} ||= '#5aa9ff';
130 foreach my $k(qw(new_today done_today overdue_n blocked_n)){$d->{$k} = ($d->{$k} || 0) + 0;}
131 $d->{is_quiet} = ($d->{new_today} == 0 && $d->{done_today} == 0 && $d->{overdue_n} == 0 && $d->{blocked_n} == 0) ? 1 : 0;
132 $d->{has_today_activity} = ($d->{new_today} > 0 || $d->{done_today} > 0) ? 1 : 0;
133 $d->{has_risk} = ($d->{overdue_n} > 0 || $d->{blocked_n} > 0) ? 1 : 0;
134
135 #Tone marker: green if mostly positive, red if risk-heavy, neutral otherwise
136 if ($d->{overdue_n} > 0 || $d->{blocked_n} >= 3){$d->{tone} = 'risk';}
137 elsif($d->{done_today} > 0) {$d->{tone} = 'good';}
138 else {$d->{tone} = 'neutral';}
139 $d->{tone_good} = ($d->{tone} eq 'good') ? 1 : 0;
140 $d->{tone_risk} = ($d->{tone} eq 'risk') ? 1 : 0;
141 $d->{tone_neutral} = ($d->{tone} eq 'neutral') ? 1 : 0;
142 }
143 #------------------------------------------------------------------------------------------------------
144
145 #------------------------------------------------------------------------------------------------------
146 #Recent activity feed (last 12 audit rows)
147 my $sql_a = qq~SELECT a.action, a.target_type, a.target_id, a.occurred_at, u.display_name AS actor_name, u.avatar_color FROM `${database_name}`.audit_log a LEFT JOIN `${database_name}`.users u ON u.id = a.user_id WHERE a.company_id='$cid' ORDER BY a.occurred_at DESC LIMIT 12~;
148 my @activity = $db->db_readwrite_multiple($dbh, $sql_a, $ENV{SCRIPT_NAME}, __LINE__);
149 #------------------------------------------------------------------------------------------------------
150
151 #------------------------------------------------------------------------------------------------------
152 #Workload summary - top 8 by open task count
153 my $sql_w = qq~SELECT u.id, u.display_name, u.avatar_color, COUNT(t.id) AS open_count, SUM(IF(t.due_date < CURDATE(), 1, 0)) AS overdue_count FROM `${database_name}`.users u LEFT JOIN `${database_name}`.tasks t ON t.assigned_user_id = u.id AND t.status IN ('open','in_progress','review','blocked') WHERE u.company_id='$cid' AND u.account_status='active' GROUP BY u.id ORDER BY open_count DESC LIMIT 8~;
154 my @workload = $db->db_readwrite_multiple($dbh, $sql_w, $ENV{SCRIPT_NAME}, __LINE__);
155 foreach my $w(@workload){
156 $w->{display_name} ||= '';
157 $w->{initials} = &initials($w->{display_name});
158 $w->{open_count} ||= 0;
159 $w->{overdue_count} ||= 0;
160 }
161 #------------------------------------------------------------------------------------------------------
162
163 $db->db_disconnect($dbh);
164
165
166 #Create all template variables
167 my $tvars;
168 $tvars->{kpi_my_open} = ($kpi_my_open->{n} || 0);
169 $tvars->{kpi_due_week} = ($kpi_due_week->{n} || 0);
170 $tvars->{kpi_overdue} = ($kpi_overdue->{n} || 0);
171 $tvars->{kpi_completed_week} = ($kpi_completed_week->{n} || 0);
172 $tvars->{projects} = \@projects;
173 $tvars->{has_projects} = scalar(@projects) ? 1 : 0;
174 $tvars->{my_tasks} = \@my_tasks;
175 $tvars->{has_my_tasks} = scalar(@my_tasks) ? 1 : 0;
176 $tvars->{activity} = \@activity;
177 $tvars->{has_activity} = scalar(@activity) ? 1 : 0;
178 $tvars->{digest} = \@digest;
179 $tvars->{has_digest} = scalar(@digest) ? 1 : 0;
180 $tvars->{workload} = \@workload;
181 $tvars->{has_workload} = scalar(@workload) ? 1 : 0;
182 $tvars->{show_welcome} = ($form->{welcome} ? 1 : 0);
183 $tvars->{display_name} = $userinfo->{display_name};
184
185 my $body = join('', $tfile->template('tf_dashboard.html', $tvars, $userinfo));
186
187 $load->render({
188 userinfo => $userinfo,
189 page_key => 'dashboard',
190 title => 'Dashboard',
191 body => $body,
192 });
193}
194
195#Two-letter initials from a display name - used in the workload tile avatars
196sub initials{
197 my($name) = @_;
198 return '?' unless $name;
199 my @parts = split /\s+/, $name;
200 my $i = uc(substr($parts[0], 0, 1));
201 $i .= uc(substr($parts[1], 0, 1)) if defined $parts[1];
202 return $i;
203}