<?php
session_start();
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
if($columnIndex == 1)
{
$columnName ='imid';
}else{
$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 (res_inquiry_master.imstatus like '%".$searchValue."%' or 
        res_inquiry_master.ipname like '%".$searchValue."%' or 
        res_inquiry_master.ipemail like'%".$searchValue."%' or 
        res_inquiry_master.ipmobile like'%".$searchValue."%' or 
        res_inquiry_master.ipsurname like'%".$searchValue."%' ) ";
}
 /*  $columnName = 'imstatus';
$columnSortOrder='asc';
$row=1;
$rowperpage=20;  
 */
 
 if($_SESSION['utype']=='admin')
 {
	//echo $a = "select count(*) as allcount from res_inquiry_master,res_studentinquiry_detail where res_inquiry_master.iaddedfrom = 'adminpanel' and res_inquiry_master.imid = res_studentinquiry_detail.imid and   res_studentinquiry_detail.status in ('Open','Cold','Hot','Warm') and res_inquiry_master.imid in (select imid from res_counsellor_detail where counsellorid in (select umid from res_user_master where utype='applicationmanager'))";
	 $sel = mysqli_query($conn,"select count(*) as allcount from res_inquiry_master,res_studentinquiry_detail where res_inquiry_master.iaddedfrom = 'adminpanel' and res_inquiry_master.imid = res_studentinquiry_detail.imid and   res_studentinquiry_detail.status in ('Open','Cold','Hot','Warm') and res_studentinquiry_detail.siprefferservice = 'Coaching'");
$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,res_studentinquiry_detail WHERE res_inquiry_master.iaddedfrom = 'adminpanel' and  res_inquiry_master.imid = res_studentinquiry_detail.imid and    res_studentinquiry_detail.status in ('Open','Cold','Hot','Warm') and res_studentinquiry_detail.siprefferservice = 'Coaching'  ".$searchQuery);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records['allcount'];


## Fetch records
  $empQuery = "select * from res_inquiry_master,res_studentinquiry_detail WHERE res_inquiry_master.iaddedfrom = 'adminpanel' and  res_inquiry_master.imid = res_studentinquiry_detail.imid and    res_studentinquiry_detail.status in ('Open','Cold','Hot','Warm') and res_studentinquiry_detail.siprefferservice = 'Coaching' ".$searchQuery." order by res_inquiry_master.".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;

 
 }else{

		//echo $s1 = "select count(*) as allcount from res_inquiry_master where  imid in (select imid from res_counsellor_detail where counsellorid=".$_SESSION['umid']." and costatus in ('Pending','In Progress','Hold'))";
	 $sel = mysqli_query($conn,"select count(*) as allcount from res_inquiry_master,res_studentinquiry_detail where res_inquiry_master.imid = res_studentinquiry_detail.imid and  res_studentinquiry_detail.stiqmid in (select stiqmid from res_counsellor_detail where counsellorid=".$_SESSION['umid']." and costatus in ('Pending','In Process','Hold')) or res_inquiry_master.entrybyuserid=".$_SESSION['umid']);
$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,res_studentinquiry_detail WHERE res_inquiry_master.imid = res_studentinquiry_detail.imid and  res_studentinquiry_detail.stiqmid in (select stiqmid from res_counsellor_detail where counsellorid=".$_SESSION['umid']." and costatus in ('Pending','In Process','Hold')) or res_inquiry_master.entrybyuserid=".$_SESSION['umid']." ".$searchQuery);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records['allcount'];


## Fetch records
 $empQuery = "select * from res_inquiry_master,res_studentinquiry_detail WHERE res_inquiry_master.imid = res_studentinquiry_detail.imid and  res_studentinquiry_detail.stiqmid in (select stiqmid from res_counsellor_detail where counsellorid=".$_SESSION['umid']." and costatus in ('Pending','In Process','Hold') ) or  res_inquiry_master.entrybyuserid=".$_SESSION['umid']." ".$searchQuery." order by res_inquiry_master.".$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'];
	$agentid = $row['agentid'];
	
	$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']));
					if($row['ipaddedon'] != '')
					{
					$ipaddedon=date("d-m-Y H:i:s", strtotime($row['ipaddedon']));
					}else{
						$ipaddedon='';
					}
					
					if($agentid == 0)
				{
					$agentname="Direct";
				}else{
				$eveag = "select * from res_user_master where umid=$agentid";
				$reag = mysqli_query($conn, $eveag);
				if(mysqli_num_rows($reag) > 0)
				{
					while($rtag = mysqli_fetch_assoc($reag))
					{
						$agentname=$rtag['ufullname'];
					}
				}
				else
				{
					$agentname="";
				}
				}
					
					
					$eve3 = "select * from res_branch_master  where branchmid 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['branchname'];
				}
				}
				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) and utype='councellor'";
				$re1 = mysqli_query($conn, $eve1);
				while($rt1 = mysqli_fetch_assoc($re1))
				{
					$tt1=$rt1['ufullname'];										
													
				}
				
			
				
				
				
				
				$imstatus="<a href='coachinginquirydetail.php?id=".$row['stiqmid']."'   data-toggle='tooltip' data-placement='top' title='Delete' class='far fa-eye fa-lg'> </a>";	
				
				 // $op= $imstatus."&nbsp;&nbsp;&nbsp; ".$pidedit."&nbsp;&nbsp;&nbsp; ".$piddelete;
				  $op= $imstatus;
				  
				  
				  $inquirydate=date("d-m-Y",strtotime($row['inquirydate']));
				  $status=$row['status'];
					$sistatus=$row['sistatus'];
					$siprefferedcountry=$row['siprefferedcountry'];
						$eveag1 = "select * from res_country_master where countrymid=$siprefferedcountry";
																$reag1 = mysqli_query($conn, $eveag1);
																if(mysqli_num_rows($reag1) > 0)
																{
																	while($rtag1 = mysqli_fetch_assoc($reag1))
																	{
																		$countryname=$rtag1['countryname'];
																	}
																}
																else
																{
																	$countryname="";
																}
																
																$siprefferservice=$row['siprefferservice'];
																$siprefferedsubservice=$row['siprefferedsubservice'];
																			$subservicename ="";
																			 $eves = "select * from res_subservice_master where subservicemid = $siprefferedsubservice ";
																			$res = mysqli_query($conn, $eves);
																			
																			while($rts = mysqli_fetch_assoc($res))

																			{
																				$subservicename = $rts['subservicename'];
																			}
																
										
												
																
				 
    $data[] = array(
    		"imid"=>$i++,
    		"idate"=>$idate,
    		"inqbranch"=>$ufullname1,
    		"ipname"=>$ipname1,
    		"imstatus"=>$imstatus2,
    		"ipemail"=>$ipemail,
    		"ipmobile"=>$ipmobile,
    		"entrybyuserid"=>$entrybyuserid1,
    		"counsellorid"=>$tt1,
			"ipaddedon"=>$ipaddedon,
			"agentid"=>$agentname,
			"siprefferedcountry"=>$countryname,
			"siprefferservice"=>$siprefferservice,
			"siprefferedsubservice"=>$subservicename,
    		"op"=>$op
    		
			

				
			
    	);
}

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

echo json_encode($response);
