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

O Operator
Diff

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

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

Added
+290
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to 835a5e32d836
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();
52my $is_detail = (($ENV{SCRIPT_NAME} || '') =~ m{/admin_user\.cgi$}) ? 1 : 0;
53
54print qq~Content-type:text/html; Cache-Control:no-cache;\n\n~;
55
56if(!$userinfo){
57 print qq~<script>window.location="$url->{login}";</script>~;
58}elsif(!($userinfo->{is_super_admin} || $userinfo->{_admin_is_super_admin})){
59 print qq~<script>window.location="$url->{dashboard}";</script>~;
60}elsif($is_detail){
61 my $uid = $form->{u} || 0;
62 $uid =~ s/[^0-9]//g;
63 if(!$uid){print qq~<script>window.location="$url->{admin_users}";</script>~;}
64 else {&user_detail($uid);}
65}else{
66 &admin_users_list;
67}
68
69
70############################################################
71# Subroutines
72############################################################
73sub count_scalar{
74 my($dbh, $sql) = @_;
75 my $r = $db->db_readwrite($dbh, $sql, $ENV{SCRIPT_NAME}, __LINE__);
76 return ($r && defined $r->{n}) ? $r->{n} : 0;
77}
78
79sub admin_users_list{
80 #SUPER-ADMIN platform user list. Filters (all optional, all driven from /admin_metrics.cgi tile clicks):
81 # ?company=N - only users belonging to company N
82 # ?q=... - email/name search
83 # ?status=active - account_status='active'
84 # ?active=N - last_login_at within last N days (WAU=7, MAU=30)
85 # ?sessions=live - only users with an active (non-revoked, unexpired) session
86
87 my $cf = MODS::PTMatrix::PM::safe_int($form->{company});
88 my $sq = MODS::PTMatrix::PM::sql_quote($form->{q} || '');
89 my $f_status = lc($form->{status} || '');
90 my $f_active = MODS::PTMatrix::PM::safe_int($form->{active} || 0);
91 my $f_sessions = lc($form->{sessions} || '');
92
93 my $where = '1=1';
94 my $filter_label = '';
95 $where .= " AND u.company_id='$cf'" if $cf;
96 $where .= " AND (u.email LIKE '%$sq%' OR u.display_name LIKE '%$sq%')" if $sq;
97
98 if($f_status eq 'active'){
99 $where .= " AND u.account_status='active'";
100 $filter_label = 'Active users';
101 }
102 if($f_active > 0){
103 $where .= " AND u.last_login_at >= DATE_SUB(NOW(), INTERVAL $f_active DAY)";
104 $filter_label = "Users active in last $f_active days";
105 }
106 if($f_sessions eq 'live'){
107 $where .= " AND EXISTS (SELECT 1 FROM `${database_name}`.user_sessions s WHERE s.user_id=u.id AND s.expires_at > NOW() AND s.revoked_at IS NULL)";
108 $filter_label = 'Users with live sessions';
109 }
110
111 my $dbh = $db->db_connect();
112
113 #------------------------------------------------------------------------------------------------------
114 #The matching user list (most-recent-login first, capped at 200)
115 my $sql = qq~SELECT u.id, u.email, u.display_name, u.role, u.avatar_color, u.account_status, u.is_super_admin, u.last_login_at, u.created_at, UNIX_TIMESTAMP(u.last_login_at) AS last_login_epoch, UNIX_TIMESTAMP(u.created_at) AS created_epoch, c.id AS company_id, 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 $where ORDER BY u.last_login_at DESC LIMIT 200~;
116 my @users = $db->db_readwrite_multiple($dbh, $sql, $ENV{SCRIPT_NAME}, __LINE__);
117 #------------------------------------------------------------------------------------------------------
118
119 $db->db_disconnect($dbh);
120
121 my $self_id = MODS::PTMatrix::PM::get_user_id($userinfo);
122 foreach my $usr(@users){
123 $usr->{initials} = MODS::PTMatrix::PM::initials($usr->{display_name});
124 $usr->{display_name} = MODS::PTMatrix::PM::h($usr->{display_name});
125 $usr->{is_self} = ($usr->{id} == $self_id) ? 1 : 0;
126 $usr->{company_name} = MODS::PTMatrix::PM::h($usr->{company_name} || '');
127 }
128
129 #Create all template variables
130 my $tvars;
131 $tvars->{users} = \@users;
132 $tvars->{has_users} = scalar(@users) ? 1 : 0;
133 $tvars->{q} = MODS::PTMatrix::PM::h($form->{q} || '');
134 $tvars->{company_id} = $cf;
135 $tvars->{filter_label} = MODS::PTMatrix::PM::h($filter_label);
136 $tvars->{has_filter} = $filter_label ? 1 : 0;
137
138 my $body = join('', $tfile->template('tf_admin_users.html', $tvars, $userinfo));
139
140 $load->render({
141 userinfo => $userinfo,
142 page_key => 'admin_users',
143 title => 'All Users',
144 body => $body,
145 });
146}
147
148sub user_detail{
149 my($uid) = @_;
150 my $dbh = $db->db_connect();
151
152 #------------------------------------------------------------------------------------------------------
153 #The target user
154 my $sql = qq~SELECT id, email, display_name, role, avatar_color, account_status, is_super_admin, is_admin, two_factor_enabled, email_verified_at, last_login_at, created_at, UNIX_TIMESTAMP(last_login_at) AS last_login_epoch, UNIX_TIMESTAMP(created_at) AS created_epoch, timezone, locale, job_title, phone, company_id FROM `${database_name}`.users WHERE id='$uid' LIMIT 1~;
155 my $u = $db->db_readwrite($dbh, $sql, $ENV{SCRIPT_NAME}, __LINE__);
156 #------------------------------------------------------------------------------------------------------
157
158 if(!$u || !$u->{id}){
159 $db->db_disconnect($dbh);
160 print qq~<script>window.location="$url->{admin_users}";</script>~;
161 return;
162 }
163
164 #------------------------------------------------------------------------------------------------------
165 #Workspace / company row (optional, user might not be in one)
166 my $company;
167 if($u->{company_id}){
168 my $sqlc = qq~SELECT id, name, brand_color, plan_tier, created_at, UNIX_TIMESTAMP(created_at) AS created_epoch FROM `${database_name}`.companies WHERE id='$u->{company_id}' LIMIT 1~;
169 $company = $db->db_readwrite($dbh, $sqlc, $ENV{SCRIPT_NAME}, __LINE__);
170 }
171 #------------------------------------------------------------------------------------------------------
172
173 #------------------------------------------------------------------------------------------------------
174 #Projects this user owns (most recently updated 10)
175 my $sql_projects = qq~SELECT id, name, status, priority, color, target_due_date, created_at, UNIX_TIMESTAMP(target_due_date) AS target_due_epoch FROM `${database_name}`.projects WHERE owner_user_id='$uid' AND deleted_at IS NULL ORDER BY updated_at DESC LIMIT 10~;
176 my @projects = $db->db_readwrite_multiple($dbh, $sql_projects, $ENV{SCRIPT_NAME}, __LINE__);
177 #------------------------------------------------------------------------------------------------------
178
179 my $p_total = &count_scalar($dbh, qq~SELECT COUNT(*) AS n FROM `${database_name}`.projects WHERE owner_user_id='$uid' AND deleted_at IS NULL~);
180 my $p_active = &count_scalar($dbh, qq~SELECT COUNT(*) AS n FROM `${database_name}`.projects WHERE owner_user_id='$uid' AND deleted_at IS NULL AND status='active'~);
181
182 #------------------------------------------------------------------------------------------------------
183 #Tasks this user is assigned to (most recent 10, excluding archived/cancelled)
184 my $sql_tasks = qq~SELECT id, title, status, priority, ticket_number, due_date, updated_at, project_id, UNIX_TIMESTAMP(due_date) AS due_epoch FROM `${database_name}`.tasks WHERE assigned_user_id='$uid' AND status NOT IN ('archived','cancelled') ORDER BY updated_at DESC LIMIT 10~;
185 my @tasks = $db->db_readwrite_multiple($dbh, $sql_tasks, $ENV{SCRIPT_NAME}, __LINE__);
186 #------------------------------------------------------------------------------------------------------
187
188 my $t_open = &count_scalar($dbh, qq~SELECT COUNT(*) AS n FROM `${database_name}`.tasks WHERE assigned_user_id='$uid' AND status NOT IN ('done','cancelled','archived','pending_archive')~);
189 my $t_done = &count_scalar($dbh, qq~SELECT COUNT(*) AS n FROM `${database_name}`.tasks WHERE assigned_user_id='$uid' AND status='done'~);
190
191 #------------------------------------------------------------------------------------------------------
192 #Time tracking - last 30 days, lifetime, billable
193 my $tt_30d = &count_scalar($dbh, qq~SELECT COALESCE(SUM(minutes),0) AS n FROM `${database_name}`.time_entries WHERE user_id='$uid' AND occurred_on >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)~);
194 my $tt_life = &count_scalar($dbh, qq~SELECT COALESCE(SUM(minutes),0) AS n FROM `${database_name}`.time_entries WHERE user_id='$uid'~);
195 my $tt_billable = &count_scalar($dbh, qq~SELECT COALESCE(SUM(minutes),0) AS n FROM `${database_name}`.time_entries WHERE user_id='$uid' AND is_billable=1~);
196 #------------------------------------------------------------------------------------------------------
197
198 $db->db_disconnect($dbh);
199
200
201 #Pretty-print the role for the header
202 my $role_disp = {
203 company_admin => 'Company admin',
204 manager => 'Manager',
205 member => 'Member',
206 observer => 'Observer',
207 }->{$u->{role} || ''} || ucfirst($u->{role} || 'Member');
208
209 foreach my $p(@projects){
210 $p->{name} = MODS::PTMatrix::PM::h($p->{name});
211 }
212 foreach my $t(@tasks){
213 $t->{title} = MODS::PTMatrix::PM::h($t->{title});
214 $t->{ticket} = 'T-' . ($t->{ticket_number} || '?');
215 }
216
217 #Are we looking at our own super-admin row? Some buttons hide in that case.
218 my $self_id = $userinfo->{user_id} || $userinfo->{id} || 0;
219 my $is_self = ($uid eq $self_id) ? 1 : 0;
220
221
222 #Create all template variables
223 my $tvars;
224 $tvars->{target_id} = $u->{id};
225 $tvars->{target_email} = MODS::PTMatrix::PM::h($u->{email});
226 $tvars->{target_display_name} = MODS::PTMatrix::PM::h($u->{display_name} || $u->{email});
227 $tvars->{target_initials} = MODS::PTMatrix::PM::initials($u->{display_name} || $u->{email});
228 $tvars->{target_avatar_color} = $u->{avatar_color} || '#5aa9ff';
229 $tvars->{target_role_raw} = $u->{role} || 'member';
230 $tvars->{target_role_display} = $role_disp;
231 $tvars->{target_account_status} = $u->{account_status} || 'active';
232 $tvars->{target_is_super_admin} = $u->{is_super_admin} ? 1 : 0;
233 $tvars->{target_not_super_admin} = $u->{is_super_admin} ? 0 : 1;
234 $tvars->{target_is_admin} = $u->{is_admin} ? 1 : 0;
235 $tvars->{target_2fa} = $u->{two_factor_enabled} ? 'On' : 'Off';
236 $tvars->{target_email_verified} = $u->{email_verified_at} ? 'Verified' : 'Unverified';
237 $tvars->{target_email_is_verified} = $u->{email_verified_at} ? 1 : 0;
238 $tvars->{target_timezone} = $u->{timezone} || 'UTC';
239 $tvars->{target_locale} = $u->{locale} || 'en-US';
240 $tvars->{target_job_title} = MODS::PTMatrix::PM::h($u->{job_title} || '');
241 $tvars->{target_phone} = MODS::PTMatrix::PM::h($u->{phone} || '');
242 $tvars->{target_created_at} = $u->{created_at} || '-';
243 $tvars->{target_last_login_at} = $u->{last_login_at} || '-';
244 $tvars->{target_created_epoch} = $u->{created_epoch} || '';
245 $tvars->{target_last_login_epoch} = $u->{last_login_epoch} || '';
246
247 $tvars->{is_self} = $is_self;
248 $tvars->{is_active} = (($u->{account_status} || '') eq 'active') ? 1 : 0;
249 $tvars->{is_suspended} = (($u->{account_status} || '') eq 'suspended') ? 1 : 0;
250 $tvars->{is_closed} = (($u->{account_status} || '') eq 'closed') ? 1 : 0;
251 $tvars->{is_pending_verification} = (($u->{account_status} || '') eq 'pending_verification') ? 1 : 0;
252
253 $tvars->{viewer_is_super_admin} = ($userinfo->{is_super_admin} || $userinfo->{_admin_is_super_admin}) ? 1 : 0;
254
255 #Workspace block
256 $tvars->{has_company} = $company ? 1 : 0;
257 $tvars->{company_id} = $company ? $company->{id} : 0;
258 $tvars->{company_name} = $company ? MODS::PTMatrix::PM::h($company->{name}) : '';
259 $tvars->{company_color} = $company ? ($company->{brand_color} || '#5aa9ff') : '#5aa9ff';
260 $tvars->{company_plan} = $company ? ucfirst($company->{plan_tier} || 'free') : '';
261 $tvars->{company_joined} = $company ? ($company->{created_at} || '-') : '-';
262 $tvars->{company_joined_epoch} = $company ? ($company->{created_epoch} || '') : '';
263
264 #Projects block
265 $tvars->{projects} = \@projects;
266 $tvars->{has_projects} = scalar(@projects) ? 1 : 0;
267 $tvars->{p_total} = $p_total;
268 $tvars->{p_active} = $p_active;
269
270 #Tasks block
271 $tvars->{tasks} = \@tasks;
272 $tvars->{has_tasks} = scalar(@tasks) ? 1 : 0;
273 $tvars->{t_open} = $t_open;
274 $tvars->{t_done} = $t_done;
275
276 #Time tracking block (minutes -> hours)
277 $tvars->{tt_30d_hours} = sprintf('%.1f', $tt_30d / 60);
278 $tvars->{tt_life_hours} = sprintf('%.1f', $tt_life / 60);
279 $tvars->{tt_billable_hours} = sprintf('%.1f', $tt_billable / 60);
280
281
282 my $body = join('', $tfile->template('tf_admin_user.html', $tvars, $userinfo));
283
284 $load->render({
285 userinfo => $userinfo,
286 page_key => 'admin_users',
287 title => 'Admin: ' . ($u->{display_name} || $u->{email}),
288 body => $body,
289 });
290}