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:
37 - cust_classnum: limit to this customer class
38 - pkg_classnum: limit to customers with a package of this class. If this is
39 an arrayref, it's an ANY match.
40 - refnum: limit to this advertising source
41 - indirect: boolean; limit to customers that have a referral_custnum that
42 matches the advertising source
47 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
48 my @where = ( $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
49 'cust_main.signupdate')
52 if ( $opt{'indirect'} ) {
53 $join = " JOIN cust_main AS referring_cust_main".
54 " ON (cust_main.referral_custnum = referring_cust_main.custnum)";
56 if ( $opt{'refnum'} ) {
57 push @where, "referring_cust_main.refnum = ".$opt{'refnum'};
60 elsif ( $opt{'refnum'} ) {
61 push @where, "refnum = ".$opt{'refnum'};
64 push @where, $self->with_cust_classnum(%opt);
65 if ( $opt{'pkg_classnum'} ) {
66 my $classnum = $opt{'pkg_classnum'};
67 $classnum = [ $classnum ] unless ref $classnum;
68 @$classnum = grep /^\d+$/, @$classnum;
70 my $in = 'IN ('. join(',', @$classnum). ')';
72 "EXISTS(SELECT 1 FROM cust_pkg JOIN part_pkg USING (pkgpart) ".
73 "WHERE cust_pkg.custnum = cust_main.custnum ".
74 "AND part_pkg.classnum $in".
80 "SELECT COUNT(*) FROM cust_main $join WHERE ".join(' AND ', @where)
84 =item invoiced: The total amount charged on all invoices.
88 sub invoiced { #invoiced
89 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
91 my $sql = 'SELECT SUM(cust_bill.charged) FROM cust_bill';
92 if ( $opt{'setuprecur'} ) {
94 FS::cust_bill_pkg->charged_sql($speriod, $eperiod, %opt).
95 ') FROM cust_bill_pkg JOIN cust_bill USING (invnum)';
100 LEFT JOIN cust_main USING ( custnum )
101 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
102 $self->for_opts(%opt)
107 =item netsales: invoiced - netcredits
111 sub netsales { #net sales
112 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
114 $self->invoiced( $speriod, $eperiod, $agentnum, %opt)
115 - $self->netcredits($speriod, $eperiod, $agentnum, %opt);
118 =item cashflow: payments - refunds
123 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
125 $self->payments($speriod, $eperiod, $agentnum, %opt)
126 - $self->refunds( $speriod, $eperiod, $agentnum, %opt);
129 =item netcashflow: payments - netrefunds
134 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
136 $self->receipts( $speriod, $eperiod, $agentnum, %opt)
137 - $self->netrefunds( $speriod, $eperiod, $agentnum, %opt);
140 =item payments: The sum of payments received in the period.
145 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
149 LEFT JOIN cust_main USING ( custnum )
150 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
151 $self->for_opts(%opt)
155 =item credits: The sum of credits issued in the period.
160 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
162 SELECT SUM(cust_credit.amount)
164 LEFT JOIN cust_main USING ( custnum )
165 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
166 $self->for_opts(%opt)
170 =item refunds: The sum of refunds paid in the period.
175 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
179 LEFT JOIN cust_main USING ( custnum )
180 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum).
181 $self->for_opts(%opt)
185 =item netcredits: The sum of credit applications to invoices in the period.
190 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
192 my $sql = 'SELECT SUM(cust_credit_bill.amount) FROM cust_credit_bill';
193 if ( $opt{'setuprecur'} ) {
194 $sql = 'SELECT SUM('.
195 FS::cust_bill_pkg->credited_sql($speriod, $eperiod, %opt).
196 ') FROM cust_bill_pkg';
201 LEFT JOIN cust_bill USING ( invnum )
202 LEFT JOIN cust_main USING ( custnum )
203 WHERE ". $self->in_time_period_and_agent( $speriod,
208 $self->for_opts(%opt)
212 =item receipts: The sum of payment applications to invoices in the period.
216 sub receipts { #net payments
217 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
219 my $sql = 'SELECT SUM(cust_bill_pay.amount) FROM cust_bill_pay';
220 if ( $opt{'setuprecur'} ) {
221 $sql = 'SELECT SUM('.
222 FS::cust_bill_pkg->paid_sql($speriod, $eperiod, %opt).
223 ') FROM cust_bill_pkg';
228 LEFT JOIN cust_bill USING ( invnum )
229 LEFT JOIN cust_main USING ( custnum )
230 WHERE ". $self->in_time_period_and_agent( $speriod,
235 $self->for_opts(%opt)
239 =item netrefunds: The sum of refund applications to credits in the period.
244 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
246 SELECT SUM(cust_credit_refund.amount)
247 FROM cust_credit_refund
248 LEFT JOIN cust_credit USING ( crednum )
249 LEFT JOIN cust_main USING ( custnum )
250 WHERE ". $self->in_time_period_and_agent( $speriod,
255 $self->for_opts(%opt)
261 #these should be auto-generated or $AUTOLOADed or something
263 my( $self, $speriod, $eperiod, $agentnum ) = @_;
264 $speriod = $self->_subtract_11mo($speriod);
265 $self->invoiced($speriod, $eperiod, $agentnum);
269 my( $self, $speriod, $eperiod, $agentnum ) = @_;
270 $speriod = $self->_subtract_11mo($speriod);
271 $self->netsales($speriod, $eperiod, $agentnum);
275 my( $self, $speriod, $eperiod, $agentnum ) = @_;
276 $speriod = $self->_subtract_11mo($speriod);
277 $self->receipts($speriod, $eperiod, $agentnum);
281 my( $self, $speriod, $eperiod, $agentnum ) = @_;
282 $speriod = $self->_subtract_11mo($speriod);
283 $self->payments($speriod, $eperiod, $agentnum);
287 my( $self, $speriod, $eperiod, $agentnum ) = @_;
288 $speriod = $self->_subtract_11mo($speriod);
289 $self->credits($speriod, $eperiod, $agentnum);
292 sub netcredits_12mo {
293 my( $self, $speriod, $eperiod, $agentnum ) = @_;
294 $speriod = $self->_subtract_11mo($speriod);
295 $self->netcredits($speriod, $eperiod, $agentnum);
299 my( $self, $speriod, $eperiod, $agentnum ) = @_;
300 $speriod = $self->_subtract_11mo($speriod);
301 $self->cashflow($speriod, $eperiod, $agentnum);
304 sub netcashflow_12mo {
305 my( $self, $speriod, $eperiod, $agentnum ) = @_;
306 $speriod = $self->_subtract_11mo($speriod);
307 $self->cashflow($speriod, $eperiod, $agentnum);
311 my( $self, $speriod, $eperiod, $agentnum ) = @_;
312 $speriod = $self->_subtract_11mo($speriod);
313 $self->refunds($speriod, $eperiod, $agentnum);
316 sub netrefunds_12mo {
317 my( $self, $speriod, $eperiod, $agentnum ) = @_;
318 $speriod = $self->_subtract_11mo($speriod);
319 $self->netrefunds($speriod, $eperiod, $agentnum);
323 #not being too bad with the false laziness
325 my($self, $time) = @_;
326 my ($sec,$min,$hour,$mday,$mon,$year) = (localtime($time) )[0,1,2,3,4,5];
328 if ( $mon < 0 ) { $mon+=12; $year--; }
329 timelocal($sec,$min,$hour,$mday,$mon,$year);
332 =item cust_pkg_setup_cost: The total setup costs of packages setup in the period
334 'classnum': limit to this package class.
338 sub cust_pkg_setup_cost {
339 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
342 if ( $opt{'classnum'} ne '' ) {
343 my $classnums = $opt{'classnum'};
344 $classnums = [ $classnums ] if !ref($classnums);
345 @$classnums = grep /^\d+$/, @$classnums;
346 $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
350 $agentnum ||= $opt{'agentnum'};
352 my $total_sql = " SELECT SUM(part_pkg.setup_cost) ";
353 $total_sql .= " FROM cust_pkg
354 LEFT JOIN cust_main USING ( custnum )
355 LEFT JOIN part_pkg USING ( pkgpart )
358 AND ".$self->in_time_period_and_agent(
359 $speriod, $eperiod, $agentnum, 'cust_pkg.setup');
360 return $self->scalar_sql($total_sql);
363 =item cust_pkg_recur_cust: the total recur costs of packages in the period
365 'classnum': limit to this package class.
369 sub cust_pkg_recur_cost {
370 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
373 if ( $opt{'classnum'} ne '' ) {
374 my $classnums = $opt{'classnum'};
375 $classnums = [ $classnums ] if !ref($classnums);
376 @$classnums = grep /^\d+$/, @$classnums;
377 $where .= ' AND COALESCE(part_pkg.classnum,0) IN ('. join(',', @$classnums).
381 $agentnum ||= $opt{'agentnum'};
382 # duplication of in_time_period_and_agent
383 # because we do it a little differently here
384 $where .= " AND cust_main.agentnum = $agentnum" if $agentnum;
386 $FS::CurrentUser::CurrentUser->agentnums_sql('table' => 'cust_main');
388 my $total_sql = " SELECT SUM(part_pkg.recur_cost) ";
389 $total_sql .= " FROM cust_pkg
390 LEFT JOIN cust_main USING ( custnum )
391 LEFT JOIN part_pkg USING ( pkgpart )
394 AND cust_pkg.setup < $eperiod
395 AND (cust_pkg.cancel > $speriod OR cust_pkg.cancel IS NULL)
397 return $self->scalar_sql($total_sql);
400 =item cust_bill_pkg: the total package charges on invoice line items.
402 'charges': limit the type of charges included (setup, recur, usage).
403 Should be a string containing one or more of 'S', 'R', or 'U'; if
404 unspecified, defaults to all three.
406 'classnum': limit to this package class.
408 'use_override': for line items generated by an add-on package, use the class
409 of the add-on rather than the base package.
411 'average_per_cust_pkg': divide the result by the number of distinct packages.
413 'distribute': for non-monthly recurring charges, ignore the invoice
414 date. Instead, consider the line item's starting/ending dates. Determine
415 the fraction of the line item duration that falls within the specified
416 interval and return that fraction of the recurring charges. This is
417 somewhat experimental.
419 'project': enable if this is a projected period. This is very experimental.
425 my( $speriod, $eperiod, $agentnum, %opt ) = @_;
427 my %charges = map {$_=>1} split('', $opt{'charges'} || 'SRU');
430 $sum += $self->cust_bill_pkg_setup(@_) if $charges{S};
431 $sum += $self->cust_bill_pkg_recur(@_) if $charges{R};
432 $sum += $self->cust_bill_pkg_detail(@_) if $charges{U};
434 if ($opt{'average_per_cust_pkg'}) {
435 my $count = $self->cust_bill_pkg_count_pkgnum(@_);
436 return '' if $count == 0;
437 $sum = sprintf('%.2f', $sum / $count);
442 my $cust_bill_pkg_join = '
443 LEFT JOIN cust_bill USING ( invnum )
444 LEFT JOIN cust_main USING ( custnum )
445 LEFT JOIN cust_pkg USING ( pkgnum )
446 LEFT JOIN part_pkg USING ( pkgpart )
447 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
448 LEFT JOIN part_fee USING ( feepart )';
450 sub cust_bill_pkg_setup {
452 my ($speriod, $eperiod, $agentnum, %opt) = @_;
453 # no projecting setup fees--use real invoices only
454 # but evaluate this anyway, because the design of projection is that
455 # if there are somehow real setup fees in the future, we want to count
458 $agentnum ||= $opt{'agentnum'};
461 '(pkgnum != 0 OR feepart IS NOT NULL)',
462 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
463 $self->with_report_option(%opt),
464 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
467 # yuck, false laziness
468 push @where, "cust_main.refnum = ". $opt{'refnum'} if $opt{'refnum'};
470 push @where, $self->with_cust_classnum(%opt);
472 my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg.setup),0)
475 WHERE " . join(' AND ', grep $_, @where);
477 $self->scalar_sql($total_sql);
480 sub _cust_bill_pkg_recurring {
481 # returns the FROM/WHERE part of the statement to query all recurring
482 # line items in the period
484 my ($speriod, $eperiod, $agentnum, %opt) = @_;
486 $agentnum ||= $opt{'agentnum'};
487 my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
490 '(pkgnum != 0 OR feepart IS NOT NULL)',
491 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
492 $self->with_report_option(%opt),
495 push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
497 push @where, $self->with_cust_classnum(%opt);
499 if ( $opt{'distribute'} ) {
500 $where[0] = 'pkgnum != 0'; # specifically exclude fees
501 push @where, "cust_main.agentnum = $agentnum" if $agentnum;
503 "$cust_bill_pkg.sdate < $eperiod",
504 "$cust_bill_pkg.edate >= $speriod",
508 # we don't want to have to create v_cust_bill
509 my $_date = $opt{'project'} ? 'v_cust_bill_pkg._date' : 'cust_bill._date';
511 $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date);
517 WHERE ".join(' AND ', grep $_, @where);
521 sub cust_bill_pkg_recur {
523 my ($speriod, $eperiod, $agentnum, %opt) = @_;
525 # subtract all usage from the line item regardless of date
527 if ( $opt{'project'} ) {
528 $item_usage = 'usage'; #already calculated
531 $item_usage = '( SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
532 FROM cust_bill_pkg_detail
533 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )';
536 my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg';
538 my $recur_fraction = '';
539 if ($opt{'distribute'}) {
540 # the fraction of edate - sdate that's within [speriod, eperiod]
541 $recur_fraction = " *
542 CAST(LEAST($eperiod, $cust_bill_pkg.edate) -
543 GREATEST($speriod, $cust_bill_pkg.sdate) AS DECIMAL) /
544 ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)";
548 "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)" .
549 $self->_cust_bill_pkg_recurring(@_);
551 $self->scalar_sql($total_sql);
554 sub cust_bill_pkg_count_pkgnum {
555 # for ARPU calculation
557 my $total_sql = 'SELECT COUNT(DISTINCT pkgnum) '.
558 $self->_cust_bill_pkg_recurring(@_);
560 $self->scalar_sql($total_sql);
563 =item cust_bill_pkg_detail: the total usage charges in detail lines.
565 Arguments as for C<cust_bill_pkg>, plus:
567 'usageclass': limit to this usage class number.
571 sub cust_bill_pkg_detail {
572 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
575 ( "(cust_bill_pkg.pkgnum != 0 OR cust_bill_pkg.feepart IS NOT NULL)" );
577 push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'};
579 push @where, $self->with_cust_classnum(%opt);
581 $agentnum ||= $opt{'agentnum'};
584 $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
585 $self->with_usageclass($opt{'usageclass'}),
586 $self->with_report_option(%opt),
589 if ( $opt{'distribute'} ) {
591 $where[0] = 'cust_bill_pkg.pkgnum != 0';
592 # and limit according to the usage time, not the billing date
593 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
594 'cust_bill_pkg_detail.startdate'
598 push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum,
603 my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) ";
606 " FROM cust_bill_pkg_detail
607 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
608 LEFT JOIN cust_bill ON cust_bill_pkg.invnum = cust_bill.invnum
609 LEFT JOIN cust_main USING ( custnum )
610 LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum
611 LEFT JOIN part_pkg USING ( pkgpart )
612 LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
613 LEFT JOIN part_fee USING ( feepart )
614 WHERE ".join( ' AND ', grep $_, @where );
616 $self->scalar_sql($total_sql);
620 sub cust_bill_pkg_discount {
621 my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
623 #need to do this the new multi-classnum way if it gets re-enabled
625 #my $comparison = '';
626 #if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
628 # $comparison = "IS NULL";
630 # $comparison = "= $1";
633 # if ( $opt{'use_override'} ) {
635 # part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
636 # override.classnum $comparison AND pkgpart_override IS NOT NULL
639 # $where = "part_pkg.classnum $comparison";
643 $agentnum ||= $opt{'agentnum'};
646 " SELECT COALESCE( SUM( cust_bill_pkg_discount.amount ), 0 ) ";
649 " FROM cust_bill_pkg_discount
650 LEFT JOIN cust_bill_pkg USING ( billpkgnum )
651 LEFT JOIN cust_bill USING ( invnum )
652 LEFT JOIN cust_main USING ( custnum )
653 WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum);
654 # LEFT JOIN cust_pkg_discount USING ( pkgdiscountnum )
655 # LEFT JOIN discount USING ( discountnum )
656 # LEFT JOIN cust_pkg USING ( pkgnum )
657 # LEFT JOIN part_pkg USING ( pkgpart )
658 # LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart
660 return $self->scalar_sql($total_sql);
664 sub setup_pkg { shift->pkg_field( 'setup', @_ ); }
665 sub susp_pkg { shift->pkg_field( 'susp', @_ ); }
666 sub cancel_pkg { shift->pkg_field( 'cancel', @_ ); }
669 my( $self, $field, $speriod, $eperiod, $agentnum ) = @_;
671 SELECT COUNT(*) FROM cust_pkg
672 LEFT JOIN cust_main USING ( custnum )
673 WHERE ". $self->in_time_period_and_agent( $speriod,
682 #this is going to be harder..
684 # my( $self, $speriod, $eperiod, $agentnum ) = @_;
685 # $self->scalar_sql("
686 # SELECT COUNT(*) FROM h_cust_pkg
691 sub in_time_period_and_agent {
692 my( $self, $speriod, $eperiod, $agentnum ) = splice(@_, 0, 4);
693 my $col = @_ ? shift() : '_date';
695 my $sql = "$col >= $speriod AND $col < $eperiod";
698 $sql .= " AND cust_main.agentnum = $agentnum"
701 #agent virtualization
703 $FS::CurrentUser::CurrentUser->agentnums_sql( 'table'=>'cust_main' );
709 my ( $self, %opt ) = @_;
711 if ( $opt{'custnum'} =~ /^(\d+)$/ ) {
712 $sql .= " and custnum = $1 ";
714 if ( $opt{'refnum'} =~ /^(\d+)$/ ) {
715 $sql .= " and refnum = $1 ";
717 if ( my $where = $self->with_cust_classnum(%opt) ) {
718 $sql .= " and $where";
725 my ($self, $classnum, $use_override) = @_;
726 return '' if $classnum eq '';
728 $classnum = [ $classnum ] if !ref($classnum);
729 @$classnum = grep /^\d+$/, @$classnum;
730 my $in = 'IN ('. join(',', @$classnum). ')';
733 ( COALESCE(part_pkg.classnum, 0) $in AND pkgpart_override IS NULL)
734 OR ( COALESCE(part_fee.classnum, 0) $in AND feepart IS NOT NULL )";
735 if ( $use_override ) {
737 OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )";
742 sub with_usageclass {
744 my ($classnum, $use_override) = @_;
745 return '' unless $classnum =~ /^\d+$/;
747 if ( $classnum == 0 ) {
748 $comparison = 'IS NULL';
751 $comparison = "= $classnum";
753 return "cust_bill_pkg_detail.classnum $comparison";
756 sub with_report_option {
757 my ($self, %opt) = @_;
759 # - report_optionnum: a comma-separated list of numbers. Zero means to
760 # include packages with _no_ report classes.
761 # - not_report_optionnum: a comma-separated list. Packages that have
762 # any of these report options will be excluded from the result.
764 # - use_override: also matches line items that are add-ons to a package
765 # matching the report class.
766 # - all_report_options: returns only packages that have ALL of the
767 # report classes listed in $num. Otherwise, will return packages that
768 # have ANY of those classes.
770 my @num = ref($opt{'report_optionnum'})
771 ? @{ $opt{'report_optionnum'} }
772 : split(/\s*,\s*/, $opt{'report_optionnum'});
773 my @not_num = ref($opt{'not_report_optionnum'})
774 ? @{ $opt{'not_report_optionnum'} }
775 : split(/\s*,\s*/, $opt{'not_report_optionnum'});
777 $null = 1 if ( grep {$_ == 0} @num );
778 @num = grep {$_ > 0} @num;
779 @not_num = grep {$_ > 0} @not_num;
782 my $table = $opt{'use_override'} ? 'override' : 'part_pkg';
784 if ( $opt{'all_report_options'} ) {
785 if ( @num and $null ) {
786 return 'false'; # mutually exclusive criteria, so just bail out
790 my @where_num = map {
791 "EXISTS(SELECT 1 FROM part_pkg_option ".
792 "WHERE optionname = 'report_option_$_' ".
793 "AND part_pkg_option.pkgpart = $table.pkgpart)"
796 push @where_num, "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
797 "WHERE optionname LIKE 'report_option_%' ".
798 "AND part_pkg_option.pkgpart = $table.pkgpart)";
800 my @where_not_num = map {
801 "NOT EXISTS(SELECT 1 FROM part_pkg_option ".
802 "WHERE optionname = 'report_option_$_' ".
803 "AND part_pkg_option.pkgpart = $table.pkgpart)"
808 push @where, '( '.join($op, @where_num).' )';
810 if (@where_not_num) {
811 push @where, '( '.join(' AND ', @where_not_num).' )';
815 # this messes up totals
816 #if ( $opt{'use_override'} ) {
817 # # then also allow the non-override package to match
818 # delete $opt{'use_override'};
819 # $comparison = "( $comparison OR " . $self->with_report_option(%opt) . ")";
824 sub with_cust_classnum {
825 my ($self, %opt) = @_;
826 if ( $opt{'cust_classnum'} ) {
827 my $classnums = $opt{'cust_classnum'};
828 $classnums = [ $classnums ] if !ref($classnums);
829 @$classnums = grep /^\d+$/, @$classnums;
830 return 'cust_main.classnum in('. join(',',@$classnums) .')'
838 my( $self, $sql ) = ( shift, shift );
839 my $sth = dbh->prepare($sql) or die dbh->errstr;
840 warn "FS::Report::Table\n$sql\n" if $DEBUG;
842 or die "Unexpected error executing statement $sql: ". $sth->errstr;
843 $sth->fetchrow_arrayref->[0] || 0;
852 =item init_projection
854 Sets up for future projection of all observables on the report. Currently
855 this is limited to 'cust_bill_pkg'.
859 sub init_projection {
860 # this is weird special case stuff--some redesign may be needed
861 # to use it for anything else
864 if ( driver_name ne 'Pg' ) {
865 # also database-specific for now
866 die "projection reports not supported on this platform";
869 my %items = map {$_ => 1} @{ $self->{items} };
870 if ($items{'cust_bill_pkg'}) {
874 # could use TEMPORARY TABLE but we're already transaction-protected
875 'DROP TABLE IF EXISTS v_cust_bill_pkg',
876 'CREATE TABLE v_cust_bill_pkg ' .
877 '(LIKE cust_bill_pkg,
878 usage numeric(10,2), _date integer, expire integer)',
879 # XXX this should be smart enough to take only the ones with
880 # sdate/edate overlapping the ROI, for performance
881 "INSERT INTO v_cust_bill_pkg (
882 SELECT cust_bill_pkg.*,
883 (SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0)
884 FROM cust_bill_pkg_detail
885 WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum),
888 FROM cust_bill_pkg $cust_bill_pkg_join
891 foreach my $sql (@sql) {
892 warn "[init_projection] $sql\n" if $DEBUG;
893 $dbh->do($sql) or die $dbh->errstr;
898 =item extend_projection START END
900 Generates data for the next period of projection. This will be called
901 for sequential periods where the END of one equals the START of the next
906 sub extend_projection {
908 my ($speriod, $eperiod) = @_;
909 my %items = map {$_ => 1} @{ $self->{items} };
910 if ($items{'cust_bill_pkg'}) {
912 # Find all line items that end after the start of the period (and have
913 # recurring fees, and don't expire before they end). Choose the latest
914 # one for each package. If it ends before the end of the period, copy
915 # it forward by one billing period.
916 # Repeat this until the latest line item for each package no longer ends
917 # within the period. This is certain to happen in finitely many
918 # iterations as long as freq > 0.
919 # - Pg only, obviously.
920 # - Gives bad results if freq_override is used.
921 my @fields = ( FS::cust_bill_pkg->fields, qw( usage _date expire ) );
922 my $insert_fields = join(',', @fields);
923 my $add_freq = sub { # emulate FS::part_pkg::add_freq
925 "EXTRACT( EPOCH FROM TO_TIMESTAMP($field) + (CASE WHEN freq ~ E'\\\\D' ".
926 "THEN freq ELSE freq || 'mon' END)::INTERVAL) AS $field";
930 $_ = $add_freq->('edate');
932 elsif ($_ eq 'sdate') {
933 $_ = 'edate AS sdate'
935 elsif ($_ eq 'setup') {
936 $_ = '0 AS setup' #because recurring only
938 elsif ($_ eq '_date') {
939 $_ = $add_freq->('_date');
942 my $select_fields = join(',', @fields);
945 # Subquery here because we need to DISTINCT the whole set, select the
946 # latest charge per pkgnum, and _then_ check edate < $eperiod
947 # and edate < expire.
948 "INSERT INTO v_cust_bill_pkg ($insert_fields)
949 SELECT $select_fields FROM (
950 SELECT DISTINCT ON (pkgnum) * FROM v_cust_bill_pkg
951 WHERE edate >= $speriod
955 ORDER BY pkgnum, edate DESC
957 WHERE edate < $eperiod AND (edate < expire OR expire IS NULL)";
960 warn "[extend_projection] $sql\n" if $DEBUG;
961 $rows = $dbh->do($sql) or die $dbh->errstr;
962 warn "[extend_projection] $rows rows\n" if $DEBUG;
973 L<FS::Report::Table::Monthly>, reports in the web interface.