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


// Lista de Clubes
$sql = $bd->getConexao()->prepare('
SELECT
  clubes.*,
  PERIOD_DIFF(DATE_FORMAT(clubes.data_cadastro,"%Y%m"),DATE_FORMAT(now(),"%Y%m"))*-1 as meses,
  DATE_FORMAT(clubes.data_cadastro,"%d/%m/%Y") AS ativacao,
  config.bd,
  mensalidade.valor as valor
FROM clubes
  LEFT JOIN clubes_configuracao config ON config.id_clube = clubes.id
  LEFT JOIN (
    SELECT
      id_clube,
      valor
    FROM clubes_financeiro
    WHERE periodo_ano = 20 AND periodo_mes = 3
    GROUP BY id_clube
  ) mensalidade ON mensalidade.id_clube = clubes.id
WHERE status = 2 AND config.plano_co = 0
ORDER BY nome ASC');
if($sql->execute()) { $clubes = $sql->fetchall(PDO::FETCH_ASSOC);}


$data_ini = "2020-04-01 00:00:00";
$data_end = "2020-04-30 23:59:59";

$resultado_final = array();

$i=0;
// Percorrendo todos os clubes
foreach($clubes as $clube){ $i++;

  $bdclube = new ConeccaoClube($clube['bd']);

  $sql = $bdclube->getConexao()->prepare('
  SELECT
    "torneio" as tipo,
    DATE_FORMAT(evento_data,"%d") AS dia,
    count(DISTINCT id) as qtd
  FROM torneios
  WHERE evento_data BETWEEN (:data_ini) AND (:data_end)
  GROUP BY dia
  
  UNION
  SELECT
    "cash" as tipo,
    DATE_FORMAT(data_hora_abertura,"%d") AS dia,
    count(DISTINCT id) as qtd
  FROM cash
  WHERE data_hora_abertura BETWEEN (:data_ini) AND (:data_end)
  GROUP BY dia
  
  UNION
  SELECT
    "bar" as tipo,
    DATE_FORMAT(data_hora_abertura,"%d") AS dia,
    count(DISTINCT id) as qtd
  FROM bar
  WHERE data_hora_abertura BETWEEN (:data_ini) AND (:data_end)
  GROUP BY dia
  
  UNION
  SELECT
    "login" as tipo,
    DATE_FORMAT(data_hora,"%d") AS dia,
    count(DISTINCT id) as qtd
  FROM usuarios_acessos
  WHERE data_hora BETWEEN (:data_ini) AND (:data_end)
  GROUP BY dia');
  $sql->bindValue(':data_ini', $data_ini, PDO::PARAM_STR);
  $sql->bindValue(':data_end', $data_end, PDO::PARAM_STR);
  if( $sql->execute() ) { $dados = $sql->fetchall(PDO::FETCH_ASSOC);}

  // Dados
  $dds_clube = array();
  foreach($dados as $l){
    // Torneio
    if($l['tipo']=="torneio"){ $dds_clube['torneio'][$l['dia']] = $l['qtd']; }
    // Cash
    if($l['tipo']=="cash"){ $dds_clube['cash'][$l['dia']] = $l['qtd']; }
    // Bar
    if($l['tipo']=="bar"){ $dds_clube['bar'][$l['dia']] = $l['qtd']; }
    // Login
    if($l['tipo']=="login"){ $dds_clube['login'][$l['dia']] = $l['qtd']; }
  }

  $dds_clube['clube'] = $clube['nome'];
  $dds_clube['valor'] = $clube['valor'];
  $dds_clube['meses'] = $clube['meses'];
  $dds_clube['cidade'] = $clube['cidade'];
  $dds_clube['uf'] = $clube['uf'];

  array_push($resultado_final, $dds_clube);

}


?>
<!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>
    .qd_dia{
      padding: 4px 2px !important;
      text-align: center !important;
      width: 40px !important;
    }
  </style>

  <script type="text/javascript" language="javascript" class="init">
      $(document).ready(function() {
          $("td").each(function(){
            v = $(this).html();
            if(v==0){$(this).css('background-color', '#ffebe6').css('color', '#8c0001')}
          });
      });
  </script>
</head>
<body>

<section id="content">

<div class="clearfix padder bg-fncr-clearfix">
  <h3 class="m-b">Clubes - Utilização do Sistema</h3>
</div>

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

  <div class="col-lg-12">
    <section class="panel">
      <header class="panel-heading bg-info">
        Utilização
      </header>
      <div class="table-responsive">
        <table class="table table-striped b-t text-small">
          <?php foreach ($resultado_final as $l){ ?>
          <thead>
          <tr>
            <th>Clube</th>
            <th>Módulo</th>
            <?php for($i=1;$i<=31;$i++){ echo '<th class="qd_dia">'.$i.'</th>'; }?>
            <th class="text-center">Valor</th>
            <th class="text-center">Meses</th>
            <th class="text-center">Cidade</th>
            <th class="text-center">UF</th>
          </tr>
          </thead>
          <tbody>
            <tr>
              <td rowspan="4" style="padding: 4px 10px;border-bottom: solid 2px #000"><?php echo $l['clube']?></td>
              <td style="padding: 4px 10px">Torneios</td>
              <?php
                for($i=1;$i<=31;$i++){
                  $dia = $i<10 ? "0".$i : $i;
                  $qtd = isset($l['torneio'][$dia]) ? $l['torneio'][$dia] : 0;
                  echo '<td class="qd_dia">'.$qtd.'</td>';
                }
              ?>
              <td class="text-center" rowspan="4" style="padding: 4px 10px;border-bottom: solid 2px #000"><?php echo valorStyle($l['valor'], "moeda", "R$")?></td>
              <td class="text-center" rowspan="4" style="padding: 4px 10px;border-bottom: solid 2px #000"><?php echo $l['meses']?></td>
              <td class="text-center" rowspan="4" style="padding: 4px 10px;border-bottom: solid 2px #000"><?php echo $l['cidade']?></td>
              <td class="text-center" rowspan="4" style="padding: 4px 10px;border-bottom: solid 2px #000"><?php echo $l['uf']?></td>
            </tr>
            <tr>
              <td style="padding: 4px 10px">Cash</td>
              <?php
                for($i=1;$i<=31;$i++){
                  $dia = $i<10 ? "0".$i : $i;
                  $qtd = isset($l['cash'][$dia]) ? $l['cash'][$dia] : 0;
                  echo '<td class="qd_dia">'.$qtd.'</td>';
                }
              ?>
            </tr>
            <tr>
              <td style="padding: 4px 10px">Bar</td>
              <?php
                for($i=1;$i<=31;$i++){
                  $dia = $i<10 ? "0".$i : $i;
                  $qtd = isset($l['bar'][$dia]) ? $l['bar'][$dia] : 0;
                  echo '<td class="qd_dia">'.$qtd.'</td>';
                }
              ?>
            </tr>
            <tr style="border-bottom: solid 2px #000">
              <td style="padding: 4px 10px">Login</td>
              <?php
                for($i=1;$i<=31;$i++){
                  $dia = $i<10 ? "0".$i : $i;
                  $qtd = isset($l['login'][$dia]) ? $l['login'][$dia] : 0;
                  echo '<td class="qd_dia">'.$qtd.'</td>';
                }
              ?>
            </tr>
          </tbody>
          <?php } ?>
        </table>
      </div>
    </section>
  </div>

</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;?>