Коробка под чай

SELECT product_uid_id, product_cnt_account, product_hld_holder, product_mbr_member, product_chr_link, # Фильтр по образцам sample_uid_id, IF(sample_uid_id IS NULL,0,1) AS product_int_sample, IF(sample_uid_id IS NOT NULL AND NULL IS NOT NULL AND 'user' = 'admos','мс', '') AS product_chr_sample, IF(sample_uid_id IS NULL,'hide', 'hide') AS `sample-class`, IF(sample_uid_id IS NULL,'hide', 'hide') AS `request-class`, # Базовая информация product_chr_name, COALESCE(NULLIF(product_img_image, ''), '/i/nophoto.png') AS product_img_image, product_chr_article, product_dec_price, IFNULL(product_int_balance1, 0) AS product_int_balance1, IFNULL(product_int_balance2, 0) AS product_int_balance2, ROUND(IFNULL(product_int_balance1, 0) + IFNULL(product_int_balance2, 0), 0) AS product_int_balance, IF(0 = 0, IF(product_dec_price1 = 0, 9999, product_dec_price1), IF(product_dec_price1 = 0, 0, product_dec_price1)) AS product_dec_sortprice, # Артикула для упаковок product_txt_text, # Данные о складах product_txt_text2, IF(NULLIF(product_txt_text2, '') IS NULL, 'stocks-singe', '') AS sizes_class, # Информация на конечной + для фильтра product_set_color, REPLACE(product_set_material, ';', ', ') AS product_set_material, product_lnk_factory, REPLACE(product_set_print, ';', ', ') AS product_set_print, product_set_dimensions, # Характеристики product_set_weight, product_chr_box, product_set_field1, product_set_field2, product_set_field3, product_set_field4, product_set_field5, product_set_field6, product_set_field7, product_set_field10, product_set_field12, # Флаги product_enm_action, product_enm_exists, # Для связи со складом product_lnk_custom, # Вместо вложенного модуля в каждый элемент листинга используем следующий метод для сбора # информации о цветах товара, из них генерируем JSON который в последствии будет распарсен # через JS и вставлен как HTML для каждого элемента CONCAT( '[', GROUP_CONCAT( CONCAT( '{"color":"', IFNULL(product_set_field10, '#fff'), '","id":"', product_uid_id, '","image":"', REPLACE(COALESCE(product_img_image, '/i/nophoto.png'), 'office/654', '/cc250x250x10'), '","article":"', product_chr_article, '","name":"', REPLACE(product_chr_name, '"', "'"), '","price":"', IF(ROUND(IFNULL(product_dec_price1, 0)) = 0, NULL, product_dec_price1), '","balance":"', IFNULL(product_int_balance1, 0), '","balance2":"', IFNULL(product_int_balance2, 0), '","from_to":"', CONCAT(ROUND(IFNULL(data_int_value1, 0)), ' - ', ROUND(IFNULL(data_int_value2, 0))), '","link":"', product_chr_link, '"}' ) ), ']' ) AS product_chr_json, # Данные со склада # Срок поставки от ROUND(data_int_value1) AS product_int_term1, # Срок поставки до ROUND(data_int_value2) AS product_int_term2, # Группировка групповому артикулу product_set_group1, # Бренд IFNULL(factory.list_chr_name,'Noname') AS product_chr_brand, # Количество комментариев product_int_comments, # Ключи для калькулятора нанесения product_key_prints, # Мета (CASE WHEN (product_txt_keywords = '' OR product_txt_keywords IS NULL) THEN CONCAT(LOWER(product_chr_name),', ', LOWER(product_chr_name),' оптом, ', LOWER(product_chr_name),' с логотипом') ELSE product_txt_keywords END) AS product_txt_keywords, CONCAT(product_chr_name,' оптом под нанесение , Арт. ',product_chr_article,' | ADMOS') AS product_chr_title, IF( ROUND(IFNULL(product_int_balance1, 0) + IFNULL(product_int_balance2, 0), 0)>0, CONCAT(product_chr_name, ' оптом со склада под нанесение: тампопечать, УФ-печать, тиснение, шелкография. Для рекламных агентств скидка. В наличии ', ROUND(IFNULL(product_int_balance1, 0) + IFNULL(product_int_balance2, 0), 0) ,' штук оптом. Арт. ',product_chr_article), CONCAT(product_chr_name, ' оптом со склада под нанесение: тампопечать, УФ-печать, тиснение, шелкография. Для рекламных агентств скидка. Арт. ',product_chr_article) ) AS product_txt_descr, # Для избранного favorite.basket_uid_id AS favorite_uid_id, IF(favorite.basket_uid_id IS NOT NULL, 'active', '') AS favorite_button_class, # Для рейтинга COUNT(gbook_uid_id) AS rating_total, (CEILING(AVG(gbook_int_rating) / 0.5) * 0.5) AS rating_average, #IF(COUNT(gbook_uid_id) = 0, 'reviews-empty', '') AS reviews_empty_class, #IF(COUNT(gbook_uid_id) = 0, 'false', 'true') AS reviews_tooltip_disable, #IF(COUNT(gbook_uid_id) = 0, 'js-btn-add-review', '') AS reviews_add_class, #http://support.plarson.ru/issues/11646 # Есть ли комментарий IF(gbook_uid_id IS NOT NULL, 'YES', 'NO') AS product_enm_comments, # Копирование названия и артикула, только сотрудники IF('user' = 'admos', 'faze-copy-text', '') AS copy_class, # Проверка на существование упаковки в разделе IF(NULLIF(NULL, '') IS NULL, 'hide', '') AS box_class, IF(NULLIF(NULL, '') IS NULL, 'faze-active', '') AS not_box_class, # Наличае/отсутсвие на складе https://schema.org/ItemAvailability IF(product_int_balance1>0 OR product_int_balance2>0,'InStock','OutOfStock') AS schema_availability, CONCAT('http://', 'admos-gifts.ru','/300x300xffffff/',product_img_image) AS og_meta_image, CONCAT('https://', 'admos-gifts.ru','/300x300xffffff/',product_img_image) AS og_meta_image_s, # Сортировка IF(42096 IN (42076, 42077), product_smp_create, 1) AS sort_by1, IF(product_enm_top = 'YES', 1, 0) AS sort_by2 FROM ( SELECT product.product_uid_id, product.product_cnt_account, product.product_hld_holder, product.product_mbr_member, product.product_chr_link, # Базовая информация product.product_chr_name, product.product_dec_price1, product.product_img_image, product.product_chr_article, IFNULL(product.product_dec_price1, 0) AS product_dec_price, COALESCE(NULLIF(product.product_int_balance1, ''), 0) AS product_int_balance1, COALESCE(NULLIF(product.product_int_balance2, ''), 0) AS product_int_balance2, # Описание product.product_txt_text, # Данные по складам product.product_txt_text2, # Информация на конечной + для фильтра product.product_set_color, product.product_set_material, product.product_lnk_factory, product.product_set_print, product.product_set_size, product.product_set_dimensions, # Характеристики product.product_set_weight, product.product_chr_box, product.product_set_field1, product.product_set_field2, product.product_set_field3, product.product_set_field4, product.product_set_field5, product.product_set_field6, product.product_set_field7, product.product_set_field10, product.product_set_field12, product.product_smp_create, # Флаги product.product_enm_action, product.product_enm_exists, # Для связи со складом product.product_lnk_custom, # Группировка групповому артикулу product.product_set_group1, # Количество комментариев ( SELECT COUNT(gbook_uid_id) FROM account_gbook WHERE gbook_cnt_account = 654 AND gbook_hld_holder = 12152 AND gbook_mbr_member = product.product_uid_id ) AS product_int_comments, # Сортировка product.product_enm_top, # Мета product.product_txt_keywords, # Ключи для калькулятора нанесения MAKE_SET(product.product_chk_format, 'fancywork', 'thermo', 'silk', 'pad', 'lazer', 'uv', 'plates', 'Шильд спектрум', 'lettering', 'decal', 'Смоляная наклейка', 'Сублимация', 'digital', 'Шеврон', 'Офсет', 'Этикетка', 'Не подлежит персонализации') AS product_key_prints, sample.product_uid_id AS sample_uid_id FROM account_product AS product # Линкуем образцы LEFT JOIN account_product AS sample ON sample.product_cnt_account = 654 AND sample.product_hld_holder = 13792 AND sample.product_mbr_member = 0 AND sample.product_chr_article = product.product_chr_article WHERE product.product_cnt_account = 654 AND product.product_hld_holder = 12152 AND ( product.product_mbr_member = 42096 OR product.product_mbr_member IN ( SELECT folder_uid_id FROM account_folder WHERE folder_uid_id = 42096 OR ( folder_cnt_account = 654 AND folder_hld_holder = 12152 AND folder_mbr_member = 0 AND folder_ref_parent = 42096 ) ) ) # Та самая выборка для корректной работы ключа AND product.product_lnk_parent = 42095 # Только для сотрудников AND IF(product.product_chr_article LIKE '246%', 'user' = 'admos', TRUE) AND IF('korobka-pod-chaj' IS NOT NULL, product.product_chr_link = 'korobka-pod-chaj', TRUE) GROUP BY product_set_group1, product_set_color ) AS product_color_groups # Склад LEFT JOIN account_data ON data_cnt_account = 654 AND data_mbr_member = 0 AND data_hld_holder = 12368 AND data_uid_id = product_lnk_custom # Бренды LEFT JOIN account_list AS factory ON factory.list_cnt_account = 654 AND factory.list_hld_holder = 6552 AND factory.list_uid_id = product_lnk_factory # Избранное LEFT JOIN account_basket AS favorite ON favorite.basket_cnt_account = 654 AND favorite.basket_hld_holder = 12152 AND favorite.basket_mbr_member = 0 AND favorite.basket_int_flag = 1 AND favorite.basket_usr_user IN (NULL, 65662822) AND favorite.basket_lnk_link = product_uid_id # Рейтинг LEFT JOIN account_gbook AS rating ON gbook_cnt_account = 654 AND gbook_hld_holder = 12152 AND gbook_mbr_member = product_uid_id WHERE IF( 42096 = 46329, 'user' = 'admos', TRUE ) AND 42096 NOT IN (41984) AND product_chr_link LIKE ? # Группировка по групповому артикулу GROUP BY product_set_group1 ORDER BY sort_by1 DESC, sort_by2 DESC, product_dec_sortprice LIMIT 1 /*URN item*/

, ,