send commission reports by email, #33101

[freeside.git] / httemplate / search / report_tax-xls.cgi

diff --git a/httemplate/search/report_tax-xls.cgi b/httemplate/search/report_tax-xls.cgi
index 7b936b7..743f147 100755 (executable)
--- a/httemplate/search/report_tax-xls.cgi
+++ b/httemplate/search/report_tax-xls.cgi
@@ -99,13 +99,30 @@ my %formatdef = (
     valign    => 'vcenter',
     bold      => 1,
   },
+  rowhead_outside => {
+    size      => 11,
+    align     => 'left',
+    valign    => 'vcenter',
+    bg_color  => 'gray',
+    bold      => 1,
+    italic    => 1,
+  },
+  currency_outside => {
+    size      => 11,
+    align     => 'right',
+    valign    => 'vcenter',
+    bg_color  => 'gray',
+    italic    => 1,
+    num_format=> 8, # ($#,##0.00_);[Red]($#,##0.00)
+  },
+
 );
 my %default = (
   font      => 'Calibri',
   border    => 1,
 );
 my @widths = ( #ick
-  18, (13) x 5, 3, 7.5, 3, 11, 11, 3, 11, 3, 11
+  30, (13) x 5, 3, 7.5, 3, 11, 11, 3, 11, 3, 11
 );
 
 my @format = ( {}, {}, {} ); # white row, gray row, yellow (totals) row
@@ -129,7 +146,7 @@ my $colhead = $format[0]->{colhead};
 # print header
 $ws->merge_range($y, 1, $y, 5, 'Sales', $colhead);
 $ws->merge_range($y, 6, $y+1, 8, 'Rate', $colhead);
-$ws->merge_range($y, 9, $y, 14, 'Tax', $colhead);
+$ws->merge_range($y, 9, $y, 15, 'Tax', $colhead);
 
 $y++;
 $colhead = $format[0]->{colhead_small};
@@ -139,16 +156,17 @@ $ws->write($y, 9, 'Estimated', $colhead);
 $ws->write($y, 10, 'Invoiced', $colhead);
 $ws->write($y, 12, 'Credited', $colhead);
 $ws->write($y, 14, 'Net due',  $colhead);
+$ws->write($y, 15, 'Collected',$colhead);
 $y++;
 
 # print data
-my $rownum = 0;
+my $rownum = 1;
 my $prev_row = { pkgclass => 'DUMMY PKGCLASS' };
 
 foreach my $row (@rows) {
   $x = 0;
   if ( $row->{pkgclass} ne $prev_row->{pkgclass} ) {
-    $rownum = 0;
+    $rownum = 1;
     if ( $params{breakdown}->{pkgclass} ) {
       $ws->merge_range($y, 0, $y, 14,
         $pkgclass_name{$row->{pkgclass}},
@@ -189,12 +207,23 @@ foreach my $row (@rows) {
   $ws->write_string($y, $x, " = ", $f->{bigmath});
   $x++;
   $ws->write($y, $x, $row->{tax} - $row->{credit}, $f->{currency});
+  $x++;
+  $ws->write($y, $x, $row->{tax_paid} || 0, $f->{currency});
 
   $rownum++;
   $y++;
   $prev_row = $row;
 }
 
+# at the end of everything
+if ( $report->{outside} > 0 ) {
+  my $f = $format[0];
+  $ws->set_row($y, 30); # height
+  $ws->write($y, 0, mt('Out of taxable region'), $f->{rowhead_outside});
+  $ws->write($y, 1, $report->{outside}, $f->{currency_outside});
+  $y++;
+}
+
 # ewwwww...
 for my $x (0..scalar(@widths)-1) {
   $ws->set_column($x, $x, $widths[$x]);