<?php


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


require_once "../salaryCodeCalculate.php";
require_once('../salaryCode.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']) . "'";

// Fetch records from database  
$firstDayLast = $_GET['from'];
$lastDayLast = $_GET['to'];


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

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

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

$styleArray3 = [
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,
        'vertical' => Alignment::VERTICAL_BOTTOM,
    ],
];
$sheetTitleArray = [
    'font' => [
        'bold' => true,
        'size' => 18
    ],
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,
        'vertical' => Alignment::VERTICAL_BOTTOM,
    ],
];
// ########################################################

// ########################################################
$sheet->setCellValue('A1', date("M d Y", strtotime($firstDayLast)) . ' - to - ' . date("M d Y", strtotime($lastDayLast)))
    ->setCellValue('B1', '')
    ->setCellValue('C1', '')
    ->setCellValue('D1', '')
    ->setCellValue('E1', '')
    ->setCellValue('F1', '')
    ->setCellValue('G1', '')
    ->setCellValue('H1', '')
    ->setCellValue('I1', '')
    ->setCellValue('J1', '')
    ->setCellValue('K1', '')
    ->setCellValue('L1', '')
    ->setCellValue('M1', '')
    ->setCellValue('N1', '')
    ->setCellValue('O1', '')
    ->setCellValue('P1', '')
    ->setCellValue('Q1', '')
    ->setCellValue('R1', '')
    ->setCellValue('S1', '')
    ->setCellValue('T1', '')
    ->setCellValue('U1', '')
    ->setCellValue('V1', '')
    ->setCellValue('W1', '')
    ->setCellValue('X1', '');

$sheet->mergeCells('A1:X1')
    ->getStyle('A1:X1')
    ->applyFromArray($sheetTitleArray);
// ########################################################



// ########################################################
$sheet->setCellValue('A2', 'Details')
    ->setCellValue('B2', '')
    ->setCellValue('C2', '')
    ->setCellValue('D2', '')
    ->setCellValue('E2', 'Days Details')
    ->setCellValue('F2', '')
    ->setCellValue('G2', '')
    ->setCellValue('H2', '')
    ->setCellValue('I2', '')
    ->setCellValue('J2', '')
    ->setCellValue('K2', 'Actual Monthly salary')
    ->setCellValue('L2', '')
    ->setCellValue('M2', '')
    ->setCellValue('N2', '')
    ->setCellValue('O2', '')
    ->setCellValue('P2', '')
    ->setCellValue('Q2', 'Deduction')
    ->setCellValue('R2', '')
    ->setCellValue('S2', '')
    ->setCellValue('T2', '')
    ->setCellValue('U2', '')
    ->setCellValue('V2', '')
    ->setCellValue('W2', '')
    ->setCellValue('X2', '');

$sheet->mergeCells('A2:D2')
    ->getStyle('A2:D2')
    ->applyFromArray($styleArray1);

$sheet->mergeCells('E2:J2')
    ->getStyle('E2:J2')
    ->applyFromArray($styleArray1);

$sheet->mergeCells('K2:P2')
    ->getStyle('K2:P2')
    ->applyFromArray($styleArray1);

$sheet->mergeCells('Q2:U2')
    ->getStyle('Q2:U2')
    ->applyFromArray($styleArray1);
// ##############################################


// ##############################################
$sheet->setCellValue('A3', 'Employee Id')
    ->setCellValue('B3', 'Name')
    ->setCellValue('C3', 'Account Number')
    ->setCellValue('D3', 'Department')
    ->setCellValue('E3', 'Total Working Day')
    ->setCellValue('F3', 'Full Day Leave Taken')
    ->setCellValue('G3', 'Half Day Leave Taken')
    ->setCellValue('H3', 'Allowed Leave')
    ->setCellValue('I3', 'Net Paid Days')
    ->setCellValue('J3', 'Total Monthly Salary')
    ->setCellValue('K3', 'Basic Salary')
    ->setCellValue('L3', 'HRA')
    ->setCellValue('M3', 'Special Allowance')
    ->setCellValue('N3', 'Books & Periodicals')
    ->setCellValue('O3', 'Company EPF')
    ->setCellValue('P3', 'Employee EPF')
    ->setCellValue('Q3', 'TDS')
    ->setCellValue('R3', 'Company EPF')
    ->setCellValue('S3', 'Employee EPF')
    ->setCellValue('T3', 'Company ESI')
    ->setCellValue('U3', 'Employee ESI')
    ->setCellValue('V3', 'Net Payable')
    ->setCellValue('W3', 'Bonus')
    ->setCellValue('X3', 'Incentive');

