SHOW PROCEDURE STATUS WHERE db='productos_profesor_trigger';
DROP PROCEDURE proyectos_area;

/*Procedimiento llamado select_sencillo para una selección simple*/
DELIMITER $$
CREATE PROCEDURE select_sencillo ()
	BEGIN
		SELECT * FROM area;
END $$

/*Seleccionar el área de un profesor recibiendo su número económico*/
DELIMITER $$
CREATE PROCEDURE consulta_area_profesor(IN numero_economico VARCHAR(10))
	BEGIN
		SELECT area.nombre FROM profesor,area
			WHERE area.id_area = profesor.area_id_area
				AND profesor.numero_economico = numero_economico;
END $$

/*Seleccionar el nombre completo de un profesor en base a su número económico*/
DELIMITER $$
CREATE PROCEDURE nombre_profesor(IN numero_economico VARCHAR(10),
									OUT nombre_completo VARCHAR(70))
	BEGIN
		SELECT CONCAT(profesor.nombre,' ',profesor.primer_apellido,' ',
				profesor.segundo_apellido)  INTO nombre_completo FROM
		profesor WHERE profesor.numero_economico = numero_economico;
END $$


/*EJERCICIO*/
/*CREAR UN PROCEDIMIENTO ALMACENADO (proyectos_area)
QUE RECIBA LA CLAVE DEL ÁREA 
Y MUESTRE LOS NOMBRES DE LOS PROYECTOS DE INVESTIGACIÓN QUE ESTÁN ASOCIADOS A ELLA*/

DELIMITER $$
CREATE PROCEDURE proyectos_area (IN clave_area INT)
BEGIN 
	SELECT proyecto_investigacion.nombre FROM proyecto_investigacion
	WHERE area_id_area = clave_area;
END $$


/*EJERCICIO*/
/*
CREAR UN PROCEDIMIENTO ALMACENADO QUE RECIBA LA CLAVE DEL PROYECTO
Y EL ROL PARA MOSTRAR LOS PROFESORES QUE PARTICIPAN EN ESE PROYECTO
CON ESE ROL EN PARTICULAR. EL PROCEDIMIENTO SERÁ: profesores_roles
*/

DELIMITER $$
CREATE PROCEDURE profesores_roles (IN clave_proyecto INT,rol_buscado VARCHAR(15))
	BEGIN 
		SELECT profesor.nombre,profesor.primer_apellido,profesor.segundo_apellido,area.nombre
		FROM profesor,area,profesor_proyecto_investigacion WHERE
		profesor_proyecto_investigacion.profesor_numero_economico = profesor.numero_economico
		AND profesor.area_id_area = area.id_area
		AND profesor_proyecto_investigacion.proyecto_investigacion_id_proyecto_investigacion = clave_proyecto
		AND profesor_proyecto_investigacion.rol = rol_buscado;
END $$


/********PROCEDIMIENTO QUE RECIBE LA INFORMACIÓN DE UN PROYECTO TERMINAL, 
LO INSERTA Y LO RELACIONA CON UN PROFESOR Y CON UN PROYECTO DE INVESTIGACIÓN
******************/

DELIMITER $$
CREATE PROCEDURE insertar_proyecto_terminal(
	IN in_titulo VARCHAR (150),
	IN in_cantidad_alumnos INT,
	IN in_fecha_inicio DATE,
	IN in_fecha_finalizacion DATE,
	IN in_estado VARCHAR(30),
	IN in_proyecto_investigacion INT,
	IN in_numero_economico VARCHAR(12))

BEGIN
	DECLARE puntos INT;
	DECLARE ultimo INT;
	IF (in_estado = 'FINALIZADO') THEN
		SET puntos = 30;
	ELSE
		SET puntos = 0;
	END IF;

	INSERT INTO proyecto_terminal(titulo,cantidad_alumnos,fecha_inicio,
				fecha_finalizacion,puntos,estado,proyecto_investigacion_id_proyecto_investigacion) 
	VALUES (
		in_titulo,
		in_cantidad_alumnos,
		in_fecha_inicio,
		in_fecha_finalizacion,
		puntos,
		in_estado,
		in_proyecto_investigacion	
	);

		SELECT MAX(id_proyecto_terminal) INTO ultimo FROM proyecto_terminal;

	INSERT INTO profesor_proyecto_terminal(profesor_numero_economico,proyecto_terminal_id_proyecto_terminal)
		
		VALUES (in_numero_economico,ultimo);

END $$


