<%
#bad false laziness with search/cust_main.cgi (also needs fixing up for
#old mysql)
my $ncancelled = "
0 < ( SELECT COUNT(*) FROM cust_pkg
WHERE cust_pkg.custnum = cust_main.custnum
AND ( cust_pkg.cancel IS NULL
OR cust_pkg.cancel = 0
)
)
OR 0 = ( SELECT COUNT(*) FROM cust_pkg
WHERE cust_pkg.custnum = cust_main.custnum
)
";
my $ncancelled_sth = dbh->prepare("SELECT COUNT(*) FROM cust_main
WHERE agentnum = ?
AND $ncancelled ")
or die dbh->errstr;
my $total_sth = dbh->prepare("SELECT COUNT(*) FROM cust_main
WHERE agentnum = ? ")
or die dbh->errstr;
%>
<%= header('Agent Listing', menubar(
'Main Menu' => $p,
'Agent Types' => $p. 'browse/agent_type.cgi',
# 'Add new agent' => '../edit/agent.cgi'
)) %>
Agents are resellers of your service. Agents may be limited to a subset of your
full offerings (via their type).
Add a new agent
<%= table() %>
| Agent |
Type |
Customers |
Freq. |
Prog. |
<%
# Agent # |
# Agent |
foreach my $agent ( sort {
#$a->getfield('agentnum') <=> $b->getfield('agentnum')
$a->getfield('agent') cmp $b->getfield('agent')
} qsearch('agent',{}) ) {
$ncancelled_sth->execute($agent->agentnum) or die $ncancelled_sth->errstr;
my $num_ncancelled = $ncancelled_sth->fetchrow_arrayref->[0];
$total_sth->execute($agent->agentnum) or die $total_sth->errstr;
my $num_total = $total_sth->fetchrow_arrayref->[0];
my $num_cancelled = $num_total - $num_ncancelled;
%>
|
<%= $agent->agentnum %> |
<%= $agent->agent %> |
<%= $agent->agent_type->atype %> |
<%= $num_ncancelled %>
active
<%= $num_cancelled %>
cancelled
|
$hashref->{freq} |
$hashref->{prog} |
<% } %>