def create_management_db():
connection = create_engine(
database_connection_uri(db='management', create=True))
metadata.create_all(connection)
for i in TRANSACTION_TYPES:
ins = transaction_type.insert().values(type=i)
connection.execute(ins)
def create_management_db():
connection = create_engine(database_connection_uri(db='management', create=True))
metadata.create_all(connection)
for i in TRANSACTION_TYPES:
ins = transaction_type.insert().values(
type=i
)
connection.execute(ins)
def select_last_simulation():
app = Flask(__name__)
app.config.from_object(ProdConfig)
db.init_app(app)
app.config['SQLALCHEMY_DATABASE_URI'] = database_connection_uri(retrieve='retrieve')
with app.app_context():
simulation_sub = db.session.query(db.func.max(Simulation.date))
simulation_id = db.session.query(Simulation).filter(Simulation.date == simulation_sub).first()
return db.session.query(SimulationSummary).filter(SimulationSummary.sim_id == simulation_id.id).all()
def update_database(analysis_id) -> bool:
"""Updates csv management database with
Args:
analysis_id (int): The id for the analysis the data file corresponds to as given by the transaction table,
when running an analysis
Returns:
bool: Success or failure for the updating of the csv_management database.
"""
connection = create_engine(database_connection_uri(db='management', retrieve=True))
return post_transaction(analysis_id=analysis_id, connection=connection)
def check_keyword_sku_combo(self, dependency, nn: str, sentence):
len_tags = len(sentence.tags)
new_tags = sentence.tags
antepenultimate_word = new_tags[len_tags - 3]
end_word = new_tags[len_tags - 1]
skus = [sku[1] for sku in self._master_sku_list]
if sentence.tags[len_tags - 1][1] in ('NNP') and end_word[0] in skus:
for word in self.ANALYSIS_KEYWORDS:
if word == 'classification':
result = classification_controller(database_connection_uri(retrieve='retrieve'),sku_id=end_word[0])
response = [
'SKU {} has the classification {}'.format(str(end_word[0]), result)]
return response
def check_keyword_sku_combo(self, dependency, nn: str, sentence):
len_tags = len(sentence.tags)
new_tags = sentence.tags
antepenultimate_word = new_tags[len_tags - 3]
end_word = new_tags[len_tags - 1]
skus = [sku[1] for sku in self._master_sku_list]
if sentence.tags[len_tags - 1][1] in ('NNP') and end_word[0] in skus:
for word in self.ANALYSIS_KEYWORDS:
if word == 'classification':
result = classification_controller(
database_connection_uri(retrieve='retrieve'),
sku_id=end_word[0])
response = [
'SKU {} has the classification {}'.format(
str(end_word[0]), result)
]
return response
def store_simulation(sim_summary: dict):
app = Flask(__name__)
app.config.from_object(ProdConfig)
db.init_app(app)
app.config['SQLALCHEMY_DATABASE_URI'] = database_connection_uri(retrieve='retrieve')
with app.app_context():
simulation = Simulation()
simulation.date = datetime.datetime.now()
db.session.add(simulation)
db.session.commit()
simulation_sub = db.session.query(db.func.max(Simulation.date))
simulation_id = db.session.query(Simulation).filter(Simulation.date == simulation_sub).first()
for sim in sim_summary:
master_sku_id = _retrieve_sku_id(db, sim)
simulation_summary = SimulationSummary()
simulation_summary.sku_id = master_sku_id.id
simulation_summary.sim_id = simulation_id.id
simulation_summary.average_backlog = sim.get('average_backlog', 0)
simulation_summary.variance_opening_stock = sim.get('variance_opening_stock', 0)
simulation_summary.minimum_opening_stock = sim.get('minimum_opening_stock', 0)
simulation_summary.maximum_closing_stock = sim.get('maximum_closing_stock', 0)
simulation_summary.maximum_quantity_sold = sim.get('maximum_quantity_sold', 0)
simulation_summary.maximum_backlog = sim.get('maximum_backlog', 0)
simulation_summary.average_backlog = sim.get('average_backlog', 0)
simulation_summary.minimum_closing_stock = sim.get('minimum_closing_stock', 0)
simulation_summary.minimum_backlog = sim.get('minimum_backlog', 0)
simulation_summary.minimum_quantity_sold = sim.get('minimum_quantity_sold', 0)
simulation_summary.average_quantity_sold = sim.get('average_quantity_sold', 0)
simulation_summary.maximum_opening_stock = sim.get('maximum_opening_stock', 0)
simulation_summary.standard_deviation_quantity_sold = sim.get('standard_deviation_quantity_sold', 0)
simulation_summary.standard_deviation_closing_stock = sim.get('standard_deviation_closing_stock', 0)
simulation_summary.average_closing_stock = sim.get('average_closing_stock', 0)
simulation_summary.standard_deviation_backlog = sim.get('standard_deviation_backlog', 0)
simulation_summary.average_shortage_units = sim.get('average_shortage_units', 0)
db.session.add(simulation_summary)
db.session.commit()
def get_master_sku_list():
msk = master_sku_list(database_connection_uri(retrieve='retrieve'))
return msk
def check_min_max_semantic(self, dependency, jjs: str, sentence):
"""
Args:
dependency:
jjs:
sentence:
Returns:
"""
# probably beter to change this to a tuple of keywords, removes the call to wordnet etc
max = dependency[1][0]
min_w = dependency[1][1]
jj_word = Word(jjs)
max_word = Word(max)
min_word = Word(min_w)
len_tags = len(sentence.tags)
new_tags = sentence.tags
penultimate_word = new_tags[len_tags - 2]
end_word = new_tags[len_tags - 1]
for word in jj_word.synsets:
for set in min_word.synsets:
if word == set:
for keyword in self.ANALYSIS_KEYWORDS:
if keyword == end_word[0].lower() and keyword == 'excess':
result = excess_controller(database_connection_uri(retrieve='retrieve'),
direction='smallest')
response = [
'SKU {} has the smallest excess value at {}{:,.2f}'.format(str(result[1]),
self.currency_symbol,
result[0][1])]
return response
elif keyword == end_word[0].lower() and keyword in ('shortage', 'shortages'):
result = shortage_controller(database_connection_uri(retrieve='retrieve'),
direction='smallest')
response = [
'SKU {} has the smallest shortage value at {}{:,.2f}'.format(str(result[1]),
self.currency_symbol,
result[0][1])]
return response
elif keyword == end_word[0].lower() and keyword == 'revenue':
result = revenue_controller(database_connection_uri(retrieve='retrieve'),
direction='smallest')
response = [
'SKU {} has the lowest revenue value at {}{:,.2f}'.format(str(result[1]),
self.currency_symbol,
result[0][1])]
return response
elif keyword == '{} {}'.format(penultimate_word[0].lower(),
end_word[0].lower()) and keyword == 'inventory turns':
result = inventory_turns_controller(database_connection_uri(retrieve='retrieve'),
direction='smallest')
response = [
'SKU {} has the fewest inventory turns at {}'.format(str(result[1]), result[0][1])]
return response
elif keyword == '{} {}'.format(penultimate_word[0].lower(),
end_word[0].lower()) and keyword in ('average order', 'average orders'):
result = average_orders_controller(database_connection_uri(retrieve='retrieve'),
direction='smallest')
response = [
'SKU {} has the lowest average order at {} units.'.format(str(result[1]),
result[0][1])]
return response
elif keyword == '{} {}'.format(penultimate_word[0].lower(),
end_word[0].lower()) and keyword == 'safety stock':
result = safety_stock_controller(database_connection_uri(retrieve='retrieve'),
direction='smallest')
response = ['SKU {} has the lowest safety stock at {} units.'.format(str(result[1]),
result[0])]
return response
elif keyword == '{} {}'.format(penultimate_word[0].lower(),
end_word[0].lower()) and keyword == 'reorder level':
result = reorder_level_controller(database_connection_uri(retrieve='retrieve'),
direction='smallest')
response = ['SKU {} has the lowest reorder level at {} units.'.format(str(result[1]),
result[0])]
return response
# print(sentence.tags[len_tags - 1][1])
if sentence.tags[len_tags - 1][1] in ('NN') or max.lower() in self._MAX:
for word in self.ANALYSIS_KEYWORDS:
if word == end_word[0] and word == 'excess':
# max excess filters excess rank for lowest rank indicating biggest excess
result = excess_controller(database_connection_uri(retrieve='retrieve'), direction='biggest')
return [
'SKU {} has the largest excess value at {}{:,.2f}'.format(str(result[1]), self.currency_symbol,
result[0][1])]
elif word == end_word[0].lower() and word == 'shortage':
result = shortage_controller(database_connection_uri(retrieve='retrieve'), direction='biggest')
return ['SKU {} has the largest shortage value at {}{:,.2f}'.format(str(result[1]),
self.currency_symbol,
result[0][1])]
elif word == end_word[0].lower() and word == 'revenue':
result = revenue_controller(database_connection_uri(retrieve='retrieve'), direction='biggest')
return [
'SKU {} has the highest revenue value at {}{:,.2f}'.format(str(result[1]), self.currency_symbol,
result[0][1])]
elif word == '{} {}'.format(penultimate_word[0].lower(),
end_word[0].lower()) and word == 'inventory turns':
result = inventory_turns_controller(database_connection_uri(retrieve='retrieve'),
direction='biggest')
response = ['SKU {} has the highest inventory turn rate at {}'.format(str(result[1]), result[0][1])]
return response
elif word == '{} {}'.format(penultimate_word[0].lower(),
end_word[0].lower()) and word in ('average order', 'average orders'):
result = average_orders_controller(database_connection_uri(retrieve='retrieve'),
direction='biggest')
response = ['SKU {} has the largest average order at {} units.'.format(str(result[1]), result[0][1])]
return response
elif word == '{} {}'.format(penultimate_word[0].lower(),
end_word[0].lower()) and word =='safety stock':
result = safety_stock_controller(database_connection_uri(retrieve='retrieve'),
direction='biggest')
response = [
'SKU {} has the largest safety stock at {} units.'.format(str(result[1]), result[0])]
return response
elif word == '{} {}'.format(penultimate_word[0].lower(),
end_word[0].lower()) and word == 'reorder level':
result = reorder_level_controller(database_connection_uri(retrieve='retrieve'),
direction='biggest')
response = [
'SKU {} has the largest reorder level at {} units.'.format(str(result[1]), result[0])]
return response
def currency(self):
return currency_symbol_controller(database_connection_uri(retrieve='retrieve'))
def check_min_max_semantic(self, dependency, jjs: str, sentence):
"""
Args:
dependency:
jjs:
sentence:
Returns:
"""
# probably beter to change this to a tuple of keywords, removes the call to wordnet etc
max = dependency[1][0]
min_w = dependency[1][1]
jj_word = Word(jjs)
max_word = Word(max)
min_word = Word(min_w)
len_tags = len(sentence.tags)
new_tags = sentence.tags
penultimate_word = new_tags[len_tags - 2]
end_word = new_tags[len_tags - 1]
for word in jj_word.synsets:
for set in min_word.synsets:
if word == set:
for keyword in self.ANALYSIS_KEYWORDS:
if keyword == end_word[0].lower(
) and keyword == 'excess':
result = excess_controller(
database_connection_uri(retrieve='retrieve'),
direction='smallest')
response = [
'SKU {} has the smallest excess value at {}{:,.2f}'
.format(str(result[1]), self.currency_symbol,
result[0][1])
]
return response
elif keyword == end_word[0].lower() and keyword in (
'shortage', 'shortages'):
result = shortage_controller(
database_connection_uri(retrieve='retrieve'),
direction='smallest')
response = [
'SKU {} has the smallest shortage value at {}{:,.2f}'
.format(str(result[1]), self.currency_symbol,
result[0][1])
]
return response
elif keyword == end_word[0].lower(
) and keyword == 'revenue':
result = revenue_controller(
database_connection_uri(retrieve='retrieve'),
direction='smallest')
response = [
'SKU {} has the lowest revenue value at {}{:,.2f}'
.format(str(result[1]), self.currency_symbol,
result[0][1])
]
return response
elif keyword == '{} {}'.format(
penultimate_word[0].lower(), end_word[0].lower(
)) and keyword == 'inventory turns':
result = inventory_turns_controller(
database_connection_uri(retrieve='retrieve'),
direction='smallest')
response = [
'SKU {} has the fewest inventory turns at {}'.
format(str(result[1]), result[0][1])
]
return response
elif keyword == '{} {}'.format(
penultimate_word[0].lower(),
end_word[0].lower()) and keyword in (
'average order', 'average orders'):
result = average_orders_controller(
database_connection_uri(retrieve='retrieve'),
direction='smallest')
response = [
'SKU {} has the lowest average order at {} units.'
.format(str(result[1]), result[0][1])
]
return response
elif keyword == '{} {}'.format(
penultimate_word[0].lower(), end_word[0].lower(
)) and keyword == 'safety stock':
result = safety_stock_controller(
database_connection_uri(retrieve='retrieve'),
direction='smallest')
response = [
'SKU {} has the lowest safety stock at {} units.'
.format(str(result[1]), result[0])
]
return response
elif keyword == '{} {}'.format(
penultimate_word[0].lower(), end_word[0].lower(
)) and keyword == 'reorder level':
result = reorder_level_controller(
database_connection_uri(retrieve='retrieve'),
direction='smallest')
response = [
'SKU {} has the lowest reorder level at {} units.'
.format(str(result[1]), result[0])
]
return response
# print(sentence.tags[len_tags - 1][1])
if sentence.tags[len_tags -
1][1] in ('NN') or max.lower() in self._MAX:
for word in self.ANALYSIS_KEYWORDS:
if word == end_word[0] and word == 'excess':
# max excess filters excess rank for lowest rank indicating biggest excess
result = excess_controller(
database_connection_uri(retrieve='retrieve'),
direction='biggest')
return [
'SKU {} has the largest excess value at {}{:,.2f}'.
format(str(result[1]), self.currency_symbol,
result[0][1])
]
elif word == end_word[0].lower() and word == 'shortage':
result = shortage_controller(
database_connection_uri(retrieve='retrieve'),
direction='biggest')
return [
'SKU {} has the largest shortage value at {}{:,.2f}'.
format(str(result[1]), self.currency_symbol,
result[0][1])
]
elif word == end_word[0].lower() and word == 'revenue':
result = revenue_controller(
database_connection_uri(retrieve='retrieve'),
direction='biggest')
return [
'SKU {} has the highest revenue value at {}{:,.2f}'.
format(str(result[1]), self.currency_symbol,
result[0][1])
]
elif word == '{} {}'.format(
penultimate_word[0].lower(),
end_word[0].lower()) and word == 'inventory turns':
result = inventory_turns_controller(
database_connection_uri(retrieve='retrieve'),
direction='biggest')
response = [
'SKU {} has the highest inventory turn rate at {}'.
format(str(result[1]), result[0][1])
]
return response
elif word == '{} {}'.format(
penultimate_word[0].lower(),
end_word[0].lower()) and word in ('average order',
'average orders'):
result = average_orders_controller(
database_connection_uri(retrieve='retrieve'),
direction='biggest')
response = [
'SKU {} has the largest average order at {} units.'.
format(str(result[1]), result[0][1])
]
return response
elif word == '{} {}'.format(
penultimate_word[0].lower(),
end_word[0].lower()) and word == 'safety stock':
result = safety_stock_controller(
database_connection_uri(retrieve='retrieve'),
direction='biggest')
response = [
'SKU {} has the largest safety stock at {} units.'.
format(str(result[1]), result[0])
]
return response
elif word == '{} {}'.format(
penultimate_word[0].lower(),
end_word[0].lower()) and word == 'reorder level':
result = reorder_level_controller(
database_connection_uri(retrieve='retrieve'),
direction='biggest')
response = [
'SKU {} has the largest reorder level at {} units.'.
format(str(result[1]), result[0])
]
return response
def currency(self):
return currency_symbol_controller(
database_connection_uri(retrieve='retrieve'))
def get_master_sku_list():
msk = master_sku_list(database_connection_uri(retrieve='retrieve'))
return msk
def test_controllers(self):
smallest_excess = excess_controller(
database_connection_uri(retrieve='retrieve'), direction='smallest')
largest_excess = excess_controller(
database_connection_uri(retrieve='retrieve'), direction='biggest')
smallest_shortage = shortage_controller(
database_connection_uri(retrieve='retrieve'), direction='smallest')
largest_shortage = shortage_controller(
database_connection_uri(retrieve='retrieve'), direction='biggest')
smallest_revenue = revenue_controller(
database_connection_uri(retrieve='retrieve'), direction='smallest')
largest_revenue = revenue_controller(
database_connection_uri(retrieve='retrieve'), direction='biggest')
smallest_inventory_turns = inventory_turns_controller(
database_connection_uri(retrieve='retrieve'), direction='smallest')
largest_inventory_turns = inventory_turns_controller(
database_connection_uri(retrieve='retrieve'), direction='biggest')
smallest_average_orders = average_orders_controller(
database_connection_uri(retrieve='retrieve'), direction='smallest')
biggest_average_orders = average_orders_controller(
database_connection_uri(retrieve='retrieve'), direction='biggest')
smallest_safety_stock = safety_stock_controller(
database_connection_uri(retrieve='retrieve'), direction='smallest')
biggest_safety_stock = safety_stock_controller(
database_connection_uri(retrieve='retrieve'), direction='biggest`')
smallest_reorder_level = reorder_level_controller(
database_connection_uri(retrieve='retrieve'), direction='smallest')
biggest_reorder_level = reorder_level_controller(
database_connection_uri(retrieve='retrieve'), direction='biggest')
result = [
smallest_average_orders, smallest_excess, largest_excess,
smallest_shortage, largest_shortage, smallest_revenue,
largest_revenue, smallest_inventory_turns, largest_inventory_turns,
biggest_average_orders, smallest_safety_stock,
biggest_safety_stock, smallest_reorder_level, biggest_reorder_level
]
ses_file_regex = re.compile('\w+[-]\d+')
for i in result:
self.assertTrue(ses_file_regex.match(i[1]))
