diff options
author | Mark Wells <mark@freeside.biz> | 2015-06-30 12:14:19 -0700 |
---|---|---|
committer | Mark Wells <mark@freeside.biz> | 2015-06-30 12:15:01 -0700 |
commit | 8d19a6a0320303bf5c84f4b82d22afb9d9f36f23 (patch) | |
tree | 4a08329205fce67c227d64dcf4aec89a9b2db7d4 /FS | |
parent | e21e6a373962bcfa7c14bd4bb3e22d106badd9cd (diff) |
include discounts in gross sales reports, #25943
Diffstat (limited to 'FS')
-rw-r--r-- | FS/FS/Report/Table.pm | 30 | ||||
-rw-r--r-- | FS/FS/cust_bill/Search.pm | 91 |
2 files changed, 95 insertions, 26 deletions
diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index 4797473..eef983d 100644 --- a/FS/FS/Report/Table.pm +++ b/FS/FS/Report/Table.pm @@ -266,6 +266,36 @@ sub netrefunds { ); } +=item discounted: The sum of discounts on invoices in the period. + +=cut + +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) + ); +} + +=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 diff --git a/FS/FS/cust_bill/Search.pm b/FS/FS/cust_bill/Search.pm index 2a67529..62c55d6 100644 --- a/FS/FS/cust_bill/Search.pm +++ b/FS/FS/cust_bill/Search.pm @@ -6,14 +6,15 @@ use FS::UI::Web; use FS::Record qw( qsearchs dbh ); use FS::cust_main; use FS::access_user; +use FS::Conf; =item search HASHREF (Class method) -Returns a qsearch hash expression to search for parameters specified in HASHREF. -In addition to all parameters accepted by search_sql_where, the following -additional parameters valid: +Returns a qsearch hash expression to search for parameters specified in +HASHREF. In addition to all parameters accepted by search_sql_where, the +following additional parameters valid: =over 4 @@ -38,35 +39,77 @@ sub search { my $join_cust_main = FS::UI::Web::join_cust_main('cust_bill'); + # get discounted, credited, and paid amounts here, for use in report + # + # Testing shows that this is by far the most efficient way to do the + # joins. In particular it's almost 100x faster to join to an aggregate + # query than to put the subquery in a select expression. It also makes + # it more convenient to do arithmetic between columns, use them as sort + # keys, etc. + # + # Each ends with a RIGHT JOIN cust_bill so that it includes all invnums, + # even if they have no discounts/credits/payments; the total amount is then + # coalesced to zero. + my $join = "$join_cust_main + JOIN ( + SELECT COALESCE(SUM(cust_bill_pkg_discount.amount), 0) AS discounted, + invnum + FROM cust_bill_pkg_discount + JOIN cust_bill_pkg USING (billpkgnum) + RIGHT JOIN cust_bill USING (invnum) + GROUP BY invnum + ) AS _discount USING (invnum) + JOIN ( + SELECT COALESCE(SUM(cust_credit_bill.amount), 0) AS credited, invnum + FROM cust_credit_bill + RIGHT JOIN cust_bill USING (invnum) + GROUP BY invnum + ) AS _credit USING (invnum) + JOIN ( + SELECT COALESCE(SUM(cust_bill_pay.amount), 0) AS paid, invnum + FROM cust_bill_pay + RIGHT JOIN cust_bill USING (invnum) + GROUP BY invnum + ) AS _pay USING (invnum) + "; + unless ( $count_query ) { + + my $money = (FS::Conf->new->config('money_char') || '$') . '%.2f'; + $count_query = 'SELECT COUNT(*), '. join(', ', map "SUM($_)", - ( 'charged', - FS::cust_bill->net_sql, - FS::cust_bill->owed_sql, + ( 'charged + discounted', + 'discounted', + 'credited', + 'charged - credited', + 'charged - credited - paid', ) ); - $count_addl = [ '$%.2f invoiced (gross)', - '$%.2f invoiced (net)', - '$%.2f outstanding balance', + $count_addl = [ "$money sales (gross)", + "− $money discounted", + "− $money credited", + "= $money sales (net)", + "$money outstanding balance", ]; } - $count_query .= " FROM cust_bill $join_cust_main $extra_sql"; + $count_query .= " FROM cust_bill $join $extra_sql"; #$sql_query = +{ 'table' => 'cust_bill', - 'addl_from' => $join_cust_main, + 'addl_from' => $join, 'hashref' => {}, 'select' => join(', ', 'cust_bill.*', #( map "cust_main.$_", qw(custnum last first company) ), 'cust_main.custnum as cust_main_custnum', FS::UI::Web::cust_sql_fields(), - #$class->owed_sql. ' AS owed', - #$class->net_sql. ' AS net', - FS::cust_bill->owed_sql. ' AS owed', - FS::cust_bill->net_sql. ' AS net', + '(charged + discounted) as gross', + 'discounted', + 'credited', + '(charged - credited) as net', + '(charged - credited - paid) as owed', ), 'extra_sql' => $extra_sql, 'order_by' => 'ORDER BY '. ( $params->{'order_by'} || 'cust_bill._date' ), @@ -179,13 +222,7 @@ sub search_sql_where { @classnum = grep /^(\d*)$/, @classnum; if ( @classnum ) { - push @search, '( '. join(' OR ', map { - $_ ? "cust_main.classnum = $_" - : "cust_main.classnum IS NULL" - } - @classnum - ). - ' )'; + push @search, 'COALESCE(cust_main.classnum, 0) IN ('.join(',', @classnum).')'; } } @@ -206,6 +243,7 @@ sub search_sql_where { push @search, "cust_bill.invnum <= $1"; } + # these are from parse_lt_gt, and should already be sanitized #charged if ( $param->{charged} ) { my @charged = ref($param->{charged}) @@ -216,21 +254,22 @@ sub search_sql_where { @charged; } - my $owed_sql = FS::cust_bill->owed_sql; + #my $owed_sql = FS::cust_bill->owed_sql; + my $owed_sql = '(cust_bill.charged - credited - paid)'; + my $net_sql = '(cust_bill.charged - credited)'; #owed if ( $param->{owed} ) { my @owed = ref($param->{owed}) ? @{ $param->{owed} } : ($param->{owed}); - push @search, map { s/^owed/$owed_sql/; $_; } - @owed; + push @search, map { s/^owed/$owed_sql/ } @owed; } #open/net flags push @search, "0 != $owed_sql" if $param->{'open'}; - push @search, '0 != '. FS::cust_bill->net_sql + push @search, "0 != $net_sql" if $param->{'net'}; #days |