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 package has changed and has not reached next due date it will not be in h_cust_bill.
 977 ## this causes problems with future months, needed to use change_pkgnum instead.
 978
 979   if ($status eq "active") {
 980     $sql = "SELECT DISTINCT ON (revenue.pkgnum) revenue.pkgnum AS pkgnum, revenue.recur AS revenue
 981       FROM $from
 982       JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
 983       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 984       JOIN h_cust_bill_pkg AS revenue ON ((cust_pkg.pkgnum = revenue.pkgnum OR cust_pkg.change_pkgnum = revenue.pkgnum) AND cust_pkg.history_date < $speriod )
 985     ";
 986   }
 987   elsif ($status eq "setup") {
 988     $sql = "SELECT DISTINCT ON (revenue.pkgnum) revenue.pkgnum AS pkgnum, revenue.setup AS revenue
 989       FROM $from
 990       JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
 991       JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
 992       JOIN h_cust_bill_pkg AS revenue ON (cust_pkg.pkgnum = revenue.pkgnum AND
 993       ( cust_pkg.setup > $speriod AND cust_pkg.setup < $eperiod) )
 994     ";
 995   }
 996
 997   $sql .= ' WHERE '.join(' AND ', @where)
 998     if scalar(@where);
 999
1000   $sql .= "ORDER BY revenue.pkgnum ASC, revenue.history_date DESC";
1001
1002   my $revenue_sql = "SELECT sum(rev.revenue) AS total_revenue FROM ( $sql ) AS rev";
1003
1004   $self->scalar_sql($revenue_sql);
1005 }
1006
1007 sub churn_pkg {
1008   my $self = shift;
1009   my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_;
1010   my ($from, @where) =
1011     FS::h_cust_pkg->churn_fromwhere_sql( $status, $speriod, $eperiod);
1012
1013   push @where, $self->pkg_where(%opt, 'agentnum' => $agentnum);
1014
1015   my $sql = "SELECT COUNT(*) FROM $from
1016     JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
1017     JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)";
1018   $sql .= ' WHERE '.join(' AND ', @where)
1019     if scalar(@where);
1020
1021   $self->scalar_sql($sql);
1022 }
1023
1024 sub pkg_where {
1025   my $self = shift;
1026   my %opt = @_;
1027   my @where = (
1028     "part_pkg.freq != '0'",
1029     $self->with_refnum(%opt),
1030     $self->with_towernum(%opt),
1031     $self->with_zip(%opt),
1032   );
1033   if ($opt{agentnum} =~ /^(\d+)$/) {
1034     push @where, "cust_main.agentnum = $1";
1035   }
1036   if ($opt{classnum}) {
1037     my $classnum = $opt{classnum};
1038     $classnum = [ $classnum ] if !ref($classnum);
1039     @$classnum = grep /^\d+$/, @$classnum;
1040     my $in = 'IN ('. join(',', @$classnum). ')';
1041     push @where, "COALESCE(part_pkg.classnum, 0) $in" if scalar @$classnum;
1042   }
1043   @where;
1044 }
1045
1046 ##### end of package churn report stuff #####
1047
1048 ##### customer churn report #####
1049
1050 =item active_cust: The number of customers who had any active recurring
1051 packages at the start of the period. The end date is ignored, agentnum is
1052 mandatory, and no other parameters are accepted.
1053
1054 =item started_cust: The number of customers who had no active packages at
1055 the start of the period, but had active packages at the end. Like
1056 active_cust, agentnum is mandatory and no other parameters are accepted.
1057
1058 =item suspended_cust: The number of customers who had active packages at
1059 the start of the period, and at the end had no active packages but some
1060 suspended packages. Note that this does not necessarily mean that their
1061 packages were suspended during the period.
1062
1063 =item resumed_cust: The inverse of suspended_cust: the number of customers
1064 who had suspended packages and no active packages at the start of the
1065 period, and active packages at the end.
1066
1067 =item cancelled_cust: The number of customers who had active packages
1068 at the start of the period, and only cancelled packages at the end.
1069
1070 =cut
1071
1072 sub active_cust {
1073   my $self = shift;
1074   $self->churn_cust(@_)->{active};
1075 }
1076 sub started_cust {
1077   my $self = shift;
1078   $self->churn_cust(@_)->{started};
1079 }
1080 sub suspended_cust {
1081   my $self = shift;
1082   $self->churn_cust(@_)->{suspended};
1083 }
1084 sub resumed_cust {
1085   my $self = shift;
1086   $self->churn_cust(@_)->{resumed};
1087 }
1088 sub cancelled_cust {
1089   my $self = shift;
1090   $self->churn_cust(@_)->{cancelled};
1091 }
1092
1093 sub churn_cust {
1094   my $self = shift;
1095   my ( $speriod ) = @_;
1096
1097   # run one query for each interval
1098   return $self->{_interval}{$speriod} ||= $self->calculate_churn_cust(@_);
1099 }
1100
1101 sub calculate_churn_cust {
1102   my $self = shift;
1103   my ($speriod, $eperiod, $agentnum, %opt) = @_;
1104
1105   my $churn_sql = FS::cust_main::Status->churn_sql($speriod, $eperiod);
1106   my $where = '';
1107   $where = " WHERE cust_main.agentnum = $agentnum " if $agentnum;
1108   my $cust_sql =
1109     "SELECT churn.* ".
1110     "FROM cust_main JOIN ($churn_sql) AS churn USING (custnum)".
1111     $where;
1112
1113   # query to count the ones with certain status combinations
1114   my $total_sql = "
1115     SELECT SUM((s_active > 0)::int)                   as active,
1116            SUM((s_active = 0 and e_active > 0)::int)  as started,
1117            SUM((s_active > 0 and e_active = 0 and e_suspended > 0)::int)
1118                                                       as suspended,
1119            SUM((s_active = 0 and s_suspended > 0 and e_active > 0)::int)
1120                                                       as resumed,
1121            SUM((e_active = 0 and e_cancelled > s_cancelled)::int)
1122                                                       as cancelled
1123     FROM ($cust_sql) AS x
1124   ";
1125
1126   my $sth = dbh->prepare($total_sql);
1127   $sth->execute or die "failed to execute churn query: " . $sth->errstr;
1128
1129   $self->{_interval}{$speriod} = $sth->fetchrow_hashref;
1130 }
1131
1132 sub in_time_period_and_agent {
1133   my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
1134   my $col = @_ ? shift() : '_date';
1135
1136   my $sql = "$col >= $speriod AND $col < $eperiod";
1137
1138   #agent selection
1139   $sql .= " AND cust_main.agentnum = $agentnum"
1140     if $agentnum;
1141
1142   #agent virtualization
1143   $sql .= ' AND '.
1144           $FS::CurrentUser::CurrentUser->agentnums_sql( 'table'=>'cust_main' );
1145
1146   $sql;
1147 }
1148
1149 sub for_opts {
1150     my ( $self, %opt ) = @_;
1151     my $sql = '';
1152     if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
1153       $sql .= " and custnum = $1 ";
1154     }
1155     if ( $opt{'refnum'} ) {
1156       my $refnum = $opt{'refnum'};
1157       $refnum = [ $refnum ] if !ref($refnum);
1158       my $in = join(',', grep /^\d+$/, @$refnum);
1159       $sql .= " and refnum IN ($in)" if length $in;
1160     }
1161     if ( my $where = $self->with_cust_classnum(%opt) ) {
1162       $sql .= " and $where";
1163     }
1164
1165     $sql;
1166 }
1167
1168 sub with_classnum {
1169   my ($self, $classnum, $use_override) = @_;
1170   return '' if $classnum eq '';
1171
1172   $classnum = [ $classnum ] if !ref($classnum);
1173   @$classnum = grep /^\d+$/, @$classnum;
1174   return '' if !@$classnum;
1175   my $in = 'IN ('. join(',', @$classnum). ')';
1176
1177   if ( $use_override ) {
1178     # then include packages if their base package is in the set and they are
1179     # not overridden,
1180     # or if they are overridden and their override package is in the set,
1181     # or fees if they are in the set
1182     return "(
1183          ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL AND pkgpart_override IS NULL )
1184       OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )
1185       OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL )
1186     )";
1187   } else {
1188     # include packages if their base package is in the set,
1189     # or fees if they are in the set
1190     return "(
1191          ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL )
1192       OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL )
1193     )";
1194   }
1195 }
1196
1197 sub with_usageclass {
1198   my $self = shift;
1199   my ($classnum, $use_override) = @_;
1200   return '' unless $classnum =~ /^\d+$/;
1201   my $comparison;
1202   if ( $classnum == 0 ) {
1203     $comparison = 'IS NULL';
1204   }
1205   else {
1206     $comparison = "= $classnum";
1207   }
1208   return "cust_bill_pkg_detail.classnum $comparison";
1209 }
1210
1211 sub with_report_option {
1212   my ($self, %opt) = @_;
1213   # %opt can contain:
1214   # - report_optionnum: a comma-separated list of numbers.  Zero means to
1215   #   include packages with _no_ report classes.
1216   # - not_report_optionnum: a comma-separated list.  Packages that have
1217   #   any of these report options will be excluded from the result.
1218   #   Zero does nothing.
1219   # - use_override: also matches line items that are add-ons to a package
1220   #   matching the report class.
1221   # - all_report_options: returns only packages that have ALL of the
1222   #   report classes listed in $num.  Otherwise, will return packages that
1223   #   have ANY of those classes.
1224
1225   my @num = ref($opt{'report_optionnum'})
1226                   ? @{ $opt{'report_optionnum'} }
1227                   : split(/\s*,\s*/, $opt{'report_optionnum'});
1228   my @not_num = ref($opt{'not_report_optionnum'})
1229                       ? @{ $opt{'not_report_optionnum'} }
1230                       : split(/\s*,\s*/, $opt{'not_report_optionnum'});
1231   my $null;
1232   $null = 1 if ( grep {$_ == 0} @num );
1233   @num = grep {$_ > 0} @num;
1234   @not_num = grep {$_ > 0} @not_num;
1235
1236   # brute force
1237   my $table = $opt{'use_override'} ? 'override' : 'part_pkg';
1238   my $op = ' OR ';
1239   if ( $opt{'all_report_options'} ) {
1240     if ( @num and $null ) {
1241       return 'false'; # mutually exclusive criteria, so just bail out
1242     }
1243     $op = ' AND ';
1244   }
1245   my @where_num = map {
1246     "EXISTS(SELECT 1 FROM part_pkg_option ".
1247     "WHERE optionname = 'report_option_$_' ".
1248     "AND part_pkg_option.pkgpart = $table.pkgpart)"
1249   } @num;
1250   if ( $null ) {
1251     push @where_num, "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
1252                      "WHERE optionname LIKE 'report_option_%' ".
1253                      "AND part_pkg_option.pkgpart = $table.pkgpart)";
1254   }
1255   my @where_not_num = map {
1256     "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
1257     "WHERE optionname = 'report_option_$_' ".
1258     "AND part_pkg_option.pkgpart = $table.pkgpart)"
1259   } @not_num;
1260
1261   my @where;
1262   if (@where_num) {
1263     push @where, '( '.join($op, @where_num).' )';
1264   }
1265   if (@where_not_num) {
1266     push @where, '( '.join(' AND ', @where_not_num).' )';
1267   }
1268
1269   return @where;
1270   # this messes up totals
1271   #if ( $opt{'use_override'} ) {
1272   #  # then also allow the non-override package to match
1273   #  delete $opt{'use_override'};
1274   #  $comparison = "( $comparison OR " . $self->with_report_option(%opt) . ")";
1275   #}
1276
1277 }
1278
1279 sub with_refnum {
1280   my ($self, %opt) = @_;
1281   if ( $opt{'refnum'} ) {
1282     my $refnum = $opt{'refnum'};
1283     $refnum = [ $refnum ] if !ref($refnum);
1284     my $in = join(',', grep /^\d+$/, @$refnum);
1285     return "cust_main.refnum IN ($in)" if length $in;
1286   }
1287   return;
1288 }
1289
1290 sub with_towernum {
1291   my ($self, %opt) = @_;
1292   if ( $opt{'towernum'} ) {
1293     my $towernum = $opt{'towernum'};
1294     $towernum = [ $towernum ] if !ref($towernum);
1295     my $in = join(',', grep /^\d+$/, @$towernum);
1296     return unless length($in); # if no towers are specified, don't restrict
1297
1298     # materialize/cache the set of pkgnums that, as of the last
1299     # svc_broadband history record, had a certain towernum
1300     # (because otherwise this is painfully slow)
1301     $self->_init_tower_pkg_cache;
1302
1303     return "EXISTS(
1304             SELECT 1 FROM tower_pkg_cache
1305               WHERE towernum IN($in)
1306               AND cust_pkg.pkgnum = tower_pkg_cache.pkgnum
1307             )";
1308   }
1309   return;
1310 }
1311
1312 sub with_zip {
1313   my ($self, %opt) = @_;
1314   if (length($opt{'zip'})) {
1315     return "(SELECT zip FROM cust_location
1316              WHERE cust_location.locationnum = cust_pkg.locationnum
1317             ) = " . dbh->quote($opt{'zip'});
1318   }
1319   return;
1320 }
1321
1322 sub with_cust_classnum {
1323   my ($self, %opt) = @_;
1324   if ( $opt{'cust_classnum'} ) {
1325     my $classnums = $opt{'cust_classnum'};
1326     $classnums = [ $classnums ] if !ref($classnums);
1327     @$classnums = grep /^\d+$/, @$classnums;
1328     return 'cust_main.classnum in('. join(',',@$classnums) .')'
1329       if @$classnums;
1330   }
1331   return;
1332 }
1333
1334
1335 sub scalar_sql {
1336   my( $self, $sql ) = ( shift, shift );
1337   my $sth = dbh->prepare($sql) or die dbh->errstr;
1338   warn "FS::Report::Table\n$sql\n" if $DEBUG;
1339   $sth->execute
1340     or die "Unexpected error executing statement $sql: ". $sth->errstr;
1341   $sth->fetchrow_arrayref->[0] || 0;
1342 }
1343
1344 =back
1345
1346 =head1 METHODS
1347
1348 =over 4
1349
1350 =item init_projection
1351
1352 Sets up for future projection of all observables on the report.  Currently
1353 this is limited to 'cust_bill_pkg'.
1354
1355 =cut
1356
1357 sub init_projection {
1358   # this is weird special case stuff--some redesign may be needed
1359   # to use it for anything else
1360   my $self = shift;
1361
1362   if ( driver_name ne 'Pg' ) {
1363     # also database-specific for now
1364     die "projection reports not supported on this platform";
1365   }
1366
1367   my %items = map {$_ => 1} @{ $self->{items} };
1368   if ($items{'cust_bill_pkg'}) {
1369     my $dbh = dbh;
1370     # v_ for 'virtual'
1371     my @sql = (
1372       # could use TEMPORARY TABLE but we're already transaction-protected
1373       'DROP TABLE IF EXISTS v_cust_bill_pkg',
1374       'CREATE TABLE v_cust_bill_pkg ' .
1375        '(LIKE cust_bill_pkg,
1376           usage numeric(10,2), _date integer, expire integer)',
1377       # XXX this should be smart enough to take only the ones with
1378       # sdate/edate overlapping the ROI, for performance
1379       "INSERT INTO v_cust_bill_pkg (
1380         SELECT cust_bill_pkg.*,
1381           (SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
1382           FROM cust_bill_pkg_detail
1383           WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum),
1384           cust_bill._date,
1385           cust_pkg.expire
1386         FROM cust_bill_pkg $cust_bill_pkg_join
1387       )",
1388     );
1389     foreach my $sql (@sql) {
1390       warn "[init_projection] $sql\n" if $DEBUG;
1391       $dbh->do($sql) or die $dbh->errstr;
1392     }
1393   }
1394 }
1395
1396 =item extend_projection START END
1397
1398 Generates data for the next period of projection.  This will be called
1399 for sequential periods where the END of one equals the START of the next
1400 (with no gaps).
1401
1402 =cut
1403
1404 sub extend_projection {
1405   my $self = shift;
1406   my ($speriod, $eperiod) = @_;
1407   my %items = map {$_ => 1} @{ $self->{items} };
1408   if ($items{'cust_bill_pkg'}) {
1409     # What we do here:
1410     # Find all line items that end after the start of the period (and have
1411     # recurring fees, and don't expire before they end).  Choose the latest
1412     # one for each package.  If it ends before the end of the period, copy
1413     # it forward by one billing period.
1414     # Repeat this until the latest line item for each package no longer ends
1415     # within the period.  This is certain to happen in finitely many
1416     # iterations as long as freq > 0.
1417     # - Pg only, obviously.
1418     # - Gives bad results if freq_override is used.
1419     my @fields = ( FS::cust_bill_pkg->fields, qw( usage _date expire ) );
1420     my $insert_fields = join(',', @fields);
1421     my $add_freq = sub { # emulate FS::part_pkg::add_freq
1422       my $field = shift;
1423       "EXTRACT( EPOCH FROM TO_TIMESTAMP($field) + (CASE WHEN freq ~ E'\\\\D' ".
1424       "THEN freq ELSE freq || 'mon' END)::INTERVAL) AS $field";
1425     };
1426     foreach (@fields) {
1427       if ($_ eq 'edate') {
1428         $_ = $add_freq->('edate');
1429       }
1430       elsif ($_ eq 'sdate') {
1431         $_ = 'edate AS sdate'
1432       }
1433       elsif ($_ eq 'setup') {
1434         $_ = '0 AS setup' #because recurring only
1435       }
1436       elsif ($_ eq '_date') {
1437         $_ = $add_freq->('_date');
1438       }
1439     }
1440     my $select_fields = join(',', @fields);
1441     my $dbh = dbh;
1442     my $sql =
1443     # Subquery here because we need to DISTINCT the whole set, select the
1444     # latest charge per pkgnum, and _then_ check edate < $eperiod
1445     # and edate < expire.
1446       "INSERT INTO v_cust_bill_pkg ($insert_fields)
1447         SELECT $select_fields FROM (
1448           SELECT DISTINCT ON (pkgnum) * FROM v_cust_bill_pkg
1449             WHERE edate >= $speriod
1450               AND recur > 0
1451               AND freq IS NOT NULL
1452               AND freq != '0'
1453             ORDER BY pkgnum, edate DESC
1454           ) AS v1
1455           WHERE edate < $eperiod AND (edate < expire OR expire IS NULL)";
1456     my $rows;
1457     do {
1458       warn "[extend_projection] $sql\n" if $DEBUG;
1459       $rows = $dbh->do($sql) or die $dbh->errstr;
1460       warn "[extend_projection] $rows rows\n" if $DEBUG;
1461     } until $rows == 0;
1462   }
1463 }
1464
1465 =item _init_tower_pkg_cache
1466
1467 Internal method: creates a temporary table relating pkgnums to towernums.
1468 A (pkgnum, towernum) record indicates that this package once had a
1469 svc_broadband service which, as of its last insert or replace_new history
1470 record, had a sectornum associated with that towernum.
1471
1472 This is expensive, so it won't be done more than once an hour. Historical
1473 data about package churn shouldn't be changing in realtime anyway.
1474
1475 =cut
1476
1477 sub _init_tower_pkg_cache {
1478   my $self = shift;
1479   my $dbh = dbh;
1480
1481   my $current = $CACHE->get('tower_pkg_cache_update');
1482   return if $current;
1483
1484   # XXX or should this be in the schema?
1485   my $sql = "DROP TABLE IF EXISTS tower_pkg_cache";
1486   $dbh->do($sql) or die $dbh->errstr;
1487   $sql = "CREATE TABLE tower_pkg_cache (towernum int, pkgnum int)";
1488   $dbh->do($sql) or die $dbh->errstr;
1489
1490   # assumptions:
1491   # sectornums never get reused, or move from one tower to another
1492   # all service history is intact
1493   # svcnums never get reused (this would be bad)
1494   # pkgnums NEVER get reused (this would be extremely bad)
1495   $sql = "INSERT INTO tower_pkg_cache (
1496     SELECT COALESCE(towernum,0), pkgnum
1497     FROM ( SELECT DISTINCT pkgnum, svcnum FROM h_cust_svc ) AS pkgnum_svcnum
1498     LEFT JOIN (
1499       SELECT DISTINCT ON(svcnum) svcnum, sectornum
1500         FROM h_svc_broadband
1501         WHERE (history_action = 'replace_new'
1502                OR history_action = 'replace_old')
1503         ORDER BY svcnum ASC, history_date DESC
1504     ) AS svcnum_sectornum USING (svcnum)
1505     LEFT JOIN tower_sector USING (sectornum)
1506   )";
1507   $dbh->do($sql) or die $dbh->errstr;
1508
1509   $CACHE->set('tower_pkg_cache_update', 1, 3600);
1510
1511 };
1512
1513 =head1 BUGS
1514
1515 Documentation.
1516
1517 =head1 SEE ALSO
1518
1519 L<FS::Report::Table::Monthly>, reports in the web interface.
1520
1521 =cut
1522
1523 1;