<?php
session_start();
ob_start();
include('lib/conn.php');
include('functions.php');

require_once ('test_excel/PHPExcel.php');

function cellColor($cells,$color){
    global $objPHPExcel;

    $objPHPExcel->getActiveSheet()->getStyle($cells)->getFill()->applyFromArray(array(
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'startcolor' => array(
             'rgb' => $color
        )
    ));
}

$staff_id = $_REQUEST['staff_id'];

$hr_manager_id = $_SESSION['sat_login_id'];

$level = getUserlevel($hr_manager_id);

function change_dateformat_get_day($date_form)
{

	if($date_form!=''){

		$date1=explode("-",$date_form);

		$dateformat=$date1[2]."-".$date1[0]."-".$date1[1];

		return $dateformat;
	}
	else{
		$dateformat='';
		return $dateformat;
	}
}

function change_dateformat_get_day_rev($date_form)
{

	if($date_form!=''){

		$date1=explode("-",$date_form);

		$dateformat=$date1[1]."-".$date1[2]."-".$date1[0];

		return $dateformat;
	}
	else{
		$dateformat='';
		return $dateformat;
	}
}




// Some html code omitted
function cleanData(&$str) {
	// escape tab characters 
	$str = preg_replace("/\t/", "\\t", $str); 
	// escape new lines 
	$str = preg_replace("/\r?\n/", "\\n", $str); 
	// convert 't' and 'f' to boolean values 
	if($str == 't') $str = 'TRUE'; 
	if($str == 'f') $str = 'FALSE'; 
	// force certain number/date formats to be imported as strings 
	if(preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) 
		{ $str = "'$str"; } 
	// escape fields that include double quotes 
	if(strstr($str, '"')) 
		$str = '"' . str_replace('"', '""', $str) . '"'; 
} 
//function export() {
        // file name for download 
        /*$filename = "ABSI Timesheet Report for Period - " . $_REQUEST['start_date'] ." -  " . $_REQUEST['end_date'] .".xls"; 
        header("Content-Disposition: attachment; filename=\"$filename\""); 
        header("Content-Type: application/vnd.ms-excel");*/ 
        $flag = false;
		
		
				
		$sql = "SELECT * FROM vacation_track WHERE staff_id = '".$staff_id."' AND date BETWEEN '".change_dateformat_get_day($_REQUEST['start_date'])."' AND  '".change_dateformat_get_day($_REQUEST['end_date'])."' ORDER BY date ASC";
		
		
		$result = mysql_query($sql);
		
		// Instantiate a new PHPExcel object
		$objPHPExcel = new PHPExcel(); 
		$filename = "Vacation Report for Period - " . $_REQUEST['start_date'] ." -  " . $_REQUEST['end_date'] ."xls";
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="'.$filename.'"');
		header('Cache-Control: max-age=0');  
		
		// Set the active Excel worksheet to sheet 0
		$objPHPExcel->setActiveSheetIndex(0);
		
		$styleArray = array(
    	'font'  => array(
        'color' => array('rgb' => '000000'),
        'size'  => 10,
        'name'  => 'Arial',
					),
					'alignment' => array(
					  'wrap'       => true,
					  'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
					  'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
		));
	
	$styleArrayBold = array(
    	'font'  => array(
        'bold'  => true,
        'color' => array('rgb' => '000000'),
        'size'  => 10,
        'name'  => 'Arial',
				),
				'alignment' => array(
				  'wrap'       => true,
				  'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
				  'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
		));
	
	
		$staff_name =  getNameTable("sat_staff","firstname","staff_id",$staff_id)." ".getNameTable("sat_staff","lastname","staff_id",$staff_id);
		
		$begining_balance = getNameTable("sat_staff","begining_vacation_balance","staff_id",$staff_id);
		$reamaing_vacation = getNameTable("staff_personal","vacation","staff_id",$staff_id);
		 
		$inc = 4;
		$sl = 1;
		$objPHPExcel->getActiveSheet()->SetCellValue('A'.$inc, '1');
		$objPHPExcel->getActiveSheet()->SetCellValue('B'.$inc, $staff_name);
		$objPHPExcel->getActiveSheet()->SetCellValue('C'.$inc, $begining_balance);
		$objPHPExcel->getActiveSheet()->SetCellValue('D'.$inc, $reamaing_vacation);
		while(false !== ($row = mysql_fetch_assoc($result))) { 
			$objPHPExcel->getActiveSheet()->freezePane('A1');
			$objPHPExcel->getActiveSheet()->freezePane('B1');
			$objPHPExcel->getActiveSheet()->freezePane('C1');
			$objPHPExcel->getActiveSheet()->freezePane('D1');
			$objPHPExcel->getActiveSheet()->freezePane('E1');
			
			if($inc%2 == 0){
				cellColor('A'.$inc.':N'.$inc.'', 'D9D9D9');
				
			}else{
				cellColor('A'.$inc.':N'.$inc.'', 'FFFFFF');
			}
			
	        if(!$flag) { 
		        // display field/column names as first row 
		    /*echo  "Time Sheet Status Report"."\t"." "."\t"." "."\t"."Period -->"."\t".$_REQUEST['start_date']."\t".$_REQUEST['end_date']."\n";
			echo  " "."\t"." "."\t"." "."\t"." "."\t"."Approved"."\t"."Un-Approved"."\t"."Holiday"."\t"."Vacation"."\t"."Personal"."\t"."Sick"."\t"."Un-paid Time"."\t"."Total"."\t"."Submitted"."\t"."<40"."\n";
			echo  "Serial No."."\t"."Staff Code"."\t"."Name"."\t"."Type"."\t"."Hours"."\t"."Hours"."\t"."Hours"."\t"."Hours"."\t"."Hours"."\t"."Hours"."\t"."Hours"."\t"."Hours"."\t"."Saved"."\t"."Explanation"."\n";*/
			
			 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(3);
			 $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(16);
			 $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(23);
			 $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(23);
			 $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
			 $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
			 $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
			 $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(8);
			 $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(8);
			 $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(6);
			 $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(13);
			 $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(6);
			 $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(10);
			 $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(30);
			 
			 $objPHPExcel->getActiveSheet()->getStyle('A')->applyFromArray($styleArray);
			 $objPHPExcel->getActiveSheet()->getStyle('B')->applyFromArray($styleArray);
			 $objPHPExcel->getActiveSheet()->getStyle('C')->applyFromArray($styleArray);
			 $objPHPExcel->getActiveSheet()->getStyle('D')->applyFromArray($styleArray);
			 $objPHPExcel->getActiveSheet()->getStyle('E')->applyFromArray($styleArray);
			 $objPHPExcel->getActiveSheet()->getStyle('F')->applyFromArray($styleArray);
			 $objPHPExcel->getActiveSheet()->getStyle('G')->applyFromArray($styleArray);
			 $objPHPExcel->getActiveSheet()->getStyle('H')->applyFromArray($styleArray);
			 $objPHPExcel->getActiveSheet()->getStyle('I')->applyFromArray($styleArray);
			 $objPHPExcel->getActiveSheet()->getStyle('J')->applyFromArray($styleArray);
			 $objPHPExcel->getActiveSheet()->getStyle('K')->applyFromArray($styleArray);
			 $objPHPExcel->getActiveSheet()->getStyle('L')->applyFromArray($styleArray);
			 $objPHPExcel->getActiveSheet()->getStyle('M')->applyFromArray($styleArray);
			 $objPHPExcel->getActiveSheet()->getStyle('N')->applyFromArray($styleArray);
			 
			 $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArrayBold);
			 $objPHPExcel->getActiveSheet()->getStyle('B1')->applyFromArray($styleArrayBold);
			 $objPHPExcel->getActiveSheet()->getStyle('E1')->applyFromArray($styleArrayBold);
			 $objPHPExcel->getActiveSheet()->getStyle('F1')->applyFromArray($styleArrayBold);
			 $objPHPExcel->getActiveSheet()->getStyle('G1')->applyFromArray($styleArrayBold);
			 $objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray($styleArrayBold);
			 $objPHPExcel->getActiveSheet()->getStyle('B3')->applyFromArray($styleArrayBold);
			 $objPHPExcel->getActiveSheet()->getStyle('C3')->applyFromArray($styleArrayBold);
			 $objPHPExcel->getActiveSheet()->getStyle('d3')->applyFromArray($styleArrayBold);
			
			 $objPHPExcel->getActiveSheet()->mergeCells('A1:C1');
			 $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Staff Vacation Report');
			 $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Period -->'); 
			 $objPHPExcel->getActiveSheet()->SetCellValue('F1', $_REQUEST['start_date']);
			 $objPHPExcel->getActiveSheet()->SetCellValue('G1', $_REQUEST['end_date']);
			 
			 
			 $objPHPExcel->getActiveSheet()->SetCellValue('A3', '#');
			 $objPHPExcel->getActiveSheet()->SetCellValue('B3', 'Name');
			 $objPHPExcel->getActiveSheet()->SetCellValue('C3', 'Begining Balance');
			 $objPHPExcel->getActiveSheet()->SetCellValue('D3', 'Ending Vacation');
			 
			 $objPHPExcel->getActiveSheet()->SetCellValue('E3', 'Date');
			 $objPHPExcel->getActiveSheet()->SetCellValue('F3', 'Hours');
			
			
			 $flag = true; 
	        }         
	       // array_walk($row, 'cleanData');
			
			
			
			
			
			if($row['submit_flag'] == 1){
				$submit_status = 'Submitted';
			}else{
				$submit_status = 'Saved';
			}
			
			
			$objPHPExcel->getActiveSheet()->SetCellValue('E'.$inc, change_dateformat_get_day_rev($row['date']));
			$objPHPExcel->getActiveSheet()->SetCellValue('F'.$inc, $row['vacation']);
			
			
			
			/*$str = $inc."\t".$row['staff_code']."\t".$staff_name."\t".$row['associate_type']."\t".$approved_hours."\t".$unapproved_hours."\t".$holiday_hours."\t".$vacation_hours."\t".$personal_hours."\t".$sick_hours."\t".$unpaid_hours."\t".$total_hours."\t".$submit_status."\t".$row['explanation']."\t"."\n";
			echo $str;*/
			
			$inc++;
			$sl++;
        }
//}

$writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  

// This line will force the file to download    
$writer->save('php://output');

?>