& elements/grid-report.html,
title => $title.'Package Agent Credits and Payments',
rows => $rows,
cells => $cells,
head => <Shows agent commission credits, and payments applied to invoices for packages that triggered those credits.
END
&>
<%init>
my $curuser = $FS::CurrentUser::CurrentUser;
die "access denied"
unless $curuser->access_right('Financial reports');
my $extra_sql = '';
# search for agent
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;
$extra_sql .= " AND cust_credit.commission_agentnum = $agentnum\n";
}
my $title = $sel_agent ? $sel_agent->agent.' ' : '';
# search for credits in time period (applied to payments in $query)
my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
$extra_sql .= " AND cust_credit._date >= $beginning\n";
$extra_sql .= " AND cust_credit._date <= $ending\n";
# agent virtualization
my $agentnums_sql = $curuser->agentnums_sql( table => 'agent' );
my $join = <= $beginning
AND cust_pay._date <= $ending
AND $agentnums_sql
$extra_sql
UNION
SELECT DISTINCT
agent,
cust_pkg.custnum AS xcustnum,
cust_credit.commission_pkgnum AS xpkgnum,
pkg,
'cust_credit' AS xtable,
cust_credit.crednum AS xnum,
to_timestamp(cust_credit._date) AS xdate,
cust_credit.amount AS xamount,
'' AS order_number
FROM cust_credit
$join
WHERE cust_credit.commission_pkgnum is not null
AND $agentnums_sql
$extra_sql
ORDER BY agent, xcustnum, xpkgnum, xdate
END
my $sth = dbh->prepare($query) or die dbh->errstr;
$sth->execute() or die $sth->errstr;
my $cells = [];
my $rows = [];
my $agentstack = [];
my $custstack = [];
my $pkgstack = [];
my ($prev_agent,$count_agent,$prev_cust,$count_cust,$prev_pkg,$count_pkg);
while (my $row = $sth->fetchrow_arrayref) {
my @row = @$row;
my $curr_agent = shift @row;
my $curr_cust = shift @row;
my $curr_pkg = (shift @row) . ': ' . (shift @row);
if ($curr_pkg eq $prev_pkg) {
$count_pkg += 1;
} else {
unshift @{$$pkgstack[0]}, { value => $prev_pkg, rowspan => $count_pkg } if @$pkgstack;;
push @$custstack, @$pkgstack;
$pkgstack = [];
$count_pkg = 1;
}
$prev_pkg = $curr_pkg;
if ($curr_cust eq $prev_cust) {
$count_cust += 1;
} else {
if (@$custstack) {
my $cust_main = qsearchs('cust_main',{ custnum => $prev_cust });
unshift @{$$custstack[0]}, { value => $cust_main->name, rowspan => $count_cust } if @$custstack;;
}
push @$agentstack, @$custstack;
$custstack = [];
$count_cust = 1;
}
$prev_cust = $curr_cust;
if ($curr_agent eq $prev_agent) {
$count_agent += 1;
} else {
unshift @{$$agentstack[0]}, { value => $prev_agent, rowspan => $count_agent } if @$agentstack;;
push @$cells, @$agentstack;
$agentstack = [];
$count_agent = 1;
}
$prev_agent = $curr_agent;
my %coloropts = ($row[0] eq 'cust_credit') ? ( 'class' => 'creditcell' ) : ( 'class' => 'paycell' );
push @$pkgstack, [ map { { value => $_, %coloropts } } @row ];
}
unshift @{$$pkgstack[0]}, { value => $prev_pkg, rowspan => $count_pkg } if @$pkgstack;;
push @$custstack, @$pkgstack;
if (@$custstack) {
my $cust_main = qsearchs('cust_main',{ custnum => $prev_cust });
unshift @{$$custstack[0]}, { value => $cust_main->name, rowspan => $count_cust } if @$custstack;;
}
push @$agentstack, @$custstack;
unshift @{$$agentstack[0]}, { value => $prev_agent, rowspan => $count_agent } if @$agentstack;;
push @$cells, @$agentstack;
$sth->finish;
my $rows = [ map { {} } @$cells ];
unshift @$cells, [ map { { value => $_, header => 1 } } ('Agent','Customer','Package','Table','#','Date','Amount','Order Number') ];
unshift @$rows, { header => 1 };
%init>