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

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

  $mysqli2 = new mysqli($host2, $user2, $pass2, $base2);

  // query proporcionado por Carlos de Bruno Corza
  $query2 = "SELECT AIALMACEN AS Suc,CATDESCR AS Sucursal,CONCAT(AIALMACEN,' ',CATTEL2) AS Tienda,CATCOMENTARIO AS TipScursal,CATCONTACTO AS Zona,
  DFECHA AS Fecha,YEAR(DFECHA) AS Ano,CASE MONTH(DFECHA)
  WHEN 1 THEN 'Enero' WHEN 2 THEN 'Febrero' WHEN 3 THEN 'Marzo'
  WHEN 4 THEN 'Abril' WHEN 5 THEN 'Mayo' WHEN 6 THEN 'Junio'
  WHEN 7 THEN 'Julio' WHEN 8 THEN 'Agosto' WHEN 9 THEN 'Septiembre'
  WHEN 10 THEN 'Octubre' WHEN 11 THEN 'Noviembre' WHEN 12 THEN 'Diciembre'
  END Mes,DNUM AS Folio,LEFT(ICOD,8) AS Modelo,LEFT(ICOD,11) AS RC,
  ICOD AS Codigo,IUPC AS Talla2022,p.FAMDESCR AS H_o_M

  , l.famdescr AS Linea_nueva, n.famdescr AS Linea,

  b.FAMDESCR AS Departamento,d.FAMDESCR AS Temporada,
  e.FAMDESCR AS Marca,f.FAMDESCR AS Diseno_Tela,g.FAMDESCR AS Composicion,
  i.FAMDESCR AS Silueta,j.FAMDESCR AS TCompra,k.FAMDESCR AS Anio,l.FAMDESCR AS Proveedor
  ,n.FAMDESCR AS Tipo_Venta,Iultcpr,ICOLOREXT AS Color,MID(ICOD,11,3) AS Talla,
  AICANTF AS Piezas,AIPRECIO AS PrecioVta,
  AIDESCTO AS Dcto_Vta,ILISTA1 AS PrecioLista,ILISTA5 AS CostoUltm,
  CASE WHEN YEAR(IULTCPR)<=2018 THEN '2018 Y ANT'
  WHEN YEAR(IULTCPR)=2019 AND MONTH(IULTCPR)<=6 THEN '2019-1'
  WHEN YEAR(IULTCPR)=2019 AND MONTH(IULTCPR)>6 THEN '2019-2'
  WHEN YEAR(IULTCPR)=2020 AND MONTH(IULTCPR)>6 THEN '2020-2'
  WHEN YEAR(IULTCPR)=2021 AND MONTH(IULTCPR)<=6 THEN '2021-1'
  WHEN YEAR(IULTCPR)=2021 AND MONTH(IULTCPR)>6 THEN '2021-2'
  WHEN YEAR(IULTCPR)=2022 AND MONTH(IULTCPR)<=6 THEN '2022-1'
  WHEN YEAR(IULTCPR)=2022 AND MONTH(IULTCPR)>6 THEN '2022-2'
  ELSE '' END AS Antiguedad,
  (AICANTF*AIPRECIO) AS Mnto_Vta,(AICANTF*ILISTA5) AS Mnto_Cto
    FROM fdoc
    INNER JOIN faxinv ON fdoc.DSEQ=faxinv.DSEQ
    INNER JOIN finv ON faxinv.ISEQ=finv.ISEQ
    LEFT JOIN ffam a ON IFAM1=a.FAMTNUM LEFT JOIN ffam b ON IFAM2=b.FAMTNUM
    LEFT JOIN ffam c ON IFAM3=c.FAMTNUM LEFT JOIN ffam d ON IFAM4=d.FAMTNUM
    LEFT JOIN ffam e ON IFAM5=e.FAMTNUM LEFT JOIN ffam f ON IFAM6=f.FAMTNUM
    LEFT JOIN ffam g ON IFAM7=g.FAMTNUM LEFT JOIN ffam h ON IFAM8=h.FAMTNUM
    LEFT JOIN ffam i ON IFAM9=i.FAMTNUM LEFT JOIN ffam j ON IFAMA=j.FAMTNUM
    LEFT JOIN ffam k ON IFAMB=k.FAMTNUM LEFT JOIN ffam l ON IFAMl=l.FAMTNUM
    LEFT JOIN ffam m ON IFAMD=m.FAMTNUM LEFT JOIN ffam n ON IFAMn=n.FAMTNUM
    LEFT JOIN ffam o ON IFAMF=o.FAMTNUM LEFT JOIN ffam p ON IFAMh=p.FAMTNUM
    INNER JOIN falmcat ON faxinv.AIALMACEN=falmcat.CATALM
  WHERE ICOD NOT LIKE ('Y%') AND AITIPMV IN ('T','TO','TT') AND DNUM NOT IN ('T39000403') AND AIALMACEN<63 AND DFECHA >= DATE_ADD(CURDATE(),INTERVAL -3 DAY) 
  ORDER BY DFECHA
  LIMIT 99000
  ";
  

