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;
$mes=$mes2;
switch ($mes){
case 1: $mes = "JANEIRO"; break;
case 2: $mes = "FEVEREIRO"; break;
case 3: $mes = "MARÇO"; break;
case 4: $mes = "ABRIL"; break;
case 5: $mes = "MAIO"; break;
case 6: $mes = "JUNHO"; break;
case 7: $mes = "JULHO"; break;
case 8: $mes = "AGOSTO"; break;
case 9: $mes = "SETEMBRO"; break;
case 10: $mes = "OUTUBRO"; break;
case 11: $mes = "NOVEMBRO"; break;
case 12: $mes = "DEZEMBRO"; break;
}

$arqxlsx="Excel/FRETES TRANSWAR.xlsx";
$fptemp = fopen('relatorio_temp.csv', 'w+');
fwrite($fptemp,',CNPJ da transportadora,CNPJ da transportadora,,INFORMAR AQUI O CNPJ DO LOCAL DE ENTREGA - RECEBEDOR (na ausência do recebedor: colocar o destinatário),Número do conhecimento de transporte,Série do conhecimento,Data de emissão do conhecimento de transporte,,Sempre 1 para fretes tomados e pagos pela Firmenich,Chave de acesso,Valor total do frete,Alíquota de ICMS do frete,INDICAR O CFOP CORRESPONDENTE À OPERAÇÃO,NATUREZA DO FRETE: FRETE DE VENDAS - FRETE DE COMPRA DE MP - FRETE DE DEVOLUÇÃO - FRETE DE RESÍDUOS'.PHP_EOL);
fwrite($fptemp,',A,A,,A,A,A,D,,A,A,N,N,,'.PHP_EOL);
fwrite($fptemp,',20,20,,20,12,5,0,,1,50,22,15.4,,'.PHP_EOL);
fwrite($fptemp,',SYNITF_DOF,SYNITF_DOF,,SYNITF_DOF,SYNITF_DOF,SYNITF_DOF,SYNITF_DOF,,SYNITF_DOF,SYNITF_DOF,SYNITF_IDF,SYNITF_IDF,,'.PHP_EOL);
fwrite($fptemp,',COR_DOF,COR_DOF,,COR_DOF,COR_DOF,COR_DOF,COR_DOF,,COR_DOF,COR_DOF,COR_IDF,COR_IDF,,'.PHP_EOL);
fwrite($fptemp,',EMITENTE_PFJ_CODIGO,REMETENTE_PFJ_CODIGO,Colunas1,ENTREGA_PFJ_CODIGO,NUMERO,SERIE_SUBSERIE,DH_EMISSAO,REMETENTE,IND_RESP_FRETE,NFE_LOCALIZADOR,PRECO_UNITARIO,ALIQ_ICMS,CFOP,NATUREZA'.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
      ,rtrim(a.pc_ICMS) as aliquota
      ,a.vl_Frete as vlfrete
      ,e.cd_CGCCPF as dest
      ,m.cd_CGCCPF as remetente
      ,p.cd_CGCCPF as redespacho
      ,l.cd_CGCCPF as cnpjctrc
      ,rtrim(ltrim(k.ds_ChaveCTe)) as chave
      ,rtrim(o.cd_CFOPImpresso) as cfop
  FROM dbo.tbdMovimento as a
      LEFT JOIN dbo.tbdDimensao as b on  a.id_Movimento = b.id_Movimento
      LEFT JOIN dbo.tbdPessoa as e on a.id_Destinatario = e.id_Pessoa
      LEFT JOIN dbo.tbdPessoa as m on a.id_Remetente = m.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.tbdMovimentoDespesaManifesto as o on  a.id_Movimento = o.id_Movimento
      LEFT JOIN dbo.tbdLoteCTeMovimento as k on k.tp_CTeCancelado='N' and 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=6;$manif1="999999";
while ($res = sqlsrv_fetch_array($sql, SQLSRV_FETCH_ASSOC)) {
if ($res['CTe']<>$manif1) { $manif1=$res['CTe'];//$diversos=$res['outros']+$res['outros2'];
  $emissao=trim($res['emissao']);
  $ano=substr($emissao, 6);
  $mes3=substr($emissao, 3,-5);
  $emissao=substr($emissao, 0,-8).$mes3.$ano;
  $redes=trim($res['redespacho']);
  $redes=str_replace(',', ' ', $redes);
  if ($redes<>''){$cnpj=$res['redespacho'];}else{$cnpj=$res['dest'];}
  $lin++;$linha=",".$res['cnpjctrc'].",".$res['cnpjctrc'].",,".$cnpj.",".$res['CTe'].",".$res['serie'].",".$emissao.",".$res['remetente'].",1,".$res['chave'].",".$res['vlfrete'].",".$res['aliquota'].",".$res['cfop'].",,".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($mes);
$spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(90);
$spreadsheet->getActiveSheet()->getRowDimension('6')->setRowHeight(15);
$styleArray = [
    'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['argb' => '000000'],
        ],
    ],
];
$format2 = '###';
$format = '* #,##0.00';
$spreadsheet->getActiveSheet()->getStyle('B1:O6')->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle('A1:O6')->getAlignment()->setWrapText(true);
$spreadsheet->getActiveSheet()->getStyle('A1:O6')->getFont()->setName('Arial');
$spreadsheet->getActiveSheet()->getStyle('N1:O1')->getFont()->setSize(10);
$spreadsheet->getActiveSheet()->getStyle('B1:O1')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('B6:O6')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('A1:O6')
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A1:O6')
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A7:O'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
$spreadsheet->getActiveSheet()->getStyle('M7:O'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$spreadsheet->getActiveSheet()->getStyle('e1')
    ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
$spreadsheet->getActiveSheet()->getStyle('L7:L'.$lin)->getNumberFormat()->setFormatCode($format);
$spreadsheet->getActiveSheet()->getStyle('B7:E'.$lin)->getNumberFormat()->setFormatCode($format2);
$spreadsheet->getActiveSheet()->getStyle('I7:I'.$lin)->getNumberFormat()->setFormatCode($format2);
$spreadsheet->getActiveSheet()->getStyle('B6:O6')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('F7FE2E');
$spreadsheet->getActiveSheet()->getStyle('k6')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('FFBF00');
$spreadsheet->getActiveSheet()->getStyle('m6:O6')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('FFBF00');

$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(45);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(20);

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