<?php
include "include/db.php";
include "include/adminauth.php";
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Xls;

function clean_input_data($cellVal){	
    include "include/db.php";
	$cellVal = trim($cellVal);
	$cellVal = mysqli_real_escape_string($conn, $cellVal);
	$cellVal = strtolower($cellVal);
    $cellVal = str_replace(' ', '', $cellVal);
    $cellVal = str_replace('*', '', $cellVal);
	return $cellVal;
}

function clean_cell_input_data($cellVal1){	
    include "include/db.php";
	$cellVal1 = trim($cellVal1);
	$cellVal1 = mysqli_real_escape_string($conn, $cellVal1);
	return $cellVal1;
}
    $successmessage = '';
    $message = '';
if(isset($_REQUEST['mess'])){
    $message = $_REQUEST['mess'];
}
if($conn){  
    if(isset($_REQUEST['upload_xl_employee'])){ 
        $uniqueId= time().'-'.mt_rand();	
        $temp = explode(".", $_FILES["fileToUpload1"]["name"]);
        $pic_name =  $uniqueId . '.' . end($temp);
        $target_dir = getcwd().DIRECTORY_SEPARATOR. "reports/";
        $target_file = $target_dir . $pic_name;
        $imageFileType = strtolower(pathinfo($target_file,PATHINFO_EXTENSION));
        $time = date("Y-m-d H:i:s");
        
        if($_FILES["fileToUpload1"]["size"] > 2000000){
            $message = $message . 'The file you uploaded is too large. File size should be less than 2 MB';
        }else{
            // Allow certain file formats
            if($imageFileType != "xlsx" && $imageFileType != "xls") {
                $pic_name = '';
                $message = $message . 'The file you uploaded is not of xlsx/xls format. Please upload a XLSX/XLS file.';
            }else{
                if(move_uploaded_file($_FILES["fileToUpload1"]["tmp_name"], $target_file)) { 
                    if($imageFileType == "xlsx"){
                        $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
                    }elseif($imageFileType == "xls"){
                        $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
                    }
                    if($reader) {
                    $reader->setReadDataOnly(true);
                    $spreadsheet = $reader->load($target_file);  
                    $sheetData = $spreadsheet->getActiveSheet()->toArray();
                    $count = 0;
                    $insert_row = 0;                  
                    foreach($sheetData as $row) {
                        if($count == 0){	
                            $setVal = 0;
                            $set = 0;
                            for($i=0; $i<1000; $i++){
                                if(!isset($row[$i])){
                                    $row[$i] = " ";
                                }
                                $cellVal = clean_input_data($row[$i]);                            
                                if($cellVal == 'firstname'){
                                    $firstnameRow = $i;
                                    $set++;
                                }
                                if($cellVal == 'lastname'){
    
                                    $lastnameRow = $i;
                                    $set++;
                                }
                                if($cellVal == 'email'){
    
                                    $emailRow = $i;
                                    $set++;
                                }                                
                                if($set == 3){
                                    $setVal = 1;
                                }    
                                if($setVal == 1){
                                    break;
                                }
                            }
                        }
    
                        if($setVal != 1){                        
                            header("Location:employee.php?mess=Invalid Excel Format");
                            exit;
                        }
                       
                        if(!isset($firstnameRow) || !isset($lastnameRow) || !isset($emailRow)){                        
                            header("Location:employee.php?mess=Invalid excel Format");
                            exit;
                        }
                        if($count > 0){                        
                            $first_name = isset($row[$firstnameRow]) ? clean_cell_input_data($row[$firstnameRow]) : "";
    
                            $last_name = isset($row[$lastnameRow]) ? clean_cell_input_data($row[$lastnameRow]) : "";
                            
                            $email = isset($row[$emailRow]) ? clean_cell_input_data($row[$emailRow]) : "";     
    
                            if(strlen($first_name) > 0 || strlen($email) > 0){
                                $query0 = "SELECT * FROM employee WHERE employee_email ='$email'";
                                $run0 = mysqli_query($conn, $query0);
                                if(mysqli_num_rows($run0) >0){
                                    $message = $message." $email : A user with that Email already exists.<br>";
                                }else{      
                                    $query0 = "SELECT * FROM admin WHERE admin_email ='$email'";
                                    $run0 = mysqli_query($conn, $query0);
                                    if(mysqli_num_rows($run0)>0){
                                        $message = $message." $email : That email is disabled.<br>";
                                    }else{ 
                                        $query1 = "INSERT INTO employee SET employee_fname ='$first_name', employee_lname = '$last_name', employee_phone ='',employee_email = '$email', password_hash = '' , active_time ='$time', status ='deactive'";
                                    
                                        $run1 = mysqli_query($conn, $query1);
                                        if(mysqli_affected_rows($conn)== 1){
                                            $insert_row++;    
                                        }               
                                    } 
                                } 
                            } 
                        }
                        $count++;    
                    }
                        if($insert_row > 0){
                            $successmessage = "$insert_row Employees added.";
                        }else{
                            $message = $message . "$insert_row Employees added.";
                        }
                        
                    }
                                   
                    }else{
                        $message = $message . "The file could not be uploaded due to technical reasons. Try again.";
                    }			
                
            }	
        } 
        
    }
    if(isset($_REQUEST['pass'])){
        $passID = $_REQUEST['pass'];
        $pass_email = '';

        $query00 = "SELECT employee_email FROM employee WHERE employee_id = $passID";
        $run00 = mysqli_query($conn,$query00);
        while($row00 = mysqli_fetch_array($run00)){
            $pass_email = $row00['employee_email'];
        }

    }
    if(isset($_REQUEST['insert']) && isset($_REQUEST['employee_fname']) && isset($_REQUEST['employee_lname'])&& isset($_REQUEST['employee_email'])){
    if(strlen($_REQUEST['employee_email']) > 4 && strlen($_REQUEST['employee_fname']) > 2){
        $employee_fname = $_REQUEST['employee_fname'];
        $employee_email = $_REQUEST['employee_email'];
        $employee_lname = $_REQUEST['employee_lname'];
        $password = $_REQUEST['password'];
        $con_password =   $_REQUEST['con_password'];
        if(strlen($password) < 8){
           $message = 'The pasword is too short. Set a password longer than 6 characters.';
        }else{
        if($password !== $con_password){
            $message = "The passwords don't Match Enter Again";
        }else{
        $query0 = "SELECT * FROM employee WHERE employee_email ='$employee_email'";
        $run0 = mysqli_query($conn, $query0);
        if(mysqli_num_rows($run0) >0){
            $message = "A user with that Email already exists. Use another Email ID.";
        }else{      
                $query0 = "SELECT * FROM admin WHERE admin_email ='$employee_email'";
                $run0 = mysqli_query($conn, $query0);
                if(mysqli_num_rows($run0)>0){
                    $message = "That email is disabled.";
                }else{
                    $emp_pass_hash = password_hash($password,PASSWORD_DEFAULT);
                    $time = date("Y-m-d H:i:s");
                    if(strlen($emp_pass_hash) > 0){                                           
                        $query1 = "INSERT INTO employee SET employee_id =NULL,employee_fname ='$employee_fname', employee_lname = '$employee_lname', employee_phone ='',employee_email = '$employee_email', password_hash = '$emp_pass_hash' , active_time ='$time', status ='active'";
                    
                        $run1 = mysqli_query($conn, $query1);
                        if(mysqli_affected_rows($conn)== 1){
                            $successmessage = "Employee Created";
                        }else{
                            $message = "Failed.";
                        }  
                    } else{
                        $message = "Failed.";
                    }                  
                }                    

            
        }
        } 
        }  
        
        
    } else{
        $message = "First name or Email invalid.";
    } 
    }
    if(isset($_REQUEST['repass']) && isset($_REQUEST['password']) && isset($_REQUEST['con_password'])){       
        $passID =$_REQUEST['repass'];
        $pass_email = '';
        $query00 = "SELECT employee_email FROM employee WHERE employee_id = $passID";
        $run00 = mysqli_query($conn,$query00);
        while($row00 = mysqli_fetch_array($run00)){
            $pass_email = $row00['employee_email'];
        }
        $password = $_REQUEST['password'];
        $con_password = $_REQUEST['con_password'];
        if(strlen($password) < 8){
           $message = "The pasword is too short. Set a password longer than 6 characters for $pass_email.";
        }else{
            if($password !== $con_password){
                $message = "The passwords don't Match Enter Again for $pass_email";
            }else{
             
            $emp_pass_hash = password_hash($password,PASSWORD_DEFAULT);
            if(strlen($emp_pass_hash) > 0){    
                $time = date("Y-m-d H:i:s");
                $query1 = "UPDATE employee SET password_hash = '$emp_pass_hash' , active_time ='$time' WHERE employee_id= $passID";

                
                $run1 = mysqli_query($conn, $query1);
                if(mysqli_affected_rows($conn)==1){
                    $successmessage = "Employee Password Changed for $pass_email";
                }else{
                    $message = "Employee Password Could Not be changed for $pass_email";
                }
            }else{
                $message = "Employee Password Could Not be changed for $pass_email";
            }    

        }
     }

    }
    if(isset($_REQUEST['spe'])){

        $id2 = $_REQUEST['spe'];

        $query2 = "SELECT * FROM employee WHERE employee_id = '$id2'";
        $run2 = mysqli_query($conn, $query2);

        if($run2){
            while($row2 = mysqli_fetch_array($run2)){
                $speID2 = $row2['employee_id'];
                $employee_fname = $row2['employee_fname'];
/*                 $employee_phone = $row2['employee_phone'];
 */            $employee_email = $row2['employee_email'];
            $employee_lname = $row2['employee_lname'];
            }
        }else{
            //$message = "";
        }


    }

    if(isset($_REQUEST['edit']) && isset($_REQUEST['employee_fname'])&& isset($_REQUEST['employee_lname'])&& isset($_REQUEST['employee_email'])){
        if(strlen($_REQUEST['edit']) > 0 && strlen($_REQUEST['employee_email']) > 4 && strlen($_REQUEST['employee_fname']) > 2){
            $id2 = $_REQUEST['edit'];
            $fname = $_REQUEST['employee_fname'];
            $lname = $_REQUEST['employee_lname'];

            $employee_email = $_REQUEST['employee_email'];
            
            $query1 = "UPDATE employee SET employee_fname = '$fname',employee_lname ='$lname',employee_email ='$employee_email' WHERE employee_id = $id2";
            $run1 = mysqli_query($conn, $query1);
            if($run1){
                $successmessage = "Employee Edited";
            }else{
                //$message = "";
            }
        } else{
            $message = "First name or Email invalid.";
        } 
    }

    if(isset($_REQUEST['delete'])){
        $id3 = $_REQUEST['delete'];
        $e_name = $_REQUEST['ename'];
        $time = date("Y-m-d H:i:s");
        $query3 = "UPDATE employee SET status = 'delete', deact_time = '$time' WHERE employee_id = '$id3'";
        $run2 = mysqli_query($conn, $query3);

        if(mysqli_affected_rows($conn)==1){
            $successmessage = "Employee $e_name Deleted Permanently from system.";
        }
    }
    if(isset($_REQUEST['deactive'])){
        $id3 = $_REQUEST['deactive'];
        $time = date("Y-m-d H:i:s");
        $query3 = "UPDATE employee SET status = 'disabled', deact_time = '$time' WHERE employee_id = '$id3'";
        $run2 = mysqli_query($conn, $query3);

        if(mysqli_affected_rows($conn)==1){
            $successmessage = "Employee Deactivated.";
        }
    }
    if(isset($_REQUEST['activate'])){
        $id3 = $_REQUEST['activate'];
        $time = date("Y-m-d H:i:s");
        $check_emp_query = "SELECT * FROM employee WHERE employee_id = $id3 AND status = 'disabled'";
        $check_emp_run = mysqli_query($conn, $check_emp_query);
        if(mysqli_num_rows($check_emp_run) == 1){
            $check_emp_row = mysqli_fetch_array($check_emp_run);
            $password_h = $check_emp_row['password_hash'];
            if(strlen($password_h) > 10){
                $query3 = "UPDATE employee SET status = 'active', active_time = '$time' WHERE employee_id = '$id3'";
                $run2 = mysqli_query($conn, $query3);

                if(mysqli_affected_rows($conn)==1){
                    $successmessage = "Employee Activated.";
                }
            }            
        }   
    }
}
include "header.php";
?>
<div id="page-wrapper">
<?php
if(!isset($_REQUEST['spe']) && !isset($_REQUEST['pass']) && !isset($_REQUEST['import'])){
?>
<a style = 'float:right;' class='btn btn-success' href='employee.php?import='>Import Employee Excel</a>
<?php
}
if(isset($_REQUEST['import'])){?>
<a href="excels/employee.xlsx" download class="btn btn-danger" style = 'float:right;'>
Employee Excel Format
</a>  
<?php
}
?>
<h3>Manage Employees</h3>

