<?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)) {
                    $rowgridmedicos['NOMEMED'] = mb_convert_encoding($rowgridmedicos['NOMEMED'], 'UTF-8', 'ISO-8859-1');

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

                    $totalGeralLinha = floatval($rowgridmedicos['TOTAL_VENDA_APROVADO']) + floatval($rowgridmedicos['TOTAL_VENDA_REPROVADO']);
                    echo '<td data-order="' . $totalGeralLinha . '" >R$ ' . number_format($totalGeralLinha, 2, ',', '.') . '</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() {
        $.fn.dataTable.ext.type.order['currency-br-pre'] = function(d) {
            // Converte a string monetária formatada em um número float para ordenação correta
            return parseFloat(d.replace(/[^\d,-]/g, '').replace(',', '.'));
        };
        $('#minhaTabela').DataTable({
            "language": {
                "url": "https://cdn.datatables.net/plug-ins/1.10.21/i18n/Portuguese-Brasil.json"
            },
            "columnDefs": [{
                "type": "currency-br", 
                "targets": [2] 
            }],
            "order": [
                [2, "desc"]
            ],
            "ordering": true
        });
    });
</script>