FS/FS/Report/Table.pm

   1 package FS::Report::Table;
   2
   3 use strict;
   4 use base 'FS::Report';
   5 use Time::Local qw( timelocal );
   6 use FS::UID qw( dbh driver_name );
   7 use FS::Report::Table;
   8 use FS::CurrentUser;
   9 use Cache::FileCache;
  10
  11 our $DEBUG = 0; # turning this on will trace all SQL statements, VERY noisy
  12
  13 our $CACHE; # feel free to use this for whatever
  14
  15 FS::UID->install_callback(sub {
  16     $CACHE = Cache::FileCache->new( {
  17       'namespace'   => __PACKAGE__,
  18       'cache_root'  => "$FS::UID::cache_dir/cache.$FS::UID::datasrc",
  19     } );
  20     # reset this on startup (causes problems with database backups, etc.)
  21     $CACHE->remove('tower_pkg_cache_update');
  22 });
  23
  24 =head1 NAME
  25
  26 FS::Report::Table - Tables of report data
  27
  28 =head1 SYNOPSIS
  29
  30 See the more specific report objects, currently only
  31 FS::Report::Table::Monthly and FS::Report::Table::Daily.
  32
  33 =head1 OBSERVABLES
  34
  35 The common interface for an observable named 'foo' is:
  36
  37 $report->foo($startdate, $enddate, $agentnum, %options)
  38
  39 This returns a scalar value for foo, over the period from
  40 $startdate to $enddate, limited to agent $agentnum, subject to
  41 options in %opt.
  42
  43 =over 4
  44
  45 =item signups: The number of customers signed up.  Options are:
  46
  47 - cust_classnum: limit to this customer class
  48 - pkg_classnum: limit to customers with a package of this class.  If this is
  49   an arrayref, it's an ANY match.
  50 - refnum: limit to this advertising source
  51 - indirect: boolean; limit to customers that have a referral_custnum that
  52   matches the advertising source
  53
  54 =cut
  55
  56 sub signups {
  57   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
  58   my @where = ( $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
  59       'cust_main.signupdate')
  60   );
  61   my $join = '';
  62   if ( $opt{'indirect'} ) {
  63     $join = " JOIN cust_main AS referring_cust_main".
  64             " ON (cust_main.referral_custnum = referring_cust_main.custnum)";
  65
  66     if ( $opt{'refnum'} ) {
  67       push @where, "referring_cust_main.refnum = ".$opt{'refnum'};
  68     }
  69   }
  70   elsif ( $opt{'refnum'} ) {
  71     push @where, "refnum = ".$opt{'refnum'};
  72   }
  73
  74   push @where, $self->with_cust_classnum(%opt);
  75   if ( $opt{'pkg_classnum'} ) {
  76     my $classnum = $opt{'pkg_classnum'};
  77     $classnum = [ $classnum ] unless ref $classnum;
  78     @$classnum = grep /^\d+$/, @$classnum;
  79     if (@$classnum) {
  80       my $in = 'IN ('. join(',', @$classnum). ')';
  81       push @where,
  82         "EXISTS(SELECT 1 FROM cust_pkg JOIN part_pkg USING (pkgpart) ".
  83                "WHERE cust_pkg.custnum = cust_main.custnum ".
  84                "AND part_pkg.classnum $in".
  85                ")";
  86     }
  87   }
  88
  89   $self->scalar_sql(
  90     "SELECT COUNT(*) FROM cust_main $join WHERE ".join(' AND ', @where)
  91   );
  92 }
  93
  94 =item invoiced: The total amount charged on all invoices.
  95
  96 =cut
  97
  98 sub invoiced { #invoiced
  99   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 100
 101   my $sql = 'SELECT SUM(cust_bill.charged) FROM cust_bill';
 102   if ( $opt{'setuprecur'} ) {
 103     $sql = 'SELECT SUM('.
 104             FS::cust_bill_pkg->charged_sql($speriod, $eperiod, %opt).
 105            ') FROM cust_bill_pkg JOIN cust_bill USING (invnum)';
 106   }
 107
 108   $self->scalar_sql("
 109       $sql
 110         LEFT JOIN cust_main USING ( custnum )
 111       WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
 112                $self->for_opts(%opt)
 113   );
 114
 115 }
 116
 117 =item netsales: invoiced - netcredits
 118
 119 =cut
 120
 121 sub netsales { #net sales
 122   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 123
 124     $self->invoiced(  $speriod, $eperiod, $agentnum, %opt)
 125   - $self->netcredits($speriod, $eperiod, $agentnum, %opt);
 126 }
 127
 128 =item cashflow: payments - refunds
 129
 130 =cut
 131
 132 sub cashflow {
 133   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 134
 135     $self->payments($speriod, $eperiod, $agentnum, %opt)
 136   - $self->refunds( $speriod, $eperiod, $agentnum, %opt);
 137 }
 138
 139 =item netcashflow: payments - netrefunds
 140
 141 =cut
 142
 143 sub netcashflow {
 144   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 145
 146     $self->receipts(   $speriod, $eperiod, $agentnum, %opt)
 147   - $self->netrefunds( $speriod, $eperiod, $agentnum, %opt);
 148 }
 149
 150 =item payments: The sum of payments received in the period.
 151
 152 =cut
 153
 154 sub payments {
 155   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 156   $self->scalar_sql("
 157     SELECT SUM(paid)
 158       FROM cust_pay
 159         LEFT JOIN cust_main USING ( custnum )
 160       WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
 161                $self->for_opts(%opt)
 162   );
 163 }
 164
 165 =item credits: The sum of credits issued in the period.
 166
 167 =cut
 168
 169 sub credits {
 170   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 171   $self->scalar_sql("
 172     SELECT SUM(cust_credit.amount)
 173       FROM cust_credit
 174         LEFT JOIN cust_main USING ( custnum )
 175       WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
 176                $self->for_opts(%opt)
 177   );
 178 }
 179
 180 =item refunds: The sum of refunds paid in the period.
 181
 182 =cut
 183
 184 sub refunds {
 185   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 186   $self->scalar_sql("
 187     SELECT SUM(refund)
 188       FROM cust_refund
 189         LEFT JOIN cust_main USING ( custnum )
 190       WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
 191                $self->for_opts(%opt)
 192   );
 193 }
 194
 195 =item netcredits: The sum of credit applications to invoices in the period.
 196
 197 =cut
 198
 199 sub netcredits {
 200   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 201
 202   my $sql = 'SELECT SUM(cust_credit_bill.amount) FROM cust_credit_bill';
 203   if ( $opt{'setuprecur'} ) {
 204     $sql = 'SELECT SUM('.
 205             FS::cust_bill_pkg->credited_sql($speriod, $eperiod, %opt).
 206            ') FROM cust_bill_pkg';
 207   }
 208
 209   $self->scalar_sql("
 210     $sql
 211         LEFT JOIN cust_bill USING ( invnum  )
 212         LEFT JOIN cust_main USING ( custnum )
 213       WHERE ". $self->in_time_period_and_agent( $speriod,
 214                                                 $eperiod,
 215                                                 $agentnum,
 216                                                 'cust_bill._date'
 217                                               ).
 218                $self->for_opts(%opt)
 219   );
 220 }
 221
 222 =item receipts: The sum of payment applications to invoices in the period.
 223
 224 =cut
 225
 226 sub receipts { #net payments
 227   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 228
 229   my $sql = 'SELECT SUM(cust_bill_pay.amount) FROM cust_bill_pay';
 230   if ( $opt{'setuprecur'} ) {
 231     $sql = 'SELECT SUM('.
 232             #in practice, but not appearance, paid_sql accepts end before start
 233             FS::cust_bill_pkg->paid_sql($eperiod, $speriod, %opt).
 234            ') FROM cust_bill_pkg';
 235   }
 236
 237   $self->scalar_sql("
 238     $sql
 239         LEFT JOIN cust_bill USING ( invnum  )
 240         LEFT JOIN cust_main USING ( custnum )
 241       WHERE ". $self->in_time_period_and_agent( $speriod,
 242                                                 $eperiod,
 243                                                 $agentnum,
 244                                                 'cust_bill._date'
 245                                               ).
 246                $self->for_opts(%opt)
 247   );
 248 }
 249
 250 =item netrefunds: The sum of refund applications to credits in the period.
 251
 252 =cut
 253
 254 sub netrefunds {
 255   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 256   $self->scalar_sql("
 257     SELECT SUM(cust_credit_refund.amount)
 258       FROM cust_credit_refund
 259         LEFT JOIN cust_credit USING ( crednum  )
 260         LEFT JOIN cust_main   USING ( custnum )
 261       WHERE ". $self->in_time_period_and_agent( $speriod,
 262                                                 $eperiod,
 263                                                 $agentnum,
 264                                                 'cust_credit._date'
 265                                               ).
 266                $self->for_opts(%opt)
 267   );
 268 }
 269
 270 =item discounted: The sum of discounts on invoices in the period.
 271
 272 =cut
 273
 274 sub discounted {
 275   my( $self, $speriod, $eperiod, $agentnum, %opt) = @_;
 276
 277   my $sql = 'SELECT SUM(';
 278   if ($opt{'setuprecur'}) {
 279     # (This isn't exact but it works in most cases.)
 280     # When splitting into setup/recur values,
 281     # if the discount is allowed to apply to setup fees (discount.setup = 'Y')
 282     # then split it between the "setup" and "recurring" rows in proportion to
 283     # the "unitsetup" and "unitrecur" fields of the line item.
 284     $sql .= <<EOF;
 285 CASE
 286   WHEN discount.setup = 'Y'
 287     AND ((COALESCE(cust_bill_pkg.unitsetup,0) > 0)
 288           OR (COALESCE(cust_bill_pkg.unitrecur,0) > 0))
 289   THEN
 290 EOF
 291     if ($opt{'setuprecur'} eq 'setup') {
 292       $sql .= '    (COALESCE(cust_bill_pkg.unitsetup,0)';
 293     } elsif ($opt{'setuprecur'} eq 'recur') {
 294       $sql .= '    (COALESCE(cust_bill_pkg.unitrecur,0)';
 295     } else {
 296       die 'Unrecognized value for setuprecur';
 297     }
 298     $sql .= ' / (COALESCE(cust_bill_pkg.unitsetup,0) + COALESCE(cust_bill_pkg.unitrecur,0)))';
 299     $sql .= " * cust_bill_pkg_discount.amount\n";
 300     # Otherwise, show it all as "recurring"
 301     if ($opt{'setuprecur'} eq 'setup') {
 302       $sql .= "  ELSE 0\n";
 303     } elsif ($opt{'setuprecur'} eq 'recur') {
 304       $sql .= "  ELSE cust_bill_pkg_discount.amount\n";
 305     }
 306     $sql .= "END\n";
 307   } else {
 308     # simple case, no setuprecur
 309     $sql .= "cust_bill_pkg_discount.amount\n";
 310   }
 311   $sql .= <<EOF;
 312 ) FROM cust_bill_pkg_discount
 313   JOIN cust_bill_pkg     USING  ( billpkgnum )
 314   JOIN cust_bill         USING  ( invnum )
 315   JOIN cust_main         USING  ( custnum )
 316 EOF
 317   if ($opt{'setuprecur'}) {
 318     $sql .= <<EOF;
 319   JOIN cust_pkg_discount USING ( pkgdiscountnum )
 320   LEFT JOIN discount          USING ( discountnum )
 321 EOF
 322   }
 323   $self->scalar_sql(
 324     $sql
 325     . 'WHERE '
 326     . $self->in_time_period_and_agent( $speriod,
 327                                        $eperiod,
 328                                        $agentnum,
 329                                        'cust_bill._date'
 330                                       )
 331     . $self->for_opts(%opt)
 332   );
 333 }
 334
 335 =item gross: invoiced + discounted
 336
 337 =cut
 338
 339 sub gross {
 340   my( $self, $speriod, $eperiod, $agentnum, %opt) = @_;
 341     $self->invoiced(   $speriod, $eperiod, $agentnum, %opt)
 342   + $self->discounted( $speriod, $eperiod, $agentnum, %opt);
 343 }
 344
 345 #XXX docs
 346
 347 #these should be auto-generated or $AUTOLOADed or something
 348 sub invoiced_12mo {
 349   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 350   $speriod = $self->_subtract_11mo($speriod);
 351   $self->invoiced($speriod, $eperiod, $agentnum);
 352 }
 353
 354 sub netsales_12mo {
 355   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 356   $speriod = $self->_subtract_11mo($speriod);
 357   $self->netsales($speriod, $eperiod, $agentnum);
 358 }
 359
 360 sub receipts_12mo {
 361   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 362   $speriod = $self->_subtract_11mo($speriod);
 363   $self->receipts($speriod, $eperiod, $agentnum);
 364 }
 365
 366 sub payments_12mo {
 367   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 368   $speriod = $self->_subtract_11mo($speriod);
 369   $self->payments($speriod, $eperiod, $agentnum);
 370 }
 371
 372 sub credits_12mo {
 373   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 374   $speriod = $self->_subtract_11mo($speriod);
 375   $self->credits($speriod, $eperiod, $agentnum);
 376 }
 377
 378 sub netcredits_12mo {
 379   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 380   $speriod = $self->_subtract_11mo($speriod);
 381   $self->netcredits($speriod, $eperiod, $agentnum);
 382 }
 383
 384 sub cashflow_12mo {
 385   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 386   $speriod = $self->_subtract_11mo($speriod);
 387   $self->cashflow($speriod, $eperiod, $agentnum);
 388 }
 389
 390 sub netcashflow_12mo {
 391   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 392   $speriod = $self->_subtract_11mo($speriod);
 393   $self->cashflow($speriod, $eperiod, $agentnum);
 394 }
 395
 396 sub refunds_12mo {
 397   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 398   $speriod = $self->_subtract_11mo($speriod);
 399   $self->refunds($speriod, $eperiod, $agentnum);
 400 }
 401
 402 sub netrefunds_12mo {
 403   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 404   $speriod = $self->_subtract_11mo($speriod);
 405   $self->netrefunds($speriod, $eperiod, $agentnum);
 406 }
 407
 408
 409 #not being too bad with the false laziness
 410 sub _subtract_11mo {
 411   my($self, $time) = @_;
 412   my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
 413   $mon -= 11;
 414   if ( $mon < 0 ) { $mon+=12; $year--; }
 415   timelocal($sec,$min,$hour,$mday,$mon,$year);
 416 }
 417
 418 =item _subtract_months: subtracts the number of months from a given unix date stamp
 419
 420 =cut
 421
 422 sub _subtract_months {
 423   my($self, $number_of_months, $time) = @_;
 424   my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
 425   $mon -= $number_of_months;
 426   if ( $mon < 0 ) { $mon+=12; $year--; }
 427   timelocal($sec,$min,$hour,$mday,$mon,$year);
 428 }
 429
 430 =item cust_pkg_setup_cost: The total setup costs of packages setup in the period
 431
 432 'classnum': limit to this package class.
 433
 434 =cut
 435
 436 sub cust_pkg_setup_cost {
 437   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 438   my $where = '';
 439
 440   if ( $opt{'classnum'} ne '' ) {
 441     my $classnums = $opt{'classnum'};
 442     $classnums = [ $classnums ] if !ref($classnums);
 443     @$classnums = grep /^\d+$/, @$classnums;
 444     $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
 445                                                     ')';
 446   }
 447
 448   $agentnum ||= $opt{'agentnum'};
 449
 450   my $total_sql = " SELECT SUM(part_pkg.setup_cost) ";
 451   $total_sql .= " FROM cust_pkg
 452              LEFT JOIN cust_main USING ( custnum )
 453              LEFT JOIN part_pkg  USING ( pkgpart )
 454                   WHERE pkgnum != 0
 455                   $where
 456                   AND ".$self->in_time_period_and_agent(
 457                     $speriod, $eperiod, $agentnum, 'cust_pkg.setup');
 458   return $self->scalar_sql($total_sql);
 459 }
 460
 461 =item cust_pkg_recur_cust: the total recur costs of packages in the period
 462
 463 'classnum': limit to this package class.
 464
 465 =cut
 466
 467 sub cust_pkg_recur_cost {
 468   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 469   my $where = '';
 470
 471   if ( $opt{'classnum'} ne '' ) {
 472     my $classnums = $opt{'classnum'};
 473     $classnums = [ $classnums ] if !ref($classnums);
 474     @$classnums = grep /^\d+$/, @$classnums;
 475     $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
 476                                                     ')';
 477   }
 478
 479   $agentnum ||= $opt{'agentnum'};
 480   # duplication of in_time_period_and_agent
 481   # because we do it a little differently here
 482   $where .= " AND cust_main.agentnum = $agentnum" if $agentnum;
 483   $where .= " AND ".
 484           $FS::CurrentUser::CurrentUser->agentnums_sql('table' => 'cust_main');
 485
 486   my $total_sql = " SELECT SUM(part_pkg.recur_cost) ";
 487   $total_sql .= " FROM cust_pkg
 488              LEFT JOIN cust_main USING ( custnum )
 489              LEFT JOIN part_pkg  USING ( pkgpart )
 490                   WHERE pkgnum != 0
 491                   $where
 492                   AND cust_pkg.setup < $eperiod
 493                   AND (cust_pkg.cancel > $speriod OR cust_pkg.cancel IS NULL)
 494                   ";
 495   return $self->scalar_sql($total_sql);
 496 }
 497
 498 =item cust_bill_pkg: the total package charges on invoice line items.
 499
 500 'charges': limit the type of charges included (setup, recur, usage, discount, taxes).
 501 Should be a string containing one or more of 'S', 'R', or 'U'; or 'D' or 'T' (discount
 502 and taxes should not be combined with the others.)  If unspecified, defaults to 'SRU'.
 503
 504 'classnum': limit to this package class.
 505
 506 'use_override': for line items generated by an add-on package, use the class
 507 of the add-on rather than the base package.
 508
 509 'average_per_cust_pkg': divide the result by the number of distinct packages.
 510
 511 'distribute': for non-monthly recurring charges, ignore the invoice
 512 date.  Instead, consider the line item's starting/ending dates.  Determine
 513 the fraction of the line item duration that falls within the specified
 514 interval and return that fraction of the recurring charges.  This is
 515 somewhat experimental.
 516
 517 'project': enable if this is a projected period.  This is very experimental.
 518
 519 =cut
 520
 521 sub cust_bill_pkg {
 522   my $self = shift;
 523   my( $speriod, $eperiod, $agentnum, %opt ) = @_;
 524
 525   my %charges = map {$_=>1} split('', $opt{'charges'} || 'SRU');
 526
 527   my $sum = 0;
 528   $sum += $self->cust_bill_pkg_setup(@_) if $charges{S};
 529   $sum += $self->cust_bill_pkg_recur(@_) if $charges{R};
 530   $sum += $self->cust_bill_pkg_detail(@_) if $charges{U};
 531   $sum += $self->cust_bill_pkg_discount(@_) if $charges{D};
 532   $sum += $self->cust_bill_pkg_taxes(@_) if $charges{T};
 533
 534   if ($opt{'average_per_cust_pkg'}) {
 535     my $count = $self->cust_bill_pkg_count_pkgnum(@_);
 536     return '' if $count == 0;
 537     $sum = sprintf('%.2f', $sum / $count);
 538   }
 539   $sum;
 540 }
 541
 542 my $cust_bill_pkg_join = '
 543     LEFT JOIN cust_bill USING ( invnum )
 544     LEFT JOIN cust_main USING ( custnum )
 545     LEFT JOIN cust_pkg USING ( pkgnum )
 546     LEFT JOIN part_pkg USING ( pkgpart )
 547     LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
 548     LEFT JOIN part_fee USING ( feepart )';
 549
 550 sub cust_bill_pkg_setup {
 551   my $self = shift;
 552   my ($speriod, $eperiod, $agentnum, %opt) = @_;
 553   # no projecting setup fees--use real invoices only
 554   # but evaluate this anyway, because the design of projection is that
 555   # if there are somehow real setup fees in the future, we want to count
 556   # them
 557
 558   $agentnum ||= $opt{'agentnum'};
 559
 560   my @where = (
 561     '(pkgnum != 0 OR feepart IS NOT NULL)',
 562     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
 563     $self->with_report_option(%opt),
 564     $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
 565     $self->with_refnum(%opt),
 566     $self->with_cust_classnum(%opt)
 567   );
 568
 569   my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0)
 570   FROM cust_bill_pkg
 571   $cust_bill_pkg_join
 572   WHERE " . join(' AND ', grep $_, @where);
 573
 574   $self->scalar_sql($total_sql);
 575 }
 576
 577 sub _cust_bill_pkg_recurring {
 578   # returns the FROM/WHERE part of the statement to query all recurring
 579   # line items in the period
 580   my $self = shift;
 581   my ($speriod, $eperiod, $agentnum, %opt) = @_;
 582
 583   $agentnum ||= $opt{'agentnum'};
 584   my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
 585
 586   my @where = (
 587     '(pkgnum != 0 OR feepart IS NOT NULL)',
 588     $self->with_report_option(%opt),
 589     $self->with_refnum(%opt),
 590     $self->with_cust_classnum(%opt)
 591   );
 592
 593   my $where_classnum = $self->with_classnum($opt{'classnum'}, $opt{'use_override'});
 594   if ($opt{'project'}) {
 595     $where_classnum =~ s/\bcust_bill_pkg/v_cust_bill_pkg/g;
 596   }
 597   push @where, $where_classnum;
 598
 599   if ( $opt{'distribute'} ) {
 600     $where[0] = 'pkgnum != 0'; # specifically exclude fees
 601     push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 602     push @where,
 603       "$cust_bill_pkg.sdate <  $eperiod",
 604       "$cust_bill_pkg.edate >= $speriod",
 605     ;
 606   }
 607   else {
 608     # we don't want to have to create v_cust_bill
 609     my $_date = $opt{'project'} ? 'v_cust_bill_pkg._date' : 'cust_bill._date';
 610     push @where,
 611       $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date);
 612   }
 613
 614   if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
 615     push @where, "(cust_main.custnum = $1)";
 616   }
 617
 618   return "
 619   FROM $cust_bill_pkg
 620   $cust_bill_pkg_join
 621   WHERE ".join(' AND ', grep $_, @where);
 622
 623 }
 624
 625 =item cust_bill_pkg_recur: the total recur charges
 626
 627 Most arguments as for C<cust_bill_pkg>, plus:
 628
 629 'custnum': limit to this customer
 630
 631 'cost': if true, return total recur costs instead
 632
 633 =cut
 634
 635 sub cust_bill_pkg_recur {
 636   my $self = shift;
 637   my ($speriod, $eperiod, $agentnum, %opt) = @_;
 638
 639   # subtract all usage from the line item regardless of date
 640   my $item_usage;
 641   if ( $opt{'project'} ) {
 642     $item_usage = 'usage'; #already calculated
 643   }
 644   else {
 645     $item_usage = '( SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
 646       FROM cust_bill_pkg_detail
 647       WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
 648   }
 649
 650   my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
 651
 652   my $recur_fraction = '';
 653   if ($opt{'distribute'}) {
 654     # the fraction of edate - sdate that's within [speriod, eperiod]
 655     $recur_fraction = " *
 656       CAST(LEAST($eperiod, $cust_bill_pkg.edate) -
 657        GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) /
 658       ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
 659   }
 660
 661   my $total_sql = $opt{'cost'}
 662     ? "SELECT SUM(part_pkg.recur_cost)"
 663     : "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)";
 664
 665   $total_sql .= $self->_cust_bill_pkg_recurring(@_);
 666
 667   $self->scalar_sql($total_sql);
 668 }
 669
 670 sub cust_bill_pkg_count_pkgnum {
 671   # for ARPU calculation
 672   my $self = shift;
 673   my $total_sql = 'SELECT COUNT(DISTINCT pkgnum) '.
 674     $self->_cust_bill_pkg_recurring(@_);
 675
 676   $self->scalar_sql($total_sql);
 677 }
 678
 679 =item cust_bill_pkg_detail: the total usage charges in detail lines.
 680
 681 Most arguments as for C<cust_bill_pkg>, plus:
 682
 683 'usageclass': limit to this usage class number.
 684
 685 'custnum': limit to this customer
 686
 687 'cost': if true, return total usage costs instead
 688
 689 =cut
 690
 691 sub cust_bill_pkg_detail {
 692   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 693
 694   my @where =
 695     ( "(cust_bill_pkg.pkgnum != 0 OR cust_bill_pkg.feepart IS NOT NULL)" );
 696
 697   $agentnum ||= $opt{'agentnum'};
 698
 699   push @where,
 700     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
 701     $self->with_usageclass($opt{'usageclass'}),
 702     $self->with_report_option(%opt),
 703     $self->with_refnum(%opt),
 704     $self->with_cust_classnum(%opt)
 705     ;
 706
 707   if ( $opt{'distribute'} ) {
 708     # exclude fees
 709     $where[0] = 'cust_bill_pkg.pkgnum != 0';
 710     # and limit according to the usage time, not the billing date
 711     push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
 712       'cust_bill_pkg_detail.startdate'
 713     );
 714   }
 715   else {
 716     push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
 717       'cust_bill._date'
 718     );
 719   }
 720
 721   if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
 722     push @where, "(cust_main.custnum = $1)";
 723   }
 724
 725   my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) ";
 726   my $extra_join = '';
 727   if ($opt{'cost'}) {
 728     $extra_join = "   JOIN cdr USING ( detailnum ) ";
 729     $total_sql  = " SELECT SUM(cdr.rated_cost) ";
 730   }
 731
 732   $total_sql .=
 733     " FROM cust_bill_pkg_detail
 734         LEFT JOIN cust_bill_pkg USING ( billpkgnum )
 735         LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
 736         LEFT JOIN cust_main USING ( custnum )
 737         LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
 738         LEFT JOIN part_pkg USING ( pkgpart )
 739         LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
 740         LEFT JOIN part_fee USING ( feepart )
 741     ".$extra_join.
 742     " WHERE ".join( ' AND ', grep $_, @where );
 743
 744   $self->scalar_sql($total_sql);
 745
 746 }
 747
 748 =item cust_bill_pkg_discount: Discounts issued
 749
 750 Arguments: agentnum, refnum, cust_classnum
 751
 752 =cut
 753
 754 sub cust_bill_pkg_discount {
 755   my $self = shift;
 756   my ($speriod, $eperiod, $agentnum, %opt) = @_;
 757   # apply all the same constraints here as for setup/recur
 758
 759   $agentnum ||= $opt{'agentnum'};
 760
 761   my @where = (
 762     '(pkgnum != 0 OR feepart IS NOT NULL)',
 763     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
 764     $self->with_report_option(%opt),
 765     $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
 766     $self->with_refnum(%opt),
 767     $self->with_cust_classnum(%opt)
 768   );
 769
 770   my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg_discount.amount), 0)
 771   FROM cust_bill_pkg_discount
 772   JOIN cust_bill_pkg USING (billpkgnum)
 773   $cust_bill_pkg_join
 774   WHERE " . join(' AND ', grep $_, @where);
 775
 776   $self->scalar_sql($total_sql);
 777 }
 778
 779 =item cust_bill_pkg_discount_or_waived: Discounts and waived fees issued
 780
 781 Arguments: agentnum, refnum, cust_classnum
 782
 783 =cut
 784
 785 sub cust_bill_pkg_discount_or_waived {
 786
 787   my $self = shift;
 788   my ($speriod, $eperiod, $agentnum, %opt) = @_;
 789
 790   $agentnum ||= $opt{'agentnum'};
 791
 792   my $total_sql = "
 793     SELECT
 794       COALESCE(
 795           SUM(
 796             COALESCE(
 797               cust_bill_pkg_discount.amount,
 798               CAST((  SELECT optionvalue
 799                  FROM part_pkg_option
 800                  WHERE
 801                     part_pkg_option.pkgpart = cust_pkg.pkgpart
 802                     AND optionname = 'setup_fee'
 803               ) AS NUMERIC )
 804             )
 805           ),
 806           0
 807        )
 808     FROM cust_bill_pkg
 809     LEFT JOIN cust_bill_pkg_discount USING (billpkgnum)
 810     LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
 811     LEFT JOIN part_pkg USING (pkgpart)
 812     LEFT JOIN cust_bill USING ( invnum )
 813     LEFT JOIN cust_main ON cust_pkg.custnum = cust_main.custnum
 814     WHERE
 815     (
 816         cust_bill_pkg_discount.billpkgdiscountnum IS NOT NULL
 817         OR (
 818             cust_pkg.setup = cust_bill_pkg.sdate
 819             AND cust_pkg.waive_setup = 'Y'
 820         )
 821     )
 822     AND cust_bill_pkg.pkgpart_override IS NULL
 823   " . join "\n",
 824       map  { " AND ( $_ ) " }
 825       grep { $_ }
 826       $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
 827       $self->with_report_option(%opt),
 828       $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
 829
 830   $self->scalar_sql($total_sql);
 831 }
 832
 833 sub cust_bill_pkg_taxes {
 834   my $self = shift;
 835   my ($speriod, $eperiod, $agentnum, %opt) = @_;
 836
 837   $agentnum ||= $opt{'agentnum'};
 838
 839   my @where = (
 840     '(cust_bill_pkg.pkgnum != 0 OR feepart IS NOT NULL)',
 841     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
 842     $self->with_report_option(%opt),
 843     $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
 844     $self->with_refnum(%opt),
 845     $self->with_cust_classnum(%opt)
 846   );
 847
 848   my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg_tax_location.amount),0)
 849     FROM cust_bill_pkg
 850     $cust_bill_pkg_join
 851     LEFT JOIN cust_bill_pkg_tax_location
 852       ON (cust_bill_pkg.billpkgnum = cust_bill_pkg_tax_location.taxable_billpkgnum)
 853     WHERE " . join(' AND ', grep $_, @where);
 854
 855   $self->scalar_sql($total_sql);
 856 }
 857
 858 #all credits applied to matching pkg line items (ie not taxes)
 859
 860 sub cust_bill_pkg_credits {
 861   my $self = shift;
 862   my ($speriod, $eperiod, $agentnum, %opt) = @_;
 863
 864   $agentnum ||= $opt{'agentnum'};
 865
 866   my @where = (
 867     '(cust_bill_pkg.pkgnum != 0 OR feepart IS NOT NULL)',
 868     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
 869     $self->with_report_option(%opt),
 870     $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
 871     $self->with_refnum(%opt),
 872     $self->with_cust_classnum(%opt)
 873   );
 874
 875   my $total_sql = "SELECT COALESCE(SUM(cust_credit_bill_pkg.amount),0)
 876     FROM cust_bill_pkg
 877     $cust_bill_pkg_join
 878     LEFT JOIN cust_credit_bill_pkg
 879       USING ( billpkgnum )
 880     WHERE " . join(' AND ', grep $_, @where);
 881
 882   $self->scalar_sql($total_sql);
 883 }
 884
 885 ##### package churn report #####
 886
 887 =item active_pkg: The number of packages that were active at the start of
 888 the period. The end date of the period is ignored. Options:
 889
 890 - refnum: Limit to customers with this advertising source.
 891 - classnum: Limit to packages with this class.
 892 - towernum: Limit to packages that have a broadband service with this tower.
 893 - zip: Limit to packages with this service location zip code.
 894
 895 Except for zip, any of these can be an arrayref to allow multiple values for
 896 the field.
 897
 898 =item setup_pkg: The number of packages with setup dates in the period. This
 899 excludes packages created by package changes. Options are as for active_pkg.
 900
 901 =item susp_pkg: The number of packages that were suspended in the period
 902 (and not canceled).  Options are as for active_pkg.
 903
 904 =item unsusp_pkg: The number of packages that were unsuspended in the period.
 905 Options are as for active_pkg.
 906
 907 =item cancel_pkg: The number of packages with cancel dates in the period.
 908 Excludes packages that were canceled to be changed to a new package. Options
 909 are as for active_pkg.
 910
 911 =cut
 912
 913 sub active_pkg {
 914   my $self = shift;
 915   $self->churn_pkg('active', @_);
 916 }
 917
 918 sub setup_pkg {
 919   my $self = shift;
 920   $self->churn_pkg('setup', @_);
 921 }
 922
 923 sub cancel_pkg {
 924   my $self = shift;
 925   $self->churn_pkg('cancel', @_);
 926 }
 927
 928 sub susp_pkg {
 929   my $self = shift;
 930   $self->churn_pkg('susp', @_);
 931 }
 932
 933 sub unsusp_pkg {
 934   my $self = shift;
 935   $self->churn_pkg('unsusp', @_);
 936 }
 937
 938 sub total_revenue_pkg {
 939   my $self = shift;
 940   my $active_revenue = $self->revenue_pkg('active', @_);
 941   my $setup_revenue = $self->revenue_pkg('setup', @_);
 942   my $return = $active_revenue + $setup_revenue;
 943
 944   return $return;
 945 }
 946
 947 sub total_revenue_diff {
 948   my $self = shift;
 949
 950   my @current_month = @_;
 951   my @previous_month = @current_month;
 952
 953   $previous_month[0] = $self->_subtract_months(1,$current_month[0]);
 954   $previous_month[1] = $self->_subtract_months(1,$current_month[1]);
 955
 956   my $previous_revenue = $self->revenue_pkg('active', @previous_month) + $self->revenue_pkg('setup', @previous_month);
 957   my $current_revenue  = $self->revenue_pkg('active', @current_month) + $self->revenue_pkg('setup', @current_month);
 958
 959   my $return = $current_revenue - $previous_revenue;
 960
 961   return $return;
 962 }
 963
 964 sub revenue_pkg {
 965   my $self = shift;
 966   my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_;
 967   my $totalrevenue;
 968
 969   my ($from, @where) =
 970     FS::h_cust_pkg->churn_fromwhere_sql( $status, $speriod, $eperiod);
 971
 972   push @where, $self->pkg_where(%opt, 'agentnum' => $agentnum);
 973
 974   my $sql;
 975
 976   if ($status eq "active") {
 977     $sql = "SELECT DISTINCT ON (revenue.pkgnum) revenue.pkgnum AS pkgnum, revenue.recur AS revenue
 978       FROM $from
 979       JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
 980       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 981       JOIN h_cust_bill_pkg AS revenue ON (cust_pkg.pkgnum = revenue.pkgnum AND cust_pkg.history_date < $speriod )
 982     ";
 983   }
 984   elsif ($status eq "setup") {
 985     $sql = "SELECT DISTINCT ON (revenue.pkgnum) revenue.pkgnum AS pkgnum, revenue.setup AS revenue
 986       FROM $from
 987       JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
 988       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 989       JOIN h_cust_bill_pkg AS revenue ON (cust_pkg.pkgnum = revenue.pkgnum AND
 990       ( cust_pkg.setup > $speriod AND cust_pkg.setup < $eperiod) )
 991     ";
 992   }
 993
 994   $sql .= ' WHERE '.join(' AND ', @where)
 995     if scalar(@where);
 996
 997   $sql .= "ORDER BY revenue.pkgnum ASC, revenue.history_date DESC";
 998
 999   my $revenue_sql = "SELECT sum(rev.revenue) AS total_revenue FROM ( $sql ) AS rev";
1000
1001   $self->scalar_sql($revenue_sql);
1002 }
1003
1004 sub churn_pkg {
1005   my $self = shift;
1006   my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_;
1007   my ($from, @where) =
1008     FS::h_cust_pkg->churn_fromwhere_sql( $status, $speriod, $eperiod);
1009
1010   push @where, $self->pkg_where(%opt, 'agentnum' => $agentnum);
1011
1012   my $sql = "SELECT COUNT(*) FROM $from
1013     JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
1014     JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)";
1015   $sql .= ' WHERE '.join(' AND ', @where)
1016     if scalar(@where);
1017
1018   $self->scalar_sql($sql);
1019 }
1020
1021 sub pkg_where {
1022   my $self = shift;
1023   my %opt = @_;
1024   my @where = (
1025     "part_pkg.freq != '0'",
1026     $self->with_refnum(%opt),
1027     $self->with_towernum(%opt),
1028     $self->with_zip(%opt),
1029   );
1030   if ($opt{agentnum} =~ /^(\d+)$/) {
1031     push @where, "cust_main.agentnum = $1";
1032   }
1033   if ($opt{classnum}) {
1034     my $classnum = $opt{classnum};
1035     $classnum = [ $classnum ] if !ref($classnum);
1036     @$classnum = grep /^\d+$/, @$classnum;
1037     my $in = 'IN ('. join(',', @$classnum). ')';
1038     push @where, "COALESCE(part_pkg.classnum, 0) $in" if scalar @$classnum;
1039   }
1040   @where;
1041 }
1042
1043 ##### end of package churn report stuff #####
1044
1045 ##### customer churn report #####
1046
1047 =item active_cust: The number of customers who had any active recurring
1048 packages at the start of the period. The end date is ignored, agentnum is
1049 mandatory, and no other parameters are accepted.
1050
1051 =item started_cust: The number of customers who had no active packages at
1052 the start of the period, but had active packages at the end. Like
1053 active_cust, agentnum is mandatory and no other parameters are accepted.
1054
1055 =item suspended_cust: The number of customers who had active packages at
1056 the start of the period, and at the end had no active packages but some
1057 suspended packages. Note that this does not necessarily mean that their
1058 packages were suspended during the period.
1059
1060 =item resumed_cust: The inverse of suspended_cust: the number of customers
1061 who had suspended packages and no active packages at the start of the
1062 period, and active packages at the end.
1063
1064 =item cancelled_cust: The number of customers who had active packages
1065 at the start of the period, and only cancelled packages at the end.
1066
1067 =cut
1068
1069 sub active_cust {
1070   my $self = shift;
1071   $self->churn_cust(@_)->{active};
1072 }
1073 sub started_cust {
1074   my $self = shift;
1075   $self->churn_cust(@_)->{started};
1076 }
1077 sub suspended_cust {
1078   my $self = shift;
1079   $self->churn_cust(@_)->{suspended};
1080 }
1081 sub resumed_cust {
1082   my $self = shift;
1083   $self->churn_cust(@_)->{resumed};
1084 }
1085 sub cancelled_cust {
1086   my $self = shift;
1087   $self->churn_cust(@_)->{cancelled};
1088 }
1089
1090 sub churn_cust {
1091   my $self = shift;
1092   my ( $speriod ) = @_;
1093
1094   # run one query for each interval
1095   return $self->{_interval}{$speriod} ||= $self->calculate_churn_cust(@_);
1096 }
1097
1098 sub calculate_churn_cust {
1099   my $self = shift;
1100   my ($speriod, $eperiod, $agentnum, %opt) = @_;
1101
1102   my $churn_sql = FS::cust_main::Status->churn_sql($speriod, $eperiod);
1103   my $where = '';
1104   $where = " WHERE cust_main.agentnum = $agentnum " if $agentnum;
1105   my $cust_sql =
1106     "SELECT churn.* ".
1107     "FROM cust_main JOIN ($churn_sql) AS churn USING (custnum)".
1108     $where;
1109
1110   # query to count the ones with certain status combinations
1111   my $total_sql = "
1112     SELECT SUM((s_active > 0)::int)                   as active,
1113            SUM((s_active = 0 and e_active > 0)::int)  as started,
1114            SUM((s_active > 0 and e_active = 0 and e_suspended > 0)::int)
1115                                                       as suspended,
1116            SUM((s_active = 0 and s_suspended > 0 and e_active > 0)::int)
1117                                                       as resumed,
1118            SUM((s_active > 0 and e_active = 0 and e_suspended = 0)::int)
1119                                                       as cancelled
1120     FROM ($cust_sql) AS x
1121   ";
1122
1123   my $sth = dbh->prepare($total_sql);
1124   $sth->execute or die "failed to execute churn query: " . $sth->errstr;
1125
1126   $self->{_interval}{$speriod} = $sth->fetchrow_hashref;
1127 }
1128
1129 sub in_time_period_and_agent {
1130   my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
1131   my $col = @_ ? shift() : '_date';
1132
1133   my $sql = "$col >= $speriod AND $col < $eperiod";
1134
1135   #agent selection
1136   $sql .= " AND cust_main.agentnum = $agentnum"
1137     if $agentnum;
1138
1139   #agent virtualization
1140   $sql .= ' AND '.
1141           $FS::CurrentUser::CurrentUser->agentnums_sql( 'table'=>'cust_main' );
1142
1143   $sql;
1144 }
1145
1146 sub for_opts {
1147     my ( $self, %opt ) = @_;
1148     my $sql = '';
1149     if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
1150       $sql .= " and custnum = $1 ";
1151     }
1152     if ( $opt{'refnum'} ) {
1153       my $refnum = $opt{'refnum'};
1154       $refnum = [ $refnum ] if !ref($refnum);
1155       my $in = join(',', grep /^\d+$/, @$refnum);
1156       $sql .= " and refnum IN ($in)" if length $in;
1157     }
1158     if ( my $where = $self->with_cust_classnum(%opt) ) {
1159       $sql .= " and $where";
1160     }
1161
1162     $sql;
1163 }
1164
1165 sub with_classnum {
1166   my ($self, $classnum, $use_override) = @_;
1167   return '' if $classnum eq '';
1168
1169   $classnum = [ $classnum ] if !ref($classnum);
1170   @$classnum = grep /^\d+$/, @$classnum;
1171   return '' if !@$classnum;
1172   my $in = 'IN ('. join(',', @$classnum). ')';
1173
1174   if ( $use_override ) {
1175     # then include packages if their base package is in the set and they are
1176     # not overridden,
1177     # or if they are overridden and their override package is in the set,
1178     # or fees if they are in the set
1179     return "(
1180          ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL AND pkgpart_override IS NULL )
1181       OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )
1182       OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL )
1183     )";
1184   } else {
1185     # include packages if their base package is in the set,
1186     # or fees if they are in the set
1187     return "(
1188          ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL )
1189       OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL )
1190     )";
1191   }
1192 }
1193
1194 sub with_usageclass {
1195   my $self = shift;
1196   my ($classnum, $use_override) = @_;
1197   return '' unless $classnum =~ /^\d+$/;
1198   my $comparison;
1199   if ( $classnum == 0 ) {
1200     $comparison = 'IS NULL';
1201   }
1202   else {
1203     $comparison = "= $classnum";
1204   }
1205   return "cust_bill_pkg_detail.classnum $comparison";
1206 }
1207
1208 sub with_report_option {
1209   my ($self, %opt) = @_;
1210   # %opt can contain:
1211   # - report_optionnum: a comma-separated list of numbers.  Zero means to
1212   #   include packages with _no_ report classes.
1213   # - not_report_optionnum: a comma-separated list.  Packages that have
1214   #   any of these report options will be excluded from the result.
1215   #   Zero does nothing.
1216   # - use_override: also matches line items that are add-ons to a package
1217   #   matching the report class.
1218   # - all_report_options: returns only packages that have ALL of the
1219   #   report classes listed in $num.  Otherwise, will return packages that
1220   #   have ANY of those classes.
1221
1222   my @num = ref($opt{'report_optionnum'})
1223                   ? @{ $opt{'report_optionnum'} }
1224                   : split(/\s*,\s*/, $opt{'report_optionnum'});
1225   my @not_num = ref($opt{'not_report_optionnum'})
1226                       ? @{ $opt{'not_report_optionnum'} }
1227                       : split(/\s*,\s*/, $opt{'not_report_optionnum'});
1228   my $null;
1229   $null = 1 if ( grep {$_ == 0} @num );
1230   @num = grep {$_ > 0} @num;
1231   @not_num = grep {$_ > 0} @not_num;
1232
1233   # brute force
1234   my $table = $opt{'use_override'} ? 'override' : 'part_pkg';
1235   my $op = ' OR ';
1236   if ( $opt{'all_report_options'} ) {
1237     if ( @num and $null ) {
1238       return 'false'; # mutually exclusive criteria, so just bail out
1239     }
1240     $op = ' AND ';
1241   }
1242   my @where_num = map {
1243     "EXISTS(SELECT 1 FROM part_pkg_option ".
1244     "WHERE optionname = 'report_option_$_' ".
1245     "AND part_pkg_option.pkgpart = $table.pkgpart)"
1246   } @num;
1247   if ( $null ) {
1248     push @where_num, "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
1249                      "WHERE optionname LIKE 'report_option_%' ".
1250                      "AND part_pkg_option.pkgpart = $table.pkgpart)";
1251   }
1252   my @where_not_num = map {
1253     "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
1254     "WHERE optionname = 'report_option_$_' ".
1255     "AND part_pkg_option.pkgpart = $table.pkgpart)"
1256   } @not_num;
1257
1258   my @where;
1259   if (@where_num) {
1260     push @where, '( '.join($op, @where_num).' )';
1261   }
1262   if (@where_not_num) {
1263     push @where, '( '.join(' AND ', @where_not_num).' )';
1264   }
1265
1266   return @where;
1267   # this messes up totals
1268   #if ( $opt{'use_override'} ) {
1269   #  # then also allow the non-override package to match
1270   #  delete $opt{'use_override'};
1271   #  $comparison = "( $comparison OR " . $self->with_report_option(%opt) . ")";
1272   #}
1273
1274 }
1275
1276 sub with_refnum {
1277   my ($self, %opt) = @_;
1278   if ( $opt{'refnum'} ) {
1279     my $refnum = $opt{'refnum'};
1280     $refnum = [ $refnum ] if !ref($refnum);
1281     my $in = join(',', grep /^\d+$/, @$refnum);
1282     return "cust_main.refnum IN ($in)" if length $in;
1283   }
1284   return;
1285 }
1286
1287 sub with_towernum {
1288   my ($self, %opt) = @_;
1289   if ( $opt{'towernum'} ) {
1290     my $towernum = $opt{'towernum'};
1291     $towernum = [ $towernum ] if !ref($towernum);
1292     my $in = join(',', grep /^\d+$/, @$towernum);
1293     return unless length($in); # if no towers are specified, don't restrict
1294
1295     # materialize/cache the set of pkgnums that, as of the last
1296     # svc_broadband history record, had a certain towernum
1297     # (because otherwise this is painfully slow)
1298     $self->_init_tower_pkg_cache;
1299
1300     return "EXISTS(
1301             SELECT 1 FROM tower_pkg_cache
1302               WHERE towernum IN($in)
1303               AND cust_pkg.pkgnum = tower_pkg_cache.pkgnum
1304             )";
1305   }
1306   return;
1307 }
1308
1309 sub with_zip {
1310   my ($self, %opt) = @_;
1311   if (length($opt{'zip'})) {
1312     return "(SELECT zip FROM cust_location
1313              WHERE cust_location.locationnum = cust_pkg.locationnum
1314             ) = " . dbh->quote($opt{'zip'});
1315   }
1316   return;
1317 }
1318
1319 sub with_cust_classnum {
1320   my ($self, %opt) = @_;
1321   if ( $opt{'cust_classnum'} ) {
1322     my $classnums = $opt{'cust_classnum'};
1323     $classnums = [ $classnums ] if !ref($classnums);
1324     @$classnums = grep /^\d+$/, @$classnums;
1325     return 'cust_main.classnum in('. join(',',@$classnums) .')'
1326       if @$classnums;
1327   }
1328   return;
1329 }
1330
1331
1332 sub scalar_sql {
1333   my( $self, $sql ) = ( shift, shift );
1334   my $sth = dbh->prepare($sql) or die dbh->errstr;
1335   warn "FS::Report::Table\n$sql\n" if $DEBUG;
1336   $sth->execute
1337     or die "Unexpected error executing statement $sql: ". $sth->errstr;
1338   $sth->fetchrow_arrayref->[0] || 0;
1339 }
1340
1341 =back
1342
1343 =head1 METHODS
1344
1345 =over 4
1346
1347 =item init_projection
1348
1349 Sets up for future projection of all observables on the report.  Currently
1350 this is limited to 'cust_bill_pkg'.
1351
1352 =cut
1353
1354 sub init_projection {
1355   # this is weird special case stuff--some redesign may be needed
1356   # to use it for anything else
1357   my $self = shift;
1358
1359   if ( driver_name ne 'Pg' ) {
1360     # also database-specific for now
1361     die "projection reports not supported on this platform";
1362   }
1363
1364   my %items = map {$_ => 1} @{ $self->{items} };
1365   if ($items{'cust_bill_pkg'}) {
1366     my $dbh = dbh;
1367     # v_ for 'virtual'
1368     my @sql = (
1369       # could use TEMPORARY TABLE but we're already transaction-protected
1370       'DROP TABLE IF EXISTS v_cust_bill_pkg',
1371       'CREATE TABLE v_cust_bill_pkg ' .
1372        '(LIKE cust_bill_pkg,
1373           usage numeric(10,2), _date integer, expire integer)',
1374       # XXX this should be smart enough to take only the ones with
1375       # sdate/edate overlapping the ROI, for performance
1376       "INSERT INTO v_cust_bill_pkg (
1377         SELECT cust_bill_pkg.*,
1378           (SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
1379           FROM cust_bill_pkg_detail
1380           WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum),
1381           cust_bill._date,
1382           cust_pkg.expire
1383         FROM cust_bill_pkg $cust_bill_pkg_join
1384       )",
1385     );
1386     foreach my $sql (@sql) {
1387       warn "[init_projection] $sql\n" if $DEBUG;
1388       $dbh->do($sql) or die $dbh->errstr;
1389     }
1390   }
1391 }
1392
1393 =item extend_projection START END
1394
1395 Generates data for the next period of projection.  This will be called
1396 for sequential periods where the END of one equals the START of the next
1397 (with no gaps).
1398
1399 =cut
1400
1401 sub extend_projection {
1402   my $self = shift;
1403   my ($speriod, $eperiod) = @_;
1404   my %items = map {$_ => 1} @{ $self->{items} };
1405   if ($items{'cust_bill_pkg'}) {
1406     # What we do here:
1407     # Find all line items that end after the start of the period (and have
1408     # recurring fees, and don't expire before they end).  Choose the latest
1409     # one for each package.  If it ends before the end of the period, copy
1410     # it forward by one billing period.
1411     # Repeat this until the latest line item for each package no longer ends
1412     # within the period.  This is certain to happen in finitely many
1413     # iterations as long as freq > 0.
1414     # - Pg only, obviously.
1415     # - Gives bad results if freq_override is used.
1416     my @fields = ( FS::cust_bill_pkg->fields, qw( usage _date expire ) );
1417     my $insert_fields = join(',', @fields);
1418     my $add_freq = sub { # emulate FS::part_pkg::add_freq
1419       my $field = shift;
1420       "EXTRACT( EPOCH FROM TO_TIMESTAMP($field) + (CASE WHEN freq ~ E'\\\\D' ".
1421       "THEN freq ELSE freq || 'mon' END)::INTERVAL) AS $field";
1422     };
1423     foreach (@fields) {
1424       if ($_ eq 'edate') {
1425         $_ = $add_freq->('edate');
1426       }
1427       elsif ($_ eq 'sdate') {
1428         $_ = 'edate AS sdate'
1429       }
1430       elsif ($_ eq 'setup') {
1431         $_ = '0 AS setup' #because recurring only
1432       }
1433       elsif ($_ eq '_date') {
1434         $_ = $add_freq->('_date');
1435       }
1436     }
1437     my $select_fields = join(',', @fields);
1438     my $dbh = dbh;
1439     my $sql =
1440     # Subquery here because we need to DISTINCT the whole set, select the
1441     # latest charge per pkgnum, and _then_ check edate < $eperiod
1442     # and edate < expire.
1443       "INSERT INTO v_cust_bill_pkg ($insert_fields)
1444         SELECT $select_fields FROM (
1445           SELECT DISTINCT ON (pkgnum) * FROM v_cust_bill_pkg
1446             WHERE edate >= $speriod
1447               AND recur > 0
1448               AND freq IS NOT NULL
1449               AND freq != '0'
1450             ORDER BY pkgnum, edate DESC
1451           ) AS v1
1452           WHERE edate < $eperiod AND (edate < expire OR expire IS NULL)";
1453     my $rows;
1454     do {
1455       warn "[extend_projection] $sql\n" if $DEBUG;
1456       $rows = $dbh->do($sql) or die $dbh->errstr;
1457       warn "[extend_projection] $rows rows\n" if $DEBUG;
1458     } until $rows == 0;
1459   }
1460 }
1461
1462 =item _init_tower_pkg_cache
1463
1464 Internal method: creates a temporary table relating pkgnums to towernums.
1465 A (pkgnum, towernum) record indicates that this package once had a
1466 svc_broadband service which, as of its last insert or replace_new history
1467 record, had a sectornum associated with that towernum.
1468
1469 This is expensive, so it won't be done more than once an hour. Historical
1470 data about package churn shouldn't be changing in realtime anyway.
1471
1472 =cut
1473
1474 sub _init_tower_pkg_cache {
1475   my $self = shift;
1476   my $dbh = dbh;
1477
1478   my $current = $CACHE->get('tower_pkg_cache_update');
1479   return if $current;
1480
1481   # XXX or should this be in the schema?
1482   my $sql = "DROP TABLE IF EXISTS tower_pkg_cache";
1483   $dbh->do($sql) or die $dbh->errstr;
1484   $sql = "CREATE TABLE tower_pkg_cache (towernum int, pkgnum int)";
1485   $dbh->do($sql) or die $dbh->errstr;
1486
1487   # assumptions:
1488   # sectornums never get reused, or move from one tower to another
1489   # all service history is intact
1490   # svcnums never get reused (this would be bad)
1491   # pkgnums NEVER get reused (this would be extremely bad)
1492   $sql = "INSERT INTO tower_pkg_cache (
1493     SELECT COALESCE(towernum,0), pkgnum
1494     FROM ( SELECT DISTINCT pkgnum, svcnum FROM h_cust_svc ) AS pkgnum_svcnum
1495     LEFT JOIN (
1496       SELECT DISTINCT ON(svcnum) svcnum, sectornum
1497         FROM h_svc_broadband
1498         WHERE (history_action = 'replace_new'
1499                OR history_action = 'replace_old')
1500         ORDER BY svcnum ASC, history_date DESC
1501     ) AS svcnum_sectornum USING (svcnum)
1502     LEFT JOIN tower_sector USING (sectornum)
1503   )";
1504   $dbh->do($sql) or die $dbh->errstr;
1505
1506   $CACHE->set('tower_pkg_cache_update', 1, 3600);
1507
1508 };
1509
1510 =head1 BUGS
1511
1512 Documentation.
1513
1514 =head1 SEE ALSO
1515
1516 L<FS::Report::Table::Monthly>, reports in the web interface.
1517
1518 =cut
1519
1520 1;