X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fcustomer_accounting_summary.html;h=a8e503324030a00af6766ab108c3ec40ddd5ea55;hp=8da8914c8c648cc1437cc8b230032075ba08dc7b;hb=3f2a7b01b59902faed5767d81e2959e131bdbdfd;hpb=b5c4237a34aef94976bc343c8d9e138664fc3984 diff --git a/httemplate/search/customer_accounting_summary.html b/httemplate/search/customer_accounting_summary.html index 8da8914c8..a8e503324 100644 --- a/httemplate/search/customer_accounting_summary.html +++ b/httemplate/search/customer_accounting_summary.html @@ -1,59 +1,214 @@ -<% include('/graph/elements/monthly.html', - #Dumper( - 'title' => $title, - 'graph_type' => 'none', - 'items' => \@items, - 'params' => \@params, - 'labels' => \@labels, - 'graph_labels' => \@labels, - 'remove_empty' => 1, - 'bottom_total' => 1, - 'agentnum' => $agentnum, - 'doublemonths' => \@doublemonths, - 'nototal' => 1, - ) -%> +<& elements/grid-report.html, + title => $title, + rows => \@rows, + cells => \@cells, +&> <%init> die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); -my @doublemonths = ( 'Billed', 'Paid' ); - 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; } my $title = $sel_agent ? $sel_agent->agent.' ' : ''; + +my ($refnum,$sel_part_referral); +if ( $cgi->param('refnum') =~ /^(\d+)$/ ) { + $refnum = $1; + $sel_part_referral = qsearchs('part_referral', { 'refnum' => $refnum } ); + die "refnum $refnum not found!" unless $sel_part_referral; +} +$title .= $sel_part_referral->referral.' ' + if $sel_part_referral; + $title .= 'Customer Accounting Summary Report'; -my @custs = (); -@custs = qsearch('cust_main', {} ); +my @items = ('netsales', 'cashflow'); +my @params = ( [], [] ); +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'); + @params = ( + [ setuprecur => 'setup' ], + [ setuprecur => 'setup' ], + [ 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 @items = (); -my @params = (); my @labels = (); +my @cross_params = (); my $status = $cgi->param('status'); die "invalid status" unless $status =~ /^\w+|$/; -foreach my $cust_main ( @custs ) { - next unless ($status eq '' || $status eq $cust_main->status); - next unless ($agentnum == 0 || $cust_main->agentnum eq $agentnum); +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; + +my $query = FS::cust_main::Search->search(\%search_hash); +my @cust_main = qsearch($query); - push @items, 'netsales', 'cashflow'; +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, + refnum => $refnum, +); +for ( qw(start_month start_year end_month end_year) ) { + if ( $cgi->param($_) =~ /^(\d+)$/ ) { + $opt{$_} = $1; + } +} + +warn Dumper(OPTIONS => \%opt) if $cgi->param('debug'); +my $report = FS::Report::Table::Monthly->new(%opt); +my $data = $report->data; +warn Dumper(DATA => $data) if $cgi->param('debug') >= 2; + +my @total; - push @labels, $cust_main->name; +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, colspan => ($setuprecur ? 4 : 3) }, + map { + { header => 1, colspan => ($grossdiscount ? 3 : 2), value => time2str('%b %Y', $_) }, + } @{ $data->{speriod} } +]; +my $ncols = scalar(@{ $data->{speriod} }); + +$rows[1] = {}; +$cells[1] = [ + map { + ( ($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 $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' : '' }; + my @thisrow; + if ( $subrow == 0 ) { + # customer name + 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 + push @thisrow, + { value => $subrow ? mt('recurring') : mt('setup'), + header => 1 }; + } + for my $col (0..$ncols-1) { # the month + 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 ) * ($grossdiscount ? 3 : 2) + $subcol] += $value; + } #subcol + } #col + push @cells, \@thisrow; + } #subrow + if ( $skip ) { + # all values are zero--remove the rows we just added + pop @rows; + pop @cells; + if ( $setuprecur ) { + pop @rows; + pop @cells; + } + } + $row++; +} +for my $subrow (0..($setuprecur ? 1 : 0)) { + push @rows, { class => ($subrow ? 'totalshaded' : 'total') }; + my @thisrow; + if ( $subrow == 0 ) { + push @thisrow, + { value => mt('Total'), + header => 1, + colspan => 3, + rowspan => ($setuprecur ? 2 : 1), }; + } + if ( $setuprecur ) { + push @thisrow, + { value => $subrow ? mt('recurring') : mt('setup'), + header => 1 }; + } + 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; +} #subrow - push @params, [ ('custnum' => $cust_main->custnum), - ], - [ ('custnum' => $cust_main->custnum), - ]; +if ( $cgi->param('debug') >= 3 ) { + warn Dumper(\@rows, \@cells); } +my $title = 'Customer Accounting Summary';