X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2FReport%2FTable.pm;h=eeb99bac574e74a1cf930d3326a8c31a4f490012;hb=060c42eac1ecf2f376fe806c08a2ac78987cf9a6;hp=cba968b43fc6ea19f898b633d06f7bc25235e7d2;hpb=4a03b0b62cef514f9217e7be61cf7a16218ea387;p=freeside.git diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index cba968b43..eeb99bac5 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'; } @@ -272,17 +273,62 @@ sub netrefunds { sub discounted { my( $self, $speriod, $eperiod, $agentnum, %opt) = @_; - $self->scalar_sql('SELECT SUM(cust_bill_pkg_discount.amount) - FROM cust_bill_pkg_discount - JOIN cust_bill_pkg USING ( billpkgnum ) - JOIN cust_bill USING ( invnum ) - JOIN cust_main USING ( custnum ) - WHERE '. $self->in_time_period_and_agent( $speriod, - $eperiod, - $agentnum, - 'cust_bill._date' - ). - $self->for_opts(%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) ); } @@ -439,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, discount). -Should be a string containing one or more of 'S', 'R', 'U', or 'D'; 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. @@ -471,6 +517,7 @@ sub cust_bill_pkg { $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(@_); @@ -681,6 +728,58 @@ sub cust_bill_pkg_discount { $self->scalar_sql($total_sql); } +sub cust_bill_pkg_taxes { + 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_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 ##### =item active_pkg: The number of packages that were active at the start of @@ -901,6 +1000,7 @@ sub with_classnum { $classnum = [ $classnum ] if !ref($classnum); @$classnum = grep /^\d+$/, @$classnum; + return '' if !@$classnum; my $in = 'IN ('. join(',', @$classnum). ')'; if ( $use_override ) {