<?
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");
$s="select a.prospect,a.platf,a.date from followup a,inquiry b where a.date>='$df' and a.date<='$dt' and a.inqno=b.inqno and b.model='$exec' order by date";

$chre=mysqli_query($db,$s)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='viewplmodel.php?pl=$sumrow[0]&df=$df&dt=$dt&model=$exec' 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>";
*/

$sql="select distinct exec from inquiry where model='$exec' order by exec";


$re=mysqli_query($db,$sql)or die(mysqli_error($db));
while($row=mysqli_fetch_array($re))
{
 print "<fieldset><legend><b>$row[0]</b></legend>";
 $sq="select inqno from inquiry where model='$exec' and exec='$row[exec]'";
 $re1=mysqli_query($db,$sq);
 print "<table cellspacing=1 cellpadding=2 bgcolor='lightsteelblue' width=100%>";
 print "<th>No <th>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>Entry date";
 $i=1;
 while($inrow=mysqli_fetch_array($re1))
 {
  $s="select * from followup where inqno='$inrow[inqno]'  and executive='$row[exec]' and date>='$df' and date<='$dt' order by date";

 $re2=mysqli_query($db,$s);

 while($row1=mysqli_fetch_array($re2))
 {
  $mod=mysqli_query($db,"select model from inquiry where inqno='$row1[inqno]' and exec='$row[exec]'");
  $mod_row=mysqli_fetch_row($mod);
  print "<tr><td>".$i++."</td>";
  print "<td>".datetohtml($row1[date])."</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><center>".datetohtml(substr($row1['ondate'],0,10)).substr($row1['ondate'],10)
."</td>";

 }
}
 print "</table></fieldset><br>";

}
return;
}// end if
?>
<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'>
MODEL 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>Model</td><td>
<select name='exec'>
<option>All</option>
<?
$sql="select distinct model from inquiry order by model";
$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");

?>
