Employee Time Sheet template

Will66 · February 11, 2008

Made this for work mates and so far pretty happy with it, be cool to know your feedback .

Some of the menu options aren't finished but file->save and file-> open and the Print options are done.

Fill out your timesheet and save it as a record for later viewing. Print preview etc.

Requires excel, tested on Excel 2000 haven't tested later versions yet but should be fine.

Requires: Ms Excel

zip includes timesheetDemo.xls and timesheet.html and timesheet3.au3....(XLS files are not allowed to be uploaded to forum.)

timesheet3.au3:

#include <GUIConstants.au3>
#include <IE.au3>
#include <Date.au3>
Dim $Gui2
_IEErrorHandlerRegister ()
Dim $oIE = _IECreateEmbedded ()


Dim $myevent,$oEvent,$date,$hListBox
Dim $xl_template = @ScriptDir & "\timesheetDemo.xls"
Dim $init_file = @MyDocumentsDir & "\Timesheets\history.ini"
Opt("GUIResizeMode", 1)
Opt("GUIOnEventMode", 1)  ; OnEvent mode
$gui = GUICreate("Frames", 940, 600,-1,-1,$WS_OVERLAPPEDWINDOW)
GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSEClicked")
GUISetFont (8,-1,"Arial")
Dim $GUIActiveX = GUICtrlCreateObj($oIE, 1,10,938,570)
_build_menus()
_build_frames()
GUISetState ()
$oButton = $oIE.document.forms(0)
$myevent = _IEGetObjByName ($oButton, "calendar")
$oEvent = ObjEvent( $myevent, "my_Event_")

While 1
  Sleep(1000)  ; Idle around
WEnd

Func CLOSEClicked2()
    $myDate=GUICtrlRead($date)
    $myDate = _DateTimeFormat($myDate,2)
    $oIE.document.forms(0).AB8.value=$myDate
    GUIDelete($Gui2)    
EndFunc

Func CLOSEClicked3()
    GUIDelete($Gui2)    
EndFunc

func my_Event_onclick()
If $Gui2 then GUIDelete($Gui2)  
$Gui2 = GUICreate("Choose Date",200,200)
GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSEClicked2")
$date=GUICtrlCreateMonthCal  ("", 10,10,180,158)
GUISetState ()
EndFunc

    Func _build_frames()
        _IENavigate ($oIE, @ScriptDir & "\timesheet.html")
    EndFunc
    
    Func _printPreview()
        $filled = fillExcel(1)
    EndFunc
    
    Func _printExcel()
        $filled = fillExcel(2)
    EndFunc
    
    Func fillExcel($action="")
        $FileName= $xl_template
        If not FileExists($FileName) then
            Msgbox (0,"Excel Template - Not Found","Can't run this test, because I cant find Excel Template File! "& $FileName)
            Return
        endif
        
        $oExcelDoc = ObjGet($FileName)  ; Get an Excel Object from an existing filename
        If IsObj($oExcelDoc) then
                    
            $oForm = _IEFormGetCollection ($oIE, 0)
            For $oInput In $oForm

                If $oInput.type = "radio" Or $oInput.type = "checkbox" Then
                    If $oInput.checked=true Then
                        If $oInput.name = "Z15" Then
                            $oExcelDoc.ActiveSheet.Range($oInput.name).value = "Fixed:                               þ" 
                            $oExcelDoc.ActiveSheet.Range($oInput.name).Characters(38,1).Font.Name = "Wingdings"
                            $oExcelDoc.ActiveSheet.Range($oInput.name).Characters(38,1).Font.FontStyle = "Bold"
                            $oExcelDoc.ActiveSheet.Range($oInput.name).Characters(38,1).Font.Size = 9
                        Else
                            $oExcelDoc.ActiveSheet.Range($oInput.name).Font.Name = "Wingdings"
                            $oExcelDoc.ActiveSheet.Range($oInput.name).Font.Size = 9    
                            $oExcelDoc.ActiveSheet.Range($oInput.name).value = "þ" 
                        Endif
                    EndIf
                ElseIf $oInput.type = "button" Then 
                    ContinueLoop;do nothing
                Else
                    $textFieldValue = $oInput.value
                    If $textFieldValue == 0 Then $textFieldValue = ""
                    $oExcelDoc.ActiveSheet.Range($oInput.name).value = $textFieldValue 
                EndIf
            Next

            If $action=1 Then
                $oExcelDoc.Windows(1).Visible = 1; Set the first worksheet in the workbook visible
                $oExcelDoc.Application.Visible = 1; Set the application visible (without this Excel will exit)
                $oExcelDoc.ActiveSheet.PrintPreview      ; Print Preview
            Else
                $oExcelDoc.ActiveSheet.PrintOut
            EndIf
            $oExcelDoc.Close(False)     ; Close the Excel document
        Else
            Msgbox (0,"Excel File Test","Error: Could not open "& $FileName & " as an Excel Object.")
        Endif
        
    EndFunc

    Func _getFormValues()
        $arrayString = ""
    $oForm = _IEFormGetCollection ($oIE, 0)
    For $oInput In $oForm
        If $oInput.type = "button" Then
            ContinueLoop ;do nothing
        ElseIf $oInput.type = "checkbox" Then
            If $oInput.checked=true Then
                $arrayString &= "checked~" & $oInput.name & "|"
            Else
                $arrayString &= "unchecked~" & $oInput.name & "|"
            EndIf
        Else
            $arrayString &= $oInput.value & "~" & $oInput.name & "|" 
        EndIf       
    Next
        Return $arrayString 

