Джемперы
SELECT SQL_CALC_FOUND_ROWS
product_uid_id,
product_cnt_account,
product_hld_holder,
product_mbr_member,
product_chr_link,
# Для листинга
product_chr_name,
product_chr_article,
COALESCE(product_img_image, '/i/img/no_product.png') AS product_img_image,
product_dec_price1,
# Общие данные
product_txt_text,
product_txt_text2,
product_set_group1,
product_set_brand,
LOWER(product_set_color) AS product_set_color,
product_set_size,
product_set_dimensions,
product_set_material,
product_set_print,
product_int_balance1,
IF(product_set_group1 is NULL OR product_set_group1 ='',product_chr_article,product_set_group1) AS product_set_group,
product_set_color AS product_color,
product_dat_checked,
# Мета
(CASE
WHEN (product_txt_keywords = '' OR product_txt_keywords IS NULL)
THEN CONCAT(LOWER(product_chr_name),' ', product_dec_price1,' москва купить продажа заказать магазин интернет характеристики ', LOWER(product_set_material))
ELSE product_txt_keywords
END) AS product_txt_keywords,
(CASE
WHEN (product_chr_title = '' OR product_chr_title IS NULL)
THEN CONCAT('Товар ', product_chr_name,' - это вы можете купить в Москве по цене ', product_dec_price1,' руб.')
ELSE product_chr_title
END) AS product_chr_title,
(CASE
WHEN (product_txt_descr = '' OR product_txt_descr IS NULL)
THEN CONCAT('Закажите и купите ', LOWER(product_chr_name), ' в нашем интернет-магазине по низкой цене с доставкой по Москве и Подмосковью.')
ELSE product_txt_descr
END) AS product_txt_descr,
product_ind_index,
product_int_hasbalnce,
product_int_manual,
(
CAST(IFNULL(JSON_UNQUOTE(JSON_EXTRACT(product_txt_text2, CONCAT('$.stocks[', '0].have.total'))), 0) AS UNSIGNED)
#+ CAST(IFNULL(JSON_UNQUOTE(JSON_EXTRACT(product_txt_text2, CONCAT('$.stocks[', '1].have.total'))), 0) AS UNSIGNED)
#+ CAST(IFNULL(JSON_UNQUOTE(JSON_EXTRACT(product_txt_text2, CONCAT('$.stocks[', '2].have.total'))), 0) AS UNSIGNED)
) AS product_int_realbalnce
,
# JSON для цветов в листинге
CONCAT(
'[',
GROUP_CONCAT(
CONCAT(
'{"image":"',
REPLACE(product_img_image, 'office/758', '/250x250xffffff'),
'","article":"',
product_chr_article,
'","name":"',
REPLACE(product_chr_name, '"', "'"),
'","price":"',
IF(ROUND(IFNULL(product_dec_price1, 0)) = 0, NULL, product_dec_price1),
'","balance":"',
IF(IFNULL(product_int_balance1, 0) = 0, NULL, product_int_balance1),
'","link":"',
product_chr_link,
'"}'
)
),
']'
) AS product_chr_json
FROM (
SELECT
product_uid_id,
product_cnt_account,
product_hld_holder,
product_mbr_member,
product_chr_link,
product_ind_index,
# Для листинга
product_chr_name,
product_chr_article,
COALESCE(product_img_image, '/i/img/no_product.png') AS product_img_image,
product_dec_price1,
# Общие данные
product_txt_text,
product_txt_text2,
product_set_group1,
#IF(product_set_group1 is NULL OR product_set_group1 ='',product_chr_article,product_set_group1) AS product_set_group1,
product_set_brand,
product_set_color,
product_set_size,
product_set_dimensions,
product_set_material,
product_set_print,
product_int_balance1,
product_txt_descr,
product_txt_keywords,
product_chr_title,
product_dat_checked,
(CASE
WHEN (
CAST(IFNULL(JSON_UNQUOTE(JSON_EXTRACT(product_txt_text2, CONCAT('$.stocks[', '0].have.total'))), 0) AS UNSIGNED)
#+ CAST(IFNULL(JSON_UNQUOTE(JSON_EXTRACT(product_txt_text2, CONCAT('$.stocks[', '1].have.total'))), 0) AS UNSIGNED)
#+ CAST(IFNULL(JSON_UNQUOTE(JSON_EXTRACT(product_txt_text2, CONCAT('$.stocks[', '2].have.total'))), 0) AS UNSIGNED)
) > 0
THEN 1
ELSE 0
END) AS product_int_hasbalnce,
(CASE
WHEN product_enm_spec = 'YES'
THEN 1
ELSE 0
END) AS product_int_manual
FROM account_product
WHERE product_cnt_account = 758
AND product_hld_holder = 13519
AND (
product_mbr_member = 47201
OR
product_mbr_member IN (
SELECT folder_uid_id FROM account_folder
WHERE folder_uid_id = 47201
OR (
folder_cnt_account = 758
AND
folder_hld_holder = 13519
AND
folder_mbr_member = 0
AND
folder_ref_parent = 47201
)
)
OR
product_uid_id IN (
SELECT join_this
FROM reestr_join
WHERE join_account = 758
AND join_this_holder = 13519
AND join_this_table = 'account_product'
AND join_holder = 13519
AND join_that_table = 'account_folder'
AND join_that IN (
SELECT 47201
UNION
SELECT folder_uid_id FROM account_folder
WHERE folder_ref_parent = 47201
)
)
)
AND
IF(NULL IS NOT NULL, product_chr_link = NULL, TRUE)
AND
IF(product_enm_spec='NO',product_dat_checked >= NOW() - INTERVAL 5 DAY,true)
) AS product_color_groups
WHERE TRUE
AND product_dec_price1>0
# Группировка по групповому артикулу
GROUP BY IF(product_set_group1 is NULL OR product_set_group1 ='',product_chr_article,product_set_group1)
ORDER BY product_int_manual ASC, product_int_hasbalnce DESC, product_ind_index ASC
LIMIT 18 OFFSET 0 /* module_id:294326
date()
integer()
like()
letter() _Items*/