<br>   
<?php 
                    
                    
                    if(isset($successmessage)){
                        if(strlen($successmessage) > 0){   
                            echo '<div class="alert alert-success">
                            <strong>'. $successmessage .'</strong></div>';
                        } 
                    }

                   if(isset($message)){
                        if(strlen($message) > 0){
                            echo '<div class="alert alert-danger">
                                <strong>'. $message .'</strong>
                            </div>';
                        }
                   }
                    ?>              
    <div class="row">
        <div class="col-lg-12">
            <div class="panel panel-default">
                <div class="panel-heading">
                <!-- <div class="col-lg-12"> -->
                <h3>
        <?php if(isset($_REQUEST['spe'])){ echo "Edit Employee Details<a style = 'float:right;' class='btn btn-warning' href='employee.php'>Cancel</a>";}else if(isset($_REQUEST['pass'])|| isset($_REQUEST['repass'])){
              echo "Change Password for $pass_email <a style = 'float:right;' class='btn btn-warning' href='employee.php'>Cancel</a>";
        }else if(isset($_REQUEST['import'])){
            echo "Import Employee Excel <a style = 'float:right;' class='btn btn-warning' href='employee.php'>Cancel</a>";
      }else{
            echo "Add Employee";
        } ?> </h3>
        </div>
              <!--   </div> -->
                                                                  

             <div class="panel-body">
                      
             <div class = "row">                   
      
                  
