<?php 

require_once "config.php";

/*********************/
/********** GET CITY CLICK STATE ***********/
/*********************/
// ini_set('display_errors', 1);
// ini_set('display_startup_errors', 1);
// error_reporting(E_ALL);
if (!isset($_SESSION)) {
    session_start();
}
if( isset($_POST['action']) && $_POST['action'] == 'getStateData' ){

  $html = '';
  $getstates = "SELECT * FROM states WHERE country_id =".$_POST['country_id']." ORDER BY name ASC";
  $sqlstates = mysqli_query($mysqli,$getstates);
  $html .='<option value="">STATE</option>';
  if($sqlstates->num_rows > 0){
     while($row = mysqli_fetch_assoc($sqlstates)){
      $html .= "<option value=".$row['id'].">".$row['name']."</option>";
    }
  }
  echo json_encode(array('html' => $html));
 exit;
}

if( isset($_POST['action']) && $_POST['action'] == 'getCityData' ){

  $html = '';
  $getcities = "SELECT * FROM cities WHERE state_id =".$_POST['state_id']." ORDER BY name ASC";
  $sqlcities = mysqli_query($mysqli,$getcities);
  $html .='<option value="">CITY</option>';
  if($sqlcities->num_rows > 0){
     while($row = mysqli_fetch_assoc($sqlcities)){
      // print_r($row);
      $html .= "<option value=".$row['id'].">".$row['name']."</option>";
    }
  }
  echo json_encode(array('html' => $html));
 exit;
}

function getUserData($mysqli,$state = '', $city = '')
{
  if (($state != '' || $state != 0) && ($city == 0)) {
    $getusers = "SELECT * FROM users WHERE state = '".trim($state)."' AND total_survey_number > 0";
  }else{
    $getusers = "SELECT * FROM users WHERE state = '".trim($state)."' AND city = '".trim($city)."' AND total_survey_number > 0";
  }
  $homelatlonquery = mysqli_query($mysqli,$getusers);

  
  $geojson = array( 'type' => 'FeatureCollection', 'features' => array());

  while($row = mysqli_fetch_assoc($homelatlonquery)){

    $marker = array(
      'type' => 'Feature',
      'properties' => array(
        'title' => $row['username'],
        'color' => $row['color'],
        'marker-size' => 'small'
      ),
      'geometry' => array(
        'type' => 'Point',
        'coordinates' => array(
          $row['homelon'],
          $row['homelat']
        )
      )
    );
    array_push($geojson['features'], $marker);
  }
  return $geojson;
}

if( isset($_POST['action']) && $_POST['action'] == 'get_city_data' ){

  $arrcsc   = array('mysqli'=>$mysqli, 'state' => $_POST['id']);
  $getCTCdata = getCountryStateCity($arrcsc);
  $geojson =   getUserData($mysqli,$_POST['id'],'0');
  $getcities = "SELECT s.id AS s_id, AVG(s.score) as avg_score,c.state_id, c.id AS c_id,c.name AS c_name FROM scores AS s RIGHT JOIN cities AS c 
    ON c.state_id=s.state_id 
    AND c.id=s.city_id
    WHERE c.state_id = {$_POST['id']}
    GROUP BY s.city_id 
    HAVING avg_score > 0 ORDER BY avg_score DESC , c_name ASC";
    // print_r($getcities);
    // exit;
  
  $getcities = mysqli_query($mysqli,$getcities);
  $html = '';
  $html .= '<section class="city-ranking"><div class="container-fluid">
    <div class="row justify-content-center ">
      <div class="col-md-3">
            <div class="city-ranking-box">
              <div class="cr-title">
                <h3>CITY RANKINGS</h3>
              </div>
              <div class="cr-search">
                <div class="cr-search-con">
                <input type="text" class="form-control" name="searchcity" id="searchcity" placeholder="Search..." aria-label="Search" aria-describedby="basic-addon1">
			           <input type="hidden" name="srcstatename" id="srcstatename" value="'.$_POST['id'].'">
                <i></i>
                </div>
              </div>
              <div class="cr-listing" id="style-2">
                <ul id="src-tbl-city">'; 
  if($getcities->num_rows > 0){
  	$x = 1;
     while($row = mysqli_fetch_assoc($getcities)){
      // $avg = (float)number_format((float)$row['avg_score'], 2 , '.', '');
      $avg = floatValueScore($row['avg_score']);
      $html .='<li><div class="cr-listing-text"><div class="cr-listing-text-col-1"><a href="javascript:void(0);" class="srccity" id="srccity" searchcity="'.$row['c_id'].'" searchstate="'.$row['state_id'].'" ><span class="st-county-name">'.$x.'. <span>'.$row['c_name'].'</span></a></div> <div class="cr-listing-text-col-2"><span>'.$avg.'</span> </div> </div> </li>';
      $x++;
    }
  }else{
     $html .= '<tr>No record Found.</tr>'; 
  }
  
  $html .= '</ul></div></div></div></div></div></section>';
  echo json_encode(array('html' => $html, 'state' => $getCTCdata['state_name'],'country' => $getCTCdata['country_name'], 'geojson' => $geojson));
 exit;
}

/*********************/
/********** SEARCH CITY IN INDEX.PHP ***********/
/*********************/

