public async Task <IReadOnlyCollection <Client.Models.Customer> > GetLatestCustomersAsync()
{
using (var connection = _sqlConnection.GetConnection())
{
var result = (await connection.QueryAsync <Customer>("dbo.Customer_GetLatest",
commandType: CommandType.StoredProcedure, commandTimeout: 0)
.ConfigureAwait(false)).ToList().AsReadOnly();
//Could bring auto mapper in here
List <Client.Models.Customer> customerList = new List <Client.Models.Customer>();
foreach (var customer in result)
{
customerList.Add(new Client.Models.Customer
{
Id = customer.Id,
Name = customer.Name,
PreviouslyOrdered = customer.PreviouslyOrdered,
WebCustomer = customer.WebCustomer,
LastActive = customer.LastActive,
FavouriteColours = new List <string>(customer.FavouriteColours.Split(',').ToList())
});
}
return(customerList.AsReadOnly());
}
}
public IEnumerable <Product> GetProducts()
{
using (var conn = _sqlConnectionFactory.GetConnection())
{
return(conn.GetList <Product>());
}
}
public void Create()
{
using (var connection = _connectionFactory.GetConnection(initialCatalog: "master"))
using (var command = connection.CreateCommand())
{
var builder = new SqlConnectionStringBuilder {
ConnectionString = _connectionFactory.GetConnection().ConnectionString
};
command.CommandType = CommandType.Text;
command.CommandText = SqlScripts.Database_Create.Replace("CatalogName", builder.InitialCatalog);
connection.Open();
command.ExecuteNonQuery();
}
}
public async Task <Unit> Handle(ProcessOutboxCommand request, CancellationToken cancellationToken)
{
var connection = sqlConnectionFactory.GetConnection();
var entities = (await connection.QueryAsync <OutboxNotificationProcessingDto>(
@"SELECT id, type, data
FROM app.outbox_notifications
WHERE processed_date IS NULL;"
)).AsList();
if (entities.Any())
{
var updateSql =
@"UPDATE app.outbox_notifications
SET processed_date = @Date
WHERE id = @Id;";
foreach (var entity in entities)
{
var type = Assemblies.Application.GetType(entity.Type);
var notification = JsonConvert.DeserializeObject(entity.Data, type) as IDomainEventNotification;
await mediator.Publish(notification, cancellationToken);
await connection.ExecuteAsync(updateSql, new
{
Date = DateTime.Now,
Id = entity.Id
});
}
}
return(Unit.Value);
}
public async Task <ScheduleDto> Handle(GetScheduleByIdQuery request, CancellationToken cancellationToken)
{
var connection = sqlConnectionFactory.GetConnection();
var schedule = await connection.QuerySingleOrDefaultAsync <ScheduleDto>(
@"SELECT
name,
start_date as StartDate,
end_date as EndDate,
creation_date AS CreationDate
FROM accessibility.schedules
WHERE schedule_id = @ScheduleId;",
new {
request.ScheduleId
});
var availabilities = (await connection.QueryAsync <AvailabilityDto>(
@"SELECT
employee_id AS EmployeeId,
start_time AS StartTime,
end_time AS EndTime,
priority,
creator_id AS CreatorId,
creation_date AS CreationDate
FROM accessibility.availabilities
WHERE schedule_id = @ScheduleId;",
new {
request.ScheduleId
})).AsList();
schedule.Availabilities = availabilities;
return(schedule);
}
public async Task <List <BookedRecordOfFacilityDto> > Handle(GetBookedRecordsOfFacilityQuery request, CancellationToken cancellationToken)
{
var connection = sqlConnectionFactory.GetConnection();
return((await connection.QueryAsync <BookedRecordOfFacilityDto>(
@"SELECT
b.booking_id as BookingId,
r.offer_id as OfferId,
o.name as OfferName,
r.employee_id as EmployeeId,
null as EmployeeName,
b.customer_id as CustomerId,
r.date as DateFrom,
r.date + r.duration * INTERVAL '1 minute' as DateTo,
r.duration as Duration,
r.status as Status,
r.price as Price,
r.currency as Currency
FROM
booking.bookings b
INNER JOIN
booking.booked_records r ON b.booking_id = r.booking_id
INNER JOIN
facility.offers o ON r.offer_id = o.offer_id
WHERE
b.facility_id = @FacilityId AND
r.date BETWEEN @DateFrom::timestamp AND @DateTo::timestamp",
new
{
request.FacilityId,
request.DateFrom,
request.DateTo
}))
.AsList());
}
public bool IsAvailable(FacilityId facilityId, DateTime startDate, DateTime endDate)
{
var connection = sqlConnectionFactory.GetConnection();
var result = connection.QuerySingleOrDefault <int?>(
@"SELECT 1
FROM accessibility.schedules
WHERE
facility_id = @FacilityId AND
startDate < @EndDate AND
@StartDate < end_date
LIMIT 1;",
new {
FacilityId = facilityId.Value,
StartDate = startDate,
EndDate = endDate
});
return(!result.HasValue);
}
public async Task <IEnumerable <Todo> > GetAll()
{
using (SqlConnection connection = _sqlConnectionFactory.GetConnection())
{
return(await connection.GetListAsync <Todo>());
}
}
private async Task <bool> CanConnectToDb()
{
try
{
using var connection = _sqlConnectionFactory.GetConnection();
var query = "SELECT 1";
var cmd = new SqlCommand(query, connection);
await cmd.ExecuteScalarAsync();
}
catch (Exception e)
{
_logger.LogError(e, "Could not connect to database");
return(false);
}
return(true);
}
public async Task <bool> Handle(AnyUnfinishedBookingOfEmployeeQuery request, CancellationToken cancellationToken)
{
var connection = sqlConnectionFactory.GetConnection();
return((await connection.QueryFirstOrDefaultAsync <int?>(
@"SELECT 1
FROM
accessibility.booked_records r INNER JOIN accessibility.bookings b
ON r.booking_id = b.booking_id
WHERE
r.employee_id = @EmployeeId AND
b.facility_id = @FacilityId AND
r.status = 0
LIMIT 1;",
new {
request.EmployeeId,
request.FacilityId
})).HasValue);
}
public async Task <SubscriberDiscountProjection> Find(Guid id, CancellationToken cancellationToken = default)
{
var connection = _sqlConnectionFactory.GetConnection();
var sql = new[]
{
"SELECT DISTINCT",
"discount.Code as DiscountCode,",
"subscriber.Email",
"FROM",
"Discounts discount",
"JOIN",
"Subscribers subscriber",
"ON",
"discount.SubscriberId = subscriber.Id",
"WHERE",
"subscriber.Id = @SubscriberId"
};
var subscriberDiscountProjections = await connection.QueryAsync <SubscriberDiscountProjection>(string.Join(" ", sql), new { SubscriberId = id });
return(subscriberDiscountProjections.SingleOrDefault());
}
public async Task <bool> IsRecordAvailable(
BookingId bookingId,
FacilityId facilityId,
EmployeeId employeeId,
DateTime startDate,
DateTime endDate)
{
var connection = sqlConnectionFactory.GetConnection();
var result = await connection.QueryFirstOrDefaultAsync <int?>(
@"SELECT 1
FROM
booking.bookings b INNER JOIN
booking.booked_records r ON b.booking_id = r.booking_id
WHERE
b.booking_id != @BookingId AND
b.status = @Status AND
b.facility_id = @FacilityId AND
r.status = @RecordStatus AND
r.employee_id = @EmployeeId AND
r.date < @EndDate AND
@StartDate < r.date + r.duration * INTERVAL '1 minute';",
new
{
BookingId = bookingId.Value,
Status = (int)BookingStatus.Booked,
FacilityId = facilityId.Value,
RecordStatus = (int)BookedRecordStatus.Booked,
EmployeeId = employeeId.Value,
StartDate = startDate,
EndDate = endDate
}
);
return(!result.HasValue);
}
protected async Task BackgroundProcessing(CancellationToken cancellationToken)
{
bool hasItems = true;
while (!cancellationToken.IsCancellationRequested)
{
try
{
var allItems = new List <QueueItem>();
using (var connection = sqlConnectionFactory.GetConnection())
{
await sqlConnectionFactory.OpenAsync(connection, cancellationToken);
if (!hasItems)
{
hasItems = await queueItemRepository.HasItems(connection);
if (!hasItems)
{
logger.LogInformation($"No items to process, going to sleep for {config.NoQueueItemsToProcessSleepTimeMS} milliseconds");
await Task.Delay(config.NoQueueItemsToProcessSleepTimeMS, cancellationToken);
sqlConnectionFactory.Close(connection);
continue;
}
}
var workers = await queueWorkerRepository.GetQueueWorkers(connection);
foreach (var worker in workers.Where(w => w.Enabled).OrderByDescending(w => w.Priority))
{
var items = await queueItemRepository.GetQueueItems(connection, worker.Id, worker.Retries, worker.BatchSize);
items.ForEach(i => i.QueueWorker = worker);
allItems.AddRange(items);
if (allItems.Count > config.GlobalBatchSizeLimit)
{
break;
}
}
sqlConnectionFactory.Close(connection);
}
if (!allItems.Any())
{
logger.LogInformation($"No items to process, going to sleep for {config.NoQueueItemsToProcessSleepTimeMS} milliseconds");
hasItems = false;
await Task.Delay(config.NoQueueItemsToProcessSleepTimeMS, cancellationToken);
continue;
}
var allTasks = new List <Task>();
foreach (var item in allItems.OrderByDescending(i => i.QueueWorker.Priority).ThenBy(i => i.Id))
{
allTasks.Add(ProcessItem(item, cancellationToken));
}
await Task.WhenAll(allTasks);
}
catch (Exception ex)
{
logger.LogError(ex, $"Error processing queue.");
await Task.Delay(config.NoQueueItemsToProcessSleepTimeMS, cancellationToken);
}
}
logger.LogInformation("QueueProcessor Service is cancelled.");
}
public ScheduleQueryRepository(ISqlConnectionFactory sqlConnectionFactory)
{
connection = sqlConnectionFactory.GetConnection();
}
public async Task SaveAsync(Account account)
{
using var connection = connectionFactory.GetConnection();
await connection.ExecuteAsync("INSERT INTO Account (Id, CustomerId, Balance) VALUES (@Id, @CustomerId, @Balance)", new { Id = account.Id.Value, CustomerId = account.CustomerId.Value, account.Balance });
}
public OfferQueryRepository(ISqlConnectionFactory sqlConnectionFactory)
{
connection = sqlConnectionFactory.GetConnection();
}
public BookingQueryRepository(ISqlConnectionFactory sqlConnectionFactory)
{
connection = sqlConnectionFactory.GetConnection();
}