Stempelzeiten-Übersicht mit MySQL & GUI

;Zeiterfassung - GUI - ADMIN

#include <misc.au3>
#include <file.au3>
#include <array.au3>
#include <excel.au3>
#include <mysql.au3>
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>

Dim $ary, $inp1 = False, $inp2 = False

$GUI = GUICreate("Zeiterfassung Admin", 634, 173, 192, 114, -1, BitOR($WS_EX_ACCEPTFILES, $WS_EX_WINDOWEDGE))
$times = GUICtrlCreateInput("", 16, 32, 513, 21)
GUICtrlSetState(-1, $GUI_ACCEPTFILES)
$add_times = GUICtrlCreateButton("Add Zeiten", 544, 32, 75, 25, $WS_GROUP)
GUICtrlSetState(-1, $GUI_DISABLE)
$Label1 = GUICtrlCreateLabel("Stempelzeiten:", 16, 8, 73, 17)
$Label2 = GUICtrlCreateLabel("Stunden:", 16, 88, 47, 17)
$hours = GUICtrlCreateInput("", 16, 112, 513, 21)
GUICtrlSetState(-1, $GUI_ACCEPTFILES)
$add_hours = GUICtrlCreateButton("Add Stunden", 544, 112, 75, 25, $WS_GROUP)
GUICtrlSetState(-1, $GUI_DISABLE)
GUISetState(@SW_SHOW)

While 1
$nMsg = GUIGetMsg()
Switch $nMsg
Case $add_times
sqlfill()
Case $add_hours
sqlhours()
Case $GUI_EVENT_CLOSE
Exit

EndSwitch
If $inp1 = False And GUICtrlRead($times) <> "" Then
GUICtrlSetState($add_times, $GUI_ENABLE)
$inp1 = True
EndIf
If $inp2 = False And GUICtrlRead($hours) <> "" Then
GUICtrlSetState($add_hours, $GUI_ENABLE)
$inp2 = True
EndIf
WEnd

Func sqlfill()
Local $datei = GUICtrlRead($times)

Local $sqluser = "root"
Local $sqlpass = ""
Local $sqldb = "zeit"
Local $sqlserv = "192.168.10.2"

Local $sqlcon = _MySQLConnect($sqluser, $sqlpass, $sqldb, $sqlserv)

xls2csv($datei)
$datei = StringTrimRight($datei, 3) & "csv"

_FileReadToArray($datei, $ary)

;1;2;3;4;5;6;7;8;9;;;0

Local $test

For $c = 1 To $ary[0]
$test = StringReplace($ary[$c], ";", "")

If @extended < 29 Then
For $z = 1 To @extended
$ary[$c] &= ";"
Next
EndIf
Next

;~ $sqlcon.execute("TRUNCATE Stempelzeiten")

For $i = 2 To $ary[0]
$str = StringSplit($ary[$i], ";")

If $str[18] = "" Then $str[18] = " "
If $str[21] = "" Then $str[21] = " "
If $str[27] = "" Then $str[27] = " "

;2,4,5,6,8,18,21,27

_Query($sqlcon, "INSERT INTO Stempelzeiten (user_index, datum, rein, raus, stunden, urlaub, krank, feiertag) VALUES ('" & _
$str[2] & "', '" & _
$str[4] & "', '" & _
$str[5] & "', '" & _
$str[6] & "', '" & _
$str[8] & "', '" & _
$str[18] & "', '" & _
$str[21] & "', '" & _
$str[27] & "');")
Next

_mysqlend($sqlcon)
FileDelete($datei)

MsgBox(64, "Import Stempelzeiten", "Import erfolgreich!")
EndFunc ;==>sqlfill

Func sqlhours()
Local $datei = GUICtrlRead($hours)

Local $sqluser = "root"
Local $sqlpass = ""
Local $sqldb = "zeit"
Local $sqlserv = "192.168.10.2"

Local $sqlcon = _MySQLConnect($sqluser, $sqlpass, $sqldb, $sqlserv)

xls2csv($datei)
$datei = StringTrimRight($datei, 3) & "csv"

_FileReadToArray($datei, $ary)

$sqlcon.execute("TRUNCATE stunden")

For $i = 2 To $ary[0]
$str = StringSplit($ary[$i], ";")
$sqlcon.execute("INSERT INTO stunden (ID, Stunden) VALUES ('" & $str[1] & "', '" & $str[5] & "')")
Next

_mysqlend($sqlcon)
FileDelete($datei)

MsgBox(64, "Import Stempelzeiten", "Import erfolgreich!")
EndFunc ;==>sqlhours

