Created
March 22, 2012 15:11
-
-
Save ChrisMoney/4f1b6e702604aa89e3e3 to your computer and use it in GitHub Desktop.
VB - Time Tracker App that opens results in Excel Spreadsheet
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Imports System.Data.OleDb | |
| Imports System.Windows.Forms.VisualStyles.VisualStyleElement.TaskbarClock | |
| Imports System.Security.Principal | |
| Imports System.Net.Mime.MediaTypeNames | |
| Imports Microsoft.Office.Interop.Access | |
| Imports System.Globalization | |
| Public Class Time_Tracker | |
| Dim timeStart As DateTime 'Time when timer starts | |
| Dim timeEnd As DateTime 'Time when timer ends | |
| Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load | |
| Dim ident As String = SystemInformation.UserName | |
| 'Get user settings | |
| Dim OleConn2 As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Time_Tracker\Time_Tracker.accdb;") | |
| Dim OleCmd2 As New OleDbCommand("SELECT * FROM User_Settings WHERE User = '" & ident.ToString & "'", OleConn2) | |
| Dim OleDa2 As New OleDbDataAdapter(OleCmd2) | |
| Dim dt2 As New DataTable | |
| OleDa2.Fill(dt2) | |
| If (dt2.Rows.Count > 0) Then | |
| Dim x = dt2.Rows(0)("Form_X") | |
| Dim y = dt2.Rows(0)("Form_Y") | |
| 'Explictly set form location | |
| Me.Location = New Size(x, y) | |
| End If | |
| ToolTip1.SetToolTip(reportsButton, "Set report parameters") | |
| ToolTip1.SetToolTip(start_stop_Timer, "Start and stop your project time") | |
| 'Bind Project Type combo box with data from Access | |
| Dim OleConn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Time_Tracker\Time_Tracker.accdb;") | |
| Dim OleCmd As New OleDbCommand("SELECT * FROM Project_Type Order By ID", OleConn) | |
| Dim OleDa As New OleDbDataAdapter(OleCmd) | |
| Dim dt As New DataTable | |
| OleDa.Fill(dt) | |
| projectName.DataSource = dt | |
| projectName.ValueMember = "Project_Type" | |
| projectName.DisplayMember = "Project_Type" | |
| End Sub | |
| Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.FormClosing | |
| 'If user attempts to close the tracker and it is, automatically save the time | |
| If (projectTime.Text = "00:00:00") Then | |
| e.Cancel = False 'dont cancel the closing event | |
| Else | |
| If (Not projectTime.Text = "00.00.00") Then | |
| Timer1.Stop() 'Stops if it was started | |
| timeEnd = DateTime.Now | |
| Dim span As TimeSpan = timeEnd.Subtract(timeStart) | |
| '______________________________________________________________________________________________________________ | |
| 'Dim result = MessageBox.Show("Are you sure you want to save this project", "caption", MessageBoxButtons.YesNo) | |
| 'If (result = DialogResult.No) Then | |
| 'Exit Sub | |
| 'ElseIf (result = DialogResult.Yes) Then | |
| '____________________________________________________ | |
| 'Insert research results | |
| Dim researchInsert As String = "No" | |
| If (research.Checked = True) Then | |
| researchInsert = "Research" | |
| End If | |
| 'Insert meeting/phone results | |
| Dim meetingPhoneInsert As String = "No" | |
| If (meetingPhone.Checked = True) Then | |
| meetingPhoneInsert = "Meeting/Phone" | |
| End If | |
| 'Get the fund number associated with the project type | |
| Dim conn0 As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Time_Tracker\Time_Tracker.accdb;") | |
| conn0.Open() | |
| Dim cmd0 As New OleDbCommand("SELECT Fund_Number FROM Project_Type WHERE Project_Type = '" & projectName.Text & "'", conn0) | |
| Dim oleDA As New OleDbDataAdapter(cmd0) | |
| Dim dt As New DataTable | |
| oleDA.Fill(dt) | |
| Dim fundNumber As Integer = 0 | |
| If (dt.Rows.Count > 0) Then | |
| If (Not IsDBNull(dt.Rows(0)("Fund_Number"))) Then | |
| fundNumber = dt.Rows(0)("Fund_Number") | |
| End If | |
| Dim currentDate As Date = DateTime.Now.ToShortDateString() | |
| Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Time_Tracker\Time_Tracker.accdb;") | |
| conn.Open() | |
| Dim cmd As New OleDbCommand("Insert Into Time_Tracker(Project_User,Project_Name,Project_Time,[Project_Date],Notes,Research,Meeting_Phone,Fund_Num)Values(@name, '" & projectName.Text & "','" & span.TotalSeconds & "','" & currentDate & "','" & projectNotes.Text & "','" & researchInsert & "','" & meetingPhoneInsert & "','" & fundNumber & "')", conn) | |
| cmd.Parameters.AddWithValue("@name", SystemInformation.UserName) | |
| cmd.ExecuteNonQuery() | |
| 'MessageBox.Show("Your project has been submitted") | |
| 'Reset Project Time | |
| e.Cancel = False | |
| Else | |
| MessageBox.Show("Your project could not be saved") | |
| End If | |
| End If | |
| End If | |
| 'If (projectTime.Text = "00:00:00") Then | |
| ' e.Cancel = False 'dont cancel the closing event | |
| 'Else | |
| ' If (Not projectTime.Text = "00.00.00") Then | |
| ' MessageBox.Show("Do you want to submit this project time?", "Confirm", MessageBoxButtons.YesNo) | |
| ' If (DialogResult.Yes) Then | |
| ' e.Cancel = True 'cancel the closing event | |
| ' Exit Sub | |
| ' Else | |
| ' If (DialogResult.No) Then | |
| ' e.Cancel = False 'dont cancel the closing event | |
| ' End If | |
| ' End If | |
| ' End If | |
| 'End If | |
| End Sub | |
| Private Sub start_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles start_stop_Timer.Click | |
| If (Timer1.Enabled = False) Then | |
| timeStart = DateTime.Now 'Changes time to current time | |
| Timer1.Start() 'Starts timer | |
| Else | |
| If (Timer1.Enabled = True) Then | |
| Timer1.Stop() 'Stops if it was started | |
| timeEnd = DateTime.Now | |
| Dim span As TimeSpan = timeEnd.Subtract(timeStart) | |
| '______________________________________________________________________________________________________________ | |
| 'Dim result = MessageBox.Show("Are you sure you want to save this project", "caption", MessageBoxButtons.YesNo) | |
| 'If (result = DialogResult.No) Then | |
| 'Exit Sub | |
| 'ElseIf (result = DialogResult.Yes) Then | |
| '____________________________________________________ | |
| 'Insert research results | |
| Dim researchInsert As String = "No" | |
| If (research.Checked = True) Then | |
| researchInsert = "Research" | |
| End If | |
| 'Insert meeting/phone results | |
| Dim meetingPhoneInsert As String = "No" | |
| If (meetingPhone.Checked = True) Then | |
| meetingPhoneInsert = "Meeting/Phone" | |
| End If | |
| 'Get the fund number associated with the project type | |
| Dim conn0 As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Time_Tracker\Time_Tracker.accdb;") | |
| conn0.Open() | |
| Dim cmd0 As New OleDbCommand("SELECT Fund_Number FROM Project_Type WHERE Project_Type = '" & projectName.Text & "'", conn0) | |
| Dim oleDA As New OleDbDataAdapter(cmd0) | |
| Dim dt As New DataTable | |
| oleDA.Fill(dt) | |
| Dim fundNumber As Integer = 0 | |
| If (dt.Rows.Count > 0) Then | |
| If (Not IsDBNull(dt.Rows(0)("Fund_Number"))) Then | |
| fundNumber = dt.Rows(0)("Fund_Number") | |
| End If | |
| Dim currentDate As Date = DateTime.Now.ToShortDateString() | |
| Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Time_Tracker\Time_Tracker.accdb;") | |
| conn.Open() | |
| Dim cmd As New OleDbCommand("Insert Into Time_Tracker(Project_User,Project_Name,Project_Time,[Project_Date],Notes,Research,Meeting_Phone,Fund_Num)Values(@name, '" & projectName.Text & "','" & span.TotalSeconds & "','" & currentDate & "','" & projectNotes.Text & "','" & researchInsert & "','" & meetingPhoneInsert & "','" & fundNumber & "')", conn) | |
| cmd.Parameters.AddWithValue("@name", SystemInformation.UserName) | |
| cmd.ExecuteNonQuery() | |
| 'MessageBox.Show("Your project has been submitted") | |
| 'Reset Project Time | |
| projectTime.Text = "00.00.00" | |
| projectNotes.Text = "" | |
| research.Checked = False | |
| meetingPhone.Checked = False | |
| Else | |
| MessageBox.Show("Your project could not be saved") | |
| End If | |
| End If | |
| End If | |
| End Sub | |
| Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick | |
| Dim devTime As TimeSpan = DateTime.Now.Subtract(timeStart) | |
| projectTime.Text = devTime.Hours.ToString("00") & ":" & devTime.Minutes.ToString("00") & ":" & devTime.Seconds.ToString("00") | |
| End Sub | |
| Private Sub reports_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles reportsButton.Click | |
| Reports.Show() | |
| Me.Hide() | |
| End Sub | |
| Private Sub ToolTip1_Popup(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PopupEventArgs) Handles ToolTip1.Popup | |
| End Sub | |
| Private Sub Panel1_Paint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PaintEventArgs) | |
| End Sub | |
| Private Sub createReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) | |
| End Sub | |
| Private Sub editTime_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles editTime.Click | |
| Dim accessApp As New Microsoft.Office.Interop.Access.Application | |
| accessApp.OpenCurrentDatabase("C:\Time_Tracker\Time_Tracker.accdb", Exclusive:=False) | |
| accessApp.Visible = True | |
| End Sub | |
| End Class | |
| '------------------------------------------------------------------------------------- | |
| 'Reports section | |
| '------------------------------------------------------------------------------------- | |
| Imports System.Data.OleDb | |
| Imports Microsoft.Office.Interop.Word | |
| Imports Microsoft.Office.Interop | |
| Imports Microsoft.Office.Interop.Excel | |
| Imports Microsoft | |
| Imports System.Text.RegularExpressions | |
| Imports System.Globalization | |
| Public Class Reports | |
| Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load | |
| 'Get user name and set the form location on the screen | |
| Dim currentUser As String = SystemInformation.UserName | |
| Dim OleConn2 As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Time_Tracker\Time_Tracker.accdb;") | |
| Dim OleCmd2 As New OleDbCommand("SELECT * FROM User_Settings WHERE User = '" & currentUser.ToString & "'", OleConn2) | |
| Dim OleDa2 As New OleDbDataAdapter(OleCmd2) | |
| Dim dt2 As New Data.DataTable | |
| OleDa2.Fill(dt2) | |
| If (dt2.Rows.Count > 0) Then | |
| Dim x = dt2.Rows(0)("Form_X") | |
| Dim y = dt2.Rows(0)("Form_Y") | |
| 'Explictly set form location | |
| Me.Location = New Size(x, y) | |
| End If | |
| 'Set the prior month value for the monthly report | |
| Dim thisMonth As New DateTime(DateTime.Today.Year, DateTime.Today.Month, 1) | |
| 'First day of the last month | |
| reportStartDate.Text = thisMonth.AddMonths(-1) | |
| 'Last day of the last month | |
| reportEndDate.Text = thisMonth.AddDays(-1) | |
| 'Month Of Report defaults to previous month | |
| 'Convert the previous date to a character string | |
| Dim previousMonth As String = Month(Now) - 1 | |
| If (previousMonth = 1) Then | |
| previousMonth = "January" | |
| monthOfReport.Text = previousMonth | |
| ElseIf (previousMonth = 2) Then | |
| previousMonth = "Febuary" | |
| monthOfReport.Text = previousMonth | |
| ElseIf (previousMonth = 3) Then | |
| previousMonth = "March" | |
| monthOfReport.Text = previousMonth | |
| ElseIf (previousMonth = 4) Then | |
| previousMonth = "April" | |
| monthOfReport.Text = previousMonth | |
| ElseIf (previousMonth = 5) Then | |
| previousMonth = "May" | |
| monthOfReport.Text = previousMonth | |
| ElseIf (previousMonth = 6) Then | |
| previousMonth = "June" | |
| ElseIf (previousMonth = 7) Then | |
| previousMonth = "July" | |
| monthOfReport.Text = previousMonth | |
| ElseIf (previousMonth = 8) Then | |
| previousMonth = "August" | |
| monthOfReport.Text = previousMonth | |
| ElseIf (previousMonth = 9) Then | |
| previousMonth = "September" | |
| ElseIf (previousMonth = 10) Then | |
| previousMonth = "October" | |
| monthOfReport.Text = previousMonth | |
| ElseIf (previousMonth = 11) Then | |
| previousMonth = "November" | |
| monthOfReport.Text = previousMonth | |
| ElseIf (previousMonth = 12) Then | |
| previousMonth = "December" | |
| monthOfReport.Text = previousMonth | |
| End If | |
| End Sub | |
| Private Sub exitButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exitButton.Click | |
| Me.Visible = False | |
| Time_Tracker.Visible = True | |
| End Sub | |
| Private Sub monthlyReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles monthlyReport.Click | |
| 'Get digit value of month selected | |
| 'If the selected month is ahead of the current month we will get the prior year data for that month | |
| 'Represents the numeric value of the text month | |
| Dim monthDigit As Integer | |
| 'Represents the current date | |
| Dim reportYear As Integer = DateTime.Now.Year | |
| 'Represents the current month | |
| Dim thisMonth As Integer = DateTime.Today.Month | |
| If (monthOfReport.Text = "January") Then | |
| monthDigit = 1 | |
| If (monthDigit > thisMonth) Then | |
| reportYear = DateTime.Now.Year - 1 | |
| End If | |
| End If | |
| If (monthOfReport.Text = "Febuary") Then | |
| monthDigit = 2 | |
| If (monthDigit > thisMonth) Then | |
| reportYear = DateTime.Now.Year - 1 | |
| End If | |
| End If | |
| If (monthOfReport.Text = "March") Then | |
| monthDigit = 3 | |
| If (monthDigit > thisMonth) Then | |
| reportYear = DateTime.Now.Year - 1 | |
| End If | |
| End If | |
| If (monthOfReport.Text = "April") Then | |
| monthDigit = 4 | |
| If (monthDigit > thisMonth) Then | |
| reportYear = DateTime.Now.Year - 1 | |
| End If | |
| End If | |
| If (monthOfReport.Text = "May") Then | |
| monthDigit = 5 | |
| If (monthDigit > thisMonth) Then | |
| reportYear = DateTime.Now.Year - 1 | |
| End If | |
| End If | |
| If (monthOfReport.Text = "June") Then | |
| monthDigit = 6 | |
| If (monthDigit > thisMonth) Then | |
| reportYear = DateTime.Now.Year - 1 | |
| End If | |
| End If | |
| If (monthOfReport.Text = "July") Then | |
| monthDigit = 7 | |
| If (monthDigit > thisMonth) Then | |
| reportYear = DateTime.Now.Year - 1 | |
| End If | |
| End If | |
| If (monthOfReport.Text = "August") Then | |
| monthDigit = 8 | |
| If (monthDigit > thisMonth) Then | |
| reportYear = DateTime.Now.Year - 1 | |
| End If | |
| End If | |
| If (monthOfReport.Text = "September") Then | |
| monthDigit = 9 | |
| If (monthDigit > thisMonth) Then | |
| reportYear = DateTime.Now.Year - 1 | |
| End If | |
| End If | |
| If (monthOfReport.Text = "October") Then | |
| monthDigit = 10 | |
| If (monthDigit > thisMonth) Then | |
| reportYear = DateTime.Now.Year - 1 | |
| End If | |
| End If | |
| If (monthOfReport.Text = "November") Then | |
| monthDigit = 11 | |
| If (monthDigit > thisMonth) Then | |
| reportYear = DateTime.Now.Year - 1 | |
| End If | |
| End If | |
| If (monthOfReport.Text = "December") Then | |
| monthDigit = 12 | |
| If (monthDigit > thisMonth) Then | |
| reportYear = DateTime.Now.Year - 1 | |
| End If | |
| End If | |
| Dim currentUser As String = SystemInformation.UserName | |
| Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Time_Tracker\Time_Tracker.accdb;") | |
| conn.Open() | |
| Dim cmd As New OleDbCommand("SELECT DISTINCT Project_User,Project_Name,Fund_Num,Sum(Project_Time) As Total,(SELECT Sum(Project_Time) As ResearchTime FROM Time_Tracker WHERE Month(Project_Date) = '" & monthOfReport.Text & "' AND Research LIKE 'Research' OR Meeting_Phone LIKE 'Meeting/Phone') As OtherTime FROM Time_Tracker WHERE Month(Project_Date) = '" & monthDigit & "' AND Year(Project_Date) = '" & reportYear & "' Group By Project_User,Project_Name,Fund_Num", conn) | |
| Dim da As New OleDbDataAdapter(cmd) | |
| Dim dt As New Data.DataTable | |
| da.Fill(dt) | |
| 'Check if ANY of the rows returns data | |
| If (dt.Rows.Count > 0) Then | |
| 'Create word application | |
| Dim excelApp As Excel.Application | |
| Dim excelWB As Excel.Workbook | |
| Dim workSheet As Excel.Worksheet | |
| Dim misValue As Object = System.Reflection.Missing.Value | |
| excelApp = New Excel.Application | |
| excelApp.Visible = True | |
| excelWB = excelApp.Workbooks.Add(misValue) | |
| workSheet = excelWB.Sheets("sheet1") | |
| workSheet = CType(excelWB.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet) | |
| 'Create Report Header | |
| Dim currentyear As String = Year(Now) | |
| workSheet.Range("A1,O1").Value = "Time Tracker - Monthly Report By Project" & Space(30) & monthOfReport.Text & Space(1) & reportYear | |
| workSheet.Range("A1,O1").Font.Bold = True | |
| workSheet.Range("A1,O1").Font.Size = "16" | |
| workSheet.Range("A1,O1").Font.FontStyle = "Georgia" | |
| 'Set column width | |
| 'row 2 | |
| workSheet.Range("A2").ColumnWidth = 30 | |
| workSheet.Range("B2").ColumnWidth = 30 | |
| workSheet.Range("C2").ColumnWidth = 30 | |
| workSheet.Range("D2").ColumnWidth = 30 | |
| workSheet.Range("E2").ColumnWidth = 30 | |
| 'workSheet.Range("F2").ColumnWidth = 30 | |
| 'Add user | |
| workSheet.Range("A2").Value = "Project User" | |
| workSheet.Range("B2").Value = dt.Rows(0)("Project_User").ToString() | |
| workSheet.Range("A2").Font.Bold = True | |
| 'Set column header width | |
| 'Create column headers | |
| workSheet.Range("A3").Value = "Project" | |
| workSheet.Range("B3").Value = "Fund Number" | |
| workSheet.Range("C3").Value = "Development Time" | |
| workSheet.Range("D3").Value = "Other Time" | |
| workSheet.Range("A3", "F3").Font.Bold = True | |
| 'Add row 1 data | |
| workSheet.Range("A4").Value = dt.Rows(0)("Project_Name").ToString() | |
| workSheet.Range("B4").Value = dt.Rows(0)("Fund_Num").ToString() | |
| 'Format Dev Time Row 1 | |
| Dim t As Long = dt.Rows(0)("Total") / 3600 | |
| Dim total As String = t.ToString() | |
| workSheet.Range("C4").Value = total | |
| 'Get other time for the month | |
| If (IsDBNull(dt.Rows(0)("OtherTime"))) Then | |
| workSheet.Range("D4").Value = "N/A" & Space(1) & monthOfReport.Text | |
| Else | |
| 'Dim ot As Long = dt.Rows(0)("OtherTime") / 3600 | |
| 'Dim otherTotal As String = ot.ToString() | |
| workSheet.Range("D4").Value = dt.Rows(0)("Total").ToString("00:00:00") | |
| End If | |
| 'If the dt has data, there's a small chance no other rows will have data after row 1 | |
| 'so me must check if the dt is greater than the next row we are retreiving | |
| 'If not we exit the sub | |
| If (Not dt.Rows.Count > 1) Then | |
| Exit Sub | |
| Else | |
| 'Add row 2 data | |
| workSheet.Range("A5").Value = dt.Rows(1)("Project_Name").ToString() | |
| workSheet.Range("B5").Value = dt.Rows(1)("Fund_Num").ToString() | |
| 'Format Dev Time Row 2 | |
| Dim t1 As Long = dt.Rows(1)("Total") / 3600 | |
| Dim total1 As String = t1.ToString() | |
| workSheet.Range("C5").Value = total1 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(1)("OtherTime"))) Then | |
| workSheet.Range("D5").Value = "N/A" | |
| Else | |
| 'Format other time | |
| Dim ot1 As Long = dt.Rows(1)("OtherTime") / 3600 | |
| Dim otherTotal1 As String = ot1.ToString() | |
| workSheet.Range("D5").Value = otherTotal1 | |
| End If | |
| End If | |
| If (Not dt.Rows.Count > 2) Then | |
| Exit Sub | |
| Else | |
| 'Add row 3 data | |
| workSheet.Range("A6").Value = dt.Rows(2)("Project_Name").ToString() | |
| workSheet.Range("B6").Value = dt.Rows(2)("Fund_Num").ToString() | |
| 'Format Dev Time Row 3 | |
| Dim t2 As Long = dt.Rows(2)("Total") / 3600 | |
| Dim total2 As String = t2.ToString() | |
| workSheet.Range("C6").Value = total2 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(2)("OtherTime"))) Then | |
| workSheet.Range("D6").Value = "N/A" | |
| Else | |
| 'Format other time | |
| Dim ot2 As Long = dt.Rows(2)("OtherTime") / 3600 | |
| Dim otherTotal2 As String = ot2.ToString() | |
| workSheet.Range("D6").Value = otherTotal2 | |
| End If | |
| End If | |
| If (Not dt.Rows.Count > 3) Then | |
| Exit Sub | |
| Else | |
| 'Add row 4 | |
| workSheet.Range("A7").Value = dt.Rows(3)("Project_Name").ToString() | |
| workSheet.Range("B7").Value = dt.Rows(3)("Fund_Number").ToString() | |
| 'Format Time Row 4 | |
| Dim t3 As Long = dt.Rows(3)("Total") / 3600 | |
| Dim total3 As String = t3.ToString() | |
| workSheet.Range("C7").Value = total3.ToString() | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(3)("OtherTime"))) Then | |
| workSheet.Range("D7").Value = "N/A" | |
| Else | |
| 'Format other time | |
| Dim ot3 As Long = dt.Rows(2)("OtherTime") / 3600 | |
| Dim otherTotal3 As String = ot3.ToString() | |
| workSheet.Range("D7").Value = otherTotal3.ToString() | |
| End If | |
| End If | |
| If (Not dt.Rows.Count > 4) Then | |
| Exit Sub | |
| Else | |
| 'Add row 5 | |
| workSheet.Range("A8").Value = dt.Rows(4)("Project_Name").ToString() | |
| workSheet.Range("B8").Value = dt.Rows(4)("Fund_Num").ToString() | |
| 'Format Time Row 5 | |
| Dim t4 As Long = dt.Rows(4)("Total") / 3600 | |
| Dim total4 As String = t4.ToString() | |
| workSheet.Range("C8").Value = total4 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(4)("OtherTime"))) Then | |
| workSheet.Range("D8").Value = "N/A" | |
| Else | |
| 'Format other time | |
| Dim ot4 As Long = dt.Rows(2)("OtherTime") / 3600 | |
| Dim otherTotal4 As String = ot4.ToString() | |
| workSheet.Range("D8").Value = otherTotal4 | |
| End If | |
| End If | |
| If (Not dt.Rows.Count > 5) Then | |
| Exit Sub | |
| Else | |
| 'Add row 6 | |
| workSheet.Range("A9").Value = dt.Rows(5)("Project_Name").ToString() | |
| workSheet.Range("B9").Value = dt.Rows(5)("Fund_Num").ToString() | |
| 'Format Time Row 6 | |
| Dim t5 As Long = dt.Rows(5)("Total") / 3600 | |
| Dim total5 As String = t5.ToString() | |
| workSheet.Range("C9").Value = total5 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(5)("OtherTime"))) Then | |
| workSheet.Range("D9").Value = "N/A" | |
| Else | |
| 'Format other time | |
| Dim ot5 As Long = dt.Rows(5)("OtherTime") / 3600 | |
| Dim otherTotal5 As String = ot5.ToString() | |
| workSheet.Range("D9").Value = otherTotal5 | |
| End If | |
| End If | |
| If (Not dt.Rows.Count > 6) Then | |
| Exit Sub | |
| Else | |
| 'Add row 7 | |
| workSheet.Range("A10").Value = dt.Rows(6)("Project_Name").ToString() | |
| workSheet.Range("B10").Value = dt.Rows(6)("Fund_Number").ToString() | |
| 'Format Time Row 7 | |
| Dim t6 As Long = dt.Rows(6)("Total") / 3600 | |
| Dim total6 As String = t6.ToString() | |
| workSheet.Range("C10").Value = total6 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(6)("OtherTime"))) Then | |
| workSheet.Range("D10").Value = "N/A" | |
| Else | |
| 'Format other time | |
| Dim ot6 As Long = dt.Rows(6)("OtherTime") / 3600 | |
| Dim otherTotal6 As String = ot6.ToString() | |
| workSheet.Range("D10").Value = otherTotal6 | |
| End If | |
| End If | |
| If (Not dt.Rows.Count > 7) Then | |
| Exit Sub | |
| Else | |
| 'Add row 8 | |
| workSheet.Range("A11").Value = dt.Rows(7)("Project_Name").ToString() | |
| workSheet.Range("B11").Value = dt.Rows(7)("Fund_Num").ToString() | |
| 'Format Time Row 8 | |
| Dim t7 As Long = dt.Rows(7)("Total") / 3600 | |
| Dim total7 As String = t7.ToString() | |
| workSheet.Range("C11").Value = total7 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(7)("OtherTime"))) Then | |
| workSheet.Range("D11").Value = "N/A" | |
| Else | |
| 'Format other time | |
| Dim ot7 As Long = dt.Rows(7)("OtherTime") / 3600 | |
| Dim otherTotal7 As String = ot7.ToString() | |
| workSheet.Range("D11").Value = otherTotal7 | |
| End If | |
| End If | |
| If (Not dt.Rows.Count > 8) Then | |
| Exit Sub | |
| Else | |
| 'Add row 9 | |
| workSheet.Range("A12").Value = dt.Rows(8)("Project_Name").ToString() | |
| workSheet.Range("B12").Value = dt.Rows(8)("Fund_Num").ToString() | |
| 'Format Dev Time Row 9 | |
| Dim t8 As Long = dt.Rows(8)("Total") / 3600 | |
| Dim total8 As String = t8.ToString() | |
| workSheet.Range("C12").Value = total8 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(8)("OtherTime"))) Then | |
| workSheet.Range("D12").Value = "N/A" | |
| Else | |
| 'Format othe time | |
| Dim ot8 As Long = dt.Rows(8)("OtherTime") / 3600 | |
| Dim otherTotal8 As String = ot8.ToString() | |
| workSheet.Range("D12").Value = otherTotal8 | |
| End If | |
| End If | |
| If (Not dt.Rows.Count > 9) Then | |
| Exit Sub | |
| Else | |
| 'Add row 10 | |
| workSheet.Range("A13").Value = dt.Rows(9)("Project_Name").ToString() | |
| workSheet.Range("B13").Value = dt.Rows(9)("Fund_Num").ToString() | |
| 'Format Time Row 10 | |
| Dim t9 As Long = dt.Rows(9)("Total") / 3600 | |
| Dim total9 As String = t9.ToString() | |
| workSheet.Range("C13").Value = total9 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(9)("OtherTime"))) Then | |
| workSheet.Range("D13").Value = "N/A" | |
| Else | |
| Dim ot9 As Long = dt.Rows(9)("OtherTime") / 3600 | |
| Dim otherTotal9 As String = ot9.ToString() | |
| workSheet.Range("D13").Value = total9 | |
| End If | |
| End If | |
| If (Not dt.Rows.Count > 10) Then | |
| Exit Sub | |
| Else | |
| 'Add Row 11 | |
| workSheet.Range("A14").Value = dt.Rows(10)("Project_Name").ToString() | |
| workSheet.Range("B14").Value = dt.Rows(10)("Fund_Num").ToString() | |
| 'Format Time Row 11 | |
| Dim t10 As Long = dt.Rows(10)("Total") / 3600 | |
| Dim total10 As String = t10.ToString() | |
| workSheet.Range("C14").Value = total10 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(10)("OtherTime"))) Then | |
| workSheet.Range("D14").Value = "N/A" | |
| Else | |
| Dim ot10 As Long = dt.Rows(10)("OtherTime") / 3600 | |
| Dim otherTotal10 As String = ot10.ToString() | |
| workSheet.Range("D14").Value = total10 | |
| End If | |
| End If | |
| If (Not dt.Rows.Count > 11) Then | |
| Exit Sub | |
| Else | |
| 'Add Row 12 | |
| workSheet.Range("A15").Value = dt.Rows(11)("Project_Name").ToString() | |
| workSheet.Range("B15").Value = dt.Rows(11)("Fund_Num").ToString() | |
| 'Format Time Row 12 | |
| Dim t11 As Long = dt.Rows(11)("Total") / 3600 | |
| Dim total11 As String = t11.ToString() | |
| workSheet.Range("C15").Value = total11 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(11)("OtherTime"))) Then | |
| workSheet.Range("D15").Value = "N/A" | |
| Else | |
| Dim ot11 As Long = dt.Rows(11)("OtherTime") / 3600 | |
| Dim otherTotal11 As String = ot11.ToString() | |
| workSheet.Range("D15").Value = total11 | |
| End If | |
| End If | |
| If (Not dt.Rows.Count > 12) Then | |
| Exit Sub | |
| Else | |
| 'Add Row 13 | |
| workSheet.Range("A16").Value = dt.Rows(12)("Project_Name").ToString() | |
| workSheet.Range("B16").Value = dt.Rows(12)("Fund_Num").ToString() | |
| 'Format Time Row 13 | |
| Dim t12 As Long = dt.Rows(12)("Total") / 3600 | |
| Dim total12 As String = t12.ToString() | |
| workSheet.Range("C16").Value = total12 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(12)("OtherTime"))) Then | |
| workSheet.Range("D16").Value = "N/A" | |
| Else | |
| Dim ot12 As Long = dt.Rows(12)("OtherTime") / 3600 | |
| Dim otherTotal12 As String = ot12.ToString() | |
| workSheet.Range("D16").Value = total12.ToString() | |
| End If | |
| End If | |
| If (Not dt.Rows.Count > 13) Then | |
| Exit Sub | |
| Else | |
| 'Add Row 14 | |
| workSheet.Range("A17").Value = dt.Rows(13)("Project_Name").ToString() | |
| workSheet.Range("B17").Value = dt.Rows(13)("Fund_Num").ToString() | |
| 'Format Time Row 14 | |
| Dim t13 As Long = dt.Rows(13)("Total") / 3600 | |
| Dim total13 As String = t13.ToString() | |
| workSheet.Range("C17").Value = total13 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(13)("OtherTime"))) Then | |
| workSheet.Range("D17").Value = "N/A" | |
| Else | |
| Dim ot13 As Long = dt.Rows(13)("OtherTime") / 3600 | |
| Dim otherTotal13 As String = ot13.ToString() | |
| workSheet.Range("D17").Value = total13.ToString() | |
| End If | |
| End If | |
| If (dt.Rows.Count > 14) Then | |
| Exit Sub | |
| Else | |
| 'Add Row 15 | |
| workSheet.Range("A18").Value = dt.Rows(14)("Project_Name").ToString() | |
| workSheet.Range("B18").Value = dt.Rows(14)("Fund_Num").ToString() | |
| 'Format Time Row 15 | |
| Dim t14 As Long = dt.Rows(14)("Total") / 3600 | |
| Dim total14 As String = t14.ToString() | |
| workSheet.Range("C18").Value = total14 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(14)("OtherTime"))) Then | |
| workSheet.Range("D18").Value = "N/A" | |
| Else | |
| Dim ot14 As Long = dt.Rows(14)("OtherTime") / 3600 | |
| Dim otherTotal14 As String = ot14.ToString() | |
| workSheet.Range("D18").Value = otherTotal14.ToString() | |
| End If | |
| End If | |
| Else | |
| MessageBox.Show("No data matched your criteria") | |
| End If | |
| End Sub | |
| Private Sub createReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles createReport.Click | |
| Dim currentUser As String = SystemInformation.UserName | |
| 'Check if the converted start date is a proper date value | |
| If (IsDate(reportStartDate.Text)) Then | |
| 'do nothing | |
| Else | |
| MessageBox.Show("Incorrect date value") | |
| End If | |
| 'Check if the converted end date is a proper date value | |
| If (IsDate(reportEndDate.Text)) Then | |
| 'do nothing | |
| Else | |
| MessageBox.Show("Incorrect date value") | |
| End If | |
| Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Time_Tracker\Time_Tracker.accdb") | |
| conn.Open() | |
| Dim cmd As New OleDbCommand("SELECT DISTINCT Project_User,Project_Name,Fund_Num,(SELECT Sum(Project_Time) FROM Time_Tracker) As Total,(SELECT SUM(Project_Time) FROM Time_Tracker WHERE Research = 'Research' OR Meeting_Phone = 'Meeting/Phone') AS OtherTime,(SELECT MIN(Project_Date) FROM Time_Tracker WHERE Project_Date BETWEEN @ReportStart AND @ReportEnd) As MinDate,(SELECT MAX(Project_Date) FROM Time_Tracker WHERE Project_Date BETWEEN @ReportStart AND @ReportEnd) As MaxDate FROM Time_Tracker WHERE Project_Date BETWEEN @ReportStart AND @ReportEnd Group By Project_User,Project_Name,Fund_Num ", conn) | |
| cmd.Parameters.Add(New OleDbParameter("@ReportStart", OleDbType.DBDate)).Value = reportStartDate.Text | |
| cmd.Parameters.Add(New OleDbParameter("@ReportEnd", OleDbType.DBDate)).Value = reportEndDate.Text | |
| cmd.Parameters.Add(New OleDbParameter("@ReportStart1", OleDbType.DBDate)).Value = reportStartDate.Text | |
| cmd.Parameters.Add(New OleDbParameter("@ReportEnd1", OleDbType.DBDate)).Value = reportEndDate.Text | |
| Dim da As New OleDbDataAdapter(cmd) | |
| Dim dt As New Data.DataTable | |
| da.Fill(dt) | |
| 'Check if ANY of the rows returns data | |
| If (dt.Rows.Count = 0) Then | |
| MessageBox.Show("No data matched your criteria") | |
| Else | |
| If (dt.Rows.Count > 0) Then | |
| 'Create word application | |
| Dim excelApp As Excel.Application | |
| Dim excelWB As Excel.Workbook | |
| Dim workSheet As Excel.Worksheet | |
| Dim misValue As Object = System.Reflection.Missing.Value | |
| excelApp = New Excel.Application | |
| excelApp.Visible = True | |
| excelWB = excelApp.Workbooks.Add(misValue) | |
| workSheet = excelWB.Sheets("sheet1") | |
| workSheet = CType(excelWB.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet) | |
| 'Create Report Header | |
| workSheet.Range("A1").Value = "Time Tracker - By Date Range" & Space(30) & "For" & Space(1) & reportStartDate.Text & Space(1) & "-" & Space(1) & reportEndDate.Text | |
| workSheet.Range("A1").Font.Bold = True | |
| workSheet.Range("A1").Font.Size = "16" | |
| workSheet.Range("A1").Font.FontStyle = "Georgia" | |
| 'Set column width | |
| workSheet.Range("A2").ColumnWidth = 30 | |
| workSheet.Range("B2").ColumnWidth = 30 | |
| workSheet.Range("C2").ColumnWidth = 30 | |
| workSheet.Range("D2").ColumnWidth = 50 | |
| workSheet.Range("E2").ColumnWidth = 50 | |
| 'Add user | |
| workSheet.Range("A2").Value = "Project User" | |
| workSheet.Range("B2").Value = dt.Rows(0)("Project_User").ToString() | |
| workSheet.Range("A2").Font.Bold = True | |
| 'Create column headers | |
| workSheet.Range("A3").Value = "Project" | |
| workSheet.Range("B3").Value = "Fund Number" | |
| workSheet.Range("C3").Value = "Development Time" | |
| workSheet.Range("D3").Value = "Other Time" | |
| workSheet.Range("E3").Value = "Period" | |
| workSheet.Range("A3", "F3").Font.Bold = True | |
| 'Add row 1 data | |
| 'Format Dev Time Row 2 | |
| Dim t As Long = dt.Rows(0)("Total") / 3600 | |
| Dim total As String = t.ToString() | |
| workSheet.Range("A4").Value = dt.Rows(0)("Project_Name").ToString() | |
| workSheet.Range("B4").Value = dt.Rows(0)("Fund_Num").ToString() | |
| workSheet.Range("C4").Value = total.ToString() | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(0)("OtherTime"))) Then | |
| workSheet.Range("D4").Value = "N/A" | |
| Else | |
| 'Format Other Time | |
| Dim ot As Long = dt.Rows(0)("OtherTime") / 3600 | |
| Dim otherTotal As String = ot.ToString() | |
| workSheet.Range("D4").Value = otherTotal | |
| End If | |
| 'Add project period | |
| Dim minDate As Date = Date.Parse(dt.Rows(0)("MinDate")) | |
| Dim maxDate As Date = Date.Parse(dt.Rows(0)("MaxDate")) | |
| workSheet.Range("E4").Value = minDate.ToShortDateString() & Space(1) & "-" & Space(1) & maxDate.ToShortDateString() | |
| 'If the dt has data, there's a small chance no other rows will have data after row 1 | |
| 'so me must check if the dt is greater than the next row we are retreiving | |
| 'We will hardcode the rows up to a certain amount and exit if any hard coded row is empty | |
| If (Not dt.Rows.Count > 1) Then | |
| Exit Sub | |
| Else | |
| 'Format Dev Time Row 2 | |
| Dim t1 As Long = dt.Rows(1)("Total") / 3600 | |
| Dim total1 As String = t1.ToString() | |
| 'Add row 2 data | |
| workSheet.Range("A5").Value = dt.Rows(1)("Project_Name").ToString() | |
| workSheet.Range("B5").Value = dt.Rows(1)("Fund_Num").ToString() | |
| workSheet.Range("C5").Value = total1 | |
| 'Add other time | |
| If (IsDBNull(dt.Rows(1)("OtherTime"))) Then | |
| workSheet.Range("D5").Value = "N/A" | |
| Else | |
| 'Format other time | |
| Dim ot1 As Long = dt.Rows(1)("OtherTime") / 3600 | |
| Dim otherTotal1 As String = ot1.ToString() | |
| workSheet.Range("D5").Value = otherTotal1 | |
| End If | |
| 'Get project period | |
| Dim minDate1 As Date = Date.Parse(dt.Rows(1)("MinDate")) | |
| Dim maxDate1 As Date = Date.Parse(dt.Rows(1)("MaxDate")) | |
| workSheet.Range("E5").Value = minDate1.ToShortDateString() & Space(1) & "-" & Space(1) & maxDate1.ToShortDateString() | |
| End If | |
| If (Not dt.Rows.Count > 2) Then | |
| Exit Sub | |
| Else | |
| 'Format Dev Time Row 3 | |
| Dim t2 As Long = dt.Rows(2)("Total") / 3600 | |
| Dim total2 As String = t2.ToString() | |
| 'Add row 3 data | |
| workSheet.Range("A6").Value = dt.Rows(2)("Project_Name").ToString() | |
| workSheet.Range("B6").Value = dt.Rows(2)("Fund_Num").ToString() | |
| workSheet.Range("C6").Value = total2.ToString() | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(2)("OtherTime"))) Then | |
| workSheet.Range("D6").Value = "N/A" | |
| Else | |
| 'Format other time | |
| Dim ot2 As Long = dt.Rows(2)("OtherTime") / 3600 | |
| Dim otherTotal2 As String = ot2.ToString() | |
| workSheet.Range("D6").Value = otherTotal2.ToString() | |
| End If | |
| 'Add project period | |
| Dim minDate2 As Date = Date.Parse(dt.Rows(2)("MinDate")) | |
| Dim maxDate2 As Date = Date.Parse(dt.Rows(2)("MaxDate")) | |
| workSheet.Range("E6").Value = minDate2.ToShortDateString() & Space(1) & "-" & Space(1) & maxDate2.ToShortDateString() | |
| End If | |
| If (Not dt.Rows.Count > 3) Then | |
| Exit Sub | |
| Else | |
| 'Format Dev Time Row 4 | |
| Dim t3 As Long = dt.Rows(3)("Total") / 3600 | |
| Dim total3 As String = t3.ToString() | |
| 'Add row 4 | |
| workSheet.Range("A7").Value = dt.Rows(3)("Project_Name").ToString() | |
| workSheet.Range("B7").Value = dt.Rows(3)("Fund_Num").ToString() | |
| workSheet.Range("C7").Value = total3.ToString() | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(3)("OtherTime"))) Then | |
| workSheet.Range("D7").Value = "N/A" | |
| Else | |
| 'Format other time | |
| Dim ot3 As Long = dt.Rows(3)("OtherTime") / 3600 | |
| Dim otherTotal3 As String = ot3.ToString() | |
| workSheet.Range("D7").Value = otherTotal3 | |
| End If | |
| 'Get project period | |
| Dim minDate3 As Date = Date.Parse(dt.Rows(3)("MinDate")) | |
| Dim maxDate3 As Date = Date.Parse(dt.Rows(3)("MaxDate")) | |
| workSheet.Range("E7").Value = minDate3.ToShortDateString() & Space(1) & "-" & Space(1) & maxDate3.ToShortDateString() | |
| End If | |
| If (Not dt.Rows.Count > 4) Then | |
| Exit Sub | |
| Else | |
| 'Format Dev Time Row 5 | |
| Dim t4 As Long = dt.Rows(4)("Total") / 3600 | |
| Dim total4 As String = t4.ToString() | |
| 'Add row 5 | |
| workSheet.Range("A8").Value = dt.Rows(4)("Project_Name").ToString() | |
| workSheet.Range("B8").Value = dt.Rows(4)("Fund_Num").ToString() | |
| workSheet.Range("C8").Value = total4 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(4)("OtherTime"))) Then | |
| workSheet.Range("D8").Value = "N/A" | |
| Else | |
| 'Format other time | |
| Dim ot4 As Long = dt.Rows(4)("OtherTime") / 3600 | |
| Dim otherTotal4 As String = ot4.ToString() | |
| workSheet.Range("D8").Value = "N/A" | |
| End If | |
| 'Add project period | |
| Dim minDate4 As Date = Date.Parse(dt.Rows(4)("MinDate")) | |
| Dim maxDate4 As Date = Date.Parse(dt.Rows(4)("MaxDate")) | |
| workSheet.Range("E8").Value = minDate4.ToShortDateString() & Space(1) & "-" & Space(1) & maxDate4.ToShortDateString() | |
| End If | |
| If (Not dt.Rows.Count > 5) Then | |
| Exit Sub | |
| Else | |
| 'Format Dev Time Row 6 | |
| Dim t5 As Long = dt.Rows(5)("Total") / 3600 | |
| Dim total5 As String = t5.ToString() | |
| 'Add row 6 | |
| workSheet.Range("A9").Value = dt.Rows(5)("Project_Name").ToString() | |
| workSheet.Range("B9").Value = dt.Rows(5)("Fund_Num").ToString() | |
| workSheet.Range("C9").Value = total5 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(5)("OtherTime"))) Then | |
| workSheet.Range("D9").Value = "N/A" | |
| Else | |
| 'Format other time | |
| Dim ot5 As Long = dt.Rows(5)("OtherTime") / 3600 | |
| Dim otherTotal5 As String = ot5.ToString() | |
| workSheet.Range("D9").Value = otherTotal5 | |
| End If | |
| 'Get project period | |
| Dim minDate5 As Date = Date.Parse(dt.Rows(1)("MinDate")) | |
| Dim maxDate5 As Date = Date.Parse(dt.Rows(1)("MaxDate")) | |
| workSheet.Range("E9").Value = minDate5.ToShortDateString() & Space(1) & "-" & Space(1) & maxDate5.ToShortDateString() | |
| End If | |
| If (Not dt.Rows.Count > 6) Then | |
| Exit Sub | |
| Else | |
| 'Format Dev Time Row 7 | |
| Dim t6 As Long = dt.Rows(6)("Total") / 3600 | |
| Dim total6 As String = t6.ToString() | |
| 'Add row 7 | |
| workSheet.Range("A10").Value = dt.Rows(6)("Project_Name").ToString() | |
| workSheet.Range("B10").Value = dt.Rows(6)("Fund_Nunm").ToString() | |
| workSheet.Range("C10").Value = total6 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(6)("OtherTime"))) Then | |
| workSheet.Range("D10").Value = "N/A" | |
| Else | |
| 'Fomrat other time | |
| Dim ot6 As Long = dt.Rows(6)("OtherTime") / 3600 | |
| Dim otherTotal6 As String = ot6.ToString() | |
| workSheet.Range("D10").Value = otherTotal6 | |
| 'Get project period | |
| Dim minDate6 As Date = Date.Parse(dt.Rows(6)("MinDate")) | |
| Dim maxDate6 As Date = Date.Parse(dt.Rows(6)("MaxDate")) | |
| workSheet.Range("E10").Value = minDate6.ToShortDateString() & Space(1) & "-" & Space(1) & maxDate6.ToShortDateString() | |
| End If | |
| If (Not dt.Rows.Count > 7) Then | |
| Exit Sub | |
| Else | |
| 'Format Dev Time Row 8 | |
| Dim t7 As Long = dt.Rows(7)("Total") / 3600 | |
| Dim total7 As String = t7.ToString() | |
| 'Add row 8 | |
| workSheet.Range("A11").Value = dt.Rows(7)("Project_Name").ToString() | |
| workSheet.Range("B11").Value = dt.Rows(7)("Fund_Num").ToString() | |
| workSheet.Range("C11").Value = total7 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(7)("OtherTime"))) Then | |
| workSheet.Range("D11").Value = "N/A" | |
| Else | |
| 'Format other time | |
| Dim ot7 As Long = dt.Rows(1)("OtherTime") / 3600 | |
| Dim otherTotal7 As String = ot7.ToString() | |
| workSheet.Range("D11").Value = otherTotal7 | |
| End If | |
| 'Get project period | |
| Dim minDate7 As Date = Date.Parse(dt.Rows(7)("MinDate")) | |
| Dim maxDate7 As Date = Date.Parse(dt.Rows(7)("MaxDate")) | |
| workSheet.Range("E11").Value = minDate7.ToShortDateString() & Space(1) & "-" & Space(1) & maxDate7.ToShortDateString() | |
| End If | |
| If (Not dt.Rows.Count > 8) Then | |
| Exit Sub | |
| Else | |
| 'Format Dev Time Row 9 | |
| Dim t8 As Long = dt.Rows(8)("Total") / 3600 | |
| Dim total8 As String = t8.ToString() | |
| 'Add row 9 | |
| workSheet.Range("A12").Value = dt.Rows(8)("Project_Name").ToString() | |
| workSheet.Range("B12").Value = dt.Rows(8)("Fund_Number").ToString() | |
| workSheet.Range("C12").Value = total8 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(8)("OtherTime"))) Then | |
| workSheet.Range("D12").Value = "N/A" | |
| Else | |
| 'Format other time | |
| Dim ot8 As Long = dt.Rows(8)("OtherTime") / 3600 | |
| Dim otherTotal8 As String = ot8.ToString() | |
| workSheet.Range("D12").Value = otherTotal8 | |
| End If | |
| 'Get project period | |
| Dim minDate8 As Date = Date.Parse(dt.Rows(8)("MinDate")) | |
| Dim maxDate8 As Date = Date.Parse(dt.Rows(8)("MaxDate")) | |
| workSheet.Range("E12").Value = minDate8.ToShortDateString() & Space(1) & "-" & Space(1) & maxDate8.ToShortDateString() | |
| End If | |
| If (Not dt.Rows.Count > 9) Then | |
| Exit Sub | |
| Else | |
| 'Format Dev Time Row 10 | |
| Dim t9 As Long = dt.Rows(9)("Total") / 3600 | |
| Dim total9 As String = t9.ToString() | |
| 'Add row 10 | |
| workSheet.Range("A13").Value = dt.Rows(9)("Project_Name").ToString() | |
| workSheet.Range("B13").Value = dt.Rows(9)("Fund_Num").ToString() | |
| workSheet.Range("C13").Value = total9 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(9)("OtherTime"))) Then | |
| workSheet.Range("D13").Value = "N/A" | |
| Else | |
| 'Format other time | |
| Dim ot9 As Long = dt.Rows(9)("OtherTime") / 3600 | |
| Dim otherTotal9 As String = ot9.ToString() | |
| workSheet.Range("D13").Value = otherTotal9 | |
| End If | |
| 'Get project period | |
| Dim minDate9 As Date = Date.Parse(dt.Rows(9)("MinDate")) | |
| Dim maxDate9 As Date = Date.Parse(dt.Rows(9)("MaxDate")) | |
| workSheet.Range("E13").Value = minDate9.ToShortDateString() & Space(1) & "-" & Space(1) & maxDate9.ToShortDateString() | |
| End If | |
| If (Not dt.Rows.Count > 10) Then | |
| Exit Sub | |
| Else | |
| 'Format Dev Time Row 11 | |
| Dim t10 As Long = dt.Rows(10)("Total") / 3600 | |
| Dim total10 As String = t10.ToString() | |
| 'Add Row 11 | |
| workSheet.Range("A14").Value = dt.Rows(10)("Project_Name").ToString() | |
| workSheet.Range("B14").Value = dt.Rows(10)("Fund_Num").ToString() | |
| workSheet.Range("C14").Value = total10 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(10)("OtherTime"))) Then | |
| workSheet.Range("D14").Value = "N/A" | |
| Else | |
| 'Format other time | |
| Dim ot10 As Long = dt.Rows(10)("OtherTime") / 3600 | |
| Dim otherTotal10 As String = ot10.ToString() | |
| workSheet.Range("D14").Value = otherTotal10.ToString() | |
| End If | |
| 'Get project period | |
| Dim minDate10 As Date = Date.Parse(dt.Rows(10)("MinDate")) | |
| Dim maxDate10 As Date = Date.Parse(dt.Rows(10)("MaxDate")) | |
| workSheet.Range("E14").Value = minDate10.ToShortDateString() & Space(1) & "-" & Space(1) & maxDate10.ToShortDateString() | |
| End If | |
| If (Not dt.Rows.Count > 11) Then | |
| Exit Sub | |
| Else | |
| 'Format Dev Time Row 12 | |
| Dim t11 As Long = dt.Rows(11)("Total") / 3600 | |
| Dim total11 As String = t11.ToString() | |
| 'Add Row 12 | |
| workSheet.Range("A15").Value = dt.Rows(11)("Project_Name").ToString() | |
| workSheet.Range("B15").Value = dt.Rows(11)("Fund_Num").ToString() | |
| workSheet.Range("C15").Value = total11 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(11)("OtherTime"))) Then | |
| workSheet.Range("D15").Value = "N/A" | |
| Else | |
| 'Format other time | |
| Dim ot11 As Long = dt.Rows(11)("OtherTime") / 3600 | |
| Dim otherTotal11 As String = ot11.ToString() | |
| workSheet.Range("D15").Value = otherTotal11 | |
| End If | |
| 'Get project period | |
| Dim minDate11 As Date = Date.Parse(dt.Rows(11)("MinDate")) | |
| Dim maxDate11 As Date = Date.Parse(dt.Rows(11)("MaxDate")) | |
| workSheet.Range("E15").Value = minDate11.ToShortDateString() & Space(1) & "-" & Space(1) & maxDate11.ToShortDateString() | |
| End If | |
| If (Not dt.Rows.Count > 12) Then | |
| Exit Sub | |
| Else | |
| 'Format Dev Time Row 13 | |
| Dim t12 As Long = dt.Rows(12)("Total") / 3600 | |
| Dim total12 As String = t12.ToString() | |
| 'Add Row 13 | |
| workSheet.Range("A16").Value = dt.Rows(12)("Project_Name").ToString() | |
| workSheet.Range("B16").Value = dt.Rows(12)("Fund_Num").ToString() | |
| workSheet.Range("C16").Value = total12.ToString() | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(12)("OtherTime"))) Then | |
| workSheet.Range("D16").Value = "N/A" | |
| Else | |
| 'Format other time | |
| Dim ot12 As Long = dt.Rows(12)("OtherTime") / 3600 | |
| Dim otherTotal12 As String = ot12.ToString() | |
| workSheet.Range("D16").Value = otherTotal12 | |
| End If | |
| 'Get project period | |
| Dim minDate12 As Date = Date.Parse(dt.Rows(12)("MinDate")) | |
| Dim maxDate12 As Date = Date.Parse(dt.Rows(12)("MaxDate")) | |
| workSheet.Range("E16").Value = minDate12.ToShortDateString() & Space(1) & "-" & Space(1) & maxDate12.ToShortDateString() | |
| End If | |
| If (Not dt.Rows.Count > 13) Then | |
| Exit Sub | |
| Else | |
| 'Format Dev Time Row 14 | |
| Dim t13 As Long = dt.Rows(13)("Total") / 3600 | |
| Dim total13 As String = t13.ToString() | |
| 'Add Row 14 | |
| workSheet.Range("A17").Value = dt.Rows(13)("Project_Name").ToString() | |
| workSheet.Range("B17").Value = dt.Rows(13)("Fund_Num").ToString() | |
| workSheet.Range("C17").Value = total13 | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(13)("OtherTime"))) Then | |
| workSheet.Range("D17").Value = "N/A" | |
| Else | |
| 'Format other time | |
| Dim ot13 As Long = dt.Rows(13)("OtherTime") / 3600 | |
| Dim otherTotal13 As String = ot13.ToString() | |
| workSheet.Range("D17").Value = otherTotal13 | |
| End If | |
| 'Get project period | |
| Dim minDate13 As Date = Date.Parse(dt.Rows(13)("MinDate")) | |
| Dim maxDate13 As Date = Date.Parse(dt.Rows(13)("MaxDate")) | |
| workSheet.Range("E17").Value = minDate13.ToShortDateString() & Space(1) & "-" & Space(1) & maxDate13.ToShortDateString() | |
| End If | |
| If (dt.Rows.Count > 14) Then | |
| Exit Sub | |
| Else | |
| 'Format Dev Time Row 15 | |
| Dim t14 As Long = dt.Rows(14)("Total") / 3600 | |
| Dim total14 As String = t14.ToString() | |
| 'Add Row 15 | |
| workSheet.Range("A18").Value = dt.Rows(14)("Project_Name").ToString() | |
| workSheet.Range("B18").Value = dt.Rows(14)("Fund_Num").ToString() | |
| workSheet.Range("C18").Value = total14.ToString() | |
| 'Get other time | |
| If (IsDBNull(dt.Rows(14)("OtherTime"))) Then | |
| workSheet.Range("D18").Value = "N/A" | |
| Else | |
| Dim ot14 As Long = dt.Rows(14)("OtherTime") / 3600 | |
| Dim otherTotal14 As String = ot14.ToString() | |
| workSheet.Range("D18").Value = otherTotal14 | |
| End If | |
| 'Get project period | |
| Dim minDate14 As Date = Date.Parse(dt.Rows(14)("MinDate")) | |
| Dim maxDate14 As Date = Date.Parse(dt.Rows(14)("MaxDate")) | |
| workSheet.Range("E18").Value = minDate14.ToShortDateString() & Space(1) & "-" & Space(1) & maxDate14.ToShortDateString() | |
| End If | |
| End If | |
| End If | |
| End If | |
| End Sub | |
| Private Sub reportStartDate_LostFocus(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles reportStartDate.LostFocus | |
| If (Not Regex.Match(reportStartDate.Text, "^(([1-9])|(0[1-9])|(1[0-2]))\/(([0-9])|([0-2][0-9])|(3[0-1]))\/(([0-9][0-9])|([1-2][0,9][0-9][0-9]))$").Success) Then | |
| Dim rDate As String = reportStartDate.Text.Trim | |
| 'Iterate through all possible date scenarios and properly appending the necessary characters to create propert short string format | |
| 'If date format is #/# | |
| If (rDate.Length = 3 And IsNumeric(rDate.Length - 1)) Then | |
| reportStartDate.Text = rDate & "/" & Now().Year | |
| End If | |
| 'If date format is #/#/ | |
| If (rDate.Length = 4 And IsNumeric(rDate.Length - 1) = False) Then | |
| reportStartDate.Text = rDate & Now().Year | |
| End If | |
| 'If date format is #/## or ##/# | |
| If (rDate.Length = 4 And IsNumeric(rDate.Length - 1)) Then | |
| reportStartDate.Text = rDate & Now().Year | |
| End If | |
| 'If date format is #/##/ | |
| If (rDate.Length = 5 And IsNumeric(rDate.Length - 1) = False) Then | |
| reportStartDate.Text = rDate & Now().Year | |
| End If | |
| 'If date format is ##/## | |
| If (rDate.Length = 5 And IsNumeric(rDate.Length - 1)) Then | |
| reportStartDate.Text = rDate & "/" & Now().Year | |
| End If | |
| 'If date format is ##/##/ | |
| If (rDate.Length = 6) Then | |
| reportStartDate.Text = rDate & Now().Year | |
| End If | |
| End If | |
| 'If (IsDate(reportStartDate.Text) = False) Then | |
| ' reportStartDate.Text = DateTime.Now.AddMonths(-1) | |
| 'End If | |
| End Sub | |
| Private Sub reportEndDate_LostFocus(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles reportEndDate.LostFocus | |
| If (Not Regex.Match(reportEndDate.Text, "^(([1-9])|(0[1-9])|(1[0-2]))\/(([0-9])|([0-2][0-9])|(3[0-1]))\/(([0-9][0-9])|([1-2][0,9][0-9][0-9]))$").Success) Then | |
| Dim rDate As String = reportEndDate.Text | |
| Dim slash As String = "/" | |
| 'Iterate through all possible date scenarios and properly appending the necessary characters to create propert short string format | |
| 'If date format is #/# | |
| If (rDate.Length = 3 And IsNumeric(rDate.Length - 1)) Then | |
| reportEndDate.Text = rDate & slash & Now().Year | |
| 'If date format is #/#/ | |
| ElseIf (rDate.Length = 4 And IsNumeric(rDate.Length - 1) = False) Then | |
| reportEndDate.Text = rDate & Now().Year | |
| 'If date format is #/## or ##/# | |
| ElseIf (rDate.Length = 4 And IsNumeric(rDate.Length - 1)) Then | |
| reportEndDate.Text = rDate & slash & Now().Year | |
| 'If date format is #/##/ | |
| ElseIf (rDate.Length = 5 And IsNumeric(rDate.Length - 1) = False) Then | |
| reportEndDate.Text = rDate & Now().Year | |
| 'If date format is ##/## | |
| ElseIf (rDate.Length = 5 And IsNumeric(rDate.Length - 1)) Then | |
| reportEndDate.Text = rDate & slash & Now().Year | |
| 'If date format is ##/##/ | |
| ElseIf (rDate.Length = 6) Then | |
| reportEndDate.Text = rDate & Now().Year | |
| End If | |
| End If | |
| 'Dim endDate As Date = Date.Parse(reportEndDate.Text) | |
| If (IsDate(reportEndDate.Text) = False) Then | |
| reportEndDate.Text = "Incorrect Date" | |
| Else | |
| 'Do Nothing | |
| End If | |
| End Sub | |
| End Class |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment