X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Ftax_rate.pm;h=9920aa6502972669d8018d489e84f05829264f09;hb=1a2e0285a2c596cc202ef9a9f1cf0815f4d7c5ff;hp=3e9982c6c2792c61d7023e5382c28529dd44b738;hpb=016ffd08546c5d05b8e7b115db72ed137da4d087;p=freeside.git diff --git a/FS/FS/tax_rate.pm b/FS/FS/tax_rate.pm index 3e9982c6c..9920aa650 100644 --- a/FS/FS/tax_rate.pm +++ b/FS/FS/tax_rate.pm @@ -502,7 +502,9 @@ given customer (see L) =cut + #hot sub tax_on_tax { + #akshun my $self = shift; my $cust_main = shift; @@ -911,7 +913,8 @@ sub _perform_batch_import { my $files = $param->{'uploaded_files'} or die "No files provided."; - my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files; + my (%files) = map { /^(\w+):((taxdata\/\w+\.\w+\/)?[\.\w]+)$/ ? ($1,$2):() } + split /,/, $files; if ( $format eq 'cch' || $format eq 'cch-fixed' || $format eq 'cch-update' || $format eq 'cch-fixed-update' ) @@ -928,15 +931,16 @@ sub _perform_batch_import { my $deletename = ''; my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc; - my @list = ( 'GEOCODE', 'geofile', \&FS::tax_rate_location::batch_import, - '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, + 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, $file, $import_sub ) = splice( @list, 0, 3 ); + my ( $name, $import_sub ) = splice( @list, 0, 2 ); + my $file = lc($name). 'file'; unless ($files{$file}) { $error = "No $name supplied"; @@ -957,9 +961,9 @@ sub _perform_batch_import { unless $keep_cch_files; push @insert_list, $name, $insertname, $import_sub, $format; if ( $name eq 'GEOCODE' ) { #handle this whole ordering issue better - unshift @predelete_list, $name, $deletename, $import_sub; + unshift @predelete_list, $name, $deletename, $import_sub, $format; } else { - unshift @delete_list, $name, $deletename, $import_sub; + unshift @delete_list, $name, $deletename, $import_sub, $format; } } else { @@ -1261,7 +1265,7 @@ sub _remember_tax_products { if $part_pkg_taxproduct && $part_pkg_taxproduct->data_vendor eq $format; foreach my $option ( $part_pkg->part_pkg_option ) { - next unless $option->optionname =~ /^usage_taxproductnum_(\w)$/; + next unless $option->optionname =~ /^usage_taxproductnum_(\w+)$/; my $class = $1; $part_pkg_taxproduct = $part_pkg->taxproduct($class); @@ -1387,49 +1391,71 @@ sub _remove_old_tax_data { my $dbh = dbh; my $error = $job->update_statustext( "0,Removing old tax data" ); - dir $error if $error; - foreach my $tax_rate_location ( qsearch( 'tax_rate_location', - { data_vendor => $format, - disabled => '', - } - ) - ) - { - $tax_rate_location->disabled('Y'); - my $error = $tax_rate_location->replace; - return $error if $error; - } + die $error if $error; + + my $sql = "UPDATE public.tax_rate_location SET disabled='Y' ". + "WHERE data_vendor = ". $dbh->quote($format); + $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr; - local $FS::part_pkg_taxproduct::delete_kludge = 1; my @table = qw( tax_rate part_pkg_taxrate part_pkg_taxproduct tax_class cust_tax_location ); foreach my $table ( @table ) { - my $dbh = dbh; -# my $primary_key = dbdef->table($table)->primary_key; -# my $sql = "SELECT $primary_key FROM $table WHERE data_vendor = ". - my $sql = "DELETE FROM $table WHERE data_vendor = ". + $sql = "DELETE FROM public.$table WHERE data_vendor = ". $dbh->quote($format); - my $sth = $dbh->prepare($sql); - return $dbh->errstr unless ($sth); - $sth->execute or return "Failed to execute $sql: ". $sth->errstr; -# foreach my $row ( @{ $sth->fetchall_arrayref } ) { -# my $record = qsearchs( $table, { $primary_key => $row->[0] } ) -# or return "Failed to find $table with $primary_key ". $row->[0]; -# my $error = $record->delete; -# return $error if $error; -# } + $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr; } if ( $format eq 'cch' ) { - foreach my $cust_tax_location ( qsearch( 'cust_tax_location', - { data_vendor => "$format-zip" } - ) - ) - { - my $error = $cust_tax_location->delete; - return $error if $error; - } + $sql = "DELETE FROM public.cust_tax_location WHERE data_vendor = ". + $dbh->quote("$format-zip"); + $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr; + } + + ''; +} + +sub _create_temporary_tables { + my ( $job, $format ) = @_; + + my $dbh = dbh; + my $error = $job->update_statustext( "0,Creating temporary tables" ); + die $error if $error; + + my @table = qw( tax_rate + tax_rate_location + part_pkg_taxrate + part_pkg_taxproduct + tax_class + cust_tax_location + ); + foreach my $table ( @table ) { + my $sql = + "CREATE TEMPORARY TABLE $table ( LIKE $table INCLUDING DEFAULTS )"; + $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr; + } + + ''; +} + +sub _copy_from_temp { + my ( $job, $format ) = @_; + + my $dbh = dbh; + my $error = $job->update_statustext( "0,Making permanent" ); + die $error if $error; + + my @table = qw( tax_rate + tax_rate_location + part_pkg_taxrate + part_pkg_taxproduct + tax_class + cust_tax_location + ); + foreach my $table ( @table ) { + my $sql = + "INSERT INTO public.$table SELECT * from $table"; + $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr; } ''; @@ -1496,8 +1522,8 @@ sub _process_reload { my %taxproduct = (); $error ||= _remember_tax_products( $job, $format, \%taxproduct ); - #wipe out the old data - $error ||= _remove_old_tax_data( $job, $format ); + #create temp tables + $error ||= _create_temporary_tables( $job, $format ); #import new data unless ($error) { @@ -1513,6 +1539,12 @@ sub _process_reload { $error ||= _restore_remembered_disabled_taxes( $job, $format, \%disabled_tax_rate ); + #wipe out the old data + $error ||= _remove_old_tax_data( $job, $format ); + + #untemporize + $error ||= _copy_from_temp( $job, $format ); + if ($error) { $dbh->rollback or die $dbh->errstr if $oldAutoCommit; die $error; @@ -1542,7 +1574,8 @@ sub process_download_and_update { die $error if $error; } - my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/taxdata'; + my $cache_dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/'; + my $dir = $cache_dir. 'taxdata'; unless (-d $dir) { mkdir $dir or die "can't create $dir: $!\n"; } @@ -1610,19 +1643,23 @@ sub process_download_and_update { # extract csv files from the dbf files foreach my $name ( @namelist ) { - cch_extract_csv_from_dbf( $job, $dir, $name ); + _cch_extract_csv_from_dbf( $job, $dir, $name ); } # generate the diff files my @list = (); foreach my $name ( @namelist ) { - my $error = $job->update_statustext( "0,Comparing to previous $name" ); - die $error if $error; - warn "processing $dir.new/$name.txt\n" if $DEBUG; - my $olddir = $update ? "$dir.1" : ""; - my $difffile = _perform_cch_diff( $name, "$dir.new", $olddir ); - push @list, "$name:$difffile"; + my $difffile = "$dir.new/$name.txt"; + if ($update) { + my $error = $job->update_statustext( "0,Comparing to previous $name" ); + die $error if $error; + warn "processing $dir.new/$name.txt\n" if $DEBUG; + my $olddir = $update ? "$dir.1" : ""; + $difffile = _perform_cch_diff( $name, "$dir.new", $olddir ); + } + $difffile =~ s/^$cache_dir//; + push @list, "${name}file:$difffile"; } # perform the import @@ -1720,111 +1757,6 @@ sub browse_queries { 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; - - } - } - - } elsif ( $dbh->{pg_server_version} =~ /^704/ ) { - - # 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; - - foreach my $table ( qw( tax_rate h_tax_rate ) ) { - - my $sql = "ALTER TABLE $table RENAME $column TO old_$column"; - my $sth = $dbh->prepare($sql) or die $dbh->errstr; - $sth->execute or die $sth->errstr; - - my $def = dbdef->table($table)->column($column); - $def->type('numeric'); - $def->length('14,8'); - my $null = $def->null; - $def->null('NULL'); - - $sql = "ALTER TABLE $table ADD COLUMN ". $def->line($dbh); - $sth = $dbh->prepare($sql) or die $dbh->errstr; - $sth->execute or die $sth->errstr; - - $sql = "UPDATE $table SET $column = CAST( old_$column AS numeric )"; - $sth = $dbh->prepare($sql) or die $dbh->errstr; - $sth->execute or die $sth->errstr; - - unless ( $null eq 'NULL' ) { - $sql = "ALTER TABLE $table ALTER $column SET NOT NULL"; - $sth = $dbh->prepare($sql) or die $dbh->errstr; - $sth->execute or die $sth->errstr; - } - - $sql = "ALTER TABLE $table DROP old_$column"; - $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