def setUp(self):
self._states = (
'EXCESS_RANK',
'SHORTAGE_RANK',
'TRAFFIC_LIGHT',
'CLASSIFICATION',
'FORECAST',
'RECOMMENDATION',
'INVENTORY_TURNS',
'START'
)
self.orders_analysis = model_inventory.analyse(file_path=ABS_FILE_PATH['COMPLETE_CSV_SM'],
z_value=Decimal(1.28),
reorder_cost=Decimal(5000),
file_type='csv',
length=12,
currency='USD')
self.forecast = deserialise_config(ABS_FILE_PATH['FORECAST_PICKLE'])
self.recommend = SkuMachine()
self.states = SKUStates(analysed_orders=self.orders_analysis, forecast=self.forecast)
self.recommend.add_state("start", self.states.initialise_machine)
self.recommend.add_state("excess_rank", self.states.excess_rank)
self.recommend.add_state("shortage_rank", self.states.shortage_rank)
self.recommend.add_state("inventory_turns", self.states.inventory_turns)
self.recommend.add_state("classification", self.states.classification)
self.recommend.add_state("traffic_light", self.states.traffic_light)
self.recommend.add_state("forecast", self.states.forecast)
self.recommend.add_state("recommendation", self.recommend, end_state=1)
self.recommend.set_start("start")
def createSupplyChainAnalysis(pdesc):
raw_df = createPdForAnalysis()
orders_df = raw_df
analyse_kv = dict(df=raw_df,
start=1,
interval_length=12,
interval_type='months',
z_value=Decimal(1.28),
reorder_cost=Decimal(400),
retail_price=Decimal(455),
file_type='csv',
currency='USD')
print(raw_df)
analysis_df = analyse(**analyse_kv)
analysis_rev = analysis_df[['sku', 'revenue']]
row_ds = raw_df[raw_df['Sku'] == pdesc].squeeze()
ses = simple_exponential_smoothing_forecast(demand=list(row_ds[1:12]),
alpha=0.5,
forecast_length=6,
initial_period=18)
forecast_breakdown_df = pd.DataFrame(
ses.get('forecast_breakdown', 'UNKNOWN'))
regression = {
'regression': [(ses.get('statistics')['slope'] * i) +
ses.get('statistics')['intercept']
for i in range(1, 12)]
}
print(regression)
forecast_breakdown_df['regression'] = regression.get('regression')
forecast_breakdown_df.plot(x='t',
y=['one_step_forecast', 'demand', 'regression'])
plt.show()
def test_abcxyz_classification(self):
abc = model_inventory.analyse(file_path=ABS_FILE_PATH['COMPLETE_CSV_SM'],
z_value=Decimal(1.28),
reorder_cost=Decimal(5000),
file_type="csv")
for sku in abc:
item = sku.orders_summary()
if item['sku'] == 'KR202-209':
self.assertEqual(item['ABC_XYZ_Classification'], 'BY')
def test_standard_deviation_row_count(self):
d = model_inventory.analyse(file_path=ABS_FILE_PATH['COMPLETE_CSV_SM'],
z_value=Decimal(1.28),
reorder_cost=Decimal(400),
retail_price=Decimal(455),
file_type='csv')
analysed_orders = [demand.orders_summary() for demand in d]
self.assertEqual(len(d), 39)
def test_standard_deviation_row_count(self):
d = model_inventory.analyse(file_path=ABS_FILE_PATH['COMPLETE_CSV_SM'],
z_value=Decimal(1.28),
reorder_cost=Decimal(400),
retail_price=Decimal(455),
file_type='csv')
analysed_orders = [demand.orders_summary() for demand in d]
self.assertEqual(len(d), 39)
def simulation(runs: int = None):
try:
database_path = ''
file_name = ''
sim_results = []
sim_summary_results = []
inventory = []
if runs is not None:
config = deserialise_config(ABS_FILE_PATH_APPLICATION_CONFIG)
database_path = config['database_path']
file_name = config['file']
file_path = database_path.replace(
' ', '') + '/' + (file_name.replace(' ', ''))
analysed_orders = analyse(file_path=str(file_path),
z_value=Decimal(1.28),
reorder_cost=Decimal(5000),
file_type=FileFormats.csv.name,
length=12,
currency='USD')
# run the simulation, populate a database and then retrieve the most current values for the simulation page.
try:
with ThreadPoolExecutor(max_workers=1) as executor:
sim = executor.submit(simulate.run_monte_carlo,
orders_analysis=analysed_orders,
runs=runs,
period_length=12)
sim_results = sim.result()
sim_window = executor.submit(simulate.summarize_window,
simulation_frame=sim_results,
period_length=12)
sim_window_result = sim_window.result()
sim_summary = executor.submit(simulate.summarise_frame,
sim_window_result)
sim_summary_results = sim_summary.result()
store_simulation(sim_summary_results)
inventory = db.session.query(MasterSkuList).all()
except OSError as e:
print(e)
elif runs is None and request.method == 'GET':
try:
sim_summary_results = select_last_simulation()
inventory = db.session.query(MasterSkuList).all()
except AttributeError as e:
pass
return flask.render_template('simulation.html',
db=database_path,
file_name=file_name,
sim=sim_summary_results,
runs=sim_results,
inventory=inventory)
except OSError:
abort(404)
def test_data_frame(self):
raw_df = pd.read_csv(ABS_FILE_PATH['COMPLETE_CSV_SM'])
analysis_df = analyse(df=raw_df,
start=1,
interval_length=12,
interval_type='months')
self.assertIsInstance(
analysis_df[[
'sku', 'quantity_on_hand', 'excess_stock', 'shortages',
'ABC_XYZ_Classification'
]], DataFrame)
def setUp(self):
self.orders_analysis = model_inventory.analyse(
file_path=ABS_FILE_PATH['COMPLETE_CSV_XSM'],
z_value=Decimal(1.28),
reorder_cost=Decimal(5000),
file_type="csv",
length=12,
currency='USD')
self.cores = int(multiprocessing.cpu_count()) - 1
self.batched_analysis = [
i for i in batch(self.orders_analysis, self.cores)
]
def setUp(self):
self._z_value = Decimal(1.28)
self._lead_time = Decimal(4)
self._holding_cost_percentge = Decimal(0.25)
self._retail_price = Decimal(5000)
self._unit_cost = Decimal(55)
self._reorder_cost = Decimal(450)
self._quantity_on_hand = 1000
self._backlog = 0
self._data_set = {
'jan': 25,
'feb': 25,
'mar': 25,
'apr': 25,
'may': 25,
'jun': 25,
'jul': 75,
'aug': 75,
'sep': 75,
'oct': 75,
'nov': 75,
'dec': 75
}
self._orders_analysis = model_inventory.analyse(
file_path=ABS_FILE_PATH['COMPLETE_CSV_SM'],
z_value=Decimal(1.28),
reorder_cost=Decimal(5000),
file_type="csv",
length=12,
currency='USD')
self._uncertain_demand = analyse_uncertain_demand.UncertainDemand(
self._data_set,
sku='Rx493-90',
lead_time=self._lead_time,
reorder_cost=self._reorder_cost,
z_value=self._z_value,
holding_cost=self._holding_cost_percentge,
retail_price=self._retail_price,
unit_cost=self._unit_cost,
currency='USD',
quantity_on_hand=self._quantity_on_hand,
backlog=0)
def step_impl(context):
"""
Args:
context (behave.runner.Context):
"""
analyse_kv = dict(
df=context.response,
start=1,
interval_length=12,
interval_type='months',
z_value=Decimal(1.28),
reorder_cost=Decimal(400),
retail_price=Decimal(455),
file_type='csv',
currency='USD'
)
analysis_df = analyse(**analyse_kv)
context.response = analysis_df
def setUp(self):
self._data_set = {'jan': 25, 'feb': 25, 'mar': 25, 'apr': 25, 'may': 25, 'jun': 25, 'jul': 75,
'aug': 75, 'sep': 75, 'oct': 75, 'nov': 75, 'dec': 75}
self._orders_analysis = model_inventory.analyse(
file_path=ABS_FILE_PATH['COMPLETE_CSV_SM'],
z_value=Decimal(1.28),
reorder_cost=Decimal(5000),
file_type="csv",
length=12)
self._uncertain_demand = analyse_uncertain_demand.UncertainDemand(self._data_set,
sku='Rx493-90',
lead_time=Decimal(4),
reorder_cost=Decimal(450),
z_value=Decimal(1.28),
holding_cost=Decimal(0.25),
retail_price=Decimal(400.58),
unit_cost=Decimal(55),
currency='USD')
def setUp(self):
self.STATES = ('EXCESS_RANK', 'SHORTAGE_RANK', 'TRAFFIC_LIGHT', 'CLASSIFICATION', 'FORECAST', 'RECOMMENDATION',
'INVENTORY_TURNS', 'START')
self.orders_analysis = model_inventory.analyse(file_path=ABS_FILE_PATH['COMPLETE_CSV_SM'],
z_value=Decimal(1.28),
reorder_cost=Decimal(5000),
file_type="csv",
length=12)
self.forecast = deserialise_config(ABS_FILE_PATH['FORECAST_PICKLE'])
self.recommend = SkuMachine()
self.states = SKUStates(analysed_orders=self.orders_analysis, forecast=self.forecast)
self.recommend.add_state("start", self.states.initialise_machine)
self.recommend.add_state("excess_rank", self.states.excess_rank)
self.recommend.add_state("shortage_rank", self.states.shortage_rank)
self.recommend.add_state("inventory_turns", self.states.inventory_turns)
self.recommend.add_state("classification", self.states.classification)
self.recommend.add_state("traffic_light", self.states.traffic_light)
self.recommend.add_state("forecast", self.states.forecast)
self.recommend.add_state("recommendation", self.recommend, end_state=1)
self.recommend.set_start("start")
recommend.add_state("revenue", states.revenue)
recommend.add_state("excess", states.excess)
recommend.add_state("shortage", states.shortage)
recommend.add_state("classification", states.classification)
recommend.add_state("inventory", states.inventory_turns)
recommend.add_state("recommendation", recommend, end_state=1)
recommend.set_start("start")
recommend.run()
# have to serialise the profile recommendation seperately from the sku recommendation
return deserialise_config(ABS_FILE_PATH['PROFILE_PICKLE'])
if __name__ == '__main__':
orders_analysis = model_inventory.analyse(
file_path=ABS_FILE_PATH['COMPLETE_CSV_SM'],
z_value=Decimal(1.28),
reorder_cost=Decimal(5000),
file_type="csv",
length=12)
# d = ProfileGenerator(analysed_orders=orders_analysis)
# d.Top_Concerns()
# resp = {}
#for i in run_sku_recommendation(analysed_orders=orders_analysis, forecast=deserialise_config(FORECAST_PICKLE)).values():
# print(i)
# d = ProfileStates(analysed_orders=orders_analysis, forecast=deserialise_config(FORECAST_PICKLE))
# d.revenue()
for i in run_profile_recommendation(
analysed_orders=orders_analysis,
forecast=deserialise_config(FORECAST_PICKLE)).values():
print(i)
recommend.add_state("start", states.initialise_machine)
recommend.add_state("revenue", states.revenue)
recommend.add_state("excess", states.excess)
recommend.add_state("shortage", states.shortage)
recommend.add_state("classification", states.classification)
recommend.add_state("inventory", states.inventory_turns)
recommend.add_state("recommendation", recommend, end_state=1)
recommend.set_start("start")
recommend.run()
# have to serialise the profile recommendation seperately from the sku recommendation
return deserialise_config(ABS_FILE_PATH['PROFILE_PICKLE'])
if __name__ == '__main__':
orders_analysis = model_inventory.analyse(file_path=ABS_FILE_PATH['COMPLETE_CSV_SM'],
z_value=Decimal(1.28),
reorder_cost=Decimal(5000),
file_type="csv",
length=12)
# d = ProfileGenerator(analysed_orders=orders_analysis)
# d.Top_Concerns()
# resp = {}
#for i in run_sku_recommendation(analysed_orders=orders_analysis, forecast=deserialise_config(FORECAST_PICKLE)).values():
# print(i)
# d = ProfileStates(analysed_orders=orders_analysis, forecast=deserialise_config(FORECAST_PICKLE))
# d.revenue()
for i in run_profile_recommendation(analysed_orders=orders_analysis,
forecast=deserialise_config(FORECAST_PICKLE)).values():
print(i)
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)
def load(file_path: str, location: str = None):
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')
db.create_all()
log.log(logging.DEBUG, 'loading currency symbols...\n')
print('loading currency symbols...', end="")
fx = currency_codes()
for key, value in fx.items():
codes = Currency()
codes.country = value[0]
codes.symbol = value[1]
codes.currency_code = key
db.session.add(codes)
db.session.commit()
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)
# remove assumption file type is csv
ia = [analysis.orders_summary() for analysis in
model_inventory.analyse(file_path=file_path, z_value=Decimal(1.28),
reorder_cost=Decimal(5000), file_type="csv", length=12, currency=currency)]
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="")
simple_forecast = {analysis.sku_id: analysis.simple_exponential_smoothing_forecast for analysis in
model_inventory.analyse(file_path=file_path, z_value=Decimal(1.28),
reorder_cost=Decimal(5000), file_type="csv",
length=12, currency=currency)}
holts_forecast = {analysis.sku_id: analysis.holts_trend_corrected_forecast for analysis in
model_inventory.analyse(file_path=file_path, z_value=Decimal(1.28),
reorder_cost=Decimal(5000), file_type="csv",
length=12,currency=currency)}
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()
# loads inventory profile recommendations
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]")
def test_data_frame(self):
raw_df = pd.read_csv(ABS_FILE_PATH['COMPLETE_CSV_SM'])
analysis_df = analyse(df=raw_df, start=1, interval_length=12, interval_type='months')
self.assertIsInstance(analysis_df[['sku', 'quantity_on_hand', 'excess_stock', 'shortages', 'ABC_XYZ_Classification']], DataFrame)
