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/Relatório Movimentação ".$final.".xlsx";
$fptemp = fopen('relatorio_temp.csv', 'w+');
$cabecalho="TRANS WAR TRANSPORTES LTDA.   -   Relatório de Movimentação do Grupo FIRMENICH de  ".$inicial." até  ".$final;
fwrite($fptemp,$cabecalho.PHP_EOL); 
fwrite($fptemp,'DT EMISSÃO,REMETENTE,CID REMET,UF REMET,DESTINATÁRIO,CID DEST,UF DEST,RAZÃO_REDESP,NUM CTe,SÉRIE,NOTA FISCAL,SHIPMENT,PESO,VAL MERCADORIA,FRETE PESO(01),VL DESPACHO,GRIS(20),TDE/OUTROS,TOTAL A RECEBER,NRO FAT,OBS DOCUMENTO,CHAVE CTE,VALOR ICMS,CNPJ CTRC,ALIQUOTA,CFOP,DATA DE ENTREGA'.PHP_EOL);
require_once("conexao_srvtms.php");
$sql = sqlsrv_query( $conn, "SELECT rtrim(a.cd_NFFatura) as fatura
      ,Convert(varchar(10), a.dt_ImpressaoConhecimento, 103) as emissao
      ,'/'+rtrim(a.nr_Conhecimento) as CTe
      ,rtrim(a.cd_SerieCTRC) as serie
      ,a.nr_NotaFiscal as nrNF
      ,f.ds_Cidade as cidori
      ,d.ds_Pessoa as remet
      ,m.cd_Estado as ufremet
      ,g.ds_Cidade as ciddes
      ,e.ds_Pessoa as dest
      ,n.cd_Estado as ufdest
      ,b.kg_Mercadoria as bruto
      ,a.vl_NotaFiscal as vlnf
      ,a.cm_Movimento as obs
      ,a.vl_Peso as vlpeso
      ,a.vl_Frete as vlfrete
      ,p.ds_Pessoa as redespacho
      ,j.vPrest_Comp_GRIS as gris
      ,l.cd_CGCCPF as cnpjctrc
      ,k.ds_ChaveCTe as chave
      ,rtrim(a.pc_ICMS) as aliquota
      ,a.vl_ICMS as icms
      ,a.vl_Despacho as despacho
      ,rtrim(o.cd_CFOPImpresso) as cfop
      ,rtrim(r.cd_ServicoNotaFiscal) as pedido
      ,CASE r.dt_EntregaReal 
        WHEN r.dt_EntregaReal THEN Convert(varchar(10), r.dt_EntregaReal, 103) 
        ELSE Convert(varchar(10), r.dt_Entrega, 103)  
        END                                                     as entrega
  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.tbdPessoa as l on a.id_Transportadora = l.id_Pessoa
      left JOIN dbo.tbdPessoa as p on a.id_Consignatario = p.id_Pessoa
      LEFT JOIN dbo.tbdCidade as f on a.id_CidadeOrigem = f.id_Cidade
      LEFT JOIN dbo.tbdEstado as m on f.id_Estado = m.id_Estado
      LEFT JOIN dbo.tbdCidade as g on a.id_CidadeDestinatario = g.id_Cidade
      LEFT JOIN dbo.tbdEstado as n on g.id_Estado = n.id_Estado
      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
      LEFT JOIN dbo.tbdMovimentoNotaFiscal as r on  a.id_Movimento = r.id_Movimento
      LEFT JOIN dbo.tbdLoteCTeMovimento as k on  a.id_Movimento = k.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 ))or die('Ocorreu o seguinte erro: ' . sqlsrv_errors());
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'];
  $redes=trim($res['redespacho']);
  $redes=str_replace(',', ' ', $redes);
  $obs=trim($res['obs']);
  $obs2=str_replace(',', ' ', $obs);$obs2=$obs2;
  $lin++;$linha=trim($res['emissao']).",".$res['remet'].",".$res['cidori'].",".$res['ufremet'].",".$res['dest'].",".$res['ciddes'].",".$res['ufdest'].",".$redes.",".$res['CTe'].",".$res['serie'].",".$res['nrNF'].",".$res['pedido'].",".$res['bruto'].",".$res['vlnf'].",".$res['vlpeso'].",".$res['despacho'].",".$res['gris'].",,".$res['vlfrete'].",".$res['fatura'].",".$obs2.",".$res['chave'].",".$res['icms'].",".$res['cnpjctrc'].",".$res['aliquota'].",".$res['cfop'].",".$res['entrega'].",".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("relatorio_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';
$format2 = '* ###';
$spreadsheet->getActiveSheet()->getStyle('A1:AA2')->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle('A1:AA2')->getAlignment()->setWrapText(true);
$spreadsheet->getActiveSheet()->getStyle('a1:AA1')->getFont()->setSize(14);
$spreadsheet->getActiveSheet()->getStyle('a2:AA2')->getFont()->setSize(12);
$spreadsheet->getActiveSheet()->getStyle('a1:AA2')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('A1:AA2')
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A3:A'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('D3:D'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('G3:G'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('I3:J'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('L3:L'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('T3:T'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A2:AA2')
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A1:AA2')
    ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$spreadsheet->getActiveSheet()->getStyle('X3:Y'.$lin)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);
$spreadsheet->getActiveSheet()->getStyle('J3:J'.$lin)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);
$spreadsheet->getActiveSheet()->getStyle('y3:y'.$lin)->getNumberFormat()->setFormatCode($format2);
$spreadsheet->getActiveSheet()->getStyle('m3:s'.$lin)->getNumberFormat()->setFormatCode($format);
$spreadsheet->getActiveSheet()->getStyle('X2:AA'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
for ($c=3; $c <= $lin; $c++) 
  { if ($c % 2 == 0) { $bg="d8d8d8";} else {$bg="ffffff";}
    $range='A'.$c.':AA'.$c;
    $spreadsheet->getActiveSheet()->setCellValue('R'.$c,'=S'.$c.'-W'.$c.'-O'.$c.'-P'.$c.'-Q'.$c);    
    $TDE = $spreadsheet->getActiveSheet()->getCell('R'.$c)->getCalculatedValue(); 
    if ($TDE<'-0.01'){$spreadsheet->getActiveSheet()->getStyleByColumnAndRow(18,$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:AA2')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('5882FA');
$spreadsheet->getActiveSheet()->mergeCells('A1:AA1');
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(7);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('S')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('T')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('U')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('V')->setWidth(45);
$spreadsheet->getActiveSheet()->getColumnDimension('W')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('X')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('Y')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('Z')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('AA')->setWidth(12);

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