TransWar File Browser

Path: C:\

<?php
date_default_timezone_set('America/Sao_Paulo');
$dateadded = date("d-m-Y"); 
ini_set('max_execution_time',1200); 
ini_set ('memory_limit','8095M') ;
$grupo=$_POST['grupo'];$ds_grupo=$_POST['dsgrupo'];
$arqxlsx="FUP-".$ds_grupo.".xlsx";
$fptemp = fopen('fup_temp.csv', 'w+');
$cabecalho="DASHBOARD ".$ds_grupo;
$cabecalho2="CONTROLE DE DESCARGA - GRUPO ".$ds_grupo;
$cabecalho3="Cálculo Horas Paradas - 2019 - Grupo ".$ds_grupo;
fwrite($fptemp,$cabecalho.PHP_EOL);  
fwrite($fptemp2,$cabecalho2.PHP_EOL);  
fwrite($fptemp3,$cabecalho3.',,,,,,,,,,,Acordo:,,,,TOTAL,,,,,,,,,,,,'.PHP_EOL);
fwrite($fptemp3,',,,,,,,,,,,,,,,,,,,,,,,,,,,'.PHP_EOL);
fwrite($fptemp,'DATA RECEBIM./ COLETA,CTe,TASK,DATA EMISSÃO NF,CFOP,FORNECEDOR,Nº DO PEDIDO,Nº DA N.F.,TIPO DE VEÍCULO,Nº PALETES,Nº VOLUMES,TRANS.,PLACA CAVALO/CARRETA,CHAVE NFe,PESO TAXADO,VALOR NF,NATUREZA MERCADORIA,TIPO DE MATERIAL,DESTINATÁRIO,CIDADE DESTINO,MANIFESTO,AR,Observação AR,Agenda,Carro,Data Solicitação,Data Confirmação,Data Agendada,Data Pretendida,Permanência (em dias),Manifesto,Chegada,Entregue,Tempo Total (em dias),Status,MÊS,DIA AGENDA,MÊS AGENDA,Conta,Prazo Entrega (Confirmação - Agenda) em Horas'.PHP_EOL);
fwrite($fptemp2,'Manifesto,Data,Mês,Motorista,Veículo,Carreta,Tipo de Veículo,Faturamento,Peso,Custo do Veículo,Tipo Contratação,Empresa,Valor Descarga,Data de Cobrança,Mês2,Agregado'.PHP_EOL);
fwrite($fptemp3,'Manifesto,Placa Cavalo,Placa Carreta,Data Pretendida,Veículo,Destino,Data Agendamento,Motorista,Data Apresentação,Data Apresentação Considerada (Se chegada antes da agenda),Status Descarregamento,Data Saída (Previsão),Total Dias,Total Dias Considerado,Valor Diária (24h),Total Complemento sem ICMS,Total Complemento com ICMS,Status Autorização Cobrança,Cte Complemento,Fatura,Vencimento,Status Financeiro,Status Atualizado,Mês,RETORNO BRAINFARMA,COMENTÁRIOS TRANS WAR,Prazo Entrega,MÊS AGENDA,DIA AGENDA'.PHP_EOL);
require_once("conexao_srvtms.php");
$sqlquery="SELECT    Convert(varchar(10), a.dt_Coleta,103) as c09
      ,a.nr_Conhecimento
      ,Convert(varchar(10), b.dt_Emissao,103) as c01    
      ,Convert(varchar(10), g.dt_Sugerida,103) as c02    
      ,g.nr_Carro
      ,g.ds_Agenda   
      ,Convert(varchar(10), g.dt_Solicitacao,103) as c03    
      ,Convert(varchar(10), g.dt_Retorno,103) as c04    
      ,Convert(varchar(10), g.dt_Agendamento,103) as c05    
      ,Convert(varchar(10), g.dt_Saida,103) as c06   
      ,Convert(varchar(5), g.hr_Saida,114) as c07    
      ,g.nr_Manifesto   
      ,g.ds_EmpresaDescarga  
      ,g.vl_Descarga
      ,g.ds_Destino
      ,g.ds_Autoriza
      ,g.nr_CTeParada
      ,g.nr_FaturaParada
      ,g.ds_Obs
      ,b.ds_Modelo
      ,a.ds_Remetente
      ,b.cd_ServicoNotaFiscal  
      ,b.nr_NotaFiscalInteiro
      ,b.vl_NotaFiscal
      ,b.qt_Volume
      ,a.nr_Referencia
      ,b.ds_ChaveNFe
      ,a.id_Movimento
      ,h.kg_PesoTaxado
      ,i.ds_Cliente
      ,e.ds_NaturezaMercadoria
      ,a.id_NaturezaMercadoria
      ,Convert(varchar(35), j.ds_Pessoa) as c13
      ,k.ds_Cidade
      ,m.id_Embalagem
      ,n.ds_Tipo
      ,o.id_Manifesto
      ,Convert(varchar(10), g.dt_Chegada,103) as c15    
      ,Convert(varchar(5), g.hr_Chegada,114) as c21    
      ,Convert(varchar(10), o.dt_Inclusao,103) as c16
      ,o.vl_FreteCTRCFOB
      ,o.kg_Mercadoria
      ,o.vl_Servico
      ,Convert(varchar(40), r.ds_Pessoa) as c17
      ,Convert(varchar(40), t.ds_Pessoa) as c18
      ,p.cd_Placa as c19 
      ,q.cd_Placa as c20 
      ,s.ds_TipoVeiculo
      ,w.id_GrupoCliente as destinatario
      ,v.id_GrupoCliente as remetente
    FROM tbdMovimento AS a
      left JOIN tbdMovimentoNotaFiscal AS b ON  a.id_Movimento = b.id_Movimento
      left JOIN tbdDimensao AS h ON  a.id_Movimento = h.id_Movimento
      left JOIN tbdclienteSistema AS i ON  h.id_Cliente = i.id_Cliente
      left JOIN tbdAgendaNFs AS f ON  f.ds_ChaveNFe = b.ds_ChaveNFe
      left JOIN tbdAgenda AS g ON  f.id_Agenda = g.id_Agenda
      left JOIN tbdPessoa AS c on a.id_ClienteFaturamento = c.id_Pessoa
      left JOIN tbdConfiguracaoCliente AS d on d.id_Pessoa = c.id_Pessoa
      left JOIN tbdPessoa AS u on a.id_Remetente = u.id_Pessoa
      left JOIN tbdConfiguracaoCliente AS v on v.id_Pessoa = u.id_Pessoa
      left JOIN tbdNaturezaMercadoria AS e on a.id_NaturezaMercadoria = e.id_NaturezaMercadoria
      left JOIN tbdPessoa AS j on a.id_Destinatario = j.id_pessoa
      left JOIN tbdConfiguracaoCliente AS w on w.id_Pessoa = j.id_Pessoa
      left JOIN tbdCidade AS k ON  a.id_CidadeDestinatario = k.id_Cidade
      left JOIN tbdItemPedidoColeta AS m on b.id_Movimento = m.id_Movimento
      left JOIN tbdManifestoMovimento AS n on (a.id_Movimento = n.id_Movimento and n.ds_Tipo = 'ENTREGA' and (n.id_Ocorrencia is null or n.id_Ocorrencia<>69))
      left JOIN tbdManifesto AS o on n.id_Manifesto = o.id_Manifesto
      left JOIN tbdVeiculo AS p on o.id_Veiculo = p.id_Veiculo
      left JOIN tbdVeiculo AS q on o.id_Carreta = q.id_Veiculo
      left JOIN tbdPessoa AS r on o.id_Motorista = r.id_Pessoa
      left JOIN tbdPessoa AS t on o.id_Agregado = t.id_Pessoa
      left JOIN tbdTipoVeiculo AS s on p.id_TipoVeiculo = s.id_TipoVeiculo
    WHERE  d.id_GrupoCliente='".$grupo."' and Convert(varchar(10), a.dt_Cadastro,112)>='".$dia."' and a.id_TipoMovimento<>'5'  and a.id_TipoMovimento<>'109' and (v.id_GrupoCliente='".$grupo."' or w.id_GrupoCliente='".$grupo."') order by o.id_Manifesto, b.nr_NotaFiscalInteiro ASC";
