<?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['from']) || !isset($_GET['to'])){
    exit();
}


// Fetch records from database  
$from = date("Y-m", strtotime($_GET['from']));
$to = date("Y-m", strtotime($_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 Y", strtotime($from)) . ' - to - ' . date("M Y", strtotime($to)))
    ->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', '');

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


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

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

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

$sheet->mergeCells('J2:N2')
    ->getStyle('J2:N2')
    ->applyFromArray($styleArray1);
// ########################################################

// ########################################################
$sheet->setCellValue('A3', 'Employee Id')
    ->setCellValue('B3', 'Name')
    ->setCellValue('C3', 'Department')
    ->setCellValue('D3', 'Basic Salary')
    ->setCellValue('E3', 'HRA')
    ->setCellValue('F3', 'Special Allowance')
    ->setCellValue('G3', 'Books & Periodicals')
    ->setCellValue('H3', 'Company EPF')
    ->setCellValue('I3', 'Employee EPF')
    ->setCellValue('J3', 'TDS')
    ->setCellValue('K3', 'Company EPF')
    ->setCellValue('L3', 'Employee EPF')
    ->setCellValue('M3', 'Company ESI')
    ->setCellValue('N3', 'Employee ESI')
    ->setCellValue('O3', 'Net Payable')
    ->setCellValue('P3', 'Bonus')
    ->setCellValue('Q3', 'Incentive');

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

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

$i = 4;
$yearlySalary = mysqli_query($db, "SELECT `employee_id`, SUM(`working_day`) as `working_day`, SUM(`leave_days`) as `leave_days`, SUM(`half_days`) as `half_days`, SUM(`allowed_leave_days`) as `allowed_leave_days`, SUM(`net_paid_days`) as `net_paid_days`, SUM(`total_monthly_salary`) as `total_monthly_salary`, SUM(`working_basic_salary`) as `working_basic_salary`, SUM(`working_hra`) as `working_hra`, SUM(`working_sp_allowance`) as `working_sp_allowance`, SUM(`working_books_per`) as `working_books_per`, SUM(`working_company_epf`) as `working_company_epf`, SUM(`working_employee_epf`) as `working_employee_epf`, SUM(`working_tds`) as `working_tds`, SUM(`working_company_esi`) as `working_company_esi`, SUM(`working_employee_esi`) as `working_employee_esi` , SUM(`net_payable`) as `net_payable`, SUM(`bonus`) as `bonus`, SUM(`incentive`) as `incentive` From `employee_yearly_salary` WHERE (`which_month`>='$from' AND `which_month`<='$to') GROUP by `employee_id` order by employee_id asc ");

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

    $user = mysqli_fetch_assoc(mysqli_query($db, "select u.*, d.department_name from users u JOIN depratments d on u.department=d.id where employee_id='$row[employee_id]'"));

    $working_basic_salary = $row['working_basic_salary'];
    $working_hra = $row['working_hra'];
    $working_sp_allowance = $row['working_sp_allowance'];
    $working_books_per = $row['working_books_per'];
    $working_company_epf = $row['working_company_epf'];
    $working_employee_epf = $row['working_employee_epf'];
    $working_tds = $row['working_tds'];
    $working_company_esi = $row['working_company_esi'];
    $working_employee_esi = $row['working_employee_esi'];
    $payableSalary = $row['net_payable'];
    $bonus = $row['bonus'];
    $incentive = $row['incentive'];
    // $which_month = date("M Y", strtotime($row['which_month']));


    $sheet->setCellValue('A' . $i, $row['employee_id'])
        ->setCellValue('B' . $i, $user['name'])
        ->setCellValue('C' . $i, $user['department_name'])
        ->setCellValue('D' . $i, $working_basic_salary)
        ->setCellValue('E' . $i, $working_hra)
        ->setCellValue('F' . $i, $working_sp_allowance)
        ->setCellValue('G' . $i, $working_books_per)
        ->setCellValue('H' . $i, $working_company_epf)
        ->setCellValue('I' . $i, $working_employee_epf)
        ->setCellValue('J' . $i, $working_tds)
        ->setCellValue('K' . $i, $working_company_epf)
        ->setCellValue('L' . $i, $working_employee_epf)
        ->setCellValue('M' . $i, $working_company_esi)
        ->setCellValue('N' . $i, $working_employee_esi)
        ->setCellValue('O' . $i, $payableSalary)
        ->setCellValue('P' . $i, $bonus)
        ->setCellValue('Q' . $i, $incentive);

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

    $i++;
}

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

// save in the folder
$writer->save("../files_download/$fileName");
$tag = "salary_yearly_" . str_replace(" ", "-", $from) . "_" . str_replace(" ", "-", $to);
$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();
