add customer accounting summary report, RT10911

6 files changed, 143 insertions, 17 deletions

diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm
index 376ee5c..2d8dd7e 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 c4422a0..1e7c12b 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 7b1b98a..de2b2e9 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 b831f3a..2a5a142 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'); 
 
 <IMG SRC="<% $cgi->self_url %>" WIDTH="976" HEIGHT="384">
+% }
 <P ALIGN="right">
 
 % 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 0000000..8da8914
--- /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),
+		];
+}
+
+</%init>
diff --git a/httemplate/search/report_customer_accounting_summary.html b/httemplate/search/report_customer_accounting_summary.html
new file mode 100755
index 0000000..d20f756
--- /dev/null
+++ b/httemplate/search/report_customer_accounting_summary.html
@@ -0,0 +1,33 @@
+<% include('/elements/header.html', 'Customer Accounting Summary Report' ) %>
+
+<FORM ACTION="customer_accounting_summary.html" METHOD="GET">
+
+  <TABLE BGCOLOR="#cccccc" CELLSPACING=0>
+
+    <% 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'
+    ) %>
+    
+  </TABLE>
+
+<BR>
+<INPUT TYPE="submit" VALUE="Get Report">
+
+</FORM>
+
+<% include('/elements/footer.html') %>
+<%init>
+
+die "access denied"
+  unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
+
+</%init>