Washington State University



Shopping Cart POS System for a Construction Hardware Store? Featherman webpage has three views, shopping cart, contractor analysis and payments, and product catalog and inventory. This web page is the most advanced by far and solves the problem of creating an in-memory shopping cart, and pulls together (and leverages) data from three concepts (sales info, customer info and product info). In your final project you may need to move from two tables (customers and sales) to three (as shown here adding products). You may choose to significantly edit this webpage for your final project, or just learn from it to improve your own projects. Your final project should use four database table, not necessarily the same tables used here. Solve a business processing problem in your final project, something that requires planning and coordination of SQL database tables rather than simple INSERT/UPDATES of tables individually. In this program the invoice transaction drives updates of the products and customers table using UPDATE SET commands. The SQL GROUP BY and FORMAT commands demonstrated here are helpful but you do not need to incorporate them.The first view allows the POS employee of a construction hardware supply to select a vendor, see some analytics for them and see the available credit for the contractor. Next the POS allows selection of products into and out of a shopping cart, and when the invoice is finalized the customer account is updated, the inventory is reduced, and the sales are save in two ways (detail level – each line item on the invoice, and summary level – overall invoice totals information. The second view allows a manager or analyst to review some analytics about the selected customer, and see invoice data (both detailed and summary) for the selected customer.The third view shows the product catalog for the construction hardware store. The profit generated by the sale of each unit is displayed. The current inventory levels are displayed. The invoicing system only displays products currently in stock, so view 3 also includes a procedure to replenish each inventory level to 2500 units.View #1: Shopping cart invoicing systemView #2: Contractor Analysis and PaymentsView #3: Product Catalog and Inventory895351524000013843015240000The database table schemas for the four tables are shown below, followed by the data model that connects these tables.-1905000This customers table is used to keep demographic information about the contractors which are the customers of the construction hardware store.This is a hybrid dimension and analytics table. Dimensions useful for reporting include customer ID/name, city/state (location).This table also maintains measures and running totals very useful for customer metrics, comparison, and analysis. The metrics use the RFM (recency, frequency, monetary) model. Columns of analytics include last purchase (recency), numberpurchases (frequency) and totalsales/profitgenerated (monetary). The analytics columns in this table get updated after every sale. In the next module we use PowerBI to create column and area charts to visualize the metrics.1270000This products table that holds the product ID, name, descriptions, inventory level, prices, and other important information that represent the products catalog. The inventory balance gets reduced after each sale for each product sold. View three on the webpage allows the program user to see current inventory levels.An interesting column is the profit per unit column, which enables the calculation of the amount of profit generated by each sales invoice. This profit for the invoice is used to update the customers profit generated column. -1905190500This table stores a summary of each invoice in one row. This summary nature of the transaction record makes this table the source for many reports and charts.1270190500This table stores the sales details from the invoice. One row is saved in this table for each item sold on the invoice. This table is the datasource for product and inventory movement based reports.left11938000The image on the right is the data model for the construction hardware store website designed and implemented with this module. These four tables were organized into the following data model using the database diagrams feature of SQL Server Management Studio (SSMS). 447675080835500One of the most important learning objectives of this module is that you need to start web development projects with the plan to save data. As your projects grow in complexity, the planning process becomes more important. The data being saved to tables must be accurate across all tables. For example a new sale must be to an existing customer (no cash sales in this scenario), updates the customers table. The prudent developer makes their data model first, then plans out the updates needed across tables, BEFORE BEGINNING TO CODE.Here is the list of primary and foreign key constraints:1. Customers are assigned a unique Customer_ID number which is the primary key for the POSCustomers table. When adding a new invoice to the POSSales and POSSalesLineItems tables the customer ID must match pre-existing customers in the customers table.2. The POSSalesLineItems represent the products in the shopping cart that are getting purchased. The productID’s in the shopping cart must match products from the POSProducts table.OK let’s get into the code!!'This program functions as a POS system where each invoice can have many line items. This program uses the POSSales table for storage of invoice summary data (the grand total, date, customerID, etc.) and a POSSalesLineItems table to show 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, products.'Do not use this program for your final project, rather use it to get ideas for your final project. The scenario is that a construction supplies warehouse receives money from general contractors in large sums (ie $10,000) and then 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.)'some improvements include:'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 wholesale reorders of inventory is needed.'3. The invoice form could be made nicer '4. More leeway is needed to assign discountsThis program has three views View #1 – needs to have customer information retrieved and displayed, products are made available for selection. The program user adds products to the shopping cart (and has a facility to remove items from the shopping cart). After items are added to the shopping cart (in-memory datatable) they complete the sale, save all data for the overall invoice and the line items as well, update customer data and update product inventory levels for the items that were sold.View #2 – allows the deposit of more money to the customer account to increase the available credit. Also different tables of data are displayed related to the customer a) list of invoices and some summary information, b) list of line items which displays the products purchased and their quantities, and c) some summary sales metrics for the customerView #3 – shows the products for sale, their prices and inventory levels.Imports System.DataImports System.Data.SqlClientPartial Class NewSale Inherits System.Web.UI.Page#Region "con" Public Shared con As New SqlConnection("Data Source=cb-ot-devst03.ad.wsu.edu;Initial Catalog=Featherman_Analytics;Persist Security Info=True;User ID=featherman;Password=Analytics123!@#")#End Region 'this next dataadapter is used to both fetch the schema of a table to copy into a virtual datatable and also to push back inserted rows back to the sql database. We need an in-memory data table for the invoice line items. While we could create the in-mery data table and add columns, we will rather use a functionality of the dataAdapter (pulling the schema from the database table and assigning it to a datatable. Public Shared daPOSSalesLineItems As New SqlDataAdapter("SELECT * FROM featherman.POSSalesLineItems", con) 'this next line creates a commandbuilder that will help its best friend dataadapter to do its job of inserting the new row into the db. The lineitems for the invoice are stored in a virtual table (shown on the webpage) - see the page_init for the .fillschema code. The commandbuilder helps the dataAdapter, making possible a one-line of code to save the records to the database. The line later is dataAdapter.UPDATE(sourcedatatable) Public Shared cbSLI As New SqlCommandBuilder(daPOSSalesLineItems) 'these data tables are all public page level so that different procedures can access the 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 integer variable here is used to hold a retrieved invoice # so we know in the in-memory datatable of line items, what the next invoice # is Public Shared gintNextInvoiceNumber As Integer 'the integer variable here keeps track of sales order line items for the in-memory datatable that is displayed on-screen. So one order can have many line items. Public Shared gintLineNumber As Integer Public Shared gdecInvoiceProfit As Decimal#Region "Page Init" Protected Sub Page_Init(sender As Object, e As EventArgs) Handles Me.Init Label1.Text = Today.Date View1ddlRemove.Items.Insert(0, "Select item to remove from invoice") ' we will need this later '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 POSPOSSalesLineItems table. We cannot rely on the auto-increment property of teh SQL Serer database table since we are working with an in-memory datatable. So we need to retrieve the highest invoice # and then increment that manually. Dim cmdGetInvoiceNumber As New SqlCommand("SELECT MAX(Sale_ID) FROM [featherman].[POSSalesLineItems]", con) Dim dtCustomers As New DataTable 'this datatable array is used to store the customer info for the ddl Try ' fill some dropdownlists (ddl) 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 add rows of invoice line items into a virtual table named gdtLineItems. We can write lots of lines of code to create the table and add rows and columns to it....but here is an easier way - we copy the schema from the sql server table into an in-memory datatable (array). 'Here we copy the table schema for the lineitems on our invoice into the in-table memory which will be our shopping cart. We do this to set the column names and data types. So we are using now a strongly typed in-memory datatable which is far superior to an array that has for example all string (textual info). This is the magic daPOSSalesLineItems.FillSchema(gdtLineItems, SchemaType.Mapped) 'here we retrieve the next invoice # in sequence, so that we can insert it into the in-memory datatable that will be displayed on-screen as the customer is adding line items to their invoice. sqlcommand.executescalar is used to retrieve one value. .executenonquery is used when you are running an insert, update or delete statement. Try If con.State = ConnectionState.Closed Then con.Open() gintNextInvoiceNumber = 0 'reset the invoice # for the next invoice gintNextInvoiceNumber = cmdGetInvoiceNumber.ExecuteScalar gintNextInvoiceNumber += 1 Catch ex As Exception Response.Write(ex.Message) Finally con.Close() End Try End Sub#End Region#Region "View #1: Setting up the webpage for the shopping experience" #Region "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) - then retrieve and show some more information about that customer. We also call another GetMetrics() procedure that compiles more metrics for one customer. These metrics are shown at the top of the invoice, so that the sales rep knows whom is standing in front of them (could be a VIP or a customer with limited credit). IF the invoise with shopping cart was printed then its a nice feature to have the customer address formatted nicely. Dim dtMetricsFor1Contractor As New DataTable 'so pull a bunch of columns from the SQL Server table for the ONE elected customer. Copy the data into an in-memory data table and displayed in a gridview 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) 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 With gdtInfoForOneCustomer If .Rows.Count > 0 Then .Rows.Clear() End With Try 'this is where the red sql SELECT statement is run and the retrieved row of data copied into this datatable daGetDimensionInfoForCustomer.Fill(gdtInfoForOneCustomer) If gdtInfoForOneCustomer.Rows(0).Item("VIP") = True Then 'change the webform to display a VIP checkbox if the customer is VIP View1chkVIP.Checked = True View1chkVIP.Visible = True Label2.Visible = True Else View1chkVIP.Visible = False Label2.Visible = False End If With View1dgvDataFor1Customer .DataSource = gdtInfoForOneCustomer .DataBind() .Visible = True End With Call GetMetrics() 'call and run anothe rprocedure to get more customer metrics View1dgvDataFor1CustomerMetrics.Visible = True Catch ex As Exception Response.Write(ex.Message) End Try End Sub#End Region#Region "Populate DDL with construction products for selected product category" Private Sub View1rblProductCategory_SelectedIndexChanged(sender As Object, e As EventArgs) Handles View1rblProductCategory.SelectedIndexChanged 'This construction supply store has products 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. '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). Rather than have one DDL with all the products that are in inventory, this procedure provides a filtered list to the products DDL keeping the options limited and speeding up the transaction. A dataAdapter runs a SQL SELECT command that retrieves three columns into a global datatable. Two of the columns are used 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 data table. 'FYI - this system does not include an ordering page yet to replace products that are low in stock or not in stock. Dim daGetProducts As New SqlDataAdapter("Select ProductID, ProductName, SalesPrice FROM featherman.POSProducts WHERE Category =@p1 AND [StockQuantity] > 0", con) With daGetProducts.SelectCommand.Parameters .Clear() .AddWithValue("@p1", View1rblProductCategory.SelectedItem.Text) End With 'clear out any prior products info 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#End Region#Region "View #1: Add line items to shopping cart" Protected Sub View1BtnAddProduct_Click(sender As Object, e As EventArgs) Handles View1BtnAddProduct.Click 'here we add new line items to the in-memory datatable that stores the line items on the invoice (aka the shopping cart). 'In the another procedure we will save the data in the shopping cart, all the line items at the same time. 'stop the procedure if a product and quantity were not selected. If View1ddlProducts.SelectedIndex < 0 OrElse CInt(txtQty.Text) < 0 Then txtOutput.Text = "Select a product and numeric quantity" Exit Sub End If gintLineNumber += 1 'set the line item# for the next item placed into the shopping cart. This variable gets zero'd out when you clear the invoice from the form. 'now we create a new line item for teh 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 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 saved back to the database in one sweet dataadapter.update operation. Dim dr As DataRow = gdtLineItems.NewRow() 'above we created the new row which knows the schema that is needed, 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 'this is pretty tricky. The products View1rblProductCategory_SelectedIndexChanged procedure pulled product info into a datatable for the selected product category. So we can retrieve the sales price from that in-meory datatable, as we did before by leveraging the fact that the items are in a list control, here a dropdown list. We identify the row# that has the sales price we are needing by using dropdownlist.selectedindex value. We have to subtract 1 from the .selectedindex since we added a textual prompt at row 0. 'So rather than retrieving just 2 columns when fetching the data to fill your dropdown lists in a page_init procedre, you can retrieve some other columns into a datatable that you might need to lookup and use in a later transaction. Another way to lookup a value from a database table is to use a SQLCommand and some try/catch code such as decPrice = cmdGetPrice.ExecuteScalar, which means run a SQL statement to retrieve one value from a database table (not a column of values or table of values). dr("Sales_Price") = gdtProductsDetails.Rows(View1ddlProducts.SelectedIndex - 1).Item("SalesPrice") 'here we limit the number units on one line item to 100 If Convert.ToInt16(txtQty.Text) > 100 Then dr("Quantity") = 100 Else dr("Quantity") = Convert.ToInt16(txtQty.Text) End If dr("Date") = Now() 'now that we have our new row all filled in, we append the row to the in memory datatable. Later we will push these rows of line items to the database. Here we are just buiding the shopping cart which is a bunch 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 progam user, so that hey 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 'A dropdownlist has the items that are added to the invoice so we can use the rows.removeat function to remove the same index # from the datatable. Becase the dropdownlist has a new 0-indexed item (the prompt to select an item to remove it) we have to subtract one from the index number selected in the DDL. 'After we remove a row from the dataTable whe we rebind the gridview, so that the deleted row is removed from display. If View1ddlRemove.SelectedIndex <= 0 Then Exit Sub ' only run the next code if a valid selection was made in the dropdown list. 'here is the code that removes one line item gdtLineItems.Rows.RemoveAt(View1ddlRemove.SelectedIndex - 1) 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 all data, update customer data and product levels for the items that sold.." Protected Sub View1btnRecordSale_Click(sender As Object, e As EventArgs) Handles View1btnRecordSale.Click Dim decinvoiceTotal As Decimal gdecInvoiceProfit = 0 'used later in a loop to calculate the amount of profit generated by an invoice. So we better zero this value out. '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. The unsung hero again is the commandbuilder which writes the insert/update/delete statements for us (here just the insert statements are needed). The rows from the in-memory shopping cart datatable are inserted into the sql table pretty easily, because they have the exact same database structure. Mentioned elsewhere we leveraged the dataAdapter.Fillschema code in a page_init procedure to copy a database table's schema into a web-server's memory using an datatable. This is very efficient code from Microsoft. So we created the shopping cart table easily, pull together sales line items in an invoice by accessign different data columns from 4 different SQL database tables, we added and displayed rows easily to teh on-screen blue shopping cart. 'This procedure now can easily save the sales line items into new rows of the SQL database table. There are just a minimal # of lines of code which makes it efficient. The best thing about learning web-server array tables, is that they work so effortlessly with the SQLdataAdapter and the .Update function. The .update command can actually push back to the SQL database a table full of edited data with new row inserts, data row updates and delete row statements. Since we have NEW records here, a parameterized SQL INSERT statement is generated for us. Thank you commandbuilder! If gdtLineItems.Rows.Count = 0 Then 'stop this procedure if no invoice is ready to be saved into the database. Response.Write("No records to save") Exit Sub End If Try daPOSSalesLineItems.Update(gdtLineItems) ' all the shopping cart rows of data are saved to the database now, our job is done here. The next task is to generate and save the invoice summary to a different sales table. We use a second list of columns in a second table to combine and present the data in a more summary level, which is useful for reporting. Both of these tables can be analyzed, described, and summarized using drill-down charts. In the next module you will learn the free PowerBI software. You can create and publish a nice dashboard and set of reports. 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. Insert a new row into a second database table featherman.POSSales. This next data management procedure gathers customer data, and summarizes the invoice data using two financial from View #1. So a second SQL database table was designed and implemented in the Featherman_Analytics database related to sales. We take control of the SQL code to write a specific procedure to assign the webpage values to the parameters of the SQL INSERT command. '2. Reduce the stock level in the SQL products table by deducting the items from the blue shopping cart. This code runs an UPDATE SET SQL command over and over inside a loop, reducing the inventory for each line item sold. The concept of running an UPDATE SET SQL statement inside a loop is a great leap forward. '3. The customer summary data must also be updated after the transaction, to record the increased business, and reduced Available Credit.‘WHEN YOU HAVE COMPLEX CALCULATIONS YOU USE A INSERT SHOW BELOW. IF JUST UPDATING SIMPLE DATA USE THE DATAADAPTER APPROACH. Dim cmdInsertSalesSummmary As New SqlCommand("INSERT featherman.POSSales (Sale_ID, Customer_ID, Total_Sale, Invoice_Date, State, Paid, Year, Profit, NumberItems) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9)", con) 'To facilitate later analytics by customer and date, we will add a row of data to a sales table (POSSales in the database) to summarize and recap the sales transaction. While we save the total for the transaction we also include the customer ID and date. We could use a more tricky pute command to calculate the invoice total, but here we can easily use a for each loop. 'we are running a loop to go thru each line item in the shopping cart to total and calculate some measuers. We multiplying 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 need to retrieve the profit per unit for each line item sold and use the profit per unit to calculate profit per invoice line item. Since this code is inside a loop we can calculate the total invoice profit. Here we show how to run a SQL SELECT statement using the .ExecuteScalar method, which returns ONE value that is immediately assigned to a local variable and used in subsequent calculations. 'Sometimes when you are developing you eralize you need to retrieve a value from the database. This is when you use a SQLCommand with an executescalar method. We could also have added another column from the products table (the profit per unit) when we ran the page_init procedure. Then we would have the value in an in-memory array to retrieve. Very often in larger programs you retrieve values into arrays and then use them later in calculations. This speeds up processing and calculations.Dim intProductID As Integer Dim decProfitPerUnit As Decimal Dim cmdGetProfitPerUnit As New SqlCommand("SELECT ProfitPerUnit FROM POSProducts WHERE ProductID = @p1", con) For Each dr As DataRow In gdtLineItems.Rows 'remember we are doing these calculations over and over once for each line item in the shopping cart. The goal is to calculate some totals for the invoice, total revenue, total profit. THis is where you could calculate other invoice metrics, suh as total units. With cmdGetProfitPerUnit.Parameters .Clear() .AddWithValue("@p1", intProductID) End With 'here we retrieve the profit per unit using the SQL Command's executescalar method and use that number in a calculation. Try If con.State = ConnectionState.Closed Then con.Open() decProfitPerUnit = cmdGetProfitPerUnit.ExecuteScalar intProductID = dr.Item("Product_ID") decinvoiceTotal += CDec(dr("Sales_Price")) * CInt(dr("Quantity")) gdecInvoiceProfit += decProfitPerUnit * CInt(dr.Item("Quantity")) Catch ex As Exception Response.Write(ex.Message) Finally con.Close() End Try Next 'So you may be wondering why are we doing all this work to save the invoice data summary? Well we tie together the customer, date and geographic data with the sales data, into a nice summary of the sales invoice transaction. The summary of the invoice is stored in a row in a datatable and is compiled when performing other analytics. Sales analytics for individual products is performed on the sales line items table. Sales analytics included are the # of line items (width of shopping cart), the profit generated by the sales invoice, and the invoice total. With cmdInsertSalesSummmary.Parameters .Clear() .AddWithValue("@p1", gintNextInvoiceNumber) .AddWithValue("@p2", CInt(View1ddlCustomer.SelectedValue)) .AddWithValue("@p3", decinvoiceTotal) .AddWithValue("@p4", Now()) .AddWithValue("@p5", gdtInfoForOneCustomer.Rows(0).Item("State")) '0 used below to signify the invoice is not paid yet. The scenario is that we built a webapp POS system that supports a construction supply store where the client has corpoprate contracts and pre-paid credit. The employees of the construction companies pick up supplies, receive a detailed invoice and summary of account, and head out to their job site. Periodically the controller of the construction companies write checks or transfer funds to pay off the specific invoices. When the invoices are initially saved they are not paid so the checkbox will be empty. A new procedure is needed to pay off invoices, so that the checkbox displayed on view #2 will be checked. (this functionality forthcoming). The available credit balance would also need to be replenished when invoices are paid, creating store credit. .AddWithValue("@p6", 0) .AddWithValue("@p7", Now.Year) 'It is useful to save the year into its own column to facilitate later reporting. We could have added 5 more date/time based columns such as day of week, day of month, time of day, month of year, etc, but we can also create this functionality when we retrieve the data from the database. .AddWithValue("@p8", gdecInvoiceProfit) 'it is useful to know the profit generated by the invoice to help identify the customers that are driving profitability (now and in the past) .AddWithValue("@p9", gdtLineItems.Rows.Count) 'it is useful to know the width of the shopping cart for the invoice. Does the customer buy many different items or just a few? End With Try 'SQLCommands require you to open and close the connection with code. A dataAdapter takes care of this for you. If con.State = ConnectionState.Closed Then con.Open() 'This is the line of code that pushes the red SQL Statement and all the parameter values back to the SQL Server database, creating a new row of data in a table. Business data is safely stored in databases. Perhaps most importantly is that the data is organized, and verified to be accurate. When the data is stored in database tables, then reporting and dashboards can be refreshed automatically. cmdInsertSalesSummmary.ExecuteNonQuery() txtOutput.Visible = True txtOutput.BackColor = Drawing.Color.Aquamarine 'your author's favorite color. Search the world's oceans and you will find this color. 'Here a nicely formatted summary of the invoice and data management performed is helpful and reassuring to the program user. txtOutput.Text = View1ddlCustomer.SelectedItem.Text & " purchased " & gdtLineItems.Rows.Count & " items totalling: " & FormatCurrency(decinvoiceTotal, 2) & vbNewLine & "Invoice profit: " & gdecInvoiceProfit.ToString("C2") & vbNewLine & "Transaction records saved to the Sales and Sales Detail tables. Inventory reduced." & vbNewLine & "Customer's financial account and sales metrics were also updated." Catch ex As Exception Response.Write(ex.Message) Finally con.Close() End Try 'now we call another sub procedure that is used to reduce the stock for the items that were in the shopping cart. Call ReduceStock() 'finally we update the customer's data, to reflect the increased business with them. We also reduce the customer's available credit. There needs to be more intelligence added to the transaction to ensure a customer is not overspending their credit limit (future program enhancement). Dim cmdUpdateCustomerInfo As New SqlCommand("UPDATE featherman.POSCustomers Set LastPurchase = @p1, NumberPurchases +=1, TotalSales += @p2, AvailableCredit -= @p3, ProfitGenerated += @p4 WHERE Customer_ID = @p5", con) With cmdUpdateCustomerInfo.Parameters .Clear() .AddWithValue("@p1", Now) .AddWithValue("@p2", decinvoiceTotal) 'increment total sales for customer .AddWithValue("@p3", decinvoiceTotal) 'reduce available credit for customer .AddWithValue("@p4", gdecInvoiceProfit) 'increment profit generated by the customer. .AddWithValue("@p5", CInt(View1ddlCustomer.SelectedValue)) 'this is the customer selected in the list control End With Try 'you do not need to run each command in its own try/catch. that was done here to add organization and enhance learning. If con.State = ConnectionState.Closed Then con.Open() cmdUpdateCustomerInfo.ExecuteNonQuery() Call GetMetrics() Catch ex As Exception Response.Write(ex.Message) End Try 'Just in case the user presses the button twice, we delete the rows of the table so that they cannont be added again. gdtLineItems.Rows.Clear() End Sub#End Region#Region "View 1 - Reduce Stock" Private Sub ReduceStock() 'Here we reduce the stock in the Products table for each product sold on the invoice. This can be a separate procedure since the data for the parameters is in a global table. Check this out! its a sql update command inside a loop, that fires once for each line item on the invoice. This should give you some ideas. This should be followed up with a check on what to reorder...that functionality is needed. 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 'for each of the line items 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 set the two parameter values for the UPDATE SET command by retrieving values from the first row of the shopping cart data table. We run the UPDATE SET command and then since we are in a loop, we return to the second row (and each successive row) of the shopping cart datatable which is the second line item on the invoice. We retrieve the two parameter values from teh second row and again run the SQL UPDATE SET command. 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() cmdUpdateProductsTable.ExecuteNonQuery() Catch ex As Exception Response.Write(ex.Message) Finally con.Close() End Try Next End Sub#End Region#Region "View #1: Get a Row of Metrics For the Selected Customer" Protected Sub GetMetrics() 'Here an effort is made to FORMAT() the columns of data that are retrieved to display the customer sales metrics. Can you think of other columns of metrics to add? You should be able to identify several more if you are a student of business. Dim daGetMetricsForCustomer As New SqlDataAdapter("SELECT FORMAT([LastPurchase] , 'd') as [Last TA], [NumberPurchases] as [# TA], FORMAT([TotalSales], 'C0') as [$Revenue], FORMAT([TotalSales]/[NumberPurchases], 'N0') as [Avg$ale], FORMAT([ProfitGenerated], 'C0') as [Profit], FORMAT([AvailableCredit], 'C0') as [Available Credit] FROM [Featherman_Analytics].[featherman].[POSCustomers] WHERE Customer_ID = @p1", con) With gdtMetricsFor1Contractor.Rows 'clear out any prior contractor information If .Count > 0 Then .Clear() End With With daGetMetricsForCustomer.SelectCommand.Parameters 'capture the contractor_ID number and feed that to the SQL Select statement as the filter. .Clear() .AddWithValue("@p1", CInt(View1ddlCustomer.SelectedValue)) End With Try 'now run the red SQL Select statement to retrieve some of the columns for the selected customer, and display the data in a gridview daGetMetricsForCustomer.Fill(gdtMetricsFor1Contractor) View1dgvDataFor1CustomerMetrics.DataSource = gdtMetricsFor1Contractor View1dgvDataFor1CustomerMetrics.DataBind() Catch ex As Exception Response.Write(ex.Message) End Try End Sub#End Region#End Region#Region "View #2: Contractor Page - Pay into account and show metrics, sales, and details" 'The contractors page accepts payments and shows a lot of tables of data and metrics for the selected customer. 266700000 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 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 SubPrivate 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 business decision-making. Each gridview is filtered to display only data for the one selected contractor. This procedure is actually repetitive running similar dataAdapter.Fill(datatable) commands. You can put many of these .Fill commands in one procedure or each .Fill in its own procedure that are called by another ‘parent’ procedure. You can do whatever is easier to maintain. When you start to have too many line of code however, its best to segment and compartmentalize the code. However don’t mix dataAdapter.Fill commands with sqlcommand.executenonquery commands they are best in separate procedure. Dim dtSalesOrder, dtSalesOrderDetails, dtProductsPurchased As New DataTable Dim daGetInvoiceTotals As New SqlDataAdapter("SELECT 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] , 'd') as [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 the query to retrieve all the line items on the sales invoices for the one selected contractor Dim daTotalProducts As New SqlDataAdapter("SELECT [Product_ID], [Product_Name], SUM([Quantity]) as [Total Units Purchased] FROM [featherman].[POSSalesLineItems] 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 from the last customer 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 2 select statements 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 is more of the same 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 data, 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], FORMAT([TotalSales], 'C0') as [$Revenue], FORMAT([TotalSales]/[NumberPurchases], 'N0') as [Avg$ale], FORMAT([AvailableCredit], 'C0') as [Credit], FORMAT([ProfitGenerated], 'C0') 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 View2dgvDeposits .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"9525015557500 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 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 daGetProducts As New SqlDataAdapter("SELECT * FROM featherman.POSProducts ORDER BY Category", con) Try daGetProducts.Fill(dtProducts) View3dgvProducts.DataSource = dtProducts View3dgvProducts.DataBind() Catch ex As Exception Response.Write(ex.Message) End Try End Sub#End Region '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 View2dgvDeposits.Visible = False 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 View2dgvDeposits.Visible = False View2dgvOrdersForCustomer.Visible = False End Sub#End RegionEnd Class ................
................

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

Google Online Preview   Download