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
declare(strict_types=1);
namespace HiEvents\Repository\Eloquent;
use Carbon\CarbonImmutable;
use HiEvents\DomainObjects\AccountDomainObject;
use HiEvents\DomainObjects\AttendeeDomainObject;
use HiEvents\DomainObjects\EventDomainObject;
use HiEvents\DomainObjects\Generated\OrderDomainObjectAbstract;
use HiEvents\DomainObjects\OrderDomainObject;
use HiEvents\DomainObjects\OrderItemDomainObject;
use HiEvents\DomainObjects\Status\OrderPaymentStatus;
use HiEvents\DomainObjects\Status\OrderStatus;
use HiEvents\Enterprise\BoxOffice\Repository\DTO\BoxOfficeSalesCountDTO;
use HiEvents\Enterprise\BoxOffice\Repository\DTO\BoxOfficeSummaryRowDTO;
use HiEvents\Http\DTO\QueryParamsDTO;
use HiEvents\Models\Order;
use HiEvents\Models\OrderItem;
use HiEvents\Repository\DTO\SalesByCurrencyDTO;
use HiEvents\Repository\Eloquent\Value\Relationship;
use HiEvents\Repository\Interfaces\OrderRepositoryInterface;
use Illuminate\Contracts\Pagination\LengthAwarePaginator;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
/**
* @extends BaseRepository<OrderDomainObject>
*/
class OrderRepository extends BaseRepository implements OrderRepositoryInterface
{
public function findByEventId(int $eventId, QueryParamsDTO $params): LengthAwarePaginator
{
$where = [
[OrderDomainObjectAbstract::EVENT_ID, '=', $eventId],
[OrderDomainObjectAbstract::STATUS, '!=', OrderStatus::RESERVED->name],
[OrderDomainObjectAbstract::STATUS, '!=', OrderStatus::ABANDONED->name],
];
if ($params->query) {
$where[] = static function (Builder $builder) use ($params) {
$builder
->where(
DB::raw(
sprintf(
"(%s||' '||%s)",
OrderDomainObjectAbstract::FIRST_NAME,
OrderDomainObjectAbstract::LAST_NAME
)
), 'ilike', '%'.$params->query.'%')
->orWhere(OrderDomainObjectAbstract::LAST_NAME, 'ilike', '%'.$params->query.'%')
->orWhere(OrderDomainObjectAbstract::PUBLIC_ID, 'ilike', '%'.$params->query.'%')
->orWhere(OrderDomainObjectAbstract::EMAIL, 'ilike', '%'.$params->query.'%');
};
}
if (! empty($params->filter_fields)) {
$this->applyFilterFields($params, OrderDomainObject::getAllowedFilterFields());
$occurrenceFilter = $params->filter_fields->firstWhere('field', 'event_occurrence_id');
if ($occurrenceFilter) {
$this->model = $this->model->whereHas('order_items', function (Builder $query) use ($occurrenceFilter) {
$query->where('order_items.event_occurrence_id', $occurrenceFilter->value);
});
}
$productFilter = $params->filter_fields->firstWhere('field', 'product_id');
if ($productFilter?->value) {
$this->applyProductFilter($productFilter->value);
}
}
$this->model = $this->model->orderBy(
column: $this->validateSortColumn($params->sort_by, OrderDomainObject::class),
direction: $this->validateSortDirection($params->sort_direction, OrderDomainObject::class),
);
return $this->paginateWhere(
where: $where,
limit: $params->per_page,
page: $params->page,
);
}
private function applyProductFilter(string $value): void
{
$productIds = array_values(array_filter(
array_map('intval', explode(',', $value)),
static fn (int $productId) => $productId > 0,
));
$this->model = $this->model->where(static function (Builder $builder) use ($productIds) {
$builder
->whereHas('order_items', static fn (Builder $query) => $query->whereIn('order_items.product_id', $productIds))
->orWhereHas('attendees', static fn (Builder $query) => $query->whereIn('attendees.product_id', $productIds));
});
}
public function findByOrganizerId(int $organizerId, int $accountId, QueryParamsDTO $params): LengthAwarePaginator
{
$where = [
['orders.status', '!=', OrderStatus::RESERVED->name],
['orders.status', '!=', OrderStatus::ABANDONED->name],
];
if ($params->query) {
$where[] = static function (Builder $builder) use ($params) {
$builder
->where(
DB::raw(
sprintf(
"(%s||' '||%s)",
OrderDomainObjectAbstract::FIRST_NAME,
OrderDomainObjectAbstract::LAST_NAME
)
), 'ilike', '%'.$params->query.'%')
->orWhere(OrderDomainObjectAbstract::LAST_NAME, 'ilike', '%'.$params->query.'%')
->orWhere(OrderDomainObjectAbstract::PUBLIC_ID, 'ilike', '%'.$params->query.'%')
->orWhere(OrderDomainObjectAbstract::EMAIL, 'ilike', '%'.$params->query.'%');
};
}
if (! empty($params->filter_fields)) {
$this->applyFilterFields($params, OrderDomainObject::getAllowedFilterFields());
}
$this->model = $this->model
->select('orders.*')
->join('events', 'orders.event_id', '=', 'events.id')
->where('events.organizer_id', $organizerId)
->where('events.account_id', $accountId)
->whereNull('events.deleted_at');
$sortBy = $this->validateSortColumn($params->sort_by, OrderDomainObject::class);
$this->model = $this->model->orderBy(
column: 'orders.'.$sortBy,
direction: $this->validateSortDirection($params->sort_direction, OrderDomainObject::class),
);
return $this->paginateWhere(
where: $where,
limit: $params->per_page,
page: $params->page,
);
}
public function getOrderItems(int $orderId)
{
return $this->handleResults(
$this->model->find($orderId)->orderItems,
OrderItemDomainObject::class
);
}
public function getAttendees(int $orderId)
{
return $this->handleResults(
$this->model->find($orderId)->attendees,
AttendeeDomainObject::class
);
}
public function addOrderItem(array $data): OrderItemDomainObject
{
$orderItem = $this->initModel(OrderItem::class)->create($data);
return $this->handleSingleResult($orderItem, OrderItemDomainObject::class);
}
public function findByShortId(string $orderShortId): ?OrderDomainObject
{
return $this->findFirstByField('short_id', $orderShortId);
}
public function getDomainObject(): string
{
return OrderDomainObject::class;
}
protected function getModel(): string
{
return Order::class;
}
public function findOrdersAssociatedWithProducts(
int $eventId,
array $productIds,
array $orderStatuses,
?int $eventOccurrenceId = null,
?array $eventOccurrenceIds = null,
): Collection {
$query = $this->model
->whereHas('order_items', static function (Builder $query) use ($productIds, $eventOccurrenceId, $eventOccurrenceIds) {
$query->whereIn('product_id', $productIds);
if (! empty($eventOccurrenceIds)) {
$query->whereIn('order_items.event_occurrence_id', $eventOccurrenceIds);
} elseif ($eventOccurrenceId !== null) {
$query->where('order_items.event_occurrence_id', $eventOccurrenceId);
}
})
->whereIn('status', $orderStatuses)
->where('event_id', $eventId);
return $this->handleResults($query->get());
}
public function countOrdersAssociatedWithProducts(
int $eventId,
array $productIds,
array $orderStatuses,
?int $eventOccurrenceId = null,
?array $eventOccurrenceIds = null,
): int {
$count = $this->model
->whereHas('order_items', static function (Builder $query) use ($productIds, $eventOccurrenceId, $eventOccurrenceIds) {
$query->whereIn('product_id', $productIds);
if (! empty($eventOccurrenceIds)) {
$query->whereIn('order_items.event_occurrence_id', $eventOccurrenceIds);
} elseif ($eventOccurrenceId !== null) {
$query->where('order_items.event_occurrence_id', $eventOccurrenceId);
}
})
->whereIn('status', $orderStatuses)
->where('event_id', $eventId)
->count();
$this->resetModel();
return $count;
}
public function countActivePromoCodeUsage(int $promoCodeId): int
{
$count = $this->model
->where('promo_code_id', $promoCodeId)
->where(static function (Builder $query) {
$query->whereIn('status', [
OrderStatus::COMPLETED->name,
OrderStatus::AWAITING_OFFLINE_PAYMENT->name,
])->orWhere(static function (Builder $reserved) {
$reserved->where('status', OrderStatus::RESERVED->name)
->where('reserved_until', '>', now());
});
})
->count();
$this->resetModel();
return $count;
}
public function getAllOrdersForAdmin(
?string $search = null,
int $perPage = 20,
?string $sortBy = 'created_at',
?string $sortDirection = 'desc'
): LengthAwarePaginator {
$this->model = $this->model
->select('orders.*')
->join('events', 'orders.event_id', '=', 'events.id')
->join('accounts', 'events.account_id', '=', 'accounts.id');
if ($search) {
$this->model = $this->model->where(function ($q) use ($search) {
$q->where(OrderDomainObjectAbstract::EMAIL, 'ilike', '%'.$search.'%')
->orWhere(OrderDomainObjectAbstract::FIRST_NAME, 'ilike', '%'.$search.'%')
->orWhere(OrderDomainObjectAbstract::LAST_NAME, 'ilike', '%'.$search.'%')
->orWhere(OrderDomainObjectAbstract::PUBLIC_ID, 'ilike', '%'.$search.'%')
->orWhere(OrderDomainObjectAbstract::SHORT_ID, 'ilike', '%'.$search.'%');
});
}
$this->model = $this->model->where('orders.status', '!=', OrderStatus::RESERVED->name)
->where('orders.status', '!=', OrderStatus::ABANDONED->name);
$allowedSortColumns = ['created_at', 'total_gross', 'email', 'first_name', 'last_name'];
$sortColumn = in_array($sortBy, $allowedSortColumns, true) ? $sortBy : 'created_at';
$sortDir = in_array(strtolower($sortDirection), ['asc', 'desc']) ? $sortDirection : 'desc';
$this->model = $this->model->orderBy('orders.'.$sortColumn, $sortDir);
$this->loadRelation(new Relationship(EventDomainObject::class, nested: [
new Relationship(AccountDomainObject::class, name: 'account'),
], name: 'event'));
return $this->paginate($perPage);
}
public function hasCompletedPaidOrderForAccount(int $accountId): bool
{
$exists = $this->model
->join('events', 'orders.event_id', '=', 'events.id')
->join('stripe_payments', 'orders.id', '=', 'stripe_payments.order_id')
->where('events.account_id', $accountId)
->where('orders.payment_status', OrderPaymentStatus::PAYMENT_RECEIVED->name)
->whereNotNull('stripe_payments.payment_intent_id')
->exists();
$this->resetModel();
return $exists;
}
public function accountHasCompletedOrders(int $accountId): bool
{
return $this->runQuery(fn () => $this->model
->join('events', 'orders.event_id', '=', 'events.id')
->where('events.account_id', $accountId)
->where('orders.status', OrderStatus::COMPLETED->name)
->exists());
}
public function findByBoxOfficeId(int $boxOfficeId, QueryParamsDTO $params): LengthAwarePaginator
{
$where = [
[OrderDomainObjectAbstract::BOX_OFFICE_ID, '=', $boxOfficeId],
static function (Builder $builder) {
$builder
->whereIn(OrderDomainObjectAbstract::STATUS, [OrderStatus::COMPLETED->name, OrderStatus::CANCELLED->name])
->orWhere(static function (Builder $reserved) {
$reserved
->where(OrderDomainObjectAbstract::STATUS, OrderStatus::RESERVED->name)
->where(OrderDomainObjectAbstract::RESERVED_UNTIL, '>', now());
});
},
];
if ($params->query) {
$where[] = static function (Builder $builder) use ($params) {
$builder
->where(
DB::raw(
sprintf(
"(%s||' '||%s)",
OrderDomainObjectAbstract::FIRST_NAME,
OrderDomainObjectAbstract::LAST_NAME
)
), 'ilike', '%'.$params->query.'%')
->orWhere(OrderDomainObjectAbstract::LAST_NAME, 'ilike', '%'.$params->query.'%')
->orWhere(OrderDomainObjectAbstract::PUBLIC_ID, 'ilike', '%'.$params->query.'%')
->orWhere(OrderDomainObjectAbstract::EMAIL, 'ilike', '%'.$params->query.'%');
};
}
$this->model = $this->model->orderBy(OrderDomainObjectAbstract::CREATED_AT, 'desc');
return $this->paginateWhere(
where: $where,
limit: $params->per_page,
page: $params->page,
);
}
public function getBoxOfficeSalesCountsByIds(array $boxOfficeIds): Collection
{
if ($boxOfficeIds === []) {
return collect();
}
$placeholders = implode(',', array_fill(0, count($boxOfficeIds), '?'));
$rows = $this->db->select(
<<<SQL
SELECT box_office_id, COUNT(*) AS sales_count, COALESCE(SUM(total_gross), 0) AS gross_sales
FROM orders
WHERE box_office_id IN ($placeholders)
AND status = ?
AND deleted_at IS NULL
GROUP BY box_office_id
SQL,
array_merge($boxOfficeIds, [OrderStatus::COMPLETED->name]),
);
return collect($rows)->map(static fn ($row) => new BoxOfficeSalesCountDTO(
boxOfficeId: (int) $row->box_office_id,
salesCount: (int) $row->sales_count,
grossSales: (float) $row->gross_sales,
));
}
public function getNetSalesByCurrency(CarbonImmutable $from, CarbonImmutable $to): Collection
{
$rows = $this->db->select(
<<<'SQL'
SELECT currency, ROUND(SUM(net_sales), 2) AS net_sales
FROM (
SELECT
currency,
total_before_additions * (total_gross - LEAST(COALESCE(total_refunded, 0), total_gross)) / total_gross AS net_sales
FROM orders
WHERE payment_status = :payment_status
AND deleted_at IS NULL
AND total_gross > 0
AND created_at >= :from
AND created_at < :to
) paid_orders
GROUP BY currency
HAVING SUM(net_sales) > 0
ORDER BY currency
SQL,
[
'payment_status' => OrderPaymentStatus::PAYMENT_RECEIVED->name,
'from' => $from->utc()->toDateTimeString(),
'to' => $to->utc()->toDateTimeString(),
],
);
return collect($rows)->map(static fn ($row) => new SalesByCurrencyDTO(
currency: (string) $row->currency,
amount: (float) $row->net_sales,
));
}
public function getBoxOfficeSummary(int $boxOfficeId, string $timezone, ?string $from, ?string $to): Collection
{
$bindings = [
'timezone' => $timezone,
'box_office_id' => $boxOfficeId,
'status' => OrderStatus::COMPLETED->name,
];
$rangeClause = '';
if ($from !== null) {
$rangeClause .= ' AND created_at >= :from';
$bindings['from'] = $from;
}
if ($to !== null) {
$rangeClause .= ' AND created_at <= :to';
$bindings['to'] = $to;
}
$rows = $this->db->select(
<<<SQL
SELECT
box_office_tender AS tender,
COALESCE(box_office_operator_name, '') AS operator_name,
(created_at AT TIME ZONE 'UTC' AT TIME ZONE :timezone)::date AS day,
COUNT(*) AS orders,
COALESCE(SUM(total_gross), 0) AS gross,
COALESCE(SUM(total_refunded), 0) AS refunded
FROM orders
WHERE box_office_id = :box_office_id
AND status = :status
AND deleted_at IS NULL
$rangeClause
GROUP BY box_office_tender, box_office_operator_name, day
ORDER BY day
SQL,
$bindings,
);
return collect($rows)->map(static fn ($row) => new BoxOfficeSummaryRowDTO(
tender: (string) $row->tender,
operatorName: (string) $row->operator_name,
day: (string) $row->day,
orders: (int) $row->orders,
gross: (float) $row->gross,
refunded: (float) $row->refunded,
));
}
}