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. 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).
42 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
43 my @where = ( $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
44 'cust_main.signupdate')
47 if ( $opt{'indirect'} ) {
48 $join = " JOIN cust_main AS referring_cust_main".
49 " ON (cust_main.referral_custnum = referring_cust_main.custnum)";
51 if ( $opt{'refnum'} ) {
52 push @where, "referring_cust_main.refnum = ".$opt{'refnum'};
55 elsif ( $opt{'refnum'} ) {
56 push @where, "refnum = ".$opt{'refnum'};
60 "SELECT COUNT(*) FROM cust_main $join WHERE ".join(' AND ', @where)
64 =item invoiced: The total amount charged on all invoices.
68 sub invoiced { #invoiced
69 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
74 LEFT JOIN cust_main USING ( custnum )
75 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
81 =item netsales: invoiced - netcredits
85 sub netsales { #net sales
86 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
88 $self->invoiced( $speriod, $eperiod, $agentnum, %opt)
89 - $self->netcredits($speriod, $eperiod, $agentnum, %opt);
92 =item cashflow: payments - refunds
97 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
99 $self->payments($speriod, $eperiod, $agentnum, %opt)
100 - $self->refunds( $speriod, $eperiod, $agentnum, %opt);
103 =item netcashflow: payments - netrefunds
108 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
110 $self->receipts( $speriod, $eperiod, $agentnum, %opt)
111 - $self->netrefunds( $speriod, $eperiod, $agentnum, %opt);
114 =item payments: The sum of payments received 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 $self->for_opts(%opt)
129 =item credits: The sum of credits issued in the period.
134 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
138 LEFT JOIN cust_main USING ( custnum )
139 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
140 $self->for_opts(%opt)
144 =item refunds: The sum of refunds paid in the period.
149 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
153 LEFT JOIN cust_main USING ( custnum )
154 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
155 $self->for_opts(%opt)
159 =item netcredits: The sum of credit applications to invoices in the period.
164 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
166 SELECT SUM(cust_credit_bill.amount)
167 FROM cust_credit_bill
168 LEFT JOIN cust_bill USING ( invnum )
169 LEFT JOIN cust_main USING ( custnum )
170 WHERE ". $self->in_time_period_and_agent( $speriod,
175 $self->for_opts(%opt)
179 =item receipts: The sum of payment applications to invoices in the period.
183 sub receipts { #net payments
184 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
186 SELECT SUM(cust_bill_pay.amount)
188 LEFT JOIN cust_bill USING ( invnum )
189 LEFT JOIN cust_main USING ( custnum )
190 WHERE ". $self->in_time_period_and_agent( $speriod,
195 $self->for_opts(%opt)
199 =item netrefunds: The sum of refund applications to credits in the period.
204 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
206 SELECT SUM(cust_credit_refund.amount)
207 FROM cust_credit_refund
208 LEFT JOIN cust_credit USING ( crednum )
209 LEFT JOIN cust_main USING ( custnum )
210 WHERE ". $self->in_time_period_and_agent( $speriod,
215 $self->for_opts(%opt)
221 #these should be auto-generated or $AUTOLOADed or something
223 my( $self, $speriod, $eperiod, $agentnum ) = @_;
224 $speriod = $self->_subtract_11mo($speriod);
225 $self->invoiced($speriod, $eperiod, $agentnum);
229 my( $self, $speriod, $eperiod, $agentnum ) = @_;
230 $speriod = $self->_subtract_11mo($speriod);
231 $self->netsales($speriod, $eperiod, $agentnum);
235 my( $self, $speriod, $eperiod, $agentnum ) = @_;
236 $speriod = $self->_subtract_11mo($speriod);
237 $self->receipts($speriod, $eperiod, $agentnum);
241 my( $self, $speriod, $eperiod, $agentnum ) = @_;
242 $speriod = $self->_subtract_11mo($speriod);
243 $self->payments($speriod, $eperiod, $agentnum);
247 my( $self, $speriod, $eperiod, $agentnum ) = @_;
248 $speriod = $self->_subtract_11mo($speriod);
249 $self->credits($speriod, $eperiod, $agentnum);
252 sub netcredits_12mo {
253 my( $self, $speriod, $eperiod, $agentnum ) = @_;
254 $speriod = $self->_subtract_11mo($speriod);
255 $self->netcredits($speriod, $eperiod, $agentnum);
259 my( $self, $speriod, $eperiod, $agentnum ) = @_;
260 $speriod = $self->_subtract_11mo($speriod);
261 $self->cashflow($speriod, $eperiod, $agentnum);
264 sub netcashflow_12mo {
265 my( $self, $speriod, $eperiod, $agentnum ) = @_;
266 $speriod = $self->_subtract_11mo($speriod);
267 $self->cashflow($speriod, $eperiod, $agentnum);
271 my( $self, $speriod, $eperiod, $agentnum ) = @_;
272 $speriod = $self->_subtract_11mo($speriod);
273 $self->refunds($speriod, $eperiod, $agentnum);
276 sub netrefunds_12mo {
277 my( $self, $speriod, $eperiod, $agentnum ) = @_;
278 $speriod = $self->_subtract_11mo($speriod);
279 $self->netrefunds($speriod, $eperiod, $agentnum);
283 #not being too bad with the false laziness
285 my($self, $time) = @_;
286 my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
288 if ( $mon < 0 ) { $mon+=12; $year--; }
289 timelocal($sec,$min,$hour,$mday,$mon,$year);
292 =item cust_pkg_setup_cost: The total setup costs of packages setup in the period
294 'classnum': limit to this package class.
298 sub cust_pkg_setup_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'};
313 my $total_sql = " SELECT SUM(part_pkg.setup_cost) ";
314 $total_sql .= " FROM cust_pkg
315 LEFT JOIN cust_main USING ( custnum )
316 LEFT JOIN part_pkg USING ( pkgpart )
319 AND ".$self->in_time_period_and_agent(
320 $speriod, $eperiod, $agentnum, 'cust_pkg.setup');
321 return $self->scalar_sql($total_sql);
324 =item cust_pkg_recur_cust: the total recur costs of packages in the period
326 'classnum': limit to this package class.
330 sub cust_pkg_recur_cost {
331 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
334 if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
336 $comparison = 'IS NULL';
339 $comparison = "= $1";
341 $where = " AND part_pkg.classnum $comparison";
343 $agentnum ||= $opt{'agentnum'};
344 # duplication of in_time_period_and_agent
345 # because we do it a little differently here
346 $where .= " AND cust_main.agentnum = $agentnum" if $agentnum;
348 $FS::CurrentUser::CurrentUser->agentnums_sql('table' => 'cust_main');
350 my $total_sql = " SELECT SUM(part_pkg.recur_cost) ";
351 $total_sql .= " FROM cust_pkg
352 LEFT JOIN cust_main USING ( custnum )
353 LEFT JOIN part_pkg USING ( pkgpart )
356 AND cust_pkg.setup < $eperiod
357 AND (cust_pkg.cancel > $speriod OR cust_pkg.cancel IS NULL)
359 return $self->scalar_sql($total_sql);
362 =item cust_bill_pkg: the total package charges on invoice line items.
364 'charges': limit the type of charges included (setup, recur, usage).
365 Should be a string containing one or more of 'S', 'R', or 'U'; if
366 unspecified, defaults to all three.
368 'classnum': limit to this package class.
370 'use_override': for line items generated by an add-on package, use the class
371 of the add-on rather than the base package.
373 'freq': limit to packages with this frequency. Currently uses the part_pkg
374 frequency, so term discounted packages may give odd results.
376 'distribute': for non-monthly recurring charges, ignore the invoice
377 date. Instead, consider the line item's starting/ending dates. Determine
378 the fraction of the line item duration that falls within the specified
379 interval and return that fraction of the recurring charges. This is
380 somewhat experimental.
382 'project': enable if this is a projected period. This is very experimental.
388 my( $speriod, $eperiod, $agentnum, %opt ) = @_;
390 my %charges = map {$_=>1} split('', $opt{'charges'} || 'SRU');
393 $sum += $self->cust_bill_pkg_setup(@_) if $charges{S};
394 $sum += $self->cust_bill_pkg_recur(@_) if $charges{R};
395 $sum += $self->cust_bill_pkg_detail(@_) if $charges{U};
399 my $cust_bill_pkg_join = '
400 LEFT JOIN cust_bill USING ( invnum )
401 LEFT JOIN cust_main USING ( custnum )
402 LEFT JOIN cust_pkg USING ( pkgnum )
403 LEFT JOIN part_pkg USING ( pkgpart )
404 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart';
406 sub cust_bill_pkg_setup {
408 my ($speriod, $eperiod, $agentnum, %opt) = @_;
409 # no projecting setup fees--use real invoices only
410 # but evaluate this anyway, because the design of projection is that
411 # if there are somehow real setup fees in the future, we want to count
414 $agentnum ||= $opt{'agentnum'};
418 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
419 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
422 push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
424 my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0)
427 WHERE " . join(' AND ', grep $_, @where);
429 $self->scalar_sql($total_sql);
432 sub cust_bill_pkg_recur {
434 my ($speriod, $eperiod, $agentnum, %opt) = @_;
436 $agentnum ||= $opt{'agentnum'};
437 my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
441 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
444 push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
446 # subtract all usage from the line item regardless of date
448 if ( $opt{'project'} ) {
449 $item_usage = 'usage'; #already calculated
452 $item_usage = '( SELECT COALESCE(SUM(amount),0)
453 FROM cust_bill_pkg_detail
454 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
456 my $recur_fraction = '';
458 if ( $opt{'distribute'} ) {
459 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
461 "$cust_bill_pkg.sdate < $eperiod",
462 "$cust_bill_pkg.edate >= $speriod",
464 # the fraction of edate - sdate that's within [speriod, eperiod]
465 $recur_fraction = " *
466 CAST(LEAST($eperiod, $cust_bill_pkg.edate) -
467 GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) /
468 ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
471 # we don't want to have to create v_cust_bill
472 my $_date = $opt{'project'} ? 'v_cust_bill_pkg._date' : 'cust_bill._date';
474 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date);
477 my $total_sql = 'SELECT '.
478 "COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)
481 WHERE ".join(' AND ', grep $_, @where);
483 $self->scalar_sql($total_sql);
486 =item cust_bill_pkg_detail: the total usage charges in detail lines.
488 Arguments as for C<cust_bill_pkg>, plus:
490 'usageclass': limit to this usage class number.
494 sub cust_bill_pkg_detail {
495 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
497 my @where = ( "cust_bill_pkg.pkgnum != 0" );
499 push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
501 $agentnum ||= $opt{'agentnum'};
504 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
505 $self->with_usageclass($opt{'usageclass'}),
508 if ( $opt{'distribute'} ) {
509 # then limit according to the usage time, not the billing date
510 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
511 'cust_bill_pkg_detail.startdate'
515 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
520 my $total_sql = " SELECT SUM(amount) ";
523 " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
524 if $opt{average_per_cust_pkg};
527 " FROM cust_bill_pkg_detail
528 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
529 LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
530 LEFT JOIN cust_main USING ( custnum )
531 LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
532 LEFT JOIN part_pkg USING ( pkgpart )
533 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
534 WHERE ".join( ' AND ', grep $_, @where );
536 $self->scalar_sql($total_sql);
540 sub cust_bill_pkg_discount {
541 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
544 #my $comparison = '';
545 #if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
547 # $comparison = "IS NULL";
549 # $comparison = "= $1";
552 # if ( $opt{'use_override'} ) {
554 # part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
555 # override.classnum $comparison AND pkgpart_override IS NOT NULL
558 # $where = "part_pkg.classnum $comparison";
562 $agentnum ||= $opt{'agentnum'};
565 " SELECT COALESCE( SUM( cust_bill_pkg_discount.amount ), 0 ) ";
568 # " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
569 # if $opt{average_per_cust_pkg};
572 " FROM cust_bill_pkg_discount
573 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
574 LEFT JOIN cust_bill USING ( invnum )
575 LEFT JOIN cust_main USING ( custnum )
576 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
577 # LEFT JOIN cust_pkg_discount USING ( pkgdiscountnum )
578 # LEFT JOIN discount USING ( discountnum )
579 # LEFT JOIN cust_pkg USING ( pkgnum )
580 # LEFT JOIN part_pkg USING ( pkgpart )
581 # LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
583 return $self->scalar_sql($total_sql);
587 sub setup_pkg { shift->pkg_field( 'setup', @_ ); }
588 sub susp_pkg { shift->pkg_field( 'susp', @_ ); }
589 sub cancel_pkg { shift->pkg_field( 'cancel', @_ ); }
592 my( $self, $field, $speriod, $eperiod, $agentnum ) = @_;
594 SELECT COUNT(*) FROM cust_pkg
595 LEFT JOIN cust_main USING ( custnum )
596 WHERE ". $self->in_time_period_and_agent( $speriod,
605 #this is going to be harder..
607 # my( $self, $speriod, $eperiod, $agentnum ) = @_;
608 # $self->scalar_sql("
609 # SELECT COUNT(*) FROM h_cust_pkg
614 sub in_time_period_and_agent {
615 my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
616 my $col = @_ ? shift() : '_date';
618 my $sql = "$col >= $speriod AND $col < $eperiod";
621 $sql .= " AND cust_main.agentnum = $agentnum"
624 #agent virtualization
626 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table'=>'cust_main' );
632 my ( $self, %opt ) = @_;
634 if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
635 $sql .= " and custnum = $1 ";
637 if ( $opt{'refnum'} =~ /^(\d+)$/ ) {
638 $sql .= " and refnum = $1 ";
645 my ($classnum, $use_override) = @_;
646 return '' unless $classnum =~ /^\d+$/;
648 if ( $classnum == 0 ) {
649 $comparison = 'IS NULL';
652 $comparison = "= $classnum";
654 if ( $use_override ) {
656 part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
657 override.classnum $comparison AND pkgpart_override IS NOT NULL
661 return "part_pkg.classnum $comparison";
665 sub with_usageclass {
667 my ($classnum, $use_override) = @_;
668 return '' unless $classnum =~ /^\d+$/;
670 if ( $classnum == 0 ) {
671 $comparison = 'IS NULL';
674 $comparison = "= $classnum";
676 return "cust_bill_pkg_detail.classnum $comparison";
680 my( $self, $sql ) = ( shift, shift );
681 my $sth = dbh->prepare($sql) or die dbh->errstr;
682 warn "FS::Report::Table\n$sql\n" if $DEBUG;
684 or die "Unexpected error executing statement $sql: ". $sth->errstr;
685 $sth->fetchrow_arrayref->[0] || 0;
694 =item init_projection
696 Sets up for future projection of all observables on the report. Currently
697 this is limited to 'cust_bill_pkg'.
701 sub init_projection {
702 # this is weird special case stuff--some redesign may be needed
703 # to use it for anything else
706 if ( driver_name ne 'Pg' ) {
707 # also database-specific for now
708 die "projection reports not supported on this platform";
711 my %items = map {$_ => 1} @{ $self->{items} };
712 if ($items{'cust_bill_pkg'}) {
716 # could use TEMPORARY TABLE but we're already transaction-protected
717 'DROP TABLE IF EXISTS v_cust_bill_pkg',
718 'CREATE TABLE v_cust_bill_pkg ' .
719 '(LIKE cust_bill_pkg,
720 usage numeric(10,2), _date integer, expire integer)',
721 # XXX this should be smart enough to take only the ones with
722 # sdate/edate overlapping the ROI, for performance
723 "INSERT INTO v_cust_bill_pkg (
724 SELECT cust_bill_pkg.*,
725 (SELECT COALESCE(SUM(amount),0) FROM cust_bill_pkg_detail
726 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum),
729 FROM cust_bill_pkg $cust_bill_pkg_join
732 foreach my $sql (@sql) {
733 warn "[init_projection] $sql\n" if $DEBUG;
734 $dbh->do($sql) or die $dbh->errstr;
739 =item extend_projection START END
741 Generates data for the next period of projection. This will be called
742 for sequential periods where the END of one equals the START of the next
747 sub extend_projection {
749 my ($speriod, $eperiod) = @_;
750 my %items = map {$_ => 1} @{ $self->{items} };
751 if ($items{'cust_bill_pkg'}) {
753 # Find all line items that end after the start of the period (and have
754 # recurring fees, and don't expire before they end). Choose the latest
755 # one for each package. If it ends before the end of the period, copy
756 # it forward by one billing period.
757 # Repeat this until the latest line item for each package no longer ends
758 # within the period. This is certain to happen in finitely many
759 # iterations as long as freq > 0.
760 # - Pg only, obviously.
761 # - Gives bad results if freq_override is used.
762 my @fields = ( FS::cust_bill_pkg->fields, qw( usage _date expire ) );
763 my $insert_fields = join(',', @fields);
764 my $add_freq = sub { # emulate FS::part_pkg::add_freq
766 "EXTRACT( EPOCH FROM TO_TIMESTAMP($field) + (CASE WHEN freq ~ E'\\\\D' ".
767 "THEN freq ELSE freq || 'mon' END)::INTERVAL) AS $field";
771 $_ = $add_freq->('edate');
773 elsif ($_ eq 'sdate') {
774 $_ = 'edate AS sdate'
776 elsif ($_ eq 'setup') {
777 $_ = '0 AS setup' #because recurring only
779 elsif ($_ eq '_date') {
780 $_ = $add_freq->('_date');
783 my $select_fields = join(',', @fields);
786 # Subquery here because we need to DISTINCT the whole set, select the
787 # latest charge per pkgnum, and _then_ check edate < $eperiod
788 # and edate < expire.
789 "INSERT INTO v_cust_bill_pkg ($insert_fields)
790 SELECT $select_fields FROM (
791 SELECT DISTINCT ON (pkgnum) * FROM v_cust_bill_pkg
792 WHERE edate >= $speriod
796 ORDER BY pkgnum, edate DESC
798 WHERE edate < $eperiod AND (edate < expire OR expire IS NULL)";
801 warn "[extend_projection] $sql\n" if $DEBUG;
802 $rows = $dbh->do($sql) or die $dbh->errstr;
803 warn "[extend_projection] $rows rows\n" if $DEBUG;
814 L<FS::Report::Table::Monthly>, reports in the web interface.