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)
271 #these should be auto-generated or $AUTOLOADed or something
273 my( $self, $speriod, $eperiod, $agentnum ) = @_;
274 $speriod = $self->_subtract_11mo($speriod);
275 $self->invoiced($speriod, $eperiod, $agentnum);
279 my( $self, $speriod, $eperiod, $agentnum ) = @_;
280 $speriod = $self->_subtract_11mo($speriod);
281 $self->netsales($speriod, $eperiod, $agentnum);
285 my( $self, $speriod, $eperiod, $agentnum ) = @_;
286 $speriod = $self->_subtract_11mo($speriod);
287 $self->receipts($speriod, $eperiod, $agentnum);
291 my( $self, $speriod, $eperiod, $agentnum ) = @_;
292 $speriod = $self->_subtract_11mo($speriod);
293 $self->payments($speriod, $eperiod, $agentnum);
297 my( $self, $speriod, $eperiod, $agentnum ) = @_;
298 $speriod = $self->_subtract_11mo($speriod);
299 $self->credits($speriod, $eperiod, $agentnum);
302 sub netcredits_12mo {
303 my( $self, $speriod, $eperiod, $agentnum ) = @_;
304 $speriod = $self->_subtract_11mo($speriod);
305 $self->netcredits($speriod, $eperiod, $agentnum);
309 my( $self, $speriod, $eperiod, $agentnum ) = @_;
310 $speriod = $self->_subtract_11mo($speriod);
311 $self->cashflow($speriod, $eperiod, $agentnum);
314 sub netcashflow_12mo {
315 my( $self, $speriod, $eperiod, $agentnum ) = @_;
316 $speriod = $self->_subtract_11mo($speriod);
317 $self->cashflow($speriod, $eperiod, $agentnum);
321 my( $self, $speriod, $eperiod, $agentnum ) = @_;
322 $speriod = $self->_subtract_11mo($speriod);
323 $self->refunds($speriod, $eperiod, $agentnum);
326 sub netrefunds_12mo {
327 my( $self, $speriod, $eperiod, $agentnum ) = @_;
328 $speriod = $self->_subtract_11mo($speriod);
329 $self->netrefunds($speriod, $eperiod, $agentnum);
333 #not being too bad with the false laziness
335 my($self, $time) = @_;
336 my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
338 if ( $mon < 0 ) { $mon+=12; $year--; }
339 timelocal($sec,$min,$hour,$mday,$mon,$year);
342 =item cust_pkg_setup_cost: The total setup costs of packages setup in the period
344 'classnum': limit to this package class.
348 sub cust_pkg_setup_cost {
349 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
352 if ( $opt{'classnum'} ne '' ) {
353 my $classnums = $opt{'classnum'};
354 $classnums = [ $classnums ] if !ref($classnums);
355 @$classnums = grep /^\d+$/, @$classnums;
356 $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
360 $agentnum ||= $opt{'agentnum'};
362 my $total_sql = " SELECT SUM(part_pkg.setup_cost) ";
363 $total_sql .= " FROM cust_pkg
364 LEFT JOIN cust_main USING ( custnum )
365 LEFT JOIN part_pkg USING ( pkgpart )
368 AND ".$self->in_time_period_and_agent(
369 $speriod, $eperiod, $agentnum, 'cust_pkg.setup');
370 return $self->scalar_sql($total_sql);
373 =item cust_pkg_recur_cust: the total recur costs of packages in the period
375 'classnum': limit to this package class.
379 sub cust_pkg_recur_cost {
380 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
383 if ( $opt{'classnum'} ne '' ) {
384 my $classnums = $opt{'classnum'};
385 $classnums = [ $classnums ] if !ref($classnums);
386 @$classnums = grep /^\d+$/, @$classnums;
387 $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
391 $agentnum ||= $opt{'agentnum'};
392 # duplication of in_time_period_and_agent
393 # because we do it a little differently here
394 $where .= " AND cust_main.agentnum = $agentnum" if $agentnum;
396 $FS::CurrentUser::CurrentUser->agentnums_sql('table' => 'cust_main');
398 my $total_sql = " SELECT SUM(part_pkg.recur_cost) ";
399 $total_sql .= " FROM cust_pkg
400 LEFT JOIN cust_main USING ( custnum )
401 LEFT JOIN part_pkg USING ( pkgpart )
404 AND cust_pkg.setup < $eperiod
405 AND (cust_pkg.cancel > $speriod OR cust_pkg.cancel IS NULL)
407 return $self->scalar_sql($total_sql);
410 =item cust_bill_pkg: the total package charges on invoice line items.
412 'charges': limit the type of charges included (setup, recur, usage).
413 Should be a string containing one or more of 'S', 'R', or 'U'; if
414 unspecified, defaults to all three.
416 'classnum': limit to this package class.
418 'use_override': for line items generated by an add-on package, use the class
419 of the add-on rather than the base package.
421 'average_per_cust_pkg': divide the result by the number of distinct packages.
423 'distribute': for non-monthly recurring charges, ignore the invoice
424 date. Instead, consider the line item's starting/ending dates. Determine
425 the fraction of the line item duration that falls within the specified
426 interval and return that fraction of the recurring charges. This is
427 somewhat experimental.
429 'project': enable if this is a projected period. This is very experimental.
435 my( $speriod, $eperiod, $agentnum, %opt ) = @_;
437 my %charges = map {$_=>1} split('', $opt{'charges'} || 'SRU');
440 $sum += $self->cust_bill_pkg_setup(@_) if $charges{S};
441 $sum += $self->cust_bill_pkg_recur(@_) if $charges{R};
442 $sum += $self->cust_bill_pkg_detail(@_) if $charges{U};
444 if ($opt{'average_per_cust_pkg'}) {
445 my $count = $self->cust_bill_pkg_count_pkgnum(@_);
446 return '' if $count == 0;
447 $sum = sprintf('%.2f', $sum / $count);
452 my $cust_bill_pkg_join = '
453 LEFT JOIN cust_bill USING ( invnum )
454 LEFT JOIN cust_main USING ( custnum )
455 LEFT JOIN cust_pkg USING ( pkgnum )
456 LEFT JOIN part_pkg USING ( pkgpart )
457 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
458 LEFT JOIN part_fee USING ( feepart )';
460 sub cust_bill_pkg_setup {
462 my ($speriod, $eperiod, $agentnum, %opt) = @_;
463 # no projecting setup fees--use real invoices only
464 # but evaluate this anyway, because the design of projection is that
465 # if there are somehow real setup fees in the future, we want to count
468 $agentnum ||= $opt{'agentnum'};
471 '(pkgnum != 0 OR feepart IS NOT NULL)',
472 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
473 $self->with_report_option(%opt),
474 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
475 $self->with_refnum(%opt),
476 $self->with_cust_classnum(%opt)
479 my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0)
482 WHERE " . join(' AND ', grep $_, @where);
484 $self->scalar_sql($total_sql);
487 sub _cust_bill_pkg_recurring {
488 # returns the FROM/WHERE part of the statement to query all recurring
489 # line items in the period
491 my ($speriod, $eperiod, $agentnum, %opt) = @_;
493 $agentnum ||= $opt{'agentnum'};
494 my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
497 '(pkgnum != 0 OR feepart IS NOT NULL)',
498 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
499 $self->with_report_option(%opt),
500 $self->with_refnum(%opt),
501 $self->with_cust_classnum(%opt)
504 if ( $opt{'distribute'} ) {
505 $where[0] = 'pkgnum != 0'; # specifically exclude fees
506 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
508 "$cust_bill_pkg.sdate < $eperiod",
509 "$cust_bill_pkg.edate >= $speriod",
513 # we don't want to have to create v_cust_bill
514 my $_date = $opt{'project'} ? 'v_cust_bill_pkg._date' : 'cust_bill._date';
516 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date);
522 WHERE ".join(' AND ', grep $_, @where);
526 sub cust_bill_pkg_recur {
528 my ($speriod, $eperiod, $agentnum, %opt) = @_;
530 # subtract all usage from the line item regardless of date
532 if ( $opt{'project'} ) {
533 $item_usage = 'usage'; #already calculated
536 $item_usage = '( SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
537 FROM cust_bill_pkg_detail
538 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
541 my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
543 my $recur_fraction = '';
544 if ($opt{'distribute'}) {
545 # the fraction of edate - sdate that's within [speriod, eperiod]
546 $recur_fraction = " *
547 CAST(LEAST($eperiod, $cust_bill_pkg.edate) -
548 GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) /
549 ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
553 "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)" .
554 $self->_cust_bill_pkg_recurring(@_);
556 $self->scalar_sql($total_sql);
559 sub cust_bill_pkg_count_pkgnum {
560 # for ARPU calculation
562 my $total_sql = 'SELECT COUNT(DISTINCT pkgnum) '.
563 $self->_cust_bill_pkg_recurring(@_);
565 $self->scalar_sql($total_sql);
568 =item cust_bill_pkg_detail: the total usage charges in detail lines.
570 Arguments as for C<cust_bill_pkg>, plus:
572 'usageclass': limit to this usage class number.
576 sub cust_bill_pkg_detail {
577 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
580 ( "(cust_bill_pkg.pkgnum != 0 OR cust_bill_pkg.feepart IS NOT NULL)" );
582 $agentnum ||= $opt{'agentnum'};
585 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
586 $self->with_usageclass($opt{'usageclass'}),
587 $self->with_report_option(%opt),
588 $self->with_refnum(%opt),
589 $self->with_cust_classnum(%opt)
592 if ( $opt{'distribute'} ) {
594 $where[0] = 'cust_bill_pkg.pkgnum != 0';
595 # and limit according to the usage time, not the billing date
596 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
597 'cust_bill_pkg_detail.startdate'
601 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
606 my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) ";
609 " FROM cust_bill_pkg_detail
610 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
611 LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
612 LEFT JOIN cust_main USING ( custnum )
613 LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
614 LEFT JOIN part_pkg USING ( pkgpart )
615 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
616 LEFT JOIN part_fee USING ( feepart )
617 WHERE ".join( ' AND ', grep $_, @where );
619 $self->scalar_sql($total_sql);
623 sub cust_bill_pkg_discount {
624 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
626 #need to do this the new multi-classnum way if it gets re-enabled
628 #my $comparison = '';
629 #if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
631 # $comparison = "IS NULL";
633 # $comparison = "= $1";
636 # if ( $opt{'use_override'} ) {
638 # part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
639 # override.classnum $comparison AND pkgpart_override IS NOT NULL
642 # $where = "part_pkg.classnum $comparison";
646 $agentnum ||= $opt{'agentnum'};
649 " SELECT COALESCE( SUM( cust_bill_pkg_discount.amount ), 0 ) ";
652 " FROM cust_bill_pkg_discount
653 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
654 LEFT JOIN cust_bill USING ( invnum )
655 LEFT JOIN cust_main USING ( custnum )
656 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
657 # LEFT JOIN cust_pkg_discount USING ( pkgdiscountnum )
658 # LEFT JOIN discount USING ( discountnum )
659 # LEFT JOIN cust_pkg USING ( pkgnum )
660 # LEFT JOIN part_pkg USING ( pkgpart )
661 # LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
663 return $self->scalar_sql($total_sql);
667 ##### package churn report #####
669 =item active_pkg: The number of packages that were active at the start of
670 the period. The end date of the period is ignored. Options:
672 - refnum: Limit to customers with this advertising source.
673 - classnum: Limit to packages with this class.
674 - towernum: Limit to packages that have a broadband service with this tower.
675 - zip: Limit to packages with this service location zip code.
677 Except for zip, any of these can be an arrayref to allow multiple values for
680 =item setup_pkg: The number of packages with setup dates in the period. This
681 excludes packages created by package changes. Options are as for active_pkg.
683 =item susp_pkg: The number of packages that were suspended in the period
684 (and not canceled). Options are as for active_pkg.
686 =item unsusp_pkg: The number of packages that were unsuspended in the period.
687 Options are as for active_pkg.
689 =item cancel_pkg: The number of packages with cancel dates in the period.
690 Excludes packages that were canceled to be changed to a new package. Options
691 are as for active_pkg.
697 $self->churn_pkg('active', @_);
702 $self->churn_pkg('setup', @_);
707 $self->churn_pkg('cancel', @_);
712 $self->churn_pkg('susp', @_);
717 $self->churn_pkg('unsusp', @_);
722 my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_;
724 FS::h_cust_pkg->churn_fromwhere_sql( $status, $speriod, $eperiod);
726 push @where, $self->pkg_where(%opt, 'agentnum' => $agentnum);
728 my $sql = "SELECT COUNT(*) FROM $from
729 JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart)
730 JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum)";
731 $sql .= ' WHERE '.join(' AND ', @where)
734 $self->scalar_sql($sql);
741 "part_pkg.freq != '0'",
742 $self->with_refnum(%opt),
743 $self->with_towernum(%opt),
744 $self->with_zip(%opt),
746 if ($opt{agentnum} =~ /^(\d+)$/) {
747 push @where, "cust_main.agentnum = $1";
749 if ($opt{classnum}) {
750 my $classnum = $opt{classnum};
751 $classnum = [ $classnum ] if !ref($classnum);
752 @$classnum = grep /^\d+$/, @$classnum;
753 my $in = 'IN ('. join(',', @$classnum). ')';
754 push @where, "COALESCE(part_pkg.classnum, 0) $in" if scalar @$classnum;
759 ##### end of package churn report stuff #####
761 ##### customer churn report #####
763 =item active_cust: The number of customers who had any active recurring
764 packages at the start of the period. The end date is ignored, agentnum is
765 mandatory, and no other parameters are accepted.
767 =item started_cust: The number of customers who had no active packages at
768 the start of the period, but had active packages at the end. Like
769 active_cust, agentnum is mandatory and no other parameters are accepted.
771 =item suspended_cust: The number of customers who had active packages at
772 the start of the period, and at the end had no active packages but some
773 suspended packages. Note that this does not necessarily mean that their
774 packages were suspended during the period.
776 =item resumed_cust: The inverse of suspended_cust: the number of customers
777 who had suspended packages and no active packages at the start of the
778 period, and active packages at the end.
780 =item cancelled_cust: The number of customers who had active packages
781 at the start of the period, and only cancelled packages at the end.
787 $self->churn_cust(@_)->{active};
791 $self->churn_cust(@_)->{started};
795 $self->churn_cust(@_)->{suspended};
799 $self->churn_cust(@_)->{resumed};
803 $self->churn_cust(@_)->{cancelled};
808 my ( $speriod ) = @_;
810 # run one query for each interval
811 return $self->{_interval}{$speriod} ||= $self->calculate_churn_cust(@_);
814 sub calculate_churn_cust {
816 my ($speriod, $eperiod, $agentnum, %opt) = @_;
818 my $churn_sql = FS::cust_main::Status->churn_sql($speriod, $eperiod);
820 $where = " WHERE cust_main.agentnum = $agentnum " if $agentnum;
823 "FROM cust_main JOIN ($churn_sql) AS churn USING (custnum)".
826 # query to count the ones with certain status combinations
828 SELECT SUM((s_active > 0)::int) as active,
829 SUM((s_active = 0 and e_active > 0)::int) as started,
830 SUM((s_active > 0 and e_active = 0 and e_suspended > 0)::int)
832 SUM((s_active = 0 and s_suspended > 0 and e_active > 0)::int)
834 SUM((s_active > 0 and e_active = 0 and e_suspended = 0)::int)
836 FROM ($cust_sql) AS x
839 my $sth = dbh->prepare($total_sql);
840 $sth->execute or die "failed to execute churn query: " . $sth->errstr;
842 $self->{_interval}{$speriod} = $sth->fetchrow_hashref;
845 sub in_time_period_and_agent {
846 my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
847 my $col = @_ ? shift() : '_date';
849 my $sql = "$col >= $speriod AND $col < $eperiod";
852 $sql .= " AND cust_main.agentnum = $agentnum"
855 #agent virtualization
857 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table'=>'cust_main' );
863 my ( $self, %opt ) = @_;
865 if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
866 $sql .= " and custnum = $1 ";
868 if ( $opt{'refnum'} ) {
869 my $refnum = $opt{'refnum'};
870 $refnum = [ $refnum ] if !ref($refnum);
871 my $in = join(',', grep /^\d+$/, @$refnum);
872 $sql .= " and refnum IN ($in)" if length $in;
874 if ( my $where = $self->with_cust_classnum(%opt) ) {
875 $sql .= " and $where";
882 my ($self, $classnum, $use_override) = @_;
883 return '' if $classnum eq '';
885 $classnum = [ $classnum ] if !ref($classnum);
886 @$classnum = grep /^\d+$/, @$classnum;
887 my $in = 'IN ('. join(',', @$classnum). ')';
890 ( COALESCE(part_pkg.classnum, 0) $in AND pkgpart_override IS NULL)
891 OR ( COALESCE(part_fee.classnum, 0) $in AND feepart IS NOT NULL )";
892 if ( $use_override ) {
894 OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )";
899 sub with_usageclass {
901 my ($classnum, $use_override) = @_;
902 return '' unless $classnum =~ /^\d+$/;
904 if ( $classnum == 0 ) {
905 $comparison = 'IS NULL';
908 $comparison = "= $classnum";
910 return "cust_bill_pkg_detail.classnum $comparison";
913 sub with_report_option {
914 my ($self, %opt) = @_;
916 # - report_optionnum: a comma-separated list of numbers. Zero means to
917 # include packages with _no_ report classes.
918 # - not_report_optionnum: a comma-separated list. Packages that have
919 # any of these report options will be excluded from the result.
921 # - use_override: also matches line items that are add-ons to a package
922 # matching the report class.
923 # - all_report_options: returns only packages that have ALL of the
924 # report classes listed in $num. Otherwise, will return packages that
925 # have ANY of those classes.
927 my @num = ref($opt{'report_optionnum'})
928 ? @{ $opt{'report_optionnum'} }
929 : split(/\s*,\s*/, $opt{'report_optionnum'});
930 my @not_num = ref($opt{'not_report_optionnum'})
931 ? @{ $opt{'not_report_optionnum'} }
932 : split(/\s*,\s*/, $opt{'not_report_optionnum'});
934 $null = 1 if ( grep {$_ == 0} @num );
935 @num = grep {$_ > 0} @num;
936 @not_num = grep {$_ > 0} @not_num;
939 my $table = $opt{'use_override'} ? 'override' : 'part_pkg';
941 if ( $opt{'all_report_options'} ) {
942 if ( @num and $null ) {
943 return 'false'; # mutually exclusive criteria, so just bail out
947 my @where_num = map {
948 "EXISTS(SELECT 1 FROM part_pkg_option ".
949 "WHERE optionname = 'report_option_$_' ".
950 "AND part_pkg_option.pkgpart = $table.pkgpart)"
953 push @where_num, "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
954 "WHERE optionname LIKE 'report_option_%' ".
955 "AND part_pkg_option.pkgpart = $table.pkgpart)";
957 my @where_not_num = map {
958 "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
959 "WHERE optionname = 'report_option_$_' ".
960 "AND part_pkg_option.pkgpart = $table.pkgpart)"
965 push @where, '( '.join($op, @where_num).' )';
967 if (@where_not_num) {
968 push @where, '( '.join(' AND ', @where_not_num).' )';
972 # this messes up totals
973 #if ( $opt{'use_override'} ) {
974 # # then also allow the non-override package to match
975 # delete $opt{'use_override'};
976 # $comparison = "( $comparison OR " . $self->with_report_option(%opt) . ")";
982 my ($self, %opt) = @_;
983 if ( $opt{'refnum'} ) {
984 my $refnum = $opt{'refnum'};
985 $refnum = [ $refnum ] if !ref($refnum);
986 my $in = join(',', grep /^\d+$/, @$refnum);
987 return "cust_main.refnum IN ($in)" if length $in;
993 my ($self, %opt) = @_;
994 if ( $opt{'towernum'} ) {
995 my $towernum = $opt{'towernum'};
996 $towernum = [ $towernum ] if !ref($towernum);
997 my $in = join(',', grep /^\d+$/, @$towernum);
998 return unless length($in); # if no towers are specified, don't restrict
1000 # materialize/cache the set of pkgnums that, as of the last
1001 # svc_broadband history record, had a certain towernum
1002 # (because otherwise this is painfully slow)
1003 $self->_init_tower_pkg_cache;
1006 SELECT 1 FROM tower_pkg_cache
1007 WHERE towernum IN($in)
1008 AND cust_pkg.pkgnum = tower_pkg_cache.pkgnum
1015 my ($self, %opt) = @_;
1016 if (length($opt{'zip'})) {
1017 return "(SELECT zip FROM cust_location
1018 WHERE cust_location.locationnum = cust_pkg.locationnum
1019 ) = " . dbh->quote($opt{'zip'});
1024 sub with_cust_classnum {
1025 my ($self, %opt) = @_;
1026 if ( $opt{'cust_classnum'} ) {
1027 my $classnums = $opt{'cust_classnum'};
1028 $classnums = [ $classnums ] if !ref($classnums);
1029 @$classnums = grep /^\d+$/, @$classnums;
1030 return 'cust_main.classnum in('. join(',',@$classnums) .')'
1038 my( $self, $sql ) = ( shift, shift );
1039 my $sth = dbh->prepare($sql) or die dbh->errstr;
1040 warn "FS::Report::Table\n$sql\n" if $DEBUG;
1042 or die "Unexpected error executing statement $sql: ". $sth->errstr;
1043 $sth->fetchrow_arrayref->[0] || 0;
1052 =item init_projection
1054 Sets up for future projection of all observables on the report. Currently
1055 this is limited to 'cust_bill_pkg'.
1059 sub init_projection {
1060 # this is weird special case stuff--some redesign may be needed
1061 # to use it for anything else
1064 if ( driver_name ne 'Pg' ) {
1065 # also database-specific for now
1066 die "projection reports not supported on this platform";
1069 my %items = map {$_ => 1} @{ $self->{items} };
1070 if ($items{'cust_bill_pkg'}) {
1074 # could use TEMPORARY TABLE but we're already transaction-protected
1075 'DROP TABLE IF EXISTS v_cust_bill_pkg',
1076 'CREATE TABLE v_cust_bill_pkg ' .
1077 '(LIKE cust_bill_pkg,
1078 usage numeric(10,2), _date integer, expire integer)',
1079 # XXX this should be smart enough to take only the ones with
1080 # sdate/edate overlapping the ROI, for performance
1081 "INSERT INTO v_cust_bill_pkg (
1082 SELECT cust_bill_pkg.*,
1083 (SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
1084 FROM cust_bill_pkg_detail
1085 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum),
1088 FROM cust_bill_pkg $cust_bill_pkg_join
1091 foreach my $sql (@sql) {
1092 warn "[init_projection] $sql\n" if $DEBUG;
1093 $dbh->do($sql) or die $dbh->errstr;
1098 =item extend_projection START END
1100 Generates data for the next period of projection. This will be called
1101 for sequential periods where the END of one equals the START of the next
1106 sub extend_projection {
1108 my ($speriod, $eperiod) = @_;
1109 my %items = map {$_ => 1} @{ $self->{items} };
1110 if ($items{'cust_bill_pkg'}) {
1112 # Find all line items that end after the start of the period (and have
1113 # recurring fees, and don't expire before they end). Choose the latest
1114 # one for each package. If it ends before the end of the period, copy
1115 # it forward by one billing period.
1116 # Repeat this until the latest line item for each package no longer ends
1117 # within the period. This is certain to happen in finitely many
1118 # iterations as long as freq > 0.
1119 # - Pg only, obviously.
1120 # - Gives bad results if freq_override is used.
1121 my @fields = ( FS::cust_bill_pkg->fields, qw( usage _date expire ) );
1122 my $insert_fields = join(',', @fields);
1123 my $add_freq = sub { # emulate FS::part_pkg::add_freq
1125 "EXTRACT( EPOCH FROM TO_TIMESTAMP($field) + (CASE WHEN freq ~ E'\\\\D' ".
1126 "THEN freq ELSE freq || 'mon' END)::INTERVAL) AS $field";
1129 if ($_ eq 'edate') {
1130 $_ = $add_freq->('edate');
1132 elsif ($_ eq 'sdate') {
1133 $_ = 'edate AS sdate'
1135 elsif ($_ eq 'setup') {
1136 $_ = '0 AS setup' #because recurring only
1138 elsif ($_ eq '_date') {
1139 $_ = $add_freq->('_date');
1142 my $select_fields = join(',', @fields);
1145 # Subquery here because we need to DISTINCT the whole set, select the
1146 # latest charge per pkgnum, and _then_ check edate < $eperiod
1147 # and edate < expire.
1148 "INSERT INTO v_cust_bill_pkg ($insert_fields)
1149 SELECT $select_fields FROM (
1150 SELECT DISTINCT ON (pkgnum) * FROM v_cust_bill_pkg
1151 WHERE edate >= $speriod
1153 AND freq IS NOT NULL
1155 ORDER BY pkgnum, edate DESC
1157 WHERE edate < $eperiod AND (edate < expire OR expire IS NULL)";
1160 warn "[extend_projection] $sql\n" if $DEBUG;
1161 $rows = $dbh->do($sql) or die $dbh->errstr;
1162 warn "[extend_projection] $rows rows\n" if $DEBUG;
1167 =item _init_tower_pkg_cache
1169 Internal method: creates a temporary table relating pkgnums to towernums.
1170 A (pkgnum, towernum) record indicates that this package once had a
1171 svc_broadband service which, as of its last insert or replace_new history
1172 record, had a sectornum associated with that towernum.
1174 This is expensive, so it won't be done more than once an hour. Historical
1175 data about package churn shouldn't be changing in realtime anyway.
1179 sub _init_tower_pkg_cache {
1183 my $current = $CACHE->get('tower_pkg_cache_update');
1186 # XXX or should this be in the schema?
1187 my $sql = "DROP TABLE IF EXISTS tower_pkg_cache";
1188 $dbh->do($sql) or die $dbh->errstr;
1189 $sql = "CREATE TABLE tower_pkg_cache (towernum int, pkgnum int)";
1190 $dbh->do($sql) or die $dbh->errstr;
1193 # sectornums never get reused, or move from one tower to another
1194 # all service history is intact
1195 # svcnums never get reused (this would be bad)
1196 # pkgnums NEVER get reused (this would be extremely bad)
1197 $sql = "INSERT INTO tower_pkg_cache (
1198 SELECT COALESCE(towernum,0), pkgnum
1199 FROM ( SELECT DISTINCT pkgnum, svcnum FROM h_cust_svc ) AS pkgnum_svcnum
1201 SELECT DISTINCT ON(svcnum) svcnum, sectornum
1202 FROM h_svc_broadband
1203 WHERE (history_action = 'replace_new'
1204 OR history_action = 'replace_old')
1205 ORDER BY svcnum ASC, history_date DESC
1206 ) AS svcnum_sectornum USING (svcnum)
1207 LEFT JOIN tower_sector USING (sectornum)
1209 $dbh->do($sql) or die $dbh->errstr;
1211 $CACHE->set('tower_pkg_cache_update', 1, 3600);
1221 L<FS::Report::Table::Monthly>, reports in the web interface.