1 package FS::Report::Table;
5 use Time::Local qw( timelocal );
6 use FS::UID qw( dbh driver_name );
11 our $DEBUG = 0; # turning this on will trace all SQL statements, VERY noisy
13 our $CACHE; # feel free to use this for whatever
15 FS::UID->install_callback(sub {
16 $CACHE = Cache::FileCache->new( {
17 'namespace' => __PACKAGE__,
18 'cache_root' => "$FS::UID::cache_dir/cache.$FS::UID::datasrc",
20 # reset this on startup (causes problems with database backups, etc.)
21 $CACHE->remove('tower_pkg_cache_update');
26 FS::Report::Table - Tables of report data
30 See the more specific report objects, currently only
31 FS::Report::Table::Monthly and FS::Report::Table::Daily.
35 The common interface for an observable named 'foo' is:
37 $report->foo($startdate, $enddate, $agentnum, %options)
39 This returns a scalar value for foo, over the period from
40 $startdate to $enddate, limited to agent $agentnum, subject to
45 =item signups: The number of customers signed up. Options are:
47 - cust_classnum: limit to this customer class
48 - pkg_classnum: limit to customers with a package of this class. If this is
49 an arrayref, it's an ANY match.
50 - refnum: limit to this advertising source
51 - indirect: boolean; limit to customers that have a referral_custnum that
52 matches the advertising source
57 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
58 my @where = ( $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
59 'cust_main.signupdate')
62 if ( $opt{'indirect'} ) {
63 $join = " JOIN cust_main AS referring_cust_main".
64 " ON (cust_main.referral_custnum = referring_cust_main.custnum)";
66 if ( $opt{'refnum'} ) {
67 push @where, "referring_cust_main.refnum = ".$opt{'refnum'};
70 elsif ( $opt{'refnum'} ) {
71 push @where, "refnum = ".$opt{'refnum'};
74 push @where, $self->with_cust_classnum(%opt);
75 if ( $opt{'pkg_classnum'} ) {
76 my $classnum = $opt{'pkg_classnum'};
77 $classnum = [ $classnum ] unless ref $classnum;
78 @$classnum = grep /^\d+$/, @$classnum;
80 my $in = 'IN ('. join(',', @$classnum). ')';
82 "EXISTS(SELECT 1 FROM cust_pkg JOIN part_pkg USING (pkgpart) ".
83 "WHERE cust_pkg.custnum = cust_main.custnum ".
84 "AND part_pkg.classnum $in".
90 "SELECT COUNT(*) FROM cust_main $join WHERE ".join(' AND ', @where)
94 =item invoiced: The total amount charged on all invoices.
98 sub invoiced { #invoiced
99 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
101 my $sql = 'SELECT SUM(cust_bill.charged) FROM cust_bill';
102 if ( $opt{'setuprecur'} ) {
103 $sql = 'SELECT SUM('.
104 FS::cust_bill_pkg->charged_sql($speriod, $eperiod, %opt).
105 ') FROM cust_bill_pkg JOIN cust_bill USING (invnum)';
110 LEFT JOIN cust_main USING ( custnum )
111 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
112 $self->for_opts(%opt)
117 =item netsales: invoiced - netcredits
121 sub netsales { #net sales
122 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
124 $self->invoiced( $speriod, $eperiod, $agentnum, %opt)
125 - $self->netcredits($speriod, $eperiod, $agentnum, %opt);
128 =item cashflow: payments - refunds
133 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
135 $self->payments($speriod, $eperiod, $agentnum, %opt)
136 - $self->refunds( $speriod, $eperiod, $agentnum, %opt);
139 =item netcashflow: payments - netrefunds
144 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
146 $self->receipts( $speriod, $eperiod, $agentnum, %opt)
147 - $self->netrefunds( $speriod, $eperiod, $agentnum, %opt);
150 =item payments: The sum of payments received 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 credits: The sum of credits issued in the period.
170 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
172 SELECT SUM(cust_credit.amount)
174 LEFT JOIN cust_main USING ( custnum )
175 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
176 $self->for_opts(%opt)
180 =item refunds: The sum of refunds paid in the period.
185 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
189 LEFT JOIN cust_main USING ( custnum )
190 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
191 $self->for_opts(%opt)
195 =item netcredits: The sum of credit applications to invoices in the period.
200 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
202 my $sql = 'SELECT SUM(cust_credit_bill.amount) FROM cust_credit_bill';
203 if ( $opt{'setuprecur'} ) {
204 $sql = 'SELECT SUM('.
205 FS::cust_bill_pkg->credited_sql($speriod, $eperiod, %opt).
206 ') FROM cust_bill_pkg';
211 LEFT JOIN cust_bill USING ( invnum )
212 LEFT JOIN cust_main USING ( custnum )
213 WHERE ". $self->in_time_period_and_agent( $speriod,
218 $self->for_opts(%opt)
222 =item receipts: The sum of payment applications to invoices in the period.
226 sub receipts { #net payments
227 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
229 my $sql = 'SELECT SUM(cust_bill_pay.amount) FROM cust_bill_pay';
230 if ( $opt{'setuprecur'} ) {
231 $sql = 'SELECT SUM('.
232 #in practice, but not appearance, paid_sql accepts end before start
233 FS::cust_bill_pkg->paid_sql($eperiod, $speriod, %opt).
234 ') FROM cust_bill_pkg';
239 LEFT JOIN cust_bill USING ( invnum )
240 LEFT JOIN cust_main USING ( custnum )
241 WHERE ". $self->in_time_period_and_agent( $speriod,
246 $self->for_opts(%opt)
250 =item netrefunds: The sum of refund applications to credits in the period.
255 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
257 SELECT SUM(cust_credit_refund.amount)
258 FROM cust_credit_refund
259 LEFT JOIN cust_credit USING ( crednum )
260 LEFT JOIN cust_main USING ( custnum )
261 WHERE ". $self->in_time_period_and_agent( $speriod,
266 $self->for_opts(%opt)
270 =item discounted: The sum of discounts on invoices in the period.
275 my( $self, $speriod, $eperiod, $agentnum, %opt) = @_;
277 my $sql = 'SELECT SUM(';
278 if ($opt{'setuprecur'}) {
279 # (This isn't exact but it works in most cases.)
280 # When splitting into setup/recur values,
281 # if the discount is allowed to apply to setup fees (discount.setup = 'Y')
282 # then split it between the "setup" and "recurring" rows in proportion to
283 # the "unitsetup" and "unitrecur" fields of the line item.
286 WHEN discount.setup = 'Y'
287 AND ((COALESCE(cust_bill_pkg.unitsetup,0) > 0)
288 OR (COALESCE(cust_bill_pkg.unitrecur,0) > 0))
291 if ($opt{'setuprecur'} eq 'setup') {
292 $sql .= ' (COALESCE(cust_bill_pkg.unitsetup,0)';
293 } elsif ($opt{'setuprecur'} eq 'recur') {
294 $sql .= ' (COALESCE(cust_bill_pkg.unitrecur,0)';
296 die 'Unrecognized value for setuprecur';
298 $sql .= ' / (COALESCE(cust_bill_pkg.unitsetup,0) + COALESCE(cust_bill_pkg.unitrecur,0)))';
299 $sql .= " * cust_bill_pkg_discount.amount\n";
300 # Otherwise, show it all as "recurring"
301 if ($opt{'setuprecur'} eq 'setup') {
303 } elsif ($opt{'setuprecur'} eq 'recur') {
304 $sql .= " ELSE cust_bill_pkg_discount.amount\n";
308 # simple case, no setuprecur
309 $sql .= "cust_bill_pkg_discount.amount\n";
312 ) FROM cust_bill_pkg_discount
313 JOIN cust_bill_pkg USING ( billpkgnum )
314 JOIN cust_bill USING ( invnum )
315 JOIN cust_main USING ( custnum )
317 if ($opt{'setuprecur'}) {
319 JOIN cust_pkg_discount USING ( pkgdiscountnum )
320 LEFT JOIN discount USING ( discountnum )
326 . $self->in_time_period_and_agent( $speriod,
331 . $self->for_opts(%opt)
335 =item gross: invoiced + discounted
340 my( $self, $speriod, $eperiod, $agentnum, %opt) = @_;
341 $self->invoiced( $speriod, $eperiod, $agentnum, %opt)
342 + $self->discounted( $speriod, $eperiod, $agentnum, %opt);
347 #these should be auto-generated or $AUTOLOADed or something
349 my( $self, $speriod, $eperiod, $agentnum ) = @_;
350 $speriod = $self->_subtract_11mo($speriod);
351 $self->invoiced($speriod, $eperiod, $agentnum);
355 my( $self, $speriod, $eperiod, $agentnum ) = @_;
356 $speriod = $self->_subtract_11mo($speriod);
357 $self->netsales($speriod, $eperiod, $agentnum);
361 my( $self, $speriod, $eperiod, $agentnum ) = @_;
362 $speriod = $self->_subtract_11mo($speriod);
363 $self->receipts($speriod, $eperiod, $agentnum);
367 my( $self, $speriod, $eperiod, $agentnum ) = @_;
368 $speriod = $self->_subtract_11mo($speriod);
369 $self->payments($speriod, $eperiod, $agentnum);
373 my( $self, $speriod, $eperiod, $agentnum ) = @_;
374 $speriod = $self->_subtract_11mo($speriod);
375 $self->credits($speriod, $eperiod, $agentnum);
378 sub netcredits_12mo {
379 my( $self, $speriod, $eperiod, $agentnum ) = @_;
380 $speriod = $self->_subtract_11mo($speriod);
381 $self->netcredits($speriod, $eperiod, $agentnum);
385 my( $self, $speriod, $eperiod, $agentnum ) = @_;
386 $speriod = $self->_subtract_11mo($speriod);
387 $self->cashflow($speriod, $eperiod, $agentnum);
390 sub netcashflow_12mo {
391 my( $self, $speriod, $eperiod, $agentnum ) = @_;
392 $speriod = $self->_subtract_11mo($speriod);
393 $self->cashflow($speriod, $eperiod, $agentnum);
397 my( $self, $speriod, $eperiod, $agentnum ) = @_;
398 $speriod = $self->_subtract_11mo($speriod);
399 $self->refunds($speriod, $eperiod, $agentnum);
402 sub netrefunds_12mo {
403 my( $self, $speriod, $eperiod, $agentnum ) = @_;
404 $speriod = $self->_subtract_11mo($speriod);
405 $self->netrefunds($speriod, $eperiod, $agentnum);
409 #not being too bad with the false laziness
411 my($self, $time) = @_;
412 my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
414 if ( $mon < 0 ) { $mon+=12; $year--; }
415 timelocal($sec,$min,$hour,$mday,$mon,$year);
418 =item _subtract_months: subtracts the number of months from a given unix date stamp
422 sub _subtract_months {
423 my($self, $number_of_months, $time) = @_;
424 my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
425 $mon -= $number_of_months;
426 if ( $mon < 0 ) { $mon+=12; $year--; }
427 timelocal($sec,$min,$hour,$mday,$mon,$year);
430 =item cust_pkg_setup_cost: The total setup costs of packages setup in the period
432 'classnum': limit to this package class.
436 sub cust_pkg_setup_cost {
437 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
440 if ( $opt{'classnum'} ne '' ) {
441 my $classnums = $opt{'classnum'};
442 $classnums = [ $classnums ] if !ref($classnums);
443 @$classnums = grep /^\d+$/, @$classnums;
444 $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
448 $agentnum ||= $opt{'agentnum'};
450 my $total_sql = " SELECT SUM(part_pkg.setup_cost) ";
451 $total_sql .= " FROM cust_pkg
452 LEFT JOIN cust_main USING ( custnum )
453 LEFT JOIN part_pkg USING ( pkgpart )
456 AND ".$self->in_time_period_and_agent(
457 $speriod, $eperiod, $agentnum, 'cust_pkg.setup');
458 return $self->scalar_sql($total_sql);
461 =item cust_pkg_recur_cust: the total recur costs of packages in the period
463 'classnum': limit to this package class.
467 sub cust_pkg_recur_cost {
468 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
471 if ( $opt{'classnum'} ne '' ) {
472 my $classnums = $opt{'classnum'};
473 $classnums = [ $classnums ] if !ref($classnums);
474 @$classnums = grep /^\d+$/, @$classnums;
475 $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
479 $agentnum ||= $opt{'agentnum'};
480 # duplication of in_time_period_and_agent
481 # because we do it a little differently here
482 $where .= " AND cust_main.agentnum = $agentnum" if $agentnum;
484 $FS::CurrentUser::CurrentUser->agentnums_sql('table' => 'cust_main');
486 my $total_sql = " SELECT SUM(part_pkg.recur_cost) ";
487 $total_sql .= " FROM cust_pkg
488 LEFT JOIN cust_main USING ( custnum )
489 LEFT JOIN part_pkg USING ( pkgpart )
492 AND cust_pkg.setup < $eperiod
493 AND (cust_pkg.cancel > $speriod OR cust_pkg.cancel IS NULL)
495 return $self->scalar_sql($total_sql);
498 =item cust_bill_pkg: the total package charges on invoice line items.
500 'charges': limit the type of charges included (setup, recur, usage, discount, taxes).
501 Should be a string containing one or more of 'S', 'R', or 'U'; or 'D' or 'T' (discount
502 and taxes should not be combined with the others.) If unspecified, defaults to 'SRU'.
504 'classnum': limit to this package class.
506 'use_override': for line items generated by an add-on package, use the class
507 of the add-on rather than the base package.
509 'average_per_cust_pkg': divide the result by the number of distinct packages.
511 'distribute': for non-monthly recurring charges, ignore the invoice
512 date. Instead, consider the line item's starting/ending dates. Determine
513 the fraction of the line item duration that falls within the specified
514 interval and return that fraction of the recurring charges. This is
515 somewhat experimental.
517 'project': enable if this is a projected period. This is very experimental.
523 my( $speriod, $eperiod, $agentnum, %opt ) = @_;
525 my %charges = map {$_=>1} split('', $opt{'charges'} || 'SRU');
528 $sum += $self->cust_bill_pkg_setup(@_) if $charges{S};
529 $sum += $self->cust_bill_pkg_recur(@_) if $charges{R};
530 $sum += $self->cust_bill_pkg_detail(@_) if $charges{U};
531 $sum += $self->cust_bill_pkg_discount(@_) if $charges{D};
532 $sum += $self->cust_bill_pkg_taxes(@_) if $charges{T};
534 if ($opt{'average_per_cust_pkg'}) {
535 my $count = $self->cust_bill_pkg_count_pkgnum(@_);
536 return '' if $count == 0;
537 $sum = sprintf('%.2f', $sum / $count);
542 my $cust_bill_pkg_join = '
543 LEFT JOIN cust_bill USING ( invnum )
544 LEFT JOIN cust_main USING ( custnum )
545 LEFT JOIN cust_pkg USING ( pkgnum )
546 LEFT JOIN part_pkg USING ( pkgpart )
547 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
548 LEFT JOIN part_fee USING ( feepart )';
550 sub cust_bill_pkg_setup {
552 my ($speriod, $eperiod, $agentnum, %opt) = @_;
553 # no projecting setup fees--use real invoices only
554 # but evaluate this anyway, because the design of projection is that
555 # if there are somehow real setup fees in the future, we want to count
558 $agentnum ||= $opt{'agentnum'};
561 '(pkgnum != 0 OR feepart IS NOT NULL)',
562 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
563 $self->with_report_option(%opt),
564 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
565 $self->with_refnum(%opt),
566 $self->with_cust_classnum(%opt)
569 my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0)
572 WHERE " . join(' AND ', grep $_, @where);
574 $self->scalar_sql($total_sql);
577 sub _cust_bill_pkg_recurring {
578 # returns the FROM/WHERE part of the statement to query all recurring
579 # line items in the period
581 my ($speriod, $eperiod, $agentnum, %opt) = @_;
583 $agentnum ||= $opt{'agentnum'};
584 my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
587 '(pkgnum != 0 OR feepart IS NOT NULL)',
588 $self->with_report_option(%opt),
589 $self->with_refnum(%opt),
590 $self->with_cust_classnum(%opt)
593 my $where_classnum = $self->with_classnum($opt{'classnum'}, $opt{'use_override'});
594 if ($opt{'project'}) {
595 $where_classnum =~ s/\bcust_bill_pkg/v_cust_bill_pkg/g;
597 push @where, $where_classnum;
599 if ( $opt{'distribute'} ) {
600 $where[0] = 'pkgnum != 0'; # specifically exclude fees
601 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
603 "$cust_bill_pkg.sdate < $eperiod",
604 "$cust_bill_pkg.edate >= $speriod",
608 # we don't want to have to create v_cust_bill
609 my $_date = $opt{'project'} ? 'v_cust_bill_pkg._date' : 'cust_bill._date';
611 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date);
614 if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
615 push @where, "(cust_main.custnum = $1)";
621 WHERE ".join(' AND ', grep $_, @where);
625 =item cust_bill_pkg_recur: the total recur charges
627 Most arguments as for C<cust_bill_pkg>, plus:
629 'custnum': limit to this customer
631 'cost': if true, return total recur costs instead
635 sub cust_bill_pkg_recur {
637 my ($speriod, $eperiod, $agentnum, %opt) = @_;
639 # subtract all usage from the line item regardless of date
641 if ( $opt{'project'} ) {
642 $item_usage = 'usage'; #already calculated
645 $item_usage = '( SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
646 FROM cust_bill_pkg_detail
647 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
650 my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
652 my $recur_fraction = '';
653 if ($opt{'distribute'}) {
654 # the fraction of edate - sdate that's within [speriod, eperiod]
655 $recur_fraction = " *
656 CAST(LEAST($eperiod, $cust_bill_pkg.edate) -
657 GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) /
658 ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
661 my $total_sql = $opt{'cost'}
662 ? "SELECT SUM(part_pkg.recur_cost)"
663 : "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)";
665 $total_sql .= $self->_cust_bill_pkg_recurring(@_);
667 $self->scalar_sql($total_sql);
670 sub cust_bill_pkg_count_pkgnum {
671 # for ARPU calculation
673 my $total_sql = 'SELECT COUNT(DISTINCT pkgnum) '.
674 $self->_cust_bill_pkg_recurring(@_);
676 $self->scalar_sql($total_sql);
679 =item cust_bill_pkg_detail: the total usage charges in detail lines.
681 Most arguments as for C<cust_bill_pkg>, plus:
683 'usageclass': limit to this usage class number.
685 'custnum': limit to this customer
687 'cost': if true, return total usage costs instead
691 sub cust_bill_pkg_detail {
692 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
695 ( "(cust_bill_pkg.pkgnum != 0 OR cust_bill_pkg.feepart IS NOT NULL)" );
697 $agentnum ||= $opt{'agentnum'};
700 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
701 $self->with_usageclass($opt{'usageclass'}),
702 $self->with_report_option(%opt),
703 $self->with_refnum(%opt),
704 $self->with_cust_classnum(%opt)
707 if ( $opt{'distribute'} ) {
709 $where[0] = 'cust_bill_pkg.pkgnum != 0';
710 # and limit according to the usage time, not the billing date
711 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
712 'cust_bill_pkg_detail.startdate'
716 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
721 if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
722 push @where, "(cust_main.custnum = $1)";
725 my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) ";
728 $extra_join = " JOIN cdr USING ( detailnum ) ";
729 $total_sql = " SELECT SUM(cdr.rated_cost) ";
733 " FROM cust_bill_pkg_detail
734 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
735 LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
736 LEFT JOIN cust_main USING ( custnum )
737 LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
738 LEFT JOIN part_pkg USING ( pkgpart )
739 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
740 LEFT JOIN part_fee USING ( feepart )
742 " WHERE ".join( ' AND ', grep $_, @where );
744 $self->scalar_sql($total_sql);
748 =item cust_bill_pkg_discount: Discounts issued
750 Arguments: agentnum, refnum, cust_classnum
754 sub cust_bill_pkg_discount {
756 my ($speriod, $eperiod, $agentnum, %opt) = @_;
757 # apply all the same constraints here as for setup/recur
759 $agentnum ||= $opt{'agentnum'};
762 '(pkgnum != 0 OR feepart IS NOT NULL)',
763 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
764 $self->with_report_option(%opt),
765 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
766 $self->with_refnum(%opt),
767 $self->with_cust_classnum(%opt)
770 my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg_discount.amount), 0)
771 FROM cust_bill_pkg_discount
772 JOIN cust_bill_pkg USING (billpkgnum)
774 WHERE " . join(' AND ', grep $_, @where);
776 $self->scalar_sql($total_sql);
779 =item cust_bill_pkg_discount_or_waived: Discounts and waived fees issued
781 Arguments: agentnum, refnum, cust_classnum
785 sub cust_bill_pkg_discount_or_waived {
788 my ($speriod, $eperiod, $agentnum, %opt) = @_;
790 $agentnum ||= $opt{'agentnum'};
797 cust_bill_pkg_discount.amount,
798 CAST(( SELECT optionvalue
801 part_pkg_option.pkgpart = cust_pkg.pkgpart
802 AND optionname = 'setup_fee'
809 LEFT JOIN cust_bill_pkg_discount USING (billpkgnum)
810 LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
811 LEFT JOIN part_pkg USING (pkgpart)
812 LEFT JOIN cust_bill USING ( invnum )
813 LEFT JOIN cust_main ON cust_pkg.custnum = cust_main.custnum
816 cust_bill_pkg_discount.billpkgdiscountnum IS NOT NULL
818 cust_pkg.setup = cust_bill_pkg.sdate
819 AND cust_pkg.waive_setup = 'Y'
822 AND cust_bill_pkg.pkgpart_override IS NULL
824 map { " AND ( $_ ) " }
826 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
827 $self->with_report_option(%opt),
828 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
830 $self->scalar_sql($total_sql);
833 sub cust_bill_pkg_taxes {
835 my ($speriod, $eperiod, $agentnum, %opt) = @_;
837 $agentnum ||= $opt{'agentnum'};
840 '(cust_bill_pkg.pkgnum != 0 OR feepart IS NOT NULL)',
841 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
842 $self->with_report_option(%opt),
843 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
844 $self->with_refnum(%opt),
845 $self->with_cust_classnum(%opt)
848 my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg_tax_location.amount),0)
851 LEFT JOIN cust_bill_pkg_tax_location
852 ON (cust_bill_pkg.billpkgnum = cust_bill_pkg_tax_location.taxable_billpkgnum)
853 WHERE " . join(' AND ', grep $_, @where);
855 $self->scalar_sql($total_sql);
858 #all credits applied to matching pkg line items (ie not taxes)
860 sub cust_bill_pkg_credits {
862 my ($speriod, $eperiod, $agentnum, %opt) = @_;
864 $agentnum ||= $opt{'agentnum'};
867 '(cust_bill_pkg.pkgnum != 0 OR feepart IS NOT NULL)',
868 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
869 $self->with_report_option(%opt),
870 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
871 $self->with_refnum(%opt),
872 $self->with_cust_classnum(%opt)
875 my $total_sql = "SELECT COALESCE(SUM(cust_credit_bill_pkg.amount),0)
878 LEFT JOIN cust_credit_bill_pkg
880 WHERE " . join(' AND ', grep $_, @where);
882 $self->scalar_sql($total_sql);
885 ##### package churn report #####
887 =item active_pkg: The number of packages that were active at the start of
888 the period. The end date of the period is ignored. Options:
890 - refnum: Limit to customers with this advertising source.
891 - classnum: Limit to packages with this class.
892 - towernum: Limit to packages that have a broadband service with this tower.
893 - zip: Limit to packages with this service location zip code.
895 Except for zip, any of these can be an arrayref to allow multiple values for
898 =item setup_pkg: The number of packages with setup dates in the period. This
899 excludes packages created by package changes. Options are as for active_pkg.
901 =item susp_pkg: The number of packages that were suspended in the period
902 (and not canceled). Options are as for active_pkg.
904 =item unsusp_pkg: The number of packages that were unsuspended in the period.
905 Options are as for active_pkg.
907 =item cancel_pkg: The number of packages with cancel dates in the period.
908 Excludes packages that were canceled to be changed to a new package. Options
909 are as for active_pkg.
915 $self->churn_pkg('active', @_);
920 $self->churn_pkg('setup', @_);
925 $self->churn_pkg('cancel', @_);
930 $self->churn_pkg('susp', @_);
935 $self->churn_pkg('unsusp', @_);
938 sub total_revenue_pkg {
940 my $active_revenue = $self->revenue_pkg('active', @_);
941 my $setup_revenue = $self->revenue_pkg('setup', @_);
942 my $return = $active_revenue + $setup_revenue;
947 sub total_revenue_diff {
950 my @current_month = @_;
951 my @previous_month = @current_month;
953 $previous_month[0] = $self->_subtract_months(1,$current_month[0]);
954 $previous_month[1] = $self->_subtract_months(1,$current_month[1]);
956 my $previous_revenue = $self->revenue_pkg('active', @previous_month) + $self->revenue_pkg('setup', @previous_month);
957 my $current_revenue = $self->revenue_pkg('active', @current_month) + $self->revenue_pkg('setup', @current_month);
959 my $return = $current_revenue - $previous_revenue;
966 my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_;
970 FS::h_cust_pkg->churn_fromwhere_sql( $status, $speriod, $eperiod);
972 push @where, $self->pkg_where(%opt, 'agentnum' => $agentnum);
976 ## if package has changed and has not reached next due date it will not be in h_cust_bill.
977 ## this causes problems with future months, needed to use change_pkgnum instead.
979 if ($status eq "active") {
980 $sql = "SELECT DISTINCT ON (revenue.pkgnum) revenue.pkgnum AS pkgnum, revenue.recur AS revenue
982 JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
983 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
984 JOIN h_cust_bill_pkg AS revenue ON ((cust_pkg.pkgnum = revenue.pkgnum OR cust_pkg.change_pkgnum = revenue.pkgnum) AND cust_pkg.history_date < $speriod )
987 elsif ($status eq "setup") {
988 $sql = "SELECT DISTINCT ON (revenue.pkgnum) revenue.pkgnum AS pkgnum, revenue.setup AS revenue
990 JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
991 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)
992 JOIN h_cust_bill_pkg AS revenue ON (cust_pkg.pkgnum = revenue.pkgnum AND
993 ( cust_pkg.setup > $speriod AND cust_pkg.setup < $eperiod) )
997 $sql .= ' WHERE '.join(' AND ', @where)
1000 $sql .= "ORDER BY revenue.pkgnum ASC, revenue.history_date DESC";
1002 my $revenue_sql = "SELECT sum(rev.revenue) AS total_revenue FROM ( $sql ) AS rev";
1004 $self->scalar_sql($revenue_sql);
1009 my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_;
1010 my ($from, @where) =
1011 FS::h_cust_pkg->churn_fromwhere_sql( $status, $speriod, $eperiod);
1013 push @where, $self->pkg_where(%opt, 'agentnum' => $agentnum);
1015 my $sql = "SELECT COUNT(*) FROM $from
1016 JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
1017 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)";
1018 $sql .= ' WHERE '.join(' AND ', @where)
1021 $self->scalar_sql($sql);
1028 "part_pkg.freq != '0'",
1029 $self->with_refnum(%opt),
1030 $self->with_towernum(%opt),
1031 $self->with_zip(%opt),
1033 if ($opt{agentnum} =~ /^(\d+)$/) {
1034 push @where, "cust_main.agentnum = $1";
1036 if ($opt{classnum}) {
1037 my $classnum = $opt{classnum};
1038 $classnum = [ $classnum ] if !ref($classnum);
1039 @$classnum = grep /^\d+$/, @$classnum;
1040 my $in = 'IN ('. join(',', @$classnum). ')';
1041 push @where, "COALESCE(part_pkg.classnum, 0) $in" if scalar @$classnum;
1046 ##### end of package churn report stuff #####
1048 ##### customer churn report #####
1050 =item active_cust: The number of customers who had any active recurring
1051 packages at the start of the period. The end date is ignored, agentnum is
1052 mandatory, and no other parameters are accepted.
1054 =item started_cust: The number of customers who had no active packages at
1055 the start of the period, but had active packages at the end. Like
1056 active_cust, agentnum is mandatory and no other parameters are accepted.
1058 =item suspended_cust: The number of customers who had active packages at
1059 the start of the period, and at the end had no active packages but some
1060 suspended packages. Note that this does not necessarily mean that their
1061 packages were suspended during the period.
1063 =item resumed_cust: The inverse of suspended_cust: the number of customers
1064 who had suspended packages and no active packages at the start of the
1065 period, and active packages at the end.
1067 =item cancelled_cust: The number of customers who had active packages
1068 at the start of the period, and only cancelled packages at the end.
1074 $self->churn_cust(@_)->{active};
1078 $self->churn_cust(@_)->{started};
1080 sub suspended_cust {
1082 $self->churn_cust(@_)->{suspended};
1086 $self->churn_cust(@_)->{resumed};
1088 sub cancelled_cust {
1090 $self->churn_cust(@_)->{cancelled};
1095 my ( $speriod ) = @_;
1097 # run one query for each interval
1098 return $self->{_interval}{$speriod} ||= $self->calculate_churn_cust(@_);
1101 sub calculate_churn_cust {
1103 my ($speriod, $eperiod, $agentnum, %opt) = @_;
1105 my $churn_sql = FS::cust_main::Status->churn_sql($speriod, $eperiod);
1107 $where = " WHERE cust_main.agentnum = $agentnum " if $agentnum;
1110 "FROM cust_main JOIN ($churn_sql) AS churn USING (custnum)".
1113 # query to count the ones with certain status combinations
1115 SELECT SUM((s_active > 0)::int) as active,
1116 SUM((s_active = 0 and e_active > 0)::int) as started,
1117 SUM((s_active > 0 and e_active = 0 and e_suspended > 0)::int)
1119 SUM((s_active = 0 and s_suspended > 0 and e_active > 0)::int)
1121 SUM((e_active = 0 and e_cancelled > s_cancelled)::int)
1123 FROM ($cust_sql) AS x
1126 my $sth = dbh->prepare($total_sql);
1127 $sth->execute or die "failed to execute churn query: " . $sth->errstr;
1129 $self->{_interval}{$speriod} = $sth->fetchrow_hashref;
1132 sub in_time_period_and_agent {
1133 my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
1134 my $col = @_ ? shift() : '_date';
1136 my $sql = "$col >= $speriod AND $col < $eperiod";
1139 $sql .= " AND cust_main.agentnum = $agentnum"
1142 #agent virtualization
1144 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table'=>'cust_main' );
1150 my ( $self, %opt ) = @_;
1152 if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
1153 $sql .= " and custnum = $1 ";
1155 if ( $opt{'refnum'} ) {
1156 my $refnum = $opt{'refnum'};
1157 $refnum = [ $refnum ] if !ref($refnum);
1158 my $in = join(',', grep /^\d+$/, @$refnum);
1159 $sql .= " and refnum IN ($in)" if length $in;
1161 if ( my $where = $self->with_cust_classnum(%opt) ) {
1162 $sql .= " and $where";
1169 my ($self, $classnum, $use_override) = @_;
1170 return '' if $classnum eq '';
1172 $classnum = [ $classnum ] if !ref($classnum);
1173 @$classnum = grep /^\d+$/, @$classnum;
1174 return '' if !@$classnum;
1175 my $in = 'IN ('. join(',', @$classnum). ')';
1177 if ( $use_override ) {
1178 # then include packages if their base package is in the set and they are
1180 # or if they are overridden and their override package is in the set,
1181 # or fees if they are in the set
1183 ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL AND pkgpart_override IS NULL )
1184 OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )
1185 OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL )
1188 # include packages if their base package is in the set,
1189 # or fees if they are in the set
1191 ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL )
1192 OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL )
1197 sub with_usageclass {
1199 my ($classnum, $use_override) = @_;
1200 return '' unless $classnum =~ /^\d+$/;
1202 if ( $classnum == 0 ) {
1203 $comparison = 'IS NULL';
1206 $comparison = "= $classnum";
1208 return "cust_bill_pkg_detail.classnum $comparison";
1211 sub with_report_option {
1212 my ($self, %opt) = @_;
1214 # - report_optionnum: a comma-separated list of numbers. Zero means to
1215 # include packages with _no_ report classes.
1216 # - not_report_optionnum: a comma-separated list. Packages that have
1217 # any of these report options will be excluded from the result.
1218 # Zero does nothing.
1219 # - use_override: also matches line items that are add-ons to a package
1220 # matching the report class.
1221 # - all_report_options: returns only packages that have ALL of the
1222 # report classes listed in $num. Otherwise, will return packages that
1223 # have ANY of those classes.
1225 my @num = ref($opt{'report_optionnum'})
1226 ? @{ $opt{'report_optionnum'} }
1227 : split(/\s*,\s*/, $opt{'report_optionnum'});
1228 my @not_num = ref($opt{'not_report_optionnum'})
1229 ? @{ $opt{'not_report_optionnum'} }
1230 : split(/\s*,\s*/, $opt{'not_report_optionnum'});
1232 $null = 1 if ( grep {$_ == 0} @num );
1233 @num = grep {$_ > 0} @num;
1234 @not_num = grep {$_ > 0} @not_num;
1237 my $table = $opt{'use_override'} ? 'override' : 'part_pkg';
1239 if ( $opt{'all_report_options'} ) {
1240 if ( @num and $null ) {
1241 return 'false'; # mutually exclusive criteria, so just bail out
1245 my @where_num = map {
1246 "EXISTS(SELECT 1 FROM part_pkg_option ".
1247 "WHERE optionname = 'report_option_$_' ".
1248 "AND part_pkg_option.pkgpart = $table.pkgpart)"
1251 push @where_num, "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
1252 "WHERE optionname LIKE 'report_option_%' ".
1253 "AND part_pkg_option.pkgpart = $table.pkgpart)";
1255 my @where_not_num = map {
1256 "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
1257 "WHERE optionname = 'report_option_$_' ".
1258 "AND part_pkg_option.pkgpart = $table.pkgpart)"
1263 push @where, '( '.join($op, @where_num).' )';
1265 if (@where_not_num) {
1266 push @where, '( '.join(' AND ', @where_not_num).' )';
1270 # this messes up totals
1271 #if ( $opt{'use_override'} ) {
1272 # # then also allow the non-override package to match
1273 # delete $opt{'use_override'};
1274 # $comparison = "( $comparison OR " . $self->with_report_option(%opt) . ")";
1280 my ($self, %opt) = @_;
1281 if ( $opt{'refnum'} ) {
1282 my $refnum = $opt{'refnum'};
1283 $refnum = [ $refnum ] if !ref($refnum);
1284 my $in = join(',', grep /^\d+$/, @$refnum);
1285 return "cust_main.refnum IN ($in)" if length $in;
1291 my ($self, %opt) = @_;
1292 if ( $opt{'towernum'} ) {
1293 my $towernum = $opt{'towernum'};
1294 $towernum = [ $towernum ] if !ref($towernum);
1295 my $in = join(',', grep /^\d+$/, @$towernum);
1296 return unless length($in); # if no towers are specified, don't restrict
1298 # materialize/cache the set of pkgnums that, as of the last
1299 # svc_broadband history record, had a certain towernum
1300 # (because otherwise this is painfully slow)
1301 $self->_init_tower_pkg_cache;
1304 SELECT 1 FROM tower_pkg_cache
1305 WHERE towernum IN($in)
1306 AND cust_pkg.pkgnum = tower_pkg_cache.pkgnum
1313 my ($self, %opt) = @_;
1314 if (length($opt{'zip'})) {
1315 return "(SELECT zip FROM cust_location
1316 WHERE cust_location.locationnum = cust_pkg.locationnum
1317 ) = " . dbh->quote($opt{'zip'});
1322 sub with_cust_classnum {
1323 my ($self, %opt) = @_;
1324 if ( $opt{'cust_classnum'} ) {
1325 my $classnums = $opt{'cust_classnum'};
1326 $classnums = [ $classnums ] if !ref($classnums);
1327 @$classnums = grep /^\d+$/, @$classnums;
1328 return 'cust_main.classnum in('. join(',',@$classnums) .')'
1336 my( $self, $sql ) = ( shift, shift );
1337 my $sth = dbh->prepare($sql) or die dbh->errstr;
1338 warn "FS::Report::Table\n$sql\n" if $DEBUG;
1340 or die "Unexpected error executing statement $sql: ". $sth->errstr;
1341 $sth->fetchrow_arrayref->[0] || 0;
1350 =item init_projection
1352 Sets up for future projection of all observables on the report. Currently
1353 this is limited to 'cust_bill_pkg'.
1357 sub init_projection {
1358 # this is weird special case stuff--some redesign may be needed
1359 # to use it for anything else
1362 if ( driver_name ne 'Pg' ) {
1363 # also database-specific for now
1364 die "projection reports not supported on this platform";
1367 my %items = map {$_ => 1} @{ $self->{items} };
1368 if ($items{'cust_bill_pkg'}) {
1372 # could use TEMPORARY TABLE but we're already transaction-protected
1373 'DROP TABLE IF EXISTS v_cust_bill_pkg',
1374 'CREATE TABLE v_cust_bill_pkg ' .
1375 '(LIKE cust_bill_pkg,
1376 usage numeric(10,2), _date integer, expire integer)',
1377 # XXX this should be smart enough to take only the ones with
1378 # sdate/edate overlapping the ROI, for performance
1379 "INSERT INTO v_cust_bill_pkg (
1380 SELECT cust_bill_pkg.*,
1381 (SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
1382 FROM cust_bill_pkg_detail
1383 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum),
1386 FROM cust_bill_pkg $cust_bill_pkg_join
1389 foreach my $sql (@sql) {
1390 warn "[init_projection] $sql\n" if $DEBUG;
1391 $dbh->do($sql) or die $dbh->errstr;
1396 =item extend_projection START END
1398 Generates data for the next period of projection. This will be called
1399 for sequential periods where the END of one equals the START of the next
1404 sub extend_projection {
1406 my ($speriod, $eperiod) = @_;
1407 my %items = map {$_ => 1} @{ $self->{items} };
1408 if ($items{'cust_bill_pkg'}) {
1410 # Find all line items that end after the start of the period (and have
1411 # recurring fees, and don't expire before they end). Choose the latest
1412 # one for each package. If it ends before the end of the period, copy
1413 # it forward by one billing period.
1414 # Repeat this until the latest line item for each package no longer ends
1415 # within the period. This is certain to happen in finitely many
1416 # iterations as long as freq > 0.
1417 # - Pg only, obviously.
1418 # - Gives bad results if freq_override is used.
1419 my @fields = ( FS::cust_bill_pkg->fields, qw( usage _date expire ) );
1420 my $insert_fields = join(',', @fields);
1421 my $add_freq = sub { # emulate FS::part_pkg::add_freq
1423 "EXTRACT( EPOCH FROM TO_TIMESTAMP($field) + (CASE WHEN freq ~ E'\\\\D' ".
1424 "THEN freq ELSE freq || 'mon' END)::INTERVAL) AS $field";
1427 if ($_ eq 'edate') {
1428 $_ = $add_freq->('edate');
1430 elsif ($_ eq 'sdate') {
1431 $_ = 'edate AS sdate'
1433 elsif ($_ eq 'setup') {
1434 $_ = '0 AS setup' #because recurring only
1436 elsif ($_ eq '_date') {
1437 $_ = $add_freq->('_date');
1440 my $select_fields = join(',', @fields);
1443 # Subquery here because we need to DISTINCT the whole set, select the
1444 # latest charge per pkgnum, and _then_ check edate < $eperiod
1445 # and edate < expire.
1446 "INSERT INTO v_cust_bill_pkg ($insert_fields)
1447 SELECT $select_fields FROM (
1448 SELECT DISTINCT ON (pkgnum) * FROM v_cust_bill_pkg
1449 WHERE edate >= $speriod
1451 AND freq IS NOT NULL
1453 ORDER BY pkgnum, edate DESC
1455 WHERE edate < $eperiod AND (edate < expire OR expire IS NULL)";
1458 warn "[extend_projection] $sql\n" if $DEBUG;
1459 $rows = $dbh->do($sql) or die $dbh->errstr;
1460 warn "[extend_projection] $rows rows\n" if $DEBUG;
1465 =item _init_tower_pkg_cache
1467 Internal method: creates a temporary table relating pkgnums to towernums.
1468 A (pkgnum, towernum) record indicates that this package once had a
1469 svc_broadband service which, as of its last insert or replace_new history
1470 record, had a sectornum associated with that towernum.
1472 This is expensive, so it won't be done more than once an hour. Historical
1473 data about package churn shouldn't be changing in realtime anyway.
1477 sub _init_tower_pkg_cache {
1481 my $current = $CACHE->get('tower_pkg_cache_update');
1484 # XXX or should this be in the schema?
1485 my $sql = "DROP TABLE IF EXISTS tower_pkg_cache";
1486 $dbh->do($sql) or die $dbh->errstr;
1487 $sql = "CREATE TABLE tower_pkg_cache (towernum int, pkgnum int)";
1488 $dbh->do($sql) or die $dbh->errstr;
1491 # sectornums never get reused, or move from one tower to another
1492 # all service history is intact
1493 # svcnums never get reused (this would be bad)
1494 # pkgnums NEVER get reused (this would be extremely bad)
1495 $sql = "INSERT INTO tower_pkg_cache (
1496 SELECT COALESCE(towernum,0), pkgnum
1497 FROM ( SELECT DISTINCT pkgnum, svcnum FROM h_cust_svc ) AS pkgnum_svcnum
1499 SELECT DISTINCT ON(svcnum) svcnum, sectornum
1500 FROM h_svc_broadband
1501 WHERE (history_action = 'replace_new'
1502 OR history_action = 'replace_old')
1503 ORDER BY svcnum ASC, history_date DESC
1504 ) AS svcnum_sectornum USING (svcnum)
1505 LEFT JOIN tower_sector USING (sectornum)
1507 $dbh->do($sql) or die $dbh->errstr;
1509 $CACHE->set('tower_pkg_cache_update', 1, 3600);
1519 L<FS::Report::Table::Monthly>, reports in the web interface.