Guest JRowe_1 Posted November 28, 2006 Share Posted November 28, 2006 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? *********************************************************************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.)expandcollapse popup _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)) EndFuncTimesheet.zip Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now