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
+0
lines
Removed
-0
lines
Context
261
unchanged
Blobs
from 70127f96072f
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.
11package MODS::MetaAdmin::Aggregate;
22#======================================================================
33# Meta-Admin -- per-site query templates for revenue + support
44# roll-up. Each site's billing/support schema is slightly different
55# because the codebases were forked, so we keep one set of SQL per
66# slug. Anything we don't have a recipe for returns undef and the
77# UI shows '--' instead of a fake zero.
88#
99# All queries are SELECT only -- SiteConn enforces that, and these
1010# templates are wrapped in eval() so a single broken site never
1111# breaks the whole roll-up.
1212#======================================================================
1313use strict;
1414use warnings;
1515
1616use MODS::MetaAdmin::SiteConn;
1717
1818sub new {
1919 my $class = shift;
2020 return bless({ sc => MODS::MetaAdmin::SiteConn->new }, $class);
2121}
2222
2323# Per-slug template registry. Each entry returns a hashref of named
2424# SQL snippets we know how to interpret.
2525#
2626# Recognized keys:
2727# active_users_sql -> single value 'n'
2828# mtd_orders_sql -> single value { n => count, total_cents => sum }
2929# open_threads_sql -> single value 'n'
3030#
3131my %SITES = (
3232 contactforge => {
3333 active_users_sql => q~
3434 SELECT COUNT(*) AS n FROM users WHERE COALESCE(account_status,'active') NOT IN ('closed','suspended')
3535 ~,
3636 mtd_orders_sql => q~
3737 SELECT COUNT(*) AS n, COALESCE(SUM(total_cents),0) AS total_cents
3838 FROM invoices
3939 WHERE status IN ('paid','open') AND issued_at >= DATE_FORMAT(NOW(),'%Y-%m-01')
4040 ~,
4141 open_threads_sql => q~
4242 SELECT COUNT(*) AS n FROM email_sends_log
4343 WHERE status='queued' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
4444 ~,
4545 },
4646 repricer => {
4747 active_users_sql => q~
4848 SELECT COUNT(*) AS n FROM users WHERE COALESCE(account_status,'active') NOT IN ('closed','suspended')
4949 ~,
5050 mtd_orders_sql => q~
5151 SELECT COUNT(*) AS n, COALESCE(SUM(amount_due_cents),0) AS total_cents
5252 FROM invoices
5353 WHERE status IN ('paid','open') AND created_at >= DATE_FORMAT(NOW(),'%Y-%m-01')
5454 ~,
5555 open_threads_sql => q~
5656 SELECT COUNT(*) AS n FROM email_sends_log
5757 WHERE status='queued' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
5858 ~,
5959 },
6060 shopcart => {
6161 active_users_sql => q~SELECT COUNT(*) AS n FROM users~,
6262 mtd_orders_sql => q~
6363 SELECT COUNT(*) AS n, COALESCE(SUM(total_cents),0) AS total_cents
6464 FROM orders
6565 WHERE status='paid' AND created_at >= DATE_FORMAT(NOW(),'%Y-%m-01')
6666 ~,
6767 open_threads_sql => q~SELECT COUNT(*) AS n FROM email_sends_log WHERE status='queued' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)~,
6868 },
6969 abforge => {
7070 active_users_sql => q~SELECT COUNT(*) AS n FROM users~,
7171 mtd_orders_sql => q~
7272 SELECT COUNT(*) AS n, COALESCE(SUM(amount_due_cents),0) AS total_cents
7373 FROM invoices
7474 WHERE status IN ('paid','open') AND created_at >= DATE_FORMAT(NOW(),'%Y-%m-01')
7575 ~,
7676 open_threads_sql => q~SELECT COUNT(*) AS n FROM email_sends_log WHERE status='queued'~,
7777 },
7878 affsoft => {
7979 active_users_sql => q~SELECT COUNT(*) AS n FROM users~,
8080 mtd_orders_sql => undef,
8181 open_threads_sql => q~SELECT COUNT(*) AS n FROM email_sends_log WHERE status='queued'~,
8282 },
8383 ptmatrix => {
8484 active_users_sql => q~SELECT COUNT(*) AS n FROM users~,
8585 mtd_orders_sql => q~
8686 SELECT COUNT(*) AS n, COALESCE(SUM(amount_due_cents),0) AS total_cents
8787 FROM invoices
8888 WHERE status IN ('paid','open') AND created_at >= DATE_FORMAT(NOW(),'%Y-%m-01')
8989 ~,
9090 open_threads_sql => q~SELECT COUNT(*) AS n FROM email_sends_log WHERE status='queued'~,
9191 },
9292 webstls => {
9393 active_users_sql => q~SELECT COUNT(*) AS n FROM users~,
9494 mtd_orders_sql => q~
9595 SELECT COUNT(*) AS n, COALESCE(SUM(total_cents),0) AS total_cents
9696 FROM orders
9797 WHERE status='paid' AND created_at >= DATE_FORMAT(NOW(),'%Y-%m-01')
9898 ~,
9999 open_threads_sql => q~SELECT COUNT(*) AS n FROM email_sends_log WHERE status='queued'~,
100100 },
101101);
102102
103103#---------------------------------------------------------------------
104104# Roll up revenue across every active site. Returns:
105105# { total_users => N, total_revenue_cents => N,
106106# per_site => [ { slug, name, color, users, orders, revenue_cents, err } ] }
107107#---------------------------------------------------------------------
108108sub revenue_rollup {
109109 my $self = shift;
110110 my $sites = $self->{sc}->list_sites(active => 1);
111111 my %out = (total_users => 0, total_orders => 0, total_revenue_cents => 0, per_site => []);
112112 foreach my $row (@$sites) {
113113 my $slug = $row->{slug};
114114 my $tmpl = $SITES{$slug};
115115 my $entry = {
116116 slug => $slug,
117117 display_name => $row->{display_name},
118118 color => $row->{brand_color} || '#807aa8',
119119 users => undef,
120120 orders => undef,
121121 revenue_cents=> undef,
122122 err => '',
123123 };
124124 unless ($tmpl) {
125125 $entry->{err} = 'no template';
126126 push @{ $out{per_site} }, $entry;
127127 next;
128128 }
129129 if ($tmpl->{active_users_sql}) {
130130 my $r = eval { $self->{sc}->query_one($slug, $tmpl->{active_users_sql}) };
131131 if ($r) { $entry->{users} = $r->{n} + 0; $out{total_users} += $entry->{users}; }
132132 else { $entry->{err} = $self->{sc}->last_error; }
133133 }
134134 if ($tmpl->{mtd_orders_sql}) {
135135 my $r = eval { $self->{sc}->query_one($slug, $tmpl->{mtd_orders_sql}) };
136136 if ($r) {
137137 $entry->{orders} = ($r->{n} || 0) + 0;
138138 $entry->{revenue_cents} = ($r->{total_cents} || 0) + 0;
139139 $out{total_orders} += $entry->{orders};
140140 $out{total_revenue_cents} += $entry->{revenue_cents};
141141 } else {
142142 $entry->{err} ||= $self->{sc}->last_error;
143143 }
144144 }
145145 push @{ $out{per_site} }, $entry;
146146 }
147147 return \%out;
148148}
149149
150150#---------------------------------------------------------------------
151151# Roll up queued outbound emails per site (the "mail queue backlog"
152152# monitor rendered on /support.cgi).
153153#
154154# $range: either an integer day-count (rolling) or a hashref
155155# { from => 'YYYY-MM-DD', to => 'YYYY-MM-DD' } for a fixed window.
156156# Undef defaults to 30-day rolling.
157157#---------------------------------------------------------------------
158158sub support_rollup {
159159 my ($self, $range) = @_;
160160 $range //= 30;
161161 my ($where, $range_label, $range_days) = _build_range_where($range);
162162
163163 my $sites = $self->{sc}->list_sites(active => 1);
164164 my %out = (
165165 total_open => 0,
166166 per_site => [],
167167 range_label => $range_label,
168168 range_days => $range_days,
169169 );
170170 foreach my $row (@$sites) {
171171 my $slug = $row->{slug};
172172 my $entry = {
173173 slug => $slug,
174174 display_name => $row->{display_name},
175175 color => $row->{brand_color} || '#807aa8',
176176 open => undef,
177177 err => '',
178178 };
179179 my $sql = qq~SELECT COUNT(*) AS n FROM email_sends_log
180180 WHERE status='queued' AND $where~;
181181 my $r = eval { $self->{sc}->query_one($slug, $sql) };
182182 if ($r) {
183183 $entry->{open} = ($r->{n} || 0) + 0;
184184 $out{total_open} += $entry->{open};
185185 } else {
186186 $entry->{err} = $self->{sc}->last_error;
187187 }
188188 push @{ $out{per_site} }, $entry;
189189 }
190190 return \%out;
191191}
192192
193193# Build a WHERE clause on `created_at` from either a day count or a
194194# custom {from,to} hash. Returns ($sql_where, $display_label, $days_or_zero).
195195sub _build_range_where {
196196 my ($range) = @_;
197197 if (ref($range) eq 'HASH' && $range->{from} && $range->{to}
198198 && $range->{from} =~ /^\d{4}-\d{2}-\d{2}$/
199199 && $range->{to} =~ /^\d{4}-\d{2}-\d{2}$/) {
200200 my $where = "created_at BETWEEN '$range->{from} 00:00:00' AND '$range->{to} 23:59:59'";
201201 return ($where, "$range->{from} to $range->{to}", 0);
202202 }
203203 my $days = (ref($range) eq 'HASH' && $range->{days}) ? $range->{days} : $range;
204204 $days = 30 unless defined($days) && $days =~ /^\d+$/ && $days > 0;
205205 $days = 730 if $days > 730;
206206 my $label = $days == 1 ? 'Last 24h'
207207 : $days == 7 ? 'Last 7 days'
208208 : $days == 30 ? 'Last 30 days'
209209 : $days == 90 ? 'Last 90 days'
210210 : $days == 365 ? 'Last 1 year'
211211 : "Last $days days";
212212 return ("created_at >= DATE_SUB(NOW(), INTERVAL $days DAY)", $label, $days);
213213}
214214
215215#---------------------------------------------------------------------
216216# db_sizes -- per-site MySQL DB size (data + indexes) from each site's
217217# information_schema.TABLES. Returns arrayref of:
218218# { slug, display_name, brand_color, db_name,
219219# data_b, indexes_b, total_b, tables_ct, err }
220220# sorted total_b desc. On per-site failure, err is set and totals are 0.
221221#---------------------------------------------------------------------
222222sub db_sizes {
223223 my $self = shift;
224224 my $sites = $self->{sc}->list_sites(active => 1);
225225 my @out;
226226 foreach my $srow (@$sites) {
227227 my $slug = $srow->{slug} or next;
228228 my $entry = {
229229 slug => $slug,
230230 display_name => $srow->{display_name},
231231 brand_color => $srow->{brand_color} || '#807aa8',
232232 db_name => $srow->{db_name} || '',
233233 data_b => 0,
234234 indexes_b => 0,
235235 total_b => 0,
236236 tables_ct => 0,
237237 err => '',
238238 };
239239 my $r = eval { $self->{sc}->query_one($slug, q~
240240 SELECT COUNT(*) AS tables_ct,
241241 COALESCE(SUM(DATA_LENGTH), 0) AS data_b,
242242 COALESCE(SUM(INDEX_LENGTH), 0) AS indexes_b,
243243 COALESCE(SUM(DATA_LENGTH + INDEX_LENGTH), 0) AS total_b
244244 FROM information_schema.TABLES
245245 WHERE TABLE_SCHEMA = DATABASE()
246246 ~) };
247247 if ($r) {
248248 $entry->{tables_ct} = ($r->{tables_ct} || 0) + 0;
249249 $entry->{data_b} = ($r->{data_b} || 0) + 0;
250250 $entry->{indexes_b} = ($r->{indexes_b} || 0) + 0;
251251 $entry->{total_b} = ($r->{total_b} || 0) + 0;
252252 } else {
253253 $entry->{err} = $self->{sc}->last_error;
254254 }
255255 push @out, $entry;
256256 }
257257 @out = sort { $b->{total_b} <=> $a->{total_b} } @out;
258258 return \@out;
259259}
260260
2612611;