1 package FS::cust_bill::Search;
6 use FS::Record qw( qsearchs dbh );
10 use charnames ':full';
16 Returns a qsearch hash expression to search for parameters specified in
17 HASHREF. In addition to all parameters accepted by search_sql_where, the
18 following additional parameters valid:
22 =item newest_percust - only show the most recent invoice for each customer
24 =item invoiced - show the invoiced amount (excluding discounts) instead of gross sales
31 my( $class, $params ) = @_;
36 #some false laziness w/cust_bill::re_X
38 $count_query = "SELECT COUNT(DISTINCT cust_bill.custnum), 'N/A', 'N/A'"
39 if $params->{'newest_percust'};
41 my $extra_sql = FS::cust_bill->search_sql_where( $params );
42 $extra_sql = "WHERE $extra_sql" if $extra_sql;
44 my $join_cust_main = FS::UI::Web::join_cust_main('cust_bill');
46 # get discounted, credited, and paid amounts here, for use in report
48 # Testing shows that this is by far the most efficient way to do the
49 # joins. In particular it's almost 100x faster to join to an aggregate
50 # query than to put the subquery in a select expression. It also makes
51 # it more convenient to do arithmetic between columns, use them as sort
54 # Each ends with a RIGHT JOIN cust_bill so that it includes all invnums,
55 # even if they have no discounts/credits/payments; the total amount is then
57 my $join = "$join_cust_main
59 SELECT COALESCE(SUM(cust_bill_pkg_discount.amount), 0) AS discounted,
61 FROM cust_bill_pkg_discount
62 JOIN cust_bill_pkg USING (billpkgnum)
63 RIGHT JOIN cust_bill USING (invnum)
65 ) AS _discount USING (invnum)
67 SELECT COALESCE(SUM(cust_credit_bill.amount), 0) AS credited, invnum
69 RIGHT JOIN cust_bill USING (invnum)
71 ) AS _credit USING (invnum)
73 SELECT COALESCE(SUM(cust_bill_pay.amount), 0) AS paid, invnum
75 RIGHT JOIN cust_bill USING (invnum)
77 ) AS _pay USING (invnum)
80 unless ( $count_query ) {
82 my $money = (FS::Conf->new->config('money_char') || '$') . '%.2f';
84 my @sums = ( 'credited', # credits
85 'charged - credited', # net sales
86 'charged - credited - paid', # balance due
89 @count_addl = ( "\N{MINUS SIGN} $money credited",
91 "$money outstanding balance",
94 if ( $params->{'invoiced'} ) {
96 unshift @sums, 'charged';
97 unshift @count_addl, "$money invoiced";
101 unshift @sums, 'charged + discounted', 'discounted';
102 unshift @count_addl, "$money gross sales",
103 "\N{MINUS SIGN} $money discounted";
107 $count_query = 'SELECT COUNT(*), '. join(', ', map "SUM($_)", @sums);
109 $count_query .= " FROM cust_bill $join $extra_sql";
113 'table' => 'cust_bill',
114 'addl_from' => $join,
116 'select' => join(', ',
118 #( map "cust_main.$_", qw(custnum last first company) ),
119 'cust_main.custnum as cust_main_custnum',
120 FS::UI::Web::cust_sql_fields(),
121 '(charged + discounted) as gross',
124 '(charged - credited) as net',
125 '(charged - credited - paid) as owed',
127 'extra_sql' => $extra_sql,
128 'order_by' => 'ORDER BY '. ( $params->{'order_by'} || 'cust_bill._date' ),
130 'count_query' => $count_query,
131 'count_addl' => \@count_addl,
136 =item search_sql_where HASHREF
138 Class method which returns an SQL WHERE fragment to search for parameters
139 specified in HASHREF. Valid parameters are
145 List reference of start date, end date, as UNIX timestamps.
161 List reference of charged limits (exclusive).
165 List reference of charged limits (exclusive).
169 flag, return open invoices only
173 flag, return net invoices only
181 Return only invoices belonging to that customer.
185 Limit to that customer class (single value or arrayref).
189 Limit to customers with that payment method (single value or arrayref).
193 Limit to customers with that advertising source.
197 Note: validates all passed-in data; i.e. safe to use with unchecked CGI params.
201 sub search_sql_where {
202 my($class, $param) = @_;
203 #if ( $cust_bill::DEBUG ) {
204 # warn "$me search_sql_where called with params: \n".
205 # join("\n", map { " $_: ". $param->{$_} } keys %$param ). "\n";
208 #some false laziness w/cust_bill::re_X
213 if ( $param->{'agentnum'} =~ /^(\d+)$/ ) {
214 push @search, "cust_main.agentnum = $1";
218 if ( $param->{'refnum'} =~ /^(\d+)$/ ) {
219 push @search, "cust_main.refnum = $1";
223 if ( $param->{'custnum'} =~ /^(\d+)$/ ) {
224 push @search, "cust_bill.custnum = $1";
228 if ( $param->{'cust_status'} =~ /^([a-z]+)$/ ) {
229 push @search, FS::cust_main->cust_status_sql . " = '$1' ";
232 #customer classnum (false laziness w/ cust_main/Search.pm)
233 if ( $param->{'cust_classnum'} ) {
235 my @classnum = ref( $param->{'cust_classnum'} )
236 ? @{ $param->{'cust_classnum'} }
237 : ( $param->{'cust_classnum'} );
239 @classnum = grep /^(\d+)$/, @classnum;
242 push @search, 'COALESCE(cust_main.classnum, 0) IN ('.join(',', @classnum).')';
248 if ( $param->{payby} ) {
249 my $payby = $param->{payby};
250 $payby = [ $payby ] unless ref $payby;
251 my $payby_in = join(',', map {dbh->quote($_)} @$payby);
252 push @search, "cust_main.payby IN($payby_in)" if length($payby_in);
256 if ( $param->{_date} ) {
257 my($beginning, $ending) = @{$param->{_date}};
259 push @search, "cust_bill._date >= $beginning",
260 "cust_bill._date < $ending";
264 if ( $param->{'invnum_min'} =~ /^\s*(\d+)\s*$/ ) {
265 push @search, "cust_bill.invnum >= $1";
267 if ( $param->{'invnum_max'} =~ /^\s*(\d+)\s*$/ ) {
268 push @search, "cust_bill.invnum <= $1";
271 # these are from parse_lt_gt, and should already be sanitized
273 if ( $param->{charged} ) {
274 my @charged = ref($param->{charged})
275 ? @{ $param->{charged} }
276 : ($param->{charged});
278 push @search, map { s/^charged/cust_bill.charged/; $_; }
282 #my $owed_sql = FS::cust_bill->owed_sql;
283 my $owed_sql = '(cust_bill.charged - credited - paid)';
284 my $net_sql = '(cust_bill.charged - credited)';
287 if ( $param->{owed} ) {
288 my @owed = ref($param->{owed})
289 ? @{ $param->{owed} }
291 push @search, map { s/^owed/$owed_sql/; $_ } @owed;
295 push @search, "0 != $owed_sql"
297 push @search, "0 != $net_sql"
301 push @search, "cust_bill._date < ". (time-86400*$param->{'days'})
305 if ( $param->{'newest_percust'} ) {
307 #$distinct = 'DISTINCT ON ( cust_bill.custnum )';
308 #$orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC';
310 my @newest_where = map { my $x = $_;
311 $x =~ s/\bcust_bill\./newest_cust_bill./g;
314 grep ! /^cust_main./, @search;
315 my $newest_where = scalar(@newest_where)
316 ? ' AND '. join(' AND ', @newest_where)
320 push @search, "cust_bill._date = (
321 SELECT(MAX(newest_cust_bill._date)) FROM cust_bill AS newest_cust_bill
322 WHERE newest_cust_bill.custnum = cust_bill.custnum
328 #promised_date - also has an option to accept nulls
329 if ( $param->{promised_date} ) {
330 my($beginning, $ending, $null) = @{$param->{promised_date}};
332 push @search, "(( cust_bill.promised_date >= $beginning AND ".
333 "cust_bill.promised_date < $ending )" .
334 ($null ? ' OR cust_bill.promised_date IS NULL ) ' : ')');
337 #agent virtualization
338 my $curuser = $FS::CurrentUser::CurrentUser;
339 if ( $curuser->username eq 'fs_queue'
340 && $param->{'CurrentUser'} =~ /^(\w+)$/ ) {
342 my $newuser = qsearchs('access_user', {
343 'username' => $username,
349 #warn "$me WARNING: (fs_queue) can't find CurrentUser $username\n";
350 warn "[FS::cust_bill::Search] WARNING: (fs_queue) can't find CurrentUser $username\n";
353 push @search, $curuser->agentnums_sql;
355 join(' AND ', @search );