1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
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::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>
|
