コード例 #1
0
ファイル: TokenRepository.cs プロジェクト: Mesh-Tech/koasta
        public async Task <Result <Maybe <ApiToken> > > GetApiAuthToken(string apiToken)
        {
            try
            {
                using var con = new Npgsql.NpgsqlConnection(_settings.Connection.DatabaseConnectionString);
                var data = await con.QueryAsync <ApiToken>("SELECT apiTokenId, apiTokenValue, description, expiry FROM \"ApiToken\" WHERE apiTokenValue = @token",
                                                           new { token = apiToken }).ConfigureAwait(false);

                if (data?.Any() != true)
                {
                    return(Result.Fail <Maybe <ApiToken> >("Not found"));
                }

                return(Result.Ok(Maybe <ApiToken> .From(data.FirstOrDefault())));
            }
            catch (Exception ex)
            {
                return(Result.Fail <Maybe <ApiToken> >(ex.ToString()));
            }
        }
コード例 #2
0
        /// <summary>
        /// Replaces a venue's set of tags with a new set
        /// </summary>
        /// <returns>Returns a result indicating if the delete succeeded</returns>
        public async Task <Result> ReplaceVenueTags(int venueId, List <string> tagNames)
        {
            if (tagNames.Count == 0)
            {
                return(Result.Ok());
            }

            try
            {
                using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
                await con.OpenAsync().ConfigureAwait(false);

                var tran = await con.BeginTransactionAsync().ConfigureAwait(false);

                var tags = await con.QueryAsync <Tag>("SELECT * FROM \"Tag\" WHERE tagname = ANY(@TagNames)", new { TagNames = tagNames }).ConfigureAwait(false);

                await con.ExecuteAsync("DELETE FROM \"VenueTag\" WHERE venueId = @VenueId", new { VenueId = venueId }).ConfigureAwait(false);

                var venueTags = tags.Select(t => new VenueTag {
                    VenueId = venueId, TagId = t.TagId
                }).ToList();

                await con.ExecuteAsync("INSERT INTO \"VenueTag\" (venueid, tagid) VALUES (@VenueId, @TagId)", venueTags).ConfigureAwait(false);

                try {
                    await tran.CommitAsync().ConfigureAwait(false);
                }
                catch (Exception ex)
                {
                    await tran.RollbackAsync().ConfigureAwait(false);

                    return(Result.Fail(ex.ToString()));
                }

                return(Result.Ok());
            }
            catch (Exception ex)
            {
                return(Result.Fail(ex.ToString()));
            }
        }
コード例 #3
0
        /// <summary>
        /// Fetches multiple Tags
        /// </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 <Tag> > > > FetchTags(int page, int count)
        {
            try
            {
                using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
                {
                    var data = (await con.QueryAsync <Tag>("SELECT * FROM \"Tag\" LIMIT @Limit OFFSET @Offset", new { Limit = count, Offset = page * count }).ConfigureAwait(false)).ToList();
                    if (data == null)
                    {
                        return(Result.Ok(Maybe <List <Tag> > .None));
                    }

                    return(Result.Ok(Maybe <List <Tag> > .From(data)));
                }
            }
            catch (Exception ex)
            {
                logger.LogError(ex, "Query failed");
                return(Result.Fail <Maybe <List <Tag> > >(ex.ToString()));
            }
        }
コード例 #4
0
        /// <summary>
        /// Fetches a dictionary of productIds and their information
        /// </summary>
        /// <param name="productIds">The list of products to query</param>
        /// <returns>Returns a result containing an optional dictionary of prices</returns>
        public virtual async Task <Result <Maybe <Dictionary <int, Product> > > > FetchProducts(List <int> productIds)
        {
            try
            {
                using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
                var data = (await con.QueryAsync <Product>("SELECT * FROM \"Product\" WHERE productId = ANY (@ProductIds)", new { ProductIds = productIds }).ConfigureAwait(false)).ToList();
                if (data == null)
                {
                    return(Result.Ok(Maybe <Dictionary <int, Product> > .None));
                }

                var priceDirectory = new Dictionary <int, Product>();
                data.ForEach(p => priceDirectory[p.ProductId] = p);

                return(Result.Ok(Maybe <Dictionary <int, Product> > .From(priceDirectory)));
            }
            catch (Exception ex)
            {
                return(Result.Fail <Maybe <Dictionary <int, Product> > >(ex.ToString()));
            }
        }
