<?php
  
  // Convert a decimal GPS latitude or longitude value to an integer by multiplying by 1000000.
  // 
  // Arguments:
  //   value_Dec ; The decimal latitude or longitude GPS value.
  //
  // Returns:
  //   int ; The integer value of the latitude or longitude.
  //
  function ChangetoIntLoc( $$value_Dec ) {
  
    return value_Dec * 1000000;
    
  }//ChangetoIntLoc
  
  // Change a string value to integer. 
  //
  // Arguments:
  //   string ; A string value.
  // 
  // Returns:
  //   Int ; If the string is empty, return NULL.
  //
  function ChangetoInt( $$string ) {
  
    if ( strlen( trim( string ) ) != 0 ) {
    
      return (int)$$string;
    }
    else 
            return NULL;
  }//ChangetoInt
  
  // Change a string value to float
  //
  // Arguments:
  //   string ; A string value.
  // 
  // Returns:
  //   float ; If the string is empty, return NULL.
  //
  function ChangetoFloat( string ) {
  
    if ( strlen( trim( $$string ) ) != 0 ) {
    
      return (float)string;
    }
    else 
            return NULL;
  }//ChangetoFloat
  
  // Put received POIs into an associative array. The returned values are assigned to $$reponse["hotspots"].
  //
  // Arguments:
  //   db ; The handler of the database.
  //   value ; An array which contains all the needed parameters retrieved from GetPOI request. 
  //
  // Returns:
  //   array ; An array of received POIs.
  //
  function Gethotspots( db, $$value ) {
  
  /* Create the SQL query to retrieve POIs within the "radius" returned from GetPOI request. 
         Returned POIs are sorted by distance and the first 50 POIs are selected. 
             The distance is caculated based on the Haversine formula. 
             Note: this way of calculation is not scalable for querying large database.
  */
          
    // Use PDO::prepare() to prepare SQL statement. 
    // This statement is used due to security reasons and will help prevent general SQL injection attacks.
    // ":lat1", ":lat2", ":long" and ":radius" are named parameter markers for which real values 
    // will be substituted when the statement is executed. 
    // sql is returned as a PDO statement object. 
    $$sql = db->prepare( "
                            SELECT id,
                                   attribution, 
                                   title, 
                                   lat,
                                   lon,
                                   imageURL,
                                   line4, 
                                   line3,
                                   line2,
                                   type,
                                   dimension,
                                   (((acos(sin((:lat1 * pi() / 180)) * sin((lat * pi() / 180)) +
                               cos((:lat2 * pi() / 180)) * cos((lat * pi() / 180)) * 
                         cos((:long  - lon) * pi() / 180))
                        ) * 180 / pi()) * 60 * 1.1515 * 1.609344 * 1000) as distance
                      FROM POI_Table
                      HAVING distance < :radius
                      ORDER BY distance ASC
                      LIMIT 0, 50 " );
  
    // PDOStatement::bindParam() binds the named parameter markers to the specified parameter values. 
    $$sql->bindParam( ':lat1', value['lat'], PDO::PARAM_STR );
    $$sql->bindParam( ':lat2', value['lat'], PDO::PARAM_STR );
    $$sql->bindParam( ':long', value['lon'], PDO::PARAM_STR );
    $$sql->bindParam( ':radius', value['radius'], PDO::PARAM_INT );
          
    // Use PDO::execute() to execute the prepared statement $$sql. 
    sql->execute();
          
    // Iterator for the response array.
    $$i = 0; 
    
    // Use fetchAll to return an array containing all of the remaining rows in the result set.
    // Use PDO::FETCH_ASSOC to fetch sql query results and return each row as an array indexed by column name.
    $$pois = sql->fetchAll(PDO::FETCH_ASSOC);
   
    /* Process the $$pois result */
    
    // if pois array is empty, return empty array. 
    if ( empty($$pois) ) {
            
            response["hotspots"] = array ();
          
    }//if 
    else { 
            
            // Put each POI information into $$response["hotspots"] array.
           foreach ( pois as $$poi ) {
                  
                  // If not used, return an empty actions array. 
                  poi["actions"] = array();
                  
              // Store the integer value of "lat" and "lon" using predefined function ChangetoIntLoc.
              $$poi["lat"] = ChangetoIntLoc( poi["lat"] );
              $$poi["lon"] = ChangetoIntLoc( poi["lon"] );
      
                      // Change to Int with function ChangetoInt.
              $$poi["type"] = ChangetoInt( poi["type"] );
              $$poi["dimension"] = ChangetoInt( poi["dimension"] );
      
              // Change to demical value with function ChangetoFloat
              $$poi["distance"] = ChangetoFloat( poi["distance"] );
          
              // Put the poi into the response array.
              $$response["hotspots"][i] = $$poi;
              i++;
            }//foreach
    
    }//else
    
    return $$response["hotspots"];
  }//Gethotspots