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 advertising source.
193 Note: validates all passed-in data; i.e. safe to use with unchecked CGI params.
197 sub search_sql_where {
198 my($class, $param) = @_;
199 #if ( $cust_bill::DEBUG ) {
200 # warn "$me search_sql_where called with params: \n".
201 # join("\n", map { " $_: ". $param->{$_} } keys %$param ). "\n";
204 #some false laziness w/cust_bill::re_X
209 if ( $param->{'agentnum'} =~ /^(\d+)$/ ) {
210 push @search, "cust_main.agentnum = $1";
214 if ( $param->{'refnum'} =~ /^(\d+)$/ ) {
215 push @search, "cust_main.refnum = $1";
219 if ( $param->{'custnum'} =~ /^(\d+)$/ ) {
220 push @search, "cust_bill.custnum = $1";
224 if ( $param->{'cust_status'} =~ /^([a-z]+)$/ ) {
225 push @search, FS::cust_main->cust_status_sql . " = '$1' ";
228 #customer classnum (false laziness w/ cust_main/Search.pm)
229 if ( $param->{'cust_classnum'} ) {
231 my @classnum = ref( $param->{'cust_classnum'} )
232 ? @{ $param->{'cust_classnum'} }
233 : ( $param->{'cust_classnum'} );
235 @classnum = grep /^(\d+)$/, @classnum;
238 push @search, 'COALESCE(cust_main.classnum, 0) IN ('.join(',', @classnum).')';
244 if ( $param->{_date} ) {
245 my($beginning, $ending) = @{$param->{_date}};
247 push @search, "cust_bill._date >= $beginning",
248 "cust_bill._date < $ending";
252 if ( $param->{'invnum_min'} =~ /^\s*(\d+)\s*$/ ) {
253 push @search, "cust_bill.invnum >= $1";
255 if ( $param->{'invnum_max'} =~ /^\s*(\d+)\s*$/ ) {
256 push @search, "cust_bill.invnum <= $1";
259 # these are from parse_lt_gt, and should already be sanitized
261 if ( $param->{charged} ) {
262 my @charged = ref($param->{charged})
263 ? @{ $param->{charged} }
264 : ($param->{charged});
266 push @search, map { s/^charged/cust_bill.charged/; $_; }
270 #my $owed_sql = FS::cust_bill->owed_sql;
271 my $owed_sql = '(cust_bill.charged - credited - paid)';
272 my $net_sql = '(cust_bill.charged - credited)';
275 if ( $param->{owed} ) {
276 my @owed = ref($param->{owed})
277 ? @{ $param->{owed} }
279 push @search, map { s/^owed/$owed_sql/; $_ } @owed;
283 push @search, "0 != $owed_sql"
285 push @search, "0 != $net_sql"
289 push @search, "cust_bill._date < ". (time-86400*$param->{'days'})
293 if ( $param->{'newest_percust'} ) {
295 #$distinct = 'DISTINCT ON ( cust_bill.custnum )';
296 #$orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC';
298 my @newest_where = map { my $x = $_;
299 $x =~ s/\bcust_bill\./newest_cust_bill./g;
302 grep ! /^cust_main./, @search;
303 my $newest_where = scalar(@newest_where)
304 ? ' AND '. join(' AND ', @newest_where)
308 push @search, "cust_bill._date = (
309 SELECT(MAX(newest_cust_bill._date)) FROM cust_bill AS newest_cust_bill
310 WHERE newest_cust_bill.custnum = cust_bill.custnum
316 #promised_date - also has an option to accept nulls
317 if ( $param->{promised_date} ) {
318 my($beginning, $ending, $null) = @{$param->{promised_date}};
320 push @search, "(( cust_bill.promised_date >= $beginning AND ".
321 "cust_bill.promised_date < $ending )" .
322 ($null ? ' OR cust_bill.promised_date IS NULL ) ' : ')');
325 #agent virtualization
326 my $curuser = $FS::CurrentUser::CurrentUser;
327 if ( $curuser->username eq 'fs_queue'
328 && $param->{'CurrentUser'} =~ /^(\w+)$/ ) {
330 my $newuser = qsearchs('access_user', {
331 'username' => $username,
337 #warn "$me WARNING: (fs_queue) can't find CurrentUser $username\n";
338 warn "[FS::cust_bill::Search] WARNING: (fs_queue) can't find CurrentUser $username\n";
341 push @search, $curuser->agentnums_sql;
343 join(' AND ', @search );