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} );
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><BR>
85 % # as <A HREF="<% "$myself;_type=csv" %>">CSV file</A> # is this still needed?
87 <style type="text/css">
89 background-color: #f8f8f8;
90 border: 1px solid #999999;
96 .total * { background-color: #f5f6be; }
97 .shaded * { background-color: #c8c8c8; }
98 .totalshaded * { background-color: #bfc094; }
100 <table class="report" width="100%" cellspacing=0>
101 % foreach my $rowinfo (@rows) {
102 <tr<% $rowinfo->{class} ? ' class="'.$rowinfo->{class}.'"' : ''%>>
103 % my $thisrow = shift @cells;
104 % foreach my $cell (@$thisrow) {
105 % next if !ref($cell); # placeholders
106 % my $td = $cell->{header} ? 'th' : 'td';
108 % $style .= " rowspan=".$cell->{rowspan} if $cell->{rowspan} > 1;
109 % $style .= " colspan=".$cell->{colspan} if $cell->{colspan} > 1;
110 <<%$td%><%$style%>><% $cell->{value} |h %></<%$td%>>
116 <& /elements/footer.html &>
121 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
123 my ($agentnum,$sel_agent);
124 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
126 $sel_agent = qsearchs('agent', { 'agentnum' => $agentnum } );
127 die "agentnum $agentnum not found!" unless $sel_agent;
129 my $title = $sel_agent ? $sel_agent->agent.' ' : '';
131 my ($refnum,$sel_part_referral);
132 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
134 $sel_part_referral = qsearchs('part_referral', { 'refnum' => $refnum } );
135 die "refnum $refnum not found!" unless $sel_part_referral;
137 $title .= $sel_part_referral->referral.' '
138 if $sel_part_referral;
140 $title .= 'Customer Accounting Summary Report';
142 my @items = ('netsales', 'cashflow');
143 my @params = ( [], [] );
144 my $grossdiscount = $cgi->param('grossdiscount');
145 my $setuprecur = $cgi->param('setuprecur');
146 if ($setuprecur && $grossdiscount) {
147 #see blocks below for more details on each option
148 @items = ('gross', 'discounted', 'receipts', 'gross', 'discounted', 'receipts');
150 [ setuprecur => 'setup' ],
151 [ setuprecur => 'setup' ],
152 [ setuprecur => 'setup' ],
153 [ setuprecur => 'recur' ],
154 [ setuprecur => 'recur' ],
155 [ setuprecur => 'recur' ],
157 } elsif ($setuprecur) {
158 # instead of 'cashflow' (payments - refunds), use 'receipts'
159 # (applied payments), because it's divisible into setup and recur.
160 @items = ('netsales', 'receipts', 'netsales', 'receipts');
162 [ setuprecur => 'setup' ],
163 [ setuprecur => 'setup' ],
164 [ setuprecur => 'recur' ],
165 [ setuprecur => 'recur' ],
167 } elsif ($grossdiscount) {
168 # instead of 'netsales' (invoiced - netcredits)
169 # use 'gross' (invoiced + discounted) and 'discounted' (sum of discounts on invoices)
170 @items = ('gross', 'discounted', 'cashflow');
171 @params = ( [], [], [] );
176 my @cross_params = ();
178 my $status = $cgi->param('status');
179 die "invalid status" unless $status =~ /^\w+|$/;
182 foreach (qw(agentnum refnum status)) {
183 if ( defined $cgi->param($_) ) {
184 $search_hash{$_} = $cgi->param($_);
187 $search_hash{'classnum'} = [ $cgi->param('cust_classnum') ]
188 if grep { $_ eq 'cust_classnum' } $cgi->param;
190 my $query = FS::cust_main::Search->search(\%search_hash);
191 my @cust_main = qsearch($query);
193 foreach my $cust_main (@cust_main) {
194 push @cross_params, [ ('custnum' => $cust_main->custnum) ];
200 cross_params => \@cross_params,
201 agentnum => $agentnum,
204 for ( qw(start_month start_year end_month end_year) ) {
205 if ( $cgi->param($_) =~ /^(\d+)$/ ) {
210 warn Dumper(OPTIONS => \%opt) if $cgi->param('debug');
211 my $report = FS::Report::Table::Monthly->new(%opt);
212 my $data = $report->data;
213 warn Dumper(DATA => $data) if $cgi->param('debug') >= 2;
217 my @rows; # hashes of row info
218 my @cells; # arrayrefs of cell info
219 # We use Excel currency format, but not Excel dates, because
220 # these are whole months and there's no nice way to express that.
221 # This is the historical behavior for monthly reports.
226 { header => 1, rowspan => 2, colspan => ($setuprecur ? 4 : 3) },
227 ($setuprecur ? '' : ()),
229 { header => 1, colspan => ($grossdiscount ? 3 : 2), value => time2str('%b %Y', $_) },
231 } @{ $data->{speriod} }
233 my $ncols = scalar(@{ $data->{speriod} });
237 ($setuprecur ? '' : ()),
241 { header => 1, value => mt('Gross') },
242 { header => 1, value => mt('Discount') }
244 : { header => 1, value => mt('Billed') }
246 { header => 1, value => mt('Paid') },
250 # use PDL; # ha ha, I just might.
252 foreach my $cust_main (@cust_main) { # correspond to cross_params
253 my $skip = 1; # skip the customer iff ALL of their values are zero
254 for my $subrow (0..($setuprecur ? 1 : 0)) { # the setup/recur axis
255 push @rows, { class => $subrow ? 'shaded' : '' };
257 if ( $subrow == 0 ) {
260 { value => $cust_main->name,
262 rowspan => ($setuprecur ? 2 : 1),
264 { value => $cust_main->state, #cust_main->bill_location->state,
266 rowspan => ($setuprecur ? 2 : 1),
268 { value => $cust_main->salesnum ? $cust_main->sales->salesperson : '',
270 rowspan => ($setuprecur ? 2 : 1),
279 { value => $subrow ? mt('recurring') : mt('setup'),
282 for my $col (0..$ncols-1) { # the month
283 for my $subcol (0..($grossdiscount ? 2 : 1)) { # the billed/paid or gross/discount/paid axis
284 my $item = $subrow * ($grossdiscount ? 3 : 2) + $subcol;
285 my $value = $data->{data}[$item][$col][$row];
286 $skip = 0 if abs($value) > 0.005;
287 push @thisrow, { value => sprintf('%0.2f', $value), format => 'money' };
288 $total[( ($ncols * $subrow) + $col ) * ($grossdiscount ? 3 : 2) + $subcol] += $value;
291 push @cells, \@thisrow;
294 # all values are zero--remove the rows we just added
304 for my $subrow (0..($setuprecur ? 1 : 0)) {
305 push @rows, { class => ($subrow ? 'totalshaded' : 'total') };
307 if ( $subrow == 0 ) {
309 { value => mt('Total'),
312 rowspan => ($setuprecur ? 2 : 1), };
318 { value => $subrow ? mt('recurring') : mt('setup'),
321 for my $col (0..($ncols * ($grossdiscount ? 3 : 2))-1) { # month and billed/paid or gross/discount/paid axis
322 my $value = $total[($subrow * $ncols * ($grossdiscount ? 3 : 2)) + $col];
323 push @thisrow, { value => sprintf('%0.2f', $value), format => 'money' };
325 push @cells, \@thisrow;
328 if ( $cgi->param('debug') >= 3 ) {
329 warn Dumper(\@rows, \@cells);
332 my $title = 'Customer Accounting Summary';