1 <& /elements/header.html, "$agentname Tax Report: ".
3 ? time2str('%h %o %Y ', $beginning )
7 ( $ending == 4294967295
9 : time2str('%h %o %Y', $ending )
13 Download full results<BR>
14 as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel spreadsheet</A>
17 <STYLE type="text/css">
19 background-color: #777777;
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 }
30 <& /elements/table-grid.html &>
33 <TH COLSPAN=5>Sales</TH>
35 <TH ROWSPAN=3>Rate</TH>
37 <TH ROWSPAN=3>Estimated tax</TH>
38 <TH ROWSPAN=3>Tax invoiced</TH>
40 <TH ROWSPAN=3>Tax credited</TH>
42 <TH ROWSPAN=3>Net tax due</TH>
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>
53 <TR STYLE="font-size:small">
54 <TH>(tax-exempt customer)</TH>
55 <TH>(tax-exempt package)</TH>
56 <TH>(monthly exemption)</TH>
63 % my ($data, $label) = @_;
64 % if ( ref $data eq 'ARRAY' ) {
65 % # then we've reached the bottom
66 % my (%taxnums, %values);
68 % $taxnums{ $_->[0] } = $_->[1];
69 % $values{ $_->[0] } = $_->[2];
72 <TR CLASS="row<% $row %>">
74 <TD CLASS="rowhead"><% $label |h %></TD>
76 % my $sales = $money_sprintf->(
78 % $values{exempt_cust} +
79 % $values{exempt_pkg} +
80 % $values{exempt_monthly}
83 % foreach my $x (qw(taxable exempt_cust exempt_pkg exempt_monthly)) {
84 % foreach (split(',', $taxnums{$x})) {
85 % $sales_taxnums{$_} = 1;
88 % my $sales_taxnums = join(',', keys %sales_taxnums);
90 <A HREF="<% "$saleslink;$classlink;taxnum=$sales_taxnums" %>">
95 % foreach(qw(cust pkg)) {
97 <A HREF="<% "$saleslink;$classlink;exempt_$_=Y;taxnum=".$taxnums{"exempt_$_"} %>">
98 <% $money_sprintf->($values{"exempt_$_"}) %>
103 <A HREF="<% "$exemptlink;$classlink;taxnum=".$taxnums{"exempt_monthly"} %>">
104 <% $money_sprintf->($values{"exempt_monthly"}) %>
109 <A HREF="<% "$saleslink;$classlink;taxable=1;taxnum=$taxnums{taxable}" %>">
110 <% $money_sprintf->($values{taxable}) %>
115 % foreach(split(',', $taxnums{tax})) {
116 % $rate ||= $taxrates{$_};
117 % if ($rate != $taxrates{$_}) {
118 % $rate = 'variable';
122 % $rate = sprintf('%.2f', $rate) . '%' if ($rate and $rate ne 'variable');
123 <TD CLASS="bigmath"> × </TD>
126 <TD CLASS="bigmath"> = </TD>
127 <TD><% $rate eq 'variable'
129 : $money_sprintf->( $values{taxable} * $rate / 100 ) %>
133 <A HREF="<% "$taxlink;$classlink;taxnum=$taxnums{taxable}" %>">
134 <% $money_sprintf->( $values{tax} ) %>
138 <TD CLASS="bigmath"> − </TD>
140 <A HREF="<% "$creditlink;$classlink;taxnum=$taxnums{credited}" %>">
141 <% $money_sprintf->( $values{credited} ) %>
145 <TD CLASS="bigmath"> = </TD>
146 <TD><% $money_sprintf->( $values{tax} - $values{credited} ) %></TD>
149 % $row = $row ? 0 : 1;
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);
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' });
165 % if ( $breakdown{pkgclass} ) {
167 <TD COLSPAN=19 CLASS="sectionhead"><% $class->classname %></TD>
171 % $classlink = "classnum=".($pkgclass || 0) if $breakdown{pkgclass};
172 % &{ $descend }( $data{$pkgclass}, '' );
175 <TBODY CLASS="total">
176 % &{ $descend }( $total{$pkgclass}, 'Total' );
178 % } # foreach $pkgclass
181 <& /elements/footer.html &>
185 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
187 my $DEBUG = $cgi->param('debug') || 0;
189 my $conf = new FS::Conf;
191 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
193 my ($taxname, $country, %breakdown);
195 if ( $cgi->param('taxname') =~ /^([\w\s]+)$/ ) {
198 die "taxname required"; # UI prevents this
201 if ( $cgi->param('country') =~ /^(\w\w)$/ ) {
204 die "country required";
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';
218 # always break these down
219 $breakdown{'state'} = 'cust_main_county.state';
220 $breakdown{'county'} = 'cust_main_county.county';
222 my $join_cust = ' JOIN cust_bill USING ( invnum )
223 LEFT JOIN cust_main USING ( custnum ) ';
225 my $join_cust_pkg = $join_cust.
226 ' LEFT JOIN cust_pkg USING ( pkgnum )
227 LEFT JOIN part_pkg USING ( pkgpart ) ';
229 my $from_join_cust_pkg = " FROM cust_bill_pkg $join_cust_pkg ";
231 # all queries MUST be linked to both cust_bill and cust_main_county
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";
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";
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{$_}, ";
253 $select .= "NULL AS $_, ";
256 $select .= "array_to_string(array_agg(DISTINCT(cust_main_county.taxnum)), ',') AS taxnums, ";
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, ";
263 if ( $breakdown{pkgclass} ) {
264 $select_all = "SELECT $breakdown{pkgclass} AS pkgclass, ";
265 $group_all = "GROUP BY $breakdown{pkgclass}";
267 $select_all .= "array_to_string(array_agg(DISTINCT(cust_main_county.taxnum)), ',') AS taxnums, ";
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;
277 my $nottax = 'cust_bill_pkg.pkgnum != 0';
279 # one query for each column of the report
280 # plus separate queries for the totals row
283 # SALES QUERIES (taxable sales, all types of exempt sales)
287 my $exempt = "$select SUM(exempt_charged)
288 FROM cust_main_county
289 JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
291 JOIN cust_bill_pkg USING (billpkgnum)
292 $join_cust_pkg $where AND $nottax
295 my $all_exempt = "$select_all SUM(exempt_charged)
296 FROM cust_main_county
297 JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
299 JOIN cust_bill_pkg USING (billpkgnum)
300 $join_cust_pkg $where AND $nottax
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'/;
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'/;
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'/;
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
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
344 $sql{taxable} =~ s/EXEMPT_WHERE//; # unrestricted
345 $all_sql{taxable} =~ s/EXEMPT_WHERE//;
347 # there isn't one for 'sales', because we calculate sales by adding up
348 # the taxable and exempt columns.
350 # TAX QUERIES (billed tax, credited tax)
354 # join cust_bill_pkg to cust_main_county via cust_bill_pkg_tax_location
355 my $taxfrom = " FROM cust_bill_pkg
357 LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum )
358 LEFT JOIN cust_main_county USING ( taxnum )";
360 if ( $breakdown{pkgclass} ) {
361 # If we're not grouping by package class, this is unnecessary, and
362 # probably really expensive.
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)";
370 my $istax = "cust_bill_pkg.pkgnum = 0";
372 $sql{tax} = "$select SUM(cust_bill_pkg_tax_location.amount)
377 $all_sql{tax} = "$select_all SUM(cust_bill_pkg_tax_location.amount)
382 # sum of credits applied against billed tax
383 # ($creditfrom includes join of taxable item to part_pkg if with_pkgclass
385 my $creditfrom = $taxfrom .
386 ' JOIN cust_credit_bill_pkg USING (billpkgtaxlocationnum)';
387 my $creditwhere = $where .
388 ' AND billpkgtaxratelocationnum IS NULL';
390 $sql{credit} = "$select SUM(cust_credit_bill_pkg.amount)
392 $creditwhere AND $istax
395 $all_sql{credit} = "$select_all SUM(cust_credit_bill_pkg.amount)
397 $creditwhere AND $istax
402 my %taxclass_name = { '' => '' };
403 if ( $breakdown{taxclass} ) {
404 $taxclass_name{$_->taxclassnum} = $_->taxclass
405 foreach qsearch('tax_class');
406 $taxclass_name{''} = 'Unclassified';
408 foreach my $k (keys(%sql)) {
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
416 or die "failed to execute $k query: ".$sth->errstr;
417 while ( my $row = $sth->fetchrow_arrayref ) {
420 {$taxclass_name{$row->[1]}}
422 {$row->[3] ? $row->[3] . ' County' : ''}
426 push @$bin, [ $k, $row->[6], $row->[7] ];
429 warn "DATA:\n".Dumper(\%data) if $DEBUG > 1;
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
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] ];
442 warn "TOTALS:\n".Dumper(\%total) if $DEBUG > 1;
444 # $data{$pkgclass}{$taxclass}{$state}{$county}{$city}{$district} = [
445 # [ 'taxable', taxnums, amount ],
446 # [ 'exempt_cust', taxnums, amount ],
449 # non-requested grouping levels simply collapse into key = ''
451 my $money_char = $conf->config('money_char') || '$';
452 my $money_sprintf = sub {
453 $money_char. sprintf('%.2f', shift );
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";
466 qsearch('cust_main_county', {
468 tax => { op => '>', value => 0 }
471 $taxrates{$tax->taxnum} = $tax->tax;