<?php
// ini_set('max_execution_time','3');
session_start();

include('config.php');
 $return = '';
 $name = $_SESSION['name'];
if(isset($_POST["query"]))
{
	$search = mysqli_real_escape_string($conn3, $_POST["query"]);

    $query="SELECT L.assignedto,EM.username as username,B.applicationnumber as IMAGENAME,L.assignedto as EMPNAME,L.processid as PROCESS  , RIGHT(L.startdate,8) as startdate, RIGHT(L.completeddate,8) AS EndTime,LEFT(SEC_TO_TIME(TIMESTAMPDIFF(SECOND, L.startdate, L.completeddate)),8) as TDif,LEFT(SEC_TO_TIME(BM.BTD),8) as BTD , IF(BM.BTD < TIMESTAMPDIFF(SECOND, L.startdate, L.completeddate), 'NO', 'YES') AS FinalStatus ,IF(BM.BTD < TIMESTAMPDIFF(SECOND,L.startdate, L.completeddate), LEFT(SEC_TO_TIME((TIMESTAMPDIFF(SECOND, L.startdate, L.completeddate)-BM.BTD)),8), LEFT(SEC_TO_TIME((TIMESTAMPDIFF(SECOND, L.startdate, L.completeddate)-BM.BTD)),9)) AS TIMEEXCEED from application_process  L 
    left JOIN Application B ON B.applicationnumber = L.applicationnumber 
    left JOIN MASTER_BENCHMARK BM ON BM.PROCESS = L.processid 
    left JOIN user EM ON EM.userid = L.assignedto 
    Where left(startdate,10)=CURDATE() AND L.assignedto = '$name' AND  L.processid  LIKE '%".$search."%' ORDER BY B.applicationnumber,L.startdate ASC";
}
else
{
$query="SELECT L.assignedto,EM.username as username,B.applicationnumber as IMAGENAME,L.assignedto as EMPNAME,L.processid as PROCESS  , RIGHT(L.startdate,8) as startdate, RIGHT(L.completeddate,8) AS EndTime,LEFT(SEC_TO_TIME(TIMESTAMPDIFF(SECOND, L.startdate, L.completeddate)),8) as TDif,LEFT(SEC_TO_TIME(BM.BTD),8) as BTD , IF(BM.BTD < TIMESTAMPDIFF(SECOND, L.startdate, L.completeddate), 'NO', 'YES') AS FinalStatus ,IF(BM.BTD < TIMESTAMPDIFF(SECOND,L.startdate, L.completeddate), LEFT(SEC_TO_TIME((TIMESTAMPDIFF(SECOND, L.startdate, L.completeddate)-BM.BTD)),8), LEFT(SEC_TO_TIME((TIMESTAMPDIFF(SECOND, L.startdate, L.completeddate)-BM.BTD)),9)) AS TIMEEXCEED from application_process  L 
left JOIN Application B ON B.applicationnumber = L.applicationnumber 
left JOIN MASTER_BENCHMARK BM ON BM.PROCESS = L.processid 
left JOIN user EM ON EM.userid = L.assignedto 
Where left(startdate,10)=CURDATE() AND L.assignedto = '$name' ORDER BY B.applicationnumber,L.startdate desc";
}
$result = mysqli_query($conn3, $query)  or die(mysqli_error($conn3));
$RES=mysqli_num_rows($result);
// echo $RES;

if($RES  >0)
{ 
    
	
  	$return .='
	<table  style="">
	<tr style="">
	<th style="padding:8px">Emp Name</th>
	<th style="padding:8px">Imagename</th>
	<th style="padding:8px">Process</th>
	<th style="padding:8px">StartTime</th>
	<th style="padding:8px">EndTime</th>
	<th style="padding:8px">Benchmark</th>
	<th style="padding:8px">TimeTaken</th>
	<th style="padding:8px">TimeExceed</th>
	</tr>';
	while($row1 = mysqli_fetch_array($result))
	{
		$F_S =  $row1['FinalStatus'];
		if($F_S == "YES")
		{
		$return .= '
		<tr">
		<td  style="">'.$row1["username"].' </td> 
		<td> '.$row1["IMAGENAME"].'</td> 
		<td> '.$row1["PROCESS"].'</td> 
		<td> '.$row1["startdate"].'</td> 
		<td> '.$row1["EndTime"].' </td> 
		<td> '.$row1["BTD"].' </td>
		<td> '.$row1["TDif"].'</td> 
	
		<td style="background-color:#c3e6cb ">'.$row1['TIMEEXCEED']. '</td>';
 
		}
		else{ $return.= '
			<td  style="">'.$row1["username"].' </td> 
		<td style="font-weight: bolder;color:#db1709"> '.$row1["IMAGENAME"].'</td> 
		<td style="font-weight: bolder;color:#db1709"> '.$row1["PROCESS"].'</td> 
		<td> '.$row1["startdate"].' </td> 
		<td>'.$row1["EndTime"].' </td> 
		<td> '.$row1["BTD"].' </td>
		<td  >'.$row1["TDif"].' </td>
        <td style="background-color:#f5c6cb;font-weight:bold;color:#8f1b1b;";>'.$row1['TIMEEXCEED']. '</td>';}
		$return.= '</tr> </table>' ;

	}
    
	 echo $return;
	}
else
{
	// echo 'No results containing all your search terms were found....';
	echo 'No data found....';
}
?>