SELECT
product.goods_code as 'id',
'В наличии' as 'Статус товара',
'Есть' as 'Доставка',
' ' as 'Стоимость доставки',
' ' as 'Срок доставки',
'Есть' as 'Самовывоз',
'Можно' as 'Купить в магазине без заказа',
CASE
WHEN price.price = (
select price
FROM shop_goods_base
where goods_id = product.id
AND base_id NOT IN (select id from `base` where hide_contacts = 1)
ORDER BY price
DESC LIMIT 1
)
THEN CONCAT('https://site.ru/', category.path,'/', product.path)
ELSE
CONCAT('https://', (
select domain from base where id =
(
select base_id
FROM shop_goods_base
where goods_id = product.id
AND base_id NOT IN (select id from `base` where hide_contacts = 1)
ORDER BY price
DESC LIMIT 1
)
),
'.site.ru/',
category.path, '/', product.path
)
END AS 'Ссылка на товар на сайте магазина',
brand.name as 'Производитель',
product.name as 'Название',
category.name as 'Категория',
(
select price
FROM shop_goods_base
where goods_id = product.id
AND base_id NOT IN (select id from `base` where hide_contacts = 1)
ORDER BY price
DESC LIMIT 1
) as 'Цена',
'' as 'Цена без скидки',
'RUR' as 'Валюта',
CONCAT('https://site.ru/u/shop/', SUBSTRING(product.goods_code, 1, 3), '/orig/', image.file) as 'Ссылка на картинку',
product.description as 'Описание',
CONCAT_WS(
"",
(
select GROUP_CONCAT('Цвет|', sc.header, ';')
from shop_filter_value as sfv
LEFT JOIN shop_color as sc
ON sfv.int_value = sc.id
where sfv.filter_set_id = 7 and sfv.goods_id = product.id
limit 1
),
(
select GROUP_CONCAT('Длина|', sfv.float_value, ' ', shc.units, ';')
from shop_filter_value as sfv
LEFT JOIN shop_filter_set as shc
ON sfv.filter_set_id = shc.id
where sfv.filter_set_id = 9 and sfv.goods_id = product.id
limit 1
),
(
select GROUP_CONCAT('Ширина|', sfv.float_value, ' ', shc.units, ';')
from shop_filter_value as sfv
LEFT JOIN shop_filter_set as shc
ON sfv.filter_set_id = shc.id
where sfv.filter_set_id = 35 and sfv.goods_id = product.id
limit 1
),
(
select GROUP_CONCAT('Высота|', sfv.float_value, ' ', shc.units, ';')
from shop_filter_value as sfv
LEFT JOIN shop_filter_set as shc
ON sfv.filter_set_id = shc.id
where sfv.filter_set_id = 32 and sfv.goods_id = product.id
limit 1
),
(
select GROUP_CONCAT('Пустотность|', sfs.value, ';')
from shop_filter_value as sfv
LEFT JOIN shop_filter_select as sfs
ON sfv.int_value = sfs.id
where sfv.filter_set_id = 196 and sfv.goods_id = product.id
limit 1
),
(
select GROUP_CONCAT('Тип|', sfs.value, ';')
from shop_filter_value as sfv
LEFT JOIN shop_filter_select as sfs
ON sfv.int_value = sfs.id
where sfv.filter_set_id = 43 and sfv.goods_id = product.id
limit 1
),
(
select GROUP_CONCAT('Материал|', sfs.value, ';')
from shop_filter_value as sfv
LEFT JOIN shop_filter_select as sfs
ON sfv.int_value = sfs.id
where sfv.filter_set_id = 18 and sfv.goods_id = product.id
limit 1
),
(
select GROUP_CONCAT('Диаметр|', sfv.float_value, ' ', shc.units, ';')
from shop_filter_value as sfv
LEFT JOIN shop_filter_set as shc
ON sfv.filter_set_id = shc.id
where sfv.filter_set_id = 8 and sfv.goods_id = product.id
limit 1
)
) as 'Характеристики товара',
'Необходима предоплата.' as 'Условия продажи',
'Есть' as 'Гарантия производителя',
country.xart_header as 'Страна происхождения',
product.goods_code as 'Штрихкод',
'80' as 'bid'
FROM shop_goods as product
LEFT JOIN shop_goods_base as price
ON product.id = price.goods_id
LEFT JOIN shop_catalog_goods as cat_table
ON product.id = cat_table.goods_id
LEFT JOIN shop_catalog as category
ON cat_table.catalog_id = category.id
LEFT JOIN shop_brand as brand
ON product.brand_id = brand.id
LEFT JOIN shop_country as country
ON product.country_id = country.id
LEFT JOIN shop_image as image
ON product.id = image.goods_id
WHERE product.goods_code IN (1,2,3,4,5)
GROUP BY product.goods_code