<?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';


$query=$conn->prepare("SELECT name FROM branch WHERE id=:param");
$query->bindParam(':param', $_SESSION['branch']);
$query->execute();
$result = $query -> fetch(PDO::FETCH_ASSOC);
$br=$result['name'];
// 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:Z1');
$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('I')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('Q')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('R')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('S')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('T')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('U')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('V')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('W')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('X')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('Y')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('Z')->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:Z3')->applyFromArray($styleArray1);
$spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);


$sheet->setTitle('Employee List');
$a = 1;
$i = 4;

  $sth = $conn->prepare("SELECT * from `emp_details` WHERE `status`='1' ");
  $sth->execute();
  while ($row = $sth->fetch(PDO::FETCH_NAMED)) {

//$stmt = $conn->prepare("SELECT *  from emp_details WHERE `e_id`='".$row2['e_id']."'  ORDER BY e_name ASC");
//$stmt->execute();

  $sheet->setCellValue('A1', $br.' : ACTIVE EMPLYOEES LIST FOR '.$monthName.' '. $year);
  $sheet->setCellValue('A3', 'SR NO');
  $sheet->setCellValue('B3', 'Employee Id');
  $sheet->setCellValue('C3', 'Employee Name');
  $sheet->setCellValue('D3', 'Father Name');
  $sheet->setCellValue('E3', 'Mother Name');
  $sheet->setCellValue('F3', 'Husband/ Wife Name');
  $sheet->setCellValue('G3', 'Designation');
  $sheet->setCellValue('H3', 'Dob');
  $sheet->setCellValue('I3', 'Doj');
  $sheet->setCellValue('J3', 'Mob No');
  $sheet->setCellValue('K3', 'Alt Mob No');
  $sheet->setCellValue('L3', 'Adhara');
  $sheet->setCellValue('M3', 'Address');
  $sheet->setCellValue('N3', 'Bank A.C No');
  $sheet->setCellValue('O3', 'IFSC');
  $sheet->setCellValue('P3', 'Bank Name');
  $sheet->setCellValue('Q3', 'PAN');
  $sheet->setCellValue('R3', 'UAN');
  $sheet->setCellValue('S3', 'Gross');
  $sheet->setCellValue('T3', 'OT');
  $sheet->setCellValue('U3', 'Conv');
  $sheet->setCellValue('V3', 'Med');
  $sheet->setCellValue('W3', 'PF');
  $sheet->setCellValue('X3', 'ESI');
  $sheet->setCellValue('Y3', 'ESIC No');
  $sheet->setCellValue('Z3', 'Branch');
  
   
//while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
  $stmt1 = $conn->prepare("SELECT `name`  from branch WHERE `id`='".$row['branch']."' ");
  $stmt1->execute();
  
  $sheet->setCellValue('A'.$i, $a);
  $sheet->setCellValue('B'.$i, $row['e_id']);
  $sheet->setCellValue('C'.$i, $row['e_name']);
  $sheet->setCellValue('D'.$i, $row['fname']);
  $sheet->setCellValue('E'.$i, $row['mname']);
  $sheet->setCellValue('F'.$i, $row['husband_wife']);
  $sheet->setCellValue('G'.$i, $row['designation']);
  $sheet->setCellValue('H'.$i, $row['dob']);
  $sheet->setCellValue('I'.$i, $row['e_doj']);
  $sheet->setCellValue('J'.$i, $row['mobile_num']);
  $sheet->setCellValue('K'.$i, ' '.$row['alt_mob_no']);
  $sheet->setCellValue('L'.$i, ' '.$row['aadhar_num']);
  $sheet->setCellValue('M'.$i, $row['addr']);
  $sheet->setCellValue('N'.$i, ' '.$row['bank_ac_no']);
  $sheet->setCellValue('O'.$i, $row['bank_ifsc']);
  $sheet->setCellValue('P'.$i, $row['bank_name']);
  $sheet->setCellValue('Q'.$i, $row['pan_card']);
  $sheet->setCellValue('R'.$i, ' '.$row['uan_no']);
  $sheet->setCellValue('S'.$i, $row['salary']);
  $sheet->setCellValue('T'.$i, $row['ot']);
  $sheet->setCellValue('U'.$i, $row['con']);
  $sheet->setCellValue('V'.$i, $row['med']);
  $sheet->setCellValue('W'.$i, $row['pf']);
  $sheet->setCellValue('X'.$i, $row['esi']);
  $sheet->setCellValue('Y'.$i, ' '.$row['esic_no']);
  
 
  while ($row1 = $stmt1->fetch(PDO::FETCH_NAMED)) {
  $sheet->setCellValue('Z'.$i, $row1['name']);
  }
  
//}
  $i++;
  $a++;
} 

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


// OUTPUT
$writer = new Xlsx($spreadsheet);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="All-Employee(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');
?>