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_snumbern('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->ut_enum('disabled', [ '', 'Y' ] )
223 || $self->SUPER::check
228 =item taxclass_description
230 Returns the human understandable value associated with the related
235 sub taxclass_description {
237 my $tax_class = qsearchs('tax_class', {'taxclassnum' => $self->taxclassnum });
238 $tax_class ? $tax_class->description : '';
243 Returns the human understandable value associated with the unittype column
247 %tax_unittypes = ( '0' => 'access line',
254 $tax_unittypes{$self->unittype};
259 Returns the human understandable value associated with the maxtype column
263 %tax_maxtypes = ( '0' => 'receipts per invoice',
264 '1' => 'receipts per item',
265 '2' => 'total utility charges per utility tax year',
266 '3' => 'total charges per utility tax year',
267 '4' => 'receipts per access line',
268 '9' => 'monthly receipts per location',
273 $tax_maxtypes{$self->maxtype};
278 Returns the human understandable value associated with the basetype column
282 %tax_basetypes = ( '0' => 'sale price',
283 '1' => 'gross receipts',
284 '2' => 'sales taxable telecom revenue',
285 '3' => 'minutes carried',
286 '4' => 'minutes billed',
287 '5' => 'gross operating revenue',
288 '6' => 'access line',
290 '8' => 'gross revenue',
291 '9' => 'portion gross receipts attributable to interstate service',
292 '10' => 'access line',
293 '11' => 'gross profits',
294 '12' => 'tariff rate',
300 $tax_basetypes{$self->basetype};
305 Returns the human understandable value associated with the taxauth column
309 %tax_authorities = ( '0' => 'federal',
314 '5' => 'county administered by state',
315 '6' => 'city administered by state',
316 '7' => 'city administered by county',
317 '8' => 'local administered by state',
318 '9' => 'local administered by county',
323 $tax_authorities{$self->taxauth};
328 Returns the human understandable value associated with the passtype column
332 %tax_passtypes = ( '0' => 'separate tax line',
333 '1' => 'separate surcharge line',
334 '2' => 'surcharge not separated',
335 '3' => 'included in base rate',
340 $tax_passtypes{$self->passtype};
343 =item taxline CUST_BILL_PKG|AMOUNT, ...
345 Returns a listref of a name and an amount of tax calculated for the list
346 of packages/amounts. If an error occurs, a message is returned as a scalar.
353 my $name = $self->taxname;
354 $name = 'Other surcharges'
355 if ($self->passtype == 2);
358 return [$name, $amount] # we always know how to handle disabled taxes
361 my $taxable_charged = 0;
362 my @cust_bill_pkg = grep { $taxable_charged += $_ unless ref; ref; } @_;
364 warn "calculating taxes for ". $self->taxnum. " on ".
365 join (",", map { $_->pkgnum } @cust_bill_pkg)
368 if ($self->passflag eq 'N') {
369 return "fatal: can't (yet) handle taxes not passed to the customer";
372 if ($self->maxtype != 0 && $self->maxtype != 9) {
373 return qq!fatal: can't (yet) handle tax with "!. $self->maxtype_name.
377 if ($self->maxtype == 9) {
378 return qq!fatal: can't (yet) handle tax with "!. $self->maxtype_name.
379 '" threshold'; # "texas" tax
382 if ($self->basetype != 0 && $self->basetype != 1 &&
383 $self->basetype != 6 && $self->basetype != 7 &&
384 $self->basetype != 14
386 return qq!fatal: can't (yet) handle tax with "!. $self->basetype_name.
390 unless ($self->setuptax =~ /^Y$/i) {
391 $taxable_charged += $_->setup foreach @cust_bill_pkg;
393 unless ($self->recurtax =~ /^Y$/i) {
394 $taxable_charged += $_->recur foreach @cust_bill_pkg;
397 my $taxable_units = 0;
398 unless ($self->recurtax =~ /^Y$/i) {
399 if ($self->unittype == 0) {
401 foreach (@cust_bill_pkg) {
402 $taxable_units += $_->units
403 unless $seen{$_->pkgnum};
406 }elsif ($self->unittype == 1) {
407 return qq!fatal: can't (yet) handle fee with minute unit type!;
408 }elsif ($self->unittype == 2) {
411 return qq!fatal: can't (yet) handle unknown unit type in tax!.
417 # XXX insert exemption handling here
419 # the tax or fee is applied to taxbase or feebase and then
420 # the excessrate or excess fee is applied to taxmax or feemax
423 $amount += $taxable_charged * $self->tax;
424 $amount += $taxable_units * $self->fee;
426 warn "calculated taxes as [ $name, $amount ]\n"
429 return [$name, $amount];
433 =item tax_on_tax CUST_MAIN
435 Returns a list of taxes which are candidates for taxing taxes for the
436 given customer (see L<FS::cust_main>)
442 my $cust_main = shift;
444 warn "looking up taxes on tax ". $self->taxnum. " for customer ".
448 my $geocode = $cust_main->geocode($self->data_vendor);
452 my $extra_sql = ' AND ('.
453 join(' OR ', map{ 'geocode = '. $dbh->quote(substr($geocode, 0, $_)) }
458 my $order_by = 'ORDER BY taxclassnum, length(geocode) desc';
459 my $select = 'DISTINCT ON(taxclassnum) *';
461 # should qsearch preface columns with the table to facilitate joins?
462 my @taxclassnums = map { $_->taxclassnum }
463 qsearch( { 'table' => 'part_pkg_taxrate',
465 'hashref' => { 'data_vendor' => $self->data_vendor,
466 'taxclassnumtaxed' => $self->taxclassnum,
468 'extra_sql' => $extra_sql,
469 'order_by' => $order_by,
472 return () unless @taxclassnums;
475 "AND (". join(' OR ', map { "taxclassnum = $_" } @taxclassnums ). ")";
477 qsearch({ 'table' => 'tax_rate',
478 'hashref' => { 'geocode' => $geocode, },
479 'extra_sql' => $extra_sql,
495 my ($param, $job) = @_;
497 my $fh = $param->{filehandle};
498 my $format = $param->{'format'};
507 my ( $count, $last, $min_sec ) = (0, time, 5); #progressbar
510 while ( defined($line=<$fh>) );
515 if ( $format eq 'cch' || $format eq 'cch-update' ) {
516 @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax
517 excessrate effective_date taxauth taxtype taxcat taxname
518 usetax useexcessrate fee unittype feemax maxtype passflag
520 push @fields, 'actionflag' if $format eq 'cch-update';
525 $hash->{'actionflag'} ='I' if ($hash->{'data_vendor'} eq 'cch');
526 $hash->{'data_vendor'} ='cch';
527 $hash->{'effective_date'} = str2time($hash->{'effective_date'});
530 join(':', map{ $hash->{$_} } qw(taxtype taxcat) );
532 my %tax_class = ( 'data_vendor' => 'cch',
533 'taxclass' => $taxclassid,
536 my $tax_class = qsearchs( 'tax_class', \%tax_class );
537 return "Error updating tax rate: no tax class $taxclassid"
540 $hash->{'taxclassnum'} = $tax_class->taxclassnum;
542 foreach (qw( inoutcity inoutlocal taxtype taxcat )) {
546 my %passflagmap = ( '0' => '',
550 $hash->{'passflag'} = $passflagmap{$hash->{'passflag'}}
551 if exists $passflagmap{$hash->{'passflag'}};
553 foreach (keys %$hash) {
554 $hash->{$_} = substr($hash->{$_}, 0, 80)
555 if length($hash->{$_}) > 80;
558 my $actionflag = delete($hash->{'actionflag'});
559 if ($actionflag eq 'I') {
560 $insert{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = $hash;
561 }elsif ($actionflag eq 'D') {
562 $delete{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = $hash;
564 return "Unexpected action flag: ". $hash->{'actionflag'};
571 } elsif ( $format eq 'extended' ) {
572 die "unimplemented\n";
576 die "unknown format $format";
579 eval "use Text::CSV_XS;";
582 my $csv = new Text::CSV_XS;
586 local $SIG{HUP} = 'IGNORE';
587 local $SIG{INT} = 'IGNORE';
588 local $SIG{QUIT} = 'IGNORE';
589 local $SIG{TERM} = 'IGNORE';
590 local $SIG{TSTP} = 'IGNORE';
591 local $SIG{PIPE} = 'IGNORE';
593 my $oldAutoCommit = $FS::UID::AutoCommit;
594 local $FS::UID::AutoCommit = 0;
597 while ( defined($line=<$fh>) ) {
598 $csv->parse($line) or do {
599 $dbh->rollback if $oldAutoCommit;
600 return "can't parse: ". $csv->error_input();
603 if ( $job ) { # progress bar
604 if ( time - $min_sec > $last ) {
605 my $error = $job->update_statustext(
606 int( 100 * $imported / $count )
608 die $error if $error;
613 my @columns = $csv->fields();
615 my %tax_rate = ( 'data_vendor' => $format );
616 foreach my $field ( @fields ) {
617 $tax_rate{$field} = shift @columns;
619 if ( scalar( @columns ) ) {
620 $dbh->rollback if $oldAutoCommit;
621 return "Unexpected trailing columns in line (wrong format?): $line";
624 my $error = &{$hook}(\%tax_rate);
626 $dbh->rollback if $oldAutoCommit;
634 for (grep { !exists($delete{$_}) } keys %insert) {
635 if ( $job ) { # progress bar
636 if ( time - $min_sec > $last ) {
637 my $error = $job->update_statustext(
638 int( 100 * $imported / $count )
640 die $error if $error;
645 my $tax_rate = new FS::tax_rate( $insert{$_} );
646 my $error = $tax_rate->insert;
649 $dbh->rollback if $oldAutoCommit;
650 my $hashref = $insert{$_};
651 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
652 return "can't insert tax_rate for $line: $error";
658 for (grep { exists($delete{$_}) } keys %insert) {
659 if ( $job ) { # progress bar
660 if ( time - $min_sec > $last ) {
661 my $error = $job->update_statustext(
662 int( 100 * $imported / $count )
664 die $error if $error;
669 my $old = qsearchs( 'tax_rate', $delete{$_} );
671 $dbh->rollback if $oldAutoCommit;
673 return "can't find tax_rate to replace for: ".
674 #join(" ", map { "$_ => ". $old->{$_} } @fields);
675 join(" ", map { "$_ => ". $old->{$_} } keys(%$old) );
677 my $new = new FS::tax_rate({ $old->hash, %{$insert{$_}}, 'manual' => '' });
678 $new->taxnum($old->taxnum);
679 my $error = $new->replace($old);
682 $dbh->rollback if $oldAutoCommit;
683 my $hashref = $insert{$_};
684 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
685 return "can't replace tax_rate for $line: $error";
692 for (grep { !exists($insert{$_}) } keys %delete) {
693 if ( $job ) { # progress bar
694 if ( time - $min_sec > $last ) {
695 my $error = $job->update_statustext(
696 int( 100 * $imported / $count )
698 die $error if $error;
703 my $tax_rate = qsearchs( 'tax_rate', $delete{$_} );
705 $dbh->rollback if $oldAutoCommit;
706 $tax_rate = $delete{$_};
707 return "can't find tax_rate to delete for: ".
708 #join(" ", map { "$_ => ". $tax_rate->{$_} } @fields);
709 join(" ", map { "$_ => ". $tax_rate->{$_} } keys(%$tax_rate) );
711 my $error = $tax_rate->delete;
714 $dbh->rollback if $oldAutoCommit;
715 my $hashref = $delete{$_};
716 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
717 return "can't delete tax_rate for $line: $error";
723 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
725 return "Empty file!" unless ($imported || $format eq 'cch-update');
733 Load a batch import as a queued JSRPC job
740 my $param = thaw(decode_base64(shift));
741 my $format = $param->{'format'}; #well... this is all cch specific
743 my $files = $param->{'uploaded_files'}
744 or die "No files provided.";
746 my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files;
748 if ($format eq 'cch') {
750 my $oldAutoCommit = $FS::UID::AutoCommit;
751 local $FS::UID::AutoCommit = 0;
755 my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import,
756 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
757 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
758 'DETAIL', 'detail', \&FS::tax_rate::batch_import,
760 while( scalar(@list) ) {
761 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
762 unless ($files{$file}) {
763 $error = "No $name supplied";
766 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
767 my $filename = "$dir/". $files{$file};
768 open my $fh, "< $filename" or $error ||= "Can't open $name file: $!";
770 $error ||= &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
772 unlink $filename or warn "Can't delete $filename: $!";
776 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
779 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
782 }elsif ($format eq 'cch-update') {
784 my $oldAutoCommit = $FS::UID::AutoCommit;
785 local $FS::UID::AutoCommit = 0;
788 my @insert_list = ();
789 my @delete_list = ();
791 my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import,
792 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
793 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
795 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
796 while( scalar(@list) ) {
797 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
798 unless ($files{$file}) {
799 $error = "No $name supplied";
802 my $filename = "$dir/". $files{$file};
803 open my $fh, "< $filename" or $error ||= "Can't open $name file $filename: $!";
804 unlink $filename or warn "Can't delete $filename: $!";
806 my $ifh = new File::Temp( TEMPLATE => "$name.insert.XXXXXXXX",
809 ) or die "can't open temp file: $!\n";
811 my $dfh = new File::Temp( TEMPLATE => "$name.delete.XXXXXXXX",
814 ) or die "can't open temp file: $!\n";
818 $handle = $ifh if $_ =~ /"I"\s*$/;
819 $handle = $dfh if $_ =~ /"D"\s*$/;
821 $error = "bad input line: $_" unless $handle;
830 push @insert_list, $name, $ifh->filename, $import_sub;
831 unshift @delete_list, $name, $dfh->filename, $import_sub;
834 while( scalar(@insert_list) ) {
835 my ($name, $file, $import_sub) =
836 (shift @insert_list, shift @insert_list, shift @insert_list);
838 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
840 &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
842 unlink $file or warn "Can't delete $file: $!";
845 $error ||= "No DETAIL supplied"
846 unless ($files{detail});
847 open my $fh, "< $dir/". $files{detail}
848 or $error ||= "Can't open DETAIL file: $!";
850 &FS::tax_rate::batch_import({ 'filehandle' => $fh, 'format' => $format },
853 unlink "$dir/". $files{detail} or warn "Can't delete $files{detail}: $!"
856 while( scalar(@delete_list) ) {
857 my ($name, $file, $import_sub) =
858 (shift @delete_list, shift @delete_list, shift @delete_list);
860 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
862 &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
864 unlink $file or warn "Can't delete $file: $!";
868 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
871 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
875 die "Unknown format: $format";
880 =item browse_queries PARAMS
882 Returns a list consisting of a hashref suited for use as the argument
883 to qsearch, and sql query string. Each is based on the PARAMS hashref
884 of keys and values which frequently would be passed as C<scalar($cgi->Vars)>
885 from a form. This conveniently creates the query hashref and count_query
886 string required by the browse and search elements. As a side effect,
887 the PARAMS hashref is untainted and keys with unexpected values are removed.
895 'table' => 'tax_rate',
897 'order_by' => 'ORDER BY geocode, taxclassnum',
902 if ( $params->{data_vendor} =~ /^(\w+)$/ ) {
903 $extra_sql .= ' WHERE data_vendor = '. dbh->quote($1);
905 delete $params->{data_vendor};
908 if ( $params->{geocode} =~ /^(\w+)$/ ) {
909 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
910 'geocode LIKE '. dbh->quote($1.'%');
912 delete $params->{geocode};
915 if ( $params->{taxclassnum} =~ /^(\d+)$/ &&
916 qsearchs( 'tax_class', {'taxclassnum' => $1} )
919 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
920 ' taxclassnum = '. dbh->quote($1)
922 delete $params->{taxclassnun};
926 if ( $params->{tax_type} =~ /^(\d+)$/ );
927 delete $params->{tax_type}
931 if ( $params->{tax_cat} =~ /^(\d+)$/ );
932 delete $params->{tax_cat}
935 my @taxclassnum = ();
936 if ($tax_type || $tax_cat ) {
937 my $compare = "LIKE '". ( $tax_type || "%" ). ":". ( $tax_cat || "%" ). "'";
938 $compare = "= '$tax_type:$tax_cat'" if ($tax_type && $tax_cat);
939 @taxclassnum = map { $_->taxclassnum }
940 qsearch({ 'table' => 'tax_class',
942 'extra_sql' => "WHERE taxclass $compare",
946 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ). '( '.
947 join(' OR ', map { " taxclassnum = $_ " } @taxclassnum ). ' )'
950 unless ($params->{'showdisabled'}) {
951 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
952 "( disabled = '' OR disabled IS NULL )";
955 $query->{extra_sql} = $extra_sql;
957 return ($query, "SELECT COUNT(*) FROM tax_rate $extra_sql");
964 Mixing automatic and manual editing works poorly at present.
968 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base