FS/FS/Report/Table.pm

   1 package FS::Report::Table;
   2
   3 use strict;
   4 use vars qw( @ISA $DEBUG );
   5 use FS::Report;
   6 use Time::Local qw( timelocal );
   7 use FS::UID qw( dbh driver_name );
   8 use FS::Report::Table;
   9 use FS::CurrentUser;
  10
  11 $DEBUG = 0; # turning this on will trace all SQL statements, VERY noisy
  12 @ISA = qw( FS::Report );
  13
  14 =head1 NAME
  15
  16 FS::Report::Table - Tables of report data
  17
  18 =head1 SYNOPSIS
  19
  20 See the more specific report objects, currently only
  21 FS::Report::Table::Monthly and FS::Report::Table::Daily.
  22
  23 =head1 OBSERVABLES
  24
  25 The common interface for an observable named 'foo' is:
  26
  27 $report->foo($startdate, $enddate, $agentnum, %options)
  28
  29 This returns a scalar value for foo, over the period from
  30 $startdate to $enddate, limited to agent $agentnum, subject to
  31 options in %opt.
  32
  33 =over 4
  34
  35 =item signups: The number of customers signed up.
  36
  37 =cut
  38
  39 sub signups {
  40   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
  41   my @where = (
  42     $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, 'signupdate')
  43   );
  44   if ( $opt{'refnum'} ) {
  45     push @where, "refnum = ".$opt{'refnum'};
  46   }
  47
  48   $self->scalar_sql(
  49     "SELECT COUNT(*) FROM cust_main WHERE ".join(' AND ', @where)
  50   );
  51 }
  52
  53 =item invoiced: The total amount charged on all invoices.
  54
  55 =cut
  56
  57 sub invoiced { #invoiced
  58   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
  59
  60   $self->scalar_sql("
  61     SELECT SUM(charged)
  62       FROM cust_bill
  63         LEFT JOIN cust_main USING ( custnum )
  64       WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
  65       . (%opt ? $self->for_custnum(%opt) : '')
  66   );
  67
  68 }
  69
  70 =item netsales: invoiced - netcredits
  71
  72 =cut
  73
  74 sub netsales { #net sales
  75   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
  76
  77     $self->invoiced($speriod,$eperiod,$agentnum,%opt)
  78   - $self->netcredits($speriod,$eperiod,$agentnum,%opt);
  79 }
  80
  81 =item cashflow: payments - refunds
  82
  83 =cut
  84
  85 sub cashflow {
  86   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
  87
  88     $self->payments($speriod, $eperiod, $agentnum, %opt)
  89   - $self->refunds( $speriod, $eperiod, $agentnum, %opt);
  90 }
  91
  92 =item netcashflow: payments - netrefunds
  93
  94 =cut
  95
  96 sub netcashflow {
  97   my( $self, $speriod, $eperiod, $agentnum ) = @_;
  98
  99     $self->receipts($speriod, $eperiod, $agentnum)
 100   - $self->netrefunds( $speriod, $eperiod, $agentnum);
 101 }
 102
 103 =item payments: The sum of payments received in the period.
 104
 105 =cut
 106
 107 sub payments {
 108   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 109   $self->scalar_sql("
 110     SELECT SUM(paid)
 111       FROM cust_pay
 112         LEFT JOIN cust_main USING ( custnum )
 113       WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
 114       . (%opt ? $self->for_custnum(%opt) : '')
 115   );
 116 }
 117
 118 =item credits: The sum of credits issued in the period.
 119
 120 =cut
 121
 122 sub credits {
 123   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 124   $self->scalar_sql("
 125     SELECT SUM(amount)
 126       FROM cust_credit
 127         LEFT JOIN cust_main USING ( custnum )
 128       WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
 129   );
 130 }
 131
 132 =item refunds: The sum of refunds paid in the period.
 133
 134 =cut
 135
 136 sub refunds {
 137   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 138   $self->scalar_sql("
 139     SELECT SUM(refund)
 140       FROM cust_refund
 141         LEFT JOIN cust_main USING ( custnum )
 142       WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
 143       . (%opt ? $self->for_custnum(%opt) : '')
 144   );
 145 }
 146
 147 =item netcredits: The sum of credit applications to invoices in the period.
 148
 149 =cut
 150
 151 sub netcredits {
 152   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 153   $self->scalar_sql("
 154     SELECT SUM(cust_credit_bill.amount)
 155       FROM cust_credit_bill
 156         LEFT JOIN cust_bill USING ( invnum  )
 157         LEFT JOIN cust_main USING ( custnum )
 158       WHERE ". $self->in_time_period_and_agent( $speriod,
 159                                                 $eperiod,
 160                                                 $agentnum,
 161                                                 'cust_bill._date'
 162                                               )
 163       . (%opt ? $self->for_custnum(%opt) : '')
 164   );
 165 }
 166
 167 =item receipts: The sum of payment applications to invoices in the period.
 168
 169 =cut
 170
 171 sub receipts { #net payments
 172   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 173   $self->scalar_sql("
 174     SELECT SUM(cust_bill_pay.amount)
 175       FROM cust_bill_pay
 176         LEFT JOIN cust_bill USING ( invnum  )
 177         LEFT JOIN cust_main USING ( custnum )
 178       WHERE ". $self->in_time_period_and_agent( $speriod,
 179                                                 $eperiod,
 180                                                 $agentnum,
 181                                                 'cust_bill._date'
 182                                               )
 183   );
 184 }
 185
 186 =item netrefunds: The sum of refund applications to credits in the period.
 187
 188 =cut
 189
 190 sub netrefunds {
 191   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 192   $self->scalar_sql("
 193     SELECT SUM(cust_credit_refund.amount)
 194       FROM cust_credit_refund
 195         LEFT JOIN cust_credit USING ( crednum  )
 196         LEFT JOIN cust_main   USING ( custnum )
 197       WHERE ". $self->in_time_period_and_agent( $speriod,
 198                                                 $eperiod,
 199                                                 $agentnum,
 200                                                 'cust_credit._date'
 201                                               )
 202   );
 203 }
 204
 205 #XXX docs
 206
 207 #these should be auto-generated or $AUTOLOADed or something
 208 sub invoiced_12mo {
 209   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 210   $speriod = $self->_subtract_11mo($speriod);
 211   $self->invoiced($speriod, $eperiod, $agentnum);
 212 }
 213
 214 sub netsales_12mo {
 215   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 216   $speriod = $self->_subtract_11mo($speriod);
 217   $self->netsales($speriod, $eperiod, $agentnum);
 218 }
 219
 220 sub receipts_12mo {
 221   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 222   $speriod = $self->_subtract_11mo($speriod);
 223   $self->receipts($speriod, $eperiod, $agentnum);
 224 }
 225
 226 sub payments_12mo {
 227   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 228   $speriod = $self->_subtract_11mo($speriod);
 229   $self->payments($speriod, $eperiod, $agentnum);
 230 }
 231
 232 sub credits_12mo {
 233   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 234   $speriod = $self->_subtract_11mo($speriod);
 235   $self->credits($speriod, $eperiod, $agentnum);
 236 }
 237
 238 sub netcredits_12mo {
 239   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 240   $speriod = $self->_subtract_11mo($speriod);
 241   $self->netcredits($speriod, $eperiod, $agentnum);
 242 }
 243
 244 sub cashflow_12mo {
 245   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 246   $speriod = $self->_subtract_11mo($speriod);
 247   $self->cashflow($speriod, $eperiod, $agentnum);
 248 }
 249
 250 sub netcashflow_12mo {
 251   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 252   $speriod = $self->_subtract_11mo($speriod);
 253   $self->cashflow($speriod, $eperiod, $agentnum);
 254 }
 255
 256 sub refunds_12mo {
 257   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 258   $speriod = $self->_subtract_11mo($speriod);
 259   $self->refunds($speriod, $eperiod, $agentnum);
 260 }
 261
 262 sub netrefunds_12mo {
 263   my( $self, $speriod, $eperiod, $agentnum ) = @_;
 264   $speriod = $self->_subtract_11mo($speriod);
 265   $self->netrefunds($speriod, $eperiod, $agentnum);
 266 }
 267
 268
 269 #not being too bad with the false laziness
 270 sub _subtract_11mo {
 271   my($self, $time) = @_;
 272   my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
 273   $mon -= 11;
 274   if ( $mon < 0 ) { $mon+=12; $year--; }
 275   timelocal($sec,$min,$hour,$mday,$mon,$year);
 276 }
 277
 278 =item cust_pkg_setup_cost: The total setup costs of packages setup in the period
 279
 280 'classnum': limit to this package class.
 281
 282 =cut
 283
 284 sub cust_pkg_setup_cost {
 285   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 286   my $where = '';
 287   my $comparison = '';
 288   if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
 289     if ( $1 == 0 ) {
 290       $comparison = 'IS NULL';
 291     }
 292     else {
 293       $comparison = "= $1";
 294     }
 295     $where = "AND part_pkg.classnum $comparison";
 296   }
 297   $agentnum ||= $opt{'agentnum'};
 298
 299   my $total_sql = " SELECT SUM(part_pkg.setup_cost) ";
 300   $total_sql .= " FROM cust_pkg
 301              LEFT JOIN cust_main USING ( custnum )
 302              LEFT JOIN part_pkg  USING ( pkgpart )
 303                   WHERE pkgnum != 0
 304                   $where
 305                   AND ".$self->in_time_period_and_agent(
 306                     $speriod, $eperiod, $agentnum, 'cust_pkg.setup');
 307   return $self->scalar_sql($total_sql);
 308 }
 309
 310 =item cust_pkg_recur_cust: the total recur costs of packages in the period
 311
 312 'classnum': limit to this package class.
 313
 314 =cut
 315
 316 sub cust_pkg_recur_cost {
 317   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 318   my $where = '';
 319   my $comparison = '';
 320   if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
 321     if ( $1 == 0 ) {
 322       $comparison = 'IS NULL';
 323     }
 324     else {
 325       $comparison = "= $1";
 326     }
 327     $where = " AND part_pkg.classnum $comparison";
 328   }
 329   $agentnum ||= $opt{'agentnum'};
 330   # duplication of in_time_period_and_agent
 331   # because we do it a little differently here
 332   $where .= " AND cust_main.agentnum = $agentnum" if $agentnum;
 333   $where .= " AND ".
 334           $FS::CurrentUser::CurrentUser->agentnums_sql('table' => 'cust_main');
 335
 336   my $total_sql = " SELECT SUM(part_pkg.recur_cost) ";
 337   $total_sql .= " FROM cust_pkg
 338              LEFT JOIN cust_main USING ( custnum )
 339              LEFT JOIN part_pkg  USING ( pkgpart )
 340                   WHERE pkgnum != 0
 341                   $where
 342                   AND cust_pkg.setup < $eperiod
 343                   AND (cust_pkg.cancel > $speriod OR cust_pkg.cancel IS NULL)
 344                   ";
 345   return $self->scalar_sql($total_sql);
 346 }
 347
 348 =item cust_bill_pkg: the total package charges on invoice line items.
 349
 350 'charges': limit the type of charges included (setup, recur, usage).
 351 Should be a string containing one or more of 'S', 'R', or 'U'; if
 352 unspecified, defaults to all three.
 353
 354 'classnum': limit to this package class.
 355
 356 'use_override': for line items generated by an add-on package, use the class
 357 of the add-on rather than the base package.
 358
 359 'freq': limit to packages with this frequency.  Currently uses the part_pkg
 360 frequency, so term discounted packages may give odd results.
 361
 362 'distribute': for non-monthly recurring charges, ignore the invoice
 363 date.  Instead, consider the line item's starting/ending dates.  Determine
 364 the fraction of the line item duration that falls within the specified
 365 interval and return that fraction of the recurring charges.  This is
 366 somewhat experimental.
 367
 368 'project': enable if this is a projected period.  This is very experimental.
 369
 370 =cut
 371
 372 sub cust_bill_pkg {
 373   my $self = shift;
 374   my( $speriod, $eperiod, $agentnum, %opt ) = @_;
 375
 376   my %charges = map {$_=>1} split('', $opt{'charges'} || 'SRU');
 377
 378   my $sum = 0;
 379   $sum += $self->cust_bill_pkg_setup(@_) if $charges{S};
 380   $sum += $self->cust_bill_pkg_recur(@_) if $charges{R};
 381   $sum += $self->cust_bill_pkg_detail(@_) if $charges{U};
 382   $sum;
 383 }
 384
 385 my $cust_bill_pkg_join = '
 386     LEFT JOIN cust_bill USING ( invnum )
 387     LEFT JOIN cust_main USING ( custnum )
 388     LEFT JOIN cust_pkg USING ( pkgnum )
 389     LEFT JOIN part_pkg USING ( pkgpart )
 390     LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart';
 391
 392 sub cust_bill_pkg_setup {
 393   my $self = shift;
 394   my ($speriod, $eperiod, $agentnum, %opt) = @_;
 395   # no projecting setup fees--use real invoices only
 396   # but evaluate this anyway, because the design of projection is that
 397   # if there are somehow real setup fees in the future, we want to count
 398   # them
 399
 400   $agentnum ||= $opt{'agentnum'};
 401
 402   my @where = (
 403     'pkgnum != 0',
 404     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
 405     $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
 406   );
 407
 408   my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0)
 409   FROM cust_bill_pkg
 410   $cust_bill_pkg_join
 411   WHERE " . join(' AND ', grep $_, @where);
 412
 413   $self->scalar_sql($total_sql);
 414 }
 415
 416 sub cust_bill_pkg_recur {
 417   my $self = shift;
 418   my ($speriod, $eperiod, $agentnum, %opt) = @_;
 419
 420   $agentnum ||= $opt{'agentnum'};
 421   my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
 422
 423   my @where = (
 424     'pkgnum != 0',
 425     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
 426   );
 427
 428   # subtract all usage from the line item regardless of date
 429   my $item_usage;
 430   if ( $opt{'project'} ) {
 431     $item_usage = 'usage'; #already calculated
 432   }
 433   else {
 434     $item_usage = '( SELECT COALESCE(SUM(amount),0)
 435       FROM cust_bill_pkg_detail
 436       WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
 437   }
 438   my $recur_fraction = '';
 439
 440   if ( $opt{'distribute'} ) {
 441     push @where, "cust_main.agentnum = $agentnum" if $agentnum;
 442     push @where,
 443       "$cust_bill_pkg.sdate <  $eperiod",
 444       "$cust_bill_pkg.edate >= $speriod",
 445     ;
 446     # the fraction of edate - sdate that's within [speriod, eperiod]
 447     $recur_fraction = " *
 448       CAST(LEAST($eperiod, $cust_bill_pkg.edate) -
 449        GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) /
 450       ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
 451   }
 452   else {
 453     # we don't want to have to create v_cust_bill
 454     my $_date = $opt{'project'} ? 'v_cust_bill_pkg._date' : 'cust_bill._date';
 455     push @where,
 456       $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date);
 457   }
 458
 459   my $total_sql = 'SELECT '.
 460   "COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)
 461   FROM $cust_bill_pkg
 462   $cust_bill_pkg_join
 463   WHERE ".join(' AND ', grep $_, @where);
 464
 465   $self->scalar_sql($total_sql);
 466 }
 467
 468 =item cust_bill_pkg_detail: the total usage charges in detail lines.
 469
 470 Arguments as for C<cust_bill_pkg>, plus:
 471
 472 'usageclass': limit to this usage class number.
 473
 474 =cut
 475
 476 sub cust_bill_pkg_detail {
 477   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 478
 479   my @where = ( "cust_bill_pkg.pkgnum != 0" );
 480
 481   $agentnum ||= $opt{'agentnum'};
 482
 483   push @where,
 484     $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
 485     $self->with_usageclass($opt{'usageclass'}),
 486     ;
 487
 488   if ( $opt{'distribute'} ) {
 489     # then limit according to the usage time, not the billing date
 490     push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
 491       'cust_bill_pkg_detail.startdate'
 492     );
 493   }
 494   else {
 495     push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
 496       'cust_bill._date'
 497     );
 498   }
 499
 500   my $total_sql = " SELECT SUM(amount) ";
 501
 502   $total_sql .=
 503     " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
 504       if $opt{average_per_cust_pkg};
 505
 506   $total_sql .=
 507     " FROM cust_bill_pkg_detail
 508         LEFT JOIN cust_bill_pkg USING ( billpkgnum )
 509         LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
 510         LEFT JOIN cust_main USING ( custnum )
 511         LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
 512         LEFT JOIN part_pkg USING ( pkgpart )
 513         LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
 514       WHERE ".join( ' AND ', grep $_, @where );
 515
 516   $self->scalar_sql($total_sql);
 517
 518 }
 519
 520 sub cust_bill_pkg_discount {
 521   my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
 522
 523   #my $where = '';
 524   #my $comparison = '';
 525   #if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
 526   #  if ( $1 == 0 ) {
 527   #    $comparison = "IS NULL";
 528   #  } else {
 529   #    $comparison = "= $1";
 530   #  }
 531   #
 532   #  if ( $opt{'use_override'} ) {
 533   #    $where = "(
 534   #      part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
 535   #      override.classnum $comparison AND pkgpart_override IS NOT NULL
 536   #    )";
 537   #  } else {
 538   #    $where = "part_pkg.classnum $comparison";
 539   #  }
 540   #}
 541
 542   $agentnum ||= $opt{'agentnum'};
 543
 544   my $total_sql =
 545     " SELECT COALESCE( SUM( cust_bill_pkg_discount.amount ), 0 ) ";
 546
 547   #$total_sql .=
 548   #  " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
 549   #    if $opt{average_per_cust_pkg};
 550
 551   $total_sql .=
 552     " FROM cust_bill_pkg_discount
 553         LEFT JOIN cust_bill_pkg USING ( billpkgnum )
 554         LEFT JOIN cust_bill USING ( invnum )
 555         LEFT JOIN cust_main USING ( custnum )
 556       WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
 557   #      LEFT JOIN cust_pkg_discount USING ( pkgdiscountnum )
 558   #      LEFT JOIN discount USING ( discountnum )
 559   #      LEFT JOIN cust_pkg USING ( pkgnum )
 560   #      LEFT JOIN part_pkg USING ( pkgpart )
 561   #      LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
 562
 563   return $self->scalar_sql($total_sql);
 564
 565 }
 566
 567 sub setup_pkg  { shift->pkg_field( 'setup',  @_ ); }
 568 sub susp_pkg   { shift->pkg_field( 'susp',   @_ ); }
 569 sub cancel_pkg { shift->pkg_field( 'cancel', @_ ); }
 570
 571 sub pkg_field {
 572   my( $self, $field, $speriod, $eperiod, $agentnum ) = @_;
 573   $self->scalar_sql("
 574     SELECT COUNT(*) FROM cust_pkg
 575         LEFT JOIN cust_main USING ( custnum )
 576       WHERE ". $self->in_time_period_and_agent( $speriod,
 577                                                 $eperiod,
 578                                                 $agentnum,
 579                                                 "cust_pkg.$field",
 580                                               )
 581   );
 582
 583 }
 584
 585 #this is going to be harder..
 586 #sub unsusp_pkg {
 587 #  my( $self, $speriod, $eperiod, $agentnum ) = @_;
 588 #  $self->scalar_sql("
 589 #    SELECT COUNT(*) FROM h_cust_pkg
 590 #      WHERE
 591 #
 592 #}
 593
 594 sub in_time_period_and_agent {
 595   my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
 596   my $col = @_ ? shift() : '_date';
 597
 598   my $sql = "$col >= $speriod AND $col < $eperiod";
 599
 600   #agent selection
 601   $sql .= " AND cust_main.agentnum = $agentnum"
 602     if $agentnum;
 603
 604   #agent virtualization
 605   $sql .= ' AND '.
 606           $FS::CurrentUser::CurrentUser->agentnums_sql( 'table'=>'cust_main' );
 607
 608   $sql;
 609 }
 610
 611 sub for_custnum {
 612     my ( $self, %opt ) = @_;
 613     return '' unless $opt{'custnum'};
 614     $opt{'custnum'} =~ /^\d+$/ ? " and custnum = $opt{custnum} " : '';
 615 }
 616
 617 sub with_classnum {
 618   my $self = shift;
 619   my ($classnum, $use_override) = @_;
 620   return '' unless $classnum =~ /^\d+$/;
 621   my $comparison;
 622   if ( $classnum == 0 ) {
 623     $comparison = 'IS NULL';
 624   }
 625   else {
 626     $comparison = "= $classnum";
 627   }
 628   if ( $use_override ) {
 629     return "(
 630       part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
 631       override.classnum $comparison AND pkgpart_override IS NOT NULL
 632     )";
 633   }
 634   else {
 635     return "part_pkg.classnum $comparison";
 636   }
 637 }
 638
 639 sub with_usageclass {
 640   my $self = shift;
 641   my ($classnum, $use_override) = @_;
 642   return '' unless $classnum =~ /^\d+$/;
 643   my $comparison;
 644   if ( $classnum == 0 ) {
 645     $comparison = 'IS NULL';
 646   }
 647   else {
 648     $comparison = "= $classnum";
 649   }
 650   return "cust_bill_pkg_detail.classnum $comparison";
 651 }
 652
 653 sub scalar_sql {
 654   my( $self, $sql ) = ( shift, shift );
 655   my $sth = dbh->prepare($sql) or die dbh->errstr;
 656   warn "FS::Report::Table\n$sql\n" if $DEBUG;
 657   $sth->execute
 658     or die "Unexpected error executing statement $sql: ". $sth->errstr;
 659   $sth->fetchrow_arrayref->[0] || 0;
 660 }
 661
 662 =back
 663
 664 =head1 METHODS
 665
 666 =over 4
 667
 668 =item init_projection
 669
 670 Sets up for future projection of all observables on the report.  Currently
 671 this is limited to 'cust_bill_pkg'.
 672
 673 =cut
 674
 675 sub init_projection {
 676   # this is weird special case stuff--some redesign may be needed
 677   # to use it for anything else
 678   my $self = shift;
 679
 680   if ( driver_name ne 'Pg' ) {
 681     # also database-specific for now
 682     die "projection reports not supported on this platform";
 683   }
 684
 685   my %items = map {$_ => 1} @{ $self->{items} };
 686   if ($items{'cust_bill_pkg'}) {
 687     my $dbh = dbh;
 688     # v_ for 'virtual'
 689     my @sql = (
 690       # could use TEMPORARY TABLE but we're already transaction-protected
 691       'DROP TABLE IF EXISTS v_cust_bill_pkg',
 692       'CREATE TABLE v_cust_bill_pkg ' .
 693        '(LIKE cust_bill_pkg,
 694           usage numeric(10,2), _date integer, expire integer)',
 695       # XXX this should be smart enough to take only the ones with
 696       # sdate/edate overlapping the ROI, for performance
 697       "INSERT INTO v_cust_bill_pkg (
 698         SELECT cust_bill_pkg.*,
 699           (SELECT COALESCE(SUM(amount),0) FROM cust_bill_pkg_detail
 700           WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum),
 701           cust_bill._date,
 702           cust_pkg.expire
 703         FROM cust_bill_pkg $cust_bill_pkg_join
 704       )",
 705     );
 706     foreach my $sql (@sql) {
 707       warn "[init_projection] $sql\n" if $DEBUG;
 708       $dbh->do($sql) or die $dbh->errstr;
 709     }
 710   }
 711 }
 712
 713 =item extend_projection START END
 714
 715 Generates data for the next period of projection.  This will be called
 716 for sequential periods where the END of one equals the START of the next
 717 (with no gaps).
 718
 719 =cut
 720
 721 sub extend_projection {
 722   my $self = shift;
 723   my ($speriod, $eperiod) = @_;
 724   my %items = map {$_ => 1} @{ $self->{items} };
 725   if ($items{'cust_bill_pkg'}) {
 726     # What we do here:
 727     # Find all line items that end after the start of the period (and have
 728     # recurring fees, and don't expire before they end).  Choose the latest
 729     # one for each package.  If it ends before the end of the period, copy
 730     # it forward by one billing period.
 731     # Repeat this until the latest line item for each package no longer ends
 732     # within the period.  This is certain to happen in finitely many
 733     # iterations as long as freq > 0.
 734     # - Pg only, obviously.
 735     # - Gives bad results if freq_override is used.
 736     my @fields = ( FS::cust_bill_pkg->fields, qw( usage _date expire ) );
 737     my $insert_fields = join(',', @fields);
 738     my $add_freq = sub { # emulate FS::part_pkg::add_freq
 739       my $field = shift;
 740       "EXTRACT( EPOCH FROM TO_TIMESTAMP($field) + (CASE WHEN freq ~ E'\\\\D' ".
 741       "THEN freq ELSE freq || 'mon' END)::INTERVAL) AS $field";
 742     };
 743     foreach (@fields) {
 744       if ($_ eq 'edate') {
 745         $_ = $add_freq->('edate');
 746       }
 747       elsif ($_ eq 'sdate') {
 748         $_ = 'edate AS sdate'
 749       }
 750       elsif ($_ eq 'setup') {
 751         $_ = '0 AS setup' #because recurring only
 752       }
 753       elsif ($_ eq '_date') {
 754         $_ = $add_freq->('_date');
 755       }
 756     }
 757     my $select_fields = join(',', @fields);
 758     my $dbh = dbh;
 759     my $sql =
 760     # Subquery here because we need to DISTINCT the whole set, select the
 761     # latest charge per pkgnum, and _then_ check edate < $eperiod
 762     # and edate < expire.
 763       "INSERT INTO v_cust_bill_pkg ($insert_fields)
 764         SELECT $select_fields FROM (
 765           SELECT DISTINCT ON (pkgnum) * FROM v_cust_bill_pkg
 766             WHERE edate >= $speriod
 767               AND recur > 0
 768               AND freq IS NOT NULL
 769               AND freq != '0'
 770             ORDER BY pkgnum, edate DESC
 771           ) AS v1
 772           WHERE edate < $eperiod AND (edate < expire OR expire IS NULL)";
 773     my $rows;
 774     do {
 775       warn "[extend_projection] $sql\n" if $DEBUG;
 776       $rows = $dbh->do($sql) or die $dbh->errstr;
 777       warn "[extend_projection] $rows rows\n" if $DEBUG;
 778     } until $rows == 0;
 779   }
 780 }
 781
 782 =head1 BUGS
 783
 784 Documentation.
 785
 786 =head1 SEE ALSO
 787
 788 L<FS::Report::Table::Monthly>, reports in the web interface.
 789
 790 =cut
 791
 792 1;