def __init__(self, analysed_orders: UncertainDemand, forecast: dict = None):
self._analysed_orders = analysed_orders
self._summarised_inventory = Inventory(analysed_orders)
self._htces_forecast = forecast
self._summary = {}
self._compiled_response = ResponseSingleton()
self._currency_symbol = self._retrieve_currency()
def setUp(self):
self.__skus = ['KR202-209', 'KR202-210', 'KR202-211']
self.__orders_analysis = model_inventory.analyse_orders_abcxyz_from_file(
file_path=ABS_FILE_PATH['COMPLETE_CSV_SM'],
z_value=Decimal(1.28),
reorder_cost=Decimal(5000),
file_type="csv",
length=12)
self.__categories = ['excess_stock', 'shortages', 'revenue']
self.__abc_classification = ('AX', 'AY', 'AZ', 'BX', 'BY', 'BZ', 'CX',
'CY', 'CZ')
self.__analysis_summary = Inventory(
processed_orders=self.__orders_analysis)
self.__describe_sku = [
'excess_cost', 'percentage_contribution_revenue',
'markup_percentage', 'shortage_rank', 'unit_cost', 'max_order',
'retail_price', 'classification', 'excess_rank', 'average_orders',
'revenue_rank', 'excess_units', 'safety_stock_units',
'shortage_cost', 'revenue', 'min_order', 'safety_stock_rank',
'safety_stock_cost', 'sku_id', 'gross_profit_margin',
'shortage_units'
]
self.__abc_raw = self.__analysis_summary.abc_xyz_raw
def setUp(self):
self.__skus = ['KR202-209', 'KR202-210', 'KR202-211']
self.__orders_analysis = model_inventory.analyse_orders_abcxyz_from_file(file_path=ABS_FILE_PATH['COMPLETE_CSV_SM'],
z_value=Decimal(1.28),
reorder_cost=Decimal(5000),
file_type="csv",
length=12)
self.__categories = ['excess_stock', 'shortages', 'revenue']
self.__abc_classification = ('AX', 'AY', 'AZ', 'BX', 'BY', 'BZ', 'CX', 'CY', 'CZ')
self.__analysis_summary = Inventory( processed_orders =self.__orders_analysis)
self.__describe_sku = ['excess_cost', 'percentage_contribution_revenue', 'markup_percentage',
'shortage_rank', 'unit_cost', 'max_order', 'retail_price', 'classification',
'excess_rank', 'average_orders', 'revenue_rank', 'excess_units', 'safety_stock_units',
'shortage_cost', 'revenue', 'min_order', 'safety_stock_rank', 'safety_stock_cost',
'sku_id', 'gross_profit_margin', 'shortage_units']
self.__abc_raw = self.__analysis_summary.abc_xyz_raw
class SKUStates:
"""Contains states required to generate recommendations for individual SKUs"""
_END_STATE = 'recommendation'
_EMPTY = 'EMPTY'
_TRANSITION_STATES = {
'EXCESS_RANK_STATE': 'excess_rank',
'SHORTAGE_RANK_STATE': 'shortage_rank',
'INVENTORY_TURNS_STATE': 'inventory_turns',
'CLASSIFICATION_STATE': 'classification',
'TRAFFIC_LIGHT_STATE': 'traffic_light',
'FORECAST_STATE': 'forecast',
'RECOMMENDATION_STATE': 'recommendation'
}
def __init__(self, analysed_orders: UncertainDemand, forecast: dict = None):
self._analysed_orders = analysed_orders
self._summarised_inventory = Inventory(analysed_orders)
self._htces_forecast = forecast
self._summary = {}
self._compiled_response = ResponseSingleton()
self._currency_symbol = self._retrieve_currency()
@property
def currency(self):
return self._currency_symbol
@property
def analysed_orders(self) -> UncertainDemand:
return self._analysed_orders
@property
def summarised_inventory(self) -> Inventory:
return self._summarised_inventory
@property
def htces_forecast(self) -> dict:
return self._htces_forecast
@property
def compiled_response(self) -> ResponseSingleton:
return self._compiled_response
@property
def summary(self) -> dict:
return self._summary
@summary.setter
def summary(self, value):
self._summary = value
def _retrieve_currency(self) -> str:
currency_code = ''
for i in self.analysed_orders:
currency_code = i.currency
break
symbol = Currency(code=currency_code)
return symbol.retrieve_symbol()
def _setup_summary(self, sku: str):
"""Prepares summary for each sku.
Args:
sku (str): SKU unique identification
"""
self.summary = [description for description in
self._summarised_inventory.describe_sku(sku)][0]
def initialise_machine(self, sku: str) -> tuple:
"""Initialises state machine.
Args:
sku (str): SKU unique identification
Returns:
tuple: New State and sku unique identification
"""
self._setup_summary(sku=sku)
excess_units = int(self._summary.get('excess_units'))
shortage_units = int(self._summary.get('shortage_units'))
if excess_units > 0:
state = self._TRANSITION_STATES.get('EXCESS_RANK_STATE', self._END_STATE)
elif shortage_units > 0:
state = self._TRANSITION_STATES.get('SHORTAGE_RANK_STATE', self._END_STATE)
else:
state = self._TRANSITION_STATES.get('INVENTORY_TURNS_STATE', self._END_STATE)
return state, sku
def append_response(self, response: str, sku: str):
""" Appends each response to the dict in the singleton.
Args:
response:
sku:
Returns:
"""
resp = self.compiled_response.shared_response.get(sku, self._EMPTY)
if self._EMPTY != resp:
response = resp + response
self.compiled_response.shared_response.update(**{'{}'.format(sku): response})
else:
self.compiled_response.shared_response.update(**{'{}'.format(sku): response})
def excess_rank(self, sku: str) -> tuple:
"""Excess State.
Args:
sku (str): SKU unique identification number.
Returns:
str: New state and SKU ID
"""
excess_rank = int(self._summary.get('excess_rank', 11))
if excess_rank <= 10:
response = '{} is one of the top 10 overstocked SKUs in your inventory profile, ranked {} of ' \
'10 overstocked SKUs. '.format(sku, sku, excess_rank)
self.append_response(response=response, sku=sku)
sku_classification = self._summary.get('classification', 'XX')
if sku_classification == 'AX':
quantity_on_hand = self._summary.get('quantity_on_hand')
reorder_quantity = self._summary.get('reorder_quantity')
response = 'The {} inventory classification indicates a stable demand profile. ' \
'SKU {} is in the 20% of SKU\'s that contribute 80% yearly revenue. ' \
'Unless {} product is approaching end of life (EOL)' \
', there is likely little need to panic. ' \
'Holding purchase orders for this SKU will reduce ' \
'the excess. Review consumption of this SKU until the quantity on hand, ' \
'currently {}, is close to or below the reorder level of {}. '.format(sku_classification,
sku, sku,
quantity_on_hand,
reorder_quantity)
self.append_response(response=response, sku=sku)
state = self._TRANSITION_STATES.get('INVENTORY_TURNS_STATE', self._END_STATE)
return state, sku
def shortage_rank(self, sku: str) -> tuple:
"""Shortage rank state
Args:
sku (str): SKU unique identification.
Returns:
tuple: New state and SKU ID
"""
shortage_rank = int(self._summary.get('shortage_rank', 11))
if shortage_rank <= 10:
response = '{} is one of the top 10 understocked SKUs ' \
'in your inventory profile. {} is currently ranked {} of ' \
'10 understocked SKUs. '.format(sku, sku, shortage_rank)
self.append_response(response=response, sku=sku)
state = self._TRANSITION_STATES.get('INVENTORY_TURNS_STATE', self._END_STATE)
return state, sku
def inventory_turns(self, sku: str) -> tuple:
"""Inventory turns state.
Args:
sku: SKU unique identification.
Returns:
tuple: New state and SKU ID
"""
inventory_turns = float(self._summary.get('inventory_turns'))
excess_rank = int(self._summary.get('excess_rank', 11))
if inventory_turns <= 2.00 and excess_rank <= 10:
response = '{} has a very low rolling inventory turn rate at {:.2f}. {} may be a greater cause for ' \
'concern considering it is also in the top 10 overstocked ' \
'SKUs in the inventory profile. '.format(sku, float(self._summary.get('inventory_turns')), sku)
self.append_response(response=response, sku=sku)
elif inventory_turns <= 2.00:
response = '{} has a very low rolling inventory turn rate at {:.2f}. '.format(
sku, float(self._summary.get('inventory_turns')))
self.append_response(response=response, sku=sku)
state = self._TRANSITION_STATES.get('CLASSIFICATION_STATE', self._END_STATE)
return state, sku
def classification(self, sku: str) -> tuple:
"""Classification state.
Args:
sku (str): SKU ID number
Returns:
tuple: New state and SKU ID
"""
classification = self._summary.get('classification')
unit_cost_rank = self._summary.get('unit_cost_rank')
excess_rank = int(self._summary.get('excess_rank', 11))
if classification in ('AZ', 'BZ', 'CZ'):
response = 'The {} classification indicates that {} has volatile demand, ' \
'making it difficult to predict' \
' the consumption of the excess. '.format(self._summary.get('classification'), sku)
self.append_response(response=response, sku=sku)
if classification == 'CZ' and unit_cost_rank < 10 and excess_rank <= 10:
response = '{} is a slow moving volatile SKU. A unit cost of {} makes it also one of the more costly' \
' in the inventory profile. ' \
'It may be prudent to make financial provisions for this product, ' \
'discount them or bundle them with a complimentary popular product ' \
'in a promotion. '.format(sku, self._summary.get('unit_cost'))
self.append_response(response=response, sku=sku)
if classification in ('AX', 'BX'):
response = 'The {} classification, indicates that {} has a stable demand profile and ' \
'contributes significantly to revenue. ' \
'Corrective action should be taken to bring the quantity on hand (QOH) up from {} to {}. '
self.append_response(response=response, sku=sku)
state = self._TRANSITION_STATES.get('TRAFFIC_LIGHT_STATE', self._END_STATE)
return state, sku
def traffic_light(self, sku: str) -> tuple:
""" Traffic light state.
Args:
sku (str): SKU ID number
Returns:
tuple: New state and SKU ID
"""
traffic_light = self._summary.get('inventory_traffic_light')
excess_units = int(self._summary.get('excess_units', 11))
classification = self._summary.get('classification')
shortage_units = int(self._summary.get('shortage_units', 11))
if traffic_light == 'white':
response = 'The QOH is less than 75% of safety stock, implying a substantial depletion of ' \
'buffer stock. Take into consideration that it is acceptable ' \
'to dip into safety stock approximately 50% of the time. However, this situation ' \
'poses a threat for servicing future demand. This situation may be acceptable if this product ' \
'is \'end of life\' and the goal is to drive down stock. Corrective action is ' \
'required if it is not currently a policy to reduce the QOH for {}, and the ' \
'receipt of a purchase order is not imminent. '.format(sku)
self.append_response(response=response, sku=sku)
elif traffic_light == 'red':
response = 'The QOH is less 50% of the recommended safety stock. ' \
'Take into consideration that it is acceptable ' \
'to dip into safety stock approximately 50% of the time, consuming ' \
'approximately 50% of its value.' \
'Therefore, in this situation there may be little imminent threat to the service level. ' \
'Checking that a purchase order has been placed may be prudent. '.format(sku)
self.append_response(response=response, sku=sku)
elif traffic_light == 'amber':
response = 'The QOH is less than the reorder level but has yet to hit safety stock. ' \
'There is little to worry about at this point. The periodic review of inventory ' \
'and communication of any upcoming deals or promotions ' \
'that may significantly impact the demand profile should be a focus.'
self.append_response(response=response, sku=sku)
elif traffic_light == 'green' and excess_units == 0 and classification not in ('CZ', 'CY', 'BZ', 'AZ') \
and shortage_units == 0:
response = 'Congratulations the QOH is within optimal boundaries. ' \
'There is little to worry about at this point. The periodic review of stock ' \
'and communication of any upcoming deals or promotions that may significantly ' \
'impact the demand profile should be a focus. '
self.append_response(response=response, sku=sku)
state = self._TRANSITION_STATES.get('FORECAST_STATE', self._END_STATE)
return state, sku
def forecast(self, sku: str) -> tuple:
"""Forecast state.
Args:
sku (str): SKU unique identification number
Returns:
tuple:
"""
# if the demand shows a linear trend then check if the forecast is less than the excess units.
if self._htces_forecast.get(sku)['statistics']['trend']:
if self._htces_forecast.get(sku)['forecast'][0] < int(self._summary.get('excess_units')):
response = 'It is unlikely {} will fair better next month as the most optimistic forecast is '
self.append_response(response=response, sku=sku)
if self._htces_forecast.get(sku)['statistics']['trend'] and int(self._summary.get('excess_units')) > 0 \
and self._summary.get('classification') in ('AX', 'AY', 'BX', 'BY', 'CX') \
and self._htces_forecast.get(sku)['forecast'][0] > int(self._summary.get('excess_units')):
response = 'The current excess can be reduced by reducing purchase orders and allow the ' \
'forecasted demand to be catered for from stock. '
self.append_response(response=response, sku=sku)
state = self._TRANSITION_STATES.get('RECOMMENDATION_STATE', self._END_STATE)
serialise_config(configuration=self._compiled_response.shared_response,
file_path=ABS_FILE_PATH['RECOMMENDATION_PICKLE'])
return state, sku
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 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)
class TestSummariseAnalysis(TestCase):
def setUp(self):
self.__skus = ['KR202-209', 'KR202-210', 'KR202-211']
self.__orders_analysis = model_inventory.analyse_orders_abcxyz_from_file(file_path=ABS_FILE_PATH['COMPLETE_CSV_SM'],
z_value=Decimal(1.28),
reorder_cost=Decimal(5000),
file_type="csv",
length=12)
self.__categories = ['excess_stock', 'shortages', 'revenue']
self.__abc_classification = ('AX', 'AY', 'AZ', 'BX', 'BY', 'BZ', 'CX', 'CY', 'CZ')
self.__analysis_summary = Inventory( processed_orders =self.__orders_analysis)
self.__describe_sku = ['excess_cost', 'percentage_contribution_revenue', 'markup_percentage',
'shortage_rank', 'unit_cost', 'max_order', 'retail_price', 'classification',
'excess_rank', 'average_orders', 'revenue_rank', 'excess_units', 'safety_stock_units',
'shortage_cost', 'revenue', 'min_order', 'safety_stock_rank', 'safety_stock_cost',
'sku_id', 'gross_profit_margin', 'shortage_units']
self.__abc_raw = self.__analysis_summary.abc_xyz_raw
def test_describe_sku_length(self):
"""counts number of items returned as a description of the sku
"""
item = [description for description in self.__analysis_summary.describe_sku('KR202-209')]
def test_describe_type_error(self):
with self.assertRaises(expected_exception=TypeError):
desc = [description for description in self.__analysis_summary.describe_sku('KR2-0')]
def test_describe_sku_keys(self):
s = [summarised for summarised in self.__analysis_summary.describe_sku('KR202-217')]
for key in self.__describe_sku:
self.assertIn(key, s[0])
# TODO-fix makes sure that all categories have are safe in this method
def test_category_ranking_filter_top10(self):
"""Checks that the filter returns top ten shortages that are greater than the bottom ten.
uses list of categories."""
for category in self.__categories:
top_ten = [item for item in
self.__analysis_summary.rank_summary(
attribute=category, count=10, reverse=True)]
for item in top_ten:
for bottom in [item for item in
self.__analysis_summary.rank_summary(
attribute=category, count=10, reverse=False)]:
self.assertGreaterEqual(Decimal(item[category]), Decimal(bottom[category]))
def test_category_ranking_filter_top10_attr_error(self):
""" Tests the correct attribute name has been provided. Test uses shortage instead of shortages."""
with self.assertRaises(expected_exception=AttributeError):
top_ten_shortages = [item for item in
self.__analysis_summary.rank_summary(
attribute="shortageee", count=10, reverse=True)]
def test_category_ranking_filter_bottom10(self):
"""Checks that the filter returns bottom ten of each category. Each value is compared against top ten for the
same category."""
for category in self.__categories:
bottom_ten = [item for item in
self.__analysis_summary.rank_summary(
attribute=category, count=10, reverse=False)]
for item in bottom_ten:
for Top in [item for item in
self.__analysis_summary.rank_summary(
attribute=category, count=10, reverse=True)]:
self.assertLessEqual(Decimal(item[category]), Decimal(Top[category]))
def test_abcxyz_summary_category_selection(self):
# assert that every summarised cost is eqal to the cost of every sku with that
# category in the original structure
for category in self.__categories:
for analysis in self.__analysis_summary.abc_xyz_summary(category=(category,)):
self.assertEqual(1, len(analysis))
def test_abcxyz_classification_selection(self):
for category in self.__abc_classification:
for summary in self.__analysis_summary.abc_xyz_summary(classification=((category,))):
for classification in summary:
self.assertEqual(category, classification)
def test_abcxyz_classification_units_vs_cost(self):
"""checks that the units areg lower thant the cost for each classification summary. Using the category list
all versions are tested in this one test."""
for category in self.__abc_classification:
for summary in self.__analysis_summary.abc_xyz_summary(classification=(category,),
category=('excess_stock',), value='units'):
for summary1 in self.__analysis_summary.abc_xyz_summary(classification=(category,),
category=('excess_stock',)):
self.assertLessEqual(summary.get(category)['excess_stock_units'],
summary1.get(category)['excess_stock_cost'])
def test_abcxyz_classification_label_error(self):
"""checks that an incorrect label will raise an error."""
for category in self.__abc_classification:
with self.assertRaises(expected_exception=TypeError):
x = [summary for summary in
self.__analysis_summary.abc_xyz_summary(classification=(category,), category=('excess',),
value='units')]
def test_abcxyz_units_summary(self):
pass
def test_abcxyz_currency_summary(self):
pass
class TestSummariseAnalysis(TestCase):
def setUp(self):
self.__skus = ['KR202-209', 'KR202-210', 'KR202-211']
self.__orders_analysis = model_inventory.analyse_orders_abcxyz_from_file(file_path=ABS_FILE_PATH['COMPLETE_CSV_SM'],
z_value=Decimal(1.28),
reorder_cost=Decimal(5000),
file_type="csv",
length=12)
self.__categories = ['excess_stock', 'shortages', 'revenue']
self.__abc_classification = ('AX', 'AY', 'AZ', 'BX', 'BY', 'BZ', 'CX', 'CY', 'CZ')
self.__analysis_summary = Inventory( processed_orders =self.__orders_analysis)
self.__describe_sku = ['excess_cost', 'percentage_contribution_revenue', 'markup_percentage',
'shortage_rank', 'unit_cost', 'max_order', 'retail_price', 'classification',
'excess_rank', 'average_orders', 'revenue_rank', 'excess_units', 'safety_stock_units',
'shortage_cost', 'revenue', 'min_order', 'safety_stock_rank', 'safety_stock_cost',
'sku_id', 'gross_profit_margin', 'shortage_units']
self.__abc_raw = self.__analysis_summary.abc_xyz_raw
def test_describe_sku_length(self):
"""counts number of items returned as a description of the sku
"""
item = [description for description in self.__analysis_summary.describe_sku('KR202-209')]
def test_describe_type_error(self):
with self.assertRaises(expected_exception=TypeError):
desc = [description for description in self.__analysis_summary.describe_sku('KR2-0')]
def test_describe_sku_keys(self):
s = [summarised for summarised in self.__analysis_summary.describe_sku('KR202-217')]
for key in self.__describe_sku:
self.assertIn(key, s[0])
# TODO-fix makes sure that all categories have are safe in this method
def test_category_ranking_filter_top10(self):
"""Checks that the filter returns top ten shortages that are greater than the bottom ten.
uses list of categories."""
for category in self.__categories:
top_ten = [item for item in
self.__analysis_summary.rank_summary(
attribute=category, count=10, reverse=True)]
for item in top_ten:
for bottom in [item for item in
self.__analysis_summary.rank_summary(
attribute=category, count=10, reverse=False)]:
self.assertGreaterEqual(Decimal(item[category]), Decimal(bottom[category]))
def test_category_ranking_filter_top10_attr_error(self):
""" Tests the correct attribute name has been provided. Test uses shortage instead of shortages."""
with self.assertRaises(expected_exception=AttributeError):
top_ten_shortages = [item for item in
self.__analysis_summary.rank_summary(
attribute="shortageee", count=10, reverse=True)]
def test_category_ranking_filter_bottom10(self):
"""Checks that the filter returns bottom ten of each category. Each value is compared against top ten for the
same category."""
for category in self.__categories:
bottom_ten = [item for item in
self.__analysis_summary.rank_summary(
attribute=category, count=10, reverse=False)]
for item in bottom_ten:
for Top in [item for item in
self.__analysis_summary.rank_summary(
attribute=category, count=10, reverse=True)]:
self.assertLessEqual(Decimal(item[category]), Decimal(Top[category]))
def test_abcxyz_summary_category_selection(self):
# assert that every summarised cost is eqal to the cost of every sku with that
# category in the original structure
for category in self.__categories:
for analysis in self.__analysis_summary.abc_xyz_summary(category=(category,)):
self.assertEqual(1, len(analysis))
def test_abcxyz_classification_selection(self):
for category in self.__abc_classification:
for summary in self.__analysis_summary.abc_xyz_summary(classification=((category,))):
for classification in summary:
self.assertEqual(category, classification)
def test_abcxyz_classification_units_vs_cost(self):
"""checks that the units areg lower thant the cost for each classification summary. Using the category list
all versions are tested in this one test."""
for category in self.__abc_classification:
for summary in self.__analysis_summary.abc_xyz_summary(classification=(category,),
category=('excess_stock',), value='units'):
for summary1 in self.__analysis_summary.abc_xyz_summary(classification=(category,),
category=('excess_stock',)):
self.assertLessEqual(summary.get(category)['excess_stock_units'],
summary1.get(category)['excess_stock_cost'])
def test_abcxyz_classification_label_error(self):
"""checks that an incorrect label will raise an error."""
for category in self.__abc_classification:
with self.assertRaises(expected_exception=TypeError):
x = [summary for summary in
self.__analysis_summary.abc_xyz_summary(classification=(category,), category=('excess',),
value='units')]
def test_abcxyz_units_summary(self):
pass
def test_abcxyz_currency_summary(self):
pass
