1 package FS::cust_bill::Search;
6 use FS::Record qw( qsearchs dbh );
15 Returns a qsearch hash expression to search for parameters specified in
16 HASHREF. In addition to all parameters accepted by search_sql_where, the
17 following additional parameters valid:
28 my( $class, $params ) = @_;
30 my( $count_query, $count_addl ) = ( '', '' );
32 #some false laziness w/cust_bill::re_X
34 $count_query = "SELECT COUNT(DISTINCT cust_bill.custnum), 'N/A', 'N/A'"
35 if $params->{'newest_percust'};
37 my $extra_sql = FS::cust_bill->search_sql_where( $params );
38 $extra_sql = "WHERE $extra_sql" if $extra_sql;
40 my $join_cust_main = FS::UI::Web::join_cust_main('cust_bill');
42 # get discounted, credited, and paid amounts here, for use in report
44 # Testing shows that this is by far the most efficient way to do the
45 # joins. In particular it's almost 100x faster to join to an aggregate
46 # query than to put the subquery in a select expression. It also makes
47 # it more convenient to do arithmetic between columns, use them as sort
50 # Each ends with a RIGHT JOIN cust_bill so that it includes all invnums,
51 # even if they have no discounts/credits/payments; the total amount is then
53 my $join = "$join_cust_main
55 SELECT COALESCE(SUM(cust_bill_pkg_discount.amount), 0) AS discounted,
57 FROM cust_bill_pkg_discount
58 JOIN cust_bill_pkg USING (billpkgnum)
59 RIGHT JOIN cust_bill USING (invnum)
61 ) AS _discount USING (invnum)
63 SELECT COALESCE(SUM(cust_credit_bill.amount), 0) AS credited, invnum
65 RIGHT JOIN cust_bill USING (invnum)
67 ) AS _credit USING (invnum)
69 SELECT COALESCE(SUM(cust_bill_pay.amount), 0) AS paid, invnum
71 RIGHT JOIN cust_bill USING (invnum)
73 ) AS _pay USING (invnum)
76 unless ( $count_query ) {
78 my $money = (FS::Conf->new->config('money_char') || '$') . '%.2f';
80 $count_query = 'SELECT COUNT(*), '. join(', ',
82 ( 'charged + discounted',
86 'charged - credited - paid',
89 $count_addl = [ "$money sales (gross)",
90 "− $money discounted",
91 "− $money credited",
92 "= $money sales (net)",
93 "$money outstanding balance",
96 $count_query .= " FROM cust_bill $join $extra_sql";
100 'table' => 'cust_bill',
101 'addl_from' => $join,
103 'select' => join(', ',
105 #( map "cust_main.$_", qw(custnum last first company) ),
106 'cust_main.custnum as cust_main_custnum',
107 FS::UI::Web::cust_sql_fields(),
108 '(charged + discounted) as gross',
111 '(charged - credited) as net',
112 '(charged - credited - paid) as owed',
114 'extra_sql' => $extra_sql,
115 'order_by' => 'ORDER BY '. ( $params->{'order_by'} || 'cust_bill._date' ),
117 'count_query' => $count_query,
118 'count_addl' => $count_addl,
123 =item search_sql_where HASHREF
125 Class method which returns an SQL WHERE fragment to search for parameters
126 specified in HASHREF. Valid parameters are
132 List reference of start date, end date, as UNIX timestamps.
148 List reference of charged limits (exclusive).
152 List reference of charged limits (exclusive).
156 flag, return open invoices only
160 flag, return net invoices only
168 Return only invoices belonging to that customer.
172 Limit to that customer class (single value or arrayref).
176 Limit to customers with that payment method (single value or arrayref).
180 Limit to customers with that advertising source.
184 Note: validates all passed-in data; i.e. safe to use with unchecked CGI params.
188 sub search_sql_where {
189 my($class, $param) = @_;
190 #if ( $cust_bill::DEBUG ) {
191 # warn "$me search_sql_where called with params: \n".
192 # join("\n", map { " $_: ". $param->{$_} } keys %$param ). "\n";
195 #some false laziness w/cust_bill::re_X
200 if ( $param->{'agentnum'} =~ /^(\d+)$/ ) {
201 push @search, "cust_main.agentnum = $1";
205 if ( $param->{'refnum'} =~ /^(\d+)$/ ) {
206 push @search, "cust_main.refnum = $1";
210 if ( $param->{'custnum'} =~ /^(\d+)$/ ) {
211 push @search, "cust_bill.custnum = $1";
215 if ( $param->{'cust_status'} =~ /^([a-z]+)$/ ) {
216 push @search, FS::cust_main->cust_status_sql . " = '$1' ";
219 #customer classnum (false laziness w/ cust_main/Search.pm)
220 if ( $param->{'cust_classnum'} ) {
222 my @classnum = ref( $param->{'cust_classnum'} )
223 ? @{ $param->{'cust_classnum'} }
224 : ( $param->{'cust_classnum'} );
226 @classnum = grep /^(\d*)$/, @classnum;
229 push @search, 'COALESCE(cust_main.classnum, 0) IN ('.join(',', @classnum).')';
235 if ( $param->{payby} ) {
236 my $payby = $param->{payby};
237 $payby = [ $payby ] unless ref $payby;
238 my $payby_in = join(',', map {dbh->quote($_)} @$payby);
239 push @search, "cust_main.payby IN($payby_in)" if length($payby_in);
243 if ( $param->{_date} ) {
244 my($beginning, $ending) = @{$param->{_date}};
246 push @search, "cust_bill._date >= $beginning",
247 "cust_bill._date < $ending";
251 if ( $param->{'invnum_min'} =~ /^\s*(\d+)\s*$/ ) {
252 push @search, "cust_bill.invnum >= $1";
254 if ( $param->{'invnum_max'} =~ /^\s*(\d+)\s*$/ ) {
255 push @search, "cust_bill.invnum <= $1";
258 # these are from parse_lt_gt, and should already be sanitized
260 if ( $param->{charged} ) {
261 my @charged = ref($param->{charged})
262 ? @{ $param->{charged} }
263 : ($param->{charged});
265 push @search, map { s/^charged/cust_bill.charged/; $_; }
269 #my $owed_sql = FS::cust_bill->owed_sql;
270 my $owed_sql = '(cust_bill.charged - credited - paid)';
271 my $net_sql = '(cust_bill.charged - credited)';
274 if ( $param->{owed} ) {
275 my @owed = ref($param->{owed})
276 ? @{ $param->{owed} }
278 push @search, map { s/^owed/$owed_sql/ } @owed;
282 push @search, "0 != $owed_sql"
284 push @search, "0 != $net_sql"
288 push @search, "cust_bill._date < ". (time-86400*$param->{'days'})
292 if ( $param->{'newest_percust'} ) {
294 #$distinct = 'DISTINCT ON ( cust_bill.custnum )';
295 #$orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC';
297 my @newest_where = map { my $x = $_;
298 $x =~ s/\bcust_bill\./newest_cust_bill./g;
301 grep ! /^cust_main./, @search;
302 my $newest_where = scalar(@newest_where)
303 ? ' AND '. join(' AND ', @newest_where)
307 push @search, "cust_bill._date = (
308 SELECT(MAX(newest_cust_bill._date)) FROM cust_bill AS newest_cust_bill
309 WHERE newest_cust_bill.custnum = cust_bill.custnum
315 #promised_date - also has an option to accept nulls
316 if ( $param->{promised_date} ) {
317 my($beginning, $ending, $null) = @{$param->{promised_date}};
319 push @search, "(( cust_bill.promised_date >= $beginning AND ".
320 "cust_bill.promised_date < $ending )" .
321 ($null ? ' OR cust_bill.promised_date IS NULL ) ' : ')');
324 #agent virtualization
325 my $curuser = $FS::CurrentUser::CurrentUser;
326 if ( $curuser->username eq 'fs_queue'
327 && $param->{'CurrentUser'} =~ /^(\w+)$/ ) {
329 my $newuser = qsearchs('access_user', {
330 'username' => $username,
336 #warn "$me WARNING: (fs_queue) can't find CurrentUser $username\n";
337 warn "[FS::cust_bill::Search] WARNING: (fs_queue) can't find CurrentUser $username\n";
340 push @search, $curuser->agentnums_sql;
342 join(' AND ', @search );