private Guid SelectAidKit(EventDbDto dto, char type)
{
var query = @"
SELECT ID
FROM Item
WHERE Aid_kit = :type
AND (ID NOT IN (
SELECT Item_ID
FROM Reservation
) OR ID IN (
SELECT Item_ID
FROM Reservation
WHERE Event_ID IN (
SELECT ID
FROM Event
WHERE (Date_until <= :date_from
OR Date_from >= :date_until)
)))
AND ROWNUM = 1";
Guid id = Guid.Empty;
using OracleCommand cmd = new OracleCommand(query);
try {
cmd.Connection = Database.Instance.Connection;
cmd.Parameters.Add("type", type);
cmd.Parameters.Add("date_from", OracleDbType.Date).Value = dto.DateFrom;
cmd.Parameters.Add("date_until", OracleDbType.Date).Value = dto.DateUntil;
cmd.Connection.Open();
using OracleDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
id = Guid.Parse(dr[0] as string ?? throw new EntityNotFoundException());
}
else
{
throw new EntityNotFoundException($"{(type == 'S' ? "Small" : "Big")} aid kit not available for event");
}
}
catch (Exception e) {
Console.WriteLine(e.StackTrace);
throw;
}
finally {
cmd.Connection.Close();
}
return(id);
}
public IEnumerable <Guid> SelectRegistered(EventDbDto e)
{
var query = @"
SELECT Team_ID
FROM Child
WHERE ID IN (
SELECT Child_ID
FROM Registration
WHERE Event_ID = :event_id
)
GROUP BY Team_ID
";
using OracleCommand cmd = new OracleCommand(query);
List <Guid> reged = new List <Guid>();
try {
cmd.Connection = Database.Instance.Connection;
cmd.Parameters.Add("event_id", e.Id);
cmd.Connection.Open();
using OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Guid id = Guid.Parse(dr[0] as string ?? throw new EntityNotFoundException());
reged.Add(id);
}
}
catch (Exception ex) {
Console.WriteLine(ex.StackTrace);
throw;
}
finally {
cmd.Connection.Close();
}
return(reged);
}
public Guid SelectBigAidKit(EventDbDto dto)
{
return(SelectAidKit(dto, 'B'));
}
public Guid SelectSmallAidKit(EventDbDto dto)
{
return(SelectAidKit(dto, 'S'));
}