Excel download of Agent Commission report, #22090

1 files changed, 94 insertions, 6 deletions

diff --git a/httemplate/search/agent_commission.html b/httemplate/search/agent_commission.html
index b8fbe200f..b94ae9f6e 100644
--- a/httemplate/search/agent_commission.html
+++ b/httemplate/search/agent_commission.html
@@ -1,6 +1,12 @@
 %# still not a good way to do rows grouped by some field in a search.html 
 %# report
+% if ( $type eq 'xls' ) {
+<% $data %>\
+% } else {
 <& /elements/header.html, $title &>
+<P ALIGN="right" CLASS="noprint">
+Download full results<BR>
+as <A HREF="<% $cgi->self_url %>;_type=xls">Excel spreadsheet</A></P>
 <BR>
 <STYLE TYPE="text/css">
 td.cust_head {
@@ -58,6 +64,7 @@ td.money:before { content: '<% $money_char %>'; }
   </TR>
 </TABLE>
 <& /elements/footer.html &>
+% }
 <%init>
 die "access denied" 
   unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
@@ -100,10 +107,91 @@ my @cust_pkg = qsearch($query);
 
 my $money_char = FS::Conf->new->config('money_char') || '$';
 
-#my $count_query = 
-#  'SELECT COUNT(*) FROM cust_pkg '.$query->{'addl_from'}.$query->{'extra_sql'}.
-#  ' AND EXISTS(SELECT 1 FROM cust_bill_pkg JOIN cust_bill USING (invnum) '.
-#    ' WHERE cust_bill_pkg.pkgnum = cust_pkg.pkgnum AND '.
-#    "cust_bill._date >= $begin AND cust_bill._date < $end".
-#  ')';
+my $data = '';
+my $type = $cgi->param('_type');
+if ( $type eq 'xls') {
+  # some false laziness with the above...
+  my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format;
+  my $filename = 'agent_commission' . $format->{extension}; 
+  http_header('Content-Type' => $format->{mime_type});
+  http_header('Content-Disposition' => qq!attachment;filename="$filename"!);
+  my $XLS = IO::Scalar->new(\$data);
+  my $workbook = $format->{class}->new($XLS);
+  my $worksheet = $workbook->add_worksheet(substr($title, 0, 31));
+
+  my $cust_head_format = $workbook->add_format(
+    bold      => 1,
+    underline => 1,
+    text_wrap => 0,
+    bg_color  => 'white',
+  );
+
+  my $col_head_format = $workbook->add_format(
+    bold      => 1,
+    align     => 'center',
+    bg_color  => 'silver'
+  );
+
+  my @format;
+  foreach (0, 1) {
+    my %bg = (bg_color => $_ ? 'white' : 'silver');
+    $format[$_] = {
+      'text'    => $workbook->add_format(%bg),
+      'money'   => $workbook->add_format(%bg, num_format => $money_char.'#0.00'),
+      'percent' => $workbook->add_format(%bg, num_format => '0.00%'),
+    };
+  }
+  my $total_format = $workbook->add_format(
+    bg_color    => 'yellow',
+    num_format  => $money_char.'#0.00',
+    top         => 1
+  );
+
+  my ($r, $c) = (0, 0);
+  foreach (qw(Package Sales Percentage Commission)) {
+    $worksheet->write($r, $c++, $_, $col_head_format);
+  }
+  $r++;
+
+  my ($custnum, $sales, $commission, $row, $bgcolor) = (0, 0, 0, 0);
+  my $label_length = 0;
+  foreach my $cust_pkg ( @cust_pkg ) {
+    if ( $custnum ne $cust_pkg->custnum ) {
+      # start of a new customer section
+      my $cust_main = $cust_pkg->cust_main;
+      my $label = $cust_main->custnum . ': '. $cust_main->name;
+      $bgcolor = 0;
+      $worksheet->set_row($r, 20);
+      $worksheet->merge_range($r, 0, $r, 3, $label, $cust_head_format);
+      $r++;
+    }
+    $c = 0;
+    my $percent = $cust_pkg->percent / 100;
+    $worksheet->write($r, $c++, $cust_pkg->pkg_label, $format[$bgcolor]{text});
+    $worksheet->write($r, $c++, $cust_pkg->sum_charged, $format[$bgcolor]{money});
+    $worksheet->write($r, $c++, $percent, $format[$bgcolor]{percent});
+    $worksheet->write($r, $c++, ($cust_pkg->sum_charged * $percent),
+                                $format[$bgcolor]{money});
+
+    $label_length = max($label_length, length($cust_pkg->pkg_label));
+    $sales += $cust_pkg->sum_charged;
+    $commission += $cust_pkg->sum_charged * $cust_pkg->percent / 100;
+    $row++;
+    $bgcolor = 1-$bgcolor;
+    $custnum = $cust_pkg->custnum;
+    $r++;
+  }
+
+  $c = 0;
+  $label_length = max($label_length, 20);
+  $worksheet->set_column($c, $c, $label_length);
+  $worksheet->write($r, $c++, mt('[quant,_1,package] with commission', $row),
+                                  $total_format);
+  $worksheet->set_column($c, $c + 2, 11);
+  $worksheet->write($r, $c++, $sales, $total_format);
+  $worksheet->write($r, $c++, '', $total_format);
+  $worksheet->write($r, $c++, $commission, $total_format);
+
+  $workbook->close;
+}
 </%init>