Sign in to follow this  
Followers 0
Guest JRowe_1

(work in progress) Employee Time Management tool

1 post in this topic

This application allows employees of a business who must travel to various job sites to keep track of how much time was spent at each site. This generates a list of all sites dynamically, so the admin can update site and employe information over time, if sites or employees change. No updates the to the application are necessary ;)

I'm building an Admin application as well, but that probably won't be released.

Many thanks for those of you who have helped out, this is really a good exercise for me, and demonstrates some of the potential for the mysql UDF.

Thirdly, it demonstrates my utter lack of knowledge about how to use Tidy. It doesnt show up in my Scite editor... anyone able to throw me a bone and point me to a "how to use tidy" thread? :lmao:

*********************************************************************

You must have the mysql ODBC 3.51 driver, as mentioned in the MySQL UDF Thread.

You must also have a valid database. Create a new database on your server by importing the timesheet.sql (attached to this thread.)

You must edit the $connection variable, this is where your mysql server connection information goes.

*********************************************************************

First off, you can implement login functionality at a very basic level. If you're logged in, it works, if you're not logged in, it doesnt work.

Secondly, you can dynamically generate lists, automatically updating from the data in the database, and edit that data as well. Check out the list and comment box as you input new jobs.

Anyway, my problem is that the application is inherently insecure, since it includes the username/PW for the MySQL server. Anyone have any thoughts on how to make the application secure without putting anything on the mysql server? (My only thought is a per-distribution .ini file which the end-user must fill out themselves.)

 _linenums:0'>#include <GUIConstants.au3>
#include<mysql.au3> ;thanks cdkid!

$objErr = ObjEvent("AutoIt.Error","MyErrFunc")
$loginStatus = "Not Logged In"
$PW = "default"
$connect = _MySQLConnect('USERNAME', 'PASSWORD', 'DATABASE', 'SERVER IP OR URL')
dim $sitelist
dim $EmployeeName
dim $jobsList
dim $JobsArray
$populated = 0
$currentDate = @MON &"/" & @MDAY&"/" & @Year

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;end initializations

#Region ### START Koda GUI section ### Form=
Opt("GUIOnEventMode", 1)


$Form1 = GUICreate("AForm1", 633, 424, 191, 111)
GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSEClicked")


$TabControl = GUICtrlCreateTab(0, 0, 632, 423, -1, $TCS_EX_FLATSEPARATORS)
GUICtrlSetFont(-1, 10, 400, 0, "Arial")

$LoginPage = GUICtrlCreateTabItem("Log In")


$UsernameField = GUICtrlCreateInput("", 152, 123, 257, 24, BitOR($ES_LOWERCASE,$ES_AUTOHSCROLL))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
GUICtrlSetTip(-1, "Enter Your Username Here")
$PasswordField = GUICtrlCreateInput("", 152, 155, 257, 24, BitOR($ES_PASSWORD,$ES_AUTOHSCROLL))
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
GUICtrlSetTip(-1, "Enter Your Password Here")
$LoginButton = GUICtrlCreateButton("Log In", 264, 211, 75, 25, 0)
GUICtrlSetFont(-1, 10, 400, 0, "Arial")
GUICtrlSetOnEvent($LoginButton, "TestingMySQL")
$Label1 = GUICtrlCreateLabel("Username:", 64, 123, 82, 24)
GUICtrlSetFont(-1, 12, 400, 0, "MS Sans Serif")
$Label2 = GUICtrlCreateLabel("Password:", 66, 158, 77, 24)
GUICtrlSetFont(-1, 12, 400, 0, "MS Sans Serif")


$NewJobPage = GUICtrlCreateTabItem("New Job")

$Label4 = GUICtrlCreateLabel(" New Job", 26, 35, 69, 22, $SS_CENTERIMAGE)
GUICtrlSetFont(-1, 12, 400, 0, "Arial")

$DateBoxControl = GUICtrlCreateDate($currentDate, 13, 64, 106, 24, $WS_TABSTOP)


$JobSiteDDControl = GUICtrlCreateCombo("", 13, 96, 145, 25)
GUICtrlSetFont(-1, 10, 400, 0, "Arial")

