X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Freport_tax-xls.cgi;h=30b32e8d861936fc398eef9e2e794e98f69b173a;hp=bb843a73f31cc0a324367898fa1e22b19771e791;hb=46fe3dbcb3ca97d1f3c70d49351846cf0ab6461d;hpb=226fffec6fd0154ea8798b58321d4d119341879f

diff --git a/httemplate/search/report_tax-xls.cgi b/httemplate/search/report_tax-xls.cgi
index bb843a73f..30b32e8d8 100755
--- a/httemplate/search/report_tax-xls.cgi
+++ b/httemplate/search/report_tax-xls.cgi
@@ -1,15 +1,65 @@
 <%init>
-my $htmldoc = include('report_tax.cgi');
 
-my ($title) = ($htmldoc =~ /<title>\s*(.*)\s*<\/title>/i);
+die "access denied"
+  unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
 
-# do this first so we can override the format if it's too many rows
-# 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 $DEBUG = $cgi->param('debug') || 0;
 
-my $override = ($table->row_count >= 65536 ? 'XLSX' : '');
+my $conf = new FS::Conf;
+
+my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
+
+my %params = (
+  beginning => $beginning,
+  ending    => $ending,
+);
+$params{debug}   = $DEBUG;
+
+my $agentname;
+if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
+  my $agent = FS::agent->by_key($1) or die "unknown agentnum $1";
+  $params{agentnum} = $1;
+  $agentname = $agent->agentname;
+}
+
+# credit date behavior: limit by the date of the credit application, or
+# the invoice?
+if ( $cgi->param('credit_date') eq 'cust_credit_bill' ) {
+  $params{credit_date} = 'cust_credit_bill';
+} else {
+  $params{credit_date} = 'cust_bill';
+}
+
+my $all = $cgi->param('all');
+my $report_class;
+
+if ( $all ) {
+  $report_class = 'FS::Report::Tax::All';
+} else {
+  $report_class = 'FS::Report::Tax::ByName';
+  $params{country} = $cgi->param('country');
+  $params{breakdown} = { map { $_ => 1 } $cgi->param('breakdown') };
+
+  # 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";
+  }
+}
+
+if ($DEBUG) {
+  warn "REPORT: $report_class\nPARAMS:\n".Dumper(\%params)."\n\n";
+}
+
+# generate the report
+my $report = $report_class->report(%params);
+my @rows = $report->table; # array of hashrefs
+
+my %pkgclass_name = map { $_->classnum, $_->classname } qsearch('pkg_class');
+$pkgclass_name{''} = 'Unclassified';
+
+my $override = (scalar(@rows) >= 65536 ? 'XLSX' : '');
 my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format($override);
 my $filename = 'report_tax'.$format->{extension};
 
@@ -23,12 +73,17 @@ my $workbook = $format->{class}->new($XLS)
 
 # hardcoded formats, this could be handled better
 my $light_gray = $workbook->set_custom_color(63, '#eeeeee');
