p_setSkill procedure for Life Is Feudal admins

p_setSkill.sql

 -- Author: Sean McNamara <smcnam@gmail.com>
 -- Last Updated: 02/21/2015
 -- For: Life Is Feudal: Your Own on MySQL/MariaDB (tested with MariaDB 10.1.x)
 -- License: Apache License 2.0 http://www.apache.org/licenses/LICENSE-2.0.txt
 -- Parameter documentation:
 -- cname: The first name of the character whose skill you want to change.
 -- sname: The case-sensitive skill name of the skill you want to change.
 -- amt: The whole number (decimals are not supported) of the skill to set.
 -- stus: 1 if you want to unlock the skill, or 0 if you want to lock it.
DELIMITER //
DROP PROCEDURE IF EXISTS p_setSkill;
CREATE PROCEDURE p_setSkill (cname VARCHAR(9), sname VARCHAR(45), amt INT, stus TINYINT)
startx:BEGIN
	DECLARE totalskill INT DEFAULT 0;
	DECLARE maxskill INT DEFAULT 0;
	DECLARE parentskillid INT DEFAULT NULL;
	DECLARE parentskillamt INT DEFAULT NULL;
	DECLARE targetskillid INT DEFAULT NULL;
	DECLARE targetskillamt INT DEFAULT 0;
	DECLARE charid INT DEFAULT NULL;
	DECLARE nextskillid INT DEFAULT NULL;
	DECLARE nextskillamt INT DEFAULT 0;
	
	(SELECT ID 
		FROM `character` chr 
		WHERE chr.Name = cname COLLATE utf8_unicode_ci 
		INTO charid);
		
	(SELECT ID 
		FROM `skill_type` typ1 
		WHERE typ1.Name = sname COLLATE utf8_unicode_ci
		INTO targetskillid);
		
	(SELECT CAST((SkillAmount / 10000000) AS INT)
		FROM `skills` s1
		WHERE s1.CharacterID = charid
		AND s1.SkillTypeID = targetskillid
		INTO targetskillamt);
		
	(SELECT `Parent`
		FROM `skill_type` typ2
		WHERE typ2.ID = targetskillid
		INTO parentskillid);
		
	(SELECT CAST((SkillAmount / 10000000) AS INT)
		FROM `skills` s2
		WHERE s2.CharacterID = charid
		AND s2.SkillTypeID = parentskillid
		INTO parentskillamt);
		
	(SELECT ID
		FROM `skill_type` typ3
		WHERE typ3.Parent = targetskillid
		INTO nextskillid);
		
	(SELECT (SkillAmount)
		FROM `skills` s3
		WHERE s3.CharacterID = charid
		AND s3.SkillTypeID = nextskillid
		INTO nextskillamt);
	
	(SELECT (SUM(SkillAmount) / 10000000)
		FROM `skills` s4
		WHERE s4.CharacterID = charid
		AND s4.SkillTypeID IN
			(SELECT ID FROM `skill_type` typ4 WHERE `Group` = (SELECT `Group`
			FROM `skill_type` typ5
			WHERE typ5.Name = sname COLLATE utf8_unicode_ci
			)) 
	INTO totalskill);
	
	(SELECT (1400 + (Intellect / 1000000) - 10) 
		FROM `character` chr2
		WHERE chr2.ID = charid
	INTO maxskill);
	
	-- Make sure the character name exists
	IF charid IS NULL THEN
		SELECT "Invalid character name." AS Result;
		LEAVE startx;
	END IF;
	
	-- Make sure that the skill name exists
	IF targetskillid IS NULL THEN
		SELECT "Invalid skill name." AS Result;
		LEAVE startx;
	END IF;
	
	-- Make sure that the previous skill in the skill tree is at the right amount
	IF NOT ((amt >= 60 AND parentskillamt >= 60)
		OR (amt >= 30 AND amt < 60 AND parentskillamt >= 30)
		OR (amt < 30)
		OR parentskillid IS NULL)
	THEN
		SELECT "Prerequisite not met." AS Result;
		LEAVE startx;
	END IF;
	
	-- Make sure the user has enough skillpoints remaining to have this set
	IF totalskill + amt - targetskillamt > maxskill THEN
		SELECT "User does not have enough skillpoints remaining for this." AS Result;
		LEAVE startx;
	END IF;

	-- Set the requested skill to the requested value
	IF (SELECT COUNT(*)
		 FROM `skills` s 
		 WHERE s.CharacterID = charid AND s.SkillTypeID = targetskillid)
		 < 1
	THEN
	 INSERT INTO `skills`
	 VALUES (
	  ((SELECT MAX(ID) FROM `skills` sk) + 1), charid, targetskillid, amt * 10000000, stus);
	ELSE
	 UPDATE `skills`
	 SET SkillAmount=amt * 10000000, LockStatus=stus
	 WHERE CharacterID=charid AND SkillTypeID=targetskillid;
	END IF;
	
	-- Unlock or lock the next skill if it exists
	IF nextskillid IS NOT NULL AND amt >= 30 THEN
		IF nextskillamt > 0 THEN
			 UPDATE `skills`
			 SET SkillAmount=nextskillamt, LockStatus=stus
			 WHERE CharacterID=charid AND SkillTypeID=nextskillid;
		ELSE
			IF (SELECT COUNT(*)
				 FROM `skills` s 
				 WHERE s.CharacterID = charid AND s.SkillTypeID = nextskillid)
				 < 1
			THEN
			 INSERT INTO `skills`
			 VALUES (
			  ((SELECT MAX(ID) FROM `skills` sk) + 1), charid, nextskillid, 0, stus);
			ELSE
			 UPDATE `skills`
			 SET SkillAmount=0, LockStatus=stus
			 WHERE CharacterID=charid AND SkillTypeID=nextskillid;
			END IF;
		END IF;
	END IF;
	
	SELECT "Success" AS Result;
END //
DELIMITER ;