4 use vars qw( @ISA $DEBUG $me
5 %tax_unittypes %tax_maxtypes %tax_basetypes %tax_authorities
8 use Storable qw( thaw );
10 use FS::Record qw( qsearchs dbh );
12 use FS::cust_bill_pkg;
13 use FS::cust_tax_location;
14 use FS::part_pkg_taxrate;
16 @ISA = qw( FS::Record );
19 $me = '[FS::tax_rate]';
23 FS::tax_rate - Object methods for tax_rate objects
29 $record = new FS::tax_rate \%hash;
30 $record = new FS::tax_rate { 'column' => 'value' };
32 $error = $record->insert;
34 $error = $new_record->replace($old_record);
36 $error = $record->delete;
38 $error = $record->check;
42 An FS::tax_rate object represents a tax rate, defined by locale.
43 FS::tax_rate inherits from FS::Record. The following fields are
50 primary key (assigned automatically for new tax rates)
54 a geographic location code provided by a tax data vendor
62 a location code provided by a tax authority
66 a foreign key into FS::tax_class - the type of tax
67 referenced but FS::part_pkg_taxrate
70 the time after which the tax applies
78 second bracket percentage
82 the amount to which the tax applies (first bracket)
86 a cap on the amount of tax if a cap exists
90 percentage on out of jurisdiction purchases
94 second bracket percentage on out of jurisdiction purchases
98 one of the values in %tax_unittypes
102 amount of tax per unit
106 second bracket amount of tax per unit
110 the number of units to which the fee applies (first bracket)
114 the most units to which fees apply (first and second brackets)
118 a value from %tax_maxtypes indicating how brackets accumulate (i.e. monthly, per invoice, etc)
122 if defined, printed on invoices instead of "Tax"
126 a value from %tax_authorities
130 a value from %tax_basetypes indicating the tax basis
134 a value from %tax_passtypes indicating how the tax should displayed to the customer
138 'Y', 'N', or blank indicating the tax can be passed to the customer
142 if 'Y', this tax does not apply to setup fees
146 if 'Y', this tax does not apply to recurring fees
150 if 'Y', has been manually edited
160 Creates a new tax rate. To add the tax rate to the database, see L<"insert">.
164 sub table { 'tax_rate'; }
168 Adds this tax rate to the database. If there is an error, returns the error,
169 otherwise returns false.
173 Deletes this tax rate from the database. If there is an error, returns the
174 error, otherwise returns false.
176 =item replace OLD_RECORD
178 Replaces the OLD_RECORD with this one in the database. If there is an error,
179 returns the error, otherwise returns false.
183 Checks all fields to make sure this is a valid tax rate. If there is an error,
184 returns the error, otherwise returns false. Called by the insert and replace
192 foreach (qw( taxbase taxmax )) {
193 $self->$_(0) unless $self->$_;
196 $self->ut_numbern('taxnum')
197 || $self->ut_text('geocode')
198 || $self->ut_textn('data_vendor')
199 || $self->ut_textn('location')
200 || $self->ut_foreign_key('taxclassnum', 'tax_class', 'taxclassnum')
201 || $self->ut_numbern('effective_date')
202 || $self->ut_float('tax')
203 || $self->ut_floatn('excessrate')
204 || $self->ut_money('taxbase')
205 || $self->ut_money('taxmax')
206 || $self->ut_floatn('usetax')
207 || $self->ut_floatn('useexcessrate')
208 || $self->ut_numbern('unittype')
209 || $self->ut_floatn('fee')
210 || $self->ut_floatn('excessfee')
211 || $self->ut_floatn('feemax')
212 || $self->ut_numbern('maxtype')
213 || $self->ut_textn('taxname')
214 || $self->ut_numbern('taxauth')
215 || $self->ut_numbern('basetype')
216 || $self->ut_numbern('passtype')
217 || $self->ut_enum('passflag', [ '', 'Y', 'N' ])
218 || $self->ut_enum('setuptax', [ '', 'Y' ] )
219 || $self->ut_enum('recurtax', [ '', 'Y' ] )
220 || $self->ut_enum('manual', [ '', 'Y' ] )
221 || $self->SUPER::check
226 =item taxclass_description
228 Returns the human understandable value associated with the related
233 sub taxclass_description {
235 my $tax_class = qsearchs('tax_class', {'taxclassnum' => $self->taxclassnum });
236 $tax_class ? $tax_class->description : '';
241 Returns the human understandable value associated with the unittype column
245 %tax_unittypes = ( '0' => 'access line',
252 $tax_unittypes{$self->unittype};
257 Returns the human understandable value associated with the maxtype column
261 %tax_maxtypes = ( '0' => 'receipts per invoice',
262 '1' => 'receipts per item',
263 '2' => 'total utility charges per utility tax year',
264 '3' => 'total charges per utility tax year',
265 '4' => 'receipts per access line',
266 '9' => 'monthly receipts per location',
271 $tax_maxtypes{$self->maxtype};
276 Returns the human understandable value associated with the basetype column
280 %tax_basetypes = ( '0' => 'sale price',
281 '1' => 'gross receipts',
282 '2' => 'sales taxable telecom revenue',
283 '3' => 'minutes carried',
284 '4' => 'minutes billed',
285 '5' => 'gross operating revenue',
286 '6' => 'access line',
288 '8' => 'gross revenue',
289 '9' => 'portion gross receipts attributable to interstate service',
290 '10' => 'access line',
291 '11' => 'gross profits',
292 '12' => 'tariff rate',
298 $tax_basetypes{$self->basetype};
303 Returns the human understandable value associated with the taxauth column
307 %tax_authorities = ( '0' => 'federal',
312 '5' => 'county administered by state',
313 '6' => 'city administered by state',
314 '7' => 'city administered by county',
315 '8' => 'local administered by state',
316 '9' => 'local administered by county',
321 $tax_authorities{$self->taxauth};
326 Returns the human understandable value associated with the passtype column
330 %tax_passtypes = ( '0' => 'separate tax line',
331 '1' => 'separate surcharge line',
332 '2' => 'surcharge not separated',
333 '3' => 'included in base rate',
338 $tax_passtypes{$self->passtype};
341 =item taxline CUST_BILL_PKG, ...
343 Returns a listref of a name and an amount of tax calculated for the list
344 of packages. If an error occurs, a message is returned as a scalar.
350 my @cust_bill_pkg = @_;
352 if ($self->passflag eq 'N') {
353 return "fatal: can't (yet) handle taxes not passed to the customer";
356 if ($self->maxtype != 0 && $self->maxtype != 9) {
357 return qq!fatal: can't (yet) handle tax with "!. $self->maxtype_name.
361 if ($self->maxtype == 9) {
362 return qq!fatal: can't (yet) handle tax with "!. $self->maxtype_name.
363 '" threshold'; # "texas" tax
366 if ($self->basetype != 0 && $self->basetype != 1 &&
367 $self->basetype != 6 && $self->basetype != 7 &&
368 $self->basetype != 14
370 return qq!fatal: can't (yet) handle tax with "!. $self->basetype_name.
374 my $name = $self->taxname;
375 $name = 'Other surcharges'
376 if ($self->passtype == 2);
379 my $taxable_charged = 0;
380 unless ($self->setuptax =~ /^Y$/i) {
381 $taxable_charged += $_->setup foreach @cust_bill_pkg;
383 unless ($self->recurtax =~ /^Y$/i) {
384 $taxable_charged += $_->recur foreach @cust_bill_pkg;
387 my $taxable_units = 0;
388 unless ($self->recurtax =~ /^Y$/i) {
389 $taxable_units += $_->units foreach @cust_bill_pkg;
393 # XXX insert exemption handling here
395 # the tax or fee is applied to taxbase or feebase and then
396 # the excessrate or excess fee is applied to taxmax or feemax
399 $amount += $taxable_charged * $self->tax;
400 $amount += $taxable_units * $self->fee;
402 return [$name, $amount];
417 my ($param, $job) = @_;
419 my $fh = $param->{filehandle};
420 my $format = $param->{'format'};
429 my ( $count, $last, $min_sec ) = (0, time, 5); #progressbar
432 while ( defined($line=<$fh>) );
437 if ( $format eq 'cch' || $format eq 'cch-update' ) {
438 @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax
439 excessrate effective_date taxauth taxtype taxcat taxname
440 usetax useexcessrate fee unittype feemax maxtype passflag
442 push @fields, 'actionflag' if $format eq 'cch-update';
447 $hash->{'actionflag'} ='I' if ($hash->{'data_vendor'} eq 'cch');
448 $hash->{'data_vendor'} ='cch';
449 $hash->{'effective_date'} = str2time($hash->{'effective_date'});
452 join(':', map{ $hash->{$_} } qw(taxtype taxcat) );
454 my %tax_class = ( 'data_vendor' => 'cch',
455 'taxclass' => $taxclassid,
458 my $tax_class = qsearchs( 'tax_class', \%tax_class );
459 return "Error updating tax rate: no tax class $taxclassid"
462 $hash->{'taxclassnum'} = $tax_class->taxclassnum;
464 foreach (qw( inoutcity inoutlocal taxtype taxcat )) {
468 my %passflagmap = ( '0' => '',
472 $hash->{'passflag'} = $passflagmap{$hash->{'passflag'}}
473 if exists $passflagmap{$hash->{'passflag'}};
475 foreach (keys %$hash) {
476 $hash->{$_} = substr($hash->{$_}, 0, 80)
477 if length($hash->{$_}) > 80;
480 my $actionflag = delete($hash->{'actionflag'});
481 if ($actionflag eq 'I') {
482 $insert{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = $hash;
483 }elsif ($actionflag eq 'D') {
484 $delete{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = $hash;
486 return "Unexpected action flag: ". $hash->{'actionflag'};
493 } elsif ( $format eq 'extended' ) {
494 die "unimplemented\n";
498 die "unknown format $format";
501 eval "use Text::CSV_XS;";
504 my $csv = new Text::CSV_XS;
508 local $SIG{HUP} = 'IGNORE';
509 local $SIG{INT} = 'IGNORE';
510 local $SIG{QUIT} = 'IGNORE';
511 local $SIG{TERM} = 'IGNORE';
512 local $SIG{TSTP} = 'IGNORE';
513 local $SIG{PIPE} = 'IGNORE';
515 my $oldAutoCommit = $FS::UID::AutoCommit;
516 local $FS::UID::AutoCommit = 0;
519 while ( defined($line=<$fh>) ) {
520 $csv->parse($line) or do {
521 $dbh->rollback if $oldAutoCommit;
522 return "can't parse: ". $csv->error_input();
525 if ( $job ) { # progress bar
526 if ( time - $min_sec > $last ) {
527 my $error = $job->update_statustext(
528 int( 100 * $imported / $count )
530 die $error if $error;
535 my @columns = $csv->fields();
537 my %tax_rate = ( 'data_vendor' => $format );
538 foreach my $field ( @fields ) {
539 $tax_rate{$field} = shift @columns;
541 if ( scalar( @columns ) ) {
542 $dbh->rollback if $oldAutoCommit;
543 return "Unexpected trailing columns in line (wrong format?): $line";
546 my $error = &{$hook}(\%tax_rate);
548 $dbh->rollback if $oldAutoCommit;
556 for (grep { !exists($delete{$_}) } keys %insert) {
557 if ( $job ) { # progress bar
558 if ( time - $min_sec > $last ) {
559 my $error = $job->update_statustext(
560 int( 100 * $imported / $count )
562 die $error if $error;
567 my $tax_rate = new FS::tax_rate( $insert{$_} );
568 my $error = $tax_rate->insert;
571 $dbh->rollback if $oldAutoCommit;
572 return "can't insert tax_rate for $line: $error";
578 for (grep { exists($delete{$_}) } keys %insert) {
579 if ( $job ) { # progress bar
580 if ( time - $min_sec > $last ) {
581 my $error = $job->update_statustext(
582 int( 100 * $imported / $count )
584 die $error if $error;
589 my $old = qsearchs( 'tax_rate', $delete{$_} );
591 $dbh->rollback if $oldAutoCommit;
593 return "can't find tax_rate to replace for: ".
594 #join(" ", map { "$_ => ". $old->{$_} } @fields);
595 join(" ", map { "$_ => ". $old->{$_} } keys(%$old) );
597 my $new = new FS::tax_rate( $insert{$_} );
598 $new->taxnum($old->taxnum);
599 my $error = $new->replace($old);
602 $dbh->rollback if $oldAutoCommit;
603 return "can't insert tax_rate for $line: $error";
610 for (grep { !exists($insert{$_}) } keys %delete) {
611 if ( $job ) { # progress bar
612 if ( time - $min_sec > $last ) {
613 my $error = $job->update_statustext(
614 int( 100 * $imported / $count )
616 die $error if $error;
621 my $tax_rate = qsearchs( 'tax_rate', $delete{$_} );
623 $dbh->rollback if $oldAutoCommit;
624 $tax_rate = $delete{$_};
625 return "can't find tax_rate to delete for: ".
626 #join(" ", map { "$_ => ". $tax_rate->{$_} } @fields);
627 join(" ", map { "$_ => ". $tax_rate->{$_} } keys(%$tax_rate) );
629 my $error = $tax_rate->delete;
632 $dbh->rollback if $oldAutoCommit;
633 return "can't insert tax_rate for $line: $error";
639 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
641 return "Empty file!" unless $imported;
649 Load an batch import as a queued JSRPC job
656 my $param = thaw(decode_base64(shift));
657 my $format = $param->{'format'}; #well... this is all cch specific
659 my $files = $param->{'uploaded_files'}
660 or die "No files provided.";
662 my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files;
664 if ($format eq 'cch') {
666 my $oldAutoCommit = $FS::UID::AutoCommit;
667 local $FS::UID::AutoCommit = 0;
671 my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import,
672 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
673 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
674 'DETAIL', 'detail', \&FS::tax_rate::batch_import,
676 while( scalar(@list) ) {
677 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
678 unless ($files{$file}) {
679 $error = "No $name supplied";
682 my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc;
683 my $filename = "$dir/". $files{$file};
684 open my $fh, "< $filename" or $error ||= "Can't open $name file: $!";
686 $error ||= &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
688 unlink $filename or warn "Can't delete $filename: $!";
692 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
695 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
698 }elsif ($format eq 'cch-update') {
700 my $oldAutoCommit = $FS::UID::AutoCommit;
701 local $FS::UID::AutoCommit = 0;
704 my @insert_list = ();
705 my @delete_list = ();
707 my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import,
708 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
709 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
711 my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc;
712 while( scalar(@list) ) {
713 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
714 unless ($files{$file}) {
715 $error = "No $name supplied";
718 my $filename = "$dir/". $files{$file};
719 open my $fh, "< $filename" or $error ||= "Can't open $name file $filename: $!";
720 unlink $filename or warn "Can't delete $filename: $!";
722 my $ifh = new File::Temp( TEMPLATE => "$name.insert.XXXXXXXX",
725 ) or die "can't open temp file: $!\n";
727 my $dfh = new File::Temp( TEMPLATE => "$name.delete.XXXXXXXX",
730 ) or die "can't open temp file: $!\n";
734 $handle = $ifh if $_ =~ /"I"\s*$/;
735 $handle = $dfh if $_ =~ /"D"\s*$/;
737 $error = "bad input line: $_" unless $handle;
746 push @insert_list, $name, $ifh->filename, $import_sub;
747 unshift @delete_list, $name, $dfh->filename, $import_sub;
750 while( scalar(@insert_list) ) {
751 my ($name, $file, $import_sub) =
752 (shift @insert_list, shift @insert_list, shift @insert_list);
754 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
756 &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
758 unlink $file or warn "Can't delete $file: $!";
761 $error = "No DETAIL supplied"
762 unless ($files{detail});
763 open my $fh, "< $dir/". $files{detail}
764 or $error ||= "Can't open DETAIL file: $!";
766 &FS::tax_rate::batch_import({ 'filehandle' => $fh, 'format' => $format },
769 unlink "$dir/". $files{detail} or warn "Can't delete $files{detail}: $!"
772 while( scalar(@delete_list) ) {
773 my ($name, $file, $import_sub) =
774 (shift @delete_list, shift @delete_list, shift @delete_list);
776 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
778 &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
780 unlink $file or warn "Can't delete $file: $!";
784 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
787 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
791 die "Unknown format: $format";
800 Mixing automatic and manual editing works poorly at present.
804 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base