<?php include('fncr-geral/configuracao_geral.php');
$pagina = "gameid";
$blq_pag = "gameid_saldo_clubes";
include('fncr-geral/permissoes_restricao.php');

// Funções do Financeiro
$config_fingeral = array( "tipo"=>"financeiro" );
require_once('scripts/financeiro/functions_fingeral.php');

$id_clube = 200;
$id_etapa = 32;

$inicio = "2026-01-22 00:00:00";
$fim = "2026-12-01 00:00:00";

$resul_saldos = sel_fingeral("saldos", [
  "id_conta"=>3,
  "data_inicio"=>$inicio,
  "data_fim"=>$fim,
  "pgto_inicio"=>$inicio,
  "pgto_fim"=>$fim,
  "competencia"=> 0
]);


// Taxas
$sql = $bd->getConexao()->prepare('
    SELECT
      sum(CASE WHEN vp.id_ref = 5 THEN vp.valor ELSE 0 END) as taxa_conv_pix,
      sum(CASE WHEN vp.id_ref = 4 THEN vp.valor ELSE 0 END) as taxa_conv_saldo,
      sum(CASE WHEN vp.id_ref = 7 THEN vp.valor ELSE 0 END) as taxa_conv_time,
      pgto_futuros.taxa_conv as taxa_conv_cartao,
      pgto_futuros.taxa_op as taxa_op_cartao,
      pgto_futuros.taxa_servico as taxa_op_cartao_servico
    FROM api_gameid.jogadores_vendas_pgtos vp
      LEFT JOIN api_gameid.jogadores_vendas v ON v.id = vp.id_venda
      CROSS JOIN (
        SELECT
          ifnull(sum(CASE WHEN r.id_categoria=127 THEN r.valor ELSE 0 END),0) as taxa_servico,
          ifnull(sum(CASE WHEN r.id_categoria=132 THEN r.valor ELSE 0 END),0) as pagamentos,
          ifnull(sum(CASE WHEN r.id_categoria=132 THEN r.gid_taxa_conv ELSE 0 END),0) as taxa_conv,
          ifnull(sum(CASE WHEN r.id_categoria=132 THEN r.gid_taxa_op ELSE 0 END),0) as taxa_op
        FROM fingeral_registros r
        WHERE r.id_conta in(5) and r.excluido = 0 and r.data_lancamento BETWEEN ("'.$inicio.'") AND ("'.$fim.'")
      ) pgto_futuros
    WHERE
      vp.finalizado_data_hora BETWEEN ("'.$inicio.'") AND ("'.$fim.'")
      AND vp.finalizado = 1 AND vp.id_forma_pgto = 0 AND vp.valor > 0 AND v.FNCR_id_clube = '.$id_clube);
if($sql->execute()){$taxas_gameid = $sql->fetch(PDO::FETCH_ASSOC);}

$taxas_gameid_tt =
  $taxas_gameid['taxa_conv_pix']
  + $taxas_gameid['taxa_conv_saldo']
  + $taxas_gameid['taxa_conv_time']
  + $taxas_gameid['taxa_conv_cartao']
  + $taxas_gameid['taxa_op_cartao']
  + $taxas_gameid['taxa_op_cartao_servico'];


// Saldo
$sql = $bd->getConexao()->prepare('
    SELECT
      c.id,
      c.nome,
      IFNULL(valores.credito_clube,0) credito_clube,
      IFNULL(valores.debito_clube,0) debito_clube,
      IFNULL(fncr.valor,0) fncr_valor,
      IFNULL(pgto_futuros.total_taxa_conv,0) total_taxa_conv,
      IFNULL(pgto_futuros.total_taxa_op,0) total_taxa_op,
      IFNULL(pgto_futuros.total_pendente,0) - IFNULL(pgto_futuros.total_taxas,0) total_pendente,
      IFNULL(salve_bolha.vendas, 0) salvebolha_vendas,
      IFNULL(salve_bolha.premio, 0) salvebolha_premios
    FROM clubes c
      LEFT JOIN gameid_clubes_config cf ON cf.id_clube = c.id
      LEFT JOIN (
                  SELECT
                    v.FNCR_id_clube as id_clube,
                    sum(CASE WHEN fpgto.intermediado=1 AND (vp.id_forma_pgto>0 OR vp.id_forma_pgto=0 AND vp.cancelado=1) THEN vp.valor ELSE 0 END) as credito_clube,
                    sum(CASE WHEN fpgto.intermediado=0 AND vp.id_forma_pgto=0 THEN vp.valor ELSE 0 END) as debito_clube
                  FROM api_gameid.jogadores_vendas_pgtos vp
                    LEFT JOIN api_gameid.jogadores_vendas v ON v.id = vp.id_venda
                    LEFT JOIN gameid_formas_pgto fpgto ON fpgto.id = vp.id_forma_pgto OR vp.id_forma_pgto=0 AND fpgto.id = vp.id_ref
                  WHERE vp.finalizado = 1 AND v.FNCR_id_clube>2
                  GROUP BY v.FNCR_id_clube
                ) valores ON valores.id_clube = c.id
      LEFT JOIN (
                  SELECT
                    r.id_clube,
                    sum(r.valor) as valor
                  FROM fingeral_registros r
                  WHERE (r.id_conta = 3 AND r.id_categoria = 123 OR r.id_conta = 2 AND r.id_categoria = 120) AND r.excluido = 0 AND r.id_clube > 2
                  GROUP BY r.id_clube
                ) fncr ON fncr.id_clube = c.id
      LEFT JOIN (
                  SELECT
                    r.id_clube,
                    sum(r.valor) as total_pendente,
                    sum(r.gid_taxa_conv) as total_taxa_conv,
                    sum(r.gid_taxa_op) as total_taxa_op,
                    sum(r.gid_taxa_conv) + sum(r.gid_taxa_op) as total_taxas
                  FROM fingeral_registros r
                  WHERE r.id_conta in(5) AND r.id_categoria in(132) AND r.status_pago = 0 AND r.excluido = 0
                  GROUP BY r.id_clube
                ) pgto_futuros ON pgto_futuros.id_clube = c.id
      LEFT JOIN (
                  SELECT
                    v.FNCR_id_clube,
                    sum(v.valor) as vendas,
                    sum(CASE WHEN v.premiado=1 AND v.creditado=1 THEN t.premio ELSE 0 END) as premio
                  FROM salve_bolha_vendas v
                    LEFT JOIN salve_bolha_torneios t ON t.id = v.FNCR_id_salve_bolha_torneio
                  GROUP BY v.FNCR_id_clube
                ) salve_bolha ON salve_bolha.FNCR_id_clube = c.id
    WHERE c.id = :id_clube');
$sql->bindValue(':id_clube', $id_clube, PDO::PARAM_STR);
if($sql->execute()){$saldos = $sql->fetch(PDO::FETCH_ASSOC);}

$repasse_evento = 0;
if($saldos){
  $repasse_evento = $saldos['credito_clube'] - $saldos['debito_clube'] - $saldos['total_pendente'] + $saldos['fncr_valor'] - $saldos['salvebolha_vendas'] + $saldos['salvebolha_premios'];
}


// Salve bolha
$sql = $bd->getConexao()->prepare('
  SELECT
    sum(v.valor) as total,
    sum(CASE WHEN v.creditado=1 THEN t.premio ELSE 0 END) as premiacao
  FROM salve_bolha_vendas v
    LEFT JOIN salve_bolha_torneios t ON t.id = v.FNCR_id_salve_bolha_torneio
  WHERE
    v.finalizado = 1
    AND cancelado = 0
    AND excluido = 0
    AND v.FNCR_id_clube = :id_clube
    AND v.FNCR_id_salve_bolha_torneio in(select id from salve_bolha_torneios where CLUBE_id_evento = :CLUBE_id_evento)');
$sql->bindValue(':id_clube', $id_clube, PDO::PARAM_STR);
$sql->bindValue(':CLUBE_id_evento', $id_etapa, PDO::PARAM_STR);
//$sql->bindValue(':id_etapa', $id_etapa, PDO::PARAM_STR);
if($sql->execute()){$salve_bolha = $sql->fetch(PDO::FETCH_ASSOC);}

$salve_bolha['saldo'] = $salve_bolha['total'] - $salve_bolha['premiacao'];

?>
<!DOCTYPE html>
<html lang="pt-br">
<head>
    <?php include('fncr-geral/html-head.php');?>

  <link rel="stylesheet" type="text/css" href="js/datatables2/media/css/jquery.dataTables.css">
  <script type="text/javascript" language="javascript" src="js/datatables2/media/js/jquery.dataTables.js"></script>

  <style>
    tr.saldo td{
      font-weight: bold;
      font-size: 14px;
      background-color: #d0e9ff !important;
    }
    table{
      font-size: 14px !important;
    }
  </style>
</head>
<body>

<!-- BARRA SUPERIOR -->
<?php include('barra-sup.php');?>
<!-- MENU PRINCIPAL -->
<?php include('menu.php');?>

<section id="content">

<div class="clearfix padder bg-fncr-clearfix">
<h3 class="m-b">GameID - Clubes</h3>
</div>

<section id="content" class="content-sidebar bg-white" style="padding-bottom: 100px">
<section class="main">
<br>

  <div class="col-lg-3">
    <section class="panel">
      <header class="panel-heading">
        <div class="pull-left">Taxas GameID</div>
      </header>
      <table id="table_registros" class="table table-striped b-t text-small" style="font-size: 11px">
        <tbody>
        <tr>
          <td>Convêniencia (Pix)</td>
          <td class="text-right"><?php echo valorStyle($taxas_gameid['taxa_conv_pix'], 'moeda')?></td>
        </tr>
        <tr>
          <td>Convêniencia (Saldo)</td>
          <td class="text-right"><?php echo valorStyle($taxas_gameid['taxa_conv_saldo'], 'moeda')?></td>
        </tr>
        <tr>
          <td>Convêniencia (Times)</td>
          <td class="text-right"><?php echo valorStyle($taxas_gameid['taxa_conv_time'], 'moeda')?></td>
        </tr>
        <tr class="saldo">
          <td>TAXAS TOTAL</td>
          <td class="text-right"><?php echo valorStyle($taxas_gameid_tt, 'moeda')?></td>
        </tr>
        </tbody>
      </table>
    </section>
  </div>

  <div class="col-lg-3">
    <section class="panel">
      <header class="panel-heading">
        <div class="pull-left">Salve Bolha</div>
      </header>
      <table id="table_registros" class="table table-striped b-t text-small" style="font-size: 11px">
        <tbody>
        <tr>
          <td>Vendas</td>
          <td class="text-right"><?php echo valorStyle($salve_bolha['total'], 'moeda')?></td>
        </tr>
        <tr>
          <td>Premiação</td>
          <td class="text-right"><?php echo valorStyle($salve_bolha['premiacao'], 'moeda')?></td>
        </tr>
        <tr class="saldo">
          <td>SALDO</td>
          <td class="text-right"><?php echo valorStyle($salve_bolha['saldo'], 'moeda')?></td>
        </tr>
        </tbody>
      </table>
    </section>
  </div>

  <div class="col-lg-3">
    <section class="panel">
      <header class="panel-heading" style="padding: 5px 5px 4px 10px">
        <div class="pull-left" style="margin-top: 5px">Conta</div>
      </header>
      <table id="table_registros" class="table table-striped b-t text-small" style="font-size: 12px">
        <tbody>
        <tr>
          <td>Conta Bancária</td>
          <td class="text-right"><?php echo valorStyle($resul_saldos['periodo'], 'moeda')?></td>
        </tr>
        <tr>
          <td>Repasse (evento)</td>
          <td class="text-right"><?php echo valorStyle($repasse_evento*-1, 'moeda')?></td>
        </tr>
        <tr>
          <td>Taxas GameID</td>
          <td class="text-right"><?php echo valorStyle($taxas_gameid_tt, 'moeda')?></td>
        </tr>
        <tr>
          <td>Salve Bolha</td>
          <td class="text-right"><?php echo valorStyle($salve_bolha['saldo'], 'moeda')?></td>
        </tr>
        <tr class="saldo">
          <td>CONFERÊNCIA</td>
          <td class="text-right"><?php echo valorStyle(($resul_saldos['periodo'] - $repasse_evento - $taxas_gameid_tt - $salve_bolha['saldo']), 'moeda')?></td>
        </tr>

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


</section>
</section>
</section>

<!-- FOOTER -->
<?php include('footer.php');?>

<!-- app -->
<script src="js/app.js"></script>
<script src="js/app.plugin.js"></script>
<script src="js/app.data.js"></script>
<!-- Sparkline Chart -->
<script src="js/charts/sparkline/jquery.sparkline.min.js"></script>
<!-- Easy Pie Chart -->
<script src="js/charts/easypiechart/jquery.easy-pie-chart.js"></script>
  <!-- select2 -->
<script src="js/select2/select2.min.js"></script>
  <!-- fuelux -->
<script src="js/fuelux/fuelux.js"></script>
<!-- datepicker -->
<script src="js/datepicker/bootstrap-datepicker.js"></script>
<!-- slider -->
<script src="js/slider/bootstrap-slider.js"></script>
<!-- file input -->
<script src="js/file-input/bootstrap.file-input.js"></script>
<!-- combodate -->
<script src="js/combodate/moment.min.js"></script>
<script src="js/combodate/combodate.js"></script>
<!-- parsley -->
<script src="js/parsley/parsley.min.js"></script>

</body>
</html>
<?php $bd = null; $bd_fn = null;?>