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

$ano = $_GET['ano'] ?? null;
$mes = $_GET['mes'] ?? null;
$filial = $_GET['filial'] ?? null;

$sql_grid_medicos = "SELECT
m.nomemed,
COALESCE(MIN(par.parametro), 'Sem Especialidade') AS parametro,
SUM(CASE WHEN o.qtaprov = 1 THEN o.prreal - o.vrdsc ELSE 0 END) AS total_venda_aprovado,
COUNT(CASE WHEN o.qtaprov = 1 THEN 1 END) AS quantidade_aprovado,
ROUND(100 * SUM(CASE WHEN o.qtaprov = 1 THEN o.prreal - o.vrdsc ELSE 0 END) / NULLIF(SUM(o.prreal - o.vrdsc), 0), 2) AS porcentagem_aprovado,
SUM(CASE WHEN o.qtaprov = 0 THEN o.prreal - o.vrdsc ELSE 0 END) AS total_venda_reprovado,
REPLACE(CAST(CAST((SUM(CASE WHEN o.qtaprov = 1 THEN o.prreal - o.vrdsc ELSE 0 END) / NULLIF(COUNT(CASE WHEN o.qtaprov = 1 THEN 1 END), 0)) AS DECIMAL(10, 2)) AS VARCHAR(20)), '.', ',') AS ticket_medio_aprovado,
COUNT(CASE WHEN o.qtaprov = 0 THEN 1 END) AS quantidade_reprovado,
ROUND(100 * SUM(CASE WHEN o.qtaprov = 0 THEN o.prreal - o.vrdsc ELSE 0 END) / NULLIF(SUM(o.prreal - o.vrdsc), 0), 2) AS porcentagem_reprovado
FROM
fc15100 o
INNER JOIN
FC04000 m ON o.nrcrm = m.nrcrm AND o.ufcrm = m.ufcrm AND o.pfcrm = m.pfcrm
LEFT JOIN
fc04300 esp ON esp.nrcrm = m.nrcrm AND esp.ufcrm = m.ufcrm AND esp.pfcrm = m.pfcrm
LEFT JOIN
fc99999 par ON par.subargum = esp.cdesp AND par.cdpar = '14' AND par.argumento = 'ESPEC'
WHERE
(o.qtaprov = 0 OR o.qtaprov = 1)";

if ($filial && $filial != "Todos") {
    $sql_grid_medicos .= " AND o.CDFIL in (" . ($filial). ")";
}

if ($ano && $ano != "Todos") {
    $sql_grid_medicos .= " AND EXTRACT(YEAR FROM dtentr) = " . intval($ano);
}

if ($mes && $mes != "Todos") {
    $sql_grid_medicos .= " AND EXTRACT(MONTH FROM dtentr) = " . intval($mes);
}

$sql_grid_medicos .= " GROUP BY  m.nomemed ORDER BY total_venda_aprovado DESC ";

$result_grid_medicos = ibase_query($connection, $sql_grid_medicos);

if (!$result_grid_medicos) {
    die('Erro na consulta '.$sql_grid_medicos . ' Firebird/InterBase: ' . ibase_errmsg());
}
?>


    <div class="row">
        <div class="col-12">
            <table id="minhaTabela" class="table table-bordered table-hover table-sm" style="width:100%;">
                <thead class="thead-light">
                    <tr>
                        <th scope="col">Médico</th>
                        <th escope="col">Especialidade</th>
                        <th scope="col">Aprovado</th>
                        <th scope="col" style="text-align: center;">%</th>
                        <th scope="col">Qtd</th>
                        <th scope="col">Ticket Médio</th>
                        <th scope="col">Reprovado</th>
                        <th scope="col" style="text-align: center;">%</th>
                        <th scope="col">Qtd</th>
                        <th scope="col">Total Geral</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    $totalVendaAprovadoGeral = 0;
                    $quantidadeAprovadoGeral = 0;
                    $totalVendaReprovadoGeral = 0;
                    $quantidadeReprovadoGeral = 0;

                    while ($rowgridmedicos = ibase_fetch_assoc($result_grid_medicos)) {
                        echo '<tr>';
                        echo '<th scope="row">' . $rowgridmedicos['NOMEMED'] . '</th>';
                        echo '<th scope="row">' . $rowgridmedicos['PARAMETRO'] . '</th>';
                        echo '<td>R$ ' . $rowgridmedicos['TOTAL_VENDA_APROVADO'] . ' </td>';
                        echo '<td><span class="badge badge-success" style="font-size:0.92em;">' . number_format($rowgridmedicos['PORCENTAGEM_APROVADO'], 2, ',', '.') . '%</span></td>';
                        echo '<td>' . $rowgridmedicos['QUANTIDADE_APROVADO'] . '</td>';
                        echo '<td>R$' . $rowgridmedicos['TICKET_MEDIO_APROVADO'] . '</td>';
                        echo '<td>R$ ' . $rowgridmedicos['TOTAL_VENDA_REPROVADO']. ' </td>';
                        echo '<td><span class="badge badge-danger" style="font-size:0.92em;">' . number_format($rowgridmedicos['PORCENTAGEM_REPROVADO'], 2, ',', '.') . '%</span></td>';
                        echo '<td>' . $rowgridmedicos['QUANTIDADE_REPROVADO'] . '</td>';

                        $totalGeralLinha = $rowgridmedicos['TOTAL_VENDA_APROVADO'] + $rowgridmedicos['TOTAL_VENDA_REPROVADO'];
                       // echo '<td>' . number_format($totalGeralLinha, 2, ',', '.') . '</td>';
                       echo '<td>R$' . $totalGeralLinha.'</td>';
                        echo '</tr>';
                    }
                    ?>
                </tbody>
            </table>
        </div>
    </div>


<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.11.3/js/dataTables.bootstrap5.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.11.3/css/dataTables.bootstrap5.min.css">

<script>
$(document).ready(function() {
    $('#minhaTabela').DataTable({
        "language": {
            "url": "//cdn.datatables.net/plug-ins/1.10.21/i18n/Portuguese-Brasil.json"
        },
        "order": [[2, "desc"]],
        "ordering": true
    });
});
</script>
