X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_credit_bill_pkg.html;h=622d1cfb47d21c843e5e44e34875e4607f0e580a;hb=aab3b8983e23c77f3c8654f458eca0f53ade80f8;hp=a1d4561237ed2938ec1b2f0aac115d55391b1781;hpb=a5c4a4352ed1ed761f9e040d72e3ad9ea7d8f1ac;p=freeside.git diff --git a/httemplate/search/cust_credit_bill_pkg.html b/httemplate/search/cust_credit_bill_pkg.html index a1d456123..622d1cfb4 100644 --- a/httemplate/search/cust_credit_bill_pkg.html +++ b/httemplate/search/cust_credit_bill_pkg.html @@ -8,57 +8,59 @@ #'#', 'Amount', - # credit date, By, Reason, - # line item, invoice + #credit + 'Date', + 'By', + 'Reason', + + # line item 'Description', - 'Setup charge', - ( $use_usage eq 'usage' - ? 'Usage charge' - : 'Recurring charge' - ), + + #invoice 'Invoice', 'Date', FS::UI::Web::cust_header(), ], 'fields' => [ #'creditbillpkgnum', - 'amount', + sub { sprintf($money_char.'%.2f', shift->amount ) }, + + sub { time2str('%b %d %Y', shift->get('cust_credit_date') ) }, + sub { shift->cust_credit_bill->cust_credit->otaker }, + sub { shift->cust_credit_bill->cust_credit->reason }, sub { $_[0]->pkgnum > 0 ? $_[0]->get('pkg') # possibly use override.pkg : $_[0]->get('itemdesc') # but i think this correct }, - #strikethrough or "N/A ($amount)" or something these when - # they're not applicable to pkg_tax search - sub { sprintf($money_char.'%.2f', shift->setup ) }, - sub { my $row = shift; - my $value = 0; - if ( $use_usage eq 'recurring' ) { - $value = $row->recur - $row->usage; - } elsif ( $use_usage eq 'usage' ) { - $value = $row->usage; - } else { - $value = $row->recur; - } - sprintf($money_char.'%.2f', $value ); - }, 'invnum', 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' => [ '', '', '', '', + '', $ilink, $ilink, ( map { $_ ne 'Cust. Status' ? $clink : '' } FS::UI::Web::cust_header() ), ], - 'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(), + 'align' => 'rrlllrr'.FS::UI::Web::cust_aligns(), 'color' => [ '', '', @@ -66,6 +68,7 @@ '', '', '', + '', FS::UI::Web::cust_colors(), ], 'style' => [ @@ -75,6 +78,7 @@ '', '', '', + '', FS::UI::Web::cust_styles(), ], ) @@ -98,9 +102,6 @@ my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); push @where, "cust_bill._date >= $beginning", "cust_bill._date <= $ending"; -push @where , " payby != 'COMP' " - unless $cgi->param('include_comp_cust'); - if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { push @where, "cust_main.agentnum = $1"; } @@ -342,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 ) @@ -427,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') ) { @@ -441,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? @@ -479,24 +408,17 @@ 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"; -#} - -my @select = ( 'cust_credit_bill_pkg.amount', +$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.*', + 'cust_credit.otaker', + 'cust_credit._date AS cust_credit_date', 'cust_bill._date', ); push @select, 'part_pkg.pkg' unless $cgi->param('istax');