4 use vars qw( @ISA $DEBUG $me
5 %tax_unittypes %tax_maxtypes %tax_basetypes %tax_authorities
6 %tax_passtypes %GetInfoType );
9 use DateTime::Format::Strptime;
10 use Storable qw( thaw );
18 use DBIx::DBSchema::Table;
19 use DBIx::DBSchema::Column;
20 use FS::Record qw( qsearch qsearchs dbh dbdef );
22 use FS::cust_bill_pkg;
23 use FS::cust_tax_location;
24 use FS::tax_rate_location;
25 use FS::part_pkg_taxrate;
26 use FS::part_pkg_taxproduct;
28 use FS::Misc qw( csv_from_fixed );
30 @ISA = qw( FS::Record );
33 $me = '[FS::tax_rate]';
37 FS::tax_rate - Object methods for tax_rate objects
43 $record = new FS::tax_rate \%hash;
44 $record = new FS::tax_rate { 'column' => 'value' };
46 $error = $record->insert;
48 $error = $new_record->replace($old_record);
50 $error = $record->delete;
52 $error = $record->check;
56 An FS::tax_rate object represents a tax rate, defined by locale.
57 FS::tax_rate inherits from FS::Record. The following fields are
64 primary key (assigned automatically for new tax rates)
68 a geographic location code provided by a tax data vendor
76 a location code provided by a tax authority
80 a foreign key into FS::tax_class - the type of tax
81 referenced but FS::part_pkg_taxrate
84 the time after which the tax applies
92 second bracket percentage
96 the amount to which the tax applies (first bracket)
100 a cap on the amount of tax if a cap exists
104 percentage on out of jurisdiction purchases
108 second bracket percentage on out of jurisdiction purchases
112 one of the values in %tax_unittypes
116 amount of tax per unit
120 second bracket amount of tax per unit
124 the number of units to which the fee applies (first bracket)
128 the most units to which fees apply (first and second brackets)
132 a value from %tax_maxtypes indicating how brackets accumulate (i.e. monthly, per invoice, etc)
136 if defined, printed on invoices instead of "Tax"
140 a value from %tax_authorities
144 a value from %tax_basetypes indicating the tax basis
148 a value from %tax_passtypes indicating how the tax should displayed to the customer
152 'Y', 'N', or blank indicating the tax can be passed to the customer
156 if 'Y', this tax does not apply to setup fees
160 if 'Y', this tax does not apply to recurring fees
164 if 'Y', has been manually edited
174 Creates a new tax rate. To add the tax rate to the database, see L<"insert">.
178 sub table { 'tax_rate'; }
182 Adds this tax rate to the database. If there is an error, returns the error,
183 otherwise returns false.
187 Deletes this tax rate from the database. If there is an error, returns the
188 error, otherwise returns false.
190 =item replace OLD_RECORD
192 Replaces the OLD_RECORD with this one in the database. If there is an error,
193 returns the error, otherwise returns false.
197 Checks all fields to make sure this is a valid tax rate. If there is an error,
198 returns the error, otherwise returns false. Called by the insert and replace
206 foreach (qw( taxbase taxmax )) {
207 $self->$_(0) unless $self->$_;
210 $self->ut_numbern('taxnum')
211 || $self->ut_text('geocode')
212 || $self->ut_textn('data_vendor')
213 || $self->ut_textn('location')
214 || $self->ut_foreign_key('taxclassnum', 'tax_class', 'taxclassnum')
215 || $self->ut_snumbern('effective_date')
216 || $self->ut_float('tax')
217 || $self->ut_floatn('excessrate')
218 || $self->ut_money('taxbase')
219 || $self->ut_money('taxmax')
220 || $self->ut_floatn('usetax')
221 || $self->ut_floatn('useexcessrate')
222 || $self->ut_numbern('unittype')
223 || $self->ut_floatn('fee')
224 || $self->ut_floatn('excessfee')
225 || $self->ut_floatn('feemax')
226 || $self->ut_numbern('maxtype')
227 || $self->ut_textn('taxname')
228 || $self->ut_numbern('taxauth')
229 || $self->ut_numbern('basetype')
230 || $self->ut_numbern('passtype')
231 || $self->ut_enum('passflag', [ '', 'Y', 'N' ])
232 || $self->ut_enum('setuptax', [ '', 'Y' ] )
233 || $self->ut_enum('recurtax', [ '', 'Y' ] )
234 || $self->ut_enum('inoutcity', [ '', 'I', 'O' ] )
235 || $self->ut_enum('inoutlocal', [ '', 'I', 'O' ] )
236 || $self->ut_enum('manual', [ '', 'Y' ] )
237 || $self->ut_enum('disabled', [ '', 'Y' ] )
238 || $self->SUPER::check
243 =item taxclass_description
245 Returns the human understandable value associated with the related
250 sub taxclass_description {
252 my $tax_class = qsearchs('tax_class', {'taxclassnum' => $self->taxclassnum });
253 $tax_class ? $tax_class->description : '';
258 Returns the human understandable value associated with the unittype column
262 %tax_unittypes = ( '0' => 'access line',
269 $tax_unittypes{$self->unittype};
274 Returns the human understandable value associated with the maxtype column
278 %tax_maxtypes = ( '0' => 'receipts per invoice',
279 '1' => 'receipts per item',
280 '2' => 'total utility charges per utility tax year',
281 '3' => 'total charges per utility tax year',
282 '4' => 'receipts per access line',
283 '9' => 'monthly receipts per location',
288 $tax_maxtypes{$self->maxtype};
293 Returns the human understandable value associated with the basetype column
297 %tax_basetypes = ( '0' => 'sale price',
298 '1' => 'gross receipts',
299 '2' => 'sales taxable telecom revenue',
300 '3' => 'minutes carried',
301 '4' => 'minutes billed',
302 '5' => 'gross operating revenue',
303 '6' => 'access line',
305 '8' => 'gross revenue',
306 '9' => 'portion gross receipts attributable to interstate service',
307 '10' => 'access line',
308 '11' => 'gross profits',
309 '12' => 'tariff rate',
311 '15' => 'prior year gross receipts',
316 $tax_basetypes{$self->basetype};
321 Returns the human understandable value associated with the taxauth column
325 %tax_authorities = ( '0' => 'federal',
330 '5' => 'county administered by state',
331 '6' => 'city administered by state',
332 '7' => 'city administered by county',
333 '8' => 'local administered by state',
334 '9' => 'local administered by county',
339 $tax_authorities{$self->taxauth};
344 Returns the human understandable value associated with the passtype column
348 %tax_passtypes = ( '0' => 'separate tax line',
349 '1' => 'separate surcharge line',
350 '2' => 'surcharge not separated',
351 '3' => 'included in base rate',
356 $tax_passtypes{$self->passtype};
359 =item taxline TAXABLES, [ OPTIONSHASH ]
361 Returns a listref of a name and an amount of tax calculated for the list
362 of packages/amounts referenced by TAXABLES. If an error occurs, a message
363 is returned as a scalar.
373 if (ref($_[0]) eq 'ARRAY') {
378 #exemptions would be broken in this case
381 my $name = $self->taxname;
382 $name = 'Other surcharges'
383 if ($self->passtype == 2);
386 if ( $self->disabled ) { # we always know how to handle disabled taxes
393 my $taxable_charged = 0;
394 my @cust_bill_pkg = grep { $taxable_charged += $_ unless ref; ref; }
397 warn "calculating taxes for ". $self->taxnum. " on ".
398 join (",", map { $_->pkgnum } @cust_bill_pkg)
401 if ($self->passflag eq 'N') {
402 # return "fatal: can't (yet) handle taxes not passed to the customer";
403 # until someone needs to track these in freeside
410 if ($self->maxtype != 0 && $self->maxtype != 9) {
411 return $self->_fatal_or_null( 'tax with "'.
412 $self->maxtype_name. '" threshold'
416 if ($self->maxtype == 9) {
418 $self->_fatal_or_null( 'tax with "'. $self->maxtype_name. '" threshold' );
422 # we treat gross revenue as gross receipts and expect the tax data
423 # to DTRT (i.e. tax on tax rules)
424 if ($self->basetype != 0 && $self->basetype != 1 &&
425 $self->basetype != 5 && $self->basetype != 6 &&
426 $self->basetype != 7 && $self->basetype != 8 &&
427 $self->basetype != 14
430 $self->_fatal_or_null( 'tax with "'. $self->basetype_name. '" basis' );
433 unless ($self->setuptax =~ /^Y$/i) {
434 $taxable_charged += $_->setup foreach @cust_bill_pkg;
436 unless ($self->recurtax =~ /^Y$/i) {
437 $taxable_charged += $_->recur foreach @cust_bill_pkg;
440 my $taxable_units = 0;
441 unless ($self->recurtax =~ /^Y$/i) {
442 if ($self->unittype == 0) {
444 foreach (@cust_bill_pkg) {
445 $taxable_units += $_->units
446 unless $seen{$_->pkgnum};
449 }elsif ($self->unittype == 1) {
450 return $self->_fatal_or_null( 'fee with minute unit type' );
451 }elsif ($self->unittype == 2) {
454 return $self->_fatal_or_null( 'unknown unit type in tax'. $self->taxnum );
459 # XXX insert exemption handling here
461 # the tax or fee is applied to taxbase or feebase and then
462 # the excessrate or excess fee is applied to taxmax or feemax
465 $amount += $taxable_charged * $self->tax;
466 $amount += $taxable_units * $self->fee;
468 warn "calculated taxes as [ $name, $amount ]\n"
479 my ($self, $error) = @_;
481 my $conf = new FS::Conf;
483 $error = "fatal: can't yet handle ". $error;
484 my $name = $self->taxname;
485 $name = 'Other surcharges'
486 if ($self->passtype == 2);
488 if ($conf->exists('ignore_incalculable_taxes')) {
490 return { name => $name, amount => 0 };
496 =item tax_on_tax CUST_MAIN
498 Returns a list of taxes which are candidates for taxing taxes for the
499 given customer (see L<FS::cust_main>)
505 my $cust_main = shift;
507 warn "looking up taxes on tax ". $self->taxnum. " for customer ".
511 my $geocode = $cust_main->geocode($self->data_vendor);
515 my $extra_sql = ' AND ('.
516 join(' OR ', map{ 'geocode = '. $dbh->quote(substr($geocode, 0, $_)) }
521 my $order_by = 'ORDER BY taxclassnum, length(geocode) desc';
522 my $select = 'DISTINCT ON(taxclassnum) *';
524 # should qsearch preface columns with the table to facilitate joins?
525 my @taxclassnums = map { $_->taxclassnum }
526 qsearch( { 'table' => 'part_pkg_taxrate',
528 'hashref' => { 'data_vendor' => $self->data_vendor,
529 'taxclassnumtaxed' => $self->taxclassnum,
531 'extra_sql' => $extra_sql,
532 'order_by' => $order_by,
535 return () unless @taxclassnums;
538 "AND (". join(' OR ', map { "taxclassnum = $_" } @taxclassnums ). ")";
540 qsearch({ 'table' => 'tax_rate',
541 'hashref' => { 'geocode' => $geocode, },
542 'extra_sql' => $extra_sql,
547 =item tax_rate_location
549 Returns an object representing the location associated with this tax
550 (see L<FS::tax_rate_location>)
554 sub tax_rate_location {
557 qsearchs({ 'table' => 'tax_rate_location',
558 'hashref' => { 'data_vendor' => $self->data_vendor,
559 'geocode' => $self->geocode,
563 new FS::tax_rate_location;
578 my ($param, $job) = @_;
580 my $fh = $param->{filehandle};
581 my $format = $param->{'format'};
589 my @column_lengths = ();
590 my @column_callbacks = ();
591 if ( $format eq 'cch-fixed' || $format eq 'cch-fixed-update' ) {
592 $format =~ s/-fixed//;
593 my $date_format = sub { my $r='';
594 /^(\d{4})(\d{2})(\d{2})$/ && ($r="$3/$2/$1");
597 my $trim = sub { my $r = shift; $r =~ s/^\s*//; $r =~ s/\s*$//; $r };
598 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 );
599 push @column_lengths, 1 if $format eq 'cch-update';
600 push @column_callbacks, $trim foreach (@column_lengths); # 5, 6, 15, 17 esp
601 $column_callbacks[8] = $date_format;
605 my ( $count, $last, $min_sec ) = (0, time, 5); #progressbar
606 if ( $job || scalar(@column_callbacks) ) {
608 csv_from_fixed(\$fh, \$count, \@column_lengths, \@column_callbacks);
609 return $error if $error;
613 if ( $format eq 'cch' || $format eq 'cch-update' ) {
614 @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax
615 excessrate effective_date taxauth taxtype taxcat taxname
616 usetax useexcessrate fee unittype feemax maxtype passflag
618 push @fields, 'actionflag' if $format eq 'cch-update';
623 $hash->{'actionflag'} ='I' if ($hash->{'data_vendor'} eq 'cch');
624 $hash->{'data_vendor'} ='cch';
625 my $parser = new DateTime::Format::Strptime( pattern => "%m/%d/%Y",
626 time_zone => 'floating',
628 my $dt = $parser->parse_datetime( $hash->{'effective_date'} );
629 $hash->{'effective_date'} = $dt ? $dt->epoch : '';
631 $hash->{$_} = sprintf("%.2f", $hash->{$_}) foreach qw( taxbase taxmax );
634 join(':', map{ $hash->{$_} } qw(taxtype taxcat) );
636 my %tax_class = ( 'data_vendor' => 'cch',
637 'taxclass' => $taxclassid,
640 my $tax_class = qsearchs( 'tax_class', \%tax_class );
641 return "Error updating tax rate: no tax class $taxclassid"
644 $hash->{'taxclassnum'} = $tax_class->taxclassnum;
646 foreach (qw( taxtype taxcat )) {
650 my %passflagmap = ( '0' => '',
654 $hash->{'passflag'} = $passflagmap{$hash->{'passflag'}}
655 if exists $passflagmap{$hash->{'passflag'}};
657 foreach (keys %$hash) {
658 $hash->{$_} = substr($hash->{$_}, 0, 80)
659 if length($hash->{$_}) > 80;
662 my $actionflag = delete($hash->{'actionflag'});
664 $hash->{'taxname'} =~ s/`/'/g;
665 $hash->{'taxname'} =~ s|\\|/|g;
667 return '' if $format eq 'cch'; # but not cch-update
669 if ($actionflag eq 'I') {
670 $insert{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
671 }elsif ($actionflag eq 'D') {
672 $delete{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
674 return "Unexpected action flag: ". $hash->{'actionflag'};
677 delete($hash->{$_}) for keys %$hash;
683 } elsif ( $format eq 'extended' ) {
684 die "unimplemented\n";
688 die "unknown format $format";
691 eval "use Text::CSV_XS;";
694 my $csv = new Text::CSV_XS;
698 local $SIG{HUP} = 'IGNORE';
699 local $SIG{INT} = 'IGNORE';
700 local $SIG{QUIT} = 'IGNORE';
701 local $SIG{TERM} = 'IGNORE';
702 local $SIG{TSTP} = 'IGNORE';
703 local $SIG{PIPE} = 'IGNORE';
705 my $oldAutoCommit = $FS::UID::AutoCommit;
706 local $FS::UID::AutoCommit = 0;
709 while ( defined($line=<$fh>) ) {
710 $csv->parse($line) or do {
711 $dbh->rollback if $oldAutoCommit;
712 return "can't parse: ". $csv->error_input();
715 if ( $job ) { # progress bar
716 if ( time - $min_sec > $last ) {
717 my $error = $job->update_statustext(
718 int( 100 * $imported / $count ). ",Importing tax rates"
721 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
728 my @columns = $csv->fields();
730 my %tax_rate = ( 'data_vendor' => $format );
731 foreach my $field ( @fields ) {
732 $tax_rate{$field} = shift @columns;
734 if ( scalar( @columns ) ) {
735 $dbh->rollback if $oldAutoCommit;
736 return "Unexpected trailing columns in line (wrong format?): $line";
739 my $error = &{$hook}(\%tax_rate);
741 $dbh->rollback if $oldAutoCommit;
745 if (scalar(keys %tax_rate)) { #inserts only, not updates for cch
747 my $tax_rate = new FS::tax_rate( \%tax_rate );
748 $error = $tax_rate->insert;
751 $dbh->rollback if $oldAutoCommit;
752 return "can't insert tax_rate for $line: $error";
761 for (grep { !exists($delete{$_}) } keys %insert) {
762 if ( $job ) { # progress bar
763 if ( time - $min_sec > $last ) {
764 my $error = $job->update_statustext(
765 int( 100 * $imported / $count ). ",Importing tax rates"
768 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
775 my $tax_rate = new FS::tax_rate( $insert{$_} );
776 my $error = $tax_rate->insert;
779 $dbh->rollback if $oldAutoCommit;
780 my $hashref = $insert{$_};
781 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
782 return "can't insert tax_rate for $line: $error";
788 for (grep { exists($delete{$_}) } keys %insert) {
789 if ( $job ) { # progress bar
790 if ( time - $min_sec > $last ) {
791 my $error = $job->update_statustext(
792 int( 100 * $imported / $count ). ",Importing tax rates"
795 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
802 my $old = qsearchs( 'tax_rate', $delete{$_} );
804 $dbh->rollback if $oldAutoCommit;
806 return "can't find tax_rate to replace for: ".
807 #join(" ", map { "$_ => ". $old->{$_} } @fields);
808 join(" ", map { "$_ => ". $old->{$_} } keys(%$old) );
810 my $new = new FS::tax_rate({ $old->hash, %{$insert{$_}}, 'manual' => '' });
811 $new->taxnum($old->taxnum);
812 my $error = $new->replace($old);
815 $dbh->rollback if $oldAutoCommit;
816 my $hashref = $insert{$_};
817 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
818 return "can't replace tax_rate for $line: $error";
825 for (grep { !exists($insert{$_}) } keys %delete) {
826 if ( $job ) { # progress bar
827 if ( time - $min_sec > $last ) {
828 my $error = $job->update_statustext(
829 int( 100 * $imported / $count ). ",Importing tax rates"
832 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
839 my $tax_rate = qsearchs( 'tax_rate', $delete{$_} );
841 $dbh->rollback if $oldAutoCommit;
842 $tax_rate = $delete{$_};
843 return "can't find tax_rate to delete for: ".
844 #join(" ", map { "$_ => ". $tax_rate->{$_} } @fields);
845 join(" ", map { "$_ => ". $tax_rate->{$_} } keys(%$tax_rate) );
847 my $error = $tax_rate->delete;
850 $dbh->rollback if $oldAutoCommit;
851 my $hashref = $delete{$_};
852 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
853 return "can't delete tax_rate for $line: $error";
859 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
861 return "Empty file!" unless ($imported || $format eq 'cch-update');
867 =item process_batch_import
869 Load a batch import as a queued JSRPC job
873 sub process_batch_import {
876 my $param = thaw(decode_base64(shift));
877 my $format = $param->{'format'}; #well... this is all cch specific
879 my $files = $param->{'uploaded_files'}
880 or die "No files provided.";
882 my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files;
884 if ($format eq 'cch' || $format eq 'cch-fixed') {
886 my $oldAutoCommit = $FS::UID::AutoCommit;
887 local $FS::UID::AutoCommit = 0;
890 my $have_location = 0;
892 my @list = ( 'GEOCODE', 'geofile', \&FS::tax_rate_location::batch_import,
893 'CODE', 'codefile', \&FS::tax_class::batch_import,
894 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
895 'ZIP', 'zipfile', \&FS::cust_tax_location::batch_import,
896 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
897 'DETAIL', 'detail', \&FS::tax_rate::batch_import,
899 while( scalar(@list) ) {
900 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
901 unless ($files{$file}) {
902 next if $name eq 'PLUS4';
903 $error = "No $name supplied";
904 $error = "Neither PLUS4 nor ZIP supplied"
905 if ($name eq 'ZIP' && !$have_location);
908 $have_location = 1 if $name eq 'PLUS4';
909 my $fmt = $format. ( $name eq 'ZIP' ? '-zip' : '' );
910 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
911 my $filename = "$dir/". $files{$file};
912 open my $fh, "< $filename" or $error ||= "Can't open $name file: $!";
914 $error ||= &{$import_sub}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
916 unlink $filename or warn "Can't delete $filename: $!";
920 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
923 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
926 }elsif ($format eq 'cch-update' || $format eq 'cch-fixed-update') {
928 my $oldAutoCommit = $FS::UID::AutoCommit;
929 local $FS::UID::AutoCommit = 0;
932 my @insert_list = ();
933 my @delete_list = ();
934 my @predelete_list = ();
936 my @list = ( 'GEOCODE', 'geofile', \&FS::tax_rate_location::batch_import,
937 'CODE', 'codefile', \&FS::tax_class::batch_import,
938 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
939 'ZIP', 'zipfile', \&FS::cust_tax_location::batch_import,
940 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
942 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
943 while( scalar(@list) ) {
944 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
945 unless ($files{$file}) {
946 my $vendor = $name eq 'ZIP' ? 'cch' : 'cch-zip';
947 next # update expected only for previously installed location data
948 if ( ($name eq 'PLUS4' || $name eq 'ZIP')
949 && !scalar( qsearch( { table => 'cust_tax_location',
950 hashref => { data_vendor => $vendor },
951 select => 'DISTINCT data_vendor',
956 $error = "No $name supplied";
959 my $filename = "$dir/". $files{$file};
960 open my $fh, "< $filename" or $error ||= "Can't open $name file $filename: $!";
961 unlink $filename or warn "Can't delete $filename: $!";
963 my $ifh = new File::Temp( TEMPLATE => "$name.insert.XXXXXXXX",
966 ) or die "can't open temp file: $!\n";
968 my $dfh = new File::Temp( TEMPLATE => "$name.delete.XXXXXXXX",
971 ) or die "can't open temp file: $!\n";
973 my $insert_pattern = ($format eq 'cch-update') ? qr/"I"\s*$/ : qr/I\s*$/;
974 my $delete_pattern = ($format eq 'cch-update') ? qr/"D"\s*$/ : qr/D\s*$/;
977 $handle = $ifh if $_ =~ /$insert_pattern/;
978 $handle = $dfh if $_ =~ /$delete_pattern/;
980 $error = "bad input line: $_" unless $handle;
989 push @insert_list, $name, $ifh->filename, $import_sub;
990 if ( $name eq 'GEOCODE' ) { #handle this whole ordering issue better
991 unshift @predelete_list, $name, $dfh->filename, $import_sub;
993 unshift @delete_list, $name, $dfh->filename, $import_sub;
998 while( scalar(@predelete_list) ) {
999 my ($name, $file, $import_sub) =
1000 (shift @predelete_list, shift @predelete_list, shift @predelete_list);
1002 my $fmt = $format. ( $name eq 'ZIP' ? '-zip' : '' );
1003 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
1005 &{$import_sub}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
1007 unlink $file or warn "Can't delete $file: $!";
1010 while( scalar(@insert_list) ) {
1011 my ($name, $file, $import_sub) =
1012 (shift @insert_list, shift @insert_list, shift @insert_list);
1014 my $fmt = $format. ( $name eq 'ZIP' ? '-zip' : '' );
1015 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
1017 &{$import_sub}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
1019 unlink $file or warn "Can't delete $file: $!";
1022 $error ||= "No DETAIL supplied"
1023 unless ($files{detail});
1024 open my $fh, "< $dir/". $files{detail}
1025 or $error ||= "Can't open DETAIL file: $!";
1027 &FS::tax_rate::batch_import({ 'filehandle' => $fh, 'format' => $format },
1030 unlink "$dir/". $files{detail} or warn "Can't delete $files{detail}: $!"
1033 while( scalar(@delete_list) ) {
1034 my ($name, $file, $import_sub) =
1035 (shift @delete_list, shift @delete_list, shift @delete_list);
1037 my $fmt = $format. ( $name eq 'ZIP' ? '-zip' : '' );
1038 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
1040 &{$import_sub}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
1042 unlink $file or warn "Can't delete $file: $!";
1046 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1049 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
1053 die "Unknown format: $format";
1058 =item process_download_and_reload
1060 Download and process a tax update as a queued JSRPC job after wiping the
1061 existing wipable tax data.
1065 sub process_download_and_reload {
1068 my $param = thaw(decode_base64($_[0]));
1069 my $format = $param->{'format'}; #well... this is all cch specific
1071 my ( $count, $last, $min_sec, $imported ) = (0, time, 5, 0); #progressbar
1074 if ( $job ) { # progress bar
1075 my $error = $job->update_statustext( int( 100 * $imported / $count ) );
1076 die $error if $error;
1079 my $oldAutoCommit = $FS::UID::AutoCommit;
1080 local $FS::UID::AutoCommit = 0;
1085 "SELECT count(*) FROM part_pkg_taxoverride JOIN tax_class ".
1086 "USING (taxclassnum) WHERE data_vendor = '$format'";
1087 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
1089 or die "Unexpected error executing statement $sql: ". $sth->errstr;
1090 die "Don't (yet) know how to handle part_pkg_taxoverride records."
1091 if $sth->fetchrow_arrayref->[0];
1093 # really should get a table EXCLUSIVE lock here
1095 #remember disabled taxes
1096 my %disabled_tax_rate = ();
1097 my @items = qsearch( { table => 'tax_rate',
1098 hashref => { disabled => 'Y',
1099 data_vendor => $format,
1101 select => 'geocode, taxclassnum',
1104 $count = scalar(@items);
1105 foreach my $tax_rate ( @items ) {
1106 if ( time - $min_sec > $last ) {
1107 my $error = $job->update_statustext(
1108 int( 100 * $imported / $count ). ",Remembering disabled taxes"
1111 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1118 qsearchs( 'tax_class', { taxclassnum => $tax_rate->taxclassnum } );
1119 unless ( $tax_class ) {
1120 warn "failed to find tax_class ". $tax_rate->taxclassnum;
1123 $disabled_tax_rate{$tax_rate->geocode. ':'. $tax_class->taxclass} = 1;
1126 #remember tax products
1127 # XXX FIXME this loop only works when cch is the only data provider
1128 my %taxproduct = ();
1129 my $extra_sql = "WHERE taxproductnum IS NOT NULL OR ".
1130 "0 < ( SELECT count(*) from part_pkg_option WHERE ".
1131 " part_pkg_option.pkgpart = part_pkg.pkgpart AND ".
1132 " optionname LIKE 'usage_taxproductnum_%' AND ".
1133 " optionvalue != '' )";
1134 @items = qsearch( { table => 'part_pkg',
1135 select => 'DISTINCT pkgpart,taxproductnum',
1137 extra_sql => $extra_sql,
1140 $count = scalar(@items);
1142 foreach my $part_pkg ( @items ) {
1143 if ( time - $min_sec > $last ) {
1144 my $error = $job->update_statustext(
1145 int( 100 * $imported / $count ). ",Remembering tax products"
1148 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1154 warn "working with package part ". $part_pkg->pkgpart.
1155 "which has a taxproductnum of ". $part_pkg->taxproductnum. "\n" if $DEBUG;
1156 my $part_pkg_taxproduct = $part_pkg->taxproduct('');
1157 $taxproduct{$part_pkg->pkgpart}{''} = $part_pkg_taxproduct->taxproduct
1158 if $part_pkg_taxproduct;
1160 foreach my $option ( $part_pkg->part_pkg_option ) {
1161 next unless $option->optionname =~ /^usage_taxproductnum_(\w)$/;
1164 $part_pkg_taxproduct = $part_pkg->taxproduct($class);
1165 $taxproduct{$part_pkg->pkgpart}{$class} = $part_pkg_taxproduct->taxproduct
1166 if $part_pkg_taxproduct;
1170 #wipe out the old data
1171 $error = $job->update_statustext( "0,Removing old tax data" );
1173 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1176 foreach my $tax_rate_location ( qsearch( 'tax_rate_location',
1177 { data_vendor => $format,
1183 $tax_rate_location->disabled('Y');
1184 my $error = $tax_rate_location->replace;
1186 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1191 local $FS::part_pkg_taxproduct::delete_kludge = 1;
1193 tax_rate part_pkg_taxrate part_pkg_taxproduct tax_class cust_tax_location
1195 foreach my $table ( @table ) {
1197 # my $primary_key = dbdef->table($table)->primary_key;
1198 # my $sql = "SELECT $primary_key FROM $table WHERE data_vendor = ".
1199 my $sql = "DELETE FROM $table WHERE data_vendor = ".
1200 $dbh->quote($format);
1201 my $sth = $dbh->prepare($sql);
1203 $error = $dbh->errstr;
1204 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1207 unless ($sth->execute) {
1208 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1209 die "Failed to execute $sql: ". $sth->errstr;
1211 # foreach my $row ( @{ $sth->fetchall_arrayref } ) {
1212 # my $record = qsearchs( $table, { $primary_key => $row->[0] } )
1213 # or die "Failed to find $table with $primary_key ". $row->[0];
1214 # my $error = $record->delete;
1216 # $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1222 if ( $format eq 'cch' ) {
1223 foreach my $cust_tax_location ( qsearch( 'cust_tax_location',
1224 { data_vendor => "$format-zip" }
1228 my $error = $cust_tax_location->delete;
1230 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1237 my $statement = ' &process_download_and_update($job, @_); ';
1240 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1244 #restore taxproducts
1245 $count = scalar(keys %taxproduct);
1247 foreach my $pkgpart ( keys %taxproduct ) {
1248 warn "restoring taxproductnums on pkgpart $pkgpart\n" if $DEBUG;
1249 if ( time - $min_sec > $last ) {
1250 my $error = $job->update_statustext(
1251 int( 100 * $imported / $count ). ",Restoring tax products"
1254 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1261 my $part_pkg = qsearchs('part_pkg', { pkgpart => $pkgpart } );
1262 unless ( $part_pkg ) {
1263 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1264 die "somehow failed to find part_pkg with pkgpart $pkgpart!\n";
1267 my %options = $part_pkg->options;
1268 my %pkg_svc = map { $_->svcpart => $_->quantity } $part_pkg->pkg_svc;
1269 my $primary_svc = $part_pkg->svcpart;
1270 my $new = new FS::part_pkg { $part_pkg->hash };
1272 foreach my $class ( keys %{ $taxproduct{$pkgpart} } ) {
1273 warn "working with class '$class'\n" if $DEBUG;
1274 my $part_pkg_taxproduct =
1275 qsearchs( 'part_pkg_taxproduct',
1276 { taxproduct => $taxproduct{$pkgpart}{$class},
1277 data_vendor => $format,
1281 unless ( $part_pkg_taxproduct ) {
1282 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1283 die "failed to find part_pkg_taxproduct ($taxproduct{pkgpart}{$class})".
1284 " for pkgpart $pkgpart\n";
1287 if ( $class eq '' ) {
1288 $new->taxproductnum($part_pkg_taxproduct->taxproductnum);
1292 $options{"usage_taxproductnum_$class"} =
1293 $part_pkg_taxproduct->taxproductnum;
1297 my $error = $new->replace( $part_pkg,
1298 'pkg_svc' => \%pkg_svc,
1299 'primary_svc' => $primary_svc,
1300 'options' => \%options,
1304 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1310 $count = scalar(keys %disabled_tax_rate);
1312 foreach my $key (keys %disabled_tax_rate) {
1313 if ( time - $min_sec > $last ) {
1314 my $error = $job->update_statustext(
1315 int( 100 * $imported / $count ). ",Disabling tax rates"
1318 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1324 my ($geocode,$taxclass) = split /:/, $key, 2;
1325 my @tax_class = qsearch( 'tax_class', { data_vendor => $format,
1326 taxclass => $taxclass,
1328 if (scalar(@tax_class) > 1) {
1329 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1330 die "found multiple tax_class records for format $format class $taxclass";
1333 unless (scalar(@tax_class)) {
1334 warn "no tax_class for format $format class $taxclass\n";
1339 qsearch('tax_rate', { data_vendor => $format,
1340 geocode => $geocode,
1341 taxclassnum => $tax_class[0]->taxclassnum,
1345 if (scalar(@tax_rate) > 1) {
1346 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1347 die "found multiple tax_rate records for format $format geocode $geocode".
1348 " and taxclass $taxclass ( taxclassnum ". $tax_class[0]->taxclassnum.
1352 if (scalar(@tax_rate)) {
1353 $tax_rate[0]->disabled('Y');
1354 my $error = $tax_rate[0]->replace;
1356 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1364 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
1368 =item process_download_and_update
1370 Download and process a tax update as a queued JSRPC job
1374 sub process_download_and_update {
1377 my $param = thaw(decode_base64(shift));
1378 my $format = $param->{'format'}; #well... this is all cch specific
1380 my ( $count, $last, $min_sec, $imported ) = (0, time, 5, 0); #progressbar
1383 if ( $job ) { # progress bar
1384 my $error = $job->update_statustext( int( 100 * $imported / $count ) );
1385 die $error if $error;
1388 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/taxdata';
1390 mkdir $dir or die "can't create $dir: $!\n";
1393 if ($format eq 'cch') {
1395 eval "use Text::CSV_XS;";
1401 my $conffile = '%%%FREESIDE_CONF%%%/cchconf';
1402 my $conffh = new IO::File "<$conffile" or die "can't open $conffile: $!\n";
1403 my ( $urls, $secret, $states ) =
1404 map { /^(.*)$/ or die "bad config line in $conffile: $_\n"; $1 }
1409 my $oldAutoCommit = $FS::UID::AutoCommit;
1410 local $FS::UID::AutoCommit = 0;
1414 # really should get a table EXCLUSIVE lock here
1415 # check if initial import or update
1417 my $sql = "SELECT count(*) from tax_rate WHERE data_vendor='$format'";
1418 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
1419 $sth->execute() or die $sth->errstr;
1420 my $upgrade = $sth->fetchrow_arrayref->[0];
1422 # create cache and/or rotate old tax data
1427 opendir(my $dirh, "$dir.4") or die "failed to open $dir.4: $!\n";
1428 foreach my $file (readdir($dirh)) {
1429 unlink "$dir.4/$file" if (-f "$dir.4/$file");
1436 if ( -e "$dir.$_" ) {
1437 rename "$dir.$_", "$dir.". ($_+1) or die "can't rename $dir.$_: $!\n";
1440 rename "$dir", "$dir.1" or die "can't rename $dir: $!\n";
1444 die "can't find previous tax data\n" if $upgrade;
1448 mkdir "$dir.new" or die "can't create $dir.new: $!\n";
1450 # fetch and unpack the zip files
1452 my $ua = new LWP::UserAgent;
1453 foreach my $url (split ',', $urls) {
1454 my @name = split '/', $url; #somewhat restrictive
1455 my $name = pop @name;
1456 $name =~ /(.*)/; # untaint that which we trust;
1459 open my $taxfh, ">$dir.new/$name" or die "Can't open $dir.new/$name: $!\n";
1461 my $res = $ua->request(
1462 new HTTP::Request( GET => $url),
1463 sub { #my ($data, $response_object) = @_;
1464 print $taxfh $_[0] or die "Can't write to $dir.new/$name: $!\n";
1465 my $content_length = $_[1]->content_length;
1466 $imported += length($_[0]);
1467 if ( time - $min_sec > $last ) {
1468 my $error = $job->update_statustext(
1469 ($content_length ? int(100 * $imported/$content_length) : 0 ).
1470 ",Downloading data from CCH"
1472 die $error if $error;
1477 die "download of $url failed: ". $res->status_line
1478 unless $res->is_success;
1481 my $error = $job->update_statustext( "0,Unpacking data" );
1482 die $error if $error;
1483 $secret =~ /(.*)/; # untaint that which we trust;
1485 system('unzip', "-P", $secret, "-d", "$dir.new", "$dir.new/$name") == 0
1486 or die "unzip -P $secret -d $dir.new $dir.new/$name failed";
1487 #unlink "$dir.new/$name";
1490 # extract csv files from the dbf files
1492 foreach my $name ( qw( code detail geocode plus4 txmatrix zip ) ) {
1493 my $error = $job->update_statustext( "0,Unpacking $name" );
1494 die $error if $error;
1495 warn "opening $dir.new/$name.dbf\n" if $DEBUG;
1496 my $table = new XBase 'name' => "$dir.new/$name.dbf";
1497 die "failed to access $dir.new/$name.dbf: ". XBase->errstr
1498 unless defined($table);
1499 $count = $table->last_record; # approximately;
1501 open my $csvfh, ">$dir.new/$name.txt"
1502 or die "failed to open $dir.new/$name.txt: $!\n";
1504 my $csv = new Text::CSV_XS { 'always_quote' => 1 };
1505 my @fields = $table->field_names;
1506 my $cursor = $table->prepare_select;
1508 sub { my $date = shift;
1509 $date =~ /^(\d{4})(\d{2})(\d{2})$/ && ($date = "$2/$3/$1");
1512 while (my $row = $cursor->fetch_hashref) {
1513 $csv->combine( map { ($table->field_type($_) eq 'D')
1514 ? &{$format_date}($row->{$_})
1519 print $csvfh $csv->string, "\n";
1521 if ( time - $min_sec > $last ) {
1522 my $error = $job->update_statustext(
1523 int(100 * $imported/$count). ",Unpacking $name"
1525 die $error if $error;
1533 # generate the diff files
1535 my @insert_list = ();
1536 my @delete_list = ();
1537 my @predelete_list = ();
1540 'geocode', \&FS::tax_rate_location::batch_import,
1541 'code', \&FS::tax_class::batch_import,
1542 'plus4', \&FS::cust_tax_location::batch_import,
1543 'zip', \&FS::cust_tax_location::batch_import,
1544 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
1545 'detail', \&FS::tax_rate::batch_import,
1548 while( scalar(@list) ) {
1549 my ( $name, $method ) = ( shift @list, shift @list );
1552 my $error = $job->update_statustext( "0,Comparing to previous $name" );
1553 die $error if $error;
1555 warn "processing $dir.new/$name.txt\n" if $DEBUG;
1558 open my $oldcsvfh, "$dir.1/$name.txt"
1559 or die "failed to open $dir.1/$name.txt: $!\n";
1561 while(<$oldcsvfh>) {
1568 open my $newcsvfh, "$dir.new/$name.txt"
1569 or die "failed to open $dir.new/$name.txt: $!\n";
1571 my $ifh = new File::Temp( TEMPLATE => "$name.insert.XXXXXXXX",
1574 ) or die "can't open temp file: $!\n";
1576 my $dfh = new File::Temp( TEMPLATE => "$name.delete.XXXXXXXX",
1579 ) or die "can't open temp file: $!\n";
1581 while(<$newcsvfh>) {
1583 if (exists($oldlines{$_})) {
1586 print $ifh $_, ',"I"', "\n";
1591 if ($name eq 'detail') {
1592 for (keys %oldlines) { # one file for rate details
1593 print $ifh $_, ',"D"', "\n" if $oldlines{$_};
1596 for (keys %oldlines) {
1597 print $dfh $_, ',"D"', "\n" if $oldlines{$_};
1602 push @insert_list, $name, $ifh->filename, $method;
1603 if ( $name eq 'geocode' ) {
1604 unshift @predelete_list, $name, $dfh->filename, $method
1605 unless $name eq 'detail';
1607 unshift @delete_list, $name, $dfh->filename, $method
1608 unless $name eq 'detail';
1615 while( scalar(@predelete_list) ) {
1616 my ($name, $file, $method) =
1617 (shift @predelete_list, shift @predelete_list, shift @predelete_list);
1619 my $fmt = "$format-update";
1620 $fmt = $fmt. ( $name eq 'zip' ? '-zip' : '' );
1621 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
1623 &{$method}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
1625 #unlink $file or warn "Can't delete $file: $!";
1628 while( scalar(@insert_list) ) {
1629 my ($name, $file, $method) =
1630 (shift @insert_list, shift @insert_list, shift @insert_list);
1632 my $fmt = "$format-update";
1633 $fmt = $fmt. ( $name eq 'zip' ? '-zip' : '' );
1634 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
1636 &{$method}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
1638 #unlink $file or warn "Can't delete $file: $!";
1641 while( scalar(@delete_list) ) {
1642 my ($name, $file, $method) =
1643 (shift @delete_list, shift @delete_list, shift @delete_list);
1645 my $fmt = "$format-update";
1646 $fmt = $fmt. ( $name eq 'zip' ? '-zip' : '' );
1647 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
1649 &{$method}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
1651 #unlink $file or warn "Can't delete $file: $!";
1655 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1658 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
1661 rename "$dir.new", "$dir"
1662 or die "cch tax update processed, but can't rename $dir.new: $!\n";
1665 die "Unknown format: $format";
1669 =item browse_queries PARAMS
1671 Returns a list consisting of a hashref suited for use as the argument
1672 to qsearch, and sql query string. Each is based on the PARAMS hashref
1673 of keys and values which frequently would be passed as C<scalar($cgi->Vars)>
1674 from a form. This conveniently creates the query hashref and count_query
1675 string required by the browse and search elements. As a side effect,
1676 the PARAMS hashref is untainted and keys with unexpected values are removed.
1680 sub browse_queries {
1684 'table' => 'tax_rate',
1686 'order_by' => 'ORDER BY geocode, taxclassnum',
1691 if ( $params->{data_vendor} =~ /^(\w+)$/ ) {
1692 $extra_sql .= ' WHERE data_vendor = '. dbh->quote($1);
1694 delete $params->{data_vendor};
1697 if ( $params->{geocode} =~ /^(\w+)$/ ) {
1698 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1699 'geocode LIKE '. dbh->quote($1.'%');
1701 delete $params->{geocode};
1704 if ( $params->{taxclassnum} =~ /^(\d+)$/ &&
1705 qsearchs( 'tax_class', {'taxclassnum' => $1} )
1708 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1709 ' taxclassnum = '. dbh->quote($1)
1711 delete $params->{taxclassnun};
1715 if ( $params->{tax_type} =~ /^(\d+)$/ );
1716 delete $params->{tax_type}
1720 if ( $params->{tax_cat} =~ /^(\d+)$/ );
1721 delete $params->{tax_cat}
1724 my @taxclassnum = ();
1725 if ($tax_type || $tax_cat ) {
1726 my $compare = "LIKE '". ( $tax_type || "%" ). ":". ( $tax_cat || "%" ). "'";
1727 $compare = "= '$tax_type:$tax_cat'" if ($tax_type && $tax_cat);
1728 @taxclassnum = map { $_->taxclassnum }
1729 qsearch({ 'table' => 'tax_class',
1731 'extra_sql' => "WHERE taxclass $compare",
1735 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ). '( '.
1736 join(' OR ', map { " taxclassnum = $_ " } @taxclassnum ). ' )'
1737 if ( @taxclassnum );
1739 unless ($params->{'showdisabled'}) {
1740 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1741 "( disabled = '' OR disabled IS NULL )";
1744 $query->{extra_sql} = $extra_sql;
1746 return ($query, "SELECT COUNT(*) FROM tax_rate $extra_sql");
1751 # Used by FS::Upgrade to migrate to a new database.
1755 sub _upgrade_data { # class method
1756 my ($self, %opts) = @_;
1759 warn "$me upgrading $self\n" if $DEBUG;
1761 my @column = qw ( tax excessrate usetax useexcessrate fee excessfee
1764 if ( $dbh->{Driver}->{Name} eq 'Pg' ) {
1766 eval "use DBI::Const::GetInfoType;";
1769 my $major_version = 0;
1770 $dbh->get_info( $GetInfoType{SQL_DBMS_VER} ) =~ /^(\d{2})/
1771 && ( $major_version = sprintf("%d", $1) );
1773 if ( $major_version > 7 ) {
1775 # ideally this would be supported in DBIx-DBSchema and friends
1777 foreach my $column ( @column ) {
1778 my $columndef = dbdef->table($self->table)->column($column);
1779 unless ($columndef->type eq 'numeric') {
1781 warn "updating tax_rate column $column to numeric\n" if $DEBUG;
1782 my $sql = "ALTER TABLE tax_rate ALTER $column TYPE numeric(14,8)";
1783 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
1784 $sth->execute or die $sth->errstr;
1786 warn "updating h_tax_rate column $column to numeric\n" if $DEBUG;
1787 $sql = "ALTER TABLE h_tax_rate ALTER $column TYPE numeric(14,8)";
1788 $sth = $dbh->prepare($sql) or die $dbh->errstr;
1789 $sth->execute or die $sth->errstr;
1794 } elsif ( $dbh->{pg_server_version} =~ /^704/ ) {
1796 # ideally this would be supported in DBIx-DBSchema and friends
1798 foreach my $column ( @column ) {
1799 my $columndef = dbdef->table($self->table)->column($column);
1800 unless ($columndef->type eq 'numeric') {
1802 warn "updating tax_rate column $column to numeric\n" if $DEBUG;
1804 foreach my $table ( qw( tax_rate h_tax_rate ) ) {
1806 my $sql = "ALTER TABLE $table RENAME $column TO old_$column";
1807 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
1808 $sth->execute or die $sth->errstr;
1810 my $def = dbdef->table($table)->column($column);
1811 $def->type('numeric');
1812 $def->length('14,8');
1813 my $null = $def->null;
1816 $sql = "ALTER TABLE $table ADD COLUMN ". $def->line($dbh);
1817 $sth = $dbh->prepare($sql) or die $dbh->errstr;
1818 $sth->execute or die $sth->errstr;
1820 $sql = "UPDATE $table SET $column = CAST( old_$column AS numeric )";
1821 $sth = $dbh->prepare($sql) or die $dbh->errstr;
1822 $sth->execute or die $sth->errstr;
1824 unless ( $null eq 'NULL' ) {
1825 $sql = "ALTER TABLE $table ALTER $column SET NOT NULL";
1826 $sth = $dbh->prepare($sql) or die $dbh->errstr;
1827 $sth->execute or die $sth->errstr;
1830 $sql = "ALTER TABLE $table DROP old_$column";
1831 $sth = $dbh->prepare($sql) or die $dbh->errstr;
1832 $sth->execute or die $sth->errstr;
1840 warn "WARNING: tax_rate table upgrade unsupported for this Pg version\n";
1846 warn "WARNING: tax_rate table upgrade only supported for Pg 8+\n";
1858 Mixing automatic and manual editing works poorly at present.
1862 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base