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