X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Freport_tax.cgi;h=479b990442e957a3b3777cecd04fcd1d443348f2;hb=63973c641c4be00765fa27e55c57cc5b9aa4da19;hp=ac76fad6e7ad50c2add0f8cb6a34a41cda96cb97;hpb=eb9668a6f3181ee02cb335272c5ee4616e61fd09;p=freeside.git
diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi
index ac76fad6e..479b99044 100755
--- a/httemplate/search/report_tax.cgi
+++ b/httemplate/search/report_tax.cgi
@@ -1,25 +1,635 @@
-
-<%
+<% 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
+as Excel spreadsheet
+ |
+
+<% include('/elements/table-grid.html') %>
+
+
+ |
+ Sales |
+ |
+ Rate |
+ |
+ Tax owed |
+% unless ( $cgi->param('show_taxclasses') ) {
+ Tax invoiced |
+ |
+ Tax credited |
+ |
+ Tax collected |
+% }
+
+
+
+ Total |
+ |
+ Non-taxable |
+ |
+ Non-taxable |
+ |
+ Non-taxable |
+ |
+ Taxable |
+
+
+
+ (tax-exempt customer) |
+ (tax-exempt package) |
+ (monthly exemption) |
+
+
+% my $bgcolor1 = '#eeeeee';
+% my $bgcolor2 = '#ffffff';
+% my $bgcolor;
+%
+% foreach my $region ( @regions ) {
+%
+% my $link = '';
+% 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 = '';
+
+
+ <<%$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'} ) %>
+
+% }
+% } # not $out
+
+
+% }
+
+
+
+% if ( $cgi->param('show_taxclasses') ) {
+
+
+ <% include('/elements/table-grid.html') %>
+
+ |
+ Tax invoiced |
+ |
+ Tax credited |
+ |
+ Tax collected |
+
+
+% #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;
+
+
+ <<%$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
+
+
+
+% } # 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 $user = getotaker;
+my $conf = new FS::Conf;
-$cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/;
-my $beginning = $1;
+my $out = 'Out of taxable region(s)';
-$cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/;
-my $ending = $1;
+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');
-print header('Tax Report Results');
+$label_opt{with_taxclass} = 1 if $cgi->param('show_taxclasses');
-open (REPORT, "freeside-tax-report -v -s $beginning -f $ending $user |");
+my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
-print '';
-while() {
- print $_;
+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 ";
+
+# either or both of these can be used to link cust_bill_pkg to cust_main_county
+my $pkg_tax = "SELECT SUM(amount) as tax_amount, invnum, taxnum, ".
+ "cust_bill_pkg_tax_location.pkgnum ".
+ "FROM cust_bill_pkg_tax_location JOIN cust_bill_pkg USING (billpkgnum) ".
+ "GROUP BY billpkgnum, invnum, taxnum, cust_bill_pkg_tax_location.pkgnum";
+
+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 _date >= $beginning AND _date <= $ending ";
+my $group = "GROUP BY cust_main_county.taxnum";
+
+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;
}
-print '
';
-print '