Ticket types allow you to offer different pricing tiers based on age, group type, or other factors. Each tour can have multiple ticket types with independent pricing, capacity limits, and sale windows.
Every tour must have at least one active ticket type to be bookable on the marketplace.
Ticket types are stored in the TourTicketType table with the following properties:
public record TourTicketTypeRequest( string Name, // "Adult", "Child", "Senior" int? MinAge, // Minimum age (optional) int? MaxAge, // Maximum age (optional) decimal? Price, // Base price string CurrencyCode, // "USD", "DOP", "EUR" int SortOrder, // Display order int? CapacidadPorTipo, // Per-type capacity limit DateTime? VentaInicioUtc, // Sale start date DateTime? VentaFinUtc, // Sale end date int? MaxPorOrden, // Max per order int? MaxPorUsuario); // Max per customer
Limit the number of tickets available for each type:
CapacidadPorTipo = 20 // Max 20 child tickets
How Capacity is Tracked
The system queries reserved tickets in real-time:
public async Task<Dictionary<int, int>> GetTicketTypeReservedCountAsync( int tourId, CancellationToken cancellationToken = default){ var result = new Dictionary<int, int>(); if (tourId <= 0) { return result; } await using var connection = CreateConnection(); await connection.OpenAsync(cancellationToken); const string sql = """ SELECT ttt.TourTicketTypeId, COUNT(trt.TourReservationTicketId) AS ReservedCount FROM TourTicketType ttt LEFT JOIN TourReservationTicket trt ON trt.TourTicketTypeId = ttt.TourTicketTypeId LEFT JOIN TourReservation r ON r.TourReservationId = trt.TourReservationId WHERE ttt.TourId = @TourId GROUP BY ttt.TourTicketTypeId """; await using var command = new SqlCommand(sql, connection); command.Parameters.Add(new SqlParameter("@TourId", SqlDbType.Int) { Value = tourId }); await using var reader = await command.ExecuteReaderAsync(cancellationToken); while (await reader.ReadAsync(cancellationToken)) { var ticketTypeId = reader.GetInt32(reader.GetOrdinal("TourTicketTypeId")); var reserved = reader.IsDBNull(reader.GetOrdinal("ReservedCount")) ? 0 : reader.GetInt32(reader.GetOrdinal("ReservedCount")); result[ticketTypeId] = reserved; } return result;}
The system replaces all ticket types each time you save:
public async Task UpsertTicketTypesAsync( int tourId, IEnumerable<TourTicketTypeRequest> tickets, CancellationToken cancellationToken = default){ if (tickets is null) return; await using var connection = CreateConnection(); await connection.OpenAsync(cancellationToken); await using var tx = await connection.BeginTransactionAsync(cancellationToken); // Delete existing ticket types const string deleteSql = "DELETE FROM TourTicketType WHERE TourId = @TourId;"; await using (var deleteCmd = new SqlCommand(deleteSql, connection, (SqlTransaction)tx)) { deleteCmd.Parameters.Add(new SqlParameter("@TourId", SqlDbType.Int) { Value = tourId }); await deleteCmd.ExecuteNonQueryAsync(cancellationToken); } // Insert new ticket types const string insertSql = @"INSERT INTO TourTicketType (TourId, Name, MinAge, MaxAge, Price, CurrencyCode, IsActive, SortOrder, CapacidadPorTipo, VentaInicioUtc, VentaFinUtc, MaxPorOrden, MaxPorUsuario)VALUES (@TourId, @Name, @MinAge, @MaxAge, @Price, @CurrencyCode, 1, @SortOrder, @CapacidadPorTipo, @VentaInicioUtc, @VentaFinUtc, @MaxPorOrden, @MaxPorUsuario);"; foreach (var ticket in tickets) { await using var command = new SqlCommand(insertSql, connection, (SqlTransaction)tx); command.Parameters.Add(new SqlParameter("@TourId", SqlDbType.Int) { Value = tourId }); command.Parameters.AddWithValue("@Name", ticket.Name ?? string.Empty); command.Parameters.AddWithValue("@MinAge", (object?)ticket.MinAge ?? DBNull.Value); command.Parameters.AddWithValue("@MaxAge", (object?)ticket.MaxAge ?? DBNull.Value); command.Parameters.AddWithValue("@Price", (object?)ticket.Price ?? DBNull.Value); command.Parameters.AddWithValue("@CurrencyCode", ticket.CurrencyCode ?? "USD"); command.Parameters.AddWithValue("@SortOrder", ticket.SortOrder); command.Parameters.AddWithValue("@CapacidadPorTipo", (object?)ticket.CapacidadPorTipo ?? DBNull.Value); command.Parameters.AddWithValue("@VentaInicioUtc", (object?)ticket.VentaInicioUtc ?? DBNull.Value); command.Parameters.AddWithValue("@VentaFinUtc", (object?)ticket.VentaFinUtc ?? DBNull.Value); command.Parameters.AddWithValue("@MaxPorOrden", (object?)ticket.MaxPorOrden ?? DBNull.Value); command.Parameters.AddWithValue("@MaxPorUsuario", (object?)ticket.MaxPorUsuario ?? DBNull.Value); await command.ExecuteNonQueryAsync(cancellationToken); } await tx.CommitAsync(cancellationToken);}
This operation deletes all existing ticket types and recreates them. Do not call this method if you have active reservations without careful consideration.
The marketplace queries ticket types for all tours:
private async Task<Dictionary<int, List<TourTicketTypeSummaryDto>>> GetTicketTypesForToursAsync( IReadOnlyCollection<int> tourIds, CancellationToken cancellationToken){ var result = new Dictionary<int, List<TourTicketTypeSummaryDto>>(); if (tourIds is null || tourIds.Count == 0) return result; await using var conn = new SqlConnection(_connectionString); await conn.OpenAsync(cancellationToken); var parameterNames = tourIds.Select((_, index) => $"@p{index}").ToArray(); var sql = $@"SELECT TourId, Name, Price, CurrencyCode, SortOrder, VentaInicioUtc, VentaFinUtc, IsActive, CapacidadPorTipoFROM TourTicketTypeWHERE IsActive = 1 AND TourId IN ({string.Join(",", parameterNames)})ORDER BY TourId, SortOrder"; await using var cmd = new SqlCommand(sql, conn); var idx = 0; foreach (var tourId in tourIds) { cmd.Parameters.AddWithValue(parameterNames[idx], tourId); idx++; } await using var reader = await cmd.ExecuteReaderAsync(cancellationToken); while (await reader.ReadAsync(cancellationToken)) { var tourId = reader.GetInt32(reader.GetOrdinal("TourId")); // ... build TourTicketTypeSummaryDto if (!result.TryGetValue(tourId, out var list)) { list = new List<TourTicketTypeSummaryDto>(); result[tourId] = list; } list.Add(ticketTypeDto); } return result;}