Награды. Сувениры оптом. Печать логотипа

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 = 46969
	  OR
	  product_mbr_member IN (
        SELECT folder_uid_id FROM account_folder
        WHERE folder_uid_id = 46969
          OR (
            folder_cnt_account = 757 
            AND 
            folder_hld_holder = 13466
            AND 
            folder_mbr_member = 0
            AND 
            folder_ref_parent = 46969
          )
	  )
	  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 46969 
            UNION 
            SELECT folder_uid_id FROM account_folder 
            WHERE folder_ref_parent = 46969
          )
        )
     )
	
	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*/