$result = sqlsrv_query( $conn, $sqlquery, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
$num = sqlsrv_num_rows($result); 
$lin=2;$lin2=2;$lin3=3;$manif1="999999";
while ($resultado = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
  $remete=substr($resultado['ds_Remetente'], 0, 35);$remete=str_replace(',', ' ',$remete );
  $destin=str_replace(',', ' ', trim($resultado['c13']));
  $NR=trim($resultado['nr_Referencia']); $b= strpos($NR, "TASK"); $NR= substr($NR, $b+4, 7);
  $tipo=$resultado['id_NaturezaMercadoria'];
  $cte=trim($resultado['nr_Conhecimento']);if ($cte!=""){$cte="/".$cte;}
  $volume=$resultado['qt_Volume'];
  $paletes=$resultado['id_Embalagem'];
  $autoriza=trim($resultado['ds_Autoriza']);
  $chave=substr($resultado['ds_ChaveNFe'],0,44);
  $transp=substr($resultado['ds_Cliente'],0,20);
  $natu=substr($resultado['ds_NaturezaMercadoria'],0,35);
  $dest=substr($resultado['ds_Cidade'],0,20);
  $veic=substr($resultado['ds_TipoVeiculo'],0,15);
    if ($tipo=='50' or $tipo=='146') {$tipo='QUÍMICO CLASSIFICADO';} else 
    {if ($tipo=='33' or $tipo=='147') {$tipo='QUÍMICO NÃO CLASSIFICADO';} else 
      {if ($tipo=='131' or $tipo=='101') {$tipo='CONTROLADO';} else 
        {if ($tipo=='104' or $tipo=='110' or $tipo=='130') {$tipo='PERECÍVEL';} else 
          {if ($tipo=='27' or $tipo=='97' or $tipo=='99' or $tipo=='70' or $tipo=='11') {$tipo='FARMA';} else 
            {if ($tipo=='17' or $tipo=='34' or $tipo=='35' or $tipo=='55' or $tipo=='102') {$tipo='DIVERSOS';} else 
              {$tipo='N/I';}}}}}}
  if ($paletes==9){$paletes=$volume;}else{$paletes="";}
  if (trim($resultado['ds_Tipo'])=="ENTREGA"){
    $manifesto=$resultado['id_Manifesto'];$veiculo=current(str_word_count($veic,2));$placa=$resultado['c19'];
    if($veiculo=="CAVALO"){$veiculo="CARRETA";}
    if($veiculo=="CARRETA"){$carreta=$resultado['c20'];}else{$carreta="";}
  }else{$manifesto="";$placa="";$veiculo="";$carreta="";}

  $lin++;$linha=$resultado['c09'].",".$cte.",".$NR.",".$resultado['c01'].",".trim($resultado['ds_Modelo']).",".$remete.",".trim($resultado['cd_ServicoNotaFiscal']).",".$resultado['nr_NotaFiscalInteiro'].",".$veiculo.",".$paletes.",".$resultado['qt_Volume'].",".$transp.",".$placa."/".$carreta.",".$chave.",".$resultado['kg_PesoTaxado'].",".$resultado['vl_NotaFiscal'].",".$natu.",".$tipo.",".$destin.",".$dest.",".$manifesto.",,,".$resultado['ds_Agenda'].",".$resultado['nr_Carro'].",".$resultado['c03'].",".$resultado['c04'].",".$resultado['c05'].",,,,".$resultado['c15']." ".$resultado['c21'].",".$resultado['c06']." ".$resultado['c07'].",,,,,,,".PHP_EOL;

  if ($manifesto<>""){ if ($manifesto<>$manif1) { $manif1=$manifesto;

    $lin2++;$linha2=$manifesto.",".$resultado['c16'].",,".trim($resultado['c17']).",".$placa.",".$carreta.",".$veiculo.",".$resultado['vl_FreteCTRCFOB'].",".$resultado['kg_Mercadoria'].",".$resultado['vl_Servico'].",,".trim($resultado['ds_EmpresaDescarga']).",".$resultado['vl_Descarga'].",,,".trim($resultado['c18']).PHP_EOL;
    fwrite($fptemp2, $linha2);

    $lin3++;$linha3=$manifesto.",".$placa.",".$carreta.",".$resultado['c02'].",".trim($resultado['nr_Carro']).",".trim($resultado['ds_Destino']).",".$resultado['c05'].",".trim($resultado['c17']).",".$resultado['c15']." ".$resultado['c21'].",,,".$resultado['c06']." ".$resultado['c07'].",0,0,,,,".$autoriza.",".$resultado['nr_CTeParada'].",".$resultado['nr_FaturaParada'].",,,,,,".trim($resultado['ds_Obs']).",,,,".PHP_EOL;
    fwrite($fptemp3, $linha3);  

  } 
  }

fwrite($fptemp, $linha);}
fclose($fptemp); 
fclose($fptemp2); 
fclose($fptemp3); 
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("agenda_temp.csv");
$writer = new Xlsx($spreadsheet);
$spreadsheet->getActiveSheet()->setTitle('AR GERADO');
$spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(66);
$spreadsheet->getActiveSheet()->mergeCells('A1:AN1');
$styleArray = [
    'borders' => [
        'inside' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['argb' => '000000'],
        ],
    ],
];
$styleArray2 = [
    'borders' => [
        'vertical' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['argb' => 'a4a4a4'],
        ],
    ],
];
$format = '#,##0.00';
$format1 = '#,###';
$format2 = '###';
$spreadsheet->getActiveSheet()->getStyle('A1:AN2')->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle('A2:AN'.$lin)->applyFromArray($styleArray2);
$spreadsheet->getActiveSheet()->getStyle('a1:AN1')->getFont()->setSize(20);
$spreadsheet->getActiveSheet()->getStyle('a2:AN2')->getFont()->setSize(11);
$spreadsheet->getActiveSheet()->getStyle('a2:AN2')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('A1:AN2')
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a1:AN2')
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('y3:AH'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('AJ3:AN'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('u3:v'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a3:e'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a2:AN2')
    ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$spreadsheet->getActiveSheet()->getStyle('o3:o'.$lin)->getNumberFormat()->setFormatCode($format1);
$spreadsheet->getActiveSheet()->getStyle('p3:p'.$lin)->getNumberFormat()->setFormatCode($format);
$spreadsheet->getActiveSheet()->getStyle('W3:W'.$lin)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DDMMYYYY);
$spreadsheet->getActiveSheet()->getStyle('Z3:AC'.$lin)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DDMMYYYY);
$spreadsheet->getActiveSheet()->getStyle('AF3:AG'.$lin)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME);
$spreadsheet->getActiveSheet()->getStyle('AH3:AH'.$lin)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_00);
$spreadsheet->getActiveSheet()->getStyle('AD3:AD'.$lin)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_00);
for ($c=3; $c <= $lin; $c++) 
  { if ($c % 2 == 0) { $bg="d8d8d8";} else {$bg="ffffff";}
    $range='A'.$c.':AN'.$c;
    $spreadsheet->getActiveSheet()->setCellValue('AJ'.$c,'=MONTH(A'.$c.')');
    $spreadsheet->getActiveSheet()->setCellValue('AK'.$c,'=if(AB'.$c.'<>"",DAY(AB'.$c.'),"")');
    $spreadsheet->getActiveSheet()->setCellValue('AL'.$c,'=if(AB'.$c.'<>"",MONTH(AB'.$c.'),"")');
    $spreadsheet->getActiveSheet()->setCellValue('AE'.$c,'=if(U'.$c.'="","",U'.$c.')');
    $spreadsheet->getActiveSheet()->setCellValue('AN'.$c,'=if(AB'.$c.'<>"" ,if(AA'.$c.'<>"" ,(AB'.$c.'-AA'.$c.')*24,""),"")');
    $spreadsheet->getActiveSheet()->setCellValue('AH'.$c,'=IFERROR(AG'.$c.'-AF'.$c.',IFERROR(now()-AF'.$c.',""))');
    $spreadsheet->getActiveSheet()->setCellValue('W'.$c,'=if(AB'.$c.'<>"",AB'.$c.',if(X'.$c.'<>"",X'.$c.',if(V'.$c.'="","AGUARDANDO AR/CONSOLIDAÇÃO",if(V'.$c.'>0,"AGUARDANDO CONSOLIDAÇÃO","AGUARDANDO AR"))))');
    $spreadsheet->getActiveSheet()->setCellValue('AI'.$c,'=if(AG'.$c.'<>" ","Entregue",if(AF'.$c.'<>" ","Na porta",if(U'.$c.'<>"","Em Transito",if(AA'.$c.'<>"","Agendada",if(Z'.$c.'<>"","Agenda_Solicitada",W'.$c.')))))');
    $spreadsheet->getActiveSheet()->setCellValue('AD'.$c,'=if(AB'.$c.'="",TODAY()-A'.$c.',AB'.$c.'-A'.$c.')');
    $spreadsheet->getActiveSheet()->getStyle($range)->getFill()
        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
        ->getStartColor()->setARGB($bg);
  }
