<?php
session_start();

include_once("../class/class.MySQL.php");
include_once("classes/userdefinedfunctions.php");
require_once ('Classes/PHPExcel/IOFactory.php');
require_once('Classes/PHPExcel.php');
  
$db	=new MySQL();
$userfunction	=new userdefinefunctions();  

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

//error_reporting(-1);

$limit='50';
$page=1; $employees='';
$month=date("m");
$year=date("Y");
if(isset($_GET['page']))
$page=$_GET['page'];
else
$page=1;
if($page) 
		$start = ($page - 1) * $limit;  //first item to display on this page
	else
		$start = 0;			
if(isset($_GET['month']) && ($_GET['month']!='undefined' || $_GET['month']!=''))
{
$month=$_GET['month'];
$mthdisplay= date('F',strtotime('1-'.$month.'-2000'));
}
else
{
$month=date("m");
$mthdisplay = date('F');
}
if(isset($_GET['year'])  && ($_GET['year']!='undefined'  || $_GET['year']!='' ))
{
$year=$_GET['year'];
$yeardisplay= date('Y',strtotime('1-2-'.$year));
}
else
{
$year=date("Y");
$yeardisplay=$year;
}
if(isset($_GET['employees']))
$employees=$_GET['employees'];
//---------------------------------------------------------------------------------------------//
$start=0;
$view="";

if($month=='null')$month=date("m");if($year=='null')$year=date("Y");
if($employees=='null'){
$qqry="select  e_id,e_fname from employee limit 0,1";
$ssql=mysql_query($qqry);
$oobj=mysql_fetch_object($ssql);
 $employees=$oobj->e_id;
}

$check=$year.'-'.$month;


///get employee Name
 $empnameQuery="select  e_id,e_fname from employee WHERE e_id='$employees'";
 
  $workingDays=  $db->getWorkingDays($firstOfLastmth,$endOfLastmth,$holidays);

	 $lastyrattendance = $db->fetchQuery("SELECT count(distinct(e_date)) FROM time WHERE e_date>='$firstOfLastmth' and e_date<='$endOfLastmth' and e_id='$employees'");
	 $yrattendance=$lastyrattendance[0]['count(distinct(e_date))'];
	
	$availedleaves=$workingDays-$yrattendance;
	 
	
	 	$nolossleaveapplied = $db->fetchQuery("Select count(*) as nolossleave from payroll_user_leave_requests where employee_id=$employees and is_cancelled=0 and imd_status=1 and day_from>='$firstOfLastmth' and day_from<='$endOfLastmth' and (leave_id='3' or leave_id='1') ");
	 
	   $nolossleaves=$nolossleaveapplied[0]['nolossleave'];
	
	$leavesentitledqry = $db->fetchQuery("SELECT sum(`number_of_days`) as entitled FROM `payroll_leave` WHERE `lid` IN  (select `leave_id` from `payroll_user_leave` where `employee_id`='$employees')");
	$leavesentitled=$leavesentitledqry[0]['entitled'];
	
	
	$balanceleaves=$leavesentitled-$nolossleaves;
	
	if($balanceleaves<0)
	{
	$balanceleaves=0;
	}

//$empnameobj=$db->fetchQuery($empnameQuery);

$empnamesql=mysql_query($empnameQuery);
$empnameobj=mysql_fetch_object($empnamesql);
$employeeName=$empnameobj->e_fname;

// Create new PHPExcel object

$objPHPExcel = new PHPExcel();


// Set document properties
$objPHPExcel->getProperties()->setCreator("Aadhocc Technologies")
							 ->setLastModifiedBy("Aadhocc Technologies")
							 ->setTitle("Employee Timesheet")
							 ->setSubject("Employee Timesheet")
							 ->setDescription("Employees attendance and time management report")
							 ->setKeywords("Employee time management")
							 ->setCategory("Employee Timesheet");


///// Create 
$objPHPExcel->setActiveSheetIndex(0);

$nummonth=cal_days_in_month(CAL_GREGORIAN,$month,$year);

$empid=$employees;
if($month=='null')$month=date("m");if($year=='null')$year=date("Y");
//---------------------------------------for date 1 of the selected month------------//

