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
 234 # cust_main_county. This one links a taxed line item (billpkgnum) to a tax rate
 235 # (taxnum), and gives the amount of tax charged on that line item under that
 236 # rate (as tax_amount).
 237 my $pkg_tax = "SELECT SUM(amount) as tax_amount, taxnum, ".
 238   "taxable_billpkgnum AS billpkgnum ".
 239   "FROM cust_bill_pkg_tax_location JOIN cust_bill_pkg USING (billpkgnum) ".
 240   "GROUP BY taxable_billpkgnum, taxnum";
 241
 242 # This one links a tax-exempted line item (billpkgnum) to a tax rate (taxnum),
 243 # and gives the amount of the tax exemption.  EXEMPT_WHERE should be replaced
 244 # with a real WHERE clause to further limit the tax exemptions that will be
 245 # included.
 246 my $pkg_tax_exempt = "SELECT SUM(amount) AS exempt_charged, billpkgnum, taxnum ".
 247   "FROM cust_tax_exempt_pkg EXEMPT_WHERE GROUP BY billpkgnum, taxnum";
 248
 249 my $where = "WHERE _date >= $beginning AND _date <= $ending ".
 250             "AND COALESCE(cust_main_county.taxname,'Tax') = '$taxname' ".
 251             "AND cust_main_county.country = '$country'";
 252 # SELECT/GROUP clauses for first-level queries
 253 my $select = "SELECT ";
 254 my $group = "GROUP BY ";
 255 foreach (qw(pkgclass taxclass state county city district)) {
 256   if ( $breakdown{$_} ) {
 257     $select .= "$breakdown{$_} AS $_, ";
 258     $group  .= "$breakdown{$_}, ";
 259   } else {
 260     $select .= "NULL AS $_, ";
 261   }
 262 }
 263 $select .= "array_to_string(array_agg(DISTINCT(cust_main_county.taxnum)), ',') AS taxnums, ";
 264 $group =~ s/, $//;
 265
 266 # SELECT/GROUP clauses for second-level (totals) queries
 267 # breakdown by package class only, if anything
 268 my $select_all = "SELECT NULL AS pkgclass, ";
 269 my $group_all = "";
 270 if ( $breakdown{pkgclass} ) {
 271   $select_all = "SELECT $breakdown{pkgclass} AS pkgclass, ";
 272   $group_all = "GROUP BY $breakdown{pkgclass}";
 273 }
 274 $select_all .= "array_to_string(array_agg(DISTINCT(cust_main_county.taxnum)), ',') AS taxnums, ";
 275
 276 my $agentname = '';
 277 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
 278   my $agent = qsearchs('agent', { 'agentnum' => $1 } );
 279   die "agent not found" unless $agent;
 280   $agentname = $agent->agent;
 281   $where .= ' AND cust_main.agentnum = '. $agent->agentnum;
 282 }
 283
 284 my $nottax =
 285   '(cust_bill_pkg.pkgnum != 0 OR cust_bill_pkg.feepart IS NOT NULL)';
 286
 287 # one query for each column of the report
 288 # plus separate queries for the totals row
 289 my (%sql, %all_sql);
 290
 291 # SALES QUERIES (taxable sales, all types of exempt sales)
 292 # -------------
 293
 294 # general form
 295 my $exempt = "$select 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";
 302
 303 my $all_exempt = "$select_all SUM(exempt_charged)
 304   FROM cust_main_county
 305   JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
 306   USING (taxnum)
 307   JOIN cust_bill_pkg USING (billpkgnum)
 308   $join_cust_pkg $where AND $nottax
 309   $group_all";
 310
 311 # sales to tax-exempt customers
 312 $sql{exempt_cust} = $exempt;
 313 $sql{exempt_cust} =~ s/EXEMPT_WHERE/WHERE exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/;
 314 $all_sql{exempt_cust} = $all_exempt;
 315 $all_sql{exempt_cust} =~ s/EXEMPT_WHERE/WHERE exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/;
 316
 317 # sales of tax-exempt packages
 318 $sql{exempt_pkg} = $exempt;
 319 $sql{exempt_pkg} =~ s/EXEMPT_WHERE/WHERE exempt_setup = 'Y' OR exempt_recur = 'Y'/;
 320 $all_sql{exempt_pkg} = $all_exempt;
 321 $all_sql{exempt_pkg} =~ s/EXEMPT_WHERE/WHERE exempt_setup = 'Y' OR exempt_recur = 'Y'/;
 322
 323 # monthly per-customer exemptions
 324 $sql{exempt_monthly} = $exempt;
 325 $sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/;
 326 $all_sql{exempt_monthly} = $all_exempt;
 327 $all_sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/;
 328
 329 # taxable sales
 330 $sql{taxable} = "$select
 331   SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0))
 332   FROM cust_main_county
 333   JOIN ($pkg_tax) AS pkg_tax USING (taxnum)
 334   JOIN cust_bill_pkg USING (billpkgnum)
 335   LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
 336     ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum
 337         AND pkg_tax_exempt.taxnum = cust_main_county.taxnum)
 338   $join_cust_pkg $where AND $nottax
 339   $group";
 340
 341 $all_sql{taxable} = "$select_all
 342   SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0))
 343   FROM cust_main_county
 344   JOIN ($pkg_tax) AS pkg_tax USING (taxnum)
 345   JOIN cust_bill_pkg USING (billpkgnum)
 346   LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
 347     ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum
 348         AND pkg_tax_exempt.taxnum = cust_main_county.taxnum)
 349   $join_cust_pkg $where AND $nottax
 350   $group_all";
 351
 352 $sql{taxable} =~ s/EXEMPT_WHERE//; # unrestricted
 353 $all_sql{taxable} =~ s/EXEMPT_WHERE//;
 354
 355 # there isn't one for 'sales', because we calculate sales by adding up
 356 # the taxable and exempt columns.
 357
 358 # TAX QUERIES (billed tax, credited tax)
 359 # -----------
 360
 361 # sum of billed tax:
 362 # join cust_bill_pkg to cust_main_county via cust_bill_pkg_tax_location
 363 my $taxfrom = " FROM cust_bill_pkg
 364                 $join_cust
 365                 LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum )
 366                 LEFT JOIN cust_main_county USING ( taxnum )";
 367
 368 if ( $breakdown{pkgclass} ) {
 369   # If we're not grouping by package class, this is unnecessary, and
 370   # probably really expensive.
 371   $taxfrom .= "
 372                 LEFT JOIN cust_bill_pkg AS taxable
 373                   ON (cust_bill_pkg_tax_location.taxable_billpkgnum = taxable.billpkgnum)
 374                 LEFT JOIN cust_pkg ON (taxable.pkgnum = cust_pkg.pkgnum)
 375                 LEFT JOIN part_pkg USING (pkgpart)";
 376 }
 377
 378 my $istax = "cust_bill_pkg.pkgnum = 0";
 379
 380 $sql{tax} = "$select SUM(cust_bill_pkg_tax_location.amount)
 381              $taxfrom
 382              $where AND $istax
 383              $group";
 384
 385 $all_sql{tax} = "$select_all SUM(cust_bill_pkg_tax_location.amount)
 386              $taxfrom
 387              $where AND $istax
 388              $group_all";
 389
 390 # sum of credits applied against billed tax
 391 # ($creditfrom includes join of taxable item to part_pkg if with_pkgclass
 392 # is on)
 393 my $creditfrom = $taxfrom .
 394    ' JOIN cust_credit_bill_pkg USING (billpkgtaxlocationnum)';
 395 my $creditwhere = $where .
 396    ' AND billpkgtaxratelocationnum IS NULL';
 397
 398 $sql{credit} = "$select SUM(cust_credit_bill_pkg.amount)
 399                 $creditfrom
 400                 $creditwhere AND $istax
 401                 $group";
 402
 403 $all_sql{credit} = "$select_all SUM(cust_credit_bill_pkg.amount)
 404                 $creditfrom
 405                 $creditwhere AND $istax
 406                 $group_all";
 407
 408 my %data;
 409 my %total;
 410 my %taxclass_name = { '' => '' };
 411 if ( $breakdown{taxclass} ) {
 412   $taxclass_name{$_->taxclassnum} = $_->taxclass
 413     foreach qsearch('tax_class');
 414   $taxclass_name{''} = 'Unclassified';
 415 }
 416 foreach my $k (keys(%sql)) {
 417   my $stmt = $sql{$k};
 418   warn "\n".uc($k).":\n".$stmt."\n" if $DEBUG;
 419   my $sth = dbh->prepare($stmt);
 420   # eight columns: pkgclass, taxclass, state, county, city, district
 421   # taxnums (comma separated), value
 422   # *sigh*
 423   $sth->execute
 424     or die "failed to execute $k query: ".$sth->errstr;
 425   while ( my $row = $sth->fetchrow_arrayref ) {
 426     my $bin = $data
 427               {$row->[0]}
 428               {$taxclass_name{$row->[1]}}
 429               {$row->[2]}
 430               {$row->[3] ? $row->[3] . ' County' : ''}
 431               {$row->[4]}
 432               {$row->[5]}
 433             ||= [];
 434     push @$bin, [ $k, $row->[6], $row->[7] ];
 435   }
 436 }
 437 warn "DATA:\n".Dumper(\%data) if $DEBUG > 1;
 438
 439 foreach my $k (keys %all_sql) {
 440   warn "\nTOTAL ".uc($k).":\n".$all_sql{$k}."\n" if $DEBUG;
 441   my $sth = dbh->prepare($all_sql{$k});
 442   # three columns: pkgclass, taxnums (comma separated), value
 443   $sth->execute
 444     or die "failed to execute $k totals query: ".$sth->errstr;
 445   while ( my $row = $sth->fetchrow_arrayref ) {
 446     my $bin = $total{$row->[0]} ||= [];
 447     push @$bin, [ $k, $row->[1], $row->[2] ];
 448   }
 449 }
 450 warn "TOTALS:\n".Dumper(\%total) if $DEBUG > 1;
 451
 452 # $data{$pkgclass}{$taxclass}{$state}{$county}{$city}{$district} = [
 453 #   [ 'taxable',     taxnums, amount ],
 454 #   [ 'exempt_cust', taxnums, amount ],
 455 #   ...
 456 # ]
 457 # non-requested grouping levels simply collapse into key = ''
 458
 459 my $money_char = $conf->config('money_char') || '$';
 460 my $money_sprintf = sub {
 461   $money_char. sprintf('%.2f', shift );
 462 };
 463
 464 my $dateagentlink = "begin=$beginning;end=$ending";
 465 $dateagentlink .= ';agentnum='. $cgi->param('agentnum')
 466   if length($agentname);
 467 my $saleslink  = $p. "search/cust_bill_pkg.cgi?$dateagentlink;nottax=1";
 468 my $taxlink    = $p. "search/cust_bill_pkg.cgi?$dateagentlink;istax=1";
 469 my $exemptlink = $p. "search/cust_tax_exempt_pkg.cgi?$dateagentlink";
 470 my $creditlink = $p. "search/cust_bill_pkg.cgi?$dateagentlink;credit=1;istax=1";
 471
 472 my %taxrates;
 473 foreach my $tax (
 474   qsearch('cust_main_county', {
 475             country => $country,
 476             tax => { op => '>', value => 0 }
 477           }) )
 478   {
 479   $taxrates{$tax->taxnum} = $tax->tax;
 480 }
 481
 482 </%init>