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