use strict;
use vars qw( @ISA $DEBUG $me
%tax_unittypes %tax_maxtypes %tax_basetypes %tax_authorities
- %tax_passtypes );
+ %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 FS::Record qw( qsearchs dbh );
+use DBIx::DBSchema;
+use DBIx::DBSchema::Table;
+use DBIx::DBSchema::Column;
+use FS::Record qw( qsearch qsearchs dbh dbdef );
use FS::tax_class;
use FS::cust_bill_pkg;
use FS::cust_tax_location;
use FS::part_pkg_taxrate;
+use FS::cust_main;
+use FS::Misc qw( csv_from_fixed );
@ISA = qw( FS::Record );
|| $self->ut_textn('data_vendor')
|| $self->ut_textn('location')
|| $self->ut_foreign_key('taxclassnum', 'tax_class', 'taxclassnum')
- || $self->ut_numbern('effective_date')
+ || $self->ut_snumbern('effective_date')
|| $self->ut_float('tax')
|| $self->ut_floatn('excessrate')
|| $self->ut_money('taxbase')
|| $self->ut_enum('setuptax', [ '', 'Y' ] )
|| $self->ut_enum('recurtax', [ '', 'Y' ] )
|| $self->ut_enum('manual', [ '', 'Y' ] )
+ || $self->ut_enum('disabled', [ '', 'Y' ] )
|| $self->SUPER::check
;
'11' => 'gross profits',
'12' => 'tariff rate',
'14' => 'account',
+ '15' => 'prior year gross receipts',
);
sub basetype_name {
$tax_passtypes{$self->passtype};
}
-=item taxline CUST_BILL_PKG, ...
+=item taxline TAXABLES, [ OPTIONSHASH ]
Returns a listref of a name and an amount of tax calculated for the list
-of packages. If an error occurs, a message is returned as a scalar.
+of packages/amounts referenced by TAXABLES. If an error occurs, a message
+is returned as a scalar.
=cut
sub taxline {
my $self = shift;
- my @cust_bill_pkg = @_;
+
+ my $taxables;
+ my %opt = ();
+
+ if (ref($_[0]) eq 'ARRAY') {
+ $taxables = shift;
+ %opt = @_;
+ }else{
+ $taxables = [ @_ ];
+ #exemptions would be broken in this case
+ }
+
+ my $name = $self->taxname;
+ $name = 'Other surcharges'
+ if ($self->passtype == 2);
+ my $amount = 0;
+
+ if ( $self->disabled ) { # we always know how to handle disabled taxes
+ return {
+ 'name' => $name,
+ 'amount' => $amount,
+ };
+ }
+
+ my $taxable_charged = 0;
+ my @cust_bill_pkg = grep { $taxable_charged += $_ unless ref; ref; }
+ @$taxables;
warn "calculating taxes for ". $self->taxnum. " on ".
join (",", map { $_->pkgnum } @cust_bill_pkg)
if $DEBUG;
if ($self->passflag eq 'N') {
- return "fatal: can't (yet) handle taxes not passed to the customer";
+ # return "fatal: can't (yet) handle taxes not passed to the customer";
+ # until someone needs to track these in freeside
+ return {
+ 'name' => $name,
+ 'amount' => 0,
+ };
}
if ($self->maxtype != 0 && $self->maxtype != 9) {
- return qq!fatal: can't (yet) handle tax with "!. $self->maxtype_name.
- '" threshold';
+ return $self->_fatal_or_null( 'tax with "'.
+ $self->maxtype_name. '" threshold'
+ );
}
if ($self->maxtype == 9) {
- return qq!fatal: can't (yet) handle tax with "!. $self->maxtype_name.
- '" threshold'; # "texas" tax
+ return
+ $self->_fatal_or_null( 'tax with "'. $self->maxtype_name. '" threshold' );
+ # "texas" tax
}
+ # we treat gross revenue as gross receipts and expect the tax data
+ # to DTRT (i.e. tax on tax rules)
if ($self->basetype != 0 && $self->basetype != 1 &&
- $self->basetype != 6 && $self->basetype != 7 &&
+ $self->basetype != 5 && $self->basetype != 6 &&
+ $self->basetype != 7 && $self->basetype != 8 &&
$self->basetype != 14
) {
- return qq!fatal: can't (yet) handle tax with "!. $self->basetype_name.
- '" basis';
+ return
+ $self->_fatal_or_null( 'tax with "'. $self->basetype_name. '" basis' );
}
- my $name = $self->taxname;
- $name = 'Other surcharges'
- if ($self->passtype == 2);
- my $amount = 0;
-
- my $taxable_charged = 0;
unless ($self->setuptax =~ /^Y$/i) {
$taxable_charged += $_->setup foreach @cust_bill_pkg;
}
my $taxable_units = 0;
unless ($self->recurtax =~ /^Y$/i) {
if ($self->unittype == 0) {
- $taxable_units += $_->units foreach @cust_bill_pkg;
+ my %seen = ();
+ foreach (@cust_bill_pkg) {
+ $taxable_units += $_->units
+ unless $seen{$_->pkgnum};
+ $seen{$_->pkgnum}++;
+ }
}elsif ($self->unittype == 1) {
- return qq!fatal: can't (yet) handle fee with minute unit type!;
+ return $self->_fatal_or_null( 'fee with minute unit type' );
}elsif ($self->unittype == 2) {
$taxable_units = 1;
}else {
- return qq!fatal: can't (yet) handle unknown unit type in tax!.
- $self->taxnum;
+ return $self->_fatal_or_null( 'unknown unit type in tax'. $self->taxnum );
}
}
warn "calculated taxes as [ $name, $amount ]\n"
if $DEBUG;
- return [$name, $amount];
+ return {
+ 'name' => $name,
+ 'amount' => $amount,
+ };
+
+}
+
+sub _fatal_or_null {
+ my ($self, $error) = @_;
+
+ my $conf = new FS::Conf;
+
+ $error = "fatal: can't yet handle ". $error;
+ my $name = $self->taxname;
+ $name = 'Other surcharges'
+ if ($self->passtype == 2);
+
+ if ($conf->exists('ignore_incalculable_taxes')) {
+ warn $error;
+ return { name => $name, amount => 0 };
+ } else {
+ return $error;
+ }
+}
+
+=item tax_on_tax CUST_MAIN
+
+Returns a list of taxes which are candidates for taxing taxes for the
+given customer (see L<FS::cust_main>)
+
+=cut
+
+sub tax_on_tax {
+ my $self = shift;
+ my $cust_main = shift;
+
+ warn "looking up taxes on tax ". $self->taxnum. " for customer ".
+ $cust_main->custnum
+ if $DEBUG;
+
+ my $geocode = $cust_main->geocode($self->data_vendor);
+
+ # CCH oddness in m2m
+ my $dbh = dbh;
+ my $extra_sql = ' AND ('.
+ join(' OR ', map{ 'geocode = '. $dbh->quote(substr($geocode, 0, $_)) }
+ qw(10 5 2)
+ ).
+ ')';
+
+ my $order_by = 'ORDER BY taxclassnum, length(geocode) desc';
+ my $select = 'DISTINCT ON(taxclassnum) *';
+
+ # should qsearch preface columns with the table to facilitate joins?
+ my @taxclassnums = map { $_->taxclassnum }
+ qsearch( { 'table' => 'part_pkg_taxrate',
+ 'select' => $select,
+ 'hashref' => { 'data_vendor' => $self->data_vendor,
+ 'taxclassnumtaxed' => $self->taxclassnum,
+ },
+ 'extra_sql' => $extra_sql,
+ 'order_by' => $order_by,
+ } );
+
+ return () unless @taxclassnums;
+
+ $extra_sql =
+ "AND (". join(' OR ', map { "taxclassnum = $_" } @taxclassnums ). ")";
+
+ qsearch({ 'table' => 'tax_rate',
+ 'hashref' => { 'geocode' => $geocode, },
+ 'extra_sql' => $extra_sql,
+ })
}
my @fields;
my $hook;
+ my @column_lengths = ();
+ my @column_callbacks = ();
+ if ( $format eq 'cch-fixed' || $format eq 'cch-fixed-update' ) {
+ $format =~ s/-fixed//;
+ my $date_format = sub { my $r='';
+ /^(\d{4})(\d{2})(\d{2})$/ && ($r="$1/$2/$3");
+ $r;
+ };
+ my $trim = sub { my $r = shift; $r =~ s/^\s*//; $r =~ s/\s*$//; $r };
+ 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 );
+ push @column_lengths, 1 if $format eq 'cch-update';
+ push @column_callbacks, $trim foreach (@column_lengths); # 5, 6, 15, 17 esp
+ $column_callbacks[8] = $date_format;
+ }
+
my $line;
my ( $count, $last, $min_sec ) = (0, time, 5); #progressbar
- if ( $job ) {
- $count++
- while ( defined($line=<$fh>) );
- seek $fh, 0, 0;
+ if ( $job || scalar(@column_callbacks) ) {
+ my $error =
+ csv_from_fixed(\$fh, \$count, \@column_lengths, \@column_callbacks);
+ return $error if $error;
}
$count *=2;
}
my $actionflag = delete($hash->{'actionflag'});
+
+ $hash->{'taxname'} =~ s/`/'/g;
+ $hash->{'taxname'} =~ s|\\|/|g;
+
+ return '' if $format eq 'cch'; # but not cch-update
+
if ($actionflag eq 'I') {
- $insert{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = $hash;
+ $insert{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
}elsif ($actionflag eq 'D') {
- $delete{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = $hash;
+ $delete{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
}else{
return "Unexpected action flag: ". $hash->{'actionflag'};
}
+ delete($hash->{$_}) for keys %$hash;
+
'';
};
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;
return $error;
}
+ if (scalar(keys %tax_rate)) { #inserts only, not updates for cch
+
+ my $tax_rate = new FS::tax_rate( \%tax_rate );
+ $error = $tax_rate->insert;
+
+ if ( $error ) {
+ $dbh->rollback if $oldAutoCommit;
+ return "can't insert tax_rate for $line: $error";
+ }
+
+ }
+
$imported++;
}
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;
if ( $error ) {
$dbh->rollback if $oldAutoCommit;
+ my $hashref = $insert{$_};
+ $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
return "can't insert tax_rate for $line: $error";
}
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;
#join(" ", map { "$_ => ". $old->{$_} } @fields);
join(" ", map { "$_ => ". $old->{$_} } keys(%$old) );
}
- my $new = new FS::tax_rate( $insert{$_} );
+ my $new = new FS::tax_rate({ $old->hash, %{$insert{$_}}, 'manual' => '' });
$new->taxnum($old->taxnum);
my $error = $new->replace($old);
if ( $error ) {
$dbh->rollback if $oldAutoCommit;
- return "can't insert tax_rate for $line: $error";
+ my $hashref = $insert{$_};
+ $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
+ return "can't replace tax_rate for $line: $error";
}
$imported++;
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;
if ( $error ) {
$dbh->rollback if $oldAutoCommit;
- return "can't insert tax_rate for $line: $error";
+ my $hashref = $delete{$_};
+ $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
+ return "can't delete tax_rate for $line: $error";
}
$imported++;
$dbh->commit or die $dbh->errstr if $oldAutoCommit;
- return "Empty file!" unless $imported;
+ return "Empty file!" unless ($imported || $format eq 'cch-update');
''; #no error
}
-=item process_batch
+=item process_batch_import
-Load an batch import as a queued JSRPC job
+Load a batch import as a queued JSRPC job
=cut
-sub process_batch {
+sub process_batch_import {
my $job = shift;
my $param = thaw(decode_base64(shift));
my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files;
- if ($format eq 'cch') {
+ if ($format eq 'cch' || $format eq 'cch-fixed') {
my $oldAutoCommit = $FS::UID::AutoCommit;
local $FS::UID::AutoCommit = 0;
my $dbh = dbh;
my $error = '';
+ my $have_location = 0;
my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import,
'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
+ 'ZIP', 'zipfile', \&FS::cust_tax_location::batch_import,
'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
'DETAIL', 'detail', \&FS::tax_rate::batch_import,
);
while( scalar(@list) ) {
my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
unless ($files{$file}) {
+ next if $name eq 'PLUS4';
$error = "No $name supplied";
+ $error = "Neither PLUS4 nor ZIP supplied"
+ if ($name eq 'ZIP' && !$have_location);
next;
}
- my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc;
+ $have_location = 1 if $name eq 'PLUS4';
+ my $fmt = $format. ( $name eq 'ZIP' ? '-zip' : '' );
+ my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
my $filename = "$dir/". $files{$file};
open my $fh, "< $filename" or $error ||= "Can't open $name file: $!";
- $error ||= &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
+ $error ||= &{$import_sub}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
close $fh;
unlink $filename or warn "Can't delete $filename: $!";
}
$dbh->commit or die $dbh->errstr if $oldAutoCommit;
}
- }elsif ($format eq 'cch-update') {
+ }elsif ($format eq 'cch-update' || $format eq 'cch-fixed-update') {
my $oldAutoCommit = $FS::UID::AutoCommit;
local $FS::UID::AutoCommit = 0;
my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import,
'PLUS4', 'plus4file', \&FS::cust_tax_location::batch_import,
+ 'ZIP', 'zipfile', \&FS::cust_tax_location::batch_import,
'TXMATRIX', 'txmatrix', \&FS::part_pkg_taxrate::batch_import,
);
- my $dir = $FS::UID::conf_dir. "/cache.". $FS::UID::datasrc;
+ my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
while( scalar(@list) ) {
my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
unless ($files{$file}) {
+ my $vendor = $name eq 'ZIP' ? 'cch' : 'cch-zip';
+ next # update expected only for previously installed location data
+ if ( ($name eq 'PLUS4' || $name eq 'ZIP')
+ && !scalar( qsearch( { table => 'cust_tax_location',
+ hashref => { data_vendor => $vendor },
+ select => 'DISTINCT data_vendor',
+ } )
+ )
+ );
+
$error = "No $name supplied";
next;
}
UNLINK => 0, #meh
) or die "can't open temp file: $!\n";
+ my $insert_pattern = ($format eq 'cch-update') ? qr/"I"\s*$/ : qr/I\s*$/;
+ my $delete_pattern = ($format eq 'cch-update') ? qr/"D"\s*$/ : qr/D\s*$/;
while(<$fh>) {
my $handle = '';
- $handle = $ifh if $_ =~ /"I"\s*$/;
- $handle = $dfh if $_ =~ /"D"\s*$/;
+ $handle = $ifh if $_ =~ /$insert_pattern/;
+ $handle = $dfh if $_ =~ /$delete_pattern/;
unless ($handle) {
$error = "bad input line: $_" unless $handle;
last;
my ($name, $file, $import_sub) =
(shift @insert_list, shift @insert_list, shift @insert_list);
+ my $fmt = $format. ( $name eq 'ZIP' ? '-zip' : '' );
open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
$error ||=
- &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
+ &{$import_sub}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
close $fh;
unlink $file or warn "Can't delete $file: $!";
}
- $error = "No DETAIL supplied"
+ $error ||= "No DETAIL supplied"
unless ($files{detail});
open my $fh, "< $dir/". $files{detail}
or $error ||= "Can't open DETAIL file: $!";
my ($name, $file, $import_sub) =
(shift @delete_list, shift @delete_list, shift @delete_list);
+ my $fmt = $format. ( $name eq 'ZIP' ? '-zip' : '' );
open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
$error ||=
- &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
+ &{$import_sub}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
close $fh;
unlink $file or warn "Can't delete $file: $!";
}
}
+=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
+to qsearch, and sql query string. Each is based on the PARAMS hashref
+of keys and values which frequently would be passed as C<scalar($cgi->Vars)>
+from a form. This conveniently creates the query hashref and count_query
+string required by the browse and search elements. As a side effect,
+the PARAMS hashref is untainted and keys with unexpected values are removed.
+
+=cut
+
+sub browse_queries {
+ my $params = shift;
+
+ my $query = {
+ 'table' => 'tax_rate',
+ 'hashref' => {},
+ 'order_by' => 'ORDER BY geocode, taxclassnum',
+ },
+
+ my $extra_sql = '';
+
+ if ( $params->{data_vendor} =~ /^(\w+)$/ ) {
+ $extra_sql .= ' WHERE data_vendor = '. dbh->quote($1);
+ } else {
+ delete $params->{data_vendor};
+ }
+
+ if ( $params->{geocode} =~ /^(\w+)$/ ) {
+ $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
+ 'geocode LIKE '. dbh->quote($1.'%');
+ } else {
+ delete $params->{geocode};
+ }
+
+ if ( $params->{taxclassnum} =~ /^(\d+)$/ &&
+ qsearchs( 'tax_class', {'taxclassnum' => $1} )
+ )
+ {
+ $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
+ ' taxclassnum = '. dbh->quote($1)
+ } else {
+ delete $params->{taxclassnun};
+ }
+
+ my $tax_type = $1
+ if ( $params->{tax_type} =~ /^(\d+)$/ );
+ delete $params->{tax_type}
+ unless $tax_type;
+
+ my $tax_cat = $1
+ if ( $params->{tax_cat} =~ /^(\d+)$/ );
+ delete $params->{tax_cat}
+ unless $tax_cat;
+
+ my @taxclassnum = ();
+ if ($tax_type || $tax_cat ) {
+ my $compare = "LIKE '". ( $tax_type || "%" ). ":". ( $tax_cat || "%" ). "'";
+ $compare = "= '$tax_type:$tax_cat'" if ($tax_type && $tax_cat);
+ @taxclassnum = map { $_->taxclassnum }
+ qsearch({ 'table' => 'tax_class',
+ 'hashref' => {},
+ 'extra_sql' => "WHERE taxclass $compare",
+ });
+ }
+
+ $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ). '( '.
+ join(' OR ', map { " taxclassnum = $_ " } @taxclassnum ). ' )'
+ if ( @taxclassnum );
+
+ unless ($params->{'showdisabled'}) {
+ $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
+ "( disabled = '' OR disabled IS NULL )";
+ }
+
+ $query->{extra_sql} = $extra_sql;
+
+ return ($query, "SELECT COUNT(*) FROM tax_rate $extra_sql");
+}
+
+# _upgrade_data
+#
+# Used by FS::Upgrade to migrate to a new database.
+#
+#
+
+sub _upgrade_data { # class method
+ my ($self, %opts) = @_;
+ my $dbh = dbh;
+
+ warn "$me upgrading $self\n" if $DEBUG;
+
+ my @column = qw ( tax excessrate usetax useexcessrate fee excessfee
+ feebase feemax );
+
+ if ( $dbh->{Driver}->{Name} eq 'Pg' ) {
+
+ eval "use DBI::Const::GetInfoType;";
+ die $@ if $@;
+
+ my $major_version = 0;
+ $dbh->get_info( $GetInfoType{SQL_DBMS_VER} ) =~ /^(\d{2})/
+ && ( $major_version = sprintf("%d", $1) );
+
+ if ( $major_version > 7 ) {
+
+ # ideally this would be supported in DBIx-DBSchema and friends
+
+ foreach my $column ( @column ) {
+ my $columndef = dbdef->table($self->table)->column($column);
+ unless ($columndef->type eq 'numeric') {
+
+ warn "updating tax_rate column $column to numeric\n" if $DEBUG;
+ my $sql = "ALTER TABLE tax_rate ALTER $column TYPE numeric(14,8)";
+ my $sth = $dbh->prepare($sql) or die $dbh->errstr;
+ $sth->execute or die $sth->errstr;
+
+ warn "updating h_tax_rate column $column to numeric\n" if $DEBUG;
+ $sql = "ALTER TABLE h_tax_rate ALTER $column TYPE numeric(14,8)";
+ $sth = $dbh->prepare($sql) or die $dbh->errstr;
+ $sth->execute or die $sth->errstr;
+
+ }
+ }
+
+ } else {
+
+ warn "WARNING: tax_rate table upgrade unsupported for this Pg version\n";
+
+ }
+
+ } else {
+
+ warn "WARNING: tax_rate table upgrade only supported for Pg 8+\n";
+
+ }
+
+ '';
+
+}
+
=back
=head1 BUGS
projects / freeside.git / blobdiff