public async Task <int> AddProductToWarehouseAsync(ProductWarehouse productWarehouse)
{
int idProductWarehouse = 0;
using var connection = new SqlConnection(connectionString);
using var cmd = new SqlCommand("AddProductToWarehouse", connection);
var transaction = (SqlTransaction)await connection.BeginTransactionAsync();
cmd.Transaction = transaction;
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("IdProduct", productWarehouse.IdProduct);
cmd.Parameters.AddWithValue("IdWarehouse", productWarehouse.IdWarehouse);
cmd.Parameters.AddWithValue("Amount", productWarehouse.Amount);
cmd.Parameters.AddWithValue("CreatedAt", productWarehouse.CreatedAt);
await connection.OpenAsync();
int rowsChanged = await cmd.ExecuteNonQueryAsync();
if (rowsChanged < 1)
{
throw new NoResultException();
}
await transaction.CommitAsync();
} catch (Exception)
{
await transaction.RollbackAsync();
throw new Exception();
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT TOP 1 IdProductWarehouse FROM Product_Warehouse ORDER BY IdProductWarehouse DESC";
using var reader = await cmd.ExecuteReaderAsync();
await reader.ReadAsync();
if (await reader.ReadAsync())
{
idProductWarehouse = int.Parse(reader["IdProductWarehouse"].ToString());
}
await reader.CloseAsync();
await connection.CloseAsync();
return(idProductWarehouse);
}
private ProductWarehouse Create(int initialQuantity, int reserveQuantity)
{
var result = ProductWarehouse.Create(WarehouseConstants.ProductWarehouseId,
WarehouseConstants.ProductId,
initialQuantity,
WarehouseConstants.UserId,
WarehouseConstants.Timestamp);
result.ReserveForOrder(WarehouseConstants.OrderId, reserveQuantity, WarehouseConstants.Timestamp);
return(result);
}
public async Task RecalcInventoryAfterUnshipping()
{
var options = new DbContextOptionsBuilder <WHMSDbContext>().UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString()).Options;
using var context = new WHMSDbContext(options);
var warehouse = new Warehouse
{
Address = new Address {
},
Name = "Test",
};
var product = new Product
{
ProductName = "Test Product",
};
var productWarehouse = new ProductWarehouse
{
Product = product,
Warehouse = warehouse,
AggregateQuantity = 0,
TotalPhysicalQuanitiy = 10,
ReservedQuantity = 5,
};
context.Warehouses.Add(warehouse);
context.Products.Add(product);
context.ProductWarehouses.Add(productWarehouse);
await context.SaveChangesAsync();
var order = new Order {
WarehouseId = warehouse.Id, ShippingStatus = ShippingStatus.Unshipped
};
context.Orders.Add(order);
var orderItem = new OrderItem {
ProductId = product.Id, Qty = 5, OrderId = order.Id, Order = order
};
context.OrderItems.Add(orderItem);
await context.SaveChangesAsync();
var service = new InventoryService(context);
await service.RecalculateInventoryAfterUnshippingAsync(product.Id, warehouse.Id);
productWarehouse = context.ProductWarehouses.FirstOrDefault();
var expected = 5;
var expectedPhysical = 15;
Assert.Equal(expected, productWarehouse.ReservedQuantity);
Assert.Equal(expectedPhysical, productWarehouse.TotalPhysicalQuanitiy);
}
public async Task AddOrderItemsShouldUpdateQtyIfItemsIsAlreadyAddedWhenAddingItemsFromProduct()
{
var options = new DbContextOptionsBuilder <WHMSDbContext>().UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString()).Options;
using var context = new WHMSDbContext(options);
var warehouse = new Warehouse
{
Address = new Address {
},
Name = "Test",
};
var product = new Product
{
ProductName = "Test Product",
};
var productWarehouse = new ProductWarehouse
{
Product = product,
Warehouse = warehouse,
AggregateQuantity = 0,
TotalPhysicalQuanitiy = 10,
ReservedQuantity = 5,
};
context.Warehouses.Add(warehouse);
context.Products.Add(product);
context.ProductWarehouses.Add(productWarehouse);
var order = new Order {
WarehouseId = warehouse.Id
};
context.Orders.Add(order);
var orderItem = new OrderItem {
OrderId = order.Id, ProductId = product.Id, Qty = 3
};
context.OrderItems.Add(orderItem);
await context.SaveChangesAsync();
var mockInventoryService = new Mock <IInventoryService>();
var mockOrdersService = new Mock <IOrdersService>();
var service = new OrderItemsService(context, mockInventoryService.Object, mockOrdersService.Object);
var model = new AddProductToOrderInputModel {
OrderId = order.Id, ProductId = product.Id, Qty = -3
};
var id = await service.AddOrderItemAsync(model);
var orderItemDB = context.OrderItems.FirstOrDefault();
Assert.Null(orderItemDB);
}
public async Task GetPhysicalInventoryShouldReturnPositiveWithPositiveAggregateInventoryInMultipleWarehouses()
{
var options = new DbContextOptionsBuilder <WHMSDbContext>().UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString()).Options;
using var context = new WHMSDbContext(options);
var warehouse = new Warehouse
{
Address = new Address {
},
Name = "Test",
};
var warehouse2 = new Warehouse
{
Address = new Address {
},
Name = "Test",
};
var product = new Product
{
ProductName = "Test Product",
};
var productWarehouse = new ProductWarehouse
{
Product = product,
Warehouse = warehouse,
AggregateQuantity = 5,
TotalPhysicalQuanitiy = 5,
ReservedQuantity = 0,
};
var productWarehouse2 = new ProductWarehouse
{
Product = product,
Warehouse = warehouse2,
AggregateQuantity = 12,
TotalPhysicalQuanitiy = 12,
ReservedQuantity = 0,
};
context.Warehouses.Add(warehouse);
context.Warehouses.Add(warehouse2);
context.Products.Add(product);
context.ProductWarehouses.Add(productWarehouse);
context.ProductWarehouses.Add(productWarehouse2);
await context.SaveChangesAsync();
var service = new InventoryService(context);
var physicalInventory = service.GetProductPhysicalInventory(product.Id);
var expected = 17;
Assert.Equal(expected, physicalInventory);
}
public void Happy(Guid productWarehouseId, Guid productId, int quantity, string createdBy, DateTime createdAt)
{
var result = ProductWarehouse.Create(productWarehouseId, productId, quantity, createdBy, createdAt);
result.Id.Should().Be(productWarehouseId);
result.ProductId.Should().Be(productId);
result.OnHandQuantity.Should().Be(quantity);
result.ReservedQuantity.Should().Be(0);
result.AvailableQuantity.Should().Be(quantity);
result.Changes.Should().NotBeEmpty()
.And.HaveCount(1)
.And.ContainItemsAssignableTo <V1.ProductWarehouseCreated>();
}
public async Task CancelOrderShouldSetOrderStatusToCancelled()
{
var options = new DbContextOptionsBuilder <WHMSDbContext>().UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString()).Options;
using var context = new WHMSDbContext(options);
var warehouse = new Warehouse
{
Address = new Address {
},
Name = "Test",
};
var product = new Product
{
ProductName = "Test Product",
};
var productWarehouse = new ProductWarehouse
{
Product = product,
Warehouse = warehouse,
AggregateQuantity = 0,
TotalPhysicalQuanitiy = 10,
ReservedQuantity = 5,
};
context.Warehouses.Add(warehouse);
context.Products.Add(product);
context.ProductWarehouses.Add(productWarehouse);
var order = new Order {
WarehouseId = warehouse.Id
};
context.Orders.Add(order);
var orderItem = new OrderItem {
OrderId = order.Id, ProductId = product.Id, Qty = 3
};
context.Orders.Add(order);
context.OrderItems.Add(orderItem);
await context.SaveChangesAsync();
var mockInventoryService = new Mock <IInventoryService>();
var mockCustomersService = new Mock <ICustomersService>();
var service = new OrdersService(context, mockInventoryService.Object, mockCustomersService.Object);
await service.CancelOrderAsync(order.Id);
Assert.True(order.OrderStatus == OrderStatus.Cancelled);
mockInventoryService.Verify(x => x.RecalculateAvailableInventoryAsync(It.IsAny <int>()), Times.AtLeastOnce);
}
public async Task <Product> WarehouseTransfer(int id, int stock, int from, int to)
{
if (stock <= 0)
{
throw new ArgumentNullException("Invalid Stock.");
}
if (from == to)
{
throw new ArgumentNullException("Same Warehouse.");
}
// Validar el almacen origen.
await this.ValidationWarehouse(from);
// Validar el almacen destino.
await this.ValidationWarehouse(to);
var warehouseOrigin = await this.GetProductWarehouse(id, from);
if (warehouseOrigin == null || warehouseOrigin.Stock < stock)
{
throw new ArgumentNullException("Origin Warehouse Inferior Stock.");
}
// Obtener el almacen destino.
var warehouseDestination = await this.GetProductWarehouse(id, to);
// Si la relación no existe, se crea.
if (warehouseDestination == null)
{
warehouseDestination = new ProductWarehouse
{
ProductID = id,
WarehouseID = to
};
this._context.Entry(warehouseDestination).State = EntityState.Added;
}
// Restar el stock al origen y sumar al destino.
warehouseOrigin.Stock -= stock;
warehouseDestination.Stock += stock;
await this._context.SaveChangesAsync();
return(await this.Get(id));
}
public void TestProductWarehouse()
{
using (StringWriter sw = new StringWriter())
{
TextWriter stdout = Console.Out;
Console.SetOut(sw);
ProductWarehouse juice = new ProductWarehouse("Juice", 1000);
juice.AddToWarehouse(1000);
juice.TakeFromWarehouse(11);
Console.WriteLine(juice.productName); // Juice
Console.WriteLine(juice);
Console.SetOut(stdout);
string example = "Juice\nJuice: balance: 989, space left 11\n";
Assert.AreEqual(example, sw.ToString().Replace("\r\n", "\n"), "ProductWarehouse should work like in the example!");
}
}
public void TestChangeHistory()
{
using (StringWriter sw = new StringWriter())
{
TextWriter stdout = Console.Out;
Console.SetOut(sw);
ProductWarehouse juice = new ProductWarehouse("Juice", 1000);
ChangeHistory cs = new ChangeHistory();
cs.Add(100);
cs.Add(10);
cs.Add(200);
cs.Add(50);
Console.WriteLine(cs);
Console.SetOut(stdout);
string example = "Current: 50 Min: 10 Max: 200\n";
Assert.AreEqual(example, sw.ToString().Replace("\r\n", "\n"), "Changehistory should work like in the example!");
}
}
private async Task <ProductWarehouse> GetOrCreateProductWarehouseAsync(int productId, int warehouseId)
{
var productWarehouse = this.context.ProductWarehouses
.Where(x => x.ProductId == productId &&
x.WarehouseId == warehouseId)
.FirstOrDefault();
if (productWarehouse == null)
{
productWarehouse = new ProductWarehouse()
{
ProductId = productId, WarehouseId = warehouseId
};
this.context.Add(productWarehouse);
await this.context.SaveChangesAsync();
}
return(productWarehouse);
}
public async Task GetProductWarehousesShouldReturnCorrectInfoWhenProductWarehouseExists()
{
var options = new DbContextOptionsBuilder <WHMSDbContext>().UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString()).Options;
using var context = new WHMSDbContext(options);
var warehouse = new Warehouse
{
Address = new Address
{
City = "Test",
StreetAddress = "Test",
ZIP = "test",
Country = "Test",
},
Name = "Test",
};
var product = new Product
{
ProductName = "Test Product",
};
var productWarehouse = new ProductWarehouse
{
Product = product,
Warehouse = warehouse,
AggregateQuantity = 0,
TotalPhysicalQuanitiy = 0,
ReservedQuantity = 0,
};
context.Warehouses.Add(warehouse);
context.Products.Add(product);
context.ProductWarehouses.Add(productWarehouse);
await context.SaveChangesAsync();
var service = new WarehouseService(context);
var serviceProductWarehouse = service.GetProductWarehouseInfo(product.Id).FirstOrDefault();
Assert.Equal(productWarehouse.ProductId, serviceProductWarehouse.ProductId);
Assert.Equal(productWarehouse.Warehouse.Name, serviceProductWarehouse.WarehouseName);
Assert.Equal(productWarehouse.TotalPhysicalQuanitiy, serviceProductWarehouse.TotalPhysicalQuanitity);
Assert.Equal(productWarehouse.TotalPhysicalQuanitiy, serviceProductWarehouse.AggregateQuantity);
Assert.Equal(productWarehouse.TotalPhysicalQuanitiy, serviceProductWarehouse.ReservedQuantity);
}
public async Task AdjustInventoryShouldAdjustInventoryCorrectlyWithNegativeAdjustment()
{
var options = new DbContextOptionsBuilder <WHMSDbContext>().UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString()).Options;
using var context = new WHMSDbContext(options);
var warehouse = new Warehouse
{
Address = new Address {
},
Name = "Test",
};
var product = new Product
{
ProductName = "Test Product",
};
var productWarehouse = new ProductWarehouse
{
Product = product,
Warehouse = warehouse,
AggregateQuantity = 0,
TotalPhysicalQuanitiy = 10,
ReservedQuantity = 0,
};
context.Warehouses.Add(warehouse);
context.Products.Add(product);
context.ProductWarehouses.Add(productWarehouse);
await context.SaveChangesAsync();
var service = new InventoryService(context);
var adjustment = new ProductAdjustmentInputModel {
ProductId = product.Id, Qty = -10, WarehouseId = warehouse.Id
};
await service.AdjustInventoryAsync(adjustment);
productWarehouse = context.ProductWarehouses.FirstOrDefault();
var expected = 0;
Assert.Equal(expected, productWarehouse.TotalPhysicalQuanitiy);
Assert.Equal(expected, productWarehouse.AggregateQuantity);
}
public bool Update(ProductWarehouse item)
{
if (item == null)
{
throw new ArgumentNullException("item");
}
int index = repository.ProductWarehouses.FindIndex(pw => pw.Id == item.Id);
if (index == -1)
{
return(false);
}
repository.ProductWarehouses.RemoveAt(index);
var product = repository.Products.Find(p => p.Id == item.ProductId);
if (product == null)
{
item.ProductId = 0;
}
else
{
item.Product = product;
}
var warehous = repository.Warehouses.Find(w => w.Id == item.WarehouseId);
if (warehous == null)
{
item.WarehouseId = 0;
}
else
{
item.Warehouse = warehous;
}
repository.ProductWarehouses.Add(item);
return(true);
}
private void AddControls(ProductWarehouse productWarehouse)
{
var metroLabel1 = new MetroLabel();
metroLabel1.AutoSize = true;
metroLabel1.FontWeight = MetroLabelWeight.Bold;
metroLabel1.Location = new Point(15, this._index);
metroLabel1.Size = new Size(33, 19);
metroLabel1.Text = productWarehouse.Stock.ToString();
metroLabel1.UseCustomForeColor = true;
metroLabel1.ForeColor = Color.MediumSeaGreen;
if (productWarehouse.Stock <= 0)
{
metroLabel1.ForeColor = Color.Crimson;
}
if (!productWarehouse.Warehouse.Enable)
{
metroLabel1.ForeColor = Color.OrangeRed;
}
this.warehousePanel.Controls.Add(metroLabel1);
var link = new MetroLink();
link.Location = new Point(50, this._index);
link.Size = new Size(75, 23);
link.TabIndex = 2;
link.AutoSize = true;
link.Text = productWarehouse.Warehouse.Name;
link.TextAlign = ContentAlignment.MiddleLeft;
link.UseSelectable = true;
link.Tag = productWarehouse.Warehouse;
link.Click += new System.EventHandler(this.metroLink1_Click);
this.warehousePanel.Controls.Add(link);
this._index += this._margin;
}
public async Task RecalcualteAvaialbleInventoryShouldRecalculateReservedInventory()
{
var options = new DbContextOptionsBuilder <WHMSDbContext>().UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString()).Options;
using var context = new WHMSDbContext(options);
var warehouse = new Warehouse
{
Address = new Address {
},
Name = "Test",
};
var product = new Product
{
ProductName = "Test Product",
};
var productWarehouse = new ProductWarehouse
{
Product = product,
Warehouse = warehouse,
AggregateQuantity = 0,
TotalPhysicalQuanitiy = 10,
ReservedQuantity = 5,
};
context.Warehouses.Add(warehouse);
context.Products.Add(product);
context.ProductWarehouses.Add(productWarehouse);
await context.SaveChangesAsync();
var service = new InventoryService(context);
await service.RecalculateAvailableInventoryAsync(product.Id);
productWarehouse = context.ProductWarehouses.FirstOrDefault();
var expected = 0;
Assert.Equal(expected, productWarehouse.ReservedQuantity);
}
public async Task <bool> AdjustInventoryAsync(ProductAdjustmentInputModel input)
{
var productWarehouse = this.context.ProductWarehouses
.FirstOrDefault(
x => x.ProductId == input.ProductId &&
x.WarehouseId == input.WarehouseId);
if (productWarehouse == null)
{
productWarehouse = new ProductWarehouse()
{
ProductId = input.ProductId, WarehouseId = input.WarehouseId
};
this.context.ProductWarehouses.Add(productWarehouse);
}
productWarehouse.TotalPhysicalQuanitiy += input.Qty;
await this.context.SaveChangesAsync();
await this.RecalculateAvailableInventoryAsync(input.ProductId);
return(true);
}
public async Task <int> AddProductToWarehouseAsync(ProductWarehouse productWarehouse)
{
using var connection = new SqlConnection(connectionString);
using var cmd = new SqlCommand();
cmd.Connection = connection;
await connection.OpenAsync();
cmd.CommandText = "SELECT TOP 1 [Order].IdOrder FROM [Order] " +
"LEFT JOIN Product_Warehouse ON [Order].IdOrder = Product_Warehouse.IdOrder " +
"WHERE [Order].IdProduct = @IdProduct " +
"AND [Order].Amount = @Amount " +
"AND Product_Warehouse.IdProductWarehouse IS NULL " +
"AND [Order].CreatedAt < @CreatedAt";
cmd.Parameters.AddWithValue("IdProduct", productWarehouse.IdProduct);
cmd.Parameters.AddWithValue("Amount", productWarehouse.Amount);
cmd.Parameters.AddWithValue("CreatedAt", productWarehouse.CreatedAt);
// Note: close it later!
var reader = await cmd.ExecuteReaderAsync();
if (!reader.HasRows)
{
throw new NoRowsException("Invalid parameter: there is no order to fullfill!");
}
await reader.ReadAsync();
int idOrder = int.Parse(reader["IdOrder"].ToString());
await reader.CloseAsync();
cmd.Parameters.Clear();
cmd.CommandText = "SELECT Price FROM Product WHERE IdProduct = @IdProduct";
cmd.Parameters.AddWithValue("IdProduct", productWarehouse.IdProduct);
reader = await cmd.ExecuteReaderAsync();
if (!reader.HasRows)
{
throw new NoRowsException("Invalid parameter: provided IdProduct does not exist!");
}
await reader.ReadAsync();
double price = double.Parse(reader["Price"].ToString());
await reader.CloseAsync();
cmd.Parameters.Clear();
cmd.CommandText = "SELECT IdWarehouse FROM Warehouse WHERE IdWarehouse = @IdWarehouse";
cmd.Parameters.AddWithValue("IdWarehouse", productWarehouse.IdWarehouse);
reader = await cmd.ExecuteReaderAsync();
if (!reader.HasRows)
{
throw new NoRowsException("Invalid parameter: provided IdWarehouse does not exist!");
}
await reader.CloseAsync();
cmd.Parameters.Clear();
var transaction = (SqlTransaction)await connection.BeginTransactionAsync();
cmd.Transaction = transaction;
try
{
cmd.CommandText = "UPDATE [Order] SET FulfilledAt = @CreatedAt WHERE IdOrder = @IdOrder";
cmd.Parameters.AddWithValue("CreatedAt", productWarehouse.CreatedAt);
cmd.Parameters.AddWithValue("IdOrder", idOrder);
int rowsUpdated = await cmd.ExecuteNonQueryAsync();
if (rowsUpdated < 1)
{
throw new NoResultException();
}
cmd.Parameters.Clear();
cmd.CommandText = "INSERT INTO Product_Warehouse(IdWarehouse, IdProduct, IdOrder, Amount, Price, CreatedAt) " +
$"VALUES(@IdWarehouse, @IdProduct, @IdOrder, @Amount, @Amount*{price}, @CreatedAt)";
cmd.Parameters.AddWithValue("IdWarehouse", productWarehouse.IdWarehouse);
cmd.Parameters.AddWithValue("IdProduct", productWarehouse.IdProduct);
cmd.Parameters.AddWithValue("IdOrder", idOrder);
cmd.Parameters.AddWithValue("Amount", productWarehouse.Amount);
cmd.Parameters.AddWithValue("CreatedAt", productWarehouse.CreatedAt);
int rowsInserted = await cmd.ExecuteNonQueryAsync();
if (rowsInserted < 1)
{
throw new NoResultException();
}
await transaction.CommitAsync();
} catch (Exception) {
await transaction.RollbackAsync();
throw new SomethingWentWrongException("Something went wrong! Database internal server problem!");
}
cmd.Parameters.Clear();
cmd.CommandText = "SELECT TOP 1 IdProductWarehouse FROM Product_Warehouse ORDER BY IdProductWarehouse DESC";
reader = await cmd.ExecuteReaderAsync();
await reader.ReadAsync();
int idProductWarehouse = int.Parse(reader["IdProductWarehouse"].ToString());
await reader.CloseAsync();
await connection.CloseAsync();
return(idProductWarehouse);
}
public async Task ShipOrderShouldNotUpdateOrderStatusesAndInventoryWhenAlreadyShipped()
{
var options = new DbContextOptionsBuilder <WHMSDbContext>().UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString()).Options;
using var context = new WHMSDbContext(options);
var warehouse = new Warehouse
{
Address = new Address {
},
Name = "Test",
};
var product = new Product
{
ProductName = "Test Product",
};
var productWarehouse = new ProductWarehouse
{
Product = product,
Warehouse = warehouse,
AggregateQuantity = 0,
TotalPhysicalQuanitiy = 10,
ReservedQuantity = 5,
};
var shippingService = "FedEx";
var carrier = new Carrier {
Name = shippingService
};
context.Carriers.Add(carrier);
var shipping = new ShippingMethod {
Carrier = carrier, CarrierId = carrier.Id, Name = shippingService
};
context.ShippingMethods.Add(shipping);
await context.SaveChangesAsync();
context.Warehouses.Add(warehouse);
context.Products.Add(product);
context.ProductWarehouses.Add(productWarehouse);
await context.SaveChangesAsync();
var order = new Order {
WarehouseId = warehouse.Id, ShippingStatus = ShippingStatus.Shipped
};
context.Orders.Add(order);
var orderItem = new OrderItem {
ProductId = product.Id, Qty = 5, OrderId = order.Id, Order = order
};
context.OrderItems.Add(orderItem);
await context.SaveChangesAsync();
var mockInventoryService = new Mock <IInventoryService>();
var service = new ShippingService(context, mockInventoryService.Object);
var shipMethod = new ShippingMethodInputModel {
CarrierId = carrier.Id, Id = shipping.Id
};
await service.ShipOrderAsync(new Web.ViewModels.Orders.ShipOrderInputModel {
OrderId = order.Id, ShippingMethod = shipMethod, TrackingNumber = "test"
});
mockInventoryService.Verify(x => x.RecalculateInventoryAfterShippingAsync(It.IsAny <int>(), It.IsAny <int>()), Times.Never);
}
public void Bad(Guid productWarehouseId, Guid productId, int quantity, string createdBy, DateTime createdAt)
{
Action act = () => ProductWarehouse.Create(productWarehouseId, productId, quantity, createdBy, createdAt);
act.Should().Throw <DomainValidationException>();
}
