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) = @_;
275 $self->scalar_sql('SELECT SUM(cust_bill_pkg_discount.amount)
276 FROM cust_bill_pkg_discount
277 JOIN cust_bill_pkg USING ( billpkgnum )
278 JOIN cust_bill USING ( invnum )
279 JOIN cust_main USING ( custnum )
280 WHERE '. $self->in_time_period_and_agent( $speriod,
285 $self->for_opts(%opt)
289 =item gross: invoiced + discounted
294 my( $self, $speriod, $eperiod, $agentnum, %opt) = @_;
295 $self->invoiced( $speriod, $eperiod, $agentnum, %opt)
296 + $self->discounted( $speriod, $eperiod, $agentnum, %opt);
301 #these should be auto-generated or $AUTOLOADed or something
303 my( $self, $speriod, $eperiod, $agentnum ) = @_;
304 $speriod = $self->_subtract_11mo($speriod);
305 $self->invoiced($speriod, $eperiod, $agentnum);
309 my( $self, $speriod, $eperiod, $agentnum ) = @_;
310 $speriod = $self->_subtract_11mo($speriod);
311 $self->netsales($speriod, $eperiod, $agentnum);
315 my( $self, $speriod, $eperiod, $agentnum ) = @_;
316 $speriod = $self->_subtract_11mo($speriod);
317 $self->receipts($speriod, $eperiod, $agentnum);
321 my( $self, $speriod, $eperiod, $agentnum ) = @_;
322 $speriod = $self->_subtract_11mo($speriod);
323 $self->payments($speriod, $eperiod, $agentnum);
327 my( $self, $speriod, $eperiod, $agentnum ) = @_;
328 $speriod = $self->_subtract_11mo($speriod);
329 $self->credits($speriod, $eperiod, $agentnum);
332 sub netcredits_12mo {
333 my( $self, $speriod, $eperiod, $agentnum ) = @_;
334 $speriod = $self->_subtract_11mo($speriod);
335 $self->netcredits($speriod, $eperiod, $agentnum);
339 my( $self, $speriod, $eperiod, $agentnum ) = @_;
340 $speriod = $self->_subtract_11mo($speriod);
341 $self->cashflow($speriod, $eperiod, $agentnum);
344 sub netcashflow_12mo {
345 my( $self, $speriod, $eperiod, $agentnum ) = @_;
346 $speriod = $self->_subtract_11mo($speriod);
347 $self->cashflow($speriod, $eperiod, $agentnum);
351 my( $self, $speriod, $eperiod, $agentnum ) = @_;
352 $speriod = $self->_subtract_11mo($speriod);
353 $self->refunds($speriod, $eperiod, $agentnum);
356 sub netrefunds_12mo {
357 my( $self, $speriod, $eperiod, $agentnum ) = @_;
358 $speriod = $self->_subtract_11mo($speriod);
359 $self->netrefunds($speriod, $eperiod, $agentnum);
363 #not being too bad with the false laziness
365 my($self, $time) = @_;
366 my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
368 if ( $mon < 0 ) { $mon+=12; $year--; }
369 timelocal($sec,$min,$hour,$mday,$mon,$year);
372 =item cust_pkg_setup_cost: The total setup costs of packages setup in the period
374 'classnum': limit to this package class.
378 sub cust_pkg_setup_cost {
379 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
382 if ( $opt{'classnum'} ne '' ) {
383 my $classnums = $opt{'classnum'};
384 $classnums = [ $classnums ] if !ref($classnums);
385 @$classnums = grep /^\d+$/, @$classnums;
386 $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
390 $agentnum ||= $opt{'agentnum'};
392 my $total_sql = " SELECT SUM(part_pkg.setup_cost) ";
393 $total_sql .= " FROM cust_pkg
394 LEFT JOIN cust_main USING ( custnum )
395 LEFT JOIN part_pkg USING ( pkgpart )
398 AND ".$self->in_time_period_and_agent(
399 $speriod, $eperiod, $agentnum, 'cust_pkg.setup');
400 return $self->scalar_sql($total_sql);
403 =item cust_pkg_recur_cust: the total recur costs of packages in the period
405 'classnum': limit to this package class.
409 sub cust_pkg_recur_cost {
410 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
413 if ( $opt{'classnum'} ne '' ) {
414 my $classnums = $opt{'classnum'};
415 $classnums = [ $classnums ] if !ref($classnums);
416 @$classnums = grep /^\d+$/, @$classnums;
417 $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
421 $agentnum ||= $opt{'agentnum'};
422 # duplication of in_time_period_and_agent
423 # because we do it a little differently here
424 $where .= " AND cust_main.agentnum = $agentnum" if $agentnum;
426 $FS::CurrentUser::CurrentUser->agentnums_sql('table' => 'cust_main');
428 my $total_sql = " SELECT SUM(part_pkg.recur_cost) ";
429 $total_sql .= " FROM cust_pkg
430 LEFT JOIN cust_main USING ( custnum )
431 LEFT JOIN part_pkg USING ( pkgpart )
434 AND cust_pkg.setup < $eperiod
435 AND (cust_pkg.cancel > $speriod OR cust_pkg.cancel IS NULL)
437 return $self->scalar_sql($total_sql);
440 =item cust_bill_pkg: the total package charges on invoice line items.
442 'charges': limit the type of charges included (setup, recur, usage).
443 Should be a string containing one or more of 'S', 'R', or 'U'; if
444 unspecified, defaults to all three.
446 'classnum': limit to this package class.
448 'use_override': for line items generated by an add-on package, use the class
449 of the add-on rather than the base package.
451 'average_per_cust_pkg': divide the result by the number of distinct packages.
453 'distribute': for non-monthly recurring charges, ignore the invoice
454 date. Instead, consider the line item's starting/ending dates. Determine
455 the fraction of the line item duration that falls within the specified
456 interval and return that fraction of the recurring charges. This is
457 somewhat experimental.
459 'project': enable if this is a projected period. This is very experimental.
465 my( $speriod, $eperiod, $agentnum, %opt ) = @_;
467 my %charges = map {$_=>1} split('', $opt{'charges'} || 'SRU');
470 $sum += $self->cust_bill_pkg_setup(@_) if $charges{S};
471 $sum += $self->cust_bill_pkg_recur(@_) if $charges{R};
472 $sum += $self->cust_bill_pkg_detail(@_) if $charges{U};
474 if ($opt{'average_per_cust_pkg'}) {
475 my $count = $self->cust_bill_pkg_count_pkgnum(@_);
476 return '' if $count == 0;
477 $sum = sprintf('%.2f', $sum / $count);
482 my $cust_bill_pkg_join = '
483 LEFT JOIN cust_bill USING ( invnum )
484 LEFT JOIN cust_main USING ( custnum )
485 LEFT JOIN cust_pkg USING ( pkgnum )
486 LEFT JOIN part_pkg USING ( pkgpart )
487 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
488 LEFT JOIN part_fee USING ( feepart )';
490 sub cust_bill_pkg_setup {
492 my ($speriod, $eperiod, $agentnum, %opt) = @_;
493 # no projecting setup fees--use real invoices only
494 # but evaluate this anyway, because the design of projection is that
495 # if there are somehow real setup fees in the future, we want to count
498 $agentnum ||= $opt{'agentnum'};
501 '(pkgnum != 0 OR feepart IS NOT NULL)',
502 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
503 $self->with_report_option(%opt),
504 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
505 $self->with_refnum(%opt),
506 $self->with_cust_classnum(%opt)
509 my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0)
512 WHERE " . join(' AND ', grep $_, @where);
514 $self->scalar_sql($total_sql);
517 sub _cust_bill_pkg_recurring {
518 # returns the FROM/WHERE part of the statement to query all recurring
519 # line items in the period
521 my ($speriod, $eperiod, $agentnum, %opt) = @_;
523 $agentnum ||= $opt{'agentnum'};
524 my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
527 '(pkgnum != 0 OR feepart IS NOT NULL)',
528 $self->with_report_option(%opt),
529 $self->with_refnum(%opt),
530 $self->with_cust_classnum(%opt)
533 my $where_classnum = $self->with_classnum($opt{'classnum'}, $opt{'use_override'});
534 if ($opt{'project'}) {
535 $where_classnum =~ s/\bcust_bill_pkg/v_cust_bill_pkg/g;
537 push @where, $where_classnum;
539 if ( $opt{'distribute'} ) {
540 $where[0] = 'pkgnum != 0'; # specifically exclude fees
541 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
543 "$cust_bill_pkg.sdate < $eperiod",
544 "$cust_bill_pkg.edate >= $speriod",
548 # we don't want to have to create v_cust_bill
549 my $_date = $opt{'project'} ? 'v_cust_bill_pkg._date' : 'cust_bill._date';
551 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date);
557 WHERE ".join(' AND ', grep $_, @where);
561 sub cust_bill_pkg_recur {
563 my ($speriod, $eperiod, $agentnum, %opt) = @_;
565 # subtract all usage from the line item regardless of date
567 if ( $opt{'project'} ) {
568 $item_usage = 'usage'; #already calculated
571 $item_usage = '( SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
572 FROM cust_bill_pkg_detail
573 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
576 my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
578 my $recur_fraction = '';
579 if ($opt{'distribute'}) {
580 # the fraction of edate - sdate that's within [speriod, eperiod]
581 $recur_fraction = " *
582 CAST(LEAST($eperiod, $cust_bill_pkg.edate) -
583 GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) /
584 ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
588 "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)" .
589 $self->_cust_bill_pkg_recurring(@_);
591 $self->scalar_sql($total_sql);
594 sub cust_bill_pkg_count_pkgnum {
595 # for ARPU calculation
597 my $total_sql = 'SELECT COUNT(DISTINCT pkgnum) '.
598 $self->_cust_bill_pkg_recurring(@_);
600 $self->scalar_sql($total_sql);
603 =item cust_bill_pkg_detail: the total usage charges in detail lines.
605 Arguments as for C<cust_bill_pkg>, plus:
607 'usageclass': limit to this usage class number.
611 sub cust_bill_pkg_detail {
612 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
615 ( "(cust_bill_pkg.pkgnum != 0 OR cust_bill_pkg.feepart IS NOT NULL)" );
617 $agentnum ||= $opt{'agentnum'};
620 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
621 $self->with_usageclass($opt{'usageclass'}),
622 $self->with_report_option(%opt),
623 $self->with_refnum(%opt),
624 $self->with_cust_classnum(%opt)
627 if ( $opt{'distribute'} ) {
629 $where[0] = 'cust_bill_pkg.pkgnum != 0';
630 # and limit according to the usage time, not the billing date
631 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
632 'cust_bill_pkg_detail.startdate'
636 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
641 my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) ";
644 " FROM cust_bill_pkg_detail
645 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
646 LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
647 LEFT JOIN cust_main USING ( custnum )
648 LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
649 LEFT JOIN part_pkg USING ( pkgpart )
650 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
651 LEFT JOIN part_fee USING ( feepart )
652 WHERE ".join( ' AND ', grep $_, @where );
654 $self->scalar_sql($total_sql);
658 sub cust_bill_pkg_discount {
659 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
661 #need to do this the new multi-classnum way if it gets re-enabled
663 #my $comparison = '';
664 #if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
666 # $comparison = "IS NULL";
668 # $comparison = "= $1";
671 # if ( $opt{'use_override'} ) {
673 # part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
674 # override.classnum $comparison AND pkgpart_override IS NOT NULL
677 # $where = "part_pkg.classnum $comparison";
681 $agentnum ||= $opt{'agentnum'};
684 " SELECT COALESCE( SUM( cust_bill_pkg_discount.amount ), 0 ) ";
687 " FROM cust_bill_pkg_discount
688 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
689 LEFT JOIN cust_bill USING ( invnum )
690 LEFT JOIN cust_main USING ( custnum )
691 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
692 # LEFT JOIN cust_pkg_discount USING ( pkgdiscountnum )
693 # LEFT JOIN discount USING ( discountnum )
694 # LEFT JOIN cust_pkg USING ( pkgnum )
695 # LEFT JOIN part_pkg USING ( pkgpart )
696 # LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
698 return $self->scalar_sql($total_sql);
702 ##### package churn report #####
704 =item active_pkg: The number of packages that were active at the start of
705 the period. The end date of the period is ignored. Options:
707 - refnum: Limit to customers with this advertising source.
708 - classnum: Limit to packages with this class.
709 - towernum: Limit to packages that have a broadband service with this tower.
710 - zip: Limit to packages with this service location zip code.
712 Except for zip, any of these can be an arrayref to allow multiple values for
715 =item setup_pkg: The number of packages with setup dates in the period. This
716 excludes packages created by package changes. Options are as for active_pkg.
718 =item susp_pkg: The number of packages that were suspended in the period
719 (and not canceled). Options are as for active_pkg.
721 =item unsusp_pkg: The number of packages that were unsuspended in the period.
722 Options are as for active_pkg.
724 =item cancel_pkg: The number of packages with cancel dates in the period.
725 Excludes packages that were canceled to be changed to a new package. Options
726 are as for active_pkg.
732 $self->churn_pkg('active', @_);
737 $self->churn_pkg('setup', @_);
742 $self->churn_pkg('cancel', @_);
747 $self->churn_pkg('susp', @_);
752 $self->churn_pkg('unsusp', @_);
757 my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_;
759 FS::h_cust_pkg->churn_fromwhere_sql( $status, $speriod, $eperiod);
761 push @where, $self->pkg_where(%opt, 'agentnum' => $agentnum);
763 my $sql = "SELECT COUNT(*) FROM $from
764 JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
765 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)";
766 $sql .= ' WHERE '.join(' AND ', @where)
769 $self->scalar_sql($sql);
776 "part_pkg.freq != '0'",
777 $self->with_refnum(%opt),
778 $self->with_towernum(%opt),
779 $self->with_zip(%opt),
781 if ($opt{agentnum} =~ /^(\d+)$/) {
782 push @where, "cust_main.agentnum = $1";
784 if ($opt{classnum}) {
785 my $classnum = $opt{classnum};
786 $classnum = [ $classnum ] if !ref($classnum);
787 @$classnum = grep /^\d+$/, @$classnum;
788 my $in = 'IN ('. join(',', @$classnum). ')';
789 push @where, "COALESCE(part_pkg.classnum, 0) $in" if scalar @$classnum;
794 ##### end of package churn report stuff #####
796 ##### customer churn report #####
798 =item active_cust: The number of customers who had any active recurring
799 packages at the start of the period. The end date is ignored, agentnum is
800 mandatory, and no other parameters are accepted.
802 =item started_cust: The number of customers who had no active packages at
803 the start of the period, but had active packages at the end. Like
804 active_cust, agentnum is mandatory and no other parameters are accepted.
806 =item suspended_cust: The number of customers who had active packages at
807 the start of the period, and at the end had no active packages but some
808 suspended packages. Note that this does not necessarily mean that their
809 packages were suspended during the period.
811 =item resumed_cust: The inverse of suspended_cust: the number of customers
812 who had suspended packages and no active packages at the start of the
813 period, and active packages at the end.
815 =item cancelled_cust: The number of customers who had active packages
816 at the start of the period, and only cancelled packages at the end.
822 $self->churn_cust(@_)->{active};
826 $self->churn_cust(@_)->{started};
830 $self->churn_cust(@_)->{suspended};
834 $self->churn_cust(@_)->{resumed};
838 $self->churn_cust(@_)->{cancelled};
843 my ( $speriod ) = @_;
845 # run one query for each interval
846 return $self->{_interval}{$speriod} ||= $self->calculate_churn_cust(@_);
849 sub calculate_churn_cust {
851 my ($speriod, $eperiod, $agentnum, %opt) = @_;
853 my $churn_sql = FS::cust_main::Status->churn_sql($speriod, $eperiod);
855 $where = " WHERE cust_main.agentnum = $agentnum " if $agentnum;
858 "FROM cust_main JOIN ($churn_sql) AS churn USING (custnum)".
861 # query to count the ones with certain status combinations
863 SELECT SUM((s_active > 0)::int) as active,
864 SUM((s_active = 0 and e_active > 0)::int) as started,
865 SUM((s_active > 0 and e_active = 0 and e_suspended > 0)::int)
867 SUM((s_active = 0 and s_suspended > 0 and e_active > 0)::int)
869 SUM((s_active > 0 and e_active = 0 and e_suspended = 0)::int)
871 FROM ($cust_sql) AS x
874 my $sth = dbh->prepare($total_sql);
875 $sth->execute or die "failed to execute churn query: " . $sth->errstr;
877 $self->{_interval}{$speriod} = $sth->fetchrow_hashref;
880 sub in_time_period_and_agent {
881 my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
882 my $col = @_ ? shift() : '_date';
884 my $sql = "$col >= $speriod AND $col < $eperiod";
887 $sql .= " AND cust_main.agentnum = $agentnum"
890 #agent virtualization
892 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table'=>'cust_main' );
898 my ( $self, %opt ) = @_;
900 if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
901 $sql .= " and custnum = $1 ";
903 if ( $opt{'refnum'} ) {
904 my $refnum = $opt{'refnum'};
905 $refnum = [ $refnum ] if !ref($refnum);
906 my $in = join(',', grep /^\d+$/, @$refnum);
907 $sql .= " and refnum IN ($in)" if length $in;
909 if ( my $where = $self->with_cust_classnum(%opt) ) {
910 $sql .= " and $where";
917 my ($self, $classnum, $use_override) = @_;
918 return '' if $classnum eq '';
920 $classnum = [ $classnum ] if !ref($classnum);
921 @$classnum = grep /^\d+$/, @$classnum;
922 my $in = 'IN ('. join(',', @$classnum). ')';
924 if ( $use_override ) {
925 # then include packages if their base package is in the set and they are
927 # or if they are overridden and their override package is in the set,
928 # or fees if they are in the set
930 ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL AND pkgpart_override IS NULL )
931 OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )
932 OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL )
935 # include packages if their base package is in the set,
936 # or fees if they are in the set
938 ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL )
939 OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL )
944 sub with_usageclass {
946 my ($classnum, $use_override) = @_;
947 return '' unless $classnum =~ /^\d+$/;
949 if ( $classnum == 0 ) {
950 $comparison = 'IS NULL';
953 $comparison = "= $classnum";
955 return "cust_bill_pkg_detail.classnum $comparison";
958 sub with_report_option {
959 my ($self, %opt) = @_;
961 # - report_optionnum: a comma-separated list of numbers. Zero means to
962 # include packages with _no_ report classes.
963 # - not_report_optionnum: a comma-separated list. Packages that have
964 # any of these report options will be excluded from the result.
966 # - use_override: also matches line items that are add-ons to a package
967 # matching the report class.
968 # - all_report_options: returns only packages that have ALL of the
969 # report classes listed in $num. Otherwise, will return packages that
970 # have ANY of those classes.
972 my @num = ref($opt{'report_optionnum'})
973 ? @{ $opt{'report_optionnum'} }
974 : split(/\s*,\s*/, $opt{'report_optionnum'});
975 my @not_num = ref($opt{'not_report_optionnum'})
976 ? @{ $opt{'not_report_optionnum'} }
977 : split(/\s*,\s*/, $opt{'not_report_optionnum'});
979 $null = 1 if ( grep {$_ == 0} @num );
980 @num = grep {$_ > 0} @num;
981 @not_num = grep {$_ > 0} @not_num;
984 my $table = $opt{'use_override'} ? 'override' : 'part_pkg';
986 if ( $opt{'all_report_options'} ) {
987 if ( @num and $null ) {
988 return 'false'; # mutually exclusive criteria, so just bail out
992 my @where_num = map {
993 "EXISTS(SELECT 1 FROM part_pkg_option ".
994 "WHERE optionname = 'report_option_$_' ".
995 "AND part_pkg_option.pkgpart = $table.pkgpart)"
998 push @where_num, "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
999 "WHERE optionname LIKE 'report_option_%' ".
1000 "AND part_pkg_option.pkgpart = $table.pkgpart)";
1002 my @where_not_num = map {
1003 "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
1004 "WHERE optionname = 'report_option_$_' ".
1005 "AND part_pkg_option.pkgpart = $table.pkgpart)"
1010 push @where, '( '.join($op, @where_num).' )';
1012 if (@where_not_num) {
1013 push @where, '( '.join(' AND ', @where_not_num).' )';
1017 # this messes up totals
1018 #if ( $opt{'use_override'} ) {
1019 # # then also allow the non-override package to match
1020 # delete $opt{'use_override'};
1021 # $comparison = "( $comparison OR " . $self->with_report_option(%opt) . ")";
1027 my ($self, %opt) = @_;
1028 if ( $opt{'refnum'} ) {
1029 my $refnum = $opt{'refnum'};
1030 $refnum = [ $refnum ] if !ref($refnum);
1031 my $in = join(',', grep /^\d+$/, @$refnum);
1032 return "cust_main.refnum IN ($in)" if length $in;
1038 my ($self, %opt) = @_;
1039 if ( $opt{'towernum'} ) {
1040 my $towernum = $opt{'towernum'};
1041 $towernum = [ $towernum ] if !ref($towernum);
1042 my $in = join(',', grep /^\d+$/, @$towernum);
1043 return unless length($in); # if no towers are specified, don't restrict
1045 # materialize/cache the set of pkgnums that, as of the last
1046 # svc_broadband history record, had a certain towernum
1047 # (because otherwise this is painfully slow)
1048 $self->_init_tower_pkg_cache;
1051 SELECT 1 FROM tower_pkg_cache
1052 WHERE towernum IN($in)
1053 AND cust_pkg.pkgnum = tower_pkg_cache.pkgnum
1060 my ($self, %opt) = @_;
1061 if (length($opt{'zip'})) {
1062 return "(SELECT zip FROM cust_location
1063 WHERE cust_location.locationnum = cust_pkg.locationnum
1064 ) = " . dbh->quote($opt{'zip'});
1069 sub with_cust_classnum {
1070 my ($self, %opt) = @_;
1071 if ( $opt{'cust_classnum'} ) {
1072 my $classnums = $opt{'cust_classnum'};
1073 $classnums = [ $classnums ] if !ref($classnums);
1074 @$classnums = grep /^\d+$/, @$classnums;
1075 return 'cust_main.classnum in('. join(',',@$classnums) .')'
1083 my( $self, $sql ) = ( shift, shift );
1084 my $sth = dbh->prepare($sql) or die dbh->errstr;
1085 warn "FS::Report::Table\n$sql\n" if $DEBUG;
1087 or die "Unexpected error executing statement $sql: ". $sth->errstr;
1088 $sth->fetchrow_arrayref->[0] || 0;
1097 =item init_projection
1099 Sets up for future projection of all observables on the report. Currently
1100 this is limited to 'cust_bill_pkg'.
1104 sub init_projection {
1105 # this is weird special case stuff--some redesign may be needed
1106 # to use it for anything else
1109 if ( driver_name ne 'Pg' ) {
1110 # also database-specific for now
1111 die "projection reports not supported on this platform";
1114 my %items = map {$_ => 1} @{ $self->{items} };
1115 if ($items{'cust_bill_pkg'}) {
1119 # could use TEMPORARY TABLE but we're already transaction-protected
1120 'DROP TABLE IF EXISTS v_cust_bill_pkg',
1121 'CREATE TABLE v_cust_bill_pkg ' .
1122 '(LIKE cust_bill_pkg,
1123 usage numeric(10,2), _date integer, expire integer)',
1124 # XXX this should be smart enough to take only the ones with
1125 # sdate/edate overlapping the ROI, for performance
1126 "INSERT INTO v_cust_bill_pkg (
1127 SELECT cust_bill_pkg.*,
1128 (SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
1129 FROM cust_bill_pkg_detail
1130 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum),
1133 FROM cust_bill_pkg $cust_bill_pkg_join
1136 foreach my $sql (@sql) {
1137 warn "[init_projection] $sql\n" if $DEBUG;
1138 $dbh->do($sql) or die $dbh->errstr;
1143 =item extend_projection START END
1145 Generates data for the next period of projection. This will be called
1146 for sequential periods where the END of one equals the START of the next
1151 sub extend_projection {
1153 my ($speriod, $eperiod) = @_;
1154 my %items = map {$_ => 1} @{ $self->{items} };
1155 if ($items{'cust_bill_pkg'}) {
1157 # Find all line items that end after the start of the period (and have
1158 # recurring fees, and don't expire before they end). Choose the latest
1159 # one for each package. If it ends before the end of the period, copy
1160 # it forward by one billing period.
1161 # Repeat this until the latest line item for each package no longer ends
1162 # within the period. This is certain to happen in finitely many
1163 # iterations as long as freq > 0.
1164 # - Pg only, obviously.
1165 # - Gives bad results if freq_override is used.
1166 my @fields = ( FS::cust_bill_pkg->fields, qw( usage _date expire ) );
1167 my $insert_fields = join(',', @fields);
1168 my $add_freq = sub { # emulate FS::part_pkg::add_freq
1170 "EXTRACT( EPOCH FROM TO_TIMESTAMP($field) + (CASE WHEN freq ~ E'\\\\D' ".
1171 "THEN freq ELSE freq || 'mon' END)::INTERVAL) AS $field";
1174 if ($_ eq 'edate') {
1175 $_ = $add_freq->('edate');
1177 elsif ($_ eq 'sdate') {
1178 $_ = 'edate AS sdate'
1180 elsif ($_ eq 'setup') {
1181 $_ = '0 AS setup' #because recurring only
1183 elsif ($_ eq '_date') {
1184 $_ = $add_freq->('_date');
1187 my $select_fields = join(',', @fields);
1190 # Subquery here because we need to DISTINCT the whole set, select the
1191 # latest charge per pkgnum, and _then_ check edate < $eperiod
1192 # and edate < expire.
1193 "INSERT INTO v_cust_bill_pkg ($insert_fields)
1194 SELECT $select_fields FROM (
1195 SELECT DISTINCT ON (pkgnum) * FROM v_cust_bill_pkg
1196 WHERE edate >= $speriod
1198 AND freq IS NOT NULL
1200 ORDER BY pkgnum, edate DESC
1202 WHERE edate < $eperiod AND (edate < expire OR expire IS NULL)";
1205 warn "[extend_projection] $sql\n" if $DEBUG;
1206 $rows = $dbh->do($sql) or die $dbh->errstr;
1207 warn "[extend_projection] $rows rows\n" if $DEBUG;
1212 =item _init_tower_pkg_cache
1214 Internal method: creates a temporary table relating pkgnums to towernums.
1215 A (pkgnum, towernum) record indicates that this package once had a
1216 svc_broadband service which, as of its last insert or replace_new history
1217 record, had a sectornum associated with that towernum.
1219 This is expensive, so it won't be done more than once an hour. Historical
1220 data about package churn shouldn't be changing in realtime anyway.
1224 sub _init_tower_pkg_cache {
1228 my $current = $CACHE->get('tower_pkg_cache_update');
1231 # XXX or should this be in the schema?
1232 my $sql = "DROP TABLE IF EXISTS tower_pkg_cache";
1233 $dbh->do($sql) or die $dbh->errstr;
1234 $sql = "CREATE TABLE tower_pkg_cache (towernum int, pkgnum int)";
1235 $dbh->do($sql) or die $dbh->errstr;
1238 # sectornums never get reused, or move from one tower to another
1239 # all service history is intact
1240 # svcnums never get reused (this would be bad)
1241 # pkgnums NEVER get reused (this would be extremely bad)
1242 $sql = "INSERT INTO tower_pkg_cache (
1243 SELECT COALESCE(towernum,0), pkgnum
1244 FROM ( SELECT DISTINCT pkgnum, svcnum FROM h_cust_svc ) AS pkgnum_svcnum
1246 SELECT DISTINCT ON(svcnum) svcnum, sectornum
1247 FROM h_svc_broadband
1248 WHERE (history_action = 'replace_new'
1249 OR history_action = 'replace_old')
1250 ORDER BY svcnum ASC, history_date DESC
1251 ) AS svcnum_sectornum USING (svcnum)
1252 LEFT JOIN tower_sector USING (sectornum)
1254 $dbh->do($sql) or die $dbh->errstr;
1256 $CACHE->set('tower_pkg_cache_update', 1, 3600);
1266 L<FS::Report::Table::Monthly>, reports in the web interface.