promo.sql

gistfile1.sql

SELECT DISTINCT
    unity.id AS idUnity,
    unity.name,
    unity.active,
    NULL AS idFact,
    NULL AS descricao,
    NULL AS title,
    TRUNCATE((SELECT 
                ST_DISTANCE_SPHERE(POINT(- 30.0275415, - 51.1826964),
                            POINT(unity.lng, unity.lat))
            ),
        2) AS distance
FROM
    marketplace.unity unity
        LEFT JOIN
    marketplace.unity_item unityItem ON unity.id = unityItem.unity_id
        LEFT JOIN
    marketplace.marketplace_unity marketplaceUnity ON marketplaceUnity.id_unity = unity.id
WHERE
    unityItem.price < unityItem.original_price
        AND unity.active = 1
        AND marketplaceUnity.id_marketplace = 1
        AND unityItem.active = 1
        AND NOT EXISTS( SELECT 
            *
        FROM
            marketplace.product_category pc,
            marketplace.unity_item_product_category_xref uipcx
        WHERE
            pc.id_product_category_type = 2
                AND pc.id = uipcx.product_category_id
                AND uipcx.unity_item_id = unityItem.id)
        AND (unity.order_enabled = 1
        AND ((unity.order_delivery_enabled = 1)
        OR (unity.order_take_away_enabled = 1)
        OR (unity.order_short_delivery_enabled = 1)
        OR (unity.order_voucher_enabled = 1))) 
UNION (SELECT DISTINCT
    unity.id AS idUnity,
    unity.name,
    unity.active,
    fact.id AS idFact,
    fact.description AS descricao,
    fact.title AS title,
    TRUNCATE((SELECT 
                ST_DISTANCE_SPHERE(POINT(- 30.0275415, - 51.1826964),
                            POINT(unity.lng, unity.lat))
            ),
        2) AS distance
FROM
    marketplace.fact fact
        LEFT JOIN
    marketplace.unity unity ON fact.id_unity = unity.id
WHERE
    fact.fact_type_id = 2
        AND fact.id_marketplace = 1
        AND fact.active = 1
        AND NOW() BETWEEN fact.timestamp_begin AND fact.timestamp_end) ORDER BY distance