Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- product_id AS style_id,
- color,
- MaxRegularPrice,
- discountprice,
- marker
- FROM
- (SELECT
- product_id,
- id AS style_id,
- color,
- MaxRegularPrice,
- discountprice,
- SUM(marker) AS marker
- FROM
- (SELECT
- /* hotlistComponents::executeList */
- sp_product_style.product_id,
- sp_product_style.id,
- sp_product_style.color,
- sp_product_style.maxsaleprice AS DiscountPrice,
- sp_product_style.maxregularprice AS MaxRegularPrice,
- 1 AS marker
- FROM
- (SELECT
- sp_product_style.id,
- sp_product_style.product_id,
- sp_product_style.color,
- sp_product_style.NAME,
- sp_product_style.slug,
- sp_product_style.is_new,
- sp_product_style.product_image,
- sp_product_style.sorting_order,
- sp_product_style.updated_at,
- sp_product_style.created_at,
- MAX(
- COALESCE(
- ROUND(
- (
- regular_price.price * sp_discount_group.multiplier
- ) + .0049,
- 2
- ),
- regular_price.price
- )
- ) AS MaxRegularPrice,
- MAX(sale_price.price) AS MaxSalePrice,
- sp_product.in_stock = 1
- AND sp_product.ash_city_status IN ("20", "50")
- AND sp_product.assortment_check = 0
- AND COUNT(sp_product_item.id) > 0
- AND sp_product.STATUS != 'just_created' AS IsVisible
- FROM
- `sp_product`
- INNER JOIN
- `sp_product_style`
- ON (
- sp_product.sku = sp_product_style.product_id
- )
- INNER JOIN
- `sp_product_item`
- ON (
- sp_product_style.id = sp_product_item.style_id
- AND sp_product_item.ash_city_status IN (20, 50)
- )
- INNER JOIN
- `sp_price` `regular_price`
- ON (
- (
- (
- (
- (
- (
- (
- sp_product_style.id = regular_price.style_id
- AND regular_price.TYPE = 'regular'
- )
- AND regular_price.currency = 2
- )
- AND regular_price.valid_from <= '2012-10-04'
- )
- AND regular_price.valid_till >= '2012-10-04'
- )
- AND regular_price.price > 0.01
- )
- AND regular_price.price < 999
- )
- AND regular_price.SIZE IN ('L', 'ONE', 'REG', 'LXL')
- )
- INNER JOIN
- `sp_discount_group`
- ON (
- (
- sp_discount_group.group_name = 'F'
- AND sp_discount_group.from_q <= 0
- )
- AND sp_discount_group.to_q >= 0
- )
- INNER JOIN
- `sp_customer_promo` `customer_promo`
- ON (
- (
- customer_promo.customer_no = '610209'
- AND customer_promo.valid_from <= '2012-10-04'
- )
- AND customer_promo.valid_till >= '2012-10-04'
- )
- INNER JOIN
- `sp_price` `sale_price`
- ON (
- (
- (
- (
- (
- (
- (
- (
- sp_product_style.id = sale_price.style_id
- AND sale_price.pricelist_code = customer_promo.pricelist_code
- )
- AND sale_price.TYPE = 'on-sale'
- )
- AND sale_price.currency = 2
- )
- AND sale_price.valid_from <= '2012-10-04'
- )
- AND sale_price.valid_till >= '2012-10-04'
- )
- AND sale_price.price > 0.01
- )
- AND sale_price.price < 999
- )
- AND sale_price.SIZE = regular_price.SIZE
- )
- WHERE sale_price.pricelist_code IN (
- 'M31C',
- 'MDCA',
- 'MDCB',
- 'MFSC',
- 'MHCT',
- 'MHLC',
- 'MLCC',
- 'MLCT',
- 'MMMM',
- 'MSCT'
- )
- AND sp_product_item.quantity > 0
- AND sp_product_item.warehouse_id IN ('100', '500')
- GROUP BY sp_product_style.id
- HAVING (
- (
- (MAX(sale_price.price)) > 0
- AND (
- MAX(
- COALESCE(
- ROUND(
- (
- regular_price.price * sp_discount_group.multiplier
- ) + .0049,
- 2
- ),
- regular_price.price
- )
- )
- ) > 0
- )
- AND isvisible
- )
- ORDER BY sp_product.sku ASC,
- sp_product_style.color ASC) AS sp_product_style
- UNION
- ALL
- SELECT
- /* hotlistComponents::executeList */
- sp_product_style.product_id,
- sp_product_style.id,
- sp_product_style.color,
- sp_product_style.maxsaleprice AS DiscountPrice,
- sp_product_style.maxregularprice AS MaxRegularPrice,
- 2 AS marker
- FROM
- (SELECT
- sp_product_style.id,
- sp_product_style.product_id,
- sp_product_style.color,
- sp_product_style.NAME,
- sp_product_style.slug,
- sp_product_style.is_new,
- sp_product_style.product_image,
- sp_product_style.sorting_order,
- sp_product_style.updated_at,
- sp_product_style.created_at,
- MAX(
- COALESCE(
- ROUND(
- (
- regular_price.price * sp_discount_group.multiplier
- ) + .0049,
- 2
- ),
- regular_price.price
- )
- ) AS MaxRegularPrice,
- MAX(sale_price.price) AS MaxSalePrice,
- sp_product.in_stock = 1
- AND sp_product.ash_city_status IN ("20", "50")
- AND sp_product.assortment_check = 0
- AND COUNT(sp_product_item.id) > 0
- AND sp_product.STATUS != 'just_created' AS IsVisible
- FROM
- `sp_product`
- INNER JOIN
- `sp_product_style`
- ON (
- sp_product.sku = sp_product_style.product_id
- )
- INNER JOIN
- `sp_product_item`
- ON (
- sp_product_style.id = sp_product_item.style_id
- AND sp_product_item.ash_city_status IN (20, 50)
- )
- INNER JOIN
- `sp_price` `regular_price`
- ON (
- (
- (
- (
- (
- (
- (
- sp_product_style.id = regular_price.style_id
- AND regular_price.TYPE = 'regular'
- )
- AND regular_price.currency = 2
- )
- AND regular_price.valid_from <= '2012-10-04'
- )
- AND regular_price.valid_till >= '2012-10-04'
- )
- AND regular_price.price > 0.01
- )
- AND regular_price.price < 999
- )
- AND regular_price.SIZE IN ('L', 'ONE', 'REG', 'LXL')
- )
- INNER JOIN
- `sp_discount_group`
- ON (
- (
- sp_discount_group.group_name = 'F'
- AND sp_discount_group.from_q <= 0
- )
- AND sp_discount_group.to_q >= 0
- )
- INNER JOIN
- `sp_customer_promo` `customer_promo`
- ON (
- (
- customer_promo.customer_no = '610209'
- AND customer_promo.valid_from <= '2012-10-04'
- )
- AND customer_promo.valid_till >= '2012-10-04'
- )
- INNER JOIN
- `sp_price` `sale_price`
- ON (
- (
- (
- (
- (
- (
- (
- (
- sp_product_style.id = sale_price.style_id
- AND sale_price.pricelist_code = customer_promo.pricelist_code
- )
- AND sale_price.TYPE = 'on-sale'
- )
- AND sale_price.currency = 2
- )
- AND sale_price.valid_from <= '2012-10-04'
- )
- AND sale_price.valid_till >= '2012-10-04'
- )
- AND sale_price.price > 0.01
- )
- AND sale_price.price < 999
- )
- /* AND sale_price.size = regular_price.size */
- )
- WHERE sale_price.pricelist_code IN (
- 'M31C',
- 'MDCA',
- 'MDCB',
- 'MFSC',
- 'MHCT',
- 'MHLC',
- 'MLCC',
- 'MLCT',
- 'MMMM',
- 'MSCT'
- )
- AND sp_product_item.quantity > 0
- AND sp_product_item.warehouse_id IN ('100', '500')
- GROUP BY sp_product_style.id
- HAVING (
- (
- (MAX(sale_price.price)) > 0
- AND (
- MAX(
- COALESCE(
- ROUND(
- (
- regular_price.price * sp_discount_group.multiplier
- ) + .0049,
- 2
- ),
- regular_price.price
- )
- )
- ) > 0
- )
- AND isvisible
- )
- ORDER BY sp_product.sku ASC,
- sp_product_style.color ASC) AS sp_product_style) X
- GROUP BY product_id,
- id,
- MaxRegularPrice,
- discountprice) X
- WHERE marker <> 3
- ORDER BY style_id,
- color
Add Comment
Please, Sign In to add comment