r3dbullo88 Posted October 18, 2016 Posted October 18, 2016 (edited) Ehi Guys, I've edited this post according to the big last update on this work. // This script handles the booking system of a room. The room is composed of 3 seats, which can be booked between 3 different time zones 07:00-09:00, 12:00-14:00 and 18:00-20:00. The function that can be used are: - Prenota ( Insert a Booking ) - Verifica disponibilità ( Display booking of given date / time zone) - Cancella prenotazione ( Delete a booking, using a passcode that is generated while creating the Booking ) - Le mie prenotazioni ( Display the bookings of the current user on given date ) The script is fully working, initially based on an Excel file and lately switched to SQLite which I feel more stable for this. What I am looking for here: I am looking on ways to improve the code Thanks in advance for any support, Luciano. Below, the last version: expandcollapse popup#include <Array.au3> #include <Date.au3> #include <SQLite.au3> #include <MsgBoxConstants.au3> #include <ButtonConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <GuiEdit.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <ComboConstants.au3> #include <DateTimeConstants.au3> #include <GuiComboBox.au3> Global $lista_orari[31]=["Seleziona Orario","07:00 - 07:30","07:00 - 08:00","07:00 - 08:30","07:00 - 09:00","07:30 - 08:00","07:30 - 08:30","07:30 - 09:00","08:00 - 08:30","08:00 - 09:00","08:30 - 09:00","12:00 - 12:30","12:00 - 13:00","12:00 - 13:30","12:00 - 14:00","12:30 - 13:00","12:30 - 13:30","12:30 - 14:00","13:00 - 13:30","13:00 - 14:00","13:30 - 14:00","18:00 - 18:30","18:00 - 19:00","18:00 - 19:30","18:00 - 20:00","18:30 - 19:00","18:30 - 19:30","18:30 - 20:00","19:00 - 19:30","19:00 - 20:00","19:30 - 20:00"] Global $lista_posti[4]=["Seleziona Posto","Poltrona","Cuscino A","Cuscino B"] Global $fascia_orari[4]=["Seleziona Fascia","Mattina","Pranzo","Sera"] Global $aPrenotazione,$posto_prenotazione,$pass_prenotazione,$aResult Global $DB="K:\Public Folder\Italy IT\Relax Room\testDB.db" ;Main GUI #Region ### START Koda GUI section ### Form=H:\AutoIT Scripts\Relax Room\Booking_Tool_Main.kxf $GUI_Main = GUICreate("Relax Room Booking Tool", 882, 451, 324, 354) GUISetBkColor(0x000000) $Console = GUICtrlCreateEdit("", 416, 8, 449, 425, BitOR($ES_AUTOVSCROLL,$ES_AUTOHSCROLL,$ES_WANTRETURN,$GUI_SS_DEFAULT_EDIT,$ES_READONLY)) GUICtrlSetData($Console,"Relax Room Booking Tool Console"&@crlf) $Button_Prenota = GUICtrlCreateButton("Prenota", 16, 80, 385, 81) $Button_Check = GUICtrlCreateButton("Verifica Disponibilità", 16, 184, 385, 81) $Button_Delete = GUICtrlCreateButton("Cancella Prenotazione", 16, 288, 385, 81) $Button_Exit = GUICtrlCreateButton("Esci", 304, 384, 97, 49) $Button_MyBook = GUICtrlCreateButton("Le mie prenotazioni", 144, 384, 145, 49) GUICtrlCreateLabel("Relax Room Booking Tool", 56, 24, 303, 37) GUICtrlSetFont(-1, 22, 400, 0, "Californian FB") GUICtrlSetColor(-1, 0xFF0000) $Biogen_Logo = GUICtrlCreatePic("K:\Public Folder\Italy IT\Relax Room\Aratari_Logo.jpg", 18, 394, 105, 41) GUISetState(@SW_SHOW,$GUI_Main) #EndRegion ### END Koda GUI section ### ;Prenota GUI #Region ### START Koda GUI section ### Form=H:\AutoIT Scripts\Relax Room\GUI_Prenota.kxf $GUI_Prenota = GUICreate("Relax Room - Prenota", 351, 316, 1236, 354) GUISetBkColor(0x000000) $Date1 = GUICtrlCreateDate(_NowCalc ( ), 152, 16, 185, 49,$DTS_SHORTDATEFORMAT) $Combo1 = GUICtrlCreateCombo("", 152, 96, 185, 25, BitOR($CBS_DROPDOWN,$CBS_AUTOHSCROLL)) GUICtrlCreateLabel("Data", 16, 24, 59, 37) GUICtrlSetFont(-1, 22, 400, 0, "Californian FB") GUICtrlSetColor(-1, 0xFF0000) GUICtrlCreateLabel("Orario", 16, 88, 80, 37) GUICtrlSetFont(-1, 22, 400, 0, "Californian FB") GUICtrlSetColor(-1, 0xFF0000) GUICtrlCreateLabel("Posto", 16, 160, 70, 37) GUICtrlSetFont(-1, 22, 400, 0, "Californian FB") GUICtrlSetColor(-1, 0xFF0000) $Combo2 = GUICtrlCreateCombo("", 152, 168, 185, 25, BitOR($CBS_DROPDOWN,$CBS_AUTOHSCROLL)) For $i=1 To Ubound($lista_orari)-1 GUICtrlSetData($Combo1,$lista_orari[$i]) Next For $i=1 To Ubound($lista_posti)-1 GUICtrlSetData($Combo2,$lista_posti[$i]) Next $Button_Conferma_Prenotazione = GUICtrlCreateButton("Prenota", 32, 232, 113, 57) $Button_Annulla_Prenotazione = GUICtrlCreateButton("Annulla", 208, 232, 113, 57) GUISetState(@SW_HIDE,$GUI_Prenota) #EndRegion ### END Koda GUI section ### ;Check GUI 2.0 #Region ### START Koda GUI section ### Form=h:\autoit scripts\relax room\gui_check.kxf $GUI_Check = GUICreate("Relax Room - Verifica disponibilità", 724, 284, 369, 473) GUISetBkColor(0x000000) $Date_Check = GUICtrlCreateDate(_NowCalc ( ), 152, 40, 185, 49,$DTS_SHORTDATEFORMAT) $Combo_Check = GUICtrlCreateCombo("", 184, 120, 153, 25, BitOR($CBS_DROPDOWN,$CBS_AUTOHSCROLL)) For $i=1 To Ubound($fascia_orari)-1 GUICtrlSetData($Combo_Check,$fascia_orari[$i]) Next GUICtrlCreateLabel("Data", 16, 48, 59, 37) GUICtrlSetFont(-1, 22, 400, 0, "Californian FB") GUICtrlSetColor(-1, 0xFF0000) GUICtrlCreateLabel("Fascia Oraria", 16, 112, 156, 37) GUICtrlSetFont(-1, 22, 400, 0, "Californian FB") GUICtrlSetColor(-1, 0xFF0000) $Button_Check_Verifica = GUICtrlCreateButton("Verifica", 32, 200, 113, 57) $Button_Check_Annulla = GUICtrlCreateButton("Annulla", 192, 200, 113, 57) $Edit_Check = GUICtrlCreateEdit("", 352, 8, 361, 257, BitOR($ES_AUTOVSCROLL,$ES_AUTOHSCROLL,$ES_WANTRETURN,$WS_VSCROLL)) GUICtrlSetData(-1, "Inserisci la data e la fascia oraria poi clicca su Verifica.") GUISetState(@SW_HIDE) #EndRegion ### END Koda GUI section ### ;MyBook GUI #Region ### START Koda GUI section ### Form=h:\autoit scripts\relax room\gui_prenota.kxf $GUI_MyBook = GUICreate("Relax Room - Le mie prenotazioni", 668, 163, 520, 521) GUISetBkColor(0x000000) $Date_MyBook = GUICtrlCreateDate(_NowCalc ( ), 80, 16, 193, 49,$DTS_SHORTDATEFORMAT) GUICtrlCreateLabel("Data", 16, 24, 59, 37) GUICtrlSetFont(-1, 22, 400, 0, "Californian FB") GUICtrlSetColor(-1, 0xFF0000) $Button_MyBook_Verifica = GUICtrlCreateButton("Verifica", 16, 88, 113, 57) $Button_MyBook_Annulla = GUICtrlCreateButton("Annulla", 160, 88, 113, 57) $Edit_MyBook = GUICtrlCreateEdit("Seleziona la data e premi Verifica", 288, 8, 369, 145, BitOR($ES_AUTOVSCROLL,$ES_AUTOHSCROLL,$ES_WANTRETURN,$WS_VSCROLL)) GUICtrlSetData(-1, "Seleziona la data e premi Verifica") GUISetState(@SW_HIDE) #EndRegion ### END Koda GUI section ### ;Delete GUI #Region ### START Koda GUI section ### Form=h:\autoit scripts\relax room\gui_check.kxf $GUI_Delete = GUICreate("Relax Room - Cancella prenotazione", 444, 186, 462, 482) GUISetBkColor(0x000000) GUICtrlCreateLabel("Codice Prenotazione", 8, 56, 241, 37) GUICtrlSetFont(-1, 22, 400, 0, "Californian FB") GUICtrlSetColor(-1, 0xFF0000) $Button_Delete_Conferma = GUICtrlCreateButton("Conferma", 72, 120, 113, 57) $Button_Delete_Annulla = GUICtrlCreateButton("Annulla", 264, 120, 113, 57) GUICtrlCreateLabel("Inserisci il codice della prenotazione che desideri eliminare e poi premi su Conferma", 24, 8, 393, 17) GUICtrlSetFont(-1, 9, 400, 0, "Californian FB") GUICtrlSetColor(-1, 0xFF0000) $pass_delete = GUICtrlCreateInput("", 264, 56, 177, 37) GUICtrlSetLimit(-1, 6) GUICtrlSetFont(-1, 18, 400, 0, "MS Sans Serif") GUISetState(@SW_HIDE) #EndRegion ### END Koda GUI section ### Func WriteDB($aPrenotazione) Local $aResult, $iRows, $iColumns, $iRval _SQLite_Startup("sqlite3.dll") If @error Then Exit MsgBox(17, 'Startup Error', '@ERROR = ' & @error & @CRLF & '@EXTENDED = ' & @extended) Local $hDB = _SQLite_Open($DB) ; for a memory DB or _SQLite_Open("myDB.sq3") for a disk-based DB If @error Then Exit MsgBox(17, 'DB open failed', '@ERROR = ' & @error & @CRLF & '@EXTENDED = ' & @extended) ;-- If we do a Fresh Startup, with an Empty DB, we need to create it: ;First startup, need to create DB ;If Not _SQLite_Exec(-1, "CREATE TABLE Booking (data_prenotazione,fascia_prenotazione,cod_ora_prenotazione,ora_prenotazione,posto_prenotazione,username,pass_prenotazione);") = $SQLITE_OK Then _ ;MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) If Not _SQLite_Exec(-1, "INSERT INTO Booking VALUES ("&$aPrenotazione&");") = $SQLITE_OK Then If _SQLite_ErrMsg()="database is locked" Then MsgBox(64,"Database bloccato","Il database è in uso da un'altro utente, riprovare più tardi.",5) EndIf Else _GUICtrlEdit_AppendText($Console,"Prenotazione inserita con successo, prendi nota del codice di prenotazione: "&$pass_prenotazione&@crlf) MsgBox(64,"Prenotazione OK!","Il codice della tua prenotazione è: "&$pass_prenotazione,10) EndIf _SQLite_Close() _SQLite_Shutdown() EndFunc Func ReadDB() Local $iRows, $iColumns, $iRval _SQLite_Startup("sqlite3.dll") If @error Then Exit MsgBox(17, 'Startup Error', '@ERROR = ' & @error & @CRLF & '@EXTENDED = ' & @extended) Local $hDB = _SQLite_Open($DB) If @error Then Exit MsgBox(17, 'DB open failed', '@ERROR = ' & @error & @CRLF & '@EXTENDED = ' & @extended) If _SQLite_ErrMsg()="database is locked" Then MsgBox(64,"Database bloccato","Il database è in uso da un'altro utente, riprovare più tardi.",5) EndIf $iRval = _SQLite_GetTable2d(-1, "SELECT * FROM Booking WHERE data_prenotazione = '"&$data_prenotazione&"' AND fascia_prenotazione='"&$fascia_prenotazione&"';", $aResult, $iRows, $iColumns) _SQLite_Close() _SQLite_Shutdown() EndFunc Func CheckDB() Local $data = GUICtrlRead($Date_Check) Local $fascia = GUICtrlRead($Combo_Check) Local $iRows, $iColumns, $iRval _SQLite_Startup("sqlite3.dll") If @error Then Exit MsgBox(17, 'Startup Error', '@ERROR = ' & @error & @CRLF & '@EXTENDED = ' & @extended) Local $hDB = _SQLite_Open($DB) ; for a memory DB or _SQLite_Open("myDB.sq3") for a disk-based DB If @error Then Exit MsgBox(17, 'DB open failed', '@ERROR = ' & @error & @CRLF & '@EXTENDED = ' & @extended) If _SQLite_ErrMsg()="database is locked" Then MsgBox(64,"Database bloccato","Il database è in uso da un'altro utente, riprovare più tardi.",5) EndIf $iRval = _SQLite_GetTable2d(-1, "SELECT * FROM Booking WHERE data_prenotazione = '"&$data&"' AND fascia_prenotazione = '"&$fascia&"';", $aResult, $iRows, $iColumns) _SQLite_Close() _SQLite_Shutdown() If $iRval = $SQLITE_OK Then Local $text_display For $i = 1 to Ubound($aResult)-1 $text_display &=@crlf & " - - - " & @crlf & $aResult[$i][3] & " - " & $aResult[$i][4] & " - " & $aResult[$i][5] Next If $text_display = "" Then GUICtrlSetData($Edit_Check,"Non ci sono prenotazioni nella data selezionata ( "&$data&" ).") Else GUICtrlSetData($Edit_Check,"Le prenotazioni per la data e fascia oraria selezionata"&@crlf&"( "&$data&" - "&$fascia&" ):"&@crlf&$text_display) EndIf Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf EndFunc Func CheckIfRoomIsFree($fascia_prenotazione,$posto_prenotazione) Local $room_status=0 ; 0=libera, 1,2,3,4,5,... = Mostra l'orario in cui la stanza è occupata e in conflitto con l'orario che desideriamo prenotare For $i = 1 To UBound($aResult,1)-1 If $aResult[$i][4] = $posto_prenotazione Then ; Se si tratta dello stesso posto: If $aResult[$i][1] = $fascia_prenotazione Then ; Se si tratta della stessa fascia oraria ;Allora controllo l'orario ConsoleWrite(" - - START - - "&@crlf) ConsoleWrite($aResult[$i][0] & " <- Booked | Trying to Book-> " & $data_prenotazione&@crlf) ConsoleWrite($aResult[$i][4] & " <- Booked | Trying to Book-> " & $posto_prenotazione&@crlf) ConsoleWrite($aResult[$i][1] & " <- Booked | Trying to Book-> " & $fascia_prenotazione&@crlf) ConsoleWrite($aResult[$i][2] & " <- Booked | Trying to Book-> " & $cod_ora_prenotazione&@crlf) ConsoleWrite($aResult[$i][3] & " <- Booked | Trying to Book-> " & $lista_orari[$cod_ora_prenotazione]&@crlf) ConsoleWrite(" - - FINISH - - "&@crlf) Local $right_cod_ora = StringRight($cod_ora_prenotazione,1) Switch StringRight($aResult[$i][2],1) Case 1 If $right_cod_ora <= 4 AND $right_cod_ora >= 1 Then $room_status=1 ;BOOKING FAILURE -> Informare l'utente che la sala è occupata in fascia 1 ExitLoop EndIf Case 2 If $right_cod_ora <= 7 Then $room_status=2 ;BOOKING FAILURE -> Informare l'utente che la sala è occupata in fascia 2 ExitLoop EndIf Case 3 If $right_cod_ora <= 9 Then $room_status=3 ;BOOKING FAILURE -> Informare l'utente che la sala è occupata in fascia 3 ExitLoop EndIf Case 4 $room_status=4 ;BOOKING FAILURE -> Informare l'utente che la sala è occupata in fascia 4 ExitLoop Case 5 If $right_cod_ora >= 2 AND $right_cod_ora <= 7 Then $room_status=5 ;BOOKING FAILURE -> Informare l'utente che la sala è occupata in fascia 5 ExitLoop EndIf Case 6 If $right_cod_ora >= 2 AND $right_cod_ora <= 9 Then $room_status=6 ;BOOKING FAILURE -> Informare l'utente che la sala è occupata in fascia 6 ExitLoop EndIf Case 7 If $right_cod_ora >= 2 AND $right_cod_ora <= 9 OR $right_cod_ora = 0 Then $room_status=7 ;BOOKING FAILURE -> Informare l'utente che la sala è occupata in fascia 7 ExitLoop EndIf Case 8 If $right_cod_ora =1 OR $right_cod_ora =2 OR $right_cod_ora =5 OR $right_cod_ora =0 Then $room_status=0 ;BOOKING ACCEPT -> Confermare prenotazione Else $room_status=8 ;BOOKING FAILURE -> Informare l'utente che la sala è occupata in fascia 8 ExitLoop EndIf Case 9 If $right_cod_ora =1 OR $right_cod_ora =2 OR $right_cod_ora =5 Then $room_status=0 ;BOOKING ACCEPT -> Confermare prenotazione Else $room_status=9 ;BOOKING FAILURE -> Informare l'utente che la sala è occupata in fascia 9 ExitLoop EndIf Case 0 If $right_cod_ora =1 OR $right_cod_ora =2 OR $right_cod_ora =3 OR $right_cod_ora =5 OR $right_cod_ora =6 OR $right_cod_ora =8 Then $room_status=0 ;BOOKING ACCEPT -> Confermare prenotazione Else $room_status=10 ;BOOKING FAILURE -> Informare l'utente che la sala è occupata in fascia 10 ExitLoop EndIf EndSwitch EndIf EndIf Next If $room_status>0 Then MsgBox(48,"Prenotazione fallita","La combinazione data/orario/posto scelta non è disponibile:"&@crlf&"("&$data_prenotazione&"-"&$ora_prenotazione&"-"&$posto_prenotazione&")"&@crlf&@crlf&"Conflitto con la seguente prenotazione:"&@crlf&"Data: "&$aResult[$i][0]&@crlf&"Orario: "&$aResult[$i][3]&@crlf&"Posto: "&$aResult[$i][4]&@crlf&"User: "&$aResult[$i][5]) _GUICtrlEdit_AppendText($Console,"La combinazione data/orario/posto scelta non è disponibile:"&@crlf&"("&$data_prenotazione&"-"&$ora_prenotazione&"-"&$posto_prenotazione&")"&@crlf&@crlf&"Conflitto con la seguente prenotazione:"&@crlf&"Data: "&$aResult[$i][0]&@crlf&"Orario: "&$aResult[$i][3]&@crlf&"Posto: "&$aResult[$i][4]&@crlf&"User: "&$aResult[$i][5]&@crlf) EndIf Return $room_status EndFunc Func Prenota() _GUICtrlEdit_SetText($Console,"Relax Room Booking Tool Console"&@crlf) Global $data_prenotazione = GUICtrlRead($Date1) Global $username = @UserName Global $cod_ora_prenotazione = _GUICtrlComboBox_GetCurSel($Combo1) $cod_ora_prenotazione = $cod_ora_prenotazione+1 Global $ora_prenotazione = $lista_orari[$cod_ora_prenotazione] Global $fascia_prenotazione If $cod_ora_prenotazione <= 10 Then $fascia_prenotazione = "Mattina" Consolewrite("Stai prenotando la mattina"&@crlf) ElseIf $cod_ora_prenotazione <= 20 Then $fascia_prenotazione = "Pranzo" Consolewrite("Stai prenotando il pranzo"&@crlf) Else $fascia_prenotazione = "Sera" Consolewrite("Stai prenotando la sera"&@crlf) EndIf Local $posto=_GUICtrlComboBox_GetCurSel($Combo2) $posto = $posto+1 $posto_prenotazione = $lista_posti[$posto] Local $AreYouSure=MsgBox(4,"Prenotazione Relax Room","Conferma che i dati sono corretti:"&@crlf&"Giorno: "&$data_prenotazione& " - Orario: "&$lista_orari[$cod_ora_prenotazione]&" - Posto: "&$posto_prenotazione&" - Utente: "&$username) If $AreYouSure = 6 Then _GUICtrlEdit_AppendText($Console,@crlf) Local $pass_i $pass_prenotazione="" For $pass_i = 1 To 6 $pass_prenotazione &= Random(0, 9, 1) ;Generate random passcode Next $aPrenotazione = "'"&$data_prenotazione&"','"&$fascia_prenotazione&"','"&$cod_ora_prenotazione&"','"&$ora_prenotazione&"','"&$posto_prenotazione&"','"&$username&"','"&$pass_prenotazione&"'" ;Read the database, look for bookings on given Date ReadDB() ;Launch Function to check if room is free in given date/time/seat Local $check=CheckIfRoomIsFree($fascia_prenotazione,$posto_prenotazione) IF $check = 0 Then WriteDB($aPrenotazione) GUISetState(@SW_HIDE,$GUI_Prenota) EndIf Else MsgBox(0,"Prenotazione Relax Room","La prenotazione è stata annullata") EndIf EndFunc Func MyBook_Verifica() Local $data = GUICtrlRead($Date_MyBook) Local $iRows, $iColumns, $iRval _SQLite_Startup("sqlite3.dll") If @error Then Exit MsgBox(17, 'Startup Error', '@ERROR = ' & @error & @CRLF & '@EXTENDED = ' & @extended) Local $hDB = _SQLite_Open($DB) ; for a memory DB or _SQLite_Open("myDB.sq3") for a disk-based DB If @error Then Exit MsgBox(17, 'DB open failed', '@ERROR = ' & @error & @CRLF & '@EXTENDED = ' & @extended) If _SQLite_ErrMsg()="database is locked" Then MsgBox(64,"Database bloccato","Il database è in uso da un'altro utente, riprovare più tardi.",5) EndIf $iRval = _SQLite_GetTable2d(-1, "SELECT * FROM Booking WHERE data_prenotazione = '"&$data&"' AND username = '"&@UserName&"';", $aResult, $iRows, $iColumns) _SQLite_Close() _SQLite_Shutdown() If $iRval = $SQLITE_OK Then Local $text_display For $i = 1 to Ubound($aResult)-1 $text_display &=@crlf & " - - - " & @crlf & $aResult[$i][3] & " - " & $aResult[$i][4] & " - " & $aResult[$i][5] & " - Codice Prenotazione: " & $aResult[$i][6] Next If $text_display = "" Then GUICtrlSetData($Edit_MyBook,"Non ci sono prenotazioni a tuo nome nella data selezionata ( "&$data&" ).") Else GUICtrlSetData($Edit_MyBook,"Le tue prenotazioni per la data selezionata: ( "&$data&" ):"&@crlf&$text_display) EndIf Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf EndFunc Func DeleteDB() Local $pass = GUICtrlRead($pass_delete) If $pass = "" OR StringLen($pass)<6 Then MsgBox(48,"Relax Room - Cancella prenotazione","Il codice prenotazione inserito non è valido.") Return EndIf Local $iRows, $iColumns, $iRval _SQLite_Startup("sqlite3.dll") If @error Then Exit MsgBox(17, 'Startup Error', '@ERROR = ' & @error & @CRLF & '@EXTENDED = ' & @extended) Local $hDB = _SQLite_Open($DB) If @error Then Exit MsgBox(17, 'DB open failed', '@ERROR = ' & @error & @CRLF & '@EXTENDED = ' & @extended) If _SQLite_ErrMsg()="database is locked" Then MsgBox(64,"Database bloccato","Il database è in uso da un'altro utente, riprovare più tardi.",5) EndIf $iRval = _SQLite_GetTable2d(-1, "SELECT * FROM Booking WHERE pass_prenotazione = '"&$pass&"' AND username ='"&@UserName&"';", $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then Local $text_display For $i = 1 to Ubound($aResult)-1 $text_display &=@crlf & $aResult[$i][0] & " - " & $aResult[$i][3] & " - " & $aResult[$i][4] & " - " & $aResult[$i][5] Next If $text_display = "" Then MsgBox(48,"Relax Room - Cancella prenotazione","Non ci sono prenotazioni a tuo nome con questo codice.",5) Else Local $response = MsgBox(65,"Relax Room - Cancella prenotazione","Vuoi eliminare la seguente prenotazione:"&@crlf&$text_display,8) IF $response = 1 Then If @error Then Exit MsgBox(17, 'DB open failed', '@ERROR = ' & @error & @CRLF & '@EXTENDED = ' & @extended) _SQLite_Exec(-1, "DELETE FROM Booking WHERE pass_prenotazione = '"&$pass&"' AND username ='"&@UserName&"';") MsgBox(64,"Relax Room - Cancella prenotazione","La prenotazione è stata annullata",5) GUISetState(@SW_HIDE,$GUI_Delete) ElseIf $response = -1 Then MsgBox(64,"Relax Room - Cancella prenotazione","TIMEOUT: La prenotazione non è stata annullata",5) Else MsgBox(64,"Relax Room - Cancella prenotazione","La prenotazione non è stata eliminata.",5) EndIf EndIf Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf _SQLite_Close() _SQLite_Shutdown() EndFunc While 1 $nMsg = GUIGetMsg(1) Switch $nMsg[1] Case $GUI_Main Switch $nMsg[0] Case $GUI_EVENT_CLOSE Exit Case $Button_Prenota GUISetState(@SW_SHOW,$GUI_Prenota) Case $Button_Check GUISetState(@SW_SHOW,$GUI_Check) Case $Button_MyBook GUISetState(@SW_SHOW,$GUI_MyBook) Case $Button_Delete GUISetState(@SW_SHOW,$GUI_Delete) Case $Button_Exit Exit EndSwitch Case $GUI_Prenota Switch $nMsg[0] Case $GUI_EVENT_CLOSE GUISetState(@SW_HIDE,$GUI_Prenota) GUICtrlSetState($Button_Prenota, $GUI_ENABLE) Case $Button_Annulla_Prenotazione GUISetState(@SW_HIDE,$GUI_Prenota) GUICtrlSetState($Button_Prenota, $GUI_ENABLE) Case $Button_Conferma_Prenotazione If GUICtrlRead($Combo1) = "" Then MsgBox(48,"Selezione non valida","Devi selezionare un orario dal menu a tendina.") ElseIF GUICtrlRead($Combo2) = "" Then MsgBox(48,"Selezione non valida","Devi selezionare un posto dal menu a tendina") Else GUICtrlSetState($Button_Conferma_Prenotazione, $GUI_DISABLE) Prenota() GUICtrlSetState($Button_Conferma_Prenotazione, $GUI_ENABLE) EndIf EndSwitch Case $GUI_Check Switch $nMsg[0] Case $GUI_EVENT_CLOSE GUISetState(@SW_HIDE,$GUI_Check) Case $Button_Check_Verifica GUICtrlSetState($Button_Check_Verifica, $GUI_DISABLE) CheckDB() GUICtrlSetState($Button_Check_Verifica, $GUI_ENABLE) Case $Button_Check_Annulla GUISetState(@SW_HIDE,$GUI_Check) EndSwitch Case $GUI_MyBook Switch $nMsg[0] Case $GUI_EVENT_CLOSE GUISetState(@SW_HIDE,$GUI_MyBook) Case $Button_MyBook_Annulla GUISetState(@SW_HIDE,$GUI_MyBook) Case $Button_MyBook_Verifica GUICtrlSetState($Button_MyBook_Verifica, $GUI_DISABLE) MyBook_Verifica() GUICtrlSetState($Button_MyBook_Verifica, $GUI_ENABLE) EndSwitch Case $GUI_Delete Switch $nMsg[0] Case $GUI_EVENT_CLOSE GUISetState(@SW_HIDE,$GUI_Delete) Case $Button_Delete_Annulla GUISetState(@SW_HIDE,$GUI_Delete) Case $Button_Delete_Conferma GUICtrlSetState($Button_Delete_Conferma, $GUI_DISABLE) DeleteDB() GUICtrlSetState($Button_Delete_Conferma, $GUI_ENABLE) EndSwitch EndSwitch WEnd Edited October 26, 2016 by r3dbullo88
spudw2k Posted October 19, 2016 Posted October 19, 2016 (edited) Without diving too deep into the code a couple of things came to mind. Firstly your pre-defined schedules. I was looking to see if there was a pattern...best I could tell, the following conditions are representative of the schedules you are enabling: Nothing before 07:00 and nothing after 20:00 can be scheduled. A booking cannot exceed 2 hours and is booked in 30 min increments. 09:00 - 12:00 is blocked out, as well as from 14:00 - 18:00. If all of those statements are true and all encompassing, you could build logic to enforce those conditions instead of defining each unique schedule. Secondly, in the CheckRoomFree func it looks like there is potential to optimize the stack of if statements. Once again without diving into it...I don't have any specific suggestions other than the following: You use StringRight over and over in that loop. You'd might be better suited to set a variable to the return value of StringRight and then evaluate the variable instead of re-running StringRight every time. Here's a crude example (pseudo-code...notional/non-functional): $sLocation = "Location2" $iLocationValue = StringRight($sLocation,1) Switch ;.... Case 1 If $iLocationValue = ;.... Case 2 If $iLocationValue <= ;..... Edited October 19, 2016 by spudw2k Spoiler Things I've Made: Always On Top Tool ◊ AU History ◊ Deck of Cards ◊ HideIt ◊ ICU ◊ Icon Freezer ◊ Ipod Ejector ◊ Junos Configuration Explorer ◊ Link Downloader ◊ MD5 Folder Enumerator ◊ PassGen ◊ Ping Tool ◊ Quick NIC ◊ Read OCR ◊ RemoteIT ◊ SchTasksGui ◊ SpyCam ◊ System Scan Report Tool ◊ System UpTime ◊ Transparency Machine ◊ VMWare ESX Builder Misc Code Snippets: ADODB Example ◊ CheckHover ◊ Detect SafeMode ◊ DynEnumArray ◊ GetNetStatData ◊ HashArray ◊ IsBetweenDates ◊ Local Admins ◊ Make Choice ◊ Recursive File List ◊ Remove Sizebox Style ◊ Retrieve PNPDeviceID ◊ Retrieve SysListView32 Contents ◊ Set IE Homepage ◊ Tickle Expired Password ◊ Transpose Array Projects: Drive Space Usage GUI ◊ LEDkIT ◊ Plasma_kIt ◊ Scan Engine Builder ◊ SpeeDBurner ◊ SubnetCalc Cool Stuff: AutoItObject UDF ◊ Extract Icon From Proc ◊ GuiCtrlFontRotate ◊ Hex Edit Funcs ◊ Run binary ◊ Service_UDF
r3dbullo88 Posted October 19, 2016 Author Posted October 19, 2016 (edited) Thank you for your thoughts spudw2k, I will fix right now that abuse of StringRight function, and then will work back towards a better way of handling the schedule possibilities. Will update once done. Update: Probably I should split booking times in "booking_start" and "booking_end", in order to perform the checks better. Luciano Edited October 19, 2016 by r3dbullo88
r3dbullo88 Posted October 26, 2016 Author Posted October 26, 2016 (edited) I've updated the first post as I completed the script with all the functions that were missing. I am now facing the logical aspect of the time scheduling and actually found what it seems to be a good workaround. With this method I could completely avoid the CheckIfRoomIsFree() function, so less lines but abuse of StringLeft & Right... Any thoughts on this? expandcollapse popupFunc ReadDB() Local $booking_time= ---> Get from GuiCtrlRead Local $DB="K:\Public Folder\Italy IT\Relax Room\testDB.db" Local $iRows, $iColumns, $iRval,$aResult _SQLite_Startup("sqlite3.dll") If @error Then Exit MsgBox(17, 'Startup Error', '@ERROR = ' & @error & @CRLF & '@EXTENDED = ' & @extended) Local $hDB = _SQLite_Open($DB) If @error Then Exit MsgBox(17, 'DB open failed', '@ERROR = ' & @error & @CRLF & '@EXTENDED = ' & @extended) If _SQLite_ErrMsg()="database is locked" Then MsgBox(64,"Database bloccato","Il database è in uso da un'altro utente, riprovare più tardi.",5) EndIf $iRval = _SQLite_GetTable2d(-1, "SELECT * FROM Booking WHERE data_prenotazione = '----> Get From GuiCtrlRead';", $aResult, $iRows, $iColumns) _SQLite_Close() _SQLite_Shutdown() For $i=1 to Ubound($aResult)-1 ;Separate Hours & Minutes $booked_sh = StringLeft($aResult[$i][3],2) $booked_sm = StringLeft($aResult[$i][3],5) $booked_sm = StringRight($booked_sm,2) $booked_eh = StringRight($aResult[$i][3],5) $booked_eh = StringLeft($booked_eh,2) $booked_em = StringRight($aResult[$i][3],2) $booking_sh = StringLeft($booking_time,2) $booking_sm = StringLeft($booking_time,5) $booking_sm = StringRight($booking_sm,2) $booking_eh = StringRight($booking_time,5) $booking_eh = StringLeft($booking_eh,2) $booking_em = StringRight($booking_time,2) ;Compare If $booking_sh > $booked_sh AND $booking_sh >= $booked_eh Then ConsoleWrite("La mia prenotazione "&$booking_time&" non è in conflitto con la prenotazione "&$i&": "&$aResult[$i][3]&@crlf) ElseIf $booking_eh < $booked_sh Then ConsoleWrite("La mia prenotazione "&$booking_time&" non è in conflitto con la prenotazione "&$i&": "&$aResult[$i][3]&@crlf) ElseIf $booking_sh >= $booked_sh AND $booking_eh <= $booked_eh Then ConsoleWrite("La mia prenotazione "&$booking_time&" è in conflitto con la prenotazione "&$i&": "&$aResult[$i][3]&@crlf) Else ConsoleWrite("La mia prenotazione "&$booking_time&" non è in conflitto con la prenotazione "&$i&": "&$aResult[$i][3]&@crlf) EndIf Next EndFunc Edited October 26, 2016 by r3dbullo88
spudw2k Posted October 26, 2016 Posted October 26, 2016 Well, arguably the most sophisticated way to parse your booking time would be to use Regular Expressions, but I'm no regex expert. Another option is the StringMid function. It allows you to extract characters from a string (from left-to-right) at a specified location and length. You could use that to grab parts of the string in one step instead of grabbing the left side of a string and subsequently the right. Spoiler Things I've Made: Always On Top Tool ◊ AU History ◊ Deck of Cards ◊ HideIt ◊ ICU ◊ Icon Freezer ◊ Ipod Ejector ◊ Junos Configuration Explorer ◊ Link Downloader ◊ MD5 Folder Enumerator ◊ PassGen ◊ Ping Tool ◊ Quick NIC ◊ Read OCR ◊ RemoteIT ◊ SchTasksGui ◊ SpyCam ◊ System Scan Report Tool ◊ System UpTime ◊ Transparency Machine ◊ VMWare ESX Builder Misc Code Snippets: ADODB Example ◊ CheckHover ◊ Detect SafeMode ◊ DynEnumArray ◊ GetNetStatData ◊ HashArray ◊ IsBetweenDates ◊ Local Admins ◊ Make Choice ◊ Recursive File List ◊ Remove Sizebox Style ◊ Retrieve PNPDeviceID ◊ Retrieve SysListView32 Contents ◊ Set IE Homepage ◊ Tickle Expired Password ◊ Transpose Array Projects: Drive Space Usage GUI ◊ LEDkIT ◊ Plasma_kIt ◊ Scan Engine Builder ◊ SpeeDBurner ◊ SubnetCalc Cool Stuff: AutoItObject UDF ◊ Extract Icon From Proc ◊ GuiCtrlFontRotate ◊ Hex Edit Funcs ◊ Run binary ◊ Service_UDF
coffeeturtle Posted October 26, 2016 Posted October 26, 2016 On 10/19/2016 at 9:19 AM, r3dbullo88 said: Any suggestions? would you mind providing your test.db so we can give it a run?
r3dbullo88 Posted November 2, 2016 Author Posted November 2, 2016 Sure, here it is. I will have to do some changes, because the "Business" is asking me couple changes. The new request is to have people book a maximum of 30 minutes, therefore a lot of complications go away testDB.db
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