TransWar File Browser

Path: C:\

<?php
date_default_timezone_set('America/Sao_Paulo');
$dateadded = date("Ymd"); 
ini_set('max_execution_time', 300); //300 seconds = 5 minutes
ini_set ('memory_limit','4095M') ;
$grupo=$_POST['grupo'];$ds_grupo=$_POST['dsgrupo'];
require_once("conexao_srvtms.php");
if ( isset($_POST['dia']) ) { $dia=$_POST['dia'];
$ano=substr($dia, 6);
$mes=substr($dia, 3,-5);
$dia=$ano.$mes.substr($dia, 0,-8);
} else { $dia=$dateadded;}
$tsql= "SELECT	 Convert(varchar(44), b.ds_ChaveNFe) as chave
		,Convert(varchar(10), a.dt_Coleta,103) as dtColeta
		,f.ds_Cidade as cidade
		,b.nr_NotaFiscalInteiro as nf
		,Convert(varchar(10), b.dt_Emissao,103) as emissao
		,a.cm_Coleta as coleta
		,'/'+a.nr_Conhecimento as CTe
		,h.cd_Placa as placa	  
		,b.kg_Mercadoria as pesoTotal
		,b.qt_Volume as qtd
		,b.vl_NotaFiscal as vlrNF
		,Convert(varchar(10), j.dt_Saida,103) AS saida
		,a.vl_Frete as vlrFrete
		,a.vl_Coleta as vlrColeta
		,a.vl_Peso as pesoFrete
		,g.vPrest_Comp_GRIS AS gris
		,a.vl_Pedagio as pedagio
	FROM dbo.tbdMovimento AS a
		left JOIN dbo.tbdMovimentoNotaFiscal AS b ON  a.id_Movimento = b.id_Movimento
		left JOIN dbo.tbdManifesto AS j ON  j.id_Manifesto = a.id_ManifestoEntrega
		left JOIN dbo.tbdCTeValores AS g ON  g.id_Movimento = a.id_Movimento
		left JOIN dbo.tbdVeiculo AS h ON  h.id_Veiculo = j.id_Veiculo
		left JOIN dbo.tbdCidade AS f ON  f.id_Cidade = a.id_CidadeOrigem
		left JOIN dbo.tbdPessoa AS c on a.id_ClienteFaturamento = c.id_Pessoa
		left JOIN dbo.tbdConfiguracaoCliente AS d on d.id_Pessoa = c.id_Pessoa
 	WHERE d.id_GrupoCliente='$grupo' and Convert(varchar(10), a.dt_Coleta,112) >= '".$dia."' order by nf ASC";
