X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Freport_tax.cgi;h=2786f571bf0c066e41b7bd3b63181f186584453d;hb=01629c3c934f1f6fd2ab9de5f7638f671fd59791;hp=8062479b5982d53d008309b2167a1e6235a97273;hpb=b50b2e5f94774268c271484f9c07bfe316f95527;p=freeside.git diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index 8062479b5..2786f571b 100755 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -1,94 +1,770 @@ -<% -#!/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('; - 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 ++ + +% my $bgcolor1 = '#eeeeee'; +% my $bgcolor2 = '#ffffff'; +% my $bgcolor; +% +% foreach my $region ( @regions ) { +% +% my $link = ''; +% 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 $diff = 0; +% 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' ? '#eeee66' : '#ffff99'; +% #} elsif ( $diff ) { +% $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 = ''; + +(tax-exempt customer) +(tax-exempt package) +(monthly exemption) ++ <<%$td%>><% $region->{'label'} %> + <<%$td%> ALIGN="right"> + <% &$money_sprintf( $region->{'total'} ) %> + + <<%$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'} ) %> + + +% unless ( $cgi->param('show_taxclasses') ) { +% my $invlink = $region->{'url_param_inv'} +% ? ';'. $region->{'url_param_inv'} +% : $link; + + <<%$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'} ) %> + +% } + + +% } + + + +% if ( $cgi->param('show_taxclasses') ) { + +
+ <% include('/elements/table-grid.html') %> ++ + +% #some false laziness w/above +% $bgcolor1 = '#eeeeee'; +% $bgcolor2 = '#ffffff'; +% +% foreach my $region ( @base_regions ) { +% +% my $link = ''; +% 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; + ++ Tax invoiced ++ Tax credited ++ Tax collected ++ <<%$td%>><% $region->{'label'} %> + <<%$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 ( $bgcolor eq $bgcolor1 ) { +% $bgcolor = $bgcolor2; +% } else { +% $bgcolor = $bgcolor1; +% } +% my $td = qq(TD CLASS="grid" BGCOLOR="$bgcolor"); + ++ <<%$td%>>Total + <<%$td%> ALIGN="right"> + <% &$money_sprintf( $tot_tax ) %> + + <<%$td%>> - + <<%$td%> ALIGN="right"> + <% &$money_sprintf( $tot_credit ) %> + + <<%$td%>> = + <<%$td%> ALIGN="right"> + <% &$money_sprintf( $tot_tax - $tot_credit ) %> + + + + + +% } + +<% include('/elements/footer.html') %> + +<%init> + +my $DEBUG = $cgi->param('debug') || 0; + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); + +my $conf = new FS::Conf; + +my $user = getotaker; + +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 ) + LEFT JOIN cust_location + ON ( cust_location.locationnum = ' . + FS::cust_pkg->tax_locationnum_sql . ' )'; + +my $from_join_cust_pkg = " FROM cust_bill_pkg $join_cust_pkg "; + +my $where = "WHERE _date >= $beginning AND _date <= $ending "; + +# this query will be run once per cust_main_county, +# or maybe once per country/state/city tuple, +# or maybe once per country/state...it's hard to say. +my ($location_sql, @base_param) = FS::cust_location->in_county_sql(param => 1); +$where .= " AND $location_sql "; + +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; +} + +sub gotcust { + my $table = shift; + my $prefix = @_ ? shift : ''; + " + ( $table.district = cust_main_county.district + OR cust_main_county.district = '' + OR cust_main_county.district IS NULL ) + AND ( $table.${prefix}city = cust_main_county.city + OR cust_main_county.city = '' + OR cust_main_county.city IS NULL ) + AND ( $table.${prefix}county = cust_main_county.county + OR cust_main_county.county = '' + OR cust_main_county.county IS NULL ) + AND ( $table.${prefix}state = cust_main_county.state + OR cust_main_county.state = '' + OR cust_main_county.state IS NULL ) + AND ( $table.${prefix}country = cust_main_county.country ) + "; +} + +#non-parameterized form +my $location_in_county = FS::cust_location->in_county_sql; +my $gotcust = "WHERE EXISTS( + SELECT 1 FROM cust_location WHERE $location_in_county AND disabled IS NULL +)"; + +my $out = 'Out of taxable region(s)'; +# these are actually tax labels, not regions +my %regions = (); + +# Phase 1: Taxable and exempt sales +# Collect for each cust_main_county, and assign to a bin based on label. +# Note that "label" includes city if show_cities is on, and taxclass if +# show_taxclasses is on. +foreach my $r ( qsearch({ 'table' => 'cust_main_county', + 'extra_sql' => $gotcust, + 'debug' => $DEBUG, + }) + ) +{ + warn $r->county. ' '. $r->state. ' '. $r->country. "\n" if $DEBUG > 1; + + # set up a %regions entry for this region's tax label + my $label = getlabel($r); + $regions{$label}->{'label'} = $label; + + $regions{$label}->{$_} = $r->$_() for (qw( county state country )); #taxname? + + my @url_param = qw( county state country taxname ); + push @url_param, 'city' if $cgi->param('show_cities') && $r->city(); + + $regions{$label}->{'url_param'} = + join(';', map "$_=".uri_escape($r->$_()), @url_param ); + + my @param = @base_param; + my $mywhere = $where; + + if ( $r->taxclass ) { + + $mywhere .= " AND taxclass = ? "; + push @param, 'taxclass'; + $regions{$label}->{'url_param'} .= ';taxclass='. uri_escape($r->taxclass); + #no, always# if $cgi->param('show_taxclasses'); + + $regions{$label}->{'taxclass'} = $r->taxclass; + + } else { + + # SQL for "taxclass doesn't match any other tax in the region" + my $same_sql = $r->sql_taxclass_sameregion; + $mywhere .= " AND $same_sql" if $same_sql; + + $regions{$label}->{'url_param'} .= ';taxclassNULL=1' + if $cgi->param('show_taxclasses') + || $same_sql; + } - print '