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