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

// REPRESENTANTES
$sql = $bd->getConexao()->prepare('
SELECT
  c.*,
  clubes.clubes_total,
  clubes.clubes_ativos,
  clubes.clubes_inativos
FROM clientes c
      LEFT JOIN (
                  SELECT id_representante,
                    sum(1) as clubes_total,
                    sum(CASE WHEN cancelado = 1 THEN 1 ELSE 0 END) as clubes_inativos,
                    sum(CASE WHEN cancelado = 0 AND (status = 2 or status = 9) THEN 1 ELSE 0 END) as clubes_ativos
                  FROM clubes
                  GROUP BY id_representante
                ) clubes ON clubes.id_representante = c.id
WHERE c.representante = 1
ORDER BY nome ASC');
if($sql->execute()) { $resul_reps = $sql->fetchall(PDO::FETCH_ASSOC);}


?>
<!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>
  <link rel="stylesheet" href="js/checkbox/jquery.checkbox.css" />

  <script type="text/javascript" language="javascript" class="init">
    $(document).ready(function() {

      // Formatação de Tabelas
      $('table').DataTable({

        stateSave: false, // Salvar última pesquisa
        "dom": '<"top"fl>rt<"bottom"ip><"clear">', // Layout
        "pagingType": "full_numbers", // Modo Paginação
        "iDisplayLength": 50,

        "language": { // Tradução
          "lengthMenu": "Resultados por P&aacute;gina: _MENU_ ",
          "zeroRecords": "N&atilde;o existe Registros para esta pesquisa",
          "info": "P&aacute;gina _PAGE_ de _PAGES_",
          "infoEmpty": "N&atilde;o existe Registros para esta pesquisa",
          "infoFiltered": "(Filtrando de um total de _MAX_ Registros)",
          "search": "Localizar Registro: ",
          "paginate": {
            "first": "Primeira",
            "previous": "Anterior",
            "next": "Pr&oacute;xima",
            "last": "&Uacute;ltima"
          }
        }
      });
    });
  </script>

</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">Representantes</h3>

</div>

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

  <div class="col-lg-12">
    <section class="panel">
      <header class="panel-heading">
        <span class="pull-right"><a href="representante.php" class="btn btn-inverse btn-xs">Novo Representante</a></span>
        <div style="margin-top: 5px">Representantes</div>
      </header>
      <div class="table-responsive">
        <table class="table table-striped b-t text-small">
          <thead>
          <tr>
            <th rowspan="2" class="text-center" width="15"></th>
            <th rowspan="2">Nome</th>
            <th rowspan="2" width="150">Celular</th>
            <th rowspan="2" width="200">E-mail</th>
            <th class="text-center" colspan="3">Clubes</th>
          </tr>
          <tr>
            <th class="text-center" width="60">Ativos</th>
            <th class="text-center" width="60">Inativo</th>
            <th class="text-center" width="60">TOTAL</th>
          </tr>
          </thead>
          <tbody>
          <?php foreach($resul_reps as $l){ ?>
            <tr>
              <td>
                <a href="representante.php?r=<?php echo $l['id']?>"><i class="fa fa-folder-open fa-lg" title="Abrir"></i></a>
              </td>
              <td><?php echo $l['nome']?></td>
              <td><?php echo $l['cel']?></td>
              <td><?php echo $l['email']?></td>
              <td class="text-center"><?php echo $l['clubes_ativos']?></td>
              <td class="text-center"><?php echo $l['clubes_inativos']?></td>
              <td class="text-center"><?php echo $l['clubes_total']?></td>
            </tr>
          <?php } ?>
          </tbody>
        </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>

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