22

✏️ Editing: daily_report.php

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

date_default_timezone_set('Asia/Riyadh'); // Set your timezone

// === 1. DB CONFIGURATION ===
$host = 'localhost';
$db = 'balubaid_core3_leads';
$user = 'balubaid_core3_leads';
$pass = 'Vision@2050';
$table = 'xx_workforcesaudia_leads_vu';
$date_column = 'request_date';

$conn = new mysqli($host, $user, $pass, $db);
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);

// === 2. FETCH DATA ===
$yesterday = date('Y-m-d', strtotime('-1 day'));
$query = "SELECT * FROM $table WHERE DATE($date_column) = '$yesterday'";
$result = $conn->query($query);
if (!$result) die("Query failed: " . $conn->error);

// === 3. CREATE EXCEL FILE ===
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Write headers
$headers = array_keys($result->fetch_assoc());
$result->data_seek(0);
$sheet->fromArray($headers, NULL, 'A1');

// Write rows
$row = 2;
while ($data = $result->fetch_assoc()) {
    $sheet->fromArray(array_values($data), NULL, 'A' . $row);
    $row++;
}

// === 4. EXPORT FILE TO 'exports' FOLDER ===
$filename = 'daily_report_' . date('Y-m-d') . '.xlsx';
$exportPath = __DIR__ . '/exports';

if (!file_exists($exportPath)) {
    mkdir($exportPath, 0775, true); // create folder if it doesn't exist
}

$filepath = $exportPath . '/' . $filename;
$writer = new Xlsx($spreadsheet);
$writer->save($filepath);

// === 5. EMAIL THE EXCEL FILE ===
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

require 'vendor/autoload.php';

$mail = new PHPMailer(true);

try {
    // Server settings
   $mail->isSMTP();
$mail->Host       = 'smtp-relay.brevo.com';  // or the actual mail server from cPanel
$mail->SMTPAuth   = true;
$mail->Username   = '907035001@smtp-brevo.com';
$mail->Password   = '9H0TS1tVZJUQFKv2';
$mail->SMTPSecure = 'tls'; // or 'tls' depending on your cPanel host
$mail->Port       = 587;   // or 587 for TLS

// Recipients
    $mail->setFrom('khurram.dhedhi@core3consultancy.com', 'Khurram Dhedhi');
    $mail->addAddress('afnan.alsanie@workforcesaudia.com');
    $mail->addAddress('rani.sokkar@workforcesaudia.com');
    $mail->addCc('khurram.dhedhi@core3consultancy.com');

// Attachments
    $mail->addAttachment($filepath);

// Content
    $mail->isHTML(false);
    $mail->Subject = 'Workforce Saudi Stayin Daily Leads - ' . date('Y-m-d');
    $mail->Body    = 'Attached is the daily leads for stayin campaign.';

$mail->send();
    echo "Email sent successfully.";
} catch (Exception $e) {
    echo "Message could not be sent. Error: {$mail->ErrorInfo}";
}
// Optional: Uncomment to delete file after sending
// unlink($filepath);

$conn->close();
?>

MR SKB