Sign in to follow this  
Followers 0
r3dbullo88

Any improvements for this Room-Booking script?

8 posts in this topic

#1 ·  Posted (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:

#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 by r3dbullo88

Share this post


Link to post
Share on other sites



Any suggestions?

Share this post


Link to post
Share on other sites

#3 ·  Posted (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:

  1. Nothing before 07:00 and nothing after 20:00 can be scheduled.
  2. A booking cannot exceed 2 hours and is booked in 30 min increments.
  3. 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 by spudw2k

Share this post


Link to post
Share on other sites

#4 ·  Posted (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 by r3dbullo88

Share this post


Link to post
Share on other sites

#5 ·  Posted (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?

Func 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 by r3dbullo88

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Similar Content

    • Fhelipe
      By Fhelipe
      So I wanted to use my machine as a server for a program that had a unique login system. It can only log in 1 person and the password is in the script itself. I would like to adptar my code or change it completely so that I can create a database and the Script At Time Of Login Get Database Login And Log In
      Experience with Autoit: Basic                      (sorry for bad English)
      Experience with SQL: None                              (translator only)
      Code Currently Used To Login:
      ;=========================================================
      While 1
          $MSG = GUIGetMsg()
          Switch $MSG
          Case $ButtonOk
              If VerifyLogin(GUICtrlRead($USERNAME),GUICtrlRead($PASSWORD)) = 1 Then
                  GUIDelete($Form1)
                  MsgBox(-1,"Logado com Sucesso","Login Sucedido, Para Parar o Aimbot Pressione ESC!")
                  Run ("C:\Program Files (x86)\Steam\steamapps\common\Mitos.is The Game\Mitosis.exe")
                  RunP()
              Else
                  MsgBox(-1,"Error","Usúario ou Senha está incorreto, Ou Ainda Não Pagou!")
               EndIf
          Case -3
              Exit
           Case $ButtonCancel
              Exit
                Case $HelpButton
               MsgBox(-1, "Criadores", "Criadores: Zummey & Ralta")
      Case $Register
         MsgBox(-1, "ERRO", "PROGRAMA EM DESENVOLVIMENTO FUNÇÃO DESABILITADA")
          EndSwitch
      WEnd
      Func VerifyLogin($USERNAME,$PASSWORD)
          If $USERNAME = "Zummey" And $PASSWORD = "xd90fe10" Then
              Return 1
          Else
              Return 0
          EndIf
      EndFunc; End login
      ;=========================================================
    • IamKJ
      By IamKJ
      I am trying to allow the GUI to gather info as to when to execute a function.  I am having trouble doing this.  So far this is what I have.
       
      ;Timer Func timer () If Not IsDeclared("iMsgBoxAnswer") Then Local $iMsgBoxAnswer $iMsgBoxAnswer = MsgBox(36,"Timer","Please format your answer in 00:00:00:000") Select Case $iMsgBoxAnswer = 6 ;Yes Global $infotime = InputBox ('Time', 'What time to execute?') Do $rawtimer = ToolTip(@Hour & ':' & @Min & ':' & @Sec & ':' & _MSec()) until $rawtimer = $infotime if $rawtimer = $infotime Then msgbox (0,'Worked','Worked') Else EndIf Case $iMsgBoxAnswer = 7 ;No Exit EndSelect EndFunc Func _MSec() Local $stSystemTime = DllStructCreate('ushort;ushort;ushort;ushort;ushort;ushort;ushort;ushort') DllCall('kernel32.dll', 'none', 'GetSystemTime', 'ptr', DllStructGetPtr($stSystemTime)) $sMilliSeconds = StringFormat('%03d', DllStructGetData($stSystemTime, 8)) $stSystemTime = 0 Return $sMilliSeconds EndFunc I have also tried _GUIToolTip_GetText in order to read the tooltip until the time specified, but it still doesn't work.  Any help would be great.
    • IamKJ
      By IamKJ
      So I have a lot of different functions in the little program I'm making.  Most of them use the Do statement, such as finding images, clicking buttons, etc.
       
      The question I have is how would I be able to both
      1) Create a hotkey to pause whatever I am doing at the moment.  If I have to put something into every function in the program, I don't mind.
      2) Create a hotkey to exit whatever I am doing and stop all loops or commands and bring back the main GUI?
    • Manivel
      By Manivel
      Hi,
      Can any one please share the Connection string for SQL Developer. 
      How to Connect the SQL Developer from AutoIT. We need to execute the query through AutoIt. is that possible ?
    • Manivel
      By Manivel
      We are launching the AutoIT script through selenium and passing the values through command lines.
      Similarly able to return the value from AutoIt to selenium ?  If yes ? how to return the values ?