From: jeff Date: Thu, 23 Apr 2009 20:31:26 +0000 (+0000) Subject: autodownload and update of cch tax data X-Git-Tag: root_of_svc_elec_features~1241 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=ec71691725b6c5211b6967323cbc56a03038385d autodownload and update of cch tax data --- diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm index 7be0dd0ca..ac3b1eacb 100644 --- a/FS/FS/Conf.pm +++ b/FS/FS/Conf.pm @@ -1511,6 +1511,13 @@ worry that config_items is freeside-specific and icky. }, { + 'key' => 'taxdatadirectdownload', + 'section' => 'billing', #well + 'description' => 'Enable downloading tax data directly from the vendor site', + 'type' => 'checkbox', + }, + + { 'key' => 'ignore_incalculable_taxes', 'section' => 'billing', 'description' => 'Prefer to invoice without tax over not billing at all', diff --git a/FS/FS/cust_tax_location.pm b/FS/FS/cust_tax_location.pm index 7c6989c65..161a6547b 100644 --- a/FS/FS/cust_tax_location.pm +++ b/FS/FS/cust_tax_location.pm @@ -125,19 +125,25 @@ sub check { ; return $error if $error; - #ugh! cch canada weirdness - if ($self->state eq 'CN') { + #ugh! cch canada weirdness and more + if ($self->state eq 'CN' && $self->data_vendor eq 'cch-zip' ) { $error = "Illegal cch canadian zip" unless $self->zip =~ /^[A-Z]$/; + } elsif ($self->state =~ /^E([B-DFGILNPR-UW])$/ && $self->data_vendor eq 'cch-zip' ) { + $error = "Illegal cch european zip" + unless $self->zip =~ /^E$1$/; } else { $error = $self->ut_number('zip', $self->state eq 'CN' ? 'CA' : 'US'); } return $error if $error; - #ugh! cch canada weirdness + #ugh! cch canada weirdness and more return "must specify either city/county or plus4lo/plus4hi" unless ( $self->plus4lo && $self->plus4hi || - ($self->city || $self->state eq 'CN') && $self->county + ( $self->city || + $self->state eq 'CN' || + $self->state =~ /^E([B-DFGILNPR-UW])$/ + ) && $self->county ); $self->SUPER::check; @@ -277,7 +283,7 @@ sub batch_import { if ( $job ) { # progress bar if ( time - $min_sec > $last ) { my $error = $job->update_statustext( - int( 100 * $imported / $count ) + int( 100 * $imported / $count ). ",Importing locations" ); die $error if $error; $last = time; diff --git a/FS/FS/part_pkg_taxrate.pm b/FS/FS/part_pkg_taxrate.pm index aaf7f60a2..bc1047ee2 100644 --- a/FS/FS/part_pkg_taxrate.pm +++ b/FS/FS/part_pkg_taxrate.pm @@ -353,7 +353,7 @@ sub batch_import { if ( $job ) { # progress bar if ( time - $min_sec > $last ) { my $error = $job->update_statustext( - int( 100 * $imported / $count ) + int( 100 * $imported / $count ). ",Importing tax matrix" ); die $error if $error; $last = time; diff --git a/FS/FS/tax_class.pm b/FS/FS/tax_class.pm index 480fa10a6..4f0396982 100644 --- a/FS/FS/tax_class.pm +++ b/FS/FS/tax_class.pm @@ -246,7 +246,7 @@ sub batch_import { if ( $job ) { # progress bar if ( time - $min_sec > $last ) { my $error = $job->update_statustext( - int( 100 * $imported / $count ) + int( 100 * $imported / $count ). ",Importing tax classes" ); die $error if $error; $last = time; @@ -270,7 +270,7 @@ sub batch_import { if ( $job ) { # progress bar if ( time - $min_sec > $last ) { my $error = $job->update_statustext( - int( 100 * $imported / $count ) + int( 100 * $imported / $count ). ",Importing tax classes" ); die $error if $error; $last = time; @@ -319,7 +319,7 @@ sub batch_import { if ( $job ) { # progress bar if ( time - $min_sec > $last ) { my $error = $job->update_statustext( - int( 100 * $imported / $count ) + int( 100 * $imported / $count ). ",Importing tax classes" ); die $error if $error; $last = time; diff --git a/FS/FS/tax_rate.pm b/FS/FS/tax_rate.pm index 3323e0060..dfa7d5f44 100644 --- a/FS/FS/tax_rate.pm +++ b/FS/FS/tax_rate.pm @@ -6,6 +6,11 @@ use vars qw( @ISA $DEBUG $me %tax_passtypes %GetInfoType ); use Date::Parse; use Storable qw( thaw ); +use IO::File; +use File::Temp; +use LWP::UserAgent; +use HTTP::Request; +use HTTP::Response; use MIME::Base64; use DBIx::DBSchema; use DBIx::DBSchema::Table; @@ -678,7 +683,7 @@ sub batch_import { if ( $job ) { # progress bar if ( time - $min_sec > $last ) { my $error = $job->update_statustext( - int( 100 * $imported / $count ) + int( 100 * $imported / $count ). ",Importing tax rates" ); die $error if $error; $last = time; @@ -722,7 +727,7 @@ sub batch_import { if ( $job ) { # progress bar if ( time - $min_sec > $last ) { my $error = $job->update_statustext( - int( 100 * $imported / $count ) + int( 100 * $imported / $count ). ",Importing tax rates" ); die $error if $error; $last = time; @@ -746,7 +751,7 @@ sub batch_import { if ( $job ) { # progress bar if ( time - $min_sec > $last ) { my $error = $job->update_statustext( - int( 100 * $imported / $count ) + int( 100 * $imported / $count ). ",Importing tax rates" ); die $error if $error; $last = time; @@ -780,7 +785,7 @@ sub batch_import { if ( $job ) { # progress bar if ( time - $min_sec > $last ) { my $error = $job->update_statustext( - int( 100 * $imported / $count ) + int( 100 * $imported / $count ). ",Importing tax rates" ); die $error if $error; $last = time; @@ -986,6 +991,288 @@ sub process_batch_import { } +=item process_download_and_update + +Download and process a tax update as a queued JSRPC job + +=cut + +sub process_download_and_update { + my $job = shift; + + my $param = thaw(decode_base64(shift)); + my $format = $param->{'format'}; #well... this is all cch specific + + my ( $count, $last, $min_sec, $imported ) = (0, time, 5, 0); #progressbar + $count = 100; + + if ( $job ) { # progress bar + my $error = $job->update_statustext( int( 100 * $imported / $count ) ); + die $error if $error; + } + + my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/taxdata'; + unless (-d $dir) { + mkdir $dir or die "can't create $dir: $!\n"; + } + + if ($format eq 'cch') { + + eval "use Text::CSV_XS;"; + die $@ if $@; + + eval "use XBase;"; + die $@ if $@; + + my $conffile = '%%%FREESIDE_CONF%%%/cchconf'; + my $conffh = new IO::File "<$conffile" or die "can't open $conffile: $!\n"; + my ( $urls, $secret, $states ) = + map { /^(.*)$/ or die "bad config line in $conffile: $_\n"; $1 } + <$conffh>; + + $dir .= '/cch'; + + my $oldAutoCommit = $FS::UID::AutoCommit; + local $FS::UID::AutoCommit = 0; + my $dbh = dbh; + my $error = ''; + + # really should get a table EXCLUSIVE lock here + # check if initial import or update + + my $sql = "SELECT count(*) from tax_rate WHERE data_vendor='$format'"; + my $sth = $dbh->prepare($sql) or die $dbh->errstr; + $sth->execute() or die $sth->errstr; + my $upgrade = $sth->fetchrow_arrayref->[0]; + + # create cache and/or rotate old tax data + + if (-d $dir) { + + if (-d "$dir.4") { + opendir(my $dirh, $dir) or die "failed to open $dir.4: $!\n"; + foreach my $file (readdir($dirh)) { + unlink "$dir.4/$file" if (-f "$dir.4/$file"); + } + closedir($dirh); + rmdir "$dir.4"; + } + + for (3, 2, 1) { + if ( -e "$dir.$_" ) { + rename "$dir.$_", "$dir.". ($_+1) or die "can't rename $dir.$_: $!\n"; + } + } + rename "$dir", "$dir.1" or die "can't rename $dir: $!\n"; + + } else { + + die "can't find previous tax data\n" if $upgrade; + + } + + mkdir "$dir.new" or die "can't create $dir.new: $!\n"; + + # fetch and unpack the zip files + + my $ua = new LWP::UserAgent; + foreach my $url (split ',', $urls) { + my @name = split '/', $url; #somewhat restrictive + my $name = pop @name; + $name =~ /(.*)/; # untaint that which we trust; + $name = $1; + + open my $taxfh, ">$dir.new/$name" or die "Can't open $dir.new/$name: $!\n"; + + my $res = $ua->request( + new HTTP::Request( GET => $url), + sub { #my ($data, $response_object) = @_; + print $taxfh $_[0] or die "Can't write to $dir.new/$name: $!\n"; + my $content_length = $_[1]->content_length; + $imported += length($_[0]); + if ( time - $min_sec > $last ) { + my $error = $job->update_statustext( + ($content_length ? int(100 * $imported/$content_length) : 0 ). + ",Downloading data from CCH" + ); + die $error if $error; + $last = time; + } + }, + ); + die "download of $url failed: ". $res->status_line + unless $res->is_success; + + close $taxfh; + my $error = $job->update_statustext( "0,Unpacking data" ); + die $error if $error; + $secret =~ /(.*)/; # untaint that which we trust; + $secret = $1; + system('unzip', "-P", $secret, "-d", "$dir.new", "$dir.new/$name") == 0 + or die "unzip -P $secret -d $dir.new $dir.new/$name failed"; + #unlink "$dir.new/$name"; + } + + # extract csv files from the dbf files + + foreach my $name ( qw( code detail geocode plus4 txmatrix zip ) ) { + my $error = $job->update_statustext( "0,Unpacking $name" ); + die $error if $error; + warn "opening $dir.new/$name.dbf\n" if $DEBUG; + my $table = new XBase 'name' => "$dir.new/$name.dbf"; + die "failed to access $dir.new/$name.dbf: ". XBase->errstr + unless defined($table); + $count = $table->last_record; # approximately; + $imported = 0; + open my $csvfh, ">$dir.new/$name.txt" + or die "failed to open $dir.new/$name.txt: $!\n"; + + my $csv = new Text::CSV_XS { 'always_quote' => 1 }; + my @fields = $table->field_names; + my $cursor = $table->prepare_select; + my $format_date = + sub { my $date = shift; + $date =~ /^(\d{4})(\d{2})(\d{2})$/ && ($date = "$2/$3/$1"); + $date; + }; + while (my $row = $cursor->fetch_hashref) { + $csv->combine( map { ($table->field_type($_) eq 'D') + ? &{$format_date}($row->{$_}) + : $row->{$_} + } + @fields + ); + print $csvfh $csv->string, "\n"; + $imported++; + if ( time - $min_sec > $last ) { + my $error = $job->update_statustext( + int(100 * $imported/$count). ",Unpacking $name" + ); + die $error if $error; + $last = time; + } + } + $table->close; + close $csvfh; + } + + # generate the diff files + + my @insert_list = (); + my @delete_list = (); + + my @list = ( + # 'geocode', \&FS::tax_rate_location::batch_import, + 'code', \&FS::tax_class::batch_import, + 'plus4', \&FS::cust_tax_location::batch_import, + 'zip', \&FS::cust_tax_location::batch_import, + 'txmatrix', \&FS::part_pkg_taxrate::batch_import, + 'detail', \&FS::tax_rate::batch_import, + ); + + while( scalar(@list) ) { + my ( $name, $method ) = ( shift @list, shift @list ); + my %oldlines = (); + + my $error = $job->update_statustext( "0,Comparing to previous $name" ); + die $error if $error; + + warn "processing $dir.new/$name.txt\n" if $DEBUG; + + if ($upgrade) { + open my $oldcsvfh, "$dir.1/$name.txt" + or die "failed to open $dir.1/$name.txt: $!\n"; + + while(<$oldcsvfh>) { + chomp; + $oldlines{$_} = 1; + } + close $oldcsvfh; + } + + open my $newcsvfh, "$dir.new/$name.txt" + or die "failed to open $dir.new/$name.txt: $!\n"; + + my $ifh = new File::Temp( TEMPLATE => "$name.insert.XXXXXXXX", + DIR => "$dir.new", + UNLINK => 0, #meh + ) or die "can't open temp file: $!\n"; + + my $dfh = new File::Temp( TEMPLATE => "$name.delete.XXXXXXXX", + DIR => "$dir.new", + UNLINK => 0, #meh + ) or die "can't open temp file: $!\n"; + + while(<$newcsvfh>) { + chomp; + if (exists($oldlines{$_})) { + $oldlines{$_} = 0; + } else { + print $ifh $_, ',"I"', "\n"; + } + } + close $newcsvfh; + + if ($name eq 'detail') { + for (keys %oldlines) { # one file for rate details + print $ifh $_, ',"D"', "\n" if $oldlines{$_}; + } + } else { + for (keys %oldlines) { + print $dfh $_, ',"D"', "\n" if $oldlines{$_}; + } + } + %oldlines = (); + + push @insert_list, $name, $ifh->filename, $method; + unshift @delete_list, $name, $dfh->filename, $method + unless $name eq 'detail'; + + close $dfh; + close $ifh; + } + + while( scalar(@insert_list) ) { + my ($name, $file, $method) = + (shift @insert_list, shift @insert_list, shift @insert_list); + + my $fmt = "$format-update"; + $fmt = $fmt. ( $name eq 'zip' ? '-zip' : '' ); + open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!"; + $error ||= + &{$method}({ 'filehandle' => $fh, 'format' => $fmt }, $job); + close $fh; + #unlink $file or warn "Can't delete $file: $!"; + } + + while( scalar(@delete_list) ) { + my ($name, $file, $method) = + (shift @delete_list, shift @delete_list, shift @delete_list); + + my $fmt = "$format-update"; + $fmt = $fmt. ( $name eq 'zip' ? '-zip' : '' ); + open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!"; + $error ||= + &{$method}({ 'filehandle' => $fh, 'format' => $fmt }, $job); + close $fh; + #unlink $file or warn "Can't delete $file: $!"; + } + + if ($error) { + $dbh->rollback or die $dbh->errstr if $oldAutoCommit; + die $error; + }else{ + $dbh->commit or die $dbh->errstr if $oldAutoCommit; + } + + rename "$dir.new", "$dir" + or die "cch tax update processed, but can't rename $dir.new: $!\n"; + + }else{ + die "Unknown format: $format"; + } +} + =item browse_queries PARAMS Returns a list consisting of a hashref suited for use as the argument diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index 627f9c857..409a1525a 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -227,8 +227,14 @@ tie my %tools_importing, 'Tie::IxHash', 'Import payments from CSV file' => [ $fsurl.'misc/cust_pay-import.cgi', '' ], 'Import phone numbers (DIDs)' => [ $fsurl.'misc/phone_avail-import.html', '' ], 'Import Call Detail Records (CDRs) from CSV file' => [ $fsurl.'misc/cdr-import.html', '' ], - 'Import tax rates from CSV files' => [ $fsurl.'misc/tax-import.cgi', '' ], ; +if ( $conf->exists('taxdatadirectdownload') ) { + $tools_importing{'Import tax rates from vendor site'} = + [ $fsurl.'misc/tax-fetch_and_import.cgi', '' ]; +} else { + $tools_importing{'Import tax rates from CSV files'} = + [ $fsurl.'misc/tax-import.cgi', '' ]; +} tie my %tools_exporting, 'Tie::IxHash', 'Download database dump' => [ $fsurl. 'misc/dump.cgi', '' ], diff --git a/httemplate/misc/process/tax-fetch_and_import.cgi b/httemplate/misc/process/tax-fetch_and_import.cgi new file mode 100644 index 000000000..553c7551a --- /dev/null +++ b/httemplate/misc/process/tax-fetch_and_import.cgi @@ -0,0 +1,9 @@ +<% $server->process %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Configuration'); + +my $server = new FS::UI::Web::JSRPC 'FS::tax_rate::process_download_and_update', $cgi; + + diff --git a/httemplate/misc/tax-fetch_and_import.cgi b/httemplate/misc/tax-fetch_and_import.cgi new file mode 100644 index 000000000..33a6c9b01 --- /dev/null +++ b/httemplate/misc/tax-fetch_and_import.cgi @@ -0,0 +1,48 @@ +<% include("/elements/header.html",'Tax Rate Download and Import') %> + +Import a tax data update. +

+ +<% include( '/elements/progress-init.html', 'TaxRateImport',[ 'format', ], + 'process/tax-fetch_and_import.cgi', { 'message' => 'Tax rates imported' }, + ) +%> + +
+<% &ntable("#cccccc", 2) %> + + + Format + + + + + + Update Password + + + + + + + + + + + + + +
+ +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Import'); + +