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