X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Freport_tax-xls.cgi;h=c914d5adc3bd60b3c0b51d6600f17a58090e0571;hb=d65927fb45aa1c47d7f593db0142d14799b0fabf;hp=d0ef434f48354c86cc239bb715861b7fb8b1dce0;hpb=642f5b08d9e9ac63252d07523d8f04b9e09752c2;p=freeside.git

diff --git a/httemplate/search/report_tax-xls.cgi b/httemplate/search/report_tax-xls.cgi
index d0ef434f4..c914d5adc 100755
--- a/httemplate/search/report_tax-xls.cgi
+++ b/httemplate/search/report_tax-xls.cgi
@@ -24,8 +24,9 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
   $agentname = $agent->agentname;
 }
 
-if ( $cgi->param('taxname') =~ /^([\w ]+)$/ ) {
-  $params{taxname} = $1;
+# allow anything in here; FS::Report::Tax will treat it as unsafe
+if ( length($cgi->param('taxname')) ) {
+  $params{taxname} = $cgi->param('taxname');
 } else {
   die "taxname required";
 }
@@ -122,7 +123,7 @@ my %default = (
   border    => 1,
 );
 my @widths = ( #ick
-  30, (13) x 5, 3, 7.5, 3, 11, 11, 3, 11, 3, 11
+  30, (13) x 6, 3, 7.5, 3, 11, 11, 3, 11, 3, 11
 );
 
 my @format = ( {}, {}, {} ); # white row, gray row, yellow (totals) row
@@ -134,40 +135,46 @@ foreach (keys(%formatdef)) {
                                            italic   => 1,
                                            %f);
 }
-my $ws = $workbook->add_worksheet('taxreport');
+my $ws = $workbook->add_worksheet('Sales and Tax');
 
 # main title
 $ws->merge_range(0, 0, 0, 14, $report->title, $format[0]->{title});
+$ws->set_row(0, 30);
 # excel position
 my $x = 0;
 my $y = 2;
 
 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, 1, $y, 6, 'Sales', $colhead);
+$ws->merge_range($y, 7, $y+1, 9, 'Rate', $colhead);
+$ws->merge_range($y, 10, $y, 16, 'Tax', $colhead);
 
 $y++;
 $colhead = $format[0]->{colhead_small};
-$ws->write($y, 1, [ 'Total', 'Exempt customer', 'Exempt package', 'Monthly exemption',
+$ws->write($y, 1, [ 'Total',
+                    'Exempt customer',
+                    'Exempt package',
+                    'Monthly exemption',
+                    'Credited',
                     'Taxable' ], $colhead);
-$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, 10, 'Estimated', $colhead);
+$ws->write($y, 11, 'Invoiced', $colhead);
+$ws->write($y, 13, 'Credited', $colhead);
+$ws->write($y, 15, 'Net due',  $colhead);
+$ws->write($y, 16, '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,
+      $ws->merge_range($y, 0, $y, 15,
         $pkgclass_name{$row->{pkgclass}},
         $format[0]->{sectionhead}
       );
@@ -181,7 +188,7 @@ foreach my $row (@rows) {
   }
   $ws->write($y, $x, $row->{label}, $f->{rowhead});
   $x++;
-  foreach (qw(sales exempt_cust exempt_pkg exempt_monthly taxable)) {
+  foreach (qw(sales exempt_cust exempt_pkg exempt_monthly sales_credited taxable)) {
     $ws->write($y, $x, $row->{$_} || 0, $f->{currency});
     $x++;
   }
@@ -201,11 +208,13 @@ foreach my $row (@rows) {
   $x++;
   $ws->write_string($y, $x, " \N{U+2212} ", $f->{bigmath}); # MINUS SIGN
   $x++;
-  $ws->write($y, $x, $row->{credit} || 0, $f->{currency});
+  $ws->write($y, $x, $row->{tax_credited} || 0, $f->{currency});
   $x++;
   $ws->write_string($y, $x, " = ", $f->{bigmath});
   $x++;
-  $ws->write($y, $x, $row->{tax} - $row->{credit}, $f->{currency});
+  $ws->write($y, $x, $row->{tax} - $row->{tax_credited}, $f->{currency});
+  $x++;
+  $ws->write($y, $x, $row->{tax_paid} || 0, $f->{currency});
 
   $rownum++;
   $y++;
@@ -213,11 +222,11 @@ foreach my $row (@rows) {
 }
 
 # at the end of everything
-if ( $report->{outside} > 0 ) {
+if ( $report->{out_sales} > 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});
+  $ws->write($y, 1, $report->{out_sales}, $f->{currency_outside});
   $y++;
 }
 
@@ -226,6 +235,78 @@ for my $x (0..scalar(@widths)-1) {
   $ws->set_column($x, $x, $widths[$x]);
 }
 
+# do the same for the credit worksheet
+$ws = $workbook->add_worksheet('Credits');
+
+my $title = $report->title;
+$title =~ s/Tax Report/Credits/;
+# main title
+$ws->merge_range(0, 0, 0, 14, $title, $format[0]->{title});
+$ws->set_row(0, 30); # height
+# excel position
+$x = 0;
+$y = 2;
+
+$colhead = $format[0]->{colhead};
+# print header
+$ws->merge_range($y, 1, $y+1, 1, 'Total', $colhead);
+$ws->merge_range($y, 2, $y, 4, 'Applied to', $colhead);
+
+$y++;
+$colhead = $format[0]->{colhead_small};
+$ws->write($y, 2, [ 'Taxable sales',
+                    'Tax-exempt sales',
+                    'Taxes'
+                  ], $colhead);
+$y++;
+
+# print data
+$rownum = 1;
+$prev_row = { pkgclass => 'DUMMY PKGCLASS' };
+
+foreach my $row (@rows) {
+  $x = 0;
+  if ( $row->{pkgclass} ne $prev_row->{pkgclass} ) {
+    $rownum = 1;
+    if ( $params{breakdown}->{pkgclass} ) {
+      $ws->merge_range($y, 0, $y, 4,
+        $pkgclass_name{$row->{pkgclass}},
+        $format[0]->{sectionhead}
+      );
+      $y++;
+    }
+  }
+  # pick a format set
+  my $f = $format[$rownum % 2];
+  if ( $row->{total} ) {
+    $f = $format[2];
+  }
+  $ws->write($y, $x, $row->{label}, $f->{rowhead});
+  $x++;
+  foreach (qw(credits sales_credited exempt_credited tax_credited)) {
+    $ws->write($y, $x, $row->{$_} || 0, $f->{currency});
+    $x++;
+  }
+
+  $rownum++;
+  $y++;
+  $prev_row = $row;
+}
+
+if ( $report->{out_credit} > 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->{out_credit}, $f->{currency_outside});
+  $y++;
+}
+
+
+for my $x (0..4) {
+  $ws->set_column($x, $x, $widths[$x]);
+}
+
+
 $workbook->close;
 
 http_header('Content-Length' => length($data));