/*******************MISMAS OPERACIONES CON CASE****************/
DELIMITER $$
CREATE PROCEDURE insertar_proyecto_terminal_case(
	IN in_titulo VARCHAR (150),
	IN in_cantidad_alumnos INT,
	IN in_fecha_inicio DATE,
	IN in_fecha_finalizacion DATE,
	IN in_estado VARCHAR(30),
	IN in_proyecto_investigacion INT,
	IN in_numero_economico VARCHAR(12))

BEGIN
	DECLARE puntos INT;
	DECLARE ultimo INT;

	CASE in_estado
		WHEN 'FINALIZADO' THEN
			SET puntos = 30;
		ELSE 
			SET puntos = 0;
	END CASE;

	INSERT INTO proyecto_terminal(titulo,cantidad_alumnos,fecha_inicio,
				fecha_finalizacion,puntos,estado,proyecto_investigacion_id_proyecto_investigacion) 
	VALUES (
		in_titulo,
		in_cantidad_alumnos,
		in_fecha_inicio,
		in_fecha_finalizacion,
		puntos,
		in_estado,
		in_proyecto_investigacion	
	);

		SELECT MAX(id_proyecto_terminal) INTO ultimo FROM proyecto_terminal;

	INSERT INTO profesor_proyecto_terminal(profesor_numero_economico,proyecto_terminal_id_proyecto_terminal)
		
		VALUES (in_numero_economico,ultimo);

END $$


/***PROCEDIMIENTO PARA INSERTAR UN ARTÍCULO Y RELACIONARLO CON UNA REVISTA***/
DELIMITER $$
	CREATE PROCEDURE insertar_articulo(
		IN titulo_a VARCHAR(100),
		IN fecha_p_a DATE,
		IN estado_a VARCHAR(10),
		IN proyecto_a INT,
		IN revista_a VARCHAR(15),
		IN numero_economico_a VARCHAR(10)
)
BEGIN
		DECLARE puntos_a INT;
		DECLARE ultimo INT;
		DECLARE tipo_revista VARCHAR(15);

		SELECT tipo INTO tipo_revista FROM revista WHERE isbn = revista_a;

	
IF (tipo_revista = 'INDEXADA' AND estado_a = 'FINALIZADO') THEN
			SET puntos_a = 100;
		ELSEIF (tipo_revista = 'NO INDEXADA' AND estado_a = 'FINALIZADO') THEN
			SET puntos_a = 80;
		ELSEIF (tipo_revista = 'INDEXADA' AND estado_a = 'EN PROCESO') THEN
			SET puntos_a = 0;
		ELSEIF (tipo_revista = 'NO INDEXADA' AND estado_a = 'EN PROCESO') THEN
			SET puntos_a = 0;
		END IF;

	INSERT INTO articulo_investigacion (
		titulo,
		fecha_publicacion,
		puntos,
		estado,
		proyecto_investigacion_id_proyecto_investigacion,
		revista_isbn)
		VALUES (
		titulo_a,
		fecha_p_a,
		puntos_a,
		estado_a,
		proyecto_a,
		revista_a);

	SELECT MAX(id_articulo_investigacion) INTO ultimo FROM articulo_investigacion;

INSERT INTO profesor_articulo_investigacion(profesor_numero_economico,articulo_investigacion_id_articulo_investigacion)
		VALUES (numero_economico_a,ultimo);

END $$

/***********ANIDANDO IF**********************/
DELIMITER $$
	CREATE PROCEDURE insertar_articulo_anidado(
		IN titulo_a VARCHAR(100),
		IN fecha_p_a DATE,
		IN estado_a VARCHAR(10),
		IN proyecto_a INT,
		IN revista_a VARCHAR(15),
		IN numero_economico_a VARCHAR(10)
)
BEGIN
		DECLARE puntos_a INT;
		DECLARE ultimo INT;
		DECLARE tipo_revista VARCHAR(15);

		SELECT tipo INTO tipo_revista FROM revista WHERE isbn = revista_a;

	IF(estado_a = 'FINALIZADO') THEN
	IF(tipo_revista = 'INDEXADA' )THEN 
		SET puntos_a = 100;
	ELSE
		SET puntos_a = 80;
	END IF;
ELSE 
	SET puntos_a = 0;
	END IF;

	INSERT INTO articulo_investigacion (
		titulo,
		fecha_publicacion,
		puntos,
		estado,
		proyecto_investigacion_id_proyecto_investigacion,
		revista_isbn)
		VALUES (
		titulo_a,
		fecha_p_a,
		puntos_a,
		estado_a,
		proyecto_a,
		revista_a);

	SELECT MAX(id_articulo_investigacion) INTO ultimo FROM articulo_investigacion;

