Exemple #1
0
        /// <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()));
            }
        }
Exemple #2
0
        /// <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()));
            }
        }
Exemple #3
0
        /// <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()));
            }
        }