/// <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())); } }