<?php
ob_start();
include('includes/session.inc');
$Title = _('Print Roster- Aviation West');
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.
		_('Nadi Airport -Aviation West ') . '" /> ' .// Icon title.
		_('Choose below the roster to print ') . '</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 aflrosterid,
											start_date,
				                            end_date,
											client,
											roster_group,
											patrol_location,
											department,
											status
		                            FROM aflrostering
									WHERE status = 'approved' OR 'pending'
		                             ";

$result=DB_query($SQL);
//echo '<option value="0">0 - ' . _('None Available') . '</option>';
 echo '<option value="0">0 - ' . _('Print All Approved Rosters Only') . '</option>';
while ($myrow=DB_fetch_array($result)){
	
	$SQLpatrol = "SELECT 
											guard_location,
											debtorno
		                            FROM aflpatrollocations
		                            WHERE aflid ='" . $myrow['patrol_location']."' ";
	$resultP=DB_query($SQLpatrol);
	
	while ($myrowP=DB_fetch_array($resultP)){
		$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['aflrosterid']) {
	    
		echo '<option selected="selected" value="' . $myrow['aflrosterid'] . '">' . $myrow['aflrosterid']. ' - ' .$myrow['start_date'] . ' - ' .$myrow['end_date'] .' -' .$myrowR['name'] .' -' .$myrowP['guard_location'] .' -' .$myrow['roster_group'].' -' .$myrow['department'] . '-' .$myrow['status'] .'</option>';
	} else {
			echo '<option value="' . $myrow['aflrosterid'] . '">' . $myrow['aflrosterid'].' - ' .$myrow['start_date'] . ' - ' .$myrow['end_date'] .' -' .$myrowR['name'] .' -' .$myrowP['guard_location'] .' -' .$myrow['roster_group'].' -' .$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/rosteraviationwest.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 aflrosterid,
											start_date,
				                            end_date,
											client,
											roster_group,
											patrol_location,
											department,
											status
		                            FROM aflrostering
								 WHERE aflrosterid = '" . $rosterid."' 
		                             ";

$result=DB_query($SQL);
	
	
	$myrow50=DB_fetch_array($result);
	
	$SQLpatrol = "SELECT 
											guard_location,
											debtorno
		                            FROM aflpatrollocations
		                            WHERE aflid ='" . $myrow50['patrol_location']."' ";
	$resultP=DB_query($SQLpatrol);
	$myrow100=DB_fetch_array($resultP);
	
	$SQLdebtors = "SELECT 
											
											name
											
		                            FROM debtorsmaster
		                            WHERE debtorno ='" . $myrow50['client']."' ";
	$resultR=DB_query($SQLdebtors);
	
	$myrow300=DB_fetch_array($resultR);
      
	
	
	$empty = '';
	
	  $objPHPExcel->getActiveSheet()->setCellValue('B2', $empty );
		$objPHPExcel->getActiveSheet()->setCellValue('F2',  $empty );
		$objPHPExcel->getActiveSheet()->setCellValue('J2', $empty );
		$objPHPExcel->getActiveSheet()->setCellValue('B3', $empty );
		$objPHPExcel->getActiveSheet()->setCellValue('F3', $empty );
		$objPHPExcel->getActiveSheet()->setCellValue('B4', $empty );
		$objPHPExcel->getActiveSheet()->setCellValue('F4', $empty );
		$objPHPExcel->getActiveSheet()->setCellValue('J4', $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++;
		
			
	
	
}

	$timenow = date('Y-m-d H:i:s');
	
	
	//now write to this
	
        $objPHPExcel->getActiveSheet()->setCellValue('B2',  ConvertSQLDate($myrow50['start_date']));
		$objPHPExcel->getActiveSheet()->setCellValue('F2',  ConvertSQLDate($myrow50['end_date']));
		$objPHPExcel->getActiveSheet()->setCellValue('J2', $timenow);
		$objPHPExcel->getActiveSheet()->setCellValue('B3', $myrow300['name']);
		$objPHPExcel->getActiveSheet()->setCellValue('F3', $myrow100['guard_location']);
		$objPHPExcel->getActiveSheet()->setCellValue('B4', $myrow100['account_number']);
		$objPHPExcel->getActiveSheet()->setCellValue('F4', $myrow50['department']);
		$objPHPExcel->getActiveSheet()->setCellValue('J4', $myrow50['aflrosterid']);
	    

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

  
  			

	$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'].''.$myrow3['certifications'];
	    
		 
			
		$objPHPExcel->getActiveSheet()->setCellValue('A'.$cellcount, $employee);
		
		$mon = $myrow3['montime_in'].'-'.$myrow3['montime_out'].'-'.round($myrow3['mon_total']).'h';
		
		
		
	    $objPHPExcel->getActiveSheet()->setCellValue('B'.$cellcount, $mon);
		$objPHPExcel->getActiveSheet()->setCellValue('C'.$cellcount, round($myrow3['mon_total']));
		

			
		//tues
			$tues = $myrow3['tuestime_in'].'-'.$myrow3['tuestime_out'].'-'.round($myrow3['tues_total']).'h';
		
		 
	    $objPHPExcel->getActiveSheet()->setCellValue('D'.$cellcount, $tues);
		$objPHPExcel->getActiveSheet()->setCellValue('E'.$cellcount, round($myrow3['tues_total']));
		

		

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

		

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

		
		

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

		
				

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

		

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

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

		

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

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

?>
