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

            $tienda = "Costos";
            $titulo = "Por Categoría";
            $subtitulo = "";
            
            if($_GET["gen"]=='DAMA') {
                $tipo = "and genero='DAMA' and LEFT(linea,5)<>'ACCES'";
                $titulo = "DAMA";
                $gen = "DAMA";
                $porc = $_GET["porc"];
            } elseif($_GET["gen"]=='CABALLERO') {
                $tipo = "and genero='CABALLERO' and LEFT(linea,5)<>'ACCES'";
                $titulo = "CABALLERO";
                $gen = "CABALLERO";
                $porc = $_GET["porc"];
            } elseif($_GET["gen"]=='ACCESORIOS') {
                $tipo = "and LEFT(linea,5)='ACCES'";
                $titulo = "ACCESORIOS";
                $gen = "ACCESORIOS";
                $porc = $_GET["porc"];
              } else {
                // por default filtra caballero
                $tipo = "and genero='CABALLERO' and LEFT(linea,5)<>'ACCES'";
                $titulo = "CABALLERO";
                $gen = "CABALLERO";
                $porc = "100";
              }

            # metas costo
            $query = "REPLACE INTO metas_costos
            SELECT CURDATE() AS fecha,a.genero,a.linea
            ,ROUND( SUM(existencia+transito)*MAX(costo) ) AS tot_costo_actual
            ,ROUND( ( ((MAX(meta_piso)+MAX(meta_bodega)+MAX(meta_cedis_calc)) * (".$porc."/100)) - COUNT(*) ) * AVG(tallas) * MAX(costo) ) AS tot_costo_meta
            ,AVG(tallas) AS tallas_ideal
            ,AVG(tallas_exist) AS tallas_prom
            ,COUNT(*) AS tot_lotes
            ,(MAX(meta_piso)+MAX(meta_bodega)+MAX(meta_cedis_calc)) * (".$porc."/100) tot_lotes_meta
            ,AVG(existencia) AS prom_exist
            ,SUM(existencia+transito) AS tot_existencia
            ,MAX(meta_piso) AS meta_piso,MAX(meta_bodega) AS meta_bodega,MAX(meta_cedis_calc) AS meta_cedis_calc
            ,MAX(costo) AS costo_prom
            FROM inv_res a INNER JOIN globales
            inner JOIN metas_linea b ON a.genero=b.genero AND a.linea=b.linea
            inner JOIN costos_linea c ON a.genero=c.genero AND a.linea=c.linea
            WHERE a.fecha=CURDATE() AND a.entallado < porcentaje_entallado_global
            GROUP BY 1,2,3";
            $mysqli->query($query);

            // Select all records from the db. Keep inside try block incase there an issue with sql query
            $query = "select fecha,genero,linea,format(tot_costo_actual,0) as tot_costo_actual,format(tot_costo_meta,0) as tot_costo_meta,tallas_ideal,tallas_prom
            ,tot_lotes,tot_lotes_meta,prom_exist,tot_existencia,meta_piso,meta_bodega,meta_cedis_calc,costo_prom
            from metas_costos
            where fecha=curdate()
            ".$tipo."
            ";
            //die($query);
            //echo $query;
            
            if ($result = $mysqli->query($query)) {

                /* fetch associative array */
                while ($row = $result->fetch_assoc()) {
                    
                    if($tienda==''){
                      $tienda = $row["sucursal"];
                    }
                    
                    $tabla = $tabla . '<tr>
                    <td>'.$row["genero"].'</td>
                    <td><a href="tot_cat_det.php?gen='.$row["genero"].'&linea='.$row["linea"].'">'.$row["linea"].'</a></td>
                    <td>$'.$row["tot_costo_actual"].'</td>
                    <td>$'.$row["tot_costo_meta"].'</td>
                    <td>'.$row["tallas_ideal"].'</td>
                    <td>'.$row["tot_lotes"].'</td>
                    <td>'.$row["tot_lotes_meta"].'</td>
                    <td>'.$row["tot_existencia"].'</td>
                    <td>'.$row["costo_prom"].'</td>
                    <td>'.$row["tallas_prom"].'</td>
                    <td>'.$row["prom_exist"].'</td>
                  </tr>';
                }

                // ******** TOTAL **************
                $query = "select fecha,'TOTAL' as genero,'' as linea,format(sum(tot_costo_actual),0) as tot_costo_actual
                ,format(sum(tot_costo_meta),0) as tot_costo_meta,avg(tallas_ideal) as tallas_ideal
                ,avg(tallas_prom) as tallas_prom
                ,sum(tot_lotes) as tot_lotes,sum(tot_lotes_meta) as tot_lotes_meta,avg(prom_exist) as prom_exist
                ,sum(tot_existencia) as tot_existencia
                ,meta_piso,meta_bodega,meta_cedis_calc
                ,sum(costo_prom) as costo_prom
                from metas_costos
                where fecha=curdate()
                ".$tipo."
                ";
    //die($query);
                if ($result = $mysqli->query($query)) {
    
                    /* fetch associative array */
                    while ($row = $result->fetch_assoc()) {
                        
                      $totales = $totales . '<tr>
                      <TH>'.$row["genero"].'</TH>
                      <TH><a href="tot_cat_det.php?gen='.$row["genero"].'&linea='.$row["linea"].'">'.$row["linea"].'</a></TH>
                      <TH>$'.$row["tot_costo_actual"].'</TH>
                      <TH>$'.$row["tot_costo_meta"].'</TH>
                      <TH>'.$row["tallas_ideal"].'</TH>
                      <TH>'.$row["tot_lotes"].'</TH>
                      <TH>'.$row["tot_lotes_meta"].'</TH>
                      <TH>'.$row["tot_existencia"].'</TH>
                      <TH>'.$row["costo_prom"].'</TH>
                      <th>'.$row["tallas_prom"].'</th>
                      <th>'.$row["prom_exist"].'</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" value="<?php echo $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>


                Porcentaje de la meta:<input type="text" size=3 name="porc" id="porc" value="<?php echo $porc;?>"><input type=submit value="Calcular">

              </form>
              <hr>
              <a href="metas_costo.php" class="btn btn-sm btn-neutral">Entallados</a>

            </div>

            <div class="table-responsive py-4">
              <table class="table table-flush" id="datatable-basic" data-order='[[ 6, "desc" ]]' data-page-length='100'>
                <thead class="thead-light">
                  <tr>
                    <th>Genero</th>
                    <th>Linea</th>
                    <th>Costo actual</th>
                    <th>Dif Costo meta</th>
                    <th>Tallas ideal</th>
                    <th>Lotes</th>
                    <th>Meta</th>
                    <th>Existencia</th>
                    <th>Costo</th>
                    <th>Tallas prom</th>
                    <th>Exist prom</th>
                    </tr>  
                </thead>

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


                <tbody>
                  <?php echo $tabla;?>
                </tbody>
              </table>
            </div>
          </div>
          
        </div>
      </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>