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


					$qry_manipulado = "SELECT fr.CDFUNRE,fun.nomefun, 

									 sum(fr.PRCOBR - fr.VRDSC) AS soma

									 FROM FC12100 fr 
									  LEFT join fc08000 fun on ( fun.cdcon = fr.cdconre and fun.cdfun = fr.cdfunre ) 
									  WHERE
									 fr.cdfil IN  (2,4 ) AND
									 fr.DTENTR BETWEEN '2023-10-01' AND '2023-10-31'
									 group by fr.CDFUNRE,fun.nomefun";

    $qry_revenda = "select fun.nomefun,cp.cdfunre, SUM(p.vrtot - p.vrdsc - p.vrdscv + p.vrtxav) as revenda_total  from fc14100 p, fc14000 cp, FC03000 pr, fc08000 fun
                           where p.cdfil = cp.cdfil and
                           p.nrcpm = cp.nrcpm and
                           p.cdpro = pr.cdpro and
                            EXTRACT(YEAR FROM cp.dtpagefe) = 2023
						   AND EXTRACT(MONTH FROM cp.dtpagefe) = 10 
                           AND pr.setor not in (276,330) and
                           cp.cdconre in (2, 4) and
						   cp.cdfil = fun.cdcon and
                           cp.cdfunre = fun.cdfun
                           group by fun.nomefun,cp.cdfunre
                           order by fun.nomefun";

    $qry_marca = "select fun.nomefun,cp.cdfunre, SUM(p.vrtot - p.vrdsc - p.vrdscv + p.vrtxav) as marca_propria_total  from fc14100 p, fc14000 cp, FC03000 pr, fc08000 fun
                           where p.cdfil = cp.cdfil and
                           p.nrcpm = cp.nrcpm and
                           p.cdpro = pr.cdpro and
                            EXTRACT(YEAR FROM cp.dtpagefe) = 2023
						   AND EXTRACT(MONTH FROM cp.dtpagefe) = 10 
                           AND pr.setor in (276,330) and
                           cp.cdconre in (2, 4) and
						   cp.cdfil = fun.cdcon and
                           cp.cdfunre = fun.cdfun
                           group by fun.nomefun,cp.cdfunre
                           order by fun.nomefun";

    $qryResult = ibase_query($connection, $qry_manipulado);
    if (!$qryResult) {
        die("Erro na consulta SQL (Manipulado): " . ibase_errmsg());
    }

    $qryResultRevenda = ibase_query($connection, $qry_revenda);
    if (!$qryResultRevenda) {
        die("Erro na consulta SQL (Revenda): " . ibase_errmsg());
    }

    $qryResultMarca = ibase_query($connection, $qry_marca);
    if (!$qryResultMarca) {
        die("Erro na consulta SQL (Marca Própria): " . ibase_errmsg());
    }

?>

<!DOCTYPE html>
<html lang="pt-br">

<body>

                        <div class="col-12">
                            <div style="display: flex; justify-content: space-between; align-items: center;">
                                <h6>Faturamento por Vendedor</h6>
                            </div>

                            <table id="datatable-buttons" class="table table-striped table-bordered dt-responsive nowrap">
                                <thead>
                                    <tr class="text-center">
                                        <th>Vendedor</th>
										<th>Código</th>
                                        <th>Manipulado</th>
                                        <th>Revenda</th>
                                        <th>Marca Própria</th>
                                        <th>Total</th>
                                    </tr>
                                </thead>
                                <tbody>

                                    <?php
                                    // Criar arrays associativos para armazenar os resultados
// Criar arrays associativos para armazenar os resultados
$dataManipulado = [];
$dataRevenda = [];
$dataMarca = [];
$nomesVendedores = []; // Novo array para armazenar nomes dos vendedores

// Buscar dados de manipulado
while ($row = ibase_fetch_assoc($qryResult)) {
    $dataManipulado[$row['CDFUNRE']] = $row['SOMA'];
    $nomesVendedores[$row['CDFUNRE']] = $row['NOMEFUN']; // Adiciona o nome do vendedor
}

// Buscar dados de revenda
while ($row = ibase_fetch_assoc($qryResultRevenda)) {
    $dataRevenda[$row['CDFUNRE']] = $row['REVENDA_TOTAL'];

    // Adicionar vendedor se não estiver na lista de manipulado
    if (!isset($nomesVendedores[$row['CDFUNRE']])) {
        $nomesVendedores[$row['CDFUNRE']] = $row['NOMEFUN'];
        $dataManipulado[$row['CDFUNRE']] = 0; // Valor zerado para manipulado
    }
}

// Buscar dados de marca própria
while ($row = ibase_fetch_assoc($qryResultMarca)) {
    $dataMarca[$row['CDFUNRE']] = $row['MARCA_PROPRIA_TOTAL'];

    // Adicionar vendedor se não estiver na lista de manipulado
    if (!isset($nomesVendedores[$row['CDFUNRE']])) {
        $nomesVendedores[$row['CDFUNRE']] = $row['NOMEFUN'];
        $dataManipulado[$row['CDFUNRE']] = 0; // Valor zerado para manipulado
    }
}

// Inicializar totais
$totalManipulado = 0;
$totalRevenda = 0;
$totalMarca = 0;

// Calcular totais e gerar linhas da tabela
foreach ($dataManipulado as $cdfunre => $manipulado) {
    $revenda = $dataRevenda[$cdfunre] ?? 0;
    $marca_propria = $dataMarca[$cdfunre] ?? 0;
    $total = $manipulado + $revenda + $marca_propria;
    $nomefun = $nomesVendedores[$cdfunre] ?? ''; // Obtém o nome do vendedor

    $totalManipulado += $manipulado;
    $totalRevenda += $revenda;
    $totalMarca += $marca_propria;
?>

<tr class='text-center'>
    <td width='87'><?php echo $nomefun; ?></td>
    <td width='87'><?php echo htmlspecialchars($cdfunre); ?></td>
    <td width='89'><div align='center'>R$ <?php echo htmlspecialchars(number_format($manipulado, 2, ',', '.')); ?></div></td>
    <td width='89'><div align='center'>R$ <?php echo htmlspecialchars(number_format($revenda, 2, ',', '.')); ?></div></td>
    <td width='89'><div align='center'>R$ <?php echo htmlspecialchars(number_format($marca_propria, 2, ',', '.')); ?></div></td>
    <td width='89'><div align='center'>R$ <?php echo htmlspecialchars(number_format($total, 2, ',', '.')); ?></div></td>
</tr>

<?php
}
?>

<tr class='text-center'>
    <td width='87'><div align='center'><b>Total</b></div></td>
	<td></td>
    <td width='89'><div align='center'><b>R$ <?php echo htmlspecialchars(number_format($totalManipulado, 2, ',', '.')); ?></b></div></td>
    <td width='89'><div align='center'><b>R$ <?php echo htmlspecialchars(number_format($totalRevenda, 2, ',', '.')); ?></b></div></td>
    <td width='89'><div align='center'><b>R$ <?php echo htmlspecialchars(number_format($totalMarca, 2, ',', '.')); ?></b></div></td>
    <td width='89'><div align='center'><b>R$ <?php echo htmlspecialchars(number_format($totalManipulado + $totalRevenda + $totalMarca, 2, ',', '.')); ?></b></div></td>
</tr>


                                </tbody>
                            </table>
                        </div>

                   
                </div>
            </div>

        </div> <!-- end container -->
    </div>

   


</body>

</html>
