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

    • Nareshm
      By Nareshm
      I create message box but i dont know how to add button action when i click it.

      Help for : Add script for Button : Left, Right, Right Finger
      example when i press Left finger button do Case $idNotepad
                      $iPID = WinActivate ( " :PS :: Version - 2.8.0.0  - [eFS Bill]")
                              ControlSend(" :DS  - eF :: Version - 2.8.0.0  - [eF Bill]", "", "[NAME:btnReset]", "{esc}")
                              ControlClick ( " :DS  - eF :: Version - 2.8.0.0  - [eF Bill]", "", "[NAME:txtrdNo]")
                              ControlSend(" :DS  - eF:: Version - 2.8.0.0  - [eF Bill]", "", "[NAME:txtrdNo]", "^v")
                              ControlSend(" :DS  - eF :: Version - 2.8.0.0  - [eF Bill]", "", "[NAME:txtrdNo]", "{enter}")
      How to add like above ?
                    
                                                                sorry for my bad english
       
    • nacerbaaziz
      By nacerbaaziz
      Hi friends, I have a question, please.
      I have created a window containing a edit box
      I need to know how to change the keyboard language,
      e.g :
      if a user open the  script  it will be change the keyBord language   in window automatically into English
      I found more examples but didn't work with me .
      Note:  
       I use the windows 10 os
      Please help me .
      thanks in advance to all
      with my Greetings and my appreciation for all users and admins
    • JustinZandee
      By JustinZandee
      I want to create a loop which loops a code until for example f2 is pressed.
      So I tried something with _IsPressed but it doesn't work.
      Local $test = WinActivate("Notepad") Do Send("hi") Until _IsPressed("72", $test)  
      I don't know how I can do nothing when pressing f2 because it says "error: _IsPressed(): undefined function.".
      I did put 
      Send("hi") after Local $test = but it gives the same error.
      Does anyone know how to solve this problem? 
      Maybe I am wrong and I need to use a whole other Function.. I am new to AutoIT so I am sorry..
       
    • JustinZandee
      By JustinZandee
      I need a random string generator which creates 15 letters/numbers.
      How can I make that?
    • KB505
      By KB505
      Hello,
       
      I'm starting a script in AutoIT to automize a task in a software. I need to click a several Controls. 
      I'm just starting to learn AutoIT, and discovered that we can use the Window Info tool (au3info) to identify controls precisely and targetting safely a specific button.
      However, when I try it, I realize that most of the controls in my toolbar have the same Control Info (No ID, same class, same instance, ...). The only thing that changes is the ControlClicks Coord but I don't think that it's interesting, I don't see the difference with the Mouse Coordinates. Besides, I was aiming to write a stable script that would work even if the button changes a little bit of place for example. 
      Should I use another tool that Window Info tool to detect this Controls ? Is there another way to identify a Control ? 
      Thank you in advance for your help