RT#32892: Monthly Sales Tax Report

diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm
index 4b1ad05..4b22b60 100644 (file)
--- a/FS/FS/Report/Table.pm
+++ b/FS/FS/Report/Table.pm
@@ -753,6 +753,33 @@ sub cust_bill_pkg_taxes {
   $self->scalar_sql($total_sql);
 }
 
+#all credits applied to matching pkg line items (ie not taxes or fees)
+
+sub cust_bill_pkg_credits {
+  my $self = shift;
+  my ($speriod, $eperiod, $agentnum, %opt) = @_;
+
+  $agentnum ||= $opt{'agentnum'};
+
+  my @where = (
+    '(cust_bill_pkg.pkgnum != 0 OR feepart IS NOT NULL)',
+    $self->with_classnum($opt{'classnum'}, $opt{'use_override'}),
+    $self->with_report_option(%opt),
+    $self->in_time_period_and_agent($speriod, $eperiod, $agentnum),
+    $self->with_refnum(%opt),
+    $self->with_cust_classnum(%opt)
+  );
+
+  my $total_sql = "SELECT COALESCE(SUM(cust_credit_bill_pkg.amount),0)
+    FROM cust_bill_pkg
+    $cust_bill_pkg_join
+    LEFT JOIN cust_credit_bill_pkg 
+      USING ( billpkgnum )
+    WHERE " . join(' AND ', grep $_, @where);
+
+  $self->scalar_sql($total_sql);
+}
+
 ##### package churn report #####
 
 =item active_pkg: The number of packages that were active at the start of 

