Washington State University



Saving Data to a Database, Updating Summary Table and a Peek at Some Analytics? – Featherman 0381000'This version of the program is step 2 of the development task. Step 1 was to create the database tables with default values and correct data types for our program needs.So use SMSS to create SQL Server database tables for the transaction data (in data warehouse terms called the fact table). Each row is storing data about one employee’s shift for a local construction company called Svenson’s Construction. A second table is also made to store the running totals for each labor grade category. The SQL Server tables are similar to 'This program demonstrates two methodologies to produce a compiled report of total labor by labor category. The first you must learn, disregard page 11 it is not required.'Methodology 1. Here a dimension table similar to our prior array is used to store data in rows that are updated after each labor record is recorded and saved. This is the methodology you would use for a bank account app, you want the account balance updated immediately after the transaction.So the overall procedure is INSERT THEN UPDATE. a) transfer one row of data from the webpage to the SQL server database table. You use the dataAdapter.Update procedure to copy/insert/save the row into the database. 'b) update the relevant row in the summary table, which is similar to our updating a summary level array in a prior module. Same-same, but different.'INSERT THEN UPDATE means save a transaction record then update a summary record. Inventory management systems also use this approach. . This type of update is at the heart of business programming for transactions and to reflect changes in status. Even a simple sales transaction entails several sub-transactions such as updating the customer and inventory master records, which could also kick-off inventory purchase or movement processes.Another way to produce compiled summary reports is to use SQL GROUP BY() and PIVOT() queries. Examples are shown on page 11. You are not required to learn these for your current class, however please do take a look, and realize that the red SQL statements are compiling the data. In this second methodology of creating compiled, summary data, you do not have a summary table that gets updated after each transaction record is saved. Rather when you want a compiled report (here summary data in a gridview) you compile the data at that time. In summary, this methodology of SQL analytics is outside the scope of the class and not on any exam, however is shown here to expand your understanding of how transaction systems and analytics are integrated.'This program again saves rows of transaction data to a SQL Server table (PayrollRecords), and then takes the labor hours & grosspay numbers and uses them to update a second summary level SQL Server table (PayrollTotals) after each labor transaction. In your final project you should be updating many different tables after a transaction. Again after a sales transaction record is saved, you may want to also update the inventory table, customers table, orders table and accounting records as well. Similarly after a production run the inventory levels should increase and machine, employee and workgroup labor performance will be updated.'This construction payroll program would be helpful to construction manager or project manager (you could also swap out the labor categories and think of another scenario). A likely extension to this project is to have different projects and total the labor for different projects. You could also extend the project management approach and create a budget for a project and the total cost of each labor grade budgeted for the project. You could compare actual to budget and give a project manager a status program such as comparing actual spent to budgeted amount, and you could also add a column that showed how much of the job is completed. These upgrades would enable a manager to say something like "$7,000 has been spent on electricians so far. This is half the budget for electricians and the job is only 30% completed, why is that?" The hope is that problems like these can be discovered quickly enough so that corrective action is possible. The manager could perhaps renegotiate the contract to receive more budget, somehow speed up the work, shift work to part-timers, or change the job. Status reports allow managers to make course corrections (like steering a ship into the wind) to reduce problems and improve operations. Reports shown in this module are foundational to your MIS learning and to business management.Shown next is the table schema for the transaction table, where the rows of labor records are recorded.left1606550034671001524000This program also updates a summary level database table named payrollTotals0190500Imports System.DataImports System.Data.SqlClientPartial Class PayrollwithTimespan3 Inherits System.Web.UI.Page 'This app saves the rows of transaction data (the labor records), and updates running summary totals in SQL Server tables stored in the cloud somewhere over the rainbow (Pullman, WA). So this webpage is connecting to two database tables to save data and also generate some management status reports. The dataAdapter runs SQL statements (the red code) over the connection to the database that you specify. Here is an example connectionstring, build your own using the credentials provided by your professor.Public Shared con?As?New?SqlConnection("Data Source=cb-ot-devst04.ad.wsu.edu; Initial Catalog=MF01YourNetworkID; Persist Security Info=True; User ID=?Network ID; Password=?asdf34"?) For the SQL Server password refer to the class website. 'Here is the array that we save transaction records to. We create it public shared so it can be referenced in different procedures. Public Shared gdtPayrollRecords As New DataTable 'The dataAdapter (DA) created next has several uses in this project. '1) The dataAdapter has has .fillschema method to retrieve the schema from specified database table and copy it into the webpage’s datatable array when the program starts (so you do not have to add the columns one at a time as before). Copying the schema to the webpage’s datatable array lets the datatable.newrow() command know what columns the row needs to have. So in the procedure to add a new row to the datatable and collect the webpage entered data into the columns for the blank row, that new row already knows the columns and their names, datatypes, restrictions and default values!! This facilitates data accuracy, meaning the right data needs to be stored to the columns. '2) When we want to save a row of data from the webpage to the database, use the DA's UPDATE method to save the new row of data back to the SQL Server database table. The dataAdapter can do all this magic for us with a simple 3-word code because it has a powerful friend the SQLcommandbuilder which generates insert statements for the data adapter to run. The following one line of code dataAdapter.Update(datatable) performs the following functionality:a) opens the connection to the SQL Server databaseb) copies the row of data to the database table specified in the red SQL text, over the specified connection. c) closes the connection to the SQL Server database '3) We also use the DataAdapter.fill(datatable) command to fetch and display the updated dataset. After the new row of data is added the red sql statement retrieves the most recent 5 labor records so that they can be displayed to the program user, giving them an indication that their data entry was successful. This SQL SELECT statement is as simple as they can be. An easy improvement would be to shorten the names of the column headers to improve the fit of the gridview to the webpage. That functionality shown later. Public Shared daLaborRecords As New SqlDataAdapter("SELECT Top 5 * FROM featherman.PayrollRecords ORDER BY LaborID desc", con)‘notice when you create a SQLcommandbuilder you have to specify which dataAdapter it works with. Public Shared cbLaborRecords As New SqlCommandBuilder(daLaborRecords)'these variables are used to pass values from one procedure to the next. Public Shared gdecHours, gdecGrossPay, gdecFullyBurdoned As Decimal Public Shared gstrLaborCategory As String------------------------------------------------------------------------------------------------------------#Region "Page inititalization" Private Sub PayrollwithTimespan3_Init(sender As Object, e As EventArgs) Handles Me.Init 'This is a very important line of code because it allows data entry to be possible. The schema of a SQL Server database table is passed into an in-memory data table. The work of creating the columns of the datatable, and adding other properties is removed. daLaborRecords.FillSchema(gdtPayrollRecords, SchemaType.Mapped) 'The dataAdapter objects that we create can with the help of the SQLcommandbuilder, perform data inserts into a SQL database (local, or cloud) pretty easily. In order for the new row of data to accept data from the webpage and to be inserted to the data table then saved back to the SQL Server database, we have to pass the schema of the SQL database table into a data table inside the web page. 'Being part of the system set-up this mapping of data structure from a database to a local in-memory webpage needs to occur when the webpage first starts up. By pulling the database table schema (including data types, restrictions, etc.) into the local webpage, the webpage can then push back the data row of the correct format and data type. Here a lot of quality control in data entry is thankfully enforced. The reports coming out of a business transaction system are garbage if the data entering the system is garbage and erroneous. While Excel allows sloppy data entry, databases can be set up to enforce data quality. Because the datatable above now knows its set of columns, the correct data from the web form must be supplied to the correct columns of the datatable. End Sub#End Region#Region "Save labor record" 'Now the timecard keeping system saves the rows of data permanently inside a cloud database. The database table can store an unlimited number of rows. Because each row has data stored in a uniform, accurate manner, the analytics process can be semi-automated later using SQL GROUP BY() and PIVOT() commands (another class). Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click 'variables used in payroll calculations Dim decPayrate As Decimal Dim dtLabordate As Date Dim ts As TimeSpan 'the datatable to store new rows of data is global, so you better clear out the last labor record so that you can add a new one. gdtPayrollRecords.Rows.Clear() 'In this next code, the DataRow knows the column schema information (passed in at page_init) and is used to add collect and organize the data for the current transaction. This row starts out blank but then data is stored into the columns of the row to save aspect of each labor shift worked. This is a one row horizontal array similar to a row in Excel. This current procedure fills the empty columns of the new row with data from the controls on the web page, and then the data Adapter object takes the row of data and save it to a cloud database, as long as the data is formatted well and is of the right datatypes for each column. Dim dr As DataRow = gdtPayrollRecords.NewRow 'now some error checking for each data entry control on the webpage If txtStart.Text = Nothing OrElse txtEnd.Text = Nothing OrElse txtDate.Text = Nothing Then txtOutput.Visible = True txtOutput.Text = "Check entries for time and date" Exit Sub End If If rblLaborgrades.SelectedIndex = -1 Then txtOutput.Visible = True txtOutput.Text = "Select a labor type" Exit SubEnd if If rblHRCategory.SelectedIndex = -1 Then txtOutput.Visible = True txtOutput.Text = "Select an HR category" Exit Sub End If ‘ if we get this far in the code we will perform the necessary calculations and pull different values from the form into the columns of the new row called dr (datarow), similar as in prior modules. dr("LaborCategory") = rblLaborgrades.SelectedItem.Text decPayrate = rblLaborgrades.SelectedValue 'pull the pay rate into a local variable 'if a HR category was selected then assign it to a column in the new row dr("HRCategory") = rblHRCategory.SelectedItem.Text 'Select today in the calendar if no selection was made or a date in the future is selected dtLabordate = DateTime.Parse(txtDate.Text) If dtLabordate = Nothing OrElse dtLabordate > Today() Then dtLabordate = Today() dr("LaborDate") = dtLabordate.Date 'In accordance with federal law in the USA the construction firm pays overtime. Labor hours worked on Saturday earn time and 1/2, and the Sunday labor rate per hour is double the weekly rate. A common HR analytics task to produce automated exception reports to identify employees that habitually work Sunday then call in sick Monday or Tuesday. 'The Weekday function is an example of a very useful built-in time/date function (similar to DATEPART in SQL). Here we calculate the payrate depending on day of week and we also write a value to a column of the new row of data. This is a good example of writing a different value to a column for a new row of data depending on certain important criteria. This use of select case can solve a lot of problems. Select Case Weekday(DateTime.Parse(txtDate.Text)) Case 1 'Sunday decPayrate *= 2 'double the payrate for Sundays dr("Day") = "SunnyDay" Case 2 To 6 'weekday dr("Day") = "Weekday" Case 7 'saturday 'the payrate is time and 1/2 for Sundays decPayrate *= 1.5 dr("Day") = "Saturnday" End Select dr("Payrate") = decPayrate 'place the value that was just calculated into a column of the new row 'Timespan is an amazingly useful datatype that can be instantiated like any other variable. Timespan is used to hold the result of some date comparison. Such as the number of hours (or days) difference between two times (or dates). Here we create an instance of timespan with the name ts. We have to pull the date out of the textbox using datetime.parse(textbox.text). Once you have the results of the calculation in a timespan variable then you can format the output into # mintutes, weeks, months, days, etc. very easily such as you see below. ts.totaldays can output the number of hours with partial hours included (e.g 8.5 hours, not rounded up or down). ts = DateTime.Parse(txtEnd.Text) - DateTime.Parse(txtStart.Text) dr("Hours") = ts.TotalHours 'results of a timespan calculation makes this next line of code very simple. Multiply the number of hours worked times the labor rate, and store the value into a column of the datarow so that it can be saved into the transactions table later. We also update a global variable to pass the value to the UPDATE procedure. dr("GrossPay") = ts.TotalHours * decPayrate gdecGrossPay = ts.TotalHours * decPayrate 'the business employees part-time, full-time and 1099 (seasonal) workers. HR and project managers when bidding jobs use the metric that the medical/dental/retirement/doggie daycare/spa benefits add an additional 40% to the typical shift pay. HR provides a modest benefit for the 1099 workers. 1099 workers can access medical benefits at a local clinic. To cover this expense they add a 15% addendum to the gross pay calculation for the shift. The grosspay plus additional 15% or 40% create the 'fully burdoned' cost for the shift. This 'fully burdoned cost' is used by project managers and accountants to cost out projects. Select Case rblHRCategory.SelectedIndex Case 0 'part-time employee dr("FullyBurdoned") = dr("GrossPay") Case 1 ' full-time employee dr("FullyBurdoned") = dr("GrossPay") * 1.4 Case 2 '1099 employee dr("FullyBurdoned") = dr("GrossPay") * 1.15 End Select'Soon we need to update the summary table. To improve the organization of this project, the updating of the summary table is performed in a separate called procedure. While segmenting code improves organization, it also causes the problem of passing values from one procedure to another. The second procedure cannot read the values from the first procedure, but that problem is easily fixed using global variables. 'Because we only need three values from the labor transaction to update the summary table we use global variables. A future example shows that the values are in the public shared data table that holds the transaction and can be passed to the 2nd procedure that way. Here we use global variables, the value for gdecGrossPay already updated. gdecHours = ts.TotalHours gdecFullyBurdoned = dr("FullyBurdoned") gstrLaborCategory = rblLaborgrades.SelectedItem.Text 'now that the columns of the new row are populated with values, we can now add that row to the datatable. gdtPayrollRecords.Rows.Add(dr) Try 'We use a TRY/CATCH block to catch any errors gracefully and not crash the program. This next line of code runs the red SQL statement that can insert the new row of labor data. Thanks commandbuilder. daLaborRecords.Update(gdtPayrollRecords)'Next we call the procedure that updates one row in a summary database table. We will pass in global variables, to update the number of hours worked, total pay and fully burdened pay. This called procedure updates the summary table, and will need values passed from the datatable filled in this procedure Call UpdateTotals() It was important to perform the update of the summary table (with the Call UpdateTotals procedure) BEFORE we cleared out the data (otherwise there would be no row of data from the new labor record that can be used to update the summary table.'next we want to display the 5 most recent rows of data, but we don’t want to see duplicate data so we clear out the recently added row of data. BE careful NOT to clear out the row of data until the summary table is updated. We also clear out the gridview before loading it again. gdtPayrollRecords.Rows.Clear() GridView2.DataSource = Nothing GridView2.DataBind()'Now that the data row was saved to the database, and the data table cleared out, the next line will run the red SQL SELECT statement to retrieve the records from the labor records table daLaborRecords.Fill(gdtPayrollRecords)'to display the refreshed dataset that was retrieved into the datatable use a gridview control. GridView2.DataSource = gdtPayrollRecords GridView2.DataBind() Catch ex As Exception txtOutput.Visible = True txtOutput.Text = ex.Message End Try txtOutput.Visible = False‘so the overall procedure was save the row of data to the database, update the summary table, then clear out the datatable and gridview, before filling them again with the new data. End Sub#End RegionProtected Sub UpdateTotals() 'This query updates one row of data in the summary table. The labor category that the shift was recorded for (i.e., Electrician) has four columns updated. The NumberRecords column gets incremented by 1, and then 3 columns of data are increased using the value passed in from 3 parameters (@p1, @p2, @p3); ONLY for the labor category passed in by parameter @p4 (the labor category value selected in the radiobuttonlist).In the red SQL statement:be careful to put a space between words, be careful to keep the parameters names tight @p1 rather than @ p1be careful to use the same parameter names in the red SQL statement and in the parameter.addwithvalue statements belowbe careful to use a WHERE statement which specifies which row of data to update. The row that you update is specified by identifying the primary key column (usually an integer) 'The syntax is UPDATE tablename set column 1 = @p1, column 2= @p2, etc. WHERE the row # is specified. You would typically update only 1 row. Dim cmdUpdateLaborTotals As New SqlCommand("UPDATE [featherman].[PayrollTotals] SET NumberRecords +=1, TotalHours += @p1, GrossPay += @p2, FullyBurdoned += @p3 WHERE LaborCategory = @p4", con)'The above SQL command can be created before the values of the parameter are set (presumably after the user input is verified and processed) because it’s a string of text. Below is how the the values from the webform (currently either in controls on the form or in columns of a datarow) are passed into the parameters for the SQL update statement. When the SQL update statement is run, the values are passed from the webpage into the SQL statement and used to make changes in the back-end database table. This process may seem tedious but enforces security and protects against SQL injection attacks. 'so a SQL UPDATE SET statement (it's red because it is text) is run against the SQL Server database using a SQLCommand (an object). Next we create the parameters that the SQL command will use. The data values are already neatly in the new row of the data table so we can access them there. When you create the parameter you specify where the value is going to come from. The parameter allows the value from the webpage to get inserted into the SQL UPDATE SET statement. So you are passing values used to update a summary table from the webpage into the SQL UPDATE SET statement. With cmdUpdateLaborTotals.Parameters .Clear() .AddWithValue("@p1", gdecHours) .AddWithValue("@p2", gdecGrossPay) .AddWithValue("@p3", gdecFullyBurdoned) .AddWithValue("@p4", gstrLaborCategory) End With‘The above methodology pulls values to update the summary table from the array. In the above query you also could have just saved the three values GrossPay, FullyBurdoned, and LaborCategory in global variables. Try'updates and deletes are performed with a SQLcommand, not dataAdapter. The main difference is that we have to manually open and close the connection. Next we open the connection to the DB if it is closed If con.State = ConnectionState.Closed Then con.Open()'the next line of code reads execute the red UPDATE SET SQL Statement over the connection to the database. The function is called .ExecuteNonQuery because it does not return any dataset cmdUpdateLaborTotals.ExecuteNonQuery()'now that the summary table is updated with the UPDATE SET SQL statement, now call (invoke) another procedure fetching a new copy of the updated totals into the webpage. It is useful to break functionality into segments (such as shown here) that can be combined. Call FetchLaborTotals() Catch ex As Exception txtOutput.Visible = True txtOutput.Text = ex.Message Finally con.Close() 'again SQL commands need to have their connections explicitly opened and closed. End Try End Sub Protected Sub FetchLaborTotals() 'This procedure can fetch the current results of the SUMMARY table into a datatable and then displayed in a gridview on your webpage on view #2. We run the red SQL command shown in red. After the summary table is updated, display the table in a gridview. Dim daFetchTotals As New SqlDataAdapter("SELECT * FROM PayrollTotals", con)'You may wonder why we are not clearing out the rows of the following datatable, and clearing out the data from the gridview before refilling them. Simple, because they are local objects that are created new each time the procedure runs. If the datatable was global, then it would have to have its rows cleared. When you create a new datatable each time this procedure runs, it is empty meaning no rows no columns, both of which are passed in on the .FILL command. Dim dtLaborTotals As New DataTable Try daFetchTotals.Fill(dtLaborTotals) GridView5.DataSource = dtLaborTotals GridView5.DataBind() Catch ex As Exception txtOutput.Text = ex.Message End Try End Subleft317500#Region "Analytics - you can ignore this section for now, and go forward to the next topic that gets you closer to completing your final project" Protected Sub PerformAnalytics() 'Three SQL analytics queries are performed against a SQL database. Each query retrieves a compiled dataset that is stored in these data tables. Dim dtLaborgradeTotals, dtLGHRCatTotals, dtYear As New DataTable 'when the program user clicks on the second link button, view # 2 will display and this procedure runs SQL queries that retrieve two datasets from a cloud-based SQL Server database. DataAdapters are created and used to run SQL statements that perform the analytics (shown in red). The retrieved datasets are assigned to datatables, and then displayed in gridviews. This information is shown here to show a realistic usage of SQL inside a webpage, and to demonstrate the common integration of \SQL and SQL Server databases. You do not need to learn this functionality for an introductory programming class. Rather you can connect to the database and use a pivot table to provide the same analytics. You can also perhaps use Tableau or PowerBI. In each case the software is running SQL in the background, and providing the same results. 'Challenge! Use an Excel pivot table to verify the results shown on your screen. An example is shown below. You can connect to the featherman.PayrollRecords table in the featherman_analytics cloud database on cb-ot-devst03.ad.wsu.edu (user ID = mfstudent, pwd = BIanalyst). If you can use a pivot table then you can see how useful it is to connect a pivot table to a database. 'The first query is an example of a standard SQL GROUP BY() statement where one column (FullyBurdoned) is summed based on values in a dimension (labor category). Much more on how these SQL queries work in the link below - look for module #2 and #4 ' 'The code performs a Count, Average and Sum for each value of a dimension field named LaborCategory (ie part-time, full-time, 1099.). These totals that are calculated are shown for each labor category. The SUM() provides the similar functionality inside a GROUP BY() above or PIVOT() below, however the SUM result is cut into subgroups (the columns) in the PIVOT cross-tabulation query. Dim daGroupData As New SqlDataAdapter("SELECT [LaborCategory] as [Category], COUNT([LaborID]) as [#Shifts], FORMAT(AVG([FullyBurdoned]), 'N0') as [Avg. Shift Expense], FORMAT(SUM([FullyBurdoned]), 'N0') As [Total Expense] From [featherman].[PayrollRecords] Group BY [LaborCategory]", con) 'In the next query the data is cross-tabulated, there are rows of data that are cut into columns that provide totals for Part-time, full-time and 1099 workers. Dim daPivotData As New SqlDataAdapter("SELECT * FROM (SELECT [LaborCategory] as [Category], [HRCategory], [FullyBurdoned] FROM [featherman].[PayrollRecords] ) AS BaseDataTable PIVOT (SUM([FullyBurdoned]) FOR [HRCategory] IN ([Part-time], [Full-time], [1099])) AS PivotTable", con) 'This is another example of a SQL pivot query (essentially the same concept and functionality as an Excel pivot table) that can build a table one row for each value of a dimension, such as here the 4 Labor Categories. After generating the rows next the query generates 12 columns of summary data for each row. Notice the word after the key term PIVOT? The word sum lets you know that totaling is going to occur by the combination of month and labor grade. So here 48 calculations are made to complete the table. The labor is summarized by month and labor category. You can also perform this calendar functionality with an Excel pivot table, if you have a column that extracts the month out of the labor date column. Dim daPivotYearData As New SqlDataAdapter("SELECT * FROM (SELECT [LaborCategory] as [Category], DATENAME(MONTH,[LaborDate]) AS [MonthName], [FullyBurdoned] FROM [featherman].[PayrollRecords]) AS BaseDataTable PIVOT (SUM([FullyBurdoned]) FOR [MonthName] IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS PivotTable", con) 'anytime you query a database, there is a good chance that your code is wrong, or the password is wrong, or the database is only available within a VPN, the server is down, etc. So with so many chances for failure, you need to put the code inside a TRY CATCH block. The sensitive code goes in the TRY section, then you CATCH the errors (useful for debugging) without the web page crashing. Try daGroupData.Fill(dtLaborgradeTotals) ' this line runs the first SQL statement over the connection to the cloud database, and returns a dataset of compiled numbers that is assigned to the datatable. The # of rows returned is based on the cardinality (# of different values) in the dimension specified (here LaborCategory). daPivotData.Fill(dtLGHRCatTotals) ' this line runs the second SQL statement over the connection to the cloud database, and returns a dataset of compiled numbers that is assigned to the datatable. We get again the same number of rows as before but now have used the pivot operation to generate columns. daPivotYearData.Fill(dtYear) 'this line runs the third red SQL query, and demonstrates again the usefulness of learning SQL programming. GridView1.DataSource = dtLaborgradeTotals 'tell each gridview control which table of data to display GridView3.DataSource = dtLGHRCatTotals GridView4.DataSource = dtYear GridView1.DataBind() 'use gridview controls to display the retrieved data that is now in the datatables GridView3.DataBind() GridView4.DataBind() Catch ex As Exception txtOutput.Visible = True txtOutput.Text = ex.Message End Try End Sub#End Region#Region "Utilities" 'switch between views Protected Sub LinkButton1_Click(sender As Object, e As EventArgs) Handles LinkButton1.Click MultiView1.ActiveViewIndex = 0 End Sub Protected Sub LinkButton2_Click(sender As Object, e As EventArgs) Handles LinkButton2.Click MultiView1.ActiveViewIndex = 1 Call PerformAnalytics()471634615769700 End Sub Protected Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click 'here we can clear the form for further data entry rblLaborgrades.SelectedIndex = -1 rblHRCategory.SelectedIndex = -1 txtStart.Text = Nothing txtEnd.Text = Nothing txtDate.Text = Nothing txtOutput.Visible = False txtOutput.Text = Nothing End Sub#End RegionEnd ClassWhile you can use the SQL statements to compile the data, you can also use an Excel pivot table or pivot chart to compile the data by labor category if you connect Excel to your database. ................
................

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

Google Online Preview   Download