.-.-.-.-.-

home/emeraadmin/public_html/pages/emeraadmin/export_schedule.php000064400000014543151677422070021231 0ustar00<?php
// Enable error reporting
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);

// Include Composer's autoload file to load PHPSpreadsheet
require '../../vendor/autoload.php';

require_once __DIR__ . '/../../Classes/Database.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Function to export schedule tasks to Excel and return file contents
function exportScheduleToExcel($scheduleId) {
    // Establish database connection (replace with your connection logic)
    $db = new Database();
    $pdo = $db->getConn();

// SQL query to fetch tasks with subtask dates and times formatted
    $sql = "
    SELECT
        srv.name AS service_name,
        srv.region,
        t.frequency,
        st.subtask_date,
        st.completed,
        st.ignored
    FROM
        tasks t
    JOIN
        subtasks st ON t.id = st.task_id
    JOIN
        services srv ON t.service_id = srv.id
    WHERE
        t.schedule_id = :schedule_id
    ORDER BY
        srv.name, srv.region, t.frequency, st.subtask_date
    ";

$stmt = $pdo->prepare($sql);
    $stmt->bindValue(':schedule_id', $scheduleId, PDO::PARAM_INT);
    $stmt->execute();
    $tasks = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Create a new Spreadsheet object
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

// Set headers or titles in the Excel sheet
    $sheet->setCellValue('A1', 'Service Name');
    $sheet->setCellValue('B1', 'Region');
    $sheet->setCellValue('C1', 'Frequency');

// Get all distinct subtask dates
    $distinctDates = [];
    foreach ($tasks as $task) {
        $distinctDates[] = $task['subtask_date'];
    }
    $distinctDates = array_unique($distinctDates);
    sort($distinctDates);

// Set dates as headers in the Excel sheet
    $col = 'D';
    foreach ($distinctDates as $date) {
        $sheet->setCellValue($col . '1', $date);
        $sheet->getColumnDimension($col)->setWidth(15); // Set width to 15
        $col++;
    }

// Populate tasks data into Excel
    $row = 2;
    $taskGroups = [];
    foreach ($tasks as $task) {
        $taskKey = $task['service_name'] . '-' . $task['region'] . '-' . $task['frequency'];
        if (!isset($taskGroups[$taskKey])) {
            $taskGroups[$taskKey] = [
                'service_name' => $task['service_name'],
                'region' => $task['region'],
                'frequency' => $task['frequency'],
                'subtasks' => []
            ];
        }
        $taskGroups[$taskKey]['subtasks'][$task['subtask_date']] = ['completed' => $task['completed'], 'ignored' => $task['ignored']];
    }

foreach ($taskGroups as $taskGroup) {
        $sheet->setCellValue('A' . $row, $taskGroup['service_name']);
        $sheet->setCellValue('B' . $row, $taskGroup['region']);
        $sheet->setCellValue('C' . $row, $taskGroup['frequency']);

// Populate subtask statuses
        $col = 'D';
        foreach ($distinctDates as $date) {
            $value = '';
            $styleArray = null;
            if (isset($taskGroup['subtasks'][$date])) {
                $completed = $taskGroup['subtasks'][$date]['completed'];
                $ignored = $taskGroup['subtasks'][$date]['ignored'];
                if ($ignored) {
                    $value = 'Ignored';
                    $styleArray = [
                        'fill' => [
                            'fillType' => Fill::FILL_SOLID,
                            'color' => ['rgb' => 'FFFF00']
                        ],
                        'font' => [
                            'color' => ['rgb' => '9C6500']
                        ]
                    ];
                } elseif ($completed) {
                    $value = 'Completed';
                    $styleArray = [
                        'fill' => [
                            'fillType' => Fill::FILL_SOLID,
                            'color' => ['rgb' => 'C6EFCE']
                        ],
                        'font' => [
                            'color' => ['rgb' => '006100']
                        ]
                    ];
                } else {
                    $value = 'Not Completed';
                    $styleArray = [
                        'fill' => [
                            'fillType' => Fill::FILL_SOLID,
                            'color' => ['rgb' => 'FFC7CE']
                        ],
                        'font' => [
                            'color' => ['rgb' => '9C0006']
                        ]
                    ];
                }
            }

// Set value in corresponding cell
            $cellCoordinate = $col . $row;
            $sheet->setCellValue($cellCoordinate, $value);

// Apply style if applicable
            if ($styleArray) {
                $sheet->getStyle($cellCoordinate)->applyFromArray($styleArray);
            }

$col++;
        }

$row++;
    }

// Set column widths (optional)
    $sheet->getColumnDimension('A')->setWidth(25); // Adjust width as needed
    $sheet->getColumnDimension('B')->setWidth(20);
    $sheet->getColumnDimension('C')->setWidth(15);

// Set headers for Excel file download
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="schedule_export_' . date('Y-m-d') . '.xlsx"');
    header('Cache-Control: max-age=0');

// Save Excel file to PHP output (browser will download it)
    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
}

