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

O Operator
Diff

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

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

Added
+542
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to 358a12a66078
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::DBConnect; my $db = new MODS::DBConnect;
28use MODS::Login; my $login = new MODS::Login;
29use MODS::PTMatrix::Config; my $config = new MODS::PTMatrix::Config; my $database_name = $config->settings('database_name');
30use MODS::PTMatrix::Wrapper; my $load = new MODS::PTMatrix::Wrapper;
31use MODS::AnonPaths;
32use Time::Local;
33
34
35
36#############################################################
37## Get form data
38#############################################################
39#Preserve raw description/vendor/notes/receipt_url before quotemeta chews them
40my %rawform = %$form;
41
42
43
44############################################################
45# Program Controls
46############################################################
47$|=1;
48my $userinfo = $login->login_verify();
49if(!$userinfo){
50 print qq~Status: 302 Found\nLocation: /login.cgi\n\n~;
51 exit;
52}
53
54#Super-admin check via users table (login_verify doesn't populate is_super_admin here)
55my $sa_dbh = $db->db_connect();
56my $sa_uid = $userinfo->{user_id};
57$sa_uid =~ s/[^0-9]//g if defined $sa_uid;
58my $sa_row = $sa_uid ? $db->db_readwrite($sa_dbh, "SELECT is_super_admin FROM `${database_name}`.users WHERE id='$sa_uid' LIMIT 1", $ENV{SCRIPT_NAME}, __LINE__) : undef;
59$db->db_disconnect($sa_dbh);
60if(!$sa_row || !$sa_row->{is_super_admin}){
61 print qq~Status: 403 Forbidden\nContent-Type: text/plain\n\nSuper-admin only.\n~;
62 exit;
63}
64
65&admin_earnings;
66
67
68############################################################
69# Subroutines
70############################################################
71sub safe_int_val{
72 my $v = shift // '';
73 $v =~ s/[^0-9\-]//g;
74 return $v ? $v + 0 : 0;
75}
76
77sub safe_cents{
78 my $v = shift // '';
79 $v =~ s/[^0-9.\-]//g;
80 return int($v * 100 + ($v < 0 ? -0.5 : 0.5));
81}
82
83sub safe_date_val{
84 my $v = shift // '';
85 return ($v =~ /^(\d{4}-\d{2}-\d{2})$/) ? $1 : '';
86}
87
88sub sql_q{
89 my $s = shift // '';
90 $s =~ s/\\/\\\\/g;
91 $s =~ s/'/\\'/g;
92 return $s;
93}
94
95sub h_esc{
96 my $s = shift // '';
97 $s =~ s/&/&amp;/g; $s =~ s/</&lt;/g; $s =~ s/>/&gt;/g; $s =~ s/"/&quot;/g;
98 return $s;
99}
100
101sub fmt_money{
102 my($cents) = @_;
103 $cents = int($cents || 0);
104 my $neg = $cents < 0 ? '-' : '';
105 $cents = abs($cents);
106 my $whole = int($cents / 100);
107 my $frac = sprintf('%02d', $cents % 100);
108 1 while $whole =~ s/(\d)(\d{3})(?!\d)/$1,$2/;
109 return "$neg\$$whole.$frac";
110}
111
112sub fmt_n{
113 my $n = int(shift // 0);
114 1 while $n =~ s/(\d)(\d{3})(?!\d)/$1,$2/;
115 return $n;
116}
117
118sub strftime_today{
119 my @t = localtime();
120 return sprintf('%04d-%02d-%02d', $t[5]+1900, $t[4]+1, $t[3]);
121}
122
123#Some MODS::DBConnect versions warn to STDOUT if the CGI header hasn't been
124#printed. We're deep in the render pipeline before headers - silence STDOUT
125#for the duration of each query so the header isn't dirtied.
126sub silenced_query{
127 my($dbh, $sql) = @_;
128 local *OLDOUT;
129 open(OLDOUT, '>&', \*STDOUT) or do {};
130 open(STDOUT, '>', '/dev/null') or do {};
131 my @rows = eval { $db->db_readwrite_multiple($dbh, $sql, $ENV{SCRIPT_NAME} || 'admin_earnings', __LINE__) };
132 @rows = () unless @rows;
133 open(STDOUT, '>&', \*OLDOUT) or do {};
134 return @rows;
135}
136
137sub silenced_exec{
138 my($dbh, $sql) = @_;
139 local *OLDOUT;
140 open(OLDOUT, '>&', \*STDOUT) or do {};
141 open(STDOUT, '>', '/dev/null') or do {};
142 eval { $db->db_readwrite($dbh, $sql, $ENV{SCRIPT_NAME} || 'admin_earnings', __LINE__) };
143 open(STDOUT, '>&', \*OLDOUT) or do {};
144}
145
146sub handle_mutations{
147 my($dbh, $uid) = @_;
148 my $act = $rawform{act} // '';
149 return '' unless $query->request_method eq 'POST';
150
151 if($act eq 'add_expense'){
152 my $cat = &sql_q(substr($rawform{category} // 'other', 0, 80));
153 my $ven = &sql_q(substr($rawform{vendor} // '', 0, 160));
154 my $desc = &sql_q(substr($rawform{description} // '', 0, 500));
155 my $amt = &safe_cents($rawform{amount});
156 my $when = &safe_date_val($rawform{incurred_at}) || &strftime_today;
157 my $url = &sql_q(substr($rawform{receipt_url} // '', 0, 500));
158 my $notes = &sql_q(substr($rawform{notes} // '', 0, 4000));
159 if($amt){
160 &silenced_exec($dbh, "INSERT INTO `${database_name}`.expenses (category,vendor,description,amount_cents,currency,incurred_at,receipt_url,notes,created_by_user_id) VALUES ('$cat','$ven','$desc',$amt,'USD','$when','$url','$notes',$uid)");
161 return 'Expense added.';
162 }
163 return 'Amount required.';
164 }
165 if($act eq 'edit_expense'){
166 my $eid = &safe_int_val($rawform{eid});
167 my $cat = &sql_q(substr($rawform{category} // 'other', 0, 80));
168 my $ven = &sql_q(substr($rawform{vendor} // '', 0, 160));
169 my $desc = &sql_q(substr($rawform{description} // '', 0, 500));
170 my $amt = &safe_cents($rawform{amount});
171 my $when = &safe_date_val($rawform{incurred_at});
172 my $url = &sql_q(substr($rawform{receipt_url} // '', 0, 500));
173 my $notes = &sql_q(substr($rawform{notes} // '', 0, 4000));
174 if($eid && $amt && $when){
175 &silenced_exec($dbh, "UPDATE `${database_name}`.expenses SET category='$cat',vendor='$ven',description='$desc',amount_cents=$amt,incurred_at='$when',receipt_url='$url',notes='$notes' WHERE id=$eid");
176 return 'Expense updated.';
177 }
178 return '';
179 }
180 if($act eq 'delete_expense'){
181 my $eid = &safe_int_val($rawform{eid});
182 if($eid){
183 &silenced_exec($dbh, "DELETE FROM `${database_name}`.expenses WHERE id=$eid");
184 return 'Expense deleted.';
185 }
186 }
187 return '';
188}
189
190sub resolve_range{
191 #Default to this month when nothing is set. AnonPaths gives back a
192 #(range_opts, preset_val, range_from, range_to) tuple - normalize it to
193 #concrete from_iso / to_iso for SQL, whether presets or days-based.
194 my %qparams = %$form;
195 if(!$qparams{preset} && !$qparams{from} && !$qparams{to} && !$qparams{days}){
196 $qparams{preset} = 'this_month';
197 }
198 my($range_opts, $preset_val, $range_from, $range_to) = MODS::AnonPaths::parse_range_params(\%qparams);
199 my $is_range_mode = ($range_opts->{from} && $range_opts->{to}) ? 1 : 0;
200 my $days = (ref($range_opts) eq 'HASH' && $range_opts->{days}) ? $range_opts->{days} : 30;
201
202 my($from_iso, $to_iso);
203 if($is_range_mode){
204 $from_iso = $range_opts->{from};
205 $to_iso = $range_opts->{to};
206 }else{
207 my $now = time();
208 my $ago = $now - $days * 86400;
209 my @t1 = localtime($ago);
210 my @t2 = localtime($now);
211 $from_iso = sprintf('%04d-%02d-%02d', $t1[5]+1900, $t1[4]+1, $t1[3]);
212 $to_iso = sprintf('%04d-%02d-%02d', $t2[5]+1900, $t2[4]+1, $t2[3]);
213 }
214 return ($preset_val, $range_from, $range_to, $from_iso, $to_iso);
215}
216
217sub build_inv_rows{
218 my($invoices) = @_;
219 my $inv_rows = '';
220 foreach my $r(@$invoices){
221 my $num = &h_esc($r->{invoice_number} // '');
222 my $cname = &h_esc($r->{company_name} // '(unknown)');
223 my $when = &h_esc($r->{paid_at} // '');
224 my $epoch = $r->{paid_epoch} // '';
225 my $amt = &fmt_money($r->{amount_paid_cents});
226 $inv_rows .= qq~<tr><td><span class="ts" data-ts="$epoch" data-fmt="date">$when</span></td><td>$num</td><td>$cname</td><td class="t-right">$amt</td></tr>~;
227 }
228 $inv_rows ||= qq~<tr><td colspan="4" class="erng-empty">No paid invoices in this window.</td></tr>~;
229 return $inv_rows;
230}
231
232sub build_cat_rows{
233 my($cat_totals, $expense_cents) = @_;
234 my $cat_rows = '';
235 foreach my $c(@$cat_totals){
236 my $cat = &h_esc($c->{category} // 'other');
237 my $cn = &fmt_n($c->{n});
238 my $cm = &fmt_money($c->{cents});
239 my $pct = $expense_cents ? sprintf('%.1f', 100 * $c->{cents} / $expense_cents) : '0.0';
240 $cat_rows .= qq~<div class="erng-cat-row"><span class="erng-cat-name">$cat</span><span class="erng-cat-bar"><span style="width:${pct}%"></span></span><span class="erng-cat-cnt">$cn</span><span class="erng-cat-amt">$cm</span></div>~;
241 }
242 $cat_rows ||= '<div class="erng-empty">No expenses logged in this window.</div>';
243 return $cat_rows;
244}
245
246sub build_exp_rows{
247 my($expenses, $preset_val, $range_from, $range_to) = @_;
248 my $exp_rows = '';
249 foreach my $e(@$expenses){
250 my $eid = $e->{id} + 0;
251 my $cat = &h_esc($e->{category} // 'other');
252 my $ven = &h_esc($e->{vendor} // '');
253 my $desc = &h_esc($e->{description} // '');
254 my $amt = &fmt_money($e->{amount_cents});
255 my $when = &h_esc($e->{incurred_at} // '');
256 my $epoch = $e->{incurred_epoch} // '';
257 my $url = &h_esc($e->{receipt_url} // '');
258 my $notes = &h_esc($e->{notes} // '');
259 my $rcpt = length($url) ? qq~<a href="$url" target="_blank" rel="noopener" class="erng-rcpt">receipt &#8599;</a>~ : '';
260 my $amt_input = sprintf('%.2f', ($e->{amount_cents} || 0)/100);
261 $exp_rows .= qq~
262 <tr class="erng-exp-row">
263 <td><span class="ts" data-ts="$epoch" data-fmt="date">$when</span></td>
264 <td><span class="erng-pill erng-pill-cat">$cat</span></td>
265 <td>$ven</td>
266 <td>$desc $rcpt</td>
267 <td class="t-right">$amt</td>
268 <td class="t-right">
269 <button type="button" class="erng-btn-ghost" onclick="ergEdit($eid)">Edit</button>
270 <form method="POST" action="/admin_earnings.cgi" style="display:inline" onsubmit="return confirm('Delete this expense?')">
271 <input type="hidden" name="act" value="delete_expense">
272 <input type="hidden" name="eid" value="$eid">
273 <input type="hidden" name="preset" value="$preset_val">
274 <input type="hidden" name="from" value="$range_from">
275 <input type="hidden" name="to" value="$range_to">
276 <button type="submit" class="erng-btn-danger">&times;</button>
277 </form>
278 </td>
279 </tr>
280 <tr class="erng-edit-row" id="erng-edit-$eid" style="display:none">
281 <td colspan="6">
282 <form method="POST" action="/admin_earnings.cgi" class="erng-edit-form">
283 <input type="hidden" name="act" value="edit_expense">
284 <input type="hidden" name="eid" value="$eid">
285 <input type="hidden" name="preset" value="$preset_val">
286 <input type="hidden" name="from" value="$range_from">
287 <input type="hidden" name="to" value="$range_to">
288 <label>Date <input type="date" name="incurred_at" value="$when" required></label>
289 <label>Category <input type="text" name="category" value="$cat" required></label>
290 <label>Vendor <input type="text" name="vendor" value="$ven"></label>
291 <label class="grow">Description <input type="text" name="description" value="$desc"></label>
292 <label>Amount <input type="number" name="amount" step="0.01" value="$amt_input" required></label>
293 <label class="grow">Receipt URL <input type="url" name="receipt_url" value="$url"></label>
294 <label class="grow">Notes <input type="text" name="notes" value="$notes"></label>
295 <div class="row-end">
296 <button type="button" class="erng-btn-ghost" onclick="ergCancel($eid)">Cancel</button>
297 <button type="submit" class="erng-btn">Save</button>
298 </div>
299 </form>
300 </td>
301 </tr>~;
302 }
303 $exp_rows ||= qq~<tr><td colspan="6" class="erng-empty">No expenses logged in this window. Use the form above to add one.</td></tr>~;
304 return $exp_rows;
305}
306
307sub earnings_css{
308 return qq~
309<style>
310.erng-hero { padding: 28px 28px 22px; background: linear-gradient(135deg, #1a0e08 0%, #0a0f17 100%); border-radius: 18px; margin-bottom: 22px; position: relative; overflow: hidden; }
311.erng-hero::after { content: ""; position: absolute; right: -60px; top: -60px; width: 320px; height: 320px; border-radius: 50%; background: radial-gradient(circle, rgba(74,222,128,0.22), transparent 70%); pointer-events: none; }
312.erng-hero h1 { margin: 0 0 6px; font-size: 28px; color: #fff; position: relative; }
313.erng-hero .sub { color: #8893a4; font-size: 13px; position: relative; max-width: 720px; line-height: 1.55; }
314.erng-toolbar { margin: 0 0 18px; padding: 12px 16px; background: rgba(255,255,255,0.03); border: 1px solid rgba(255,255,255,0.06); border-radius: 12px; display: flex; gap: 12px; flex-wrap: wrap; align-items: end; }
315.erng-toolbar .afnl-field { min-width: 140px; display: flex; flex-direction: column; gap: 4px; }
316.erng-toolbar .afnl-field-lbl { font-size: 10px; color: #6f7787; font-weight: 700; letter-spacing: 0.08em; text-transform: uppercase; }
317.erng-toolbar select, .erng-toolbar input { background: rgba(255,255,255,0.05); border: 1px solid rgba(255,255,255,0.1); color: #fff; padding: 8px 10px; border-radius: 7px; font-size: 13px; }
318.erng-kpi-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(220px, 1fr)); gap: 14px; margin-bottom: 22px; }
319.erng-kpi { position: relative; overflow: hidden; padding: 20px; background: linear-gradient(160deg, rgba(255,255,255,0.04), rgba(255,255,255,0.01)); border: 1px solid rgba(255,255,255,0.06); border-radius: 14px; }
320.erng-kpi::after { content: ""; position: absolute; width: 200px; height: 100px; border-radius: 50%; filter: blur(40px); right: -40px; top: -30px; opacity: 0.4; pointer-events: none; }
321.erng-kpi.k-rev::after { background: #22c55e; }
322.erng-kpi.k-exp::after { background: #ef4444; }
323.erng-kpi.k-net::after { background: #5aa9ff; }
324.erng-kpi.k-mar::after { background: #c4a3f0; }
325.erng-kpi .lbl { font-size: 11px; color: #8893a4; font-weight: 700; letter-spacing: 0.08em; text-transform: uppercase; position: relative; }
326.erng-kpi .val { font-size: 30px; font-weight: 800; color: #fff; margin-top: 6px; position: relative; }
327.erng-kpi .sub { font-size: 12px; color: #7a8392; margin-top: 4px; position: relative; }
328.erng-pos { color: #4ade80 !important; }
329.erng-neg { color: #f87171 !important; }
330.erng-panel { background: linear-gradient(160deg, rgba(255,255,255,0.03), rgba(255,255,255,0.01)); border: 1px solid rgba(255,255,255,0.06); border-radius: 14px; padding: 20px 22px; margin-bottom: 22px; }
331.erng-panel-head { display: flex; justify-content: space-between; align-items: baseline; margin-bottom: 14px; gap: 16px; flex-wrap: wrap; }
332.erng-panel-title { font-size: 16px; font-weight: 700; color: #fff; }
333.erng-panel-sub { font-size: 12px; color: #8893a4; }
334.erng-flash { padding: 12px 16px; background: rgba(34,197,94,0.12); border: 1px solid rgba(34,197,94,0.36); color: #4ade80; border-radius: 8px; margin-bottom: 18px; font-size: 13px; font-weight: 600; }
335table.erng-table { width: 100%; border-collapse: collapse; font-size: 13px; }
336table.erng-table th, table.erng-table td { padding: 9px 12px; text-align: left; border-bottom: 1px solid rgba(255,255,255,0.05); color: #cfd6e0; }
337table.erng-table th { font-size: 10px; color: #6f7787; font-weight: 700; letter-spacing: 0.08em; text-transform: uppercase; border-bottom: 1px solid rgba(255,255,255,0.08); }
338.t-right { text-align: right !important; }
339.erng-pill { display: inline-block; padding: 3px 9px; border-radius: 10px; font-size: 11px; font-weight: 700; background: rgba(196,163,240,0.18); color: #c4a3f0; text-transform: uppercase; letter-spacing: 0.05em; }
340.erng-rcpt { color: #5aa9ff; margin-left: 6px; font-size: 11px; }
341.erng-btn { padding: 7px 14px; background: #5aa9ff; color: #0a0f17; border: 0; border-radius: 7px; font-weight: 700; font-size: 13px; cursor: pointer; }
342.erng-btn:hover { background: #7cc1ff; }
343.erng-btn-ghost { padding: 5px 10px; background: rgba(255,255,255,0.06); color: #cfd6e0; border: 1px solid rgba(255,255,255,0.1); border-radius: 6px; font-size: 12px; cursor: pointer; }
344.erng-btn-ghost:hover { background: rgba(255,255,255,0.1); }
345.erng-btn-danger { padding: 5px 10px; background: rgba(239,68,68,0.18); color: #f87171; border: 1px solid rgba(239,68,68,0.4); border-radius: 6px; font-size: 14px; cursor: pointer; font-weight: 700; }
346.erng-btn-danger:hover { background: rgba(239,68,68,0.3); }
347.erng-add-form { display: grid; grid-template-columns: 130px 140px 1fr 1fr 130px 1fr auto; gap: 10px 12px; align-items: end; }
348.erng-add-form label { font-size: 10px; color: #6f7787; font-weight: 700; letter-spacing: 0.08em; text-transform: uppercase; display: flex; flex-direction: column; gap: 4px; }
349.erng-add-form input, .erng-add-form select { background: rgba(255,255,255,0.05); border: 1px solid rgba(255,255,255,0.1); color: #fff; padding: 8px 10px; border-radius: 7px; font-size: 13px; font-weight: 400; text-transform: none; letter-spacing: normal; }
350.erng-add-form .row-end { display: flex; gap: 8px; align-items: end; }
351.erng-edit-form { display: flex; flex-wrap: wrap; gap: 10px 12px; padding: 12px 14px; background: rgba(90,169,255,0.06); border-radius: 8px; }
352.erng-edit-form label { font-size: 10px; color: #6f7787; font-weight: 700; letter-spacing: 0.08em; text-transform: uppercase; display: flex; flex-direction: column; gap: 4px; }
353.erng-edit-form label.grow { flex: 1 1 220px; }
354.erng-edit-form input { background: rgba(255,255,255,0.05); border: 1px solid rgba(255,255,255,0.1); color: #fff; padding: 7px 9px; border-radius: 6px; font-size: 12px; font-weight: 400; text-transform: none; letter-spacing: normal; }
355.erng-edit-form .row-end { display: flex; gap: 8px; align-items: end; margin-left: auto; }
356.erng-cat-grid { display: flex; flex-direction: column; gap: 6px; }
357.erng-cat-row { display: grid; grid-template-columns: 140px 1fr 60px 100px; gap: 12px; align-items: center; padding: 6px 0; font-size: 13px; color: #cfd6e0; }
358.erng-cat-name { font-weight: 600; color: #fff; text-transform: capitalize; }
359.erng-cat-bar { background: rgba(255,255,255,0.05); border-radius: 4px; height: 8px; overflow: hidden; }
360.erng-cat-bar span { display: block; height: 100%; background: linear-gradient(90deg, #ef4444, #f87171); border-radius: 4px; }
361.erng-cat-cnt { font-size: 11px; color: #7a8392; text-align: right; }
362.erng-cat-amt { font-weight: 700; color: #f87171; text-align: right; }
363.erng-empty { text-align: center; padding: 24px; color: #7a8392; font-size: 13px; font-style: italic; }
364</style>
365~;
366}
367
368sub admin_earnings{
369 my $dbh = $db->db_connect();
370 my $uid = ($userinfo->{user_id} || 0) + 0;
371
372 my $flash = &handle_mutations($dbh, $uid);
373 my($preset_val, $range_from, $range_to, $from_iso, $to_iso) = &resolve_range;
374
375 my $from_sql = "'$from_iso 00:00:00'";
376 my $to_sql = "'$to_iso 23:59:59'";
377
378 my @rev_total = &silenced_query($dbh, "SELECT COALESCE(SUM(amount_paid_cents),0) AS cents, COUNT(*) AS n FROM `${database_name}`.invoices WHERE status='paid' AND paid_at BETWEEN $from_sql AND $to_sql");
379 my $revenue_cents = ($rev_total[0] && $rev_total[0]->{cents}) ? $rev_total[0]->{cents} + 0 : 0;
380 my $invoice_count = ($rev_total[0] && $rev_total[0]->{n}) ? $rev_total[0]->{n} + 0 : 0;
381
382 my @invoices = &silenced_query($dbh, "SELECT i.id, i.invoice_number, i.company_id, i.status, i.amount_paid_cents, i.paid_at, UNIX_TIMESTAMP(i.paid_at) AS paid_epoch, c.name AS company_name FROM `${database_name}`.invoices i LEFT JOIN `${database_name}`.companies c ON c.id=i.company_id WHERE i.status='paid' AND i.paid_at BETWEEN $from_sql AND $to_sql ORDER BY i.paid_at DESC LIMIT 500");
383
384 my @exp_total = &silenced_query($dbh, "SELECT COALESCE(SUM(amount_cents),0) AS cents, COUNT(*) AS n FROM `${database_name}`.expenses WHERE incurred_at BETWEEN '$from_iso' AND '$to_iso'");
385 my $expense_cents = ($exp_total[0] && $exp_total[0]->{cents}) ? $exp_total[0]->{cents} + 0 : 0;
386 my $expense_count = ($exp_total[0] && $exp_total[0]->{n}) ? $exp_total[0]->{n} + 0 : 0;
387
388 my @expenses = &silenced_query($dbh, "SELECT id, category, vendor, description, amount_cents, incurred_at, UNIX_TIMESTAMP(incurred_at) AS incurred_epoch, receipt_url, notes FROM `${database_name}`.expenses WHERE incurred_at BETWEEN '$from_iso' AND '$to_iso' ORDER BY incurred_at DESC, id DESC");
389 my @cat_totals = &silenced_query($dbh, "SELECT category, COALESCE(SUM(amount_cents),0) AS cents, COUNT(*) AS n FROM `${database_name}`.expenses WHERE incurred_at BETWEEN '$from_iso' AND '$to_iso' GROUP BY category ORDER BY cents DESC");
390
391 my $net_cents = $revenue_cents - $expense_cents;
392 my $net_margin = $revenue_cents ? sprintf('%.1f', 100 * $net_cents / $revenue_cents) : '0.0';
393
394 $db->db_disconnect($dbh);
395
396 my $range_controls = MODS::AnonPaths::render_range_controls($preset_val, $range_from, $range_to);
397 my $today = &strftime_today;
398
399 my $cat_rows = &build_cat_rows(\@cat_totals, $expense_cents);
400 my $inv_rows = &build_inv_rows(\@invoices);
401 my $exp_rows = &build_exp_rows(\@expenses, $preset_val, $range_from, $range_to);
402
403 my $flash_html = '';
404 if(length $flash){
405 my $f = &h_esc($flash);
406 $flash_html = qq~<div class="erng-flash">$f</div>~;
407 }
408
409 my $rev_disp = &fmt_money($revenue_cents);
410 my $exp_disp = &fmt_money($expense_cents);
411 my $net_disp = &fmt_money($net_cents);
412 my $net_color = ($net_cents >= 0) ? 'erng-pos' : 'erng-neg';
413 my $inv_n = &fmt_n($invoice_count);
414 my $exp_n = &fmt_n($expense_count);
415 my $inv_s = ($invoice_count == 1) ? '' : 's';
416 my $exp_s = ($expense_count == 1) ? '' : 's';
417 my $css = &earnings_css;
418
419 my $body = qq~
420$css
421
422<div class="erng-hero">
423 <h1>Earnings &amp; Expenses</h1>
424 <p class="sub">Super-admin financial summary. Income comes from paid invoices in the window. Expenses are a manual ledger you maintain here. Numbers are gross; check with your accountant before filing.</p>
425</div>
426
427$flash_html
428
429<form method="GET" action="/admin_earnings.cgi" class="erng-toolbar">
430 $range_controls
431</form>
432
433<div class="erng-kpi-grid">
434 <div class="erng-kpi k-rev">
435 <div class="lbl">Revenue</div>
436 <div class="val erng-pos">$rev_disp</div>
437 <div class="sub">$inv_n paid invoice$inv_s</div>
438 </div>
439 <div class="erng-kpi k-exp">
440 <div class="lbl">Expenses</div>
441 <div class="val erng-neg">$exp_disp</div>
442 <div class="sub">$exp_n expense$exp_s logged</div>
443 </div>
444 <div class="erng-kpi k-net">
445 <div class="lbl">Net</div>
446 <div class="val $net_color">$net_disp</div>
447 <div class="sub">Revenue minus expenses</div>
448 </div>
449 <div class="erng-kpi k-mar">
450 <div class="lbl">Net margin</div>
451 <div class="val">$net_margin%</div>
452 <div class="sub">Net &divide; revenue</div>
453 </div>
454</div>
455
456<div class="erng-panel">
457 <div class="erng-panel-head">
458 <div>
459 <div class="erng-panel-title">Add expense</div>
460 <div class="erng-panel-sub">Logged expenses count against this window's net.</div>
461 </div>
462 </div>
463 <form method="POST" action="/admin_earnings.cgi" class="erng-add-form">
464 <input type="hidden" name="act" value="add_expense">
465 <input type="hidden" name="preset" value="$preset_val">
466 <input type="hidden" name="from" value="$range_from">
467 <input type="hidden" name="to" value="$range_to">
468 <label>Date <input type="date" name="incurred_at" value="$today" required></label>
469 <label>Category <input type="text" name="category" list="erng-cats" value="other" required></label>
470 <label>Vendor <input type="text" name="vendor" placeholder="e.g. AWS, Plesk"></label>
471 <label>Description <input type="text" name="description" placeholder="What was this for?"></label>
472 <label>Amount (\$) <input type="number" name="amount" step="0.01" placeholder="0.00" required></label>
473 <label>Receipt URL <input type="url" name="receipt_url" placeholder="Optional link to receipt"></label>
474 <div class="row-end"><button type="submit" class="erng-btn">Add</button></div>
475 </form>
476 <datalist id="erng-cats">
477 <option value="hosting">
478 <option value="software">
479 <option value="marketing">
480 <option value="contractor">
481 <option value="payroll">
482 <option value="fees">
483 <option value="advertising">
484 <option value="domains">
485 <option value="travel">
486 <option value="office">
487 <option value="legal">
488 <option value="taxes">
489 <option value="other">
490 </datalist>
491</div>
492
493<div class="erng-panel">
494 <div class="erng-panel-head">
495 <div>
496 <div class="erng-panel-title">Expenses by category</div>
497 <div class="erng-panel-sub">Where the money went this window.</div>
498 </div>
499 </div>
500 <div class="erng-cat-grid">$cat_rows</div>
501</div>
502
503<div class="erng-panel">
504 <div class="erng-panel-head">
505 <div>
506 <div class="erng-panel-title">Expense ledger</div>
507 <div class="erng-panel-sub">Edit or delete any row. Newest first.</div>
508 </div>
509 </div>
510 <table class="erng-table">
511 <thead><tr><th>Date</th><th>Category</th><th>Vendor</th><th>Description</th><th class="t-right">Amount</th><th class="t-right">&nbsp;</th></tr></thead>
512 <tbody>$exp_rows</tbody>
513 </table>
514</div>
515
516<div class="erng-panel">
517 <div class="erng-panel-head">
518 <div>
519 <div class="erng-panel-title">Paid invoices</div>
520 <div class="erng-panel-sub">Every invoice in the window with status=paid.</div>
521 </div>
522 </div>
523 <table class="erng-table">
524 <thead><tr><th>Paid at</th><th>Invoice #</th><th>Company</th><th class="t-right">Amount</th></tr></thead>
525 <tbody>$inv_rows</tbody>
526 </table>
527</div>
528
529<script>
530function ergEdit(eid){ var r=document.getElementById('erng-edit-'+eid); if(r) r.style.display='table-row'; }
531function ergCancel(eid){ var r=document.getElementById('erng-edit-'+eid); if(r) r.style.display='none'; }
532</script>
533~;
534
535 print qq~Content-Type: text/html; charset=utf-8\nCache-Control: no-cache\n\n~;
536 $load->render({
537 userinfo => $userinfo,
538 page_key => 'admin_earnings',
539 title => 'Earnings & Expenses',
540 body => $body,
541 });
542}