1 package FS::cust_bill::Search;
6 use FS::Record qw( qsearchs dbh );
14 Returns a qsearch hash expression to search for parameters specified in HASHREF.
15 In addition to all parameters accepted by search_sql_where, the following
16 additional parameters valid:
27 my( $class, $params ) = @_;
29 my( $count_query, $count_addl ) = ( '', '' );
31 #some false laziness w/cust_bill::re_X
33 $count_query = "SELECT COUNT(DISTINCT cust_bill.custnum), 'N/A', 'N/A'"
34 if $params->{'newest_percust'};
36 my $extra_sql = FS::cust_bill->search_sql_where( $params );
37 $extra_sql = "WHERE $extra_sql" if $extra_sql;
39 my $join_cust_main = FS::UI::Web::join_cust_main('cust_bill');
41 unless ( $count_query ) {
42 $count_query = 'SELECT COUNT(*), '. join(', ',
45 FS::cust_bill->net_sql,
46 FS::cust_bill->owed_sql,
49 $count_addl = [ '$%.2f invoiced (gross)',
50 '$%.2f invoiced (net)',
51 '$%.2f outstanding balance',
54 $count_query .= " FROM cust_bill $join_cust_main $extra_sql";
58 'table' => 'cust_bill',
59 'addl_from' => $join_cust_main,
61 'select' => join(', ',
63 #( map "cust_main.$_", qw(custnum last first company) ),
64 'cust_main.custnum as cust_main_custnum',
65 FS::UI::Web::cust_sql_fields(),
66 #$class->owed_sql. ' AS owed',
67 #$class->net_sql. ' AS net',
68 FS::cust_bill->owed_sql. ' AS owed',
69 FS::cust_bill->net_sql. ' AS net',
71 'extra_sql' => $extra_sql,
72 'order_by' => 'ORDER BY '. ( $params->{'order_by'} || 'cust_bill._date' ),
74 'count_query' => $count_query,
75 'count_addl' => $count_addl,
80 =item search_sql_where HASHREF
82 Class method which returns an SQL WHERE fragment to search for parameters
83 specified in HASHREF. Valid parameters are
89 List reference of start date, end date, as UNIX timestamps.
105 List reference of charged limits (exclusive).
109 List reference of charged limits (exclusive).
113 flag, return open invoices only
117 flag, return net invoices only
125 Return only invoices belonging to that customer.
129 Limit to that customer class (single value or arrayref).
133 Limit to customers with that payment method (single value or arrayref).
137 Limit to customers with that advertising source.
141 Note: validates all passed-in data; i.e. safe to use with unchecked CGI params.
145 sub search_sql_where {
146 my($class, $param) = @_;
147 #if ( $cust_bill::DEBUG ) {
148 # warn "$me search_sql_where called with params: \n".
149 # join("\n", map { " $_: ". $param->{$_} } keys %$param ). "\n";
152 #some false laziness w/cust_bill::re_X
157 if ( $param->{'agentnum'} =~ /^(\d+)$/ ) {
158 push @search, "cust_main.agentnum = $1";
162 if ( $param->{'refnum'} =~ /^(\d+)$/ ) {
163 push @search, "cust_main.refnum = $1";
167 if ( $param->{'custnum'} =~ /^(\d+)$/ ) {
168 push @search, "cust_bill.custnum = $1";
172 if ( $param->{'cust_status'} =~ /^([a-z]+)$/ ) {
173 push @search, FS::cust_main->cust_status_sql . " = '$1' ";
176 #customer classnum (false laziness w/ cust_main/Search.pm)
177 if ( $param->{'cust_classnum'} ) {
179 my @classnum = ref( $param->{'cust_classnum'} )
180 ? @{ $param->{'cust_classnum'} }
181 : ( $param->{'cust_classnum'} );
183 @classnum = grep /^(\d*)$/, @classnum;
186 push @search, '( '. join(' OR ', map {
187 $_ ? "cust_main.classnum = $_"
188 : "cust_main.classnum IS NULL"
198 if ( $param->{payby} ) {
199 my $payby = $param->{payby};
200 $payby = [ $payby ] unless ref $payby;
201 my $payby_in = join(',', map {dbh->quote($_)} @$payby);
202 push @search, "cust_main.payby IN($payby_in)" if length($payby_in);
206 if ( $param->{_date} ) {
207 my($beginning, $ending) = @{$param->{_date}};
209 push @search, "cust_bill._date >= $beginning",
210 "cust_bill._date < $ending";
214 if ( $param->{'invnum_min'} =~ /^\s*(\d+)\s*$/ ) {
215 push @search, "cust_bill.invnum >= $1";
217 if ( $param->{'invnum_max'} =~ /^\s*(\d+)\s*$/ ) {
218 push @search, "cust_bill.invnum <= $1";
222 if ( $param->{charged} ) {
223 my @charged = ref($param->{charged})
224 ? @{ $param->{charged} }
225 : ($param->{charged});
227 push @search, map { s/^charged/cust_bill.charged/; $_; }
231 my $owed_sql = FS::cust_bill->owed_sql;
234 if ( $param->{owed} ) {
235 my @owed = ref($param->{owed})
236 ? @{ $param->{owed} }
238 push @search, map { s/^owed/$owed_sql/; $_; }
243 push @search, "0 != $owed_sql"
245 push @search, '0 != '. FS::cust_bill->net_sql
249 push @search, "cust_bill._date < ". (time-86400*$param->{'days'})
253 if ( $param->{'newest_percust'} ) {
255 #$distinct = 'DISTINCT ON ( cust_bill.custnum )';
256 #$orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC';
258 my @newest_where = map { my $x = $_;
259 $x =~ s/\bcust_bill\./newest_cust_bill./g;
262 grep ! /^cust_main./, @search;
263 my $newest_where = scalar(@newest_where)
264 ? ' AND '. join(' AND ', @newest_where)
268 push @search, "cust_bill._date = (
269 SELECT(MAX(newest_cust_bill._date)) FROM cust_bill AS newest_cust_bill
270 WHERE newest_cust_bill.custnum = cust_bill.custnum
276 #promised_date - also has an option to accept nulls
277 if ( $param->{promised_date} ) {
278 my($beginning, $ending, $null) = @{$param->{promised_date}};
280 push @search, "(( cust_bill.promised_date >= $beginning AND ".
281 "cust_bill.promised_date < $ending )" .
282 ($null ? ' OR cust_bill.promised_date IS NULL ) ' : ')');
285 #agent virtualization
286 my $curuser = $FS::CurrentUser::CurrentUser;
287 if ( $curuser->username eq 'fs_queue'
288 && $param->{'CurrentUser'} =~ /^(\w+)$/ ) {
290 my $newuser = qsearchs('access_user', {
291 'username' => $username,
297 #warn "$me WARNING: (fs_queue) can't find CurrentUser $username\n";
298 warn "[FS::cust_bill::Search] WARNING: (fs_queue) can't find CurrentUser $username\n";
301 push @search, $curuser->agentnums_sql;
303 join(' AND ', @search );