<div class="col-lg-12">

               <?php 
               if(isset($_REQUEST['spe'])){
                echo "<form action = 'employee.php?edit=$speID2' method = 'POST' class = 'form-group' enctype = 'multipart/form-data'>";
               }else if(isset($_REQUEST['pass']) || isset($_REQUEST['repass'])){
                echo "<form action = 'employee.php?repass=$passID' method = 'POST' class = 'form-group' enctype = 'multipart/form-data'>";
               }else if(isset($_REQUEST['import'])){
                echo "<form action = 'set-up-employee.php?upload_xl_employee=&import=' method = 'POST' class = 'form-group' enctype = 'multipart/form-data'>";
               }else{
                echo '<form action = "employee.php?insert=" method = "POST" class = "form-group" enctype = "multipart/form-data">';
               }
               ?>

<?php

  if(isset($_REQUEST['pass']) || isset($_REQUEST['repass'])){

  }   else if(isset($_REQUEST['import'])){ ?>
<div class="row">
<div class="form-group col-md-6">                  
				    <input type="file" class="upload-file" required name="fileToUpload1" id="fileToUpload1">   
                </div>
                <div class="form-group col-md-6">
                   
               </div>
</div>

<?php
}else{
    echo '<div class="row">

    <div class="form-group col-lg-6">
            <label for="employee_fname">First Name</label>';

}
?>                

        <?php if(isset($_REQUEST['spe'])){
        echo "<input type='text' class='form-control' name='employee_fname' id='employee_fname' value='$employee_fname' required='required' autofocus>";

    }else if(isset($_REQUEST['pass']) || isset($_REQUEST['repass'])){

    }else if(isset($_REQUEST['import'])){
              
    }  
    else{
        echo '<input type="text" class="form-control" name="employee_fname" id="employee_fname" required="required" autofocus>';
    }   
    
    ?> 
    <?php
   if(isset($_REQUEST['pass']) || isset($_REQUEST['repass'])){

  }else if(isset($_REQUEST['import'])){
              
} else{echo '
</div> 
<div class = "form-group col-lg-6">
<label for="employee_lname">Last Name</label>';
  }  ?> 

     
        <?php if(isset($_REQUEST['spe'])){
        echo "<input type='text' class='form-control' name='employee_lname' id='employee_lname' value='$employee_lname' required='required'>";

    }else if(isset($_REQUEST['import'])){
              
    }else if(isset($_REQUEST['pass']) || isset($_REQUEST['repass'])){

    }else{
        echo '<input type="text" class="form-control" name="employee_lname" id="employee_lname" required="required">';
    }   
    
    ?> 

    <?php
   if(isset($_REQUEST['pass']) || isset($_REQUEST['repass'])){

  }else if(isset($_REQUEST['import'])){
              
}   else{
      echo '</div>
      </div>
      <div class="row">
      
       
      <div class = "form-group col-lg-6">
      <label for="employee_email">Email</label>';

  }  ?>

        <?php if(isset($_REQUEST['spe'])){
        echo "<input type='email' class='form-control' name='employee_email' id='employee_email' value='$employee_email' required='required'>";

    }else if(isset($_REQUEST['pass']) || isset($_REQUEST['repass'])){

    }else if(isset($_REQUEST['import'])){
              
    }else{
        echo '<input type="email" class="form-control" name="employee_email" id="employee_email" required="required">';
    }   
    
    ?> 

    


