TransWar File Browser

Path: C:\

<?php
ini_set('max_execution_time',259200); 
ini_set ('memory_limit','-1');
date_default_timezone_set('America/Fortaleza');
$dateadded = date("d/m/Y  --  G:i"); 
$DT=date('d-m-Y_G\hi\m');
$arqcsv="relat_temp.csv";
if (isset($_POST['grupo'])) {$grupo=$_POST['grupo'];$ds_grupo=$_POST['dsgru'];}else{$grupo="nulo";$ds_grupo="TODOS";}
if ($grupo!='nulo'){$where=" AND e.id_GrupoCliente='$grupo' ";}else{$where="";}
if (isset($_POST['di'])){$dia=$_POST['di'];}
$ano=substr($dia, 6);
$mes=substr($dia, 3,-5);
$di=$ano.$mes.substr($dia, 0,-8);
if (isset($_POST['df'])){$dia2=$_POST['df'];}
$ano2=substr($dia2, 6);
$mes2=substr($dia2, 3,-5);
$df=$ano2.$mes2.substr($dia2, 0,-8);
$gr=strtok($ds_grupo, " ");
$arqxlsx="Relat_".$gr."_".$DT.".xlsx";
require_once("../conexao_srvtms.php");
$tsql="SELECT DISTINCT 
    g.ped                                                       as pedido
    ,'/'+ltrim(rtrim(a.[nr_Conhecimento]))                      as cte
    ,convert(varchar(10),a.dt_Coleta,105)                       as embarque
    ,ltrim(rtrim(a.[ds_Remetente]))                             as remetente
    ,ltrim(rtrim(a.[ds_Cliente]))                               as destinatario
    ,ltrim(rtrim(b.ds_Cidade))                                  as cidade
    ,ltrim(rtrim(a.[nr_NotaFiscal]))                            as nota
    ,case
    when d.id_TipoTabelaPreco = '1' then 'LOTAÇÃO'
    when d.id_TipoTabelaPreco = '2' then 'FRACIONADA'
    when d.id_TipoTabelaPreco = '3' then 'IMP/EXP'
    when d.id_TipoTabelaPreco = '4' then 'PADRÃO'
    when d.id_TipoTabelaPreco = '5' then 'AÉREO'
    else '' end                                                 as tipo_frete
    ,a.[vl_NotaFiscal]                                          as valor
    ,case
    when a.dt_Recepcao is null then 'COLETADA'
    else 'ENTREGUE' end                                         as status
    ,h.[hr_ChegadaCliente]                                      as chegada
    ,h.[hr_SaidaCliente]                                        as saida
    ,substring(h.[ds_PeriodoParadoCliente],1,5)                 as parada
FROM [dtbTransporte].[dbo].[tbdMovimento] as a
    left join [dtbTransporte].[dbo].[tbdManifestoMovimento] as h on 
        ((h.id_Movimento=a.id_Movimento and h.id_Ocorrencia='32') or 
        (h.id_PedidoColeta=a.nr_Coleta and h.id_Ocorrencia='32'))  and h.ds_Tipo='ENTREGA'
    left join(
        select [dtbAuxiliar].[dbo].[TiraLetras](g1.[cd_ServicoNotaFiscal])  as ped ,g1.id_Movimento as mov
        FROM(select ROW_NUMBER () OVER (PARTITION BY id_Movimento order by id_Sequencia) as ROW_NUM, *
        from [dtbTransporte].[dbo].[tbdMovimentoNotaFiscal]) as g1 where g1.ROW_NUM='1') as g on g.mov=a.id_Movimento
    left join [dtbTransporte].[dbo].tbdCidade as b on a.id_Cidade=b.id_Cidade
    left join [dtbTransporte].[dbo].[tbdTransportadoraServico] as c on a.id_TransportadoraServico=c.id_TransportadoraServico
    left join [dtbTransporte].[dbo].[tbdTipoTabelaPreco] as d on c.id_TipoTabelaPreco=d.id_TipoTabelaPreco
    left join [dtbTransporte].[dbo].[tbdConfiguracaoCliente]  AS e  ON e.id_Pessoa = a.id_ClienteFaturamento
    left join [dtbTransporte].[dbo].[tbdVeiculo]  AS f  ON f.id_Veiculo = a.id_Veiculo
