class CCAAUToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
EXCLUDE_FILTER = 0
INCLUDE_FILTER = 1
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.templates = self.data_provider[Data.TEMPLATES]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.template = self.data_provider.all_templates # templates
self.kpi_static_data = self.common.get_new_kpi_static_data()
self.toolbox = GENERALToolBox(data_provider)
kpi_path = os.path.dirname(os.path.realpath(__file__))
base_file = os.path.basename(kpi_path)
self.exclude_filters = pd.read_excel(os.path.join(
kpi_path[:-len(base_file)], 'Data', 'template.xlsx'),
sheetname="Exclude")
self.Include_filters = pd.read_excel(os.path.join(
kpi_path[:-len(base_file)], 'Data', 'template.xlsx'),
sheetname="Include")
self.bay_count_kpi = pd.read_excel(os.path.join(
kpi_path[:-len(base_file)], 'Data', 'template.xlsx'),
sheetname="BayCountKPI")
def main_calculation(self):
"""
This function calculates the KPI results.
"""
self.calculate_sos()
self.calculate_bay_kpi()
self.common.commit_results_data_to_new_tables()
def calculate_sos(self):
"""
This function filtering Data frame - "scene item facts" by the parameters in the template.
Sending the filtered data frames to linear Sos calculation and facing Sos calculation
Writing the results to the new tables in DB
"""
facing_kpi_fk = self.kpi_static_data[
self.kpi_static_data['client_name'] ==
'FACINGS_SOS_SCENE_TYPE_BY_MANUFACTURER']['pk'].iloc[0]
linear_kpi_fk = self.kpi_static_data[
self.kpi_static_data['client_name'] ==
'LINEAR_SOS_SCENE_TYPE_BY_MANUFACTURER']['pk'].iloc[0]
den_facing_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Facing')
& (self.exclude_filters['apply on'] == 'Denominator')]
den_linear_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Linear')
& (self.exclude_filters['apply on'] == 'Denominator')]
num_facing_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Facing')
& (self.exclude_filters['apply on'] == 'Numerator')]
num_linear_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Linear')
& (self.exclude_filters['apply on'] == 'Numerator')]
scene_templates = self.scif['template_fk'].unique().tolist()
scene_manufactures = self.scif['manufacturer_fk'].unique().tolist()
# exclude filters denominator
den_general_facing_filters = self.create_dict_filters(
den_facing_exclude_template, self.EXCLUDE_FILTER)
den_general_linear_filters = self.create_dict_filters(
den_linear_exclude_template, self.EXCLUDE_FILTER)
# exclude filters numerator
num_general_facing_filters = self.create_dict_filters(
num_facing_exclude_template, self.EXCLUDE_FILTER)
num_general_linear_filters = self.create_dict_filters(
num_linear_exclude_template, self.EXCLUDE_FILTER)
df_num_fac = self.filter_2_cond(self.scif, num_facing_exclude_template)
df_num_lin = self.filter_2_cond(self.scif, num_linear_exclude_template)
df_den_lin = self.filter_2_cond(self.scif, den_facing_exclude_template)
df_den_fac = self.filter_2_cond(self.scif, den_linear_exclude_template)
for template in scene_templates:
for manufacture in scene_manufactures:
sos_filters = {
"template_fk": (template, self.INCLUDE_FILTER),
"manufacturer_fk": (manufacture, self.INCLUDE_FILTER)
}
tem_filters = {"template_fk": (template, self.INCLUDE_FILTER)}
dict_num_facing = dict(
(k, v) for d in [sos_filters, num_general_facing_filters]
for k, v in d.items())
numerator_facings = self.calculate_share_space(
df_num_fac, dict_num_facing)[0]
dict_num_linear = dict(
(k, v) for d in [sos_filters, num_general_linear_filters]
for k, v in d.items())
numerator_linear = self.calculate_share_space(
df_num_lin, dict_num_linear)[1]
dict_den_facing = dict(
(k, v) for d in [tem_filters, den_general_facing_filters]
for k, v in d.items())
denominator_facings = self.calculate_share_space(
df_den_fac, dict_den_facing)[0]
dict_den_linear = dict(
(k, v) for d in [tem_filters, den_general_linear_filters]
for k, v in d.items())
denominator_linear = self.calculate_share_space(
df_den_lin, dict_den_linear)[1]
score_facing = 0 if denominator_facings == 0 else (
numerator_facings / denominator_facings) * 100
score_linear = 0 if denominator_linear == 0 else (
numerator_linear / denominator_linear) * 100
self.common.write_to_db_result_new_tables(
facing_kpi_fk, manufacture, numerator_facings,
score_facing, template, denominator_facings, score_facing)
self.common.write_to_db_result_new_tables(
linear_kpi_fk, manufacture, numerator_linear, score_linear,
template, denominator_linear, score_linear)
def create_dict_filters(self, template, param):
"""
:param template : Template of the desired filtering to data frame
:param param : exclude /include
:return: Dictionary of filters and parameter : exclude / include by demeaned
"""
filters_dict = {}
template_without_second = template[template['Param 2'].isnull()]
for row in template_without_second.iterrows():
filters_dict[row[1]['Param 1']] = (row[1]['Value 1'].split(','),
param)
return filters_dict
def filter_2_cond(self, data_frame, template):
"""
:param template: Template of the desired filtering
:param data_frame : Data frame
:return: data frame filtered by entries in the template with 2 conditions
"""
template_without_second = template[template['Param 2'].notnull()]
if template_without_second is not None:
for row in template_without_second.iterrows():
data_frame = data_frame.loc[
(~data_frame[row[1]['Param 1']].isin(row[1]['Value 1'].
split(','))) |
(~data_frame[row[1]['Param 2']].isin(row[1]['Value 2'].
split(',')))]
return data_frame
def calculate_share_space(self, data_frame, filters):
"""
:param filters: These are the parameters which the data frame is filtered by.
:param data_frame : relevant scene item facts data frame (filtered )
:return: The total number of facings and the shelf width (in mm) according to the filters.
"""
filtered_scif = data_frame[self.toolbox.get_filter_condition(
data_frame, **filters)]
sum_of_facings = filtered_scif['facings'].sum()
space_length = filtered_scif['gross_len_split_stack'].sum()
return sum_of_facings, space_length
def calculate_bay_kpi(self):
bay_kpi_sheet = self.bay_count_kpi
kpi = self.kpi_static_data.loc[self.kpi_static_data['type'] ==
BAY_COUNT_KPI]
if kpi.empty:
Log.info("CCAAU Calculate KPI Name:{} not found in DB".format(
BAY_COUNT_KPI))
else:
Log.info("CCAAU Calculate KPI Name:{} found in DB".format(
BAY_COUNT_KPI))
bay_kpi_row = bay_kpi_sheet[bay_kpi_sheet['KPI Name'] ==
BAY_COUNT_KPI]
if not bay_kpi_row.empty:
scene_types_to_consider = bay_kpi_row['Scene Type'].iloc[0]
if scene_types_to_consider == '*':
# Consider all scene types
scene_types_to_consider = 'all'
else:
scene_types_to_consider = [
x.strip() for x in scene_types_to_consider.split(',')
]
mpis_with_scene = self.match_product_in_scene.merge(
self.scene_info, how='left', on='scene_fk')
mpis_with_scene_and_template = mpis_with_scene.merge(
self.templates, how='left', on='template_fk')
if scene_types_to_consider != 'all':
mpis_with_scene_and_template = mpis_with_scene_and_template[
mpis_with_scene_and_template['template_name'].isin(
scene_types_to_consider)]
mpis_template_group = mpis_with_scene_and_template.groupby(
'template_fk')
for template_fk, template_data in mpis_template_group:
Log.info("Running for template ID {templ_id}".format(
templ_id=template_fk, ))
total_bays_for_scene_type = 0
scene_group = template_data.groupby('scene_fk')
for scene_fk, scene_data in scene_group:
Log.info(
"KPI Name:{kpi} bay count is {bay_c} for scene ID {scene_id}"
.format(
kpi=BAY_COUNT_KPI,
bay_c=int(scene_data['bay_number'].max()),
scene_id=scene_fk,
))
total_bays_for_scene_type += int(
scene_data['bay_number'].max())
Log.info(
"KPI Name:{kpi} total bay count is {bay_c} for template ID {templ_id}"
.format(
kpi=BAY_COUNT_KPI,
bay_c=total_bays_for_scene_type,
templ_id=template_fk,
))
self.common.write_to_db_result_new_tables(
fk=int(kpi['pk'].iloc[0]),
numerator_id=int(template_fk),
numerator_result=total_bays_for_scene_type,
denominator_id=int(self.store_id),
denominator_result=total_bays_for_scene_type,
result=total_bays_for_scene_type,
)
class CCPHLToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.template_data = pd.read_excel(TEMPLATE_PATH, 'KPIs').fillna('')
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_new_kpi_static_data()
self.kpi_static_data = self.kpi_static_data[self.kpi_static_data['kpi_family_fk'] == CCPHLConsts.CUSTOM_KPIS]
self.kpi_results_queries = []
self.mapping_param = {"manufacturer": "manufacturer_name"}
self.mapping_entity = {"manufacturer": "manufacturer_fk", "store": "store_id",
"scene_type": "template_fk", "brand": "brand_fk", "product": "product_fk"}
def main_calculation(self):
"""
This function calculates the KPI results.
"""
for group in self.template_data[CCPHLConsts.KPI_GROUP].unique():
kpis = self.template_data[self.template_data[CCPHLConsts.KPI_GROUP] == group]
if kpis.empty:
print("KPI Group:{} is not valid".format(group))
continue
for row_num, kpi in kpis.iterrows():
if kpi[CCPHLConsts.KPI_GROUP] == CCPHLConsts.SHELF_PURITY:
self.calculate_self_purity_util(kpi)
elif kpi[CCPHLConsts.KPI_GROUP] == CCPHLConsts.SHELF_CUTIL:
self.calculate_self_purity_util(kpi)
else:
continue
self.common.commit_results_data_to_new_tables()
def calculate_self_purity_util(self, kpi):
df_scene_data = self.scif
if df_scene_data.empty:
return
scene_types = [x.strip() for x in kpi[CCPHLConsts.SCENE_TYPE].split(',')]
filter_param_name_1 = kpi['filter_param_name_1'].strip()
if len(filter_param_name_1) != 0:
filter_param_value_1 = kpi['filter_param_value_1'].strip()
else:
filter_param_value_1 = ""
if str(kpi[CCPHLConsts.EXCLUDE_EMPTY]).upper() == 'Y':
df_scene_data = df_scene_data[df_scene_data[CCPHLConsts.PRODUCT_FK] != CCPHLConsts.EMPTY]
df_scene_data = df_scene_data[df_scene_data[CCPHLConsts.PRODUCT_FK] != CCPHLConsts.GENERAL_EMPTY]
if str(kpi[CCPHLConsts.EXCLUDE_IRRELEVANT]).upper() == 'Y':
df_scene_data = df_scene_data[df_scene_data[CCPHLConsts.IRRELEVANT] != CCPHLConsts.IRRELEVANT]
df_kpi_level_2_fk = self.kpi_static_data[self.kpi_static_data['type'] == kpi['kpi_name']]
if df_kpi_level_2_fk.empty:
kpi_level_2_fk = 0
else:
kpi_level_2_fk = df_kpi_level_2_fk.iloc[0]['pk']
df_scene_data = df_scene_data[df_scene_data['template_name'].isin(scene_types)]
group_list = []
for idx in range(1, 5):
entity = kpi['entity' + str(idx)].strip()
if entity == 'N/A' or len(entity) == 0:
continue
else:
entity = self.mapping_entity[kpi['entity' + str(idx)].strip()]
group_list.append(entity)
denominator = 0
if group_list[0] == 'store_id':
total_facings = df_scene_data['facings'].sum()
denominator = float(total_facings)
if len(filter_param_value_1) != 0:
df_scene_data2 = df_scene_data[df_scene_data[self.mapping_param[filter_param_name_1]]==filter_param_value_1]
else:
df_scene_data2 = df_scene_data
filter_columns = list(group_list)
filter_columns.append('facings')
df_scene_data2 = df_scene_data2[filter_columns]
df_purity = pd.DataFrame(df_scene_data2.groupby(group_list).sum().reset_index())
store_zero_results = str(kpi[CCPHLConsts.STORE_ZERO_RESULTS]).strip().upper()
for row_num, row_data in df_purity.iterrows():
numerator = row_data['facings']
if group_list[0] == 'template_fk':
df_scene_count = df_scene_data[df_scene_data['template_fk'] == row_data['template_fk']]
if df_scene_count.empty:
total_facings = 0
else:
total_facings = df_scene_count['facings'].sum()
denominator = float(total_facings)
try:
result = round(float(numerator) / float(denominator), 4)
except ZeroDivisionError:
print("Error: {}".format(ZeroDivisionError.message))
continue
if kpi_level_2_fk != 0:
if result == 0:
if store_zero_results == 'Y':
self.common.write_to_db_result_new_tables(fk=kpi_level_2_fk,
numerator_id=row_data[group_list[len(group_list)-1]],
denominator_id=row_data[group_list[0]],
numerator_result=numerator,
denominator_result=denominator,
result=result,
score=result)
else:
self.common.write_to_db_result_new_tables(fk=kpi_level_2_fk,
numerator_id=row_data[group_list[len(group_list) - 1]],
denominator_id=row_data[group_list[0]],
numerator_result=numerator,
denominator_result=denominator,
result=result,
score=result)
class PERFETTICNToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_new_kpi_static_data()
self.kpi_results_queries = []
self.store_info = self.data_provider[Data.STORE_INFO]
self.assortment = Assortment(self.data_provider, self.output)
self.template = self.data_provider.all_templates
def main_calculation(self, *args, **kwargs):
self.display_count()
self.assortment_calculation()
self.common.commit_results_data_to_new_tables()
def display_count(self):
"""This function calculates how many displays find from types secondary shelf
"""
num_brands = {}
display_info = self.scif['template_fk']
display_fks = display_info.unique()
template_seco = self.template[
self.template['included_in_secondary_shelf_report'] ==
'Y']['template_fk']
display_fks = list(
filter(lambda x: x in template_seco.values, display_fks))
count_fk = self.kpi_static_data[self.kpi_static_data['client_name'] ==
'COUNT OF DISPLAY']['pk'].iloc[0]
for value in display_fks:
num_brands[value] = display_info[display_info == value].count()
score = num_brands[value]
self.common.write_to_db_result_new_tables(count_fk, value, None,
score, score, score,
score)
return
def assortment_calculation(self):
"""
This function calculates 3 levels of assortment :
level3 is assortment SKU
level2 is assortment groups
level1 how many groups passed out of all
"""
lvl3_result = self.assortment.calculate_lvl3_assortment()
for result in lvl3_result.itertuples():
score = result.in_store
if score >= 1:
score = 100
self.common.write_to_db_result_new_tables(
result.kpi_fk_lvl3, result.product_fk, result.in_store, score,
result.assortment_group_fk, 1, score)
if not lvl3_result.empty:
lvl2_result = self.assortment.calculate_lvl2_assortment(
lvl3_result)
for result in lvl2_result.itertuples():
denominator_res = result.total
res = np.divide(float(result.passes), float(denominator_res))
if result.passes >= 1:
score = 100
else:
score = 0
self.common.write_to_db_result_new_tables(
result.kpi_fk_lvl2, result.assortment_group_fk,
result.passes, (res * 100),
result.assortment_super_group_fk, denominator_res, score)
if not lvl2_result.empty:
lvl1_result = self.assortment.calculate_lvl1_assortment(
lvl2_result)
for result in lvl1_result.itertuples():
denominator_res = result.total
res = np.divide(float(result.passes),
float(denominator_res))
if res >= 0:
score = 100
else:
score = 0
self.common.write_to_db_result_new_tables(
fk=result.kpi_fk_lvl1,
numerator_id=result.assortment_super_group_fk,
numerator_result=result.passes,
denominator_result=denominator_res,
result=(res * 100),
score=score)
return
class HEINEKENTWToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
DIST_STORE_LVL1 = 1014
OOS_STORE_LVL1 = 1015
DIST_STORE_LVL2 = 1016
OOS_STORE_LVL2 = 1017
DIST_CATEGORY_LVL1 = 1018
OOS_CATEGORY_LVL1 = 1019
DIST_CATEGORY_LVL2 = 1020
OOS_CATEGORY_LVL2 = 1021
DISTRIBUTION = 4
OOS = 5
MANUFACTURER_FK = 175 # heinken manfucturer
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_new_kpi_static_data()
self.kpi_results_queries = []
self.assortment = Assortment(self.data_provider, self.output)
def main_calculation(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
lvl3_result = self.assortment.calculate_lvl3_assortment()
self.category_assortment_calculation(lvl3_result)
self.store_assortment_calculation(lvl3_result)
self.common.commit_results_data_to_new_tables()
# self.common.commit_results_data_to_new_tables()
def category_assortment_calculation(self, lvl3_result):
"""
This function calculates 3 levels of assortment :
level3 is assortment SKU
level2 is assortment groups
"""
if not lvl3_result.empty:
# cat_df = self.scif[['product_fk', 'category_fk']]
cat_df = self.all_products[['product_fk', 'category_fk']]
lvl3_with_cat = lvl3_result.merge(cat_df,
on='product_fk',
how='left')
lvl3_with_cat = lvl3_with_cat[
lvl3_with_cat['category_fk'].notnull()]
for result in lvl3_with_cat.itertuples():
if result.in_store == 1:
score = self.DISTRIBUTION
else:
score = self.OOS
# Distrubtion
self.common.write_to_db_result_new_tables(
fk=self.DIST_CATEGORY_LVL1,
numerator_id=result.product_fk,
numerator_result=score,
result=score,
denominator_id=result.category_fk,
denominator_result=1,
score=score,
score_after_actions=score)
if score == self.OOS:
# OOS
self.common.write_to_db_result_new_tables(
fk=self.OOS_CATEGORY_LVL1,
numerator_id=result.product_fk,
numerator_result=score,
result=score,
denominator_id=result.category_fk,
denominator_result=1,
score=score,
score_after_actions=score)
category_list = lvl3_with_cat['category_fk'].unique()
for cat in category_list:
lvl3_result_cat = lvl3_with_cat[lvl3_with_cat["category_fk"] ==
cat]
lvl2_result = self.assortment.calculate_lvl2_assortment(
lvl3_result_cat)
for result in lvl2_result.itertuples():
denominator_res = result.total
res = np.divide(float(result.passes),
float(denominator_res))
# Distrubtion
self.common.write_to_db_result_new_tables(
fk=self.DIST_CATEGORY_LVL2,
numerator_id=self.MANUFACTURER_FK,
numerator_result=result.passes,
denominator_id=cat,
denominator_result=denominator_res,
result=res,
score=res,
score_after_actions=res)
# OOS
self.common.write_to_db_result_new_tables(
fk=self.OOS_CATEGORY_LVL2,
numerator_id=self.MANUFACTURER_FK,
numerator_result=denominator_res - result.passes,
denominator_id=cat,
denominator_result=denominator_res,
result=1 - res,
score=(1 - res),
score_after_actions=1 - res)
return
def store_assortment_calculation(self, lvl3_result):
"""
This function calculates the KPI results.
"""
for result in lvl3_result.itertuples():
if result.in_store == 1:
score = self.DISTRIBUTION
else:
score = self.OOS
# Distrubtion
self.common.write_to_db_result_new_tables(
fk=self.DIST_STORE_LVL1,
numerator_id=result.product_fk,
numerator_result=score,
result=score,
denominator_id=self.store_id,
denominator_result=1,
score=score)
if score == self.OOS:
# OOS
self.common.write_to_db_result_new_tables(
fk=self.OOS_STORE_LVL1,
numerator_id=result.product_fk,
numerator_result=score,
result=score,
denominator_id=self.store_id,
denominator_result=1,
score=score,
score_after_actions=score)
if not lvl3_result.empty:
lvl2_result = self.assortment.calculate_lvl2_assortment(
lvl3_result)
for result in lvl2_result.itertuples():
denominator_res = result.total
if not pd.isnull(result.target) and not pd.isnull(
result.group_target_date
) and result.group_target_date <= self.assortment.current_date:
denominator_res = result.target
res = np.divide(float(result.passes), float(denominator_res))
# Distrubtion
self.common.write_to_db_result_new_tables(
fk=self.DIST_STORE_LVL2,
numerator_id=self.MANUFACTURER_FK,
denominator_id=self.store_id,
numerator_result=result.passes,
denominator_result=denominator_res,
result=res,
score=res,
score_after_actions=res)
# OOS
self.common.write_to_db_result_new_tables(
fk=self.OOS_STORE_LVL2,
numerator_id=self.MANUFACTURER_FK,
numerator_result=denominator_res - result.passes,
denominator_id=self.store_id,
denominator_result=denominator_res,
result=1 - res,
score=1 - res,
score_after_actions=1 - res)
return
class SOLARBRToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
EXCLUDE_EMPTY = False
EXCLUDE_FILTER = 0
EMPTY = 'Empty'
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.k_engine = BaseCalculationsGroup(data_provider, output)
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.templates = {}
self.session_id = self.data_provider.session_id
self.score_templates = {}
self.get_templates()
self.get_score_template()
self.manufacturer_fk = self.all_products[
self.all_products['manufacturer_name'] == 'Coca Cola'].iloc[0]
self.sos = SOS(self.data_provider, self.output)
self.total_score = 0
self.session_fk = self.data_provider[Data.SESSION_INFO]['pk'].iloc[0]
self.toolbox = GENERALToolBox(self.data_provider)
self.scenes_info = self.data_provider[Data.SCENES_INFO]
self.kpi_results_new_tables_queries = []
# self.store_type = self.data_provider.store_type
def get_templates(self):
for sheet in Const.SHEETS_MAIN:
self.templates[sheet] = pd.read_excel(MAIN_TEMPLATE_PATH, sheetname=sheet.decode("utf-8"), keep_default_na=False)
def get_score_template(self):
for sheet in Const.SHEETS_SCORE:
self.score_templates[sheet] = pd.read_excel(SCORE_TEMPLATE_PATH, sheetname=sheet.decode("utf-8"), keep_default_na=False, encoding = "utf-8")
def main_calculation(self, *args, **kwargs):
main_template = self.templates[Const.KPIS]
for i, main_line in main_template.iterrows():
self.calculate_main_kpi(main_line)
self.commit_results()
def calculate_main_kpi(self, main_line):
kpi_name = main_line[Const.KPI_NAME]
kpi_type = main_line[Const.Type]
scene_types = self.does_exist(main_line, Const.SCENE_TYPES)
result = score = 0
general_filters = {}
scif_scene_types = self.scif['template_name'].unique().tolist()
store_type = str(self.store_info["store_type"].iloc[0])
store_types = self.does_exist_store(main_line, Const.STORE_TYPES)
if store_type in store_types:
if scene_types:
if (('All' in scene_types) or bool(set(scif_scene_types) & set(scene_types))) :
if not ('All' in scene_types):
general_filters['template_name'] = scene_types
if kpi_type == Const.SOVI:
relevant_template = self.templates[kpi_type]
relevant_template = relevant_template[relevant_template[Const.KPI_NAME] == kpi_name]
if relevant_template["numerator param 1"].all() and relevant_template["denominator param"].all():
function = self.get_kpi_function(kpi_type)
for i, kpi_line in relevant_template.iterrows():
result, score = function(kpi_line, general_filters)
else:
pass
else:
pass
@staticmethod
def does_exist(kpi_line, column_name):
"""
checks if kpi_line has values in this column, and if it does - returns a list of these values
:param kpi_line: line from template
:param column_name: str
:return: list of values if there are, otherwise None
"""
if column_name in kpi_line.keys() and kpi_line[column_name] != "":
cell = kpi_line[column_name]
if type(cell) in [int, float]:
return [cell]
elif type(cell) in [unicode, str]:
return cell.split(", ")
return None
@staticmethod
def does_exist_store(kpi_line, column_name):
"""
checks if kpi_line has values in this column, and if it does - returns a list of these values
:param kpi_line: line from template
:param column_name: str
:return: list of values if there are, otherwise None
"""
if column_name in kpi_line.keys() and kpi_line[column_name] != "":
cell = kpi_line[column_name]
if type(cell) in [int, float]:
return [cell]
elif type(cell) in [unicode, str]:
return cell.split(",")
return None
def calculate_sos(self, kpi_line, general_filters):
kpi_name = kpi_line[Const.KPI_NAME]
den_type = kpi_line[Const.DEN_TYPES_1]
den_value = kpi_line[Const.DEN_VALUES_1].split(',')
num_type = kpi_line[Const.NUM_TYPES_1]
num_value = kpi_line[Const.NUM_VALUES_1].split(',')
general_filters[den_type] = den_value
sos_filters = {num_type : num_value}
if kpi_line[Const.NUM_TYPES_2]:
num_type_2 = kpi_line[Const.NUM_TYPES_2]
num_value_2 = kpi_line[Const.NUM_VALUES_2].split(',')
sos_filters[num_type_2] = num_value_2
sos_value = self.sos.calculate_share_of_shelf(sos_filters, **general_filters)
# sos_value *= 100
sos_value = round(sos_value, 2)
score = self.get_score_from_range(kpi_name, sos_value)
manufacturer_products = self.all_products[
self.all_products['manufacturer_name'] == num_value[0]].iloc[0]
manufacturer_fk = manufacturer_products["manufacturer_fk"]
all_products = self.all_products[
self.all_products['category'] == den_value[0]].iloc[0]
category_fk = all_products["category_fk"]
numerator_res, denominator_res = self.get_numerator_and_denominator(sos_filters, **general_filters)
self.common.write_to_db_result_new_tables(fk = 1,
numerator_id=manufacturer_fk,
numerator_result= numerator_res,
denominator_id=category_fk,
denominator_result= denominator_res,
result=sos_value,
score= score,
score_after_actions= score)
return sos_value, score
def get_score_from_range(self, kpi_name, sos_value):
store_type = str(self.store_info["store_type"].iloc[0])
self.score_templates[store_type] = self.score_templates[store_type].replace(kpi_name, kpi_name.encode("utf-8"))
score_range = self.score_templates[store_type].query('Kpi == "' + str(kpi_name.encode("utf-8")) +
'" & Low <= ' + str(sos_value) +
' & High >= ' + str(sos_value)+'')
score = score_range['Score'].iloc[0]
return score
def get_kpi_function(self, kpi_type):
"""
transfers every kpi to its own function .encode('utf-8')
:param kpi_type: value from "sheet" column in the main sheet
:return: function
"""
if kpi_type == Const.SOVI:
return self.calculate_sos
else:
Log.warning("The value '{}' in column sheet in the template is not recognized".format(kpi_type))
return None
@staticmethod
def round_result(result):
return round(result, 3)
def get_numerator_and_denominator(self, sos_filters=None, include_empty=False, **general_filters):
if include_empty == self.EXCLUDE_EMPTY and 'product_type' not in sos_filters.keys() + general_filters.keys():
general_filters['product_type'] = (self.EMPTY, self.EXCLUDE_FILTER)
pop_filter = self.toolbox.get_filter_condition(self.scif, **general_filters)
subset_filter = self.toolbox.get_filter_condition(self.scif, **sos_filters)
try:
pop = self.scif
filtered_population = pop[pop_filter]
if filtered_population.empty:
return 0,0
else:
subset_population = filtered_population[subset_filter]
# ratio = TBox.calculate_ratio_sum_field_in_rows(filtered_population, subset_population, Fd.FACINGS)
df = filtered_population
subset_df = subset_population
sum_field = Fd.FACINGS
try:
Validation.is_empty_df(df)
Validation.is_empty_df(subset_df)
Validation.is_subset(df, subset_df)
Validation.df_columns_equality(df, subset_df)
Validation.validate_columns_exists(df, [sum_field])
Validation.validate_columns_exists(subset_df, [sum_field])
Validation.is_none(sum_field)
except Exception, e:
msg = "Data verification failed: {}.".format(e)
# raise Exception(msg)
default_value = 0
numerator = TBox.calculate_frame_column_sum(subset_df, sum_field, default_value)
denominator = TBox.calculate_frame_column_sum(df, sum_field, default_value)
return numerator, denominator
except Exception as e:
Log.error(e.message)
return True
def commit_results(self):
insert_queries = self.merge_insert_queries(self.kpi_results_new_tables_queries)
self.rds_conn.disconnect_rds()
self.rds_conn.connect_rds()
cur = self.rds_conn.db.cursor()
delete_query = SOLARBRQueries.get_delete_session_results_query(self.session_uid, self.session_id)
cur.execute(delete_query)
for query in insert_queries:
cur.execute(query)
self.rds_conn.db.commit()
self.rds_conn.disconnect_rds()
@staticmethod
def merge_insert_queries(insert_queries):
query_groups = {}
for query in insert_queries:
static_data, inserted_data = query.split('VALUES ')
if static_data not in query_groups:
query_groups[static_data] = []
query_groups[static_data].append(inserted_data)
merged_queries = []
for group in query_groups:
merged_queries.append('{0} VALUES {1}'.format(group, ',\n'.join(query_groups[group])))
return merged_queries