FS/FS/cust_bill/Search.pm

   1 package FS::cust_bill::Search;
   2
   3 use strict;
   4 use FS::CurrentUser;
   5 use FS::UI::Web;
   6 use FS::Record qw( qsearchs dbh );
   7 use FS::cust_main;
   8 use FS::access_user;
   9 use FS::Conf;
  10
  11 =item search HASHREF
  12
  13 (Class method)
  14
  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:
  18
  19 =over 4
  20
  21 =item newest_percust
  22
  23 =back
  24
  25 =cut
  26
  27 sub search {
  28   my( $class, $params ) = @_;
  29
  30   my( $count_query, $count_addl ) = ( '', '' );
  31
  32   #some false laziness w/cust_bill::re_X
  33
  34   $count_query = "SELECT COUNT(DISTINCT cust_bill.custnum), 'N/A', 'N/A'"
  35     if $params->{'newest_percust'};
  36
  37   my $extra_sql = FS::cust_bill->search_sql_where( $params );
  38   $extra_sql = "WHERE $extra_sql" if $extra_sql;
  39
  40   my $join_cust_main = FS::UI::Web::join_cust_main('cust_bill');
  41
  42   # get discounted, credited, and paid amounts here, for use in report
  43   #
  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
  48   # keys, etc.
  49   #
  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
  52   # coalesced to zero.
  53   my $join = "$join_cust_main
  54   JOIN (
  55     SELECT COALESCE(SUM(cust_bill_pkg_discount.amount), 0) AS discounted,
  56       invnum
  57       FROM cust_bill_pkg_discount
  58         JOIN cust_bill_pkg USING (billpkgnum)
  59         RIGHT JOIN cust_bill USING (invnum)
  60       GROUP BY invnum
  61     ) AS _discount USING (invnum)
  62   JOIN (
  63     SELECT COALESCE(SUM(cust_credit_bill.amount), 0) AS credited, invnum
  64       FROM cust_credit_bill
  65         RIGHT JOIN cust_bill USING (invnum)
  66       GROUP BY invnum
  67     ) AS _credit USING (invnum)
  68   JOIN (
  69     SELECT COALESCE(SUM(cust_bill_pay.amount), 0) AS paid, invnum
  70       FROM cust_bill_pay
  71         RIGHT JOIN cust_bill USING (invnum)
  72       GROUP BY invnum
  73     ) AS _pay USING (invnum)
  74   ";
  75
  76   unless ( $count_query ) {
  77
  78     my $money = (FS::Conf->new->config('money_char') || '$') . '%.2f';
  79
  80     $count_query = 'SELECT COUNT(*), '. join(', ',
  81                      map "SUM($_)",
  82                          ( 'charged + discounted',
  83                            'discounted',
  84                            'credited',
  85                            'charged - credited',
  86                            'charged - credited - paid',
  87                          )
  88                    );
  89     $count_addl = [ "$money sales (gross)",
  90                     "&minus; $money discounted",
  91                     "&minus; $money credited",
  92                     "= $money sales (net)",
  93                     "$money outstanding balance",
  94                   ];
  95   }
  96   $count_query .=  " FROM cust_bill $join $extra_sql";
  97
  98   #$sql_query =
  99   +{
 100     'table'     => 'cust_bill',
 101     'addl_from' => $join,
 102     'hashref'   => {},
 103     'select'    => join(', ',
 104                      'cust_bill.*',
 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',
 109                      'discounted',
 110                      'credited',
 111                      '(charged - credited) as net',
 112                      '(charged - credited - paid) as owed',
 113                    ),
 114     'extra_sql' => $extra_sql,
 115     'order_by'  => 'ORDER BY '. ( $params->{'order_by'} || 'cust_bill._date' ),
 116
 117     'count_query' => $count_query,
 118     'count_addl'  => $count_addl,
 119   };
 120
 121 }
 122
 123 =item search_sql_where HASHREF
 124
 125 Class method which returns an SQL WHERE fragment to search for parameters
 126 specified in HASHREF.  Valid parameters are
 127
 128 =over 4
 129
 130 =item _date
 131
 132 List reference of start date, end date, as UNIX timestamps.
 133
 134 =item invnum_min
 135
 136 =item invnum_max
 137
 138 =item agentnum
 139
 140 =item cust_status
 141
 142 =item cust_classnum
 143
 144 List reference
 145
 146 =item charged
 147
 148 List reference of charged limits (exclusive).
 149
 150 =item owed
 151
 152 List reference of charged limits (exclusive).
 153
 154 =item open
 155
 156 flag, return open invoices only
 157
 158 =item net
 159
 160 flag, return net invoices only
 161
 162 =item days
 163
 164 =item newest_percust
 165
 166 =item custnum
 167
 168 Return only invoices belonging to that customer.
 169
 170 =item cust_classnum
 171
 172 Limit to that customer class (single value or arrayref).
 173
 174 =item refnum
 175
 176 Limit to customers with that advertising source.
 177
 178 =back
 179
 180 Note: validates all passed-in data; i.e. safe to use with unchecked CGI params.
 181
 182 =cut
 183
 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";
 189   #}
 190
 191   #some false laziness w/cust_bill::re_X
 192
 193   my @search = ();
 194
 195   #agentnum
 196   if ( $param->{'agentnum'} =~ /^(\d+)$/ ) {
 197     push @search, "cust_main.agentnum = $1";
 198   }
 199
 200   #refnum
 201   if ( $param->{'refnum'} =~ /^(\d+)$/ ) {
 202     push @search, "cust_main.refnum = $1";
 203   }
 204
 205   #custnum
 206   if ( $param->{'custnum'} =~ /^(\d+)$/ ) {
 207     push @search, "cust_bill.custnum = $1";
 208   }
 209
 210   #cust_status
 211   if ( $param->{'cust_status'} =~ /^([a-z]+)$/ ) {
 212     push @search, FS::cust_main->cust_status_sql . " = '$1' ";
 213   }
 214
 215   #customer classnum (false laziness w/ cust_main/Search.pm)
 216   if ( $param->{'cust_classnum'} ) {
 217
 218     my @classnum = ref( $param->{'cust_classnum'} )
 219                      ? @{ $param->{'cust_classnum'} }
 220                      :  ( $param->{'cust_classnum'} );
 221
 222     @classnum = grep /^(\d*)$/, @classnum;
 223
 224     if ( @classnum ) {
 225       push @search, 'COALESCE(cust_main.classnum, 0) IN ('.join(',', @classnum).')';
 226     }
 227
 228   }
 229
 230   #_date
 231   if ( $param->{_date} ) {
 232     my($beginning, $ending) = @{$param->{_date}};
 233
 234     push @search, "cust_bill._date >= $beginning",
 235                   "cust_bill._date <  $ending";
 236   }
 237
 238   #invnum
 239   if ( $param->{'invnum_min'} =~ /^\s*(\d+)\s*$/ ) {
 240     push @search, "cust_bill.invnum >= $1";
 241   }
 242   if ( $param->{'invnum_max'} =~ /^\s*(\d+)\s*$/ ) {
 243     push @search, "cust_bill.invnum <= $1";
 244   }
 245
 246   # these are from parse_lt_gt, and should already be sanitized
 247   #charged
 248   if ( $param->{charged} ) {
 249     my @charged = ref($param->{charged})
 250                     ? @{ $param->{charged} }
 251                     : ($param->{charged});
 252
 253     push @search, map { s/^charged/cust_bill.charged/; $_; }
 254                       @charged;
 255   }
 256
 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)';
 260
 261   #owed
 262   if ( $param->{owed} ) {
 263     my @owed = ref($param->{owed})
 264                  ? @{ $param->{owed} }
 265                  : ($param->{owed});
 266     push @search, map { s/^owed/$owed_sql/ } @owed;
 267   }
 268
 269   #open/net flags
 270   push @search, "0 != $owed_sql"
 271     if $param->{'open'};
 272   push @search, "0 != $net_sql"
 273     if $param->{'net'};
 274
 275   #days
 276   push @search, "cust_bill._date < ". (time-86400*$param->{'days'})
 277     if $param->{'days'};
 278
 279   #newest_percust
 280   if ( $param->{'newest_percust'} ) {
 281
 282     #$distinct = 'DISTINCT ON ( cust_bill.custnum )';
 283     #$orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC';
 284
 285     my @newest_where = map { my $x = $_;
 286                              $x =~ s/\bcust_bill\./newest_cust_bill./g;
 287                              $x;
 288                            }
 289                            grep ! /^cust_main./, @search;
 290     my $newest_where = scalar(@newest_where)
 291                          ? ' AND '. join(' AND ', @newest_where)
 292                          : '';
 293
 294
 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
 298           $newest_where
 299     )";
 300
 301   }
 302
 303   #promised_date - also has an option to accept nulls
 304   if ( $param->{promised_date} ) {
 305     my($beginning, $ending, $null) = @{$param->{promised_date}};
 306
 307     push @search, "(( cust_bill.promised_date >= $beginning AND ".
 308                     "cust_bill.promised_date <  $ending )" .
 309                     ($null ? ' OR cust_bill.promised_date IS NULL ) ' : ')');
 310   }
 311
 312   #agent virtualization
 313   my $curuser = $FS::CurrentUser::CurrentUser;
 314   if ( $curuser->username eq 'fs_queue'
 315        && $param->{'CurrentUser'} =~ /^(\w+)$/ ) {
 316     my $username = $1;
 317     my $newuser = qsearchs('access_user', {
 318       'username' => $username,
 319       'disabled' => '',
 320     } );
 321     if ( $newuser ) {
 322       $curuser = $newuser;
 323     } else {
 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";
 326     }
 327   }
 328   push @search, $curuser->agentnums_sql;
 329
 330   join(' AND ', @search );
 331
 332 }
 333
 334 1;
 335