def buy(dict, id):
# Lock mutex
airplane_lock.acquire()
# Get the number of available seats
available_seats = AirplaneRepository.__availableSeats(id)
# The desired number of seats to buy, comes from the body dictionary
desired_number_of_seats = int(dict['number_of_seats'])
# If we want to buy none seats, then everything is ok
if desired_number_of_seats == 0:
airplane_lock.release()
return True
# If we want to buy more seats then the number that is available, we return an error
if available_seats - desired_number_of_seats < 0:
airplane_lock.release()
return False
sql = "INSERT INTO airplane_ticket (`airplane_id`) VALUES (:airplane_id);"
cursor = DBConnection.cursor()
# Buy each seat
for n in range(desired_number_of_seats):
cursor.execute(sql, {"airplane_id": id})
if cursor.rowcount == 0:
airplane_lock.release()
return False
DBConnection.commit()
# Release mutex
airplane_lock.release()
return True
def delete(id):
# Delete an airplane with ID
sql = "DELETE FROM airplane WHERE id = :id"
cursor = DBConnection.cursor()
cursor.execute(sql, {"id": id})
DBConnection.commit()
# Check for errors
return AirplaneRepository.__checkForErrors(cursor)
def update(dict):
# Update an existing airplane
sql = "UPDATE airplane SET `flight_number` = :flight_number, `origin` = :origin, `destination` = :destination, `seats` = :seats, `price` = :price, `date` = :date WHERE id = :id;"
cursor = DBConnection.cursor()
# The json from body needs to have flight_number, origin, destination, seats, price, date and ID
cursor.execute(sql, dict)
DBConnection.commit()
# Check for errors
return AirplaneRepository.__checkForErrors(cursor)
def insert(dict):
# Insert a new airplane
sql = "INSERT INTO airplane (`flight_number`, `origin`, `destination`, `seats`, `price`, `date`) VALUES (:flight_number, :origin, :destination, :seats, :price, :date);"
cursor = DBConnection.cursor()
# The json from body needs to have flight_number, origin, destination, seats, price and date
cursor.execute(sql, dict)
DBConnection.commit()
# Check for errors
return AirplaneRepository.__checkForErrors(cursor)
def insert(dict):
# Insert a new hotel
sql = "INSERT INTO hotel (`name`, `city`) VALUES (:name, :city);"
cursor = DBConnection.cursor()
# The post body needs to have name and city
cursor.execute(sql, dict)
DBConnection.commit()
# Check for success
return HotelRepository.__checkQuerySuccess(cursor)
def insertRoom(dict):
# Insert an new hotel room
sql = """
INSERT INTO hotel_room (`hotel_id`, `size`, `price`)
VALUES (:hotel_id, :size, :price)
"""
cursor = DBConnection.cursor()
DBConnection.commit()
# The post body needs to have hotel_id, size and price
cursor.execute(sql, dict)
DBConnection.commit()
return HotelRepository.__checkQuerySuccess(cursor)
def getWithFilters(self, query):
# Get a hotel list using filters
sql = """
SELECT DISTINCT hr.id, hr.price, hr.size, h.id
FROM hotel h
JOIN hotel_room hr ON h.id = hr.hotel_id
LEFT JOIN hotel_room_check_in hrc ON hrc.hotel_room_id = hr.id
WHERE hr.price <= :price
AND (hrc.id IS NULL
OR :check_in_date > hrc.check_out_date
OR :check_out_date < hrc.check_in_date)
AND h.city = :city
AND hr.size >= :size;
"""
cursor = DBConnection.cursor()
# Parse the query to be usend in the SQL, possible filters are check_out_date, check_in_date, price, location and room capacity
cursor.execute(
sql, {
"check_out_date": query['check_out_date'][0],
"check_in_date": query['check_in_date'][0],
"price": query['price'][0],
"city": query['location'][0],
"size": query['capacity'][0],
})
# Create an hotel list from cursor
rooms = HotelRepository.__createRoomListFromCursor(cursor)
return rooms
def getAll():
# Get all airplanes from table
sql = "SELECT * FROM airplane;"
cursor = DBConnection.cursor()
cursor.execute(sql)
# Create a list of airplanes from cursor
airplanes = AirplaneRepository.__createAirplaneListFromCursor(cursor)
return airplanes
def __availableSeats(id):
# Get the number of available seats from the database
sql = """
SELECT a.seats - COALESCE(count(at.id), 0) available_seats
FROM airplane a
LEFT JOIN airplane_ticket at ON a.id = at.airplane_id
WHERE a.id = :id;"""
cursor = DBConnection.cursor()
cursor.execute(sql, {"id": id})
data = cursor.fetchone()
return data[0]
def buy(dict, room_id):
# Lock mutex
hotel_lock.acquire()
# Check if this room is available in the requested date
if not HotelRepository.__isRoomAvalible(dict, room_id):
hotel_lock.release()
return False
# Insert new check_in entry
sql = """
INSERT INTO hotel_room_check_in (`hotel_room_id`, `check_in_date`, `check_out_date`)
VALUES (:hotel_room_id, :check_in_date, :check_out_date);
"""
cursor = DBConnection.cursor()
dict['hotel_room_id'] = room_id
# We need to pass in the post body the check_in_date and check_out_date
cursor.execute(sql, dict)
DBConnection.commit()
# Release hotel lock
hotel_lock.release()
# Check errors
return HotelRepository.__checkQuerySuccess(cursor)
def getAll():
# Get all hotels with the count of rooms
sql = """
SELECT h.id, h.name, h.city, coalesce (count(hr.id), 0) AS rooms
FROM hotel h
LEFT JOIN hotel_room hr ON h.id = hr.hotel_id
GROUP BY h.id;
"""
cursor = DBConnection.cursor()
cursor.execute(sql)
# Create a hotel list from cursor
hotels = HotelRepository.__createHotelListFromCursor(cursor)
return hotels
def getWithFilters(query):
# Get airplanes from filters
sql = "SELECT * FROM airplane WHERE origin = :origin AND destination = :destination AND date(`date`) = :date;"
cursor = DBConnection.cursor()
# Parse arguments to query for filters. Can filter by origin, destination and date
cursor.execute(
sql, {
"origin": query['origin'][0],
"destination": query['destination'][0],
"date": query['date'][0]
})
# Create a list of airplanes from cursor
airplanes = AirplaneRepository.__createAirplaneListFromCursor(cursor)
return airplanes
def getById(id):
# Get an airplane with the id :id
sql = "SELECT * FROM airplane WHERE id = :id;"
cursor = DBConnection.cursor()
cursor.execute(sql, {"id": id})
data = cursor.fetchone()
# None airplane found, return error
if data is None:
return None
# Create an airplane from cursor
airplane = AirplaneRepository.__createAirplaneFromData(data)
return airplane
def getById(id):
# Get an specific hotel from id
sql = """
SELECT h.id, h.name, h.city, coalesce (count(hr.id), 0) AS rooms
FROM hotel h
LEFT JOIN hotel_room hr ON h.id = hr.hotel_id
WHERE h.id = :id;
"""
cursor = DBConnection.cursor()
cursor.execute(sql, {"id": id})
data = cursor.fetchone()
# If data is none, then there is an error
if data is None:
return None
# Create an hotel from cursor
hotel = HotelRepository.__createHotelFromData(data)
return hotel
def __isRoomAvalible(dict, room_id):
# Query to check if the room is not check_in already in the requested date
sql = """
SELECT 1
FROM hotel_room hr
JOIN hotel_room_check_in hrc on hr.id = hrc.hotel_room_id
WHERE hr.id = :hotel_room_id AND
(:check_in_date BETWEEN hrc.check_in_date AND hrc.check_out_date OR
:check_out_date BETWEEN hrc.check_in_date AND hrc.check_out_date) OR
(hrc.check_in_date BETWEEN :check_in_date AND :check_out_date OR
hrc.check_out_date BETWEEN :check_in_date AND :check_out_date )
"""
cursor = DBConnection.cursor()
dict['hotel_room_id'] = room_id
cursor.execute(sql, dict)
# Get hotel
data = cursor.fetchone()
# If None returned, then this hotel was not already booked for the desired date
if data is None:
return True
# Otherwise, this room was already booked!
return False
try:
# Call the scrapers
jobforecast = MatchESDandBLSSources()
date = jobforecast.formatDate
jobforecast.runTheESDOccupationProjection()
jobforecast.runTheBLSOccupationTraining()
jobforecast.runTheBLSWages()
# Format Job Data
jobforecast = jobforecast.matchtoTrainingData()
jobforecast['Id'] = jobforecast.index
# MIGRATE DATA INTO DATA FRAME (APPEND NOT REPLACE)
# CONNECT TO DATABASE
connect = DBConnection()
engine = connect.engine()
jobforecast = jobforecast[[
'Id', 'SOC', 'SOC_Name', 'WDA_ID', 'WDA_Name', 'CurrentEmployment',
'CurrentYear', 'ProjectedOpenings', 'ProjectedYear', 'SOC_Level',
'Training_Education', 'Training_WorkExperience',
'Training_OntheJob', 'WDA_SOC', 'Matched_MSA_orWA', 'Wage_Median',
'Wage_90'
]]
results = os.path.join(os.path.sep, dir_path, 'JobForecast.csv')
jobforecast.to_csv(results)
jobforecast.to_sql(name='JobsForecast',
con=engine,
if_exists='replace',