$spreadsheet->getActiveSheet()->getStyle('A2:AN2')->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(40);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(45);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(35);
$spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(25);
$spreadsheet->getActiveSheet()->getColumnDimension('S')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('T')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('U')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('V')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('W')->setWidth(33);
$spreadsheet->getActiveSheet()->getColumnDimension('X')->setWidth(25);
$spreadsheet->getActiveSheet()->getColumnDimension('Y')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('Z')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('AA')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('AB')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('AC')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('AD')->setWidth(13);
$spreadsheet->getActiveSheet()->getColumnDimension('AE')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('AF')->setWidth(17);
$spreadsheet->getActiveSheet()->getColumnDimension('AG')->setWidth(17);
$spreadsheet->getActiveSheet()->getColumnDimension('AH')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('AI')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('AJ')->setWidth(7);
$spreadsheet->getActiveSheet()->getColumnDimension('AK')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('AL')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('AM')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('AN')->setWidth(15);
$spreadsheet->getActiveSheet()->getStyle('A1:AN2')->getAlignment()->setWrapText(true);

$reader->setSheetIndex(1);
//$spreadsheet->setActiveSheetIndex(1);
$reader->loadIntoExisting("descarga_temp.csv", $spreadsheet);
$spreadsheet->getActiveSheet()->setTitle('CONTROLE DESCARGA');
$spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(40);
$spreadsheet->getActiveSheet()->getStyle('A1:p2')->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle('a1:p1')->getFont()->setSize(20);
$spreadsheet->getActiveSheet()->getStyle('a2:p2')->getFont()->setSize(11);
$spreadsheet->getActiveSheet()->getStyle('a2:p2')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->mergeCells('A1:p1');
$spreadsheet->getActiveSheet()->getStyle('a2:p2')
    ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$spreadsheet->getActiveSheet()->getStyle('h3:j'.$lin2)->getNumberFormat()->setFormatCode($format);
