<?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
$searchByUserName = $_POST['searchByUserName'];
$searchByroleName = $_POST['searchByroleName'];
$searchByStaffCurrent = 1;
## Search 

$searchQuery = " ";
if($searchByUserName != ''){
    $searchQuery .= " and (staff.firstname like '%".$searchByUserName."%' or staff.middlename like '%".$searchByUserName."%' or staff.lastname like '%".$searchByUserName."%' or staff.email like '%".$searchByUserName."%' or users.role_id like '%".$searchByUserName."%' or users.dept_id like '%".$searchByUserName."%') ";
}


if($searchByroleName != ''){
    $searchQuery .= " and (users.role_id='".$searchByroleName."') ";
}



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



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

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

## Fetch records
$empQuery = "select users.status,staff.firstname,staff.lastname,staff.dept_id,users.user_id,users.role_id,users.remarks,staff.des_id,staff.email,staff.phone_number from staff,users WHERE staff.staff_id=users.staff_id and users.status=1 and 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;
$empRecords = mysqli_query($conn, $empQuery);
$data = array();
$x = 1;
while ($row = mysqli_fetch_assoc($empRecords)){
	
	$productId = $row['user_id'];
	$roleId = $row['role_id'];
	$desId = $row['des_id'];
	$deptId = $row['dept_id'];
	$email = $row['email'];
	$phoneNum = $row['phone_number'];
	$status = $row['status'];
	$remarks = $row['remarks'];
	
	$phoneNumber = substr($phoneNum, 3);
	
	$pNumber = '0'.$phoneNumber;
	
	
	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 user_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($status==1){
	$st = 'ACTIVE';
	$button = '<!-- Single button -->
	<div class="btn-group">
	    <a type="button" class="btn btn-primary" href="manageUserForm.php?i='.$productId.'" id="editOrderModalBtn">  Block User </a>
	</div>';
	}else{
	$st = 'PASSIVE';
	$button = '<!-- Single button -->
	<div class="btn-group">
	    <a type="button" class="btn btn-primary" href="manageUserForm.php?i='.$productId.'" id="editOrderModalBtn">  Give Access </a>
	</div>';
	}
	
	$button1 = '<!-- Single button -->
	<div class="btn-group">
	    <a type="button" class="btn btn-primary" href="userPermissions.php?i='.$productId.'" id="editOrderModalBtn">  View </a>
	</div>';
	
	$button2 = '<!-- Single button -->
	<div class="btn-group">
	    <a type="button" class="btn btn-primary" href="addPermissions.php?i='.$productId.'" id="editOrderModalBtn">  Add </a>
	</div>';
	
	$button3 = '<!-- 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>';
	
	
    $data[] = array(
    		"user_id"=>$x,
			"Staff"=>$row['firstname'].'&nbsp&nbsp'.$row['lastname'],
			"PhoneNumber"=>$pNumber,
			"DeptName"=>$depart_name,
			"DesignName"=>$design_name,
			"RoleName"=>$role_name22,
			"Email"=>$email,
			"Remarks"=>$remarks,
			"Status"=>$st,
    		"edit"=>$button,
			"permissions"=>$button1,
			"add"=>$button2,
			"delete"=>$button3
    	);
		$x++;
		
		
}

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

echo json_encode($response);
