X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Freport_tax.cgi;h=9926133be847dcb9c154b0e7da70b56bd595e006;hb=9fae251fc1e3069694ebaf4fae62bde844f45cff;hp=8062479b5982d53d008309b2167a1e6235a97273;hpb=b50b2e5f94774268c271484f9c07bfe316f95527;p=freeside.git diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index 8062479b5..9926133be 100755 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -1,94 +1,817 @@ -<% -#!/usr/bin/perl -Tw -# -# $Id: report_tax.cgi,v 1.1 2002-02-22 23:18:34 jeff Exp $ -# -# Usage: post form to: -# http://server.name/path/svc_domain.cgi -# -# ivan@voicenet.com 96-mar-5 -# -# need to look at table in results to make it more readable -# -# ivan@voicenet.com -# -# rewrite ivan@sisd.com 98-mar-15 -# -# Changes to allow page to work at a relative position in server -# bmccane@maxbaud.net 98-apr-3 -# -# $Log: report_tax.cgi,v $ -# Revision 1.1 2002-02-22 23:18:34 jeff -# add some reporting features -# -# Revision 1.1 2002/02/05 15:22:00 jeff -# preserving state prior to 1.4.0pre7 upgrade -# -# Revision 1.2 2000/09/20 19:25:19 jeff -# local modifications -# -# Revision 1.1.1.1 2000/09/18 06:26:58 jeff -# Import of Freeside 1.2.3 -# -# Revision 1.10 1999/07/20 06:03:36 ivan -# s/CGI::Request/CGI/; (how'd i miss that before?) -# -# Revision 1.9 1999/04/09 04:22:34 ivan -# also table() -# -# Revision 1.8 1999/04/09 03:52:55 ivan -# explicit & for table/itable/ntable -# -# Revision 1.7 1999/02/28 00:03:56 ivan -# removed misleading comments -# -# Revision 1.6 1999/02/09 09:22:58 ivan -# visual and bugfixes -# -# Revision 1.5 1999/01/19 05:14:16 ivan -# for mod_perl: no more top-level my() variables; use vars instead -# also the last s/create/new/; -# -# Revision 1.4 1999/01/18 09:41:40 ivan -# all $cgi->header calls now include ( '-expires' => 'now' ) for mod_perl -# (good idea anyway) -# -# Revision 1.3 1998/12/17 09:41:11 ivan -# s/CGI::(Base|Request)/CGI.pm/; -# - -use strict; -use vars qw( $conf $cgi $beginning $ending ); -use CGI; -use CGI::Carp qw(fatalsToBrowser); -use FS::UID qw(cgisuidsetup); -use FS::CGI qw(popurl idiot header table); -use FS::Record qw(qsearch qsearchs); -use FS::Conf; - -$cgi = new CGI; -&cgisuidsetup($cgi); - -$conf = new FS::Conf; - -$cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/; -$beginning = $1; - -$cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/; -$ending = $1; - - print $cgi->header( '-expires' => '-2m' ), - header('Tax Report Results'); - - open (REPORT, "/usr/bin/freeside-tax-report -v -s $beginning -d $ending freeside |"); - - print '
'; - while('; +} +warn "DATA:\n".Dumper(\%data) if $DEBUG > 1; - print ') { - print $_; +<% include("/elements/header.html", "$agentname Tax Report - ". + ( $beginning + ? time2str('%h %o %Y ', $beginning ) + : '' + ). + 'through '. + ( $ending == 4294967295 + ? 'now' + : time2str('%h %o %Y', $ending ) + ) + ) +%> + +Download full results + + +<% include('/elements/table-grid.html') %> +
+as Excel spreadsheet ++ + ++ Sales ++ Rate ++ Tax owed +% unless ( $cgi->param('show_taxclasses') ) { +Tax invoiced ++ Tax credited ++ Tax collected +% } ++ + +Total ++ Non-taxable ++ Non-taxable ++ Non-taxable ++ Taxable ++ + +% foreach my $class (@pkgclasses ) { +% next if @{ $class->{regions} } == 0; +% if ( $class->{classname} ) { +(tax-exempt customer) +(tax-exempt package) +(monthly exemption) ++ +% } + +% my $bgcolor1 = '#eeeeee'; +% my $bgcolor2 = '#ffffff'; +% my $bgcolor; + +% my @regions = @{ $class->{regions} }; +% foreach my $region ( @regions ) { +% +% my $link = ''; +% if ( $with_pkgclass and length($class->{classnum}) ) { +% $link = ';classnum='.$class->{classnum}; +% } # else we're not breaking down pkg class, or this is the grand total +% +% if ( $region->{'label'} eq $out ) { +% $link .= ';out=1'; +% } elsif ( $region->{'taxnums'} ) { +% # might be nicer to specify this as country:state:city +% $link .= ';'.join(';', map { "taxnum=$_" } @{ $region->{'taxnums'} }); +% } +% +% if ( $bgcolor eq $bgcolor1 ) { +% $bgcolor = $bgcolor2; +% } else { +% $bgcolor = $bgcolor1; +% } +% +% my $hicolor = $bgcolor; +% unless ( $cgi->param('show_taxclasses') ) { +% my $diff = abs( sprintf( '%.2f', $region->{'owed'} ) +% - sprintf( '%.2f', $region->{'tax'} ) +% ); +% if ( $diff > 0.02 ) { +% $hicolor = $hicolor eq '#eeeeee' ? '#eeee99' : '#ffffcc'; +% } +% } +% +% +% my $td = qq(TD CLASS="grid" BGCOLOR="$bgcolor"); +% my $tdh = qq(TD CLASS="grid" BGCOLOR="$hicolor"); +% my $bigmath = ''; +% my $bme = ''; + +% if ( $region->{'is_total'} ) { +<% $class->{classname} %> ++ Total +% } else { ++ <<%$td%>><% $region->{'label'} %> +% } + <<%$td%> ALIGN="right"> + <% &$money_sprintf( $region->{'sales'} ) %> + +% if ( $region->{'label'} eq $out ) { + <<%$td%> COLSPAN=12> +% } else { #not $out + <<%$td%>> - + <<%$td%> ALIGN="right"> + <% &$money_sprintf( $region->{'exempt_cust'} ) %> + + <<%$td%>> - + <<%$td%> ALIGN="right"> + <% &$money_sprintf( $region->{'exempt_pkg'} ) %> + + <<%$td%>> - + <<%$td%> ALIGN="right"> + <% &$money_sprintf( $region->{'exempt_monthly'} ) %> + + <<%$td%>> = + <<%$td%> ALIGN="right"> + <% &$money_sprintf( $region->{'taxable'} ) %> + + <<%$td%>><% $region->{'label'} eq 'Total' ? '' : "$bigmath X $bme" %> + <<%$td%> ALIGN="right"><% $region->{'rate'} %> + <<%$td%>><% $region->{'label'} eq 'Total' ? '' : "$bigmath = $bme" %> + <<%$tdh%> ALIGN="right"> + <% &$money_sprintf( $region->{'owed'} ) %> + +% } # if !$out +% unless ( $cgi->param('show_taxclasses') ) { +% my $invlink = $region->{'url_param_inv'} +% ? ';'. $region->{'url_param_inv'} +% : $link; + +% if ( $region->{'label'} eq $out ) { + <<%$td%> ALIGN="right"> + <% &$money_sprintf_nonzero( $region->{'tax'} ) %> + + <<%$td%>> + <<%$td%> ALIGN="right"> + <% &$money_sprintf_nonzero( $region->{'credit'} ) %> + + <<%$td%> COLSPAN=2> +% } else { #not $out + <<%$tdh%> ALIGN="right"> + <% &$money_sprintf( $region->{'tax'} ) %> + + <<%$tdh%>> - + <<%$tdh%> ALIGN="right"> + <% &$money_sprintf( $region->{'credit'} ) %> + + <<%$tdh%>> = + <<%$tdh%> ALIGN="right"> + <% &$money_sprintf( $region->{'tax'} - $region->{'credit'} ) %> + +% } +% } # show_taxclasses + + +% } # foreach $region + +%} # foreach $class + + + +% if ( $cgi->param('show_taxclasses') ) { + +
+ <% include('/elements/table-grid.html') %> ++ + +% #some false laziness w/above +% foreach my $class (@pkgclasses) { +% if ( $class->{classname} ) { ++ Tax invoiced ++ Tax credited ++ Tax collected ++ +% } + +% my $bgcolor1 = '#eeeeee'; +% my $bgcolor2 = '#ffffff'; +% my $bgcolor; +% +% foreach my $region ( @{ $class->{base_regions} } ) { +% +% my $link = ''; +% if ( $with_pkgclass and length($class->{classnum}) ) { +% $link = ';classnum='.$class->{classnum}; +% } +% +% if ( $region->{'label'} eq $out ) { +% $link .= ';out=1'; +% } else { +% $link .= ';'. $region->{'url_param'} +% if $region->{'url_param'}; +% } +% +% if ( $bgcolor eq $bgcolor1 ) { +% $bgcolor = $bgcolor2; +% } else { +% $bgcolor = $bgcolor1; +% } +% my $td = qq(TD CLASS="grid" BGCOLOR="$bgcolor"); +% my $tdh = qq(TD CLASS="grid" BGCOLOR="$bgcolor"); +% +% #? +% my $invlink = $region->{'url_param_inv'} +% ? ';'. $region->{'url_param_inv'} +% : $link; + +<% $class->{classname} %> ++ <<%$td%>><% $region->{'label'} %> +% if ( $region->{'label'} eq $out ) { + <<%$td%> ALIGN="right"> + <% &$money_sprintf_nonzero( $region->{'tax'} ) %> + + <<%$td%>> + <<%$td%> ALIGN="right"> + <% &$money_sprintf_nonzero( $region->{'credit'} ) %> + + <<%$td%> COLSPAN=2> +% } else { #not $out + <<%$td%> ALIGN="right"> + <% &$money_sprintf( $region->{'tax'} ) %> + + <<%$td%>> - + <<%$tdh%> ALIGN="right"> + <% &$money_sprintf( $region->{'credit'} ) %> + + <<%$td%>> = + <<%$tdh%> ALIGN="right"> + <% &$money_sprintf( $region->{'tax'} - $region->{'credit'} ) %> + + +% } # if $out +% } #foreach $region +% } #foreach $class + + + +% } # if show_taxclasses + +<% include('/elements/footer.html') %> + +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +my $DEBUG = $cgi->param('debug') || 0; + +my $conf = new FS::Conf; + +my $out = 'Out of taxable region(s)'; + +my %label_opt = ( out => 1 ); #enable 'Out of Taxable Region' label +$label_opt{with_city} = 1 if $cgi->param('show_cities'); +$label_opt{with_district} = 1 if $cgi->param('show_districts'); + +$label_opt{with_taxclass} = 1 if $cgi->param('show_taxclasses'); + +my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); + +my $join_cust = ' JOIN cust_bill USING ( invnum ) + LEFT JOIN cust_main USING ( custnum ) '; + +my $join_cust_pkg = $join_cust. + ' LEFT JOIN cust_pkg USING ( pkgnum ) + LEFT JOIN part_pkg USING ( pkgpart ) '; + +my $from_join_cust_pkg = " FROM cust_bill_pkg $join_cust_pkg "; + +my $with_pkgclass = $cgi->param('show_pkgclasses'); + +# Either or both of these can be used to link cust_bill_pkg to +# cust_main_county. This one links a taxed line item (billpkgnum) to a tax rate +# (taxnum), and gives the amount of tax charged on that line item under that +# rate (as tax_amount). +my $pkg_tax = "SELECT SUM(amount) as tax_amount, taxnum, ". + "taxable_billpkgnum AS billpkgnum ". + "FROM cust_bill_pkg_tax_location JOIN cust_bill_pkg USING (billpkgnum) ". + "GROUP BY taxable_billpkgnum, taxnum"; + +# This one links a tax-exempted line item (billpkgnum) to a tax rate (taxnum), +# and gives the amount of the tax exemption. EXEMPT_WHERE should be replaced +# with a real WHERE clause to further limit the tax exemptions that will be +# included. +my $pkg_tax_exempt = "SELECT SUM(amount) AS exempt_charged, billpkgnum, taxnum ". + "FROM cust_tax_exempt_pkg EXEMPT_WHERE GROUP BY billpkgnum, taxnum"; + +my $where = "WHERE cust_bill._date >= $beginning AND cust_bill._date <= $ending "; +# SELECT/GROUP clauses for first-level queries +# classnum is a placeholder; they all go in one class in this case. +my $select = "SELECT NULL AS classnum, cust_main_county.taxnum, "; +my $group = "GROUP BY cust_main_county.taxnum"; +# SELECT/GROUP clauses for second-level (totals) queries +my $select_all = "SELECT NULL AS classnum, "; +my $group_all = ""; + +if ( $with_pkgclass ) { + $select = "SELECT COALESCE(part_pkg.classnum,0), cust_main_county.taxnum, "; + $group = "GROUP BY part_pkg.classnum, cust_main_county.taxnum"; + $select_all = "SELECT COALESCE(part_pkg.classnum,0), "; + $group_all = "GROUP BY COALESCE(part_pkg.classnum,0)"; +} + +my $agentname = ''; +if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + my $agent = qsearchs('agent', { 'agentnum' => $1 } ); + die "agent not found" unless $agent; + $agentname = $agent->agent; + $where .= ' AND cust_main.agentnum = '. $agent->agentnum; +} + +my $nottax = + '(cust_bill_pkg.pkgnum != 0 OR cust_bill_pkg.feepart IS NOT NULL)'; + +# one query for each column of the report +# plus separate queries for the totals row +my (%sql, %all_sql); + +# SALES QUERIES (taxable sales, all types of exempt sales) +# ------------- + +# general form +my $exempt = "$select SUM(exempt_charged) + FROM cust_main_county + JOIN ($pkg_tax_exempt) AS pkg_tax_exempt + USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust_pkg $where AND $nottax $group"; + +my $all_exempt = "$select_all SUM(exempt_charged) + FROM cust_main_county + JOIN ($pkg_tax_exempt) AS pkg_tax_exempt + USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust_pkg $where AND $nottax + $group_all"; + +# sales to tax-exempt customers +$sql{exempt_cust} = $exempt; +$sql{exempt_cust} =~ s/EXEMPT_WHERE/WHERE exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/; +$all_sql{exempt_cust} = $all_exempt; +$all_sql{exempt_cust} =~ s/EXEMPT_WHERE/WHERE exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/; + +# sales of tax-exempt packages +$sql{exempt_pkg} = $exempt; +$sql{exempt_pkg} =~ s/EXEMPT_WHERE/WHERE exempt_setup = 'Y' OR exempt_recur = 'Y'/; +$all_sql{exempt_pkg} = $all_exempt; +$all_sql{exempt_pkg} =~ s/EXEMPT_WHERE/WHERE exempt_setup = 'Y' OR exempt_recur = 'Y'/; + +# monthly per-customer exemptions +$sql{exempt_monthly} = $exempt; +$sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/; +$all_sql{exempt_monthly} = $all_exempt; +$all_sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/; + +# taxable sales +$sql{taxable} = "$select + SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0)) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt + ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum + AND pkg_tax_exempt.taxnum = cust_main_county.taxnum) + $join_cust_pkg $where AND $nottax $group"; + +# Here we're going to sum all line items that are taxable _at all_, +# under any tax. exempt_charged is the sum of all exemptions for a +# particular billpkgnum + taxnum; we take the taxnum that has the +# smallest sum of exemptions and subtract that from the charged amount. +# +# (This isn't an exact result, since line items can be taxable under +# one tax and not another. Under 4.x the tax report is designed to +# consider only one variety of tax at a time, which should solve this.) + +$all_sql{taxable} = "$select_all + SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(min_exempt, 0)) + FROM cust_bill_pkg + JOIN ( + SELECT billpkgnum, MIN(exempt_charged) AS min_exempt + FROM ($pkg_tax) AS pkg_tax + JOIN cust_bill_pkg USING (billpkgnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) + GROUP BY billpkgnum + ) AS pkg_is_taxable + USING (billpkgnum) + $join_cust_pkg $where AND $nottax $group_all"; + +$sql{taxable} =~ s/EXEMPT_WHERE//; # unrestricted +$all_sql{taxable} =~ s/EXEMPT_WHERE//; + +# there isn't one for 'sales', because we calculate sales by adding up +# the taxable and exempt columns. + +# TAX QUERIES (billed tax, credited tax) +# ----------- + +# sum of billed tax: +# join cust_bill_pkg to cust_main_county via cust_bill_pkg_tax_location +my $taxfrom = " FROM cust_bill_pkg + $join_cust + LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum ) + LEFT JOIN cust_main_county USING ( taxnum )"; + +if ( $with_pkgclass ) { + # If we're not grouping by package class, this is unnecessary, and + # probably really expensive. + $taxfrom .= " + LEFT JOIN cust_bill_pkg AS taxable + ON (cust_bill_pkg_tax_location.taxable_billpkgnum = taxable.billpkgnum) + LEFT JOIN cust_pkg ON (taxable.pkgnum = cust_pkg.pkgnum) + LEFT JOIN part_pkg USING (pkgpart)"; +} + +my $istax = "cust_bill_pkg.pkgnum = 0"; +my $named_tax = + "COALESCE(taxname,'Tax') = COALESCE(cust_bill_pkg.itemdesc,'Tax')"; + +$sql{tax} = "$select SUM(cust_bill_pkg_tax_location.amount) + $taxfrom + $where AND $istax AND $named_tax + $group"; + +$all_sql{tax} = "$select_all SUM(cust_bill_pkg.setup) + FROM cust_bill_pkg + $join_cust + $where AND $istax + $group_all"; + +# sum of credits applied against billed tax +# ($creditfrom includes join of taxable item to part_pkg if with_pkgclass +# is on) +my $creditfrom = $taxfrom . + ' JOIN cust_credit_bill_pkg USING (billpkgtaxlocationnum)' . + ' JOIN cust_credit_bill USING (creditbillnum)'; +my $creditwhere = $where . + ' AND billpkgtaxratelocationnum IS NULL'; +my $creditwhere_all = $where; + +# if the credit_date option is set to application date, change +# $creditwhere accordingly +if ( $cgi->param('credit_date') eq 'cust_credit_bill' ) { + $creditwhere =~ s/cust_bill._date/cust_credit_bill._date/g; + $creditwhere_all =~ s/cust_bill._date/cust_credit_bill._date/g; +} + +$sql{credit} = "$select SUM(cust_credit_bill_pkg.amount) + $creditfrom + $creditwhere AND $istax AND $named_tax + $group"; + +$all_sql{credit} = "$select_all SUM(cust_credit_bill_pkg.amount) + FROM cust_credit_bill_pkg + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust + JOIN cust_credit_bill USING (creditbillnum) + $creditwhere_all AND $istax + $group_all"; +warn "\n\n$all_sql{credit}\n\n"; +if ( $with_pkgclass ) { + # the slightly more complicated version, with lots of joins that are + # unnecessary if you're not breaking down by package class + $all_sql{tax} = "$select_all SUM(cust_bill_pkg_tax_location.amount) + $taxfrom + $where AND $istax + $group_all"; + + $all_sql{credit} = "$select_all SUM(cust_credit_bill_pkg.amount) + $creditfrom + $creditwhere_all AND $istax + $group_all"; +} + +# "out of taxable region" sales +$all_sql{out_sales} = + "$select_all SUM(cust_bill_pkg.setup + cust_bill_pkg.recur) + FROM (cust_bill_pkg $join_cust_pkg) + LEFT JOIN ($pkg_tax) AS pkg_tax USING (billpkgnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum) + $where AND $nottax + AND pkg_tax.taxnum IS NULL AND pkg_tax_exempt.taxnum IS NULL + $group_all" +; + +$all_sql{out_sales} =~ s/EXEMPT_WHERE//; + +my %data; +my %total; +foreach my $k (keys(%sql)) { + my $stmt = $sql{$k}; + warn "\n".uc($k).":\n".$stmt."\n" if $DEBUG; + my $sth = dbh->prepare($stmt); + # three columns: classnum, taxnum, value + $sth->execute + or die "failed to execute $k query: ".$sth->errstr; + while ( my $row = $sth->fetchrow_arrayref ) { + $data{$k}{$row->[0]}{$row->[1]} = $row->[2]; } - print '