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 refnum
 188
 189 Limit to customers with that advertising source.
 190
 191 =back
 192
 193 Note: validates all passed-in data; i.e. safe to use with unchecked CGI params.
 194
 195 =cut
 196
 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";
 202   #}
 203
 204   #some false laziness w/cust_bill::re_X
 205
 206   my @search = ();
 207
 208   #agentnum
 209   if ( $param->{'agentnum'} =~ /^(\d+)$/ ) {
 210     push @search, "cust_main.agentnum = $1";
 211   }
 212
 213   #refnum
 214   if ( $param->{'refnum'} =~ /^(\d+)$/ ) {
 215     push @search, "cust_main.refnum = $1";
 216   }
 217
 218   #custnum
 219   if ( $param->{'custnum'} =~ /^(\d+)$/ ) {
 220     push @search, "cust_bill.custnum = $1";
 221   }
 222
 223   #cust_status
 224   if ( $param->{'cust_status'} =~ /^([a-z]+)$/ ) {
 225     push @search, FS::cust_main->cust_status_sql . " = '$1' ";
 226   }
 227
 228   #customer classnum (false laziness w/ cust_main/Search.pm)
 229   if ( $param->{'cust_classnum'} ) {
 230
 231     my @classnum = ref( $param->{'cust_classnum'} )
 232                      ? @{ $param->{'cust_classnum'} }
 233                      :  ( $param->{'cust_classnum'} );
 234
 235     @classnum = grep /^(\d*)$/, @classnum;
 236
 237     if ( @classnum ) {
 238       push @search, 'COALESCE(cust_main.classnum, 0) IN ('.join(',', @classnum).')';
 239     }
 240
 241   }
 242
 243   #_date
 244   if ( $param->{_date} ) {
 245     my($beginning, $ending) = @{$param->{_date}};
 246
 247     push @search, "cust_bill._date >= $beginning",
 248                   "cust_bill._date <  $ending";
 249   }
 250
 251   #invnum
 252   if ( $param->{'invnum_min'} =~ /^\s*(\d+)\s*$/ ) {
 253     push @search, "cust_bill.invnum >= $1";
 254   }
 255   if ( $param->{'invnum_max'} =~ /^\s*(\d+)\s*$/ ) {
 256     push @search, "cust_bill.invnum <= $1";
 257   }
 258
 259   # these are from parse_lt_gt, and should already be sanitized
 260   #charged
 261   if ( $param->{charged} ) {
 262     my @charged = ref($param->{charged})
 263                     ? @{ $param->{charged} }
 264                     : ($param->{charged});
 265
 266     push @search, map { s/^charged/cust_bill.charged/; $_; }
 267                       @charged;
 268   }
 269
 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)';
 273
 274   #owed
 275   if ( $param->{owed} ) {
 276     my @owed = ref($param->{owed})
 277                  ? @{ $param->{owed} }
 278                  : ($param->{owed});
 279     push @search, map { s/^owed/$owed_sql/ } @owed;
 280   }
 281
 282   #open/net flags
 283   push @search, "0 != $owed_sql"
 284     if $param->{'open'};
 285   push @search, "0 != $net_sql"
 286     if $param->{'net'};
 287
 288   #days
 289   push @search, "cust_bill._date < ". (time-86400*$param->{'days'})
 290     if $param->{'days'};
 291
 292   #newest_percust
 293   if ( $param->{'newest_percust'} ) {
 294
 295     #$distinct = 'DISTINCT ON ( cust_bill.custnum )';
 296     #$orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC';
 297
 298     my @newest_where = map { my $x = $_;
 299                              $x =~ s/\bcust_bill\./newest_cust_bill./g;
 300                              $x;
 301                            }
 302                            grep ! /^cust_main./, @search;
 303     my $newest_where = scalar(@newest_where)
 304                          ? ' AND '. join(' AND ', @newest_where)
 305                          : '';
 306
 307
 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
 311           $newest_where
 312     )";
 313
 314   }
 315
 316   #promised_date - also has an option to accept nulls
 317   if ( $param->{promised_date} ) {
 318     my($beginning, $ending, $null) = @{$param->{promised_date}};
 319
 320     push @search, "(( cust_bill.promised_date >= $beginning AND ".
 321                     "cust_bill.promised_date <  $ending )" .
 322                     ($null ? ' OR cust_bill.promised_date IS NULL ) ' : ')');
 323   }
 324
 325   #agent virtualization
 326   my $curuser = $FS::CurrentUser::CurrentUser;
 327   if ( $curuser->username eq 'fs_queue'
 328        && $param->{'CurrentUser'} =~ /^(\w+)$/ ) {
 329     my $username = $1;
 330     my $newuser = qsearchs('access_user', {
 331       'username' => $username,
 332       'disabled' => '',
 333     } );
 334     if ( $newuser ) {
 335       $curuser = $newuser;
 336     } else {
 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";
 339     }
 340   }
 341   push @search, $curuser->agentnums_sql;
 342
 343   join(' AND ', @search );
 344
 345 }
 346
 347 1;
 348