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 invoiced: The total amount charged on all invoices.
39 sub invoiced { #invoiced
40 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
45 LEFT JOIN cust_main USING ( custnum )
46 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
47 . (%opt ? $self->for_custnum(%opt) : '')
52 =item netsales: invoiced - netcredits
56 sub netsales { #net sales
57 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
59 $self->invoiced($speriod,$eperiod,$agentnum,%opt)
60 - $self->netcredits($speriod,$eperiod,$agentnum,%opt);
63 =item cashflow: payments - refunds
68 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
70 $self->payments($speriod, $eperiod, $agentnum, %opt)
71 - $self->refunds( $speriod, $eperiod, $agentnum, %opt);
74 =item netcashflow: payments - netrefunds
79 my( $self, $speriod, $eperiod, $agentnum ) = @_;
81 $self->receipts($speriod, $eperiod, $agentnum)
82 - $self->netrefunds( $speriod, $eperiod, $agentnum);
85 =item payments: The sum of payments received in the period.
90 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
94 LEFT JOIN cust_main USING ( custnum )
95 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
96 . (%opt ? $self->for_custnum(%opt) : '')
100 =item credits: The sum of credits issued in the period.
105 my( $self, $speriod, $eperiod, $agentnum ) = @_;
109 LEFT JOIN cust_main USING ( custnum )
110 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
114 =item refunds: The sum of refunds paid in the period.
119 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
123 LEFT JOIN cust_main USING ( custnum )
124 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
125 . (%opt ? $self->for_custnum(%opt) : '')
129 =item netcredits: The sum of credit applications to invoices in the period.
134 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
136 SELECT SUM(cust_credit_bill.amount)
137 FROM cust_credit_bill
138 LEFT JOIN cust_bill USING ( invnum )
139 LEFT JOIN cust_main USING ( custnum )
140 WHERE ". $self->in_time_period_and_agent( $speriod,
145 . (%opt ? $self->for_custnum(%opt) : '')
149 =item receipts: The sum of payment applications to invoices in the period.
153 sub receipts { #net payments
154 my( $self, $speriod, $eperiod, $agentnum ) = @_;
156 SELECT SUM(cust_bill_pay.amount)
158 LEFT JOIN cust_bill USING ( invnum )
159 LEFT JOIN cust_main USING ( custnum )
160 WHERE ". $self->in_time_period_and_agent( $speriod,
168 =item netrefunds: The sum of refund applications to credits in the period.
173 my( $self, $speriod, $eperiod, $agentnum ) = @_;
175 SELECT SUM(cust_credit_refund.amount)
176 FROM cust_credit_refund
177 LEFT JOIN cust_credit USING ( crednum )
178 LEFT JOIN cust_main USING ( custnum )
179 WHERE ". $self->in_time_period_and_agent( $speriod,
189 #these should be auto-generated or $AUTOLOADed or something
191 my( $self, $speriod, $eperiod, $agentnum ) = @_;
192 $speriod = $self->_subtract_11mo($speriod);
193 $self->invoiced($speriod, $eperiod, $agentnum);
197 my( $self, $speriod, $eperiod, $agentnum ) = @_;
198 $speriod = $self->_subtract_11mo($speriod);
199 $self->netsales($speriod, $eperiod, $agentnum);
203 my( $self, $speriod, $eperiod, $agentnum ) = @_;
204 $speriod = $self->_subtract_11mo($speriod);
205 $self->receipts($speriod, $eperiod, $agentnum);
209 my( $self, $speriod, $eperiod, $agentnum ) = @_;
210 $speriod = $self->_subtract_11mo($speriod);
211 $self->payments($speriod, $eperiod, $agentnum);
215 my( $self, $speriod, $eperiod, $agentnum ) = @_;
216 $speriod = $self->_subtract_11mo($speriod);
217 $self->credits($speriod, $eperiod, $agentnum);
220 sub netcredits_12mo {
221 my( $self, $speriod, $eperiod, $agentnum ) = @_;
222 $speriod = $self->_subtract_11mo($speriod);
223 $self->netcredits($speriod, $eperiod, $agentnum);
227 my( $self, $speriod, $eperiod, $agentnum ) = @_;
228 $speriod = $self->_subtract_11mo($speriod);
229 $self->cashflow($speriod, $eperiod, $agentnum);
232 sub netcashflow_12mo {
233 my( $self, $speriod, $eperiod, $agentnum ) = @_;
234 $speriod = $self->_subtract_11mo($speriod);
235 $self->cashflow($speriod, $eperiod, $agentnum);
239 my( $self, $speriod, $eperiod, $agentnum ) = @_;
240 $speriod = $self->_subtract_11mo($speriod);
241 $self->refunds($speriod, $eperiod, $agentnum);
244 sub netrefunds_12mo {
245 my( $self, $speriod, $eperiod, $agentnum ) = @_;
246 $speriod = $self->_subtract_11mo($speriod);
247 $self->netrefunds($speriod, $eperiod, $agentnum);
251 #not being too bad with the false laziness
253 my($self, $time) = @_;
254 my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
256 if ( $mon < 0 ) { $mon+=12; $year--; }
257 timelocal($sec,$min,$hour,$mday,$mon,$year);
260 =item cust_pkg_setup_cost: The total setup costs of packages setup in the period
262 'classnum': limit to this package class.
266 sub cust_pkg_setup_cost {
267 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
270 if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
272 $comparison = 'IS NULL';
275 $comparison = "= $1";
277 $where = "AND part_pkg.classnum $comparison";
279 $agentnum ||= $opt{'agentnum'};
281 my $total_sql = " SELECT SUM(part_pkg.setup_cost) ";
282 $total_sql .= " FROM cust_pkg
283 LEFT JOIN cust_main USING ( custnum )
284 LEFT JOIN part_pkg USING ( pkgpart )
287 AND ".$self->in_time_period_and_agent(
288 $speriod, $eperiod, $agentnum, 'cust_pkg.setup');
289 return $self->scalar_sql($total_sql);
292 =item cust_pkg_recur_cust: the total recur costs of packages in the period
294 'classnum': limit to this package class.
298 sub cust_pkg_recur_cost {
299 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
302 if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
304 $comparison = 'IS NULL';
307 $comparison = "= $1";
309 $where = " AND part_pkg.classnum $comparison";
311 $agentnum ||= $opt{'agentnum'};
312 # duplication of in_time_period_and_agent
313 # because we do it a little differently here
314 $where .= " AND cust_main.agentnum = $agentnum" if $agentnum;
316 $FS::CurrentUser::CurrentUser->agentnums_sql('table' => 'cust_main');
318 my $total_sql = " SELECT SUM(part_pkg.recur_cost) ";
319 $total_sql .= " FROM cust_pkg
320 LEFT JOIN cust_main USING ( custnum )
321 LEFT JOIN part_pkg USING ( pkgpart )
324 AND cust_pkg.setup < $eperiod
325 AND (cust_pkg.cancel > $speriod OR cust_pkg.cancel IS NULL)
327 return $self->scalar_sql($total_sql);
330 =item cust_bill_pkg: the total package charges on invoice line items.
332 'charges': limit the type of charges included (setup, recur, usage).
333 Should be a string containing one or more of 'S', 'R', or 'U'; if
334 unspecified, defaults to all three.
336 'classnum': limit to this package class.
338 'use_override': for line items generated by an add-on package, use the class
339 of the add-on rather than the base package.
341 'freq': limit to packages with this frequency. Currently uses the part_pkg
342 frequency, so term discounted packages may give odd results.
344 'distribute': for non-monthly recurring charges, ignore the invoice
345 date. Instead, consider the line item's starting/ending dates. Determine
346 the fraction of the line item duration that falls within the specified
347 interval and return that fraction of the recurring charges. This is
348 somewhat experimental.
350 'project': enable if this is a projected period. This is very experimental.
356 my( $speriod, $eperiod, $agentnum, %opt ) = @_;
358 my %charges = map {$_=>1} split('', $opt{'charges'} || 'SRU');
361 $sum += $self->cust_bill_pkg_setup(@_) if $charges{S};
362 $sum += $self->cust_bill_pkg_recur(@_) if $charges{R};
363 $sum += $self->cust_bill_pkg_detail(@_) if $charges{U};
367 my $cust_bill_pkg_join = '
368 LEFT JOIN cust_bill USING ( invnum )
369 LEFT JOIN cust_main USING ( custnum )
370 LEFT JOIN cust_pkg USING ( pkgnum )
371 LEFT JOIN part_pkg USING ( pkgpart )
372 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart';
374 sub cust_bill_pkg_setup {
376 my ($speriod, $eperiod, $agentnum, %opt) = @_;
377 # no projecting setup fees--use real invoices only
378 # but evaluate this anyway, because the design of projection is that
379 # if there are somehow real setup fees in the future, we want to count
382 $agentnum ||= $opt{'agentnum'};
386 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
387 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
390 my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0)
393 WHERE " . join(' AND ', grep $_, @where);
395 $self->scalar_sql($total_sql);
398 sub cust_bill_pkg_recur {
400 my ($speriod, $eperiod, $agentnum, %opt) = @_;
402 $agentnum ||= $opt{'agentnum'};
403 my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
407 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
410 # subtract all usage from the line item regardless of date
412 if ( $opt{'project'} ) {
413 $item_usage = 'usage'; #already calculated
416 $item_usage = '( SELECT COALESCE(SUM(amount),0)
417 FROM cust_bill_pkg_detail
418 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
420 my $recur_fraction = '';
422 if ( $opt{'distribute'} ) {
423 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
425 "$cust_bill_pkg.sdate < $eperiod",
426 "$cust_bill_pkg.edate > $speriod",
428 # the fraction of edate - sdate that's within [speriod, eperiod]
429 $recur_fraction = " *
430 CAST(LEAST($eperiod, $cust_bill_pkg.edate) -
431 GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) /
432 ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
435 # we don't want to have to create v_cust_bill
436 my $_date = $opt{'project'} ? 'v_cust_bill_pkg._date' : 'cust_bill._date';
438 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date);
441 my $total_sql = 'SELECT '.
442 "COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)
445 WHERE ".join(' AND ', grep $_, @where);
447 $self->scalar_sql($total_sql);
450 =item cust_bill_pkg_detail: the total usage charges in detail lines.
452 Arguments as for C<cust_bill_pkg>, plus:
454 'usageclass': limit to this usage class number.
458 sub cust_bill_pkg_detail {
459 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
461 my @where = ( "cust_bill_pkg.pkgnum != 0" );
463 $agentnum ||= $opt{'agentnum'};
466 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
467 $self->with_usageclass($opt{'usageclass'}),
470 if ( $opt{'distribute'} ) {
471 # then limit according to the usage time, not the billing date
472 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
473 'cust_bill_pkg_detail.startdate'
477 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
482 my $total_sql = " SELECT SUM(amount) ";
485 " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
486 if $opt{average_per_cust_pkg};
489 " FROM cust_bill_pkg_detail
490 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
491 LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
492 LEFT JOIN cust_main USING ( custnum )
493 LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
494 LEFT JOIN part_pkg USING ( pkgpart )
495 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
496 WHERE ".join( ' AND ', grep $_, @where );
498 $self->scalar_sql($total_sql);
502 sub cust_bill_pkg_discount {
503 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
506 #my $comparison = '';
507 #if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
509 # $comparison = "IS NULL";
511 # $comparison = "= $1";
514 # if ( $opt{'use_override'} ) {
516 # part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
517 # override.classnum $comparison AND pkgpart_override IS NOT NULL
520 # $where = "part_pkg.classnum $comparison";
524 $agentnum ||= $opt{'agentnum'};
527 " SELECT COALESCE( SUM( cust_bill_pkg_discount.amount ), 0 ) ";
530 # " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
531 # if $opt{average_per_cust_pkg};
534 " FROM cust_bill_pkg_discount
535 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
536 LEFT JOIN cust_bill USING ( invnum )
537 LEFT JOIN cust_main USING ( custnum )
538 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
539 # LEFT JOIN cust_pkg_discount USING ( pkgdiscountnum )
540 # LEFT JOIN discount USING ( discountnum )
541 # LEFT JOIN cust_pkg USING ( pkgnum )
542 # LEFT JOIN part_pkg USING ( pkgpart )
543 # LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
545 return $self->scalar_sql($total_sql);
549 sub setup_pkg { shift->pkg_field( @_, 'setup' ); }
550 sub susp_pkg { shift->pkg_field( @_, 'susp' ); }
551 sub cancel_pkg { shift->pkg_field( @_, 'cancel'); }
554 my( $self, $speriod, $eperiod, $agentnum, $field ) = @_;
556 SELECT COUNT(*) FROM cust_pkg
557 LEFT JOIN cust_main USING ( custnum )
558 WHERE ". $self->in_time_period_and_agent( $speriod,
567 #this is going to be harder..
569 # my( $self, $speriod, $eperiod, $agentnum ) = @_;
570 # $self->scalar_sql("
571 # SELECT COUNT(*) FROM h_cust_pkg
576 sub in_time_period_and_agent {
577 my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
578 my $col = @_ ? shift() : '_date';
580 my $sql = "$col >= $speriod AND $col < $eperiod";
583 $sql .= " AND cust_main.agentnum = $agentnum"
586 #agent virtualization
588 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table'=>'cust_main' );
594 my ( $self, %opt ) = @_;
595 return '' unless $opt{'custnum'};
596 $opt{'custnum'} =~ /^\d+$/ ? " and custnum = $opt{custnum} " : '';
601 my ($classnum, $use_override) = @_;
602 return '' unless $classnum =~ /^\d+$/;
604 if ( $classnum == 0 ) {
605 $comparison = 'IS NULL';
608 $comparison = "= $classnum";
610 if ( $use_override ) {
612 part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
613 override.classnum $comparison AND pkgpart_override IS NOT NULL
617 return "part_pkg.classnum $comparison";
621 sub with_usageclass {
623 my ($classnum, $use_override) = @_;
624 return '' unless $classnum =~ /^\d+$/;
626 if ( $classnum == 0 ) {
627 $comparison = 'IS NULL';
630 $comparison = "= $classnum";
632 return "cust_bill_pkg_detail.classnum $comparison";
636 my( $self, $sql ) = ( shift, shift );
637 my $sth = dbh->prepare($sql) or die dbh->errstr;
638 warn "FS::Report::Table\n$sql\n" if $DEBUG;
640 or die "Unexpected error executing statement $sql: ". $sth->errstr;
641 $sth->fetchrow_arrayref->[0] || 0;
650 =item init_projection
652 Sets up for future projection of all observables on the report. Currently
653 this is limited to 'cust_bill_pkg'.
657 sub init_projection {
658 # this is weird special case stuff--some redesign may be needed
659 # to use it for anything else
662 if ( driver_name ne 'Pg' ) {
663 # also database-specific for now
664 die "projection reports not supported on this platform";
667 my %items = map {$_ => 1} @{ $self->{items} };
668 if ($items{'cust_bill_pkg'}) {
672 # could use TEMPORARY TABLE but we're already transaction-protected
673 'DROP TABLE IF EXISTS v_cust_bill_pkg',
674 'CREATE TABLE v_cust_bill_pkg ' .
675 '(LIKE cust_bill_pkg,
676 usage numeric(10,2), _date integer, expire integer)',
677 # XXX this should be smart enough to take only the ones with
678 # sdate/edate overlapping the ROI, for performance
679 "INSERT INTO v_cust_bill_pkg (
680 SELECT cust_bill_pkg.*,
681 (SELECT COALESCE(SUM(amount),0) FROM cust_bill_pkg_detail
682 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum),
685 FROM cust_bill_pkg $cust_bill_pkg_join
688 foreach my $sql (@sql) {
689 warn "[init_projection] $sql\n" if $DEBUG;
690 $dbh->do($sql) or die $dbh->errstr;
695 =item extend_projection START END
697 Generates data for the next period of projection. This will be called
698 for sequential periods where the END of one equals the START of the next
703 sub extend_projection {
705 my ($speriod, $eperiod) = @_;
706 my %items = map {$_ => 1} @{ $self->{items} };
707 if ($items{'cust_bill_pkg'}) {
708 # append, head-to-tail, new line items identical to any that end within the
709 # period (and aren't expiring)
710 my @fields = ( FS::cust_bill_pkg->fields, qw( usage _date expire ) );
711 my $insert_fields = join(',', @fields);
712 #advance (sdate, edate) by one billing period
715 $_ = '(edate + (edate - sdate)) AS edate' #careful of integer overflow
717 elsif ($_ eq 'sdate') {
718 $_ = 'edate AS sdate'
720 elsif ($_ eq 'setup') {
721 $_ = '0 AS setup' #because recurring only
723 elsif ($_ eq '_date') {
724 $_ = '(_date + (edate - sdate)) AS _date'
727 my $select_fields = join(',', @fields);
730 "INSERT INTO v_cust_bill_pkg ($insert_fields)
731 SELECT $select_fields FROM v_cust_bill_pkg
732 WHERE edate >= $speriod AND edate < $eperiod
734 AND (expire IS NULL OR expire > edate)";
735 warn "[extend_projection] $sql\n" if $DEBUG;
736 my $rows = $dbh->do($sql) or die $dbh->errstr;
737 warn "[extend_projection] $rows rows\n" if $DEBUG;
747 L<FS::Report::Table::Monthly>, reports in the web interface.