<?php

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
//date_default_timezone_set('Europe/London');
date_default_timezone_set('America/Sao_Paulo');

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


/**
 * PHPExcel
 *
 * Copyright (c) 2006 - 2015 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 * @version    ##VERSION##, ##DATE##
 */

/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . dirname(__FILE__) . '/../Classes/');

/** PHPExcel_IOFactory */
include '../PHPExcel/Classes/PHPExcel/IOFactory.php';

$inputFileType = 'Excel2007';
// $inputFileNames = 'modelos/32readwrite*[0-9].xlsx';
$inputFileNames = 'modelos/erpc_local_estoque_report.xlsx';

if ((isset($argc)) && ($argc > 1)) {
	$inputFileNames = array();
	for($i = 1; $i < $argc; ++$i) {
		$inputFileNames[] = dirname(__FILE__) . '/modelos/' . $argv[$i];
	}
} else {
	$inputFileNames = glob($inputFileNames);
}
foreach($inputFileNames as $inputFileName) {
	$inputFileNameShort = basename($inputFileName);

	if (!file_exists($inputFileName)) {
		// echo date('H:i:s') , " File " , $inputFileNameShort , ' does not exist' , EOL;
		continue;
	}

	// echo date('H:i:s') , " Load Test from $inputFileType file " , $inputFileNameShort , EOL;

	$objReader = PHPExcel_IOFactory::createReader($inputFileType);
	$objReader->setIncludeCharts(TRUE);
	$objPHPExcel = $objReader->load($inputFileName);

	// echo date('H:i:s') , " Iterate worksheets looking at the charts" , EOL;
	foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
		$sheetName = $worksheet->getTitle();
		// echo 'Worksheet: ' , $sheetName , EOL;

		$chartNames = $worksheet->getChartNames();
		if(empty($chartNames)) {
			// echo '    There are no charts in this worksheet' , EOL;
		} else {
			natsort($chartNames);
			foreach($chartNames as $i => $chartName) {
				$chart = $worksheet->getChartByName($chartName);
				if (!is_null($chart->getTitle())) {
					$caption = '"' . implode(' ',$chart->getTitle()->getCaption()) . '"';
				} else {
					$caption = 'Untitled';
				}
				// echo '    ' , $chartName , ' - ' , $caption , EOL;
				// echo str_repeat(' ',strlen($chartName)+3);
				$groupCount = $chart->getPlotArea()->getPlotGroupCount();
				if ($groupCount == 1) {
					$chartType = $chart->getPlotArea()->getPlotGroupByIndex(0)->getPlotType();
					// echo '    ' , $chartType , EOL;
				} else {
					$chartTypes = array();
					for($i = 0; $i < $groupCount; ++$i) {
						$chartTypes[] = $chart->getPlotArea()->getPlotGroupByIndex($i)->getPlotType();
					}
					$chartTypes = array_unique($chartTypes);
					if (count($chartTypes) == 1) {
						$chartType = 'Multiple Plot ' . array_pop($chartTypes);
						// echo '    ' , $chartType , EOL;
					} elseif (count($chartTypes) == 0) {
						// echo '    *** Type not yet implemented' , EOL;
					} else {
						// echo '    Combination Chart' , EOL;
					}
				}
			}
		}
	}


	include_once 'connect.php';

	// Faz a consulta ao BD com o POST dos dados informados
	$consulta   = "select ds_local, count(*) as nr_registros from erpc_local_estoque where st_ativo=1 ";
	$sql_having = "";

	//Efetuando busca avançada - se passado os parametros
	if(isset($_POST['visualizar'])){
	  $id  = $_POST['id'];
	  $ds_local	  = trim($_POST['ds_local']);

	  $sql_having .= (!empty($id)         ? " and id like '%$id%'"                  : '');
	  $sql_having .= (!empty($ds_local) ? " and ds_local like '%$ds_local%'"  : '');
	}

  // Monta o SQL por agrupamento para a Aba Dados
	$sql_group = "group by ds_local";
	$consulta .= $sql_group;
	if(!$sql_having==""){
		$consulta .= $sql_having;
	}

	// Definimos o estilo da fonte
	$objPHPExcel->setActiveSheetIndex(0);
	$objPHPExcel->getActiveSheet()->getStyle('A1:B1')->getFont()->setBold(true);

	// Criamos as colunas
	$objPHPExcel->setActiveSheetIndex(0)
	            ->setCellValue('A1', 'local de estoque' )
	            ->setCellValue('B1', 'Registros' );

	// Podemos configurar diferentes larguras paras as colunas como padrão
	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(90);
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);

	$nr_linha = 2;

	$resultado = mysqli_query($conexao, $consulta) or die ($erro = mysqli_error($conexao));
	if (mysqli_num_rows($resultado)>0) {
	  while ($registro = mysqli_fetch_assoc($resultado)){

	    // $id = $registro['id'];
	    $ds_local  = $registro['ds_local'];
			$nr_registros= $registro['nr_registros'];

	    // Também podemos escolher a posição exata aonde o dado será inserido (coluna, linha, dado);
	    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $nr_linha, $ds_local);
	    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $nr_linha, $nr_registros);

	    $nr_linha += 1;

	    /*
	    echo "<tr>";
	    echo "<td>$id</td>";
	    echo "<td>$ds_local</td>";
	    echo "</tr>";*/
	  }
	}

	/*// Exemplo inserindo uma segunda linha, note a diferença no segundo parâmetro
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 3, "Beltrano");
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 3, " da Silva Sauro");
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, 3, "beltrando@exemplo.com.br");
	*/

	$objPHPExcel->setActiveSheetIndex(1);

	// Podemos renomear o nome das planilha atual, lembrando que um único arquivo pode ter várias planilhas
	// $objPHPExcel->getActiveSheet()->setTitle('Credenciamento para o Evento');

	// define o nome do arquivo
	$outputFileName = substr($inputFileName, 0, -5) . '_' . date('Ymd') . '_' . date('His') . '.xlsx';

  // $outputFileName = basename($inputFileName);

	// echo date('H:i:s') , " Write Tests to Excel2007 file " , EOL;
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter->setIncludeCharts(TRUE);
	$objWriter->save($outputFileName);
	// $objWriter->save('php://output'); Não utilizar dá erro

	// echo date('H:i:s') , " File written to " , $outputFileName , EOL;

	$objPHPExcel->disconnectWorksheets();
	unset($objPHPExcel);

	/*
	echo "<span font-face=Arial>";
  echo "<br>Arquivo gerado com sucesso:$outputFileName! <a href='$outputFileName'/>Clique aqui para baixar</a>";
	echo "<br>Voltar a lista! <a href='javascript:window.history.back();'/>Voltar</a>";
	echo "</span>";
	*/
	// echo "Relatório gerado com sucesso! Baixar : <a href='$outputFileName'><i class='glyphicon glyphicon-download' title='baixar'><span class='sr-only'>Baixe o Arquivo</span></i></a>";
	echo "Relatório gerado com sucesso! Baixar : <a href='$outputFileName'><i class='fa fa-download text-success'  title='Status'><span class='sr-only'>Baixe o Arquivo</span></i></a>";
}

// Echo memory peak usage
// echo date('H:i:s') , ' Peak memory usage: ' , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;

// Echo done
// echo date('H:i:s') , " Done writing files" , EOL;
// echo 'Files have been created in ' , getcwd() , EOL;