$spreadsheet->getActiveSheet()->getStyle('m3:m'.$lin2)->getNumberFormat()->setFormatCode($format);
for ($c=3; $c <= $lin2; $c++) 
  { if ($c % 2 == 0) { $bg="d8d8d8";} else {$bg="ffffff";}
    $range='A'.$c.':p'.$c;
    $spreadsheet->getActiveSheet()->setCellValue('O'.$c,'=if(N'.$c.'<>"",MONTH(N'.$c.'),"")');
    $spreadsheet->getActiveSheet()->setCellValue('c'.$c,'=if(b'.$c.'<>"",MONTH(b'.$c.'),"")');
    $spreadsheet->getActiveSheet()->setCellValue('k'.$c,'=if(p'.$c.'="","PRÓPRIO","TERCEIRO")');
    $spreadsheet->getActiveSheet()->getStyle($range)->getFill()
        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
        ->getStartColor()->setARGB($bg);
  }
$spreadsheet->getActiveSheet()->getStyle('A2:p2')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('5882FA');
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(7);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(7);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(40);
$spreadsheet->getActiveSheet()->getStyle('a1:p2')
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a3:c'.$lin2)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('e3:f'.$lin2)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('n3:o'.$lin2)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('k3:k'.$lin2)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A2:P'.$lin2)->applyFromArray($styleArray2);
$spreadsheet->getActiveSheet()->getStyle('A1:p2')
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A1:p2')->getAlignment()->setWrapText(true);

