From: ivan <ivan>
Date: Mon, 8 May 2006 10:01:54 +0000 (+0000)
Subject: sales report per agent and package class looks good
X-Git-Tag: BEFORE_FINAL_MASONIZE~148
X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=896ef9b1a5302dc2d96d351702be94184825cc91

sales report per agent and package class looks good
---

diff --git a/FS/FS/Report/Table/Monthly.pm b/FS/FS/Report/Table/Monthly.pm
index e7f05e5f2..1b7e93aba 100644
--- a/FS/FS/Report/Table/Monthly.pm
+++ b/FS/FS/Report/Table/Monthly.pm
@@ -24,6 +24,11 @@ FS::Report::Table::Monthly - Tables of report data, indexed monthly
     'start_year'  => 2000,
     'end_month'   => 4,
     'end_year'    => 2020,
+    #opt
+    'agentnum'    => 54
+    'params'      => [ [ 'paramsfor', 'item_one' ], [ 'item', 'two' ] ], # ...
+    'remove_empty' => 1, #collapse empty rows, default 0
+    'item_labels' => [ ], #useful with remove_empty
   );
 
   my $data = $report->data;
@@ -41,6 +46,9 @@ Returns a hashref of data (!! describe)
 sub data {
   my $self = shift;
 
+  #use Data::Dumper;
+  #warn Dumper($self);
+
   my $smonth = $self->{'start_month'};
   my $syear = $self->{'start_year'};
   my $emonth = $self->{'end_month'};
@@ -59,24 +67,75 @@ sub data {
     my $eperiod = timelocal(0,0,0,1,$smonth-1,$syear);
     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;
+    }
+
+  }
+
+  #these need to get generalized, sheesh
+  $data{'items'}       = $self->{'items'};
+  $data{'item_labels'} = $self->{'item_labels'} || $self->{'items'};
+  $data{'colors'}      = $self->{'colors'};
+  $data{'links'}       = $self->{'links'} || [];
+
+  #use Data::Dumper;
+  #warn Dumper(\%data);
+
+  if ( $self->{'remove_empty'} ) {
+
+    #warn "removing empty rows\n";
+
+    my $col = 0;
+    #these need to get generalized, sheesh
+    my @newitems = ();
+    my @newlabels = ();
+    my @newdata = ();
+    my @newcolors = ();
+    my @newlinks = ();
     foreach my $item ( @{$self->{'items'}} ) {
-      push @{$data{$item}}, $self->$item($speriod, $eperiod, $agentnum);
+
+      if ( grep { $_ != 0 } @{$data{'data'}->[$col]} ) {
+        push @newitems,  $data{'items'}->[$col];
+        push @newlabels, $data{'item_labels'}->[$col];
+        push @newdata,   $data{'data'}->[$col];
+        push @newcolors, $data{'colors'}->[$col];
+        push @newlinks,  $data{'links'}->[$col];
+      }
+
+      $col++;
     }
 
+    $data{'items'}       = \@newitems;
+    $data{'item_labels'} = \@newlabels;
+    $data{'data'}        = \@newdata;
+    $data{'colors'}      = \@newcolors;
+    $data{'links'}       = \@newlinks;
+
   }
 
+  #use Data::Dumper;
+  #warn Dumper(\%data);
+
   \%data;
 
 }
 
 sub invoiced { #invoiced
   my( $self, $speriod, $eperiod, $agentnum ) = @_;
+
   $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)
   );
