ehcontrol_estructure.sql

-- phpMyAdmin SQL Dump
-- version 3.4.10.1deb1
-- http://www.phpmyadmin.net
--
-- Servidor: localhost
-- Tiempo de generación: 23-05-2014 a las 10:53:20
-- Versión del servidor: 5.5.35
-- Versión de PHP: 5.3.10-1ubuntu3.10

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


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

--
-- Base de datos: `ehcontrol`
--

DELIMITER $$
--
-- Procedimientos
--
DROP PROCEDURE IF EXISTS `addcommandprogram`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `addcommandprogram`( IN u VARCHAR(50), IN c VARCHAR(50), IN idpa INTEGER, IN n INTEGER)
    COMMENT 'add an acction to a command.'
begin

	DECLARE num INTEGER DEFAULT 0;
	DECLARE ida, idu, idh, idc INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;

end_proc:begin
	IF (u IS NULL OR c IS NULL OR idpa IS NULL OR n IS NULL ) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), USERS.IDUSER INTO num, idu
	FROM PROGRAMACTIONS, USERS
	WHERE IDPROGRAM = idpa AND PROGRAMACTIONS.IDUSER = USERS.IDUSER AND USERNAME = u;
	
	CASE num 
	WHEN 1 THEN
	
		SELECT COUNT(*), IDCOMMAND INTO num, idc
		FROM COMMANDS
		WHERE COMMANDNAME = c AND IDUSER = idu;
		
		CASE num
		WHEN 1 THEN
			SELECT COUNT(*) INTO num
			FROM COMMAND_PROGRAM
			WHERE IDCOMMAND = idc AND POS = n;
			
			CASE num
			WHEN 0 THEN
				IF (n > 0) THEN 
					INSERT INTO COMMAND_PROGRAM (IDCOMMAND, IDPROGRAM, POS) VALUE (idc, idpa, n);
					SET err = 56;
				ELSE
					SET err = 57;
				END IF;
			ELSE
				SET err = 55;
			END CASE;
		ELSE
			SET err = 60;
		END CASE;
	WHEN 0 THEN
		SET err = 33;
	ELSE
		SET err = 4;
	END CASE;
	
	SELECT IDHOUSE INTO idh
	FROM loginVIEW
	WHERE IDACTION = ida AND USERNAME = u;
end;
	
	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,  idu,    idh,  IF(err = 56, 0, err),  28, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 56, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;

end$$

DROP PROCEDURE IF EXISTS `addtaskprogram`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `addtaskprogram`( IN u VARCHAR(50), IN t VARCHAR(50),IN idpa INTEGER)
    COMMENT 'add an acction to a task.'
begin
	DECLARE num INTEGER DEFAULT 0;
	DECLARE ida, idu, idh, idta INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;

end_proc:begin
	IF (u IS NULL OR t IS NULL OR idpa IS NULL ) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IDACTION, USERS.IDUSER INTO num, ida, idu
	FROM PROGRAMACTIONS, USERS
	WHERE IDPROGRAM = idpa AND PROGRAMACTIONS.IDUSER = USERS.IDUSER AND USERNAME = u;
	
	CASE num 
	WHEN 1 THEN
	
		SELECT COUNT(*), IDTASK  INTO num, idta
		FROM TASKS
		WHERE TASKNAME = t AND IDUSER = idu;
		
		CASE num
		WHEN 1 THEN
			SELECT COUNT(*) INTO num
			FROM TASKPROGRAM
			WHERE IDTASK = idta AND IDPROGRAM = idpa;
			
			CASE num
			WHEN 0 THEN
				INSERT INTO TASKPROGRAM (IDTASK, IDPROGRAM) VALUE (idta, idpa);
				SET err = 34;
			ELSE
				SET err = 37;
			END CASE;
		ELSE
			SET err = 32;
		END CASE;
	WHEN 0 THEN
		SET err = 33;
	ELSE
		SET err = 4;
	END CASE;
	
	SELECT IDHOUSE INTO idh
	FROM loginVIEW 
	WHERE IDACTION = ida AND IDUSER = idu;
end;
	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,  idu,    idh,  IF(err = 34, 0, err),  21, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 34, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;

end$$

DROP PROCEDURE IF EXISTS `createaccesshouse`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `createaccesshouse`( IN u VARCHAR(50), IN h VARCHAR(50), IN u2 VARCHAR(50), IN n INTEGER)
    COMMENT 'An admistrator grant access somobody to a house.'
begin

	DECLARE num,acc INTEGER DEFAULT 0;
	DECLARE idu, idh INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;

end_proc:begin
	IF (u IS NULL OR h IS NULL OR u2 IS NULL OR n IS NULL ) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IFNULL(ACCESSNUMBER, 0), IDUSER, IDHOUSE INTO num, acc, idu, idh
	FROM loginVIEW
	WHERE USERNAME = u AND HOUSENAME = h;
			
	CASE num 
	WHEN 0 THEN
		SET err = 11;
 	ELSE
		CASE acc
		WHEN 1 THEN 
			SELECT COUNT(*), IDUSER INTO num, idu 
			FROM USERS
			WHERE USERNAME = u2;

			IF (num <> 0) THEN 
				DELETE FROM ACCESSHOUSE WHERE IDUSER=idu AND IDHOUSE = idh;
				INSERT INTO ACCESSHOUSE (IDUSER, IDHOUSE, ACCESSNUMBER, DATEBEGIN) VALUES
										(idu,    idh, n, CURRENT_TIMESTAMP);
				SET err = 40;
			ELSE
				SET err = 3;
			END IF;
		WHEN 0 THEN
			SET err = 11;
		ELSE
			SET err = 39;
		END CASE;
	END CASE;
end;

	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,    idu,   idh,  IF(err = 40, 0, err),  24, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 40, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;
end$$

DROP PROCEDURE IF EXISTS `createcommand`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `createcommand`( IN u VARCHAR(50), IN c VARCHAR(50))
    COMMENT 'Create a new command (conjunto de mandatos)'
begin

	DECLARE num INTEGER DEFAULT 0;
	DECLARE idu INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;

end_proc:begin
	IF (u IS NULL OR c IS NULL ) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

    SELECT COUNT(*), IDUSER INTO num, idu
	FROM USERS
	WHERE USERNAME = u;
			
	CASE num 
	WHEN 1 THEN 
		SELECT COUNT(*) INTO num
		FROM COMMANDS
		WHERE COMMANDNAME = c AND IDUSER = idu;

		CASE num
		WHEN 0 THEN
			INSERT INTO COMMANDS (IDCOMMAND, COMMANDNAME, IDUSER) VALUES
								(NULL, c, idu);
			SET err = 52; 
		ELSE
			SET err = 51;
		END CASE;
	WHEN 0 THEN
		SET err = 3;
	ELSE
		SET err = 4;
	END CASE;
end;

	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,  idu,    NULL,  IF(err = 52, 0, err),  26, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 52, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;
end$$

DROP PROCEDURE IF EXISTS `createdevice`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `createdevice`( IN u VARCHAR(50), IN ip VARCHAR(500), IN s VARCHAR(50), IN d VARCHAR(50))
    COMMENT 'Create a new device.'
begin

	DECLARE num, v INTEGER DEFAULT 0;
	DECLARE idu, idd, idh, i INTEGER DEFAULT NULL;
	DECLARE err, done, ids INTEGER DEFAULT 0;
	DECLARE ser, t, st, en, sp VARCHAR(50);
	DECLARE a, fc VARCHAR(50);

	DECLARE cur CURSOR FOR SELECT  IDSERVICE, `SERVICENAME`, TYPE, `SERVICEINTERFACE`, `STATUS`, `ENGLISH`, `SPANISH`
						FROM SERVICES
						WHERE IDDEVICE = idd; 

	DECLARE cur2 CURSOR FOR SELECT  ACTIONNAME, `FCODE`
						FROM ACTIONS
						WHERE IDSERVICE= ids; 
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

end_proc:begin
	IF (u IS NULL OR d IS NULL ) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IDUSER INTO num, idu
	FROM USERS
	WHERE USERNAME = u;
			
	CASE num 
	WHEN 1 THEN 
			SELECT COUNT(*), IDDEVICE INTO num, idd
			FROM DEVICES
			WHERE SERIAL = s AND IPADDRESS = ip;
				
			CASE num
			WHEN 0 THEN 
				SELECT COUNT(*), IDDEVICE, ENGLISH, SPANISH, VERSION INTO num, idd, en, sp, v
				FROM DEVICES
				WHERE DEVICENAME = d AND IDUSER IS NULL AND IPADDRESS IS NULL AND SERIAL IS NULL
				HAVING MAX(VERSION);
				
				CASE num 
				WHEN 1 THEN
					INSERT INTO DEVICES (IDDEVICE, IDUSER, IPADDRESS, SERIAL, DEVICENAME, ENGLISH, SPANISH, `DATE`, VERSION) VALUES
										(NULL,   idu,    ip,    s,   d,   en, sp,   CURRENT_TIMESTAMP,   v);
					
					OPEN cur;

					SELECT IDDEVICE INTO idd
					FROM DEVICES
					WHERE SERIAL = s AND IPADDRESS = ip;

					LOOP1:LOOP
						FETCH cur INTO ids, ser, t, i, st, en, sp; 
						IF done THEN 
							CLOSE cur;
							LEAVE LOOP1;

						END IF; 

						INSERT INTO SERVICES(IDSERVICE, IDROOM, IDDEVICE, SERVICENAME, TYPE, SERVICEINTERFACE, STATUS, ENGLISH, SPANISH) 
									VALUES (NULL, NULL, idd, ser, t, i, st, en, sp); 
						OPEN cur2; 

						SELECT IDSERVICE INTO ids 
						FROM SERVICES 
						WHERE IDDEVICE = idd AND SERVICENAME =ser;

						LOOP2:LOOP
							FETCH cur2 INTO a, fc;
							IF done THEN
								SET done = 0;
								CLOSE cur2;
								LEAVE LOOP2;
							END IF;

							INSERT INTO ACTIONS(`IDACTION`, `IDSERVICE`, `ACTIONNAME`, `ENGLISH`, `SPANISH`, `FCODE`)
										VALUES (NULL, ids, a, NULL,NULL, fc);
						END LOOP LOOP2;
					END LOOP LOOP1;

					SET err = 49;
				ELSE
					SET err = 48;
				END CASE;
			ELSE
				SET err = 47;
			END CASE;
	WHEN 0 THEN
		SET err = 3;
	ELSE
		SET err = 4;
	END CASE;
end;

	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,    idu,   idh,  IF(err = 49, 0, err),  25, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 49, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;
end$$

DROP PROCEDURE IF EXISTS `createhouse`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `createhouse`( IN u VARCHAR(50), IN h VARCHAR(50), IN c VARCHAR(50), IN ctry VARCHAR(50))
    COMMENT 'Create a new house if not exist.'
begin

	DECLARE num INTEGER DEFAULT 0;
	DECLARE idu, idh INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;

end_proc:begin
	IF (u IS NULL OR h IS NULL OR c IS NULL OR ctry IS NULL ) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IDUSER INTO num, idu
	FROM USERS
	WHERE USERNAME = u;
			
	CASE num 
	WHEN 1 THEN 
			SELECT COUNT(*), IDHOUSE INTO num, idh
			FROM HOUSES
			WHERE HOUSENAME = h;
				
			CASE num
			WHEN 0 THEN 
				INSERT INTO HOUSES  (IDHOUSE, IDUSER, HOUSENAME, IDIMAGE, CITY, COUNTRY, GPS, DATEBEGIN) VALUES
									(null, idu, h,NULL, c, ctry ,NULL,CURRENT_TIMESTAMP);
				SELECT IDHOUSE INTO idh
				FROM HOUSES
				WHERE HOUSENAME = h;
				
				INSERT INTO ACCESSHOUSE (IDUSER, IDHOUSE, ACCESSNUMBER, DATEBEGIN) VALUES
										(idu,    idh, 1, CURRENT_TIMESTAMP);
				SET err = 17;
			WHEN 1 THEN
				SET err = 22;
			ELSE
				SET err = 4;
			END CASE;
	WHEN 0 THEN
		SET err = 3;
	ELSE
		SET err = 4;
	END CASE;
end;
	
	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,    idu,   idh,  IF(err = 17, 0, err),  7, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 17, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;
end$$

DROP PROCEDURE IF EXISTS `createpermissionservice`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `createpermissionservice`( IN u VARCHAR(50), IN h VARCHAR(50), IN r VARCHAR(50), IN s VARCHAR(50), IN u2 VARCHAR(50), IN n INTEGER)
    COMMENT 'An admistrator give permission to somobody to a service.'
begin

	DECLARE num,acc INTEGER DEFAULT 0;
	DECLARE idu, ids INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;

