Untitled

 avatar
unknown
sql
a year ago
4.9 kB
7
Indexable
select tickets.title, tickets.ticket_date, ticket_types.name as type, attendees.time , tickets.quantity_available,
       CASE
        WHEN
            MAX((SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[0].max_capacity'), SIGNED INTEGER) AS max_capacity
            FROM tickets ts
            WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title
            AND JSON_EXTRACT(ts.ticket_session, '$[0].time') = attendees.time )) IS NOT NULL
        THEN
           (SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[0].max_capacity'), SIGNED INTEGER) AS max_capacity
            FROM tickets ts
            WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title
            AND JSON_EXTRACT(ts.ticket_session, '$[0].time') = attendees.time )
        WHEN
            MAX((SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[1].max_capacity'), SIGNED INTEGER) AS max_capacity
            FROM tickets ts
            WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title
            AND JSON_EXTRACT(ts.ticket_session, '$[1].time') = attendees.time )) IS NOT NULL
        THEN
           (SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[1].max_capacity'), SIGNED INTEGER) AS max_capacity
            FROM tickets ts
            WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title
            AND JSON_EXTRACT(ts.ticket_session, '$[1].time') = attendees.time )
        WHEN
            MAX((SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[2].max_capacity'), SIGNED INTEGER) AS max_capacity
            FROM tickets ts
            WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title
            AND JSON_EXTRACT(ts.ticket_session, '$[2].time') = attendees.time )) IS NOT NULL
        THEN
           (SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[2].max_capacity'), SIGNED INTEGER) AS max_capacity
            FROM tickets ts
            WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title
            AND JSON_EXTRACT(ts.ticket_session, '$[2].time') = attendees.time )
        ELSE null
    END AS max_capacity,
       count(attendees.id) as total_sell,
       CASE
        WHEN
            MAX((SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[0].max_capacity'), SIGNED INTEGER) AS max_capacity
            FROM tickets ts
            WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title
            AND JSON_EXTRACT(ts.ticket_session, '$[0].time') = attendees.time )) IS NOT NULL
        THEN
           (SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[0].max_capacity'), SIGNED INTEGER) AS max_capacity
            FROM tickets ts
            WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title
            AND JSON_EXTRACT(ts.ticket_session, '$[0].time') = attendees.time ) -  count(attendees.id)
        WHEN
            MAX((SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[1].max_capacity'), SIGNED INTEGER) AS max_capacity
            FROM tickets ts
            WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title
            AND JSON_EXTRACT(ts.ticket_session, '$[1].time') = attendees.time )) IS NOT NULL
        THEN
           (SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[1].max_capacity'), SIGNED INTEGER) AS max_capacity
            FROM tickets ts
            WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title
            AND JSON_EXTRACT(ts.ticket_session, '$[1].time') = attendees.time ) -  count(attendees.id)
        WHEN
            MAX((SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[2].max_capacity'), SIGNED INTEGER) AS max_capacity
            FROM tickets ts
            WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title
            AND JSON_EXTRACT(ts.ticket_session, '$[2].time') = attendees.time )) IS NOT NULL
        THEN
           (SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[2].max_capacity'), SIGNED INTEGER) AS max_capacity
            FROM tickets ts
            WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title
            AND JSON_EXTRACT(ts.ticket_session, '$[2].time') = attendees.time ) -  count(attendees.id)
        ELSE null
    END AS tot_available,
    (count(attendees.id) * tickets.price) as gross_revenue, sum(orders.discount_vendor) as discount_vendor,
             (count(attendees.id) * tickets.price - sum(orders.discount_vendor)) as net
    from tickets
    join ticket_types
    join attendees
    join orders
    on tickets.type = ticket_types.id and attendees.ticket_id = tickets.id and orders.id = attendees.order_id
    where attendees.is_cancelled = 0 and attendees.is_expired = 0
    and tickets.event_id = 3 and tickets.ticket_session IS NOT NULL
    and attendees.created_at < '2024-03-20 23:59:59'
    group by tickets.title, tickets.ticket_date, ticket_types.name, attendees.time, tickets.quantity_available, tickets.price;
Leave a Comment