TransWar File Browser

Path: C:\

<?php
date_default_timezone_set('America/Sao_Paulo');
$dateadded = date("d/m/Y"); 
ini_set('max_execution_time', 259200); //300 seconds = 5 minutes
ini_set ( 'memory_limit' , '1536M' ) ;
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;
$arqxlsx="Excel/Fretes Transwar ".$final.".xlsx";
$fptemp = fopen('fatura_temp.csv', 'w+');
$cabecalho="TRANS WAR TRANSPORTES LTDA.   -   Relação de faturas do Grupo FIRMENICH de  ".$inicial." até  ".$final;
fwrite($fptemp,$cabecalho.PHP_EOL); 
fwrite($fptemp,'Numero da Fatura,Data de Emissão,Data de Entrega,CT-e,Po number,SAP ID,País de Origem,Cidade de Origem,CEP (Postcode) Origem,Origem Nome da Empresa,CNPJ Origem,País de Destino,Cidade de Destino,CEP (Postcode) Destino,Destino Nome da Empresa,CNPJ Destino,Placa do Veículo,Mode: Truck/ Aéreo/ Marítimo,"Peso Bruto (KG)",Peso Cobrado (KG),Volume (Pallets ou Caixas),Valor da Nota Fiscal,Frete Peso,Frete Valor,Combustivel, Despacho,GRIS,Pedágio,(Dificuldade de Entrega),(ICMS),TDE,TRT,SECAT,Manuseio (Handling),Ajudante,Tempo de Espera,Outros (favor especificar),Observaçoes / informação adicional,Moeda da fatura,Total do CT-e'.PHP_EOL);
require_once("conexao_srvtms.php");
$sql = sqlsrv_query( $conn, "SELECT a.cd_NFFatura as fatura
      ,Convert(varchar(10), a.dt_ImpressaoConhecimento, 103) as emissao
      ,Convert(varchar(10), a.dt_Recepcao, 103) as entrega
      ,rtrim(a.nr_Conhecimento)+'-'+rtrim(a.cd_SerieCTRC) as CTe
      ,f.ds_Cidade as cidori
      ,d.ds_Pessoa as remet
      ,d.cd_CEP as cepori
      ,d.cd_CGCCPF as cnpjori
      ,g.ds_Cidade as ciddes
      ,e.ds_Pessoa as dest
      ,e.cd_CEP as cepdes
      ,e.cd_CGCCPF as cnpjdes
      ,b.kg_Mercadoria as bruto
      ,b.kg_PesoTaxado as taxado
      ,b.qt_Volume as volume
      ,a.vl_NotaFiscal as vlnf
      ,a.vl_Peso as vlpeso
      ,a.vl_Frete as vlfrete
      ,j.vPrest_Comp_GRIS as gris
      ,a.vl_Redespacho
      ,a.vl_Pedagio as pedagio
      ,a.vl_ICMS as icms
      ,a.vl_Acrescimo as outros
      ,a.vl_TaxasDiversas as outros2
      ,a.vl_SETCAT as setcat
      ,a.vl_Despacho as despacho
      ,a.ds_Embalagem 
      ,i.cd_Placa as placa
  FROM dbo.tbdMovimento as a
      LEFT JOIN dbo.tbdDimensao as b on  a.id_Movimento = b.id_Movimento
      LEFT JOIN dbo.tbdPessoa as d on a.id_Remetente = d.id_Pessoa
      LEFT JOIN dbo.tbdPessoa as e on a.id_Destinatario = e.id_Pessoa
      LEFT JOIN dbo.tbdCidade as f on a.id_CidadeOrigem = f.id_Cidade
      LEFT JOIN dbo.tbdCidade as g on a.id_CidadeDestinatario = g.id_Cidade
      LEFT JOIN dbo.tbdVeiculo as i on a.id_Veiculo = i.id_Veiculo
      LEFT JOIN dbo.tbdMovimentoDespesaManifesto as o on  a.id_Movimento = o.id_Movimento
      LEFT JOIN dbo.tbdCTeValores as j on  o.id_LoteCTe = j.id_LoteCTe and o.id_Movimento = j.id_Movimento
    WHERE a.id_ClienteFaturamento='29117' and (Convert(varchar(8), a.dt_ImpressaoConhecimento,112) BETWEEN '$data1' and '$data2') order by a.nr_Conhecimento", array(), array( "Scrollable" => SQLSRV_CURSOR_FORWARD ));
if (!$sql){echo "ATENÇÃOs!! FALHA NA CONSULTA!";}
$lin=2;$manif1="999999";
while ($res = sqlsrv_fetch_array($sql, SQLSRV_FETCH_ASSOC)) {
if ($res['CTe']<>$manif1) { $manif1=$res['CTe'];//$diversos=$res['outros']+$res['outros2'];
  $lin++;$linha=trim($res['fatura']).",".$res['emissao'].",".$res['entrega'].",".$res['CTe'].",,,BR,".$res['cidori'].",".$res['cepori'].",".$res['remet'].",".$res['cnpjori'].",BR,".$res['ciddes'].",".$res['cepdes'].",".$res['dest'].",".$res['cnpjdes'].",".trim($res['placa']).",Truck,".$res['bruto'].",".$res['taxado'].",".$res['volume'].",".$res['vlnf'].",".$res['vlpeso'].",".$res['vlfrete'].",0,".$res['despacho'].",".$res['gris'].",".$res['pedagio'].",,".$res['icms'].",0,0,0,0,0,0,0,,BRL,".$res['vlfrete'].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("fatura_temp.csv");
$writer = new Xlsx($spreadsheet);
$spreadsheet->getActiveSheet()->setTitle($final);
$spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
$styleArray = [
    'borders' => [
        'inside' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['argb' => '000000'],
        ],
    ],
];
$format = '* #,##0.00';
$format1 = '* #,##0.00';
$format2 = '* ###';
$spreadsheet->getActiveSheet()->getStyle('A1:AN2')->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle('A1:AN2')->getAlignment()->setWrapText(true);
$spreadsheet->getActiveSheet()->getStyle('a1:an1')->getFont()->setSize(14);
$spreadsheet->getActiveSheet()->getStyle('a2:AN2')->getFont()->setSize(12);
$spreadsheet->getActiveSheet()->getStyle('a1:AN2')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('A1:AN2')
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A3:g'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('i3:i'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('k3:l'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('n3:n'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('p3:r'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('am3:am'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a2:AN2')
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a1:AN2')
    ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$spreadsheet->getActiveSheet()->getStyle('Ak3:Ak'.$lin)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_00);
