after-login/scheduler.php

<?php
    date_default_timezone_set('Etc/UTC');
    include '../config/connection.php';
    require_once '../dompdf_config.inc.php';
    require 'php-export-data.class.php';
    require '../PHPMailerAutoload.php';

    function genereateQuery($kriteria_jadwal, $interval){
        return "
            SELECT
                id,
                id_laporan,
                email_tujuan,
                durasi,
                format,
                MAX(last_eksekusi) AS max_last_eksekusi
            FROM (
                SELECT eo.id,
                		eo.id_laporan,
                		eo.email_tujuan,
                		ls.last_eksekusi,
                		durasi,
                        format
                FROM eksekusi_otomatis eo
                LEFT JOIN log_schedule ls ON eo.id = ls.id_eksekusi_otomatis
                WHERE jadwal = '" . $kriteria_jadwal . "'
            ) q
            GROUP BY id_laporan
            HAVING max_last_eksekusi IS NULL OR max_last_eksekusi <= (NOW() - INTERVAL durasi " . $interval . ")
        ";
    }

    function sendReport($conn, $sql){
        $query = mysqli_query($conn, $sql);
        while($data = mysqli_fetch_assoc($query)){
            $id_laporan = $data['id_laporan'];
            $id = $data['id'];
            $email = $data['email_tujuan'];
            $format = $data['format'];
            $filename = 'report-' . $id_laporan . '-' . date('YmdHis') . '.' . strtolower($format);
            $filepath = '../reports/' . $filename;

            if($format == "XLS"){
                $exporter = new ExportDataExcel('file', $filepath);
            	$exporter->initialize(); // starts streaming data to web browser

                $select_laporan = "SELECT judul, queri FROM laporan WHERE id=".$id_laporan;
            	$result_select_laporan = mysqli_query($conn, $select_laporan);

            	$select_data = "SELECT judul_parameter, kode, tipe, nilai_awal FROM parameter_laporan WHERE id_laporan='".$id_laporan."'";
            	$result_select_data = mysqli_query($conn, $select_data);

            	// pass addRow() an array and it converts it to Excel XML format and sends
            	// it to the browser
        		while ($row_data = mysqli_fetch_array($result_select_laporan)){
        			$exporter->addRow(array($row_data["judul"]));
        			$result_data = mysqli_query($conn, $row_data['queri']);
        			$i = 0;
        			$columns = [];
        			$rows = [];
    				while($data = mysqli_fetch_assoc($result_data)){
    					foreach($data as $key => $value){
    						if($i == 0){
    							$columns[] = $key;
    						}
    						$rows[$i][] = $value;
    					}
    					$i++;
    				}

    				$exporter->addRow($columns);
    				foreach($rows as $row){
    					$exporter->addRow($row);
    				}

    				while($parameter = mysqli_fetch_assoc($result_select_data)){
    					$exporter->addRow(array($parameter['judul_parameter'], $parameter['nilai_awal']));
    				}
        		}

        	    $exporter->finalize();
            }else{
                $dompdf = new DOMPDF();
        		$html = file_get_contents('http://localhost/skripsi_v4copy/after-login/tampilan_PDF.php?id=' . $id_laporan);

        		$dompdf->load_html($html);
                $dompdf->render();
                $output = $dompdf->output();

                //save pdf
                file_put_contents($filepath, $output);
            }

            //sent email with attachment
            $mail = new PHPMailer;
            $mail->isSMTP();
            $mail->SMTPDebug = 2;
            $mail->Debugoutput = 'html';
            $mail->Host = 'smtp.gmail.com';
            $mail->Port = 587;
            $mail->SMTPSecure = 'tls';
            $mail->SMTPAuth = true;
            $mail->Username = "dheafiirda@gmail.com";
            $mail->Password = "arZ2b1Dn";
            $mail->setFrom('dheafiirda@gmail.com', 'Dea Firda');
            $mail->addAddress('indrawan.wilianto@gmail.com');
            $mail->Subject = 'PHPMailer GMail SMTP test';
            $mail->msgHTML("report dikirim dan berhasil", dirname(__FILE__));
            $mail->AltBody = 'This is a plain-text message body';
            $mail->AddAttachment($filepath, $filename);

            if (!$mail->send()) {
                echo "Mailer Error: " . $mail->ErrorInfo . "<br>";
            } else {
                echo "Report ID " . $id_laporan . " sent to " . $email . " in " . $format . " => (" . $filepath . ")<br>";
            }

            //insert last update
            $sql_insert = "INSERT INTO log_schedule (last_eksekusi, id_eksekusi_otomatis)
                    VALUES (NOW(), '$id')";
            $query_insert = mysqli_query($conn, $sql_insert);
        }
    }

    $query_per_minute = genereateQuery("Menit", "MINUTE");
    $query_per_hour = genereateQuery("Jam", "HOUR");
    $query_per_day = genereateQuery("Harian", "DAY");
    $query_per_week = genereateQuery("Mingguan", "WEEK");
    $query_per_month = genereateQuery("Bulanan", "MONTH");

    sendReport($conn, $query_per_minute);
    sendReport($conn, $query_per_hour);
    sendReport($conn, $query_per_day);
    sendReport($conn, $query_per_week);
    sendReport($conn, $query_per_month);