From: ivan Date: Fri, 14 Mar 2008 07:49:13 +0000 (+0000) Subject: add net credits to sales/credits/receipts report X-Git-Tag: TRIXBOX_2_6~20 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=9d55204f876f52e314076f5fa9d1ae15a1639dae add net credits to sales/credits/receipts report --- diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm index 33fdfd80c..746362008 100644 --- a/FS/FS/Report/Table/Monthly.pm +++ b/FS/FS/Report/Table/Monthly.pm @@ -228,6 +228,21 @@ sub credits { ); } +sub netcredits { + my( $self, $speriod, $eperiod, $agentnum ) = @_; + $self->scalar_sql(" + SELECT SUM(cust_credit_bill.amount) + FROM cust_credit_bill + LEFT JOIN cust_bill USING ( invnum ) + LEFT JOIN cust_main USING ( custnum ) + WHERE ". $self->in_time_period_and_agent( $speriod, + $eperiod, + $agentnum, + 'cust_bill._date' + ) + ); +} + #these should be auto-generated or $AUTOLOADed or something sub invoiced_12mo { my( $self, $speriod, $eperiod, $agentnum ) = @_; diff --git a/FS/FS/cust_credit_bill.pm b/FS/FS/cust_credit_bill.pm index 411bae21a..375c885a0 100644 --- a/FS/FS/cust_credit_bill.pm +++ b/FS/FS/cust_credit_bill.pm @@ -4,11 +4,12 @@ use strict; use vars qw( @ISA $conf ); use FS::UID qw( getotaker ); use FS::Record qw( qsearch qsearchs ); +use FS::cust_main_Mixin; use FS::cust_bill_ApplicationCommon; use FS::cust_bill; use FS::cust_credit; -@ISA = qw( FS::cust_bill_ApplicationCommon ); +@ISA = qw( FS::cust_main_Mixin FS::cust_bill_ApplicationCommon ); #ask FS::UID to run this stuff for us later FS::UID->install_callback( sub { diff --git a/httemplate/graph/money_time.cgi b/httemplate/graph/money_time.cgi index c30ff207a..b996380b6 100644 --- a/httemplate/graph/money_time.cgi +++ b/httemplate/graph/money_time.cgi @@ -25,25 +25,27 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { my $agentname = $agent ? $agent->agent.' ' : ''; -my @items = qw( invoiced netsales credits payments receipts ); +my @items = qw( invoiced netsales credits netcredits payments receipts ); if ( $cgi->param('12mo') == 1 ) { @items = map $_.'_12mo', @items; } my %label = ( - 'invoiced' => 'Gross Sales', - 'netsales' => 'Net Sales', - 'credits' => 'Credits', - 'payments' => 'Gross Receipts', - 'receipts' => 'Net Receipts', + 'invoiced' => 'Gross Sales', + 'netsales' => 'Net Sales', + 'credits' => 'Gross Credits', + 'netcredits' => 'Net Credits', + 'payments' => 'Gross Receipts', + 'receipts' => 'Net Receipts', ); my %graph_suffix = ( - 'invoiced' => ' (invoiced)', - 'netsales' => ' (invoiced - applied credits)', - 'credits' => '', - 'payments' => ' (payments)', - 'receipts' => '/Cashflow (payments - refunds)', + 'invoiced' => ' (invoiced)', + 'netsales' => ' (invoiced - applied credits)', + 'credits' => ' (credited)', + 'netcredits' => ' (applied credits)', + 'payments' => ' (payments)', + 'receipts' => '/Cashflow (payments - refunds)', ); my %graph_label = map { $_ => $label{$_}.$graph_suffix{$_} } keys %label; @@ -54,20 +56,22 @@ $graph_label{$_.'_12mo'} = $graph_label{$_}. " (previous 12 months)" foreach keys %graph_label; my %color = ( - 'invoiced' => '9999ff', #light blue - 'netsales' => '0000cc', #blue - 'credits' => 'cc0000', #red - 'payments' => '99cc99', #light green - 'receipts' => '00cc00', #green + 'invoiced' => '9999ff', #light blue + 'netsales' => '0000cc', #blue + 'credits' => 'ff9999', #light red + 'netcredits' => 'cc0000', #red + 'payments' => '99cc99', #light green + 'receipts' => '00cc00', #green ); $color{$_.'_12mo'} = $color{$_} foreach keys %color; my %link = ( - 'invoiced' => "${p}search/cust_bill.html?agentnum=$agentnum;", - 'netsales' => "${p}search/cust_bill.html?agentnum=$agentnum;net=1;", - 'credits' => "${p}search/cust_credit.html?agentnum=$agentnum;", - 'payments' => "${p}search/cust_pay.cgi?magic=_date;agentnum=$agentnum;", + 'invoiced' => "${p}search/cust_bill.html?agentnum=$agentnum;", + 'netsales' => "${p}search/cust_bill.html?agentnum=$agentnum;net=1;", + 'credits' => "${p}search/cust_credit.html?agentnum=$agentnum;", + 'netcredits' => "${p}search/cust_credit_bill.html?agentnum=$agentnum;", + 'payments' => "${p}search/cust_pay.cgi?magic=_date;agentnum=$agentnum;", ); # XXX link 12mo? diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html index e4975c8de..2c59297be 100755 --- a/httemplate/search/cust_credit.html +++ b/httemplate/search/cust_credit.html @@ -3,7 +3,7 @@ 'name' => 'credits', 'query' => $sql_query, 'count_query' => $count_query, - 'count_addl' => [ '$%.2f total credited', ], + 'count_addl' => [ '$%.2f total credited (gross)', ], #'redirect' => $link, 'header' => [ 'Amount', 'Date', diff --git a/httemplate/search/cust_credit_bill.html b/httemplate/search/cust_credit_bill.html new file mode 100644 index 000000000..818e603a1 --- /dev/null +++ b/httemplate/search/cust_credit_bill.html @@ -0,0 +1,135 @@ +<% include( 'elements/search.html', + 'title' => $title, + 'name' => 'net credits', + 'query' => $sql_query, + 'count_query' => $count_query, + 'count_addl' => [ '$%.2f total credited (net)', ], + 'header' => [ 'Net applied', + 'to Invoice', + 'Credit', + 'By', + 'Reason', + FS::UI::Web::cust_header(), + ], + 'fields' => [ + sub { $money_char. sprintf('%.2f', shift->amount ) }, + sub { my $ccb = shift; + '#'.$ccb->invnum. ' '. + time2str('%b %d %Y', $ccb->cust_bill_date ). + " ($money_char". + sprintf('%.2f', $ccb->cust_bill_amount). + ")" + }, + sub { my $ccb = shift; + time2str('%b %d %Y', $ccb->_date ). + " ($money_char". + sprintf('%.2f', $ccb->cust_credit_amount ). + ")" + }, + sub { shift->cust_credit->otaker }, + sub { shift->cust_credit->reason }, + \&FS::UI::Web::cust_fields, + ], + 'align' => 'rrrll'.FS::UI::Web::cust_aligns(), + 'links' => [ + '', + $cust_bill_link, + '', + '', + '', + ( map { $_ ne 'Cust. Status' ? $cust_link : '' } + FS::UI::Web::cust_header() + ), + ], + 'color' => [ + '', + '', + '', + '', + '', + FS::UI::Web::cust_colors(), + ], + 'style' => [ + '', + '', + '', + '', + '', + FS::UI::Web::cust_styles(), + ], + ) +%> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +my $conf = new FS::Conf; +my $money_char = $conf->config('money_char') || '$'; + +my $title = 'Net Credit Search Results'; + +my @search = (); + +if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) { + push @search, "agentnum = $1"; + my $agent = qsearchs('agent', { 'agentnum' => $1 } ); + die "unknown agentnum $1" unless $agent; + $title = $agent->agent. " $title"; +} + +my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); +push @search, "cust_bill._date >= $beginning ", + "cust_bill._date <= $ending"; + +#here is the agent virtualization +push @search, $FS::CurrentUser::CurrentUser->agentnums_sql; + +my $where = 'WHERE '. join(' AND ', @search); +# +my $count_query = 'SELECT COUNT(*), SUM(amount) + FROM cust_credit_bill + LEFT JOIN cust_bill USING ( invnum ) + LEFT JOIN cust_main USING ( custnum ) '. + $where; + +my $sql_query = { + 'table' => 'cust_credit_bill', + 'select' => join(', ', + 'cust_credit_bill.*', + 'cust_credit.amount AS cust_credit_amount', + 'cust_bill._date AS cust_bill_date', + 'cust_bill.charged AS cust_bill_charged', + 'cust_credit.custnum AS custnum', + 'cust_main.custnum AS cust_main_custnum', + FS::UI::Web::cust_sql_fields(), + ), + 'hashref' => {}, + 'extra_sql' => $where, + 'addl_from' => 'LEFT JOIN cust_bill USING ( invnum ) + LEFT JOIN cust_credit USING ( crednum ) + LEFT JOIN cust_main ON ( cust_bill.custnum = cust_main.custnum )', +}; + +my $cust_bill_link = sub { + my $cust_credit_bill = shift; + $cust_credit_bill->invnum + ? [ "${p}view/cust_bill.cgi?", 'invnum' ] + : ''; +}; + +#my $cust_credit_link = sub { +# my $cust_credit_bill = shift; +# $cust_credit_bill->crednum +# ? [ "${p}view/cust_credit.cgi?", 'crednum' ] +# : ''; +#}; + +my $cust_link = sub { + my $cust_credit_bill = shift; + $cust_credit_bill->cust_main_custnum + ? [ "${p}view/cust_main.cgi?", 'cust_main_custnum' ] + : ''; +}; + +