X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_credit_bill_pkg.html;h=622d1cfb47d21c843e5e44e34875e4607f0e580a;hp=0f78344468b3d861e404d069c4a1c5c2100e1c8e;hb=c3da5cf1caa244937d280e0f406927103beef148;hpb=0fb307c305e4bc2c9c27dc25a3308beae3a4d33c diff --git a/httemplate/search/cust_credit_bill_pkg.html b/httemplate/search/cust_credit_bill_pkg.html index 0f7834446..622d1cfb4 100644 --- a/httemplate/search/cust_credit_bill_pkg.html +++ b/httemplate/search/cust_credit_bill_pkg.html @@ -27,7 +27,7 @@ sub { sprintf($money_char.'%.2f', shift->amount ) }, sub { time2str('%b %d %Y', shift->get('cust_credit_date') ) }, - 'otaker', + sub { shift->cust_credit_bill->cust_credit->otaker }, sub { shift->cust_credit_bill->cust_credit->reason }, sub { $_[0]->pkgnum > 0 @@ -38,6 +38,16 @@ sub { time2str('%b %d %Y', shift->_date ) }, \&FS::UI::Web::cust_fields, ], + 'sort_fields' => [ + 'amount', + 'cust_credit_date', + '', #'otaker', + '', #reason + '', #line item description + 'invnum', + '_date', + #cust fields + ], 'links' => [ '', '', @@ -333,83 +343,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 ) @@ -418,7 +353,7 @@ my $join_cust = my $join_pkg; -my $join_cust_bill_pkg = 'LEFT JOIN cust_bill_pkg USING ( billpkgnum '; +my $join_cust_bill_pkg = 'LEFT JOIN cust_bill_pkg USING ( billpkgnum )'; if ( $cgi->param('nottax') ) { @@ -432,29 +367,32 @@ if ( $cgi->param('nottax') ) { } 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 ) '; - - #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 ) ) || + 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 ) '; - } - if ( $conf->exists('tax-pkg_address') ) { - $join_cust_bill_pkg .= ', billpkgtaxlocationnum )'; + $join_pkg .= + ' LEFT JOIN cust_bill_pkg_tax_rate_location 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"; } + $join_pkg .= ' 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; + } + } else { #die? @@ -470,21 +408,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.*',