FS/FS/Report/Table.pm

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