2 use base qw( FS::Record );
5 use vars qw( $DEBUG $me
6 %tax_unittypes %tax_maxtypes %tax_basetypes %tax_authorities
7 %tax_passtypes %GetInfoType $keep_cch_files );
10 use DateTime::Format::Strptime;
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 );
34 $me = '[FS::tax_rate]';
39 FS::tax_rate - Object methods for tax_rate objects
45 $record = new FS::tax_rate \%hash;
46 $record = new FS::tax_rate { 'column' => 'value' };
48 $error = $record->insert;
50 $error = $new_record->replace($old_record);
52 $error = $record->delete;
54 $error = $record->check;
58 An FS::tax_rate object represents a tax rate, defined by locale.
59 FS::tax_rate inherits from FS::Record. The following fields are
66 primary key (assigned automatically for new tax rates)
70 a geographic location code provided by a tax data vendor
78 a location code provided by a tax authority
82 a foreign key into FS::tax_class - the type of tax referenced by
87 the time after which the tax applies
95 second bracket percentage
99 the amount to which the tax applies (first bracket)
103 a cap on the amount of tax if a cap exists
107 percentage on out of jurisdiction purchases
111 second bracket percentage on out of jurisdiction purchases
115 one of the values in %tax_unittypes
119 amount of tax per unit
123 second bracket amount of tax per unit
127 the number of units to which the fee applies (first bracket)
131 the most units to which fees apply (first and second brackets)
135 a value from %tax_maxtypes indicating how brackets accumulate (i.e. monthly, per invoice, etc)
139 if defined, printed on invoices instead of "Tax"
143 a value from %tax_authorities
147 a value from %tax_basetypes indicating the tax basis
151 a value from %tax_passtypes indicating how the tax should displayed to the customer
155 'Y', 'N', or blank indicating the tax can be passed to the customer
159 if 'Y', this tax does not apply to setup fees
163 if 'Y', this tax does not apply to recurring fees
167 if 'Y', has been manually edited
177 Creates a new tax rate. To add the tax rate to the database, see L<"insert">.
181 sub table { 'tax_rate'; }
185 Adds this tax rate to the database. If there is an error, returns the error,
186 otherwise returns false.
190 Deletes this tax rate from the database. If there is an error, returns the
191 error, otherwise returns false.
193 =item replace OLD_RECORD
195 Replaces the OLD_RECORD with this one in the database. If there is an error,
196 returns the error, otherwise returns false.
200 Checks all fields to make sure this is a valid tax rate. If there is an error,
201 returns the error, otherwise returns false. Called by the insert and replace
209 foreach (qw( taxbase taxmax )) {
210 $self->$_(0) unless $self->$_;
213 $self->ut_numbern('taxnum')
214 || $self->ut_text('geocode')
215 || $self->ut_textn('data_vendor')
216 || $self->ut_cch_textn('location')
217 || $self->ut_foreign_keyn('taxclassnum', 'tax_class', 'taxclassnum')
218 || $self->ut_snumbern('effective_date')
219 || $self->ut_float('tax')
220 || $self->ut_floatn('excessrate')
221 || $self->ut_money('taxbase')
222 || $self->ut_money('taxmax')
223 || $self->ut_floatn('usetax')
224 || $self->ut_floatn('useexcessrate')
225 || $self->ut_numbern('unittype')
226 || $self->ut_floatn('fee')
227 || $self->ut_floatn('excessfee')
228 || $self->ut_floatn('feemax')
229 || $self->ut_numbern('maxtype')
230 || $self->ut_textn('taxname')
231 || $self->ut_numbern('taxauth')
232 || $self->ut_numbern('basetype')
233 || $self->ut_numbern('passtype')
234 || $self->ut_enum('passflag', [ '', 'Y', 'N' ])
235 || $self->ut_enum('setuptax', [ '', 'Y' ] )
236 || $self->ut_enum('recurtax', [ '', 'Y' ] )
237 || $self->ut_enum('inoutcity', [ '', 'I', 'O' ] )
238 || $self->ut_enum('inoutlocal', [ '', 'I', 'O' ] )
239 || $self->ut_enum('manual', [ '', 'Y' ] )
240 || $self->ut_enum('disabled', [ '', 'Y' ] )
241 || $self->SUPER::check
246 #ut_text / ut_textn w/ ` added cause now that's in the data
249 $self->getfield($field)
250 =~ /^([\wô \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=\[\]\<\>\`]*)$/
251 or return gettext('illegal_or_empty_text'). " $field: ".
252 $self->getfield($field);
253 $self->setfield($field,$1);
258 =item taxclass_description
260 Returns the human understandable value associated with the related
265 sub taxclass_description {
267 my $tax_class = qsearchs('tax_class', {'taxclassnum' => $self->taxclassnum });
268 $tax_class ? $tax_class->description : '';
273 Returns the human understandable value associated with the unittype column
277 %tax_unittypes = ( '0' => 'access line',
284 $tax_unittypes{$self->unittype};
289 Returns the human understandable value associated with the maxtype column.
293 # XXX these are non-functional, and most of them are horrible to implement
294 # in our current model
296 %tax_maxtypes = ( '0' => 'receipts per invoice',
297 '1' => 'receipts per item',
298 '2' => 'total utility charges per utility tax year',
299 '3' => 'total charges per utility tax year',
300 '4' => 'receipts per access line',
301 '7' => 'total utility charges per calendar year',
302 '9' => 'monthly receipts per location',
303 '10' => 'monthly receipts exceeds taxbase and total tax per month does not exceed maxtax', # wtf?
304 '11' => 'receipts/units per access line',
305 '14' => 'units per invoice',
306 '15' => 'units per month',
307 '18' => 'units per account',
312 $tax_maxtypes{$self->maxtype};
317 Returns the human understandable value associated with the basetype column
321 %tax_basetypes = ( '0' => 'sale price',
322 '1' => 'gross receipts',
323 '2' => 'sales taxable telecom revenue',
324 '3' => 'minutes carried',
325 '4' => 'minutes billed',
326 '5' => 'gross operating revenue',
327 '6' => 'access line',
329 '8' => 'gross revenue',
330 '9' => 'portion gross receipts attributable to interstate service',
331 '10' => 'access line',
332 '11' => 'gross profits',
333 '12' => 'tariff rate',
335 '15' => 'prior year gross receipts',
340 $tax_basetypes{$self->basetype};
345 Returns the human understandable value associated with the taxauth column
349 %tax_authorities = ( '0' => 'federal',
354 '5' => 'county administered by state',
355 '6' => 'city administered by state',
356 '7' => 'city administered by county',
357 '8' => 'local administered by state',
358 '9' => 'local administered by county',
363 $tax_authorities{$self->taxauth};
368 Returns the human understandable value associated with the passtype column
372 %tax_passtypes = ( '0' => 'separate tax line',
373 '1' => 'separate surcharge line',
374 '2' => 'surcharge not separated',
375 '3' => 'included in base rate',
380 $tax_passtypes{$self->passtype};
383 =item taxline_cch TAXABLES, CLASSES
385 Takes an arrayref of L<FS::cust_bill_pkg> objects representing taxable line
386 items, and an arrayref of charge classes ('setup', 'recur', '' for
387 unclassified usage, or an L<FS::usage_class> number). Calculates the tax on
388 each item under this tax definition and returns a list of new
389 L<FS::cust_bill_pkg> objects for the taxes charged. Each returned object
390 will have a pseudo-field, "cust_bill_pkg_tax_rate_location", containing a
391 single L<FS::cust_bill_pkg_tax_rate_location> object linking the tax rate
392 back to this tax, and to its originating sale.
394 If the taxable objects are linked to an invoice, this will also calculate
395 per-customer exemptions (cust_exempt and cust_taxname_exempt) and attach them
396 to the line items in the 'cust_tax_exempt_pkg' pseudo-field.
398 For accurate calculation of per-customer or per-location taxes, ALL items
399 appearing on the invoice (and subject to this tax) MUST be passed to this
400 method together, and NO items from any other invoice should be included.
404 # future optimization: it would probably suffice to return only the link
405 # records, and let the consolidation routine build the cust_bill_pkgs
409 # this used to accept a hash of options but none of them did anything
410 # so it's been removed.
412 my $taxables = shift;
413 my $classes = shift || [];
415 my $name = $self->taxname;
416 $name = 'Other surcharges'
417 if ($self->passtype == 2);
420 return unless @$taxables; # nothing to do
421 return if $self->disabled;
422 return if $self->passflag eq 'N'; # tax can't be passed to the customer
423 # but should probably still appear on the liability report--create a
424 # cust_tax_exempt_pkg record for it?
426 # in 4.x, the invoice is _already inserted_ before we try to calculate
427 # tax on it. though it may be a quotation, so be careful.
430 my $cust_bill = $taxables->[0]->cust_bill;
431 $cust_main = $cust_bill->cust_main if $cust_bill;
433 my $taxable_charged = 0;
434 my @cust_bill_pkg = grep { $taxable_charged += $_ unless ref; ref; }
437 my $taxratelocationnum = $self->tax_rate_location->taxratelocationnum;
439 warn "calculating taxes for ". $self->taxnum. " on ".
440 join (",", map { $_->pkgnum } @cust_bill_pkg)
443 my $maxtype = $self->maxtype || 0;
444 if ($maxtype != 0 && $maxtype != 1
445 && $maxtype != 14 && $maxtype != 15
446 && $maxtype != 18 # sigh
448 return $self->_fatal_or_null( 'tax with "'.
449 $self->maxtype_name. '" threshold'
451 } # I don't know why, it's not like there are maxtypes that we DO support
453 # we treat gross revenue as gross receipts and expect the tax data
454 # to DTRT (i.e. tax on tax rules)
455 if ($self->basetype != 0 && $self->basetype != 1 &&
456 $self->basetype != 5 && $self->basetype != 6 &&
457 $self->basetype != 7 && $self->basetype != 8 &&
458 $self->basetype != 14
461 $self->_fatal_or_null( 'tax with "'. $self->basetype_name. '" basis' );
465 my %seen; # locationnum or pkgnum => 1
467 my $taxable_cents = 0;
468 my $taxable_units = 0;
472 my $cust_bill_pkg = shift @$taxables;
473 my $class = shift @$classes;
474 $class = 'all' if !defined($class);
476 my %usage_map = map { $_ => $cust_bill_pkg->usage($_) }
477 $cust_bill_pkg->usage_classes;
478 my $usage_total = sum( values(%usage_map), 0 );
480 # determine if the item has exemptions that apply to this tax def
481 my @exemptions = grep { $_->taxnum == $self->taxnum }
482 @{ $cust_bill_pkg->cust_tax_exempt_pkg };
484 if ( $self->tax > 0 ) {
486 my $taxable_charged = 0;
487 if ($class eq 'all') {
488 $taxable_charged = $cust_bill_pkg->setup + $cust_bill_pkg->recur;
489 } elsif ($class eq 'setup') {
490 $taxable_charged = $cust_bill_pkg->setup;
491 } elsif ($class eq 'recur') {
492 $taxable_charged = $cust_bill_pkg->recur - $usage_total;
494 $taxable_charged = $usage_map{$class} || 0;
497 foreach my $ex (@exemptions) {
498 # the only cases where the exemption doesn't apply:
499 # if it's a setup exemption and $class is not 'setup' or 'all'
500 # if it's a recur exemption and $class is 'setup'
501 if ( ( $ex->exempt_recur and $class eq 'setup' )
502 or ( $ex->exempt_setup and $class ne 'setup' and $class ne 'all' )
507 $taxable_charged -= $ex->amount;
509 # cust_main_county handles monthly capped exemptions; this doesn't.
511 # $taxable_charged can also be less than zero at this point
512 # (recur exemption + usage class breakdown); treat that as zero.
513 next if $taxable_charged <= 0;
515 # yeah, some false laziness with cust_main_county
516 my $this_tax_cents = int(100 * $taxable_charged * $self->tax);
517 my $tax_location = FS::cust_bill_pkg_tax_rate_location->new({
518 'taxnum' => $self->taxnum,
519 'taxtype' => ref($self),
520 'cents' => $this_tax_cents, # not a real field
521 'locationtaxid' => $self->location, # fundamentally silly
522 'taxable_billpkgnum' => $cust_bill_pkg->billpkgnum,
523 'taxable_cust_bill_pkg' => $cust_bill_pkg,
524 'taxratelocationnum' => $taxratelocationnum,
525 'taxclass' => $class,
527 push @tax_locations, $tax_location;
529 $taxable_cents += 100 * $taxable_charged;
530 $tax_cents += $this_tax_cents;
532 } elsif ( $self->fee > 0 ) {
533 # most CCH taxes are this type, because nearly every county has a 911
537 # since we don't support partial exemptions (except setup/recur),
538 # if there's an exemption that applies to this package and taxrate,
539 # don't charge ANY per-unit fees
542 # don't apply fees to usage classes (maybe if we ever get per-minute
544 next unless $class eq 'setup'
548 if ( $self->unittype == 0 ) {
549 if ( !$seen{$cust_bill_pkg->pkgnum} ) {
551 $units = $cust_bill_pkg->units;
552 $seen{$cust_bill_pkg->pkgnum} = 1;
553 } # else it's been seen, leave it at zero units
555 } elsif ($self->unittype == 1) { # per minute
556 # STILL not supported...fortunately these only exist if you happen
557 # to be in Idaho or Little Rock, Arkansas
559 # though a voip_cdr package could easily report minutes of usage...
560 return $self->_fatal_or_null( 'fee with minute unit type' );
562 } elsif ( $self->unittype == 2 ) {
565 my $locationnum = $cust_bill_pkg->tax_locationnum;
566 if (!$locationnum and $cust_main) {
567 $locationnum = $cust_main->ship_locationnum;
569 # the other case is that it's a quotation
571 $units = 1 unless $seen{$cust_bill_pkg->tax_locationnum};
572 $seen{$cust_bill_pkg->tax_locationnum} = 1;
575 # Unittype 19 is used for prepaid wireless E911 charges in many states.
576 # Apparently "per retail purchase", which for us would mean per invoice.
577 # Unittype 20 is used for some 911 surcharges and I have no idea what
579 return $self->_fatal_or_null( 'unknown unit type in tax'. $self->taxnum );
581 my $this_tax_cents = int($units * $self->fee * 100);
582 my $tax_location = FS::cust_bill_pkg_tax_rate_location->new({
583 'taxnum' => $self->taxnum,
584 'taxtype' => ref($self),
585 'cents' => $this_tax_cents,
586 'locationtaxid' => $self->location,
587 'taxable_cust_bill_pkg' => $cust_bill_pkg,
588 'taxratelocationnum' => $taxratelocationnum,
590 push @tax_locations, $tax_location;
592 $taxable_units += $units;
593 $tax_cents += $this_tax_cents;
596 } # foreach $cust_bill_pkg
598 # check bracket maxima; throw an error if we've gone over, because
599 # we don't really implement them
601 if ( ($self->taxmax > 0 and $taxable_cents > $self->taxmax*100 ) or
602 ($self->feemax > 0 and $taxable_units > $self->feemax) ) {
604 # (why not just cap taxable_charged/units at the taxmax/feemax? because
605 # it's way more complicated than that. this won't even catch every case
606 # where a bracket maximum should apply.)
607 return $self->_fatal_or_null( 'tax base > taxmax/feemax for tax'.$self->taxnum );
610 # round and distribute
611 my $total_tax_cents = sprintf('%.0f',
612 ($taxable_cents * $self->tax) + ($taxable_units * $self->fee * 100)
614 my $extra_cents = sprintf('%.0f', $total_tax_cents - $tax_cents);
615 $tax_cents += $extra_cents;
617 foreach (@tax_locations) { # can never require more than a single pass, yes?
618 my $cents = $_->get('cents');
619 if ( $extra_cents > 0 ) {
623 $_->set('amount', sprintf('%.2f', $cents/100));
626 # just transform each CBPTRL record into a tax line item.
627 # calculate_taxes will consolidate them, but before that happens we have
628 # to do tax on tax calculation.
630 foreach (@tax_locations) {
631 next if $_->amount == 0;
632 my $tax_item = FS::cust_bill_pkg->new({
635 'setup' => $_->amount,
636 'sdate' => '', # $_->sdate?
639 'cust_bill_pkg_tax_rate_location' => [ $_ ],
641 $_->set('tax_cust_bill_pkg' => $tax_item);
642 push @tax_items, $tax_item;
649 my ($self, $error) = @_;
651 $DB::single = 1; # not a mistake
653 my $conf = new FS::Conf;
655 $error = "can't yet handle ". $error;
656 my $name = $self->taxname;
657 $name = 'Other surcharges'
658 if ($self->passtype == 2);
660 if ($conf->exists('ignore_incalculable_taxes')) {
661 warn "WARNING: $error; billing anyway per ignore_incalculable_taxes conf\n";
662 return { name => $name, amount => 0 };
664 return "fatal: $error";
668 =item tax_on_tax CUST_LOCATION
670 Returns a list of taxes which are candidates for taxing taxes for the
671 given service location (see L<FS::cust_location>)
679 my $cust_location = shift;
681 warn "looking up taxes on tax ". $self->taxnum. " for customer ".
682 $cust_location->custnum
685 my $geocode = $cust_location->geocode($self->data_vendor);
689 my $extra_sql = ' AND ('.
690 join(' OR ', map{ 'geocode = '. $dbh->quote(substr($geocode, 0, $_)) }
695 my $order_by = 'ORDER BY taxclassnum, length(geocode) desc';
696 my $select = 'DISTINCT ON(taxclassnum) *';
698 # should qsearch preface columns with the table to facilitate joins?
699 my @taxclassnums = map { $_->taxclassnum }
700 qsearch( { 'table' => 'part_pkg_taxrate',
702 'hashref' => { 'data_vendor' => $self->data_vendor,
703 'taxclassnumtaxed' => $self->taxclassnum,
705 'extra_sql' => $extra_sql,
706 'order_by' => $order_by,
709 return () unless @taxclassnums;
712 "AND (". join(' OR ', map { "taxclassnum = $_" } @taxclassnums ). ")";
714 qsearch({ 'table' => 'tax_rate',
715 'hashref' => { 'geocode' => $geocode, },
716 'extra_sql' => $extra_sql,
721 =item tax_rate_location
723 Returns an object representing the location associated with this tax
724 (see L<FS::tax_rate_location>)
728 sub tax_rate_location {
731 qsearchs({ 'table' => 'tax_rate_location',
732 'hashref' => { 'data_vendor' => $self->data_vendor,
733 'geocode' => $self->geocode,
737 new FS::tax_rate_location;
744 Finds an existing tax definition matching the data_vendor, taxname,
745 taxclassnum, and geocode of this one, if one exists, and sets the contents of
746 this tax rate equal to that one (including its taxnum). If an existing
747 definition is not found, inserts this one. Returns an error string if
748 inserting a record failed.
754 # this doesn't uniquely identify CCH taxes (kinda goofy, I know)
755 die "find_or_insert is not compatible with CCH taxes\n"
756 if $self->data_vendor eq 'cch';
758 my @keys = (qw(data_vendor taxname taxclassnum geocode));
759 my %hash = map { $_ => $self->get($_) } @keys;
760 my $existing = qsearchs('tax_rate', \%hash);
762 foreach ($self->fields) {
763 $self->set($_, $existing->get($_));
767 return $self->insert;
781 sub _progressbar_foo {
786 my ($param, $job) = @_;
788 my $fh = $param->{filehandle};
789 my $format = $param->{'format'};
797 my @column_lengths = ();
798 my @column_callbacks = ();
799 if ( $format eq 'cch-fixed' || $format eq 'cch-fixed-update' ) {
800 $format =~ s/-fixed//;
801 my $date_format = sub { my $r='';
802 /^(\d{4})(\d{2})(\d{2})$/ && ($r="$3/$2/$1");
805 my $trim = sub { my $r = shift; $r =~ s/^\s*//; $r =~ s/\s*$//; $r };
806 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 );
807 push @column_lengths, 1 if $format eq 'cch-update';
808 push @column_callbacks, $trim foreach (@column_lengths); # 5, 6, 15, 17 esp
809 $column_callbacks[8] = $date_format;
813 my ( $count, $last, $min_sec ) = _progressbar_foo();
814 if ( $job || scalar(@column_callbacks) ) {
816 csv_from_fixed(\$fh, \$count, \@column_lengths, \@column_callbacks);
817 return $error if $error;
821 if ( $format eq 'cch' || $format eq 'cch-update' ) {
822 #false laziness w/below (sub _perform_cch_diff)
823 @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax
824 excessrate effective_date taxauth taxtype taxcat taxname
825 usetax useexcessrate fee unittype feemax maxtype passflag
827 push @fields, 'actionflag' if $format eq 'cch-update';
832 $hash->{'actionflag'} ='I' if ($hash->{'data_vendor'} eq 'cch');
833 $hash->{'data_vendor'} ='cch';
834 my $parser = new DateTime::Format::Strptime( pattern => "%m/%d/%Y",
835 time_zone => 'floating',
837 my $dt = $parser->parse_datetime( $hash->{'effective_date'} );
838 $hash->{'effective_date'} = $dt ? $dt->epoch : '';
840 $hash->{$_} =~ s/\s//g foreach qw( inoutcity inoutlocal ) ;
841 $hash->{$_} = sprintf("%.2f", $hash->{$_}) foreach qw( taxbase taxmax );
844 join(':', map{ $hash->{$_} } qw(taxtype taxcat) );
846 my %tax_class = ( 'data_vendor' => 'cch',
847 'taxclass' => $taxclassid,
850 my $tax_class = qsearchs( 'tax_class', \%tax_class );
851 return "Error updating tax rate: no tax class $taxclassid"
854 $hash->{'taxclassnum'} = $tax_class->taxclassnum;
856 foreach (qw( taxtype taxcat )) {
860 my %passflagmap = ( '0' => '',
864 $hash->{'passflag'} = $passflagmap{$hash->{'passflag'}}
865 if exists $passflagmap{$hash->{'passflag'}};
867 foreach (keys %$hash) {
868 $hash->{$_} = substr($hash->{$_}, 0, 80)
869 if length($hash->{$_}) > 80;
872 my $actionflag = delete($hash->{'actionflag'});
874 $hash->{'taxname'} =~ s/`/'/g;
875 $hash->{'taxname'} =~ s|\\|/|g;
877 return '' if $format eq 'cch'; # but not cch-update
879 if ($actionflag eq 'I') {
880 $insert{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
881 }elsif ($actionflag eq 'D') {
882 $delete{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
884 return "Unexpected action flag: ". $hash->{'actionflag'};
887 delete($hash->{$_}) for keys %$hash;
893 } elsif ( $format eq 'extended' ) {
894 die "unimplemented\n";
898 die "unknown format $format";
901 my $csv = new Text::CSV_XS;
905 local $SIG{HUP} = 'IGNORE';
906 local $SIG{INT} = 'IGNORE';
907 local $SIG{QUIT} = 'IGNORE';
908 local $SIG{TERM} = 'IGNORE';
909 local $SIG{TSTP} = 'IGNORE';
910 local $SIG{PIPE} = 'IGNORE';
912 my $oldAutoCommit = $FS::UID::AutoCommit;
913 local $FS::UID::AutoCommit = 0;
916 while ( defined($line=<$fh>) ) {
917 $csv->parse($line) or do {
918 $dbh->rollback if $oldAutoCommit;
919 return "can't parse: ". $csv->error_input();
922 if ( $job ) { # progress bar
923 if ( time - $min_sec > $last ) {
924 my $error = $job->update_statustext(
925 int( 100 * $imported / $count ). ",Importing tax rates"
928 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
935 my @columns = $csv->fields();
937 my %tax_rate = ( 'data_vendor' => $format );
938 foreach my $field ( @fields ) {
939 $tax_rate{$field} = shift @columns;
942 if ( scalar( @columns ) ) {
943 $dbh->rollback if $oldAutoCommit;
944 return "Unexpected trailing columns in line (wrong format?) importing tax_rate: $line";
947 my $error = &{$hook}(\%tax_rate);
949 $dbh->rollback if $oldAutoCommit;
953 if (scalar(keys %tax_rate)) { #inserts only, not updates for cch
955 my $tax_rate = new FS::tax_rate( \%tax_rate );
956 $error = $tax_rate->insert;
959 $dbh->rollback if $oldAutoCommit;
960 return "can't insert tax_rate for $line: $error";
969 my @replace = grep { exists($delete{$_}) } keys %insert;
971 if ( $job ) { # progress bar
972 if ( time - $min_sec > $last ) {
973 my $error = $job->update_statustext(
974 int( 100 * $imported / $count ). ",Importing tax rates"
977 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
984 my $old = qsearchs( 'tax_rate', $delete{$_} );
988 my $new = new FS::tax_rate({ $old->hash, %{$insert{$_}}, 'manual' => '' });
989 $new->taxnum($old->taxnum);
990 my $error = $new->replace($old);
993 $dbh->rollback if $oldAutoCommit;
994 my $hashref = $insert{$_};
995 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
996 return "can't replace tax_rate for $line: $error";
1003 $old = delete $delete{$_};
1004 warn "WARNING: can't find tax_rate to replace (inserting instead and continuing) for: ".
1005 #join(" ", map { "$_ => ". $old->{$_} } @fields);
1006 join(" ", map { "$_ => ". $old->{$_} } keys(%$old) );
1012 for (grep { !exists($delete{$_}) } keys %insert) {
1013 if ( $job ) { # progress bar
1014 if ( time - $min_sec > $last ) {
1015 my $error = $job->update_statustext(
1016 int( 100 * $imported / $count ). ",Importing tax rates"
1019 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1026 my $tax_rate = new FS::tax_rate( $insert{$_} );
1027 my $error = $tax_rate->insert;
1030 $dbh->rollback if $oldAutoCommit;
1031 my $hashref = $insert{$_};
1032 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
1033 return "can't insert tax_rate for $line: $error";
1039 for (grep { !exists($insert{$_}) } keys %delete) {
1040 if ( $job ) { # progress bar
1041 if ( time - $min_sec > $last ) {
1042 my $error = $job->update_statustext(
1043 int( 100 * $imported / $count ). ",Importing tax rates"
1046 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1053 my $tax_rate = qsearchs( 'tax_rate', $delete{$_} );
1055 $dbh->rollback if $oldAutoCommit;
1056 $tax_rate = $delete{$_};
1057 warn "WARNING: can't find tax_rate to delete for: ".
1058 join(" ", map { "$_ => ". $tax_rate->{$_} } keys(%$tax_rate) ).
1061 my $error = $tax_rate->delete; # XXX we really should not do this
1062 # (it orphans CBPTRL records)
1065 $dbh->rollback if $oldAutoCommit;
1066 my $hashref = $delete{$_};
1067 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
1068 return "can't delete tax_rate for $line: $error";
1075 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
1077 return "Empty file!" unless ($imported || $format eq 'cch-update');
1083 =item process_batch_import
1085 Load a batch import as a queued JSRPC job
1089 sub process_batch_import {
1090 my ($job, $param) = @_;
1092 if ( $param->{reload} ) {
1093 process_batch_reload($job, $param);
1096 _perform_batch_import($job, $param);
1101 sub _perform_batch_import {
1102 my ($job, $param) = @_;
1104 my $oldAutoCommit = $FS::UID::AutoCommit;
1105 local $FS::UID::AutoCommit = 0;
1108 my $format = $param->{'format'};
1110 my $files = $param->{'uploaded_files'}
1111 or die "No files provided.";
1113 my (%files) = map { /^(\w+):((taxdata\/\w+\.\w+\/)?[\.\w]+)$/ ? ($1,$2):() }
1116 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
1119 if ( $format eq 'cch' || $format eq 'cch-fixed'
1120 || $format eq 'cch-update' || $format eq 'cch-fixed-update' )
1123 my @insert_list = ();
1124 my @delete_list = ();
1125 my @predelete_list = ();
1126 my $insertname = '';
1127 my $deletename = '';
1129 my @list = ( 'GEOCODE', \&FS::tax_rate_location::batch_import,
1130 'CODE', \&FS::tax_class::batch_import,
1131 'PLUS4', \&FS::cust_tax_location::batch_import,
1132 'ZIP', \&FS::cust_tax_location::batch_import,
1133 'TXMATRIX', \&FS::part_pkg_taxrate::batch_import,
1134 'DETAIL', \&FS::tax_rate::batch_import,
1136 while( scalar(@list) ) {
1137 my ( $name, $import_sub ) = splice( @list, 0, 2 );
1138 my $file = lc($name). 'file';
1140 unless ($files{$file}) {
1141 #$error = "No $name supplied";
1144 next if $name eq 'DETAIL' && $format =~ /update/;
1146 my $filename = "$dir/". $files{$file};
1148 if ( $format =~ /update/ ) {
1150 ( $error, $insertname, $deletename ) =
1151 _perform_cch_insert_delete_split( $name, $filename, $dir, $format )
1155 unlink $filename or warn "Can't delete $filename: $!"
1156 unless $keep_cch_files;
1157 push @insert_list, $name, $insertname, $import_sub, $format;
1158 if ( $name eq 'GEOCODE' || $name eq 'CODE' ) { #handle this whole ordering issue better
1159 unshift @predelete_list, $name, $deletename, $import_sub, $format;
1161 unshift @delete_list, $name, $deletename, $import_sub, $format;
1166 push @insert_list, $name, $filename, $import_sub, $format;
1173 'DETAIL', "$dir/".$files{detailfile}, \&FS::tax_rate::batch_import, $format
1174 if $format =~ /update/;
1176 my %addl_param = ();
1177 if ( $param->{'delete_only'} ) {
1178 $addl_param{'delete_only'} = $param->{'delete_only'};
1182 $error ||= _perform_cch_tax_import( $job,
1183 [ @predelete_list ],
1190 @list = ( @predelete_list, @insert_list, @delete_list );
1191 while( !$keep_cch_files && scalar(@list) ) {
1192 my ( undef, $file, undef, undef ) = splice( @list, 0, 4 );
1193 unlink $file or warn "Can't delete $file: $!";
1196 } elsif ( $format =~ /^billsoft-(\w+)$/ ) {
1198 my $file = $dir.'/'.$files{'file'};
1199 open my $fh, "< $file" or $error ||= "Can't open file $file: $!";
1203 format => 'billsoft',
1205 if ( $mode eq 'pcode' ) {
1206 $error ||= FS::cust_tax_location::batch_import(@param);
1208 $error ||= FS::tax_rate_location::batch_import(@param);
1209 } elsif ( $mode eq 'taxclass' ) {
1210 $error ||= FS::tax_class::batch_import(@param);
1211 } elsif ( $mode eq 'taxproduct' ) {
1212 $error ||= FS::part_pkg_taxproduct::batch_import(@param);
1214 die "unknown import mode 'billsoft-$mode'\n";
1218 die "Unknown format: $format";
1222 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1225 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
1232 # EVERYTHING THAT FOLLOWS IS CCH-SPECIFIC.
1236 sub _perform_cch_tax_import {
1237 my ( $job, $predelete_list, $insert_list, $delete_list, $addl_param ) = @_;
1241 foreach my $list ($predelete_list, $insert_list, $delete_list) {
1242 while( scalar(@$list) ) {
1243 my ( $name, $file, $method, $format ) = splice( @$list, 0, 4 );
1244 my $fmt = "$format-update";
1245 $fmt = $format. ( lc($name) eq 'zip' ? '-zip' : '' );
1246 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
1247 my $param = { 'filehandle' => $fh,
1251 $error ||= &{$method}($param, $job);
1259 sub _perform_cch_insert_delete_split {
1260 my ($name, $filename, $dir, $format) = @_;
1264 open my $fh, "< $filename"
1265 or $error ||= "Can't open $name file $filename: $!";
1267 my $ifh = new File::Temp( TEMPLATE => "$name.insert.XXXXXXXX",
1270 ) or die "can't open temp file: $!\n";
1271 my $insertname = $ifh->filename;
1273 my $dfh = new File::Temp( TEMPLATE => "$name.delete.XXXXXXXX",
1276 ) or die "can't open temp file: $!\n";
1277 my $deletename = $dfh->filename;
1279 my $insert_pattern = ($format eq 'cch-update') ? qr/"I"\s*$/ : qr/I\s*$/;
1280 my $delete_pattern = ($format eq 'cch-update') ? qr/"D"\s*$/ : qr/D\s*$/;
1283 $handle = $ifh if $_ =~ /$insert_pattern/;
1284 $handle = $dfh if $_ =~ /$delete_pattern/;
1286 $error = "bad input line: $_" unless $handle;
1295 return ($error, $insertname, $deletename);
1298 sub _perform_cch_diff {
1299 my ($name, $newdir, $olddir) = @_;
1304 open my $oldcsvfh, "$olddir/$name.txt"
1305 or die "failed to open $olddir/$name.txt: $!\n";
1307 while(<$oldcsvfh>) {
1314 open my $newcsvfh, "$newdir/$name.txt"
1315 or die "failed to open $newdir/$name.txt: $!\n";
1317 my $dfh = new File::Temp( TEMPLATE => "$name.diff.XXXXXXXX",
1320 ) or die "can't open temp file: $!\n";
1321 my $diffname = $dfh->filename;
1323 while(<$newcsvfh>) {
1325 if (exists($oldlines{$_})) {
1328 print $dfh $_, ',"I"', "\n";
1333 #false laziness w/above (sub batch_import)
1334 my @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax
1335 excessrate effective_date taxauth taxtype taxcat taxname
1336 usetax useexcessrate fee unittype feemax maxtype passflag
1337 passtype basetype );
1338 my $numfields = scalar(@fields);
1340 my $csv = new Text::CSV_XS { 'always_quote' => 1 };
1342 for my $line (grep $oldlines{$_}, keys %oldlines) {
1344 $csv->parse($line) or do {
1345 #$dbh->rollback if $oldAutoCommit;
1346 die "can't parse: ". $csv->error_input();
1348 my @columns = $csv->fields();
1350 $csv->combine( splice(@columns, 0, $numfields) );
1352 print $dfh $csv->string, ',"D"', "\n";
1360 sub _cch_fetch_and_unzip {
1361 my ( $job, $urls, $secret, $dir ) = @_;
1363 my $ua = new LWP::UserAgent;
1364 foreach my $url (split ',', $urls) {
1365 my @name = split '/', $url; #somewhat restrictive
1366 my $name = pop @name;
1367 $name =~ /([\w.]+)/; # untaint that which we don't trust so much any more
1370 open my $taxfh, ">$dir/$name" or die "Can't open $dir/$name: $!\n";
1372 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1373 my $res = $ua->request(
1374 new HTTP::Request( GET => $url ),
1376 print $taxfh $_[0] or die "Can't write to $dir/$name: $!\n";
1377 my $content_length = $_[1]->content_length;
1378 $imported += length($_[0]);
1379 if ( time - $min_sec > $last ) {
1380 my $error = $job->update_statustext(
1381 ($content_length ? int(100 * $imported/$content_length) : 0 ).
1382 ",Downloading data from CCH"
1384 die $error if $error;
1389 die "download of $url failed: ". $res->status_line
1390 unless $res->is_success;
1393 my $error = $job->update_statustext( "0,Unpacking data" );
1394 die $error if $error;
1395 $secret =~ /([\w.]+)/; # untaint that which we don't trust so much any more
1397 system('unzip', "-P", $secret, "-d", "$dir", "$dir/$name") == 0
1398 or die "unzip -P $secret -d $dir $dir/$name failed";
1399 #unlink "$dir/$name";
1403 sub _cch_extract_csv_from_dbf {
1404 my ( $job, $dir, $name ) = @_;
1409 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1410 my $error = $job->update_statustext( "0,Unpacking $name" );
1411 die $error if $error;
1412 warn "opening $dir.new/$name.dbf\n" if $DEBUG;
1413 my $table = new XBase 'name' => "$dir.new/$name.dbf";
1414 die "failed to access $dir.new/$name.dbf: ". XBase->errstr
1415 unless defined($table);
1416 my $count = $table->last_record; # approximately;
1417 open my $csvfh, ">$dir.new/$name.txt"
1418 or die "failed to open $dir.new/$name.txt: $!\n";
1420 my $csv = new Text::CSV_XS { 'always_quote' => 1 };
1421 my @fields = $table->field_names;
1422 my $cursor = $table->prepare_select;
1424 sub { my $date = shift;
1425 $date =~ /^(\d{4})(\d{2})(\d{2})$/ && ($date = "$2/$3/$1");
1428 while (my $row = $cursor->fetch_hashref) {
1429 $csv->combine( map { my $type = $table->field_type($_);
1431 &{$format_date}($row->{$_}) ;
1432 } elsif ($type eq 'N' && $row->{$_} =~ /e-/i ) {
1433 sprintf('%.8f', $row->{$_}); #db row is numeric(14,8)
1440 print $csvfh $csv->string, "\n";
1442 if ( time - $min_sec > $last ) {
1443 my $error = $job->update_statustext(
1444 int(100 * $imported/$count). ",Unpacking $name"
1446 die $error if $error;
1454 sub _remember_disabled_taxes {
1455 my ( $job, $format, $disabled_tax_rate ) = @_;
1459 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1461 my @items = qsearch( { table => 'tax_rate',
1462 hashref => { disabled => 'Y',
1463 data_vendor => $format,
1465 select => 'geocode, taxclassnum',
1468 my $count = scalar(@items);
1469 foreach my $tax_rate ( @items ) {
1470 if ( time - $min_sec > $last ) {
1471 $job->update_statustext(
1472 int( 100 * $imported / $count ). ",Remembering disabled taxes"
1478 qsearchs( 'tax_class', { taxclassnum => $tax_rate->taxclassnum } );
1479 unless ( $tax_class ) {
1480 warn "failed to find tax_class ". $tax_rate->taxclassnum;
1483 $disabled_tax_rate->{$tax_rate->geocode. ':'. $tax_class->taxclass} = 1;
1487 sub _remember_tax_products {
1488 my ( $job, $format, $taxproduct ) = @_;
1490 # XXX FIXME this loop only works when cch is the only data provider
1492 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1495 WHERE taxproductnum IS NOT NULL
1496 OR EXISTS ( SELECT 1 from part_pkg_option
1497 WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
1498 AND optionname LIKE 'usage_taxproductnum_%'
1499 AND optionvalue != ''
1502 my @items = qsearch( { table => 'part_pkg',
1503 select => 'DISTINCT pkgpart,taxproductnum',
1505 extra_sql => $extra_sql,
1508 my $count = scalar(@items);
1509 foreach my $part_pkg ( @items ) {
1510 if ( time - $min_sec > $last ) {
1511 $job->update_statustext(
1512 int( 100 * $imported / $count ). ",Remembering tax products"
1517 warn "working with package part ". $part_pkg->pkgpart.
1518 "which has a taxproductnum of ". $part_pkg->taxproductnum. "\n" if $DEBUG;
1519 my $part_pkg_taxproduct = $part_pkg->taxproduct('');
1520 $taxproduct->{$part_pkg->pkgpart}->{''} = $part_pkg_taxproduct->taxproduct
1521 if $part_pkg_taxproduct && $part_pkg_taxproduct->data_vendor eq $format;
1523 foreach my $option ( $part_pkg->part_pkg_option ) {
1524 next unless $option->optionname =~ /^usage_taxproductnum_(\w+)$/;
1527 $part_pkg_taxproduct = $part_pkg->taxproduct($class);
1528 $taxproduct->{$part_pkg->pkgpart}->{$class} =
1529 $part_pkg_taxproduct->taxproduct
1530 if $part_pkg_taxproduct && $part_pkg_taxproduct->data_vendor eq $format;
1535 sub _restore_remembered_tax_products {
1536 my ( $job, $format, $taxproduct ) = @_;
1540 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1541 my $count = scalar(keys %$taxproduct);
1542 foreach my $pkgpart ( keys %$taxproduct ) {
1543 warn "restoring taxproductnums on pkgpart $pkgpart\n" if $DEBUG;
1544 if ( time - $min_sec > $last ) {
1545 $job->update_statustext(
1546 int( 100 * $imported / $count ). ",Restoring tax products"
1552 my $part_pkg = qsearchs('part_pkg', { pkgpart => $pkgpart } );
1553 unless ( $part_pkg ) {
1554 return "somehow failed to find part_pkg with pkgpart $pkgpart!\n";
1557 my %options = $part_pkg->options;
1558 my %pkg_svc = map { $_->svcpart => $_->quantity } $part_pkg->pkg_svc;
1559 my $primary_svc = $part_pkg->svcpart;
1560 my $new = new FS::part_pkg { $part_pkg->hash };
1562 foreach my $class ( keys %{ $taxproduct->{$pkgpart} } ) {
1563 warn "working with class '$class'\n" if $DEBUG;
1564 my $part_pkg_taxproduct =
1565 qsearchs( 'part_pkg_taxproduct',
1566 { taxproduct => $taxproduct->{$pkgpart}->{$class},
1567 data_vendor => $format,
1571 unless ( $part_pkg_taxproduct ) {
1572 return "failed to find part_pkg_taxproduct (".
1573 $taxproduct->{$pkgpart}->{$class}. ") for pkgpart $pkgpart\n";
1576 if ( $class eq '' ) {
1577 $new->taxproductnum($part_pkg_taxproduct->taxproductnum);
1581 $options{"usage_taxproductnum_$class"} =
1582 $part_pkg_taxproduct->taxproductnum;
1586 my $error = $new->replace( $part_pkg,
1587 'pkg_svc' => \%pkg_svc,
1588 'primary_svc' => $primary_svc,
1589 'options' => \%options,
1592 return $error if $error;
1599 sub _restore_remembered_disabled_taxes {
1600 my ( $job, $format, $disabled_tax_rate ) = @_;
1602 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1603 my $count = scalar(keys %$disabled_tax_rate);
1604 foreach my $key (keys %$disabled_tax_rate) {
1605 if ( time - $min_sec > $last ) {
1606 $job->update_statustext(
1607 int( 100 * $imported / $count ). ",Disabling tax rates"
1612 my ($geocode,$taxclass) = split /:/, $key, 2;
1613 my @tax_class = qsearch( 'tax_class', { data_vendor => $format,
1614 taxclass => $taxclass,
1616 return "found multiple tax_class records for format $format class $taxclass"
1617 if scalar(@tax_class) > 1;
1619 unless (scalar(@tax_class)) {
1620 warn "no tax_class for format $format class $taxclass\n";
1625 qsearch('tax_rate', { data_vendor => $format,
1626 geocode => $geocode,
1627 taxclassnum => $tax_class[0]->taxclassnum,
1631 if (scalar(@tax_rate) > 1) {
1632 return "found multiple tax_rate records for format $format geocode ".
1633 "$geocode and taxclass $taxclass ( taxclassnum ".
1634 $tax_class[0]->taxclassnum. " )";
1637 if (scalar(@tax_rate)) {
1638 $tax_rate[0]->disabled('Y');
1639 my $error = $tax_rate[0]->replace;
1640 return $error if $error;
1645 sub _remove_old_tax_data {
1646 my ( $job, $format ) = @_;
1649 my $error = $job->update_statustext( "0,Removing old tax data" );
1650 die $error if $error;
1652 my $sql = "UPDATE public.tax_rate_location SET disabled='Y' ".
1653 "WHERE data_vendor = ". $dbh->quote($format);
1654 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1657 tax_rate part_pkg_taxrate part_pkg_taxproduct tax_class cust_tax_location
1659 foreach my $table ( @table ) {
1660 $sql = "DELETE FROM public.$table WHERE data_vendor = ".
1661 $dbh->quote($format);
1662 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1665 if ( $format eq 'cch' ) {
1666 $sql = "DELETE FROM public.cust_tax_location WHERE data_vendor = ".
1667 $dbh->quote("$format-zip");
1668 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1674 sub _create_temporary_tables {
1675 my ( $job, $format ) = @_;
1678 my $error = $job->update_statustext( "0,Creating temporary tables" );
1679 die $error if $error;
1681 my @table = qw( tax_rate
1688 foreach my $table ( @table ) {
1690 "CREATE TEMPORARY TABLE $table ( LIKE $table INCLUDING DEFAULTS )";
1691 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1697 sub _copy_from_temp {
1698 my ( $job, $format ) = @_;
1701 my $error = $job->update_statustext( "0,Making permanent" );
1702 die $error if $error;
1704 my @table = qw( tax_rate
1711 foreach my $table ( @table ) {
1713 "INSERT INTO public.$table SELECT * from $table";
1714 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1720 =item process_download_and_reload
1722 Download and process a tax update as a queued JSRPC job after wiping the
1723 existing wipeable tax data.
1727 sub process_download_and_reload {
1728 _process_reload(\&process_download_and_update, @_);
1737 =item process_batch_reload
1739 Load and process a tax update from the provided files as a queued JSRPC job
1740 after wiping the existing wipable tax data.
1744 sub process_batch_reload {
1745 _process_reload(\&_perform_batch_import, @_);
1748 sub _process_reload {
1749 my ( $continuation, $job, $param ) = @_;
1750 my $format = $param->{'format'};
1752 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1754 if ( $job ) { # progress bar
1755 my $error = $job->update_statustext( 0 );
1756 die $error if $error;
1759 my $oldAutoCommit = $FS::UID::AutoCommit;
1760 local $FS::UID::AutoCommit = 0;
1764 if ( $format =~ /^cch/ ) {
1765 # no, THIS part is CCH specific
1768 "SELECT count(*) FROM part_pkg_taxoverride JOIN tax_class ".
1769 "USING (taxclassnum) WHERE data_vendor = '$format'";
1770 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
1772 or die "Unexpected error executing statement $sql: ". $sth->errstr;
1773 die "Don't (yet) know how to handle part_pkg_taxoverride records."
1774 if $sth->fetchrow_arrayref->[0];
1776 # really should get a table EXCLUSIVE lock here
1778 #remember disabled taxes
1779 my %disabled_tax_rate = ();
1780 $error ||= _remember_disabled_taxes( $job, $format, \%disabled_tax_rate );
1782 #remember tax products
1783 my %taxproduct = ();
1784 $error ||= _remember_tax_products( $job, $format, \%taxproduct );
1787 $error ||= _create_temporary_tables( $job, $format );
1791 eval { &{$continuation}( $job, $param ) };
1795 #restore taxproducts
1796 $error ||= _restore_remembered_tax_products( $job, $format, \%taxproduct );
1800 _restore_remembered_disabled_taxes( $job, $format, \%disabled_tax_rate );
1802 #wipe out the old data
1803 $error ||= _remove_old_tax_data( $job, $format );
1806 $error ||= _copy_from_temp( $job, $format );
1808 } elsif ( $format =~ /^billsoft-(\w+)/ ) {
1812 if ( $mode eq 'pcode' ) {
1814 "DELETE FROM cust_tax_location WHERE data_vendor = 'billsoft'",
1815 "UPDATE tax_rate_location SET disabled = 'Y' WHERE data_vendor = 'billsoft'";
1816 } elsif ( $mode eq 'taxclass' ) {
1818 "DELETE FROM tax_class WHERE data_vendor = 'billsoft'";
1819 } elsif ( $mode eq 'taxproduct' ) {
1821 "DELETE FROM part_pkg_taxproduct WHERE data_vendor = 'billsoft'";
1825 if (!$dbh->do($_)) {
1826 $error = $dbh->errstr;
1833 eval { &{ $continuation }($job, $param) };
1836 } # if ($format ...)
1839 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1844 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
1848 =item process_download_and_update
1850 Download and process a tax update as a queued JSRPC job
1854 sub process_download_and_update {
1858 my $format = $param->{'format'}; #well... this is all cch specific
1860 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1862 if ( $job ) { # progress bar
1863 my $error = $job->update_statustext( 0);
1864 die $error if $error;
1867 my $cache_dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/';
1868 my $dir = $cache_dir. 'taxdata';
1870 mkdir $dir or die "can't create $dir: $!\n";
1873 if ($format eq 'cch') {
1875 my @namelist = qw( code detail geocode plus4 txmatrix zip );
1877 my $conf = new FS::Conf;
1878 die "direct download of tax data not enabled\n"
1879 unless $conf->exists('taxdatadirectdownload');
1880 my ( $urls, $username, $secret, $states ) =
1881 $conf->config('taxdatadirectdownload');
1882 die "No tax download URL provided. ".
1883 "Did you set the taxdatadirectdownload configuration value?\n"
1891 # really should get a table EXCLUSIVE lock here
1892 # check if initial import or update
1894 # relying on mkdir "$dir.new" as a mutex
1896 my $sql = "SELECT count(*) from tax_rate WHERE data_vendor='$format'";
1897 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
1898 $sth->execute() or die $sth->errstr;
1899 my $update = $sth->fetchrow_arrayref->[0];
1901 # create cache and/or rotate old tax data
1906 opendir(my $dirh, "$dir.9") or die "failed to open $dir.9: $!\n";
1907 foreach my $file (readdir($dirh)) {
1908 unlink "$dir.9/$file" if (-f "$dir.9/$file");
1914 for (8, 7, 6, 5, 4, 3, 2, 1) {
1915 if ( -e "$dir.$_" ) {
1916 rename "$dir.$_", "$dir.". ($_+1) or die "can't rename $dir.$_: $!\n";
1919 rename "$dir", "$dir.1" or die "can't rename $dir: $!\n";
1923 die "can't find previous tax data\n" if $update;
1927 mkdir "$dir.new" or die "can't create $dir.new: $!\n";
1929 # fetch and unpack the zip files
1931 _cch_fetch_and_unzip( $job, $urls, $secret, "$dir.new" );
1933 # extract csv files from the dbf files
1935 foreach my $name ( @namelist ) {
1936 _cch_extract_csv_from_dbf( $job, $dir, $name );
1939 # generate the diff files
1942 foreach my $name ( @namelist ) {
1943 my $difffile = "$dir.new/$name.txt";
1945 my $error = $job->update_statustext( "0,Comparing to previous $name" );
1946 die $error if $error;
1947 warn "processing $dir.new/$name.txt\n" if $DEBUG;
1948 my $olddir = $update ? "$dir.1" : "";
1949 $difffile = _perform_cch_diff( $name, "$dir.new", $olddir );
1951 $difffile =~ s/^$cache_dir//;
1952 push @list, "${name}file:$difffile";
1955 # perform the import
1956 local $keep_cch_files = 1;
1957 $param->{uploaded_files} = join( ',', @list );
1958 $param->{format} .= '-update' if $update;
1960 _perform_batch_import( $job, $param );
1962 rename "$dir.new", "$dir"
1963 or die "cch tax update processed, but can't rename $dir.new: $!\n";
1966 die "Unknown format: $format";
1970 =item browse_queries PARAMS
1972 Returns a list consisting of a hashref suited for use as the argument
1973 to qsearch, and sql query string. Each is based on the PARAMS hashref
1974 of keys and values which frequently would be passed as C<scalar($cgi->Vars)>
1975 from a form. This conveniently creates the query hashref and count_query
1976 string required by the browse and search elements. As a side effect,
1977 the PARAMS hashref is untainted and keys with unexpected values are removed.
1981 sub browse_queries {
1985 'table' => 'tax_rate',
1987 'order_by' => 'ORDER BY geocode, taxclassnum',
1992 if ( $params->{data_vendor} =~ /^(\w+)$/ ) {
1993 $extra_sql .= ' WHERE data_vendor = '. dbh->quote($1);
1995 delete $params->{data_vendor};
1998 if ( $params->{geocode} =~ /^(\w+)$/ ) {
1999 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
2000 'geocode LIKE '. dbh->quote($1.'%');
2002 delete $params->{geocode};
2005 if ( $params->{taxclassnum} =~ /^(\d+)$/ &&
2006 qsearchs( 'tax_class', {'taxclassnum' => $1} )
2009 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
2010 ' taxclassnum = '. dbh->quote($1)
2012 delete $params->{taxclassnun};
2016 if ( $params->{tax_type} =~ /^(\d+)$/ );
2017 delete $params->{tax_type}
2021 if ( $params->{tax_cat} =~ /^(\d+)$/ );
2022 delete $params->{tax_cat}
2025 my @taxclassnum = ();
2026 if ($tax_type || $tax_cat ) {
2027 my $compare = "LIKE '". ( $tax_type || "%" ). ":". ( $tax_cat || "%" ). "'";
2028 $compare = "= '$tax_type:$tax_cat'" if ($tax_type && $tax_cat);
2029 @taxclassnum = map { $_->taxclassnum }
2030 qsearch({ 'table' => 'tax_class',
2032 'extra_sql' => "WHERE taxclass $compare",
2036 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ). '( '.
2037 join(' OR ', map { " taxclassnum = $_ " } @taxclassnum ). ' )'
2038 if ( @taxclassnum );
2040 unless ($params->{'showdisabled'}) {
2041 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
2042 "( disabled = '' OR disabled IS NULL )";
2045 $query->{extra_sql} = $extra_sql;
2047 return ($query, "SELECT COUNT(*) FROM tax_rate $extra_sql");
2050 =item queue_liability_report PARAMS
2052 Launches a tax liability report.
2054 PARAMS needs to be a base64-encoded Storable hash containing:
2055 - beginning: the start date, as a I<user-readable string> (not a timestamp).
2056 - end: the end date of the report, likewise.
2057 - agentnum: the agent to limit the report to, if any.
2061 sub queue_liability_report {
2066 $cgi->param('beginning', $param->{beginning});
2067 $cgi->param('ending', $param->{ending});
2068 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
2069 my $agentnum = $param->{agentnum};
2070 if ($agentnum =~ /^(\d+)$/) { $agentnum = $1; } else { $agentnum = ''; };
2071 generate_liability_report(
2072 'beginning' => $beginning,
2073 'ending' => $ending,
2074 'agentnum' => $agentnum,
2075 'p' => $param->{RootURL},
2080 =item generate_liability_report PARAMS
2082 Generates a tax liability report. PARAMS must include:
2084 - beginning, as a timestamp
2085 - ending, as a timestamp
2086 - p: the Freeside root URL, for generating links
2087 - agentnum (optional)
2091 #shit, all sorts of false laxiness w/report_newtax.cgi
2092 sub generate_liability_report {
2095 my ( $count, $last, $min_sec ) = _progressbar_foo();
2097 #let us open the temp file early
2098 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
2099 my $report = new File::Temp( TEMPLATE => 'report.tax.liability.XXXXXXXX',
2101 UNLINK => 0, # not so temp
2102 ) or die "can't open report file: $!\n";
2104 my $conf = new FS::Conf;
2105 my $money_char = $conf->config('money_char') || '$';
2108 JOIN cust_bill USING ( invnum )
2109 LEFT JOIN cust_main USING ( custnum )
2113 "LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum )";
2114 my $join_tax_loc = "LEFT JOIN tax_rate_location USING ( taxratelocationnum )";
2116 my $addl_from = " $join_cust $join_loc $join_tax_loc ";
2118 my $where = "WHERE _date >= $args{beginning} AND _date <= $args{ending} ";
2121 if ( $args{agentnum} =~ /^(\d+)$/ ) {
2122 my $agent = qsearchs('agent', { 'agentnum' => $1 } );
2123 die "agent not found" unless $agent;
2124 $agentname = $agent->agent;
2125 $where .= ' AND cust_main.agentnum = '. $agent->agentnum;
2128 #my @taxparam = ( 'itemdesc', 'tax_rate_location.state', 'tax_rate_location.county', 'tax_rate_location.city', 'cust_bill_pkg_tax_rate_location.locationtaxid' );
2129 my @taxparams = qw( city county state locationtaxid );
2130 my @params = ('itemdesc', @taxparams);
2132 my $select = 'DISTINCT itemdesc,locationtaxid,tax_rate_location.state,tax_rate_location.county,tax_rate_location.city';
2134 #false laziness w/FS::Report::Table::Monthly (sub should probably be moved up
2135 #to FS::Report or FS::Record or who the fuck knows where)
2136 my $scalar_sql = sub {
2137 my( $r, $param, $sql ) = @_;
2138 my $sth = dbh->prepare($sql) or die dbh->errstr;
2139 $sth->execute( map $r->$_(), @$param )
2140 or die "Unexpected error executing statement $sql: ". $sth->errstr;
2141 $sth->fetchrow_arrayref->[0] || 0;
2150 # get all distinct tuples of (tax name, state, county, city, locationtaxid)
2151 # for taxes that have been charged
2152 # (state, county, city are from tax_rate_location, not from customer data)
2153 my @tax_and_location = qsearch({ table => 'cust_bill_pkg',
2155 hashref => { pkgpart => 0 },
2156 addl_from => $addl_from,
2157 extra_sql => $where,
2160 $count = scalar(@tax_and_location);
2161 foreach my $t ( @tax_and_location ) {
2164 if ( time - $min_sec > $last ) {
2165 $args{job}->update_statustext( int( 100 * $calculated / $count ).
2172 #my @params = map { my $f = $_; $f =~ s/.*\.//; $f } @taxparam;
2173 my $label = join('~', map { $t->$_ } @params);
2174 $label = 'Tax'. $label if $label =~ /^~/;
2175 unless ( exists( $taxes{$label} ) ) {
2176 my ($baselabel, @trash) = split /~/, $label;
2178 $taxes{$label}->{'label'} = join(', ', split(/~/, $label) );
2179 $taxes{$label}->{'url_param'} =
2180 join(';', map { "$_=". uri_escape($t->$_) } @params);
2183 " ( itemdesc = ? OR ? = '' AND itemdesc IS NULL ) ".
2184 "AND ". FS::tax_rate_location->location_sql( map { $_ => $t->$_ }
2189 "FROM cust_bill_pkg $addl_from $where AND $itemdesc_loc";
2191 my $sql = "SELECT SUM(amount) $taxwhere AND cust_bill_pkg.pkgnum = 0";
2193 my $x = &{$scalar_sql}($t, [ 'itemdesc', 'itemdesc' ], $sql );
2195 $taxes{$label}->{'tax'} += $x;
2198 "JOIN cust_credit_bill_pkg USING (billpkgnum,billpkgtaxratelocationnum)";
2200 "FROM cust_bill_pkg $addl_from $creditfrom $where AND $itemdesc_loc";
2202 $sql = "SELECT SUM(cust_credit_bill_pkg.amount) ".
2203 " $creditwhere AND cust_bill_pkg.pkgnum = 0";
2205 my $y = &{$scalar_sql}($t, [ 'itemdesc', 'itemdesc' ], $sql );
2207 $taxes{$label}->{'credit'} += $y;
2209 unless ( exists( $taxes{$baselabel} ) ) {
2211 $basetaxes{$baselabel}->{'label'} = $baselabel;
2212 $basetaxes{$baselabel}->{'url_param'} = "itemdesc=$baselabel";
2213 $basetaxes{$baselabel}->{'base'} = 1;
2217 $basetaxes{$baselabel}->{'tax'} += $x;
2218 $basetaxes{$baselabel}->{'credit'} += $y;
2222 # calculate customer-exemption for this tax
2223 # calculate package-exemption for this tax
2224 # calculate monthly exemption (texas tax) for this tax
2225 # count up all the cust_tax_exempt_pkg records associated with
2226 # the actual line items.
2233 $args{job}->update_statustext( "0,Sorted" );
2239 foreach my $tax ( sort { $a cmp $b } keys %taxes ) {
2240 my ($base, @trash) = split '~', $tax;
2241 my $basetax = delete( $basetaxes{$base} );
2243 if ( $basetax->{tax} == $taxes{$tax}->{tax} ) {
2244 $taxes{$tax}->{base} = 1;
2246 push @taxes, $basetax;
2249 push @taxes, $taxes{$tax};
2256 'credit' => $credit,
2261 my $dateagentlink = "begin=$args{beginning};end=$args{ending}";
2262 $dateagentlink .= ';agentnum='. $args{agentnum}
2263 if length($agentname);
2264 my $baselink = $args{p}. "search/cust_bill_pkg.cgi?vendortax=1;" .
2266 my $creditlink = $args{p}. "search/cust_credit_bill_pkg.html?$dateagentlink";
2268 print $report <<EOF;
2270 <% include("/elements/header.html", "$agentname Tax Report - ".
2272 ? time2str('%h %o %Y ', $args{beginning} )
2276 ( $args{ending} == 4294967295
2278 : time2str('%h %o %Y', $args{ending} )
2283 <% include('/elements/table-grid.html') %>
2286 <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
2287 <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
2288 <TH CLASS="grid" BGCOLOR="#cccccc">Tax invoiced</TH>
2289 <TH CLASS="grid" BGCOLOR="#cccccc"> </TH>
2290 <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
2291 <TH CLASS="grid" BGCOLOR="#cccccc">Tax credited</TH>
2295 my $bgcolor1 = '#eeeeee';
2296 my $bgcolor2 = '#ffffff';
2299 $count = scalar(@taxes);
2301 foreach my $tax ( @taxes ) {
2304 if ( time - $min_sec > $last ) {
2305 $args{job}->update_statustext( int( 100 * $calculated / $count ).
2312 if ( $bgcolor eq $bgcolor1 ) {
2313 $bgcolor = $bgcolor2;
2315 $bgcolor = $bgcolor1;
2319 if ( $tax->{'label'} ne 'Total' ) {
2320 $link = ';'. $tax->{'url_param'};
2323 print $report <<EOF;
2325 <TD CLASS="grid" BGCOLOR="<% '$bgcolor' %>"><% '$tax->{label}' %></TD>
2326 <% ($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
2327 <TD CLASS="grid" BGCOLOR="<% '$bgcolor' %>" ALIGN="right">
2328 <A HREF="<% '$baselink$link' %>;istax=1"><% '$money_char' %><% sprintf('%.2f', $tax->{'tax'} ) %></A>
2330 <% !($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
2331 <TD CLASS="grid" BGCOLOR="<% '$bgcolor' %>"></TD>
2332 <% ($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
2333 <TD CLASS="grid" BGCOLOR="<% '$bgcolor' %>" ALIGN="right">
2334 <A HREF="<% '$creditlink$link' %>;istax=1;iscredit=rate"><% '$money_char' %><% sprintf('%.2f', $tax->{'credit'} ) %></A>
2336 <% !($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
2341 print $report <<EOF;
2348 my $reportname = $report->filename;
2351 my $dropstring = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/report.';
2352 $reportname =~ s/^$dropstring//;
2354 my $reporturl = "%%%ROOTURL%%%/misc/queued_report?report=$reportname";
2355 die "<a href=$reporturl>view</a>\n";
2365 Highly specific to CCH taxes. This should arguably go in some kind of
2366 subclass (FS::tax_rate::CCH) with auto-reblessing, similar to part_pkg
2367 subclasses. But currently there aren't any other options, so.
2369 Mixing automatic and manual editing works poorly at present.
2371 Tax liability calculations take too long and arguably don't belong here.
2372 Tax liability report generation not entirely safe (escaped).
2374 Sparse documentation.
2378 L<FS::Record>, L<FS::cust_location>, L<FS::cust_bill>