File "export.php"

Full path: /home2/santhos2/public_html/bharath-coe.firstmatrix.in.net/api/staff/export.php
File size: 7.5 KiB (7679 bytes)
MIME-type: text/x-php
Charset: utf-8

Download   Open   Back

<?php
// Suppress all error output
error_reporting(0);
ini_set('display_errors', 0);

require_once '../../config/config.php';
require_once '../../config/date_helper.php';

try {
    // Get filter parameters
    $filter_college = $_GET['filter_college'] ?? '';
    $filter_role = $_GET['filter_role'] ?? '';
    $filter_department = $_GET['filter_department'] ?? '';
    $filter_status = $_GET['filter_status'] ?? '';
    $format = $_GET['format'] ?? 'xlsx'; // Default to Excel
    
    // Check if department_id column exists
    $check_dept_column = $pdo->query("SHOW COLUMNS FROM staff LIKE 'department_id'");
    $has_department_column = $check_dept_column->rowCount() > 0;
    
    // Build query based on department column availability
    // Check external_college_name support
    $check_ext_col = $pdo->query("SHOW COLUMNS FROM staff LIKE 'external_college_name'");
    $has_external_college = $check_ext_col->rowCount() > 0;
    $collegeSelect = $has_external_college ? "COALESCE(s.external_college_name, c.college_name)" : "c.college_name";

    if ($has_department_column) {
        $sql = "SELECT s.staff_code, s.first_name, s.last_name, s.email, s.phone, s.status,
                       $collegeSelect AS college_name, d.department_name, GROUP_CONCAT(srt.role_name SEPARATOR ', ') as roles
                FROM staff s 
                LEFT JOIN colleges c ON s.college_id = c.college_id 
                LEFT JOIN departments d ON s.department_id = d.department_id
                LEFT JOIN staff_roles sr ON s.staff_id = sr.staff_id
                LEFT JOIN staff_role_types srt ON sr.role_id = srt.role_id
                WHERE 1=1";
    } else {
        $sql = "SELECT s.staff_code, s.first_name, s.last_name, s.email, s.phone, s.status,
                       $collegeSelect AS college_name, NULL as department_name, GROUP_CONCAT(srt.role_name SEPARATOR ', ') as roles
                FROM staff s 
                LEFT JOIN colleges c ON s.college_id = c.college_id 
                LEFT JOIN staff_roles sr ON s.staff_id = sr.staff_id
                LEFT JOIN staff_role_types srt ON sr.role_id = srt.role_id
                WHERE 1=1";
    }
    
    $params = [];
    
    if (!empty($filter_college)) {
        $sql .= " AND s.college_id = ?";
        $params[] = $filter_college;
    }
    
    if (!empty($filter_department) && $has_department_column) {
        $sql .= " AND s.department_id = ?";
        $params[] = $filter_department;
    }
    
    if (!empty($filter_role)) {
        $sql .= " AND EXISTS (SELECT 1 FROM staff_roles sr2 JOIN staff_role_types srt2 ON sr2.role_id = srt2.role_id WHERE sr2.staff_id = s.staff_id AND srt2.role_name = ?)";
        $params[] = $filter_role;
    }
    
    if (!empty($filter_status)) {
        $sql .= " AND s.status = ?";
        $params[] = $filter_status;
    }
    
    $sql .= " GROUP BY s.staff_id ORDER BY s.staff_code";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $staff = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Generate filename with timestamp
    $filename = 'staff_export_' . date('Y-m-d_H-i-s') . '.xlsx';
    
    // Excel Export
    if (file_exists('../../vendor/autoload.php')) {
        require_once '../../vendor/autoload.php';
        
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        
        // Set headers
        $headers = [
            'Staff Code',
            'First Name',
            'Last Name',
            'Full Name',
            'Email',
            'Phone',
            'College',
            'Department',
            'Roles',
            'Status'
        ];
        
        $sheet->fromArray($headers, null, 'A1');
        
        // Style header row
        $headerStyle = [
            'font' => ['bold' => true],
            'fill' => [
                'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                'startColor' => ['rgb' => '007BFF']
            ],
            'font' => ['color' => ['rgb' => 'FFFFFF']],
            'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER]
        ];
        
        $sheet->getStyle('A1:J1')->applyFromArray($headerStyle);
        
        // Add data
        $row = 2;
        foreach ($staff as $staffMember) {
            $fullName = trim($staffMember['first_name'] . ' ' . $staffMember['last_name']);
            
            $sheet->fromArray([
                $staffMember['staff_code'],
                $staffMember['first_name'],
                $staffMember['last_name'],
                $fullName,
                $staffMember['email'],
                $staffMember['phone'],
                $staffMember['college_name'],
                $staffMember['department_name'] ?? 'N/A',
                $staffMember['roles'],
                $staffMember['status']
            ], null, "A{$row}");
            $row++;
        }
        
        // Set column widths
        $sheet->getColumnDimension('A')->setWidth(15); // Staff Code
        $sheet->getColumnDimension('B')->setWidth(15); // First Name
        $sheet->getColumnDimension('C')->setWidth(15); // Last Name
        $sheet->getColumnDimension('D')->setWidth(25); // Full Name
        $sheet->getColumnDimension('E')->setWidth(25); // Email
        $sheet->getColumnDimension('F')->setWidth(15); // Phone
        $sheet->getColumnDimension('G')->setWidth(20); // College
        $sheet->getColumnDimension('H')->setWidth(20); // Department
        $sheet->getColumnDimension('I')->setWidth(30); // Roles
        $sheet->getColumnDimension('J')->setWidth(12); // Status
        
        // Set headers for download
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        
        // Write file
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    } else {
        // Fallback to CSV if PhpSpreadsheet not available
        $csvFilename = 'staff_export_' . date('Y-m-d_H-i-s') . '.csv';
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment;filename="' . $csvFilename . '"');
        header('Cache-Control: max-age=0');
        
        // Add headers
        $headers = [
            'Staff Code',
            'First Name',
            'Last Name',
            'Full Name',
            'Email',
            'Phone',
            'College',
            'Department',
            'Roles',
            'Status'
        ];
        
        echo implode(',', $headers) . "\n";
        
        // Add data
        foreach ($staff as $staffMember) {
            $fullName = trim($staffMember['first_name'] . ' ' . $staffMember['last_name']);
            
            echo implode(',', [
                '"' . $staffMember['staff_code'] . '"',
                '"' . $staffMember['first_name'] . '"',
                '"' . $staffMember['last_name'] . '"',
                '"' . $fullName . '"',
                '"' . $staffMember['email'] . '"',
                '"' . $staffMember['phone'] . '"',
                '"' . $staffMember['college_name'] . '"',
                '"' . ($staffMember['department_name'] ?? 'N/A') . '"',
                '"' . $staffMember['roles'] . '"',
                '"' . $staffMember['status'] . '"'
            ]) . "\n";
        }
    }
    
} catch (Exception $e) {
    http_response_code(500);
    echo "Error generating export file: " . $e->getMessage();
}
?>