Exemplo n.º 1
0
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