private function getTecDocResults($query, $step = 1) {
$sql = "SELECT
ARL.ARL_SEARCH_NUMBER,
BRANDS.BRA_BRAND as BRAND,
ART.ART_ARTICLE_NR,
ARL.ARL_BRA_ID,
SUP.SUP_BRAND,
TEX.TEX_TEXT,
TEX.TEX_ID
FROM ART_LOOKUP ARL
LEFT JOIN BRANDS ON (BRANDS.BRA_ID = ARL.ARL_BRA_ID)
INNER JOIN ARTICLES ART ON (ART.ART_ID = ARL.ARL_ART_ID)
INNER JOIN SUPPLIERS SUP ON (SUP.SUP_ID = ART.ART_SUP_ID)
INNER JOIN DESIGNATIONS DES ON (DES.DES_ID = ART.ART_COMPLETE_DES_ID)
INNER JOIN DES_TEXTS TEX ON (DES.DES_TEX_ID = TEX.TEX_ID)
WHERE DES.DES_LNG_ID = 16 AND ARL.ARL_SEARCH_NUMBER = '$query'
GROUP BY BRAND";
if($step == 2) {
$sql = "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,
ARTICLES.ART_ARTICLE_NR,
ART_LOOKUP.ARL_BRA_ID,
SUPPLIERS.SUP_BRAND,
DES_TEXTS.TEX_TEXT,
DES_TEXTS.TEX_ID
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
INNER JOIN DESIGNATIONS ON (DESIGNATIONS.DES_ID = ARTICLES.ART_COMPLETE_DES_ID)
INNER JOIN DES_TEXTS ON (DESIGNATIONS.DES_TEX_ID = DES_TEXTS.TEX_ID)
WHERE
DESIGNATIONS.DES_LNG_ID = 16 AND
ART_LOOKUP.ARL_SEARCH_NUMBER = '$query' AND
(ART_LOOKUP.ARL_KIND IN (3, 4) AND BRANDS.BRA_BRAND = '".Tools::getValue('td_brand')."' OR
SUPPLIERS.SUP_BRAND = '".Tools::getValue('td_brand')."') 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
;";
}
//print_r(json_encode($this->pdo->query($sql)->fetchAll()));
return $this->pdo->query($sql)->fetchAll();
return $this->pdo->query("
SELECT
ART.ART_ARTICLE_NR,
SUP.SUP_BRAND,
TEX.TEX_TEXT,
TEX.TEX_ID
FROM ART_LOOKUP ARL
INNER JOIN ARTICLES ART ON (ART.ART_ID = ARL.ARL_ART_ID)
INNER JOIN SUPPLIERS SUP ON (SUP.SUP_ID = ART.ART_SUP_ID)
INNER JOIN DESIGNATIONS DES ON (DES.DES_ID = ART.ART_COMPLETE_DES_ID)
INNER JOIN DES_TEXTS TEX ON (DES.DES_TEX_ID = TEX.TEX_ID)
WHERE DES.DES_LNG_ID = 16 AND ARL.ARL_SEARCH_NUMBER = '$query' AND ARL.ARL_KIND IN ('3') ".($step == 1 ? 'GROUP BY TEX.TEX_ID' : '')." ORDER BY ARL_BRA_ID
")->fetchAll();
}
