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