'billpkgtaxlocationnum', 'serial', '', '', '', '',
'billpkgnum', 'int', '', '', '', '',
'taxnum', 'int', '', '', '', '',
- 'taxtype', 'varchar', $char_d, '', '', '',
+ 'taxtype', 'varchar', '', $char_d, '', '',
'pkgnum', 'int', '', '', '', '',
'locationnum', 'int', '', '', '', '', #redundant?
'amount', @money_type, '', '',
'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', '', '', '', '',
'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', '', '', '', '',
}
}
+ 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;
'';
--- /dev/null
+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;
+
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;
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;
# 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;
};
}
+ $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
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}++;
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;
'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,
};
}
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 );
}
+=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
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,
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,
--- /dev/null
+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;
+
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
--- /dev/null
+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";
--- /dev/null
+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";
--- /dev/null
+#!/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"; }
<% 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',
)
</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',
}
+ } 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')) {
#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 {
<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';
<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>
% }
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 ";
$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,
};
#--
#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;
projects / freeside.git / commitdiff