$mysqli->query("DELETE FROM ventas WHERE fecha>=DATE_ADD(CURDATE(),INTERVAL -3 DAY)");
  
  if ($result = $mysqli2->query($query2)) {

      // fetch associative array
      while ($row = $result->fetch_assoc()) {
          #Fecha,Folio,RC,Codigo,Talla2022,Departamento,Color,Tienda,H_o_M,Linea,Mnto_Vta,Piezas
          #PrecioVta,Dcto_Vta,PrecioLista,Mnto_Cto
          $query = "insert into ventas(fecha,folio,rc,codigo,talla2,departamento,color,tienda,genero,linea,mntovta
          ,piezas,preciovta,dctovta,preciolista,mntocto) values(";
          $query = $query . "
          '" . $row["Fecha"]."'
          ,'" . $row["Folio"]."'
          ,'" . $row["RC"]."'
          ,'" . $row["Codigo"]."'
          ,'" . $row["Talla2022"]."'
          ,'". $row["Departamento"]."'
          ,'". $row["Color"]."'
          ,'". $row["Tienda"]."'
          ,'". $row["H_o_M"]."'
          ,'". $row["Linea"]."'
          ,'". $row["Mnto_Vta"]."'
          ,'". $row["Piezas"]."'
          ,'". $row["PrecioVta"]."'
          ,'". $row["Dcto_Vta"]."'
          ,'". $row["PrecioLista"]."'
          ,'". $row["Mnto_Cto"]."')";

          $mysqli->query("$query");
          //die($query);
      } 


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


