From d99758f77f8d2f839934498af839109596e216b8 Mon Sep 17 00:00:00 2001 From: ivan Date: Mon, 16 Nov 2009 03:55:18 +0000 Subject: [PATCH] add ability to search on ranges of charged, owed to adv. invoice report, RT#6407 --- FS/FS/cust_bill.pm | 68 +++++++++++++++++++++++++-------- FS/FS/cust_main.pm | 4 +- httemplate/search/cust_bill.html | 33 ++++++++-------- httemplate/search/report_cust_bill.html | 15 ++++++++ 4 files changed, 85 insertions(+), 35 deletions(-) diff --git a/FS/FS/cust_bill.pm b/FS/FS/cust_bill.pm index 2229cf9bc..82fa78a4c 100644 --- a/FS/FS/cust_bill.pm +++ b/FS/FS/cust_bill.pm @@ -35,7 +35,7 @@ use FS::payby; @ISA = qw( FS::cust_main_Mixin FS::Record ); -$DEBUG = 0; +$DEBUG = 1; $me = '[FS::cust_bill]'; #ask FS::UID to run this stuff for us later @@ -3741,13 +3741,9 @@ specified in HASHREF. Valid parameters are =over 4 -=item begin +=item _date -Epoch date (UNIX timestamp) setting a lower bound for _date values - -=item end - -Epoch date (UNIX timestamp) setting an upper bound for _date values +List reference of start date, end date, as UNIX timestamps. =item invnum_min @@ -3755,10 +3751,22 @@ Epoch date (UNIX timestamp) setting an upper bound for _date values =item agentnum +=item charged + +List reference of charged limits (exclusive). + =item owed +List reference of charged limits (exclusive). + +=item open + +flag, return open invoices only + =item net +flag, return net invoices only + =item days =item newest_percust @@ -3778,31 +3786,59 @@ sub search_sql { my @search = (); - if ( $param->{'begin'} =~ /^(\d+)$/ ) { - push @search, "cust_bill._date >= $1"; + #agentnum + if ( $param->{'agentnum'} =~ /^(\d+)$/ ) { + push @search, "cust_main.agentnum = $1"; } - if ( $param->{'end'} =~ /^(\d+)$/ ) { - push @search, "cust_bill._date < $1"; + + #_date + if ( $param->{_date} ) { + my($beginning, $ending) = @{$param->{_date}}; + + push @search, "cust_bill._date >= $beginning", + "cust_bill._date < $ending"; } + + #invnum if ( $param->{'invnum_min'} =~ /^(\d+)$/ ) { push @search, "cust_bill.invnum >= $1"; } if ( $param->{'invnum_max'} =~ /^(\d+)$/ ) { push @search, "cust_bill.invnum <= $1"; } - if ( $param->{'agentnum'} =~ /^(\d+)$/ ) { - push @search, "cust_main.agentnum = $1"; + + #charged + if ( $param->{charged} ) { + my @charged = ref($param->{charged}) + ? @{ $param->{charged} } + : ($param->{charged}); + + push @search, map { s/^charged/cust_bill.charged/; $_; } + @charged; } - push @search, '0 != '. FS::cust_bill->owed_sql - if $param->{'open'}; + my $owed_sql = FS::cust_bill->owed_sql; + + #owed + if ( $param->{owed} ) { + my @owed = ref($param->{owed}) + ? @{ $param->{owed} } + : ($param->{owed}); + push @search, map { s/^owed/$owed_sql/; $_; } + @owed; + } + #open/net flags + push @search, "0 != $owed_sql" + if $param->{'open'}; push @search, '0 != '. FS::cust_bill->net_sql if $param->{'net'}; + #days push @search, "cust_bill._date < ". (time-86400*$param->{'days'}) if $param->{'days'}; + #newest_percust if ( $param->{'newest_percust'} ) { #$distinct = 'DISTINCT ON ( cust_bill.custnum )'; @@ -3826,6 +3862,7 @@ sub search_sql { } + #agent virtualization my $curuser = $FS::CurrentUser::CurrentUser; if ( $curuser->username eq 'fs_queue' && $param->{'CurrentUser'} =~ /^(\w+)$/ ) { @@ -3840,7 +3877,6 @@ sub search_sql { warn "$me WARNING: (fs_queue) can't find CurrentUser $username\n"; } } - push @search, $curuser->agentnums_sql; join(' AND ', @search ); diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index 08c7a5113..5d4e47ee7 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -8428,8 +8428,10 @@ sub search_sql { #my $balance_sql = $class->balance_sql(); my $balance_sql = FS::cust_main->balance_sql(); + my @current_balance = @{ $params->{'current_balance'} }; + push @where, map { s/current_balance/$balance_sql/; $_ } - @{ $params->{'current_balance'} }; + @current_balance; ## # custbatch diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html index 751bef677..2f6bd9cc9 100755 --- a/httemplate/search/cust_bill.html +++ b/httemplate/search/cust_bill.html @@ -85,24 +85,16 @@ if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { } else { #some false laziness w/cust_bill::re_X - my @where; my $orderby = 'ORDER BY cust_bill._date'; - if ( $cgi->param('beginning') - && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) { - $search{'begin'} = str2time($1); - } - if ( $cgi->param('ending') - && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) { - $search{'end'} = str2time($1) + 86399; + if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $search{'agentnum'} = $1; } - if ( $cgi->param('begin') =~ /^(\d+)$/ ) { - $search{'begin'} = $1; - } - if ( $cgi->param('end') =~ /^(\d+)$/ ) { - $search{'end'} = $1; - } + # begin/end/beginning/ending + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, ''); + $search{'_date'} = [ $beginning, $ending ] + unless $beginning == 0 && $ending == 4294967295; if ( $cgi->param('invnum_min') =~ /^\s*(\d+)\s*$/ ) { $search{'invnum_min'} = $1; @@ -111,9 +103,9 @@ if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { $search{'invnum_max'} = $1; } - if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { - $search{'agentnum'} = $1; - } + #amounts + $search{$_} = [ FS::UI::Web::parse_lt_gt($cgi, $_) ] + foreach qw( charged owed ); $search{'open'} = 1 if $cgi->param('open'); $search{'net'} = 1 if $cgi->param('net' ); @@ -188,7 +180,12 @@ my $html_init = join("\n", map { $_, #key ), qq!
!, - ( map qq!!, keys %search ), + ( map { my $f = $_; + my @values = ref($search{$f}) ? @{ $search{$f} } : $search{$f}; + map qq!!, @values; + } + keys %search + ), qq!
! } qw( print_ email_ fax_ ftp_ spool_ ) ). diff --git a/httemplate/search/report_cust_bill.html b/httemplate/search/report_cust_bill.html index 96cf49263..00d566a62 100644 --- a/httemplate/search/report_cust_bill.html +++ b/httemplate/search/report_cust_bill.html @@ -11,7 +11,21 @@ 'disable_empty' => 0, ) %> + <% include( '/elements/tr-input-beginning_ending.html' ) %> + + <% include( '/elements/tr-input-lessthan_greaterthan.html', + label => 'Charged', + field => 'charged', + ) + %> + + <% include( '/elements/tr-input-lessthan_greaterthan.html', + label => 'Owed', + field => 'owed', + ) + %> + Show only open invoices @@ -20,6 +34,7 @@ Show only the single most recent invoice per-customer +
-- 2.11.0