sales tax report in Excel, RT#10321

2 files changed, 187 insertions, 19 deletions

diff --git a/httemplate/search/report_tax-xls.cgi b/httemplate/search/report_tax-xls.cgi
new file mode 100755
index 000000000..54a17d0b6
--- /dev/null
+++ b/httemplate/search/report_tax-xls.cgi
@@ -0,0 +1,158 @@
+<% $data %>
+<%init>
+
+use Spreadsheet::WriteExcel;
+use Spreadsheet::WriteExcel::Utility 'xl_range_formula';
+use List::Util 'max';
+use HTML::TableExtract 'tree';
+
+my $data = '';
+my $XLS = new IO::Scalar \$data;
+my $workbook = Spreadsheet::WriteExcel->new($XLS)
+  or die "Error opening .xls file: $!";
+
+# hardcoded formats, this could be handled better
+my $light_gray = $workbook->set_custom_color(63, '#eeeeee');
+my %format = (
+  title => {
+    size      => 24,
+    align     => 'center',
+    bg_color  => 'silver',
+  },
+  colhead => {
+    size      => 11,
+    bold      => 1,
+    align     => 'center',
+    valign    => 'vcenter',
+    text_wrap => 1,
+  },
+  rowhead => {
+    size      => 11,
+    valign    => 'bottom',
+    text_wrap => 1,
+  },
+  amount  => {
+    size      => 11,
+    align     => 'right',
+    valign    => 'bottom',
+    num_format=> 8,
+  },
+  'size-1' => {
+    size      => 7.5,
+    align     => 'center',
+    valign    => 'vcenter',
+    bold      => 1,
+    text_wrap => 1,
+  },
+  'size+1' => {
+    size      => 12,
+    align     => 'center',
+    valign    => 'vcenter',
+    bold      => 1,
+  },
+  text => {
+    size      => 11,
+    text_wrap => 1,
+  },
+);
+my %default = (
+  font      => 'Calibri',
+  bg_color  => $light_gray,
+  border    => 1,
+);
+my @widths = ( #ick
+  18, (10.5, 3) x 6, 10.5, 10.5, 3, 10.5, 3, 10.5, 3, 10.5
+);
+foreach (keys(%format)) {
+  my %f = (%default, %{$format{$_}});
+  $format{$_} = $workbook->add_format(%f);
+  $format{"m_$_"} = $workbook->add_format(%f); # for merged cells
+  $format{"t_$_"} = $workbook->add_format(%f, bg_color => 'yellow'); # totals
+}
+my $ws = $workbook->add_worksheet('taxreport');
+
+my $htmldoc = include('report_tax.cgi');
+
+my ($title) = ($htmldoc =~ /<title>\s*(.*)\s*<\/title>/i);
+
+# attribs option: how to locate the table?  It's the only one with class="grid".
+my $te = HTML::TableExtract->new(attribs => {class => 'grid'});
+$te->parse($htmldoc);
+my $table = $te->first_table_found;
+
+my @sheet;
+$sheet[0][0] = {
+  text    => $title,
+  format  => 'title',
+  colspan => '18',
+};  
+# excel position
+my $x = 0;
+my $y = 3;
+foreach my $row ($table->rows()) {
+  $x = 0;
+  $sheet[$y] = [];
+  foreach my $cell (@$row) {
+    if ($cell and ref($cell) eq 'HTML::ElementTable::DataElement') {
+      my $f = 'text';
+      if ( $cell->as_HTML =~ /font/i ) {
+        my ($el) = $cell->content_list;
+        $f = 'size'.$el->attr('size') if $el->attr('size');
+      }
+      elsif ( $cell->as_text =~ /^\$/ ) {
+        $f = 'amount'
+      }
+      elsif ( $cell->tag eq 'th' ) {
+        $f = 'colhead';
+      }
+      elsif ( $x == 0 ) {
+        $f = 'rowhead';
+      }
+      $sheet[$y][$x] = {
+        text    => $cell->as_text,
+        format  => $f,
+        rowspan => $cell->attr('rowspan'),
+        colspan => $cell->attr('colspan'),
+      };
+    }
+    $x++;
+  } #for $cell
+  $y++;
+}
+
+$y = 0;
+foreach my $row (@sheet) {
+  $x = 0;
+  my $t_row = 1 if($row->[0]->{'text'} eq 'Total');
+  foreach my $cell (@$row) {
+    if ($cell) {
+      my $f = $cell->{format};
+      if ($cell->{rowspan} > 1 or $cell->{colspan} > 1) {
+        my $range = xl_range_formula(
+          'Taxreport', 
+          $y,
+          $y - 1 + ($cell->{rowspan} || 1),
+          $x,
+          $x - 1 + ($cell->{colspan} || 1)
+        );
+        $ws->merge_range($range, $cell->{text}, $format{"m_$f"});
+      }
+      else {
+        $f = "t_$f" if $t_row;
+        $ws->write($y, $x, $cell->{text}, $format{$f});
+      }
+    } #if $cell
+    $x++;
+  }
+  $y++;
+}
+
+for my $x (0..scalar(@widths)-1) {
+  $ws->set_column($x, $x, $widths[$x]);
+}
+
+$workbook->close;
+
+http_header('Content-Type' => 'application/vnd.ms-excel');
+http_header('Content-Disposition' => 'attachment;filename="report_tax.xls"');
+</%init>
diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi
index 803b7d48f..93e5b51c9 100755
--- a/httemplate/search/report_tax.cgi
+++ b/httemplate/search/report_tax.cgi
@@ -10,35 +10,45 @@
               )
           )
 %>
