DROP FUNCTION IF EXISTS bng347_get_product_url;
DELIMITER //

CREATE FUNCTION bng347_get_product_url(pid INT(1))
RETURNS TEXT
BEGIN
	DECLARE out TEXT;
	DECLARE cat_id INT(1);
	DECLARE is_there_anybody TINYINT(1);
	DECLARE cat_slug VARCHAR(255);

	SET cat_id = (SELECT virtuemart_category_id FROM ytgb1_virtuemart_product_categories WHERE virtuemart_product_id = pid and ordering = 0 LIMIT 1)	
	IF cat_id > 0 
		SET is_there_anybody = 1;
	END IF;
	SET out = CONCAT((SELECT slug FROM ytgb1_virtuemart_products_ru_ru WHERE virtuemart_product_id = pid), "-detail");
	WHILE cat_id > 0 DO
		SELECT cru.virtuemart_category_id, cru.slug INTO cat_id, cat_slug
		FROM ytgb1_virtuemart_category_categories cc 
		JOIN ytgb1_virtuemart_categories_ru_ru cru ON cru.virtuemart_category_id = cc.category_parent_id
		WHERE category_child_id = cat_id;
		SET out = CONCAT("/", cat_slug, "/", out);
	END WHILE;
	RETURN out;
END//
DELIMITER ;