<?php
include "conn.php";
set_time_limit(600);

// si ya confirmaron pulsando el boton
// ****************************************************************************************************
if($_GET["confirma"]=="si"){

$tiempo_inicial = microtime(true);

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["limit"]=="0,1500"){

                // # inicializa lotifica
                $query = "
                DELETE FROM inv_res_loti WHERE fecha=CURDATE()";
                $mysqli->query($query);

                // borra movs
                $query = "INSERT INTO inv_res_loti(fecha,suc,genero,rc,linea,sucursal,descripcion,existencia,transito,tallas,entallado,departamento,tallatipo,anio,alta_suc,color)
                SELECT fecha,suc,genero,rc,linea,sucursal,descripcion,existencia,transito,tallas,entallado,departamento,tallatipo,anio,alta_suc,color FROM inv_res WHERE fecha=CURDATE()";
                $mysqli->query($query);

                // borra movs
                $query = "DELETE FROM movs WHERE DATE(fecha)=CURDATE()";
                $mysqli->query($query);

            }

            // Select all records from the db. Keep inside try block incase there an issue with sql query
            $query = "SELECT rc,COUNT(*),SUM(piezas) AS pzas 
            FROM ventas INNER JOIN globales
            WHERE fecha>=DATE_ADD(CURDATE(),INTERVAL -dias_venta DAY)
            GROUP BY rc 
            HAVING pzas>=40
            ORDER BY pzas DESC
            LIMIT 99000";
            //echo $query;

            if ($result = $mysqli->query($query)) {
                
                /* fetch associative array */
                $traspasos=0;
                while ($row = $result->fetch_assoc()) {
                    exec('wget https://'.$_SERVER['SERVER_NAME'].'/lotes_virtuales_dobles.php?rc='.$row["rc"]);
                    //exec('wget https://'.$_SERVER['SERVER_NAME'].'/lotes_virtuales.php?rc='.$row["rc"]);
                    $traspasos++;
                }

            }

            if($_GET["limit"]=="0,1500"){

                // regenera la tabla de tienda_entallado_loti
                $query = "DELETE FROM tienda_entallado_loti WHERE fecha=CURDATE()";
                $mysqli->query($query);

            }


            $mysqli->query("REPLACE INTO tienda_entallado_loti
            SELECT fecha,suc,genero,linea
                        ,SUM(IF(entallado>=porcentaje_entallado_global AND tallas>1,1,0)) AS articulos_entallados
                        ,ROUND( 100* SUM(IF(entallado>=porcentaje_entallado_global AND tallas>1,1,0)) /COUNT(*)) AS parte_entallados
                        ,SUM(IF(entallado<porcentaje_entallado_global AND tallas>1,1,0)) AS articulos_picos
                        ,ROUND( 100* SUM(IF(entallado<porcentaje_entallado_global AND tallas>1,1,0)) /COUNT(*)) AS parte_picos
                        ,SUM(IF(tallas=1,1,0)) AS articulos_unitalla
                        ,ROUND( 100* SUM(IF(tallas=1,1,0)) /COUNT(*)) AS parte_unitalla
                        ,COUNT(*) AS tot_articulos
                        ,SUM(existencia) AS tot_existencia
                        ,SUM(transito) AS tot_transito
                        FROM inv_res_loti inner join globales
                        WHERE fecha=CURDATE() 
                        GROUP BY suc,genero,linea");
            
                        # agrega las lineas que tienen venta pero no aparecen en el inventario
                        $mysqli->query("REPLACE INTO tienda_entallado_loti(`fecha`,`suc`,`genero`,`linea`,`articulos_entallados`,`parte_entallados`,`articulos_picos`,`parte_picos`,
                        `articulos_unitalla`,`parte_unitalla`,`tot_articulos`,`tot_existencia`,`tot_transito`)
                        SELECT CURDATE() AS fecha,a.tienda AS suc,a.genero,a.linea,0 AS art_ent,0 AS part_ent,0 AS art_pic,0 AS part_pic,0 AS art_uni,0 AS part_uni,0 AS tot_art,0 AS tot_ex,0 AS tot_tra
                        FROM ventas_gen a LEFT JOIN tienda_entallado_loti b
                        ON a.`tienda`=b.`suc` AND a.`genero`=b.`genero` AND a.`linea`=b.`linea`
                        AND b.fecha=CURDATE() 
                        WHERE a.periodo=DATE_FORMAT(CURDATE(),'%Y%m') 
                        AND b.`linea` IS NULL
                        GROUP BY 1,2,3,4");
            
                        # metas que no tienen existencia
                        # inserta en tabla de tienda_entallado_loti para que aparezacan en el query que muestra todas las metas 
                        # en index.php y detalle metas que no tienen existencia
                        # este query también debe de ejecutarse al subirse nuevas metas
                        $mysqli->query("REPLACE INTO tienda_entallado_loti(fecha,suc,genero,linea,articulos_entallados,parte_entallados,articulos_picos,
                        parte_picos,articulos_unitalla,parte_unitalla,tot_articulos,tot_existencia,tot_transito)
                        SELECT CURDATE() AS fecha,a.tienda AS suc,a.genero,a.linea,0 AS ent,0 AS pent,0 AS pic,0 AS ppic,0 AS uni,0 AS puni,0 AS arti,0 AS exi,0 AS transi 
                        FROM metas a
                        LEFT JOIN tienda_entallado_loti b ON b.suc=a.tienda AND b.genero=a.genero AND b.linea=a.linea
                        AND b.fecha=CURDATE()
                        WHERE b.linea IS NULL
                        GROUP BY 1,2,3,4");

                        # # totaliza completados por linea
                        $query = "replace into entallado_loti(fecha,tienda,genero,linea,completos)
                        select curdate() as fecha,b.destino,a.genero,a.`linea`,count(*) as completos
                        from inv_res a inner join movs b on a.fecha=b.`fecha`
                        and a.rc=b.rc and a.suc=b.`origen`
                        inner join globales g 
                        where a.fecha=curdate() and b.`porc_dest`>=g.`porcentaje_entallado_global`
                        group by 1,2,3,4";
                        $mysqli->query($query);

                        # # suma completados del loti
                        $query = "UPDATE tienda_entallado_loti a INNER JOIN entallado_loti b
                        ON a.fecha=b.fecha AND b.`tienda`=a.`suc` AND b.`genero`=a.`genero` AND b.`linea`=a.linea
                        SET a.`articulos_entallados`=a.`articulos_entallados`+b.`completos`
                        WHERE a.fecha=CURDATE()";
                        $mysqli->query($query);

                        # recalcula partes
                        $query = "update tienda_entallado_loti a
                        SET parte_entallados=IFNULL(ROUND(100*articulos_entallados/(articulos_entallados+articulos_picos)),0),parte_picos=IFNULL(ROUND(100*articulos_picos/(articulos_entallados+articulos_picos)),0)
                        WHERE fecha=CURDATE() AND articulos_entallados>0 and articulos_picos>0";
                        $mysqli->query($query);

                        $tiempo_final = microtime(true);
                        $tiempo = round($tiempo_final - $tiempo_inicial);
                        $date = date('m/d/Y h:i:s a', time());
                        $mensaje = "Lotifica finalizado en ".$tiempo." segundos. ".$date; 
                        //echo $mensaje;
 
                        //$mysqli->query("insert into log(tipo,log) values('inv','$mensaje')"); 

                        // tabla que despues de movs queda en cero
                        $query_movs = "DROP TABLE IF EXISTS movs_cero";
                        $mysqli->query($query_movs);
                        // crea
                        $query_movs = "CREATE TABLE movs_cero
                        SELECT fecha,origen,rc,talla,exist_orig
                        FROM movs WHERE fecha=CURDATE() AND exist_orig=0
                        GROUP BY 1,2,3,4";
                        $mysqli->query($query_movs);
                        // key
                        $query_movs = "ALTER TABLE movs_cero ADD KEY cer(fecha,origen,rc,talla)";
                        $mysqli->query($query_movs);

                // borra movs
                $query = "DELETE FROM movs_buenos WHERE DATE(fecha)=CURDATE()";
                $mysqli->query($query);

                        // tabla que despues de movs queda en cero
                        $query_movs = "INSERT INTO movs_buenos(fecha,rc,origen,destino,talla,cant,exist_orig,exist_dest,porc_orig,porc_dest,id)
                        SELECT m.*
                        FROM inv_res a INNER JOIN globales 
                        INNER JOIN inv b ON b.tienda=a.suc AND a.rc=b.rc
                        INNER JOIN movs m ON m.fecha=a.fecha AND m.origen=a.suc AND m.rc=a.rc AND m.talla=b.talla
                        AND m.porc_dest >= porcentaje_entallado_global
                        AND a.transito=0
                        WHERE a.fecha=CURDATE()
                        AND DATEDIFF(CURDATE(),ultima_entrada) > dias_venta_movs
                        #AND DATEDIFF(CURDATE(),ultima_venta) > dias_venta_movs
                        AND entallado < porcentaje_entallado_global
                        AND b.talla<>'UNITALLA'
                        AND a.suc<>'00 CDS'
                        UNION
                        SELECT mt.*
                        FROM inv_res a INNER JOIN globales 
                        INNER JOIN inv b ON b.tienda=a.suc AND a.rc=b.rc
                        INNER JOIN movs m ON m.fecha=a.fecha AND m.origen=a.suc AND m.rc=a.rc AND m.talla=b.talla
                        AND m.porc_dest >= porcentaje_entallado_global
                        AND a.transito=0
                        INNER JOIN movs mt ON mt.fecha=a.fecha AND mt.destino=m.destino AND mt.rc=a.rc #AND mt.talla=b.talla    
                        AND a.transito=0
                        INNER JOIN inv c ON c.tienda=mt.origen AND c.rc=mt.rc AND c.talla=mt.talla
                        WHERE a.fecha=CURDATE()
                        AND DATEDIFF(CURDATE(),ultima_entrada) > dias_venta_movs
                        #AND DATEDIFF(CURDATE(),ultima_venta) > dias_venta_movs         
                        AND b.talla<>'UNITALLA'
                        AND a.suc<>'00 CDS'";
                        $mysqli->query($query_movs);
            
        } 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

 // FIN de ya confirmaron pulsando el boton
