X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FTable.pm;h=e3e0854ecef4cfdc81af30865a2389497ca76ef5;hb=f654e068b6e7be55bdbd749293c1bda7737cf870;hp=934287a153be4897ace16b6187334698e98d8a30;hpb=af501d08494e5930d53da1cb1f02e65e2a6ebd07;p=freeside.git diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index 934287a15..e3e0854ec 100644 --- a/FS/FS/Report/Table.pm +++ b/FS/FS/Report/Table.pm @@ -229,7 +229,8 @@ sub receipts { #net payments my $sql = 'SELECT SUM(cust_bill_pay.amount) FROM cust_bill_pay'; if ( $opt{'setuprecur'} ) { $sql = 'SELECT SUM('. - FS::cust_bill_pkg->paid_sql($speriod, $eperiod, %opt). + #in practice, but not appearance, paid_sql accepts end before start + FS::cust_bill_pkg->paid_sql($eperiod, $speriod, %opt). ') FROM cust_bill_pkg'; } @@ -266,6 +267,81 @@ sub netrefunds { ); } +=item discounted: The sum of discounts on invoices in the period. + +=cut + +sub discounted { + my( $self, $speriod, $eperiod, $agentnum, %opt) = @_; + + my $sql = 'SELECT SUM('; + if ($opt{'setuprecur'}) { + # (This isn't exact but it works in most cases.) + # When splitting into setup/recur values, + # if the discount is allowed to apply to setup fees (discount.setup = 'Y') + # then split it between the "setup" and "recurring" rows in proportion to + # the "unitsetup" and "unitrecur" fields of the line item. + $sql .= < 0) + OR (COALESCE(cust_bill_pkg.unitrecur,0) > 0)) + THEN +EOF + if ($opt{'setuprecur'} eq 'setup') { + $sql .= ' (COALESCE(cust_bill_pkg.unitsetup,0)'; + } elsif ($opt{'setuprecur'} eq 'recur') { + $sql .= ' (COALESCE(cust_bill_pkg.unitrecur,0)'; + } else { + die 'Unrecognized value for setuprecur'; + } + $sql .= ' / (COALESCE(cust_bill_pkg.unitsetup,0) + COALESCE(cust_bill_pkg.unitrecur,0)))'; + $sql .= " * cust_bill_pkg_discount.amount\n"; + # Otherwise, show it all as "recurring" + if ($opt{'setuprecur'} eq 'setup') { + $sql .= " ELSE 0\n"; + } elsif ($opt{'setuprecur'} eq 'recur') { + $sql .= " ELSE cust_bill_pkg_discount.amount\n"; + } + $sql .= "END\n"; + } else { + # simple case, no setuprecur + $sql .= "cust_bill_pkg_discount.amount\n"; + } + $sql .= <scalar_sql( + $sql + . 'WHERE ' + . $self->in_time_period_and_agent( $speriod, + $eperiod, + $agentnum, + 'cust_bill._date' + ) + . $self->for_opts(%opt) + ); +} + +=item gross: invoiced + discounted + +=cut + +sub gross { + my( $self, $speriod, $eperiod, $agentnum, %opt) = @_; + $self->invoiced( $speriod, $eperiod, $agentnum, %opt) + + $self->discounted( $speriod, $eperiod, $agentnum, %opt); +} + #XXX docs #these should be auto-generated or $AUTOLOADed or something @@ -409,9 +485,9 @@ sub cust_pkg_recur_cost { =item cust_bill_pkg: the total package charges on invoice line items. -'charges': limit the type of charges included (setup, recur, usage). -Should be a string containing one or more of 'S', 'R', or 'U'; if -unspecified, defaults to all three. +'charges': limit the type of charges included (setup, recur, usage, discount, taxes). +Should be a string containing one or more of 'S', 'R', or 'U'; or 'D' or 'T' (discount +and taxes should not be combined with the others.) If unspecified, defaults to 'SRU'. 'classnum': limit to this package class. @@ -440,6 +516,8 @@ sub cust_bill_pkg { $sum += $self->cust_bill_pkg_setup(@_) if $charges{S}; $sum += $self->cust_bill_pkg_recur(@_) if $charges{R}; $sum += $self->cust_bill_pkg_detail(@_) if $charges{U}; + $sum += $self->cust_bill_pkg_discount(@_) if $charges{D}; + $sum += $self->cust_bill_pkg_taxes(@_) if $charges{T}; if ($opt{'average_per_cust_pkg'}) { my $count = $self->cust_bill_pkg_count_pkgnum(@_); @@ -495,12 +573,17 @@ sub _cust_bill_pkg_recurring { my @where = ( '(pkgnum != 0 OR feepart IS NOT NULL)', - $self->with_classnum($opt{'classnum'}, $opt{'use_override'}), $self->with_report_option(%opt), $self->with_refnum(%opt), $self->with_cust_classnum(%opt) ); + my $where_classnum = $self->with_classnum($opt{'classnum'}, $opt{'use_override'}); + if ($opt{'project'}) { + $where_classnum =~ s/\bcust_bill_pkg/v_cust_bill_pkg/g; + } + push @where, $where_classnum; + if ( $opt{'distribute'} ) { $where[0] = 'pkgnum != 0'; # specifically exclude fees push @where, "cust_main.agentnum = $agentnum" if $agentnum; @@ -516,6 +599,10 @@ sub _cust_bill_pkg_recurring { $self->in_time_period_and_agent($speriod, $eperiod, $agentnum, $_date); } + if ( $opt{'custnum'} =~ /^(\d+)$/ ) { + push @where, "(cust_main.custnum = $1)"; + } + return " FROM $cust_bill_pkg $cust_bill_pkg_join @@ -523,6 +610,16 @@ sub _cust_bill_pkg_recurring { } +=item cust_bill_pkg_recur: the total recur charges + +Most arguments as for C, plus: + +'custnum': limit to this customer + +'cost': if true, return total recur costs instead + +=cut + sub cust_bill_pkg_recur { my $self = shift; my ($speriod, $eperiod, $agentnum, %opt) = @_; @@ -549,9 +646,11 @@ sub cust_bill_pkg_recur { ($cust_bill_pkg.edate - $cust_bill_pkg.sdate)"; } - my $total_sql = - "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)" . - $self->_cust_bill_pkg_recurring(@_); + my $total_sql = $opt{'cost'} + ? "SELECT SUM(part_pkg.recur_cost)" + : "SELECT COALESCE(SUM(($cust_bill_pkg.recur - $item_usage) $recur_fraction),0)"; + + $total_sql .= $self->_cust_bill_pkg_recurring(@_); $self->scalar_sql($total_sql); } @@ -567,10 +666,14 @@ sub cust_bill_pkg_count_pkgnum { =item cust_bill_pkg_detail: the total usage charges in detail lines. -Arguments as for C, plus: +Most arguments as for C, plus: 'usageclass': limit to this usage class number. +'custnum': limit to this customer + +'cost': if true, return total usage costs instead + =cut sub cust_bill_pkg_detail { @@ -603,7 +706,16 @@ sub cust_bill_pkg_detail { ); } + if ( $opt{'custnum'} =~ /^(\d+)$/ ) { + push @where, "(cust_main.custnum = $1)"; + } + my $total_sql = " SELECT SUM(cust_bill_pkg_detail.amount) "; + my $extra_join = ''; + if ($opt{'cost'}) { + $extra_join = " JOIN cdr USING ( detailnum ) "; + $total_sql = " SELECT SUM(cdr.rated_cost) "; + } $total_sql .= " FROM cust_bill_pkg_detail @@ -613,55 +725,89 @@ 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 ); + LEFT JOIN part_fee USING ( feepart ) + ".$extra_join. + " WHERE ".join( ' AND ', grep $_, @where ); $self->scalar_sql($total_sql); } sub cust_bill_pkg_discount { - my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; + my $self = shift; + my ($speriod, $eperiod, $agentnum, %opt) = @_; + # apply all the same constraints here as for setup/recur - #need to do this the new multi-classnum way if it gets re-enabled - #my $where = ''; - #my $comparison = ''; - #if ( $opt{'classnum'} =~ /^(\d+)$/ ) { - # if ( $1 == 0 ) { - # $comparison = "IS NULL"; - # } else { - # $comparison = "= $1"; - # } - # - # if ( $opt{'use_override'} ) { - # $where = "( - # part_pkg.classnum $comparison AND pkgpart_override IS NULL OR - # override.classnum $comparison AND pkgpart_override IS NOT NULL - # )"; - # } else { - # $where = "part_pkg.classnum $comparison"; - # } - #} + $agentnum ||= $opt{'agentnum'}; + + my @where = ( + '(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), + $self->with_refnum(%opt), + $self->with_cust_classnum(%opt) + ); + + my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg_discount.amount), 0) + FROM cust_bill_pkg_discount + JOIN cust_bill_pkg USING (billpkgnum) + $cust_bill_pkg_join + WHERE " . join(' AND ', grep $_, @where); + + $self->scalar_sql($total_sql); +} + +sub cust_bill_pkg_taxes { + my $self = shift; + my ($speriod, $eperiod, $agentnum, %opt) = @_; $agentnum ||= $opt{'agentnum'}; - my $total_sql = - " SELECT COALESCE( SUM( cust_bill_pkg_discount.amount ), 0 ) "; + my @where = ( + '(cust_bill_pkg.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), + $self->with_refnum(%opt), + $self->with_cust_classnum(%opt) + ); - $total_sql .= - " FROM cust_bill_pkg_discount - LEFT JOIN cust_bill_pkg USING ( billpkgnum ) - LEFT JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) - WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum); - # LEFT JOIN cust_pkg_discount USING ( pkgdiscountnum ) - # LEFT JOIN discount USING ( discountnum ) - # LEFT JOIN cust_pkg USING ( pkgnum ) - # LEFT JOIN part_pkg USING ( pkgpart ) - # LEFT JOIN part_pkg AS override ON pkgpart_override = override.pkgpart - - return $self->scalar_sql($total_sql); + my $total_sql = "SELECT COALESCE(SUM(cust_bill_pkg_tax_location.amount),0) + FROM cust_bill_pkg + $cust_bill_pkg_join + LEFT JOIN cust_bill_pkg_tax_location + ON (cust_bill_pkg.billpkgnum = cust_bill_pkg_tax_location.taxable_billpkgnum) + WHERE " . join(' AND ', grep $_, @where); + $self->scalar_sql($total_sql); +} + +#all credits applied to matching pkg line items (ie not taxes) + +sub cust_bill_pkg_credits { + my $self = shift; + my ($speriod, $eperiod, $agentnum, %opt) = @_; + + $agentnum ||= $opt{'agentnum'}; + + my @where = ( + '(cust_bill_pkg.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), + $self->with_refnum(%opt), + $self->with_cust_classnum(%opt) + ); + + my $total_sql = "SELECT COALESCE(SUM(cust_credit_bill_pkg.amount),0) + FROM cust_bill_pkg + $cust_bill_pkg_join + LEFT JOIN cust_credit_bill_pkg + USING ( billpkgnum ) + WHERE " . join(' AND ', grep $_, @where); + + $self->scalar_sql($total_sql); } ##### package churn report ##### @@ -717,6 +863,54 @@ sub unsusp_pkg { $self->churn_pkg('unsusp', @_); } +sub total_revenue_pkg { + my $self = shift; + my $active_revenue = $self->revenue_pkg('active', @_); + my $setup_revenue = $self->revenue_pkg('setup', @_); + my $return = sprintf("%.2f", $active_revenue + $setup_revenue); + + return $return; +} + +sub revenue_pkg { + my $self = shift; + my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_; + my $totalrevenue; + + my ($from, @where) = + FS::h_cust_pkg->churn_fromwhere_sql( $status, $speriod, $eperiod); + + push @where, $self->pkg_where(%opt, 'agentnum' => $agentnum); + + my $sql; + + if ($status eq "active") { + $sql = "SELECT DISTINCT ON (revenue.pkgnum) revenue.pkgnum AS pkgnum, revenue.recur AS revenue + FROM $from + JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart) + JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum) + JOIN h_cust_bill_pkg AS revenue ON (cust_pkg.pkgnum = revenue.pkgnum AND cust_pkg.history_date + 5 > revenue.history_date) + "; + } + elsif ($status eq "setup") { + $sql = "SELECT DISTINCT ON (revenue.pkgnum) revenue.pkgnum AS pkgnum, revenue.setup AS revenue + FROM $from + JOIN part_pkg ON (cust_pkg.pkgpart = part_pkg.pkgpart) + JOIN cust_main ON (cust_pkg.custnum = cust_main.custnum) + JOIN h_cust_bill_pkg AS revenue ON (cust_pkg.pkgnum = revenue.pkgnum AND cust_pkg.setup + 15 > revenue.history_date) + "; + } + + $sql .= ' WHERE '.join(' AND ', @where) + if scalar(@where); + + $sql .= "ORDER BY revenue.pkgnum ASC, revenue.history_date DESC"; + + my $revenue_sql = "SELECT sum(rev.revenue) AS total_revenue FROM ( $sql ) AS rev"; + + $self->scalar_sql($revenue_sql); +} + sub churn_pkg { my $self = shift; my ( $status, $speriod, $eperiod, $agentnum, %opt ) = @_; @@ -884,16 +1078,27 @@ sub with_classnum { $classnum = [ $classnum ] if !ref($classnum); @$classnum = grep /^\d+$/, @$classnum; + return '' if !@$classnum; my $in = 'IN ('. join(',', @$classnum). ')'; - my $expr = " - ( COALESCE(part_pkg.classnum, 0) $in AND pkgpart_override IS NULL) - 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 )"; + # then include packages if their base package is in the set and they are + # not overridden, + # or if they are overridden and their override package is in the set, + # or fees if they are in the set + return "( + ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL AND pkgpart_override IS NULL ) + OR ( COALESCE(override.classnum, 0) $in AND pkgpart_override IS NOT NULL ) + OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL ) + )"; + } else { + # include packages if their base package is in the set, + # or fees if they are in the set + return "( + ( COALESCE(part_pkg.classnum, 0) $in AND cust_pkg.pkgpart IS NOT NULL ) + OR ( COALESCE(part_fee.classnum, 0) $in AND cust_bill_pkg.feepart IS NOT NULL ) + )"; } - "( $expr )"; } sub with_usageclass {