$HoursWorked = GUICtrlCreateInput("", 13, 128, 145, 24)
GUICtrlSetFont(-1, 10, 400, 0, "Arial")
GUICtrlSetLimit($HoursWorked, 10)


$CommentBox = GUICtrlCreateEdit("", 173, 40, 193, 153, BitOR($ES_AUTOVSCROLL,$ES_AUTOHSCROLL,$ES_WANTRETURN))
GUICtrlSetLimit($CommentBox, 100)
GUICtrlSetFont(-1, 10, 400, 0, "Arial")

$Button1 = GUICtrlCreateButton("Submit New Job", 29, 168, 107, 25, BitOR($BS_CENTER,$BS_VCENTER,$WS_BORDER), $WS_EX_STATICEDGE)
GUICtrlSetFont(-1, 10, 400, 0, "Arial")
GUICtrlSetOnEvent($Button1, "SubmitNewJob")

$HistoryListA =GUICtrlCreateList("", 389, 37, 225, 326, BitOR($WS_VSCROLL,$WS_BORDER))
GUICtrlSetFont(-1, 08, 400, 0, "Arial")
GUICtrlSetOnEvent(-1, 'PopulateJobView')


$JobViewEdit = GUICtrlCreateEdit("", 13, 225, 355, 129, BitOR($ES_AUTOVSCROLL,$ES_AUTOHSCROLL,$ES_READONLY,$ES_WANTRETURN))
GUICtrlSetFont(-1, 10, 400, 0, "Arial")

$HistoryPage = GUICtrlCreateTabItem("History")
$HistoryListB = GUICtrlCreateList("", 453, 32, 153, 358)
GUICtrlSetFont(-1, 10, 400, 0, "Arial")
$HistoryDisplay = GUICtrlCreateGroup("", 13, 192, 425, 193, $BS_FLAT)
GUICtrlSetFont(-1, 10, 400, 0, "Arial")
$HistoryDate = GUICtrlCreateDate("2006/11/26 17:49:48", 21, 208, 186, 24)
$HistorySite = GUICtrlCreateCombo("", 21, 240, 145, 25)
GUICtrlSetFont(-1, 10, 400, 0, "Arial")
$HistoryHours = GUICtrlCreateInput("", 21, 272, 121, 24)
GUICtrlSetFont(-1, 10, 400, 0, "Arial")
$HistoryComment = GUICtrlCreateLabel("", 21, 304, 404, 76, BitOR($SS_CENTER,$SS_CENTERIMAGE,$SS_SUNKEN), $WS_EX_CLIENTEDGE)
GUICtrlSetFont(-1, 10, 400, 2, "Arial")
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Label3 = GUICtrlCreateLabel("Select a Job from the list on the right to see the details below.", 293, 88, 154, 52, BitOR($SS_CENTER,$SS_SUNKEN), $WS_EX_CLIENTEDGE)
GUICtrlSetFont(-1, 10, 400, 0, "Arial")
GUICtrlCreateTabItem("")
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###


While 1
    
    
If $populated = 0 Then
        If $loginStatus = "Logged In" Then
            $query2 = "SELECT Name FROM Sites WHERE Code >-1"
            $jobs = _Query($connect , $query2)
                With $jobs
                    While NOT .EOF
                        $siteList = $siteList & "|" & $jobs.Fields('Name').Value
                        .MoveNext
                    WEnd
                EndWith
            $populated = 1
            GUICtrlSetData($JobSiteDDControl, $siteList)
            GUICtrlSetData($DateBoxControl, $currentDate)

    EndIf
    
    EndIf
    
    
WEnd


        
        Func MyErrFunc()

$hexnum=hex($objErr.number,8)

Msgbox(0,"","We intercepted a COM Error!!"        & @CRLF                   & @CRLF & _
             "err.description is: "    & $objErr.description    & @CRLF & _
             "err.windescription is: " & $objErr.windescription & @CRLF & _
             "err.lastdllerror is: "   & $objErr.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & $objErr.scriptline     & @CRLF & _
             "err.number is: "         & $hexnum                 & @CRLF & _
             "err.source is: "         & $objErr.source         & @CRLF & _
             "err.helpfile is: "       & $objErr.helpfile       & @CRLF & _
             "err.helpcontext is: "    & $objErr.helpcontext _
            )