EndFunc

    Func _fileOpen()
    $var = IniReadSectionNames($init_file)
    If @error Then 
        MsgBox(4096, "", "Error occurred, probably no History saved yet.")
    Else


        If $Gui2 then GUIDelete($Gui2)  
        $Gui2 = GUICreate("History",200,250)
        GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSEClicked3")
        $hListBox_button = GUICtrlCreateButton("OK",75,210,50,20)
        GUICtrlSetOnEvent(-1, "_historyListClicked")

        $hListBox = GUICtrlCreateList("", 2, 2,190,190, BitOR($WS_BORDER, $WS_VSCROLL, $LBS_NOTIFY))
        GUISetState ()
        $listData = ""
        For $i = 1 To $var[0]
            GUICtrlSetData(-1,$var[$i])
        Next

    EndIf
EndFunc

    func _historyListClicked()
        $section = GUICtrlRead($hListBox) 
        GUIDelete($Gui2)

        If StringLen($section) > 1 Then
            $var = IniRead($init_file, $section, "key", "NotFound")
            $newArray = StringSplit($var,"|")
        
            $oForm = _IEFormGetObjByName ($oIE, "timesheet")
            For $f = 1 to $newArray[0] -1
                $fieldpair = StringSplit($newArray[$f],"~") 
                $fieldvalue = $fieldpair[1]
                $fieldname = $fieldpair[2]
                If $fieldvalue == 0 Then $fieldvalue = ""
                $oText = _IEFormElementGetObjByName ($oForm, $fieldname)

                If $fieldvalue = "checked" Then 
                    $oText.checked = "checked"
                Elseif $fieldvalue = "unchecked" Then
                    $oText.checked = ""         
                Else            
                    $oText.value = $fieldvalue
                EndIf
            Next
        EndIf
    EndFunc

    Func _fileSave()

        $oForm = _IEFormGetCollection ($oIE, 0)     
        $oForm_date = $oForm("AB8").value
        If Not $oForm_date >0 Then
            msgbox(0,"Invalid Date","Date must be entered before you can save!" & @crlf & "Click the icon next to Fortnight Ending:")
            return
        EndIf
        FileOpen($init_file,1 + 8)
        FileClose($init_file)       
        $arrayString=_getFormValues()
        IniWrite($init_file, $oForm_date, "key", $arrayString)
    EndFunc


    Func _filedelete()
        $oForm = _IEFormGetCollection ($oIE, 0)     
        $oForm_date = $oForm("AB8").value
        ;msgbox(0,"Deleted","Rcord " & $oForm_date & " about to delete history.")
        
        If $oForm_date <> "" Then
            $deleted = IniDelete($init_file, $oForm_date)
            msgbox(0,"Deleted: " & $oForm_date,"Record " & $oForm_date & " deleted from your history.")
        EndIf
    EndFunc
    
    Func _build_menus()
        
        
        $FileMenu = GUICtrlCreateMenu ("&File")
        GUICtrlCreateMenuitem ("Open",$FileMenu)
        GUICtrlSetOnEvent(-1, "_fileOpen")   
        
        ;GUICtrlCreateMenuitem ("New",$FileMenu)        
        GUICtrlCreateMenuitem ("Save",$FileMenu)
        GUICtrlSetOnEvent(-1, "_fileSave")   

        GUICtrlCreateMenuitem ("Delete",$FileMenu)
        GUICtrlSetOnEvent(-1, "_filedelete")   
        
        $ReportsMenu = GUICtrlCreateMenu ("&Print")
        GUICtrlCreateMenuitem ("Print Preview",$ReportsMenu)    
        GUICtrlSetOnEvent(-1, "_printPreview")   
        GUICtrlCreateMenuitem ("Print",$ReportsMenu)    
        GUICtrlSetOnEvent(-1, "_printExcel")

    EndFunc
    
    Func CLOSEClicked()
        Exit   
    EndFunc

Edited February 11, 2008 by Will66

gseller · February 11, 2008

Pretty cool!! Would like to see it done all in autoit without the use of html or excel. maybe as a database only... Still nice work tho.. Thanks for sharing it..

Tomb · February 11, 2008

very nice work

Will66 · February 11, 2008

Pretty cool!! Would like to see it done all in autoit without the use of html or excel. maybe as a database only... Still nice work tho.. Thanks for sharing it..

The html will be migrated to a autoit function eg document.write

i think if it was done using autoit fields instead of html it would be rather huge and convoluted. Perhaps a reliable edit in place listview might do it.

I don't reckon the excel file can be migrated because it incorporates the regulated layout for the corporation.

A bit of javascript will be palced to auto-calculate the totals columns

Cheers

Edited February 11, 2008 by Will66

Sign In

Employee Time Sheet template

Recommended Posts

Will66

gseller

Tomb

Will66

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta