New
Акция
Hit
Sale
103,00
New
Акция
Hit
Sale
210,00
New
Акция
Hit
Sale
330,00
SELECT SQL_CALC_FOUND_ROWS
product_uid_id,
product_cnt_account,
product_set_field5,
product_set_field2,
product_set_field3,
product_hld_holder,
product_mbr_member,
product_chr_link,
product_enm_spec,
product_set_color,
product_set_size,
product_set_brand,
product_set_material,
product_set_print,
product_enm_new,
product_enm_vip,
product_set_filtercolor,
product_set_filtermaterial,
product_enm_top,
product_enm_exists,
product_chr_name,
product_img_image,
REPLACE(product_dec_price1, ".", ",") AS product_dec_price1,
product_dec_price1 AS product_dec_price_real,
product_chr_article,
product_set_dimensions,
(CASE
WHEN product_enm_vip = 'YES'
THEN 'hide'
END) AS Hide,
/*если отмечен чекбокс то остаток не отображаем, пишем уточняйте у манагера*/
/*
(CASE
WHEN product_enm_vip = 'YES'
THEN 'в наличии, уточняйте у менеджера'
ELSE IFNULL(product_int_balance1, 0)
END) AS product_int_balance1,
*/
product_int_balance2,
product_set_group1,
product_txt_text,
product_txt_text2,
product_chr_box,
product_lnk_factory,
product_enm_eco,
product_dat_checked,
(CASE
WHEN (product_chr_title IS NULL OR product_chr_title = '')
THEN CONCAT(product_chr_name,' купить оптом. Нанесение логотипа')
ELSE product_chr_title
END) AS product_chr_title,
product_enm_promo,
(CASE
WHEN (product_txt_descr IS NULL OR product_txt_descr = '') AND product_int_balance1 > 0
THEN CONCAT(product_chr_name,' - продажа оптом сувениров. В наличии ',product_int_balance1,' штук. Все виды нанесения логотипа компании. Доставка сувенирной продукции.')
WHEN (product_txt_descr IS NULL OR product_txt_descr = '') AND product_int_balance1 = 0
THEN CONCAT(product_chr_name,' - продажа оптом сувениров. Все виды нанесения логотипа компании. Доставка сувенирной продукции.')
ELSE product_txt_descr
END) AS product_txt_descr,
(CASE
WHEN (product_txt_keywords IS NULL OR product_txt_keywords = '')
THEN product_chr_name
ELSE product_txt_keywords
END) AS product_txt_keywords,
(CASE
WHEN (product_chr_article LIKE 'oa%' OR product_chr_article LIKE 'hg%' OR product_chr_article LIKE 'p111%')
THEN product_enm_new
ELSE
(CASE
WHEN product_smp_create >= NOW() - INTERVAL 1 MONTH
THEN 'YES'
ELSE 'NO'
END)
END) AS product_enm_new,
product_enm_action,
CONCAT(
'[',
GROUP_CONCAT(
CONCAT(
'{"image":"',
REPLACE(product_img_image, 'office/757', '/150x150xffffff'),
'","full_image":"',
REPLACE(product_img_image, 'office/757', '/230x200xffffff'),
'","article":"',
product_chr_article,
'","name":"',
REPLACE(product_chr_name, '"', "'"),
'","price":"',
IF(ROUND(IFNULL(product_dec_price1, 0)) = 0, NULL, REPLACE(product_dec_price1, ".", ",")),
'","balance":"',
#IF(IFNULL(product_int_balance1, 0) = 0, NULL, product_int_balance1),
IFNULL(product_int_balance1, 0),
'","link":"',
product_chr_link,
'","stocks":',
(
CASE
WHEN IFNULL(product_txt_text2, '{}') = ''
THEN '{}'
ELSE IFNULL(product_txt_text2, '{}')
END
),
'}'
) ORDER BY product_set_group1 ASC, product_set_color ASC
),
']'
) AS product_chr_json,
(CASE
WHEN product_int_balance1 > 0
THEN 1
ELSE 0
END) AS Sort,
product_set_field1,
(CASE
WHEN product_set_field1 IS NOT NULL AND product_set_field1 <> ""
THEN product_set_field1
ELSE product_set_weight
END) AS product_set_weight,
/* Сортировка вывода товаров по папкам */
product_category.folder_int_value AS product_ind_folder,
IF(product_lnk_favorite IS NOT NULL, 'active', 'inactive') AS product_lnk_favorite,
basket_uid_id,
list_chr_name,
product_dat_checked,
(
IFNULL(JSON_UNQUOTE(JSON_EXTRACT(product_txt_text2, CONCAT('$.stocks[', '0].have.total'))), 0) +
IFNULL(JSON_UNQUOTE(JSON_EXTRACT(product_txt_text2, CONCAT('$.stocks[', '1].have.total'))), 0) +
IFNULL(JSON_UNQUOTE(JSON_EXTRACT(product_txt_text2, CONCAT('$.stocks[', '2].have.total'))), 0) +
IFNULL(JSON_UNQUOTE(JSON_EXTRACT(product_txt_text2, CONCAT('$.stocks[', '3].have.total'))), 0)
) AS product_int_balance1
FROM (
SELECT
product_uid_id,
product_cnt_account,
product_set_field5,
product_set_field1,
product_set_field2,
product_set_field3,
product_set_weight,
product_hld_holder,
product_mbr_member,
product_chr_link,
product_enm_new,
product_enm_vip,
product_enm_top,
product_enm_exists,
product_set_color,
product_set_size,
product_set_brand,
product_set_material,
product_set_print,
product_enm_spec,
product_chr_name,
product_img_image,
product_dec_price1,
product_chr_article,
product_set_dimensions,
product_set_filtercolor,
product_set_filtermaterial,
product_int_balance1,
product_int_balance2,
product_set_group1,
#product_enm_promo,
product_enm_eco,
product_txt_text,
product_txt_text2,
product_chr_box,
product_enm_action,
product_lnk_factory,
product_txt_descr,
product_txt_keywords,
product_chr_title,
product_lnk_favorite,
basket_uid_id,
product_dat_checked,
product_smp_create,
(CASE
WHEN (product_enm_eco = 'YES' OR product_enm_promo = 'YES')
THEN 'YES'
ELSE 'NO'
END) AS product_enm_promo,
list_chr_name
FROM (
SELECT
/*
В этом селекте после группировки только
product_set_group1, product_set_color имеют смысл
GROUP_CONCAT(DISTINCT product_set_size) AS product_set_size
*/
product_uid_id,
product_cnt_account,
product_set_field5,
product_set_field2,
product_set_field3,
product_hld_holder,
product_mbr_member,
product_chr_link,
product_txt_descr,
product_txt_keywords,
product_chr_title,
product_set_color,
product_set_filtercolor,
product_set_filtermaterial,
product_set_size,
product_set_brand,
product_set_material,
product_set_print,
product_lnk_factory,
(CASE
WHEN ((product_smp_create >= NOW() - INTERVAL 1 MONTH) OR product_enm_new = 'YES')
THEN 'YES'
ELSE 'NO'
END) AS product_enm_new,
product_enm_spec,
product_enm_action,
product_enm_promo,
product_enm_eco,
product_enm_vip,
product_enm_top,
product_enm_exists,
product_chr_name,
product_img_image,
product_dec_price1,
product_chr_article,
product_set_dimensions,
product_int_balance1,
product_int_balance2,
product_set_group1,
product_dat_checked,
product_txt_text,
product_txt_text2,
product_chr_box,
product_smp_create,
product_set_field1,
(CASE
WHEN product_set_field1 IS NOT NULL AND product_set_field1 <> ""
THEN product_set_field1
ELSE product_set_weight
END) AS product_set_weight,
(CASE
WHEN (product_enm_eco = 'YES' OR product_enm_promo = 'YES')
THEN 'YES'
ELSE 'NO'
END) AS product_enm_test,
list_chr_name,
basket_uid_id AS product_lnk_favorite,
basket_uid_id,
/* Сортировка вывода товаров по папкам */
product_category.folder_int_value AS product_ind_folder
FROM account_product
LEFT JOIN account_basket ON basket_cnt_account=757
AND basket_hld_holder = product_hld_holder # подшивка товаров, которые покупаем
AND basket_lnk_link = product_uid_id # ID товара, который покупаем
AND basket_mbr_member=0 # равен нулю, пока заказ не оформлен
AND basket_tbl_table = 'account_product'
AND basket_fnc_function = 100
LEFT JOIN account_folder AS product_category
ON product_category.folder_cnt_account = 757
AND product_category.folder_hld_holder = 13466
AND product_category.folder_uid_id = product_mbr_member
AND product_category.folder_ref_link IS NULL
AND product_category.folder_lng_language IS NULL
AND basket_usr_user IN (56591201)
AND basket_mdl_module = 292731
AND basket_int_flag = 1
LEFT JOIN account_list ON list_uid_id = product_lnk_factory
WHERE product_cnt_account = 757
AND product_hld_holder = 13466
AND (
product_mbr_member = 47309
OR
product_mbr_member IN (
SELECT folder_uid_id FROM account_folder
WHERE folder_uid_id = 47309
OR (
folder_cnt_account = 757
AND
folder_hld_holder = 13466
AND
folder_mbr_member = 0
AND
folder_ref_parent = 47309
)
)
OR
product_uid_id IN (
SELECT join_this
FROM webplatform.reestr_join
WHERE join_account = 757
AND join_this_holder = 13466
AND join_this_table = 'account_product'
AND join_holder = 13466
AND join_that_table = 'account_folder'
AND join_that IN (
SELECT 47309
UNION
SELECT folder_uid_id FROM account_folder
WHERE folder_ref_parent = 47309
)
)
)
AND (NULL IS NULL OR product_chr_link = NULL)
) AS product_color_groups_sorted
WHERE TRUE
GROUP BY product_set_group1, product_set_color
ORDER BY product_set_color ASC, product_set_color ASC
) AS product_color_groups
LEFT JOIN account_folder AS product_category
ON product_category.folder_cnt_account = 757
AND product_category.folder_hld_holder = 13466
AND product_category.folder_uid_id = product_mbr_member
AND product_category.folder_ref_link IS NULL
AND product_category.folder_lng_language IS NULL
WHERE TRUE
AND product_dec_price1>0
AND
(product_dat_checked >= (NOW() - INTERVAL 2 DAY) OR product_enm_top = 'YES')
AND IFNULL(product_enm_exists,'NO') = 'NO'
GROUP BY product_set_group1
ORDER BY product_ind_folder ASC, product_dec_price_real ASC, product_set_color ASC
LIMIT 60 OFFSET 0 /* module_id:292731
date()
integer()
like()
letter() _Items*/