use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;
require 'vendor/autoload.php';
include 'Config.php';
// $argv[1] daily weekly monthly
if ($argc > 1) {
$var_type = strtolower($argv[1]);
$sql_branch = "SELECT branch_id, branch_name, branch_email FROM branch";
$result_branch = $db->query($sql_branch);
if ($var_type == 'daily') {
foreach ($result_branch as $row_branch) {
$sql = "SELECT
HOUR(m.data_startdatetime) AS hours,
b.branch_name AS branch,
c.camera_name AS camera_name,
SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 5), ',', -1) AS people_in,
SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 6), ',', -1) AS people_out,
SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 5), ',', -1) - SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 6), ',', -1) AS diff,
SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 5), ',', -1) - SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 6), ',', -1) AS total
FROM data_master m
LEFT JOIN data_detail d ON m.datamaster_id = d.datamaster_id
LEFT JOIN camera_set c ON m.camera_id = c.camera_id
LEFT JOIN branch b ON c.branch_id = b.branch_id
WHERE
date(data_startdatetime) = subdate(current_date, 1)
AND c.branch_id = " . $row_branch["branch_id"] . "
AND datatype_id = 1 AND data_row = 1";
$result_data = $db->query($sql);
if ($result_data->num_rows > 0) {
$CSV_Directory = $main_path.'/Daily/' . $row_branch["branch_name"];
if (!file_exists($CSV_Directory)) {
mkdir($CSV_Directory, 0777, true);
}
$filename = $CSV_Directory . "/Report_Daily_" . date('Y_m_d', strtotime("-1 day")) . ".csv";
$BOM = "\xEF\xBB\xBF";
$f = fopen($filename, 'w');
fwrite($f, $BOM); // NEW LINE
//set column headers
$fields = array('Hours', 'Branch', 'Camera Name', 'People In', 'People Out', 'Diff', 'Total');
$fields = str_replace('"', '', $fields);
fputs($f, implode(',', $fields) . "\n");
//fputcsv($f, $fields, $delimiter, $enclosure);
while ($row_data = $result_data->fetch_assoc()) {
//$lineData = array($row['hours'], $row['branch'], $row['camera_name'], $row['people_in'], $row['people_out'], $row['diff']);
$row_data = str_replace('"', '', $row_data);
fputs($f, implode(',', $row_data) . "\n");
//fputcsv($f, $row, $delimiter, $enclosure);
}
fclose($f);
$stmt = $db->prepare("INSERT INTO mailinglist (branch_name, branch_id, branch_email, schedule_date, attachment, mailinglist_type)
VALUES (?, ?, ?, ?, ?, ?)");
$stmt->bind_param('ssssss', $branch_name, $branch_id, $branch_email, $schedule_date, $attachment, $mailing_type);
$branch_name = $row_branch["branch_name"];
$branch_id = $row_branch["branch_id"];
$branch_email = $row_branch["branch_email"];
$schedule_date = date('Y-m-d');
$attachment = $filename;
$mailing_type = "Daily";
if ($stmt->execute()) {
$last_id = $db->insert_id;
// send email
$mail = new PHPMailer(true);
$mail->isSMTP();
$mail->Host = $mailHost;
$mail->SMTPAuth = true;
$mail->SMTPKeepAlive = true; // SMTP connection will not close after each email sent, reduces SMTP overhead
$mail->Port = $maiPort;
$mail->Username = $mailUsername;
$mail->Password = $mailPassword;
$mail->setFrom($mailFromAddress, $mailFromName);
$mail->addReplyTo($mailFromAddress, $mailFromName);
$mail->isHTML(true);
$mail->Subject = 'People Counting Report Daily ('.$branch_name.')';
$mail->Body = 'report in <b>Attactment</b>';
try {
$mail->addAddress($row_branch["branch_email"]);
} catch (Exception $e) {
echo 'Invalid address skipped: ' . htmlspecialchars($row_branch["branch_email"]) . '<br>';
continue;
}
$mail->addAttachment($filename);
try {
$mail->send();
echo 'Message sent to : (' . htmlspecialchars($row_branch["branch_email"]) . ')<br>';
//Mark it as sent in the DB
$sql_update = "UPDATE mailinglist SET sent = True WHERE mailinglist_id = ".$last_id;
if ($db->query($sql_update) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $db->error;
}
} catch (Exception $e) {
echo 'Mailer Error (' . htmlspecialchars($row_branch["branch_email"]) . ') ' . $mail->ErrorInfo . '<br>';
$mail->getSMTPInstance()->reset();
}
$mail->clearAddresses();
$mail->clearAttachments();
} else {
// it didn't
echo "Error inserting record: " . $db->error;
}
$stmt->close();
}
}
} elseif ($var_type == 'weekly') {
foreach ($result_branch as $row_branch) {
$sql = "SELECT
date(m.data_startdatetime) AS data_date,
b.branch_name AS branch,
c.camera_name AS camera_name,
SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 5), ',', -1)) AS people_in,
SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 6), ',', -1)) AS people_out,
SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 5), ',', -1)) - SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 6), ',', -1)) AS diff,
SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 5), ',', -1)) + SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 6), ',', -1)) AS total
FROM data_master m
LEFT JOIN data_detail d ON m.datamaster_id = d.datamaster_id
LEFT JOIN camera_set c ON m.camera_id = c.camera_id
LEFT JOIN branch b ON c.branch_id = b.branch_id
WHERE date(data_startdatetime) between subdate(current_date, 7) AND subdate(current_date, 1) AND datatype_id = 1 AND data_row = 1
AND c.branch_id = " . $row_branch["branch_id"] . "
GROUP BY DATE(m.data_startdatetime), c.camera_name
ORDER BY data_startdatetime";
$result_data = $db->query($sql);
if ($result_data->num_rows > 0) {
$CSV_Directory = $main_path.'/Weekly/' . $row_branch["branch_name"];
if (!file_exists($CSV_Directory)) {
mkdir($CSV_Directory, 0777, true);
}
$filename = $CSV_Directory . "/Report_Weekly_" . date('o_W', strtotime("-1 week")) . ".csv";
$BOM = "\xEF\xBB\xBF";
$f = fopen($filename, 'w');
fwrite($f, $BOM); // NEW LINE
//set column headers
$fields = array('Date', 'Branch', 'Camera Name', 'People In', 'People Out', 'Diff', 'Total');
$fields = str_replace('"', '', $fields);
fputs($f, implode(',', $fields) . "\n");
//fputcsv($f, $fields, $delimiter, $enclosure);
//output each row of the data, format line as csv and write to file pointer
while ($row_data = $result_data->fetch_assoc()) {
//$lineData = array($row['hours'], $row['branch'], $row['camera_name'], $row['people_in'], $row['people_out'], $row['diff']);
$row_data = str_replace('"', '', $row_data);
fputs($f, implode(',', $row_data) . "\n");
//fputcsv($f, $row, $delimiter, $enclosure);
}
fclose($f);
$stmt = $db->prepare("INSERT INTO mailinglist (branch_name, branch_id, branch_email, schedule_date, attachment, mailinglist_type)
VALUES (?, ?, ?, ?, ?, ?)");
$stmt->bind_param('ssssss', $branch_name, $branch_id, $branch_email, $schedule_date, $attachment, $mailing_type);
$branch_name = $row_branch["branch_name"];
$branch_id = $row_branch["branch_id"];
$branch_email = $row_branch["branch_email"];
$schedule_date = date('Y-m-d');
$attachment = $filename;
$mailing_type = "Weekly";
if ($stmt->execute()) {
$last_id = $db->insert_id;
// send email
$mail = new PHPMailer(true);
$mail->isSMTP();
$mail->Host = $mailHost;
$mail->SMTPAuth = true;
$mail->SMTPKeepAlive = true; // SMTP connection will not close after each email sent, reduces SMTP overhead
$mail->Port = $maiPort;
$mail->Username = $mailUsername;
$mail->Password = $mailPassword;
$mail->setFrom($mailFromAddress, $mailFromName);
$mail->addReplyTo($mailFromAddress, $mailFromName);
$mail->isHTML(true);
$mail->Subject = 'People Counting Report Daily ('.$branch_name.')';
$mail->Body = 'report in <b>Attactment</b>';
try {
$mail->addAddress($row_branch["branch_email"]);
} catch (Exception $e) {
echo 'Invalid address skipped: ' . htmlspecialchars($row_branch["branch_email"]) . '<br>';
continue;
}
$mail->addAttachment($filename);
try {
$mail->send();
echo 'Message sent to : (' . htmlspecialchars($row_branch["branch_email"]) . ')<br>';
//Mark it as sent in the DB
$sql_update = "UPDATE mailinglist SET sent = True WHERE mailinglist_id = ".$last_id;
if ($db->query($sql_update) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $db->error;
}
} catch (Exception $e) {
echo 'Mailer Error (' . htmlspecialchars($row_branch["branch_email"]) . ') ' . $mail->ErrorInfo . '<br>';
$mail->getSMTPInstance()->reset();
}
$mail->clearAddresses();
$mail->clearAttachments();
} else {
// it didn't
echo "Error inserting record: " . $db->error;
}
$stmt->close();
}
}
} elseif ($var_type == 'monthly') {
foreach ($result_branch as $row_branch) {
$sql = "SELECT
date(m.data_startdatetime) AS data_date,
b.branch_name AS branch,
c.camera_name AS camera_name,
SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 5), ',', -1)) AS people_in,
SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 6), ',', -1)) AS people_out,
SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 5), ',', -1)) - SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 6), ',', -1)) AS diff,
SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 5), ',', -1)) + SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 6), ',', -1)) AS total
FROM data_master m
LEFT JOIN data_detail d ON m.datamaster_id = d.datamaster_id
LEFT JOIN camera_set c ON m.camera_id = c.camera_id
LEFT JOIN branch b ON c.branch_id = b.branch_id
WHERE date(data_startdatetime) between '".date('Y-m-01')."' AND '".date('Y-m-t')."'
AND datatype_id = 1 AND data_row = 1 AND c.branch_id = " . $row_branch["branch_id"] . "
GROUP BY DATE(m.data_startdatetime), c.camera_name
ORDER BY data_startdatetime";
$result_data = $db->query($sql);
if ($result_data->num_rows > 0) {
$CSV_Directory = $main_path.'/Monthly/' . $row_branch["branch_name"];
echo nl2br($CSV_Directory . "\n");
if (!file_exists($CSV_Directory)) {
mkdir($CSV_Directory, 0777, true);
}
$filename = $CSV_Directory . "/Report_Monthly_" . date("Y_m", strtotime("-1 months")) . ".csv";
$BOM = "\xEF\xBB\xBF";
$f = fopen($filename, 'w');
fwrite($f, $BOM); // NEW LINE
//set column headers
$fields = array('Date', 'Branch', 'Camera Name', 'People In', 'People Out', 'Diff', 'Total');
$fields = str_replace('"', '', $fields);
fputs($f, implode(',', $fields) . "\n");
//fputcsv($f, $fields, $delimiter, $enclosure);
//output each row of the data, format line as csv and write to file pointer
while ($row_data = $result_data->fetch_assoc()) {
//$lineData = array($row['hours'], $row['branch'], $row['camera_name'], $row['people_in'], $row['people_out'], $row['diff']);
$row_data = str_replace('"', '', $row_data);
fputs($f, implode(',', $row_data) . "\n");
//fputcsv($f, $row, $delimiter, $enclosure);
}
fclose($f);
$stmt = $db->prepare("INSERT INTO mailinglist (branch_name, branch_id, branch_email, schedule_date, attachment, mailinglist_type)
VALUES (?, ?, ?, ?, ?, ?)");
$stmt->bind_param('ssssss', $branch_name, $branch_id, $branch_email, $schedule_date, $attachment, $mailing_type);
$branch_name = $row_branch["branch_name"];
$branch_id = $row_branch["branch_id"];
$branch_email = $row_branch["branch_email"];
$schedule_date = date('Y-m-d');
$attachment = $filename;
$mailing_type = "Monthly";
if ($stmt->execute()) {
$last_id = $db->insert_id;
// send email
$mail = new PHPMailer(true);
$mail->isSMTP();
$mail->Host = $mailHost;
$mail->SMTPAuth = true;
$mail->SMTPKeepAlive = true; // SMTP connection will not close after each email sent, reduces SMTP overhead
$mail->Port = $maiPort;
$mail->Username = $mailUsername;
$mail->Password = $mailPassword;
$mail->setFrom($mailFromAddress, $mailFromName);
$mail->addReplyTo($mailFromAddress, $mailFromName);
$mail->isHTML(true);
$mail->Subject = 'People Counting Report Daily ('.$branch_name.')';
$mail->Body = 'report in <b>Attactment</b>';
try {
$mail->addAddress($row_branch["branch_email"]);
} catch (Exception $e) {
echo 'Invalid address skipped: ' . htmlspecialchars($row_branch["branch_email"]) . '<br>';
continue;
}
$mail->addAttachment($filename);
try {
$mail->send();
echo 'Message sent to : (' . htmlspecialchars($row_branch["branch_email"]) . ')<br>';
//Mark it as sent in the DB
$sql_update = "UPDATE mailinglist SET sent = True WHERE mailinglist_id = ".$last_id;
if ($db->query($sql_update) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $db->error;
}
} catch (Exception $e) {
echo 'Mailer Error (' . htmlspecialchars($row_branch["branch_email"]) . ') ' . $mail->ErrorInfo . '<br>';
$mail->getSMTPInstance()->reset();
}
$mail->clearAddresses();
$mail->clearAttachments();
} else {
// it didn't
echo "Error inserting record: " . $db->error;
}
$stmt->close();
}
}
}
exit;
} else {
echo "no argument passed\n";
exit;
}
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;
require 'vendor/autoload.php';
include 'Config.php';
// $argv[1] daily weekly monthly
if ($argc > 1) {
$var_type = strtolower($argv[1]);
$sql_branch = "SELECT branch_id, branch_name, branch_email FROM branch";
$result_branch = $db->query($sql_branch);
if ($var_type == 'daily') {
foreach ($result_branch as $row_branch) {
$sql = "SELECT
HOUR(m.data_startdatetime) AS hours,
b.branch_name AS branch,
c.camera_name AS camera_name,
SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 5), ',', -1) AS people_in,
SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 6), ',', -1) AS people_out,
SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 5), ',', -1) - SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 6), ',', -1) AS diff,
SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 5), ',', -1) - SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 6), ',', -1) AS total
FROM data_master m
LEFT JOIN data_detail d ON m.datamaster_id = d.datamaster_id
LEFT JOIN camera_set c ON m.camera_id = c.camera_id
LEFT JOIN branch b ON c.branch_id = b.branch_id
WHERE
date(data_startdatetime) = subdate(current_date, 1)
AND c.branch_id = " . $row_branch["branch_id"] . "
AND datatype_id = 1 AND data_row = 1";
$result_data = $db->query($sql);
if ($result_data->num_rows > 0) {
$CSV_Directory = $main_path.'/Daily/' . $row_branch["branch_name"];
if (!file_exists($CSV_Directory)) {
mkdir($CSV_Directory, 0777, true);
}
$filename = $CSV_Directory . "/Report_Daily_" . date('Y_m_d', strtotime("-1 day")) . ".csv";
$BOM = "";
$f = fopen($filename, 'w');
fwrite($f, $BOM); // NEW LINE
//set column headers
$fields = array('Hours', 'Branch', 'Camera Name', 'People In', 'People Out', 'Diff', 'Total');
$fields = str_replace('"', '', $fields);
fputs($f, implode(',', $fields) . "\n");
//fputcsv($f, $fields, $delimiter, $enclosure);
while ($row_data = $result_data->fetch_assoc()) {
//$lineData = array($row['hours'], $row['branch'], $row['camera_name'], $row['people_in'], $row['people_out'], $row['diff']);
$row_data = str_replace('"', '', $row_data);
fputs($f, implode(',', $row_data) . "\n");
//fputcsv($f, $row, $delimiter, $enclosure);
}
fclose($f);
$stmt = $db->prepare("INSERT INTO mailinglist (branch_name, branch_id, branch_email, schedule_date, attachment, mailinglist_type)
VALUES (?, ?, ?, ?, ?, ?)");
$stmt->bind_param('ssssss', $branch_name, $branch_id, $branch_email, $schedule_date, $attachment, $mailing_type);
$branch_name = $row_branch["branch_name"];
$branch_id = $row_branch["branch_id"];
$branch_email = $row_branch["branch_email"];
$schedule_date = date('Y-m-d');
$attachment = $filename;
$mailing_type = "Daily";
if ($stmt->execute()) {
$last_id = $db->insert_id;
// send email
$mail = new PHPMailer(true);
$mail->isSMTP();
$mail->Host = $mailHost;
$mail->SMTPAuth = true;
$mail->SMTPKeepAlive = true; // SMTP connection will not close after each email sent, reduces SMTP overhead
$mail->Port = $maiPort;
$mail->Username = $mailUsername;
$mail->Password = $mailPassword;
$mail->setFrom($mailFromAddress, $mailFromName);
$mail->addReplyTo($mailFromAddress, $mailFromName);
$mail->isHTML(true);
$mail->Subject = 'People Counting Report Daily ('.$branch_name.')';
$mail->Body = 'report in <b>Attactment</b>';
try {
$mail->addAddress($row_branch["branch_email"]);
} catch (Exception $e) {
echo 'Invalid address skipped: ' . htmlspecialchars($row_branch["branch_email"]) . '<br>';
continue;
}
$mail->addAttachment($filename);
try {
$mail->send();
echo 'Message sent to : (' . htmlspecialchars($row_branch["branch_email"]) . ')<br>';
//Mark it as sent in the DB
$sql_update = "UPDATE mailinglist SET sent = True WHERE mailinglist_id = ".$last_id;
if ($db->query($sql_update) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $db->error;
}
} catch (Exception $e) {
echo 'Mailer Error (' . htmlspecialchars($row_branch["branch_email"]) . ') ' . $mail->ErrorInfo . '<br>';
$mail->getSMTPInstance()->reset();
}
$mail->clearAddresses();
$mail->clearAttachments();
} else {
// it didn't
echo "Error inserting record: " . $db->error;
}
$stmt->close();
}
}
} elseif ($var_type == 'weekly') {
foreach ($result_branch as $row_branch) {
$sql = "SELECT
date(m.data_startdatetime) AS data_date,
b.branch_name AS branch,
c.camera_name AS camera_name,
SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 5), ',', -1)) AS people_in,
SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 6), ',', -1)) AS people_out,
SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 5), ',', -1)) - SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 6), ',', -1)) AS diff,
SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 5), ',', -1)) + SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 6), ',', -1)) AS total
FROM data_master m
LEFT JOIN data_detail d ON m.datamaster_id = d.datamaster_id
LEFT JOIN camera_set c ON m.camera_id = c.camera_id
LEFT JOIN branch b ON c.branch_id = b.branch_id
WHERE date(data_startdatetime) between subdate(current_date, 7) AND subdate(current_date, 1) AND datatype_id = 1 AND data_row = 1
AND c.branch_id = " . $row_branch["branch_id"] . "
GROUP BY DATE(m.data_startdatetime), c.camera_name
ORDER BY data_startdatetime";
$result_data = $db->query($sql);
if ($result_data->num_rows > 0) {
$CSV_Directory = $main_path.'/Weekly/' . $row_branch["branch_name"];
if (!file_exists($CSV_Directory)) {
mkdir($CSV_Directory, 0777, true);
}
$filename = $CSV_Directory . "/Report_Weekly_" . date('o_W', strtotime("-1 week")) . ".csv";
$BOM = "";
$f = fopen($filename, 'w');
fwrite($f, $BOM); // NEW LINE
//set column headers
$fields = array('Date', 'Branch', 'Camera Name', 'People In', 'People Out', 'Diff', 'Total');
$fields = str_replace('"', '', $fields);
fputs($f, implode(',', $fields) . "\n");
//fputcsv($f, $fields, $delimiter, $enclosure);
//output each row of the data, format line as csv and write to file pointer
while ($row_data = $result_data->fetch_assoc()) {
//$lineData = array($row['hours'], $row['branch'], $row['camera_name'], $row['people_in'], $row['people_out'], $row['diff']);
$row_data = str_replace('"', '', $row_data);
fputs($f, implode(',', $row_data) . "\n");
//fputcsv($f, $row, $delimiter, $enclosure);
}
fclose($f);
$stmt = $db->prepare("INSERT INTO mailinglist (branch_name, branch_id, branch_email, schedule_date, attachment, mailinglist_type)
VALUES (?, ?, ?, ?, ?, ?)");
$stmt->bind_param('ssssss', $branch_name, $branch_id, $branch_email, $schedule_date, $attachment, $mailing_type);
$branch_name = $row_branch["branch_name"];
$branch_id = $row_branch["branch_id"];
$branch_email = $row_branch["branch_email"];
$schedule_date = date('Y-m-d');
$attachment = $filename;
$mailing_type = "Weekly";
if ($stmt->execute()) {
$last_id = $db->insert_id;
// send email
$mail = new PHPMailer(true);
$mail->isSMTP();
$mail->Host = $mailHost;
$mail->SMTPAuth = true;
$mail->SMTPKeepAlive = true; // SMTP connection will not close after each email sent, reduces SMTP overhead
$mail->Port = $maiPort;
$mail->Username = $mailUsername;
$mail->Password = $mailPassword;
$mail->setFrom($mailFromAddress, $mailFromName);
$mail->addReplyTo($mailFromAddress, $mailFromName);
$mail->isHTML(true);
$mail->Subject = 'People Counting Report Daily ('.$branch_name.')';
$mail->Body = 'report in <b>Attactment</b>';
try {
$mail->addAddress($row_branch["branch_email"]);
} catch (Exception $e) {
echo 'Invalid address skipped: ' . htmlspecialchars($row_branch["branch_email"]) . '<br>';
continue;
}
$mail->addAttachment($filename);
try {
$mail->send();
echo 'Message sent to : (' . htmlspecialchars($row_branch["branch_email"]) . ')<br>';
//Mark it as sent in the DB
$sql_update = "UPDATE mailinglist SET sent = True WHERE mailinglist_id = ".$last_id;
if ($db->query($sql_update) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $db->error;
}
} catch (Exception $e) {
echo 'Mailer Error (' . htmlspecialchars($row_branch["branch_email"]) . ') ' . $mail->ErrorInfo . '<br>';
$mail->getSMTPInstance()->reset();
}
$mail->clearAddresses();
$mail->clearAttachments();
} else {
// it didn't
echo "Error inserting record: " . $db->error;
}
$stmt->close();
}
}
} elseif ($var_type == 'monthly') {
foreach ($result_branch as $row_branch) {
$sql = "SELECT
date(m.data_startdatetime) AS data_date,
b.branch_name AS branch,
c.camera_name AS camera_name,
SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 5), ',', -1)) AS people_in,
SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 6), ',', -1)) AS people_out,
SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 5), ',', -1)) - SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 6), ',', -1)) AS diff,
SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 5), ',', -1)) + SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(d.data_text, ',', 6), ',', -1)) AS total
FROM data_master m
LEFT JOIN data_detail d ON m.datamaster_id = d.datamaster_id
LEFT JOIN camera_set c ON m.camera_id = c.camera_id
LEFT JOIN branch b ON c.branch_id = b.branch_id
WHERE date(data_startdatetime) between '".date('Y-m-01')."' AND '".date('Y-m-t')."'
AND datatype_id = 1 AND data_row = 1 AND c.branch_id = " . $row_branch["branch_id"] . "
GROUP BY DATE(m.data_startdatetime), c.camera_name
ORDER BY data_startdatetime";
$result_data = $db->query($sql);
if ($result_data->num_rows > 0) {
$CSV_Directory = $main_path.'/Monthly/' . $row_branch["branch_name"];
echo nl2br($CSV_Directory . "\n");
if (!file_exists($CSV_Directory)) {
mkdir($CSV_Directory, 0777, true);
}
$filename = $CSV_Directory . "/Report_Monthly_" . date("Y_m", strtotime("-1 months")) . ".csv";
$BOM = "";
$f = fopen($filename, 'w');
fwrite($f, $BOM); // NEW LINE
//set column headers
$fields = array('Date', 'Branch', 'Camera Name', 'People In', 'People Out', 'Diff', 'Total');
$fields = str_replace('"', '', $fields);
fputs($f, implode(',', $fields) . "\n");
//fputcsv($f, $fields, $delimiter, $enclosure);
//output each row of the data, format line as csv and write to file pointer
while ($row_data = $result_data->fetch_assoc()) {
//$lineData = array($row['hours'], $row['branch'], $row['camera_name'], $row['people_in'], $row['people_out'], $row['diff']);
$row_data = str_replace('"', '', $row_data);
fputs($f, implode(',', $row_data) . "\n");
//fputcsv($f, $row, $delimiter, $enclosure);
}
fclose($f);
$stmt = $db->prepare("INSERT INTO mailinglist (branch_name, branch_id, branch_email, schedule_date, attachment, mailinglist_type)
VALUES (?, ?, ?, ?, ?, ?)");
$stmt->bind_param('ssssss', $branch_name, $branch_id, $branch_email, $schedule_date, $attachment, $mailing_type);
$branch_name = $row_branch["branch_name"];
$branch_id = $row_branch["branch_id"];
$branch_email = $row_branch["branch_email"];
$schedule_date = date('Y-m-d');
$attachment = $filename;
$mailing_type = "Monthly";
if ($stmt->execute()) {
$last_id = $db->insert_id;
// send email
$mail = new PHPMailer(true);
$mail->isSMTP();
$mail->Host = $mailHost;
$mail->SMTPAuth = true;
$mail->SMTPKeepAlive = true; // SMTP connection will not close after each email sent, reduces SMTP overhead
$mail->Port = $maiPort;
$mail->Username = $mailUsername;
$mail->Password = $mailPassword;
$mail->setFrom($mailFromAddress, $mailFromName);
$mail->addReplyTo($mailFromAddress, $mailFromName);
$mail->isHTML(true);
$mail->Subject = 'People Counting Report Daily ('.$branch_name.')';
$mail->Body = 'report in <b>Attactment</b>';
try {
$mail->addAddress($row_branch["branch_email"]);
} catch (Exception $e) {
echo 'Invalid address skipped: ' . htmlspecialchars($row_branch["branch_email"]) . '<br>';
continue;
}
$mail->addAttachment($filename);
try {
$mail->send();
echo 'Message sent to : (' . htmlspecialchars($row_branch["branch_email"]) . ')<br>';
//Mark it as sent in the DB
$sql_update = "UPDATE mailinglist SET sent = True WHERE mailinglist_id = ".$last_id;
if ($db->query($sql_update) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $db->error;
}
} catch (Exception $e) {
echo 'Mailer Error (' . htmlspecialchars($row_branch["branch_email"]) . ') ' . $mail->ErrorInfo . '<br>';
$mail->getSMTPInstance()->reset();
}
$mail->clearAddresses();
$mail->clearAttachments();
} else {
// it didn't
echo "Error inserting record: " . $db->error;
}
$stmt->close();
}
}
}
exit;
} else {
echo "no argument passed\n";
exit;
}
© 2023 Quttera Ltd. All rights reserved.