TransWar File Browser

Path: C:\

<?php
date_default_timezone_set('America/Sao_Paulo');
$dateadded = date("d-m-Y"); 
$dia=date('d/m/Y', strtotime($dateadded. ' - 6 days'));
$ano=substr($dia, 6);
$mes=substr($dia, 3,-5);
$dia=$ano.$mes.substr($dia, 0,-8);
//$grupo=$_POST['grupo'];$ds_grupo=$_POST['dsgrupo'];
$grupo=4;$ds_grupo='Brainfarma';
$fptemp = fopen('validacao_temp.csv', 'w+');
$cabecalho="VALIDAÇÃO DE NOTAS FISCAIS DO GRUPO ".$ds_grupo;
fwrite($fptemp,$cabecalho.PHP_EOL);  
fwrite($fptemp,'DATA RECEBIM./ COLETA,CTe,TASK,DATA EMISSÃO NF,CFOP,FORNECEDOR,Nº DO PEDIDO,Nº DA N.F.,VEÍCULO,Nº PALETES,Nº VOLUMES,TRANS.,PLACA,CHAVE NFe,AR,OBSERVAÇÕES'.PHP_EOL);  
require_once("conexao_srvtms.php");
$result = sqlsrv_query( $conn, "SELECT  nr_NotaFiscal as nrNF
            ,Convert(varchar(10), dt_Coleta,103) as dtColeta
            ,Convert(varchar(10), dt_Emissao,103) as dtEmissao    
            ,Convert(varchar(44), ds_ChaveNFe) as chave
            ,nr_Conhecimento as CTe
            ,ds_Modelo as CFOP
            ,ds_Remetente as fornec
            ,cd_ServicoNotaFiscal as pedido
            ,qt_Volume as volume
            ,qt_Paletes as palete
            ,cm_Coleta as col
            ,nr_AR as nrAR
            ,ds_Status as obs
            ,ds_Situacao as bip FROM dbo.tbdValidacao
      WHERE  id_GrupoCliente=".$grupo."  and dt_Coleta>='2021-07-01' order by nrNF ASC", array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
$lin = 2;
while ($resultado = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
  $remete=substr($resultado['fornec'], 0, 35);$remete=str_replace(',', ' ',$remete );
  $obs=substr($resultado['obs'], 0, 35);$obs=str_replace(',', ' ',$obs );
        $lin++;$linha=$resultado['dtColeta'].",".$resultado['CTe'].",".$resultado['col'].",".$resultado['dtEmissao'].",".$resultado['CFOP'].",".$remete.",".$resultado['pedido'].",".$resultado['nrNF'].",,".$resultado['palete'].",".$resultado['volume'].",,,".$resultado['chave'].",".$resultado['nrAR'].",".$obs.",".$resultado['bip'].PHP_EOL;
fwrite($fptemp, $linha);}
fclose($fptemp); 
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('"');
$reader->setSheetIndex(0);
/* Load a CSV file and save as a XLS */
$spreadsheet = $reader->load("validacao_temp.csv");
$writer = new Xlsx($spreadsheet);
$spreadsheet->getActiveSheet()->setTitle('VALIDAÇÃO');
$spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(46);
$spreadsheet->getActiveSheet()->mergeCells('A1:P1');
$styleArray = [
    'borders' => [
        'inside' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['argb' => '000000'],
        ],
    ],
];
$spreadsheet->getActiveSheet()->getStyle('A1:P2')->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle('A1:P2')->getAlignment()->setWrapText(true);
$spreadsheet->getActiveSheet()->getStyle('a1:P2')->getFont()->setSize(11);
$spreadsheet->getActiveSheet()->getStyle('a1:P2')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('A1:P2')
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a1:P2')
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a1:P2')
    ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
    echo $lin;
for ($c=3; $c <= $lin; $c++) 
  { $spreadsheet->getActiveSheet()->getStyleByColumnAndRow(15,$c)->getFont()->getColor()
      ->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
    if ($c % 2 == 0) { $bg="d8d8d8";} else {$bg="ffffff";}
    $range='A'.$c.':P'.$c;
    $spreadsheet->getActiveSheet()->getStyle($range)->getFill()
        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
        ->getStartColor()->setARGB($bg);
    $bip=$spreadsheet->getActiveSheet()->getCellByColumnAndRow(17,$c)->getValue();
    $data=$spreadsheet->getActiveSheet()->getCellByColumnAndRow(1,$c)->getValue();
    $ano=substr($data, 6);
    $mes=substr($data, 3,-5);
    $data=$ano.$mes.substr($data, 0,-8);
    if ($dia >= $data) 
      { $spreadsheet->getActiveSheet()->getStyleByColumnAndRow(1,$c)->getFill()
          ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
          ->getStartColor()->setARGB('F3F781');
      }
    if ($bip == "N") 
      { $range='D'.$c.':N'.$c;
        $spreadsheet->getActiveSheet()->getStyle($range)->getFill()
          ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
          ->getStartColor()->setARGB('82FA58');
      } 
  }
$spreadsheet->getActiveSheet()->getStyle('A1:P2')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('5882FA');
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(11);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(9);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(11);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(7);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(25);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(9);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(45);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(25);
$spreadsheet->getActiveSheet()->getColumnDimension('Q')->setVisible(false);
$arq="validacao_".$ds_grupo.".xlsx";
$writer->save($arq);
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
unlink('validacao_temp.csv'); 
echo "<script language='JavaScript'>alert('Planilha EXCEL gerada com Sucesso!');location.href='listanota.php?grupo=".$grupo."&dsgrupo=".$ds_grupo."';</script>";
?>