IResult SetVariantRecordQuantity(SqlConnection connection, int quantity, InventoryDetermination inventoryDetermination)
{
// Update the existing variant record
using (var command = connection.CreateCommand())
{
command.CommandText = @"
update ProductVariant
set Inventory = @quantity
where VariantID = @variantId" ;
command.Parameters.AddWithValue("@quantity", quantity);
command.Parameters.AddWithValue("@variantId", inventoryDetermination.VariantId);
// If no records were updated, then the variant record got deleted after we identified it
if (command.ExecuteNonQuery() == 0)
{
return(Result.Error <InventoryDetermination>(new InventoryError.VariantNotFound(inventoryDetermination.VariantId)));
}
return(Result.Ok());
}
}
IResult SetInventoryRecordQuantity(SqlConnection connection, int quantity, InventoryDetermination inventoryDetermination)
{
// Determine the inventory record to update
int?inventoryId;
using (var command = connection.CreateCommand())
{
command.CommandText = @"
select InventoryID
from Inventory
where
VariantID = @variantId
and coalesce(Size, '') = coalesce(@size, '')
and coalesce(Color, '') = coalesce(@color, '')" ;
command.Parameters.AddWithValue("@variantId", inventoryDetermination.VariantId);
command.Parameters.AddWithValue("@size", inventoryDetermination.Size);
command.Parameters.AddWithValue("@color", inventoryDetermination.Color);
inventoryId = command.ExecuteScalar() as int?;
}
// If no inventory record exists, create one
if (inventoryId == null)
{
using (var command = connection.CreateCommand())
{
command.CommandText = @"
insert into Inventory(
[VariantID], [Color], [Size], [Quan])
values(
@variantId, @color, @size @quantity)" ;
command.Parameters.AddWithValue("@variantId", inventoryDetermination.VariantId);
command.Parameters.AddWithValue("@size", inventoryDetermination.Size);
command.Parameters.AddWithValue("@color", inventoryDetermination.Color);
command.Parameters.AddWithValue("@quantity", quantity);
command.ExecuteNonQuery();
return(Result.Ok());
}
}
// Update the existing inventory record
using (var command = connection.CreateCommand())
{
command.CommandText = @"
update Inventory
set Quan = @quantity
where InventoryID = @inventoryId" ;
command.Parameters.AddWithValue("@quantity", quantity);
command.Parameters.AddWithValue("@inventoryId", inventoryId.Value);
// If no records were updated, then the inventory record got deleted after we identified it
if (command.ExecuteNonQuery() == 0)
{
return(Result.Error <InventoryDetermination>(new InventoryError.InventoryRecordNotFound(inventoryDetermination.VariantId, inventoryDetermination.Size, inventoryDetermination.Color)));
}
return(Result.Ok());
}
}