<% '----------------------------------------------------------- ' trackhours.asp ' ' A simple project/hours tracking script. ' by pb ' http://www.onfocus.com/ '----------------------------------------------------------- Set oConn = Server.CreateObject("ADODB.Connection") oConn.Open "[insert a connection string for your db]" %>

TrackHours

<% strAction = request("action") intProjectID = request("projectid") intSessionID = request("sessionid") strProjectName = Trim(request("projname")) strProjectRate = Trim(request("rate")) If strProjectRate = "" Then strProjectRate = "NULL" End If strNotes = Trim(request("notes")) If Len(strNotes) > 255 Then strNotes = Left(strNotes,255) End If If strAction = "start" Then If strProjectName <> "" Then sql = "INSERT INTO time_projects (ProjectName, ProjectRate) VALUES ('" & strProjectName & "'," & strProjectRate & ")" oConn.Execute(sql) sql = "SELECT Max(ProjectID) As MaxID FROM time_projects" Set rsMaxID = oConn.Execute(sql) intProjectID = rsMaxID("MaxID") Set rsMaxID = Nothing End If strNotes = Replace(strNotes,"'","''") sql = "INSERT INTO time_hours (ProjectID, startTime, endTime, notes) VALUES (" & intProjectID & ",getDate(),NULL,'" & strNotes & "')" oConn.Execute(sql) response.redirect "trackhours.asp" ElseIf strAction = "stop" Then sql = "UPDATE time_hours SET endTime = getDate() WHERE SessionID = " & intSessionID oConn.Execute(sql) response.redirect "trackhours.asp" ElseIf strAction = "delete" Then sql = "DELETE FROM time_hours WHERE sessionID = " & intSessionID oConn.Execute(sql) response.redirect "trackhours.asp" ElseIf strAction = "notesup" Then strNotes = Replace(strNotes,"'","''") sql = "UPDATE time_hours SET notes = '" & strNotes & "' WHERE SessionID = " & intSessionID oConn.Execute(sql) response.redirect "trackhours.asp" End If sql = "SELECT sessionID, time_hours.projectID, startTime, endTime, Notes, projectName FROM time_hours INNER JOIN time_projects ON time_hours.ProjectID = time_projects.ProjectID WHERE endTime IS NULL" Set rsOpenSessions = oConn.Execute(sql) If NOT rsOpenSessions.EOF Then response.write "

» Open Session

" response.write "

" & strProjectName & " - started: " & dtmStartTime & "
Notes: """ & strNotes & """
stop timing
" rsOpenSessions.MoveNext Loop response.write "

" blnNoSessions = 0 Else blnNosessions = 1 End If Set rsOpenSessions = Nothing If blnNoSessions Then sql = "SELECT projectID, projectName FROM time_projects" Set rsProjects = oConn.Execute(sql) 'If NOT rsProjects.EOF Then response.write "

» Start a Session

" & vbCrLf response.write "" & vbCrLf 'End If Set rsProjects = Nothing %> <% End If sql = "SELECT TOP 15 sessionID, time_hours.projectID, startTime, endTime, Notes, projectName, projectRate FROM time_hours INNER JOIN time_projects ON time_hours.ProjectID = time_projects.ProjectID WHERE endTime IS NOT NULL ORDER BY sessionID DESC" Set rsSessionLog = oConn.Execute(sql) If Not rsSessionLog.EOF Then response.write "

» Recent Sessions

" response.write "" response.write "" Do While Not rsSessionLog.EOF strNotes = rsSessionLog("Notes") intMinutes = FormatNumber((DateDiff("n",rsSessionLog("startTime"),rsSessionLog("endTime")) / 60),2) intRate = rsSessionLog("ProjectRate") If intRate = "" OR IsNull(intRate) Then intRate = 0 End If If IsNull(strNotes) OR strNotes = "" Then strNotes = " " End If response.write "" response.write "" response.write "" response.write "" response.write "" response.write "" response.write "" response.write "" rsSessionLog.MoveNext Loop response.write "

Project	Date	Hours	Notes	Approx.	Delete
" & rsSessionLog("projectName") & "	" & FormatDateTime(rsSessionLog("startTime"),2) & "	" & intMinutes & "	" & strNotes & "	" & FormatCurrency(intMinutes * intRate) & "	[x]

" End If Set rsSessionLog = Nothing %> <% oConn.Close Set oConn = Nothing %>