-- Search analogues according to search numbers and brand (brand found in the previous chapter)
	
	SET @LNGID=4; -- by default English language
	SET @SEARCHNUMBER = '02354';
	SET @SEARCHBRAND = 'METALCAUCHO';  
	
	SELECT DISTINCT
		IF (ART_LOOKUP2.ARL_KIND = 3, BRANDS2.BRA_BRAND, SUPPLIERS2.SUP_BRAND) AS BRAND,
		IF (ART_LOOKUP2.ARL_KIND IN (2, 3), ART_LOOKUP2.ARL_DISPLAY_NR, ARTICLES2.ART_ARTICLE_NR) AS NUMBER,
		ART_LOOKUP2.ARL_KIND
	FROM
				   ART_LOOKUP
		 LEFT JOIN BRANDS ON BRANDS.BRA_ID = ART_LOOKUP.ARL_BRA_ID
		INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_LOOKUP.ARL_ART_ID
		INNER JOIN SUPPLIERS ON SUPPLIERS.SUP_ID = ARTICLES.ART_SUP_ID
		INNER JOIN ART_LOOKUP AS ART_LOOKUP2 FORCE KEY (PRIMARY) ON ART_LOOKUP2.ARL_ART_ID = ART_LOOKUP.ARL_ART_ID
		 LEFT JOIN BRANDS AS BRANDS2 ON BRANDS2.BRA_ID = ART_LOOKUP2.ARL_BRA_ID
		INNER JOIN ARTICLES AS ARTICLES2 ON ARTICLES2.ART_ID = ART_LOOKUP2.ARL_ART_ID
		INNER JOIN SUPPLIERS AS SUPPLIERS2 FORCE KEY (PRIMARY) ON SUPPLIERS2.SUP_ID = ARTICLES2.ART_SUP_ID
	WHERE
		ART_LOOKUP.ARL_SEARCH_NUMBER = CLEAN_NUMBER(@SEARCHNUMBER) AND
		(ART_LOOKUP.ARL_KIND IN (3, 4) AND BRANDS.BRA_BRAND = @SEARCHBRAND OR
		 ART_LOOKUP.ARL_KIND IN (1, 2) AND SUPPLIERS.SUP_BRAND = @SEARCHBRAND) AND
		(ART_LOOKUP.ARL_KIND, ART_LOOKUP2.ARL_KIND) IN
			((1, 1), (1, 2), (1, 3),
			 (2, 1), (2, 2), (2, 3),
			 (3, 1), (3, 2), (3, 3),
			 (4, 1))
	ORDER BY
		BRAND,
		NUMBER

-- Search analogues according to search numbers and brand (brand found in the previous chapter)
	
	SET @LNGID=4; -- by default English language
	SET @SEARCHNUMBER = '02354';
	SET @IDBRAND = 101;   -- by default for FEBI
	
	SELECT DISTINCT
		IF (ART_LOOKUP2.ARL_KIND = 3, BRANDS2.BRA_BRAND, SUPPLIERS2.SUP_BRAND) AS BRAND,
		IF (ART_LOOKUP2.ARL_KIND IN (2, 3), ART_LOOKUP2.ARL_DISPLAY_NR, ARTICLES2.ART_ARTICLE_NR) AS NUMBER,
		ART_LOOKUP2.ARL_KIND
	FROM
				   ART_LOOKUP
		 LEFT JOIN BRANDS ON BRANDS.BRA_ID = ART_LOOKUP.ARL_BRA_ID
		INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_LOOKUP.ARL_ART_ID
		INNER JOIN SUPPLIERS ON SUPPLIERS.SUP_ID = ARTICLES.ART_SUP_ID
		INNER JOIN ART_LOOKUP AS ART_LOOKUP2 FORCE KEY (PRIMARY) ON ART_LOOKUP2.ARL_ART_ID = ART_LOOKUP.ARL_ART_ID
		 LEFT JOIN BRANDS AS BRANDS2 ON BRANDS2.BRA_ID = ART_LOOKUP2.ARL_BRA_ID
		INNER JOIN ARTICLES AS ARTICLES2 ON ARTICLES2.ART_ID = ART_LOOKUP2.ARL_ART_ID
		INNER JOIN SUPPLIERS AS SUPPLIERS2 FORCE KEY (PRIMARY) ON SUPPLIERS2.SUP_ID = ARTICLES2.ART_SUP_ID
	WHERE
		ART_LOOKUP.ARL_SEARCH_NUMBER = CLEAN_NUMBER(@SEARCHNUMBER) AND
		(ART_LOOKUP.ARL_KIND IN (3, 4) AND BRANDS.BRA_ID = @IDBRAND OR
		 ART_LOOKUP.ARL_KIND IN (1, 2) AND SUPPLIERS.SUP_ID = @IDBRAND) AND
		(ART_LOOKUP.ARL_KIND, ART_LOOKUP2.ARL_KIND) IN
			((1, 1), (1, 2), (1, 3),
			 (2, 1), (2, 2), (2, 3),
			 (3, 1), (3, 2), (3, 3),
			 (4, 1))
	ORDER BY
		BRAND,
		NUMBER

DELIMITER $$
CREATE  FUNCTION `CLEAN_NUMBER`(STR VARCHAR(105)) RETURNS varchar(105) CHARSET utf8
    DETERMINISTIC
BEGIN
	DECLARE LEN INT DEFAULT LENGTH(STR);
	DECLARE I INT DEFAULT 1;
	DECLARE NEWSTR VARCHAR(105) DEFAULT '';
	DECLARE C CHAR;

	WHILE I<=LEN DO
		SET C = SUBSTR(STR, I, 1);
		IF C >= 'a' AND C <= 'z' OR
		   C >= 'A' AND C <= 'Z' OR
		   C >= '0' AND C <= '9' THEN
			SET NEWSTR = CONCAT(NEWSTR, C);
		END IF;
		SET I = I+1;
	END WHILE;

	RETURN NEWSTR;
END$$
DELIMITER ;