X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2Ftax_rate.pm;h=265383398f7983a3a4d81f64b584696e9b301dc1;hp=b375a661be318d5f98ea08e236d3a3926e31d6ce;hb=66720bf1f5bae4b1d4022eabff456c247447fbc1;hpb=1702ffad58949ebe9e4769afeb9fe9c93249cf69 diff --git a/FS/FS/tax_rate.pm b/FS/FS/tax_rate.pm index b375a661b..265383398 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; @@ -18,6 +19,7 @@ use DBIx::DBSchema; use DBIx::DBSchema::Table; use DBIx::DBSchema::Column; use FS::Record qw( qsearch qsearchs dbh dbdef ); +use FS::Conf; use FS::tax_class; use FS::cust_bill_pkg; use FS::cust_tax_location; @@ -443,20 +445,35 @@ sub taxline { my $taxable_units = 0; unless ($self->recurtax =~ /^Y$/i) { - if (( $self->unittype || 0 ) == 0) { + + if (( $self->unittype || 0 ) == 0) { #access line my %seen = (); foreach (@cust_bill_pkg) { $taxable_units += $_->units - unless $seen{$_->pkgnum}; - $seen{$_->pkgnum}++; + unless $seen{$_->pkgnum}++; } - }elsif ($self->unittype == 1) { + + } elsif ($self->unittype == 1) { #minute return $self->_fatal_or_null( 'fee with minute unit type' ); - }elsif ($self->unittype == 2) { - $taxable_units = 1; - }else { + + } elsif ($self->unittype == 2) { #account + + my $conf = new FS::Conf; + if ( $conf->exists('tax-pkg_address') ) { + #number of distinct locations + my %seen = (); + foreach (@cust_bill_pkg) { + $taxable_units++ + unless $seen{$_->cust_pkg->locationnum}++; + } + } else { + $taxable_units = 1; + } + + } else { return $self->_fatal_or_null( 'unknown unit type in tax'. $self->taxnum ); } + } # @@ -621,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 @@ -699,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; @@ -742,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); @@ -769,7 +785,8 @@ sub batch_import { } - for (grep { !exists($delete{$_}) } keys %insert) { + my @replace = grep { exists($delete{$_}) } keys %insert; + for (@replace) { if ( $job ) { # progress bar if ( time - $min_sec > $last ) { my $error = $job->update_statustext( @@ -783,20 +800,35 @@ sub batch_import { } } - my $tax_rate = new FS::tax_rate( $insert{$_} ); - my $error = $tax_rate->insert; + my $old = qsearchs( 'tax_rate', $delete{$_} ); - 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 ( $old ) { + + 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; + my $hashref = $insert{$_}; + $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) ); + return "can't replace tax_rate for $line: $error"; + } + + $imported++; + + } else { + + $old = delete $delete{$_}; + warn "WARNING: can't find tax_rate to replace (inserting instead and continuing) for: ". + #join(" ", map { "$_ => ". $old->{$_} } @fields); + join(" ", map { "$_ => ". $old->{$_} } keys(%$old) ); } $imported++; } - for (grep { exists($delete{$_}) } keys %insert) { + for (grep { !exists($delete{$_}) } keys %insert) { if ( $job ) { # progress bar if ( time - $min_sec > $last ) { my $error = $job->update_statustext( @@ -810,27 +842,17 @@ sub batch_import { } } - my $old = qsearchs( 'tax_rate', $delete{$_} ); - unless ($old) { - $dbh->rollback if $oldAutoCommit; - $old = $delete{$_}; - return "can't find tax_rate to replace for: ". - #join(" ", map { "$_ => ". $old->{$_} } @fields); - join(" ", map { "$_ => ". $old->{$_} } keys(%$old) ); - } - my $new = new FS::tax_rate({ $old->hash, %{$insert{$_}}, 'manual' => '' }); - $new->taxnum($old->taxnum); - my $error = $new->replace($old); + my $tax_rate = new FS::tax_rate( $insert{$_} ); + my $error = $tax_rate->insert; if ( $error ) { $dbh->rollback if $oldAutoCommit; my $hashref = $insert{$_}; $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) ); - return "can't replace tax_rate for $line: $error"; + return "can't insert tax_rate for $line: $error"; } $imported++; - $imported++; } for (grep { !exists($insert{$_}) } keys %delete) { @@ -945,7 +967,7 @@ sub _perform_batch_import { my $file = lc($name). 'file'; unless ($files{$file}) { - $error = "No $name supplied"; + #$error = "No $name supplied"; next; } next if $name eq 'DETAIL' && $format =~ /update/; @@ -962,7 +984,7 @@ sub _perform_batch_import { unlink $filename or warn "Can't delete $filename: $!" unless $keep_cch_files; push @insert_list, $name, $insertname, $import_sub, $format; - if ( $name eq 'GEOCODE' ) { #handle this whole ordering issue better + if ( $name eq 'GEOCODE' || $name eq 'CODE' ) { #handle this whole ordering issue better unshift @predelete_list, $name, $deletename, $import_sub, $format; } else { unshift @delete_list, $name, $deletename, $import_sub, $format; @@ -977,13 +999,20 @@ 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/; + my %addl_param = (); + if ( $param->{'delete_only'} ) { + $addl_param{'delete_only'} = $param->{'delete_only'}; + @insert_list = () + } + $error ||= _perform_cch_tax_import( $job, [ @predelete_list ], [ @insert_list ], [ @delete_list ], + \%addl_param, ); @@ -1008,7 +1037,8 @@ sub _perform_batch_import { sub _perform_cch_tax_import { - my ( $job, $predelete_list, $insert_list, $delete_list ) = @_; + my ( $job, $predelete_list, $insert_list, $delete_list, $addl_param ) = @_; + $addl_param ||= {}; my $error = ''; foreach my $list ($predelete_list, $insert_list, $delete_list) { @@ -1017,7 +1047,11 @@ sub _perform_cch_tax_import { my $fmt = "$format-update"; $fmt = $format. ( lc($name) eq 'zip' ? '-zip' : '' ); open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!"; - $error ||= &{$method}({ 'filehandle' => $fh, 'format' => $fmt }, $job); + my $param = { 'filehandle' => $fh, + 'format' => $fmt, + %$addl_param, + }; + $error ||= &{$method}($param, $job); close $fh; } } @@ -1099,8 +1133,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; @@ -1154,9 +1206,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 $@; @@ -1180,9 +1229,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 ); @@ -1316,7 +1370,7 @@ sub _restore_remembered_tax_products { unless ( $part_pkg_taxproduct ) { return "failed to find part_pkg_taxproduct (". - $taxproduct->{pkgpart}->{$class}. ") for pkgpart $pkgpart\n"; + $taxproduct->{$pkgpart}->{$class}. ") for pkgpart $pkgpart\n"; } if ( $class eq '' ) { @@ -1614,16 +1668,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"; } @@ -1773,7 +1827,7 @@ sub queue_liability_report { $cgi->param('ending', $param->{ending}); my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); my $agentnum = $param->{agentnum}; - $agentnum =~ /^(\d+)$/ ? $agentnum = $1 : $agentnum = ''; + if ($agentnum =~ /^(\d+)$/) { $agentnum = $1; } else { $agentnum = ''; }; generate_liability_report( 'beginning' => $beginning, 'ending' => $ending, @@ -1790,6 +1844,7 @@ agentnum, beginning, and ending =cut +#shit, all sorts of false laxiness w/report_newtax.cgi sub generate_liability_report { my %args = @_; @@ -1826,12 +1881,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'; @@ -1862,13 +1914,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} ) ) { @@ -1878,25 +1930,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(cust_bill_pkg.setup+cust_bill_pkg.recur) ". - " $taxwhere AND cust_bill_pkg.pkgnum = 0"; + 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; @@ -1956,7 +2014,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 @@ -2024,7 +2082,7 @@ EOF <% ($tax->{base}) ? qq!! : '' %> - <% '$money_char' %><% sprintf('%.2f', $tax->{'credit'} ) %> + <% '$money_char' %><% sprintf('%.2f', $tax->{'credit'} ) %> <% !($tax->{base}) ? qq!! : '' %>