'cust_bill_pkg' => {
'columns' => [
- 'billpkgnum', 'serial', '', '', '', '',
- 'pkgnum', 'int', '', '', '', '',
- 'pkgpart_override', 'int', 'NULL', '', '', '',
- 'invnum', 'int', '', '', '', '',
- 'setup', @money_type, '', '',
- 'recur', @money_type, '', '',
- 'sdate', @date_type, '', '',
- 'edate', @date_type, '', '',
- 'itemdesc', 'varchar', 'NULL', $char_d, '', '',
- 'section', 'varchar', 'NULL', $char_d, '', '',
- 'quantity', 'int', 'NULL', '', '', '',
- 'unitsetup', @money_typen, '', '',
- 'unitrecur', @money_typen, '', '',
+ 'billpkgnum', 'serial', '', '', '', '',
+ 'invnum', 'int', '', '', '', '',
+ 'pkgnum', 'int', '', '', '', '',
+ 'pkgpart_override', 'int', 'NULL', '', '', '',
+ 'setup', @money_type, '', '',
+ 'recur', @money_type, '', '',
+ 'sdate', @date_type, '', '',
+ 'edate', @date_type, '', '',
+ 'itemdesc', 'varchar', 'NULL', $char_d, '', '',
+ 'section', 'varchar', 'NULL', $char_d, '', '',
+ 'quantity', 'int', 'NULL', '', '', '',
+ 'unitsetup', @money_typen, '', '',
+ 'unitrecur', @money_typen, '', '',
],
'primary_key' => 'billpkgnum',
'unique' => [],
'index' => [ ['billpkgnum'], ],
},
+ 'cust_bill_pkg_tax_location' => {
+ 'columns' => [
+ 'billpkgtaxlocationnum', 'serial', '', '', '', '',
+ 'billpkgnum', 'int', '', '', '', '',
+ 'taxnum', 'int', '', '', '', '',
+ 'taxtype', 'varchar', $char_d, '', '', '',
+ 'pkgnum', 'int', '', '', '', '',
+ 'locationnum', 'int', '', '', '', '', #redundant?
+ 'amount', @money_type, '', '',
+ ],
+ 'primary_key' => 'billpkgtaxlocationnum',
+ 'unique' => [],
+ 'index' => [ [ 'billpkgnum' ], [ 'taxnum' ], [ 'pkgnum' ], [ 'locationnum' ] ],
+ },
+
'cust_credit' => {
'columns' => [
'crednum', 'serial', '', '', '', '',
'primary_key' => 'taxnum',
'unique' => [],
# 'unique' => [ ['taxnum'], ['state', 'county'] ],
- 'index' => [ [ 'county' ], [ 'state' ], [ 'country' ] ],
+ 'index' => [ [ 'county' ], [ 'state' ], [ 'country' ],
+ [ 'taxclass' ],
+ ],
},
'tax_rate' => {
}
}
+ my $tax_location = $self->get('cust_bill_pkg_tax_location');
+ if ( $tax_location ) {
+ foreach my $cust_bill_pkg_tax_location ( @$tax_location ) {
+ $cust_bill_pkg_tax_location->billpkgnum($self->billpkgnum);
+ warn $cust_bill_pkg_tax_location;
+ $error = $cust_bill_pkg_tax_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_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_location - Object methods for cust_bill_pkg_tax_location records
+
+=head1 SYNOPSIS
+
+ use FS::cust_bill_pkg_tax_location;
+
+ $record = new FS::cust_bill_pkg_tax_location \%hash;
+ $record = new FS::cust_bill_pkg_tax_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_location object represents an record of taxation
+based on package location. FS::cust_bill_pkg_tax_location inherits from
+FS::Record. The following fields are currently supported:
+
+=over 4
+
+=item billpkgtaxlocationnum
+
+billpkgtaxlocationnum
+
+=item billpkgnum
+
+billpkgnum
+
+=item taxnum
+
+taxnum
+
+=item taxtype
+
+taxtype
+
+=item pkgnum
+
+pkgnum
+
+=item locationnum
+
+locationnum
+
+=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_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('billpkgtaxlocationnum')
+ || $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_foreign_key('pkgnum', 'cust_pkg', 'pkgnum' )
+ || $self->ut_foreign_key('locationnum', 'cust_location', 'locationnum' )
+ || $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;
use FS::cust_bill_pkg;
use FS::cust_bill_pkg_display;
+use FS::cust_bill_pkg_tax_location;
use FS::cust_pay;
use FS::cust_pay_pending;
use FS::cust_pay_void;
###
my( $total_setup, $total_recur, $postal_charge ) = ( 0, 0, 0 );
- my %tax;
my %taxlisthash;
- my %taxname;
my @precommit_hooks = ();
my @cust_pkgs = qsearch('cust_pkg', { 'custnum' => $self->custnum } );
}
warn "having a look at the taxes we found...\n" if $DEBUG > 2;
+
+ # keys are tax names (as printed on invoices / itemdesc )
+ # values are listrefs of taxlisthash keys (internal identifiers)
+ my %taxname = ();
+
+ # keys are taxlisthash keys (internal identifiers)
+ # values are (cumulative) amounts
+ my %tax = ();
+
+ # keys are taxlisthash keys (internal identifiers)
+ # values are listrefs of cust_bill_pkg_tax_location hashrefs
+ my %tax_location = ();
+
foreach my $tax ( keys %taxlisthash ) {
my $tax_object = shift @{ $taxlisthash{$tax} };
warn "found ". $tax_object->taxname. " as $tax\n" if $DEBUG > 2;
- my $listref_or_error =
+ my $hashref_or_error =
$tax_object->taxline( $taxlisthash{$tax},
'custnum' => $self->custnum,
'invoice_time' => $invoice_time
);
- unless (ref($listref_or_error)) {
+ unless ( ref($hashref_or_error) ) {
$dbh->rollback if $oldAutoCommit;
- return $listref_or_error;
+ return $hashref_or_error;
}
unshift @{ $taxlisthash{$tax} }, $tax_object;
- warn "adding ". $listref_or_error->[1].
- " as ". $listref_or_error->[0]. "\n"
- if $DEBUG > 2;
- $tax{ $tax } += $listref_or_error->[1];
- if ( $taxname{ $listref_or_error->[0] } ) {
- push @{ $taxname{ $listref_or_error->[0] } }, $tax;
- }else{
- $taxname{ $listref_or_error->[0] } = [ $tax ];
+ my $name = $hashref_or_error->{'name'};
+ my $amount = $hashref_or_error->{'amount'};
+
+ #warn "adding $amount as $name\n";
+ $taxname{ $name } ||= [];
+ push @{ $taxname{ $name } }, $tax;
+
+ $tax{ $tax } += $amount;
+
+ $tax_location{ $tax } ||= [];
+ if ( $tax_object->get('pkgnum') || $tax_object->get('locationnum') ) {
+ push @{ $tax_location{ $tax } },
+ {
+ 'taxnum' => $tax_object->taxnum,
+ 'taxtype' => ref($tax_object),
+ 'pkgnum' => $tax_object->get('pkgnum'),
+ 'locationnum' => $tax_object->get('locationnum'),
+ 'amount' => sprintf('%.2f', $amount ),
+ };
}
-
+
}
#move the cust_tax_exempt_pkg records to the cust_bill_pkgs we will commit
foreach my $taxname ( keys %taxname ) {
my $tax = 0;
my %seen = ();
+ my @cust_bill_pkg_tax_location = ();
warn "adding $taxname\n" if $DEBUG > 1;
foreach my $taxitem ( @{ $taxname{$taxname} } ) {
- $tax += $tax{$taxitem} unless $seen{$taxitem};
- $seen{$taxitem} = 1;
+ next if $seen{$taxitem}++;
warn "adding $tax{$taxitem}\n" if $DEBUG > 1;
+ $tax += $tax{$taxitem};
+ push @cust_bill_pkg_tax_location,
+ map { new FS::cust_bill_pkg_tax_location $_ }
+ @{ $tax_location{ $taxitem } };
}
next unless $tax;
'sdate' => '',
'edate' => '',
'itemdesc' => $taxname,
+ 'cust_bill_pkg_tax_location' => \@cust_bill_pkg_tax_location,
};
}
my %cust_bill_pkg = ();
my %taxes = ();
- my $prefix =
- ( $conf->exists('tax-ship_address') && length($self->ship_last) )
- ? 'ship_'
- : '';
-
my @classes;
#push @classes, $cust_bill_pkg->usage_classes if $cust_bill_pkg->type eq 'U';
push @classes, $cust_bill_pkg->usage_classes if $cust_bill_pkg->usage;
push @classes, 'setup' if $cust_bill_pkg->setup;
push @classes, 'recur' if $cust_bill_pkg->recur;
- if ( $conf->exists('enable_taxproducts')
- && (scalar($part_pkg->part_pkg_taxoverride) || $part_pkg->has_taxproduct)
- && ( $self->tax !~ /Y/i && $self->payby ne 'COMP' )
- )
- {
+ if ( $self->tax !~ /Y/i && $self->payby ne 'COMP' ) {
- foreach my $class (@classes) {
- my $err_or_ref = $self->_gather_taxes( $part_pkg, $class, $prefix );
- return $err_or_ref unless ref($err_or_ref);
- $taxes{$class} = $err_or_ref;
- }
+ if ( $conf->exists('enable_taxproducts')
+ && ( scalar($part_pkg->part_pkg_taxoverride)
+ || $part_pkg->has_taxproduct
+ )
+ )
+ {
- unless (exists $taxes{''}) {
- my $err_or_ref = $self->_gather_taxes( $part_pkg, '', $prefix );
- return $err_or_ref unless ref($err_or_ref);
- $taxes{''} = $err_or_ref;
- }
+ if ( $conf->exists('tax-pkg_address') && $cust_pkg->locationnum ) {
+ return "fatal: Can't (yet) use tax-pkg_address with taxproducts";
+ }
- } elsif ( $self->tax !~ /Y/i && $self->payby ne 'COMP' ) {
+ foreach my $class (@classes) {
+ my $err_or_ref = $self->_gather_taxes( $part_pkg, $class );
+ return $err_or_ref unless ref($err_or_ref);
+ $taxes{$class} = $err_or_ref;
+ }
- my %taxhash = map { $_ => $self->get("$prefix$_") }
- qw( state county country );
+ unless (exists $taxes{''}) {
+ my $err_or_ref = $self->_gather_taxes( $part_pkg, '' );
+ return $err_or_ref unless ref($err_or_ref);
+ $taxes{''} = $err_or_ref;
+ }
- $taxhash{'taxclass'} = $part_pkg->taxclass;
+ } else {
- my @taxes = qsearch( 'cust_main_county', \%taxhash );
+ my @loc_keys = qw( state county country );
+ my %taxhash;
+ if ( $conf->exists('tax-pkg_address') && $cust_pkg->locationnum ) {
+ my $cust_location = $cust_pkg->cust_location;
+ %taxhash = map { $_ => $cust_location->$_() } @loc_keys;
+ } else {
+ my $prefix =
+ ( $conf->exists('tax-ship_address') && length($self->ship_last) )
+ ? 'ship_'
+ : '';
+ %taxhash = map { $_ => $self->get("$prefix$_") } @loc_keys;
+ }
- unless ( @taxes ) {
- $taxhash{'taxclass'} = '';
- @taxes = qsearch( 'cust_main_county', \%taxhash );
- }
+ $taxhash{'taxclass'} = $part_pkg->taxclass;
- #one more try at a whole-country tax rate
- unless ( @taxes ) {
- $taxhash{$_} = '' foreach qw( state county );
- @taxes = qsearch( 'cust_main_county', \%taxhash );
- }
+ my @taxes = qsearch( 'cust_main_county', \%taxhash );
- $taxes{''} = [ @taxes ];
- $taxes{'setup'} = [ @taxes ];
- $taxes{'recur'} = [ @taxes ];
- $taxes{$_} = [ @taxes ] foreach (@classes);
-
- # maybe eliminate this entirely, along with all the 0% records
- unless ( @taxes ) {
- return
- "fatal: can't find tax rate for state/county/country/taxclass ".
- join('/', ( map $self->get("$prefix$_"),
- qw(state county country)
- ),
- $part_pkg->taxclass ). "\n";
- }
+ unless ( @taxes ) {
+ $taxhash{'taxclass'} = '';
+ @taxes = qsearch( 'cust_main_county', \%taxhash );
+ }
+
+ #one more try at a whole-country tax rate
+ unless ( @taxes ) {
+ $taxhash{$_} = '' foreach qw( state county );
+ @taxes = qsearch( 'cust_main_county', \%taxhash );
+ }
+
+ if ( $conf->exists('tax-pkg_address') && $cust_pkg->locationnum ) {
+ foreach (@taxes) {
+ $_->set('pkgnum', $cust_pkg->pkgnum );
+ $_->set('locationnum', $cust_pkg->locationnum );
+ }
+ }
+
+ $taxes{''} = [ @taxes ];
+ $taxes{'setup'} = [ @taxes ];
+ $taxes{'recur'} = [ @taxes ];
+ $taxes{$_} = [ @taxes ] foreach (@classes);
+
+ # maybe eliminate this entirely, along with all the 0% records
+ unless ( @taxes ) {
+ return
+ "fatal: can't find tax rate for state/county/country/taxclass ".
+ join('/', map $taxhash{$_}, qw(state county country taxclass) );
+ }
+
+ } #if $conf->exists('enable_taxproducts') ...
- } #if $conf->exists('enable_taxproducts') ...
+ }
my @display = ();
if ( $conf->exists('separate_usage') ) {
my $tax_cust_bill_pkg = $tax_cust_bill_pkg{$key};
foreach my $tax ( @taxes ) {
- my $taxname = ref( $tax ). ' '. $tax->taxnum;
+
+ my $taxname = ref( $tax ). ' taxnum'. $tax->taxnum;
+# $taxname .= ' pkgnum'. $cust_pkg->pkgnum.
+# ' locationnum'. $cust_pkg->locationnum
+# if $conf->exists('tax-pkg_address') && $cust_pkg->locationnum;
+
if ( exists( $taxlisthash->{ $taxname } ) ) {
push @{ $taxlisthash->{ $taxname } }, $tax_cust_bill_pkg;
}else{
my $self = shift;
my $part_pkg = shift;
my $class = shift;
- my $prefix = shift;
my @taxes = ();
my $geocode = $self->geocode('cch');
# maybe eliminate this entirely, along with all the 0% records
unless ( @taxes ) {
return
- "fatal: can't find tax rate for zip/taxproduct/pkgpart ".
- join('/', ( map $self->get("$prefix$_"),
- qw(zip)
- ),
+ "fatal: can't find tax rate for geocode/taxproduct/pkgpart ".
+ join('/', $geocode,
$part_pkg->taxproduct_description,
- $part_pkg->pkgpart ). "\n";
+ $part_pkg->pkgpart
+ );
}
warn "Found taxes ".
map $_->[0], @{ $sth->fetchall_arrayref };
}
-=item taxline TAXABLES, [ OPTIONSHASH ]
+=item taxline TAXABLES_ARRAYREF, [ OPTION => VALUE ... ]
Returns a listref of a name and an amount of tax calculated for the list of
-packages or amounts referenced by TAXABLES. Returns a scalar error message
-on error.
+packages or amounts referenced by TAXABLES_ARRAYREF. Returns a scalar error
+message on error.
-OPTIONSHASH includes custnum and invoice_date and are hints to this method
+Options include custnum and invoice_date and are hints to this method
=cut
sub taxline {
- my $self = shift;
-
- my $taxables;
- my %opt = ();
-
- if (ref($_[0]) eq 'ARRAY') {
- $taxables = shift;
- %opt = @_;
- }else{
- $taxables = [ @_ ];
- # exemptions broken in this case
- }
+ my( $self, $taxables, %opt ) = @_;
my @exemptions = ();
push @exemptions, @{ $_->_cust_tax_exempt_pkg }
}
$dbh->commit or die $dbh->errstr if $oldAutoCommit;
- return [ $name, $amount ]
+
+ return {
+ 'name' => $name,
+ 'amount' => $amount,
+ };
+
}
=back
warn "calculated taxes as [ $name, $amount ]\n"
if $DEBUG;
- return [$name, $amount];
+ return {
+ 'name' => $name,
+ 'amount' => $amount,
+ };
}
t/cust_pkg_detail.t
FS/cust_location.pm
t/cust_location.t
+FS/cust_bill_pkg_tax_location.pm
+t/cust_bill_pkg_tax_location.t
--- /dev/null
+BEGIN { $| = 1; print "1..1\n" }
+END {print "not ok 1\n" unless $loaded;}
+use FS::cust_bill_pkg_tax_location;
+$loaded=1;
+print "ok 1\n";
die "access denied"
unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
+my $conf = new FS::Conf;
+
#here is the agent virtualization
my $agentnums_sql =
$FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
}
}
+sub _where {
+ my $table = shift;
+ my $prefix = @_ ? shift : '';
+ "
+ ( cust_main_county.county = $table.${prefix}.county
+ OR ( cust_main_county.county IS NULL AND $table.${prefix}.county = '' )
+ OR ( cust_main_county.county = '' AND $table.${prefix}.county IS NULL)
+ OR ( cust_main_county.county IS NULL AND $table.${prefix}.county IS NULL)
+ )
+ AND ( cust_main_county.state = $table.${prefix}.state
+ OR ( cust_main_county.state IS NULL AND $table.${prefix}.state = '' )
+ OR ( cust_main_county.state = '' AND $table.${prefix}.state IS NULL )
+ OR ( cust_main_county.state IS NULL AND $table.${prefix}.state IS NULL )
+ )
+ AND cust_main_county.country = $table.${prefix}.country
+ ";
+
+}
+
if ( $cgi->param('out') ) {
+ my ( $loc_sql, @param ) = FS::cust_pkg->location_sql( 'ornull' => 1 );
+ while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution
+ $loc_sql =~ s/\?/'cust_main_county.'.shift(@param)/e;
+ }
+
+ $loc_sql =~ s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g
+ if $cgi->param('istax');
+
push @where, "
0 = (
- SELECT COUNT(*) FROM cust_main_county
- WHERE ( cust_main_county.county = cust_main.county
- OR ( cust_main_county.county IS NULL AND cust_main.county = '' )
- OR ( cust_main_county.county = '' AND cust_main.county IS NULL)
- OR ( cust_main_county.county IS NULL AND cust_main.county IS NULL)
- )
- AND ( cust_main_county.state = cust_main.state
- OR ( cust_main_county.state IS NULL AND cust_main.state = '' )
- OR ( cust_main_county.state = '' AND cust_main.state IS NULL )
- OR ( cust_main_county.state IS NULL AND cust_main.state IS NULL )
- )
- AND cust_main_county.country = cust_main.country
- AND cust_main_county.tax > 0
- )
+ SELECT COUNT(*) FROM cust_main_county
+ WHERE cust_main_county.tax > 0
+ AND $loc_sql
+ )
";
} elsif ( $cgi->param('country' ) ) {
- my $county = dbh->quote( $cgi->param('county') );
- my $state = dbh->quote( $cgi->param('state') );
- my $country = dbh->quote( $cgi->param('country') );
- push @where,
- " ( county = $county OR $county = '' ) ",
- " ( state = $state OR $state = '' ) ",
- " country = $country "
- ;
- if ( $cgi->param('taxname') ) {
- push @where, 'itemdesc = '. dbh->quote( $cgi->param('taxname') );
- #} elsif ( $cgi->param('taxnameNULL') {
+ my %ph = map { $_ => dbh->quote( $cgi->param($_) ) }
+ qw( county state country );
+
+ my ( $loc_sql, @param ) = FS::cust_pkg->location_sql;
+ while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution
+ $loc_sql =~ s/\?/$ph{shift(@param)}/e;
+ }
+
+ push @where, $loc_sql;
+
+ if ( $cgi->param('istax') ) {
+ if ( $cgi->param('taxname') ) {
+ push @where, 'itemdesc = '. dbh->quote( $cgi->param('taxname') );
+ #} elsif ( $cgi->param('taxnameNULL') {
+ } else {
+ push @where, "( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )";
+ }
+ } elsif ( $cgi->param('nottax') ) {
+ #what can we usefully do with "taxname" ???? look up a class???
} else {
- push @where, "( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )";
+ #warn "neither nottax nor istax parameters specified";
}
push @where, ' taxclass = '. dbh->quote( $cgi->param('taxclass') )
push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
}
}
-push @where, 'pkgnum != 0' if $cgi->param('nottax');
-push @where, 'pkgnum = 0' if $cgi->param('istax');
+push @where, 'cust_bill_pkg.pkgnum != 0' if $cgi->param('nottax');
+push @where, 'cust_bill_pkg.pkgnum = 0' if $cgi->param('istax');
push @where, " tax = 'Y' " if $cgi->param('cust_tax');
my $where = ' WHERE '. join(' AND ', @where);
-my $join_cust = "
- JOIN cust_bill USING ( invnum )
- LEFT JOIN cust_main USING ( custnum )
-";
+my $join_cust = ' JOIN cust_bill USING ( invnum )
+ LEFT JOIN cust_main USING ( custnum ) ';
+
+
+my $join_pkg;
+if ( $cgi->param('nottax') ) {
+
+ $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum )
+ LEFT JOIN part_pkg USING ( pkgpart ) ';
+ $join_pkg .= ' LEFT JOIN cust_location USING ( locationnum ) '
+ if $conf->exists('tax-pkg_address');
+
+} elsif ( $cgi->param('istax') ) {
+
+ #false laziness w/report_tax.cgi $taxfromwhere
+ if ( $conf->exists('tax-pkg_address') ) {
+ $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum )
+ LEFT JOIN cust_location USING ( locationnum ) ';
-my $join_pkg = "
- LEFT JOIN cust_pkg USING ( pkgnum )
- LEFT JOIN part_pkg USING ( pkgpart )
-";
+ #quelle kludge, false laziness w/report_tax.cgi
+ $where =~ s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g;
+ }
+
+} else {
+
+ #die?
+ warn "neiether nottax nor istax parameters specified";
+ #same as before?
+ $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum )
+ LEFT JOIN part_pkg USING ( pkgpart ) ';
+
+}
$count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where";
+my @select = (
+ 'cust_bill_pkg.*',
+ 'cust_bill._date',
+ );
+push @select, 'part_pkg.pkg' unless $cgi->param('istax');
+push @select, 'cust_main.custnum',
+ FS::UI::Web::cust_sql_fields();
+
my $query = {
'table' => 'cust_bill_pkg',
'addl_from' => "$join_cust $join_pkg",
'hashref' => {},
- 'select' => join(', ',
- 'cust_bill_pkg.*',
- 'cust_bill._date',
- 'part_pkg.pkg',
- 'cust_main.custnum',
- FS::UI::Web::cust_sql_fields(),
- ),
+ 'select' => join(', ', @select ),
'extra_sql' => $where,
'order_by' => 'ORDER BY _date, billpkgnum',
};
<TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
<TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax owed</TH>
% unless ( $cgi->param('show_taxclasses') ) {
-
<TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax invoiced</TH>
% }
-
</TR>
+
<TR>
<TH CLASS="grid" BGCOLOR="#cccccc">Total</TH>
<TH CLASS="grid" BGCOLOR="#cccccc"></TH>
<TH CLASS="grid" BGCOLOR="#cccccc"></TH>
<TH CLASS="grid" BGCOLOR="#cccccc">Taxable</TH>
</TR>
+
% my $bgcolor1 = '#eeeeee';
-% my $bgcolor2 = '#ffffff';
-% my $bgcolor;
+% my $bgcolor2 = '#ffffff';
+% my $bgcolor;
%
% foreach my $region ( @regions ) {
%
-% if ( $bgcolor eq $bgcolor1 ) {
-% $bgcolor = $bgcolor2;
-% } else {
-% $bgcolor = $bgcolor1;
-% }
-%
-% my $link = '';
-% if ( $region->{'label'} ne 'Total' ) {
-% if ( $region->{'label'} eq $out ) {
-% $link = ';out=1';
-% } else {
-% $link = ';'. $region->{'url_param'};
-% }
-% }
-%
-%
+% my $link = '';
+% if ( $region->{'label'} ne 'Total' ) {
+% if ( $region->{'label'} eq $out ) {
+% $link = ';out=1';
+% } else {
+% $link = ';'. $region->{'url_param'};
+% }
+% }
%
+% if ( $bgcolor eq $bgcolor1 ) {
+% $bgcolor = $bgcolor2;
+% } else {
+% $bgcolor = $bgcolor1;
+% }
%
-%
-
+% my $td = qq(TD CLASS="grid" BGCOLOR="$bgcolor");
+% my $bigmath = '<FONT FACE="sans-serif" SIZE="+1"><B>';
+% my $bme = '</B></FONT>';
<TR>
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $region->{'label'} %></TD>
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
- <A HREF="<% $baselink. $link %>;nottax=1"><% $money_char %><% sprintf('%.2f', $region->{'total'} ) %></A>
+ <<%$td%>><% $region->{'label'} %></TD>
+ <<%$td%> ALIGN="right">
+ <A HREF="<% $baselink. $link %>;nottax=1"
+ ><% &$money_sprintf( $region->{'total'} ) %></A>
</TD>
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><FONT SIZE="+1"><B> - </B></FONT></TD>
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
- <A HREF="<% $baselink. $link %>;nottax=1;cust_tax=Y"><% $money_char %><% sprintf('%.2f', $region->{'exempt_cust'} ) %></A>
+ <<%$td%>><FONT SIZE="+1"><B> - </B></FONT></TD>
+ <<%$td%> ALIGN="right">
+ <A HREF="<% $baselink. $link %>;nottax=1;cust_tax=Y"
+ ><% &$money_sprintf( $region->{'exempt_cust'} ) %></A>
</TD>
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><FONT SIZE="+1"><B> - </B></FONT></TD>
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
- <A HREF="<% $baselink. $link %>;nottax=1;pkg_tax=Y"><% $money_char %><% sprintf('%.2f', $region->{'exempt_pkg'} ) %></A>
+ <<%$td%>><FONT SIZE="+1"><B> - </B></FONT></TD>
+ <<%$td%> ALIGN="right">
+ <A HREF="<% $baselink. $link %>;nottax=1;pkg_tax=Y"
+ ><% &$money_sprintf( $region->{'exempt_pkg'} ) %></A>
</TD>
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><FONT SIZE="+1"><B> - </B></FONT></TD>
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
- <A HREF="<% $exemptlink. $link %>"><% $money_char %><% sprintf('%.2f', $region->{'exempt_monthly'} ) %></A>
+ <<%$td%>><FONT SIZE="+1"><B> - </B></FONT></TD>
+ <<%$td%> ALIGN="right">
+ <A HREF="<% $exemptlink. $link %>"
+ ><% &$money_sprintf( $region->{'exempt_monthly'} ) %></A>
</TD>
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><FONT SIZE="+1"><B> = </B></FONT></TD>
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
- <% $money_char %><% sprintf('%.2f', $region->{'taxable'} ) %></A>
+ <<%$td%>><FONT SIZE="+1"><B> = </B></FONT></TD>
+ <<%$td%> ALIGN="right">
+ <% &$money_sprintf( $region->{'taxable'} ) %></A>
</TD>
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $region->{'label'} eq 'Total' ? '' : '<FONT FACE="sans-serif" SIZE="+1"><B> X </B></FONT>' %></TD>
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right"><% $region->{'rate'} %></TD>
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $region->{'label'} eq 'Total' ? '' : '<FONT FACE="sans-serif" SIZE="+1"><B> = </B></FONT>' %></TD>
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
- <% $money_char %><% sprintf('%.2f', $region->{'owed'} ) %>
+ <<%$td%>><% $region->{'label'} eq 'Total' ? '' : "$bigmath X $bme" %></TD>
+ <<%$td%> ALIGN="right"><% $region->{'rate'} %></TD>
+ <<%$td%>><% $region->{'label'} eq 'Total' ? '' : "$bigmath = $bme" %></TD>
+ <<%$td%> ALIGN="right">
+ <% &$money_sprintf( $region->{'owed'} ) %>
</TD>
-% unless ( $cgi->param('show_taxclasses') ) {
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
- <A HREF="<% $baselink. $link %>;istax=1"><% $money_char %><% sprintf('%.2f', $region->{'tax'} ) %></A>
+% unless ( $cgi->param('show_taxclasses') ) {
+ <<%$td%> ALIGN="right">
+ <A HREF="<% $baselink. $link %>;istax=1"
+ ><% &$money_sprintf( $region->{'tax'} ) %></A>
</TD>
% }
</TR>
% }
-
</TABLE>
-% if ( $cgi->param('show_taxclasses') ) {
+% if ( $cgi->param('show_taxclasses') ) {
- <BR>
- <% include('/elements/table-grid.html') %>
- <TR>
- <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
- <TH CLASS="grid" BGCOLOR="#cccccc">Tax invoiced</TH>
- </TR>
-% #some false laziness w/above
-% $bgcolor1 = '#eeeeee';
-% $bgcolor2 = '#ffffff';
-% foreach my $region ( @base_regions ) {
+ <BR>
+ <% include('/elements/table-grid.html') %>
+ <TR>
+ <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc">Tax invoiced</TH>
+ </TR>
+
+% #some false laziness w/above
+% $bgcolor1 = '#eeeeee';
+% $bgcolor2 = '#ffffff';
%
-% if ( $bgcolor eq $bgcolor1 ) {
-% $bgcolor = $bgcolor2;
+% foreach my $region ( @base_regions ) {
+%
+% my $link = '';
+% #if ( $region->{'label'} ne 'Total' ) {
+% if ( $region->{'label'} eq $out ) {
+% $link = ';out=1';
% } else {
-% $bgcolor = $bgcolor1;
+% $link = ';'. $region->{'url_param'};
% }
-%
-% my $link = '';
-% #if ( $region->{'label'} ne 'Total' ) {
-% if ( $region->{'label'} eq $out ) {
-% $link = ';out=1';
-% } else {
-% $link = ';'. $region->{'url_param'};
-% }
-% #}
-%
-
-
- <TR>
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $region->{'label'} %></TD>
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
- <A HREF="<% $baselink. $link %>;istax=1"><% $money_char %><% sprintf('%.2f', $region->{'tax'} ) %></A>
- </TD>
- </TR>
-% }
+% #}
%
% if ( $bgcolor eq $bgcolor1 ) {
% $bgcolor = $bgcolor2;
% } else {
% $bgcolor = $bgcolor1;
% }
-%
+% my $td = qq(TD CLASS="grid" BGCOLOR="$bgcolor");
+ <TR>
+ <<%$td%>><% $region->{'label'} %></TD>
+ <<%$td%> ALIGN="right">
+ <A HREF="<% $baselink. $link %>;istax=1"
+ ><% &$money_sprintf( $region->{'tax'} ) %></A>
+ </TD>
+ </TR>
+
+% }
+
+% if ( $bgcolor eq $bgcolor1 ) {
+% $bgcolor = $bgcolor2;
+% } else {
+% $bgcolor = $bgcolor1;
+% }
+% my $td = qq(TD CLASS="grid" BGCOLOR="$bgcolor");
<TR>
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>">Total</TD>
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
- <A HREF="<% $baselink %>;istax=1"><% $money_char %><% sprintf('%.2f', $tax ) %></A>
- </TD>
+ <<%$td%>>Total</TD>
+ <<%$td%> ALIGN="right">
+ <A HREF="<% $baselink %>;istax=1"
+ ><% &$money_sprintf( $tax ) %></A>
+ </TD>
</TR>
</TABLE>
+
% }
+<% include('/elements/footer.html') %>
-</BODY>
-</HTML>
<%init>
die "access denied"
unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
my $conf = new FS::Conf;
-my $money_char = $conf->config('money_char') || '$';
my $user = getotaker;
my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
-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_pkg = "
- LEFT JOIN cust_pkg USING ( pkgnum )
- LEFT JOIN part_pkg USING ( pkgpart )
-";
+my $join_cust = ' JOIN cust_bill USING ( invnum )
+ LEFT JOIN cust_main USING ( custnum ) ';
+my $join_cust_pkg = $join_cust.
+ ' LEFT JOIN cust_pkg USING ( pkgnum )
+ LEFT JOIN part_pkg USING ( pkgpart ) ';
+$join_cust_pkg .= ' LEFT JOIN cust_location USING ( locationnum )'
+ if $conf->exists('tax-pkg_address');
-my $where = "WHERE _date >= $beginning AND _date <= $ending ";
-my @base_param = qw( county county state state country );
-if ( $conf->exists('tax-ship_address') ) {
-
- $where .= "
- AND ( ( ( ship_last IS NULL OR ship_last = '' )
- AND ( county = ? OR ? = '' )
- AND ( state = ? OR ? = '' )
- AND country = ?
- )
- OR ( ship_last IS NOT NULL AND ship_last != ''
- AND ( ship_county = ? OR ? = '' )
- AND ( ship_state = ? OR ? = '' )
- AND ship_country = ?
- )
- )
- ";
- # AND payby != 'COMP'
-
- push @base_param, @base_param;
-
-} else {
+my $from_join_cust_pkg = " FROM cust_bill_pkg $join_cust_pkg ";
- $where .= "
- AND ( county = ? OR ? = '' )
- AND ( state = ? OR ? = '' )
- AND country = ?
- ";
- # AND payby != 'COMP'
+my $where = "WHERE _date >= $beginning AND _date <= $ending ";
-}
+my( $location_sql, @base_param ) = FS::cust_pkg->location_sql;
+$where .= " AND $location_sql ";
my $agentname = '';
if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
$where .= ' AND cust_main.agentnum = '. $agent->agentnum;
}
-my $gotcust = "
- WHERE 0 < ( SELECT COUNT(*) FROM cust_main
-";
-if ( $conf->exists('tax-ship_address') ) {
-
- $gotcust .= "
- WHERE
+sub gotcust {
+ my $table = shift;
+ my $prefix = @_ ? shift : '';
+ "
+ ( $table.${prefix}county = cust_main_county.county
+ OR cust_main_county.county = ''
+ OR cust_main_county.county IS NULL )
+ AND ( $table.${prefix}state = cust_main_county.state
+ OR cust_main_county.state = ''
+ OR cust_main_county.state IS NULL )
+ AND ( $table.${prefix}country = cust_main_county.country )
+ ";
+}
- ( cust_main_county.country = cust_main.country
- OR cust_main_county.country = cust_main.ship_country
- )
+my $gotcust;
+if ( $conf->exists('tax-ship_address') ) {
- AND
+ $gotcust = "
+ ( cust_main_county.country = cust_main.country
+ OR cust_main_county.country = cust_main.ship_country
+ )
- (
+ AND
- ( ( ship_last IS NULL OR ship_last = '' )
- AND ( cust_main_county.country = cust_main.country )
- AND ( cust_main_county.state = cust_main.state
- OR cust_main_county.state = ''
- OR cust_main_county.state IS NULL )
- AND ( cust_main_county.county = cust_main.county
- OR cust_main_county.county = ''
- OR cust_main_county.county IS NULL )
- )
-
- OR
-
- ( ship_last IS NOT NULL AND ship_last != ''
- AND ( cust_main_county.country = cust_main.ship_country )
- AND ( cust_main_county.state = cust_main.ship_state
- OR cust_main_county.state = ''
- OR cust_main_county.state IS NULL )
- AND ( cust_main_county.county = cust_main.ship_county
- OR cust_main_county.county = ''
- OR cust_main_county.county IS NULL )
- )
-
- )
-
- LIMIT 1
- )
+ (
+ ( ( ship_last IS NULL OR ship_last = '' )
+ AND ". gotcust('cust_main'). "
+ )
+ OR
+ ( ship_last IS NOT NULL AND ship_last != ''
+ AND ". gotcust('cust_main', 'ship_'). "
+ )
+ )
";
} else {
- $gotcust .= "
- WHERE ( cust_main.county = cust_main_county.county
- OR cust_main_county.county = ''
- OR cust_main_county.county IS NULL )
- AND ( cust_main.state = cust_main_county.state
- OR cust_main_county.state = ''
- OR cust_main_county.state IS NULL )
- AND ( cust_main.country = cust_main_county.country )
- LIMIT 1
- )
- ";
+ $gotcust = gotcust('cust_main');
}
+if ( $conf->exists('tax-pkg_address') ) {
+ $gotcust = "
+ ( cust_pkg.locationnum IS NULL AND $gotcust)
+ OR ( cust_pkg.locationnum IS NOT NULL AND ". gotcust('cust_location'). " )";
+ $gotcust =
+ "WHERE 0 < ( SELECT COUNT(*) FROM cust_pkg
+ LEFT JOIN cust_main USING ( custnum )
+ LEFT JOIN cust_location USING ( locationnum )
+ WHERE $gotcust
+ LIMIT 1
+ )
+ ";
+} else {
+ $gotcust =
+ "WHERE 0 < ( SELECT COUNT(*) FROM cust_main WHERE $gotcust LIMIT 1 )";
+}
my($total, $tot_taxable, $owed, $tax) = ( 0, 0, 0, 0 );
my( $exempt_cust, $exempt_pkg, $exempt_monthly ) = ( 0, 0, 0 );
my $out = 'Out of taxable region(s)';
my %regions = ();
+
foreach my $r ( qsearch({ 'table' => 'cust_main_county',
'extra_sql' => $gotcust,
})
}
- my $fromwhere = $from_join_cust. $join_pkg. $mywhere. " AND payby != 'COMP' ";
+ my $fromwhere = "$from_join_cust_pkg $mywhere AND payby != 'COMP' ";
# my $label = getlabel($r);
# $regions{$label}->{'label'} = $label;
"SELECT SUM(amount)
FROM cust_tax_exempt_pkg
JOIN cust_bill_pkg USING ( billpkgnum )
- $join_cust $join_pkg
+ $join_cust_pkg
$mywhere"
);
# if ( $x_monthly ) {
: " '' "
)." AS taxclass";
+
my %qsearch = (
'select' => "DISTINCT $distinct, $taxclass_distinct",
'table' => 'cust_main_county',
'extra_sql' => $gotcust,
);
-my $taxwhere = "$from_join_cust $where AND payby != 'COMP' ";
+my $taxfromwhere = " FROM cust_bill_pkg $join_cust ";
+my $taxwhere = $where;
+if ( $conf->exists('tax-pkg_address') ) {
+
+ $taxfromwhere .= 'LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum )
+ LEFT JOIN cust_location USING ( locationnum ) ';
+
+ #quelle kludge
+ $taxwhere =~ s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g;
+
+}
+$taxfromwhere .= " $taxwhere AND payby != 'COMP' ";
my @taxparam = @base_param;
#should i be a cust_main_county method or something
-#need to pass in $taxwhere & @taxparam???
+#need to pass in $taxfromwhere & @taxparam???
my $_taxamount_sub = sub {
my $r = shift;
: "AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )";
my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) ".
- " $taxwhere AND pkgnum = 0 $named_tax";
+ " $taxfromwhere AND cust_bill_pkg.pkgnum = 0 $named_tax";
scalar_sql($r, \@taxparam, $sql );
};
#--
+my $money_char = $conf->config('money_char') || '$';
+my $money_sprintf = sub {
+ $money_char. sprintf('%.2f', shift );
+};
+
sub getlabel {
my $r = shift;
my %opt = @_;
</I>
-% if ($FS::CurrentUser::CurrentUser->access_right('Change customer package')) {
+% if ( ! $cust_pkg->get('cancel')
+% && $FS::CurrentUser::CurrentUser->access_right('Change customer package')
+% )
+% {
<FONT SIZE=-1>
( <%pkg_change_location_link($cust_pkg)%> )
</FONT>
projects / freeside.git / commitdiff