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 FS::cust_bill_pkg->paid_sql($speriod, $eperiod, %opt).
233 ') FROM cust_bill_pkg';
238 LEFT JOIN cust_bill USING ( invnum )
239 LEFT JOIN cust_main USING ( custnum )
240 WHERE ". $self->in_time_period_and_agent( $speriod,
245 $self->for_opts(%opt)
249 =item netrefunds: The sum of refund applications to credits in the period.
254 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
256 SELECT SUM(cust_credit_refund.amount)
257 FROM cust_credit_refund
258 LEFT JOIN cust_credit USING ( crednum )
259 LEFT JOIN cust_main USING ( custnum )
260 WHERE ". $self->in_time_period_and_agent( $speriod,
265 $self->for_opts(%opt)
269 =item discounted: The sum of discounts on invoices in the period.
274 my( $self, $speriod, $eperiod, $agentnum, %opt) = @_;
276 my $sql = 'SELECT SUM(';
277 if ($opt{'setuprecur'}) {
280 WHEN discount.setup = 'Y'
281 AND ((COALESCE(cust_bill_pkg.unitsetup,0) > 0)
282 OR (COALESCE(cust_bill_pkg.unitrecur,0) > 0))
285 if ($opt{'setuprecur'} eq 'setup') {
286 $sql .= ' (COALESCE(cust_bill_pkg.unitsetup,0)';
287 } elsif ($opt{'setuprecur'} eq 'recur') {
288 $sql .= ' (COALESCE(cust_bill_pkg.unitrecur,0)';
290 die 'Unrecognized value for setuprecur';
292 $sql .= ' / (COALESCE(cust_bill_pkg.unitsetup,0) + COALESCE(cust_bill_pkg.unitrecur,0)))';
293 $sql .= " * cust_bill_pkg_discount.amount\n";
295 ELSE cust_bill_pkg_discount.amount
299 $sql .= "cust_bill_pkg_discount.amount\n";
302 ) FROM cust_bill_pkg_discount
303 JOIN cust_bill_pkg USING ( billpkgnum )
304 JOIN cust_bill USING ( invnum )
305 JOIN cust_main USING ( custnum )
307 if ($opt{'setuprecur'}) {
309 JOIN cust_pkg_discount USING ( pkgdiscountnum )
310 LEFT JOIN discount USING ( discountnum )
316 . $self->in_time_period_and_agent( $speriod,
321 . $self->for_opts(%opt)
325 =item gross: invoiced + discounted
330 my( $self, $speriod, $eperiod, $agentnum, %opt) = @_;
331 $self->invoiced( $speriod, $eperiod, $agentnum, %opt)
332 + $self->discounted( $speriod, $eperiod, $agentnum, %opt);
337 #these should be auto-generated or $AUTOLOADed or something
339 my( $self, $speriod, $eperiod, $agentnum ) = @_;
340 $speriod = $self->_subtract_11mo($speriod);
341 $self->invoiced($speriod, $eperiod, $agentnum);
345 my( $self, $speriod, $eperiod, $agentnum ) = @_;
346 $speriod = $self->_subtract_11mo($speriod);
347 $self->netsales($speriod, $eperiod, $agentnum);
351 my( $self, $speriod, $eperiod, $agentnum ) = @_;
352 $speriod = $self->_subtract_11mo($speriod);
353 $self->receipts($speriod, $eperiod, $agentnum);
357 my( $self, $speriod, $eperiod, $agentnum ) = @_;
358 $speriod = $self->_subtract_11mo($speriod);
359 $self->payments($speriod, $eperiod, $agentnum);
363 my( $self, $speriod, $eperiod, $agentnum ) = @_;
364 $speriod = $self->_subtract_11mo($speriod);
365 $self->credits($speriod, $eperiod, $agentnum);
368 sub netcredits_12mo {
369 my( $self, $speriod, $eperiod, $agentnum ) = @_;
370 $speriod = $self->_subtract_11mo($speriod);
371 $self->netcredits($speriod, $eperiod, $agentnum);
375 my( $self, $speriod, $eperiod, $agentnum ) = @_;
376 $speriod = $self->_subtract_11mo($speriod);
377 $self->cashflow($speriod, $eperiod, $agentnum);
380 sub netcashflow_12mo {
381 my( $self, $speriod, $eperiod, $agentnum ) = @_;
382 $speriod = $self->_subtract_11mo($speriod);
383 $self->cashflow($speriod, $eperiod, $agentnum);
387 my( $self, $speriod, $eperiod, $agentnum ) = @_;
388 $speriod = $self->_subtract_11mo($speriod);
389 $self->refunds($speriod, $eperiod, $agentnum);
392 sub netrefunds_12mo {
393 my( $self, $speriod, $eperiod, $agentnum ) = @_;
394 $speriod = $self->_subtract_11mo($speriod);
395 $self->netrefunds($speriod, $eperiod, $agentnum);
399 #not being too bad with the false laziness
401 my($self, $time) = @_;
402 my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
404 if ( $mon < 0 ) { $mon+=12; $year--; }
405 timelocal($sec,$min,$hour,$mday,$mon,$year);
408 =item cust_pkg_setup_cost: The total setup costs of packages setup in the period
410 'classnum': limit to this package class.
414 sub cust_pkg_setup_cost {
415 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
418 if ( $opt{'classnum'} ne '' ) {
419 my $classnums = $opt{'classnum'};
420 $classnums = [ $classnums ] if !ref($classnums);
421 @$classnums = grep /^\d+$/, @$classnums;
422 $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
426 $agentnum ||= $opt{'agentnum'};
428 my $total_sql = " SELECT SUM(part_pkg.setup_cost) ";
429 $total_sql .= " FROM cust_pkg
430 LEFT JOIN cust_main USING ( custnum )
431 LEFT JOIN part_pkg USING ( pkgpart )
434 AND ".$self->in_time_period_and_agent(
435 $speriod, $eperiod, $agentnum, 'cust_pkg.setup');
436 return $self->scalar_sql($total_sql);
439 =item cust_pkg_recur_cust: the total recur costs of packages in the period
441 'classnum': limit to this package class.
445 sub cust_pkg_recur_cost {
446 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
449 if ( $opt{'classnum'} ne '' ) {
450 my $classnums = $opt{'classnum'};
451 $classnums = [ $classnums ] if !ref($classnums);
452 @$classnums = grep /^\d+$/, @$classnums;
453 $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
457 $agentnum ||= $opt{'agentnum'};
458 # duplication of in_time_period_and_agent
459 # because we do it a little differently here
460 $where .= " AND cust_main.agentnum = $agentnum" if $agentnum;
462 $FS::CurrentUser::CurrentUser->agentnums_sql('table' => 'cust_main');
464 my $total_sql = " SELECT SUM(part_pkg.recur_cost) ";
465 $total_sql .= " FROM cust_pkg
466 LEFT JOIN cust_main USING ( custnum )
467 LEFT JOIN part_pkg USING ( pkgpart )
470 AND cust_pkg.setup < $eperiod
471 AND (cust_pkg.cancel > $speriod OR cust_pkg.cancel IS NULL)
473 return $self->scalar_sql($total_sql);
476 =item cust_bill_pkg: the total package charges on invoice line items.
478 'charges': limit the type of charges included (setup, recur, usage, discount).
479 Should be a string containing one or more of 'S', 'R', 'U', or 'D'; if
480 unspecified, defaults to all three.
482 'classnum': limit to this package class.
484 'use_override': for line items generated by an add-on package, use the class
485 of the add-on rather than the base package.
487 'average_per_cust_pkg': divide the result by the number of distinct packages.
489 'distribute': for non-monthly recurring charges, ignore the invoice
490 date. Instead, consider the line item's starting/ending dates. Determine
491 the fraction of the line item duration that falls within the specified
492 interval and return that fraction of the recurring charges. This is
493 somewhat experimental.
495 'project': enable if this is a projected period. This is very experimental.
501 my( $speriod, $eperiod, $agentnum, %opt ) = @_;
503 my %charges = map {$_=>1} split('', $opt{'charges'} || 'SRU');
506 $sum += $self->cust_bill_pkg_setup(@_) if $charges{S};
507 $sum += $self->cust_bill_pkg_recur(@_) if $charges{R};
508 $sum += $self->cust_bill_pkg_detail(@_) if $charges{U};
509 $sum += $self->cust_bill_pkg_discount(@_) if $charges{D};
511 if ($opt{'average_per_cust_pkg'}) {
512 my $count = $self->cust_bill_pkg_count_pkgnum(@_);
513 return '' if $count == 0;
514 $sum = sprintf('%.2f', $sum / $count);
519 my $cust_bill_pkg_join = '
520 LEFT JOIN cust_bill USING ( invnum )
521 LEFT JOIN cust_main USING ( custnum )
522 LEFT JOIN cust_pkg USING ( pkgnum )
523 LEFT JOIN part_pkg USING ( pkgpart )
524 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
525 LEFT JOIN part_fee USING ( feepart )';
527 sub cust_bill_pkg_setup {
529 my ($speriod, $eperiod, $agentnum, %opt) = @_;
530 # no projecting setup fees--use real invoices only
531 # but evaluate this anyway, because the design of projection is that
532 # if there are somehow real setup fees in the future, we want to count
535 $agentnum ||= $opt{'agentnum'};
538 '(pkgnum != 0 OR feepart IS NOT NULL)',
539 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
540 $self->with_report_option(%opt),
541 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
542 $self->with_refnum(%opt),
543 $self->with_cust_classnum(%opt)
546 my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0)
549 WHERE " . join(' AND ', grep $_, @where);
551 $self->scalar_sql($total_sql);
554 sub _cust_bill_pkg_recurring {
555 # returns the FROM/WHERE part of the statement to query all recurring
556 # line items in the period
558 my ($speriod, $eperiod, $agentnum, %opt) = @_;
560 $agentnum ||= $opt{'agentnum'};
561 my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
564 '(pkgnum != 0 OR feepart IS NOT NULL)',
565 $self->with_report_option(%opt),
566 $self->with_refnum(%opt),
567 $self->with_cust_classnum(%opt)
570 my $where_classnum = $self->with_classnum($opt{'classnum'}, $opt{'use_override'});
571 if ($opt{'project'}) {
572 $where_classnum =~ s/\bcust_bill_pkg/v_cust_bill_pkg/g;
574 push @where, $where_classnum;
576 if ( $opt{'distribute'} ) {
577 $where[0] = 'pkgnum != 0'; # specifically exclude fees
578 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
580 "$cust_bill_pkg.sdate < $eperiod",
581 "$cust_bill_pkg.edate >= $speriod",
585 # we don't want to have to create v_cust_bill
586 my $_date = $opt{'project'} ? 'v_cust_bill_pkg._date' : 'cust_bill._date';
588 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date);
594 WHERE ".join(' AND ', grep $_, @where);
598 sub cust_bill_pkg_recur {
600 my ($speriod, $eperiod, $agentnum, %opt) = @_;
602 # subtract all usage from the line item regardless of date
604 if ( $opt{'project'} ) {
605 $item_usage = 'usage'; #already calculated
608 $item_usage = '( SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
609 FROM cust_bill_pkg_detail
610 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
613 my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
615 my $recur_fraction = '';
616 if ($opt{'distribute'}) {
617 # the fraction of edate - sdate that's within [speriod, eperiod]
618 $recur_fraction = " *
619 CAST(LEAST($eperiod, $cust_bill_pkg.edate) -
620 GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) /
621 ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
625 "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)" .
626 $self->_cust_bill_pkg_recurring(@_);
628 $self->scalar_sql($total_sql);
631 sub cust_bill_pkg_count_pkgnum {
632 # for ARPU calculation
634 my $total_sql = 'SELECT COUNT(DISTINCT pkgnum) '.
635 $self->_cust_bill_pkg_recurring(@_);
637 $self->scalar_sql($total_sql);
640 =item cust_bill_pkg_detail: the total usage charges in detail lines.
642 Arguments as for C<cust_bill_pkg>, plus:
644 'usageclass': limit to this usage class number.
648 sub cust_bill_pkg_detail {
649 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
652 ( "(cust_bill_pkg.pkgnum != 0 OR cust_bill_pkg.feepart IS NOT NULL)" );
654 $agentnum ||= $opt{'agentnum'};
657 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
658 $self->with_usageclass($opt{'usageclass'}),
659 $self->with_report_option(%opt),
660 $self->with_refnum(%opt),
661 $self->with_cust_classnum(%opt)
664 if ( $opt{'distribute'} ) {
666 $where[0] = 'cust_bill_pkg.pkgnum != 0';
667 # and limit according to the usage time, not the billing date
668 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
669 'cust_bill_pkg_detail.startdate'
673 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
678 my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) ";
681 " FROM cust_bill_pkg_detail
682 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
683 LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
684 LEFT JOIN cust_main USING ( custnum )
685 LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
686 LEFT JOIN part_pkg USING ( pkgpart )
687 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
688 LEFT JOIN part_fee USING ( feepart )
689 WHERE ".join( ' AND ', grep $_, @where );
691 $self->scalar_sql($total_sql);
695 sub cust_bill_pkg_discount {
697 my ($speriod, $eperiod, $agentnum, %opt) = @_;
698 # apply all the same constraints here as for setup/recur
700 $agentnum ||= $opt{'agentnum'};
703 '(pkgnum != 0 OR feepart IS NOT NULL)',
704 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
705 $self->with_report_option(%opt),
706 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
707 $self->with_refnum(%opt),
708 $self->with_cust_classnum(%opt)
711 my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg_discount.amount), 0)
712 FROM cust_bill_pkg_discount
713 JOIN cust_bill_pkg USING (billpkgnum)
715 WHERE " . join(' AND ', grep $_, @where);
717 $self->scalar_sql($total_sql);
720 ##### package churn report #####
722 =item active_pkg: The number of packages that were active at the start of
723 the period. The end date of the period is ignored. Options:
725 - refnum: Limit to customers with this advertising source.
726 - classnum: Limit to packages with this class.
727 - towernum: Limit to packages that have a broadband service with this tower.
728 - zip: Limit to packages with this service location zip code.
730 Except for zip, any of these can be an arrayref to allow multiple values for
733 =item setup_pkg: The number of packages with setup dates in the period. This
734 excludes packages created by package changes. Options are as for active_pkg.
736 =item susp_pkg: The number of packages that were suspended in the period
737 (and not canceled). Options are as for active_pkg.
739 =item unsusp_pkg: The number of packages that were unsuspended in the period.
740 Options are as for active_pkg.
742 =item cancel_pkg: The number of packages with cancel dates in the period.
743 Excludes packages that were canceled to be changed to a new package. Options
744 are as for active_pkg.
750 $self->churn_pkg('active', @_);
755 $self->churn_pkg('setup', @_);
760 $self->churn_pkg('cancel', @_);
765 $self->churn_pkg('susp', @_);
770 $self->churn_pkg('unsusp', @_);
775 my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_;
777 FS::h_cust_pkg->churn_fromwhere_sql( $status, $speriod, $eperiod);
779 push @where, $self->pkg_where(%opt, 'agentnum' => $agentnum);
781 my $sql = "SELECT COUNT(*) FROM $from
782 JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
783 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)";
784 $sql .= ' WHERE '.join(' AND ', @where)
787 $self->scalar_sql($sql);
794 "part_pkg.freq != '0'",
795 $self->with_refnum(%opt),
796 $self->with_towernum(%opt),
797 $self->with_zip(%opt),
799 if ($opt{agentnum} =~ /^(\d+)$/) {
800 push @where, "cust_main.agentnum = $1";
802 if ($opt{classnum}) {
803 my $classnum = $opt{classnum};
804 $classnum = [ $classnum ] if !ref($classnum);
805 @$classnum = grep /^\d+$/, @$classnum;
806 my $in = 'IN ('. join(',', @$classnum). ')';
807 push @where, "COALESCE(part_pkg.classnum, 0) $in" if scalar @$classnum;
812 ##### end of package churn report stuff #####
814 ##### customer churn report #####
816 =item active_cust: The number of customers who had any active recurring
817 packages at the start of the period. The end date is ignored, agentnum is
818 mandatory, and no other parameters are accepted.
820 =item started_cust: The number of customers who had no active packages at
821 the start of the period, but had active packages at the end. Like
822 active_cust, agentnum is mandatory and no other parameters are accepted.
824 =item suspended_cust: The number of customers who had active packages at
825 the start of the period, and at the end had no active packages but some
826 suspended packages. Note that this does not necessarily mean that their
827 packages were suspended during the period.
829 =item resumed_cust: The inverse of suspended_cust: the number of customers
830 who had suspended packages and no active packages at the start of the
831 period, and active packages at the end.
833 =item cancelled_cust: The number of customers who had active packages
834 at the start of the period, and only cancelled packages at the end.
840 $self->churn_cust(@_)->{active};
844 $self->churn_cust(@_)->{started};
848 $self->churn_cust(@_)->{suspended};
852 $self->churn_cust(@_)->{resumed};
856 $self->churn_cust(@_)->{cancelled};
861 my ( $speriod ) = @_;
863 # run one query for each interval
864 return $self->{_interval}{$speriod} ||= $self->calculate_churn_cust(@_);
867 sub calculate_churn_cust {
869 my ($speriod, $eperiod, $agentnum, %opt) = @_;
871 my $churn_sql = FS::cust_main::Status->churn_sql($speriod, $eperiod);
873 $where = " WHERE cust_main.agentnum = $agentnum " if $agentnum;
876 "FROM cust_main JOIN ($churn_sql) AS churn USING (custnum)".
879 # query to count the ones with certain status combinations
881 SELECT SUM((s_active > 0)::int) as active,
882 SUM((s_active = 0 and e_active > 0)::int) as started,
883 SUM((s_active > 0 and e_active = 0 and e_suspended > 0)::int)
885 SUM((s_active = 0 and s_suspended > 0 and e_active > 0)::int)
887 SUM((s_active > 0 and e_active = 0 and e_suspended = 0)::int)
889 FROM ($cust_sql) AS x
892 my $sth = dbh->prepare($total_sql);
893 $sth->execute or die "failed to execute churn query: " . $sth->errstr;
895 $self->{_interval}{$speriod} = $sth->fetchrow_hashref;
898 sub in_time_period_and_agent {
899 my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
900 my $col = @_ ? shift() : '_date';
902 my $sql = "$col >= $speriod AND $col < $eperiod";
905 $sql .= " AND cust_main.agentnum = $agentnum"
908 #agent virtualization
910 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table'=>'cust_main' );
916 my ( $self, %opt ) = @_;
918 if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
919 $sql .= " and custnum = $1 ";
921 if ( $opt{'refnum'} ) {
922 my $refnum = $opt{'refnum'};
923 $refnum = [ $refnum ] if !ref($refnum);
924 my $in = join(',', grep /^\d+$/, @$refnum);
925 $sql .= " and refnum IN ($in)" if length $in;
927 if ( my $where = $self->with_cust_classnum(%opt) ) {
928 $sql .= " and $where";
935 my ($self, $classnum, $use_override) = @_;
936 return '' if $classnum eq '';
938 $classnum = [ $classnum ] if !ref($classnum);
939 @$classnum = grep /^\d+$/, @$classnum;
940 return '' if !@$classnum;
941 my $in = 'IN ('. join(',', @$classnum). ')';
943 if ( $use_override ) {
944 # then include packages if their base package is in the set and they are
946 # or if they are overridden and their override package is in the set,
947 # or fees if they are in the set
949 ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL AND pkgpart_override IS NULL )
950 OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )
951 OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL )
954 # include packages if their base package is in the set,
955 # or fees if they are in the set
957 ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL )
958 OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL )
963 sub with_usageclass {
965 my ($classnum, $use_override) = @_;
966 return '' unless $classnum =~ /^\d+$/;
968 if ( $classnum == 0 ) {
969 $comparison = 'IS NULL';
972 $comparison = "= $classnum";
974 return "cust_bill_pkg_detail.classnum $comparison";
977 sub with_report_option {
978 my ($self, %opt) = @_;
980 # - report_optionnum: a comma-separated list of numbers. Zero means to
981 # include packages with _no_ report classes.
982 # - not_report_optionnum: a comma-separated list. Packages that have
983 # any of these report options will be excluded from the result.
985 # - use_override: also matches line items that are add-ons to a package
986 # matching the report class.
987 # - all_report_options: returns only packages that have ALL of the
988 # report classes listed in $num. Otherwise, will return packages that
989 # have ANY of those classes.
991 my @num = ref($opt{'report_optionnum'})
992 ? @{ $opt{'report_optionnum'} }
993 : split(/\s*,\s*/, $opt{'report_optionnum'});
994 my @not_num = ref($opt{'not_report_optionnum'})
995 ? @{ $opt{'not_report_optionnum'} }
996 : split(/\s*,\s*/, $opt{'not_report_optionnum'});
998 $null = 1 if ( grep {$_ == 0} @num );
999 @num = grep {$_ > 0} @num;
1000 @not_num = grep {$_ > 0} @not_num;
1003 my $table = $opt{'use_override'} ? 'override' : 'part_pkg';
1005 if ( $opt{'all_report_options'} ) {
1006 if ( @num and $null ) {
1007 return 'false'; # mutually exclusive criteria, so just bail out
1011 my @where_num = map {
1012 "EXISTS(SELECT 1 FROM part_pkg_option ".
1013 "WHERE optionname = 'report_option_$_' ".
1014 "AND part_pkg_option.pkgpart = $table.pkgpart)"
1017 push @where_num, "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
1018 "WHERE optionname LIKE 'report_option_%' ".
1019 "AND part_pkg_option.pkgpart = $table.pkgpart)";
1021 my @where_not_num = map {
1022 "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
1023 "WHERE optionname = 'report_option_$_' ".
1024 "AND part_pkg_option.pkgpart = $table.pkgpart)"
1029 push @where, '( '.join($op, @where_num).' )';
1031 if (@where_not_num) {
1032 push @where, '( '.join(' AND ', @where_not_num).' )';
1036 # this messes up totals
1037 #if ( $opt{'use_override'} ) {
1038 # # then also allow the non-override package to match
1039 # delete $opt{'use_override'};
1040 # $comparison = "( $comparison OR " . $self->with_report_option(%opt) . ")";
1046 my ($self, %opt) = @_;
1047 if ( $opt{'refnum'} ) {
1048 my $refnum = $opt{'refnum'};
1049 $refnum = [ $refnum ] if !ref($refnum);
1050 my $in = join(',', grep /^\d+$/, @$refnum);
1051 return "cust_main.refnum IN ($in)" if length $in;
1057 my ($self, %opt) = @_;
1058 if ( $opt{'towernum'} ) {
1059 my $towernum = $opt{'towernum'};
1060 $towernum = [ $towernum ] if !ref($towernum);
1061 my $in = join(',', grep /^\d+$/, @$towernum);
1062 return unless length($in); # if no towers are specified, don't restrict
1064 # materialize/cache the set of pkgnums that, as of the last
1065 # svc_broadband history record, had a certain towernum
1066 # (because otherwise this is painfully slow)
1067 $self->_init_tower_pkg_cache;
1070 SELECT 1 FROM tower_pkg_cache
1071 WHERE towernum IN($in)
1072 AND cust_pkg.pkgnum = tower_pkg_cache.pkgnum
1079 my ($self, %opt) = @_;
1080 if (length($opt{'zip'})) {
1081 return "(SELECT zip FROM cust_location
1082 WHERE cust_location.locationnum = cust_pkg.locationnum
1083 ) = " . dbh->quote($opt{'zip'});
1088 sub with_cust_classnum {
1089 my ($self, %opt) = @_;
1090 if ( $opt{'cust_classnum'} ) {
1091 my $classnums = $opt{'cust_classnum'};
1092 $classnums = [ $classnums ] if !ref($classnums);
1093 @$classnums = grep /^\d+$/, @$classnums;
1094 return 'cust_main.classnum in('. join(',',@$classnums) .')'
1102 my( $self, $sql ) = ( shift, shift );
1103 my $sth = dbh->prepare($sql) or die dbh->errstr;
1104 warn "FS::Report::Table\n$sql\n" if $DEBUG;
1106 or die "Unexpected error executing statement $sql: ". $sth->errstr;
1107 $sth->fetchrow_arrayref->[0] || 0;
1116 =item init_projection
1118 Sets up for future projection of all observables on the report. Currently
1119 this is limited to 'cust_bill_pkg'.
1123 sub init_projection {
1124 # this is weird special case stuff--some redesign may be needed
1125 # to use it for anything else
1128 if ( driver_name ne 'Pg' ) {
1129 # also database-specific for now
1130 die "projection reports not supported on this platform";
1133 my %items = map {$_ => 1} @{ $self->{items} };
1134 if ($items{'cust_bill_pkg'}) {
1138 # could use TEMPORARY TABLE but we're already transaction-protected
1139 'DROP TABLE IF EXISTS v_cust_bill_pkg',
1140 'CREATE TABLE v_cust_bill_pkg ' .
1141 '(LIKE cust_bill_pkg,
1142 usage numeric(10,2), _date integer, expire integer)',
1143 # XXX this should be smart enough to take only the ones with
1144 # sdate/edate overlapping the ROI, for performance
1145 "INSERT INTO v_cust_bill_pkg (
1146 SELECT cust_bill_pkg.*,
1147 (SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
1148 FROM cust_bill_pkg_detail
1149 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum),
1152 FROM cust_bill_pkg $cust_bill_pkg_join
1155 foreach my $sql (@sql) {
1156 warn "[init_projection] $sql\n" if $DEBUG;
1157 $dbh->do($sql) or die $dbh->errstr;
1162 =item extend_projection START END
1164 Generates data for the next period of projection. This will be called
1165 for sequential periods where the END of one equals the START of the next
1170 sub extend_projection {
1172 my ($speriod, $eperiod) = @_;
1173 my %items = map {$_ => 1} @{ $self->{items} };
1174 if ($items{'cust_bill_pkg'}) {
1176 # Find all line items that end after the start of the period (and have
1177 # recurring fees, and don't expire before they end). Choose the latest
1178 # one for each package. If it ends before the end of the period, copy
1179 # it forward by one billing period.
1180 # Repeat this until the latest line item for each package no longer ends
1181 # within the period. This is certain to happen in finitely many
1182 # iterations as long as freq > 0.
1183 # - Pg only, obviously.
1184 # - Gives bad results if freq_override is used.
1185 my @fields = ( FS::cust_bill_pkg->fields, qw( usage _date expire ) );
1186 my $insert_fields = join(',', @fields);
1187 my $add_freq = sub { # emulate FS::part_pkg::add_freq
1189 "EXTRACT( EPOCH FROM TO_TIMESTAMP($field) + (CASE WHEN freq ~ E'\\\\D' ".
1190 "THEN freq ELSE freq || 'mon' END)::INTERVAL) AS $field";
1193 if ($_ eq 'edate') {
1194 $_ = $add_freq->('edate');
1196 elsif ($_ eq 'sdate') {
1197 $_ = 'edate AS sdate'
1199 elsif ($_ eq 'setup') {
1200 $_ = '0 AS setup' #because recurring only
1202 elsif ($_ eq '_date') {
1203 $_ = $add_freq->('_date');
1206 my $select_fields = join(',', @fields);
1209 # Subquery here because we need to DISTINCT the whole set, select the
1210 # latest charge per pkgnum, and _then_ check edate < $eperiod
1211 # and edate < expire.
1212 "INSERT INTO v_cust_bill_pkg ($insert_fields)
1213 SELECT $select_fields FROM (
1214 SELECT DISTINCT ON (pkgnum) * FROM v_cust_bill_pkg
1215 WHERE edate >= $speriod
1217 AND freq IS NOT NULL
1219 ORDER BY pkgnum, edate DESC
1221 WHERE edate < $eperiod AND (edate < expire OR expire IS NULL)";
1224 warn "[extend_projection] $sql\n" if $DEBUG;
1225 $rows = $dbh->do($sql) or die $dbh->errstr;
1226 warn "[extend_projection] $rows rows\n" if $DEBUG;
1231 =item _init_tower_pkg_cache
1233 Internal method: creates a temporary table relating pkgnums to towernums.
1234 A (pkgnum, towernum) record indicates that this package once had a
1235 svc_broadband service which, as of its last insert or replace_new history
1236 record, had a sectornum associated with that towernum.
1238 This is expensive, so it won't be done more than once an hour. Historical
1239 data about package churn shouldn't be changing in realtime anyway.
1243 sub _init_tower_pkg_cache {
1247 my $current = $CACHE->get('tower_pkg_cache_update');
1250 # XXX or should this be in the schema?
1251 my $sql = "DROP TABLE IF EXISTS tower_pkg_cache";
1252 $dbh->do($sql) or die $dbh->errstr;
1253 $sql = "CREATE TABLE tower_pkg_cache (towernum int, pkgnum int)";
1254 $dbh->do($sql) or die $dbh->errstr;
1257 # sectornums never get reused, or move from one tower to another
1258 # all service history is intact
1259 # svcnums never get reused (this would be bad)
1260 # pkgnums NEVER get reused (this would be extremely bad)
1261 $sql = "INSERT INTO tower_pkg_cache (
1262 SELECT COALESCE(towernum,0), pkgnum
1263 FROM ( SELECT DISTINCT pkgnum, svcnum FROM h_cust_svc ) AS pkgnum_svcnum
1265 SELECT DISTINCT ON(svcnum) svcnum, sectornum
1266 FROM h_svc_broadband
1267 WHERE (history_action = 'replace_new'
1268 OR history_action = 'replace_old')
1269 ORDER BY svcnum ASC, history_date DESC
1270 ) AS svcnum_sectornum USING (svcnum)
1271 LEFT JOIN tower_sector USING (sectornum)
1273 $dbh->do($sql) or die $dbh->errstr;
1275 $CACHE->set('tower_pkg_cache_update', 1, 3600);
1285 L<FS::Report::Table::Monthly>, reports in the web interface.