<?
session_start();
include("../opendb.php");
include("../test.php");
?>
<head>
<TITLE>EXECUTIVE WISE REPORT</TITLE>
<style>
tr{font-family:arial;font-size:13;background-color:white}
TH{COLOR:WHITE;BACKGROUND-COLOR:STEELBLUE}
</style>

<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<CENTER>

<?
if($show && $pswd=='repo')
{
$df=$y.'-'.$m.'-'.$d;
$dt=$y1.'-'.$m1.'-'.$d1;
print "<FONT FACE='ARIAL BLACK' color='steelblue'>
EXECUTIVE WISE REPORT FROM ".datetohtml($df).' TO '.datetohtml($dt)."
</FONT>";
$tbl="exec".$user;
mysqli_query($db,"create table $tbl (prospect varchar(100),platf varchar(5),date date)");
mysqli_query($db,"delete from $tbl");
$chre=mysqli_query($db,"select prospect,platf,date from followup where date>='$df' and date<='$dt'  order by date")or die(mysqli_error($db));
while($chrow=mysqli_fetch_array($chre))
{
  $achre=mysqli_query($db,"select * from $tbl where prospect='$chrow[prospect]'"); 
   if(mysqli_num_rows($achre)>0)
   { 
    mysqli_query($db,"delete from $tbl where prospect='$chrow[prospect]'"); 
    mysqli_query($db,"insert into $tbl values('$chrow[prospect]','$chrow[platf]','$chrow[date]')")or die(mysqli_error($db));
   } 
    else
    {
      mysqli_query($db,"insert into $tbl values('$chrow[prospect]','$chrow[platf]','$chrow[date]')")or die(mysqli_error($db));
    }
}
$q="select platf,count(platf) from $tbl group by platf order by platf ";
$sumres=mysqli_query($db,$q)or die(mysqli_error($db));
print "<table width=70%><tr><td><center>";
print "<table border=1><th colspan=2>Summary of all executives</th><tr><th>Platf <th>Count";
while($sumrow=mysqli_fetch_row($sumres))
{
 print "<tr><td><center><a href='viewpl.php?pl=$sumrow[0]&df=$df&dt=$dt' target='a'>$sumrow[0]</a></td><td><center>$sumrow[1]</td>";
}
print "</table>";
print "</td><td align=left>";
$sumres1=mysqli_query($db,"select platf,count(platf) from $tbl group by platf order by platf")or die(mysqli_error($db));
//print "<table border=1><tr><td>";
?>
<table>
<tr>
<tr><td>Legend</td>
<td bgcolor='ecac2c'>&nbsp;</td><td>S1</td>
<td bgcolor='3b8717'>&nbsp;</td><td>S2</td>
<td bgcolor='b94e20'>&nbsp;</td><td>S3</td>
<td bgcolor='e765df'>&nbsp;</td><td>S4</td>
<td bgcolor='red'>&nbsp;</td><td>S5</td>
</table><br>
<?
print "<table border=0 cellspacing=0 cellpadding=0 width=50%><th colspan=2>Chart";
while($sumrow=mysqli_fetch_row($sumres1))
{
 print "<tr><td>
<center><table border=0 cellspacing=0 cellpadding=0 width=".($sumrow[1]*2).">";
if($sumrow[0]=='S1')
 print "<td bgcolor='ecac2c'>";
else if($sumrow[0]=='S2')
 print "<td bgcolor='3b8717'>";
else if($sumrow[0]=='S3')
 print "<td bgcolor='b94e20'>";
else if($sumrow[0]=='S4')
 print "<td bgcolor='e765df'>";
else if($sumrow[0]=='S5')
 print "<td bgcolor='red'>";

print "<font color=black>&nbsp;</td></td></table>";
}
print "</td></tr></table>";
print "</td></tr></table>";

if($exec=='All')
$sql="select distinct executive from followup where date>='$df' and date<='$dt' order by executive";
else
$sql="select distinct executive from followup where date>='$df' and date<='$dt' and executive='$exec' order by executive";


$re=mysqli_query($db,$sql);

while($row=mysqli_fetch_array($re))

{
 print "<fieldset><legend><b>$row[0]</b></legend>";
 print "<table cellspacing=1 cellpadding=2 bgcolor='lightsteelblue' width=100%>";
 print "<th>No <th>Visit Date <th>Entry Date<th>Time Frm <th>Time To<th>Model<th>Prospect <th>Address1<th>City<th>Dist<th>Phone<th>Mob<th>Nxt Obj<th>Nxt Visit Dt. <th>Platform <th>Discus <th>Action to take<th>Remark";
 $re2=mysqli_query($db,"select * from followup where user='$row[0]' and date>='$df' and date<='$dt' order by date");
$sql="select active from bruser where username='$row[0]'";

$rw=mysqli_query($db,$sql);
$rew=mysqli_fetch_array($rw);

if($rew[0]=='Y')
 {
$i=1;
  $rspn=mysqli_num_rows($re2);
 while($row1=mysqli_fetch_array($re2))
 {
  $mod=mysqli_query($db,"select model from inquiry where inqno='$row1[inqno]'");
  $mod_row=mysqli_fetch_row($mod);
  print "\n<tr><td>".$i++."</td>";
  print "<td>".datetohtml($row1[followdate])."</td>";
    print "<td><center>".datetohtml(substr($row1['ondate'],0,10)).substr($row1['ondate'],10)
."</td>";
  print "<td align=center>".substr($row1[tfrom],0,5)."</td>";
  print "<td align=center>".substr($row1[tto],0,5)."</td>";

  print "<td>".($mod_row[0])."</td>";
  print "<td>".getpName($row1[prospect])."</td>";

  print "<td>$row1[nxtobj]</td>";
  print "<td>".datetohtml($row1[nxtobjdt])."</td>";
  print "<td><center>".$row1[platf]."</td>";
  print "<td>".$row1[discus]."</td>";
  print "<td>".$row1[atake]."</td>";
 print "<td>".$row1[rmrk]."</td></tr>";

print "</td></tr>";
 }
$dare=mysqli_query($db,"select * from dabill where brcode='$brcode' and user='$row[executive]' and trdate>='$df' and trdate<='$dt'");
if(mysqli_num_rows($dare)>0)
{
print " <tr><td colspan=21>";
print "\n<table border=1><th colspan=12 style='background-color:yellow'><font color='brown'>D.A bill</th><tr>";
print "<th>Date <th>Place <th>Total Amount <th>DA <th>Hotel <th>Other <br>Charges <th>G Tot <th>Remark";
while($darow=mysqli_fetch_array($dare))
{
print "\n<tr>\n<td>".datetohtml($darow[trdate])."</td><td>$darow[place]</td>";
print "<td align=right>$darow[totamt]</td><td align=right>$darow[da]</td>";
print "<td align=right>$darow[hotel]</td><td align=right>$darow[ocharge]</td>";
print "<td  align=right>$darow[gtot]</td><td>$darow[remark]</td>";
print "</tr>";
}
}
print "</table>";
}

 print "</table></fieldset><br>&nbsp;";
}
return;
}// end if
mysqli_query($db,"drop table $tbl");
?>
<p>&nbsp;<p>&nbsp;<p>&nbsp;
<form action='<?print $PHP_SELF?>'>
<table>
<tr><td bgcolor='steelblue'>
<img src='exec.jpg'>
</td><td>
<table border=0 width=250>
<th colspan=2 height=23><FONT FACE='ARIAL' color='white'>
EXECUTIVE WISE REPORT
</FONT>

