/* Procedure structure for procedure `oms_unlock` */

drop procedure if exists `oms_unlock`;
drop function  if exists `oms_chgpass`;
drop function  if exists `oms_getsystempass`;
drop function  if exists `oms_login`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `oms_unlock`(xusername VARCHAR(32))
BEGIN
	UPDATE OMS_USER
	SET login_fails=0
	WHERE OMS_USER.username = xusername;
END$$

/* Function  structure for function  `oms_chgpass` */


CREATE DEFINER=`root`@`localhost` FUNCTION `oms_chgpass`(xusername VARCHAR(32), xpasswd VARCHAR(32), xnewpasswd VARCHAR(32)) RETURNS int(11)
BEGIN
	DECLARE thestate INT;	
	SELECT oms_login(xusername,xpasswd) INTO thestate;
	IF thestate = 1 THEN 
		BEGIN
		UPDATE OMS_USER
		SET OMS_USER.passwd = xnewpasswd
		WHERE OMS_USER.username=xusername AND OMS_USER.passwd = xpasswd;		
		SELECT state INTO thestate		
		FROM OMS_USER
		WHERE OMS_USER.username=xusername AND OMS_USER.passwd = xnewpasswd;
		RETURN thestate;
		END;
	ELSE 
		BEGIN
		RETURN thestate;
		END;
	END IF;
END$$

/* Function  structure for function  `oms_getsystempass` */

CREATE DEFINER=`sa`@`%` FUNCTION `oms_getsystempass`(xusername VARCHAR(32), xpasswd VARCHAR(32)) RETURNS varchar(32)
BEGIN
	DECLARE thestate,login_failures INT;	
	DECLARE systemPass VARCHAR(32);
	SELECT oms_login(xusername,xpasswd) INTO thestate;
	IF thestate > 0 THEN
		SELECT c.value INTO systemPass
			FROM oms_config c
			WHERE c.config_item='SystemPass';
		RETURN SystemPass ;
	END IF;
END$$

/* Function  structure for function  `oms_login` */

CREATE DEFINER=`root`@`localhost` FUNCTION `oms_login`(xusername VARCHAR(32), xpasswd VARCHAR(32)) RETURNS int(11)
BEGIN
	DECLARE thestate,login_failures INT;	
	SELECT state,login_fails INTO thestate,login_failures
	FROM oms_user
	WHERE oms_user.username=xusername AND oms_user.passwd = xpasswd;
	IF thestate > 0 AND login_failures < 5 THEN
		BEGIN
		UPDATE oms_user
		SET oms_user.last_login=NOW(), login_fails=0
		WHERE oms_user.username=xusername;
		RETURN thestate;
		END;
	ELSE
		BEGIN
		UPDATE oms_user
		SET oms_user.login_fails = login_fails+1
		WHERE oms_user.username=xusername;
		RETURN -1;
		END;
	END IF;
END$$

DELIMITER ;