end_proc:begin
	IF (u IS NULL OR h IS NULL OR u2 IS NULL OR r IS NULL OR n IS NULL ) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IFNULL(ACCESSNUMBER, 0), IDUSER, IDSERVICE INTO num, acc, idu, ids
	FROM loginVIEW
	WHERE USERNAME = u AND HOUSENAME = h AND ROOMNAME = r AND SERVICENAME = s;
			
	CASE num 
	WHEN 0 THEN
		SET err = 11;
	ELSE
		SELECT COUNT(*), IDUSER INTO num, idu 
		FROM USERS
		WHERE USERNAME = u2;	
			
		CASE acc
		WHEN 1 THEN 
			DELETE FROM PERMISSIONS WHERE IDUSER=idu AND IDSERVICE = ids;
			INSERT INTO PERMISSIONS (`IDUSER`, `IDSERVICE`, `PERMISSIONNUMBER`, `DATEBEGIN`) VALUES
										(idu,    ids, n, CURRENT_TIMESTAMP);
			SET err = 71;
		ELSE
			IF (n <> 1) THEN 
				DELETE FROM PERMISSIONS WHERE IDUSER=idu AND IDSERVICE = ids;
				INSERT INTO PERMISSIONS (`IDUSER`, `IDSERVICE`, `PERMISSIONNUMBER`, `DATEBEGIN`) VALUES
										(idu,    ids, n, CURRENT_TIMESTAMP);
				SET err = 71;
			ELSE
				SET err = 39;
			END IF;
		END CASE;
	END CASE;
end;

	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,    idu,   NULL,  IF(err = 71, 0, err),  34, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 71, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;
end$$

DROP PROCEDURE IF EXISTS `createprogramaction`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `createprogramaction`( IN pa VARCHAR(50), IN u VARCHAR(50), IN h VARCHAR(50),IN r VARCHAR(50),IN s VARCHAR(50), IN a VARCHAR(50),IN dat VARCHAR(50), IN t timestamp, IN d timestamp)
    COMMENT 'Program an action to be done.'
begin
	DECLARE num, acc INTEGER DEFAULT 0;
	DECLARE ida, idu, idh, ids INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;
	
end_proc:begin
	IF (pa IS NULL OR u IS NULL OR h IS NULL OR r IS NULL OR s IS NULL OR a IS NULL ) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IDACTION, IDSERVICE, IDHOUSE INTO num, ida, ids, idh
	FROM houseRoomServiceActionVIEW
	WHERE HOUSENAME = h AND ROOMNAME = r AND SERVICENAME = s AND ACTIONNAME = a; 
	
	CASE num  
	WHEN 1 THEN  
			SELECT COUNT(*), IDUSER, IFNULL(ACCESSNUMBER, 0) INTO num, idu, acc 
			FROM loginVIEW
			WHERE USERNAME = u AND IDHOUSE = idh ;
			
			CASE num
			WHEN 0 THEN
				SET err = 11; 
			ELSE
				CASE acc
				WHEN 1 THEN
					SELECT COUNT(*) INTO num
					FROM PROGRAMACTIONS 
					WHERE IDUSER = idu AND PROGRAMNAME = pa ;

					CASE num
					WHEN 0 THEN
						INSERT INTO `PROGRAMACTIONS` (`IDPROGRAM`, `PROGRAMNAME`, `IDUSER`, `IDACTION`, `DATA`, `STARTTIME`, `DATEBEGIN`)
											 VALUES (NULL, pa, idu, ida, dat, t, d);
						SET err = 75;
					ELSE
						SET err = 74;
					END CASE;
				ELSE
					SELECT COUNT(*), IFNULL(PERMISSIONNUMBER, 0) INTO num, acc 
					FROM PERMISSIONS
					WHERE IDUSER = idu AND IDSERVICE= ids ;
					
					CASE acc
					WHEN 1 THEN
						SELECT COUNT(*) INTO num
						FROM PROGRAMACTIONS 
						WHERE IDUSER = idu AND PROGRAMNAME = pa ;

						CASE num
						WHEN 0 THEN
							INSERT INTO `PROGRAMACTIONS` (`IDPROGRAM`, `PROGRAMNAME`, `IDUSER`, `IDACTION`, `DATA`, `STARTTIME`, `DATEBEGIN`) 
											VALUES 		 (NULL, pa, idu, ida, dat, t, d);
							SET err = 75;
						ELSE
							SET err = 74;
						END CASE;
					ELSE
						SET err = 10;
					END CASE;
				END CASE;
			END CASE;
	WHEN 0 THEN
		SET err = 21;
	ELSE
		SET err = 4;
	END CASE;
end;

	SELECT IDHOUSE INTO  idh
	FROM HOUSES
	WHERE HOUSENAME = h;

	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,  idu,    idh,  IF(err = 75, 0, err),  14, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 75, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;

end$$

DROP PROCEDURE IF EXISTS `createroom`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `createroom`( IN u VARCHAR(50), IN h VARCHAR(50), IN r VARCHAR(50))
    COMMENT 'Create a new room if not exist.'
begin

	DECLARE num, acc INTEGER DEFAULT 0;
	DECLARE idu, idh, idr INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;

end_proc:begin
	IF (u IS NULL OR h IS NULL OR r IS NULL ) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IFNULL(ACCESSNUMBER, 0), IDUSER, IDHOUSE INTO num, acc, idu, idh
	FROM loginVIEW
	WHERE USERNAME = u AND HOUSENAME = h;
			
	CASE num 
	WHEN 0 THEN
		SET err = 11;
	ELSE
		CASE acc
		WHEN 1 THEN 
			SELECT COUNT(*) INTO num
			FROM ROOMS
			WHERE ROOMNAME = r AND IDHOUSE = idh;
				
			CASE num
			WHEN 0 THEN 
				INSERT INTO ROOMS  (IDROOM, IDHOUSE, IDUSER, ROOMNAME, `TYPE`, DATEBEGIN) VALUES
									(null, idh, idu, r,0, CURRENT_TIMESTAMP);
				SET err = 44;
			WHEN 1 THEN
				SET err = 43;
			ELSE
				SET err = 4;
			END CASE;
		WHEN 0 THEN
			SET err = 11;
		ELSE
			SET err = 39;
		END CASE;
	END CASE;
end;
	
	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,    idu,   idh,  IF(err = 44, 0, err),  17, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 44, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;
end$$

DROP PROCEDURE IF EXISTS `createtask`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `createtask`( IN u VARCHAR(50), IN ta VARCHAR(50),IN des VARCHAR(50),IN fre timestamp)
    COMMENT 'Create a task, will group programaction.'
begin
	DECLARE num INTEGER DEFAULT 0;
	DECLARE idu INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;
	
end_proc:begin
	IF (u IS NULL OR ta IS NULL) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IDUSER INTO num, idu
	FROM USERS
	WHERE USERNAME = u;
	
	CASE num 
	WHEN 1 THEN 
		SELECT COUNT(*) INTO num
		FROM TASKS
		WHERE IDUSER = idu AND TASKNAME = ta;
		
		CASE num 
		WHEN 0 THEN 
			INSERT INTO `TASKS` (`IDTASK`, `TASKNAME`, `IDUSER`, `DESCRIPTION`, ` FREQUENCY`, `DATEBEGIN`) 
					VALUES	(NULL, ta, idu, des, fre, CURRENT_TIMESTAMP);
			SET err = 29;
		WHEN 1 THEN 
			SET err = 24;
		ELSE
			SET err = 4;
		END CASE;
	WHEN 0 THEN
		SET err = 3;
	ELSE
		SET err = 4;
	END CASE;
end;

	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,  idu,    null,  IF(err = 29, 0, err),  11, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 29, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;

end$$

DROP PROCEDURE IF EXISTS `createuser`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `createuser`( IN u VARCHAR(50), IN p VARCHAR(50), IN mail VARCHAR(50), h VARCHAR(50), c VARCHAR(50))
    COMMENT 'Create a new user if username not exist and email have not an account yet.'
begin

	DECLARE num INTEGER DEFAULT 0;
	DECLARE idu INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;

end_proc:begin
	IF (u IS NULL OR p IS NULL OR mail IS NULL OR h IS NULL OR c IS NULL ) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

        SELECT COUNT(*) INTO num
        FROM
        (
                SELECT IDUSER
                FROM USERS
                WHERE USERNAME = u
         UNION 
                SELECT IDUSER
                FROM REGISTRATIONS
                WHERE USERNAME = u
        ) AS TOTAL;
			
	CASE num 
	WHEN 0 THEN 
			SELECT COUNT(*) INTO num
			FROM
			(
				SELECT IDUSER
				FROM USERS
				WHERE EMAIL = mail
			UNION 
				SELECT IDUSER 
				FROM REGISTRATIONS
				WHERE EMAIL = mail
			) AS TOTAL;

			CASE num
			WHEN 0 THEN 
				INSERT INTO `REGISTRATIONS` (`IDUSER`, `USERNAME`, `PASSWORD`, `EMAIL`, `HINT`, `DATEBEGIN`, CODECONFIRM) VALUES
									(NULL, u, p, mail, h, CURRENT_TIMESTAMP, c);

				SET err = 13;
			WHEN 1 THEN
				SET err = 7; 
			ELSE
				SET err = 4;
			END CASE;
	WHEN 1 THEN
		SET err = 6;
	ELSE
		SET err = 4;
	END CASE;
end;

	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,  idu,    NULL,  IF(err = 13, 0, err),  3, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 13, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;
end$$

DROP PROCEDURE IF EXISTS `deleteaccesshouse`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteaccesshouse`( IN u VARCHAR(50), IN h VARCHAR(50), IN u2 VARCHAR(50))
    COMMENT 'Delete the access to a house of an user.'
begin

	DECLARE num,acc INTEGER DEFAULT 0;
	DECLARE idu, idh INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;

end_proc:begin
	IF (u IS NULL OR h IS NULL OR u2 IS NULL) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IFNULL(ACCESSNUMBER, 0), IDUSER, IDHOUSE INTO num, acc, idu, idh
	FROM loginVIEW
	WHERE USERNAME = u AND HOUSENAME = h;
			
	CASE num 
	WHEN 0 THEN
		SET err = 11;
	ELSE
		CASE acc
		WHEN 1 THEN 
			SELECT COUNT(*), IDUSER INTO num, idu
			FROM USERS
			WHERE USERNAME = u2;

			IF (num <> 0) THEN 
				SELECT COUNT(*) INTO num
				FROM ACCESSHOUSE
				WHERE IDHOUSE = idh AND ACCESSNUMBER = 1 AND IDUSER <> idu;

				CASE num 
				WHEN 0 THEN
					SET err = 63;
				ELSE
					DELETE FROM ACCESSHOUSE WHERE IDUSER=idu AND IDHOUSE = idh;
					SET err = 41;
				END CASE;
			ELSE
				SET err = 3;
			END IF;
		WHEN 0 THEN
			SET err = 11;
		ELSE
			SET err = 39;
		END CASE;
	END CASE;
end;
	
	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,    idu,   idh,  IF(err = 41, 0, err),  24, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 41, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;
end$$

DROP PROCEDURE IF EXISTS `deletecommand`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `deletecommand`( IN u VARCHAR(50), IN c VARCHAR(50))
    COMMENT 'Delete a command from an user (conjunto de mandatos)'
begin

	DECLARE num INTEGER DEFAULT 0;
	DECLARE idu INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;

end_proc:begin
	IF (u IS NULL OR c IS NULL OR idpa IS NULL OR n IS NULL ) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

    SELECT COUNT(*), IDUSER INTO num, idu
	FROM USERS
	WHERE USERNAME = u;
			
	CASE num 
	WHEN 1 THEN 
		SELECT COUNT(*) INTO num
		FROM COMMANDS
		WHERE COMMANDNAME = c AND IDUSER = idu;

		CASE num
		WHEN 1 THEN
			DELETE FROM COMMANDS WHERE COMMANDNAME = c AND IDUSER = idu;
			SET err = 54; 
		ELSE
			SET err = 53;
		END CASE;
	WHEN 0 THEN
		SET err = 3;
	ELSE
		SET err = 4;
	END CASE;
end;

	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,  idu,    NULL,  IF(err = 54, 0, err),  27, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 54, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;
end$$

DROP PROCEDURE IF EXISTS `deletedevice`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `deletedevice`( IN u VARCHAR(50),IN p VARCHAR(50), IN idd INTEGER)
    COMMENT 'Delete a device.'
begin

	DECLARE num INTEGER DEFAULT 0;
	DECLARE idu INTEGER DEFAULT NULL;  
	DECLARE err INTEGER DEFAULT 0; 
	DECLARE pass VARCHAR(50); 

end_proc:begin
	IF (u IS NULL OR p IS NULL OR idd IS NULL ) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IDUSER, PASSWORD INTO num, idu, pass
	FROM USERS
	WHERE USERNAME = u;
			 
	CASE num 
	WHEN 1 THEN 
		IF (pass = p) THEN
			SELECT COUNT(*) INTO num
			FROM DEVICES
			WHERE IDUSER = idu AND IDDEVICE = idd;
			
			CASE num 
			WHEN 1 THEN 
				DELETE FROM DEVICES WHERE IDDEVICE = idd;
				
				SET err = 62;
			ELSE
				SET err = 39;
			END CASE;
		ELSE
			SET err = 2;
		END IF;
	WHEN 0 THEN
		SET err = 3;
	ELSE
		SET err = 4;
	END CASE;
end;
	
	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,    idu,   NULL,  IF(err = 62, 0, err),  30, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 62, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;
end$$

DROP PROCEDURE IF EXISTS `deletehouse`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `deletehouse`( IN u VARCHAR(50),IN p VARCHAR(50), IN h VARCHAR(50))
    COMMENT 'Delete a house if exist and the user is an administrator.'
begin

	DECLARE num, acc INTEGER DEFAULT 0;
	DECLARE idu, idh INTEGER DEFAULT NULL;  
	DECLARE err INTEGER DEFAULT 0; 
	DECLARE pass VARCHAR(50); 

end_proc:begin
	IF (u IS NULL OR p IS NULL OR h IS NULL ) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IFNULL(ACCESSNUMBER, 0), IDUSER, IDHOUSE, PASSWORD INTO num, acc, idu, idh, pass
	FROM loginVIEW
	WHERE USERNAME = u AND HOUSENAME = h;
			 
	CASE num 
	WHEN 0 THEN
		SET err = 11;
	ELSE
		CASE acc
		WHEN 1 THEN 
			IF (pass = p) THEN
				DELETE FROM HOUSES WHERE IDHOUSE= idh;
				SET err = 19;
			ELSE
				SET err = 2;
			END IF;
		WHEN 0 THEN
			SET err = 11;
		ELSE
			SET err = 39;
		END CASE;
	END CASE;
end;
	
	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,    idu,   idh,  IF(err = 19, 0, err),  9, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 19, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;
end$$

DROP PROCEDURE IF EXISTS `deletepermissionservice`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `deletepermissionservice`( IN u VARCHAR(50), IN h VARCHAR(50), IN r VARCHAR(50), IN s VARCHAR(50), IN u2 VARCHAR(50))
    COMMENT 'An admistrator delete permission to somobody to a service.'
begin

	DECLARE num,acc, per INTEGER DEFAULT 0;
	DECLARE idu, ids INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;

end_proc:begin
	IF (u IS NULL OR h IS NULL OR u2 IS NULL OR r IS NULL ) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IFNULL(ACCESSNUMBER, 0), IDUSER, IDSERVICE, PERMISSIONNUMBER INTO num, acc, idu, ids, per
	FROM loginVIEW
	WHERE USERNAME = u AND HOUSENAME = h AND ROOMNAME = r AND SERVICENAME = s;
			
	CASE num 
	WHEN 0 THEN
		SET err = 11;
	ELSE
		SELECT COUNT(*), IDUSER INTO num, idu 
		FROM USERS
		WHERE USERNAME = u2;	
			
		CASE acc
		WHEN 1 THEN 
			DELETE FROM PERMISSIONS WHERE IDUSER=idu AND IDSERVICE = ids;
			SET err = 72;
		ELSE
			IF (per = 1) THEN 
				DELETE FROM PERMISSIONS WHERE IDUSER=idu AND IDSERVICE = ids;
				SET err = 72;
			ELSE
				SET err = 39;
			END IF;
		END CASE;
	END CASE;
end;

	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,    idu,   NULL,  IF(err = 72, 0, err),  35, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 72, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;
end$$

DROP PROCEDURE IF EXISTS `deleteprogramaction`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteprogramaction`( IN u VARCHAR(50), IN pa VARCHAR(50))
    COMMENT 'Delete program action.'
