<?php
session_start();
//php_spreadsheet_export.php

 
include 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;


//$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");



$servername = "localhost";
$servername = "3.110.17.158";
$username = "Tarun";
$password = "Tarun@1234";
$dbname = "vowsls";
$connect = new mysqli($servername, $username, $password, $dbname);


$date1=$_GET["date1"];
$date2=($_GET['date2']);
//$ebno=($_GET['education']);
$cmp=($_GET['dept']);

$dat1=$date1;
$dat2=$date2;

 
$date1=date("Y-m-d", strtotime($date1) );
 
$date2=date("Y-m-d", strtotime($date2) );

//echo $date1;
//echo $date2;





 
  $sql="select * from company_master where comp_id=$cmp";
  $resulta = $connect->query($sql);
 while($rowa = $resulta->fetch_assoc()) 
  {
	$cmpn=$rowa["company_name"];
  }

//echo $date1;
//echo $date2;
//echo $ebno;
//echo $cmp;
//echo $cmpn;













$file = new Spreadsheet();

$file_type="Xlsx"; 

$query = "
select da.attendance_date,da.eb_no,concat(wm.worker_name ,' ',wm.last_name) wname, spell,da.working_hours,da.entry_time ,da.exit_time , spell_name,
starting_time,end_time,concat(da.attendance_date,' ',sm.starting_time) sttime,
case when  sm.end_time< sm.starting_time then CONCAT(date_add(da.attendance_date,INTERVAL 1 DAY),' ',sm.end_time) else  concat(da.attendance_date,' ',sm.end_time) 
end as endtime, 'gg' nn,DATEDIFF(da.attendance_date,'".$date1."')+2 nofda1,
date_add(concat(da.attendance_date,' ',sm.starting_time),interval 15 minute) startgracetime,
IFNULL( FLOOR(round(TIMESTAMPDIFF(SECOND,entry_time,da.exit_time)/3600,2)),0)+(IFNULL(ROUND(MOD(round(TIMESTAMPDIFF(SECOND,entry_time,da.exit_time)/3600,2),1)*60,0),0) )/100
whrs,IFNULL(ROUND(MOD(round(TIMESTAMPDIFF(SECOND,entry_time,da.exit_time)/3600,2),1)*60,0),0) WMIN
from daily_attendance da,spell_master sm ,worker_master wm  
where attendance_date >='".$date1."' and attendance_date <='".$date2."'  and da.company_id =".$cmp." and da.spell =sm.spell_name and da.company_id =sm.company_id AND attendance_source ='B' and
entry_time IS NOT null  and is_active =1 and da.eb_no =wm.eb_no 
ORDEr by da.eb_no,attendance_date ";

//echo $query;

$result = $connect->query($query);


 
		


  $active_sheet = $file->getActiveSheet();

   $rep="Attendance Sheet for ".$dat1." To ".$dat2;
   
  
  $active_sheet->setCellValue('A1', $cmpn);
  $active_sheet->setCellValue('A2', $rep);

  $file->getActiveSheet()->getColumnDimension('A')->setWidth(10); 
  $file->getActiveSheet()->getColumnDimension('B')->setWidth(40); 

//$file->->getStyle()->getFont()->setBold(true);

//$file->getActiveSheet()->getStyle("A1:B1")->getFont()->setBold( true );


//$file->getActiveSheet()->getCell('A')->getStyle()->getFont()->setBOLD(true);




  $active_sheet->setCellValue('A4', 'EB No');
  $active_sheet->setCellValue('B4', 'Name ');
//  $active_sheet->setCellValue('E1', $query);




  //$diff=date_diff($date1,$date2);





$dt1=date_create($date1);
$dt2=date_create($date2);
$diff=date_diff($dt1,$dt2);

$date3=$dt1;
$date4=$dt2;
$date5=$dt1;


//echo $diff;
//echo $diff->format("%R%a days");
$df1=$diff->format("%a");
 // $active_sheet->setCellValue('B2', $df1);

$x=1;

while($date3 <= $date4) {
	
$diff=date_diff($date3,$date4);
$df=$diff->format("%a");

$df2=$df1-$df+2;

	$noc1=0;
	$noc2=0;
	if ($df2<=25) {
		$noc1=$df2;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df2-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	
	$string = $date3->format('Y-m-d');
	$day=substr($string,8,2);
	$month=substr($string,5,2);
	$dm="'".$day.'/'.$month;

	
$cn=4; 
	$active_sheet->setCellValue($chf . $cn,$dm );
	
//	$active_sheet->setCellValue($chf . $cn-1,$df );

//	$active_sheet->setCellValue($chf . $cn+1,$x );
//	$active_sheet->setCellValue($chf . $cn+1,$df2 );
	
	
	$x++;
//  echo "The number is: ".$date3 ;

//$date=date_create("2013-03-15");
//echo date_format($date3,"Y-m-d");


$date3=date_add($date3,date_interval_create_from_date_string("1 days"));



}


	$df3=$df1+1+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,"Late" );
	
	$df3=$df1+2+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,"Early" );

	$df3=$df1+3+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,"Late & Early" );
	
	$df3=$df1+4+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,"Present" );

	$df3=$df1+5+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,"Holiday" );


	$df3=$df1+6+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,"Absent" );

	$df3=$df1+7+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,"Total" );



 // $active_sheet->setCellValue('c4', $df);
  
   

