<?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'];
$searchBystaffCate = $_POST['searchBystaffCate'];
$searchByGender = $_POST['searchByGender'];
$searchByLeaveType = $_POST['searchByLeaveType'];
$searchByMarital = $_POST['searchByMarital'];
//$searchByStaffCurrent = 1;
## Search 

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




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

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

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


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



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


if($searchByLeaveType != ''){
    $searchQuery .= " and (leaves.leaveType_id='".$searchByLeaveType."') ";
}

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

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."%')";
}

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



## Total number of records without filtering
$sel = mysqli_query($conn,"select count(*) as allcount from staff,leaves,leave_type where staff.staff_id=leaves.staff_id and leaves.leaveType_id=leave_type.leaveType_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,leaves,leave_type WHERE staff.staff_id=leaves.staff_id and leaves.leaveType_id=leave_type.leaveType_id and 1 ".$searchQuery);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records['allcount'];

## Fetch records
$empQuery = "select staff.firstname,staff.lastname,staff.staff_id,leaves.leavestartDate,leaves.leaveendDate,leaves.remarks,leave_type.type,leave_type.duration from staff,leaves,leave_type WHERE staff.staff_id=leaves.staff_id and leaves.leaveType_id=leave_type.leaveType_id and 1 ".$searchQuery." order by staff.firstname";
$empRecords = mysqli_query($conn, $empQuery);
$data = array();
$x = 1;
while ($row = mysqli_fetch_assoc($empRecords)){
	
	$productId = $row['staff_id'];
	$leaveDate = $row['leavestartDate'];
	$leaveEndDate = $row['leaveendDate'];
	$leaveType = $row['type'];
	$leaveDuration = $row['duration'];
	$remarks = $row['remarks'];
	$firstname = $row['firstname'];
	$lastname = $row['lastname'];
	$leave_Date = date('d-m-Y', strtotime($leaveDate));
	$leave_endDate = date('d-m-Y', strtotime($leaveEndDate));
	
	$currentDate = date("Y-m-d");
	
			if($leaveEndDate>$currentDate){
					
					
		$d1m = strtotime("$currentDate");
                             $d2m = strtotime("$leave_endDate");
                             $mindate = min($d1m, $d2m);
                             $maxdate = max($d1m, $d2m);
                                    $i = 0;
				while (($mindate = strtotime("+1 YEAR", $mindate)) <= $maxdate) {
                                   $i++;
                                      }
									  $Ye_ar = $i;
									  $ag_e=$Ye_ar;
									  
							if($ag_e>0) {
									$ag_e=$Ye_ar; 
										 
									 }else{
										 
						$d11m = strtotime("$currentDate");
                             $d22m = strtotime("$leave_endDate");
                             $mindate = min($d11m, $d22m);
                             $maxdate = max($d11m, $d22m);
                                    $i = 0;
				while (($mindate = strtotime("+1 MONTH", $mindate)) <= $maxdate) {
                                   $i++;
                                      }					  
										  
								$Mon_th = $i;		  
								$ag_e1 = $Mon_th.' '.'Months';
                                  
                                  if($Mon_th>0){
									 $ag_e =  $ag_e1;
								      }else{
									$d111m = strtotime("$currentDate");
                             $d222m = strtotime("$leave_endDate");
                             $mindate = min($d111m, $d222m);
                             $maxdate = max($d111m, $d222m);
                                    $i = 0;
				while (($mindate = strtotime("+1 DAY", $mindate)) <= $maxdate) {
                                   $i++;
                                      }	

                                $da_y = $i;		  
								$ag_e1 = $da_y.' '.'Days';									  
								$ag_e =  $ag_e1;	  
									  }								  
										 
									 }			
					
					
					
					
		$st = $ag_e." "."Left";
	}else if($leaveEndDate==$currentDate){
		$st = "Leave End";
	}else{
		$st = "Leave End";
	}
	
	$button3 = '<!-- Single button -->
	<div class="btn-group">
	    <a type="button" class="btn btn-primary" href="leavestaffDocs.php?i='.$productId.'" id="editOrderModalBtn"><center><i class="glyphicon glyphicon-eye-open"></i></center></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>';
	
	$button4 = '<!-- 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(
    		"staff_id"=>$x,
			"Staff"=>$row['firstname'].'&nbsp&nbsp'.$row['lastname'],
			"LeaveType"=>$leaveType,
			"LeaveStartDate"=>$leave_Date,
			"LeaveEndDate"=>$leave_endDate,
			"Duration"=>$leaveDuration,
			"remarks"=>$remarks,
			"leaveStatus"=>$st,
			"staffdocs"=>$button3,
			"staffFile"=>$button2
    	);
		$x++;
		
		
}

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

echo json_encode($response);
