Washington State University



Featherman’s Working with Calendar Dates and Time Data ?left698500 Class TextboxTimespan Inherits System.Web.UI.Page 'this project demonstrates how to work with dates and times that are entered into calendars and textbox controls. Dates can be formatted in different ways, and date calculations are pretty easy. Easier than using DATEFIFF(in Excel) or SQL. This module also shows how to calculate how many days and/or hours there are between time/date selections from two user controls. If you want to calculate the # days between two user-selected dates then you need a timespan variable, two calendar controls, or two textboxes with their .selectionmode set to date or datetime or datetime local, or time (depending on requirement). 'You can also perform calculations by using the .NOW() funtion which accesses the clock time in your computer. You may begin to wonder how we progressed this far in the class and did not use dates yet in any calculations. Public Shared gdtTimerStart, gdtTimerEnd As DateTime Public Shared gintNumberWeekDays, gintNumberWeekEnds As Integer#Region "Page Init" Protected Sub Page_Init(sender As Object, e As EventArgs) Handles Me.Init 'The page init code runs just once when the webpage is first rendered. This procedure demonstrates how to add hours to the local time on your computer. Did you know the entire People's Republic of China (PRC) uses just one time zone? Beijing time. The built-in function .NOW()works here andalso in Excel. Change the .to### code after the .NOW function on the next line of code to test out some different formatting options. Its pretty impressive. Label2.Text = "Local time Pullman, USA: " & Now.ToShortDateString & " " & Now.ToShortTimeString Label3.Text = "Beijing time: " & Now.AddDays(1).ToShortDateString & " " & Now.AddHours(16).ToShortTimeString 'the following line of code is the supposed fix to maintaining the browser screen position on postback, so the webpage does not jump aound. Page.MaintainScrollPositionOnPostBack = True 'Food for thought: if your business transaction requires a human to pick a date then provide a webpage control as shown below. You might not need a webcontrol however for many applications. 'Consider the simple POS transaction. When does this always occur? Now. Whatever point in time, or day of teh week it is, the transaction always occurs at a moment in time that can be captured easily by using the Now() or Today() functions. So often there is no need to task the app user to use a webpage control to indicate the transaction date. You can just use the Now() or Today() built-in functions (which are also in Excel) and save a little time for the app user. Its also less code. So when planning some functionality for a webpage, consider if it is unnecessary to include a web control. You might save everyone a little time by just capturing the date and/or time from the server. End Sub#End Region#Region "First calendar" 'this procedure fires when a selection is made in the textbox that uses a date or datetime selectionmode. Just be sure to set the .autopostback property of the textbox to true! Protected Sub txtFirst_TextChanged(sender As Object, e As EventArgs) Handles txtFirst.TextChanged 'stop the program if no selection was made in the calendar. If txtFirst.Text = Nothing Then Exit Sub 'Timespan is a variable that holds the result of a time or date calculation. This variable makes this entire process much simpler Dim ts As TimeSpan = Nothing 'The content in the textbox is text even if it looks like a calendar. So we have to convert the textbox selection contents into a date variable. Dim dtDate As Date = DateTime.Parse(txtFirst.Text) TextBox1.Text = "Here are some common ways to parse out different date data that can be assigned to fields in a database table. Each line produces useful data from the date captured by the calendar-looking textbox. A great deal of business performance analytics depend on deriving these date focused data fields. In analytics terms these are time dimensions." & vbNewLine & vbNewLine & "The original date captured by the .text format: " & txtFirst.Text & vbNewLine & "After assigment to a date var the var.date format is: " & dtDate.Date & vbNewLine & vbNewLine 'These first three examples are the most common used just to format the date nicely for data storeage or display in a report TextBox1.Text &= "Date variables have many different .tostring formats useful to display the date nicely in database storage, dashboard or report:" & vbNewLine & vbNewLine TextBox1.Text &= ".ToLongDateString formatting: " & vbTab & dtDate.ToLongDateString & vbNewLine TextBox1.Text &= ".ToShortDateString formatting: " & vbTab & dtDate.ToShortDateString & vbNewLine TextBox1.Text &= ".ToString formatting using MM/dd/yy formatting is useful if data retrieved from SQL database is year/month/day: " & dtDate.ToString("MM/dd/yy") & vbNewLine & vbNewLine 'Many times you want to capture some text from the date selected in a calendar such as day or month (e.g. Tuesday or March) and otherwise just capture a nicely formatted date. This is what string functions do, they provide nice strings of text. This is in comparison to later code that provides numbers from the date such as 2 (for Tuesday) or 3 (for March). As a designer of a sales or production system, when you store the transaction into a database you can parse out some numerics about the date. Saving parsed date information into new columns eats a little storage space, but speeds up analytics. If you had millions of rows in your stored fact table of transactions, the adding to many extra columns would not be feasible. Your will need to retrieve records based on filters (such as month or week#) and need to perform complex datetime calculations, the pre-processing of dates can make those later calculations run MUCH faster. TextBox1.Text &= "Everyone prefers to read text names for day of week and month:" & vbNewLine & vbNewLine TextBox1.Text &= ".To String('ddd') and ('dddd') formatting parse the name of day short: " & dtDate.ToString("ddd") & " or long: " & dtDate.ToString("dddd") & vbNewLine & vbNewLine TextBox1.Text &= ".To String('MMM') and ('MMMM') formatting parse the name of month either short: " & dtDate.ToString("MMM") & " or long: " & dtDate.ToString("MMMM") & vbNewLine & vbNewLine 'below are many formats that return numeric values that are very helpful to analytics and ETL processes TextBox1.Text &= "Other than the .tostring formatting options, if you want to generate integer fields such as for day or week or month, then use these built-in date variable functions: .dayofweek .day .dayofyear .month or .year. All are useful as date/time dimensions in a pivotchart." & vbNewLine & vbNewLine TextBox1.Text &= ".DayOfWeek function of date variable parses day # of week: " & (dtDate.DayOfWeek + 1) & vbNewLine 'on the above line we add one to the day of week # because the default is day #'s 0 to 6, that's just not right. 1-7 is more intuitive. TextBox1.Text &= ".Day function of date variable parses day# of month: " & vbTab & dtDate.Day & vbNewLine TextBox1.Text &= ".DayOfYear function of date variable: " & vbTab & dtDate.DayOfYear & vbNewLine 'this uses a built-in function TextBox1.Text &= ".Month function of date variable parses month# of year: " & dtDate.Month & vbNewLine TextBox1.Text &= ".Year function of date variable parses year: " & dtDate.Year & vbNewLine & vbNewLine 'the timespan variable is used next to hold the result of the calculation of how many days in the past the calendar selection was. 'The Today function reads the clock date from the server. The NOW function works similarly and provides the date and time. ts = Today.Subtract(dtDate) 'timespan is a variable that has many ways to present its results, here we want to see # of days an even occured and give different messages depending on whether the calendar date seleced is in the past of guture. Notice the value in the timespan variable can be formatted many ways such as .totalMinutes, .totalhours. Others time measures such as seconds & milliseconds are also available. If dtDate > Today Then TextBox1.Text &= "This date is " & (ts.TotalDays) * -1 & " days in the future." Else TextBox1.Text &= "This date is " & ts.TotalDays & " days in the past." & vbNewLine & vbNewLine TextBox1.Text &= "Also calculated as: " & ts.TotalMinutes.ToString("N0") & " minutes ago or " & ts.TotalHours.ToString("N0") & " hours ago" End If End Sub#End Region#Region "Accounts Receivable" Protected Sub Button9_Click(sender As Object, e As EventArgs) Handles Button9.Click 'this is an accounts receivable example calculates the number of days an invoice was issued,and provides a different managerial message to the accounts receivable employee. Some customers do not pay on time. While we calculate the number of days here, the values (the # of days, and the text instructions of what to do) could also be saved as new columns into a database, or used in an analytics report. Timespan similarly is used to ascertain the # of days or minutes or hours ago an event happened. There are many situations where it is important to add time as a dimension to your transactions or reporting. Timespan makes this relatively easy. For example this example displays how many days ago an invoice was issued. First we ready the webform. TextBox2.Text = Nothing Image2.Visible = False 'here are the two new stars of the show, 2 new variable types to leverage Dim dtDate As Date Dim ts As TimeSpan 'if a valid selection was not made in the calendar then stop the program If txtAR.Text = Nothing OrElse DateTime.Parse(txtAR.Text) > Today Then TextBox2.Text = "Please select a date in the past." Exit Sub End If 'This code could have gone into an Else statement of the above If-then-endif processing. But often it is easier cognitively not to use different levels of code nested under eachother. This code below would not run if there was a data entry problem. 'Next we extract a date value, store it in a date variable and then use a timespan variable to capture the result of a date subtraction (ie. subtracting one date in a calendar from another. Here we subtract the selected date from today. dtDate = DateTime.Parse(txtAR.Text) ts = Today.Subtract(dtDate) TextBox2.Text = "Invoice dated: " & dtDate.Date & " - " & ts.TotalDays & " days ago" & vbNewLine 'based on how many days ago the selected date is show a different message Select Case ts.TotalDays Case 0 To 30 'invoice is under 30 days old TextBox2.Text &= "Current invoice" Case 30 To 60 ' invoice is over 30 but less than 60 days old TextBox2.Text &= "past due. Reduce credit to customer. " Case 61 To 90 ' invoice is over 30 but less than 60 days old TextBox2.Text &= "past due. Send dead flowers. " Case Is > 90 'invoice is over 60 days old send the bill collector to talk to the client. TextBox2.Text &= "past due. Send Luca Brasi" ' 'Luca Brasi is a famous bill collector from the movie "The Godfather" Image2.Visible = True Image2.ImageUrl = "luca.gif" End Select 'What if your company had 2000 open invoices, how would you identify how many were unpaid? how would you prioritize the accounts receivable employee? How would you give management a list of outstanding invoice sorted by length of time? Well It is possible to loop (or other methodology) a database table and examine all the rows inside the database table and then perhaps identify the invoices that are delinquent (60 or 90 days) and then send a different toned correspondence. You could also use SQL SELECT statements to categorize the data. 'A similar scenario is to send a different promotional email based on activity within the prior 60 days. You can perform similar functionality in SQL in group mode, select all the invoices in one time block (say >30 and <60 days) and then kick off some other procedure such as sending them all voicemail or facebook ad. End Sub#End Region#Region "Day of Week" Protected Sub txtDayofWeek_TextChanged(sender As Object, e As EventArgs) Handles txtDayofWeek.TextChanged 'be sure to set the autopostback property to true so that this .textchanged event can fire 'Visual studio dev team has also made it easy to identify what day of the week or year the transaction is occuring. For example if a retailer has a slow day, you can create a website program to kick-in a 25% discount on for example Wednesdays. This is why restaurants have 2-for-1 specials on slow nights. 'have you ever noticed the price of airline tickets changes by time of day and day of week? How do they do that? Your humble professor identified that ticket prices change for the same route, based on time of day, and day of week (cheapest flight tickets were before noon on Tuesdays). 'here you can just pull a way to format the date. this would be useful if for example you wanted to create new columns of data for storage to facilitate subsequent analytics (storing the month # or day # as the data goes into the database rather than having to parse it out using DATEPART() in your SQL in subsequent reporting. Here are a sample of the ways you can parcel the data. Dim dtDate As Date Dim decPrice As Decimal Dim strDate As String If txtDayofWeek.Text = Nothing Then TextBox3.Text = "Kindly select a date in the calendar" Exit Sub Else dtDate = DateTime.Parse(txtDayofWeek.Text) End If 'this is how you can alter a price based on what day of the week it is (making products a little more expensive on the weekends to cover labor costs), you can theoretically also alter price based on time of day. By default the days of teh week are 0 based so 0 is sunday etc. Referring to an item or row of data as teh 0 item is ok, but referring to the first day of the week as 0 seems wierd so we add 1. The default day numbers are 0 to 6, so by adding one we change the day numbers to 1 - 7 'so here is an example of changing a price based on day of week (time of day would work similarly, for example if the lunch and dinner menu had the same item but the price changes on the wireless ordering app.) Select Case dtDate.DayOfWeek + 1 Case 1, 7 'these are the weekend days 1 is Sunday, 7 is Saturday decPrice = 7.25 strDate &= dtDate.ToString("dddd") & " was selected which is on the weekend. " gintNumberWeekEnds += 1 Case 2 To 6 'these are the weekdays 2 is Monday, 3 is Tuesday, etc. Yes these are different in the textbox when the selectionmode is date decPrice = 5.75 strDate &= dtDate.ToString("dddd") & " was selected - a weekday. " gintNumberWeekDays += 1 End Select 'Recall we created 2 global integer variables gintNumberWeekDays, gintNumberWeekEnds, so we can track the number of orders on weekdays vs. weekends. TextBox3.Text = vbNewLine & strDate & vbNewLine & "Charge the customer " & decPrice.ToString("C") & vbNewLine & vbNewLine TextBox3.Text &= "# weekday transactions: " & gintNumberWeekDays & vbNewLine & "# weekend transactions: " & gintNumberWeekEnds 'This might reduce the screen bounce Page.MaintainScrollPositionOnPostBack = True End Sub#End Region#Region "Day of Month" 'be sure to set the autopostback property for the textbox to true so that this event will exececute when a selection is made in the calendar. Protected Sub TextBox9_TextChanged(sender As Object, e As EventArgs) Handles txtMonth.TextChanged 'The ability to parse different time and date dimensions from a calendar selection so that MORE information and insight can be derived from the transaction program. Many standard business reports and analytics are not possible if you do not have access to the day#, week#, or month#. When you save transaction data into a database, you can add a few extra columns of different date formats, which makes reporting much easier. The alternative is to create these fields when you pull the data from the database (typically with SQL in the classic Extract, transform, and load (ETL) process when moving transaction data into the data warehouse). Dim dtDate As Date If IsNothing(txtDayofWeek.Text) = True Then Exit Sub Else dtDate = DateTime.Parse(txtDayofWeek.Text) End If 'Here are more ways to parse out different numbers and words from the value in the date variable TextBox3.Text = "Date.Month function displays: " & dtDate.Month & vbNewLine TextBox3.Text &= "Date.Year function displays: " & dtDate.Year & vbNewLine TextBox3.Text &= ".Year + .Month functions concatenated : " & (dtDate.Year * 100) + dtDate.Month & vbNewLine TextBox3.Text &= "Year() and Month() SQL-like functions : " & (Year(dtDate) * 100) + Month(dtDate) & vbNewLine & vbNewLine TextBox3.Text &= "Monthname(Month) parses the text month: " & MonthName(Month(dtDate)) & vbNewLine TextBox3.Text &= "Monthname(Month) can abbreviate the month : " & MonthName(Month(dtDate), True) & vbNewLine & vbNewLine TextBox3.Text &= "This is useful in a report: " & MonthName(Month(dtDate), True) & "-" & Year(dtDate) & vbNewLine 'thank you to 2019 student Alex Shey for next solution TextBox3.Text &= "This is better: " & MonthName(Month(dtDate), True) & "-" & dtDate.ToString("yy") & vbNewLine 'The last two output fields look great in report, or in the X-axis of a chart, but be careful to check that your sorting is correct. Sometimes a text column (such as text names of months ) must be sorted by another numeric field. Textual values often cannot be sorted veryeasily. Take for example month - not many reports would look good with april and august the first two columns on the chart. IN this case sort on the month #, but display the month name. You could also sort on a field that looks like 201904 (year*100) + month. End Sub#End Region#Region "Calendars" Protected Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click 'The calendar control is very easy to work with, it is just a bit large for webpages though, and takes up a lot of space. Similar to the .selectedvalue, or .selecteditem.text, the calendar has a .selectedDate property, so its very comfortable to learn and use and is presented here first. The following traps for two errors a) if no date was selected in the calendar, b) if a date was selected in the future. This program requires a .selected date in the past. 'Here is how you subtract dates or times using 2 calendars, the formula is endtimeControl.selecteddate.subtract(startimecontrol.selecteddate) Dim decTotal As Decimal Dim ts As TimeSpan If Calendar3.SelectedDate = Nothing Or Calendar3.SelectedDate = Nothing Then TextBox4.Text = "please be sure to select a date in both calendars" Exit Sub End If ts = Calendar3.SelectedDate.Subtract(Calendar2.SelectedDate) decTotal = ts.TotalDays * RadioButtonList2.SelectedValue TextBox4.Text = Now() & vbNewLine & "Booking registered for a " & RadioButtonList2.SelectedItem.Text & vbNewLine & vbNewLine TextBox4.Text &= ts.TotalDays & " days@ " & FormatCurrency(RadioButtonList2.SelectedValue, 0) & " per day" & vbNewLine & vbNewLine TextBox4.Text &= "SubTotal: " & vbTab & vbTab & FormatCurrency(decTotal) & vbNewLine & "Total with tax is:" & vbTab & FormatCurrency((decTotal) * 1.06) Page.MaintainScrollPositionOnPostBack = True End Sub#End Region#Region "Timecard Processing" Protected Sub Button6_Click(sender As Object, e As System.EventArgs) Handles Button6.Click 'Ok. So we have done a lot with dates, what about hours? You can also parse (convert) the time or date typed into a textbox (if its close to being accurate). 'So here is a start of a timekeeping system that a payroll clerk or office manager would use. So a computer consultant is figuring out how much money to charge on an invoice. This assumes all consulting labor on the job is charged@ $50 per hour. Can you envision a timekeeping system that can collect all the labor for a construction job that incorporates different labor costs? (say painters, electricians, roofers, framers, plumbers, etc.). Dim decTotal As Decimal Dim ts As TimeSpan If TextBox6.Text = Nothing OrElse TextBox7.Text = Nothing OrElse RadioButtonList1.SelectedIndex = -1 Then TextBox8.Text = "kindly enter times for start and end of shift and select a labor grade before pressing the button. Thanks." Exit Sub End If ts = DateTime.Parse(TextBox7.Text) - DateTime.Parse(TextBox6.Text) decTotal = Convert.ToDecimal(RadioButtonList1.SelectedValue) * ts.Hours 'Here we subtract an hour for mandatory lunch break TextBox8.Text = "Hours on the job: " & FormatNumber(ts.TotalHours - 1) & vbNewLine & "Gross pay: " & FormatCurrency(decTotal, 2) If decTotal > 500 Then TextBox8.Text &= vbNewLine & vbNewLine & "That was a good day." End If Page.MaintainScrollPositionOnPostBack = True End Sub#End Region#Region "Stopwatch" Protected Sub btnStopwatch_Click(sender As Object, e As System.EventArgs) Handles btnStopwatch.Click 'just grab the current time and save it into a global variable txtElapsedTime.Text = "Timer started..." gdtTimerStart = Now Page.MaintainScrollPositionOnPostBack = True End Sub Protected Sub btnFinished_Click(sender As Object, e As System.EventArgs) Handles btnFinished.Click 'create the variable that can hold the data, assign the value to the second variable (end time) which is also captured into a global variable with the value captured from the system clock using the .NOW function. Dim tstimeElapsed As TimeSpan = Nothing gdtTimerEnd = Now 'calculate the elapsed time tstimeElapsed = gdtTimerEnd.Subtract(gdtTimerStart) 'display the elapsed time in minutes and seconds txtElapsedTime.Text = tstimeElapsed.Minutes & " minutes " & tstimeElapsed.Seconds & " seconds" 'reset the variable that can hold the elapsed time tstimeElapsed = Nothing Page.MaintainScrollPositionOnPostBack = True End Sub#End Region ................
................

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

Google Online Preview   Download