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 ⭐️
PHP attendize-alternativedoor-managementevent-check-inevent-managementevent-registrationevent-ticketingeventbrite-alternativeeventslaravelopen-sourcephpreactself-hostedsell-ticketssell-tickets-onlineticket-sellingticketing-systemtickettailor-alternativetito-alternative
<?php
namespace HiEvents\Repository\Eloquent;
use HiEvents\DomainObjects\CheckInListDomainObject;
use HiEvents\DomainObjects\Generated\CapacityAssignmentDomainObjectAbstract;
use HiEvents\DomainObjects\Generated\CheckInListDomainObjectAbstract;
use HiEvents\Http\DTO\QueryParamsDTO;
use HiEvents\Models\CheckInList;
use HiEvents\Repository\DTO\CheckedInAttendeesCountDTO;
use HiEvents\Repository\DTO\CheckInListProductStatDTO;
use HiEvents\Repository\DTO\CheckInListRecentCheckInDTO;
use HiEvents\Repository\Interfaces\CheckInListRepositoryInterface;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Pagination\LengthAwarePaginator;
use Illuminate\Support\Collection;
/**
* @extends BaseRepository<CheckInListDomainObject>
*/
class CheckInListRepository extends BaseRepository implements CheckInListRepositoryInterface
{
protected function getModel(): string
{
return CheckInList::class;
}
public function getDomainObject(): string
{
return CheckInListDomainObject::class;
}
public function getCheckedInAttendeeCountById(
int $checkInListId,
?int $eventOccurrenceIdOverride = null,
): CheckedInAttendeesCountDTO {
$clause = $this->buildOccurrenceFilterClauses($eventOccurrenceIdOverride);
$sql = <<<SQL
WITH valid_check_ins AS (
SELECT attendee_id, check_in_list_id
FROM attendee_check_ins aci
JOIN check_in_lists cil ON aci.check_in_list_id = cil.id
WHERE aci.deleted_at IS NULL
AND aci.check_in_list_id = :check_in_list_id
{$clause->checkInClause}
GROUP BY attendee_id, check_in_list_id
),
valid_attendees AS (
SELECT a.id, cil.id AS check_in_list_id
FROM attendees a
JOIN orders o ON a.order_id = o.id
JOIN check_in_lists cil ON cil.event_id = a.event_id
AND cil.id = :check_in_list_id
AND cil.deleted_at IS NULL
JOIN event_settings es ON cil.event_id = es.event_id
WHERE a.deleted_at IS NULL
{$clause->attendeeClause}
AND (
EXISTS (
SELECT 1 FROM product_check_in_lists pcil
WHERE pcil.check_in_list_id = cil.id
AND pcil.product_id = a.product_id
AND pcil.deleted_at IS NULL
)
OR NOT EXISTS (
SELECT 1 FROM product_check_in_lists pcil
WHERE pcil.check_in_list_id = cil.id
AND pcil.deleted_at IS NULL
)
)
AND (
(es.allow_orders_awaiting_offline_payment_to_check_in = true AND a.status in ('ACTIVE', 'AWAITING_PAYMENT') AND o.status IN ('COMPLETED', 'AWAITING_OFFLINE_PAYMENT'))
OR
(es.allow_orders_awaiting_offline_payment_to_check_in = false AND a.status = 'ACTIVE' AND o.status = 'COMPLETED')
)
)
SELECT
cil.id AS check_in_list_id,
COUNT(va.id) AS total_attendees,
COUNT(DISTINCT vci.attendee_id) AS checked_in_attendees
FROM check_in_lists cil
LEFT JOIN valid_attendees va ON va.check_in_list_id = cil.id
LEFT JOIN valid_check_ins vci ON vci.attendee_id = va.id AND vci.check_in_list_id = va.check_in_list_id
WHERE cil.id = :check_in_list_id
AND cil.deleted_at IS NULL
GROUP BY cil.id;
SQL;
$query = $this->db->selectOne(
$sql,
array_merge(['check_in_list_id' => $checkInListId], $clause->bindings),
);
return new CheckedInAttendeesCountDTO(
checkInListId: $checkInListId,
checkedInCount: $query->checked_in_attendees ?? 0,
totalAttendeesCount: $query->total_attendees ?? 0,
);
}
private function buildOccurrenceFilterClauses(?int $override): object
{
if ($override !== null) {
return (object) [
'attendeeClause' => 'AND a.event_occurrence_id = :occurrence_id',
'checkInClause' => 'AND aci.event_occurrence_id = :occurrence_id',
'bindings' => ['occurrence_id' => $override],
];
}
return (object) [
'attendeeClause' => 'AND (cil.event_occurrence_id IS NULL OR a.event_occurrence_id = cil.event_occurrence_id)',
'checkInClause' => 'AND (cil.event_occurrence_id IS NULL OR aci.event_occurrence_id = cil.event_occurrence_id)',
'bindings' => [],
];
}
public function getCheckedInAttendeeCountByIds(array $checkInListIds): Collection
{
$placeholders = implode(',', array_fill(0, count($checkInListIds), '?'));
$sql = <<<SQL
WITH valid_check_ins AS (
SELECT aci.attendee_id, aci.check_in_list_id
FROM attendee_check_ins aci
JOIN check_in_lists cil ON aci.check_in_list_id = cil.id
WHERE aci.deleted_at IS NULL
AND aci.check_in_list_id IN ($placeholders)
AND (cil.event_occurrence_id IS NULL OR aci.event_occurrence_id = cil.event_occurrence_id)
GROUP BY aci.attendee_id, aci.check_in_list_id
),
valid_attendees AS (
SELECT a.id, cil.id AS check_in_list_id
FROM attendees a
JOIN orders o ON a.order_id = o.id
JOIN check_in_lists cil ON cil.event_id = a.event_id
AND cil.id IN ($placeholders)
AND cil.deleted_at IS NULL
JOIN event_settings es ON cil.event_id = es.event_id
WHERE a.deleted_at IS NULL
AND (cil.event_occurrence_id IS NULL OR a.event_occurrence_id = cil.event_occurrence_id)
AND (
EXISTS (
SELECT 1 FROM product_check_in_lists pcil
WHERE pcil.check_in_list_id = cil.id
AND pcil.product_id = a.product_id
AND pcil.deleted_at IS NULL
)
OR NOT EXISTS (
SELECT 1 FROM product_check_in_lists pcil
WHERE pcil.check_in_list_id = cil.id
AND pcil.deleted_at IS NULL
)
)
AND (
(es.allow_orders_awaiting_offline_payment_to_check_in = true AND a.status IN ('ACTIVE', 'AWAITING_PAYMENT') AND o.status IN ('COMPLETED', 'AWAITING_OFFLINE_PAYMENT'))
OR
(es.allow_orders_awaiting_offline_payment_to_check_in = false AND a.status = 'ACTIVE' AND o.status = 'COMPLETED')
)
)
SELECT
cil.id AS check_in_list_id,
COUNT(va.id) AS total_attendees,
COUNT(DISTINCT vci.attendee_id) AS checked_in_attendees
FROM check_in_lists cil
LEFT JOIN valid_attendees va ON va.check_in_list_id = cil.id
LEFT JOIN valid_check_ins vci ON vci.attendee_id = va.id AND vci.check_in_list_id = va.check_in_list_id
WHERE cil.id IN ($placeholders)
AND cil.deleted_at IS NULL
GROUP BY cil.id;
SQL;
$query = $this->db->select($sql, array_merge($checkInListIds, $checkInListIds, $checkInListIds));
return collect($query)->map(
static fn ($item) => new CheckedInAttendeesCountDTO(
checkInListId: $item->check_in_list_id,
checkedInCount: $item->checked_in_attendees,
totalAttendeesCount: $item->total_attendees,
)
);
}
public function getPerProductCheckInStatsById(
int $checkInListId,
?int $eventOccurrenceIdOverride = null,
): Collection {
$clause = $this->buildOccurrenceFilterClauses($eventOccurrenceIdOverride);
$sql = <<<SQL
WITH valid_check_ins AS (
SELECT aci.attendee_id, aci.check_in_list_id
FROM attendee_check_ins aci
JOIN check_in_lists cil ON aci.check_in_list_id = cil.id
WHERE aci.deleted_at IS NULL
AND aci.check_in_list_id = :check_in_list_id
{$clause->checkInClause}
GROUP BY aci.attendee_id, aci.check_in_list_id
),
valid_attendees AS (
SELECT a.id, a.product_id, cil.id AS check_in_list_id
FROM attendees a
JOIN orders o ON a.order_id = o.id
JOIN check_in_lists cil ON cil.event_id = a.event_id
AND cil.id = :check_in_list_id
AND cil.deleted_at IS NULL
JOIN event_settings es ON cil.event_id = es.event_id
WHERE a.deleted_at IS NULL
{$clause->attendeeClause}
AND (
EXISTS (
SELECT 1 FROM product_check_in_lists pcil
WHERE pcil.check_in_list_id = cil.id
AND pcil.product_id = a.product_id
AND pcil.deleted_at IS NULL
)
OR NOT EXISTS (
SELECT 1 FROM product_check_in_lists pcil
WHERE pcil.check_in_list_id = cil.id
AND pcil.deleted_at IS NULL
)
)
AND (
(es.allow_orders_awaiting_offline_payment_to_check_in = true AND a.status IN ('ACTIVE', 'AWAITING_PAYMENT') AND o.status IN ('COMPLETED', 'AWAITING_OFFLINE_PAYMENT'))
OR
(es.allow_orders_awaiting_offline_payment_to_check_in = false AND a.status = 'ACTIVE' AND o.status = 'COMPLETED')
)
)
SELECT
p.id AS product_id,
p.title AS product_title,
COUNT(va.id) AS total_attendees,
COUNT(DISTINCT vci.attendee_id) AS checked_in_attendees
FROM products p
JOIN check_in_lists cil ON cil.id = :check_in_list_id
LEFT JOIN valid_attendees va ON va.product_id = p.id
LEFT JOIN valid_check_ins vci ON vci.attendee_id = va.id
WHERE p.deleted_at IS NULL
AND (
EXISTS (
SELECT 1 FROM product_check_in_lists pcil
WHERE pcil.check_in_list_id = cil.id
AND pcil.product_id = p.id
AND pcil.deleted_at IS NULL
)
OR (
p.event_id = cil.event_id
AND NOT EXISTS (
SELECT 1 FROM product_check_in_lists pcil
WHERE pcil.check_in_list_id = cil.id
AND pcil.deleted_at IS NULL
)
)
)
GROUP BY p.id, p.title
ORDER BY p.title;
SQL;
$rows = $this->db->select(
$sql,
array_merge(['check_in_list_id' => $checkInListId], $clause->bindings),
);
return collect($rows)->map(
static fn ($row) => new CheckInListProductStatDTO(
productId: (int) $row->product_id,
productTitle: $row->product_title,
totalAttendees: (int) $row->total_attendees,
checkedInAttendees: (int) $row->checked_in_attendees,
)
);
}
public function getRecentCheckInsById(
int $checkInListId,
int $limit,
?int $eventOccurrenceIdOverride = null,
): Collection {
$clause = $this->buildOccurrenceFilterClauses($eventOccurrenceIdOverride);
$sql = <<<SQL
SELECT
a.public_id AS attendee_public_id,
a.first_name,
a.last_name,
p.title AS product_title,
aci.created_at AS checked_in_at
FROM attendee_check_ins aci
JOIN check_in_lists cil ON aci.check_in_list_id = cil.id
JOIN attendees a ON a.id = aci.attendee_id
LEFT JOIN products p ON p.id = a.product_id
WHERE aci.check_in_list_id = :check_in_list_id
AND aci.deleted_at IS NULL
AND a.deleted_at IS NULL
{$clause->checkInClause}
ORDER BY aci.created_at DESC
LIMIT :row_limit;
SQL;
$rows = $this->db->select($sql, array_merge([
'check_in_list_id' => $checkInListId,
'row_limit' => $limit,
], $clause->bindings));
return collect($rows)->map(
static fn ($row) => new CheckInListRecentCheckInDTO(
attendeePublicId: $row->attendee_public_id,
firstName: $row->first_name ?? '',
lastName: $row->last_name ?? '',
productTitle: $row->product_title,
checkedInAt: (string) $row->checked_in_at,
)
);
}
public function findByEventId(int $eventId, QueryParamsDTO $params): LengthAwarePaginator
{
$where = [
[CheckInListDomainObjectAbstract::EVENT_ID, '=', $eventId],
];
if (! empty($params->query)) {
$where[] = static function (Builder $builder) use ($params) {
$builder
->where(CapacityAssignmentDomainObjectAbstract::NAME, 'ilike', '%'.$params->query.'%');
};
}
$this->model = $this->model->orderBy(
$this->validateSortColumn($params->sort_by, CheckInListDomainObject::class),
$this->validateSortDirection($params->sort_direction, CheckInListDomainObject::class),
);
return $this->paginateWhere(
where: $where,
limit: $params->per_page,
page: $params->page,
);
}
}