<?php
require '../conexao/conexao.php';
// Receba o ID da requisição da consulta
$requisicaoId = $_GET['id'];
$requisicaoId = $_REQUEST['id'];
//$filial = $_GET['filial'] ?? null;
$ano = $_GET['ano'] ?? null;
$mes = $_GET['mes'] ?? null;
//$filial = $_REQUEST['filial'] ?? null;


//$requisicaoId = 5649;
//Consulta Detalhe Requi
$query_detalhe_requi = "SELECT DISTINCT             
req.CDFIL,       
req.NRRQU,
cli.NOMECLI,
SUM(v.VRRCB) as VRRCB,
req.VRSDO,       
req.DTPAG
FROM FC17000 as req 
inner join fc17100 v on req.cdfil = v.cdfil and req.nrrqu = v.nrrqu
LEFT OUTER JOIN FC07000 as cli ON (cli.CDCLI = req.CDCLI)   
WHERE req.NRRQU > 0                             
 AND req.DTENTR >= (CURRENT_DATE - 180) AND req.DTENTR <= (CURRENT_DATE)
and cli.cdcli = " . intval($requisicaoId) . " ";

$query_detalhe_requi .= "GROUP BY req.CDFIL,req.NRRQU,cli.NOMECLI,req.VRSDO,req.DTPAG";
//echo $query_detalhe_varejo;
$result_detalhe_requi = ibase_query($connection, $query_detalhe_requi);
if (!$result_detalhe_requi) {
    die('Erro na consulta: ' . ibase_errmsg());
}


// SELECT DE PRODUTOS DE VAREJO
$query_detalhe_varejo = " SELECT
cpm.CDFIL,
cpm.nrcpm,
cpm.DTSAI,
cpm.CDCLI,
cli.NOMECLI,
cp.CDPRO,
prod.DESCRPRD,
cp.PRUNI
FROM FC14000 as cpm
INNER JOIN FC14100 as cp ON (cp.CDFIL = cpm.CDFIL AND cp.NRCPM = cpm.NRCPM)
INNER JOIN FC03000 as prod ON (prod.CDPRO = cp.CDPRO)
LEFT  JOIN FC07000 as cli ON (cli.CDCLI = cpm.CDCLI) 
WHERE 
 cpm.DTSAI >= (CURRENT_DATE - 180)  AND  cpm.DTSAI <= (CURRENT_DATE)
AND cli.cdcli = " . intval($requisicaoId) . " ";


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

<!-- Tabela de detalhes da requisição -->
<table class="table table-bordered table-hover table-sm">
    <h4>Compras Manipulado</h4>
    <thead class="thead-light">
        <tr>
            <th scope="col">Filial</th>
            <th scope="col">Requisição</th>
            <th scope="col">Data</th>
            <th scope="col">Cliente</th>
            <th scope="col">Saldo</th>
            <th scope="col">Valor</th>

        </tr>
    </thead>
    <tbody>
        <?php while ($row = ibase_fetch_assoc($result_detalhe_requi)) {

            $row['NOMECLI'] = mb_convert_encoding($row['NOMECLI'], 'UTF-8', 'ISO-8859-1');
            $datePag = new DateTime($row['DTPAG']);
            $formattedDatePag = $datePag->format('d/m/Y');
        ?>


            <tr>
                <td><?= htmlspecialchars($row['CDFIL']) ?></td>
                <td><?= htmlspecialchars($row['NRRQU']) ?></td>
                <td style="text-align: center;" data-order="<?= htmlspecialchars($row['DTPAG']) ?>">
                    <?= htmlspecialchars($formattedDatePag) ?>
                </td>
                <td><?= ($row['NOMECLI']) ?></td>
                <td>R$<?= number_format($row['VRSDO'], 2, ',', '.') ?></td>
                <td>R$<?= number_format($row['VRRCB'], 2, ',', '.') ?></td>

            </tr>
        <?php } ?>
    </tbody>
</table>

<!-- Tabela de detalhes varejo -->
<table class="table table-bordered table-hover table-sm">
    <h4>Compras Varejo</h4>
    <thead class="thead-light">
        <tr>
            <th scope="col">Filial</th>
            <th scope="col">Cupom</th>
            <th scope="col">Data</th>
            <th scope="col">Cliente</th>
            <th scope="col">Cod Produto</th>
            <th scope="col">Produto</th>
            <th scope="col">Valor</th>
        </tr>
    </thead>
    <tbody>
        <?php while ($row = ibase_fetch_assoc($result_detalhe_formula)) {
            $row['NOMECLI'] = mb_convert_encoding($row['NOMECLI'], 'UTF-8', 'ISO-8859-1');
            $row['DESCRPRD'] = mb_convert_encoding($row['DESCRPRD'], 'UTF-8', 'ISO-8859-1');

            $dateSai = new DateTime($row['DTSAI']);
            $formattedDateSai = $dateSai->format('d/m/Y');
        ?>
            <tr>
                <td><?= htmlspecialchars($row['CDFIL']) ?></td>
                <td><?= htmlspecialchars($row['NRCPM']) ?></td>
                <td style="text-align: center;" data-order="<?= htmlspecialchars($row['DTSAI']) ?>">
                    <?= htmlspecialchars($formattedDateSai) ?>
                </td>
                <td><?= ($row['NOMECLI']) ?></td>
                <td><?= htmlspecialchars($row['CDPRO']) ?></td>
                <td><?= ($row['DESCRPRD']) ?></td>
                <td>R$<?= number_format($row['PRUNI'], 2, ',', '.') ?></td>

            </tr>
        <?php } ?>
    </tbody>
</table>