<?php

header('Cache-Control: no-cache, no-store, must-revalidate');   
header('Expires: 0');
header('Access-Control-Allow-Origin: *');
set_time_limit(3600);
ini_set('memory_limit','1024M');

// error_reporting(E_ALL);
// ini_set('display_errors', TRUE);
// ini_set('display_startup_errors', TRUE);

date_default_timezone_set('Europe/London');
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

// Table Print

$mFunction  		= $_GET['function'];
$mConnect   		= $_GET['connection'];
$mUser				= $_GET['user'];
$mPwd				= $_GET['pword'];
$mHost              = $_GET['host'];
$mDbName			= $_GET['dbase'];
$mTableName 		= $_GET['table'];
$mFilter			= $_GET['filter'];
$mLimit				= $_GET['limit'];
$mOffset			= $_GET['offset'];
$mReturnFields		= $_GET['returnfields'];
$mOutput			= $_GET['output'];

$mFilter = str_replace("|AND|","&",$mFilter);
$mFilter = str_replace("/dbase/",$mDbName,$mFilter);

require_once dirname(__FILE__) . '/../../../phpexcel/Classes/PHPExcel/IOFactory.php';
// require(dirname(__FILE__) . '/../../vimsconfig/phpConfig.php');

buildExcel($mHost, $mUser, $mPwd, $mDbName, $mTableName, $mFilter, $mLimit, $mOffset, $mOutput);

function buildExcel($mHost, $mUser, $mPwd, $mDbName, $mTableName, $mFilter="", $mLimit="", $mOffset=0, $mOutput="")
{
// Create new PHPExcel object
	$objPHPExcel = new PHPExcel();
// Set document properties
	$objPHPExcel->getProperties()->setCreator("vims")
		->setLastModifiedBy("vims")
		->setTitle($mTableName)
		->setSubject("PHPExcel")
		->setDescription("PHPExcel")
		->setKeywords("office PHPExcel php")
		->setCategory("PHPExcel");
		
//	$objWorkSheet = $objPHPExcel->createSheet();
//	$objPHPExcel->addSheet($objWorkSheet);
		
// Add some data
//	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "Table");
//	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 2, $mTableName);

// get header
	$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or	
		exit("mySql_columns - Error Connecting To Database ".$mHost.":".$mDbName.":".$mUser.":".$mPwd."-".mysqli_error($mConnection));
	$mDataQuery = "SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='".$mDbName."' AND `TABLE_NAME`='".$mTableName."' ORDER BY ORDINAL_POSITION";
	$mData = mysqli_query($mConnection,$mDataQuery);
	$i = 0;
// Header
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i, 1, $mRow["COLUMN_NAME"]); 
		$i = $i+1;
	}
	mysqli_close();
	$r = 2;
	$mLimit = 5000;
	$mOffset = 0;
// Data
	$mFilter = str_replace(";",",",$mFilter);
// If Input table doesn't start with SELECT assume it's just a table name
	if (strtolower(substr($mTableName,0,6)) != "select")
		$mTableName = "SELECT * FROM ".$mTableName;
// Check for Filter / selection
//	if (strtolower(substr($mFilter,0,5)) != "where")
//		$mFilter = "WHERE ".$mFilter;
	$mDataQuery=$mTableName." ".$mFilter;
	$s = strpos(strtolower($mDataQuery),"limit");

	for($i=0;$i<5;$i++)
	{
		$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or	
			exit("mySql_columns - Error Connecting To Database ".$mHost.":".$mDbName.":".$mUser.":".$mPwd."-".mysqli_error($mConnection));
		$mDataQuery=$mTableName." ".$mFilter;
		$mDataQuery .= " LIMIT ".$mLimit;
		$mDataQuery .= " OFFSET ".$mOffset;
// mysql query construct
		$mData = mysqli_query($mConnection,$mDataQuery);
	
		if (mysqli_num_rows($mData)==0)
			break;
		while($mRow = mysqli_fetch_assoc($mData)) 
		{
			$c = 0;
			foreach($mRow as $mCol)
			{
				$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($c, $r, $mCol);
				$c++;
			}
			$r++;
		}
		mysqli_close();
		$mOffset = $mOffset+$mLimit;
		$objWorkSheet = $objPHPExcel->createSheet();
		$objPHPExcel->setActiveSheetIndex($i+1);
		$r=2;
	}
		
// Set page orientation and size
	$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
	$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

// Save Excel 2007 file

// $objWriter->save($mXls);
	if ($mOutput == "D")
	{
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
		$mXls = "C:\\web\\veldb\\vimsreports\\".$mTableName.$mCurrentUser.".xlsx";
		if ($mCurrentUser == "")
			$mCurrentUser="xxx";
		$objWriter->save("C:\\web\\veldb\\vimsreports\\".$mTableName.$mCurrentUser.".xlsx");
	}
	else
	{
	// Redirect output to a client’s web browser (Excel2007)
		header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		header('Content-Disposition: attachment;filename"'.$mTableName.'.xlsx"');
		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, 'Excel2007');
		$objWriter->save('php://output');
	}
	mysqli_close($mConnection);
}

?>