X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_bill_pkg.cgi;h=17b4bc2400345ff4ab95f25007c889a469ecd0fd;hb=4b5c6ba830fe2845a0055e1fcf5af0c7e8b8cd0c;hp=875cbc3ac0a36e4846d6832d5ad62e7254197995;hpb=386ec52836abbe32ce2fbf575e851a75f6e112f8;p=freeside.git diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 875cbc3ac..17b4bc240 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -1,10 +1,74 @@ -<% +<% include( 'elements/search.html', + 'title' => 'Line items', + 'name' => 'line items', + 'query' => $query, + 'count_query' => $count_query, + 'count_addl' => [ $money_char. '%.2f total', ], + 'header' => [ + '#', + 'Description', + 'Setup charge', + 'Recurring charge', + 'Invoice', + 'Date', + FS::UI::Web::cust_header(), + ], + 'fields' => [ + 'billpkgnum', + sub { $_[0]->pkgnum > 0 + ? $_[0]->get('pkg') + : $_[0]->get('itemdesc') + }, + #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 { sprintf($money_char.'%.2f', shift->recur ) }, + 'invnum', + sub { time2str('%b %d %Y', shift->_date ) }, + \&FS::UI::Web::cust_fields, + ], + 'links' => [ + '', + '', + '', + '', + $ilink, + $ilink, + ( map { $_ ne 'Cust. Status' ? $clink : '' } + FS::UI::Web::cust_header() + ), + ], + 'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(), + 'color' => [ + '', + '', + '', + '', + '', + '', + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + '', + '', + '', + '', + FS::UI::Web::cust_styles(), + ], + ) +%> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); my $join_cust = " JOIN cust_bill USING ( invnum ) - JOIN cust_main USING ( custnum ) + LEFT JOIN cust_main USING ( custnum ) "; my $join_pkg = " @@ -12,26 +76,41 @@ my $join_pkg = " LEFT JOIN part_pkg USING ( pkgpart ) "; -my $where = " - WHERE _date >= $beginning AND _date <= $ending - AND payby != 'COMP' -"; +my $where = " WHERE _date >= $beginning AND _date <= $ending "; + +$where .= " AND payby != 'COMP' " + unless $cgi->param('include_comp_cust'); + +if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $where .= " AND agentnum = $1 "; +} + +if ( $cgi->param('classnum') =~ /^(\d+)$/ ) { + if ( $1 == 0 ) { + $where .= " AND classnum IS NULL "; + } else { + $where .= " AND classnum = $1 "; + } +} if ( $cgi->param('out') ) { $where .= " - AND 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 - ) - 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 - ) - AND cust_main_county.country = cust_main.country - AND cust_main_county.tax > 0 + AND 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 + ) "; } elsif ( $cgi->param('country' ) ) { @@ -59,19 +138,27 @@ my $count_query; if ( $cgi->param('pkg_tax') ) { $count_query = - "SELECT COUNT(*), SUM( ( CASE WHEN part_pkg.setuptax = 'Y' - THEN cust_bill_pkg.setup - ELSE 0 ) - + - ( CASE WHEN part_pkg.recurtax = 'Y' - THEN cust_bill_pkg.recur - ELSE 0 ) - )"; + "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 + ) + ) + "; $where .= " AND ( ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 ) OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) - )"; + ) + AND ( tax != 'Y' OR tax IS NULL ) + "; } else { @@ -79,7 +166,7 @@ if ( $cgi->param('pkg_tax') ) { "SELECT COUNT(*), SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)"; } -$count_query += " FROM cust_bill_pkg $join_cust $join_pkg $where"; +$count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where"; my $query = { 'table' => 'cust_bill_pkg', @@ -101,45 +188,4 @@ my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; my $conf = new FS::Conf; my $money_char = $conf->config('money_char') || '$'; -%><%= include( 'elements/search.html', - 'title' => 'Line items', - 'name' => 'line items', - 'query' => $query, - 'count_query' => $count_query, - 'count_addl' => [ $money_char. '%.2f total', ], - 'header' => [ - '#', - 'Description', - 'Setup charge', - 'Recurring charge', - 'Invoice', - 'Date', - FS::UI::Web::cust_header(), - ], - 'fields' => [ - 'billpkgnum', - sub { $_[0]->pkgnum > 0 - ? $_[0]->get('pkg') - : $_[0]->get('itemdesc') - }, - #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 { sprintf($money_char.'%.2f', shift->recur ) }, - 'invnum', - sub { time2str('%b %d %Y', shift->_date ) }, - \&FS::UI::Web::cust_fields, - ], - 'links' => [ - '', - '', - '', - '', - $ilink, - $ilink, - ( map { $clink } FS::UI::Web::cust_header() ), - ], - 'align' => 'rlrrrc', - ) -%> - +