+  
 }
 
 sub netsales { #net sales
@@ -197,6 +256,34 @@ sub _subtract_11mo {
   timelocal($sec,$min,$hour,$mday,$mon,$year);
 }
 
+sub cust_bill_pkg {
+  my( $self, $speriod, $eperiod, $agentnum, %opt ) = @_;
+
+  my $where = '';
+  if ( $opt{'classnum'} =~ /^(\d+)$/ ) {
+    if ( $1 == 0 ) {
+      $where = "classnum IS NULL";
+    } else {
+      $where = "classnum = $1";
+    }
+  }
+
+  $agentnum ||= $opt{'agentnum'};
+
+  $self->scalar_sql("
+    SELECT SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)
+      FROM cust_bill_pkg
+        LEFT JOIN cust_bill USING ( invnum )
+        LEFT JOIN cust_main USING ( custnum )
+        LEFT JOIN cust_pkg USING ( pkgnum )
+        LEFT JOIN part_pkg USING ( pkgpart )
+      WHERE pkgnum != 0
+        AND $where
+        AND ". $self->in_time_period_and_agent($speriod, $eperiod, $agentnum)
+  );
+  
+}
+
 # NEEDS TO BE AGENTNUM-capable
 sub canceled { #active
   my( $self, $speriod, $eperiod, $agentnum ) = @_;
diff --git a/httemplate/graph/cust_bill_pkg-graph.cgi b/httemplate/graph/cust_bill_pkg-graph.cgi
deleted file mode 100755
index 637a3bf94..000000000
--- a/httemplate/graph/cust_bill_pkg-graph.cgi
+++ /dev/null
@@ -1,84 +0,0 @@
-<%
-
-#my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
-my ($curmon,$curyear) = (localtime(time))[4,5];
-
-#find first month
-my $syear = $cgi->param('syear') || 1899+$curyear;
-my $smonth = $cgi->param('smonth') || $curmon+1;
-
-#find last month
-my $eyear = $cgi->param('eyear') || 1900+$curyear;
-my $emonth = $cgi->param('emonth') || $curmon+1;
-#if ( $emonth++>12 ) { $emonth-=12; $eyear++; }
-
-# XXX or virtual
-my $agentnum = '';
-if ( $cgi->param('agentnum') =~ /^(\d*)$/ ) {
-  $agentnum = $1;
-}
-
-#my @labels;
-#my %data;
-
-my @items = qw( invoiced netsales credits payments receipts );
-if ( $cgi->param('12mo') == 1 ) {
-  @items = map $_.'_12mo', @items;
-}
-
-my %label = (
- 'invoiced' => 'Gross Sales (invoiced)', 
- 'netsales' => 'Net Sales (invoiced - applied credits)',
- 'credits'  => 'Credits',
- 'payments' => 'Gross Receipts (payments)',
- 'receipts' => 'Net Receipts/Cashflow (payments - refunds)',
-);
-$label{$_.'_12mo'} = $label{$_}. " (previous 12 months)"
-  foreach keys %label;
-
-my %color = (
-  'invoiced' => [ 153, 153, 255 ], #light blue
-  'netsales' => [   0,   0, 204 ], #blue
-  'credits'  => [ 204,   0,   0 ], #red
-  'payments' => [ 153, 204, 153 ], #light green
-  'receipts' => [   0, 204,   0 ], #green
-);
-$color{$_.'_12mo'} = $color{$_}
-  foreach keys %color;
-
-my $report = new FS::Report::Table::Monthly (
-  'items' => \@items,
-  'start_month' => $smonth,
-  'start_year'  => $syear,
-  'end_month'   => $emonth,
-  'end_year'    => $eyear,
-  'agentnum'    => $agentnum,
-);
-my %data = %{$report->data};
-
-#my $chart = Chart::LinesPoints->new(1024,480);
-#my $chart = Chart::LinesPoints->new(768,480);
-my $chart = Chart::LinesPoints->new(976,384);
-
-my $d = 0;
-$chart->set(
-  #'min_val' => 0,
-  'legend' => 'bottom',
-  'colors' => { ( map { 'dataset'.$d++ => $color{$_} } @items ),
-                #'grey_background' => [ 211, 211, 211 ],
-                'grey_background' => 'white',
-                'background' => [ 0xe8, 0xe8, 0xe8 ], #grey
-              },
-  #'grey_background' => 'false',
-  'legend_labels' => [ map { $label{$_} } @items ],
-  'brush_size' => 4,
-  #'pt_size' => 12,
-);
-
-my @data = map { $data{$_} } ( 'label', @items );
-
-http_header('Content-Type' => 'image/png' );
-
-$chart->_set_colors();
-
-%><%= $chart->scalar_png(\@data) %>
diff --git a/httemplate/graph/cust_bill_pkg.cgi b/httemplate/graph/cust_bill_pkg.cgi
index 0dbd222ce..cb8225516 100644
--- a/httemplate/graph/cust_bill_pkg.cgi
+++ b/httemplate/graph/cust_bill_pkg.cgi
@@ -36,17 +36,22 @@ if ( $cgi->param('classnum') =~ /^(\d*)$/ ) {
 }
 
 my $hue = 0;
-my $hue_increment = 145;
+#my $hue_increment = 170;
+#my $hue_increment = 145;
+my $hue_increment = 125;
 
-my @items = ();
+my @items  = ();
 my @params = ();
 my @labels = ();
 my @colors = ();
+my @links  = ();
+
+my $link = "${p}search/cust_bill_pkg.cgi?nottax=1;include_comp_cust=1";
 
 foreach my $agent ( $sel_agent || qsearch('agent', { 'disabled' => '' } ) ) {
 
   my $col_scheme = Color::Scheme->new
-                     ->from_hue($hue)
+                     ->from_hue($hue) #->from_hex($agent->color)
                      ->scheme('analogic')
                    ;
   my @recur_colors = ();
@@ -67,9 +72,13 @@ foreach my $agent ( $sel_agent || qsearch('agent', { 'disabled' => '' } ) ) {
           : ''
       );
 
-    # push some params
-    push @params,
-      [ 'classnum' => ( ref($pkg_class) ? $pkg_class->classnum : '' ) ];
+    my $row_classnum = ref($pkg_class) ? $pkg_class->classnum : 0;
+    my $row_agentnum = $agent->agentnum;
+    push @params, [ 'classnum' => $row_classnum,
+                    'agentnum' => $row_agentnum,
+                  ];
+
+    push @links, "$link;agentnum=$row_agentnum;classnum=$row_classnum;";
 
     @recur_colors = ($col_scheme->colors)[0,4,8,1,5,9]
       unless @recur_colors;
@@ -83,18 +92,21 @@ foreach my $agent ( $sel_agent || qsearch('agent', { 'disabled' => '' } ) ) {
 
 }
 
-use Data::Dumper;
-warn Dumper(\@items);
+#use Data::Dumper;
+#warn Dumper(\@items);
 
 %><%= include('elements/monthly.html',
-                'title'        => $title. 'Sales Report',
+                'title'        => $title. 'Sales Report (Gross)',
                 'graph_type'   => 'Mountain',
                 'items'        => \@items,
                 'params'       => \@params,
                 'labels'       => \@labels,
                 'graph_labels' => \@labels,
                 'colors'       => \@colors,
-                #'links'        => \%link,
+                'links'        => \@links,
+                'remove_empty' => 1,
+                'bottom_total' => 1,
+                'bottom_link'  => "$link;",
                 'start_month'  => $smonth,
                 'start_year'   => $syear,
                 'end_month'    => $emonth,
diff --git a/httemplate/graph/elements/monthly.html b/httemplate/graph/elements/monthly.html
index c45113fa3..3b9f73aa1 100644
--- a/httemplate/graph/elements/monthly.html
+++ b/httemplate/graph/elements/monthly.html
@@ -5,10 +5,10 @@
   # 'title'        => 'Page title',
   # 'items'        => \@items,
   # 'params'       => \@params, # opt,
-  # 'labels'       => \@labels, # or \%labels (keys are items)
-  # 'graph_labels' => \%graph_labels,
-  # 'colors'       => \%colors,
-  # 'links         => \%link, #opt
+  # 'labels'       => \@labels,       # or \%labels (keys are items)
+  # 'graph_labels' => \@graph_labels, # or \%graph_labels,
+  # 'colors'       => \@colors,       # or \%colors,
+  # 'links         => \@links,        # or \%link, #opt
   # 'start_month'  => $smonth,
   # 'start_year'   => $syear,
   # 'end_month'    => $emonth,
@@ -16,26 +16,38 @@
   # 'agentnum'     => $agentnum, #opt
   # 'nototal'      => 1, #opt,
   # 'graph_type'   => 'LinesPoints', #opt
+  # 'remove_empty' => 1, #opt,
+  # 'bottom_total' => 1, #opt,
 
   my(%opt) = @_;
   my @items = @{ $opt{'items'} };
 
-  #foreach my $other (qw( labels graph_labels colors links )) {
-  foreach my $other (qw( labels graph_labels colors )) {
+  foreach my $other (qw( labels graph_labels colors links )) {
+  #foreach my $other (qw( labels graph_labels colors )) {
     if ( ref($opt{$other}) eq 'HASH' ) {
       $opt{$other} = [ map $opt{$other}{$_}, @items ];
     }
   }
 
   my $report = new FS::Report::Table::Monthly (
+
     #'items'       => $opt{'items'},
-    'items'       => \@items,
-    'params'      => $opt{'params'},
-    'start_month' => $opt{'start_month'},
-    'start_year'  => $opt{'start_year'},
-    'end_month'   => $opt{'end_month'},
-    'end_year'    => $opt{'end_year'},
-    'agentnum'    => $opt{'agentnum'},
+    'items'        => \@items,
+    'params'       => $opt{'params'},
+    'item_labels'  => ( $cgi->param('_type') =~ /^(png)$/
+                          ? $opt{'graph_labels'}
+                          : $opt{'labels'}
+                      ),
+    'colors'       => $opt{'colors'},
+    'links'        => $opt{'links'},
+
+    'start_month'  => $opt{'start_month'},
+    'start_year'   => $opt{'start_year'},
+    'end_month'    => $opt{'end_month'},
+    'end_year'     => $opt{'end_year'},
+
+    'agentnum'     => $opt{'agentnum'},
+    'remove_empty' => $opt{'remove_empty'},
   );
   my $data = $report->data;
 
@@ -61,14 +73,15 @@
                             'dataset'.$d++ =>
                               [ map hex($_), unpack 'a2a2a2', $color ]
                           }
-                          @{ $opt{'colors'} }
+                          #@{ $opt{'colors'} }
+                          @{ $data->{'colors'} }
                     ),
                     #'grey_background' => [ 211, 211, 211 ],
                     'grey_background' => 'white',
                     'background' => [ 0xe8, 0xe8, 0xe8 ], #grey
                   },
       #'grey_background' => 'false',
-      'legend_labels' => $opt{'graph_labels'},
+      'legend_labels' => $data->{'item_labels'},
       'brush_size' => 4,
       #'pt_size' => 12,
     );
