/home/desid573/public_html/family
Edit: /home/desid573/public_html/family/tmr_trips.php (11287B)
connect_error);
$qcon=mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
date_default_timezone_set("Australia/Perth");
$query1 = "SELECT * FROM invoices WHERE type = 'BF' OR type = 'GC' or type = 'SC'";
$result = mysqli_query($conn, $query1);
$array = array();
$count = 0;
while($rowSQL = mysqli_fetch_assoc($result)){
$dateMin = strtotime("2025-05-28 00:00");
$dateMax = strtotime("2025-08-19 00:00");
$reference = $rowSQL["reference"];
$amount = $rowSQL["amount"];
if(strtotime($rowSQL["date_created"]) > $dateMin && strtotime($rowSQL["date_created"]) < $dateMax) {
$query = "SELECT * FROM `desid573_eway_codes`.`driver_compliance` WHERE `driver_name` = '".$rowSQL["driver"]."'";
$drivers = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($drivers);
$driverNumber = $row["state_driver"];
$vehicleNumber = $row["registration"];
if($driverNumber != "") {
$query = "SELECT * FROM `desid573_familycab`.`jobs` WHERE `reference` = '".$reference."'";
$drivers = mysqli_query($conn, $query);
if($drivers) {
$row = mysqli_fetch_assoc($drivers);
$duration = $row["quote_time"];
$pickup = $row["job_pickup"];
$dropoff = $row["job_dropoff"];
$quote = $row["job_quote"];
$pickQuery = "SELECT * FROM `desid573_familycab`.`terminal_details` WHERE `name` = '".$pickup."'";
$pick = mysqli_query($conn, $pickQuery);
//echo $pickup;
if(mysqli_num_rows($pick) == 0) {
$pickAddress = "SELECT * FROM `desid573_eway_codes`.`place_coords` WHERE `name` = '".$pickup."'";
//echo $pickAddress;
$pickQuery = mysqli_query($conn, $pickAddress);
if(!$pickQuery) {
echo $conn->error;
}
if(mysqli_num_rows($pickQuery) > 0) {
$coords = mysqli_fetch_assoc($pickQuery);
$pickSuburb = getSuburb($pickup);
$pickLat = $coords["lat"];
$pickLong = $coords["lon"];
} else {
$picklookup = getplaceid($pickup);
if($picklookup) {
$pickCoords = getCoords($picklookup, $pickup);
$pickSuburb = getSuburb($pickup);
$pickLat = $pickCoords["lat"];
$pickLong = $pickCoords["lon"];
}
}
} else {
$pickDetails = mysqli_fetch_assoc($pick);
if($pickDetails["buttonLabel"] == "BNE") {
$pickSuburb = "Brisbane Airport";
} else if($pickDetails["buttonLabel"] == "OOL") {
$pickSuburb = "Bilinga";
} else if($pickDetails["buttonLabel"] == "MCY") {
$pickSuburb = "Marcoola";
}
$pickLat = $pickDetails["lat"];
$pickLong = $pickDetails["long"];
}
$dropQuery = "SELECT * FROM `desid573_familycab`.`terminal_details` WHERE `name` = '".$dropoff."'";
$drop = mysqli_query($conn, $dropQuery);
//echo $pickup;
if(mysqli_num_rows($drop) == 0) {
$dropAddress = "SELECT * FROM `desid573_eway_codes`.`place_coords` WHERE `name` = '".$dropoff."'";
//echo $dropAddress;
$dropQuery = mysqli_query($conn, $dropAddress);
if(!$dropQuery) {
echo $conn->error;
}
if(mysqli_num_rows($dropQuery) > 0) {
$coords = mysqli_fetch_assoc($dropQuery);
$dropSuburb = getSuburb($dropoff);
$dropLat = $coords["lat"];
$dropLong = $coords["lon"];
error_log(json_encode($coords, true));
} else {
$droplookup = getplaceid($dropoff);
if($droplookup) {
$dropCoords = getCoords($droplookup, $dropoff);
$dropSuburb = getSuburb($dropoff);
$dropLat = $dropCoords["lat"];
$dropLong = $dropCoords["lon"];
}
}
} else {
$dropDetails = mysqli_fetch_assoc($drop);
if($dropDetails["buttonLabel"] == "BNE") {
$dropSuburb = "Brisbane Airport";
} else if($dropDetails["buttonLabel"] == "OOL") {
$dropSuburb = "Bilinga";
} else if($dropDetails["buttonLabel"] == "MCY") {
$dropSuburb = "Marcoola";
} else {
$dropSuburb = "Brisbane Airport";
}
$dropLat = $dropDetails["lat"];
$dropLong = $dropDetails["long"];
}
$time24 = date("G:i", strtotime($row["job_time"]));
$d = date_create_from_format("jS M, Y", $row["job_date"]);
$date24 = date_format($d, "Y-m-d");
$bookTime = strtotime($date24." ".$time24);
$bookEnd = $bookTime + $duration*60;
echo "146642, ".$reference.", ".$row["date_added"].", ".date("d-m-Y G:i", $bookTime).", ".$driverNumber.", ".$pickLat.", ".$pickLong.", ".$dropLat.", ".$dropLong.", ".$pickSuburb.", ".$dropSuburb.", ".date("d-m-Y G:i", $bookTime).", ".date("d-m-Y G:i", $bookEnd).", ".$vehicleNumber.", ".$driverNumber.", N, ".$quote.", ".$amount.", , N
";
$count = $count + 1;
} else {
error_log($conn->error);
}
}
//echo $rowSQL["date_created"]."
";
}
}
function getplaceid($place) {
$url = "https://maps.googleapis.com/maps/api/place/findplacefromtext/json?key=AIzaSyCcEAMzk2nhNbFFBpq0X7DhmNlrdUtW0a0&inputtype=textquery&input=".urlencode($place);
//error_log($url);
$curlObj = curl_init();
curl_setopt($curlObj, CURLOPT_URL, $url);
curl_setopt($curlObj, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($curlObj, CURLOPT_SSL_VERIFYPEER, 0);
curl_setopt($curlObj, CURLOPT_HEADER, 0);
curl_setopt($curlObj, CURLOPT_HTTPHEADER, array('Content-type:application/json'));
$response = curl_exec($curlObj);
error_log($response);
$json = json_decode($response, true);
curl_close($curlObj);
if($json["status"] == "OK") {
return $json["candidates"][0]["place_id"];
} else {
return false;
}
}
function getCoords($placeid, $name) {
$url = "https://maps.googleapis.com/maps/api/place/details/json?fields=name,geometry,place_id,formatted_address&key=AIzaSyCcEAMzk2nhNbFFBpq0X7DhmNlrdUtW0a0&placeid=".$placeid;
$curlObj = curl_init();
curl_setopt($curlObj, CURLOPT_URL, $url);
curl_setopt($curlObj, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($curlObj, CURLOPT_SSL_VERIFYPEER, 0);
curl_setopt($curlObj, CURLOPT_HEADER, 0);
curl_setopt($curlObj, CURLOPT_HTTPHEADER, array('Content-type:application/json'));
$response = curl_exec($curlObj);
error_log($response);
$json = json_decode($response, true);
curl_close($curlObj);
if($json["status"] == "OK") {
// updatePlace($json["result"]["formatted_address"], $json["result"]["geometry"]["location"]["lat"], $json["result"]["geometry"]["location"]["lng"]);
// if($name != $json["result"]["formatted_address"]) {
// updatePlace($name, $json["result"]["geometry"]["location"]["lat"], $json["result"]["geometry"]["location"]["lng"]);
// }
return array("address" => $json["result"]["formatted_address"], "lat" => $json["result"]["geometry"]["location"]["lat"], "lon" => $json["result"]["geometry"]["location"]["lng"]);
} else {
return false;
}
}
function getSuburb($csv) {
// Convert the string to an array, removing any extra whitespace
$values = array_map('trim', explode(',', $csv));
// Check if there are at least two values
if (count($values) < 2) {
return null; // or return a default value / throw an exception
}
// Return the second last value
$suburb = str_replace('QLD', '', $values[count($values) - 2]);
return preg_replace('/\b\d{4}\b/', '', $suburb);
}
