class OOSOtherAKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(OOSOtherAKpi, self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.utils = StraussfritolayilUtil(None, data_provider)
def calculate(self):
kpi_fk = self.utils.common.get_kpi_fk_by_kpi_type(
Consts.OOS_OTHER_A_KPI)
sku_results = self.dependencies_data
if sku_results.empty:
return
assortment_fks = set(sku_results['denominator_id'])
for assortment_fk in assortment_fks:
assortment_df = sku_results[sku_results['denominator_id'] ==
assortment_fk]
denominator = len(assortment_df)
numerator = len(
assortment_df[assortment_df['result'] == Consts.OOS])
result = self.utils.calculate_sos_result(numerator, denominator)
self.write_to_db_result(fk=kpi_fk,
numerator_id=self.utils.own_manuf_fk,
denominator_id=self.utils.store_id,
context_id=assortment_fk,
result=result,
numerator_result=numerator,
denominator_result=denominator)
def kpi_type(self):
pass
class NumberOfUniqueSKUsKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(NumberOfUniqueSKUsKpi, self).__init__(data_provider, config_params=config_params, **kwargs)
self.utils = StraussfritolayilUtil(None, data_provider)
def calculate(self):
kpi_fk = self.utils.common.get_kpi_fk_by_kpi_type(Consts.NUMBER_OF_UNQIUE_SKUS_KPI)
template = self.utils.kpi_external_targets[self.utils.kpi_external_targets['kpi_type'] ==
Consts.NUMBER_OF_UNQIUE_SKUS_KPI]
fields_df = template[[Consts.FIELD, Consts.TARGET_MIN, Consts.TARGET_MAX]]
if template.empty:
categories = ['Core Salty']
else:
categories = template.iloc[0][Consts.CATEGORY].split(",")
sku_results = self.dependencies_data
df = self.utils.match_product_in_scene_wo_hangers.copy()
df['facings'] = 1
store_df = df.groupby(['scene_fk', 'bay_number', 'shelf_number']).sum().reset_index()[
['scene_fk', 'bay_number', 'shelf_number', 'facings']]
# filter only specific categories
df = df[(df['category'].isin(categories)) & (df['manufacturer_fk'] == self.utils.own_manuf_fk)]
category_df = df.groupby(['scene_fk', 'bay_number', 'shelf_number']).sum().reset_index()[
['scene_fk', 'bay_number', 'shelf_number', 'facings']]
category_df.columns = ['scene_fk', 'bay_number', 'shelf_number', 'facings category']
join_df = store_df.merge(category_df, on=['scene_fk', 'bay_number', 'shelf_number'], how="left").fillna(0)
join_df['percentage'] = join_df['facings category'] / join_df['facings']
# number of shelves with more than 50% strauss products
denominator = len(join_df)
numerator = len(join_df[join_df['percentage'] >= 0.5])
number_of_unique_skus = len(sku_results)
sadot = math.ceil(numerator / 5.0)
sadot = sadot if sadot != 0 else 1
target, upper_target = self.get_target(fields_df, sadot)
if not target:
score = Consts.NO_TARGET
ratio = None
else:
score = Consts.PASS if target <= number_of_unique_skus <= upper_target else Consts.FAIL
ratio = self.utils.calculate_sos_result(number_of_unique_skus, upper_target)
self.write_to_db_result(fk=kpi_fk, numerator_id=self.utils.own_manuf_fk, denominator_id=self.utils.store_id,
numerator_result=sadot, denominator_result=denominator, result=number_of_unique_skus,
target=upper_target, weight=ratio, score=score)
@staticmethod
def get_target(fields_df, sadot):
if sadot in fields_df[Consts.FIELD].values:
target = fields_df[fields_df['Field'] == sadot][Consts.TARGET_MIN].values[0]
upper_target = fields_df[fields_df['Field'] == sadot][Consts.TARGET_MAX].values[0]
elif sadot >= fields_df[Consts.FIELD].max():
target = fields_df[fields_df['Field'] == fields_df[Consts.FIELD].max()][Consts.TARGET_MIN].values[0]
upper_target = fields_df[fields_df['Field'] == fields_df[Consts.FIELD].max()][Consts.TARGET_MAX].values[0]
else:
target = upper_target = None
return target, upper_target
def kpi_type(self):
pass
class OSAStoreKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(OSAStoreKpi, self).__init__(data_provider, config_params=config_params, **kwargs)
self.utils = StraussfritolayilUtil(None, data_provider)
def calculate(self):
kpi_fk = self.utils.common.get_kpi_fk_by_kpi_type(Consts.OSA_STORE_KPI)
sku_results = self.dependencies_data
denominator = len(sku_results)
numerator = len(sku_results[sku_results['score'] == Consts.PASS])
result = self.utils.calculate_sos_result(numerator, denominator)
self.write_to_db_result(fk=kpi_fk, numerator_id=self.utils.own_manuf_fk,
denominator_id=self.utils.store_id, result=result,
numerator_result=numerator, denominator_result=denominator)
def kpi_type(self):
pass
def __init__(self, data_provider, config_params=None, **kwargs):
super(OOSOtherASKUKpi, self).__init__(data_provider, config_params=config_params, **kwargs)
self.utils = StraussfritolayilUtil(None, data_provider)
def __init__(self, data_provider, config_params=None, **kwargs):
super(NumberOfUniqueSKUsSKUKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.utils = StraussfritolayilUtil(None, data_provider)
def __init__(self, data_provider, config_params=None, **kwargs):
super(LSOSOwnBrandOutOfCategoryKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.utils = StraussfritolayilUtil(None, data_provider)
class LSOSOwnBrandOutOfCategoryKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(LSOSOwnBrandOutOfCategoryKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.utils = StraussfritolayilUtil(None, data_provider)
def calculate(self):
kpi_fk = self.utils.common.get_kpi_fk_by_kpi_type(
Consts.LSOS_OWN_BRAND_OUT_OF_CATEGORY_KPI)
template = self.utils.kpi_external_targets[
self.utils.kpi_external_targets['kpi_type'] ==
Consts.LSOS_OWN_BRAND_OUT_OF_CATEGORY_KPI]
if template.empty:
template_categories = ['Core Salty']
else:
template_categories = set(template[Consts.CATEGORY])
template = template.merge(self.utils.brand_mix_df,
left_on=Consts.BRAND_MIX,
right_on="entity_name",
how="left")
target_range = 2.0
own_manufacturer_matches = self.utils.own_manufacturer_matches_wo_hangers.copy(
)
own_manufacturer_matches = own_manufacturer_matches[
own_manufacturer_matches['stacking_layer'] == 1]
own_manufacturer_matches = own_manufacturer_matches[
own_manufacturer_matches['category'].isin(template_categories)]
own_manufacturer_matches = own_manufacturer_matches[
own_manufacturer_matches['product_type'].isin(
['Empty', 'Other', 'SKU'])]
for category in template_categories:
category_fk = self.utils.all_products[
self.utils.all_products['category'] ==
category]['category_fk'].values[0]
category_df = own_manufacturer_matches[
own_manufacturer_matches['category'] == category]
category_linear_length = category_df['width_mm_advance'].sum()
# strauss are looking at brand_mix as brand in this KPI
brands_mix = set(category_df['brand_mix_fk'])
for brand_mix_fk in brands_mix:
target = template[template['brand_mix_fk'] == brand_mix_fk][
Consts.TARGET]
if not target.empty:
target = target.values[0] * 100
else:
target = None
brand_mix_df = category_df[category_df['brand_mix_fk'] ==
brand_mix_fk]
brand_mix_linear_length = brand_mix_df['width_mm_advance'].sum(
)
sos_result = self.utils.calculate_sos_result(
brand_mix_linear_length, category_linear_length)
if not target:
kpi_score = Consts.NO_TARGET
else:
kpi_score = Consts.PASS if (
(target - target_range) <= sos_result <=
(target + target_range)) else Consts.FAIL
self.write_to_db_result(
fk=kpi_fk,
numerator_id=brand_mix_fk,
denominator_id=self.utils.own_manuf_fk,
context_id=category_fk,
numerator_result=brand_mix_linear_length,
target=target,
weight=target_range,
denominator_result=category_linear_length,
result=sos_result,
score=kpi_score)
def kpi_type(self):
pass
class LSOSManufacturerOutOfCategoryKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(LSOSManufacturerOutOfCategoryKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.utils = StraussfritolayilUtil(None, data_provider)
def calculate(self):
kpi_fk = self.utils.common.get_kpi_fk_by_kpi_type(
Consts.LSOS_MANUFACTURER_OUT_OF_CATEGORY_KPI)
template = self.utils.kpi_external_targets[
self.utils.kpi_external_targets['kpi_type'] ==
Consts.LSOS_MANUFACTURER_OUT_OF_CATEGORY_KPI]
if template.empty:
template_categories = ['Crackers', 'Core Salty']
else:
template_categories = set(template[Consts.CATEGORY])
own_manufacturer_matches = self.utils.own_manufacturer_matches_wo_hangers.copy(
)
own_manufacturer_matches = own_manufacturer_matches[
own_manufacturer_matches['stacking_layer'] == 1]
all_store_matches = self.utils.match_product_in_scene_wo_hangers.copy()
all_store_matches = all_store_matches[
all_store_matches['stacking_layer'] == 1]
for category in template_categories:
target = template[template[Consts.CATEGORY] == category][
Consts.TARGET]
if not target.empty:
target = target.values[0] * 100
else:
target = None
category_fk = self.utils.all_products[
self.utils.all_products['category'] ==
category]['category_fk'].values[0]
own_skus_category_df = own_manufacturer_matches[
own_manufacturer_matches['category_fk'] == category_fk]
store_category_df = all_store_matches[
all_store_matches['category_fk'] == category_fk]
own_category_linear_length = own_skus_category_df[
'width_mm_advance'].sum()
store_category_linear_length = store_category_df[
'width_mm_advance'].sum()
sos_result = self.utils.calculate_sos_result(
own_category_linear_length, store_category_linear_length)
if not target:
kpi_score = Consts.NO_TARGET
else:
kpi_score = Consts.PASS if (
target <= sos_result) else Consts.FAIL
self.write_to_db_result(
fk=kpi_fk,
numerator_id=self.utils.own_manuf_fk,
denominator_id=category_fk,
numerator_result=own_category_linear_length,
target=target,
denominator_result=store_category_linear_length,
result=sos_result,
score=kpi_score)
def kpi_type(self):
pass
def __init__(self, data_provider, config_params=None, **kwargs):
super(NumberOfFacingsMustHaveAssortmentSKUKpi, self).__init__(data_provider, config_params=config_params,
**kwargs)
self.utils = StraussfritolayilUtil(None, data_provider)
class NumberOfFacingsMustHaveAssortmentSKUKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(NumberOfFacingsMustHaveAssortmentSKUKpi, self).__init__(data_provider, config_params=config_params,
**kwargs)
self.utils = StraussfritolayilUtil(None, data_provider)
def calculate(self):
kpi_fk = self.utils.common.get_kpi_fk_by_kpi_type(Consts.NUMBER_OF_FACINGS_MUST_HAVE_KPI)
template = self.utils.kpi_external_targets[self.utils.kpi_external_targets['kpi_type'] ==
Consts.NUMBER_OF_FACINGS_MUST_HAVE_KPI]
template_categories = set(template[Consts.CATEGORY])
fields_df = template[[Consts.EAN_CODE, Consts.FIELD, Consts.TARGET_MAX]]
matches = self.utils.match_product_in_scene_wo_hangers.copy()
matches['facings'] = 1
store_df = matches.groupby(['scene_fk', 'bay_number', 'shelf_number']).sum().reset_index()[
['scene_fk', 'bay_number', 'shelf_number', 'facings']]
categories = set(self.utils.all_products[self.utils.all_products[
'category'].isin(template_categories)]['category_fk'])
# not_existing_products_df = assortment[assortment['in_store_wo_hangers'] == 0]
df = matches[(matches['category_fk'].isin(categories)) & (matches['manufacturer_fk'] ==
self.utils.own_manuf_fk)]
category_df = df.groupby(['scene_fk', 'bay_number', 'shelf_number']).sum().reset_index()[
['scene_fk', 'bay_number', 'shelf_number', 'facings']]
category_df.columns = ['scene_fk', 'bay_number', 'shelf_number', 'facings category']
join_df = store_df.merge(category_df, on=['scene_fk', 'bay_number', 'shelf_number'], how="left").fillna(0)
join_df['percentage'] = join_df['facings category'] / join_df['facings']
# number of shelves with more than 50% strauss products
number_of_shelves = len(join_df[join_df['percentage'] >= 0.5])
sadot = math.ceil(number_of_shelves / 5.0)
sadot = sadot if sadot != 0 else 1
sadot = sadot if sadot < fields_df[Consts.FIELD].max() else fields_df[Consts.FIELD].max()
template = template[template[Consts.FIELD] == sadot]
assortment = self.tarnsform_kpi_external_targets_to_assortment(template)
for i, sku_row in assortment.iterrows():
product_fk = sku_row['product_fk']
facings = sku_row['facings_all_products_wo_hangers']
target = sku_row[Consts.TARGET_MAX]
score = Consts.PASS if facings >= target else Consts.FAIL
self.write_to_db_result(fk=kpi_fk, numerator_id=product_fk, result=facings, weight=sadot, target=target,
denominator_id=self.utils.store_id, score=score)
def tarnsform_kpi_external_targets_to_assortment(self, template):
assortment = template[['kpi_fk', Consts.EAN_CODE, Consts.FIELD, Consts.TARGET_MAX]].copy()
assortment.rename(columns={'EAN Code': Consts.REPLACMENT_EAN_CODES}, inplace=True)
assortment['facings'] = assortment['facings_wo_hangers'] = 0
assortment['in_store'] = assortment['in_store_wo_hangers'] = 0
assortment[Consts.REPLACMENT_EAN_CODES] = assortment[Consts.REPLACMENT_EAN_CODES].map(
lambda row: [row] if type(row) != list else row)
assortment[Consts.REPLACMENT_EAN_CODES] = assortment[Consts.REPLACMENT_EAN_CODES].apply(
lambda row: [x.strip() for x in row] if row else None)
assortment = self.utils.handle_replacment_products_row(assortment)
assortment = self.add_product_fk_to_missing_products(assortment)
return assortment
def add_product_fk_to_missing_products(self, assortment):
missing_products = assortment[assortment['in_store_wo_hangers'] != 1]
for i, row in missing_products.iterrows():
ean_code = row[Consts.REPLACMENT_EAN_CODES][0]
product_df = self.utils.all_products[self.utils.all_products['product_ean_code'] == ean_code][
'product_fk']
product_fk = int(product_df.values[0]) if product_df.values[0] else -1
assortment.loc[i, 'product_fk'] = product_fk
return assortment
def kpi_type(self):
pass