<?php
ini_set('display_errors', 1);

require_once $base_path_to_learning . "vendor/autoload.php";

use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use \PhpOffice\PhpSpreadsheet\Style\Border;
use \PhpOffice\PhpSpreadsheet\Style\Font;

function removeBranchIdAndAddBranchName($array) {
    foreach ($array['faculty_info'] as &$faculty) {
        if (isset($faculty['branch_id'])) {
            $faculty['branch_name'] = $faculty['branch_id']['name'];
            unset($faculty['branch_id']);
        }
    }
    return $array;
}

$facultyFields = array(
    'name' => 'Name',
    "branch_name"=>'Branch Name',
    'father_name' => 'Father Name',
    'mother_name' => 'Mother Name',
    'spouse_name' => 'Spouse Name',
    'mobile' => 'Mobile',
    'email' => 'Email',
    'address' => 'Address',
    'city' => 'City',
    'pincode' => 'Pincode',
    'dob' => 'DOB',
    'gender' => 'Gender',
    'designation' => 'Designation',
    'nationality' => 'Nationality',
    'role' => 'Role',
    'city' => 'City',
    'state' => 'State',
    'country' => 'Country',
    'date_of_joining' => 'Date of Joining',
    'userid' => 'User ID'
);

$data = GetBasicfacultyDetail($conn_rjllearning, $conn_rjllearning_specific, $institution_id, $branch_id, $search_string, $limit, $offset, $additionaldata, $role_option, $faculty_status);

$result = removeBranchIdAndAddBranchName($data);
$faculty = $result['faculty_info'];

$fields_to_remove = ['id','branch_id'];

$faculty_data = removeKeys($faculty, $fields_to_remove);
if (empty($faculty_data)) {
    errorreturn("No faculty data found.");
}
$values = getArrayValues($faculty_data);
$keys = getArrayKeys($faculty_data[0]);

$formattedKeys = getArrayKeys($faculty_data[0]);

foreach ($keys as $index => $k) {
    $formattedKeys[$index] = getCorrespondingValue($facultyFields, $k);
}

$data = array("values" => $values, "keys" => $formattedKeys);
create_facultyExcel($conn_rjllearning, $conn_rjllearning_specific, $data, $institution_id, $branch_id);

function create_facultyExcel($conn_rjllearning, $conn_rjllearning_specific, $data,  $institution_id, $branch_id)
{
    // Start output buffering
    ob_start();

    $values_count = count($data['values']);
    $keys_count = count($data['keys']);
    $values = $data['values'];
    $keys = $data['keys'];
    $institutions = getInstitution($conn_rjllearning, $institution_id);
    $additional_data = ['branch_code','pincode','address','contact_number','email'];

    $columns = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');

    // Create a new Spreadsheet instance
    $spreadsheet = new Spreadsheet();

    // Set values in the spreadsheet
    $sheet = $spreadsheet->getActiveSheet();
    // Set the column width for columns A to G
    for ($column = 'A'; $column <= 'G'; $column++) {
        $sheet->getColumnDimension($column)->setWidth(19); // Set the width as desired (15 in this example)
    }
    
    $branch_name = "All Branches"; // Default value
    
    if ($branch_id !== "all") {
        $branch = getBranchInfo($conn_rjllearning_specific, $institution_id, $branch_id,$additional_data);
        $branch_name = $branch['name']; // Get branch name if branch_id is specific
    }
    $spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A1', $institutions['institution_name'])
    ->setCellValue('A2', $branch_name);

// Set alignment for cells A1 and A2
    $spreadsheet->getActiveSheet()
        ->getStyle('A1')
        ->getAlignment()
        ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
        ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

    $spreadsheet->getActiveSheet()
        ->getStyle('A2')
        ->getAlignment()
        ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
        ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

    // Get the cell style and font
    $style = $sheet->getStyle('A1');
    $font = $style->getFont();
    // Set the font to bold
    $font->setBold(true);
    $rowCount = 4;
    $column = 'A';
    // Set column headers
    foreach ($keys as $item) {
        $sheet->setCellValue($column . $rowCount, $item);
        $sheet->getStyle($column . $rowCount, $item)->getFont()->setBold(true);
        $sheet->getStyle($column . $rowCount)->getAlignment()
        ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
              ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $sheet->getStyle($column . $rowCount)->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
        $column++;
    }

    $rowCount = 5;
    // Set row data
    // foreach ($values as $v) {
    //     $column = 'A';
    //     foreach ($v as $item1) {

    //         $item_data = $item1;
    //         $sheet->setCellValue($column . $rowCount, $item_data);
    //         $alignment = $sheet->getStyle($column . $rowCount)->getAlignment();
    //         $column++;
    //     }
    //     $rowCount++;
    // }
    // Set headers for download
    
    foreach ($values as $v) {
    $column = 'A';
    foreach ($v as $item1) {
        // Check if the column is for mobile number
        $columnIndex = ord($column) - ord('A'); // Convert column letter to numeric index
        if ($keys[$columnIndex] === 'Mobile') {
            // Set the cell format to text
            $sheet->getStyle($column . $rowCount)->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);
            // Prepend an apostrophe to force Excel to treat it as text
            $sheet->setCellValue($column . $rowCount, "'" . $item1);
        } elseif (in_array($keys[$columnIndex], ['DOB', 'Date of Joining']) && !empty($item1)) {
            // Convert the date format from Y-m-d to d-m-Y
            $formattedDate = date('d-m-Y', strtotime($item1));
            $sheet->setCellValue($column . $rowCount, $formattedDate);
        } else {
            $sheet->setCellValue($column . $rowCount, $item1);
        }
        $sheet->getStyle($column . $rowCount)->getAlignment()
            ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
            ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
            
        $sheet->getStyle($column . $rowCount)->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
        $column++;
    }
    $rowCount++;
}

    // Auto-size columns based on content
    foreach (range('A', $sheet->getHighestDataColumn()) as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }

    // Start output buffering
    ob_start();

    // Create Excel writer object
    $writer = new Xlsx($spreadsheet);

    // Save the spreadsheet to a variable instead of a file
    $writer->save('php://output');

    // Get the content that was written to the output buffer
    $excelData = ob_get_contents();

    // Clear the output buffer
    ob_end_clean();

    // Set appropriate headers for download
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="Faculty Report - Rejoice Learning.xlsx"');
    header('Cache-Control: max-age=0');

    // Output the Excel file data
    echo $excelData;
    exit;
}

