From c8be0008fa9f9d351c7b0276fbd2a22debe042d2 Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Mon, 3 Nov 2014 23:12:57 -0800 Subject: [PATCH] add customer status to advanced invoice report, RT#29810 --- FS/FS/cust_bill.pm | 217 +--------------------- FS/FS/cust_bill/Search.pm | 308 ++++++++++++++++++++++++++++++++ FS/MANIFEST | 1 + httemplate/search/cust_bill.html | 113 ++++-------- httemplate/search/report_cust_bill.html | 5 + 5 files changed, 347 insertions(+), 297 deletions(-) create mode 100644 FS/FS/cust_bill/Search.pm diff --git a/FS/FS/cust_bill.pm b/FS/FS/cust_bill.pm index e7c799fb6..5be984e86 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 diff --git a/FS/FS/cust_bill/Search.pm b/FS/FS/cust_bill/Search.pm new file mode 100644 index 000000000..1fc818d35 --- /dev/null +++ b/FS/FS/cust_bill/Search.pm @@ -0,0 +1,308 @@ +package FS::cust_bill::Search; + +use strict; +use FS::CurrentUser; +use FS::UI::Web; +use FS::Record qw( qsearchs dbh ); +use FS::cust_main; +use FS::access_user; + +=item search HASHREF + +(Class method) + +Returns a qsearch hash expression to search for parameters specified in HASHREF. +In addition to all parameters accepted by search_sql_where, the following +additional parameters valid: + +=over 4 + +=item newest_percust + +=back + +=cut + +sub search { + my( $class, $params ) = @_; + + my( $count_query, $count_addl ) = ( '', '' ); + + #some false laziness w/cust_bill::re_X + + $count_query = "SELECT COUNT(DISTINCT cust_bill.custnum), 'N/A', 'N/A'" + if $params->{'newest_percust'}; + + my $extra_sql = FS::cust_bill->search_sql_where( $params ); + $extra_sql = "WHERE $extra_sql" if $extra_sql; + + my $join_cust_main = FS::UI::Web::join_cust_main('cust_bill'); + + unless ( $count_query ) { + $count_query = 'SELECT COUNT(*), '. join(', ', + map "SUM($_)", + ( 'charged', + FS::cust_bill->net_sql, + FS::cust_bill->owed_sql, + ) + ); + $count_addl = [ '$%.2f invoiced (gross)', + '$%.2f invoiced (net)', + '$%.2f outstanding balance', + ]; + } + $count_query .= " FROM cust_bill $join_cust_main $extra_sql"; + + #$sql_query = + +{ + 'table' => 'cust_bill', + 'addl_from' => $join_cust_main, + 'hashref' => {}, + 'select' => join(', ', + 'cust_bill.*', + #( map "cust_main.$_", qw(custnum last first company) ), + 'cust_main.custnum as cust_main_custnum', + FS::UI::Web::cust_sql_fields(), + #$class->owed_sql. ' AS owed', + #$class->net_sql. ' AS net', + FS::cust_bill->owed_sql. ' AS owed', + FS::cust_bill->net_sql. ' AS net', + ), + 'extra_sql' => $extra_sql, + 'order_by' => 'ORDER BY '. ( $params->{'order_by'} || 'cust_bill._date' ), + + 'count_query' => $count_query, + 'count_addl' => $count_addl, + }; + +} + +=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 cust_status + +=item cust_classnum + +List reference + +=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 ( $cust_bill::DEBUG ) { + # warn "$me search_sql_where called with params: \n". + # join("\n", map { " $_: ". $param->{$_} } keys %$param ). "\n"; + #} + + #some false laziness w/cust_bill::re_X + + 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"; + } + + #cust_status + if ( $param->{'cust_status'} =~ /^([a-z]+)$/ ) { + push @search, FS::cust_main->cust_status_sql . " = '$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'} =~ /^\s*(\d+)\s*$/ ) { + push @search, "cust_bill.invnum >= $1"; + } + if ( $param->{'invnum_max'} =~ /^\s*(\d+)\s*$/ ) { + 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"; + warn "[FS::cust_bill::Search] WARNING: (fs_queue) can't find CurrentUser $username\n"; + } + } + push @search, $curuser->agentnums_sql; + + join(' AND ', @search ); + +} + +1; + diff --git a/FS/MANIFEST b/FS/MANIFEST index 4811e1094..79a7dc523 100644 --- a/FS/MANIFEST +++ b/FS/MANIFEST @@ -66,6 +66,7 @@ FS/addr_block.pm FS/agent.pm FS/agent_type.pm FS/cust_bill.pm +FS/cust_bill/Search.pm FS/cust_bill_pkg.pm FS/cust_bill_pkg_detail.pm FS/cust_credit.pm diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html index 8d512f583..bd302c64f 100755 --- a/httemplate/search/cust_bill.html +++ b/httemplate/search/cust_bill.html @@ -62,16 +62,16 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('List invoices'); -my $join_cust_main = FS::UI::Web::join_cust_main('cust_bill'); -#here is the agent virtualization -my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql; - my( $count_query, $sql_query ); my $count_addl = ''; -my %search; +my %search = (); if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { + my $join_cust_main = FS::UI::Web::join_cust_main('cust_bill'); + #here is the agent virtualization + my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql; + my $invnum_or_invid = "( invnum = $2 OR agent_invid = $2 )"; my $where = "WHERE $invnum_or_invid AND $agentnums_sql"; @@ -100,56 +100,37 @@ if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) { } else { - #some false laziness w/cust_bill::re_X - my $orderby = 'ORDER BY cust_bill._date'; - - if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { - $search{'agentnum'} = $1; - } - - if ( $cgi->param('refnum') =~ /^(\d+)$/ ) { - $search{'refnum'} = $1; + #deal with some old-style URLs + my($query) = $cgi->keywords; + if ( $query =~ /^(OPEN(\d*)_)?(invnum|date|custnum)$/ ) { + $search{'open'} = 1 if $1; + ($search{'days'}, my $field) = ($2, $3); + $field = "_date" if $field eq 'date'; + $search{'order_by'} = "cust_bill.$field"; } -if ( grep { $_ eq 'cust_classnum' } $cgi->param ) { - $search{'cust_classnum'} = [ $cgi->param('cust_classnum') ]; - } + #scalars + for (qw( agentnum custnum cust_status refnum invnum_min invnum_max + open net newest_percust - if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { - $search{'custnum'} = $1; + )) + { + $search{$_} = $cgi->param($_) if length($cgi->param($_)); } - # 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; - } - if ( $cgi->param('invnum_max') =~ /^\s*(\d+)\s*$/ ) { - $search{'invnum_max'} = $1; + #arrays + for my $param (qw( cust_classnum payby )) { + $search{$param} = [ $cgi->param($param) ] + if grep { $_ eq $param } $cgi->param; } - #amounts + #amounts (range) $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' ); - - my($query) = $cgi->keywords; - if ( $query =~ /^(OPEN(\d*)_)?(invnum|date|custnum)$/ ) { - $search{'open'} = 1 if $1; - ($search{'days'}, my $field) = ($2, $3); - $field = "_date" if $field eq 'date'; - $orderby = "ORDER BY cust_bill.$field"; - } - - if ( $cgi->param('newest_percust') ) { - $search{'newest_percust'} = 1; - $count_query = "SELECT COUNT(DISTINCT cust_bill.custnum), 'N/A', 'N/A'"; - } + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, ''); + $search{'_date'} = [ $beginning, $ending ] + unless $beginning == 0 && $ending == 4294967295; # promised date my $start_of_day = timelocal(0, 0, 0, (localtime(time))[3,4,5]); @@ -170,46 +151,12 @@ if ( grep { $_ eq 'cust_classnum' } $cgi->param ) { } } - #payby - if ($cgi->param('payby')) { - $search{payby} = [ $cgi->param('payby') ]; - } - - my $extra_sql = FS::cust_bill->search_sql_where( \%search ); - $extra_sql = "WHERE $extra_sql" if $extra_sql; - - unless ( $count_query ) { - $count_query = 'SELECT COUNT(*), '. join(', ', - map "SUM($_)", - ( 'charged', - FS::cust_bill->net_sql, - FS::cust_bill->owed_sql, - ) - ); - $count_addl = [ '$%.2f invoiced (gross)', - '$%.2f invoiced (net)', - '$%.2f outstanding balance', - ]; - } - $count_query .= " FROM cust_bill $join_cust_main $extra_sql"; - - $sql_query = { - 'table' => 'cust_bill', - 'addl_from' => $join_cust_main, - 'hashref' => {}, - 'select' => join(', ', - 'cust_bill.*', - #( map "cust_main.$_", qw(custnum last first company) ), - 'cust_main.custnum as cust_main_custnum', - FS::UI::Web::cust_sql_fields(), - FS::cust_bill->owed_sql. ' AS owed', - FS::cust_bill->net_sql. ' AS net', - ), - 'extra_sql' => $extra_sql, - 'order_by' => $orderby, - }; + $sql_query = FS::cust_bill->search(\%search); + $count_query = delete( $sql_query->{'count_query'} ); + $count_addl = delete( $sql_query->{'count_addl'} ); } + my $link = [ "${p}view/cust_bill.cgi?", 'invnum', ]; my $clink = sub { my $cust_bill = shift; diff --git a/httemplate/search/report_cust_bill.html b/httemplate/search/report_cust_bill.html index 4a005e97d..8734467a4 100644 --- a/httemplate/search/report_cust_bill.html +++ b/httemplate/search/report_cust_bill.html @@ -20,6 +20,11 @@ 'disable_empty' => 0, &> + <& /elements/tr-select-cust_main-status.html, + 'label' => 'Customer status', + 'field' => 'cust_status', + &> + <& /elements/tr-select-cust_class.html, label => mt('Customer Class'), field => 'cust_classnum', -- 2.11.0