$reader->setSheetIndex(2);
//$spreadsheet->setActiveSheetIndex(1);
$format3 = 'R$  #,##0.00';
$reader->loadIntoExisting("horaparada_temp.csv", $spreadsheet);
$spreadsheet->getActiveSheet()->setTitle('HORA PARADA');
$spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
$spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(30);
$spreadsheet->getActiveSheet()->getStyle('A1:AC3')->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle('A4:AC'.$lin3)->applyFromArray($styleArray2);
$spreadsheet->getActiveSheet()->getStyle('a1:AC2')->getFont()->setSize(20);
$spreadsheet->getActiveSheet()->getStyle('a3:AC3')->getFont()->setSize(11);
$spreadsheet->getActiveSheet()->getStyle('a1:AC3')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getCell('N1')->setValue('ACORDO:');
$spreadsheet->getActiveSheet()->getCell('N2')->setValue('R$ 36,00 a hora isento 24h');
$spreadsheet->getActiveSheet()->getCell('Q1')->setValue('TOTAL:');
$spreadsheet->getActiveSheet()->getCell('Q2')->setValue('=SUM(Q4:Q'.$lin3.')');
$spreadsheet->getActiveSheet()->mergeCells('N1:P1');
$spreadsheet->getActiveSheet()->mergeCells('N2:P2');
$spreadsheet->getActiveSheet()->mergeCells('Q1:T1');
$spreadsheet->getActiveSheet()->mergeCells('Q2:T2');
$spreadsheet->getActiveSheet()->getStyle('a3:AC3')
    ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$format4 = 'dd/mm/yyy hh:mm';
