tax report!

2 files changed, 123 insertions, 301 deletions

diff --git a/FS/bin/freeside-tax-report b/FS/bin/freeside-tax-report
deleted file mode 100755
index 240f3ad37..000000000
--- a/FS/bin/freeside-tax-report
+++ /dev/null
@@ -1,292 +0,0 @@
-#!/usr/bin/perl -Tw
-
-
-use strict;
-use Date::Parse;
-use Time::Local;
-use Getopt::Std;
-use Text::Template;
-use Net::SMTP;
-use Mail::Header;
-use Mail::Internet;
-use FS::Conf;
-use FS::UID qw(adminsuidsetup);
-use FS::Record qw(qsearch);
-use FS::cust_bill;
-use FS::cust_bill_pay;
-use FS::cust_pay;
-
-
-&untaint_argv;	#what it sounds like  (eww)
-use vars qw($opt_v $opt_p $opt_m $opt_e $opt_t $opt_s $opt_f $report_lines $report_template @buf $header);
-getopts("vpmef:s:");	#switches
-
-#we're at now now (and later).
-my($_finishdate)= $opt_f ? str2time($main::opt_f) : $^T;
-my($_startdate)= $opt_s ? str2time($main::opt_s) : $^T;
-
-# Get the current month
-my ($ssec,$smin,$shour,$smday,$smon,$syear) =
-	(localtime($_startdate) )[0,1,2,3,4,5]; 
-$smon++;
-$syear += 1900;
-
-# Get the current month
-my ($fsec,$fmin,$fhour,$fmday,$fmon,$fyear) =
-	(localtime($_finishdate) )[0,1,2,3,4,5]; 
-$fmon++;
-$fyear += 1900;
-
-# Login to the database
-my $user = shift or die &usage;
-adminsuidsetup $user;
-
-# Get the needed configuration files
-my $conf = new FS::Conf;
-my $lpr = $conf->config('lpr');
-my $email = $conf->config('email');
-my $smtpmachine = $conf->config('smtpmachine');
-my $mail_sender = $conf->exists('invoice_from') ? $conf->config('invoice_from') :
-  'postmaster';
-my @report_template = $conf->config('report_template')
-  or die "cannot load config file report_template";
-$report_lines = 0;
-foreach ( grep /report_lines\(\d+\)/, @report_template ) { #kludgy :/
-  /report_lines\((\d+)\)/;
-  $report_lines += $1;
-}
-die "no report_lines() functions in template?" unless $report_lines;
-$report_template = new Text::Template (
-  TYPE   => 'ARRAY',
-  SOURCE => [ map "$_\n", @report_template ],
-) or die "can't create new Text::Template object: $Text::Template::ERROR";
-
-
-my(@cust_bills)=qsearch('cust_bill',{});
-if (scalar(@cust_bills) == 0)
-{
-	exit 1;
-}
-
-# Open print and email pipes
-# $lpr and opt_p for printing
-# $email and opt_m for email
-
-if ($lpr && $main::opt_p)
-{
-        open(LPR, "|$lpr");
-}
-
-if ($email && $main::opt_m)
-{
-  $ENV{MAILADDRESS} = $mail_sender;
-  $header = new Mail::Header ( [
-    "From: Account Processor",
-    "To: $email",
-    "Sender: $mail_sender",
-    "Reply-To: $mail_sender",
-    "Subject: Sales Taxes Invoiced",
-  ] );
-}
-
-my $comped = 0;
-my $comped_tax = 0;
-my $other = 0;
-my $other_tax = 0;
-my $total = 0;
-my $taxed = 0;
-my $untaxed = 0;
-my $total_tax = 0;
-
-# Now I can start looping
-foreach my $cust_bill (@cust_bills)
-{
-	my $_date = $cust_bill->getfield('_date');
-	my $invnum = $cust_bill->getfield('invnum');
-	my $charged = $cust_bill->getfield('charged');
-
-	if ($_date >= $_startdate && $_date <= $_finishdate) {
-		$total += $charged;
-
-                # The following lines were used to produce rather verbose reports
-                #my ($sec,$min,$hour,$mday,$mon,$year) =
-                #       (localtime($_date) )[0,1,2,3,4,5]; 
-                #$mon++;
-                #$year -= 100 if $year >= 100;
-                #$year = "0" . $year if $year < 10;
-
-                my $invoice_amt =0;
-                my $invoice_tax =0;
-                my $invoice_comped =0;
-                my(@cust_bill_pkgs)= $cust_bill->cust_bill_pkg;
-                foreach my $cust_bill_pkg (@cust_bill_pkgs) {
-
-                        my $recur = $cust_bill_pkg->getfield('recur');
-                        my $setup = $cust_bill_pkg->getfield('setup');
-                        my $pkgnum = $cust_bill_pkg->getfield('pkgnum');
-                        
-                        if ($pkgnum == 0) {
-                                # The following line was used to produce rather verbose reports
-                                # push @buf, ('', sprintf(qq{%10s%15s%14.2f}, "$mon/$mday/$year", "Tax $invnum", $recur+$setup));
-                                $invoice_tax += $recur;
-                                $invoice_tax += $setup;
-                        } else {
-                                # The following line was used to produce rather verbose reports
-                                # push @buf, ('', sprintf(qq{%10s%15s%14.2f}, "$mon/$mday/$year", "Inv $invnum", $recur+$setup));
-                                $invoice_amt += $recur;
-                                $invoice_amt += $setup;
-                        }
-
-                }
-
-                my(@cust_bill_pays)= $cust_bill->cust_bill_pay;
-                foreach my $cust_bill_pay (@cust_bill_pays) {
-                        my $payby = $cust_bill_pay->cust_pay->payby;
-                        my $paid = $cust_bill_pay->getfield('amount');
-                        if ($payby =~ 'COMP') {
-                                $invoice_comped += $paid;
-                        }
-                }
-
-                if (abs($invoice_comped - ($invoice_amt + $invoice_tax)) < 0.0001){
-                        $comped += $invoice_amt;
-                        $comped_tax += $invoice_tax;
-                } elsif ($invoice_comped > 0) {
-                        push @buf, sprintf(qq{\nInvoice %10d has inexpliciable complimentary payments of %14.9f\n}, $invnum, $invoice_comped);
-                        $other += $invoice_amt;
-                        $other_tax += $invoice_tax;
-                } elsif ($invoice_tax > 0) {
-                        $total_tax += $invoice_tax;
-                        $taxed += $invoice_amt;
-                } else {
-                        $untaxed += $invoice_amt;
-                }
-
-        }
-
-}
-
-push @buf, ('', sprintf(qq{%25s%14.2f}, "Complimentary", $comped));
-push @buf, sprintf(qq{%25s%14.2f}, "Complimentary Tax", $comped_tax);
-push @buf, sprintf(qq{%25s%14.2f}, "Other", $other);
-push @buf, sprintf(qq{%25s%14.2f}, "Other Tax", $other_tax);
-push @buf, sprintf(qq{%25s%14.2f}, "Untaxed", $untaxed);
-push @buf, sprintf(qq{%25s%14.2f}, "Taxed", $taxed);
-push @buf, sprintf(qq{%25s%14.2f}, "Tax", $total_tax);
-push @buf, ('', sprintf(qq{%39s}, "========="), sprintf(qq{%39.2f}, $total));
-
-sub FS::tax_report::_template::report_lines {
-  my $lines = shift;
-  map {
-    scalar(@buf) ? shift @buf : '' ;
-  }
-  ( 1 .. $lines );
-}
-
-$FS::tax_report::_template::title = qq~SALES TAXES INVOICED for $smon/$smday/$syear through $fmon/$fmday/$fyear~;
-$FS::tax_report::_template::title = $opt_t if $opt_t;
-$FS::tax_report::_template::page = 1;
-$FS::tax_report::_template::date = $^T;
-$FS::tax_report::_template::date = $^T;
-$FS::tax_report::_template::fdate = $_finishdate;
-$FS::tax_report::_template::fdate = $_finishdate;
-$FS::tax_report::_template::sdate = $_startdate;
-$FS::tax_report::_template::sdate = $_startdate;
-$FS::tax_report::_template::total_pages = 
-  int( scalar(@buf) / $report_lines);
-$FS::tax_report::_template::total_pages++ if scalar(@buf) % $report_lines;
-
-my @report;
-while (@buf) {
-  push @report, split("\n", 
-    $report_template->fill_in( PACKAGE => 'FS::tax_report::_template' )
-  );
-  $FS::tax_report::_template::page++;
-}
-
-if ($opt_v) {
-  print map "$_\n", @report;
-}
-if($lpr && $opt_p)
-{
-  print LPR map "$_\n", @report;
-  print LPR "\f" if $opt_e;
-  close LPR || die "Could not close printer: $lpr\n";
-}
-if($email && $opt_m)
-{
-  my $message = new Mail::Internet (
-    'Header' => $header,
-    'Body' => [ (@report) ],
-  );
-  $!=0;
-  $message->smtpsend( Host => "$smtpmachine" )
-    or die "can't send report to $email via $smtpmachine: $!";
-}
-
-
-# subroutines
-sub untaint_argv {
-  foreach $_ ( $[ .. $#ARGV ) { #untaint @ARGV
-    $ARGV[$_] =~ /^([\w\-\/ :\.]*)$/ || die "Illegal argument \"$ARGV[$_]\"";
-    $ARGV[$_]=$1;
-  }
-}
-
-sub usage {
-  die "Usage:\n\n  freeside-tax-report [-v] [-p] [-e] user\n";
-}
-
-=head1 NAME
-
-freeside-tax-report - Prints or emails sales taxes invoiced in a given period.
-
-=head1 SYNOPSIS
-
-  freeside-tax-report [-v] [-p] [-m] [-e] [-t "title"] [-s date] [-f date] user
-
-=head1 DESCRIPTION
-
-Prints or emails sales taxes invoiced in a given period.
-
--v: Verbose - Prints records to STDOUT.
-
--p: Print to printer lpr as found in the conf directory.
-
--m: Email output to user found in the Conf email file.
-
--e: Print a final form feed to the printer.
-
--t: supply a title for the top of each page.
-
--s: starting date for inclusion
-
--f: final date for inclusion
-
-user: From the mapsecrets file - see config.html from the base documentation
-
-=head1 VERSION
-
-$Id: freeside-tax-report,v 1.5 2002-09-09 22:57:34 ivan Exp $
-
-=head1 BUGS
-
-Yes..... Use at your own risk. No guarantees or warrantees of any
-kind apply to this program. Parts of this program are hacked from
-other GNU licensed software created mainly by Ivan Kohler.
-
-This is released under the GNU Public License. See www.gnu.org
-for more information regarding this license.
-
-=head1 SEE ALSO
-
-L<FS::cust_main>, config.html from the base documentation
-
-=head1 AUTHOR
-
-Jeff Finucane <jeff@cmh.net>
-
-based on print-batch by Joel Griffiths <griff@aver-computer.com>
-
-=cut
-
diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi
index ac76fad6e..08b6d0796 100755
--- a/httemplate/search/report_tax.cgi
+++ b/httemplate/search/report_tax.cgi
@@ -4,22 +4,136 @@
 my $user = getotaker;
 
 $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/;
-my $beginning = $1;
+my $pbeginning = $1;
+my $beginning = str2time($1) || 0;
 
 $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/;
-my $ending = $1;
+my $pending = $1;
+my $ending = ( str2time($1) || 4294880896 ) + 86399;
 
-print header('Tax Report Results');
+my($total, $exempt, $taxable, $tax) = ( 0, 0, 0, 0 );
+my $out = 'Out of taxable region(s)';
+my %regions;
+foreach my $r ( qsearch('cust_main_county', {}) ) {
+  my $label;
+  if ( $r->tax == 0 ) {
+    $label = $out;
+  } elsif ( $r->taxname ) {
+    $label = $r->taxname;
+  } else {
+    $label = $r->country;
+    $label = $r->state.", $label" if $r->state;
+    $label = $r->county." county, $label" if $r->county;
+  }
 
-open (REPORT, "freeside-tax-report -v -s $beginning -f $ending $user |");
+  #match taxclass too?
+
+  my $fromwhere = "
+    FROM cust_bill_pkg
+      JOIN cust_bill USING ( invnum ) 
+      JOIN cust_main USING ( custnum )
+    WHERE _date >= $beginning AND _date <= $ending
+      AND county = ? AND state = ? AND country = ?
+  ";
+  my $nottax = 'pkgnum != 0';
+
+  my $a = scalar_sql($r,
+    "SELECT SUM(setup+recur) $fromwhere AND $nottax"
+  );
+  $total += $a;
+  $regions{$label}->{'total'} += $a;
+
+  foreach my $e ( grep { $r->get($_.'tax') =~ /^Y/i } qw( setup recur ) ) {
+    my $x = scalar_sql($r,
+      "SELECT SUM($e) $fromwhere AND $nottax"
+    );
+    $exempt += $x;
+    $regions{$label}->{'exempt'} += $x;
+  }
+
+  foreach my $e ( grep { $r->get($_.'tax') !~ /^Y/i } qw( setup recur ) ) {
+    my $x = scalar_sql($r,
+      "SELECT SUM($e) $fromwhere AND $nottax"
+    );
+    $taxable += $x;
+    $regions{$label}->{'taxable'} += $x;
+  }
+
+  if ( defined($regions{$label}->{'rate'})
+       && $regions{$label}->{'rate'} != $r->tax.'%' ) {
+    $regions{$label}->{'rate'} = 'variable';
+  } else {
+    $regions{$label}->{'rate'} = $r->tax.'%';
+  }
+
+  #match itemdesc if necessary!
+  my $named_tax = $r->taxname ? 'AND itemdesc = '. dbh->quote($r->taxname) : '';
+  my $x = scalar_sql($r,
+    "SELECT SUM(setup+recur) $fromwhere AND pkgnum = 0 $named_tax",
+  );
+  $tax += $x;
+  $regions{$label}->{'tax'} += $x;
+
+  $regions{$label}->{'label'} = $label;
 
-print '<PRE>';
-while(<REPORT>) {
-  print $_;
 }
-print '</PRE>';
 
-print '</BODY></HTML>';
+#ordering
+my @regions = map $regions{$_},
+              sort { ( $b eq $out cmp $a eq $out ) || ( $a cmp $b ) }
+              keys %regions;
+
+push @regions, {
+  'label'     => 'Total',
+  'total'     => $total,
+  'exempt'    => $exempt,
+  'taxable'   => $taxable,
+  'rate'      => '',
+  'tax'       => $tax,
+};
+
+#-- 
+
+#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, $sql ) = @_;
+  my $sth = dbh->prepare($sql) or die dbh->errstr;
+  $sth->execute( map $r->$_(), qw( county state country ) )
+    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</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">$<%= $region->{'total'} %></TD>
+      <TD ALIGN="right">$<%= $region->{'exempt'} %></TD>
+      <TD ALIGN="right">$<%= $region->{'taxable'} %></TD>
+      <TD ALIGN="right"><%= $region->{'rate'} %></TD>
+      <TD ALIGN="right">$<%= $region->{'tax'} %></TD>
+    </TR>
+  <% } %>
+
+</TABLE>
+
+</BODY>
+</HTML>
+
+