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) {
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 my $hashref = $insert{$_};
646 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
647 return "can't insert tax_rate for $line: $error";
653 for (grep { exists($delete{$_}) } keys %insert) {
654 if ( $job ) { # progress bar
655 if ( time - $min_sec > $last ) {
656 my $error = $job->update_statustext(
657 int( 100 * $imported / $count )
659 die $error if $error;
664 my $old = qsearchs( 'tax_rate', $delete{$_} );
666 $dbh->rollback if $oldAutoCommit;
668 return "can't find tax_rate to replace for: ".
669 #join(" ", map { "$_ => ". $old->{$_} } @fields);
670 join(" ", map { "$_ => ". $old->{$_} } keys(%$old) );
672 my $new = new FS::tax_rate({ $old->hash, %{$insert{$_}}, 'manual' => '' });
673 $new->taxnum($old->taxnum);
674 my $error = $new->replace($old);
677 $dbh->rollback if $oldAutoCommit;
678 my $hashref = $insert{$_};
679 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
680 return "can't replace tax_rate for $line: $error";
687 for (grep { !exists($insert{$_}) } keys %delete) {
688 if ( $job ) { # progress bar
689 if ( time - $min_sec > $last ) {
690 my $error = $job->update_statustext(
691 int( 100 * $imported / $count )
693 die $error if $error;
698 my $tax_rate = qsearchs( 'tax_rate', $delete{$_} );
700 $dbh->rollback if $oldAutoCommit;
701 $tax_rate = $delete{$_};
702 return "can't find tax_rate to delete for: ".
703 #join(" ", map { "$_ => ". $tax_rate->{$_} } @fields);
704 join(" ", map { "$_ => ". $tax_rate->{$_} } keys(%$tax_rate) );
706 my $error = $tax_rate->delete;
709 $dbh->rollback if $oldAutoCommit;
710 my $hashref = $delete{$_};
711 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
712 return "can't delete tax_rate for $line: $error";
718 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
720 return "Empty file!" unless ($imported || $format eq 'cch-update');
728 Load a batch import as a queued JSRPC job
735 my $param = thaw(decode_base64(shift));
736 my $format = $param->{'format'}; #well... this is all cch specific
738 my $files = $param->{'uploaded_files'}
739 or die "No files provided.";
741 my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files;
743 if ($format eq 'cch') {
745 my $oldAutoCommit = $FS::UID::AutoCommit;
746 local $FS::UID::AutoCommit = 0;
750 my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import,
751 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
752 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
753 'DETAIL', 'detail', \&FS::tax_rate::batch_import,
755 while( scalar(@list) ) {
756 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
757 unless ($files{$file}) {
758 $error = "No $name supplied";
761 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
762 my $filename = "$dir/". $files{$file};
763 open my $fh, "< $filename" or $error ||= "Can't open $name file: $!";
765 $error ||= &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
767 unlink $filename or warn "Can't delete $filename: $!";
771 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
774 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
777 }elsif ($format eq 'cch-update') {
779 my $oldAutoCommit = $FS::UID::AutoCommit;
780 local $FS::UID::AutoCommit = 0;
783 my @insert_list = ();
784 my @delete_list = ();
786 my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import,
787 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
788 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
790 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
791 while( scalar(@list) ) {
792 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
793 unless ($files{$file}) {
794 $error = "No $name supplied";
797 my $filename = "$dir/". $files{$file};
798 open my $fh, "< $filename" or $error ||= "Can't open $name file $filename: $!";
799 unlink $filename or warn "Can't delete $filename: $!";
801 my $ifh = new File::Temp( TEMPLATE => "$name.insert.XXXXXXXX",
804 ) or die "can't open temp file: $!\n";
806 my $dfh = new File::Temp( TEMPLATE => "$name.delete.XXXXXXXX",
809 ) or die "can't open temp file: $!\n";
813 $handle = $ifh if $_ =~ /"I"\s*$/;
814 $handle = $dfh if $_ =~ /"D"\s*$/;
816 $error = "bad input line: $_" unless $handle;
825 push @insert_list, $name, $ifh->filename, $import_sub;
826 unshift @delete_list, $name, $dfh->filename, $import_sub;
829 while( scalar(@insert_list) ) {
830 my ($name, $file, $import_sub) =
831 (shift @insert_list, shift @insert_list, shift @insert_list);
833 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
835 &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
837 unlink $file or warn "Can't delete $file: $!";
840 $error ||= "No DETAIL supplied"
841 unless ($files{detail});
842 open my $fh, "< $dir/". $files{detail}
843 or $error ||= "Can't open DETAIL file: $!";
845 &FS::tax_rate::batch_import({ 'filehandle' => $fh, 'format' => $format },
848 unlink "$dir/". $files{detail} or warn "Can't delete $files{detail}: $!"
851 while( scalar(@delete_list) ) {
852 my ($name, $file, $import_sub) =
853 (shift @delete_list, shift @delete_list, shift @delete_list);
855 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
857 &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
859 unlink $file or warn "Can't delete $file: $!";
863 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
866 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
870 die "Unknown format: $format";
875 =item browse_queries PARAMS
877 Returns a list consisting of a hashref suited for use as the argument
878 to qsearch, and sql query string. Each is based on the PARAMS hashref
879 of keys and values which frequently would be passed as C<scalar($cgi->Vars)>
880 from a form. This conveniently creates the query hashref and count_query
881 string required by the browse and search elements. As a side effect,
882 the PARAMS hashref is untainted and keys with unexpected values are removed.
890 'table' => 'tax_rate',
892 'order_by' => 'ORDER BY geocode, taxclassnum',
897 if ( $params->{data_vendor} =~ /^(\w+)$/ ) {
898 $extra_sql .= ' WHERE data_vendor = '. dbh->quote($1);
900 delete $params->{data_vendor};
903 if ( $params->{geocode} =~ /^(\w+)$/ ) {
904 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
905 'geocode LIKE '. dbh->quote($1.'%');
907 delete $params->{geocode};
910 if ( $params->{taxclassnum} =~ /^(\d+)$/ &&
911 qsearchs( 'tax_class', {'taxclassnum' => $1} )
914 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
915 ' taxclassnum = '. dbh->quote($1)
917 delete $params->{taxclassnun};
921 if ( $params->{tax_type} =~ /^(\d+)$/ );
922 delete $params->{tax_type}
926 if ( $params->{tax_cat} =~ /^(\d+)$/ );
927 delete $params->{tax_cat}
930 my @taxclassnum = ();
931 if ($tax_type || $tax_cat ) {
932 my $compare = "LIKE '". ( $tax_type || "%" ). ":". ( $tax_cat || "%" ). "'";
933 $compare = "= '$tax_type:$tax_cat'" if ($tax_type && $tax_cat);
934 @taxclassnum = map { $_->taxclassnum }
935 qsearch({ 'table' => 'tax_class',
937 'extra_sql' => "WHERE taxclass $compare",
941 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ). '( '.
942 join(' OR ', map { " taxclassnum = $_ " } @taxclassnum ). ' )'
945 unless ($params->{'showdisabled'}) {
946 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
947 "( disabled = '' OR disabled IS NULL )";
950 $query->{extra_sql} = $extra_sql;
952 return ($query, "SELECT COUNT(*) FROM tax_rate $extra_sql");
959 Mixing automatic and manual editing works poorly at present.
963 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base