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