// ****************************************************************************************************       
} 

    
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);
              
            $query = "SELECT count(*) as completos FROM lotes_completados where fecha=curdate()";
            if ($result = $mysqli->query($query)) {
                while ($row = $result->fetch_assoc()) {
                    $lotes_acompletados = $row["completos"];
                }
            }  

            if(isset($mensaje)){
              $titulo = "Se procesaron ".$traspasos." SKU's y se completaron ".$lotes_acompletados." lotes";
            } else {
              $titulo = "Lotes completados la última vez: ".$lotes_acompletados;
            }

              if(isset($mensaje)){
                $subtitulo = $mensaje;
              } else {
                $subtitulo = "El lotifica tarda aproximadamente 4 minutos";
              }

            // para mostrar solo los completados
            $query = "
            SELECT DATE(fecha) AS fecha,rc,destino,round(avg(porc_dest)) AS porc_dest,COUNT(*) AS movs
            FROM movs_buenos INNER JOIN globales g
            WHERE DATE(fecha)=CURDATE() 
            and porc_dest >= g.porcentaje_entallado_global
            GROUP BY 1,2,3
            ORDER BY movs DESC
            LIMIT 99000
            ";

            //echo $query;
            // RESUMEN DE EXISTENCIAS
            if ($result = $mysqli->query($query)) {

                /* fetch associative array */
                while ($row = $result->fetch_assoc()) {
                    
                    $tabla = $tabla . '<tr>
                    <td>'.$row["fecha"].'</td>
                    <td><a href="lotes_virtuales_dobles.php?rc='.$row["rc"].'">'.$row["rc"].'</a></td>
                    <td>'.$row["destino"].'</td>
                    <td>'.$row["porc_dest"].'</td>
                    <td>'.$row["movs"].'</td>
                  </tr>';
                }

                // ******** TOTAL **************
                $query = "SELECT '' AS fecha,'TOTAL' as rc,'' as destino,round(avg(porc_dest)) AS porc_dest,COUNT(*) AS movs
                FROM movs_buenos INNER JOIN globales g
                WHERE DATE(fecha)=CURDATE() 
                and porc_dest >= g.porcentaje_entallado_global";
    
                if ($result = $mysqli->query($query)) {
    
                    /* fetch associative array */
                    while ($row = $result->fetch_assoc()) {
                        
                      $totales = $totales . '<tr>
                      <th>'.$row["fecha"].'</th>
                      <th><a href="lotes_virtuales_dobles.php?rc='.$row["rc"].'">'.$row["rc"].'</a></th>
                      <th>'.$row["destino"].'</th>
                      <th>'.$row["porc_dest"].'</th>
                      <th>'.$row["movs"].'</th>
                    </tr>';

                      $subtitulo = "Total de movimientos ".$row["movs"]." con porcentaje de lote al ".$row["porc_dest"]."%";
                    }
                }



                /* free the memory associated with this result set */
                $result->free();
            }


            $query = "DROP TABLE IF EXISTS salidas";
            $mysqli->query($query);

            $query = "CREATE TABLE salidas
            SELECT origen,COUNT(*) AS salidas
            FROM movs_buenos
            WHERE DATE(fecha)=CURDATE() 
            GROUP BY 1";
            $mysqli->query($query);
            
            $query = "DROP TABLE IF EXISTS entradas";
            $mysqli->query($query);
            
            $query = "CREATE TABLE entradas
            SELECT destino,COUNT(*) AS entradas
            FROM movs_buenos
            WHERE DATE(fecha)=CURDATE() 
            GROUP BY 1";
            $mysqli->query($query);
            
            $query = "DROP TABLE IF EXISTS tiendas_all";
            $mysqli->query($query);
            
            $query = "CREATE TABLE tiendas_all
            SELECT origen AS tienda FROM salidas
            UNION
            SELECT destino AS tienda FROM entradas
            GROUP BY 1";
            $mysqli->query($query);
            
            $query = "delete from lotes_completados where fecha=curdate()";
            $mysqli->query($query);
            
            $query = "insert into lotes_completados
            SELECT DATE(fecha) AS fecha,rc,destino,max(porc_dest) AS porc_dest,COUNT(*) AS movs
            FROM movs INNER JOIN globales g
            WHERE DATE(fecha)=CURDATE() 
            AND porc_dest >= g.porcentaje_entallado_global
            GROUP BY 1,2,3";
            $mysqli->query($query);
            
            // MONITOR DEL LOG
            $query = "
            SELECT a.tienda,b.entradas,c.salidas,IFNULL(b.entradas,0)-IFNULL(c.salidas,0) AS dif
            FROM tiendas_all a LEFT JOIN entradas b ON a.tienda=b.destino
            LEFT JOIN salidas c ON a.tienda=c.origen
            UNION
            SELECT 'TOTAL' AS tienda,SUM(b.entradas) AS entradas,SUM(c.salidas) AS salidas,SUM(IFNULL(b.entradas,0)-IFNULL(c.salidas,0)) AS dif
            FROM tiendas_all a LEFT JOIN entradas b ON a.tienda=b.destino
            LEFT JOIN salidas c ON a.tienda=c.origen
            ";
            if ($result = $mysqli->query($query)) {

              /* fetch associative array */
              while ($row = $result->fetch_assoc()) {
                  
                  $tabla2 = $tabla2 . '<tr>
                  <td>'.$row["tienda"].'</td>
                  <td>'.$row["entradas"].'</td>
                  <td>'.$row["salidas"].'</td>
                  <td>'.$row["dif"].'</td>
                </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();
            echo "Query: " . $query;
            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">LOTES VIRTUALES</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 id="boton" class="card-header bg-transparent border-0">
              <form method="get" name="forma1" id="forma1">
                <input type="hidden" name="confirma" id="confirma">
                <input type="hidden" name="limit" id="limit" value="<?php echo $_GET["limit"]?>">

                <a href="#" class="btn btn-sm btn-neutral" onclick="document.getElementById('confirma').value='si';document.getElementById('boton').style.display='none';document.getElementById('imagen').style.display='inline';document.getElementById('forma1').submit();return false;">Iniciar el proceso de lotifica</a>
                
                <!--a href="https://<? echo $_SERVER["SERVER_NAME"]?>/lotes_virtuales_dobles.php?rc=22209804500" class="btn btn-sm btn-neutral">
                  Caso 1: con mas tallas</a>
                  <a href="https://<? echo $_SERVER["SERVER_NAME"]?>/lotes_virtuales_dobles.php?rc=23200918590" class="btn btn-sm btn-neutral">
                  Caso : sin clasificacion</a>
                  <a href="https://<? echo $_SERVER["SERVER_NAME"]?>/lotes_virtuales_dobles.php?rc=22209804500" class="btn btn-sm btn-neutral">
                  Caso 1: con mas tallas sin revisar porcentaje</a-->

            </form>
            </div>
            <div id="imagen" style="display:none">
                <img src="procesing.gif" width="100" height="100">
            </div>

              <!-- LOG -->
              <div>
              <table class="table-dark" border="2" cellpadding=2 cellsapcing=2 style="align: center">
                <thead class="thead-light" style="font-size: small">
                  <tr>
                    <th>Tienda</th>
                    <th>Entradas</th>
                    <th>Salidas</th>
                    <th>Dif</th>
                  </tr>
                </thead>
                <tbody>
                  <?php echo $tabla2;?>
                </tbody>
              </table>
            </div>  

            <!-- RESUMEN DE EXISTENCIAS -->
            <div class="table-responsive py-4">
              <table class="table table-flush"  id="datatable-basic">
                <thead class="thead-light">
                  <tr>
                    <th>Fecha</th>
                    <th>RC</th>
                    <th>Destino</th>
                    <th>Porc dest</th>
                    <th>Movs</th>
                  </tr>
                </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>
 