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 advertising source.
180 Note: validates all passed-in data; i.e. safe to use with unchecked CGI params.
184 sub search_sql_where {
185 my($class, $param) = @_;
186 #if ( $cust_bill::DEBUG ) {
187 # warn "$me search_sql_where called with params: \n".
188 # join("\n", map { " $_: ". $param->{$_} } keys %$param ). "\n";
191 #some false laziness w/cust_bill::re_X
196 if ( $param->{'agentnum'} =~ /^(\d+)$/ ) {
197 push @search, "cust_main.agentnum = $1";
201 if ( $param->{'refnum'} =~ /^(\d+)$/ ) {
202 push @search, "cust_main.refnum = $1";
206 if ( $param->{'custnum'} =~ /^(\d+)$/ ) {
207 push @search, "cust_bill.custnum = $1";
211 if ( $param->{'cust_status'} =~ /^([a-z]+)$/ ) {
212 push @search, FS::cust_main->cust_status_sql . " = '$1' ";
215 #customer classnum (false laziness w/ cust_main/Search.pm)
216 if ( $param->{'cust_classnum'} ) {
218 my @classnum = ref( $param->{'cust_classnum'} )
219 ? @{ $param->{'cust_classnum'} }
220 : ( $param->{'cust_classnum'} );
222 @classnum = grep /^(\d*)$/, @classnum;
225 push @search, 'COALESCE(cust_main.classnum, 0) IN ('.join(',', @classnum).')';
231 if ( $param->{_date} ) {
232 my($beginning, $ending) = @{$param->{_date}};
234 push @search, "cust_bill._date >= $beginning",
235 "cust_bill._date < $ending";
239 if ( $param->{'invnum_min'} =~ /^\s*(\d+)\s*$/ ) {
240 push @search, "cust_bill.invnum >= $1";
242 if ( $param->{'invnum_max'} =~ /^\s*(\d+)\s*$/ ) {
243 push @search, "cust_bill.invnum <= $1";
246 # these are from parse_lt_gt, and should already be sanitized
248 if ( $param->{charged} ) {
249 my @charged = ref($param->{charged})
250 ? @{ $param->{charged} }
251 : ($param->{charged});
253 push @search, map { s/^charged/cust_bill.charged/; $_; }
257 #my $owed_sql = FS::cust_bill->owed_sql;
258 my $owed_sql = '(cust_bill.charged - credited - paid)';
259 my $net_sql = '(cust_bill.charged - credited)';
262 if ( $param->{owed} ) {
263 my @owed = ref($param->{owed})
264 ? @{ $param->{owed} }
266 push @search, map { s/^owed/$owed_sql/ } @owed;
270 push @search, "0 != $owed_sql"
272 push @search, "0 != $net_sql"
276 push @search, "cust_bill._date < ". (time-86400*$param->{'days'})
280 if ( $param->{'newest_percust'} ) {
282 #$distinct = 'DISTINCT ON ( cust_bill.custnum )';
283 #$orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC';
285 my @newest_where = map { my $x = $_;
286 $x =~ s/\bcust_bill\./newest_cust_bill./g;
289 grep ! /^cust_main./, @search;
290 my $newest_where = scalar(@newest_where)
291 ? ' AND '. join(' AND ', @newest_where)
295 push @search, "cust_bill._date = (
296 SELECT(MAX(newest_cust_bill._date)) FROM cust_bill AS newest_cust_bill
297 WHERE newest_cust_bill.custnum = cust_bill.custnum
303 #promised_date - also has an option to accept nulls
304 if ( $param->{promised_date} ) {
305 my($beginning, $ending, $null) = @{$param->{promised_date}};
307 push @search, "(( cust_bill.promised_date >= $beginning AND ".
308 "cust_bill.promised_date < $ending )" .
309 ($null ? ' OR cust_bill.promised_date IS NULL ) ' : ')');
312 #agent virtualization
313 my $curuser = $FS::CurrentUser::CurrentUser;
314 if ( $curuser->username eq 'fs_queue'
315 && $param->{'CurrentUser'} =~ /^(\w+)$/ ) {
317 my $newuser = qsearchs('access_user', {
318 'username' => $username,
324 #warn "$me WARNING: (fs_queue) can't find CurrentUser $username\n";
325 warn "[FS::cust_bill::Search] WARNING: (fs_queue) can't find CurrentUser $username\n";
328 push @search, $curuser->agentnums_sql;
330 join(' AND ', @search );