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::part_pkg_taxrate;
24 use FS::Misc qw( csv_from_fixed );
26 @ISA = qw( FS::Record );
29 $me = '[FS::tax_rate]';
33 FS::tax_rate - Object methods for tax_rate objects
39 $record = new FS::tax_rate \%hash;
40 $record = new FS::tax_rate { 'column' => 'value' };
42 $error = $record->insert;
44 $error = $new_record->replace($old_record);
46 $error = $record->delete;
48 $error = $record->check;
52 An FS::tax_rate object represents a tax rate, defined by locale.
53 FS::tax_rate inherits from FS::Record. The following fields are
60 primary key (assigned automatically for new tax rates)
64 a geographic location code provided by a tax data vendor
72 a location code provided by a tax authority
76 a foreign key into FS::tax_class - the type of tax
77 referenced but FS::part_pkg_taxrate
80 the time after which the tax applies
88 second bracket percentage
92 the amount to which the tax applies (first bracket)
96 a cap on the amount of tax if a cap exists
100 percentage on out of jurisdiction purchases
104 second bracket percentage on out of jurisdiction purchases
108 one of the values in %tax_unittypes
112 amount of tax per unit
116 second bracket amount of tax per unit
120 the number of units to which the fee applies (first bracket)
124 the most units to which fees apply (first and second brackets)
128 a value from %tax_maxtypes indicating how brackets accumulate (i.e. monthly, per invoice, etc)
132 if defined, printed on invoices instead of "Tax"
136 a value from %tax_authorities
140 a value from %tax_basetypes indicating the tax basis
144 a value from %tax_passtypes indicating how the tax should displayed to the customer
148 'Y', 'N', or blank indicating the tax can be passed to the customer
152 if 'Y', this tax does not apply to setup fees
156 if 'Y', this tax does not apply to recurring fees
160 if 'Y', has been manually edited
170 Creates a new tax rate. To add the tax rate to the database, see L<"insert">.
174 sub table { 'tax_rate'; }
178 Adds this tax rate to the database. If there is an error, returns the error,
179 otherwise returns false.
183 Deletes this tax rate from the database. If there is an error, returns the
184 error, otherwise returns false.
186 =item replace OLD_RECORD
188 Replaces the OLD_RECORD with this one in the database. If there is an error,
189 returns the error, otherwise returns false.
193 Checks all fields to make sure this is a valid tax rate. If there is an error,
194 returns the error, otherwise returns false. Called by the insert and replace
202 foreach (qw( taxbase taxmax )) {
203 $self->$_(0) unless $self->$_;
206 $self->ut_numbern('taxnum')
207 || $self->ut_text('geocode')
208 || $self->ut_textn('data_vendor')
209 || $self->ut_textn('location')
210 || $self->ut_foreign_key('taxclassnum', 'tax_class', 'taxclassnum')
211 || $self->ut_snumbern('effective_date')
212 || $self->ut_float('tax')
213 || $self->ut_floatn('excessrate')
214 || $self->ut_money('taxbase')
215 || $self->ut_money('taxmax')
216 || $self->ut_floatn('usetax')
217 || $self->ut_floatn('useexcessrate')
218 || $self->ut_numbern('unittype')
219 || $self->ut_floatn('fee')
220 || $self->ut_floatn('excessfee')
221 || $self->ut_floatn('feemax')
222 || $self->ut_numbern('maxtype')
223 || $self->ut_textn('taxname')
224 || $self->ut_numbern('taxauth')
225 || $self->ut_numbern('basetype')
226 || $self->ut_numbern('passtype')
227 || $self->ut_enum('passflag', [ '', 'Y', 'N' ])
228 || $self->ut_enum('setuptax', [ '', 'Y' ] )
229 || $self->ut_enum('recurtax', [ '', 'Y' ] )
230 || $self->ut_enum('manual', [ '', 'Y' ] )
231 || $self->ut_enum('disabled', [ '', 'Y' ] )
232 || $self->SUPER::check
237 =item taxclass_description
239 Returns the human understandable value associated with the related
244 sub taxclass_description {
246 my $tax_class = qsearchs('tax_class', {'taxclassnum' => $self->taxclassnum });
247 $tax_class ? $tax_class->description : '';
252 Returns the human understandable value associated with the unittype column
256 %tax_unittypes = ( '0' => 'access line',
263 $tax_unittypes{$self->unittype};
268 Returns the human understandable value associated with the maxtype column
272 %tax_maxtypes = ( '0' => 'receipts per invoice',
273 '1' => 'receipts per item',
274 '2' => 'total utility charges per utility tax year',
275 '3' => 'total charges per utility tax year',
276 '4' => 'receipts per access line',
277 '9' => 'monthly receipts per location',
282 $tax_maxtypes{$self->maxtype};
287 Returns the human understandable value associated with the basetype column
291 %tax_basetypes = ( '0' => 'sale price',
292 '1' => 'gross receipts',
293 '2' => 'sales taxable telecom revenue',
294 '3' => 'minutes carried',
295 '4' => 'minutes billed',
296 '5' => 'gross operating revenue',
297 '6' => 'access line',
299 '8' => 'gross revenue',
300 '9' => 'portion gross receipts attributable to interstate service',
301 '10' => 'access line',
302 '11' => 'gross profits',
303 '12' => 'tariff rate',
305 '15' => 'prior year gross receipts',
310 $tax_basetypes{$self->basetype};
315 Returns the human understandable value associated with the taxauth column
319 %tax_authorities = ( '0' => 'federal',
324 '5' => 'county administered by state',
325 '6' => 'city administered by state',
326 '7' => 'city administered by county',
327 '8' => 'local administered by state',
328 '9' => 'local administered by county',
333 $tax_authorities{$self->taxauth};
338 Returns the human understandable value associated with the passtype column
342 %tax_passtypes = ( '0' => 'separate tax line',
343 '1' => 'separate surcharge line',
344 '2' => 'surcharge not separated',
345 '3' => 'included in base rate',
350 $tax_passtypes{$self->passtype};
353 =item taxline TAXABLES, [ OPTIONSHASH ]
355 Returns a listref of a name and an amount of tax calculated for the list
356 of packages/amounts referenced by TAXABLES. If an error occurs, a message
357 is returned as a scalar.
367 if (ref($_[0]) eq 'ARRAY') {
372 #exemptions would be broken in this case
375 my $name = $self->taxname;
376 $name = 'Other surcharges'
377 if ($self->passtype == 2);
380 if ( $self->disabled ) { # we always know how to handle disabled taxes
387 my $taxable_charged = 0;
388 my @cust_bill_pkg = grep { $taxable_charged += $_ unless ref; ref; }
391 warn "calculating taxes for ". $self->taxnum. " on ".
392 join (",", map { $_->pkgnum } @cust_bill_pkg)
395 if ($self->passflag eq 'N') {
396 # return "fatal: can't (yet) handle taxes not passed to the customer";
397 # until someone needs to track these in freeside
404 if ($self->maxtype != 0 && $self->maxtype != 9) {
405 return $self->_fatal_or_null( 'tax with "'.
406 $self->maxtype_name. '" threshold'
410 if ($self->maxtype == 9) {
412 $self->_fatal_or_null( 'tax with "'. $self->maxtype_name. '" threshold' );
416 # we treat gross revenue as gross receipts and expect the tax data
417 # to DTRT (i.e. tax on tax rules)
418 if ($self->basetype != 0 && $self->basetype != 1 &&
419 $self->basetype != 5 && $self->basetype != 6 &&
420 $self->basetype != 7 && $self->basetype != 8 &&
421 $self->basetype != 14
424 $self->_fatal_or_null( 'tax with "'. $self->basetype_name. '" basis' );
427 unless ($self->setuptax =~ /^Y$/i) {
428 $taxable_charged += $_->setup foreach @cust_bill_pkg;
430 unless ($self->recurtax =~ /^Y$/i) {
431 $taxable_charged += $_->recur foreach @cust_bill_pkg;
434 my $taxable_units = 0;
435 unless ($self->recurtax =~ /^Y$/i) {
436 if ($self->unittype == 0) {
438 foreach (@cust_bill_pkg) {
439 $taxable_units += $_->units
440 unless $seen{$_->pkgnum};
443 }elsif ($self->unittype == 1) {
444 return $self->_fatal_or_null( 'fee with minute unit type' );
445 }elsif ($self->unittype == 2) {
448 return $self->_fatal_or_null( 'unknown unit type in tax'. $self->taxnum );
453 # XXX insert exemption handling here
455 # the tax or fee is applied to taxbase or feebase and then
456 # the excessrate or excess fee is applied to taxmax or feemax
459 $amount += $taxable_charged * $self->tax;
460 $amount += $taxable_units * $self->fee;
462 warn "calculated taxes as [ $name, $amount ]\n"
473 my ($self, $error) = @_;
475 my $conf = new FS::Conf;
477 $error = "fatal: can't yet handle ". $error;
478 my $name = $self->taxname;
479 $name = 'Other surcharges'
480 if ($self->passtype == 2);
482 if ($conf->exists('ignore_incalculable_taxes')) {
484 return { name => $name, amount => 0 };
490 =item tax_on_tax CUST_MAIN
492 Returns a list of taxes which are candidates for taxing taxes for the
493 given customer (see L<FS::cust_main>)
499 my $cust_main = shift;
501 warn "looking up taxes on tax ". $self->taxnum. " for customer ".
505 my $geocode = $cust_main->geocode($self->data_vendor);
509 my $extra_sql = ' AND ('.
510 join(' OR ', map{ 'geocode = '. $dbh->quote(substr($geocode, 0, $_)) }
515 my $order_by = 'ORDER BY taxclassnum, length(geocode) desc';
516 my $select = 'DISTINCT ON(taxclassnum) *';
518 # should qsearch preface columns with the table to facilitate joins?
519 my @taxclassnums = map { $_->taxclassnum }
520 qsearch( { 'table' => 'part_pkg_taxrate',
522 'hashref' => { 'data_vendor' => $self->data_vendor,
523 'taxclassnumtaxed' => $self->taxclassnum,
525 'extra_sql' => $extra_sql,
526 'order_by' => $order_by,
529 return () unless @taxclassnums;
532 "AND (". join(' OR ', map { "taxclassnum = $_" } @taxclassnums ). ")";
534 qsearch({ 'table' => 'tax_rate',
535 'hashref' => { 'geocode' => $geocode, },
536 'extra_sql' => $extra_sql,
552 my ($param, $job) = @_;
554 my $fh = $param->{filehandle};
555 my $format = $param->{'format'};
563 my @column_lengths = ();
564 my @column_callbacks = ();
565 if ( $format eq 'cch-fixed' || $format eq 'cch-fixed-update' ) {
566 $format =~ s/-fixed//;
567 my $date_format = sub { my $r='';
568 /^(\d{4})(\d{2})(\d{2})$/ && ($r="$1/$2/$3");
571 my $trim = sub { my $r = shift; $r =~ s/^\s*//; $r =~ s/\s*$//; $r };
572 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 );
573 push @column_lengths, 1 if $format eq 'cch-update';
574 push @column_callbacks, $trim foreach (@column_lengths); # 5, 6, 15, 17 esp
575 $column_callbacks[8] = $date_format;
579 my ( $count, $last, $min_sec ) = (0, time, 5); #progressbar
580 if ( $job || scalar(@column_callbacks) ) {
582 csv_from_fixed(\$fh, \$count, \@column_lengths, \@column_callbacks);
583 return $error if $error;
587 if ( $format eq 'cch' || $format eq 'cch-update' ) {
588 @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax
589 excessrate effective_date taxauth taxtype taxcat taxname
590 usetax useexcessrate fee unittype feemax maxtype passflag
592 push @fields, 'actionflag' if $format eq 'cch-update';
597 $hash->{'actionflag'} ='I' if ($hash->{'data_vendor'} eq 'cch');
598 $hash->{'data_vendor'} ='cch';
599 $hash->{'effective_date'} = str2time($hash->{'effective_date'});
602 join(':', map{ $hash->{$_} } qw(taxtype taxcat) );
604 my %tax_class = ( 'data_vendor' => 'cch',
605 'taxclass' => $taxclassid,
608 my $tax_class = qsearchs( 'tax_class', \%tax_class );
609 return "Error updating tax rate: no tax class $taxclassid"
612 $hash->{'taxclassnum'} = $tax_class->taxclassnum;
614 foreach (qw( inoutcity inoutlocal taxtype taxcat )) {
618 my %passflagmap = ( '0' => '',
622 $hash->{'passflag'} = $passflagmap{$hash->{'passflag'}}
623 if exists $passflagmap{$hash->{'passflag'}};
625 foreach (keys %$hash) {
626 $hash->{$_} = substr($hash->{$_}, 0, 80)
627 if length($hash->{$_}) > 80;
630 my $actionflag = delete($hash->{'actionflag'});
632 $hash->{'taxname'} =~ s/`/'/g;
633 $hash->{'taxname'} =~ s|\\|/|g;
635 return '' if $format eq 'cch'; # but not cch-update
637 if ($actionflag eq 'I') {
638 $insert{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
639 }elsif ($actionflag eq 'D') {
640 $delete{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
642 return "Unexpected action flag: ". $hash->{'actionflag'};
645 delete($hash->{$_}) for keys %$hash;
651 } elsif ( $format eq 'extended' ) {
652 die "unimplemented\n";
656 die "unknown format $format";
659 eval "use Text::CSV_XS;";
662 my $csv = new Text::CSV_XS;
666 local $SIG{HUP} = 'IGNORE';
667 local $SIG{INT} = 'IGNORE';
668 local $SIG{QUIT} = 'IGNORE';
669 local $SIG{TERM} = 'IGNORE';
670 local $SIG{TSTP} = 'IGNORE';
671 local $SIG{PIPE} = 'IGNORE';
673 my $oldAutoCommit = $FS::UID::AutoCommit;
674 local $FS::UID::AutoCommit = 0;
677 while ( defined($line=<$fh>) ) {
678 $csv->parse($line) or do {
679 $dbh->rollback if $oldAutoCommit;
680 return "can't parse: ". $csv->error_input();
683 if ( $job ) { # progress bar
684 if ( time - $min_sec > $last ) {
685 my $error = $job->update_statustext(
686 int( 100 * $imported / $count ). ",Importing tax rates"
688 die $error if $error;
693 my @columns = $csv->fields();
695 my %tax_rate = ( 'data_vendor' => $format );
696 foreach my $field ( @fields ) {
697 $tax_rate{$field} = shift @columns;
699 if ( scalar( @columns ) ) {
700 $dbh->rollback if $oldAutoCommit;
701 return "Unexpected trailing columns in line (wrong format?): $line";
704 my $error = &{$hook}(\%tax_rate);
706 $dbh->rollback if $oldAutoCommit;
710 if (scalar(keys %tax_rate)) { #inserts only, not updates for cch
712 my $tax_rate = new FS::tax_rate( \%tax_rate );
713 $error = $tax_rate->insert;
716 $dbh->rollback if $oldAutoCommit;
717 return "can't insert tax_rate for $line: $error";
726 for (grep { !exists($delete{$_}) } keys %insert) {
727 if ( $job ) { # progress bar
728 if ( time - $min_sec > $last ) {
729 my $error = $job->update_statustext(
730 int( 100 * $imported / $count ). ",Importing tax rates"
732 die $error if $error;
737 my $tax_rate = new FS::tax_rate( $insert{$_} );
738 my $error = $tax_rate->insert;
741 $dbh->rollback if $oldAutoCommit;
742 my $hashref = $insert{$_};
743 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
744 return "can't insert tax_rate for $line: $error";
750 for (grep { exists($delete{$_}) } keys %insert) {
751 if ( $job ) { # progress bar
752 if ( time - $min_sec > $last ) {
753 my $error = $job->update_statustext(
754 int( 100 * $imported / $count ). ",Importing tax rates"
756 die $error if $error;
761 my $old = qsearchs( 'tax_rate', $delete{$_} );
763 $dbh->rollback if $oldAutoCommit;
765 return "can't find tax_rate to replace for: ".
766 #join(" ", map { "$_ => ". $old->{$_} } @fields);
767 join(" ", map { "$_ => ". $old->{$_} } keys(%$old) );
769 my $new = new FS::tax_rate({ $old->hash, %{$insert{$_}}, 'manual' => '' });
770 $new->taxnum($old->taxnum);
771 my $error = $new->replace($old);
774 $dbh->rollback if $oldAutoCommit;
775 my $hashref = $insert{$_};
776 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
777 return "can't replace tax_rate for $line: $error";
784 for (grep { !exists($insert{$_}) } keys %delete) {
785 if ( $job ) { # progress bar
786 if ( time - $min_sec > $last ) {
787 my $error = $job->update_statustext(
788 int( 100 * $imported / $count ). ",Importing tax rates"
790 die $error if $error;
795 my $tax_rate = qsearchs( 'tax_rate', $delete{$_} );
797 $dbh->rollback if $oldAutoCommit;
798 $tax_rate = $delete{$_};
799 return "can't find tax_rate to delete for: ".
800 #join(" ", map { "$_ => ". $tax_rate->{$_} } @fields);
801 join(" ", map { "$_ => ". $tax_rate->{$_} } keys(%$tax_rate) );
803 my $error = $tax_rate->delete;
806 $dbh->rollback if $oldAutoCommit;
807 my $hashref = $delete{$_};
808 $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
809 return "can't delete tax_rate for $line: $error";
815 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
817 return "Empty file!" unless ($imported || $format eq 'cch-update');
823 =item process_batch_import
825 Load a batch import as a queued JSRPC job
829 sub process_batch_import {
832 my $param = thaw(decode_base64(shift));
833 my $format = $param->{'format'}; #well... this is all cch specific
835 my $files = $param->{'uploaded_files'}
836 or die "No files provided.";
838 my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files;
840 if ($format eq 'cch' || $format eq 'cch-fixed') {
842 my $oldAutoCommit = $FS::UID::AutoCommit;
843 local $FS::UID::AutoCommit = 0;
846 my $have_location = 0;
848 my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import,
849 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
850 'ZIP', 'zipfile', \&FS::cust_tax_location::batch_import,
851 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
852 'DETAIL', 'detail', \&FS::tax_rate::batch_import,
854 while( scalar(@list) ) {
855 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
856 unless ($files{$file}) {
857 next if $name eq 'PLUS4';
858 $error = "No $name supplied";
859 $error = "Neither PLUS4 nor ZIP supplied"
860 if ($name eq 'ZIP' && !$have_location);
863 $have_location = 1 if $name eq 'PLUS4';
864 my $fmt = $format. ( $name eq 'ZIP' ? '-zip' : '' );
865 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
866 my $filename = "$dir/". $files{$file};
867 open my $fh, "< $filename" or $error ||= "Can't open $name file: $!";
869 $error ||= &{$import_sub}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
871 unlink $filename or warn "Can't delete $filename: $!";
875 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
878 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
881 }elsif ($format eq 'cch-update' || $format eq 'cch-fixed-update') {
883 my $oldAutoCommit = $FS::UID::AutoCommit;
884 local $FS::UID::AutoCommit = 0;
887 my @insert_list = ();
888 my @delete_list = ();
890 my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import,
891 'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
892 'ZIP', 'zipfile', \&FS::cust_tax_location::batch_import,
893 'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
895 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
896 while( scalar(@list) ) {
897 my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
898 unless ($files{$file}) {
899 my $vendor = $name eq 'ZIP' ? 'cch' : 'cch-zip';
900 next # update expected only for previously installed location data
901 if ( ($name eq 'PLUS4' || $name eq 'ZIP')
902 && !scalar( qsearch( { table => 'cust_tax_location',
903 hashref => { data_vendor => $vendor },
904 select => 'DISTINCT data_vendor',
909 $error = "No $name supplied";
912 my $filename = "$dir/". $files{$file};
913 open my $fh, "< $filename" or $error ||= "Can't open $name file $filename: $!";
914 unlink $filename or warn "Can't delete $filename: $!";
916 my $ifh = new File::Temp( TEMPLATE => "$name.insert.XXXXXXXX",
919 ) or die "can't open temp file: $!\n";
921 my $dfh = new File::Temp( TEMPLATE => "$name.delete.XXXXXXXX",
924 ) or die "can't open temp file: $!\n";
926 my $insert_pattern = ($format eq 'cch-update') ? qr/"I"\s*$/ : qr/I\s*$/;
927 my $delete_pattern = ($format eq 'cch-update') ? qr/"D"\s*$/ : qr/D\s*$/;
930 $handle = $ifh if $_ =~ /$insert_pattern/;
931 $handle = $dfh if $_ =~ /$delete_pattern/;
933 $error = "bad input line: $_" unless $handle;
942 push @insert_list, $name, $ifh->filename, $import_sub;
943 unshift @delete_list, $name, $dfh->filename, $import_sub;
946 while( scalar(@insert_list) ) {
947 my ($name, $file, $import_sub) =
948 (shift @insert_list, shift @insert_list, shift @insert_list);
950 my $fmt = $format. ( $name eq 'ZIP' ? '-zip' : '' );
951 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
953 &{$import_sub}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
955 unlink $file or warn "Can't delete $file: $!";
958 $error ||= "No DETAIL supplied"
959 unless ($files{detail});
960 open my $fh, "< $dir/". $files{detail}
961 or $error ||= "Can't open DETAIL file: $!";
963 &FS::tax_rate::batch_import({ 'filehandle' => $fh, 'format' => $format },
966 unlink "$dir/". $files{detail} or warn "Can't delete $files{detail}: $!"
969 while( scalar(@delete_list) ) {
970 my ($name, $file, $import_sub) =
971 (shift @delete_list, shift @delete_list, shift @delete_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: $!";
982 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
985 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
989 die "Unknown format: $format";
994 =item process_download_and_update
996 Download and process a tax update as a queued JSRPC job
1000 sub process_download_and_update {
1003 my $param = thaw(decode_base64(shift));
1004 my $format = $param->{'format'}; #well... this is all cch specific
1006 my ( $count, $last, $min_sec, $imported ) = (0, time, 5, 0); #progressbar
1009 if ( $job ) { # progress bar
1010 my $error = $job->update_statustext( int( 100 * $imported / $count ) );
1011 die $error if $error;
1014 my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/taxdata';
1016 mkdir $dir or die "can't create $dir: $!\n";
1019 if ($format eq 'cch') {
1021 eval "use Text::CSV_XS;";
1027 my $conffile = '%%%FREESIDE_CONF%%%/cchconf';
1028 my $conffh = new IO::File "<$conffile" or die "can't open $conffile: $!\n";
1029 my ( $urls, $secret, $states ) =
1030 map { /^(.*)$/ or die "bad config line in $conffile: $_\n"; $1 }
1035 my $oldAutoCommit = $FS::UID::AutoCommit;
1036 local $FS::UID::AutoCommit = 0;
1040 # really should get a table EXCLUSIVE lock here
1041 # check if initial import or update
1043 my $sql = "SELECT count(*) from tax_rate WHERE data_vendor='$format'";
1044 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
1045 $sth->execute() or die $sth->errstr;
1046 my $upgrade = $sth->fetchrow_arrayref->[0];
1048 # create cache and/or rotate old tax data
1053 opendir(my $dirh, $dir) or die "failed to open $dir.4: $!\n";
1054 foreach my $file (readdir($dirh)) {
1055 unlink "$dir.4/$file" if (-f "$dir.4/$file");
1062 if ( -e "$dir.$_" ) {
1063 rename "$dir.$_", "$dir.". ($_+1) or die "can't rename $dir.$_: $!\n";
1066 rename "$dir", "$dir.1" or die "can't rename $dir: $!\n";
1070 die "can't find previous tax data\n" if $upgrade;
1074 mkdir "$dir.new" or die "can't create $dir.new: $!\n";
1076 # fetch and unpack the zip files
1078 my $ua = new LWP::UserAgent;
1079 foreach my $url (split ',', $urls) {
1080 my @name = split '/', $url; #somewhat restrictive
1081 my $name = pop @name;
1082 $name =~ /(.*)/; # untaint that which we trust;
1085 open my $taxfh, ">$dir.new/$name" or die "Can't open $dir.new/$name: $!\n";
1087 my $res = $ua->request(
1088 new HTTP::Request( GET => $url),
1089 sub { #my ($data, $response_object) = @_;
1090 print $taxfh $_[0] or die "Can't write to $dir.new/$name: $!\n";
1091 my $content_length = $_[1]->content_length;
1092 $imported += length($_[0]);
1093 if ( time - $min_sec > $last ) {
1094 my $error = $job->update_statustext(
1095 ($content_length ? int(100 * $imported/$content_length) : 0 ).
1096 ",Downloading data from CCH"
1098 die $error if $error;
1103 die "download of $url failed: ". $res->status_line
1104 unless $res->is_success;
1107 my $error = $job->update_statustext( "0,Unpacking data" );
1108 die $error if $error;
1109 $secret =~ /(.*)/; # untaint that which we trust;
1111 system('unzip', "-P", $secret, "-d", "$dir.new", "$dir.new/$name") == 0
1112 or die "unzip -P $secret -d $dir.new $dir.new/$name failed";
1113 #unlink "$dir.new/$name";
1116 # extract csv files from the dbf files
1118 foreach my $name ( qw( code detail geocode plus4 txmatrix zip ) ) {
1119 my $error = $job->update_statustext( "0,Unpacking $name" );
1120 die $error if $error;
1121 warn "opening $dir.new/$name.dbf\n" if $DEBUG;
1122 my $table = new XBase 'name' => "$dir.new/$name.dbf";
1123 die "failed to access $dir.new/$name.dbf: ". XBase->errstr
1124 unless defined($table);
1125 $count = $table->last_record; # approximately;
1127 open my $csvfh, ">$dir.new/$name.txt"
1128 or die "failed to open $dir.new/$name.txt: $!\n";
1130 my $csv = new Text::CSV_XS { 'always_quote' => 1 };
1131 my @fields = $table->field_names;
1132 my $cursor = $table->prepare_select;
1134 sub { my $date = shift;
1135 $date =~ /^(\d{4})(\d{2})(\d{2})$/ && ($date = "$2/$3/$1");
1138 while (my $row = $cursor->fetch_hashref) {
1139 $csv->combine( map { ($table->field_type($_) eq 'D')
1140 ? &{$format_date}($row->{$_})
1145 print $csvfh $csv->string, "\n";
1147 if ( time - $min_sec > $last ) {
1148 my $error = $job->update_statustext(
1149 int(100 * $imported/$count). ",Unpacking $name"
1151 die $error if $error;
1159 # generate the diff files
1161 my @insert_list = ();
1162 my @delete_list = ();
1165 # 'geocode', \&FS::tax_rate_location::batch_import,
1166 'code', \&FS::tax_class::batch_import,
1167 'plus4', \&FS::cust_tax_location::batch_import,
1168 'zip', \&FS::cust_tax_location::batch_import,
1169 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
1170 'detail', \&FS::tax_rate::batch_import,
1173 while( scalar(@list) ) {
1174 my ( $name, $method ) = ( shift @list, shift @list );
1177 my $error = $job->update_statustext( "0,Comparing to previous $name" );
1178 die $error if $error;
1180 warn "processing $dir.new/$name.txt\n" if $DEBUG;
1183 open my $oldcsvfh, "$dir.1/$name.txt"
1184 or die "failed to open $dir.1/$name.txt: $!\n";
1186 while(<$oldcsvfh>) {
1193 open my $newcsvfh, "$dir.new/$name.txt"
1194 or die "failed to open $dir.new/$name.txt: $!\n";
1196 my $ifh = new File::Temp( TEMPLATE => "$name.insert.XXXXXXXX",
1199 ) or die "can't open temp file: $!\n";
1201 my $dfh = new File::Temp( TEMPLATE => "$name.delete.XXXXXXXX",
1204 ) or die "can't open temp file: $!\n";
1206 while(<$newcsvfh>) {
1208 if (exists($oldlines{$_})) {
1211 print $ifh $_, ',"I"', "\n";
1216 if ($name eq 'detail') {
1217 for (keys %oldlines) { # one file for rate details
1218 print $ifh $_, ',"D"', "\n" if $oldlines{$_};
1221 for (keys %oldlines) {
1222 print $dfh $_, ',"D"', "\n" if $oldlines{$_};
1227 push @insert_list, $name, $ifh->filename, $method;
1228 unshift @delete_list, $name, $dfh->filename, $method
1229 unless $name eq 'detail';
1235 while( scalar(@insert_list) ) {
1236 my ($name, $file, $method) =
1237 (shift @insert_list, shift @insert_list, shift @insert_list);
1239 my $fmt = "$format-update";
1240 $fmt = $fmt. ( $name eq 'zip' ? '-zip' : '' );
1241 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
1243 &{$method}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
1245 #unlink $file or warn "Can't delete $file: $!";
1248 while( scalar(@delete_list) ) {
1249 my ($name, $file, $method) =
1250 (shift @delete_list, shift @delete_list, shift @delete_list);
1252 my $fmt = "$format-update";
1253 $fmt = $fmt. ( $name eq 'zip' ? '-zip' : '' );
1254 open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
1256 &{$method}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
1258 #unlink $file or warn "Can't delete $file: $!";
1262 $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
1265 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
1268 rename "$dir.new", "$dir"
1269 or die "cch tax update processed, but can't rename $dir.new: $!\n";
1272 die "Unknown format: $format";
1276 =item browse_queries PARAMS
1278 Returns a list consisting of a hashref suited for use as the argument
1279 to qsearch, and sql query string. Each is based on the PARAMS hashref
1280 of keys and values which frequently would be passed as C<scalar($cgi->Vars)>
1281 from a form. This conveniently creates the query hashref and count_query
1282 string required by the browse and search elements. As a side effect,
1283 the PARAMS hashref is untainted and keys with unexpected values are removed.
1287 sub browse_queries {
1291 'table' => 'tax_rate',
1293 'order_by' => 'ORDER BY geocode, taxclassnum',
1298 if ( $params->{data_vendor} =~ /^(\w+)$/ ) {
1299 $extra_sql .= ' WHERE data_vendor = '. dbh->quote($1);
1301 delete $params->{data_vendor};
1304 if ( $params->{geocode} =~ /^(\w+)$/ ) {
1305 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1306 'geocode LIKE '. dbh->quote($1.'%');
1308 delete $params->{geocode};
1311 if ( $params->{taxclassnum} =~ /^(\d+)$/ &&
1312 qsearchs( 'tax_class', {'taxclassnum' => $1} )
1315 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1316 ' taxclassnum = '. dbh->quote($1)
1318 delete $params->{taxclassnun};
1322 if ( $params->{tax_type} =~ /^(\d+)$/ );
1323 delete $params->{tax_type}
1327 if ( $params->{tax_cat} =~ /^(\d+)$/ );
1328 delete $params->{tax_cat}
1331 my @taxclassnum = ();
1332 if ($tax_type || $tax_cat ) {
1333 my $compare = "LIKE '". ( $tax_type || "%" ). ":". ( $tax_cat || "%" ). "'";
1334 $compare = "= '$tax_type:$tax_cat'" if ($tax_type && $tax_cat);
1335 @taxclassnum = map { $_->taxclassnum }
1336 qsearch({ 'table' => 'tax_class',
1338 'extra_sql' => "WHERE taxclass $compare",
1342 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ). '( '.
1343 join(' OR ', map { " taxclassnum = $_ " } @taxclassnum ). ' )'
1344 if ( @taxclassnum );
1346 unless ($params->{'showdisabled'}) {
1347 $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
1348 "( disabled = '' OR disabled IS NULL )";
1351 $query->{extra_sql} = $extra_sql;
1353 return ($query, "SELECT COUNT(*) FROM tax_rate $extra_sql");
1358 # Used by FS::Upgrade to migrate to a new database.
1362 sub _upgrade_data { # class method
1363 my ($self, %opts) = @_;
1366 warn "$me upgrading $self\n" if $DEBUG;
1368 my @column = qw ( tax excessrate usetax useexcessrate fee excessfee
1371 if ( $dbh->{Driver}->{Name} eq 'Pg' ) {
1373 eval "use DBI::Const::GetInfoType;";
1376 my $major_version = 0;
1377 $dbh->get_info( $GetInfoType{SQL_DBMS_VER} ) =~ /^(\d{2})/
1378 && ( $major_version = sprintf("%d", $1) );
1380 if ( $major_version > 7 ) {
1382 # ideally this would be supported in DBIx-DBSchema and friends
1384 foreach my $column ( @column ) {
1385 my $columndef = dbdef->table($self->table)->column($column);
1386 unless ($columndef->type eq 'numeric') {
1388 warn "updating tax_rate column $column to numeric\n" if $DEBUG;
1389 my $sql = "ALTER TABLE tax_rate ALTER $column TYPE numeric(14,8)";
1390 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
1391 $sth->execute or die $sth->errstr;
1393 warn "updating h_tax_rate column $column to numeric\n" if $DEBUG;
1394 $sql = "ALTER TABLE h_tax_rate ALTER $column TYPE numeric(14,8)";
1395 $sth = $dbh->prepare($sql) or die $dbh->errstr;
1396 $sth->execute or die $sth->errstr;
1403 warn "WARNING: tax_rate table upgrade unsupported for this Pg version\n";
1409 warn "WARNING: tax_rate table upgrade only supported for Pg 8+\n";
1421 Mixing automatic and manual editing works poorly at present.
1425 L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base