#!/usr/bin/perl -w
use strict;

use DBI;
use Google::Directions::Client;

my $hostname = "localhost";
my $db = "sugarcrm";

my $dsn = "dbi:mysqlPP:database=$db;host=$hostname";
my $dbh = DBI->connect($dsn, "root", "skynet") or die 'CANT CONNECT!!!';
my $drh = DBI->install_driver("mysqlPP");

#CREATE AND EXECUTE QUERY   
my  $sth = $dbh->prepare("SELECT p.id, p.primary_address_street address, p.primary_address_city city, p.primary_address_state state, p.primary_address_postalcode zip
  FROM prospects p LEFT JOIN prospects_cstm pc ON p.id = pc.id_c
WHERE deleted <> 1 AND pc.addr_status_c is null 
                                 limit 0, 1000;");
$sth->execute;

my ($id,$address1,$city,$prov,$postalcode,$dealer_id);
my $count = 0;

#BIND TABLE COLUMNS TO VARIABLES
$sth->bind_columns(\$id,\$address1,\$city,\$prov,\$postalcode);

while($sth->fetch()) {
    my $goog = Google::Directions::Client->new();

        my $response = $goog->directions(
            destination      => $address1 . ',' . $city . ',' . $prov . ',' . $postalcode,
            origin => '467 W. Shaw Fresno, CA 93724',
            );
    
    if ($response->status eq 'OVER_QUERY_LIMIT') {
     die "Error: OVER_QUERY_LIMIT";
    }      
    unless ($response->status eq 'OK') {
    my $upd = $dbh->prepare("UPDATE prospects_cstm SET addr_status='NOT_FOUND'");
    $upd->execute;
    
     print "Count: " . $count++ . "\t" . "ID: " . $id . "\t" . "Status: " . $response->status . "\n";
     
    next;
    }
    
    my $distance =  sprintf "%.2f", $response->routes->[0]->legs->[0]->distance / 1000;
    my $duration =  sprintf "%.2f", $response->routes->[0]->legs->[0]->duration / 60;
    
    my $upd = $dbh->prepare("UPDATE prospects_cstm SET distance_c='" . $distance . "', duration_c=" . $duration . ", addr_status_c='" . $response->status  . "', lat_c=" .
                            $response->routes->[0]->legs->[0]->end_location->lat . ", lng_c=" . $response->routes->[0]->legs->[0]->end_location->lng . " WHERE id_c='" . $id . "'");
    $upd->execute;
    
    print "Count: " . $count++ . "\t" . "ADDRESS: " . $address1 . "\t" . "CITY: " . $city . "\t" . "STATE :" . $prov . "\t" .  "Duration: " . $duration . "\t" . "Distance: " . $distance . "\t" . "Status: " . $response->status . "\n";
}

$sth->finish;