class Shared():
def __init__(self, data_provider, output):
self.data_provider = data_provider
self.output = output
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.result_values = self.ps_data_provider.get_result_values(
).set_index('value')['pk'].to_dict()
@staticmethod
def sos_with_num_and_dem(kpi_line, num_scif, den_scif, facings_field):
try:
Validation.is_empty_df(den_scif)
Validation.df_columns_equality(den_scif, num_scif)
Validation.is_subset(den_scif, num_scif)
except Exception, e:
msg = "Data verification failed: {}.".format(e)
# raise Exception(msg)
# print(msg)
return None, None, None
den = den_scif[facings_field].sum()
try:
Validation.is_empty_df(num_scif)
except Exception as e:
return (0, 0, den)
num = num_scif[facings_field].sum()
if den:
ratio = round((num / float(den)) * 100, 2)
else:
ratio = 0
return ratio, num, den
class MSCToolBox:
def __init__(self, data_provider, output, common_db):
self.output = output
self.data_provider = 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.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.scif = self.scif[self.scif['product_type'] != "Irrelevant"]
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.templates = {}
self.result_values = self.ps_data_provider.get_result_values()
for sheet in Const.SHEETS:
self.templates[sheet] = pd.read_excel(Const.TEMPLATE_PATH,
sheetname=sheet).fillna('')
self.common_db = common_db
self.region = self.store_info['region_name'].iloc[0]
self.store_type = self.store_info['store_type'].iloc[0]
self.manufacturer_fk = Const.MANUFACTURER_FK
# main functions:
def main_calculation(self, *args, **kwargs):
"""
This function gets all the scene results from the SceneKPI, after that calculates every session's KPI,
and in the end it calls "filter results" to choose every KPI and scene and write the results in DB.
"""
main_template = self.templates[Const.KPIS]
for i, main_line in main_template.iterrows():
relevant_store_types = self.does_exist(main_line, Const.STORE_TYPE)
if relevant_store_types and self.store_type not in relevant_store_types:
continue
self.calculate_main_kpi(main_line)
if len(self.common_db.kpi_results) > 0:
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(Const.MSC)
self.common_db.write_to_db_result(kpi_fk,
numerator_id=1,
denominator_id=self.store_id,
result=1,
identifier_result=Const.MSC,
should_enter=True)
return
def calculate_main_kpi(self, main_line):
"""
This function gets a line from the main_sheet, transfers it to the match function, and checks all of the
KPIs in the same name in the match sheet.
:param main_line: series from the template of the main_sheet.
"""
kpi_name = main_line[Const.KPI_NAME]
relevant_scif = self.scif
scene_types = self.does_exist(main_line, Const.SCENE_TYPE)
if scene_types:
relevant_scif = relevant_scif[relevant_scif['template_name'].isin(
scene_types)]
result = self.calculate_kpi_by_type(main_line, relevant_scif)
# self.write_to_session_level(kpi_name=kpi_name, result=result)
return result
def calculate_kpi_by_type(self, main_line, filtered_scif):
"""
the function calculates all the kpis
:param main_line: one kpi line from the main template
:param filtered_scif:
:return: boolean, but it can be None if we want not to write it in DB
"""
kpi_type = main_line[Const.KPI_TYPE]
relevant_template = self.templates[kpi_type]
relevant_template = relevant_template[relevant_template[Const.KPI_NAME]
== main_line[Const.KPI_NAME]]
kpi_function = self.get_kpi_function(kpi_type)
return self.calculate_specific_kpi(relevant_template, filtered_scif,
kpi_function)
@staticmethod
def calculate_specific_kpi(relevant_template,
filtered_scif,
kpi_function,
target=None):
"""
checks if the passed lines are more than target
:param relevant_template: specific template filtered with the specific kpi lines
:param filtered_scif:
:param target: integer
:param kpi_function: specific function for the calculation
:return: boolean, but it can be None if we want not to write it in DB
"""
passed_counter = 0
for i, kpi_line in relevant_template.iterrows():
answer = kpi_function(kpi_line, filtered_scif)
if answer:
passed_counter += 1
elif answer is None:
return None
return passed_counter >= target
# facings calculations
def calculate_facings(self, kpi_line, relevant_scif):
if not self.check_activation_status(kpi_line, relevant_scif):
return
numerator_param = kpi_line[Const.NUMERATOR_TYPE]
numerator_values = self.does_exist(kpi_line, Const.NUMERATOR_VALUE)
denominator_param = kpi_line[Const.DENOMINATOR_TYPE]
if denominator_param:
denominator_values = self.does_exist(kpi_line,
Const.DENOMINATOR_VALUE)
denominator_scif = relevant_scif[
relevant_scif[denominator_param].isin(denominator_values)]
else:
denominator_scif = relevant_scif
excluded_param = kpi_line[Const.EXCLUDED_TYPE]
if excluded_param:
excluded_values = self.does_exist(kpi_line, Const.EXCLUDED_VALUE)
denominator_scif = denominator_scif[
~denominator_scif[excluded_param].isin(excluded_values)]
numerator_scif = denominator_scif[
denominator_scif[numerator_param].isin(numerator_values)]
numerator_result = numerator_scif['facings'].sum()
denominator_result = denominator_scif['facings'].sum()
if denominator_result > 0:
sos_value = numerator_result / float(denominator_result)
else:
sos_value = 0
if not self.check_activation_threshold(kpi_line, sos_value):
return
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(
kpi_line[Const.KPI_NAME])
self.common_db.write_to_db_result(
kpi_fk,
numerator_id=self.manufacturer_fk,
numerator_result=numerator_result,
denominator_id=self.store_id,
denominator_result=denominator_result,
result=sos_value * 100,
identifier_parent=Const.MSC,
should_enter=True)
return
def check_activation_status(self, kpi_line, relevant_scif):
"""
This function checks to see whether or not the KPI has an activation parameter and value combo defined.
If it does, the function makes sure that ALL values are present
:param kpi_line:
:param relevant_scif:
:return:
"""
try:
activation_param = kpi_line[Const.ACTIVATION_TYPE]
except KeyError:
activation_param = None
if activation_param:
# get activation parameter columns and iterate over them
for parameter_column in [
col for col in kpi_line.keys()
if Const.ACTIVATION_TYPE in col
]:
if kpi_line[
parameter_column]: # check to make sure this kpi has this activation param
# get the corresponding value column, e.g. 'activation_value 2' for 'activation_type 2'
value_column = parameter_column.replace(
Const.ACTIVATION_TYPE, Const.ACTIVATION_VALUE)
# get the values for the value column
values = self.does_exist(kpi_line, value_column)
# filter the relevant_scif for these values
relevant_scif = relevant_scif[relevant_scif[
kpi_line[parameter_column]].isin(values)]
# verify all of the main activation values are a part of the relevant_scif
activation_value = self.does_exist(kpi_line,
Const.ACTIVATION_VALUE)
# return true if all values are present, else false
return set(activation_value).issubset(
set(relevant_scif[activation_param].tolist()))
else:
# no activation for this KPI? return true
return True
def check_activation_threshold(self, kpi_line, sos_value):
threshold = self.does_exist(kpi_line, Const.THRESHOLD)
if threshold:
return sos_value > threshold
else:
return True
# availability calculations
def calculate_availability(self, kpi_line, relevant_scif):
"""
checks if all the lines in the availability sheet passes the KPI (there is at least one product
in this relevant scif that has the attributes).
:param relevant_scif: filtered scif
:param minimum_facings: minimum facings required to pass
:param kpi_line: line from the availability sheet
:return: boolean
"""
filtered_scif = self.filter_scif_availability(kpi_line, relevant_scif)
minimum_facings = kpi_line[Const.MINIMUM_FACINGS]
availability = filtered_scif[
filtered_scif['facings'] > 0]['facings'].count() >= minimum_facings
# result = self.ps_data_provider.get_pks_of_result(
# Const.PASS) if availability else self.ps_data_provider.get_pks_of_result(Const.FAIL)
result = 100 if availability else 0
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(
kpi_line[Const.KPI_NAME])
self.common_db.write_to_db_result(kpi_fk,
numerator_id=self.manufacturer_fk,
denominator_id=self.store_id,
result=result,
identifier_parent=Const.MSC,
should_enter=True)
return availability
def calculate_double_availability(self, kpi_line, relevant_scif):
group_1_scif = self.filter_scif_availability(kpi_line,
relevant_scif,
group=1)
group_1_minimum_facings = kpi_line[Const.GROUP1_MINIMUM_FACINGS]
if not group_1_scif['facings'].sum() >= group_1_minimum_facings:
return False
group_2_scif = self.filter_scif_availability(kpi_line,
relevant_scif,
group=2)
group_2_minimum_facings = kpi_line[Const.GROUP2_MINIMUM_FACINGS]
availability = group_2_scif['facings'].sum() >= group_2_minimum_facings
# result = self.ps_data_provider.get_pks_of_result(
# Const.PASS) if availability else self.ps_data_provider.get_pks_of_result(Const.FAIL)
result = 100 if availability else 0
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(
kpi_line[Const.KPI_NAME])
self.common_db.write_to_db_result(kpi_fk,
numerator_id=self.manufacturer_fk,
denominator_id=self.store_id,
result=result,
identifier_parent=Const.MSC,
should_enter=True)
return availability
def filter_scif_availability(self, kpi_line, relevant_scif, group=None):
"""
calls filter_scif_specific for every column in the template of availability
:param kpi_line:
:param relevant_scif:
:param group: used to indicate group for double availability
:return:
"""
try:
excluded_param = kpi_line[Const.EXCLUDED_TYPE]
except KeyError:
excluded_param = None
if excluded_param:
excluded_values = self.does_exist(kpi_line, Const.EXCLUDED_VALUE)
relevant_scif = relevant_scif[~relevant_scif[excluded_param].
isin(excluded_values)]
if group == 1:
names_of_columns = {
Const.GROUP1_BRAND: "brand_name",
Const.MANUFACTURER: "manufacturer_name"
}
elif group == 2:
names_of_columns = {
Const.GROUP2_BRAND: "brand_name",
Const.MANUFACTURER: "manufacturer_name"
}
else:
names_of_columns = {
Const.MANUFACTURER: "manufacturer_name",
Const.BRAND: "brand_name",
Const.ATT1: "att1",
Const.ATT3: "att3",
Const.SIZE: "size",
Const.SUB_PACKAGES: "number_of_sub_packages"
}
for name in names_of_columns:
relevant_scif = self.filter_scif_specific(relevant_scif, kpi_line,
name,
names_of_columns[name])
return relevant_scif
def filter_scif_specific(self, relevant_scif, kpi_line, name_in_template,
name_in_scif):
"""
takes scif and filters it from the template
:param relevant_scif: the current filtered scif
:param kpi_line: line from one sheet (availability for example)
:param name_in_template: the column name in the template
:param name_in_scif: the column name in SCIF
:return:
"""
values = self.does_exist(kpi_line, name_in_template)
if values:
if name_in_scif in Const.NUMERIC_VALUES_TYPES:
values = [float(x) for x in values]
return relevant_scif[relevant_scif[name_in_scif].isin(values)]
return relevant_scif
# helper functions
def get_kpi_function(self, kpi_type):
"""
transfers every kpi to its own function
:param kpi_type: value from "sheet" column in the main sheet
:return: function
"""
if kpi_type == Const.AVAILABILITY:
return self.calculate_availability
elif kpi_type == Const.DOUBLE_AVAILABILITY:
return self.calculate_double_availability
elif kpi_type == Const.FACINGS:
return self.calculate_facings
elif kpi_type == Const.SHARE_OF_DISPLAYS:
return self.calculate_facings
elif kpi_type == Const.DISPLAY_PRESENCE:
return self.calculate_facings
else:
Log.warning(
"The value '{}' in column sheet in the template is not recognized"
.format(kpi_type))
return None
@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 [x.strip() for x in cell.split(",")]
return None
class CMAToolBox:
EXCLUDE_FILTER = 0
INCLUDE_FILTER = 1
CONTAIN_FILTER = 2
def __init__(self, data_provider, output, common_db2):
self.output = output
self.data_provider = data_provider
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.manufacturer_fk = 1
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.united_scenes = self.get_united_scenes(
) # we don't need to check scenes without United products
self.survey = Survey(self.data_provider, self.output)
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.sos = SOS(self.data_provider, self.output)
self.templates = {}
self.common_db = Common(self.data_provider, SUB_PROJECT)
self.common_db2 = common_db2
self.result_values = self.ps_data_provider.get_result_values()
self.region = self.store_info['region_name'].iloc[0]
self.store_type = self.store_info['store_type'].iloc[0]
self.program = self.store_info['additional_attribute_14'].iloc[0]
self.sales_center = self.store_info['additional_attribute_5'].iloc[0]
if self.store_type in STORE_TYPES:
self.store_type = STORE_TYPES[self.store_type]
self.store_attr = self.store_info['additional_attribute_15'].iloc[0]
self.kpi_static_data = self.common_db.get_kpi_static_data()
self.ignore_stacking = False
self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack'
self.total_score = 0
self.total_count = 0
for sheet in Const.SHEETS_CMA:
self.templates[sheet] = pd.read_excel(TEMPLATE_PATH,
sheetname=sheet).fillna('')
self.tools = Shared(self.data_provider, self.output)
# main functions:
def main_calculation(self, *args, **kwargs):
"""
This function gets all the scene results from the SceneKPI, after that calculates every session's KPI,
and in the end it calls "filter results" to choose every KPI and scene and write the results in DB.
"""
main_template = self.templates[Const.KPIS]
if self.region in Const.REGIONS:
for i, main_line in main_template.iterrows():
store_type = self.does_exist(main_line, Const.STORE_TYPE)
if store_type is None or self.store_type in self.does_exist(
main_line, Const.STORE_TYPE):
self.calculate_main_kpi(main_line)
kpi_fk = self.common_db2.get_kpi_fk_by_kpi_name(SUB_PROJECT)
result = 0
if self.total_count:
result = self.total_score * 100.0 / self.total_count
self.common_db2.write_to_db_result(
fk=kpi_fk,
result=result,
numerator_result=self.total_score,
numerator_id=self.manufacturer_fk,
denominator_result=self.total_count,
denominator_id=self.store_id,
identifier_result=self.common_db2.get_dictionary(
parent_name=SUB_PROJECT))
self.write_to_db_result(self.common_db.get_kpi_fk_by_kpi_name(
SUB_PROJECT, 1),
score=self.total_score,
level=1)
def calculate_main_kpi(self, main_line):
"""
This function gets a line from the main_sheet, transfers it to the match function, and checks all of the
KPIs in the same name in the match sheet.
:param main_line: series from the template of the main_sheet.
"""
kpi_name = main_line[Const.KPI_NAME]
kpi_type = main_line[Const.TYPE]
relevant_scif = self.scif[self.scif['scene_id'].isin(
self.united_scenes)]
scene_types = self.does_exist(main_line, Const.SCENE_TYPE)
result = score = target = None
general_filters = {}
if scene_types:
relevant_scif = relevant_scif[relevant_scif['template_name'].isin(
scene_types)]
general_filters['template_name'] = scene_types
scene_groups = self.does_exist(main_line, Const.TEMPLATE_GROUP)
if scene_groups:
relevant_scif = relevant_scif[relevant_scif['template_group'].isin(
scene_groups)]
general_filters['template_group'] = scene_groups
if kpi_type == Const.SOS:
isnt_dp = True if self.store_attr != Const.DP and main_line[
Const.STORE_ATTRIBUTE] == Const.DP else False
relevant_template = self.templates[kpi_type]
relevant_template = relevant_template[relevant_template[
Const.KPI_NAME] == kpi_name]
kpi_function = self.get_kpi_function(kpi_type)
for i, kpi_line in relevant_template.iterrows():
result, score, target = kpi_function(kpi_line, relevant_scif,
isnt_dp, general_filters)
else:
pass
self.total_count += 1
if score > 0:
self.total_score += 1
if isinstance(result, tuple):
self.write_to_all_levels(kpi_name=kpi_name,
result=result[0],
score=score,
target=target,
num=result[1],
den=result[2])
else:
self.write_to_all_levels(kpi_name=kpi_name,
result=result,
score=score,
target=target)
# write in DF:
def write_to_all_levels(self,
kpi_name,
result,
score,
target=None,
num=None,
den=None):
"""
Writes the final result in the "all" DF, add the score to the red score and writes the KPI in the DB
:param kpi_name: str
:param result: int
:param display_text: str
:param weight: int/float
:param scene_fk: for the scene's kpi
:param reuse_scene: this kpi can use scenes that were used
"""
# result_dict = {Const.KPI_NAME: kpi_name, Const.RESULT: result, Const.SCORE: score, Const.THRESHOLD: target}
# self.all_results = self.all_results.append(result_dict, ignore_index=True)
self.write_to_db(kpi_name,
score,
result=result,
target=target,
num=num,
den=den)
# survey:
def calculate_survey_specific(self,
kpi_line,
relevant_scif=None,
isnt_dp=None):
"""
returns a survey line if True or False
:param kpi_line: line from the survey sheet
:param relevant_scif:
:param isnt_dp:
:return: True or False - if the question gets the needed answer
"""
question = kpi_line[Const.Q_TEXT]
if not question:
question_id = kpi_line[Const.Q_ID]
if question_id == "":
Log.warning(
"The template has a survey question without ID or text")
return False
question = ('question_fk', int(question_id))
answers = kpi_line[Const.ACCEPTED_ANSWER].split(',')
min_answer = None if kpi_line[Const.REQUIRED_ANSWER] == '' else True
for answer in answers:
if self.survey.check_survey_answer(survey_text=question,
target_answer=answer,
min_required_answer=min_answer):
return True
return False
# availability:
def calculate_availability_with_same_pack(self, relevant_template,
relevant_scif, isnt_dp):
"""
checks if all the lines in the availability sheet passes the KPI, AND if all of these filtered scif has
at least one common product that has the same size and number of sub_packages.
:param relevant_template: all the match lines from the availability sheet.
:param relevant_scif: filtered scif
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we shouldn't calculate
DP lines
:return: boolean
"""
packages = None
for i, kpi_line in relevant_template.iterrows():
if isnt_dp and kpi_line[Const.MANUFACTURER] in Const.DP_MANU:
continue
filtered_scif = self.filter_scif_availability(
kpi_line, relevant_scif)
filtered_scif = filtered_scif.fillna("NAN")
target = kpi_line[Const.TARGET]
sizes = filtered_scif['size'].tolist()
sub_packages_nums = filtered_scif['number_of_sub_packages'].tolist(
)
cur_packages = set(zip(sizes, sub_packages_nums))
if packages is None:
packages = cur_packages
else:
packages = cur_packages & packages
if len(packages) == 0:
return False
if filtered_scif[
filtered_scif['facings'] > 0]['facings'].count() < target:
return False
if len(packages) > 1:
return False
return True
def calculate_availability(self, kpi_line, relevant_scif, isnt_dp):
"""
checks if all the lines in the availability sheet passes the KPI (there is at least one product
in this relevant scif that has the attributes).
:param relevant_scif: filtered scif
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we shouldn't calculate
DP lines
:param kpi_line: line from the availability sheet
:return: boolean
"""
if isnt_dp and kpi_line[Const.MANUFACTURER] in Const.DP_MANU:
return True
filtered_scif = self.filter_scif_availability(kpi_line, relevant_scif)
target = kpi_line[Const.TARGET]
return filtered_scif[
filtered_scif['facings'] > 0]['facings'].count() >= target
def filter_scif_specific(self, relevant_scif, kpi_line, name_in_template,
name_in_scif):
"""
takes scif and filters it from the template
:param relevant_scif: the current filtered scif
:param kpi_line: line from one sheet (availability for example)
:param name_in_template: the column name in the template
:param name_in_scif: the column name in SCIF
:return:
"""
values = self.does_exist(kpi_line, name_in_template)
if values:
if name_in_scif in Const.NUMERIC_VALUES_TYPES:
values = [float(x) for x in values]
return relevant_scif[relevant_scif[name_in_scif].isin(values)]
return relevant_scif
def filter_scif_availability(self, kpi_line, relevant_scif):
"""
calls filter_scif_specific for every column in the template of availability
:param kpi_line:
:param relevant_scif:
:return:
"""
names_of_columns = {
Const.MANUFACTURER: "manufacturer_name",
Const.BRAND: "brand_name",
Const.TRADEMARK: "att2",
Const.SIZE: "size",
Const.NUM_SUB_PACKAGES: "number_of_sub_packages",
}
for name in names_of_columns:
relevant_scif = self.filter_scif_specific(relevant_scif, kpi_line,
name,
names_of_columns[name])
return relevant_scif
# SOS:
def calculate_sos(self, kpi_line, relevant_scif, isnt_dp, general_filters):
"""
calculates SOS line in the relevant scif.
:param kpi_line: line from SOS sheet.
:param relevant_scif: filtered scif.
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter
all the DP products out of the numerator.
:return: boolean
"""
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(',')
target = self.get_sos_targets(kpi_name)
general_filters[den_type] = den_value
if kpi_line[Const.DEN_TYPES_2]:
den_type_2 = kpi_line[Const.DEN_TYPES_2]
den_value_2 = kpi_line[Const.DEN_VALUES_2].split(',')
general_filters[den_type_2] = den_value_2
sos_filters = {num_type: num_value}
if isnt_dp:
sos_filters['manufacturer_name'] = (Const.DP_MANU, 0)
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
num_scif = relevant_scif[self.get_filter_condition(
relevant_scif, **sos_filters)]
den_scif = relevant_scif[self.get_filter_condition(
relevant_scif, **general_filters)]
sos_value, num, den = self.tools.sos_with_num_and_dem(
kpi_line, num_scif, den_scif, self.facings_field)
# sos_value = self.sos.calculate_share_of_shelf(sos_filters, **general_filters)
# sos_value *= 100
# sos_value = round(sos_value, 2)
if target:
target *= 100
score = 1 if sos_value >= target else 0
else:
score = 0
target = 0
return (sos_value, num, den), score, target
# SOS majority:
def get_sos_targets(self, kpi_name):
targets_template = self.templates[Const.TARGETS]
store_targets = targets_template.loc[
(targets_template['program'] == self.program)
& (targets_template['channel'] == self.store_type)]
filtered_targets_to_kpi = store_targets.loc[
targets_template['KPI name'] == kpi_name]
if not filtered_targets_to_kpi.empty:
target = filtered_targets_to_kpi[Const.TARGET].values[0]
else:
target = None
return target
# return False
def calculate_sos_maj(self, kpi_line, relevant_scif, isnt_dp):
"""
calculates SOS majority line in the relevant scif. Filters the denominator and sends the line to the
match function (majority or dominant)
:param kpi_line: line from SOS majority sheet.
:param relevant_scif: filtered scif.
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter
all the DP products out of the numerator (and the denominator of the dominant part).
:return: boolean
"""
kpi_name = kpi_line[Const.KPI_NAME]
if kpi_line[Const.EXCLUSION_SHEET] == Const.V:
exclusion_sheet = self.templates[Const.SKU_EXCLUSION]
relevant_exclusions = exclusion_sheet[exclusion_sheet[
Const.KPI_NAME] == kpi_name]
for i, exc_line in relevant_exclusions.iterrows():
relevant_scif = self.exclude_scif(exc_line, relevant_scif)
relevant_scif = relevant_scif[relevant_scif['product_type'] != "Empty"]
den_type = kpi_line[Const.DEN_TYPES_1]
den_value = kpi_line[Const.DEN_VALUES_1]
relevant_scif = self.filter_by_type_value(relevant_scif, den_type,
den_value)
den_type = kpi_line[Const.DEN_TYPES_2]
den_value = kpi_line[Const.DEN_VALUES_2]
relevant_scif = self.filter_by_type_value(relevant_scif, den_type,
den_value)
if kpi_line[Const.MAJ_DOM] == Const.MAJOR:
answer = self.calculate_majority_part(kpi_line, relevant_scif,
isnt_dp)
elif kpi_line[Const.MAJ_DOM] == Const.DOMINANT:
answer = self.calculate_dominant_part(kpi_line, relevant_scif,
isnt_dp)
else:
Log.warning("SOS majority does not know '{}' part".format(
kpi_line[Const.MAJ_DOM]))
answer = False
return answer
def calculate_majority_part(self, kpi_line, relevant_scif, isnt_dp):
"""
filters the numerator and checks if the SOS is bigger than 50%.
:param kpi_line: line from SOS majority sheet.
:param relevant_scif: filtered scif.
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter
all the DP products out of the numerator.
:return: boolean
"""
num_type = kpi_line[Const.NUM_TYPES_1]
num_value = kpi_line[Const.NUM_VALUES_1]
num_scif = self.filter_by_type_value(relevant_scif, num_type,
num_value)
num_type = kpi_line[Const.NUM_TYPES_2]
num_value = kpi_line[Const.NUM_VALUES_2]
num_scif = self.filter_by_type_value(num_scif, num_type, num_value)
if num_scif.empty:
return None
if isnt_dp:
num_scif = num_scif[~(
num_scif['manufacturer_name'].isin(Const.DP_MANU))]
target = Const.MAJORITY_TARGET
return num_scif['facings'].sum() / relevant_scif['facings'].sum(
) >= target
def calculate_dominant_part(self, kpi_line, relevant_scif, isnt_dp):
"""
filters the numerator and checks if the given value in the given type is the one with the most facings.
:param kpi_line: line from SOS majority sheet.
:param relevant_scif: filtered scif.
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter
all the DP products out.
:return: boolean
"""
if isnt_dp:
relevant_scif = relevant_scif[~(
relevant_scif['manufacturer_name'].isin(Const.DP_MANU))]
type_name = self.get_column_name(kpi_line[Const.NUM_TYPES_1],
relevant_scif)
values = str(kpi_line[Const.NUM_VALUES_1]).split(', ')
if type_name in Const.NUMERIC_VALUES_TYPES:
values = [float(x) for x in values]
max_facings, needed_one = 0, 0
values_type = relevant_scif[type_name].unique().tolist()
if None in values_type:
values_type.remove(None)
current_sum = relevant_scif[
relevant_scif[type_name].isnull()]['facings'].sum()
if current_sum > max_facings:
max_facings = current_sum
for value in values_type:
current_sum = relevant_scif[relevant_scif[type_name] ==
value]['facings'].sum()
if current_sum > max_facings:
max_facings = current_sum
if value in values:
needed_one += current_sum
return needed_one >= max_facings
def get_filter_condition(self, df, **filters):
"""
:param df: The data frame to be filters.
:param filters: These are the parameters which the data frame is filtered by.
Every parameter would be a tuple of the value and an include/exclude flag.
INPUT EXAMPLE (1): manufacturer_name = ('Diageo', DIAGEOAUPNGAMERICAGENERALToolBox.INCLUDE_FILTER)
INPUT EXAMPLE (2): manufacturer_name = 'Diageo'
:return: a filtered Scene Item Facts data frame.
"""
if not filters:
return df['pk'].apply(bool)
if self.facings_field in df.keys():
filter_condition = (df[self.facings_field] > 0)
else:
filter_condition = None
for field in filters.keys():
if field in df.keys():
if isinstance(filters[field], tuple):
value, exclude_or_include = filters[field]
else:
value, exclude_or_include = filters[
field], self.INCLUDE_FILTER
if not value:
continue
if not isinstance(value, list):
value = [value]
if exclude_or_include == self.INCLUDE_FILTER:
condition = (df[field].isin(value))
elif exclude_or_include == self.EXCLUDE_FILTER:
condition = (~df[field].isin(value))
elif exclude_or_include == self.CONTAIN_FILTER:
condition = (df[field].str.contains(value[0], regex=False))
for v in value[1:]:
condition |= df[field].str.contains(v, regex=False)
else:
continue
if filter_condition is None:
filter_condition = condition
else:
filter_condition &= condition
else:
Log.warning('field {} is not in the Data Frame'.format(field))
return filter_condition
# helpers:
@staticmethod
def get_column_name(field_name, df):
"""
checks what the real field name in DttFrame is (if it exists in the DF or exists in the "converter" sheet).
:param field_name: str
:param df: scif/products
:return: real column name (if exists)
"""
if field_name in df.columns:
return field_name
return None
def filter_by_type_value(self, relevant_scif, type_name, value):
"""
filters scif with the type and value
:param relevant_scif: current filtered scif
:param type_name: str (from the template)
:param value: str
:return: new scif
"""
if type_name == "":
return relevant_scif
values = value.split(', ')
new_type_name = self.get_column_name(type_name, relevant_scif)
if not new_type_name:
print "There is no field '{}'".format(type_name)
return relevant_scif
if new_type_name in Const.NUMERIC_VALUES_TYPES:
values = [float(x) for x in values]
return relevant_scif[relevant_scif[new_type_name].isin(values)]
@staticmethod
def exclude_scif(exclude_line, relevant_scif):
"""
filters products out of the scif
:param exclude_line: line from the exclusion sheet
:param relevant_scif: current filtered scif
:return: new scif
"""
exclude_products = exclude_line[Const.PRODUCT_EAN].split(', ')
return relevant_scif[~(
relevant_scif['product_ean_code'].isin(exclude_products))]
@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]:
if ", " in cell:
return cell.split(", ")
else:
return cell.split(',')
return None
def get_kpi_function(self, kpi_type):
"""
transfers every kpi to its own function
:param kpi_type: value from "sheet" column in the main sheet
:return: function
"""
if kpi_type == Const.SURVEY:
return self.calculate_survey_specific
elif kpi_type == Const.AVAILABILITY:
return self.calculate_availability
elif kpi_type == Const.SOS:
return self.calculate_sos
elif kpi_type == Const.SOS_MAJOR:
return self.calculate_sos_maj
else:
Log.warning(
"The value '{}' in column sheet in the template is not recognized"
.format(kpi_type))
return None
def get_united_scenes(self):
return self.scif[self.scif['United Deliver'] ==
'Y']['scene_id'].unique().tolist()
def get_pks_of_result(self, result):
"""
converts string result to its pk (in static.kpi_result_value)
:param result: str
:return: int
"""
pk = self.result_values[self.result_values['value'] ==
result]['pk'].iloc[0]
return pk
def write_to_db(self,
kpi_name,
score,
result=None,
target=None,
num=None,
den=None):
"""
writes result in the DB
:param kpi_name: str
:param score: float
:param result: str
:param target: int
"""
if target and score == 0:
delta = den * (target / 100) - num
else:
delta = 0
score_value = Const.PASS if score == 1 else Const.FAIL
score = self.get_pks_of_result(score_value)
kpi_fk = self.common_db2.get_kpi_fk_by_kpi_type(SUB_PROJECT + " " +
kpi_name)
self.common_db2.write_to_db_result(
fk=kpi_fk,
result=result,
score=score,
should_enter=True,
target=target,
numerator_result=num,
denominator_result=den,
weight=delta,
numerator_id=Const.MANUFACTURER_FK,
denominator_id=self.store_id,
identifier_parent=self.common_db2.get_dictionary(
parent_name=SUB_PROJECT))
self.write_to_db_result(self.common_db.get_kpi_fk_by_kpi_name(
kpi_name, 2),
score=score,
level=2)
self.write_to_db_result(self.common_db.get_kpi_fk_by_kpi_name(
kpi_name, 3),
score=score,
level=3,
threshold=target,
result=result)
def write_to_db_result(self,
fk,
level,
score,
set_type=Const.SOVI,
**kwargs):
"""
This function creates the result data frame of every KPI (atomic KPI/KPI/KPI set),
and appends the insert SQL query into the queries' list, later to be written to the DB.
"""
if kwargs:
kwargs['score'] = score
attributes = self.create_attributes_dict(fk=fk,
level=level,
**kwargs)
else:
attributes = self.create_attributes_dict(fk=fk,
score=score,
level=level)
if level == self.common_db.LEVEL1:
table = self.common_db.KPS_RESULT
elif level == self.common_db.LEVEL2:
table = self.common_db.KPK_RESULT
elif level == self.common_db.LEVEL3:
table = self.common_db.KPI_RESULT
else:
return
query = insert(attributes, table)
self.common_db.kpi_results_queries.append(query)
def create_attributes_dict(self,
score,
fk=None,
level=None,
display_text=None,
set_type=Const.SOVI,
**kwargs):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
or
you can send dict with all values in kwargs
"""
kpi_static_data = self.kpi_static_data if set_type == Const.SOVI else self.kpi_static_data_integ
if level == self.common_db.LEVEL1:
if kwargs:
kwargs['score'] = score
values = [val for val in kwargs.values()]
col = [col for col in kwargs.keys()]
attributes = pd.DataFrame(values, columns=col)
else:
kpi_set_name = kpi_static_data[kpi_static_data['kpi_set_fk'] ==
fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame(
[(kpi_set_name, self.session_uid, self.store_id,
self.visit_date.isoformat(), format(score, '.2f'), fk)],
columns=[
'kps_name', 'session_uid', 'store_fk', 'visit_date',
'score_1', 'kpi_set_fk'
])
elif level == self.common_db.LEVEL2:
if kwargs:
kwargs['score'] = score
values = [val for val in kwargs.values()]
col = [col for col in kwargs.keys()]
attributes = pd.DataFrame(values, columns=col)
else:
kpi_name = kpi_static_data[kpi_static_data['kpi_fk'] ==
fk]['kpi_name'].values[0].replace(
"'", "\\'")
attributes = pd.DataFrame(
[(self.session_uid, self.store_id,
self.visit_date.isoformat(), fk, kpi_name, score)],
columns=[
'session_uid', 'store_fk', 'visit_date', 'kpi_fk',
'kpk_name', 'score'
])
elif level == self.common_db.LEVEL3:
data = kpi_static_data[kpi_static_data['atomic_kpi_fk'] == fk]
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = kpi_static_data[kpi_static_data['atomic_kpi_fk'] ==
fk]['kpi_set_name'].values[0]
display_text = data['kpi_name'].values[0]
if kwargs:
kwargs = self.add_additional_data_to_attributes(
kwargs, score, kpi_set_name, kpi_fk, fk,
datetime.utcnow().isoformat(), display_text)
values = tuple([val for val in kwargs.values()])
col = [col for col in kwargs.keys()]
attributes = pd.DataFrame([values], columns=col)
else:
attributes = pd.DataFrame(
[(display_text, self.session_uid, kpi_set_name,
self.store_id, self.visit_date.isoformat(),
datetime.utcnow().isoformat(), score, kpi_fk, fk)],
columns=[
'display_text', 'session_uid', 'kps_name', 'store_fk',
'visit_date', 'calculation_time', 'score', 'kpi_fk',
'atomic_kpi_fk'
])
else:
attributes = pd.DataFrame()
return attributes.to_dict()
def add_additional_data_to_attributes(self, kwargs_dict, score,
kpi_set_name, kpi_fk, fk, calc_time,
display_text):
kwargs_dict['score'] = score
kwargs_dict['kps_name'] = kpi_set_name
kwargs_dict['kpi_fk'] = kpi_fk
kwargs_dict['atomic_kpi_fk'] = fk
kwargs_dict['calculation_time'] = calc_time
kwargs_dict['session_uid'] = self.session_uid
kwargs_dict['store_fk'] = self.store_id
kwargs_dict['visit_date'] = self.visit_date.isoformat()
kwargs_dict['display_text'] = display_text
return kwargs_dict
def commit_results(self):
"""
committing the results in both sets
"""
self.common_db.delete_results_data_by_kpi_set()
self.common_db.commit_results_data_without_delete()
class JNJToolBox:
NUMERATOR = 'numerator'
DENOMINATOR = 'denominator'
SP_LOCATION_KPI = 'secondary placement location quality'
SP_LOCATION_QUALITY_KPI = 'secondary placement location visibility quality'
LVL3_HEADERS = [
'assortment_group_fk', 'assortment_fk', 'target', 'product_fk',
'in_store', 'kpi_fk_lvl1', 'kpi_fk_lvl2', 'kpi_fk_lvl3',
'group_target_date', 'assortment_super_group_fk', 'super_group_target'
]
LVL2_HEADERS = [
'assortment_super_group_fk', 'assortment_group_fk', 'assortment_fk',
'target', 'passes', 'total', 'kpi_fk_lvl1', 'kpi_fk_lvl2',
'group_target_date', 'super_group_target'
]
LVL1_HEADERS = [
'assortment_super_group_fk', 'assortment_group_fk',
'super_group_target', 'passes', 'total', 'kpi_fk_lvl1'
]
ASSORTMENT_FK = 'assortment_fk'
ASSORTMENT_GROUP_FK = 'assortment_group_fk'
ASSORTMENT_SUPER_GROUP_FK = 'assortment_super_group_fk'
# local_msl availability
# LOCAL_MSL_AVAILABILITY = 'local_msl'
# LOCAL_MSL_AVAILABILITY_SKU = 'local_msl - SKU'
# TODO: change this
# local_msl availability
LOCAL_MSL_AVAILABILITY = 'Distribution'
LOCAL_MSL_AVAILABILITY_SKU = 'Distribution - SKU'
# jnjanz local msl/oos KPIs
OOS_BY_LOCAL_ASSORT_STORE_KPI = 'OOS_BY_LOCAL_ASSORT_STORE'
OOS_BY_LOCAL_ASSORT_PRODUCT = 'OOS_BY_LOCAL_ASSORT_PRODUCT'
OOS_BY_LOCAL_ASSORT_CATEGORY = 'OOS_BY_LOCAL_ASSORT_CATEGORY'
OOS_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY = 'OOS_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY'
OOS_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY_PRODUCT = 'OOS_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY_PRODUCT'
MSL_BY_LOCAL_ASSORT = 'MSL_BY_LOCAL_ASSORT'
MSL_BY_LOCAL_ASSORT_PRODUCT = 'MSL_BY_LOCAL_ASSORT_PRODUCT'
MSL_BY_LOCAL_ASSORT_CATEGORY = 'MSL_BY_LOCAL_ASSORT_CATEGORY'
MSL_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY = 'MSL_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY'
MSL_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY_PRODUCT = 'MSL_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY_PRODUCT'
# msl availability
MSL_AVAILABILITY = 'MSL'
MSL_AVAILABILITY_SKU = 'MSL - SKU'
JNJ = 'JOHNSON & JOHNSON'
TYPE_SKU = 'SKU'
TYPE_OTHER = 'Other'
SUCCESSFUL = [1, 4]
OTHER = 'Other'
YES = 'Yes'
NO = 'No'
OOS = 'OOS'
DISTRIBUTED = 'DISTRIBUTED'
EXTRA = 'EXTRA'
def __init__(self, data_provider, output, common, exclusive_template):
self.output = output
self.data_provider = data_provider
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.session_id = self.data_provider.session_id
self.products = self.data_provider[Data.PRODUCTS]
self.all_products_i_d = self.data_provider[
Data.ALL_PRODUCTS_INCLUDING_DELETED]
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.templates = self.data_provider[Data.ALL_TEMPLATES]
self.survey_response = self.data_provider[Data.SURVEY_RESPONSES]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.tools = JNJGENERALToolBox(self.data_provider,
self.output,
rds_conn=self.rds_conn)
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.common = common
self.New_kpi_static_data = common.get_new_kpi_static_data()
self.kpi_results_new_tables_queries = []
self.all_products = self.ps_data_provider.get_sub_category(
self.all_products, 'sub_category_local_name')
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_info = self.ps_data_provider.get_ps_store_info(
self.store_info)
self.current_date = datetime.now()
self.labels = self.ps_data_provider.get_labels()
self.products_in_ass = []
self.products_to_ass = pd.DataFrame(
columns=assTemplate.COLUMNS_ASSORTMENT_DEFINITION_SHEET)
self.assortment_policy = pd.DataFrame(
columns=assTemplate.COLUMNS_STORE_ATTRIBUTES_TO_ASSORT)
self.ass_deleted_prod = pd.DataFrame(columns=[
assTemplate.COLUMN_GRANULAR_GROUP, assTemplate.COLUMN_EAN_CODE
])
self.session_category_info = pd.DataFrame()
self.session_products = pd.DataFrame()
self.assortment = Assortment(self.data_provider, self.output,
self.ps_data_provider)
self.products_to_remove = []
self.ignore_from_top = 1
self.start_shelf = 3
self.products_for_ass_new = pd.DataFrame(
columns=['session_id', 'product_fk'])
self.prev_session_products_new_ass = pd.DataFrame()
self.session_category_new_ass = pd.DataFrame()
self.own_manuf_fk = int(
self.data_provider.own_manufacturer.param_value.values[0])
self.kpi_result_values = self.get_kpi_result_values_df()
self.parser = Parser
self.exclusive_template = exclusive_template
self.result_values = self.ps_data_provider.get_result_values()
def get_kpi_result_values_df(self):
query = JNJQueries.get_kpi_result_values()
query_result = pd.read_sql_query(query, self.rds_conn.db)
return query_result
def get_session_products(self, session):
return self.session_products[self.session_products['session_id'] ==
session]
def result_value_pk(self, result):
"""
converts string result to its pk (in static.kpi_result_value)
:param result: str
:return: int
"""
pk = self.result_values[self.result_values['value'] ==
result]["pk"].iloc[0]
return pk
@staticmethod
def split_and_strip(value):
return map(lambda x: x.strip(), value.split(';'))
def reset_scif_and_matches(self):
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS].copy()
self.match_product_in_scene = self.data_provider[Data.MATCHES].copy()
def filter_scif_matches_for_kpi(self, kpi_name):
if not self.exclusive_template.empty:
template_filters = {}
kpi_filters_df = self.exclusive_template[
self.exclusive_template['KPI'] == kpi_name]
if kpi_filters_df.empty:
return
if not kpi_filters_df.empty:
if kpi_filters_df['Exclude1'].values[0]:
template_filters.update({
kpi_filters_df['Exclude1'].values[0]:
(self.split_and_strip(
kpi_filters_df['Value1'].values[0]), 0)
})
if kpi_filters_df['Exclude2'].values[0]:
template_filters.update({
kpi_filters_df['Exclude2'].values[0]:
(self.split_and_strip(
kpi_filters_df['Value2'].values[0]), 0)
})
if 'Exclude3' in kpi_filters_df.columns.values:
if kpi_filters_df['Exclude3'].values[0]:
template_filters.update({
kpi_filters_df['Exclude3'].values[0]:
(self.split_and_strip(
kpi_filters_df['Value3'].values[0]), 0)
})
if 'Exclude4' in kpi_filters_df.columns.values:
if kpi_filters_df['Exclude4'].values[0]:
template_filters.update({
template_filters['Exclude4'].values[0]:
(self.split_and_strip(
template_filters['Value4'].values[0]), 0)
})
filters = self.get_filters_for_scif_and_matches(
template_filters)
self.scif = self.scif[self.tools.get_filter_condition(
self.scif, **filters)]
self.match_product_in_scene = self.match_product_in_scene[
self.tools.get_filter_condition(
self.match_product_in_scene, **filters)]
def get_filters_for_scif_and_matches(self, template_filters):
product_keys = filter(
lambda x: x in self.data_provider[Data.ALL_PRODUCTS].columns.values
.tolist(), template_filters.keys())
scene_keys = filter(
lambda x: x in self.data_provider[Data.ALL_TEMPLATES].columns.
values.tolist(), template_filters.keys())
product_filters = {}
scene_filters = {}
filters_all = {}
for key in product_keys:
product_filters.update({key: template_filters[key]})
for key in scene_keys:
scene_filters.update({key: template_filters[key]})
if product_filters:
product_fks = self.get_fk_from_filters(product_filters)
filters_all.update({'product_fk': product_fks})
if scene_filters:
scene_fks = self.get_scene_fk_from_filters(scene_filters)
filters_all.update({'scene_fk': scene_fks})
return filters_all
def get_fk_from_filters(self, filters):
all_products = self.data_provider.all_products
product_fk_list = all_products[self.tools.get_filter_condition(
all_products, **filters)]
product_fk_list = product_fk_list['product_fk'].unique().tolist()
return product_fk_list
def get_scene_fk_from_filters(self, filters):
scif_data = self.data_provider[Data.SCENE_ITEM_FACTS]
scene_fk_list = scif_data[self.tools.get_filter_condition(
scif_data, **filters)]
scene_fk_list = scene_fk_list['scene_fk'].unique().tolist()
return scene_fk_list
def get_own_manufacturer_skus_in_scif(self):
# Filter scif by own_manufacturer & product_type = 'SKU'
return self.scif[(self.scif.manufacturer_fk == self.own_manuf_fk)
& (self.scif.product_type == "SKU")
& (self.scif["facings"] >
0)]['item_id'].unique().tolist()
def fetch_local_assortment_products(self):
# TODO Fix with real assortment
lvl3_assortment = self.assortment.get_lvl3_relevant_ass()
local_msl_ass_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.LOCAL_MSL_AVAILABILITY]['pk'].drop_duplicates().values[0]
local_msl_ass_sku_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.LOCAL_MSL_AVAILABILITY_SKU]['pk'].drop_duplicates().values[0]
if lvl3_assortment.empty:
return [], pd.DataFrame()
lvl3_assortment = lvl3_assortment[lvl3_assortment['kpi_fk_lvl3'] ==
local_msl_ass_sku_fk]
if lvl3_assortment.empty:
return [], pd.DataFrame()
assortments = lvl3_assortment['assortment_group_fk'].unique()
products_in_ass = []
for assortment in assortments:
current_assortment = lvl3_assortment[
lvl3_assortment['assortment_group_fk'] == assortment]
current_assortment_product_fks = list(
current_assortment[~current_assortment['product_fk'].isna()]
['product_fk'].unique())
products_in_ass.extend(current_assortment_product_fks)
#ignore None if anty
products_in_ass = [
p for p in products_in_ass if not ((p == None) or p == 'None')
]
return products_in_ass, lvl3_assortment
@kpi_runtime()
def local_assortment_hierarchy_per_store_calc(self):
Log.debug("starting local_assortment calc")
self.products_in_ass, lvl3_assortment = self.fetch_local_assortment_products(
)
self.products_in_ass = np.unique(self.products_in_ass)
if lvl3_assortment.empty or len(self.products_in_ass) == 0:
Log.warning(
"Assortment list is empty for store_fk {} in the requested session : {} - visit_date: {}"
.format(self.store_id, self.session_id,
self.session_info.get('visit_date').iloc[0]))
return
self.local_assortment_hierarchy_per_category_and_subcategory()
oos_per_product_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.OOS_BY_LOCAL_ASSORT_PRODUCT]['pk'].values[0]
msl_per_product_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.MSL_BY_LOCAL_ASSORT_PRODUCT]['pk'].values[0]
products_in_session = self.scif['item_id'].drop_duplicates().values
for sku in self.products_in_ass:
if sku in products_in_session:
result = self.result_value_pk(self.DISTRIBUTED)
result_num = 1
else:
result = self.result_value_pk(self.OOS)
result_num = 0
# Saving OOS
self.common.write_to_db_result(
fk=oos_per_product_kpi_fk,
numerator_id=sku,
numerator_result=result,
result=result,
denominator_id=self.own_manuf_fk,
denominator_result=1,
score=result,
identifier_parent="OOS_Local_store",
should_enter=True)
# Saving MSL
self.common.write_to_db_result(fk=msl_per_product_kpi_fk,
numerator_id=sku,
numerator_result=result_num,
result=result,
denominator_id=self.own_manuf_fk,
denominator_result=1,
score=result,
identifier_parent="MSL_Local_store",
should_enter=True)
# Saving MSL - Extra
# Add the Extra Products found in Session from same manufacturer into MSL
own_manufacturer_skus = self.get_own_manufacturer_skus_in_scif()
extra_products_in_scene = set(products_in_session) - set(
self.products_in_ass)
for sku in extra_products_in_scene:
if sku in own_manufacturer_skus:
result = self.result_value_pk(self.EXTRA) # Extra
result_num = 1
self.common.write_to_db_result(
fk=msl_per_product_kpi_fk,
numerator_id=sku,
numerator_result=result_num,
result=result,
denominator_id=self.own_manuf_fk,
denominator_result=1,
score=result,
identifier_parent="MSL_Local_store",
should_enter=True)
oos_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.OOS_BY_LOCAL_ASSORT_STORE_KPI]['pk'].values[0]
msl_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.MSL_BY_LOCAL_ASSORT]['pk'].values[0]
denominator = len(self.products_in_ass)
# Saving OOS
oos_numerator = len(
list(set(self.products_in_ass) - set(products_in_session)))
oos_res = round(
(oos_numerator / float(denominator)), 4) if denominator != 0 else 0
self.common.write_to_db_result(fk=oos_kpi_fk,
numerator_id=self.own_manuf_fk,
denominator_id=self.store_id,
numerator_result=oos_numerator,
result=oos_res,
denominator_result=denominator,
score=oos_res,
identifier_result="OOS_Local_store")
# Saving MSL
msl_numerator = len(
list(set(self.products_in_ass) & set(products_in_session)))
msl_res = round(
(msl_numerator / float(denominator)), 4) if denominator != 0 else 0
self.common.write_to_db_result(fk=msl_kpi_fk,
numerator_id=self.own_manuf_fk,
denominator_id=self.store_id,
numerator_result=msl_numerator,
result=msl_res,
denominator_result=denominator,
score=msl_res,
identifier_result="MSL_Local_store")
Log.debug("finishing oos_per_store_calc")
return
def local_assortment_hierarchy_per_category_and_subcategory(self):
Log.debug("starting oos_per_category_per_sub_category_per_product")
products_in_session = self.scif['product_fk'].drop_duplicates().values
# OOS KPIs
oos_cat_subcat_sku_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] == self.
OOS_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY_PRODUCT]['pk'].values[0]
oos_cat_subcat_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.OOS_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY]['pk'].values[0]
oos_cat_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.OOS_BY_LOCAL_ASSORT_CATEGORY]['pk'].values[0]
# MSL KPIs
msl_cat_subcat_sku_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] == self.
MSL_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY_PRODUCT]['pk'].values[0]
msl_cat_subcat_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.MSL_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY]['pk'].values[0]
msl_cat_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.MSL_BY_LOCAL_ASSORT_CATEGORY]['pk'].values[0]
categories = self.all_products[self.all_products['product_fk'].isin(self.products_in_ass)] \
['category_fk'].drop_duplicates().values
for category in categories:
products_in_cat = self.all_products[
self.all_products['category_fk'] ==
category]['product_fk'].drop_duplicates().values
relevant_for_ass = list(
set(self.products_in_ass) & set(products_in_cat))
denominator = len(relevant_for_ass)
# Saving OOS
oos_numerator = len(
list(set(relevant_for_ass) - set(products_in_session)))
oos_res = round((oos_numerator /
float(denominator)), 4) if denominator != 0 else 0
self.common.write_to_db_result(fk=oos_cat_kpi_fk,
numerator_id=self.own_manuf_fk,
numerator_result=oos_numerator,
result=oos_res,
denominator_id=category,
denominator_result=denominator,
score=oos_res,
identifier_result="OOS_Local_cat_" +
str(int(category)))
# Saving MSL
msl_numerator = len(
list(set(relevant_for_ass) & set(products_in_session)))
msl_res = round((msl_numerator /
float(denominator)), 4) if denominator != 0 else 0
self.common.write_to_db_result(fk=msl_cat_kpi_fk,
numerator_id=self.own_manuf_fk,
numerator_result=msl_numerator,
result=msl_res,
denominator_id=category,
denominator_result=denominator,
score=msl_res,
identifier_result="MSL_Local_cat_" +
str(int(category)))
sub_categories = self.all_products[(
self.all_products['product_fk'].isin(self.products_in_ass) &
(self.all_products['category_fk']
== category))]['sub_category_fk'].drop_duplicates().values
for sub_category in sub_categories:
products_in_sub_cat = self.all_products[
(self.all_products['sub_category_fk'] == sub_category)
& (self.all_products['category_fk'] == category
)]['product_fk'].drop_duplicates().values
relevant_for_ass = list(
set(self.products_in_ass) & set(products_in_sub_cat))
denominator = len(relevant_for_ass)
# Saving OOS
oos_numerator = len(
list(set(relevant_for_ass) - set(products_in_session)))
oos_res = round(
(oos_numerator /
float(denominator)), 4) if denominator != 0 else 0
self.common.write_to_db_result(
fk=oos_cat_subcat_kpi_fk,
numerator_id=self.own_manuf_fk,
numerator_result=oos_numerator,
result=oos_res,
denominator_id=sub_category,
denominator_result=denominator,
score=oos_res,
identifier_result="OOS_Local_subcat_" +
str(int(sub_category)),
identifier_parent="OOS_Local_cat_" + str(int(category)),
should_enter=True)
# Saving MSL
msl_numerator = len(
list(set(relevant_for_ass) & set(products_in_session)))
msl_res = round(
(msl_numerator /
float(denominator)), 4) if denominator != 0 else 0
self.common.write_to_db_result(
fk=msl_cat_subcat_kpi_fk,
numerator_id=self.own_manuf_fk,
numerator_result=msl_numerator,
result=msl_res,
denominator_id=sub_category,
denominator_result=denominator,
score=msl_res,
identifier_result="MSL_Local_subcat_" +
str(int(sub_category)),
identifier_parent="MSL_Local_cat_" + str(int(category)),
should_enter=True)
for sku in relevant_for_ass:
if sku in products_in_session:
result = self.result_value_pk(self.DISTRIBUTED)
result_num = 1
else:
result = self.result_value_pk(self.OOS)
result_num = 0
# Saving OOS
self.common.write_to_db_result(
fk=oos_cat_subcat_sku_kpi_fk,
result=result,
score=result,
numerator_id=sku,
numerator_result=result,
denominator_id=sub_category,
denominator_result=1,
identifier_parent="OOS_Local_subcat_" +
str(int(sub_category)),
should_enter=True)
# Saving MSL
self.common.write_to_db_result(
fk=msl_cat_subcat_sku_kpi_fk,
result=result,
score=result,
numerator_id=sku,
numerator_result=result_num,
denominator_id=sub_category,
denominator_result=1,
identifier_parent="MSL_Local_subcat_" +
str(int(sub_category)),
should_enter=True)
# Saving MSL
# Add the New Products found in Session for the subcat,cat from same manufacturer into MSL
# Filter products in session based on sub_cat and category
# extra_products_in_scene = set(products_in_session) - set(self.products_in_ass)
# for sku in extra_products_in_scene:
relevant_products_in_session = list(
set(products_in_session) & set(products_in_sub_cat))
extra_products_in_scene = set(
relevant_products_in_session) - set(relevant_for_ass)
for sku in extra_products_in_scene:
# Filter scif by own_manufacturer & product_type = 'SKU'
own_manufacturer_skus = self.scif[
(self.scif.manufacturer_fk == self.own_manuf_fk)
& (self.scif.product_type == "SKU")
& (self.scif["facings"] > 0)]['item_id'].tolist()
if sku in own_manufacturer_skus:
result = self.result_value_pk(self.EXTRA) # Extra
result_num = 1
self.common.write_to_db_result(
fk=msl_cat_subcat_sku_kpi_fk,
result=result,
score=result,
numerator_id=sku,
numerator_result=result_num,
denominator_id=sub_category,
denominator_result=1,
identifier_parent="MSL_Local_subcat_" +
str(int(sub_category)),
should_enter=True)
Log.debug("finishing assortment_per_category")
return
def main_calculation(self):
try:
if self.scif.empty:
Log.warning('Scene item facts is empty for this session')
Log.warning(
'Unable to calculate local_msl assortment KPIs: SCIF is empty'
)
return 0
self.reset_scif_and_matches()
self.filter_scif_matches_for_kpi(
"Distribution") #changed from local_msl to Distribution
self.local_assortment_hierarchy_per_store_calc()
except Exception as e:
Log.error("Error: {}".format(e))
return 0
class LIBERTYToolBox:
def __init__(self, data_provider, output, common_db):
self.output = output
self.data_provider = 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.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.store_info = self.ps_data_provider.get_ps_store_info(
self.data_provider[Data.STORE_INFO])
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.scif = self.scif[self.scif['product_type'] != "Irrelevant"]
self.result_values = self.ps_data_provider.get_result_values()
self.templates = self.read_templates()
self.common_db = common_db
self.survey = Survey(self.data_provider, output=self.output, ps_data_provider=self.ps_data_provider,
common=self.common_db)
self.manufacturer_fk = Const.MANUFACTURER_FK
self.region = self.store_info['region_name'].iloc[0]
self.store_type = self.store_info['store_type'].iloc[0]
self.retailer = self.store_info['retailer_name'].iloc[0]
self.branch = self.store_info['branch_name'].iloc[0]
self.additional_attribute_4 = self.store_info['additional_attribute_4'].iloc[0]
self.additional_attribute_7 = self.store_info['additional_attribute_7'].iloc[0]
self.body_armor_delivered = self.get_body_armor_delivery_status()
self.convert_base_size_and_multi_pack()
def read_templates(self):
templates = {}
for sheet in Const.SHEETS:
converters = None
if sheet == Const.MINIMUM_FACINGS:
converters = {Const.BASE_SIZE_MIN: self.convert_base_size_values,
Const.BASE_SIZE_MAX: self.convert_base_size_values}
templates[sheet] = \
pd.read_excel(Const.TEMPLATE_PATH, sheet_name=sheet,
converters=converters).fillna('')
return templates
# main functions:
def main_calculation(self, *args, **kwargs):
"""
This function gets all the scene results from the SceneKPI, after that calculates every session's KPI,
and in the end it calls "filter results" to choose every KPI and scene and write the results in DB.
"""
if self.region != 'Liberty':
return
red_score = 0
main_template = self.templates[Const.KPIS]
for i, main_line in main_template.iterrows():
relevant_store_types = self.does_exist(main_line, Const.ADDITIONAL_ATTRIBUTE_7)
if relevant_store_types and self.additional_attribute_7 not in relevant_store_types:
continue
result = self.calculate_main_kpi(main_line)
if result:
red_score += main_line[Const.WEIGHT] * result
if len(self.common_db.kpi_results) > 0:
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(Const.RED_SCORE_PARENT)
self.common_db.write_to_db_result(kpi_fk, numerator_id=1, denominator_id=self.store_id, result=red_score,
identifier_result=Const.RED_SCORE_PARENT, should_enter=True)
return
def calculate_main_kpi(self, main_line):
"""
This function gets a line from the main_sheet, transfers it to the match function, and checks all of the
KPIs in the same name in the match sheet.
:param main_line: series from the template of the main_sheet.
"""
relevant_scif = self.scif
scene_types = self.does_exist(main_line, Const.SCENE_TYPE)
if scene_types:
relevant_scif = relevant_scif[relevant_scif['template_name'].isin(scene_types)]
excluded_scene_types = self.does_exist(main_line, Const.EXCLUDED_SCENE_TYPE)
if excluded_scene_types:
relevant_scif = relevant_scif[~relevant_scif['template_name'].isin(
excluded_scene_types)]
template_groups = self.does_exist(main_line, Const.TEMPLATE_GROUP)
if template_groups:
relevant_scif = relevant_scif[relevant_scif['template_group'].isin(template_groups)]
result = self.calculate_kpi_by_type(main_line, relevant_scif)
return result
def calculate_kpi_by_type(self, main_line, relevant_scif):
"""
the function calculates all the kpis
:param main_line: one kpi line from the main template
:param relevant_scif:
:return: boolean, but it can be None if we want not to write it in DB
"""
kpi_type = main_line[Const.KPI_TYPE]
relevant_template = self.templates[kpi_type]
kpi_line = relevant_template[relevant_template[Const.KPI_NAME]
== main_line[Const.KPI_NAME]].iloc[0]
kpi_function = self.get_kpi_function(kpi_type)
weight = main_line[Const.WEIGHT]
if relevant_scif.empty:
result = 0
else:
result = kpi_function(kpi_line, relevant_scif, weight)
result_type_fk = self.ps_data_provider.get_pks_of_result(
Const.PASS) if result > 0 else self.ps_data_provider.get_pks_of_result(Const.FAIL)
if self.does_exist(main_line, Const.PARENT_KPI_NAME):
# if this is a child KPI, we do not need to return a value to the Total Score KPI
return 0
else: # normal behavior for when this isn't a child KPI
kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(kpi_name)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id, denominator_result=0, weight=weight,
result=result_type_fk, identifier_parent=Const.RED_SCORE_PARENT,
identifier_result=kpi_name, should_enter=True)
return result
# SOS functions
def calculate_sos(self, kpi_line, relevant_scif, weight):
market_share_required = self.does_exist(kpi_line, Const.MARKET_SHARE_TARGET)
if market_share_required:
market_share_target = self.get_market_share_target()
else:
market_share_target = 0
if not market_share_target:
market_share_target = 0
denominator_facings = relevant_scif['facings'].sum()
filtered_scif = relevant_scif.copy()
manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER)
if manufacturer:
filtered_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)]
liberty_truck = self.does_exist(kpi_line, Const.LIBERTY_KEY_MANUFACTURER)
if liberty_truck:
liberty_truck_scif = relevant_scif[relevant_scif[Const.LIBERTY_KEY_MANUFACTURER].isin(
liberty_truck)]
filtered_scif = filtered_scif.append(liberty_truck_scif, sort=False).drop_duplicates()
if self.does_exist(kpi_line, Const.INCLUDE_BODY_ARMOR) and self.body_armor_delivered:
body_armor_scif = relevant_scif[relevant_scif['brand_fk'] == Const.BODY_ARMOR_BRAND_FK]
filtered_scif = filtered_scif.append(body_armor_scif, sort=False)
numerator_facings = filtered_scif['facings'].sum()
sos_value = numerator_facings / float(denominator_facings)
result = 1 if sos_value > market_share_target else 0
parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=numerator_facings,
denominator_id=self.store_id, denominator_result=denominator_facings,
weight=weight, score=result * weight,
result=sos_value * 100, target=market_share_target * 100,
identifier_parent=parent_kpi_name, should_enter=True)
return result
# Availability functions
def calculate_availability(self, kpi_line, relevant_scif, weight):
survey_question_skus_required = self.does_exist(
kpi_line, Const.SURVEY_QUESTION_SKUS_REQUIRED)
if survey_question_skus_required:
survey_question_skus, secondary_survey_question_skus = \
self.get_relevant_product_assortment_by_kpi_name(kpi_line[Const.KPI_NAME])
unique_skus = \
relevant_scif[relevant_scif['product_fk'].isin(
survey_question_skus)]['product_fk'].unique().tolist()
if secondary_survey_question_skus:
secondary_unique_skus = \
relevant_scif[relevant_scif['product_fk'].isin(secondary_survey_question_skus)][
'product_fk'].unique().tolist()
else:
secondary_unique_skus = None
else:
secondary_unique_skus = None
manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER)
if manufacturer:
relevant_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)]
brand = self.does_exist(kpi_line, Const.BRAND)
if brand:
relevant_scif = relevant_scif[relevant_scif['brand_name'].isin(brand)]
category = self.does_exist(kpi_line, Const.CATEGORY)
if category:
relevant_scif = relevant_scif[relevant_scif['category'].isin(category)]
excluded_brand = self.does_exist(kpi_line, Const.EXCLUDED_BRAND)
if excluded_brand:
relevant_scif = relevant_scif[~relevant_scif['brand_name'].isin(excluded_brand)]
excluded_sku = self.does_exist(kpi_line, Const.EXCLUDED_SKU)
if excluded_sku:
relevant_scif = relevant_scif[~relevant_scif['product_name'].isin(excluded_sku)]
unique_skus = relevant_scif['product_fk'].unique().tolist()
length_of_unique_skus = len(unique_skus)
minimum_number_of_skus = kpi_line[Const.MINIMUM_NUMBER_OF_SKUS]
if length_of_unique_skus >= minimum_number_of_skus:
if secondary_unique_skus:
length_of_unique_skus = len(secondary_unique_skus)
minimum_number_of_skus = kpi_line[Const.SECONDARY_MINIMUM_NUMBER_OF_SKUS]
result = 1 if length_of_unique_skus > minimum_number_of_skus else 0
else:
result = 1
else:
result = 0
parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id, denominator_result=0, weight=weight,
result=length_of_unique_skus, target=minimum_number_of_skus,
score=result * weight,
identifier_parent=parent_kpi_name, should_enter=True)
return result
def get_relevant_product_assortment_by_kpi_name(self, kpi_name):
template = self.templates[Const.SURVEY_QUESTION_SKUS]
relevant_template = template[template[Const.KPI_NAME] == kpi_name]
# we need this to fix dumb template
relevant_template[Const.EAN_CODE] = \
relevant_template[Const.EAN_CODE].apply(lambda x: str(int(x)) if x != '' else None)
primary_ean_codes = \
relevant_template[relevant_template[Const.SECONDARY_GROUP]
!= 'Y'][Const.EAN_CODE].unique().tolist()
primary_ean_codes = [code for code in primary_ean_codes if code is not None]
primary_products = self.all_products[self.all_products['product_ean_code'].isin(
primary_ean_codes)]
primary_product_pks = primary_products['product_fk'].unique().tolist()
secondary_ean_codes = \
relevant_template[relevant_template[Const.SECONDARY_GROUP]
== 'Y'][Const.EAN_CODE].unique().tolist()
if secondary_ean_codes:
secondary_products = self.all_products[self.all_products['product_ean_code'].isin(
secondary_ean_codes)]
secondary_product_pks = secondary_products['product_fk'].unique().tolist()
else:
secondary_product_pks = None
return primary_product_pks, secondary_product_pks
# Count of Display functions
def calculate_count_of_display(self, kpi_line, relevant_scif, weight):
filtered_scif = relevant_scif.copy()
manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER)
if manufacturer:
filtered_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)]
liberty_truck = self.does_exist(kpi_line, Const.LIBERTY_KEY_MANUFACTURER)
if liberty_truck:
liberty_truck_scif = relevant_scif[relevant_scif[Const.LIBERTY_KEY_MANUFACTURER].isin(
liberty_truck)]
filtered_scif = filtered_scif.append(liberty_truck_scif, sort=False).drop_duplicates()
brand = self.does_exist(kpi_line, Const.BRAND)
if brand:
filtered_scif = filtered_scif[filtered_scif['brand_name'].isin(brand)]
category = self.does_exist(kpi_line, Const.CATEGORY)
if category:
filtered_scif = filtered_scif[filtered_scif['category'].isin(category)]
excluded_brand = self.does_exist(kpi_line, Const.EXCLUDED_BRAND)
if excluded_brand:
filtered_scif = filtered_scif[~filtered_scif['brand_name'].isin(excluded_brand)]
excluded_category = self.does_exist(kpi_line, Const.EXCLUDED_CATEGORY)
if excluded_category:
filtered_scif = filtered_scif[~filtered_scif['category'].isin(excluded_category)]
ssd_still = self.does_exist(kpi_line, Const.ATT4)
if ssd_still:
filtered_scif = filtered_scif[filtered_scif['att4'].isin(ssd_still)]
if self.does_exist(kpi_line, Const.INCLUDE_BODY_ARMOR) and self.body_armor_delivered:
body_armor_scif = relevant_scif[relevant_scif['brand_fk'] == Const.BODY_ARMOR_BRAND_FK]
filtered_scif = filtered_scif.append(body_armor_scif, sort=False)
size_subpackages = self.does_exist(kpi_line, Const.SIZE_SUBPACKAGES_NUM)
if size_subpackages:
# convert all pairings of size and number of subpackages to tuples
# size_subpackages_tuples = [tuple([float(i) for i in x.split(';')]) for x in size_subpackages]
size_subpackages_tuples = [tuple([self.convert_base_size_values(i) for i in x.split(';')]) for x in
size_subpackages]
filtered_scif = filtered_scif[pd.Series(list(zip(filtered_scif['Base Size'],
filtered_scif['Multi-Pack Size'])),
index=filtered_scif.index).isin(size_subpackages_tuples)]
excluded_size_subpackages = self.does_exist(kpi_line, Const.EXCLUDED_SIZE_SUBPACKAGES_NUM)
if excluded_size_subpackages:
# convert all pairings of size and number of subpackages to tuples
# size_subpackages_tuples = [tuple([float(i) for i in x.split(';')]) for x in size_subpackages]
size_subpackages_tuples = [tuple([self.convert_base_size_values(i) for i in x.split(';')]) for x in
excluded_size_subpackages]
filtered_scif = filtered_scif[~pd.Series(list(zip(filtered_scif['Base Size'],
filtered_scif['Multi-Pack Size'])),
index=filtered_scif.index).isin(size_subpackages_tuples)]
sub_packages = self.does_exist(kpi_line, Const.SUBPACKAGES_NUM)
if sub_packages:
if sub_packages == [Const.NOT_NULL]:
filtered_scif = filtered_scif[~filtered_scif['Multi-Pack Size'].isnull()]
elif sub_packages == [Const.GREATER_THAN_ONE]:
filtered_scif = filtered_scif[filtered_scif['Multi-Pack Size'] > 1]
else:
filtered_scif = filtered_scif[filtered_scif['Multi-Pack Size'].isin(
[int(i) for i in sub_packages])]
if self.does_exist(kpi_line, Const.MINIMUM_FACINGS_REQUIRED):
number_of_passing_displays, _ = self.get_number_of_passing_displays(filtered_scif)
if self.does_exist(kpi_line, Const.PARENT_KPI_NAME):
parent_kpi_name = kpi_line[Const.PARENT_KPI_NAME] + Const.LIBERTY + Const.DRILLDOWN
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(
kpi_line[Const.KPI_NAME] + Const.LIBERTY)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id, denominator_result=0, weight=weight,
result=number_of_passing_displays,
score=number_of_passing_displays,
identifier_parent=parent_kpi_name, should_enter=True)
return 0
else:
parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
identifier_result = parent_kpi_name + Const.DRILLDOWN
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id, denominator_result=0, weight=weight,
result=number_of_passing_displays,
score=number_of_passing_displays * weight,
identifier_parent=parent_kpi_name,
identifier_result=identifier_result, should_enter=True)
return number_of_passing_displays
else:
return 0
# Share of Display functions
def calculate_share_of_display(self, kpi_line, relevant_scif, weight):
base_scif = relevant_scif.copy()
ssd_still = self.does_exist(kpi_line, Const.ATT4)
if ssd_still:
ssd_still_scif = base_scif[base_scif['att4'].isin(ssd_still)]
else:
ssd_still_scif = base_scif
denominator_passing_displays, _ = \
self.get_number_of_passing_displays(ssd_still_scif)
manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER)
if manufacturer:
filtered_scif = ssd_still_scif[ssd_still_scif['manufacturer_name'].isin(manufacturer)]
else:
filtered_scif = ssd_still_scif
liberty_truck = self.does_exist(kpi_line, Const.LIBERTY_KEY_MANUFACTURER)
if liberty_truck:
liberty_truck_scif = ssd_still_scif[ssd_still_scif[Const.LIBERTY_KEY_MANUFACTURER].isin(
liberty_truck)]
filtered_scif = filtered_scif.append(liberty_truck_scif, sort=False).drop_duplicates()
if self.does_exist(kpi_line, Const.MARKET_SHARE_TARGET):
market_share_target = self.get_market_share_target(ssd_still=ssd_still)
else:
market_share_target = 0
if self.does_exist(kpi_line, Const.INCLUDE_BODY_ARMOR) and self.body_armor_delivered:
body_armor_scif = relevant_scif[relevant_scif['brand_fk'] == Const.BODY_ARMOR_BRAND_FK]
filtered_scif = filtered_scif.append(body_armor_scif, sort=False)
if self.does_exist(kpi_line, Const.MINIMUM_FACINGS_REQUIRED):
numerator_passing_displays, _ = \
self.get_number_of_passing_displays(filtered_scif)
if denominator_passing_displays != 0:
share_of_displays = \
numerator_passing_displays / float(denominator_passing_displays)
else:
share_of_displays = 0
result = 1 if share_of_displays > market_share_target else 0
parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk,
numerator_result=numerator_passing_displays,
denominator_id=self.store_id,
denominator_result=denominator_passing_displays, weight=weight,
result=share_of_displays * 100, target=market_share_target * 100,
score=result * weight,
identifier_parent=parent_kpi_name, should_enter=True)
return result
else:
return 0
def get_number_of_passing_displays(self, filtered_scif):
if filtered_scif.empty:
return 0, 0
filtered_scif = \
filtered_scif.groupby(['Base Size', 'Multi-Pack Size', 'scene_id'],
as_index=False)['facings'].sum()
filtered_scif['passed_displays'] = \
filtered_scif.apply(lambda row: self._calculate_pass_status_of_display(row), axis=1)
number_of_displays = filtered_scif['passed_displays'].sum()
facings_of_displays = filtered_scif[filtered_scif['passed_displays'] == 1]['facings'].sum()
return number_of_displays, facings_of_displays
def _calculate_pass_status_of_display(self, row): # need to move to external KPI targets
template = self.templates[Const.MINIMUM_FACINGS]
relevant_template = template[(template[Const.BASE_SIZE_MIN] <= row['Base Size']) &
(template[Const.BASE_SIZE_MAX] >= row['Base Size']) &
(template[Const.MULTI_PACK_SIZE] == row['Multi-Pack Size'])]
if relevant_template.empty:
return 0
minimum_facings = relevant_template[Const.MINIMUM_FACINGS_REQUIRED_FOR_DISPLAY].min()
return 1 if row['facings'] >= minimum_facings else 0
# Share of Cooler functions
def calculate_share_of_coolers(self, kpi_line, relevant_scif, weight):
scene_ids = relevant_scif['scene_id'].unique().tolist()
total_coolers = len(scene_ids)
if total_coolers == 0:
return 0
passing_coolers = 0
if self.does_exist(kpi_line, Const.MARKET_SHARE_TARGET):
market_share_target = self.get_market_share_target()
else:
market_share_target = 0
for scene_id in scene_ids:
cooler_scif = relevant_scif[relevant_scif['scene_id'] == scene_id]
filtered_scif = cooler_scif.copy()
manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER)
if manufacturer:
filtered_scif = cooler_scif[cooler_scif['manufacturer_name'].isin(manufacturer)]
liberty_truck = self.does_exist(kpi_line, Const.LIBERTY_KEY_MANUFACTURER)
if liberty_truck:
liberty_truck_scif = cooler_scif[cooler_scif[Const.LIBERTY_KEY_MANUFACTURER].isin(
liberty_truck)]
filtered_scif = filtered_scif.append(
liberty_truck_scif, sort=False).drop_duplicates()
if self.does_exist(kpi_line, Const.INCLUDE_BODY_ARMOR) and self.body_armor_delivered:
body_armor_scif = cooler_scif[cooler_scif['brand_fk'] == Const.BODY_ARMOR_BRAND_FK]
filtered_scif = filtered_scif.append(body_armor_scif, sort=False).drop_duplicates()
coke_facings_threshold = self.does_exist(kpi_line, Const.COKE_FACINGS_THRESHOLD)
cooler_sos = filtered_scif['facings'].sum() / cooler_scif['facings'].sum()
cooler_result = 1 if cooler_sos >= coke_facings_threshold else 0
passing_coolers += cooler_result
coke_market_share = passing_coolers / float(total_coolers)
result = 1 if coke_market_share > market_share_target else 0
parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk,
numerator_result=passing_coolers,
denominator_id=self.store_id,
denominator_result=total_coolers, weight=weight,
result=coke_market_share * 100, target=market_share_target * 100,
score=result * weight,
identifier_parent=parent_kpi_name, should_enter=True)
return result
# Survey functions
def calculate_survey(self, kpi_line, relevant_scif, weight):
return 1 if self.survey.check_survey_answer(kpi_line[Const.QUESTION_TEXT], 'Yes') else 0
# helper functions
def convert_base_size_and_multi_pack(self):
self.scif.loc[:, 'Base Size'] = self.scif['Base Size'].apply(self.convert_base_size_values)
self.scif.loc[:, 'Multi-Pack Size'] = \
self.scif['Multi-Pack Size'].apply(lambda x: int(x) if x is not None else None)
@staticmethod
def convert_base_size_values(value):
try:
new_value = float(value.split()[0]) if value not in [None, ''] else None
except IndexError:
Log.error('Could not convert base size value for {}'.format(value))
new_value = None
return new_value
def get_market_share_target(self, ssd_still=None): # need to move to external KPI targets
template = self.templates[Const.MARKET_SHARE]
relevant_template = template[(template[Const.ADDITIONAL_ATTRIBUTE_4] == self.additional_attribute_4) &
(template[Const.RETAILER] == self.retailer) &
(template[Const.BRANCH] == self.branch)]
if relevant_template.empty:
if ssd_still:
if ssd_still[0].lower() == Const.SSD.lower():
return 49
elif ssd_still[0].lower() == Const.STILL.lower():
return 16
else:
return 0
else:
return 26
if ssd_still:
if ssd_still[0].lower() == Const.SSD.lower():
return relevant_template[Const.SSD].iloc[0]
elif ssd_still[0].lower() == Const.STILL.lower():
return relevant_template[Const.STILL].iloc[0]
# total 26, ssd only 49, still only 16
return relevant_template[Const.SSD_AND_STILL].iloc[0]
def get_body_armor_delivery_status(self):
if self.store_info['additional_attribute_8'].iloc[0] == 'Y':
return True
else:
return False
def get_kpi_function(self, kpi_type):
"""
transfers every kpi to its own function
:param kpi_type: value from "sheet" column in the main sheet
:return: function
"""
if kpi_type == Const.SOS:
return self.calculate_sos
elif kpi_type == Const.AVAILABILITY:
return self.calculate_availability
elif kpi_type == Const.COUNT_OF_DISPLAY:
return self.calculate_count_of_display
elif kpi_type == Const.SHARE_OF_DISPLAY:
return self.calculate_share_of_display
elif kpi_type == Const.SHARE_OF_COOLERS:
return self.calculate_share_of_coolers
elif kpi_type == Const.SURVEY:
return self.calculate_survey
else:
Log.warning(
"The value '{}' in column sheet in the template is not recognized".format(kpi_type))
return None
@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, np.float64]:
return [cell]
elif type(cell) in [unicode, str]:
return [x.strip() for x in cell.split(",")]
return None
class LIBERTYToolBox:
def __init__(self, data_provider, output, common_db):
self.output = output
self.data_provider = 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.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.store_info = self.ps_data_provider.get_ps_store_info(self.data_provider[Data.STORE_INFO])
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.scif = self.scif[self.scif['product_type'] != "Irrelevant"]
self.templates = {}
self.result_values = self.ps_data_provider.get_result_values()
for sheet in Const.SHEETS:
self.templates[sheet] = pd.read_excel(Const.TEMPLATE_PATH, sheetname=sheet).fillna('')
self.common_db = common_db
self.survey = Survey(self.data_provider, output=self.output, ps_data_provider=self.ps_data_provider,
common=self.common_db)
self.manufacturer_fk = Const.MANUFACTURER_FK
self.region = self.store_info['region_name'].iloc[0]
self.store_type = self.store_info['store_type'].iloc[0]
self.retailer = self.store_info['retailer_name'].iloc[0]
self.branch = self.store_info['branch_name'].iloc[0]
self.additional_attribute_4 = self.store_info['additional_attribute_4'].iloc[0]
self.additional_attribute_7 = self.store_info['additional_attribute_7'].iloc[0]
self.body_armor_delivered = self.get_body_armor_delivery_status()
# main functions:
def main_calculation(self, *args, **kwargs):
"""
This function gets all the scene results from the SceneKPI, after that calculates every session's KPI,
and in the end it calls "filter results" to choose every KPI and scene and write the results in DB.
"""
red_score = 0
main_template = self.templates[Const.KPIS]
for i, main_line in main_template.iterrows():
relevant_store_types = self.does_exist(main_line, Const.ADDITIONAL_ATTRIBUTE_7)
if relevant_store_types and self.additional_attribute_7 not in relevant_store_types:
continue
result = self.calculate_main_kpi(main_line)
if result:
red_score += main_line[Const.WEIGHT]
if len(self.common_db.kpi_results) > 0:
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(Const.RED_SCORE_PARENT)
self.common_db.write_to_db_result(kpi_fk, numerator_id=1, denominator_id=self.store_id, result=red_score,
identifier_result=Const.RED_SCORE_PARENT, should_enter=True)
return
def calculate_main_kpi(self, main_line):
"""
This function gets a line from the main_sheet, transfers it to the match function, and checks all of the
KPIs in the same name in the match sheet.
:param main_line: series from the template of the main_sheet.
"""
relevant_scif = self.scif
scene_types = self.does_exist(main_line, Const.SCENE_TYPE)
if scene_types:
relevant_scif = relevant_scif[relevant_scif['template_name'].isin(scene_types)]
excluded_scene_types = self.does_exist(main_line, Const.EXCLUDED_SCENE_TYPE)
if excluded_scene_types:
relevant_scif = relevant_scif[~relevant_scif['template_name'].isin(excluded_scene_types)]
template_groups = self.does_exist(main_line, Const.TEMPLATE_GROUP)
if template_groups:
relevant_scif = relevant_scif[relevant_scif['template_group'].isin(template_groups)]
result = self.calculate_kpi_by_type(main_line, relevant_scif)
return result
def calculate_kpi_by_type(self, main_line, relevant_scif):
"""
the function calculates all the kpis
:param main_line: one kpi line from the main template
:param relevant_scif:
:return: boolean, but it can be None if we want not to write it in DB
"""
kpi_type = main_line[Const.KPI_TYPE]
relevant_template = self.templates[kpi_type]
kpi_line = relevant_template[relevant_template[Const.KPI_NAME] == main_line[Const.KPI_NAME]].iloc[0]
kpi_function = self.get_kpi_function(kpi_type)
weight = main_line[Const.WEIGHT]
if relevant_scif.empty:
result = 0
else:
result = kpi_function(kpi_line, relevant_scif, weight)
result_type_fk = self.ps_data_provider.get_pks_of_result(
Const.PASS) if result > 0 else self.ps_data_provider.get_pks_of_result(Const.FAIL)
kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(kpi_name)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id, denominator_result=0, weight=weight,
result=result_type_fk, identifier_parent=Const.RED_SCORE_PARENT,
identifier_result=kpi_name, should_enter=True)
return result
# SOS functions
def calculate_sos(self, kpi_line, relevant_scif, weight):
market_share_required = self.does_exist(kpi_line, Const.MARKET_SHARE_TARGET)
if market_share_required:
market_share_target = self.get_market_share_target()
else:
market_share_target = 0
if not market_share_target:
market_share_target = 0
manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER)
if manufacturer:
relevant_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)]
number_of_facings = relevant_scif['facings'].sum()
result = 1 if number_of_facings > market_share_target else 0
parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id, denominator_result=0, weight=weight,
result=number_of_facings, target=market_share_target,
identifier_parent=parent_kpi_name, should_enter=True)
return result
# Availability functions
def calculate_availability(self, kpi_line, relevant_scif, weight):
survey_question_skus_required = self.does_exist(kpi_line, Const.SURVEY_QUESTION_SKUS_REQUIRED)
if survey_question_skus_required:
survey_question_skus = self.get_relevant_product_assortment_by_kpi_name(kpi_line[Const.KPI_NAME])
unique_skus = \
relevant_scif[relevant_scif['product_fk'].isin(survey_question_skus)]['product_fk'].unique().tolist()
else:
manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER)
if manufacturer:
relevant_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)]
brand = self.does_exist(kpi_line, Const.BRAND)
if brand:
relevant_scif = relevant_scif[relevant_scif['brand_name'].isin(brand)]
category = self.does_exist(kpi_line, Const.CATEGORY)
if category:
relevant_scif = relevant_scif[relevant_scif['category'].isin(category)]
excluded_brand = self.does_exist(kpi_line, Const.EXCLUDED_BRAND)
if excluded_brand:
relevant_scif = relevant_scif[~relevant_scif['brand_name'].isin(excluded_brand)]
unique_skus = relevant_scif['product_fk'].unique().tolist()
length_of_unique_skus = len(unique_skus)
minimum_number_of_skus = kpi_line[Const.MINIMUM_NUMBER_OF_SKUS]
result = 1 if length_of_unique_skus >= minimum_number_of_skus else 0
parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id, denominator_result=0, weight=weight,
result=length_of_unique_skus, target=minimum_number_of_skus,
identifier_parent=parent_kpi_name, should_enter=True)
return result
def get_relevant_product_assortment_by_kpi_name(self, kpi_name):
template = self.templates[Const.SURVEY_QUESTION_SKUS]
relevant_template = template[template[Const.KPI_NAME] == kpi_name]
relevant_ean_codes = relevant_template[Const.EAN_CODE].unique().tolist()
relevant_ean_codes = [str(int(x)) for x in relevant_ean_codes if x != ''] # we need this to fix dumb template
relevant_products = self.all_products[self.all_products['product_ean_code'].isin(relevant_ean_codes)]
return relevant_products['product_fk'].unique().tolist()
# Count of Display functions
def calculate_count_of_display(self, kpi_line, relevant_scif, weight):
filtered_scif = relevant_scif
manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER)
if manufacturer:
filtered_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)]
brand = self.does_exist(kpi_line, Const.BRAND)
if brand:
filtered_scif = filtered_scif[filtered_scif['brand_name'].isin(brand)]
ssd_still = self.does_exist(kpi_line, Const.ATT4)
if ssd_still:
filtered_scif = filtered_scif[filtered_scif['att4'].isin(ssd_still)]
size_subpackages = self.does_exist(kpi_line, Const.SIZE_SUBPACKAGES_NUM)
if size_subpackages:
# convert all pairings of size and number of subpackages to tuples
size_subpackages_tuples = [tuple([float(i) for i in x.split(';')]) for x in size_subpackages]
filtered_scif = filtered_scif[pd.Series(list(zip(filtered_scif['size'],
filtered_scif['number_of_sub_packages'])),
index=filtered_scif.index).isin(size_subpackages_tuples)]
sub_packages = self.does_exist(kpi_line, Const.SUBPACKAGES_NUM)
if sub_packages:
if sub_packages == [Const.NOT_NULL]:
filtered_scif = filtered_scif[~filtered_scif['number_of_sub_packages'].isnull()]
else:
filtered_scif = filtered_scif[filtered_scif['number_of_sub_packages'].isin([int(i) for i in sub_packages])]
if self.does_exist(kpi_line, Const.MINIMUM_FACINGS_REQUIRED):
number_of_passing_displays = self.get_number_of_passing_displays(filtered_scif)
parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id, denominator_result=0, weight=weight,
result=number_of_passing_displays,
identifier_parent=parent_kpi_name, should_enter=True)
return 1 if number_of_passing_displays > 0 else 0
else:
return 0
# Share of Display functions
def calculate_share_of_display(self, kpi_line, relevant_scif, weight):
filtered_scif = relevant_scif
manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER)
if manufacturer:
filtered_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)]
ssd_still = self.does_exist(kpi_line, Const.ATT4)
if ssd_still:
filtered_scif = filtered_scif[filtered_scif['att4'].isin(ssd_still)]
if self.does_exist(kpi_line, Const.MARKET_SHARE_TARGET):
market_share_target = self.get_market_share_target(ssd_still=ssd_still)
else:
market_share_target = 0
if self.does_exist(kpi_line, Const.INCLUDE_BODY_ARMOR) and self.body_armor_delivered:
body_armor_scif = relevant_scif[relevant_scif['brand_fk'] == Const.BODY_ARMOR_BRAND_FK]
filtered_scif = filtered_scif.append(body_armor_scif, sort=False)
if self.does_exist(kpi_line, Const.MINIMUM_FACINGS_REQUIRED):
number_of_passing_displays = self.get_number_of_passing_displays(filtered_scif)
result = 1 if number_of_passing_displays > market_share_target else 0
parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id, denominator_result=0, weight=weight,
result=number_of_passing_displays, target=market_share_target,
identifier_parent=parent_kpi_name, should_enter=True)
return result
else:
return 0
def get_number_of_passing_displays(self, filtered_scif):
if filtered_scif.empty:
return 0
filtered_scif['passed_displays'] = \
filtered_scif.apply(lambda row: self._calculate_pass_status_of_display(row), axis=1)
return filtered_scif['passed_displays'].sum()
def _calculate_pass_status_of_display(self, row): # need to move to external KPI targets
template = self.templates[Const.MINIMUM_FACINGS]
package_category = (row['size'], row['number_of_sub_packages'], row['size_unit'])
relevant_template = template[pd.Series(zip(template['size'],
template['subpackages_num'],
template['unit_of_measure'])) == package_category]
minimum_facings = relevant_template[Const.MINIMUM_FACINGS_REQUIRED_FOR_DISPLAY].min()
return 1 if row['facings'] > minimum_facings else 0
# Survey functions
def calculate_survey(self, kpi_line, relevant_scif, weight):
return 1 if self.survey.check_survey_answer(kpi_line[Const.QUESTION_TEXT], 'Yes') else 0
# helper functions
def get_market_share_target(self, ssd_still=None): # need to move to external KPI targets
template = self.templates[Const.MARKET_SHARE]
relevant_template = template[(template[Const.ADDITIONAL_ATTRIBUTE_4] == self.additional_attribute_4) &
(template[Const.RETAILER] == self.retailer) &
(template[Const.BRANCH] == self.branch)]
if relevant_template.empty:
if ssd_still:
if ssd_still[0].lower() == Const.SSD.lower():
return 49
elif ssd_still[0].lower() == Const.STILL.lower():
return 16
else:
return 0
else:
return 26
if ssd_still:
if ssd_still[0].lower() == Const.SSD.lower():
return relevant_template[Const.SSD].iloc[0]
elif ssd_still[0].lower() == Const.STILL.lower():
return relevant_template[Const.STILL].iloc[0]
# total 26, ssd only 49, still only 16
return relevant_template[Const.SSD_AND_STILL].iloc[0]
def get_body_armor_delivery_status(self):
if self.store_info['additional_attribute_8'].iloc[0] == 'Y':
return True
else:
return False
def get_kpi_function(self, kpi_type):
"""
transfers every kpi to its own function
:param kpi_type: value from "sheet" column in the main sheet
:return: function
"""
if kpi_type == Const.SOS:
return self.calculate_sos
elif kpi_type == Const.AVAILABILITY:
return self.calculate_availability
elif kpi_type == Const.COUNT_OF_DISPLAY:
return self.calculate_count_of_display
elif kpi_type == Const.SHARE_OF_DISPLAY:
return self.calculate_share_of_display
elif kpi_type == Const.SURVEY:
return self.calculate_survey
else:
Log.warning(
"The value '{}' in column sheet in the template is not recognized".format(kpi_type))
return None
@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 [x.strip() for x in cell.split(",")]
return None
class NESTLEILToolBox:
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.ps_data_provider = PsDataProvider(data_provider)
self.kpi_result_values = self.ps_data_provider.get_result_values()
self.common_v2 = Common(self.data_provider)
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.assortment = Assortment(self.data_provider)
self.own_manufacturer_fk = int(
self.data_provider.own_manufacturer.param_value.values[0])
self.kpi_static_data = self.common_v2.kpi_static_data[[
'pk', StaticKpis.TYPE
]]
# self.custom_entity = self.ps_data_provider.get_custom_entities(Consts.PRODUCT_GROUP)
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.project_name = self.data_provider.project_name
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.external_targets = self._retrieve_completeness_external_targets()
self.products_trax_cat = self._get_products_with_trax_categories()
self.ass_groups_present = {
Consts.DISTR_SNACKS: 0,
Consts.DISTR_SABRA: 0
}
def _get_products_with_trax_categories(self):
products_categories = self._get_trax_category_for_products()
prod_group_trax_cat_map = self.external_targets[[
Consts.PRODUCT_GROUP_FK, Consts.TRAX_CATEGORY_FK
]]
products_categories = products_categories.merge(
prod_group_trax_cat_map, on=Consts.TRAX_CATEGORY_FK, how='left')
products_categories[Consts.IS_INCLUDED] = 0
return products_categories
def _get_trax_category_for_products(self):
query = NestleilQueries.get_trax_category_for_products_query()
products_categories = pd.read_sql_query(query, self.rds_conn.db)
return products_categories
def _retrieve_completeness_external_targets(self):
external_targets = self._get_kpi_external_targets()
external_targets = external_targets.drop_duplicates(
subset=[SessionResultsConsts.KPI_LEVEL_2_FK], keep='last')
external_targets = self._unpack_kpi_targets_from_db(
external_targets, 'key_json')
return external_targets
def _get_kpi_external_targets(self):
query = NestleilQueries.kpi_external_targets_query(
Consts.COMPLETENESS_CHECK, self.visit_date)
external_targets = pd.read_sql_query(query, self.rds_conn.db)
return external_targets
def _unpack_kpi_targets_from_db(self, input_df, field_name):
input_df['json_dict_with_pk'] = input_df.apply(self._add_pk_to_json,
args=(field_name, ),
axis=1)
json_dict_list = input_df['json_dict_with_pk'].values.tolist()
output_df = pd.DataFrame(json_dict_list)
input_df = input_df.merge(output_df, on='pk', how='left')
return input_df
@staticmethod
def _add_pk_to_json(row, field_name):
json_value = row[field_name]
json_to_dict = json.loads(json_value)
json_to_dict.update({'pk': row['pk']})
return json_to_dict
def main_calculation(self):
""" This function calculates the KPI results."""
self._determine_collection_completeness_for_product_groups()
self._calculate_assortment()
self.common_v2.commit_results_data()
def _determine_collection_completeness_for_product_groups(self):
self._define_snacks_completeness()
self._define_sabra_completeness()
def get_external_targets_row(self, kpi_name):
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(kpi_name)
external_targets = self.external_targets[self.external_targets[
ExternalTargetsConsts.KPI_LEVEL_2_FK] == kpi_fk]
et_row = pd.Series()
if not external_targets.empty:
et_row = external_targets.iloc[0]
else:
Log.error('No kpi targets set for kpi {}'.format(kpi_name))
return et_row
# def get_custom_entity_fk(self, entity_value):
# entity_df = self.custom_entity[self.custom_entity['name'].str.encode('utf8') ==
# entity_value.encode('utf8')]
# entity_fk = entity_df['pk'].values[0] if not entity_df.empty else None
# return entity_fk
def _define_snacks_completeness(self):
et_row = self.get_external_targets_row(
Consts.COLLECTION_COMPLETENESS_SNACKS)
brands_list = et_row['allocation_value']
brands_facings = self.scif.groupby([ScifConsts.BRAND_FK],
as_index=False).agg(
{ScifConsts.FACINGS: np.sum})
brands_facings = brands_facings[brands_facings[
ScifConsts.BRAND_FK].isin(brands_list)]
brands_in_session = brands_facings[ScifConsts.BRAND_FK].values.tolist()
all_brands_exist = all(
[brand in brands_in_session for brand in brands_list])
score = 0
if all_brands_exist:
brands_below_threshold = brands_facings[
brands_facings[ScifConsts.FACINGS] < 3]
if brands_below_threshold.empty:
score = 1
self.ass_groups_present[Consts.DISTR_SNACKS] = score
product_group_fk = et_row[Consts.PRODUCT_GROUP_FK]
# self.products_trax_cat.loc[self.products_trax_cat[Consts.PRODUCT_GROUP_FK] == product_group_fk,
# Consts.IS_INCLUDED] = score
self.common_v2.write_to_db_result(
fk=et_row[ExternalTargetsConsts.KPI_LEVEL_2_FK],
numerator_id=product_group_fk,
denominator_id=self.store_id,
score=score,
result=score)
def _define_sabra_completeness(self):
et_row = self.get_external_targets_row(
Consts.COLLECTION_COMPLETENESS_SABRA)
score = 0
cat_list = et_row['allocation_value']
cat_in_session = self.scif[ScifConsts.CATEGORY_FK].unique().tolist()
all_categories_exist = all([cat in cat_in_session for cat in cat_list])
if all_categories_exist:
score = 1
product_group_fk = et_row[Consts.PRODUCT_GROUP_FK]
self.ass_groups_present[Consts.DISTR_SABRA] = score
# self.products_trax_cat.loc[self.products_trax_cat[Consts.PRODUCT_GROUP_FK] == product_group_fk,
# Consts.IS_INCLUDED] = score
self.common_v2.write_to_db_result(
fk=et_row[ExternalTargetsConsts.KPI_LEVEL_2_FK],
numerator_id=product_group_fk,
denominator_id=self.store_id,
score=score,
result=score)
def _calculate_assortment(self):
"""
This method calculates and saves results into the DB.
First, it calculate the results per store and sku level
and than saves results both for Assortment and SKU.
"""
lvl3_result = self.assortment.calculate_lvl3_assortment()
if lvl3_result.empty:
Log.warning(Consts.EMPTY_ASSORTMENT_DATA)
return
lvl3_result = self._add_kpi_types_to_assortment_result(lvl3_result)
lvl3_result = lvl3_result.merge(self.products_trax_cat,
on=ScifConsts.PRODUCT_FK,
how='left')
# Getting KPI fks
for ass_group, presence_score in self.ass_groups_present.items():
lvl3_result.loc[lvl3_result[Consts.STORE_ASS_KPI_TYPE] ==
ass_group, Consts.IS_INCLUDED] = presence_score
lvl3_result = lvl3_result[lvl3_result[Consts.IS_INCLUDED] == 1]
store_lvl_kpi_list = lvl3_result[
Consts.STORE_ASS_KPI_TYPE].unique().tolist()
for store_lvl_kpi in store_lvl_kpi_list:
relevant_lvl3_res = lvl3_result[lvl3_result[
Consts.STORE_ASS_KPI_TYPE] == store_lvl_kpi]
sku_lvl_kpi = relevant_lvl3_res[Consts.SKU_ASS_KPI_TYPE].values[0]
dist_store_kpi_fk, oos_store_kpi_fk = self._get_ass_kpis(
store_lvl_kpi)
dist_sku_kpi_fk, oos_sku_kpi_fk = self._get_ass_kpis(sku_lvl_kpi)
# Calculating the assortment results
store_level_res = self._calculated_store_level_results(
relevant_lvl3_res)
sku_level_res = self._calculated_sku_level_results(
relevant_lvl3_res)
# Saving to DB
self._save_results_to_db(store_level_res, dist_store_kpi_fk)
self._save_results_to_db(sku_level_res, dist_sku_kpi_fk,
dist_store_kpi_fk)
self._save_results_to_db(store_level_res,
oos_store_kpi_fk,
is_complementary=True)
self._save_results_to_db(sku_level_res, oos_sku_kpi_fk,
oos_store_kpi_fk)
# sku lvl assortment result for canvas
self.calculate_aggregate_sku_lvl_assortment(lvl3_result)
def calculate_aggregate_sku_lvl_assortment(self, lvl3_result):
distr_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(
Consts.DISTRIBUTION_SKU_LEVEL)
oos_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(
Consts.OOS_SKU_LEVEL)
for i, row in lvl3_result.iterrows():
distr_res = row[Consts.IN_STORE]
oos_res = 1 - distr_res
self.common_v2.write_to_db_result(
fk=distr_kpi_fk,
numerator_id=row[Consts.PRODUCT_FK],
denominator_id=self.own_manufacturer_fk,
numerator_result=distr_res,
denominator_result=1,
context_id=row[Consts.PRODUCT_GROUP_FK],
result=distr_res,
score=distr_res)
self.common_v2.write_to_db_result(
fk=oos_kpi_fk,
numerator_id=row[Consts.PRODUCT_FK],
denominator_id=self.own_manufacturer_fk,
numerator_result=oos_res,
denominator_result=1,
context_id=row[Consts.PRODUCT_GROUP_FK],
result=oos_res,
score=oos_res)
def _add_kpi_types_to_assortment_result(self, lvl3_result):
lvl3_result = lvl3_result.merge(self.kpi_static_data,
left_on=Consts.KPI_FK_LVL2,
right_on='pk',
how='left')
lvl3_result = lvl3_result.drop(['pk'], axis=1)
lvl3_result.rename(
columns={StaticKpis.TYPE: Consts.STORE_ASS_KPI_TYPE}, inplace=True)
lvl3_result = lvl3_result.merge(self.kpi_static_data,
left_on=Consts.KPI_FK_LVL3,
right_on='pk',
how='left')
lvl3_result = lvl3_result.drop(['pk'], axis=1)
lvl3_result.rename(columns={StaticKpis.TYPE: Consts.SKU_ASS_KPI_TYPE},
inplace=True)
return lvl3_result
def _get_ass_kpis(self, distr_kpi_type):
"""
This method fetches the assortment KPIs in the store level
:return: A tuple: (Distribution store KPI fk, OOS store KPI fk)
"""
distribution_kpi = self.common_v2.get_kpi_fk_by_kpi_type(
distr_kpi_type)
oos_kpi_type = Consts.DIST_OOS_KPIS_MAP[distr_kpi_type]
oos_kpi = self.common_v2.get_kpi_fk_by_kpi_type(oos_kpi_type)
return distribution_kpi, oos_kpi
# def _get_store_level_kpis(self):
# """
# This method fetches the assortment KPIs in the store level
# :return: A tuple: (Distribution store KPI fk, OOS store KPI fk)
# """
# distribution_store_level_kpi = self.common_v2.get_kpi_fk_by_kpi_type(Consts.DISTRIBUTION_STORE_LEVEL)
# oos_store_level_kpi = self.common_v2.get_kpi_fk_by_kpi_type(Consts.OOS_STORE_LEVEL)
# return distribution_store_level_kpi, oos_store_level_kpi
#
# def _get_sku_level_kpis(self):
# """
# This method fetches the assortment KPIs in the SKU level
# :return: A tuple: (Distribution SKU KPI fk, OOS SKU KPI fk)
# """
# distribution_sku_level_kpi = self.common_v2.get_kpi_fk_by_kpi_type(Consts.DISTRIBUTION_SKU_LEVEL)
# oos_sku_level_kpi = self.common_v2.get_kpi_fk_by_kpi_type(Consts.OOS_SKU_LEVEL)
# return distribution_sku_level_kpi, oos_sku_level_kpi
def _calculated_store_level_results(self, lvl3_result):
"""
This method calculates the assortment results in the store level
:return: A list with a dictionary that includes the relevant entities for the DB
"""
result = {key: 0 for key in Consts.ENTITIES_FOR_DB}
result[Consts.NUMERATOR_RESULT] = lvl3_result.in_store.sum()
result[Consts.DENOMINATOR_RESULT] = lvl3_result.in_store.count()
result[Consts.NUMERATOR_ID] = self.own_manufacturer_fk
result[Consts.DENOMINATOR_ID] = self.store_id
return [result]
def _calculated_sku_level_results(self, lvl3_result):
"""
This method calculates the assortment results in the sku level
:return: A dictionary with the relevant entities for the DB
"""
sku_level_res = lvl3_result[[Consts.PRODUCT_FK, Consts.IN_STORE]]
sku_level_res.rename(Consts.SOS_SKU_LVL_RENAME, axis=1, inplace=True)
sku_level_res = sku_level_res.assign(
denominator_id=self.own_manufacturer_fk)
sku_level_res = sku_level_res.assign(denominator_result=1)
sku_level_res = sku_level_res.to_dict('records')
return sku_level_res
def _calculate_assortment_score_and_result(self,
num_result,
den_result,
save_result_values=False):
"""
The method calculates the score & result per kpi. In order to support MR Icons results, in case of
SKU level KPI, the method fetches the relevant pk from kpi_result_value entities.
:return: A tuple of the score and result.
"""
score = result = round(
(num_result / float(den_result)) * 100, 2) if den_result else 0
if save_result_values:
result_type = Consts.DISTRIBUTED_VALUE if score else Consts.OOS_VALUE
result = self.kpi_result_values.loc[self.kpi_result_values.value ==
result_type, 'pk'].values[0]
return score, result
def _save_results_to_db(self,
results_list,
kpi_fk,
parent_kpi_fk=None,
is_complementary=False):
"""
This method saves result into the DB. The only change between Distribution
and OOS is the numerator result so it is taking this into consideration.
:param results_list: A list of dictionary with the results.
:param is_complementary: There are complementary KPIs (like distribution and OOS) that the only
difference is in the numerator result (and the score that is being affected from it).
So instead of calculating twice we are just changing the numerator result.
"""
for result in results_list:
numerator_id, denominator_id = result[Consts.NUMERATOR_ID], result[
Consts.DENOMINATOR_ID]
num_res, den_res = result[Consts.NUMERATOR_RESULT], result[
Consts.DENOMINATOR_RESULT]
num_res = den_res - num_res if is_complementary else num_res
should_enter = save_result_values = True if parent_kpi_fk is not None else False
score, result = self._calculate_assortment_score_and_result(
num_res, den_res, save_result_values)
identifier_parent = (parent_kpi_fk,
denominator_id) if should_enter else None
self.common_v2.write_to_db_result(
fk=kpi_fk,
numerator_id=numerator_id,
numerator_result=num_res,
denominator_id=denominator_id,
denominator_result=den_res,
score=score,
result=result,
identifier_result=(kpi_fk, numerator_id),
should_enter=should_enter,
identifier_parent=identifier_parent)
class MSCToolBox:
def __init__(self, data_provider, output, common_db):
self.output = output
self.data_provider = 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.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.scif = self.scif[~(self.scif['product_type'].isin(["Irrelevant", "Empty", "Other"]))]
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.templates = {}
self.result_values = self.ps_data_provider.get_result_values()
for sheet in Const.SHEETS:
self.templates[sheet] = pd.read_excel(Const.TEMPLATE_PATH, sheetname=sheet).fillna('')
self.common_db = common_db
self.region = self.store_info['region_name'].iloc[0]
self.store_type = self.store_info['store_type'].iloc[0]
self.manufacturer_fk = Const.MANUFACTURER_FK
# main functions:
def main_calculation(self, *args, **kwargs):
"""
This function gets all the scene results from the SceneKPI, after that calculates every session's KPI,
and in the end it calls "filter results" to choose every KPI and scene and write the results in DB.
"""
main_template = self.templates[Const.KPIS]
for i, main_line in main_template.iterrows():
relevant_store_types = self.does_exist(main_line, Const.STORE_TYPE)
if relevant_store_types and self.store_type not in relevant_store_types:
continue
self.calculate_main_kpi(main_line)
if len(self.common_db.kpi_results) > 0:
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(Const.MSC)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, denominator_id=self.store_id,
result=1,
identifier_result=Const.MSC, should_enter=True)
return
def calculate_main_kpi(self, main_line):
"""
This function gets a line from the main_sheet, transfers it to the match function, and checks all of the
KPIs in the same name in the match sheet.
:param main_line: series from the template of the main_sheet.
"""
kpi_name = main_line[Const.KPI_NAME]
relevant_scif = self.scif
scene_types = self.does_exist(main_line, Const.SCENE_TYPE)
if scene_types:
relevant_scif = relevant_scif[relevant_scif['template_name'].isin(scene_types)]
result = self.calculate_kpi_by_type(main_line, relevant_scif)
# self.write_to_session_level(kpi_name=kpi_name, result=result)
return result
def calculate_kpi_by_type(self, main_line, filtered_scif):
"""
the function calculates all the kpis
:param main_line: one kpi line from the main template
:param filtered_scif:
:return: boolean, but it can be None if we want not to write it in DB
"""
kpi_type = main_line[Const.KPI_TYPE]
relevant_template = self.templates[kpi_type]
relevant_template = relevant_template[relevant_template[Const.KPI_NAME]
== main_line[Const.KPI_NAME]]
kpi_function = self.get_kpi_function(kpi_type)
return self.calculate_specific_kpi(relevant_template, filtered_scif, kpi_function)
@staticmethod
def calculate_specific_kpi(relevant_template, filtered_scif, kpi_function, target=None):
"""
checks if the passed lines are more than target
:param relevant_template: specific template filtered with the specific kpi lines
:param filtered_scif:
:param target: integer
:param kpi_function: specific function for the calculation
:return: boolean, but it can be None if we want not to write it in DB
"""
passed_counter = 0
for i, kpi_line in relevant_template.iterrows():
answer = kpi_function(kpi_line, filtered_scif)
if answer:
passed_counter += 1
elif answer is None:
return None
return passed_counter >= target
# facings calculations
def calculate_facings(self, kpi_line, relevant_scif):
denominator_scif = self.get_denominator_scif(kpi_line, relevant_scif)
numerator_scif = self.get_numerator_scif(kpi_line, denominator_scif)
numerator_result = numerator_scif['facings'].sum()
denominator_result = denominator_scif['facings'].sum()
sos_value = numerator_result / denominator_result
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(kpi_line[Const.KPI_NAME])
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=numerator_result,
denominator_id=self.store_id, denominator_result=denominator_result,
result=sos_value * 100, identifier_parent=Const.MSC, should_enter=True)
return
def check_activation_status(self, kpi_line, relevant_scif):
"""
This function checks to see whether or not the KPI has an activation parameter and value combo defined.
If it does, the function makes sure that ALL values are present
:param kpi_line:
:param relevant_scif:
:return:
"""
try:
activation_param = kpi_line[Const.ACTIVATION_TYPE]
except KeyError:
activation_param = None
if activation_param:
relevant_scif = self.filter_scif_by_template_columns(kpi_line, Const.ACTIVATION_TYPE,
Const.ACTIVATION_VALUE, relevant_scif)
if relevant_scif.empty:
return False
else:
return True
else:
# no activation for this KPI? return true
return True
def calculate_availability(self, kpi_line, relevant_scif, save_result=True):
availability = True
numerator_scif = self.get_numerator_scif(kpi_line, relevant_scif)
if numerator_scif.empty:
availability = False
minimum_skus = self.does_exist(kpi_line, Const.MINIMUM_SKUS)
if minimum_skus:
number_of_skus = len(numerator_scif['product_name'].unique())
availability = number_of_skus >= minimum_skus[0]
minimum_brands = self.does_exist(kpi_line, Const.MINIMUM_BRANDS)
if minimum_brands:
number_of_brands = len(numerator_scif['brand_name'].unique())
availability = number_of_brands >= minimum_brands[0]
minimum_packages = self.does_exist(kpi_line, Const.MINIMUM_PACKAGES)
if minimum_packages:
number_of_packages = len(numerator_scif.drop_duplicates(subset=['Multi-Pack Size', 'Base Size']))
availability = number_of_packages >= minimum_packages[0]
threshold = self.does_exist(kpi_line, Const.THRESHOLD)
if threshold:
availability = numerator_scif['facings'].sum() / relevant_scif['facings'].sum() >= threshold[0]
# result = self.ps_data_provider.get_pks_of_result(
# Const.PASS) if availability else self.ps_data_provider.get_pks_of_result(Const.FAIL)
result = 100 if availability else 0
if save_result:
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(kpi_line[Const.KPI_NAME])
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, denominator_id=self.store_id,
result=result, identifier_parent=Const.MSC, should_enter=True)
return availability
def calculate_per_scene_availability(self, kpi_line, relevant_scif):
scenes = relevant_scif['scene_fk'].unique().tolist()
passing_scenes = 0
for scene in scenes:
scene_scif = relevant_scif[relevant_scif['scene_fk'] == scene]
if not self.check_activation_status(kpi_line, scene_scif):
continue
passing_scenes += self.calculate_availability(kpi_line, scene_scif, save_result=False)
if passing_scenes: # we only need at least one passing scene
break
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(kpi_line[Const.KPI_NAME])
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, denominator_id=self.store_id,
result=passing_scenes, identifier_parent=Const.MSC, should_enter=True)
return passing_scenes
def calculate_double_availability(self, kpi_line, relevant_scif):
group_1_scif = self.filter_scif_availability(kpi_line, relevant_scif, group=1)
group_1_minimum_facings = kpi_line[Const.GROUP1_MINIMUM_FACINGS]
availability = group_1_scif['facings'].sum() >= group_1_minimum_facings
if availability:
group_2_scif = self.filter_scif_availability(kpi_line, relevant_scif, group=2)
group_2_minimum_facings = kpi_line[Const.GROUP2_MINIMUM_FACINGS]
availability = group_2_scif['facings'].sum() >= group_2_minimum_facings
# result = self.ps_data_provider.get_pks_of_result(
# Const.PASS) if availability else self.ps_data_provider.get_pks_of_result(Const.FAIL)
result = 100 if availability else 0
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(kpi_line[Const.KPI_NAME])
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, denominator_id=self.store_id,
result=result, identifier_parent=Const.MSC, should_enter=True)
return availability
def filter_scif_availability(self, kpi_line, relevant_scif, group=None):
"""
calls filter_scif_specific for every column in the template of availability
:param kpi_line:
:param relevant_scif:
:param group: used to indicate group for double availability
:return:
"""
if group == 1:
names_of_columns = {
Const.GROUP1_BRAND: "brand_name",
}
elif group == 2:
names_of_columns = {
Const.GROUP2_BRAND: "brand_name",
}
else:
return relevant_scif
for name in names_of_columns:
relevant_scif = self.filter_scif_specific(
relevant_scif, kpi_line, name, names_of_columns[name])
return relevant_scif
def filter_scif_specific(self, relevant_scif, kpi_line, name_in_template, name_in_scif):
"""
takes scif and filters it from the template
:param relevant_scif: the current filtered scif
:param kpi_line: line from one sheet (availability for example)
:param name_in_template: the column name in the template
:param name_in_scif: the column name in SCIF
:return:
"""
values = self.does_exist(kpi_line, name_in_template)
if values:
if name_in_scif in Const.NUMERIC_VALUES_TYPES:
values = [float(x) for x in values]
return relevant_scif[relevant_scif[name_in_scif].isin(values)]
return relevant_scif
# Share of Scenes functions
def calculate_share_of_scenes(self, kpi_line, relevant_scif):
relevant_scenes_scif = self.get_denominator_scif(kpi_line, relevant_scif)
# we need to get only the scenes from the denominator scif
denominator_scif = \
relevant_scif[relevant_scif['scene_fk'].isin(relevant_scenes_scif['scene_fk'].unique().tolist())]
# the numerator population is applied only to scenes that exist in the denominator population
numerator_scif = self.get_numerator_scif(kpi_line, denominator_scif)
agg_denominator_scif = denominator_scif.groupby('scene_fk', as_index=False)[['facings']].sum()
agg_denominator_scif.rename(columns={'facings': 'den_facings'}, inplace=True)
agg_numerator_scif = numerator_scif.groupby('scene_fk', as_index=False)[['facings']].sum()
agg_numerator_scif.rename(columns={'facings': 'num_facings'}, inplace=True)
results = pd.merge(agg_denominator_scif, agg_numerator_scif, on='scene_fk', how='outer').fillna(0)
results['sos'] = (results['num_facings'] / results['den_facings'])
results['sos'].fillna(0, inplace=True)
threshold = self.does_exist(kpi_line, Const.THRESHOLD)
if threshold:
results = results[results['sos'] >= threshold[0]]
numerator_scenes = len(results[results['sos'] > 0])
denominator_scenes = len(results)
if denominator_scenes > 0:
sos_value = numerator_scenes / float(denominator_scenes)
else:
sos_value = 0
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(kpi_line[Const.KPI_NAME])
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=numerator_scenes,
denominator_id=self.store_id, denominator_result=denominator_scenes,
result=sos_value * 100, identifier_parent=Const.MSC, should_enter=True)
return
# Share of POCs functions
def calculate_share_of_pocs(self, kpi_line, relevant_scif):
numerator_scif = self.get_numerator_scif(kpi_line, relevant_scif)
denominator_scif = self.get_denominator_scif(kpi_line, relevant_scif)
# remove the numerator subset from the denominator
denominator_scif = pd.concat([numerator_scif, denominator_scif]).drop_duplicates(keep=False)
minimum_facings = self.does_exist(kpi_line, Const.MINIMUM_FACINGS)
if minimum_facings:
numerator_scif = numerator_scif[numerator_scif['facings'] >= minimum_facings[0]]
denominator_scif = denominator_scif[denominator_scif['facings'] >= minimum_facings[0]]
numerator_scenes = len(numerator_scif['scene_fk'].unique())
denominator_scenes = len(denominator_scif['scene_fk'].unique())
denominator_value = float(numerator_scenes + denominator_scenes)
if denominator_value > 0:
poc_share = numerator_scenes / denominator_value
else:
poc_share = 0
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(kpi_line[Const.KPI_NAME])
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=numerator_scenes,
denominator_id=self.store_id, denominator_result=denominator_value,
result=poc_share * 100, identifier_parent=Const.MSC, should_enter=True)
return
# helper functions
def get_kpi_function(self, kpi_type):
"""
transfers every kpi to its own function
:param kpi_type: value from "sheet" column in the main sheet
:return: function
"""
if kpi_type == Const.AVAILABILITY:
return self.calculate_availability
elif kpi_type == Const.DOUBLE_AVAILABILITY:
return self.calculate_double_availability
elif kpi_type == Const.PER_SCENE_AVAILABILITY:
return self.calculate_per_scene_availability
elif kpi_type == Const.FACINGS:
return self.calculate_facings
elif kpi_type == Const.SHARE_OF_SCENES:
return self.calculate_share_of_scenes
elif kpi_type == Const.SHARE_OF_POCS:
return self.calculate_share_of_pocs
else:
Log.warning(
"The value '{}' in column sheet in the template is not recognized".format(kpi_type))
return None
@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 [x.strip() for x in cell.split(",")]
return None
def get_numerator_scif(self, kpi_line, denominator_scif):
numerator_scif = self.filter_scif_by_template_columns(kpi_line, Const.NUM_TYPE, Const.NUM_VALUE,
denominator_scif)
numerator_scif = self.filter_scif_by_template_columns(kpi_line, Const.NUM_EXCLUDE_TYPE, Const.NUM_EXCLUDE_VALUE,
numerator_scif, exclude=True)
numerator_scif = self.filter_scif_by_template_columns(kpi_line, Const.EXCLUDED_TYPE, Const.EXCLUDED_VALUE,
numerator_scif, exclude=True)
return numerator_scif
def get_denominator_scif(self, kpi_line, relevant_scif):
denominator_scif = self.filter_scif_by_template_columns(kpi_line, Const.DEN_TYPE, Const.DEN_VALUE,
relevant_scif)
denominator_scif = self.filter_scif_by_template_columns(kpi_line, Const.EXCLUDED_TYPE, Const.EXCLUDED_VALUE,
denominator_scif, exclude=True)
return denominator_scif
@staticmethod
def filter_scif_by_template_columns(kpi_line, type_base, value_base, relevant_scif, exclude=False):
filters = {}
# get denominator filters
for den_column in [col for col in kpi_line.keys() if type_base in col]: # get relevant den columns
if kpi_line[den_column]: # check to make sure this kpi has this denominator param
filters[kpi_line[den_column]] = \
[value.strip() for value in kpi_line[den_column.replace(type_base, value_base)].split(
',')] # get associated values
for key in filters.iterkeys():
if key not in relevant_scif.columns.tolist():
Log.error('{} is not a valid parameter type'.format(key))
continue
if exclude:
relevant_scif = relevant_scif[~(relevant_scif[key].isin(filters[key]))]
else:
relevant_scif = relevant_scif[relevant_scif[key].isin(filters[key])]
return relevant_scif
class ToolBox(GlobalSessionToolBox):
def __init__(self, data_provider, output):
GlobalSessionToolBox.__init__(self, data_provider, output)
self.assortment = Assortment(self.data_provider)
self.ps_data = PsDataProvider(self.data_provider, self.output)
self.result_values = self.ps_data.get_result_values()
self.db_handler = DBHandler(self.data_provider.project_name, self.data_provider.session_uid)
def main_calculation(self):
# facings
facings_with_stacking_res = self.category_facings(Consts.FACINGS, Consts.FACINGS_STACKING_KPI)
self.common.save_json_to_new_tables(facings_with_stacking_res)
facings_ignore_stacking_res = self.category_facings(Consts.FACINGS_IGN_STACK, 'SKU_Facings_ Exclude_Stacking')
self.common.save_json_to_new_tables(facings_ignore_stacking_res)
# sos calculation
sos_res_secondary = self.sos_calculation_secondary()
self.common.save_json_to_new_tables(sos_res_secondary)
sos_res_primary = self.sos_calculation_primary()
self.common.save_json_to_new_tables(sos_res_primary)
# Assortment base availabilities kpis
lvl3_result = self.assortment.calculate_lvl3_assortment(False)
availability_res = self.assortment_calculation(lvl3_result)
self.common.save_json_to_new_tables(availability_res)
wine_res = self.wine_availability_calculation(lvl3_result)
self.common.save_json_to_new_tables(wine_res)
# commit all results to db
self.common.commit_results_data()
def sos_calculation_secondary(self):
sos_results = []
manu_from_store_fk = self.common.get_kpi_fk_by_kpi_type('SOS OWN MANUFACTURER OUT OF STORE - SECONDARY SHELF')
manu_from_store_res = OwnManufacturerSecondaryFacingsSOSInStore(data_provider=self.data_provider,
kpi_definition_fk=manu_from_store_fk,
categories_include=Consts.SOS_CATEGORY).calculate()
manu_store_df = self.prepare_table_for_res(manu_from_store_res, False)
manu_store_df = self.assign_parent_child_identifiers(manu_store_df,
result_identifier={'val': manu_from_store_fk})
sos_results.extend(manu_store_df.to_dict('records'))
category_from_store_fk = self.common.get_kpi_fk_by_kpi_type('SOS CATEGORY OUT OF STORE - SECONDARY SHELF')
category_from_store_res = CategorySecondaryFacingsSOSInStore(data_provider=self.data_provider,
kpi_definition_fk=category_from_store_fk,
categories_include=Consts.SOS_CATEGORY).calculate()
category_from_store_df = self.prepare_table_for_res(category_from_store_res, True)
category_from_store_df = self.assign_parent_child_identifiers(category_from_store_df, parent_identifier={
'val': manu_from_store_fk}, result_identifier={'col': ['numerator_id', 'denominator_id'], 'val':
category_from_store_fk})
sos_results.extend(category_from_store_df.to_dict('records'))
manu_from_cat_fk = self.common.get_kpi_fk_by_kpi_type('SOS MANUFACTURER OUT OF CATEGORY - SECONDARY SHELF')
manu_from_cat_res = ManufacturerSecondaryFacingsSOSPerCategory(data_provider=self.data_provider,
kpi_definition_fk=manu_from_cat_fk,
categories_include=Consts.SOS_CATEGORY).calculate()
manu_from_cat_df = self.prepare_table_for_res(manu_from_cat_res, True)
manu_from_cat_df = self.assign_parent_child_identifiers(manu_from_cat_df,
parent_identifier={'col': ['denominator_id',
'context_id'], 'val':
category_from_store_fk},
result_identifier={'col': ['numerator_id',
'denominator_id'], 'val':
manu_from_cat_fk})
sos_results.extend(manu_from_cat_df.to_dict('records'))
barnd_out_manu_fk = self.common.get_kpi_fk_by_kpi_type(
'SOS BRAND OUT OF MANUFACTURER OUT OF CATEGORY - SECONDARY SHELF')
brand_out_manu_res = BrandOutManufacturerOutCategorySecondaryFacingsSOS(data_provider=self.data_provider,
kpi_definition_fk=barnd_out_manu_fk,
categories_include=Consts.SOS_CATEGORY).calculate()
brand_out_manu_df = self.prepare_table_for_res(brand_out_manu_res, True)
brand_out_manu_df = self.assign_parent_child_identifiers(brand_out_manu_df,
parent_identifier={'col':
['denominator_id',
'context_id'],
'val': manu_from_cat_fk})
sos_results.extend(brand_out_manu_df.to_dict('records'))
return sos_results
def sos_calculation_primary(self):
sos_results = []
manu_from_store_fk = self.common.get_kpi_fk_by_kpi_type(
'SOS OWN MANUFACTURER OUT OF STORE - PRIMARY SHELF')
manu_from_store_res = OwnManufacturerPrimaryFacingsSOSInStore(data_provider=self.data_provider,
kpi_definition_fk=manu_from_store_fk,
categories_include=Consts.SOS_CATEGORY).calculate()
manu_store_df = self.prepare_table_for_res(manu_from_store_res, False)
manu_store_df = self.assign_parent_child_identifiers(manu_store_df,
result_identifier={'val': manu_from_store_fk})
sos_results.extend(manu_store_df.to_dict('records'))
category_from_store_fk = self.common.get_kpi_fk_by_kpi_type('SOS CATEGORY OUT OF STORE - PRIMARY SHELF')
category_from_store_res = CategoryPrimaryFacingsSOSInStore(data_provider=self.data_provider,
kpi_definition_fk=category_from_store_fk,
categories_include=Consts.SOS_CATEGORY).calculate()
category_from_store_df = self.prepare_table_for_res(category_from_store_res, True)
category_from_store_df = self.assign_parent_child_identifiers(category_from_store_df, parent_identifier={
'val': manu_from_store_fk}, result_identifier={'col': ['numerator_id', 'denominator_id'], 'val':
category_from_store_fk})
sos_results.extend(category_from_store_df.to_dict('records'))
manu_from_cat_fk = self.common.get_kpi_fk_by_kpi_type('SOS MANUFACTURER OUT OF CATEGORY - PRIMARY SHELF')
manu_from_cat_res = ManufacturerPrimaryFacingsSOSPerCategory(data_provider=self.data_provider,
kpi_definition_fk=manu_from_cat_fk,
categories_include=Consts.SOS_CATEGORY).calculate()
manu_from_cat_df = self.prepare_table_for_res(manu_from_cat_res, True)
manu_from_cat_df = self.assign_parent_child_identifiers(manu_from_cat_df,
parent_identifier={'col': ['denominator_id',
'context_id'], 'val':
category_from_store_fk},
result_identifier={'col': ['numerator_id',
'denominator_id'], 'val':
manu_from_cat_fk})
sos_results.extend(manu_from_cat_df.to_dict('records'))
barnd_out_manu_fk = self.common.get_kpi_fk_by_kpi_type(
'SOS BRAND OUT OF MANUFACTURER OUT OF CATEGORY - PRIMARY SHELF')
brand_out_manu_res = BrandOutManufacturerOutCategoryPrimaryFacingsSOS(data_provider=self.data_provider,
kpi_definition_fk=barnd_out_manu_fk,
categories_include=Consts.SOS_CATEGORY).calculate()
brand_out_manu_df = self.prepare_table_for_res(brand_out_manu_res, True)
brand_out_manu_df = self.assign_parent_child_identifiers(brand_out_manu_df,
parent_identifier={'col':
['denominator_id',
'context_id'],
'val': manu_from_cat_fk})
sos_results.extend(brand_out_manu_df.to_dict('records'))
return sos_results
def assign_parent_child_identifiers(self, df, parent_identifier=None, result_identifier=None):
"""
This function extract from parent identifier and result identifier the values from the dict by keys:val and col
It sendt it to assign_cols func which add the identifiers to the df
"""
if parent_identifier:
df = self.assign_cols(df, 'identifier_parent', parent_identifier.get('col', None),
parent_identifier.get('val',
''))
if result_identifier:
df = self.assign_cols(df, 'identifier_result', result_identifier.get('col', None),
result_identifier.get('val', ''))
return df
@staticmethod
def assign_cols(df, identifier, col_to_assign, val_to_assign):
"""
function add identifer column and determine the column value ,
identifier will be string = combination of other columns in df and value received
:param df:
:param identifier: string 'identifier_parent'/ 'identifier_result'
:param col_to_assign: array of columns name.
:param val_to_assign: string/int
:return:
"""
if col_to_assign:
df.loc[:, identifier] = df[col_to_assign].apply(lambda x: ' '.join(x.astype(str)), axis=1) + ' ' + str(
val_to_assign)
else:
df.loc[:, identifier] = str(val_to_assign)
return df
@staticmethod
def prepare_table_for_res(res_dict, should_enter):
"""This function takes the df results which returns from sos classes and change it to be a db result dict"""
df = pd.DataFrame(
[res.to_dict if type(res) != dict else res for res in res_dict]) # need to add parent identifer
df.loc[:, 'session_fk'] = df['fk']
df.loc[:, 'fk'] = df['kpi_definition_fk']
df.loc[:, 'should_enter'] = should_enter
return df
def wine_availability_calculation(self, lvl3_result):
"""This function calcualte the wine availability in sku level and store level"""
wine_res = []
lvl3_result = self.filter_by_kpis(lvl3_result, Consts.WINE_LEVEL_2_NAMES)
if lvl3_result.empty:
return wine_res
wine_sku = self.distribution_sku_level(lvl3_result, Consts.WINE_SKU_LVL, {'col': ['kpi_fk_lvl2']})
wine_res.extend(wine_sku.to_dict('records'))
store_res = self.wine_store_assortment(lvl3_result, Consts.WINE_STORE_LEVEL)
wine_res.extend(store_res.to_dict('records'))
return wine_res
def filter_by_kpis(self, lvl3_result, kpi_types):
""" filter assortment df from irrelevant kpis """
distribution_kpis = [self.common.get_kpi_fk_by_kpi_type(kpi_name) for kpi_name in kpi_types]
lvl3_result = lvl3_result[lvl3_result['kpi_fk_lvl2'].isin(distribution_kpis)]
return lvl3_result
def assortment_category(self, lvl3_result):
""" Combine assortment df to lvl3_result"""
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()]
return lvl3_with_cat
def assortment_calculation(self, lvl3_result):
ava_res = []
lvl3_result = self.filter_by_kpis(lvl3_result, Consts.DISTRIBUTION_LEVEL_2_NAMES)
lvl3_result = self.update_by_oos_reason(lvl3_result)
if lvl3_result.empty:
return ava_res
# level 3 results - SKU LEVEL
lvl3_with_cat = self.assortment_category(lvl3_result)
sku_results = self.distribution_sku_level(lvl3_with_cat, Consts.DIST_SKU_LVL,
{'col': ['kpi_fk_lvl2', 'category_fk'], 'val': Consts.DISTRIBUTION})
ava_res = self.append_to_res(ava_res, sku_results, Consts.SKU_LEVEL)
oos_sku_res = self.sku_oos(sku_results)
ava_res = self.append_to_res(ava_res, oos_sku_res, Consts.SKU_LEVEL)
# calculate level 2 results - GROUP LEVEL
group_result = self.calculate_lvl2_assortment(lvl3_with_cat, Consts.LVL2_GROUP_HEADERS)
oos_group_res = self.calculate_oos_lvl2(group_result)
group_result = self.assign_parent_child_identifiers(group_result, parent_identifier={'col': ['category_fk'],
'val': Consts.DISTRIBUTION},
result_identifier={'col': ['kpi_fk_lvl2',
'category_fk'], 'val': Consts.DISTRIBUTION})
group_result = self.group_level(group_result, Consts.DIST_GROUP_LVL)
oos_group_res = self.assign_parent_child_identifiers(oos_group_res, parent_identifier={'col': ['category_fk'],
'val': Consts.OOS},
result_identifier={'col': ['kpi_fk_lvl2', 'category_fk'], 'val':
Consts.OOS})
oos_group_res = self.group_level(oos_group_res, Consts.OOS_GROUP_LVL)
ava_res.extend(group_result.to_dict('records'))
ava_res.extend(oos_group_res.to_dict('records'))
# calculate - CATEGORY LEVEL
category_result = self.calculate_category_result(group_result, Consts.DISTRIBUTION)
ava_res.extend(category_result.to_dict('records'))
category_result_oos = self.calculate_category_result(oos_group_res, Consts.OOS)
ava_res.extend(category_result_oos.to_dict('records'))
# calculate - STORE LEVEL
store_result = self.calculate_store_assortment(category_result, Consts.DIST_STORE_LVL)
oos_store_result = self.calculate_store_assortment(category_result_oos, Consts.OOS_STORE_LVL)
ava_res.extend(store_result.to_dict('records'))
ava_res.extend(oos_store_result.to_dict('records'))
return ava_res
def distribution_sku_level(self, lvl_3_result, kpi_name, identifiers):
""" This function receive df = lvl_3_result assortment with data regarding the assortment products
This function turn the sku_assortment_results to be in a shape of db result.
return distribution_db_results df
"""
sku_results = lvl_3_result.rename(
columns={'product_fk': 'numerator_id', 'assortment_group_fk': 'denominator_id',
'in_store': 'result', 'kpi_fk_lvl3': 'fk', 'facings':
'numerator_result'}, inplace=False)
sku_results.loc[:, 'result'] = sku_results.apply(lambda row: self.kpi_result_value(row.result), axis=1)
sku_results = sku_results.assign(denominator_result=1,
score=sku_results['result'])
sku_results.loc[:, 'should_enter'] = True
sku_results['fk'] = self.common.get_kpi_fk_by_kpi_type(kpi_name)
self.assign_parent_child_identifiers(sku_results, identifiers)
Log.info('Distribution sku level is done ')
return sku_results
def sku_oos(self, sku_results):
oos_res = sku_results[sku_results['result'] == self.kpi_result_value(0)]
oos_res.loc[:, 'fk'] = self.common.get_kpi_fk_by_kpi_type(Consts.OOS_SKU_LVL)
self.assign_parent_child_identifiers(oos_res, {'col': ['kpi_fk_lvl2', 'category_fk'], 'val': 'OOS'})
return oos_res
def update_by_oos_reason(self, lvl3_results):
"""
This function change assortment results if products has oos reason.
Product with oos reason : Distribuiert , will be treated as distributed
Product with oos reason : Nicht distribuiert (technisches Problem), will be removed from assortment list
"""
if self.data_provider.session_info.status.values[0] == Consts.COMPLETED_STATUS:
oos_reason = self.db_handler.get_oos_reasons_for_session(self.session_uid)
if oos_reason.empty:
return lvl3_results
remove_products = oos_reason[oos_reason['message'] == Consts.REMOVE_REASON][
'product_fk'].unique()
distriubted_products = oos_reason[oos_reason['message'] == Consts.DISTRIBUTED_REASON]['product_fk'].unique()
lvl3_results = lvl3_results[~lvl3_results['product_fk'].isin(remove_products)]
lvl3_results.loc[lvl3_results['product_fk'].isin(distriubted_products), 'in_store'] = 1
return lvl3_results
def group_level(self, lvl_2_result, kpi_name):
""" This function receive df = lvl_3_result assortment with data regarding the assortment products
This function turn the sku_assortment_results to be in a shape of db result.
return distribution_db_results df
"""
group_results = lvl_2_result.rename(
columns={'kpi_fk_lvl2': 'numerator_id', 'category_fk': 'denominator_id',
'total': 'denominator_result', 'passes': 'numerator_result'}, inplace=False)
group_results.loc[:, 'result'] = group_results['numerator_result'] / group_results['denominator_result']
group_results = group_results.assign(score=group_results['result'])
group_results.loc[:, 'fk'] = self.common.get_kpi_fk_by_kpi_type(kpi_name)
group_results = self.filter_df_by_col(group_results, Consts.GROUPS_LEVEL)
Log.info('Group level is done ')
return group_results
def filter_df_by_col(self, df, level):
"""
:param df: df results lvl2 / lvl3 assortment results
:param level: sku / group level
:return:filtered df
"""
if level == Consts.SKU_LEVEL:
return df[Consts.LVL3_SESSION_RESULTS_COL]
if level == Consts.GROUPS_LEVEL:
return df[Consts.LVL2_SESSION_RESULTS_COL]
if level == Consts.STORE_LEVEL:
return df[Consts.LVL1_SESSION_RESULTS_COL]
def kpi_result_value(self, value):
"""
:param value: availability kpi result 0 for oos and 1 for distrbution
Function retrieve the kpi_result_value needed for Availability KPIS
(kpi result value match to mobile report signs) , according to the kpi result.
:return pk of kpi_result_value
"""
value = Consts.OOS if value == 0 else Consts.DISTRIBUTION
value_info = self.result_values[self.result_values['value'] == value]
if value_info.empty:
return
return value_info.pk.iloc[0]
@staticmethod
def calculate_oos_lvl2(lvl2_res):
""" create df for oos level based on distribution results
:param lvl2_res - assortment df results in sku level
"""
oos_res = lvl2_res.copy()
oos_res['passes'] = oos_res['total'] - oos_res['passes']
return oos_res
@staticmethod
def calculate_lvl2_assortment(lvl3_assortment, group_by_cols):
"""
:param lvl3_assortment: return value of 'calculate_lvl3_assortment' func.
:return: data frame on the assortment group level with the following fields:
['assortment_super_group_fk', 'assortment_group_fk', 'assortment_fk', 'target', 'passes',
'total', 'kpi_fk_lvl1', 'kpi_fk_lvl2', 'group_target_date', 'super_group_target'].
Indicates for each assortment group how many products were in the store (passes) out of the total\ target
(total\ target).
"""
lvl3_with_cat = lvl3_assortment.copy()
lvl3_with_cat = lvl3_with_cat.fillna(Consts.EMPTY_VAL)
lvl2_res = lvl3_with_cat.groupby(group_by_cols)['in_store'].agg(
[('total', 'count'), ('passes', 'sum')]).reset_index()
lvl2_res.loc[:, 'should_enter'] = True
return lvl2_res
def calculate_category_result(self, group_result, parent_iden):
"""Create df results for assortment kpi in category level
:param group_result - db results for assortment kpi in group level
:param parent_iden - 'OOS'/'DIST , kpi identifier between oos and dist
"""
category_result = group_result.groupby(['denominator_id'])['numerator_result', 'denominator_result'].agg(
{'numerator_result': 'sum', 'denominator_result': 'sum'}).reset_index()
category_result.loc[:, 'result'] = category_result['numerator_result'] / category_result['denominator_result']
category_result = category_result.rename(columns={'denominator_id': 'numerator_id'},
inplace=False)
category_result.loc[:, 'denominator_id'] = self.manufacturer_fk
kpi_name = Consts.OOS_CAT_LVL if parent_iden == Consts.OOS else Consts.DIST_CAT_LVL
category_result.loc[:, 'fk'] = self.common.get_kpi_fk_by_kpi_type(kpi_name)
category_result = category_result.assign(score=category_result['result'])
category_result = self.assign_parent_child_identifiers(category_result,
result_identifier={'col': ['numerator_id'], 'val': parent_iden},
parent_identifier={'val': parent_iden})
category_result.loc[:, 'should_enter'] = True
return category_result
def calculate_store_assortment(self, lvl_2_res, kpi_name):
""" Calculate assortment in store level
:param lvl_2_res- lvl2 result , df of results from granular group level/ category level
:param kpi_name - kpi type
"""
store_assortment = lvl_2_res.groupby(['fk'])['numerator_result', 'denominator_result'].agg(
{'numerator_result': 'sum', 'denominator_result': 'sum'}).reset_index()
store_assortment.loc[:, 'result'] = store_assortment['numerator_result'] / store_assortment['denominator_result']
store_assortment.loc[:, 'denominator_id'] = self.store_id
store_assortment.loc[:, 'numerator_id'] = self.manufacturer_fk
store_assortment.loc[:, 'fk'] = self.common.get_kpi_fk_by_kpi_type(kpi_name)
store_assortment.loc[:, 'should_enter'] = False
kpi_identifier = Consts.DISTRIBUTION if kpi_name == Consts.DIST_STORE_LVL else Consts.OOS
self.assign_parent_child_identifiers(store_assortment, result_identifier={'val': kpi_identifier})
return store_assortment
def wine_store_assortment(self, wine_lvl3_res, kpi_type):
""" Create df results for wine assortment kpi in store level
:param kpi_type - kpi name
:param wine_lvl3_res - df of lvl3_results
"""
lvl2_result = self.calculate_lvl2_assortment(wine_lvl3_res, Consts.WINE_GROUP_HEADERS)
self.assign_parent_child_identifiers(lvl2_result, result_identifier={'col': ['kpi_fk_lvl2']})
lvl2_result.loc[:, 'denominator_id'] = self.store_id
lvl2_result.loc[:, 'numerator_id'] = self.manufacturer_fk
lvl2_result = lvl2_result.rename(
columns={'total': 'denominator_result', 'passes': 'numerator_result'}, inplace=False)
lvl2_result.loc[:, 'fk'] = self.common.get_kpi_fk_by_kpi_type(kpi_type)
lvl2_result.loc[:, 'result'] = lvl2_result['numerator_result'] / lvl2_result['denominator_result']
store_results = lvl2_result.assign(score=lvl2_result['result'])
store_results = self.filter_df_by_col(store_results, Consts.STORE_LEVEL)
return store_results
def category_facings(self, facings, kpi_name):
""" return df results with facings count per kpi
:param facings : 'facings' / 'facings'
:param kpi_name : kpi type from df
"""
filterd_scif = self.scif[~ self.scif['product_type'].isin(Consts.IGNORE_PRODUCT_TYPE) & self.scif[
'category'].isin(Consts.FACINGS_CATEGORIES)]
results = filterd_scif.groupby(['product_fk'])[facings].agg(
{facings: 'sum'}).reset_index()
results.loc[:, 'denominator_id'] = self.store_id
results.loc[:, 'fk'] = self.common.get_kpi_fk_by_kpi_type(kpi_name)
results = results.rename(columns={facings: 'result', 'numerator_id': 'product_fk'})
results = results.assign(score=results['result'])
return results.to_dict('records')
def append_to_res(self, total_res, res, level):
sku_results = self.filter_df_by_col(res, level)
total_res.extend(sku_results.to_dict('records'))
return total_res
class REDToolBox:
def __init__(self, data_provider, output, calculation_type, common_db2):
self.output = output
self.data_provider = 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.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.scif = self.scif[self.scif['product_type'] != "Irrelevant"]
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.templates = {}
self.result_values = self.ps_data_provider.get_result_values()
self.calculation_type = calculation_type
if self.calculation_type == Const.SOVI:
self.TEMPLATE_PATH = Const.TEMPLATE_PATH
self.RED_SCORE = Const.RED_SCORE
self.RED_SCORE_INTEG = Const.RED_SCORE_INTEG
for sheet in Const.SHEETS:
self.templates[sheet] = pd.read_excel(self.TEMPLATE_PATH, sheetname=sheet).fillna('')
self.converters = self.templates[Const.CONVERTERS]
self.scenes_results = self.ps_data_provider.get_scene_results(
self.scene_info['scene_fk'].drop_duplicates().values)
self.scenes_results = self.scenes_results[[Const.DB_RESULT, Const.DB_SCENE_FK, Const.DB_SCENE_KPI_FK]]
else:
self.TEMPLATE_PATH = Const.SURVEY_TEMPLATE_PATH
self.RED_SCORE = Const.MANUAL_RED_SCORE
self.RED_SCORE_INTEG = Const.MANUAL_RED_SCORE_INTEG
for sheet in Const.SHEETS_MANUAL:
self.templates[sheet] = pd.read_excel(self.TEMPLATE_PATH, sheetname=sheet).fillna('')
self.store_attr = self.store_info['additional_attribute_15'].iloc[0]
self.toolbox = FunctionsToolBox(self.data_provider, self.output, self.templates, self.store_attr)
self.common_db_integ = Common(self.data_provider, self.RED_SCORE_INTEG)
self.kpi_static_data_integ = self.common_db_integ.get_kpi_static_data()
self.common_db = Common(self.data_provider, self.RED_SCORE)
self.common_db2 = common_db2
self.region = self.store_info['region_name'].iloc[0]
self.store_type = self.store_info['store_type'].iloc[0]
if self.store_type in Const.STORE_TYPES:
self.store_type = Const.STORE_TYPES[self.store_type]
self.kpi_static_data = self.common_db.get_kpi_static_data()
main_template = self.templates[Const.KPIS]
self.templates[Const.KPIS] = main_template[(main_template[Const.REGION] == self.region) &
(main_template[Const.STORE_TYPE] == self.store_type)]
self.session_results = pd.DataFrame(columns=Const.COLUMNS_OF_RESULTS)
self.all_results = pd.DataFrame(columns=Const.COLUMNS_OF_RESULTS)
self.used_scenes = []
self.red_score = 0
self.set_fk = self.common_db2.get_kpi_fk_by_kpi_name(self.RED_SCORE)
self.set_integ_fk = self.common_db2.get_kpi_fk_by_kpi_name(self.RED_SCORE_INTEG)
self.weight_factor = self.get_weight_factor()
# main functions:
def main_calculation(self, *args, **kwargs):
"""
This function gets all the scene results from the SceneKPI, after that calculates every session's KPI,
and in the end it calls "filter results" to choose every KPI and scene and write the results in DB.
"""
main_template = self.templates[Const.KPIS]
if self.calculation_type == Const.SOVI:
session_template = main_template[main_template[Const.SESSION_LEVEL] == Const.V]
for i, main_line in session_template.iterrows():
self.calculate_main_kpi(main_line)
else:
for i, main_line in main_template.iterrows():
self.calculate_manual_kpi(main_line)
if not main_template.empty:
self.choose_and_write_results()
return self.red_score
def calculate_main_kpi(self, main_line):
"""
This function gets a line from the main_sheet, transfers it to the match function, and checks all of the
KPIs in the same name in the match sheet.
:param main_line: series from the template of the main_sheet.
"""
kpi_name = main_line[Const.KPI_NAME]
relevant_scif = self.scif
scene_types = self.toolbox.does_exist(main_line, Const.SCENE_TYPE)
if scene_types:
relevant_scif = relevant_scif[relevant_scif['template_name'].isin(scene_types)]
scene_groups = self.toolbox.does_exist(main_line, Const.SCENE_TYPE_GROUP)
if scene_groups:
relevant_scif = relevant_scif[relevant_scif['template_group'].isin(scene_groups)]
if main_line[Const.SHEET] == Const.SCENE_AVAILABILITY:
result = False if relevant_scif.empty else True
else:
result = self.toolbox.calculate_kpi_by_type(main_line, relevant_scif)
self.write_to_session_level(kpi_name=kpi_name, result=result)
def calculate_manual_kpi(self, main_line):
"""
This function gets a line from the main_sheet, transfers it to the match function, and checks all of the
KPIs in the same name in the match sheet.
:param main_line: series from the template of the main_sheet.
"""
kpi_name = main_line[Const.KPI_NAME]
relevant_template = self.templates[Const.SURVEY]
relevant_template = relevant_template[relevant_template[Const.KPI_NAME] == kpi_name]
target = len(relevant_template) if main_line[Const.GROUP_TARGET] == Const.ALL \
else main_line[Const.GROUP_TARGET]
passed_counter = 0
for i, kpi_line in relevant_template.iterrows():
answer = self.toolbox.calculate_survey_specific(kpi_line)
if answer:
passed_counter += 1
result = passed_counter >= target
self.write_to_session_level(kpi_name=kpi_name, result=result)
# write in DF:
def write_to_session_level(self, kpi_name, result=0):
"""
Writes a result in the DF
:param kpi_name: string
:param result: boolean
"""
result_dict = {Const.KPI_NAME: kpi_name, Const.DB_RESULT: result * 1}
self.session_results = self.session_results.append(result_dict, ignore_index=True)
def write_to_all_levels(self, kpi_name, result, display_text, weight, scene_fk=None, reuse_scene=False):
"""
Writes the final result in the "all" DF, add the score to the red score and writes the KPI in the DB
:param kpi_name: str
:param result: int
:param display_text: str
:param weight: int/float
:param scene_fk: for the scene's kpi
:param reuse_scene: this kpi can use scenes that were used
"""
score = self.get_score(weight)
result_value = Const.PASS if result > 0 else Const.FAIL
if result_value == Const.PASS:
self.red_score += score
result_dict = {Const.KPI_NAME: kpi_name, Const.DB_RESULT: result, Const.SCORE: score}
if scene_fk:
result_dict[Const.DB_SCENE_FK] = scene_fk
if not reuse_scene:
self.used_scenes.append(scene_fk)
self.all_results = self.all_results.append(result_dict, ignore_index=True)
self.write_to_db(kpi_name, score, display_text=display_text, result_value=result_value)
def choose_and_write_results(self):
"""
writes all the KPI in the DB: first the session's ones, second the scene's ones and in the end the ones
that depends on the previous ones. After all it writes the red score
"""
main_template = self.templates[Const.KPIS]
self.write_session_kpis(main_template)
if self.calculation_type == Const.SOVI:
self.write_scene_kpis(main_template)
self.write_condition_kpis(main_template)
self.write_missings(main_template)
self.write_to_db(self.RED_SCORE, self.red_score)
def write_missings(self, main_template):
"""
write 0 in all the KPIs that didn't get score
:param main_template:
"""
for i, main_line in main_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
if not self.all_results[self.all_results[Const.KPI_NAME] == kpi_name].empty:
continue
result = 0
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
self.write_to_all_levels(kpi_name, result, display_text, weight)
def write_session_kpis(self, main_template):
"""
iterates all the session's KPIs and saves them
:param main_template: main_sheet.
"""
session_template = main_template[main_template[Const.CONDITION] == ""]
if self.calculation_type == Const.SOVI:
session_template = session_template[session_template[Const.SESSION_LEVEL] == Const.V]
for i, main_line in session_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
result = self.session_results[self.session_results[Const.KPI_NAME] == kpi_name]
if result.empty:
continue
result = result.iloc[0][Const.DB_RESULT]
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
self.write_to_all_levels(kpi_name, result, display_text, weight)
def write_incremental_kpis(self, scene_template):
"""
lets the incremental KPIs choose their scenes (if they passed).
if KPI passed some scenes, we will choose the scene that the children passed
:param scene_template: filtered main_sheet
:return: the new template (without the KPI written already)
"""
incremental_template = scene_template[scene_template[Const.INCREMENTAL] != ""]
while not incremental_template.empty:
for i, main_line in incremental_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
reuse_scene = main_line[Const.REUSE_SCENE] == Const.V
kpi_scene_fk = self.common_db2.get_kpi_fk_by_kpi_name(kpi_name + Const.SCENE_SUFFIX)
kpi_results = self.scenes_results[self.scenes_results[Const.DB_SCENE_KPI_FK] == kpi_scene_fk]
if not reuse_scene:
kpi_results = kpi_results[~(kpi_results[Const.DB_SCENE_FK].isin(self.used_scenes))]
true_results = kpi_results[kpi_results[Const.DB_RESULT] > 0]
increments = main_line[Const.INCREMENTAL]
if ', ' in increments:
first_kpi = increments.split(', ')[0]
others = increments.replace(', '.format(first_kpi), '')
scene_template.loc[scene_template[Const.KPI_NAME] == first_kpi, Const.INCREMENTAL] = others
if true_results.empty:
scene_template.loc[scene_template[Const.KPI_NAME] == kpi_name, Const.INCREMENTAL] = ""
else:
true_results = true_results.sort_values(by=Const.DB_RESULT, ascending=False)
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
scene_fk = true_results.iloc[0][Const.DB_SCENE_FK]
self.write_to_all_levels(kpi_name, true_results.iloc[0][Const.DB_RESULT], display_text,
weight, scene_fk=scene_fk, reuse_scene=reuse_scene)
scene_template = scene_template[~(scene_template[Const.KPI_NAME] == kpi_name)]
incremental_template = scene_template[scene_template[Const.INCREMENTAL] != ""]
return scene_template
def write_regular_scene_kpis(self, scene_template):
"""
lets the regular KPIs choose their scenes (if they passed).
Like in the incremental part - if KPI passed some scenes, we will choose the scene that the children passed
:param scene_template: filtered main_sheet (only scene KPIs, and without the passed incremental)
:return: the new template (without the KPI written already)
"""
for i, main_line in scene_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
reuse_scene = main_line[Const.REUSE_SCENE] == Const.V
kpi_scene_fk = self.common_db2.get_kpi_fk_by_kpi_name(kpi_name + Const.SCENE_SUFFIX)
kpi_results = self.scenes_results[self.scenes_results[Const.DB_SCENE_KPI_FK] == kpi_scene_fk]
if not reuse_scene:
kpi_results = kpi_results[~(kpi_results[Const.DB_SCENE_FK].isin(self.used_scenes))]
true_results = kpi_results[kpi_results[Const.DB_RESULT] > 0]
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
if true_results.empty:
continue
true_results = true_results.sort_values(by=Const.DB_RESULT, ascending=False)
scene_fk = true_results.iloc[0][Const.DB_SCENE_FK]
self.write_to_all_levels(kpi_name, true_results.iloc[0][Const.DB_RESULT], display_text, weight,
scene_fk=scene_fk, reuse_scene=reuse_scene)
scene_template = scene_template[~(scene_template[Const.KPI_NAME] == kpi_name)]
return scene_template
def write_not_passed_scene_kpis(self, scene_template):
"""
lets the KPIs not passed choose their scenes.
:param scene_template: filtered main_sheet (only scene KPIs, and without the passed KPIs)
"""
for i, main_line in scene_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
kpi_scene_fk = self.common_db2.get_kpi_fk_by_kpi_name(kpi_name + Const.SCENE_SUFFIX)
reuse_scene = main_line[Const.REUSE_SCENE] == Const.V
kpi_results = self.scenes_results[self.scenes_results[Const.DB_SCENE_KPI_FK] == kpi_scene_fk]
if not reuse_scene:
kpi_results = kpi_results[~(kpi_results[Const.DB_SCENE_FK].isin(self.used_scenes))]
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
if kpi_results.empty:
continue
scene_fk = kpi_results.iloc[0][Const.DB_SCENE_FK]
self.write_to_all_levels(kpi_name, 0, display_text, weight, scene_fk=scene_fk, reuse_scene=reuse_scene)
def write_scene_kpis(self, main_template):
"""
iterates every scene_kpi that does not depend on others, and choose the scene they will take:
1. the incrementals take their scene (if they passed).
2. the regular KPIs that passed choose their scenes.
3. the ones that didn't pass choose their random scenes.
:param main_template: main_sheet.
"""
scene_template = main_template[(main_template[Const.SESSION_LEVEL] != Const.V) &
(main_template[Const.CONDITION] == "")]
scene_template = self.write_incremental_kpis(scene_template)
scene_template = self.write_regular_scene_kpis(scene_template)
self.write_not_passed_scene_kpis(scene_template)
def write_condition_kpis(self, main_template):
"""
writes all the KPI that depend on other KPIs by checking if the parent KPI has passed and in which scene.
:param main_template: main_sheet
"""
condition_template = main_template[main_template[Const.CONDITION] != '']
for i, main_line in condition_template.iterrows():
condition = main_line[Const.CONDITION]
kpi_name = main_line[Const.KPI_NAME]
if self.calculation_type == Const.MANUAL or main_line[Const.SESSION_LEVEL] == Const.V:
kpi_results = self.session_results[self.session_results[Const.KPI_NAME] == kpi_name]
else:
kpi_scene_fk = self.common_db2.get_kpi_fk_by_kpi_name(kpi_name + Const.SCENE_SUFFIX)
kpi_results = self.scenes_results[self.scenes_results[Const.DB_SCENE_KPI_FK] == kpi_scene_fk]
condition_result = self.all_results[(self.all_results[Const.KPI_NAME] == condition) &
(self.all_results[Const.DB_RESULT] > 0)]
if condition_result.empty:
continue
condition_result = condition_result.iloc[0]
if Const.DB_SCENE_FK in condition_result:
condition_scene = condition_result[Const.DB_SCENE_FK]
else:
condition_scene = None
if condition_scene and Const.DB_SCENE_FK in kpi_results:
results = kpi_results[kpi_results[Const.DB_SCENE_FK] == condition_scene]
else:
results = kpi_results
if results.empty:
continue
result = results.iloc[0][Const.DB_RESULT]
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
scene_fk = results.iloc[0][Const.DB_SCENE_FK] if Const.DB_SCENE_FK in kpi_results else None
self.write_to_all_levels(kpi_name, result, display_text, weight, scene_fk=scene_fk)
def get_weight_factor(self):
sum_weights = self.templates[Const.KPIS][Const.WEIGHT].sum()
return sum_weights / 100.0
def get_score(self, weight):
return weight / self.weight_factor
def get_pks_of_result(self, result):
"""
converts string result to its pk (in static.kpi_result_value)
:param result: str
:return: int
"""
pk = self.result_values[self.result_values['value'] == result]['pk'].iloc[0]
return pk
@staticmethod
def get_0_1_of_result(result):
"""
converts string result to its pk (in static.kpi_result_value)
:param result: str
:return: int
"""
pk = 0 if result == Const.FAIL else 1
return pk
def write_to_db(self, kpi_name, score, display_text='', result_value=Const.FAIL):
"""
writes result in the DB
:param kpi_name: str
:param score: float, the weight of the question
:param display_text: str
:param result_value: str, Pass/Fail
"""
if kpi_name == self.RED_SCORE:
self.common_db2.write_to_db_result(
fk=self.set_fk, score=score, numerator_id=Const.MANUFACTURER_FK, denominator_id=self.store_id,
identifier_result=self.common_db2.get_dictionary(kpi_fk=self.set_fk))
self.common_db2.write_to_db_result(
fk=self.set_integ_fk, score=score, numerator_id=Const.MANUFACTURER_FK, denominator_id=self.store_id,
identifier_result=self.common_db2.get_dictionary(kpi_fk=self.set_integ_fk))
self.write_to_db_result(
self.common_db.get_kpi_fk_by_kpi_name(self.RED_SCORE, 1), score=score, level=1)
self.write_to_db_result(
self.common_db_integ.get_kpi_fk_by_kpi_name(self.RED_SCORE_INTEG, 1), score=score, level=1,
set_type=Const.MANUAL)
else:
integ_kpi_fk = self.common_db2.get_kpi_fk_by_kpi_name(kpi_name)
display_kpi_fk = self.common_db2.get_kpi_fk_by_kpi_name(display_text)
if display_kpi_fk is None:
display_kpi_fk = self.common_db2.get_kpi_fk_by_kpi_name(display_text[:100])
result = self.get_pks_of_result(result_value)
self.common_db2.write_to_db_result(
fk=display_kpi_fk, score=score, identifier_parent=self.common_db2.get_dictionary(kpi_fk=self.set_fk),
should_enter=True, result=result, numerator_id=Const.MANUFACTURER_FK, denominator_id=self.store_id)
result = self.get_0_1_of_result(result_value)
self.common_db2.write_to_db_result(
fk=integ_kpi_fk, score=score, should_enter=True, result=result,
identifier_parent=self.common_db2.get_dictionary(kpi_fk=self.set_integ_fk),
numerator_id=Const.MANUFACTURER_FK, denominator_id=self.store_id)
if result_value == Const.FAIL:
score = 0
self.write_to_db_result(
self.common_db.get_kpi_fk_by_kpi_name(kpi_name, 2), score=score, level=2)
self.write_to_db_result(
self.common_db.get_kpi_fk_by_kpi_name(kpi_name, 3), score=score, level=3, display_text=display_text)
self.write_to_db_result(self.common_db_integ.get_kpi_fk_by_kpi_name(
kpi_name, 3), score=score, level=3, display_text=kpi_name, set_type=Const.MANUAL)
def write_to_db_result(self, fk, level, score, set_type=Const.SOVI, **kwargs):
"""
This function creates the result data frame of every KPI (atomic KPI/KPI/KPI set),
and appends the insert SQL query into the queries' list, later to be written to the DB.
"""
if kwargs:
kwargs['score'] = score
attributes = self.create_attributes_dict(fk=fk, level=level, set_type=set_type, **kwargs)
else:
attributes = self.create_attributes_dict(fk=fk, score=score, set_type=set_type, level=level)
if level == self.common_db.LEVEL1:
table = self.common_db.KPS_RESULT
elif level == self.common_db.LEVEL2:
table = self.common_db.KPK_RESULT
elif level == self.common_db.LEVEL3:
table = self.common_db.KPI_RESULT
else:
return
query = insert(attributes, table)
if set_type == Const.SOVI:
self.common_db.kpi_results_queries.append(query)
else:
self.common_db_integ.kpi_results_queries.append(query)
def create_attributes_dict(self, score, fk=None, level=None, display_text=None, set_type=Const.SOVI, **kwargs):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
or
you can send dict with all values in kwargs
"""
kpi_static_data = self.kpi_static_data if set_type == Const.SOVI else self.kpi_static_data_integ
if level == self.common_db.LEVEL1:
if kwargs:
kwargs['score'] = score
values = [val for val in kwargs.values()]
col = [col for col in kwargs.keys()]
attributes = pd.DataFrame(values, columns=col)
else:
kpi_set_name = kpi_static_data[kpi_static_data['kpi_set_fk'] == fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame(
[(kpi_set_name, self.session_uid, self.store_id, self.visit_date.isoformat(),
format(score, '.2f'), fk)],
columns=['kps_name', 'session_uid', 'store_fk', 'visit_date', 'score_1', 'kpi_set_fk'])
elif level == self.common_db.LEVEL2:
if kwargs:
kwargs['score'] = score
values = [val for val in kwargs.values()]
col = [col for col in kwargs.keys()]
attributes = pd.DataFrame(values, columns=col)
else:
kpi_name = kpi_static_data[kpi_static_data['kpi_fk'] == fk]['kpi_name'].values[0].replace("'", "\\'")
attributes = pd.DataFrame(
[(self.session_uid, self.store_id, self.visit_date.isoformat(), fk, kpi_name, score)],
columns=['session_uid', 'store_fk', 'visit_date', 'kpi_fk', 'kpk_name', 'score'])
elif level == self.common_db.LEVEL3:
if kwargs:
kwargs['score'] = score
values = tuple([val for val in kwargs.values()])
col = [col for col in kwargs.keys()]
attributes = pd.DataFrame([values], columns=col)
else:
data = kpi_static_data[kpi_static_data['atomic_kpi_fk'] == fk]
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = kpi_static_data[kpi_static_data['atomic_kpi_fk'] == fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame(
[(display_text, self.session_uid, kpi_set_name, self.store_id, self.visit_date.isoformat(),
datetime.utcnow().isoformat(), score, kpi_fk, fk)],
columns=['display_text', 'session_uid', 'kps_name', 'store_fk', 'visit_date',
'calculation_time', 'score', 'kpi_fk', 'atomic_kpi_fk'])
else:
attributes = pd.DataFrame()
return attributes.to_dict()
def remove_queries_of_calculation_type(self):
"""
In case that the session has no results in the SOVI KPIs we are deleting all the queries
and calculating the MANUAL
:return:
"""
self.common_db2.kpi_results = pd.DataFrame(columns=self.common_db2.COLUMNS)
def commit_results(self):
"""
committing the results in both sets
"""
self.common_db.delete_results_data_by_kpi_set()
self.common_db.commit_results_data_without_delete()
if self.common_db_integ:
self.common_db_integ.delete_results_data_by_kpi_set()
self.common_db_integ.commit_results_data_without_delete()
