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

O Operator
Diff

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

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

Added
+194
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to f1c5ca58ed58
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::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;
34
35
36
37#############################################################
38## Get form data
39#############################################################
40foreach my $line(keys %$form){
41 $form->{$line} =~ s/[^!-~\s]//g;
42 $form->{$line} = quotemeta("$form->{$line}");
43}
44
45
46
47############################################################
48# Program Controls
49############################################################
50$|=1;
51my $userinfo = $login->login_verify();
52print qq~Content-type:text/html; Cache-Control:no-cache;\n\n~;
53if(!$userinfo){print qq~<script>window.location="$url->{login}";</script>~;}
54elsif(!($userinfo->{is_super_admin} || $userinfo->{_admin_is_super_admin})){print qq~<script>window.location="$url->{dashboard}";</script>~;}
55else{&metrics_dashboard;}
56
57
58############################################################
59# Subroutines
60############################################################
61sub company_row{
62 #Decorate a company-shaped row for the preview overlays
63 my($r) = @_;
64 $r->{name} = MODS::PTMatrix::PM::h($r->{name} // '');
65 $r->{brand_color} ||= '#5aa9ff';
66 return $r;
67}
68
69sub user_row{
70 #Decorate a user-shaped row for the preview overlays
71 my($r) = @_;
72 $r->{display_name} = MODS::PTMatrix::PM::h($r->{display_name} // $r->{email} // '');
73 $r->{email} = MODS::PTMatrix::PM::h($r->{email} // '');
74 $r->{company_name} = MODS::PTMatrix::PM::h($r->{company_name} // '');
75 $r->{avatar_color} ||= '#5aa9ff';
76 $r->{company_color} ||= '#5aa9ff';
77 $r->{initials} = MODS::PTMatrix::PM::initials($r->{display_name});
78 $r->{last_login_at} //= '';
79 return $r;
80}
81
82sub metrics_dashboard{
83 my $dbh = $db->db_connect();
84
85 #------------------------------------------------------------------------------------------------------
86 #KPI scalar block - one query, 13 subqueries
87 my $sql_k = qq~SELECT (SELECT COUNT(*) FROM `${database_name}`.companies) AS companies_n, (SELECT COUNT(*) FROM `${database_name}`.companies WHERE plan_tier='trial' AND status='active') AS trial_n, (SELECT COUNT(*) FROM `${database_name}`.companies WHERE plan_tier IN ('starter','pro','business','enterprise') AND status='active') AS paying_n, (SELECT COUNT(*) FROM `${database_name}`.users WHERE account_status='active') AS users_n, (SELECT COUNT(*) FROM `${database_name}`.user_sessions WHERE expires_at > NOW() AND revoked_at IS NULL) AS sessions_n, (SELECT COUNT(*) FROM `${database_name}`.projects) AS projects_n, (SELECT COUNT(*) FROM `${database_name}`.tasks) AS tasks_n, (SELECT COUNT(*) FROM `${database_name}`.tasks WHERE status='done') AS tasks_done_n, (SELECT COUNT(*) FROM `${database_name}`.tasks WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)) AS tasks_7d, (SELECT COUNT(*) FROM `${database_name}`.companies WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS new_companies_30d, (SELECT COUNT(*) FROM `${database_name}`.users WHERE last_login_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)) AS wau, (SELECT COUNT(*) FROM `${database_name}`.users WHERE last_login_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS mau, (SELECT COUNT(*) FROM `${database_name}`.audit_log WHERE occurred_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)) AS actions_24h FROM dual~;
88 my $k = $db->db_readwrite($dbh, $sql_k, $ENV{SCRIPT_NAME}, __LINE__);
89 foreach my $f(keys %$k){$k->{$f} = ($k->{$f} || 0) + 0;}
90 #------------------------------------------------------------------------------------------------------
91
92 #------------------------------------------------------------------------------------------------------
93 #Plan breakdown bars (one row per plan tier)
94 my @plan_breakdown = $db->db_readwrite_multiple($dbh, qq~SELECT plan_tier, COUNT(*) AS n FROM `${database_name}`.companies GROUP BY plan_tier ORDER BY n DESC~, $ENV{SCRIPT_NAME}, __LINE__);
95 my $max_pb = 1;
96 foreach my $r(@plan_breakdown){$max_pb = $r->{n} if $r->{n} > $max_pb;}
97 foreach my $r(@plan_breakdown){
98 $r->{pct} = $max_pb > 0 ? int($r->{n} * 100 / $max_pb) : 0;
99 $r->{pct_css} = $r->{pct} . '%';
100 }
101 #------------------------------------------------------------------------------------------------------
102
103 #------------------------------------------------------------------------------------------------------
104 #Recent signups (10 most-recent companies for the panel)
105 my @recent_signups = $db->db_readwrite_multiple($dbh, qq~SELECT c.id, c.name, c.plan_tier, c.created_at, UNIX_TIMESTAMP(c.created_at) AS created_epoch, c.brand_color, (SELECT COUNT(*) FROM `${database_name}`.users u WHERE u.company_id=c.id) AS users_n FROM `${database_name}`.companies c ORDER BY c.created_at DESC LIMIT 10~, $ENV{SCRIPT_NAME}, __LINE__);
106 foreach my $c(@recent_signups){
107 $c->{name} = MODS::PTMatrix::PM::h($c->{name});
108 $c->{brand_color} ||= '#5aa9ff';
109 }
110 #------------------------------------------------------------------------------------------------------
111
112 #------------------------------------------------------------------------------------------------------
113 #Per-tile preview rows (top 5) for the hover overlay
114 my @pv_total = $db->db_readwrite_multiple($dbh, qq~SELECT c.id, c.name, c.plan_tier, c.brand_color, c.created_at, UNIX_TIMESTAMP(c.created_at) AS created_epoch, (SELECT COUNT(*) FROM `${database_name}`.users u WHERE u.company_id=c.id) AS users_n FROM `${database_name}`.companies c ORDER BY c.created_at DESC LIMIT 5~, $ENV{SCRIPT_NAME}, __LINE__);
115 &company_row($_) for @pv_total;
116
117 my @pv_paying = $db->db_readwrite_multiple($dbh, qq~SELECT c.id, c.name, c.plan_tier, c.brand_color, c.created_at, UNIX_TIMESTAMP(c.created_at) AS created_epoch, (SELECT COUNT(*) FROM `${database_name}`.users u WHERE u.company_id=c.id AND u.account_status='active') AS users_n FROM `${database_name}`.companies c WHERE c.plan_tier IN ('starter','pro','business','enterprise') AND c.status='active' ORDER BY c.created_at DESC LIMIT 5~, $ENV{SCRIPT_NAME}, __LINE__);
118 &company_row($_) for @pv_paying;
119
120 my @pv_trial = $db->db_readwrite_multiple($dbh, qq~SELECT c.id, c.name, c.plan_tier, c.brand_color, c.trial_ends_at, UNIX_TIMESTAMP(c.trial_ends_at) AS trial_ends_epoch, DATEDIFF(c.trial_ends_at, NOW()) AS days_left, (SELECT COUNT(*) FROM `${database_name}`.users u WHERE u.company_id=c.id AND u.account_status='active') AS users_n FROM `${database_name}`.companies c WHERE c.plan_tier='trial' AND c.status='active' ORDER BY c.trial_ends_at ASC LIMIT 5~, $ENV{SCRIPT_NAME}, __LINE__);
121 foreach my $r(@pv_trial){
122 &company_row($r);
123 $r->{days_left} = defined $r->{days_left} ? $r->{days_left} + 0 : 0;
124 $r->{trial_ends_at} //= '';
125 }
126
127 my @pv_active = $db->db_readwrite_multiple($dbh, qq~SELECT u.id, u.email, u.display_name, u.avatar_color, u.last_login_at, UNIX_TIMESTAMP(u.last_login_at) AS last_login_epoch, c.name AS company_name, c.brand_color AS company_color FROM `${database_name}`.users u LEFT JOIN `${database_name}`.companies c ON c.id = u.company_id WHERE u.account_status='active' ORDER BY u.last_login_at DESC LIMIT 5~, $ENV{SCRIPT_NAME}, __LINE__);
128 &user_row($_) for @pv_active;
129
130 my @pv_sessions = $db->db_readwrite_multiple($dbh, qq~SELECT s.id AS session_id, s.issued_at, UNIX_TIMESTAMP(s.issued_at) AS issued_epoch, u.id, u.email, u.display_name, u.avatar_color, c.name AS company_name, c.brand_color AS company_color, u.last_login_at, UNIX_TIMESTAMP(u.last_login_at) AS last_login_epoch FROM `${database_name}`.user_sessions s JOIN `${database_name}`.users u ON u.id = s.user_id LEFT JOIN `${database_name}`.companies c ON c.id = u.company_id WHERE s.expires_at > NOW() AND s.revoked_at IS NULL ORDER BY s.issued_at DESC LIMIT 5~, $ENV{SCRIPT_NAME}, __LINE__);
131 &user_row($_) for @pv_sessions;
132
133 my @pv_wau = $db->db_readwrite_multiple($dbh, qq~SELECT u.id, u.email, u.display_name, u.avatar_color, u.last_login_at, UNIX_TIMESTAMP(u.last_login_at) AS last_login_epoch, c.name AS company_name, c.brand_color AS company_color FROM `${database_name}`.users u LEFT JOIN `${database_name}`.companies c ON c.id = u.company_id WHERE u.last_login_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) ORDER BY u.last_login_at DESC LIMIT 5~, $ENV{SCRIPT_NAME}, __LINE__);
134 &user_row($_) for @pv_wau;
135
136 my @pv_mau = $db->db_readwrite_multiple($dbh, qq~SELECT u.id, u.email, u.display_name, u.avatar_color, u.last_login_at, UNIX_TIMESTAMP(u.last_login_at) AS last_login_epoch, c.name AS company_name, c.brand_color AS company_color FROM `${database_name}`.users u LEFT JOIN `${database_name}`.companies c ON c.id = u.company_id WHERE u.last_login_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY u.last_login_at DESC LIMIT 5~, $ENV{SCRIPT_NAME}, __LINE__);
137 &user_row($_) for @pv_mau;
138
139 my @pv_newsignups = $db->db_readwrite_multiple($dbh, qq~SELECT c.id, c.name, c.plan_tier, c.brand_color, c.created_at, UNIX_TIMESTAMP(c.created_at) AS created_epoch, (SELECT COUNT(*) FROM `${database_name}`.users u WHERE u.company_id=c.id) AS users_n FROM `${database_name}`.companies c WHERE c.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY c.created_at DESC LIMIT 5~, $ENV{SCRIPT_NAME}, __LINE__);
140 &company_row($_) for @pv_newsignups;
141
142 my @pv_projects = $db->db_readwrite_multiple($dbh, qq~SELECT c.id, c.name, c.plan_tier, c.brand_color, (SELECT COUNT(*) FROM `${database_name}`.projects p WHERE p.company_id=c.id) AS projects_n FROM `${database_name}`.companies c ORDER BY projects_n DESC, c.id ASC LIMIT 5~, $ENV{SCRIPT_NAME}, __LINE__);
143 &company_row($_) for @pv_projects;
144
145 my @pv_tasks = $db->db_readwrite_multiple($dbh, qq~SELECT c.id, c.name, c.plan_tier, c.brand_color, (SELECT COUNT(*) FROM `${database_name}`.tasks t WHERE t.company_id=c.id) AS tasks_n FROM `${database_name}`.companies c ORDER BY tasks_n DESC, c.id ASC LIMIT 5~, $ENV{SCRIPT_NAME}, __LINE__);
146 &company_row($_) for @pv_tasks;
147
148 my @pv_tasks_done = $db->db_readwrite_multiple($dbh, qq~SELECT c.id, c.name, c.plan_tier, c.brand_color, (SELECT COUNT(*) FROM `${database_name}`.tasks t WHERE t.company_id=c.id AND t.status='done') AS tasks_done_n FROM `${database_name}`.companies c ORDER BY tasks_done_n DESC, c.id ASC LIMIT 5~, $ENV{SCRIPT_NAME}, __LINE__);
149 &company_row($_) for @pv_tasks_done;
150
151 my @pv_actions = $db->db_readwrite_multiple($dbh, qq~SELECT a.id, a.action, a.actor_type, a.occurred_at, UNIX_TIMESTAMP(a.occurred_at) AS occurred_epoch, a.target_type AS resource_type, u.display_name, u.avatar_color, u.email, c.name AS company_name, c.brand_color AS company_color FROM `${database_name}`.audit_log a LEFT JOIN `${database_name}`.users u ON u.id = a.user_id LEFT JOIN `${database_name}`.companies c ON c.id = u.company_id WHERE a.occurred_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR) ORDER BY a.occurred_at DESC LIMIT 5~, $ENV{SCRIPT_NAME}, __LINE__);
152 foreach my $r(@pv_actions){
153 $r->{action} = MODS::PTMatrix::PM::h($r->{action} // '');
154 $r->{actor_type} = MODS::PTMatrix::PM::h($r->{actor_type} // '');
155 $r->{resource_type} = MODS::PTMatrix::PM::h($r->{resource_type} // '');
156 $r->{display_name} = MODS::PTMatrix::PM::h($r->{display_name} // $r->{email} // 'system');
157 $r->{company_name} = MODS::PTMatrix::PM::h($r->{company_name} // '');
158 $r->{avatar_color} ||= '#7d8492';
159 $r->{company_color} ||= '#5aa9ff';
160 $r->{initials} = MODS::PTMatrix::PM::initials($r->{display_name});
161 }
162 #------------------------------------------------------------------------------------------------------
163
164 $db->db_disconnect($dbh);
165
166 #Create all template variables
167 my $tvars;
168 $tvars->{k} = $k;
169 $tvars->{plan_breakdown} = \@plan_breakdown;
170 $tvars->{has_pb} = scalar(@plan_breakdown) ? 1 : 0;
171 $tvars->{recent_signups} = \@recent_signups;
172 $tvars->{has_rs} = scalar(@recent_signups) ? 1 : 0;
173 $tvars->{pv_total} = \@pv_total; $tvars->{has_pv_total} = scalar(@pv_total) ? 1 : 0;
174 $tvars->{pv_paying} = \@pv_paying; $tvars->{has_pv_paying} = scalar(@pv_paying) ? 1 : 0;
175 $tvars->{pv_trial} = \@pv_trial; $tvars->{has_pv_trial} = scalar(@pv_trial) ? 1 : 0;
176 $tvars->{pv_active} = \@pv_active; $tvars->{has_pv_active} = scalar(@pv_active) ? 1 : 0;
177 $tvars->{pv_sessions} = \@pv_sessions; $tvars->{has_pv_sessions} = scalar(@pv_sessions) ? 1 : 0;
178 $tvars->{pv_wau} = \@pv_wau; $tvars->{has_pv_wau} = scalar(@pv_wau) ? 1 : 0;
179 $tvars->{pv_mau} = \@pv_mau; $tvars->{has_pv_mau} = scalar(@pv_mau) ? 1 : 0;
180 $tvars->{pv_newsignups} = \@pv_newsignups; $tvars->{has_pv_newsignups} = scalar(@pv_newsignups) ? 1 : 0;
181 $tvars->{pv_projects} = \@pv_projects; $tvars->{has_pv_projects} = scalar(@pv_projects) ? 1 : 0;
182 $tvars->{pv_tasks} = \@pv_tasks; $tvars->{has_pv_tasks} = scalar(@pv_tasks) ? 1 : 0;
183 $tvars->{pv_tasks_done} = \@pv_tasks_done; $tvars->{has_pv_tasks_done} = scalar(@pv_tasks_done) ? 1 : 0;
184 $tvars->{pv_actions} = \@pv_actions; $tvars->{has_pv_actions} = scalar(@pv_actions) ? 1 : 0;
185
186 my $body = join('', $tfile->template('tf_admin_metrics.html', $tvars, $userinfo));
187
188 $load->render({
189 userinfo => $userinfo,
190 page_key => 'admin_metrics',
191 title => 'System Metrics',
192 body => $body,
193 });
194}