-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.0.51a-log


/*!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 utf8 */;

/*!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' */;


--
-- Create schema SAC
--

CREATE DATABASE IF NOT EXISTS SAC;
USE SAC;

--
-- Definition of procedure `SAC`.`fu_add_platform`
--

DROP PROCEDURE IF EXISTS `SAC`.`fu_add_platform`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`sac`@`172.16.99.%` PROCEDURE  `SAC`.`fu_add_platform`(p_name varchar(50),p_id_user int(11))
BEGIN
insert into platform (name) values (p_name);
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `SAC`.`fu_add_Profile`
--

DROP PROCEDURE IF EXISTS `SAC`.`fu_add_Profile`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`sac`@`172.16.99.%` PROCEDURE  `SAC`.`fu_add_Profile`(p_nombrePerfil varchar(100),p_plataforma varchar(50),p_baja int(11),p_modificar int(11),p_agregar int(11),p_verCampos int(11),p_verReportes int(11),p_id_user int(11))
BEGIN
insert into profile(`description`,`id_platform`,`delete`,`change`,`add`,`view_restricted`,`view_report`) 
values (p_nombrePerfil,p_plataforma,p_baja,p_modificar,p_agregar,p_verCampos,p_verReportes);
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `SAC`.`fu_add_sac_service`
--

DROP PROCEDURE IF EXISTS `SAC`.`fu_add_sac_service`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`sac`@`172.16.99.%` PROCEDURE  `SAC`.`fu_add_sac_service`(p_description varchar(50),p_id_platform int(11),p_info_servicio varchar(11),p_id_user int(11))
BEGIN
insert into service (description,id_platform,name) value (p_description,p_id_platform,p_info_servicio);
select last_insert_id() as"id_service";
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `SAC`.`fu_del_sac`
--

DROP PROCEDURE IF EXISTS `SAC`.`fu_del_sac`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`sac`@`172.16.99.%` PROCEDURE  `SAC`.`fu_del_sac`(p_id int(11),p_table varchar(50),p_id_user int(11))
BEGIN
if p_table='service_ubi' THEN
   delete  from service_ubi where id_service=p_id;
END IF;

if p_table='service' THEN
   delete  from service where id_service=p_id;
END IF;

if p_table='profile' THEN
   delete from `profile` where id_profile=p_id;
END IF;

if p_table='platform' THEN
   delete  from platform where id_platform=p_id;
END IF;

END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `SAC`.`fu_get_dato_platform`
--

DROP PROCEDURE IF EXISTS `SAC`.`fu_get_dato_platform`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`sac`@`172.16.99.%` PROCEDURE  `SAC`.`fu_get_dato_platform`()
BEGIN
select id_platform as "Id",name as "Nombre de Plataforma" from platform;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `SAC`.`fu_get_dato_service`
--

DROP PROCEDURE IF EXISTS `SAC`.`fu_get_dato_service`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`sac`@`172.16.99.%` PROCEDURE  `SAC`.`fu_get_dato_service`(p_id_service int(11),p_opcional int (11))
BEGIN
if (p_opcional=0) then  
select u.description 
from  ubi u 
group by u.description;

else

select u.description,s.description,s.id_platform,u.id_ubi,s.name, s.id_service
from service s , ubi u , service_ubi su 
where s.id_service=su.id_service 
and u.id_ubi=su.id_ubi and s.id_service=p_id_service
group by u.description;

end if;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `SAC`.`fu_get_listservice`
--

DROP PROCEDURE IF EXISTS `SAC`.`fu_get_listservice`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`sac`@`172.16.99.%` PROCEDURE  `SAC`.`fu_get_listservice`()
BEGIN
select id_service as "Id",description as "Nombre del Servicio",s.name as "Descripcion del Servicio",p.name as "Plataforma" 

from service as s join platform as p ON s.id_platform=p.id_platform;


END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `SAC`.`fu_get_platform`
--

DROP PROCEDURE IF EXISTS `SAC`.`fu_get_platform`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`sac`@`172.16.99.%` PROCEDURE  `SAC`.`fu_get_platform`(p_id_platform int(11))
BEGIN
select id_platform,name from platform where id_platform = p_id_platform;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `SAC`.`fu_get_profile`
--