$spreadsheet->getActiveSheet()->getStyle('q2')->getNumberFormat()->setFormatCode($format3);
$spreadsheet->getActiveSheet()->getStyle('O4:Q'.$lin3)->getNumberFormat()->setFormatCode($format3);
$spreadsheet->getActiveSheet()->getStyle('N4:N'.$lin3)->getNumberFormat()->setFormatCode($format);
$spreadsheet->getActiveSheet()->getStyle('M4:M'.$lin3)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_00);
$spreadsheet->getActiveSheet()->getStyle('i4:j'.$lin3)->getNumberFormat()->setFormatCode($format4);
$spreadsheet->getActiveSheet()->getStyle('G4:G'.$lin3)->getNumberFormat()->setFormatCode($format4);
$spreadsheet->getActiveSheet()->getStyle('l4:l'.$lin3)->getNumberFormat()->setFormatCode($format4);
for ($c=4; $c <= $lin3; $c++) 
  { if ($c % 2 == 0) { $bg="d8d8d8";} else {$bg="ffffff";}
    $range='A'.$c.':AC'.$c;
    $status1=$spreadsheet->getActiveSheet()->getCellByColumnAndRow(18,$c)->getValue();
    $spreadsheet->getActiveSheet()->setCellValue('X'.$c,'=IFERROR(MONTH(I'.$c.'),MONTH(TODAY()))');
    $spreadsheet->getActiveSheet()->setCellValue('AB'.$c,'=if(G'.$c.'<>"",MONTH(G'.$c.'),"")');
    $spreadsheet->getActiveSheet()->setCellValue('AC'.$c,'=if(G'.$c.'<>"",DAY(G'.$c.'),"")');
    $spreadsheet->getActiveSheet()->setCellValue('K'.$c,'=if(L'.$c.'<>" ","Finalizado",if(I'.$c.'<>" ","Aguardando",if(A'.$c.'<>"","Em Rota","")))');
    $spreadsheet->getActiveSheet()->setCellValue('J'.$c,'=if(I'.$c.'<>" ",if(I'.$c.'<(G'.$c.'+0.25),G'.$c.'+0.25,I'.$c.'+0.0001),"")');
    $spreadsheet->getActiveSheet()->setCellValue('M'.$c,'=if(L'.$c.'<>" ",if(I'.$c.'<>" ",if((L'.$c.'-J'.$c.')<0,"",L'.$c.'-J'.$c.'),""),"")');
    $spreadsheet->getActiveSheet()->setCellValue('O'.$c,'=if(M'.$c.'<>"",if((M'.$c.'-1)<0,"",864),"")');
    $spreadsheet->getActiveSheet()->setCellValue('N'.$c,'=if(M'.$c.'<>"",if((M'.$c.'-1)<0,"",M'.$c.'-1),"")');
    $spreadsheet->getActiveSheet()->setCellValue('P'.$c,'=IFERROR(N'.$c.'*O'.$c.',"")');
    $spreadsheet->getActiveSheet()->setCellValue('Q'.$c,'=IFERROR(P'.$c.'/0.93,"")');
    if ($status1==""){$spreadsheet->getActiveSheet()->setCellValue('R'.$c,'=if(L'.$c.'<>" ",if(Q'.$c.'<>"",if(J'.$c.'<=G'.$c.'+0.7089,"Aguardando Autorização","Perdeu Agenda"),if(J'.$c.'<=G'.$c.'+0.7089,"","Perdeu Agenda")),"")');}
    $spreadsheet->getActiveSheet()->setCellValue('W'.$c,'=if(V'.$c.'="PAGA","42. PAGO",if(V'.$c.'="EM ABERTO","41. FATURADO",if(T'.$c.'="FATURAR","40. PENDENTE FATURAMENTO",if(R'.$c.'="Não gerou Hora Parada","20. Não gerou Hora Parada",if(R'.$c.'="Perdeu Agenda","21. Perdeu Agenda",if(R'.$c.'="erro agendamento trans war","22. ERRO AGENDAMENTO TW",if(R'.$c.'="aguardando autorização","31. Aguardando Autorização",if(S'.$c.'="AUTORIZADO","32. AUTORIZADO",if(R'.$c.'="NÃO AUTORIZADA - DE ACORDO","34. NÃO AUTORIZADA - DE ACORDO",if(R'.$c.'="NÃO AUTORIZADA","33. NÃO AUTORIZADA EM ANÁLISE",if(R'.$c.'="NÃO AUTORIZADA - PROCEDENTE SEGUNDO TRANS WAR","35. NÃO AUTORIZADA - COBRANÇA PROCEDE",if(R'.$c.'="GERAR CTE","30. GERAR CTE",if(K'.$c.'="FINALIZADO","12. CARGA ENTREGUE",if(K'.$c.'="AGUARDANDO","11. NA PORTA",if(I'.$c.'="","10. EM TRANSITO","")))))))))))))))');

    $spreadsheet->getActiveSheet()->getStyle($range)->getFill()
        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
        ->getStartColor()->setARGB($bg);
  }
$spreadsheet->getActiveSheet()->getStyle('A3:AC3')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('5882FA');
$spreadsheet->getActiveSheet()->mergeCells('A1:M2');
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(14);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('S')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('T')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('U')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('V')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('W')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('X')->setWidth(7);
$spreadsheet->getActiveSheet()->getColumnDimension('Y')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('Z')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('AA')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('AB')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('AC')->setWidth(10);
$spreadsheet->getActiveSheet()->getStyle('A1:AC3')
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a1:AC3')
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a4:G'.$lin3)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('I4:O'.$lin3)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('S4:V'.$lin3)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('X4:AC'.$lin3)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->mergeCells('U1:AC2');
$spreadsheet->getActiveSheet()->getStyle('A1:AC3')->getAlignment()->setWrapText(true);
//$spreadsheet->getActiveSheet()->getCell('Q2')->getCalculatedValue();
$writer->save($arqxlsx);
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
unlink('agenda_temp.csv'); 
unlink('descarga_temp.csv'); 
unlink('horaparada_temp.csv');
echo "<script language='JavaScript'>alert('Planilha EXCEL gerada com Sucesso!  \\nTecle OK para BAIXAR.');location.href='dashboard.php?arquivo=".$arqxlsx."';</script>";
?>