<?php
include "conn.php";

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Make MySQLi throw exceptions.

        try {
            // Try Connect to the DB with new MySqli object - Params {hostname, userid, password, dbname}
            $mysqli = new mysqli($host, $user, $pass, $base);

            if($_GET["gen"]=='dama'||$_GET["gen"]=='caballero') {
              $tipo1 = " and c.genero='".$_GET["gen"]."'";
            } else {
              $tipo1 = ""; 
            }

              if($_GET["tie"]=="5") {
                $tipo2 = " AND c.suc in ('011 POL','014 SAT','045 TEZ','048 NEZ','057 COS') ";
                $tienda = "Solo 5 tiendas";
              } else{
                $tipo2 = "";
                $tienda = "Todas las tiendas";
              }

              if($_GET["cds"]=="si") {

                if($tipo2 == ""){
                  $tipo3 = "";
                } else {
                  $tipo3 = "";
                  $tipo2 = " AND c.suc in ('011 POL','014 SAT','045 TEZ','048 NEZ','057 COS','00 CDS') ";
                }                
                
                $tienda = $tienda." y Cedis ";

              } else {

                $tipo3 = " AND c.suc not in ('00 CDS')";

              }
              
              if($_GET["gen"]=='dama') {
                $titulo = "DAMA";
            } elseif($_GET["gen"]=='caballero') {
                $titulo = "CABALLERO";
            } else { 
                $titulo = "DAMA Y CABALLERO";
              }

              //$titulo = "Totales generales y por tienda";
              $subtitulo = "Artículos entallados que debería tener cada tienda";

//echo $tipo1.$tipo2.$tipo3;
            // Select all records from the db. Keep inside try block incase there an issue with sql query
            $query = "SELECT c.linea AS tienda,c.genero,CONCAT('$',FORMAT(SUM(ROUND(a.mntovta)),0)) AS venta_pesos
            ,SUM(ROUND(100*porc_vta,1)) AS porc_vta
            ,FORMAT(SUM(ROUND(a.piezas)),0) AS venta_piezas
            ,SUM(ROUND(100*porc_pza,1)) AS porc_pza
            ,ROUND(SUM(a.porc_vta)*SUM(meta_piso)) AS entallados_venta
            ,CONCAT(format(SUM(c.articulos_entallados),0),' --> ',ROUND(100*SUM(c.articulos_entallados)/(SUM(c.articulos_entallados)+SUM(c.articulos_picos))),'%') AS entallados
            ,CONCAT(format(SUM(c.articulos_picos),0),' --> ',ROUND(100*SUM(c.articulos_picos)/(SUM(c.articulos_entallados)+SUM(c.articulos_picos))),'%') AS picos
            ,SUM(c.articulos_entallados)+SUM(c.articulos_picos) as tot_lotes
            ,FORMAT(SUM(b.meta_piso),0) AS meta_piso
            ,FORMAT(SUM(b.meta_bodega),0) AS meta_bodega
            ,FORMAT(SUM(b.meta_piso)+ifnull(SUM(b.meta_bodega),0)+ifnull(SUM(b.meta_cedis_calc),0),0) as meta_total
              ,( SUM(b.meta_piso)+ifnull(SUM(b.meta_bodega),0)+ifnull(SUM(b.meta_cedis_calc),0) ) - SUM(c.articulos_entallados) AS compra_lotes
              ,( SUM(c.articulos_entallados) / ( SUM(b.meta_piso)+ifnull(SUM(b.meta_bodega),0)+ifnull(SUM(b.meta_cedis_calc),0) ) ) as nivel_servicio
              ,round( 100*SUM(c.articulos_entallados) / ( SUM(b.meta_piso)+ifnull(SUM(b.meta_bodega),0)+ifnull(SUM(b.meta_cedis_calc),0) ) ) as nivel_servicio_porc
            ,FORMAT(SUM(c.tot_existencia),0) AS existencia
            ,FORMAT(SUM(c.tot_transito),0) AS transito
            ,FORMAT(SUM(c.tot_transito)+SUM(c.tot_existencia),0) AS total
            ,sum(b.meta_cedis_calc) as meta_cedis_calc
            FROM ventas_gen a RIGHT JOIN tienda_entallado c
            ON a.tienda=c.suc AND a.genero=c.genero AND a.linea=c.linea
            AND a.periodo = DATE_FORMAT(CURDATE(),'%Y%m') 
            LEFT JOIN metas2 b ON b.tienda=c.suc AND b.genero=c.genero AND b.linea=c.linea
            WHERE c.linea<>'' and c.fecha=CURDATE() ".$tipo1.$tipo2.$tipo3." 
            GROUP BY 1,2";
            
            //echo "tipo3=".$tipo3;
            //die ($query);
          
            if ($result = $mysqli->query($query)) {

                /* fetch associative array */
                while ($row = $result->fetch_assoc()) {
                    
                    if($tienda==''){
                      $tienda = $row["sucursal"];
                    }
                    
                    $tabla = $tabla . '<tr>
                    <td><a href="index_ent_det_cat_map_niv.php?tienda='.$row["tienda"].'&gen='.$row["genero"].'">'.$row["tienda"].'</a></td>
                    <td>'.$row["genero"].'</td>
                    <td>'.$row["nivel_servicio_porc"].'%</td>
                    <td>'.$row["compra_lotes"].'</td>
                    <td>'.$row["entallados"].'</td>
                    <td>'.$row["picos"].'</td>
                    <td>'.$row["tot_lotes"].'</td>
                    <td>'.$row["meta_piso"].'</td>
                    <td>'.$row["meta_bodega"].'</td>
                    <td>'.$row["meta_cedis_calc"].'</td>
                    <td>'.$row["meta_total"].'</td>
                    
                  </tr>';
                }


                // ******** TOTAL **************
                $query = "SELECT '0 TOTAL' AS tienda,'' as genero,CONCAT('$',FORMAT(SUM(ROUND(a.mntovta)),0)) AS venta_pesos
                ,SUM(ROUND(100*porc_vta,1)) AS porc_vta
                ,FORMAT(SUM(ROUND(a.piezas)),0) AS venta_piezas
                ,SUM(ROUND(100*porc_pza,1)) AS porc_pza
                ,ROUND(SUM(a.porc_vta)*SUM(meta_piso)) AS entallados_venta
                ,CONCAT(format(SUM(c.articulos_entallados),0),' --> ',ROUND(100*SUM(c.articulos_entallados)/(SUM(c.articulos_entallados)+SUM(c.articulos_picos))),'%') AS entallados
                ,CONCAT(format(SUM(c.articulos_picos),0),' --> ',ROUND(100*SUM(c.articulos_picos)/(SUM(c.articulos_entallados)+SUM(c.articulos_picos))),'%') AS picos
                ,format(SUM(c.articulos_entallados)+SUM(c.articulos_picos),0) as tot_lotes
                ,FORMAT(SUM(b.meta_piso),0) AS meta_piso
                ,FORMAT(SUM(b.meta_bodega),0) AS meta_bodega
                ,FORMAT(SUM(b.meta_piso)+ifnull(SUM(b.meta_bodega),0)+ifnull(SUM(b.meta_cedis_calc),0) ,0) as meta_total
                  , ( SUM(b.meta_piso)+ifnull(SUM(b.meta_bodega),0)+ifnull(SUM(b.meta_cedis_calc),0) ) - SUM(c.articulos_entallados) AS compra_lotes
                  ,IFNULL(ROUND(10*SUM(c.articulos_entallados) / ( SUM(b.meta_piso)+ifnull(SUM(b.meta_bodega),0)+ifnull(SUM(b.meta_cedis_calc ) ,0) ) ),0) AS nivel_servicio
                  ,IFNULL(ROUND(100*SUM(c.articulos_entallados) / ( SUM(b.meta_piso)+ifnull(SUM(b.meta_bodega),0)+ifnull(SUM(b.meta_cedis_calc ) ,0) ) ),0) AS nivel_servicio_porc
                ,FORMAT(SUM(c.tot_existencia),0) AS existencia
                ,FORMAT(SUM(c.tot_transito),0) AS transito
                ,FORMAT(SUM(c.tot_transito)+SUM(c.tot_existencia),0) AS total
                ,sum(b.meta_cedis_calc) as meta_cedis_calc
                FROM ventas_gen a RIGHT JOIN tienda_entallado c
                ON a.tienda=c.suc AND a.genero=c.genero AND a.linea=c.linea
                AND a.periodo = DATE_FORMAT(CURDATE(),'%Y%m') 
                LEFT JOIN metas2 b ON b.tienda=c.suc AND b.genero=c.genero AND b.linea=c.linea
                WHERE c.linea<>'' and c.fecha=CURDATE() ".$tipo1.$tipo2.$tipo3."
                GROUP BY 1,2";

                if ($result = $mysqli->query($query)) {
    
                  /* fetch associative array */
                  while ($row = $result->fetch_assoc()) {
                      
                      $totales = $totales . '<tr>
                      <th><a href="index_ent_det_cat_map_niv.php?tienda='.$row["tienda"].'&gen='.$row["genero"].'">'.$row["tienda"].'</a></th>
                      <th>'.$row["genero"].'</th>
                      <th>'.$row["nivel_servicio_porc"].'%</th>
                      <th>'.$row["compra_lotes"].'</th>
                      <th>'.$row["entallados"].'</th>
                      <th>'.$row["picos"].'</th>
                      <th>'.$row["tot_lotes"].'</th>
                      <th>'.$row["meta_piso"].'</th>
                      <th>'.$row["meta_bodega"].'</th>
                      <th>'.$row["meta_cedis_calc"].'</th>
                      <th>'.$row["meta_total"].'</th>
                      
                    </tr>';

                  }
                }


                /* free the memory associated with this result set */
                $result->free();
            }
        } catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
            echo "MySQLi Error Code: " . $e->getCode() . "<br />";
            echo "Exception Msg: " . $e->getMessage();
            exit; // exit and close connection.
        }

        $mysqli->close(); // finally, close the connection
        
 ?>  
 
