<?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; 
           
        }
    #nav-toggle:checked ~ .split.bor
    {
        margin-left:20%;
        transition: 1s;
    }
       
    h5{
        text-align:center;
    }

   .container-table{
    margin-top:-10px;
    height:400px;
     overflow-y: scroll;
     overflow-x: hidden;
   }
   

</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>Employee Report</h5>

    <div class="container">
        <div class="row">
            <div class="col-md-12"><br>
                <form  action="" method = "POST" enctype="multipart/form-data">
                    
                        <div class="form-control" style="background-color:#ffefea;">
                            <label for="Project" class=""><b>Project :</b></label>
                            <?php
                            $_SESSION['pro'] = $proj;
                            $q = mysqli_query($con,"SELECT  Project FROM epb_tbl WHERE PROJECT = 'OTHER'");
                            while($q_row= mysqli_fetch_array($q)) {
                                     $project = $q_row['Project'];
                                                      }
                            ?>
                             <input  type="text" name="Project" value="<?php echo $project?>" style="text-align: center;font-weight: bold;" readonly >
                           
                                <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="" autocomplete="off">
                                <br> <label class="control-label"><b>Process :</b></label>
                                <input type="text" name="Process" value="" autocomplete="off">
                                <button class="btn btn-success btn-sm" type="submit" name="search" value="Search" style="border-radius:15px;" onclick="mytable()">Search</button>
                     </div>
                </form>
               
            </div>
        </div>
 
     
    <?php
  $proj = $_SESSION['proj']; 
  if(isset($_POST['search'])){
    $From_date     = $_POST['dateFrom'];
    $To_date       = $_POST['dateTo'];
    $EmpName       = $_POST['EmpName'];
    $Process       = $_POST['Process'];
    $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-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>
              </tr>
          </thead>";

                if($Project =="OTHER" && $EmpName == ""  && $Process== "")
                {
                  $up= "SELECT E.EMP as EMPNAME,E.PROJECT as PROJECT,E.CATEGORY as CATEGORY,E.PROCESS as PROCESS,LEFT(E.START_TIME,10) AS startTime,LEFT(E.END_TIME,10) 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((TIMESTAMPDIFF(SECOND, E.START_TIME, E.END_TIME)-MP.BENCHMARK)),9)) AS TIMEEXCEED FROM EMPDATA_TBL E left JOIN master_process_copy MP ON E.PROJECT= MP.PROJECT AND E.CATEGORY= MP.CATEGORY AND E.PROCESS= MP.PROCESS  WHERE left(START_TIME,10) between '$From_date' and '$To_date' ORDER BY START_TIME DESC " ;
                }
                elseif($Project =="OTHER" && $EmpName != "" && $Process == "")
                {
                    $up="SELECT E.EMP as EMPNAME,E.PROJECT as PROJECT,E.CATEGORY as CATEGORY,E.PROCESS as PROCESS,LEFT(E.START_TIME,10) AS startTime,LEFT(E.END_TIME,10) 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((TIMESTAMPDIFF(SECOND, E.START_TIME, E.END_TIME)-MP.BENCHMARK)),9)) AS TIMEEXCEED FROM EMPDATA_TBL E left JOIN master_process_copy MP ON E.PROJECT= MP.PROJECT AND E.CATEGORY= MP.CATEGORY  AND E.PROCESS= MP.PROCESS  WHERE left(START_TIME,10) between '$From_date' and '$To_date' and EMP = '$EmpName'  ORDER BY START_TIME DESC";
                }
                elseif($Project =="OTHER" && $Process != "" && $EmpName == "" )
                {
                    $up="SELECT E.EMP as EMPNAME,E.PROJECT as PROJECT,E.CATEGORY as CATEGORY,E.PROCESS as PROCESS,LEFT(E.START_TIME,10) AS startTime,LEFT(E.END_TIME,10) 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((TIMESTAMPDIFF(SECOND, E.START_TIME, E.END_TIME)-MP.BENCHMARK)),9)) AS TIMEEXCEED FROM EMPDATA_TBL E left JOIN master_process_copy MP ON E.PROJECT= MP.PROJECT AND E.CATEGORY= MP.CATEGORY AND E.PROCESS= MP.PROCESS  WHERE left(START_TIME,10) between '$From_date' and '$To_date' and E.PROCESS ='$Process'";
                }
                else
                {
                    $up="SELECT E.EMP as EMPNAME,E.PROJECT as PROJECT,E.CATEGORY as CATEGORY,E.PROCESS as PROCESS,LEFT(E.START_TIME,10) AS startTime,LEFT(E.END_TIME,10) 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((TIMESTAMPDIFF(SECOND, E.START_TIME, E.END_TIME)-MP.BENCHMARK)),9)) AS TIMEEXCEED FROM EMPDATA_TBL E left JOIN master_process_copy MP ON E.PROJECT= MP.PROJECT AND E.CATEGORY= MP.CATEGORY AND E.PROCESS= MP.PROCESS  WHERE left(START_TIME,10) between '$From_date' and '$To_date' and EMP = '$EmpName' and E.PROCESS ='$Process'";
                }

                    ini_set('max_execution_time', '300');
                    // set_time_limit(300);
                    $Query = mysqli_query($connect, $up);
                
                   $RES=mysqli_num_rows($Query);
                    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>';
                if($F_S == "YES")
                { $head.= '<td style="border:;background-color:#c3e6cb;color:green; ">'.$r['TIMEEXCEED']. '</td>';
                }
                else{ $head.= '<td style="background-color:#f5c6cb;font-weight:bold;color:red;">'.$r['TIMEEXCEED']. '</td>';
                }
                $head.= '</tr>';
 
            }
             
             echo $head;

            ?>
            
            </div> 
                <div class="form-group">
                    <div class="row">
                    <div class="col-md-6"><b>Selected Project :</b><b  style="color:#4a0904"> <?php echo $Project?></b>
                    </div>
                <div class="col-md-6">
                    <div class="input-group" >
                        <label for=""><b>Total Count :</b></label> &nbsp;
                        <input class="bg-danger text-light" type="text" name="" id="" value="<?php echo $RES;?>" style="width:70px;font-weight: bolder;border-radius:10px; text-align:center">
                    </div>
                </div>
                          
    </div>
    <?php } ?>
    </div>
</div>          
</div>
</body>
</html>
                