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( qsearch qsearchs dbh );
12 use FS::cust_bill_pkg;
13 use FS::cust_tax_location;
14 use FS::part_pkg_taxrate;
17 @ISA = qw( FS::Record );
20 $me = '[FS::tax_rate]';
24 FS::tax_rate - Object methods for tax_rate objects
30 $record = new FS::tax_rate \%hash;
31 $record = new FS::tax_rate { 'column' => 'value' };
33 $error = $record->insert;
35 $error = $new_record->replace($old_record);
37 $error = $record->delete;
39 $error = $record->check;
43 An FS::tax_rate object represents a tax rate, defined by locale.
44 FS::tax_rate inherits from FS::Record. The following fields are
51 primary key (assigned automatically for new tax rates)
55 a geographic location code provided by a tax data vendor
63 a location code provided by a tax authority
67 a foreign key into FS::tax_class - the type of tax
68 referenced but FS::part_pkg_taxrate
71 the time after which the tax applies
79 second bracket percentage
83 the amount to which the tax applies (first bracket)
87 a cap on the amount of tax if a cap exists
91 percentage on out of jurisdiction purchases
95 second bracket percentage on out of jurisdiction purchases
99 one of the values in %tax_unittypes
103 amount of tax per unit
107 second bracket amount of tax per unit
111 the number of units to which the fee applies (first bracket)
115 the most units to which fees apply (first and second brackets)
119 a value from %tax_maxtypes indicating how brackets accumulate (i.e. monthly, per invoice, etc)
123 if defined, printed on invoices instead of "Tax"
127 a value from %tax_authorities
131 a value from %tax_basetypes indicating the tax basis
135 a value from %tax_passtypes indicating how the tax should displayed to the customer
139 'Y', 'N', or blank indicating the tax can be passed to the customer
143 if 'Y', this tax does not apply to setup fees
147 if 'Y', this tax does not apply to recurring fees
151 if 'Y', has been manually edited
161 Creates a new tax rate. To add the tax rate to the database, see L<"insert">.
165 sub table { 'tax_rate'; }
169 Adds this tax rate to the database. If there is an error, returns the error,
170 otherwise returns false.
174 Deletes this tax rate from the database. If there is an error, returns the
175 error, otherwise returns false.
177 =item replace OLD_RECORD
179 Replaces the OLD_RECORD with this one in the database. If there is an error,
180 returns the error, otherwise returns false.
184 Checks all fields to make sure this is a valid tax rate. If there is an error,
185 returns the error, otherwise returns false. Called by the insert and replace
193 foreach (qw( taxbase taxmax )) {
194 $self->$_(0) unless $self->$_;
197 $self->ut_numbern('taxnum')
198 || $self->ut_text('geocode')
199 || $self->ut_textn('data_vendor')
200 || $self->ut_textn('location')
201 || $self->ut_foreign_key('taxclassnum', 'tax_class', 'taxclassnum')
202 || $self->ut_numbern('effective_date')
203 || $self->ut_float('tax')
204 || $self->ut_floatn('excessrate')
205 || $self->ut_money('taxbase')
206 || $self->ut_money('taxmax')
207 || $self->ut_floatn('usetax')
208 || $self->ut_floatn('useexcessrate')
209 || $self->ut_numbern('unittype')
210 || $self->ut_floatn('fee')
211 || $self->ut_floatn('excessfee')
212 || $self->ut_floatn('feemax')
213 || $self->ut_numbern('maxtype')
214 || $self->ut_textn('taxname')
215 || $self->ut_numbern('taxauth')
216 || $self->ut_numbern('basetype')
217 || $self->ut_numbern('passtype')
218 || $self->ut_enum('passflag', [ '', 'Y', 'N' ])
219 || $self->ut_enum('setuptax', [ '', 'Y' ] )
220 || $self->ut_enum('recurtax', [ '', 'Y' ] )
221 || $self->ut_enum('manual', [ '', 'Y' ] )
222 || $self->SUPER::check
227 =item taxclass_description
229 Returns the human understandable value associated with the related
234 sub taxclass_description {
236 my $tax_class = qsearchs('tax_class', {'taxclassnum' => $self->taxclassnum });
237 $tax_class ? $tax_class->description : '';
242 Returns the human understandable value associated with the unittype column
246 %tax_unittypes = ( '0' => 'access line',
253 $tax_unittypes{$self->unittype};
258 Returns the human understandable value associated with the maxtype column
262 %tax_maxtypes = ( '0' => 'receipts per invoice',
263 '1' => 'receipts per item',
264 '2' => 'total utility charges per utility tax year',
265 '3' => 'total charges per utility tax year',
266 '4' => 'receipts per access line',
267 '9' => 'monthly receipts per location',
272 $tax_maxtypes{$self->maxtype};
277 Returns the human understandable value associated with the basetype column
281 %tax_basetypes = ( '0' => 'sale price',
282 '1' => 'gross receipts',
283 '2' => 'sales taxable telecom revenue',
284 '3' => 'minutes carried',
285 '4' => 'minutes billed',
286 '5' => 'gross operating revenue',
287 '6' => 'access line',
289 '8' => 'gross revenue',
290 '9' => 'portion gross receipts attributable to interstate service',
291 '10' => 'access line',
292 '11' => 'gross profits',
293 '12' => 'tariff rate',
299 $tax_basetypes{$self->basetype};
304 Returns the human understandable value associated with the taxauth column
308 %tax_authorities = ( '0' => 'federal',
313 '5' => 'county administered by state',
314 '6' => 'city administered by state',
315 '7' => 'city administered by county',
316 '8' => 'local administered by state',
317 '9' => 'local administered by county',
322 $tax_authorities{$self->taxauth};
327 Returns the human understandable value associated with the passtype column
331 %tax_passtypes = ( '0' => 'separate tax line',
332 '1' => 'separate surcharge line',
333 '2' => 'surcharge not separated',
334 '3' => 'included in base rate',
339 $tax_passtypes{$self->passtype};
342 =item taxline CUST_BILL_PKG|AMOUNT, ...
344 Returns a listref of a name and an amount of tax calculated for the list
345 of packages/amounts. If an error occurs, a message is returned as a scalar.
352 my $taxable_charged = 0;
353 my @cust_bill_pkg = grep { $taxable_charged += $_ unless ref; ref; } @_;
355 warn "calculating taxes for ". $self->taxnum. " on ".
356 join (",", map { $_->pkgnum } @cust_bill_pkg)
359 if ($self->passflag eq 'N') {
360 return "fatal: can't (yet) handle taxes not passed to the customer";
363 if ($self->maxtype != 0 && $self->maxtype != 9) {
364 return qq!fatal: can't (yet) handle tax with "!. $self->maxtype_name.
368 if ($self->maxtype == 9) {
369 return qq!fatal: can't (yet) handle tax with "!. $self->maxtype_name.
370 '" threshold'; # "texas" tax
373 if ($self->basetype != 0 && $self->basetype != 1 &&
374 $self->basetype != 6 && $self->basetype != 7 &&
375 $self->basetype != 14
377 return qq!fatal: can't (yet) handle tax with "!. $self->basetype_name.
381 my $name = $self->taxname;
382 $name = 'Other surcharges'
383 if ($self->passtype == 2);
386 unless ($self->setuptax =~ /^Y$/i) {
387 $taxable_charged += $_->setup foreach @cust_bill_pkg;
389 unless ($self->recurtax =~ /^Y$/i) {
390 $taxable_charged += $_->recur foreach @cust_bill_pkg;
393 my $taxable_units = 0;
394 unless ($self->recurtax =~ /^Y$/i) {
395 if ($self->unittype == 0) {
396 $taxable_units += $_->units foreach @cust_bill_pkg;
397 }elsif ($self->unittype == 1) {
398 return qq!fatal: can't (yet) handle fee with minute unit type!;
399 }elsif ($self->unittype == 2) {
402 return qq!fatal: can't (yet) handle unknown unit type in tax!.
408 # XXX insert exemption handling here
410 # the tax or fee is applied to taxbase or feebase and then
411 # the excessrate or excess fee is applied to taxmax or feemax
414 $amount += $taxable_charged * $self->tax;
415 $amount += $taxable_units * $self->fee;
417 warn "calculated taxes as [ $name, $amount ]\n"
420 return [$name, $amount];
424 =item tax_on_tax CUST_MAIN
426 Returns a list of taxes which are candidates for taxing taxes for the
427 given customer (see L<FS::cust_main>)
433 my $cust_main = shift;
435 warn "looking up taxes on tax ". $self->taxnum. " for customer ".
439 my $geocode = $cust_main->geocode($self->data_vendor);
443 my $extra_sql = ' AND ('.
444 join(' OR ', map{ 'geocode = '. $dbh->quote(substr($geocode, 0, $_)) }
449 my $order_by = 'ORDER BY taxclassnum, length(geocode) desc';
450 my $select = 'DISTINCT ON(taxclassnum) *';
452 # should qsearch preface columns with the table to facilitate joins?
453 my @taxclassnums = map { $_->taxclassnum }
454 qsearch( { 'table' => 'part_pkg_taxrate',
456 'hashref' => { 'data_vendor' => $self->data_vendor,
457 'taxclassnumtaxed' => $self->taxclassnum,
459 'extra_sql' => $extra_sql,
460 'order_by' => $order_by,
463 return () unless @taxclassnums;
466 "AND (". join(' OR ', map { "taxclassnum = $_" } @taxclassnums ). ")";
468 qsearch({ 'table' => 'tax_rate',
469 'hashref' => { 'geocode' => $geocode, },
470 'extra_sql' => $extra_sql,
486 my ($param, $job) = @_;
488 my $fh = $param->{filehandle};
489 my $format = $param->{'format'};
498 my ( $count, $last, $min_sec ) = (0, time, 5); #progressbar
501 while ( defined($line=<$fh>) );
506 if ( $format eq 'cch' || $format eq 'cch-update' ) {
507 @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax
508 excessrate effective_date taxauth taxtype taxcat taxname
509 usetax useexcessrate fee unittype feemax maxtype passflag
511 push @fields, 'actionflag' if $format eq 'cch-update';
516 $hash->{'actionflag'} ='I' if ($hash->{'data_vendor'} eq 'cch');
517 $hash->{'data_vendor'} ='cch';
518 $hash->{'effective_date'} = str2time($hash->{'effective_date'});
521 join(':', map{ $hash->{$_} } qw(taxtype taxcat) );
523 my %tax_class = ( 'data_vendor' => 'cch',
524 'taxclass' => $taxclassid,
527 my $tax_class = qsearchs( 'tax_class', \%tax_class );
528 return "Error updating tax rate: no tax class $taxclassid"
531 $hash->{'taxclassnum'} = $tax_class->taxclassnum;
533 foreach (qw( inoutcity inoutlocal taxtype taxcat )) {
537 my %passflagmap = ( '0' => '',
541 $hash->{'passflag'} = $passflagmap{$hash->{'passflag'}}
542 if exists $passflagmap{$hash->{'passflag'}};
544 foreach (keys %$hash) {
545 $hash->{$_} = substr($hash->{$_}, 0, 80)
546 if length($hash->{$_}) > 80;
549 my $actionflag = delete($hash->{'actionflag'});
550 if ($actionflag eq 'I') {
551 $insert{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = $hash;
552 }elsif ($actionflag eq 'D') {
553 $delete{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = $hash;
555 return "Unexpected action flag: ". $hash->{'actionflag'};
562 } elsif ( $format eq 'extended' ) {
563 die "unimplemented\n";
567 die "unknown format $format";
570 eval "use Text::CSV_XS;";
573 my $csv = new Text::CSV_XS;
577 local $SIG{HUP} = 'IGNORE';
578 local $SIG{INT} = 'IGNORE';
579 local $SIG{QUIT} = 'IGNORE';
580 local $SIG{TERM} = 'IGNORE';
581 local $SIG{TSTP} = 'IGNORE';
582 local $SIG{PIPE} = 'IGNORE';
584 my $oldAutoCommit = $FS::UID::AutoCommit;
585 local $FS::UID::AutoCommit = 0;
588 while ( defined($line=<$fh>) ) {
589 $csv->parse($line) or do {
590 $dbh->rollback if $oldAutoCommit;
591 return "can't parse: ". $csv->error_input();
594 if ( $job ) { # progress bar
595 if ( time - $min_sec > $last ) {
596 my $error = $job->update_statustext(
597 int( 100 * $imported / $count )
599 die $error if $error;
604 my @columns = $csv->fields();
606 my %tax_rate = ( 'data_vendor' => $format );
607 foreach my $field ( @fields ) {
608 $tax_rate{$field} = shift @columns;
610 if ( scalar( @columns ) ) {
611 $dbh->rollback if $oldAutoCommit;
612 return "Unexpected trailing columns in line (wrong format?): $line";
615 my $error = &{$hook}(\%tax_rate);
617 $dbh->rollback if $oldAutoCommit;
625 for (grep { !exists($delete{$_}) } keys %insert) {
626 if ( $job ) { # progress bar
627 if ( time - $min_sec > $last ) {
628 my $error = $job->update_statustext(
629 int( 100 * $imported / $count )
631 die $error if $error;
636 my $tax_rate = new FS::tax_rate( $insert{$_} );
637 my $error = $tax_rate->insert;
640 $dbh->rollback if $oldAutoCommit;
641 return "can't insert tax_rate for $line: $error";
647 for (grep { exists($delete{$_}) } keys %insert) {
648 if ( $job ) { # progress bar
649 if ( time - $min_sec > $last ) {
650 my $error = $job->update_statustext(
651 int( 100 * $imported / $count )
653 die $error if $error;
658 my $old = qsearchs( 'tax_rate', $delete{$_} );
660 $dbh->rollback if $oldAutoCommit;
662 return "can't find tax_rate to replace for: ".
663 #join(" ", map { "$_ => ". $old->{$_} } @fields);
664 join(" ", map { "$_ => ". $old->{$_} } keys(%$old) );
666 my $new = new FS::tax_rate( $insert{$_} );
667 $new->taxnum($old->taxnum);
668 my $error = $new->replace($old);
671 $dbh->rollback if $oldAutoCommit;
672 return "can't insert tax_rate for $line: $error";
679 for (grep { !exists($insert{$_}) } keys %delete) {
680 if ( $job ) { # progress bar
681 if ( time - $min_sec > $last ) {
682 my $error = $job->update_statustext(
683 int( 100 * $imported / $count )
685 die $error if $error;
690 my $tax_rate = qsearchs( 'tax_rate', $delete{$_} );
692 $dbh->rollback if $oldAutoCommit;
693 $tax_rate = $delete{$_};
694 return "can't find tax_rate to delete for: ".
695 #join(" ", map { "$_ => ". $tax_rate->{$_} } @fields);
696 join(" ", map { "$_ => ". $tax_rate->{$_} } keys(%$tax_rate) );
698 my $error = $tax_rate->delete;
701 $dbh->rollback if $oldAutoCommit;
702 return "can't insert tax_rate for $line: $error";
708 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
710 return "Empty file!" unless $imported;
718 Load an batch import as a queued JSRPC job
725 my $param = thaw(decode_base64(shift));
726 my $format = $param->{'format'}; #well... this is all cch specific
728 my $files = $param->{'uploaded_files'}
729 or die "No files provided.";
731 my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files;
733 if ($format eq 'cch') {
735 my $oldAutoCommit = $FS::UID::AutoCommit;
736 local $FS::UID::AutoCommit = 0;
740 my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import,
741 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
742 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
743 'DETAIL', 'detail', \&FS::tax_rate::batch_import,
745 while( scalar(@list) ) {
746 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
747 unless ($files{$file}) {
748 $error = "No $name supplied";
751 my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc;
752 my $filename = "$dir/". $files{$file};
753 open my $fh, "< $filename" or $error ||= "Can't open $name file: $!";
755 $error ||= &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
757 unlink $filename or warn "Can't delete $filename: $!";
761 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
764 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
767 }elsif ($format eq 'cch-update') {
769 my $oldAutoCommit = $FS::UID::AutoCommit;
770 local $FS::UID::AutoCommit = 0;
773 my @insert_list = ();
774 my @delete_list = ();
776 my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import,
777 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
778 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
780 my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc;
781 while( scalar(@list) ) {
782 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
783 unless ($files{$file}) {
784 $error = "No $name supplied";
787 my $filename = "$dir/". $files{$file};
788 open my $fh, "< $filename" or $error ||= "Can't open $name file $filename: $!";
789 unlink $filename or warn "Can't delete $filename: $!";
791 my $ifh = new File::Temp( TEMPLATE => "$name.insert.XXXXXXXX",
794 ) or die "can't open temp file: $!\n";
796 my $dfh = new File::Temp( TEMPLATE => "$name.delete.XXXXXXXX",
799 ) or die "can't open temp file: $!\n";
803 $handle = $ifh if $_ =~ /"I"\s*$/;
804 $handle = $dfh if $_ =~ /"D"\s*$/;
806 $error = "bad input line: $_" unless $handle;
815 push @insert_list, $name, $ifh->filename, $import_sub;
816 unshift @delete_list, $name, $dfh->filename, $import_sub;
819 while( scalar(@insert_list) ) {
820 my ($name, $file, $import_sub) =
821 (shift @insert_list, shift @insert_list, shift @insert_list);
823 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
825 &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
827 unlink $file or warn "Can't delete $file: $!";
830 $error = "No DETAIL supplied"
831 unless ($files{detail});
832 open my $fh, "< $dir/". $files{detail}
833 or $error ||= "Can't open DETAIL file: $!";
835 &FS::tax_rate::batch_import({ 'filehandle' => $fh, 'format' => $format },
838 unlink "$dir/". $files{detail} or warn "Can't delete $files{detail}: $!"
841 while( scalar(@delete_list) ) {
842 my ($name, $file, $import_sub) =
843 (shift @delete_list, shift @delete_list, shift @delete_list);
845 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
847 &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
849 unlink $file or warn "Can't delete $file: $!";
853 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
856 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
860 die "Unknown format: $format";
869 Mixing automatic and manual editing works poorly at present.
873 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base