<& elements/grid-report.html, title => $title, rows => \@rows, cells => \@cells, head => $head, # would be better handled with Mason inheritance? consider this. easy enough # to change it at this point. &> <%init> die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports') && $FS::CurrentUser::CurrentUser->access_right('List rating data'); my ($agentnum,$sel_agent); if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { $agentnum = $1; $sel_agent = qsearchs('agent', { 'agentnum' => $agentnum } ); die "agentnum $agentnum not found!" unless $sel_agent; } my $title = $sel_agent ? $sel_agent->agent.' ' : ''; $title .= 'Customer CDRs Profit/Loss Report'; my @items = ('cust_bill_pkg_recur', 'cust_bill_pkg_recur', 'cust_bill_pkg_detail', 'cust_bill_pkg_detail' ); my @params = ( [], [ 'cost' => 1 ], [], [ 'cost' => 1 ] ); my @labels = (); my @cross_params = (); my %search_hash; foreach (qw(agentnum)) { if ( defined $cgi->param($_) ) { $search_hash{$_} = $cgi->param($_); } } my $query = FS::cust_main::Search->search(\%search_hash); my @cust_main = qsearch($query); foreach my $cust_main (@cust_main) { push @cross_params, [ ('custnum' => $cust_main->custnum) ]; } my %opt = ( items => \@items, params => \@params, cross_params => \@cross_params, agentnum => $agentnum, ); for ( qw(start_month start_year end_month end_year) ) { if ( $cgi->param($_) =~ /^(\d+)$/ ) { $opt{$_} = $1; } } my $report = FS::Report::Table::Monthly->new(%opt); my $data = $report->data; ### False laziness with customer_accounting_summary.html my @total; my @rows; # hashes of row info my @cells; # arrayrefs of cell info # We use Excel currency format, but not Excel dates, because # these are whole months and there's no nice way to express that. # This is the historical behavior for monthly reports. # header row $rows[0] = {}; $cells[0] = [ { header => 1, rowspan => 2 }, map { { header => 1, colspan => 5, value => time2str('%b %Y', $_) } } @{ $data->{speriod} } ]; my $ncols = scalar(@{ $data->{speriod} }); $rows[1] = {}; $cells[1] = [ '', map { ( { header => 1, value => mt('Recur Fee') }, { header => 1, value => mt('Recur Cost') }, { header => 1, value => mt('Usage Fee') }, { header => 1, value => mt('Usage Cost') }, { header => 1, value => mt('Profit'), class => 'shaded' }, ) } (1..$ncols) ]; my $row = 0; foreach my $cust_main (@cust_main) { # correspond to cross_params my $skip = 1; # skip the customer iff ALL of their values are zero push @rows, {}; my @thisrow; # customer name push @thisrow, { value => $cust_main->name, header => 1 }; for my $col (0..$ncols-1) { # the month my $profit = 0; for my $item (0..3) { # recur/recur_cost/usage/usage_cost my $value = $data->{data}[$item][$col][$row]; $skip = 0 if abs($value) > 0.005; push @thisrow, { value => sprintf('%0.2f', $value), format => 'money', class => ($value < 0 ? 'negative' : ''), }; $total[$col * 5 + $item] += $value; $profit += (($item % 2) ? -1 : 1) * $value; } #item push @thisrow, { value => sprintf('%0.2f', $profit), format => 'money', class => 'shaded', }; $total[$col * 5 + 4] += $profit; } #month push @cells, \@thisrow; if ( $skip ) { # all values are zero--remove the rows we just added pop @rows; pop @cells; } $row++; } push @rows, { class => 'total' }; my @thisrow; push @thisrow, { value => mt('Total'), header => 1 }; for my $col (0..($ncols * 5)-1) { # month and recur/recur_cost/usage/usage_cost/profit my $value = $total[$col]; push @thisrow, { value => sprintf('%0.2f', $value), format => 'money', class => ($col % 5 == 4) ? 'totalshaded' : 'total', }; } push @cells, \@thisrow; my $head = q[ ];