class AppsClient(object):
"""
Client interface to Apps API
"""
def __init__(self, db, check=False):
"""
Instantiates client class.
This class in turn instantiations the Apps API class and passes the db
connection
Parameters:
:param db: a mysql.connector connection
:param check: Boolean indicating whether SQL checks should be used
"""
self.apps = Apps(db, check)
self.db = db
# Helper interfaces
def select(self, where_dict, table_name):
"""
Used to make basic SELECT queries to the database. Interfaces with
get_data_frame method in Apps.py
Parameters:
:param where_dict: dictionary of attributes for the select clause
:param table_name: Name of SQL table to search
Returns:
:return: Pandas data frame or Error
"""
if where_dict is not None:
return self.apps.get_data_frame('*', table_name, where_dict)
else:
return self.apps.get_data_frame('*', table_name)
def zip_is_present(self, zip_code):
"""
Used to check existence of a zip code in the database.
Parameters:
:param zip_code: Zip code of interest
Returns:
:return: Pandas data frame or Error
"""
result = self.apps.get_data_frame('*', 'ZipToCityState',
{'zip': zip_code})
if len(result) == 0:
return False
else:
return True
def insert(self, param_dict, api_info):
"""
Used to make INSERT transactions on the database. Interfaces with the
insert method of the appropriate entity in Apps.py. Since zip is a
foreign key constraint, any zip information is entered first.
Parameters:
:param param_dict: Attribute dictionary containing embedded 'set'
and 'where' dictionaries for formulating SQL statement. For
inserts, only the 'set' dictionary need be used.
:param api_info: The api information (attribute lists) for the
relevant entity
Returns:
:return: Pandas data frame or Error
"""
set_dict = param_dict['set']
zip_result = None
with sql_transaction(self.db):
# If a city and state is present, insert the new zip
if 'zip' in set_dict and 'city' in set_dict and 'state' in set_dict:
zip_dict = {k: set_dict[k] for k in ('city', 'state', 'zip')
if k in set_dict}
zip_result = self.apps.add_zip(zip_dict)
if not isinstance(zip_result, pd.DataFrame):
return zip_result
# Remove extra arguments (city, state)
item_dict = {k: set_dict[k] for k in api_info.attr_names['set']
if k in set_dict}
# select the correct API and submit
result = {
'Hotels': lambda x: self.apps.add_hotel(x),
'Rooms': lambda x: self.apps.add_room(x),
'Staff': lambda x: self.apps.add_staff(x),
'Customers': lambda x: self.apps.add_customer(x),
'Reservations': lambda x: self.apps.add_reservation(x),
'Transactions': lambda x: self.apps.add_transaction(x),
'Serves': lambda x: self.apps.add_serves(x)
}[api_info.table_name](item_dict)
if zip_result is not None:
result = result.merge(zip_result, left_on='zip',
right_on='zip', how='outer')
return result
def update(self, param_dict, api_info):
"""
Used to make UPDATE transactions on the database. Interfaces with the
update method of the appropriate entity in Apps.py. Since zip is a
foreign key constraint, any zip information is entered first.
Parameters:
:param param_dict: Attribute dictionary containing embedded 'set'
and 'where' dictionaries for formulating SQL statement.
:param api_info: The api information (attribute lists) for the
relevant entity
Returns:
:return: Pandas data frame or Error
"""
set_dict = param_dict['set']
where_dict = param_dict['where']
zip_result = None
print set_dict
print where_dict
with sql_transaction(self.db):
# If a city and state is present, insert the new zip
if 'zip' in set_dict and 'city' in set_dict and 'state' in set_dict:
zip_dict = {k: set_dict[k] for k in ('city', 'state', 'zip')
if k in set_dict}
zip_result = self.apps.add_zip(zip_dict)
if not isinstance(zip_result, pd.DataFrame):
return zip_result
# Remove extra arguments (city, state)
item_dict = {k: set_dict[k] for k in api_info.attr_names['set']
if k in set_dict}
where_clause_dict = {k: where_dict[k]
for k in api_info.attr_names['where']
if k in where_dict}
# select the correct API and submit
result = {
'Hotels': lambda x, y: self.apps.update_hotel(x, y),
'Rooms': lambda x, y: self.apps.update_room(x, y),
'Staff': lambda x, y: self.apps.update_staff(x, y),
'Customers': lambda x, y: self.apps.update_customer(x, y),
'Reservations': lambda x, y: self.apps.update_reservation(x, y),
'Transactions': lambda x, y: self.apps.update_transaction(x, y),
'Serves': lambda x, y: self.apps.update_serves(x, y)
}[api_info.table_name](item_dict, where_clause_dict)
if zip_result is not None:
result = result.merge(zip_result, left_on='zip',
right_on='zip', how='outer')
return result
def delete(self, param_dict, api_info):
"""
Used to make DELETE transactions on the database. Interfaces with the
delete method of the appropriate entity in Apps.py. Since zip is a
foreign key constraint, any zip information is entered first.
Parameters:
:param param_dict: Attribute dictionary containing embedded 'set'
and 'where'dictionaries for formulating SQL statement. For deletes,
only the 'where' dictionary need be used.
:param api_info: The api information (attribute lists) for the
relevant entity
Returns:
:return: Pandas data frame or Error
"""
where_dict = param_dict['where']
print where_dict
with sql_transaction(self.db):
# select the correct API and submit
result = {
'Hotels': lambda x: self.apps.delete_hotel(x),
'Rooms': lambda x: self.apps.delete_room(x),
'Staff': lambda x: self.apps.delete_staff(x),
'Customers': lambda x: self.apps.delete_customer(x),
'Reservations': lambda x: self.apps.delete_reservation(x),
'Transactions': lambda x: self.apps.delete_transaction(x),
'Serves': lambda x: self.apps.delete_serves(x)
}[api_info.table_name](where_dict)
return result
def get_report(self, param_dict, api_info):
"""
Used to call pre-formulated SQL queries for generating reports.
Parameters:
:param param_dict: Attribute dictionary containing embedded 'set'
and 'where' dictionaries for formulating SQL statement. For reports
only the 'set' dictionary need be used.
:param api_info: The api information (attribute lists) for the
relevant report
Returns:
:return: Pandas data frame or Error
"""
set_dict = param_dict['set']
arg_list = [set_dict[key] for key in api_info.attr_names['set']]
with sql_transaction(self.db):
result = {
'Generate_bill': lambda x: self.apps.generate_bill(x[0]),
'Occupancy_hotel': lambda x:
self.apps.report_occupancy_by_hotel(x[0]),
'Occupancy_room': lambda x:
self.apps.report_occupancy_by_room_type(x[0]),
'Occupancy_city': lambda x:
self.apps.report_occupancy_by_city(x[0]),
'Occupancy_date': lambda x:
self.apps.report_occupancy_by_date_range(x[0], x[1]),
'List_staff': lambda x:
self.apps.report_staff_by_role(x[0]),
'Customer_inter': lambda x:
self.apps.report_customer_interactions(x[0]),
'Revenue_hotel': lambda x:
self.apps.report_revenue_single_hotel(x[0], x[1], x[2]),
'Revenue_all': lambda x:
self.apps.report_revenue_all_hotels(x[0], x[1])
}[api_info.report_name](arg_list)
return result
def get_report_with_dict(self, param_dict, api_info):
"""
Used to call pre-formulated SQL queries for generating reports. Similar
to previous function except api call uses a dictionary instead of a
list.
Parameters:
:param param_dict: Attribute dictionary containing embedded 'set'
and 'where' dictionaries for formulating SQL statement. For reports
only the 'set' dictionary need be used.
:param api_info: The api information (attribute lists) for the
relevant report
Returns:
:return: Pandas data frame or Error
"""
set_dict = param_dict['set']
print set_dict
with sql_transaction(self.db):
result = {
'Room_avail': lambda x: self.apps.room_availability(x)
}[api_info.report_name](set_dict)
return result