<?php
	//ini_set('display_errors',1);
	//error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED);
	error_reporting(0);
	session_start();
	//echo 'here'; return;
	$get_save = $_GET;
	include('../../../wp-config.php');
	$_GET = $get_save;
	date_default_timezone_set(APP_TIMEZONE);
	try {
		$db_connect_string = 'mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset='.DB_CHARSET.';';
		global $db; 
		$db = new PDO($db_connect_string, DB_USER, DB_PASSWORD);
	} catch (Exception $e) {
		die('Could not connect: ' . 'Database Error!');
	}
	$sql_tz = "SET time_zone = '".get_option('timezone_string', 'America/New_York')."'";
	$db->query($sql_tz);
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

	if (PHP_SAPI == 'cli')
		die('This example should only be run from a Web Browser');
	
	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("LI FamFest")
								 ->setDescription("LI FamFest")
								 ->setKeywords("LI FamFest")
								 ->setCategory("LI FamFest");
	$sheet = $objPHPExcel->getActiveSheet();
	$sheet0 = $objPHPExcel->setActiveSheetIndex(0);
	//echo htmlspecialchars(urldecode($_GET['sql'])).'<br />';
	//echo urlencode($_GET['sql']).'<br />';
	$sql = gzuncompress(lcs_pdb_sys_decrypt($_GET['sql'], $_SESSION['lcs_rand_key']));
	//echo $sql; return;
	$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);
				if (strtolower(substr($row_field,0,7)) == 'http://' || strtolower(substr($row_field,0,8)) == 'https://') :
					//$url = str_replace('http://', '', $link);
					//$sheet0->getCellByColumnAndRow($curr_col, $curr_row)->getHyperlink()->setUrl('http://www.'.$url);
					$parts = parse_url($row_field);
					parse_str($parts['query'], $query);
					//echo $query['email'];
					$query['mslink'] = 'true';
					$url = $parts['scheme'].'://'.$parts['host'].$parts['path'].'?'.http_build_query($query); 
					$sheet0->getCellByColumnAndRow($curr_col, $curr_row)->getHyperlink()->setUrl($url);
					$sheet->getStyleByColumnAndRow($curr_col, $curr_row)->applyFromArray(
						array	(	'font' => array('color' => array('argb' => PHPExcel_Style_Color::COLOR_BLUE)),
								)
						);
				endif;
			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;