4 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
6 my $DEBUG = $cgi->param('debug') || 0;
8 my $conf = new FS::Conf;
10 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
13 beginning => $beginning,
16 $params{country} = $cgi->param('country');
17 $params{debug} = $DEBUG;
18 $params{breakdown} = { map { $_ => 1 } $cgi->param('breakdown') };
21 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
22 my $agent = FS::agent->by_key($1) or die "unknown agentnum $1";
23 $params{agentnum} = $1;
24 $agentname = $agent->agentname;
27 if ( $cgi->param('taxname') =~ /^([\w ]+)$/ ) {
28 $params{taxname} = $1;
30 die "taxname required";
34 my $report = FS::Report::Tax->report_internal(%params);
35 my @rows = $report->table; # array of hashrefs
37 my %pkgclass_name = map { $_->classnum, $_->classname } qsearch('pkg_class');
38 $pkgclass_name{''} = 'Unclassified';
40 my $override = (scalar(@rows) >= 65536 ? 'XLSX' : '');
41 my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format($override);
42 my $filename = 'report_tax'.$format->{extension};
44 http_header('Content-Type' => $format->{mime_type});
45 http_header('Content-Disposition' => qq!attachment;filename="$filename"! );
48 my $XLS = new IO::Scalar \$data;
49 my $workbook = $format->{class}->new($XLS)
50 or die "Error opening .xls file: $!";
52 # hardcoded formats, this could be handled better
53 my $light_gray = $workbook->set_custom_color(63, '#eeeeee');
88 num_format=> 8, # ($#,##0.00_);[Red]($#,##0.00)
94 num_format=> 10, # 0.00%
110 currency_outside => {
116 num_format=> 8, # ($#,##0.00_);[Red]($#,##0.00)
125 30, (13) x 5, 3, 7.5, 3, 11, 11, 3, 11, 3, 11
128 my @format = ( {}, {}, {} ); # white row, gray row, yellow (totals) row
129 foreach (keys(%formatdef)) {
130 my %f = (%default, %{$formatdef{$_}});
131 $format[0]->{$_} = $workbook->add_format(%f);
132 $format[1]->{$_} = $workbook->add_format(bg_color => $light_gray, %f);
133 $format[2]->{$_} = $workbook->add_format(bg_color => 'yellow',
137 my $ws = $workbook->add_worksheet('taxreport');
140 $ws->merge_range(0, 0, 0, 14, $report->title, $format[0]->{title});
145 my $colhead = $format[0]->{colhead};
147 $ws->merge_range($y, 1, $y, 5, 'Sales', $colhead);
148 $ws->merge_range($y, 6, $y+1, 8, 'Rate', $colhead);
149 $ws->merge_range($y, 9, $y, 14, 'Tax', $colhead);
152 $colhead = $format[0]->{colhead_small};
153 $ws->write($y, 1, [ 'Total', 'Exempt customer', 'Exempt package', 'Monthly exemption',
154 'Taxable' ], $colhead);
155 $ws->write($y, 9, 'Estimated', $colhead);
156 $ws->write($y, 10, 'Invoiced', $colhead);
157 $ws->write($y, 12, 'Credited', $colhead);
158 $ws->write($y, 14, 'Net due', $colhead);
163 my $prev_row = { pkgclass => 'DUMMY PKGCLASS' };
165 foreach my $row (@rows) {
167 if ( $row->{pkgclass} ne $prev_row->{pkgclass} ) {
169 if ( $params{breakdown}->{pkgclass} ) {
170 $ws->merge_range($y, 0, $y, 14,
171 $pkgclass_name{$row->{pkgclass}},
172 $format[0]->{sectionhead}
178 my $f = $format[$rownum % 2];
179 if ( $row->{total} ) {
182 $ws->write($y, $x, $row->{label}, $f->{rowhead});
184 foreach (qw(sales exempt_cust exempt_pkg exempt_monthly taxable)) {
185 $ws->write($y, $x, $row->{$_} || 0, $f->{currency});
188 $ws->write_string($y, $x, " \N{U+00D7} ", $f->{bigmath}); # MULTIPLICATION SIGN
190 my $rate = $row->{rate};
191 $rate = $rate / 100 if $rate =~ /^[\d\.]+$/;
192 $ws->write($y, $x, $rate, $f->{number});
194 $ws->write_string($y, $x, " = ", $f->{bigmath});
196 my $estimated = $row->{estimated} || 0;
197 $estimated = '' if $rate eq 'variable';
198 $ws->write($y, $x, $estimated, $f->{currency});
200 $ws->write($y, $x, $row->{tax} || 0, $f->{currency});
202 $ws->write_string($y, $x, " \N{U+2212} ", $f->{bigmath}); # MINUS SIGN
204 $ws->write($y, $x, $row->{credit} || 0, $f->{currency});
206 $ws->write_string($y, $x, " = ", $f->{bigmath});
208 $ws->write($y, $x, $row->{tax} - $row->{credit}, $f->{currency});
215 # at the end of everything
216 if ( $report->{outside} > 0 ) {
218 $ws->set_row($y, 30); # height
219 $ws->write($y, 0, mt('Out of taxable region'), $f->{rowhead_outside});
220 $ws->write($y, 1, $report->{outside}, $f->{currency_outside});
225 for my $x (0..scalar(@widths)-1) {
226 $ws->set_column($x, $x, $widths[$x]);
231 http_header('Content-Length' => length($data));