/// <summary>
/// Inserts many Venue Tags
/// </summary>
/// <returns>Returns a result containing the created resource id</returns>
public async Task <Result> ReplaceVenueDocuments(int venueId, List <VenueDocument> documents)
{
try
{
using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
await con.OpenAsync().ConfigureAwait(false);
var transaction = con.BeginTransaction();
await con.ExecuteAsync(@"DELETE FROM ""VenueDocuments"" WHERE venueId = @VenueId", new { @VenueId = venueId }).ConfigureAwait(false);
if (documents.Count > 0)
{
await con.ExecuteAsync(@"INSERT INTO ""VenueDocuments"" (venueId, documentId) VALUES (@VenueId, @DocumentId)", documents).ConfigureAwait(false);
}
await transaction.CommitAsync().ConfigureAwait(false);
return(Result.Ok());
}
catch (Exception ex)
{
return(Result.Fail(ex.ToString()));
}
}
public async Task Run()
{
using (var conn = new Npgsql.NpgsqlConnection(appSettings.DatabaseConnection)) {
var carts = await conn.QueryAsync <dynamic> ("select * from carts where datecreated < @date ", new { date = DateTime.Today.AddDays(-1) });
logger.Information("Found @count cart(s) to remove", carts.Count());
foreach (var item in carts)
{
await conn.ExecuteAsync("update albums set quantity = quantity + @q where albumid = @aid", new { q = item.count, aid = item.albumid });
await conn.ExecuteAsync("delete from carts where recordid = @rid", new { rid = item.recordid });
}
}
}
/// <summary>
/// Replaces a single VenueOpeningTime with a new full set of values
/// </summary>
/// <param name="replacedVenueOpeningTime">The new data for the VenueOpeningTime you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> ReplaceVenueOpeningTime(VenueOpeningTime replacedVenueOpeningTime)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
await con.ExecuteAsync(
@"UPDATE ""VenueOpeningTime""
SET
venueId = @VenueId,
startTime = @StartTime,
endTime = @EndTime,
dayOfWeek = @DayOfWeek
WHERE venueOpeningTimeId = @VenueOpeningTimeId",
replacedVenueOpeningTime
).ConfigureAwait(false);
return(Result.Ok());
}
}
catch (Exception ex)
{
logger.LogError(ex, "Query failed");
return(Result.Fail(ex.ToString()));
}
}
/// <summary>
/// Replaces a single Review with a new full set of values
/// </summary>
/// <param name="replacedReview">The new data for the Review you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> ReplaceReview(Review replacedReview)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
await con.ExecuteAsync(
@"UPDATE ""Review""
SET
venueId = @VenueId,
userId = @UserId,
reviewSummary = @ReviewSummary,
reviewDetail = @ReviewDetail,
rating = @Rating,
registeredInterest = @RegisteredInterest,
approved = @Approved,
created = @Created,
updated = @Updated
WHERE reviewId = @ReviewId",
replacedReview
).ConfigureAwait(false);
return(Result.Ok());
}
}
catch (Exception ex)
{
logger.LogError(ex, "Query failed");
return(Result.Fail(ex.ToString()));
}
}
/// <summary>
/// Deletes entries
/// </summary>
/// <param name="planId">The id of the plan whose entries you wish to delete</param>
/// <returns>Returns a result indicating if the delete succeeded</returns>
public async Task <Result> ReplaceSubscriptionPackageEntriesForPlanId(int planId, List <int> packageIds)
{
try
{
using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
await con.ExecuteAsync("DELETE FROM \"SubscriptionPackageEntries\" WHERE planId = @ResourceId", new { ResourceId = planId }).ConfigureAwait(false);
await con.ExecuteAsync("INSERT INTO \"SubscriptionPackageEntries\" (packageId) VALUES (@PackageIds)", new { PackageIds = packageIds }).ConfigureAwait(false);
return(Result.Ok());
}
catch (Exception ex)
{
return(Result.Fail(ex.ToString()));
}
}
/// <summary>
/// Replaces a single OrderLine with a new full set of values
/// </summary>
/// <param name="replacedOrderLine">The new data for the OrderLine you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task<Result> ReplaceOrderLine(OrderLine replacedOrderLine)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
await con.ExecuteAsync(
@"UPDATE ""OrderLine""
SET
orderId = @OrderId,
productId = @ProductId,
quantity = @Quantity,
amount = @Amount
WHERE orderLineId = @OrderLineId",
replacedOrderLine
).ConfigureAwait(false);
return Result.Ok();
}
}
catch (Exception ex)
{
logger.LogError(ex, "Query failed");
return Result.Fail(ex.ToString());
}
}
/// <summary>
/// Replaces a single UserSession with a new full set of values
/// </summary>
/// <param name="replacedUserSession">The new data for the UserSession you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> ReplaceUserSession(UserSession replacedUserSession)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
await con.ExecuteAsync(
@"UPDATE ""UserSession""
SET
sessionId = @SessionId,
userId = @UserId,
authToken = @AuthToken,
type = @TokenType,
authTokenExpiry = @AuthTokenExpiry
WHERE sessionId = @UserSessionId",
replacedUserSession
).ConfigureAwait(false);
return(Result.Ok());
}
}
catch (Exception ex)
{
logger.LogError(ex, "Query failed");
return(Result.Fail(ex.ToString()));
}
}
public async Task <bool> InsertImageData(ImageLabel img)
{
try
{
using (var conn = new Npgsql.NpgsqlConnection(_configService.GetValue(ConfigurationServiceWellKnownKeys.PostgresqlDbConnectionString)))
{
await conn.OpenAsync();
var query = $"INSERT INTO label.\"images_for_labelling\" (id, id_creator_fk, createdon, filename, view, image_quality, context, container_url) VALUES (@Id, (SELECT id from campaign.\"user\" WHERE id=@UserId), @CreatedOn, @Filename, @View, @ImgQuality, @Context, @Url)";
var result = await conn.ExecuteAsync(query,
new
{
Id = img.Id,
UserId = img.Id_creator_fk,
View = img.View,
Filename = img.FileName,
ImgQuality = img.Image_Quality,
Context = img.Context,
Url = img.Container_url,
@CreatedOn = img.Createdon
}
);
return(result > 0);
}
}
catch (Exception e)
{
throw e;
}
}
public async Task <bool> AnnotateImage(ImageAnnotationBoundingBoxResult aBbox)
{
try
{
using (var conn = new Npgsql.NpgsqlConnection(_configService.GetValue(ConfigurationServiceWellKnownKeys.PostgresqlDbConnectionString)))
{
await conn.OpenAsync();
Guid id = Guid.NewGuid();
var insertQuery = "INSERT INTO label.\"bounding_boxes\" (id, id_creator_fk, createdon, id_ref_trash_type_fk, id_ref_images_for_labelling, location_x, location_y, width, height) VALUES ( @Id, @CreatorId, current_timestamp, @TrashId, @ImageId, @Location_x, @Location_y, @Width, @Height)";
var result = await conn.ExecuteAsync(insertQuery,
new
{
Id = id,
CreatorId = aBbox.CreatorId,
TrashId = aBbox.TrashId,
ImageId = aBbox.ImageId,
Location_x = aBbox.Location_x,
Location_y = aBbox.Location_y,
Width = aBbox.Width,
Height = aBbox.Height
}
);
return(result > 0);
}
}
catch (Exception e)
{
throw e;
}
}
public async Task <bool> UpdateImageData(ImageLabelViewModel img)
{
try
{
using (var conn = new Npgsql.NpgsqlConnection(_configService.GetValue(ConfigurationServiceWellKnownKeys.PostgresqlDbConnectionString)))
{
await conn.OpenAsync();
var query = "UPDATE label.\"images_for_labelling\" SET view = @View, image_quality = @ImgQuality, context = @Context WHERE id = @ImageId";
var result = await conn.ExecuteAsync(query,
new
{
View = img.View,
ImgQuality = img.ImgQuality,
Context = img.Context,
ImageId = img.ImageId,
}
);
return(result > 0);
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Replaces a single User with a new full set of values
/// </summary>
/// <param name="replacedUser">The new data for the User you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> ReplaceUser(User replacedUser)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
await con.ExecuteAsync(
@"UPDATE ""User""
SET
registrationId = @RegistrationId,
firstName = @FirstName,
lastName = @LastName,
email = @Email,
dob = @Dob,
isVerified = @IsVerified,
wantAdvertising = @WantAdvertising,
externalPaymentProcessorId = @ExternalPaymentProcessorId,
appleUserIdentifier = @AppleUserIdentifier,
facebookUserIdentifier = @FacebookUserIdentifier,
googleUserIdentifier = @GoogleUserIdentifier
WHERE userId = @UserId",
replacedUser
).ConfigureAwait(false);
return(Result.Ok());
}
}
catch (Exception ex)
{
logger.LogError(ex, "Query failed");
return(Result.Fail(ex.ToString()));
}
}
/// <summary>
/// Replaces an employee record with the given ID
/// </summary>
/// <param name="roleId">The role id for this request</param>
/// <param name="role">The new role data</param>
/// <returns>Returns a result containing an optional list of items</returns>
public async Task <Result <bool> > ReplaceEmployeeRole(int roleId, EmployeeRole role)
{
try
{
using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
await con.ExecuteAsync(
@"UPDATE ""EmployeeRole""
SET
roleName = @RoleName,
canWorkWithVenue = @CanWorkWithVenue,
canAdministerVenue = @CanAdministerVenue,
canWorkWithCompany = @CanWorkWithCompany,
canAdministerCompany = @CanAdministerCompany,
canAdministerSystem = @CanAdministerSystem,
WHERE employeeId = @EmployeeId"
, new
{
RoleId = roleId,
role.RoleName,
role.CanWorkWithVenue,
role.CanAdministerVenue,
role.CanWorkWithCompany,
role.CanAdministerCompany,
role.CanAdministerSystem
}).ConfigureAwait(false);
return(Result.Ok(true));
}
catch (Exception ex)
{
return(Result.Fail <bool>(ex.ToString()));
}
}
/// <summary>
/// Updates a single Tag with one or more values
/// </summary>
/// <param name="updatedTag">The new data for the Tag you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> UpdateTag(TagPatch updatedTag)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
var sqlPatchOperations = new StringBuilder();
var obj = updatedTag;
var operationCount = 0;
if (obj.TagName != null)
{
sqlPatchOperations.AppendLine(obj.TagName.Operation == OperationKind.Remove
? "tagName = NULL,"
: "tagName = @TagName,"
);
operationCount++;
}
var patchOperations = sqlPatchOperations.ToString();
if (operationCount > 0)
{
// Remove final ", " from StringBuilder to ensure query is valid
patchOperations = patchOperations.TrimEnd(System.Environment.NewLine.ToCharArray());
patchOperations = patchOperations.TrimEnd(',');
}
await con.ExecuteAsync($"UPDATE \"Tag\" SET {patchOperations} WHERE tagId = @ResourceId", new {
ResourceId = obj.ResourceId,
TagName = (string)(obj.TagName == default ? default : obj.TagName.Value)
}).ConfigureAwait(false);
return(Result.Ok());
}
public async Task <string> Create(User userData)
{
using (var conn = new Npgsql.NpgsqlConnection(_configService.GetValue(ConfigurationServiceWellKnownKeys.PostgresqlDbConnectionString)))
{
await conn.OpenAsync();
Guid id = Guid.NewGuid();
string insertQuery = "INSERT INTO campaign.\"user\" (id,firstname,lastname,email,emailconfirmed,passwordhash,yearofbirth,isdeleted,createdon) VALUES (@Id, @FirstName, @LastName, @Email, FALSE, @PassHash, @BirthYear, FALSE, @CreationTime)";
DateTime?birthDate = null;
int yearOfBirth;
if (Int32.TryParse(userData.BirthYear, out yearOfBirth))
{
birthDate = new DateTime(yearOfBirth, 01, 01);
}
var result = await conn.ExecuteAsync(insertQuery,
new
{
Id = id,
userData.FirstName,
userData.LastName,
userData.Email,
PassHash = userData.PasswordHash,
BirthYear = birthDate,
CreationTime = DateTime.UtcNow
}
);
return(id.ToString("D"));
}
}
/// <summary>
/// Replaces a single Company with a new full set of values
/// </summary>
/// <param name="replacedCompany">The new data for the Company you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> ReplaceCompany(Company replacedCompany)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
await con.ExecuteAsync(
@"UPDATE ""Company""
SET
companyName = @CompanyName,
companyAddress = @CompanyAddress,
companyPostcode = @CompanyPostcode,
companyContact = @CompanyContact,
companyPhone = @CompanyPhone,
companyEmail = @CompanyEmail,
externalAccountId = @ExternalAccountId,
externalCustomerId = @ExternalCustomerId,
externalAccessToken = @ExternalAccessToken,
externalRefreshToken = @ExternalRefreshToken,
externalTokenExpiry = @ExternalTokenExpiry,
referenceCode = @ReferenceCode
WHERE companyId = @CompanyId",
replacedCompany
).ConfigureAwait(false);
return(Result.Ok());
}
}
catch (Exception ex)
{
logger.LogError(ex, "Query failed");
return(Result.Fail(ex.ToString()));
}
}
/// <summary>
/// Replaces a single Product with a new full set of values
/// </summary>
/// <param name="replacedProduct">The new data for the Product you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> ReplaceVenueProduct(UpdatedProduct replacedProduct)
{
try
{
using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
await con.ExecuteAsync(
@"UPDATE ""Product""
SET
productTypeId = @ProductTypeId,
productName = @ProductName,
productDescription = @ProductDescription,
price = @Price,
image = @Image,
ageRestricted = @AgeRestricted,
parentProductId = @ParentProductId
WHERE venueId = @VenueId AND productId = @ProductId",
replacedProduct
).ConfigureAwait(false);
return(Result.Ok());
}
catch (Exception ex)
{
return(Result.Fail(ex.ToString()));
}
}
/// <summary>
/// Replaces a single EmployeeRole with a new full set of values
/// </summary>
/// <param name="replacedEmployeeRole">The new data for the EmployeeRole you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> ReplaceEmployeeRole(EmployeeRole replacedEmployeeRole)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
await con.ExecuteAsync(
@"UPDATE ""EmployeeRole""
SET
roleId = @RoleId,
roleName = @RoleName,
canWorkWithVenue = @CanWorkWithVenue,
canAdministerVenue = @CanAdministerVenue,
canWorkWithCompany = @CanWorkWithCompany,
canAdministerCompany = @CanAdministerCompany,
canAdministerSystem = @CanAdministerSystem
WHERE roleId = @EmployeeRoleId",
replacedEmployeeRole
).ConfigureAwait(false);
return(Result.Ok());
}
}
catch (Exception ex)
{
logger.LogError(ex, "Query failed");
return(Result.Fail(ex.ToString()));
}
}
/// <summary>
/// Replaces a single MenuItem with a new full set of values
/// </summary>
/// <param name="replacedMenuItem">The new data for the MenuItem you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> ReplaceMenuItem(MenuItem replacedMenuItem)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
await con.ExecuteAsync(
@"UPDATE ""MenuItem""
SET
venueId = @VenueId,
menuId = @MenuId,
productId = @ProductId
WHERE menuItemId = @MenuItemId",
replacedMenuItem
).ConfigureAwait(false);
return(Result.Ok());
}
}
catch (Exception ex)
{
logger.LogError(ex, "Query failed");
return(Result.Fail(ex.ToString()));
}
}
/// <summary>
/// Replaces a single Employee with a new full set of values
/// </summary>
/// <param name="replacedEmployee">The new data for the Employee you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> ReplaceEmployee(Employee replacedEmployee)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
await con.ExecuteAsync(
@"UPDATE ""Employee""
SET
employeeName = @EmployeeName,
username = @Username,
passwordHash = @PasswordHash,
companyId = @CompanyId,
venueId = @VenueId,
roleId = @RoleId,
securityStamp = @SecurityStamp,
confirmed = @Confirmed
WHERE employeeId = @EmployeeId",
replacedEmployee
).ConfigureAwait(false);
return(Result.Ok());
}
}
catch (Exception ex)
{
logger.LogError(ex, "Query failed");
return(Result.Fail(ex.ToString()));
}
}
/// <summary>
/// Inserts many Venue Tags
/// </summary>
/// <returns>Returns a result containing the created resource id</returns>
public async Task <Result> CreateVenueTags(int venueId, List <int> tagIds)
{
try
{
using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
await con.OpenAsync().ConfigureAwait(false);
var transaction = con.BeginTransaction();
await Task.WhenAll(tagIds.Select(async _ =>
{
return(await con.ExecuteAsync(
@"INSERT INTO ""VenueTag""(
venueId, tagId
) VALUES (
@VenueId, @TagId
)",
tagIds.Select(t => new { VenueId = venueId, TagId = t }).ToList()
).ConfigureAwait(false));
}).ToArray()).ConfigureAwait(false);
await transaction.CommitAsync().ConfigureAwait(false);
return(Result.Ok());
}
catch (Exception ex)
{
return(Result.Fail(ex.ToString()));
}
}
/// <summary>
/// Replaces a single Order with a new full set of values
/// </summary>
/// <param name="replacedOrder">The new data for the Order you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> ReplaceOrder(Order replacedOrder)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
await con.ExecuteAsync(
@"UPDATE ""CustomerOrder""
SET
orderNumber = @OrderNumber,
userId = @UserId,
venueId = @VenueId,
orderStatus = @OrderStatus,
employeeId = @EmployeeId,
orderTimeStamp = @OrderTimeStamp,
externalPaymentId = @ExternalPaymentId,
total = @Total,
serviceCharge = @ServiceCharge,
nonce = @Nonce,
orderNotes = @OrderNotes,
servingType = @ServingType,
tableName = @Table
WHERE orderId = @OrderId",
replacedOrder
).ConfigureAwait(false);
return(Result.Ok());
}
}
catch (Exception ex)
{
logger.LogError(ex, "Query failed");
return(Result.Fail(ex.ToString()));
}
}
/// <summary>
/// Replaces a single FeatureFlag with a new full set of values
/// </summary>
/// <param name="replacedFeatureFlag">The new data for the FeatureFlag you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> ReplaceFeatureFlag(FeatureFlag replacedFeatureFlag)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
await con.ExecuteAsync(
@"UPDATE ""FeatureFlag""
SET
flagId = @FlagId,
name = @Name,
description = @Description,
value = @Value
WHERE flagId = @FeatureFlagId",
replacedFeatureFlag
).ConfigureAwait(false);
return(Result.Ok());
}
}
catch (Exception ex)
{
logger.LogError(ex, "Query failed");
return(Result.Fail(ex.ToString()));
}
}
/// <summary>
/// Replaces a single Image with a new full set of values
/// </summary>
/// <param name="replacedImage">The new data for the Image you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> ReplaceImage(Image replacedImage)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
await con.ExecuteAsync(
@"UPDATE ""Image""
SET
companyId = @CompanyId,
imageKey = @ImageKey,
imageTitle = @ImageTitle
WHERE imageId = @ImageId",
replacedImage
).ConfigureAwait(false);
return(Result.Ok());
}
}
catch (Exception ex)
{
logger.LogError(ex, "Query failed");
return(Result.Fail(ex.ToString()));
}
}
/// <summary>
/// Replaces a single SubscriptionPlan with a new full set of values
/// </summary>
/// <param name="replacedSubscriptionPlan">The new data for the SubscriptionPlan you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> ReplaceSubscriptionPlan(SubscriptionPlan replacedSubscriptionPlan)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
await con.ExecuteAsync(
@"UPDATE ""SubscriptionPlan""
SET
planId = @PlanId,
companyId = @CompanyId,
externalPlanId = @ExternalPlanId
WHERE planId = @SubscriptionPlanId",
replacedSubscriptionPlan
).ConfigureAwait(false);
return(Result.Ok());
}
}
catch (Exception ex)
{
logger.LogError(ex, "Query failed");
return(Result.Fail(ex.ToString()));
}
}
/// <summary>
/// Replaces a single EmployeeSession with a new full set of values
/// </summary>
/// <param name="replacedEmployeeSession">The new data for the EmployeeSession you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> ReplaceEmployeeSession(EmployeeSession replacedEmployeeSession)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
await con.ExecuteAsync(
@"UPDATE ""EmployeeSession""
SET
sessionId = @SessionId,
employeeId = @EmployeeId,
authToken = @AuthToken,
refreshToken = @RefreshToken,
expiry = @Expiry,
refreshExpiry = @RefreshExpiry
WHERE sessionId = @EmployeeSessionId",
replacedEmployeeSession
).ConfigureAwait(false);
return(Result.Ok());
}
}
catch (Exception ex)
{
logger.LogError(ex, "Query failed");
return(Result.Fail(ex.ToString()));
}
}
/// <summary>
/// Replaces a single Device with a new full set of values
/// </summary>
/// <param name="replacedDevice">The new data for the Device you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> ReplaceDevice(Device replacedDevice)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
await con.ExecuteAsync(
@"UPDATE ""UserDevice""
SET
userId = @UserId,
deviceToken = @Token,
platform = @Platform,
updated = @UpdateTimestamp
WHERE deviceId = @DeviceId",
replacedDevice
).ConfigureAwait(false);
return(Result.Ok());
}
}
catch (Exception ex)
{
logger.LogError(ex, "Query failed");
return(Result.Fail(ex.ToString()));
}
}
public async Task CreateCampaign(Trace trace)
{
using (var conn = new Npgsql.NpgsqlConnection(_configService.GetValue(ConfigurationServiceWellKnownKeys.PostgresqlDbConnectionString)))
{
await conn.OpenAsync();
string insertQuery = "INSERT INTO campaign.\"campaign\" (id, locomotion, isaidriven, remark, id_ref_user_fk, riverside, createdon) VALUES (@Id, @Locomotion, @IsAiDriven, @Remark, @UserId, @Riverside, @CapturedOn)";
var result = await conn.ExecuteAsync(insertQuery, trace);
}
}
public async Task CreateTrajectoryPoints(IEnumerable <Position> positions)
{
using (var conn = new Npgsql.NpgsqlConnection(_configService.GetValue(ConfigurationServiceWellKnownKeys.PostgresqlDbConnectionString)))
{
await conn.OpenAsync();
string insertQuery = "INSERT INTO campaign.\"trajectory_point\" (id, id_ref_campaign_fk, \"time\", lat, lon, createdon) VALUES (@Id, @RefCampaign, @Time, @Lat, @Lon, @Createdon)";
var result = await conn.ExecuteAsync(insertQuery, positions);
}
}
/// <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>
/// Updates a single MenuItem with one or more values
/// </summary>
/// <param name="updatedMenuItem">The new data for the MenuItem you wish to update</param>
/// <returns>Returns a result indicating if the operation succeeded</returns>
public async Task <Result> UpdateMenuItem(MenuItemPatch updatedMenuItem)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
var sqlPatchOperations = new StringBuilder();
var obj = updatedMenuItem;
var operationCount = 0;
if (obj.VenueId != null)
{
sqlPatchOperations.AppendLine(obj.VenueId.Operation == OperationKind.Remove
? "venueId = NULL,"
: "venueId = @VenueId,"
);
operationCount++;
}
if (obj.MenuId != null)
{
sqlPatchOperations.AppendLine(obj.MenuId.Operation == OperationKind.Remove
? "menuId = NULL,"
: "menuId = @MenuId,"
);
operationCount++;
}
if (obj.ProductId != null)
{
sqlPatchOperations.AppendLine(obj.ProductId.Operation == OperationKind.Remove
? "productId = NULL,"
: "productId = @ProductId,"
);
operationCount++;
}
var patchOperations = sqlPatchOperations.ToString();
if (operationCount > 0)
{
// Remove final ", " from StringBuilder to ensure query is valid
patchOperations = patchOperations.TrimEnd(System.Environment.NewLine.ToCharArray());
patchOperations = patchOperations.TrimEnd(',');
}
await con.ExecuteAsync($"UPDATE \"MenuItem\" SET {patchOperations} WHERE menuItemId = @ResourceId", new {
ResourceId = obj.ResourceId,
VenueId = (int)(obj.VenueId == default ? default : obj.VenueId.Value),
MenuId = (int)(obj.MenuId == default ? default : obj.MenuId.Value),
ProductId = (string)(obj.ProductId == default ? default : obj.ProductId.Value)
}).ConfigureAwait(false);
return(Result.Ok());
}