<?php
	error_reporting(0);
	//error_reporting(E_ALL);
	session_start();
	include('../db_conn.php');
	$con = mysqli_connect($db_server,$db_user,$db_pwd, $GLOBALS["db_name"]);
	if (!$con)
	  {
	  die('Could not connect: ' . 'Database Error!');
	  }
	mysqli_set_charset($con, "UTF8");

	if (PHP_SAPI == 'cli')
		die('This example should only be run from a Web Browser');
	
	require_once('_functions.php');
	require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';
	
	$objPHPExcel = new PHPExcel();
	$objPHPExcel->getProperties()->setCreator("MitzvahOrganizer.com")
								 ->setLastModifiedBy("MitzvahOrganizer.com")
								 ->setTitle("MitzvahOrganizer.com")
								 ->setSubject("MitzvahOrganizer.com")
								 ->setDescription("MitzvahOrganizer.com")
								 ->setKeywords("MitzvahOrganizer.com")
								 ->setCategory("MitzvahOrganizer.com");
	$sheet = $objPHPExcel->getActiveSheet();
	$sheet0 = $objPHPExcel->setActiveSheetIndex(0);
	$sql_header = "SELECT * FROM events WHERE user_id = ".$_SESSION['user_id']." AND id = ".$_SESSION['active_event'];
	$result_header = mysqli_query($GLOBALS['con'], $sql_header);
	if (mysqli_num_rows($result_header) > 0) :
		$row_header = mysqli_fetch_object($result_header);
		$objPHPExcel->setActiveSheetIndex(0)
					->setCellValue('A1', 'Event: ')
					->setCellValue('B1', html_entity_decode($row_header->event_name, ENT_QUOTES))
					->setCellValue('A2', 'Date: ')
					->setCellValue('B2', $row_header->event_date)
					->mergeCells('B1:H1');
	endif;
	$sql = gzuncompress(sys_decrypt($_GET['sql'], $_SESSION['rand_key']));
	$result = mysqli_query($GLOBALS['con'], $sql);
	$first_row = true;
	$curr_row = 3;
	$tot_cols = 1;
	$ar_field_types = array();
	$i = 0;
	while ($i < mysqli_num_fields($result)) :
		$meta = mysqli_fetch_field($result);
		if (!$meta) :
			$ar_field_types[$i] = 'string';
		else :
			$ar_field_types[$i] = $meta->type;
		endif;
		$i++;
	endwhile;
	while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) :
		if ($first_row) :
			$curr_col = 0;
			foreach($row as $key => $row_field) :
				$sheet0->setCellValueByColumnAndRow($curr_col, $curr_row, $key);
							//->setCellValue('A'.$curr_row, $key);
				$sheet->getStyleByColumnAndRow($curr_col, $curr_row)->applyFromArray(
					array	(	'fill' 	=> array('type'	=> PHPExcel_Style_Fill::FILL_SOLID,	'color'	=> array('rgb' => 'E93F86')),
								'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'color' => array('argb' => PHPExcel_Style_Color::COLOR_WHITE))),
								'font' => array('color' => array('argb' => PHPExcel_Style_Color::COLOR_WHITE)),
								'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
						 	)
					);
				$sheet->getColumnDimension('B')->setAutoSize(true);

				$curr_col = $curr_col + 1;
			endforeach;
			$tot_cols = $curr_col;
			$curr_row = $curr_row + 1;
			$first_row = false;
		endif;
		$max_col = PHPExcel_Cell::stringFromColumnIndex($tot_cols - 1);
		$curr_col = 0;
		foreach($row as $row_field) :
			if ($ar_field_types[$curr_col] == 'int') :
				$sheet0->setCellValueExplicitByColumnAndRow($curr_col, $curr_row, html_entity_decode($row_field, ENT_QUOTES), PHPExcel_Cell_DataType::TYPE_NUMERIC);
			elseif ($ar_field_types[$curr_col] == 'real') :
				$sheet0->setCellValueExplicitByColumnAndRow($curr_col, $curr_row, html_entity_decode($row_field, ENT_QUOTES), PHPExcel_Cell_DataType::TYPE_NUMERIC);
			else :
				$sheet0->setCellValueExplicitByColumnAndRow($curr_col, $curr_row, html_entity_decode($row_field, ENT_QUOTES), PHPExcel_Cell_DataType::TYPE_STRING);
			endif;
			$curr_col = $curr_col + 1;
		endforeach;
		$curr_row = $curr_row + 1;
	endwhile;
	$max_row = $curr_row - 1;
	if ($max_row > 3) :
		$i = 0;
		while ($i < mysqli_num_fields($result)) :
			if ($ar_field_types[$i] == 'int') :
				$sheet->getStyle(PHPExcel_Cell::stringFromColumnIndex($i).'4:'.PHPExcel_Cell::stringFromColumnIndex($i).$max_row)->applyFromArray(
					array	('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT) )
				);
			elseif ($ar_field_types[$i] == 'real') :
				$sheet->getStyle(PHPExcel_Cell::stringFromColumnIndex($i).'4:'.PHPExcel_Cell::stringFromColumnIndex($i).$max_row)->applyFromArray(
					array	('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT) )
					);
				$sheet->getStyle(PHPExcel_Cell::stringFromColumnIndex($i).'4:'.PHPExcel_Cell::stringFromColumnIndex($i).$max_row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
			else :
				$sheet->getStyle(PHPExcel_Cell::stringFromColumnIndex($i).'4:'.PHPExcel_Cell::stringFromColumnIndex($i).$max_row)->applyFromArray(
					array	('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT) )
					);
			endif;
			$i++;
		endwhile;
		$sheet->getStyle('A4:'.$max_col.$max_row)->applyFromArray(
			array	(	'fill' 	=> array('type'	=> PHPExcel_Style_Fill::FILL_SOLID,	'color'	=> array('rgb' => 'E2C3D0'))
						,'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'color' => array('argb' => PHPExcel_Style_Color::COLOR_WHITE)))
						//,'font' => array('color' => array('argb' => PHPExcel_Style_Color::COLOR_WHITE))
						//,'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)
					)
			);
	endif;

	foreach (range(0, $tot_cols) as $col) :
		$sheet->getColumnDimensionByColumn($col)->setAutoSize(true);
	endforeach;
	
	// Rename worksheet
	$sheet->setTitle('exportfile');
	
	// Set active sheet index to the first sheet, so Excel opens this as the first sheet
	$sheet0;
	
	// Redirect output to a client�s web browser (Excel5)
	header('Content-Type: application/vnd.ms-excel');
	header('Content-Disposition: attachment;filename="exportfile.xls"');
	header('Cache-Control: max-age=0');
	// If you're serving to IE 9, then the following may be needed
	header('Cache-Control: max-age=1');
	
	// If you're serving to IE over SSL, then the following may be needed
	header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
	header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
	header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
	header ('Pragma: public'); // HTTP/1.0
	
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	$objWriter->save('php://output');
	exit;

?>