I have made a Cronjob code that checks every day on 12:00 every invoice and if the invoice data is equal to the date of today then a mail will be send. But when I have 2 invoices on the same date and run the cronJob in my browser for testing it will only send the first one and when I refresh the second until the loop is done.
I have to break the code into little pieces and view every step of the code but it does not want to work.
<?php
include '../../core/connect.php';
include '../../core/functions.php';
require '../../../vendor/autoload.php';
use PHPMailer\PHPMailer\PHPMailer;
$push_message = "";
$sql = "SELECT * FROM `inv_invoices` WHERE NOT `state` = 2";
$result = $con->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo $result->num_rows;
$userId = $row['user_id'];
$invoiceId = $row['invoice_id'];
if($row['state'] == 0) {
if($row['date'] === date('Y-m-d')) {
$time = date('H');
if ($time >= 00 && $time < 12) {
$opening = "Goedemorgen ";
} elseif ($time >= 12 && $time < 18) {
$opening = "Goedemiddag ";
} elseif ($time >= 18 && $time < 24) {
$opening = "Goedenavond ";
}
$sql = "SELECT `first_name`, `last_name`, `e_mail` FROM `users` WHERE `user_id`=" . $userId . " ";
$result = $con->query($sql);
$row = $result->fetch_assoc();
$name = $row['first_name'] . " " . $row['last_name'];
$email = $row['e_mail'];
$sql = "SELECT DISTINCT i.invoice_id, i.date FROM `inv_invoices_products` ip INNER JOIN `inv_invoices` i ON ip.invoice_id=i.invoice_id WHERE i.user_id = ".$userId." AND i.invoice_id=".$invoiceId." ";
$result = $con->query($sql);
$row = $result->fetch_assoc();
$date = date('d-m-Y', strtotime($row['date']));
$fid = $row['invoice_id'];
$sql2 = "SELECT sum(p.price) as price_sum FROM `inv_invoices_products` ip INNER JOIN `inv_products` p ON ip.product_id=p.product_id WHERE invoice_id=".$invoiceId." AND p.type = 0";
$result2 = $con->query($sql2);
$row2 = $result2->fetch_assoc();
$sql3 = "SELECT sum(p.price) as price_dis FROM `inv_invoices_products` ip INNER JOIN `inv_products` p ON ip.product_id=p.product_id WHERE invoice_id=".$invoiceId." AND p.type != 0";
$result3 = $con->query($sql3);
$row3 = $result3->fetch_assoc();
$price_total = $row2['price_sum'] - $row3['price_dis'];
$btw = round(($price_total) / 100 * 21, 2);
$total = $price_total + $btw;
$html = file_get_contents('../../../templates/mail.html');
$html = str_replace(
[
'{{opening}}',
'{{name}}',
'{{id}}',
'{{uid}}',
'{{date}}',
'{{price}}'
],
[
$opening,
$name,
$fid,
$userId,
$date,
$total
],
$html );
$mail = new PHPMailer(true);
try {
$mail->IsSMTP();
$mail->Host='*****';
$mail->Port = 465;
$mail->SMTPSecure = 'ssl';
$mail->SMTPAuth = true;
$mail->Username = "*****";
$mail->Password = "*****";
$mail->SMTPOptions = array( 'ssl'=> array('verify_peer' => false, 'verify_peer_name'=>false, 'allow_self_signed' => true));
$mail->From = '*****';
$mail->FromName = '*****';
$mail->AddAddress($email, $name);
$mail->WordWrap = 50;
$mail->IsHTML(true);
$mail->Subject = 'Factuur';
$mail->Body = $html;
$mail->send();
$send = true;
} catch (\Exception $e) {
echo 'Message could not be sent. Mailer Error: ', $mail->ErrorInfo;
$send = false;
}
if($send == true) {
$dateNow = date('Y-m-d');
$sql = "UPDATE `inv_invoices` SET `state`=1, `mail_date`='".$dateNow."' WHERE `invoice_id`=".$invoiceId." ";
if ($con->query($sql) === TRUE) {
$push_message .= "Factuur #{$invoiceId} verstuurd.<br>";
} else {
$push_message .= "Error updating record #{$invoiceId}: " . $con->error . ".<br>";
}
}
}
} elseif ($row['state'] == 1) {
$dateWeek = date('Y-m-d', strtotime('-7 day'));
if ($row['mail_date'] === $dateWeek){
$time = date('H');
if ($time >= 00 && $time < 12) {
$opening = "Goedemorgen ";
} elseif ($time >= 12 && $time < 18) {
$opening = "Goedemiddag ";
} elseif ($time >= 18 && $time < 24) {
$opening = "Goedenavond ";
}
$sql = "SELECT `first_name`, `last_name`, `e_mail` FROM `users` WHERE `user_id`=" . $userId . " ";
$result = $con->query($sql);
$row = $result->fetch_assoc();
$name = $row['first_name'] . " " . $row['last_name'];
$email = $row['e_mail'];
$sql = "SELECT DISTINCT i.invoice_id, i.date FROM `inv_invoices_products` ip INNER JOIN `inv_invoices` i ON ip.invoice_id=i.invoice_id WHERE i.user_id = ".$userId." AND i.invoice_id=".$invoiceId." ";
$result = $con->query($sql);
$row = $result->fetch_assoc();
$date = date('d-m-Y', strtotime($row['date']));
$fid = $row['invoice_id'];
$sql2 = "SELECT sum(p.price) as price_sum FROM `inv_invoices_products` ip INNER JOIN `inv_products` p ON ip.product_id=p.product_id WHERE invoice_id=".$invoiceId." AND p.type = 0";
$result2 = $con->query($sql2);
$row2 = $result2->fetch_assoc();
$sql3 = "SELECT sum(p.price) as price_dis FROM `inv_invoices_products` ip INNER JOIN `inv_products` p ON ip.product_id=p.product_id WHERE invoice_id=".$invoiceId." AND p.type != 0";
$result3 = $con->query($sql3);
$row3 = $result3->fetch_assoc();
$price_total = $row2['price_sum'] - $row3['price_dis'];
$btw = round(($price_total) / 100 * 21, 2);
$total = $price_total + $btw;
$html = file_get_contents('../../../templates/re-mail.html');
$html = str_replace(
[
'{{opening}}',
'{{name}}',
'{{id}}',
'{{uid}}',
'{{date}}',
'{{price}}'
],
[
$opening,
$name,
$fid,
$userId,
$date,
$total
],
$html );
$mail = new PHPMailer(true);
try {
$mail->IsSMTP();
$mail->Host='*****';
$mail->Port = 465;
$mail->SMTPSecure = 'ssl';
$mail->SMTPAuth = true;
$mail->Username = "*****";
$mail->Password = "*****";
$mail->SMTPOptions = array( 'ssl'=> array('verify_peer' => false, 'verify_peer_name'=>false, 'allow_self_signed' => true));
$mail->From = '*****';
$mail->FromName = '*****';
$mail->AddAddress($email, $name);
$mail->WordWrap = 50;
$mail->IsHTML(true);
$mail->Subject = 'Factuur';
$mail->Body = $html;
$mail->send();
$send = true;
} catch (\Exception $e) {
echo 'Message could not be sent. Mailer Error: ', $mail->ErrorInfo;
$send = false;
}
if($send == true) {
$dateNow = date('Y-m-d');
$sql = "UPDATE `inv_invoices` SET `mail_date`='".$dateNow."' WHERE `invoice_id`=".$invoiceId." ";
if ($con->query($sql) === TRUE) {
$push_message .= "Factuur #{$invoiceId} opnieuw verstuurd.<br>";
} else {
$push_message .= "Error updating record #{$invoiceId}: " . $con->error . ".<br>";
}
}
}
}
}
sendPush('*****', $push_message, 'gamelan');
}
I expect that every invoice that is found in the first query gets looped trough and send instead of just one and then needing to refers
