<?php
 session_start();
 error_reporting(E_ERROR | E_PARSE);
 error_reporting(0);
//  mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Emp_Prod_Benchmark</title>
    <link rel="shortcut icon" href="images/Files_Download.png" type="image/x-icon"/>
</head>
<style>
    .split.bor{
            margin-left:5%;
            transition: 1s; 
             /* margin-right:;  */
            /* margin-top:-15px; */ 
        }
    #nav-toggle:checked ~ .split.bor
    {
        margin-left:20%;
        transition: 1s;
        /* margin-right:15%; */
        
    }
       
    h5{
        text-align:center;
    }
   .container-table{
    margin-top:-10px;
    height:400px;
       overflow-y: scroll;
   }
   .container-table::-webkit-scrollbar {
    display: none;
   }

</style>
<body>
    <?php
    include "Nav.php";
    include "Connection.php"; 
    ?>

<?php 
    date_default_timezone_set('Asia/Kolkata');
    $date =  date('Y-m-d h:i:s');
?>
<br><br><br>
<div class="container split bor">
<h5>View Status</h5>
    <div class="container">
        <div class="row">
            <div class="col-md-12"><br>
                <form  action="" method = "POST" enctype="multipart/form-data">
                    <?php  
                        //$proj = $_SESSION['pro']; 
                    ?>
                        <div class="form-control">

                            <label for="Project" class=""><b>Select Project :</b></label>
                            <select id="Project" name="Project" class="" placeholder="Select-Project" value="<?php ?>">
                            <option value="" ></option>
                            <?php
                            $_SESSION['pro'] = $proj;
                            $q = mysqli_query($con,"SELECT distinct Project FROM epb_tbl");
                            while($q_row= mysqli_fetch_array($q)) {
                            ?>
                            <option>
                            <?php echo $project = $q_row['Project'];?></option>
                            <?php
                            
                            }
                            ?>
                            </select>
                                <label class=" control-label"><b>From:</b></label>
                                <input type="date" name="dateFrom" value="<?php echo date('Y-m-d');?>" required >
                                <label class="control-label"><b>To:</b></label>
                                <input type="date" name="dateTo" value="<?php echo date('Y-m-d');?>" required>
                                <label class="control-label"><b>Emp Name :</b></label>
                                <input type="text" name="EmpName" value="">
                                <button class="btn btn-success btn-sm" type="submit" name="search" value="Search" style="border-radius:15px;" onclick="mytable()">Search</button>
                     </div>
                </form>

                
                <!-- <form method='POST' action='Export_report.php'>      
<input type='submit' value='Export File ' name='Export' class="btn btn-success">
</form>  -->
               
            </div>
        </div>
    </div>
     
    <?php
  $proj = $_SESSION['proj']; 
  if(isset($_POST['search'])){
    $From_date     = $_POST['dateFrom'];
    $To_date       = $_POST['dateTo'];
    $EmpName       = $_POST['EmpName'];
    $Project       = $_POST['Project'];
    $up = "select * from epb_tbl where Project='$Project'" ;
    $Query = mysqli_query($con, $up);
    if(!$Query){
             echo("Failed");
      }else{
        while($r = mysqli_fetch_array($Query)){ 
            $D_B = $r['Database'];
            $S_I = $r['Server_ip'];
            $U_N = $r['Username'];
            $P_W = $r['Password'];
          //echo("Success");       
        } 
    }
  }
?>

<?php
$server= $S_I;
$user= $U_N;
$password= $P_W;
$db= $D_B ;
$connect=mysqli_connect($server,$user,$password,$db);

if($connect->connect_error){
die("Connection Failed");
}
else{
// echo $EmpName;
}           
 ?><br>
 <div class="container">
<h6>Selected Project : <?php echo $Project?></h6>
 </div>
