X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Freport_tax-xls.cgi;h=d0ef434f48354c86cc239bb715861b7fb8b1dce0;hb=6419542b10f8ebb0dada9dcb1a48cf78151ca82a;hp=54a17d0b644680fc3d9247580e522012d018a323;hpb=11686f7c21bed96f563e70212f44044604f8a733;p=freeside.git diff --git a/httemplate/search/report_tax-xls.cgi b/httemplate/search/report_tax-xls.cgi index 54a17d0b6..d0ef434f4 100755 --- a/httemplate/search/report_tax-xls.cgi +++ b/httemplate/search/report_tax-xls.cgi @@ -1,24 +1,67 @@ -<% $data %> <%init> -use Spreadsheet::WriteExcel; -use Spreadsheet::WriteExcel::Utility 'xl_range_formula'; -use List::Util 'max'; -use HTML::TableExtract 'tree'; +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +my $DEBUG = $cgi->param('debug') || 0; + +my $conf = new FS::Conf; + +my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); + +my %params = ( + beginning => $beginning, + ending => $ending, +); +$params{country} = $cgi->param('country'); +$params{debug} = $DEBUG; +$params{breakdown} = { map { $_ => 1 } $cgi->param('breakdown') }; + +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; +} + +if ( $cgi->param('taxname') =~ /^([\w ]+)$/ ) { + $params{taxname} = $1; +} else { + die "taxname required"; +} + +# generate the report +my $report = FS::Report::Tax->report_internal(%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}; + +http_header('Content-Type' => $format->{mime_type}); +http_header('Content-Disposition' => qq!attachment;filename="$filename"! ); my $data = ''; my $XLS = new IO::Scalar \$data; -my $workbook = Spreadsheet::WriteExcel->new($XLS) +my $workbook = $format->{class}->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 = ( +my %formatdef = ( title => { size => 24, align => 'center', bg_color => 'silver', }, + sectionhead => { + size => 11, + bold => 1, + bg_color => 'silver', + }, colhead => { size => 11, bold => 1, @@ -26,133 +69,165 @@ 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 5, 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 $htmldoc = include('report_tax.cgi'); +# main title +$ws->merge_range(0, 0, 0, 14, $report->title, $format[0]->{title}); +# excel position +my $x = 0; +my $y = 2; -my ($title) = ($htmldoc =~ /