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 );
16 use DBIx::DBSchema::Table;
17 use DBIx::DBSchema::Column;
18 use FS::Record qw( qsearch qsearchs dbh dbdef );
20 use FS::cust_bill_pkg;
21 use FS::cust_tax_location;
22 use FS::tax_rate_location;
23 use FS::part_pkg_taxrate;
25 use FS::Misc qw( csv_from_fixed );
27 @ISA = qw( FS::Record );
30 $me = '[FS::tax_rate]';
34 FS::tax_rate - Object methods for tax_rate objects
40 $record = new FS::tax_rate \%hash;
41 $record = new FS::tax_rate { 'column' => 'value' };
43 $error = $record->insert;
45 $error = $new_record->replace($old_record);
47 $error = $record->delete;
49 $error = $record->check;
53 An FS::tax_rate object represents a tax rate, defined by locale.
54 FS::tax_rate inherits from FS::Record. The following fields are
61 primary key (assigned automatically for new tax rates)
65 a geographic location code provided by a tax data vendor
73 a location code provided by a tax authority
77 a foreign key into FS::tax_class - the type of tax
78 referenced but FS::part_pkg_taxrate
81 the time after which the tax applies
89 second bracket percentage
93 the amount to which the tax applies (first bracket)
97 a cap on the amount of tax if a cap exists
101 percentage on out of jurisdiction purchases
105 second bracket percentage on out of jurisdiction purchases
109 one of the values in %tax_unittypes
113 amount of tax per unit
117 second bracket amount of tax per unit
121 the number of units to which the fee applies (first bracket)
125 the most units to which fees apply (first and second brackets)
129 a value from %tax_maxtypes indicating how brackets accumulate (i.e. monthly, per invoice, etc)
133 if defined, printed on invoices instead of "Tax"
137 a value from %tax_authorities
141 a value from %tax_basetypes indicating the tax basis
145 a value from %tax_passtypes indicating how the tax should displayed to the customer
149 'Y', 'N', or blank indicating the tax can be passed to the customer
153 if 'Y', this tax does not apply to setup fees
157 if 'Y', this tax does not apply to recurring fees
161 if 'Y', has been manually edited
171 Creates a new tax rate. To add the tax rate to the database, see L<"insert">.
175 sub table { 'tax_rate'; }
179 Adds this tax rate to the database. If there is an error, returns the error,
180 otherwise returns false.
184 Deletes this tax rate from the database. If there is an error, returns the
185 error, otherwise returns false.
187 =item replace OLD_RECORD
189 Replaces the OLD_RECORD with this one in the database. If there is an error,
190 returns the error, otherwise returns false.
194 Checks all fields to make sure this is a valid tax rate. If there is an error,
195 returns the error, otherwise returns false. Called by the insert and replace
203 foreach (qw( taxbase taxmax )) {
204 $self->$_(0) unless $self->$_;
207 $self->ut_numbern('taxnum')
208 || $self->ut_text('geocode')
209 || $self->ut_textn('data_vendor')
210 || $self->ut_textn('location')
211 || $self->ut_foreign_key('taxclassnum', 'tax_class', 'taxclassnum')
212 || $self->ut_snumbern('effective_date')
213 || $self->ut_float('tax')
214 || $self->ut_floatn('excessrate')
215 || $self->ut_money('taxbase')
216 || $self->ut_money('taxmax')
217 || $self->ut_floatn('usetax')
218 || $self->ut_floatn('useexcessrate')
219 || $self->ut_numbern('unittype')
220 || $self->ut_floatn('fee')
221 || $self->ut_floatn('excessfee')
222 || $self->ut_floatn('feemax')
223 || $self->ut_numbern('maxtype')
224 || $self->ut_textn('taxname')
225 || $self->ut_numbern('taxauth')
226 || $self->ut_numbern('basetype')
227 || $self->ut_numbern('passtype')
228 || $self->ut_enum('passflag', [ '', 'Y', 'N' ])
229 || $self->ut_enum('setuptax', [ '', 'Y' ] )
230 || $self->ut_enum('recurtax', [ '', 'Y' ] )
231 || $self->ut_enum('manual', [ '', 'Y' ] )
232 || $self->ut_enum('disabled', [ '', 'Y' ] )
233 || $self->SUPER::check
238 =item taxclass_description
240 Returns the human understandable value associated with the related
245 sub taxclass_description {
247 my $tax_class = qsearchs('tax_class', {'taxclassnum' => $self->taxclassnum });
248 $tax_class ? $tax_class->description : '';
253 Returns the human understandable value associated with the unittype column
257 %tax_unittypes = ( '0' => 'access line',
264 $tax_unittypes{$self->unittype};
269 Returns the human understandable value associated with the maxtype column
273 %tax_maxtypes = ( '0' => 'receipts per invoice',
274 '1' => 'receipts per item',
275 '2' => 'total utility charges per utility tax year',
276 '3' => 'total charges per utility tax year',
277 '4' => 'receipts per access line',
278 '9' => 'monthly receipts per location',
283 $tax_maxtypes{$self->maxtype};
288 Returns the human understandable value associated with the basetype column
292 %tax_basetypes = ( '0' => 'sale price',
293 '1' => 'gross receipts',
294 '2' => 'sales taxable telecom revenue',
295 '3' => 'minutes carried',
296 '4' => 'minutes billed',
297 '5' => 'gross operating revenue',
298 '6' => 'access line',
300 '8' => 'gross revenue',
301 '9' => 'portion gross receipts attributable to interstate service',
302 '10' => 'access line',
303 '11' => 'gross profits',
304 '12' => 'tariff rate',
306 '15' => 'prior year gross receipts',
311 $tax_basetypes{$self->basetype};
316 Returns the human understandable value associated with the taxauth column
320 %tax_authorities = ( '0' => 'federal',
325 '5' => 'county administered by state',
326 '6' => 'city administered by state',
327 '7' => 'city administered by county',
328 '8' => 'local administered by state',
329 '9' => 'local administered by county',
334 $tax_authorities{$self->taxauth};
339 Returns the human understandable value associated with the passtype column
343 %tax_passtypes = ( '0' => 'separate tax line',
344 '1' => 'separate surcharge line',
345 '2' => 'surcharge not separated',
346 '3' => 'included in base rate',
351 $tax_passtypes{$self->passtype};
354 =item taxline TAXABLES, [ OPTIONSHASH ]
356 Returns a listref of a name and an amount of tax calculated for the list
357 of packages/amounts referenced by TAXABLES. If an error occurs, a message
358 is returned as a scalar.
368 if (ref($_[0]) eq 'ARRAY') {
373 #exemptions would be broken in this case
376 my $name = $self->taxname;
377 $name = 'Other surcharges'
378 if ($self->passtype == 2);
381 if ( $self->disabled ) { # we always know how to handle disabled taxes
388 my $taxable_charged = 0;
389 my @cust_bill_pkg = grep { $taxable_charged += $_ unless ref; ref; }
392 warn "calculating taxes for ". $self->taxnum. " on ".
393 join (",", map { $_->pkgnum } @cust_bill_pkg)
396 if ($self->passflag eq 'N') {
397 # return "fatal: can't (yet) handle taxes not passed to the customer";
398 # until someone needs to track these in freeside
405 if ($self->maxtype != 0 && $self->maxtype != 9) {
406 return $self->_fatal_or_null( 'tax with "'.
407 $self->maxtype_name. '" threshold'
411 if ($self->maxtype == 9) {
413 $self->_fatal_or_null( 'tax with "'. $self->maxtype_name. '" threshold' );
417 # we treat gross revenue as gross receipts and expect the tax data
418 # to DTRT (i.e. tax on tax rules)
419 if ($self->basetype != 0 && $self->basetype != 1 &&
420 $self->basetype != 5 && $self->basetype != 6 &&
421 $self->basetype != 7 && $self->basetype != 8 &&
422 $self->basetype != 14
425 $self->_fatal_or_null( 'tax with "'. $self->basetype_name. '" basis' );
428 unless ($self->setuptax =~ /^Y$/i) {
429 $taxable_charged += $_->setup foreach @cust_bill_pkg;
431 unless ($self->recurtax =~ /^Y$/i) {
432 $taxable_charged += $_->recur foreach @cust_bill_pkg;
435 my $taxable_units = 0;
436 unless ($self->recurtax =~ /^Y$/i) {
437 if ($self->unittype == 0) {
439 foreach (@cust_bill_pkg) {
440 $taxable_units += $_->units
441 unless $seen{$_->pkgnum};
444 }elsif ($self->unittype == 1) {
445 return $self->_fatal_or_null( 'fee with minute unit type' );
446 }elsif ($self->unittype == 2) {
449 return $self->_fatal_or_null( 'unknown unit type in tax'. $self->taxnum );
454 # XXX insert exemption handling here
456 # the tax or fee is applied to taxbase or feebase and then
457 # the excessrate or excess fee is applied to taxmax or feemax
460 $amount += $taxable_charged * $self->tax;
461 $amount += $taxable_units * $self->fee;
463 warn "calculated taxes as [ $name, $amount ]\n"
474 my ($self, $error) = @_;
476 my $conf = new FS::Conf;
478 $error = "fatal: can't yet handle ". $error;
479 my $name = $self->taxname;
480 $name = 'Other surcharges'
481 if ($self->passtype == 2);
483 if ($conf->exists('ignore_incalculable_taxes')) {
485 return { name => $name, amount => 0 };
491 =item tax_on_tax CUST_MAIN
493 Returns a list of taxes which are candidates for taxing taxes for the
494 given customer (see L<FS::cust_main>)
500 my $cust_main = shift;
502 warn "looking up taxes on tax ". $self->taxnum. " for customer ".
506 my $geocode = $cust_main->geocode($self->data_vendor);
510 my $extra_sql = ' AND ('.
511 join(' OR ', map{ 'geocode = '. $dbh->quote(substr($geocode, 0, $_)) }
516 my $order_by = 'ORDER BY taxclassnum, length(geocode) desc';
517 my $select = 'DISTINCT ON(taxclassnum) *';
519 # should qsearch preface columns with the table to facilitate joins?
520 my @taxclassnums = map { $_->taxclassnum }
521 qsearch( { 'table' => 'part_pkg_taxrate',
523 'hashref' => { 'data_vendor' => $self->data_vendor,
524 'taxclassnumtaxed' => $self->taxclassnum,
526 'extra_sql' => $extra_sql,
527 'order_by' => $order_by,
530 return () unless @taxclassnums;
533 "AND (". join(' OR ', map { "taxclassnum = $_" } @taxclassnums ). ")";
535 qsearch({ 'table' => 'tax_rate',
536 'hashref' => { 'geocode' => $geocode, },
537 'extra_sql' => $extra_sql,
542 =item tax_rate_location
544 Returns an object representing the location associated with this tax
545 (see L<FS::tax_rate_location>)
549 sub tax_rate_location {
552 qsearchs({ 'table' => 'tax_rate_location',
553 'hashref' => { 'data_vendor' => $self->data_vendor,
554 'geocode' => $self->geocode,
558 new FS::tax_rate_location;
573 my ($param, $job) = @_;
575 my $fh = $param->{filehandle};
576 my $format = $param->{'format'};
584 my @column_lengths = ();
585 my @column_callbacks = ();
586 if ( $format eq 'cch-fixed' || $format eq 'cch-fixed-update' ) {
587 $format =~ s/-fixed//;
588 my $date_format = sub { my $r='';
589 /^(\d{4})(\d{2})(\d{2})$/ && ($r="$1/$2/$3");
592 my $trim = sub { my $r = shift; $r =~ s/^\s*//; $r =~ s/\s*$//; $r };
593 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 );
594 push @column_lengths, 1 if $format eq 'cch-update';
595 push @column_callbacks, $trim foreach (@column_lengths); # 5, 6, 15, 17 esp
596 $column_callbacks[8] = $date_format;
600 my ( $count, $last, $min_sec ) = (0, time, 5); #progressbar
601 if ( $job || scalar(@column_callbacks) ) {
603 csv_from_fixed(\$fh, \$count, \@column_lengths, \@column_callbacks);
604 return $error if $error;
608 if ( $format eq 'cch' || $format eq 'cch-update' ) {
609 @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax
610 excessrate effective_date taxauth taxtype taxcat taxname
611 usetax useexcessrate fee unittype feemax maxtype passflag
613 push @fields, 'actionflag' if $format eq 'cch-update';
618 $hash->{'actionflag'} ='I' if ($hash->{'data_vendor'} eq 'cch');
619 $hash->{'data_vendor'} ='cch';
620 $hash->{'effective_date'} = str2time($hash->{'effective_date'});
623 join(':', map{ $hash->{$_} } qw(taxtype taxcat) );
625 my %tax_class = ( 'data_vendor' => 'cch',
626 'taxclass' => $taxclassid,
629 my $tax_class = qsearchs( 'tax_class', \%tax_class );
630 return "Error updating tax rate: no tax class $taxclassid"
633 $hash->{'taxclassnum'} = $tax_class->taxclassnum;
635 foreach (qw( inoutcity inoutlocal taxtype taxcat )) {
639 my %passflagmap = ( '0' => '',
643 $hash->{'passflag'} = $passflagmap{$hash->{'passflag'}}
644 if exists $passflagmap{$hash->{'passflag'}};
646 foreach (keys %$hash) {
647 $hash->{$_} = substr($hash->{$_}, 0, 80)
648 if length($hash->{$_}) > 80;
651 my $actionflag = delete($hash->{'actionflag'});
653 $hash->{'taxname'} =~ s/`/'/g;
654 $hash->{'taxname'} =~ s|\\|/|g;
656 return '' if $format eq 'cch'; # but not cch-update
658 if ($actionflag eq 'I') {
659 $insert{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
660 }elsif ($actionflag eq 'D') {
661 $delete{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
663 return "Unexpected action flag: ". $hash->{'actionflag'};
666 delete($hash->{$_}) for keys %$hash;
672 } elsif ( $format eq 'extended' ) {
673 die "unimplemented\n";
677 die "unknown format $format";
680 eval "use Text::CSV_XS;";
683 my $csv = new Text::CSV_XS;
687 local $SIG{HUP} = 'IGNORE';
688 local $SIG{INT} = 'IGNORE';
689 local $SIG{QUIT} = 'IGNORE';
690 local $SIG{TERM} = 'IGNORE';
691 local $SIG{TSTP} = 'IGNORE';
692 local $SIG{PIPE} = 'IGNORE';
694 my $oldAutoCommit = $FS::UID::AutoCommit;
695 local $FS::UID::AutoCommit = 0;
698 while ( defined($line=<$fh>) ) {
699 $csv->parse($line) or do {
700 $dbh->rollback if $oldAutoCommit;
701 return "can't parse: ". $csv->error_input();
704 if ( $job ) { # progress bar
705 if ( time - $min_sec > $last ) {
706 my $error = $job->update_statustext(
707 int( 100 * $imported / $count ). ",Importing tax rates"
709 die $error if $error;
714 my @columns = $csv->fields();
716 my %tax_rate = ( 'data_vendor' => $format );
717 foreach my $field ( @fields ) {
718 $tax_rate{$field} = shift @columns;
720 if ( scalar( @columns ) ) {
721 $dbh->rollback if $oldAutoCommit;
722 return "Unexpected trailing columns in line (wrong format?): $line";
725 my $error = &{$hook}(\%tax_rate);
727 $dbh->rollback if $oldAutoCommit;
731 if (scalar(keys %tax_rate)) { #inserts only, not updates for cch
733 my $tax_rate = new FS::tax_rate( \%tax_rate );
734 $error = $tax_rate->insert;
737 $dbh->rollback if $oldAutoCommit;
738 return "can't insert tax_rate for $line: $error";
747 for (grep { !exists($delete{$_}) } keys %insert) {
748 if ( $job ) { # progress bar
749 if ( time - $min_sec > $last ) {
750 my $error = $job->update_statustext(
751 int( 100 * $imported / $count ). ",Importing tax rates"
753 die $error if $error;
758 my $tax_rate = new FS::tax_rate( $insert{$_} );
759 my $error = $tax_rate->insert;
762 $dbh->rollback if $oldAutoCommit;
763 my $hashref = $insert{$_};
764 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
765 return "can't insert tax_rate for $line: $error";
771 for (grep { exists($delete{$_}) } keys %insert) {
772 if ( $job ) { # progress bar
773 if ( time - $min_sec > $last ) {
774 my $error = $job->update_statustext(
775 int( 100 * $imported / $count ). ",Importing tax rates"
777 die $error if $error;
782 my $old = qsearchs( 'tax_rate', $delete{$_} );
784 $dbh->rollback if $oldAutoCommit;
786 return "can't find tax_rate to replace for: ".
787 #join(" ", map { "$_ => ". $old->{$_} } @fields);
788 join(" ", map { "$_ => ". $old->{$_} } keys(%$old) );
790 my $new = new FS::tax_rate({ $old->hash, %{$insert{$_}}, 'manual' => '' });
791 $new->taxnum($old->taxnum);
792 my $error = $new->replace($old);
795 $dbh->rollback if $oldAutoCommit;
796 my $hashref = $insert{$_};
797 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
798 return "can't replace tax_rate for $line: $error";
805 for (grep { !exists($insert{$_}) } keys %delete) {
806 if ( $job ) { # progress bar
807 if ( time - $min_sec > $last ) {
808 my $error = $job->update_statustext(
809 int( 100 * $imported / $count ). ",Importing tax rates"
811 die $error if $error;
816 my $tax_rate = qsearchs( 'tax_rate', $delete{$_} );
818 $dbh->rollback if $oldAutoCommit;
819 $tax_rate = $delete{$_};
820 return "can't find tax_rate to delete for: ".
821 #join(" ", map { "$_ => ". $tax_rate->{$_} } @fields);
822 join(" ", map { "$_ => ". $tax_rate->{$_} } keys(%$tax_rate) );
824 my $error = $tax_rate->delete;
827 $dbh->rollback if $oldAutoCommit;
828 my $hashref = $delete{$_};
829 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
830 return "can't delete tax_rate for $line: $error";
836 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
838 return "Empty file!" unless ($imported || $format eq 'cch-update');
844 =item process_batch_import
846 Load a batch import as a queued JSRPC job
850 sub process_batch_import {
853 my $param = thaw(decode_base64(shift));
854 my $format = $param->{'format'}; #well... this is all cch specific
856 my $files = $param->{'uploaded_files'}
857 or die "No files provided.";
859 my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files;
861 if ($format eq 'cch' || $format eq 'cch-fixed') {
863 my $oldAutoCommit = $FS::UID::AutoCommit;
864 local $FS::UID::AutoCommit = 0;
867 my $have_location = 0;
869 my @list = ( 'GEOCODE', 'geofile', \&FS::tax_rate_location::batch_import,
870 'CODE', 'codefile', \&FS::tax_class::batch_import,
871 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
872 'ZIP', 'zipfile', \&FS::cust_tax_location::batch_import,
873 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
874 'DETAIL', 'detail', \&FS::tax_rate::batch_import,
876 while( scalar(@list) ) {
877 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
878 unless ($files{$file}) {
879 next if $name eq 'PLUS4';
880 $error = "No $name supplied";
881 $error = "Neither PLUS4 nor ZIP supplied"
882 if ($name eq 'ZIP' && !$have_location);
885 $have_location = 1 if $name eq 'PLUS4';
886 my $fmt = $format. ( $name eq 'ZIP' ? '-zip' : '' );
887 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
888 my $filename = "$dir/". $files{$file};
889 open my $fh, "< $filename" or $error ||= "Can't open $name file: $!";
891 $error ||= &{$import_sub}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
893 unlink $filename or warn "Can't delete $filename: $!";
897 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
900 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
903 }elsif ($format eq 'cch-update' || $format eq 'cch-fixed-update') {
905 my $oldAutoCommit = $FS::UID::AutoCommit;
906 local $FS::UID::AutoCommit = 0;
909 my @insert_list = ();
910 my @delete_list = ();
912 my @list = ( 'GEOCODE', 'geofile', \&FS::tax_rate_location::batch_import,
913 'CODE', 'codefile', \&FS::tax_class::batch_import,
914 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
915 'ZIP', 'zipfile', \&FS::cust_tax_location::batch_import,
916 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
918 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
919 while( scalar(@list) ) {
920 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
921 unless ($files{$file}) {
922 my $vendor = $name eq 'ZIP' ? 'cch' : 'cch-zip';
923 next # update expected only for previously installed location data
924 if ( ($name eq 'PLUS4' || $name eq 'ZIP')
925 && !scalar( qsearch( { table => 'cust_tax_location',
926 hashref => { data_vendor => $vendor },
927 select => 'DISTINCT data_vendor',
932 $error = "No $name supplied";
935 my $filename = "$dir/". $files{$file};
936 open my $fh, "< $filename" or $error ||= "Can't open $name file $filename: $!";
937 unlink $filename or warn "Can't delete $filename: $!";
939 my $ifh = new File::Temp( TEMPLATE => "$name.insert.XXXXXXXX",
942 ) or die "can't open temp file: $!\n";
944 my $dfh = new File::Temp( TEMPLATE => "$name.delete.XXXXXXXX",
947 ) or die "can't open temp file: $!\n";
949 my $insert_pattern = ($format eq 'cch-update') ? qr/"I"\s*$/ : qr/I\s*$/;
950 my $delete_pattern = ($format eq 'cch-update') ? qr/"D"\s*$/ : qr/D\s*$/;
953 $handle = $ifh if $_ =~ /$insert_pattern/;
954 $handle = $dfh if $_ =~ /$delete_pattern/;
956 $error = "bad input line: $_" unless $handle;
965 push @insert_list, $name, $ifh->filename, $import_sub;
966 unshift @delete_list, $name, $dfh->filename, $import_sub;
969 while( scalar(@insert_list) ) {
970 my ($name, $file, $import_sub) =
971 (shift @insert_list, shift @insert_list, shift @insert_list);
973 my $fmt = $format. ( $name eq 'ZIP' ? '-zip' : '' );
974 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
976 &{$import_sub}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
978 unlink $file or warn "Can't delete $file: $!";
981 $error ||= "No DETAIL supplied"
982 unless ($files{detail});
983 open my $fh, "< $dir/". $files{detail}
984 or $error ||= "Can't open DETAIL file: $!";
986 &FS::tax_rate::batch_import({ 'filehandle' => $fh, 'format' => $format },
989 unlink "$dir/". $files{detail} or warn "Can't delete $files{detail}: $!"
992 while( scalar(@delete_list) ) {
993 my ($name, $file, $import_sub) =
994 (shift @delete_list, shift @delete_list, shift @delete_list);
996 my $fmt = $format. ( $name eq 'ZIP' ? '-zip' : '' );
997 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
999 &{$import_sub}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
1001 unlink $file or warn "Can't delete $file: $!";
1005 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1008 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
1012 die "Unknown format: $format";
1017 =item process_download_and_update
1019 Download and process a tax update as a queued JSRPC job
1023 sub process_download_and_update {
1026 my $param = thaw(decode_base64(shift));
1027 my $format = $param->{'format'}; #well... this is all cch specific
1029 my ( $count, $last, $min_sec, $imported ) = (0, time, 5, 0); #progressbar
1032 if ( $job ) { # progress bar
1033 my $error = $job->update_statustext( int( 100 * $imported / $count ) );
1034 die $error if $error;
1037 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/taxdata';
1039 mkdir $dir or die "can't create $dir: $!\n";
1042 if ($format eq 'cch') {
1044 eval "use Text::CSV_XS;";
1050 my $conffile = '%%%FREESIDE_CONF%%%/cchconf';
1051 my $conffh = new IO::File "<$conffile" or die "can't open $conffile: $!\n";
1052 my ( $urls, $secret, $states ) =
1053 map { /^(.*)$/ or die "bad config line in $conffile: $_\n"; $1 }
1058 my $oldAutoCommit = $FS::UID::AutoCommit;
1059 local $FS::UID::AutoCommit = 0;
1063 # really should get a table EXCLUSIVE lock here
1064 # check if initial import or update
1066 my $sql = "SELECT count(*) from tax_rate WHERE data_vendor='$format'";
1067 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
1068 $sth->execute() or die $sth->errstr;
1069 my $upgrade = $sth->fetchrow_arrayref->[0];
1071 # create cache and/or rotate old tax data
1076 opendir(my $dirh, $dir) or die "failed to open $dir.4: $!\n";
1077 foreach my $file (readdir($dirh)) {
1078 unlink "$dir.4/$file" if (-f "$dir.4/$file");
1085 if ( -e "$dir.$_" ) {
1086 rename "$dir.$_", "$dir.". ($_+1) or die "can't rename $dir.$_: $!\n";
1089 rename "$dir", "$dir.1" or die "can't rename $dir: $!\n";
1093 die "can't find previous tax data\n" if $upgrade;
1097 mkdir "$dir.new" or die "can't create $dir.new: $!\n";
1099 # fetch and unpack the zip files
1101 my $ua = new LWP::UserAgent;
1102 foreach my $url (split ',', $urls) {
1103 my @name = split '/', $url; #somewhat restrictive
1104 my $name = pop @name;
1105 $name =~ /(.*)/; # untaint that which we trust;
1108 open my $taxfh, ">$dir.new/$name" or die "Can't open $dir.new/$name: $!\n";
1110 my $res = $ua->request(
1111 new HTTP::Request( GET => $url),
1112 sub { #my ($data, $response_object) = @_;
1113 print $taxfh $_[0] or die "Can't write to $dir.new/$name: $!\n";
1114 my $content_length = $_[1]->content_length;
1115 $imported += length($_[0]);
1116 if ( time - $min_sec > $last ) {
1117 my $error = $job->update_statustext(
1118 ($content_length ? int(100 * $imported/$content_length) : 0 ).
1119 ",Downloading data from CCH"
1121 die $error if $error;
1126 die "download of $url failed: ". $res->status_line
1127 unless $res->is_success;
1130 my $error = $job->update_statustext( "0,Unpacking data" );
1131 die $error if $error;
1132 $secret =~ /(.*)/; # untaint that which we trust;
1134 system('unzip', "-P", $secret, "-d", "$dir.new", "$dir.new/$name") == 0
1135 or die "unzip -P $secret -d $dir.new $dir.new/$name failed";
1136 #unlink "$dir.new/$name";
1139 # extract csv files from the dbf files
1141 foreach my $name ( qw( code detail geocode plus4 txmatrix zip ) ) {
1142 my $error = $job->update_statustext( "0,Unpacking $name" );
1143 die $error if $error;
1144 warn "opening $dir.new/$name.dbf\n" if $DEBUG;
1145 my $table = new XBase 'name' => "$dir.new/$name.dbf";
1146 die "failed to access $dir.new/$name.dbf: ". XBase->errstr
1147 unless defined($table);
1148 $count = $table->last_record; # approximately;
1150 open my $csvfh, ">$dir.new/$name.txt"
1151 or die "failed to open $dir.new/$name.txt: $!\n";
1153 my $csv = new Text::CSV_XS { 'always_quote' => 1 };
1154 my @fields = $table->field_names;
1155 my $cursor = $table->prepare_select;
1157 sub { my $date = shift;
1158 $date =~ /^(\d{4})(\d{2})(\d{2})$/ && ($date = "$2/$3/$1");
1161 while (my $row = $cursor->fetch_hashref) {
1162 $csv->combine( map { ($table->field_type($_) eq 'D')
1163 ? &{$format_date}($row->{$_})
1168 print $csvfh $csv->string, "\n";
1170 if ( time - $min_sec > $last ) {
1171 my $error = $job->update_statustext(
1172 int(100 * $imported/$count). ",Unpacking $name"
1174 die $error if $error;
1182 # generate the diff files
1184 my @insert_list = ();
1185 my @delete_list = ();
1188 # 'geocode', \&FS::tax_rate_location::batch_import,
1189 'code', \&FS::tax_class::batch_import,
1190 'plus4', \&FS::cust_tax_location::batch_import,
1191 'zip', \&FS::cust_tax_location::batch_import,
1192 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
1193 'detail', \&FS::tax_rate::batch_import,
1196 while( scalar(@list) ) {
1197 my ( $name, $method ) = ( shift @list, shift @list );
1200 my $error = $job->update_statustext( "0,Comparing to previous $name" );
1201 die $error if $error;
1203 warn "processing $dir.new/$name.txt\n" if $DEBUG;
1206 open my $oldcsvfh, "$dir.1/$name.txt"
1207 or die "failed to open $dir.1/$name.txt: $!\n";
1209 while(<$oldcsvfh>) {
1216 open my $newcsvfh, "$dir.new/$name.txt"
1217 or die "failed to open $dir.new/$name.txt: $!\n";
1219 my $ifh = new File::Temp( TEMPLATE => "$name.insert.XXXXXXXX",
1222 ) or die "can't open temp file: $!\n";
1224 my $dfh = new File::Temp( TEMPLATE => "$name.delete.XXXXXXXX",
1227 ) or die "can't open temp file: $!\n";
1229 while(<$newcsvfh>) {
1231 if (exists($oldlines{$_})) {
1234 print $ifh $_, ',"I"', "\n";
1239 if ($name eq 'detail') {
1240 for (keys %oldlines) { # one file for rate details
1241 print $ifh $_, ',"D"', "\n" if $oldlines{$_};
1244 for (keys %oldlines) {
1245 print $dfh $_, ',"D"', "\n" if $oldlines{$_};
1250 push @insert_list, $name, $ifh->filename, $method;
1251 unshift @delete_list, $name, $dfh->filename, $method
1252 unless $name eq 'detail';
1258 while( scalar(@insert_list) ) {
1259 my ($name, $file, $method) =
1260 (shift @insert_list, shift @insert_list, shift @insert_list);
1262 my $fmt = "$format-update";
1263 $fmt = $fmt. ( $name eq 'zip' ? '-zip' : '' );
1264 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
1266 &{$method}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
1268 #unlink $file or warn "Can't delete $file: $!";
1271 while( scalar(@delete_list) ) {
1272 my ($name, $file, $method) =
1273 (shift @delete_list, shift @delete_list, shift @delete_list);
1275 my $fmt = "$format-update";
1276 $fmt = $fmt. ( $name eq 'zip' ? '-zip' : '' );
1277 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
1279 &{$method}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
1281 #unlink $file or warn "Can't delete $file: $!";
1285 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1288 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
1291 rename "$dir.new", "$dir"
1292 or die "cch tax update processed, but can't rename $dir.new: $!\n";
1295 die "Unknown format: $format";
1299 =item browse_queries PARAMS
1301 Returns a list consisting of a hashref suited for use as the argument
1302 to qsearch, and sql query string. Each is based on the PARAMS hashref
1303 of keys and values which frequently would be passed as C<scalar($cgi->Vars)>
1304 from a form. This conveniently creates the query hashref and count_query
1305 string required by the browse and search elements. As a side effect,
1306 the PARAMS hashref is untainted and keys with unexpected values are removed.
1310 sub browse_queries {
1314 'table' => 'tax_rate',
1316 'order_by' => 'ORDER BY geocode, taxclassnum',
1321 if ( $params->{data_vendor} =~ /^(\w+)$/ ) {
1322 $extra_sql .= ' WHERE data_vendor = '. dbh->quote($1);
1324 delete $params->{data_vendor};
1327 if ( $params->{geocode} =~ /^(\w+)$/ ) {
1328 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1329 'geocode LIKE '. dbh->quote($1.'%');
1331 delete $params->{geocode};
1334 if ( $params->{taxclassnum} =~ /^(\d+)$/ &&
1335 qsearchs( 'tax_class', {'taxclassnum' => $1} )
1338 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1339 ' taxclassnum = '. dbh->quote($1)
1341 delete $params->{taxclassnun};
1345 if ( $params->{tax_type} =~ /^(\d+)$/ );
1346 delete $params->{tax_type}
1350 if ( $params->{tax_cat} =~ /^(\d+)$/ );
1351 delete $params->{tax_cat}
1354 my @taxclassnum = ();
1355 if ($tax_type || $tax_cat ) {
1356 my $compare = "LIKE '". ( $tax_type || "%" ). ":". ( $tax_cat || "%" ). "'";
1357 $compare = "= '$tax_type:$tax_cat'" if ($tax_type && $tax_cat);
1358 @taxclassnum = map { $_->taxclassnum }
1359 qsearch({ 'table' => 'tax_class',
1361 'extra_sql' => "WHERE taxclass $compare",
1365 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ). '( '.
1366 join(' OR ', map { " taxclassnum = $_ " } @taxclassnum ). ' )'
1367 if ( @taxclassnum );
1369 unless ($params->{'showdisabled'}) {
1370 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1371 "( disabled = '' OR disabled IS NULL )";
1374 $query->{extra_sql} = $extra_sql;
1376 return ($query, "SELECT COUNT(*) FROM tax_rate $extra_sql");
1381 # Used by FS::Upgrade to migrate to a new database.
1385 sub _upgrade_data { # class method
1386 my ($self, %opts) = @_;
1389 warn "$me upgrading $self\n" if $DEBUG;
1391 my @column = qw ( tax excessrate usetax useexcessrate fee excessfee
1394 if ( $dbh->{Driver}->{Name} eq 'Pg' ) {
1396 eval "use DBI::Const::GetInfoType;";
1399 my $major_version = 0;
1400 $dbh->get_info( $GetInfoType{SQL_DBMS_VER} ) =~ /^(\d{2})/
1401 && ( $major_version = sprintf("%d", $1) );
1403 if ( $major_version > 7 ) {
1405 # ideally this would be supported in DBIx-DBSchema and friends
1407 foreach my $column ( @column ) {
1408 my $columndef = dbdef->table($self->table)->column($column);
1409 unless ($columndef->type eq 'numeric') {
1411 warn "updating tax_rate column $column to numeric\n" if $DEBUG;
1412 my $sql = "ALTER TABLE tax_rate ALTER $column TYPE numeric(14,8)";
1413 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
1414 $sth->execute or die $sth->errstr;
1416 warn "updating h_tax_rate column $column to numeric\n" if $DEBUG;
1417 $sql = "ALTER TABLE h_tax_rate ALTER $column TYPE numeric(14,8)";
1418 $sth = $dbh->prepare($sql) or die $dbh->errstr;
1419 $sth->execute or die $sth->errstr;
1426 warn "WARNING: tax_rate table upgrade unsupported for this Pg version\n";
1432 warn "WARNING: tax_rate table upgrade only supported for Pg 8+\n";
1444 Mixing automatic and manual editing works poorly at present.
1448 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base