<?php
ob_start();
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
include('includes/session.inc');
$Title = _('Print Timsheet for GFS');
include('includes/SQL_CommonFunctions.inc');

include('includes/header.inc');
echo '<p> </p> <p></p><p class="page_title_text"><img alt="" src="'.$RootPath.'/css/'.$Theme.
		'/images/printer.png" title="' .// Icon image.
		_('Outstanding Approved Job Costing ') . '" /> ' .// Icon title.
		_('Choose below the GFS Roster to Print. Locked Rosters are not shown') . '</p>';// Page title.
echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '">';
	echo '<div class="col-sm-10">';// div class=?
	echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
echo '<table class="table">';

echo '<td>' . _('Select Roster to Print ') . ':</td>
							<td><select name="RosterPeriod">';
							
							$SQL = "SELECT timesuvaid,
											
											start_date,
				                            end_date,
											sectors,
											patrol_location,
											account_number,
											department,
											status
		                            FROM rostering
		                            WHERE status = 'approved'
									
									";

$result=DB_query($SQL);
//echo '<option value="0">0 - ' . _('None Available') . '</option>';
 echo '<option value="0">0 - ' . _('Print All') . '</option>';
while ($myrow=DB_fetch_array($result)){
	
	$SQLpatrol = "SELECT 
											patrollocation,
											debtorno,
											account_number
											
		                            FROM guardforceclient
		                            WHERE clientid ='" . $myrow['patrol_location']."' ";
	$resultP=DB_query($SQLpatrol);
	
	
	$SQLsectors = "SELECT 
											
											name
											
		                            FROM sectors
		                            WHERE id ='" . $myrow['sectors']."' ";
	$resultQ=DB_query($SQLsectors);
	
	
	
	
	while (($myrowP=DB_fetch_array($resultP)) && ($myrowQ=DB_fetch_array($resultQ))) {
		
		$SQLdebtors = "SELECT 
											
											name
											
		                            FROM debtorsmaster
		                            WHERE debtorno ='" . $myrowP['debtorno']."' ";
	$resultR=DB_query($SQLdebtors);
	
	while ($myrowR=DB_fetch_array($resultR)) {
		
	if (isset($_POST['RosterPeriod']) AND $_POST['RosterPeriod']==$myrow['timesuvaid']){
	    
		echo '<option selected="selected" value="' . $myrow['timesuvaid'] . '">' . $myrow['timesuvaid'] . ' - ' .$myrow['start_date'] . ' - ' .$myrow['end_date'] .' -' .$myrowQ['name'] .' -' .$myrowR['name'] .' -'  .$myrowP['patrollocation'] .' -' .$myrow['department'] . '-' .$myrow['status'] .'</option>';
	} else {
			echo '<option value="' . $myrow['timesuvaid'] . '">' . $myrow['timesuvaid'] . ' - ' .$myrow['start_date'] . ' - ' .$myrow['end_date'] .' -' .$myrowQ['name'] .' -' .$myrowR['name'] .' -'  .$myrowP['patrollocation'] .' -' .$myrow['department'] . '-' .$myrow['status'] .'</option>';
	}
	
	}
	}
}
echo '</select>';


echo '<input type="submit" name="PrintRoster" value="' ._('Print Roster') . '" />
		</div>
		';



echo '</div>';

echo '</div>';
$rosterid = $_POST['RosterPeriod'];

$printdate = date("d-m-Y");


	if (isset($_POST['PrintRoster']) && ($_POST['RosterPeriod'] == 0)){


	echo ' <b><font style="color: red">all rosters will be printed. </font></b>';	
		
	}

elseif (isset($_POST['PrintRoster']) && ($_POST['RosterPeriod'] == $rosterid)){
		
	
	
	
if (PHP_SAPI == 'cli')
	die('This example should only be run from a Web Browser');

/** Include PHPExcel */
//include_once('Classes/PHPExcel.php');


include_once('Classes/PHPExcel/IOFactory.php');

$inputFileName = 'printing/timesheetgfs.xlsx';
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);


/*check point*/

// Read the existing excel file
//$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(false);
$objPHPExcel = $objReader->load($inputFileName);

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
//$objPHPExcel->setActiveSheetIndex();

