4 use vars qw( @ISA $DEBUG $me
5 %tax_unittypes %tax_maxtypes %tax_basetypes %tax_authorities
6 %tax_passtypes %GetInfoType );
8 use Storable qw( thaw );
11 use DBIx::DBSchema::Table;
12 use DBIx::DBSchema::Column;
13 use FS::Record qw( qsearch qsearchs dbh dbdef );
15 use FS::cust_bill_pkg;
16 use FS::cust_tax_location;
17 use FS::part_pkg_taxrate;
19 use FS::Misc qw( csv_from_fixed );
21 @ISA = qw( FS::Record );
24 $me = '[FS::tax_rate]';
28 FS::tax_rate - Object methods for tax_rate objects
34 $record = new FS::tax_rate \%hash;
35 $record = new FS::tax_rate { 'column' => 'value' };
37 $error = $record->insert;
39 $error = $new_record->replace($old_record);
41 $error = $record->delete;
43 $error = $record->check;
47 An FS::tax_rate object represents a tax rate, defined by locale.
48 FS::tax_rate inherits from FS::Record. The following fields are
55 primary key (assigned automatically for new tax rates)
59 a geographic location code provided by a tax data vendor
67 a location code provided by a tax authority
71 a foreign key into FS::tax_class - the type of tax
72 referenced but FS::part_pkg_taxrate
75 the time after which the tax applies
83 second bracket percentage
87 the amount to which the tax applies (first bracket)
91 a cap on the amount of tax if a cap exists
95 percentage on out of jurisdiction purchases
99 second bracket percentage on out of jurisdiction purchases
103 one of the values in %tax_unittypes
107 amount of tax per unit
111 second bracket amount of tax per unit
115 the number of units to which the fee applies (first bracket)
119 the most units to which fees apply (first and second brackets)
123 a value from %tax_maxtypes indicating how brackets accumulate (i.e. monthly, per invoice, etc)
127 if defined, printed on invoices instead of "Tax"
131 a value from %tax_authorities
135 a value from %tax_basetypes indicating the tax basis
139 a value from %tax_passtypes indicating how the tax should displayed to the customer
143 'Y', 'N', or blank indicating the tax can be passed to the customer
147 if 'Y', this tax does not apply to setup fees
151 if 'Y', this tax does not apply to recurring fees
155 if 'Y', has been manually edited
165 Creates a new tax rate. To add the tax rate to the database, see L<"insert">.
169 sub table { 'tax_rate'; }
173 Adds this tax rate to the database. If there is an error, returns the error,
174 otherwise returns false.
178 Deletes this tax rate from the database. If there is an error, returns the
179 error, otherwise returns false.
181 =item replace OLD_RECORD
183 Replaces the OLD_RECORD with this one in the database. If there is an error,
184 returns the error, otherwise returns false.
188 Checks all fields to make sure this is a valid tax rate. If there is an error,
189 returns the error, otherwise returns false. Called by the insert and replace
197 foreach (qw( taxbase taxmax )) {
198 $self->$_(0) unless $self->$_;
201 $self->ut_numbern('taxnum')
202 || $self->ut_text('geocode')
203 || $self->ut_textn('data_vendor')
204 || $self->ut_textn('location')
205 || $self->ut_foreign_key('taxclassnum', 'tax_class', 'taxclassnum')
206 || $self->ut_snumbern('effective_date')
207 || $self->ut_float('tax')
208 || $self->ut_floatn('excessrate')
209 || $self->ut_money('taxbase')
210 || $self->ut_money('taxmax')
211 || $self->ut_floatn('usetax')
212 || $self->ut_floatn('useexcessrate')
213 || $self->ut_numbern('unittype')
214 || $self->ut_floatn('fee')
215 || $self->ut_floatn('excessfee')
216 || $self->ut_floatn('feemax')
217 || $self->ut_numbern('maxtype')
218 || $self->ut_textn('taxname')
219 || $self->ut_numbern('taxauth')
220 || $self->ut_numbern('basetype')
221 || $self->ut_numbern('passtype')
222 || $self->ut_enum('passflag', [ '', 'Y', 'N' ])
223 || $self->ut_enum('setuptax', [ '', 'Y' ] )
224 || $self->ut_enum('recurtax', [ '', 'Y' ] )
225 || $self->ut_enum('manual', [ '', 'Y' ] )
226 || $self->ut_enum('disabled', [ '', 'Y' ] )
227 || $self->SUPER::check
232 =item taxclass_description
234 Returns the human understandable value associated with the related
239 sub taxclass_description {
241 my $tax_class = qsearchs('tax_class', {'taxclassnum' => $self->taxclassnum });
242 $tax_class ? $tax_class->description : '';
247 Returns the human understandable value associated with the unittype column
251 %tax_unittypes = ( '0' => 'access line',
258 $tax_unittypes{$self->unittype};
263 Returns the human understandable value associated with the maxtype column
267 %tax_maxtypes = ( '0' => 'receipts per invoice',
268 '1' => 'receipts per item',
269 '2' => 'total utility charges per utility tax year',
270 '3' => 'total charges per utility tax year',
271 '4' => 'receipts per access line',
272 '9' => 'monthly receipts per location',
277 $tax_maxtypes{$self->maxtype};
282 Returns the human understandable value associated with the basetype column
286 %tax_basetypes = ( '0' => 'sale price',
287 '1' => 'gross receipts',
288 '2' => 'sales taxable telecom revenue',
289 '3' => 'minutes carried',
290 '4' => 'minutes billed',
291 '5' => 'gross operating revenue',
292 '6' => 'access line',
294 '8' => 'gross revenue',
295 '9' => 'portion gross receipts attributable to interstate service',
296 '10' => 'access line',
297 '11' => 'gross profits',
298 '12' => 'tariff rate',
300 '15' => 'prior year gross receipts',
305 $tax_basetypes{$self->basetype};
310 Returns the human understandable value associated with the taxauth column
314 %tax_authorities = ( '0' => 'federal',
319 '5' => 'county administered by state',
320 '6' => 'city administered by state',
321 '7' => 'city administered by county',
322 '8' => 'local administered by state',
323 '9' => 'local administered by county',
328 $tax_authorities{$self->taxauth};
333 Returns the human understandable value associated with the passtype column
337 %tax_passtypes = ( '0' => 'separate tax line',
338 '1' => 'separate surcharge line',
339 '2' => 'surcharge not separated',
340 '3' => 'included in base rate',
345 $tax_passtypes{$self->passtype};
348 =item taxline TAXABLES, [ OPTIONSHASH ]
350 Returns a listref of a name and an amount of tax calculated for the list
351 of packages/amounts referenced by TAXABLES. If an error occurs, a message
352 is returned as a scalar.
362 if (ref($_[0]) eq 'ARRAY') {
367 #exemptions would be broken in this case
370 my $name = $self->taxname;
371 $name = 'Other surcharges'
372 if ($self->passtype == 2);
375 if ( $self->disabled ) { # we always know how to handle disabled taxes
382 my $taxable_charged = 0;
383 my @cust_bill_pkg = grep { $taxable_charged += $_ unless ref; ref; }
386 warn "calculating taxes for ". $self->taxnum. " on ".
387 join (",", map { $_->pkgnum } @cust_bill_pkg)
390 if ($self->passflag eq 'N') {
391 # return "fatal: can't (yet) handle taxes not passed to the customer";
392 # until someone needs to track these in freeside
399 if ($self->maxtype != 0 && $self->maxtype != 9) {
400 return $self->_fatal_or_null( 'tax with "'.
401 $self->maxtype_name. '" threshold'
405 if ($self->maxtype == 9) {
407 $self->_fatal_or_null( 'tax with "'. $self->maxtype_name. '" threshold' );
411 # we treat gross revenue as gross receipts and expect the tax data
412 # to DTRT (i.e. tax on tax rules)
413 if ($self->basetype != 0 && $self->basetype != 1 &&
414 $self->basetype != 5 && $self->basetype != 6 &&
415 $self->basetype != 7 && $self->basetype != 8 &&
416 $self->basetype != 14
419 $self->_fatal_or_null( 'tax with "'. $self->basetype_name. '" basis' );
422 unless ($self->setuptax =~ /^Y$/i) {
423 $taxable_charged += $_->setup foreach @cust_bill_pkg;
425 unless ($self->recurtax =~ /^Y$/i) {
426 $taxable_charged += $_->recur foreach @cust_bill_pkg;
429 my $taxable_units = 0;
430 unless ($self->recurtax =~ /^Y$/i) {
431 if ($self->unittype == 0) {
433 foreach (@cust_bill_pkg) {
434 $taxable_units += $_->units
435 unless $seen{$_->pkgnum};
438 }elsif ($self->unittype == 1) {
439 return $self->_fatal_or_null( 'fee with minute unit type' );
440 }elsif ($self->unittype == 2) {
443 return $self->_fatal_or_null( 'unknown unit type in tax'. $self->taxnum );
448 # XXX insert exemption handling here
450 # the tax or fee is applied to taxbase or feebase and then
451 # the excessrate or excess fee is applied to taxmax or feemax
454 $amount += $taxable_charged * $self->tax;
455 $amount += $taxable_units * $self->fee;
457 warn "calculated taxes as [ $name, $amount ]\n"
468 my ($self, $error) = @_;
470 my $conf = new FS::Conf;
472 $error = "fatal: can't yet handle ". $error;
473 my $name = $self->taxname;
474 $name = 'Other surcharges'
475 if ($self->passtype == 2);
477 if ($conf->exists('ignore_incalculable_taxes')) {
479 return { name => $name, amount => 0 };
485 =item tax_on_tax CUST_MAIN
487 Returns a list of taxes which are candidates for taxing taxes for the
488 given customer (see L<FS::cust_main>)
494 my $cust_main = shift;
496 warn "looking up taxes on tax ". $self->taxnum. " for customer ".
500 my $geocode = $cust_main->geocode($self->data_vendor);
504 my $extra_sql = ' AND ('.
505 join(' OR ', map{ 'geocode = '. $dbh->quote(substr($geocode, 0, $_)) }
510 my $order_by = 'ORDER BY taxclassnum, length(geocode) desc';
511 my $select = 'DISTINCT ON(taxclassnum) *';
513 # should qsearch preface columns with the table to facilitate joins?
514 my @taxclassnums = map { $_->taxclassnum }
515 qsearch( { 'table' => 'part_pkg_taxrate',
517 'hashref' => { 'data_vendor' => $self->data_vendor,
518 'taxclassnumtaxed' => $self->taxclassnum,
520 'extra_sql' => $extra_sql,
521 'order_by' => $order_by,
524 return () unless @taxclassnums;
527 "AND (". join(' OR ', map { "taxclassnum = $_" } @taxclassnums ). ")";
529 qsearch({ 'table' => 'tax_rate',
530 'hashref' => { 'geocode' => $geocode, },
531 'extra_sql' => $extra_sql,
547 my ($param, $job) = @_;
549 my $fh = $param->{filehandle};
550 my $format = $param->{'format'};
558 my @column_lengths = ();
559 my @column_callbacks = ();
560 if ( $format eq 'cch-fixed' || $format eq 'cch-fixed-update' ) {
561 $format =~ s/-fixed//;
562 my $date_format = sub { my $r='';
563 /^(\d{4})(\d{2})(\d{2})$/ && ($r="$1/$2/$3");
566 my $trim = sub { my $r = shift; $r =~ s/^\s*//; $r =~ s/\s*$//; $r };
567 push @column_lengths, qw( 10 1 1 8 8 5 8 8 8 1 2 2 30 8 8 10 2 8 2 1 2 2 );
568 push @column_lengths, 1 if $format eq 'cch-update';
569 push @column_callbacks, $trim foreach (@column_lengths); # 5, 6, 15, 17 esp
570 $column_callbacks[8] = $date_format;
574 my ( $count, $last, $min_sec ) = (0, time, 5); #progressbar
575 if ( $job || scalar(@column_callbacks) ) {
577 csv_from_fixed(\$fh, \$count, \@column_lengths, \@column_callbacks);
578 return $error if $error;
582 if ( $format eq 'cch' || $format eq 'cch-update' ) {
583 @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax
584 excessrate effective_date taxauth taxtype taxcat taxname
585 usetax useexcessrate fee unittype feemax maxtype passflag
587 push @fields, 'actionflag' if $format eq 'cch-update';
592 $hash->{'actionflag'} ='I' if ($hash->{'data_vendor'} eq 'cch');
593 $hash->{'data_vendor'} ='cch';
594 $hash->{'effective_date'} = str2time($hash->{'effective_date'});
597 join(':', map{ $hash->{$_} } qw(taxtype taxcat) );
599 my %tax_class = ( 'data_vendor' => 'cch',
600 'taxclass' => $taxclassid,
603 my $tax_class = qsearchs( 'tax_class', \%tax_class );
604 return "Error updating tax rate: no tax class $taxclassid"
607 $hash->{'taxclassnum'} = $tax_class->taxclassnum;
609 foreach (qw( inoutcity inoutlocal taxtype taxcat )) {
613 my %passflagmap = ( '0' => '',
617 $hash->{'passflag'} = $passflagmap{$hash->{'passflag'}}
618 if exists $passflagmap{$hash->{'passflag'}};
620 foreach (keys %$hash) {
621 $hash->{$_} = substr($hash->{$_}, 0, 80)
622 if length($hash->{$_}) > 80;
625 my $actionflag = delete($hash->{'actionflag'});
627 $hash->{'taxname'} =~ s/`/'/g;
628 $hash->{'taxname'} =~ s|\\|/|g;
630 return '' if $format eq 'cch'; # but not cch-update
632 if ($actionflag eq 'I') {
633 $insert{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
634 }elsif ($actionflag eq 'D') {
635 $delete{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
637 return "Unexpected action flag: ". $hash->{'actionflag'};
640 delete($hash->{$_}) for keys %$hash;
646 } elsif ( $format eq 'extended' ) {
647 die "unimplemented\n";
651 die "unknown format $format";
654 eval "use Text::CSV_XS;";
657 my $csv = new Text::CSV_XS;
661 local $SIG{HUP} = 'IGNORE';
662 local $SIG{INT} = 'IGNORE';
663 local $SIG{QUIT} = 'IGNORE';
664 local $SIG{TERM} = 'IGNORE';
665 local $SIG{TSTP} = 'IGNORE';
666 local $SIG{PIPE} = 'IGNORE';
668 my $oldAutoCommit = $FS::UID::AutoCommit;
669 local $FS::UID::AutoCommit = 0;
672 while ( defined($line=<$fh>) ) {
673 $csv->parse($line) or do {
674 $dbh->rollback if $oldAutoCommit;
675 return "can't parse: ". $csv->error_input();
678 if ( $job ) { # progress bar
679 if ( time - $min_sec > $last ) {
680 my $error = $job->update_statustext(
681 int( 100 * $imported / $count )
683 die $error if $error;
688 my @columns = $csv->fields();
690 my %tax_rate = ( 'data_vendor' => $format );
691 foreach my $field ( @fields ) {
692 $tax_rate{$field} = shift @columns;
694 if ( scalar( @columns ) ) {
695 $dbh->rollback if $oldAutoCommit;
696 return "Unexpected trailing columns in line (wrong format?): $line";
699 my $error = &{$hook}(\%tax_rate);
701 $dbh->rollback if $oldAutoCommit;
705 if (scalar(keys %tax_rate)) { #inserts only, not updates for cch
707 my $tax_rate = new FS::tax_rate( \%tax_rate );
708 $error = $tax_rate->insert;
711 $dbh->rollback if $oldAutoCommit;
712 return "can't insert tax_rate for $line: $error";
721 for (grep { !exists($delete{$_}) } keys %insert) {
722 if ( $job ) { # progress bar
723 if ( time - $min_sec > $last ) {
724 my $error = $job->update_statustext(
725 int( 100 * $imported / $count )
727 die $error if $error;
732 my $tax_rate = new FS::tax_rate( $insert{$_} );
733 my $error = $tax_rate->insert;
736 $dbh->rollback if $oldAutoCommit;
737 my $hashref = $insert{$_};
738 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
739 return "can't insert tax_rate for $line: $error";
745 for (grep { exists($delete{$_}) } keys %insert) {
746 if ( $job ) { # progress bar
747 if ( time - $min_sec > $last ) {
748 my $error = $job->update_statustext(
749 int( 100 * $imported / $count )
751 die $error if $error;
756 my $old = qsearchs( 'tax_rate', $delete{$_} );
758 $dbh->rollback if $oldAutoCommit;
760 return "can't find tax_rate to replace for: ".
761 #join(" ", map { "$_ => ". $old->{$_} } @fields);
762 join(" ", map { "$_ => ". $old->{$_} } keys(%$old) );
764 my $new = new FS::tax_rate({ $old->hash, %{$insert{$_}}, 'manual' => '' });
765 $new->taxnum($old->taxnum);
766 my $error = $new->replace($old);
769 $dbh->rollback if $oldAutoCommit;
770 my $hashref = $insert{$_};
771 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
772 return "can't replace tax_rate for $line: $error";
779 for (grep { !exists($insert{$_}) } keys %delete) {
780 if ( $job ) { # progress bar
781 if ( time - $min_sec > $last ) {
782 my $error = $job->update_statustext(
783 int( 100 * $imported / $count )
785 die $error if $error;
790 my $tax_rate = qsearchs( 'tax_rate', $delete{$_} );
792 $dbh->rollback if $oldAutoCommit;
793 $tax_rate = $delete{$_};
794 return "can't find tax_rate to delete for: ".
795 #join(" ", map { "$_ => ". $tax_rate->{$_} } @fields);
796 join(" ", map { "$_ => ". $tax_rate->{$_} } keys(%$tax_rate) );
798 my $error = $tax_rate->delete;
801 $dbh->rollback if $oldAutoCommit;
802 my $hashref = $delete{$_};
803 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
804 return "can't delete tax_rate for $line: $error";
810 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
812 return "Empty file!" unless ($imported || $format eq 'cch-update');
818 =item process_batch_import
820 Load a batch import as a queued JSRPC job
824 sub process_batch_import {
827 my $param = thaw(decode_base64(shift));
828 my $format = $param->{'format'}; #well... this is all cch specific
830 my $files = $param->{'uploaded_files'}
831 or die "No files provided.";
833 my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files;
835 if ($format eq 'cch' || $format eq 'cch-fixed') {
837 my $oldAutoCommit = $FS::UID::AutoCommit;
838 local $FS::UID::AutoCommit = 0;
841 my $have_location = 0;
843 my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import,
844 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
845 'ZIP', 'zipfile', \&FS::cust_tax_location::batch_import,
846 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
847 'DETAIL', 'detail', \&FS::tax_rate::batch_import,
849 while( scalar(@list) ) {
850 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
851 unless ($files{$file}) {
852 next if $name eq 'PLUS4';
853 $error = "No $name supplied";
854 $error = "Neither PLUS4 nor ZIP supplied"
855 if ($name eq 'ZIP' && !$have_location);
858 $have_location = 1 if $name eq 'PLUS4';
859 my $fmt = $format. ( $name eq 'ZIP' ? '-zip' : '' );
860 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
861 my $filename = "$dir/". $files{$file};
862 open my $fh, "< $filename" or $error ||= "Can't open $name file: $!";
864 $error ||= &{$import_sub}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
866 unlink $filename or warn "Can't delete $filename: $!";
870 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
873 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
876 }elsif ($format eq 'cch-update' || $format eq 'cch-fixed-update') {
878 my $oldAutoCommit = $FS::UID::AutoCommit;
879 local $FS::UID::AutoCommit = 0;
882 my @insert_list = ();
883 my @delete_list = ();
885 my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import,
886 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
887 'ZIP', 'zipfile', \&FS::cust_tax_location::batch_import,
888 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
890 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
891 while( scalar(@list) ) {
892 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
893 unless ($files{$file}) {
894 my $vendor = $name eq 'ZIP' ? 'cch' : 'cch-zip';
895 next # update expected only for previously installed location data
896 if ( ($name eq 'PLUS4' || $name eq 'ZIP')
897 && !scalar( qsearch( { table => 'cust_tax_location',
898 hashref => { data_vendor => $vendor },
899 select => 'DISTINCT data_vendor',
904 $error = "No $name supplied";
907 my $filename = "$dir/". $files{$file};
908 open my $fh, "< $filename" or $error ||= "Can't open $name file $filename: $!";
909 unlink $filename or warn "Can't delete $filename: $!";
911 my $ifh = new File::Temp( TEMPLATE => "$name.insert.XXXXXXXX",
914 ) or die "can't open temp file: $!\n";
916 my $dfh = new File::Temp( TEMPLATE => "$name.delete.XXXXXXXX",
919 ) or die "can't open temp file: $!\n";
921 my $insert_pattern = ($format eq 'cch-update') ? qr/"I"\s*$/ : qr/I\s*$/;
922 my $delete_pattern = ($format eq 'cch-update') ? qr/"D"\s*$/ : qr/D\s*$/;
925 $handle = $ifh if $_ =~ /$insert_pattern/;
926 $handle = $dfh if $_ =~ /$delete_pattern/;
928 $error = "bad input line: $_" unless $handle;
937 push @insert_list, $name, $ifh->filename, $import_sub;
938 unshift @delete_list, $name, $dfh->filename, $import_sub;
941 while( scalar(@insert_list) ) {
942 my ($name, $file, $import_sub) =
943 (shift @insert_list, shift @insert_list, shift @insert_list);
945 my $fmt = $format. ( $name eq 'ZIP' ? '-zip' : '' );
946 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
948 &{$import_sub}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
950 unlink $file or warn "Can't delete $file: $!";
953 $error ||= "No DETAIL supplied"
954 unless ($files{detail});
955 open my $fh, "< $dir/". $files{detail}
956 or $error ||= "Can't open DETAIL file: $!";
958 &FS::tax_rate::batch_import({ 'filehandle' => $fh, 'format' => $format },
961 unlink "$dir/". $files{detail} or warn "Can't delete $files{detail}: $!"
964 while( scalar(@delete_list) ) {
965 my ($name, $file, $import_sub) =
966 (shift @delete_list, shift @delete_list, shift @delete_list);
968 my $fmt = $format. ( $name eq 'ZIP' ? '-zip' : '' );
969 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
971 &{$import_sub}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
973 unlink $file or warn "Can't delete $file: $!";
977 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
980 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
984 die "Unknown format: $format";
989 =item browse_queries PARAMS
991 Returns a list consisting of a hashref suited for use as the argument
992 to qsearch, and sql query string. Each is based on the PARAMS hashref
993 of keys and values which frequently would be passed as C<scalar($cgi->Vars)>
994 from a form. This conveniently creates the query hashref and count_query
995 string required by the browse and search elements. As a side effect,
996 the PARAMS hashref is untainted and keys with unexpected values are removed.
1000 sub browse_queries {
1004 'table' => 'tax_rate',
1006 'order_by' => 'ORDER BY geocode, taxclassnum',
1011 if ( $params->{data_vendor} =~ /^(\w+)$/ ) {
1012 $extra_sql .= ' WHERE data_vendor = '. dbh->quote($1);
1014 delete $params->{data_vendor};
1017 if ( $params->{geocode} =~ /^(\w+)$/ ) {
1018 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1019 'geocode LIKE '. dbh->quote($1.'%');
1021 delete $params->{geocode};
1024 if ( $params->{taxclassnum} =~ /^(\d+)$/ &&
1025 qsearchs( 'tax_class', {'taxclassnum' => $1} )
1028 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1029 ' taxclassnum = '. dbh->quote($1)
1031 delete $params->{taxclassnun};
1035 if ( $params->{tax_type} =~ /^(\d+)$/ );
1036 delete $params->{tax_type}
1040 if ( $params->{tax_cat} =~ /^(\d+)$/ );
1041 delete $params->{tax_cat}
1044 my @taxclassnum = ();
1045 if ($tax_type || $tax_cat ) {
1046 my $compare = "LIKE '". ( $tax_type || "%" ). ":". ( $tax_cat || "%" ). "'";
1047 $compare = "= '$tax_type:$tax_cat'" if ($tax_type && $tax_cat);
1048 @taxclassnum = map { $_->taxclassnum }
1049 qsearch({ 'table' => 'tax_class',
1051 'extra_sql' => "WHERE taxclass $compare",
1055 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ). '( '.
1056 join(' OR ', map { " taxclassnum = $_ " } @taxclassnum ). ' )'
1057 if ( @taxclassnum );
1059 unless ($params->{'showdisabled'}) {
1060 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1061 "( disabled = '' OR disabled IS NULL )";
1064 $query->{extra_sql} = $extra_sql;
1066 return ($query, "SELECT COUNT(*) FROM tax_rate $extra_sql");
1071 # Used by FS::Upgrade to migrate to a new database.
1075 sub _upgrade_data { # class method
1076 my ($self, %opts) = @_;
1079 warn "$me upgrading $self\n" if $DEBUG;
1081 my @column = qw ( tax excessrate usetax useexcessrate fee excessfee
1084 if ( $dbh->{Driver}->{Name} eq 'Pg' ) {
1086 eval "use DBI::Const::GetInfoType;";
1089 my $major_version = 0;
1090 $dbh->get_info( $GetInfoType{SQL_DBMS_VER} ) =~ /^(\d{2})/
1091 && ( $major_version = sprintf("%d", $1) );
1093 if ( $major_version > 7 ) {
1095 # ideally this would be supported in DBIx-DBSchema and friends
1097 foreach my $column ( @column ) {
1098 my $columndef = dbdef->table($self->table)->column($column);
1099 unless ($columndef->type eq 'numeric') {
1101 warn "updating tax_rate column $column to numeric\n" if $DEBUG;
1102 my $sql = "ALTER TABLE tax_rate ALTER $column TYPE numeric(14,8)";
1103 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
1104 $sth->execute or die $sth->errstr;
1106 warn "updating h_tax_rate column $column to numeric\n" if $DEBUG;
1107 $sql = "ALTER TABLE h_tax_rate ALTER $column TYPE numeric(14,8)";
1108 $sth = $dbh->prepare($sql) or die $dbh->errstr;
1109 $sth->execute or die $sth->errstr;
1116 warn "WARNING: tax_rate table upgrade unsupported for this Pg version\n";
1122 warn "WARNING: tax_rate table upgrade only supported for Pg 8+\n";
1134 Mixing automatic and manual editing works poorly at present.
1138 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base