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

// Connect to mysqli database
$page = 1; // The current page
$sortname = 'schedule_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
$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=='training_name'){
            $searchSql =" and  assign_training_id in ( select training_master_id from training_master where upper(training_name) like upper(trim('%$query%')) ) ";
        }
        
    } 
}
if($searchSql!=''){
    if($qtype!=''){
        if($qtype=='trainer_name'){
            $searchSql =" and  training_schedule_id in ( select schedule_id from training_schedule where upper(trainer_name) like upper(trim('%$query%')) ) ";
        }
        
    }
}
if($searchSql!=''){
    if($qtype!=''){
        if($qtype=='location'){
            $searchSql =" and  training_schedule_id in ( select schedule_id from training_schedule where upper(location) like upper(trim('%$query%')) ) ";
        }
        
    }
}
if ($searchSql != '') {
    
    if ($qtype == 'from_date') {
        
        $searchSql = " and training_schedule_id in (select schedule_id from training_schedule  where upper(date_format(date(from_date),'%Y-%m-%d')=str_to_date('$query','%d-%m-%Y'))";
        //echo $searchSql;
        $searchSql=preg_replace('/and/', 'where', $searchSql, 1);
    }
}

if ($searchSql != '') {
    
    if ($qtype == 'completion_date') {
        
        $searchSql = " and date_format(date(completion_date),'%Y-%m-%d')=str_to_date('$query','%d-%m-%Y') ";
        //echo $searchSql;
        $searchSql=preg_replace('/and/', 'where', $searchSql, 1);
    }
}


if($_SESSION['RoleId']=='7'){
  $sql = "select count(*) from employee_assign_training   where emp_id='".$_SESSION['logged_user_empid']."'  and status='Y' $searchSql  $sortSql ";
   //echo  $sql;
}else{
	
    
	$sql = "select count(*) from employee_assign_training   where  status='Y'  $searchSql $sortSql ";
//	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_training = "select * from employee_assign_training  where emp_id='".$_SESSION['logged_user_empid']."' and  status='Y'  $searchSql  $sortSql    $limitSql";
		//echo $sql_emp_assign_training;
}else{
	
	
	$sql_emp_assign_training = "select * from employee_assign_training  where status='Y' $searchSql  $sortSql  $limitSql";
}


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

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

while ($row_emp_assign_training = mysqli_fetch_assoc($results_emp_assign_training)) {

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

if($access_level=='W' || $access_level=='E' )
{
$edit_link="<a href=\"#\" class=\"blue\" onclick=\"open_assign_training_schedule('".$emp_assign_training_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_training['emp_id']);
 $query_schedule = "select * from  training_schedule where schedule_id='".$row_emp_assign_training['training_schedule_id']."' ";
	  //echo   $query_schedule;
                              $result_schedule = @mysqli_query($conn,$query_schedule);
							   while($rows_schedule = mysqli_fetch_assoc($result_schedule)){
								   $training_name=getTableFieldValue('training_master','training_name','training_master_id',$rows_schedule['training_id']);
								    $from_date=  date_format(date_create($row_training['from_date']),"d-M-Y");
									$to_date=date_format(date_create($row_training['to_date']),"d-M-Y");
									$trainer_name=$rows_schedule['trainer_name'];
									$training_location=$rows_schedule['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_training['completion_date']!=null && $row_emp_assign_training['completion_date']!='0000-00-00' )				
$date_completion= date_format(date_create($row_emp_assign_training['completion_date']),"d-M-Y");
$data['rows'][] = array(
'id' => $row_emp_assign_training['emp_assign_training_id'],
'cell' => array($count++,  $emp_name,$training_name,$from_date,$to_date,$trainer_name,$training_location,$status,$date_completion)
);
}
echo json_encode($data);
?>