/// <summary>
/// Fetches multiple Venues
/// </summary>
/// <param name="page">The current page number</param>
/// <param name="count">The page size</param>
/// <returns>Returns a result containing an optional list of items</returns>
public async Task <Result <Maybe <List <Venue> > > > FetchNearbyVenues(double lat, double lon, int page, int count)
{
try
{
using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
var lookup = new Dictionary <int, Venue>();
var data = (await con.QueryAsync <Venue, Tag, Image, VenueOpeningTime, Venue>(@"
SELECT ""Venue"".*, ""Tag"".*, ""Image"".*, ""VenueOpeningTime"".* FROM ""Venue""
left outer join ""VenueTag""
on ""VenueTag"".venueId = ""Venue"".venueId
left outer join ""Tag""
on ""Tag"".tagId = ""VenueTag"".tagId
left outer join ""Image""
on ""Image"".imageid = ""Venue"".imageid
left join ""VenueOpeningTime""
on (
""VenueOpeningTime"".venueId = ""Venue"".venueId and
(
(""VenueOpeningTime"".starttime <= @CurrentTime and ""VenueOpeningTime"".dayofweek = @DayOfWeek)
or
(""VenueOpeningTime"".endtime < ""VenueOpeningTime"".starttime and ""VenueOpeningTime"".endtime > @CurrentTime and ""VenueOpeningTime"".dayofweek = @DayOfWeek)
)
)
WHERE ST_DWithin(ST_MakePoint(@Lat, @Lon), venueCoordinate, 300)
AND ""Venue"".verificationstatus = 1
group by ""Venue"".venueid, ""Image"".imagekey, ""VenueTag"".venuetagid, ""Tag"".tagid, ""Image"".imageid, ""VenueOpeningTime"".venueopeningtimeid
ORDER BY ST_Distance (ST_MakePoint(@Lat, @Lon), venueCoordinate) ASC
LIMIT @Limit OFFSET @Offset",
map: (Venue venue, Tag tag, Image image, VenueOpeningTime venueOpeningTime) =>
{
Venue curVenue;
if (!lookup.TryGetValue(venue.VenueId, out curVenue))
{
lookup.Add(venue.VenueId, curVenue = venue);
}
if (venue.Tags == null)
{
venue.Tags = new List <string>();
}
if (tag != null)
{
venue.Tags.Add(tag.TagName);
}
if (image != null)
{
venue.ImageUrl = $"https://s3-eu-west-1.amazonaws.com/{settings.Connection.S3BucketName}/images/{curVenue.CompanyId}__{image.ImageKey}__img";
}
if (venueOpeningTime != null)
{
venue.IsOpen = true;
}
return(curVenue);
},
splitOn: "tagId,imageId,venueOpeningTimeId",
param: new { Limit = count, Offset = page * count, Lat = lat, Lon = lon, DayOfWeek = (int)DateTime.UtcNow.DayOfWeek, CurrentTime = TimeSpanHelper.LondonNow() }
).ConfigureAwait(false)).GroupBy(x => x.VenueId).Select(y => y.First()).ToList();
if (data == null)
{
return(Result.Ok(Maybe <List <Venue> > .None));
}
return(Result.Ok(Maybe <List <Venue> > .From(data)));
}
catch (Exception ex)
{
return(Result.Fail <Maybe <List <Venue> > >(ex.ToString()));
}
}
/// <summary>
/// Fetches multiple Venues
/// </summary>
/// <param name="page">The current page number</param>
/// <param name="count">The page size</param>
/// <returns>Returns a result containing an optional list of items</returns>
public async Task <Result <Maybe <List <Venue> > > > FetchQueriedVenues(string query, int page, int count)
{
try
{
using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
var lookup = new Dictionary <int, Venue>();
var data = (await con.QueryAsync <Venue, Tag, Image, VenueOpeningTime, Venue>(@"
select ""Venue"".*, ""Tag"".*, ""Image"".*, ""VenueOpeningTime"".* from ""Venue""
left outer join ""VenueTag""
on ""VenueTag"".venueId = ""Venue"".venueId
left outer join ""Tag""
on ""Tag"".tagId = ""VenueTag"".tagId
left outer join ""Image""
on ""Image"".imageid = ""Venue"".imageid
left join ""VenueOpeningTime""
on (
""VenueOpeningTime"".venueId = ""Venue"".venueId and
(
(""VenueOpeningTime"".starttime <= @CurrentTime and ""VenueOpeningTime"".dayofweek = @DayOfWeek)
or
(""VenueOpeningTime"".endtime < ""VenueOpeningTime"".starttime and ""VenueOpeningTime"".endtime > @CurrentTime and ""VenueOpeningTime"".dayofweek = @DayOfWeek)
)
)
where @Query <-> ""Venue"".venuename > 0.3
or @Query <-> ""Venue"".venueaddress > 0.3
group by ""Venue"".venueid, ""Image"".imagekey, ""VenueTag"".venuetagid, ""Tag"".tagid, ""Image"".imageid, ""VenueOpeningTime"".venueopeningtimeid
order by @Query <-> ""Venue"".venuename, @Query <-> ""Venue"".venueaddress
LIMIT @Limit OFFSET @Offset",
map: (Venue venue, Tag tag, Image image, VenueOpeningTime venueOpeningTime) =>
{
Venue curVenue;
if (!lookup.TryGetValue(venue.VenueId, out curVenue))
{
lookup.Add(venue.VenueId, curVenue = venue);
}
if (venue.Tags == null)
{
venue.Tags = new List <string>();
}
if (tag != null)
{
venue.Tags.Add(tag.TagName);
}
if (image != null)
{
venue.ImageUrl = $"https://s3-eu-west-1.amazonaws.com/{settings.Connection.S3BucketName}/images/{curVenue.CompanyId}__{image.ImageKey}__img";
}
if (venueOpeningTime != null)
{
venue.IsOpen = true;
}
return(curVenue);
},
splitOn: "tagId,imageId,venueOpeningTimeId",
param: new { Limit = count, Offset = page * count, Query = query, DayOfWeek = (int)DateTime.UtcNow.DayOfWeek, CurrentTime = TimeSpanHelper.LondonNow() }
).ConfigureAwait(false)).GroupBy(x => x.VenueId).Select(y => y.First()).ToList();
if (data == null)
{
return(Result.Ok(Maybe <List <Venue> > .None));
}
return(Result.Ok(Maybe <List <Venue> > .From(data)));
}
catch (Exception ex)
{
return(Result.Fail <Maybe <List <Venue> > >(ex.ToString()));
}
}
/// <summary>
/// Fetches a single Venue
/// </summary>
/// <param name="resourceId">The id of the Venue you wish to fetch</param>
/// <returns>Returns a result containing an optional item</returns>
public async Task <Result <Maybe <Venue> > > FetchFullVenue(int resourceId)
{
try
{
using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
var lookup = new Dictionary <int, Venue>();
var data = (await con.QueryAsync <Venue, Tag, Image, VenueOpeningTime, Venue>(@"
SELECT ""Venue"".*, ""Tag"".*, ""Image"".*, ""VenueOpeningTime"".* FROM ""Venue""
left outer join ""VenueTag""
on ""VenueTag"".venueId = ""Venue"".venueId
left outer join ""Tag""
on ""Tag"".tagId = ""VenueTag"".tagId
left outer join ""Image""
on ""Image"".imageid = ""Venue"".imageid
left join ""VenueOpeningTime""
on (
""VenueOpeningTime"".venueId = ""Venue"".venueId and
(
(""VenueOpeningTime"".starttime <= @CurrentTime and ""VenueOpeningTime"".dayofweek = @DayOfWeek)
or
(""VenueOpeningTime"".endtime < ""VenueOpeningTime"".starttime and ""VenueOpeningTime"".endtime > @CurrentTime and ""VenueOpeningTime"".dayofweek = @DayOfWeek)
)
)
WHERE ""Venue"".venueId = @VenueId
group by ""Venue"".venueid, ""Image"".imagekey, ""VenueTag"".venuetagid, ""Tag"".tagid, ""Image"".imageid, ""VenueOpeningTime"".venueopeningtimeid",
map: (Venue venue, Tag tag, Image image, VenueOpeningTime venueOpeningTime) =>
{
Venue curVenue;
if (!lookup.TryGetValue(venue.VenueId, out curVenue))
{
lookup.Add(venue.VenueId, curVenue = venue);
}
if (venue.Tags == null)
{
venue.Tags = new List <string>();
}
if (tag != null)
{
venue.Tags.Add(tag.TagName);
}
if (image != null)
{
venue.ImageUrl = $"https://s3-eu-west-1.amazonaws.com/{settings.Connection.S3BucketName}/images/{curVenue.CompanyId}__{image.ImageKey}__img";
}
if (venueOpeningTime != null)
{
venue.IsOpen = true;
}
return(curVenue);
},
splitOn: "tagId,imageId,venueOpeningTimeId",
param: new { VenueId = resourceId, DayOfWeek = (int)DateTime.UtcNow.DayOfWeek, CurrentTime = TimeSpanHelper.LondonNow() }).ConfigureAwait(false)).FirstOrDefault();
if (data == null)
{
return(Result.Ok(Maybe <Venue> .None));
}
return(Result.Ok(Maybe <Venue> .From(data)));
}
catch (Exception ex)
{
return(Result.Fail <Maybe <Venue> >(ex.ToString()));
}
}