X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Freport_tax.cgi;h=1d906473edf2c6e9ac9cdd1ba30a7f815b4be353;hb=c0c5709fb022b83a482d0b35f7094505766d5868;hp=0a368d33c31091826b0b13279faa9ed2be23ae13;hpb=7b973e57e167efdce2218209e9198ae34f2483b2;p=freeside.git
diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi
old mode 100755
new mode 100644
index 0a368d33c..1d906473e
--- a/httemplate/search/report_tax.cgi
+++ b/httemplate/search/report_tax.cgi
@@ -1,141 +1,236 @@
-
-<%
-
-my $user = getotaker;
-
-$cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/;
-my $pbeginning = $1;
-my $beginning = str2time($1) || 0;
-
-$cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/;
-my $pending = $1;
-my $ending = ( str2time($1) || 4294880896 ) + 86399;
-
-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;
- }
-
- #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 = ? OR ( ? = '' AND county IS NULL ) )
- AND ( state = ? OR ( ? = '' AND state IS NULL ) )
- AND ( country = ? OR ( ? = '' AND country IS NULL ) )
- ";
- 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;
-
+<& /elements/header.html, $report->title &>
+
+Download full results
+as Excel spreadsheet
+ |
+
+
+<& /elements/table-grid.html &>
+
+
+ |
+ Sales |
+ |
+ Rate |
+ |
+ Estimated tax |
+ Tax invoiced |
+ |
+ Tax credited |
+ |
+ Net tax due |
+ |
+ Tax collected |
+
-#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,
-};
-
-#--
-
-#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->$_(), map { $_, $_ } 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() %>
- |
- Sales |
- Rate |
- Tax |
+ Total |
+ Non-taxable |
+ Non-taxable |
+ Non-taxable |
+ Taxable |
+
+
+
+ (tax-exempt customer) |
+ (tax-exempt package) |
+ (monthly exemption) |
+
+
+% my $rownum = 0;
+% my $prev_row = { pkgclass => 'DUMMY PKGCLASS' };
+
+
+% foreach my $row (@rows) {
+% # before anything else: if this row's pkgclass is not the same as the
+% # previous row's, then:
+% if ( $row->{pkgclass} ne $prev_row->{pkgclass} ) {
+% if ( $rownum > 0 ) { # start a new section
+% $rownum = 0;
+
+% }
+% if ( $params{breakdown}->{pkgclass} ) { # and caption the new section
- Total |
- Non-taxable |
- Taxable |
+
+ <% $pkgclass_name{$row->{pkgclass}} %>
+ |
- <% foreach my $region ( @regions ) { %>
+% }
+% } # if $row->{pkgclass} ne ...
+
+% # construct base links that limit to the tax rates described by this row
+% my $rowlink = ';taxnum=' . $row->{taxnums};
+% # and also the package class, if we're limiting package class
+% if ( $params{breakdown}->{pkgclass} ) {
+% $rowlink .= ';classnum=' . ($row->{pkgclass} || 0);
+% }
+%
+% if ( $row->{total} ) {
+
+% }
+
+% # Row label
+ <% $row->{label} |h %> |
+
+% # Total sales
+
+ <% $money_sprintf->( $row->{sales} ) %>
+
+ |
+% # Exemptions: customer
+
+
+ <% $money_sprintf->( $row->{exempt_cust} ) %>
+
+ |
+% # package
+
+
+ <% $money_sprintf->( $row->{exempt_pkg} ) %>
+
+ |
+% # monthly (note this uses $exemptlink; it's a completely separate report)
+
+
+ <% $money_sprintf->( $row->{exempt_monthly} ) %>
+
+ |
+% # taxable sales
+
+ ">
+ <% $money_sprintf->( $row->{taxable} ) %>
+
+ |
+ × |
+ <% $row->{rate} %> |
+% # estimated tax
+ = |
+
+% if ( $row->{estimated} ) {
+ <% $money_sprintf->( $row->{estimated} ) %>
+% }
+ |
+% # invoiced tax
+
+
+ <% $money_sprintf->( $row->{tax} ) %>
+
+ |
+% # credited tax
+ − |
+
+%# currently broken
+ <% $money_sprintf->( $row->{credit} ) %>
+%#
+ |
+% # net tax due
+ = |
+ <% $money_sprintf->( $row->{tax} - $row->{credit} ) %> |
+% # tax collected
+ |
+ <% $money_sprintf->( $row->{tax_paid} ) %> |
+
+% $rownum++;
+% $prev_row = $row;
+% } # foreach my $row
+% # at the end of everything
+
+% if ( $report->{outside} > 0 ) {
+
- <%= $region->{'label'} %> |
- $<%= sprintf('%.2f', $region->{'total'} ) %> |
- $<%= sprintf('%.2f', $region->{'exempt'} ) %> |
- $<%= sprintf('%.2f', $region->{'taxable'} ) %> |
- <%= $region->{'rate'} %> |
- $<%= sprintf('%.2f', $region->{'tax'} ) %> |
+
+ <% emt('Out of taxable region') %>
+ |
+
+
+ <% $money_sprintf->( $report->{outside } ) %>
+
+ |
+ |
- <% } %>
-
+
+% }
-