where  a.id_Movimento>='726853' and ( (a.id_TipoMovimento='1' or a.id_TipoMovimento='4' or a.id_TipoMovimento='112') and (Convert(varchar(8), a.dt_Coleta,112) BETWEEN '$di' and '$df') ";
$tsql=$tsql.$where.") order by embarque, destinatario";
$query=sqlsrv_query($conn, $tsql, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
$q = 0;
while ($res = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC)) 
  { $q++;
    $pedido[$q]         =$res['pedido'];
    $cte[$q]            =$res['cte'];
    $embarque[$q]       =$res['embarque'];
    $remetente[$q]      =str_replace(',', ' ', $res['remetente']);
    $destinatario[$q]   =str_replace(',', ' ', $res['destinatario']);
    $cidade[$q]         =str_replace(',', ' ', $res['cidade']);
    $nota[$q]           =$res['nota'];
    $tipo_frete[$q]     =$res['tipo_frete'];
    $valor[$q]          =$res['valor'];
    $status[$q]         =$res['status'];
    $chegada[$q]        =$res['chegada'];
    $saida[$q]          =$res['saida'];
    $parada[$q]         =$res['parada'];
    if ($status[$q]=='ENTREGUE'){$obs[$q]='FINALIZADO';}else{$obs[$q]='';}
  }
$fptemp = fopen($arqcsv, 'w+');
fwrite($fptemp,'Pedido Cliente,Número CTe,Data de Embarque,Remetente,Destinatário,Cidade Destinatário,Nota Fiscal,Tipo de Frete,Valor N.F.,Status,Hora de Chegada,Hora de Recebimento,Horas Paradas,Taxa TDE,Observação,Aprovação '.$gr.',SH'.PHP_EOL);
for ($d=1; $d<=$q; $d++) 
  { 
    $linha=$pedido[$d].",".$cte[$d].",".$embarque[$d].",".$remetente[$d].",".$destinatario[$d].",".$cidade[$d].",".$nota[$d].",".$tipo_frete[$d].",".$valor[$d].",".$status[$d].",".$chegada[$d].",".$saida[$d].",".$parada[$d].",,".$obs[$d].",".PHP_EOL;
    fwrite($fptemp, $linha);
  }
$lin=$q+1;
fclose($fptemp); 
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
$reader->setDelimiter(',');
$reader->setEnclosure('"');
$reader->setSheetIndex(0);
$spreadsheet = $reader->load($arqcsv);
$writer = new Xlsx($spreadsheet);
$spreadsheet->getActiveSheet()->setTitle($gr);
$spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
$styleArray = [
    'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['argb' => '000000'],
        ],
    ],
];
$format = 'R$* #,##0.00';
$spreadsheet->getActiveSheet()->getStyle('A1:Q'.$lin)->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle('A1:Q1')->getAlignment()->setWrapText(true);
$spreadsheet->getActiveSheet()->getStyle('A1:Q1')->getFont()->setName('Arial');
$spreadsheet->getActiveSheet()->getStyle('A1:Q1')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('I2:I'.$lin)->getNumberFormat()->setFormatCode($format);
$spreadsheet->getActiveSheet()->getStyle('A1:Q1')
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A1:Q1')
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A2:C'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('H2:H'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('J2:N'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('O2:O'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A1:Q1')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('5882FA');
$spreadsheet->getActiveSheet()->getStyle('A1:Q1')
    ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
for ($c=2; $c <= $lin; $c++) 
  { 
    if ($c % 2 == 0) { $bg="d8d8d8";} else {$bg="ffffff";}
    $range='A'.$c.':Q'.$c;
    $range2='K'.$c.':L'.$c;
    $spreadsheet->getActiveSheet()->getStyle($range)->getFill()
        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
        ->getStartColor()->setARGB($bg);
    $spreadsheet->getActiveSheet()->getStyle('k'.$c)
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DDMMYYYY);
    $spreadsheet->getActiveSheet()->getStyle($range2)
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_TIME3);
  }
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(50);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(25);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(16);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(15);

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