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 advertising source.
137 Note: validates all passed-in data; i.e. safe to use with unchecked CGI params.
141 sub search_sql_where {
142 my($class, $param) = @_;
143 #if ( $cust_bill::DEBUG ) {
144 # warn "$me search_sql_where called with params: \n".
145 # join("\n", map { " $_: ". $param->{$_} } keys %$param ). "\n";
148 #some false laziness w/cust_bill::re_X
153 if ( $param->{'agentnum'} =~ /^(\d+)$/ ) {
154 push @search, "cust_main.agentnum = $1";
158 if ( $param->{'refnum'} =~ /^(\d+)$/ ) {
159 push @search, "cust_main.refnum = $1";
163 if ( $param->{'custnum'} =~ /^(\d+)$/ ) {
164 push @search, "cust_bill.custnum = $1";
168 if ( $param->{'cust_status'} =~ /^([a-z]+)$/ ) {
169 push @search, FS::cust_main->cust_status_sql . " = '$1' ";
172 #customer classnum (false laziness w/ cust_main/Search.pm)
173 if ( $param->{'cust_classnum'} ) {
175 my @classnum = ref( $param->{'cust_classnum'} )
176 ? @{ $param->{'cust_classnum'} }
177 : ( $param->{'cust_classnum'} );
179 @classnum = grep /^(\d*)$/, @classnum;
182 push @search, '( '. join(' OR ', map {
183 $_ ? "cust_main.classnum = $_"
184 : "cust_main.classnum IS NULL"
194 if ( $param->{_date} ) {
195 my($beginning, $ending) = @{$param->{_date}};
197 push @search, "cust_bill._date >= $beginning",
198 "cust_bill._date < $ending";
202 if ( $param->{'invnum_min'} =~ /^\s*(\d+)\s*$/ ) {
203 push @search, "cust_bill.invnum >= $1";
205 if ( $param->{'invnum_max'} =~ /^\s*(\d+)\s*$/ ) {
206 push @search, "cust_bill.invnum <= $1";
210 if ( $param->{charged} ) {
211 my @charged = ref($param->{charged})
212 ? @{ $param->{charged} }
213 : ($param->{charged});
215 push @search, map { s/^charged/cust_bill.charged/; $_; }
219 my $owed_sql = FS::cust_bill->owed_sql;
222 if ( $param->{owed} ) {
223 my @owed = ref($param->{owed})
224 ? @{ $param->{owed} }
226 push @search, map { s/^owed/$owed_sql/; $_; }
231 push @search, "0 != $owed_sql"
233 push @search, '0 != '. FS::cust_bill->net_sql
237 push @search, "cust_bill._date < ". (time-86400*$param->{'days'})
241 if ( $param->{'newest_percust'} ) {
243 #$distinct = 'DISTINCT ON ( cust_bill.custnum )';
244 #$orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC';
246 my @newest_where = map { my $x = $_;
247 $x =~ s/\bcust_bill\./newest_cust_bill./g;
250 grep ! /^cust_main./, @search;
251 my $newest_where = scalar(@newest_where)
252 ? ' AND '. join(' AND ', @newest_where)
256 push @search, "cust_bill._date = (
257 SELECT(MAX(newest_cust_bill._date)) FROM cust_bill AS newest_cust_bill
258 WHERE newest_cust_bill.custnum = cust_bill.custnum
264 #promised_date - also has an option to accept nulls
265 if ( $param->{promised_date} ) {
266 my($beginning, $ending, $null) = @{$param->{promised_date}};
268 push @search, "(( cust_bill.promised_date >= $beginning AND ".
269 "cust_bill.promised_date < $ending )" .
270 ($null ? ' OR cust_bill.promised_date IS NULL ) ' : ')');
273 #agent virtualization
274 my $curuser = $FS::CurrentUser::CurrentUser;
275 if ( $curuser->username eq 'fs_queue'
276 && $param->{'CurrentUser'} =~ /^(\w+)$/ ) {
278 my $newuser = qsearchs('access_user', {
279 'username' => $username,
285 #warn "$me WARNING: (fs_queue) can't find CurrentUser $username\n";
286 warn "[FS::cust_bill::Search] WARNING: (fs_queue) can't find CurrentUser $username\n";
289 push @search, $curuser->agentnums_sql;
291 join(' AND ', @search );