public async Task OneOrder_ReturnOneRow()
{
await TestRunner.InsertTemplateAsync(new OrdersTemplate()
.WithOrderID(1001)
.WithShippedDate(DateTime.Parse("05-Mar-2020")));
ProductsTemplate product = await TestRunner.InsertTemplateAsync <ProductsTemplate>();
await TestRunner.InsertTemplateAsync(new Order_DetailsTemplate()
.WithOrderID(1001)
.WithProductID(product.Identity)
.WithUnitPrice(10)
.WithQuantity(5)
.WithDiscount(0));
QueryResult data = await TestRunner.ExecuteStoredProcedureQueryAsync("dbo.[Sales by Year]",
new SqlQueryParameter("Beginning_Date", DateTime.Parse("01-Mar-2020")),
new SqlQueryParameter("Ending_Date", DateTime.Parse("10-Mar-2020"))
);
data
.AssertRowCount(1)
.AssertRowValues(0, new DataSetRow
{
{ "ShippedDate", DateTime.Parse("05-Mar-2020") },
{ "OrderID", 1001 },
{ "Subtotal", 50m },
{ "Year", "2020" }
});
}
public async Task OneOrder_ReturnOneRow()
{
CategoriesTemplate category = await TestRunner.InsertTemplateAsync(new CategoriesTemplate()
.WithCategoryName("Category1"));
ProductsTemplate product = await TestRunner.InsertTemplateAsync(new ProductsTemplate()
.WithProductName("Product1")
.WithCategoryID(category.Identity));
await TestRunner.InsertTemplateAsync(new OrdersTemplate()
.WithOrderID(1001)
.WithOrderDate(DateTime.Parse("05-Mar-1998")));
await TestRunner.InsertTemplateAsync(new Order_DetailsTemplate()
.WithOrderID(1001)
.WithProductID(product.Identity)
.WithUnitPrice(10)
.WithQuantity(5)
.WithDiscount(0));
QueryResult data = await TestRunner.ExecuteStoredProcedureQueryAsync("dbo.[SalesByCategory]",
new SqlQueryParameter("CategoryName", "Category1"),
new SqlQueryParameter("OrdYear", "1998")
);
data
.AssertRowCount(1)
.AssertRowValues(0, new DataSetRow
{
{ "ProductName", "Product1" },
{ "TotalPurchase", 50m }
});
}
public async Task OneOrderForDifferentYear_NoRows()
{
CategoriesTemplate category = await TestRunner.InsertTemplateAsync(new CategoriesTemplate()
.WithCategoryName("Category1"));
ProductsTemplate product = await TestRunner.InsertTemplateAsync(new ProductsTemplate()
.WithProductName("Product1")
.WithCategoryID(category.Identity));
await TestRunner.InsertTemplateAsync(new OrdersTemplate()
.WithOrderID(1001)
.WithOrderDate(DateTime.Parse("05-Mar-1997")));
await TestRunner.InsertTemplateAsync(new Order_DetailsTemplate()
.WithOrderID(1001)
.WithProductID(product.Identity)
.WithUnitPrice(10)
.WithQuantity(5)
.WithDiscount(0));
QueryResult data = await TestRunner.ExecuteStoredProcedureQueryAsync("dbo.[SalesByCategory]",
new SqlQueryParameter("CategoryName", "Category2"),
new SqlQueryParameter("OrdYear", "1998")
);
data
.AssertRowCount(0);
}
public async Task OneOrderFor1998Year_ValidValue(int year, int expectedTotal)
{
CategoriesTemplate category = await TestRunner.InsertTemplateAsync(new CategoriesTemplate()
.WithCategoryName("Category1"));
ProductsTemplate product = await TestRunner.InsertTemplateAsync(new ProductsTemplate()
.WithProductName("Product1")
.WithCategoryID(category.Identity));
await TestRunner.InsertTemplateAsync(new OrdersTemplate()
.WithOrderID(1001)
.WithOrderDate(DateTime.Parse($"05-Mar-1996")));
await TestRunner.InsertTemplateAsync(new OrdersTemplate()
.WithOrderID(1002)
.WithOrderDate(DateTime.Parse($"05-Mar-1997")));
await TestRunner.InsertTemplateAsync(new OrdersTemplate()
.WithOrderID(1003)
.WithOrderDate(DateTime.Parse($"05-Mar-1998")));
await TestRunner.InsertTemplateAsync(new Order_DetailsTemplate()
.WithOrderID(1001)
.WithProductID(product.Identity)
.WithUnitPrice(10)
.WithQuantity(10)
.WithDiscount(0));
await TestRunner.InsertTemplateAsync(new Order_DetailsTemplate()
.WithOrderID(1002)
.WithProductID(product.Identity)
.WithUnitPrice(10)
.WithQuantity(5)
.WithDiscount(0));
await TestRunner.InsertTemplateAsync(new Order_DetailsTemplate()
.WithOrderID(1003)
.WithProductID(product.Identity)
.WithUnitPrice(10)
.WithQuantity(8)
.WithDiscount(0));
QueryResult data = await TestRunner.ExecuteStoredProcedureQueryAsync("dbo.[SalesByCategory]",
new SqlQueryParameter("CategoryName", "Category1"),
new SqlQueryParameter("OrdYear", year)
);
data
.AssertRowCount(1)
.AssertValue(0, "TotalPurchase", Convert.ToDecimal(expectedTotal));
}
public async Task OneEmployeesAndOneOrderTwoLiness_ReturnOneRow()
{
EmployeesTemplate employee = await TestRunner.InsertTemplateAsync(new EmployeesTemplate()
.WithFirstName("Jamie")
.WithLastName("Burns")
.WithCountry("UK"));
await TestRunner.InsertTemplateAsync(new OrdersTemplate()
.WithEmployeeID(employee.Identity)
.WithOrderID(1001)
.WithShippedDate(DateTime.Parse("05-Mar-2020")));
ProductsTemplate product1 = await TestRunner.InsertTemplateAsync <ProductsTemplate>();
ProductsTemplate product2 = await TestRunner.InsertTemplateAsync <ProductsTemplate>();
await TestRunner.InsertTemplateAsync(new Order_DetailsTemplate()
.WithOrderID(1001)
.WithProductID(product1.Identity)
.WithUnitPrice(10)
.WithQuantity(5)
.WithDiscount(0));
await TestRunner.InsertTemplateAsync(new Order_DetailsTemplate()
.WithOrderID(1001)
.WithProductID(product2.Identity)
.WithUnitPrice(20)
.WithQuantity(5)
.WithDiscount(0));
QueryResult data = await TestRunner.ExecuteStoredProcedureQueryAsync("dbo.[Employee Sales by Country]",
new SqlQueryParameter("Beginning_Date", DateTime.Parse("01-Mar-2020")),
new SqlQueryParameter("Ending_Date", DateTime.Parse("10-Mar-2020"))
);
data
.AssertRowCount(1)
.AssertRowValues(0, new DataSetRow
{
{ "Country", "UK" },
{ "LastName", "Burns" },
{ "FirstName", "Jamie" },
{ "ShippedDate", DateTime.Parse("05-Mar-2020") },
{ "OrderID", 1001 },
{ "SaleAmount", 150m }
});
}
public async Task OneOrderWithDiscount_ReturnValid(string discount, int expectedValue)
{
await TestRunner.InsertTemplateAsync(new OrdersTemplate()
.WithOrderID(1001)
.WithShippedDate(DateTime.Parse("05-Mar-2020")));
ProductsTemplate product = await TestRunner.InsertTemplateAsync <ProductsTemplate>();
await TestRunner.InsertTemplateAsync(new Order_DetailsTemplate()
.WithOrderID(1001)
.WithProductID(product.Identity)
.WithUnitPrice(10)
.WithQuantity(5)
.WithDiscount(Convert.ToSingle(discount)));
QueryResult data = await TestRunner.ExecuteStoredProcedureQueryAsync("dbo.[Sales by Year]",
new SqlQueryParameter("Beginning_Date", DateTime.Parse("01-Mar-2020")),
new SqlQueryParameter("Ending_Date", DateTime.Parse("10-Mar-2020"))
);
data
.AssertRowCount(1)
.AssertValue(0, "Subtotal", Convert.ToDecimal(expectedValue));
}