$spreadsheet->getActiveSheet()->getStyle('k3:k'.$lin)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);
$spreadsheet->getActiveSheet()->getStyle('p3:p'.$lin)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);
$spreadsheet->getActiveSheet()->getStyle('i3:i'.$lin)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);
$spreadsheet->getActiveSheet()->getStyle('n3:n'.$lin)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);
$spreadsheet->getActiveSheet()->getStyle('s3:t'.$lin)->getNumberFormat()->setFormatCode($format);
$spreadsheet->getActiveSheet()->getStyle('v3:ab'.$lin)->getNumberFormat()->setFormatCode($format);
$spreadsheet->getActiveSheet()->getStyle('ad3:ak'.$lin)->getNumberFormat()->setFormatCode($format);
$spreadsheet->getActiveSheet()->getStyle('an3:an'.$lin)->getNumberFormat()->setFormatCode($format);
for ($c=3; $c <= $lin; $c++) 
  { if ($c % 2 == 0) { $bg="d8d8d8";} else {$bg="ffffff";}
    $range='A'.$c.':AN'.$c;
    $spreadsheet->getActiveSheet()->setCellValue('AE'.$c,'=X'.$c.'-AA'.$c.'-W'.$c.'-AD'.$c.'-Z'.$c); 
    $TDE = $spreadsheet->getActiveSheet()->getCell('AE'.$c)->getCalculatedValue(); 
    if ($TDE<'-0.01'){$spreadsheet->getActiveSheet()->getStyleByColumnAndRow(31,$c)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED); }  
    $spreadsheet->getActiveSheet()->getStyle($range)->getFill()
        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
        ->getStartColor()->setARGB($bg);
  }
$spreadsheet->getActiveSheet()->getStyle('A1:AN2')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('5882FA');
$spreadsheet->getActiveSheet()->mergeCells('A1:AN1');
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('S')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('T')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('U')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('V')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('W')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('X')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('Y')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('Z')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('AA')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('AB')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('AC')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('AD')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('AE')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('AF')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('AG')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('AH')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('AI')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('AJ')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('AK')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('AL')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('AM')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('AN')->setWidth(15);

$writer->save($arqxlsx);
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
unlink('fatura_temp.csv'); 
echo "<script language='JavaScript'>location.href='firmenich2.php?arquivo=".$arqxlsx."';</script>";
?>