Orders - Part 3
Introduction
In this post we will add some (more) data validation and show how to ensure a supplier is selected in the order header, and similarly, product and tax rate on the order lines. We will be linking the product drop-down list to the selected supplier and preventing the supplier being changed once order lines have been selected. Order line totals will be added to the data-grid and a previous omission corrected.
Data Validation
DataAnnotations
DataAnnotations provides a very good starting point for data validation, but I found I had difficulty getting this to work as I would expect with Syncfusion drop-down lists so had to deal with drop-down validation in C# code.
First, DataAnnotations, and changing the POHeader and POLine data models to add additional requirements and specific error messages.
Change POHeader.cs to the following. Notice that the only additional data restriction I have added is to check that the email address of the supplier is in the correct format. The other changes are to add error messages. It might be tempting to put a check that the order date falls within a range of dates; however it appears that at the time of writing there is no simple way of achieving this using DataAnnotations.
using System;
using System.ComponentModel.DataAnnotations;
// This is the model for one row in the database table.
namespace BlazorPurchaseOrders.Data
{
public class POHeader
{
[Required]
public int POHeaderID { get; set; }
[Required]
public int POHeaderOrderNumber { get; set; }
[Required]
public DateTime POHeaderOrderDate { get; set; }
[Required]
public int POHeaderSupplierID { get; set; }
[StringLength(50,ErrorMessage = "'Address' has a maximum length of 50 characters.")]
public string POHeaderSupplierAddress1 { get; set; }
[StringLength(50, ErrorMessage = "'Address' has a maximum length of 50 characters.")]
public string POHeaderSupplierAddress2 { get; set; }
[StringLength(50, ErrorMessage = "'Address' has a maximum length of 50 characters.")]
public string POHeaderSupplierAddress3 { get; set; }
[StringLength(10, ErrorMessage = "'Post Code' has a maximum length of 10 characters.")]
public string POHeaderSupplierPostCode { get; set; }
[StringLength(256, ErrorMessage = "'Email' has a maximum length of 256 characters.")]
[EmailAddress(ErrorMessage = "Invalid Email Address format.")]
public string POHeaderSupplierEmail { get; set; }
[StringLength(450)]
public string POHeaderRequestedBy { get; set; }
[Required]
public bool POHeaderIsArchived { get; set; }
public decimal POHeaderOrderTotal { get; }
}
}
And for POLines.cs, replace the existing code for ProductDescription with this:
[Required (ErrorMessage = "Product Description is compulsory.")]
[StringLength(50, MinimumLength =2, ErrorMessage ="Product Description must be between 2 and 20 characters.")]
I haven't changed any other fields on the basis that the quantity, unit price and tax rate can quite legitimately be 0 and we are setting them to 0 as defaults.
DataAnnotation Error Messages
To add the error messages to the header part of the form, first add <DataAnnotationsValidator /> after the opening <EditForm..> tag.
<DataAnnotationsValidator />After each of the address, post code and email fields insert the 'error message', such as shown below for the first address line.
<ValidationMessage For="@(() => orderaddedit.POHeaderSupplierAddress1)" />
In the SfDialog there is only one field being validated with DataAnnotations; enter the following after the text box for Product Description.
<div class=flex-container>
<ValidationMessage For="@(() => addeditOrderLine.POLineProductDescription)" />
</div>
The last error message for Product Description is enclosed by the 'flex-container' div to force the error message onto a new row below Product Description.
Checking a Supplier is selected
At a later stage, in the order line dialog, we will be restricting the Product drop-down list to those products associated with the supplier selected in the order header. However, there is nothing to prevent the user from adding order lines without selecting a supplier first. The Syncfusion DropDownList documentation had some information on how to validate drop-down values using DataAnnotations, but I had little success. Instead I decided on using C# code to check drop-down list data. To ensure a supplier has been selected we will add some validation to the toolbar handler that opens the order line dialog. We can re-use the Warning component we already have and change the warning message accordingly. To add the Warning component, add the following at the end of the html section:
<WarningPage @ref="Warning" WarningHeaderMessage="@WarningHeaderMessage" WarningContentMessage="@WarningContentMessage" />And declare the variables used at the top of the code section.
WarningPage Warning;
string WarningHeaderMessage = "";
string WarningContentMessage = "";That sets up the warning. To implement it, change the code that handles adding order lines as shown below. This checks if the SupplierID is 0 (i.e. supplier has not been selected) and if so displays a warning and prevents the order lines dialog opening.
if (args.Item.Text == "Add")
{
//Code for adding goes here
//Check that a supplier has been selected from the drop-down list
if (orderaddedit.POHeaderSupplierID == 0)
{
WarningHeaderMessage = "Warning!";
WarningContentMessage = "Please Select a Supplier before adding order lines.";
Warning.OpenDialog();
}
else
{
addeditOrderLine = new POLine(); // Ensures a blank form when adding
addeditOrderLine.POLineNetPrice = 0;
addeditOrderLine.POLineTaxID = 0;
addeditOrderLine.POLineProductID = 0;
await this.DialogAddEditOrderLine.Show();
}
}If a user attempts to add an order line before selecting a supplier they will see this message:
This does beg the question as to how to prevent the user selecting a different supplier having already added some order lines. We will tackle that little problem a little later!
Checking Product and Tax Rate is selected
We have a similar problem with order lines where we want to ensure that the user selects a product and tax rate. (Actually, I am not entirely sure that selecting a product is essential. Depending on circumstances I think it would be OK not to select a product - the user would just need to enter a description. In many cases this might be all that's required; it would mean no Product Code would appear on the order but that might be acceptable. However, for demonstration purposes I'm going to force the user to select a product!)
Again we can use the same Warning component and call it as appropriate. We will check that the user has selected a product and tax rate on the 'Save' action of the dialog. To achieve this add the following code to top or the 'OrderLineSave' method, after the check that the POLIneID == 0. You will also need to enter a closing } after the orderLines.Add section
//Check that a product has been selected from the drop-down list
if (addeditOrderLine.POLineProductCode == null || addeditOrderLine.POLineProductCode == "")
{
WarningHeaderMessage = "Warning!";
WarningContentMessage = "Please select a Product.";
Warning.OpenDialog();
}
//And check that a tax rate has been selected from the drop-down list
else if (addeditOrderLine.POLineTaxID == 0)
{
WarningHeaderMessage = "Warning!";
WarningContentMessage = "Please select a Tax Rate.";
Warning.OpenDialog();
}
else
{
The first check for Product Code checks for the Code being either null or an empty string. If this is true it calls the Warning component and supplies the header and message.
In the case of Tax Rate, the check is to test if the TaxID is 0, the value assigned to new records. If this is true again it calls the Warning component passing the header and message.
Preventing Supplier being changed
As mentioned earlier, it is proposed to limit the Products drop-down list on the order line dialog to those products associated with the supplier selected in the order header (plus products not associated with any supplier). Once order lines have been entered we therefore need to prevent the user going back to the order header and changing the supplier. To do this, whenever an order line is saved the Supplier drop-down list will be disabled. (The knock-on consequence is that if all order lines are deleted then the supplier drop-down list should be re-enabled - but that's for another day...)
At the top of the code section enter the code to declare a boolean called 'supplierEnabled' and set it to true.
public bool supplierEnabled { get; set; } = true;
In the html for the supplier drop-down list, change the Enabled property to:
Enabled="@supplierEnabled"
And at the end of the OrderLinesSave method, after updating the addeditOrderLine properties, add the following:
//We now have order lines, so prevent user from changing the supplier
supplierEnabled = false;The effect of this is that once order lines have been added the supplier drop-down disabled.
Limiting Products by Supplier
When products are added they can be associated with a supplier, or the supplier can be omitted. When entering order lines the selection of products can be made more streamlined by only showing the user the products for the supplier already entered in the order header, plus any products not linked with a supplier.
At present the Products List is populated on the form OnInitialized event and retrieves all products; we are going to move the population of the Products List to the opening of the dialog to enter order lines and at that stage, knowing the supplier, we can call a different service (and different SQL stored procedure) to retrieve only those products linked to that supplier.
Product Service
Open ProductService.cs and copy the code below to create a new method. Note that the SupplierID is being passed as a parameter to the method.
// Get list of products based on their SupplierID (SQL Select)
public async Task<IEnumerable<Product>> ProductListBySupplier(int @SupplierID)
{
IEnumerable<Product> products;
var parameters = new DynamicParameters();
parameters.Add("@SupplierID", SupplierID, DbType.Int32);
using (var conn = new SqlConnection(_configuration.Value))
{
products = await conn.QueryAsync<Product>("spProduct_ListBySupplier", parameters, commandType: CommandType.StoredProcedure);
}
return products;
}We need to add the new service to IProductService.cs. Insert the following line (after ProductList, for example.)
Task<IEnumerable<Product>> ProductListBySupplier(int SupplierID);SQL Stored Procedure
The next stage is to create the SQL stored procedure. Open SQL Management Studio, select the PurchaseOrders database and the Programmability and Stored Procedures. The stored procedure is going to be very similar to the 'Product_GetOne'. Open a new query window and enter the following and execute. Notice that the 'Where' clause includes records where the ProductSupplierID is null or 0.
USE [PurchaseOrders]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------- Stored Proc for SELECT
CREATE PROCEDURE [dbo].[spProduct_ListBySupplier]
-- Needs one parameter for Supplier
@SupplierID int
AS
BEGIN
-- SQL Select for
SELECT ProductID,
ProductCode,
ProductDescription,
ProductUnitPrice,
ProductSupplierID,
ProductIsArchived
FROM Product
WHERE (ProductSupplierID= @SupplierID) Or (ProductSupplierID=0) Or (ProductSupplierID=null)
ORDER BY ProductCode
END
GO
PurchaseOrderPage.razor
The final part of this process is to comment out or remove the existing line that gets 'product' in the OnInitializedAsync method and to insert the following in the OnChangeSupplier method after the line that sets the PreviousSelectedSupplier.
//Refresh product to select only those products for this supplier (and products with null suppliers)
product = await ProductService.ProductListBySupplier(args.ItemData.SupplierID);One other modification required is to change the OnChangeSupplier from 'void' to be an 'async Task' so that the 'await ProductService' works. With all this in place the Product dropdown list in the order lines dialog is now limited to the selected supplier.
Adding Totals to the Order Lines grid
A comment on the YouTube video for the previous post asked about putting totals at the foot of the order lines data grid. Without too much explanation, copy the code below and place it at the end of the <SfGrid> block; I suggest just before the <GridEvents> tag.
<GridAggregates>
<GridAggregate>
<GridAggregateColumns>
<GridAggregateColumn Field=@nameof(POLine.POLineNetPrice) Type="AggregateType.Sum" Format="C2">
<FooterTemplate Context="NetContext">
@{
var aggregate = NetContext as AggregateTemplateContext;
<div>
<p>@aggregate.Sum</p>
</div>
}
</FooterTemplate>
</GridAggregateColumn>
<GridAggregateColumn Field=@nameof(POLine.POLineTaxAmount) Type="AggregateType.Sum" Format="C2">
<FooterTemplate Context="TaxContext">
@{
var aggregate = TaxContext as AggregateTemplateContext;
<div>
<p>@aggregate.Sum</p>
</div>
}
</FooterTemplate>
</GridAggregateColumn>
<GridAggregateColumn Field=@nameof(POLine.POLineGrossPrice) Type="AggregateType.Sum" Format="C2">
<FooterTemplate Context="GrossContext">
@{
var aggregate = GrossContext as AggregateTemplateContext;
<div>
<p>@aggregate.Sum</p>
</div>
}
</FooterTemplate>
</GridAggregateColumn>
</GridAggregateColumns>
</GridAggregate>
</GridAggregates>You will notice that there are 3 <GridAggregateColumn> tags, and within each is a <FooterTemplate with it's own Context; I found this to be important although it wasn't obvious from the documentation.
Adding a few order lines now shows this:
And a Bug Fix...
Whilst testing for adding totals to the order lines grid I changed the Unit Price of a product and spotted that the order line Gross Price didn't change. The reason for this was that I had previously omitted to add the POLineCalc method when the user changes the unit price. To correct this add the following to the SfNumericTextBox tag (see the Quantity text box as an example).
@onfocusout='@POLineCalc'Project Code
The C# and SQL code for the changes made for adding purchase order form lines are shown here.
YouTube Video
Blazor Purchase Orders - Part 12 - Data Validation and Business Rules Logic.