Skip to content

Open-source event management and ticket selling platform — perfect for concerts, conferences, and everything in between 🎟️ If you find this project helpful, please consider giving us a star ⭐️

3.0 KB
Raw Blame
<?php

namespace HiEvents\Services\Domain\Report\OrganizerReports;

use HiEvents\DomainObjects\Status\AttendeeStatus;
use HiEvents\Services\Domain\Report\AbstractOrganizerReportService;
use Illuminate\Support\Carbon;

class CheckInSummaryReport extends AbstractOrganizerReportService
{
    protected function getSqlQuery(Carbon $startDate, Carbon $endDate, ?string $currency = null): string
    {
        $activeStatus = AttendeeStatus::ACTIVE->name;

        return <<<SQL
            WITH organizer_events AS (
                SELECT id
                FROM events
                WHERE organizer_id = :organizer_id
                    AND deleted_at IS NULL
            ),
            event_dates AS (
                SELECT
                    eo.event_id,
                    MIN(eo.start_date) AS start_date
                FROM event_occurrences eo
                WHERE eo.event_id IN (SELECT id FROM organizer_events)
                    AND eo.deleted_at IS NULL
                GROUP BY eo.event_id
            )
            SELECT
                e.id AS event_id,
                e.title AS event_name,
                ed.start_date,
                COALESCE(attendee_counts.total_attendees, 0) AS total_attendees,
                COALESCE(checkin_counts.total_checked_in, 0) AS total_checked_in,
                CASE
                    WHEN COALESCE(attendee_counts.total_attendees, 0) = 0 THEN 0
                    ELSE ROUND((COALESCE(checkin_counts.total_checked_in, 0)::numeric / attendee_counts.total_attendees) * 100, 1)
                END AS check_in_rate,
                COALESCE(list_counts.check_in_lists_count, 0) AS check_in_lists_count
            FROM events e
            LEFT JOIN event_dates ed ON e.id = ed.event_id
            LEFT JOIN (
                SELECT
                    event_id,
                    COUNT(*) AS total_attendees
                FROM attendees
                WHERE event_id IN (SELECT id FROM organizer_events)
                    AND status = '$activeStatus'
                    AND deleted_at IS NULL
                GROUP BY event_id
            ) attendee_counts ON e.id = attendee_counts.event_id
            LEFT JOIN (
                SELECT
                    event_id,
                    COUNT(DISTINCT attendee_id) AS total_checked_in
                FROM attendee_check_ins
                WHERE event_id IN (SELECT id FROM organizer_events)
                    AND deleted_at IS NULL
                GROUP BY event_id
            ) checkin_counts ON e.id = checkin_counts.event_id
            LEFT JOIN (
                SELECT
                    event_id,
                    COUNT(*) AS check_in_lists_count
                FROM check_in_lists
                WHERE event_id IN (SELECT id FROM organizer_events)
                    AND deleted_at IS NULL
                GROUP BY event_id
            ) list_counts ON e.id = list_counts.event_id
            WHERE e.organizer_id = :organizer_id
                AND e.deleted_at IS NULL
            ORDER BY ed.start_date DESC NULLS LAST
SQL;
    }
}