<?php

/*********************/
/********** Get Country State City Name ***********/
/*********************/
function getCountryStateCity($filter = array()) {
  $mysqli = $filter['mysqli'];
  $return = array();
  $return['country_name'] = "";
  $return['state_name'] = "";
  $return['city_name'] = "";
  if(isset($filter['country']) && $filter['country'] != "")   {
    $sqlcountry   = "SELECT `name` FROM `countries` WHERE `id` = ".$filter['country']." ";
    $country      = mysqli_query($mysqli,$sqlcountry);
    $getcountry   = mysqli_fetch_assoc($country);
    $return['country_name'] = $getcountry['name'];
  }
  if(isset($filter['state']) && $filter['state'] != "")   {
    $sqlstate   = "SELECT s.name AS s_name,c.name AS c_name FROM states AS s LEFT JOIN countries AS c ON s.country_id = c.id WHERE s.id = ".$filter['state'];
  
    $state      = mysqli_query($mysqli,$sqlstate);
    $getstate   = mysqli_fetch_assoc($state);
    $return['state_name']   = $getstate['s_name'];
    $return['country_name'] = $getstate['c_name'];
  }
  if(isset($filter['city']) && $filter['city'] != "")   {
    
    $sqlcity   = "SELECT s.name AS s_name,c.name AS c_name, cities.name AS city_name FROM cities LEFT JOIN states AS s ON cities.state_id = s.id LEFT JOIN countries AS c ON s.country_id = c.id WHERE cities.id =".$filter['city'];
    $city      = mysqli_query($mysqli,$sqlcity);
    $getcity   = mysqli_fetch_assoc($city);
    $return['country_name'] = $getcity['c_name'];
    $return['state_name']   = $getcity['s_name'];
    $return['city_name']    = $getcity['city_name'];
  }
  return $return;
}

function getAvgCountryStateCity($filter = array()) {

  $mysqli = $filter['mysqli'];
  $return = array();
  $return['country_avg']  = "0";
  $return['state_avg']    = "0";
  $return['city_avg']     = "0";
  if(isset($filter['country']) && $filter['country'] != "")   {
    $csc_id     = $filter['country'];
    $countryavg = array();
    $sqlquery   = "SELECT `score` FROM `scores` WHERE `country_id` = ".$csc_id;
    $country    = mysqli_query($mysqli,$sqlquery);
    if($country->num_rows > 0){
      while ($row = mysqli_fetch_assoc($country)) {
        $countryavg[] = $row['score'];
      }  
      $return['country_avg'] = array_sum($countryavg)/count($countryavg); 
    }
  }
  
  if(isset($filter['state']) && $filter['state'] != "")   {
    $csc_id       = $filter['state'];
    $stateavg     = array();
    $sqlquery   = "SELECT `score` FROM `scores` WHERE `state_id` = ".$csc_id;
    $state        = mysqli_query($mysqli,$sqlquery);
    if($state->num_rows > 0){
      while ($row   = mysqli_fetch_assoc($state)) {
        $stateavg[] = $row['score'];
      }
      $return['state_avg'] = array_sum($stateavg)/count($stateavg);
    }
  }

  if(isset($filter['city']) && $filter['city'] != "")   {
    $csc_id     = $filter['city'];
    $cityavg    = array();
    $sqlquery = "SELECT `score` FROM `scores` WHERE `city_id` = ".$csc_id;
    $city       = mysqli_query($mysqli,$sqlquery);
    if($city->num_rows > 0){
      while ($row = mysqli_fetch_assoc($city)) {
        $cityavg[]= $row['score'];
      }
      $return['city_avg'] = array_sum($cityavg)/count($cityavg);
    }
  }
  return $return;
}

function floatValueScore($digit){
  $d = explode('.', $digit);
  $val1 = $d[0];
  $val2 = substr($d[1], 0, 2);
  // echo $val1.'.'.$val2
  if($val2 == '00'){
    return rtrim($val1, ".");
  }else{
    return rtrim($val1.'.'.$val2, ".");
  }
}