% if ( $cgi->param('_type') =~ /(xls)$/ ) {
<%perl>
# egregious false laziness w/ search/report_tax-xls.cgi
my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format;
my $filename = $cgi->url(-relative => 1);
$filename =~ s/\.html$//;
$filename .= $format->{extension};
http_header('Content-Type' => $format->{mime_type});
http_header('Content-Disposition' => qq!attachment;filename="$filename"!);
my $output = '';
my $XLS = IO::String->new($output);
my $workbook = $format->{class}->new($XLS)
or die "Error opening .xls file: $!";
my $worksheet = $workbook->add_worksheet('Summary');
my %format = (
header => {
size => 11,
bold => 1,
align => 'center',
valign => 'vcenter',
text_wrap => 1,
},
money => {
size => 11,
align => 'right',
valign => 'bottom',
num_format=> 8,
},
'' => {},
);
my %default = (
font => 'Calibri',
border => 1,
);
foreach (keys %format) {
my %f = (%default, %{$format{$_}});
$format{$_} = $workbook->add_format(%f);
$format{"m_$_"} = $workbook->add_format(%f);
}
my ($r, $c) = (0, 0);
for my $row (@rows) {
$c = 0;
my $thisrow = shift @cells;
for my $cell (@$thisrow) {
if (!ref($cell)) {
# placeholder, so increment $c so that we write to the correct place
$c++;
next;
}
# format name
my $f = '';
$f = 'header' if $row->{header} or $cell->{header};
$f = 'money' if $cell->{format} eq 'money';
if ( $cell->{rowspan} > 1 or $cell->{colspan} > 1 ) {
my $range = xl_range_formula(
'Summary',
$r, $r - 1 + ($cell->{rowspan} || 1),
$c, $c - 1 + ($cell->{colspan} || 1)
);
#warn "merging $range\n";
$worksheet->merge_range($range, $cell->{value}, $format{"m_$f"});
} else {
#warn "writing ".xl_rowcol_to_cell($r, $c)."\n";
$worksheet->write( $r, $c, $cell->{value}, $format{$f} );
}
$c += $cell->{colspan} || 1;
} #$cell
$r++;
} #$row
$workbook->close;
http_header('Content-Length' => length($output));
$m->print($output);
% } else {
<& /elements/header.html, $title &>
% my $myself = $cgi->self_url;
Download full reports
as ">Excel spreadsheet
% foreach my $rowinfo (@rows) {
{class} ? ' class="'.$rowinfo->{class}.'"' : ''%>>
% my $thisrow = shift @cells;
% foreach my $cell (@$thisrow) {
% next if !ref($cell); # placeholders
% my $td = $cell->{header} ? 'th' : 'td';
% my $style = '';
% $style .= ' class="'.$cell->{class}.'"' if $cell->{class};
% $style .= " rowspan=".$cell->{rowspan} if $cell->{rowspan} > 1;
% $style .= " colspan=".$cell->{colspan} if $cell->{colspan} > 1;
% $style .= ' style="color: red"' if $cell->{value} < 0;
<<%$td%><%$style%>><% $cell->{value} |h %>>
% }
% }
<& /elements/footer.html &>
% }
<%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' };
$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;