text: CREATE DEFINER=`admin`@`192.168.1.2` FUNCTION `getUserMaterialPriority`(user_ID INT, material_ID INT, user_quantity INT) RETURNS int
  BEGIN
  /*Determines the best priority for a material. Material is based upon the if the user has enough quantity to satisfy all servants using at that priority. First checks to see if it's Not Needed. If it is needed, it loops through to find the best priority.
  	Changelog
  		- 12/23/2020: Created.
          - 01/04/2021: Completed.*/
          
  	DECLARE priority_ID, final_priorityID, total, quantity, defaultID, completedID, notNeededID, priorityTotal INT;
      DECLARE done BOOLEAN DEFAULT 0;
  	DECLARE priorityCursor CURSOR FOR
  		SELECT p.id, SUM(IF(p.name = usumv.priority, usumv.quantity, 0)) As 'Total'
  		FROM user_servant_upgrade_material_vw usumv
  		JOIN priority p
  		WHERE usumv.userid = user_ID AND usumv.materialID = material_ID AND p.isMaterialCalculated = TRUE
  		GROUP BY usumv.userid, usumv.materialID, p.id ORDER BY p.sortOrder DESC;
  	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
      
      SET quantity = user_quantity;
      SET defaultID = (SELECT id FROM priority WHERE name = 'Default');
      SET completedID = (SELECT id FROM priority WHERE name = 'Completed');
      SET notNeededID = (SELECT id FROM priority WHERE name = 'Not Needed');
      SET final_priorityID = defaultID; -- Default priority ID to Default status
      SET priorityTotal = (SELECT SUM(IF(p.name = usumv.priority, usumv.quantity, 0)) FROM user_servant_upgrade_material_vw usumv
  							JOIN priority p
  							WHERE usumv.userid = user_ID AND usumv.materialID = material_ID AND p.id != notNeededID
  							GROUP BY usumv.userid, usumv.materialID); -- Find sum of all totals that is 'Non-Not Needed'
                              
  	IF (priorityTotal = 0 OR priorityTotal IS NULL) THEN -- All 'Non-Not Needed' priorities are 0 or material doesn't exist. Set to Not Needed.
  		SET final_priorityID = notNeededID;
  	ELSE  -- Found totals in needed priorities. Loop through to see what that priority should be.
  		OPEN priorityCursor;
  		read_loop: LOOP -- Loop through all the rows
  			FETCH priorityCursor INTO priority_ID, total;
  			IF done THEN
  				IF (total IS NOT NULL) THEN -- Quantity is greater than all totals and totals actually existed
  					SET final_priorityID = completedID;
  				END IF;
  				CLOSE priorityCursor; LEAVE read_loop; 
  			END IF;
  			
  			IF (total != 0 AND quantity >= total) THEN -- Quantity is greater than equal to current priority total. Take off from current quantity and continue.
  				SET quantity = quantity - total;
  			ELSEIF (total != 0 AND quantity < total) THEN -- Quantity is less than current priority total. Set to final priority and leave.
  				SET final_priorityID = priority_ID;
  				CLOSE priorityCursor; LEAVE read_loop;
  			END IF;
  		END LOOP;
  	END IF;
      
  RETURN final_priorityID;
  END