From adcf4e1d0e02f9e91513bdccd4a8d811f052a7f6 Mon Sep 17 00:00:00 2001 From: Jonathan Prykop Date: Wed, 15 Jul 2015 22:07:27 -0500 Subject: [PATCH] RT#37125: Include discounts in report: customer accounting summary --- FS/FS/Report/Table.pm | 58 ++++++++++++++++++---- httemplate/search/customer_accounting_summary.html | 46 +++++++++++++---- .../search/report_customer_accounting_summary.html | 5 ++ 3 files changed, 87 insertions(+), 22 deletions(-) diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index 0a0d24a79..ffa117240 100644 --- a/FS/FS/Report/Table.pm +++ b/FS/FS/Report/Table.pm @@ -272,17 +272,53 @@ 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'}) { + $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"; + $sql .= <scalar_sql( + $sql + . 'WHERE ' + . $self->in_time_period_and_agent( $speriod, + $eperiod, + $agentnum, + 'cust_bill._date' + ) + . $self->for_opts(%opt) ); } diff --git a/httemplate/search/customer_accounting_summary.html b/httemplate/search/customer_accounting_summary.html index 0dab7cecf..744b313f9 100644 --- a/httemplate/search/customer_accounting_summary.html +++ b/httemplate/search/customer_accounting_summary.html @@ -141,9 +141,20 @@ $title .= 'Customer Accounting Summary Report'; my @items = ('netsales', 'cashflow'); my @params = ( [], [] ); -my $setuprecur = ''; -if ( $cgi->param('setuprecur') ) { - $setuprecur = 1; +my $grossdiscount = $cgi->param('grossdiscount'); +my $setuprecur = $cgi->param('setuprecur'); +if ($setuprecur && $grossdiscount) { + #see blocks below for more details on each option + @items = ('gross', 'discounted', 'receipts', 'gross', 'discounted', 'receipts'); + @params = ( + [ setuprecur => 'setup' ], + [ setuprecur => 'setup' ], + [ setuprecur => 'setup' ], + [ setuprecur => 'recur' ], + [ setuprecur => 'recur' ], + [ setuprecur => 'recur' ], + ); +} elsif ($setuprecur) { # instead of 'cashflow' (payments - refunds), use 'receipts' # (applied payments), because it's divisible into setup and recur. @items = ('netsales', 'receipts', 'netsales', 'receipts'); @@ -153,7 +164,14 @@ if ( $cgi->param('setuprecur') ) { [ setuprecur => 'recur' ], [ setuprecur => 'recur' ], ); +} elsif ($grossdiscount) { + # instead of 'netsales' (invoiced - netcredits) + # use 'gross' (invoiced + discounted) and 'discounted' (sum of discounts on invoices) + @items = ('gross', 'discounted', 'cashflow'); + @params = ( [], [], [] ); } + + my @labels = (); my @cross_params = (); @@ -208,7 +226,7 @@ $cells[0] = [ { header => 1, rowspan => 2, colspan => ($setuprecur ? 4 : 3) }, ($setuprecur ? '' : ()), map { - { header => 1, colspan => 2, value => time2str('%b %Y', $_) }, + { header => 1, colspan => ($grossdiscount ? 3 : 2), value => time2str('%b %Y', $_) }, '' } @{ $data->{speriod} } ]; @@ -218,8 +236,14 @@ $rows[1] = {}; $cells[1] = [ '', ($setuprecur ? '' : ()), map { - ( { header => 1, value => mt('Billed') }, - { header => 1, value => mt('Paid') } + ( ($grossdiscount + ? ( + { header => 1, value => mt('Gross') }, + { header => 1, value => mt('Discount') } + ) + : { header => 1, value => mt('Billed') } + ), + { header => 1, value => mt('Paid') }, ) } (1..$ncols) ]; @@ -256,12 +280,12 @@ foreach my $cust_main (@cust_main) { # correspond to cross_params header => 1 }; } for my $col (0..$ncols-1) { # the month - for my $subcol (0..1) { # the billed/paid axis - my $item = $subrow * 2 + $subcol; + for my $subcol (0..($grossdiscount ? 2 : 1)) { # the billed/paid or gross/discount/paid axis + my $item = $subrow * ($grossdiscount ? 3 : 2) + $subcol; my $value = $data->{data}[$item][$col][$row]; $skip = 0 if abs($value) > 0.005; push @thisrow, { value => sprintf('%0.2f', $value), format => 'money' }; - $total[( ($ncols * $subrow) + $col ) * 2 + $subcol] += $value; + $total[( ($ncols * $subrow) + $col ) * ($grossdiscount ? 3 : 2) + $subcol] += $value; } #subcol } #col push @cells, \@thisrow; @@ -294,8 +318,8 @@ for my $subrow (0..($setuprecur ? 1 : 0)) { { value => $subrow ? mt('recurring') : mt('setup'), header => 1 }; } - for my $col (0..($ncols * 2)-1) { # month and billed/paid axis - my $value = $total[($subrow * $ncols * 2) + $col]; + for my $col (0..($ncols * ($grossdiscount ? 3 : 2))-1) { # month and billed/paid or gross/discount/paid axis + my $value = $total[($subrow * $ncols * ($grossdiscount ? 3 : 2)) + $col]; push @thisrow, { value => sprintf('%0.2f', $value), format => 'money' }; } push @cells, \@thisrow; diff --git a/httemplate/search/report_customer_accounting_summary.html b/httemplate/search/report_customer_accounting_summary.html index 8206f34ca..bdcdbccd1 100755 --- a/httemplate/search/report_customer_accounting_summary.html +++ b/httemplate/search/report_customer_accounting_summary.html @@ -37,6 +37,11 @@ 'value' => 1, &> + <& /elements/tr-checkbox.html, + 'label' => 'Show Gross & Discounted', + 'field' => 'grossdiscount', + 'value' => 1, + &> -- 2.11.0