<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
include 'connect.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

## Search 
$searchQuery = " ";
if($searchValue != ''){
	$searchQuery = " and (visatype like '%".$searchValue."%' or 
        visatypedesc like '%".$searchValue."%' or 
        visatmid like'%".$searchValue."%' ) ";
}

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

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


## Fetch records
$empQuery = "select * from res_inquiry_master WHERE 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;
//$empQuery = "select * from res_inquiry_master WHERE 1 ";
$empRecords = mysqli_query($conn, $empQuery);
$data = array();
$i=1;
while ($row = mysqli_fetch_assoc($empRecords)) {
	$imid=$row['imid'];
	$imstatus = $row['imstatus'];
	
	$ipmobile=str_replace ("'","'",$row['ipmobile']);
				$ipmobile=str_replace ('"','\"',$ipmobile);
				
	if($imstatus == 'New')	
					{						
				$imstatus2="<div class='badge badge-light-info'>$imstatus</div>";
					}else if($imstatus == 'Inquiry Details')
					{
						$imstatus2="<div class='badge badge-light-success'>$imstatus</div>";
					}else if($imstatus == 'Counselling Details')
					{
						$imstatus2="<div class='badge badge-light-primary'>$imstatus</div>";
					}else{
						$imstatus2="<div class='badge badge-light-danger'>$imstatus</div>";
					}	
					
					$ipname=str_replace ("'","'",$row['ipname']);
				$ipname=str_replace ('"','\"',$ipname);
				
				$ipsurname=str_replace ("'","'",$row['ipsurname']);
				$ipsurname=str_replace ('"','\"',$ipsurname);
				
				$ipname1 = $ipname." ".$ipsurname;
					
					$iaddedtype=$row['iaddedtype'];
				$entrybyuserid=str_replace ("'","'",$row['entrybyuserid']);
				$entrybyuserid=str_replace ('"','\"',$entrybyuserid);
				$eveag = "select ufullname,utype from res_user_master where umid=$entrybyuserid";
				$reag = mysqli_query($conn, $eveag);
				if(mysqli_num_rows($reag) > 0)
				{
					while($rtag = mysqli_fetch_assoc($reag))
					{
						$entrybyuserid=$rtag['ufullname'];
						$utype=$rtag['utype'];
					}
				}
				else
				{
					$entrybyuserid="";
					$utype="";
				}
				if($iaddedtype=='')
				{
					$iaddedtype=$utype;
				}	
					
					$entrybyuserid1 = $entrybyuserid." (".$iaddedtype.")"; 
					
					$idate=date("d-m-Y", strtotime($row['idate']));
					
					
					$eve3 = "select * from res_user_master  where umid in (select inqbranch from res_inquiry_master where imid=$imid)";
				$re3 = mysqli_query($conn, $eve3);
				if(mysqli_num_rows($re3)>0)
				{
					while($rt3 = mysqli_fetch_assoc($re3))
				{
					
					$ufullname1=$rt3['ufullname'];
				}
				}
				else
				{
					$ufullname1="";
				}
				
				$ipemail=str_replace ("'","'",$row['ipemail']);
				$ipemail=str_replace ('"','\"',$ipemail);
				
				$tt1="";
				
				$eve1 = "select * from res_user_master  where umid in(select counsellorid from res_counsellor_detail where imid=$imid)";
				$re1 = mysqli_query($conn, $eve1);
				while($rt1 = mysqli_fetch_assoc($re1))
				{
					$tt1=$rt1['ufullname'];										
				}
    $data[] = array(
    		"imid"=>$imid,
    		"idate"=>$idate,
    		"ufullname"=>$ufullname1,
    		"ipname"=>$ipname1,
    		"imstatus"=>$imstatus2,
    		"ipemail"=>$ipemail,
    		"ipmobile"=>$ipmobile,
    		"entrybyuserid"=>$entrybyuserid1,
    		"counsellorid"=>$tt1
    		
			

				
			
    	);
}

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

echo json_encode($response);
