2 <% include('/graph/elements/report.html',
3 'title' => 'Monthly Sales Tax Report',
4 'items' => \@row_labels,
6 'row_labels' => \@row_labels,
8 'col_labels' => \@col_labels,
14 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
17 my $start_month = $cgi->param('start_month');
18 die "Bad start month" unless $start_month =~ /^\d*$/;
19 my $start_year = $cgi->param('start_year');
20 die "Bad start year" unless $start_year =~ /^\d*$/;
21 my $end_month = $cgi->param('end_month');
22 die "Bad end month" unless $end_month =~ /^\d*$/;
23 my $end_year = $cgi->param('end_year');
24 die "Bad end year" unless $end_year =~ /^\d*$/;
25 die "End year before start year" if $end_year < $start_year;
26 die "End month before start month" if ($start_year == $end_year) && ($end_month < $start_month);
27 my $country = $cgi->param('country');
28 die "Bad country code" unless $country =~ /^\w\w$/;
30 # Data structure for building final table
31 # row order will be calculated separately
33 # $data->{$rowlabel} = \@rowvalues
38 ### Calculate package values
40 my @pkg_class = qsearch('pkg_class');
41 my @pkg_classnum = map { $_->classnum } @pkg_class;
42 unshift(@pkg_classnum,0);
43 my @pkg_classname = map { $_->classname } @pkg_class;
44 unshift(@pkg_classname,'(empty class)');
46 # some false laziness with graph/elements/monthly.html
48 'items' => [ qw( cust_bill_pkg cust_bill_pkg_credits ) ],
49 'cross_params' => [ map { [ 'classnum', $_ ] } @pkg_classnum ],
50 'start_month' => $start_month,
51 'start_year' => $start_year,
52 'end_month' => $end_month,
53 'end_year' => $end_year,
55 my $pkgreport = new FS::Report::Table::Monthly(%reportopts);
56 my $pkgdata = $pkgreport->data;
58 # assuming every month/year combo is included in results,
59 # just use this list for the final table
60 my @col_labels = @{$pkgdata->{'label'}};
62 # unpack report data into a more manageable format
63 foreach my $item ( qw( invoiced credited ) ) { # invoiced, credited
64 my $itemref = shift @{$pkgdata->{'data'}};
65 foreach my $label (@{$pkgdata->{'label'}}) { # month/year
66 my $labelref = shift @$itemref;
67 foreach my $classname (@pkg_classname) { # pkg class
68 my $value = shift @$labelref;
69 my $rowlabel = $classname.' '.$item;
70 $data->{$rowlabel} ||= [];
71 push(@{$data->{$rowlabel}},$value);
76 ### Calculate tax values
78 # false laziness w report_tax.html, put this in FS::Report::Tax?
79 my $sth = dbh->prepare('SELECT DISTINCT(COALESCE(taxname, \'Tax\')) FROM cust_main_county');
80 $sth->execute or die $sth->errstr;
81 my @taxnames = map { $_->[0] } @{ $sth->fetchall_arrayref };
84 # get DateTime objects for start & end
85 my $startdate = DateTime->new(
87 month => $start_month,
90 my $enddate = DateTime->new(
95 $enddate->add( months => 1 )->subtract( seconds => 1 ); # the last second of the month
97 # common to all tax reports
99 'country' => $country,
100 'credit_date' => 'cust_bill',
103 # run a report for each month, for each tax
104 my $countdate = $startdate->clone;
105 while ($countdate < $enddate) {
107 # set report start date, iterate to end of this month, set report end date
108 $params{'beginning'} = $countdate->epoch;
109 $params{'ending'} = $countdate->add( months => 1 )->subtract( seconds => 1 )->epoch;
111 # run a report for each tax name
112 foreach my $taxname (@taxnames) {
113 $params{'taxname'} = $taxname;
114 my $report = FS::Report::Tax->report_internal(%params);
116 # extract totals from report, kinda awkward
117 my $pkgclass = ''; # this will get more complicated if we breakdown by pkgclass
119 if ($report->{'total'}->{$pkgclass}) {
120 my %totals = map { $$_[0] => $$_[2] } @{$report->{'total'}->{$pkgclass}};
121 $values[0] = $totals{'tax'};
122 $values[1] = $totals{'credit'};
125 # treat each tax class like it's an additional pkg class
126 foreach my $item ( qw ( invoiced credited ) ) {
127 my $rowlabel = $taxname . ' ' . $item;
128 my $value = shift @values;
129 $data->{$rowlabel} ||= [];
130 push(@{$data->{$rowlabel}},$value);
135 # iterate to next month
136 $countdate->add( seconds => 1 );
139 # put the data in the order we want it
142 foreach my $classname (@pkg_classname,@taxnames) {
143 my @classlabels = ();
146 foreach my $item ( qw( invoiced credited ) ) {
147 my $rowlabel = $classname . ' ' . $item;
148 my $rowdata = $data->{$rowlabel};
149 $hasdata = 1 if grep { $_ } @$rowdata;
150 push(@classlabels,$rowlabel);
151 push(@classdata,$rowdata);
153 next unless $hasdata; # don't include class if it has no data in time range
154 push(@row_labels,@classlabels);
155 push(@rowdata,@classdata);