ap2i/sql/schema-extensions/001-view_public_events.sql

CREATE OR REPLACE VIEW view_public_events
AS
    SELECT
        b.uuid, 
        b.organization_id AS organizationId, 
        b.name, 
        b.description, 
        b.url, 
        b.datetimeStart, 
        b.datetimeEnd, 
        null as priceMini, 
        null as priceMaxi, 
        null as categoryCode,
        p.name AS placeName, 
        p.description AS placeDescription, 
        p.floorSize AS placeFloorSize, 
        p.capacity AS placeCapacity,
        ap.addressCity AS city, 
        ap.postalCode,
        TRIM(BOTH ' ' FROM CONCAT( IFNULL(ap.streetAddress, ''), ' ', IFNULL(ap.streetAddressSecond, ''), ' ', IFNULL(ap.streetAddressThird, ''))) AS streetAddress,
        ap.longitude, 
        ap.latitude,
        r.name AS roomName, 
        r.description AS roomDescription, 
        r.localisation AS roomLocalisation, 
        r.capacity AS roomCapacity, 
        r.floorSize AS roomFloorSize,
        IF(b.image_id is not null, CONCAT('https://api.opentalent.fr/app.php/_internal/secure/files/', b.image_id, '/raw'), null) AS imageUrl,
        IF(b.image_id is not null, CONCAT('https://api.opentalent.fr/app.php/_internal/secure/files/', b.image_id, '/0x120'), null) AS thumbnailUrl,
        (SELECT GROUP_CONCAT(DISTINCT CONCAT(f.code))
         FROM event_categories AS ec
            LEFT JOIN Categories AS cs ON(cs.id = ec.categories_id)
            LEFT JOIN Familly AS f ON(f.id = cs.familly_id)
         WHERE ec.event_id = b.id
        ) AS categories, 
        'opentalent' as origin, 
        b.id as entityId
    FROM Booking AS b
        INNER JOIN Organization o ON o.id = b.organization_id
        INNER JOIN Parameters par ON par.id = o.parameters_id
        LEFT JOIN Place AS p ON (p.id = b.place_id)
        LEFT JOIN AddressPostal AS ap ON (ap.id = p.addressPostal_id)
        LEFT JOIN Room AS r ON (r.id = b.room_id)
    WHERE b.discr = 'event' 
        AND b.datetimeEnd >= NOW() 
        AND b.visibility = 'PUBLIC_VISIBILITY' 
        AND b.isCanceled = 0
UNION
    SELECT
        aw.uuid, 
        null AS organizationId, 
        aw.name, 
        aw.description, 
        aw.deepLink AS url, 
        aw.datetimeStart, 
        aw.datetimeEnd, 
        aw.priceMini, 
        aw.priceMaxi, 
        aw.subCategory AS categoryCode,
        aw.place AS placeName, 
        NULL AS placeDescription, 
        NULL AS placeFloorSize, 
        NULL AS placeCapacity,
        aw.city, 
        aw.postalCode, 
        aw.streetAddress, 
        aw.longitude, 
        aw.latitude,
        NULL AS roomName, 
        NULL AS roomDescription, 
        NULL AS roomLocalisation, 
        NULL AS roomCapacity, 
        NULL AS roomFloorSize,
        aw.largeimage AS imageUrl, 
        aw.mediumimage as thumbnailUrl, 
        aw.categoryCode AS categories, 
        'awin' as origin, 
        aw.id as entityId
    FROM AwinProduct as aw
    WHERE
        aw.datetimeEnd >= NOW() 
        AND aw.datetimeStart IS NOT NULL;