/home/desid573/public_html/family
Edit: /home/desid573/public_html/family/postcodeReport.php (21015B)
setTimestamp($period);
$periodString = date_format($dt, "jS M");
$dt = new DateTime();
$dt->setTimestamp($periodLower);
$periodStringLower = date_format($dt, "jS M Y");
require_once('class.phpmailer.php');
require_once('class.smtp.php');
$dbhost = "localhost";
$dbuser = "desid573_famcab";
$dbpass = "C]+6wQySxov(";
$dbname = "desid573_familycab";
$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname) or die($conn->connect_error);
$qcon=mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
date_default_timezone_set("Australia/Perth");
$yesterday = "Last ".$periodString." Days";
$lastDriver = "";
$lastDriverTotal = "";
$lastDriverTollTotal = "";
$query1 = "SELECT * FROM invoices ORDER BY driver";
$result = mysqli_query($conn, $query1);
$count = array("sydney" => 0, "melbourne" => 0, "brisbane" => 0, "goldcoast" => 0, "perth" => 0);
$value = array("sydney" => 0, "melbourne" => 0, "brisbane" => 0, "goldcoast" => 0, "perth" => 0);
$postcodes = array();
$tolls = 0;
while($rowSQL = mysqli_fetch_assoc($result)){
if(strtotime($rowSQL['date_created']) > $period && strtotime($rowSQL['date_created']) < $periodLower) {
if($rowSQL["type"] == "SF") {
$count["sydney"] = $count["sydney"] + 1;
$value["sydney"] = $value["sydney"] + $rowSQL["amount"];
} else if ($rowSQL["type"] == "MF") {
$count["melbourne"] = $count["melbourne"] + 1;
$value["melbourne"] = $value["melbourne"] + $rowSQL["amount"];
} else if($rowSQL["type"] == "BF") {
$count["brisbane"] = $count["brisbane"] + 1;
$value["brisbane"] = $value["brisbane"] + $rowSQL["amount"];
$postcodeCheck = "SELECT * FROM `postcode_array` WHERE `ref` = '".$rowSQL["reference"]."'";
$checkSQL = $conn->query($postcodeCheck);
if($checkSQL->num_rows == 0) {
postcodeCheck($rowSQL["reference"], $conn);
}
} else if($rowSQL["type"] == "GC") {
$count["goldcoast"] = $count["goldcoast"] + 1;
$value["goldcoast"] = $value["goldcoast"] + $rowSQL["amount"];
$postcodeCheck = "SELECT * FROM `postcode_array` WHERE `ref` = '".$rowSQL["reference"]."'";
$checkSQL = $conn->query($postcodeCheck);
if($checkSQL->num_rows == 0) {
postcodeCheck($rowSQL["reference"], $conn);
}
} else if($rowSQL["type"] == "SC") {
$postcodeCheck = "SELECT * FROM `postcode_array` WHERE `ref` = '".$rowSQL["reference"]."'";
$checkSQL = $conn->query($postcodeCheck);
if($checkSQL->num_rows == 0) {
postcodeCheck($rowSQL["reference"], $conn);
}
} else if($rowSQL["type"] == "FC") {
$count["perth"] = $count["perth"] + 1;
$value["perth"] = $value["perth"] + $rowSQL["amount"];
}
}
}
$dbhost = "localhost";
$dbuser = "desid573_fam_app";
$dbpass = "TW#7CCqKReDn";
$dbname = "desid573_famcab_api";
$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname) or die($conn->connect_error);
$qcon=mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
$query2 = "SELECT * FROM invoices ORDER BY driver";
$result2 = mysqli_query($conn, $query2);
while($rowSQL = mysqli_fetch_assoc($result2)){
if(strtotime($rowSQL['date_created']) > $period && strtotime($rowSQL['date_created']) < $periodLower) {
if($rowSQL["type"] == "SA") {
$count["sydney"] = $count["sydney"] + 1;
$value["sydney"] = $value["sydney"] + $rowSQL["amount"];
} else if ($rowSQL["type"] == "MA") {
$count["melbourne"] = $count["melbourne"] + 1;
$value["melbourne"] = $value["melbourne"] + $rowSQL["amount"];
} else if($rowSQL["type"] == "BA") {
$count["brisbane"] = $count["brisbane"] + 1;
$value["brisbane"] = $value["brisbane"] + $rowSQL["amount"];
$postcodeCheck = "SELECT * FROM `desid573_familycab`.`postcode_array` WHERE `ref` = '".$rowSQL["reference"]."'";
$checkSQL = $conn->query($postcodeCheck);
if($checkSQL->num_rows == 0) {
postcodeCheckApp($rowSQL["reference"], $conn);
}
} else if($rowSQL["type"] == "GA") {
$count["goldcoast"] = $count["goldcoast"] + 1;
$value["goldcoast"] = $value["goldcoast"] + $rowSQL["amount"];
$postcodeCheck = "SELECT * FROM `desid573_familycab`.`postcode_array` WHERE `ref` = '".$rowSQL["reference"]."'";
$checkSQL = $conn->query($postcodeCheck);
if($checkSQL->num_rows == 0) {
postcodeCheckApp($rowSQL["reference"], $conn);
}
} else if($rowSQL["type"] == "YA") {
$postcodeCheck = "SELECT * FROM `postcode_array` WHERE `ref` = '".$rowSQL["reference"]."'";
$checkSQL = $conn->query($postcodeCheck);
if($checkSQL->num_rows == 0) {
postcodeCheckApp($rowSQL["reference"], $conn);
}
} else if($rowSQL["type"] == "FA") {
$count["perth"] = $count["perth"] + 1;
$value["perth"] = $value["perth"] + $rowSQL["amount"];
}
}
}
$countTotal = $count["sydney"] + $count["melbourne"] + $count["brisbane"] + $count["goldcoast"] + $count["perth"];
$valueTotal = $value["sydney"] + $value["melbourne"] + $value["brisbane"] + $value["goldcoast"] + $value["perth"];
$lastDriver = "";
$quotetotal = 0;
$acttotal = 0;
$contEst = $valueTotal * 0.85 + $tolls;
function postcodeCheck($ref, $conn) {
global $postcodes;
$check = "SELECT `job_pickup` FROM `jobs` WHERE `reference` = '".$ref."'";
$checkFind = mysqli_query($conn, $check);
if($checkFind) {
$checkResult = mysqli_fetch_assoc($checkFind);
$pickup = $checkResult["job_pickup"];
if($pickup != "") {
if($pickup == "Brisbane Airport (BNE), Airport Drive, Brisbane Airport QLD") {
$suburb = "Brisbane Airport";
} else if($pickup == "Gold Coast Airport, Eastern Avenue, Bilinga QLD") {
$suburb = "Bilinga";
} else {
$suburb = getSuburb($pickup);
}
if($postcodes[$suburb] !== false) {
$number = $postcodes[$suburb];
$postcodes[$suburb] = $number + 1;
} else {
$postcodes[$suburb] = 1;
}
}
}
}
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);
}
function postcodeCheckApp($ref, $conn) {
global $postcodes;
$check = "SELECT `pickup_addr` FROM `booking` WHERE `reference` = '".$ref."'";
$checkFind = mysqli_query($conn, $check);
if($checkFind) {
$checkResult = mysqli_fetch_assoc($checkFind);
$pickup = $checkResult["pickup_addr"];
if($pickup != "") {
if($pickup == "Brisbane Airport (BNE), Airport Drive, Brisbane Airport QLD") {
$suburb = "Brisbane Airport";
} else if($pickup == "Gold Coast Airport, Eastern Avenue, Bilinga QLD") {
$suburb = "Bilinga";
} else {
$suburb = getSuburb($pickup);
}
if($postcodes[$suburb] !== false) {
$number = $postcodes[$suburb];
$postcodes[$suburb] = $number + 1;
} else {
$postcodes[$suburb] = 1;
}
// $url = "https://maps.googleapis.com/maps/api/place/findplacefromtext/json?key=AIzaSyCzDCB1H9ZvIgT4NHGqCXajVQlRXKwTVY4&inputtype=textquery&input=".urlencode($pickup);
// $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") {
// $placeid = $json["candidates"][0]["place_id"];
// $url = "https://maps.googleapis.com/maps/api/place/details/json?fields=address_component&key=AIzaSyCzDCB1H9ZvIgT4NHGqCXajVQlRXKwTVY4&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") {
// $addresses = $json["result"]["address_components"];
// foreach($addresses as $type) {
// error_log(json_encode($type, true));
// if($type["types"][0] == "postal_code") {
// error_log($postcodes["bob"]);
// if($postcodes[$type["long_name"]] !== false) {
// $name = $type["long_name"];
// $number = $postcodes[$name];
// $postcodes[$name] = $number + 1;
// } else {
// $postcodes[$type["long_name"]] = 1;
// }
// $insert = "INSERT INTO `desid573_familycab`.`postcode_array`(`id`, `ref`, `postcode`, `year`) VALUES (NULL,'".$ref."','".$type["long_name"]."', '2023')";
// $insertSQL = $conn->query($insert);
// if(!$insertSQL) {
// error_log($conn->error);
// }
// }
// }
// } else {
// }
// }
// }
} else {
error_log($conn->error);
}
}
}
$message = "
Week Report
Report for ".$periodString." to ".$periodStringLower."
Range:
| No. Jobs | Value |
|---|
| Sydney | ".$count["sydney"]." | ".$value["sydney"]." |
| Melbourne | ".$count["melbourne"]." | ".$value["melbourne"]." |
| Brisbane | ".$count["brisbane"]." | ".$value["brisbane"]." |
| Gold Coast | ".$count["goldcoast"]." | ".$value["goldcoast"]." |
| Perth | ".$count["perth"]." | ".$value["perth"]." |
| Total | ".$countTotal." | ".$valueTotal." |
| Tolls | | ".$tolls." |
| Cont. Pay | | ".$contEst." |
";
echo json_encode($postcodes);
$message = $message."
";
//send_mail("lwscann@gmail.com", "reports@perthdesignateddrivers.com.au", "Report ".$yesterday, $message);
echo $message;
$mail = new PHPMailer;
$mail->SMTPDebug = 3;
$mail->isSMTP();
$mail->Host = "webcloud90.au.syrahost.com"; // specify main and backup server
$mail->Port = 465;
$mail->SMTPSecure = "ssl";
$mail->SMTPAuth = true; // turn on SMTP authentication
$mail->Username = "lewis@perthdesignateddrivers.com.au"; // SMTP username
$mail->Password = "Chunky11"; // SMTP password
$mail->From = "lewis@perthdesignateddrivers.com.au";
$mail->FromName = "Reports";
$mail->AddAddress("lwscann@gmail.com");
$mail->IsHTML(true); // set email format to HTML
$mail->Subject = 'Daily Report for'.$yesterday;
$mail->Body = $message;
// if(!$mail->Send())
//{
// echo "Message could not be sent.
//";
// echo "Mailer Error: " . $mail->ErrorInfo;
// exit;
//
//echo "Message has been sent";
?>
