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

O Operator
Diff

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

added on local at 2026-07-11 18:38:40

Added
+357
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to 21d97e268b9b
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# AffSoft - SUPER-ADMIN: Users list + per-user detail
4#
5# Consolidates former /admin_user.cgi (detail) into this file. Dispatch
6# happens on presence of the `?u=<id>` query param -- when it's a
7# nonzero int we render the per-user detail view; otherwise we render
8# the list. /admin_user.cgi is now a 3-line wrapper that `do`s this
9# file.
10#======================================================================
11use strict; use warnings;
12use lib '/var/www/vhosts/3dshawn.com/affiliate.3dshawn.com';
13use CGI;
14use MODS::Template;
15use MODS::DBConnect;
16use MODS::Login;
17use MODS::AffSoft::Config;
18use MODS::AffSoft::Wrapper;
19use MODS::AffSoft::Admin;
20
21my $q = CGI->new;
22my $form = $q->Vars;
23my $auth = MODS::Login->new;
24my $wrap = MODS::AffSoft::Wrapper->new;
25my $tfile = MODS::Template->new;
26my $db = MODS::DBConnect->new;
27my $cfg = MODS::AffSoft::Config->new;
28my $admin = MODS::AffSoft::Admin->new;
29my $DB = $cfg->settings('database_name');
30
31my $userinfo = $auth->login_verify();
32if (!$userinfo) { print "Status: 302 Found\nLocation: /login.cgi\n\n"; exit; }
33$admin->require_admin($userinfo);
34require MODS::AffSoft::Permissions;
35MODS::AffSoft::Permissions->new->require_feature($userinfo, 'admin_view_users');
36
37# ---- Dispatch: ?u=<nonzero int> -> detail; otherwise -> list --------
38my $u_param = $form->{u} || 0;
39$u_param =~ s/[^0-9]//g;
40
41if ($u_param && $u_param + 0 > 0) {
42 _usr_detail($u_param);
43} else {
44 _usr_list();
45}
46exit;
47
48#======================================================================
49# LIST view (default entry)
50#======================================================================
51sub _usr_list {
52 my $sq = $form->{q} || '';
53 $sq =~ s/[^A-Za-z0-9_\-\.\s\@]//g;
54
55 my $dbh = $db->db_connect();
56
57 my $where = '1=1';
58 $where .= " AND (u.email LIKE '%$sq%' OR u.display_name LIKE '%$sq%' OR p.name LIKE '%$sq%')" if $sq;
59
60 my @users = $db->db_readwrite_multiple($dbh, qq~
61 SELECT u.id, u.email, u.display_name, u.plan_tier, u.account_status,
62 u.is_admin, u.last_login_at, u.created_at,
63 UNIX_TIMESTAMP(u.last_login_at) AS last_login_at_epoch,
64 UNIX_TIMESTAMP(u.created_at) AS created_at_epoch,
65 MIN(p.id) AS storefront_id, MIN(p.name) AS storefront_name,
66 '' AS subdomain
67 FROM `${DB}`.users u
68 LEFT JOIN `${DB}`.affiliate_programs p ON p.merchant_user_id = u.id
69 WHERE $where
70 GROUP BY u.id
71 ORDER BY u.created_at DESC
72 LIMIT 200
73 ~, $ENV{SCRIPT_NAME}, __LINE__);
74
75 my @palette = ('#5aa9ff','#34d399','#fbbf24','#a78bfa','#f87171','#22c55e','#06b6d4','#ec4899','#f59e0b');
76 foreach my $u (@users) {
77 my $dn = $u->{display_name} || $u->{email} || ('User #' . $u->{id});
78 $u->{display_name} = _usr_h($dn);
79 $u->{email} = _usr_h($u->{email});
80 $u->{plan_tier} = ucfirst($u->{plan_tier} || 'free');
81 $u->{account_status} = _usr_h($u->{account_status} || 'active');
82 $u->{is_admin_flag} = $u->{is_admin} ? 1 : 0;
83 $u->{last_login_at} = $u->{last_login_at} || '-';
84 $u->{storefront_name} = _usr_h($u->{storefront_name} || '-');
85 $u->{avatar_color} = $palette[ ($u->{id} || 0) % scalar(@palette) ];
86 my @parts = split /\s+/, $dn, 2;
87 my $ini = '';
88 $ini .= uc(substr($parts[0], 0, 1)) if defined $parts[0];
89 $ini .= uc(substr($parts[1], 0, 1)) if defined $parts[1] && length $parts[1];
90 $ini = uc(substr($dn, 0, 2)) unless length $ini;
91 $u->{initials} = $ini;
92 $u->{is_self} = ($u->{id} == $userinfo->{user_id}) ? 1 : 0;
93 }
94 $db->db_disconnect($dbh);
95
96 print "Content-Type: text/html; charset=utf-8\nCache-Control: no-store, no-cache, must-revalidate, max-age=0\nPragma: no-cache\nExpires: 0\n\n";
97 my $tvars = {
98 users => \@users,
99 has_users => scalar(@users) ? 1 : 0,
100 q => _usr_h($sq),
101 };
102 my $body = join('', $tfile->template('affsoft_admin_users.html', $tvars, $userinfo));
103 $wrap->render({
104 userinfo => $userinfo,
105 page_key => 'admin_users',
106 title => 'All Users',
107 body => $body,
108 });
109}
110
111#======================================================================
112# DETAIL view (?u=<id>)
113# Drill into a single seller's account. Renders the user's profile,
114# storefront(s), models, orders, marketplace connections, credit
115# balance, and admin-action toolbar.
116#======================================================================
117sub _usr_detail {
118 my ($uid) = @_;
119
120 # Need Marketplaces + Billing modules only in this branch.
121 require MODS::AffSoft::Marketplaces;
122 require MODS::AffSoft::Billing;
123 my $mp = MODS::AffSoft::Marketplaces->new;
124 my $bill = MODS::AffSoft::Billing->new;
125
126 $| = 1;
127
128 my $dbh = $db->db_connect();
129
130 # ---- The target user -------------------------------------------------
131 my $u = $db->db_readwrite($dbh, qq~
132 SELECT id, email, display_name, plan_tier, account_status, is_admin,
133 trust_level, two_factor_enabled, email_verified_at,
134 last_login_at, default_currency, timezone, created_at, updated_at,
135 UNIX_TIMESTAMP(last_login_at) AS last_login_at_epoch,
136 UNIX_TIMESTAMP(created_at) AS created_at_epoch,
137 UNIX_TIMESTAMP(updated_at) AS updated_at_epoch
138 FROM `${DB}`.users
139 WHERE id='$uid'
140 LIMIT 1
141 ~, $ENV{SCRIPT_NAME}, __LINE__);
142
143 if (!$u || !$u->{id}) {
144 $db->db_disconnect($dbh);
145 print "Status: 302 Found\nLocation: /admin.cgi\n\n";
146 return;
147 }
148
149 # ---- Storefront(s) ---------------------------------------------------
150 my @storefronts = $db->db_readwrite_multiple($dbh, qq~
151 SELECT id, name, subdomain, created_at,
152 UNIX_TIMESTAMP(created_at) AS created_at_epoch
153 FROM `${DB}`.storefronts
154 WHERE user_id='$uid'
155 ORDER BY id ASC
156 ~, $ENV{SCRIPT_NAME}, __LINE__);
157
158 # ---- Model counts ----------------------------------------------------
159 my $m_total = _usr_scalar($dbh,
160 "SELECT COUNT(*) AS n FROM `${DB}`.models WHERE user_id='$uid' AND purged_at IS NULL", 'n');
161 my $m_purged = _usr_scalar($dbh,
162 "SELECT COUNT(*) AS n FROM `${DB}`.models WHERE user_id='$uid' AND purged_at IS NOT NULL", 'n');
163 my $m_published = _usr_scalar($dbh,
164 "SELECT COUNT(*) AS n FROM `${DB}`.models WHERE user_id='$uid' AND status='published' AND purged_at IS NULL", 'n');
165
166 # Last 10 models so the admin can see what they're working on.
167 my @models = $db->db_readwrite_multiple($dbh, qq~
168 SELECT id, title, status, visibility, currency, base_price_cents,
169 created_at, updated_at,
170 UNIX_TIMESTAMP(created_at) AS created_at_epoch,
171 UNIX_TIMESTAMP(updated_at) AS updated_at_epoch
172 FROM `${DB}`.models
173 WHERE user_id='$uid' AND purged_at IS NULL
174 ORDER BY updated_at DESC
175 LIMIT 10
176 ~, $ENV{SCRIPT_NAME}, __LINE__);
177
178 foreach my $m (@models) {
179 $m->{price} = '$' . sprintf('%.2f', ($m->{base_price_cents} || 0) / 100);
180 $m->{title} = $m->{title} || ('Model #' . $m->{id});
181 }
182
183 # ---- Orders / revenue -----------------------------------------------
184 # Sum across every storefront the user owns.
185 my $orders_30d_count = 0;
186 my $orders_30d_cents = 0;
187 my $orders_all_cents = 0;
188 my @recent_orders;
189
190 if (@storefronts) {
191 my $sids = join(',', map { "'$_->{id}'" } @storefronts);
192
193 my $rec30 = $db->db_readwrite($dbh, qq~
194 SELECT COUNT(*) AS n, COALESCE(SUM(total_amount_cents),0) AS cents
195 FROM `${DB}`.orders
196 WHERE storefront_id IN ($sids)
197 AND status='paid'
198 AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
199 ~, $ENV{SCRIPT_NAME}, __LINE__);
200 if ($rec30) {
201 $orders_30d_count = $rec30->{n} || 0;
202 $orders_30d_cents = $rec30->{cents} || 0;
203 }
204
205 my $allr = $db->db_readwrite($dbh, qq~
206 SELECT COALESCE(SUM(total_amount_cents),0) AS cents
207 FROM `${DB}`.orders
208 WHERE storefront_id IN ($sids)
209 AND status='paid'
210 ~, $ENV{SCRIPT_NAME}, __LINE__);
211 $orders_all_cents = ($allr && $allr->{cents}) ? $allr->{cents} : 0;
212
213 @recent_orders = $db->db_readwrite_multiple($dbh, qq~
214 SELECT id, storefront_id, buyer_email, total_amount_cents,
215 currency, status, created_at,
216 UNIX_TIMESTAMP(created_at) AS created_at_epoch
217 FROM `${DB}`.orders
218 WHERE storefront_id IN ($sids)
219 ORDER BY created_at DESC
220 LIMIT 10
221 ~, $ENV{SCRIPT_NAME}, __LINE__);
222
223 foreach my $o (@recent_orders) {
224 $o->{total} = '$' . sprintf('%.2f', ($o->{total_amount_cents} || 0) / 100);
225 }
226 }
227
228 # ---- Marketplace connections ----------------------------------------
229 my @mp_rows;
230 my $have_mp = _usr_scalar($dbh, qq~
231 SELECT COUNT(*) AS n FROM information_schema.tables
232 WHERE table_schema='$DB' AND table_name='marketplace_accounts'
233 ~, 'n');
234 if ($have_mp) {
235 @mp_rows = $db->db_readwrite_multiple($dbh, qq~
236 SELECT platform, status, account_handle, connected_at, last_used_at,
237 UNIX_TIMESTAMP(connected_at) AS connected_at_epoch,
238 UNIX_TIMESTAMP(last_used_at) AS last_used_at_epoch
239 FROM `${DB}`.marketplace_accounts
240 WHERE user_id='$uid'
241 ORDER BY platform ASC
242 ~, $ENV{SCRIPT_NAME}, __LINE__);
243 foreach my $r (@mp_rows) {
244 my $p = $mp->by_slug($r->{platform});
245 $r->{name} = $p ? $p->{name} : ucfirst($r->{platform});
246 $r->{connected_at} = $r->{connected_at} || '-';
247 $r->{last_used_at} = $r->{last_used_at} || '-';
248 }
249 }
250
251 # ---- Credit balance --------------------------------------------------
252 # Billing helpers self-guard against missing credit_ledger table.
253 my $credit_cents = $bill->credit_balance_cents($db, $dbh, $DB, $uid);
254
255 $db->db_disconnect($dbh);
256
257 # ---- Format / tvars -------------------------------------------------
258 my $is_self = ($uid eq $userinfo->{_admin_user_id} || $uid eq $userinfo->{user_id}) ? 1 : 0;
259
260 my $tvars = {
261 target_id => $u->{id},
262 target_email => _usr_esc($u->{email}),
263 target_display_name => _usr_esc($u->{display_name} || $u->{email}),
264 target_plan_tier => ucfirst($u->{plan_tier} || 'free'),
265 target_plan_raw => $u->{plan_tier} || 'free',
266 target_account_status => $u->{account_status} || 'active',
267 target_is_admin => $u->{is_admin} ? 1 : 0,
268 target_is_super_admin => $u->{is_super_admin} ? 1 : 0,
269 target_not_super_admin => $u->{is_super_admin} ? 0 : 1,
270 # login_verify() does NOT include is_super_admin -- read it through
271 # Permissions->is_super_admin() which probes lazily and caches.
272 viewer_is_super_admin => do {
273 require MODS::AffSoft::Permissions;
274 MODS::AffSoft::Permissions->new->is_super_admin($userinfo) ? 1 : 0;
275 },
276 target_2fa => $u->{two_factor_enabled} ? 'On' : 'Off',
277 target_trust_level => $u->{trust_level} || 0,
278 target_currency => $u->{default_currency} || 'USD',
279 target_timezone => $u->{timezone} || 'UTC',
280 target_created_at => $u->{created_at} || '-',
281 target_created_at_epoch=> $u->{created_at_epoch} || 0,
282 target_last_login_at => $u->{last_login_at} || '-',
283 target_last_login_at_epoch => $u->{last_login_at_epoch} || 0,
284 target_email_verified => $u->{email_verified_at} ? 'Verified' : 'Unverified',
285
286 is_self => $is_self,
287 is_active => (($u->{account_status} || '') eq 'active') ? 1 : 0,
288 is_suspended => (($u->{account_status} || '') eq 'suspended') ? 1 : 0,
289 is_closed => (($u->{account_status} || '') eq 'closed') ? 1 : 0,
290
291 # Sub-data
292 storefronts => \@storefronts,
293 storefront_count => scalar(@storefronts),
294 has_storefront => scalar(@storefronts) ? 1 : 0,
295
296 models => \@models,
297 model_count => scalar(@models),
298 m_total => $m_total,
299 m_published => $m_published,
300 m_purged => $m_purged,
301 has_models => scalar(@models) ? 1 : 0,
302
303 recent_orders => \@recent_orders,
304 has_orders => scalar(@recent_orders) ? 1 : 0,
305 orders_30d_count => $orders_30d_count,
306 orders_30d_revenue => '$' . sprintf('%.2f', $orders_30d_cents / 100),
307 orders_all_revenue => '$' . sprintf('%.2f', $orders_all_cents / 100),
308
309 marketplaces => \@mp_rows,
310 has_marketplaces => scalar(@mp_rows) ? 1 : 0,
311
312 # Credits (account-level credit ledger balance)
313 target_credit_cents => $credit_cents,
314 target_credit_dollars => ($credit_cents < 0 ? '-' : '') . '$' . sprintf('%.2f', abs($credit_cents) / 100),
315 target_has_credit => $credit_cents > 0 ? 1 : 0,
316 target_has_debit => $credit_cents < 0 ? 1 : 0,
317 target_no_credit => $credit_cents == 0 ? 1 : 0,
318 };
319
320 print "Content-Type: text/html; charset=utf-8\nCache-Control: no-store, no-cache, must-revalidate, max-age=0\nPragma: no-cache\nExpires: 0\n\n";
321
322 my $body = join('', $tfile->template('webstls_admin_user.html', $tvars, $userinfo));
323
324 $wrap->render({
325 userinfo => $userinfo,
326 page_key => 'admin',
327 title => 'Admin: ' . ($u->{display_name} || $u->{email}),
328 body => $body,
329 extra_js => ['/assets/javascript/graphs.js'],
330 });
331}
332
333sub _usr_scalar {
334 my ($dbh, $sql, $key) = @_;
335 my $r = $db->db_readwrite($dbh, $sql, $ENV{SCRIPT_NAME}, __LINE__);
336 return ($r && defined $r->{$key}) ? $r->{$key} : 0;
337}
338
339sub _usr_esc {
340 my $s = shift;
341 return '' unless defined $s;
342 $s =~ s/&/&amp;/g;
343 $s =~ s/</&lt;/g;
344 $s =~ s/>/&gt;/g;
345 $s =~ s/"/&quot;/g;
346 return $s;
347}
348
349sub _usr_h {
350 my $s = shift;
351 return '' unless defined $s;
352 $s =~ s/&/&amp;/g;
353 $s =~ s/</&lt;/g;
354 $s =~ s/>/&gt;/g;
355 $s =~ s/"/&quot;/g;
356 return $s;
357}