% 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::ByName->report(%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>