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

O Operator
Diff

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

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

Added
+751
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to 6b439497b251
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/affiliate.3dshawn.com';
20use CGI;
21use MODS::Template;
22use MODS::DBConnect;
23use MODS::Login;
24use MODS::AffSoft::Config;
25use MODS::AffSoft::Wrapper;
26use MODS::AffSoft::Permissions;
27use MODS::AffSoft::Tax;
28
29my $q = CGI->new;
30my $form = $q->Vars;
31my $auth = MODS::Login->new;
32my $wrap = MODS::AffSoft::Wrapper->new;
33my $tfile = MODS::Template->new;
34my $db = MODS::DBConnect->new;
35my $cfg = MODS::AffSoft::Config->new;
36my $tax = MODS::AffSoft::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::AffSoft::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_at_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 is_active => $j->{is_active} ? 1 : 0,
462 edit_href => "/admin_tax.cgi?tab=jurisdictions&edit=$j->{id}",
463 };
464}
465
466my @set_rows;
467foreach my $s (@settings) {
468 push @set_rows, {
469 id => $s->{id},
470 country => _h($s->{country_iso2}),
471 region => _h($s->{region_code} || ''),
472 collects => $s->{platform_collects} ? 1 : 0,
473 oss_scheme => _h($s->{oss_scheme}),
474 reg_number => _h($s->{registration_number} || ''),
475 price_display=> _h($s->{price_display}),
476 edit_href => "/admin_tax.cgi?tab=settings&edit=$s->{id}",
477 };
478}
479
480my @sel_rows;
481my ($n_plat, $n_self) = (0, 0);
482foreach my $s (@seller_rows) {
483 my $mode = $s->{tax_mode} || 'platform_collects';
484 if ($mode eq 'seller_handles') { $n_self++; } else { $n_plat++; }
485 push @sel_rows, {
486 id => $s->{id},
487 name => _h($s->{name} || $s->{subdomain} || ('SF#' . $s->{id})),
488 subdomain => _h($s->{subdomain} || ''),
489 owner_email => _h($s->{email} || ''),
490 owner_name => _h($s->{display_name} || ''),
491 mode => _h($mode),
492 is_platform => ($mode eq 'platform_collects') ? 1 : 0,
493 is_self => ($mode eq 'seller_handles') ? 1 : 0,
494 tax_id_kind => _h($s->{seller_tax_id_kind} || ''),
495 tax_id_value => _h($s->{seller_tax_id_value} || ''),
496 has_tax_id => length($s->{seller_tax_id_value} || '') ? 1 : 0,
497 edit_href => "/admin_tax.cgi?tab=sellers&edit=$s->{id}",
498 };
499}
500
501my @aud_rows;
502foreach my $a (@audit_rows) {
503 push @aud_rows, {
504 id => $a->{id},
505 when => _h($a->{created_at} || ''),
506 when_epoch => ($a->{created_at_epoch} || 0) + 0,
507 actor => _h($a->{actor_name} || $a->{actor_email} || 'system'),
508 action => _h($a->{action}),
509 target => _h($a->{target_kind}) . ' #' . _h($a->{target_id} || '?'),
510 before => _h($a->{before_json} || ''),
511 after => _h($a->{after_json} || ''),
512 };
513}
514
515my $tvars = {
516 tab_jurisdictions => ($tab eq 'jurisdictions') ? 1 : 0,
517 tab_settings => ($tab eq 'settings') ? 1 : 0,
518 tab_sellers => ($tab eq 'sellers') ? 1 : 0,
519 tab_reports => ($tab eq 'reports') ? 1 : 0,
520 tab_k1099 => ($tab eq 'k1099') ? 1 : 0,
521 tab_audit => ($tab eq 'audit') ? 1 : 0,
522
523 schema_ready => $schema_ready ? 1 : 0,
524 schema_missing => $schema_ready ? 0 : 1,
525 is_super => $is_super ? 1 : 0,
526 not_super => $is_super ? 0 : 1,
527
528 flash_ok => _h($flash_ok),
529 has_flash_ok => length($flash_ok) ? 1 : 0,
530 flash_err => _h($flash_err),
531 has_flash_err => length($flash_err) ? 1 : 0,
532
533 jur_rows => \@jur_rows,
534 has_jur_rows => scalar(@jur_rows) ? 1 : 0,
535 set_rows => \@set_rows,
536 has_set_rows => scalar(@set_rows) ? 1 : 0,
537 aud_rows => \@aud_rows,
538 has_aud_rows => scalar(@aud_rows) ? 1 : 0,
539
540 # Edit form prefills.
541 edit_jur => $edit_jur,
542 has_edit_jur => $edit_jur ? 1 : 0,
543 edit_setting => $edit_setting,
544 has_edit_setting => $edit_setting ? 1 : 0,
545 edit_seller => $edit_seller,
546 has_edit_seller => $edit_seller ? 1 : 0,
547
548 # Sellers tab
549 sel_rows => \@sel_rows,
550 has_sel_rows => scalar(@sel_rows) ? 1 : 0,
551 sel_count_platform=> $n_plat,
552 sel_count_self => $n_self,
553 platform_default_mode => _h($platform_default_mode),
554 default_is_platform => ($platform_default_mode eq 'platform_collects') ? 1 : 0,
555 default_is_self => ($platform_default_mode eq 'seller_handles') ? 1 : 0,
556
557 # Reports tab
558 rep_rows => \@tvars_reports_rows,
559 has_rep_rows => scalar(@tvars_reports_rows) ? 1 : 0,
560 rep_from => _h($tvars_reports_from),
561 rep_to => _h($tvars_reports_to),
562 rep_total => _h($tvars_reports_total),
563 rep_taxable => _h($tvars_reports_taxable),
564 rep_orders => $tvars_reports_orders,
565 rep_csv_href => "/admin_tax.cgi?tab=reports&from=$tvars_reports_from&to=$tvars_reports_to&export=csv",
566
567 # 1099-K tab
568 k_rows => \@tvars_k1099_rows,
569 has_k_rows => scalar(@tvars_k1099_rows) ? 1 : 0,
570 k_year => _h($tvars_k1099_year),
571 k_total => _h($tvars_k1099_total),
572 k_threshold => _h($tvars_k1099_threshold),
573 k_n_over => $tvars_k1099_n_over,
574 k_csv_href => "/admin_tax.cgi?tab=k1099&year=$tvars_k1099_year&export=csv",
575};
576
577# Edit-form prefill scalars (so the template doesn't need to dig into
578# hashes -- our template engine isn't great at nested deref).
579if ($edit_jur) {
580 $tvars->{ej_id} = $edit_jur->{id};
581 $tvars->{ej_country} = _h($edit_jur->{country_iso2});
582 $tvars->{ej_region} = _h($edit_jur->{region_code} || '');
583 $tvars->{ej_display} = _h($edit_jur->{display_name});
584 $tvars->{ej_type} = _h($edit_jur->{tax_type});
585 $tvars->{ej_rate} = sprintf('%.4f', $edit_jur->{rate_pct});
586 $tvars->{ej_applies} = _h($edit_jur->{applies_to});
587 $tvars->{ej_eff_from} = _h($edit_jur->{effective_from} || '');
588 $tvars->{ej_eff_to} = _h($edit_jur->{effective_to} || '');
589 $tvars->{ej_active} = $edit_jur->{is_active} ? 1 : 0;
590 $tvars->{ej_notes} = _h($edit_jur->{notes} || '');
591 $tvars->{ej_type_vat} = ($edit_jur->{tax_type} eq 'vat') ? 'selected' : '';
592 $tvars->{ej_type_sales} = ($edit_jur->{tax_type} eq 'sales') ? 'selected' : '';
593 $tvars->{ej_type_gst} = ($edit_jur->{tax_type} eq 'gst') ? 'selected' : '';
594 $tvars->{ej_type_hst} = ($edit_jur->{tax_type} eq 'hst') ? 'selected' : '';
595 $tvars->{ej_type_pst} = ($edit_jur->{tax_type} eq 'pst') ? 'selected' : '';
596 $tvars->{ej_type_qst} = ($edit_jur->{tax_type} eq 'qst') ? 'selected' : '';
597 $tvars->{ej_type_other} = ($edit_jur->{tax_type} eq 'other') ? 'selected' : '';
598 $tvars->{ej_applies_digital} = ($edit_jur->{applies_to} eq 'digital') ? 'selected' : '';
599 $tvars->{ej_applies_physical}= ($edit_jur->{applies_to} eq 'physical') ? 'selected' : '';
600 $tvars->{ej_applies_both} = ($edit_jur->{applies_to} eq 'both') ? 'selected' : '';
601}
602if ($edit_seller) {
603 $tvars->{esr_id} = $edit_seller->{id};
604 $tvars->{esr_name} = _h($edit_seller->{name} || $edit_seller->{subdomain} || ('SF#' . $edit_seller->{id}));
605 $tvars->{esr_subdomain} = _h($edit_seller->{subdomain} || '');
606 $tvars->{esr_owner} = _h(($edit_seller->{display_name} || '') . ' (' . ($edit_seller->{email} || '') . ')');
607 $tvars->{esr_mode} = _h($edit_seller->{tax_mode} || 'platform_collects');
608 $tvars->{esr_kind} = _h($edit_seller->{seller_tax_id_kind} || '');
609 $tvars->{esr_value} = _h($edit_seller->{seller_tax_id_value} || '');
610 $tvars->{esr_is_plat} = (($edit_seller->{tax_mode} || 'platform_collects') eq 'platform_collects') ? 1 : 0;
611 $tvars->{esr_is_self} = (($edit_seller->{tax_mode} || '') eq 'seller_handles') ? 1 : 0;
612 $tvars->{esr_kind_us} = ($edit_seller->{seller_tax_id_kind} || '') eq 'us_state_seller_permit' ? 'selected' : '';
613 $tvars->{esr_kind_eu} = ($edit_seller->{seller_tax_id_kind} || '') eq 'eu_vat' ? 'selected' : '';
614 $tvars->{esr_kind_gb} = ($edit_seller->{seller_tax_id_kind} || '') eq 'gb_vat' ? 'selected' : '';
615 $tvars->{esr_kind_ca} = ($edit_seller->{seller_tax_id_kind} || '') eq 'ca_gst' ? 'selected' : '';
616 $tvars->{esr_kind_au} = ($edit_seller->{seller_tax_id_kind} || '') eq 'au_abn' ? 'selected' : '';
617 $tvars->{esr_kind_oth} = ($edit_seller->{seller_tax_id_kind} || '') eq 'other' ? 'selected' : '';
618}
619if ($edit_setting) {
620 $tvars->{es_id} = $edit_setting->{id};
621 $tvars->{es_country} = _h($edit_setting->{country_iso2});
622 $tvars->{es_region} = _h($edit_setting->{region_code} || '');
623 $tvars->{es_collects} = $edit_setting->{platform_collects} ? 1 : 0;
624 $tvars->{es_scheme} = _h($edit_setting->{oss_scheme});
625 $tvars->{es_reg} = _h($edit_setting->{registration_number} || '');
626 $tvars->{es_display} = _h($edit_setting->{price_display});
627 $tvars->{es_notes} = _h($edit_setting->{notes} || '');
628 $tvars->{es_scheme_none} = ($edit_setting->{oss_scheme} eq 'none') ? 'selected' : '';
629 $tvars->{es_scheme_eu} = ($edit_setting->{oss_scheme} eq 'eu_oss') ? 'selected' : '';
630 $tvars->{es_scheme_uk} = ($edit_setting->{oss_scheme} eq 'uk_vat') ? 'selected' : '';
631 $tvars->{es_scheme_ioss} = ($edit_setting->{oss_scheme} eq 'ioss') ? 'selected' : '';
632 $tvars->{es_scheme_us} = ($edit_setting->{oss_scheme} eq 'us_mpf') ? 'selected' : '';
633 $tvars->{es_scheme_ca} = ($edit_setting->{oss_scheme} eq 'ca_gst') ? 'selected' : '';
634 $tvars->{es_scheme_au} = ($edit_setting->{oss_scheme} eq 'au_gst') ? 'selected' : '';
635 $tvars->{es_display_inc} = ($edit_setting->{price_display} eq 'inclusive') ? 'selected' : '';
636 $tvars->{es_display_exc} = ($edit_setting->{price_display} eq 'exclusive') ? 'selected' : '';
637}
638
639print "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";
640
641my $body = join('', $tfile->template('webstls_admin_tax.html', $tvars, $userinfo));
642
643$wrap->render({
644 userinfo => $userinfo,
645 page_key => 'admin_tax',
646 title => 'Admin · Tax',
647 body => $body,
648});
649exit;
650
651#======================================================================
652# Helpers
653#======================================================================
654sub _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; }
655sub _esc { my $s = shift; $s = '' unless defined $s; $s =~ s/\\/\\\\/g; $s =~ s/'/''/g; return $s; }
656
657sub _juris_form {
658 my ($form) = @_;
659 my %r;
660 $r{country_iso2} = uc(substr(($form->{country_iso2} || ''), 0, 2));
661 $r{country_iso2} =~ s/[^A-Z]//g;
662 return (err => 'Country code required (ISO 3166 alpha-2).') unless length($r{country_iso2}) == 2;
663
664 $r{region_code} = substr(($form->{region_code} || ''), 0, 10);
665 $r{region_code} =~ s/[^A-Za-z0-9]//g;
666
667 $r{display_name} = substr(($form->{display_name} || ''), 0, 120);
668 return (err => 'Display name required.') unless length $r{display_name};
669
670 my $type = lc($form->{tax_type} || 'vat');
671 $type = 'vat' unless $type =~ /^(vat|sales|gst|hst|pst|qst|other)$/;
672 $r{tax_type} = $type;
673
674 my $rate = $form->{rate_pct} || '';
675 $rate =~ s/[^0-9.]//g;
676 return (err => 'Rate required.') unless length $rate;
677 $rate = sprintf('%.4f', $rate);
678 return (err => 'Rate must be between 0 and 100.') if $rate < 0 || $rate > 100;
679 $r{rate_pct} = $rate;
680
681 my $applies = lc($form->{applies_to} || 'both');
682 $applies = 'both' unless $applies =~ /^(digital|physical|both)$/;
683 $r{applies_to} = $applies;
684
685 my $ef = substr(($form->{effective_from} || ''), 0, 10);
686 $ef =~ s/[^0-9-]//g;
687 $ef = '' unless $ef =~ /^\d{4}-\d{2}-\d{2}$/;
688 return (err => 'Effective-from date required (YYYY-MM-DD).') unless length $ef;
689 $r{effective_from} = $ef;
690
691 my $et = substr(($form->{effective_to} || ''), 0, 10);
692 $et =~ s/[^0-9-]//g;
693 $et = '' unless $et =~ /^\d{4}-\d{2}-\d{2}$/;
694 $r{effective_to} = $et;
695
696 $r{is_active} = ($form->{is_active} && $form->{is_active} eq '1') ? 1 : 0;
697 $r{notes} = substr(($form->{notes} || ''), 0, 250);
698 return %r;
699}
700
701sub _juris_insert {
702 my ($db, $dbh, $DB, $r) = @_;
703 my $et_sql = length($r->{effective_to}) ? "'$r->{effective_to}'" : 'NULL';
704 $db->db_readwrite($dbh, qq~
705 INSERT INTO `${DB}`.tax_jurisdictions SET
706 country_iso2='$r->{country_iso2}',
707 region_code='$r->{region_code}',
708 display_name='~ . _esc($r->{display_name}) . qq~',
709 tax_type='$r->{tax_type}',
710 rate_pct=$r->{rate_pct},
711 applies_to='$r->{applies_to}',
712 effective_from='$r->{effective_from}',
713 effective_to=$et_sql,
714 is_active='$r->{is_active}',
715 notes='~ . _esc($r->{notes}) . qq~',
716 created_at=NOW()
717 ~, $ENV{SCRIPT_NAME}, __LINE__);
718 my $row = $db->db_readwrite($dbh, qq~SELECT LAST_INSERT_ID() AS id~, $ENV{SCRIPT_NAME}, __LINE__);
719 return ($row && $row->{id}) ? $row->{id} : 0;
720}
721
722sub _juris_update {
723 my ($db, $dbh, $DB, $jid, $r) = @_;
724 my $et_sql = length($r->{effective_to}) ? "'$r->{effective_to}'" : 'NULL';
725 $db->db_readwrite($dbh, qq~
726 UPDATE `${DB}`.tax_jurisdictions SET
727 country_iso2='$r->{country_iso2}',
728 region_code='$r->{region_code}',
729 display_name='~ . _esc($r->{display_name}) . qq~',
730 tax_type='$r->{tax_type}',
731 rate_pct=$r->{rate_pct},
732 applies_to='$r->{applies_to}',
733 effective_from='$r->{effective_from}',
734 effective_to=$et_sql,
735 is_active='$r->{is_active}',
736 notes='~ . _esc($r->{notes}) . qq~'
737 WHERE id='$jid'
738 ~, $ENV{SCRIPT_NAME}, __LINE__);
739}
740
741sub _hash_to_text {
742 my $h = shift;
743 return '' unless ref $h eq 'HASH';
744 my @kv;
745 foreach my $k (sort keys %$h) {
746 my $v = $h->{$k}; $v = '' unless defined $v;
747 $v =~ s/[\r\n]+/ /g;
748 push @kv, "$k=$v";
749 }
750 return join('; ', @kv);
751}