Washington State University



#Region "Introduction and Premise"'This program functions as a POS system and is an extension over prior programs in that each invoice can have many line items. Also the transaction table now has information pulled from to two dimension tables (products and customers). An array is used to collect and organize rows of transaction data (the line items on an invoice).'This program uses the POSSales table for storage of summary data about an invoice (the grand total, date, and customerID, etc.) and a POSSalesLineItems table to show the detail line items which are the products purchased on that invoice. A customer is selected, products are selected, and the system builds an invoice. Three entities come together customers, sales, and products. Sales data is organized into detailed and summary. The detail data can be mined for patterns of product and customer activity. While any sales report can be based on the detailed POSSalesLineItems in PowerBI, Tableau or Excel, we build simple summary reports using the POSSales table and display them in a gridview. Some reports are more easily made with the summary table, and others with the salesdetail table.'This project then is a POS for a construction supply store. The products sold are in two different product categories, building supplies and tools. The scenario is that construction employees for approved companies come into the store, pick out the products they need and the invoicing system records the transaction details and the customer available credit is reduced. Customers take products off the shelf and bring them to the front check out for acct settlement. Employees from the general contractors can come into the store and pick up the construction supplies that they need without paying cash. Rather than use cash, the available credit of the general contractor is reduced after each sale. The general contractor can have an invoice with lots of line items (nails, sheetrock, hammers, etc.)'Use the clever use of arrays to get ideas for your final project. ' Here is a list of suggested future program enhancements:'1. the need to check if the entire invoice cost exceeds the available credit for the customer.'2. check of inventory and projected sales to see if automatic reorder of inventory is needed.'3. The invoice form could be made nicer looking'4. More leeway is needed to assign discounts to customers might be nice.'5. Currently the inventory balance in the products table can for example can indicate that there are 10 of a product in stock, however the system will allow a customer to buy 50. There is no check to limit the number of a product purchased, or a facility to reconcile (correct) the inventory levels.#End RegionImports System.DataImports System.Data.SqlClientPartial Class NewSale Inherits System.Web.UI.Page#Region "con" Public Shared con As New SqlConnection("Data Source=cb-ot-devst05.ad.wsu.edu;Initial Catalog=Featherman_Analytics;Persist Security Info=True;User ID=???;Password=???")#End Region#Region "Public Shared objects" 'this first dataadapter is used to both a) fetch the schema of a database table to copy into an array to facilitate saving a new line item on the invoice and b) also to push back 1 or more rows of new data back to the sql database. That the dataAdapter has a SQLcommandBuilder assigned to it, tells you that there will be new rows of data added or edited. The SQLcommandBuilder writes much of the SQL for that so you do not have to. Public Shared daPOSSalesLineItems As New SqlDataAdapter("SELECT * FROM featherman.POSSalesLineItems", con) Public Shared cbSLI As New SqlCommandBuilder(daPOSSalesLineItems) 'these data tables are all public page level so that different procedures can access their values Public Shared gdtLineItems As New DataTable Public Shared gdtProductsDetails As New DataTable Public Shared gdtMetricsFor1Contractor As New DataTable Public Shared gdtInfoForOneCustomer As New DataTable 'The first integer variable declared here is used to hold a retrieved invoice # so we can calculate what the next invoice # is. The second integer variable declared here keeps track of sales order line items for the in-memory datatable (aka the shopping cart) that is displayed on-screen. Public Shared gintNextInvoiceNumber, gintLineNumber As Integer ' The global variables are used to pass values from one procedure to another. Public Shared gdecSalesPrice, gdecinvoiceTotal, gdecInvoiceProfit As Decimal#End Region#Region "Page Init" Protected Sub Page_Init(sender As Object, e As EventArgs) Handles Me.Init Label1.Text = Today.Date 'show the current data on the screen View1ddlRemove.Items.Insert(0, "Select item to remove from invoice") ' we add a phrase to a dropdownlist for later use. 'get all customer names and ID's for the dropdownlist Dim daGetCustomers As New SqlDataAdapter("SELECT Customer_ID, CustomerName FROM POSCustomers", con) 'at page initialization we can also get the highest invoice # so that it can be used to save line items into the POSSalesLineItems table. We cannot rely on the auto-increment property of the SQL Server database table since we are working with an in-memory datatable, and the auto-increment only works when you save the data to th database. While we could leave the invoice# blank during the transaction, adding it when the data is saved, we rather demonstrate how to retrieve the highest invoice # (using the SELECT MAX(Sale_ID)) and then increment that manually for the current invoice. We also could just leave the Sale_ID blank on the webpage, trusting that SQLServer will add that later. Dim cmdGetInvoiceNumber As New SqlCommand("SELECT MAX(Sale_ID) FROM [featherman].[POSSalesLineItems]", con) 'this local datatable array is used to store the customer info for the ddl Dim dtCustomers As New DataTable Try If con.State = ConnectionState.Closed Then con.Open() gintNextInvoiceNumber = 0 'reset the invoice # for the next invoice gintNextInvoiceNumber = cmdGetInvoiceNumber.ExecuteScalar gintNextInvoiceNumber += 1 daGetCustomers.Fill(dtCustomers) With View1ddlCustomer .DataSource = dtCustomers .DataTextField = "CustomerName" .DataValueField = "Customer_ID" .DataBind() .Items.Insert(0, "Select a customer") End With With View2ddlContractors .DataSource = dtCustomers .DataTextField = "CustomerName" .DataValueField = "Customer_ID" .DataBind() .Items.Insert(0, "Select a contractor") End With Catch ex As Exception Response.Write(ex.Message) End Try 'We will use a dataAdapter to save the line items from the invoice back to the database. We will capture rows of line items for an invoice (this is the shopping cart) by creating new rows in an in-memory array. That in-webpage array however does not know the schema (columns names, etc.) unless you tell it, so the next line assigns the schema from the database table to the webpage's array to facilitate data entry. When we create a new row in the shopping cart, it already knows its columns daPOSSalesLineItems.FillSchema(gdtLineItems, SchemaType.Mapped) End Sub#End Region#Region "View #1: Get customer info" Protected Sub View1ddlCustomer_SelectedIndexChanged(sender As Object, e As EventArgs) Handles View1ddlCustomer.SelectedIndexChanged 'when the POS worker selects the customer from the DDL (and their customerID is captured in the .selectedvalue property) - this procedure retrieves and shows more information about that customer. One row is selected from the database's customers table. This one row of data is shown in a gridview so the POS program user knows more about whom they are transacting with in the store. If View1ddlCustomer.SelectedIndex <= 0 Then Exit Sub 'the SQL SELECT procedure below requires a customerID so we stop and exit the procedure if no customer was selected in the list control. 'When the program user selects a new customer the sales process starts, so this next line of code ensures no rows of data exist in the shopping cart. If gdtLineItems.Rows.Count > 0 Then gdtLineItems.Rows.Clear() 'The database table from which to retrieve the data to display in the webpage's gridview is designated in the FROM portion of this next SQL statement. You often do not need to specify the full name of the table as demonstrated here, usually you just need to specify the tablename (here POSCustomers). Dim daGetDimensionInfoForCustomer As New SqlDataAdapter("SELECT Customer_ID as [ID], [CustomerName] as [Name], [Address], [City], [State], [Zip], VIP FROM [Featherman_Analytics].[featherman].[POSCustomers] WHERE Customer_ID = @p1", con) 'this SQL statement is parameterized (variant) so this next line of code tells the dataAdapter that is going to run the SQL SELECT statement what web control to get its value from. When the SQL statement is executed, the dataAdapter pulls the current value from the webcontrol specified in the .addwithvalue statement. With daGetDimensionInfoForCustomer.SelectCommand.Parameters .Clear() .AddWithValue("@p1", CInt(View1ddlCustomer.SelectedValue)) End With 'if the data for the last customer is still on screen then clear it out If gdtInfoForOneCustomer.Rows.Count > 0 Then gdtInfoForOneCustomer.Rows.Clear() 'this is where the red sql SELECT statement above is run and the ONE retrieved row of data is copied into this datatable Try daGetDimensionInfoForCustomer.Fill(gdtInfoForOneCustomer) If gdtInfoForOneCustomer.Rows(0).Item("VIP") = True Then 'change the webform to display a VIP checkbox if the customer is a VIP View1chkVIP.Checked = True View1chkVIP.Visible = True Label2.Visible = True Else View1chkVIP.Visible = False Label2.Visible = False End If 'Here we instruct the gridview control to display the one row of data retrived With View1dgvDataFor1Customer .DataSource = gdtInfoForOneCustomer .DataBind() End With 'This procedure is getting lengthy, but more work is needed when a customer is selected. Here we call another GetMetrics() procedure that compiles and displays a second gridview with a second table of metrics (dtMetricsFor1Contractor) for the selected customer. These metrics are shown at the top of the invoice, so that the sales rep knows even more about the customer standing in front of them (could be a VIP, or a customer with limited credit). Also if the invoice is printed or PDF emailed, then its a nice feature to have the customer address formatted nicely. Call GetMetrics() Catch ex As Exception Response.Write(ex.Message) End Try End Sub 'View #1: Get a Row of Metrics For the Selected Customer" Protected Sub GetMetrics() 'Here an effort is made to nicely FORMAT() the columns of data that are retrieved to display the customer sales metrics. The FORMAT() command reads FORMAT([ColumnName], format style). 'Use SSMS to look at the publicly accessible [Featherman_Analytics].[featherman].[POSCustomers]. Can you think of other columns of metrics to add based on the available columns? An engaged business student is often looking for more insight into the data. Your suggestions to your boss based on your careful analysis can reengineer a business process making it more efficient. Productivity improvement faster that competitors is a core business goal. 'Any intricate SQL statement you want to run should be created in SQL Server (SSMS) and tested for accuracy. Only after the query works in SSMS should you copy it into a dataAdapter. Dim daGetMetricsForCustomer As New SqlDataAdapter("SELECT [State], [CustomerName], FORMAT([LastPurchase] , 'd') as [Last TA], [NumberPurchases] as [# TA], [TotalUnits], FORMAT([TotalSales], 'C0') as [$Revenue], FORMAT([TotalSales]/[NumberPurchases], 'N0') as [Avg. $ale], FORMAT([ProfitGenerated], 'C0') as [Profit], FORMAT([ProfitGenerated]/[TotalSales], 'p1') as [Contribution Margin %], FORMAT([AvailableCredit], 'C0') as [Available Credit] FROM [Featherman_Analytics].[featherman].[POSCustomers] WHERE Customer_ID = @p1", con)'clear out any prior contractor information from the datatable, so that you only display the corrent customer. With gdtMetricsFor1Contractor.Rows If .Count > 0 Then .Clear() End With 'capture the contractor_ID number and feed that to the SQL Select statement as the filter, so that you retrive and display the correct row of data, at the top of the POS sale form. With daGetMetricsForCustomer.SelectCommand.Parameters .Clear() .AddWithValue("@p1", CInt(View1ddlCustomer.SelectedValue)) End With'now run the red SQL Select statement to retrieve some of the columns for the selected customer, and display the data in a gridview Try daGetMetricsForCustomer.Fill(gdtMetricsFor1Contractor) View1dgvDataFor1CustomerMetrics.DataSource = gdtMetricsFor1Contractor View1dgvDataFor1CustomerMetrics.DataBind() Catch ex As Exception Response.Write(ex.Message) End Try End Sub#End Region#Region "View #1: Populate DDL with construction products for selected product category" Private Sub View1rblProductCategory_SelectedIndexChanged(sender As Object, e As EventArgs) Handles View1rblProductCategory.SelectedIndexChanged 'When the POS employee selects the category in this radiobutton list, then the adjacent products dropdown list is populated with the products that are in inventory (have a stock quantity >0) FOR ONLY THE SELECTED PRODUCT CATEGORY. The program user has an easier transaction experience when the DDL has fewer options, so the list is filtered a) by category and b) only products with inventory are displayed 'this procedure provides a filtered list to the products DDL keeping the options limited and speeding up the transaction. A dataAdapter runs a PARAMETERIZED SQL SELECT command that retrieves two columns into a global datatable to populate the products DDL, the third column is sales price. When a product is added to the shopping cart data table in a procedure below, the sales price is pulled from this global gdtProductsDetails data table. 'FYI - this system does not include an ordering page yet to replace products that are low in stock or not in stock. If View1rblProductCategory.SelectedIndex = -1 Then Exit Sub Dim daGetProducts As New SqlDataAdapter("Select ProductID, ProductName FROM featherman.POSProducts WHERE Category = @p1 AND StockQuantity > 0", con) With daGetProducts.SelectCommand.Parameters .Clear() 'Here the parameter needs the word for the category, so we capture the term selected not the category ID number. .AddWithValue("@p1", View1rblProductCategory.SelectedItem.Text) End With 'The list of products in the DDL is from different categories of products. We need to clear out any prior products info in the drop down list before we load the products for the category that the program user specified. If gdtProductsDetails.Rows.Count > 0 Then gdtProductsDetails.Rows.Clear() Try daGetProducts.Fill(gdtProductsDetails) With View1ddlProducts .DataSource = gdtProductsDetails .DataTextField = "ProductName" .DataValueField = "ProductID" .DataBind() .Items.Insert(0, "Select a product") End With Catch ex As Exception Response.Write(ex.Message) End Try End Sub#End Region#Region "View #1: Add line items to shopping cart" Protected Sub View1ddlProducts_SelectedIndexChanged(sender As Object, e As EventArgs) Handles View1ddlProducts.SelectedIndexChanged 'While we have the customer, category, and product ID's and names available in list controls each of those .fill operations imported only two columns into the webpage. We need a lookup table or way to bring the sales price of the product being purchased, into the shopping cart. This procedure pulls the sales price of the selected product into a global variable so it can be passed into the new row of data being created in the next procedure which adds a row (a sales line item) to the shopping cart. 'This procedure then 'looks up and retrieves' the sales price for the selected product from the POSProducts database table. When you need to retrieve only one value (not an entire row of data or an entire table of data) use a SQLCommand and the .Executescalar method assigning the retrieved value directly to a variable or property of a control. Here the query retrieves a sales price and assigns it as follows gdecPrice = cmdGetPrice.ExecuteScalar. The variable receiving the value is global becuase it needs to be passed to the next procedure. Dim getSalesPriceCommand As New SqlCommand("SELECT SalesPrice FROM featherman.POSProducts WHERE ProductID = @p1", con) 'make sure a product was selected in the DDL because the SQL SELECT statement is parameterized and expects a value. The .addwithValue tells the SqlCommand what control to retrieve the value from. If View1ddlProducts.SelectedValue <= 0 Then Exit Sub With getSalesPriceCommand.Parameters .Clear() .AddWithValue("@p1", View1ddlProducts.SelectedValue) End With 'now open a connection to the database and fetch the value you need into a global variable so it can be passed to the next procedure. Try If con.State = ConnectionState.Closed Then con.Open() gdecSalesPrice = getSalesPriceCommand.ExecuteScalar Catch ex As Exception Response.Write(ex.Message) Finally con.Close() End Try End Sub Protected Sub View1BtnAddProduct_Click(sender As Object, e As EventArgs) Handles View1BtnAddProduct.Click 'This code is realtively simple from a coding perspective, however it is core to the advancement demonstrated in this progam. The concept remains simple, create a new blank row and stuff it with data from different tables and controls. Rather than be saved to database immediately however,the line items are stored in an array until they are all saved to database in one simple dataAdapter.Update procedure (below). 'Here rows of data (each representing a line item on an invoice) are added to the datatable (gdtLineItems) being used as the shopping cart. Each row added to the shopping cart array holds information from the products and customers table and records line items on an invoice. We add to the datatable in memory with one row per product placed on the order (after the sale is saved the array holding the line items is cleared out. Here for the first time we have an invoice that is for specific products which are recorded. we utilize an array in a new way which facilitates pulling data together and saving data. This procedure is used to add more and more items to the shopping cart, another procedure is used to calculate the entire order, and save save all the data in the shopping cart into the sales order details table. This functionality made possible by the .fillschema code that is run at page inititalization. 'Now we create a new line item for the product to be added to the shopping cart. As before we create a new blank row then gather the data for the columns of the new row. Notice that FOR NOW we are saving these transaction rows to the in-memory datatable, not directly to the database. We allow the POS employee to enter in all the line items before they are saved to the database. All line items will be block processed and saved back to the database in one sweet dataadapter.update operation. Dim dr As DataRow = gdtLineItems.NewRow() 'Stop the procedure if a product, customer or quantity were not selected. If View1ddlProducts.SelectedIndex <= 0 OrElse View1ddlCustomer.SelectedIndex <= 0 OrElse Convert.ToDecimal(txtQty.Text) = 0 OrElse txtQty.Text = Nothing Then Exit Sub 'We need to manually set the line item numbers inside the shopping cart. This gintLineNumber variable used to manage the item numbers gets zero'd out when you save the invoie and clear the form. This next variable sets the line item for the current invoice You may ask, why not use a auto-number field? Because auto-number fields are for the primary key only and this LineNumber (LIne_item) field is not the primary key in the table, rather it is the second column. Technically you could create a composite primary key (combination of SaleID# and Line Item#) for this table, but that is outside the scope of this introductory class. gintLineNumber += 1 'above we created the new row and the prior .fillschema code instructed the row to know its columns. The next lines fill in the values for each column of the blank row. Figure out where each value came from. dr("Sale_ID") = gintNextInvoiceNumber dr("Line_Item") = gintLineNumber dr("Customer_ID") = View1ddlCustomer.SelectedValue dr("Product_ID") = View1ddlProducts.SelectedValue dr("Product_Name") = View1ddlProducts.SelectedItem.Text dr("Sales_Price") = gdecSalesPrice 'The View1ddlProducts_SelectedIndexChanged procedure above uses a SQLCommand.executescalar to fetch the price for the product selected on the list 'here we limit the number units on one line item to 50. Notice there is no check of the available inventory. The scenario is that a customer brings items to the cashier and so the quantity is accurate as there are physical products, however there is the potential problem of the POS computer system having an incorrect inventory balance. This inventory check has not been included in this version and is in the product development backlog. Select Case Convert.ToDecimal(txtQty.Text) Case Is > 50 dr("Quantity") = 50 Case = Nothing Exit Sub Case Is > 0 dr("Quantity") = Convert.ToDecimal(txtQty.Text) End Select dr("Date") = Now() 'the built-in Now() function retrieves the current date and time from the computer's clock. 'now that we have the columns of our new row filled in, we append the row to the webpage's in memory datatable. The progam user can choose to add more items into the shopping cart by re-running this procedure. 'In the next procedure we will save all the datarows of line items to the database in one procedure. Here we are adding rows of products to the shopping cart which is a set of rows in a datatable onscreen in our webpage. gdtLineItems.Rows.Add(dr) 'now that we have a row of data, lets show it in the gridview control View1dgvShoppingCart.DataSource = gdtLineItems View1dgvShoppingCart.DataBind() 'When we add a row to the shopping cart, the customer might realize they made a mistake and want to remove the item from the shopping cart. To facilitate removing an item from the shopping cart, we put it into a second dropdownlist to show a list to the program user, so that they can remove an item if needed. View1ddlRemove.Items.Add(View1ddlProducts.SelectedItem.Text) 'keep the form a little clean to prompt the program user to keep adding products to the shopping cart. View1ddlProducts.SelectedIndex = -1 txtQty.Text = Nothing End Sub#End Region#Region "View #1: Remove line items from the shopping cart" Protected Sub View1ddlRemove_SelectedIndexChanged(sender As Object, e As EventArgs) Handles View1ddlRemove.SelectedIndexChanged 'The items that are added to the invoice and stored in the global array are also cleverly added to a dropdownlist. why? to facilitate a product being removed from the shopping cart array. So the DDL here has the items that are added to the shopping cart (see code near the end of the prior procedure). He we use the rows.removeat function to remove an item from the shoppingcart datatable. Because the line items in the array are indexed the removeat function of the datatable is useful, and in fact genious! 'This procedure should not run if a valid selection was not made in the dropdown list. If View1ddlRemove.SelectedIndex <= 0 Then Exit Sub 'here is the code that removes one line item from the shoppingcart array, then from the DDL. Because the dropdownlist has a 0-indexed item prompting the program user to select an item to remove it (check the page_init for that line of code) we have to subtract one from the index number selected in the DDL. So as the program user puts line items into the shopping cart, they are also added to a DDL. The program user can select an item from the DDL and run this procedure, which removes the product from BOTH the shopping cart array and the DDL. gdtLineItems.Rows.RemoveAt(View1ddlRemove.SelectedIndex - 1) View1ddlRemove.Items.RemoveAt(View1ddlRemove.SelectedIndex - 1) 'After we remove a row from the dataTable then we rebind the gridview, so that the deleted row is removed from display. View1dgvShoppingCart.DataSource = Nothing View1dgvShoppingCart.DataSource = gdtLineItems View1dgvShoppingCart.DataBind() 'ready the form for further data entry. View1ddlProducts.SelectedIndex = -1 txtQty.Text = Nothing End Sub#End Region#Region "View #1: Close out shopping cart: Save and Update" Protected Sub View1btnRecordSale_Click(sender As Object, e As EventArgs) Handles View1btnRecordSale.Click 'Save sales transaction both at detail level (the line items) and summary level, then update the customer record and inventory levels for the products that sold" 'These variables are used to calculate the invoice total and profit. We zero out these values. gdecInvoiceProfit = 0 gdecinvoiceTotal = 0 'here we take the items from the in-memory datatable, compile data, and push them back to the database. Again we leverage the dataAdapter's .update function, which is special because it can insert many rows of data for us. 'This code stops this procedure if the save button was pressed but no items were selected yet. If gdtLineItems.Rows.Count = 0 Then Response.Write("No records To save") Exit Sub End If 'This next line saves all the individual rows of data to the POSSalesLineItems database table. Try daPOSSalesLineItems.Update(gdtLineItems) Catch ex As Exception Response.Write(ex.Message) End Try 'now that the detail line items are saved (the easiest part!) We have 3 more jobs. ' 1. Save the comiled sales data to the summary Sales table ' 2. Reduce the stock levels ' 3. Update the customer master record to reflect the increased business, and reduced Available Credit. 'To compile the data from the invoice into summary values we loop to go thru each line item in the shopping cart and calculate running totals. We multiply a couple of column values together, once for each row in the sales line items table to arrive at a invoiceTotal and an invoice profit. In this procedure we use a command.executescalar query to retrieve the product's profit per unit for each line item sold, using that value to calculate profit per invoice line item, and total profit from the entire invoice. Dim decLineItemProfitPerUnit As Decimal 'used in the next loop Dim cmdGetProfitPerUnit As New SqlCommand("Select ProfitPerUnit FROM POSProducts WHERE ProductID = @p1", con) 'We run some calculations for each row in the shopping cart. The goal is to calculate some totals for the invoice, total revenue, total profit. For Each dr As DataRow In gdtLineItems.Rows With cmdGetProfitPerUnit.Parameters .Clear() ' we pull the parameter value from the product_ID column from the shopping cart. We are doing this inside the loop, executing once for each line item. .AddWithValue("@p1", dr("Product_ID")) End With 'For each of the products in the shopping cart run a query inside the SQLcommand to retrieve the profit per unit from the product table to calculate the profitability of the invoice. Try If con.State = ConnectionState.Closed Then con.Open() decLineItemProfitPerUnit = cmdGetProfitPerUnit.ExecuteScalar 'now that we retrieved the expected profit per unit we can calculate the profit per line item (the right hand side of the = sign) and add that to a global variable used to calculate the profit for the entire invoice. This same functionality could be performed in PowerBI or Excel using a DAX SUMX() iterator gdecInvoiceProfit += decLineItemProfitPerUnit * CInt(dr.Item("Quantity")) 'Next we calculate the line item revenue (the right hand side of the = sign) and add that value to a global variable used to calculate the total price of the invoice. gdecinvoiceTotal += CDec(dr("Sales_Price")) * CInt(dr("Quantity")) Catch ex As Exception Response.Write(ex.Message) Finally con.Close() End Try Next 'The next procedure compiles the rows of shopping cart line item data for the entire invoice into one row of summary information, metrics and dimensional attributes. The procedure compiles the information into one row and then saves that row to the sales table. When the invoice data is summarized, it is useful for reporting with the numeric columns able to be grouped by customer, year, month, product, etc. Call SaveToSummarySalesTable() 'Update the customer's record to reflect the recent invoice (update customer totals) Call UpdateCustomer() 'Update the inventory level for each product that was sold. Call ReduceStock() txtOutput.Visible = True 'your author's favorite color. Search the world's oceans and you will find this color. txtOutput.BackColor = Drawing.Color.Aquamarine 'Here a nicely formatted summary of the invoice and data management performed is helpful and reassuring to the program user. txtOutput.Text = "Customer " & View1ddlCustomer.SelectedItem.Text & " invoice for " & gdtLineItems.Rows.Count & " line items and " & pute("SUM (Quantity)", Nothing) & " units. " & vbNewLine & "Invoice total: " & FormatCurrency(gdecinvoiceTotal, 2) & " Invoice profit: " & gdecInvoiceProfit.ToString("C2") & " Gross Profit Margin " & FormatPercent(gdecInvoiceProfit / gdecinvoiceTotal, 1) & vbNewLine & vbNewLine txtOutput.Text &= " Transaction records saved to the Sales and Sales Detail tables" & vbNewLine & " Inventory reduced, and customer's financial account and sales metrics updated." 'Delete the data in the shopping cart to clear it out for the next invoice gdtLineItems.Rows.Clear() End Sub#End Region#Region "Save data To salesSummary table" Protected Sub SaveToSummarySalesTable() 'If you take a look at the data model the sales data is saved at both the detail and summary level to facilitate different reporting and data summarization processes. This procedure saves the summary version of the customer's sales transaction. You cannot see the individual items purchased in this table, rather totals fir number items, number units, revenue and profit. The summary measures can be read by PowerBi to produce basic business reports. 'The sales data saved in this table can later be grouped by customer, and/or yearmonth to total the a) profit generated, b) NumberItems purchased, c) number units sold, and d) total revenue. Those metrics can be also be easily grouped by year and month. Business activity can be aggregated by dimensions such a product or product category, and region. The database table we add a row to with this procedure enables analytics such as examining changes in breadth of sale (# of different products), number units moved through the supply chain and changes in supply chain velocity, total revenue, and total profit generated. These metrics can be analyzed by month and year, by product, or by customer. Examine the data model provided to consider the charting options. 'Here we INSERT a new row of data into a second sales related table (featherman.POSSales). The data management procedure gathers and summarizes the invoice data using four metrics. Here the power and straightforwardness of the SQLcommand is again demonstrated. SQL INSERT statements use the format INSERT tablename (columns names) VALUES (list of parameters). You just have to be careful and name each database column correctly, then make sure the parameters each refer to the correct database column. You also have to make the order of the columns and parameters match, so that the right data goes into the right column of the database. You can get quite creative when setting the values for the parameter, as demonstrated. Here global variables are passed in, columns of the shoppinng cart are tallied, and even a pute function is used. SQL Insert statements are run by many statistics programs, web or Internet based programming languages. Dim cmdInsertSalesSummmary As New SqlCommand("INSERT featherman.POSSales (Sale_ID, Customer_ID, Total_Sale, Invoice_Date, Paid, YearMonth, Profit, NumberItems, TotalUnits) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9)", con) With cmdInsertSalesSummmary.Parameters .Clear() .AddWithValue("@p1", gintNextInvoiceNumber) 'SaleID .AddWithValue("@p2", CInt(View1ddlCustomer.SelectedValue)) 'The CustomerID can be captured from the DDL selection. .AddWithValue("@p3", gdecinvoiceTotal) 'calculated in the loop above (we also could have used a pute). .AddWithValue("@p4", Now()) 'captured current system time/date stamp 'The next field is the Paid field which stores true/false values. The dataytpe that stores these true/false or 1/0 values using the datatype bit in the SQL Server database table. The 0 used below signifies the invoice is not paid yet. .AddWithValue("@p5", 0) 'To facilitate analytics and reporting we create a YearMonth column that holds values such as 202011 for November 2020. This YearMonth column is needed to create PowerBI line charts. .AddWithValue("@p6", (Now.Year * 100) + Now.Month) 'it is useful to record the profit generated by the invoice. This column can be used to identify the customers, months, or products that drive profitability (now and in the past). Profitability must be understood to ensure sustainability of business operations. .AddWithValue("@p7", gdecInvoiceProfit) 'it is useful to know the width of the shopping cart for the invoice (number of line items). Does the customer normally buy many different items on an invoice or just a few? Is the width of the purchase changing over time? .AddWithValue("@p8", gdtLineItems.Rows.Count) 'Supply chain manager types want to know the number of units (boxes) moving through the delivery system. Here the value for the TotalUnits sold on the invoice is calculated. .AddWithValue("@p9", pute("SUM (Quantity)", Nothing)) End With Try If con.State = ConnectionState.Closed Then con.Open() 'This next line of code(.executenonquery) runs the red INSERT SQL Statement creating a new row of data in the summary sales table. Business data belongs in databases where the data can be organized, and verified to be accurate. When the data is stored in database tables, then reporting and dashboard refresh can be automated and scheduled. cmdInsertSalesSummmary.ExecuteNonQuery() Catch ex As Exception Response.Write(ex.Message) Finally con.Close() End Try 'So you may be wondering why are we doing all this work to save the invoice data in summary format? Well we tie together the customer, date and with the sales data, into a nice summary of the sales invoice transaction. Also the database tables will grow in size and insightfulness as a system like this is used in business. The data gathered can be visualized in PowerBI for free. The business analyst searches for patterns in business operations and cycles, ultmately steering the company through seasonality and other changes. End Sub#End Region#Region "Update Customer after sale" Protected Sub UpdateCustomer() 'finally we update the customer's data, to reflect the increased business with them. We also reduce the customer's available credit. A suggested improvement to this portion of the program would be to enforce credit limits. Dim cmdUpdateCustomerInfo As New SqlCommand("UPDATE featherman.POSCustomers Set LastPurchase = @p1, NumberPurchases +=1, TotalSales += @p2, AvailableCredit -= @p3, ProfitGenerated += @p4, TotalUnits += @p5 WHERE Customer_ID = @p6", con) With cmdUpdateCustomerInfo.Parameters .Clear() .AddWithValue("@p1", Now) 'its funny that this function provides the best formatting for data storage .AddWithValue("@p2", gdecinvoiceTotal) 'increment the totalSales database column for customer .AddWithValue("@p3", gdecinvoiceTotal) 'reduce available credit for the customer .AddWithValue("@p4", gdecInvoiceProfit) 'increment profit generated by the customer. This is the most useful metric. 'the data table has the epic ability to total any of its columns. Here we total the Quantity field of the global shopping cart. This value is used to update the TotalUnits in the Customer master record. One way to evaluate business operation is supply chain movement and velocity .AddWithValue("@p5", pute("SUM (Quantity)", Nothing))'this is the customer selected in the list control. Here we update one customer's totals. .AddWithValue("@p6", CInt(View1ddlCustomer.SelectedValue)) End With Try 'you do not need to run each SQL INSERT or UPDATE SET() commands in its own procedure and its own try/catch. Here the usefulness of segmenting code into called codeblocks is demonstrated. It is helpful to break SQL database interaction into separate procedures (ie inserts, updates of different dimension tables - here products and customers). Doing so aids program organization and system troubleshooting during development. If con.State = ConnectionState.Closed Then con.Open() cmdUpdateCustomerInfo.ExecuteNonQuery() 'This next line calls a procedure to refresh the data reports shown on view #2 Call GetMetrics() Catch ex As Exception Response.Write(ex.Message) End Try End Sub#End Region#Region "View 1 - Reduce Stock" Private Sub ReduceStock() 'Here we loop the shopping cart, and reduce the stock level for each product sold on the invoice. This functionality can be in a separate procedure since the data for the parameters used in the UPDATE SET SQLcommand is in the global shopping cart table. Here we use a parameterized sql update command inside a loop that fires once for each line item on the invoice. Commands inside loops are a powerful way to ensure many rows of data get updated. Additional functionality needed is a check on what to reorder. Dim cmdUpdateProductsTable As New SqlCommand("UPDATE featherman.POSProducts Set StockQuantity -= @p1 WHERE ProductID = @p2", con) 'cool how you can use the += or -= code in a SQL statement 'The loop below reads, for each line item in the shopping cart and on the invoice, reduce the stock on hand for that product. Here is our useful friend the FOR EACH NEXT loop. We use the values in the global datatable from the Product_ID and Quantity columns to provide to the parameters used in the UPDATE SET command. We run the UPDATE SET command and then since we are in a loop, we return to the next row of the shopping cart datatable. So we have a SQL command running inside a loop. Remember this possibility to solve other problems. For Each dr As DataRow In gdtLineItems.Rows With cmdUpdateProductsTable.Parameters .Clear() .AddWithValue("@p1", dr.Item("Quantity")) .AddWithValue("@p2", dr.Item("Product_ID")) End With Try 'again with SQLCommands we manually open and close the conection to the cloud server If con.State = ConnectionState.Closed Then con.Open() 'This next line runs the red SQL UPDATE SET command. cmdUpdateProductsTable.ExecuteNonQuery() Catch ex As Exception Response.Write(ex.Message) Finally con.Close() End Try Next End Sub#End Region#Region "View #2: Contractor Page - show metrics, sales, sale details and pay into the credit account" 'The contractors page accepts payments and shows a lot of tables of data and metrics for the selected customer. Protected Sub View2btnDeposit_Click(sender As Object, e As EventArgs) Handles View2btnDeposit.Click 'this procedure allows the POS rep to accept payment on account. The code runs a standard SQL UPDATE SET command to update the customer's available credit column after the payment on account. Next after the update, the updated totals are shown in a gridview. The most important thing here is the WHERE statement that specifies that the available credit is being changed for just one customer. Dim cmdUpdateBalance As New SqlCommand("UPDATE featherman.POSCustomers SET AvailableCredit += @p1 WHERE Customer_ID = @p2", con) If txtDeposit.Text = Nothing OrElse CDec(txtDeposit.Text) < 0 OrElse View2ddlContractors.SelectedIndex <= 0 Then Response.Write("Select a contactor and type deposit amount in numbers") Exit Sub Response.Write(" ") Else End If With cmdUpdateBalance.Parameters .Clear() .AddWithValue("@p1", CDec(txtDeposit.Text)) .AddWithValue("@p2", View2ddlContractors.SelectedValue) End With Try If con.State = ConnectionState.Closed Then con.Open() cmdUpdateBalance.ExecuteNonQuery() 'now retrieve and display the row of data metrics for the one selected contractor, to verify the available credit is updated. Call GetContractor() Catch ex As Exception Response.Write(ex.Message) Finally con.Close() End Try End Sub ' Show the contractor analytics (recency - last transaction, frequency - #TA, Monetary - Revenue, Available credit, Profit generated by customer ' Show the invoices for the one customer ' Show all the line items from the selected customer's invoice Private Sub View2ddlContractors_SelectedIndexChanged(sender As Object, e As EventArgs) Handles View2ddlContractors.SelectedIndexChanged 'On the contractors page when a contractor is selected, different gridviews are displayed. The same type of code is running three times, pulling data from three different tables into gridviews for display, analysis, and to facilitate business decision-making. Each gridview is filtered to display only data for the one selected contractor, recent sales, list of sales, compilation of units purchased, and profitability measures. If View2ddlContractors.SelectedValue <= 0 Then Exit Sub Dim dtSalesOrder, dtSalesOrderDetails, dtProductsPurchased As New DataTable 'Here the list of invoices for the selected customer is retrieved Dim daGetInvoiceTotals As New SqlDataAdapter("SELECT Top 5 Sale_ID, FORMAT(Invoice_Date, 'd') as [Date], Total_Sale, Profit, Paid, NumberItems FROM POSSales WHERE Customer_ID = @p1 ORDER BY Sale_ID DESC", con) With daGetInvoiceTotals.SelectCommand.Parameters .Clear() .AddWithValue("@p1", View2ddlContractors.SelectedValue) End With 'this is the query to retrieve all the line items on the sales invoices for the one selected contractor Dim daGetLineItems As New SqlDataAdapter("SELECT Sale_ID as [Invoice #], Customer_ID as [Customer], Product_ID as [Product ID], Product_Name as [Name], Sales_Price as [Price], Quantity, FORMAT([Date] , 'MM.dd.yy') as [Invoice Date] FROM Featherman.POSSalesLineItems WHERE Customer_ID = @p1 ORDER BY Sale_ID DESC", con) With daGetLineItems.SelectCommand.Parameters .Clear() .AddWithValue("@p1", View2ddlContractors.SelectedValue) End With 'This is an example of a GROUP BY query which is beyond the scope of this class. The total units and revenue are summarized by product. We use a dataAdapter because we retrieve a dataset. Dim daTotalProducts As New SqlDataAdapter("SELECT [Product_ID], [Product_Name], SUM([Quantity]) as [Total Units], FORMAT(SUM([Quantity] * [Sales_Price]), 'N0') as [Revenue], FORMAT(SUM([Quantity] * [ProfitPerUnit]), 'N0') as [Profit Generated] FROM [featherman].[POSSalesLineItems] as LI INNER JOIN [featherman].[POSProducts] as p ON LI.Product_ID = p.ProductID WHERE Customer_ID = @p1 GROUP BY [Product_ID], [Product_Name] ORDER BY [Product_ID]", con) With daTotalProducts.SelectCommand.Parameters .Clear() .AddWithValue("@p1", View2ddlContractors.SelectedValue) End With 'clear out any data in the gridviews from the last customer, before we refill them. If dtSalesOrderDetails.Rows.Count > 0 Then dtSalesOrderDetails.Rows.Clear() If dtSalesOrder.Rows.Count > 0 Then dtSalesOrder.Rows.Clear() If dtProductsPurchased.Rows.Count > 0 Then dtProductsPurchased.Rows.Clear() Try 'here run the select statements created above. daGetLineItems.Fill(dtSalesOrderDetails) daGetInvoiceTotals.Fill(dtSalesOrder) daTotalProducts.Fill(dtProductsPurchased) With View2dgvOrdersForCustomer .DataSource = dtSalesOrderDetails .DataBind() .Visible = True End With With View2dgvInvoices .DataSource = dtSalesOrder .DataBind() .Visible = True End With With View2dgvProductUnits .DataSource = dtProductsPurchased .DataBind() .Visible = True End With Call GetContractor() 'this called procedure provides more data retrieval and was separated out just to add organization Catch ex As Exception Response.Write(ex.Message) End Try End Sub Protected Sub GetContractor() Dim dtDeposits As New DataTable 'Now show the updated customer account info. The FORMAT([Field name], function us useful. If you add a 'd', the field is formatted to a date, C0 the field is formatted to Currency with 0 decimal places, N0 the field is formatted to Numeric (just uses commas between the thousands)) with 0 decimal places. Rather than use the simple SELECT *, we a) specify the columns we want, b) define the formatting and c) the name for the column header (specified in the AS term). Dim daShowUpdatedContractor As New SqlDataAdapter("Select FORMAT([LastPurchase] , 'd') as [Last TA], [NumberPurchases] as [# TA], [TotalUnits], FORMAT([TotalSales], 'C0') as [$Revenue], FORMAT([TotalSales]/[NumberPurchases], 'N0') as [Avg$ale], FORMAT([AvailableCredit], 'N0') as [Credit], FORMAT([ProfitGenerated], 'N0') as [Profit Generated] FROM [Featherman_Analytics].[featherman].[POSCustomers] WHERE Customer_ID = @p1", con) With daShowUpdatedContractor.SelectCommand.Parameters .Clear() .AddWithValue("@p1", CInt(View2ddlContractors.SelectedValue)) End With Try daShowUpdatedContractor.Fill(dtDeposits) With View2dgvCustomerAnalytics .DataSource = dtDeposits .DataBind() .Visible = True End With Catch ex As Exception Response.Write(ex.Message) End Try End Sub#End Region#Region "View #3: Show Current Inventory" Private Sub LinkButton3_Click(sender As Object, e As EventArgs) Handles LinkButton3.Click 'the inventory view just shows the current inventory of the construction products MultiView1.ActiveViewIndex = 2 ShowInventory() End Sub Protected Sub View3ReceiveInventory_Click(sender As Object, e As EventArgs) Handles View3ReceiveInventory.Click 'here we reset each product's stockQuantity back to 2500 Dim cmdUpdateInventory As New SqlCommand("Update [featherman].[POSProducts] SET [StockQuantity] = 2500", con) Try If con.State = ConnectionState.Closed Then con.Open() cmdUpdateInventory.ExecuteNonQuery() Call ShowInventory() Catch ex As Exception Response.Write(ex.Message) Finally con.Close() End Try End Sub Private Sub ShowInventory() Dim dtProducts As New DataTable Dim dtProductAnalytics As New DataTable Dim daGetProducts As New SqlDataAdapter("SELECT [Category], [ProductID],[ProductName], [StockQuantity], [SalesPrice], ([SalesPrice] * [StockQuantity]) as [Stock Value], [ProductCost], [ProfitPerUnit] FROM featherman.POSProducts ORDER BY Category", con) 'now display the products table 'This next query is outside the scope of this introductory SQL Server Class, and is show here to encourage you to realize that a little SQL knowledge can bring data together into analytics using the same dataAdapter, datatable, and gridview combination. Data is pulled from two tables that are joined together, and summary totals for each product. The compiled data is useful to the operations manager. Dim daProductAnalytics As New SqlDataAdapter("SELECT [Category], [Product_ID], [ProductName], SUM([Quantity]) as [Total Units Sold], FORMAT(SUM([ProfitPerUnit] * [Quantity]), 'N0') as [Profit generated], FORMAT(SUM([SalesPrice] * [Quantity]), 'N0') as [Revenue Generated], FORMAT(SUM([ProfitPerUnit] * [Quantity])/ SUM([SalesPrice] * [Quantity]), 'P1') as [Profit Margin] FROM [featherman].[POSSalesLineItems] as li INNER JOIN [featherman].[POSProducts] as p ON p.ProductID = li.Product_ID GROUP BY [Category], [Product_ID], [ProductName]", con) Try daGetProducts.Fill(dtProducts) daProductAnalytics.Fill(dtProductAnalytics) View3dgvProducts.DataSource = dtProducts View3dgvProducts.DataBind() View3gvProductAnalytics.DataSource = dtProductAnalytics View3gvProductAnalytics.DataBind Catch ex As Exception Response.Write(ex.Message) End Try End Sub#End Region#Region "View #4 Show Customer table with metrics" Protected Sub LinkButton4_Click(sender As Object, e As EventArgs) Handles LinkButton4.Click MultiView1.ActiveViewIndex = 3 Dim CustomersTable As New DataTable Dim GetCustomerSummaryData As New SqlDataAdapter("Select [State], [CustomerName], FORMAT([LastPurchase] , 'd') as [Last TA], [NumberPurchases] as [# TA], [TotalUnits], FORMAT([TotalSales], 'C0') as [$Revenue], FORMAT([TotalSales]/[NumberPurchases], 'N0') as [Avg. $ale], FORMAT([ProfitGenerated], 'C0') as [Profit], FORMAT([ProfitGenerated]/[TotalSales], 'p1') as [Contribution Margin %], FORMAT([AvailableCredit], 'C0') as [Available Credit] FROM [Featherman_Analytics].[featherman].[POSCustomers] ORDER BY [State]", con) Try GetCustomerSummaryData.Fill(CustomersTable) gvCustomers.DataSource = CustomersTable gvCustomers.DataBind() Catch ex As Exception Response.Write(ex.Message) End Try End Sub#End Region#Region "Utility Code" 'Clear out the selections and shopping cart from the invoicing tab Protected Sub View1btnClear_Click(sender As Object, e As EventArgs) Handles View1btnClear.Click 'clear for new invoice. Include deleting the prior line items from the prior invoice (clearing the in-memory shopping cart table) View1chkVIP.Visible = False Label2.Visible = False txtOutput.Visible = False With gdtLineItems If .Rows.Count > 0 Then .Rows.Clear() End With 'binding gridview to an empty datatable clears the display View1dgvShoppingCart.DataSource = gdtLineItems View1dgvShoppingCart.DataBind() 'clear out the customer info If gdtInfoForOneCustomer.Rows.Count > 0 Then gdtInfoForOneCustomer.Rows.Clear() If gdtMetricsFor1Contractor.Rows.Count > 0 Then gdtMetricsFor1Contractor.Rows.Clear() If gdtProductsDetails.Rows.Count > 0 Then gdtProductsDetails.Rows.Clear() 'hide the gridviews to clear out the form View1dgvDataFor1Customer.Visible = False View1dgvDataFor1CustomerMetrics.Visible = False 'reset the line item # for the next invoice. This is an important variable that handles row numbering so we add rows to the shopping cart table correctly gintLineNumber = 0 View1ddlRemove.Items.Clear() 'clear out the selections made in the invoicing tab View1ddlCustomer.SelectedIndex = -1 View1ddlProducts.SelectedIndex = -1 View1rblProductCategory.SelectedIndex = -1 txtQty.Text = Nothing txtOutput.Text = Nothing End Sub 'linkbutton navigation from view to view Private Sub LinkButton1_Click(sender As Object, e As EventArgs) Handles LinkButton1.Click MultiView1.ActiveViewIndex = 0 End Sub Private Sub LinkButton2_Click(sender As Object, e As EventArgs) Handles LinkButton2.Click MultiView1.ActiveViewIndex = 1 'clear off the data tables from the screen when switching views View2dgvOrdersForCustomer.Visible = False View2dgvCustomerAnalytics.Visible = False View2dgvInvoices.Visible = False View2dgvProductUnits.Visible = False View2ddlContractors.SelectedIndex = -1 End Sub Protected Sub View2btnClear_Click(sender As Object, e As EventArgs) Handles View2btnClear.Click 'clear the deposits screen txtDeposit.Text = Nothing View2ddlContractors.SelectedIndex = -1 View2dgvCustomerAnalytics.Visible = False View2dgvOrdersForCustomer.Visible = False End Sub#End Region End Class ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download