コード例 #5
0
        /// <summary>
        /// Fetches a single Tag
        /// </summary>
        /// <param name="resourceId">The id of the Tag you wish to fetch</param>
        /// <returns>Returns a result containing an optional item</returns>
        public async Task <Result <Maybe <Tag> > > FetchTag(int resourceId)
        {
            try
            {
                using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
                {
                    var data = (await con.QueryAsync <Tag>("SELECT * FROM \"Tag\" WHERE tagId = @ResourceId", new { ResourceId = resourceId }).ConfigureAwait(false)).FirstOrDefault();
                    if (data == null)
                    {
                        return(Result.Ok(Maybe <Tag> .None));
                    }

                    return(Result.Ok(Maybe <Tag> .From(data)));
                }
            }
            catch (Exception ex)
            {
                logger.LogError(ex, "Query failed");
                return(Result.Fail <Maybe <Tag> >(ex.ToString()));
            }
        }
        public async Task <IEnumerable <TrashType> > GetTrashTypes()
        {
            try
            {
                using (var conn = new Npgsql.NpgsqlConnection(_configService.GetValue(ConfigurationServiceWellKnownKeys.PostgresqlDbConnectionString)))
                {
                    await conn.OpenAsync();

                    string query = "SELECT * FROM campaign.\"trash_type\"";


                    var result = await conn.QueryAsync <TrashType>(query);

                    return(result);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }
コード例 #7
0
        /// <summary>
        /// Fetches multiple Tags
        /// </summary>
        /// <returns>Returns a result containing an optional list of items</returns>
        public async Task <Result <Maybe <List <Tag> > > > FetchVenueTags(int venueId)
        {
            try
            {
                using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
                var data = (await con.QueryAsync <Tag>(@"SELECT * FROM ""Tag"" AS t
                                       INNER JOIN ""VenueTag"" vt
                                       ON t.tagId = vt.tagId
                                       WHERE vt.venueId = @VenueId", new { VenueId = venueId }).ConfigureAwait(false)).ToList();
                if (data == null)
                {
                    return(Result.Ok(Maybe <List <Tag> > .None));
                }

                return(Result.Ok(Maybe <List <Tag> > .From(data)));
            }
            catch (Exception ex)
            {
                return(Result.Fail <Maybe <List <Tag> > >(ex.ToString()));
            }
        }
        public async Task <IEnumerable <ImageAnnotationBoundingBox> > GetBBoxForOneImage(Guid id)
        {
            try
            {
                using (var conn = new Npgsql.NpgsqlConnection(_configService.GetValue(ConfigurationServiceWellKnownKeys.PostgresqlDbConnectionString)))
                {
                    await conn.OpenAsync();

                    string query = $"SELECT * FROM label.\"bounding_boxes\" WHERE label.\"bounding_boxes\".id_ref_images_for_labelling = \'{id}\'";


                    var result = await conn.QueryAsync <ImageAnnotationBoundingBox>(query);

                    return(result);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }
コード例 #9
0
        /// <summary>
        /// Fetches multiple Venues
        /// </summary>
        /// <param name="companyId">The venue's company id, or null to return all venues</param>
        /// <returns>Returns a result containing an optional list of items</returns>
        public async Task <Result <Maybe <List <VenueItem> > > > FetchVenueItems(int?companyId)
        {
            try
            {
                using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
                var query = companyId == null
                    ? @"SELECT companyId, venueId, venueName FROM ""Venue"" ORDER BY venueName ASC"
                    : @"SELECT companyId, venueId, venueName FROM ""Venue"" WHERE companyId = @CompanyId ORDER BY venueName ASC";
                var data = (await con.QueryAsync <VenueItem>(query, new { CompanyId = companyId }).ConfigureAwait(false)).ToList();
                if (data == null)
                {
                    return(Result.Ok(Maybe <List <VenueItem> > .None));
                }

                return(Result.Ok(Maybe <List <VenueItem> > .From(data)));
            }
            catch (Exception ex)
            {
                return(Result.Fail <Maybe <List <VenueItem> > >(ex.ToString()));
            }
        }
コード例 #10
0
        /// <summary>
        /// Inserts a new EmployeeRole
        /// </summary>
        /// <param name="newEmployeeRole">The EmployeeRole to be inserted</param>
        /// <returns>Returns a result containing the created resource id</returns>
        public async Task <Result <Maybe <int> > > CreateEmployeeRole(EmployeeRole newEmployeeRole)
        {
            try
            {
                using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
                {
                    var data = (await con.QueryAsync <int>(
                                    @"INSERT INTO ""EmployeeRole""(
                            
                            roleName,
                            canWorkWithVenue,
                            canAdministerVenue,
                            canWorkWithCompany,
                            canAdministerCompany,
                            canAdministerSystem
                        ) VALUES (
                            
                            @RoleName,
                            @CanWorkWithVenue,
                            @CanAdministerVenue,
                            @CanWorkWithCompany,
                            @CanAdministerCompany,
                            @CanAdministerSystem
                        ) RETURNING roleId",
                                    newEmployeeRole
                                    ).ConfigureAwait(false)).FirstOrDefault();
                    if (data < 1)
                    {
                        return(Result.Ok(Maybe <int> .None));
                    }

                    return(Result.Ok(Maybe <int> .From(data)));
                }
            }
            catch (Exception ex)
            {
                logger.LogError(ex, "Query failed");
                return(Result.Fail <Maybe <int> >(ex.ToString()));
            }
        }
コード例 #11
0
        /// <summary>
        /// Inserts a new Product
        /// </summary>
        /// <param name="newProduct">The Product to be inserted</param>
        /// <returns>Returns a result containing the created resource id</returns>
        public async Task <Result <Maybe <int> > > CreateVenueProduct(NewProduct newProduct)
        {
            try
            {
                using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
                var data = (await con.QueryAsync <int>(
                                @"INSERT INTO ""Product""(
              venueId,
              productTypeId,
              productName,
              productDescription,
              price,
              image,
              ageRestricted,
              parentProductId
            ) VALUES (
              @VenueId,
              @ProductTypeId,
              @ProductName,
              @ProductDescription,
              @Price,
              @Image,
              @AgeRestricted,
              @ParentProductId
            ) RETURNING productId",
                                newProduct
                                ).ConfigureAwait(false)).FirstOrDefault();
                if (data < 1)
                {
                    return(Result.Ok(Maybe <int> .None));
                }

                return(Result.Ok(Maybe <int> .From(data)));
            }
            catch (Exception ex)
            {
                return(Result.Fail <Maybe <int> >(ex.ToString()));
            }
        }
コード例 #12
0
        /// <summary>
        /// Inserts a new EmployeeSession
        /// </summary>
        /// <param name="newEmployeeSession">The EmployeeSession to be inserted</param>
        /// <returns>Returns a result containing the created resource id</returns>
        public async Task <Result <Maybe <int> > > CreateEmployeeSession(EmployeeSession newEmployeeSession)
        {
            try
            {
                using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
                {
                    var data = (await con.QueryAsync <int>(
                                    @"INSERT INTO ""EmployeeSession""(
                            
                            employeeId,
                            authToken,
                            refreshToken,
                            expiry,
                            refreshExpiry
                        ) VALUES (
                            
                            @EmployeeId,
                            @AuthToken,
                            @RefreshToken,
                            @Expiry,
                            @RefreshExpiry
                        ) RETURNING sessionId",
                                    newEmployeeSession
                                    ).ConfigureAwait(false)).FirstOrDefault();
                    if (data < 1)
                    {
                        return(Result.Ok(Maybe <int> .None));
                    }

                    return(Result.Ok(Maybe <int> .From(data)));
                }
            }
            catch (Exception ex)
            {
                logger.LogError(ex, "Query failed");
                return(Result.Fail <Maybe <int> >(ex.ToString()));
            }
        }
コード例 #13
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 <PaginatedResult <Venue> > > > FetchCountedQueriedCompanyVenues(int companyId, string query, int page, int count)
        {
            try
            {
                using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
                var lookup     = new Dictionary <int, Venue>();
                var totalCount = await con.QuerySingleAsync <int>(@"SELECT COUNT(*) FROM ""Venue"" where companyId = @CompanyId AND (@Query <-> ""Venue"".venuename > 0.3 or @Query <-> ""Venue"".venueaddress > 0.3);", new { CompanyId = companyId, Query = query }).ConfigureAwait(false);

                var data = (await con.QueryAsync <Venue, Tag, Image, Venue>(@"
                    select ""Venue"".*, ""Tag"".*, ""Image"".* 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
                    where ""Venue"".companyId = @CompanyId
                    and (@Query <-> ""Venue"".venuename > 0.3
                    or @Query <-> ""Venue"".venueaddress > 0.3)
                    group by ""Venue"".venueid, ""Image"".imagekey, ""VenueTag"".venuetagid, ""Tag"".tagid, ""Image"".imageid
                    order by @Query <-> ""Venue"".venuename, @Query <-> ""Venue"".venueaddress
                    LIMIT @Limit OFFSET @Offset",
                                                                            map: (Venue venue, Tag tag, Image image) =>
                {
                    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";
                    }

                    return(curVenue);
                },
                                                                            splitOn: "tagId,imageId",
                                                                            param: new { CompanyId = companyId, Limit = count, Offset = page * count, Query = query }
                                                                            ).ConfigureAwait(false)).GroupBy(x => x.VenueId).Select(y => y.First()).ToList();
                if (data == null)
                {
                    return(Result.Ok(Maybe <PaginatedResult <Venue> > .None));
                }

                return(Result.Ok(Maybe <PaginatedResult <Venue> > .From(new PaginatedResult <Venue>
                {
                    Count = totalCount,
                    Data = data
                })));
            }
            catch (Exception ex)
            {
                return(Result.Fail <Maybe <PaginatedResult <Venue> > >(ex.ToString()));
            }
        }
コード例 #14
0
        /// <summary>
        /// Inserts a new Venue
        /// </summary>
        /// <param name="newVenue">The Venue to be inserted</param>
        /// <returns>Returns a result containing the created resource id</returns>
        public async Task <Result <Maybe <int> > > CreateVenue(Venue newVenue)
        {
            try
            {
                using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
                {
                    var data = (await con.QueryAsync <int>(
                                    @"INSERT INTO ""Venue""(
                            companyId,
                            venueCode,
                            venueName,
                            venueAddress,
                            venueAddress2,
                            venueAddress3,
                            venueCounty,
                            venuePostCode,
                            venuePhone,
                            venueContact,
                            venueDescription,
                            venueNotes,
                            imageId,
                            venueLatitude,
                            venueLongitude,
                            externalLocationId,
                            verificationStatus,
                            referenceCode,
                            venueProgress,
                            servingType
                        ) VALUES (
                            @CompanyId,
                            @VenueCode,
                            @VenueName,
                            @VenueAddress,
                            @VenueAddress2,
                            @VenueAddress3,
                            @VenueCounty,
                            @VenuePostCode,
                            @VenuePhone,
                            @VenueContact,
                            @VenueDescription,
                            @VenueNotes,
                            @ImageId,
                            @VenueLatitude,
                            @VenueLongitude,
                            @ExternalLocationId,
                            @VerificationStatus,
                            @ReferenceCode,
                            @Progress,
                            @ServingType
                        ) RETURNING venueId",
                                    newVenue
                                    ).ConfigureAwait(false)).FirstOrDefault();
                    if (data < 1)
                    {
                        return(Result.Ok(Maybe <int> .None));
                    }

                    return(Result.Ok(Maybe <int> .From(data)));
                }
            }
            catch (Exception ex)
            {
                logger.LogError(ex, "Query failed");
                return(Result.Fail <Maybe <int> >(ex.ToString()));
            }
        }
コード例 #15
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()));
            }
        }
コード例 #16
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()));
            }
        }
コード例 #17
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()));
            }
        }