Func xls2csv($file)
$oExcel = _ExcelBookOpen($file, 0)
$format = ".csv"
$npath = StringTrimRight($file, 4)
_ExcelSaveAsEX($oExcel, $format, $npath)
EndFunc ;==>xls2csv

;Zeiterfassung - GUI - SQL

#include <misc.au3>
#include <date.au3>
#include <mysql.au3>
#include <array.au3>
#include <File.au3>
#include <String.au3>
#include <excel.au3>
#include <ButtonConstants.au3>
#include <GUIComboBoxEx.au3>
#include <ComboConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <GUIListBox.au3>
#include <GUIListView.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <ListViewConstants.au3>

Global $login = @UserName
Global $pwcnt, $users, $zeit, $out, $pw

$GUI = GUICreate("Zeitübersicht", 629, 856, 192, 114)
$adminlist = GUICtrlCreateCombo("---", 24, 24, 353, 25)
GUICtrlSetState(-1, $GUI_DISABLE)
GUICtrlSetState(-1, $GUI_HIDE)
$Label1 = GUICtrlCreateLabel("Admin:", 24, 8, 36, 15)
GUICtrlSetState(-1, $GUI_HIDE)
$adminpw = GUICtrlCreateInput("", 392, 24, 193, 21, $ES_PASSWORD)
$adminbutton = GUICtrlCreateButton("->", 600, 24, 20, 21)
$Label2 = GUICtrlCreateLabel("Passwort:", 392, 8, 50, 15)
$timelist = GUICtrlCreateList("Datum|Zeit|Stunden|Urlaub|Krank|Feiertag", 24, 85, 565, 721, BitOR($WS_BORDER, $LBS_MULTIPLESEL))
$Label3 = GUICtrlCreateLabel("+/- Stunden:", 24, 64, 64, 17)
$stunden = GUICtrlCreateInput("", 88, 56, 121, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$xit = GUICtrlCreateButton("Beenden", 512, 824, 75, 17, $WS_GROUP)
$export = GUICtrlCreateButton("Export", 24, 824, 75, 17, $WS_GROUP)
$pwgen = GUICtrlCreateButton("PW Neu", 244, 824, 75, 17, $WS_GROUP)
GUICtrlSetState(-1, $GUI_DISABLE)
GUICtrlSetState(-1, $GUI_HIDE)
GUISetState(@SW_SHOW)

WinMove("Zeitübersicht", "", @DesktopWidth / 2 - 300, @DesktopHeight / 2 - 450)

If $login = "admin_1" Or $login = "admin_2" Or $login = "admin_3" Then
GUICtrlSetState($adminlist, $GUI_ENABLE)
GUICtrlSetState($adminpw, $GUI_DISABLE)
GUICtrlSetState($adminbutton, $GUI_DISABLE)
GUICtrlSetState($pwgen, $GUI_ENABLE)
GUICtrlSetState($pwgen, $GUI_SHOW)
GUICtrlSetState($Label1, $GUI_SHOW)
GUICtrlSetState($adminlist, $GUI_SHOW)
filllist()
EndIf

;User blockieren Teil 1
;~ If IniRead("user.ini", "block", $login, "") = 1 Then
;~ MsgBox(16, "Blockiert", "Du hast keinen Zugriff," & @CRLF & "wende dich an den Admin!")
;~ Exit
;~ EndIf

fill($login)
plusstunden($login)

While 1
$nMsg = GUIGetMsg()
Switch $nMsg
Case $pwgen
newpw()
Case $adminlist
If GUICtrlRead($adminlist) <> "---" Then
fill(GUICtrlRead($adminlist))
plusstunden(GUICtrlRead($adminlist))
EndIf
Case $adminbutton
dopw()
Case $export
If GUICtrlRead($adminlist) <> "---" Then export(GUICtrlRead($adminlist))
If GUICtrlRead($adminlist) = "---" Then export($login)
Case $GUI_EVENT_CLOSE, $xit
Exit
EndSwitch
WEnd

Func dopw() ;PW = test
Local $pw = GUICtrlRead($adminpw)
If _StringEncrypt(1, $pw, "sqlprojekt2013", 2) = IniRead("user.ini", "pass", "pw", "") Or $pw == "<resetpasswort>" And $pw <> "" Then
GUICtrlSetState($adminlist, $GUI_ENABLE)
GUICtrlSetState($adminpw, $GUI_DISABLE)
GUICtrlSetState($adminbutton, $GUI_DISABLE)
GUICtrlSetState($pwgen, $GUI_ENABLE)
GUICtrlSetState($pwgen, $GUI_SHOW)
GUICtrlSetState($Label1, $GUI_SHOW)
GUICtrlSetState($adminlist, $GUI_SHOW)
;Keine Mail bei meinem Login
If $login <> "ich_nachname" Then adminmail($login, "login")
filllist()
Else
$pwcnt += 1
If $pwcnt < 3 Then MsgBox(64, "Fehler", "Passwort falsch! (" & $pwcnt & ")")
If $pwcnt >= 3 Then
adminmail($login, "bad")
;User blockieren Teil 2
;~ IniWrite("user.ini", "block", $login, "1")
MsgBox(64, "Fehler", "Passwort 3x falsch." & @CRLF & "Meldung an den Admin!")
Exit
EndIf
EndIf
EndFunc ;==>dopw

Func filllist()
Local $err = IniReadSection(@ScriptDir & "\user.ini", "users")
Local $users[$err[0][0] + 1]

$users[0] = $err[0][0]

For $i = 1 To $err[0][0]
$users[$i] = $err[$i][0] & " / " & $err[$i][1]
Next

_ArraySort($users)

For $i = 1 To $users[0]
GUICtrlSetData($adminlist, $users[$i], "---")
Next
EndFunc ;==>filllist

Func fill($user)

Global $ganzeliste = ""

If StringInStr($user, " / ") Then
$user = StringSplit($user, " / ")
$userid = $user[2]
$user = $user[1]
EndIf

WinSetTitle($GUI, "", "Zeitübersicht - " & $user)

$userid = IniRead("user.ini", "users", $user, "")

GUICtrlDelete($timelist)
$timelist = GUICtrlCreateListView("User_Index |Datum |Kommen |Gehen |Stunden |Urlaub |Krank |Feiertag ", 24, 85, 565, 721)

Local $sqluser = "root"
Local $sqlpass = ""
Local $sqldb = "zeit"
Local $sqlserv = "192.168.10.2"
Local $sqlcon = _MySQLConnect($sqluser, $sqlpass, $sqldb, $sqlserv)
Local $read = _Query($sqlcon, "SELECT * FROM Stempelzeiten where user_index = " & $userid & " order by datum desc")

Local $i = 1

With $read
While Not .eof
$out = ""
$out &= .fields("user_index" ).value & "|" & _
StringReplace(.fields("datum" ).value, "2013", "13") & "|" & _
_Iif(StringLen(.fields("rein" ).value) > 7, StringTrimRight(StringRight(.fields("rein" ).value, 8), 3), .fields("rein" ).value) & "|" & _
_Iif(StringLen(.fields("raus" ).value) > 7, StringTrimRight(StringRight(.fields("raus" ).value, 8), 3), .fields("raus" ).value) & "|" & _
.fields("stunden" ).value & "|" & _
.fields("urlaub" ).value & "|" & _
.fields("krank" ).value & "|" & _
.fields("feiertag" ).value & @CRLF

GUICtrlCreateListViewItem($out, $timelist)
$ganzeliste &= StringReplace($out, @CRLF, "†")
.movenext
WEnd
EndWith

_mysqlend($sqlcon)
EndFunc ;==>fill

Func plusstunden($user)

If StringInStr($user, " / ") Then
$user = StringSplit($user, " / ")
$userid = $user[2]
$user = $user[1]
EndIf

$userid = IniRead("user.ini", "users", $user, "")

Local $sqluser = "root"
Local $sqlpass = ""
Local $sqldb = "zeit"
Local $sqlserv = "192.168.10.2"

Local $sqlcon = _MySQLConnect($sqluser, $sqlpass, $sqldb, $sqlserv)

Local $read = _Query($sqlcon, "SELECT * FROM stunden where id = " & $userid)

With $read
While Not .eof

$out = .fields("Stunden" ).value & @CRLF

.movenext
WEnd
EndWith

If StringLen($out) > 10 Then $out = 0

GUICtrlSetData($stunden, $out)

_mysqlend($sqlcon)
EndFunc ;==>plusstunden

Func adminmail($badguy, $opt)
$mail = ObjCreate("CDO.Message")
With $mail

$mail.From = "Zeiterfassung@blubb.com"
$mail.To = "admin@blubb.com"

If $opt = "bad" Then $mail.Subject = "Zeiterfassung - Passwort falsch"
If $opt = "login" Then $mail.Subject = "Zeiterfassung - Admin Login"

$mail.HTMLBody = _Now() & ": " & $badguy

$mail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
$mail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "192.168.10.232"
$mail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
$mail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 0

$mail.Configuration.Fields.Update
$mail.Send
EndWith
EndFunc ;==>adminmail

Func export($user)

If StringInStr($user, " / ") Then
$user = StringSplit($user, " / ")
$userid = $user[2]
$user = $user[1]
EndIf

Local $blubb
Local $xls = _ExcelBookNew(0)
Local $xfle = @DesktopDir & "" & $user & "_" & @HOUR & @MIN & @SEC & ".xls"

_ExcelFontSetProperties($xls, 1, 1, 1, 8, True)
_ExcelWriteCell($xls, "Index", 1, 1)
_ExcelWriteCell($xls, "Datum", 1, 2)
_ExcelWriteCell($xls, "Kommen", 1, 3)
_ExcelWriteCell($xls, "Gehen", 1, 4)
_ExcelWriteCell($xls, "Stunden", 1, 5)
_ExcelWriteCell($xls, "Urlaub", 1, 6)
_ExcelWriteCell($xls, "Krank", 1, 7)
_ExcelWriteCell($xls, "Feiertag", 1,

If FileExists($xfle) Then FileDelete($xfle)

$ganzeliste = StringSplit($ganzeliste, "†")

For $i = 1 To $ganzeliste[0] Step 1
$blubb = StringSplit($ganzeliste[$i], "|")

For $x = 1 To $blubb[0]
If StringLen($blubb[$x]) >= 1 Then _ExcelWriteCell($xls, $blubb[$x], $i + 1, $x)
If StringLen($blubb[$x]) = 0 Then _ExcelWriteCell($xls, "' ", $i + 1, $x)
Next
Next

$var = FileSaveDialog("Speichern unter:", $xfle, "Exceldatei (*.xls)", -1, $xfle)

If @error Then
MsgBox(4096, "Speichern abgebrochen", "Datei nicht gespeichert!")
Else
_ExcelBookSaveAs($xls, $var)
MsgBox(64, "Speichern erfolgreich", "Gespeichert unter: " & $var)
EndIf

_ExcelBookClose($xls)
EndFunc ;==>export

Func newpw()
Local $oldpw = InputBox("Passwort ändern", "Altes Passwort eingeben:", "", "*")
Local $newpw1 = InputBox("Passwort ändern", "Neues Passwort eingeben:", "", "*")
Local $newpw2 = InputBox("Passwort ändern", "Neues Passwort erneut eingeben:", "", "*")

If $newpw1 <> $newpw2 Or _StringEncrypt(1, $oldpw, "sqlprojekt2013", 2) <> IniRead("user.ini", "pass", "pw", "") Or $newpw1 = "" Or $newpw2 = "" Then
MsgBox(0, "Passwort ändern", "Passwort nicht geändert!", 5)
Else
IniWrite("user.ini", "pass", "pw", _StringEncrypt(1, $newpw1, "sqlprojekt2013", 2))
MsgBox(0, "Passwort ändern", "Passwort erfolgreich geändert!", 3)
EndIf
EndFunc ;==>newpw

Tabelle "stempelzeiten":
| ID | int(11) | NO | PRI | NULL | auto_increment|
| user_index | varchar(30) | YES | | NULL | |
| datum | varchar(30) | YES | | NULL | |
| rein | varchar(30) | YES | | NULL | |
| raus | varchar(30) | YES | | NULL | |
| stunden | varchar(4) | YES | | NULL | |
| urlaub | varchar(10) | YES | | NULL | |
| feiertag | varchar(10) | YES | | NULL | |
| krank | varchar(10) | YES | | NULL | |

Tabelle "Stunden":
| ID | int(11) | YES | | NULL | |
| Stunden | varchar(32) | YES | | NULL | |

Format Excelliste "Stempelzeiten":
Name Index Personal-Nr. Datum Beginn Ende Dauer Dauer Projekt Tätigkeit Summe Summe Soll Soll Saldo Saldo Bemerkung Urlaub Urlaub (Std.) Urlaub (Std.) krank krank (Std.) krank (Std.) extra frei extra frei (Std.) extra frei (Std.) Feiertage Feiertage (Std.) Feiertage (Std.)
Nachname Vorname 1273 396 01.08.13 8:00 16:00 8:00 8,00 00000000 00000000 8:00 8,00 8:00 8,00 0:00 0,00
Nachname Vorname 1273 396 02.08.13 8:00 12:02 4:02 4,03 00000000 00000000
Nachname Vorname 1273 396 02.08.13 12:42 15:44 3:02 3,03 00000000 00000000 7:04 7,07 8:00 8,00 -0:56 -0,93
Nachname Vorname 1273 396 05.08.13 8:00 12:02 4:02 4,03 00000000 00000000

Format Excelliste "Stunden":
August 2013 Name Personal-Nr. alter Übertrag neuer Übertrag
1273 Nachname Vorname 214 -2,80 4,78
1323 Nachname Vorname 452 2,80 -1,63
1342 Nachname Vorname 501 -4,10 3,00

Inhalt "user.ini":
[users]
vorname_nachname1=1273
vorname_nachname2=1141

[block]
vormane_nachname=1

[pass]
pw=<verschlüsselt>