exit
EndFunc

Func TestingMySQL()
    $Employee = String(GUICtrlRead($UsernameField))
    $query = "SELECT Password FROM Employees WHERE Username = '" & $Employee & "'"
    
    $id = _Query($connect , $query)
    With $id
        While NOT .EOF
            $PW = $id.Fields('Password').Value
            .MoveNext
        WEnd
    EndWith
    
If $loginStatus = "Not Logged In" Or "Invalid Username or Password" Then
        If $PW = String(GUICtrlRead($PasswordField)) Then
            $loginStatus = "Logged In"
                Else
                    $loginStatus = "Invalid Username or Password"
        EndIf
    MsgBox(0,"Login Status", $loginStatus)
EndIf
        Call("GetEmployeeName")
        Call("PopulateJobsList")
EndFunc

Func CLOSEClicked()
  ;Note: at this point @GUI_CTRLID would equal $GUI_EVENT_CLOSE,
  ;@GUI_WINHANDLE will be either $mainwindow or $dummywindow
  If @GUI_WINHANDLE = $Form1 Then 
      _MySQLEnd($connect)
    Exit
  EndIf 
EndFunc

Func DateBoxTest()
    MsgBox(0,"DateBox Contents", StringLen(String(GUICtrlRead($DateBoxControl)))& " " & String(GUICtrlRead($DateBoxControl)))
    
EndFunc

Func SubmitNewJob()
    If $loginStatus = "Not Logged In" Then
                        MsgBox(0,"Login Status", "You Are Not Logged In")
    EndIf                   
    If $loginStatus = "Logged In" Then
    $query3 = "INSERT INTO Jobs(Name, Date, Site, Hours, Comment) VALUES('"& $EmployeeName & "', '" & String(GUICtrlRead($DateBoxControl)) & "', '" & String(GUICtrlRead($JobSiteDDControl)) & "', '" &  GUICtrlRead($HoursWorked) & "', '" &  String(GUICtrlRead($CommentBox)) & "')"
    _Query($connect , $query3)
    EndIf
    Call("PopulateJobsList")
EndFunc

Func GetEmployeeName()
        $Employee = String(GUICtrlRead($UsernameField))
        $query = "SELECT Employee FROM Employees WHERE Username = '" & $Employee & "'"
    
    $id = _Query($connect , $query)
    With $id
        While NOT .EOF
            $EmployeeName = $id.Fields('Employee').Value
            .MoveNext
        WEnd
    EndWith
EndFunc


Func PopulateJobsList()
    GUICtrlSetData($HistoryListA, "")
    $jobsList = ""
        $query4 = "SELECT * FROM Jobs WHERE Name = '" & $EmployeeName & "'"
            $jobs2 = _Query($connect, $query4)
                With $jobs2
                    While NOT .EOF
                        $jobsList = $jobsList & "|" & $jobs2.Fields('Date').Value
                        $jobsList = $jobsList & " , " & $jobs2.Fields('Site').Value
                        $jobsList = $jobsList & " , " & $jobs2.Fields('Hours').Value
                        $jobsList = $jobsList & " , " & $jobs2.Fields('Id').Value
                    
                        .MoveNext
                    WEnd
                EndWith
            GUICtrlSetData($HistoryListA, $jobsList)
        EndFunc
        
Func PopulateJobView()


;   $jobsList = ""
;       $query4 = "SELECT * FROM Jobs WHERE Name = '" & $EmployeeName & "'"
;           $jobs2 = _Query($connect, $query4)
;               With $jobs2
;                   While NOT .EOF
;                       $jobsList = $jobsList & "|" & $jobs2.Fields('Date').Value
;                       $jobsList = $jobsList & "," & $jobs2.Fields('Site').Value
;                       $jobsList = $jobsList & "," & $jobs2.Fields('Hours').Value
;                   
;                       .MoveNext
;                   WEnd
;               EndWith
        GUICtrlSetData($JobViewEdit , "")   
        GUICtrlSetData($JobViewEdit, GUICtrlRead($HistoryListA))
        
        
EndFunc

Timesheet.zip

Share this post


Link to post
Share on other sites



Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0