4 use vars qw( @ISA $DEBUG $me
5 %tax_unittypes %tax_maxtypes %tax_basetypes %tax_authorities
6 %tax_passtypes %GetInfoType $keep_cch_files );
9 use DateTime::Format::Strptime;
10 use Storable qw( thaw nfreeze );
19 use DBIx::DBSchema::Table;
20 use DBIx::DBSchema::Column;
22 use FS::Record qw( qsearch qsearchs dbh dbdef );
25 use FS::cust_bill_pkg;
26 use FS::cust_tax_location;
27 use FS::tax_rate_location;
28 use FS::part_pkg_taxrate;
29 use FS::part_pkg_taxproduct;
31 use FS::Misc qw( csv_from_fixed );
35 @ISA = qw( FS::Record );
38 $me = '[FS::tax_rate]';
43 FS::tax_rate - Object methods for tax_rate objects
49 $record = new FS::tax_rate \%hash;
50 $record = new FS::tax_rate { 'column' => 'value' };
52 $error = $record->insert;
54 $error = $new_record->replace($old_record);
56 $error = $record->delete;
58 $error = $record->check;
62 An FS::tax_rate object represents a tax rate, defined by locale.
63 FS::tax_rate inherits from FS::Record. The following fields are
70 primary key (assigned automatically for new tax rates)
74 a geographic location code provided by a tax data vendor
82 a location code provided by a tax authority
86 a foreign key into FS::tax_class - the type of tax
87 referenced but FS::part_pkg_taxrate
90 the time after which the tax applies
98 second bracket percentage
102 the amount to which the tax applies (first bracket)
106 a cap on the amount of tax if a cap exists
110 percentage on out of jurisdiction purchases
114 second bracket percentage on out of jurisdiction purchases
118 one of the values in %tax_unittypes
122 amount of tax per unit
126 second bracket amount of tax per unit
130 the number of units to which the fee applies (first bracket)
134 the most units to which fees apply (first and second brackets)
138 a value from %tax_maxtypes indicating how brackets accumulate (i.e. monthly, per invoice, etc)
142 if defined, printed on invoices instead of "Tax"
146 a value from %tax_authorities
150 a value from %tax_basetypes indicating the tax basis
154 a value from %tax_passtypes indicating how the tax should displayed to the customer
158 'Y', 'N', or blank indicating the tax can be passed to the customer
162 if 'Y', this tax does not apply to setup fees
166 if 'Y', this tax does not apply to recurring fees
170 if 'Y', has been manually edited
180 Creates a new tax rate. To add the tax rate to the database, see L<"insert">.
184 sub table { 'tax_rate'; }
188 Adds this tax rate to the database. If there is an error, returns the error,
189 otherwise returns false.
193 Deletes this tax rate from the database. If there is an error, returns the
194 error, otherwise returns false.
196 =item replace OLD_RECORD
198 Replaces the OLD_RECORD with this one in the database. If there is an error,
199 returns the error, otherwise returns false.
203 Checks all fields to make sure this is a valid tax rate. If there is an error,
204 returns the error, otherwise returns false. Called by the insert and replace
212 foreach (qw( taxbase taxmax )) {
213 $self->$_(0) unless $self->$_;
216 $self->ut_numbern('taxnum')
217 || $self->ut_text('geocode')
218 || $self->ut_textn('data_vendor')
219 || $self->ut_cch_textn('location')
220 || $self->ut_foreign_key('taxclassnum', 'tax_class', 'taxclassnum')
221 || $self->ut_snumbern('effective_date')
222 || $self->ut_float('tax')
223 || $self->ut_floatn('excessrate')
224 || $self->ut_money('taxbase')
225 || $self->ut_money('taxmax')
226 || $self->ut_floatn('usetax')
227 || $self->ut_floatn('useexcessrate')
228 || $self->ut_numbern('unittype')
229 || $self->ut_floatn('fee')
230 || $self->ut_floatn('excessfee')
231 || $self->ut_floatn('feemax')
232 || $self->ut_numbern('maxtype')
233 || $self->ut_textn('taxname')
234 || $self->ut_numbern('taxauth')
235 || $self->ut_numbern('basetype')
236 || $self->ut_numbern('passtype')
237 || $self->ut_enum('passflag', [ '', 'Y', 'N' ])
238 || $self->ut_enum('setuptax', [ '', 'Y' ] )
239 || $self->ut_enum('recurtax', [ '', 'Y' ] )
240 || $self->ut_enum('inoutcity', [ '', 'I', 'O' ] )
241 || $self->ut_enum('inoutlocal', [ '', 'I', 'O' ] )
242 || $self->ut_enum('manual', [ '', 'Y' ] )
243 || $self->ut_enum('disabled', [ '', 'Y' ] )
244 || $self->SUPER::check
249 #ut_text / ut_textn w/ ` added cause now that's in the data
252 $self->getfield($field)
253 =~ /^([\wô \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=\[\]\<\>\`]*)$/
254 or return gettext('illegal_or_empty_text'). " $field: ".
255 $self->getfield($field);
256 $self->setfield($field,$1);
261 =item taxclass_description
263 Returns the human understandable value associated with the related
268 sub taxclass_description {
270 my $tax_class = qsearchs('tax_class', {'taxclassnum' => $self->taxclassnum });
271 $tax_class ? $tax_class->description : '';
276 Returns the human understandable value associated with the unittype column
280 %tax_unittypes = ( '0' => 'access line',
287 $tax_unittypes{$self->unittype};
292 Returns the human understandable value associated with the maxtype column.
296 # XXX these are non-functional, and most of them are horrible to implement
297 # in our current model
299 %tax_maxtypes = ( '0' => 'receipts per invoice',
300 '1' => 'receipts per item',
301 '2' => 'total utility charges per utility tax year',
302 '3' => 'total charges per utility tax year',
303 '4' => 'receipts per access line',
304 '7' => 'total utility charges per calendar year',
305 '9' => 'monthly receipts per location',
306 '10' => 'monthly receipts exceeds taxbase and total tax per month does not exceed maxtax', # wtf?
307 '11' => 'receipts/units per access line',
308 '14' => 'units per invoice',
309 '15' => 'units per month',
310 '18' => 'units per account',
315 $tax_maxtypes{$self->maxtype};
320 Returns the human understandable value associated with the basetype column
324 %tax_basetypes = ( '0' => 'sale price',
325 '1' => 'gross receipts',
326 '2' => 'sales taxable telecom revenue',
327 '3' => 'minutes carried',
328 '4' => 'minutes billed',
329 '5' => 'gross operating revenue',
330 '6' => 'access line',
332 '8' => 'gross revenue',
333 '9' => 'portion gross receipts attributable to interstate service',
334 '10' => 'access line',
335 '11' => 'gross profits',
336 '12' => 'tariff rate',
338 '15' => 'prior year gross receipts',
343 $tax_basetypes{$self->basetype};
348 Returns the human understandable value associated with the taxauth column
352 %tax_authorities = ( '0' => 'federal',
357 '5' => 'county administered by state',
358 '6' => 'city administered by state',
359 '7' => 'city administered by county',
360 '8' => 'local administered by state',
361 '9' => 'local administered by county',
366 $tax_authorities{$self->taxauth};
371 Returns the human understandable value associated with the passtype column
375 %tax_passtypes = ( '0' => 'separate tax line',
376 '1' => 'separate surcharge line',
377 '2' => 'surcharge not separated',
378 '3' => 'included in base rate',
383 $tax_passtypes{$self->passtype};
386 #Returns a listref of a name and an amount of tax calculated for the list
387 #of packages/amounts referenced by TAXABLES. If an error occurs, a message
388 #is returned as a scalar.
390 =item taxline TAXABLES_ARRAYREF, [ OPTION => VALUE ... ]
392 Takes an arrayref of L<FS::cust_bill_pkg> objects representing taxable
393 line items, and returns some number of new L<FS::cust_bill_pkg> objects
394 representing the tax on them under this tax rate. Each returned object
395 will correspond to a single input line item.
397 For accurate calculation of per-customer or per-location taxes, ALL items
398 appearing on the invoice MUST be passed to this method together.
400 Optionally, any of the L<FS::cust_bill_pkg> objects may be followed in the
401 array by a charge class: 'setup', 'recur', '' (for unclassified usage), or an
402 integer denoting an L<FS::usage_class> number. In this case, the tax will
403 only be charged on that portion of the line item.
405 Each returned object will have a pseudo-field,
406 "cust_bill_pkg_tax_rate_location", containing a single
407 L<FS::cust_bill_pkg_tax_rate_location> object. This will in turn
408 have a "taxable_cust_bill_pkg" pseudo-field linking it to one of the taxable
409 items. All of these links must be resolved as the objects are inserted.
411 If the tax is disabled, this method will return nothing. Be prepared for
414 In addition to calculating the tax for the line items, this will calculate
415 tax exemptions and attach them to the line items. I<Currently this only
416 supports customer exemptions.>
418 Options may include 'custnum' and 'invoice_time' in case the cust_bill_pkg
419 objects belong to an invoice that hasn't been inserted yet.
421 The 'exemptions' option allowed in L<FS::cust_main_county::taxline> does
422 nothing here, since monthly exemptions aren't supported.
427 my( $self, $taxables, %opt) = @_;
428 $taxables = [ $taxables ] unless ref($taxables) eq 'ARRAY';
430 my $name = $self->taxname;
431 $name = 'Other surcharges'
432 if ($self->passtype == 2);
435 return unless @$taxables; # nothing to do
436 return if $self->disabled; # tax is disabled, skip it
437 return if $self->passflag eq 'N'; # tax can't be passed to the customer
438 # but should probably still appear on the liability report--create a
439 # cust_tax_exempt_pkg record for it?
441 # XXX a certain amount of false laziness with FS::cust_main_county
442 my $cust_bill = $taxables->[0]->cust_bill;
443 my $custnum = $cust_bill ? $cust_bill->custnum : $opt{'custnum'};
444 my $cust_main = FS::cust_main->by_key($custnum) if $custnum > 0;
446 die "unable to calculate taxes for an unknown customer\n";
449 my $taxratelocationnum = $self->tax_rate_location->taxratelocationnum
450 or die "no tax_rate_location linked to tax_rate #".$self->taxnum."\n";
452 warn "calculating taxes for ". $self->taxnum. " on ".
453 join (",", map { $_->pkgnum } @$taxables)
456 my $maxtype = $self->maxtype || 0;
457 if ($maxtype != 0 && $maxtype != 1
458 && $maxtype != 14 && $maxtype != 15
459 && $maxtype != 18 # sigh
461 return $self->_fatal_or_null( 'tax with "'.
462 $self->maxtype_name. '" threshold'
464 } # I don't know why, it's not like there are maxtypes that we DO support
466 # we treat gross revenue as gross receipts and expect the tax data
467 # to DTRT (i.e. tax on tax rules)
468 if ($self->basetype != 0 && $self->basetype != 1 &&
469 $self->basetype != 5 && $self->basetype != 6 &&
470 $self->basetype != 7 && $self->basetype != 8 &&
471 $self->basetype != 14
474 $self->_fatal_or_null( 'tax with "'. $self->basetype_name. '" basis' );
478 my %seen; # locationnum or pkgnum => 1
480 my $taxable_cents = 0;
481 my $taxable_units = 0;
485 my $cust_bill_pkg = shift @$taxables;
487 if ( defined($taxables->[0]) and !ref($taxables->[0]) ) {
488 $class = shift @$taxables;
491 my %usage_map = map { $_ => $cust_bill_pkg->usage($_) }
492 $cust_bill_pkg->usage_classes;
493 my $usage_total = sum( values(%usage_map), 0 );
495 # determine if the item has exemptions that apply to this tax def
496 my @exemptions = grep { $_->taxnum == $self->taxnum }
497 @{ $cust_bill_pkg->cust_tax_exempt_pkg };
499 if ( $self->tax > 0 ) {
501 my $taxable_charged = 0;
502 if ($class eq 'all') {
503 $taxable_charged = $cust_bill_pkg->setup + $cust_bill_pkg->recur;
504 } elsif ($class eq 'setup') {
505 $taxable_charged = $cust_bill_pkg->setup;
506 } elsif ($class eq 'recur') {
507 $taxable_charged = $cust_bill_pkg->recur - $usage_total;
509 $taxable_charged = $usage_map{$class} || 0;
512 foreach my $ex (@exemptions) {
513 # the only cases where the exemption doesn't apply:
514 # if it's a setup exemption and $class is not 'setup' or 'all'
515 # if it's a recur exemption and $class is 'setup'
516 if ( ( $ex->exempt_recur and $class eq 'setup' )
517 or ( $ex->exempt_setup and $class ne 'setup' and $class ne 'all' )
522 $taxable_charged -= $ex->amount;
524 # cust_main_county handles monthly capped exemptions; this doesn't.
526 # $taxable_charged can also be less than zero at this point
527 # (recur exemption + usage class breakdown); treat that as zero.
528 next if $taxable_charged <= 0;
530 # yeah, some false laziness with cust_main_county
531 my $this_tax_cents = int(100 * $taxable_charged * $self->tax);
532 my $tax_location = FS::cust_bill_pkg_tax_rate_location->new({
533 'taxnum' => $self->taxnum,
534 'taxtype' => ref($self),
535 'cents' => $this_tax_cents, # not a real field
536 'locationtaxid' => $self->location, # fundamentally silly
537 'taxable_cust_bill_pkg' => $cust_bill_pkg,
538 'taxratelocationnum' => $taxratelocationnum,
539 'taxclass' => $class,
541 push @tax_locations, $tax_location;
543 $taxable_cents += 100 * $taxable_charged;
544 $tax_cents += $this_tax_cents;
546 } elsif ( $self->fee > 0 ) {
547 # most CCH taxes are this type, because nearly every county has a 911
551 # since we don't support partial exemptions (except setup/recur),
552 # if there's an exemption that applies to this package and taxrate,
553 # don't charge ANY per-unit fees
556 # don't apply fees to usage classes (maybe if we ever get per-minute
558 next unless $class eq 'setup'
562 if ( $self->unittype == 0 ) {
563 if ( !$seen{$cust_bill_pkg->pkgnum} ) {
565 $units = $cust_bill_pkg->units;
566 $seen{$cust_bill_pkg->pkgnum} = 1;
567 } # else it's been seen, leave it at zero units
569 } elsif ($self->unittype == 1) { # per minute
570 # STILL not supported...fortunately these only exist if you happen
571 # to be in Idaho or Little Rock, Arkansas
573 # though a voip_cdr package could easily report minutes of usage...
574 return $self->_fatal_or_null( 'fee with minute unit type' );
576 } elsif ( $self->unittype == 2 ) {
578 my $locationnum = $cust_bill_pkg->tax_locationnum
579 || $cust_main->ship_locationnum;
581 $units = 1 unless $seen{$locationnum};
582 $seen{$locationnum} = 1;
585 # Unittype 19 is used for prepaid wireless E911 charges in many states.
586 # Apparently "per retail purchase", which for us would mean per invoice.
587 # Unittype 20 is used for some 911 surcharges and I have no idea what
589 return $self->_fatal_or_null( 'unknown unit type in tax'. $self->taxnum );
591 my $this_tax_cents = int($units * $self->fee * 100);
592 my $tax_location = FS::cust_bill_pkg_tax_rate_location->new({
593 'taxnum' => $self->taxnum,
594 'taxtype' => ref($self),
595 'cents' => $this_tax_cents,
596 'locationtaxid' => $self->location,
597 'taxable_cust_bill_pkg' => $cust_bill_pkg,
598 'taxratelocationnum' => $taxratelocationnum,
600 push @tax_locations, $tax_location;
602 $taxable_units += $units;
603 $tax_cents += $this_tax_cents;
606 } # foreach $cust_bill_pkg
608 # check bracket maxima; throw an error if we've gone over, because
609 # we don't really implement them
611 if ( ($self->taxmax > 0 and $taxable_cents > $self->taxmax*100 ) or
612 ($self->feemax > 0 and $taxable_units > $self->feemax) ) {
614 # (why not just cap taxable_charged/units at the taxmax/feemax? because
615 # it's way more complicated than that. this won't even catch every case
616 # where a bracket maximum should apply.)
617 return $self->_fatal_or_null( 'tax base > taxmax/feemax for tax'.$self->taxnum );
620 # round and distribute
621 my $total_tax_cents = sprintf('%.0f',
622 ($taxable_cents * $self->tax) + ($taxable_units * $self->fee * 100)
624 my $extra_cents = sprintf('%.0f', $total_tax_cents - $tax_cents);
625 $tax_cents += $extra_cents;
627 foreach (@tax_locations) { # can never require more than a single pass, yes?
628 my $cents = $_->get('cents');
629 if ( $extra_cents > 0 ) {
633 $_->set('amount', sprintf('%.2f', $cents/100));
636 # just transform each CBPTRL record into a tax line item.
637 # calculate_taxes will consolidate them, but before that happens we have
638 # to do tax on tax calculation.
640 foreach (@tax_locations) {
641 next if $_->amount == 0;
642 my $tax_item = FS::cust_bill_pkg->new({
645 'setup' => $_->amount,
646 'sdate' => '', # $_->sdate?
649 'cust_bill_pkg_tax_rate_location' => [ $_ ],
651 $_->set('tax_cust_bill_pkg' => $tax_item);
652 push @tax_items, $tax_item;
659 my ($self, $error) = @_;
661 $DB::single = 1; # not a mistake
663 my $conf = new FS::Conf;
665 $error = "can't yet handle ". $error;
666 my $name = $self->taxname;
667 $name = 'Other surcharges'
668 if ($self->passtype == 2);
670 if ($conf->exists('ignore_incalculable_taxes')) {
671 warn "WARNING: $error; billing anyway per ignore_incalculable_taxes conf\n";
672 return { name => $name, amount => 0 };
674 return "fatal: $error";
678 =item tax_on_tax CUST_LOCATION
680 Returns a list of taxes which are candidates for taxing taxes for the
681 given service location (see L<FS::cust_location>)
689 my $cust_location = shift;
691 warn "looking up taxes on tax ". $self->taxnum. " for customer ".
692 $cust_location->custnum
695 my $geocode = $cust_location->geocode($self->data_vendor);
699 my $extra_sql = ' AND ('.
700 join(' OR ', map{ 'geocode = '. $dbh->quote(substr($geocode, 0, $_)) }
705 my $order_by = 'ORDER BY taxclassnum, length(geocode) desc';
706 my $select = 'DISTINCT ON(taxclassnum) *';
708 # should qsearch preface columns with the table to facilitate joins?
709 my @taxclassnums = map { $_->taxclassnum }
710 qsearch( { 'table' => 'part_pkg_taxrate',
712 'hashref' => { 'data_vendor' => $self->data_vendor,
713 'taxclassnumtaxed' => $self->taxclassnum,
715 'extra_sql' => $extra_sql,
716 'order_by' => $order_by,
719 return () unless @taxclassnums;
722 "AND (". join(' OR ', map { "taxclassnum = $_" } @taxclassnums ). ")";
724 qsearch({ 'table' => 'tax_rate',
725 'hashref' => { 'geocode' => $geocode, },
726 'extra_sql' => $extra_sql,
731 =item tax_rate_location
733 Returns an object representing the location associated with this tax
734 (see L<FS::tax_rate_location>)
738 sub tax_rate_location {
741 qsearchs({ 'table' => 'tax_rate_location',
742 'hashref' => { 'data_vendor' => $self->data_vendor,
743 'geocode' => $self->geocode,
747 new FS::tax_rate_location;
761 sub _progressbar_foo {
766 my ($param, $job) = @_;
768 my $fh = $param->{filehandle};
769 my $format = $param->{'format'};
777 my @column_lengths = ();
778 my @column_callbacks = ();
779 if ( $format eq 'cch-fixed' || $format eq 'cch-fixed-update' ) {
780 $format =~ s/-fixed//;
781 my $date_format = sub { my $r='';
782 /^(\d{4})(\d{2})(\d{2})$/ && ($r="$3/$2/$1");
785 my $trim = sub { my $r = shift; $r =~ s/^\s*//; $r =~ s/\s*$//; $r };
786 push @column_lengths, qw( 10 1 1 8 8 5 8 8 8 1 2 2 30 8 8 10 2 8 2 1 2 2 );
787 push @column_lengths, 1 if $format eq 'cch-update';
788 push @column_callbacks, $trim foreach (@column_lengths); # 5, 6, 15, 17 esp
789 $column_callbacks[8] = $date_format;
793 my ( $count, $last, $min_sec ) = _progressbar_foo();
794 if ( $job || scalar(@column_callbacks) ) {
796 csv_from_fixed(\$fh, \$count, \@column_lengths, \@column_callbacks);
797 return $error if $error;
801 if ( $format eq 'cch' || $format eq 'cch-update' ) {
802 #false laziness w/below (sub _perform_cch_diff)
803 @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax
804 excessrate effective_date taxauth taxtype taxcat taxname
805 usetax useexcessrate fee unittype feemax maxtype passflag
807 push @fields, 'actionflag' if $format eq 'cch-update';
812 $hash->{'actionflag'} ='I' if ($hash->{'data_vendor'} eq 'cch');
813 $hash->{'data_vendor'} ='cch';
814 my $parser = new DateTime::Format::Strptime( pattern => "%m/%d/%Y",
815 time_zone => 'floating',
817 my $dt = $parser->parse_datetime( $hash->{'effective_date'} );
818 $hash->{'effective_date'} = $dt ? $dt->epoch : '';
820 $hash->{$_} =~ s/\s//g foreach qw( inoutcity inoutlocal ) ;
821 $hash->{$_} = sprintf("%.2f", $hash->{$_}) foreach qw( taxbase taxmax );
824 join(':', map{ $hash->{$_} } qw(taxtype taxcat) );
826 my %tax_class = ( 'data_vendor' => 'cch',
827 'taxclass' => $taxclassid,
830 my $tax_class = qsearchs( 'tax_class', \%tax_class );
831 return "Error updating tax rate: no tax class $taxclassid"
834 $hash->{'taxclassnum'} = $tax_class->taxclassnum;
836 foreach (qw( taxtype taxcat )) {
840 my %passflagmap = ( '0' => '',
844 $hash->{'passflag'} = $passflagmap{$hash->{'passflag'}}
845 if exists $passflagmap{$hash->{'passflag'}};
847 foreach (keys %$hash) {
848 $hash->{$_} = substr($hash->{$_}, 0, 80)
849 if length($hash->{$_}) > 80;
852 my $actionflag = delete($hash->{'actionflag'});
854 $hash->{'taxname'} =~ s/`/'/g;
855 $hash->{'taxname'} =~ s|\\|/|g;
857 return '' if $format eq 'cch'; # but not cch-update
859 if ($actionflag eq 'I') {
860 $insert{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
861 }elsif ($actionflag eq 'D') {
862 $delete{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
864 return "Unexpected action flag: ". $hash->{'actionflag'};
867 delete($hash->{$_}) for keys %$hash;
873 } elsif ( $format eq 'extended' ) {
874 die "unimplemented\n";
878 die "unknown format $format";
881 my $csv = new Text::CSV_XS;
885 local $SIG{HUP} = 'IGNORE';
886 local $SIG{INT} = 'IGNORE';
887 local $SIG{QUIT} = 'IGNORE';
888 local $SIG{TERM} = 'IGNORE';
889 local $SIG{TSTP} = 'IGNORE';
890 local $SIG{PIPE} = 'IGNORE';
892 my $oldAutoCommit = $FS::UID::AutoCommit;
893 local $FS::UID::AutoCommit = 0;
896 while ( defined($line=<$fh>) ) {
897 $csv->parse($line) or do {
898 $dbh->rollback if $oldAutoCommit;
899 return "can't parse: ". $csv->error_input();
902 if ( $job ) { # progress bar
903 if ( time - $min_sec > $last ) {
904 my $error = $job->update_statustext(
905 int( 100 * $imported / $count ). ",Importing tax rates"
908 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
915 my @columns = $csv->fields();
917 my %tax_rate = ( 'data_vendor' => $format );
918 foreach my $field ( @fields ) {
919 $tax_rate{$field} = shift @columns;
922 if ( scalar( @columns ) ) {
923 $dbh->rollback if $oldAutoCommit;
924 return "Unexpected trailing columns in line (wrong format?) importing tax_rate: $line";
927 my $error = &{$hook}(\%tax_rate);
929 $dbh->rollback if $oldAutoCommit;
933 if (scalar(keys %tax_rate)) { #inserts only, not updates for cch
935 my $tax_rate = new FS::tax_rate( \%tax_rate );
936 $error = $tax_rate->insert;
939 $dbh->rollback if $oldAutoCommit;
940 return "can't insert tax_rate for $line: $error";
949 my @replace = grep { exists($delete{$_}) } keys %insert;
951 if ( $job ) { # progress bar
952 if ( time - $min_sec > $last ) {
953 my $error = $job->update_statustext(
954 int( 100 * $imported / $count ). ",Importing tax rates"
957 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
964 my $old = qsearchs( 'tax_rate', $delete{$_} );
968 my $new = new FS::tax_rate({ $old->hash, %{$insert{$_}}, 'manual' => '' });
969 $new->taxnum($old->taxnum);
970 my $error = $new->replace($old);
973 $dbh->rollback if $oldAutoCommit;
974 my $hashref = $insert{$_};
975 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
976 return "can't replace tax_rate for $line: $error";
983 $old = delete $delete{$_};
984 warn "WARNING: can't find tax_rate to replace (inserting instead and continuing) for: ".
985 #join(" ", map { "$_ => ". $old->{$_} } @fields);
986 join(" ", map { "$_ => ". $old->{$_} } keys(%$old) );
992 for (grep { !exists($delete{$_}) } keys %insert) {
993 if ( $job ) { # progress bar
994 if ( time - $min_sec > $last ) {
995 my $error = $job->update_statustext(
996 int( 100 * $imported / $count ). ",Importing tax rates"
999 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1006 my $tax_rate = new FS::tax_rate( $insert{$_} );
1007 my $error = $tax_rate->insert;
1010 $dbh->rollback if $oldAutoCommit;
1011 my $hashref = $insert{$_};
1012 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
1013 return "can't insert tax_rate for $line: $error";
1019 for (grep { !exists($insert{$_}) } keys %delete) {
1020 if ( $job ) { # progress bar
1021 if ( time - $min_sec > $last ) {
1022 my $error = $job->update_statustext(
1023 int( 100 * $imported / $count ). ",Importing tax rates"
1026 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1033 my $tax_rate = qsearchs( 'tax_rate', $delete{$_} );
1035 $dbh->rollback if $oldAutoCommit;
1036 $tax_rate = $delete{$_};
1037 warn "WARNING: can't find tax_rate to delete for: ".
1038 join(" ", map { "$_ => ". $tax_rate->{$_} } keys(%$tax_rate) ).
1041 my $error = $tax_rate->delete; # XXX we really should not do this
1042 # (it orphans CBPTRL records)
1045 $dbh->rollback if $oldAutoCommit;
1046 my $hashref = $delete{$_};
1047 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
1048 return "can't delete tax_rate for $line: $error";
1055 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
1057 return "Empty file!" unless ($imported || $format eq 'cch-update');
1063 =item process_batch_import
1065 Load a batch import as a queued JSRPC job
1069 sub process_batch_import {
1072 my $oldAutoCommit = $FS::UID::AutoCommit;
1073 local $FS::UID::AutoCommit = 0;
1076 my $param = thaw(decode_base64(shift));
1077 my $args = '$job, encode_base64( nfreeze( $param ) )';
1079 my $method = '_perform_batch_import';
1080 if ( $param->{reload} ) {
1081 $method = 'process_batch_reload';
1084 eval "$method($args);";
1086 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1091 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
1094 sub _perform_batch_import {
1097 my $param = thaw(decode_base64(shift));
1098 my $format = $param->{'format'}; #well... this is all cch specific
1100 my $files = $param->{'uploaded_files'}
1101 or die "No files provided.";
1103 my (%files) = map { /^(\w+):((taxdata\/\w+\.\w+\/)?[\.\w]+)$/ ? ($1,$2):() }
1106 if ( $format eq 'cch' || $format eq 'cch-fixed'
1107 || $format eq 'cch-update' || $format eq 'cch-fixed-update' )
1110 my $oldAutoCommit = $FS::UID::AutoCommit;
1111 local $FS::UID::AutoCommit = 0;
1114 my @insert_list = ();
1115 my @delete_list = ();
1116 my @predelete_list = ();
1117 my $insertname = '';
1118 my $deletename = '';
1119 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
1121 my @list = ( 'GEOCODE', \&FS::tax_rate_location::batch_import,
1122 'CODE', \&FS::tax_class::batch_import,
1123 'PLUS4', \&FS::cust_tax_location::batch_import,
1124 'ZIP', \&FS::cust_tax_location::batch_import,
1125 'TXMATRIX', \&FS::part_pkg_taxrate::batch_import,
1126 'DETAIL', \&FS::tax_rate::batch_import,
1128 while( scalar(@list) ) {
1129 my ( $name, $import_sub ) = splice( @list, 0, 2 );
1130 my $file = lc($name). 'file';
1132 unless ($files{$file}) {
1133 #$error = "No $name supplied";
1136 next if $name eq 'DETAIL' && $format =~ /update/;
1138 my $filename = "$dir/". $files{$file};
1140 if ( $format =~ /update/ ) {
1142 ( $error, $insertname, $deletename ) =
1143 _perform_cch_insert_delete_split( $name, $filename, $dir, $format )
1147 unlink $filename or warn "Can't delete $filename: $!"
1148 unless $keep_cch_files;
1149 push @insert_list, $name, $insertname, $import_sub, $format;
1150 if ( $name eq 'GEOCODE' || $name eq 'CODE' ) { #handle this whole ordering issue better
1151 unshift @predelete_list, $name, $deletename, $import_sub, $format;
1153 unshift @delete_list, $name, $deletename, $import_sub, $format;
1158 push @insert_list, $name, $filename, $import_sub, $format;
1165 'DETAIL', "$dir/".$files{detailfile}, \&FS::tax_rate::batch_import, $format
1166 if $format =~ /update/;
1168 my %addl_param = ();
1169 if ( $param->{'delete_only'} ) {
1170 $addl_param{'delete_only'} = $param->{'delete_only'};
1174 $error ||= _perform_cch_tax_import( $job,
1175 [ @predelete_list ],
1182 @list = ( @predelete_list, @insert_list, @delete_list );
1183 while( !$keep_cch_files && scalar(@list) ) {
1184 my ( undef, $file, undef, undef ) = splice( @list, 0, 4 );
1185 unlink $file or warn "Can't delete $file: $!";
1189 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1192 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
1196 die "Unknown format: $format";
1202 sub _perform_cch_tax_import {
1203 my ( $job, $predelete_list, $insert_list, $delete_list, $addl_param ) = @_;
1207 foreach my $list ($predelete_list, $insert_list, $delete_list) {
1208 while( scalar(@$list) ) {
1209 my ( $name, $file, $method, $format ) = splice( @$list, 0, 4 );
1210 my $fmt = "$format-update";
1211 $fmt = $format. ( lc($name) eq 'zip' ? '-zip' : '' );
1212 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
1213 my $param = { 'filehandle' => $fh,
1217 $error ||= &{$method}($param, $job);
1225 sub _perform_cch_insert_delete_split {
1226 my ($name, $filename, $dir, $format) = @_;
1230 open my $fh, "< $filename"
1231 or $error ||= "Can't open $name file $filename: $!";
1233 my $ifh = new File::Temp( TEMPLATE => "$name.insert.XXXXXXXX",
1236 ) or die "can't open temp file: $!\n";
1237 my $insertname = $ifh->filename;
1239 my $dfh = new File::Temp( TEMPLATE => "$name.delete.XXXXXXXX",
1242 ) or die "can't open temp file: $!\n";
1243 my $deletename = $dfh->filename;
1245 my $insert_pattern = ($format eq 'cch-update') ? qr/"I"\s*$/ : qr/I\s*$/;
1246 my $delete_pattern = ($format eq 'cch-update') ? qr/"D"\s*$/ : qr/D\s*$/;
1249 $handle = $ifh if $_ =~ /$insert_pattern/;
1250 $handle = $dfh if $_ =~ /$delete_pattern/;
1252 $error = "bad input line: $_" unless $handle;
1261 return ($error, $insertname, $deletename);
1264 sub _perform_cch_diff {
1265 my ($name, $newdir, $olddir) = @_;
1270 open my $oldcsvfh, "$olddir/$name.txt"
1271 or die "failed to open $olddir/$name.txt: $!\n";
1273 while(<$oldcsvfh>) {
1280 open my $newcsvfh, "$newdir/$name.txt"
1281 or die "failed to open $newdir/$name.txt: $!\n";
1283 my $dfh = new File::Temp( TEMPLATE => "$name.diff.XXXXXXXX",
1286 ) or die "can't open temp file: $!\n";
1287 my $diffname = $dfh->filename;
1289 while(<$newcsvfh>) {
1291 if (exists($oldlines{$_})) {
1294 print $dfh $_, ',"I"', "\n";
1299 #false laziness w/above (sub batch_import)
1300 my @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax
1301 excessrate effective_date taxauth taxtype taxcat taxname
1302 usetax useexcessrate fee unittype feemax maxtype passflag
1303 passtype basetype );
1304 my $numfields = scalar(@fields);
1306 my $csv = new Text::CSV_XS { 'always_quote' => 1 };
1308 for my $line (grep $oldlines{$_}, keys %oldlines) {
1310 $csv->parse($line) or do {
1311 #$dbh->rollback if $oldAutoCommit;
1312 die "can't parse: ". $csv->error_input();
1314 my @columns = $csv->fields();
1316 $csv->combine( splice(@columns, 0, $numfields) );
1318 print $dfh $csv->string, ',"D"', "\n";
1326 sub _cch_fetch_and_unzip {
1327 my ( $job, $urls, $secret, $dir ) = @_;
1329 my $ua = new LWP::UserAgent;
1330 foreach my $url (split ',', $urls) {
1331 my @name = split '/', $url; #somewhat restrictive
1332 my $name = pop @name;
1333 $name =~ /([\w.]+)/; # untaint that which we don't trust so much any more
1336 open my $taxfh, ">$dir/$name" or die "Can't open $dir/$name: $!\n";
1338 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1339 my $res = $ua->request(
1340 new HTTP::Request( GET => $url ),
1342 print $taxfh $_[0] or die "Can't write to $dir/$name: $!\n";
1343 my $content_length = $_[1]->content_length;
1344 $imported += length($_[0]);
1345 if ( time - $min_sec > $last ) {
1346 my $error = $job->update_statustext(
1347 ($content_length ? int(100 * $imported/$content_length) : 0 ).
1348 ",Downloading data from CCH"
1350 die $error if $error;
1355 die "download of $url failed: ". $res->status_line
1356 unless $res->is_success;
1359 my $error = $job->update_statustext( "0,Unpacking data" );
1360 die $error if $error;
1361 $secret =~ /([\w.]+)/; # untaint that which we don't trust so much any more
1363 system('unzip', "-P", $secret, "-d", "$dir", "$dir/$name") == 0
1364 or die "unzip -P $secret -d $dir $dir/$name failed";
1365 #unlink "$dir/$name";
1369 sub _cch_extract_csv_from_dbf {
1370 my ( $job, $dir, $name ) = @_;
1375 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1376 my $error = $job->update_statustext( "0,Unpacking $name" );
1377 die $error if $error;
1378 warn "opening $dir.new/$name.dbf\n" if $DEBUG;
1379 my $table = new XBase 'name' => "$dir.new/$name.dbf";
1380 die "failed to access $dir.new/$name.dbf: ". XBase->errstr
1381 unless defined($table);
1382 my $count = $table->last_record; # approximately;
1383 open my $csvfh, ">$dir.new/$name.txt"
1384 or die "failed to open $dir.new/$name.txt: $!\n";
1386 my $csv = new Text::CSV_XS { 'always_quote' => 1 };
1387 my @fields = $table->field_names;
1388 my $cursor = $table->prepare_select;
1390 sub { my $date = shift;
1391 $date =~ /^(\d{4})(\d{2})(\d{2})$/ && ($date = "$2/$3/$1");
1394 while (my $row = $cursor->fetch_hashref) {
1395 $csv->combine( map { my $type = $table->field_type($_);
1397 &{$format_date}($row->{$_}) ;
1398 } elsif ($type eq 'N' && $row->{$_} =~ /e-/i ) {
1399 sprintf('%.8f', $row->{$_}); #db row is numeric(14,8)
1406 print $csvfh $csv->string, "\n";
1408 if ( time - $min_sec > $last ) {
1409 my $error = $job->update_statustext(
1410 int(100 * $imported/$count). ",Unpacking $name"
1412 die $error if $error;
1420 sub _remember_disabled_taxes {
1421 my ( $job, $format, $disabled_tax_rate ) = @_;
1425 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1427 my @items = qsearch( { table => 'tax_rate',
1428 hashref => { disabled => 'Y',
1429 data_vendor => $format,
1431 select => 'geocode, taxclassnum',
1434 my $count = scalar(@items);
1435 foreach my $tax_rate ( @items ) {
1436 if ( time - $min_sec > $last ) {
1437 $job->update_statustext(
1438 int( 100 * $imported / $count ). ",Remembering disabled taxes"
1444 qsearchs( 'tax_class', { taxclassnum => $tax_rate->taxclassnum } );
1445 unless ( $tax_class ) {
1446 warn "failed to find tax_class ". $tax_rate->taxclassnum;
1449 $disabled_tax_rate->{$tax_rate->geocode. ':'. $tax_class->taxclass} = 1;
1453 sub _remember_tax_products {
1454 my ( $job, $format, $taxproduct ) = @_;
1456 # XXX FIXME this loop only works when cch is the only data provider
1458 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1460 my $extra_sql = "WHERE taxproductnum IS NOT NULL OR ".
1461 "0 < ( SELECT count(*) from part_pkg_option WHERE ".
1462 " part_pkg_option.pkgpart = part_pkg.pkgpart AND ".
1463 " optionname LIKE 'usage_taxproductnum_%' AND ".
1464 " optionvalue != '' )";
1465 my @items = qsearch( { table => 'part_pkg',
1466 select => 'DISTINCT pkgpart,taxproductnum',
1468 extra_sql => $extra_sql,
1471 my $count = scalar(@items);
1472 foreach my $part_pkg ( @items ) {
1473 if ( time - $min_sec > $last ) {
1474 $job->update_statustext(
1475 int( 100 * $imported / $count ). ",Remembering tax products"
1480 warn "working with package part ". $part_pkg->pkgpart.
1481 "which has a taxproductnum of ". $part_pkg->taxproductnum. "\n" if $DEBUG;
1482 my $part_pkg_taxproduct = $part_pkg->taxproduct('');
1483 $taxproduct->{$part_pkg->pkgpart}->{''} = $part_pkg_taxproduct->taxproduct
1484 if $part_pkg_taxproduct && $part_pkg_taxproduct->data_vendor eq $format;
1486 foreach my $option ( $part_pkg->part_pkg_option ) {
1487 next unless $option->optionname =~ /^usage_taxproductnum_(\w+)$/;
1490 $part_pkg_taxproduct = $part_pkg->taxproduct($class);
1491 $taxproduct->{$part_pkg->pkgpart}->{$class} =
1492 $part_pkg_taxproduct->taxproduct
1493 if $part_pkg_taxproduct && $part_pkg_taxproduct->data_vendor eq $format;
1498 sub _restore_remembered_tax_products {
1499 my ( $job, $format, $taxproduct ) = @_;
1503 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1504 my $count = scalar(keys %$taxproduct);
1505 foreach my $pkgpart ( keys %$taxproduct ) {
1506 warn "restoring taxproductnums on pkgpart $pkgpart\n" if $DEBUG;
1507 if ( time - $min_sec > $last ) {
1508 $job->update_statustext(
1509 int( 100 * $imported / $count ). ",Restoring tax products"
1515 my $part_pkg = qsearchs('part_pkg', { pkgpart => $pkgpart } );
1516 unless ( $part_pkg ) {
1517 return "somehow failed to find part_pkg with pkgpart $pkgpart!\n";
1520 my %options = $part_pkg->options;
1521 my %pkg_svc = map { $_->svcpart => $_->quantity } $part_pkg->pkg_svc;
1522 my $primary_svc = $part_pkg->svcpart;
1523 my $new = new FS::part_pkg { $part_pkg->hash };
1525 foreach my $class ( keys %{ $taxproduct->{$pkgpart} } ) {
1526 warn "working with class '$class'\n" if $DEBUG;
1527 my $part_pkg_taxproduct =
1528 qsearchs( 'part_pkg_taxproduct',
1529 { taxproduct => $taxproduct->{$pkgpart}->{$class},
1530 data_vendor => $format,
1534 unless ( $part_pkg_taxproduct ) {
1535 return "failed to find part_pkg_taxproduct (".
1536 $taxproduct->{$pkgpart}->{$class}. ") for pkgpart $pkgpart\n";
1539 if ( $class eq '' ) {
1540 $new->taxproductnum($part_pkg_taxproduct->taxproductnum);
1544 $options{"usage_taxproductnum_$class"} =
1545 $part_pkg_taxproduct->taxproductnum;
1549 my $error = $new->replace( $part_pkg,
1550 'pkg_svc' => \%pkg_svc,
1551 'primary_svc' => $primary_svc,
1552 'options' => \%options,
1555 return $error if $error;
1562 sub _restore_remembered_disabled_taxes {
1563 my ( $job, $format, $disabled_tax_rate ) = @_;
1565 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1566 my $count = scalar(keys %$disabled_tax_rate);
1567 foreach my $key (keys %$disabled_tax_rate) {
1568 if ( time - $min_sec > $last ) {
1569 $job->update_statustext(
1570 int( 100 * $imported / $count ). ",Disabling tax rates"
1575 my ($geocode,$taxclass) = split /:/, $key, 2;
1576 my @tax_class = qsearch( 'tax_class', { data_vendor => $format,
1577 taxclass => $taxclass,
1579 return "found multiple tax_class records for format $format class $taxclass"
1580 if scalar(@tax_class) > 1;
1582 unless (scalar(@tax_class)) {
1583 warn "no tax_class for format $format class $taxclass\n";
1588 qsearch('tax_rate', { data_vendor => $format,
1589 geocode => $geocode,
1590 taxclassnum => $tax_class[0]->taxclassnum,
1594 if (scalar(@tax_rate) > 1) {
1595 return "found multiple tax_rate records for format $format geocode ".
1596 "$geocode and taxclass $taxclass ( taxclassnum ".
1597 $tax_class[0]->taxclassnum. " )";
1600 if (scalar(@tax_rate)) {
1601 $tax_rate[0]->disabled('Y');
1602 my $error = $tax_rate[0]->replace;
1603 return $error if $error;
1608 sub _remove_old_tax_data {
1609 my ( $job, $format ) = @_;
1612 my $error = $job->update_statustext( "0,Removing old tax data" );
1613 die $error if $error;
1615 my $sql = "UPDATE public.tax_rate_location SET disabled='Y' ".
1616 "WHERE data_vendor = ". $dbh->quote($format);
1617 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1620 tax_rate part_pkg_taxrate part_pkg_taxproduct tax_class cust_tax_location
1622 foreach my $table ( @table ) {
1623 $sql = "DELETE FROM public.$table WHERE data_vendor = ".
1624 $dbh->quote($format);
1625 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1628 if ( $format eq 'cch' ) {
1629 $sql = "DELETE FROM public.cust_tax_location WHERE data_vendor = ".
1630 $dbh->quote("$format-zip");
1631 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1637 sub _create_temporary_tables {
1638 my ( $job, $format ) = @_;
1641 my $error = $job->update_statustext( "0,Creating temporary tables" );
1642 die $error if $error;
1644 my @table = qw( tax_rate
1651 foreach my $table ( @table ) {
1653 "CREATE TEMPORARY TABLE $table ( LIKE $table INCLUDING DEFAULTS )";
1654 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1660 sub _copy_from_temp {
1661 my ( $job, $format ) = @_;
1664 my $error = $job->update_statustext( "0,Making permanent" );
1665 die $error if $error;
1667 my @table = qw( tax_rate
1674 foreach my $table ( @table ) {
1676 "INSERT INTO public.$table SELECT * from $table";
1677 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1683 =item process_download_and_reload
1685 Download and process a tax update as a queued JSRPC job after wiping the
1686 existing wipable tax data.
1690 sub process_download_and_reload {
1691 _process_reload('process_download_and_update', @_);
1695 =item process_batch_reload
1697 Load and process a tax update from the provided files as a queued JSRPC job
1698 after wiping the existing wipable tax data.
1702 sub process_batch_reload {
1703 _process_reload('_perform_batch_import', @_);
1707 sub _process_reload {
1708 my ( $method, $job ) = ( shift, shift );
1710 my $param = thaw(decode_base64($_[0]));
1711 my $format = $param->{'format'}; #well... this is all cch specific
1713 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1715 if ( $job ) { # progress bar
1716 my $error = $job->update_statustext( 0 );
1717 die $error if $error;
1720 my $oldAutoCommit = $FS::UID::AutoCommit;
1721 local $FS::UID::AutoCommit = 0;
1726 "SELECT count(*) FROM part_pkg_taxoverride JOIN tax_class ".
1727 "USING (taxclassnum) WHERE data_vendor = '$format'";
1728 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
1730 or die "Unexpected error executing statement $sql: ". $sth->errstr;
1731 die "Don't (yet) know how to handle part_pkg_taxoverride records."
1732 if $sth->fetchrow_arrayref->[0];
1734 # really should get a table EXCLUSIVE lock here
1736 #remember disabled taxes
1737 my %disabled_tax_rate = ();
1738 $error ||= _remember_disabled_taxes( $job, $format, \%disabled_tax_rate );
1740 #remember tax products
1741 my %taxproduct = ();
1742 $error ||= _remember_tax_products( $job, $format, \%taxproduct );
1745 $error ||= _create_temporary_tables( $job, $format );
1749 my $args = '$job, @_';
1750 eval "$method($args);";
1754 #restore taxproducts
1755 $error ||= _restore_remembered_tax_products( $job, $format, \%taxproduct );
1759 _restore_remembered_disabled_taxes( $job, $format, \%disabled_tax_rate );
1761 #wipe out the old data
1762 $error ||= _remove_old_tax_data( $job, $format );
1765 $error ||= _copy_from_temp( $job, $format );
1768 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1773 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
1777 =item process_download_and_update
1779 Download and process a tax update as a queued JSRPC job
1783 sub process_download_and_update {
1786 my $param = thaw(decode_base64(shift));
1787 my $format = $param->{'format'}; #well... this is all cch specific
1789 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1791 if ( $job ) { # progress bar
1792 my $error = $job->update_statustext( 0);
1793 die $error if $error;
1796 my $cache_dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/';
1797 my $dir = $cache_dir. 'taxdata';
1799 mkdir $dir or die "can't create $dir: $!\n";
1802 if ($format eq 'cch') {
1804 my @namelist = qw( code detail geocode plus4 txmatrix zip );
1806 my $conf = new FS::Conf;
1807 die "direct download of tax data not enabled\n"
1808 unless $conf->exists('taxdatadirectdownload');
1809 my ( $urls, $username, $secret, $states ) =
1810 $conf->config('taxdatadirectdownload');
1811 die "No tax download URL provided. ".
1812 "Did you set the taxdatadirectdownload configuration value?\n"
1820 # really should get a table EXCLUSIVE lock here
1821 # check if initial import or update
1823 # relying on mkdir "$dir.new" as a mutex
1825 my $sql = "SELECT count(*) from tax_rate WHERE data_vendor='$format'";
1826 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
1827 $sth->execute() or die $sth->errstr;
1828 my $update = $sth->fetchrow_arrayref->[0];
1830 # create cache and/or rotate old tax data
1835 opendir(my $dirh, "$dir.9") or die "failed to open $dir.9: $!\n";
1836 foreach my $file (readdir($dirh)) {
1837 unlink "$dir.9/$file" if (-f "$dir.9/$file");
1843 for (8, 7, 6, 5, 4, 3, 2, 1) {
1844 if ( -e "$dir.$_" ) {
1845 rename "$dir.$_", "$dir.". ($_+1) or die "can't rename $dir.$_: $!\n";
1848 rename "$dir", "$dir.1" or die "can't rename $dir: $!\n";
1852 die "can't find previous tax data\n" if $update;
1856 mkdir "$dir.new" or die "can't create $dir.new: $!\n";
1858 # fetch and unpack the zip files
1860 _cch_fetch_and_unzip( $job, $urls, $secret, "$dir.new" );
1862 # extract csv files from the dbf files
1864 foreach my $name ( @namelist ) {
1865 _cch_extract_csv_from_dbf( $job, $dir, $name );
1868 # generate the diff files
1871 foreach my $name ( @namelist ) {
1872 my $difffile = "$dir.new/$name.txt";
1874 my $error = $job->update_statustext( "0,Comparing to previous $name" );
1875 die $error if $error;
1876 warn "processing $dir.new/$name.txt\n" if $DEBUG;
1877 my $olddir = $update ? "$dir.1" : "";
1878 $difffile = _perform_cch_diff( $name, "$dir.new", $olddir );
1880 $difffile =~ s/^$cache_dir//;
1881 push @list, "${name}file:$difffile";
1884 # perform the import
1885 local $keep_cch_files = 1;
1886 $param->{uploaded_files} = join( ',', @list );
1887 $param->{format} .= '-update' if $update;
1889 _perform_batch_import( $job, encode_base64( nfreeze( $param ) ) );
1891 rename "$dir.new", "$dir"
1892 or die "cch tax update processed, but can't rename $dir.new: $!\n";
1895 die "Unknown format: $format";
1899 =item browse_queries PARAMS
1901 Returns a list consisting of a hashref suited for use as the argument
1902 to qsearch, and sql query string. Each is based on the PARAMS hashref
1903 of keys and values which frequently would be passed as C<scalar($cgi->Vars)>
1904 from a form. This conveniently creates the query hashref and count_query
1905 string required by the browse and search elements. As a side effect,
1906 the PARAMS hashref is untainted and keys with unexpected values are removed.
1910 sub browse_queries {
1914 'table' => 'tax_rate',
1916 'order_by' => 'ORDER BY geocode, taxclassnum',
1921 if ( $params->{data_vendor} =~ /^(\w+)$/ ) {
1922 $extra_sql .= ' WHERE data_vendor = '. dbh->quote($1);
1924 delete $params->{data_vendor};
1927 if ( $params->{geocode} =~ /^(\w+)$/ ) {
1928 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1929 'geocode LIKE '. dbh->quote($1.'%');
1931 delete $params->{geocode};
1934 if ( $params->{taxclassnum} =~ /^(\d+)$/ &&
1935 qsearchs( 'tax_class', {'taxclassnum' => $1} )
1938 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1939 ' taxclassnum = '. dbh->quote($1)
1941 delete $params->{taxclassnun};
1945 if ( $params->{tax_type} =~ /^(\d+)$/ );
1946 delete $params->{tax_type}
1950 if ( $params->{tax_cat} =~ /^(\d+)$/ );
1951 delete $params->{tax_cat}
1954 my @taxclassnum = ();
1955 if ($tax_type || $tax_cat ) {
1956 my $compare = "LIKE '". ( $tax_type || "%" ). ":". ( $tax_cat || "%" ). "'";
1957 $compare = "= '$tax_type:$tax_cat'" if ($tax_type && $tax_cat);
1958 @taxclassnum = map { $_->taxclassnum }
1959 qsearch({ 'table' => 'tax_class',
1961 'extra_sql' => "WHERE taxclass $compare",
1965 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ). '( '.
1966 join(' OR ', map { " taxclassnum = $_ " } @taxclassnum ). ' )'
1967 if ( @taxclassnum );
1969 unless ($params->{'showdisabled'}) {
1970 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1971 "( disabled = '' OR disabled IS NULL )";
1974 $query->{extra_sql} = $extra_sql;
1976 return ($query, "SELECT COUNT(*) FROM tax_rate $extra_sql");
1979 =item queue_liability_report PARAMS
1981 Launches a tax liability report.
1983 PARAMS needs to be a base64-encoded Storable hash containing:
1984 - beginning: the start date, as a I<user-readable string> (not a timestamp).
1985 - end: the end date of the report, likewise.
1986 - agentnum: the agent to limit the report to, if any.
1990 sub queue_liability_report {
1992 my $param = thaw(decode_base64(shift));
1995 $cgi->param('beginning', $param->{beginning});
1996 $cgi->param('ending', $param->{ending});
1997 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
1998 my $agentnum = $param->{agentnum};
1999 if ($agentnum =~ /^(\d+)$/) { $agentnum = $1; } else { $agentnum = ''; };
2000 generate_liability_report(
2001 'beginning' => $beginning,
2002 'ending' => $ending,
2003 'agentnum' => $agentnum,
2004 'p' => $param->{RootURL},
2009 =item generate_liability_report PARAMS
2011 Generates a tax liability report. PARAMS must include:
2013 - beginning, as a timestamp
2014 - ending, as a timestamp
2015 - p: the Freeside root URL, for generating links
2016 - agentnum (optional)
2020 #shit, all sorts of false laxiness w/report_newtax.cgi
2021 sub generate_liability_report {
2024 my ( $count, $last, $min_sec ) = _progressbar_foo();
2026 #let us open the temp file early
2027 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
2028 my $report = new File::Temp( TEMPLATE => 'report.tax.liability.XXXXXXXX',
2030 UNLINK => 0, # not so temp
2031 ) or die "can't open report file: $!\n";
2033 my $conf = new FS::Conf;
2034 my $money_char = $conf->config('money_char') || '$';
2037 JOIN cust_bill USING ( invnum )
2038 LEFT JOIN cust_main USING ( custnum )
2042 "LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum )";
2043 my $join_tax_loc = "LEFT JOIN tax_rate_location USING ( taxratelocationnum )";
2045 my $addl_from = " $join_cust $join_loc $join_tax_loc ";
2047 my $where = "WHERE _date >= $args{beginning} AND _date <= $args{ending} ";
2050 if ( $args{agentnum} =~ /^(\d+)$/ ) {
2051 my $agent = qsearchs('agent', { 'agentnum' => $1 } );
2052 die "agent not found" unless $agent;
2053 $agentname = $agent->agent;
2054 $where .= ' AND cust_main.agentnum = '. $agent->agentnum;
2057 #my @taxparam = ( 'itemdesc', 'tax_rate_location.state', 'tax_rate_location.county', 'tax_rate_location.city', 'cust_bill_pkg_tax_rate_location.locationtaxid' );
2058 my @taxparams = qw( city county state locationtaxid );
2059 my @params = ('itemdesc', @taxparams);
2061 my $select = 'DISTINCT itemdesc,locationtaxid,tax_rate_location.state,tax_rate_location.county,tax_rate_location.city';
2063 #false laziness w/FS::Report::Table::Monthly (sub should probably be moved up
2064 #to FS::Report or FS::Record or who the fuck knows where)
2065 my $scalar_sql = sub {
2066 my( $r, $param, $sql ) = @_;
2067 my $sth = dbh->prepare($sql) or die dbh->errstr;
2068 $sth->execute( map $r->$_(), @$param )
2069 or die "Unexpected error executing statement $sql: ". $sth->errstr;
2070 $sth->fetchrow_arrayref->[0] || 0;
2079 # get all distinct tuples of (tax name, state, county, city, locationtaxid)
2080 # for taxes that have been charged
2081 # (state, county, city are from tax_rate_location, not from customer data)
2082 my @tax_and_location = qsearch({ table => 'cust_bill_pkg',
2084 hashref => { pkgpart => 0 },
2085 addl_from => $addl_from,
2086 extra_sql => $where,
2089 $count = scalar(@tax_and_location);
2090 foreach my $t ( @tax_and_location ) {
2093 if ( time - $min_sec > $last ) {
2094 $args{job}->update_statustext( int( 100 * $calculated / $count ).
2101 #my @params = map { my $f = $_; $f =~ s/.*\.//; $f } @taxparam;
2102 my $label = join('~', map { $t->$_ } @params);
2103 $label = 'Tax'. $label if $label =~ /^~/;
2104 unless ( exists( $taxes{$label} ) ) {
2105 my ($baselabel, @trash) = split /~/, $label;
2107 $taxes{$label}->{'label'} = join(', ', split(/~/, $label) );
2108 $taxes{$label}->{'url_param'} =
2109 join(';', map { "$_=". uri_escape($t->$_) } @params);
2112 # " payby != 'COMP' ". # breaks the entire report under 4.x
2113 # # and unnecessary since COMP accounts don't
2114 # # get taxes calculated in the first place
2115 " ( itemdesc = ? OR ? = '' AND itemdesc IS NULL ) ".
2116 "AND ". FS::tax_rate_location->location_sql( map { $_ => $t->$_ }
2121 "FROM cust_bill_pkg $addl_from $where AND $itemdesc_loc";
2123 my $sql = "SELECT SUM(amount) $taxwhere AND cust_bill_pkg.pkgnum = 0";
2125 my $x = &{$scalar_sql}($t, [ 'itemdesc', 'itemdesc' ], $sql );
2127 $taxes{$label}->{'tax'} += $x;
2130 "JOIN cust_credit_bill_pkg USING (billpkgnum,billpkgtaxratelocationnum)";
2132 "FROM cust_bill_pkg $addl_from $creditfrom $where AND $itemdesc_loc";
2134 $sql = "SELECT SUM(cust_credit_bill_pkg.amount) ".
2135 " $creditwhere AND cust_bill_pkg.pkgnum = 0";
2137 my $y = &{$scalar_sql}($t, [ 'itemdesc', 'itemdesc' ], $sql );
2139 $taxes{$label}->{'credit'} += $y;
2141 unless ( exists( $taxes{$baselabel} ) ) {
2143 $basetaxes{$baselabel}->{'label'} = $baselabel;
2144 $basetaxes{$baselabel}->{'url_param'} = "itemdesc=$baselabel";
2145 $basetaxes{$baselabel}->{'base'} = 1;
2149 $basetaxes{$baselabel}->{'tax'} += $x;
2150 $basetaxes{$baselabel}->{'credit'} += $y;
2154 # calculate customer-exemption for this tax
2155 # calculate package-exemption for this tax
2156 # calculate monthly exemption (texas tax) for this tax
2157 # count up all the cust_tax_exempt_pkg records associated with
2158 # the actual line items.
2165 $args{job}->update_statustext( "0,Sorted" );
2171 foreach my $tax ( sort { $a cmp $b } keys %taxes ) {
2172 my ($base, @trash) = split '~', $tax;
2173 my $basetax = delete( $basetaxes{$base} );
2175 if ( $basetax->{tax} == $taxes{$tax}->{tax} ) {
2176 $taxes{$tax}->{base} = 1;
2178 push @taxes, $basetax;
2181 push @taxes, $taxes{$tax};
2188 'credit' => $credit,
2193 my $dateagentlink = "begin=$args{beginning};end=$args{ending}";
2194 $dateagentlink .= ';agentnum='. $args{agentnum}
2195 if length($agentname);
2196 my $baselink = $args{p}. "search/cust_bill_pkg.cgi?vendortax=1;" .
2198 my $creditlink = $args{p}. "search/cust_credit_bill_pkg.html?$dateagentlink";
2200 print $report <<EOF;
2202 <% include("/elements/header.html", "$agentname Tax Report - ".
2204 ? time2str('%h %o %Y ', $args{beginning} )
2208 ( $args{ending} == 4294967295
2210 : time2str('%h %o %Y', $args{ending} )
2215 <% include('/elements/table-grid.html') %>
2218 <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
2219 <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
2220 <TH CLASS="grid" BGCOLOR="#cccccc">Tax invoiced</TH>
2221 <TH CLASS="grid" BGCOLOR="#cccccc"> </TH>
2222 <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
2223 <TH CLASS="grid" BGCOLOR="#cccccc">Tax credited</TH>
2227 my $bgcolor1 = '#eeeeee';
2228 my $bgcolor2 = '#ffffff';
2231 $count = scalar(@taxes);
2233 foreach my $tax ( @taxes ) {
2236 if ( time - $min_sec > $last ) {
2237 $args{job}->update_statustext( int( 100 * $calculated / $count ).
2244 if ( $bgcolor eq $bgcolor1 ) {
2245 $bgcolor = $bgcolor2;
2247 $bgcolor = $bgcolor1;
2251 if ( $tax->{'label'} ne 'Total' ) {
2252 $link = ';'. $tax->{'url_param'};
2255 print $report <<EOF;
2257 <TD CLASS="grid" BGCOLOR="<% '$bgcolor' %>"><% '$tax->{label}' %></TD>
2258 <% ($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
2259 <TD CLASS="grid" BGCOLOR="<% '$bgcolor' %>" ALIGN="right">
2260 <A HREF="<% '$baselink$link' %>;istax=1"><% '$money_char' %><% sprintf('%.2f', $tax->{'tax'} ) %></A>
2262 <% !($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
2263 <TD CLASS="grid" BGCOLOR="<% '$bgcolor' %>"></TD>
2264 <% ($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
2265 <TD CLASS="grid" BGCOLOR="<% '$bgcolor' %>" ALIGN="right">
2266 <A HREF="<% '$creditlink$link' %>;istax=1;iscredit=rate"><% '$money_char' %><% sprintf('%.2f', $tax->{'credit'} ) %></A>
2268 <% !($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
2273 print $report <<EOF;
2280 my $reportname = $report->filename;
2283 my $dropstring = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/report.';
2284 $reportname =~ s/^$dropstring//;
2286 my $reporturl = "%%%ROOTURL%%%/misc/queued_report?report=$reportname";
2287 die "<a href=$reporturl>view</a>\n";
2297 Mixing automatic and manual editing works poorly at present.
2299 Tax liability calculations take too long and arguably don't belong here.
2300 Tax liability report generation not entirely safe (escaped).
2304 L<FS::Record>, L<FS::cust_location>, L<FS::cust_bill>