library(shiny) library(shinyjs) library(shinydashboard) library(DBI) library(RMySQL) library(DT) library(leaflet) library(ggmap) library(shinythemes) library(reticulate)

Configure Python environment

Sys.setenv(RETICULATE_PYTHON = “C:/Users/16825/anaconda3/python.exe”) # Adjust path if needed py_config() # Verify Python configuration

Python code as a string for SQL query generation

python_code <- ” import os from langchain_community.utilities import SQLDatabase from langchain_openai import ChatOpenAI from dotenv import load_dotenv

Load environment variables

load_dotenv()

Set API key and initialize LLM

api_key = ‘sk-proj-RdmMzr9ThnDc81j7iDoLPnrMRbRIKz8WWiyaV–GlhNB8ZBgJqJp9zPyp8CNFXYmGoGFWxj_RYT3BlbkFJyYl1i3V6IuDAKcH–LJ8wz3Ga4kgajJPcbSm5oj5Xy4dHHy5zaPdLaZFj0WekpCfjxY5nm3G0A’ # Replace with your actual OpenAI API key llm = ChatOpenAI(model=‘gpt-4o’, api_key=api_key)

Define the function to generate SQL

def generate_sql(question): try: schema = ’’’ Table: Bookings Columns: BookingID (INT, PK), BookingDate (TEXT), BookingStatus (TEXT), CustomerID (INT), DeliveryRequired (INT), EndDate (TEXT), StartDate (TEXT), TotalBookingCost (DOUBLE), VehicleID (INT)

    Table: Customers
    Columns: CustomerID (INT, PK), Address (TEXT), CompanyName (TEXT), Email (TEXT), FirstName (TEXT), LastName (TEXT), Phone (TEXT), RegistrationDate (TEXT), UserType (TEXT)

    Table: Payments
    Columns: PaymentID (INT, PK), BookingID (INT), PaymentAmount (DOUBLE), PaymentDate (TEXT), PaymentMethod (TEXT), PaymentStatus (TEXT)

    Table: Vehicles
    Columns: VehicleID (INT, PK), AvailabilityStatus (TEXT), Description (TEXT), LastServiceDate (TEXT), Location (TEXT), Manufacturer (TEXT), Model (TEXT), RentalRate (DOUBLE), VehicleType (TEXT)
    '''
    template = f'Based on the schema: {schema}, generate a SQL query for the question: {question}'
    response = llm.invoke(template)  # Call invoke method
    raw_query = response.content  # Extract the full response text

    # Extract only the SQL query (remove explanatory text and code block markers)
    sql_query = raw_query.split('```sql')[1].split('```')[0].strip()  # Extract SQL from code block
    
    # Modify the SQL query to use LIKE operator for Location search
    sql_query = sql_query.replace('Location =', 'Location LIKE')
    sql_query = sql_query.replace(\"'Dallas'\", \"'%Dallas%'\")  # Add wildcards around Dallas
    
    # Remove extra LIKE operator
    sql_query = sql_query.replace('LIKE LIKE', 'LIKE')
    
    return sql_query
except Exception as e:
    print(f'Error in generate_sql: {e}')
    raise

”

Load the Python code into R

tryCatch({ py_run_string(python_code) print(“Python functions loaded successfully.”) }, error = function(e) { stop(“Python initialization failed:”, e$message) })

Database connection setup for both the “Book Your Vehicle” tab and “Management Dashboard”

db_connect <- function() { dbConnect(RMySQL::MySQL(), dbname = “Golden_Peace”, host = “itom6265-db.c1e6oi6e06on.us-east-2.rds.amazonaws.com”, port = 3306, user = “root”, password = “mysql_local_pass”) }

Query to retrieve vehicles data for the “Book Your Vehicle” tab

vehicles_query <- “SELECT VehicleID, Model, Manufacturer, RentalRate, AvailabilityStatus, Location FROM Vehicles” con <- db_connect() vehicles_data <- dbGetQuery(con, vehicles_query) dbDisconnect(con)

Extract city and state from Location for the “Book Your Vehicle” tab

vehicles_data$CityState <- sub(".*?, (.*?, .*?)$“,”\1”, vehicles_data$Location)

Define UI

ui <- dashboardPage( dashboardHeader(title = “Golden Peace”), dashboardSidebar( sidebarMenu( # Book Your Vehicle Tab menuItem(“Book Your Vehicle”, tabName = “book_vehicle”, icon = icon(“car”)),

  # Manage Booking Tab
  menuItem("Manage Booking", tabName = "manage_booking", icon = icon("edit")),
  
  # Management Dashboard Tab
  menuItem("Management Dashboard", tabName = "management_dashboard", icon = icon("cogs")),
  
  #SQL Query Generator Tab
  menuItem("SQL Query Generator", tabName = "sql_query", icon = icon("search")),
  
  # Add in Analytics tab
  menuItem("Analytics", tabName = "analytics", icon = icon("chart-bar"))
)

), dashboardBody( useShinyjs(), # Initialize shinyjs tabItems( # Book Your Vehicle Tab tabItem(tabName = “book_vehicle”, fluidRow( box(title = “Book Your Vehicle”, status = “primary”, solidHeader = TRUE, width = 12, selectInput(“vehicle_select”, “Select Vehicle”, choices = vehicles_data$Model), dateRangeInput(“date_input”, “Select Dates:”, start = Sys.Date(), end = Sys.Date() + 30), actionButton(“confirm_button”, “Confirm”, class = “btn-soft”), textOutput(“booking_step”) ) ), uiOutput(“customer_info_section”) # Dynamically show the customer info section ),

  # Manage Booking Tab
  tabItem(tabName = "manage_booking",
          fluidRow(
            box(title = "Manage Booking", status = "primary", solidHeader = TRUE, width = 12,
                textInput("booking_select", "Enter Booking ID", value = ""),
                actionButton("modify_booking_button", "Modify Booking", class = "btn-soft"),
                actionButton("cancel_booking_button", "Cancel Booking", class = "btn-soft"),
                uiOutput("update_booking_form")  # Form for updating booking
            )
          )
  ),
  
  # Management Dashboard Tab
  tabItem(tabName = "management_dashboard",
          fluidRow(
            box(title = "Admin Login", status = "primary", solidHeader = TRUE, width = 12,
                textInput("admin_username", "Username", value = ""),
                passwordInput("admin_password", "Password"),
                actionButton("login_button", "Login", class = "btn-soft"),
                textOutput("login_message")
            )
          ),
          fluidRow(
            # This section will only be visible after login
            uiOutput("management_section")
          )
  ),
  
  # SQL Query Generator Tab
  tabItem(tabName = "sql_query",
          fluidRow(
            box(title = "AI-Powered SQL Query Generator", status = "primary", solidHeader = TRUE, width = 12,
                textInput("question", "Enter your question:", placeholder = "e.g., How many Vehicles are there?"),
                actionButton("generate_sql", "Generate SQL and Run"),
                tags$h4("Generated SQL Query"),
                verbatimTextOutput("generated_sql"),
                tags$h4("Query Results"),
                DT::dataTableOutput("query_results")
            )
          )),
  
  #Analytics tab
  tabItem(tabName = "analytics",
          fluidRow(
            box(id = "login_box", title = "Admin Login for Analytics", status = "primary", solidHeader = TRUE, width = 6,
                textInput("admin_username_analytics", "Username", value = ""),
                passwordInput("admin_password_analytics", "Password"),
                actionButton("login_button_analytics", "Login", class = "btn-soft"),
                textOutput("login_message_analytics")
            ),
            box(id = "analytics_plots", style = "display: none;",  # Initially hidden
                box(title = "Booking Trends", status = "primary", solidHeader = TRUE, width = 6, plotOutput("plot_booking_trends", height = "300px")),
                box(title = "Customer Segmentation", status = "primary", solidHeader = TRUE, width = 6, plotOutput("plot_customer_segment", height = "300px"))
            )
          )
  )
)))

Server logic

server <- function(input, output, session) {

# Store selected vehicle details in reactiveValues selected_data <- reactiveValues(vehicle_id = NULL, rental_rate = NULL, selected_dates = NULL, vehicle_name = NULL)

# Admin login flag logged_in <- reactiveVal(FALSE)

# Book Your Vehicle Tab - When “Confirm” is clicked, store vehicle details observeEvent(input$confirm_button, { selected_vehicle <- input$vehicle_select selected_dates <- input$date_input

# Retrieve the selected vehicle details from vehicles_data based on Model name
selected_vehicle_data <- vehicles_data[vehicles_data$Model == selected_vehicle, ]
selected_data$vehicle_id <- selected_vehicle_data$VehicleID
selected_data$rental_rate <- selected_vehicle_data$RentalRate
selected_data$vehicle_name <- selected_vehicle_data$Model
selected_data$selected_dates <- selected_dates

# Calculate total booking cost (just an example, can be customized)
total_booking_cost <- selected_data$rental_rate * as.numeric(diff(selected_dates))  # Simple calculation

output$booking_step <- renderText({
  paste("Vehicle:", selected_data$vehicle_name, "has been booked.")
})

# Show customer information section dynamically
output$customer_info_section <- renderUI({
  fluidRow(
    box(title = "Enter Customer Information", status = "primary", solidHeader = TRUE, width = 12,
        textInput("first_name", "First Name"),
        textInput("last_name", "Last Name"),
        textInput("email", "Email Address"),
        textInput("phone", "Phone Number"),
        textInput("address", "Address"),
        selectInput("user_type", "User Type", choices = c("Corporate", "Individual")),
        actionButton("info_button", "Proceed to Payment", class = "btn-soft"),
        textOutput("info_confirmation")
    )
  )
})

})

# Insert customer info and booking details into database when “Proceed to Payment” is clicked observeEvent(input$info_button, { # Retrieve customer information first_name <- input$first_name last_name <- input$last_name email <- input$email phone <- input$phone address <- input$address user_type <- input$user_type selected_dates <- selected_data$selected_dates vehicle_id <- selected_data$vehicle_id rental_rate <- selected_data$rental_rate

# Connect to the database
con <- db_connect()

# Check if customer already exists based on first name, last name, and address
existing_customer_query <- sprintf("SELECT CustomerID FROM Customers WHERE FirstName = '%s' AND LastName = '%s' AND Address = '%s'",
                                   first_name, last_name, address)
existing_customer <- dbGetQuery(con, existing_customer_query)

# If the customer exists, use the existing CustomerID
if (nrow(existing_customer) > 0) {
  customer_id <- existing_customer$CustomerID[1]
  message("Existing customer found. Using CustomerID:", customer_id)
} else {
  # Get the latest CustomerID from the database and increment by 1
  latest_customer_id_query <- "SELECT MAX(CustomerID) AS max_id FROM Customers"
  latest_customer_id <- dbGetQuery(con, latest_customer_id_query)$max_id
  customer_id <- ifelse(is.na(latest_customer_id), 1, latest_customer_id + 1)  # Start at 1 if no customers exist
  
  # Insert the new customer without setting CustomerID (it will be set manually)
  insert_customer_query <- sprintf("INSERT INTO Customers (CustomerID, FirstName, LastName, Email, Phone, Address, UserType, RegistrationDate) 
                                    VALUES (%d, '%s', '%s', '%s', '%s', '%s', '%s', NOW())", 
                                   customer_id, first_name, last_name, email, phone, address, user_type)
  dbExecute(con, insert_customer_query)
  
  message("New customer added. CustomerID:", customer_id)
}

# Calculate total booking cost
total_booking_cost <- rental_rate * as.numeric(diff(selected_dates))  # Simple calculation for now

# Insert booking details into Bookings table
insert_booking_query <- sprintf("INSERT INTO Bookings (CustomerID, VehicleID, StartDate, EndDate, TotalBookingCost, 
                                BookingStatus, DeliveryRequired, BookingDate) 
                                VALUES (%d, %d, '%s', '%s', %.2f, 'Pending', 0, NOW())", 
                                customer_id, vehicle_id, selected_dates[1], selected_dates[2], total_booking_cost)
dbExecute(con, insert_booking_query)

# Get the last inserted BookingID
booking_id <- dbGetQuery(con, "SELECT LAST_INSERT_ID() AS BookingID")$BookingID

# Display confirmation message
output$info_confirmation <- renderText({
  paste("Thank you", first_name, last_name, ". Your booking is confirmed with Booking ID:", booking_id)
})

# Close the database connection
dbDisconnect(con)

})

# Connect to the database conn <- tryCatch({ dbConnect( RMySQL::MySQL(), host = “itom6265-db.c1e6oi6e06on.us-east-2.rds.amazonaws.com”, port = 3306, dbname = “Golden_Peace”, # Use the correct database name ‘ap’ user = “root”, password = “mysql_local_pass” ) }, error = function(e) { stop(“Database connection failed:”, e$message) })

observeEvent(input$generate_sql, { tryCatch({ # Fetch user question question <- input$question print(paste(“User Question:”, question))

  # Call the Python function to generate SQL
  py_run_string(paste0("query = generate_sql('", question, "')"))
  
  # Retrieve the generated SQL from Python
  generated_sql <- py$`query`
  print(paste("Generated SQL Query:", generated_sql))
  
  # Execute the SQL query in the database
  data <- dbGetQuery(conn, generated_sql)
  print("Query Results Retrieved.")
  
  # Display SQL query and query results in the app
  output$generated_sql <- renderText({ generated_sql })
  output$query_results <- DT::renderDataTable(
    DT::datatable(data, options = list(pageLength = 5, scrollX = TRUE))
  )
}, error = function(e) {
  # Handle errors gracefully
  print("Error occurred:")
  print(e$message)
  output$generated_sql <- renderText("Error generating SQL or fetching results.")
  output$query_results <- renderText("No data to display.")
})

})

# Manage Booking Tab - Connect to MySQL Database db_connect <- function() { dbConnect(RMySQL::MySQL(), dbname = “Golden_Peace”, host = “itom6265-db.c1e6oi6e06on.us-east-2.rds.amazonaws.com”, port = 3307, user = “root”, password = “mysql_local_pass”) }

# Fetch bookings for the customer in the Manage Booking Tab observe({ customer_id <- 1 # Placeholder: Replace with actual customer ID after login

con <- db_connect()

get_bookings_query <- sprintf("SELECT b.BookingID, v.Model, b.StartDate, b.EndDate, b.TotalBookingCost, b.BookingStatus 
                             FROM Bookings b 
                             JOIN Vehicles v ON b.VehicleID = v.VehicleID 
                             WHERE b.CustomerID = %d", as.numeric(customer_id))

bookings <- dbGetQuery(con, get_bookings_query)
dbDisconnect(con)

# You can add logic here to dynamically populate the booking selection in the textInput

})

# When user types a booking ID, fetch its details observeEvent(input$booking_select, { booking_id <- as.numeric(input$booking_select) # Convert input to numeric

# Only proceed if the input is a valid number
if (!is.na(booking_id) && booking_id > 0) {
  con <- db_connect()
  
  # Query to get booking details based on the entered BookingID
  booking_details_query <- sprintf("SELECT b.BookingID, b.StartDate, b.EndDate, b.TotalBookingCost, b.BookingStatus 
                                   FROM Bookings b 
                                   WHERE b.BookingID = %d", booking_id)
  
  booking_details <- dbGetQuery(con, booking_details_query)
  dbDisconnect(con)
  
  # If booking exists, render the update form
  if (nrow(booking_details) > 0) {
    output$update_booking_form <- renderUI({
      fluidRow(
        box(title = "Update Booking", status = "primary", solidHeader = TRUE, width = 12,
            dateRangeInput("update_date_input", "Select New Dates:", start = booking_details$StartDate, end = booking_details$EndDate),
            numericInput("update_total_cost", "Total Booking Cost", value = booking_details$TotalBookingCost),
            actionButton("update_booking_button", "Update Booking", class = "btn-soft"),
            textOutput("update_confirmation")
        )
      )
    })
  } else {
    output$update_booking_form <- renderUI({
      fluidRow(
        box(title = "Booking Not Found", status = "danger", solidHeader = TRUE, width = 12,
            p("No booking found for the entered Booking ID.")
        )
      )
    })
  }
}

})

# Modify the booking in the database when “Update Booking” button is pressed in the Manage Booking Tab observeEvent(input$update_booking_button, { booking_id <- as.numeric(input$booking_select) # Ensure BookingID is numeric updated_dates <- input$update_date_input updated_cost <- as.numeric(input$update_total_cost) # Ensure cost is numeric

if (!is.na(booking_id) && booking_id > 0 && !is.na(updated_cost)) {
  con <- db_connect()
  
  update_booking_query <- sprintf("UPDATE Bookings 
                                  SET StartDate = '%s', EndDate = '%s', TotalBookingCost = %.2f 
                                  WHERE BookingID = %d", 
                                  updated_dates[1], updated_dates[2], updated_cost, booking_id)
  
  dbExecute(con, update_booking_query)
  dbDisconnect(con)
  
  output$update_confirmation <- renderText({
    paste("Booking ID", booking_id, "has been updated successfully!")
  })
} else {
  output$update_confirmation <- renderText({
    "Error: Invalid data entered. Please check your input and try again."
  })
}

})

# Cancel the booking in the database when “Cancel Booking” button is pressed in the Manage Booking Tab observeEvent(input$cancel_booking_button, { # Show a JavaScript confirmation dialog when the cancel button is clicked runjs(’ var proceed = confirm(“Are you sure you want to cancel this booking?”); if (proceed) { Shiny.setInputValue(“confirmed_cancel”, true); } else { Shiny.setInputValue(“confirmed_cancel”, false); } ’) })

# Proceed with cancellation if confirmed observeEvent(input$confirmed_cancel, { if (input$confirmed_cancel) { booking_id <- as.numeric(input$booking_select) # Ensure BookingID is numeric

  if (!is.na(booking_id) && booking_id > 0) {
    con <- db_connect()
    
    cancel_booking_query <- sprintf("UPDATE Bookings 
                                    SET BookingStatus = 'Cancelled' 
                                    WHERE BookingID = %d", booking_id)
    
    dbExecute(con, cancel_booking_query)
    dbDisconnect(con)
    
    output$update_confirmation <- renderText({
      paste("Booking ID", booking_id, "has been cancelled successfully!")
    })
  } else {
    output$update_confirmation <- renderText({
      "Error: Invalid booking selected. Please try again."
    })
  }
} else {
  output$update_confirmation <- renderText({
    "Booking cancellation has been cancelled."
  })
}

})

# Management Dashboard Tab - Admin login functionality observeEvent(input$login_button, { # Simple username and password check (can be expanded for a real application) if (input$admin_username == “admin” && input$admin_password == "admin123") { logged_in(TRUE) output$login_message <- renderText({ “Login successful! You can now retire vehicles.” }) } else { logged_in(FALSE) output$login_message <- renderText({ “Invalid username or password. Please try again.” }) } })

# Show Management Dashboard only if logged in output$management_section <- renderUI({ if (logged_in()) { fluidRow( box(title = "Manage Vehicles", status = "primary", solidHeader = TRUE, width = 12, selectInput("delete_vehicle", "Select Vehicle to Retire", choices = vehicles_data$Model), actionButton(“delete_button”, “Delete Vehicle”, class = “btn-soft”) ) ) } else { NULL } })

# Delete vehicle from database when “Delete Vehicle” button is clicked observeEvent(input$delete_button, { selected_vehicle_to_delete <- input$delete_vehicle

# Connect to the database
con <- db_connect()

# Delete the selected vehicle from the database
delete_vehicle_query <- sprintf("DELETE FROM Vehicles WHERE Model = '%s'", selected_vehicle_to_delete)
dbExecute(con, delete_vehicle_query)

# Update the list of vehicles in the selection dropdown
vehicles_data <<- dbGetQuery(con, vehicles_query)

# Provide feedback to the admin
shinyjs::alert(sprintf("The vehicle '%s' has been deleted successfully!", selected_vehicle_to_delete))

dbDisconnect(con)

})

#analytics tab # Admin login flag for analytics logged_in <- reactiveVal(FALSE)

observeEvent(input$login_button_analytics, { if (input$admin_username_analytics == “admin” && input$admin_password_analytics == "admin123") { logged_in(TRUE) output$login_message_analytics <- renderText(“Login successful! Displaying analytics.”) # After successful login, hide the login box and show plots shinyjs::hide(“login_box”) shinyjs::show(“analytics_plots”) } else { logged_in(FALSE) output$login_message_analytics <- renderText(“Invalid username or password. Please try again.”) } })

output$plot_booking_trends <- renderPlot({ if (logged_in()) { con <- db_connect() data <- dbGetQuery(con, “SELECT DATE(BookingDate) AS Date, COUNT(*) AS Count FROM Bookings GROUP BY DATE(BookingDate)“) dbDisconnect(con) if (nrow(data) > 0) { ggplot(data, aes(x = as.Date(Date), y = Count)) + geom_line() + labs(title =”Booking Trends Over Time”, x = “Date”, y = “Bookings”) } else { print(“No data to display for bookings.”) # Debug message } } })

output$plot_customer_segment <- renderPlot({ if (logged_in()) { con <- db_connect() data <- dbGetQuery(con, “SELECT UserType, COUNT(*) AS Count FROM Customers GROUP BY UserType”) dbDisconnect(con) if (nrow(data) > 0) { ggplot(data, aes(x = UserType, y = Count, fill = UserType)) + geom_bar(stat = “identity”) + labs(title = “Customer Segmentation”, x = “User Type”, y = “Count”) } else { print(“No data to display for customer segmentation.”) # Debug message } } })

}

Run the app

shinyApp(ui, server)