// Add months and till date information derived from above
//$objPHPExcel->getActiveSheet()->duplicateStyle($objPHPExcel-‌​>getActiveSheet()->g‌​etStyle('B2'), 'B3:B20');

	//header
	
			$SQL = "SELECT timesuvaid,
											
											start_date,
				                            end_date,
											sectors,
											patrol_location,
											account_number,
											department,
											status
		                            FROM timesheetsuva
		                            WHERE timesuvaid = '" . $rosterid."' 
									
									";

$result=DB_query($SQL);
	
	$myrow50=DB_fetch_array($result);
	
	
	
	$timenow = date('Y-m-d H:i:s');
	
	$SQLpatrol = "SELECT 
											patrollocation,
											debtorno,
											account_number
											
		                            FROM guardforceclient
		                            WHERE clientid ='" . $myrow50['patrol_location']."' ";
	$resultP=DB_query($SQLpatrol);
	
		$myrow100=DB_fetch_array($resultP);
	$SQLsectors = "SELECT 
											
											name
											
		                            FROM sectors
		                            WHERE id ='" . $myrow50['sectors']."' ";
	$resultQ=DB_query($SQLsectors);
	
	$myrow200=DB_fetch_array($resultQ);
	$SQLdebtors = "SELECT 
											
											name
											
		                            FROM debtorsmaster
		                            WHERE debtorno ='" . $myrow100['debtorno']."' ";
	$resultR=DB_query($SQLdebtors);
	
	
	$myrow300=DB_fetch_array($resultR);
	
	//clear or empty needed cells first
	
	$empty = '';
	$objPHPExcel->getActiveSheet()->setCellValue('B2', $empty );
		$objPHPExcel->getActiveSheet()->setCellValue('F2',  $empty );
		$objPHPExcel->getActiveSheet()->setCellValue('L2', $empty );
		$objPHPExcel->getActiveSheet()->setCellValue('B3', $empty );
		$objPHPExcel->getActiveSheet()->setCellValue('F3', $empty );
		$objPHPExcel->getActiveSheet()->setCellValue('B4', $empty );
		$objPHPExcel->getActiveSheet()->setCellValue('F4', $empty );
		$objPHPExcel->getActiveSheet()->setCellValue('L4', $empty );
	    $objPHPExcel->getActiveSheet()->setCellValue('B5', $empty );
	
	//delete other cells in ranges
	
	




	$cellcount = 8;	