<tr><td>From</td><td>
<input type=text name=d value='<?print date("d",time());?>' size=1>
<input type=text name=m value='<?print date("m",time());?>' size=1>
<input type=text name=y value='<?print date("Y",time());?>' size=3>
</td></tr>
<tr><td>To</td><td>
<input type=text name=d1 value='<?print date("d",time());?>' size=1>
<input type=text name=m1 value='<?print date("m",time());?>' size=1>
<input type=text name=y1 value='<?print date("Y",time());?>' size=3>
</td></tr>
<tr><td>Executive</td><td>
<select name='exec'>
<option>All</option>
<?
$sql="select distinct user from followup order by user";
$exres=mysqli_query($db,$sql);
while($exrow=mysqli_fetch_array($exres))
{
 print "<option>$exrow[0]</option>";
}
?>
</select></td></tr>
<tr><td>Password</td><td><input type=password name='pswd'></td></tr>

<tr><td colspan=2 align=center bgcolor='steelblue'><input type=submit value="Show" name='show'></td></tr>
</table>
</td></tr>
</table>
</form>
<?
function getpName($x)
{
 $gg=mysqli_query($db,"select * from prospmaster where pcode='$x'")or die(mysqli_error($db));
  $gg2=mysqli_fetch_array($gg);
  $var=$gg2[pname].'<td>'.$gg2[addr1].'</td></td><td>'.$gg2[city].'</td></td><td>'.$gg2[district].'</td><td>'.$gg2[ph1].',<br>'.$gg2[ph2].'</td><td>'.$gg2[mob].'</td>';
  return $var;
}

mysqli_query($db,"drop table $tbl");

?>