// Check if schedule_id is provided via GET request
if ($_SERVER['REQUEST_METHOD'] === 'GET' && isset($_GET['schedule_id'])) {
    $scheduleId = $_GET['schedule_id'];

try {
        // Export schedule to Excel and initiate download
        exportScheduleToExcel($scheduleId);
        exit; // Stop further execution after file download
    } catch (Exception $e) {
        // Handle exceptions (e.g., database errors, file saving errors)
        echo 'Error: ' . $e->getMessage();
    }
} else {
    // Handle case where schedule_id parameter is missing
    echo 'Error: Schedule ID parameter missing.';
}
?>
home/emeraadmin/public_html/pages/taskprovider/export_schedule.php000064400000011045151677423000021630 0ustar00<?php
//enable error reporting
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);

// Include Composer's autoload file to load PHPSpreadsheet
require '../../vendor/autoload.php';

require_once __DIR__ . '/../../Classes/Database.php';

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

// SQL query to fetch tasks with subtask dates and times formatted
    $sql = "
    SELECT
        srv.name AS service_name,
        srv.region,
        t.frequency,
        GROUP_CONCAT(DISTINCT st.subtask_date ORDER BY st.subtask_date SEPARATOR ', ') AS subtask_dates
    FROM
        tasks t
    JOIN
        subtasks st ON t.id = st.task_id
    JOIN
        services srv ON t.service_id = srv.id
    WHERE
        t.schedule_id = :schedule_id
    GROUP BY
        srv.name, srv.region, t.frequency, t.id
    ORDER BY
        srv.name, srv.region, t.frequency
";

$stmt = $pdo->prepare($sql);
    $stmt->bindValue(':schedule_id', $scheduleId, PDO::PARAM_INT);
    $stmt->execute();
    $tasks = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Create a new Spreadsheet object
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

// Set headers or titles in the Excel sheet
    $sheet->setCellValue('A1', 'Service Name');
    $sheet->setCellValue('B1', 'Region');
    $sheet->setCellValue('C1', 'Frequency');

// Get all distinct subtask dates
    $distinctDates = [];
    foreach ($tasks as $task) {
        $dates = explode(', ', $task['subtask_dates']);
        $distinctDates = array_merge($distinctDates, $dates);
    }
    $distinctDates = array_unique($distinctDates);
    sort($distinctDates);

// Populate tasks data into Excel
    $row = 2;
    foreach ($tasks as $task) {
        $sheet->setCellValue('A' . $row, $task['service_name']);
        $sheet->setCellValue('B' . $row, $task['region']);
        $sheet->setCellValue('C' . $row, $task['frequency']);

// Split subtask dates for the current task
        $dates = explode(', ', $task['subtask_dates']);
        $col = 'D';
        foreach ($distinctDates as $date) {
            // Check if the date exists for the current task or color black
            $value = in_array($date, $dates) ? 'X' : '';

// Set value in corresponding cell
            $cellCoordinate = $col . $row;
            $sheet->setCellValue($cellCoordinate, $value);

// Apply black color to non-X values
            if ($value !== 'X') {
                $spreadsheet->getActiveSheet()->getStyle($cellCoordinate)
                    ->getFont()->getColor()->setARGB(Color::COLOR_BLACK);
            }

$col++;
        }

$row++;
    }

// Save Excel file to PHP output (browser will download it)
    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
}

// Check if schedule_id is provided via GET request
if ($_SERVER['REQUEST_METHOD'] === 'GET' && isset($_GET['schedule_id'])) {
    $scheduleId = $_GET['schedule_id'];

/home/emeraadmin/.htpasswds/../public_html/ADVIA/../4d695/export_schedule.php.tar