+<TD ALIGN="right">
+Download full results<BR>
+as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel spreadsheet</A>
+</TD>
 
 <% include('/elements/table-grid.html') %>
 
   <TR>
-    <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3></TH>
     <TH CLASS="grid" BGCOLOR="#cccccc" COLSPAN=9>Sales</TH>
-    <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
-    <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Rate</TH>
-    <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
-    <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax owed</TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3></TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3>Rate</TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3></TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3>Tax owed</TH>
 % unless ( $cgi->param('show_taxclasses') ) { 
-      <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax invoiced</TH>
-      <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
-      <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax credited</TH>
-      <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
-      <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax collected</TH>
+      <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3>Tax invoiced</TH>
+      <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3></TH>
+      <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3>Tax credited</TH>
+      <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3></TH>
+      <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3>Tax collected</TH>
 % } 
   </TR>
 
   <TR>
-    <TH CLASS="grid" BGCOLOR="#cccccc">Total</TH>
-    <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
-    <TH CLASS="grid" BGCOLOR="#cccccc">Non-taxable<BR><FONT SIZE=-1>(tax-exempt customer)</FONT></TH>
-    <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
-    <TH CLASS="grid" BGCOLOR="#cccccc">Non-taxable<BR><FONT SIZE=-1>(tax-exempt package)</FONT></TH>
-    <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
-    <TH CLASS="grid" BGCOLOR="#cccccc">Non-taxable<BR><FONT SIZE=-1>(monthly exemption)</FONT></TH>
-    <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
-    <TH CLASS="grid" BGCOLOR="#cccccc">Taxable</TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Total</TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=1>Non-taxable</TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=1>Non-taxable</TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=1>Non-taxable</TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Taxable</TH>
+  </TR>
+
+  <TR>
+    <TH CLASS="grid" BGCOLOR="#cccccc"><FONT SIZE=-1>(tax-exempt customer)</FONT></TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc"><FONT SIZE=-1>(tax-exempt package)</FONT></TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc"><FONT SIZE=-1>(monthly exemption)</FONT></TH>
   </TR>
 
 % my $bgcolor1 = '#eeeeee';