<?php
session_start();
$date=explode('-',date("Y-m-d", strtotime("last day of previous month")));
$month=$date[1];
$year=$date[0];

$monthNum  = $month;
$dateObj   = DateTime::createFromFormat('!m', $monthNum);
$monthName = strtoupper($dateObj->format('F'));
// CONNECT TO DATABASE
// ! CHANGE THESE SETTINGS TO YOUR OWN !
require_once '../system/conn.php';
// CREATE PHPSPREADSHEET OBJECT
require "excel_test/vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// CREATE A NEW SPREADSHEET + POPULATE DATA
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$spreadsheet->getActiveSheet()->mergeCells('A1:H1');
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);

$styleArray = [
    'font' => [
        'bold' => true,
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
        'rotation' => 90,
        'startColor' => [
            'argb' => 'FFA0A0A0',
        ],
        'endColor' => [
            'argb' => 'FFFFFFFF',
        ],
    ],
];
$styleArray1 = [
    'font' => [
        'bold' => true,
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
    ],
];

$spreadsheet->getActiveSheet()->getStyle('A3:H3')->applyFromArray($styleArray1);
$spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);

$sheet->setTitle('Attendance sheet');

    $stmt = $conn->prepare("SELECT * FROM `salary_increment` ");
    //print_r($stmt);exit;  
    $stmt->execute();
    $a = 1;
    $i = 4;
    $sheet->setCellValue('A1', ' Current Attendance Sheet '.strtoupper($monthName).' '. $year);
    $sheet->setCellValue('A3', 'SR NO');
    $sheet->setCellValue('B3', 'DATE');
    $sheet->setCellValue('C3', 'BRANCH');
    $sheet->setCellValue('D3', 'EMP ID');
    $sheet->setCellValue('E3', 'EMP NAME');
    $sheet->setCellValue('F3', 'DESIGNATION');
    $sheet->setCellValue('G3', 'PREVIOUS SALARY');
    $sheet->setCellValue('H3', 'NEW SALARY');
   
    while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
      $sheet->setCellValue('A'.$i, $a);
      $sheet->setCellValue('B'.$i, $row['updated_date']);
      
      $stmt2 = $conn->prepare("SELECT `name`  from branch WHERE `id`='".$row['branch']."' ");
      $stmt2->execute();
      while ($row3 = $stmt2->fetch(PDO::FETCH_NAMED)) {
        $sheet->setCellValue('C'.$i, $row3['name']);
      }
      
      $sheet->setCellValue('D'.$i, $row['emp_id']);
      $sheet->setCellValue('E'.$i, $row['emp_name']);
      $sheet->setCellValue('F'.$i, $row['designation']);
      $sheet->setCellValue('G'.$i, $row['prv_sal']);
      $sheet->setCellValue('H'.$i, $row['new_sal']);
      
      
      
      
    

  $i++;
  $a++;
}

$spreadsheet->getActiveSheet()->getStyle('A'.$i.':H'.$i)->applyFromArray($styleArray1);

// OUTPUT
$writer = new Xlsx($spreadsheet);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Salary-Increment(Admin).xlsx"');
header('Cache-Control: max-age=0');
header('Expires: Fri, 11 Nov 2011 11:11:11 GMT');
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
header('Cache-Control: cache, must-revalidate');
header('Pragma: public');
$writer->save('php://output');
?>