INSERT INTO profesor_articulo_investigacion(profesor_numero_economico,articulo_investigacion_id_articulo_investigacion)
		VALUES (numero_economico_a,ultimo);

END $$




/**ITERANDO EN UN SP PARA OBTENER LOS PUNTOS DE UN PROFESOR EN ARTÍCULOS DE INVESTIGACIÓN**/

DELIMITER $$

	CREATE PROCEDURE puntos_profesor (IN in_numero_economico VARCHAR(10),OUT total INT) 
		BEGIN

			DECLARE fin_registros BOOLEAN;
			DECLARE punto_articulo INT DEFAULT 0;
			DECLARE total_puntos INT DEFAULT 0;
			

			DECLARE puntos_cursor CURSOR FOR 
				SELECT articulo_investigacion.puntos FROM articulo_investigacion,
				profesor_articulo_investigacion WHERE 
				profesor_articulo_investigacion.articulo_investigacion_id_articulo_investigacion 
					= articulo_investigacion.id_articulo_investigacion
				AND profesor_articulo_investigacion.profesor_numero_economico = in_numero_economico;
			
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin_registros = TRUE;

			OPEN puntos_cursor;

			ciclo_loop: LOOP
				FETCH puntos_cursor INTO punto_articulo;

				IF fin_registros THEN 
					LEAVE ciclo_loop;
				END IF;

				SET total_puntos = total_puntos + punto_articulo;
				SET punto_articulo = 0;
			END LOOP ciclo_loop;

				SET total = total_puntos;

END $$

CALL puntos_profesor('1234',@total);
SELECT @total AS 'Total de Puntos';

/**ITERANDO EN UN SP CON WHILE PARA OBTENER LOS PUNTOS DE UN PROFESOR EN ARTÍCULOS DE INVESTIGACIÓN**/
DELIMITER $$

	CREATE PROCEDURE puntos_profesor_while (IN in_numero_economico VARCHAR(10),OUT total INT) 
		BEGIN

			DECLARE fin_registros INT DEFAULT 0;
			DECLARE punto_articulo INT DEFAULT 0;
			DECLARE total_puntos INT DEFAULT 0;
			

			DECLARE puntos_cursor CURSOR FOR 
				SELECT articulo_investigacion.puntos FROM articulo_investigacion,
				profesor_articulo_investigacion WHERE 
				profesor_articulo_investigacion.articulo_investigacion_id_articulo_investigacion 
					= articulo_investigacion.id_articulo_investigacion
				AND profesor_articulo_investigacion.profesor_numero_economico = in_numero_economico;
			
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin_registros = 1;

			OPEN puntos_cursor;

			WHILE (fin_registros = 0) DO
				FETCH puntos_cursor INTO punto_articulo;
				SET total_puntos = total_puntos + punto_articulo;
				SET punto_articulo = 0;
			END WHILE;

				SET total = total_puntos;

END $$


/*********STORED PROCEDURE CON ROLLBACK*************/
DELIMITER $$
CREATE PROCEDURE insertar_proyecto_terminal_protegido(
	IN in_titulo VARCHAR (150),
	IN in_cantidad_alumnos INT,
	IN in_fecha_inicio DATE,
	IN in_fecha_finalizacion DATE,
	IN in_estado VARCHAR(30),
	IN in_proyecto_investigacion INT,
	IN in_numero_economico VARCHAR(12))
BEGIN

	

	DECLARE puntos INT;
	DECLARE ultimo INT;

	DECLARE EXIT HANDLER for sqlexception
  BEGIN
  ROLLBACK;
END;


	IF (in_estado = 'FINALIZADO') THEN
		SET puntos = 30;
	ELSE
		SET puntos = 0;
	END IF;



 START TRANSACTION;

	INSERT INTO proyecto_terminal(titulo,cantidad_alumnos,fecha_inicio,
				fecha_finalizacion,puntos,estado,proyecto_investigacion_id_proyecto_investigacion) 
	VALUES (
		in_titulo,
		in_cantidad_alumnos,
		in_fecha_inicio,
		in_fecha_finalizacion,
		puntos,
		in_estado,
		in_proyecto_investigacion	
	);

		SELECT MAX(id_proyecto_terminal) INTO ultimo FROM proyecto_terminal;

	INSERT INTO profesor_proyecto_terminal(profesor_numero_economico,proyecto_terminal_id_proyecto_terminal)
		
		VALUES (in_numero_economico,ultimo);

	

COMMIT;
END $$