$consulta = sqlsrv_query($conn, $tsql, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
if (!$consulta){echo "ATENÇÃO!! FALHA NA CONSULTA!";}
$numReg1 = sqlsrv_num_rows($consulta);
$arqcsv="consolidado_".$ds_grupo.".csv";
$arqxlsx="consolidado_".$ds_grupo.".xlsx";
$file_csv = fopen($arqcsv, 'w+');
$l=1;
while ($row = sqlsrv_fetch_array($consulta, SQLSRV_FETCH_ASSOC)) 
{	$transp="TRANS WAR";
	$chave=$row['chave'];
	$dtColeta=$row['dtColeta'];
	$cidade=trim($row['cidade']);
	$nf=trim($row['nf']);
	$emissao=$row['emissao'];
	$NR=$row['coleta']; $b= stripos($NR, "TASK"); 
	if ($b===false){$NR= '';}else{$NR= substr($NR, $b, 13);}
	$coleta=$NR;
	$CTe=trim($row['CTe']);
	$placa=trim($row['placa']);
	$pesoTotal=$row['pesoTotal'];
	$qtd=$row['qtd'];
	$m3="";
	$vlrNF=$row['vlrNF'];
	$embarque=$row['saida'];
	$vlrFrete=$row['vlrFrete'];
	$vlrColeta=$row['vlrColeta'];
	$pesoFrete=$row['pesoFrete'];
	$gris=$row['gris'];
	$pedagio=$row['pedagio'];
	if (isset($row['saida'])){$status="ENTREGUE";}else{$status="";}
	if ($coleta==""){ $coleta= "RECEBIDA";}
	$linha[$l] = array($transp,$chave,$dtColeta,$cidade,$nf,$emissao,$coleta,$CTe,$placa,$pesoTotal,$qtd,$m3,$vlrNF,$embarque,$vlrFrete,$vlrColeta,$pesoFrete,$gris,$pedagio,$status);	++$l;
}
fwrite($file_csv,''.PHP_EOL);   
fwrite($file_csv,'Transportadora;Chave de Acesso NFe;Data;Cidade da Coleta;NF;Data de Emissão NF;Nr. da Coleta;CTe;Placa;Peso Total;QTD;M3;Vlr Total Mercadoria;Embarque;Total Frete;Vlr Coleta;Frete Peso;GRIS;Pedágio;Status Encerrado'.PHP_EOL);   
for ( $i = 1; $i < $l; ++$i )
	{$linha2 = $linha[$i][0]. ";" .$linha[$i][1] . ";" . $linha[$i][2].";".$linha[$i][3].";".$linha[$i][4].";".$linha[$i][5].";".$linha[$i][6].";".$linha[$i][7].";".$linha[$i][8].";".$linha[$i][9].";".$linha[$i][10].";".$linha[$i][11].";".$linha[$i][12].";".$linha[$i][13].";".$linha[$i][14].";".$linha[$i][15].";".$linha[$i][16].";".$linha[$i][17].";".$linha[$i][18].";".$linha[$i][19] . PHP_EOL;
		fwrite($file_csv, $linha2);
	}
fclose($file_csv);
$l=$l+2;
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
/* Set CSV parsing options */
$reader->setDelimiter(';');
$reader->setEnclosure('"');
$locale = 'pt_br';
$validLocale = \PhpOffice\PhpSpreadsheet\Settings::setLocale($locale);
if (!$validLocale) {
    echo 'Unable to set locale to ' . $locale . " - reverting to en_us" . PHP_EOL;
}
$logo1="images/".$ds_grupo."1.png";
$logo2="images/".$ds_grupo."2.png";
$logo3="images/".$ds_grupo."3.png";
$reader->setSheetIndex(0);
/* Load a CSV file and save as a XLS */
$spreadsheet = $reader->load($arqcsv);
$writer = new Xlsx($spreadsheet);
$spreadsheet->getActiveSheet()->setTitle('CONSOLIDADO');
$drawing1 = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing1->setPath($logo3);
$drawing1->setHeight(100);
$drawing1->setWorksheet($spreadsheet->getActiveSheet());
$drawing1->setCoordinates('A1');
$drawing2 = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing2->setPath($logo2);
$drawing2->setHeight(100);
$drawing2->setWorksheet($spreadsheet->getActiveSheet());
$drawing2->setCoordinates('G1');
$drawing3 = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing3->setPath($logo1);
$drawing3->setHeight(100);
$drawing3->setWorksheet($spreadsheet->getActiveSheet());
$drawing3->setCoordinates('O1');
$spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(80);
$spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
$styleArray = [
    'borders' => [
        'inside' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['argb' => '000000'],
        ],
    ],
];
$format = '* #,##0.00';
$format1 = '* #,###';
$spreadsheet->getActiveSheet()->getStyle('A1:T'.$l)->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle('A2:T2')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('F4FA58');
$spreadsheet->getActiveSheet()->getStyle('A2:T2')->getAlignment()->setWrapText(true);
$spreadsheet->getActiveSheet()->getStyle('a2:T2')->getFont()->setSize(11);
$spreadsheet->getActiveSheet()->getStyle('a2:T2')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('A2:T2')
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a2:T2')
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a3:c'.$l)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('e3:i'.$l)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('j3:j'.$l)->getNumberFormat()->setFormatCode($format);
$spreadsheet->getActiveSheet()->getStyle('k3:l'.$l)->getNumberFormat()->setFormatCode($format1);
$spreadsheet->getActiveSheet()->getStyle('m3:m'.$l)->getNumberFormat()->setFormatCode($format);
$spreadsheet->getActiveSheet()->getStyle('o3:q'.$l)->getNumberFormat()->setFormatCode($format);
$spreadsheet->getActiveSheet()->getStyle('s3:s'.$l)->getNumberFormat()->setFormatCode($format);
for ($c=3; $c <= $l; $c++) 
  { if ($c % 2 == 0) { $bg="d8d8d8";} else {$bg="ffffff";}
    $range='A'.$c.':T'.$c;
    $spreadsheet->getActiveSheet()->getStyle($range)->getFill()
        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
        ->getStartColor()->setARGB($bg);
  }
$spreadsheet->getActiveSheet()->mergeCells('A1:T1');
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(45);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(17);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('S')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('T')->setWidth(30);
$writer->save($arqxlsx);
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
unlink($arqcsv); 
echo "<script language='JavaScript'>alert('Planilha EXCEL gerada com Sucesso! \\nTecle OK para BAIXAR.');location.href='consolidado.php?arquivo=".$arqxlsx."';</script>";
?>