begin
	DECLARE num, acc, per INTEGER DEFAULT 0;
	DECLARE ida, idu, idh INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;
	
end_proc:begin
	IF (u IS NULL OR pa IS NULL ) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IDUSER INTO num, idu
	FROM USERS
	WHERE USERNAME = u;
	
	CASE num 
	WHEN 1 THEN

		SELECT COUNT(*) INTO num
		FROM PROGRAMACTIONS
		WHERE PROGRAMNAME = pa AND IDUSER=idu;

		CASE num
		WHEN 1 THEN
			DELETE FROM PROGRAMACTIONS WHERE PROGRAMNAME= pa AND IDUSER=idu;
			SET err = 76;

		WHEN 0 THEN
			SET err = 77;

		ELSE

			SET err = 4;
		END CASE;
	ELSE 
		SET err = 3;
	END CASE;
end;	

	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,  idu,    idh,  IF(err = 76, 0, err),  15, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 76, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;

end$$

DROP PROCEDURE IF EXISTS `deleteroom`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteroom`( IN u VARCHAR(50),IN p VARCHAR(50), IN h VARCHAR(50), IN r VARCHAR(50))
    COMMENT 'Delete a room if exist.'
begin

	DECLARE num, acc INTEGER DEFAULT 0;
	DECLARE idu, idh, idr INTEGER DEFAULT NULL;  
	DECLARE err INTEGER DEFAULT 0; 
	DECLARE pass VARCHAR(50); 

end_proc:begin
	IF (u IS NULL OR h IS NULL OR r IS NULL ) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IFNULL(ACCESSNUMBER, 0), IDUSER, IDHOUSE, IDROOM, PASSWORD INTO num, acc, idu, idh, idr, pass
	FROM loginVIEW
	WHERE USERNAME = u AND HOUSENAME = h AND ROOMNAME = r;
			 
	CASE num 
	WHEN 0 THEN
		SET err = 46;
	ELSE
		CASE acc
		WHEN 1 THEN 
			IF (pass = p) THEN
				DELETE FROM ROOMS WHERE IDROOM = idr;
				SET err = 45;
			ELSE
				SET err = 2;
			END IF;
		WHEN 0 THEN
			SET err = 11;
		ELSE
			SET err = 39;
		END CASE;
	END CASE;
end;
	
	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,    idu,   idh,  IF(err = 45, 0, err),  18, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 45, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;
end$$

DROP PROCEDURE IF EXISTS `deletetask`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `deletetask`( IN u VARCHAR(50), IN ta VARCHAR(50))
    COMMENT 'Delete a user task.'
begin
	DECLARE num INTEGER DEFAULT 0;
	DECLARE idu, idta INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;
	
end_proc:begin
	IF (u IS NULL OR ta IS NULL) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IDUSER INTO num, idu
	FROM USERS
	WHERE USERNAME = u;
	
	CASE num 
	WHEN 1 THEN 
		SELECT COUNT(*), IDTASK INTO num, idta
		FROM TASKS
		WHERE IDUSER = idu AND TASKNAME = ta;
		
		CASE num 
		WHEN 1 THEN 
			DELETE FROM TASKPROGRAM WHERE IDTASK = idta;
			DELETE FROM TASKS WHERE IDTASK = idta;
			SET err = 30;
		WHEN 0 THEN 
			SET err = 31;
		ELSE
			SET err = 4;
		END CASE;
	WHEN 0 THEN
		SET err = 3;
	ELSE
		SET err = 4;
	END CASE;
end;

	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,  idu,    null,  IF(err = 30, 0, err),  12, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 30, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;

end$$

DROP PROCEDURE IF EXISTS `deleteuser`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteuser`( IN u VARCHAR(50), IN p VARCHAR(50))
    COMMENT 'Delete user if posible by deleting all information restring.'
begin

	DECLARE num INTEGER DEFAULT 0;
	DECLARE idu INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;
	DECLARE pass VARCHAR(50);

end_proc:begin
	IF (u IS NULL ) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;
	
	SELECT COUNT(*), IDUSER, PASSWORD INTO num , idu, pass
	FROM USERS
	WHERE USERNAME = u;
			
	CASE num 
	WHEN 1 THEN 
			IF (pass = p) THEN 
				DELETE FROM `USERS` WHERE IDUSER = idu;
				SET err = 14;
			ELSE 
				SET err = 2;
			END IF;
	WHEN 0 THEN
		SET err = 3;
	ELSE
		SET err = 4;
	END CASE;
end;

	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,   idu,    NULL,  IF(err = 14, 0, err),  4, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 14, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;

end$$

DROP PROCEDURE IF EXISTS `linkserviceroom`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `linkserviceroom`( IN ids INTEGER, IN idr INTEGER)
    COMMENT 'Attach the service with the room.'
begin

	DECLARE num INTEGER DEFAULT 0;
	DECLARE ida, idu, idh, idc INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;

end_proc:begin
	IF (ids IS NULL OR idr IS NULL) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*) INTO num
	FROM SERVICES
	WHERE IDSERVICE = ids;
	
	CASE num 
	WHEN 1 THEN
		SELECT COUNT(*) INTO num
		FROM ROOMS
		WHERE IDROOM = idr;

		CASE num 
		WHEN 1 THEN
			UPDATE `SERVICES` SET `IDROOM`= idr WHERE IDSERVICE = ids;
			SET err = 65;
		ELSE
			SET err = 66;
		END CASE;
	WHEN 0 THEN
		SET err = 64;
	ELSE
		SET err = 4;
	END CASE;
	
end;
	
	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,  ids,    idr,  IF(err = 65, 0, err),  31, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 65, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;

end$$

DROP PROCEDURE IF EXISTS `loginJSON`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `loginJSON`( in u VARCHAR(50))
begin

SELECT *
	FROM  loginVIEW
	WHERE USERNAME = u ;

end$$

DROP PROCEDURE IF EXISTS `modifyhouse`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `modifyhouse`( IN u VARCHAR(50), IN h VARCHAR(50), IN n_h VARCHAR(50), IN im VARCHAR(50), IN c VARCHAR(50), IN ctry VARCHAR(50))
    COMMENT 'Modify house information.'
begin

	DECLARE num, acc INTEGER DEFAULT 0;
	DECLARE idu, idh, idim INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;

end_proc:begin
	IF (u IS NULL OR h IS NULL OR n_h IS NULL) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IFNULL(ACCESSNUMBER, 0), IDUSER, IDHOUSE INTO num, acc, idu, idh
	FROM loginVIEW
	WHERE USERNAME = u AND HOUSENAME = h;
			
	CASE num 
	WHEN 0 THEN
		SET err = 11;
	ELSE
		CASE acc
		WHEN 1 THEN 
			SELECT COUNT(*) INTO num
			FROM HOUSES
			WHERE HOUSENAME = n_h AND IDHOUSE <> idh;
					
			CASE num 
			WHEN 0 THEN 
SELECT COUNT(*), IDIMAGE  INTO num, idim
FROM IMAGES
WHERE URL = im;
CASE num 
			WHEN 0 THEN 
				SET err = 73;
ELSE
				UPDATE HOUSES SET HOUSENAME=n_h, IDIMAGE=idim, CITY=c, COUNTRY=ctry WHERE IDHOUSE = idh;
				SET err = 42;
			END CASE;
			WHEN 1 THEN
				SET err = 22;
			ELSE
				SET err = 4;
			END CASE;
		WHEN 0 THEN
			SET err = 11;
		ELSE
			SET err = 39;
		END CASE;
	END CASE;
end;
	
	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,    idu,   idh,  IF(err = 42, 0, err),  20, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 42, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;
end$$

DROP PROCEDURE IF EXISTS `modifyservicetype`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `modifyservicetype`( IN u VARCHAR(50), IN idd INTEGER, IN s VARCHAR(50), IN ty VARCHAR(50))
    COMMENT 'An admistrator modify the type of service instaled on the device.'
begin

	DECLARE num INTEGER DEFAULT 0;
	DECLARE idu, ids INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;

end_proc:begin
	IF (u IS NULL OR idd IS NULL OR s IS NULL) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IDSERVICE INTO num, ids
	FROM deviceVIEW
	WHERE USERNAME = u AND SERVICENAME = s AND IDDEVICE = idd;
			
	CASE num 
	WHEN 1 THEN 
		UPDATE SERVICES SET `TYPE` = ty WHERE IDSERVICE = ids;
		SET err = 70;
	WHEN 0 THEN
		SET err = 69;
	ELSE
		SET err = 4;
	END CASE;
end;
	
	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,  ids,    NULL,  IF(err = 70, 0, err),  33, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 70, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;

end$$

DROP PROCEDURE IF EXISTS `modifyuser`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `modifyuser`( IN u VARCHAR(50), IN p VARCHAR(50), IN n_u VARCHAR(50), IN n_p VARCHAR(50), IN n_mail VARCHAR(50), n_h VARCHAR(50), IN im VARCHAR(50))
    COMMENT 'Mdify the especcification of an existing user.'
begin

	DECLARE num INTEGER DEFAULT 0;
	DECLARE idu, idim INTEGER DEFAULT NULL;
	DECLARE pass VARCHAR(50);
	DECLARE err INTEGER DEFAULT 0;

