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{debug} = $DEBUG;
19 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
20 my $agent = FS::agent->by_key($1) or die "unknown agentnum $1";
21 $params{agentnum} = $1;
22 $agentname = $agent->agentname;
25 # credit date behavior: limit by the date of the credit application, or
27 if ( $cgi->param('credit_date') eq 'cust_credit_bill' ) {
28 $params{credit_date} = 'cust_credit_bill';
30 $params{credit_date} = 'cust_bill';
33 my $all = $cgi->param('all');
37 $report_class = 'FS::Report::Tax::All';
39 $report_class = 'FS::Report::Tax::ByName';
40 $params{country} = $cgi->param('country');
41 $params{breakdown} = { map { $_ => 1 } $cgi->param('breakdown') };
43 # allow anything in here; FS::Report::Tax will treat it as unsafe
44 if ( length($cgi->param('taxname')) ) {
45 $params{taxname} = $cgi->param('taxname');
47 die "taxname required";
52 warn "REPORT: $report_class\nPARAMS:\n".Dumper(\%params)."\n\n";
56 my $report = $report_class->report(%params);
57 my @rows = $report->table; # array of hashrefs
59 my %pkgclass_name = map { $_->classnum, $_->classname } qsearch('pkg_class');
60 $pkgclass_name{''} = 'Unclassified';
62 my $override = (scalar(@rows) >= 65536 ? 'XLSX' : '');
63 my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format($override);
64 my $filename = 'report_tax'.$format->{extension};
66 http_header('Content-Type' => $format->{mime_type});
67 http_header('Content-Disposition' => qq!attachment;filename="$filename"! );
70 my $XLS = new IO::Scalar \$data;
71 my $workbook = $format->{class}->new($XLS)
72 or die "Error opening .xls file: $!";
74 # hardcoded formats, this could be handled better
75 my $light_gray = $workbook->set_custom_color(63, '#eeeeee');
110 num_format=> 8, # ($#,##0.00_);[Red]($#,##0.00)
116 num_format=> 10, # 0.00%
132 currency_outside => {
138 num_format=> 8, # ($#,##0.00_);[Red]($#,##0.00)
147 30, (13) x 6, 3, 7.5, 3, 11, 11, 3, 11, 3, 11
150 my @format = ( {}, {}, {} ); # white row, gray row, yellow (totals) row
151 foreach (keys(%formatdef)) {
152 my %f = (%default, %{$formatdef{$_}});
153 $format[0]->{$_} = $workbook->add_format(%f);
154 $format[1]->{$_} = $workbook->add_format(bg_color => $light_gray, %f);
155 $format[2]->{$_} = $workbook->add_format(bg_color => 'yellow',
159 my $ws = $workbook->add_worksheet('Sales and Tax');
162 $ws->merge_range(0, 0, 0, 14, $report->title, $format[0]->{title});
168 my $colhead = $format[0]->{colhead};
170 $ws->merge_range($y, 1, $y, 6, 'Sales', $colhead);
171 $ws->merge_range($y, 7, $y+1, 9, 'Rate', $colhead);
172 $ws->merge_range($y, 10, $y, 16, 'Tax', $colhead);
175 $colhead = $format[0]->{colhead_small};
176 $ws->write($y, 1, [ 'Total',
181 'Taxable' ], $colhead);
182 $ws->write($y, 10, 'Estimated', $colhead);
183 $ws->write($y, 11, 'Invoiced', $colhead);
184 $ws->write($y, 13, 'Credited', $colhead);
185 $ws->write($y, 15, 'Net due', $colhead);
186 $ws->write($y, 16, 'Collected',$colhead);
191 my $prev_row = { pkgclass => 'DUMMY PKGCLASS' };
193 foreach my $row (@rows) {
195 if ( $row->{pkgclass} ne $prev_row->{pkgclass} ) {
197 if ( $params{breakdown}->{pkgclass} ) {
198 $ws->merge_range($y, 0, $y, 15,
199 $pkgclass_name{$row->{pkgclass}},
200 $format[0]->{sectionhead}
206 my $f = $format[$rownum % 2];
207 if ( $row->{total} ) {
210 $ws->write($y, $x, $row->{label}, $f->{rowhead});
212 foreach (qw(sales exempt_cust exempt_pkg exempt_monthly sales_credited taxable)) {
213 $ws->write($y, $x, $row->{$_} || 0, $f->{currency});
216 $ws->write_string($y, $x, " \N{U+00D7} ", $f->{bigmath}); # MULTIPLICATION SIGN
218 my $rate = $row->{rate};
219 $rate = $rate / 100 if $rate =~ /^[\d\.]+$/;
220 $ws->write($y, $x, $rate, $f->{number});
222 $ws->write_string($y, $x, " = ", $f->{bigmath});
224 my $estimated = $row->{estimated} || 0;
225 $estimated = '' if $rate eq 'variable';
226 $ws->write($y, $x, $estimated, $f->{currency});
228 $ws->write($y, $x, $row->{tax} || 0, $f->{currency});
230 $ws->write_string($y, $x, " \N{U+2212} ", $f->{bigmath}); # MINUS SIGN
232 $ws->write($y, $x, $row->{tax_credited} || 0, $f->{currency});
234 $ws->write_string($y, $x, " = ", $f->{bigmath});
236 $ws->write($y, $x, $row->{tax} - $row->{tax_credited}, $f->{currency});
238 $ws->write($y, $x, $row->{tax_paid} || 0, $f->{currency});
245 # at the end of everything
246 if ( $report->{out_sales} > 0 ) {
248 $ws->set_row($y, 30); # height
249 $ws->write($y, 0, mt('Out of taxable region'), $f->{rowhead_outside});
250 $ws->write($y, 1, $report->{out_sales}, $f->{currency_outside});
255 for my $x (0..scalar(@widths)-1) {
256 $ws->set_column($x, $x, $widths[$x]);
259 # do the same for the credit worksheet
260 $ws = $workbook->add_worksheet('Credits');
262 my $title = $report->title;
263 $title =~ s/Tax Report/Credits/;
265 $ws->merge_range(0, 0, 0, 14, $title, $format[0]->{title});
266 $ws->set_row(0, 30); # height
271 $colhead = $format[0]->{colhead};
273 $ws->merge_range($y, 1, $y+1, 1, 'Total', $colhead);
274 $ws->merge_range($y, 2, $y, 4, 'Applied to', $colhead);
277 $colhead = $format[0]->{colhead_small};
278 $ws->write($y, 2, [ 'Taxable sales',
286 $prev_row = { pkgclass => 'DUMMY PKGCLASS' };
288 foreach my $row (@rows) {
290 if ( $row->{pkgclass} ne $prev_row->{pkgclass} ) {
292 if ( $params{breakdown}->{pkgclass} ) {
293 $ws->merge_range($y, 0, $y, 4,
294 $pkgclass_name{$row->{pkgclass}},
295 $format[0]->{sectionhead}
301 my $f = $format[$rownum % 2];
302 if ( $row->{total} ) {
305 $ws->write($y, $x, $row->{label}, $f->{rowhead});
307 foreach (qw(credits sales_credited exempt_credited tax_credited)) {
308 $ws->write($y, $x, $row->{$_} || 0, $f->{currency});
317 if ( $report->{out_credit} > 0 ) {
319 $ws->set_row($y, 30); # height
320 $ws->write($y, 0, mt('Out of taxable region'), $f->{rowhead_outside});
321 $ws->write($y, 1, $report->{out_credit}, $f->{currency_outside});
327 $ws->set_column($x, $x, $widths[$x]);
333 http_header('Content-Length' => length($data));