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             FS::cust_bill_pkg->paid_sql($speriod, $eperiod, %opt).
 233            ') FROM cust_bill_pkg';
 234   }
 235
 236   $self->scalar_sql("
 237     $sql
 238         LEFT JOIN cust_bill USING ( invnum  )
 239         LEFT JOIN cust_main USING ( custnum )
 240       WHERE ". $self->in_time_period_and_agent( $speriod,
 241                                                 $eperiod,
 242                                                 $agentnum,
 243                                                 'cust_bill._date'
 244                                               ).
 245                $self->for_opts(%opt)
 246   );
 247 }
 248
 249 =item netrefunds: The sum of refund applications to credits in the period.
 250
 251 =cut
 252
 253 sub netrefunds {
 254   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 255   $self->scalar_sql("
 256     SELECT SUM(cust_credit_refund.amount)
 257       FROM cust_credit_refund
 258         LEFT JOIN cust_credit USING ( crednum  )
 259         LEFT JOIN cust_main   USING ( custnum )
 260       WHERE ". $self->in_time_period_and_agent( $speriod,
 261                                                 $eperiod,
 262                                                 $agentnum,
 263                                                 'cust_credit._date'
 264                                               ).
 265                $self->for_opts(%opt)
 266   );
 267 }
 268
 269 =item discounted: The sum of discounts on invoices in the period.
 270
 271 =cut
 272
 273 sub discounted {
 274   my( $self, $speriod, $eperiod, $agentnum, %opt) = @_;
 275
 276   my $sql = 'SELECT SUM(';
 277   if ($opt{'setuprecur'}) {
 278     $sql .= <<EOF;
 279 CASE
 280   WHEN discount.setup = 'Y'
 281     AND ((COALESCE(cust_bill_pkg.unitsetup,0) > 0)
 282           OR (COALESCE(cust_bill_pkg.unitrecur,0) > 0))
 283   THEN
 284 EOF
 285     if ($opt{'setuprecur'} eq 'setup') {
 286       $sql .= '    (COALESCE(cust_bill_pkg.unitsetup,0)';
 287     } elsif ($opt{'setuprecur'} eq 'recur') {
 288       $sql .= '    (COALESCE(cust_bill_pkg.unitrecur,0)';
 289     } else {
 290       die 'Unrecognized value for setuprecur';
 291     }
 292     $sql .= ' / (COALESCE(cust_bill_pkg.unitsetup,0) + COALESCE(cust_bill_pkg.unitrecur,0)))';
 293     $sql .= " * cust_bill_pkg_discount.amount\n";
 294     $sql .= <<EOF;
 295   ELSE cust_bill_pkg_discount.amount
 296 END
 297 EOF
 298   } else {
 299     $sql .= "cust_bill_pkg_discount.amount\n";
 300   }
 301   $sql .= <<EOF;
 302 ) FROM cust_bill_pkg_discount
 303   JOIN cust_bill_pkg     USING  ( billpkgnum )
 304   JOIN cust_bill         USING  ( invnum )
 305   JOIN cust_main         USING  ( custnum )
 306 EOF
 307   if ($opt{'setuprecur'}) {
 308     $sql .= <<EOF;
 309   JOIN cust_pkg_discount USING ( pkgdiscountnum )
 310   LEFT JOIN discount          USING ( discountnum )
 311 EOF
 312   }
 313   $self->scalar_sql(
 314     $sql
 315     . 'WHERE '
 316     . $self->in_time_period_and_agent( $speriod,
 317                                        $eperiod,
 318                                        $agentnum,
 319                                        'cust_bill._date'
 320                                       )
 321     . $self->for_opts(%opt)
 322   );
 323 }
 324
 325 =item gross: invoiced + discounted
 326
 327 =cut
 328
 329 sub gross {
 330   my( $self, $speriod, $eperiod, $agentnum, %opt) = @_;
 331     $self->invoiced(   $speriod, $eperiod, $agentnum, %opt)
 332   + $self->discounted( $speriod, $eperiod, $agentnum, %opt);
 333 }
 334
 335 #XXX docs
 336
 337 #these should be auto-generated or $AUTOLOADed or something
 338 sub invoiced_12mo {
 339   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 340   $speriod = $self->_subtract_11mo($speriod);
 341   $self->invoiced($speriod, $eperiod, $agentnum);
 342 }
 343
 344 sub netsales_12mo {
 345   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 346   $speriod = $self->_subtract_11mo($speriod);
 347   $self->netsales($speriod, $eperiod, $agentnum);
 348 }
 349
 350 sub receipts_12mo {
 351   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 352   $speriod = $self->_subtract_11mo($speriod);
 353   $self->receipts($speriod, $eperiod, $agentnum);
 354 }
 355
 356 sub payments_12mo {
 357   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 358   $speriod = $self->_subtract_11mo($speriod);
 359   $self->payments($speriod, $eperiod, $agentnum);
 360 }
 361
 362 sub credits_12mo {
 363   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 364   $speriod = $self->_subtract_11mo($speriod);
 365   $self->credits($speriod, $eperiod, $agentnum);
 366 }
 367
 368 sub netcredits_12mo {
 369   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 370   $speriod = $self->_subtract_11mo($speriod);
 371   $self->netcredits($speriod, $eperiod, $agentnum);
 372 }
 373
 374 sub cashflow_12mo {
 375   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 376   $speriod = $self->_subtract_11mo($speriod);
 377   $self->cashflow($speriod, $eperiod, $agentnum);
 378 }
 379
 380 sub netcashflow_12mo {
 381   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 382   $speriod = $self->_subtract_11mo($speriod);
 383   $self->cashflow($speriod, $eperiod, $agentnum);
 384 }
 385
 386 sub refunds_12mo {
 387   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 388   $speriod = $self->_subtract_11mo($speriod);
 389   $self->refunds($speriod, $eperiod, $agentnum);
 390 }
 391
 392 sub netrefunds_12mo {
 393   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 394   $speriod = $self->_subtract_11mo($speriod);
 395   $self->netrefunds($speriod, $eperiod, $agentnum);
 396 }
 397
 398
 399 #not being too bad with the false laziness
 400 sub _subtract_11mo {
 401   my($self, $time) = @_;
 402   my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
 403   $mon -= 11;
 404   if ( $mon < 0 ) { $mon+=12; $year--; }
 405   timelocal($sec,$min,$hour,$mday,$mon,$year);
 406 }
 407
 408 =item cust_pkg_setup_cost: The total setup costs of packages setup in the period
 409
 410 'classnum': limit to this package class.
 411
 412 =cut
 413
 414 sub cust_pkg_setup_cost {
 415   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 416   my $where = '';
 417
 418   if ( $opt{'classnum'} ne '' ) {
 419     my $classnums = $opt{'classnum'};
 420     $classnums = [ $classnums ] if !ref($classnums);
 421     @$classnums = grep /^\d+$/, @$classnums;
 422     $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
 423                                                     ')';
 424   }
 425
 426   $agentnum ||= $opt{'agentnum'};
 427
 428   my $total_sql = " SELECT SUM(part_pkg.setup_cost) ";
 429   $total_sql .= " FROM cust_pkg
 430              LEFT JOIN cust_main USING ( custnum )
 431              LEFT JOIN part_pkg  USING ( pkgpart )
 432                   WHERE pkgnum != 0
 433                   $where
 434                   AND ".$self->in_time_period_and_agent(
 435                     $speriod, $eperiod, $agentnum, 'cust_pkg.setup');
 436   return $self->scalar_sql($total_sql);
 437 }
 438
 439 =item cust_pkg_recur_cust: the total recur costs of packages in the period
 440
 441 'classnum': limit to this package class.
 442
 443 =cut
 444
 445 sub cust_pkg_recur_cost {
 446   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 447   my $where = '';
 448
 449   if ( $opt{'classnum'} ne '' ) {
 450     my $classnums = $opt{'classnum'};
 451     $classnums = [ $classnums ] if !ref($classnums);
 452     @$classnums = grep /^\d+$/, @$classnums;
 453     $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
 454                                                     ')';
 455   }
 456
 457   $agentnum ||= $opt{'agentnum'};
 458   # duplication of in_time_period_and_agent
 459   # because we do it a little differently here
 460   $where .= " AND cust_main.agentnum = $agentnum" if $agentnum;
 461   $where .= " AND ".
 462           $FS::CurrentUser::CurrentUser->agentnums_sql('table' => 'cust_main');
 463
 464   my $total_sql = " SELECT SUM(part_pkg.recur_cost) ";
 465   $total_sql .= " FROM cust_pkg
 466              LEFT JOIN cust_main USING ( custnum )
 467              LEFT JOIN part_pkg  USING ( pkgpart )
 468                   WHERE pkgnum != 0
 469                   $where
 470                   AND cust_pkg.setup < $eperiod
 471                   AND (cust_pkg.cancel > $speriod OR cust_pkg.cancel IS NULL)
 472                   ";
 473   return $self->scalar_sql($total_sql);
 474 }
 475
 476 =item cust_bill_pkg: the total package charges on invoice line items.
 477
 478 'charges': limit the type of charges included (setup, recur, usage, discount).
 479 Should be a string containing one or more of 'S', 'R', 'U', or 'D'; if
 480 unspecified, defaults to all three.
 481
 482 'classnum': limit to this package class.
 483
 484 'use_override': for line items generated by an add-on package, use the class
 485 of the add-on rather than the base package.
 486
 487 'average_per_cust_pkg': divide the result by the number of distinct packages.
 488
 489 'distribute': for non-monthly recurring charges, ignore the invoice
 490 date.  Instead, consider the line item's starting/ending dates.  Determine
 491 the fraction of the line item duration that falls within the specified
 492 interval and return that fraction of the recurring charges.  This is
 493 somewhat experimental.
 494
 495 'project': enable if this is a projected period.  This is very experimental.
 496
 497 =cut
 498
 499 sub cust_bill_pkg {
 500   my $self = shift;
 501   my( $speriod, $eperiod, $agentnum, %opt ) = @_;
 502
 503   my %charges = map {$_=>1} split('', $opt{'charges'} || 'SRU');
 504
 505   my $sum = 0;
 506   $sum += $self->cust_bill_pkg_setup(@_) if $charges{S};
 507   $sum += $self->cust_bill_pkg_recur(@_) if $charges{R};
 508   $sum += $self->cust_bill_pkg_detail(@_) if $charges{U};
 509   $sum += $self->cust_bill_pkg_discount(@_) if $charges{D};
 510
 511   if ($opt{'average_per_cust_pkg'}) {
 512     my $count = $self->cust_bill_pkg_count_pkgnum(@_);
 513     return '' if $count == 0;
 514     $sum = sprintf('%.2f', $sum / $count);
 515   }
 516   $sum;
 517 }
 518
 519 my $cust_bill_pkg_join = '
 520     LEFT JOIN cust_bill USING ( invnum )
 521     LEFT JOIN cust_main USING ( custnum )
 522     LEFT JOIN cust_pkg USING ( pkgnum )
 523     LEFT JOIN part_pkg USING ( pkgpart )
 524     LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
 525     LEFT JOIN part_fee USING ( feepart )';
 526
 527 sub cust_bill_pkg_setup {
 528   my $self = shift;
 529   my ($speriod, $eperiod, $agentnum, %opt) = @_;
 530   # no projecting setup fees--use real invoices only
 531   # but evaluate this anyway, because the design of projection is that
 532   # if there are somehow real setup fees in the future, we want to count
 533   # them
 534
 535   $agentnum ||= $opt{'agentnum'};
 536
 537   my @where = (
 538     '(pkgnum != 0 OR feepart IS NOT NULL)',
 539     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
 540     $self->with_report_option(%opt),
 541     $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
 542     $self->with_refnum(%opt),
 543     $self->with_cust_classnum(%opt)
 544   );
 545
 546   my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0)
 547   FROM cust_bill_pkg
 548   $cust_bill_pkg_join
 549   WHERE " . join(' AND ', grep $_, @where);
 550
 551   $self->scalar_sql($total_sql);
 552 }
 553
 554 sub _cust_bill_pkg_recurring {
 555   # returns the FROM/WHERE part of the statement to query all recurring
 556   # line items in the period
 557   my $self = shift;
 558   my ($speriod, $eperiod, $agentnum, %opt) = @_;
 559
 560   $agentnum ||= $opt{'agentnum'};
 561   my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
 562
 563   my @where = (
 564     '(pkgnum != 0 OR feepart IS NOT NULL)',
 565     $self->with_report_option(%opt),
 566     $self->with_refnum(%opt),
 567     $self->with_cust_classnum(%opt)
 568   );
 569
 570   my $where_classnum = $self->with_classnum($opt{'classnum'}, $opt{'use_override'});
 571   if ($opt{'project'}) {
 572     $where_classnum =~ s/\bcust_bill_pkg/v_cust_bill_pkg/g;
 573   }
 574   push @where, $where_classnum;
 575
 576   if ( $opt{'distribute'} ) {
 577     $where[0] = 'pkgnum != 0'; # specifically exclude fees
 578     push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 579     push @where,
 580       "$cust_bill_pkg.sdate <  $eperiod",
 581       "$cust_bill_pkg.edate >= $speriod",
 582     ;
 583   }
 584   else {
 585     # we don't want to have to create v_cust_bill
 586     my $_date = $opt{'project'} ? 'v_cust_bill_pkg._date' : 'cust_bill._date';
 587     push @where,
 588       $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date);
 589   }
 590
 591   return "
 592   FROM $cust_bill_pkg
 593   $cust_bill_pkg_join
 594   WHERE ".join(' AND ', grep $_, @where);
 595
 596 }
 597
 598 sub cust_bill_pkg_recur {
 599   my $self = shift;
 600   my ($speriod, $eperiod, $agentnum, %opt) = @_;
 601
 602   # subtract all usage from the line item regardless of date
 603   my $item_usage;
 604   if ( $opt{'project'} ) {
 605     $item_usage = 'usage'; #already calculated
 606   }
 607   else {
 608     $item_usage = '( SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
 609       FROM cust_bill_pkg_detail
 610       WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
 611   }
 612
 613   my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
 614
 615   my $recur_fraction = '';
 616   if ($opt{'distribute'}) {
 617     # the fraction of edate - sdate that's within [speriod, eperiod]
 618     $recur_fraction = " *
 619       CAST(LEAST($eperiod, $cust_bill_pkg.edate) -
 620        GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) /
 621       ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
 622   }
 623
 624   my $total_sql =
 625     "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)" .
 626     $self->_cust_bill_pkg_recurring(@_);
 627
 628   $self->scalar_sql($total_sql);
 629 }
 630
 631 sub cust_bill_pkg_count_pkgnum {
 632   # for ARPU calculation
 633   my $self = shift;
 634   my $total_sql = 'SELECT COUNT(DISTINCT pkgnum) '.
 635     $self->_cust_bill_pkg_recurring(@_);
 636
 637   $self->scalar_sql($total_sql);
 638 }
 639
 640 =item cust_bill_pkg_detail: the total usage charges in detail lines.
 641
 642 Arguments as for C<cust_bill_pkg>, plus:
 643
 644 'usageclass': limit to this usage class number.
 645
 646 =cut
 647
 648 sub cust_bill_pkg_detail {
 649   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 650
 651   my @where =
 652     ( "(cust_bill_pkg.pkgnum != 0 OR cust_bill_pkg.feepart IS NOT NULL)" );
 653
 654   $agentnum ||= $opt{'agentnum'};
 655
 656   push @where,
 657     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
 658     $self->with_usageclass($opt{'usageclass'}),
 659     $self->with_report_option(%opt),
 660     $self->with_refnum(%opt),
 661     $self->with_cust_classnum(%opt)
 662     ;
 663
 664   if ( $opt{'distribute'} ) {
 665     # exclude fees
 666     $where[0] = 'cust_bill_pkg.pkgnum != 0';
 667     # and limit according to the usage time, not the billing date
 668     push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
 669       'cust_bill_pkg_detail.startdate'
 670     );
 671   }
 672   else {
 673     push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
 674       'cust_bill._date'
 675     );
 676   }
 677
 678   my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) ";
 679
 680   $total_sql .=
 681     " FROM cust_bill_pkg_detail
 682         LEFT JOIN cust_bill_pkg USING ( billpkgnum )
 683         LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
 684         LEFT JOIN cust_main USING ( custnum )
 685         LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
 686         LEFT JOIN part_pkg USING ( pkgpart )
 687         LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
 688         LEFT JOIN part_fee USING ( feepart )
 689       WHERE ".join( ' AND ', grep $_, @where );
 690
 691   $self->scalar_sql($total_sql);
 692
 693 }
 694
 695 sub cust_bill_pkg_discount {
 696   my $self = shift;
 697   my ($speriod, $eperiod, $agentnum, %opt) = @_;
 698   # apply all the same constraints here as for setup/recur
 699
 700   $agentnum ||= $opt{'agentnum'};
 701
 702   my @where = (
 703     '(pkgnum != 0 OR feepart IS NOT NULL)',
 704     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
 705     $self->with_report_option(%opt),
 706     $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
 707     $self->with_refnum(%opt),
 708     $self->with_cust_classnum(%opt)
 709   );
 710
 711   my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg_discount.amount), 0)
 712   FROM cust_bill_pkg_discount
 713   JOIN cust_bill_pkg USING (billpkgnum)
 714   $cust_bill_pkg_join
 715   WHERE " . join(' AND ', grep $_, @where);
 716
 717   $self->scalar_sql($total_sql);
 718 }
 719
 720 ##### package churn report #####
 721
 722 =item active_pkg: The number of packages that were active at the start of
 723 the period. The end date of the period is ignored. Options:
 724
 725 - refnum: Limit to customers with this advertising source.
 726 - classnum: Limit to packages with this class.
 727 - towernum: Limit to packages that have a broadband service with this tower.
 728 - zip: Limit to packages with this service location zip code.
 729
 730 Except for zip, any of these can be an arrayref to allow multiple values for
 731 the field.
 732
 733 =item setup_pkg: The number of packages with setup dates in the period. This
 734 excludes packages created by package changes. Options are as for active_pkg.
 735
 736 =item susp_pkg: The number of packages that were suspended in the period
 737 (and not canceled).  Options are as for active_pkg.
 738
 739 =item unsusp_pkg: The number of packages that were unsuspended in the period.
 740 Options are as for active_pkg.
 741
 742 =item cancel_pkg: The number of packages with cancel dates in the period.
 743 Excludes packages that were canceled to be changed to a new package. Options
 744 are as for active_pkg.
 745
 746 =cut
 747
 748 sub active_pkg {
 749   my $self = shift;
 750   $self->churn_pkg('active', @_);
 751 }
 752
 753 sub setup_pkg {
 754   my $self = shift;
 755   $self->churn_pkg('setup', @_);
 756 }
 757
 758 sub cancel_pkg {
 759   my $self = shift;
 760   $self->churn_pkg('cancel', @_);
 761 }
 762
 763 sub susp_pkg {
 764   my $self = shift;
 765   $self->churn_pkg('susp', @_);
 766 }
 767
 768 sub unsusp_pkg {
 769   my $self = shift;
 770   $self->churn_pkg('unsusp', @_);
 771 }
 772
 773 sub churn_pkg {
 774   my $self = shift;
 775   my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_;
 776   my ($from, @where) =
 777     FS::h_cust_pkg->churn_fromwhere_sql( $status, $speriod, $eperiod);
 778
 779   push @where, $self->pkg_where(%opt, 'agentnum' => $agentnum);
 780
 781   my $sql = "SELECT COUNT(*) FROM $from
 782     JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
 783     JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)";
 784   $sql .= ' WHERE '.join(' AND ', @where)
 785     if scalar(@where);
 786
 787   $self->scalar_sql($sql);
 788 }
 789
 790 sub pkg_where {
 791   my $self = shift;
 792   my %opt = @_;
 793   my @where = (
 794     "part_pkg.freq != '0'",
 795     $self->with_refnum(%opt),
 796     $self->with_towernum(%opt),
 797     $self->with_zip(%opt),
 798   );
 799   if ($opt{agentnum} =~ /^(\d+)$/) {
 800     push @where, "cust_main.agentnum = $1";
 801   }
 802   if ($opt{classnum}) {
 803     my $classnum = $opt{classnum};
 804     $classnum = [ $classnum ] if !ref($classnum);
 805     @$classnum = grep /^\d+$/, @$classnum;
 806     my $in = 'IN ('. join(',', @$classnum). ')';
 807     push @where, "COALESCE(part_pkg.classnum, 0) $in" if scalar @$classnum;
 808   }
 809   @where;
 810 }
 811
 812 ##### end of package churn report stuff #####
 813
 814 ##### customer churn report #####
 815
 816 =item active_cust: The number of customers who had any active recurring
 817 packages at the start of the period. The end date is ignored, agentnum is
 818 mandatory, and no other parameters are accepted.
 819
 820 =item started_cust: The number of customers who had no active packages at
 821 the start of the period, but had active packages at the end. Like
 822 active_cust, agentnum is mandatory and no other parameters are accepted.
 823
 824 =item suspended_cust: The number of customers who had active packages at
 825 the start of the period, and at the end had no active packages but some
 826 suspended packages. Note that this does not necessarily mean that their
 827 packages were suspended during the period.
 828
 829 =item resumed_cust: The inverse of suspended_cust: the number of customers
 830 who had suspended packages and no active packages at the start of the
 831 period, and active packages at the end.
 832
 833 =item cancelled_cust: The number of customers who had active packages
 834 at the start of the period, and only cancelled packages at the end.
 835
 836 =cut
 837
 838 sub active_cust {
 839   my $self = shift;
 840   $self->churn_cust(@_)->{active};
 841 }
 842 sub started_cust {
 843   my $self = shift;
 844   $self->churn_cust(@_)->{started};
 845 }
 846 sub suspended_cust {
 847   my $self = shift;
 848   $self->churn_cust(@_)->{suspended};
 849 }
 850 sub resumed_cust {
 851   my $self = shift;
 852   $self->churn_cust(@_)->{resumed};
 853 }
 854 sub cancelled_cust {
 855   my $self = shift;
 856   $self->churn_cust(@_)->{cancelled};
 857 }
 858
 859 sub churn_cust {
 860   my $self = shift;
 861   my ( $speriod ) = @_;
 862
 863   # run one query for each interval
 864   return $self->{_interval}{$speriod} ||= $self->calculate_churn_cust(@_);
 865 }
 866
 867 sub calculate_churn_cust {
 868   my $self = shift;
 869   my ($speriod, $eperiod, $agentnum, %opt) = @_;
 870
 871   my $churn_sql = FS::cust_main::Status->churn_sql($speriod, $eperiod);
 872   my $where = '';
 873   $where = " WHERE cust_main.agentnum = $agentnum " if $agentnum;
 874   my $cust_sql =
 875     "SELECT churn.* ".
 876     "FROM cust_main JOIN ($churn_sql) AS churn USING (custnum)".
 877     $where;
 878
 879   # query to count the ones with certain status combinations
 880   my $total_sql = "
 881     SELECT SUM((s_active > 0)::int)                   as active,
 882            SUM((s_active = 0 and e_active > 0)::int)  as started,
 883            SUM((s_active > 0 and e_active = 0 and e_suspended > 0)::int)
 884                                                       as suspended,
 885            SUM((s_active = 0 and s_suspended > 0 and e_active > 0)::int)
 886                                                       as resumed,
 887            SUM((s_active > 0 and e_active = 0 and e_suspended = 0)::int)
 888                                                       as cancelled
 889     FROM ($cust_sql) AS x
 890   ";
 891
 892   my $sth = dbh->prepare($total_sql);
 893   $sth->execute or die "failed to execute churn query: " . $sth->errstr;
 894
 895   $self->{_interval}{$speriod} = $sth->fetchrow_hashref;
 896 }
 897
 898 sub in_time_period_and_agent {
 899   my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
 900   my $col = @_ ? shift() : '_date';
 901
 902   my $sql = "$col >= $speriod AND $col < $eperiod";
 903
 904   #agent selection
 905   $sql .= " AND cust_main.agentnum = $agentnum"
 906     if $agentnum;
 907
 908   #agent virtualization
 909   $sql .= ' AND '.
 910           $FS::CurrentUser::CurrentUser->agentnums_sql( 'table'=>'cust_main' );
 911
 912   $sql;
 913 }
 914
 915 sub for_opts {
 916     my ( $self, %opt ) = @_;
 917     my $sql = '';
 918     if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
 919       $sql .= " and custnum = $1 ";
 920     }
 921     if ( $opt{'refnum'} ) {
 922       my $refnum = $opt{'refnum'};
 923       $refnum = [ $refnum ] if !ref($refnum);
 924       my $in = join(',', grep /^\d+$/, @$refnum);
 925       $sql .= " and refnum IN ($in)" if length $in;
 926     }
 927     if ( my $where = $self->with_cust_classnum(%opt) ) {
 928       $sql .= " and $where";
 929     }
 930
 931     $sql;
 932 }
 933
 934 sub with_classnum {
 935   my ($self, $classnum, $use_override) = @_;
 936   return '' if $classnum eq '';
 937
 938   $classnum = [ $classnum ] if !ref($classnum);
 939   @$classnum = grep /^\d+$/, @$classnum;
 940   return '' if !@$classnum;
 941   my $in = 'IN ('. join(',', @$classnum). ')';
 942
 943   if ( $use_override ) {
 944     # then include packages if their base package is in the set and they are
 945     # not overridden,
 946     # or if they are overridden and their override package is in the set,
 947     # or fees if they are in the set
 948     return "(
 949          ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL AND pkgpart_override IS NULL )
 950       OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )
 951       OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL )
 952     )";
 953   } else {
 954     # include packages if their base package is in the set,
 955     # or fees if they are in the set
 956     return "(
 957          ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL )
 958       OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL )
 959     )";
 960   }
 961 }
 962
 963 sub with_usageclass {
 964   my $self = shift;
 965   my ($classnum, $use_override) = @_;
 966   return '' unless $classnum =~ /^\d+$/;
 967   my $comparison;
 968   if ( $classnum == 0 ) {
 969     $comparison = 'IS NULL';
 970   }
 971   else {
 972     $comparison = "= $classnum";
 973   }
 974   return "cust_bill_pkg_detail.classnum $comparison";
 975 }
 976
 977 sub with_report_option {
 978   my ($self, %opt) = @_;
 979   # %opt can contain:
 980   # - report_optionnum: a comma-separated list of numbers.  Zero means to
 981   #   include packages with _no_ report classes.
 982   # - not_report_optionnum: a comma-separated list.  Packages that have
 983   #   any of these report options will be excluded from the result.
 984   #   Zero does nothing.
 985   # - use_override: also matches line items that are add-ons to a package
 986   #   matching the report class.
 987   # - all_report_options: returns only packages that have ALL of the
 988   #   report classes listed in $num.  Otherwise, will return packages that
 989   #   have ANY of those classes.
 990
 991   my @num = ref($opt{'report_optionnum'})
 992                   ? @{ $opt{'report_optionnum'} }
 993                   : split(/\s*,\s*/, $opt{'report_optionnum'});
 994   my @not_num = ref($opt{'not_report_optionnum'})
 995                       ? @{ $opt{'not_report_optionnum'} }
 996                       : split(/\s*,\s*/, $opt{'not_report_optionnum'});
 997   my $null;
 998   $null = 1 if ( grep {$_ == 0} @num );
 999   @num = grep {$_ > 0} @num;
1000   @not_num = grep {$_ > 0} @not_num;
1001
1002   # brute force
1003   my $table = $opt{'use_override'} ? 'override' : 'part_pkg';
1004   my $op = ' OR ';
1005   if ( $opt{'all_report_options'} ) {
1006     if ( @num and $null ) {
1007       return 'false'; # mutually exclusive criteria, so just bail out
1008     }
1009     $op = ' AND ';
1010   }
1011   my @where_num = map {
1012     "EXISTS(SELECT 1 FROM part_pkg_option ".
1013     "WHERE optionname = 'report_option_$_' ".
1014     "AND part_pkg_option.pkgpart = $table.pkgpart)"
1015   } @num;
1016   if ( $null ) {
1017     push @where_num, "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
1018                      "WHERE optionname LIKE 'report_option_%' ".
1019                      "AND part_pkg_option.pkgpart = $table.pkgpart)";
1020   }
1021   my @where_not_num = map {
1022     "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
1023     "WHERE optionname = 'report_option_$_' ".
1024     "AND part_pkg_option.pkgpart = $table.pkgpart)"
1025   } @not_num;
1026
1027   my @where;
1028   if (@where_num) {
1029     push @where, '( '.join($op, @where_num).' )';
1030   }
1031   if (@where_not_num) {
1032     push @where, '( '.join(' AND ', @where_not_num).' )';
1033   }
1034
1035   return @where;
1036   # this messes up totals
1037   #if ( $opt{'use_override'} ) {
1038   #  # then also allow the non-override package to match
1039   #  delete $opt{'use_override'};
1040   #  $comparison = "( $comparison OR " . $self->with_report_option(%opt) . ")";
1041   #}
1042
1043 }
1044
1045 sub with_refnum {
1046   my ($self, %opt) = @_;
1047   if ( $opt{'refnum'} ) {
1048     my $refnum = $opt{'refnum'};
1049     $refnum = [ $refnum ] if !ref($refnum);
1050     my $in = join(',', grep /^\d+$/, @$refnum);
1051     return "cust_main.refnum IN ($in)" if length $in;
1052   }
1053   return;
1054 }
1055
1056 sub with_towernum {
1057   my ($self, %opt) = @_;
1058   if ( $opt{'towernum'} ) {
1059     my $towernum = $opt{'towernum'};
1060     $towernum = [ $towernum ] if !ref($towernum);
1061     my $in = join(',', grep /^\d+$/, @$towernum);
1062     return unless length($in); # if no towers are specified, don't restrict
1063
1064     # materialize/cache the set of pkgnums that, as of the last
1065     # svc_broadband history record, had a certain towernum
1066     # (because otherwise this is painfully slow)
1067     $self->_init_tower_pkg_cache;
1068
1069     return "EXISTS(
1070             SELECT 1 FROM tower_pkg_cache
1071               WHERE towernum IN($in)
1072               AND cust_pkg.pkgnum = tower_pkg_cache.pkgnum
1073             )";
1074   }
1075   return;
1076 }
1077
1078 sub with_zip {
1079   my ($self, %opt) = @_;
1080   if (length($opt{'zip'})) {
1081     return "(SELECT zip FROM cust_location
1082              WHERE cust_location.locationnum = cust_pkg.locationnum
1083             ) = " . dbh->quote($opt{'zip'});
1084   }
1085   return;
1086 }
1087
1088 sub with_cust_classnum {
1089   my ($self, %opt) = @_;
1090   if ( $opt{'cust_classnum'} ) {
1091     my $classnums = $opt{'cust_classnum'};
1092     $classnums = [ $classnums ] if !ref($classnums);
1093     @$classnums = grep /^\d+$/, @$classnums;
1094     return 'cust_main.classnum in('. join(',',@$classnums) .')'
1095       if @$classnums;
1096   }
1097   return;
1098 }
1099
1100
1101 sub scalar_sql {
1102   my( $self, $sql ) = ( shift, shift );
1103   my $sth = dbh->prepare($sql) or die dbh->errstr;
1104   warn "FS::Report::Table\n$sql\n" if $DEBUG;
1105   $sth->execute
1106     or die "Unexpected error executing statement $sql: ". $sth->errstr;
1107   $sth->fetchrow_arrayref->[0] || 0;
1108 }
1109
1110 =back
1111
1112 =head1 METHODS
1113
1114 =over 4
1115
1116 =item init_projection
1117
1118 Sets up for future projection of all observables on the report.  Currently
1119 this is limited to 'cust_bill_pkg'.
1120
1121 =cut
1122
1123 sub init_projection {
1124   # this is weird special case stuff--some redesign may be needed
1125   # to use it for anything else
1126   my $self = shift;
1127
1128   if ( driver_name ne 'Pg' ) {
1129     # also database-specific for now
1130     die "projection reports not supported on this platform";
1131   }
1132
1133   my %items = map {$_ => 1} @{ $self->{items} };
1134   if ($items{'cust_bill_pkg'}) {
1135     my $dbh = dbh;
1136     # v_ for 'virtual'
1137     my @sql = (
1138       # could use TEMPORARY TABLE but we're already transaction-protected
1139       'DROP TABLE IF EXISTS v_cust_bill_pkg',
1140       'CREATE TABLE v_cust_bill_pkg ' .
1141        '(LIKE cust_bill_pkg,
1142           usage numeric(10,2), _date integer, expire integer)',
1143       # XXX this should be smart enough to take only the ones with
1144       # sdate/edate overlapping the ROI, for performance
1145       "INSERT INTO v_cust_bill_pkg (
1146         SELECT cust_bill_pkg.*,
1147           (SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
1148           FROM cust_bill_pkg_detail
1149           WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum),
1150           cust_bill._date,
1151           cust_pkg.expire
1152         FROM cust_bill_pkg $cust_bill_pkg_join
1153       )",
1154     );
1155     foreach my $sql (@sql) {
1156       warn "[init_projection] $sql\n" if $DEBUG;
1157       $dbh->do($sql) or die $dbh->errstr;
1158     }
1159   }
1160 }
1161
1162 =item extend_projection START END
1163
1164 Generates data for the next period of projection.  This will be called
1165 for sequential periods where the END of one equals the START of the next
1166 (with no gaps).
1167
1168 =cut
1169
1170 sub extend_projection {
1171   my $self = shift;
1172   my ($speriod, $eperiod) = @_;
1173   my %items = map {$_ => 1} @{ $self->{items} };
1174   if ($items{'cust_bill_pkg'}) {
1175     # What we do here:
1176     # Find all line items that end after the start of the period (and have
1177     # recurring fees, and don't expire before they end).  Choose the latest
1178     # one for each package.  If it ends before the end of the period, copy
1179     # it forward by one billing period.
1180     # Repeat this until the latest line item for each package no longer ends
1181     # within the period.  This is certain to happen in finitely many
1182     # iterations as long as freq > 0.
1183     # - Pg only, obviously.
1184     # - Gives bad results if freq_override is used.
1185     my @fields = ( FS::cust_bill_pkg->fields, qw( usage _date expire ) );
1186     my $insert_fields = join(',', @fields);
1187     my $add_freq = sub { # emulate FS::part_pkg::add_freq
1188       my $field = shift;
1189       "EXTRACT( EPOCH FROM TO_TIMESTAMP($field) + (CASE WHEN freq ~ E'\\\\D' ".
1190       "THEN freq ELSE freq || 'mon' END)::INTERVAL) AS $field";
1191     };
1192     foreach (@fields) {
1193       if ($_ eq 'edate') {
1194         $_ = $add_freq->('edate');
1195       }
1196       elsif ($_ eq 'sdate') {
1197         $_ = 'edate AS sdate'
1198       }
1199       elsif ($_ eq 'setup') {
1200         $_ = '0 AS setup' #because recurring only
1201       }
1202       elsif ($_ eq '_date') {
1203         $_ = $add_freq->('_date');
1204       }
1205     }
1206     my $select_fields = join(',', @fields);
1207     my $dbh = dbh;
1208     my $sql =
1209     # Subquery here because we need to DISTINCT the whole set, select the
1210     # latest charge per pkgnum, and _then_ check edate < $eperiod
1211     # and edate < expire.
1212       "INSERT INTO v_cust_bill_pkg ($insert_fields)
1213         SELECT $select_fields FROM (
1214           SELECT DISTINCT ON (pkgnum) * FROM v_cust_bill_pkg
1215             WHERE edate >= $speriod
1216               AND recur > 0
1217               AND freq IS NOT NULL
1218               AND freq != '0'
1219             ORDER BY pkgnum, edate DESC
1220           ) AS v1
1221           WHERE edate < $eperiod AND (edate < expire OR expire IS NULL)";
1222     my $rows;
1223     do {
1224       warn "[extend_projection] $sql\n" if $DEBUG;
1225       $rows = $dbh->do($sql) or die $dbh->errstr;
1226       warn "[extend_projection] $rows rows\n" if $DEBUG;
1227     } until $rows == 0;
1228   }
1229 }
1230
1231 =item _init_tower_pkg_cache
1232
1233 Internal method: creates a temporary table relating pkgnums to towernums.
1234 A (pkgnum, towernum) record indicates that this package once had a
1235 svc_broadband service which, as of its last insert or replace_new history
1236 record, had a sectornum associated with that towernum.
1237
1238 This is expensive, so it won't be done more than once an hour. Historical
1239 data about package churn shouldn't be changing in realtime anyway.
1240
1241 =cut
1242
1243 sub _init_tower_pkg_cache {
1244   my $self = shift;
1245   my $dbh = dbh;
1246
1247   my $current = $CACHE->get('tower_pkg_cache_update');
1248   return if $current;
1249
1250   # XXX or should this be in the schema?
1251   my $sql = "DROP TABLE IF EXISTS tower_pkg_cache";
1252   $dbh->do($sql) or die $dbh->errstr;
1253   $sql = "CREATE TABLE tower_pkg_cache (towernum int, pkgnum int)";
1254   $dbh->do($sql) or die $dbh->errstr;
1255
1256   # assumptions:
1257   # sectornums never get reused, or move from one tower to another
1258   # all service history is intact
1259   # svcnums never get reused (this would be bad)
1260   # pkgnums NEVER get reused (this would be extremely bad)
1261   $sql = "INSERT INTO tower_pkg_cache (
1262     SELECT COALESCE(towernum,0), pkgnum
1263     FROM ( SELECT DISTINCT pkgnum, svcnum FROM h_cust_svc ) AS pkgnum_svcnum
1264     LEFT JOIN (
1265       SELECT DISTINCT ON(svcnum) svcnum, sectornum
1266         FROM h_svc_broadband
1267         WHERE (history_action = 'replace_new'
1268                OR history_action = 'replace_old')
1269         ORDER BY svcnum ASC, history_date DESC
1270     ) AS svcnum_sectornum USING (svcnum)
1271     LEFT JOIN tower_sector USING (sectornum)
1272   )";
1273   $dbh->do($sql) or die $dbh->errstr;
1274
1275   $CACHE->set('tower_pkg_cache_update', 1, 3600);
1276
1277 };
1278
1279 =head1 BUGS
1280
1281 Documentation.
1282
1283 =head1 SEE ALSO
1284
1285 L<FS::Report::Table::Monthly>, reports in the web interface.
1286
1287 =cut
1288
1289 1;