<?php
header("Access-Control-Allow-Origin: *");
header('Content-Type: application/json');
include "config.php";

$getInstQuery="SELECT inst.id, inst.institutions_name, inst.category_id, icat.category_name, inst.state_id, s.state_name, s.image_icon,  inst.city_id, c.city, inst.address, inst.pincode, inst.official_website_link, inst.online_apply_link, inst.online_fee_link, inst.result_link, inst.other_info FROM sp_institutions AS inst LEFT JOIN sp_institutions_category AS icat ON icat.id=inst.category_id LEFT JOIN sp_states AS s on s.state_id = inst.state_id LEFT JOIN sp_city AS c ON c.city_id=inst.city_id where inst.status='1' order by inst.display_order";

$sqlInstQuery=$db->prepare($getInstQuery);
$sqlInstQuery->execute();

$instDataArray = array();
while($rowInstQuery=$sqlInstQuery->fetch()){
	$instArray = array("id"=>$rowInstQuery['id'], "institutions_name"=>$rowInstQuery['institutions_name'], "category_id"=>$rowInstQuery['category_id'], "category_name"=>$rowInstQuery['category_name'], "state_id"=>$rowInstQuery['state_id'], "state_name"=>$rowInstQuery['state_name'], "state_image"=>$rowInstQuery['image_icon'], "city_id"=>$rowInstQuery['city_id'], "city_name"=>$rowInstQuery['city'], "address"=>$rowInstQuery['address'], "pincode"=>$rowInstQuery['pincode'], "official_website_link"=>$rowInstQuery['official_website_link'], "online_apply_link"=>$rowInstQuery['online_apply_link'], "online_fee_link"=>$rowInstQuery['online_fee_link'], "result_link"=>$rowInstQuery['result_link'], "other_info"=>$rowInstQuery['other_info']);
	$instDataArray[]=$instArray;
}

$getInstCatQuery="SELECT inst.category_id, count(inst.id) as count, ic.category_name FROM sp_institutions as inst INNER JOIN sp_institutions_category as ic ON ic.id=inst.category_id WHERE inst.status='1' group by inst.category_id order by ic.display_order ";	

	$sqlInstCat=$db->prepare($getInstCatQuery);
	$sqlInstCat->execute();

	$instCatDataArray = array();
	while($rowInstCat=$sqlInstCat->fetch()){	
		$category_id= $rowInstCat['category_id'];
		$inst_count= $rowInstCat['count'];
		$category_name = $rowInstCat['category_name'];
		
		$instCatArray=array("category_id"=>$category_id, "category_name"=>$category_name, "inst_count"=>$inst_count);
		$instCatDataArray[]=$instCatArray;
	}

$getInstStateQuery="SELECT inst.state_id, count(inst.id) as count, s.state_name, s.image_icon, s.state_slug FROM sp_institutions as inst INNER JOIN sp_states as s ON s.state_id=inst.state_id WHERE inst.status='1' group by inst.state_id order by s.state_name";	

	$sqlInstState=$db->prepare($getInstStateQuery);
	$sqlInstState->execute();

	$instStateDataArray = array();
	while($rowInstState=$sqlInstState->fetch()){	
		$state_id= $rowInstState['state_id'];
		$inst_count= $rowInstState['count'];
		$state_name = $rowInstState['state_name'];
		$state_slug = $rowInstState['state_slug'];
		$state_image = $rowInstState['image_icon'];
		
		$instStateArray=array("state_id"=>$state_id, "state_name"=>$state_name, "inst_count"=>$inst_count, "state_slug"=>$state_slug, "state_image"=>$state_image);
		$instStateDataArray[]=$instStateArray;
	}
	
$mainDataArray=array("institute_data"=>$instDataArray, "category_data"=>$instCatDataArray, "state_data"=>$instStateDataArray);	

echo json_encode($mainDataArray);

?>