DROP PROCEDURE IF EXISTS `SAC`.`fu_get_profile`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`sac`@`172.16.99.%` PROCEDURE  `SAC`.`fu_get_profile`(in p_id_user int(11),in p_id_platform int(11),in p_id_profile int(11))
BEGIN
IF (p_id_profile = 0) THEN
SELECT
	up.id_profile,
	p.description,
	p.change,
	p.delete,
	p.add,
	view_restricted,
	view_report,
    	pl.name as "plataforma"
FROM user_profile up JOIN profile p ON up.id_profile=p.id_profile
JOIN platform  as pl
ON pl.id_platform = p.id_platform
 where up.id_user=p_id_user and p.id_platform=p_id_platform;

ELSE


	SELECT
		id_profile,			
		description,
		id_platform,
		p.delete as 'del',
		p.change as 'set',
		p.add as 'add',
		view_restricted,
		view_report
		
	FROM 	
		profile as p
	WHERE
		id_profile = p_id_profile;

END IF;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `SAC`.`fu_get_profiles`
--

DROP PROCEDURE IF EXISTS `SAC`.`fu_get_profiles`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`sac`@`172.16.99.%` PROCEDURE  `SAC`.`fu_get_profiles`()
BEGIN
SELECT p.id_profile as "Id",description as "Descripcion del Perfil",name as "Nombre de Plataforma",pl.id_platform as "Id Plataforma" 
FROM profile p join platform pl 
ON p.id_platform = pl.id_platform order by pl.id_platform;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `SAC`.`fu_get_ubi`
--

DROP PROCEDURE IF EXISTS `SAC`.`fu_get_ubi`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`sac`@`172.16.99.%` PROCEDURE  `SAC`.`fu_get_ubi`()
BEGIN
select u.description as nameubis,u.id_ubi,u.id_group,ug.description
from ubi u join ubi_group ug
where u.id_group = ug.id_group  
order by 3,1;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `SAC`.`fu_set_platform`
--

DROP PROCEDURE IF EXISTS `SAC`.`fu_set_platform`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`sac`@`172.16.99.%` PROCEDURE  `SAC`.`fu_set_platform`(p_id_platform int(11),p_name varchar(50),p_id_user int(11))
BEGIN
UPDATE platform
SET
   name=trim(p_name)
WHERE id_platform = p_id_platform; 

END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `SAC`.`fu_set_profile`
--

DROP PROCEDURE IF EXISTS `SAC`.`fu_set_profile`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`sac`@`172.16.99.%` PROCEDURE  `SAC`.`fu_set_profile`(p_description varchar(100),p_id_platform varchar(50),p_delete int(11),p_change int(11),p_add int(11),p_view_restricted int(11),p_view_report int(11),p_id_profile int(11),p_id_user int(11))
BEGIN
UPDATE profile
SET
    description=p_description,
    id_platform=p_id_platform,
    `delete`=p_delete,
    `change`=p_change,
    `add`=p_add,
    view_restricted=p_view_restricted,
    view_report=p_view_report
    
WHERE    
     id_profile=p_id_profile;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `SAC`.`fu_set_sac_ubis`
--

DROP PROCEDURE IF EXISTS `SAC`.`fu_set_sac_ubis`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`sac`@`172.16.99.%` PROCEDURE  `SAC`.`fu_set_sac_ubis`(p_id_service int(11),p_id_ubi int(11))
BEGIN
insert into service_ubi (id_service,id_ubi) value (p_id_service,p_id_ubi);

END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `SAC`.`fu_set_service`
--

DROP PROCEDURE IF EXISTS `SAC`.`fu_set_service`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`sac`@`172.16.99.%` PROCEDURE  `SAC`.`fu_set_service`(p_description varchar(50),p_id_platform int(11),p_id_service int(11),p_info_servicio varchar(50),p_id_user int(11))
BEGIN
update service
set
    id_platform = p_id_platform,
    description = p_description,
    name = p_info_servicio
WHERE 
   id_service = p_id_service;   
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;



/*!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 */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;