bobbo85 Posted August 8, 2008 Share Posted August 8, 2008 I have a form that users fill out, creating a list of Job orders. If each job has maybe 8 fields, what is the best way to store the information for retrieval? Before I decided to make a "save" function, I had only three fields, so I used a ListView with three columns. I could just have the "queue" button add an item to the listview. Then when the script performed its tasks on each item, it just removed the item from the listview. Now with more pieces of info per item the listview might not be appropriate, and I have no idea what to do about a save/open function. Thanks in advance! Link to comment Share on other sites More sharing options...
DirtDBaK Posted August 8, 2008 Share Posted August 8, 2008 well show what you already have made I am sure that I can help you improve it [center][/center] Link to comment Share on other sites More sharing options...
bobbo85 Posted August 8, 2008 Author Share Posted August 8, 2008 well show what you already have made I am sure that I can help you improve it Here it is, I tried to comment it well. The outline is like this: Users want to make a list of Jobs to put into a database, each job has a title, description, id, etc. The script then puts the jobs in the database for the user. 1) Users can import an excel file that already contains a formatted list of jobs, or they can manually add the info in one job at a time 2) The jobs are added to a queue 3) The queue can be saved and loaded at a later time (maybe you have to leave for the day and don't want to let the script go unattended) 4) The script takes over, logs into a website and enters all of the info one job at a time expandcollapse popup#include <GUIConstants.au3> #include <StaticConstants.au3> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #include <ButtonConstants.au3> #include <IE.au3> #include <Excel.au3> #include <Array.au3> #Include <GuiListView.au3> #Include <File.au3> #include <ComboConstants.au3> ;save and open do not function yet! Opt("GUIOnEventMode", 1) ; Change to OnEvent mode AutoItSetOption("WinTitleMatchMode",2);will match internet explorer window even if the first part of the title changes global $oIE global $sheetarray;if the user imports data in a spreadhseet, it will be read into an array (excel opens, the sheet is read to an array, excel closes) global $i=1;this variable keeps track of how far we've gone when parsing the imported data one entry at a time global $flag=0;did the user provide an excel file to read data from? $nexticonloc="C:\Documents and Settings\Bobby\Desktop\nexticon.bmp" $Main = GUICreate("Vurv Job Order Script", 550, 520, 236, 153) $filemenu = GUICtrlCreateMenu("&File") $openitem = GUICtrlCreateMenuItem("Open queue", $filemenu) $saveitem = GUICtrlCreateMenuItem("Save queue", $filemenu) $exititem = GUICtrlCreateMenuItem("Exit", $filemenu) $Tab1 = GUICtrlCreateTab(0, 0, 550, 520) GUICtrlSetResizing(-1, $GUI_DOCKWIDTH+$GUI_DOCKHEIGHT) $TabSheet1 = GUICtrlCreateTabItem("Step 1") $Next1 = GUICtrlCreateIcon("", 0, 496, 464, 32, 32, BitOR($SS_NOTIFY,$WS_GROUP)) GUICtrlCreateLabel("Enter the file containing the job descriptions (excel spreadsheet):", 40, 72, 459, 24) GUICtrlSetFont(-1, 12, 400, 0, "MS Sans Serif") $importlocation =GUICtrlCreateInput("", 88, 184, 361, 21) $browsebutton = GUICtrlCreateButton("Browse", 232, 208, 75, 25, 0) GUICtrlCreateLabel("(This step is optional, you can manually add jobs in step 2)", 64, 112, 410, 24) GUICtrlSetFont(-1, 12, 400, 0, "MS Sans Serif") $TabSheet2 = GUICtrlCreateTabItem("Step 2") $Next2 = GUICtrlCreateIcon("", 0, 496, 464, 32, 32, BitOR($SS_NOTIFY,$WS_GROUP)) GUICtrlCreateLabel("Verify the following information, make corrections as needed.", 40, 48, 431, 24) GUICtrlSetFont(-1, 12, 400, 0, "MS Sans Serif") GUICtrlCreateLabel("Job-ID:", 16, 88, 38, 17) $jobid = GUICtrlCreateInput("", 104, 88, 73, 21) $ltitle = GUICtrlCreateLabel("Job Title:", 16, 112, 47, 17) $jobtitle = GUICtrlCreateInput("", 104, 112, 345, 21) GUICtrlCreateLabel("Job Description:", 16, 184, 80, 17) $jobdesc = GUICtrlCreateEdit("", 104, 184, 353, 169) GUICtrlSetData(-1, "") $Progress = GUICtrlCreateProgress(152, 464, 238, 17) $lprogress = GUICtrlCreateLabel("Progress:", 104, 464, 48, 17) GUICtrlCreateLabel("Priority: ", 16, 368, 41, 17) $jobpriority = GUICtrlCreateCombo("Hot", 104, 360, 145, 25,$CBS_DROPDOWNLIST) GUICtrlSetData(-1, "LukeWarm|Cold") GUICtrlCreateLabel("Years exp: ", 16, 392, 57, 17) $jobyearsexp = GUICtrlCreateCombo("3-5 Years", 104, 384, 145, 25,$CBS_DROPDOWNLIST) GUICtrlSetData(-1, "0-1|1-3|5-9|9+") GUICtrlCreateLabel("Job Owner: ", 16, 136, 61, 17) $jobowner = GUICtrlCreateCombo("David Tomer", 104, 136, 217, 21,$CBS_DROPDOWNLIST) guictrlsetdata(-1,"Cara Myers|Cheri Countermash|Edward Flemming|John Adduci|Kathy Metras|Pamela Colbourne") GUICtrlCreateLabel("Job Location: ", 16, 160, 71, 17) $joblocation = GUICtrlCreateInput("", 104, 160, 217, 21) GUICtrlCreateLabel("Annual Salary: ", 16, 416, 75, 17) $jobsalary = GUICtrlCreateInput("", 104, 408, 121, 21) $skipbutton = GUICtrlCreateButton("Skip", 460, 248, 75, 25, 0) $queuebutton = GUICtrlCreateButton("Add to Queue", 460, 224, 75, 25, 0) $TabSheet3 = GUICtrlCreateTabItem("Step 3") $queue = GUICtrlCreateList("", 160, 96, 225, 188) $removebutton = GUICtrlCreateButton("Remove", 400, 176, 75, 25, 0) GUICtrlCreateLabel("The following job orders will be added into Vurv:", 96, 56, 335, 24) GUICtrlSetFont(-1, 12, 400, 0, "MS Sans Serif") $gobutton = GUICtrlCreateButton("Go", 144, 304, 259, 41, 0) GUICtrlCreateTabItem("") GUISetState(@SW_SHOW) GUICtrlSetOnEvent($openitem,"openqueue") GUICtrlSetOnEvent($exititem,"CLOSEClicked") GUICtrlSetOnEvent($openitem,"openqueue") GUICtrlSetOnEvent($saveitem,"savequeue") GUICtrlSetOnEvent($Next1,"next1") GUICtrlSetOnEvent($browsebutton,"browse") GUICtrlSetOnEvent($Next2,"next2") GUICtrlSetOnEvent($queuebutton,"queueit") GUICtrlSetOnEvent($skipbutton,"skipit") GUICtrlSetOnEvent($removebutton,"removeone") GUICtrlSetOnEvent($gobutton,"maingo") GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSEClicked") GUICtrlSetImage ($Next1, $nexticonloc) GUICtrlSetImage ($Next2, $nexticonloc) While 1 Sleep(1000) ; Idle around WEnd func savequeue() $savelocation=FileSaveDialog ("Save Queue", @DesktopCommonDir&"\", "Text (*.txt)",16,"my queue.txt") FileOpen ($savelocation, 2);creates file for $x=0 to _GUICtrlListView_GetItemCount($queue)-1 for $y=0 to 2;this was just hardcoded to 3 fields per job, but there will be more filewrite($savelocation,_guictrllistview_getitemtext($queue,$x,$y)&"#");# is delimiter Next ;should i have another delimiter here to split the jobs as well as their parts? Next fileclose($savelocation) EndFunc Func openqueue() $queuelocation=FileOpenDialog("Open Queue",@DesktopCommonDir&"\", "Text (*.txt)",16,"") FileOpen ($queuelocation, 0);opens the saved file dim $linearray[1];here is where i attempted and failed to reach the saved data usefully fileclose($queuelocation) $linearray=stringsplit(FileRead($queuelocation),"#") _ArrayDisplay($linearray) ;for $x=0 to $linearray[0] ;_GUICtrlListView_Additem($queue,$linearray[1]&;gave up EndFunc Func CLOSEClicked() Exit EndFunc Func browse() dim $tmp=FileOpenDialog("",@DesktopCommonDir&"\","Spreadsheets (*.xls)",1) if $tmp Then GUICtrlSetData($importlocation,$tmp) EndIf EndFunc func next1() if guictrlread($importlocation)<>"" Then readexcel();opens excel sheet, reads it to an array, then closes it GUICtrlSetState ($TabSheet2, $GUI_SHOW) parsearray();reads one job every time it is called, fills in the fields for the user $flag=1; yes we have an excel sheet Else GUICtrlSetState ($TabSheet2, $GUI_SHOW) EndIf EndFunc func next2() GUICtrlSetState($TabSheet3,$GUI_SHOW) EndFunc Func queueit() if guictrlread($jobid)<>"" Then dim $dupecheck=dupecheckfunc() if $dupecheck==1 Then msgbox(0,"","This job is already in the queue! Either remove it from the queue in step 3 or enter a different job") Else GUICtrlCreateListViewItem(guictrlread($jobid)&"|"&guictrlread($jobtitle)&"|"&guictrlread($jobdesc),$queue);sends to the queue EndIf guictrlsetdata($jobid,"") guictrlsetdata($jobdesc,"") guictrlsetdata($jobtitle,"") if $flag==1 Then;If the user imported an excel sheet, the script uses that info to populate the fields and the user can edit/queue/skip that entry parsearray() guictrlsetdata($Progress,$i/UBound($sheetarray)*100);tells the user how close they are to the end of their imported data Else guictrlsetdata($Progress,100);this just blinks full then empty when manually entering the info - lets the user know their item got queued sleep(250) guictrlsetdata($Progress,0) EndIf EndIf EndFunc Func skipit() if $flag==1 Then parsearray();reads the next entry from imported data for the user guictrlsetdata($Progress,$i/UBound($sheetarray)*100) Else guictrlsetdata($jobid,"") guictrlsetdata($jobdesc,"") guictrlsetdata($jobtitle,"") EndIf EndFunc Func dupecheckfunc();goes down the queue and checks each job-id against the one the user is trying to add to the queue for $x=0 to _GUICtrlListView_GetItemCount($queue)-1 if guictrlread($jobid)==_GUICtrlListView_GetItemText($queue,$x,0) Then return 1 EndIf Next Return 0 EndFunc func removeone() _GUICtrlListView_DeleteItemsSelected($queue) EndFunc Func maingo() while _GUICtrlListView_GetItemCount($queue)>0 vurvlogin() hitjobs() fillbasics() enterdata() winwaitclose("Windows Internet Explorer") ;dim $oSubmit = _IEGetObjByName ($oIE, "cmdSubmit") ;_IEAction ($oSubmit, "click") ;_IELoadWait ($oIE) _GUICtrlListView_DeleteItem($queue, 0) WEnd EndFunc Func readexcel() $sFilePath1 = guictrlread($importlocation) $oExcel = _ExcelBookOpen($sFilePath1) $sheetarray = _ExcelReadSheetToArray($oExcel,1,1);is global _ExcelBookClose($oExcel,0) EndFunc func parsearray() ;format is such that one line has "Job-5555 - Title" ;each line after that until the next job is the job description if $i<UBound($sheetarray)Then dim $temp=$sheetarray[$i][2];row, column dim $foundjobdesc="" if stringregexp($temp,"Job-\d{4}",0) Then;this is the first time we find a job id $temp=$sheetarray[$i][2];row, column $temparray=StringRegExp ($temp,"\d{4}",1) $foundjobid=$temparray[0] $foundjobtitle=StringRegExpReplace($temp,"Job-\d{4} - ","") $i=$i+1 if $i<UBound($sheetarray) Then $temp=$sheetarray[$i][2] EndIf while $i<UBound($sheetarray) and not StringRegExp($temp,"Job-\d{4}",0) $temp=$sheetarray[$i][2] if not StringRegExp($temp,"Job-\d{4}",0) Then $foundjobdesc=$foundjobdesc&$temp&@CRLF $i=$i+1 EndIf WEnd guictrlsetdata($jobid,$foundjobid) guictrlsetdata($jobtitle,$foundjobtitle) guictrlsetdata($jobdesc,$foundjobdesc) EndIf EndIf EndFunc func vurvlogin() ;open an internet explorer window and navigate to the login page $oIE=_IECreate ("http://recruitment.vurvexpress.com") WinSetState ( "Vurv Express", "", @SW_MAXIMIZE ) WinWaitActive( "Vurv Express","") ;identify the login form, and its elements - login and password $o_form = _IEFormGetCollection ($oIE, 0) $o_login = _IEFormElementGetObjByName ($o_form, "txtEmail") $o_password = _IEFormElementGetObjByName ($o_form, "txtPassword") ;hardcoded login info $username = "x" $password = "x" ;fill in the login fields _IEFormElementSetValue ($o_login, $username) _IEFormElementSetValue ($o_password, $password) ;submit - would have used a click function, but the submit button had no name associated with it in the code, and using coordinate clicks or tabs can vary on systems _IEFormSubmit ($o_form) EndFunc func hitjobs(); navigate to the "add a new job order" page _IENavigate($oIE,"http://recruitment.vurvexpress.com/users/orders/orderaddupdate.cfm?szClientID=7099&szOrderID=0") EndFunc func fillbasics();this fills in info that is constant $o_jobtype=_IEFormElementGetObjByName($o_form,"cboTypes") _IEFormElementSetValue($o_jobtype,"8");set job to "regular" $o_comptype=_IEFormElementGetObjByName($o_form,"cboCompensationType") _IEFormElementSetValue($o_comptype,"2");set compensation to annual salary ;txtFunction job function is read only... need to use the "lookup" function ;there is more, but it's not important for the forum, this is just the pattern i use, maybe it could be improved! EndFunc func jobdesc();this gets to the job description part, which is some funky iframe i couldn't get to without letting the page load, then tabbing my way into the text field. send("^f") sleep(250) send("Public Job Description{ENTER}") sleep(250) send("{esc}") send("{tab}") sleep(250) EndFunc func enterdata() dim $o_form = _IEFormGetCollection ($oIE, 0) dim $o_jobid=_IEFormElementGetObjByName($o_form,"txtOrderNumber") _IEFormElementSetValue($o_jobid,_guictrllistview_getitemtext($queue, 0,0)) dim $o_jobtitle=_IEFormElementGetObjByName($o_form,"txtJobTitle") _IEFormElementSetValue($o_jobtitle,_guictrllistview_getitemtext($queue, 0,1)) jobdesc() dim $mydesc=_guictrllistview_getitemtext($queue,0,2) ClipPut($mydesc);copies description to clipboard sleep(500) send("^v") EndFunc 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