def extensions(app):
"""
Register 0 or more extensions (mutates the app passed in).
"""
db.init_app(app)
manager.init_app(app)
return None
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 setUp(self):
app = Flask(__name__, instance_relative_config=True)
PWD = os.path.abspath(os.curdir)
i_c = IntegrationConfig
i_c.SQLALCHEMY_DATABASE_URI = 'sqlite:///{}/reporting.db'.format(PWD)
app.config.from_object(i_c)
app.config['DATABASE'] = PWD
app.config['TESTING'] = True
self.app = app.test_client()
self._file = ABS_FILE_PATH['COMPLETE_CSV_SM']
app_settings = {
'file': self._file,
'currency': 'USD',
'database_path': PWD,
}
serialise_config(app_settings, ABS_FILE_PATH_APPLICATION_CONFIG)
with app.app_context():
db.init_app(app)
db.create_all()
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 load(file_path: str, location: str = None):
""" Loads analysis and forecast into local database for reporting suite.
Args:
file_path (str): File path to source file containing data for analysis.
location (str): Location of database to populate.
"""
try:
app = create_app()
db.init_app(app)
if location is not None and os.name in ['posix', 'mac']:
app.config[
'SQLALCHEMY_DATABASE_URI'] = 'sqlite:///{}/reporting.db'.format(
location)
elif location is not None and os.name == 'nt':
app.config[
'SQLALCHEMY_DATABASE_URI'] = 'sqlite:///{}\\reporting.db'.format(
location)
log.log(logging.DEBUG,
'Loading data analysis for reporting suite... \n')
with app.app_context():
db.create_all()
log.log(logging.DEBUG, 'loading currency symbols...\n')
print('loading currency symbols...', end="")
fx = currency_codes()
load_currency(fx, db)
print('[COMPLETED]\n')
config = deserialise_config(ABS_FILE_PATH_APPLICATION_CONFIG)
currency = config.get('currency')
log.log(logging.DEBUG, 'Analysing file: {}...\n'.format(file_path))
print('Analysing file: {}...'.format(file_path), end="")
orders_analysis = model_inventory.analyse(
file_path=file_path,
z_value=Decimal(1.28),
reorder_cost=Decimal(5000),
file_type="csv",
length=12,
currency=currency)
ia = [analysis.orders_summary() for analysis in orders_analysis]
date_now = datetime.datetime.now()
analysis_summary = Inventory(processed_orders=orders_analysis)
print('[COMPLETED]\n')
log.log(logging.DEBUG, 'Calculating Forecasts...\n')
print('Calculating Forecasts...', end="")
cores = int(multiprocessing.cpu_count())
cores -= 1
import multiprocessing as mp
simple_forecast_gen = {}
simple_forecast = {}
with mp.Pool(processes=cores) as pool:
simple_forecast_gen = ({
analysis.sku_id:
pool.apply_async(_analysis_forecast_simple,
args=(analysis, ))
} for analysis in orders_analysis)
for gen in simple_forecast_gen:
simple_forecast.update(gen)
simple_forecast = {
key: value.get()
for key, value in simple_forecast.items()
}
holts_forecast_gen = {
analysis.sku_id: pool.apply_async(_analysis_forecast_holt,
args=(analysis, ))
for analysis in orders_analysis
}
holts_forecast = {
key: holts_forecast_gen[key].get()
for key in holts_forecast_gen
}
# with ProcessPoolExecutor(max_workers=cores) as executor:
# simple_forecast_futures = { analysis.sku_id: executor.submit(_analysis_forecast_simple, analysis) for analysis in orders_analysis}
# simple_forecast_gen = {future: concurrent.futures.as_completed(simple_forecast_futures[future]) for future in simple_forecast_futures}
# simple_forecast = {value: simple_forecast_futures[value].result() for value in simple_forecast_gen}
# holts_forecast_futures = { analysis.sku_id: executor.submit(_analysis_forecast_holt, analysis) for analysis in orders_analysis}
# holts_forecast_gen = { future: concurrent.futures.as_completed(holts_forecast_futures[future]) for future in holts_forecast_futures}
# holts_forecast = {value: holts_forecast_futures[value].result() for value in holts_forecast_gen}
# executor.shutdown(wait=False)
transact = TransactionLog()
transact.date = date_now
db.session.add(transact)
db.session.commit()
transaction_sub = db.session.query(db.func.max(
TransactionLog.date))
transaction_id = db.session.query(TransactionLog).filter(
TransactionLog.date == transaction_sub).first()
load_profile_recommendations(analysed_order=orders_analysis,
forecast=holts_forecast,
transaction_log_id=transaction_id)
# d = _Orchestrate()
# d.update_database(int(transaction_id.id))
forecast_types = ('ses', 'htces')
for f_type in forecast_types:
forecast_type = ForecastType()
forecast_type.type = f_type
db.session.add(forecast_type)
db.session.commit()
ses_id = db.session.query(ForecastType.id).filter(
ForecastType.type == forecast_types[0]).first()
htces_id = db.session.query(ForecastType.id).filter(
ForecastType.type == forecast_types[1]).first()
print('[COMPLETED]\n')
log.log(logging.DEBUG, 'loading database ...\n')
print('loading database ...', end="")
for item in ia:
re = 0
skus_description = [
summarised for summarised in analysis_summary.describe_sku(
item['sku'])
]
denom = db.session.query(Currency.id).filter(
Currency.currency_code == item['currency']).first()
master_sku = MasterSkuList()
master_sku.sku_id = item['sku']
db.session.add(master_sku)
i_up = InventoryAnalysis()
mk = db.session.query(MasterSkuList.id).filter(
MasterSkuList.sku_id == item['sku']).first()
i_up.sku_id = mk.id
tuple_orders = item['orders']
# print(tuple_orders)
i_up.abc_xyz_classification = item['ABC_XYZ_Classification']
i_up.standard_deviation = item['standard_deviation']
i_up.backlog = item['backlog']
i_up.safety_stock = item['safety_stock']
i_up.reorder_level = item['reorder_level']
i_up.economic_order_quantity = item['economic_order_quantity']
i_up.demand_variability = item['demand_variability']
i_up.average_orders = round(float(item['average_orders']))
i_up.shortages = item['shortages']
i_up.excess_stock = item['excess_stock']
i_up.reorder_quantity = item['reorder_quantity']
i_up.economic_order_variable_cost = item[
'economic_order_variable_cost']
i_up.unit_cost = item['unit_cost']
i_up.revenue = item['revenue']
i_up.date = date_now
i_up.safety_stock_rank = skus_description[0][
'safety_stock_rank']
i_up.shortage_rank = skus_description[0]['shortage_rank']
i_up.excess_cost = skus_description[0]['excess_cost']
i_up.percentage_contribution_revenue = skus_description[0][
'percentage_contribution_revenue']
i_up.excess_rank = skus_description[0]['excess_rank']
i_up.retail_price = skus_description[0]['retail_price']
i_up.gross_profit_margin = skus_description[0][
'gross_profit_margin']
i_up.min_order = skus_description[0]['min_order']
i_up.safety_stock_cost = skus_description[0][
'safety_stock_cost']
i_up.revenue_rank = skus_description[0]['revenue_rank']
i_up.markup_percentage = skus_description[0][
'markup_percentage']
i_up.max_order = skus_description[0]['max_order']
i_up.shortage_cost = skus_description[0]['shortage_cost']
i_up.quantity_on_hand = item['quantity_on_hand']
i_up.currency_id = denom.id
i_up.traffic_light = skus_description[0][
'inventory_traffic_light']
i_up.inventory_turns = skus_description[0]['inventory_turns']
i_up.transaction_log_id = transaction_id.id
db.session.add(i_up)
inva = db.session.query(InventoryAnalysis.id).filter(
InventoryAnalysis.sku_id == mk.id).first()
for i, t in enumerate(tuple_orders['demand'], 1):
orders_data = Orders()
# print(r)
orders_data.order_quantity = t
orders_data.rank = i
orders_data.analysis_id = inva.id
db.session.add(orders_data)
# need to select sku id
for i, forecasted_demand in enumerate(simple_forecast, 1):
if forecasted_demand == item['sku']:
forecast_stats = ForecastStatistics()
forecast_stats.analysis_id = inva.id
forecast_stats.mape = simple_forecast.get(
forecasted_demand)['mape']
forecast_stats.forecast_type_id = ses_id.id
forecast_stats.slope = simple_forecast.get(
forecasted_demand)['statistics']['slope']
forecast_stats.p_value = simple_forecast.get(
forecasted_demand)['statistics']['pvalue']
forecast_stats.test_statistic = simple_forecast.get(
forecasted_demand)['statistics']['test_statistic']
forecast_stats.slope_standard_error = simple_forecast.get(
forecasted_demand
)['statistics']['slope_standard_error']
forecast_stats.intercept = simple_forecast.get(
forecasted_demand)['statistics']['intercept']
forecast_stats.standard_residuals = simple_forecast.get(
forecasted_demand)['statistics']['std_residuals']
forecast_stats.trending = simple_forecast.get(
forecasted_demand)['statistics']['trend']
forecast_stats.optimal_alpha = simple_forecast.get(
forecasted_demand)['optimal_alpha']
forecast_stats.optimal_gamma = 0
db.session.add(forecast_stats)
for p in range(
0,
len(
simple_forecast.get(forecasted_demand)
['forecast'])):
forecast_data = Forecast()
forecast_data.forecast_quantity = simple_forecast.get(
forecasted_demand)['forecast'][p]
forecast_data.analysis_id = inva.id
forecast_data.forecast_type_id = ses_id.id
forecast_data.period = p + 1
forecast_data.create_date = date_now
db.session.add(forecast_data)
for q, sesf in enumerate(
simple_forecast.get(forecasted_demand)
['forecast_breakdown']):
forecast_breakdown = ForecastBreakdown()
forecast_breakdown.analysis_id = inva.id
forecast_breakdown.forecast_type_id = ses_id.id
forecast_breakdown.trend = 0
forecast_breakdown.period = sesf['t']
forecast_breakdown.level_estimates = \
sesf['level_estimates']
forecast_breakdown.one_step_forecast = \
sesf['one_step_forecast']
forecast_breakdown.forecast_error = \
sesf['forecast_error']
forecast_breakdown.squared_error = sesf[
'squared_error']
forecast_breakdown.regression = simple_forecast.get(
forecasted_demand)['regression'][q]
db.session.add(forecast_breakdown)
break
for i, holts_forecast_demand in enumerate(holts_forecast, 1):
if holts_forecast_demand == item['sku']:
forecast_stats = ForecastStatistics()
forecast_stats.analysis_id = inva.id
forecast_stats.mape = holts_forecast.get(
holts_forecast_demand)['mape']
forecast_stats.forecast_type_id = htces_id.id
forecast_stats.slope = holts_forecast.get(
holts_forecast_demand)['statistics']['slope']
forecast_stats.p_value = holts_forecast.get(
holts_forecast_demand)['statistics']['pvalue']
forecast_stats.test_statistic = holts_forecast.get(
holts_forecast_demand
)['statistics']['test_statistic']
forecast_stats.slope_standard_error = holts_forecast.get(
holts_forecast_demand
)['statistics']['slope_standard_error']
forecast_stats.intercept = holts_forecast.get(
holts_forecast_demand)['statistics']['intercept']
forecast_stats.standard_residuals = holts_forecast.get(
holts_forecast_demand
)['statistics']['std_residuals']
forecast_stats.trending = holts_forecast.get(
holts_forecast_demand)['statistics']['trend']
forecast_stats.optimal_alpha = holts_forecast.get(
holts_forecast_demand)['optimal_alpha']
forecast_stats.optimal_gamma = holts_forecast.get(
holts_forecast_demand)['optimal_gamma']
db.session.add(forecast_stats)
for p in range(
0,
len(
holts_forecast.get(holts_forecast_demand)
['forecast'])):
forecast_data = Forecast()
forecast_data.forecast_quantity = holts_forecast.get(
holts_forecast_demand)['forecast'][p]
forecast_data.analysis_id = inva.id
forecast_data.forecast_type_id = htces_id.id
forecast_data.period = p + 1
forecast_data.create_date = date_now
db.session.add(forecast_data)
for i, htcesf in enumerate(
holts_forecast.get(holts_forecast_demand)
['forecast_breakdown']):
forecast_breakdown = ForecastBreakdown()
forecast_breakdown.analysis_id = inva.id
forecast_breakdown.forecast_type_id = htces_id.id
forecast_breakdown.trend = htcesf['trend']
forecast_breakdown.period = htcesf['t']
forecast_breakdown.level_estimates = \
htcesf['level_estimates']
forecast_breakdown.one_step_forecast = \
htcesf['one_step_forecast']
forecast_breakdown.forecast_error = \
htcesf['forecast_error']
forecast_breakdown.squared_error = htcesf[
'squared_error']
forecast_breakdown.regression = holts_forecast.get(
holts_forecast_demand)['regression'][i]
db.session.add(forecast_breakdown)
break
db.session.commit()
print('[COMPLETED]\n')
loading = 'Loading recommendations into database... '
print(loading, end="")
load_recommendations(summary=ia,
forecast=holts_forecast,
analysed_order=orders_analysis)
print('[COMPLETED]\n')
log.log(logging.DEBUG, "Analysis ...\n")
print("Analysis ... [COMPLETED]")
except OSError as e:
print(e)
