X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Freport_tax-xls.cgi;h=7b936b7294c143c1dd540f33df6b35ddf1336bda;hb=03ab761a53bffa14d09f23fb8b9702806a1c6b79;hp=1c278dfd1af6c7e675bb563b081b88cc64959b22;hpb=fb4ab1073f0d15d660c6cdc4e07afebf68ef3924;p=freeside.git diff --git a/httemplate/search/report_tax-xls.cgi b/httemplate/search/report_tax-xls.cgi index 1c278dfd1..7b936b729 100755 --- a/httemplate/search/report_tax-xls.cgi +++ b/httemplate/search/report_tax-xls.cgi @@ -1,19 +1,67 @@ -<% $data %> <%init> +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, @@ -21,133 +69,139 @@ 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, + num_format=> 8, # ($#,##0.00_);[Red]($#,##0.00) }, - 'size-1' => { - size => 7.5, - align => 'center', - valign => 'vcenter', - bold => 1, - text_wrap => 1, + number => { + size => 11, + align => 'right', + valign => 'bottom', + num_format=> 10, # 0.00% }, - 'size+1' => { + bigmath => { 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 + 18, (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 =~ /