<?
include "./auth.inc.php";
$dsql = "SELECT DealerID, DealerName, MasterAccountID FROM Dealer ORDER BY DealerName";
$dres=mysql_query($dsql);
while ($drow = mysql_fetch_row($dres)){
$dealer[$drow[0]] = $drow[1];
$link[$drow[0]] = $drow[2];
$k[$drow[0]] = $drow[0];
if ($drow[2]){
$lsql = "SELECT TO_DAYS(now())-TO_DAYS(LastUse) FROM MasterAccounts WHERE CustomerID=$drow[2]";
$lres = mysql_query($lsql);
$lastuse[$drow[0]] = mysql_result($lres,0);
if (!$lastuse[$drow[0]] && $lastuse[$drow[0]] <> '0')
$lastuse[$drow[0]] = -1;
}
}
?><HTML>
<HEAD>
</HEAD>
<BODY bgcolor=white>
<H2>Dealer Summary</H2>
<TABLE border=1>
<TR><TH rowspan=2><A HREF="dealersummary.php">Dealer Name</A></TH>
<TH colspan=2>Dealer Special Accounts</TH><TH colspan=2>Paying
Customers</TH><TH COLSPAN=3>Ratios</th>
<TH rowspan=2><A HREF="dealersummary.php?sort=ln">Days .inc.php.php.php.php.php.phpe <BR>Last New User</A></TH>
<TH rowspan=2><A HREF="dealersummary.php?sort=lc">Days .inc.php.php.php.php.php.phpe <BR>Last Call</A></TH>
</TR>
<TR><th><A HREF="dealersummary.php?sort=dc">Current</A></th>
<th><A HREF="dealersummary.php?sort=dd">Dead</A></TH>
<th><A HREF="dealersummary.php?sort=cc">Current</A></th>
<th><A HREF="dealersummary.php?sort=cd">Dead</A></TH>
<TH><A HREF="dealersummary.php?sort=kp">Joined</A></TH>
<TH><A HREF="dealersummary.php?sort=cr">Current</A></TH>
<TH><A HREF="dealersummary.php?sort=ch">Churn</A></TH>
</TR>
<?
#should probably fill this into an array to allow sorting..
$sql = "SELECT Reseller, AccountType, MasterAccounts.Active, TO_DAYS(now()) - TO_DAYS(CreateDate)
FROM MasterAccounts LEFT JOIN SubAccounts ON MasterAccounts.CustomerID = SubAccounts.CustomerID
WHERE Reseller >0";
#$sql = "SELECT Reseller, AccountType, MasterAccounts.Active
# FROM MasterAccounts, SubAccounts
# WHERE Reseller>0
# AND MasterAccounts.CustomerID = SubAccounts.CustomerID";
$res = mysql_query($sql);
if (mysql_error())
die(mysql_error());
while ($row = mysql_fetch_row($res)){
if ($row[1] == 'dealerspecial'){
if ($row[2]){
$trial[$row[0]]++;
}else{
$deadtrial[$row[0]]++;
}
$ds++;
}else{
if ($row[2]){
$customer[$row[0]]++;
}else{
$deadcustomer[$row[0]]++;
}
$norm++;
}
if ($row[3] < $lastuser[$row[0]] || !$lastuser[$row[0]])
$lastuser[$row[0]] = $row[3];
}
for ($i=0;$i<count($k)+1;$i++){
if ($customer[$i]){
$keeppercent[$i] = ($customer[$i] + $deadcustomer[$i])/ ($customer[$i] + $deadcustomer[$i] + $deadtrial[$i]);
$curpercent[$i] = $customer[$i] / ($customer[$i] + $deadcustomer[$i] + $deadtrial[$i] + $trial[$i]);
$churn[$i] = $deadcustomer[$i]/($customer[$i]+$deadcustomer[$i]);
}
}
if ($sort){
for ($i=1;$i<count($k);$i++){
#print "!";
for ($j=1;$j<count($k)-$i;$j++){
#sort criteria is paying customers
#print $k[$j].":";
if ($sort == 'cc'){
if ($customer[$k[$j]] < $customer[$k[$j+1]]){
#print "dealer ".$dealer[$k[$j]]."\n";
$tmp = $k[$j];
$k[$j] = $k[$j+1];
$k[$j+1] = $tmp;
}
}
if ($sort == 'cd'){
if ($deadcustomer[$k[$j]] < $deadcustomer[$k[$j+1]]){
#print "dealer ".$dealer[$k[$j]]."\n";
$tmp = $k[$j];
$k[$j] = $k[$j+1];
$k[$j+1] = $tmp;
}
}
if ($sort == 'dd'){
if ($deadtrial[$k[$j]] < $deadtrial[$k[$j+1]]){
#print "dealer ".$dealer[$k[$j]]."\n";
$tmp = $k[$j];
$k[$j] = $k[$j+1];
$k[$j+1] = $tmp;
}
}
if ($sort == 'dc'){
if ($trial[$k[$j]] < $trial[$k[$j+1]]){
#print "dealer ".$dealer[$k[$j]]."\n";
$tmp = $k[$j];
$k[$j] = $k[$j+1];
$k[$j+1] = $tmp;
}
}
if ($sort == 'kp'){
if ($keeppercent[$k[$j]] < $keeppercent[$k[$j+1]]){
#print "dealer ".$dealer[$k[$j]]."\n";
$tmp = $k[$j];
$k[$j] = $k[$j+1];
$k[$j+1] = $tmp;
}
}
if ($sort == 'cr'){
if ($curpercent[$k[$j]] < $curpercent[$k[$j+1]]){
#print "dealer ".$dealer[$k[$j]]."\n";
$tmp = $k[$j];
$k[$j] = $k[$j+1];
$k[$j+1] = $tmp;
}
}
if ($sort == 'ln'){
if ($lastuser[$k[$j]] < $lastuser[$k[$j+1]]){
#print "dealer ".$dealer[$k[$j]]."\n";
$tmp = $k[$j];
$k[$j] = $k[$j+1];
$k[$j+1] = $tmp;
}
}
if ($sort == 'lc'){
if ($lastuse[$k[$j]] < $lastuse[$k[$j+1]]){
#print "dealer ".$dealer[$k[$j]]."\n";
$tmp = $k[$j];
$k[$j] = $k[$j+1];
$k[$j+1] = $tmp;
}
}
if ($sort == 'ch'){
if ($churn[$k[$j]] < $churn[$k[$j+1]]){
#print "dealer ".$dealer[$k[$j]]."\n";
$tmp = $k[$j];
$k[$j] = $k[$j+1];
$k[$j+1] = $tmp;
}
}
}
}
}
for ($x=1;$x<count($k)-1;$x++){
$i = $k[$x];
print "<TR><TD>";
if ($link[$i])
print "<A HREF=\"user.php?ID=$link[$i]\">";
print $dealer[$i];
if ($link[$i])
print "</A>";
print "</TD><TD>";
print intval($trial[$i]);
print "</TD><TD>";
print intval($deadtrial[$i]);
print "</TD><TD>";
print intval($customer[$i]);
print "</TD><TD>";
print intval($deadcustomer[$i]);
print "</TD><TD>";
print intval($keeppercent[$i]*100)."%";
print "</TD><TD>";
print intval($curpercent[$i]*100)."%";
print "</TD><TD>";
print intval($churn[$i]*100)."%";
print "</TD><TD>";
if ($lastuser[$i] || $lastuser[$i] == '0')
print $lastuser[$i];
else
print " ";
print "</TD><TD>";
if ($lastuse[$i] == -1){
print " ";
}else{
print intval($lastuse[$i]);
}
print "</TD></TR>\n";
}
?>
</TABLE>
<? print "Normal Customers $norm, Dealer Specials $ds\n";
?>