+<% include("/elements/header.html", "$agentname Sales Tax Report - ".
+ ( $beginning
+ ? time2str('%h %o %Y ', $beginning )
+ : ''
+ ).
+ 'through '.
+ ( $ending == 4294967295
+ ? 'now'
+ : time2str('%h %o %Y', $ending )
+ )
+ )
+%>
+
+<% include('/elements/table-grid.html') %>
+
+ <TR>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" COLSPAN=9>Sales</TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Rate</TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax owed</TH>
+% unless ( $cgi->param('show_taxclasses') ) {
+
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax invoiced</TH>
+% }
+
+ </TR>
+ <TR>
+ <TH CLASS="grid" BGCOLOR="#cccccc">Total</TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc">Non-taxable<BR><FONT SIZE=-1>(tax-exempt customer)</FONT></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc">Non-taxable<BR><FONT SIZE=-1>(tax-exempt package)</FONT></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc">Non-taxable<BR><FONT SIZE=-1>(monthly exemption)</FONT></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc">Taxable</TH>
+ </TR>
+% my $bgcolor1 = '#eeeeee';
+% my $bgcolor2 = '#ffffff';
+% my $bgcolor;
+%
+% foreach my $region ( @regions ) {
+%
+% if ( $bgcolor eq $bgcolor1 ) {
+% $bgcolor = $bgcolor2;
+% } else {
+% $bgcolor = $bgcolor1;
+% }
+%
+% my $link = '';
+% if ( $region->{'label'} ne 'Total' ) {
+% if ( $region->{'label'} eq $out ) {
+% $link = ';out=1';
+% } else {
+% $link = ';'. $region->{'url_param'};
+% }
+% }
+%
+%
+%
+%
+%
+
+
+ <TR>
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $region->{'label'} %></TD>
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
+ <A HREF="<% $baselink. $link %>;nottax=1"><% $money_char %><% sprintf('%.2f', $region->{'total'} ) %></A>
+ </TD>
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><FONT SIZE="+1"><B> - </B></FONT></TD>
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
+ <A HREF="<% $baselink. $link %>;nottax=1;cust_tax=Y"><% $money_char %><% sprintf('%.2f', $region->{'exempt_cust'} ) %></A>
+ </TD>
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><FONT SIZE="+1"><B> - </B></FONT></TD>
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
+ <A HREF="<% $baselink. $link %>;nottax=1;pkg_tax=Y"><% $money_char %><% sprintf('%.2f', $region->{'exempt_pkg'} ) %></A>
+ </TD>
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><FONT SIZE="+1"><B> - </B></FONT></TD>
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
+ <A HREF="<% $exemptlink. $link %>"><% $money_char %><% sprintf('%.2f', $region->{'exempt_monthly'} ) %></A>
+ </TD>
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><FONT SIZE="+1"><B> = </B></FONT></TD>
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
+ <% $money_char %><% sprintf('%.2f', $region->{'taxable'} ) %></A>
+ </TD>
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $region->{'label'} eq 'Total' ? '' : '<FONT FACE="sans-serif" SIZE="+1"><B> X </B></FONT>' %></TD>
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right"><% $region->{'rate'} %></TD>
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $region->{'label'} eq 'Total' ? '' : '<FONT FACE="sans-serif" SIZE="+1"><B> = </B></FONT>' %></TD>
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
+ <% $money_char %><% sprintf('%.2f', $region->{'owed'} ) %>
+ </TD>
+% unless ( $cgi->param('show_taxclasses') ) {
+
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
+ <A HREF="<% $baselink. $link %>;istax=1"><% $money_char %><% sprintf('%.2f', $region->{'tax'} ) %></A>
+ </TD>
+% }
+
+ </TR>
+% }
+
+
+</TABLE>
+% if ( $cgi->param('show_taxclasses') ) {
+
+
+ <BR>
+ <% include('/elements/table-grid.html') %>
+ <TR>
+ <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc">Tax invoiced</TH>
+ </TR>
+% #some false laziness w/above
+% $bgcolor1 = '#eeeeee';
+% $bgcolor2 = '#ffffff';
+% foreach my $region ( @base_regions ) {
+%
+% if ( $bgcolor eq $bgcolor1 ) {
+% $bgcolor = $bgcolor2;
+% } else {
+% $bgcolor = $bgcolor1;
+% }
+%
+% my $link = '';
+% #if ( $region->{'label'} ne 'Total' ) {
+% if ( $region->{'label'} eq $out ) {
+% $link = ';out=1';
+% } else {
+% $link = ';'. $region->{'url_param'};
+% }
+% #}
+%
+
+
+ <TR>
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $region->{'label'} %></TD>
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
+ <A HREF="<% $baselink. $link %>;istax=1"><% $money_char %><% sprintf('%.2f', $region->{'tax'} ) %></A>
+ </TD>
+ </TR>
+% }
+%
+% if ( $bgcolor eq $bgcolor1 ) {
+% $bgcolor = $bgcolor2;
+% } else {
+% $bgcolor = $bgcolor1;
+% }
+%
+
+
+ <TR>
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>">Total</TD>
+ <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
+ <A HREF="<% $baselink %>;istax=1"><% $money_char %><% sprintf('%.2f', $tax ) %></A>
+ </TD>
+ </TR>
+
+ </TABLE>
+% }
+
+
+</BODY>
+</HTML>
+<%init>
+
+die "access denied"
+ unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
+
+my $conf = new FS::Conf;
+my $money_char = $conf->config('money_char') || '$';
+
+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 $from_join_cust = "
+ FROM cust_bill_pkg
+ $join_cust
+";
+my $join_pkg = "
+ LEFT JOIN cust_pkg USING ( pkgnum )
+ LEFT JOIN part_pkg USING ( pkgpart )
+";
+
+my $where = "WHERE _date >= $beginning AND _date <= $ending ";
+my @base_param = qw( county county state state country );
+if ( $conf->exists('tax-ship_address') ) {
+
+ $where .= "
+ AND ( ( ( ship_last IS NULL OR ship_last = '' )
+ AND ( county = ? OR ? = '' )
+ AND ( state = ? OR ? = '' )
+ AND country = ?
+ )
+ OR ( ship_last IS NOT NULL AND ship_last != ''
+ AND ( ship_county = ? OR ? = '' )
+ AND ( ship_state = ? OR ? = '' )
+ AND ship_country = ?
+ )
+ )
+ ";
+ # AND payby != 'COMP'
+
+ push @base_param, @base_param;
+
+} else {
+
+ $where .= "
+ AND ( county = ? OR ? = '' )
+ AND ( state = ? OR ? = '' )
+ AND country = ?
+ ";
+ # AND payby != 'COMP'
+
+}
+
+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 $gotcust = "
+ WHERE 0 < ( SELECT COUNT(*) FROM cust_main
+";
+if ( $conf->exists('tax-ship_address') ) {
+
+ $gotcust .= "
+ WHERE
+
+ ( cust_main_county.country = cust_main.country
+ OR cust_main_county.country = cust_main.ship_country
+ )
+
+ AND
+
+ (
+
+ ( ( ship_last IS NULL OR ship_last = '' )
+ AND ( cust_main_county.country = cust_main.country )
+ AND ( cust_main_county.state = cust_main.state
+ OR cust_main_county.state = ''
+ OR cust_main_county.state IS NULL )
+ AND ( cust_main_county.county = cust_main.county
+ OR cust_main_county.county = ''
+ OR cust_main_county.county IS NULL )
+ )
+
+ OR
+
+ ( ship_last IS NOT NULL AND ship_last != ''
+ AND ( cust_main_county.country = cust_main.ship_country )
+ AND ( cust_main_county.state = cust_main.ship_state
+ OR cust_main_county.state = ''
+ OR cust_main_county.state IS NULL )
+ AND ( cust_main_county.county = cust_main.ship_county
+ OR cust_main_county.county = ''
+ OR cust_main_county.county IS NULL )
+ )
+
+ )
+
+ LIMIT 1
+ )
+ ";
+
+} else {
+
+ $gotcust .= "
+ WHERE ( cust_main.county = cust_main_county.county
+ OR cust_main_county.county = ''
+ OR cust_main_county.county IS NULL )
+ AND ( cust_main.state = cust_main_county.state
+ OR cust_main_county.state = ''
+ OR cust_main_county.state IS NULL )
+ AND ( cust_main.country = cust_main_county.country )
+ LIMIT 1
+ )
+ ";
+
+}
+
+my($total, $tot_taxable, $owed, $tax) = ( 0, 0, 0, 0, 0 );
+my( $exempt_cust, $exempt_pkg, $exempt_monthly ) = ( 0, 0 );
+my $out = 'Out of taxable region(s)';
+my %regions = ();
+foreach my $r (qsearch('cust_main_county', {}, '', $gotcust) ) {
+ #warn $r->county. ' '. $r->state. ' '. $r->country. "\n";
+
+ my $label = getlabel($r);
+ $regions{$label}->{'label'} = $label;
+ $regions{$label}->{'url_param'} = join(';', map "$_=".$r->$_(), qw( county state country ) );
+
+ my @param = @base_param;
+ my $mywhere = $where;
+
+ if ( $r->taxclass ) {
+
+ $mywhere .= " AND taxclass = ? ";
+ push @param, 'taxclass';
+ $regions{$label}->{'url_param'} .= ';taxclass='. $r->taxclass
+ if $cgi->param('show_taxclasses');
+
+ } else {
+
+ my $same_query = 'SELECT taxclass FROM cust_main_county '.
+ ' WHERE taxnum != ? AND country = ?';
+ my @same_param = ( 'taxnum', 'country' );
+ foreach my $opt_field (qw( state county )) {
+ if ( $r->$opt_field() ) {
+ $same_query .= " AND $opt_field = ?";
+ push @same_param, $opt_field;
+ } else {
+ $same_query .= " AND $opt_field IS NULL";
+ }
+ }
+
+ my @taxclasses = list_sql( $r, \@same_param, $same_query );
+
+ if ( scalar(@taxclasses) ) {
+ $mywhere .= ' AND '. join(' AND ', map ' taxclass != ? ', @taxclasses );
+ push @param, @taxclasses;
+ }
+
+ }
+
+ my $fromwhere = $from_join_cust. $join_pkg. $mywhere. " AND payby != 'COMP' ";
+
+# my $label = getlabel($r);
+# $regions{$label}->{'label'} = $label;
+
+ my $nottax = 'pkgnum != 0';
+
+ ## calculate total for this region
+
+ my $t = scalar_sql($r, \@param,
+ "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax"
+ );
+ $total += $t;
+ $regions{$label}->{'total'} += $t;
+
+ ## calculate customer-exemption for this region
+
+## my $taxable = $t;
+
+# my($taxable, $x_cust) = (0, 0);
+# foreach my $e ( grep { $r->get($_.'tax') !~ /^Y/i }
+# qw( cust_bill_pkg.setup cust_bill_pkg.recur ) ) {
+# $taxable += scalar_sql($r, \@param,
+# "SELECT SUM($e) $fromwhere AND $nottax AND ( tax != 'Y' OR tax IS NULL )"
+# );