i have the code to send auto email based on date in the database …
run the script with task scheduler everyday…
but what i want is when the date in the database shows 30 more days will expire the contract then email is automatically sent but if more than 30 emails are not sent. please give directions…
<?php
require_once('koneksi.php');
require_once('config.php');
require('PHPMailer/PHPMailerAutoload.php');
if(mysqli_connect_errno()){
echo 'Gagal melakukan koneksi ke Database : '.mysqli_connect_error();
}
$date = date('Y-m-d');
//$kontrak = "kontrak";
$query1="SELECT *,DATE_ADD(endKontrak, INTERVAL -30 DAY) as kontrak_habis, DATEDIFF(DATE_ADD(endKontrak, INTERVAL 0 DAY), CURDATE()) as selisih, DATEDIFF(DATE_ADD(endKontrak, INTERVAL 0 DAY), CURDATE()) as selisih1 FROM customer WHERE DATEDIFF(DATE_ADD(endKontrak, INTERVAL 0 DAY), CURDATE()) >= '1' AND DATEDIFF(DATE_ADD(endKontrak, INTERVAL 0 DAY), CURDATE()) <= '30' or DATEDIFF(DATE_ADD(endKontrak, INTERVAL 0 DAY), CURDATE()) >= '1' AND DATEDIFF(DATE_ADD(endKontrak, INTERVAL 0 DAY), CURDATE()) <= '30'";
$daftar=mysqli_query($koneksi, $query1) or die (mysqli_error());
$laporan .="<h4><b>List Customer Yang Akan Habis Masa Kontraknya 30 Hari Lagi</b></h4>";
$laporan .="<br/>";
$laporan .="<table width=\"100%\" border=\"1\" align=\"center\" cellpadding=\"3\" cellspacing=\"0\">";
$laporan .="<tr style=\"color: blue;\">";
$laporan .="<td>Customer Name</td><td>Lokasi</td><td>Berakhir Masa Kontrak</td>";
$laporan .="</tr>";
while($dataku=mysqli_fetch_object($daftar))
{
$laporan .="<tr>";
$laporan .="<td>$dataku->customerName</td><td>$dataku->lokasi</td><td>$dataku->endKontrak</td>";
$laporan .="</tr>";
}
$laporan .="</table>";
$laporan .="<h5><a href='http://10.10.119.40/cronjob'><b>Untuk detail klik link ini, untuk login gunakan User dan Pass = admin</b></a></h5>";
//require_once("phpmailer/class.phpmailer.php");
//require_once("phpmailer/class.smtp.php");
$to = "ryan123@123.com";
$headers = "From : " . $email;
$sendmail = new PHPMailer();
$sendmail->isSMTP();
$sendmail->SMTPOptions = array(
'ssl' => array(
'verify_peer' => false,
'verify_peer_name' => false,
'allow_self_signed' => true
)
);
$sendmail->Host = $smtphost;
//$mail->Host = gethostbyname('smtp.gmail.com');
$sendmail->SMTPAuth = true;
$sendmail->Username = $smtpuser;
$sendmail->Password = $smtppass;
$sendmail->SMTPSecure = 'tls';
$sendmail->Port = 587;
$sendmail->setFrom('ryan@123.com', 'Tessssteerrrrr');
$sendmail->addAddress('ryanbro@123.co.id', 'Ricky Paul');
$sendmail->setFrom('ryanbro@123.co.id','Ryan'); //email pengirim
$sendmail->addReplyTo('ryanbro@123.co.id','Ryan'); //email replay
$sendmail->addAddress('ryanbro@123.co.id','Team Solar'); //email tujuan
//$sendmail->AddBCC('ryanbro@123.co.id');
$sendmail->Subject = 'Customer Masa Kontrak Habis 30 Hari Lagi'; //subjek email
$sendmail->Body=$laporan; //isi pesan dalam format laporan
$sendmail->isHTML(true);
if(!$sendmail->Send())
{
echo "Email not send : " . $sendmail->ErrorInfo;
}
else
{
echo "Email send!";
}
?>
I had to read it a few times but I think I understand the goal.
Say I have a 90 day contract, from day one to sixty I will not get any emails. But on day sixty-one the end of the contract is thirty days away. I start getting emails, one every day, until the end of the contract upon which they stop.
Nothing personal against the intention, but if it was me getting an email every day for a month I would be, uhm, less than pleased.
Anyway, have you tried running the query outside of PHP, phpMyAdmin, WorkBench, the CLI etc. ? No syntax errors, but not the results you’re expecting?
It looks to be so. I think it would be better to use CAST(endKontrak as other_type) IMHO it would be the more appropriate function and it would make the code easier to read and understand.
I always group my AND / OR in parenthesis because I am never quite sure about precedence and worry about making a mistake otherwise. But the two >= 1 and <= 30 in the WHERE look redundant to me.
thx all, script it’s worked…
so I run this script with windows task scheduler, every day at 10:00 AM, I have not been able his way to auto email is running when the customer contract is approaching the validity period and when all customers have been extended, this script does not send email automatically.
<?php
require_once('koneksi.php');
require_once('config.php');
require('PHPMailer/PHPMailerAutoload.php');
if(mysqli_connect_errno()){
echo 'Gagal melakukan koneksi ke Database : '.mysqli_connect_error();
}
$date = date('Y-m-d');
//$kontrak = "kontrak";
$query1="SELECT *,DATE_ADD(endKontrak, INTERVAL -30 DAY) as kontrak_habis, DATEDIFF(DATE_ADD(endKontrak, INTERVAL 0 DAY), CURDATE()) , DATEDIFF(DATE_ADD(endKontrak, INTERVAL 0 DAY), CURDATE()) FROM customer WHERE DATEDIFF(DATE_ADD(endKontrak, INTERVAL 0 DAY), CURDATE()) >= '1' AND DATEDIFF(DATE_ADD(endKontrak, INTERVAL 0 DAY), CURDATE()) <= '30' or DATEDIFF(DATE_ADD(endKontrak, INTERVAL 0 DAY), CURDATE()) <= '1' AND DATEDIFF(DATE_ADD(endKontrak, INTERVAL 0 DAY), CURDATE()) <= '30'";
$daftar=mysqli_query($koneksi, $query1) or die (mysqli_error());
$laporan .="<h4><b>List Customer Yang Akan Habis Masa Kontraknya 30 Hari Lagi</b></h4>";
$laporan .="<br/>";
$laporan .="<table width=\"100%\" border=\"1\" align=\"center\" cellpadding=\"3\" cellspacing=\"0\">";
$laporan .="<tr style=\"color: blue;\">";
$laporan .="<td>Customer Name</td><td>Lokasi</td><td>Berakhir Masa Kontrak</td>";
$laporan .="</tr>";
while($dataku=mysqli_fetch_object($daftar))
{
$laporan .="<tr>";
$laporan .="<td>$dataku->customerName</td><td>$dataku->lokasi</td><td>$dataku->endKontrak</td>";
$laporan .="</tr>";
}
$laporan .="</table>";
$laporan .="<h5><a href='http://10.10.119.40/cronjob'><b>Untuk detail klik link ini, untuk login gunakan User dan Pass = admin</b></a></h5>";
//require_once("phpmailer/class.phpmailer.php");
//require_once("phpmailer/class.smtp.php");
$to = "ryan123@123.com";
$headers = "From : " . $email;
$sendmail = new PHPMailer();
$sendmail->isSMTP();
$sendmail->SMTPOptions = array(
'ssl' => array(
'verify_peer' => false,
'verify_peer_name' => false,
'allow_self_signed' => true
)
);
$sendmail->Host = $smtphost;
//$mail->Host = gethostbyname('smtp.gmail.com');
$sendmail->SMTPAuth = true;
$sendmail->Username = $smtpuser;
$sendmail->Password = $smtppass;
$sendmail->SMTPSecure = 'tls';
$sendmail->Port = 587;
$sendmail->setFrom('ryan@123.com', 'Tessssteerrrrr');
$sendmail->addAddress('ryanbro@123.co.id', 'Ricky Paul');
$sendmail->setFrom('ryanbro@123.co.id','Ryan'); //email pengirim
$sendmail->addReplyTo('ryanbro@123.co.id','Ryan'); //email replay
$sendmail->addAddress('ryanbro@123.co.id','Team Solar'); //email tujuan
//$sendmail->AddBCC('ryanbro@123.co.id');
$sendmail->Subject = 'Customer Masa Kontrak Habis 30 Hari Lagi'; //subjek email
$sendmail->Body=$laporan; //isi pesan dalam format laporan
$sendmail->isHTML(true);
if(!$sendmail->Send())
{
echo "Email not send : " . $sendmail->ErrorInfo;
}
else
{
echo "Email send!";
}
?>
I’ve been using the windows task scheduler every 10:00 am running, but all the customers who have extended the contract always get a blank email …
this is my experience in creating automated email scripts, I have not got an idea if the extended customer contract period does not get any more emails.