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 if ( $opt{'cust_classnum'} ) {
60 my $classnums = $opt{'cust_classnum'};
61 $classnums = [ $classnums ] if !ref($classnums);
62 @$classnums = grep /^\d+$/, @$classnums;
63 push @where, 'cust_main.classnum in('. join(',',@$classnums) .')';
67 "SELECT COUNT(*) FROM cust_main $join WHERE ".join(' AND ', @where)
71 =item invoiced: The total amount charged on all invoices.
75 sub invoiced { #invoiced
76 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
78 my $sql = 'SELECT SUM(cust_bill.charged) FROM cust_bill';
79 if ( $opt{'setuprecur'} ) {
81 FS::cust_bill_pkg->charged_sql($speriod, $eperiod, %opt).
82 ') FROM cust_bill_pkg JOIN cust_bill USING (invnum)';
87 LEFT JOIN cust_main USING ( custnum )
88 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
94 =item netsales: invoiced - netcredits
98 sub netsales { #net sales
99 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
101 $self->invoiced( $speriod, $eperiod, $agentnum, %opt)
102 - $self->netcredits($speriod, $eperiod, $agentnum, %opt);
105 =item cashflow: payments - refunds
110 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
112 $self->payments($speriod, $eperiod, $agentnum, %opt)
113 - $self->refunds( $speriod, $eperiod, $agentnum, %opt);
116 =item netcashflow: payments - netrefunds
121 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
123 $self->receipts( $speriod, $eperiod, $agentnum, %opt)
124 - $self->netrefunds( $speriod, $eperiod, $agentnum, %opt);
127 =item payments: The sum of payments received in the period.
132 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
136 LEFT JOIN cust_main USING ( custnum )
137 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
138 $self->for_opts(%opt)
142 =item credits: The sum of credits issued in the period.
147 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
151 LEFT JOIN cust_main USING ( custnum )
152 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
153 $self->for_opts(%opt)
157 =item refunds: The sum of refunds paid in the period.
162 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
166 LEFT JOIN cust_main USING ( custnum )
167 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
168 $self->for_opts(%opt)
172 =item netcredits: The sum of credit applications to invoices in the period.
177 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
179 my $sql = 'SELECT SUM(cust_credit_bill.amount) FROM cust_credit_bill';
180 if ( $opt{'setuprecur'} ) {
181 $sql = 'SELECT SUM('.
182 FS::cust_bill_pkg->credited_sql($speriod, $eperiod, %opt).
183 ') FROM cust_bill_pkg';
188 LEFT JOIN cust_bill USING ( invnum )
189 LEFT JOIN cust_main USING ( custnum )
190 WHERE ". $self->in_time_period_and_agent( $speriod,
195 $self->for_opts(%opt)
199 =item receipts: The sum of payment applications to invoices in the period.
203 sub receipts { #net payments
204 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
206 my $sql = 'SELECT SUM(cust_bill_pay.amount) FROM cust_bill_pay';
207 if ( $opt{'setuprecur'} ) {
208 $sql = 'SELECT SUM('.
209 FS::cust_bill_pkg->paid_sql($speriod, $eperiod, %opt).
210 ') FROM cust_bill_pkg';
215 LEFT JOIN cust_bill USING ( invnum )
216 LEFT JOIN cust_main USING ( custnum )
217 WHERE ". $self->in_time_period_and_agent( $speriod,
222 $self->for_opts(%opt)
226 =item netrefunds: The sum of refund applications to credits in the period.
231 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
233 SELECT SUM(cust_credit_refund.amount)
234 FROM cust_credit_refund
235 LEFT JOIN cust_credit USING ( crednum )
236 LEFT JOIN cust_main USING ( custnum )
237 WHERE ". $self->in_time_period_and_agent( $speriod,
242 $self->for_opts(%opt)
248 #these should be auto-generated or $AUTOLOADed or something
250 my( $self, $speriod, $eperiod, $agentnum ) = @_;
251 $speriod = $self->_subtract_11mo($speriod);
252 $self->invoiced($speriod, $eperiod, $agentnum);
256 my( $self, $speriod, $eperiod, $agentnum ) = @_;
257 $speriod = $self->_subtract_11mo($speriod);
258 $self->netsales($speriod, $eperiod, $agentnum);
262 my( $self, $speriod, $eperiod, $agentnum ) = @_;
263 $speriod = $self->_subtract_11mo($speriod);
264 $self->receipts($speriod, $eperiod, $agentnum);
268 my( $self, $speriod, $eperiod, $agentnum ) = @_;
269 $speriod = $self->_subtract_11mo($speriod);
270 $self->payments($speriod, $eperiod, $agentnum);
274 my( $self, $speriod, $eperiod, $agentnum ) = @_;
275 $speriod = $self->_subtract_11mo($speriod);
276 $self->credits($speriod, $eperiod, $agentnum);
279 sub netcredits_12mo {
280 my( $self, $speriod, $eperiod, $agentnum ) = @_;
281 $speriod = $self->_subtract_11mo($speriod);
282 $self->netcredits($speriod, $eperiod, $agentnum);
286 my( $self, $speriod, $eperiod, $agentnum ) = @_;
287 $speriod = $self->_subtract_11mo($speriod);
288 $self->cashflow($speriod, $eperiod, $agentnum);
291 sub netcashflow_12mo {
292 my( $self, $speriod, $eperiod, $agentnum ) = @_;
293 $speriod = $self->_subtract_11mo($speriod);
294 $self->cashflow($speriod, $eperiod, $agentnum);
298 my( $self, $speriod, $eperiod, $agentnum ) = @_;
299 $speriod = $self->_subtract_11mo($speriod);
300 $self->refunds($speriod, $eperiod, $agentnum);
303 sub netrefunds_12mo {
304 my( $self, $speriod, $eperiod, $agentnum ) = @_;
305 $speriod = $self->_subtract_11mo($speriod);
306 $self->netrefunds($speriod, $eperiod, $agentnum);
310 #not being too bad with the false laziness
312 my($self, $time) = @_;
313 my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
315 if ( $mon < 0 ) { $mon+=12; $year--; }
316 timelocal($sec,$min,$hour,$mday,$mon,$year);
319 =item cust_pkg_setup_cost: The total setup costs of packages setup in the period
321 'classnum': limit to this package class.
325 sub cust_pkg_setup_cost {
326 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
329 if ( $opt{'classnum'} ne '' ) {
330 my $classnums = $opt{'classnum'};
331 $classnums = [ $classnums ] if !ref($classnums);
332 @$classnums = grep /^\d+$/, @$classnums;
333 $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
337 $agentnum ||= $opt{'agentnum'};
339 my $total_sql = " SELECT SUM(part_pkg.setup_cost) ";
340 $total_sql .= " FROM cust_pkg
341 LEFT JOIN cust_main USING ( custnum )
342 LEFT JOIN part_pkg USING ( pkgpart )
345 AND ".$self->in_time_period_and_agent(
346 $speriod, $eperiod, $agentnum, 'cust_pkg.setup');
347 return $self->scalar_sql($total_sql);
350 =item cust_pkg_recur_cust: the total recur costs of packages in the period
352 'classnum': limit to this package class.
356 sub cust_pkg_recur_cost {
357 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
360 if ( $opt{'classnum'} ne '' ) {
361 my $classnums = $opt{'classnum'};
362 $classnums = [ $classnums ] if !ref($classnums);
363 @$classnums = grep /^\d+$/, @$classnums;
364 $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
368 $agentnum ||= $opt{'agentnum'};
369 # duplication of in_time_period_and_agent
370 # because we do it a little differently here
371 $where .= " AND cust_main.agentnum = $agentnum" if $agentnum;
373 $FS::CurrentUser::CurrentUser->agentnums_sql('table' => 'cust_main');
375 my $total_sql = " SELECT SUM(part_pkg.recur_cost) ";
376 $total_sql .= " FROM cust_pkg
377 LEFT JOIN cust_main USING ( custnum )
378 LEFT JOIN part_pkg USING ( pkgpart )
381 AND cust_pkg.setup < $eperiod
382 AND (cust_pkg.cancel > $speriod OR cust_pkg.cancel IS NULL)
384 return $self->scalar_sql($total_sql);
387 =item cust_bill_pkg: the total package charges on invoice line items.
389 'charges': limit the type of charges included (setup, recur, usage).
390 Should be a string containing one or more of 'S', 'R', or 'U'; if
391 unspecified, defaults to all three.
393 'classnum': limit to this package class.
395 'use_override': for line items generated by an add-on package, use the class
396 of the add-on rather than the base package.
398 'freq': limit to packages with this frequency. Currently uses the part_pkg
399 frequency, so term discounted packages may give odd results.
401 'distribute': for non-monthly recurring charges, ignore the invoice
402 date. Instead, consider the line item's starting/ending dates. Determine
403 the fraction of the line item duration that falls within the specified
404 interval and return that fraction of the recurring charges. This is
405 somewhat experimental.
407 'project': enable if this is a projected period. This is very experimental.
413 my( $speriod, $eperiod, $agentnum, %opt ) = @_;
415 my %charges = map {$_=>1} split('', $opt{'charges'} || 'SRU');
418 $sum += $self->cust_bill_pkg_setup(@_) if $charges{S};
419 $sum += $self->cust_bill_pkg_recur(@_) if $charges{R};
420 $sum += $self->cust_bill_pkg_detail(@_) if $charges{U};
424 my $cust_bill_pkg_join = '
425 LEFT JOIN cust_bill USING ( invnum )
426 LEFT JOIN cust_main USING ( custnum )
427 LEFT JOIN cust_pkg USING ( pkgnum )
428 LEFT JOIN part_pkg USING ( pkgpart )
429 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart';
431 sub cust_bill_pkg_setup {
433 my ($speriod, $eperiod, $agentnum, %opt) = @_;
434 # no projecting setup fees--use real invoices only
435 # but evaluate this anyway, because the design of projection is that
436 # if there are somehow real setup fees in the future, we want to count
439 $agentnum ||= $opt{'agentnum'};
443 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
444 $self->with_report_option($opt{'report_optionnum'}, $opt{'use_override'}),
445 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
448 # yuck, false laziness
449 push @where, "cust_main.refnum = ". $opt{'refnum'} if $opt{'refnum'};
451 if ( $opt{'cust_classnum'} ) {
452 my $classnums = $opt{'cust_classnum'};
453 $classnums = [ $classnums ] if !ref($classnums);
454 @$classnums = grep /^\d+$/, @$classnums;
455 push @where, 'cust_main.classnum in('. join(',',@$classnums) .')';
458 my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0)
461 WHERE " . join(' AND ', grep $_, @where);
463 $self->scalar_sql($total_sql);
466 sub cust_bill_pkg_recur {
468 my ($speriod, $eperiod, $agentnum, %opt) = @_;
470 $agentnum ||= $opt{'agentnum'};
471 my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
475 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
476 $self->with_report_option($opt{'report_optionnum'}, $opt{'use_override'}),
479 push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
481 if ( $opt{'cust_classnum'} ) {
482 my $classnums = $opt{'cust_classnum'};
483 $classnums = [ $classnums ] if !ref($classnums);
484 @$classnums = grep /^\d+$/, @$classnums;
485 push @where, 'cust_main.classnum in('. join(',',@$classnums) .')';
488 # subtract all usage from the line item regardless of date
490 if ( $opt{'project'} ) {
491 $item_usage = 'usage'; #already calculated
494 $item_usage = '( SELECT COALESCE(SUM(amount),0)
495 FROM cust_bill_pkg_detail
496 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
498 my $recur_fraction = '';
500 if ( $opt{'distribute'} ) {
501 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
503 "$cust_bill_pkg.sdate < $eperiod",
504 "$cust_bill_pkg.edate >= $speriod",
506 # the fraction of edate - sdate that's within [speriod, eperiod]
507 $recur_fraction = " *
508 CAST(LEAST($eperiod, $cust_bill_pkg.edate) -
509 GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) /
510 ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
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);
519 my $total_sql = 'SELECT '.
520 "COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)
523 WHERE ".join(' AND ', grep $_, @where);
525 $self->scalar_sql($total_sql);
528 =item cust_bill_pkg_detail: the total usage charges in detail lines.
530 Arguments as for C<cust_bill_pkg>, plus:
532 'usageclass': limit to this usage class number.
536 sub cust_bill_pkg_detail {
537 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
539 my @where = ( "cust_bill_pkg.pkgnum != 0" );
541 push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
543 if ( $opt{'cust_classnum'} ) {
544 my $classnums = $opt{'cust_classnum'};
545 $classnums = [ $classnums ] if !ref($classnums);
546 @$classnums = grep /^\d+$/, @$classnums;
547 push @where, 'cust_main.classnum in('. join(',',@$classnums) .')';
550 $agentnum ||= $opt{'agentnum'};
553 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
554 $self->with_usageclass($opt{'usageclass'}),
555 $self->with_report_option($opt{'report_optionnum'}, $opt{'use_override'}),
558 if ( $opt{'distribute'} ) {
559 # then limit according to the usage time, not the billing date
560 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
561 'cust_bill_pkg_detail.startdate'
565 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
570 my $total_sql = " SELECT SUM(amount) ";
573 " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
574 if $opt{average_per_cust_pkg};
577 " FROM cust_bill_pkg_detail
578 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
579 LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
580 LEFT JOIN cust_main USING ( custnum )
581 LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
582 LEFT JOIN part_pkg USING ( pkgpart )
583 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
584 WHERE ".join( ' AND ', grep $_, @where );
586 $self->scalar_sql($total_sql);
590 sub cust_bill_pkg_discount {
591 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
593 #need to do this the new multi-classnum way if it gets re-enabled
595 #my $comparison = '';
596 #if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
598 # $comparison = "IS NULL";
600 # $comparison = "= $1";
603 # if ( $opt{'use_override'} ) {
605 # part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
606 # override.classnum $comparison AND pkgpart_override IS NOT NULL
609 # $where = "part_pkg.classnum $comparison";
613 $agentnum ||= $opt{'agentnum'};
616 " SELECT COALESCE( SUM( cust_bill_pkg_discount.amount ), 0 ) ";
619 # " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END "
620 # if $opt{average_per_cust_pkg};
623 " FROM cust_bill_pkg_discount
624 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
625 LEFT JOIN cust_bill USING ( invnum )
626 LEFT JOIN cust_main USING ( custnum )
627 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
628 # LEFT JOIN cust_pkg_discount USING ( pkgdiscountnum )
629 # LEFT JOIN discount USING ( discountnum )
630 # LEFT JOIN cust_pkg USING ( pkgnum )
631 # LEFT JOIN part_pkg USING ( pkgpart )
632 # LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
634 return $self->scalar_sql($total_sql);
638 sub setup_pkg { shift->pkg_field( 'setup', @_ ); }
639 sub susp_pkg { shift->pkg_field( 'susp', @_ ); }
640 sub cancel_pkg { shift->pkg_field( 'cancel', @_ ); }
643 my( $self, $field, $speriod, $eperiod, $agentnum ) = @_;
645 SELECT COUNT(*) FROM cust_pkg
646 LEFT JOIN cust_main USING ( custnum )
647 WHERE ". $self->in_time_period_and_agent( $speriod,
656 #this is going to be harder..
658 # my( $self, $speriod, $eperiod, $agentnum ) = @_;
659 # $self->scalar_sql("
660 # SELECT COUNT(*) FROM h_cust_pkg
665 sub in_time_period_and_agent {
666 my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
667 my $col = @_ ? shift() : '_date';
669 my $sql = "$col >= $speriod AND $col < $eperiod";
672 $sql .= " AND cust_main.agentnum = $agentnum"
675 #agent virtualization
677 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table'=>'cust_main' );
683 my ( $self, %opt ) = @_;
685 if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
686 $sql .= " and custnum = $1 ";
688 if ( $opt{'refnum'} =~ /^(\d+)$/ ) {
689 $sql .= " and refnum = $1 ";
691 if ( $opt{'cust_classnum'} ) {
692 my $classnums = $opt{'cust_classnum'};
693 $classnums = [ $classnums ] if !ref($classnums);
694 @$classnums = grep /^\d+$/, @$classnums;
695 $sql .= ' and cust_main.classnum in('. join(',',@$classnums) .')'
703 my ($self, $classnum, $use_override) = @_;
704 return '' if $classnum eq '';
706 $classnum = [ $classnum ] if !ref($classnum);
707 @$classnum = grep /^\d+$/, @$classnum;
708 my $in = 'IN ('. join(',', @$classnum). ')';
710 if ( $use_override ) {
712 ( COALESCE(part_pkg.classnum, 0) $in AND pkgpart_override IS NULL)
713 OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )
716 "COALESCE(part_pkg.classnum, 0) $in";
720 sub with_usageclass {
722 my ($classnum, $use_override) = @_;
723 return '' unless $classnum =~ /^\d+$/;
725 if ( $classnum == 0 ) {
726 $comparison = 'IS NULL';
729 $comparison = "= $classnum";
731 return "cust_bill_pkg_detail.classnum $comparison";
734 sub with_report_option {
735 my ($self, $num, $use_override) = @_;
736 # $num can be a single number, or a comma-delimited list of numbers,
737 # or an arrayref. 0 matches the empty set
738 # or the word 'multiple' for all packages with more than one report class
739 return '' if !defined($num);
741 $num = join(',', @$num) if ref($num);
743 # stringify the set of report options for each pkgpart
744 my $table = $use_override ? 'override' : 'part_pkg';
746 SELECT replace(optionname, 'report_option_', '') AS num
748 WHERE optionname like 'report_option_%'
749 AND part_pkg_option.pkgpart = $table.pkgpart
753 if ( $num eq 'multiple' ) {
754 $comparison = "(SELECT COUNT(*) FROM ($subselect) AS x) > 1";
757 my @num = split(/\s*,\s*/, $num);
759 #$comparison = "(SELECT COALESCE(string_agg(num, ','), '') FROM ( #Pg 9-ism
760 $comparison = "(SELECT COALESCE(array_to_string(array_agg(num), ','), '')
761 FROM ($subselect) AS x
762 ) = '". join(',', grep $_, @num). "'";
764 $comparison = "( $comparison OR NOT EXISTS ($subselect) )"
768 if ( $use_override ) {
769 # then also allow the non-override package to match
770 $comparison = "( $comparison OR " . $self->with_report_option($num) . ")";
776 my( $self, $sql ) = ( shift, shift );
777 my $sth = dbh->prepare($sql) or die dbh->errstr;
778 warn "FS::Report::Table\n$sql\n" if $DEBUG;
780 or die "Unexpected error executing statement $sql: ". $sth->errstr;
781 $sth->fetchrow_arrayref->[0] || 0;
790 =item init_projection
792 Sets up for future projection of all observables on the report. Currently
793 this is limited to 'cust_bill_pkg'.
797 sub init_projection {
798 # this is weird special case stuff--some redesign may be needed
799 # to use it for anything else
802 if ( driver_name ne 'Pg' ) {
803 # also database-specific for now
804 die "projection reports not supported on this platform";
807 my %items = map {$_ => 1} @{ $self->{items} };
808 if ($items{'cust_bill_pkg'}) {
812 # could use TEMPORARY TABLE but we're already transaction-protected
813 'DROP TABLE IF EXISTS v_cust_bill_pkg',
814 'CREATE TABLE v_cust_bill_pkg ' .
815 '(LIKE cust_bill_pkg,
816 usage numeric(10,2), _date integer, expire integer)',
817 # XXX this should be smart enough to take only the ones with
818 # sdate/edate overlapping the ROI, for performance
819 "INSERT INTO v_cust_bill_pkg (
820 SELECT cust_bill_pkg.*,
821 (SELECT COALESCE(SUM(amount),0) FROM cust_bill_pkg_detail
822 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum),
825 FROM cust_bill_pkg $cust_bill_pkg_join
828 foreach my $sql (@sql) {
829 warn "[init_projection] $sql\n" if $DEBUG;
830 $dbh->do($sql) or die $dbh->errstr;
835 =item extend_projection START END
837 Generates data for the next period of projection. This will be called
838 for sequential periods where the END of one equals the START of the next
843 sub extend_projection {
845 my ($speriod, $eperiod) = @_;
846 my %items = map {$_ => 1} @{ $self->{items} };
847 if ($items{'cust_bill_pkg'}) {
849 # Find all line items that end after the start of the period (and have
850 # recurring fees, and don't expire before they end). Choose the latest
851 # one for each package. If it ends before the end of the period, copy
852 # it forward by one billing period.
853 # Repeat this until the latest line item for each package no longer ends
854 # within the period. This is certain to happen in finitely many
855 # iterations as long as freq > 0.
856 # - Pg only, obviously.
857 # - Gives bad results if freq_override is used.
858 my @fields = ( FS::cust_bill_pkg->fields, qw( usage _date expire ) );
859 my $insert_fields = join(',', @fields);
860 my $add_freq = sub { # emulate FS::part_pkg::add_freq
862 "EXTRACT( EPOCH FROM TO_TIMESTAMP($field) + (CASE WHEN freq ~ E'\\\\D' ".
863 "THEN freq ELSE freq || 'mon' END)::INTERVAL) AS $field";
867 $_ = $add_freq->('edate');
869 elsif ($_ eq 'sdate') {
870 $_ = 'edate AS sdate'
872 elsif ($_ eq 'setup') {
873 $_ = '0 AS setup' #because recurring only
875 elsif ($_ eq '_date') {
876 $_ = $add_freq->('_date');
879 my $select_fields = join(',', @fields);
882 # Subquery here because we need to DISTINCT the whole set, select the
883 # latest charge per pkgnum, and _then_ check edate < $eperiod
884 # and edate < expire.
885 "INSERT INTO v_cust_bill_pkg ($insert_fields)
886 SELECT $select_fields FROM (
887 SELECT DISTINCT ON (pkgnum) * FROM v_cust_bill_pkg
888 WHERE edate >= $speriod
892 ORDER BY pkgnum, edate DESC
894 WHERE edate < $eperiod AND (edate < expire OR expire IS NULL)";
897 warn "[extend_projection] $sql\n" if $DEBUG;
898 $rows = $dbh->do($sql) or die $dbh->errstr;
899 warn "[extend_projection] $rows rows\n" if $DEBUG;
910 L<FS::Report::Table::Monthly>, reports in the web interface.