X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcustomer_accounting_summary.html;h=a8e503324030a00af6766ab108c3ec40ddd5ea55;hb=d2485c6fa278d7a4a1c940605816013f41d1f7e0;hp=0e9e24fa6c9b92eae236678bc62e86f560175603;hpb=e513d7cb331d7c7d99d1ea7b5f0a5f8e08e0d725;p=freeside.git
diff --git a/httemplate/search/customer_accounting_summary.html b/httemplate/search/customer_accounting_summary.html
index 0e9e24fa6..a8e503324 100644
--- a/httemplate/search/customer_accounting_summary.html
+++ b/httemplate/search/customer_accounting_summary.html
@@ -1,130 +1,15 @@
-% 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
- $r++;
- } #$row
- $workbook->close;
-
- http_header('Content-Length' => length($output));
- $m->print($output);
-%perl>
-% } else {
-<& /elements/header.html, $title &>
-% my $myself = $cgi->self_url;
-
-Download full reports
-as ">Excel spreadsheet
-% # as ">CSV file # is this still needed?
-
-
-
-% 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 .= " rowspan=".$cell->{rowspan} if $cell->{rowspan} > 1;
-% $style .= " colspan=".$cell->{colspan} if $cell->{colspan} > 1;
- <<%$td%><%$style%>><% $cell->{value} |h %><%$td%>>
-% }
-
-% }
-
-
-<& /elements/footer.html &>
-% }
+<& elements/grid-report.html,
+ title => $title,
+ rows => \@rows,
+ cells => \@cells,
+&>
<%init>
die "access denied"
unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
my ($agentnum,$sel_agent);
-if ( $cgi->param('agentnum') eq 'all' ) {
- $agentnum = 0;
-}
-elsif ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
+if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
$agentnum = $1;
$sel_agent = qsearchs('agent', { 'agentnum' => $agentnum } );
die "agentnum $agentnum not found!" unless $sel_agent;
@@ -142,14 +27,22 @@ $title .= $sel_part_referral->referral.' '
$title .= 'Customer Accounting Summary Report';
-my @custs = ();
-@custs = qsearch('cust_main', {} );
-
my @items = ('netsales', 'cashflow');
my @params = ( [], [] );
-my $setuprecur = '';
-if ( $cgi->param('setuprecur') ) {
- $setuprecur = 1;
+my $grossdiscount = $cgi->param('grossdiscount');
+my $setuprecur = $cgi->param('setuprecur');
+if ($setuprecur && $grossdiscount) {
+ #see blocks below for more details on each option
+ @items = ('gross', 'discounted', 'receipts', 'gross', 'discounted', 'receipts');
+ @params = (
+ [ setuprecur => 'setup' ],
+ [ setuprecur => 'setup' ],
+ [ setuprecur => 'setup' ],
+ [ setuprecur => 'recur' ],
+ [ setuprecur => 'recur' ],
+ [ setuprecur => 'recur' ],
+ );
+} elsif ($setuprecur) {
# instead of 'cashflow' (payments - refunds), use 'receipts'
# (applied payments), because it's divisible into setup and recur.
@items = ('netsales', 'receipts', 'netsales', 'receipts');
@@ -159,22 +52,33 @@ if ( $cgi->param('setuprecur') ) {
[ setuprecur => 'recur' ],
[ setuprecur => 'recur' ],
);
+} elsif ($grossdiscount) {
+ # instead of 'netsales' (invoiced - netcredits)
+ # use 'gross' (invoiced + discounted) and 'discounted' (sum of discounts on invoices)
+ @items = ('gross', 'discounted', 'cashflow');
+ @params = ( [], [], [] );
}
+
+
my @labels = ();
my @cross_params = ();
-my @custnames = ();
my $status = $cgi->param('status');
die "invalid status" unless $status =~ /^\w+|$/;
-foreach my $cust_main ( @custs ) {
- # XXX should do this in the qsearch
- next unless ($status eq '' || $status eq $cust_main->status);
- next unless ($agentnum == 0 || $cust_main->agentnum eq $agentnum);
- next unless ($refnum == 0 || $cust_main->refnum eq $refnum);
+my %search_hash;
+foreach (qw(agentnum refnum status)) {
+ if ( defined $cgi->param($_) ) {
+ $search_hash{$_} = $cgi->param($_);
+ }
+}
+$search_hash{'classnum'} = [ $cgi->param('cust_classnum') ]
+ if grep { $_ eq 'cust_classnum' } $cgi->param;
- push @custnames, $cust_main->name;
+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) ];
}
@@ -207,27 +111,30 @@ my @cells; # arrayrefs of cell info
# header row
$rows[0] = {};
$cells[0] = [
- { header => 1, rowspan => 2, colspan => ($setuprecur ? 2 : 1) },
- ($setuprecur ? '' : ()),
+ { header => 1, rowspan => 2, colspan => ($setuprecur ? 4 : 3) },
map {
- { header => 1, colspan => 2, value => time2str('%b %Y', $_) },
- ''
+ { header => 1, colspan => ($grossdiscount ? 3 : 2), value => time2str('%b %Y', $_) },
} @{ $data->{speriod} }
];
my $ncols = scalar(@{ $data->{speriod} });
$rows[1] = {};
-$cells[1] = [ '',
- ($setuprecur ? '' : ()),
+$cells[1] = [
map {
- ( { header => 1, value => mt('Billed') },
- { header => 1, value => mt('Paid') }
+ ( ($grossdiscount
+ ? (
+ { header => 1, value => mt('Gross') },
+ { header => 1, value => mt('Discount') }
+ )
+ : { header => 1, value => mt('Billed') }
+ ),
+ { header => 1, value => mt('Paid') },
) } (1..$ncols)
];
# use PDL; # ha ha, I just might.
my $row = 0;
-foreach my $name (@custnames) { # correspond to cross_params
+foreach my $cust_main (@cust_main) { # correspond to cross_params
my $skip = 1; # skip the customer iff ALL of their values are zero
for my $subrow (0..($setuprecur ? 1 : 0)) { # the setup/recur axis
push @rows, { class => $subrow ? 'shaded' : '' };
@@ -235,11 +142,19 @@ foreach my $name (@custnames) { # correspond to cross_params
if ( $subrow == 0 ) {
# customer name
push @thisrow,
- { value => $name,
- header => 1,
- rowspan => ($setuprecur ? 2 : 1) };
- } else {
- push @thisrow, '';
+ { value => $cust_main->name,
+ header => 1,
+ rowspan => ($setuprecur ? 2 : 1),
+ },
+ { value => $cust_main->state, #cust_main->bill_location->state,
+ header => 1,
+ rowspan => ($setuprecur ? 2 : 1),
+ },
+ { value => $cust_main->salesnum ? $cust_main->sales->salesperson : '',
+ header => 1,
+ rowspan => ($setuprecur ? 2 : 1),
+ },
+ ;
}
if ( $setuprecur ) {
# subheading
@@ -248,12 +163,12 @@ foreach my $name (@custnames) { # correspond to cross_params
header => 1 };
}
for my $col (0..$ncols-1) { # the month
- for my $subcol (0..1) { # the billed/paid axis
- my $item = $subrow * 2 + $subcol;
+ for my $subcol (0..($grossdiscount ? 2 : 1)) { # the billed/paid or gross/discount/paid axis
+ my $item = $subrow * ($grossdiscount ? 3 : 2) + $subcol;
my $value = $data->{data}[$item][$col][$row];
$skip = 0 if abs($value) > 0.005;
push @thisrow, { value => sprintf('%0.2f', $value), format => 'money' };
- $total[( ($ncols * $subrow) + $col ) * 2 + $subcol] += $value;
+ $total[( ($ncols * $subrow) + $col ) * ($grossdiscount ? 3 : 2) + $subcol] += $value;
} #subcol
} #col
push @cells, \@thisrow;
@@ -276,17 +191,16 @@ for my $subrow (0..($setuprecur ? 1 : 0)) {
push @thisrow,
{ value => mt('Total'),
header => 1,
+ colspan => 3,
rowspan => ($setuprecur ? 2 : 1), };
- } else {
- push @thisrow, '';
}
if ( $setuprecur ) {
push @thisrow,
{ value => $subrow ? mt('recurring') : mt('setup'),
header => 1 };
}
- for my $col (0..($ncols * 2)-1) { # month and billed/paid axis
- my $value = $total[($subrow * $ncols * 2) + $col];
+ for my $col (0..($ncols * ($grossdiscount ? 3 : 2))-1) { # month and billed/paid or gross/discount/paid axis
+ my $value = $total[($subrow * $ncols * ($grossdiscount ? 3 : 2)) + $col];
push @thisrow, { value => sprintf('%0.2f', $value), format => 'money' };
}
push @cells, \@thisrow;