Index.razor
@page "/"
@using BlazorPurchaseOrders.Data
@inject IPOHeaderService POHeaderService
@inject NavigationManager NavigationManager
@inject IJSRuntime IJS
@attribute [Microsoft.AspNetCore.Authorization.AllowAnonymous]
@inject AuthenticationStateProvider AuthenticationStateProvider
<div class="col-sm-12">
<AuthorizeView>
<NotAuthorized>
<h5>Please Log in</h5>
<h6>or</h6>
<h5>Register to use Blazor Purchase Orders</h5>
</NotAuthorized>
<Authorized>
<h3>Purchase Orders</h3>
<br />
<SfGrid DataSource="@poheader"
Toolbar="Toolbaritems">
<GridEvents RowSelected="RowSelectHandler" OnToolbarClick="ToolbarClickHandler" TValue="POHeader"></GridEvents>
<GridColumns>
<GridColumn Field="@nameof(POHeader.POHeaderOrderNumber)"
HeaderText="No"
TextAlign="@TextAlign.Left"
Width="10">
</GridColumn>
<GridColumn Field="@nameof(POHeader.POHeaderOrderDate)"
HeaderText="Date"
Format="d"
Type="ColumnType.Date"
TextAlign="@TextAlign.Center"
Width="15">
</GridColumn>
<GridColumn Field="@nameof(POHeader.SupplierName)"
HeaderText="Supplier"
TextAlign="@TextAlign.Left"
Width="40">
</GridColumn>
<GridColumn Field="@nameof(POHeader.TotalOrderValue)"
HeaderText="Value"
TextAlign="@TextAlign.Right"
Format="C2"
Width="20">
</GridColumn>
<GridColumn Field="@nameof(POHeader.POHeaderRequestedBy)"
HeaderText="Requested by"
TextAlign="@TextAlign.Left"
Width="40">
</GridColumn>
</GridColumns>
</SfGrid>
<WarningPage @ref="Warning" WarningHeaderMessage="@WarningHeaderMessage" WarningContentMessage="@WarningContentMessage" />
<ConfirmPage @ref="ConfirmOrderDelete" ConfirmHeaderMessage="@ConfirmHeaderMessage" ConfirmContentMessage="@ConfirmContentMessage" ConfirmationChanged="ConfirmOrderArchive" />
</Authorized>
</AuthorizeView>
</div>
@code {
// Create an empty list, named poheader, of empty POHeader objects.
IEnumerable<POHeader> poheader;
POHeader orderHeader = new POHeader();
private List<ItemModel> Toolbaritems = new List<ItemModel>();
int POHeaderID = 0;
private int selectedPOHeaderID { get; set; } = 0;
WarningPage Warning;
string WarningHeaderMessage = "";
string WarningContentMessage = "";
ConfirmPage ConfirmOrderDelete;
string ConfirmHeaderMessage = "";
string ConfirmContentMessage = "";
public bool ConfirmationChanged { get; set; } = false;
[CascadingParameter]
private Task<AuthenticationState> authenticationStateTask { get; set; }
protected override async Task OnInitializedAsync()
{
//Populate the list of orders objects from the Purchase Order Header table.
await GetOrderList();
Toolbaritems.Add(new ItemModel() { Text = "Add", TooltipText = "Add a new order", PrefixIcon = "e-add" });
Toolbaritems.Add(new ItemModel() { Text = "Edit", TooltipText = "Edit selected order", PrefixIcon = "e-edit" });
Toolbaritems.Add(new ItemModel() { Text = "Delete", TooltipText = "Delete selected order", PrefixIcon = "e-delete" });
Toolbaritems.Add(new ItemModel() { Text = "Preview", TooltipText = "Preview selected order", PrefixIcon = "e-print" });
}
public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Text == "Add")
{
//Code for adding goes here
POHeaderID = 0;
NavigationManager.NavigateTo($"/purchaseorder/{POHeaderID}");
}
if (args.Item.Text == "Edit")
{
//Code for editing - Check that an Order has been selected from the grid
if (selectedPOHeaderID == 0)
{
WarningHeaderMessage = "Warning!";
WarningContentMessage = "Please select an Order from the grid.";
Warning.OpenDialog();
}
else
{
NavigationManager.NavigateTo($"/purchaseorder/{selectedPOHeaderID}");
}
}
if (args.Item.Text == "Delete")
{
//Code for deleting
if (selectedPOHeaderID == 0) //Check that an order has been selected
{
WarningHeaderMessage = "Warning!";
WarningContentMessage = "Please select an Order from the grid.";
Warning.OpenDialog();
}
else
{
//Populate orderHeader using selectedPOHeaderID
orderHeader = await POHeaderService.POHeader_GetOne(selectedPOHeaderID);
ConfirmHeaderMessage = "Confirm Deletion";
ConfirmContentMessage = "Please confirm that this order should be deleted.";
ConfirmOrderDelete.OpenDialog();
}
}
if (args.Item.Text == "Preview")
{
//Code for editing - Check that an Order has been selected from the grid
if (selectedPOHeaderID == 0)
{
WarningHeaderMessage = "Warning!";
WarningContentMessage = "Please select an Order from the grid.";
Warning.OpenDialog();
}
else
{
//NavigationManager.NavigateTo($"/previeworder/");
//NavigationManager.NavigateTo($"/previeworder/{selectedPOHeaderID}");
await IJS.InvokeAsync<object>("open", new object[] { "/previeworder/" + selectedPOHeaderID + "", "_blank" });
}
}
}
public void RowSelectHandler(RowSelectEventArgs<POHeader> args)
{
//{args.Data} returns the current selected records.
selectedPOHeaderID = args.Data.POHeaderID;
}
protected async Task ConfirmOrderArchive(bool archiveConfirmed)
{
if (archiveConfirmed)
{
orderHeader.POHeaderIsArchived = true;
bool Success = await POHeaderService.POHeaderUpdate(orderHeader);
await GetOrderList();
StateHasChanged();
}
}
protected async Task GetOrderList()
{
var user = (await authenticationStateTask).User;
if (user.IsInRole("Admin") || user.IsInRole("Manager"))
{
poheader = await POHeaderService.POHeaderList(null); //leave user name blank
}
else
{
poheader = await POHeaderService.POHeaderList(user.Identity.Name); //pass user name
}
}
}
using Dapper;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
namespace BlazorPurchaseOrders.Data
{
public class POHeaderService : IPOHeaderService
{
// Database connection
private readonly SqlConnectionConfiguration _configuration;
public POHeaderService(SqlConnectionConfiguration configuration)
{
_configuration = configuration;
}
// Add (create) a POHeader table row (SQL Insert)
public async Task<int> POHeaderInsert(
DateTime POHeaderOrderDate,
int POHeaderSupplierID,
string POHeaderSupplierAddress1,
string POHeaderSupplierAddress2,
string POHeaderSupplierAddress3,
string POHeaderSupplierPostCode,
string POHeaderSupplierEmail,
string POHeaderRequestedBy)
{
int newHeaderID = 0;
using (var conn = new SqlConnection(_configuration.Value))
{
var parameters = new DynamicParameters();
parameters.Add("POHeaderOrderDate", POHeaderOrderDate, DbType.Date);
parameters.Add("POHeaderSupplierID", POHeaderSupplierID, DbType.Int32);
parameters.Add("POHeaderSupplierAddress1", POHeaderSupplierAddress1, DbType.String);
parameters.Add("POHeaderSupplierAddress2", POHeaderSupplierAddress2, DbType.String);
parameters.Add("POHeaderSupplierAddress3", POHeaderSupplierAddress3, DbType.String);
parameters.Add("POHeaderSupplierPostCode", POHeaderSupplierPostCode, DbType.String);
parameters.Add("POHeaderSupplierEmail", POHeaderSupplierEmail, DbType.String);
parameters.Add("POHeaderRequestedBy", POHeaderRequestedBy, DbType.String);
parameters.Add("@Output", DbType.Int32, direction: ParameterDirection.Output);
// Stored procedure method
await conn.ExecuteAsync("spPOHeader_Insert", parameters, commandType: CommandType.StoredProcedure);
newHeaderID = parameters.Get<int>("@Output");
};
return newHeaderID;
}
// Get a list of poheader rows (SQL Select)
public async Task<IEnumerable<POHeader>> POHeaderList(string @UserName)
{
IEnumerable<POHeader> poheaders;
var parameters = new DynamicParameters();
parameters.Add("@UserName", UserName);
using (var conn = new SqlConnection(_configuration.Value))
{
poheaders = await conn.QueryAsync<POHeader>("spPOHeader_List", parameters, commandType: CommandType.StoredProcedure);
}
return poheaders;
}
// Get one poheader based on its POHeaderID (SQL Select)
// This only works if you're already created the stored procedure.
public async Task<POHeader> POHeader_GetOne(int @POHeaderID)
{
POHeader poheader = new POHeader();
var parameters = new DynamicParameters();
parameters.Add("@POHeaderID", POHeaderID, DbType.Int32);
using (var conn = new SqlConnection(_configuration.Value))
{
poheader = await conn.QueryFirstOrDefaultAsync<POHeader>("spPOHeader_GetOne", parameters, commandType: CommandType.StoredProcedure);
}
return poheader;
}
// Update one POHeader row based on its POHeaderID (SQL Update)
// This only works if you're already created the stored procedure.
public async Task<bool> POHeaderUpdate(POHeader poheader)
{
using (var conn = new SqlConnection(_configuration.Value))
{
var parameters = new DynamicParameters();
parameters.Add("POHeaderID", poheader.POHeaderID, DbType.Int32);
parameters.Add("POHeaderOrderNumber", poheader.POHeaderOrderNumber, DbType.Int32);
parameters.Add("POHeaderOrderDate", poheader.POHeaderOrderDate, DbType.Date);
parameters.Add("POHeaderSupplierID", poheader.POHeaderSupplierID, DbType.Int32);
parameters.Add("POHeaderSupplierAddress1", poheader.POHeaderSupplierAddress1, DbType.String);
parameters.Add("POHeaderSupplierAddress2", poheader.POHeaderSupplierAddress2, DbType.String);
parameters.Add("POHeaderSupplierAddress3", poheader.POHeaderSupplierAddress3, DbType.String);
parameters.Add("POHeaderSupplierPostCode", poheader.POHeaderSupplierPostCode, DbType.String);
parameters.Add("POHeaderSupplierEmail", poheader.POHeaderSupplierEmail, DbType.String);
parameters.Add("POHeaderRequestedBy", poheader.POHeaderRequestedBy, DbType.String);
parameters.Add("POHeaderIsArchived", poheader.POHeaderIsArchived, DbType.Boolean);
await conn.ExecuteAsync("spPOHeader_Update", parameters, commandType: CommandType.StoredProcedure);
}
return true;
}
}
}
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace BlazorPurchaseOrders.Data
{
// Each item below provides an interface to a method in POHeaderServices.cs
public interface IPOHeaderService
{
Task<int> POHeaderInsert(
DateTime POHeaderOrderDate,
int POHeaderSupplierID,
string POHeaderSupplierAddress1,
string POHeaderSupplierAddress2,
string POHeaderSupplierAddress3,
string POHeaderSupplierPostCode,
string POHeaderSupplierEmail,
string POHeaderRequestedBy
);
Task<IEnumerable<POHeader>> POHeaderList(string @UserName);
Task<POHeader> POHeader_GetOne(int POHeaderID);
Task<bool> POHeaderUpdate(POHeader poheader);
}
}
SQL
USE [PurchaseOrders]
GO
/****** Object: StoredProcedure [dbo].[spPOHeader_List] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------- Stored Proc for SELECT (LIST, just first six fields but you can change in final code.)
ALTER PROCEDURE [dbo].[spPOHeader_List]
@UserName nvarchar(100)
AS
BEGIN
--SQL for Select stored procedure.
SELECT dbo.POHeader.POHeaderID, dbo.POHeader.POHeaderOrderNumber, dbo.POHeader.POHeaderOrderDate, dbo.POHeader.POHeaderSupplierID, dbo.POHeader.POHeaderSupplierAddress1, dbo.POHeader.POHeaderSupplierAddress2, dbo.POHeader.POHeaderSupplierAddress3,
dbo.POHeader.POHeaderRequestedBy, dbo.Supplier.SupplierName, SUM(ISNULL((dbo.POLine.POLineProductQuantity * dbo.POLine.POLineProductUnitPrice) * (1 + dbo.POLine.POLineTaxRate), 0)) AS TotalOrderValue
FROM dbo.POHeader LEFT OUTER JOIN
dbo.Supplier ON dbo.POHeader.POHeaderSupplierID = dbo.Supplier.SupplierID LEFT OUTER JOIN
dbo.POLine ON dbo.POHeader.POHeaderID = dbo.POLine.POLineHeaderID
WHERE POHeaderRequestedBy Like CASE WHEN @UserName is null THEN '%' ELSE @UserName END AND POHeaderIsArchived = 0
GROUP BY dbo.POHeader.POHeaderID, dbo.POHeader.POHeaderOrderNumber, dbo.POHeader.POHeaderOrderDate, dbo.POHeader.POHeaderSupplierID, dbo.POHeader.POHeaderSupplierAddress1, dbo.POHeader.POHeaderSupplierAddress2, dbo.POHeader.POHeaderSupplierAddress3, dbo.POHeader.POHeaderRequestedBy,
dbo.Supplier.SupplierName
ORDER BY dbo.POHeader.POHeaderOrderNumber DESC
END
GO