TransWar File Browser

Path: C:\

<?php
date_default_timezone_set('America/Sao_Paulo');
$dateadded = date("Ymd"); 
ini_set('max_execution_time',1200); 
ini_set ('memory_limit','8095M') ;
$grupo=$_POST['grupo'];$ds_grupo=$_POST['dsgrupo'];
$arqxlsx="DASHBOARD-".$ds_grupo.".xlsx";
if ( isset($_POST['dia']) ) { $dia=$_POST['dia'];
$ano=substr($dia, 6);
$mes=substr($dia, 3,-5);
$dia=$ano.$mes.substr($dia, 0,-8);
} else { $dia=$dateadded;}
$fptemp = fopen('agenda_temp.csv', 'w+');
$fptemp2 = fopen('descarga_temp.csv', 'w+');
$fptemp3 = fopen('horaparada_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,Manobra/ Transbordo,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,AGREGADO,VALOR P/ AGREGADO,LIBERADO,DATA PAGAMENTO TW'.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_Agendamento,112) as c05a    
      ,Convert(varchar(10), g.dt_Saida,103) as c06   
      ,Convert(varchar(5), g.hr_Saida,114) as c07    
      ,g.nr_Manifesto   
      ,Convert(varchar(25), g.ds_EmpresaDescarga) as c22 
      ,g.vl_Descarga
      ,g.vl_Manobra
      ,g.ds_Destino
      ,g.ds_Autoriza
      ,g.nr_CTeParada
      ,g.nr_FaturaParada
      ,g.nr_CteParada
      ,g.nr_FaturaParada
      ,Convert(varchar(10), g.dt_Vencimento,103) as vencimento    
      ,Convert(varchar(10), g.dt_Descarga,103) as dtdescarga    
      ,g.ds_Retorno
      ,g.ds_Coment
      ,g.ds_Status
      ,g.ds_StatusFinanc
      ,g.ds_StatusAtualiza
      ,b.ds_Modelo
      ,a.ds_Remetente
      ,b.cd_ServicoNotaFiscal  
      ,b.nr_NotaFiscalInteiro
      ,b.vl_NotaFiscal
      ,b.qt_Volume
      ,a.cm_Coleta
      ,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(10), g.dt_Chegada,112) as c15a    
      ,Convert(varchar(8), 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
      ,g.ds_Liberado as liberado
      ,Convert(varchar(10), g.dt_Pago, 103) as dtpago
    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 or g.nr_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 ASC, g.ds_Agenda DESC, 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";$chaveant='';$agendant='999999';
while ($resultado = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) 
{ if ($chaveant!=$resultado['ds_ChaveNFe'])
  { $chaveant=$resultado['ds_ChaveNFe'];
    $remete=substr($resultado['ds_Remetente'], 0, 35);
    $remete=str_replace(',', ' ',$remete );
    $destin=str_replace(',', ' ', trim($resultado['c13']));
    $NR=trim($resultado['cm_Coleta']); $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);
    $dsagenda=trim($resultado['ds_Agenda']);
    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.",,,".$dsagenda.",".$resultado['nr_Carro'].",".$resultado['c03'].",".$resultado['c04'].",".$resultado['c05'].",,,,".$resultado['c15']." ".$resultado['c21'].",".$resultado['c06']." ".$resultado['c07'].",,,,,,,".PHP_EOL;

    if ($manifesto<>"")
    { if ((trim($resultado['ds_Cidade'])=='Anapolis' or trim($resultado['ds_Cidade'])=='Goiania') and (($manifesto<>$manif1) or ($manifesto==$manif1 and $dsagenda<>$agendant and $dsagenda<>''))) 
      { $manif1=$manifesto;$agendant=$dsagenda;
        if (trim($resultado['c18'])<>""){$agreg=trim($resultado['c18']);$propter="TERCEIRO";}else{$agreg="";$propter="PRÓPRIO";}
        $lin2++;$linha2=$manifesto.",".$resultado['c16'].",,".trim($resultado['c17']).",".$placa.",".$carreta.",".$veiculo.",".$resultado['vl_FreteCTRCFOB'].",".$resultado['kg_Mercadoria'].",".$resultado['vl_Servico'].",".$propter.",".trim($resultado['c22']).",".$resultado['vl_Descarga'].",".$resultado['vl_Manobra'].",".$resultado['dtdescarga'].",,".$agreg.PHP_EOL;
        fwrite($fptemp2, $linha2);

        if ($resultado['c15a']!=null)
          { $colI=strtotime($resultado['c15a'].' '.$resultado['c21']);$colI=date('d/m/Y H:i',$colI);}else{$colI=' ';}
        if ($resultado['liberado']=="S"){$lib="SIM";}else{$lib="NÃO";}
        $lin3++;$linha3=$manifesto.",".$placa.",".$carreta.",".$resultado['c02'].",".$dsagenda.",".trim($resultado['ds_Destino']).",".$resultado['c05'].",".trim($resultado['c17']).",".$colI.",,,".$resultado['c06']." ".$resultado['c07'].",0,0,,,,".$autoriza.",".$resultado['nr_CTeParada'].",".$resultado['nr_FaturaParada'].",".$resultado['vencimento'].",".trim($resultado['ds_StatusFinanc']).",".trim($resultado['ds_StatusAtualiza']).",,".trim($resultado['ds_Retorno']).",".trim($resultado['ds_Coment']).",,,,".$agreg.",,".$lib.",".$resultado['dtpago'].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:Q2')->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle('A1:Q1')->getFont()->setSize(20);
$spreadsheet->getActiveSheet()->getStyle('A2:Q2')->getFont()->setSize(11);
$spreadsheet->getActiveSheet()->getStyle('A2:Q2')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->mergeCells('A1:Q1');
$spreadsheet->getActiveSheet()->getStyle('A2:Q2')
    ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$spreadsheet->getActiveSheet()->getStyle('H3:J'.$lin2)->getNumberFormat()->setFormatCode($format);
$spreadsheet->getActiveSheet()->getStyle('M3:N'.$lin2)->getNumberFormat()->setFormatCode($format);
for ($c=3; $c <= $lin2; $c++) 
  { if ($c % 2 == 0) { $bg="d8d8d8";} else {$bg="ffffff";}
    $range='A'.$c.':Q'.$c;
    $spreadsheet->getActiveSheet()->setCellValue('P'.$c,'=if(O'.$c.'<>"",MONTH(O'.$c.'),"")');
    $spreadsheet->getActiveSheet()->setCellValue('C'.$c,'=if(B'.$c.'<>"",MONTH(B'.$c.'),"")');
    $spreadsheet->getActiveSheet()->getStyle($range)->getFill()
        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
        ->getStartColor()->setARGB($bg);
  }
$spreadsheet->getActiveSheet()->getStyle('A2:Q2')->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(12);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(7);
$spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(45);
$spreadsheet->getActiveSheet()->getStyle('A1:Q2')
    ->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('O3:P'.$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:Q'.$lin2)->applyFromArray($styleArray2);
$spreadsheet->getActiveSheet()->getStyle('A1:Q2')
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A1:Q2')->getAlignment()->setWrapText(true);

$reader->setSheetIndex(2);
//$spreadsheet->setActiveSheetIndex(1);
$format3 = 'R$  #,##0.00';
$format4 = 'dd/mm/yyyy hh:mm';
$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:AG3')->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle('A4:AG'.$lin3)->applyFromArray($styleArray2);
$spreadsheet->getActiveSheet()->getStyle('a1:AG2')->getFont()->setSize(20);
$spreadsheet->getActiveSheet()->getStyle('a3:AG3')->getFont()->setSize(11);
$spreadsheet->getActiveSheet()->getStyle('a1:AG3')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getCell('N1')->setValue('ACORDO:');
$spreadsheet->getActiveSheet()->getCell('N2')->setValue('R$ 36,00 a hora isento 24h');
$spreadsheet->getActiveSheet()->getCell('AD1')->setValue('ACORDO P/ AGREGADO:');
$spreadsheet->getActiveSheet()->getCell('AD2')->setValue('R$ 12,50 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('AD1:AG1');
$spreadsheet->getActiveSheet()->mergeCells('AD2:AG2');
$spreadsheet->getActiveSheet()->mergeCells('Q1:T1');
$spreadsheet->getActiveSheet()->mergeCells('Q2:T2');
$spreadsheet->getActiveSheet()->getStyle('a3:AG3')
    ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$spreadsheet->getActiveSheet()->getStyle('q2')->getNumberFormat()->setFormatCode($format3);
$spreadsheet->getActiveSheet()->getStyle('O4:Q'.$lin3)->getNumberFormat()->setFormatCode($format3);
$spreadsheet->getActiveSheet()->getStyle('AE4:AE'.$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:i'.$lin3)->getNumberFormat()->setFormatCode($format4);
$spreadsheet->getActiveSheet()->getStyle('j4: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.':AG'.$c;$b=$c-1;
    $lib1=$spreadsheet->getActiveSheet()->getCellByColumnAndRow(32,$c)->getValue();
    $status1=$spreadsheet->getActiveSheet()->getCellByColumnAndRow(18,$c)->getValue();
    $status2=$spreadsheet->getActiveSheet()->getCellByColumnAndRow(23,$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.'+0.0001<(G'.$c.'+0.271),G'.$c.'+0.271,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,"NÃO GEROU HORA PARADA","PERDEU AGENDA")),if(K'.$c.'="Aguardando","NA PORTA",if(K'.$c.'="Em Rota","EM TRÂNSITO","")))');}
    if ($status2==""){$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(R'.$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","10. EM TRANSITO"))))))))))))))');}
    $spreadsheet->getActiveSheet()->setCellValue('AE'.$c,'=IF(AD'.$c.'<>"",IFERROR(N'.$c.'*300,""),"")');
    $spreadsheet->getActiveSheet()->setCellValue('AF'.$c,'=IF(AD'.$c.'<>"",if(O'.$c.'<>"","'.$lib1.'",""),"")');

    $spreadsheet->getActiveSheet()->getStyle($range)->getFill()
        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
        ->getStartColor()->setARGB($bg);
  }
$spreadsheet->getActiveSheet()->getStyle('A3:AG3')->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(20);
$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()->getColumnDimension('AD')->setWidth(45);
$spreadsheet->getActiveSheet()->getColumnDimension('AE')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('AF')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('AG')->setWidth(15);
$spreadsheet->getActiveSheet()->getStyle('A1:AG3')
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a1:AG3')
    ->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()->getStyle('AF4:AG'.$lin3)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->mergeCells('U1:AC2');
$spreadsheet->getActiveSheet()->getStyle('A1:AG3')->getAlignment()->setWrapText(true);
$spreadsheet->getActiveSheet()->getStyle('Y3:Z'.$lin3)->getAlignment()->setWrapText(true);
$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>";
?>