<?php


require('../config.php');
require '../../../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Xls;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;


// ini_set('display_errors', 1);
// ini_set('display_startup_errors', 1);
// error_reporting(E_ALL);

if(!isset($_GET['users'])){
    exit();
}

// Fetch records from database  
// $e_ids = "'" . join("','", $_GET['users']) . "'";

// ########################################################
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$sheet->setCellValue('A1', 'Employee Id')
    ->setCellValue('B1', 'Name')
    ->setCellValue('C1', 'Email')
    ->setCellValue('D1', 'Phone')
    ->setCellValue('E1', 'Gender')
    ->setCellValue('F1', 'Designation')
    ->setCellValue('G1', 'Department')
    ->setCellValue('H1', 'Leader')
    ->setCellValue('I1', 'Role')
    ->setCellValue('J1', 'DOB')
    ->setCellValue('K1', 'Joining Date')
    ->setCellValue('L1', 'probation_date')
    ->setCellValue('M1', 'con_after_prob')
    ->setCellValue('N1', 'notice_period')
    ->setCellValue('O1', 'Working Status')
    ->setCellValue('P1', 'Working Status Date')
    ->setCellValue('Q1', 'Pan Number')
    ->setCellValue('R1', 'Account Number')
    ->setCellValue('S1', 'IFSC Code')
    ->setCellValue('T1', 'ESIC Status (For Previous ESIC Number)')
    ->setCellValue('U1', 'ESIC Number')
    ->setCellValue('V1', 'EPF Number Status (For Previous UAN Number)')
    ->setCellValue('W1', 'EPF Number')
    ->setCellValue('X1', 'Opting EPF Status (Yes < 15000 < Choice or No)')
    ->setCellValue('Y1', 'EPF Applicable Status')
    ->setCellValue('Z1', 'Consider As');

// style for excel
$styleArray1 = [
    'font' => [
        'bold' => true,
        'size' => 12,
    ],
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,
        'vertical' => Alignment::VERTICAL_BOTTOM,
    ],
];
$styleArray2 = [
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,
        'vertical' => Alignment::VERTICAL_BOTTOM,
    ],
];

$sheet->getStyle('A1:Z1')
    ->applyFromArray($styleArray1);

foreach (range('A', 'Z') as $col) {
    $sheet->getColumnDimension($col)->setAutoSize(true);
}

// $sheet->getStyle('A1:S1')
//     ->applyFromArray($styleArray2);

// use in => all_users_get.php, myProfile.php, exportEmployees.php
$users_sql = <<<EOT
SELECT * FROM
(
    SELECT
        t1.*,
        t3.department_name,
        t4.designation_name
    FROM (
        SELECT 
            p1.*,
            (CASE
            WHEN COALESCE(p2.promote_to,'')<>'' THEN p2.promote_to
            ELSE p1.designation
            END) AS new_designation
        FROM users p1
        LEFT JOIN (
            SELECT
                *
            FROM (
                SELECT 
                    id,
                    employee_id, 
                    promote_to, 
                    applicable_from, 
                    (CASE 
                    WHEN COALESCE(applicable_to,'')='' THEN DATE_FORMAT(CURRENT_DATE, '%Y-%m')
                    ELSE applicable_to
                    END) as applicable_to 
                FROM promotions
            ) final_promotions
            WHERE (DATE_FORMAT(CURRENT_DATE, '%Y-%m') BETWEEN applicable_from AND applicable_to)
        ) p2 ON p1.employee_id=p2.employee_id
    ) t1
    INNER JOIN departments AS t3 ON t3.id = t1.department
    LEFT JOIN designations AS t4 ON t4.id=t1.new_designation
) join_table
EOT;

$i = 2;
// $users = mysqli_query($db, "SELECT t1.*, t2.department_name, t3.designation_name FROM users t1 INNER JOIN departments t2 ON t1.department=t2.id INNER JOIN designations t3 ON t1.designation=t3.id WHERE t1.employee_id IN($e_ids) ");
$users = mysqli_query($db, $users_sql);

while ($row = mysqli_fetch_assoc($users)) {

    $sheet->setCellValue('A' . $i, $row['employee_id'])
        ->setCellValue('B' . $i, $row['name'])
        ->setCellValue('C' . $i, $row['email'])
        ->setCellValue('D' . $i, $row['phone'])
        ->setCellValue('E' . $i, $row['gender'])
        ->setCellValue('F' . $i, $row['designation_name'])
        ->setCellValue('G' . $i, $row['department_name'])
        ->setCellValue('H' . $i, $row['leader'])
        ->setCellValue('I' . $i, $row['role'])
        ->setCellValue('J' . $i, $row['dob'])
        ->setCellValue('K' . $i, $row['joining_date'])
        ->setCellValue('L' . $i, $row['probation_date'])
        ->setCellValue('M' . $i, $row['con_after_prob'])
        ->setCellValue('N' . $i, $row['notice_period'])
        ->setCellValue('O' . $i, $row['working_status'])
        ->setCellValue('P' . $i, $row['working_status_date'])
        ->setCellValue('Q' . $i, $row['pan_no'])
        ->setCellValue('R' . $i, $row['account_no'])
        ->setCellValue('S' . $i, $row['ifsc_code'])
        ->setCellValue('T' . $i, $row['esi_status'])
        ->setCellValue('U' . $i, $row['esi_no'])
        ->setCellValue('V' . $i, $row['uan_no_status'])
        ->setCellValue('W' . $i, $row['uan_no'])
        ->setCellValue('X' . $i, $row['opting_epf_status'])
        ->setCellValue('Y' . $i, $row['epf_applicable_status'])
        ->setCellValue('Z' . $i, $row['consider_as']);

    $sheet->getStyle('A' . $i . ':Z' . $i)
        ->applyFromArray($styleArray2);

    $i++;
}

$writer = new Xlsx($spreadsheet);
$fileName = uniqid()."_".time().".xlsx";

// save in the folder
$writer->save("../files_download/$fileName");

$q = mysqli_query($db,"INSERT INTO download_files (file_name, tag) VALUES('$fileName', 'Employees')");
if($q){
    echo json_encode(["status"=>200, 'message'=>'File Downloaded Successfully!']);
}else{
    unlink("../files_download/$fileName");
    echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
}

// // Headers for download 
// header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
// header("Content-Disposition: attachment; filename=\"$fileName\"");
// $writer->save('php://output');

exit();
