diff options
author | Jonathan Prykop <jonathan@freeside.biz> | 2015-09-02 21:05:28 -0500 |
---|---|---|
committer | Jonathan Prykop <jonathan@freeside.biz> | 2015-09-02 21:40:13 -0500 |
commit | 734d5bedca76afe77345460c2b972d89fdd291c9 (patch) | |
tree | 6b3bbaa043f629f6a500c9e6b2207e2646ab7c26 /httemplate/search/tax_sales.cgi | |
parent | 879891340e6cb028d6a4ab192d73bab1e1292785 (diff) |
RT#32892: Monthly Sales Tax Report [fixed names and colors]
Diffstat (limited to 'httemplate/search/tax_sales.cgi')
-rw-r--r-- | httemplate/search/tax_sales.cgi | 172 |
1 files changed, 172 insertions, 0 deletions
diff --git a/httemplate/search/tax_sales.cgi b/httemplate/search/tax_sales.cgi new file mode 100644 index 0000000..4b28c93 --- /dev/null +++ b/httemplate/search/tax_sales.cgi @@ -0,0 +1,172 @@ + +<% include('/graph/elements/report.html', + 'title' => 'Monthly Sales and Taxes Report', + 'items' => \@row_labels, + 'data' => \@rowdata, + 'row_labels' => \@row_labels, + 'colors' => \@rowcolors, + 'bgcolors' => \@rowbgcolors, + 'col_labels' => \@col_labels, + 'graph_type' => 'none', + ) %> + +<%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; +my @rowcolors; +my @rowbgcolors; +my $pkgcount = 0; #for colors +foreach my $classname (@pkg_classname,@taxnames) { + my $istax = ($pkgcount++ < @pkg_classname) ? 0 : 1; + my @classlabels = (); + my @classdata = (); + my @classcolors = (); + my @classbgcolors = (); + my $hasdata = 0; + foreach my $item ( qw( invoiced credited ) ) { + my $rowlabel = $classname . ' ' . $item; + my $rowdata = $data->{$rowlabel}; + my $rowcolor = $istax ? '0000ff' : '000000'; + my $rowbgcolor = ($item eq 'credited') ? 'cccccc' : 'ffffff'; + $hasdata = 1 if grep { $_ } @$rowdata; + push(@classlabels,$rowlabel); + push(@classdata,$rowdata); + push(@classcolors,$rowcolor); + push(@classbgcolors,$rowbgcolor); + } + next unless $hasdata; # don't include class if it has no data in time range + push(@row_labels,@classlabels); + push(@rowdata,@classdata); + push(@rowcolors,@classcolors); + push(@rowbgcolors,@classbgcolors); +} + +</%init> |