<?php
require "../connect.php";

require_once 'PHPExcel.php';
require_once 'PHPExcel/IOFactory.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getDefaultStyle()->getFont()->setName('Tahoma');// Set Font Style In Excel
$objPHPExcel->getDefaultStyle()->getFont()->setSize(12);// Set Font Size In Excel

// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
$texts=range('A', 'Y');//คอลัมน์
foreach ($texts as $key => $value) {
		$objPHPExcel->getActiveSheet()->getStyle($value.'1')->getFont()->setBold(true);// text Bold

	}	

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'registerId');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Code');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'name');
$objPHPExcel->getActiveSheet()->setCellValue('D1', 'surName');
$objPHPExcel->getActiveSheet()->setCellValue('E1', 'age');
$objPHPExcel->getActiveSheet()->setCellValue('F1', 'tel');
$objPHPExcel->getActiveSheet()->setCellValue('G1', 'status');
$objPHPExcel->getActiveSheet()->setCellValue('H1', 'career');
$objPHPExcel->getActiveSheet()->setCellValue('I1', 'incomePerMonthFamily');
$objPHPExcel->getActiveSheet()->setCellValue('J1', 'bedroom');
$objPHPExcel->getActiveSheet()->setCellValue('K1', 'parking');
$objPHPExcel->getActiveSheet()->setCellValue('L1', 'budget');
$objPHPExcel->getActiveSheet()->setCellValue('M1', 'timeToStay');
$objPHPExcel->getActiveSheet()->setCellValue('N1', 'buy');
$objPHPExcel->getActiveSheet()->setCellValue('O1', 'borrow');
$objPHPExcel->getActiveSheet()->setCellValue('P1', 'allow');
$objPHPExcel->getActiveSheet()->setCellValue('Q1', 'loanRate');
$objPHPExcel->getActiveSheet()->setCellValue('R1', 'bankName');
$objPHPExcel->getActiveSheet()->setCellValue('S1', 'news_know');
$objPHPExcel->getActiveSheet()->setCellValue('T1', 'forcheck');
$objPHPExcel->getActiveSheet()->setCellValue('U1', 'registerId');
$objPHPExcel->getActiveSheet()->setCellValue('V1', 'locationId');
$objPHPExcel->getActiveSheet()->setCellValue('W1', 'registerId');
$objPHPExcel->getActiveSheet()->setCellValue('X1', 'houseId');
$objPHPExcel->getActiveSheet()->setCellValue('Y1', 'registerId');
$objPHPExcel->getActiveSheet()->setCellValue('Z1', 'personId');


//Select data From Database Of Register
$txt=range('A', 'Y');
$sqlregister="SELECT * FROM tb_register where event_id = $_GET[event_id]";
$quaryregister=mysqli_query($conn,$sqlregister);
$countregister=mysqli_num_rows($quaryregister)+1;

foreach ($txt as $key => $val) {
		$objPHPExcel->getActiveSheet()->getColumnDimension(''.$val.'')->setWidth(15); //width of column
	}

//----------------------------Showdata Of tb_register-----------------------------------------------//
for($i=2;$i<=$countregister;$i++){
	$row=mysqli_fetch_assoc($quaryregister);
	
	$objPHPExcel->getActiveSheet()->setCellValue('A'.$i,$row['registerId']);
	$objPHPExcel->getActiveSheet()->setCellValue('B'.$i,$row['code']);
	$objPHPExcel->getActiveSheet()->setCellValue('C'.$i,$row['name']);
	$objPHPExcel->getActiveSheet()->setCellValue('D'.$i,$row['surName']);
	$objPHPExcel->getActiveSheet()->setCellValue('E'.$i,$row['age']);
	$objPHPExcel->getActiveSheet()->setCellValue('F'.$i,$row['tel']);
	$objPHPExcel->getActiveSheet()->setCellValue('G'.$i,$row['status']);
	$objPHPExcel->getActiveSheet()->setCellValue('H'.$i,$row['career']);
	$objPHPExcel->getActiveSheet()->setCellValue('I'.$i,$row['incomePerMonthFamily']);
	$objPHPExcel->getActiveSheet()->setCellValue('J'.$i,$row['bedroom']);
	$objPHPExcel->getActiveSheet()->setCellValue('K'.$i,$row['parking']);
	$objPHPExcel->getActiveSheet()->setCellValue('L'.$i,$row['budget']);
	$objPHPExcel->getActiveSheet()->setCellValue('M'.$i,$row['timeToStay']);
	$objPHPExcel->getActiveSheet()->setCellValue('N'.$i,$row['buy']);
	$objPHPExcel->getActiveSheet()->setCellValue('O'.$i,$row['borrow']);
	$objPHPExcel->getActiveSheet()->setCellValue('P'.$i,$row['allow']);
	$objPHPExcel->getActiveSheet()->setCellValue('Q'.$i, $row['loanRate']);
	$objPHPExcel->getActiveSheet()->setCellValue('R'.$i, $row['bankName']);
	$objPHPExcel->getActiveSheet()->setCellValue('S'.$i, $row['news_know']);
	$objPHPExcel->getActiveSheet()->setCellValue('T'.$i, $row['forcheck']);
}

