TransWar File Browser

Path: C:\

<?php
date_default_timezone_set('America/Sao_Paulo');
$hoje = date("Ymd"); $dateadded = date("d/m/Y"); 
$DT=date('d-m-Y_G\hi\m');
ini_set('max_execution_time', 600); //300 seconds = 5 minutes
ini_set ( 'memory_limit' , '4095M' ) ;
if (isset($_POST['grupo'])) {$grupo=$_POST['grupo'];$ds_grupo=$_POST['dsgrupo'];} else {$grupo="999";$ds_grupo="TODOS";}
if (isset($_POST['grupofor'])) {$grupofor=$_POST['grupofor'];$ds_grupofor=$_POST['dsgru'];} else {$grupofor="999";$ds_grupofor="TODOS";}
if (isset($_POST['statu'])) {$statu=$_POST['statu'];} else {$statu="999";}
if (isset($_POST['perfo'])) {$perfo=$_POST['perfo'];} else {$perfo="999";}
if (isset($_POST['fornecedor'])) {$fornecedor=$_POST['fornecedor'];} else {$fornecedor="999";}
if (isset($_POST['destinatario'])) {$destinatario=$_POST['destinatario'];} else {$destinatario="999";}
if (isset($_POST['local'])) {$local=$_POST['local'];} else {$local="999";}
if (isset($_POST['natureza'])) {$natureza=$_POST['natureza'];} else {$natureza="999";}
require_once("conexao_srvtms.php");
if (isset($_POST['di'])) { $inicial=$_POST['di'];} else { $inicial=$dateadded;}
if (isset($_POST['df'])) { $final=$_POST['df'];} else { $final=$dateadded;}
$ano1=substr($inicial, 6);
$mes1=substr($inicial, 3,-5);
$dia1=substr($inicial, 0,-8);
$data1=$ano1."-".$mes1."-".$dia1;
$ano2=substr($final, 6);
$mes2=substr($final, 3,-5);
$dia2=substr($final, 0,-8);
$data2=$ano2."-".$mes2."-".$dia2;
$tsql= "SELECT A.cm_PedidoColeta as NR
    ,A.id_PedidoColeta as Coleta_TW
    ,Convert(varchar(30), B.ds_Pessoa) as Fornecedor
    ,Convert(varchar(30), C.ds_Pessoa) as Destinatario
    ,Convert(varchar(27), D.ds_Cidade) as Local
    ,Convert(varchar(30), I.ds_NaturezaMercadoria) as Natureza
    ,H.id_Embalagem as Paletes  
    ,A.qt_Volume as Volume
    ,A.kg_Mercadoria as Peso
    ,Convert(varchar(10), A.dt_PedidoColeta, 103) as Data_Solicitada
    ,A.hr_Limite as Hora
    ,Convert(varchar(10), A.dt_Cadastro, 103) as Data_Cadastro
    ,Convert(varchar(10), A.dt_Limite, 103) as Data_Pretendida
    ,Convert(varchar(10), A.dt_Limite, 112) as Pretendida
    ,A.id_Manifesto as Manifesto
    ,Convert(varchar(30), O.ds_Ocorrencia) as Historico
    ,Convert(varchar(10), A.dt_Coleta, 103) as Data_Realizada
    ,Convert(varchar(10), A.dt_Coleta, 112) as Realizada
    ,Convert(varchar(27), F.ds_Pessoa) as Motorista
    ,Convert(varchar(44), X.ds_ChaveNFe) as Chave
    ,Z.ds_Cliente as empresa
    ,X.nr_NotaFiscalInteiro as nrNF
  FROM dbo.tbdPedidoColeta as A
    left JOIN dtbtransporte.dbo.tbdClienteSistema AS Z on A.id_Cliente = Z.id_Cliente
    left JOIN dtbtransporte.dbo.tbdPessoa AS B on A.id_Pessoa = B.id_Pessoa
    left JOIN dtbtransporte.dbo.tbdPessoa AS C on A.id_Remetente = C.id_Pessoa
    left JOIN dtbtransporte.dbo.tbdCidade AS D ON  D.id_Cidade = A.id_CidadeColeta
    left JOIN dtbtransporte.dbo.tbdManifesto AS G on A.id_Manifesto = G.id_Manifesto
    left JOIN dtbtransporte.dbo.tbdManifestoMovimento AS M on A.id_PedidoColeta = M.id_PedidoColeta  AND  A.id_Manifesto = M.id_Manifesto
    left JOIN dtbtransporte.dbo.tbdOcorrencia AS O on M.id_Ocorrencia = O.id_Ocorrencia
    left JOIN dtbtransporte.dbo.tbdPessoa AS F on G.id_Motorista = F.id_Pessoa
    left JOIN dtbtransporte.dbo.tbdConfiguracaoCliente AS E on E.id_Pessoa = C.id_Pessoa
    left JOIN dtbtransporte.dbo.tbdConfiguracaoCliente AS Y on Y.id_Pessoa = B.id_Pessoa
    left JOIN dtbtransporte.dbo.tbdItemPedidoColeta AS H on A.id_PedidoColeta = H.id_PedidoColeta
    left JOIN dtbtransporte.dbo.tbdNaturezaMercadoria AS I on H.id_NaturezaMercadoria = I.id_NaturezaMercadoria
    left JOIN dtbtransporte.dbo.tbdMovimentoNotaFiscal AS X on H.id_Movimento = X.id_Movimento
  WHERE (Convert(varchar(10), A.dt_PedidoColeta,120) BETWEEN '$data1' and '$data2')";
    $cabec="";
    if ($grupofor<>999){$tsql=$tsql." AND Y.id_GrupoCliente='$grupofor' ";$cabec="DO GRUPO ".$ds_grupofor."(Fornecedor)";}
    if($grupo<>999){$tsql=$tsql." AND E.id_GrupoCliente='$grupo' ";$cabec="DO GRUPO ".$ds_grupo."(Destinatário)";}
    if($fornecedor<>999){$tsql=$tsql." AND A.id_Pessoa='$fornecedor' ";}
    if($destinatario<>999){$tsql=$tsql." AND A.id_Remetente='$destinatario' ";}
    if($local<>999){$tsql=$tsql." AND A.id_CidadeColeta='$local' ";}
    if($natureza<>999){$tsql=$tsql." AND H.id_NaturezaMercadoria='$natureza' ";}
    $tsql=$tsql." ORDER by A.id_PedidoColeta 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);
