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 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]))