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 warn "calculating taxes for ". $self->taxnum. " on ".
353 join (",", map { $_->pkgnum } @cust_bill_pkg)
356 if ($self->passflag eq 'N') {
357 return "fatal: can't (yet) handle taxes not passed to the customer";
360 if ($self->maxtype != 0 && $self->maxtype != 9) {
361 return qq!fatal: can't (yet) handle tax with "!. $self->maxtype_name.
365 if ($self->maxtype == 9) {
366 return qq!fatal: can't (yet) handle tax with "!. $self->maxtype_name.
367 '" threshold'; # "texas" tax
370 if ($self->basetype != 0 && $self->basetype != 1 &&
371 $self->basetype != 6 && $self->basetype != 7 &&
372 $self->basetype != 14
374 return qq!fatal: can't (yet) handle tax with "!. $self->basetype_name.
378 my $name = $self->taxname;
379 $name = 'Other surcharges'
380 if ($self->passtype == 2);
383 my $taxable_charged = 0;
384 unless ($self->setuptax =~ /^Y$/i) {
385 $taxable_charged += $_->setup foreach @cust_bill_pkg;
387 unless ($self->recurtax =~ /^Y$/i) {
388 $taxable_charged += $_->recur foreach @cust_bill_pkg;
391 my $taxable_units = 0;
392 unless ($self->recurtax =~ /^Y$/i) {
393 if ($self->unittype == 0) {
394 $taxable_units += $_->units foreach @cust_bill_pkg;
395 }elsif ($self->unittype == 1) {
396 return qq!fatal: can't (yet) handle fee with minute unit type!;
397 }elsif ($self->unittype == 2) {
400 return qq!fatal: can't (yet) handle unknown unit type in tax!.
406 # XXX insert exemption handling here
408 # the tax or fee is applied to taxbase or feebase and then
409 # the excessrate or excess fee is applied to taxmax or feemax
412 $amount += $taxable_charged * $self->tax;
413 $amount += $taxable_units * $self->fee;
415 warn "calculated taxes as [ $name, $amount ]\n"
418 return [$name, $amount];
433 my ($param, $job) = @_;
435 my $fh = $param->{filehandle};
436 my $format = $param->{'format'};
445 my ( $count, $last, $min_sec ) = (0, time, 5); #progressbar
448 while ( defined($line=<$fh>) );
453 if ( $format eq 'cch' || $format eq 'cch-update' ) {
454 @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax
455 excessrate effective_date taxauth taxtype taxcat taxname
456 usetax useexcessrate fee unittype feemax maxtype passflag
458 push @fields, 'actionflag' if $format eq 'cch-update';
463 $hash->{'actionflag'} ='I' if ($hash->{'data_vendor'} eq 'cch');
464 $hash->{'data_vendor'} ='cch';
465 $hash->{'effective_date'} = str2time($hash->{'effective_date'});
468 join(':', map{ $hash->{$_} } qw(taxtype taxcat) );
470 my %tax_class = ( 'data_vendor' => 'cch',
471 'taxclass' => $taxclassid,
474 my $tax_class = qsearchs( 'tax_class', \%tax_class );
475 return "Error updating tax rate: no tax class $taxclassid"
478 $hash->{'taxclassnum'} = $tax_class->taxclassnum;
480 foreach (qw( inoutcity inoutlocal taxtype taxcat )) {
484 my %passflagmap = ( '0' => '',
488 $hash->{'passflag'} = $passflagmap{$hash->{'passflag'}}
489 if exists $passflagmap{$hash->{'passflag'}};
491 foreach (keys %$hash) {
492 $hash->{$_} = substr($hash->{$_}, 0, 80)
493 if length($hash->{$_}) > 80;
496 my $actionflag = delete($hash->{'actionflag'});
497 if ($actionflag eq 'I') {
498 $insert{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = $hash;
499 }elsif ($actionflag eq 'D') {
500 $delete{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = $hash;
502 return "Unexpected action flag: ". $hash->{'actionflag'};
509 } elsif ( $format eq 'extended' ) {
510 die "unimplemented\n";
514 die "unknown format $format";
517 eval "use Text::CSV_XS;";
520 my $csv = new Text::CSV_XS;
524 local $SIG{HUP} = 'IGNORE';
525 local $SIG{INT} = 'IGNORE';
526 local $SIG{QUIT} = 'IGNORE';
527 local $SIG{TERM} = 'IGNORE';
528 local $SIG{TSTP} = 'IGNORE';
529 local $SIG{PIPE} = 'IGNORE';
531 my $oldAutoCommit = $FS::UID::AutoCommit;
532 local $FS::UID::AutoCommit = 0;
535 while ( defined($line=<$fh>) ) {
536 $csv->parse($line) or do {
537 $dbh->rollback if $oldAutoCommit;
538 return "can't parse: ". $csv->error_input();
541 if ( $job ) { # progress bar
542 if ( time - $min_sec > $last ) {
543 my $error = $job->update_statustext(
544 int( 100 * $imported / $count )
546 die $error if $error;
551 my @columns = $csv->fields();
553 my %tax_rate = ( 'data_vendor' => $format );
554 foreach my $field ( @fields ) {
555 $tax_rate{$field} = shift @columns;
557 if ( scalar( @columns ) ) {
558 $dbh->rollback if $oldAutoCommit;
559 return "Unexpected trailing columns in line (wrong format?): $line";
562 my $error = &{$hook}(\%tax_rate);
564 $dbh->rollback if $oldAutoCommit;
572 for (grep { !exists($delete{$_}) } keys %insert) {
573 if ( $job ) { # progress bar
574 if ( time - $min_sec > $last ) {
575 my $error = $job->update_statustext(
576 int( 100 * $imported / $count )
578 die $error if $error;
583 my $tax_rate = new FS::tax_rate( $insert{$_} );
584 my $error = $tax_rate->insert;
587 $dbh->rollback if $oldAutoCommit;
588 return "can't insert tax_rate for $line: $error";
594 for (grep { exists($delete{$_}) } keys %insert) {
595 if ( $job ) { # progress bar
596 if ( time - $min_sec > $last ) {
597 my $error = $job->update_statustext(
598 int( 100 * $imported / $count )
600 die $error if $error;
605 my $old = qsearchs( 'tax_rate', $delete{$_} );
607 $dbh->rollback if $oldAutoCommit;
609 return "can't find tax_rate to replace for: ".
610 #join(" ", map { "$_ => ". $old->{$_} } @fields);
611 join(" ", map { "$_ => ". $old->{$_} } keys(%$old) );
613 my $new = new FS::tax_rate( $insert{$_} );
614 $new->taxnum($old->taxnum);
615 my $error = $new->replace($old);
618 $dbh->rollback if $oldAutoCommit;
619 return "can't insert tax_rate for $line: $error";
626 for (grep { !exists($insert{$_}) } keys %delete) {
627 if ( $job ) { # progress bar
628 if ( time - $min_sec > $last ) {
629 my $error = $job->update_statustext(
630 int( 100 * $imported / $count )
632 die $error if $error;
637 my $tax_rate = qsearchs( 'tax_rate', $delete{$_} );
639 $dbh->rollback if $oldAutoCommit;
640 $tax_rate = $delete{$_};
641 return "can't find tax_rate to delete for: ".
642 #join(" ", map { "$_ => ". $tax_rate->{$_} } @fields);
643 join(" ", map { "$_ => ". $tax_rate->{$_} } keys(%$tax_rate) );
645 my $error = $tax_rate->delete;
648 $dbh->rollback if $oldAutoCommit;
649 return "can't insert tax_rate for $line: $error";
655 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
657 return "Empty file!" unless $imported;
665 Load an batch import as a queued JSRPC job
672 my $param = thaw(decode_base64(shift));
673 my $format = $param->{'format'}; #well... this is all cch specific
675 my $files = $param->{'uploaded_files'}
676 or die "No files provided.";
678 my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files;
680 if ($format eq 'cch') {
682 my $oldAutoCommit = $FS::UID::AutoCommit;
683 local $FS::UID::AutoCommit = 0;
687 my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import,
688 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
689 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
690 'DETAIL', 'detail', \&FS::tax_rate::batch_import,
692 while( scalar(@list) ) {
693 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
694 unless ($files{$file}) {
695 $error = "No $name supplied";
698 my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc;
699 my $filename = "$dir/". $files{$file};
700 open my $fh, "< $filename" or $error ||= "Can't open $name file: $!";
702 $error ||= &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
704 unlink $filename or warn "Can't delete $filename: $!";
708 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
711 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
714 }elsif ($format eq 'cch-update') {
716 my $oldAutoCommit = $FS::UID::AutoCommit;
717 local $FS::UID::AutoCommit = 0;
720 my @insert_list = ();
721 my @delete_list = ();
723 my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import,
724 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
725 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
727 my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc;
728 while( scalar(@list) ) {
729 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
730 unless ($files{$file}) {
731 $error = "No $name supplied";
734 my $filename = "$dir/". $files{$file};
735 open my $fh, "< $filename" or $error ||= "Can't open $name file $filename: $!";
736 unlink $filename or warn "Can't delete $filename: $!";
738 my $ifh = new File::Temp( TEMPLATE => "$name.insert.XXXXXXXX",
741 ) or die "can't open temp file: $!\n";
743 my $dfh = new File::Temp( TEMPLATE => "$name.delete.XXXXXXXX",
746 ) or die "can't open temp file: $!\n";
750 $handle = $ifh if $_ =~ /"I"\s*$/;
751 $handle = $dfh if $_ =~ /"D"\s*$/;
753 $error = "bad input line: $_" unless $handle;
762 push @insert_list, $name, $ifh->filename, $import_sub;
763 unshift @delete_list, $name, $dfh->filename, $import_sub;
766 while( scalar(@insert_list) ) {
767 my ($name, $file, $import_sub) =
768 (shift @insert_list, shift @insert_list, shift @insert_list);
770 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
772 &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
774 unlink $file or warn "Can't delete $file: $!";
777 $error = "No DETAIL supplied"
778 unless ($files{detail});
779 open my $fh, "< $dir/". $files{detail}
780 or $error ||= "Can't open DETAIL file: $!";
782 &FS::tax_rate::batch_import({ 'filehandle' => $fh, 'format' => $format },
785 unlink "$dir/". $files{detail} or warn "Can't delete $files{detail}: $!"
788 while( scalar(@delete_list) ) {
789 my ($name, $file, $import_sub) =
790 (shift @delete_list, shift @delete_list, shift @delete_list);
792 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
794 &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
796 unlink $file or warn "Can't delete $file: $!";
800 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
803 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
807 die "Unknown format: $format";
816 Mixing automatic and manual editing works poorly at present.
820 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base