#!/usr/local/php5/bin/php -q 
<?php
function arr_ge($array, $key, $val_if_not_found=null) {
    return is_array($array) && isset($array[$key]) ? $array[$key] : $val_if_not_found;
}
class GoogGeocode {
    private $api_key = "** YOUR GOOGLE API KEY **";
    private $request_template = "http://maps.google.com/maps/geo?q={address}&output={output_format}&oe=utf8&sensor=false&key={key}";

    private $db_connection;

    public function __construct() {
        $this->db_connection = $this->db_connect();
    }
    public function process() {
        $this->make_api_call($this->get_establishments());
    }
    private function make_api_call($addresses=array(), $output_format = 'json') {
        $run_date = date('Y-m-d H:i:s');
        echo "making [".count($addresses)."] API calls\n\n";
        $addresses = is_array($addresses)?$addresses:array($addresses);
        $request_uri = str_replace(array('{output_format}','{key}'),array($output_format,$this->api_key),$this->request_template);
        foreach ($addresses as $address) {
            sleep(2);
            $this_uri = str_replace('{address}',urlencode('Oregon Health and Science University OHSU Hospital & Clinics, '.$address['full_address']),$request_uri);
            $result = json_decode($this->get($this_uri),true);
            if(isset($result['Placemark'][0])) {

                $place = $result['Placemark'][0];

                $db_inserts = array(
                    'mchd_id'=>$address['mchd_id'],
                    'processed_date'=>$run_date,
                    'address'=>$place['address'],
                    'country_name_code'=>$place['AddressDetails']['Country']['CountryNameCode'],
                    'country_name'=>$place['AddressDetails']['Country']['CountryName'],
                    'area_name'=>$place['AddressDetails']['Country']['AdministrativeArea']['AdministrativeAreaName'],
                    'locality_name'=>$place['AddressDetails']['Country']['AdministrativeArea']['Locality']['LocalityName'],
                    'thoroughfare_name'=>$place['AddressDetails']['Country']['AdministrativeArea']['Locality']['Thoroughfare']['ThoroughfareName'],
                    'postal_code'=>$place['AddressDetails']['Country']['AdministrativeArea']['Locality']['PostalCode']['PostalCodeNumber'],
                    'accuracy'=>$place['AddressDetails']['Accuracy'],
                    'lat_lon_box_n'=>$place['ExtendedData']['LatLonBox']['north'],
                    'lat_lon_box_s'=>$place['ExtendedData']['LatLonBox']['south'],
                    'lat_lon_box_e'=>$place['ExtendedData']['LatLonBox']['east'],
                    'lat_lon_box_w'=>$place['ExtendedData']['LatLonBox']['west'],
                    'latitude'=>$place['Point']['coordinates'][1],
                    'longitude'=>$place['Point']['coordinates'][0]
                );
                $query = $this->build_insert_query($db_inserts);
                $this->db_connection->query($query);
            } else {
                echo "No result for mchd[{$address['mchd_id']}]\n".print_r($result,1);
            }
        }
    }


    private function get_establishments() {
        $query = "SELECT `mchd_id`, CONCAT_WS(', ',`address`,`city`,'OR',`postal_code`) AS `full_address` FROM `establishments`
            WHERE `address` != '' AND `address` IS NOT NULL ORDER BY `mchd_id`";
        $query = "SELECT `mchd_id`, CONCAT_WS(', ',`address`,`city`,'OR',`postal_code`) AS `full_address` FROM `establishments`
            WHERE `mchd_id` IN ('26010737')";
        // ('26004006','26008574','26008816','26009225','26009253','26010075','26010315','26010366','26010504','26010626','26010720','26010737','26010841','26011484','261231','263054','263238','264177','264572','264703','265253','265298','265383','265447','265456','265461','265471','265492','265590','265623','265639','265672','265722')
        $result = $this->db_connection->query($query);
        while ($establishments[] = mysqli_fetch_array($result,MYSQLI_ASSOC)) {}
        return $establishments;
    }

    private function get($url, $authenticate=false) {
        var_dump($url);
        $handle = curl_init();    // initialize curl handle
        curl_setopt($handle, CURLOPT_URL, $url); // set url to post to
        curl_setopt($handle, CURLOPT_FAILONERROR, 1);
        curl_setopt($handle, CURLOPT_HEADER, 0);
        curl_setopt($handle, CURLOPT_FOLLOWLOCATION, 1);// allow redirects
        curl_setopt($handle, CURLOPT_RETURNTRANSFER,1); // return into a variable
        curl_setopt($handle, CURLOPT_TIMEOUT, 3); // times out after 4s
        if ($authenticate) {
            if (!empty($this->credentials['username'])&&!empty($this->credentials['password'])) {
                curl_setopt($handle,CURLOPT_USERPWD,$this->credentials['username'] . ":" . $this->credentials['password']);
            } else {
                curl_close($handle);
                throw new Exception("Authentication Credentials Not set".__METHOD__);
            }
        }
        $result = curl_exec($handle); // run the whole process
        $this->response_info = curl_getinfo($handle);
        curl_close($handle);
        return $result;
    }
    private function db_connect() {
        $mysqli = mysqli_connect( 'localhost', 'test', 'test', 'mchd' );
        /* check connection */
        if (mysqli_connect_errno()) {
            printf ( "Connect failed: %s\n", mysqli_connect_error () );
            exit ();
        }
        return $mysqli;
    }
    private function build_insert_query($insert_elements) {
        $query = "INSERT `establishment_geo_data` (`".implode('`, `',array_keys($insert_elements))."` ) VALUES(";
        foreach ($insert_elements as &$element) {
            $element = mysqli_real_escape_string($this->db_connection, $element);
        }
        $query .= "'".implode("', '", $insert_elements)."')";
        return $query;
    }
}
$geocoder = new GoogGeocode();
$geocoder->process();