%
'-----------------------------------------------------------
' 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 ""
Do While Not rsOpenSessions.EOF
intProjectID = rsOpenSessions("projectID")
intSessionID = rsOpenSessions("sessionID")
strProjectName = rsOpenSessions("projectName")
dtmStartTime = rsOpenSessions("startTime")
strNotes = rsOpenSessions("Notes")
response.write "- " & strProjectName & " - started: " & dtmStartTime & "
Notes: """ & strNotes & """"
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 "Project | Date | Hours | Notes | Approx. | Delete |
"
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 "" & rsSessionLog("projectName") & " | "
response.write "" & FormatDateTime(rsSessionLog("startTime"),2) & " | "
response.write "" & intMinutes & " | "
response.write "" & strNotes & " | "
response.write "" & FormatCurrency(intMinutes * intRate) & " | "
response.write "[x] | "
response.write "
"
rsSessionLog.MoveNext
Loop
response.write "
"
End If
Set rsSessionLog = Nothing
%>
<%
oConn.Close
Set oConn = Nothing
%>