1 package FS::Report::Table;
4 use vars qw( @ISA $DEBUG );
6 use Time::Local qw( timelocal );
7 use FS::UID qw( dbh driver_name );
11 $DEBUG = 0; # turning this on will trace all SQL statements, VERY noisy
12 @ISA = qw( FS::Report );
16 FS::Report::Table - Tables of report data
20 See the more specific report objects, currently only
21 FS::Report::Table::Monthly and FS::Report::Table::Daily.
25 The common interface for an observable named 'foo' is:
27 $report->foo($startdate, $enddate, $agentnum, %options)
29 This returns a scalar value for foo, over the period from
30 $startdate to $enddate, limited to agent $agentnum, subject to
35 =item signups: The number of customers signed up. Options are "refnum"
36 (limit by advertising source) and "indirect" (boolean, tells us to limit
37 to customers that have a referral_custnum that matches the advertising source).
42 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
43 my @where = ( $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
44 'cust_main.signupdate')
47 if ( $opt{'indirect'} ) {
48 $join = " JOIN cust_main AS referring_cust_main".
49 " ON (cust_main.referral_custnum = referring_cust_main.custnum)";
51 if ( $opt{'refnum'} ) {
52 push @where, "referring_cust_main.refnum = ".$opt{'refnum'};
55 elsif ( $opt{'refnum'} ) {
56 push @where, "refnum = ".$opt{'refnum'};
59 push @where, $self->with_cust_classnum(%opt);
62 "SELECT COUNT(*) FROM cust_main $join WHERE ".join(' AND ', @where)
66 =item invoiced: The total amount charged on all invoices.
70 sub invoiced { #invoiced
71 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
73 my $sql = 'SELECT SUM(cust_bill.charged) FROM cust_bill';
74 if ( $opt{'setuprecur'} ) {
76 FS::cust_bill_pkg->charged_sql($speriod, $eperiod, %opt).
77 ') FROM cust_bill_pkg JOIN cust_bill USING (invnum)';
82 LEFT JOIN cust_main USING ( custnum )
83 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
89 =item netsales: invoiced - netcredits
93 sub netsales { #net sales
94 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
96 $self->invoiced( $speriod, $eperiod, $agentnum, %opt)
97 - $self->netcredits($speriod, $eperiod, $agentnum, %opt);
100 =item cashflow: payments - refunds
105 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
107 $self->payments($speriod, $eperiod, $agentnum, %opt)
108 - $self->refunds( $speriod, $eperiod, $agentnum, %opt);
111 =item netcashflow: payments - netrefunds
116 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
118 $self->receipts( $speriod, $eperiod, $agentnum, %opt)
119 - $self->netrefunds( $speriod, $eperiod, $agentnum, %opt);
122 =item payments: The sum of payments received in the period.
127 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
131 LEFT JOIN cust_main USING ( custnum )
132 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
133 $self->for_opts(%opt)
137 =item credits: The sum of credits issued in the period.
142 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
144 SELECT SUM(cust_credit.amount)
146 LEFT JOIN cust_main USING ( custnum )
147 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
148 $self->for_opts(%opt)
152 =item refunds: The sum of refunds paid in the period.
157 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
161 LEFT JOIN cust_main USING ( custnum )
162 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
163 $self->for_opts(%opt)
167 =item netcredits: The sum of credit applications to invoices in the period.
172 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
174 my $sql = 'SELECT SUM(cust_credit_bill.amount) FROM cust_credit_bill';
175 if ( $opt{'setuprecur'} ) {
176 $sql = 'SELECT SUM('.
177 FS::cust_bill_pkg->credited_sql($speriod, $eperiod, %opt).
178 ') FROM cust_bill_pkg';
183 LEFT JOIN cust_bill USING ( invnum )
184 LEFT JOIN cust_main USING ( custnum )
185 WHERE ". $self->in_time_period_and_agent( $speriod,
190 $self->for_opts(%opt)
194 =item receipts: The sum of payment applications to invoices in the period.
198 sub receipts { #net payments
199 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
201 my $sql = 'SELECT SUM(cust_bill_pay.amount) FROM cust_bill_pay';
202 if ( $opt{'setuprecur'} ) {
203 $sql = 'SELECT SUM('.
204 FS::cust_bill_pkg->paid_sql($speriod, $eperiod, %opt).
205 ') FROM cust_bill_pkg';
210 LEFT JOIN cust_bill USING ( invnum )
211 LEFT JOIN cust_main USING ( custnum )
212 WHERE ". $self->in_time_period_and_agent( $speriod,
217 $self->for_opts(%opt)
221 =item netrefunds: The sum of refund applications to credits in the period.
226 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
228 SELECT SUM(cust_credit_refund.amount)
229 FROM cust_credit_refund
230 LEFT JOIN cust_credit USING ( crednum )
231 LEFT JOIN cust_main USING ( custnum )
232 WHERE ". $self->in_time_period_and_agent( $speriod,
237 $self->for_opts(%opt)
243 #these should be auto-generated or $AUTOLOADed or something
245 my( $self, $speriod, $eperiod, $agentnum ) = @_;
246 $speriod = $self->_subtract_11mo($speriod);
247 $self->invoiced($speriod, $eperiod, $agentnum);
251 my( $self, $speriod, $eperiod, $agentnum ) = @_;
252 $speriod = $self->_subtract_11mo($speriod);
253 $self->netsales($speriod, $eperiod, $agentnum);
257 my( $self, $speriod, $eperiod, $agentnum ) = @_;
258 $speriod = $self->_subtract_11mo($speriod);
259 $self->receipts($speriod, $eperiod, $agentnum);
263 my( $self, $speriod, $eperiod, $agentnum ) = @_;
264 $speriod = $self->_subtract_11mo($speriod);
265 $self->payments($speriod, $eperiod, $agentnum);
269 my( $self, $speriod, $eperiod, $agentnum ) = @_;
270 $speriod = $self->_subtract_11mo($speriod);
271 $self->credits($speriod, $eperiod, $agentnum);
274 sub netcredits_12mo {
275 my( $self, $speriod, $eperiod, $agentnum ) = @_;
276 $speriod = $self->_subtract_11mo($speriod);
277 $self->netcredits($speriod, $eperiod, $agentnum);
281 my( $self, $speriod, $eperiod, $agentnum ) = @_;
282 $speriod = $self->_subtract_11mo($speriod);
283 $self->cashflow($speriod, $eperiod, $agentnum);
286 sub netcashflow_12mo {
287 my( $self, $speriod, $eperiod, $agentnum ) = @_;
288 $speriod = $self->_subtract_11mo($speriod);
289 $self->cashflow($speriod, $eperiod, $agentnum);
293 my( $self, $speriod, $eperiod, $agentnum ) = @_;
294 $speriod = $self->_subtract_11mo($speriod);
295 $self->refunds($speriod, $eperiod, $agentnum);
298 sub netrefunds_12mo {
299 my( $self, $speriod, $eperiod, $agentnum ) = @_;
300 $speriod = $self->_subtract_11mo($speriod);
301 $self->netrefunds($speriod, $eperiod, $agentnum);
305 #not being too bad with the false laziness
307 my($self, $time) = @_;
308 my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
310 if ( $mon < 0 ) { $mon+=12; $year--; }
311 timelocal($sec,$min,$hour,$mday,$mon,$year);
314 =item cust_pkg_setup_cost: The total setup costs of packages setup in the period
316 'classnum': limit to this package class.
320 sub cust_pkg_setup_cost {
321 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
324 if ( $opt{'classnum'} ne '' ) {
325 my $classnums = $opt{'classnum'};
326 $classnums = [ $classnums ] if !ref($classnums);
327 @$classnums = grep /^\d+$/, @$classnums;
328 $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
332 $agentnum ||= $opt{'agentnum'};
334 my $total_sql = " SELECT SUM(part_pkg.setup_cost) ";
335 $total_sql .= " FROM cust_pkg
336 LEFT JOIN cust_main USING ( custnum )
337 LEFT JOIN part_pkg USING ( pkgpart )
340 AND ".$self->in_time_period_and_agent(
341 $speriod, $eperiod, $agentnum, 'cust_pkg.setup');
342 return $self->scalar_sql($total_sql);
345 =item cust_pkg_recur_cust: the total recur costs of packages in the period
347 'classnum': limit to this package class.
351 sub cust_pkg_recur_cost {
352 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
355 if ( $opt{'classnum'} ne '' ) {
356 my $classnums = $opt{'classnum'};
357 $classnums = [ $classnums ] if !ref($classnums);
358 @$classnums = grep /^\d+$/, @$classnums;
359 $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
363 $agentnum ||= $opt{'agentnum'};
364 # duplication of in_time_period_and_agent
365 # because we do it a little differently here
366 $where .= " AND cust_main.agentnum = $agentnum" if $agentnum;
368 $FS::CurrentUser::CurrentUser->agentnums_sql('table' => 'cust_main');
370 my $total_sql = " SELECT SUM(part_pkg.recur_cost) ";
371 $total_sql .= " FROM cust_pkg
372 LEFT JOIN cust_main USING ( custnum )
373 LEFT JOIN part_pkg USING ( pkgpart )
376 AND cust_pkg.setup < $eperiod
377 AND (cust_pkg.cancel > $speriod OR cust_pkg.cancel IS NULL)
379 return $self->scalar_sql($total_sql);
382 =item cust_bill_pkg: the total package charges on invoice line items.
384 'charges': limit the type of charges included (setup, recur, usage).
385 Should be a string containing one or more of 'S', 'R', or 'U'; if
386 unspecified, defaults to all three.
388 'classnum': limit to this package class.
390 'use_override': for line items generated by an add-on package, use the class
391 of the add-on rather than the base package.
393 'distribute': for non-monthly recurring charges, ignore the invoice
394 date. Instead, consider the line item's starting/ending dates. Determine
395 the fraction of the line item duration that falls within the specified
396 interval and return that fraction of the recurring charges. This is
397 somewhat experimental.
399 'project': enable if this is a projected period. This is very experimental.
405 my( $speriod, $eperiod, $agentnum, %opt ) = @_;
407 my %charges = map {$_=>1} split('', $opt{'charges'} || 'SRU');
410 $sum += $self->cust_bill_pkg_setup(@_) if $charges{S};
411 $sum += $self->cust_bill_pkg_recur(@_) if $charges{R};
412 $sum += $self->cust_bill_pkg_detail(@_) if $charges{U};
416 my $cust_bill_pkg_join = '
417 LEFT JOIN cust_bill USING ( invnum )
418 LEFT JOIN cust_main USING ( custnum )
419 LEFT JOIN cust_pkg USING ( pkgnum )
420 LEFT JOIN part_pkg USING ( pkgpart )
421 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
422 LEFT JOIN part_fee USING ( feepart )';
424 sub cust_bill_pkg_setup {
426 my ($speriod, $eperiod, $agentnum, %opt) = @_;
427 # no projecting setup fees--use real invoices only
428 # but evaluate this anyway, because the design of projection is that
429 # if there are somehow real setup fees in the future, we want to count
432 $agentnum ||= $opt{'agentnum'};
435 '(pkgnum != 0 OR feepart IS NOT NULL)',
436 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
437 $self->with_report_option(%opt),
438 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
441 # yuck, false laziness
442 push @where, "cust_main.refnum = ". $opt{'refnum'} if $opt{'refnum'};
444 push @where, $self->with_cust_classnum(%opt);
446 my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0)
449 WHERE " . join(' AND ', grep $_, @where);
451 $self->scalar_sql($total_sql);
454 sub cust_bill_pkg_recur {
456 my ($speriod, $eperiod, $agentnum, %opt) = @_;
458 $agentnum ||= $opt{'agentnum'};
459 my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
462 '(pkgnum != 0 OR feepart IS NOT NULL)',
463 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
464 $self->with_report_option(%opt),
467 push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
469 push @where, $self->with_cust_classnum(%opt);
471 # subtract all usage from the line item regardless of date
473 if ( $opt{'project'} ) {
474 $item_usage = 'usage'; #already calculated
477 $item_usage = '( SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
478 FROM cust_bill_pkg_detail
479 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
481 my $recur_fraction = '';
483 if ( $opt{'distribute'} ) {
484 $where[0] = 'pkgnum != 0'; # specifically exclude fees
485 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
487 "$cust_bill_pkg.sdate < $eperiod",
488 "$cust_bill_pkg.edate >= $speriod",
490 # the fraction of edate - sdate that's within [speriod, eperiod]
491 $recur_fraction = " *
492 CAST(LEAST($eperiod, $cust_bill_pkg.edate) -
493 GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) /
494 ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
497 # we don't want to have to create v_cust_bill
498 my $_date = $opt{'project'} ? 'v_cust_bill_pkg._date' : 'cust_bill._date';
500 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date);
503 my $total_sql = 'SELECT '.
504 "COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)
507 WHERE ".join(' AND ', grep $_, @where);
509 $self->scalar_sql($total_sql);
512 =item cust_bill_pkg_detail: the total usage charges in detail lines.
514 Arguments as for C<cust_bill_pkg>, plus:
516 'usageclass': limit to this usage class number.
520 sub cust_bill_pkg_detail {
521 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
524 ( "(cust_bill_pkg.pkgnum != 0 OR cust_bill_pkg.feepart IS NOT NULL)" );
526 push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
528 push @where, $self->with_cust_classnum(%opt);
530 $agentnum ||= $opt{'agentnum'};
533 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
534 $self->with_usageclass($opt{'usageclass'}),
535 $self->with_report_option(%opt),
538 if ( $opt{'distribute'} ) {
540 $where[0] = 'cust_bill_pkg.pkgnum != 0';
541 # and limit according to the usage time, not the billing date
542 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
543 'cust_bill_pkg_detail.startdate'
547 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
552 my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) ";
555 " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
556 if $opt{average_per_cust_pkg};
559 " FROM cust_bill_pkg_detail
560 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
561 LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
562 LEFT JOIN cust_main USING ( custnum )
563 LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
564 LEFT JOIN part_pkg USING ( pkgpart )
565 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
566 LEFT JOIN part_fee USING ( feepart )
567 WHERE ".join( ' AND ', grep $_, @where );
569 $self->scalar_sql($total_sql);
573 sub cust_bill_pkg_discount {
574 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
576 #need to do this the new multi-classnum way if it gets re-enabled
578 #my $comparison = '';
579 #if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
581 # $comparison = "IS NULL";
583 # $comparison = "= $1";
586 # if ( $opt{'use_override'} ) {
588 # part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
589 # override.classnum $comparison AND pkgpart_override IS NOT NULL
592 # $where = "part_pkg.classnum $comparison";
596 $agentnum ||= $opt{'agentnum'};
599 " SELECT COALESCE( SUM( cust_bill_pkg_discount.amount ), 0 ) ";
602 # " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
603 # if $opt{average_per_cust_pkg};
606 " FROM cust_bill_pkg_discount
607 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
608 LEFT JOIN cust_bill USING ( invnum )
609 LEFT JOIN cust_main USING ( custnum )
610 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
611 # LEFT JOIN cust_pkg_discount USING ( pkgdiscountnum )
612 # LEFT JOIN discount USING ( discountnum )
613 # LEFT JOIN cust_pkg USING ( pkgnum )
614 # LEFT JOIN part_pkg USING ( pkgpart )
615 # LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
617 return $self->scalar_sql($total_sql);
621 sub setup_pkg { shift->pkg_field( 'setup', @_ ); }
622 sub susp_pkg { shift->pkg_field( 'susp', @_ ); }
623 sub cancel_pkg { shift->pkg_field( 'cancel', @_ ); }
626 my( $self, $field, $speriod, $eperiod, $agentnum ) = @_;
628 SELECT COUNT(*) FROM cust_pkg
629 LEFT JOIN cust_main USING ( custnum )
630 WHERE ". $self->in_time_period_and_agent( $speriod,
639 #this is going to be harder..
641 # my( $self, $speriod, $eperiod, $agentnum ) = @_;
642 # $self->scalar_sql("
643 # SELECT COUNT(*) FROM h_cust_pkg
648 sub in_time_period_and_agent {
649 my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
650 my $col = @_ ? shift() : '_date';
652 my $sql = "$col >= $speriod AND $col < $eperiod";
655 $sql .= " AND cust_main.agentnum = $agentnum"
658 #agent virtualization
660 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table'=>'cust_main' );
666 my ( $self, %opt ) = @_;
668 if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
669 $sql .= " and custnum = $1 ";
671 if ( $opt{'refnum'} =~ /^(\d+)$/ ) {
672 $sql .= " and refnum = $1 ";
674 if ( my $where = $self->with_cust_classnum(%opt) ) {
675 $sql .= " and $where";
682 my ($self, $classnum, $use_override) = @_;
683 return '' if $classnum eq '';
685 $classnum = [ $classnum ] if !ref($classnum);
686 @$classnum = grep /^\d+$/, @$classnum;
687 my $in = 'IN ('. join(',', @$classnum). ')';
690 ( COALESCE(part_pkg.classnum, 0) $in AND pkgpart_override IS NULL)
691 OR ( COALESCE(part_fee.classnum, 0) $in AND feepart IS NOT NULL )";
692 if ( $use_override ) {
694 OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )";
699 sub with_usageclass {
701 my ($classnum, $use_override) = @_;
702 return '' unless $classnum =~ /^\d+$/;
704 if ( $classnum == 0 ) {
705 $comparison = 'IS NULL';
708 $comparison = "= $classnum";
710 return "cust_bill_pkg_detail.classnum $comparison";
713 sub with_report_option {
714 my ($self, %opt) = @_;
716 # - report_optionnum: a comma-separated list of numbers. Zero means to
717 # include packages with _no_ report classes.
718 # - not_report_optionnum: a comma-separated list. Packages that have
719 # any of these report options will be excluded from the result.
721 # - use_override: also matches line items that are add-ons to a package
722 # matching the report class.
723 # - all_report_options: returns only packages that have ALL of the
724 # report classes listed in $num. Otherwise, will return packages that
725 # have ANY of those classes.
727 my @num = ref($opt{'report_optionnum'})
728 ? @{ $opt{'report_optionnum'} }
729 : split(/\s*,\s*/, $opt{'report_optionnum'});
730 my @not_num = ref($opt{'not_report_optionnum'})
731 ? @{ $opt{'not_report_optionnum'} }
732 : split(/\s*,\s*/, $opt{'not_report_optionnum'});
734 $null = 1 if ( grep {$_ == 0} @num );
735 @num = grep {$_ > 0} @num;
736 @not_num = grep {$_ > 0} @not_num;
739 my $table = $opt{'use_override'} ? 'override' : 'part_pkg';
741 if ( $opt{'all_report_options'} ) {
742 if ( @num and $null ) {
743 return 'false'; # mutually exclusive criteria, so just bail out
747 my @where_num = map {
748 "EXISTS(SELECT 1 FROM part_pkg_option ".
749 "WHERE optionname = 'report_option_$_' ".
750 "AND part_pkg_option.pkgpart = $table.pkgpart)"
753 push @where_num, "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
754 "WHERE optionname LIKE 'report_option_%' ".
755 "AND part_pkg_option.pkgpart = $table.pkgpart)";
757 my @where_not_num = map {
758 "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
759 "WHERE optionname = 'report_option_$_' ".
760 "AND part_pkg_option.pkgpart = $table.pkgpart)"
765 push @where, '( '.join($op, @where_num).' )';
767 if (@where_not_num) {
768 push @where, '( '.join(' AND ', @where_not_num).' )';
772 # this messes up totals
773 #if ( $opt{'use_override'} ) {
774 # # then also allow the non-override package to match
775 # delete $opt{'use_override'};
776 # $comparison = "( $comparison OR " . $self->with_report_option(%opt) . ")";
781 sub with_cust_classnum {
782 my ($self, %opt) = @_;
783 if ( $opt{'cust_classnum'} ) {
784 my $classnums = $opt{'cust_classnum'};
785 $classnums = [ $classnums ] if !ref($classnums);
786 @$classnums = grep /^\d+$/, @$classnums;
787 return 'cust_main.classnum in('. join(',',@$classnums) .')'
795 my( $self, $sql ) = ( shift, shift );
796 my $sth = dbh->prepare($sql) or die dbh->errstr;
797 warn "FS::Report::Table\n$sql\n" if $DEBUG;
799 or die "Unexpected error executing statement $sql: ". $sth->errstr;
800 $sth->fetchrow_arrayref->[0] || 0;
809 =item init_projection
811 Sets up for future projection of all observables on the report. Currently
812 this is limited to 'cust_bill_pkg'.
816 sub init_projection {
817 # this is weird special case stuff--some redesign may be needed
818 # to use it for anything else
821 if ( driver_name ne 'Pg' ) {
822 # also database-specific for now
823 die "projection reports not supported on this platform";
826 my %items = map {$_ => 1} @{ $self->{items} };
827 if ($items{'cust_bill_pkg'}) {
831 # could use TEMPORARY TABLE but we're already transaction-protected
832 'DROP TABLE IF EXISTS v_cust_bill_pkg',
833 'CREATE TABLE v_cust_bill_pkg ' .
834 '(LIKE cust_bill_pkg,
835 usage numeric(10,2), _date integer, expire integer)',
836 # XXX this should be smart enough to take only the ones with
837 # sdate/edate overlapping the ROI, for performance
838 "INSERT INTO v_cust_bill_pkg (
839 SELECT cust_bill_pkg.*,
840 (SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
841 FROM cust_bill_pkg_detail
842 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum),
845 FROM cust_bill_pkg $cust_bill_pkg_join
848 foreach my $sql (@sql) {
849 warn "[init_projection] $sql\n" if $DEBUG;
850 $dbh->do($sql) or die $dbh->errstr;
855 =item extend_projection START END
857 Generates data for the next period of projection. This will be called
858 for sequential periods where the END of one equals the START of the next
863 sub extend_projection {
865 my ($speriod, $eperiod) = @_;
866 my %items = map {$_ => 1} @{ $self->{items} };
867 if ($items{'cust_bill_pkg'}) {
869 # Find all line items that end after the start of the period (and have
870 # recurring fees, and don't expire before they end). Choose the latest
871 # one for each package. If it ends before the end of the period, copy
872 # it forward by one billing period.
873 # Repeat this until the latest line item for each package no longer ends
874 # within the period. This is certain to happen in finitely many
875 # iterations as long as freq > 0.
876 # - Pg only, obviously.
877 # - Gives bad results if freq_override is used.
878 my @fields = ( FS::cust_bill_pkg->fields, qw( usage _date expire ) );
879 my $insert_fields = join(',', @fields);
880 my $add_freq = sub { # emulate FS::part_pkg::add_freq
882 "EXTRACT( EPOCH FROM TO_TIMESTAMP($field) + (CASE WHEN freq ~ E'\\\\D' ".
883 "THEN freq ELSE freq || 'mon' END)::INTERVAL) AS $field";
887 $_ = $add_freq->('edate');
889 elsif ($_ eq 'sdate') {
890 $_ = 'edate AS sdate'
892 elsif ($_ eq 'setup') {
893 $_ = '0 AS setup' #because recurring only
895 elsif ($_ eq '_date') {
896 $_ = $add_freq->('_date');
899 my $select_fields = join(',', @fields);
902 # Subquery here because we need to DISTINCT the whole set, select the
903 # latest charge per pkgnum, and _then_ check edate < $eperiod
904 # and edate < expire.
905 "INSERT INTO v_cust_bill_pkg ($insert_fields)
906 SELECT $select_fields FROM (
907 SELECT DISTINCT ON (pkgnum) * FROM v_cust_bill_pkg
908 WHERE edate >= $speriod
912 ORDER BY pkgnum, edate DESC
914 WHERE edate < $eperiod AND (edate < expire OR expire IS NULL)";
917 warn "[extend_projection] $sql\n" if $DEBUG;
918 $rows = $dbh->do($sql) or die $dbh->errstr;
919 warn "[extend_projection] $rows rows\n" if $DEBUG;
930 L<FS::Report::Table::Monthly>, reports in the web interface.