<?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 timesheet WHERE `date_from` BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 4 WEEK) AND CURRENT_DATE() AND `submit_flag` = '0'";
		
		
		$result = mysql_query($sql);
		
		// Instantiate a new PHPExcel object
		$objPHPExcel = new PHPExcel(); 
		$filename = "Outstanding Report for Pripor 4 Weeks.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
		));
	
		$current_date = date('d-m-Y');
		$pDate = strtotime(''.$current_date.' - 4 week');
		 
		$inc = 4;
		$sl = 1;
		while(false !== ($row = mysql_fetch_assoc($result))) { 
			
			$sql_staff = mysql_fetch_array(mysql_query("SELECT * FROM sat_staff WHERE staff_id = '".$row['staff_id']."'"));
			
			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(20);
			 $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('D1')->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()->getStyle('e3')->applyFromArray($styleArrayBold);
			
			 $objPHPExcel->getActiveSheet()->mergeCells('A1:C1');
			 $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Staff Outstanding Report -->');
			 $objPHPExcel->getActiveSheet()->SetCellValue('D1', date('m-d-Y',$pDate));
			 $objPHPExcel->getActiveSheet()->SetCellValue('E1', date('m-d-Y'));
			 $objPHPExcel->getActiveSheet()->SetCellValue('A3', '#');
			 $objPHPExcel->getActiveSheet()->SetCellValue('B3', 'Name');
			 $objPHPExcel->getActiveSheet()->SetCellValue('C3', 'Phone(Cell)');
			 $objPHPExcel->getActiveSheet()->SetCellValue('D3', 'Email(Primary)');
			 $objPHPExcel->getActiveSheet()->SetCellValue('E3', 'Week Ending');
			 
			 
			 
			
			 $flag = true; 
	        }         
	       // array_walk($row, 'cleanData');
			
			
			 $objPHPExcel->getActiveSheet()->SetCellValue('A'.$inc, $sl);
			 $objPHPExcel->getActiveSheet()->SetCellValue('B'.$inc, $sql_staff['firstname']." ".$sql_staff['lastname']);
			 $objPHPExcel->getActiveSheet()->SetCellValue('C'.$inc, $sql_staff['cphone']);
			 $objPHPExcel->getActiveSheet()->SetCellValue('D'.$inc, $sql_staff['pemail']);
			 $objPHPExcel->getActiveSheet()->SetCellValue('E'.$inc, change_dateformat_get_day_rev($row['date_to']));
			
			
			
			/*$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');

?>