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,
        o.name AS structureName,
        b.name,
        b.description,
        b.url,
        IF(b.helloAssoSlug is not null, 1, 0) AS hasHelloAssoForm,
        b.datetimeStart,
        b.datetimeEnd,
        b.gender_id as gender,
        COALESCE(b.priceMini, 0) AS priceMini,
        COALESCE(b.priceMaxi, 0) 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,
        a2.longitude as structureLongitude,
        a2.latitude as structureLatitude,
        IF(b.image_id is not null, CONCAT('https://api.opentalent.fr/api/public/files/', b.image_id, '/download/lg'), null) AS imageUrl,
        IF(b.image_id is not null, CONCAT('https://api.opentalent.fr/api/public/files/', b.image_id, '/download/md'), 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.image_id as imgId,
        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 OR p.id = b.placeSystem_id)
        LEFT JOIN AddressPostal AS ap ON (ap.id = p.addressPostal_id)
        LEFT JOIN Room AS r ON (r.id = b.room_id)
        LEFT JOIN opentalent.OrganizationAddressPostal oa2 on (oa2.organization_id = o.id and oa2.type='ADDRESS_HEAD_OFFICE')
        LEFT JOIN opentalent.AddressPostal a2 on (oa2.addressPostal_id = a2.id)
    WHERE b.discr = 'event'
        AND b.datetimeEnd >= NOW()
        AND b.visibility = 'PUBLIC_VISIBILITY'
        AND b.isCanceled = 0
        AND b.gender_id <> 7
    UNION
    SELECT
        aw.uuid,
        null AS organizationId,
        NULL AS structureName,
        aw.name,
        aw.description,
        aw.deepLink AS url,
        0 AS hasHelloAssoForm,
        aw.datetimeStart,
        aw.datetimeEnd,
        NULL as gender,
        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,
        NULL as structureLongitude,
        NULL as structureLatitude,
        aw.largeimage AS imageUrl,
        aw.mediumimage as thumbnailUrl,
        aw.categoryCode AS categories,
        'awin' as origin,
        NULL as imgId,
        aw.id as entityId
    FROM AwinProduct as aw
    WHERE
        aw.datetimeEnd >= NOW()
        AND aw.datetimeStart IS NOT NULL;