end_proc:begin
	IF (u IS NULL OR n_u IS NULL OR n_mail IS NULL) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IDUSER, PASSWORD INTO num, idu, pass
	FROM USERS
	WHERE USERNAME = u;
			
	CASE num 
	WHEN 1 THEN 
		IF (pass = p) THEN 
				SELECT COUNT(*) INTO num
				FROM USERS
				WHERE USERNAME = n_u AND IDUSER <> idu;
						
				CASE num 
				WHEN 0 THEN 
						SELECT COUNT(*) INTO num
						FROM USERS
						WHERE EMAIL = n_mail AND IDUSER <> idu;
						CASE num
						WHEN 0 THEN 
							SELECT COUNT(*), IDIMAGE INTO num, idim
							FROM IMAGES
							WHERE URL = im;
							CASE num
							WHEN 0 THEN 
								SET err = 73;
							ELSE
								UPDATE USERS SET USERNAME=n_u, PASSWORD=n_p, EMAIL=n_mail, HINT=n_h, IDIMAGE=idim
									WHERE IDUSER = idu;
								SET err = 15;
							END CASE;
						WHEN 1 THEN
							SET err = 7; 
						ELSE
							SET err = 4;
						END CASE;
				WHEN 1 THEN
					SET err = 6;
				ELSE
					SET err = 4;
				END CASE;
		ELSE 
			SET err = 2;
		END IF;
	WHEN 0 THEN
		SET err = 3;
	ELSE
		SET err = 4;
	END CASE;
end;
	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,   idu,    NULL,  IF(err = 15, 0, err),  5, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 15, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;

end$$

DROP PROCEDURE IF EXISTS `recoveryuser`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `recoveryuser`( IN u VARCHAR(50), IN p VARCHAR(50))
    COMMENT 'Generate a password to recovery user.'
begin
	DECLARE idu INTEGER DEFAULT NULL;
        DECLARE e VARCHAR(50) DEFAULT NULL;

	UPDATE USERS SET PASSWORD = p
	WHERE USERNAME = u;
        
	SELECT  IDUSER, EMAIL INTO  idu, e
	FROM USERS
	WHERE USERNAME = u;

	INSERT INTO HISTORYACCESS
				(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
		VALUES  (     NULL,   idu,    NULL,  0,  2, CURRENT_TIMESTAMP);

        SELECT e AS EMAIL;
end$$

DROP PROCEDURE IF EXISTS `removecommandprogram`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `removecommandprogram`( IN u VARCHAR(50), IN c VARCHAR(50), IN idpa INTEGER, IN n INTEGER)
    COMMENT 'Remove an acction of a command.'
begin
	DECLARE num INTEGER DEFAULT 0;
	DECLARE ida, idu, idh, idc INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;
	
end_proc:begin
	IF (u IS NULL OR c IS NULL OR idpa IS NULL OR n IS NULL ) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IDUSER INTO  num, idu
	FROM PROGRAMACTIONS 
	WHERE IDPROGRAM = idpa AND IDUSER IN ( SELECT IDUSER 
										FROM USERS 
										WHERE USERNAME = u);
	
	CASE num 
	WHEN 1 THEN
	
		SELECT COUNT(*), IDCOMMAND INTO num, idc
		FROM COMMANDS
		WHERE COMMANDNAME = c AND IDUSER = idu;
		
		CASE num
		WHEN 1 THEN
			SELECT COUNT(*) INTO num
			FROM COMMAND_PROGRAM
			WHERE IDCOMMAND = idc AND POS = n;
			
			CASE num
			WHEN 1 THEN
				DELETE FROM COMMAND_PROGRAM WHERE IDCOMMAND = idc AND IDPROGRAM = idpa AND POS = n;
				SET err = 59;
			ELSE
				SET err = 58;
			END CASE;
		ELSE
			SET err = 60;
		END CASE;
	WHEN 0 THEN
		SET err = 33;
	ELSE
		SET err = 4;
	END CASE;
end;
	
	SELECT IDHOUSE INTO idh
	FROM houseRoomServiceActionVIEW
	WHERE IDACTION = ida;
	
	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,  idu,    idh,  IF(err = 59, 0, err),  29, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 59, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;

end$$

DROP PROCEDURE IF EXISTS `removetaskprogram`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `removetaskprogram`( IN u VARCHAR(50), IN idta INTEGER, IN idpa INTEGER)
    COMMENT 'Add an acction to a task.'
begin
	DECLARE num INTEGER DEFAULT 0;
	DECLARE ida, idu, idh INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;
	
end_proc:begin
	IF (u IS NULL OR idta IS NULL OR idpa IS NULL ) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*), IDUSER INTO  num, idu
	FROM PROGRAMACTIONS 
	WHERE IDPROGRAM = idpa AND IDUSER IN ( SELECT IDUSER 
										FROM USERS 
										WHERE USERNAME = u);
	
	CASE num 
	WHEN 1 THEN
	
		SELECT COUNT(*) INTO num
		FROM TASKS
		WHERE IDTASK = idta AND IDUSER = idu;
		
		CASE num
		WHEN 1 THEN
			SELECT COUNT(*) INTO num
			FROM TASKPROGRAM
			WHERE IDTASK = idta AND IDPROGRAM = idpa;
			
			CASE num
			WHEN 1 THEN
				DELETE FROM TASKPROGRAM WHERE IDTASK = idta;
				SET err = 35;
			ELSE
				SET err = 36;
			END CASE;
		ELSE
			SET err = 32;
		END CASE;
	WHEN 0 THEN
		SET err = 33;
	ELSE
		SET err = 4;
	END CASE;
	
	SELECT IDHOUSE INTO idh
	FROM houseRoomServiceActionVIEW
	WHERE IDACTION = ida;
end;
	
	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,  idu,    idh,  IF(err = 35, 0, err),  22, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 35, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;

end$$

DROP PROCEDURE IF EXISTS `schedule`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `schedule`( IN h VARCHAR(15))
    COMMENT 'Request for all task afected to a house, by a user.'
begin

		SELECT *
	FROM ACTIONS, PROGRAMACTIONS
	LEFT JOIN (TASKPROGRAM, TASKS) ON (  TASKPROGRAM.IDPROGRAM = PROGRAMACTIONS.IDPROGRAM
										AND TASKPROGRAM.IDTASK = TASKS.IDTASK )
	JOIN SERVICES
	JOIN ROOMS 	ON ( ROOMS.IDROOM = SERVICES.IDROOM )
	JOIN HOUSES ON ( HOUSES.IDHOUSE = ROOMS.IDHOUSE )
	WHERE 	ACTIONS.IDACTION = PROGRAMACTIONS.IDACTION
		AND SERVICES.IDSERVICE = ACTIONS.IDSERVICE
		AND HOUSES.HOUSENAME = h
	ORDER BY TASKNAME DESC, PROGRAMACTIONS.IDPROGRAM ASC;

end$$

DROP PROCEDURE IF EXISTS `selectiduser`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `selectiduser`( in u VARCHAR(20), out id integer)
BEGIN
DECLARE idu INTEGER;
SELECT IDUSER into idu FROM USERS WHERE USERNAME=u;
set id = idu;
END$$

DROP PROCEDURE IF EXISTS `streaminghour`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `streaminghour`(ini TIMESTAMP, p INT)
BEGIN
DROP  TABLE IF EXISTS STADISTICS  ;
CREATE TABLE STADISTICS SELECT COUNT(*) AS Y, X
FROM (SELECT TRUNC_N_MINUTES(DATESTAMP, 60/p) AS X
		FROM HISTORYACCESS 
		WHERE DATESTAMP>= ini)AS T
GROUP BY X
ORDER BY X; 
END$$

DROP PROCEDURE IF EXISTS `unlinkserviceroom`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `unlinkserviceroom`( IN ids INTEGER)
    COMMENT 'Remove the room from a service.'
begin

	DECLARE num INTEGER DEFAULT 0;
	DECLARE ida, idu, idh, idc INTEGER DEFAULT NULL;
	DECLARE err INTEGER DEFAULT 0;

end_proc:begin
	IF (ids IS NULL) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*) INTO num
	FROM SERVICES
	WHERE IDSERVICE = ids;
	
	CASE num 
	WHEN 1 THEN
		UPDATE `SERVICES` SET `IDROOM`= NULL WHERE IDSERVICE = ids;
		SET err = 67;
	WHEN 0 THEN
		SET err = 64;
	ELSE
		SET err = 4;
	END CASE;
	
end;
	
	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,  ids,    NULL,  IF(err = 67, 0, err),  32, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 67, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;

end$$

DROP PROCEDURE IF EXISTS `updateservicestate`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `updateservicestate`( IN ids INTEGER, IN stat VARCHAR(50))
    COMMENT 'raspberry pi connect to update the state of the service.'
begin

	DECLARE num INTEGER DEFAULT 0;
	DECLARE err INTEGER DEFAULT 0;
	DECLARE s, st, boton VARCHAR(50);

end_proc:begin
	IF (ids IS NULL OR stat IS NULL) THEN 
		SET err = 61;
		LEAVE end_proc;
	END IF;

	SELECT COUNT(*),SERVICENAME, TYPE INTO num, s, st
	FROM SERVICES
	WHERE `IDSERVICE`= ids;
			
	CASE num 
	WHEN 0 THEN
		SET err = 64;
	ELSE
		CASE
		WHEN s = 'TV' THEN
			
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND POWER = stat;
			SET boton = 'POWER';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND SETUP = stat;
			SET boton = 'SETUP';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND MUTE = stat;
			SET boton = 'MUTE';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND FUNCTION = stat;
			SET boton = 'FUNCTION';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND ONE = stat;
			SET boton = 'ONE';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND TWO = stat;
			SET boton = 'TWO';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND THREE = stat;
			SET boton = 'THREE';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND FOUR = stat;
			SET boton = 'FOUR';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND FIVE = stat;
			SET boton = 'FIVE';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND SIX = stat;
			SET boton = 'SIX';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND SEVEN = stat;
			SET boton = 'SEVEN';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND EIGHT = stat;
			SET boton = 'EIGHT';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND NINE = stat;
			SET boton = 'NINE';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND ZERO = stat;
			SET boton = 'ZERO';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND FAV = stat;
			SET boton = 'FAV';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND UP = stat;
			SET boton = 'UP';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND DOWN = stat;
			SET boton = 'DOWN';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND `LEFT` = stat;
			SET boton = 'LEFT';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND `RIGHT` = stat;
			SET boton = 'RIGHT';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND OK = stat;
			SET boton = 'OK';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND PLAY = stat;
			SET boton = 'PLAY';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND PAUSE = stat;
			SET boton = 'PAUSE';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND STOP = stat;
			SET boton = 'STOP';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND FF = stat;
			SET boton = 'FF';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND FW = stat;
			SET boton = 'FW';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND VOLUMEUP = stat;
			SET boton = 'VOLUMEUP';
			IF num = 0 THEN 
			SELECT COUNT(*) INTO num FROM IRCODES WHERE TYPE = st AND VOLUMEDOWN = stat;
			SET boton = 'VOLUMEDOWN';
			IF num = 0 THEN 
			SET boton = stat;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			END IF;
			UPDATE `SERVICES` SET `STATUS`= boton WHERE `IDSERVICE`= ids;
			SET err = 78;
		ELSE
			UPDATE `SERVICES` SET `STATUS`= stat WHERE `IDSERVICE`= ids;
			SET err = 78;
		END CASE;
	END CASE;
end;

	INSERT INTO HISTORYACCESS
						(IDHISTORY, IDUSER, IDHOUSE, ERROR, FUNCT, DATESTAMP        )
				VALUES  (     NULL,    NULL,   NULL,  IF(err = 78, 0, err),  39, CURRENT_TIMESTAMP);
				
	SELECT IF(ERRORCODE = 78, 0, ERRORCODE) AS ERROR, ENGLISH, SPANISH
	FROM ERRORS
	WHERE ERRORCODE = err;
end$$

DROP PROCEDURE IF EXISTS `userexist`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `userexist`(u VARCHAR(15), p VARCHAR(40), error INTEGER)
BEGIN 
declare pass varchar(40);
SELECT PASSWORD INTO pass FROM USERS WHERE USERNAME=u;
IF (pass <> p) THEN 
  set error = 2;
ELSE 
SET error = 0;
END IF;



END$$

--
-- Funciones
--
DROP FUNCTION IF EXISTS `ROUND_HOUR`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `ROUND_HOUR`(datestamp DATETIME) RETURNS datetime
    NO SQL
    DETERMINISTIC
    COMMENT 'returns nearest hour'
RETURN DATE_FORMAT(datestamp + INTERVAL 30 MINUTE, '%Y-%m-%d %H:00')$$

DROP FUNCTION IF EXISTS `TRUNC_N_HOURS`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `TRUNC_N_HOURS`(datestamp DATETIME, n INT) RETURNS datetime
    NO SQL
    DETERMINISTIC
    COMMENT 'truncate to N hour boundary. For example,\n           TRUNCATE_N_HOURS(sometime, 12) gives the nearest\n           preceding half-day (noon, or midnight'
