New
Акция
Hit
Sale
99,00
New
Акция
Hit
Sale
126,00
New
Акция
Hit
Sale
194,00
New
Акция
Hit
Sale
305,00
New
Акция
Hit
Sale
406,00
New
Акция
Hit
Sale
447,00
New
Акция
Hit
Sale
987,00
New
Акция
Hit
Sale
1 859,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 (598702254)
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 = 49147
OR
product_mbr_member IN (
SELECT folder_uid_id FROM account_folder
WHERE folder_uid_id = 49147
OR (
folder_cnt_account = 757
AND
folder_hld_holder = 13466
AND
folder_mbr_member = 0
AND
folder_ref_parent = 49147
)
)
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 49147
UNION
SELECT folder_uid_id FROM account_folder
WHERE folder_ref_parent = 49147
)
)
)
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'
/*Доп. условия из QUERY_STRING (галочка qs-select стоит)*/
AND ((product_enm_promo /*product_enm_promo*/ LIKE CONCAT('%', ?, '%')))
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('YES')
letter() _Items*/