Jump to content

Any improvements for this Room-Booking script?


Recommended Posts

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

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

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

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
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. 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By DJ143
      I have a autoit exe file which is used in upload/browse file functionality.  This has been integrated with selenium framework and I am invoking the autoit exe using Java process and runtime. 
      Now the issue is when I run the scripts and invoke the autoit exe in local it works perfectly.  But when I use selenium grid or jenkins to run the scripts in another windows server it is not working.
      Can anyone please suggest any solution for this?
    • By Hermes
      Hello, the script below will read column A from an excel file - and if a value matches in the browser, it will click the corresponding link and click on a specific button to paste the data, then writes "Completed" in Column B. It will continue to read from the excel file and do the same thing for all the remaining rows.
      #Include "Chrome.au3" #Include "wd_core.au3" #Include "wd_helper.au3" #Include "WinHttp.au3" #include <MsgBoxConstants.au3> #include <File.au3> #include <IE.au3> #include <Array.au3> #include <INet.au3> #include <AutoItConstants.au3> #include <WinAPIFiles.au3> #include <GDIPlus.au3> #include <Excel.au3> #Include "WinHttp.au3" #Include "_HtmlTable2Array.au3" Local $sDesiredCapabilities, $sSession SetupChrome() _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_LoadWait($sSession) _WD_Navigate($sSession, "table1.html") _WD_LoadWait($sSession) _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='main']") Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='main']") ;ConsoleWrite ("mat-table " & $sElement & @CRLF) Local $aArray1 = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//td[contains(@class,'data')]", $sElement, True) sleep(1000) For $i = 0 to UBound($aArray1) - 1 $aArray1[$i] = _WD_ElementAction($sSession, $aArray1[$i], 'text') Next ;_ArrayDisplay($aArray1) ;Email variables $SmtpServer = "" ; address for the smtp-server to use - REQUIRED $FromName = "Hermes" ; name from who the email was sent $FromAddress = "sender@gmail.com" ; address from where the mail should come $ToAddress = "recipient@gmail.com" ; destination address of the email - REQUIRED, use commas (,) to add more email addresses $Subject = "File not found" ; subject from the email - can be anything you want it to be $Body = "File not found!" ; the messagebody from the mail - can be left blank but then you get a blank mail $AttachFiles = "" ; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed $CcAddress = "" ; address for cc - leave blank if not needed $BccAddress = "" ; address for bcc - leave blank if not needed $Importance = "High" ; Send message priority: "High", "Normal", "Low" $Username = "" ; username for the account used from where the mail gets sent - REQUIRED $Password = "" ; password for the account used from where the mail gets sent - REQUIRED $IPPort = 25 ; port used for sending the mail $ssl = 0 ; enables/disables secure socket layer sending - put to 1 if using httpS $tls = 0 ; enables/disables TLS when required Local $oAppl = _Excel_Open() Local $sWorkbook = "c:\test.xlsx" Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ;open excel and pass both parameters If FileExists($sWorkbook) Then ;Check if the file exist. Local $oAppl = _Excel_Open() Local $sWorkbook = "c:\test.xlsx" Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ;open excel and pass both parameters Local $aArray2 = _Excel_RangeRead($oWorkbook,Default,$oWorkbook.ActiveSheet.Usedrange.Columns("A:A")) Local $iIdx Local $Skipline = 0 ;0==> first line Do Local $temprf For $i = 0 To UBound($aArray2) - 1 $temprf &= $aArray2[$i] _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, ".//a[contains(@class,'edit') and contains(text(),'Edit')]") Local $aElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//a[contains(@class,'edit') and contains(text(),'Edit')]", $sElement, True) $iIdx = _ArraySearch($aArray1, $aArray2[$i]) If @error Then ContinueLoop _WD_ElementAction($sSession, $aElement[$iIdx], 'click') If $i < $Skipline Then ContinueLoop $oRange = $oWorkbook.ActiveSheet.Range("B" & $i + 1 & ":XFD" & $i + 1) _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange) ;Paste Local $oTest4 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "pastebutton") _WD_ElementAction($sSession, $oTest4, 'click') Sleep(1000) ;Save Button Local $save3 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "button.button") _WD_ElementAction($sSession, $save3, 'click') _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Completed", "B" & $i+1) sleep(1000) Next Until (Not @error) _Excel_Close($oWorkbook) Else _INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $ToAddress, $Subject, $Body, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl, $tls) Exit EndIf _WD_LoadWait($sSession) ;Attaching files to emails Func _INetSmtpMailCom($s_SmtpServer, $s_FromName, $s_FromAddress, $s_ToAddress, $s_Subject = "", $as_Body = "", $s_CcAddress = "", $s_BccAddress = "", $s_Importance="Normal", $s_Username = "", $s_Password = "", $IPPort = 25, $ssl = 0, $tls = 0) Local $objEmail = ObjCreate("CDO.Message") $objEmail.From = '"' & $s_FromName & '" <' & $s_FromAddress & '>' $objEmail.To = $s_ToAddress Local $i_Error = 0 Local $i_Error_desciption = "" If $s_CcAddress <> "" Then $objEmail.Cc = $s_CcAddress If $s_BccAddress <> "" Then $objEmail.Bcc = $s_BccAddress $objEmail.Subject = $s_Subject If StringInStr($as_Body, "<") And StringInStr($as_Body, ">") Then $objEmail.HTMLBody = $as_Body Else $objEmail.Textbody = $as_Body & @CRLF EndIf $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = $s_SmtpServer If Number($IPPort) = 0 then $IPPort = 25 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = $IPPort ;Authenticated SMTP If $s_Username <> "" Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = $s_Username $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = $s_Password EndIf ; Set security params If $ssl Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True If $tls Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendtls") = True ;Update settings $objEmail.Configuration.Fields.Update ; Set Email Importance Switch $s_Importance Case "High" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "High" Case "Normal" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Normal" Case "Low" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Low" EndSwitch $objEmail.Fields.Update ; Sent the Message $objEmail.Send $objEmail="" EndFunc ;==>_INetSmtpMailCom Local $aDir = _FileListToArrayRec(@TempDir, "scoped_dir*;chrome_*", $FLTAR_FOLDERS, $FLTAR_NORECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH) Sleep(2000) For $i = 1 To $aDir[0] DirRemove($aDir[$i], $DIR_REMOVE) Next _WD_LoadWait($sSession) _WD_Shutdown() Func SetupChrome() _WD_Option('Driver', 'chromedriver.exe') _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}' EndFunc ;==>SetupChrome If the excel file doesn't exists in the folder, it will send an email to a specific recipient.
      What i am trying figure out now is if the excel crashes while the script/loop is running, I want to relaunch the excel file continue to the last row before the excel crashed. So if the value of column B is not marked as "completed", it should continue from that row
      Appreciate any help that I can get to achieve this.
      table1.html test.xlsx
    • By adityaparakh
      Hello ,
      I am trying to use Websockets in AutoIt.
      It is to fetch live stock market prices , API is provided and documentation available for python language.
      The link for the code snippet is :
      https://symphonyfintech.com/xts-market-data-front-end-api-v2/#tag/Introduction
      https://symphonyfintech.com/xts-market-data-front-end-api-v2/#tag/Instruments/paths/~1instruments~1subscription/post
       
      https://github.com/symphonyfintech/xts-pythonclient-api-sdk
       
      Second Link is to subscribe to a list of ExchangeInstruments.
      Now I would like to get live stock ltp (LastTradedPrice) for a few stocks whose "ExchangeInstrumentID" I know.
      I am able to use the WinHttp object to perform actions using simple codes like below :
      I have the secretKey and appkey and can generate the needed token. And get the unique ExchangeInstrumentID.

      Below code is just for example of how I am using WinHttp. Unrelated to socket part.
      Global $InteractiveAPItoken = IniRead(@ScriptDir & "\Config.ini", "token", "InteractiveAPItoken", "NA") $baseurl = "https://brokerlink.com/interactive/" $functionurl = "orders" $oHTTP = ObjCreate("winhttp.winhttprequest.5.1") $oHTTP.Open("POST", $baseurl & $functionurl, False) $oHTTP.SetRequestHeader("Content-Type", "application/json;charset=UTF-8") $oHTTP.SetRequestHeader("authorization", $InteractiveAPItoken) $pD = '{ "exchangeSegment": "NSEFO", "exchangeInstrumentID": ' & $exchangeInstrumentID & ', "productType": "' & $producttype & '", "orderType": "MARKET", "orderSide": "' & $orderside & '", "timeInForce": "DAY", "disclosedQuantity": 0, "orderQuantity": ' & $qty & ', "limitPrice": 0, "stopPrice": 0, "orderUniqueIdentifier": "' & $orderidentifier & '"}' $oHTTP.Send($pD) $oReceived = $oHTTP.ResponseText $oStatusCode = $oHTTP.Status
          
          
      But am struggling to understand and use socket.
      Would be of great help if you can have a look at the link mentioned above and help with the code sample for AutoIt.
      To connect and listen to a socket.
      Thanks a lot
       
    • By Hermes
      Hi, I have a site that has the following elements below: 
      <div>More element here</div> <div>More element here</div> <div>More element here</div> When I do this in Auto It:
      Local $oSelectDiv = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "div") _WD_HighlightElement($sSession, $oSelectDiv, 1) I also tried to add [3], but it doesnt seems to work:
      Local $oSelectDiv = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "div[3]") _WD_HighlightElement($sSession, $oSelectDiv, 1) It always highlight the first one, but I am trying to highlight the 3rd in the list. Is there anyway to select the 3rd div without having to add any class/id in the divs, and without using XPATH? The structure of the elements in that site were built that way.
    • By meety
      My au3 script program needs administrator privileges on win10 to be executed correctly, because it needs to be added to the boot---Script code:RegWrite("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run", @ScriptName, "REG_SZ", @ScriptFullPath)
      I need to let the program automatically get administrator privileges, so I use a function of #RequireAdmin. Although the program can be added to the boot, but does not execute after system boot up? why is that?
      What should I do?
      --------------------------------------------------------------------
      Update:
      I found that after using the #RequireAdmin function, the program cannot be executed after system boot up.
      An example is given below. after it is compiled, it will not be executed after system boot up in the following two ways.
      1. C:\ProgramData\Microsoft\Windows\Start Menu\Programs\StartUp\
      2. RegWrite("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run", @ScriptName, "REG_SZ", @ScriptFullPath)
      why is that?
      example
      #include <MsgBoxConstants.au3>
      #RequireAdmin
      Func test()
          MsgBox(0, "", "this is test message")
      EndFunc   ;==>test
      test()
       
       
×
×
  • Create New...