<!DOCTYPE html>
<html>

<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <meta name="description" content="Start your development with a Dashboard for Bootstrap 4.">
  <meta name="author" content="Creative Tim">
  <title><?php echo $marca;?></title>

  <!-- Fonts -->
  <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Open+Sans:300,400,600,700">
  <!-- Icons -->
  <link rel="stylesheet" href="../../assets/vendor/nucleo/css/nucleo.css" type="text/css">
  <link rel="stylesheet" href="../../assets/vendor/@fortawesome/fontawesome-free/css/all.min.css" type="text/css">
  <!-- Page plugins -->
  <link rel="stylesheet" href="../../assets/vendor/datatables.net-bs4/css/dataTables.bootstrap4.min.css">
  <link rel="stylesheet" href="../../assets/vendor/datatables.net-buttons-bs4/css/buttons.bootstrap4.min.css">
  <link rel="stylesheet" href="../../assets/vendor/datatables.net-select-bs4/css/select.bootstrap4.min.css">
  <!-- Argon CSS -->
  <link rel="stylesheet" href="../../assets/css/argon.css?v=1.1.0" type="text/css">
</head>

<body>

  <!-- menu lateral -->
  <?php include "menu.php"?>
  
  <!-- Main content -->
  <div class="main-content" id="panel">
    <!-- Topnav -->
    
    <!-- Header -->
    <!-- Header -->
    <div class="header bg-primary pb-6">
      <div class="container-fluid">
        <div class="header-body">
        <div class="row align-items-center py-4">