$file->getActiveSheet()->getStyle('A1:'.$chf.$cn)->getFont()->setBold(true);

$file->getActiveSheet()->getStyle('A4:'.$chf.$cn)->getBorders()->getTop()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);
$file->getActiveSheet()->getStyle('A4:'.$chf.$cn)->getBorders()->getBottom()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);
$file->getActiveSheet()->getStyle('A4:'.$chf.$cn)->getBorders()->getLeft()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);
$file->getActiveSheet()->getStyle('A4:'.$chf.$cn)->getBorders()->getRight()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK);




  $eb='';
  $count = 6;

  $totle=$totl=$tote=$totp=$toth=$tota=0;
  


//  foreach($result as $row)
 while($row = $result->fetch_assoc()) 


  {
	
	$noc1=0;
	$noc2=0;
	if ($row["nofda1"]<=25) {
		$noc1=$row["nofda1"];
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$row["nofda1"]-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}

//	$chf=chr($row["nofda1"]+65);
	
	
	if ($eb<>$row["eb_no"]) {

	$ln=strlen($eb);


	if ($ln>0) {
	$active_sheet->setCellValue('Al' . $count+3,$ln );

    $sql="select holiday_date ,DATEDIFF(holiday_date,'".$date1."')+2 hofda1 from holiday_master hm where company_id =".$cmp." and holiday_date BETWEEN '".$date1."' and '".$date2."'";
//	$active_sheet->setCellValue('AG' . $count+3,$sql );

/*
 SELECT holiday_date,COUNT(*) AS cnt FROM (
 select eb_no,da.attendance_date ,hm.holiday_date,hm.holiday , (working_hours)  from daily_attendance da ,holiday_master hm 
 where ((da.attendance_date =hm.period_start_date) or (da.attendance_date  =hm.period_end_date  ))
 and hm.holiday_date>='2022-10-01' and hm.holiday_date<='2022-10-31' AND da.company_id=hm.company_id and hm.company_id =3 and is_active =1
 and attendance_source ='B' AND eb_no='STPL061'
 ) a GROUP BY holiday_date
 
*/
	
	$resulta = $connect->query($sql);
	while($rowa = $resulta->fetch_assoc()) 
  {

	$noc1=0;
	$noc2=0;
	if ($rowa["hofda1"]<=25) {
		$noc1=$rowa["hofda1"];
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$rowa["hofda1"]-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
    $rem="H";
	$active_sheet->setCellValue($chf . $count+3,$rem );
	
	
  }

  
 $x=2;
  while($x <= $df1+2) {

	$noc1=0;
	$noc2=0;
	if ($x<=25) {
		$noc1=$x;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$x-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}

$cellValue="TEST";
$cellValue = $file->getActiveSheet()->getCell($chf.$count+3)->getValue();

//   $active_sheet->setCellValue('AH' . $count, $cellValue);

if ($cellValue=='L') {
	$totl=$totl+1;
}
	
if ($cellValue=='LE') {
	$totle=$totle+1;
}
	
if ($cellValue=='E') {
	$tote=$tote+1;
}

if ($cellValue=='P') {
	$totp=$totp+1;
}

if ($cellValue=='H') {
	$toth=$toth+1;
}

if (strlen($cellValue)==0) {

   $active_sheet->setCellValue($chf.$count+3, "A");

	$tota=$tota+1;
}



$x++;

  
  }	
 
$cn=$count+3; 
 
 	$df3=$df1+1+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,$totl );
	
	$df3=$df1+2+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,$tote );

	$df3=$df1+3+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,$totle );
	
	$df3=$df1+4+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,$totp );

	$df3=$df1+5+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,$toth );

	$df3=$df1+6+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,$tota );

	$df3=$df1+7+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,($totl+$totle+$tote+$totp+$toth) );
 
	
      	
		
	  $count = $count + 4;
	}	

  $totle=$totl=$tote=$totp=$toth=$tota=0;
  
	$noc1=0;
	$noc2=0;
	if ($row["nofda1"]<=25) {
		$noc1=$row["nofda1"];
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$row["nofda1"]-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}


   $active_sheet->setCellValue('A' . $count, $row["eb_no"]);
    $active_sheet->setCellValue('B' . $count, $row["wname"]);
 	$eb=$row["eb_no"];		
	
	}	
	
	$entm=substr($row["entry_time"],10,6);
	$extm=substr($row["exit_time"],10,6);
	
    $active_sheet->setCellValue($chf . $count,$entm );
    $active_sheet->setCellValue($chf . $count+1,$extm );
	$active_sheet->setCellValue($chf . $count+2,$row["whrs"] );
	
	$rem="";
    $le=$row["exit_time"];
	
	
	if ($row["entry_time"]>$row["startgracetime"] and $row["exit_time"]>=$row["endtime"] ) {
		$rem="L";
	}
	
    if ($row["entry_time"]>$row["startgracetime"] and $row["exit_time"]<$row["endtime"] ) {
		$rem="LE";
	}
		
    if ($row["entry_time"]<=$row["startgracetime"] and $row["exit_time"]<$row["endtime"] ) {
		$rem="E";

	}
	
    if ($row["entry_time"]<=$row["startgracetime"] and $row["exit_time"]>=$row["endtime"] ) {
		$rem="P";
	
	}
	
	
	$active_sheet->setCellValue($chf . $count+3,$rem );
	
	
  //  $active_sheet->setCellValue('D' . $count, $row["entry_time"]);
	
