TOPIC:

$this -> execPieceByName('ff_InitLib');

use Joomla\CMS\Log\Log;

$db = JFactory::getDbo();

// Get the record ID being worked on
$uri = $this->record_id;

// Prepare addresses to pass to Geocode API
$find=" ";
$replace="+";
$cargo_pickup_address =  str_replace($find,$replace,ff_getSubmit('pickup_address')) . "," . str_replace($find,$replace,ff_getSubmit('pickup_city')) . "," . str_replace($find,$replace,ff_getSubmit('pickup_province')) . ",Canada"; 
$cargo_destination_address =  str_replace($find,$replace,ff_getSubmit('destination_address')) . "," . str_replace($find,$replace,ff_getSubmit('destination_city')). "," . str_replace($find,$replace,ff_getSubmit('destination_province')) . ",Canada"; 
$geocode_url_pickup = "https://geocode.maps.co/search?q={" . $cargo_pickup_address . "}";
$geocode_url_destination = "https://geocode.maps.co/search?q={" . $cargo_destination_address . "}";

// init curl for pickup marker
$ch_pickup = curl_init();
curl_setopt($ch_pickup, CURLOPT_URL, $geocode_url_pickup);

// receive server response ...
curl_setopt($ch_pickup, CURLOPT_RETURNTRANSFER, true);// gives you a response from the server

$response = curl_exec ($ch_pickup);// return in the response var

// close curl connection for pickup marker
curl_close ($ch_pickup);

$data = json_decode($response, true);

//Output variables for testing
    if (JDEBUG)
    {
        $formatted_geocode = print_r($geocode_url_pickup, true);
        $formatted_response = print_r($response, true);
        $formatted_json = print_r($data, true);
        Log::add($formatted_geocode, Log::DEBUG, 'SQL Output');
        Log::add($formatted_response, Log::DEBUG, 'SQL Output');
        Log::add($formatted_json, Log::DEBUG, 'SQL Output');
        Log::add($data->lat, Log::DEBUG, 'SQL Output');
        Log::add($data->lon, Log::DEBUG, 'SQL Output');
        Log::add($data->display_name, Log::DEBUG, 'SQL Output');
    }

$pickup_lat = $data[0]["lat"];
$pickup_long = $data[0]["lon"];
$pickup_description = '<a href="Address of marker's description"]  . '</a>';

// init curl for destination marker
$ch_destination = curl_init();
curl_setopt($ch_destination, CURLOPT_URL, $geocode_url_destination);

// receive server response ...
curl_setopt($ch_destination, CURLOPT_RETURNTRANSFER, true);// gives you a response from the server

$response = curl_exec ($ch_destination);// return in the response var

// close curl connection for pickup marker
curl_close ($ch_destination);

$data = json_decode($response, true);

$destination_lat = $data[0]["lat"];
$destination_long = $data[0]["lon"];
$destination_description = '<a href="Address of marker's description"]  . '</a>';

// Begin a transaction to insert pickup marker if there was not an existing set, or update existing
try
{
    $db->transactionStart();
	
    $query = $db->getQuery(true);

    $col_array = array("catid", "title", "alias", "latitude", "longitude", "description", "osm_icon", "osm_marker_color", "osm_icon_color", "osm_icon_prefix", "osm_icon_spin", "osm_icon_class", "published", "checked_out", "ordering", "access", "language");
    $values = array(1, $db->quote('ID ' . $uri . ' Pickup'), $db->quote('id-' . $uri . 'p'), $db->quote($pickup_lat), $db->quote($pickup_long), $db->quote($pickup_description), $db->quote('circle'), $db->quote('blue'), $db->quote('#ffffff'), $db->quote('fa'), 0, $db->quote(' '), 1, 0, 1, 1, $db->quote('*'));

    //Output variables for testing
    if (JDEBUG)
    {
        $formatted_columns = print_r($col_array, true);
        $formatted_values = print_r($values, true);
        Log::add($formatted_columns, Log::DEBUG, 'SQL Output');
        Log::add($formatted_values, Log::DEBUG, 'SQL Output');
    }

// Select and build a marker to add to the database for the relevant record
    $query = "INSERT INTO #DATABASE_NAME# (" . implode(',', $col_array) . ") values (" . implode(',', $values) . ") 
        ON DUPLICATE KEY UPDATE " . $db->quoteName('latitude') . "=" . $db->quote($pickup_lat) . "," . $db->quoteName('longitude') . "=" . $db->quote($pickup_long) . "," . $db->quoteName('description') . "=" . $db->quote($pickup_description);
    
$db->quoteName('longitude') . '=' . $db->quote($pickup_long) . ',' . $db->quoteName('description') . '=' . $db->quote($pickup_description));

    echo $query;
    $db->setQuery($query);
    $db->execute();

    $db->transactionCommit();
}
catch (Exception $e)
{
    // catch any database errors.
    $db->transactionRollback();
    JErrorPage::render($e);
}

// Begin a transaction to insert destination marker
try
{
    $db->transactionStart();
	
    $query = $db->getQuery(true);

    $col_array = array("catid", "title", "alias", "latitude", "longitude", "description", "osm_icon", "osm_marker_color", "osm_icon_color", "osm_icon_prefix", "osm_icon_spin", "osm_icon_class", "published", "checked_out", "ordering", "access", "language");
    $values = array(1, $db->quote('ID ' . $uri . ' Destination'), $db->quote('id-' . $uri . 'd'), $db->quote($destination_lat), $db->quote($destination_long), $db->quote($destination_description), $db->quote('circle'), $db->quote('red'), $db->quote('#ffffff'), $db->quote('fa'), 0, $db->quote(' '), 1, 0, 1, 1, $db->quote('*'));

    //Output variables for testing
    if (JDEBUG)
    {
        $formatted_columns = print_r($col_array, true);
        $formatted_values = print_r($values, true);
        Log::add($formatted_columns, Log::DEBUG, 'SQL Output');
        Log::add($formatted_values, Log::DEBUG, 'SQL Output');
    }

    // Select and build a marker to add to the database for the relevant record

    $query = "INSERT INTO #DATABASE_NAME# (" . implode(',', $col_array) . ") values (" . implode(',', $values) . ") 
        ON DUPLICATE KEY UPDATE " . $db->quoteName('latitude') . "=" . $db->quote($destination_lat) . "," . $db->quoteName('longitude') . "=" . $db->quote($destination_long) . "," . $db->quoteName('description') . "=" . $db->quote($destination_description); 

$db->quoteName('longitude') . '=' . $db->quote($destination_long) . ',' . $db->quoteName('description') . '=' . $db->quote($destination_description));


    $db->setQuery($query);
    $db->execute();

    $db->transactionCommit();
}
catch (Exception $e)
{
    // catch any database errors.
    $db->transactionRollback();
    JErrorPage::render($e);
}