Diff -- /var/www/vhosts/3dshawn.com/admin.3dshawn.com/MODS/MetaAdmin/Aggregate.pm

O Operator
Diff

/var/www/vhosts/3dshawn.com/admin.3dshawn.com/MODS/MetaAdmin/Aggregate.pm

added on local at 2026-07-10 10:51:45

Added
+261
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to 70127f96072f
Restore this content →
Unified diff
Naive LCS-based line diff. Additions in emerald, deletions in rose. Both sides decompressed live from BLOB_STORE.
1package MODS::MetaAdmin::Aggregate;
2#======================================================================
3# Meta-Admin -- per-site query templates for revenue + support
4# roll-up. Each site's billing/support schema is slightly different
5# because the codebases were forked, so we keep one set of SQL per
6# slug. Anything we don't have a recipe for returns undef and the
7# UI shows '--' instead of a fake zero.
8#
9# All queries are SELECT only -- SiteConn enforces that, and these
10# templates are wrapped in eval() so a single broken site never
11# breaks the whole roll-up.
12#======================================================================
13use strict;
14use warnings;
15
16use MODS::MetaAdmin::SiteConn;
17
18sub new {
19 my $class = shift;
20 return bless({ sc => MODS::MetaAdmin::SiteConn->new }, $class);
21}
22
23# Per-slug template registry. Each entry returns a hashref of named
24# SQL snippets we know how to interpret.
25#
26# Recognized keys:
27# active_users_sql -> single value 'n'
28# mtd_orders_sql -> single value { n => count, total_cents => sum }
29# open_threads_sql -> single value 'n'
30#
31my %SITES = (
32 contactforge => {
33 active_users_sql => q~
34 SELECT COUNT(*) AS n FROM users WHERE COALESCE(account_status,'active') NOT IN ('closed','suspended')
35 ~,
36 mtd_orders_sql => q~
37 SELECT COUNT(*) AS n, COALESCE(SUM(total_cents),0) AS total_cents
38 FROM invoices
39 WHERE status IN ('paid','open') AND issued_at >= DATE_FORMAT(NOW(),'%Y-%m-01')
40 ~,
41 open_threads_sql => q~
42 SELECT COUNT(*) AS n FROM email_sends_log
43 WHERE status='queued' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
44 ~,
45 },
46 repricer => {
47 active_users_sql => q~
48 SELECT COUNT(*) AS n FROM users WHERE COALESCE(account_status,'active') NOT IN ('closed','suspended')
49 ~,
50 mtd_orders_sql => q~
51 SELECT COUNT(*) AS n, COALESCE(SUM(amount_due_cents),0) AS total_cents
52 FROM invoices
53 WHERE status IN ('paid','open') AND created_at >= DATE_FORMAT(NOW(),'%Y-%m-01')
54 ~,
55 open_threads_sql => q~
56 SELECT COUNT(*) AS n FROM email_sends_log
57 WHERE status='queued' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
58 ~,
59 },
60 shopcart => {
61 active_users_sql => q~SELECT COUNT(*) AS n FROM users~,
62 mtd_orders_sql => q~
63 SELECT COUNT(*) AS n, COALESCE(SUM(total_cents),0) AS total_cents
64 FROM orders
65 WHERE status='paid' AND created_at >= DATE_FORMAT(NOW(),'%Y-%m-01')
66 ~,
67 open_threads_sql => q~SELECT COUNT(*) AS n FROM email_sends_log WHERE status='queued' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)~,
68 },
69 abforge => {
70 active_users_sql => q~SELECT COUNT(*) AS n FROM users~,
71 mtd_orders_sql => q~
72 SELECT COUNT(*) AS n, COALESCE(SUM(amount_due_cents),0) AS total_cents
73 FROM invoices
74 WHERE status IN ('paid','open') AND created_at >= DATE_FORMAT(NOW(),'%Y-%m-01')
75 ~,
76 open_threads_sql => q~SELECT COUNT(*) AS n FROM email_sends_log WHERE status='queued'~,
77 },
78 affsoft => {
79 active_users_sql => q~SELECT COUNT(*) AS n FROM users~,
80 mtd_orders_sql => undef,
81 open_threads_sql => q~SELECT COUNT(*) AS n FROM email_sends_log WHERE status='queued'~,
82 },
83 ptmatrix => {
84 active_users_sql => q~SELECT COUNT(*) AS n FROM users~,
85 mtd_orders_sql => q~
86 SELECT COUNT(*) AS n, COALESCE(SUM(amount_due_cents),0) AS total_cents
87 FROM invoices
88 WHERE status IN ('paid','open') AND created_at >= DATE_FORMAT(NOW(),'%Y-%m-01')
89 ~,
90 open_threads_sql => q~SELECT COUNT(*) AS n FROM email_sends_log WHERE status='queued'~,
91 },
92 webstls => {
93 active_users_sql => q~SELECT COUNT(*) AS n FROM users~,
94 mtd_orders_sql => q~
95 SELECT COUNT(*) AS n, COALESCE(SUM(total_cents),0) AS total_cents
96 FROM orders
97 WHERE status='paid' AND created_at >= DATE_FORMAT(NOW(),'%Y-%m-01')
98 ~,
99 open_threads_sql => q~SELECT COUNT(*) AS n FROM email_sends_log WHERE status='queued'~,
100 },
101);
102
103#---------------------------------------------------------------------
104# Roll up revenue across every active site. Returns:
105# { total_users => N, total_revenue_cents => N,
106# per_site => [ { slug, name, color, users, orders, revenue_cents, err } ] }
107#---------------------------------------------------------------------
108sub revenue_rollup {
109 my $self = shift;
110 my $sites = $self->{sc}->list_sites(active => 1);
111 my %out = (total_users => 0, total_orders => 0, total_revenue_cents => 0, per_site => []);
112 foreach my $row (@$sites) {
113 my $slug = $row->{slug};
114 my $tmpl = $SITES{$slug};
115 my $entry = {
116 slug => $slug,
117 display_name => $row->{display_name},
118 color => $row->{brand_color} || '#807aa8',
119 users => undef,
120 orders => undef,
121 revenue_cents=> undef,
122 err => '',
123 };
124 unless ($tmpl) {
125 $entry->{err} = 'no template';
126 push @{ $out{per_site} }, $entry;
127 next;
128 }
129 if ($tmpl->{active_users_sql}) {
130 my $r = eval { $self->{sc}->query_one($slug, $tmpl->{active_users_sql}) };
131 if ($r) { $entry->{users} = $r->{n} + 0; $out{total_users} += $entry->{users}; }
132 else { $entry->{err} = $self->{sc}->last_error; }
133 }
134 if ($tmpl->{mtd_orders_sql}) {
135 my $r = eval { $self->{sc}->query_one($slug, $tmpl->{mtd_orders_sql}) };
136 if ($r) {
137 $entry->{orders} = ($r->{n} || 0) + 0;
138 $entry->{revenue_cents} = ($r->{total_cents} || 0) + 0;
139 $out{total_orders} += $entry->{orders};
140 $out{total_revenue_cents} += $entry->{revenue_cents};
141 } else {
142 $entry->{err} ||= $self->{sc}->last_error;
143 }
144 }
145 push @{ $out{per_site} }, $entry;
146 }
147 return \%out;
148}
149
150#---------------------------------------------------------------------
151# Roll up queued outbound emails per site (the "mail queue backlog"
152# monitor rendered on /support.cgi).
153#
154# $range: either an integer day-count (rolling) or a hashref
155# { from => 'YYYY-MM-DD', to => 'YYYY-MM-DD' } for a fixed window.
156# Undef defaults to 30-day rolling.
157#---------------------------------------------------------------------
158sub support_rollup {
159 my ($self, $range) = @_;
160 $range //= 30;
161 my ($where, $range_label, $range_days) = _build_range_where($range);
162
163 my $sites = $self->{sc}->list_sites(active => 1);
164 my %out = (
165 total_open => 0,
166 per_site => [],
167 range_label => $range_label,
168 range_days => $range_days,
169 );
170 foreach my $row (@$sites) {
171 my $slug = $row->{slug};
172 my $entry = {
173 slug => $slug,
174 display_name => $row->{display_name},
175 color => $row->{brand_color} || '#807aa8',
176 open => undef,
177 err => '',
178 };
179 my $sql = qq~SELECT COUNT(*) AS n FROM email_sends_log
180 WHERE status='queued' AND $where~;
181 my $r = eval { $self->{sc}->query_one($slug, $sql) };
182 if ($r) {
183 $entry->{open} = ($r->{n} || 0) + 0;
184 $out{total_open} += $entry->{open};
185 } else {
186 $entry->{err} = $self->{sc}->last_error;
187 }
188 push @{ $out{per_site} }, $entry;
189 }
190 return \%out;
191}
192
193# Build a WHERE clause on `created_at` from either a day count or a
194# custom {from,to} hash. Returns ($sql_where, $display_label, $days_or_zero).
195sub _build_range_where {
196 my ($range) = @_;
197 if (ref($range) eq 'HASH' && $range->{from} && $range->{to}
198 && $range->{from} =~ /^\d{4}-\d{2}-\d{2}$/
199 && $range->{to} =~ /^\d{4}-\d{2}-\d{2}$/) {
200 my $where = "created_at BETWEEN '$range->{from} 00:00:00' AND '$range->{to} 23:59:59'";
201 return ($where, "$range->{from} to $range->{to}", 0);
202 }
203 my $days = (ref($range) eq 'HASH' && $range->{days}) ? $range->{days} : $range;
204 $days = 30 unless defined($days) && $days =~ /^\d+$/ && $days > 0;
205 $days = 730 if $days > 730;
206 my $label = $days == 1 ? 'Last 24h'
207 : $days == 7 ? 'Last 7 days'
208 : $days == 30 ? 'Last 30 days'
209 : $days == 90 ? 'Last 90 days'
210 : $days == 365 ? 'Last 1 year'
211 : "Last $days days";
212 return ("created_at >= DATE_SUB(NOW(), INTERVAL $days DAY)", $label, $days);
213}
214
215#---------------------------------------------------------------------
216# db_sizes -- per-site MySQL DB size (data + indexes) from each site's
217# information_schema.TABLES. Returns arrayref of:
218# { slug, display_name, brand_color, db_name,
219# data_b, indexes_b, total_b, tables_ct, err }
220# sorted total_b desc. On per-site failure, err is set and totals are 0.
221#---------------------------------------------------------------------
222sub db_sizes {
223 my $self = shift;
224 my $sites = $self->{sc}->list_sites(active => 1);
225 my @out;
226 foreach my $srow (@$sites) {
227 my $slug = $srow->{slug} or next;
228 my $entry = {
229 slug => $slug,
230 display_name => $srow->{display_name},
231 brand_color => $srow->{brand_color} || '#807aa8',
232 db_name => $srow->{db_name} || '',
233 data_b => 0,
234 indexes_b => 0,
235 total_b => 0,
236 tables_ct => 0,
237 err => '',
238 };
239 my $r = eval { $self->{sc}->query_one($slug, q~
240 SELECT COUNT(*) AS tables_ct,
241 COALESCE(SUM(DATA_LENGTH), 0) AS data_b,
242 COALESCE(SUM(INDEX_LENGTH), 0) AS indexes_b,
243 COALESCE(SUM(DATA_LENGTH + INDEX_LENGTH), 0) AS total_b
244 FROM information_schema.TABLES
245 WHERE TABLE_SCHEMA = DATABASE()
246 ~) };
247 if ($r) {
248 $entry->{tables_ct} = ($r->{tables_ct} || 0) + 0;
249 $entry->{data_b} = ($r->{data_b} || 0) + 0;
250 $entry->{indexes_b} = ($r->{indexes_b} || 0) + 0;
251 $entry->{total_b} = ($r->{total_b} || 0) + 0;
252 } else {
253 $entry->{err} = $self->{sc}->last_error;
254 }
255 push @out, $entry;
256 }
257 @out = sort { $b->{total_b} <=> $a->{total_b} } @out;
258 return \@out;
259}
260
2611;