1 package FS::Report::Table;
4 use vars qw( @ISA $DEBUG );
6 use Time::Local qw( timelocal );
7 use FS::UID qw( dbh driver_name );
11 $DEBUG = 0; # turning this on will trace all SQL statements, VERY noisy
12 @ISA = qw( FS::Report );
16 FS::Report::Table - Tables of report data
20 See the more specific report objects, currently only
21 FS::Report::Table::Monthly and FS::Report::Table::Daily.
25 The common interface for an observable named 'foo' is:
27 $report->foo($startdate, $enddate, $agentnum, %options)
29 This returns a scalar value for foo, over the period from
30 $startdate to $enddate, limited to agent $agentnum, subject to
35 =item signups: The number of customers signed up.
40 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
42 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, 'signupdate')
44 if ( $opt{'refnum'} ) {
45 push @where, "refnum = ".$opt{'refnum'};
49 "SELECT COUNT(*) FROM cust_main WHERE ".join(' AND ', @where)
53 =item invoiced: The total amount charged on all invoices.
57 sub invoiced { #invoiced
58 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
63 LEFT JOIN cust_main USING ( custnum )
64 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
65 . (%opt ? $self->for_custnum(%opt) : '')
70 =item netsales: invoiced - netcredits
74 sub netsales { #net sales
75 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
77 $self->invoiced($speriod,$eperiod,$agentnum,%opt)
78 - $self->netcredits($speriod,$eperiod,$agentnum,%opt);
81 =item cashflow: payments - refunds
86 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
88 $self->payments($speriod, $eperiod, $agentnum, %opt)
89 - $self->refunds( $speriod, $eperiod, $agentnum, %opt);
92 =item netcashflow: payments - netrefunds
97 my( $self, $speriod, $eperiod, $agentnum ) = @_;
99 $self->receipts($speriod, $eperiod, $agentnum)
100 - $self->netrefunds( $speriod, $eperiod, $agentnum);
103 =item payments: The sum of payments received in the period.
108 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
112 LEFT JOIN cust_main USING ( custnum )
113 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
114 . (%opt ? $self->for_custnum(%opt) : '')
118 =item credits: The sum of credits issued in the period.
123 my( $self, $speriod, $eperiod, $agentnum ) = @_;
127 LEFT JOIN cust_main USING ( custnum )
128 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
132 =item refunds: The sum of refunds paid in the period.
137 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
141 LEFT JOIN cust_main USING ( custnum )
142 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
143 . (%opt ? $self->for_custnum(%opt) : '')
147 =item netcredits: The sum of credit applications to invoices in the period.
152 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
154 SELECT SUM(cust_credit_bill.amount)
155 FROM cust_credit_bill
156 LEFT JOIN cust_bill USING ( invnum )
157 LEFT JOIN cust_main USING ( custnum )
158 WHERE ". $self->in_time_period_and_agent( $speriod,
163 . (%opt ? $self->for_custnum(%opt) : '')
167 =item receipts: The sum of payment applications to invoices in the period.
171 sub receipts { #net payments
172 my( $self, $speriod, $eperiod, $agentnum ) = @_;
174 SELECT SUM(cust_bill_pay.amount)
176 LEFT JOIN cust_bill USING ( invnum )
177 LEFT JOIN cust_main USING ( custnum )
178 WHERE ". $self->in_time_period_and_agent( $speriod,
186 =item netrefunds: The sum of refund applications to credits in the period.
191 my( $self, $speriod, $eperiod, $agentnum ) = @_;
193 SELECT SUM(cust_credit_refund.amount)
194 FROM cust_credit_refund
195 LEFT JOIN cust_credit USING ( crednum )
196 LEFT JOIN cust_main USING ( custnum )
197 WHERE ". $self->in_time_period_and_agent( $speriod,
207 #these should be auto-generated or $AUTOLOADed or something
209 my( $self, $speriod, $eperiod, $agentnum ) = @_;
210 $speriod = $self->_subtract_11mo($speriod);
211 $self->invoiced($speriod, $eperiod, $agentnum);
215 my( $self, $speriod, $eperiod, $agentnum ) = @_;
216 $speriod = $self->_subtract_11mo($speriod);
217 $self->netsales($speriod, $eperiod, $agentnum);
221 my( $self, $speriod, $eperiod, $agentnum ) = @_;
222 $speriod = $self->_subtract_11mo($speriod);
223 $self->receipts($speriod, $eperiod, $agentnum);
227 my( $self, $speriod, $eperiod, $agentnum ) = @_;
228 $speriod = $self->_subtract_11mo($speriod);
229 $self->payments($speriod, $eperiod, $agentnum);
233 my( $self, $speriod, $eperiod, $agentnum ) = @_;
234 $speriod = $self->_subtract_11mo($speriod);
235 $self->credits($speriod, $eperiod, $agentnum);
238 sub netcredits_12mo {
239 my( $self, $speriod, $eperiod, $agentnum ) = @_;
240 $speriod = $self->_subtract_11mo($speriod);
241 $self->netcredits($speriod, $eperiod, $agentnum);
245 my( $self, $speriod, $eperiod, $agentnum ) = @_;
246 $speriod = $self->_subtract_11mo($speriod);
247 $self->cashflow($speriod, $eperiod, $agentnum);
250 sub netcashflow_12mo {
251 my( $self, $speriod, $eperiod, $agentnum ) = @_;
252 $speriod = $self->_subtract_11mo($speriod);
253 $self->cashflow($speriod, $eperiod, $agentnum);
257 my( $self, $speriod, $eperiod, $agentnum ) = @_;
258 $speriod = $self->_subtract_11mo($speriod);
259 $self->refunds($speriod, $eperiod, $agentnum);
262 sub netrefunds_12mo {
263 my( $self, $speriod, $eperiod, $agentnum ) = @_;
264 $speriod = $self->_subtract_11mo($speriod);
265 $self->netrefunds($speriod, $eperiod, $agentnum);
269 #not being too bad with the false laziness
271 my($self, $time) = @_;
272 my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
274 if ( $mon < 0 ) { $mon+=12; $year--; }
275 timelocal($sec,$min,$hour,$mday,$mon,$year);
278 =item cust_pkg_setup_cost: The total setup costs of packages setup in the period
280 'classnum': limit to this package class.
284 sub cust_pkg_setup_cost {
285 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
288 if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
290 $comparison = 'IS NULL';
293 $comparison = "= $1";
295 $where = "AND part_pkg.classnum $comparison";
297 $agentnum ||= $opt{'agentnum'};
299 my $total_sql = " SELECT SUM(part_pkg.setup_cost) ";
300 $total_sql .= " FROM cust_pkg
301 LEFT JOIN cust_main USING ( custnum )
302 LEFT JOIN part_pkg USING ( pkgpart )
305 AND ".$self->in_time_period_and_agent(
306 $speriod, $eperiod, $agentnum, 'cust_pkg.setup');
307 return $self->scalar_sql($total_sql);
310 =item cust_pkg_recur_cust: the total recur costs of packages in the period
312 'classnum': limit to this package class.
316 sub cust_pkg_recur_cost {
317 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
320 if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
322 $comparison = 'IS NULL';
325 $comparison = "= $1";
327 $where = " AND part_pkg.classnum $comparison";
329 $agentnum ||= $opt{'agentnum'};
330 # duplication of in_time_period_and_agent
331 # because we do it a little differently here
332 $where .= " AND cust_main.agentnum = $agentnum" if $agentnum;
334 $FS::CurrentUser::CurrentUser->agentnums_sql('table' => 'cust_main');
336 my $total_sql = " SELECT SUM(part_pkg.recur_cost) ";
337 $total_sql .= " FROM cust_pkg
338 LEFT JOIN cust_main USING ( custnum )
339 LEFT JOIN part_pkg USING ( pkgpart )
342 AND cust_pkg.setup < $eperiod
343 AND (cust_pkg.cancel > $speriod OR cust_pkg.cancel IS NULL)
345 return $self->scalar_sql($total_sql);
348 =item cust_bill_pkg: the total package charges on invoice line items.
350 'charges': limit the type of charges included (setup, recur, usage).
351 Should be a string containing one or more of 'S', 'R', or 'U'; if
352 unspecified, defaults to all three.
354 'classnum': limit to this package class.
356 'use_override': for line items generated by an add-on package, use the class
357 of the add-on rather than the base package.
359 'freq': limit to packages with this frequency. Currently uses the part_pkg
360 frequency, so term discounted packages may give odd results.
362 'distribute': for non-monthly recurring charges, ignore the invoice
363 date. Instead, consider the line item's starting/ending dates. Determine
364 the fraction of the line item duration that falls within the specified
365 interval and return that fraction of the recurring charges. This is
366 somewhat experimental.
368 'project': enable if this is a projected period. This is very experimental.
374 my( $speriod, $eperiod, $agentnum, %opt ) = @_;
376 my %charges = map {$_=>1} split('', $opt{'charges'} || 'SRU');
379 $sum += $self->cust_bill_pkg_setup(@_) if $charges{S};
380 $sum += $self->cust_bill_pkg_recur(@_) if $charges{R};
381 $sum += $self->cust_bill_pkg_detail(@_) if $charges{U};
385 my $cust_bill_pkg_join = '
386 LEFT JOIN cust_bill USING ( invnum )
387 LEFT JOIN cust_main USING ( custnum )
388 LEFT JOIN cust_pkg USING ( pkgnum )
389 LEFT JOIN part_pkg USING ( pkgpart )
390 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart';
392 sub cust_bill_pkg_setup {
394 my ($speriod, $eperiod, $agentnum, %opt) = @_;
395 # no projecting setup fees--use real invoices only
396 # but evaluate this anyway, because the design of projection is that
397 # if there are somehow real setup fees in the future, we want to count
400 $agentnum ||= $opt{'agentnum'};
404 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
405 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
408 my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0)
411 WHERE " . join(' AND ', grep $_, @where);
413 $self->scalar_sql($total_sql);
416 sub cust_bill_pkg_recur {
418 my ($speriod, $eperiod, $agentnum, %opt) = @_;
420 $agentnum ||= $opt{'agentnum'};
421 my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
425 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
428 # subtract all usage from the line item regardless of date
430 if ( $opt{'project'} ) {
431 $item_usage = 'usage'; #already calculated
434 $item_usage = '( SELECT COALESCE(SUM(amount),0)
435 FROM cust_bill_pkg_detail
436 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
438 my $recur_fraction = '';
440 if ( $opt{'distribute'} ) {
441 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
443 "$cust_bill_pkg.sdate < $eperiod",
444 "$cust_bill_pkg.edate >= $speriod",
446 # the fraction of edate - sdate that's within [speriod, eperiod]
447 $recur_fraction = " *
448 CAST(LEAST($eperiod, $cust_bill_pkg.edate) -
449 GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) /
450 ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
453 # we don't want to have to create v_cust_bill
454 my $_date = $opt{'project'} ? 'v_cust_bill_pkg._date' : 'cust_bill._date';
456 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date);
459 my $total_sql = 'SELECT '.
460 "COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)
463 WHERE ".join(' AND ', grep $_, @where);
465 $self->scalar_sql($total_sql);
468 =item cust_bill_pkg_detail: the total usage charges in detail lines.
470 Arguments as for C<cust_bill_pkg>, plus:
472 'usageclass': limit to this usage class number.
476 sub cust_bill_pkg_detail {
477 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
479 my @where = ( "cust_bill_pkg.pkgnum != 0" );
481 $agentnum ||= $opt{'agentnum'};
484 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
485 $self->with_usageclass($opt{'usageclass'}),
488 if ( $opt{'distribute'} ) {
489 # then limit according to the usage time, not the billing date
490 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
491 'cust_bill_pkg_detail.startdate'
495 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
500 my $total_sql = " SELECT SUM(amount) ";
503 " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
504 if $opt{average_per_cust_pkg};
507 " FROM cust_bill_pkg_detail
508 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
509 LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
510 LEFT JOIN cust_main USING ( custnum )
511 LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
512 LEFT JOIN part_pkg USING ( pkgpart )
513 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
514 WHERE ".join( ' AND ', grep $_, @where );
516 $self->scalar_sql($total_sql);
520 sub cust_bill_pkg_discount {
521 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
524 #my $comparison = '';
525 #if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
527 # $comparison = "IS NULL";
529 # $comparison = "= $1";
532 # if ( $opt{'use_override'} ) {
534 # part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
535 # override.classnum $comparison AND pkgpart_override IS NOT NULL
538 # $where = "part_pkg.classnum $comparison";
542 $agentnum ||= $opt{'agentnum'};
545 " SELECT COALESCE( SUM( cust_bill_pkg_discount.amount ), 0 ) ";
548 # " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
549 # if $opt{average_per_cust_pkg};
552 " FROM cust_bill_pkg_discount
553 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
554 LEFT JOIN cust_bill USING ( invnum )
555 LEFT JOIN cust_main USING ( custnum )
556 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
557 # LEFT JOIN cust_pkg_discount USING ( pkgdiscountnum )
558 # LEFT JOIN discount USING ( discountnum )
559 # LEFT JOIN cust_pkg USING ( pkgnum )
560 # LEFT JOIN part_pkg USING ( pkgpart )
561 # LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
563 return $self->scalar_sql($total_sql);
567 sub setup_pkg { shift->pkg_field( 'setup', @_ ); }
568 sub susp_pkg { shift->pkg_field( 'susp', @_ ); }
569 sub cancel_pkg { shift->pkg_field( 'cancel', @_ ); }
572 my( $self, $field, $speriod, $eperiod, $agentnum ) = @_;
574 SELECT COUNT(*) FROM cust_pkg
575 LEFT JOIN cust_main USING ( custnum )
576 WHERE ". $self->in_time_period_and_agent( $speriod,
585 #this is going to be harder..
587 # my( $self, $speriod, $eperiod, $agentnum ) = @_;
588 # $self->scalar_sql("
589 # SELECT COUNT(*) FROM h_cust_pkg
594 sub in_time_period_and_agent {
595 my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
596 my $col = @_ ? shift() : '_date';
598 my $sql = "$col >= $speriod AND $col < $eperiod";
601 $sql .= " AND cust_main.agentnum = $agentnum"
604 #agent virtualization
606 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table'=>'cust_main' );
612 my ( $self, %opt ) = @_;
613 return '' unless $opt{'custnum'};
614 $opt{'custnum'} =~ /^\d+$/ ? " and custnum = $opt{custnum} " : '';
619 my ($classnum, $use_override) = @_;
620 return '' unless $classnum =~ /^\d+$/;
622 if ( $classnum == 0 ) {
623 $comparison = 'IS NULL';
626 $comparison = "= $classnum";
628 if ( $use_override ) {
630 part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
631 override.classnum $comparison AND pkgpart_override IS NOT NULL
635 return "part_pkg.classnum $comparison";
639 sub with_usageclass {
641 my ($classnum, $use_override) = @_;
642 return '' unless $classnum =~ /^\d+$/;
644 if ( $classnum == 0 ) {
645 $comparison = 'IS NULL';
648 $comparison = "= $classnum";
650 return "cust_bill_pkg_detail.classnum $comparison";
654 my( $self, $sql ) = ( shift, shift );
655 my $sth = dbh->prepare($sql) or die dbh->errstr;
656 warn "FS::Report::Table\n$sql\n" if $DEBUG;
658 or die "Unexpected error executing statement $sql: ". $sth->errstr;
659 $sth->fetchrow_arrayref->[0] || 0;
668 =item init_projection
670 Sets up for future projection of all observables on the report. Currently
671 this is limited to 'cust_bill_pkg'.
675 sub init_projection {
676 # this is weird special case stuff--some redesign may be needed
677 # to use it for anything else
680 if ( driver_name ne 'Pg' ) {
681 # also database-specific for now
682 die "projection reports not supported on this platform";
685 my %items = map {$_ => 1} @{ $self->{items} };
686 if ($items{'cust_bill_pkg'}) {
690 # could use TEMPORARY TABLE but we're already transaction-protected
691 'DROP TABLE IF EXISTS v_cust_bill_pkg',
692 'CREATE TABLE v_cust_bill_pkg ' .
693 '(LIKE cust_bill_pkg,
694 usage numeric(10,2), _date integer, expire integer)',
695 # XXX this should be smart enough to take only the ones with
696 # sdate/edate overlapping the ROI, for performance
697 "INSERT INTO v_cust_bill_pkg (
698 SELECT cust_bill_pkg.*,
699 (SELECT COALESCE(SUM(amount),0) FROM cust_bill_pkg_detail
700 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum),
703 FROM cust_bill_pkg $cust_bill_pkg_join
706 foreach my $sql (@sql) {
707 warn "[init_projection] $sql\n" if $DEBUG;
708 $dbh->do($sql) or die $dbh->errstr;
713 =item extend_projection START END
715 Generates data for the next period of projection. This will be called
716 for sequential periods where the END of one equals the START of the next
721 sub extend_projection {
723 my ($speriod, $eperiod) = @_;
724 my %items = map {$_ => 1} @{ $self->{items} };
725 if ($items{'cust_bill_pkg'}) {
727 # Find all line items that end after the start of the period (and have
728 # recurring fees, and don't expire before they end). Choose the latest
729 # one for each package. If it ends before the end of the period, copy
730 # it forward by one billing period.
731 # Repeat this until the latest line item for each package no longer ends
732 # within the period. This is certain to happen in finitely many
733 # iterations as long as freq > 0.
734 # - Pg only, obviously.
735 # - Gives bad results if freq_override is used.
736 my @fields = ( FS::cust_bill_pkg->fields, qw( usage _date expire ) );
737 my $insert_fields = join(',', @fields);
738 my $add_freq = sub { # emulate FS::part_pkg::add_freq
740 "EXTRACT( EPOCH FROM TO_TIMESTAMP($field) + (CASE WHEN freq ~ E'\\\\D' ".
741 "THEN freq ELSE freq || 'mon' END)::INTERVAL) AS $field";
745 $_ = $add_freq->('edate');
747 elsif ($_ eq 'sdate') {
748 $_ = 'edate AS sdate'
750 elsif ($_ eq 'setup') {
751 $_ = '0 AS setup' #because recurring only
753 elsif ($_ eq '_date') {
754 $_ = $add_freq->('_date');
757 my $select_fields = join(',', @fields);
760 # Subquery here because we need to DISTINCT the whole set, select the
761 # latest charge per pkgnum, and _then_ check edate < $eperiod
762 # and edate < expire.
763 "INSERT INTO v_cust_bill_pkg ($insert_fields)
764 SELECT $select_fields FROM (
765 SELECT DISTINCT ON (pkgnum) * FROM v_cust_bill_pkg
766 WHERE edate >= $speriod
770 ORDER BY pkgnum, edate DESC
772 WHERE edate < $eperiod AND (edate < expire OR expire IS NULL)";
775 warn "[extend_projection] $sql\n" if $DEBUG;
776 $rows = $dbh->do($sql) or die $dbh->errstr;
777 warn "[extend_projection] $rows rows\n" if $DEBUG;
788 L<FS::Report::Table::Monthly>, reports in the web interface.