<?php
require_once '../../Server/connection.php';

## Read value
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
$searchValue = $_POST['search']['value']; // Search value

## Custom Field value
$searchByStaffName = $_POST['searchByStaffName'];
$searchBydeptName = $_POST['searchBydeptName'];
$searchBysectName = $_POST['searchBysectName'];
$searchBydesName = $_POST['searchBydesName'];
$searchBystatusName = $_POST['searchBystatusName'];
$searchBystaffCate = $_POST['searchBystaffCate'];
$searchByMarital = $_POST['searchByMarital'];
$searchByGender = $_POST['searchByGender'];
$searchByEduLevel = $_POST['searchByEduLevel'];
//$searchByStaffCurrent = 1;
## Search 

$searchQuery = " ";
if($searchByStaffName != ''){
    $searchQuery .= " and (firstname like '%".$searchByStaffName."%' or middlename like '%".$searchByStaffName."%' or lastname like '%".$searchByStaffName."%' or email like '%".$searchByStaffName."%') ";
}

if($searchBydeptName != ''){
    $searchQuery .= " and (dept_id='".$searchBydeptName."') ";
}

if($searchBysectName != ''){
    $searchQuery .= " and (sect_id='".$searchBysectName."') ";
}

if($searchBydesName != ''){
    $searchQuery .= " and (des_id='".$searchBydesName."') ";
}

if($searchBystatusName != ''){
    $searchQuery .= " and (status_id='".$searchBystatusName."') ";
}

if($searchBystaffCate != ''){
    $searchQuery .= " and (staffcate_id='".$searchBystaffCate."') ";
}

if($searchByMarital != ''){
    $searchQuery .= " and (marital_id='".$searchByMarital."') ";
}

if($searchByGender != ''){
    $searchQuery .= " and (gender_id='".$searchByGender."') ";
}

if($searchByEduLevel != ''){
    $searchQuery .= " and (eduLevel_id='".$searchByEduLevel."') ";
}

if($searchValue != ''){ 
    $searchQuery .= " and (firstname like '%".$searchValue."%' or middlename like '%".$searchValue."%' or lastname like '%".$searchValue."%' or email like '%".$searchValue."%' or phone_number like '%".$searchValue."%')";
}

//if($searchByStaffCurrent != ''){
    //$searchQuery .= " and (status_id='".$searchByStaffCurrent."') ";
//}



## Total number of records without filtering
$sel = mysqli_query($conn,"select count(*) as allcount from staff");
$records = mysqli_fetch_assoc($sel);
$totalRecords = $records['allcount'];

## Total number of records with filtering
$sel = mysqli_query($conn,"select count(*) as allcount from staff WHERE 1 ".$searchQuery);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records['allcount'];

