<?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
        )
    ));
}



$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;
	}
}




// 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;
		
		$associate_type = '';
		$sep = '';
		foreach($_REQUEST['associate_type'] as $assoc_type)
		{
			$associate_type = $associate_type.$sep."'".$assoc_type."'";
			$sep = ',';
		}
		
		$visa_status = '';
		$sep1 = '';
		foreach($_REQUEST['visa_status'] as $visa_sts)
		{
			$visa_status = $visa_status.$sep1."'".$visa_sts."'";
			$sep1 = ',';
		}
				
		$sql = "SELECT * FROM sat_staff as sat_sf INNER JOIN timesheet tm ON sat_sf.staff_id = tm.staff_id INNER JOIN timesheet_weekly_hours as twh ON tm.id = twh.timesheet_id  WHERE tm.date_from = '".change_dateformat_get_day($_REQUEST['start_date'])."' AND tm.date_to = '".change_dateformat_get_day($_REQUEST['end_date'])."'";
		
		if($associate_type != '')
		{
			$sql.=" AND sat_sf.associate_type in (".$associate_type.")";
		}
		
		if($visa_status != '')
		{
			$sql.=" AND sat_sf.visa_status in (".$visa_status.")";
		}
		
		if($level == '2')
		{
			$sql.=" AND sat_sf.hr_manager = '".$hr_manager_id."'";
		}

		$result = mysql_query($sql);
		
		// Instantiate a new PHPExcel object
		$objPHPExcel = new PHPExcel(); 
		$filename = "ABSI Timesheet 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
		));
	
	

		 
		$inc = 4;
		$sl = 1;
		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(12);
			 $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(23);
			 $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(13);
			 $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()->mergeCells('A1:C1');
			 $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Time Sheet Status Report');
			 $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Period -->'); 
			 $objPHPExcel->getActiveSheet()->SetCellValue('F1', $_REQUEST['start_date']);
			 $objPHPExcel->getActiveSheet()->SetCellValue('G1', $_REQUEST['end_date']);
			 $objPHPExcel->getActiveSheet()->SetCellValue('E2', 'Approved');
			 $objPHPExcel->getActiveSheet()->SetCellValue('F2', 'Un-Approved');
			 $objPHPExcel->getActiveSheet()->SetCellValue('G2', 'Holiday');
			 $objPHPExcel->getActiveSheet()->SetCellValue('H2', 'Vacation');
			 $objPHPExcel->getActiveSheet()->SetCellValue('I2', 'Personal');
			 $objPHPExcel->getActiveSheet()->SetCellValue('J2', 'Sick');
			 $objPHPExcel->getActiveSheet()->SetCellValue('K2', 'Un-paid Time');
			 $objPHPExcel->getActiveSheet()->SetCellValue('L2', 'Total');
			 $objPHPExcel->getActiveSheet()->SetCellValue('M2', 'Submitted');
			 $objPHPExcel->getActiveSheet()->SetCellValue('N2', '<40');
			 
			 $objPHPExcel->getActiveSheet()->SetCellValue('A3', '#');
			 $objPHPExcel->getActiveSheet()->SetCellValue('B3', 'Staff Code');
			 $objPHPExcel->getActiveSheet()->SetCellValue('C3', 'Name');
			 $objPHPExcel->getActiveSheet()->SetCellValue('D3', 'Type');
			 
			 $objPHPExcel->getActiveSheet()->SetCellValue('E3', 'Hours');
			 $objPHPExcel->getActiveSheet()->SetCellValue('F3', 'Hours');
			 $objPHPExcel->getActiveSheet()->SetCellValue('G3', 'Hours');
			 $objPHPExcel->getActiveSheet()->SetCellValue('H3', 'Hours');
			 $objPHPExcel->getActiveSheet()->SetCellValue('I3', 'Hours');
			 $objPHPExcel->getActiveSheet()->SetCellValue('J3', 'Hours');
			 $objPHPExcel->getActiveSheet()->SetCellValue('K3', 'Hours');
			 $objPHPExcel->getActiveSheet()->SetCellValue('L3', 'Hours');
			 $objPHPExcel->getActiveSheet()->SetCellValue('M3', 'Saved');
			 $objPHPExcel->getActiveSheet()->SetCellValue('N3', 'Explanation');
			
			 $flag = true; 
	        }         
	       // array_walk($row, 'cleanData');
			
			$staff_name =  $row['lastname'].", ".$row['firstname'];
			
			$approved_hours = ($row['sun_approved'] + $row['mon_approved'] + $row['tue_approved'] + $row['wed_approved'] + $row['thu_approved'] + $row['fri_approved'] + $row['sat_approved']);
			$unapproved_hours = ($row['sun_unapproved'] + $row['mon_unapproved'] + $row['tue_unapproved'] + $row['wed_unapproved'] + $row['thu_unapproved'] + $row['fri_unapproved'] + $row['sat_unapproved']);
			$holiday_hours = ($row['sun_holiday'] + $row['mon_holiday'] + $row['tue_holiday'] + $row['wed_holiday'] + $row['thu_holiday'] + $row['fri_holiday'] + $row['sat_holiday']);
			$vacation_hours = ($row['sun_vacation'] + $row['mon_vacation'] + $row['tue_vacation'] + $row['wed_vacation'] + $row['thu_vacation'] + $row['fri_vacation'] + $row['sat_vacation']);
			$personal_hours = ($row['sun_personal'] + $row['mon_personal'] + $row['tue_personal'] + $row['wed_personal'] + $row['thu_personal'] + $row['fri_personal'] + $row['sat_personal']);
			$sick_hours = ($row['sun_sick'] + $row['mon_sick'] + $row['tue_sick'] + $row['wed_sick'] + $row['thu_sick'] + $row['fri_sick'] + $row['sat_sick']);
			$unpaid_hours = ($row['sun_unpaid'] + $row['mon_unpaid'] + $row['tue_unpaid'] + $row['wed_unpaid'] + $row['thu_unpaid'] + $row['fri_unpaid'] + $row['sat_unpaid']);
			$total_hours = $row['weekly_hours'];
			
			if($row['submit_flag'] == 1){
				$submit_status = 'Submitted';
			}else{
				$submit_status = 'Saved';
			}
			
			$objPHPExcel->getActiveSheet()->SetCellValue('A'.$inc, $sl);
			$objPHPExcel->getActiveSheet()->SetCellValue('B'.$inc, $row['staff_code']);
			$objPHPExcel->getActiveSheet()->SetCellValue('C'.$inc, $staff_name);
			$objPHPExcel->getActiveSheet()->SetCellValue('D'.$inc, $row['associate_type']);
			$objPHPExcel->getActiveSheet()->SetCellValue('E'.$inc, $approved_hours);
			$objPHPExcel->getActiveSheet()->SetCellValue('F'.$inc, $unapproved_hours);
			$objPHPExcel->getActiveSheet()->SetCellValue('G'.$inc, $holiday_hours);
			$objPHPExcel->getActiveSheet()->SetCellValue('H'.$inc, $vacation_hours);
			$objPHPExcel->getActiveSheet()->SetCellValue('I'.$inc, $personal_hours);
			$objPHPExcel->getActiveSheet()->SetCellValue('J'.$inc, $sick_hours);
			$objPHPExcel->getActiveSheet()->SetCellValue('K'.$inc, $unpaid_hours);
			$objPHPExcel->getActiveSheet()->SetCellValue('L'.$inc, $total_hours);
			$objPHPExcel->getActiveSheet()->SetCellValue('M'.$inc, $submit_status);
			$objPHPExcel->getActiveSheet()->SetCellValue('N'.$inc, $row['explanation']);
			
			
			/*$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');

?>