diff options
| -rw-r--r-- | FS/FS/Schema.pm | 32 | ||||
| -rw-r--r-- | FS/FS/cust_bill_pkg.pm | 13 | ||||
| -rw-r--r-- | FS/FS/cust_bill_pkg_tax_rate_location.pm | 136 | ||||
| -rw-r--r-- | FS/FS/cust_main.pm | 25 | ||||
| -rw-r--r-- | FS/FS/tax_rate.pm | 27 | ||||
| -rw-r--r-- | FS/FS/tax_rate_location.pm | 309 | ||||
| -rw-r--r-- | FS/MANIFEST | 4 | ||||
| -rw-r--r-- | FS/t/cust_bill_pkg_tax_rate_location.t | 5 | ||||
| -rw-r--r-- | FS/t/tax_rate_location.t | 5 | ||||
| -rwxr-xr-x | bin/tax_rate_location.import | 48 | ||||
| -rw-r--r-- | httemplate/misc/tax-import.cgi | 8 | ||||
| -rw-r--r-- | httemplate/search/cust_bill_pkg.cgi | 24 | ||||
| -rwxr-xr-x | httemplate/search/report_newtax.cgi | 105 |
13 files changed, 695 insertions, 46 deletions
diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index e70a12c1e..a9b546940 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -560,7 +560,7 @@ sub tables_hashref { 'billpkgtaxlocationnum', 'serial', '', '', '', '', 'billpkgnum', 'int', '', '', '', '', 'taxnum', 'int', '', '', '', '', - 'taxtype', 'varchar', $char_d, '', '', '', + 'taxtype', 'varchar', '', $char_d, '', '', 'pkgnum', 'int', '', '', '', '', 'locationnum', 'int', '', '', '', '', #redundant? 'amount', @money_type, '', '', @@ -570,6 +570,21 @@ sub tables_hashref { 'index' => [ [ 'billpkgnum' ], [ 'taxnum' ], [ 'pkgnum' ], [ 'locationnum' ] ], }, + 'cust_bill_pkg_tax_rate_location' => { + 'columns' => [ + 'billpkgtaxratelocationnum', 'serial', '', '', '', '', + 'billpkgnum', 'int', '', '', '', '', + 'taxnum', 'int', '', '', '', '', + 'taxtype', 'varchar', '', $char_d, '', '', + 'locationtaxid', 'varchar', 'NULL', $char_d, '', '', + 'taxratelocationnum', 'int', '', '', '', '', + 'amount', @money_type, '', '', + ], + 'primary_key' => 'billpkgtaxratelocationnum', + 'unique' => [], + 'index' => [ [ 'billpkgnum' ], [ 'taxnum' ], [ 'taxratelocationnum' ] ], + }, + 'cust_credit' => { 'columns' => [ 'crednum', 'serial', '', '', '', '', @@ -805,6 +820,21 @@ sub tables_hashref { 'index' => [ ['taxclassnum'], ['data_vendor', 'geocode'] ], }, + 'tax_rate_location' => { + 'columns' => [ + 'taxratelocationnum', 'serial', '', '', '', '', + 'data_vendor', 'varchar', 'NULL', $char_d, '', '', + 'geocode', 'varchar', '', 20, '', '', + 'city', 'varchar', 'NULL', $char_d, '', '', + 'county', 'varchar', 'NULL', $char_d, '', '', + 'state', 'char', '', 2, '', '', + 'disabled', 'char', 'NULL', 1, '', '', + ], + 'primary_key' => 'taxratelocationnum', + 'unique' => [], + 'index' => [ [ 'data_vendor', 'geocode', 'disabled' ] ], + }, + 'cust_tax_location' => { 'columns' => [ 'custlocationnum', 'serial', '', '', '', '', diff --git a/FS/FS/cust_bill_pkg.pm b/FS/FS/cust_bill_pkg.pm index 6c0589a5c..6bd39d4b9 100644 --- a/FS/FS/cust_bill_pkg.pm +++ b/FS/FS/cust_bill_pkg.pm @@ -162,6 +162,19 @@ sub insert { } } + my $tax_rate_location = $self->get('cust_bill_pkg_tax_rate_location'); + if ( $tax_rate_location ) { + foreach my $cust_bill_pkg_tax_rate_location ( @$tax_rate_location ) { + $cust_bill_pkg_tax_rate_location->billpkgnum($self->billpkgnum); + $error = $cust_bill_pkg_tax_rate_location->insert; + warn $error; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + } + } + $dbh->commit or die $dbh->errstr if $oldAutoCommit; ''; diff --git a/FS/FS/cust_bill_pkg_tax_rate_location.pm b/FS/FS/cust_bill_pkg_tax_rate_location.pm new file mode 100644 index 000000000..fc5734fc1 --- /dev/null +++ b/FS/FS/cust_bill_pkg_tax_rate_location.pm @@ -0,0 +1,136 @@ +package FS::cust_bill_pkg_tax_rate_location; + +use strict; +use base qw( FS::Record ); +use FS::Record qw( qsearch qsearchs ); +use FS::cust_bill_pkg; +use FS::cust_pkg; +use FS::cust_location; + +=head1 NAME + +FS::cust_bill_pkg_tax_rate_location - Object methods for cust_bill_pkg_tax_rate_location records + +=head1 SYNOPSIS + + use FS::cust_bill_pkg_tax_rate_location; + + $record = new FS::cust_bill_pkg_tax_rate_location \%hash; + $record = new FS::cust_bill_pkg_tax_rate_location { 'column' => 'value' }; + + $error = $record->insert; + + $error = $new_record->replace($old_record); + + $error = $record->delete; + + $error = $record->check; + +=head1 DESCRIPTION + +An FS::cust_bill_pkg_tax_rate_location object represents an record of taxation +based on package location. FS::cust_bill_pkg_tax_rate_location inherits from +FS::Record. The following fields are currently supported: + +=over 4 + +=item billpkgtaxratelocationnum + +billpkgtaxratelocationnum + +=item billpkgnum + +billpkgnum + +=item taxnum + +taxnum + +=item taxtype + +taxtype + +=item locationtaxid + +locationtaxid + +=item taxratelocationnum + +taxratelocationnum + +=item amount + +amount + + +=back + +=head1 METHODS + +=over 4 + +=item new HASHREF + +Creates a new record. To add the record to the database, see L<"insert">. + +Note that this stores the hash reference, not a distinct copy of the hash it +points to. You can ask the object for a copy with the I<hash> method. + +=cut + +sub table { 'cust_bill_pkg_tax_rate_location'; } + +=item insert + +Adds this record to the database. If there is an error, returns the error, +otherwise returns false. + +=item delete + +Delete this record from the database. + +=item replace OLD_RECORD + +Replaces the OLD_RECORD with this one in the database. If there is an error, +returns the error, otherwise returns false. + +=item check + +Checks all fields to make sure this is a valid record. If there is +an error, returns the error, otherwise returns false. Called by the insert +and replace methods. + +=cut + +# the check method should currently be supplied - FS::Record contains some +# data checking routines + +sub check { + my $self = shift; + + my $error = + $self->ut_numbern('billpkgtaxratelocationnum') + || $self->ut_foreign_key('billpkgnum', 'cust_bill_pkg', 'billpkgnum' ) + || $self->ut_number('taxnum') #cust_bill_pkg/tax_rate key, based on taxtype + || $self->ut_enum('taxtype', [ qw( FS::cust_main_county FS::tax_rate ) ] ) + || $self->ut_textn('locationtaxid') + || $self->ut_foreign_key('taxratelocationnum', 'tax_rate_location', 'taxratelocationnum' ) + || $self->ut_money('amount') + ; + return $error if $error; + + $self->SUPER::check; +} + +=back + +=head1 BUGS + +=head1 SEE ALSO + +L<FS::Record>, schema.html from the base documentation. + +=cut + +1; + diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index 6f05fd5aa..a1bb926b3 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -30,6 +30,7 @@ use FS::cust_bill; use FS::cust_bill_pkg; use FS::cust_bill_pkg_display; use FS::cust_bill_pkg_tax_location; +use FS::cust_bill_pkg_tax_rate_location; use FS::cust_pay; use FS::cust_pay_pending; use FS::cust_pay_void; @@ -40,6 +41,7 @@ use FS::part_referral; use FS::cust_main_county; use FS::cust_location; use FS::tax_rate; +use FS::tax_rate_location; use FS::cust_tax_location; use FS::part_pkg_taxrate; use FS::agent; @@ -2461,6 +2463,10 @@ sub bill { # values are listrefs of cust_bill_pkg_tax_location hashrefs my %tax_location = (); + # keys are taxlisthash keys (internal identifiers) + # values are listrefs of cust_bill_pkg_tax_rate_location hashrefs + my %tax_rate_location = (); + foreach my $tax ( keys %taxlisthash ) { my $tax_object = shift @{ $taxlisthash{$tax} }; warn "found ". $tax_object->taxname. " as $tax\n" if $DEBUG > 2; @@ -2497,6 +2503,20 @@ sub bill { }; } + $tax_rate_location{ $tax } ||= []; + if ( ref($tax_object) eq 'FS::tax_rate' ) { + my $taxratelocationnum = + $tax_object->tax_rate_location->taxratelocationnum; + push @{ $tax_rate_location{ $tax } }, + { + 'taxnum' => $tax_object->taxnum, + 'taxtype' => ref($tax_object), + 'amount' => sprintf('%.2f', $amount ), + 'locationtaxid' => $tax_object->location, + 'taxratelocationnum' => $taxratelocationnum, + }; + } + } #move the cust_tax_exempt_pkg records to the cust_bill_pkgs we will commit @@ -2516,6 +2536,7 @@ sub bill { my $tax = 0; my %seen = (); my @cust_bill_pkg_tax_location = (); + my @cust_bill_pkg_tax_rate_location = (); warn "adding $taxname\n" if $DEBUG > 1; foreach my $taxitem ( @{ $taxname{$taxname} } ) { next if $seen{$taxitem}++; @@ -2524,6 +2545,9 @@ sub bill { push @cust_bill_pkg_tax_location, map { new FS::cust_bill_pkg_tax_location $_ } @{ $tax_location{ $taxitem } }; + push @cust_bill_pkg_tax_rate_location, + map { new FS::cust_bill_pkg_tax_rate_location $_ } + @{ $tax_rate_location{ $taxitem } }; } next unless $tax; @@ -2538,6 +2562,7 @@ sub bill { 'edate' => '', 'itemdesc' => $taxname, 'cust_bill_pkg_tax_location' => \@cust_bill_pkg_tax_location, + 'cust_bill_pkg_tax_rate_location' => \@cust_bill_pkg_tax_rate_location, }; } diff --git a/FS/FS/tax_rate.pm b/FS/FS/tax_rate.pm index dfa7d5f44..80a0f4b11 100644 --- a/FS/FS/tax_rate.pm +++ b/FS/FS/tax_rate.pm @@ -19,6 +19,7 @@ use FS::Record qw( qsearch qsearchs dbh dbdef ); use FS::tax_class; use FS::cust_bill_pkg; use FS::cust_tax_location; +use FS::tax_rate_location; use FS::part_pkg_taxrate; use FS::cust_main; use FS::Misc qw( csv_from_fixed ); @@ -538,6 +539,26 @@ sub tax_on_tax { } +=item tax_rate_location + +Returns an object representing the location associated with this tax +(see L<FS::tax_rate_location>) + +=cut + +sub tax_rate_location { + my $self = shift; + + qsearchs({ 'table' => 'tax_rate_location', + 'hashref' => { 'data_vendor' => $self->data_vendor, + 'geocode' => $self->geocode, + 'disabled' => '', + }, + }) || + new FS::tax_rate_location; + +} + =back =head1 SUBROUTINES @@ -845,7 +866,8 @@ sub process_batch_import { my $error = ''; my $have_location = 0; - my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import, + 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, @@ -887,7 +909,8 @@ sub process_batch_import { my @insert_list = (); my @delete_list = (); - my @list = ( 'CODE', 'codefile', \&FS::tax_class::batch_import, + 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, diff --git a/FS/FS/tax_rate_location.pm b/FS/FS/tax_rate_location.pm new file mode 100644 index 000000000..65bef7bf7 --- /dev/null +++ b/FS/FS/tax_rate_location.pm @@ -0,0 +1,309 @@ +package FS::tax_rate_location; + +use strict; +use base qw( FS::Record ); +use FS::Record qw( qsearch qsearchs dbh ); + +=head1 NAME + +FS::tax_rate_location - Object methods for tax_rate_location records + +=head1 SYNOPSIS + + use FS::tax_rate_location; + + $record = new FS::tax_rate_location \%hash; + $record = new FS::tax_rate_location { 'column' => 'value' }; + + $error = $record->insert; + + $error = $new_record->replace($old_record); + + $error = $record->delete; + + $error = $record->check; + +=head1 DESCRIPTION + +An FS::tax_rate_location object represents an example. FS::tax_rate_location inherits from +FS::Record. The following fields are currently supported: + +=over 4 + +=item taxratelocationnum + +Primary key (assigned automatically for new tax_rate_locations) + +=item data_vendor + +The tax data vendor + +=item geocode + +A unique geographic location code provided by the data vendor + +=item city + +City + +=item county + +County + +=item state + +State + +=item disabled + +If 'Y' this record is no longer active. + + +=back + +=head1 METHODS + +=over 4 + +=item new HASHREF + +Creates a new tax rate location. To add the record to the database, see + L<"insert">. + +Note that this stores the hash reference, not a distinct copy of the hash it +points to. You can ask the object for a copy with the I<hash> method. + +=cut + +sub table { 'tax_rate_location'; } + +=item insert + +Adds this record to the database. If there is an error, returns the error, +otherwise returns false. + +=cut + +=item delete + +Delete this record from the database. + +=cut + +sub delete { + return "Can't delete tax rate locations. Set disable to 'Y' instead."; + # check that it is unused in any cust_bill_pkg_tax_location records instead? +} + +=item replace OLD_RECORD + +Replaces the OLD_RECORD with this one in the database. If there is an error, +returns the error, otherwise returns false. + +=cut + +=item check + +Checks all fields to make sure this is a valid tax rate location. If there is +an error, returns the error, otherwise returns false. Called by the insert +and replace methods. + +=cut + +sub check { + my $self = shift; + + my $error = + $self->ut_numbern('taxratelocationnum') + || $self->ut_textn('data_vendor') + || $self->ut_alpha('geocode') + || $self->ut_textn('city') + || $self->ut_textn('county') + || $self->ut_textn('state') + || $self->ut_enum('disabled', [ '', 'Y' ]) + ; + return $error if $error; + + my $t = qsearchs( 'tax_rate_location', + { map { $_ => $self->$_ } qw( data_vendor geocode ) }, + ); + + return "geocode already in use for this vendor" + if ( $t && $t->taxratelocationnum != $self->taxratelocationnum ); + + return "may only be disabled" + if ( $t && scalar( grep { $t->$_ ne $self->$_ } + grep { $_ ne 'disabled' } + $self->fields + ) + ); + + $self->SUPER::check; +} + +=back + +=head1 SUBROUTINES + +=over 4 + +=item batch_import + +=cut + +sub batch_import { + my ($param, $job) = @_; + + my $fh = $param->{filehandle}; + my $format = $param->{'format'}; + + my %insert = (); + my %delete = (); + + my @fields; + my $hook; + + my @column_lengths = (); + my @column_callbacks = (); + if ( $format eq 'cch-fixed' || $format eq 'cch-fixed-update' ) { + $format =~ s/-fixed//; + my $trim = sub { my $r = shift; $r =~ s/^\s*//; $r =~ s/\s*$//; $r }; + push @column_lengths, qw( 28 25 2 10 ); + push @column_lengths, 1 if $format eq 'cch-update'; + push @column_callbacks, $trim foreach (@column_lengths); + } + + my $line; + my ( $count, $last, $min_sec ) = (0, time, 5); #progressbar + if ( $job || scalar(@column_callbacks) ) { + my $error = + csv_from_fixed(\$fh, \$count, \@column_lengths, \@column_callbacks); + return $error if $error; + } + + if ( $format eq 'cch' || $format eq 'cch-update' ) { + @fields = qw( city county state geocode ); + push @fields, 'actionflag' if $format eq 'cch-update'; + + $hook = sub { + my $hash = shift; + + $hash->{'data_vendor'} ='cch'; + + if (exists($hash->{'actionflag'}) && $hash->{'actionflag'} eq 'D') { + delete($hash->{actionflag}); + + $hash->{deleted} = ''; + my $tax_rate_location = qsearchs('tax_rate_location', $hash); + return "Can't find tax_rate_location to delete: ". + join(" ", map { "$_ => ". $hash->{$_} } @fields) + unless $tax_rate_location; + + $tax_rate_location->disabled('Y'); + my $error = $tax_rate_location->replace; + return $error if $error; + + delete($hash->{$_}) foreach (keys %$hash); + } + + delete($hash->{'actionflag'}); + + ''; + + }; + + } elsif ( $format eq 'extended' ) { + die "unimplemented\n"; + @fields = qw( ); + $hook = sub {}; + } else { + die "unknown format $format"; + } + + eval "use Text::CSV_XS;"; + die $@ if $@; + + my $csv = new Text::CSV_XS; + + my $imported = 0; + + local $SIG{HUP} = 'IGNORE'; + local $SIG{INT} = 'IGNORE'; + local $SIG{QUIT} = 'IGNORE'; + local $SIG{TERM} = 'IGNORE'; + local $SIG{TSTP} = 'IGNORE'; + local $SIG{PIPE} = 'IGNORE'; + + my $oldAutoCommit = $FS::UID::AutoCommit; + local $FS::UID::AutoCommit = 0; + my $dbh = dbh; + + while ( defined($line=<$fh>) ) { + $csv->parse($line) or do { + $dbh->rollback if $oldAutoCommit; + return "can't parse: ". $csv->error_input(); + }; + + if ( $job ) { # progress bar + if ( time - $min_sec > $last ) { + my $error = $job->update_statustext( + int( 100 * $imported / $count ) + ); + die $error if $error; + $last = time; + } + } + + my @columns = $csv->fields(); + + my %tax_rate_location = (); + foreach my $field ( @fields ) { + $tax_rate_location{$field} = shift @columns; + } + if ( scalar( @columns ) ) { + $dbh->rollback if $oldAutoCommit; + return "Unexpected trailing columns in line (wrong format?): $line"; + } + + my $error = &{$hook}(\%tax_rate_location); + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } + + if (scalar(keys %tax_rate_location)) { #inserts only + + my $tax_rate_location = new FS::tax_rate_location( \%tax_rate_location ); + $error = $tax_rate_location->insert; + + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "can't insert tax_rate for $line: $error"; + } + + } + + $imported++; + + } + + $dbh->commit or die $dbh->errstr if $oldAutoCommit; + + return "Empty file!" unless ($imported || $format eq 'cch-update'); + + ''; #no error + +} + +=head1 BUGS + +Currently somewhat specific to CCH supplied data. + +=head1 SEE ALSO + +L<FS::Record>, schema.html from the base documentation. + +=cut + +1; + diff --git a/FS/MANIFEST b/FS/MANIFEST index 4b9fd91b5..b5c9046a6 100644 --- a/FS/MANIFEST +++ b/FS/MANIFEST @@ -434,3 +434,7 @@ FS/cust_location.pm t/cust_location.t FS/cust_bill_pkg_tax_location.pm t/cust_bill_pkg_tax_location.t +FS/tax_rate_location.pm +t/tax_rate_location.t +FS/cust_bill_pkg_tax_rate_location.pm +t/cust_bill_pkg_tax_rate_location.t diff --git a/FS/t/cust_bill_pkg_tax_rate_location.t b/FS/t/cust_bill_pkg_tax_rate_location.t new file mode 100644 index 000000000..3250db9b5 --- /dev/null +++ b/FS/t/cust_bill_pkg_tax_rate_location.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::cust_bill_pkg_tax_rate_location; +$loaded=1; +print "ok 1\n"; diff --git a/FS/t/tax_rate_location.t b/FS/t/tax_rate_location.t new file mode 100644 index 000000000..f4ee910a0 --- /dev/null +++ b/FS/t/tax_rate_location.t @@ -0,0 +1,5 @@ +BEGIN { $| = 1; print "1..1\n" } +END {print "not ok 1\n" unless $loaded;} +use FS::tax_rate_location; +$loaded=1; +print "ok 1\n"; diff --git a/bin/tax_rate_location.import b/bin/tax_rate_location.import new file mode 100755 index 000000000..439d27cc9 --- /dev/null +++ b/bin/tax_rate_location.import @@ -0,0 +1,48 @@ +#!/usr/bin/perl -Tw + +use strict; +use vars qw($opt_g $opt_f); +use vars qw($DEBUG); +use Getopt::Std; +use FS::UID qw(adminsuidsetup); +use FS::Conf; +use FS::tax_rate_location; + +getopts('f:g:'); + +my $user = shift or die &usage; +my $dbh = adminsuidsetup $user; + +my ($format) = $opt_f =~ /^([-\w]+)$/; + +my @list = ( + 'GEOCODE', $opt_g, \&FS::tax_rate_location::batch_import, +); + +my $oldAutoCommit = $FS::UID::AutoCommit; +local $FS::UID::AutoCommit = 0; + +my $error = ''; + +while(@list) { + my ($name, $file, $method) = splice(@list, 0, 3); + + my $fh; + + $file =~ /^([\s\d\w.]+)$/ or die "Illegal filename: $file\n"; + $file = $1; + + open $fh, '<', $file or die "can't open $name file: $!\n"; + $error ||= &{$method}( { filehandle => $fh, 'format' => $format, } ); + + die "error while processing $file: $error" if $error; + close $fh; +} + +if ($error) { + $dbh->rollback or die $dbh->errstr if $oldAutoCommit; +}else{ + $dbh->commit or die $dbh->errstr if $oldAutoCommit; +} + +sub usage { die "Usage:\ntax_rates_location.import -f FORMAT -g GEOCODEFILE user\n\n"; } diff --git a/httemplate/misc/tax-import.cgi b/httemplate/misc/tax-import.cgi index a695e9706..5116e5404 100644 --- a/httemplate/misc/tax-import.cgi +++ b/httemplate/misc/tax-import.cgi @@ -6,7 +6,7 @@ Import a CSV file set containing tax rate records. <% include( '/elements/form-file_upload.html', 'name' => 'TaxRateUpload', 'action' => 'process/tax-import.cgi', - 'num_files' => 5, + 'num_files' => 6, 'fields' => [ 'format', ], 'message' => 'Tax rates imported', ) @@ -27,13 +27,15 @@ Import a CSV file set containing tax rate records. </TR> <% include( '/elements/file-upload.html', - 'field' => [ 'codefile', + 'field' => [ 'geofile', + 'codefile', 'plus4file', 'zipfile', 'txmatrix', 'detail', ], - 'label' => [ 'code filename', + 'label' => [ 'geocode filename', + 'code filename', 'plus4 filename', 'zip filename', 'txmatrix filename', diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 89901ac40..9d4843281 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -189,6 +189,26 @@ if ( $cgi->param('out') ) { } + } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) { + + # this should really be shoved out to FS::cust_pkg->location_sql or something + # along with the code in report_newtax.cgi + + my %pn = ( + 'county' => 'tax_rate_location.county', + 'state' => 'tax_rate_location.state', + 'city' => 'tax_rate_location.city', + 'locationtaxid' => 'cust_bill_pkg_tax_rate_location.locationtaxid', + ); + + my %ph = map { ( $pn{$_} => dbh->quote( $cgi->param($_) || '' ) ) } + qw( county state city locationtaxid ); + + push @where, + join( ' AND ', map { "( $_ = $ph{$_} OR $ph{$_} = '' AND $_ IS NULL)" } + keys %ph + ); + } if ($cgi->param('itemdesc')) { @@ -295,6 +315,10 @@ if ( $cgi->param('nottax') ) { #quelle kludge, false laziness w/report_tax.cgi $where =~ s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g; + } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) { + $join_pkg .= + ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '. + ' LEFT JOIN tax_rate_location USING ( taxratelocationnum ) '; } } else { diff --git a/httemplate/search/report_newtax.cgi b/httemplate/search/report_newtax.cgi index 586fddd25..0fb548352 100755 --- a/httemplate/search/report_newtax.cgi +++ b/httemplate/search/report_newtax.cgi @@ -15,6 +15,7 @@ <TR> <TH CLASS="grid" BGCOLOR="#cccccc"></TH> + <TH CLASS="grid" BGCOLOR="#cccccc"></TH> <TH CLASS="grid" BGCOLOR="#cccccc">Tax collected</TH> </TR> % my $bgcolor1 = '#eeeeee'; @@ -37,9 +38,11 @@ <TR> <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $tax->{'label'} %></TD> + <% $tax->{base} ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %> <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right"> <A HREF="<% $baselink. $link %>;istax=1"><% $money_char %><% sprintf('%.2f', $tax->{'tax'} ) %></A> </TD> + <% !($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %> </TR> % } @@ -61,10 +64,11 @@ my $join_cust = " JOIN cust_bill USING ( invnum ) LEFT JOIN cust_main USING ( custnum ) "; -my $from_join_cust = " - FROM cust_bill_pkg - $join_cust -"; + +my $join_loc = "LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum )"; +my $join_tax_loc = "LEFT JOIN tax_rate_location USING ( taxratelocationnum )"; + +my $addl_from = " $join_cust $join_loc $join_tax_loc "; my $where = "WHERE _date >= $beginning AND _date <= $ending "; @@ -76,65 +80,87 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { $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 $select = 'DISTINCT itemdesc,locationtaxid,tax_rate_location.state,tax_rate_location.county,tax_rate_location.city'; + my $tax = 0; my %taxes = (); +my %basetaxes = (); foreach my $t (qsearch({ table => 'cust_bill_pkg', + select => $select, hashref => { pkgpart => 0 }, - addl_from => $join_cust, + addl_from => $addl_from, extra_sql => $where, }) ) { - #warn $t->itemdesc. "\n"; + my @params = map { my $f = $_; $f =~ s/.*\.//; $f } @taxparam; + my $label = join('~', map { $t->$_ } @params); + $label = 'Tax'. $label if $label =~ /^~/; + unless ( exists( $taxes{$label} ) ) { + my ($baselabel, @trash) = split /~/, $label; - my $label = $t->itemdesc; - $label ||= 'Tax'; - $taxes{$label}->{'label'} = $label; - $taxes{$label}->{'url_param'} = "itemdesc=$label"; + $taxes{$label}->{'label'} = join(', ', split(/~/, $label) ); + $taxes{$label}->{'url_param'} = + join(';', map { "$_=". uri_escape($t->$_) } @params); - # calculate total for this tax - # calculate customer-exemption for this tax - # calculate package-exemption for this tax - # calculate monthly exemption (texas tax) for this tax - # count up all the cust_tax_exempt_pkg records associated with - # the actual line items. -} + my $taxwhere = "FROM cust_bill_pkg $addl_from $where AND payby != 'COMP' ". + "AND ". join( ' AND ', map { "( $_ = ? OR ? = '' AND $_ IS NULL)" } @taxparam ); + my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) ". + " $taxwhere AND cust_bill_pkg.pkgnum = 0"; -foreach my $t (qsearch({ table => 'cust_bill_pkg', - select => 'DISTINCT itemdesc', - hashref => { pkgpart => 0 }, - addl_from => $join_cust, - extra_sql => $where, - }) - ) -{ + my $x = scalar_sql($t, [ map { $_, $_ } @params ], $sql ); + $tax += $x; + $taxes{$label}->{'tax'} += $x; + + unless ( exists( $taxes{$baselabel} ) ) { - my $label = $t->itemdesc; - $label ||= 'Tax'; - my @taxparam = ( 'itemdesc' ); - my $taxwhere = "$from_join_cust $where AND payby != 'COMP' ". - "AND itemdesc = ?" ; + $basetaxes{$baselabel}->{'label'} = $baselabel; + $basetaxes{$baselabel}->{'url_param'} = "itemdesc=$baselabel"; + $basetaxes{$baselabel}->{'base'} = 1; - my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) ". - " $taxwhere AND pkgnum = 0"; + } - my $x = scalar_sql($t, \@taxparam, $sql ); - $tax += $x; - $taxes{$label}->{'tax'} += $x; + $basetaxes{$baselabel}->{'tax'} += $x; + + } + # calculate customer-exemption for this tax + # calculate package-exemption for this tax + # calculate monthly exemption (texas tax) for this tax + # count up all the cust_tax_exempt_pkg records associated with + # the actual line items. } + #ordering -my @taxes = - map $taxes{$_}, - sort { ($b cmp $a) } - keys %taxes; +my @taxes = (); + +foreach my $tax ( sort { $a cmp $b } keys %taxes ) { + my ($base, @trash) = split '~', $tax; + my $basetax = delete( $basetaxes{$base} ); + if ($basetax) { + if ( $basetax->{tax} == $taxes{$tax}->{tax} ) { + $taxes{$tax}->{base} = 1; + } else { + push @taxes, $basetax; + } + } + push @taxes, $taxes{$tax}; +} push @taxes, { 'label' => 'Total', 'url_param' => '', 'tax' => $tax, + 'base' => 1, }; #-- @@ -143,7 +169,6 @@ push @taxes, { #to FS::Report or FS::Record or who the fuck knows where) sub scalar_sql { my( $r, $param, $sql ) = @_; - #warn "$sql\n"; my $sth = dbh->prepare($sql) or die dbh->errstr; $sth->execute( map $r->$_(), @$param ) or die "Unexpected error executing statement $sql: ". $sth->errstr; |