## Fetch records
$empQuery = "select * from staff WHERE 1 ".$searchQuery." order by firstname";
$empRecords = mysqli_query($conn, $empQuery);
$data = array();
$x = 1;
while ($row = mysqli_fetch_assoc($empRecords)){
	
	$productId = $row['staff_id'];
	$deptId = $row['dept_id'];
	$desId = $row['des_id'];
	$email = $row['email'];
	$status_id = $row['status_id'];
	$phoneNum = $row['phone_number'];
	
	$phoneNumber = substr($phoneNum, 3);
	
	$pNumber = '0'.$phoneNumber;
	
	
	if($status_id==NULL){
		$status_name='';
	     }else{
	$empQuery15 = "select * from status WHERE status_id='$status_id'";
    $empRecords15 = mysqli_query($conn, $empQuery15);
    while ($row15 = mysqli_fetch_assoc($empRecords15)){
		$status_name = $row15['name'];
	         }		
		 }
	
	if($deptId==NULL){
		$depart_name='';
	     }else{
	$empQuery1 = "select * from departments WHERE dept_id='$deptId'";
    $empRecords1 = mysqli_query($conn, $empQuery1);
    while ($row1 = mysqli_fetch_assoc($empRecords1)){
		$depart_name = $row1['depart_name'];
	         }		
		 }
		 
		 if($desId==NULL){
		$design_name='';
	     }else{
	$empQuery12 = "select * from designation WHERE des_id='$desId'";
    $empRecords12 = mysqli_query($conn, $empQuery12);
    while ($row12 = mysqli_fetch_assoc($empRecords12)){
		$design_name = $row12['des_name'];
	         }		
		 }
		
	
		 $empQuery13 = "select * from users WHERE staff_id='$productId'";
    $empRecords13 = mysqli_query($conn, $empQuery13);
    while ($row13 = mysqli_fetch_assoc($empRecords13)){
		$role_id3 = $row13['role_id'];
	         }	
			 
			
			  $empQuery132 = "select * from roles WHERE role_id='$role_id3'";
    $empRecords132 = mysqli_query($conn, $empQuery132);
    while ($row132 = mysqli_fetch_assoc($empRecords132)){
		$role_name22 = $row132['role_name'];
	         }
			 
	
/*	
    if($roleId==NULL){
		$role_name='';
	     }else{
	$empQuery12 = "select * from roles WHERE role_id='$roleId'";
    $empRecords12 = mysqli_query($conn, $empQuery12);
    while ($row12 = mysqli_fetch_assoc($empRecords12)){
		$role_name = $row12['role_name'];
	         }		
		 }
		 */
		
	
	//$imageUrl = substr($row['staff_image'], 3);
	
	//$staffImage = "<img class='img-round' src='".$imageUrl."' style='height:40px; width:70px;'  />";
	
	$button3 = '<!-- Single button -->
	<div class="btn-group">
	    <a type="button" class="btn btn-primary" href="staffDocs.php?i='.$productId.'" id="editOrderModalBtn"><center><i class="glyphicon glyphicon-eye-open"></i></center></a>
	</div>';
	
	$button4 = '<!-- Single button -->
	<div class="btn-group">
	    <a type="button" class="btn btn-primary" href="staffCont.php?i='.$productId.'" id="editOrderModalBtn"><center><i class="glyphicon glyphicon-eye-open"></i></center></a>
	</div>';
	
		
		$button = '<!-- Single button -->
	<div class="btn-group">
	    <a type="button" class="btn btn-primary" data-toggle="modal"  data-target="#MyModal" data-id='.$productId.' id="getUser11"> <i class="glyphicon glyphicon-eye-open"></i></a>
	</div>';
	
	$button1 = '<!-- Single button -->
	<div class="btn-group">
	    <a type="button" class="btn btn-primary" href="editStaff.php?i='.$productId.'" id="editOrderModalBtn"><center><i class="glyphicon glyphicon-edit"></i></center></a>
	</div>';
	
	
	
	//$button1 = '<!-- Single button -->
	//<div class="btn-group">
	    //<a type="button" class="btn btn-primary" data-toggle="modal" id="editCategoriesModalBtn" data-target="#editCategoriesModal" onclick="editCategories('.$productId.')"> <i class="glyphicon glyphicon-edit"></i></a>
	//</div>';
	
	//$button2 = '<!-- Single button -->
	//<div class="btn-group">
	//<a type="button" class="btn btn-primary" data-toggle="modal" data-target="#removeProductModal" id="removeProductModalBtn" onclick="removeProduct('.$productId.')"> <i class="glyphicon glyphicon-trash"></i></a>
	
	//</div>';
	
	
	$button2 = '<!-- Single button -->
	<div class="btn-group">
	    <a type="button" class="btn btn-primary" href="pdf_Stafffile.php?i='.$productId.'" id="editOrderModalBtn"> <center><i class="glyphicon glyphicon-eye-open"></i></center></a>
	</div>';
	
	
    $data[] = array(
    		"staff_id"=>$x,
			"Staff"=>$row['firstname'].'&nbsp&nbsp'.$row['lastname'],
			"PhoneNumber"=>$pNumber,
			"DeptName"=>$depart_name,
			"DesignName"=>$design_name,
			"Email"=>$email,
			"Status"=>$status_name,
    		"edit"=>$button1,
			"staffdocs"=>$button3,
			"staffCont"=>$button4,
			"staffFile"=>$button2
    	);
		$x++;
		
		
}

## Response
$response = array(
    "draw" => intval($draw),
    "iTotalRecords" => $totalRecords,
    "iTotalDisplayRecords" => $totalRecordwithFilter,
    "aaData" => $data
);

echo json_encode($response);
