Ошибка SQL: Unexpected end of JSON text in argument 1 to function 'JSON_TABLE'
SQL: [SELECT /* 654,297325 */ offer_uid_id, offer_md5_hash AS offer_chr_link, offer_chr_name, COALESCE(NULLIF(offer_txt_text,''), 'Срок поставки продукции — от 7 до 35 рабочих дней в зависимости от наличия товара на складе.') AS offer_txt_text, (CASE WHEN NULL = 'giftsprice' THEN '/i/img/giftsprice-logo.png' ELSE COALESCE(offer_img_image, company_img_image, user_img_32x32avatar, '/i/img/admos_logo683x136.png') END) AS offer_img_image, # Логотип 200x200 COALESCE(user_img_640x480image, '/i/img/1x1.gif') AS manager_img_image, # Личное фото сотрудника (CASE WHEN NULL = 'giftsprice' THEN 'giftsprice' ELSE '' END) AS offer_chr_siteclass, offer_md5_hash AS offer_md5_link, IF(user_hld_holder = 16710, COALESCE(company_chr_name,user_chr_icq), NULL) AS client_company, IF(user_hld_holder = 16710, COALESCE(company_set_info1,user_set_country), NULL) AS client_color, IF(user_hld_holder = 16710, COALESCE(company_chr_address1,user_chr_phone2), NULL) AS client_address, IF(user_hld_holder = 16710, COALESCE(company_set_metro,user_chr_skype), NULL) AS client_site, COALESCE(JSON_VALUE(offer_txt_text2, '$.manager.manager_chr_name'), user_chr_surname) AS manager_chr_name, COALESCE(JSON_VALUE(offer_txt_text2, '$.manager.manager_chr_email'), user_chr_email) AS manager_chr_email, COALESCE(JSON_VALUE(offer_txt_text2, '$.manager.manager_chr_phone'), NULLIF(company_chr_phone1,''), user_chr_phone) AS manager_chr_phone, COALESCE(JSON_VALUE(offer_txt_text2, '$.manager.manager_chr_site'), company_set_metro, NULLIF(user_chr_skype,''), 'www.admos-gifts.ru') AS manager_chr_site, COALESCE(JSON_VALUE(offer_txt_text2, '$.manager.manager_chr_address'), NULLIF(company_chr_address1,''), NULLIF(user_chr_phone2,'')) AS manager_chr_address, (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.kit')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.kit.enabled')) = 'on') THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.kit.price')) AS DECIMAL(8,2)) ELSE NULL END) AS kit_price, (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.boxing')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.boxing.enabled')) = 'on') THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.boxing.price')) AS DECIMAL(8,2)) ELSE NULL END) AS boxing_price, (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.delivery')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.delivery.enabled')) = 'on') THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.delivery.price')) AS DECIMAL(8,2)) ELSE NULL END) AS delivery_price, (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design.enabled')) = 'on') THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design.price')) AS DECIMAL(8,2)) ELSE NULL END) AS design_price, (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.safekeeping')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.safekeeping.enabled')) = 'on') THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.safekeeping.price')) AS DECIMAL(8,2)) ELSE NULL END) AS safekeeping_price, (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.unload')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.unload.enabled')) = 'on') THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.unload.price')) AS DECIMAL(8,2)) ELSE NULL END) AS unload_price, (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.weight1')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.weight1.enabled')) = 'on') THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.weight1.price')) AS DECIMAL(8,2)) ELSE NULL END) AS weight1_price, (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.width1')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.width1.enabled')) = 'on') THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.width1.price')) AS DECIMAL(8,2)) ELSE NULL END) AS width1_price, JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.kit.enabled')) AS show_kit, JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.boxing.enabled')) AS show_boxing, JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.delivery.enabled')) AS show_delivery, JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design.enabled')) AS show_design, JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.safekeeping.enabled')) AS show_safekeeping, JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.unload.enabled')) AS show_unload, JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.weight1.enabled')) AS show_weight1, JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.width1.enabled')) AS show_width1, JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.promotions')) AS promotions, ( (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.boxing')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.boxing.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.boxing.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.delivery')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.delivery.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.delivery.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.safekeeping')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.safekeeping.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.safekeeping.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.unload')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.unload.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.unload.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) ) AS offer_dec_services, (SELECT GROUP_CONCAT(CONCAT(basket_int_amount1, ' x ', product_chr_name) SEPARATOR '
') FROM account_basket INNER JOIN account_product ON product_uid_id=basket_lnk_link AND basket_hld_holder=product_hld_holder WHERE basket_cnt_account=654 AND basket_hld_holder = 12152 AND basket_mbr_member = offer_uid_id AND basket_tbl_table = 'account_product' AND basket_fnc_function = 100 ) AS product_chr_name, (SELECT COUNT(basket_uid_id) FROM account_basket INNER JOIN account_product ON product_uid_id=basket_lnk_link AND basket_hld_holder=product_hld_holder WHERE basket_cnt_account=654 AND basket_hld_holder = 12152 AND basket_mbr_member = offer_uid_id AND basket_tbl_table = 'account_product' AND basket_fnc_function = 100 ) AS offer_int_quantity, (SELECT SUM(basket_int_amount1) FROM account_basket INNER JOIN account_product ON product_uid_id=basket_lnk_link AND basket_hld_holder=product_hld_holder WHERE basket_cnt_account=654 AND basket_hld_holder = 12152 AND basket_mbr_member = offer_uid_id AND basket_tbl_table = 'account_product' AND basket_fnc_function = 100 ) AS offer_int_amount1, (SELECT SUM(basket_int_amount1*basket_int_price1) FROM account_basket INNER JOIN account_product ON product_uid_id=basket_lnk_link AND basket_hld_holder=product_hld_holder WHERE basket_cnt_account=654 AND basket_hld_holder = 12152 AND basket_mbr_member = offer_uid_id AND basket_tbl_table = 'account_product' AND basket_fnc_function = 100 ) AS offer_dec_total1, (SELECT SUM(basket_int_amount1*basket_int_price2) FROM account_basket INNER JOIN account_product ON product_uid_id=basket_lnk_link AND basket_hld_holder=product_hld_holder WHERE basket_cnt_account=654 AND basket_hld_holder = 12152 AND basket_mbr_member = offer_uid_id AND basket_tbl_table = 'account_product' AND basket_fnc_function = 100 ) AS offer_dec_total2, # Цена без нанесения без скидки prices.sum_dec_product AS offer_dec_total3, # Цена с нанесением без скидки prices.sum_dec_product + prices.sum_dec_printing + ( (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.boxing')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.boxing.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.boxing.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.delivery')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.delivery.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.delivery.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.safekeeping')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.safekeeping.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.safekeeping.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.unload')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.unload.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.unload.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) ) AS offer_dec_total4, # Итого со скидкой и нанесением БЕЗ ДИЗАЙНА ( # Сумма товаров с нанесением и доп. услугами КРОМЕ дизайна ( # Если выбрана скидка по всей сумме КП, то считаем вручную нанесение + РРЦ, если не включено, то берем готовую рассчитанную в JS сумму IF( JSON_EXTRACT(offer_txt_text2, '$.settings') IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_totals')) = 'on', prices.sum_dec_productfixed + prices.sum_dec_printingfixed, prices.sum_printing_discount_totalfixed ) # Доп. услуги + ( (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.boxing')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.boxing.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.boxing.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.delivery')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.delivery.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.delivery.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.safekeeping')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.safekeeping.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.safekeeping.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.unload')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.unload.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.unload.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) ) ) # Считаем скидку(коммерческая скидка + доп. скидки по чекбоксам в КП) * IF( JSON_EXTRACT(offer_txt_text2, '$.settings') IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_totals')) = 'on', ROUND( IFNULL( # Если скидка ручная, то она может быть любой 1 - NULLIF(JSON_VALUE(offer_txt_text2, '$.settings.manual_discount'), 1), # Максимальная скидка может быть только 20% IF( ( discount.data_int_value - IFNULL(NULLIF(JSON_VALUE(offer_txt_text2, '$.settings.settings_discount_additional_total'), ''), 0) ) < IF(discount.data_int_value < discount.data_int_value10, discount.data_int_value, discount.data_int_value10), IF(discount.data_int_value < discount.data_int_value10, discount.data_int_value, discount.data_int_value10), ( discount.data_int_value - IFNULL(terms.answer_dec_price / 100, 0) - IFNULL(NULLIF(JSON_VALUE(offer_txt_text2, '$.settings.settings_discount_additional_total'), ''), 0) ) ) ), 2), 1 ) # Прибавляем товары с фиксируемой ценой, т.к. для них не должна применяться скидка + prices.sum_printing_discount_totalnonfixed ) AS offer_dec_total5, # Доп. услуга дизайн/макеты (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design.enabled')) = 'on') THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design.price')) AS DECIMAL(8,2)) ELSE 0 END) AS offer_dec_design, # Итого со скидкой и нанесением С ДИЗАЙНОМ ( # Сумма товаров с нанесением и доп. услугами КРОМЕ дизайна ( # Если выбрана скидка по всей сумме КП, то считаем вручную нанесение + РРЦ, если не включено, то берем готовую рассчитанную в JS сумму IF( JSON_EXTRACT(offer_txt_text2, '$.settings') IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_totals')) = 'on', prices.sum_dec_product + prices.sum_dec_printing, prices.sum_printing_discount_total ) # Доп. услуги + ( (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.boxing')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.boxing.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.boxing.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.delivery')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.delivery.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.delivery.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.safekeeping')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.safekeeping.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.safekeeping.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.unload')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.unload.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.unload.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) ) ) # Считаем скидку(коммерческая скидка + доп. скидки по чекбоксам в КП) * IF( JSON_EXTRACT(offer_txt_text2, '$.settings') IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_totals')) = 'on', ROUND( IFNULL( # Если скидка ручная, то она может быть любой 1 - NULLIF(JSON_VALUE(offer_txt_text2, '$.settings.manual_discount'), 1), # # Максимальная скидка может быть только 20% IF( ( discount.data_int_value - IFNULL(NULLIF(JSON_VALUE(offer_txt_text2, '$.settings.settings_discount_additional_total'), ''), 0) ) < IF(discount.data_int_value < discount.data_int_value10, discount.data_int_value, discount.data_int_value10), IF(discount.data_int_value < discount.data_int_value10, discount.data_int_value, discount.data_int_value10), ( discount.data_int_value - IFNULL(terms.answer_dec_price / 100, 0) - IFNULL(NULLIF(JSON_VALUE(offer_txt_text2, '$.settings.settings_discount_additional_total'), ''), 0) ) ) ), 2), 1 ) ) AS offer_dec_total6, # Отображать ли строки с дизайном IF( (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design.enabled')) = 'on'), 'on', 'off' ) AS show_total_design, # Скидка ROUND( IFNULL( # Если скидка ручная, то она может быть любой NULLIF(JSON_VALUE(offer_txt_text2, '$.settings.manual_discount'), 1) * 100, # Максимальная скидка может быть только 20% IF( ( 1 - discount.data_int_value + IFNULL(NULLIF(JSON_VALUE(offer_txt_text2, '$.settings.settings_discount_additional_total'), ''), 0) ) * 100 > IF(discount.data_int_value < discount.data_int_value10, (1 - discount.data_int_value) * 100, (1 - discount.data_int_value10) * 100), IF(discount.data_int_value < discount.data_int_value10, (1 - discount.data_int_value) * 100, (1 - discount.data_int_value10) * 100), ( 1 - discount.data_int_value + IFNULL(terms.answer_dec_price / 100, 0) + IFNULL(NULLIF(JSON_VALUE(offer_txt_text2, '$.settings.settings_discount_additional_total'), ''), 0) ) * 100 ) ), 1) AS offer_int_discount, # Стоимость нанесения prices.sum_dec_printing AS offer_dec_printing, DATE_FORMAT(offer_smp_create, '%d.%m.%Y') AS offer_dat_create, DATE_FORMAT(offer_smp_update + INTERVAL 2 WEEK, '%d.%m.%Y') AS offer_dat_expire, JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings')) AS show_settings, # Акции (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.promotions_enabled_checkbox') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.promotions_enabled_checkbox')) END) AS show_promotions, # Портфолио (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.portfolio_enabled_checkbox') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.portfolio_enabled_checkbox')) END) AS show_portfolio, # Презентации (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.presentation_enabled_checkbox') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.presentation_enabled_checkbox')) END) AS show_presentation, # Статьи (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.articles_enabled_checkbox') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.articles_enabled_checkbox')) END) AS show_articles, # Идеи (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.ideas_enabled_checkbox') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.ideas_enabled_checkbox')) END) AS show_ideas, # Услуги (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.services_enabled_checkbox') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services_enabled_checkbox')) END) AS show_services, # Менеджер (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_manager_name')) END) AS show_manager_name, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_manager_phone')) END) AS show_manager_phone, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_manager_email')) END) AS show_manager_email, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_manager_address')) END) AS show_manager_address, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_manager_site')) END) AS show_manager_site, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_offer_logo')) END) AS show_offer_logo, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_offer_header')) END) AS show_offer_header, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_offer_text')) END) AS show_offer_text, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'off' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_offer_summary')) END) AS show_offer_summary, # Товары (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_offer_products')) END) AS show_offer_products, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_offer_vat')) END) AS show_offer_vat, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'с НДС' WHEN JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_offer_vat')) = 'on' THEN 'с НДС' ELSE 'без НДС' END) AS vat, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_image')) END) AS show_product_image, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_article')) END) AS show_product_article, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_name')) END) AS show_product_name, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_features')) END) AS show_product_features, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_color')) END) AS show_product_color, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_size')) END) AS show_product_size, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_material')) END) AS show_product_material, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_brand')) END) AS show_product_brand, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_weight')) END) AS show_product_weight, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_dimensions')) END) AS show_product_dimensions, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_box')) END) AS show_product_box, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_balance')) END) AS show_product_balance, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_price')) END) AS show_product_price, (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'off' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_comment')) END) AS show_product_comment, # Нанесение (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_print')) END) AS show_product_print, # Тираж (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_printing_quantity')) END) AS show_quantity, # Цена без нанесения (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_printing_price_product')) END) AS show_price_product, # Цена за нанесение (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_printing_price_printing')) END) AS show_price_printing, # Цена с нанесением (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_printing_price_product_printing')) END) AS show_price_product_printing, # Итого с нанесением и скидкой (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_printing_price_product_printing_discount_total')) END) AS show_price_product_printing_discount_total, # Приписка "со скидкой" для "Итого с нанесением и скидкой" (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN '' ELSE IF(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_printing_price_product_printing_discount_total')) = 'on', ' и скидкой', '') END) AS show_price_product_printing_discount_total_caption, # Цена за штуку с нанесением и скидкой (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_printing_price_product_printing_discount')) END) AS show_price_product_printing_discount, # Скидки по общей сумме КП (CASE WHEN JSON_EXTRACT(offer_txt_text2, '$.settings') IS NULL THEN 'on' ELSE JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.settings.settings_product_totals')) END) AS show_product_totals, # Не используется 'on' AS show_price_product_total, offer_smp_create FROM account_offer # Пользователь, который создал КП INNER JOIN account_user ON user_uid_id = offer_lnk_manager AND user_cnt_account = 654 AND user_hld_holder IN(16710,6498) AND user_mbr_member = 0 # Группа доступа INNER JOIN account_list AS permission ON permission.list_cnt_account = 654 AND permission.list_hld_holder = 7119 AND permission.list_mbr_member = 0 AND permission.list_uid_id = user_lnk_permission # Организация клиента LEFT JOIN account_company ON company_uid_id = user_lnk_manager AND company_cnt_account = 654 AND company_hld_holder = 17310 AND company_mbr_member = 0 AND user_hld_holder = 16710 # Вычисляем общие цены всего КП, тут специально используется 46272 из прототипа, заменять на ON basket_mbr_member = offer_uid_id НЕ НУЖНО, производительность падает в десятки раз LEFT JOIN ( SELECT # Сумма за все товары в КП ROUND(SUM(s.price_product * s.quantity), 2) AS sum_dec_product, # Сумма за все товары в КП за исключением поставщиков с фиксированными ценами ROUND(SUM(IF(product_lnk_style IN (414416), 0, (s.price_product * s.quantity))), 2) AS sum_dec_productfixed, # Сумма за все нанесения в КП ROUND(SUM(s.price_printing * s.quantity), 2) AS sum_dec_printing, # Сумма за все нанесения в КП за исключением поставщиков с фиксированными ценами ROUND(SUM(IF(product_lnk_style IN (414416), 0, s.price_printing * s.quantity)), 2) AS sum_dec_printingfixed, # Сумма за все товары и нанесения в КП со скидкой ROUND(SUM(s.price_product_printing_discount_total), 2) AS sum_printing_discount_total, # Сумма за все товары и нанесения в КП со скидкой за исключением поставщиков с фиксированными ценами ROUND(SUM(IF(product_lnk_style IN (414416), 0, s.price_product_printing_discount_total)), 2) AS sum_printing_discount_totalfixed, # Сумма за все товары и нанесения в КП со скидкой только поставщиков с фиксированными ценами ROUND(SUM(IF(product_lnk_style IN (414416), s.price_product_printing_discount_total, 0)), 2) AS sum_printing_discount_totalnonfixed FROM account_basket t JOIN ( SELECT basket_txt_text, product_lnk_style, MIN(basket_uid_id) AS min_id FROM account_basket b LEFT JOIN account_product ON product_uid_id = basket_lnk_link WHERE basket_cnt_account = 654 AND basket_hld_holder IN (12152, 13644, 14380) AND basket_mbr_member = 46272 GROUP BY product_set_group1, product_set_color ) AS g ON t.basket_uid_id = g.min_id LEFT JOIN JSON_TABLE( g.basket_txt_text, '$.printings[*]' COLUMNS ( printing_ord FOR ORDINALITY, NESTED PATH '$.sets[*]' COLUMNS ( set_ord FOR ORDINALITY, price_product DECIMAL(18,2) PATH '$.price_product' DEFAULT 0 ON EMPTY, price_printing DECIMAL(18,2) PATH '$.price_printing' DEFAULT 0 ON EMPTY, quantity DECIMAL(18,2) PATH '$.quantity' DEFAULT 0 ON EMPTY, price_product_printing_discount_total DECIMAL(18,2) PATH '$.price_product_printing_discount_total' DEFAULT 0 ON EMPTY ) ) ) s ON TRUE GROUP BY basket_mbr_member ) AS prices ON TRUE # Условия оплаты LEFT JOIN account_answer terms ON terms.answer_uid_id = CAST(JSON_VALUE(offer_txt_text2, '$.settings.settings_terms_of_payment') AS INTEGER) # Скидка по коммерческой политике LEFT JOIN account_data discount ON discount.data_cnt_account = 654 AND discount.data_hld_holder = 17051 AND discount.data_mbr_member = permission.list_lnk_manager AND ( prices.sum_dec_productfixed + prices.sum_dec_printingfixed + ( (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.design.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.boxing')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.boxing.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.boxing.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.delivery')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.delivery.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.delivery.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.safekeeping')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.safekeeping.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.safekeeping.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) + (CASE WHEN (JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.unload')) IS NOT NULL AND JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.unload.enabled')) = 'on') THEN IFNULL(CAST(JSON_UNQUOTE(JSON_EXTRACT(offer_txt_text2, '$.services.unload.price')) AS DECIMAL(8, 2)), 0) ELSE 0 END) ) ) BETWEEN discount.data_int_value1 AND discount.data_int_value2 WHERE offer_cnt_account = 654 AND offer_hld_holder = 13644 AND offer_mbr_member = 0 AND (46272 IS NULL OR offer_uid_id = 46272) AND offer_md5_hash LIKE ? ORDER BY offer_uid_id DESC LIMIT 1 /* module_id:297325 URN */]