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