+<!-- mason kludge -->
<%
-#!/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;
+
+my $user = getotaker;
$cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/;
-$beginning = $1;
+my $pbeginning = $1;
+my $beginning = $1 ? str2time($1) : 0;
$cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/;
-$ending = $1;
+my $pending = $1;
+my $ending = ( $1 ? str2time($1) : 4294880896 ) + 86399;
+
+my $from_join_cust = "
+ FROM cust_bill_pkg
+ JOIN cust_bill USING ( invnum )
+ JOIN cust_main USING ( custnum )
+";
+my $join_pkg = "
+ JOIN cust_pkg USING ( pkgnum )
+ JOIN part_pkg USING ( pkgpart )
+";
+my $where = "
+ WHERE _date >= $beginning AND _date <= $ending
+ AND ( county = ? OR ? = '' )
+ AND ( state = ? OR ? = '' )
+ AND ( country = ? )
+ AND payby != 'COMP'
+";
+my @base_param = qw( county county state state country );
+
+my $gotcust = "
+ WHERE 0 < ( SELECT COUNT(*) FROM cust_main
+ 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, $exempt, $taxable, $owed, $tax) = ( 0, 0, 0, 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;
+
+ my $fromwhere = $from_join_cust. $join_pkg. $where;
+ my @param = @base_param;
+
+ if ( $r->taxclass ) {
+ $fromwhere .= " AND ( taxclass = ? ) ";
+ push @param, 'taxclass';
+ }
- print $cgi->header( '-expires' => '-2m' ),
- header('Tax Report Results');
+ my $nottax = 'pkgnum != 0';
- open (REPORT, "/usr/bin/freeside-tax-report -v -s $beginning -d $ending freeside |");
+ my $a = scalar_sql($r, \@param,
+ "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax"
+ );
+ $total += $a;
+ $regions{$label}->{'total'} += $a;
- print '<PRE>';
- while(<REPORT>) {
- print $_;
+ foreach my $e ( grep { $r->get($_.'tax') =~ /^Y/i }
+ qw( cust_bill_pkg.setup cust_bill_pkg.recur ) ) {
+ my $x = scalar_sql($r, \@param,
+ "SELECT SUM($e) $fromwhere AND $nottax"
+ );
+ $exempt += $x;
+ $regions{$label}->{'exempt'} += $x;
}
- print '</PRE>';
- print '</BODY></HTML>';
+ foreach my $e ( grep { $r->get($_.'tax') !~ /^Y/i }
+ qw( cust_bill_pkg.setup cust_bill_pkg.recur ) ) {
+ my $t = scalar_sql($r, \@param,
+ "SELECT SUM($e) $fromwhere AND $nottax AND ( tax != 'Y' OR tax IS NULL )"
+ );
+ $taxable += $t;
+ $regions{$label}->{'taxable'} += $t;
+ $owed += $t * ($r->tax/100);
+ $regions{$label}->{'owed'} += $t * ($r->tax/100);
+
+ my $x = scalar_sql($r, \@param,
+ "SELECT SUM($e) $fromwhere AND $nottax AND tax = 'Y'"
+ );
+ $exempt += $x;
+ $regions{$label}->{'exempt'} += $x;
+ }
+
+ if ( defined($regions{$label}->{'rate'})
+ && $regions{$label}->{'rate'} != $r->tax.'%' ) {
+ $regions{$label}->{'rate'} = 'variable';
+ } else {
+ $regions{$label}->{'rate'} = $r->tax.'%';
+ }
+
+}
+
+my $taxwhere = "$from_join_cust $where";
+my @taxparam = @base_param;
+
+#foreach my $label ( keys %regions ) {
+foreach my $r (
+ qsearch( 'cust_main_county',
+ {},
+ 'DISTINCT ON (country, state, county, taxname) *',
+ $gotcust
+ )
+) {
+
+ #warn join('-', map { $r->$_() } qw( country state county taxname ) )."\n";
+
+ my $label = getlabel($r);
+
+ my $fromwhere = $join_pkg. $where;
+ my @param = @base_param;
+
+ #match itemdesc if necessary!
+ my $named_tax =
+ $r->taxname
+ ? 'AND itemdesc = '. dbh->quote($r->taxname)
+ : "AND itemdesc IS NULL or itemdesc = ''";
+ my $x = scalar_sql($r, \@taxparam,
+ "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $taxwhere ".
+ "AND pkgnum = 0 $named_tax",
+ );
+ $tax += $x;
+ $regions{$label}->{'tax'} += $x;
+
+}
+
+#ordering
+my @regions = map $regions{$_},
+ sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) }
+ keys %regions;
+
+push @regions, {
+ 'label' => 'Total',
+ 'total' => $total,
+ 'exempt' => $exempt,
+ 'taxable' => $taxable,
+ 'rate' => '',
+ 'tax' => $tax,
+};
+
+#--
+
+sub getlabel {
+ my $r = shift;
+
+ my $label;
+ if ( $r->tax == 0 ) {
+ #kludge to avoid "will not stay shared" warning
+ my $out = 'Out of taxable region(s)';
+ $label = $out;
+ } elsif ( $r->taxname ) {
+ $label = $r->taxname;
+# $regions{$label}->{'taxname'} = $label;
+# push @{$regions{$label}->{$_}}, $r->$_() foreach qw( county state country );
+ } else {
+ $label = $r->country;
+ $label = $r->state.", $label" if $r->state;
+ $label = $r->county." county, $label" if $r->county;
+ #$label = $r->taxname. " ($label)" if $r->taxname;
+ }
+ return $label;
+}
+
+#false laziness w/FS::Report::Table::Monthly (sub should probably be moved up
+#to FS::Report or FS::Record or who the fuck knows where)
+sub scalar_sql {
+ my( $r, $param, $sql ) = @_;
+ #warn "$sql\n";
+ my $sth = dbh->prepare($sql) or die dbh->errstr;
+ $sth->execute( map $r->$_(), @$param )
+ or die "Unexpected error executing statement $sql: ". $sth->errstr;
+ $sth->fetchrow_arrayref->[0] || 0;
+}
%>
+<%= header( "Sales Tax Report - $pbeginning through ".($pending||'now'),
+ menubar( 'Main Menu'=>$p, ) ) %>
+<%= table() %>
+ <TR>
+ <TH ROWSPAN=2></TH>
+ <TH COLSPAN=3>Sales</TH>
+ <TH ROWSPAN=2>Rate</TH>
+ <TH ROWSPAN=2>Tax owed</TH>
+ <TH ROWSPAN=2>Tax invoiced</TH>
+ </TR>
+ <TR>
+ <TH>Total</TH>
+ <TH>Non-taxable</TH>
+ <TH>Taxable</TH>
+ </TR>
+ <% foreach my $region ( @regions ) { %>
+ <TR>
+ <TD><%= $region->{'label'} %></TD>
+ <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'total'} ) %></TD>
+ <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'exempt'} ) %></TD>
+ <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'taxable'} ) %></TD>
+ <TD ALIGN="right"><%= $region->{'rate'} %></TD>
+ <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'owed'} ) %></TD>
+ <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'tax'} ) %></TD>
+ </TR>
+ <% } %>
+
+</TABLE>
+
+</BODY>
+</HTML>
+
+