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