// ************ VENTAS A NIVEL GENERAL

  $mysqli->query("DELETE FROM ventas_gen WHERE periodo=DATE_FORMAT(CURDATE(),'%Y%m')");
  
  // usa  globales.dias_venta
  $mysqli->query("INSERT INTO ventas_gen
  SELECT DATE_FORMAT(CURDATE(),'%Y%m') AS periodo,tienda,genero,linea,SUM(mntovta) AS mntovta,SUM(piezas) AS piezas
  ,0.00000 AS porc_vta,0.00000 AS porc_pza
  FROM ventas inner join globales g
  WHERE LEFT(linea,5) <> 'acces' AND genero<>''
  AND fecha >= DATE_ADD(CURDATE(),INTERVAL -g.`dias_venta` DAY)
  GROUP BY 1,2,3,4
  HAVING SUM(mntovta) > 0
  ");

# agrega 00 CDS
# y los que tienen inventario pero no tienen ventas
$mysqli->query("
INSERT INTO ventas_gen
SELECT DATE_FORMAT(CURDATE(),'%Y%m') AS periodo,a.suc AS tienda,a.genero,a.linea,0 AS mntovta,0 AS piezas
,0.00000 AS porc_vta,0.00000 AS porc_pza
FROM inv_res a LEFT JOIN ventas_gen b ON a.suc=b.tienda AND a.genero=b.genero AND a.linea=b.linea
AND b.`periodo`=DATE_FORMAT(CURDATE(),'%Y%m')
WHERE a.fecha=CURDATE()
AND b.linea IS NULL
GROUP BY 1,2,3,4");

$mysqli->query("delete from ventas_gen_tot");

$mysqli->query("insert into ventas_gen_tot
SELECT periodo,tienda,genero,SUM(mntovta) AS mntovta,SUM(piezas)AS tot_piezas
FROM ventas_gen
WHERE genero<>'' AND periodo=DATE_FORMAT(CURDATE(),'%Y%m')
GROUP BY 1,2,3");

$mysqli->query("update ventas_gen_tot a INNER JOIN ventas_gen b 
ON a.`tienda`=b.`tienda` AND a.`genero`=b.`genero` AND a.`periodo`=b.`periodo`
SET b.`porc_vta` = IFNULL(b.`mntovta`,0)/a.`mntovta` 
,b.`porc_pza` = IFNULL(b.`piezas`,0)/a.`tot_piezas`
WHERE b.periodo=DATE_FORMAT(CURDATE(),'%Y%m') and a.mntovta>0");

// ************ VENTAS A NIVEL DE CATEGORIA

  $mysqli->query("DELETE FROM ventas_rc WHERE periodo=DATE_FORMAT(CURDATE(),'%Y%m')");
  
  // usa  globales.dias_venta
  $mysqli->query("INSERT INTO ventas_rc
  SELECT DATE_FORMAT(CURDATE(),'%Y%m') AS periodo,tienda,genero,linea,rc,SUM(mntovta) AS mntovta,SUM(piezas) AS piezas
  ,0.00000 AS porc_vta,0.00000 AS porc_pza
  FROM ventas INNER JOIN globales g
  WHERE LEFT(linea,5) <> 'acces' AND genero<>''
  AND fecha >= DATE_ADD(CURDATE(),INTERVAL -g.`dias_venta` DAY)
  GROUP BY 1,2,3,4,5
  HAVING SUM(mntovta) > 0
  ");

# agrega 00 CDS
# y los que tienen inventario pero no tienen ventas
$mysqli->query("
INSERT INTO ventas_rc
SELECT DATE_FORMAT(CURDATE(),'%Y%m') AS periodo,a.suc AS tienda,a.genero,a.linea,a.rc,0 AS mntovta,0 AS piezas
,0.00000 AS porc_vta,0.00000 AS porc_pza
FROM inv_res a LEFT JOIN ventas_rc b ON a.suc=b.tienda AND a.genero=b.genero AND a.linea=b.linea
AND b.`periodo`=DATE_FORMAT(CURDATE(),'%Y%m')
WHERE a.fecha=CURDATE()
AND b.linea IS NULL
GROUP BY 1,2,3,4,5");

$mysqli->query("delete from ventas_rc_tot");

$mysqli->query("INSERT INTO ventas_rc_tot
SELECT periodo,tienda,genero,linea,SUM(mntovta) AS mntovta,SUM(piezas)AS tot_piezas
FROM ventas_rc
WHERE genero<>'' AND periodo=DATE_FORMAT(CURDATE(),'%Y%m')
GROUP BY 1,2,3,4");

$mysqli->query("UPDATE ventas_rc_tot a INNER JOIN ventas_rc b 
ON a.`tienda`=b.`tienda` AND a.`genero`=b.`genero` AND a.`periodo`=b.`periodo`
AND a.linea=b.linea
SET b.`porc_vta` = IFNULL(b.`mntovta`,0)/a.`mntovta` 
,b.`porc_pza` = IFNULL(b.`piezas`,0)/a.`tot_piezas`
WHERE b.periodo=DATE_FORMAT(CURDATE(),'%Y%m') AND a.mntovta>0");


$mysqli->query("replace INTO inv(suc,tienda,genero,linea,rc,talla,codigo,total)
SELECT left(v.tienda,3) as suc,v.tienda,v.genero,v.linea,v.rc,v.talla2,v.codigo,0 AS total
FROM ventas v left JOIN inv a 
ON v.rc=a.rc
AND v.`tienda`=a.`tienda` AND v.`genero`=a.`genero` AND v.`linea`=a.`linea`
WHERE v.fecha>=date_add(curdate(),interval -30 day)
and a.rc IS NULL");


# para generar el nivelado de categorías por venta en lugar de meta
# porque la meta en traje está muy pareja entre ANG y PAR pero sus ventas estan 10 a 1
# vendiendo 100 trajes ANG y solo 10 PAR
$query = "delete from metas2vta";
$mysqli->query($query);
# genera metas en base a las ventas
$query = "insert into metas2vta(tienda,genero,linea,meta_piso,meta_bodega,meta_cedis_import,meta_cedis_calc)
SELECT c.suc AS tienda,c.genero,c.`linea`
,ROUND((a.porc_pza*t.tot_lotes)) AS meta_piso
,ROUND((a.porc_pza*t.tot_lotes)*(porcentaje_entallado_bodega/100)) AS meta_bodega
,ifnull(meta_piso,0) AS meta_cedis_import
,ROUND((a.porc_pza*t.tot_lotes)*(porcentaje_entallado_cedis/100)) 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')
inner join globales 
LEFT JOIN tot_exist t ON c.suc=t.suc AND c.genero=t.genero AND t.fecha=CURDATE()
LEFT JOIN metas2 b ON b.tienda=c.suc AND b.genero=c.genero AND b.linea=c.linea  
WHERE c.linea<>'' AND c.suc<>'00 CDS' AND c.`fecha`=CURDATE()
AND a.piezas>0
GROUP BY 1,2,3";
$mysqli->query($query);

# index_ent_det_cat_map.php
# nivelado por meta
$query = "REPLACE INTO cat_tot_nivel_servicio
SELECT CURDATE() AS fecha,c.genero,c.`linea`,CONCAT('$',FORMAT(SUM(ROUND(a.mntovta)),0)) AS venta_pesos
            ,ROUND(SUM(100*porc_vta),1) AS porc_vta
            ,FORMAT(SUM(ROUND(a.piezas)),0) AS venta_piezas
            ,ROUND(SUM(100*a.porc_pza),1) AS porc_pza
            ,ROUND( 100*( (SUM(c.tot_transito)+SUM(c.tot_existencia)) / t.tot_pzas ) ,1) AS exist_porc
            ,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)+SUM(b.meta_cedis_calc)+IFNULL(SUM(b.meta_bodega),0),0) AS meta_total
            ,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
                ,( SUM(b.meta_piso)+SUM(b.meta_cedis_calc)+IFNULL(SUM(b.meta_bodega),0) ) - SUM(c.articulos_entallados) AS compra_lotes
                ,SUM(c.articulos_entallados) AS articulos_entallados
                ,IFNULL(ROUND(10*SUM(c.articulos_entallados) / ( SUM(b.meta_piso)+SUM(b.meta_cedis_calc)+IFNULL(SUM(b.meta_bodega),0) )  ),0) AS nivel_servicio
                ,IFNULL(ROUND(100*SUM(c.articulos_entallados) / ( SUM(b.meta_piso)+SUM(b.meta_cedis_calc)+IFNULL(SUM(b.meta_bodega),0) ) ),0) AS nivel_servicio_porc
            ,ROUND(SUM(a.porc_vta*t.tot_lotes)) AS sug_lot_vta_pesos
            ,ROUND(SUM(a.porc_vta*t.tot_pzas)) AS sug_exist_vta_pesos
            ,ROUND(SUM(a.porc_pza*t.tot_lotes)) AS sug_lot_vta_pzas
            ,ROUND(SUM(a.porc_pza*t.tot_pzas)) AS sug_exist_vta_pzas
            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 tot_exist t ON c.suc=t.suc AND c.genero=t.genero AND t.fecha=CURDATE()
            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() 
            AND c.tot_existencia>0
            AND b.meta_piso>0
            AND c.articulos_entallados>0 AND c.articulos_picos>0
            GROUP BY 1,2,3
";
$mysqli->query($query);
# nivelado por venta
$query = "REPLACE INTO cat_tot_nivel_servicio2
SELECT CURDATE() AS fecha,c.genero,c.`linea`,CONCAT('$',FORMAT(SUM(ROUND(a.mntovta)),0)) AS venta_pesos
            ,ROUND(SUM(100*porc_vta),1) AS porc_vta
            ,FORMAT(SUM(ROUND(a.piezas)),0) AS venta_piezas
            ,ROUND(SUM(100*a.porc_pza),1) AS porc_pza
            ,ROUND( 100*( (SUM(c.tot_transito)+SUM(c.tot_existencia)) / t.tot_pzas ) ,1) AS exist_porc
            ,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)+SUM(b.meta_cedis_calc)+IFNULL(SUM(b.meta_bodega),0),0) AS meta_total
            ,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
                ,( SUM(b.meta_piso)+SUM(b.meta_cedis_calc)+IFNULL(SUM(b.meta_bodega),0) ) - SUM(c.articulos_entallados) AS compra_lotes
                ,SUM(c.articulos_entallados) AS articulos_entallados
                ,IFNULL(ROUND(10*SUM(c.articulos_entallados) / ( SUM(b.meta_piso)+SUM(b.meta_cedis_calc)+IFNULL(SUM(b.meta_bodega),0) )  ),0) AS nivel_servicio
                ,IFNULL(ROUND(100*SUM(c.articulos_entallados) / ( SUM(b.meta_piso)+SUM(b.meta_cedis_calc)+IFNULL(SUM(b.meta_bodega),0) ) ),0) AS nivel_servicio_porc
            ,ROUND(SUM(a.porc_vta*t.tot_lotes)) AS sug_lot_vta_pesos
            ,ROUND(SUM(a.porc_vta*t.tot_pzas)) AS sug_exist_vta_pesos
            ,ROUND(SUM(a.porc_pza*t.tot_lotes)) AS sug_lot_vta_pzas
            ,ROUND(SUM(a.porc_pza*t.tot_pzas)) AS sug_exist_vta_pzas
            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 tot_exist t ON c.suc=t.suc AND c.genero=t.genero AND t.fecha=CURDATE()
            LEFT JOIN metas2vta b ON b.tienda=c.suc AND b.genero=c.genero AND b.linea=c.linea  
            WHERE c.linea<>'' AND c.`fecha`=CURDATE() 
            AND c.tot_existencia>0
            AND b.meta_piso>0
            AND c.articulos_entallados>0 AND c.articulos_picos>0
            GROUP BY 1,2,3
";
$mysqli->query($query);

# nivel de servicio
# por meta
$query = "REPLACE INTO cat_nivel_servicio
SELECT CURDATE() AS fecha,c.suc AS tienda,c.genero,c.`linea`,CONCAT('$',FORMAT(SUM(ROUND(a.mntovta)),0)) AS venta_pesos
            ,ROUND(SUM(100*porc_vta),1) AS porc_vta
            ,FORMAT(SUM(ROUND(a.piezas)),0) AS venta_piezas
            ,ROUND(SUM(100*a.porc_pza),1) AS porc_pza
            ,ROUND( 100*( (SUM(c.tot_transito)+SUM(c.tot_existencia)) / t.tot_pzas ) ,1) AS exist_porc
            ,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)+SUM(b.meta_cedis_calc)+IFNULL(SUM(b.meta_bodega),0),0) AS meta_total
            ,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
                ,( SUM(b.meta_piso)+SUM(b.meta_cedis_calc)+IFNULL(SUM(b.meta_bodega),0) ) - SUM(c.articulos_entallados) AS compra_lotes
                ,SUM(c.articulos_entallados) AS articulos_entallados
                ,IFNULL(ROUND(10*SUM(c.articulos_entallados) / ( SUM(b.meta_piso)+SUM(b.meta_cedis_calc)+IFNULL(SUM(b.meta_bodega),0) )  ),0) AS nivel_servicio
                ,IFNULL(ROUND(100*SUM(c.articulos_entallados) / ( SUM(b.meta_piso)+SUM(b.meta_cedis_calc)+IFNULL(SUM(b.meta_bodega),0) ) ),0) AS nivel_servicio_porc
            ,ROUND(SUM(a.porc_vta*t.tot_lotes)) AS sug_lot_vta_pesos
            ,ROUND(SUM(a.porc_vta*t.tot_pzas)) AS sug_exist_vta_pesos
            ,ROUND(SUM(a.porc_pza*t.tot_lotes)) AS sug_lot_vta_pzas
            ,ROUND(SUM(a.porc_pza*t.tot_pzas)) AS sug_exist_vta_pzas
            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 tot_exist t ON c.suc=t.suc AND c.genero=t.genero AND t.fecha=CURDATE()
            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()
            AND b.meta_cedis_calc>0
            GROUP BY 1,2,3,4";
$mysqli->query($query);
# por venta
$query = "REPLACE INTO cat_nivel_servicio2
SELECT CURDATE() AS fecha,c.suc AS tienda,c.genero,c.`linea`,CONCAT('$',FORMAT(SUM(ROUND(a.mntovta)),0)) AS venta_pesos
            ,ROUND(SUM(100*porc_vta),1) AS porc_vta
            ,FORMAT(SUM(ROUND(a.piezas)),0) AS venta_piezas
            ,ROUND(SUM(100*a.porc_pza),1) AS porc_pza
            ,ROUND( 100*( (SUM(c.tot_transito)+SUM(c.tot_existencia)) / t.tot_pzas ) ,1) AS exist_porc
            ,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)+SUM(b.meta_cedis_calc)+IFNULL(SUM(b.meta_bodega),0),0) AS meta_total
            ,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
                ,( SUM(b.meta_piso)+SUM(b.meta_cedis_calc)+IFNULL(SUM(b.meta_bodega),0) ) - SUM(c.articulos_entallados) AS compra_lotes
                ,SUM(c.articulos_entallados) AS articulos_entallados
                ,IFNULL(ROUND(10*SUM(c.articulos_entallados) / ( SUM(b.meta_piso)+SUM(b.meta_cedis_calc)+IFNULL(SUM(b.meta_bodega),0) )  ),0) AS nivel_servicio
                ,IFNULL(ROUND(100*SUM(c.articulos_entallados) / ( SUM(b.meta_piso)+SUM(b.meta_cedis_calc)+IFNULL(SUM(b.meta_bodega),0) ) ),0) AS nivel_servicio_porc
            ,ROUND(SUM(a.porc_vta*t.tot_lotes)) AS sug_lot_vta_pesos
            ,ROUND(SUM(a.porc_vta*t.tot_pzas)) AS sug_exist_vta_pesos
            ,ROUND(SUM(a.porc_pza*t.tot_lotes)) AS sug_lot_vta_pzas
            ,ROUND(SUM(a.porc_pza*t.tot_pzas)) AS sug_exist_vta_pzas
            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 tot_exist t ON c.suc=t.suc AND c.genero=t.genero AND t.fecha=CURDATE()
            LEFT JOIN metas2vta b ON b.tienda=c.suc AND b.genero=c.genero AND b.linea=c.linea  
            WHERE c.linea<>'' AND c.`fecha`=CURDATE()
            #AND b.meta_cedis_calc>0
            GROUP BY 1,2,3,4";
$mysqli->query($query);

# por meta
$query = "replace into niv_serv(fecha,tienda,genero,linea,nivel_servicio_actual,nivela_servicio_global,nivela_lotes,dif_lotes,venta_pesos,venta_piezas,meta_total,tot_exist,compra_lotes,articulos_entallados)
SELECT a.fecha,a.tienda,a.genero,a.linea
,a.nivel_servicio_porc AS nivel_servicio_actual
,b.nivel_servicio_porc AS nivela_servicio_global
,ROUND(a.meta_total * (b.nivel_servicio_porc/100)) AS nivela_lotes
,ROUND(a.meta_total * (b.nivel_servicio_porc/100)) - a.articulos_entallados AS dif_lotes
,a.venta_pesos,a.venta_piezas,a.meta_total,a.total AS tot_exist,a.compra_lotes
,a.articulos_entallados
FROM cat_nivel_servicio a INNER JOIN cat_tot_nivel_servicio b
ON a.fecha=b.fecha AND a.genero=b.genero AND a.linea=b.linea
WHERE a.fecha=CURDATE()";
$mysqli->query($query);
# por venta
$query = "replace into niv_serv2(fecha,tienda,genero,linea,nivel_servicio_actual,nivela_servicio_global,nivela_lotes,dif_lotes,venta_pesos,venta_piezas,meta_total,tot_exist,compra_lotes,articulos_entallados)
SELECT a.fecha,a.tienda,a.genero,a.linea
,a.nivel_servicio_porc AS nivel_servicio_actual
,b.nivel_servicio_porc AS nivela_servicio_global
,ROUND(a.meta_total * (b.nivel_servicio_porc/100)) AS nivela_lotes
,ROUND(a.meta_total * (b.nivel_servicio_porc/100)) - a.articulos_entallados AS dif_lotes
,a.venta_pesos,a.venta_piezas,a.meta_total,a.total AS tot_exist,a.compra_lotes
,a.articulos_entallados
FROM cat_nivel_servicio2 a INNER JOIN cat_tot_nivel_servicio2 b
ON a.fecha=b.fecha AND a.genero=b.genero AND a.linea=b.linea
WHERE a.fecha=CURDATE()";
$mysqli->query($query);


// ********** FIN DE POR CATEGORIA

$tiempo_final = microtime(true);
$tiempo = round($tiempo_final - $tiempo_inicial);
$date = date('m/d/Y h:i:s a', time());
$mensaje = "se cargargon los datos de las ventas en ".$tiempo." segundos. ".$date; 

$mysqli->query("insert into log(tipo,log) values('vta','$mensaje')"); 

          # actualiza fechas de ultima entrada
          $query_talla="SELECT AIALMACEN AS tienda,LEFT(ICOD,11) AS rc,max(DFECHA) AS ultima_entrada,
          max(ALMALTA) AS alta_suc, max(ALMULTIMAVTA) as ultima_venta,
          CASE WHEN AITIPMV IN ('T','J') THEN 'SALIDA'
          WHEN AITIPMV IN ('I') THEN 'ENTRADA' ELSE '' END AS 'tipo',
          CASE WHEN AITIPMV IN ('J','I') THEN 'TRASPASO'
          WHEN AITIPMV IN ('T') THEN 'VENTA' ELSE '' END AS 'mov',
          n.famdescr as 'linea',ICOLOREXT AS color,AICANTF AS piezas
            FROM fdoc
            INNER JOIN faxinv ON fdoc.DSEQ=faxinv.DSEQ
            INNER JOIN finv ON faxinv.ISEQ=finv.ISEQ
            LEFT JOIN falm ON falm.ALMKEY=CONCAT(ICOD,AIALMACEN)
            left join ffam f1 on finv.ifam1=f1.famtnum left join ffam f2 on finv.ifam2=f2.famtnum left join ffam f3 on finv.ifam3=f3.famtnum
            left join ffam f4 on finv.ifam4=f4.famtnum left join ffam f5 on finv.ifam5=f5.famtnum left join ffam f6 on finv.ifam6=f6.famtnum
            left join ffam f7 on finv.ifam7=f7.famtnum left join ffam f8 on finv.ifam8=f8.famtnum left join ffam f9 on finv.ifam9=f9.famtnum
          
            left join ffam a on finv.ifama=a.famtnum left join ffam b on finv.ifamb=b.famtnum left join ffam c on finv.ifamc=c.famtnum
            left join ffam d on finv.ifamd=d.famtnum left join ffam e on finv.ifame=e.famtnum left join ffam f on finv.ifamf=f.famtnum
            left join ffam g on finv.ifamg=g.famtnum left join ffam h on finv.ifamh=h.famtnum left join ffam i on finv.ifami=i.famtnum
            left join ffam j on finv.ifamj=j.famtnum left join ffam k on finv.ifamk=k.famtnum left join ffam l on finv.ifaml=l.famtnum
            left join ffam m on finv.ifamm=m.famtnum left join ffam n on finv.ifamn=n.famtnum left join ffam o on finv.ifamo=o.famtnum
            INNER JOIN falmcat ON faxinv.AIALMACEN=falmcat.CATALM
          WHERE ICOD NOT LIKE ('Y%') AND AITIPMV='I' AND ITIPO=1 
          AND DFECHA >= DATE_ADD(CURDATE(),INTERVAL -7 DAY)
          and ALMALTA is not null and ALMULTIMAVTA is not null and DFECHA is not null
          group by 1,2";

          if ($result_talla = $mysqli2->query($query_talla)) {

            // los guarda en la tabla
            while ($row_talla = $result_talla->fetch_assoc()) {
              $query = "replace into ultima_entrada(tienda,rc,ultima_entrada,alta_suc,ultima_venta,tipo,mov,linea,color,piezas) values(
                '" . $row_talla["tienda"]."'
                ,'" . $row_talla["rc"]."'
                ,'" . $row_talla["ultima_entrada"]."'
                ,'" . $row_talla["alta_suc"]."'
                ,'" . $row_talla["ultima_venta"]."'
                ,'" . $row_talla["tipo"]."'
                ,'" . $row_talla["mov"]."'
                ,'" . $row_talla["linea"]."'
                ,'" . $row_talla["color"]."'
                ,'" . $row_talla["piezas"]."')";
              $mysqli->query("$query");  
            }

          }

} 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 "<hr>" . $query2 ."<hr>" .$query;
  exit; // exit and close connection.
}

$mysqli->close(); // finally, close the connection
$mysqli2->close(); // finally, close the connection
     
// FIN de ya confirmaron pulsando el boton
// ****************************************************************************************************       
} 
    
 ?>  