$sheet->getStyle('A3:X3')
    ->applyFromArray($styleArray2);
// ##############################################

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


$i = 4;
$join_month = date("Y-m", strtotime($firstDayLast));
$wsDate = date("Y-m", strtotime($lastDayLast));
$query = "SELECT u.employee_id, u.name, d.department_name, u.joining_date, u.working_status_date, u.account_no, '$firstDayLast' as firstDayLast, '$lastDayLast' as lastDayLast
        FROM users as u 
        JOIN attendance AS a ON u.employee_id = a.employee_id 
        JOIN departments as d on u.department=d.id
        WHERE u.employee_id in ($e_ids) 
        AND (STR_TO_DATE(a.which_month, '%M %d %Y') BETWEEN '$firstDayLast' AND '$lastDayLast')
        AND (u.joining_date IS NULL OR u.joining_date = '' OR DATE_FORMAT(STR_TO_DATE(u.joining_date, '%Y-%m-%d'),'%Y-%m') <= '$join_month')
        AND (u.working_status_date IS NULL OR u.working_status_date = '' OR DATE_FORMAT(STR_TO_DATE(u.working_status_date, '%Y-%m-%d'),'%Y-%m') >= '$wsDate')
        GROUP BY u.id 
        ORDER BY u.id ASC";

$users = mysqli_query($db, $query);

$newFirstDayLast = $firstDayLast;
$newLastDayLast = $lastDayLast;
while ($user = mysqli_fetch_assoc($users)) {

    $s_code = salaryCode($user, $firstDayLast, $firstDayLast);
    // print_r($s_code);
    $monthSalaryDetails = $s_code['monthSalaryDetails'];
    $monthlySalary = $s_code['monthlySalary'];
    $l = $s_code['l'];
    $hd = $s_code['hd'];
    $totalLeave = $s_code['totalLeave'];
    $totalWorkingDay = $s_code['totalWorkingDay'];
    $working_salary = $s_code['working_salary'];
    $working_day_salary = $s_code['working_day_salary'];
    $working_tds = $s_code['working_tds'];
    $working_employee_epf = $s_code['working_employee_epf'];
    $working_company_epf = $s_code['working_company_epf'];
    $working_company_esi = $s_code['working_company_esi'];
    $working_employee_esi = $s_code['working_employee_esi'];
    $deduction = $s_code['deduction'];
    $payableSalary = $s_code['payableSalary'];
    $bonus = $s_code['bonus'];
    $incentive = $s_code['incentive'];
    $monthDay = $s_code['monthDay'];
    $leaveOffer = 0;
    $working_basic_salary = $s_code['working_basic_salary'];
    $working_hra = $s_code['working_hra'];
    $working_sp_allowance = $s_code['working_sp_allowance'];
    $working_books_per = $s_code['working_books_per'];

    $sheet->setCellValue('A' . $i, $user['employee_id'])
        ->setCellValue('B' . $i, $user['name'])
        ->setCellValue('C' . $i, $user['account_no'])
        ->setCellValue('D' . $i, $user['department_name'])
        ->setCellValue('E' . $i, $monthDay)
        ->setCellValue('F' . $i, $l)
        ->setCellValue('G' . $i, $hd)
        ->setCellValue('H' . $i, $leaveOffer)
        ->setCellValue('I' . $i, $totalWorkingDay)
        ->setCellValue('J' . $i, $monthlySalary)
        ->setCellValue('K' . $i, $working_basic_salary)
        ->setCellValue('L' . $i, $working_hra)
        ->setCellValue('M' . $i, $working_sp_allowance)
        ->setCellValue('N' . $i, $working_books_per)
        ->setCellValue('O' . $i, $working_company_epf)
        ->setCellValue('P' . $i, $working_employee_epf)
        ->setCellValue('Q' . $i, $working_tds)
        ->setCellValue('R' . $i, $working_company_epf)
        ->setCellValue('S' . $i, $working_employee_epf)
        ->setCellValue('T' . $i, $working_company_esi)
        ->setCellValue('U' . $i, $working_employee_esi)
        ->setCellValue('V' . $i, $payableSalary)
        ->setCellValue('W' . $i, $bonus)
        ->setCellValue('X' . $i, $incentive);

    $sheet->getStyle('A' . $i . ':X' . $i)
        ->applyFromArray($styleArray3);

    $i++;
}

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

// save in the folder
$writer->save("../files_download/$fileName");
$tag = "salary_" . str_replace(" ", "-", $firstDayLast) . "_" . str_replace(" ", "-", $lastDayLast);
$q = mysqli_query($db,"INSERT INTO download_files (file_name, tag) VALUES('$fileName', '$tag')");
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();