while ($cellcount < 36 ) {
	
		
	
		
		
	
		
	   	
	  $objPHPExcel->getActiveSheet()->setCellValue('A'.$cellcount, $empty);
		
		$objPHPExcel->getActiveSheet()->setCellValue('B'.$cellcount, $empty);
		$objPHPExcel->getActiveSheet()->setCellValue('C'.$cellcount, $empty);
		$objPHPExcel->getActiveSheet()->setCellValue('D'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('E'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('F'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('G'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('H'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('I'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('J'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('K'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('L'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('M'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('N'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('O'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('P'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('Q'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('R'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('S'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('T'.$cellcount, $empty);
	$objPHPExcel->getActiveSheet()->setCellValue('U'.$cellcount, $empty);
	
		$cellcount++;
		
			
	
	
}

	
	
	
	//now write to this
	
         $objPHPExcel->getActiveSheet()->setCellValue('B2',  ConvertSQLDate($myrow50['start_date']));
		$objPHPExcel->getActiveSheet()->setCellValue('F2',  ConvertSQLDate($myrow50['end_date']));
		$objPHPExcel->getActiveSheet()->setCellValue('L2', $timenow);
		$objPHPExcel->getActiveSheet()->setCellValue('B3', $myrow300['name']);
		$objPHPExcel->getActiveSheet()->setCellValue('F3', $myrow100['patrollocation']);
		$objPHPExcel->getActiveSheet()->setCellValue('B4', $myrow100['account_number']);
		$objPHPExcel->getActiveSheet()->setCellValue('F4', $myrow50['department']);
		$objPHPExcel->getActiveSheet()->setCellValue('L4', $myrow50['rosterid']);
	    $objPHPExcel->getActiveSheet()->setCellValue('B5', $myrow200['name']);
	
	
	   
	
// Generate an updated excel file
// Redirect output to a client’s web browser (Excel2007)
	//employee schedules

  $SQL = "SELECT timesheetsuva.*,
  				
                  employeemaster.first_name
				  
				  
				FROM timesheetsuva INNER JOIN employeemaster
				ON timesheetsuva.employeeid = employeemaster.employeeid 
				WHERE timesheetsuva.timesuvaid='" . $myrow50['timesuvaid']."'
				";

  
  			

	$AccountsResult = DB_query($SQL);

	

$num= DB_num_rows($AccountsResult);


$rowCount= 0;
	
while ($rowCount< $num) {
	$cellcount = 8;	
	$cellcount2 = 9;	
	while ($myrow3=DB_fetch_array($AccountsResult)) {
		
		
	
		$employee = $myrow3['employeeid'].''.$myrow3['first_name'];
	    
		 
		
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$cellcount, $employee);
		
		$mon = $myrow3['montime_in'].'-'.$myrow3['montime_out'].'-'.round($myrow3['mon_hrs']).'h';
		
		 $mon1 = $myrow3['montime_in1'].'-'.$myrow3['montime_out1'].'-'.round($myrow3['mon_hrs2']).'h';
		
	    $objPHPExcel->getActiveSheet()->setCellValue('B'.$cellcount, $mon);
		$objPHPExcel->getActiveSheet()->setCellValue('C'.$cellcount, round($myrow3['mon_hrs']));
		

		
		 $objPHPExcel->getActiveSheet()->setCellValue('B'.$cellcount2, $mon1);
		$objPHPExcel->getActiveSheet()->setCellValue('C'.$cellcount2, round($myrow3['mon_hrs2']));
		
		//tues
			$tues = $myrow3['tuestime_in'].'-'.$myrow3['tuestime_out'].'-'.round($myrow3['tues_hrs']).'h';
		
		 $tues1 = $myrow3['tuestime_in1'].'-'.$myrow3['tuestime_out1'].'-'.round($myrow3['tues_hrs2']).'h';
		
	    $objPHPExcel->getActiveSheet()->setCellValue('D'.$cellcount, $tues);
		$objPHPExcel->getActiveSheet()->setCellValue('E'.$cellcount, round($myrow3['tues_hrs']));
		

		
		 $objPHPExcel->getActiveSheet()->setCellValue('D'.$cellcount2, $tues1);
		$objPHPExcel->getActiveSheet()->setCellValue('E'.$cellcount2, round($myrow3['tues_hrs2']));

			$wed = $myrow3['wedtime_in'].'-'.$myrow3['wedtime_out'].'-'.round($myrow3['wed_hrs']).'h';
		
		 $wed1 = $myrow3['wedtime_in1'].'-'.$myrow3['wedtime_out1'].'-'.round($myrow3['wed_hrs2']).'h';
		
	    $objPHPExcel->getActiveSheet()->setCellValue('F'.$cellcount, $wed);
		$objPHPExcel->getActiveSheet()->setCellValue('G'.$cellcount, round($myrow3['wed_hrs']));
		

		
		 $objPHPExcel->getActiveSheet()->setCellValue('F'.$cellcount2, $wed1);
		$objPHPExcel->getActiveSheet()->setCellValue('G'.$cellcount2, round($myrow3['wed_hrs2']));		

	
			$thurs = $myrow3['thurstime_in'].'-'.$myrow3['thurstime_out'].'-'.round($myrow3['thurs_hrs']).'h';
		
		 $thurs1 = $myrow3['thurstime_in1'].'-'.$myrow3['thurstime_out1'].'-'.round($myrow3['thurs_hrs2']).'h';
		
	    $objPHPExcel->getActiveSheet()->setCellValue('H'.$cellcount, $thurs);
		$objPHPExcel->getActiveSheet()->setCellValue('I'.$cellcount, round($myrow3['thurs_hrs']));
		

		
		 $objPHPExcel->getActiveSheet()->setCellValue('H'.$cellcount2, $thurs1);
		$objPHPExcel->getActiveSheet()->setCellValue('I'.$cellcount2, round($myrow3['thurs_hrs2']));		

			$fri = $myrow3['fritime_in'].'-'.$myrow3['fritime_out'].'-'.round($myrow3['fri_hrs']).'h';
		
		 $fri1 = $myrow3['fritime_in1'].'-'.$myrow3['fritime_out1'].'-'.round($myrow3['fri_hrs2']).'h';
		
	    $objPHPExcel->getActiveSheet()->setCellValue('J'.$cellcount, $fri);
		$objPHPExcel->getActiveSheet()->setCellValue('K'.$cellcount, round($myrow3['fri_hrs']));
		

		
		 $objPHPExcel->getActiveSheet()->setCellValue('J'.$cellcount2, $fri1);
		$objPHPExcel->getActiveSheet()->setCellValue('K'.$cellcount2, round($myrow3['fri_hrs2']));			

				$sat = $myrow3['sattime_in'].'-'.$myrow3['sattime_out'].'-'.round($myrow3['sat_hrs']).'h';
		
		 $sat1 = $myrow3['sattime_in1'].'-'.$myrow3['sattime_out1'].'-'.round($myrow3['sat_hrs2']).'h';
		
	    $objPHPExcel->getActiveSheet()->setCellValue('L'.$cellcount, $sat);
		$objPHPExcel->getActiveSheet()->setCellValue('M'.$cellcount, round($myrow3['sat_hrs']));
		

		
		 $objPHPExcel->getActiveSheet()->setCellValue('L'.$cellcount2, $sat1);
		$objPHPExcel->getActiveSheet()->setCellValue('M'.$cellcount2, round($myrow3['sat_hrs2']));			
	

			$sun = $myrow3['sunttime_in'].'-'.$myrow3['sunttime_out'].'-'.round($myrow3['sun_hrs']).'h';
		
		 $sun1 = $myrow3['suntime_in1'].'-'.$myrow3['suntime_out1'].'-'.round($myrow3['sun_hrs2']).'h';
		
	    $objPHPExcel->getActiveSheet()->setCellValue('N'.$cellcount, $sun);
		$objPHPExcel->getActiveSheet()->setCellValue('O'.$cellcount, round($myrow3['sun_hrs']));
		

		
		 $objPHPExcel->getActiveSheet()->setCellValue('N'.$cellcount2, $sun1);
		$objPHPExcel->getActiveSheet()->setCellValue('O'.$cellcount2, round($myrow3['sun_hrs2']));			

		
			$mon2 = $myrow3['mon2time_in'].'-'.$myrow3['mon2time_out'].'-'.round($myrow3['mon2_hrs']).'h';
		
		 $mon21 = $myrow3['mon2time_in1'].'-'.$myrow3['mon2time_out1'].'-'.round($myrow3['mon2_hrs2']).'h';
		
	    $objPHPExcel->getActiveSheet()->setCellValue('P'.$cellcount, $mon2);
		$objPHPExcel->getActiveSheet()->setCellValue('Q'.$cellcount, round($myrow3['mon2_hrs']));
		

		
		 $objPHPExcel->getActiveSheet()->setCellValue('P'.$cellcount2, $mon21);
		$objPHPExcel->getActiveSheet()->setCellValue('Q'.$cellcount2, round($myrow3['mon2_hrs2']));			
         
		
		 $objPHPExcel->getActiveSheet()->setCellValue('R'.$cellcount, round($myrow3['total_hrs']));
		$objPHPExcel->getActiveSheet()->setCellValue('S'.$cellcount, round($myrow3['normal_hours']));
		 $objPHPExcel->getActiveSheet()->setCellValue('T'.$cellcount, round($myrow3['time_half_hours']));
		$objPHPExcel->getActiveSheet()->setCellValue('U'.$cellcount, round($myrow3['double_hours']));	


		
		
		
		$cellcount+=2;
		$cellcount2+=2;

		
	}
		
		$rowCount++;
	
	
}
   
// Generate an updated excel file
// Redirect output to a client’s web browser (Excel2007)



//save the file to the server (Excel2007)

//include('includes/header.inc');
	


$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//$objWriter->save($inputFileName);
$objWriter->save($inputFileName);


    
    //<?php
	
echo '<br /><p></p><p></p> <p></p> <p></p> <p></p>  <p class="page_title_text"><a href="' . $inputFileName . '">' . _('File generated o.k. Click to view this  excel File ') . '</p>';




//echo $AccountCode;



$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);
		
				
}

			
		
			
		
			
		
		
		
			    
		
	
//echo 'Quotation Created Successfully'. $jobno.'!';




//echo 'Quotation Created Successfully'. $jobno.'!';
echo '</div>';
echo '</div>';

//include('includes/footer.inc'); 

?>

