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