<div class="col-lg-6 col-7">
  <h6 class="h2 text-white d-inline-block mb-0"><?php echo $tienda;?></h6>
</div>

  <!-- Sidenav toggler -->
  <?php include "toggler.php"?>

</div>
        </div>
      </div>
    </div>
    <!-- Page content -->
    <div class="container-fluid mt--6">
      <!-- Table -->
      <div class="row">
        <div class="col">
          <div class="card">
            <!-- Card header -->
            <div class="card-header">
              <h3 class="mb-0"><?php echo $titulo;?></h3>
              <p class="text-sm mb-0">
              <?php echo $subtitulo;?>
              </p>
            </div>

            <div class="card-header bg-transparent border-0">
              <form method="get" name="forma1" id="forma1">
                <input type="hidden" name="gen" id="gen">

                <a href="#" class="btn btn-sm btn-neutral" onclick="document.getElementById('gen').value='dama';document.getElementById('forma1').submit();return false;">Dama</a>
                <a href="#" class="btn btn-sm btn-neutral" onclick="document.getElementById('gen').value='caballero';document.getElementById('forma1').submit();return false;">Caballero</a>
                <a href="#" class="btn btn-sm btn-neutral" onclick="document.getElementById('gen').value='';document.getElementById('forma1').submit();return false;">Dama y Caballero</a>
                <a href="mapa_cat.php?gen=<?php echo $_GET["gen"]?>&tie=<?php echo $_GET["tie"]?>&cds=<?php echo $_GET["cds"]?>" class="btn btn-sm btn-neutral">Mapa</a>

                <div class="custom-control custom-checkbox">
                <input class="custom-control-input" type="checkbox" name="tie" id="tie" value="5" <?php if($_GET["tie"]=="5"){echo "checked";}?>>
                <label class="custom-control-label" for="tie">Fitra 5 tiendas</label>
                </div>

                <div class="custom-control custom-checkbox">
                <input class="custom-control-input" type="checkbox" name="cds" id="cds" value="si" <?php if($_GET["cds"]=="si"){echo "checked";}?>>
                <label class="custom-control-label" for="cds">00 CDS</label>
                </div>

              </form>
            </div>

            <div class="table-responsive py-4">
              <table class="table table-flush" id="datatable-basic" data-order='[[ 3, "desc" ]]' data-page-length='100'>
                <thead class="thead-light">
                  <tr>
                    <th>Categoria</th>
                    <th>Genero</th>
                    <th>Nivel de servicio</th>
                    <th>Lotes faltantes</th>
                    <th>Ent Tie</th>
                    <th>Picos Tie</th>
                    <th>Tot Tie</th>
                    <th>Meta piso</th>
                    <th>Meta Bod</th>
                    <th>Meta Cedis Calc</th>
                    <th>Meta Total</th>
                    </tr>  
                </thead>

                <thead class="thead-light">
                <?php echo $totales;?>
                </thead>

                <tfoot>
                <?php echo $totales;?>

                  <tr>
                  <th>Categoria</th>
                    <th>Genero</th>
                    <th>Nivel de servicio</th>
                    <th>Lotes faltantes</th>
                    <th>Ent Tie</th>
                    <th>Picos Tie</th>
                    <th>Tot Tie</th>
                    <th>Meta piso</th>
                    <th>Meta Bod</th>
                    <th>Meta Cedis Calc</th>
                    <th>Meta Total</th>
                  </tr>
                </tfoot>
                <tbody>
                  <?php echo $tabla;?>
                </tbody>
              </table>
            </div>
          </div>
          
        </div>
      </div>
      <div class="card-header bg-transparent border-0">
               
               <a href="login.php" class="btn btn-sm btn-neutral">Login Tienda</a>
               <a href="index_ini.php" class="btn btn-sm btn-neutral">Dashboard 2</a>
               <a href="sube.php" class="btn btn-sm btn-neutral">Importar Metas Ent Tie</a>
             </div>
       <!-- Footer -->
       <footer class="footer pt-0">
         <div class="row align-items-center justify-content-lg-between">
           <div class="col-lg-6">
             <div class="copyright text-center text-lg-left text-muted">
               &copy; <?php echo $anio;?> <a href="#" class="font-weight-bold ml-1" ><?php echo $marca;?></a>
             </div>
           </div>

         </div>
       </footer>
    </div>
  </div>
  <!-- Argon Scripts -->
  <!-- Core -->
  <script src="../../assets/vendor/jquery/dist/jquery.min.js"></script>
  <script src="../../assets/vendor/bootstrap/dist/js/bootstrap.bundle.min.js"></script>
  <script src="../../assets/vendor/js-cookie/js.cookie.js"></script>
  <script src="../../assets/vendor/jquery.scrollbar/jquery.scrollbar.min.js"></script>
  <script src="../../assets/vendor/jquery-scroll-lock/dist/jquery-scrollLock.min.js"></script>
  <!-- Optional JS -->
  <script src="../../assets/vendor/datatables.net/js/jquery.dataTables.min.js"></script>
  <script src="../../assets/vendor/datatables.net-bs4/js/dataTables.bootstrap4.min.js"></script>
  <script src="../../assets/vendor/datatables.net-buttons/js/dataTables.buttons.min.js"></script>
  <script src="../../assets/vendor/datatables.net-buttons-bs4/js/buttons.bootstrap4.min.js"></script>
  <script src="../../assets/vendor/datatables.net-buttons/js/buttons.html5.min.js"></script>
  <script src="../../assets/vendor/datatables.net-buttons/js/buttons.flash.min.js"></script>
  <script src="../../assets/vendor/datatables.net-buttons/js/buttons.print.min.js"></script>
  <script src="../../assets/vendor/datatables.net-select/js/dataTables.select.min.js"></script>
  <!-- Argon JS -->
  <script src="../../assets/js/argon.js?v=1.1.0"></script>

</body>

</html>