1 % if ( $cgi->param('_type') =~ /(xls)$/ ) {
3 # egregious false laziness w/ search/report_tax-xls.cgi
4 my $format = $FS::CurrentUser::CurrentUser->spreadsheet_format;
5 my $filename = $cgi->url(-relative => 1);
6 $filename =~ s/\.html$//;
7 $filename .= $format->{extension};
8 http_header('Content-Type' => $format->{mime_type});
9 http_header('Content-Disposition' => qq!attachment;filename="$filename"!);
12 my $XLS = IO::String->new($output);
13 my $workbook = $format->{class}->new($XLS)
14 or die "Error opening .xls file: $!";
16 my $worksheet = $workbook->add_worksheet('Summary');
38 foreach (keys %format) {
39 my %f = (%default, %{$format{$_}});
40 $format{$_} = $workbook->add_format(%f);
41 $format{"m_$_"} = $workbook->add_format(%f);
47 my $thisrow = shift @cells;
48 for my $cell (@$thisrow) {
50 # placeholder, so increment $c so that we write to the correct place
56 $f = 'header' if $row->{header} or $cell->{header};
57 $f = 'money' if $cell->{format} eq 'money';
58 if ( $cell->{rowspan} > 1 or $cell->{colspan} > 1 ) {
59 my $range = xl_range_formula(
61 $r, $r - 1 + ($cell->{rowspan} || 1),
62 $c, $c - 1 + ($cell->{colspan} || 1)
64 #warn "merging $range\n";
65 $worksheet->merge_range($range, $cell->{value}, $format{"m_$f"});
67 #warn "writing ".xl_rowcol_to_cell($r, $c)."\n";
68 $worksheet->write( $r, $c, $cell->{value}, $format{$f} );
70 $c += $cell->{colspan} || 1;
76 http_header('Content-Length' => length($output));
80 <& /elements/header.html, $title &>
81 % my $myself = $cgi->self_url;
82 <P ALIGN="right" CLASS="noprint">
83 Download full reports<BR>
84 as <A HREF="<% "$myself;_type=xls" %>">Excel spreadsheet</A>
86 <style type="text/css">
88 background-color: #f8f8f8;
89 border: 1px solid #999999;
95 .total { background-color: #f5f6be; }
96 .shaded { background-color: #c8c8c8; }
97 .totalshaded { background-color: #bfc094; }
99 <table class="report" width="100%" cellspacing=0>
100 % foreach my $rowinfo (@rows) {
101 <tr<% $rowinfo->{class} ? ' class="'.$rowinfo->{class}.'"' : ''%>>
102 % my $thisrow = shift @cells;
103 % foreach my $cell (@$thisrow) {
104 % next if !ref($cell); # placeholders
105 % my $td = $cell->{header} ? 'th' : 'td';
107 % $style .= ' class="'.$cell->{class}.'"' if $cell->{class};
108 % $style .= " rowspan=".$cell->{rowspan} if $cell->{rowspan} > 1;
109 % $style .= " colspan=".$cell->{colspan} if $cell->{colspan} > 1;
110 % $style .= ' style="color: red"' if $cell->{value} < 0;
111 <<%$td%><%$style%>><% $cell->{value} |h %></<%$td%>>
117 <& /elements/footer.html &>
122 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports')
123 && $FS::CurrentUser::CurrentUser->access_right('List rating data');
125 my ($agentnum,$sel_agent);
126 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
128 $sel_agent = qsearchs('agent', { 'agentnum' => $agentnum } );
129 die "agentnum $agentnum not found!" unless $sel_agent;
131 my $title = $sel_agent ? $sel_agent->agent.' ' : '';
133 $title .= 'Customer CDRs Profit/Loss Report';
135 my @items = ('cust_bill_pkg_recur', 'cust_bill_pkg_recur', 'cust_bill_pkg_detail', 'cust_bill_pkg_detail' );
136 my @params = ( [], [ 'cost' => 1 ], [], [ 'cost' => 1 ] );
139 my @cross_params = ();
142 foreach (qw(agentnum)) {
143 if ( defined $cgi->param($_) ) {
144 $search_hash{$_} = $cgi->param($_);
148 my $query = FS::cust_main::Search->search(\%search_hash);
149 my @cust_main = qsearch($query);
151 foreach my $cust_main (@cust_main) {
152 push @cross_params, [ ('custnum' => $cust_main->custnum) ];
158 cross_params => \@cross_params,
159 agentnum => $agentnum,
161 for ( qw(start_month start_year end_month end_year) ) {
162 if ( $cgi->param($_) =~ /^(\d+)$/ ) {
167 my $report = FS::Report::Table::Monthly->new(%opt);
168 my $data = $report->data;
170 ### False laziness with customer_accounting_summary.html
173 my @rows; # hashes of row info
174 my @cells; # arrayrefs of cell info
175 # We use Excel currency format, but not Excel dates, because
176 # these are whole months and there's no nice way to express that.
177 # This is the historical behavior for monthly reports.
182 { header => 1, rowspan => 2 },
184 { header => 1, colspan => 5, value => time2str('%b %Y', $_) }
185 } @{ $data->{speriod} }
187 my $ncols = scalar(@{ $data->{speriod} });
193 { header => 1, value => mt('Recur Fee') },
194 { header => 1, value => mt('Recur Cost') },
195 { header => 1, value => mt('Usage Fee') },
196 { header => 1, value => mt('Usage Cost') },
197 { header => 1, value => mt('Profit'), class => 'shaded' },
202 foreach my $cust_main (@cust_main) { # correspond to cross_params
203 my $skip = 1; # skip the customer iff ALL of their values are zero
208 { value => $cust_main->name,
211 for my $col (0..$ncols-1) { # the month
213 for my $item (0..3) { # recur/recur_cost/usage/usage_cost
214 my $value = $data->{data}[$item][$col][$row];
215 $skip = 0 if abs($value) > 0.005;
216 push @thisrow, { value => sprintf('%0.2f', $value), format => 'money' };
217 $total[$col * 5 + $item] += $value;
218 $profit += (($item % 2) ? -1 : 1) * $value;
221 value => sprintf('%0.2f', $profit),
225 $total[$col * 5 + 4] += $profit;
227 push @cells, \@thisrow;
230 # all values are zero--remove the rows we just added
237 push @rows, { class => 'total' };
240 { value => mt('Total'),
243 for my $col (0..($ncols * 5)-1) { # month and recur/recur_cost/usage/usage_cost/profit
244 my $value = $total[$col];
246 value => sprintf('%0.2f', $value),
248 class => ($col % 5 == 4) ? 'totalshaded' : 'total',
251 push @cells, \@thisrow;