<?
session_start();
include("../opendb.php");
include("../test.php");
$brcode=$_SESSION['brcode'];
$user=$_SESSION['user'];
$level=$_SESSION['level'];

$d=$_REQUEST['d'];
$m=$_REQUEST['m'];
$y=$_REQUEST['y'];
$d1=$_REQUEST['d1'];
$m1=$_REQUEST['m1'];
$y1=$_REQUEST['y1'];
$pswd=$_REQUEST['pswd'];
?>
<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(isset($_POST['show']))
{
	if($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 prospect,platf,date from followup where date>='$df' and date<='$dt' and executive='$user' 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</th><tr><th>Platf <th>Count";
while($sumrow=mysqli_fetch_row($sumres))
{
 print "<tr><td><center>$sumrow[0]</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]*10).">";
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 user from followup where date>='$df' and date<='$dt' and user='$user' order by date";

//$sql="select distinct executive from followup where date>='$df' and date<='$dt' order by executive";

$re=mysqli_query($db,$sql);
while($row=mysqli_fetch_array($re))
{
 print "\n<fieldset><legend><b>$row[user]</b></legend>";
 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>Address <th>Phone <th>Mob<th>Dist<th>Nxt Obj<th>Nxt Visit Dt. <th>Platform <th>Discus <th>Action to take";
 $re2=mysqli_query($db,"select * from followup where user='$row[user]' and date>='$df' and date<='$dt' order by date");
 $i=1;
 while($row1=mysqli_fetch_array($re2))
 {
  $mod=mysqli_query($db,"select model from inquiry where inqno='$row1[inqno]' and user = '$row[user]' ");
  $mod_row=mysqli_fetch_row($mod);

  print "\n<tr><td>".$i++."</td>";
  print "<td>".datetohtml($row1[followdate])."</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 "\n</table></fieldset><br>";
}
return;
}
else
{
 print "<script>alert('Wrong Password');</script>";

}
}// end if

?>
<p>&nbsp;<p>&nbsp;<p>&nbsp;
<form method='post' action='<?print $PHP_SELF?>'>
<table>
<tr><td bgcolor='steelblue'>
<img src='exec.jpg'>
</td><td>
<table border=0 width=250>
<th colspan=2 height=35><FONT FACE='ARIAL' color='white'>
EXECUTIVE WISE REPORT
</FONT>

<table border=0 width=250 cellspacing=0 cellpadding=0>
<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>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[addr].'</td><td>'.$gg2[ph1].',<br>'.$gg2[ph2].'</td><td>'.$gg2[mob].'</td><td>'.$gg2[district].'</td>';
  return $var;
}

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

?>