RETURN DATE(datestamp) +
                INTERVAL (HOUR(datestamp) -
                          HOUR(datestamp) MOD n) HOUR$$

DROP FUNCTION IF EXISTS `TRUNC_N_MINUTES`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `TRUNC_N_MINUTES`(datestamp DATETIME, n INT) RETURNS datetime
    NO SQL
    DETERMINISTIC
    COMMENT 'truncate to N minute boundary. For example,\n           TRUNCATE_N_MINUTES(sometime, 15) gives the nearest\n           preceding quarter hour'
RETURN DATE_FORMAT(datestamp,'%Y-%m-%d %H:00') +
                INTERVAL (MINUTE(datestamp) -
                          MINUTE(datestamp) MOD n) MINUTE$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `ACCESSHOUSE`
--
-- Creación: 14-05-2014 a las 19:16:13
--

DROP TABLE IF EXISTS `ACCESSHOUSE`;
CREATE TABLE IF NOT EXISTS `ACCESSHOUSE` (
  `IDUSER` int(11) NOT NULL DEFAULT '0',
  `IDHOUSE` int(11) NOT NULL DEFAULT '0',
  `ACCESSNUMBER` int(11) DEFAULT '1',
  `DATEBEGIN` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`IDUSER`,`IDHOUSE`),
  KEY `IDHOUSE` (`IDHOUSE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- RELACIONES PARA LA TABLA `ACCESSHOUSE`:
--   `IDUSER`
--       `USERS` -> `IDUSER`
--   `IDHOUSE`
--       `HOUSES` -> `IDHOUSE`
--

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `ACTIONMESSAGES`
--
-- Creación: 14-05-2014 a las 19:16:13
--

DROP TABLE IF EXISTS `ACTIONMESSAGES`;
CREATE TABLE IF NOT EXISTS `ACTIONMESSAGES` (
  `IDMESSAGE` int(11) NOT NULL AUTO_INCREMENT,
  `IDACTION` int(11) NOT NULL,
  `RETURNCODE` varchar(50) NOT NULL,
  `EXIT` tinyint(1) NOT NULL,
  `ENGLISH` varchar(50) DEFAULT NULL,
  `SPANISH` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`IDMESSAGE`),
  UNIQUE KEY `IDACTION_RETURNCODE` (`IDACTION`,`RETURNCODE`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=22 ;

--
-- RELACIONES PARA LA TABLA `ACTIONMESSAGES`:
--   `IDACTION`
--       `ACTIONS` -> `IDACTION`
--

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `ACTIONS`
--
-- Creación: 14-05-2014 a las 19:16:13
--

DROP TABLE IF EXISTS `ACTIONS`;
CREATE TABLE IF NOT EXISTS `ACTIONS` (
  `IDACTION` int(11) NOT NULL AUTO_INCREMENT,
  `IDSERVICE` int(11) DEFAULT NULL,
  `ACTIONNAME` varchar(50) NOT NULL,
  `ENGLISH` varchar(50) DEFAULT NULL,
  `SPANISH` varchar(50) DEFAULT NULL,
  `FCODE` varchar(20) NOT NULL,
  PRIMARY KEY (`IDACTION`),
  UNIQUE KEY `UNQ_ACTIONKEY` (`IDSERVICE`,`ACTIONNAME`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=275 ;

--
-- RELACIONES PARA LA TABLA `ACTIONS`:
--   `IDSERVICE`
--       `SERVICES` -> `IDSERVICE`
--

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `COMMANDS`
--
-- Creación: 14-05-2014 a las 19:16:13
--

DROP TABLE IF EXISTS `COMMANDS`;
CREATE TABLE IF NOT EXISTS `COMMANDS` (
  `IDCOMMAND` int(11) NOT NULL AUTO_INCREMENT,
  `COMMANDNAME` varchar(50) NOT NULL,
  `IDUSER` int(11) NOT NULL,
  PRIMARY KEY (`IDCOMMAND`),
  KEY `IDUSER` (`IDUSER`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;

--
-- RELACIONES PARA LA TABLA `COMMANDS`:
--   `IDUSER`
--       `USERS` -> `IDUSER`
--

-- --------------------------------------------------------

--
-- Estructura Stand-in para la vista `commandVIEW`
--
DROP VIEW IF EXISTS `commandVIEW`;
CREATE TABLE IF NOT EXISTS `commandVIEW` (
`IDUSER` int(11)
,`USERNAME` varchar(50)
,`IDCOMMAND` int(11)
,`COMMANDNAME` varchar(50)
,`POS` int(11)
,`IDPROGRAM` int(11)
,`IDHOUSE` int(11)
,`HOUSENAME` varchar(50)
,`IDROOM` int(11)
,`ROOMNAME` varchar(50)
,`IDSERVICE` int(11)
,`SERVICENAME` varchar(50)
,`IDACTION` int(11)
,`ACTIONNAME` varchar(50)
,`DATA` varchar(50)
);
-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `COMMAND_PROGRAM`
--
-- Creación: 14-05-2014 a las 19:16:13
--

DROP TABLE IF EXISTS `COMMAND_PROGRAM`;
CREATE TABLE IF NOT EXISTS `COMMAND_PROGRAM` (
  `IDCOMMAND` int(11) NOT NULL,
  `IDPROGRAM` int(11) NOT NULL,
  `POS` int(11) NOT NULL,
  UNIQUE KEY `IDCOMMAND_2` (`IDCOMMAND`,`IDPROGRAM`,`POS`),
  KEY `IDCOMMAND` (`IDCOMMAND`),
  KEY `IDPROGRAM` (`IDPROGRAM`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- RELACIONES PARA LA TABLA `COMMAND_PROGRAM`:
--   `IDPROGRAM`
--       `PROGRAMACTIONS` -> `IDPROGRAM`
--   `IDCOMMAND`
--       `COMMANDS` -> `IDCOMMAND`
--

--
-- Disparadores `COMMAND_PROGRAM`
--
DROP TRIGGER IF EXISTS `checkPOS_natural`;
DELIMITER //
CREATE TRIGGER `checkPOS_natural` BEFORE INSERT ON `COMMAND_PROGRAM`
 FOR EACH ROW BEGIN
  IF NEW.POS < 1 THEN
    SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'POS must be natural';
  END IF;
END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura Stand-in para la vista `countHitsVIEW`
--
DROP VIEW IF EXISTS `countHitsVIEW`;
CREATE TABLE IF NOT EXISTS `countHitsVIEW` (
`USERNAME` varchar(50)
,`FUNCTION` varchar(50)
,`TOTAL` bigint(21)
,`SUCCESS` decimal(23,0)
,`ERROR` decimal(23,0)
,`PASSWORD` decimal(23,0)
,`INTEGRITY` decimal(23,0)
);
-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `CRONACTIONS`
--
-- Creación: 14-05-2014 a las 19:16:10
--

DROP TABLE IF EXISTS `CRONACTIONS`;
CREATE TABLE IF NOT EXISTS `CRONACTIONS` (
  `city` varchar(100) NOT NULL DEFAULT 'madrid',
  `country` varchar(100) DEFAULT 'spain',
  `language` varchar(100) DEFAULT 'en',
  `Salida` varchar(500) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `id` (`city`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=156 ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `DEVICES`
--
-- Creación: 14-05-2014 a las 19:16:13
--

DROP TABLE IF EXISTS `DEVICES`;
CREATE TABLE IF NOT EXISTS `DEVICES` (
  `IDDEVICE` int(11) NOT NULL AUTO_INCREMENT,
  `IDUSER` int(11) DEFAULT NULL,
  `IPADDRESS` varchar(500) DEFAULT NULL,
  `SERIAL` varchar(50) DEFAULT NULL,
  `DEVICENAME` varchar(50) DEFAULT NULL,
  `ENGLISH` varchar(500) DEFAULT NULL,
  `SPANISH` varchar(500) DEFAULT NULL,
  `DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `VERSION` int(11) NOT NULL,
  PRIMARY KEY (`IDDEVICE`),
  KEY `IDUSER` (`IDUSER`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=51 ;

--
-- RELACIONES PARA LA TABLA `DEVICES`:
--   `IDUSER`
--       `USERS` -> `IDUSER`
--

-- --------------------------------------------------------

--
-- Estructura Stand-in para la vista `deviceVIEW`
--
DROP VIEW IF EXISTS `deviceVIEW`;
CREATE TABLE IF NOT EXISTS `deviceVIEW` (
`IDUSER` int(11)
,`USERNAME` varchar(50)
,`IDDEVICE` int(11)
,`DEVICENAME` varchar(50)
,`IPADDRESS` varchar(500)
,`SERIAL` varchar(50)
,`ENGLISH` varchar(500)
,`SPANISH` varchar(500)
,`IDSERVICE` int(11)
,`SERVICENAME` varchar(50)
,`TYPE` varchar(50)
,`IDROOM` int(11)
,`ROOMNAME` varchar(50)
,`IDHOUSE` int(11)
,`HOUSENAME` varchar(50)
);
-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `ERRORS`
--
-- Creación: 14-05-2014 a las 19:16:11
--

DROP TABLE IF EXISTS `ERRORS`;
CREATE TABLE IF NOT EXISTS `ERRORS` (
  `ERRORCODE` int(11) NOT NULL AUTO_INCREMENT,
  `ENGLISH` varchar(100) NOT NULL,
  `SPANISH` varchar(100) NOT NULL,
  PRIMARY KEY (`ERRORCODE`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=80 ;

-- --------------------------------------------------------

--
-- Estructura Stand-in para la vista `eventProgramVIEW`
--
DROP VIEW IF EXISTS `eventProgramVIEW`;
CREATE TABLE IF NOT EXISTS `eventProgramVIEW` (
`IDUSER` int(11)
,`USERNAME` varchar(50)
,`IDPROGRAM` int(11)
,`PROGRAMNAME` varchar(50)
,`DATA` varchar(50)
,`STARTTIME` datetime
,`IDHOUSE` int(11)
,`HOUSENAME` varchar(50)
,`IDROOM` int(11)
,`ROOMNAME` varchar(50)
,`IDSERVICE` int(11)
,`SERVICENAME` varchar(50)
,`IDACTION` int(11)
,`ACTIONNAME` varchar(50)
);
-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `FUNCTIONS`
--
-- Creación: 14-05-2014 a las 19:16:11
--

DROP TABLE IF EXISTS `FUNCTIONS`;
CREATE TABLE IF NOT EXISTS `FUNCTIONS` (
  `FUNCT` int(11) NOT NULL AUTO_INCREMENT,
  `FUNCTION` varchar(50) NOT NULL,
  PRIMARY KEY (`FUNCT`),
  UNIQUE KEY `FUNCTION` (`FUNCTION`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=41 ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `HISTORYACCESS`
--
-- Creación: 14-05-2014 a las 19:16:14
--

DROP TABLE IF EXISTS `HISTORYACCESS`;
CREATE TABLE IF NOT EXISTS `HISTORYACCESS` (
  `IDHISTORY` int(11) NOT NULL AUTO_INCREMENT,
  `IDUSER` int(11) DEFAULT NULL,
  `IDHOUSE` int(11) DEFAULT NULL,
  `ERROR` int(11) DEFAULT NULL,
  `FUNCT` int(11) DEFAULT NULL,
  `DATESTAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`IDHISTORY`),
  KEY `ERROR` (`ERROR`),
  KEY `FUNCT` (`FUNCT`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11749 ;

--
-- RELACIONES PARA LA TABLA `HISTORYACCESS`:
--   `ERROR`
--       `ERRORS` -> `ERRORCODE`
--   `FUNCT`
--       `FUNCTIONS` -> `FUNCT`
--

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `HISTORYACTION`
--
-- Creación: 14-05-2014 a las 19:16:12
--

DROP TABLE IF EXISTS `HISTORYACTION`;
CREATE TABLE IF NOT EXISTS `HISTORYACTION` (
  `IDHISTORYACTION` int(11) NOT NULL AUTO_INCREMENT,
  `IDACTION` int(11) NOT NULL,
  `IDPROGRAM` int(11) DEFAULT NULL,
  `IDUSER` int(11) DEFAULT NULL,
  `RETURNCODE` varchar(50) NOT NULL,
  `DATESTAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`IDHISTORYACTION`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1614 ;

-- --------------------------------------------------------

--
-- Estructura Stand-in para la vista `houseRoomServiceActionVIEW`
--
DROP VIEW IF EXISTS `houseRoomServiceActionVIEW`;
CREATE TABLE IF NOT EXISTS `houseRoomServiceActionVIEW` (
`IDHOUSE` int(11)
,`HOUSENAME` varchar(50)
,`IDROOM` int(11)
,`ROOMNAME` varchar(50)
,`IDSERVICE` int(11)
,`SERVICENAME` varchar(50)
,`TYPE` varchar(50)
,`IDACTION` int(11)
,`ACTIONNAME` varchar(50)
);
-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `HOUSES`
--
-- Creación: 14-05-2014 a las 19:16:13
--

DROP TABLE IF EXISTS `HOUSES`;
CREATE TABLE IF NOT EXISTS `HOUSES` (
  `IDHOUSE` int(11) NOT NULL AUTO_INCREMENT,
  `IDUSER` int(11) NOT NULL,
  `HOUSENAME` varchar(50) NOT NULL,
  `IDIMAGE` int(11) DEFAULT NULL,
  `CITY` varchar(50) DEFAULT NULL,
  `COUNTRY` varchar(50) DEFAULT NULL,
  `GPS` varchar(50) DEFAULT NULL,
  `DATEBEGIN` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`IDHOUSE`),
  UNIQUE KEY `HOUSENAME` (`HOUSENAME`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=27 ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `IMAGES`
--
-- Creación: 14-05-2014 a las 19:16:13
--

DROP TABLE IF EXISTS `IMAGES`;
CREATE TABLE IF NOT EXISTS `IMAGES` (
  `IDIMAGE` int(11) NOT NULL AUTO_INCREMENT,
  `URL` varchar(500) DEFAULT NULL,
  `TYPE` varchar(50) NOT NULL,
  PRIMARY KEY (`IDIMAGE`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=471 ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `IRCODES`
--
-- Creación: 14-05-2014 a las 19:16:13
--

DROP TABLE IF EXISTS `IRCODES`;
CREATE TABLE IF NOT EXISTS `IRCODES` (
  `IDCODE` int(11) NOT NULL AUTO_INCREMENT,
  `TYPE` varchar(50) NOT NULL,
  `POWER` varchar(50) DEFAULT NULL,
  `SETUP` varchar(50) DEFAULT NULL,
  `MUTE` varchar(50) DEFAULT NULL,
  `FUNCTION` varchar(50) DEFAULT NULL,
  `ONE` varchar(50) DEFAULT NULL,
  `TWO` varchar(50) DEFAULT NULL,
  `THREE` varchar(50) DEFAULT NULL,
  `FOUR` varchar(50) DEFAULT NULL,
  `FIVE` varchar(50) DEFAULT NULL,
  `SIX` varchar(50) DEFAULT NULL,
  `SEVEN` varchar(50) DEFAULT NULL,
  `EIGHT` varchar(50) DEFAULT NULL,
  `NINE` varchar(50) DEFAULT NULL,
  `ZERO` varchar(50) DEFAULT NULL,
  `FAV` varchar(50) DEFAULT NULL,
  `UP` varchar(50) DEFAULT NULL,
  `DOWN` varchar(50) DEFAULT NULL,
  `LEFT` varchar(50) DEFAULT NULL,
  `RIGHT` varchar(50) DEFAULT NULL,
  `OK` varchar(50) DEFAULT NULL,
  `PLAY` varchar(50) DEFAULT NULL,
  `PAUSE` varchar(50) DEFAULT NULL,
  `STOP` varchar(50) DEFAULT NULL,
  `FF` varchar(50) DEFAULT NULL,
  `FW` varchar(50) DEFAULT NULL,
  `VOLUMEUP` varchar(50) DEFAULT NULL,
  `VOLUMEDOWN` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`IDCODE`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `LOGED`
--
-- Creación: 14-05-2014 a las 19:16:14
--

DROP TABLE IF EXISTS `LOGED`;
CREATE TABLE IF NOT EXISTS `LOGED` (
  `IDLOG` int(11) NOT NULL AUTO_INCREMENT,
  `IDUSER` int(11) NOT NULL,
  `REGID` varchar(1000) NOT NULL,
  `OS` varchar(50) NOT NULL,
  PRIMARY KEY (`IDLOG`),
  KEY `IDUSER` (`IDUSER`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=169 ;

--
-- RELACIONES PARA LA TABLA `LOGED`:
--   `IDUSER`
--       `USERS` -> `IDUSER`
--

-- --------------------------------------------------------

--
-- Estructura Stand-in para la vista `loginVIEW`
--
DROP VIEW IF EXISTS `loginVIEW`;
CREATE TABLE IF NOT EXISTS `loginVIEW` (
`IDUSER` int(11)
,`USERNAME` varchar(50)
,`PASSWORD` varchar(50)
,`IDHOUSE` int(11)
,`HOUSENAME` varchar(50)
,`URL` varchar(500)
,`IDROOM` int(11)
,`ROOMNAME` varchar(50)
,`ROOMTYPE` int(11)
,`IDSERVICE` int(11)
,`SERVICENAME` varchar(50)
,`TYPE` varchar(50)
,`STATUS` varchar(50)
,`SERVICEINTERFACE` int(11)
,`IDACTION` int(11)
,`ACTIONNAME` varchar(50)
,`FCODE` varchar(20)
,`ACCESSNUMBER` int(11)
,`PERMISSIONNUMBER` int(11)
,`IDDEVICE` int(11)
,`IPADDRESS` varchar(500)
,`CITY` varchar(50)
,`COUNTRY` varchar(50)
);
-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `PERMISSIONS`
--
-- Creación: 14-05-2014 a las 19:16:14
--

DROP TABLE IF EXISTS `PERMISSIONS`;
CREATE TABLE IF NOT EXISTS `PERMISSIONS` (
  `IDUSER` int(11) NOT NULL DEFAULT '0',
  `IDSERVICE` int(11) NOT NULL,
  `PERMISSIONNUMBER` int(11) NOT NULL,
  `DATEBEGIN` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`IDUSER`,`IDSERVICE`),
  KEY `IDSERVICE` (`IDSERVICE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- RELACIONES PARA LA TABLA `PERMISSIONS`:
--   `IDUSER`
--       `USERS` -> `IDUSER`
--   `IDSERVICE`
--       `SERVICES` -> `IDSERVICE`
--

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `PROGRAMACTIONS`
--
-- Creación: 14-05-2014 a las 19:16:14
--

DROP TABLE IF EXISTS `PROGRAMACTIONS`;
CREATE TABLE IF NOT EXISTS `PROGRAMACTIONS` (
  `IDPROGRAM` int(11) NOT NULL AUTO_INCREMENT,
  `PROGRAMNAME` varchar(50) NOT NULL,
  `IDUSER` int(11) NOT NULL,
  `IDACTION` int(11) NOT NULL,
  `DATA` varchar(50) DEFAULT NULL,
  `STARTTIME` datetime DEFAULT NULL,
  `DATEBEGIN` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`IDPROGRAM`),
  KEY `IDACTION` (`IDACTION`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=148 ;

--
-- RELACIONES PARA LA TABLA `PROGRAMACTIONS`:
--   `IDACTION`
--       `ACTIONS` -> `IDACTION`
--

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `REGISTRATIONS`
--
-- Creación: 14-05-2014 a las 19:16:13
--

DROP TABLE IF EXISTS `REGISTRATIONS`;
CREATE TABLE IF NOT EXISTS `REGISTRATIONS` (
  `IDUSER` int(11) NOT NULL AUTO_INCREMENT,
  `USERNAME` varchar(50) NOT NULL,
  `PASSWORD` varchar(50) NOT NULL,
  `EMAIL` varchar(50) NOT NULL,
  `HINT` varchar(50) DEFAULT NULL,
  `DATEBEGIN` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `CODECONFIRM` varchar(50) NOT NULL,
  PRIMARY KEY (`IDUSER`),
  UNIQUE KEY `USERNAME` (`USERNAME`),
  UNIQUE KEY `EMAIL` (`EMAIL`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=57 ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `ROOMS`
--
-- Creación: 14-05-2014 a las 19:16:14
--

DROP TABLE IF EXISTS `ROOMS`;
CREATE TABLE IF NOT EXISTS `ROOMS` (
  `IDROOM` int(11) NOT NULL AUTO_INCREMENT,
  `IDHOUSE` int(11) DEFAULT NULL,
  `IDUSER` int(11) DEFAULT NULL,
  `ROOMNAME` varchar(50) NOT NULL,
  `TYPE` int(11) NOT NULL,
  `DATEBEGIN` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`IDROOM`),
  UNIQUE KEY `ROOMNAME` (`ROOMNAME`,`IDHOUSE`),
  KEY `IDHOUSE` (`IDHOUSE`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=32 ;

--
-- RELACIONES PARA LA TABLA `ROOMS`:
--   `IDHOUSE`
--       `HOUSES` -> `IDHOUSE`
--

-- --------------------------------------------------------

--
-- Estructura Stand-in para la vista `scheduleVIEW`
--
DROP VIEW IF EXISTS `scheduleVIEW`;
CREATE TABLE IF NOT EXISTS `scheduleVIEW` (
`IDTASK` int(11)
,`TASKNAME` varchar(50)
,`IDPROGRAM` int(11)
,`IDACTION` int(11)
,`IDHOUSE` int(11)
,`HOUSENAME` varchar(50)
,`IDROOM` int(11)
,`ROOMNAME` varchar(50)
,`IDSERVICE` int(11)
,`SERVICENAME` varchar(50)
,`DATA` varchar(50)
,`STARTTIME` datetime
);
-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `SERVICES`
--
-- Creación: 14-05-2014 a las 23:47:53
--

DROP TABLE IF EXISTS `SERVICES`;
CREATE TABLE IF NOT EXISTS `SERVICES` (
  `IDSERVICE` int(11) NOT NULL AUTO_INCREMENT,
  `IDROOM` int(11) DEFAULT NULL,
  `IDDEVICE` int(11) DEFAULT NULL,
  `SERVICENAME` varchar(50) NOT NULL,
  `TYPE` varchar(50) DEFAULT NULL,
  `SERVICEINTERFACE` int(11) NOT NULL,
  `STATUS` varchar(50) DEFAULT NULL,
  `ENGLISH` varchar(50) DEFAULT NULL,
  `SPANISH` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`IDSERVICE`),
  KEY `IDDEVICE` (`IDDEVICE`),
  KEY `UNQ_IDROOM_IDDEVICE_SERVICENAME` (`IDROOM`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=219 ;

--
-- RELACIONES PARA LA TABLA `SERVICES`:
--   `IDROOM`
--       `ROOMS` -> `IDROOM`
--   `IDDEVICE`
--       `DEVICES` -> `IDDEVICE`
--

-- --------------------------------------------------------

--
-- Estructura Stand-in para la vista `singleProgramVIEW`
--
DROP VIEW IF EXISTS `singleProgramVIEW`;
CREATE TABLE IF NOT EXISTS `singleProgramVIEW` (
`IDUSER` int(11)
,`USERNAME` varchar(50)
,`IDPROGRAM` int(11)
,`DATA` varchar(50)
,`STARTTIME` datetime
,`IDHOUSE` int(11)
,`HOUSENAME` varchar(50)
,`IDROOM` int(11)
,`ROOMNAME` varchar(50)
,`IDSERVICE` int(11)
,`SERVICENAME` varchar(50)
,`IDACTION` int(11)
,`ACTIONNAME` varchar(50)
);
-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `SOFTWARE`
--
-- Creación: 14-05-2014 a las 19:16:13
--

DROP TABLE IF EXISTS `SOFTWARE`;
CREATE TABLE IF NOT EXISTS `SOFTWARE` (
  `DEVICE` int(11) NOT NULL,
  `VERSION` int(11) NOT NULL,
  `URL` varchar(500) NOT NULL,
  UNIQUE KEY `DEVICE` (`DEVICE`,`VERSION`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `STADISTICS`
--
-- Creación: 14-05-2014 a las 19:16:13
--

DROP TABLE IF EXISTS `STADISTICS`;
CREATE TABLE IF NOT EXISTS `STADISTICS` (
  `Y` bigint(21) NOT NULL DEFAULT '0',
  `X` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `TASKPROGRAM`
--
-- Creación: 14-05-2014 a las 19:16:14
--

DROP TABLE IF EXISTS `TASKPROGRAM`;
CREATE TABLE IF NOT EXISTS `TASKPROGRAM` (
  `IDTASK` int(11) NOT NULL,
  `IDPROGRAM` int(11) NOT NULL,
  UNIQUE KEY `1_ACTION_1_TASK` (`IDTASK`,`IDPROGRAM`),
  KEY `IDPROGRAM` (`IDPROGRAM`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- RELACIONES PARA LA TABLA `TASKPROGRAM`:
--   `IDTASK`
--       `TASKS` -> `IDTASK`
--   `IDPROGRAM`
--       `PROGRAMACTIONS` -> `IDPROGRAM`
--

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `TASKS`
--
-- Creación: 14-05-2014 a las 19:16:14
--

DROP TABLE IF EXISTS `TASKS`;
CREATE TABLE IF NOT EXISTS `TASKS` (
  `IDTASK` int(11) NOT NULL AUTO_INCREMENT,
  `TASKNAME` varchar(50) NOT NULL,
  `IDUSER` int(11) DEFAULT NULL,
  `DESCRIPTION` varchar(50) DEFAULT NULL,
  ` FREQUENCY` timestamp NULL DEFAULT NULL,
  `DATEBEGIN` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`IDTASK`),
  KEY `IDUSER` (`IDUSER`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=25 ;

--
-- RELACIONES PARA LA TABLA `TASKS`:
--   `IDUSER`
--       `USERS` -> `IDUSER`
--

-- --------------------------------------------------------

--
-- Estructura Stand-in para la vista `taskVIEW`
--
DROP VIEW IF EXISTS `taskVIEW`;
CREATE TABLE IF NOT EXISTS `taskVIEW` (
`IDUSER` int(11)
,`USERNAME` varchar(50)
,`IDTASK` int(11)
,`TASKNAME` varchar(50)
,`DESCRIPTION` varchar(50)
,`STARTTIME` datetime
,`IDPROGRAM` int(11)
,`IDHOUSE` int(11)
,`HOUSENAME` varchar(50)
,`IDROOM` int(11)
,`ROOMNAME` varchar(50)
,`IDSERVICE` int(11)
,`SERVICENAME` varchar(50)
,`IDACTION` int(11)
,`ACTIONNAME` varchar(50)
,`DATA` varchar(50)
);
-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `TEMP`
--
-- Creación: 14-05-2014 a las 19:16:13
--

DROP TABLE IF EXISTS `TEMP`;
CREATE TABLE IF NOT EXISTS `TEMP` (
  `IDIMAGE` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `USERS`
--
-- Creación: 14-05-2014 a las 19:16:13
--

DROP TABLE IF EXISTS `USERS`;
CREATE TABLE IF NOT EXISTS `USERS` (
  `IDUSER` int(11) NOT NULL AUTO_INCREMENT,
  `USERNAME` varchar(50) NOT NULL,
  `PASSWORD` varchar(50) NOT NULL,
  `EMAIL` varchar(50) NOT NULL,
  `HINT` varchar(50) DEFAULT NULL,
  `IDIMAGE` int(11) DEFAULT NULL,
  `DATEBEGIN` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`IDUSER`),
  UNIQUE KEY `USERNAME` (`USERNAME`),
  UNIQUE KEY `EMAIL` (`EMAIL`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=143 ;

--
-- Disparadores `USERS`
--
DROP TRIGGER IF EXISTS `demoAccessHouse`;
DELIMITER //
CREATE TRIGGER `demoAccessHouse` AFTER INSERT ON `USERS`
 FOR EACH ROW BEGIN
    INSERT INTO `ACCESSHOUSE`(`IDUSER`, `IDHOUSE`, `ACCESSNUMBER`, `DATEBEGIN`) VALUES (NEW.IDUSER ,26 ,1 ,CURRENT_TIMESTAMP);
  END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura para la vista `commandVIEW`
--
DROP TABLE IF EXISTS `commandVIEW`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `commandVIEW` AS select `USERS`.`IDUSER` AS `IDUSER`,`USERS`.`USERNAME` AS `USERNAME`,`COMMANDS`.`IDCOMMAND` AS `IDCOMMAND`,`COMMANDS`.`COMMANDNAME` AS `COMMANDNAME`,`COMMAND_PROGRAM`.`POS` AS `POS`,`PROGRAMACTIONS`.`IDPROGRAM` AS `IDPROGRAM`,`HOUSES`.`IDHOUSE` AS `IDHOUSE`,`HOUSES`.`HOUSENAME` AS `HOUSENAME`,`ROOMS`.`IDROOM` AS `IDROOM`,`ROOMS`.`ROOMNAME` AS `ROOMNAME`,`SERVICES`.`IDSERVICE` AS `IDSERVICE`,`SERVICES`.`SERVICENAME` AS `SERVICENAME`,`ACTIONS`.`IDACTION` AS `IDACTION`,`ACTIONS`.`ACTIONNAME` AS `ACTIONNAME`,`PROGRAMACTIONS`.`DATA` AS `DATA` from (((((((`USERS` left join `COMMANDS` on((`USERS`.`IDUSER` = `COMMANDS`.`IDUSER`))) left join `COMMAND_PROGRAM` on((`COMMANDS`.`IDCOMMAND` = `COMMAND_PROGRAM`.`IDCOMMAND`))) left join `PROGRAMACTIONS` on((`COMMAND_PROGRAM`.`IDPROGRAM` = `PROGRAMACTIONS`.`IDPROGRAM`))) left join `ACTIONS` on((`PROGRAMACTIONS`.`IDACTION` = `ACTIONS`.`IDACTION`))) left join `SERVICES` on((`ACTIONS`.`IDSERVICE` = `SERVICES`.`IDSERVICE`))) left join `ROOMS` on((`SERVICES`.`IDROOM` = `ROOMS`.`IDROOM`))) left join `HOUSES` on((`ROOMS`.`IDHOUSE` = `HOUSES`.`IDHOUSE`))) order by `USERS`.`USERNAME`,`COMMANDS`.`COMMANDNAME`,`COMMAND_PROGRAM`.`POS`,`HOUSES`.`HOUSENAME`,`ROOMS`.`ROOMNAME`,`SERVICES`.`SERVICENAME`,`ACTIONS`.`ACTIONNAME`;

-- --------------------------------------------------------

--
-- Estructura para la vista `countHitsVIEW`
--
DROP TABLE IF EXISTS `countHitsVIEW`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `countHitsVIEW` AS select `USERS`.`USERNAME` AS `USERNAME`,`FUNCTIONS`.`FUNCTION` AS `FUNCTION`,count(0) AS `TOTAL`,sum(if((`HISTORYACCESS`.`ERROR` = 0),1,0)) AS `SUCCESS`,sum(if((`HISTORYACCESS`.`ERROR` <> 0),1,0)) AS `ERROR`,sum(if((`HISTORYACCESS`.`ERROR` = 2),1,0)) AS `PASSWORD`,sum(if((`HISTORYACCESS`.`ERROR` = 4),1,0)) AS `INTEGRITY` from (((`HISTORYACCESS` left join `USERS` on((`USERS`.`IDUSER` = `HISTORYACCESS`.`IDUSER`))) left join `FUNCTIONS` on((`HISTORYACCESS`.`FUNCT` = `FUNCTIONS`.`FUNCT`))) join `ERRORS` on((`ERRORS`.`ERRORCODE` = `HISTORYACCESS`.`ERROR`))) group by `USERS`.`USERNAME`,`FUNCTIONS`.`FUNCTION` order by count(0) desc;

-- --------------------------------------------------------

--
-- Estructura para la vista `deviceVIEW`
--
DROP TABLE IF EXISTS `deviceVIEW`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `deviceVIEW` AS select `USERS`.`IDUSER` AS `IDUSER`,`USERS`.`USERNAME` AS `USERNAME`,`DEVICES`.`IDDEVICE` AS `IDDEVICE`,`DEVICES`.`DEVICENAME` AS `DEVICENAME`,`DEVICES`.`IPADDRESS` AS `IPADDRESS`,`DEVICES`.`SERIAL` AS `SERIAL`,`DEVICES`.`ENGLISH` AS `ENGLISH`,`DEVICES`.`SPANISH` AS `SPANISH`,`SERVICES`.`IDSERVICE` AS `IDSERVICE`,`SERVICES`.`SERVICENAME` AS `SERVICENAME`,`SERVICES`.`TYPE` AS `TYPE`,`SERVICES`.`IDROOM` AS `IDROOM`,`ROOMS`.`ROOMNAME` AS `ROOMNAME`,`HOUSES`.`IDHOUSE` AS `IDHOUSE`,`HOUSES`.`HOUSENAME` AS `HOUSENAME` from ((((`DEVICES` join `USERS` on((`DEVICES`.`IDUSER` = `USERS`.`IDUSER`))) left join `SERVICES` on((`DEVICES`.`IDDEVICE` = `SERVICES`.`IDDEVICE`))) left join `ROOMS` on((`SERVICES`.`IDROOM` = `ROOMS`.`IDROOM`))) left join `HOUSES` on((`ROOMS`.`IDHOUSE` = `HOUSES`.`IDHOUSE`))) order by `USERS`.`IDUSER`,`DEVICES`.`IDDEVICE`;

-- --------------------------------------------------------

--
-- Estructura para la vista `eventProgramVIEW`
--
DROP TABLE IF EXISTS `eventProgramVIEW`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `eventProgramVIEW` AS select `USERS`.`IDUSER` AS `IDUSER`,`USERS`.`USERNAME` AS `USERNAME`,`PROGRAMACTIONS`.`IDPROGRAM` AS `IDPROGRAM`,`PROGRAMACTIONS`.`PROGRAMNAME` AS `PROGRAMNAME`,`PROGRAMACTIONS`.`DATA` AS `DATA`,`PROGRAMACTIONS`.`STARTTIME` AS `STARTTIME`,`HOUSES`.`IDHOUSE` AS `IDHOUSE`,`HOUSES`.`HOUSENAME` AS `HOUSENAME`,`ROOMS`.`IDROOM` AS `IDROOM`,`ROOMS`.`ROOMNAME` AS `ROOMNAME`,`SERVICES`.`IDSERVICE` AS `IDSERVICE`,`SERVICES`.`SERVICENAME` AS `SERVICENAME`,`ACTIONS`.`IDACTION` AS `IDACTION`,`ACTIONS`.`ACTIONNAME` AS `ACTIONNAME` from (((((`USERS` join `PROGRAMACTIONS` on((`USERS`.`IDUSER` = `PROGRAMACTIONS`.`IDUSER`))) left join `ACTIONS` on((`PROGRAMACTIONS`.`IDACTION` = `ACTIONS`.`IDACTION`))) left join `SERVICES` on((`ACTIONS`.`IDSERVICE` = `SERVICES`.`IDSERVICE`))) left join `ROOMS` on((`SERVICES`.`IDROOM` = `ROOMS`.`IDROOM`))) left join `HOUSES` on((`ROOMS`.`IDHOUSE` = `HOUSES`.`IDHOUSE`))) order by `HOUSES`.`HOUSENAME`,`PROGRAMACTIONS`.`IDPROGRAM`;

-- --------------------------------------------------------

--
-- Estructura para la vista `houseRoomServiceActionVIEW`
--
DROP TABLE IF EXISTS `houseRoomServiceActionVIEW`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `houseRoomServiceActionVIEW` AS select `HOUSES`.`IDHOUSE` AS `IDHOUSE`,`HOUSES`.`HOUSENAME` AS `HOUSENAME`,`ROOMS`.`IDROOM` AS `IDROOM`,`ROOMS`.`ROOMNAME` AS `ROOMNAME`,`SERVICES`.`IDSERVICE` AS `IDSERVICE`,`SERVICES`.`SERVICENAME` AS `SERVICENAME`,`SERVICES`.`TYPE` AS `TYPE`,`ACTIONS`.`IDACTION` AS `IDACTION`,`ACTIONS`.`ACTIONNAME` AS `ACTIONNAME` from (((`HOUSES` join `ROOMS` on((`HOUSES`.`IDHOUSE` = `ROOMS`.`IDHOUSE`))) join `SERVICES` on((`ROOMS`.`IDROOM` = `SERVICES`.`IDROOM`))) join `ACTIONS` on((`SERVICES`.`IDSERVICE` = `ACTIONS`.`IDSERVICE`))) order by `HOUSES`.`IDHOUSE`,`ROOMS`.`IDROOM`,`SERVICES`.`IDSERVICE`,`ACTIONS`.`IDACTION`;

-- --------------------------------------------------------

--
-- Estructura para la vista `loginVIEW`
--
DROP TABLE IF EXISTS `loginVIEW`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `loginVIEW` AS select `USERS`.`IDUSER` AS `IDUSER`,`USERS`.`USERNAME` AS `USERNAME`,`USERS`.`PASSWORD` AS `PASSWORD`,`HOUSES`.`IDHOUSE` AS `IDHOUSE`,`HOUSES`.`HOUSENAME` AS `HOUSENAME`,`IMAGES`.`URL` AS `URL`,`ROOMS`.`IDROOM` AS `IDROOM`,`ROOMS`.`ROOMNAME` AS `ROOMNAME`,`ROOMS`.`TYPE` AS `ROOMTYPE`,`SERVICES`.`IDSERVICE` AS `IDSERVICE`,`SERVICES`.`SERVICENAME` AS `SERVICENAME`,`SERVICES`.`TYPE` AS `TYPE`,`SERVICES`.`STATUS` AS `STATUS`,`SERVICES`.`SERVICEINTERFACE` AS `SERVICEINTERFACE`,`ACTIONS`.`IDACTION` AS `IDACTION`,`ACTIONS`.`ACTIONNAME` AS `ACTIONNAME`,`ACTIONS`.`FCODE` AS `FCODE`,`ACCESSHOUSE`.`ACCESSNUMBER` AS `ACCESSNUMBER`,`PERMISSIONS`.`PERMISSIONNUMBER` AS `PERMISSIONNUMBER`,`DEVICES`.`IDDEVICE` AS `IDDEVICE`,`DEVICES`.`IPADDRESS` AS `IPADDRESS`,`HOUSES`.`CITY` AS `CITY`,`HOUSES`.`COUNTRY` AS `COUNTRY` from ((((((((`USERS` left join `ACCESSHOUSE` on((`USERS`.`IDUSER` = `ACCESSHOUSE`.`IDUSER`))) left join `HOUSES` on((`ACCESSHOUSE`.`IDHOUSE` = `HOUSES`.`IDHOUSE`))) left join `IMAGES` on((`HOUSES`.`IDIMAGE` = `IMAGES`.`IDIMAGE`))) left join `ROOMS` on((`ACCESSHOUSE`.`IDHOUSE` = `ROOMS`.`IDHOUSE`))) left join `SERVICES` on((`ROOMS`.`IDROOM` = `SERVICES`.`IDROOM`))) left join `DEVICES` on((`SERVICES`.`IDDEVICE` = `DEVICES`.`IDDEVICE`))) left join `ACTIONS` on((`SERVICES`.`IDSERVICE` = `ACTIONS`.`IDSERVICE`))) left join `PERMISSIONS` on(((`PERMISSIONS`.`IDUSER` = `USERS`.`IDUSER`) and (`PERMISSIONS`.`IDSERVICE` = `SERVICES`.`IDSERVICE`)))) where 1 order by `USERS`.`USERNAME`,`HOUSES`.`HOUSENAME`,`ROOMS`.`ROOMNAME`,`SERVICES`.`SERVICENAME`,`ACTIONS`.`ACTIONNAME` desc;

-- --------------------------------------------------------

--
-- Estructura para la vista `scheduleVIEW`
--
DROP TABLE IF EXISTS `scheduleVIEW`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `scheduleVIEW` AS select `TASKS`.`IDTASK` AS `IDTASK`,`TASKS`.`TASKNAME` AS `TASKNAME`,`PROGRAMACTIONS`.`IDPROGRAM` AS `IDPROGRAM`,`PROGRAMACTIONS`.`IDACTION` AS `IDACTION`,`HOUSES`.`IDHOUSE` AS `IDHOUSE`,`HOUSES`.`HOUSENAME` AS `HOUSENAME`,`ROOMS`.`IDROOM` AS `IDROOM`,`ROOMS`.`ROOMNAME` AS `ROOMNAME`,`SERVICES`.`IDSERVICE` AS `IDSERVICE`,`SERVICES`.`SERVICENAME` AS `SERVICENAME`,`PROGRAMACTIONS`.`DATA` AS `DATA`,`PROGRAMACTIONS`.`STARTTIME` AS `STARTTIME` from (`ACTIONS` join ((((`PROGRAMACTIONS` left join (`TASKPROGRAM` join `TASKS`) on(((`TASKPROGRAM`.`IDPROGRAM` = `PROGRAMACTIONS`.`IDPROGRAM`) and (`TASKPROGRAM`.`IDTASK` = `TASKS`.`IDTASK`)))) join `SERVICES`) join `ROOMS` on((`ROOMS`.`IDROOM` = `SERVICES`.`IDROOM`))) join `HOUSES` on((`HOUSES`.`IDHOUSE` = `ROOMS`.`IDHOUSE`)))) where ((`ACTIONS`.`IDACTION` = `PROGRAMACTIONS`.`IDACTION`) and (`SERVICES`.`IDSERVICE` = `ACTIONS`.`IDSERVICE`)) order by `TASKS`.`TASKNAME` desc,`PROGRAMACTIONS`.`IDPROGRAM`;

-- --------------------------------------------------------

--
-- Estructura para la vista `singleProgramVIEW`
--
DROP TABLE IF EXISTS `singleProgramVIEW`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `singleProgramVIEW` AS select `USERS`.`IDUSER` AS `IDUSER`,`USERS`.`USERNAME` AS `USERNAME`,`PROGRAMACTIONS`.`IDPROGRAM` AS `IDPROGRAM`,`PROGRAMACTIONS`.`DATA` AS `DATA`,`PROGRAMACTIONS`.`STARTTIME` AS `STARTTIME`,`HOUSES`.`IDHOUSE` AS `IDHOUSE`,`HOUSES`.`HOUSENAME` AS `HOUSENAME`,`ROOMS`.`IDROOM` AS `IDROOM`,`ROOMS`.`ROOMNAME` AS `ROOMNAME`,`SERVICES`.`IDSERVICE` AS `IDSERVICE`,`SERVICES`.`SERVICENAME` AS `SERVICENAME`,`ACTIONS`.`IDACTION` AS `IDACTION`,`ACTIONS`.`ACTIONNAME` AS `ACTIONNAME` from (((((((`USERS` join `PROGRAMACTIONS` on((`USERS`.`IDUSER` = `PROGRAMACTIONS`.`IDUSER`))) left join `TASKPROGRAM` on((`PROGRAMACTIONS`.`IDPROGRAM` = `TASKPROGRAM`.`IDPROGRAM`))) left join `COMMAND_PROGRAM` on((`PROGRAMACTIONS`.`IDPROGRAM` = `COMMAND_PROGRAM`.`IDPROGRAM`))) left join `ACTIONS` on((`PROGRAMACTIONS`.`IDACTION` = `ACTIONS`.`IDACTION`))) left join `SERVICES` on((`ACTIONS`.`IDSERVICE` = `SERVICES`.`IDSERVICE`))) left join `ROOMS` on((`SERVICES`.`IDROOM` = `ROOMS`.`IDROOM`))) left join `HOUSES` on((`ROOMS`.`IDHOUSE` = `HOUSES`.`IDHOUSE`))) where (isnull(`TASKPROGRAM`.`IDPROGRAM`) and isnull(`COMMAND_PROGRAM`.`IDPROGRAM`)) order by `USERS`.`USERNAME`,`PROGRAMACTIONS`.`IDPROGRAM`;

-- --------------------------------------------------------

--
-- Estructura para la vista `taskVIEW`
--
DROP TABLE IF EXISTS `taskVIEW`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `taskVIEW` AS select `USERS`.`IDUSER` AS `IDUSER`,`USERS`.`USERNAME` AS `USERNAME`,`TASKS`.`IDTASK` AS `IDTASK`,`TASKS`.`TASKNAME` AS `TASKNAME`,`TASKS`.`DESCRIPTION` AS `DESCRIPTION`,`PROGRAMACTIONS`.`STARTTIME` AS `STARTTIME`,`PROGRAMACTIONS`.`IDPROGRAM` AS `IDPROGRAM`,`HOUSES`.`IDHOUSE` AS `IDHOUSE`,`HOUSES`.`HOUSENAME` AS `HOUSENAME`,`ROOMS`.`IDROOM` AS `IDROOM`,`ROOMS`.`ROOMNAME` AS `ROOMNAME`,`SERVICES`.`IDSERVICE` AS `IDSERVICE`,`SERVICES`.`SERVICENAME` AS `SERVICENAME`,`ACTIONS`.`IDACTION` AS `IDACTION`,`ACTIONS`.`ACTIONNAME` AS `ACTIONNAME`,`PROGRAMACTIONS`.`DATA` AS `DATA` from (((((((`USERS` left join `TASKS` on((`USERS`.`IDUSER` = `TASKS`.`IDUSER`))) left join `TASKPROGRAM` on((`TASKS`.`IDTASK` = `TASKPROGRAM`.`IDTASK`))) left join `PROGRAMACTIONS` on((`TASKPROGRAM`.`IDPROGRAM` = `PROGRAMACTIONS`.`IDPROGRAM`))) left join `ACTIONS` on((`PROGRAMACTIONS`.`IDACTION` = `ACTIONS`.`IDACTION`))) left join `SERVICES` on((`ACTIONS`.`IDSERVICE` = `SERVICES`.`IDSERVICE`))) left join `ROOMS` on((`SERVICES`.`IDROOM` = `ROOMS`.`IDROOM`))) left join `HOUSES` on((`ROOMS`.`IDHOUSE` = `HOUSES`.`IDHOUSE`))) order by `USERS`.`USERNAME`,`TASKS`.`TASKNAME`,`PROGRAMACTIONS`.`STARTTIME`,`HOUSES`.`HOUSENAME`,`ROOMS`.`ROOMNAME`,`SERVICES`.`SERVICENAME`,`ACTIONS`.`ACTIONNAME`;

--
-- Restricciones para tablas volcadas
--

--
-- Filtros para la tabla `ACCESSHOUSE`
--
ALTER TABLE `ACCESSHOUSE`
  ADD CONSTRAINT `ACCESSHOUSE_ibfk_3` FOREIGN KEY (`IDUSER`) REFERENCES `USERS` (`IDUSER`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ACCESSHOUSE_ibfk_4` FOREIGN KEY (`IDHOUSE`) REFERENCES `HOUSES` (`IDHOUSE`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Filtros para la tabla `ACTIONMESSAGES`
--
ALTER TABLE `ACTIONMESSAGES`
  ADD CONSTRAINT `ACTIONMESSAGES_ibfk_2` FOREIGN KEY (`IDACTION`) REFERENCES `ACTIONS` (`IDACTION`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Filtros para la tabla `ACTIONS`
--
ALTER TABLE `ACTIONS`
  ADD CONSTRAINT `ACTIONS_ibfk_2` FOREIGN KEY (`IDSERVICE`) REFERENCES `SERVICES` (`IDSERVICE`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Filtros para la tabla `COMMANDS`
--
ALTER TABLE `COMMANDS`
  ADD CONSTRAINT `COMMANDS_ibfk_1` FOREIGN KEY (`IDUSER`) REFERENCES `USERS` (`IDUSER`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Filtros para la tabla `COMMAND_PROGRAM`
--
ALTER TABLE `COMMAND_PROGRAM`
  ADD CONSTRAINT `COMMAND_PROGRAM_ibfk_2` FOREIGN KEY (`IDPROGRAM`) REFERENCES `PROGRAMACTIONS` (`IDPROGRAM`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `COMMAND_PROGRAM_ibfk_3` FOREIGN KEY (`IDCOMMAND`) REFERENCES `COMMANDS` (`IDCOMMAND`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Filtros para la tabla `DEVICES`
--
ALTER TABLE `DEVICES`
  ADD CONSTRAINT `DEVICES_ibfk_1` FOREIGN KEY (`IDUSER`) REFERENCES `USERS` (`IDUSER`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Filtros para la tabla `HISTORYACCESS`
--
ALTER TABLE `HISTORYACCESS`
  ADD CONSTRAINT `HISTORYACCESS_ibfk_3` FOREIGN KEY (`ERROR`) REFERENCES `ERRORS` (`ERRORCODE`),
  ADD CONSTRAINT `HISTORYACCESS_ibfk_4` FOREIGN KEY (`FUNCT`) REFERENCES `FUNCTIONS` (`FUNCT`);

--
-- Filtros para la tabla `LOGED`
--
ALTER TABLE `LOGED`
  ADD CONSTRAINT `LOGED_ibfk_1` FOREIGN KEY (`IDUSER`) REFERENCES `USERS` (`IDUSER`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Filtros para la tabla `PERMISSIONS`
--
ALTER TABLE `PERMISSIONS`
  ADD CONSTRAINT `PERMISSIONS_ibfk_3` FOREIGN KEY (`IDUSER`) REFERENCES `USERS` (`IDUSER`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `PERMISSIONS_ibfk_4` FOREIGN KEY (`IDSERVICE`) REFERENCES `SERVICES` (`IDSERVICE`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Filtros para la tabla `PROGRAMACTIONS`
--
ALTER TABLE `PROGRAMACTIONS`
  ADD CONSTRAINT `PROGRAMACTIONS_ibfk_1` FOREIGN KEY (`IDACTION`) REFERENCES `ACTIONS` (`IDACTION`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Filtros para la tabla `ROOMS`
--
ALTER TABLE `ROOMS`
  ADD CONSTRAINT `ROOMS_ibfk_2` FOREIGN KEY (`IDHOUSE`) REFERENCES `HOUSES` (`IDHOUSE`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Filtros para la tabla `SERVICES`
--
ALTER TABLE `SERVICES`
  ADD CONSTRAINT `SERVICES_ibfk_3` FOREIGN KEY (`IDROOM`) REFERENCES `ROOMS` (`IDROOM`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `SERVICES_ibfk_4` FOREIGN KEY (`IDDEVICE`) REFERENCES `DEVICES` (`IDDEVICE`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Filtros para la tabla `TASKPROGRAM`
--
ALTER TABLE `TASKPROGRAM`
  ADD CONSTRAINT `TASKPROGRAM_ibfk_3` FOREIGN KEY (`IDTASK`) REFERENCES `TASKS` (`IDTASK`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `TASKPROGRAM_ibfk_4` FOREIGN KEY (`IDPROGRAM`) REFERENCES `PROGRAMACTIONS` (`IDPROGRAM`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Filtros para la tabla `TASKS`
--
ALTER TABLE `TASKS`
  ADD CONSTRAINT `TASKS_ibfk_2` FOREIGN KEY (`IDUSER`) REFERENCES `USERS` (`IDUSER`) ON DELETE CASCADE ON UPDATE CASCADE;

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