SELECT SQL_CALC_FOUND_ROWS
i.id_s_item AS id, i.seo_url, i.ext_url, i.art_no, i.art_type,
i.tag_soldout, i.tag_highlight, i.tag_sale, i.tag_special, i.tag_feature,
IF (i.date_available > NOW(), 0,1) AS tag_available,
DATE_FORMAT(i.date_available, '%d.%m.%Y') AS date_available,
i.title, i.image, i.image_more, i.price_retail,
(
SELECT ic.price_retail
FROM s_item_content ic
WHERE ic.id_s_item = i.id_s_item AND ic.active=1
AND ic.stock_quantity > 0 AND ic.price_retail > 0
ORDER BY ic.price_retail
LIMIT 1
) AS ic_price_retail,
r.id_s_rubric,
(
SELECT GROUP_CONCAT(CONCAT(r.id_s_rubric,';',r.name,';',r.hide) SEPARATOR '#')
FROM s_item_rubric ir
JOIN s_rubric r ON (r.id_s_rubric = ir.id_s_rubric)
WHERE ir.id_s_item = i.id_s_item
ORDER BY r.id_parent
) AS rubrics,
(
IF (i.art_type!=2, i.stock_quantity, (
SELECT SUM(ic.stock_quantity)
FROM s_item_content ic
WHERE ic.id_s_item = i.id_s_item AND ic.active=1 AND ic.stock_quantity > 0
))
) AS stocks,
(
IF (i.art_type=1, NULL, (
SELECT GROUP_CONCAT(ad.name ORDER BY ad.sort SEPARATOR '#')
FROM s_item_content ic
JOIN s_item_content_attr ia ON (ia.id_s_item_content = ic.id_s_item_content)
JOIN s_attr_data ad ON (ad.id_s_attr_data = ia.id_s_attr_data)
JOIN s_attr a ON (a.id_s_attr = ia.id_s_attr)
WHERE ic.id_s_item = i.id_s_item AND ic.active=1 AND ic.stock_quantity > 0
ORDER BY a.sort, ad.sort, ad.name
))
) AS attrib
,g.groupID AS id_cat, g.name AS name_cat, g.link AS link_cat
FROM s_item i
LEFT JOIN s_item_rubric ir ON (ir.id_s_item = i.id_s_item)
LEFT JOIN s_rubric r ON (r.id_s_rubric = ir.id_s_rubric)
LEFT JOIN s_item_groups_link l ON (l.id_s_item = i.id_s_item AND l.id_subric=1 )
LEFT JOIN groups g ON (g.groupID = l.id_group)
WHERE i.active=1 AND i.list>=1
AND r.active=1 AND r.list=1 AND r.id_parent IS NULL
AND (i.date_active < NOW() OR i.date_active IS NULL)
GROUP BY i.id_s_item
ORDER BY i.tag_highlight DESC, i.sort DESC