httemplate/search/report_tax-xls.cgi

   1 <%init>
   2
   3 die "access denied"
   4   unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
   5
   6 my $DEBUG = $cgi->param('debug') || 0;
   7
   8 my $conf = new FS::Conf;
   9
  10 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
  11
  12 my %params = (
  13   beginning => $beginning,
  14   ending    => $ending,
  15 );
  16 $params{country} = $cgi->param('country');
  17 $params{debug}   = $DEBUG;
  18 $params{breakdown} = { map { $_ => 1 } $cgi->param('breakdown') };
  19
  20 my $agentname;
  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;
  25 }
  26
  27 if ( $cgi->param('taxname') =~ /^([\w ]+)$/ ) {
  28   $params{taxname} = $1;
  29 } else {
  30   die "taxname required";
  31 }
  32
  33 # generate the report
  34 my $report = FS::Report::Tax->report_internal(%params);
  35 my @rows = $report->table; # array of hashrefs
  36
  37 my %pkgclass_name = map { $_->classnum, $_->classname } qsearch('pkg_class');
  38 $pkgclass_name{''} = 'Unclassified';
  39
  40 my $override = (scalar(@rows) >= 65536 ? 'XLSX' : '');
  41 my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format($override);
  42 my $filename = 'report_tax'.$format->{extension};
  43
  44 http_header('Content-Type' => $format->{mime_type});
  45 http_header('Content-Disposition' => qq!attachment;filename="$filename"! );
  46
  47 my $data = '';
  48 my $XLS = new IO::Scalar \$data;
  49 my $workbook = $format->{class}->new($XLS)
  50   or die "Error opening .xls file: $!";
  51
  52 # hardcoded formats, this could be handled better
  53 my $light_gray = $workbook->set_custom_color(63, '#eeeeee');
  54 my %formatdef = (
  55   title => {
  56     size      => 24,
  57     align     => 'center',
  58     bg_color  => 'silver',
  59   },
  60   sectionhead => {
  61     size      => 11,
  62     bold      => 1,
  63     bg_color  => 'silver',
  64   },
  65   colhead => {
  66     size      => 11,
  67     bold      => 1,
  68     align     => 'center',
  69     valign    => 'vcenter',
  70     text_wrap => 1,
  71   },
  72   colhead_small => {
  73     size      => 8,
  74     bold      => 1,
  75     align     => 'center',
  76     valign    => 'vcenter',
  77     text_wrap => 1,
  78   },
  79   rowhead => {
  80     size      => 11,
  81     valign    => 'bottom',
  82     text_wrap => 1,
  83   },
  84   currency => {
  85     size      => 11,
  86     align     => 'right',
  87     valign    => 'bottom',
  88     num_format=> 8, # ($#,##0.00_);[Red]($#,##0.00)
  89   },
  90   number  => {
  91     size      => 11,
  92     align     => 'right',
  93     valign    => 'bottom',
  94     num_format=> 10, # 0.00%
  95   },
  96   bigmath => {
  97     size      => 12,
  98     align     => 'center',
  99     valign    => 'vcenter',
 100     bold      => 1,
 101   },
 102   rowhead_outside => {
 103     size      => 11,
 104     align     => 'left',
 105     valign    => 'vcenter',
 106     bg_color  => 'gray',
 107     bold      => 1,
 108     italic    => 1,
 109   },
 110   currency_outside => {
 111     size      => 11,
 112     align     => 'right',
 113     valign    => 'vcenter',
 114     bg_color  => 'gray',
 115     italic    => 1,
 116     num_format=> 8, # ($#,##0.00_);[Red]($#,##0.00)
 117   },
 118
 119 );
 120 my %default = (
 121   font      => 'Calibri',
 122   border    => 1,
 123 );
 124 my @widths = ( #ick
 125   30, (13) x 6, 3, 7.5, 3, 11, 11, 3, 11, 3, 11
 126 );
 127
 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',
 134                                            italic   => 1,
 135                                            %f);
 136 }
 137 my $ws = $workbook->add_worksheet('Sales and Tax');
 138
 139 # main title
 140 $ws->merge_range(0, 0, 0, 14, $report->title, $format[0]->{title});
 141 $ws->set_row(0, 30);
 142 # excel position
 143 my $x = 0;
 144 my $y = 2;
 145
 146 my $colhead = $format[0]->{colhead};
 147 # print header
 148 $ws->merge_range($y, 1, $y, 6, 'Sales', $colhead);
 149 $ws->merge_range($y, 7, $y+1, 9, 'Rate', $colhead);
 150 $ws->merge_range($y, 10, $y, 16, 'Tax', $colhead);
 151
 152 $y++;
 153 $colhead = $format[0]->{colhead_small};
 154 $ws->write($y, 1, [ 'Total',
 155                     'Exempt customer',
 156                     'Exempt package',
 157                     'Monthly exemption',
 158                     'Credited',
 159                     'Taxable' ], $colhead);
 160 $ws->write($y, 10, 'Estimated', $colhead);
 161 $ws->write($y, 11, 'Invoiced', $colhead);
 162 $ws->write($y, 13, 'Credited', $colhead);
 163 $ws->write($y, 15, 'Net due',  $colhead);
 164 $ws->write($y, 16, 'Collected',$colhead);
 165 $y++;
 166
 167 # print data
 168 my $rownum = 1;
 169 my $prev_row = { pkgclass => 'DUMMY PKGCLASS' };
 170
 171 foreach my $row (@rows) {
 172   $x = 0;
 173   if ( $row->{pkgclass} ne $prev_row->{pkgclass} ) {
 174     $rownum = 1;
 175     if ( $params{breakdown}->{pkgclass} ) {
 176       $ws->merge_range($y, 0, $y, 15,
 177         $pkgclass_name{$row->{pkgclass}},
 178         $format[0]->{sectionhead}
 179       );
 180       $y++;
 181     }
 182   }
 183   # pick a format set
 184   my $f = $format[$rownum % 2];
 185   if ( $row->{total} ) {
 186     $f = $format[2];
 187   }
 188   $ws->write($y, $x, $row->{label}, $f->{rowhead});
 189   $x++;
 190   foreach (qw(sales exempt_cust exempt_pkg exempt_monthly sales_credited taxable)) {
 191     $ws->write($y, $x, $row->{$_} || 0, $f->{currency});
 192     $x++;
 193   }
 194   $ws->write_string($y, $x, " \N{U+00D7} ", $f->{bigmath}); # MULTIPLICATION SIGN
 195   $x++;
 196   my $rate = $row->{rate};
 197   $rate = $rate / 100 if $rate =~ /^[\d\.]+$/;
 198   $ws->write($y, $x, $rate, $f->{number});
 199   $x++;
 200   $ws->write_string($y, $x, " = ", $f->{bigmath});
 201   $x++;
 202   my $estimated = $row->{estimated} || 0;
 203   $estimated = '' if $rate eq 'variable';
 204   $ws->write($y, $x, $estimated, $f->{currency});
 205   $x++;
 206   $ws->write($y, $x, $row->{tax} || 0, $f->{currency});
 207   $x++;
 208   $ws->write_string($y, $x, " \N{U+2212} ", $f->{bigmath}); # MINUS SIGN
 209   $x++;
 210   $ws->write($y, $x, $row->{credit} || 0, $f->{currency});
 211   $x++;
 212   $ws->write_string($y, $x, " = ", $f->{bigmath});
 213   $x++;
 214   $ws->write($y, $x, $row->{tax} - $row->{credit}, $f->{currency});
 215   $x++;
 216   $ws->write($y, $x, $row->{tax_paid} || 0, $f->{currency});
 217
 218   $rownum++;
 219   $y++;
 220   $prev_row = $row;
 221 }
 222
 223 # at the end of everything
 224 if ( $report->{out_sales} > 0 ) {
 225   my $f = $format[0];
 226   $ws->set_row($y, 30); # height
 227   $ws->write($y, 0, mt('Out of taxable region'), $f->{rowhead_outside});
 228   $ws->write($y, 1, $report->{out_sales}, $f->{currency_outside});
 229   $y++;
 230 }
 231
 232 # ewwwww...
 233 for my $x (0..scalar(@widths)-1) {
 234   $ws->set_column($x, $x, $widths[$x]);
 235 }
 236
 237 # do the same for the credit worksheet
 238 $ws = $workbook->add_worksheet('Credits');
 239
 240 my $title = $report->title;
 241 $title =~ s/Tax Report/Credits/;
 242 # main title
 243 $ws->merge_range(0, 0, 0, 14, $title, $format[0]->{title});
 244 $ws->set_row(0, 30); # height
 245 # excel position
 246 $x = 0;
 247 $y = 2;
 248
 249 $colhead = $format[0]->{colhead};
 250 # print header
 251 $ws->merge_range($y, 1, $y+1, 1, 'Total', $colhead);
 252 $ws->merge_range($y, 2, $y, 4, 'Applied to', $colhead);
 253
 254 $y++;
 255 $colhead = $format[0]->{colhead_small};
 256 $ws->write($y, 2, [ 'Taxable sales',
 257                     'Tax-exempt sales',
 258                     'Taxes'
 259                   ], $colhead);
 260 $y++;
 261
 262 # print data
 263 $rownum = 1;
 264 $prev_row = { pkgclass => 'DUMMY PKGCLASS' };
 265
 266 foreach my $row (@rows) {
 267   $x = 0;
 268   if ( $row->{pkgclass} ne $prev_row->{pkgclass} ) {
 269     $rownum = 1;
 270     if ( $params{breakdown}->{pkgclass} ) {
 271       $ws->merge_range($y, 0, $y, 4,
 272         $pkgclass_name{$row->{pkgclass}},
 273         $format[0]->{sectionhead}
 274       );
 275       $y++;
 276     }
 277   }
 278   # pick a format set
 279   my $f = $format[$rownum % 2];
 280   if ( $row->{total} ) {
 281     $f = $format[2];
 282   }
 283   $ws->write($y, $x, $row->{label}, $f->{rowhead});
 284   $x++;
 285   foreach (qw(credits sales_credited exempt_credited tax_credited)) {
 286     $ws->write($y, $x, $row->{$_} || 0, $f->{currency});
 287     $x++;
 288   }
 289
 290   $rownum++;
 291   $y++;
 292   $prev_row = $row;
 293 }
 294
 295 if ( $report->{out_credit} > 0 ) {
 296   my $f = $format[0];
 297   $ws->set_row($y, 30); # height
 298   $ws->write($y, 0, mt('Out of taxable region'), $f->{rowhead_outside});
 299   $ws->write($y, 1, $report->{out_credit}, $f->{currency_outside});
 300   $y++;
 301 }
 302
 303
 304 for my $x (0..4) {
 305   $ws->set_column($x, $x, $widths[$x]);
 306 }
 307
 308
 309 $workbook->close;
 310
 311 http_header('Content-Length' => length($data));
 312 $m->print($data);
 313 </%init>