From 807f0570749e9093cbc82f6b11b3fa743b86b1db Mon Sep 17 00:00:00 2001 From: levinse Date: Thu, 30 Dec 2010 23:03:50 +0000 Subject: [PATCH] add customer accounting summary report, RT10911 --- FS/FS/Report/Table/Monthly.pm | 64 ++++++++++++++++------ httemplate/elements/menu.html | 1 + httemplate/graph/elements/monthly.html | 1 + httemplate/graph/elements/report.html | 2 + httemplate/search/customer_accounting_summary.html | 59 ++++++++++++++++++++ .../search/report_customer_accounting_summary.html | 33 +++++++++++ 6 files changed, 143 insertions(+), 17 deletions(-) create mode 100644 httemplate/search/customer_accounting_summary.html create mode 100755 httemplate/search/report_customer_accounting_summary.html diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm index 376ee5cd8..2d8dd7ee9 100644 --- a/FS/FS/Report/Table/Monthly.pm +++ b/FS/FS/Report/Table/Monthly.pm @@ -59,7 +59,14 @@ sub data { while ( $syear < $eyear || ( $syear == $eyear && $smonth < $emonth+1 ) ) { - push @{$data{label}}, "$smonth/$syear"; + if ( $self->{'doublemonths'} ) { + my($firstLabel,$secondLabel) = @{$self->{'doublemonths'}}; + push @{$data{label}}, "$smonth/$syear $firstLabel"; + push @{$data{label}}, "$smonth/$syear $secondLabel"; + } + else { + push @{$data{label}}, "$smonth/$syear"; + } my $speriod = timelocal(0,0,0,1,$smonth-1,$syear); push @{$data{speriod}}, $speriod; @@ -68,12 +75,25 @@ sub data { push @{$data{eperiod}}, $eperiod; my $col = 0; - my @row = (); - foreach my $item ( @{$self->{'items'}} ) { - my @param = $self->{'params'} ? @{ $self->{'params'}[$col] }: (); - my $value = $self->$item($speriod, $eperiod, $agentnum, @param); - #push @{$data{$item}}, $value; - push @{$data{data}->[$col++]}, $value; + my @items = @{$self->{'items'}}; + my $i; + for ( $i = 0; $i < scalar(@items); $i++ ) { + if ( $self->{'doublemonths'} ) { + my $item = $items[$i]; + my @param = $self->{'params'} ? @{ $self->{'params'}[$i] }: (); + my $value = $self->$item($speriod, $eperiod, $agentnum, @param); + push @{$data{data}->[$col]}, $value; + $item = $items[$i+1]; + @param = $self->{'params'} ? @{ $self->{'params'}[++$i] }: (); + $value = $self->$item($speriod, $eperiod, $agentnum, @param); + push @{$data{data}->[$col++]}, $value; + } + else { + my $item = $items[$i]; + my @param = $self->{'params'} ? @{ $self->{'params'}[$col] }: (); + my $value = $self->$item($speriod, $eperiod, $agentnum, @param); + push @{$data{data}->[$col++]}, $value; + } } } @@ -127,31 +147,32 @@ sub data { } sub invoiced { #invoiced - my( $self, $speriod, $eperiod, $agentnum ) = @_; + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; $self->scalar_sql(" SELECT SUM(charged) FROM cust_bill LEFT JOIN cust_main USING ( custnum ) WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum) + . (%opt ? $self->for_custnum(%opt) : '') ); } sub netsales { #net sales - my( $self, $speriod, $eperiod, $agentnum ) = @_; + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; - $self->invoiced($speriod,$eperiod,$agentnum) - - $self->netcredits($speriod,$eperiod,$agentnum); + $self->invoiced($speriod,$eperiod,$agentnum,%opt) + - $self->netcredits($speriod,$eperiod,$agentnum,%opt); } #deferred revenue sub cashflow { - my( $self, $speriod, $eperiod, $agentnum ) = @_; + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; - $self->payments($speriod, $eperiod, $agentnum) - - $self->refunds( $speriod, $eperiod, $agentnum); + $self->payments($speriod, $eperiod, $agentnum, %opt) + - $self->refunds( $speriod, $eperiod, $agentnum, %opt); } sub netcashflow { @@ -162,12 +183,13 @@ sub netcashflow { } sub payments { - my( $self, $speriod, $eperiod, $agentnum ) = @_; + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; $self->scalar_sql(" SELECT SUM(paid) FROM cust_pay LEFT JOIN cust_main USING ( custnum ) WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum) + . (%opt ? $self->for_custnum(%opt) : '') ); } @@ -182,17 +204,18 @@ sub credits { } sub refunds { - my( $self, $speriod, $eperiod, $agentnum ) = @_; + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; $self->scalar_sql(" SELECT SUM(refund) FROM cust_refund LEFT JOIN cust_main USING ( custnum ) WHERE ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum) + . (%opt ? $self->for_custnum(%opt) : '') ); } sub netcredits { - my( $self, $speriod, $eperiod, $agentnum ) = @_; + my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_; $self->scalar_sql(" SELECT SUM(cust_credit_bill.amount) FROM cust_credit_bill @@ -203,6 +226,7 @@ sub netcredits { $agentnum, 'cust_bill._date' ) + . (%opt ? $self->for_custnum(%opt) : '') ); } @@ -567,6 +591,12 @@ sub in_time_period_and_agent { $sql; } +sub for_custnum { + my ( $self, %opt ) = @_; + return '' unless $opt{'custnum'}; + $opt{'custnum'} =~ /^\d+$/ ? " and custnum = $opt{custnum} " : ''; +} + sub scalar_sql { my( $self, $sql ) = ( shift, shift ); my $sth = dbh->prepare($sql) or die dbh->errstr; diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index c4422a017..1e7c12b2e 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -295,6 +295,7 @@ if($curuser->access_right('Financial reports')) { $report_financial{'Sales Tax Liability'} = [ $fsurl.'search/report_tax.html', 'Sales tax liability report (internal taxclass system)' ]; $report_financial{'Tax Liability'} = [ $fsurl.'search/report_newtax.html', 'Tax liability report (vendor data tax products system)' ] if $conf->exists('enable_taxproducts'); + $report_financial{'Customer Accounting Summary'} = [ $fsurl.'search/report_customer_accounting_summary.html', 'Customer accounting summary report' ]; } elsif($curuser->access_right('Receivables report')) { diff --git a/httemplate/graph/elements/monthly.html b/httemplate/graph/elements/monthly.html index 7b1b98a61..de2b2e9d4 100644 --- a/httemplate/graph/elements/monthly.html +++ b/httemplate/graph/elements/monthly.html @@ -101,6 +101,7 @@ my $report = new FS::Report::Table::Monthly ( 'agentnum' => $opt{'agentnum'}, 'remove_empty' => $opt{'remove_empty'}, + 'doublemonths' => $opt{'doublemonths'}, ); my $data = $report->data; diff --git a/httemplate/graph/elements/report.html b/httemplate/graph/elements/report.html index b831f3a4f..2a5a1420f 100644 --- a/httemplate/graph/elements/report.html +++ b/httemplate/graph/elements/report.html @@ -163,9 +163,11 @@ any delimiter and linked from the elements in @data. % } else { % <% include('/elements/header.html', $opt{'title'} ) %> +% unless ( $opt{'graph_type'} eq 'none' ) { % $cgi->param('_type', 'png'); +% }

% unless ( $opt{'disable_download'} ) { diff --git a/httemplate/search/customer_accounting_summary.html b/httemplate/search/customer_accounting_summary.html new file mode 100644 index 000000000..8da8914c8 --- /dev/null +++ b/httemplate/search/customer_accounting_summary.html @@ -0,0 +1,59 @@ +<% include('/graph/elements/monthly.html', + #Dumper( + 'title' => $title, + 'graph_type' => 'none', + 'items' => \@items, + 'params' => \@params, + 'labels' => \@labels, + 'graph_labels' => \@labels, + 'remove_empty' => 1, + 'bottom_total' => 1, + 'agentnum' => $agentnum, + 'doublemonths' => \@doublemonths, + 'nototal' => 1, + ) +%> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +my @doublemonths = ( 'Billed', 'Paid' ); + +my ($agentnum,$sel_agent); +if ( $cgi->param('agentnum') eq 'all' ) { + $agentnum = 0; +} +elsif ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $agentnum = $1; + $sel_agent = qsearchs('agent', { 'agentnum' => $agentnum } ); + die "agentnum $agentnum not found!" unless $sel_agent; +} +my $title = $sel_agent ? $sel_agent->agent.' ' : ''; +$title .= 'Customer Accounting Summary Report'; + +my @custs = (); +@custs = qsearch('cust_main', {} ); + +my @items = (); +my @params = (); +my @labels = (); + +my $status = $cgi->param('status'); +die "invalid status" unless $status =~ /^\w+|$/; + +foreach my $cust_main ( @custs ) { + next unless ($status eq '' || $status eq $cust_main->status); + next unless ($agentnum == 0 || $cust_main->agentnum eq $agentnum); + + push @items, 'netsales', 'cashflow'; + + push @labels, $cust_main->name; + + push @params, [ ('custnum' => $cust_main->custnum), + ], + [ ('custnum' => $cust_main->custnum), + ]; +} + + diff --git a/httemplate/search/report_customer_accounting_summary.html b/httemplate/search/report_customer_accounting_summary.html new file mode 100755 index 000000000..d20f756c8 --- /dev/null +++ b/httemplate/search/report_customer_accounting_summary.html @@ -0,0 +1,33 @@ +<% include('/elements/header.html', 'Customer Accounting Summary Report' ) %> + +

+ + + + <% include( '/elements/tr-select-agent.html', + 'curr_value' => scalar( $cgi->param('agentnum') ), + 'label' => 'Agent ', + 'disable_empty' => 0, + ) + %> + + <% include('/elements/tr-select-from_to.html' ) %> + + <% include( '/elements/tr-select-cust_main-status.html', + 'label' => 'Customer Status' + ) %> + +
+ +
+ + +
+ +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + + -- 2.11.0