<?php 
require_once('xconn.php');
session_start();
 
  if(!isset($_SESSION['d_usuario'])){
    header('Location: index.php');
    exit;
  }else{
    $iUsuario = $_SESSION['c_usuario'];
  }

  if ($conn ->connect_error) {
  die("Connection failed: " . $conn ->connect_error);
  }

?>
<html>
<head>
  <title>Diaso | Brokers</title>
  <!-- Required meta tags -->
  <meta charset="utf-8">
  <meta content="width=device-width, initial-scale=1.0" name="viewport" />
  <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
  <!--     Fonts and icons     -->
  <link rel="stylesheet" type="text/css" href="https://fonts.googleapis.com/css?family=Roboto:300,400,500,700|Roboto+Slab:400,700|Material+Icons" />
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/latest/css/font-awesome.min.css">
  <!-- Material Kit CSS -->
  <link href="assets/css/material-dashboard.css?v=2.1.2" rel="stylesheet" />

</head>

<body>
  <div class="wrapper ">
    <div class="sidebar" data-color="azure" data-background-color="black">
      <div class="logo">
        <a href="https://diaso.com.mx/" class="simple-text logo-mini">
          Brokers -
          Diaso
        </a>
        <img src="img/logo.png" width="200" height="100"/>
      </div>
      <div class="sidebar-wrapper">
        <ul class="nav">
          <li class="nav-item">
            <a class="nav-link" href="dashboard.php">
              <i class="material-icons">dashboard</i>
              <p>Dashboard</p>
            </a>
          </li>
          <li class="nav-item">
            <a class="nav-link" href="clientes.php">
              <i class="material-icons">dashboard</i>
              <p>Clientes</p>
            </a>
          </li>
          <li class="nav-item active">
            <a class="nav-link" href="solicitudes.php">
              <i class="material-icons">dashboard</i>
              <p>Solicitudes</p>
            </a>
          </li>
          <li class="nav-item">
            <a class="nav-link" href="catalogos.php">
              <i class="material-icons">dashboard</i>
              <p>Catalogos</p>
            </a>
          </li>
          <li class="nav-item">
            <a class="nav-link" href="users.php">
              <i class="material-icons">dashboard</i>
              <p>Usuarios</p>
            </a>
          </li>
          
          <li class="nav-item active-pro ">
              <p>Disponible en: Web & Movil</p>
          </li>
          <!-- your sidebar here -->
        </ul>
      </div>
    </div>
    <div class="main-panel">
      <!-- Navbar -->
      <nav class="navbar navbar-expand-lg navbar-transparent navbar-absolute fixed-top ">
        <div class="container-fluid">
          <div class="navbar-wrapper">
            <a class="navbar-brand" href="javascript:;">Dashboard</a>
          </div>
          <button class="navbar-toggler" type="button" data-toggle="collapse" aria-controls="navigation-index" aria-expanded="false" aria-label="Toggle navigation">
            <span class="sr-only">Toggle navigation</span>
            <span class="navbar-toggler-icon icon-bar"></span>
            <span class="navbar-toggler-icon icon-bar"></span>
            <span class="navbar-toggler-icon icon-bar"></span>
          </button>
          <div class="collapse navbar-collapse justify-content-end">
            <ul class="navbar-nav">
              <li class="nav-item">
                <a class="nav-link" href="javascript:;">
                  <i class="material-icons">notifications</i> Notificaciones
                </a>
              </li>
              <li class="nav-item dropdown">
                <a class="nav-link" href="javascript:;" id="navbarDropdownProfile" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">
                  <i class="material-icons">person</i>Hola <?php echo', ' . $_SESSION['d_usuario'] . '.'; ?>
                  <p class="d-lg-none d-md-block">
                    Hola, <?php 'Edgar Mejia'; ?>
                  </p>
                </a>
                <div class="dropdown-menu dropdown-menu-right" aria-labelledby="navbarDropdownProfile">
                  <a class="dropdown-item" href="profile.php">Perfil</a>
                  <a class="dropdown-item" href="configuration.php">Configuracion</a>
                  <div class="dropdown-divider"></div>
                  <a class="dropdown-item" href="xlogout.php">Salir</a>
                </div>
              </li>
              <!-- your navbar here -->
            </ul>
          </div>
        </div>
      </nav>
      
      <!-- End Navbar -->
      <div class="content">
        <div class="container-fluid">
          <div class="row">
            <div class="col-md-12">
              <div class="card">
                <div class="card-header card-header-primary">
                  <h4 class="card-title ">Consulta de Solicitudes</h4>
                  <p class="card-category">Filtro</p>
                </div>
                <div class="card-body">
                  <form method="post" id="fFiltro" action="<?php echo $_SERVER['PHP_SELF']; ?>">
                    <div class="row">
                      <div class="col-md-3">
                        <label class="bmd-label-floating">Filtro Asesor</label>
                        <select class="form-control" name="cbo_asesores">
                            <option value="0">Asesor</option>
                            <?php
                              $result2 = mysqli_query($conn,"SELECT d_nombre, c_usuario FROM t_usuarios WHERE c_tipo = 2");
                              while ($valores = mysqli_fetch_array($result2)) {
                                echo '<option value="' .$valores['c_usuario']. '">' .$valores['d_nombre']. '</option>';
                              }
                              
                            ?>
                        </select>
                      </div>
                       <div class="col-md-3">
                        <label class="bmd-label-floating">Banco</label>
                        <select class="form-control" name="cbo_bancos">
                            <option value="0">Banco</option>
                            <?php
                              $result2 = mysqli_query($conn,"SELECT c_banco, d_banco FROM t_catalogobancos");
                              while ($valores = mysqli_fetch_array($result2)) {
                                echo '<option value="' .$valores['c_banco']. '">' .$valores['d_banco']. '</option>';
                              }
                              
                            ?>
                        </select>
                      </div>
                       <div class="col-md-3">
                        <label class="bmd-label-floating">Estatus</label>
                        <select class="form-control" name="cbo_status">
                            <option value="0">Estatus</option>
                            <option value="1">Alta</option>
                            <option value="2">Autorizacion</option>
                            <option value="3">Avaluos</option>
                            <option value="4">Formalizacion</option>
                            <option value="5">Cancelacion</option>
                        </select>
                      </div>
                       <div class="col-md-3">
                        <label class="bmd-label-floating">Cliente</label>
                        <input type="text" name="txt_nombrecliente" class="form-control">
                      </div>
                    </div>
                    <div class="row">
                      <div class="col-md-2">
                        <label class="bmd-label-floating">Fecha Inicial</label>
                        <input type="date" class="form-control" name="txt_fecha1">
                      </div>
                      <div class="col-md-2">
                        <label class="bmd-label-floating">Fecha Final</label>
                        <input type="date" class="form-control" name="txt_fecha2">
                      </div>
                      <div class="col-md-8">
                         <button type="submit" class="btn btn-primary pull-right" name="btn_filtro">Filtro</button>
                      </div>
                      
                    </div>
                    <div class="row">
                      <div class="col-md-12">
                        <div class="card-body">
                          <div class="table-responsive">
                            <table class="table">
                              <thead class=" text-primary">
                                <th>
                                  Nombre
                                </th>
                                <th>
                                  Banco
                                </th>
                                <th>
                                  Monto
                                </th>
                                <th>
                                  Plazo
                                </th>
                                <th>
                                  Estatus
                                </th>
                                <th>
                                </th>
                              </thead>
                              <tbody>
                                <?php
                                if(isset($_POST['btn_filtro'])){
                                  $sSQL = "SELECT c_solicitudbanco, m_monto, d_plazo, CASE WHEN c_estatus = 0 THEN 'NADA' WHEN c_estatus = 1 THEN 'ALTA' WHEN c_estatus = 2 THEN 'AUTORIZACION' WHEN c_estatus = 3 THEN 'AVALUOS' WHEN c_estatus = 4 THEN 'FORMALIZACION' WHEN c_estatus = 5 THEN 'CANCELACION'END AS c_estatus, t_clientes.d_nombre, t_catalogobancos.d_banco FROM t_solicitudbanco INNER JOIN t_solicitud ON t_solicitud.c_solicitud = t_solicitudbanco.c_solicitud INNER JOIN t_clientes ON t_clientes.c_cliente = t_solicitud.c_cliente INNER JOIN t_catalogobancos ON t_catalogobancos.c_banco = t_solicitudbanco.d_banco INNER JOIN t_usuarios ON t_usuarios.c_usuario = t_solicitudbanco.c_asesor WHERE 1=1";

                                  $asesor = 0;
                                  $banco = 0;
                                  $estatus = 0;
                                  $cliente = '';
                                  $fecha1 = '';
                                  $fecha2 = '';
 
                                  $asesor = $_POST['cbo_asesores'];
                                  $banco = $_POST['cbo_bancos'];
                                  $estatus = $_POST['cbo_status'];
                                  $cliente = $_POST['txt_nombrecliente'];
                                  $fecha1 = $_POST['txt_fecha1'];
                                  $fecha2 = $_POST['txt_fecha2'];

                                  if($asesor <> 0){
                                    $sSQL = $sSQL . ' AND c_asesor = ' . $asesor;
                                  }
                                  if($banco <> 0){
                                    $sSQL = $sSQL . " AND t_catalogobancos.c_banco = $banco";
                                  }
                                  if($estatus <> 0){
                                    $sSQL = $sSQL . " AND c_estatus = $estatus ";
                                  }
                                  if($cliente <> ''){
                                    $sSQL = $sSQL . " AND d_nombre LIKE '%$cliente%'";
                                  }

                                  if($fecha1 <> ''){
                                    $sSQL = $sSQL . " AND d_fecha";
                                  }


                                  $result2 = mysqli_query($conn, $sSQL);


                                  while ($valores = mysqli_fetch_array($result2)) {
                                    echo '<tr>';
                                    echo '<td>' .$valores['d_nombre']. '</td>';
                                    echo '<td>' .$valores['d_banco']. '</td>';
                                    echo '<td>' .$valores['m_monto']. '</td>';
                                    echo '<td>' .$valores['d_plazo']. '</td>';
                                    echo '<td>' .$valores['c_estatus']. '</td>';
                                    echo '<td><a href="detallesolicitud.php?id=' .$valores['c_solicitudbanco']. '" class="nav-link">Detalle de Solicitud</a></td>';
                                    echo '</tr>';
                                  }
                                }else{
                                  $result2 = mysqli_query($conn,"SELECT c_solicitudbanco, m_monto, d_plazo, CASE WHEN c_estatus = 0 THEN 'NADA' WHEN c_estatus = 1 THEN 'ALTA' WHEN c_estatus = 2 THEN 'AUTORIZACION' WHEN c_estatus = 3 THEN 'AVALUOS' WHEN c_estatus = 4 THEN 'FORMALIZACION' WHEN c_estatus = 5 THEN 'CANCELACION'END AS c_estatus, t_clientes.d_nombre, t_catalogobancos.d_banco FROM t_solicitudbanco INNER JOIN t_solicitud ON t_solicitud.c_solicitud = t_solicitudbanco.c_solicitud INNER JOIN t_clientes ON t_clientes.c_cliente = t_solicitud.c_cliente INNER JOIN t_catalogobancos ON t_catalogobancos.c_banco = t_solicitudbanco.d_banco INNER JOIN t_usuarios ON t_usuarios.c_usuario = t_solicitudbanco.c_asesor");
                                  while ($valores = mysqli_fetch_array($result2)) {
                                    echo '<tr>';
                                    echo '<td>' .$valores['d_nombre']. '</td>';
                                    echo '<td>' .$valores['d_banco']. '</td>';
                                    echo '<td>' .$valores['m_monto']. '</td>';
                                    echo '<td>' .$valores['d_plazo']. '</td>';
                                    echo '<td>' .$valores['c_estatus']. '</td>';
                                    echo '<td><a href="detallesolicitud.php?id=' .$valores['c_solicitudbanco']. '" class="nav-link">Detalle de Solicitud</a></td>';
                                    echo '</tr>';
                                  }
                                }
                                ?>
                                
                              </tbody>
                            </table>
                          </div>
                        </div>
                      </div>
                      <div class="col-md-3"></div>
                    </div>
                  </form>
                </div>
              </div>
            </div>
          </div>
        </div>
      </div>
      <footer class="footer">
        <div class="container-fluid">
          <nav class="float-left">
            <ul>
              <li>
                <a href="https://diaso.com.mx/">
                  Brokers
                </a>
              </li>
            </ul>
          </nav>
          <div class="copyright float-right">
            &copy;
            <script>
              document.write(new Date().getFullYear())
            </script>, Diaso.
            <a href="xlogout.php" class="btn btn-primary btn-round">Salir</a>
          </div>
          <!-- your footer here -->
        </div>
      </footer>
    </div>
  </div>

  <script src="assets/js/core/jquery.min.js"></script>
  <script src="assets/js/core/popper.min.js"></script>
  <script src="assets/js/core/bootstrap-material-design.min.js"></script>
  <script src="assets/js/plugins/perfect-scrollbar.jquery.min.js"></script>


</body>
</html>