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_tax_rate_location> objects for the taxes charged.
391 If the taxable objects are linked to an invoice, this will also calculate
392 per-customer exemptions (cust_exempt and cust_taxname_exempt) and attach them
393 to the line items in the 'cust_tax_exempt_pkg' pseudo-field.
395 For accurate calculation of per-customer or per-location taxes, ALL items
396 appearing on the invoice (and subject to this tax) MUST be passed to this
397 method together, and NO items from any other invoice should be included.
401 # future optimization: it would probably suffice to return only the link
402 # records, and let the consolidation routine build the cust_bill_pkgs
406 # this used to accept a hash of options but none of them did anything
407 # so it's been removed.
409 my $taxables = shift;
410 my $classes = shift || [];
412 my $name = $self->taxname;
413 $name = 'Other surcharges'
414 if ($self->passtype == 2);
417 return unless @$taxables; # nothing to do
418 return if $self->disabled;
419 return if $self->passflag eq 'N'; # tax can't be passed to the customer
420 # but should probably still appear on the liability report--create a
421 # cust_tax_exempt_pkg record for it?
423 # in 4.x, the invoice is _already inserted_ before we try to calculate
424 # tax on it. though it may be a quotation, so be careful.
427 my $cust_bill = $taxables->[0]->cust_bill;
428 $cust_main = $cust_bill->cust_main if $cust_bill;
430 my $taxable_charged = 0;
431 my @cust_bill_pkg = grep { $taxable_charged += $_ unless ref; ref; }
434 my $taxratelocationnum = $self->tax_rate_location->taxratelocationnum;
436 warn "calculating taxes for ". $self->taxnum. " on ".
437 join (",", map { $_->pkgnum } @cust_bill_pkg)
440 my $maxtype = $self->maxtype || 0;
441 if ($maxtype != 0 && $maxtype != 1
442 && $maxtype != 14 && $maxtype != 15
443 && $maxtype != 18 # sigh
445 return $self->_fatal_or_null( 'tax with "'.
446 $self->maxtype_name. '" threshold'
448 } # I don't know why, it's not like there are maxtypes that we DO support
450 # we treat gross revenue as gross receipts and expect the tax data
451 # to DTRT (i.e. tax on tax rules)
452 if ($self->basetype != 0 && $self->basetype != 1 &&
453 $self->basetype != 5 && $self->basetype != 6 &&
454 $self->basetype != 7 && $self->basetype != 8 &&
455 $self->basetype != 14
458 $self->_fatal_or_null( 'tax with "'. $self->basetype_name. '" basis' );
461 my @tax_links; # for output
462 my %seen; # locationnum or pkgnum => 1
464 my $taxable_cents = 0;
465 my $taxable_units = 0;
469 my $cust_bill_pkg = shift @$taxables;
470 my $class = shift @$classes;
471 $class = 'all' if !defined($class);
473 my %usage_map = map { $_ => $cust_bill_pkg->usage($_) }
474 $cust_bill_pkg->usage_classes;
475 my $usage_total = sum( values(%usage_map), 0 );
477 # determine if the item has exemptions that apply to this tax def
478 my @exemptions = grep { $_->taxnum == $self->taxnum }
479 @{ $cust_bill_pkg->cust_tax_exempt_pkg };
481 if ( $self->tax > 0 ) {
483 my $taxable_charged = 0;
484 if ($class eq 'all') {
485 $taxable_charged = $cust_bill_pkg->setup + $cust_bill_pkg->recur;
486 } elsif ($class eq 'setup') {
487 $taxable_charged = $cust_bill_pkg->setup;
488 } elsif ($class eq 'recur') {
489 $taxable_charged = $cust_bill_pkg->recur - $usage_total;
491 $taxable_charged = $usage_map{$class} || 0;
494 foreach my $ex (@exemptions) {
495 # the only cases where the exemption doesn't apply:
496 # if it's a setup exemption and $class is not 'setup' or 'all'
497 # if it's a recur exemption and $class is 'setup'
498 if ( ( $ex->exempt_recur and $class eq 'setup' )
499 or ( $ex->exempt_setup and $class ne 'setup' and $class ne 'all' )
504 $taxable_charged -= $ex->amount;
506 # cust_main_county handles monthly capped exemptions; this doesn't.
508 # $taxable_charged can also be less than zero at this point
509 # (recur exemption + usage class breakdown); treat that as zero.
510 next if $taxable_charged <= 0;
512 # yeah, some false laziness with cust_main_county
513 my $this_tax_cents = int(100 * $taxable_charged * $self->tax);
514 my $tax_link = FS::cust_bill_pkg_tax_rate_location->new({
515 'taxnum' => $self->taxnum,
516 'taxtype' => ref($self),
517 'cents' => $this_tax_cents, # not a real field
518 'locationtaxid' => $self->location, # fundamentally silly
519 'taxable_billpkgnum' => $cust_bill_pkg->billpkgnum,
520 'taxable_cust_bill_pkg' => $cust_bill_pkg,
521 'taxratelocationnum' => $taxratelocationnum,
522 'taxclass' => $class,
524 push @tax_links, $tax_link;
526 $taxable_cents += 100 * $taxable_charged;
527 $tax_cents += $this_tax_cents;
529 } elsif ( $self->fee > 0 ) {
530 # most CCH taxes are this type, because nearly every county has a 911
534 # since we don't support partial exemptions (except setup/recur),
535 # if there's an exemption that applies to this package and taxrate,
536 # don't charge ANY per-unit fees
539 # don't apply fees to usage classes (maybe if we ever get per-minute
541 next unless $class eq 'setup'
545 if ( $self->unittype == 0 ) {
546 if ( !$seen{$cust_bill_pkg->pkgnum} ) {
548 $units = $cust_bill_pkg->units;
549 $seen{$cust_bill_pkg->pkgnum} = 1;
550 } # else it's been seen, leave it at zero units
552 } elsif ($self->unittype == 1) { # per minute
553 # STILL not supported...fortunately these only exist if you happen
554 # to be in Idaho or Little Rock, Arkansas
556 # though a voip_cdr package could easily report minutes of usage...
557 return $self->_fatal_or_null( 'fee with minute unit type' );
559 } elsif ( $self->unittype == 2 ) {
562 my $locationnum = $cust_bill_pkg->tax_locationnum;
563 if (!$locationnum and $cust_main) {
564 $locationnum = $cust_main->ship_locationnum;
566 # the other case is that it's a quotation
568 $units = 1 unless $seen{$cust_bill_pkg->tax_locationnum};
569 $seen{$cust_bill_pkg->tax_locationnum} = 1;
572 # Unittype 19 is used for prepaid wireless E911 charges in many states.
573 # Apparently "per retail purchase", which for us would mean per invoice.
574 # Unittype 20 is used for some 911 surcharges and I have no idea what
576 return $self->_fatal_or_null( 'unknown unit type in tax'. $self->taxnum );
578 my $this_tax_cents = int($units * $self->fee * 100);
579 my $tax_link = FS::cust_bill_pkg_tax_rate_location->new({
580 'taxnum' => $self->taxnum,
581 'taxtype' => ref($self),
582 'cents' => $this_tax_cents,
583 'locationtaxid' => $self->location,
584 'taxable_cust_bill_pkg' => $cust_bill_pkg,
585 'taxratelocationnum' => $taxratelocationnum,
587 push @tax_links, $tax_link;
589 $taxable_units += $units;
590 $tax_cents += $this_tax_cents;
593 } # foreach $cust_bill_pkg
595 # check bracket maxima; throw an error if we've gone over, because
596 # we don't really implement them
598 if ( ($self->taxmax > 0 and $taxable_cents > $self->taxmax*100 ) or
599 ($self->feemax > 0 and $taxable_units > $self->feemax) ) {
601 # (why not just cap taxable_charged/units at the taxmax/feemax? because
602 # it's way more complicated than that. this won't even catch every case
603 # where a bracket maximum should apply.)
604 return $self->_fatal_or_null( 'tax base > taxmax/feemax for tax'.$self->taxnum );
607 # round and distribute
608 my $total_tax_cents = sprintf('%.0f',
609 ($taxable_cents * $self->tax) + ($taxable_units * $self->fee * 100)
611 my $extra_cents = sprintf('%.0f', $total_tax_cents - $tax_cents);
612 $tax_cents += $extra_cents;
614 foreach (@tax_links) { # can never require more than a single pass, yes?
615 my $cents = $_->get('cents');
616 if ( $extra_cents > 0 ) {
620 $_->set('amount', sprintf('%.2f', $cents/100));
627 my ($self, $error) = @_;
629 $DB::single = 1; # not a mistake
631 my $conf = new FS::Conf;
633 $error = "can't yet handle ". $error;
634 my $name = $self->taxname;
635 $name = 'Other surcharges'
636 if ($self->passtype == 2);
638 if ($conf->exists('ignore_incalculable_taxes')) {
639 warn "WARNING: $error; billing anyway per ignore_incalculable_taxes conf\n";
640 return { name => $name, amount => 0 };
642 return "fatal: $error";
646 =item tax_on_tax CUST_LOCATION
648 Returns a list of taxes which are candidates for taxing taxes for the
649 given service location (see L<FS::cust_location>)
657 my $cust_location = shift;
659 warn "looking up taxes on tax ". $self->taxnum. " for customer ".
660 $cust_location->custnum
663 my $geocode = $cust_location->geocode($self->data_vendor);
667 my $extra_sql = ' AND ('.
668 join(' OR ', map{ 'geocode = '. $dbh->quote(substr($geocode, 0, $_)) }
673 my $order_by = 'ORDER BY taxclassnum, length(geocode) desc';
674 my $select = 'DISTINCT ON(taxclassnum) *';
676 # should qsearch preface columns with the table to facilitate joins?
677 my @taxclassnums = map { $_->taxclassnum }
678 qsearch( { 'table' => 'part_pkg_taxrate',
680 'hashref' => { 'data_vendor' => $self->data_vendor,
681 'taxclassnumtaxed' => $self->taxclassnum,
683 'extra_sql' => $extra_sql,
684 'order_by' => $order_by,
687 return () unless @taxclassnums;
690 "AND (". join(' OR ', map { "taxclassnum = $_" } @taxclassnums ). ")";
692 qsearch({ 'table' => 'tax_rate',
693 'hashref' => { 'geocode' => $geocode, },
694 'extra_sql' => $extra_sql,
699 =item tax_rate_location
701 Returns an object representing the location associated with this tax
702 (see L<FS::tax_rate_location>)
706 sub tax_rate_location {
709 qsearchs({ 'table' => 'tax_rate_location',
710 'hashref' => { 'data_vendor' => $self->data_vendor,
711 'geocode' => $self->geocode,
715 new FS::tax_rate_location;
722 Finds an existing tax definition matching the data_vendor, taxname,
723 taxclassnum, and geocode of this one, if one exists, and sets the contents of
724 this tax rate equal to that one (including its taxnum). If an existing
725 definition is not found, inserts this one. Returns an error string if
726 inserting a record failed.
732 # this doesn't uniquely identify CCH taxes (kinda goofy, I know)
733 die "find_or_insert is not compatible with CCH taxes\n"
734 if $self->data_vendor eq 'cch';
736 my @keys = (qw(data_vendor taxname taxclassnum geocode));
737 my %hash = map { $_ => $self->get($_) } @keys;
738 my $existing = qsearchs('tax_rate', \%hash);
740 foreach ($self->fields) {
741 $self->set($_, $existing->get($_));
745 return $self->insert;
759 sub _progressbar_foo {
764 my ($param, $job) = @_;
766 my $fh = $param->{filehandle};
767 my $format = $param->{'format'};
775 my @column_lengths = ();
776 my @column_callbacks = ();
777 if ( $format eq 'cch-fixed' || $format eq 'cch-fixed-update' ) {
778 $format =~ s/-fixed//;
779 my $date_format = sub { my $r='';
780 /^(\d{4})(\d{2})(\d{2})$/ && ($r="$3/$2/$1");
783 my $trim = sub { my $r = shift; $r =~ s/^\s*//; $r =~ s/\s*$//; $r };
784 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 );
785 push @column_lengths, 1 if $format eq 'cch-update';
786 push @column_callbacks, $trim foreach (@column_lengths); # 5, 6, 15, 17 esp
787 $column_callbacks[8] = $date_format;
791 my ( $count, $last, $min_sec ) = _progressbar_foo();
792 if ( $job || scalar(@column_callbacks) ) {
794 csv_from_fixed(\$fh, \$count, \@column_lengths, \@column_callbacks);
795 return $error if $error;
799 if ( $format eq 'cch' || $format eq 'cch-update' ) {
800 #false laziness w/below (sub _perform_cch_diff)
801 @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax
802 excessrate effective_date taxauth taxtype taxcat taxname
803 usetax useexcessrate fee unittype feemax maxtype passflag
805 push @fields, 'actionflag' if $format eq 'cch-update';
810 $hash->{'actionflag'} ='I' if ($hash->{'data_vendor'} eq 'cch');
811 $hash->{'data_vendor'} ='cch';
812 my $parser = new DateTime::Format::Strptime( pattern => "%m/%d/%Y",
813 time_zone => 'floating',
815 my $dt = $parser->parse_datetime( $hash->{'effective_date'} );
816 $hash->{'effective_date'} = $dt ? $dt->epoch : '';
818 $hash->{$_} =~ s/\s//g foreach qw( inoutcity inoutlocal ) ;
819 $hash->{$_} = sprintf("%.2f", $hash->{$_}) foreach qw( taxbase taxmax );
822 join(':', map{ $hash->{$_} } qw(taxtype taxcat) );
824 my %tax_class = ( 'data_vendor' => 'cch',
825 'taxclass' => $taxclassid,
828 my $tax_class = qsearchs( 'tax_class', \%tax_class );
829 return "Error updating tax rate: no tax class $taxclassid"
832 $hash->{'taxclassnum'} = $tax_class->taxclassnum;
834 foreach (qw( taxtype taxcat )) {
838 my %passflagmap = ( '0' => '',
842 $hash->{'passflag'} = $passflagmap{$hash->{'passflag'}}
843 if exists $passflagmap{$hash->{'passflag'}};
845 foreach (keys %$hash) {
846 $hash->{$_} = substr($hash->{$_}, 0, 80)
847 if length($hash->{$_}) > 80;
850 my $actionflag = delete($hash->{'actionflag'});
852 $hash->{'taxname'} =~ s/`/'/g;
853 $hash->{'taxname'} =~ s|\\|/|g;
855 return '' if $format eq 'cch'; # but not cch-update
857 if ($actionflag eq 'I') {
858 $insert{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
859 }elsif ($actionflag eq 'D') {
860 $delete{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
862 return "Unexpected action flag: ". $hash->{'actionflag'};
865 delete($hash->{$_}) for keys %$hash;
871 } elsif ( $format eq 'extended' ) {
872 die "unimplemented\n";
876 die "unknown format $format";
879 my $csv = new Text::CSV_XS;
883 local $SIG{HUP} = 'IGNORE';
884 local $SIG{INT} = 'IGNORE';
885 local $SIG{QUIT} = 'IGNORE';
886 local $SIG{TERM} = 'IGNORE';
887 local $SIG{TSTP} = 'IGNORE';
888 local $SIG{PIPE} = 'IGNORE';
890 my $oldAutoCommit = $FS::UID::AutoCommit;
891 local $FS::UID::AutoCommit = 0;
894 while ( defined($line=<$fh>) ) {
895 $csv->parse($line) or do {
896 $dbh->rollback if $oldAutoCommit;
897 return "can't parse: ". $csv->error_input();
900 if ( $job ) { # progress bar
901 if ( time - $min_sec > $last ) {
902 my $error = $job->update_statustext(
903 int( 100 * $imported / $count ). ",Importing tax rates"
906 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
913 my @columns = $csv->fields();
915 my %tax_rate = ( 'data_vendor' => $format );
916 foreach my $field ( @fields ) {
917 $tax_rate{$field} = shift @columns;
920 if ( scalar( @columns ) ) {
921 $dbh->rollback if $oldAutoCommit;
922 return "Unexpected trailing columns in line (wrong format?) importing tax_rate: $line";
925 my $error = &{$hook}(\%tax_rate);
927 $dbh->rollback if $oldAutoCommit;
931 if (scalar(keys %tax_rate)) { #inserts only, not updates for cch
933 my $tax_rate = new FS::tax_rate( \%tax_rate );
934 $error = $tax_rate->insert;
937 $dbh->rollback if $oldAutoCommit;
938 return "can't insert tax_rate for $line: $error";
947 my @replace = grep { exists($delete{$_}) } keys %insert;
949 if ( $job ) { # progress bar
950 if ( time - $min_sec > $last ) {
951 my $error = $job->update_statustext(
952 int( 100 * $imported / $count ). ",Importing tax rates"
955 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
962 my $old = qsearchs( 'tax_rate', $delete{$_} );
966 my $new = new FS::tax_rate({ $old->hash, %{$insert{$_}}, 'manual' => '' });
967 $new->taxnum($old->taxnum);
968 my $error = $new->replace($old);
971 $dbh->rollback if $oldAutoCommit;
972 my $hashref = $insert{$_};
973 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
974 return "can't replace tax_rate for $line: $error";
981 $old = delete $delete{$_};
982 warn "WARNING: can't find tax_rate to replace (inserting instead and continuing) for: ".
983 #join(" ", map { "$_ => ". $old->{$_} } @fields);
984 join(" ", map { "$_ => ". $old->{$_} } keys(%$old) );
990 for (grep { !exists($delete{$_}) } keys %insert) {
991 if ( $job ) { # progress bar
992 if ( time - $min_sec > $last ) {
993 my $error = $job->update_statustext(
994 int( 100 * $imported / $count ). ",Importing tax rates"
997 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1004 my $tax_rate = new FS::tax_rate( $insert{$_} );
1005 my $error = $tax_rate->insert;
1008 $dbh->rollback if $oldAutoCommit;
1009 my $hashref = $insert{$_};
1010 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
1011 return "can't insert tax_rate for $line: $error";
1017 for (grep { !exists($insert{$_}) } keys %delete) {
1018 if ( $job ) { # progress bar
1019 if ( time - $min_sec > $last ) {
1020 my $error = $job->update_statustext(
1021 int( 100 * $imported / $count ). ",Importing tax rates"
1024 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1031 my $tax_rate = qsearchs( 'tax_rate', $delete{$_} );
1033 $dbh->rollback if $oldAutoCommit;
1034 $tax_rate = $delete{$_};
1035 warn "WARNING: can't find tax_rate to delete for: ".
1036 join(" ", map { "$_ => ". $tax_rate->{$_} } keys(%$tax_rate) ).
1039 my $error = $tax_rate->delete; # XXX we really should not do this
1040 # (it orphans CBPTRL records)
1043 $dbh->rollback if $oldAutoCommit;
1044 my $hashref = $delete{$_};
1045 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
1046 return "can't delete tax_rate for $line: $error";
1053 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
1055 return "Empty file!" unless ($imported || $format eq 'cch-update');
1061 =item process_batch_import
1063 Load a batch import as a queued JSRPC job
1067 sub process_batch_import {
1068 my ($job, $param) = @_;
1070 if ( $param->{reload} ) {
1071 process_batch_reload($job, $param);
1074 _perform_batch_import($job, $param);
1079 sub _perform_batch_import {
1080 my ($job, $param) = @_;
1082 my $oldAutoCommit = $FS::UID::AutoCommit;
1083 local $FS::UID::AutoCommit = 0;
1086 my $format = $param->{'format'};
1088 my $files = $param->{'uploaded_files'}
1089 or die "No files provided.";
1091 my (%files) = map { /^(\w+):((taxdata\/\w+\.\w+\/)?[\.\w]+)$/ ? ($1,$2):() }
1094 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
1097 if ( $format eq 'cch' || $format eq 'cch-fixed'
1098 || $format eq 'cch-update' || $format eq 'cch-fixed-update' )
1101 my @insert_list = ();
1102 my @delete_list = ();
1103 my @predelete_list = ();
1104 my $insertname = '';
1105 my $deletename = '';
1107 my @list = ( 'GEOCODE', \&FS::tax_rate_location::batch_import,
1108 'CODE', \&FS::tax_class::batch_import,
1109 'PLUS4', \&FS::cust_tax_location::batch_import,
1110 'ZIP', \&FS::cust_tax_location::batch_import,
1111 'TXMATRIX', \&FS::part_pkg_taxrate::batch_import,
1112 'DETAIL', \&FS::tax_rate::batch_import,
1114 while( scalar(@list) ) {
1115 my ( $name, $import_sub ) = splice( @list, 0, 2 );
1116 my $file = lc($name). 'file';
1118 unless ($files{$file}) {
1119 #$error = "No $name supplied";
1122 next if $name eq 'DETAIL' && $format =~ /update/;
1124 my $filename = "$dir/". $files{$file};
1126 if ( $format =~ /update/ ) {
1128 ( $error, $insertname, $deletename ) =
1129 _perform_cch_insert_delete_split( $name, $filename, $dir, $format )
1133 unlink $filename or warn "Can't delete $filename: $!"
1134 unless $keep_cch_files;
1135 push @insert_list, $name, $insertname, $import_sub, $format;
1136 if ( $name eq 'GEOCODE' || $name eq 'CODE' ) { #handle this whole ordering issue better
1137 unshift @predelete_list, $name, $deletename, $import_sub, $format;
1139 unshift @delete_list, $name, $deletename, $import_sub, $format;
1144 push @insert_list, $name, $filename, $import_sub, $format;
1151 'DETAIL', "$dir/".$files{detailfile}, \&FS::tax_rate::batch_import, $format
1152 if $format =~ /update/;
1154 my %addl_param = ();
1155 if ( $param->{'delete_only'} ) {
1156 $addl_param{'delete_only'} = $param->{'delete_only'};
1160 $error ||= _perform_cch_tax_import( $job,
1161 [ @predelete_list ],
1168 @list = ( @predelete_list, @insert_list, @delete_list );
1169 while( !$keep_cch_files && scalar(@list) ) {
1170 my ( undef, $file, undef, undef ) = splice( @list, 0, 4 );
1171 unlink $file or warn "Can't delete $file: $!";
1174 } elsif ( $format =~ /^billsoft-(\w+)$/ ) {
1176 my $file = $dir.'/'.$files{'file'};
1177 open my $fh, "< $file" or $error ||= "Can't open file $file: $!";
1181 format => 'billsoft',
1183 if ( $mode eq 'pcode' ) {
1184 $error ||= FS::cust_tax_location::batch_import(@param);
1186 $error ||= FS::tax_rate_location::batch_import(@param);
1187 } elsif ( $mode eq 'taxclass' ) {
1188 $error ||= FS::tax_class::batch_import(@param);
1189 } elsif ( $mode eq 'taxproduct' ) {
1190 $error ||= FS::part_pkg_taxproduct::batch_import(@param);
1192 die "unknown import mode 'billsoft-$mode'\n";
1196 die "Unknown format: $format";
1200 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1203 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
1210 # EVERYTHING THAT FOLLOWS IS CCH-SPECIFIC.
1214 sub _perform_cch_tax_import {
1215 my ( $job, $predelete_list, $insert_list, $delete_list, $addl_param ) = @_;
1219 foreach my $list ($predelete_list, $insert_list, $delete_list) {
1220 while( scalar(@$list) ) {
1221 my ( $name, $file, $method, $format ) = splice( @$list, 0, 4 );
1222 my $fmt = "$format-update";
1223 $fmt = $format. ( lc($name) eq 'zip' ? '-zip' : '' );
1224 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
1225 my $param = { 'filehandle' => $fh,
1229 $error ||= &{$method}($param, $job);
1237 sub _perform_cch_insert_delete_split {
1238 my ($name, $filename, $dir, $format) = @_;
1242 open my $fh, "< $filename"
1243 or $error ||= "Can't open $name file $filename: $!";
1245 my $ifh = new File::Temp( TEMPLATE => "$name.insert.XXXXXXXX",
1248 ) or die "can't open temp file: $!\n";
1249 my $insertname = $ifh->filename;
1251 my $dfh = new File::Temp( TEMPLATE => "$name.delete.XXXXXXXX",
1254 ) or die "can't open temp file: $!\n";
1255 my $deletename = $dfh->filename;
1257 my $insert_pattern = ($format eq 'cch-update') ? qr/"I"\s*$/ : qr/I\s*$/;
1258 my $delete_pattern = ($format eq 'cch-update') ? qr/"D"\s*$/ : qr/D\s*$/;
1261 $handle = $ifh if $_ =~ /$insert_pattern/;
1262 $handle = $dfh if $_ =~ /$delete_pattern/;
1264 $error = "bad input line: $_" unless $handle;
1273 return ($error, $insertname, $deletename);
1276 sub _perform_cch_diff {
1277 my ($name, $newdir, $olddir) = @_;
1282 open my $oldcsvfh, "$olddir/$name.txt"
1283 or die "failed to open $olddir/$name.txt: $!\n";
1285 while(<$oldcsvfh>) {
1292 open my $newcsvfh, "$newdir/$name.txt"
1293 or die "failed to open $newdir/$name.txt: $!\n";
1295 my $dfh = new File::Temp( TEMPLATE => "$name.diff.XXXXXXXX",
1298 ) or die "can't open temp file: $!\n";
1299 my $diffname = $dfh->filename;
1301 while(<$newcsvfh>) {
1303 if (exists($oldlines{$_})) {
1306 print $dfh $_, ',"I"', "\n";
1311 #false laziness w/above (sub batch_import)
1312 my @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax
1313 excessrate effective_date taxauth taxtype taxcat taxname
1314 usetax useexcessrate fee unittype feemax maxtype passflag
1315 passtype basetype );
1316 my $numfields = scalar(@fields);
1318 my $csv = new Text::CSV_XS { 'always_quote' => 1 };
1320 for my $line (grep $oldlines{$_}, keys %oldlines) {
1322 $csv->parse($line) or do {
1323 #$dbh->rollback if $oldAutoCommit;
1324 die "can't parse: ". $csv->error_input();
1326 my @columns = $csv->fields();
1328 $csv->combine( splice(@columns, 0, $numfields) );
1330 print $dfh $csv->string, ',"D"', "\n";
1338 sub _cch_fetch_and_unzip {
1339 my ( $job, $urls, $secret, $dir ) = @_;
1341 my $ua = new LWP::UserAgent;
1342 foreach my $url (split ',', $urls) {
1343 my @name = split '/', $url; #somewhat restrictive
1344 my $name = pop @name;
1345 $name =~ /([\w.]+)/; # untaint that which we don't trust so much any more
1348 open my $taxfh, ">$dir/$name" or die "Can't open $dir/$name: $!\n";
1350 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1351 my $res = $ua->request(
1352 new HTTP::Request( GET => $url ),
1354 print $taxfh $_[0] or die "Can't write to $dir/$name: $!\n";
1355 my $content_length = $_[1]->content_length;
1356 $imported += length($_[0]);
1357 if ( time - $min_sec > $last ) {
1358 my $error = $job->update_statustext(
1359 ($content_length ? int(100 * $imported/$content_length) : 0 ).
1360 ",Downloading data from CCH"
1362 die $error if $error;
1367 die "download of $url failed: ". $res->status_line
1368 unless $res->is_success;
1371 my $error = $job->update_statustext( "0,Unpacking data" );
1372 die $error if $error;
1373 $secret =~ /([\w.]+)/; # untaint that which we don't trust so much any more
1375 system('unzip', "-P", $secret, "-d", "$dir", "$dir/$name") == 0
1376 or die "unzip -P $secret -d $dir $dir/$name failed";
1377 #unlink "$dir/$name";
1381 sub _cch_extract_csv_from_dbf {
1382 my ( $job, $dir, $name ) = @_;
1387 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1388 my $error = $job->update_statustext( "0,Unpacking $name" );
1389 die $error if $error;
1390 warn "opening $dir.new/$name.dbf\n" if $DEBUG;
1391 my $table = new XBase 'name' => "$dir.new/$name.dbf";
1392 die "failed to access $dir.new/$name.dbf: ". XBase->errstr
1393 unless defined($table);
1394 my $count = $table->last_record; # approximately;
1395 open my $csvfh, ">$dir.new/$name.txt"
1396 or die "failed to open $dir.new/$name.txt: $!\n";
1398 my $csv = new Text::CSV_XS { 'always_quote' => 1 };
1399 my @fields = $table->field_names;
1400 my $cursor = $table->prepare_select;
1402 sub { my $date = shift;
1403 $date =~ /^(\d{4})(\d{2})(\d{2})$/ && ($date = "$2/$3/$1");
1406 while (my $row = $cursor->fetch_hashref) {
1407 $csv->combine( map { my $type = $table->field_type($_);
1409 &{$format_date}($row->{$_}) ;
1410 } elsif ($type eq 'N' && $row->{$_} =~ /e-/i ) {
1411 sprintf('%.8f', $row->{$_}); #db row is numeric(14,8)
1418 print $csvfh $csv->string, "\n";
1420 if ( time - $min_sec > $last ) {
1421 my $error = $job->update_statustext(
1422 int(100 * $imported/$count). ",Unpacking $name"
1424 die $error if $error;
1432 sub _remember_disabled_taxes {
1433 my ( $job, $format, $disabled_tax_rate ) = @_;
1437 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1439 my @items = qsearch( { table => 'tax_rate',
1440 hashref => { disabled => 'Y',
1441 data_vendor => $format,
1443 select => 'geocode, taxclassnum',
1446 my $count = scalar(@items);
1447 foreach my $tax_rate ( @items ) {
1448 if ( time - $min_sec > $last ) {
1449 $job->update_statustext(
1450 int( 100 * $imported / $count ). ",Remembering disabled taxes"
1456 qsearchs( 'tax_class', { taxclassnum => $tax_rate->taxclassnum } );
1457 unless ( $tax_class ) {
1458 warn "failed to find tax_class ". $tax_rate->taxclassnum;
1461 $disabled_tax_rate->{$tax_rate->geocode. ':'. $tax_class->taxclass} = 1;
1465 sub _remember_tax_products {
1466 my ( $job, $format, $taxproduct ) = @_;
1468 # XXX FIXME this loop only works when cch is the only data provider
1470 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1473 WHERE taxproductnum IS NOT NULL
1474 OR EXISTS ( SELECT 1 from part_pkg_option
1475 WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
1476 AND optionname LIKE 'usage_taxproductnum_%'
1477 AND optionvalue != ''
1480 my @items = qsearch( { table => 'part_pkg',
1481 select => 'DISTINCT pkgpart,taxproductnum',
1483 extra_sql => $extra_sql,
1486 my $count = scalar(@items);
1487 foreach my $part_pkg ( @items ) {
1488 if ( time - $min_sec > $last ) {
1489 $job->update_statustext(
1490 int( 100 * $imported / $count ). ",Remembering tax products"
1495 warn "working with package part ". $part_pkg->pkgpart.
1496 "which has a taxproductnum of ". $part_pkg->taxproductnum. "\n" if $DEBUG;
1497 my $part_pkg_taxproduct = $part_pkg->taxproduct('');
1498 $taxproduct->{$part_pkg->pkgpart}->{''} = $part_pkg_taxproduct->taxproduct
1499 if $part_pkg_taxproduct && $part_pkg_taxproduct->data_vendor eq $format;
1501 foreach my $option ( $part_pkg->part_pkg_option ) {
1502 next unless $option->optionname =~ /^usage_taxproductnum_(\w+)$/;
1505 $part_pkg_taxproduct = $part_pkg->taxproduct($class);
1506 $taxproduct->{$part_pkg->pkgpart}->{$class} =
1507 $part_pkg_taxproduct->taxproduct
1508 if $part_pkg_taxproduct && $part_pkg_taxproduct->data_vendor eq $format;
1513 sub _restore_remembered_tax_products {
1514 my ( $job, $format, $taxproduct ) = @_;
1518 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1519 my $count = scalar(keys %$taxproduct);
1520 foreach my $pkgpart ( keys %$taxproduct ) {
1521 warn "restoring taxproductnums on pkgpart $pkgpart\n" if $DEBUG;
1522 if ( time - $min_sec > $last ) {
1523 $job->update_statustext(
1524 int( 100 * $imported / $count ). ",Restoring tax products"
1530 my $part_pkg = qsearchs('part_pkg', { pkgpart => $pkgpart } );
1531 unless ( $part_pkg ) {
1532 return "somehow failed to find part_pkg with pkgpart $pkgpart!\n";
1535 my %options = $part_pkg->options;
1536 my %pkg_svc = map { $_->svcpart => $_->quantity } $part_pkg->pkg_svc;
1537 my $primary_svc = $part_pkg->svcpart;
1538 my $new = new FS::part_pkg { $part_pkg->hash };
1540 foreach my $class ( keys %{ $taxproduct->{$pkgpart} } ) {
1541 warn "working with class '$class'\n" if $DEBUG;
1542 my $part_pkg_taxproduct =
1543 qsearchs( 'part_pkg_taxproduct',
1544 { taxproduct => $taxproduct->{$pkgpart}->{$class},
1545 data_vendor => $format,
1549 unless ( $part_pkg_taxproduct ) {
1550 return "failed to find part_pkg_taxproduct (".
1551 $taxproduct->{$pkgpart}->{$class}. ") for pkgpart $pkgpart\n";
1554 if ( $class eq '' ) {
1555 $new->taxproductnum($part_pkg_taxproduct->taxproductnum);
1559 $options{"usage_taxproductnum_$class"} =
1560 $part_pkg_taxproduct->taxproductnum;
1564 my $error = $new->replace( $part_pkg,
1565 'pkg_svc' => \%pkg_svc,
1566 'primary_svc' => $primary_svc,
1567 'options' => \%options,
1570 return $error if $error;
1577 sub _restore_remembered_disabled_taxes {
1578 my ( $job, $format, $disabled_tax_rate ) = @_;
1580 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1581 my $count = scalar(keys %$disabled_tax_rate);
1582 foreach my $key (keys %$disabled_tax_rate) {
1583 if ( time - $min_sec > $last ) {
1584 $job->update_statustext(
1585 int( 100 * $imported / $count ). ",Disabling tax rates"
1590 my ($geocode,$taxclass) = split /:/, $key, 2;
1591 my @tax_class = qsearch( 'tax_class', { data_vendor => $format,
1592 taxclass => $taxclass,
1594 return "found multiple tax_class records for format $format class $taxclass"
1595 if scalar(@tax_class) > 1;
1597 unless (scalar(@tax_class)) {
1598 warn "no tax_class for format $format class $taxclass\n";
1603 qsearch('tax_rate', { data_vendor => $format,
1604 geocode => $geocode,
1605 taxclassnum => $tax_class[0]->taxclassnum,
1609 if (scalar(@tax_rate) > 1) {
1610 return "found multiple tax_rate records for format $format geocode ".
1611 "$geocode and taxclass $taxclass ( taxclassnum ".
1612 $tax_class[0]->taxclassnum. " )";
1615 if (scalar(@tax_rate)) {
1616 $tax_rate[0]->disabled('Y');
1617 my $error = $tax_rate[0]->replace;
1618 return $error if $error;
1623 sub _remove_old_tax_data {
1624 my ( $job, $format ) = @_;
1627 my $error = $job->update_statustext( "0,Removing old tax data" );
1628 die $error if $error;
1630 my $sql = "UPDATE public.tax_rate_location SET disabled='Y' ".
1631 "WHERE data_vendor = ". $dbh->quote($format);
1632 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1635 tax_rate part_pkg_taxrate part_pkg_taxproduct tax_class cust_tax_location
1637 foreach my $table ( @table ) {
1638 $sql = "DELETE FROM public.$table WHERE data_vendor = ".
1639 $dbh->quote($format);
1640 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1643 if ( $format eq 'cch' ) {
1644 $sql = "DELETE FROM public.cust_tax_location WHERE data_vendor = ".
1645 $dbh->quote("$format-zip");
1646 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1652 sub _create_temporary_tables {
1653 my ( $job, $format ) = @_;
1656 my $error = $job->update_statustext( "0,Creating temporary tables" );
1657 die $error if $error;
1659 my @table = qw( tax_rate
1666 foreach my $table ( @table ) {
1668 "CREATE TEMPORARY TABLE $table ( LIKE $table INCLUDING DEFAULTS )";
1669 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1675 sub _copy_from_temp {
1676 my ( $job, $format ) = @_;
1679 my $error = $job->update_statustext( "0,Making permanent" );
1680 die $error if $error;
1682 my @table = qw( tax_rate
1689 foreach my $table ( @table ) {
1691 "INSERT INTO public.$table SELECT * from $table";
1692 $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
1698 =item process_download_and_reload
1700 Download and process a tax update as a queued JSRPC job after wiping the
1701 existing wipeable tax data.
1705 sub process_download_and_reload {
1706 _process_reload(\&process_download_and_update, @_);
1715 =item process_batch_reload
1717 Load and process a tax update from the provided files as a queued JSRPC job
1718 after wiping the existing wipable tax data.
1722 sub process_batch_reload {
1723 _process_reload(\&_perform_batch_import, @_);
1726 sub _process_reload {
1727 my ( $continuation, $job, $param ) = @_;
1728 my $format = $param->{'format'};
1730 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1732 if ( $job ) { # progress bar
1733 my $error = $job->update_statustext( 0 );
1734 die $error if $error;
1737 my $oldAutoCommit = $FS::UID::AutoCommit;
1738 local $FS::UID::AutoCommit = 0;
1742 if ( $format =~ /^cch/ ) {
1743 # no, THIS part is CCH specific
1746 "SELECT count(*) FROM part_pkg_taxoverride JOIN tax_class ".
1747 "USING (taxclassnum) WHERE data_vendor = '$format'";
1748 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
1750 or die "Unexpected error executing statement $sql: ". $sth->errstr;
1751 die "Don't (yet) know how to handle part_pkg_taxoverride records."
1752 if $sth->fetchrow_arrayref->[0];
1754 # really should get a table EXCLUSIVE lock here
1756 #remember disabled taxes
1757 my %disabled_tax_rate = ();
1758 $error ||= _remember_disabled_taxes( $job, $format, \%disabled_tax_rate );
1760 #remember tax products
1761 my %taxproduct = ();
1762 $error ||= _remember_tax_products( $job, $format, \%taxproduct );
1765 $error ||= _create_temporary_tables( $job, $format );
1769 eval { &{$continuation}( $job, $param ) };
1773 #restore taxproducts
1774 $error ||= _restore_remembered_tax_products( $job, $format, \%taxproduct );
1778 _restore_remembered_disabled_taxes( $job, $format, \%disabled_tax_rate );
1780 #wipe out the old data
1781 $error ||= _remove_old_tax_data( $job, $format );
1784 $error ||= _copy_from_temp( $job, $format );
1786 } elsif ( $format =~ /^billsoft-(\w+)/ ) {
1790 if ( $mode eq 'pcode' ) {
1792 "DELETE FROM cust_tax_location WHERE data_vendor = 'billsoft'",
1793 "UPDATE tax_rate_location SET disabled = 'Y' WHERE data_vendor = 'billsoft'";
1794 } elsif ( $mode eq 'taxclass' ) {
1796 "DELETE FROM tax_class WHERE data_vendor = 'billsoft'";
1797 } elsif ( $mode eq 'taxproduct' ) {
1799 "DELETE FROM part_pkg_taxproduct WHERE data_vendor = 'billsoft'";
1803 if (!$dbh->do($_)) {
1804 $error = $dbh->errstr;
1811 eval { &{ $continuation }($job, $param) };
1814 } # if ($format ...)
1817 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1822 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
1826 =item process_download_and_update
1828 Download and process a tax update as a queued JSRPC job
1832 sub process_download_and_update {
1836 my $format = $param->{'format'}; #well... this is all cch specific
1838 my ( $imported, $last, $min_sec ) = _progressbar_foo();
1840 if ( $job ) { # progress bar
1841 my $error = $job->update_statustext( 0);
1842 die $error if $error;
1845 my $cache_dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/';
1846 my $dir = $cache_dir. 'taxdata';
1848 mkdir $dir or die "can't create $dir: $!\n";
1851 if ($format eq 'cch') {
1853 my @namelist = qw( code detail geocode plus4 txmatrix zip );
1855 my $conf = new FS::Conf;
1856 die "direct download of tax data not enabled\n"
1857 unless $conf->exists('taxdatadirectdownload');
1858 my ( $urls, $username, $secret, $states ) =
1859 $conf->config('taxdatadirectdownload');
1860 die "No tax download URL provided. ".
1861 "Did you set the taxdatadirectdownload configuration value?\n"
1869 # really should get a table EXCLUSIVE lock here
1870 # check if initial import or update
1872 # relying on mkdir "$dir.new" as a mutex
1874 my $sql = "SELECT count(*) from tax_rate WHERE data_vendor='$format'";
1875 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
1876 $sth->execute() or die $sth->errstr;
1877 my $update = $sth->fetchrow_arrayref->[0];
1879 # create cache and/or rotate old tax data
1884 opendir(my $dirh, "$dir.9") or die "failed to open $dir.9: $!\n";
1885 foreach my $file (readdir($dirh)) {
1886 unlink "$dir.9/$file" if (-f "$dir.9/$file");
1892 for (8, 7, 6, 5, 4, 3, 2, 1) {
1893 if ( -e "$dir.$_" ) {
1894 rename "$dir.$_", "$dir.". ($_+1) or die "can't rename $dir.$_: $!\n";
1897 rename "$dir", "$dir.1" or die "can't rename $dir: $!\n";
1901 die "can't find previous tax data\n" if $update;
1905 mkdir "$dir.new" or die "can't create $dir.new: $!\n";
1907 # fetch and unpack the zip files
1909 _cch_fetch_and_unzip( $job, $urls, $secret, "$dir.new" );
1911 # extract csv files from the dbf files
1913 foreach my $name ( @namelist ) {
1914 _cch_extract_csv_from_dbf( $job, $dir, $name );
1917 # generate the diff files
1920 foreach my $name ( @namelist ) {
1921 my $difffile = "$dir.new/$name.txt";
1923 my $error = $job->update_statustext( "0,Comparing to previous $name" );
1924 die $error if $error;
1925 warn "processing $dir.new/$name.txt\n" if $DEBUG;
1926 my $olddir = $update ? "$dir.1" : "";
1927 $difffile = _perform_cch_diff( $name, "$dir.new", $olddir );
1929 $difffile =~ s/^$cache_dir//;
1930 push @list, "${name}file:$difffile";
1933 # perform the import
1934 local $keep_cch_files = 1;
1935 $param->{uploaded_files} = join( ',', @list );
1936 $param->{format} .= '-update' if $update;
1938 _perform_batch_import( $job, $param );
1940 rename "$dir.new", "$dir"
1941 or die "cch tax update processed, but can't rename $dir.new: $!\n";
1944 die "Unknown format: $format";
1948 =item browse_queries PARAMS
1950 Returns a list consisting of a hashref suited for use as the argument
1951 to qsearch, and sql query string. Each is based on the PARAMS hashref
1952 of keys and values which frequently would be passed as C<scalar($cgi->Vars)>
1953 from a form. This conveniently creates the query hashref and count_query
1954 string required by the browse and search elements. As a side effect,
1955 the PARAMS hashref is untainted and keys with unexpected values are removed.
1959 sub browse_queries {
1963 'table' => 'tax_rate',
1965 'order_by' => 'ORDER BY geocode, taxclassnum',
1970 if ( $params->{data_vendor} =~ /^(\w+)$/ ) {
1971 $extra_sql .= ' WHERE data_vendor = '. dbh->quote($1);
1973 delete $params->{data_vendor};
1976 if ( $params->{geocode} =~ /^(\w+)$/ ) {
1977 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1978 'geocode LIKE '. dbh->quote($1.'%');
1980 delete $params->{geocode};
1983 if ( $params->{taxclassnum} =~ /^(\d+)$/ &&
1984 qsearchs( 'tax_class', {'taxclassnum' => $1} )
1987 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1988 ' taxclassnum = '. dbh->quote($1)
1990 delete $params->{taxclassnun};
1994 if ( $params->{tax_type} =~ /^(\d+)$/ );
1995 delete $params->{tax_type}
1999 if ( $params->{tax_cat} =~ /^(\d+)$/ );
2000 delete $params->{tax_cat}
2003 my @taxclassnum = ();
2004 if ($tax_type || $tax_cat ) {
2005 my $compare = "LIKE '". ( $tax_type || "%" ). ":". ( $tax_cat || "%" ). "'";
2006 $compare = "= '$tax_type:$tax_cat'" if ($tax_type && $tax_cat);
2007 @taxclassnum = map { $_->taxclassnum }
2008 qsearch({ 'table' => 'tax_class',
2010 'extra_sql' => "WHERE taxclass $compare",
2014 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ). '( '.
2015 join(' OR ', map { " taxclassnum = $_ " } @taxclassnum ). ' )'
2016 if ( @taxclassnum );
2018 unless ($params->{'showdisabled'}) {
2019 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
2020 "( disabled = '' OR disabled IS NULL )";
2023 $query->{extra_sql} = $extra_sql;
2025 return ($query, "SELECT COUNT(*) FROM tax_rate $extra_sql");
2028 =item queue_liability_report PARAMS
2030 Launches a tax liability report.
2032 PARAMS needs to be a base64-encoded Storable hash containing:
2033 - beginning: the start date, as a I<user-readable string> (not a timestamp).
2034 - end: the end date of the report, likewise.
2035 - agentnum: the agent to limit the report to, if any.
2039 sub queue_liability_report {
2044 $cgi->param('beginning', $param->{beginning});
2045 $cgi->param('ending', $param->{ending});
2046 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
2047 my $agentnum = $param->{agentnum};
2048 if ($agentnum =~ /^(\d+)$/) { $agentnum = $1; } else { $agentnum = ''; };
2049 generate_liability_report(
2050 'beginning' => $beginning,
2051 'ending' => $ending,
2052 'agentnum' => $agentnum,
2053 'p' => $param->{RootURL},
2058 =item generate_liability_report PARAMS
2060 Generates a tax liability report. PARAMS must include:
2062 - beginning, as a timestamp
2063 - ending, as a timestamp
2064 - p: the Freeside root URL, for generating links
2065 - agentnum (optional)
2069 #shit, all sorts of false laxiness w/report_newtax.cgi
2070 sub generate_liability_report {
2073 my ( $count, $last, $min_sec ) = _progressbar_foo();
2075 #let us open the temp file early
2076 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
2077 my $report = new File::Temp( TEMPLATE => 'report.tax.liability.XXXXXXXX',
2079 UNLINK => 0, # not so temp
2080 ) or die "can't open report file: $!\n";
2082 my $conf = new FS::Conf;
2083 my $money_char = $conf->config('money_char') || '$';
2086 JOIN cust_bill USING ( invnum )
2087 LEFT JOIN cust_main USING ( custnum )
2091 "LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum )";
2092 my $join_tax_loc = "LEFT JOIN tax_rate_location USING ( taxratelocationnum )";
2094 my $addl_from = " $join_cust $join_loc $join_tax_loc ";
2096 my $where = "WHERE _date >= $args{beginning} AND _date <= $args{ending} ";
2099 if ( $args{agentnum} =~ /^(\d+)$/ ) {
2100 my $agent = qsearchs('agent', { 'agentnum' => $1 } );
2101 die "agent not found" unless $agent;
2102 $agentname = $agent->agent;
2103 $where .= ' AND cust_main.agentnum = '. $agent->agentnum;
2106 #my @taxparam = ( 'itemdesc', 'tax_rate_location.state', 'tax_rate_location.county', 'tax_rate_location.city', 'cust_bill_pkg_tax_rate_location.locationtaxid' );
2107 my @taxparams = qw( city county state locationtaxid );
2108 my @params = ('itemdesc', @taxparams);
2110 my $select = 'DISTINCT itemdesc,locationtaxid,tax_rate_location.state,tax_rate_location.county,tax_rate_location.city';
2112 #false laziness w/FS::Report::Table::Monthly (sub should probably be moved up
2113 #to FS::Report or FS::Record or who the fuck knows where)
2114 my $scalar_sql = sub {
2115 my( $r, $param, $sql ) = @_;
2116 my $sth = dbh->prepare($sql) or die dbh->errstr;
2117 $sth->execute( map $r->$_(), @$param )
2118 or die "Unexpected error executing statement $sql: ". $sth->errstr;
2119 $sth->fetchrow_arrayref->[0] || 0;
2128 # get all distinct tuples of (tax name, state, county, city, locationtaxid)
2129 # for taxes that have been charged
2130 # (state, county, city are from tax_rate_location, not from customer data)
2131 my @tax_and_location = qsearch({ table => 'cust_bill_pkg',
2133 hashref => { pkgpart => 0 },
2134 addl_from => $addl_from,
2135 extra_sql => $where,
2138 $count = scalar(@tax_and_location);
2139 foreach my $t ( @tax_and_location ) {
2142 if ( time - $min_sec > $last ) {
2143 $args{job}->update_statustext( int( 100 * $calculated / $count ).
2150 #my @params = map { my $f = $_; $f =~ s/.*\.//; $f } @taxparam;
2151 my $label = join('~', map { $t->$_ } @params);
2152 $label = 'Tax'. $label if $label =~ /^~/;
2153 unless ( exists( $taxes{$label} ) ) {
2154 my ($baselabel, @trash) = split /~/, $label;
2156 $taxes{$label}->{'label'} = join(', ', split(/~/, $label) );
2157 $taxes{$label}->{'url_param'} =
2158 join(';', map { "$_=". uri_escape($t->$_) } @params);
2161 " ( itemdesc = ? OR ? = '' AND itemdesc IS NULL ) ".
2162 "AND ". FS::tax_rate_location->location_sql( map { $_ => $t->$_ }
2167 "FROM cust_bill_pkg $addl_from $where AND $itemdesc_loc";
2169 my $sql = "SELECT SUM(amount) $taxwhere AND cust_bill_pkg.pkgnum = 0";
2171 my $x = &{$scalar_sql}($t, [ 'itemdesc', 'itemdesc' ], $sql );
2173 $taxes{$label}->{'tax'} += $x;
2176 "JOIN cust_credit_bill_pkg USING (billpkgnum,billpkgtaxratelocationnum)";
2178 "FROM cust_bill_pkg $addl_from $creditfrom $where AND $itemdesc_loc";
2180 $sql = "SELECT SUM(cust_credit_bill_pkg.amount) ".
2181 " $creditwhere AND cust_bill_pkg.pkgnum = 0";
2183 my $y = &{$scalar_sql}($t, [ 'itemdesc', 'itemdesc' ], $sql );
2185 $taxes{$label}->{'credit'} += $y;
2187 unless ( exists( $taxes{$baselabel} ) ) {
2189 $basetaxes{$baselabel}->{'label'} = $baselabel;
2190 $basetaxes{$baselabel}->{'url_param'} = "itemdesc=$baselabel";
2191 $basetaxes{$baselabel}->{'base'} = 1;
2195 $basetaxes{$baselabel}->{'tax'} += $x;
2196 $basetaxes{$baselabel}->{'credit'} += $y;
2200 # calculate customer-exemption for this tax
2201 # calculate package-exemption for this tax
2202 # calculate monthly exemption (texas tax) for this tax
2203 # count up all the cust_tax_exempt_pkg records associated with
2204 # the actual line items.
2211 $args{job}->update_statustext( "0,Sorted" );
2217 foreach my $tax ( sort { $a cmp $b } keys %taxes ) {
2218 my ($base, @trash) = split '~', $tax;
2219 my $basetax = delete( $basetaxes{$base} );
2221 if ( $basetax->{tax} == $taxes{$tax}->{tax} ) {
2222 $taxes{$tax}->{base} = 1;
2224 push @taxes, $basetax;
2227 push @taxes, $taxes{$tax};
2234 'credit' => $credit,
2239 my $dateagentlink = "begin=$args{beginning};end=$args{ending}";
2240 $dateagentlink .= ';agentnum='. $args{agentnum}
2241 if length($agentname);
2242 my $baselink = $args{p}. "search/cust_bill_pkg.cgi?vendortax=1;" .
2244 my $creditlink = $args{p}. "search/cust_credit_bill_pkg.html?$dateagentlink";
2246 print $report <<EOF;
2248 <% include("/elements/header.html", "$agentname Tax Report - ".
2250 ? time2str('%h %o %Y ', $args{beginning} )
2254 ( $args{ending} == 4294967295
2256 : time2str('%h %o %Y', $args{ending} )
2261 <% include('/elements/table-grid.html') %>
2264 <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
2265 <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
2266 <TH CLASS="grid" BGCOLOR="#cccccc">Tax invoiced</TH>
2267 <TH CLASS="grid" BGCOLOR="#cccccc"> </TH>
2268 <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
2269 <TH CLASS="grid" BGCOLOR="#cccccc">Tax credited</TH>
2273 my $bgcolor1 = '#eeeeee';
2274 my $bgcolor2 = '#ffffff';
2277 $count = scalar(@taxes);
2279 foreach my $tax ( @taxes ) {
2282 if ( time - $min_sec > $last ) {
2283 $args{job}->update_statustext( int( 100 * $calculated / $count ).
2290 if ( $bgcolor eq $bgcolor1 ) {
2291 $bgcolor = $bgcolor2;
2293 $bgcolor = $bgcolor1;
2297 if ( $tax->{'label'} ne 'Total' ) {
2298 $link = ';'. $tax->{'url_param'};
2301 print $report <<EOF;
2303 <TD CLASS="grid" BGCOLOR="<% '$bgcolor' %>"><% '$tax->{label}' %></TD>
2304 <% ($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
2305 <TD CLASS="grid" BGCOLOR="<% '$bgcolor' %>" ALIGN="right">
2306 <A HREF="<% '$baselink$link' %>;istax=1"><% '$money_char' %><% sprintf('%.2f', $tax->{'tax'} ) %></A>
2308 <% !($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
2309 <TD CLASS="grid" BGCOLOR="<% '$bgcolor' %>"></TD>
2310 <% ($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
2311 <TD CLASS="grid" BGCOLOR="<% '$bgcolor' %>" ALIGN="right">
2312 <A HREF="<% '$creditlink$link' %>;istax=1;iscredit=rate"><% '$money_char' %><% sprintf('%.2f', $tax->{'credit'} ) %></A>
2314 <% !($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
2319 print $report <<EOF;
2326 my $reportname = $report->filename;
2329 my $dropstring = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/report.';
2330 $reportname =~ s/^$dropstring//;
2332 my $reporturl = "%%%ROOTURL%%%/misc/queued_report?report=$reportname";
2333 die "<a href=$reporturl>view</a>\n";
2343 Highly specific to CCH taxes. This should arguably go in some kind of
2344 subclass (FS::tax_rate::CCH) with auto-reblessing, similar to part_pkg
2345 subclasses. But currently there aren't any other options, so.
2347 Mixing automatic and manual editing works poorly at present.
2349 Tax liability calculations take too long and arguably don't belong here.
2350 Tax liability report generation not entirely safe (escaped).
2352 Sparse documentation.
2356 L<FS::Record>, L<FS::cust_location>, L<FS::cust_bill>