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->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) {
400 $taxable_units += $_->units foreach @cust_bill_pkg;
401 }elsif ($self->unittype == 1) {
402 return qq!fatal: can't (yet) handle fee with minute unit type!;
403 }elsif ($self->unittype == 2) {
406 return qq!fatal: can't (yet) handle unknown unit type in tax!.
412 # XXX insert exemption handling here
414 # the tax or fee is applied to taxbase or feebase and then
415 # the excessrate or excess fee is applied to taxmax or feemax
418 $amount += $taxable_charged * $self->tax;
419 $amount += $taxable_units * $self->fee;
421 warn "calculated taxes as [ $name, $amount ]\n"
424 return [$name, $amount];
428 =item tax_on_tax CUST_MAIN
430 Returns a list of taxes which are candidates for taxing taxes for the
431 given customer (see L<FS::cust_main>)
437 my $cust_main = shift;
439 warn "looking up taxes on tax ". $self->taxnum. " for customer ".
443 my $geocode = $cust_main->geocode($self->data_vendor);
447 my $extra_sql = ' AND ('.
448 join(' OR ', map{ 'geocode = '. $dbh->quote(substr($geocode, 0, $_)) }
453 my $order_by = 'ORDER BY taxclassnum, length(geocode) desc';
454 my $select = 'DISTINCT ON(taxclassnum) *';
456 # should qsearch preface columns with the table to facilitate joins?
457 my @taxclassnums = map { $_->taxclassnum }
458 qsearch( { 'table' => 'part_pkg_taxrate',
460 'hashref' => { 'data_vendor' => $self->data_vendor,
461 'taxclassnumtaxed' => $self->taxclassnum,
463 'extra_sql' => $extra_sql,
464 'order_by' => $order_by,
467 return () unless @taxclassnums;
470 "AND (". join(' OR ', map { "taxclassnum = $_" } @taxclassnums ). ")";
472 qsearch({ 'table' => 'tax_rate',
473 'hashref' => { 'geocode' => $geocode, },
474 'extra_sql' => $extra_sql,
490 my ($param, $job) = @_;
492 my $fh = $param->{filehandle};
493 my $format = $param->{'format'};
502 my ( $count, $last, $min_sec ) = (0, time, 5); #progressbar
505 while ( defined($line=<$fh>) );
510 if ( $format eq 'cch' || $format eq 'cch-update' ) {
511 @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax
512 excessrate effective_date taxauth taxtype taxcat taxname
513 usetax useexcessrate fee unittype feemax maxtype passflag
515 push @fields, 'actionflag' if $format eq 'cch-update';
520 $hash->{'actionflag'} ='I' if ($hash->{'data_vendor'} eq 'cch');
521 $hash->{'data_vendor'} ='cch';
522 $hash->{'effective_date'} = str2time($hash->{'effective_date'});
525 join(':', map{ $hash->{$_} } qw(taxtype taxcat) );
527 my %tax_class = ( 'data_vendor' => 'cch',
528 'taxclass' => $taxclassid,
531 my $tax_class = qsearchs( 'tax_class', \%tax_class );
532 return "Error updating tax rate: no tax class $taxclassid"
535 $hash->{'taxclassnum'} = $tax_class->taxclassnum;
537 foreach (qw( inoutcity inoutlocal taxtype taxcat )) {
541 my %passflagmap = ( '0' => '',
545 $hash->{'passflag'} = $passflagmap{$hash->{'passflag'}}
546 if exists $passflagmap{$hash->{'passflag'}};
548 foreach (keys %$hash) {
549 $hash->{$_} = substr($hash->{$_}, 0, 80)
550 if length($hash->{$_}) > 80;
553 my $actionflag = delete($hash->{'actionflag'});
554 if ($actionflag eq 'I') {
555 $insert{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = $hash;
556 }elsif ($actionflag eq 'D') {
557 $delete{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = $hash;
559 return "Unexpected action flag: ". $hash->{'actionflag'};
566 } elsif ( $format eq 'extended' ) {
567 die "unimplemented\n";
571 die "unknown format $format";
574 eval "use Text::CSV_XS;";
577 my $csv = new Text::CSV_XS;
581 local $SIG{HUP} = 'IGNORE';
582 local $SIG{INT} = 'IGNORE';
583 local $SIG{QUIT} = 'IGNORE';
584 local $SIG{TERM} = 'IGNORE';
585 local $SIG{TSTP} = 'IGNORE';
586 local $SIG{PIPE} = 'IGNORE';
588 my $oldAutoCommit = $FS::UID::AutoCommit;
589 local $FS::UID::AutoCommit = 0;
592 while ( defined($line=<$fh>) ) {
593 $csv->parse($line) or do {
594 $dbh->rollback if $oldAutoCommit;
595 return "can't parse: ". $csv->error_input();
598 if ( $job ) { # progress bar
599 if ( time - $min_sec > $last ) {
600 my $error = $job->update_statustext(
601 int( 100 * $imported / $count )
603 die $error if $error;
608 my @columns = $csv->fields();
610 my %tax_rate = ( 'data_vendor' => $format );
611 foreach my $field ( @fields ) {
612 $tax_rate{$field} = shift @columns;
614 if ( scalar( @columns ) ) {
615 $dbh->rollback if $oldAutoCommit;
616 return "Unexpected trailing columns in line (wrong format?): $line";
619 my $error = &{$hook}(\%tax_rate);
621 $dbh->rollback if $oldAutoCommit;
629 for (grep { !exists($delete{$_}) } keys %insert) {
630 if ( $job ) { # progress bar
631 if ( time - $min_sec > $last ) {
632 my $error = $job->update_statustext(
633 int( 100 * $imported / $count )
635 die $error if $error;
640 my $tax_rate = new FS::tax_rate( $insert{$_} );
641 my $error = $tax_rate->insert;
644 $dbh->rollback if $oldAutoCommit;
645 return "can't insert tax_rate for $line: $error";
651 for (grep { exists($delete{$_}) } keys %insert) {
652 if ( $job ) { # progress bar
653 if ( time - $min_sec > $last ) {
654 my $error = $job->update_statustext(
655 int( 100 * $imported / $count )
657 die $error if $error;
662 my $old = qsearchs( 'tax_rate', $delete{$_} );
664 $dbh->rollback if $oldAutoCommit;
666 return "can't find tax_rate to replace for: ".
667 #join(" ", map { "$_ => ". $old->{$_} } @fields);
668 join(" ", map { "$_ => ". $old->{$_} } keys(%$old) );
670 my $new = new FS::tax_rate( $insert{$_} );
671 $new->taxnum($old->taxnum);
672 my $error = $new->replace($old);
675 $dbh->rollback if $oldAutoCommit;
676 return "can't insert tax_rate for $line: $error";
683 for (grep { !exists($insert{$_}) } keys %delete) {
684 if ( $job ) { # progress bar
685 if ( time - $min_sec > $last ) {
686 my $error = $job->update_statustext(
687 int( 100 * $imported / $count )
689 die $error if $error;
694 my $tax_rate = qsearchs( 'tax_rate', $delete{$_} );
696 $dbh->rollback if $oldAutoCommit;
697 $tax_rate = $delete{$_};
698 return "can't find tax_rate to delete for: ".
699 #join(" ", map { "$_ => ". $tax_rate->{$_} } @fields);
700 join(" ", map { "$_ => ". $tax_rate->{$_} } keys(%$tax_rate) );
702 my $error = $tax_rate->delete;
705 $dbh->rollback if $oldAutoCommit;
706 return "can't insert tax_rate for $line: $error";
712 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
714 return "Empty file!" unless $imported;
722 Load a batch import as a queued JSRPC job
729 my $param = thaw(decode_base64(shift));
730 my $format = $param->{'format'}; #well... this is all cch specific
732 my $files = $param->{'uploaded_files'}
733 or die "No files provided.";
735 my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files;
737 if ($format eq 'cch') {
739 my $oldAutoCommit = $FS::UID::AutoCommit;
740 local $FS::UID::AutoCommit = 0;
744 my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import,
745 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
746 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
747 'DETAIL', 'detail', \&FS::tax_rate::batch_import,
749 while( scalar(@list) ) {
750 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
751 unless ($files{$file}) {
752 $error = "No $name supplied";
755 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
756 my $filename = "$dir/". $files{$file};
757 open my $fh, "< $filename" or $error ||= "Can't open $name file: $!";
759 $error ||= &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
761 unlink $filename or warn "Can't delete $filename: $!";
765 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
768 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
771 }elsif ($format eq 'cch-update') {
773 my $oldAutoCommit = $FS::UID::AutoCommit;
774 local $FS::UID::AutoCommit = 0;
777 my @insert_list = ();
778 my @delete_list = ();
780 my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import,
781 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
782 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
784 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
785 while( scalar(@list) ) {
786 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
787 unless ($files{$file}) {
788 $error = "No $name supplied";
791 my $filename = "$dir/". $files{$file};
792 open my $fh, "< $filename" or $error ||= "Can't open $name file $filename: $!";
793 unlink $filename or warn "Can't delete $filename: $!";
795 my $ifh = new File::Temp( TEMPLATE => "$name.insert.XXXXXXXX",
798 ) or die "can't open temp file: $!\n";
800 my $dfh = new File::Temp( TEMPLATE => "$name.delete.XXXXXXXX",
803 ) or die "can't open temp file: $!\n";
807 $handle = $ifh if $_ =~ /"I"\s*$/;
808 $handle = $dfh if $_ =~ /"D"\s*$/;
810 $error = "bad input line: $_" unless $handle;
819 push @insert_list, $name, $ifh->filename, $import_sub;
820 unshift @delete_list, $name, $dfh->filename, $import_sub;
823 while( scalar(@insert_list) ) {
824 my ($name, $file, $import_sub) =
825 (shift @insert_list, shift @insert_list, shift @insert_list);
827 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
829 &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
831 unlink $file or warn "Can't delete $file: $!";
834 $error = "No DETAIL supplied"
835 unless ($files{detail});
836 open my $fh, "< $dir/". $files{detail}
837 or $error ||= "Can't open DETAIL file: $!";
839 &FS::tax_rate::batch_import({ 'filehandle' => $fh, 'format' => $format },
842 unlink "$dir/". $files{detail} or warn "Can't delete $files{detail}: $!"
845 while( scalar(@delete_list) ) {
846 my ($name, $file, $import_sub) =
847 (shift @delete_list, shift @delete_list, shift @delete_list);
849 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
851 &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
853 unlink $file or warn "Can't delete $file: $!";
857 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
860 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
864 die "Unknown format: $format";
869 =item browse_queries PARAMS
871 Returns a list consisting of a hashref suited for use as the argument
872 to qsearch, and sql query string. Each is based on the PARAMS hashref
873 of keys and values which frequently would be passed as C<scalar($cgi->Vars)>
874 from a form. This conveniently creates the query hashref and count_query
875 string required by the browse and search elements. As a side effect,
876 the PARAMS hashref is untainted and keys with unexpected values are removed.
884 'table' => 'tax_rate',
886 'order_by' => 'ORDER BY geocode, taxclassnum',
891 if ( $params->{data_vendor} =~ /^(\w+)$/ ) {
892 $extra_sql .= ' WHERE data_vendor = '. dbh->quote($1);
894 delete $params->{data_vendor};
897 if ( $params->{geocode} =~ /^(\w+)$/ ) {
898 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
899 'geocode LIKE '. dbh->quote($1.'%');
901 delete $params->{geocode};
904 if ( $params->{taxclassnum} =~ /^(\d+)$/ &&
905 qsearchs( 'tax_class', {'taxclassnum' => $1} )
908 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
909 ' taxclassnum = '. dbh->quote($1)
911 delete $params->{taxclassnun};
915 if ( $params->{tax_type} =~ /^(\d+)$/ );
916 delete $params->{tax_type}
920 if ( $params->{tax_cat} =~ /^(\d+)$/ );
921 delete $params->{tax_cat}
924 my @taxclassnum = ();
925 if ($tax_type || $tax_cat ) {
926 my $compare = "LIKE '". ( $tax_type || "%" ). ":". ( $tax_cat || "%" ). "'";
927 $compare = "= '$tax_type:$tax_cat'" if ($tax_type && $tax_cat);
928 @taxclassnum = map { $_->taxclassnum }
929 qsearch({ 'table' => 'tax_class',
931 'extra_sql' => "WHERE taxclass $compare",
935 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ). '( '.
936 join(' OR ', map { " taxclassnum = $_ " } @taxclassnum ). ' )'
939 unless ($params->{'showdisabled'}) {
940 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
941 "( disabled = '' OR disabled IS NULL )";
944 $query->{extra_sql} = $extra_sql;
946 return ($query, "SELECT COUNT(*) FROM tax_rate $extra_sql");
953 Mixing automatic and manual editing works poorly at present.
957 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base