//	$active_sheet->setCellValue('E' . $count, $chf);
//	$active_sheet->setCellValue('f' . $count,$row["nofda1"] );
	
//	$active_sheet->setCellValue('G' . $count,"TEST" );
	
//	$active_sheet->setCellValue($chf . $count,$chf );


   }



//last record calculation

	$ln=strlen($eb);


	if ($ln>0) {
	$active_sheet->setCellValue('Al' . $count+3,$ln );

    $sql="select holiday_date ,DATEDIFF(holiday_date,'".$date1."')+2 hofda1 from holiday_master hm where company_id =".$cmp." and holiday_date BETWEEN '".$date1."' and '".$date2."'";
//	$active_sheet->setCellValue('AG' . $count+3,$sql );
	
	$resulta = $connect->query($sql);
	while($rowa = $resulta->fetch_assoc()) 
  {

	$noc1=0;
	$noc2=0;
	if ($rowa["hofda1"]<=25) {
		$noc1=$rowa["hofda1"];
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$rowa["hofda1"]-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
    $rem="H";
	$active_sheet->setCellValue($chf . $count+3,$rem );
	
	
  }

  
 $x=2;
  while($x <= $df1+2) {

	$noc1=0;
	$noc2=0;
	if ($x<=25) {
		$noc1=$x;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$x-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}

$cellValue="TEST";
$cellValue = $file->getActiveSheet()->getCell($chf.$count+3)->getValue();

//   $active_sheet->setCellValue('AH' . $count, $cellValue);

if ($cellValue=='L') {
	$totl=$totl+1;
}
	
if ($cellValue=='LE') {
	$totle=$totle+1;
}
	
if ($cellValue=='E') {
	$tote=$tote+1;
}

if ($cellValue=='P') {
	$totp=$totp+1;
}

if ($cellValue=='H') {
	$toth=$toth+1;
}

if (strlen($cellValue)==0) {

   $active_sheet->setCellValue($chf.$count+3, "A");

	$tota=$tota+1;
}



$x++;

  
  }	
 
$cn=$count+3; 
 
 	$df3=$df1+1+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,$totl );
	
	$df3=$df1+2+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,$tote );

	$df3=$df1+3+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,$totle );
	
	$df3=$df1+4+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,$totp );

	$df3=$df1+5+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,$toth );

	$df3=$df1+6+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,$tota );

 
	$df3=$df1+7+2;
	$noc1=0;
	$noc2=0;
	if ($df3<=25) {
		$noc1=$df3;
		$noc2=0;
		$chf=chr($noc1+65);	
	} else
	{	
		$noc1=0;
		$noc2=$df3-25;
		$chf=chr($noc1+65).chr($noc2+64);
		
	}
	$active_sheet->setCellValue($chf . $cn,($totl+$totle+$tote+$totp+$toth) );
	
      	
		
	  $count = $count + 4;
	}	


$file->getActiveSheet()->getStyle('c4:AB20')
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);


$file->getActiveSheet()->getStyle('C:AB')->getAlignment()->setHorizontal('center');


$x=4;

$lastColumn = $file->getActiveSheet()->getHighestColumn();
 $lastRow = $file->getActiveSheet()->getHighestRow();


while($x <= $cn) {



$file->getActiveSheet()->getStyle('A'.$x.':'.$chf.$cn)->getBorders()->getTop()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
$file->getActiveSheet()->getStyle('A4:'.$chf.$cn)->getBorders()->getBottom()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
$file->getActiveSheet()->getStyle('A4:'.$chf.$cn)->getBorders()->getLeft()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
$file->getActiveSheet()->getStyle('A4:'.$chf.$cn)->getBorders()->getRight()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
 $x++;

	}


//$file->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);

//$file->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);

foreach(range('B','AZ') as $columnID) {
//    $file->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}

 
 
//	$active_sheet->setCellValue('A' . $cn+2, $lastColumn );

//	$active_sheet->setCellValue('B' . $cn+2, $lastRow );


  $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($file, $_GET["file_type"]);
 // $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($file, $file_type);
  

  $fl=$cmpn.'-'.$date1.' To '.$date2;

  $file_name = time() . '.' . strtolower($_GET["file_type"]);

  $file_name = $fl . '.' . strtolower($_GET["file_type"]);


  $writer->save($file_name);

  header('Content-Type: application/x-www-form-urlencoded');

  header('Content-Transfer-Encoding: Binary');

  header("Content-disposition: attachment; filename=\"".$file_name."\"");

  readfile($file_name);

  unlink($file_name);

  exit;

 
	
 

?>