//----------------------------Showdata Of location register-----------------------------------------------//
//Select data From Database Of location register
$sqllocation="SELECT * FROM tb_location_of_register WHERE event_id = $_GET[event_id]";
$quarylocation=mysqli_query($conn,$sqllocation);
$countlocate=mysqli_num_rows($quarylocation)+1;


for($i=2;$i<=$countlocate;$i++){
	$row2=mysqli_fetch_assoc($quarylocation);
	
	$objPHPExcel->getActiveSheet()->setCellValue('U'.$i,$row2['registerId']);
	$objPHPExcel->getActiveSheet()->setCellValue('V'.$i,$row2['locationId']);
	}

//----------------------------Showdata Of location register-----------------------------------------------//


//----------------------------Showdata Of house register-----------------------------------------------//
//Select data From Database Of house register
$sqlhouse="SELECT * FROM tb_house_of_register WHERE event_id=$_GET[event_id]";
$quaryhouse=mysqli_query($conn,$sqlhouse);
$counthouse=mysqli_num_rows($quaryhouse)+1;
for($i=2;$i<=$counthouse;$i++){
	$row3=mysqli_fetch_assoc($quaryhouse);
	
	$objPHPExcel->getActiveSheet()->setCellValue('W'.$i,$row3['registerId']);
	$objPHPExcel->getActiveSheet()->setCellValue('X'.$i,$row3['houseId']);
	
}
//----------------------------Showdata Of house register-----------------------------------------------//

//----------------------------Showdata Of house register-----------------------------------------------//
//Select data From Database Of house register
$sqlperson="SELECT * FROM tb_person_of_register WHERE event_id=$_GET[event_id]";
$quaryperson=mysqli_query($conn,$sqlperson);
$countperson=mysqli_num_rows($quaryperson)+1;
for($i=2;$i<=$countperson;$i++){
	$row4=mysqli_fetch_assoc($quaryperson);
	
	$objPHPExcel->getActiveSheet()->setCellValue('Y'.$i,$row4['registerId']);
	$objPHPExcel->getActiveSheet()->setCellValue('Z'.$i,$row4['personId']);
	
}

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Index');

// Create a new worksheet, after the default sheet
$objPHPExcel->createSheet();

$sqltb_location="SELECT * FROM tb_location";
$quarytb_location=mysqli_query($conn,$sqltb_location);
$countsqltb=mysqli_num_rows($quarytb_location)+1;

// Add some data to the second sheet, resembling some different data types
$objPHPExcel->setActiveSheetIndex(1);
$txts = range('A', 'B');//คอลัมน์
foreach ($txts as $key => $values) {
		$objPHPExcel->getActiveSheet()->getStyle($values.'1')->getFont()->setBold(true);// text Bold

	}	
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'locationId');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'locationName');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'county');
for ($i=2; $i <=$countsqltb ; $i++) { 
	# code...

$row5=mysqli_fetch_assoc($quarytb_location);

$objPHPExcel->getActiveSheet()->setCellValue('A'.$i,$row5['locationId']);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$i,$row5['location']);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$i,$row5['county']);


}
// Rename 2nd sheet
$objPHPExcel->getActiveSheet()->setTitle('location');


// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Bureau.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
?>
<?php require "../close_connect.php" ?>