<?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
      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:Q1');
$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);
$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);


$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:Q3')->applyFromArray($styleArray1);
$spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);


$fromyear=$_GET['fy'];
$frommonth=$_GET['fm'];
$toyear=$_GET['ty'];
$tomonth=$_GET['tm'];
$ename=$_GET['enm'];
$sheet->setTitle('Employee wise report sheet');
$stmt = $conn->prepare(" SELECT * FROM `prv_emp_salary` WHERE `month` BETWEEN '".$frommonth."' and '".$tomonth."' AND `year` BETWEEN '".$fromyear."' and '".$toyear."' and `emp_id`='".$ename."'  and `block`='' and `cheque`='' and `ac_no`!='' ");

//$stmt = $conn->prepare("SELECT admin_emp_salary.emp_id, admin_emp_salary.name, admin_emp_salary.designation, admin_emp_salary.attendance, admin_emp_salary.basic_pay, admin_emp_salary.hra, admin_emp_salary.convyance, admin_emp_salary.medical, admin_emp_salary.gross_pay, admin_emp_salary.pf, admin_emp_salary.esic, admin_emp_salary.advance, admin_emp_salary.tax, admin_emp_salary.deduction, admin_emp_salary.net_pay, emp_details.uan_no  from admin_emp_salary  LEFT JOIN emp_details ON admin_emp_salary.emp_id=emp_details.e_id AND admin_emp_salary.branch=emp_details.branch WHERE admin_emp_salary.month='$month' AND admin_emp_salary.year='$year' UNION SELECT '', 'z', 'TOTAL', SUM(attendance), SUM(basic_pay), SUM(hra), SUM(convyance), SUM(medical), SUM(gross_pay), SUM(pf), SUM(esic), SUM(advance), SUM(tax), SUM(deduction), SUM(net_pay), '' from admin_emp_salary where `month`='$month' and `year`='$year' ORDER BY name ASC");
//print_r($stmt);exit;
    
$stmt->execute();
$a = 1;
$i = 4;
  $sheet->setCellValue('A1', 'Employee wise report ('.$fromyear."-".$frommonth." to ".$toyear."-".$tomonth.')');
  $sheet->setCellValue('A3', 'SR NO');
  $sheet->setCellValue('B3', 'NAME');
  $sheet->setCellValue('C3', 'DESIGNATION');
  $sheet->setCellValue('D3', 'EMP ID');
  $sheet->setCellValue('E3', 'UAN-NO');
  $sheet->setCellValue('F3', 'ATTENDENCE');
  $sheet->setCellValue('G3', 'BASIC PAY');
  $sheet->setCellValue('H3', 'HRA');
  $sheet->setCellValue('I3', 'CONVEYANCE');
  $sheet->setCellValue('J3', 'MEDICAL');
  $sheet->setCellValue('K3', 'GROSS');
  $sheet->setCellValue('L3', 'PF');
  $sheet->setCellValue('M3', 'ESIC');
  $sheet->setCellValue('N3', 'ADVANCE');
  $sheet->setCellValue('O3', 'TAX');
  $sheet->setCellValue('P3', 'DEDUCTION');
  $sheet->setCellValue('Q3', 'NET-PAY');
   
while ($row = $stmt->fetch(PDO::FETCH_NAMED)) {
  $sheet->setCellValue('A'.$i, $a);
  $sheet->setCellValue('B'.$i, $row['name']);
  $sheet->setCellValue('C'.$i, $row['designation']);
  $sheet->setCellValue('D'.$i, $row['emp_id']);
  $sheet->setCellValue('E'.$i, $row['uan_no']);
  $sheet->setCellValue('F'.$i, $row['attendance']);
  $sheet->setCellValue('G'.$i, $row['basic_pay']);
  $sheet->setCellValue('H'.$i, $row['hra']);
  $sheet->setCellValue('I'.$i, $row['convyance']);
  $sheet->setCellValue('J'.$i, $row['medical']);
  $sheet->setCellValue('K'.$i, $row['gross_pay']);
  $sheet->setCellValue('L'.$i, $row['pf']);
  $sheet->setCellValue('M'.$i, $row['esic']);
  $sheet->setCellValue('N'.$i, $row['advance']);
  $sheet->setCellValue('O'.$i, $row['tax']);
  $sheet->setCellValue('P'.$i, $row['deduction']);
  $sheet->setCellValue('Q'.$i, $row['net_pay']);
  $i++;
  $a++;
}

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


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