From: Mark Wells Date: Tue, 30 Jun 2015 19:14:19 +0000 (-0700) Subject: include discounts in gross sales reports, #25943 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=39501b4ff6c6cd9ac3cfe7ad313affe049e18994 include discounts in gross sales reports, #25943 --- diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index 479747307..eef983d68 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 2a67529c1..62c55d6df 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 diff --git a/httemplate/graph/money_time.cgi b/httemplate/graph/money_time.cgi index 9071fc7b9..b44adeaf8 100644 --- a/httemplate/graph/money_time.cgi +++ b/httemplate/graph/money_time.cgi @@ -39,8 +39,10 @@ if ( $cgi->param('refnum') =~ /^(\d+)$/ ) { } my $referralname = $part_referral ? $part_referral->referral.' ' : ''; - -my @items = qw( invoiced netsales +# need to clean this up. the false symmetry of "gross" and "net" everything +# makes it aesthetically hard to make this report more useful. +my @items = qw( gross netsales + discounted credits netcredits payments receipts refunds netrefunds @@ -51,8 +53,9 @@ if ( $cgi->param('12mo') == 1 ) { } my %label = ( - 'invoiced' => 'Gross Sales', + 'gross' => 'Gross Sales', 'netsales' => 'Net Sales', + 'discounted' => 'Discounts', 'credits' => 'Gross Credits', 'netcredits' => 'Net Credits', 'payments' => 'Gross Receipts', @@ -64,8 +67,9 @@ my %label = ( ); my %graph_suffix = ( - 'invoiced' => ' (invoiced)', + 'gross' => ' (invoiced + discounts)', 'netsales' => ' (invoiced - applied credits)', + 'discounted' => ' (discounts)', 'credits' => ' (credited)', 'netcredits' => ' (applied credits)', 'payments' => ' (payments)', @@ -84,7 +88,7 @@ $graph_label{$_.'_12mo'} = $graph_label{$_}. " (prev 12 months)" foreach keys %graph_label; my %color = ( - 'invoiced' => '9999ff', #light blue + 'gross' => '9999ff', #light blue 'netsales' => '0000cc', #blue 'credits' => 'ff9999', #light red 'netcredits' => 'cc0000', #red @@ -94,6 +98,7 @@ my %color = ( 'netrefunds' => 'ff9900', #orange 'cashflow' => '99cc33', #light olive 'netcashflow' => '339900', #olive + 'discounted' => 'cc33cc', #purple-ish? ); $color{$_.'_12mo'} = $color{$_} foreach keys %color; @@ -102,7 +107,7 @@ my $ar = "agentnum=$agentnum;refnum=$refnum"; $ar .= ";cust_classnum=$_" foreach @classnums; my %link = ( - 'invoiced' => "${p}search/cust_bill.html?$ar;", + 'gross' => "${p}search/cust_bill.html?$ar;", 'netsales' => "${p}search/cust_bill.html?$ar;net=1;", 'credits' => "${p}search/cust_credit.html?$ar;", 'netcredits' => "${p}search/cust_credit_bill.html?$ar;", @@ -110,6 +115,7 @@ my %link = ( 'receipts' => "${p}search/cust_bill_pay.html?$ar;", 'refunds' => "${p}search/cust_refund.html?magic=_date;$ar;", 'netrefunds' => "${p}search/cust_credit_refund.html?$ar;", + 'discounted' => "${p}search/cust_bill_pkg_discount.html?$ar;", ); # XXX link 12mo? diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html index 082073339..662673676 100755 --- a/httemplate/search/cust_bill.html +++ b/httemplate/search/cust_bill.html @@ -1,61 +1,82 @@ <& elements/search.html, - 'title' => emt('Invoice Search Results'), - 'html_init' => $html_init, - 'menubar' => $menubar, - 'name' => 'invoices', - 'query' => $sql_query, - 'count_query' => $count_query, - 'count_addl' => $count_addl, - 'redirect' => $link, - 'header' => [ emt('Invoice #'), - emt('Balance'), - emt('Net Amount'), - emt('Gross Amount'), - emt('Date'), - FS::UI::Web::cust_header(), - ], - 'fields' => [ - 'display_invnum', - sub { sprintf($money_char.'%.2f', shift->get('owed') ) }, - sub { sprintf($money_char.'%.2f', shift->get('net') ) }, - sub { sprintf($money_char.'%.2f', shift->charged ) }, - sub { time2str('%b %d %Y', shift->_date ) }, - \&FS::UI::Web::cust_fields, - ], - 'sort_fields' => [ - 'COALESCE( agent_invid, invnum )', - FS::cust_bill->owed_sql, - FS::cust_bill->net_sql, - 'charged', - '_date', - ], - 'align' => 'rrrrl'.FS::UI::Web::cust_aligns(), - 'links' => [ - $link, - $link, - $link, - $link, - $link, - ( map { $_ ne 'Cust. Status' ? $clink : '' } - FS::UI::Web::cust_header() - ), - ], - 'color' => [ - '', - '', - '', - '', - '', - FS::UI::Web::cust_colors(), - ], - 'style' => [ - '', - '', - '', - '', - '', - FS::UI::Web::cust_styles(), - ], + 'title' => emt('Invoice Search Results'), + 'html_init' => $html_init, + 'menubar' => $menubar, + 'name' => 'invoices', + 'query' => $sql_query, + 'count_query' => $count_query, + 'count_addl' => $count_addl, + 'redirect' => $link, + 'header' => [ emt('Invoice #'), + emt('Gross Amount'), + emt('Discount'), + emt('Credits'), + emt('Net Amount'), + emt('Balance'), + emt('Date'), + FS::UI::Web::cust_header(), + ], + 'fields' => [ + 'display_invnum', + 'gross', + 'discounted', + 'credited', + 'net', + 'owed', + sub { time2str('%b %d %Y', shift->_date ) }, + \&FS::UI::Web::cust_fields, + ], + 'sort_fields' => [ + 'COALESCE( agent_invid, invnum )', + 'gross', + 'discounted', + 'credited', + 'net', + 'owed', + '_date', + ], + 'format' => [ + '', + $money_char.'%.2f', + $money_char.'%.2f', + $money_char.'%.2f', + $money_char.'%.2f', + $money_char.'%.2f', + '', + ], + 'align' => 'rrrrrrl'.FS::UI::Web::cust_aligns(), + 'links' => [ + $link, + $link, + $link, + $link, + $link, + $link, + $link, + ( map { $_ ne 'Cust. Status' ? $clink : '' } + FS::UI::Web::cust_header() + ), + ], + 'color' => [ + '', + '', + '', + '', + '', + '', + '', + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + '', + '', + '', + '', + '', + FS::UI::Web::cust_styles(), + ], &> <%init> diff --git a/httemplate/search/elements/search-html.html b/httemplate/search/elements/search-html.html index d8ab30a32..6121aea94 100644 --- a/httemplate/search/elements/search-html.html +++ b/httemplate/search/elements/search-html.html @@ -377,9 +377,16 @@ $rows => [] % my $sizes = $opt{'size'} ? [ @{$opt{'size'}} ] : []; % my $styles = $opt{'style'} ? [ @{$opt{'style'}} ] : []; % my $cstyles = $opt{'cell_style'} ? [ @{$opt{'cell_style'}} ] : []; +% my $formats = $opt{'format'} ? [ @{$opt{'format'}} ] : []; % % foreach my $field ( % +% # if the value of the field is an arrayref, then construct a table in +% # the cell. +% # if it's a (non-empty) scalar, and a format has been specified, then +% # format the scalar with that. +% # otherwise, just output the value. +% # XXX we should also do date formats like this % map { % if ( ref($_) eq 'ARRAY' ) { % @@ -443,10 +450,17 @@ $rows => [] % ''; % % } else { +% if ( length($_) > 0 and my $format = shift @$formats ) { +% $_ = sprintf($format, $_); +% } % $_; % } % } % +% # get the value of the field spec: +% # - if the spec is a coderef, evaluate the coderef +% # - if the spec is a string, call that string as a method +% # - if the spec is an integer, get the field in that position % map { % if ( ref($_) eq 'CODE' ) { % &{$_}($row); @@ -491,10 +505,6 @@ $rows => [] % $tooltip = &{$tooltip}($row) % if ref($tooltip) eq 'CODE'; % $tooltip = qq! title="<% $tooltip |h %>"!; -%# qq! onmouseover="return overlib(!. -%# $m->interp->apply_escapes($tooltip, 'h', 'js_string'). -%# qq!, FGCLASS, 'tooltip', REF, 'a$id', !. -%# qq!REFC, 'LL', REFP, 'UL')"! if $tooltip; % % if ( $link ) { % my( $url, $method ) = @{$link}; diff --git a/httemplate/search/report_cust_bill.html b/httemplate/search/report_cust_bill.html index 3efe830e3..e80c6e895 100644 --- a/httemplate/search/report_cust_bill.html +++ b/httemplate/search/report_cust_bill.html @@ -29,8 +29,7 @@ label => mt('Customer Class'), field => 'cust_classnum', multiple => 1, - 'pre_options' => [ '' => emt('(none)') ], - 'all_selected' => 1, + 'pre_options' => [ 0 => emt('(none)') ], &>