salix/db/routines/vn/functions/specie_IsForbidden.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` FUNCTION `vn`.`specie_IsForbidden`(vItemFk INT, vAddressFk INT)
	RETURNS tinyint(1)
	NOT DETERMINISTIC
	READS SQL DATA
BEGIN
	
	DECLARE vIsForbbiden BOOL;
	DECLARE vLeft INT;

	SELECT zg.lft INTO vLeft
		FROM vn.address a 
			JOIN vn.zoneGeo zg ON zg.name = a.postalCode 
		WHERE a.id = vAddressFk;
	
	SELECT sgi.isForbidden INTO vIsForbbiden
		FROM vn.specieGeoInvasive sgi 
			JOIN vn.zoneGeo zg ON zg.id = sgi.zoneGeofk 
			JOIN vn.itemBotanical ib ON ib.genusFk = sgi.genusFk AND sgi.specieFk IN ('sp.',ib.specieFk)
		WHERE vLeft BETWEEN zg.lft AND zg.rgt
			AND ib.itemFk = vItemFk
		ORDER BY zg.`depth` DESC 
		LIMIT 1;

	RETURN IFNULL(vIsForbbiden, FALSE);
	
END$$
DELIMITER ;