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