
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

DROP TABLE IF EXISTS `fjcategorias`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `fjcategorias` (
  `idfjcategorias` int(11) NOT NULL AUTO_INCREMENT,
  `id` int(11) DEFAULT NULL,
  `parent` varchar(100) DEFAULT NULL,
  `code` varchar(100) DEFAULT NULL,
  `catname` varchar(100) DEFAULT NULL,
  `catpath` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`idfjcategorias`),
  KEY `fjcategorias_id_IDX` (`id`) USING BTREE,
  KEY `fjcategorias_name_IDX` (`catname`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=204 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `fjlaboratorios`
--

DROP TABLE IF EXISTS `fjlaboratorios`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `fjlaboratorios` (
  `idfjlaboratorios` int(11) NOT NULL AUTO_INCREMENT,
  `manufacturer` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`idfjlaboratorios`),
  KEY `fjlaboratorios_manufacturer_IDX` (`manufacturer`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=511 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `fjlaboratoriostoimport`
--

DROP TABLE IF EXISTS `fjlaboratoriostoimport`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `fjlaboratoriostoimport` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idffjlaboratorios` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `fjlanzamientos`
--

DROP TABLE IF EXISTS `fjlanzamientos`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `fjlanzamientos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `titulo` varchar(255) DEFAULT NULL,
  `categorias` varchar(100) DEFAULT NULL,
  `codigo` varchar(255) DEFAULT NULL,
  `bajada` text DEFAULT NULL,
  `descripcion` text DEFAULT NULL,
  `video` text DEFAULT NULL,
  `link` varchar(255) DEFAULT NULL,
  `tags` text DEFAULT NULL,
  `notas_viejas` varchar(50) DEFAULT NULL,
  `identificador` varchar(100) DEFAULT NULL,
  `estado` varchar(100) DEFAULT NULL,
  `fecha` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fjlanzamientos_codigo_IDX` (`codigo`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `fjmarcas`
--

DROP TABLE IF EXISTS `fjmarcas`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `fjmarcas` (
  `idjfmarcas` int(11) NOT NULL AUTO_INCREMENT,
  `id` int(11) DEFAULT NULL,
  `brand` varchar(100) DEFAULT NULL,
  `logo` varchar(100) DEFAULT NULL,
  `code` varchar(100) DEFAULT NULL,
  `idlaboratorio` int(11) DEFAULT NULL,
  PRIMARY KEY (`idjfmarcas`),
  KEY `fjmarcas_id_IDX` (`id`) USING BTREE,
  KEY `fjmarcas_brand_IDX` (`brand`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1569 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `fjmarcastoimport`
--

DROP TABLE IF EXISTS `fjmarcastoimport`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `fjmarcastoimport` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idfjmarcas` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;


--
-- Dumping routines for database 'aliadasc_web2019'
--
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `spaltamodfjlaboratorios` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`aliadasc_am19`@`localhost` PROCEDURE `spaltamodfjlaboratorios`(IN imanufacturer varchar(100)

 )
    NO SQL
begin

declare ExisteLaboratorio  int;
declare Resultado int;

set ExisteLaboratorio = (select idfjlaboratorios from fjlaboratorios where manufacturer = ucase(trim(imanufacturer)));

if (ExisteLaboratorio > 0) then

	set Resultado = (ExisteLaboratorio);

else 

	INSERT INTO fjlaboratorios 

			(manufacturer)

	values

			(ucase(trim(imanufacturer)));

	set ExisteLaboratorio=(select last_insert_id());
	set Resultado = (ExisteLaboratorio);

end if; 


select Resultado;



END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `spaltamodfjmarcas` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`aliadasc_am19`@`localhost` PROCEDURE `spaltamodfjmarcas`(  in iid int,
  in ibrand varchar(255),
  in ilogo varchar(255),
  in icode varchar(255),
  in ilaboratorio int
  
)
    NO SQL
begin
declare ExisteMarca  int;
set ExisteMarca = (select idjfmarcas from fjmarcas where id  = iid);
if (ExisteMarca  > 0) then
	UPDATE fjmarcas
		SET 
		brand = ibrand,
		logo = ilogo,
		code = icode,
		idlaboratorio = ilaboratorio 
	WHERE idjfmarcas=ExisteMarca;
else
	INSERT INTO fjmarcas 
		(id, brand, logo, code, idlaboratorio)
	VALUES(iid, ibrand,ilogo, icode,idlaboratorio);
	set ExisteMarca = (select last_insert_id());
end if; 
select ExisteMarca;
end ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `spaltamodjfcategorias` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`aliadasc_am19`@`localhost` PROCEDURE `spaltamodjfcategorias`(
  in iid int,
  in iparent varchar(255),
  in icode varchar(255),
  in iname varchar(255),
  in ipath varchar(255)
)
    NO SQL
begin
declare ExisteCategoria  int;
declare Resultado int; 
set ExisteCategoria = (select idfjcategorias from fjcategorias where id  = iid);
if (ExisteCategoria  > 0) then
	UPDATE fjcategorias
		SET 
      parent  = iparent, 
      code = icode, 
      catname = iname, 
      catpath = ipath 
	  WHERE idfjcategorias=ExisteCategoria;
else
	INSERT INTO fjcategorias 
		(id, parent, code, catname, catpath)
	VALUES
    (iid, iparent,icode, iname,ipath);
	set ExisteCategoria = (select last_insert_id());
end if; 
set Resultado = ExisteCategoria;
select Resultado;
end ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `spfjaltalanzamientos` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`aliadasc_am19`@`localhost` PROCEDURE `spfjaltalanzamientos`(
    in ititulo varchar(255) ,
    in icategorias varchar(100) ,
    in icodigo varchar(255) ,
    in ibajada text ,
    in idescripcion text ,
    in ivideo text ,
    in ilink varchar(255) ,
    in itags text ,
    in inotas_viejas varchar(50) ,
    in iidentificador varchar(100) ,
    in iestado varchar(100) ,
    in ifecha date 

 )
    NO SQL
begin
declare Alta int;
declare ExisteLanzamiento  int;
declare Resultado int;
declare IdentificadortoSet bigint;
declare Existeiden bigint;

set ExisteLanzamiento = (select id from fjlanzamientos where codigo = icodigo);
set Alta = 0;
if (ExisteLanzamiento > 0) then
	set Resultado = (ExisteLanzamiento);
set IdentificadortoSet = 0;
else 
	set Existeiden = 1;
	WHILE (Existeiden >0) DO
		set IdentificadortoSet = (SELECT CAST((RAND()*(9999999999-1135687452+1)+1135687452) as UNSIGNED));
		set Existeiden =(select count(*) from fjlanzamientos where fjlanzamientos.identificador = IdentificadortoSet);	
	
	END WHILE;
	
	INSERT INTO fjlanzamientos
	            (titulo, categorias, codigo, bajada, descripcion, video, link, tags, notas_viejas, identificador, estado, fecha)
	VALUES
	            (ititulo, icategorias, icodigo, ibajada, idescripcion, ivideo, ilink, itags, inotas_viejas, IdentificadortoSet, iestado, ifecha);
		set ExisteLanzamiento=(select last_insert_id());
		set Resultado = (ExisteLanzamiento);
		set Alta = 1;
end if; 
select Resultado, IdentificadortoSet, Alta;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `spfjchecklabymarca` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`aliadasc_am19`@`localhost` PROCEDURE `spfjchecklabymarca`(
in ilaboratario varchar(255),
in imarca varchar(255)
)
    NO SQL
BEGIN
	declare ExisteLabToImport int;
declare Importar bool;
declare TieneMarcasDefinidas int;
declare MarcaDefinida int;

set Importar = false;
set ExisteLabToImport = (select f2.idfjlaboratorios from fjlaboratoriostoimport f inner join fjlaboratorios f2 on f.idffjlaboratorios = f2.idfjlaboratorios and f2.manufacturer = ilaboratario);


if (ExisteLabToImport is null) then 
	set Importar = FALSE  ;
else 
	
	SET TieneMarcasDefinidas = (select count(*) from fjmarcastoimport f inner  join fjmarcas f2 on f2.idjfmarcas = f.idfjmarcas and f2.idlaboratorio = ExisteLabToImport); 
	IF (TieneMarcasDefinidas = 0) then 
		set Importar = TRUE ;
	else 
		SET MarcaDefinida = (select count(*)  from fjmarcastoimport f inner join fjmarcas f2 on f2.idjfmarcas = f.idfjmarcas and f2.idlaboratorio = ExisteLabToImport and f2.brand = imarca);
		IF (MarcaDefinida >= 1) THEN 
			set Importar = TRUE ;
		ELSE
			set Importar = FALSE  ;
		END IF;
	end if; 
	
end if;

select Importar;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
/*!50003 DROP PROCEDURE IF EXISTS `spfjinsertarimagen` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
DELIMITER ;;
CREATE DEFINER=`aliadasc_am19`@`localhost` PROCEDURE `spfjinsertarimagen`(
in inombre varchar(255),
in iidentificador varchar(250), 
in ipath varchar(200), 
in iposicion varchar(100), 
in itipo varchar(100), 
in iorden int, 
in ifecha_alt date
)
    NO SQL
BEGIN
	declare Resultado int;
	INSERT INTO contenidos_imagenes
(nombre, identificador, `path`, posicion, tipo, orden, fecha_alt)
VALUES(inombre, iidentificador, ipath, iposicion, itipo, iorden, ifecha_alt);

set Resultado=(select last_insert_id());
select Resultado;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;