<?php
	//ini_set('display_errors',1);
	//error_reporting(E_ALL & ~E_NOTICE);
	error_reporting(0);
	session_start();
	include('../config.php');
	date_default_timezone_set(APP_TIMEZONE);
	$db = new PDO(DB_CONNECT_STRING, DB_USER, DB_PWD);
	if (!$db) :
		die('Could not connect: ' . 'Database Error!');
	else :
		$sql_tz = "SET time_zone = '".APP_TIMEZONE."'";
		$db->query($sql_tz);
	endif;

	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("Davler Media Group")
								 ->setLastModifiedBy("Davler Media Group")
								 ->setTitle("Davler Media Group")
								 ->setSubject("NYMP Tracking Database")
								 ->setDescription("NYMP Tracking Database")
								 ->setKeywords("NYMP Tracking Database")
								 ->setCategory("NYMP Tracking Database");
	$sheet = $objPHPExcel->getActiveSheet();
	$sheet0 = $objPHPExcel->setActiveSheetIndex(0);
	$sql = gzuncompress(sys_decrypt($_GET['sql'], $_SESSION['rand_key']));
	$result = $db->query($sql) or die('Database Error!');
	$first_row = true;
	$curr_row = 1;  /* 3 for setups with 2 lines of headers */
	$tot_cols = 1;
	$ar_field_types = array();
	$i = 0;
	//$table_fields = $result->fetchAll(PDO::FETCH_COLUMN);
	while ($i < $result->columnCount()) :
		$meta = $result->getColumnMeta($i);
		if (!$meta) :
			$ar_field_types[$i] = 'string';
		else :
			/*
			echo PDO::PARAM_INT;
			echo PDO::PARAM_BOOL;
			var_dump($meta);
			echo '<br />';
			*/
			$ar_field_types[$i] = strtolower($meta['native_type']);
		endif;
		$i++;
	endwhile;
	while ($row = $result->fetch(PDO::FETCH_ASSOC)) :
		if ($first_row) :
			$curr_col = 0;
			foreach($row as $key => $row_field) :
				$sheet0->setCellValueByColumnAndRow($curr_col, $curr_row, $key);
				$sheet->getStyleByColumnAndRow($curr_col, $curr_row)->applyFromArray(
					array	(	'fill' 	=> array('type'	=> PHPExcel_Style_Fill::FILL_SOLID,	'color'	=> array('rgb' => '3E4B62')),
								'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) :
			$row_field = utf8_encode($row_field);
			//echo $curr_col.' === '.$ar_field_types[$curr_col].' === <br />';
			if ($ar_field_types[$curr_col] == 'long' || $ar_field_types[$curr_col] == 'longlong' || $ar_field_types[$curr_col] == 'newdecimal' || $ar_field_types[$curr_col] == 'float') :
				$sheet0->setCellValueExplicitByColumnAndRow($curr_col, $curr_row, html_entity_decode($row_field, ENT_QUOTES), PHPExcel_Cell_DataType::TYPE_NUMERIC);
			/*
			elseif ($ar_field_types[$curr_col] == 'float') :
				$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;
	//die();
	$max_row = $curr_row - 1;
	if ($max_row > 3) :
		$i = 0;
		while ($i < $result->columnCount()) :
			if ($ar_field_types[$i] == 'long' || $ar_field_types[$i] == 'longlong') :
				$sheet->getStyle(PHPExcel_Cell::stringFromColumnIndex($i).'2:'.PHPExcel_Cell::stringFromColumnIndex($i).$max_row)->applyFromArray(
					array	('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT) )
				);
			elseif ($ar_field_types[$i] == 'newdecimal' || $ar_field_types[$i] == 'float') :
				$sheet->getStyle(PHPExcel_Cell::stringFromColumnIndex($i).'2:'.PHPExcel_Cell::stringFromColumnIndex($i).$max_row)->applyFromArray(
					array	('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT) )
					);
				$sheet->getStyle(PHPExcel_Cell::stringFromColumnIndex($i).'2:'.PHPExcel_Cell::stringFromColumnIndex($i).$max_row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
			else :
				$sheet->getStyle(PHPExcel_Cell::stringFromColumnIndex($i).'2:'.PHPExcel_Cell::stringFromColumnIndex($i).$max_row)->applyFromArray(
					array	('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT) )
					);
			endif;
			$i++;
		endwhile;

		$sheet->getStyle('A2:'.$max_col.$max_row)->applyFromArray(
			array	(	'fill' 	=> array('type'	=> PHPExcel_Style_Fill::FILL_SOLID,	'color'	=> array('rgb' => 'ECEDEF'))
						,'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM, 'color' => array('argb' => PHPExcel_Style_Color::COLOR_WHITE)))
					)
			);
	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;

?>