 --
 -- Dumping routines for database 'ALUC'
 --
+/*!50003 DROP PROCEDURE IF EXISTS `insertar_reserva` */;
+/*!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  = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection  = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+CREATE DEFINER=`root`@`localhost` PROCEDURE `insertar_reserva`(
+						
+						IN Sid_usuario varchar(10),
+						IN Sid_laboratorio INT(11),
+						IN Sdescripcion varchar(60),		
+						IN Sn_usuarios int(11),
+						IN Stipo_uso varchar(45),
+						IN Sestado varchar(45),
+						IN Sfecha DATE,
+						IN Shora_inicio TIME,
+						IN Shora_fin TIME,
+						IN Scodigo_secreto varchar(100))
+BEGIN
+    /*Variables para el desarrollo*/
+	declare cupos int;
+    declare ocupados int;
+    declare valor int;
+    
+    /*declaracion de las excepsiones*/
+    
+    
+    start transaction;
+    
+    SELECT capacidad into cupos
+		from laboratorio 
+		where id = Sid_laboratorio;
+                    
+    SELECT sum(n_usuarios) into ocupados
+		FROM reserva join reservacion on reserva.id = reservacion.id_reserva
+		where id_laboratorio = Sid_laboratorio  and estado = "Reservado" and 
+		TIMESTAMP(fecha,hora_inicio) 
+		between TIMESTAMP(fecha,hora_inicio) 
+		and TIMESTAMP(fecha,hora_fin);
+    
+    set valor = cupos - ocupados;
+    insert into prueba values(cupos, ocupados, valor);
+    if (valor > 0 and Sn_usuarios <= valor) then
+		INSERT INTO ALUC.reserva  
+			values(NULL,Sn_usuarios,Sdescripcion,Stipo_uso,Scodigo_secreto);
+        INSERT INTO ALUC.reservacion 
+			values(NULL, Sid_laboratorio,Sid_usuario, Sestado, Sfecha, Shora_inicio, Shora_fin);
+		commit;
+	else
+		signal sqlstate "45000" set message_text = "No hay cupos ";
+	end if;
+    
+    
+
+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 */ ;
 
 --
 -- Final view structure for view `view_administrador`

  `tipo_uso` varchar(45) DEFAULT NULL,
  `codigo_secreto` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--

/*!50001 CREATE VIEW `view_administrador` AS SELECT 
 1 AS `id`,
 1 AS `nombre`*/;
SET character_set_client = @saved_cs_client;

--
-- Temporary view structure for view `view_laboratorio`
--

DROP TABLE IF EXISTS `view_laboratorio`;
/*!50001 DROP VIEW IF EXISTS `view_laboratorio`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE VIEW `view_laboratorio` AS SELECT 
 1 AS `id`,
 1 AS `nombre`,
 1 AS `capacidad`,
 1 AS `descripcion`,
 1 AS `j1_hora_apertura`,
 1 AS `j1_hora_cierre`,
 1 AS `j2_hora_apertura`,
 1 AS `j2_hora_cierre`*/;
SET character_set_client = @saved_cs_client;

--
