X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FTable.pm;h=8c45ae1456e1a0428cc8616c80144bdb8034e01f;hb=af167dc766fad944b32ca7dc431b2549943b94dd;hp=7f593846ebe900dfd2d7c945da96c1e5d94d040e;hpb=5b670255328fbe875196e16bc8dfc57771753e90;p=freeside.git diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index 7f593846e..8c45ae145 100644 --- a/FS/FS/Report/Table.pm +++ b/FS/FS/Report/Table.pm @@ -32,9 +32,14 @@ options in %opt. =over 4 -=item signups: The number of customers signed up. Options are "refnum" -(limit by advertising source) and "indirect" (boolean, tells us to limit -to customers that have a referral_custnum that matches the advertising source). +=item signups: The number of customers signed up. Options are: + +- cust_classnum: limit to this customer class +- pkg_classnum: limit to customers with a package of this class. If this is + an arrayref, it's an ANY match. +- refnum: limit to this advertising source +- indirect: boolean; limit to customers that have a referral_custnum that + matches the advertising source =cut @@ -57,6 +62,19 @@ sub signups { } push @where, $self->with_cust_classnum(%opt); + if ( $opt{'pkg_classnum'} ) { + my $classnum = $opt{'pkg_classnum'}; + $classnum = [ $classnum ] unless ref $classnum; + @$classnum = grep /^\d+$/, @$classnum; + if (@$classnum) { + my $in = 'IN ('. join(',', @$classnum). ')'; + push @where, + "EXISTS(SELECT 1 FROM cust_pkg JOIN part_pkg USING (pkgpart) ". + "WHERE cust_pkg.custnum = cust_main.custnum ". + "AND part_pkg.classnum $in". + ")"; + } + } $self->scalar_sql( "SELECT COUNT(*) FROM cust_main $join WHERE ".join(' AND ', @where) @@ -141,7 +159,7 @@ sub payments { sub credits { my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; $self->scalar_sql(" - SELECT SUM(amount) + SELECT SUM(cust_credit.amount) FROM cust_credit LEFT JOIN cust_main USING ( custnum ) WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum). @@ -390,9 +408,6 @@ unspecified, defaults to all three. 'use_override': for line items generated by an add-on package, use the class of the add-on rather than the base package. -'freq': limit to packages with this frequency. Currently uses the part_pkg -frequency, so term discounted packages may give odd results. - 'distribute': for non-monthly recurring charges, ignore the invoice date. Instead, consider the line item's starting/ending dates. Determine the fraction of the line item duration that falls within the specified @@ -421,7 +436,8 @@ my $cust_bill_pkg_join = ' LEFT JOIN cust_main USING ( custnum ) LEFT JOIN cust_pkg USING ( pkgnum ) LEFT JOIN part_pkg USING ( pkgpart ) - LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart'; + LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart + LEFT JOIN part_fee USING ( feepart )'; sub cust_bill_pkg_setup { my $self = shift; @@ -434,7 +450,7 @@ sub cust_bill_pkg_setup { $agentnum ||= $opt{'agentnum'}; my @where = ( - 'pkgnum != 0', + '(pkgnum != 0 OR feepart IS NOT NULL)', $self->with_classnum($opt{'classnum'}, $opt{'use_override'}), $self->with_report_option(%opt), $self->in_time_period_and_agent($speriod, $eperiod, $agentnum), @@ -461,7 +477,7 @@ sub cust_bill_pkg_recur { my $cust_bill_pkg = $opt{'project'} ? 'v_cust_bill_pkg' : 'cust_bill_pkg'; my @where = ( - 'pkgnum != 0', + '(pkgnum != 0 OR feepart IS NOT NULL)', $self->with_classnum($opt{'classnum'}, $opt{'use_override'}), $self->with_report_option(%opt), ); @@ -476,13 +492,14 @@ sub cust_bill_pkg_recur { $item_usage = 'usage'; #already calculated } else { - $item_usage = '( SELECT COALESCE(SUM(amount),0) + $item_usage = '( SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0) FROM cust_bill_pkg_detail WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum )'; } my $recur_fraction = ''; if ( $opt{'distribute'} ) { + $where[0] = 'pkgnum != 0'; # specifically exclude fees push @where, "cust_main.agentnum = $agentnum" if $agentnum; push @where, "$cust_bill_pkg.sdate < $eperiod", @@ -521,7 +538,8 @@ Arguments as for C, plus: sub cust_bill_pkg_detail { my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; - my @where = ( "cust_bill_pkg.pkgnum != 0" ); + my @where = + ( "(cust_bill_pkg.pkgnum != 0 OR cust_bill_pkg.feepart IS NOT NULL)" ); push @where, 'cust_main.refnum = '. $opt{'refnum'} if $opt{'refnum'}; @@ -536,7 +554,9 @@ sub cust_bill_pkg_detail { ; if ( $opt{'distribute'} ) { - # then limit according to the usage time, not the billing date + # exclude fees + $where[0] = 'cust_bill_pkg.pkgnum != 0'; + # and limit according to the usage time, not the billing date push @where, $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, 'cust_bill_pkg_detail.startdate' ); @@ -547,7 +567,7 @@ sub cust_bill_pkg_detail { ); } - my $total_sql = " SELECT SUM(amount) "; + my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) "; $total_sql .= " / CASE COUNT(cust_pkg.*) WHEN 0 THEN 1 ELSE COUNT(cust_pkg.*) END " @@ -561,6 +581,7 @@ sub cust_bill_pkg_detail { LEFT JOIN cust_pkg ON cust_bill_pkg.pkgnum = cust_pkg.pkgnum LEFT JOIN part_pkg USING ( pkgpart ) LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart + LEFT JOIN part_fee USING ( feepart ) WHERE ".join( ' AND ', grep $_, @where ); $self->scalar_sql($total_sql); @@ -683,14 +704,14 @@ sub with_classnum { @$classnum = grep /^\d+$/, @$classnum; my $in = 'IN ('. join(',', @$classnum). ')'; - if ( $use_override ) { - "( + my $expr = " ( COALESCE(part_pkg.classnum, 0) $in AND pkgpart_override IS NULL) - OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL ) - )"; - } else { - "COALESCE(part_pkg.classnum, 0) $in"; + OR ( COALESCE(part_fee.classnum, 0) $in AND feepart IS NOT NULL )"; + if ( $use_override ) { + $expr .= " + OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL )"; } + "( $expr )"; } sub with_usageclass { @@ -834,7 +855,8 @@ sub init_projection { # sdate/edate overlapping the ROI, for performance "INSERT INTO v_cust_bill_pkg ( SELECT cust_bill_pkg.*, - (SELECT COALESCE(SUM(amount),0) FROM cust_bill_pkg_detail + (SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0) + FROM cust_bill_pkg_detail WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum), cust_bill._date, cust_pkg.expire