<br>
<div class="container">
<div class="container-table" id  ="table" >

 <?php
          
          $head = "<table class='table table-bordered table-sm'>
           <thead style='border:1px solid black'>
                  <tr style='border:1px solid black'>
                  <th style='border:;background-color: #'>Emp Name</th>
                  <th style='border:;background-color: #'>Process</th>
                  <th style='border:;background-color: #'>StartTime</th>
                  <th style='border:;background-color: #'>EndTime</th>
                  <th style='border:;background-color: #'>BenchMark</th>
                  <th style='border:;background-color: #'>TimeTaken</th>
                  <th style='border:;background-color: #'>TimeExceed</th>
                  <th style='border:;background-color: #'>Status</th>
                  
              </tr>
          </thead>";
 if($Project =="OTHER" && $EmpName == "")
{

     $up= "SELECT E.EMP as EMPNAME,E.PROJECT as PROJECT,E.CATEGORY as CATEGORY,E.STAGE as STAGE,E.PROCESS as SUBPROCESS,E.SUBPROCESS as PROCESS,RIGHT(E.START_TIME,8) AS startTime,RIGHT(E.END_TIME,8) AS EndTime,LEFT(SEC_TO_TIME(TIMESTAMPDIFF(SECOND,E.START_TIME,E.END_TIME)),8)AS TDif,LEFT(SEC_TO_TIME(MP.BENCHMARK),8) AS BTD ,IF(MP.BENCHMARK < TIMESTAMPDIFF(SECOND, E.START_TIME, E.END_TIME), 'NO', 'YES') AS FinalStatus,
     IF(MP.BENCHMARK < TIMESTAMPDIFF(SECOND, E.START_TIME, E.END_TIME), LEFT(SEC_TO_TIME((TIMESTAMPDIFF(SECOND, E.START_TIME, E.END_TIME)-MP.BENCHMARK)),8), LEFT(SEC_TO_TIME('0'),8)) AS TIMEEXCEED FROM EMPDATA_TBL E left JOIN master_process MP ON E.PROJECT= MP.PROJECT AND E.CATEGORY= MP.CATEGORY AND E.STAGE = MP.STAGE AND E.PROCESS= MP.PROCESS AND E.SUBPROCESS= MP.SUBPROCESS WHERE left(START_TIME,10) between '$From_date' and '$To_date'";

    // $up="SELECT * from empdata_tbl Where left(START_TIME,10) between '$From_date' and '$To_date'"; 
  
}
elseif($Project =="OTHER" && $EmpName != "")
{
    $up="SELECT E.EMP as EMPNAME,E.PROJECT as PROJECT,E.CATEGORY as CATEGORY,E.STAGE as STAGE,E.PROCESS as SUBPROCESS,E.SUBPROCESS as PROCESS,RIGHT(E.START_TIME,8) AS startTime,RIGHT(E.END_TIME,8) AS EndTime,LEFT(SEC_TO_TIME(TIMESTAMPDIFF(SECOND,E.START_TIME,E.END_TIME)),8)AS TDif,LEFT(SEC_TO_TIME(MP.BENCHMARK),8) AS BTD ,IF(MP.BENCHMARK < TIMESTAMPDIFF(SECOND, E.START_TIME, E.END_TIME), 'NO', 'YES') AS FinalStatus,
     IF(MP.BENCHMARK < TIMESTAMPDIFF(SECOND, E.START_TIME, E.END_TIME), LEFT(SEC_TO_TIME((TIMESTAMPDIFF(SECOND, E.START_TIME, E.END_TIME)-MP.BENCHMARK)),8), LEFT(SEC_TO_TIME('0'),8)) AS TIMEEXCEED FROM EMPDATA_TBL E left JOIN master_process MP ON E.PROJECT= MP.PROJECT AND E.CATEGORY= MP.CATEGORY AND E.STAGE = MP.STAGE AND E.PROCESS= MP.PROCESS AND E.SUBPROCESS= MP.SUBPROCESS WHERE left(START_TIME,10) between '$From_date' and '$To_date' and EMP = '$EmpName'";
}
 elseif($EmpName == "")
{         
    $up ="SELECT L.EMPNO,EM.EMPNAME,B.IMAGENAME as IMAGENAME,L.PROCESS as PROCESS ,L.PASS as PASS, L.startTime as startTime, L.EndTime EndTime, 
            LEFT(SEC_TO_TIME(TIMESTAMPDIFF(SECOND, L.startTime, L.EndTime)),8) as TDif,LEFT(SEC_TO_TIME(BM.BTD),8) as BTD , IF(BM.BTD < TIMESTAMPDIFF(SECOND, L.startTime, L.EndTime), 'NO', 'YES') AS FinalStatus ,IF(BM.BTD < TIMESTAMPDIFF(SECOND,L.startTime, L.EndTime), LEFT(SEC_TO_TIME((TIMESTAMPDIFF(SECOND, L.startTime, L.EndTime)-BM.BTD)),8), LEFT(SEC_TO_TIME('0'),8)) AS TIMEEXCEED from LOGIN_FINAL  L left JOIN BATCHES B ON B.BATCHNO = L.BATCHNO left JOIN MASTER_BENCHMARK BM ON BM.PROCESS = CONCAT(L.PROCESS,L.PASS) left JOIN empmaster EM ON EM.EMPNO = L.EMPNO Where left(starttime,10) between '$From_date' and '$To_date' ORDER BY L.startTime ,L.EndTime";
} 
else{
    $up ="SELECT L.EMPNO,EM.EMPNAME,B.IMAGENAME as IMAGENAME,L.PROCESS as PROCESS ,L.PASS as PASS, L.startTime as startTime, L.EndTime EndTime, 
          LEFT(SEC_TO_TIME(TIMESTAMPDIFF(SECOND, L.startTime, L.EndTime)),8) as TDif,LEFT(SEC_TO_TIME(BM.BTD),8) as BTD , IF(BM.BTD < TIMESTAMPDIFF(SECOND, L.startTime, L.EndTime), 'NO', 'YES') AS FinalStatus ,IF(BM.BTD < TIMESTAMPDIFF(SECOND,L.startTime, L.EndTime), LEFT(SEC_TO_TIME((TIMESTAMPDIFF(SECOND, L.startTime, L.EndTime)-BM.BTD)),8), LEFT(SEC_TO_TIME('0'),8)) AS TIMEEXCEED from LOGIN_FINAL  L left JOIN BATCHES B ON B.BATCHNO = L.BATCHNO left JOIN MASTER_BENCHMARK BM ON BM.PROCESS = CONCAT(L.PROCESS,L.PASS) left JOIN empmaster EM ON EM.EMPNO = L.EMPNO Where left(starttime,10) between '$From_date' and '$To_date' AND L.EMPNO = '$EmpName' ORDER BY L.startTime ,L.EndTime";  
  }

 
            $Query = mysqli_query($connect, $up);
            while ($r = mysqli_fetch_array($Query)){
                $F_S =  $r['FinalStatus'];
                
                $head.= 
                '<tr style="border:">
                <td style="border:;background-color:">'.$r['EMPNAME'] .'</td>
                <td style="border:;background-color: #">'.$r['PROCESS'].'</td>
                <td style="border:;background-color: #">'.$r['startTime'] .'</td>
                <td style="border:;background-color: #">'.$r['EndTime'].'</td>
                <td style="border:;background-color: #">'.$r['BTD'].' </td>
                <td style="border:;background-color: #">'.$r['TDif'].'</td>
                <td style="border:;background-color: #">'.$r['TIMEEXCEED']. '</td> ';
                if($F_S == "YES")
                { $head.= '<td style="border:;background-color:#98eba5 ">'.$F_S. '</td>';}

                else{ $head.= '<td style="border:;background-color:#f06156">'.$F_S. '</td>';}
                
                $head.= '</tr>';
            ?>
           
            <?php
            }
             echo $head;
        ?>
            
            </div>
</div> 
          
</div>


</div>
</body>
</html>