for($k=0;$k < $nummonth; $k++ )	
{
		$date =	$k + 1;
		$cellRow = $k+6; /// first four rows for set header; 
		
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$cellRow, $date);
		if($date < 10){
		$datewise=$check.'-0'.$date;
		}else{
		$datewise=$check.'-'.$date;
		}
		$qry=$userfunction->individual_qry($empid,$datewise,$db);
		$cnt= count($qry);
		$diffcnt=4-$cnt;
		$count = 'B';
		//print_r($qry);
		
		$rtqry="select * from report_time ";
		$rtqry1=mysql_query($rtqry);
		$rtrobj1=mysql_fetch_object($rtqry1);
		$reportime=$rtrobj1->report_time;
		
		if($cnt>0)
		{
		$col1 = 'B';
		
		
		
		foreach($qry as $qry)
		{
			$e_in= $qry['e_in'];
			if($e_in=="")
			$e_in="00:00:00";
			
			if($cnt<=1)
			{
			$e_out= $qry['e_out'];
			}
			else
			{
			$e_out= $qry[$cnt-1]['e_out'];
			}
			
			if($e_out=="")
			$e_out="00:00:00";
			
			//$view.=' <td align="center" valign="middle"> '.$e_in.'</td><td align="center" valign="middle"> '.$e_out.'</td> ';
			
			if($e_in <= $reportime)
			{
				$objPHPExcel->getActiveSheet()->setCellValue($col1.$cellRow,'R');
			}
			else
			{
				$objPHPExcel->getActiveSheet()->setCellValue($col1.$cellRow, 'L');
			}
			$col1++;
			
			$objPHPExcel->getActiveSheet()->setCellValue($col1.$cellRow, $e_in);

			$col1++;
			
			$objPHPExcel->getActiveSheet()->setCellValue($col1.$cellRow, $e_out);

		}
		if($diffcnt>0)
			{	
				$col2='B';
				for($c=1;$c<=$diffcnt;$c++)
				{
				//	$objPHPExcel->getActiveSheet()->setCellValue($col2.$cellRow, '');
					$col2++;
				//	$objPHPExcel->getActiveSheet()->setCellValue($col2.$cellRow, '');
					
				}
			}
			if($e_out!="00:00:00")
			$tot_time=$userfunction->ind_calldisplay($empid,$datewise,$db);
			else
			$tot_time="00:00:00";
			$objPHPExcel->getActiveSheet()->setCellValue('E'.$cellRow, $tot_time);
			$count++;
			//$view.=' <td align="center" valign="middle">'.$tot_time.'</td>  ';
		}else
		{
		$col3='B';
		for($c=1;$c<=4;$c++)
				{
					//$view.=' <td align="center" valign="middle">00:00:00</td>  ';
					$objPHPExcel->getActiveSheet()->setCellValue($col3.$cellRow, '00:00:00');
					
					$col3++;
				}
				
		}
		//	$view.='</tr>';
			
	
	}
	
$objPHPExcel->getActiveSheet()->getStyle('A1:E60')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('A1:E60')->getFont()->setSize(10);



$objPHPExcel->getActiveSheet()->setCellValue('A5', 'No');
$objPHPExcel->getActiveSheet()->setCellValue('B5', 'Late');
$objPHPExcel->getActiveSheet()->setCellValue('C5', 'Time In');
$objPHPExcel->getActiveSheet()->setCellValue('D5', 'Time Out');
$objPHPExcel->getActiveSheet()->setCellValue('E5', 'Total');



$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
$objPHPExcel->getActiveSheet()->freezePane('A6') ;

$objPHPExcel->getActiveSheet()->getStyle('A1:E4')->applyFromArray(
		array(
			'font'    => array(
				'bold'      => true
			),
			'borders' => array(
				'top'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				)
			),
			'fill' => array(
	 			'type'       => PHPExcel_Style_Fill::FILL_SOLID,
	 			'color' => array(
	 				'argb' => 'FFFFFFFF'
	 			)
	 		)
		)
);
$objPHPExcel->getActiveSheet()->getStyle('A5:E5')->applyFromArray(
		array(
			'font'    => array(
				'bold'      => true,
				'color' => array(
	 				'argb' => 'FFFFFFFF'
	 			)
			),
			'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
			),
			'borders' => array(
				'top'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				),
				'bottom'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				)
			),
			'fill' => array(
	 			'type'       => PHPExcel_Style_Fill::FILL_SOLID,
	  			'rotation'   => 90,
	 			'color' => array(
	 				'argb' => '4e4d4d4d'
	 			)
	 		)
		)
);


$objPHPExcel->getActiveSheet()->getStyle('A1:E'.$cellRow)->applyFromArray(
		array(
			'borders' => array(
				'top'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				),
				'bottom'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				),
				'left'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				),
				'right'     => array(
 					'style' => PHPExcel_Style_Border::BORDER_THIN
 				)
			),'alignment' => array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
			),
		)
);







$objPHPExcel->getActiveSheet()->mergeCells('A1:E1');	
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Time Sheet '.$mthdisplay.' '.$yeardisplay);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);



$objPHPExcel->getActiveSheet()->setCellValue('B2', 'Emp No');
$objPHPExcel->getActiveSheet()->setCellValue('B3', 'Emp Name');

$objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray(
		array(
			'alignment'    => array(
				'horizontal'      => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
			),
			'font'    => array(
				'bold'      => false
			),
		)
		);
$objPHPExcel->getActiveSheet()->getStyle('C3')->applyFromArray(
		array(
			'alignment'    => array(
				'horizontal'      => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
			),
			'font'    => array(
				'bold'      => false
			),
		)
		);
			
$objPHPExcel->getActiveSheet()->getStyle('A6:A'.$cellRow)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A6:A'.$cellRow)->getFill()->getStartColor()->setARGB('ff0ec5bf');

$objPHPExcel->getActiveSheet()->getStyle('A6:A'.$cellRow)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);


$objPHPExcel->getActiveSheet()->setCellValue('C2', $employees);
$objPHPExcel->getActiveSheet()->getStyle('C2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->setCellValue('C3', $employeeName);
$objPHPExcel->getActiveSheet()->getStyle('C3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);


header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$employeeName.'_'.$month.$year.'.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
//header('Location:test.xls');
//unlink('test.xls');
/*
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save(str_replace('.php', '.xls', __FILE__));

*/

/*
// We'll be outputting an excel file
header('Content-type: application/vnd.ms-excel');

// It will be called file.xls
header('Content-Disposition: attachment; filename="file.xls"');

// Write file to the browser
$objWriter->save('php://output');*/