<?php 
include('includes/config/config.php');
include('includes/auth/auth.php');
include('includes/functions.php');
error_reporting(E_ERROR | E_PARSE);
include('access.php');
error_reporting(E_ERROR | E_PARSE);
?>
<?php 

// Connect to mysqli database
$page = 1; // The current page
$sortname = 'checkup_mgmt_id'; // Sort column
$sortorder = 'asc'; // Sort order
$qtype = ''; // Search column
$query = ''; // Search string
// Get posted data
if (isset($_POST['page'])) {
        $page = mysqli_real_escape_string($conn,$_POST['page']);
}
if (isset($_POST['sortname'])) {
        $sortname = mysqli_real_escape_string($conn,$_POST['sortname']);
		
}
if (isset($_POST['sortorder'])) {
        $sortorder = mysqli_real_escape_string($conn,$_POST['sortorder']);
}
if (isset($_POST['qtype'])) {
        $qtype = mysqli_real_escape_string($conn,$_POST['qtype']);
}
if (isset($_POST['query'])) {
        $query = mysqli_real_escape_string($conn,$_POST['query']);
}
if (isset($_POST['rp'])) {
        $rp = mysqli_real_escape_string($conn,$_POST['rp']);
}
// Setup sort and search SQL using posted data
$hasReadAccess = isAccessible($_SESSION['RoleId'],$menu_key,'R');
$hasWriteAccess = isAccessible($_SESSION['RoleId'],$menu_key,'W');
$hasExecuteAccess = isAccessible($_SESSION['RoleId'],$menu_key,'E');

$sortSql = "order by $sortname $sortorder";
$searchSql = ($qtype != '' && $query != '') ? "where upper($qtype) like upper('%".trim($query)."%')" : '';
// Get total count of records
if($searchSql!=''){
    if($qtype!=''){
        if($qtype=='checkup_name'){
            $searchSql =" and  assign_checkup_id in ( select checkup_master_id from checkup_master where upper(checkup_name) like upper(trim('%$query%'))) ";
        }
        
    }
}
if($searchSql!=''){
    if($qtype!=''){
        if($qtype=='checkup_by'){
            $searchSql =" and  checkup_schedule_id in ( select checkup_mgmt_id from checkup_list_mgmt where upper(checkup_by) like upper(trim('%$query%')) ) ";
        }
        
    }
}
if($searchSql!=''){
    if($qtype!=''){
        if($qtype=='location'){
            $searchSql =" and  checkup_schedule_id in ( select checkup_mgmt_id from checkup_list_mgmt where upper(location) like upper(trim('%$query%')) ) ";
        }
        
    }
}
if ($searchSql != '') {
    
    if ($qtype == 'assign_date') {
        
        $searchSql = "   and  date_format(date(assign_date),'%Y-%m-%d')=str_to_date('$query','%d-%m-%Y')";
        //echo $searchSql;
  
    }
}

if($_SESSION['RoleId']=='7'){
  $sql = "select count(*) from employee_assign_checkup where emp_id='".$_SESSION['logged_user_empid']."' and status!='Y' $searchSql  ";
//echo  $sql;
}else{
	
	
	$sql = "select count(*) from employee_assign_checkup where status!='Y' $searchSql  ";
//echo $sql;
}

$result = mysqli_query($conn,$sql);
$row = mysqli_fetch_array($result);
$total = $row[0];
// Setup paging
if(!isSet($rp)){
	$rp=10;
}
$pageStart = ($page-1)*$rp;
$limitSql = "limit $pageStart, $rp";
// Return JSON data
$data = array();
$data['page'] = $page;
$data['total'] = $total;
$data['rows'] = array();
if($_SESSION['RoleId']=='7'){
   
		$sql_emp_assign_checkup = "select * from employee_assign_checkup where emp_id='".$_SESSION['logged_user_empid']."' and status!='Y'  $searchSql    $limitSql";
}else{
	
	
	$sql_emp_assign_checkup = "select * from employee_assign_checkup where status!='Y' $searchSql  $sortSql  $limitSql";
	
}


//echo $sql_emp_assign_checkup;
$results_emp_assign_checkup = mysqli_query($conn,$sql_emp_assign_checkup);
$count=($page-1)*$rp+1;

//echo $sql_training;
//echo $access_level;

while ($row_emp_assign_checkup = mysqli_fetch_assoc($results_emp_assign_checkup)) {

$emp_assign_checkup_id=$row_emp_assign_checkup['emp_assign_checkup_id'];
//echo $emp_assign_checkup_id;
if($row_emp_assign_checkup['status']=="N"){
	$status="Not Completed";
}else if($row_emp_assign_checkup['status']=="Y"){
	$status="Completed";
}
//echo $training_id;
$view_link="";
$edit_link="";
$delete_link="";
$links="";

	//echo $emp_assign_checkup_id;

	// echo $emp_assign_checkup_id ;
//	 echo hjyhjhj;
$edit_link="<a href=\"#\" class=\"blue\" onclick=\"open_assign_checkup_schedule('".$emp_assign_checkup_id."','E');\"><i class=\"glyphicon glyphicon-eye-open\"></i></a>";


$space="&nbsp;&nbsp;&nbsp;";
$links = $space.$view_link.$space.$edit_link.$space.$delete_link;
$emp_name=getTableFieldValue('employee',"CONCAT(fname,' ',lname)",'id',$row_emp_assign_checkup['emp_id']);

 //echo $row_emp_assign_checkup['checkup_schedule'];
 $query_schedule = "select * from  checkup_list_mgmt where checkup_mgmt_id='".$row_emp_assign_checkup['checkup_schedule_id']."' ";
//echo   $query_schedule;
                              $result_schedule = @mysqli_query($conn,$query_schedule);
							  
							   while($rows_schedule = mysqli_fetch_assoc($result_schedule)){
								   
								 //  $checkup_type=getTableFieldValue('checkup_type','checkup_type_name','checkup_type_id',$row1['checkup_mgmt_name'],'');
								   $checkup_name=getTableFieldValue('checkup_type','checkup_type_name','checkup_type_id',$rows_schedule['checkup_mgmt_name']);
								//   echo  $checkup_name;
								    $from_date=  date_format(date_create($rows_schedule['from_date']),"d-M-Y");
									$to_date=date_format(date_create($rows_schedule['to_date']),"d-M-Y");
									$checkup_by=$rows_schedule['checkup_by'];
									//echo 
									$checkup_location=$rows_schedule['checkup_location'];
							   	 //$training_schedule= $training_name.'('.$rows_schedule['trainer_name'].','.date_format(date_create($row_training['from_date']),"d-M-Y").' '.date_format(date_create($row_training['to_date']),"d-M-Y").')'; 
							     
							   }
							   $date_completion="";
if($row_emp_assign_checkup['completion_date']!=null && $row_emp_assign_training['completion_date']!='0000-00-00' )				
$date_completion= date_format(date_create($row_emp_assign_checkup['completion_date']),"d-M-Y");
$data['rows'][] = array(
'id' => $row_emp_assign_checkup['emp_assign_checkup_id'],
'cell' => array($count++,  $emp_name,$checkup_name,$from_date,$to_date,$checkup_by,$checkup_location,$status,$date_completion)
);

}
echo json_encode($data);
?>