def safety_stock_controller(uri: str, sku_id: str = None, direction: str = None): """ Retrieves safety stock. Args: uri (str): Database connection string. sku_id (str): SKU unique identification. direction (str): Indication of sort direction. Returns: """ meta = MetaData() connection = engine(uri) inventory_analysis = Table('inventory_analysis', meta, autoload=True, autoload_with=connection) msk = Table('master_sku_list', meta, autoload=True, autoload_with=connection) j = join(inventory_analysis, msk, msk.columns.id == inventory_analysis.columns.sku_id) if direction == 'smallest': sku_classification = select([func.min(inventory_analysis.columns.safety_stock), msk.columns.sku_id]).select_from(j) rp = connection.execute(sku_classification) else: sku_classification = select([func.max(inventory_analysis.columns.safety_stock), msk.columns.sku_id]).select_from(j) rp = connection.execute(sku_classification) safety_stock = '' for i in rp: safety_stock = i[0] sku_identification = i[1] return safety_stock, sku_identification
def reorder_level_controller(uri: str, sku_id: str = None, direction: str = None): """ Retrieves reorder level. Args: uri: sku_id: direction: Returns: """ meta = MetaData() connection = engine(uri) inventory_analysis = Table('inventory_analysis', meta, autoload=True, autoload_with=connection) msk = Table('master_sku_list', meta, autoload=True, autoload_with=connection) j = join(inventory_analysis, msk, msk.columns.id == inventory_analysis.columns.sku_id) if direction == 'smallest': sku_classification = select([func.min(inventory_analysis.columns.reorder_level), msk.columns.sku_id]).select_from(j) rp = connection.execute(sku_classification) else: sku_classification = select([func.max(inventory_analysis.columns.reorder_level), msk.columns.sku_id]).select_from(j) rp = connection.execute(sku_classification) reorder_level = '' sku_identification = '' for i in rp: reorder_level = i[0] sku_identification = i[1] return reorder_level, sku_identification
def classification_controller(uri: str, sku_id: str = None) -> tuple: """ Retrieves inventory classification for SKU. Args: uri (str): Database connection string. sku_id (str): SKU unique identification. Returns: tuple: Result. """ meta = MetaData() connection = engine(uri) inventory_analysis = Table('inventory_analysis', meta, autoload=True, autoload_with=connection) msk = Table('master_sku_list', meta, autoload=True, autoload_with=connection) j = join(inventory_analysis, msk, msk.columns.id == inventory_analysis.columns.sku_id) sku_classification = select([ inventory_analysis.columns.abc_xyz_classification ]).select_from(j).where(msk.columns.sku_id == sku_id) rp = connection.execute(sku_classification) classification = '' for i in rp: classification = i['abc_xyz_classification'] return classification
def currency_symbol_controller(uri: str) -> str: """ Retrieves currency code from analysis database. Args: uri (str): Database connection string. Returns: tuple: Result. """ meta = MetaData() connection = engine(uri) inventory_analysis = Table('inventory_analysis', meta, autoload=True, autoload_with=connection) transaction_log = Table('transaction_log', meta, autoload=True, autoload_with=connection) most_recent_transaction = select([transaction_log.columns.id, func.max(transaction_log.columns.date)]).limit(1) rp = connection.execute(most_recent_transaction) transaction_id = 0 for i in rp: transaction_id = i['id'] symbol_id = select([inventory_analysis.columns.currency_id]).where( inventory_analysis.columns.transaction_log_id == transaction_id).limit(1) rp.close() rp = connection.execute(symbol_id) currency_symbol_id = 0 for i in rp: currency_symbol_id = i['currency_id'] rp.close() currency = Table('currency', meta, autoload=True, autoload_with=connection) currency_symbol = select([currency.columns.currency_code]).where(currency.columns.id == currency_symbol_id) rp = connection.execute(currency_symbol) currency_code = 0 for i in rp: currency_code = i['currency_code'] rp.close() return currency_code
def excess_controller(uri: str, direction: str = None, sku_id: str = None) -> tuple: """ Retrieves the excess SKUs in the inventory analysis table. Args: uri (str): Database connection string. direction (str): Indication of sort direction. sku_id (str): SKU unique id. Returns: tuple: Result. """ meta = MetaData() connection = engine(uri) try: inventory_analysis = Table('inventory_analysis', meta, autoload=True, autoload_with=connection) if direction == 'biggest': skus = select([ inventory_analysis.columns.sku_id, inventory_analysis.columns.excess_cost, func.min(inventory_analysis.columns.excess_rank) ]) else: skus = select([ inventory_analysis.columns.sku_id, inventory_analysis.columns.excess_cost, func.max(inventory_analysis.columns.excess_rank) ]) # for i in inventory_analysis.columns: # print(i) rp = connection.execute(skus) result = [] sku_id = "" for i in rp: sku_id = str(i['sku_id']) result.append((i['sku_id'], i['excess_cost'])) rp.close() # print(sku_id) msk_table = Table('master_sku_list', meta, autoload=True, autoload_with=connection) skus = select([msk_table.columns.id, msk_table.columns.sku_id ]).where(msk_table.columns.id == sku_id) rp = connection.execute(skus) for i in rp: result.append(i['sku_id']) except OSError as e: print("Retrieving {} excess failed. {}".format(direction, e)) else: rp.close() return tuple(result)
def average_orders_controller(uri: str, direction: str = None, sku_id: str = None) -> tuple: """ Retrieves SKU's average orders. Args: uri (str): Database connection string. direction (str): Indication of sort direction. sku_id (str): SKU unique id. Returns: tuple: Result. """ rp = None try: meta = MetaData() connection = engine(uri) inventory_analysis = Table('inventory_analysis', meta, autoload=True, autoload_with=connection) if direction == 'smallest': skus = select([ inventory_analysis.columns.sku_id, inventory_analysis.columns.average_orders, func.min(inventory_analysis.columns.average_orders) ]) else: skus = select([ inventory_analysis.columns.sku_id, inventory_analysis.columns.average_orders, func.max(inventory_analysis.columns.average_orders) ]) rp = connection.execute(skus) result = [] sku_id = "" for i in rp: sku_id = str(i['sku_id']) result.append((i['sku_id'], i['average_orders'])) rp.close() # print(sku_id) msk_table = Table('master_sku_list', meta, autoload=True, autoload_with=connection) skus = select([msk_table.columns.id, msk_table.columns.sku_id ]).where(msk_table.columns.id == sku_id) rp = connection.execute(skus) for i in rp: result.append(i['sku_id']) return tuple(result) finally: rp.close()
def safety_stock_controller(uri: str, sku_id: str = None, direction: str = None): """ Retrieves safety stock. Args: uri (str): Database connection string. sku_id (str): SKU unique identification. direction (str): Indication of sort direction. Returns: """ meta = MetaData() connection = engine(uri) inventory_analysis = Table('inventory_analysis', meta, autoload=True, autoload_with=connection) msk = Table('master_sku_list', meta, autoload=True, autoload_with=connection) j = join(inventory_analysis, msk, msk.columns.id == inventory_analysis.columns.sku_id) if direction == 'smallest': sku_classification = select([ func.min(inventory_analysis.columns.safety_stock), msk.columns.sku_id ]).select_from(j) rp = connection.execute(sku_classification) else: sku_classification = select([ func.max(inventory_analysis.columns.safety_stock), msk.columns.sku_id ]).select_from(j) rp = connection.execute(sku_classification) safety_stock = '' for i in rp: safety_stock = i[0] sku_identification = i[1] return safety_stock, sku_identification
def reorder_level_controller(uri: str, sku_id: str = None, direction: str = None): """ Retrieves reorder level. Args: uri: sku_id: direction: Returns: """ meta = MetaData() connection = engine(uri) inventory_analysis = Table('inventory_analysis', meta, autoload=True, autoload_with=connection) msk = Table('master_sku_list', meta, autoload=True, autoload_with=connection) j = join(inventory_analysis, msk, msk.columns.id == inventory_analysis.columns.sku_id) if direction == 'smallest': sku_classification = select([ func.min(inventory_analysis.columns.reorder_level), msk.columns.sku_id ]).select_from(j) rp = connection.execute(sku_classification) else: sku_classification = select([ func.max(inventory_analysis.columns.reorder_level), msk.columns.sku_id ]).select_from(j) rp = connection.execute(sku_classification) reorder_level = '' sku_identification = '' for i in rp: reorder_level = i[0] sku_identification = i[1] return reorder_level, sku_identification
def currency_symbol_controller(uri: str) -> str: """ Retrieves currency code from analysis database. Args: uri (str): Database connection string. Returns: tuple: Result. """ meta = MetaData() connection = engine(uri) inventory_analysis = Table('inventory_analysis', meta, autoload=True, autoload_with=connection) transaction_log = Table('transaction_log', meta, autoload=True, autoload_with=connection) most_recent_transaction = select( [transaction_log.columns.id, func.max(transaction_log.columns.date)]).limit(1) rp = connection.execute(most_recent_transaction) transaction_id = 0 for i in rp: transaction_id = i['id'] symbol_id = select([inventory_analysis.columns.currency_id ]).where(inventory_analysis.columns.transaction_log_id == transaction_id).limit(1) rp.close() rp = connection.execute(symbol_id) currency_symbol_id = 0 for i in rp: currency_symbol_id = i['currency_id'] rp.close() currency = Table('currency', meta, autoload=True, autoload_with=connection) currency_symbol = select([ currency.columns.currency_code ]).where(currency.columns.id == currency_symbol_id) rp = connection.execute(currency_symbol) currency_code = 0 for i in rp: currency_code = i['currency_code'] rp.close() return currency_code
def average_orders_controller(uri: str, direction: str = None, sku_id: str = None) -> tuple: """ Retrieves SKU's average orders. Args: uri (str): Database connection string. direction (str): Indication of sort direction. sku_id (str): SKU unique id. Returns: tuple: Result. """ rp = None try: meta = MetaData() connection = engine(uri) inventory_analysis = Table('inventory_analysis', meta, autoload=True, autoload_with=connection) if direction == 'smallest': skus = select([inventory_analysis.columns.sku_id, inventory_analysis.columns.average_orders, func.min(inventory_analysis.columns.average_orders)]) else: skus = select([inventory_analysis.columns.sku_id, inventory_analysis.columns.average_orders, func.max(inventory_analysis.columns.average_orders)]) rp = connection.execute(skus) result = [] sku_id = "" for i in rp: sku_id = str(i['sku_id']) result.append((i['sku_id'], i['average_orders'])) rp.close() # print(sku_id) msk_table = Table('master_sku_list', meta, autoload=True, autoload_with=connection) skus = select([msk_table.columns.id, msk_table.columns.sku_id]).where(msk_table.columns.id == sku_id) rp = connection.execute(skus) for i in rp: result.append(i['sku_id']) return tuple(result) finally: rp.close()
def master_sku_list(uri: str) -> list: """ Connects to database to retrieve master sku list. Args: uri (str): Database connection string. Returns: list: All unique SKU identification. """ meta = MetaData() connection = engine(uri) msk_table = Table('master_sku_list', meta, autoload=True, autoload_with=connection) skus = select([msk_table.columns.id, msk_table.columns.sku_id]) rp = connection.execute(skus) result = [] for i in rp: result.append((i['id'], i['sku_id'])) rp.close() return result
def excess_controller(uri: str, direction: str = None, sku_id: str = None) -> tuple: """ Retrieves the excess SKUs in the inventory analysis table. Args: uri (str): Database connection string. direction (str): Indication of sort direction. sku_id (str): SKU unique id. Returns: tuple: Result. """ meta = MetaData() connection = engine(uri) inventory_analysis = Table('inventory_analysis', meta, autoload=True, autoload_with=connection) if direction == 'biggest': skus = select([inventory_analysis.columns.sku_id, inventory_analysis.columns.excess_cost, func.min(inventory_analysis.columns.excess_rank)]) else: skus = select([inventory_analysis.columns.sku_id, inventory_analysis.columns.excess_cost, func.max(inventory_analysis.columns.excess_rank)]) # for i in inventory_analysis.columns: # print(i) rp = connection.execute(skus) result = [] sku_id = "" for i in rp: sku_id = str(i['sku_id']) result.append((i['sku_id'], i['excess_cost'])) rp.close() # print(sku_id) msk_table = Table('master_sku_list', meta, autoload=True, autoload_with=connection) skus = select([msk_table.columns.id, msk_table.columns.sku_id]).where(msk_table.columns.id == sku_id) rp = connection.execute(skus) for i in rp: result.append(i['sku_id']) rp.close() return tuple(result)
def master_sku_list(uri: str) -> list: """ Connects to database to retrieve master sku list. Args: uri (str): Database connection string. Returns: list: All unique SKU identification. """ meta = MetaData() connection = engine(uri) try: log.log( logging.INFO, "Retrieving master sku list from database using uri connection string: \n {}." .format(uri)) msk_table = Table('master_sku_list', meta, autoload=True, autoload_with=connection) skus = select([msk_table.columns.id, msk_table.columns.sku_id]) rp = connection.execute(skus) result = [] for i in rp: result.append((i['id'], i['sku_id'])) log.log( logging.INFO, "Retrieval SUCCESSFUL. Number of records retrieved: {} .".format( len(result))) except OSError as e: print("Master Sku List retrieval failed. {}".format(e)) else: rp.close() return result
def classification_controller(uri: str, sku_id: str = None) -> tuple: """ Retrieves inventory classification for SKU. Args: uri (str): Database connection string. sku_id (str): SKU unique identification. Returns: tuple: Result. """ meta = MetaData() connection = engine(uri) inventory_analysis = Table('inventory_analysis', meta, autoload=True, autoload_with=connection) msk = Table('master_sku_list', meta, autoload=True, autoload_with=connection) j = join(inventory_analysis, msk, msk.columns.id == inventory_analysis.columns.sku_id) sku_classification = select([inventory_analysis.columns.abc_xyz_classification]).select_from(j).where( msk.columns.sku_id == sku_id) rp = connection.execute(sku_classification) classification = '' for i in rp: classification = i['abc_xyz_classification'] return classification