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 ) = @_;
71 my $sql = 'SELECT SUM(cust_bill.charged) FROM cust_bill';
72 if ( $opt{'setuprecur'} ) {
74 FS::cust_bill_pkg->charged_sql($speriod, $eperiod, %opt).
75 ') FROM cust_bill_pkg JOIN cust_bill USING (invnum)';
80 LEFT JOIN cust_main USING ( custnum )
81 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
87 =item netsales: invoiced - netcredits
91 sub netsales { #net sales
92 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
94 $self->invoiced( $speriod, $eperiod, $agentnum, %opt)
95 - $self->netcredits($speriod, $eperiod, $agentnum, %opt);
98 =item cashflow: payments - refunds
103 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
105 $self->payments($speriod, $eperiod, $agentnum, %opt)
106 - $self->refunds( $speriod, $eperiod, $agentnum, %opt);
109 =item netcashflow: payments - netrefunds
114 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
116 $self->receipts( $speriod, $eperiod, $agentnum, %opt)
117 - $self->netrefunds( $speriod, $eperiod, $agentnum, %opt);
120 =item payments: The sum of payments received in the period.
125 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
129 LEFT JOIN cust_main USING ( custnum )
130 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
131 $self->for_opts(%opt)
135 =item credits: The sum of credits issued in the period.
140 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
144 LEFT JOIN cust_main USING ( custnum )
145 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
146 $self->for_opts(%opt)
150 =item refunds: The sum of refunds paid in the period.
155 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
159 LEFT JOIN cust_main USING ( custnum )
160 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
161 $self->for_opts(%opt)
165 =item netcredits: The sum of credit applications to invoices in the period.
170 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
172 my $sql = 'SELECT SUM(cust_credit_bill.amount) FROM cust_credit_bill';
173 if ( $opt{'setuprecur'} ) {
174 $sql = 'SELECT SUM('.
175 FS::cust_bill_pkg->credited_sql($speriod, $eperiod, %opt).
176 ') FROM cust_bill_pkg';
181 LEFT JOIN cust_bill USING ( invnum )
182 LEFT JOIN cust_main USING ( custnum )
183 WHERE ". $self->in_time_period_and_agent( $speriod,
188 $self->for_opts(%opt)
192 =item receipts: The sum of payment applications to invoices in the period.
196 sub receipts { #net payments
197 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
199 my $sql = 'SELECT SUM(cust_bill_pay.amount) FROM cust_bill_pay';
200 if ( $opt{'setuprecur'} ) {
201 $sql = 'SELECT SUM('.
202 FS::cust_bill_pkg->paid_sql($speriod, $eperiod, %opt).
203 ') FROM cust_bill_pkg';
208 LEFT JOIN cust_bill USING ( invnum )
209 LEFT JOIN cust_main USING ( custnum )
210 WHERE ". $self->in_time_period_and_agent( $speriod,
215 $self->for_opts(%opt)
219 =item netrefunds: The sum of refund applications to credits in the period.
224 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
226 SELECT SUM(cust_credit_refund.amount)
227 FROM cust_credit_refund
228 LEFT JOIN cust_credit USING ( crednum )
229 LEFT JOIN cust_main USING ( custnum )
230 WHERE ". $self->in_time_period_and_agent( $speriod,
235 $self->for_opts(%opt)
241 #these should be auto-generated or $AUTOLOADed or something
243 my( $self, $speriod, $eperiod, $agentnum ) = @_;
244 $speriod = $self->_subtract_11mo($speriod);
245 $self->invoiced($speriod, $eperiod, $agentnum);
249 my( $self, $speriod, $eperiod, $agentnum ) = @_;
250 $speriod = $self->_subtract_11mo($speriod);
251 $self->netsales($speriod, $eperiod, $agentnum);
255 my( $self, $speriod, $eperiod, $agentnum ) = @_;
256 $speriod = $self->_subtract_11mo($speriod);
257 $self->receipts($speriod, $eperiod, $agentnum);
261 my( $self, $speriod, $eperiod, $agentnum ) = @_;
262 $speriod = $self->_subtract_11mo($speriod);
263 $self->payments($speriod, $eperiod, $agentnum);
267 my( $self, $speriod, $eperiod, $agentnum ) = @_;
268 $speriod = $self->_subtract_11mo($speriod);
269 $self->credits($speriod, $eperiod, $agentnum);
272 sub netcredits_12mo {
273 my( $self, $speriod, $eperiod, $agentnum ) = @_;
274 $speriod = $self->_subtract_11mo($speriod);
275 $self->netcredits($speriod, $eperiod, $agentnum);
279 my( $self, $speriod, $eperiod, $agentnum ) = @_;
280 $speriod = $self->_subtract_11mo($speriod);
281 $self->cashflow($speriod, $eperiod, $agentnum);
284 sub netcashflow_12mo {
285 my( $self, $speriod, $eperiod, $agentnum ) = @_;
286 $speriod = $self->_subtract_11mo($speriod);
287 $self->cashflow($speriod, $eperiod, $agentnum);
291 my( $self, $speriod, $eperiod, $agentnum ) = @_;
292 $speriod = $self->_subtract_11mo($speriod);
293 $self->refunds($speriod, $eperiod, $agentnum);
296 sub netrefunds_12mo {
297 my( $self, $speriod, $eperiod, $agentnum ) = @_;
298 $speriod = $self->_subtract_11mo($speriod);
299 $self->netrefunds($speriod, $eperiod, $agentnum);
303 #not being too bad with the false laziness
305 my($self, $time) = @_;
306 my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
308 if ( $mon < 0 ) { $mon+=12; $year--; }
309 timelocal($sec,$min,$hour,$mday,$mon,$year);
312 =item cust_pkg_setup_cost: The total setup costs of packages setup in the period
314 'classnum': limit to this package class.
318 sub cust_pkg_setup_cost {
319 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
322 if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
324 $comparison = 'IS NULL';
327 $comparison = "= $1";
329 $where = "AND part_pkg.classnum $comparison";
331 $agentnum ||= $opt{'agentnum'};
333 my $total_sql = " SELECT SUM(part_pkg.setup_cost) ";
334 $total_sql .= " FROM cust_pkg
335 LEFT JOIN cust_main USING ( custnum )
336 LEFT JOIN part_pkg USING ( pkgpart )
339 AND ".$self->in_time_period_and_agent(
340 $speriod, $eperiod, $agentnum, 'cust_pkg.setup');
341 return $self->scalar_sql($total_sql);
344 =item cust_pkg_recur_cust: the total recur costs of packages in the period
346 'classnum': limit to this package class.
350 sub cust_pkg_recur_cost {
351 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
354 if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
356 $comparison = 'IS NULL';
359 $comparison = "= $1";
361 $where = " AND part_pkg.classnum $comparison";
363 $agentnum ||= $opt{'agentnum'};
364 # duplication of in_time_period_and_agent
365 # because we do it a little differently here
366 $where .= " AND cust_main.agentnum = $agentnum" if $agentnum;
368 $FS::CurrentUser::CurrentUser->agentnums_sql('table' => 'cust_main');
370 my $total_sql = " SELECT SUM(part_pkg.recur_cost) ";
371 $total_sql .= " FROM cust_pkg
372 LEFT JOIN cust_main USING ( custnum )
373 LEFT JOIN part_pkg USING ( pkgpart )
376 AND cust_pkg.setup < $eperiod
377 AND (cust_pkg.cancel > $speriod OR cust_pkg.cancel IS NULL)
379 return $self->scalar_sql($total_sql);
382 =item cust_bill_pkg: the total package charges on invoice line items.
384 'charges': limit the type of charges included (setup, recur, usage).
385 Should be a string containing one or more of 'S', 'R', or 'U'; if
386 unspecified, defaults to all three.
388 'classnum': limit to this package class.
390 'use_override': for line items generated by an add-on package, use the class
391 of the add-on rather than the base package.
393 'freq': limit to packages with this frequency. Currently uses the part_pkg
394 frequency, so term discounted packages may give odd results.
396 'distribute': for non-monthly recurring charges, ignore the invoice
397 date. Instead, consider the line item's starting/ending dates. Determine
398 the fraction of the line item duration that falls within the specified
399 interval and return that fraction of the recurring charges. This is
400 somewhat experimental.
402 'project': enable if this is a projected period. This is very experimental.
408 my( $speriod, $eperiod, $agentnum, %opt ) = @_;
410 my %charges = map {$_=>1} split('', $opt{'charges'} || 'SRU');
413 $sum += $self->cust_bill_pkg_setup(@_) if $charges{S};
414 $sum += $self->cust_bill_pkg_recur(@_) if $charges{R};
415 $sum += $self->cust_bill_pkg_detail(@_) if $charges{U};
419 my $cust_bill_pkg_join = '
420 LEFT JOIN cust_bill USING ( invnum )
421 LEFT JOIN cust_main USING ( custnum )
422 LEFT JOIN cust_pkg USING ( pkgnum )
423 LEFT JOIN part_pkg USING ( pkgpart )
424 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart';
426 sub cust_bill_pkg_setup {
428 my ($speriod, $eperiod, $agentnum, %opt) = @_;
429 # no projecting setup fees--use real invoices only
430 # but evaluate this anyway, because the design of projection is that
431 # if there are somehow real setup fees in the future, we want to count
434 $agentnum ||= $opt{'agentnum'};
438 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
439 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
442 push @where, "cust_main.refnum = ". $opt{'refnum'} if $opt{'refnum'};
444 my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0)
447 WHERE " . join(' AND ', grep $_, @where);
449 $self->scalar_sql($total_sql);
452 sub cust_bill_pkg_recur {
454 my ($speriod, $eperiod, $agentnum, %opt) = @_;
456 $agentnum ||= $opt{'agentnum'};
457 my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
461 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
464 push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
466 # subtract all usage from the line item regardless of date
468 if ( $opt{'project'} ) {
469 $item_usage = 'usage'; #already calculated
472 $item_usage = '( SELECT COALESCE(SUM(amount),0)
473 FROM cust_bill_pkg_detail
474 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
476 my $recur_fraction = '';
478 if ( $opt{'distribute'} ) {
479 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
481 "$cust_bill_pkg.sdate < $eperiod",
482 "$cust_bill_pkg.edate >= $speriod",
484 # the fraction of edate - sdate that's within [speriod, eperiod]
485 $recur_fraction = " *
486 CAST(LEAST($eperiod, $cust_bill_pkg.edate) -
487 GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) /
488 ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
491 # we don't want to have to create v_cust_bill
492 my $_date = $opt{'project'} ? 'v_cust_bill_pkg._date' : 'cust_bill._date';
494 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date);
497 my $total_sql = 'SELECT '.
498 "COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)
501 WHERE ".join(' AND ', grep $_, @where);
503 $self->scalar_sql($total_sql);
506 =item cust_bill_pkg_detail: the total usage charges in detail lines.
508 Arguments as for C<cust_bill_pkg>, plus:
510 'usageclass': limit to this usage class number.
514 sub cust_bill_pkg_detail {
515 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
517 my @where = ( "cust_bill_pkg.pkgnum != 0" );
519 push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
521 $agentnum ||= $opt{'agentnum'};
524 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
525 $self->with_usageclass($opt{'usageclass'}),
528 if ( $opt{'distribute'} ) {
529 # then limit according to the usage time, not the billing date
530 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
531 'cust_bill_pkg_detail.startdate'
535 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
540 my $total_sql = " SELECT SUM(amount) ";
543 " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
544 if $opt{average_per_cust_pkg};
547 " FROM cust_bill_pkg_detail
548 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
549 LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
550 LEFT JOIN cust_main USING ( custnum )
551 LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
552 LEFT JOIN part_pkg USING ( pkgpart )
553 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
554 WHERE ".join( ' AND ', grep $_, @where );
556 $self->scalar_sql($total_sql);
560 sub cust_bill_pkg_discount {
561 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
564 #my $comparison = '';
565 #if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
567 # $comparison = "IS NULL";
569 # $comparison = "= $1";
572 # if ( $opt{'use_override'} ) {
574 # part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
575 # override.classnum $comparison AND pkgpart_override IS NOT NULL
578 # $where = "part_pkg.classnum $comparison";
582 $agentnum ||= $opt{'agentnum'};
585 " SELECT COALESCE( SUM( cust_bill_pkg_discount.amount ), 0 ) ";
588 # " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
589 # if $opt{average_per_cust_pkg};
592 " FROM cust_bill_pkg_discount
593 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
594 LEFT JOIN cust_bill USING ( invnum )
595 LEFT JOIN cust_main USING ( custnum )
596 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
597 # LEFT JOIN cust_pkg_discount USING ( pkgdiscountnum )
598 # LEFT JOIN discount USING ( discountnum )
599 # LEFT JOIN cust_pkg USING ( pkgnum )
600 # LEFT JOIN part_pkg USING ( pkgpart )
601 # LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
603 return $self->scalar_sql($total_sql);
607 sub setup_pkg { shift->pkg_field( 'setup', @_ ); }
608 sub susp_pkg { shift->pkg_field( 'susp', @_ ); }
609 sub cancel_pkg { shift->pkg_field( 'cancel', @_ ); }
612 my( $self, $field, $speriod, $eperiod, $agentnum ) = @_;
614 SELECT COUNT(*) FROM cust_pkg
615 LEFT JOIN cust_main USING ( custnum )
616 WHERE ". $self->in_time_period_and_agent( $speriod,
625 #this is going to be harder..
627 # my( $self, $speriod, $eperiod, $agentnum ) = @_;
628 # $self->scalar_sql("
629 # SELECT COUNT(*) FROM h_cust_pkg
634 sub in_time_period_and_agent {
635 my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
636 my $col = @_ ? shift() : '_date';
638 my $sql = "$col >= $speriod AND $col < $eperiod";
641 $sql .= " AND cust_main.agentnum = $agentnum"
644 #agent virtualization
646 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table'=>'cust_main' );
652 my ( $self, %opt ) = @_;
654 if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
655 $sql .= " and custnum = $1 ";
657 if ( $opt{'refnum'} =~ /^(\d+)$/ ) {
658 $sql .= " and refnum = $1 ";
665 my ($classnum, $use_override) = @_;
666 return '' unless $classnum =~ /^\d+$/;
668 if ( $classnum == 0 ) {
669 $comparison = 'IS NULL';
672 $comparison = "= $classnum";
674 if ( $use_override ) {
676 part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
677 override.classnum $comparison AND pkgpart_override IS NOT NULL
681 return "part_pkg.classnum $comparison";
685 sub with_usageclass {
687 my ($classnum, $use_override) = @_;
688 return '' unless $classnum =~ /^\d+$/;
690 if ( $classnum == 0 ) {
691 $comparison = 'IS NULL';
694 $comparison = "= $classnum";
696 return "cust_bill_pkg_detail.classnum $comparison";
700 my( $self, $sql ) = ( shift, shift );
701 my $sth = dbh->prepare($sql) or die dbh->errstr;
702 warn "FS::Report::Table\n$sql\n" if $DEBUG;
704 or die "Unexpected error executing statement $sql: ". $sth->errstr;
705 $sth->fetchrow_arrayref->[0] || 0;
714 =item init_projection
716 Sets up for future projection of all observables on the report. Currently
717 this is limited to 'cust_bill_pkg'.
721 sub init_projection {
722 # this is weird special case stuff--some redesign may be needed
723 # to use it for anything else
726 if ( driver_name ne 'Pg' ) {
727 # also database-specific for now
728 die "projection reports not supported on this platform";
731 my %items = map {$_ => 1} @{ $self->{items} };
732 if ($items{'cust_bill_pkg'}) {
736 # could use TEMPORARY TABLE but we're already transaction-protected
737 'DROP TABLE IF EXISTS v_cust_bill_pkg',
738 'CREATE TABLE v_cust_bill_pkg ' .
739 '(LIKE cust_bill_pkg,
740 usage numeric(10,2), _date integer, expire integer)',
741 # XXX this should be smart enough to take only the ones with
742 # sdate/edate overlapping the ROI, for performance
743 "INSERT INTO v_cust_bill_pkg (
744 SELECT cust_bill_pkg.*,
745 (SELECT COALESCE(SUM(amount),0) FROM cust_bill_pkg_detail
746 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum),
749 FROM cust_bill_pkg $cust_bill_pkg_join
752 foreach my $sql (@sql) {
753 warn "[init_projection] $sql\n" if $DEBUG;
754 $dbh->do($sql) or die $dbh->errstr;
759 =item extend_projection START END
761 Generates data for the next period of projection. This will be called
762 for sequential periods where the END of one equals the START of the next
767 sub extend_projection {
769 my ($speriod, $eperiod) = @_;
770 my %items = map {$_ => 1} @{ $self->{items} };
771 if ($items{'cust_bill_pkg'}) {
773 # Find all line items that end after the start of the period (and have
774 # recurring fees, and don't expire before they end). Choose the latest
775 # one for each package. If it ends before the end of the period, copy
776 # it forward by one billing period.
777 # Repeat this until the latest line item for each package no longer ends
778 # within the period. This is certain to happen in finitely many
779 # iterations as long as freq > 0.
780 # - Pg only, obviously.
781 # - Gives bad results if freq_override is used.
782 my @fields = ( FS::cust_bill_pkg->fields, qw( usage _date expire ) );
783 my $insert_fields = join(',', @fields);
784 my $add_freq = sub { # emulate FS::part_pkg::add_freq
786 "EXTRACT( EPOCH FROM TO_TIMESTAMP($field) + (CASE WHEN freq ~ E'\\\\D' ".
787 "THEN freq ELSE freq || 'mon' END)::INTERVAL) AS $field";
791 $_ = $add_freq->('edate');
793 elsif ($_ eq 'sdate') {
794 $_ = 'edate AS sdate'
796 elsif ($_ eq 'setup') {
797 $_ = '0 AS setup' #because recurring only
799 elsif ($_ eq '_date') {
800 $_ = $add_freq->('_date');
803 my $select_fields = join(',', @fields);
806 # Subquery here because we need to DISTINCT the whole set, select the
807 # latest charge per pkgnum, and _then_ check edate < $eperiod
808 # and edate < expire.
809 "INSERT INTO v_cust_bill_pkg ($insert_fields)
810 SELECT $select_fields FROM (
811 SELECT DISTINCT ON (pkgnum) * FROM v_cust_bill_pkg
812 WHERE edate >= $speriod
816 ORDER BY pkgnum, edate DESC
818 WHERE edate < $eperiod AND (edate < expire OR expire IS NULL)";
821 warn "[extend_projection] $sql\n" if $DEBUG;
822 $rows = $dbh->do($sql) or die $dbh->errstr;
823 warn "[extend_projection] $rows rows\n" if $DEBUG;
834 L<FS::Report::Table::Monthly>, reports in the web interface.