From c8be0008fa9f9d351c7b0276fbd2a22debe042d2 Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Mon, 3 Nov 2014 23:12:57 -0800 Subject: add customer status to advanced invoice report, RT#29810 --- FS/FS/cust_bill.pm | 217 +---------------------------------------------------- 1 file changed, 3 insertions(+), 214 deletions(-) (limited to 'FS/FS/cust_bill.pm') diff --git a/FS/FS/cust_bill.pm b/FS/FS/cust_bill.pm index e7c799f..5be984e 100644 --- a/FS/FS/cust_bill.pm +++ b/FS/FS/cust_bill.pm @@ -1,5 +1,7 @@ package FS::cust_bill; -use base qw( FS::Template_Mixin FS::cust_main_Mixin FS::Record ); +use base qw( FS::cust_bill::Search FS::Template_Mixin + FS::cust_main_Mixin FS::Record + ); use strict; use vars qw( $DEBUG $me ); @@ -3333,219 +3335,6 @@ sub due_date_sql { ) * 86400 + cust_bill._date' } -=item search_sql_where HASHREF - -Class method which returns an SQL WHERE fragment to search for parameters -specified in HASHREF. Valid parameters are - -=over 4 - -=item _date - -List reference of start date, end date, as UNIX timestamps. - -=item invnum_min - -=item invnum_max - -=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 - -=item custnum - -Return only invoices belonging to that customer. - -=item cust_classnum - -Limit to that customer class (single value or arrayref). - -=item payby - -Limit to customers with that payment method (single value or arrayref). - -=item refnum - -Limit to customers with that advertising source. - -=back - -Note: validates all passed-in data; i.e. safe to use with unchecked CGI params. - -=cut - -sub search_sql_where { - my($class, $param) = @_; - if ( $DEBUG ) { - warn "$me search_sql_where called with params: \n". - join("\n", map { " $_: ". $param->{$_} } keys %$param ). "\n"; - } - - my @search = (); - - #agentnum - if ( $param->{'agentnum'} =~ /^(\d+)$/ ) { - push @search, "cust_main.agentnum = $1"; - } - - #refnum - if ( $param->{'refnum'} =~ /^(\d+)$/ ) { - push @search, "cust_main.refnum = $1"; - } - - #custnum - if ( $param->{'custnum'} =~ /^(\d+)$/ ) { - push @search, "cust_bill.custnum = $1"; - } - - #customer classnum (false laziness w/ cust_main/Search.pm) - if ( $param->{'cust_classnum'} ) { - - my @classnum = ref( $param->{'cust_classnum'} ) - ? @{ $param->{'cust_classnum'} } - : ( $param->{'cust_classnum'} ); - - @classnum = grep /^(\d*)$/, @classnum; - - if ( @classnum ) { - push @search, '( '. join(' OR ', map { - $_ ? "cust_main.classnum = $_" - : "cust_main.classnum IS NULL" - } - @classnum - ). - ' )'; - } - - } - - #payby - if ( $param->{payby} ) { - my $payby = $param->{payby}; - $payby = [ $payby ] unless ref $payby; - my $payby_in = join(',', map {dbh->quote($_)} @$payby); - push @search, "cust_main.payby IN($payby_in)" if length($payby_in); - } - - #_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"; - } - - #charged - if ( $param->{charged} ) { - my @charged = ref($param->{charged}) - ? @{ $param->{charged} } - : ($param->{charged}); - - push @search, map { s/^charged/cust_bill.charged/; $_; } - @charged; - } - - 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 )'; - #$orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC'; - - my @newest_where = map { my $x = $_; - $x =~ s/\bcust_bill\./newest_cust_bill./g; - $x; - } - grep ! /^cust_main./, @search; - my $newest_where = scalar(@newest_where) - ? ' AND '. join(' AND ', @newest_where) - : ''; - - - push @search, "cust_bill._date = ( - SELECT(MAX(newest_cust_bill._date)) FROM cust_bill AS newest_cust_bill - WHERE newest_cust_bill.custnum = cust_bill.custnum - $newest_where - )"; - - } - - #promised_date - also has an option to accept nulls - if ( $param->{promised_date} ) { - my($beginning, $ending, $null) = @{$param->{promised_date}}; - - push @search, "(( cust_bill.promised_date >= $beginning AND ". - "cust_bill.promised_date < $ending )" . - ($null ? ' OR cust_bill.promised_date IS NULL ) ' : ')'); - } - - #agent virtualization - my $curuser = $FS::CurrentUser::CurrentUser; - if ( $curuser->username eq 'fs_queue' - && $param->{'CurrentUser'} =~ /^(\w+)$/ ) { - my $username = $1; - my $newuser = qsearchs('access_user', { - 'username' => $username, - 'disabled' => '', - } ); - if ( $newuser ) { - $curuser = $newuser; - } else { - warn "$me WARNING: (fs_queue) can't find CurrentUser $username\n"; - } - } - push @search, $curuser->agentnums_sql; - - join(' AND ', @search ); - -} - =back =head1 BUGS -- cgit v1.1