Online PHP and Javascript Decoder decode hidden script to uncover its real functionality


Show other level

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.