if( isset($_POST['action']) && $_POST['action'] == 'search_city' ){

  $sqlstate = "SELECT * FROM states WHERE id =".$_POST['state'];
  $getstate = mysqli_query($mysqli,$sqlstate);
  $statenm = mysqli_fetch_assoc($getstate);

	if(strlen($_POST['name']) > 0){
	
    $getcities = "SELECT s.id AS s_id, AVG(s.score) as avg_score,c.state_id, c.id AS c_id,c.name AS c_name FROM scores AS s RIGHT JOIN cities AS c 
    ON c.state_id = s.state_id
    AND c.state_id = '{$_POST['state']}'
    AND c.name LIKE '%{$_POST['name']}%'
    AND s.city_id = c.id
    AND c.id != ''
    GROUP BY s.city_id 
    HAVING avg_score > 0 ORDER BY avg_score DESC , c_name ASC";

		 $getcities = mysqli_query($mysqli,$getcities);
		 $html = '';
		  if($getcities->num_rows > 0){
		  	$x = 1;
		     while($row = mysqli_fetch_assoc($getcities)){
          // $avg = (float)number_format((float)$row['avg_score'], 2 , '.', '');
          $avg = floatValueScore($row['avg_score']);
          // $geojsoncity =   getUserData($mysqli,$_POST['state'],$_POST['name']);
          if($row['c_id'] != ''){
  		      $html .= '<li><div class="cr-listing-text"> <div class="cr-listing-text-col-1" ><a href="javascript:void(0);" class="srccity" id="srccity" searchcity="'.$row['c_id'].'" searchstate="'.$row['state_id'].'" ><span class="st-county-name" id='.$row['c_id'].'>'.$x.'. <span>'.$row['c_name'].'</span></a></div> <div class="cr-listing-text-col-2"><span>'.$avg.'</span> </div> </div> </li>'; 
  		      $x++;
          }
		    }
		  }else{
		     $html .= '<li><div class="cr-listing-text"> <div class="cr-listing-text-col-1">No record found.</div> <div class="cr-listing-text-col-2"><span></span> </div> </div> </li>'; 
		  }
	}else{
		$getcities = "SELECT s.id AS s_id, AVG(s.score) as avg_score,c.state_id, c.id AS c_id,c.name AS c_name FROM scores AS s RIGHT JOIN cities AS c 
    ON c.state_id = s.state_id
    AND c.state_id = '{$_POST['state']}'
    -- AND c.name LIKE '%{$_POST['name']}%'
    AND s.city_id = c.id
    AND c.id != ''
    GROUP BY s.city_id 
    HAVING avg_score > 0 ORDER BY  avg_score DESC , c_name ASC";

		 $getcities = mysqli_query($mysqli,$getcities);
		 $html = ''; 
		  if($getcities->num_rows > 0){
		  	$x = 1;
		     while($row = mysqli_fetch_assoc($getcities)){
          if($row['c_id'] != ''){
            // $avg = (float)number_format((float)$row['avg_score'], 2 , '.', '');
            $avg = floatValueScore($row['avg_score']);
  		      $html .= '<li><div class="cr-listing-text"> <div class="cr-listing-text-col-1"><a href="javascript:void(0);" class="srccity" id="srccity" searchcity="'.$row['c_id'].'" searchstate="'.$row['state_id'].'" ><span class="st-county-name">'.$x.'. <span>'.$row['c_name'].'</span></a></div> <div class="cr-listing-text-col-2"><span>'.$avg.'</span> </div> </div> </li>'; 
  		      $x++;
          }
		    }
		  }else{
		     $html .= '<li><div class="cr-listing-text"> <div class="cr-listing-text-col-1">No record found.</div> <div class="cr-listing-text-col-2"><span></span> </div> </div> </li>'; 
		  }
	}
  echo json_encode($html);
 exit;
}

/*********************/
/********** SEARCH CITY IN INDEX.PHP ***********/
/*********************/

if( isset($_POST['action']) && $_POST['action'] == 'click_city_servay' ){
  $geojson =   getUserData($mysqli,$_POST['state'],$_POST['name']);
  $arrcsc   = array('mysqli'=>$mysqli, 'city' => $_POST['name']);
  $getCTCdata = getCountryStateCity($arrcsc);
 
  echo json_encode(array('country'=> $getCTCdata['country_name'],'state'=> $getCTCdata['state_name'],'city'=> $getCTCdata['city_name'],'geojson' => $geojson));
 exit;
}


if( isset($_POST['action']) && $_POST['action'] == 'Reset_password' ){
    
  if(($_POST['forgotid']) > 0){
    $password = password_hash($_POST['password'], PASSWORD_DEFAULT); // Creates a password 
    $sql = "UPDATE `users` SET `password` = '".$password."' WHERE `users`.`id` = ".$_POST['forgotid'];  
    
      if (mysqli_query($mysqli, $sql)) {
        $data = "yes";
        $_SESSION['success_msg_time'] = time();
        $_SESSION['success_msg'] = 'Password changed successfully.';
      } 
      mysqli_close($mysqli); 
    }
  echo json_encode($data);
 exit;
}

if( isset($_POST['action']) && $_POST['action'] == 'check_user_name' ){
    $data = 0;
  if($_POST['username'] != '' ){
      $sqlusername = "SELECT * FROM users WHERE username = '".trim($_POST['username'])."'";
      $getusername = mysqli_query($mysqli,$sqlusername);
      // print_r($getusername);
      if ($getusername->num_rows != 0) {
        $data = 1;
      }
      mysqli_close($mysqli); 
    }
  echo json_encode($data);
 exit;
}

if( isset($_POST['action']) && $_POST['action'] == 'check_user_email' ){
    $data = 0;
  if($_POST['email'] != '' ){
      $sqlusername = "SELECT * FROM users WHERE email = '".trim($_POST['email'])."'";
      $getusername = mysqli_query($mysqli,$sqlusername);
      // print_r($getusername);
      if ($getusername->num_rows != 0) {
        $data = 1;
      }
      mysqli_close($mysqli); 
    }
  echo json_encode($data);
 exit;
}
?>
