<?php
session_start();
$month=$_GET['m'];
$year=$_GET['y'];

  $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:L1');
  $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);
  $spreadsheet->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
  $spreadsheet->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
  $spreadsheet->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
  $spreadsheet->getActiveSheet()->getColumnDimension('L')->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:L3')->applyFromArray($styleArray1);
  $spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);

  $sheet->setTitle('Attendance sheet');
  $stmt = $conn->prepare("SELECT emp_details.e_name , emp_details.designation, prv_attendance.emp_id, prv_attendance.branch, prv_attendance.site, prv_attendance.attendance, prv_attendance.only_ot , prv_attendance.check, prv_attendance.block, prv_attendance.advance_deduction, prv_attendance.spc_allow FROM `prv_attendance` LEFT JOIN `emp_details` ON prv_attendance.emp_id=emp_details.e_id AND prv_attendance.branch=emp_details.branch where prv_attendance.month='$month' and prv_attendance.year='$year' ORDER BY emp_details.e_name ASC;");

  //print_r($stmt);exit;
  $stmt->execute();
  $a = 1;
  $i = 4;
  $sheet->setCellValue('A1', $br.' SITE EMPLOYEE ATTENDANCE SHEET '.strtoupper($monthName).' '. $year);
  $sheet->setCellValue('A3', 'SR NO');
  $sheet->setCellValue('B3', 'EMP ID');
  $sheet->setCellValue('C3', 'NAME');
  $sheet->setCellValue('D3', 'DESIGNATION');
  $sheet->setCellValue('E3', 'BRANCH');
  $sheet->setCellValue('F3', 'SITE');
  $sheet->setCellValue('G3', 'ATTENDANCE');
  $sheet->setCellValue('H3', 'ONLY OT');
  $sheet->setCellValue('I3', 'CHECK');
  $sheet->setCellValue('J3', 'HOLD');
  $sheet->setCellValue('K3', 'ADV DEDUCTION');
  $sheet->setCellValue('L3', 'SPECIAL ALLOWANCE');
 
  while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
    $sheet->setCellValue('A'.$i, $a);
    $sheet->setCellValue('B'.$i, $row['emp_id']);
    $sheet->setCellValue('C'.$i, $row['e_name']);
    $sheet->setCellValue('D'.$i, $row['designation']);
  
    $stmt1 = $conn->prepare("SELECT `name` FROM `branch` WHERE `id`='".$row['branch']."' ");
    $stmt1->execute();
    while ($row1 = $stmt1->fetch(PDO::FETCH_NAMED)) {
      $sheet->setCellValue('E'.$i, $row1['name']);
    }
  
    $stmt1 = $conn->prepare("SELECT `name` FROM `site` WHERE `id`='".$row['site']."' ");
    $stmt1->execute();
    while ($row1 = $stmt1->fetch(PDO::FETCH_NAMED)) {
      $sheet->setCellValue('F'.$i, $row1['name']);
    }
  
    $sheet->setCellValue('G'.$i, $row['attendance']);
    $sheet->setCellValue('H'.$i, $row['only_ot']);
    $sheet->setCellValue('I'.$i, $row['check']);
    $sheet->setCellValue('J'.$i, $row['block']);
    $sheet->setCellValue('K'.$i, $row['advance_deduction']);
    $sheet->setCellValue('L'.$i, $row['spc_allow']);
 
  $i++;
  $a++;
}

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

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