<?php
   if(isset($_REQUEST['pass']) || isset($_REQUEST['repass'])){

  }else if(isset($_REQUEST['import'])){
              
}   else{
      echo '</div><div class="form-group col-lg-6">
			
      </div>
 </div>';

  }  ?> 


        <?php if(isset($_REQUEST['spe'])){
       

    }else if(isset($_REQUEST['import'])){
              
    }else{
        echo '<div class="row">

 
        <div class = "form-group col-lg-6">
        <label for="password">Password</label>';
        echo '<input type="password" class="form-control" name="password" id="password" required="required">';

        echo '</div>
        <div class="form-group col-lg-6">';
    }   
    
    ?> 	 

        <?php if(isset($_REQUEST['spe'])){         
        
       

    }else if(isset($_REQUEST['import'])){
              
    }else{
        echo '<label for="con_password">Confirm Password</label>
        ';
        echo '<input type="password" class="form-control" name="con_password" id="con_password" required="required">';
        echo '</div>
        </div>';
    }   
    
    ?> 		
     
<?php
if(!isset($_REQUEST['import'])){
    ?>
 <br>
<?php
}?>
    
 <div class = "row">
     
<div class = "form-group col-lg-6">
<input type="submit" name = "submit" value = "Submit" class="btn btn-success">
</div>


