X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Ftax_rate.pm;h=a5a623d948a427e7017639e1947cdef1f41d38f9;hb=e5d6af11f52aabe8c9e6e12ce43c3401ccf025a8;hp=334c31e953e7549df45f42d86ea2125ef25aaecb;hpb=0fb307c305e4bc2c9c27dc25a3308beae3a4d33c;p=freeside.git diff --git a/FS/FS/tax_rate.pm b/FS/FS/tax_rate.pm index 334c31e95..a5a623d94 100644 --- a/FS/FS/tax_rate.pm +++ b/FS/FS/tax_rate.pm @@ -10,6 +10,7 @@ use DateTime::Format::Strptime; use Storable qw( thaw nfreeze ); use IO::File; use File::Temp; +use Text::CSV_XS; use LWP::UserAgent; use HTTP::Request; use HTTP::Response; @@ -637,6 +638,7 @@ sub batch_import { $count *=2; if ( $format eq 'cch' || $format eq 'cch-update' ) { + #false laziness w/below (sub _perform_cch_diff) @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax excessrate effective_date taxauth taxtype taxcat taxname usetax useexcessrate fee unittype feemax maxtype passflag @@ -715,9 +717,6 @@ sub batch_import { die "unknown format $format"; } - eval "use Text::CSV_XS;"; - die $@ if $@; - my $csv = new Text::CSV_XS; my $imported = 0; @@ -758,9 +757,10 @@ sub batch_import { foreach my $field ( @fields ) { $tax_rate{$field} = shift @columns; } + if ( scalar( @columns ) ) { $dbh->rollback if $oldAutoCommit; - return "Unexpected trailing columns in line (wrong format?): $line"; + return "Unexpected trailing columns in line (wrong format?) importing tax_rate: $line"; } my $error = &{$hook}(\%tax_rate); @@ -993,7 +993,7 @@ sub _perform_batch_import { } push @insert_list, - 'DETAIL', "$dir/".$files{detail}, \&FS::tax_rate::batch_import, $format + 'DETAIL', "$dir/".$files{detailfile}, \&FS::tax_rate::batch_import, $format if $format =~ /update/; $error ||= _perform_cch_tax_import( $job, @@ -1115,8 +1115,26 @@ sub _perform_cch_diff { } close $newcsvfh; - for (keys %oldlines) { - print $dfh $_, ',"D"', "\n" if $oldlines{$_}; + #false laziness w/above (sub batch_import) + my @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax + excessrate effective_date taxauth taxtype taxcat taxname + usetax useexcessrate fee unittype feemax maxtype passflag + passtype basetype ); + my $numfields = scalar(@fields); + + my $csv = new Text::CSV_XS { 'always_quote' => 1 }; + + for my $line (grep $oldlines{$_}, keys %oldlines) { + + $csv->parse($line) or do { + #$dbh->rollback if $oldAutoCommit; + die "can't parse: ". $csv->error_input(); + }; + my @columns = $csv->fields(); + + $csv->combine( splice(@columns, 0, $numfields) ); + + print $dfh $csv->string, ',"D"', "\n"; } close $dfh; @@ -1170,9 +1188,6 @@ sub _cch_fetch_and_unzip { sub _cch_extract_csv_from_dbf { my ( $job, $dir, $name ) = @_; - eval "use Text::CSV_XS;"; - die $@ if $@; - eval "use XBase;"; die $@ if $@; @@ -1196,9 +1211,14 @@ sub _cch_extract_csv_from_dbf { $date; }; while (my $row = $cursor->fetch_hashref) { - $csv->combine( map { ($table->field_type($_) eq 'D') - ? &{$format_date}($row->{$_}) - : $row->{$_} + $csv->combine( map { my $type = $table->field_type($_); + if ($type eq 'D') { + &{$format_date}($row->{$_}) ; + } elsif ($type eq 'N' && $row->{$_} =~ /e-/i ) { + sprintf('%.8f', $row->{$_}); #db row is numeric(14,8) + } else { + $row->{$_}; + } } @fields ); @@ -1630,16 +1650,16 @@ sub process_download_and_update { if (-d $dir) { - if (-d "$dir.4") { - opendir(my $dirh, "$dir.4") or die "failed to open $dir.4: $!\n"; + if (-d "$dir.9") { + opendir(my $dirh, "$dir.9") or die "failed to open $dir.9: $!\n"; foreach my $file (readdir($dirh)) { - unlink "$dir.4/$file" if (-f "$dir.4/$file"); + unlink "$dir.9/$file" if (-f "$dir.9/$file"); } closedir($dirh); - rmdir "$dir.4"; + rmdir "$dir.9"; } - for (3, 2, 1) { + for (8, 7, 6, 5, 4, 3, 2, 1) { if ( -e "$dir.$_" ) { rename "$dir.$_", "$dir.". ($_+1) or die "can't rename $dir.$_: $!\n"; } @@ -1843,12 +1863,9 @@ sub generate_liability_report { $where .= ' AND cust_main.agentnum = '. $agent->agentnum; } - # my ( $location_sql, @location_param ) = FS::cust_pkg->location_sql; - # $where .= " AND $location_sql"; - #my @taxparam = ( 'itemdesc', @location_param ); - # now something along the lines of geocode matching ? - #$where .= FS::cust_pkg->_location_sql_where('cust_tax_location');; - my @taxparam = ( 'itemdesc', 'tax_rate_location.state', 'tax_rate_location.county', 'tax_rate_location.city', 'cust_bill_pkg_tax_rate_location.locationtaxid' ); + #my @taxparam = ( 'itemdesc', 'tax_rate_location.state', 'tax_rate_location.county', 'tax_rate_location.city', 'cust_bill_pkg_tax_rate_location.locationtaxid' ); + my @taxparams = qw( city county state locationtaxid ); + my @params = ('itemdesc', @taxparams); my $select = 'DISTINCT itemdesc,locationtaxid,tax_rate_location.state,tax_rate_location.county,tax_rate_location.city'; @@ -1879,13 +1896,13 @@ sub generate_liability_report { if ( $args{job} ) { if ( time - $min_sec > $last ) { $args{job}->update_statustext( int( 100 * $calculated / $count ). - ",Calculated" + ",Calculating" ); $last = time; } } - my @params = map { my $f = $_; $f =~ s/.*\.//; $f } @taxparam; + #my @params = map { my $f = $_; $f =~ s/.*\.//; $f } @taxparam; my $label = join('~', map { $t->$_ } @params); $label = 'Tax'. $label if $label =~ /^~/; unless ( exists( $taxes{$label} ) ) { @@ -1895,24 +1912,31 @@ sub generate_liability_report { $taxes{$label}->{'url_param'} = join(';', map { "$_=". uri_escape($t->$_) } @params); - my $taxwhere = "FROM cust_bill_pkg $addl_from $where AND payby != 'COMP' ". - "AND ". join( ' AND ', map { "( $_ = ? OR ? = '' AND $_ IS NULL)" } @taxparam ); + my $payby_itemdesc_loc = + " payby != 'COMP' ". + "AND ( itemdesc = ? OR ? = '' AND itemdesc IS NULL ) ". + "AND ". FS::tax_rate_location->location_sql( map { $_ => $t->$_ } + @taxparams + ); + + my $taxwhere = + "FROM cust_bill_pkg $addl_from $where AND $payby_itemdesc_loc"; my $sql = "SELECT SUM(amount) $taxwhere AND cust_bill_pkg.pkgnum = 0"; - my $x = &{$scalar_sql}($t, [ map { $_, $_ } @params ], $sql ); + my $x = &{$scalar_sql}($t, [ 'itemdesc', 'itemdesc' ], $sql ); $tax += $x; $taxes{$label}->{'tax'} += $x; - my $creditfrom = " JOIN cust_credit_bill_pkg USING (billpkgnum,billpkgtaxratelocationnum) "; - my $creditwhere = "FROM cust_bill_pkg $addl_from $creditfrom $where ". - "AND payby != 'COMP' ". - "AND ". join( ' AND ', map { "( $_ = ? OR ? = '' AND $_ IS NULL)" } @taxparam ); + my $creditfrom = + "JOIN cust_credit_bill_pkg USING (billpkgnum,billpkgtaxratelocationnum)"; + my $creditwhere = + "FROM cust_bill_pkg $addl_from $creditfrom $where AND $payby_itemdesc_loc"; $sql = "SELECT SUM(cust_credit_bill_pkg.amount) ". " $creditwhere AND cust_bill_pkg.pkgnum = 0"; - my $y = &{$scalar_sql}($t, [ map { $_, $_ } @params ], $sql ); + my $y = &{$scalar_sql}($t, [ 'itemdesc', 'itemdesc' ], $sql ); $credit += $y; $taxes{$label}->{'credit'} += $y; @@ -1972,7 +1996,7 @@ sub generate_liability_report { $dateagentlink .= ';agentnum='. $args{agentnum} if length($agentname); my $baselink = $args{p}. "search/cust_bill_pkg.cgi?$dateagentlink"; - + my $creditlink = $args{p}. "search/cust_credit_bill_pkg.html?$dateagentlink"; print $report < - Tax collected + Tax invoiced      Tax credited @@ -2040,7 +2064,7 @@ EOF <% ($tax->{base}) ? qq!! : '' %> - <% '$money_char' %><% sprintf('%.2f', $tax->{'credit'} ) %> + <% '$money_char' %><% sprintf('%.2f', $tax->{'credit'} ) %> <% !($tax->{base}) ? qq!! : '' %>