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