-my %format = (
+my %formatdef = (
   title => {
     size      => 24,
     align     => 'center',
     bg_color  => 'silver',
   },
+  sectionhead => {
+    size      => 11,
+    bold      => 1,
+    bg_color  => 'silver',
+  },
   colhead => {
     size      => 11,
     bold      => 1,
@@ -36,122 +91,243 @@ my %format = (
     valign    => 'vcenter',
     text_wrap => 1,
   },
+  colhead_small => {
+    size      => 8,
+    bold      => 1,
+    align     => 'center',
+    valign    => 'vcenter',
+    text_wrap => 1,
+  },
   rowhead => {
     size      => 11,
     valign    => 'bottom',
     text_wrap => 1,
   },
-  amount  => {
+  currency => {
+    size      => 11,
+    align     => 'right',
+    valign    => 'bottom',
+    num_format=> 8, # ($#,##0.00_);[Red]($#,##0.00)
+  },
+  number  => {
     size      => 11,
     align     => 'right',
     valign    => 'bottom',
-    num_format=> 8,
+    num_format=> 10, # 0.00%
   },
-  'size-1' => {
-    size      => 7.5,
+  bigmath => {
+    size      => 12,
     align     => 'center',
     valign    => 'vcenter',
     bold      => 1,
-    text_wrap => 1,
   },
-  'size+1' => {
-    size      => 12,
-    align     => 'center',
+  rowhead_outside => {
+    size      => 11,
+    align     => 'left',
     valign    => 'vcenter',
+    bg_color  => 'gray',
     bold      => 1,
+    italic    => 1,
   },
-  text => {
+  currency_outside => {
     size      => 11,
-    text_wrap => 1,
+    align     => 'right',
+    valign    => 'vcenter',
+    bg_color  => 'gray',
+    italic    => 1,
+    num_format=> 8, # ($#,##0.00_);[Red]($#,##0.00)
   },
+
 );
 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
+  30, (13) x 6, 3, 7.5, 3, 11, 11, 3, 11, 3, 11
 );
-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 @format = ( {}, {}, {} ); # white row, gray row, yellow (totals) row
+foreach (keys(%formatdef)) {
+  my %f = (%default, %{$formatdef{$_}});
+  $format[0]->{$_} = $workbook->add_format(%f);
+  $format[1]->{$_} = $workbook->add_format(bg_color => $light_gray, %f);
+  $format[2]->{$_} = $workbook->add_format(bg_color => 'yellow',
+                                           italic   => 1,
+                                           %f);
 }
-my $ws = $workbook->add_worksheet('taxreport');
-
-my @sheet;
-$sheet[0][0] = {
-  text    => $title,
-  format  => 'title',
-  colspan => '18',
-};  
+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 = 3;
-foreach my $row ($table->rows()) {
+my $y = 2;
+
+my $colhead = $format[0]->{colhead};
+# print header
+$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',
+                    'Credited',
+                    'Taxable' ], $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 = 1;
+my $prev_row = { pkgclass => 'DUMMY PKGCLASS' };
+
+foreach my $row (@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'),
-      };
+  if ( $row->{pkgclass} ne $prev_row->{pkgclass} ) {
+    $rownum = 1;
+    if ( $params{breakdown}->{pkgclass} ) {
+      $ws->merge_range($y, 0, $y, 15,
+        $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(sales exempt_cust exempt_pkg exempt_monthly sales_credited taxable)) {
+    $ws->write($y, $x, $row->{$_} || 0, $f->{currency});
     $x++;
-  } #for $cell
+  }
+  $ws->write_string($y, $x, " \N{U+00D7} ", $f->{bigmath}); # MULTIPLICATION SIGN
+  $x++;
+  my $rate = $row->{rate};
+  $rate = $rate / 100 if $rate =~ /^[\d\.]+$/;
+  $ws->write($y, $x, $rate, $f->{number});
+  $x++;
+  $ws->write_string($y, $x, " = ", $f->{bigmath});
+  $x++;
+  my $estimated = $row->{estimated} || 0;
+  $estimated = '' if $rate eq 'variable';
+  $ws->write($y, $x, $estimated, $f->{currency});
+  $x++;
+  $ws->write($y, $x, $row->{tax} || 0, $f->{currency});
+  $x++;
+  $ws->write_string($y, $x, " \N{U+2212} ", $f->{bigmath}); # MINUS SIGN
+  $x++;
+  $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->{tax_credited}, $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->{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->{out_sales}, $f->{currency_outside});
+  $y++;
+}
+
+# ewwwww...
+for my $x (0..scalar(@widths)-1) {
+  $ws->set_column($x, $x, $widths[$x]);
 }
 
-$y = 0;
-foreach my $row (@sheet) {
+# 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;
-  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
+  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;
 }
 
-for my $x (0..scalar(@widths)-1) {
+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));