diff --git a/httemplate/search/report_tax_sales.cgi b/httemplate/search/report_tax_sales.cgi
new file mode 100644 (file)
index 0000000..5c531c3
--- /dev/null
+++ b/httemplate/search/report_tax_sales.cgi
@@ -0,0 +1,158 @@
+
+<% include('/graph/elements/report.html',
+     'title' => 'Monthly Sales Tax Report',
+     'items' => \@row_labels,
+     'data'  => \@rowdata,
+     'row_labels' => \@row_labels,
+     'colors' => [],
+     'col_labels' => \@col_labels,
+   ) %>
+
+<%init>
+
+die "access denied"
+  unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
+
+# validate cgi input
+my $start_month = $cgi->param('start_month');
+die "Bad start month" unless $start_month =~ /^\d*$/;
+my $start_year = $cgi->param('start_year');
+die "Bad start year" unless $start_year =~ /^\d*$/;
+my $end_month = $cgi->param('end_month');
+die "Bad end month" unless $end_month =~ /^\d*$/;
+my $end_year = $cgi->param('end_year');
+die "Bad end year" unless $end_year =~ /^\d*$/;
+die "End year before start year" if $end_year < $start_year;
+die "End month before start month" if ($start_year == $end_year) && ($end_month < $start_month);
+my $country = $cgi->param('country');
+die "Bad country code" unless $country =~ /^\w\w$/;
+
+# Data structure for building final table
+# row order will be calculated separately
+#
+# $data->{$rowlabel} = \@rowvalues
+#
+
+my $data = {};
+
+### Calculate package values
+
+my @pkg_class = qsearch('pkg_class');
+my @pkg_classnum = map { $_->classnum } @pkg_class;
+unshift(@pkg_classnum,0);
+my @pkg_classname = map { $_->classname } @pkg_class;
+unshift(@pkg_classname,'(empty class)');
+
+# some false laziness with graph/elements/monthly.html
+my %reportopts = (
+  'items'        => [ qw( cust_bill_pkg cust_bill_pkg_credits ) ],
+  'cross_params' => [ map { [ 'classnum', $_ ] } @pkg_classnum ],
+  'start_month'  => $start_month,
+  'start_year'   => $start_year,
+  'end_month'    => $end_month,
+  'end_year'     => $end_year,
+);
+my $pkgreport = new FS::Report::Table::Monthly(%reportopts);
+my $pkgdata = $pkgreport->data;
+
+# assuming every month/year combo is included in results,
+# just use this list for the final table
+my @col_labels = @{$pkgdata->{'label'}}; 
+
+# unpack report data into a more manageable format
+foreach my $item ( qw( invoiced credited ) ) { # invoiced, credited
+  my $itemref = shift @{$pkgdata->{'data'}};
+  foreach my $label (@{$pkgdata->{'label'}}) { # month/year
+    my $labelref = shift @$itemref;
+    foreach my $classname (@pkg_classname) {   # pkg class
+      my $value = shift @$labelref;
+      my $rowlabel = $classname.' '.$item;
+      $data->{$rowlabel} ||= [];
+      push(@{$data->{$rowlabel}},$value);
+    }
+  }
+}
+
+### Calculate tax values
+
+# false laziness w report_tax.html, put this in FS::Report::Tax?
+my $sth = dbh->prepare('SELECT DISTINCT(COALESCE(taxname, \'Tax\')) FROM cust_main_county');
+$sth->execute or die $sth->errstr;
+my @taxnames = map { $_->[0] } @{ $sth->fetchall_arrayref };
+$sth->finish;
+
+# get DateTime objects for start & end
+my $startdate = DateTime->new(
+                  year => $start_year,
+                  month => $start_month,
+                  day => 1
+                );
+my $enddate   = DateTime->new(
+                  year => $end_year,
+                  month => $end_month,
+                  day => 1
+                );
+$enddate->add( months => 1 )->subtract( seconds => 1 ); # the last second of the month
+
+# common to all tax reports
+my %params = ( 
+  'country' => $country,
+  'credit_date' => 'cust_bill',
+);
+
+# run a report for each month, for each tax
+my $countdate = $startdate->clone;
+while ($countdate < $enddate) {
+
+  # set report start date, iterate to end of this month, set report end date
+  $params{'beginning'} = $countdate->epoch;
+  $params{'ending'} = $countdate->add( months => 1 )->subtract( seconds => 1 )->epoch;
+
+  # run a report for each tax name
+  foreach my $taxname (@taxnames) {
+    $params{'taxname'} = $taxname;
+    my $report = FS::Report::Tax->report_internal(%params);
+
+    # extract totals from report, kinda awkward
+    my $pkgclass = ''; # this will get more complicated if we breakdown by pkgclass
+    my @values = (0,0);
+    if ($report->{'total'}->{$pkgclass}) {
+      my %totals = map { $$_[0] => $$_[2] } @{$report->{'total'}->{$pkgclass}};
+      $values[0] = $totals{'tax'};
+      $values[1] = $totals{'credit'};
+    }
+
+    # treat each tax class like it's an additional pkg class
+    foreach my $item ( qw ( invoiced credited ) ) {
+      my $rowlabel = $taxname . ' ' . $item;
+      my $value = shift @values;
+      $data->{$rowlabel} ||= [];
+      push(@{$data->{$rowlabel}},$value);
+    }
+
+  }
+
+  # iterate to next month
+  $countdate->add( seconds => 1 );
+}
+
+# put the data in the order we want it
+my @row_labels;
+my @rowdata;
+foreach my $classname (@pkg_classname,@taxnames) {
+  my @classlabels = ();
+  my @classdata = ();
+  my $hasdata = 0;
+  foreach my $item ( qw( invoiced credited ) ) {
+    my $rowlabel = $classname . ' ' . $item;
+    my $rowdata = $data->{$rowlabel};
+    $hasdata = 1 if grep { $_ } @$rowdata;
+    push(@classlabels,$rowlabel);
+    push(@classdata,$rowdata);
+  }
+  next unless $hasdata; # don't include class if it has no data in time range
+  push(@row_labels,@classlabels);
+  push(@rowdata,@classdata);
+}
+
+</%init>

diff --git a/httemplate/search/report_tax_sales.html b/httemplate/search/report_tax_sales.html
new file mode 100755 (executable)
index 0000000..374a156
--- /dev/null
+++ b/httemplate/search/report_tax_sales.html
@@ -0,0 +1,35 @@
+<% include('/elements/header.html', 'Monthly Sales Tax Report' ) %>
+
+<FORM ACTION="report_tax_sales.cgi" METHOD="GET">
+
+<TABLE>
+
+  <% include('/elements/tr-select-from_to.html') %>
+
+  <% include('/elements/tr-select.html',
+      'label'         => 'Country',
+      'field'         => 'country',
+      'options'       => \@countries,
+      'curr_value'    => ($conf->config('countrydefault') || 'US'),
+     ) %>
+
+</TABLE>
+
+<BR><INPUT TYPE="submit" VALUE="Get Report">
+
+</FORM>
+
+<% include('/elements/footer.html') %>
+<%init>
+
+die "access denied"
+  unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
+
+my $conf = new FS::Conf;
+
+# false laziness w report_tax.html, put this in FS::Report::Tax?
+my $sth = dbh->prepare('SELECT DISTINCT(country) FROM cust_location');
+$sth->execute or die $sth->errstr;
+my @countries = map { $_->[0] } @{ $sth->fetchall_arrayref };
+
+</%init>