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