diff options
| -rw-r--r-- | FS/FS/Report/Table.pm | 27 | ||||
| -rw-r--r-- | httemplate/search/report_tax_sales.cgi | 158 | ||||
| -rwxr-xr-x | httemplate/search/report_tax_sales.html | 35 |
3 files changed, 220 insertions, 0 deletions
diff --git a/FS/FS/Report/Table.pm b/FS/FS/Report/Table.pm index 4b1ad05d6..4b22b60b8 100644 --- 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 index 000000000..5c531c343 --- /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 index 000000000..374a15601 --- /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> |