<& 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') =~ /^(\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 @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 @labels = (); my @cross_params = (); my $status = $cgi->param('status'); die "invalid status" unless $status =~ /^\w+|$/; 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); 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; 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 if ( $cgi->param('debug') >= 3 ) { warn Dumper(\@rows, \@cells); } my $title = 'Customer Accounting Summary';