<div class = "form-group col-lg-6">
</div>
</div>
    

 
</form>
    
<?php
if(!isset($_REQUEST['import'])){
    ?>
 <br>
<?php
}?>
</div>
</div>
                </div><!-- panel-body  -->
            </div> <!-- panel panel-default -->
       </div> <!--  col lg 12-->
   </div> <!-- row -->
                            
     <div class="row">
        <div class="col-lg-12">
            <div class="panel panel-default">
            <div class="panel-heading">
            <?php if(isset($_REQUEST['view_deactivate'])) { ?>
<h4>Deactivated Employees List</h4>
     <?php }elseif(!isset($_REQUEST['view_deactivate'])) { ?>
        <h4>Active Employees List</h4>
        <?php } ?>
        </div> 
            <div class="panel-body">
            <?php if(isset($_REQUEST['view_deactivate'])) { ?>
                            <table width="100%" class="table table-striped table-bordered table-hover" id="dataTables-bidwiz-emp">
                            <a href="<?php 
  
  if(isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on') 
      $link = "https"; 
  else
      $link = "http"; 
    
  $link .= "://"; 
    
  $link .= $_SERVER['HTTP_HOST']; 
    
  $link .= $_SERVER['REQUEST_URI']; 
  $link = substr($link, 0, strpos($link, "?"));
        
  echo $link; 
  ?>" style="float:right;margin-bottom:2px" class="btn btn-success tbl_e_narrow">View Active Employees</a>
                                    <thead>
                                        <tr>
                                                <th>
                                                Staff ID
                                                </th>
                                                <th>
                                                First Name
                                                </th>
                                                <th>
                                                Last Name

                                                </th><th>
                                                Email
                                                </th>
                                               
                                            <th>Activate</th>
                                            <th>Delete</th>
                                            </tr>
                                        </thead>
                                        <tbody>
<?php
    if($conn){
        $query = "SELECT * FROM employee WHERE status = 'disabled' ORDER BY employee_id";
        $run = mysqli_query($conn, $query);

        if($run){
            while($row = mysqli_fetch_array($run)){
                $id = $row['employee_id'];
                $name = $row['employee_fname']; 
                $lname = $row['employee_lname'];  
                $email = $row['employee_email'];       
                $status = $row['status'];  
    
                echo '<tr class="odd gradeX">';
                echo '<td>' .$id. '</td>'; 
                echo '<td>' .$name. '</td>';
                echo '<td>' .$lname. '</td>';
                echo '<td>' .$email. '</td>';
                echo "<td><a href='employee.php?activate=$id&view_deactivate='>Activate</a> </td>";              
                echo "<td><a href='employee.php?delete=$id&view_deactivate=&ename=$name $lname' class=\"delete22\" data-confirm=\"Are you sure to permanently delete $name $lname?\">Delete</a> </td>";
                echo '</tr>';
            }
        }

    }

?>
                                            
                                            </tbody>
                                    
                                    </table>
                                    <?php } ?>

<?php if(!isset($_REQUEST['view_deactivate'])) { ?>
    <table width="100%" class="table table-striped table-bordered table-hover" id="dataTables-bidwiz-emp">
    <a href="<?php 
  
  if(isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on') 
      $link = "https"; 
  else
      $link = "http"; 
    
  $link .= "://"; 
    
  $link .= $_SERVER['HTTP_HOST']; 
    
  $link .= $_SERVER['REQUEST_URI']; 
  $link = substr($link, 0, strpos($link, "?"));
        
  echo $link; 
  ?>?view_deactivate" style="float:right;margin-bottom:2px" class="btn btn-danger tbl_e_narrow">View Deactivated Employees</a>
                                    <thead>
                                        <tr>
                                                <th>
                                                Staff ID
                                                </th>
                                                <th>
                                                First Name
                                                </th>
                                                <th>
                                                Last Name

                                                </th><th>
                                                Email
                                                </th>
                                               
                                               
                                            <th>Edit</th>
                                            <th>Change Password</th>
                                            <th>Deactivate</th>
                                            </tr>
                                        </thead>
                                        <tbody>
<?php
    if($conn){
        $query = "SELECT * FROM employee WHERE status = 'active' ORDER BY employee_id";
        $run = mysqli_query($conn, $query);

        if($run){
            while($row = mysqli_fetch_array($run)){
                $id = $row['employee_id'];
                $name = $row['employee_fname']; 
                $lname = $row['employee_lname'];  
                $email = $row['employee_email'];       
                $status = $row['status'];  
    
                echo '<tr class="odd gradeX">';
                echo '<td>' .$id. '</td>'; 
                echo '<td>' .$name. '</td>';
                echo '<td>' .$lname. '</td>';
                echo '<td>' .$email. '</td>';
                echo "<td><a href='employee.php?spe=$id'>Edit</a> </td>";
                echo "<td><a href='employee.php?pass=$id'>Change</a> </td>"; 
                echo "<td><a href='employee.php?deactive=$id'>Deactivate</a> </td>";
                echo '</tr>';
            }
        }

    }

?>
                                            
                                            </tbody>
                                    
                                    </table>
<?php } ?>
                                    </div><!-- panel-body  -->
                 </div> <!-- panel panel-default -->
            </div> <!--  col lg 12-->
        </div> <!-- row -->
    </div> <!--  page wrapper -->
                              
       
        <script>
    var deleteLinks = document.querySelectorAll('.delete22');

for (var i = 0; i < deleteLinks.length; i++) {
  deleteLinks[i].addEventListener('click', function(event) {
	  event.preventDefault();

	  var choice = confirm(this.getAttribute('data-confirm'));

	  if (choice) {
	    window.location.href = this.getAttribute('href');
	  }
  });
}
    </script>
    
<?php  include "footer.php";   ?>