X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fcustomer_accounting_summary.html;h=a8e503324030a00af6766ab108c3ec40ddd5ea55;hp=0e9e24fa6c9b92eae236678bc62e86f560175603;hb=3f2a7b01b59902faed5767d81e2959e131bdbdfd;hpb=96783bdc58be6e4f2fc56d516a9ceba57af00ba8 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); - -% } 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 %>> -% } - -% } -
- -<& /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;