shop_last_query | SELECT `tbl_product`.`prod_id`, `tbl_product`.`prod_no`, CONCAT(tbl_product.prod_no, '_', tblcolor.color_code) AS style_no, `tbl_product`.`prod_name`, `tbl_product`.`prod_desc`, `tbl_product`.`prod_date`, `tbl_product`.`categories`, `tbl_product`.`seque`, `tbl_product`.`seque2`, (CASE
WHEN tbl_stock.primary_color = "1" THEN tbl_product.seque
WHEN JSON_CONTAINS_PATH(tbl_stock.options, 'one', '$.variant_seque') = 1 THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(tbl_stock.options, '$.variant_seque')) AS UNSIGNED)
ELSE tbl_product.seque
END) AS var_seque, `tbl_product`.`seque2`, (CASE
WHEN tbl_stock.primary_color = "1" THEN tbl_product.seque2
WHEN JSON_CONTAINS_PATH(tbl_stock.options, 'one', '$.variant_seque2') = 1 THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(tbl_stock.options, '$.variant_seque2')) AS UNSIGNED)
ELSE tbl_product.seque2
END) AS var_seque2, `tbl_product`.`options`, `tbl_product`.`clearance`, CAST(tbl_product.prod_date as date) as product_date, `tbl_product`.`less_discount`, `tbl_product`.`less_discount` AS `retail_price`, `tbl_product`.`catalogue_price`, `tbl_product`.`catalogue_price` AS `on_sale_price`, `tbl_product`.`catalogue_price` AS `onsale_price`, `tbl_product`.`catalogue_price` AS `retail_sale_price`, `tbl_product`.`wholesale_price`, `tbl_product`.`wholesale_price_clearance`, `designer`.`des_id`, `designer`.`designer`, `designer`.`designer` as `designer_name`, `designer`.`url_structure` as `d_url_structure`, `designer`.`url_structure` as `des_slug`, `designer`.`url_structure` as `designer_slug`, `vendors`.`vendor_id`, `vendors`.`vendor_name`, `vendors`.`vendor_code`, `vendor_types`.`type`, `vendor_types`.`type` as `vendor_type`, `c2`.`category_name` AS `subcat_name`, `tblcolor`.`color_code`, `tblcolor`.`color_name`, `tbl_stock`.`st_id`, `tbl_stock`.`color_facets`, `tbl_stock`.`color_publish`, `tbl_stock`.`custom_order`, `tbl_stock`.`new_color_publish`, `tbl_stock`.`primary_color`, `tbl_stock`.`options` as `color_options`, `tbl_stock`.`options` as `stocks_options`, `tbl_stock`.`size_sxs`, `tbl_stock`.`size_ss`, `tbl_stock`.`size_sm`, `tbl_stock`.`size_sl`, `tbl_stock`.`size_sxl`, `tbl_stock`.`size_sxxl`, `tbl_stock`.`size_sxl1`, `tbl_stock`.`size_sxl2`, (tbl_stock.size_sxs + tbl_stock.size_ss + tbl_stock.size_sm + tbl_stock.size_sl + tbl_stock.size_sxl + tbl_stock.size_sxxl + tbl_stock.size_sxl1 + tbl_stock.size_sxl2) AS summode0, `tbl_stock`.`size_0`, `tbl_stock`.`size_2`, `tbl_stock`.`size_4`, `tbl_stock`.`size_6`, `tbl_stock`.`size_8`, `tbl_stock`.`size_10`, `tbl_stock`.`size_12`, `tbl_stock`.`size_14`, `tbl_stock`.`size_16`, `tbl_stock`.`size_18`, `tbl_stock`.`size_20`, `tbl_stock`.`size_22`, (tbl_stock.size_0 + tbl_stock.size_2 + tbl_stock.size_4 + tbl_stock.size_6 + tbl_stock.size_8 + tbl_stock.size_10 + tbl_stock.size_12 + tbl_stock.size_14 + tbl_stock.size_16 + tbl_stock.size_18 + tbl_stock.size_20 + tbl_stock.size_22) AS summode1, `tbl_stock`.`size_sprepack1221`, `tbl_stock`.`size_sprepack1221` AS `summode2`, `tbl_stock`.`size_ssm`, `tbl_stock`.`size_sml`, (tbl_stock.size_ssm + tbl_stock.size_sml) AS summode3, `tbl_stock`.`size_sonesizefitsall`, `tbl_stock`.`size_sonesizefitsall` AS `summode4`, `tsav`.`size_ss` AS `available_sxs`, `tsav`.`size_ss` AS `available_ss`, `tsav`.`size_sm` AS `available_sm`, `tsav`.`size_sl` AS `available_sl`, `tsav`.`size_sxl` AS `available_sxl`, `tsav`.`size_sxxl` AS `available_sxxl`, `tsav`.`size_sxl1` AS `available_ssl1`, `tsav`.`size_sxl2` AS `available_sxl2`, `tsav`.`size_0` AS `available_0`, `tsav`.`size_2` AS `available_2`, `tsav`.`size_4` AS `available_4`, `tsav`.`size_6` AS `available_6`, `tsav`.`size_8` AS `available_8`, `tsav`.`size_10` AS `available_10`, `tsav`.`size_12` AS `available_12`, `tsav`.`size_14` AS `available_14`, `tsav`.`size_16` AS `available_16`, `tsav`.`size_18` AS `available_18`, `tsav`.`size_20` AS `available_20`, `tsav`.`size_22` AS `available_22`, `tsav`.`size_sprepack1221` AS `available_sprepack1221`, `tsav`.`size_ssm` AS `available_ssm`, `tsav`.`size_sml` AS `available_ssm`, `tsav`.`size_sonesizefitsall` AS `available_sonesizefitsall`, `tso`.`size_ss` AS `onorder_sxs`, `tso`.`size_ss` AS `onorder_ss`, `tso`.`size_sm` AS `onorder_sm`, `tso`.`size_sl` AS `onorder_sl`, `tso`.`size_sxl` AS `onorder_sxl`, `tso`.`size_sxxl` AS `onorder_sxxl`, `tso`.`size_sxl1` AS `onorder_ssl1`, `tso`.`size_sxl2` AS `onorder_sxl2`, `tso`.`size_0` AS `onorder_0`, `tso`.`size_2` AS `onorder_2`, `tso`.`size_4` AS `onorder_4`, `tso`.`size_6` AS `onorder_6`, `tso`.`size_8` AS `onorder_8`, `tso`.`size_10` AS `onorder_10`, `tso`.`size_12` AS `onorder_12`, `tso`.`size_14` AS `onorder_14`, `tso`.`size_16` AS `onorder_16`, `tso`.`size_18` AS `onorder_18`, `tso`.`size_20` AS `onorder_20`, `tso`.`size_22` AS `onorder_22`, `tso`.`size_sprepack1221` AS `onorder_sprepack1221`, `tso`.`size_ssm` AS `onorder_ssm`, `tso`.`size_sml` AS `onorder_ssm`, `tso`.`size_sonesizefitsall` AS `onorder_sonesizefitsall`, `tsa`.`size_ss` AS `admin_sxs`, `tsa`.`size_ss` AS `admin_ss`, `tsa`.`size_sm` AS `admin_sm`, `tsa`.`size_sl` AS `admin_sl`, `tsa`.`size_sxl` AS `admin_sxl`, `tsa`.`size_sxxl` AS `admin_sxxl`, `tsa`.`size_sxl1` AS `admin_ssl1`, `tsa`.`size_sxl2` AS `admin_sxl2`, `tsa`.`size_0` AS `admin_0`, `tsa`.`size_2` AS `admin_2`, `tsa`.`size_4` AS `admin_4`, `tsa`.`size_6` AS `admin_6`, `tsa`.`size_8` AS `admin_8`, `tsa`.`size_10` AS `admin_10`, `tsa`.`size_12` AS `admin_12`, `tsa`.`size_14` AS `admin_14`, `tsa`.`size_16` AS `admin_16`, `tsa`.`size_18` AS `admin_18`, `tsa`.`size_20` AS `admin_20`, `tsa`.`size_22` AS `admin_22`, `tsa`.`size_sprepack1221` AS `admin_sprepack1221`, `tsa`.`size_ssm` AS `admin_ssm`, `tsa`.`size_sml` AS `admin_ssm`, `tsa`.`size_sonesizefitsall` AS `admin_sonesizefitsall`, `tsao`.`size_ss` AS `admin_onorder_sxs`, `tsao`.`size_ss` AS `admin_onorder_ss`, `tsao`.`size_sm` AS `admin_onorder_sm`, `tsao`.`size_sl` AS `admin_onorder_sl`, `tsao`.`size_sxl` AS `admin_onorder_sxl`, `tsao`.`size_sxxl` AS `admin_onorder_sxxl`, `tsao`.`size_sxl1` AS `admin_onorder_ssl1`, `tsao`.`size_sxl2` AS `admin_onorder_sxl2`, `tsao`.`size_0` AS `admin_onorder_0`, `tsao`.`size_2` AS `admin_onorder_2`, `tsao`.`size_4` AS `admin_onorder_4`, `tsao`.`size_6` AS `admin_onorder_6`, `tsao`.`size_8` AS `admin_onorder_8`, `tsao`.`size_10` AS `admin_onorder_10`, `tsao`.`size_12` AS `admin_onorder_12`, `tsao`.`size_14` AS `admin_onorder_14`, `tsao`.`size_16` AS `admin_onorder_16`, `tsao`.`size_18` AS `admin_onorder_18`, `tsao`.`size_20` AS `admin_onorder_20`, `tsao`.`size_22` AS `admin_onorder_22`, `tsao`.`size_sprepack1221` AS `admin_onorder_sprepack1221`, `tsao`.`size_ssm` AS `admin_onorder_ssm`, `tsao`.`size_sml` AS `admin_onorder_ssm`, `tsao`.`size_sonesizefitsall` AS `admin_onorder_sonesizefitsall`, `tsap`.`size_ss` AS `admin_physical_sxs`, `tsap`.`size_ss` AS `admin_physical_ss`, `tsap`.`size_sm` AS `admin_physical_sm`, `tsap`.`size_sl` AS `admin_physical_sl`, `tsap`.`size_sxl` AS `admin_physical_sxl`, `tsap`.`size_sxxl` AS `admin_physical_sxxl`, `tsap`.`size_sxl1` AS `admin_physical_ssl1`, `tsap`.`size_sxl2` AS `admin_physical_sxl2`, `tsap`.`size_0` AS `admin_physical_0`, `tsap`.`size_2` AS `admin_physical_2`, `tsap`.`size_4` AS `admin_physical_4`, `tsap`.`size_6` AS `admin_physical_6`, `tsap`.`size_8` AS `admin_physical_8`, `tsap`.`size_10` AS `admin_physical_10`, `tsap`.`size_12` AS `admin_physical_12`, `tsap`.`size_14` AS `admin_physical_14`, `tsap`.`size_16` AS `admin_physical_16`, `tsap`.`size_18` AS `admin_physical_18`, `tsap`.`size_20` AS `admin_physical_20`, `tsap`.`size_22` AS `admin_physical_22`, `tsap`.`size_sprepack1221` AS `admin_physical_sprepack1221`, `tsap`.`size_ssm` AS `admin_physical_ssm`, `tsap`.`size_sml` AS `admin_physical_ssm`, `tsap`.`size_sonesizefitsall` AS `admin_physical_sonesizefitsall`, `tbl_product`.`size_mode`, `tbl_product`.`primary_img`, `tbl_product`.`primary_img_id`, `tbl_product`.`publish`, `tbl_product`.`public`, `tbl_product`.`view_status`, `tbl_product`.`styles`, `tbl_product`.`events`, `tbl_product`.`trends`, `tbl_product`.`materials`, `tbl_product`.`seasons`, `media_library_products`.`media_path`, `media_library_products`.`media_name`, `media_library_products`.`upload_version`, `c1`.`category_slug` as `c_url_structure`, (CASE
WHEN c1.category_slug = "apparel" THEN "WMANSAPREL"
ELSE c1.category_slug
END) AS cat_folder, (CASE
WHEN c2.category_slug = "cocktail-dresses" THEN "cocktail"
WHEN c2.category_slug = "evening-dresses" THEN "evening"
ELSE c2.category_slug
END) AS sc_url_structure, (CASE
WHEN c2.category_slug = "cocktail-dresses" THEN "cocktail"
WHEN c2.category_slug = "evening-dresses" THEN "evening"
ELSE c2.category_slug
END) AS subcat_folder, (CASE
WHEN tbl_stock.custom_order = "3" THEN tbl_product.catalogue_price
WHEN tbl_stock.options LIKE '%"clearance_consumer_only":"1"%' ESCAPE '!'
THEN tbl_product.catalogue_price
ELSE tbl_product.less_discount
END) AS net_price, (CASE
WHEN
(tbl_product.size_mode = '1'
AND (tbl_stock.size_0 > '0'
OR tbl_stock.size_2 > '0'
OR tbl_stock.size_4 > '0'
OR tbl_stock.size_6 > '0'
OR tbl_stock.size_8 > '0'
OR tbl_stock.size_10 > '0'
OR tbl_stock.size_12 > '0'
OR tbl_stock.size_14 > '0'
OR tbl_stock.size_16 > '0'
OR tbl_stock.size_18 > '0'
OR tbl_stock.size_20 > '0'
OR tbl_stock.size_22 > '0'))
OR (tbl_product.size_mode = '0'
AND (tbl_stock.size_sxs > '0'
OR tbl_stock.size_ss > '0'
OR tbl_stock.size_sm > '0'
OR tbl_stock.size_sl > '0'
OR tbl_stock.size_sxl > '0'
OR tbl_stock.size_sxxl > '0'
OR tbl_stock.size_sxl1 > '0'
OR tbl_stock.size_sxl2 > '0'))
OR (tbl_product.size_mode = '2' AND (tbl_stock.size_sprepack1221 > '0'))
OR (tbl_product.size_mode = '3' AND (tbl_stock.size_ssm > '0' AND tbl_stock.size_sml > '0'))
OR (tbl_product.size_mode = '4' AND (tbl_stock.size_sonesizefitsall > '0'))
THEN '1'
ELSE '0'
END) AS with_stocks, `ytd_cs_clicks`, `ytd_ws_clicks`, (ytd_cs_clicks + ytd_ws_clicks) AS ytd_ttl_clicks, `mo_cs_clicks`, `mo_ws_clicks`, (mo_cs_clicks + mo_ws_clicks) AS mo_ttl_clicks
FROM `tbl_product`
LEFT JOIN `designer` ON `designer`.`des_id` = `tbl_product`.`designer`
LEFT JOIN `vendors` ON `vendors`.`vendor_id` = `tbl_product`.`vendor_id`
LEFT JOIN `vendor_types` ON `vendor_types`.`id` = `vendors`.`vendor_type_id`
LEFT JOIN `categories` `c1` ON `c1`.`category_id` = `tbl_product`.`cat_id`
LEFT JOIN `categories` `c2` ON `c2`.`category_id` = `tbl_product`.`subcat_id`
LEFT JOIN (SELECT * FROM tbl_stock ORDER BY primary_color DESC) as tbl_stock ON `tbl_stock`.`prod_no` = `tbl_product`.`prod_no`
LEFT JOIN `tbl_stock_available` `tsav` ON `tsav`.`st_id` = `tbl_stock`.`st_id`
LEFT JOIN `tbl_stock_onorder` `tso` ON `tso`.`st_id` = `tbl_stock`.`st_id`
LEFT JOIN `tbl_stock_admin` `tsa` ON `tsa`.`st_id` = `tbl_stock`.`st_id`
LEFT JOIN `tbl_stock_admin_onorder` `tsao` ON `tsao`.`st_id` = `tbl_stock`.`st_id`
LEFT JOIN `tbl_stock_admin_physical` `tsap` ON `tsap`.`st_id` = `tbl_stock`.`st_id`
LEFT JOIN `tblcolor` ON `tblcolor`.`color_name` = `tbl_stock`.`color_name`
LEFT JOIN `media_library_products` ON `media_library_products`.`media_id` = `tbl_stock`.`image_url_path`
LEFT JOIN (
SELECT
productclickstats_1.style_no AS ytd_pclicks_style_no,
SUM(SUBSTRING(SUBSTRING_INDEX(click_data, ',', 1), 2)) as ytd_cs_clicks,
SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(click_data, ',', 2), ',', -1)) as ytd_ws_clicks
FROM
productclickstats_1
WHERE
click_date >= 1704067200
GROUP BY
productclickstats_1.style_no
) AS ytd_pclicks ON `ytd_pclicks`.`ytd_pclicks_style_no` = CONCAT(tbl_product.prod_no, '_', tblcolor.color_code)
LEFT JOIN (
SELECT
productclickstats_1.style_no AS mo_pclicks_style_no,
SUBSTRING(SUBSTRING_INDEX(click_data, '","', 1), 3) as mo_cs_clicks,
SUBSTRING_INDEX(SUBSTRING_INDEX(click_data, '","', 2), '","', -1) as mo_ws_clicks
FROM
productclickstats_1
WHERE
click_date >= 1730419200
GROUP BY
productclickstats_1.style_no
) AS mo_pclicks ON `mo_pclicks`.`mo_pclicks_style_no` = CONCAT(tbl_product.prod_no, '_', tblcolor.color_code)
WHERE `designer`.`url_structure` <> 'tempoparis'
AND `tbl_product`.`categories` LIKE '%"200"%' ESCAPE '!'
AND (
(
tbl_product.publish = '1'
OR tbl_product.publish = '11'
) AND (
tbl_stock.new_color_publish = '1'
OR tbl_stock.new_color_publish = '11'
)
)
AND (( tbl_stock.size_12 != '0'))
ORDER BY `seque` ASC, `seque2` ASC, `tbl_product`.`prod_no` DESC, `tbl_stock`.`primary_color` DESC, `color_name` ASC
|