<?php
require '../conexao/conexao.php';

// Receba o ID da requisição da consulta
$requisicaoId = $_GET['id'];
$requisicaoId = $_REQUEST['id'];

//$requisicaoId = 5649;

// Construa a consulta SQL aqui
$query_detalhe_formula = "SELECT
    r.cdfil AS filial,
    r.nrrqu as requisicao,
    etp.DESCRICAO AS descricao_etapa,
    CAST(MIN(CAST(pcp.DATA || ' ' || pcp.hora AS TIMESTAMP)) AS DATE) || ' ' || EXTRACT(HOUR FROM MIN(CAST(pcp.DATA || ' ' || pcp.hora AS TIMESTAMP))) || ':' || LPAD(EXTRACT(MINUTE FROM MIN(CAST(pcp.DATA || ' ' || pcp.hora AS TIMESTAMP))), 2, '0') || ':' || LPAD(EXTRACT(SECOND FROM MIN(CAST(pcp.DATA || ' ' || pcp.hora AS TIMESTAMP))), 2, '0') AS entrada,
    CAST(MAX(CAST(pcp.DATA || ' ' || pcp.hora AS TIMESTAMP)) AS DATE) || ' ' || EXTRACT(HOUR FROM MAX(CAST(pcp.DATA || ' ' || pcp.hora AS TIMESTAMP))) || ':' || LPAD(EXTRACT(MINUTE FROM MAX(CAST(pcp.DATA || ' ' || pcp.hora AS TIMESTAMP))), 2, '0') || ':' || LPAD(EXTRACT(SECOND FROM MAX(CAST(pcp.DATA || ' ' || pcp.hora AS TIMESTAMP))), 2, '0') AS saida,
    -- Calcular a diferença de tempo entre entrada e saída em segundos
    DATEDIFF(MINUTE FROM MIN(CAST(pcp.DATA || ' ' || pcp.hora AS TIMESTAMP)) TO MAX(CAST(pcp.DATA || ' ' || pcp.hora AS TIMESTAMP))) AS tempo_na_etapa
FROM
    fc12500 pcp
INNER JOIN
    fc12100 r ON r.cdfil = pcp.cdfil AND r.nrrqu = pcp.nrrqu AND r.serier = pcp.serier
INNER JOIN
    fc12540 etp ON pcp.cdetapa = etp.cdetapa
INNER JOIN
    fc12530 op ON op.CDOPERA = pcp.CDOPERA
LEFT JOIN
    fc08000 fun ON fun.CDFUN = pcp.CDFUN
WHERE
    r.cdfile = 2 AND
    pcp.nrrqu = " . intval($requisicaoId) . "
     AND
    pcp.serier = '0'
GROUP BY
    r.cdfil,
    r.nrrqu,
    r.serier,
    etp.DESCRICAO order by entrada ASC";


//echo $query_detalhe_formula;
$result_detalhe_formula = ibase_query($connection, $query_detalhe_formula);
if (!$result_detalhe_formula) {
    die('Erro na consulta: ' . ibase_errmsg());
}

function formataDataModal($var) {
	if($var <> '') {

		return(substr($var,8,2).'/'.substr($var,5,2).'/'.substr($var,0,4)); 
	} 

}

?>

<!-- Tabela de detalhes da requisição -->
<table class="table table-bordered table-hover table-sm">
    <thead class="thead-light">
        <tr>
            <th scope="col">Requisição</th>
            <th scope="col">Descrição Etapa</th>
            <th scope="col">Entrada</th>
            <th scope="col">Saída</th>
            <th scope="col">Tempo na Etapa</th>
        </tr>
    </thead>
    <tbody>
        <?php while ($row = ibase_fetch_assoc($result_detalhe_formula)) {
            $tempo_na_etapa = $row['TEMPO_NA_ETAPA'];
            $horas = floor($tempo_na_etapa / 60);
            $minutos = fmod($tempo_na_etapa, 60);
            
            $tempo_formatado = $horas . ' horas ' . $minutos . ' minutos';

            $style = '';
            if (strpos(strtolower($row['DESCRICAO_ETAPA']), 'ent cliente') !== false) {
                $style = 'style="color: blue;"';
            }
            
            ?>
            
         
            <tr>
        <td <?= $style ?>><?= htmlspecialchars($row['REQUISICAO']) ?></td>
        <td <?= $style ?>><?= $row['DESCRICAO_ETAPA'] ?></td>
        <td <?= $style ?>><?= formataDataModal($row['ENTRADA']). ' '. substr($row['ENTRADA'],10,9); ?></td>
        <td <?= $style ?>><?= formataDataModal($row['SAIDA']). ' '. substr($row['SAIDA'],10,9); ?></td>
        <td <?= $style ?>><?= $tempo_formatado ?></td>
    </tr>
        <?php } ?>
    </tbody>
</table>