if($numReg1 ==0 ) {echo "<script language='JavaScript'>alert('Sem resultados carregados. Faça uma busca ampliando o filtro.');location.href='grupo3.php';</script>";exit();}
$arqcsv="coleta".$DT.".csv";
$arqxlsx="coleta_".$DT.".xlsx";
$file_csv = fopen($arqcsv, 'w+');
$l=0;$coletaAnterior="";
while ($row = sqlsrv_fetch_array($consulta, SQLSRV_FETCH_ASSOC)) 
{	$Coleta_TW=$row['Coleta_TW'];
    $Chave=$row['Chave'];$pode="S";
    $nrNF=$row['nrNF'];
    if ($Coleta_TW==$coletaAnterior){$chavetotal=$chavetotal." \r".$Chave;$nrNFtotal=$nrNFtotal." / \r".$nrNF;}else{if ($l<>0){
        if ($statu<>999){if($statu<>$Status){$pode="N";}}
        if ($perfo<>999){if($perfo<>$Performance){$pode="N";}}
        if ($pode=="S"){$linha[$l] = array($NR,$coletaAnterior,$Fornecedor,$Destinatario,$Local,$Natureza,$Paletes,$Volume,$Peso,$Data_Solicitada,$Hora,$Data_Pretendida,$Manifesto,$Motorista,$Status,$Performance,$Historico,$Data_Realizada,$chavetotal."\"",$nrNFtotal,$Data_Cadastro,$empresa);++$l;}}else{$l=1;}
    $coletaAnterior=$Coleta_TW;
    $chavetotal="\"".$Chave;
    $nrNFtotal=$nrNF;
    $NR=trim($row['NR']);
	$b= strpos($NR, "TASK");
	$NR= substr($NR, $b+4, 7);
	$Fornecedor=trim($row['Fornecedor']);
	$Destinatario=trim($row['Destinatario']);
	$Local=trim($row['Local']);
    $Natureza=trim($row['Natureza']);
    $Paletes=$row['Paletes'];
	$Volume=$row['Volume'];
	$Peso=$row['Peso'];
    $Data_Cadastro=$row['Data_Cadastro'];
	$Data_Solicitada=$row['Data_Solicitada'];
	$Hora=$row['Hora'];
	$Data_Pretendida=$row['Data_Pretendida'];
	$Manifesto=$row['Manifesto'];
    $Motorista=trim($row['Motorista']);
	$Historico=trim($row['Historico']);
	$Data_Realizada=$row['Data_Realizada'];
	$Pretendida=$row['Pretendida'];
    $Realizada=$row['Realizada'];
	$empresa=$row['empresa'];
    if ($Paletes==9){$Paletes=$Volume;$Volume="";}else{$Paletes="";}
	if ($Data_Realizada!=""){$Status='REALIZADA';}else{if($Manifesto!=""){$Status='EM ROTA';}else{$Status='PENDENTE';}}
	if ($Realizada<>""){if ($Realizada<=$Pretendida){$Performance="NO PRAZO";}else{$Performance="FORA DO PRAZO";}}else{if ($hoje<=$Pretendida){$Performance="NO PRAZO";}else{$Performance="FORA DO PRAZO";}}
    }
}
$pode="S";
if ($statu<>"999"){if($statu<>$Status){$pode="N";}}
if ($perfo<>"999"){if($perfo<>$Performance){$pode="N";}}
if ($pode=="S"){$linha[$l] = array($NR,$coletaAnterior,$Fornecedor,$Destinatario,$Local,$Natureza,$Paletes,$Volume,$Peso,$Data_Solicitada,$Hora,$Data_Pretendida,$Manifesto,$Motorista,$Status,$Performance,$Historico,$Data_Realizada,$chavetotal."\"",$nrNFtotal,$Data_Cadastro,$empresa); ++$l;}
$cabecalho="ACOMPANHAMENTO DE COLETAS ".$cabec."  de  ".$inicial." até  ".$final;
fwrite($file_csv,$cabecalho.PHP_EOL);   
fwrite($file_csv,'TASK;Coleta TW;Fornecedor;Destinatário;Local;Natureza Mercadoria;Paletes;Volume;Peso;Solicitada;Hora;Pretendida;Manifesto;Motorista;Status;Performance;Histórico;Realizada;Chave NFe;Nº NF;Cadastro;Empresa Responsável'.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].";".$linha[$i][20].";".$linha[$i][21].  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;
}
$reader->setSheetIndex(0);
/* Load a CSV file and save as a XLS */
$spreadsheet = $reader->load($arqcsv);
$writer = new Xlsx($spreadsheet);
$spreadsheet->getActiveSheet()->setTitle('COLETAS');
$spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
$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:V'.$l)->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle('A2:V2')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('F4FA58');
$spreadsheet->getActiveSheet()->getStyle('a1:V1')->getFont()->setSize(14);
$spreadsheet->getActiveSheet()->getStyle('a2:V2')->getFont()->setSize(11);
$spreadsheet->getActiveSheet()->getStyle('a1:V2')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('A1:V'.$l)
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a1:V2')
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a1:V2')
    ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$spreadsheet->getActiveSheet()->getStyle('A1:V2')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('5882FA');
$spreadsheet->getActiveSheet()->getStyle('a3:b'.$l)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('g3:h'.$l)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('j3:m'.$l)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('o3:p'.$l)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('r3:V'.$l)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('s3:t'.$l)->getAlignment()->setWrapText(true);
$spreadsheet->getActiveSheet()->getStyle('i3:i'.$l)->getNumberFormat()->setFormatCode($format1);
for ($c=3; $c <= $l; $c++) 
  { $status=$spreadsheet->getActiveSheet()->getCellByColumnAndRow(15,$c)->getValue();
  	$performance=$spreadsheet->getActiveSheet()->getCellByColumnAndRow(16,$c)->getValue();
  	if ($status<>'REALIZADA'){$spreadsheet->getActiveSheet()->getStyleByColumnAndRow(15,$c)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);}
  	if ($performance<>'NO PRAZO'){$spreadsheet->getActiveSheet()->getStyleByColumnAndRow(16,$c)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);}
  	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:V1');
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(35);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(35);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(35);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(7);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(35);
$spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('S')->setWidth(50);
$spreadsheet->getActiveSheet()->getColumnDimension('T')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('U')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('V')->setWidth(25);
$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='grupo3.php?arquivo=".$arqxlsx."';</script>";
?>