<?php
/*
* date last modified 03-10-2017
* Edited by PI-Digi Logical Solutions
* HTML Files - manage_staff.html.php
* Mysql Tables - tbl_admin, tbl_user, tbl_admin_type
*/
require_once('./includes/functions.php'); # general functions

$debug = true;

// Get All task details to list 
$where = " status != 'temp' ";
$categoryId = $workFrom = $workTo = $error = '';
$catParentRs = mysqli_query($con, "SELECT id, name FROM tbl_service_categories WHERE parent_id = '0' AND status = 'Active' ORDER BY name");
 
if(isset($_POST['place']) && $_POST['place']!=''){			
		$location = trim($_POST['place']);
		$locationArray = explode(',', $location);
		$location = $locationArray[0];
		if($location!=''){
			//SELECT * FROM tbl_job WHERE  id>0  AND ((place = 'Attingal') OR (address_id > 0 AND address_id IN (SELECT id FROM tbl_user_address WHERE place LIKE 'Attingal%')) OR (address_id = 0 AND user_id IN (SELECT id FROM tbl_user WHERE place LIKE 'Attingal%')))  ORDER BY id DESC LIMIT 30
			//firts check in tbl_job. else if address id >0 find in tbl_user address. else if address id = 0, find in tbl_user
			$where.= " AND ((place = '".$location."') OR (address_id > 0 AND address_id IN (SELECT id FROM tbl_user_address WHERE place LIKE '".$location."%')) OR (address_id = 0 AND user_id IN (SELECT id FROM tbl_user WHERE place LIKE '".$location."%'))) ";	
		} 							
} else if(isset($_POST['city']) && $_POST['city']!=''){
	$city = trim($_POST['city']);
	$cityRec = mysqli_fetch_array(mysqli_query($con, "SELECT latitude, longitude FROM tbl_city WHERE id = '".$city."'"));
	$latitude = $cityRec['latitude'];
	$longitude = $cityRec['longitude'];
} else {
	$latitude = 8.5241391;
	$longitude = 76.9366376;
}

//get cities
$citiesRs = mysqli_query($con, "SELECT id, name FROM tbl_city ORDER BY name");

if(isset($_REQUEST['city']) && strlen($_REQUEST['city'])>0) {
	$city = trim($_REQUEST['city']);
	//$where.= " AND city = '".$city."' ";
	$where.= " AND (city = '".$city."') OR (address_id>0 AND address_id IN (SELECT id FROM tbl_user_address WHERE city = '".$city."')) ";
} 


if(isset($_REQUEST['category']) && strlen($_REQUEST['category'])>0) {
	$categoryId = trim($_REQUEST['category']);	
	$where.= " AND category_id = '".$categoryId."' ";
} 
if(isset($_POST['search'])){
	//print_r($_POST); exit;
	if(isset($_REQUEST['category']) && $_REQUEST['category']!=''){
		$categoryId = trim($_REQUEST['category']);		
	} 
		
	if(isset($_REQUEST['work_from']) && $_REQUEST['work_from']!=''){
		$workFrom = trim($_REQUEST['work_from']);		
	} 
	if(isset($_REQUEST['work_to']) && $_REQUEST['work_to']!=''){
		$workTo = trim($_REQUEST['work_to']);		
	} else {
		$workTo = date('Y-m-d');
	}
	
	if($categoryId!=''){
		$where.= " AND category_id = '".$categoryId."' ";
	}
	if($workFrom!='' && $workTo!=''){
		$where.= " AND (DATE(task_date) BETWEEN '".$workFrom."' AND '".$workTo."') ";
	}
}



$taskArray = array();
$i = 0;

if(empty($error)){
	//ok
} else {
	$latitude = 8.5241391;
	$longitude = 76.9366376;
}

$select = "SELECT * FROM tbl_job WHERE ".$where." ORDER BY id DESC LIMIT 30";
$taskRs = mysqli_query($con, $select);
if(mysqli_num_rows($taskRs)>0){
	while($taskRec = mysqli_fetch_array($taskRs)){
		$latitude = $longitude = '';
		$userId = $taskRec['user_id'];
		//get task location
		$latitude = $taskRec['latitude'];
		$longitude = $taskRec['longitude'];
		if($latitude!='' && $longitude!=''){
			//ok use the location
						
		} else {
			//find location from address id
			$addressId = $taskRec['address_id'];
			if($addressId>0){
				$addressRs = mysqli_query($con, "SELECT latitude, longitude FROM tbl_user_address WHERE id = '".$addressId."'");
				if(mysqli_num_rows($addressRs)>0){
					$addressRec = mysqli_fetch_array($addressRs);					
					$latitude = $addressRec['latitude'];
					$longitude = $addressRec['longitude'];
				} else {
					//$error = 'The task location has been removed';
				}
			} else {
				//use profile address
				$userRs = mysqli_query($con, "SELECT latitude, longitude FROM tbl_user WHERE id = '".$userId."'");
				if(mysqli_num_rows($userRs)>0){
					$userRec = mysqli_fetch_array($userRs);					
					$latitude = $userRec['latitude'];
					$longitude = $userRec['longitude'];
				} else {
					//$error = 'The user has been removed';
				}
			}
		}
		
		
		if($latitude!='' && $longitude!=''){
			$taskArray[$i]['id'] = $taskRec['id'];
			$taskArray[$i]['latitude'] = $latitude;
			$taskArray[$i]['longitude'] = $longitude;
			$taskArray[$i]['user'] = getUserName($userId);
			$taskArray[$i]['latitude'] = $latitude;
			$taskArray[$i]['longitude'] = $longitude;
			$taskArray[$i]['category'] = getCategory($taskRec['category_id']);
			$taskArray[$i]['task_date'] = $taskRec['task_date'];		
			$i++;
		}
	}
}

//print_r($taskArray);
?>