Diff -- /var/www/vhosts/3dshawn.com/crm.3dshawn.com/admin_tax.cgi

O Operator
Diff

/var/www/vhosts/3dshawn.com/crm.3dshawn.com/admin_tax.cgi

added on local at 2026-07-11 18:31:45

Added
+771
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to 5c6630290168
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# WebSTLs -- Admin Tax (jurisdictions / settings / audit log)
4#
5# Super-admin only -- the platform owner sets which countries WebSTLs
6# is registered to collect for, which OSS scheme each falls under,
7# and the rates themselves. Regular admins can VIEW; only super
8# admins can save changes. Every change writes a tax_audit_log row.
9#
10# Tabs:
11# jurisdictions -- rates table, add/edit/retire
12# settings -- per-jurisdiction MPF policy (collects yes/no,
13# OSS scheme, registration #, price display)
14# audit -- read-only audit trail of every change
15#======================================================================
16use strict;
17use warnings;
18
19use lib '/var/www/vhosts/3dshawn.com/crm.3dshawn.com';
20use CGI;
21use MODS::Template;
22use MODS::DBConnect;
23use MODS::Login;
24use MODS::ContactForge::Config;
25use MODS::ContactForge::Wrapper;
26use MODS::ContactForge::Permissions;
27use MODS::ContactForge::Tax;
28
29my $q = CGI->new;
30my $form = $q->Vars;
31my $auth = MODS::Login->new;
32my $wrap = MODS::ContactForge::Wrapper->new;
33my $tfile = MODS::Template->new;
34my $db = MODS::DBConnect->new;
35my $cfg = MODS::ContactForge::Config->new;
36my $tax = MODS::ContactForge::Tax->new;
37my $DB = $cfg->settings('database_name');
38
39$|=1;
40
41my $userinfo = $auth->login_verify();
42unless ($userinfo) {
43 print "Status: 302 Found\nLocation: /login.cgi?return=/admin_tax.cgi\n\n";
44 exit;
45}
46unless ($userinfo->{is_admin}) {
47 print "Status: 403 Forbidden\nContent-Type: text/plain\n\nAdmin access required.\n";
48 exit;
49}
50
51# Resolved at request time so impersonation can't elevate to super.
52my $perms = MODS::ContactForge::Permissions->new;
53my $is_super = $perms->is_super_admin($userinfo) ? 1 : 0;
54
55my $dbh = $db->db_connect();
56my $schema_ready = $tax->schema_ready($db, $dbh, $DB);
57
58my $tab = lc($form->{tab} || 'jurisdictions');
59$tab =~ s/[^a-z0-9]//g;
60$tab = 'jurisdictions' unless $tab =~ /^(jurisdictions|settings|sellers|reports|k1099|audit)$/;
61
62my $flash_ok = '';
63my $flash_err = '';
64
65#----------------------------------------------------------------------
66# POST handlers -- super-admin only
67#----------------------------------------------------------------------
68if ($schema_ready && ($ENV{REQUEST_METHOD} || '') eq 'POST') {
69 if (!$is_super) {
70 $flash_err = 'Only the platform owner can edit tax settings.';
71 } else {
72 my $action = $form->{action} || '';
73 if ($action eq 'jurisdiction_create') {
74 my %r = _juris_form($form);
75 if ($r{err}) { $flash_err = $r{err}; }
76 else {
77 my $jid = _juris_insert($db, $dbh, $DB, \%r);
78 if ($jid) {
79 $tax->audit($db, $dbh, $DB,
80 actor_user_id => $userinfo->{user_id},
81 action => 'jurisdiction_create',
82 target_kind => 'tax_jurisdictions',
83 target_id => $jid,
84 after => _hash_to_text(\%r),
85 );
86 $flash_ok = "Added $r{display_name}.";
87 } else {
88 $flash_err = 'Insert failed.';
89 }
90 }
91 }
92 elsif ($action eq 'jurisdiction_edit') {
93 my $jid = $form->{id} || 0; $jid =~ s/[^0-9]//g;
94 if ($jid) {
95 my %r = _juris_form($form);
96 if ($r{err}) { $flash_err = $r{err}; }
97 else {
98 my $before = $db->db_readwrite($dbh, qq~
99 SELECT id, country_iso2, region_code, display_name,
100 tax_type, rate_pct, applies_to,
101 effective_from, effective_to, is_active, notes
102 FROM `${DB}`.tax_jurisdictions WHERE id='$jid' LIMIT 1
103 ~, $ENV{SCRIPT_NAME}, __LINE__);
104 _juris_update($db, $dbh, $DB, $jid, \%r);
105 $tax->audit($db, $dbh, $DB,
106 actor_user_id => $userinfo->{user_id},
107 action => 'jurisdiction_edit',
108 target_kind => 'tax_jurisdictions',
109 target_id => $jid,
110 before => _hash_to_text($before),
111 after => _hash_to_text(\%r),
112 );
113 $flash_ok = "Saved $r{display_name}.";
114 }
115 }
116 }
117 elsif ($action eq 'jurisdiction_retire') {
118 my $jid = $form->{id} || 0; $jid =~ s/[^0-9]//g;
119 if ($jid) {
120 $db->db_readwrite($dbh, qq~
121 UPDATE `${DB}`.tax_jurisdictions
122 SET is_active=0,
123 effective_to=COALESCE(effective_to, CURDATE())
124 WHERE id='$jid'
125 ~, $ENV{SCRIPT_NAME}, __LINE__);
126 $tax->audit($db, $dbh, $DB,
127 actor_user_id => $userinfo->{user_id},
128 action => 'jurisdiction_retire',
129 target_kind => 'tax_jurisdictions',
130 target_id => $jid,
131 );
132 $flash_ok = 'Jurisdiction retired (kept for historical orders).';
133 }
134 }
135 elsif ($action eq 'seller_mode_edit') {
136 my $sfid = $form->{sf_id} || 0; $sfid =~ s/[^0-9]//g;
137 if ($sfid) {
138 my $mode = $form->{tax_mode} || 'platform_collects';
139 $mode = 'platform_collects' unless $mode =~ /^(platform_collects|seller_handles)$/;
140 my $kind = substr(($form->{seller_tax_id_kind} || ''), 0, 32);
141 my $val = substr(($form->{seller_tax_id_value} || ''), 0, 64);
142 $kind =~ s/[^A-Za-z0-9_\-]//g;
143 my $k_sql = length($kind) ? "'" . _esc($kind) . "'" : 'NULL';
144 my $v_sql = length($val) ? "'" . _esc($val) . "'" : 'NULL';
145 my $before = $db->db_readwrite($dbh, qq~
146 SELECT id, name, tax_mode, seller_tax_id_kind, seller_tax_id_value
147 FROM `${DB}`.storefronts WHERE id='$sfid' LIMIT 1
148 ~, $ENV{SCRIPT_NAME}, __LINE__);
149 $db->db_readwrite($dbh, qq~
150 UPDATE `${DB}`.storefronts
151 SET tax_mode='$mode',
152 seller_tax_id_kind=$k_sql,
153 seller_tax_id_value=$v_sql
154 WHERE id='$sfid'
155 ~, $ENV{SCRIPT_NAME}, __LINE__);
156 $tax->audit($db, $dbh, $DB,
157 actor_user_id => $userinfo->{user_id},
158 action => 'seller_mode_edit',
159 target_kind => 'storefronts',
160 target_id => $sfid,
161 before => _hash_to_text($before),
162 after => "tax_mode=$mode; seller_tax_id_kind=$kind; seller_tax_id_value=$val",
163 );
164 $flash_ok = 'Seller tax mode saved.';
165 }
166 }
167 elsif ($action eq 'settings_edit') {
168 my $sid = $form->{id} || 0; $sid =~ s/[^0-9]//g;
169 if ($sid) {
170 my $before = $db->db_readwrite($dbh, qq~
171 SELECT id, country_iso2, region_code, platform_collects,
172 oss_scheme, registration_number, price_display, notes
173 FROM `${DB}`.tax_settings WHERE id='$sid' LIMIT 1
174 ~, $ENV{SCRIPT_NAME}, __LINE__);
175
176 my $pc = ($form->{platform_collects} && $form->{platform_collects} eq '1') ? 1 : 0;
177 my $sc = $form->{oss_scheme} || 'none';
178 $sc = 'none' unless $sc =~ /^(none|eu_oss|uk_vat|ioss|us_mpf|ca_gst|au_gst)$/;
179 my $rn = substr(($form->{registration_number} || ''), 0, 64);
180 my $pd = ($form->{price_display} && $form->{price_display} eq 'inclusive') ? 'inclusive' : 'exclusive';
181 my $nt = substr(($form->{notes} || ''), 0, 2000);
182
183 my $rn_sql = length($rn) ? "'" . _esc($rn) . "'" : 'NULL';
184 $db->db_readwrite($dbh, qq~
185 UPDATE `${DB}`.tax_settings
186 SET platform_collects='$pc',
187 oss_scheme='$sc',
188 registration_number=$rn_sql,
189 price_display='$pd',
190 notes='~ . _esc($nt) . qq~'
191 WHERE id='$sid'
192 ~, $ENV{SCRIPT_NAME}, __LINE__);
193
194 $tax->audit($db, $dbh, $DB,
195 actor_user_id => $userinfo->{user_id},
196 action => 'settings_edit',
197 target_kind => 'tax_settings',
198 target_id => $sid,
199 before => _hash_to_text($before),
200 after => "platform_collects=$pc; oss_scheme=$sc; registration=$rn; price_display=$pd",
201 );
202 $flash_ok = 'Saved.';
203 }
204 }
205 }
206}
207
208#----------------------------------------------------------------------
209# Page data per tab
210#----------------------------------------------------------------------
211my @jurisdictions;
212my @settings;
213my @audit_rows;
214my @seller_rows;
215my ($edit_jur, $edit_setting, $edit_seller);
216my $platform_default_mode = 'platform_collects';
217
218# Tab-specific buckets populated only when the matching tab runs.
219my @tvars_reports_rows;
220my ($tvars_reports_from, $tvars_reports_to, $tvars_reports_total,
221 $tvars_reports_taxable, $tvars_reports_orders) = ('', '', '$0.00', '$0.00', 0);
222my @tvars_k1099_rows;
223my ($tvars_k1099_year, $tvars_k1099_total, $tvars_k1099_threshold,
224 $tvars_k1099_n_over) = ('', '$0.00', '$5,000.00', 0);
225
226if ($schema_ready) {
227 if ($tab eq 'jurisdictions') {
228 @jurisdictions = $tax->list_jurisdictions($db, $dbh, $DB);
229 if ($form->{edit}) {
230 my $jid = $form->{edit}; $jid =~ s/[^0-9]//g;
231 $edit_jur = $db->db_readwrite($dbh, qq~
232 SELECT id, country_iso2, region_code, display_name,
233 tax_type, rate_pct, applies_to,
234 DATE_FORMAT(effective_from,'%Y-%m-%d') AS effective_from,
235 DATE_FORMAT(effective_to,'%Y-%m-%d') AS effective_to,
236 is_active, notes
237 FROM `${DB}`.tax_jurisdictions WHERE id='$jid' LIMIT 1
238 ~, $ENV{SCRIPT_NAME}, __LINE__) if $jid;
239 }
240 }
241 elsif ($tab eq 'settings') {
242 @settings = $tax->list_settings($db, $dbh, $DB);
243 if ($form->{edit}) {
244 my $sid = $form->{edit}; $sid =~ s/[^0-9]//g;
245 $edit_setting = $db->db_readwrite($dbh, qq~
246 SELECT id, country_iso2, region_code, platform_collects,
247 oss_scheme, registration_number, price_display, notes
248 FROM `${DB}`.tax_settings WHERE id='$sid' LIMIT 1
249 ~, $ENV{SCRIPT_NAME}, __LINE__) if $sid;
250 }
251 }
252 elsif ($tab eq 'sellers') {
253 # Pull every storefront with its current tax_mode + seller tax
254 # ID. tax_mode column added in the same migration as the tax
255 # tables, so a guard saves a 500 if migration hasn't run on
256 # the storefronts table yet.
257 my $col = $db->db_readwrite($dbh, qq~
258 SELECT COUNT(*) AS n FROM information_schema.columns
259 WHERE table_schema='$DB' AND table_name='storefronts' AND column_name='tax_mode'
260 ~, $ENV{SCRIPT_NAME}, __LINE__);
261 if ($col && $col->{n}) {
262 @seller_rows = $db->db_readwrite_multiple($dbh, qq~
263 SELECT s.id, s.name, s.subdomain, s.tax_mode,
264 s.seller_tax_id_kind, s.seller_tax_id_value,
265 u.id AS user_id, u.email, u.display_name
266 FROM `${DB}`.storefronts s
267 JOIN `${DB}`.users u ON u.id = s.user_id
268 ORDER BY u.email, s.id
269 ~, $ENV{SCRIPT_NAME}, __LINE__);
270 }
271 if ($form->{edit}) {
272 my $sfid = $form->{edit}; $sfid =~ s/[^0-9]//g;
273 $edit_seller = $db->db_readwrite($dbh, qq~
274 SELECT s.id, s.name, s.subdomain, s.tax_mode,
275 s.seller_tax_id_kind, s.seller_tax_id_value,
276 u.email, u.display_name
277 FROM `${DB}`.storefronts s
278 JOIN `${DB}`.users u ON u.id = s.user_id
279 WHERE s.id='$sfid' LIMIT 1
280 ~, $ENV{SCRIPT_NAME}, __LINE__) if $sfid;
281 }
282 # Read the platform default so the form can show the inherited
283 # value when the seller has no override.
284 my $def = $db->db_readwrite($dbh, qq~
285 SELECT setting_value FROM `${DB}`.platform_settings
286 WHERE setting_key='tax.new_seller_default_mode' LIMIT 1
287 ~, $ENV{SCRIPT_NAME}, __LINE__);
288 $platform_default_mode = ($def && $def->{setting_value}) || 'platform_collects';
289 }
290 elsif ($tab eq 'reports') {
291 # Date range -- default to current year-to-date.
292 my $from = substr(($form->{from} || ''), 0, 10);
293 my $to = substr(($form->{to} || ''), 0, 10);
294 $from = '' unless $from =~ /^\d{4}-\d{2}-\d{2}$/;
295 $to = '' unless $to =~ /^\d{4}-\d{2}-\d{2}$/;
296 $from = sprintf('%04d-01-01', (localtime)[5] + 1900) unless length $from;
297 $to = sprintf('%04d-%02d-%02d', (localtime)[5] + 1900, (localtime)[4] + 1, (localtime)[3]) unless length $to;
298 my $f_safe = _esc($from); my $t_safe = _esc($to);
299
300 my @rep = $db->db_readwrite_multiple($dbh, qq~
301 SELECT otl.country_iso2, otl.region_code, otl.jurisdiction_label,
302 otl.tax_type, otl.rate_pct,
303 COUNT(*) AS n_orders,
304 SUM(otl.taxable_amount_cents) AS taxable_cents,
305 SUM(otl.tax_amount_cents) AS tax_cents,
306 SUM(CASE WHEN otl.basis='reverse_charge' THEN 1 ELSE 0 END) AS n_reverse
307 FROM `${DB}`.order_tax_lines otl
308 JOIN `${DB}`.orders o ON o.id = otl.order_id
309 WHERE o.status='paid'
310 AND DATE(o.paid_at) BETWEEN '$f_safe' AND '$t_safe'
311 GROUP BY otl.country_iso2, otl.region_code, otl.jurisdiction_label, otl.tax_type, otl.rate_pct
312 ORDER BY tax_cents DESC
313 ~, $ENV{SCRIPT_NAME}, __LINE__);
314
315 # CSV export: stream rows + exit. Skip the wrapper render.
316 if (($form->{export} || '') eq 'csv') {
317 $db->db_disconnect($dbh);
318 print "Content-Type: text/csv; charset=utf-8\nContent-Disposition: attachment; filename=\"webstls_tax_report_${from}_${to}.csv\"\n\n";
319 print "country,region,jurisdiction,tax_type,rate_pct,orders,taxable_cents,tax_cents,reverse_charge_orders\n";
320 foreach my $r (@rep) {
321 my @c = (
322 $r->{country_iso2} || '',
323 $r->{region_code} || '',
324 $r->{jurisdiction_label} || '',
325 $r->{tax_type} || '',
326 sprintf('%.4f', $r->{rate_pct} || 0),
327 $r->{n_orders} || 0,
328 $r->{taxable_cents} || 0,
329 $r->{tax_cents} || 0,
330 $r->{n_reverse} || 0,
331 );
332 # Escape any embedded commas/quotes per RFC4180.
333 @c = map { my $v = $_; $v =~ s/"/""/g; $v =~ /[",\n]/ ? qq{"$v"} : $v } @c;
334 print join(',', @c) . "\n";
335 }
336 exit;
337 }
338
339 # Build display rows for the template.
340 my $tot_tax = 0; my $tot_taxable = 0; my $tot_orders = 0;
341 @audit_rows = (); # reuse slot? no -- separate var below
342 @tvars_reports_rows = ();
343 foreach my $r (@rep) {
344 $tot_tax += ($r->{tax_cents} || 0);
345 $tot_taxable += ($r->{taxable_cents} || 0);
346 $tot_orders += ($r->{n_orders} || 0);
347 push @tvars_reports_rows, {
348 country => _h($r->{country_iso2}),
349 region => _h($r->{region_code} || ''),
350 label => _h($r->{jurisdiction_label}),
351 type => _h(uc $r->{tax_type}),
352 rate => sprintf('%.2f', $r->{rate_pct}),
353 n_orders => $r->{n_orders} || 0,
354 taxable => '$' . sprintf('%.2f', ($r->{taxable_cents} || 0) / 100),
355 tax => '$' . sprintf('%.2f', ($r->{tax_cents} || 0) / 100),
356 n_reverse => $r->{n_reverse} || 0,
357 };
358 }
359 $tvars_reports_from = $from;
360 $tvars_reports_to = $to;
361 $tvars_reports_total = '$' . sprintf('%.2f', $tot_tax / 100);
362 $tvars_reports_taxable= '$' . sprintf('%.2f', $tot_taxable / 100);
363 $tvars_reports_orders = $tot_orders;
364 }
365 elsif ($tab eq 'k1099') {
366 # Year selector + per-seller annual gross.
367 my $year = $form->{year} || ((localtime)[5] + 1900);
368 $year =~ s/[^0-9]//g; $year = (localtime)[5] + 1900 unless $year =~ /^\d{4}$/;
369 my $y_from = "$year-01-01";
370 my $y_to = "$year-12-31";
371 my $threshold_cents = 500000; # US federal 1099-K threshold ($5,000 -- 2024+ phasedown)
372
373 my @k = $db->db_readwrite_multiple($dbh, qq~
374 SELECT u.id AS user_id, u.email, u.display_name,
375 s.id AS storefront_id, s.name AS storefront_name,
376 COUNT(o.id) AS n_orders,
377 SUM(o.total_amount_cents) AS gross_cents
378 FROM `${DB}`.orders o
379 JOIN `${DB}`.storefronts s ON s.id = o.storefront_id
380 JOIN `${DB}`.users u ON u.id = s.user_id
381 WHERE o.status='paid'
382 AND DATE(o.paid_at) BETWEEN '$y_from' AND '$y_to'
383 GROUP BY u.id, s.id
384 ORDER BY gross_cents DESC
385 ~, $ENV{SCRIPT_NAME}, __LINE__);
386
387 if (($form->{export} || '') eq 'csv') {
388 $db->db_disconnect($dbh);
389 print "Content-Type: text/csv; charset=utf-8\nContent-Disposition: attachment; filename=\"webstls_1099k_${year}.csv\"\n\n";
390 print "user_id,email,display_name,storefront_id,storefront_name,year,orders,gross_cents,over_threshold\n";
391 foreach my $r (@k) {
392 my $gross = $r->{gross_cents} || 0;
393 my $over = ($gross >= $threshold_cents) ? 1 : 0;
394 my @c = (
395 $r->{user_id}, $r->{email} || '', $r->{display_name} || '',
396 $r->{storefront_id}, $r->{storefront_name} || '',
397 $year, $r->{n_orders} || 0, $gross, $over,
398 );
399 @c = map { my $v = $_; $v = '' unless defined $v; $v =~ s/"/""/g; $v =~ /[",\n]/ ? qq{"$v"} : $v } @c;
400 print join(',', @c) . "\n";
401 }
402 exit;
403 }
404
405 @tvars_k1099_rows = ();
406 my $tot_gross = 0; my $n_over = 0;
407 foreach my $r (@k) {
408 my $gross = $r->{gross_cents} || 0;
409 $tot_gross += $gross;
410 my $over = ($gross >= $threshold_cents) ? 1 : 0;
411 $n_over++ if $over;
412 push @tvars_k1099_rows, {
413 user_id => $r->{user_id},
414 email => _h($r->{email} || ''),
415 name => _h($r->{display_name} || ''),
416 storefront => _h($r->{storefront_name} || ('SF#' . $r->{storefront_id})),
417 n_orders => $r->{n_orders} || 0,
418 gross => '$' . sprintf('%.2f', $gross / 100),
419 over_th => $over,
420 };
421 }
422 $tvars_k1099_year = $year;
423 $tvars_k1099_total = '$' . sprintf('%.2f', $tot_gross / 100);
424 $tvars_k1099_threshold = '$' . sprintf('%.2f', $threshold_cents / 100);
425 $tvars_k1099_n_over = $n_over;
426 }
427 elsif ($tab eq 'audit') {
428 @audit_rows = $db->db_readwrite_multiple($dbh, qq~
429 SELECT a.id, a.actor_user_id, a.action, a.target_kind,
430 a.target_id,
431 LEFT(a.before_json, 200) AS before_json,
432 LEFT(a.after_json, 200) AS after_json,
433 a.created_at,
434 UNIX_TIMESTAMP(a.created_at) AS created_epoch,
435 u.email AS actor_email,
436 u.display_name AS actor_name
437 FROM `${DB}`.tax_audit_log a
438 LEFT JOIN `${DB}`.users u ON u.id = a.actor_user_id
439 ORDER BY a.id DESC
440 LIMIT 200
441 ~, $ENV{SCRIPT_NAME}, __LINE__);
442 }
443}
444
445$db->db_disconnect($dbh);
446
447#----------------------------------------------------------------------
448# Build template rows
449#----------------------------------------------------------------------
450my @jur_rows;
451foreach my $j (@jurisdictions) {
452 push @jur_rows, {
453 id => $j->{id},
454 country => _h($j->{country_iso2}),
455 region => _h($j->{region_code} || ''),
456 display_name => _h($j->{display_name}),
457 tax_type => _h(uc $j->{tax_type}),
458 rate => sprintf('%.4f', $j->{rate_pct}),
459 applies_to => _h($j->{applies_to}),
460 effective => _h($j->{effective_from} || '') . ' \→ ' . _h($j->{effective_to} || 'now'),
461 effective_from => _h($j->{effective_from} || ''),
462 effective_to => _h($j->{effective_to} || ''),
463 effective_from_epoch => _atx_ts_epoch($j->{effective_from} || ''),
464 effective_to_epoch => _atx_ts_epoch($j->{effective_to} || ''),
465 has_effective_to => ($j->{effective_to} ? 1 : 0),
466 is_active => $j->{is_active} ? 1 : 0,
467 edit_href => "/admin_tax.cgi?tab=jurisdictions&edit=$j->{id}",
468 };
469}
470
471my @set_rows;
472foreach my $s (@settings) {
473 push @set_rows, {
474 id => $s->{id},
475 country => _h($s->{country_iso2}),
476 region => _h($s->{region_code} || ''),
477 collects => $s->{platform_collects} ? 1 : 0,
478 oss_scheme => _h($s->{oss_scheme}),
479 reg_number => _h($s->{registration_number} || ''),
480 price_display=> _h($s->{price_display}),
481 edit_href => "/admin_tax.cgi?tab=settings&edit=$s->{id}",
482 };
483}
484
485my @sel_rows;
486my ($n_plat, $n_self) = (0, 0);
487foreach my $s (@seller_rows) {
488 my $mode = $s->{tax_mode} || 'platform_collects';
489 if ($mode eq 'seller_handles') { $n_self++; } else { $n_plat++; }
490 push @sel_rows, {
491 id => $s->{id},
492 name => _h($s->{name} || $s->{subdomain} || ('SF#' . $s->{id})),
493 subdomain => _h($s->{subdomain} || ''),
494 owner_email => _h($s->{email} || ''),
495 owner_name => _h($s->{display_name} || ''),
496 mode => _h($mode),
497 is_platform => ($mode eq 'platform_collects') ? 1 : 0,
498 is_self => ($mode eq 'seller_handles') ? 1 : 0,
499 tax_id_kind => _h($s->{seller_tax_id_kind} || ''),
500 tax_id_value => _h($s->{seller_tax_id_value} || ''),
501 has_tax_id => length($s->{seller_tax_id_value} || '') ? 1 : 0,
502 edit_href => "/admin_tax.cgi?tab=sellers&edit=$s->{id}",
503 };
504}
505
506my @aud_rows;
507foreach my $a (@audit_rows) {
508 push @aud_rows, {
509 id => $a->{id},
510 when => _h($a->{created_at} || ''),
511 when_epoch => ($a->{created_epoch} || 0),
512 actor => _h($a->{actor_name} || $a->{actor_email} || 'system'),
513 action => _h($a->{action}),
514 target => _h($a->{target_kind}) . ' #' . _h($a->{target_id} || '?'),
515 before => _h($a->{before_json} || ''),
516 after => _h($a->{after_json} || ''),
517 };
518}
519
520my $tvars = {
521 tab_jurisdictions => ($tab eq 'jurisdictions') ? 1 : 0,
522 tab_settings => ($tab eq 'settings') ? 1 : 0,
523 tab_sellers => ($tab eq 'sellers') ? 1 : 0,
524 tab_reports => ($tab eq 'reports') ? 1 : 0,
525 tab_k1099 => ($tab eq 'k1099') ? 1 : 0,
526 tab_audit => ($tab eq 'audit') ? 1 : 0,
527
528 schema_ready => $schema_ready ? 1 : 0,
529 schema_missing => $schema_ready ? 0 : 1,
530 is_super => $is_super ? 1 : 0,
531 not_super => $is_super ? 0 : 1,
532
533 flash_ok => _h($flash_ok),
534 has_flash_ok => length($flash_ok) ? 1 : 0,
535 flash_err => _h($flash_err),
536 has_flash_err => length($flash_err) ? 1 : 0,
537
538 jur_rows => \@jur_rows,
539 has_jur_rows => scalar(@jur_rows) ? 1 : 0,
540 set_rows => \@set_rows,
541 has_set_rows => scalar(@set_rows) ? 1 : 0,
542 aud_rows => \@aud_rows,
543 has_aud_rows => scalar(@aud_rows) ? 1 : 0,
544
545 # Edit form prefills.
546 edit_jur => $edit_jur,
547 has_edit_jur => $edit_jur ? 1 : 0,
548 edit_setting => $edit_setting,
549 has_edit_setting => $edit_setting ? 1 : 0,
550 edit_seller => $edit_seller,
551 has_edit_seller => $edit_seller ? 1 : 0,
552
553 # Sellers tab
554 sel_rows => \@sel_rows,
555 has_sel_rows => scalar(@sel_rows) ? 1 : 0,
556 sel_count_platform=> $n_plat,
557 sel_count_self => $n_self,
558 platform_default_mode => _h($platform_default_mode),
559 default_is_platform => ($platform_default_mode eq 'platform_collects') ? 1 : 0,
560 default_is_self => ($platform_default_mode eq 'seller_handles') ? 1 : 0,
561
562 # Reports tab
563 rep_rows => \@tvars_reports_rows,
564 has_rep_rows => scalar(@tvars_reports_rows) ? 1 : 0,
565 rep_from => _h($tvars_reports_from),
566 rep_to => _h($tvars_reports_to),
567 rep_total => _h($tvars_reports_total),
568 rep_taxable => _h($tvars_reports_taxable),
569 rep_orders => $tvars_reports_orders,
570 rep_csv_href => "/admin_tax.cgi?tab=reports&from=$tvars_reports_from&to=$tvars_reports_to&export=csv",
571
572 # 1099-K tab
573 k_rows => \@tvars_k1099_rows,
574 has_k_rows => scalar(@tvars_k1099_rows) ? 1 : 0,
575 k_year => _h($tvars_k1099_year),
576 k_total => _h($tvars_k1099_total),
577 k_threshold => _h($tvars_k1099_threshold),
578 k_n_over => $tvars_k1099_n_over,
579 k_csv_href => "/admin_tax.cgi?tab=k1099&year=$tvars_k1099_year&export=csv",
580};
581
582# Edit-form prefill scalars (so the template doesn't need to dig into
583# hashes -- our template engine isn't great at nested deref).
584if ($edit_jur) {
585 $tvars->{ej_id} = $edit_jur->{id};
586 $tvars->{ej_country} = _h($edit_jur->{country_iso2});
587 $tvars->{ej_region} = _h($edit_jur->{region_code} || '');
588 $tvars->{ej_display} = _h($edit_jur->{display_name});
589 $tvars->{ej_type} = _h($edit_jur->{tax_type});
590 $tvars->{ej_rate} = sprintf('%.4f', $edit_jur->{rate_pct});
591 $tvars->{ej_applies} = _h($edit_jur->{applies_to});
592 $tvars->{ej_eff_from} = _h($edit_jur->{effective_from} || '');
593 $tvars->{ej_eff_to} = _h($edit_jur->{effective_to} || '');
594 $tvars->{ej_active} = $edit_jur->{is_active} ? 1 : 0;
595 $tvars->{ej_notes} = _h($edit_jur->{notes} || '');
596 $tvars->{ej_type_vat} = ($edit_jur->{tax_type} eq 'vat') ? 'selected' : '';
597 $tvars->{ej_type_sales} = ($edit_jur->{tax_type} eq 'sales') ? 'selected' : '';
598 $tvars->{ej_type_gst} = ($edit_jur->{tax_type} eq 'gst') ? 'selected' : '';
599 $tvars->{ej_type_hst} = ($edit_jur->{tax_type} eq 'hst') ? 'selected' : '';
600 $tvars->{ej_type_pst} = ($edit_jur->{tax_type} eq 'pst') ? 'selected' : '';
601 $tvars->{ej_type_qst} = ($edit_jur->{tax_type} eq 'qst') ? 'selected' : '';
602 $tvars->{ej_type_other} = ($edit_jur->{tax_type} eq 'other') ? 'selected' : '';
603 $tvars->{ej_applies_digital} = ($edit_jur->{applies_to} eq 'digital') ? 'selected' : '';
604 $tvars->{ej_applies_physical}= ($edit_jur->{applies_to} eq 'physical') ? 'selected' : '';
605 $tvars->{ej_applies_both} = ($edit_jur->{applies_to} eq 'both') ? 'selected' : '';
606}
607if ($edit_seller) {
608 $tvars->{esr_id} = $edit_seller->{id};
609 $tvars->{esr_name} = _h($edit_seller->{name} || $edit_seller->{subdomain} || ('SF#' . $edit_seller->{id}));
610 $tvars->{esr_subdomain} = _h($edit_seller->{subdomain} || '');
611 $tvars->{esr_owner} = _h(($edit_seller->{display_name} || '') . ' (' . ($edit_seller->{email} || '') . ')');
612 $tvars->{esr_mode} = _h($edit_seller->{tax_mode} || 'platform_collects');
613 $tvars->{esr_kind} = _h($edit_seller->{seller_tax_id_kind} || '');
614 $tvars->{esr_value} = _h($edit_seller->{seller_tax_id_value} || '');
615 $tvars->{esr_is_plat} = (($edit_seller->{tax_mode} || 'platform_collects') eq 'platform_collects') ? 1 : 0;
616 $tvars->{esr_is_self} = (($edit_seller->{tax_mode} || '') eq 'seller_handles') ? 1 : 0;
617 $tvars->{esr_kind_us} = ($edit_seller->{seller_tax_id_kind} || '') eq 'us_state_seller_permit' ? 'selected' : '';
618 $tvars->{esr_kind_eu} = ($edit_seller->{seller_tax_id_kind} || '') eq 'eu_vat' ? 'selected' : '';
619 $tvars->{esr_kind_gb} = ($edit_seller->{seller_tax_id_kind} || '') eq 'gb_vat' ? 'selected' : '';
620 $tvars->{esr_kind_ca} = ($edit_seller->{seller_tax_id_kind} || '') eq 'ca_gst' ? 'selected' : '';
621 $tvars->{esr_kind_au} = ($edit_seller->{seller_tax_id_kind} || '') eq 'au_abn' ? 'selected' : '';
622 $tvars->{esr_kind_oth} = ($edit_seller->{seller_tax_id_kind} || '') eq 'other' ? 'selected' : '';
623}
624if ($edit_setting) {
625 $tvars->{es_id} = $edit_setting->{id};
626 $tvars->{es_country} = _h($edit_setting->{country_iso2});
627 $tvars->{es_region} = _h($edit_setting->{region_code} || '');
628 $tvars->{es_collects} = $edit_setting->{platform_collects} ? 1 : 0;
629 $tvars->{es_scheme} = _h($edit_setting->{oss_scheme});
630 $tvars->{es_reg} = _h($edit_setting->{registration_number} || '');
631 $tvars->{es_display} = _h($edit_setting->{price_display});
632 $tvars->{es_notes} = _h($edit_setting->{notes} || '');
633 $tvars->{es_scheme_none} = ($edit_setting->{oss_scheme} eq 'none') ? 'selected' : '';
634 $tvars->{es_scheme_eu} = ($edit_setting->{oss_scheme} eq 'eu_oss') ? 'selected' : '';
635 $tvars->{es_scheme_uk} = ($edit_setting->{oss_scheme} eq 'uk_vat') ? 'selected' : '';
636 $tvars->{es_scheme_ioss} = ($edit_setting->{oss_scheme} eq 'ioss') ? 'selected' : '';
637 $tvars->{es_scheme_us} = ($edit_setting->{oss_scheme} eq 'us_mpf') ? 'selected' : '';
638 $tvars->{es_scheme_ca} = ($edit_setting->{oss_scheme} eq 'ca_gst') ? 'selected' : '';
639 $tvars->{es_scheme_au} = ($edit_setting->{oss_scheme} eq 'au_gst') ? 'selected' : '';
640 $tvars->{es_display_inc} = ($edit_setting->{price_display} eq 'inclusive') ? 'selected' : '';
641 $tvars->{es_display_exc} = ($edit_setting->{price_display} eq 'exclusive') ? 'selected' : '';
642}
643
644print "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";
645
646my $body = join('', $tfile->template('webstls_admin_tax.html', $tvars, $userinfo));
647
648$wrap->render({
649 userinfo => $userinfo,
650 page_key => 'admin_tax',
651 title => 'Admin · Tax',
652 body => $body,
653});
654exit;
655
656#======================================================================
657# Helpers
658#======================================================================
659sub _h { my $s = shift; $s = '' unless defined $s; $s =~ s/&/&amp;/g; $s =~ s/</&lt;/g; $s =~ s/>/&gt;/g; $s =~ s/"/&quot;/g; $s =~ s/'/&#39;/g; return $s; }
660sub _esc { my $s = shift; $s = '' unless defined $s; $s =~ s/\\/\\\\/g; $s =~ s/'/''/g; return $s; }
661
662# MySQL DATE/DATETIME string -> UNIX epoch for portfolio-wide <span
663# class="ts"> tz-localization. Empty/unparseable returns 0 so JS falls
664# back to the human string in the span body.
665sub _atx_ts_epoch {
666 my $s = shift;
667 return 0 unless defined $s && length $s;
668 return 0 if $s eq '-' || $s eq 'NEVER';
669 if ($s =~ /^(\d{4})-(\d{1,2})-(\d{1,2})(?:[ T](\d{1,2}):(\d{1,2})(?::(\d{1,2}))?)?/) {
670 require Time::Local;
671 my $ep = eval { Time::Local::timelocal(($6||0), ($5||0), ($4||0), $3, $2 - 1, $1 - 1900) };
672 return $ep ? $ep + 0 : 0;
673 }
674 return 0;
675}
676
677sub _juris_form {
678 my ($form) = @_;
679 my %r;
680 $r{country_iso2} = uc(substr(($form->{country_iso2} || ''), 0, 2));
681 $r{country_iso2} =~ s/[^A-Z]//g;
682 return (err => 'Country code required (ISO 3166 alpha-2).') unless length($r{country_iso2}) == 2;
683
684 $r{region_code} = substr(($form->{region_code} || ''), 0, 10);
685 $r{region_code} =~ s/[^A-Za-z0-9]//g;
686
687 $r{display_name} = substr(($form->{display_name} || ''), 0, 120);
688 return (err => 'Display name required.') unless length $r{display_name};
689
690 my $type = lc($form->{tax_type} || 'vat');
691 $type = 'vat' unless $type =~ /^(vat|sales|gst|hst|pst|qst|other)$/;
692 $r{tax_type} = $type;
693
694 my $rate = $form->{rate_pct} || '';
695 $rate =~ s/[^0-9.]//g;
696 return (err => 'Rate required.') unless length $rate;
697 $rate = sprintf('%.4f', $rate);
698 return (err => 'Rate must be between 0 and 100.') if $rate < 0 || $rate > 100;
699 $r{rate_pct} = $rate;
700
701 my $applies = lc($form->{applies_to} || 'both');
702 $applies = 'both' unless $applies =~ /^(digital|physical|both)$/;
703 $r{applies_to} = $applies;
704
705 my $ef = substr(($form->{effective_from} || ''), 0, 10);
706 $ef =~ s/[^0-9-]//g;
707 $ef = '' unless $ef =~ /^\d{4}-\d{2}-\d{2}$/;
708 return (err => 'Effective-from date required (YYYY-MM-DD).') unless length $ef;
709 $r{effective_from} = $ef;
710
711 my $et = substr(($form->{effective_to} || ''), 0, 10);
712 $et =~ s/[^0-9-]//g;
713 $et = '' unless $et =~ /^\d{4}-\d{2}-\d{2}$/;
714 $r{effective_to} = $et;
715
716 $r{is_active} = ($form->{is_active} && $form->{is_active} eq '1') ? 1 : 0;
717 $r{notes} = substr(($form->{notes} || ''), 0, 250);
718 return %r;
719}
720
721sub _juris_insert {
722 my ($db, $dbh, $DB, $r) = @_;
723 my $et_sql = length($r->{effective_to}) ? "'$r->{effective_to}'" : 'NULL';
724 $db->db_readwrite($dbh, qq~
725 INSERT INTO `${DB}`.tax_jurisdictions SET
726 country_iso2='$r->{country_iso2}',
727 region_code='$r->{region_code}',
728 display_name='~ . _esc($r->{display_name}) . qq~',
729 tax_type='$r->{tax_type}',
730 rate_pct=$r->{rate_pct},
731 applies_to='$r->{applies_to}',
732 effective_from='$r->{effective_from}',
733 effective_to=$et_sql,
734 is_active='$r->{is_active}',
735 notes='~ . _esc($r->{notes}) . qq~',
736 created_at=NOW()
737 ~, $ENV{SCRIPT_NAME}, __LINE__);
738 my $row = $db->db_readwrite($dbh, qq~SELECT LAST_INSERT_ID() AS id~, $ENV{SCRIPT_NAME}, __LINE__);
739 return ($row && $row->{id}) ? $row->{id} : 0;
740}
741
742sub _juris_update {
743 my ($db, $dbh, $DB, $jid, $r) = @_;
744 my $et_sql = length($r->{effective_to}) ? "'$r->{effective_to}'" : 'NULL';
745 $db->db_readwrite($dbh, qq~
746 UPDATE `${DB}`.tax_jurisdictions SET
747 country_iso2='$r->{country_iso2}',
748 region_code='$r->{region_code}',
749 display_name='~ . _esc($r->{display_name}) . qq~',
750 tax_type='$r->{tax_type}',
751 rate_pct=$r->{rate_pct},
752 applies_to='$r->{applies_to}',
753 effective_from='$r->{effective_from}',
754 effective_to=$et_sql,
755 is_active='$r->{is_active}',
756 notes='~ . _esc($r->{notes}) . qq~'
757 WHERE id='$jid'
758 ~, $ENV{SCRIPT_NAME}, __LINE__);
759}
760
761sub _hash_to_text {
762 my $h = shift;
763 return '' unless ref $h eq 'HASH';
764 my @kv;
765 foreach my $k (sort keys %$h) {
766 my $v = $h->{$k}; $v = '' unless defined $v;
767 $v =~ s/[\r\n]+/ /g;
768 push @kv, "$k=$v";
769 }
770 return join('; ', @kv);
771}