<?php

// Configurações de conexão
$hostname = "localhost";
$database = "C:/Manda_Receita/alterdb.ib";
$username = "sysdba";
$password = "masterkey";

// Conectando ao banco de dados Firebird
$connection = ibase_connect($hostname . ":" . $database, $username, $password, 'UTF8');

if (!$connection) {
    die('Erro na conexão: ' . ibase_errmsg());
}  

 $qry_fun = "SELECT DISTINCT fun.nomefun FROM fc08000 fun";
 $qryfun = ibase_query ($connection, $qry_fun);
  $count5 = 0;
  while ($row5[$count5] = ibase_fetch_assoc($qryfun))
      $count5++;

//Venda Vendedores Manipulado
  $qry_VenManiVendedor = " SELECT fr.CDFUNRE as CDFUN,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  CDFUN,fun.nomefun";
  $qryVenManiVendedor = ibase_query ($connection, $qry_VenManiVendedor);
  $count2 = 0;
  while ($row2[$count2] = ibase_fetch_assoc($qryVenManiVendedor))
      $count2++;
	  
  //Venda Vendedores Marca Própria  
  $qry_VenMarcaVendedor = "select fun.nomefun, SUM(p.vrtot - p.vrdsc - p.vrdscv + p.vrtxav) as soma  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
                           order by fun.nomefun";
  $qryVenMarcaVendedor = ibase_query ($connection, $qry_VenMarcaVendedor);
  $count3 = 0;
  while ($row3[$count3] = ibase_fetch_assoc($qryVenMarcaVendedor))
      $count3++;						 
  
  //Venda Vendedores Revenda  
  $qry_VenRevendaVendedor = "select fun.nomefun, SUM(p.vrtot - p.vrdsc - p.vrdscv + p.vrtxav) as soma  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.cdfil in (2, 4) and
						   cp.cdfil = fun.cdcon and
                           cp.cdfunre = fun.cdfun
                           group by fun.nomefun
                           order by fun.nomefun";
   $qryVenRevendaVendedor = ibase_query ($connection,  $qry_VenRevendaVendedor);
  $count4 = 0;
  while ($row4[$count4] = ibase_fetch_assoc($qryVenRevendaVendedor))
      $count4++;
  ?>
  
 <table width="619" height="92" border="0">
    <tr>
        <td width="98"><div align="right"><strong>Vendedor</strong></div></td>
        <td width="98"><div align="right"><strong>Manipulado</strong></div></td>
        <td width="101"><div align="right"><strong>Revenda</strong></div></td>
        <td width="138"><div align="right"><strong>Marca Própria</strong></div></td>
    </tr>

    <?php
    $i = 0;
    while ($i <= $count2 - 1) { //laço tabelas
        $m = 0;
        $r = 0;
        $valormarca = 0;
        $valorrevenda = 0;

        // Inicializando as variáveis para garantir que tenham valor
        $row2_nomefun = !empty($row2[$i]["NOMEFUN"]) ? $row2[$i]["NOMEFUN"] : '';
        $row3_nomefun = !empty($row3[$i]["NOMEFUN"]) ? $row3[$i]["NOMEFUN"] : '';
        $row4_nomefun = !empty($row4[$i]["NOMEFUN"]) ? $row4[$i]["NOMEFUN"] : '';

        //Laço da Revenda
        while ($r <= $count2 + 10) {
            if ($r == $count4) {
                break;
            }
            if ($row2_nomefun == $row4[$r]["NOMEFUN"]) {
                $valorrevenda = $row4[$r]["SOMA"];
            }
            $r++;
        }

        //Laço do marcapropria
        while ($m <= $count2 + 10) {
            if ($m == $count3) {
                break;
            }
            if ($row2_nomefun == $row3[$m]["NOMEFUN"]) {
                $valormarca = $row3[$m]["SOMA"];
            }
            $m++;
        }

        echo "<tr>";
        echo "<td>";

        if (!empty($row2_nomefun)) {
            echo $row2_nomefun;
        } elseif (!empty($row3_nomefun)) {
            echo $row3_nomefun;
        } elseif (!empty($row4_nomefun)) {
            echo $row4_nomefun;
        } else {
            echo "Nenhum valor disponível";
        }

        echo "</td>";
        echo "<td><div align=\"right\"><font size=\"2\">" . number_format($row2[$i]["SOMA"], 2, ',', '.') . "</font></div></td>";
        echo "<td><div align=\"right\"><font size=\"2\">" . number_format($valorrevenda, 2, ',', '.') . "</font></div></td>";
        echo "<td><div align=\"right\"><font size=\"2\">" . number_format($valormarca, 2, ',', '.') . "</font></div></td>";
        echo "</tr>";
        $i++;
    }
    ?>

</table>