@@ -112,36 +125,72 @@
 
 </TR>
 
-<% foreach my $row (@items) {
+<% my @bottom_total = ();
+   foreach my $row ( @{ $data->{'items'} } ) {
 
-     my $color = shift( @{ $opt{'colors'} } );
+     #my $color = shift( @{ $opt{'colors'} } );
+     my $color = shift( @{ $data->{'colors'} } );
+     my $link = shift( @{ $data->{'links'} } );
+     $link = $link ? qq(<A HREF="$link) : '';
 %>
 
   <TR>
 
-    <TH><FONT COLOR="#<%= $color %>"><%= shift( @{ $opt{'labels'} } ) %></FONT></TH>
+    <TH><FONT COLOR="#<%= $color %>"><%= shift( @{ $data->{'item_labels'} } ) %></FONT></TH>
 
-    <% my $link = exists($opt{'links'}{$row})
-         ? qq(<A HREF="$opt{'links'}{$row})
-         : '';
+    <% #my $link = exists($opt{'links'}{$row})
+       #  ? qq(<A HREF="$opt{'links'}{$row})
+       #  : '';
        my @speriod = @{$data->{speriod}};
        my @eperiod = @{$data->{eperiod}};
        my $total = 0;
     %>
-    <% foreach my $column ( @{ shift( @{$data->{data}} ) } ) { # ( @{$data->{$row}} ) {
+    <% my $col = 0;
+       foreach my $column ( @{ shift( @{$data->{data}} ) } ) { # ( @{$data->{$row}} ) {
     %>
 
       <TD ALIGN="right" BGCOLOR="#ffffff">
         <%= $link ? $link. 'begin='. shift(@speriod). ';end='. shift(@eperiod). '">' : '' %><FONT COLOR="#<%= $color %>">$<%= sprintf("%.2f", $column) %></FONT><%= $link ? '</A>' : '' %>
       </TD>
-      <% $total += $column; %>
+      <%
+         $total += $column;
+         $bottom_total[$col++] += $column;
+      %>
 
     <% } %>
 
     <% unless ( $opt{'nototal'} ) { %>
 
       <TD ALIGN="right" BGCOLOR="#f5f6be">
-        <%= $link ? $link. 'begin='. @{$data->{speriod}}[0]. ';end='. @{$data->{eperiod}}[-1]. '">' : '' %><FONT COLOR="#<%= $color %>">$<%= sprintf("%.2f", $total) %></FONT><%= $link ? '</A>' : '' %>
+        <%= $link ? $link. 'begin='. ${$data->{speriod}}[0]. ';end='. ${$data->{eperiod}}[-1]. '">' : '' %><FONT COLOR="#<%= $color %>">$<%= sprintf("%.2f", $total) %></FONT><%= $link ? '</A>' : '' %>
+      </TD>
+
+      <% $bottom_total[$col++] += $total; %>
+
+    <% } %>
+
+  </TR>
+
+<% } %>
+
+<% if ( $opt{'bottom_total'} ) {
+     my @speriod = ( @{$data->{speriod}}, ${$data->{speriod}}[0] );
+     my @eperiod = ( @{$data->{eperiod}}, ${$data->{eperiod}}[-1] );
+%>
+
+  <TR>
+    <TH>Total</TH>
+
+    <% foreach my $total ( @bottom_total ) { %>
+
+      <TD ALIGN="right" BGCOLOR="#f5f6be">
+        <%= $opt{'bottom_link'}
+              ? '<A HREF="'. $opt{'bottom_link'}.
+                'begin='. shift(@speriod).
+                ';end='. shift(@eperiod). '">'
+              : ''
+        %>$<%= sprintf("%.2f", $total) %><%= $opt{'bottom_link'} ? '</A>' : '' %>
+
       </TD>
 
     <% } %>
diff --git a/httemplate/index.html b/httemplate/index.html
index 1a9293ecd..c8a46d08f 100644
--- a/httemplate/index.html
+++ b/httemplate/index.html
@@ -95,9 +95,10 @@
               <LI><a href="search/cust_bill_event.html">All invoice events for a date range</a>
               <LI><a href="search/cust_bill_event.html?failed=1">Invoice event errors for a date range (failed credit cards, processor or printer problems, etc.)</a>
             </UL>
-      <A HREF="search/report_cust_pay.html">Payment report (by type and/or date range)</A>
+      <A HREF="graph/report_money_time.html">Sales, Credits and Receipts Summary</A>
+      <BR><BR><A HREF="graph/report_cust_bill_pkg.html">Sales report (by agent, package class and/or date range)</A>
       <BR><BR><A HREF="search/report_cust_credit.html">Credit report (by employee and/or date range)</A>
-      <BR><BR><A HREF="graph/report_money_time.html">Sales, Credits and Receipts Summary</A>
+      <BR><BR><A HREF="search/report_cust_pay.html">Payment report (by type and/or date range)</A>
       <BR><BR><A HREF="search/report_receivables.html">Accounts Receivable Aging Summary</A>
       <BR><BR><A HREF="search/report_prepaid_income.html">Prepaid Income (Unearned Revenue) Report</A>
       <BR><BR><A HREF="search/report_tax.html">Sales Tax Liability Report</A>
diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi
index cc0f97536..4779071a4 100644
--- a/httemplate/search/cust_bill_pkg.cgi
+++ b/httemplate/search/cust_bill_pkg.cgi
@@ -12,15 +12,23 @@ my $join_pkg = "
     LEFT JOIN part_pkg USING ( pkgpart )
 ";
 
-my $where = "
-  WHERE _date >= $beginning AND _date <= $ending
-    AND payby != 'COMP'
-";
+my $where = " WHERE _date >= $beginning AND _date <= $ending ";
+
+$where .= " AND payby != 'COMP' "
+  unless $cgi->param('include_comp_cust');
 
 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
   $where .= " AND agentnum = $1 ";
 }
 
+if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
+  if ( $1 == 0 ) {
+    $where .= " AND classnum IS NULL ";
+  } else {
+    $where .= " AND classnum = $1 ";
+  }
+}
+
 if ( $cgi->param('out') ) {
 
   $where .= "