From: ivan Date: Fri, 15 Apr 2011 21:55:25 +0000 (+0000) Subject: link new tax report to cust_credit_bill_pkg for credits, RT#12332 X-Git-Tag: freeside_2_3_0~384 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=38919d1e68a62afea9cc9ae23b28957ea7a7987d link new tax report to cust_credit_bill_pkg for credits, RT#12332 --- diff --git a/httemplate/search/cust_credit_bill_pkg.html b/httemplate/search/cust_credit_bill_pkg.html index 0f7834446..d20462dea 100644 --- a/httemplate/search/cust_credit_bill_pkg.html +++ b/httemplate/search/cust_credit_bill_pkg.html @@ -38,6 +38,8 @@ sub { time2str('%b %d %Y', shift->_date ) }, \&FS::UI::Web::cust_fields, ], + #'sort_fields' => [ #XXX add + #], 'links' => [ '', '', @@ -333,83 +335,8 @@ if ( $cgi->param('cust_tax') ) { push @where, $cust_exempt; } -my $use_usage = $cgi->param('use_usage'); - -my $count_query; -if ( $cgi->param('pkg_tax') ) { #does this mean anything here? - - $count_query = - "SELECT COUNT(*), - SUM( - ( CASE WHEN part_pkg.setuptax = 'Y' - THEN cust_bill_pkg.setup - ELSE 0 - END - ) - + - ( CASE WHEN part_pkg.recurtax = 'Y' - THEN cust_bill_pkg.recur - ELSE 0 - END - ) - ) - "; - - push @where, "( ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 ) - OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) )", - "( tax != 'Y' OR tax IS NULL )"; - -} elsif ( $cgi->param('taxable') ) { #again, meaningful? - - my $setup_taxable = "( - CASE WHEN part_pkg.setuptax = 'Y' - THEN 0 - ELSE cust_bill_pkg.setup - END - )"; - - my $recur_taxable = "( - CASE WHEN part_pkg.recurtax = 'Y' - THEN 0 - ELSE cust_bill_pkg.recur - END - )"; - - my $exempt = "( - SELECT COALESCE( SUM(amount), 0 ) FROM cust_tax_exempt_pkg - WHERE cust_tax_exempt_pkg.billpkgnum = cust_bill_pkg.billpkgnum - )"; - - $count_query = - "SELECT COUNT(*), SUM( $setup_taxable + $recur_taxable - $exempt )"; - - push @where, - #not tax-exempt package (setup or recur) - "( - ( ( part_pkg.setuptax != 'Y' OR part_pkg.setuptax IS NULL ) - AND cust_bill_pkg.setup > 0 ) - OR - ( ( part_pkg.recurtax != 'Y' OR part_pkg.recurtax IS NULL ) - AND cust_bill_pkg.recur > 0 ) - )", - #not a tax_exempt customer - "( tax != 'Y' OR tax IS NULL )"; - #not covered in full by a monthly tax exemption (texas tax) - "0 < ( $setup_taxable + $recur_taxable - $exempt )", - -} else { - - $count_query = "SELECT COUNT(*), "; - - if ( $use_usage eq 'recurring' ) { #mean anything? - $count_query .= "SUM(setup + recur - usage)"; - } elsif ( $use_usage eq 'usage' ) { #mean anything? - $count_query .= "SUM(usage)"; - } else { - $count_query .= "SUM(cust_credit_bill_pkg.amount)"; - } - -} +my $count_query = "SELECT COUNT(DISTINCT billpkgnum), + SUM(cust_credit_bill_pkg.amount)"; my $join_cust = ' JOIN cust_bill ON ( cust_bill_pkg.invnum = cust_bill.invnum ) @@ -432,28 +359,34 @@ if ( $cgi->param('nottax') ) { } elsif ( $cgi->param('istax') ) { #false laziness w/report_tax.cgi $taxfromwhere - if ( $conf->exists('tax-pkg_address') ) { + if ( scalar( grep( /locationtaxid/, $cgi->param ) ) || + $cgi->param('iscredit') eq 'rate') { + + $join_pkg .= + ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '. + ' LEFT JOIN tax_rate_location USING ( taxratelocationnum ) '; + + } elsif ( $conf->exists('tax-pkg_address') ) { + $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum ) LEFT JOIN cust_location USING ( locationnum ) '; #quelle kludge, somewhat false laziness w/report_tax.cgi s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g for @where; - } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) || - $cgi->param('iscredit') eq 'rate') { - $join_pkg .= - ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '. - ' LEFT JOIN tax_rate_location USING ( taxratelocationnum ) '; } - if ( $conf->exists('tax-pkg_address') ) { - $join_cust_bill_pkg .= ', billpkgtaxlocationnum )'; + #if ( $cgi->param('iscredit') ) { + $join_pkg .= ' JOIN cust_credit_bill_pkg USING ( billpkgnum'; + if ( $cgi->param('iscredit') eq 'rate' ) { + $join_pkg .= ', billpkgtaxratelocationnum )'; + } elsif ( $conf->exists('tax-pkg_address') ) { + $join_pkg .= ', billpkgtaxlocationnum )'; push @where, "billpkgtaxratelocationnum IS NULL"; - #} elsif ( $cgi->param('iscredit') eq 'rate' ) { - # $join_pkg .= ', billpkgtaxratelocationnum )'; } else { - $join_cust_bill_pkg .= ' )'; + $join_pkg .= ' )'; push @where, "billpkgtaxratelocationnum IS NULL"; } + #} } else { @@ -470,21 +403,12 @@ my $where = ' WHERE '. join(' AND ', @where); my $join_credit = ' LEFT JOIN cust_credit_bill USING ( creditbillnum ) LEFT JOIN cust_credit USING ( crednum ) '; -#if ($use_usage) { -# $count_query .= -# " FROM (SELECT cust_bill_pkg.setup, cust_bill_pkg.recur, -# ( SELECT COALESCE( SUM(amount), 0 ) FROM cust_bill_pkg_detail -# WHERE cust_bill_pkg.billpkgnum = cust_bill_pkg_detail.billpkgnum -# ) AS usage FROM cust_bill_pkg $join_cust $join_pkg $where -# ) AS countquery"; -#} else { - $count_query .= " FROM cust_credit_bill_pkg - $join_pkg - $join_cust_bill_pkg - $join_credit - $join_cust - $where"; -#} +$count_query .= " FROM cust_credit_bill_pkg + $join_pkg + $join_cust_bill_pkg + $join_credit + $join_cust + $where"; my @select = ( 'cust_credit_bill_pkg.*', 'cust_bill_pkg.*', diff --git a/httemplate/search/report_newtax.cgi b/httemplate/search/report_newtax.cgi index 767d8cf14..4ec3f4da4 100755 --- a/httemplate/search/report_newtax.cgi +++ b/httemplate/search/report_newtax.cgi @@ -49,7 +49,7 @@ <% $tax->{base} ? qq!! : '' %> - <% $money_char %><% sprintf('%.2f', $tax->{'credit'} ) %> + <% $money_char %><% sprintf('%.2f', $tax->{'credit'} ) %> <% !($tax->{base}) ? qq!! : '' %> @@ -203,5 +203,6 @@ my $dateagentlink = "begin=$beginning;end=$ending"; $dateagentlink .= ';agentnum='. $cgi->param('agentnum') if length($agentname); my $baselink = $p. "search/cust_bill_pkg.cgi?$dateagentlink"; +my $creditlink = $p. "search/cust_credit_bill_pkg.html?$dateagentlink";