httemplate/search/report_tax.cgi

   1 <& /elements/header.html, "$agentname Tax Report: ".
   2   ( $beginning
   3       ? time2str('%h %o %Y ', $beginning )
   4       : ''
   5   ).
   6   'through '.
   7   ( $ending == 4294967295
   8       ? 'now'
   9       : time2str('%h %o %Y', $ending )
  10   ). ' - ' . $taxname
  11 &>
  12 <TD ALIGN="right">
  13 Download full results<BR>
  14 as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel spreadsheet</A>
  15 </TD>
  16
  17 <STYLE type="text/css">
  18 TD.sectionhead {
  19   background-color: #777777;
  20   color: #ffffff;
  21   font-weight: bold;
  22   text-align: left;
  23 }
  24 .grid TH { background-color: #cccccc; padding: 0px 3px 2px }
  25 .row0 TD { background-color: #eeeeee; padding: 0px 3px 2px; text-align: right}
  26 .row1 TD { background-color: #ffffff; padding: 0px 3px 2px; text-align: right}
  27 TD.rowhead { font-weight: bold; text-align: left }
  28 .bigmath { font-size: large; font-weight: bold; font: sans-serif; text-align: center }
  29 </STYLE>
  30 <& /elements/table-grid.html &>
  31   <TR>
  32     <TH ROWSPAN=3></TH>
  33     <TH COLSPAN=5>Sales</TH>
  34     <TH ROWSPAN=3></TH>
  35     <TH ROWSPAN=3>Rate</TH>
  36     <TH ROWSPAN=3></TH>
  37     <TH ROWSPAN=3>Estimated tax</TH>
  38     <TH ROWSPAN=3>Tax invoiced</TH>
  39     <TH ROWSPAN=3></TH>
  40     <TH ROWSPAN=3>Tax credited</TH>
  41     <TH ROWSPAN=3></TH>
  42     <TH ROWSPAN=3>Net tax due</TH>
  43   </TR>
  44
  45   <TR>
  46     <TH ROWSPAN=2>Total</TH>
  47     <TH ROWSPAN=1>Non-taxable</TH>
  48     <TH ROWSPAN=1>Non-taxable</TH>
  49     <TH ROWSPAN=1>Non-taxable</TH>
  50     <TH ROWSPAN=2>Taxable</TH>
  51   </TR>
  52
  53   <TR STYLE="font-size:small">
  54     <TH>(tax-exempt customer)</TH>
  55     <TH>(tax-exempt package)</TH>
  56     <TH>(monthly exemption)</TH>
  57   </TR>
  58
  59 % my $row = 0;
  60 % my $classlink = '';
  61 % my $descend;
  62 % $descend = sub {
  63 %   my ($data, $label) = @_;
  64 %   if ( ref $data eq 'ARRAY' ) {
  65 %     # then we've reached the bottom
  66 %     my (%taxnums, %values);
  67 %     foreach (@$data) {
  68 %       $taxnums{ $_->[0] } = $_->[1];
  69 %       $values{ $_->[0] } = $_->[2];
  70 %     }
  71 %     # finally, output
  72   <TR CLASS="row<% $row %>">
  73 %     # Row label
  74     <TD CLASS="rowhead"><% $label |h %></TD>
  75 %     # Total Sales
  76 %     my $sales = $money_sprintf->(
  77 %       $values{taxable} +
  78 %       $values{exempt_cust} +
  79 %       $values{exempt_pkg} +
  80 %       $values{exempt_monthly}
  81 %     );
  82 %     my %sales_taxnums;
  83 %     foreach my $x (qw(taxable exempt_cust exempt_pkg exempt_monthly)) {
  84 %       foreach (split(',', $taxnums{$x})) {
  85 %         $sales_taxnums{$_} = 1;
  86 %       }
  87 %     }
  88 %     my $sales_taxnums = join(',', keys %sales_taxnums);
  89     <TD>
  90       <A HREF="<% "$saleslink;$classlink;taxnum=$sales_taxnums" %>">
  91         <% $sales %>
  92       </A>
  93     </TD>
  94 %     # exemptions
  95 %     foreach(qw(cust pkg)) {
  96     <TD>
  97       <A HREF="<% "$saleslink;$classlink;exempt_$_=Y;taxnum=".$taxnums{"exempt_$_"} %>">
  98         <% $money_sprintf->($values{"exempt_$_"}) %>
  99       </A>
 100     </TD>
 101 %     }
 102     <TD>
 103       <A HREF="<% "$exemptlink;$classlink;taxnum=".$taxnums{"exempt_monthly"} %>">
 104         <% $money_sprintf->($values{"exempt_monthly"}) %>
 105       </A>
 106     </TD>
 107 %     # taxable
 108     <TD>
 109       <A HREF="<% "$saleslink;$classlink;taxable=1;taxnum=$taxnums{taxable}" %>">
 110         <% $money_sprintf->($values{taxable}) %>
 111       </A>
 112     </TD>
 113 %     # tax rate
 114 %     my $rate;
 115 %     foreach(split(',', $taxnums{tax})) {
 116 %       $rate ||= $taxrates{$_};
 117 %       if ($rate != $taxrates{$_}) {
 118 %         $rate = 'variable';
 119 %         last;
 120 %       }
 121 %     }
 122 %     $rate = sprintf('%.2f', $rate) . '%' if ($rate and $rate ne 'variable');
 123     <TD CLASS="bigmath"> &times; </TD>
 124     <TD><% $rate %></TD>
 125 %     # estimated tax
 126     <TD CLASS="bigmath"> = </TD>
 127     <TD><% $rate eq 'variable'
 128             ? ''
 129             : $money_sprintf->( $values{taxable} * $rate / 100 ) %>
 130     </TD>
 131 %     # invoiced tax
 132     <TD>
 133       <A HREF="<% "$taxlink;$classlink;taxnum=$taxnums{taxable}" %>">
 134         <% $money_sprintf->( $values{tax} ) %>
 135       </A>
 136     </TD>
 137 %     # credited tax
 138     <TD CLASS="bigmath"> &minus; </TD>
 139     <TD>
 140       <A HREF="<% "$creditlink;$classlink;taxnum=$taxnums{credited}" %>">
 141         <% $money_sprintf->( $values{credited} ) %>
 142       </A>
 143     </TD>
 144 %     # net tax due
 145     <TD CLASS="bigmath"> = </TD>
 146     <TD><% $money_sprintf->( $values{tax} - $values{credited} ) %></TD>
 147   </TR>
 148
 149 %     $row = $row ? 0 : 1;
 150 %
 151 %   } else { # we're not at the lowest classification
 152 %     my @keys = sort { $a <=> $b or $a cmp $b } keys(%$data);
 153 %     foreach my $key (@keys) {
 154 %       my $sublabel = join(', ', grep $_, $label, $key);
 155 %       &{ $descend }($data->{$key}, $sublabel);
 156 %     }
 157 %   }
 158 % };
 159
 160 % my @pkgclasses = sort { $a <=> $b } keys %data;
 161 % foreach my $pkgclass (@pkgclasses) {
 162 %   my $class = FS::pkg_class->by_key($pkgclass) ||
 163 %               FS::pkg_class->new({ classname => 'Unclassified' });
 164   <TBODY>
 165 %   if ( $breakdown{pkgclass} ) {
 166   <TR>
 167     <TD COLSPAN=19 CLASS="sectionhead"><% $class->classname %></TD>
 168   </TR>
 169 %   }
 170 %   $row = 0;
 171 %   $classlink = "classnum=".($pkgclass || 0) if $breakdown{pkgclass};
 172 %   &{ $descend }( $data{$pkgclass}, '' );
 173 %   # and now totals
 174   </TBODY>
 175   <TBODY CLASS="total">
 176 %   &{ $descend }( $total{$pkgclass}, 'Total' );
 177   </TBODY>
 178 % } # foreach $pkgclass
 179 </TABLE>
 180
 181 <& /elements/footer.html &>
 182 <%init>
 183
 184 die "access denied"
 185   unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
 186
 187 my $DEBUG = $cgi->param('debug') || 0;
 188
 189 my $conf = new FS::Conf;
 190
 191 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 192
 193 my ($taxname, $country, %breakdown);
 194
 195 if ( $cgi->param('taxname') =~ /^([\w\s]+)$/ ) {
 196   $taxname = $1;
 197 } else {
 198   die "taxname required"; # UI prevents this
 199 }
 200
 201 if ( $cgi->param('country') =~ /^(\w\w)$/ ) {
 202   $country = $1;
 203 } else {
 204   die "country required";
 205 }
 206
 207 # %breakdown: short name => field identifier
 208 foreach ($cgi->param('breakdown')) {
 209   if ( $_ eq 'taxclass' ) {
 210     $breakdown{'taxclass'} = 'part_pkg.taxclass';
 211   } elsif ( $_ eq 'pkgclass' ) {
 212     $breakdown{'pkgclass'} = 'part_pkg.classnum';
 213   } elsif ( $_ eq 'city' ) {
 214     $breakdown{'city'} = 'cust_main_county.city';
 215     $breakdown{'district'} = 'cust_main_county.district';
 216   }
 217 }
 218 # always break these down
 219 $breakdown{'state'} = 'cust_main_county.state';
 220 $breakdown{'county'} = 'cust_main_county.county';
 221
 222 my $join_cust =     '      JOIN cust_bill     USING ( invnum  )
 223                       LEFT JOIN cust_main     USING ( custnum ) ';
 224
 225 my $join_cust_pkg = $join_cust.
 226                     ' LEFT JOIN cust_pkg      USING ( pkgnum  )
 227                       LEFT JOIN part_pkg      USING ( pkgpart ) ';
 228
 229 my $from_join_cust_pkg = " FROM cust_bill_pkg $join_cust_pkg ";
 230
 231 # all queries MUST be linked to both cust_bill and cust_main_county
 232
 233 # either or both of these can be used to link cust_bill_pkg to cust_main_county
 234 my $pkg_tax = "SELECT SUM(amount) as tax_amount, invnum, taxnum, ".
 235   "cust_bill_pkg_tax_location.pkgnum ".
 236   "FROM cust_bill_pkg_tax_location JOIN cust_bill_pkg USING (billpkgnum) ".
 237   "GROUP BY billpkgnum, invnum, taxnum, cust_bill_pkg_tax_location.pkgnum";
 238
 239 my $pkg_tax_exempt = "SELECT SUM(amount) AS exempt_charged, billpkgnum, taxnum ".
 240   "FROM cust_tax_exempt_pkg EXEMPT_WHERE GROUP BY billpkgnum, taxnum";
 241
 242 my $where = "WHERE _date >= $beginning AND _date <= $ending ".
 243             "AND COALESCE(cust_main_county.taxname,'Tax') = '$taxname' ".
 244             "AND cust_main_county.country = '$country'";
 245 # SELECT/GROUP clauses for first-level queries
 246 my $select = "SELECT ";
 247 my $group = "GROUP BY ";
 248 foreach (qw(pkgclass taxclass state county city district)) {
 249   if ( $breakdown{$_} ) {
 250     $select .= "$breakdown{$_} AS $_, ";
 251     $group  .= "$breakdown{$_}, ";
 252   } else {
 253     $select .= "NULL AS $_, ";
 254   }
 255 }
 256 $select .= "array_to_string(array_agg(DISTINCT(cust_main_county.taxnum)), ',') AS taxnums, ";
 257 $group =~ s/, $//;
 258
 259 # SELECT/GROUP clauses for second-level (totals) queries
 260 # breakdown by package class only, if anything
 261 my $select_all = "SELECT NULL AS pkgclass, ";
 262 my $group_all = "";
 263 if ( $breakdown{pkgclass} ) {
 264   $select_all = "SELECT $breakdown{pkgclass} AS pkgclass, ";
 265   $group_all = "GROUP BY $breakdown{pkgclass}";
 266 }
 267 $select_all .= "array_to_string(array_agg(DISTINCT(cust_main_county.taxnum)), ',') AS taxnums, ";
 268
 269 my $agentname = '';
 270 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
 271   my $agent = qsearchs('agent', { 'agentnum' => $1 } );
 272   die "agent not found" unless $agent;
 273   $agentname = $agent->agent;
 274   $where .= ' AND cust_main.agentnum = '. $agent->agentnum;
 275 }
 276
 277 my $nottax = 'cust_bill_pkg.pkgnum != 0';
 278
 279 # one query for each column of the report
 280 # plus separate queries for the totals row
 281 my (%sql, %all_sql);
 282
 283 # SALES QUERIES (taxable sales, all types of exempt sales)
 284 # -------------
 285
 286 # general form
 287 my $exempt = "$select SUM(exempt_charged)
 288   FROM cust_main_county
 289   JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
 290   USING (taxnum)
 291   JOIN cust_bill_pkg USING (billpkgnum)
 292   $join_cust_pkg $where AND $nottax
 293   $group";
 294
 295 my $all_exempt = "$select_all SUM(exempt_charged)
 296   FROM cust_main_county
 297   JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
 298   USING (taxnum)
 299   JOIN cust_bill_pkg USING (billpkgnum)
 300   $join_cust_pkg $where AND $nottax
 301   $group_all";
 302
 303 # sales to tax-exempt customers
 304 $sql{exempt_cust} = $exempt;
 305 $sql{exempt_cust} =~ s/EXEMPT_WHERE/WHERE exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/;
 306 $all_sql{exempt_cust} = $all_exempt;
 307 $all_sql{exempt_cust} =~ s/EXEMPT_WHERE/WHERE exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/;
 308
 309 # sales of tax-exempt packages
 310 $sql{exempt_pkg} = $exempt;
 311 $sql{exempt_pkg} =~ s/EXEMPT_WHERE/WHERE exempt_setup = 'Y' OR exempt_recur = 'Y'/;
 312 $all_sql{exempt_pkg} = $all_exempt;
 313 $all_sql{exempt_pkg} =~ s/EXEMPT_WHERE/WHERE exempt_setup = 'Y' OR exempt_recur = 'Y'/;
 314
 315 # monthly per-customer exemptions
 316 $sql{exempt_monthly} = $exempt;
 317 $sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/;
 318 $all_sql{exempt_monthly} = $all_exempt;
 319 $all_sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/;
 320
 321 # taxable sales
 322 $sql{taxable} = "$select
 323   SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0))
 324   FROM cust_main_county
 325   JOIN ($pkg_tax) AS pkg_tax USING (taxnum)
 326   JOIN cust_bill_pkg USING (invnum, pkgnum)
 327   LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
 328     ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum
 329         AND pkg_tax_exempt.taxnum = cust_main_county.taxnum)
 330   $join_cust_pkg $where AND $nottax
 331   $group";
 332
 333 $all_sql{taxable} = "$select_all
 334   SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0))
 335   FROM cust_main_county
 336   JOIN ($pkg_tax) AS pkg_tax USING (taxnum)
 337   JOIN cust_bill_pkg USING (invnum, pkgnum)
 338   LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
 339     ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum
 340         AND pkg_tax_exempt.taxnum = cust_main_county.taxnum)
 341   $join_cust_pkg $where AND $nottax
 342   $group_all";
 343
 344 $sql{taxable} =~ s/EXEMPT_WHERE//; # unrestricted
 345 $all_sql{taxable} =~ s/EXEMPT_WHERE//;
 346
 347 # there isn't one for 'sales', because we calculate sales by adding up
 348 # the taxable and exempt columns.
 349
 350 # TAX QUERIES (billed tax, credited tax)
 351 # -----------
 352
 353 # sum of billed tax:
 354 # join cust_bill_pkg to cust_main_county via cust_bill_pkg_tax_location
 355 my $taxfrom = " FROM cust_bill_pkg
 356                 $join_cust
 357                 LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum )
 358                 LEFT JOIN cust_main_county USING ( taxnum )";
 359
 360 if ( $breakdown{pkgclass} ) {
 361   # If we're not grouping by package class, this is unnecessary, and
 362   # probably really expensive.
 363   $taxfrom .= "
 364                 LEFT JOIN cust_bill_pkg AS taxable
 365                   ON (cust_bill_pkg_tax_location.taxable_billpkgnum = taxable.billpkgnum)
 366                 LEFT JOIN cust_pkg ON (taxable.pkgnum = cust_pkg.pkgnum)
 367                 LEFT JOIN part_pkg USING (pkgpart)";
 368 }
 369
 370 my $istax = "cust_bill_pkg.pkgnum = 0";
 371
 372 $sql{tax} = "$select SUM(cust_bill_pkg_tax_location.amount)
 373              $taxfrom
 374              $where AND $istax
 375              $group";
 376
 377 $all_sql{tax} = "$select_all SUM(cust_bill_pkg_tax_location.amount)
 378              $taxfrom
 379              $where AND $istax
 380              $group_all";
 381
 382 # sum of credits applied against billed tax
 383 # ($creditfrom includes join of taxable item to part_pkg if with_pkgclass
 384 # is on)
 385 my $creditfrom = $taxfrom .
 386    ' JOIN cust_credit_bill_pkg USING (billpkgtaxlocationnum)';
 387 my $creditwhere = $where .
 388    ' AND billpkgtaxratelocationnum IS NULL';
 389
 390 $sql{credit} = "$select SUM(cust_credit_bill_pkg.amount)
 391                 $creditfrom
 392                 $creditwhere AND $istax
 393                 $group";
 394
 395 $all_sql{credit} = "$select_all SUM(cust_credit_bill_pkg.amount)
 396                 $creditfrom
 397                 $creditwhere AND $istax
 398                 $group_all";
 399
 400 my %data;
 401 my %total;
 402 my %taxclass_name = { '' => '' };
 403 if ( $breakdown{taxclass} ) {
 404   $taxclass_name{$_->taxclassnum} = $_->taxclass
 405     foreach qsearch('tax_class');
 406   $taxclass_name{''} = 'Unclassified';
 407 }
 408 foreach my $k (keys(%sql)) {
 409   my $stmt = $sql{$k};
 410   warn "\n".uc($k).":\n".$stmt."\n" if $DEBUG;
 411   my $sth = dbh->prepare($stmt);
 412   # eight columns: pkgclass, taxclass, state, county, city, district
 413   # taxnums (comma separated), value
 414   # *sigh*
 415   $sth->execute
 416     or die "failed to execute $k query: ".$sth->errstr;
 417   while ( my $row = $sth->fetchrow_arrayref ) {
 418     my $bin = $data
 419               {$row->[0]}
 420               {$taxclass_name{$row->[1]}}
 421               {$row->[2]}
 422               {$row->[3] ? $row->[3] . ' County' : ''}
 423               {$row->[4]}
 424               {$row->[5]}
 425             ||= [];
 426     push @$bin, [ $k, $row->[6], $row->[7] ];
 427   }
 428 }
 429 warn "DATA:\n".Dumper(\%data) if $DEBUG > 1;
 430
 431 foreach my $k (keys %all_sql) {
 432   warn "\nTOTAL ".uc($k).":\n".$all_sql{$k}."\n" if $DEBUG;
 433   my $sth = dbh->prepare($all_sql{$k});
 434   # three columns: pkgclass, taxnums (comma separated), value
 435   $sth->execute
 436     or die "failed to execute $k totals query: ".$sth->errstr;
 437   while ( my $row = $sth->fetchrow_arrayref ) {
 438     my $bin = $total{$row->[0]} ||= [];
 439     push @$bin, [ $k, $row->[1], $row->[2] ];
 440   }
 441 }
 442 warn "TOTALS:\n".Dumper(\%total) if $DEBUG > 1;
 443
 444 # $data{$pkgclass}{$taxclass}{$state}{$county}{$city}{$district} = [
 445 #   [ 'taxable',     taxnums, amount ],
 446 #   [ 'exempt_cust', taxnums, amount ],
 447 #   ...
 448 # ]
 449 # non-requested grouping levels simply collapse into key = ''
 450
 451 my $money_char = $conf->config('money_char') || '$';
 452 my $money_sprintf = sub {
 453   $money_char. sprintf('%.2f', shift );
 454 };
 455
 456 my $dateagentlink = "begin=$beginning;end=$ending";
 457 $dateagentlink .= ';agentnum='. $cgi->param('agentnum')
 458   if length($agentname);
 459 my $saleslink  = $p. "search/cust_bill_pkg.cgi?$dateagentlink;nottax=1";
 460 my $taxlink    = $p. "search/cust_bill_pkg.cgi?$dateagentlink;istax=1";
 461 my $exemptlink = $p. "search/cust_tax_exempt_pkg.cgi?$dateagentlink";
 462 my $creditlink = $p. "search/cust_bill_pkg.cgi?$dateagentlink;credit=1;istax=1";
 463
 464 my %taxrates;
 465 foreach my $tax (
 466   qsearch('cust_main_county', {
 467             country => $country,
 468             tax => { op => '>', value => 0 }
 469           }) )
 470   {
 471   $taxrates{$tax->taxnum} = $tax->tax;
 472 }
 473
 474 </%init>