def __init__(self, data_provider, output):
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.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_type = self.store_info['additional_attribute_1'].values[0]
self.kpi_static_data = self.get_kpi_static_data()
self.set_templates_data = {}
self.match_display_in_scene = self.get_match_display()
self.kpi_results_queries = []
self.scores = {self.LEVEL1: {}, self.LEVEL2: {}, self.LEVEL3: {}}
self.output = output
self.common = Common(self.data_provider)
self.commonV2 = CommonV2(self.data_provider)
self.global_gen = DIAGEOGenerator(self.data_provider, self.output,
self.common)
self.tools = DIAGEOToolBox(
self.data_provider,
output,
match_display_in_scene=self.match_display_in_scene
) # replace the old one
self.diageo_generator = DIAGEOGenerator(self.data_provider,
self.output, self.common)
def __init__(self, data_provider, output):
self.k_engine = BaseCalculationsScript(data_provider, output)
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.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.store_channel = self.store_info['store_type'].values[0]
if self.store_channel:
self.store_channel = self.store_channel.upper()
self.store_type = self.store_info['additional_attribute_1'].values[0]
self.segment = self.get_business_unit_name()
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.match_display_in_scene = self.get_match_display()
self.set_templates_data = {}
self.kpi_static_data = self.get_kpi_static_data()
self.tools = DIAGEOToolBox(self.data_provider, output, match_display_in_scene=self.match_display_in_scene)
self.kpi_results_queries = []
self.common = Common(self.data_provider)
self.commonV2 = CommonV2(self.data_provider)
self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common)
class PENAFLORAR_SANDDIAGEOARToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
ACTIVATION_STANDARD = 'Activation Standard'
def __init__(self, data_provider, output):
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.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_type = self.store_info['additional_attribute_1'].values[0]
self.kpi_static_data = self.get_kpi_static_data()
self.set_templates_data = {}
self.match_display_in_scene = self.get_match_display()
self.kpi_results_queries = []
self.scores = {self.LEVEL1: {}, self.LEVEL2: {}, self.LEVEL3: {}}
self.output = output
self.common = Common(self.data_provider)
self.commonV2 = CommonV2(self.data_provider)
self.global_gen = DIAGEOGenerator(self.data_provider, self.output,
self.common)
self.tools = DIAGEOToolBox(
self.data_provider,
output,
match_display_in_scene=self.match_display_in_scene
) # replace the old one
self.diageo_generator = DIAGEOGenerator(self.data_provider,
self.output, self.common)
def get_kpi_static_data(self):
"""
This function extracts the static KPI data and saves it into one global data frame.
The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
"""
query = DIAGEOQueries.get_all_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
def get_match_display(self):
"""
This function extracts the display matches data and saves it into one global data frame.
The data is taken from probedata.match_display_in_scene.
"""
query = DIAGEOQueries.get_match_display(self.session_uid)
match_display = pd.read_sql_query(query, self.rds_conn.db)
return match_display
def main_calculation(self, set_names):
"""
This function calculates the KPI results.
"""
log_runtime('Updating templates')(self.tools.update_templates)()
# Global assortment kpis
assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2(
)
self.commonV2.save_json_to_new_tables(assortment_res_dict)
for set_name in set_names:
set_score = 0
if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys(
):
try:
self.set_templates_data[
set_name] = self.tools.download_template(set_name)
except:
Log.warning("Couldn't find a template for set name: " +
str(set_name))
continue
# if set_name in ('MPA', 'New Products',):
# set_score = self.calculate_assortment_sets(set_name)
# Global Visible to Customer / Visible to Consumer
if set_name in ('Visible to Customer', 'Visible to Consumer %'):
# Global function
sku_list = filter(
None, self.scif[self.scif['product_type'] ==
'SKU'].product_ean_code.tolist())
res_dict = self.diageo_generator.diageo_global_visible_percentage(
sku_list)
if res_dict:
# Saving to new tables
# parent_res = res_dict[-1]
self.commonV2.save_json_to_new_tables(res_dict)
# Saving to old tables
# result = parent_res['result']
# self.save_level2_and_level3(set_name=set_name, kpi_name=set_name, score=result)
# Saving to old tables
filters = {self.tools.VISIBILITY_PRODUCTS_FIELD: 'Y'}
set_score = self.tools.calculate_visible_percentage(
visible_filters=filters)
self.save_level2_and_level3(set_name, set_name, set_score)
elif set_name in ('Relative Position'):
# Global function
res_dict = self.diageo_generator.diageo_global_relative_position_function(
self.set_templates_data[set_name],
location_type='template_display_name')
if res_dict:
# Saving to new tables
self.commonV2.save_json_to_new_tables(res_dict)
set_score = self.calculate_relative_position_sets(set_name)
else:
return
if set_score == 0:
pass
elif set_score is False:
continue
set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name']
== set_name]['kpi_set_fk'].values[0]
self.write_to_db_result(set_fk, set_score, self.LEVEL1)
# commiting to new tables
self.commonV2.commit_results_data()
def calculate_relative_position_sets(self, set_name):
"""
This function calculates every relative-position-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.store_info.at[0, 'additional_attribute_2'] == params.get(
'additional_attribute_2', 'Empty'):
tested_filters = {
params.get(TESTED_TYPE): params.get(TESTED_VALUE)
}
anchor_filters = {
params.get(ANCHOR_TYPE): params.get(ANCHOR_VALUE)
}
direction_data = {
'top':
self._get_direction_for_relative_position(
params.get(self.tools.TOP_DISTANCE)),
'bottom':
self._get_direction_for_relative_position(
params.get(self.tools.BOTTOM_DISTANCE)),
'left':
self._get_direction_for_relative_position(
params.get(self.tools.LEFT_DISTANCE)),
'right':
self._get_direction_for_relative_position(
params.get(self.tools.RIGHT_DISTANCE))
}
general_filters = {
'template_display_name': params.get(self.tools.LOCATION)
}
result = self.tools.calculate_relative_position(
tested_filters, anchor_filters, direction_data,
**general_filters)
score = 1 if result else 0
scores.append(score)
self.save_level2_and_level3(set_name,
params.get(self.tools.KPI_NAME),
score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def _get_direction_for_relative_position(self, value):
"""
This function converts direction data from the template (as string) to a number.
"""
if value == self.tools.UNLIMITED_DISTANCE:
value = 1000
elif not value or not str(value).isdigit():
value = 0
else:
value = int(value)
return value
def calculate_assortment_sets(self, set_name):
"""
This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
target = str(params.get(self.store_type, ''))
if target.isdigit() or target.capitalize() in (
self.tools.RELEVANT_FOR_STORE,
self.tools.OR_OTHER_PRODUCTS):
products = str(
params.get(self.tools.PRODUCT_EAN_CODE,
params.get(self.tools.PRODUCT_EAN_CODE2,
''))).replace(',', ' ').split()
target = 1 if not target.isdigit() else int(target)
kpi_name = params.get(self.tools.GROUP_NAME,
params.get(self.tools.PRODUCT_NAME))
kpi_static_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name)]
if len(products) > 1:
result = 0
for product in products:
product_score = self.tools.calculate_assortment(
product_ean_code=product)
result += product_score
atomic_fk = kpi_static_data[
kpi_static_data['description'] ==
product]['atomic_kpi_fk'].values[0]
self.write_to_db_result(atomic_fk,
product_score,
level=self.LEVEL3)
score = 1 if result >= target else 0
else:
result = self.tools.calculate_assortment(
product_ean_code=products)
atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
score = 1 if result >= target else 0
self.write_to_db_result(atomic_fk,
score,
level=self.LEVEL3)
scores.append(score)
kpi_fk = kpi_static_data['kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def calculate_activation_standard(self):
"""
This function calculates the Activation Standard KPI, and saves the result to the DB (for all 3 levels).
"""
final_score = 0
for params in self.tools.download_template(self.ACTIVATION_STANDARD):
set_name = params.get(self.tools.ACTIVATION_SET_NAME)
kpi_name = params.get(self.tools.ACTIVATION_KPI_NAME)
target = float(params.get(self.tools.ACTIVATION_TARGET))
target = target * 100 if target < 1 else target
score_type = params.get(self.tools.ACTIVATION_SCORE)
weight = float(params.get(self.tools.ACTIVATION_WEIGHT))
if kpi_name:
kpi_fk = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name
)]['kpi_fk'].values[0]
score = self.scores[self.LEVEL2].get(kpi_fk, 0)
else:
set_fk = self.kpi_static_data[
self.kpi_static_data['kpi_set_name'] ==
set_name]['kpi_set_fk'].values[0]
score = self.scores[self.LEVEL1].get(set_fk, 0)
if score >= target:
score = 100
else:
if score_type == 'PROPORTIONAL':
score = (score / float(target)) * 100
else:
score = 0
final_score += score * weight
self.save_level2_and_level3(self.ACTIVATION_STANDARD, set_name,
score)
set_fk = self.kpi_static_data[
self.kpi_static_data['kpi_set_name'] ==
self.ACTIVATION_STANDARD]['kpi_set_fk'].values[0]
self.write_to_db_result(set_fk, final_score, self.LEVEL1)
def save_level2_and_level3(self, set_name, kpi_name, score):
"""
Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
"""
kpi_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name)]
try:
kpi_fk = kpi_data['kpi_fk'].values[0]
except:
Log.warning("kpi name or set name don't exist")
return
atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, self.LEVEL2)
self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)
def write_to_db_result(self, fk, score, level):
"""
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.
"""
attributes = self.create_attributes_dict(fk, score, level)
if level == self.LEVEL1:
table = KPS_RESULT
elif level == self.LEVEL2:
table = KPK_RESULT
elif level == self.LEVEL3:
table = KPI_RESULT
else:
return
query = insert(attributes, table)
self.kpi_results_queries.append(query)
def create_attributes_dict(self, fk, score, level):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
"""
if level == self.LEVEL1:
kpi_set_name = self.kpi_static_data[
self.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.LEVEL2:
kpi_name = self.kpi_static_data[self.kpi_static_data['kpi_fk'] ==
fk]['kpi_name'].values[0]
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.LEVEL3:
data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
== fk]
atomic_kpi_name = data['atomic_kpi_name'].values[0]
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['atomic_kpi_fk'] ==
fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame(
[(atomic_kpi_name, 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()
@log_runtime('Saving to DB')
def commit_results_data(self):
"""
This function writes all KPI results to the DB, and commits the changes.
"""
insert_queries = self.merge_insert_queries(self.kpi_results_queries)
self.rds_conn.disconnect_rds()
self.rds_conn.connect_rds()
cur = self.rds_conn.db.cursor()
delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
self.session_uid)
for query in delete_queries:
cur.execute(query)
for query in insert_queries:
cur.execute(query)
self.rds_conn.db.commit()
@staticmethod
def merge_insert_queries(insert_queries):
query_groups = {}
for query in insert_queries:
static_data, inserted_data = query.split('VALUES ')
if static_data not in query_groups:
query_groups[static_data] = []
query_groups[static_data].append(inserted_data)
merged_queries = []
for group in query_groups:
merged_queries.append('{0} VALUES {1}'.format(
group, ',\n'.join(query_groups[group])))
return merged_queries
class DIAGEOPT_SANDToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
ACTIVATION_STANDARD = 'Activation Standard'
DIAGEO_MANUFACTURER = 'Diageo'
def __init__(self, data_provider, output):
self.k_engine = BaseCalculationsScript(data_provider, 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.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_channel = self.store_info['store_type'].values[0]
if self.store_channel:
self.store_channel = self.store_channel.upper()
self.store_type = self.store_info['additional_attribute_1'].values[0]
self.business_unit = self.get_business_unit()
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.match_display_in_scene = self.get_match_display()
self.set_templates_data = {}
self.kpi_static_data = self.get_kpi_static_data()
self.set_scores = {}
self.kpi_scores = {}
self.kpi_results_queries = []
self.output = output
self.common = Common(self.data_provider)
self.commonV2 = CommonV2(self.data_provider)
self.tools = DIAGEOToolBox(
self.data_provider,
output,
match_display_in_scene=self.match_display_in_scene)
self.diageo_generator = DIAGEOGenerator(self.data_provider,
self.output,
self.common,
menu=True)
def get_business_unit(self):
"""
This function returns the session's business unit (equal to store type for some KPIs)
"""
query = DIAGEOQueries.get_business_unit_data(
self.store_info['store_fk'].values[0])
business_unit = pd.read_sql_query(query, self.rds_conn.db)['name']
if not business_unit.empty:
return business_unit.values[0]
else:
return ''
def get_kpi_static_data(self):
"""
This function extracts the static KPI data and saves it into one global data frame.
The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
"""
query = DIAGEOQueries.get_all_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
def get_match_display(self):
"""
This function extracts the display matches data and saves it into one global data frame.
The data is taken from probedata.match_display_in_scene.
"""
query = DIAGEOQueries.get_match_display(self.session_uid)
match_display = pd.read_sql_query(query, self.rds_conn.db)
return match_display
def main_calculation(self, set_names):
"""
This function calculates the KPI results.
"""
# Global assortment kpis
assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2(
)
self.commonV2.save_json_to_new_tables(assortment_res_dict)
total_scores_dict = []
# saving in dictionary for activation standard use
if assortment_res_dict:
total_scores_dict.append(assortment_res_dict)
# Global Menu kpis
menus_res_dict = self.diageo_generator.diageo_global_share_of_menu_cocktail_function(
cocktail_product_level=True)
self.commonV2.save_json_to_new_tables(menus_res_dict)
for set_name in set_names:
set_score = 0
# if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys():
# self.set_templates_data[set_name] = self.tools.download_template(set_name)
if set_name in ('Visible to Customer', 'Visible to Consumer %'):
# Global function
sku_list = filter(
None, self.scif[self.scif['product_type'] ==
'SKU'].product_ean_code.tolist())
res_dict = self.diageo_generator.diageo_global_visible_percentage(
sku_list)
if res_dict:
#saving in dictionary for activation standard use
total_scores_dict.append(res_dict)
# Saving to new tables
parent_res = res_dict[-1]
self.commonV2.save_json_to_new_tables(res_dict)
# Saving to old tables
set_score = result = parent_res['result']
self.save_level2_and_level3(set_name=set_name,
kpi_name=set_name,
score=result)
elif set_name in ('Secondary Displays', 'Secondary'):
# Global function
res_json = self.diageo_generator.diageo_global_secondary_display_secondary_function(
)
if res_json:
# saving in dictionary for activation standard use
total_scores_dict.append(res_json)
# Saving to new tables
self.commonV2.write_to_db_result(
fk=res_json['fk'],
numerator_id=1,
denominator_id=self.store_id,
result=res_json['result'])
# Saving to old tables
set_score = self.tools.calculate_number_of_scenes(
location_type='Secondary')
self.save_level2_and_level3(set_name, set_name, set_score)
elif set_name in ('Activation Standard'):
manufacturer_fk = self.all_products[
self.all_products['manufacturer_name'] ==
self.DIAGEO_MANUFACTURER]['manufacturer_fk'].values[0]
self.set_templates_data[
set_name] = self.tools.download_template(set_name)
results_list = self.diageo_generator.diageo_global_activation_standard_function(
total_scores_dict, self.set_templates_data[set_name],
self.store_id, manufacturer_fk)
for result in results_list['old_tables_level2and3']:
self.save_level2_and_level3(result['kpi_set_name'],
result['kpi_name'],
result['score'])
# saving results to old table
self.write_to_db_result(
results_list['old_tables_level1']['fk'],
results_list['old_tables_level1']['score'],
results_list['old_tables_level1']['level'])
res_json = results_list['new_tables_result']
# saving results to new tables
self.commonV2.save_json_to_new_tables(res_json)
if set_score == 0:
pass
elif set_score is False:
continue
if set_name not in ('Activation Standard'):
set_fk = self.kpi_static_data[
self.kpi_static_data['kpi_set_name'] ==
set_name]['kpi_set_fk'].values[0]
self.write_to_db_result(set_fk, set_score, self.LEVEL1)
# committing to new tables
self.commonV2.commit_results_data()
return
def save_level2_and_level3(self, set_name, kpi_name, score):
"""
Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
"""
kpi_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name)]
kpi_fk = kpi_data['kpi_fk'].values[0]
self.kpi_scores[kpi_fk] = score
atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, self.LEVEL2)
self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)
def calculate_brand_pouring_sets(self, set_name):
"""
This function calculates every Brand-Pouring-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.tools.calculate_number_of_scenes(
**{self.tools.BRAND_POURING_FIELD: 'Y'}) > 0:
# 'Pouring' scenes
result = self.tools.calculate_brand_pouring_status(
params.get(self.tools.BRAND_NAME),
**{self.tools.BRAND_POURING_FIELD: 'Y'})
elif self.tools.calculate_number_of_scenes(
**{self.tools.BRAND_POURING_FIELD: 'back_bar'}) > 0:
# 'Back Bar' scenes
result = self.tools.calculate_brand_pouring_status(
params.get(self.tools.BRAND_NAME),
**{self.tools.BRAND_POURING_FIELD: 'back_bar'})
else:
result = 0
score = 1 if result else 0
scores.append(score)
self.save_level2_and_level3(set_name,
params.get(self.tools.KPI_NAME), score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def calculate_posm_sets(self, set_name):
"""
This function calculates every POSM-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.store_channel is None:
break
kpi_res = self.tools.calculate_posm(
display_name=params.get(self.tools.DISPLAY_NAME))
score = 1 if kpi_res > 0 else 0
if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
scores.append(score)
if score == 1 or params.get(
self.store_type) == self.tools.RELEVANT_FOR_STORE:
self.save_level2_and_level3(
set_name, params.get(self.tools.DISPLAY_NAME), score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def calculate_assortment_sets(self, set_name):
"""
This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
target = str(params.get(self.store_type, ''))
if target.isdigit() or target.capitalize() in (
self.tools.RELEVANT_FOR_STORE,
self.tools.OR_OTHER_PRODUCTS):
products = str(
params.get(self.tools.PRODUCT_EAN_CODE,
params.get(self.tools.PRODUCT_EAN_CODE2,
''))).replace(',', ' ').split()
target = 1 if not target.isdigit() else int(target)
kpi_name = params.get(self.tools.GROUP_NAME,
params.get(self.tools.PRODUCT_NAME))
kpi_static_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name)]
if len(products) > 1:
result = 0
for product in products:
product_score = self.tools.calculate_assortment(
product_ean_code=product)
result += product_score
atomic_fk = kpi_static_data[
kpi_static_data['description'] ==
product]['atomic_kpi_fk'].values[0]
self.write_to_db_result(atomic_fk,
product_score,
level=self.LEVEL3)
score = 1 if result >= target else 0
else:
result = self.tools.calculate_assortment(
product_ean_code=products)
atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
score = 1 if result >= target else 0
self.write_to_db_result(atomic_fk,
score,
level=self.LEVEL3)
scores.append(score)
kpi_fk = kpi_static_data['kpi_fk'].values[0]
self.kpi_scores[kpi_fk] = score
self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def calculate_activation_standard(self):
"""
This function calculates the Activation Standard KPI, and saves the result to the DB (for all 3 levels).
"""
final_score = 0
template = self.tools.download_template(self.ACTIVATION_STANDARD)
for params in template:
set_name = params.get(self.tools.ACTIVATION_SET_NAME)
kpi_name = params.get(self.tools.ACTIVATION_KPI_NAME)
target = float(params.get(self.tools.ACTIVATION_TARGET))
target = target * 100 if target < 1 else target
score_type = params.get(self.tools.ACTIVATION_SCORE)
weight = float(params.get(self.tools.ACTIVATION_WEIGHT))
if kpi_name:
kpi_fk = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name
)]['kpi_fk'].values[0]
score = self.kpi_scores.get(kpi_fk)
else:
set_fk = self.kpi_static_data[
self.kpi_static_data['kpi_set_name'] ==
set_name]['kpi_set_fk'].values[0]
score = self.set_scores.get(set_fk)
if score >= target:
score = 100
else:
if score_type == 'PROPORTIONAL':
score = (score / float(target)) * 100
else:
score = 0
final_score += score * weight
self.save_level2_and_level3(self.ACTIVATION_STANDARD, set_name,
score)
total_score = 100 if final_score == 100 else 0
set_fk = self.kpi_static_data[
self.kpi_static_data['kpi_set_name'] ==
self.ACTIVATION_STANDARD]['kpi_set_fk'].values[0]
self.write_to_db_result(set_fk, total_score, self.LEVEL1)
def write_to_db_result(self, fk, score, level):
"""
This function 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.
"""
attributes = self.create_attributes_dict(fk, score, level)
if level == self.LEVEL1:
table = KPS_RESULT
elif level == self.LEVEL2:
table = KPK_RESULT
elif level == self.LEVEL3:
table = KPI_RESULT
else:
return
query = insert(attributes, table)
self.kpi_results_queries.append(query)
def create_attributes_dict(self, fk, score, level):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
"""
score = round(score, 2)
if level == self.LEVEL1:
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['kpi_set_fk'] ==
fk]['kpi_set_name'].values[0]
score_type = '%' if kpi_set_name in self.tools.KPI_SETS_WITH_PERCENT_AS_SCORE else ''
attributes = pd.DataFrame(
[(kpi_set_name, self.session_uid, self.store_id,
self.visit_date.isoformat(), format(score,
'.2f'), score_type, fk)],
columns=[
'kps_name', 'session_uid', 'store_fk', 'visit_date',
'score_1', 'score_2', 'kpi_set_fk'
])
elif level == self.LEVEL2:
kpi_name = self.kpi_static_data[self.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.LEVEL3:
data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
== fk]
atomic_kpi_name = data['atomic_kpi_name'].values[0].replace(
"'", "\\'")
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['atomic_kpi_fk'] ==
fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame([
(atomic_kpi_name, self.session_uid,
kpi_set_name, self.store_id, self.visit_date.isoformat(),
datetime.utcnow().isoformat(), score, kpi_fk, fk, None, None)
],
columns=[
'display_text', 'session_uid',
'kps_name', 'store_fk', 'visit_date',
'calculation_time', 'score',
'kpi_fk', 'atomic_kpi_fk',
'threshold', 'result'
])
else:
attributes = pd.DataFrame()
return attributes.to_dict()
@log_runtime('Saving to DB')
def commit_results_data(self):
"""
This function writes all KPI results to the DB, and commits the changes.
"""
cur = self.rds_conn.db.cursor()
delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
self.session_uid)
for query in delete_queries:
cur.execute(query)
for query in self.kpi_results_queries:
cur.execute(query)
self.rds_conn.db.commit()
class DIAGEOITToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.k_engine = BaseCalculationsScript(data_provider, 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.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.store_channel = self.store_info['store_type'].values[0]
if self.store_channel:
self.store_channel = self.store_channel.upper()
self.store_type = self.store_info['additional_attribute_1'].values[0]
self.segment = self.get_business_unit_name()
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.match_display_in_scene = self.get_match_display()
self.set_templates_data = {}
self.kpi_static_data = self.get_kpi_static_data()
self.kpi_results_queries = []
self.output = output
self.common = Common(self.data_provider)
self.commonV2 = CommonV2(self.data_provider)
self.tools = DIAGEOToolBox(
self.data_provider,
output,
match_display_in_scene=self.match_display_in_scene)
self.kpi_results_queries = []
self.diageo_generator = DIAGEOGenerator(self.data_provider,
self.output,
self.common,
menu=True)
def get_business_unit_name(self):
"""
This function extracts the static KPI data and saves it into one global data frame.
The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
"""
query = DIAGEOQueries.get_business_unit_name(self.store_id)
business_unit_name = pd.read_sql_query(query, self.rds_conn.db)
if business_unit_name['business_unit_name'].empty:
return ""
else:
return business_unit_name['business_unit_name'].values[0]
def get_kpi_static_data(self):
"""
This function extracts the static KPI data and saves it into one global data frame.
The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
"""
query = DIAGEOQueries.get_all_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
def get_match_display(self):
"""
This function extracts the display matches data and saves it into one global data frame.
The data is taken from probedata.match_display_in_scene.
"""
query = DIAGEOQueries.get_match_display(self.session_uid)
match_display = pd.read_sql_query(query, self.rds_conn.db)
return match_display
def main_calculation(self, set_names):
"""
This function calculates the KPI results.
"""
# Global assortment kpis
assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2(
)
self.commonV2.save_json_to_new_tables(assortment_res_dict)
# Global Menu kpis
menus_res_dict = self.diageo_generator.diageo_global_share_of_menu_cocktail_function(
cocktail_product_level=True)
self.commonV2.save_json_to_new_tables(menus_res_dict)
for set_name in set_names:
set_score = 0
if set_name in ('Visible to Customer', 'Visible to Consumer %'):
# Global function
sku_list = filter(
None, self.scif[self.scif['product_type'] ==
'SKU'].product_ean_code.tolist())
res_dict = self.diageo_generator.diageo_global_visible_percentage(
sku_list)
# Saving to new tables
self.commonV2.save_json_to_new_tables(res_dict)
# Saving to old tables
filters = {self.tools.VISIBILITY_PRODUCTS_FIELD: 'Y'}
set_score = self.tools.calculate_visible_percentage(
visible_filters=filters)
self.save_level2_and_level3(set_name, set_name, set_score)
if set_score == 0:
pass
elif set_score is False:
continue
set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name']
== set_name]['kpi_set_fk'].values[0]
self.write_to_db_result(set_fk, set_score, self.LEVEL1)
# commiting to new tables
self.commonV2.commit_results_data()
return
def save_level2_and_level3(self, set_name, kpi_name, score):
"""
Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
"""
kpi_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name)]
kpi_fk = kpi_data['kpi_fk'].values[0]
atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, self.LEVEL2)
self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)
def write_to_db_result(self, fk, score, level):
"""
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.
"""
attributes = self.create_attributes_dict(fk, score, level)
if level == self.LEVEL1:
table = KPS_RESULT
elif level == self.LEVEL2:
table = KPK_RESULT
elif level == self.LEVEL3:
table = KPI_RESULT
else:
return
query = insert(attributes, table)
self.kpi_results_queries.append(query)
def create_attributes_dict(self, fk, score, level):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
"""
if level == self.LEVEL1:
kpi_set_name = self.kpi_static_data[
self.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.LEVEL2:
kpi_name = self.kpi_static_data[self.kpi_static_data['kpi_fk'] ==
fk]['kpi_name'].values[0]
attributes = pd.DataFrame(
[(self.session_uid, self.store_id, self.visit_date.isoformat(),
fk, kpi_name.replace("'", "''"), score)],
columns=[
'session_uid', 'store_fk', 'visit_date', 'kpi_fk',
'kpk_name', 'score'
])
elif level == self.LEVEL3:
data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
== fk]
atomic_kpi_name = data['atomic_kpi_name'].values[0]
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['atomic_kpi_fk'] ==
fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame(
[(atomic_kpi_name.replace("'", "''"), 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()
@log_runtime('Saving to DB')
def commit_results_data(self):
"""
This function writes all KPI results to the DB, and commits the changes.
"""
cur = self.rds_conn.db.cursor()
delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
self.session_uid)
for query in delete_queries:
cur.execute(query)
for query in self.kpi_results_queries:
cur.execute(query)
self.rds_conn.db.commit()
class DIAGEOBENELUX_SANDToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
ACTIVATION_STANDARD = 'Activation Standard'
def __init__(self, data_provider, output):
self.k_engine = BaseCalculationsScript(data_provider, 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.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_channel = self.store_info['store_type'].values[0]
if self.store_channel:
self.store_channel = self.store_channel.upper()
self.store_type = self.store_info['additional_attribute_1'].values[0]
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.match_display_in_scene = self.get_match_display()
self.set_templates_data = {}
self.kpi_static_data = self.get_kpi_static_data()
self.output = output
self.common = Common(self.data_provider)
self.commonV2 = CommonV2(self.data_provider)
self.global_gen = DIAGEOGenerator(self.data_provider, self.output,
self.common)
self.tools = DIAGEOToolBox(
self.data_provider,
output,
match_display_in_scene=self.match_display_in_scene
) # replace the old one
self.diageo_generator = DIAGEOGenerator(self.data_provider,
self.output,
self.common,
menu=True)
self.scores = {self.LEVEL1: {}, self.LEVEL2: {}, self.LEVEL3: {}}
self.kpi_results_queries = []
def get_kpi_static_data(self):
"""
This function extracts the static KPI data and saves it into one global data frame.
The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
"""
query = DIAGEOQueries.get_all_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
def get_match_display(self):
"""
This function extracts the display matches data and saves it into one global data frame.
The data is taken from probedata.match_display_in_scene.
"""
query = DIAGEOQueries.get_match_display(self.session_uid)
match_display = pd.read_sql_query(query, self.rds_conn.db)
return match_display
def main_calculation(self):
# SOS Out Of The Box kpis
self.diageo_generator.activate_ootb_kpis(self.commonV2)
# sos by scene type
self.diageo_generator.sos_by_scene_type(self.commonV2)
# Global assortment kpis
assortment_res = self.diageo_generator.diageo_global_grouping_assortment_calculation(
)
self.commonV2.save_json_to_new_tables(assortment_res)
# Global Menu kpis
menus_res = self.diageo_generator.diageo_global_new_share_of_menu_function(
)
self.commonV2.save_json_to_new_tables(menus_res)
# Global Secondary Displays function
res_json = self.diageo_generator.diageo_global_secondary_display_secondary_function(
)
if res_json:
self.commonV2.write_to_db_result(fk=res_json['fk'],
numerator_id=1,
denominator_id=self.store_id,
result=res_json['result'])
# committing to new tables
self.commonV2.commit_results_data()
# committing to the old tables
self.common.commit_results_data()
#
# def main_calculation(self, set_names):
# """
# This function calculates the KPI results.
# """
# # Global assortment kpis
# assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2()
# self.commonV2.save_json_to_new_tables(assortment_res_dict)
#
# # Global Menu kpis
# menus_res_dict = self.diageo_generator.diageo_global_share_of_menu_cocktail_function(
# cocktail_product_level=True)
# self.commonV2.save_json_to_new_tables(menus_res_dict)
#
# for set_name in set_names:
# set_score = 0
#
# # Global Secondary Displays
# if set_name in ('Secondary Displays', 'Secondary'):
# res_json = self.diageo_generator.diageo_global_secondary_display_secondary_function()
# if res_json:
# self.commonV2.write_to_db_result(fk=res_json['fk'], numerator_id=1, denominator_id=self.store_id,
# result=res_json['result'])
# set_score = self.tools.calculate_number_of_scenes(location_type='Secondary')
# self.save_level2_and_level3(set_name, set_name, set_score)
#
# if set_score == 0:
# pass
# elif set_score is False:
# continue
#
# set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name'] == set_name]['kpi_set_fk'].values[0]
# self.write_to_db_result(set_fk, set_score, self.LEVEL1)
#
# # committing to new tables
# self.commonV2.commit_results_data()
#
# return
def save_level2_and_level3(self, set_name, kpi_name, score):
"""
Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
"""
kpi_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name)]
kpi_fk = kpi_data['kpi_fk'].values[0]
atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, self.LEVEL2)
self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)
def calculate_activation_standard(self):
"""
This function calculates the Activation Standard KPI, and saves the result to the DB (for all 3 levels).
"""
final_score = 0
for params in self.tools.download_template(self.ACTIVATION_STANDARD):
set_name = params.get(self.tools.ACTIVATION_SET_NAME)
kpi_name = params.get(self.tools.ACTIVATION_KPI_NAME)
target = float(params.get(self.tools.ACTIVATION_TARGET))
target = target * 100 if target < 1 else target
score_type = params.get(self.tools.ACTIVATION_SCORE)
weight = float(params.get(self.tools.ACTIVATION_WEIGHT))
if kpi_name:
kpi_fk = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name
)]['kpi_fk'].values[0]
score = self.scores[self.LEVEL2].get(kpi_fk, 0)
else:
set_fk = self.kpi_static_data[
self.kpi_static_data['kpi_set_name'] ==
set_name]['kpi_set_fk'].values[0]
score = self.scores[self.LEVEL1].get(set_fk, 0)
if score >= target:
score = 100
else:
if score_type == 'PROPORTIONAL':
score = (score / float(target)) * 100
else:
score = 0
final_score += score * weight
self.save_level2_and_level3(self.ACTIVATION_STANDARD, set_name,
score)
set_fk = self.kpi_static_data[
self.kpi_static_data['kpi_set_name'] ==
self.ACTIVATION_STANDARD]['kpi_set_fk'].values[0]
self.write_to_db_result(set_fk, final_score, self.LEVEL1)
def calculate_sos_sets(self, set_name):
"""
This function calculates every SOS-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
result = self.tools.calculate_share_of_shelf(
manufacturer=self.tools.DIAGEO,
include_empty=self.tools.EXCLUDE_EMPTY)
score = 1 if result >= params.get(self.tools.TARGET) else 0
scores.append(score)
self.save_level2_and_level3(set_name,
params.get(self.tools.KPI_NAME),
score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def calculate_survey_sets(self, set_name):
"""
This function calculates every Survey-Answer-typed KPI and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
survey_text = params.get(self.tools.SURVEY_QUESTION)
answer_field = 'number_value' if params.get(
self.tools.SURVEY_ANSWER_TYPE) == 'Numeric' else None
answer_target = params.get(self.tools.SURVEY_ANSWER)
survey_answer = self.tools.get_survey_answer(
survey_text, answer_field)
if answer_field == 'number_value':
if str(answer_target).isdigit():
answer_target = '=={}'.format(answer_target)
if not str(survey_answer).isdigit():
result = False
else:
result = eval('{}{}'.format(survey_answer, answer_target))
else:
result = True if survey_answer in answer_target.split(
';') else False
score = 1 if result else 0
scores.append(score)
self.save_level2_and_level3(set_name,
params.get(self.tools.KPI_NAME), score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def validate_survey(self, params):
"""
This function checks whether the Survey-typed KPI should be run, based on the Execution Condition Field.
"""
survey_validated = False
condition = params.get(self.tools.SURVEY_CONDITION)
if not condition:
survey_validated = True
else:
question, answers = condition.split('=')
survey_answer = self.tools.get_survey_answer(question)
if survey_answer in answers.split(';'):
survey_validated = True
return survey_validated
def calculate_block_together_sets(self, set_name):
"""
This function calculates every block-together-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.store_channel == params.get(self.tools.CHANNEL,
'').upper():
filters = {'template_name': params.get(self.tools.LOCATION)}
if params.get(self.tools.SUB_BRAND_NAME):
filters['sub_brand_name'] = params.get(
self.tools.SUB_BRAND_NAME)
else:
filters['brand_name'] = params.get(self.tools.BRAND_NAME)
result = self.tools.calculate_block_together(**filters)
score = 1 if result else 0
scores.append(score)
self.save_level2_and_level3(set_name,
params.get(self.tools.KPI_NAME),
score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def calculate_relative_position_sets(self, set_name):
"""
This function calculates every relative-position-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.store_channel == params.get(self.tools.CHANNEL,
'').upper():
tested_filters = {
'product_ean_code': params.get(self.tools.TESTED)
}
anchor_filters = {
'product_ean_code': params.get(self.tools.ANCHOR)
}
direction_data = {
'top':
self._get_direction_for_relative_position(
params.get(self.tools.TOP_DISTANCE)),
'bottom':
self._get_direction_for_relative_position(
params.get(self.tools.BOTTOM_DISTANCE)),
'left':
self._get_direction_for_relative_position(
params.get(self.tools.LEFT_DISTANCE)),
'right':
self._get_direction_for_relative_position(
params.get(self.tools.RIGHT_DISTANCE))
}
general_filters = {
'template_name': params.get(self.tools.LOCATION)
}
result = self.tools.calculate_relative_position(
tested_filters, anchor_filters, direction_data,
**general_filters)
score = 1 if result else 0
scores.append(score)
self.save_level2_and_level3(set_name,
params.get(self.tools.KPI_NAME),
score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def _get_direction_for_relative_position(self, value):
"""
This function converts direction data from the template (as string) to a number.
"""
if value == self.tools.UNLIMITED_DISTANCE:
value = 1000
elif not value or not str(value).isdigit():
value = 0
else:
value = int(value)
return value
def calculate_posm_sets(self, set_name):
"""
This function calculates every POSM-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.store_channel is None:
break
kpi_res = self.tools.calculate_posm(
display_name=params.get(self.tools.DISPLAY_NAME))
score = 1 if kpi_res > 0 else 0
if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
scores.append(score)
if score == 1 or params.get(
self.store_type) == self.tools.RELEVANT_FOR_STORE:
self.save_level2_and_level3(
set_name, params.get(self.tools.DISPLAY_NAME), score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def calculate_assortment_sets(self, set_name):
"""
This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
target = str(params.get(self.store_type, ''))
if target.isdigit() or target.capitalize() in (
self.tools.RELEVANT_FOR_STORE,
self.tools.OR_OTHER_PRODUCTS):
products = str(
params.get(self.tools.PRODUCT_EAN_CODE,
params.get(self.tools.PRODUCT_EAN_CODE2,
''))).replace(',', ' ').split()
target = 1 if not target.isdigit() else int(target)
kpi_name = params.get(self.tools.GROUP_NAME,
params.get(self.tools.PRODUCT_NAME))
kpi_static_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name)]
if len(products) > 1:
result = 0
for product in products:
product_score = self.tools.calculate_assortment(
product_ean_code=product)
result += product_score
product_name = \
self.all_products[self.all_products['product_ean_code'] == product]['product_name'].values[0]
try:
atomic_fk = \
kpi_static_data[kpi_static_data['atomic_kpi_name'] == product_name]['atomic_kpi_fk'].values[
0]
except Exception as e:
Log.info(
'Product {} is not defined in the DB'.format(
product_name))
continue
self.write_to_db_result(atomic_fk,
product_score,
level=self.LEVEL3)
score = 1 if result >= target else 0
else:
result = self.tools.calculate_assortment(
product_ean_code=products)
atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
score = 1 if result >= target else 0
self.write_to_db_result(atomic_fk,
score,
level=self.LEVEL3)
scores.append(score)
kpi_fk = kpi_static_data['kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def write_to_db_result(self, fk, score, level):
"""
This function 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.
"""
attributes = self.create_attributes_dict(fk, score, level)
if level == self.LEVEL1:
table = KPS_RESULT
elif level == self.LEVEL2:
table = KPK_RESULT
elif level == self.LEVEL3:
table = KPI_RESULT
else:
return
self.scores[level][fk] = score
query = insert(attributes, table)
self.kpi_results_queries.append(query)
def create_attributes_dict(self, fk, score, level):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
"""
score = round(score, 2)
if level == self.LEVEL1:
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['kpi_set_fk'] ==
fk]['kpi_set_name'].values[0]
score_type = '%' if kpi_set_name in self.tools.KPI_SETS_WITH_PERCENT_AS_SCORE else ''
attributes = pd.DataFrame(
[(kpi_set_name, self.session_uid, self.store_id,
self.visit_date.isoformat(), format(score,
'.2f'), score_type, fk)],
columns=[
'kps_name', 'session_uid', 'store_fk', 'visit_date',
'score_1', 'score_2', 'kpi_set_fk'
])
elif level == self.LEVEL2:
kpi_name = self.kpi_static_data[self.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.LEVEL3:
data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
== fk]
atomic_kpi_name = data['atomic_kpi_name'].values[0].replace(
"'", "\\'")
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['atomic_kpi_fk'] ==
fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame([
(atomic_kpi_name, self.session_uid,
kpi_set_name, self.store_id, self.visit_date.isoformat(),
datetime.utcnow().isoformat(), score, kpi_fk, fk, None, None)
],
columns=[
'display_text', 'session_uid',
'kps_name', 'store_fk', 'visit_date',
'calculation_time', 'score',
'kpi_fk', 'atomic_kpi_fk',
'threshold', 'result'
])
else:
attributes = pd.DataFrame()
return attributes.to_dict()
@log_runtime('Saving to DB')
def commit_results_data(self):
"""
This function writes all KPI results to the DB, and commits the changes.
"""
cur = self.rds_conn.db.cursor()
delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
self.session_uid)
for query in delete_queries:
cur.execute(query)
for query in self.kpi_results_queries:
cur.execute(query)
self.rds_conn.db.commit()
class DIAGEOBR_SANDToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.k_engine = BaseCalculationsScript(data_provider, output)
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.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.store_channel = self.store_info['store_type'].values[0]
if self.store_channel:
self.store_channel = self.store_channel.upper()
self.store_type = self.store_info['additional_attribute_1'].values[0]
self.segment = self.get_business_unit_name()
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.match_display_in_scene = self.get_match_display()
self.set_templates_data = {}
self.kpi_static_data = self.get_kpi_static_data()
self.tools = DIAGEOToolBox(self.data_provider, output, match_display_in_scene=self.match_display_in_scene)
self.kpi_results_queries = []
self.common = Common(self.data_provider)
self.commonV2 = CommonV2(self.data_provider)
self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common)
def get_business_unit_name(self):
"""
This function extracts the static KPI data and saves it into one global data frame.
The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
"""
query = DIAGEOQueries.get_business_unit_name(self.store_id)
business_unit_name = pd.read_sql_query(query, self.rds_conn.db)
if business_unit_name['business_unit_name'].empty:
return ""
else:
return business_unit_name['business_unit_name'].values[0]
def get_kpi_static_data(self):
"""
This function extracts the static KPI data and saves it into one global data frame.
The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
"""
query = DIAGEOQueries.get_all_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
def get_match_display(self):
"""
This function extracts the display matches data and saves it into one global data frame.
The data is taken from probedata.match_display_in_scene.
"""
query = DIAGEOQueries.get_match_display(self.session_uid)
match_display = pd.read_sql_query(query, self.rds_conn.db)
return match_display
def main_calculation(self, set_names):
"""
This function calculates the KPI results.
"""
log_runtime('Updating templates')(self.tools.update_templates)()
# Global assortment kpis
assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2()
self.commonV2.save_json_to_new_tables(assortment_res_dict)
for set_name in set_names:
set_score = 0
if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys():
try:
self.set_templates_data[set_name] = self.tools.download_template(set_name)
except:
Log.warning("Couldn't find a template for set name: " + str(set_name))
continue
# if set_name in ('MPA', 'New Products'):
# set_score = self.calculate_assortment_sets(set_name)
# elif set_name in ('POSM',):
# set_score = self.calculate_posm_sets(set_name)
if set_name == 'Visible to Customer':
# Global function
sku_list = filter(None, self.scif[self.scif['product_type'] == 'SKU'].product_ean_code.tolist())
res_dict = self.diageo_generator.diageo_global_visible_percentage(sku_list)
if res_dict:
# Saving to new tables
parent_res = res_dict[-1]
for r in res_dict:
self.commonV2.write_to_db_result(**r)
# Saving to old tables
set_score = result = parent_res['result']
self.save_level2_and_level3(set_name=set_name, kpi_name=set_name, score=result)
# filters = {self.tools.VISIBILITY_PRODUCTS_FIELD: 'Y'}
# set_score = self.tools.calculate_visible_percentage(visible_filters=filters)
# self.save_level2_and_level3(set_name, set_name, set_score)
elif set_name in ('Secondary Displays', 'Secondary'):
# Global function
res_dict = self.diageo_generator.diageo_global_secondary_display_secondary_function()
# Saving to new tables
if res_dict:
self.commonV2.write_to_db_result(fk=res_dict['fk'], numerator_id=1, denominator_id=self.store_id,
result=res_dict['result'])
# Saving to old tables
set_score = self.tools.calculate_assortment(assortment_entity='scene_id', location_type='Secondary Shelf')
self.save_level2_and_level3(set_name, set_name, set_score)
if set_score == 0:
pass
elif set_score is False:
return
set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name'] == set_name]['kpi_set_fk'].values[0]
self.write_to_db_result(set_fk, set_score, self.LEVEL1)
# commiting to new tables
self.commonV2.commit_results_data()
def save_level2_and_level3(self, set_name, kpi_name, score):
"""
Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
"""
kpi_data = self.kpi_static_data[(self.kpi_static_data['kpi_set_name'] == set_name) &
(self.kpi_static_data['kpi_name'] == kpi_name)]
try:
kpi_fk = kpi_data['kpi_fk'].values[0]
except:
Log.warning("kpi name or set name don't exist")
return
atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, self.LEVEL2)
self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)
def calculate_posm_sets(self, set_name):
"""
This function calculates every POSM-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.store_channel is None:
break
kpi_res = self.tools.calculate_posm(display_name=params.get(self.tools.DISPLAY_NAME))
score = 1 if kpi_res > 0 else 0
if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
scores.append(score)
if score == 1 or params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
self.save_level2_and_level3(set_name, params.get(self.tools.DISPLAY_NAME), score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def calculate_assortment_sets(self, set_name):
"""
This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
segment = '{};{}'.format(self.store_type, self.segment)
for params in self.set_templates_data[set_name]:
if params.get(segment, '').capitalize() in (self.tools.RELEVANT_FOR_STORE,
self.tools.OR_OTHER_PRODUCTS):
object_type = self.tools.ENTITY_TYPE_CONVERTER.get(params.get(self.tools.ENTITY_TYPE),
'product_ean_code')
objects = [str(params.get(self.tools.PRODUCT_EAN_CODE, params.get(self.tools.PRODUCT_EAN_CODE2, '')))]
if params.get(self.store_type) == self.tools.OR_OTHER_PRODUCTS:
additional_objects = str(params.get(self.tools.ADDITIONAL_SKUS)).split(',')
objects.extend(additional_objects)
filters = {object_type: objects}
result = self.tools.calculate_assortment(**filters)
score = 1 if result > 0 else 0
scores.append(score)
self.save_level2_and_level3(set_name, params.get(self.tools.PRODUCT_NAME), score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def write_to_db_result(self, fk, score, level):
"""
This function 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.
"""
attributes = self.create_attributes_dict(fk, score, level)
if level == self.LEVEL1:
table = KPS_RESULT
elif level == self.LEVEL2:
table = KPK_RESULT
elif level == self.LEVEL3:
table = KPI_RESULT
else:
return
query = insert(attributes, table)
self.kpi_results_queries.append(query)
def create_attributes_dict(self, fk, score, level):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
"""
score = round(score, 2)
if level == self.LEVEL1:
kpi_set_name = self.kpi_static_data[self.kpi_static_data['kpi_set_fk'] == fk]['kpi_set_name'].values[0]
score_type = '%' if kpi_set_name in self.tools.KPI_SETS_WITH_PERCENT_AS_SCORE else ''
attributes = pd.DataFrame([(kpi_set_name, self.session_uid, self.store_id, self.visit_date.isoformat(),
format(score, '.2f'), score_type, fk)],
columns=['kps_name', 'session_uid', 'store_fk', 'visit_date', 'score_1',
'score_2', 'kpi_set_fk'])
elif level == self.LEVEL2:
kpi_name = self.kpi_static_data[self.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.LEVEL3:
data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk'] == fk]
atomic_kpi_name = data['atomic_kpi_name'].values[0].replace("'", "\\'")
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk'] == fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame([(atomic_kpi_name, self.session_uid, kpi_set_name, self.store_id,
self.visit_date.isoformat(), datetime.utcnow().isoformat(),
score, kpi_fk, fk, None, None)],
columns=['display_text', 'session_uid', 'kps_name', 'store_fk', 'visit_date',
'calculation_time', 'score', 'kpi_fk', 'atomic_kpi_fk', 'threshold',
'result'])
else:
attributes = pd.DataFrame()
return attributes.to_dict()
@log_runtime('Saving to DB')
def commit_results_data(self):
"""
This function writes all KPI results to the DB, and commits the changes.
"""
self.rds_conn.disconnect_rds()
self.rds_conn.connect_rds()
cur = self.rds_conn.db.cursor()
delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(self.session_uid)
for query in delete_queries:
cur.execute(query)
for query in self.kpi_results_queries:
cur.execute(query)
self.rds_conn.db.commit()
class DIAGEOUK_SANDToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.k_engine = BaseCalculationsScript(data_provider, 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.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_channel = self.store_info['store_type'].values[0]
if self.store_channel:
self.store_channel = self.store_channel.upper()
self.store_type = self.store_info['additional_attribute_1'].values[0]
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.match_display_in_scene = self.get_match_display()
self.set_templates_data = {}
self.kpi_static_data = self.get_kpi_static_data()
self.kpi_results_queries = []
self.output = output
self.common = Common(self.data_provider)
self.commonV2 = CommonV2(self.data_provider)
self.global_gen = DIAGEOGenerator(self.data_provider, self.output,
self.common)
self.tools = DIAGEOToolBox(
self.data_provider,
output,
match_display_in_scene=self.match_display_in_scene
) # replace the old one
self.diageo_generator = DIAGEOGenerator(self.data_provider,
self.output,
self.common,
menu=True)
def get_kpi_static_data(self):
"""
This function extracts the static KPI data and saves it into one global data frame.
The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
"""
query = DIAGEOQueries.get_all_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
def get_match_display(self):
"""
This function extracts the display matches data and saves it into one global data frame.
The data is taken from probedata.match_display_in_scene.
"""
query = DIAGEOQueries.get_match_display(self.session_uid)
match_display = pd.read_sql_query(query, self.rds_conn.db)
return match_display
def main_calculation(self, set_names):
"""
This function calculates the KPI results.
"""
log_runtime('Updating templates')(self.tools.update_templates)()
# SOS Out Of The Box kpis
self.activate_ootb_kpis()
# Global assortment kpis
assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2(
)
self.commonV2.save_json_to_new_tables(assortment_res_dict)
# Global assortment kpis - v3 for NEW MOBILE REPORTS use
assortment_res_dict_v3 = self.diageo_generator.diageo_global_assortment_function_v3(
)
self.commonV2.save_json_to_new_tables(assortment_res_dict_v3)
equipment_score_scenes = self.get_equipment_score_relevant_scenes()
res_dict = self.diageo_generator.diageo_global_equipment_score(
save_scene_level=False, scene_list=equipment_score_scenes)
self.commonV2.save_json_to_new_tables(res_dict)
# Global Menu kpis
menus_res_dict = self.diageo_generator.diageo_global_share_of_menu_cocktail_function(
cocktail_product_level=True)
self.commonV2.save_json_to_new_tables(menus_res_dict)
for set_name in set_names:
set_score = 0
if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys(
):
try:
self.set_templates_data[
set_name] = self.tools.download_template(set_name)
except:
Log.warning("Couldn't find a template for set name: " +
str(set_name))
continue
# Global relative position
if set_name in ('Relative Position'):
# Global function
res_dict = self.diageo_generator.diageo_global_relative_position_function(
self.set_templates_data[set_name],
location_type='template_group')
self.commonV2.save_json_to_new_tables(res_dict)
# Saving to old tables
self.set_templates_data[set_name] = parse_template(
RELATIVE_PATH, lower_headers_row_index=2)
set_score = self.calculate_relative_position_sets(set_name)
# elif set_name in ('MPA', 'New Products', 'Local MPA'):
elif set_name in ('Local MPA'):
set_score = self.calculate_assortment_sets(set_name)
# Global Secondary Displays
elif set_name in ('Secondary Displays', 'Secondary'):
# Global function
res_json = self.diageo_generator.diageo_global_secondary_display_secondary_function(
)
if res_json:
# Saving to new tables
self.commonV2.write_to_db_result(
fk=res_json['fk'],
numerator_id=1,
denominator_id=self.store_id,
result=res_json['result'])
# Saving to old tables
set_score = self.tools.calculate_number_of_scenes(
location_type='Secondary')
if not set_score:
set_score = self.tools.calculate_number_of_scenes(
location_type='Secondary Shelf')
self.save_level2_and_level3(set_name, set_name, set_score)
elif set_name == 'POSM':
set_score = self.calculate_posm_sets(set_name)
elif set_name in ('Visible to Customer', 'Visible to Consumer %'):
# Global function
sku_list = filter(
None, self.scif[self.scif['product_type'] ==
'SKU'].product_ean_code.tolist())
res_dict = self.diageo_generator.diageo_global_visible_percentage(
sku_list)
if res_dict:
# Saving to new tables
parent_res = res_dict[-1]
self.commonV2.save_json_to_new_tables(res_dict)
# Saving to old tables
# result = parent_res['result']
# self.save_level2_and_level3(set_name=set_name, kpi_name=set_name, score=result)
# Saving to old tables
filters = {self.tools.VISIBILITY_PRODUCTS_FIELD: 'Y'}
set_score = self.tools.calculate_visible_percentage(
visible_filters=filters)
self.save_level2_and_level3(set_name, set_name, set_score)
else:
continue
if set_score == 0:
pass
elif set_score is False:
continue
set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name']
== set_name]['kpi_set_fk'].values[0]
self.write_to_db_result(set_fk, set_score, self.LEVEL1)
# commiting to new tables
self.commonV2.commit_results_data()
def save_level2_and_level3(self, set_name, kpi_name, score):
"""
Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
"""
kpi_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'].str.encode('utf-8') ==
set_name.encode('utf-8'))
& (self.kpi_static_data['kpi_name'].str.encode('utf-8') ==
kpi_name.encode('utf-8'))]
try:
kpi_fk = kpi_data['kpi_fk'].values[0]
except:
Log.warning("kpi name or set name don't exist")
return
atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, self.LEVEL2)
self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)
def calculate_relative_position_sets(self, set_name):
"""
This function calculates every relative-position-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for i in xrange(len(self.set_templates_data[set_name])):
params = self.set_templates_data[set_name].iloc[i]
if self.store_channel == params.get(self.tools.CHANNEL,
'').upper():
scif_tested_param = 'brand_name' if params.get(self.tools.TESTED_TYPE, '') == self.tools.BRAND \
else 'product_ean_code'
scif_anchor_param = 'brand_name' if params.get(self.tools.ANCHOR_TYPE, '') == self.tools.BRAND \
else 'product_ean_code'
tested_filters = {
scif_tested_param: params.get(self.tools.TESTED_NEW)
}
anchor_filters = {
scif_anchor_param: params.get(self.tools.ANCHOR_NEW)
}
direction_data = {
'top':
self._get_direction_for_relative_position(
params.get(self.tools.TOP_DISTANCE)),
'bottom':
self._get_direction_for_relative_position(
params.get(self.tools.BOTTOM_DISTANCE)),
'left':
self._get_direction_for_relative_position(
params.get(self.tools.LEFT_DISTANCE)),
'right':
self._get_direction_for_relative_position(
params.get(self.tools.RIGHT_DISTANCE))
}
if params.get(self.tools.LOCATION_OLD, ''):
general_filters = {
'template_group': params.get(self.tools.LOCATION_OLD)
}
else:
general_filters = {}
result = self.tools.calculate_relative_position(
tested_filters, anchor_filters, direction_data,
**general_filters)
score = 1 if result else 0
scores.append(score)
self.save_level2_and_level3(set_name,
params.get(self.tools.KPI_NAME),
score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def _get_direction_for_relative_position(self, value):
"""
This function converts direction data from the template (as string) to a number.
"""
if value == self.tools.UNLIMITED_DISTANCE:
value = 1000
elif not value or not str(value).isdigit():
value = 0
else:
value = int(value)
return value
def calculate_posm_sets(self, set_name):
"""
This function calculates every POSM-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.store_channel is None:
break
kpi_res = self.tools.calculate_posm(
display_name=params.get(self.tools.DISPLAY_NAME))
score = 1 if kpi_res > 0 else 0
if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
scores.append(score)
if score == 1 or params.get(
self.store_type) == self.tools.RELEVANT_FOR_STORE:
self.save_level2_and_level3(
set_name, params.get(self.tools.DISPLAY_NAME), score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def calculate_assortment_sets(self, set_name):
"""
This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
target = str(params.get(self.store_type, ''))
if target.isdigit() or target.capitalize() in (
self.tools.RELEVANT_FOR_STORE,
self.tools.OR_OTHER_PRODUCTS):
products = str(
params.get(self.tools.PRODUCT_EAN_CODE,
params.get(self.tools.PRODUCT_EAN_CODE2,
''))).replace(',', ' ').split()
target = 1 if not target.isdigit() else int(target)
kpi_name = params.get(self.tools.GROUP_NAME,
params.get(self.tools.PRODUCT_NAME))
kpi_static_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name)]
if len(products) > 1:
result = 0
for product in products:
product_score = self.tools.calculate_assortment(
product_ean_code=product)
result += product_score
try:
product_name = self.all_products[
self.all_products['product_ean_code'] ==
product]['product_name'].values[0]
except Exception as e:
Log.warning(
'Product {} is not defined in the DB'.format(
product))
continue
try:
atomic_fk = \
kpi_static_data[kpi_static_data['atomic_kpi_name'] == product_name]['atomic_kpi_fk'].values[
0]
except Exception as e:
Log.warning(
'Product {} is not defined in the DB'.format(
product_name))
continue
self.write_to_db_result(atomic_fk,
product_score,
level=self.LEVEL3)
score = 1 if result >= target else 0
else:
result = self.tools.calculate_assortment(
product_ean_code=products)
atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
score = 1 if result >= target else 0
self.write_to_db_result(atomic_fk,
score,
level=self.LEVEL3)
scores.append(score)
kpi_fk = kpi_static_data['kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
# def calculate_assortment_sets(self, set_name): # the old version. I changed it to the function of KE for local MPA.
# """
# This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
# """
# scores = []
# for params in self.set_templates_data[set_name]:
# if params.get(self.store_type, '').capitalize() in (self.tools.RELEVANT_FOR_STORE,
# self.tools.OR_OTHER_PRODUCTS):
# object_type = self.tools.ENTITY_TYPE_CONVERTER.get(params.get(self.tools.ENTITY_TYPE),
# 'product_ean_code')
# objects = [str(params.get(self.tools.PRODUCT_EAN_CODE, params.get(self.tools.PRODUCT_EAN_CODE2, '')))]
# if params.get(self.store_type) == self.tools.OR_OTHER_PRODUCTS:
# additional_objects = str(params.get(self.tools.ADDITIONAL_SKUS)).split(',')
# objects.extend(additional_objects)
# filters = {object_type: objects}
# result = self.tools.calculate_assortment(**filters)
# score = 1 if result > 0 else 0
# scores.append(score)
#
# self.save_level2_and_level3(set_name, params.get(self.tools.PRODUCT_NAME), score)
#
# if not scores:
# return False
# set_score = (sum(scores) / float(len(scores))) * 100
# return set_score
def write_to_db_result(self, fk, score, level):
"""
This function 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.
"""
attributes = self.create_attributes_dict(fk, score, level)
if level == self.LEVEL1:
table = KPS_RESULT
elif level == self.LEVEL2:
table = KPK_RESULT
elif level == self.LEVEL3:
table = KPI_RESULT
else:
return
query = insert(attributes, table)
self.kpi_results_queries.append(query)
def create_attributes_dict(self, fk, score, level):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
"""
score = round(score, 2)
if level == self.LEVEL1:
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['kpi_set_fk'] ==
fk]['kpi_set_name'].values[0]
score_type = '%' if kpi_set_name in self.tools.KPI_SETS_WITH_PERCENT_AS_SCORE else ''
attributes = pd.DataFrame(
[(kpi_set_name, self.session_uid, self.store_id,
self.visit_date.isoformat(), format(score,
'.2f'), score_type, fk)],
columns=[
'kps_name', 'session_uid', 'store_fk', 'visit_date',
'score_1', 'score_2', 'kpi_set_fk'
])
elif level == self.LEVEL2:
kpi_name = self.kpi_static_data[self.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.LEVEL3:
data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
== fk]
atomic_kpi_name = data['atomic_kpi_name'].values[0].replace(
"'", "\\'")
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['atomic_kpi_fk'] ==
fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame([
(atomic_kpi_name, self.session_uid,
kpi_set_name, self.store_id, self.visit_date.isoformat(),
datetime.utcnow().isoformat(), score, kpi_fk, fk, None, None)
],
columns=[
'display_text', 'session_uid',
'kps_name', 'store_fk', 'visit_date',
'calculation_time', 'score',
'kpi_fk', 'atomic_kpi_fk',
'threshold', 'result'
])
else:
attributes = pd.DataFrame()
return attributes.to_dict()
def activate_ootb_kpis(self):
# FACINGS_SOS_MANUFACTURER_IN_WHOLE_STORE - level 1
sos_store_fk = self.commonV2.get_kpi_fk_by_kpi_name('SOS OUT OF STORE')
sos_store = ManufacturerFacingsSOSInWholeStore(
data_provider=self.data_provider,
kpi_definition_fk=sos_store_fk).calculate()
# FACINGS_SOS_CATEGORY_IN_WHOLE_STORE - level 2
sos_cat_out_of_store_fk = self.commonV2.get_kpi_fk_by_kpi_name(
'SOS CATEGORY OUT OF STORE')
sos_cat_out_of_store = self.calculate_sos_of_cat_of_out_of_store_new(
sos_cat_out_of_store_fk)
# FACINGS_SOS_SUB_CATEGORY_OUT_OF_CATEGORY - level 3
sos_sub_cat_out_of_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name(
'SOS SUB CATEGORY OUT OF CATEGORY')
sos_sub_cat_out_of_cat = SubCategoryFacingsSOSPerCategory(
data_provider=self.data_provider,
kpi_definition_fk=sos_sub_cat_out_of_cat_fk).calculate()
# FACINGS_SOS_MANUFACTURER_OUT_OF_SUB_CATEGORY - level 4
sos_man_out_of_sub_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name(
'SOS MANUFACTURER OUT OF SUB CATEGORY')
sos_man_out_of_sub_cat = ManufacturerFacingsSOSPerSubCategoryInStore(
data_provider=self.data_provider,
kpi_definition_fk=sos_man_out_of_sub_cat_fk).calculate()
# FACINGS_SOS_BRAND_OUT_OF_SUB_CATEGORY_IN_WHOLE_STORE - level 5
sos_brand_out_of_sub_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name(
'SOS BRAND OUT OF MANUFACTURER')
sos_brand_out_of_sub_cat = self.calculate_sos_of_brand_out_of_manufacturer_in_sub_cat(
sos_brand_out_of_sub_cat_fk)
# Savings results in Hierarchy
self.save_hierarchy(sos_store, sos_cat_out_of_store,
sos_sub_cat_out_of_cat, sos_man_out_of_sub_cat,
sos_brand_out_of_sub_cat)
def calculate_sos_of_brand_out_of_manufacturer_in_sub_cat(self, kpi_fk):
res_list = []
res_dict = dict()
# Get rid of Irrelevant and Empty types and keep only facings > 1
filtered_scif = self.scif[
~self.scif['product_type'].isin(['Irrelevant', 'Empty'])
& self.scif['facings_ign_stack'] > 0]
# Filter by each Sub Category and Manufacturer
sub_cat_fk_list = filtered_scif['sub_category_fk'].unique().tolist()
for sub_cat in sub_cat_fk_list:
filtered_scif_by_sub_cat = filtered_scif[
filtered_scif['sub_category_fk'] == sub_cat]
list_of_relevant_manufacturers = filtered_scif_by_sub_cat[
'manufacturer_fk'].unique().tolist()
for manu_fk in list_of_relevant_manufacturers:
filtered_scif_by_sub_cat_and_manufacturer = filtered_scif_by_sub_cat[
filtered_scif_by_sub_cat['manufacturer_fk'] == manu_fk]
denominator_result = filtered_scif_by_sub_cat_and_manufacturer[
'facings_ign_stack'].sum()
# Calculate results per Brand
list_of_relevant_brands = filtered_scif_by_sub_cat_and_manufacturer[
'brand_fk'].unique().tolist()
for brand_fk in list_of_relevant_brands:
filtered_scif_by_brand = filtered_scif_by_sub_cat_and_manufacturer[
filtered_scif_by_sub_cat_and_manufacturer['brand_fk']
== brand_fk]
facings_brand_results = filtered_scif_by_brand[
'facings_ign_stack'].sum()
result_for_brand = facings_brand_results / denominator_result
# Preparing the results' dictionary
res_dict['kpi_definition_fk'] = kpi_fk
res_dict['numerator_id'] = brand_fk
res_dict['numerator_result'] = facings_brand_results
res_dict['denominator_id'] = int(sub_cat)
res_dict['denominator_result'] = denominator_result
res_dict['identifier_result'] = (int(brand_fk),
int(sub_cat),
int(manu_fk))
res_dict['identifier_parent'] = int(manu_fk), (
int(sub_cat))
res_dict['result'] = result_for_brand
res_dict['score'] = result_for_brand
res_list.append(res_dict.copy())
return res_list
def calculate_sos_of_cat_of_out_of_store_new(self, kpi_fk):
res_list = []
res_dict = dict()
# Get rid of Irrelevant and Empty types and keep only facings ignore stacking > 1
filtered_scif = self.scif[
~self.scif['product_type'].isin(['Irrelevant', 'Empty'])
& self.scif['facings_ign_stack'] > 0]
denominator_result = filtered_scif['facings_ign_stack'].sum()
categories_fk_list = filtered_scif['category_fk'].unique().tolist()
# Calculate result per category (using facings_ign_stack!)
for category_fk in categories_fk_list:
filtered_scif_by_category = filtered_scif[
filtered_scif['category_fk'] == category_fk]
facings_category_result = filtered_scif_by_category[
'facings_ign_stack'].sum()
result_for_category = facings_category_result / denominator_result
# Preparing the results' dictionary
res_dict['kpi_definition_fk'] = kpi_fk
res_dict['numerator_id'] = category_fk
res_dict['numerator_result'] = facings_category_result
res_dict['denominator_id'] = self.store_id
res_dict['denominator_result'] = denominator_result
res_dict['result'] = result_for_category
res_dict['score'] = result_for_category
res_list.append(res_dict.copy())
return res_list
def save_hierarchy(self, level_1, level_2, level_3, level_4, level_5):
for i in level_1:
res = i.to_dict
kpi_identifier = "level_1"
self.commonV2.write_to_db_result(
fk=res['kpi_definition_fk'],
numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'],
result=res['result'],
score=res['result'],
identifier_result=kpi_identifier,
should_enter=False)
for res in level_2:
kpi_identifier = "level_2_" + str(int(res['numerator_id']))
parent_identifier = "level_1"
self.commonV2.write_to_db_result(
fk=res['kpi_definition_fk'],
numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'],
result=res['result'],
score=res['result'],
identifier_result=kpi_identifier,
identifier_parent=parent_identifier,
should_enter=True)
for i in level_3:
res = i.to_dict
kpi_identifier = str(int(res['numerator_id']))
parent_identifier = "level_2_" + str(int(res['denominator_id']))
self.commonV2.write_to_db_result(
fk=res['kpi_definition_fk'],
numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'],
result=res['result'],
score=res['result'],
identifier_result=kpi_identifier,
identifier_parent=parent_identifier,
should_enter=True)
for i in level_4:
res = i.to_dict
kpi_identifier = "level_4_" + str(
(int(res['numerator_id']), int(res['denominator_id'])))
parent_identifier = str(int(res['denominator_id']))
self.commonV2.write_to_db_result(
fk=res['kpi_definition_fk'],
numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'],
result=res['result'],
score=res['result'],
identifier_result=kpi_identifier,
identifier_parent=parent_identifier,
should_enter=True)
for res in level_5:
kpi_identifier = "level_5_" + str(res['identifier_result'])
parent_identifier = "level_4_" + str(res['identifier_parent'])
self.commonV2.write_to_db_result(
fk=res['kpi_definition_fk'],
numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'],
result=res['result'],
score=res['result'],
identifier_result=kpi_identifier,
identifier_parent=parent_identifier,
should_enter=True)
@log_runtime('Saving to DB')
def commit_results_data(self):
"""
This function writes all KPI results to the DB, and commits the changes.
"""
self.rds_conn.disconnect_rds()
self.rds_conn.connect_rds()
cur = self.rds_conn.db.cursor()
delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
self.session_uid)
for query in delete_queries:
cur.execute(query)
for query in self.kpi_results_queries:
cur.execute(query)
self.rds_conn.db.commit()
def get_equipment_score_relevant_scenes(self):
scenes = []
if not self.diageo_generator.scif.empty:
scenes = self.diageo_generator.scif[self.diageo_generator.scif['template_name'] == \
'ON - DRAUGHT TAPS']['scene_fk'].unique().tolist()
return scenes
class DIAGEOTWToolBox:
SOS_SETS = 'SOS_SETS'
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
ACTIVATION_STANDARD = 'Activation Standard'
def __init__(self, data_provider, output):
self.k_engine = BaseCalculationsScript(data_provider, 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.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_channel = self.store_info['store_type'].values[0]
if self.store_channel:
self.store_channel = self.store_channel.upper()
self.store_type = self.store_info['additional_attribute_1'].values[0]
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.match_display_in_scene = self.get_match_display()
self.set_templates_data = {}
self.kpi_static_data = self.get_kpi_static_data()
self.scores = {self.LEVEL1: {},
self.LEVEL2: {},
self.LEVEL3: {}}
self.kpi_results_queries = []
self.output = output
self.common = Common(self.data_provider)
self.commonV2 = CommonV2(self.data_provider)
self.tools = DIAGEOToolBox(self.data_provider, output,
match_display_in_scene=self.match_display_in_scene) # replace the old one
self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common)
def get_kpi_static_data(self):
"""
This function extracts the static KPI data and saves it into one global data frame.
The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
"""
query = DIAGEOQueries.get_all_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
def get_match_display(self):
"""
This function extracts the display matches data and saves it into one global data frame.
The data is taken from probedata.match_display_in_scene.
"""
query = DIAGEOQueries.get_match_display(self.session_uid)
match_display = pd.read_sql_query(query, self.rds_conn.db)
return match_display
def main_calculation(self, set_names):
"""
This function calculates the KPI results.
"""
# SOS Out Of The Box kpis
self.activate_ootb_kpis()
# sos by scene type
self.diageo_generator.sos_by_scene_type(self.commonV2)
# Global assortment kpis
assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2()
self.commonV2.save_json_to_new_tables(assortment_res_dict)
# Global assortment kpis - v3 for NEW MOBILE REPORTS use
assortment_res_dict_v3 = self.diageo_generator.diageo_global_assortment_function_v3()
self.commonV2.save_json_to_new_tables(assortment_res_dict_v3)
# Global menu calculation
menus_res_dict = self.diageo_generator.diageo_global_new_share_of_menu_function()
self.commonV2.save_json_to_new_tables(menus_res_dict)
for set_name in set_names:
# if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys():
# self.set_templates_data[set_name] = self.tools.download_template(set_name)
# if set_name in ('Local MPA', 'MPA', 'New Products',):
# set_score = self.calculate_assortment_sets(set_name)
# if set_name in ('Relative Position',):
# set_score = self.calculate_relative_position_sets(set_name)
# elif set_name in ('Brand Blocking',):
# set_score = self.calculate_block_together_sets(set_name)
if set_name in ('SOS',):
set_score = self.calculate_sos_sets(set_name)
if set_score:
fk = self.common.get_kpi_fk_by_kpi_name_new_tables(self.SOS_SETS)
self.commonV2.write_to_db_result(fk=fk, numerator_id=1, denominator_id=self.store_id,
result=set_score)
# elif set_name == 'Visible to Customer':
# visible_filters = {self.tools.VISIBILITY_PRODUCTS_FIELD: 'Y'}
# filters = {"manufacturer_name": "Diageo"}
# set_score = self.tools.calculate_visible_percentage(visible_filters=visible_filters, **filters)
# self.save_level2_and_level3(set_name, set_name, set_score)
# elif set_name == 'Secondary':
# set_score = self.tools.calculate_number_of_scenes(location_type='Secondary')
# self.save_level2_and_level3(set_name, set_name, set_score)
# elif set_name == 'Survey Questions':
# set_score = self.calculate_survey_sets(set_name)
# elif set_name in ('POSM',):
# set_score = self.calculate_posm_sets(set_name)
else:
continue
if set_score == 0:
pass
elif set_score is False:
continue
set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name'] == set_name]['kpi_set_fk'].values[0]
self.write_to_db_result(set_fk, set_score, self.LEVEL1)
# commiting to new tables
self.commonV2.commit_results_data()
def save_level2_and_level3(self, set_name, kpi_name, score):
"""
Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
"""
kpi_data = self.kpi_static_data[(self.kpi_static_data['kpi_set_name'] == set_name) &
(self.kpi_static_data['kpi_name'] == kpi_name)]
kpi_fk = kpi_data['kpi_fk'].values[0]
atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, self.LEVEL2)
self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)
def calculate_activation_standard(self):
"""
This function calculates the Activation Standard KPI, and saves the result to the DB (for all 3 levels).
"""
final_score = 0
for params in self.tools.download_template(self.ACTIVATION_STANDARD):
set_name = params.get(self.tools.ACTIVATION_SET_NAME)
kpi_name = params.get(self.tools.ACTIVATION_KPI_NAME)
target = float(params.get(self.tools.ACTIVATION_TARGET))
target = target * 100 if target < 1 else target
score_type = params.get(self.tools.ACTIVATION_SCORE)
weight = float(params.get(self.tools.ACTIVATION_WEIGHT))
if kpi_name:
kpi_fk = self.kpi_static_data[(self.kpi_static_data['kpi_set_name'] == set_name) &
(self.kpi_static_data['kpi_name'] == kpi_name)]['kpi_fk'].values[0]
score = self.scores[self.LEVEL2].get(kpi_fk, 0)
else:
set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name'] == set_name]['kpi_set_fk'].values[0]
score = self.scores[self.LEVEL1].get(set_fk, 0)
if score >= target:
score = 100
else:
if score_type == 'PROPORTIONAL':
score = (score / float(target)) * 100
else:
score = 0
final_score += score * weight
self.save_level2_and_level3(self.ACTIVATION_STANDARD, set_name, score)
set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name'] ==
self.ACTIVATION_STANDARD]['kpi_set_fk'].values[0]
self.write_to_db_result(set_fk, final_score, self.LEVEL1)
def calculate_sos_sets(self, set_name):
"""
This function calculates every SOS-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
result = self.tools.calculate_share_of_shelf(manufacturer=self.tools.DIAGEO,
include_empty=self.tools.EXCLUDE_EMPTY)
score = 1 if result >= params.get(self.tools.TARGET) else 0
scores.append(score)
self.save_level2_and_level3(set_name, params.get(self.tools.KPI_NAME), score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def calculate_survey_sets(self, set_name):
"""
This function calculates every Survey-Answer-typed KPI and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
survey_text = params.get(self.tools.SURVEY_QUESTION)
answer_field = 'number_value' if params.get(self.tools.SURVEY_ANSWER_TYPE) == 'Numeric' else None
answer_target = params.get(self.tools.SURVEY_ANSWER)
survey_answer = self.tools.get_survey_answer(survey_text, answer_field)
if answer_field == 'number_value':
if str(answer_target).isdigit():
answer_target = '=={}'.format(answer_target)
if not str(survey_answer).isdigit():
result = False
else:
result = eval('{}{}'.format(survey_answer, answer_target))
else:
result = True if survey_answer in answer_target.split(';') else False
score = 1 if result else 0
scores.append(score)
self.save_level2_and_level3(set_name, params.get(self.tools.KPI_NAME), score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def validate_survey(self, params):
"""
This function checks whether the Survey-typed KPI should be run, based on the Execution Condition Field.
"""
survey_validated = False
condition = params.get(self.tools.SURVEY_CONDITION)
if not condition:
survey_validated = True
else:
question, answers = condition.split('=')
survey_answer = self.tools.get_survey_answer(question)
if survey_answer in answers.split(';'):
survey_validated = True
return survey_validated
def calculate_block_together_sets(self, set_name):
"""
This function calculates every block-together-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.store_channel == params.get(self.tools.CHANNEL, '').upper():
filters = {'template_name': params.get(self.tools.LOCATION)}
if params.get(self.tools.SUB_BRAND_NAME):
filters['sub_brand_name'] = params.get(self.tools.SUB_BRAND_NAME)
else:
filters['brand_name'] = params.get(self.tools.BRAND_NAME)
result = self.tools.calculate_block_together(**filters)
score = 1 if result else 0
scores.append(score)
self.save_level2_and_level3(set_name, params.get(self.tools.KPI_NAME), score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def calculate_relative_position_sets(self, set_name):
"""
This function calculates every relative-position-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.store_channel == params.get(self.tools.CHANNEL, '').upper():
tested_filters = {'product_ean_code': params.get(self.tools.TESTED)}
anchor_filters = {'product_ean_code': params.get(self.tools.ANCHOR)}
direction_data = {'top': self._get_direction_for_relative_position(params.get(self.tools.TOP_DISTANCE)),
'bottom': self._get_direction_for_relative_position(
params.get(self.tools.BOTTOM_DISTANCE)),
'left': self._get_direction_for_relative_position(
params.get(self.tools.LEFT_DISTANCE)),
'right': self._get_direction_for_relative_position(
params.get(self.tools.RIGHT_DISTANCE))}
general_filters = {'template_name': params.get(self.tools.LOCATION)}
result = self.tools.calculate_relative_position(tested_filters, anchor_filters, direction_data,
**general_filters)
score = 1 if result else 0
scores.append(score)
self.save_level2_and_level3(set_name, params.get(self.tools.KPI_NAME), score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def _get_direction_for_relative_position(self, value):
"""
This function converts direction data from the template (as string) to a number.
"""
if value == self.tools.UNLIMITED_DISTANCE:
value = 1000
elif not value or not str(value).isdigit():
value = 0
else:
value = int(value)
return value
def calculate_posm_sets(self, set_name):
"""
This function calculates every POSM-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.store_channel is None:
break
kpi_res = self.tools.calculate_posm(display_name=params.get(self.tools.DISPLAY_NAME))
score = 1 if kpi_res > 0 else 0
if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
scores.append(score)
if score == 1 or params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
self.save_level2_and_level3(set_name, params.get(self.tools.DISPLAY_NAME), score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def calculate_assortment_sets(self, set_name):
"""
This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
target = str(params.get(self.store_type, ''))
if target.isdigit() or target.capitalize() in (self.tools.RELEVANT_FOR_STORE, self.tools.OR_OTHER_PRODUCTS):
products = str(params.get(self.tools.PRODUCT_EAN_CODE,
params.get(self.tools.PRODUCT_EAN_CODE2, ''))).replace(',', ' ').split()
target = 1 if not target.isdigit() else int(target)
kpi_name = params.get(self.tools.GROUP_NAME, params.get(self.tools.PRODUCT_NAME))
kpi_static_data = self.kpi_static_data[(self.kpi_static_data['kpi_set_name'] == set_name) &
(self.kpi_static_data['kpi_name'] == kpi_name)]
if len(products) > 1:
result = 0
for product in products:
product_score = self.tools.calculate_assortment(product_ean_code=product)
result += product_score
product_name = \
self.all_products[self.all_products['product_ean_code'] == product]['product_name'].values[
0]
try:
atomic_fk = \
kpi_static_data[kpi_static_data['atomic_kpi_name'] == product_name][
'atomic_kpi_fk'].values[
0]
except Exception as e:
Log.info('Product {} is not defined in the DB'.format(product_name))
continue
self.write_to_db_result(atomic_fk, product_score, level=self.LEVEL3)
score = 1 if result >= target else 0
else:
result = self.tools.calculate_assortment(product_ean_code=products)
atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
score = 1 if result >= target else 0
self.write_to_db_result(atomic_fk, score, level=self.LEVEL3)
scores.append(score)
kpi_fk = kpi_static_data['kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def write_to_db_result(self, fk, score, level):
"""
This function 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.
"""
attributes = self.create_attributes_dict(fk, score, level)
if level == self.LEVEL1:
table = KPS_RESULT
elif level == self.LEVEL2:
table = KPK_RESULT
elif level == self.LEVEL3:
table = KPI_RESULT
else:
return
self.scores[level][fk] = score
query = insert(attributes, table)
self.kpi_results_queries.append(query)
def activate_ootb_kpis(self):
# FACINGS_SOS_MANUFACTURER_IN_WHOLE_STORE - level 1
sos_store_fk = self.commonV2.get_kpi_fk_by_kpi_name('SOS OUT OF STORE')
sos_store = ManufacturerFacingsSOSInWholeStore(data_provider=self.data_provider,
kpi_definition_fk=sos_store_fk).calculate()
# FACINGS_SOS_CATEGORY_IN_WHOLE_STORE - level 2
sos_cat_out_of_store_fk = self.commonV2.get_kpi_fk_by_kpi_name('SOS CATEGORY OUT OF STORE')
sos_cat_out_of_store = self.calculate_sos_of_cat_of_out_of_store_new(sos_cat_out_of_store_fk)
# FACINGS_SOS_SUB_CATEGORY_OUT_OF_CATEGORY - level 3
sos_sub_cat_out_of_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name('SOS SUB CATEGORY OUT OF CATEGORY')
sos_sub_cat_out_of_cat = SubCategoryFacingsSOSPerCategory(data_provider=self.data_provider,
kpi_definition_fk=sos_sub_cat_out_of_cat_fk).calculate()
# FACINGS_SOS_MANUFACTURER_OUT_OF_SUB_CATEGORY - level 4
sos_man_out_of_sub_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name('SOS MANUFACTURER OUT OF SUB CATEGORY')
sos_man_out_of_sub_cat = ManufacturerFacingsSOSPerSubCategoryInStore(
data_provider=self.data_provider, kpi_definition_fk=sos_man_out_of_sub_cat_fk).calculate()
# FACINGS_SOS_BRAND_OUT_OF_SUB_CATEGORY_IN_WHOLE_STORE - level 5
sos_brand_out_of_sub_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name('SOS BRAND OUT OF MANUFACTURER')
sos_brand_out_of_sub_cat = self.calculate_sos_of_brand_out_of_manufacturer_in_sub_cat(
sos_brand_out_of_sub_cat_fk)
# Savings results in Hierarchy
self.save_hierarchy(sos_store, sos_cat_out_of_store, sos_sub_cat_out_of_cat, sos_man_out_of_sub_cat,
sos_brand_out_of_sub_cat)
def calculate_sos_of_brand_out_of_manufacturer_in_sub_cat(self, kpi_fk):
pass
res_list = []
res_dict = dict()
# Get rid of Irrelevant and Empty types and keep only facings > 1
filtered_scif = self.scif[
~self.scif['product_type'].isin(['Irrelevant', 'Empty']) & self.scif['facings_ign_stack'] > 0]
# Filter by each Sub Category and Manufacturer
sub_cat_fk_list = filtered_scif['sub_category_fk'].unique().tolist()
for sub_cat in sub_cat_fk_list:
filtered_scif_by_sub_cat = filtered_scif[filtered_scif['sub_category_fk'] == sub_cat]
list_of_relevant_manufacturers = filtered_scif_by_sub_cat['manufacturer_fk'].unique().tolist()
for manu_fk in list_of_relevant_manufacturers:
filtered_scif_by_sub_cat_and_manufacturer = filtered_scif_by_sub_cat[
filtered_scif_by_sub_cat['manufacturer_fk'] == manu_fk]
denominator_result = filtered_scif_by_sub_cat_and_manufacturer['facings_ign_stack'].sum()
# Calculate results per Brand
list_of_relevant_brands = filtered_scif_by_sub_cat_and_manufacturer['brand_fk'].unique().tolist()
for brand_fk in list_of_relevant_brands:
filtered_scif_by_brand = filtered_scif_by_sub_cat_and_manufacturer[
filtered_scif_by_sub_cat_and_manufacturer['brand_fk'] == brand_fk]
facings_brand_results = filtered_scif_by_brand['facings_ign_stack'].sum()
result_for_brand = facings_brand_results / denominator_result
# Preparing the results' dictionary
res_dict['kpi_definition_fk'] = kpi_fk
res_dict['numerator_id'] = brand_fk
res_dict['numerator_result'] = facings_brand_results
res_dict['denominator_id'] = int(sub_cat)
res_dict['denominator_result'] = denominator_result
res_dict['identifier_result'] = (int(brand_fk), int(sub_cat), int(manu_fk))
res_dict['identifier_parent'] = int(manu_fk), (int(sub_cat))
res_dict['result'] = result_for_brand
res_dict['score'] = result_for_brand
res_list.append(res_dict.copy())
return res_list
def calculate_sos_of_cat_of_out_of_store_new(self, kpi_fk):
res_list = []
res_dict = dict()
# Get rid of Irrelevant and Empty types and keep only facings ignore stacking > 1
filtered_scif = self.scif[
~self.scif['product_type'].isin(['Irrelevant', 'Empty']) & self.scif['facings_ign_stack'] > 0]
denominator_result = filtered_scif['facings_ign_stack'].sum()
categories_fk_list = filtered_scif['category_fk'].unique().tolist()
# Calculate result per category (using facings_ign_stack!)
for category_fk in categories_fk_list:
filtered_scif_by_category = filtered_scif[filtered_scif['category_fk'] == category_fk]
facings_category_result = filtered_scif_by_category['facings_ign_stack'].sum()
result_for_category = facings_category_result / denominator_result
# Preparing the results' dictionary
res_dict['kpi_definition_fk'] = kpi_fk
res_dict['numerator_id'] = category_fk
res_dict['numerator_result'] = facings_category_result
res_dict['denominator_id'] = self.store_id
res_dict['denominator_result'] = denominator_result
res_dict['result'] = result_for_category
res_dict['score'] = result_for_category
res_list.append(res_dict.copy())
return res_list
def save_hierarchy(self, level_1, level_2, level_3, level_4, level_5):
for i in level_1:
res = i.to_dict
kpi_identifier = "level_1"
self.commonV2.write_to_db_result(fk=res['kpi_definition_fk'], numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'], result=res['result'],
score=res['result'],
identifier_result=kpi_identifier, should_enter=False)
for res in level_2:
kpi_identifier = "level_2_" + str(int(res['numerator_id']))
parent_identifier = "level_1"
self.commonV2.write_to_db_result(fk=res['kpi_definition_fk'], numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'], result=res['result'],
score=res['result'],
identifier_result=kpi_identifier,
identifier_parent=parent_identifier, should_enter=True)
for i in level_3:
res = i.to_dict
kpi_identifier = str(int(res['numerator_id']))
parent_identifier = "level_2_" + str(int(res['denominator_id']))
self.commonV2.write_to_db_result(fk=res['kpi_definition_fk'], numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'], result=res['result'],
score=res['result'],
identifier_result=kpi_identifier,
identifier_parent=parent_identifier, should_enter=True)
for i in level_4:
res = i.to_dict
kpi_identifier = "level_4_" + str((int(res['numerator_id']), int(res['denominator_id'])))
parent_identifier = str(int(res['denominator_id']))
self.commonV2.write_to_db_result(fk=res['kpi_definition_fk'], numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'], result=res['result'],
score=res['result'],
identifier_result=kpi_identifier,
identifier_parent=parent_identifier, should_enter=True)
for res in level_5:
kpi_identifier = "level_5_" + str(res['identifier_result'])
parent_identifier = "level_4_" + str(res['identifier_parent'])
self.commonV2.write_to_db_result(fk=res['kpi_definition_fk'], numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'], result=res['result'],
score=res['result'],
identifier_result=kpi_identifier, identifier_parent=parent_identifier,
should_enter=True)
def create_attributes_dict(self, fk, score, level):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
"""
score = round(score, 2)
if level == self.LEVEL1:
kpi_set_name = self.kpi_static_data[self.kpi_static_data['kpi_set_fk'] == fk]['kpi_set_name'].values[0]
score_type = '%' if kpi_set_name in self.tools.KPI_SETS_WITH_PERCENT_AS_SCORE else ''
attributes = pd.DataFrame([(kpi_set_name, self.session_uid, self.store_id, self.visit_date.isoformat(),
format(score, '.2f'), score_type, fk)],
columns=['kps_name', 'session_uid', 'store_fk', 'visit_date', 'score_1',
'score_2', 'kpi_set_fk'])
elif level == self.LEVEL2:
kpi_name = self.kpi_static_data[self.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.LEVEL3:
data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk'] == fk]
atomic_kpi_name = data['atomic_kpi_name'].values[0].replace("'", "\\'")
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk'] == fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame([(atomic_kpi_name, self.session_uid, kpi_set_name, self.store_id,
self.visit_date.isoformat(), datetime.utcnow().isoformat(),
score, kpi_fk, fk, None, None)],
columns=['display_text', 'session_uid', 'kps_name', 'store_fk', 'visit_date',
'calculation_time', 'score', 'kpi_fk', 'atomic_kpi_fk', 'threshold',
'result'])
else:
attributes = pd.DataFrame()
return attributes.to_dict()
@log_runtime('Saving to DB')
def commit_results_data(self):
"""
This function writes all KPI results to the DB, and commits the changes.
"""
cur = self.rds_conn.db.cursor()
delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(self.session_uid)
for query in delete_queries:
cur.execute(query)
for query in self.kpi_results_queries:
cur.execute(query)
self.rds_conn.db.commit()
class DIAGEOKE_SANDToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.k_engine = BaseCalculationsScript(data_provider, 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.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_channel = self.store_info['store_type'].values[0]
if self.store_channel:
self.store_channel = self.store_channel.upper()
self.store_type = self.store_info['additional_attribute_1'].values[0]
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.match_display_in_scene = self.get_match_display()
self.set_templates_data = {}
self.kpi_static_data = self.get_kpi_static_data()
self.kpi_results_queries = []
self.output = output
self.common = Common(self.data_provider)
self.commonV2 = CommonV2(self.data_provider)
self.tools = DIAGEOToolBox(
self.data_provider,
output,
match_display_in_scene=self.match_display_in_scene)
self.diageo_generator = DIAGEOGenerator(self.data_provider,
self.output, self.common)
def get_kpi_static_data(self):
"""
This function extracts the static KPI data and saves it into one global data frame.
The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
"""
query = DIAGEOQueries.get_all_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
def get_match_display(self):
"""
This function extracts the display matches data and saves it into one global data frame.
The data is taken from probedata.match_display_in_scene.
"""
query = DIAGEOQueries.get_match_display(self.session_uid)
match_display = pd.read_sql_query(query, self.rds_conn.db)
return match_display
def main_calculation(self, set_names):
"""
This function calculates the KPI results.
"""
# Global assortment kpis
assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2(
)
self.commonV2.save_json_to_new_tables(assortment_res_dict)
for set_name in set_names:
set_score = 0
# Global Visible to Customer / Visible to Consumer
if set_name in ('Visible to Customer', 'Visible to Consumer %'):
# Global function
sku_list = filter(
None, self.scif[self.scif['product_type'] ==
'SKU'].product_ean_code.tolist())
res_dict = self.diageo_generator.diageo_global_visible_percentage(
sku_list)
if res_dict:
# Saving to new tables
parent_res = res_dict[-1]
self.commonV2.save_json_to_new_tables(res_dict)
# Saving to old tables
set_score = result = parent_res['result']
self.save_level2_and_level3(set_name=set_name,
kpi_name=set_name,
score=result)
if set_score == 0:
pass
elif set_score is False:
continue
set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name']
== set_name]['kpi_set_fk'].values[0]
self.write_to_db_result(set_fk, set_score, self.LEVEL1)
# committing to new tables
self.commonV2.commit_results_data()
return
def save_level2_and_level3(self, set_name, kpi_name, score):
"""
Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
"""
kpi_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name)]
kpi_fk = kpi_data['kpi_fk'].values[0]
atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, self.LEVEL2)
self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)
def calculate_sos_set(self):
# todo: This is a deprecated function
"""
This function calculates SOS for the Manufacturer (Level1), Category (Level2) and Brand (Level3) levels.
"""
# diageo_data = self.scif[self.scif['manufacturer_name'] == DIAGEO]
categories = self.all_products[self.all_products['manufacturer_name']
==
DIAGEO]['category'].unique().tolist()
for category in categories:
kpi_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == 'Local SOS')
& (self.kpi_static_data['kpi_name'] == category)]
if not kpi_data.empty:
kpi_fk = kpi_data['kpi_fk'].values[0]
kpi_result = self.tools.calculate_share_of_shelf(
sos_filters={'category': category},
include_empty=self.tools.INCLUDE_EMPTY,
category=(GENERAL, self.tools.EXCLUDE_FILTER))
self.write_to_db_result(kpi_fk,
round(kpi_result * 100, 2),
level=self.LEVEL2)
category_data = self.scif[self.scif['category'] == category]
for brand in category_data['brand_name'].unique():
atomic_data = kpi_data[kpi_data['atomic_kpi_name'] ==
brand]
if not atomic_data.empty:
atomic_fk = atomic_data['atomic_kpi_fk'].values[0]
numerator = self.tools.calculate_availability(
brand_name=brand, category=category)
denominator = self.tools.calculate_availability(
category=category)
if denominator:
atomic_result = numerator / float(denominator)
else:
atomic_result = 0
self.write_to_db_result(atomic_fk,
round(atomic_result * 100, 2),
level=self.LEVEL3,
result='{}/{}'.format(
int(numerator),
int(denominator)))
set_result = self.tools.calculate_share_of_shelf(
sos_filters={'manufacturer_name': DIAGEO},
include_empty=self.tools.INCLUDE_EMPTY,
category=(GENERAL, self.tools.EXCLUDE_FILTER))
set_result = round(set_result * 100, 2)
return set_result
def calculate_posm_sets(self, set_name):
"""
This function calculates every POSM-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.store_info['store_type'] is None:
break
kpi_res = self.tools.calculate_posm(
display_name=params.get(self.tools.DISPLAY_NAME))
score = 1 if kpi_res > 0 else 0
if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
scores.append(score)
if score == 1 or params.get(
self.store_type) == self.tools.RELEVANT_FOR_STORE:
self.save_level2_and_level3(
set_name, params.get(self.tools.DISPLAY_NAME), score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def calculate_assortment_sets(self, set_name):
"""
This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
target = str(params.get(self.store_type, ''))
if target.isdigit() or target.capitalize() in (
self.tools.RELEVANT_FOR_STORE,
self.tools.OR_OTHER_PRODUCTS):
products = str(
params.get(self.tools.PRODUCT_EAN_CODE,
params.get(self.tools.PRODUCT_EAN_CODE2,
''))).replace(',', ' ').split()
target = 1 if not target.isdigit() else int(target)
kpi_name = params.get(self.tools.GROUP_NAME,
params.get(self.tools.PRODUCT_NAME))
kpi_static_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name)]
if len(products) > 1:
result = 0
for product in products:
product_score = self.tools.calculate_assortment(
product_ean_code=product)
result += product_score
product_name = self.all_products[
self.all_products['product_ean_code'] ==
product]['product_name'].values[0]
try:
atomic_fk = kpi_static_data[
kpi_static_data['atomic_kpi_name'] ==
product_name]['atomic_kpi_fk'].values[0]
except Exception as e:
Log.info(
'Product {} is not defined in the DB'.format(
product_name))
continue
self.write_to_db_result(atomic_fk,
product_score,
level=self.LEVEL3)
score = 1 if result >= target else 0
else:
result = self.tools.calculate_assortment(
product_ean_code=products)
atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
score = 1 if result >= target else 0
self.write_to_db_result(atomic_fk,
score,
level=self.LEVEL3)
scores.append(score)
kpi_fk = kpi_static_data['kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
# def calculate_sos_sets(self, set_name):
# """
# This function calculates every SOS-typed KPI from the relevant sets, and returns the set final score.
# """
# scores = []
# for params in self.set_templates_data[set_name]:
# if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
# result = self.tools.calculate_share_of_shelf(manufacturer=self.tools.DIAGEO,
# include_empty=self.tools.EXCLUDE_EMPTY)
# score = 1 if result >= params.get(self.tools.TARGET) else 0
# scores.append(score)
#
# self.save_level2_and_level3(set_name, params.get(self.tools.KPI_NAME), score)
#
# set_score = (sum(scores) / float(len(scores))) * 100
# return set_score
def write_to_db_result(self, fk, score, level, result=None):
"""
This function 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.
"""
attributes = self.create_attributes_dict(fk,
score,
level,
result=result)
if level == self.LEVEL1:
table = KPS_RESULT
elif level == self.LEVEL2:
table = KPK_RESULT
elif level == self.LEVEL3:
table = KPI_RESULT
else:
return
query = insert(attributes, table)
self.kpi_results_queries.append(query)
def create_attributes_dict(self, fk, score, level, result=None):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
"""
score = round(score, 2)
if level == self.LEVEL1:
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['kpi_set_fk'] ==
fk]['kpi_set_name'].values[0]
score_type = '%' if kpi_set_name in self.tools.KPI_SETS_WITH_PERCENT_AS_SCORE else ''
attributes = pd.DataFrame(
[(kpi_set_name, self.session_uid, self.store_id,
self.visit_date.isoformat(), format(score,
'.2f'), score_type, fk)],
columns=[
'kps_name', 'session_uid', 'store_fk', 'visit_date',
'score_1', 'score_2', 'kpi_set_fk'
])
elif level == self.LEVEL2:
kpi_name = self.kpi_static_data[self.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.LEVEL3:
data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
== fk]
atomic_kpi_name = data['atomic_kpi_name'].values[0].replace(
"'", "\\'")
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['atomic_kpi_fk'] ==
fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame(
[(atomic_kpi_name, self.session_uid,
kpi_set_name, self.store_id, self.visit_date.isoformat(),
datetime.utcnow().isoformat(), score, kpi_fk, fk, result)],
columns=[
'display_text', 'session_uid', 'kps_name', 'store_fk',
'visit_date', 'calculation_time', 'score', 'kpi_fk',
'atomic_kpi_fk', 'result'
])
else:
attributes = pd.DataFrame()
return attributes.to_dict()
@log_runtime('Saving to DB')
def commit_results_data(self):
"""
This function writes all KPI results to the DB, and commits the changes.
"""
cur = self.rds_conn.db.cursor()
delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
self.session_uid)
for query in delete_queries:
cur.execute(query)
for query in self.kpi_results_queries:
cur.execute(query)
self.rds_conn.db.commit()
class DIAGEOIN_SANDToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.k_engine = BaseCalculationsScript(data_provider, output)
self.data_provider = data_provider
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.match_display_in_scene = self.get_match_display()
self.output = output
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.set_templates_data = {}
self.kpi_results_queries = []
self.store_channel = self.store_info['store_type'].values[0]
if self.store_channel:
self.store_channel = self.store_channel.upper()
self.segment = self.get_business_unit_name()
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.store_type = self.store_info['additional_attribute_1'].values[0]
self.kpi_static_data = self.get_kpi_static_data()
self.common = Common(self.data_provider)
self.commonV2 = CommonV2(self.data_provider)
self.tools = DIAGEOToolBox(
self.data_provider,
output,
match_display_in_scene=self.match_display_in_scene)
self.diageo_generator = DIAGEOGenerator(self.data_provider,
self.output, self.common)
def get_match_display(self):
"""
This function extracts the display matches data and saves it into one global data frame.
The data is taken from probedata.match_display_in_scene.
"""
query = DIAGEOQueries.get_match_display(self.session_uid)
match_display = pd.read_sql_query(query, self.rds_conn.db)
return match_display
def get_business_unit_name(self):
"""
This function extracts the static KPI data and saves it into one global data frame.
The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
"""
query = DIAGEOQueries.get_business_unit_name(self.store_id)
business_unit_name = pd.read_sql_query(query, self.rds_conn.db)
if business_unit_name['business_unit_name'].empty:
return ""
else:
return business_unit_name['business_unit_name'].values[0]
def get_kpi_static_data(self):
"""
This function extracts the static KPI data and saves it into one global data frame.
The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
"""
query = DIAGEOQueries.get_all_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
def main_calculation(self, set_names):
"""
This function calculates the KPI results.
"""
# # SOS Out Of The Box kpis
self.activate_ootb_kpis()
# Global assortment kpis
assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v3(
)
self.commonV2.save_json_to_new_tables(assortment_res_dict)
for set_name in set_names:
if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys(
):
try:
self.set_templates_data[
set_name] = self.tools.download_template(set_name)
except:
Log.warning("Couldn't find a template for set name: " +
str(set_name))
continue
if set_name in ('Secondary Displays', 'Secondary'):
# Global function
res_dict = self.diageo_generator.diageo_global_secondary_display_secondary_function(
)
# Saving to new tables
if res_dict:
self.commonV2.write_to_db_result(
fk=res_dict['fk'],
numerator_id=1,
denominator_id=self.store_id,
result=res_dict['result'])
# Saving to old tables
set_score = self.tools.calculate_assortment(
assortment_entity='scene_id',
location_type='Secondary Shelf')
self.save_level2_and_level3(set_name, set_name, set_score)
# elif set_name == 'Visible to Customer':
#
# # Global function
# sku_list = filter(None, self.scif[self.scif['product_type'] == 'SKU'].product_ean_code.tolist())
# res_dict = self.diageo_generator.diageo_global_visible_percentage(sku_list)
#
# if res_dict:
# # Saving to new tables
# parent_res = res_dict[-1]
# for r in res_dict:
# self.commonV2.write_to_db_result(**r)
#
# # Saving to old tables
# set_score = result = parent_res['result']
# self.save_level2_and_level3(set_name=set_name, kpi_name=set_name, score=result)
#
# # filters = {self.tools.VISIBILITY_PRODUCTS_FIELD: 'Y'}
# # set_score = self.tools.calculate_visible_percentage(visible_filters=filters)
# # self.save_level2_and_level3(set_name, set_name, set_score)
# elif set_name in ('MPA', 'New Products'):
# set_score = self.calculate_assortment_sets(set_name)
# elif set_name in ('POSM',):
# set_score = self.calculate_posm_sets(set_name)
else:
continue
if set_score == 0:
pass
elif set_score is False:
continue
set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name']
== set_name]['kpi_set_fk'].values[0]
self.write_to_db_result(set_fk, set_score, self.LEVEL1)
# commiting to new tables
self.commonV2.commit_results_data()
def activate_ootb_kpis(self):
# FACINGS_SOS_MANUFACTURER_IN_WHOLE_STORE - level 1
sos_store_fk = self.commonV2.get_kpi_fk_by_kpi_name('SOS OUT OF STORE')
sos_store = ManufacturerFacingsSOSInWholeStore(
data_provider=self.data_provider,
kpi_definition_fk=sos_store_fk).calculate()
# FACINGS_SOS_CATEGORY_IN_WHOLE_STORE - level 2
sos_cat_out_of_store_fk = self.commonV2.get_kpi_fk_by_kpi_name(
'SOS CATEGORY OUT OF STORE')
sos_cat_out_of_store = self.calculate_sos_of_cat_of_out_of_store_new(
sos_cat_out_of_store_fk)
# FACINGS_SOS_SUB_CATEGORY_OUT_OF_CATEGORY - level 3
sos_sub_cat_out_of_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name(
'SOS SUB CATEGORY OUT OF CATEGORY')
sos_sub_cat_out_of_cat = SubCategoryFacingsSOSPerCategory(
data_provider=self.data_provider,
kpi_definition_fk=sos_sub_cat_out_of_cat_fk).calculate()
# FACINGS_SOS_MANUFACTURER_OUT_OF_SUB_CATEGORY - level 4
sos_man_out_of_sub_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name(
'SOS MANUFACTURER OUT OF SUB CATEGORY')
sos_man_out_of_sub_cat = ManufacturerFacingsSOSPerSubCategoryInStore(
data_provider=self.data_provider,
kpi_definition_fk=sos_man_out_of_sub_cat_fk).calculate()
# FACINGS_SOS_BRAND_OUT_OF_SUB_CATEGORY_IN_WHOLE_STORE - level 5
sos_brand_out_of_sub_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name(
'SOS BRAND OUT OF SUB CATEGORY')
sos_brand_out_of_sub_cat = self.calculate_sos_of_brand_out_of_manufacturer_in_sub_cat(
sos_brand_out_of_sub_cat_fk)
# Savings results in Hierarchy
self.save_hierarchy(sos_store, sos_cat_out_of_store,
sos_sub_cat_out_of_cat, sos_man_out_of_sub_cat,
sos_brand_out_of_sub_cat)
def calculate_sos_of_brand_out_of_manufacturer_in_sub_cat(self, kpi_fk):
res_list = []
res_dict = dict()
# Get rid of Irrelevant and Empty types and keep only facings > 1
filtered_scif = self.scif[
~self.scif['product_type'].isin(['Irrelevant', 'Empty'])
& self.scif['facings_ign_stack'] > 0]
# Filter by each Sub Category and Manufacturer
sub_cat_fk_list = filtered_scif['sub_category_fk'].unique().tolist()
for sub_cat in sub_cat_fk_list:
filtered_scif_by_sub_cat = filtered_scif[
filtered_scif['sub_category_fk'] == sub_cat]
list_of_relevant_manufacturers = filtered_scif_by_sub_cat[
'manufacturer_fk'].unique().tolist()
for manu_fk in list_of_relevant_manufacturers:
filtered_scif_by_sub_cat_and_manufacturer = filtered_scif_by_sub_cat[
filtered_scif_by_sub_cat['manufacturer_fk'] == manu_fk]
denominator_result = filtered_scif_by_sub_cat_and_manufacturer[
'facings_ign_stack'].sum()
# Calculate results per Brand
list_of_relevant_brands = filtered_scif_by_sub_cat_and_manufacturer[
'brand_fk'].unique().tolist()
for brand_fk in list_of_relevant_brands:
filtered_scif_by_brand = filtered_scif_by_sub_cat_and_manufacturer[
filtered_scif_by_sub_cat_and_manufacturer['brand_fk']
== brand_fk]
facings_brand_results = filtered_scif_by_brand[
'facings_ign_stack'].sum()
result_for_brand = facings_brand_results / denominator_result
# Preparing the results' dictionary
res_dict['kpi_definition_fk'] = kpi_fk
res_dict['numerator_id'] = brand_fk
res_dict['numerator_result'] = facings_brand_results
res_dict['denominator_id'] = int(sub_cat)
res_dict['denominator_result'] = denominator_result
res_dict['identifier_result'] = (int(brand_fk),
int(sub_cat),
int(manu_fk))
res_dict['identifier_parent'] = int(manu_fk), (
int(sub_cat))
res_dict['result'] = result_for_brand
res_dict['score'] = result_for_brand
res_list.append(res_dict.copy())
return res_list
def calculate_sos_of_cat_of_out_of_store_new(self, kpi_fk):
res_list = []
res_dict = dict()
# Get rid of Irrelevant and Empty types and keep only facings ignore stacking > 1
filtered_scif = self.scif[
~self.scif['product_type'].isin(['Irrelevant', 'Empty'])
& self.scif['facings_ign_stack'] > 0]
denominator_result = filtered_scif['facings_ign_stack'].sum()
categories_fk_list = filtered_scif['category_fk'].unique().tolist()
# Calculate result per category (using facings_ign_stack!)
for category_fk in categories_fk_list:
filtered_scif_by_category = filtered_scif[
filtered_scif['category_fk'] == category_fk]
facings_category_result = filtered_scif_by_category[
'facings_ign_stack'].sum()
result_for_category = facings_category_result / denominator_result
# Preparing the results' dictionary
res_dict['kpi_definition_fk'] = kpi_fk
res_dict['numerator_id'] = category_fk
res_dict['numerator_result'] = facings_category_result
res_dict['denominator_id'] = self.store_id
res_dict['denominator_result'] = denominator_result
res_dict['result'] = result_for_category
res_dict['score'] = result_for_category
res_list.append(res_dict.copy())
return res_list
def save_hierarchy(self, level_1, level_2, level_3, level_4, level_5):
for i in level_1:
res = i.to_dict
kpi_identifier = "level_1"
self.commonV2.write_to_db_result(
fk=res['kpi_definition_fk'],
numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'],
result=res['result'],
score=res['result'],
identifier_result=kpi_identifier,
should_enter=False)
for res in level_2:
kpi_identifier = "level_2_" + str(int(res['numerator_id']))
parent_identifier = "level_1"
self.commonV2.write_to_db_result(
fk=res['kpi_definition_fk'],
numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'],
result=res['result'],
score=res['result'],
identifier_result=kpi_identifier,
identifier_parent=parent_identifier,
should_enter=True)
for i in level_3:
res = i.to_dict
kpi_identifier = str(int(res['numerator_id']))
parent_identifier = "level_2_" + str(int(res['denominator_id']))
self.commonV2.write_to_db_result(
fk=res['kpi_definition_fk'],
numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'],
result=res['result'],
score=res['result'],
identifier_result=kpi_identifier,
identifier_parent=parent_identifier,
should_enter=True)
for i in level_4:
res = i.to_dict
kpi_identifier = "level_4_" + str(
(int(res['numerator_id']), int(res['denominator_id'])))
parent_identifier = str(int(res['denominator_id']))
self.commonV2.write_to_db_result(
fk=res['kpi_definition_fk'],
numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'],
result=res['result'],
score=res['result'],
identifier_result=kpi_identifier,
identifier_parent=parent_identifier,
should_enter=True)
for res in level_5:
kpi_identifier = "level_5_" + str(res['identifier_result'])
parent_identifier = "level_4_" + str(res['identifier_parent'])
self.commonV2.write_to_db_result(
fk=res['kpi_definition_fk'],
numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'],
result=res['result'],
score=res['result'],
identifier_result=kpi_identifier,
identifier_parent=parent_identifier,
should_enter=True)
def save_level2_and_level3(self, set_name, kpi_name, score):
"""
Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
"""
kpi_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name)]
kpi_fk = kpi_data['kpi_fk'].values[0]
atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, self.LEVEL2)
self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)
def calculate_posm_sets(self, set_name):
"""
This function calculates every POSM-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.store_channel is None:
break
kpi_res = self.tools.calculate_posm(
display_name=params.get(self.tools.DISPLAY_NAME))
score = 1 if kpi_res > 0 else 0
if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
scores.append(score)
if score == 1 or params.get(
self.store_type) == self.tools.RELEVANT_FOR_STORE:
self.save_level2_and_level3(
set_name, params.get(self.tools.DISPLAY_NAME), score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def calculate_assortment_sets(self, set_name):
"""
This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
segment = '{};{}'.format(self.store_type, self.segment)
for params in self.set_templates_data[set_name]:
if params.get(segment,
'').capitalize() in (self.tools.RELEVANT_FOR_STORE,
self.tools.OR_OTHER_PRODUCTS):
object_type = self.tools.ENTITY_TYPE_CONVERTER.get(
params.get(self.tools.ENTITY_TYPE), 'product_ean_code')
objects = [
str(
params.get(
self.tools.PRODUCT_EAN_CODE,
params.get(self.tools.PRODUCT_EAN_CODE2, '')))
]
if params.get(self.store_type) == self.tools.OR_OTHER_PRODUCTS:
additional_objects = str(
params.get(self.tools.ADDITIONAL_SKUS)).split(',')
objects.extend(additional_objects)
filters = {object_type: objects}
result = self.tools.calculate_assortment(**filters)
score = 1 if result > 0 else 0
scores.append(score)
self.save_level2_and_level3(
set_name, params.get(self.tools.PRODUCT_NAME), score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def write_to_db_result(self, fk, score, level):
"""
This function 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.
"""
attributes = self.create_attributes_dict(fk, score, level)
if level == self.LEVEL1:
table = KPS_RESULT
elif level == self.LEVEL2:
table = KPK_RESULT
elif level == self.LEVEL3:
table = KPI_RESULT
else:
return
query = insert(attributes, table)
self.kpi_results_queries.append(query)
def create_attributes_dict(self, fk, score, level):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
"""
score = round(score, 2)
if level == self.LEVEL1:
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['kpi_set_fk'] ==
fk]['kpi_set_name'].values[0]
score_type = '%' if kpi_set_name in self.tools.KPI_SETS_WITH_PERCENT_AS_SCORE else ''
attributes = pd.DataFrame(
[(kpi_set_name, self.session_uid, self.store_id,
self.visit_date.isoformat(), format(score,
'.2f'), score_type, fk)],
columns=[
'kps_name', 'session_uid', 'store_fk', 'visit_date',
'score_1', 'score_2', 'kpi_set_fk'
])
elif level == self.LEVEL2:
kpi_name = self.kpi_static_data[self.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.LEVEL3:
data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
== fk]
atomic_kpi_name = data['atomic_kpi_name'].values[0].replace(
"'", "\\'")
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['atomic_kpi_fk'] ==
fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame([
(atomic_kpi_name, self.session_uid,
kpi_set_name, self.store_id, self.visit_date.isoformat(),
datetime.utcnow().isoformat(), score, kpi_fk, fk, None, None)
],
columns=[
'display_text', 'session_uid',
'kps_name', 'store_fk', 'visit_date',
'calculation_time', 'score',
'kpi_fk', 'atomic_kpi_fk',
'threshold', 'result'
])
else:
attributes = pd.DataFrame()
return attributes.to_dict()
@log_runtime('Saving to DB')
def commit_results_data(self):
"""
This function writes all KPI results to the DB, and commits the changes.
"""
cur = self.rds_conn.db.cursor()
delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
self.session_uid)
for query in delete_queries:
cur.execute(query)
for query in self.kpi_results_queries:
cur.execute(query)
self.rds_conn.db.commit()
class PENAFLORARToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
ACTIVATION_STANDARD = 'Activation Standard'
def __init__(self, data_provider, output):
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.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_type = self.store_info['additional_attribute_1'].values[0]
self.kpi_static_data = self.get_kpi_static_data()
self.set_templates_data = {}
self.match_display_in_scene = self.get_match_display()
self.kpi_results_queries = []
self.scores = {self.LEVEL1: {}, self.LEVEL2: {}, self.LEVEL3: {}}
self.output = output
self.common = Common(self.data_provider)
self.commonV2 = CommonV2(self.data_provider)
self.tools = DIAGEOToolBox(
self.data_provider,
output,
match_display_in_scene=self.match_display_in_scene
) # replace the old one
self.diageo_generator = DIAGEOGenerator(self.data_provider,
self.output, self.common)
def get_kpi_static_data(self):
"""
This function extracts the static KPI data and saves it into one global data frame.
The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
"""
query = DIAGEOQueries.get_all_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
def get_match_display(self):
"""
This function extracts the display matches data and saves it into one global data frame.
The data is taken from probedata.match_display_in_scene.
"""
query = DIAGEOQueries.get_match_display(self.session_uid)
match_display = pd.read_sql_query(query, self.rds_conn.db)
return match_display
def main_calculation(self, set_names):
"""
This function calculates the KPI results.
"""
# SOS Out Of The Box kpis
self.activate_ootb_kpis()
# sos by scene type
self.diageo_generator.sos_by_scene_type(self.commonV2)
# Global assortment kpis
assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2(
)
self.commonV2.save_json_to_new_tables(assortment_res_dict)
# Global assortment kpis - v3 for NEW MOBILE REPORTS use
assortment_res_dict_v3 = self.diageo_generator.diageo_global_assortment_function_v3(
)
self.commonV2.save_json_to_new_tables(assortment_res_dict_v3)
for set_name in set_names:
if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys(
):
try:
self.set_templates_data[
set_name] = self.tools.download_template(set_name)
except:
Log.warning("Couldn't find a template for set name: " +
str(set_name))
continue
# Global Visible to Customer / Visible to Consumer
if set_name in ('Visible to Customer', 'Visible to Consumer %',
'Visible to Consumer'):
# Global function
sku_list = filter(
None, self.scif[self.scif['product_type'] ==
'SKU'].product_ean_code.tolist())
res_dict = self.diageo_generator.diageo_global_visible_percentage(
sku_list)
if res_dict:
# Saving to new tables
self.commonV2.save_json_to_new_tables(res_dict)
# Saving to old tables
filters = {self.tools.VISIBILITY_PRODUCTS_FIELD: 'Y'}
set_score = self.tools.calculate_visible_percentage(
visible_filters=filters)
self.save_level2_and_level3(set_name, set_name, set_score)
elif set_name in ('Relative Position'):
# Global function
res_dict = self.diageo_generator.diageo_global_relative_position_function(
self.set_templates_data[set_name],
location_type='template_display_name')
if res_dict:
# Saving to new tables
self.commonV2.save_json_to_new_tables(res_dict)
set_score = self.calculate_relative_position_sets(set_name)
else:
continue
if set_score == 0:
pass
elif set_score is False:
continue
set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name']
== set_name]['kpi_set_fk'].values[0]
self.write_to_db_result(set_fk, set_score, self.LEVEL1)
# commiting to new tables
self.commonV2.commit_results_data()
def calculate_assortment_sets(self, set_name):
"""
This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
target = str(params.get(self.store_type, ''))
if target.isdigit() or target.capitalize() in (
self.tools.RELEVANT_FOR_STORE,
self.tools.OR_OTHER_PRODUCTS):
products = str(
params.get(self.tools.PRODUCT_EAN_CODE,
params.get(self.tools.PRODUCT_EAN_CODE2,
''))).replace(',', ' ').split()
target = 1 if not target.isdigit() else int(target)
kpi_name = params.get(self.tools.GROUP_NAME,
params.get(self.tools.PRODUCT_NAME))
kpi_static_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name)]
if len(products) > 1:
result = 0
for product in products:
product_score = self.tools.calculate_assortment(
product_ean_code=product)
result += product_score
atomic_fk = kpi_static_data[
kpi_static_data['description'] ==
product]['atomic_kpi_fk'].values[0]
self.write_to_db_result(atomic_fk,
product_score,
level=self.LEVEL3)
score = 1 if result >= target else 0
else:
result = self.tools.calculate_assortment(
product_ean_code=products)
atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
score = 1 if result >= target else 0
self.write_to_db_result(atomic_fk,
score,
level=self.LEVEL3)
scores.append(score)
kpi_fk = kpi_static_data['kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def calculate_relative_position_sets(self, set_name):
"""
This function calculates every relative-position-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.store_info.at[0, 'additional_attribute_2'] == params.get(
'additional_attribute_2', 'Empty'):
tested_filters = {
params.get(TESTED_TYPE): params.get(TESTED_VALUE)
}
anchor_filters = {
params.get(ANCHOR_TYPE): params.get(ANCHOR_VALUE)
}
direction_data = {
'top':
self._get_direction_for_relative_position(
params.get(self.tools.TOP_DISTANCE)),
'bottom':
self._get_direction_for_relative_position(
params.get(self.tools.BOTTOM_DISTANCE)),
'left':
self._get_direction_for_relative_position(
params.get(self.tools.LEFT_DISTANCE)),
'right':
self._get_direction_for_relative_position(
params.get(self.tools.RIGHT_DISTANCE))
}
general_filters = {
'template_display_name': params.get(self.tools.LOCATION)
}
result = self.tools.calculate_relative_position(
tested_filters, anchor_filters, direction_data,
**general_filters)
score = 1 if result else 0
scores.append(score)
self.save_level2_and_level3(set_name,
params.get(self.tools.KPI_NAME),
score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def _get_direction_for_relative_position(self, value):
"""
This function converts direction data from the template (as string) to a number.
"""
if value == self.tools.UNLIMITED_DISTANCE:
value = 1000
elif not value or not str(value).isdigit():
value = 0
else:
value = int(value)
return value
def calculate_activation_standard(self):
"""
This function calculates the Activation Standard KPI, and saves the result to the DB (for all 3 levels).
"""
final_score = 0
for params in self.tools.download_template(self.ACTIVATION_STANDARD):
set_name = params.get(self.tools.ACTIVATION_SET_NAME)
kpi_name = params.get(self.tools.ACTIVATION_KPI_NAME)
target = float(params.get(self.tools.ACTIVATION_TARGET))
target = target * 100 if target < 1 else target
score_type = params.get(self.tools.ACTIVATION_SCORE)
weight = float(params.get(self.tools.ACTIVATION_WEIGHT))
if kpi_name:
kpi_fk = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name
)]['kpi_fk'].values[0]
score = self.scores[self.LEVEL2].get(kpi_fk, 0)
else:
set_fk = self.kpi_static_data[
self.kpi_static_data['kpi_set_name'] ==
set_name]['kpi_set_fk'].values[0]
score = self.scores[self.LEVEL1].get(set_fk, 0)
if score >= target:
score = 100
else:
if score_type == 'PROPORTIONAL':
score = (score / float(target)) * 100
else:
score = 0
final_score += score * weight
self.save_level2_and_level3(self.ACTIVATION_STANDARD, set_name,
score)
set_fk = self.kpi_static_data[
self.kpi_static_data['kpi_set_name'] ==
self.ACTIVATION_STANDARD]['kpi_set_fk'].values[0]
self.write_to_db_result(set_fk, final_score, self.LEVEL1)
def save_level2_and_level3(self, set_name, kpi_name, score):
"""
Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
"""
kpi_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name)]
kpi_fk = kpi_data['kpi_fk'].values[0]
atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, self.LEVEL2)
self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)
def activate_ootb_kpis(self):
# FACINGS_SOS_MANUFACTURER_IN_WHOLE_STORE - level 1
sos_store_fk = self.commonV2.get_kpi_fk_by_kpi_name('SOS OUT OF STORE')
sos_store = ManufacturerFacingsSOSInWholeStore(
data_provider=self.data_provider,
kpi_definition_fk=sos_store_fk).calculate()
# FACINGS_SOS_CATEGORY_IN_WHOLE_STORE - level 2
sos_cat_out_of_store_fk = self.commonV2.get_kpi_fk_by_kpi_name(
'SOS CATEGORY OUT OF STORE')
sos_cat_out_of_store = self.calculate_sos_of_cat_of_out_of_store_new(
sos_cat_out_of_store_fk)
# FACINGS_SOS_SUB_CATEGORY_OUT_OF_CATEGORY - level 3
sos_sub_cat_out_of_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name(
'SOS SUB CATEGORY OUT OF CATEGORY')
sos_sub_cat_out_of_cat = SubCategoryFacingsSOSPerCategory(
data_provider=self.data_provider,
kpi_definition_fk=sos_sub_cat_out_of_cat_fk).calculate()
# FACINGS_SOS_MANUFACTURER_OUT_OF_SUB_CATEGORY - level 4
sos_man_out_of_sub_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name(
'SOS MANUFACTURER OUT OF SUB CATEGORY')
sos_man_out_of_sub_cat = ManufacturerFacingsSOSPerSubCategoryInStore(
data_provider=self.data_provider,
kpi_definition_fk=sos_man_out_of_sub_cat_fk).calculate()
# FACINGS_SOS_BRAND_OUT_OF_SUB_CATEGORY_IN_WHOLE_STORE - level 5
sos_brand_out_of_sub_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name(
'SOS BRAND OUT OF MANUFACTURER')
sos_brand_out_of_sub_cat = self.calculate_sos_of_brand_out_of_manufacturer_in_sub_cat(
sos_brand_out_of_sub_cat_fk)
# Savings results in Hierarchy
self.save_hierarchy(sos_store, sos_cat_out_of_store,
sos_sub_cat_out_of_cat, sos_man_out_of_sub_cat,
sos_brand_out_of_sub_cat)
def calculate_sos_of_brand_out_of_manufacturer_in_sub_cat(self, kpi_fk):
pass
res_list = []
res_dict = dict()
# Get rid of Irrelevant and Empty types and keep only facings > 1
filtered_scif = self.scif[
~self.scif['product_type'].isin(['Irrelevant', 'Empty'])
& self.scif['facings_ign_stack'] > 0]
# Filter by each Sub Category and Manufacturer
sub_cat_fk_list = filtered_scif['sub_category_fk'].unique().tolist()
for sub_cat in sub_cat_fk_list:
filtered_scif_by_sub_cat = filtered_scif[
filtered_scif['sub_category_fk'] == sub_cat]
list_of_relevant_manufacturers = filtered_scif_by_sub_cat[
'manufacturer_fk'].unique().tolist()
for manu_fk in list_of_relevant_manufacturers:
filtered_scif_by_sub_cat_and_manufacturer = filtered_scif_by_sub_cat[
filtered_scif_by_sub_cat['manufacturer_fk'] == manu_fk]
denominator_result = filtered_scif_by_sub_cat_and_manufacturer[
'facings_ign_stack'].sum()
# Calculate results per Brand
list_of_relevant_brands = filtered_scif_by_sub_cat_and_manufacturer[
'brand_fk'].unique().tolist()
for brand_fk in list_of_relevant_brands:
filtered_scif_by_brand = filtered_scif_by_sub_cat_and_manufacturer[
filtered_scif_by_sub_cat_and_manufacturer['brand_fk']
== brand_fk]
facings_brand_results = filtered_scif_by_brand[
'facings_ign_stack'].sum()
result_for_brand = facings_brand_results / denominator_result
# Preparing the results' dictionary
res_dict['kpi_definition_fk'] = kpi_fk
res_dict['numerator_id'] = brand_fk
res_dict['numerator_result'] = facings_brand_results
res_dict['denominator_id'] = int(sub_cat)
res_dict['denominator_result'] = denominator_result
res_dict['identifier_result'] = (int(brand_fk),
int(sub_cat),
int(manu_fk))
res_dict['identifier_parent'] = int(manu_fk), (
int(sub_cat))
res_dict['result'] = result_for_brand
res_dict['score'] = result_for_brand
res_list.append(res_dict.copy())
return res_list
def calculate_sos_of_cat_of_out_of_store_new(self, kpi_fk):
res_list = []
res_dict = dict()
# Get rid of Irrelevant and Empty types and keep only facings ignore stacking > 1
filtered_scif = self.scif[
~self.scif['product_type'].isin(['Irrelevant', 'Empty'])
& self.scif['facings_ign_stack'] > 0]
denominator_result = filtered_scif['facings_ign_stack'].sum()
categories_fk_list = filtered_scif['category_fk'].unique().tolist()
# Calculate result per category (using facings_ign_stack!)
for category_fk in categories_fk_list:
filtered_scif_by_category = filtered_scif[
filtered_scif['category_fk'] == category_fk]
facings_category_result = filtered_scif_by_category[
'facings_ign_stack'].sum()
result_for_category = facings_category_result / denominator_result
# Preparing the results' dictionary
res_dict['kpi_definition_fk'] = kpi_fk
res_dict['numerator_id'] = category_fk
res_dict['numerator_result'] = facings_category_result
res_dict['denominator_id'] = self.store_id
res_dict['denominator_result'] = denominator_result
res_dict['result'] = result_for_category
res_dict['score'] = result_for_category
res_list.append(res_dict.copy())
return res_list
def save_hierarchy(self, level_1, level_2, level_3, level_4, level_5):
for i in level_1:
res = i.to_dict
kpi_identifier = "level_1"
self.commonV2.write_to_db_result(
fk=res['kpi_definition_fk'],
numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'],
result=res['result'],
score=res['result'],
identifier_result=kpi_identifier,
should_enter=False)
for res in level_2:
kpi_identifier = "level_2_" + str(int(res['numerator_id']))
parent_identifier = "level_1"
self.commonV2.write_to_db_result(
fk=res['kpi_definition_fk'],
numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'],
result=res['result'],
score=res['result'],
identifier_result=kpi_identifier,
identifier_parent=parent_identifier,
should_enter=True)
for i in level_3:
res = i.to_dict
kpi_identifier = str(int(res['numerator_id']))
parent_identifier = "level_2_" + str(int(res['denominator_id']))
self.commonV2.write_to_db_result(
fk=res['kpi_definition_fk'],
numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'],
result=res['result'],
score=res['result'],
identifier_result=kpi_identifier,
identifier_parent=parent_identifier,
should_enter=True)
for i in level_4:
res = i.to_dict
kpi_identifier = "level_4_" + str(
(int(res['numerator_id']), int(res['denominator_id'])))
parent_identifier = str(int(res['denominator_id']))
self.commonV2.write_to_db_result(
fk=res['kpi_definition_fk'],
numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'],
result=res['result'],
score=res['result'],
identifier_result=kpi_identifier,
identifier_parent=parent_identifier,
should_enter=True)
for res in level_5:
kpi_identifier = "level_5_" + str(res['identifier_result'])
parent_identifier = "level_4_" + str(res['identifier_parent'])
self.commonV2.write_to_db_result(
fk=res['kpi_definition_fk'],
numerator_id=res['numerator_id'],
denominator_id=res['denominator_id'],
numerator_result=res['numerator_result'],
denominator_result=res['denominator_result'],
result=res['result'],
score=res['result'],
identifier_result=kpi_identifier,
identifier_parent=parent_identifier,
should_enter=True)
def write_to_db_result(self, fk, score, level):
"""
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.
"""
attributes = self.create_attributes_dict(fk, score, level)
if level == self.LEVEL1:
table = KPS_RESULT
elif level == self.LEVEL2:
table = KPK_RESULT
elif level == self.LEVEL3:
table = KPI_RESULT
else:
return
query = insert(attributes, table)
self.kpi_results_queries.append(query)
def create_attributes_dict(self, fk, score, level):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
"""
if level == self.LEVEL1:
kpi_set_name = self.kpi_static_data[
self.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.LEVEL2:
kpi_name = self.kpi_static_data[self.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.LEVEL3:
data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
== fk]
atomic_kpi_name = data['atomic_kpi_name'].values[0].replace(
"'", "''")
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['atomic_kpi_fk'] ==
fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame(
[(atomic_kpi_name, 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()
@log_runtime('Saving to DB')
def commit_results_data(self):
"""
This function writes all KPI results to the DB, and commits the changes.
"""
insert_queries = self.merge_insert_queries(self.kpi_results_queries)
rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
cur = rds_conn.db.cursor()
delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
self.session_uid)
for query in delete_queries:
cur.execute(query)
for query in insert_queries:
cur.execute(query)
rds_conn.db.commit()
@staticmethod
def merge_insert_queries(insert_queries):
query_groups = {}
for query in insert_queries:
static_data, inserted_data = query.split('VALUES ')
if static_data not in query_groups:
query_groups[static_data] = []
query_groups[static_data].append(inserted_data)
merged_queries = []
for group in query_groups:
merged_queries.append('{0} VALUES {1}'.format(
group, ',\n'.join(query_groups[group])))
return merged_queries
class DIAGEOGAToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.k_engine = BaseCalculationsScript(data_provider, output)
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.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.match_display_in_scene = self.get_match_display()
self.tools = DIAGEOToolBox(
self.data_provider,
output,
match_display_in_scene=self.match_display_in_scene)
self.common = Common(self.data_provider)
self.diageo_generator = DIAGEOGenerator(self.data_provider,
self.output,
self.common,
menu=True)
self.kpi_static_data = self.get_kpi_static_data()
self.kpi_results_queries = []
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_type = self.store_info['additional_attribute_1'].values[0]
self.commonV2 = CommonV2(self.data_provider)
self.set_templates_data = {}
self.kpi_static_data = self.get_kpi_static_data()
def get_kpi_static_data(self):
"""
This function extracts the static KPI data and saves it into one global data frame.
The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
"""
query = DIAGEOQueries.get_all_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
def main_calculation(self, set_names):
"""
This function calculates the KPI results.
"""
assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2(
)
self.commonV2.save_json_to_new_tables(assortment_res_dict)
# Global Menu kpis
menus_res_dict = self.diageo_generator.diageo_global_share_of_menu_cocktail_function(
cocktail_product_level=True)
self.commonV2.save_json_to_new_tables(menus_res_dict)
for set_name in set_names:
set_score = 0
if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys(
):
self.set_templates_data[
set_name] = self.tools.download_template(set_name)
# if set_name in ('MPA', 'New Products'):
# set_score = self.calculate_assortment_sets(set_name)
if set_name in ('Secondary Displays'):
res_json = self.diageo_generator.diageo_global_secondary_display_secondary_function(
)
if res_json:
# Saving to new tables
self.commonV2.write_to_db_result(
fk=res_json['fk'],
numerator_id=1,
denominator_id=self.store_id,
result=res_json['result'])
set_score = self.tools.calculate_number_of_scenes(
location_type='Secondary')
self.save_level2_and_level3(set_name, set_name, set_score)
if set_score == 0:
pass
elif set_score is False:
continue
set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name']
== set_name]['kpi_set_fk'].values[0]
self.write_to_db_result(set_fk, set_score, self.LEVEL1)
# committing to new tables
self.commonV2.commit_results_data()
def calculate_assortment_sets(self, set_name):
"""
This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
target = str(params.get(self.store_type, ''))
if target.isdigit() or target.capitalize() in (
self.tools.RELEVANT_FOR_STORE,
self.tools.OR_OTHER_PRODUCTS):
products = str(
params.get(self.tools.PRODUCT_EAN_CODE,
params.get(self.tools.PRODUCT_EAN_CODE2,
''))).replace(',', ' ').split()
target = 1 if not target.isdigit() else int(target)
kpi_name = params.get(self.tools.GROUP_NAME,
params.get(self.tools.PRODUCT_NAME))
kpi_static_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name)]
if len(products) > 1:
result = 0
for product in products:
product_score = self.tools.calculate_assortment(
product_ean_code=product)
result += product_score
atomic_fk = kpi_static_data[
kpi_static_data['description'] ==
product]['atomic_kpi_fk'].values[0]
self.write_to_db_result(atomic_fk,
product_score,
level=self.LEVEL3)
score = 1 if result >= target else 0
else:
result = self.tools.calculate_assortment(
product_ean_code=products)
atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
score = 1 if result >= target else 0
self.write_to_db_result(atomic_fk,
score,
level=self.LEVEL3)
scores.append(score)
kpi_fk = kpi_static_data['kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)
if not scores:
return 0
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def save_level2_and_level3(self, set_name, kpi_name, score):
"""
Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
"""
kpi_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name)]
kpi_fk = kpi_data['kpi_fk'].values[0]
atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, self.LEVEL2)
self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)
def write_to_db_result(self, fk, score, level):
"""
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.
"""
attributes = self.create_attributes_dict(fk, score, level)
if level == self.LEVEL1:
table = KPS_RESULT
elif level == self.LEVEL2:
table = KPK_RESULT
elif level == self.LEVEL3:
table = KPI_RESULT
else:
return
query = insert(attributes, table)
self.kpi_results_queries.append(query)
def create_attributes_dict(self, fk, score, level):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
"""
if level == self.LEVEL1:
kpi_set_name = self.kpi_static_data[
self.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.LEVEL2:
kpi_name = self.kpi_static_data[self.kpi_static_data['kpi_fk'] ==
fk]['kpi_name'].values[0]
attributes = pd.DataFrame(
[(self.session_uid, self.store_id, self.visit_date.isoformat(),
fk, kpi_name.replace("'", "''"), score)],
columns=[
'session_uid', 'store_fk', 'visit_date', 'kpi_fk',
'kpk_name', 'score'
])
elif level == self.LEVEL3:
data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
== fk]
atomic_kpi_name = data['atomic_kpi_name'].values[0]
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['atomic_kpi_fk'] ==
fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame(
[(atomic_kpi_name.replace("'", "''"), 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()
@log_runtime('Saving to DB')
def commit_results_data(self):
"""
This function writes all KPI results to the DB, and commits the changes.
"""
cur = self.rds_conn.db.cursor()
delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
self.session_uid)
for query in delete_queries:
cur.execute(query)
for query in self.kpi_results_queries:
cur.execute(query)
self.rds_conn.db.commit()
def get_match_display(self):
"""
This function extracts the display matches data and saves it into one global data frame.
The data is taken from probedata.match_display_in_scene.
"""
query = DIAGEOQueries.get_match_display(self.session_uid)
match_display = pd.read_sql_query(query, self.rds_conn.db)
return match_display
class DIAGEOCO_SANDToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
DIAGEO_MANUFACTURER = 'DIAGEO'
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.common_v2 = CommonV2(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_channel = self.store_info['store_type'].values[0]
if self.store_channel:
self.store_channel = self.store_channel.upper()
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.set_templates_data = {}
self.match_display_in_scene = self.get_match_display()
self.tools = DIAGEOToolBox(
self.data_provider,
output,
match_display_in_scene=self.match_display_in_scene)
self.global_gen = DIAGEOGenerator(self.data_provider,
self.output,
self.common,
menu=True)
def main_calculation(self):
"""
This function calculates the KPI results.
"""
set_names = [
'Brand Blocking', 'Secondary Displays', 'Brand Pouring',
'TOUCH POINT', 'Relative Position', 'Activation Standard'
]
total_scores_dict = []
self.tools.update_templates()
self.set_templates_data['TOUCH POINT'] = pd.read_excel(
Const.TEMPLATE_PATH,
Const.TOUCH_POINT_SHEET_NAME,
header=Const.TOUCH_POINT_HEADER_ROW)
# the manufacturer name for DIAGEO is 'Diageo' by default. We need to redefine this for DiageoCO
self.global_gen.tool_box.DIAGEO = 'DIAGEO'
assortment_res_dict = self.global_gen.diageo_global_assortment_function_v2(
)
self.common_v2.save_json_to_new_tables(assortment_res_dict)
menus_res_dict = self.global_gen.diageo_global_share_of_menu_cocktail_function(
)
self.common_v2.save_json_to_new_tables(menus_res_dict)
for set_name in set_names:
set_score = 0
if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys(
):
self.set_templates_data[
set_name] = self.tools.download_template(set_name)
if set_name == 'Secondary Displays':
result = self.global_gen.diageo_global_secondary_display_secondary_function(
)
total_scores_dict.append(result)
if result:
self.common_v2.write_to_db_result(**result)
set_score = self.tools.calculate_assortment(
assortment_entity='scene_id',
location_type='Secondary Shelf')
self.save_level2_and_level3(set_name, set_name, set_score)
elif set_name == 'Brand Pouring':
results_list = self.global_gen.diageo_global_brand_pouring_status_function(
self.set_templates_data[set_name])
total_scores_dict.append(results_list)
self.save_results_to_db(results_list)
set_score = self.calculate_brand_pouring_sets(set_name)
elif set_name == 'Brand Blocking':
results_list = self.global_gen.diageo_global_block_together(
set_name, self.set_templates_data[set_name])
total_scores_dict.append(results_list)
self.save_results_to_db(results_list)
set_score = self.calculate_block_together_sets(set_name)
elif set_name == 'Relative Position':
results_list = self.global_gen.diageo_global_relative_position_function(
self.set_templates_data[set_name])
total_scores_dict.append(results_list)
self.save_results_to_db(results_list)
set_score = self.calculate_relative_position_sets(set_name)
elif set_name == 'Activation Standard':
manufacturer_fk = self.all_products[
self.all_products['manufacturer_name'] ==
self.DIAGEO_MANUFACTURER]['manufacturer_fk'].values[0]
results_list = self.global_gen.diageo_global_activation_standard_function(
total_scores_dict, self.set_templates_data[set_name],
self.store_id, manufacturer_fk)
for result in results_list['old_tables_level2and3']:
self.save_level2_and_level3(result['kpi_set_name'],
result['kpi_name'],
result['score'])
self.write_to_db_result(
results_list['old_tables_level1']['fk'],
results_list['old_tables_level1']['score'],
results_list['old_tables_level1']['level'])
self.save_results_to_db(results_list['new_tables_result'])
elif set_name == 'TOUCH POINT':
store_attribute = 'additional_attribute_2'
template = self.set_templates_data[set_name].fillna(
method='ffill').set_index(
self.set_templates_data[set_name].keys()[0])
results_list = self.global_gen.diageo_global_touch_point_function(
template=template,
old_tables=True,
new_tables=False,
store_attribute=store_attribute)
total_scores_dict.append(results_list)
self.save_results_to_db(results_list)
else:
return
if set_score == 0:
pass
elif set_score is False:
return
# if set_name != 'TOUCH POINT': # we need to do this to prevent duplicate entries in report.kps_results
# set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name'] == set_name]['kpi_set_fk'].values[0]
# self.write_to_db_result(set_fk, set_score, self.LEVEL1)
return
def save_results_to_db(self, results_list):
if results_list:
for result in results_list:
if result is not None:
self.common_v2.write_to_db_result(**result)
def calculate_brand_pouring_sets(self, set_name):
"""
This function calculates every Brand-Pouring-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.tools.calculate_number_of_scenes(
**{self.tools.BRAND_POURING_FIELD: 'Y'}) > 0:
# 'Pouring' scenes
result = self.tools.calculate_brand_pouring_status(
params.get(self.tools.BRAND_NAME),
**{self.tools.BRAND_POURING_FIELD: 'Y'})
elif self.tools.calculate_number_of_scenes(
**{self.tools.BRAND_POURING_FIELD: 'back_bar'}) > 0:
# 'Back Bar' scenes
result = self.tools.calculate_brand_pouring_status(
params.get(self.tools.BRAND_NAME),
**{self.tools.BRAND_POURING_FIELD: 'back_bar'})
else:
result = 0
score = 1 if result else 0
scores.append(score)
self.save_level2_and_level3(set_name,
params.get(self.tools.KPI_NAME), score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def calculate_relative_position_sets(self, set_name):
"""
This function calculates every relative-position-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.store_channel == params.get(self.tools.CHANNEL,
'').upper():
tested_filters = {
'product_ean_code': params.get(self.tools.TESTED)
}
anchor_filters = {
'product_ean_code': params.get(self.tools.ANCHOR)
}
direction_data = {
'top':
self._get_direction_for_relative_position(
params.get(self.tools.TOP_DISTANCE)),
'bottom':
self._get_direction_for_relative_position(
params.get(self.tools.BOTTOM_DISTANCE)),
'left':
self._get_direction_for_relative_position(
params.get(self.tools.LEFT_DISTANCE)),
'right':
self._get_direction_for_relative_position(
params.get(self.tools.RIGHT_DISTANCE))
}
general_filters = {
'template_name': params.get(self.tools.LOCATION)
}
result = self.tools.calculate_relative_position(
tested_filters, anchor_filters, direction_data,
**general_filters)
score = 1 if result else 0
scores.append(score)
self.save_level2_and_level3(set_name,
params.get(self.tools.KPI_NAME),
score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def _get_direction_for_relative_position(self, value):
"""
This function converts direction data from the template (as string) to a number.
"""
if value == self.tools.UNLIMITED_DISTANCE:
value = 1000
elif not value or not str(value).isdigit():
value = 0
else:
value = int(value)
return value
def calculate_block_together_sets(self, set_name):
"""
This function calculates every block-together-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.store_channel == params.get(self.tools.CHANNEL,
'').upper():
filters = {'template_name': params.get(self.tools.LOCATION)}
if params.get(self.tools.SUB_BRAND_NAME):
filters['sub_brand_name'] = params.get(
self.tools.SUB_BRAND_NAME)
else:
filters['brand_name'] = params.get(self.tools.BRAND_NAME)
result = self.tools.calculate_block_together(**filters)
score = 1 if result else 0
scores.append(score)
self.save_level2_and_level3(set_name,
params.get(self.tools.KPI_NAME),
score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def get_match_display(self):
"""
This function extracts the display matches data and saves it into one global data frame.
The data is taken from probedata.match_display_in_scene.
"""
query = DIAGEOQueries.get_match_display(self.session_uid)
match_display = pd.read_sql_query(query, self.rds_conn.db)
return match_display
def save_level2_and_level3(self, set_name, kpi_name, score):
"""
Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
"""
kpi_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name)]
kpi_fk = kpi_data['kpi_fk'].values[0]
atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, self.LEVEL2)
self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)
def write_to_db_result(self, fk, score, level):
"""
This function 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.
"""
attributes = self.create_attributes_dict(fk, score, level)
if level == self.LEVEL1:
table = KPS_RESULT
elif level == self.LEVEL2:
table = KPK_RESULT
elif level == self.LEVEL3:
table = KPI_RESULT
else:
return
query = insert(attributes, table)
self.kpi_results_queries.append(query)
def create_attributes_dict(self, fk, score, level):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
"""
score = round(score, 2)
if level == self.LEVEL1:
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['kpi_set_fk'] ==
fk]['kpi_set_name'].values[0]
score_type = '%' if kpi_set_name in self.tools.KPI_SETS_WITH_PERCENT_AS_SCORE else ''
attributes = pd.DataFrame(
[(kpi_set_name, self.session_uid, self.store_id,
self.visit_date.isoformat(), format(score,
'.2f'), score_type, fk)],
columns=[
'kps_name', 'session_uid', 'store_fk', 'visit_date',
'score_1', 'score_2', 'kpi_set_fk'
])
elif level == self.LEVEL2:
kpi_name = self.kpi_static_data[self.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.LEVEL3:
data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
== fk]
atomic_kpi_name = data['atomic_kpi_name'].values[0].replace(
"'", "\\'")
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['atomic_kpi_fk'] ==
fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame([
(atomic_kpi_name, self.session_uid,
kpi_set_name, self.store_id, self.visit_date.isoformat(),
datetime.utcnow().isoformat(), score, kpi_fk, fk, None, None)
],
columns=[
'display_text', 'session_uid',
'kps_name', 'store_fk', 'visit_date',
'calculation_time', 'score',
'kpi_fk', 'atomic_kpi_fk',
'threshold', 'result'
])
else:
attributes = pd.DataFrame()
return attributes.to_dict()
def commit_results_data(self):
# self.common.commit_results_data_to_new_tables()
self.common_v2.commit_results_data() # new tables
# old tables
cur = self.rds_conn.db.cursor()
delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
self.session_uid)
for query in delete_queries:
cur.execute(query)
for query in self.kpi_results_queries:
cur.execute(query)
# needed to save Touch Point values
for query in self.common.kpi_results_queries:
cur.execute(query)
# this is only needed temporarily until the global assortment function is updated to use the new commonv2 object
insert_queries = self.common.merge_insert_queries(
self.common.kpi_results_new_tables_queries)
for query in insert_queries:
cur.execute(query)
self.rds_conn.db.commit()
class DIAGEOGRSANDToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.k_engine = BaseCalculationsScript(data_provider, 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.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_channel = self.store_info['store_type'].values[0]
if self.store_channel:
self.store_channel = self.store_channel.upper()
self.store_type = self.store_info['additional_attribute_1'].values[0]
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.match_display_in_scene = self.get_match_display()
self.set_templates_data = {}
self.kpi_static_data = self.get_kpi_static_data()
self.kpi_results_queries = []
self.output = output
self.common = Common(self.data_provider)
self.commonV2 = CommonV2(self.data_provider)
self.tools = DIAGEOToolBox(
self.data_provider,
output,
match_display_in_scene=self.match_display_in_scene)
self.diageo_generator = DIAGEOGenerator(self.data_provider,
self.output,
self.common,
menu=True)
def get_kpi_static_data(self):
"""
This function extracts the static KPI data and saves it into one global data frame.
The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
"""
query = DIAGEOQueries.get_all_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
def get_match_display(self):
"""
This function extracts the display matches data and saves it into one global data frame.
The data is taken from probedata.match_display_in_scene.
"""
query = DIAGEOQueries.get_match_display(self.session_uid)
match_display = pd.read_sql_query(query, self.rds_conn.db)
return match_display
def main_calculation(self, set_names):
"""
This function calculates the KPI results.
"""
# Global assortment kpis
assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2(
)
self.commonV2.save_json_to_new_tables(assortment_res_dict)
menus_res_dict = self.diageo_generator.diageo_global_share_of_menu_cocktail_function(
cocktail_product_level=True)
self.commonV2.save_json_to_new_tables(menus_res_dict)
for set_name in set_names:
if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys(
):
self.set_templates_data[
set_name] = self.tools.download_template(set_name)
set_score = 0
if set_name in ('Secondary Displays', 'Secondary'):
# Global function
res_json = self.diageo_generator.diageo_global_secondary_display_secondary_function(
)
if res_json:
# Saving to new tables
self.commonV2.write_to_db_result(
fk=res_json['fk'],
numerator_id=1,
denominator_id=self.store_id,
result=res_json['result'])
# Saving to old tables
set_score = self.tools.calculate_number_of_scenes(
location_type='Secondary')
self.save_level2_and_level3(set_name, set_name, set_score)
if set_score == 0:
pass
elif set_score is False:
continue
set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name']
== set_name]['kpi_set_fk'].values[0]
self.write_to_db_result(set_fk, set_score, self.LEVEL1)
# committing to new tables
self.commonV2.commit_results_data()
return
def save_level2_and_level3(self, set_name, kpi_name, score):
"""
Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
"""
kpi_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name)]
kpi_fk = kpi_data['kpi_fk'].values[0]
atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, self.LEVEL2)
self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)
def calculate_brand_pouring_sets(self, set_name):
"""
This function calculates every Brand-Pouring-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.tools.calculate_number_of_scenes(
**{self.tools.BRAND_POURING_FIELD: 'Y'}) > 0:
# 'Pouring' scenes
result = self.tools.calculate_brand_pouring_status(
params.get(self.tools.BRAND_NAME),
**{self.tools.BRAND_POURING_FIELD: 'Y'})
elif self.tools.calculate_number_of_scenes(
**{self.tools.BRAND_POURING_FIELD: 'back_bar'}) > 0:
# 'Back Bar' scenes
result = self.tools.calculate_brand_pouring_status(
params.get(self.tools.BRAND_NAME),
**{self.tools.BRAND_POURING_FIELD: 'back_bar'})
else:
result = 0
score = 1 if result else 0
scores.append(score)
self.save_level2_and_level3(set_name,
params.get(self.tools.KPI_NAME), score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def calculate_block_together_sets(self, set_name):
"""
This function calculates every block-together-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.store_channel == params.get(self.tools.CHANNEL,
'').upper():
filters = {'template_name': params.get(self.tools.LOCATION)}
if params.get(self.tools.SUB_BRAND_NAME):
filters['sub_brand_name'] = params.get(
self.tools.SUB_BRAND_NAME)
else:
filters['brand_name'] = params.get(self.tools.BRAND_NAME)
result = self.tools.calculate_block_together(**filters)
score = 1 if result else 0
scores.append(score)
self.save_level2_and_level3(set_name,
params.get(self.tools.KPI_NAME),
score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def calculate_relative_position_sets(self, set_name):
"""
This function calculates every relative-position-typed KPI from the relevant sets, and returns
the set's final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
if self.store_channel == params.get(self.tools.CHANNEL,
'').upper():
tested_filters = {
'product_ean_code': params.get(self.tools.TESTED)
}
anchor_filters = {
'product_ean_code': params.get(self.tools.ANCHOR)
}
direction_data = {
'top':
self._get_direction_for_relative_position(
params.get(self.tools.TOP_DISTANCE)),
'bottom':
self._get_direction_for_relative_position(
params.get(self.tools.BOTTOM_DISTANCE)),
'left':
self._get_direction_for_relative_position(
params.get(self.tools.LEFT_DISTANCE)),
'right':
self._get_direction_for_relative_position(
params.get(self.tools.RIGHT_DISTANCE))
}
general_filters = {
'template_name': params.get(self.tools.LOCATION)
}
result = self.tools.calculate_relative_position(
tested_filters, anchor_filters, direction_data,
**general_filters)
score = 1 if result else 0
scores.append(score)
self.save_level2_and_level3(set_name,
params.get(self.tools.KPI_NAME),
score)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def _get_direction_for_relative_position(self, value):
"""
This function converts direction data from the template (as string) to a number.
"""
if value == self.tools.UNLIMITED_DISTANCE:
value = 1000
elif not value or not str(value).isdigit():
value = 0
else:
value = int(value)
return value
def calculate_posm_sets(self, set_name):
"""
This function calculates every POSM-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
appeared_posm = False
for params in self.set_templates_data[set_name]:
if self.store_info['store_type'] is None:
break
kpi_res = self.tools.calculate_posm(
display_name=params.get(self.tools.DISPLAY_NAME))
score = 1 if kpi_res > 0 else 0
if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
scores.append(score)
if score == 1 or params.get(
self.store_type) == self.tools.RELEVANT_FOR_STORE:
self.save_level2_and_level3(
set_name, params.get(self.tools.DISPLAY_NAME), score)
appeared_posm = True
if scores:
set_score = (sum(scores) / float(len(scores))) * 100
elif appeared_posm:
set_score = 0
else:
return False
return set_score
def calculate_assortment_sets(self, set_name):
"""
This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
target = str(params.get(self.store_type, ''))
if target.isdigit() or target.capitalize() in (
self.tools.RELEVANT_FOR_STORE,
self.tools.OR_OTHER_PRODUCTS):
products = str(
params.get(self.tools.PRODUCT_EAN_CODE,
params.get(self.tools.PRODUCT_EAN_CODE2,
''))).replace(',', ' ').split()
target = 1 if not target.isdigit() else int(target)
kpi_name = params.get(self.tools.GROUP_NAME,
params.get(self.tools.PRODUCT_NAME))
kpi_static_data = self.kpi_static_data[
(self.kpi_static_data['kpi_set_name'] == set_name)
& (self.kpi_static_data['kpi_name'] == kpi_name)]
if len(products) > 1:
result = 0
for product in products:
product_score = self.tools.calculate_assortment(
product_ean_code=product)
result += product_score
try:
product_name = \
self.all_products[self.all_products['product_ean_code'] == product][
'product_name'].values[
0]
except Exception as e:
Log.warning(
'Product {} is not defined in the DB'.format(
product))
continue
try:
atomic_fk = \
kpi_static_data[kpi_static_data['atomic_kpi_name'] == product_name][
'atomic_kpi_fk'].values[
0]
except Exception as e:
Log.warning(
'Product {} is not defined in the DB'.format(
product_name))
continue
self.write_to_db_result(atomic_fk,
product_score,
level=self.LEVEL3)
score = 1 if result >= target else 0
else:
result = self.tools.calculate_assortment(
product_ean_code=products)
atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
score = 1 if result >= target else 0
self.write_to_db_result(atomic_fk,
score,
level=self.LEVEL3)
scores.append(score)
kpi_fk = kpi_static_data['kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
# def calculate_sos_sets(self, set_name):
# """
# This function calculates every SOS-typed KPI from the relevant sets, and returns the set final score.
# """
# scores = []
# for params in self.set_templates_data[set_name]:
# if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
# result = self.tools.calculate_share_of_shelf(manufacturer=self.tools.DIAGEO,
# include_empty=self.tools.EXCLUDE_EMPTY)
# score = 1 if result >= params.get(self.tools.TARGET) else 0
# scores.append(score)
#
# self.save_level2_and_level3(set_name, params.get(self.tools.KPI_NAME), score)
#
# set_score = (sum(scores) / float(len(scores))) * 100
# return set_score
def write_to_db_result(self, fk, score, level):
"""
This function 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.
"""
attributes = self.create_attributes_dict(fk, score, level)
if level == self.LEVEL1:
table = KPS_RESULT
elif level == self.LEVEL2:
table = KPK_RESULT
elif level == self.LEVEL3:
table = KPI_RESULT
else:
return
query = insert(attributes, table)
self.kpi_results_queries.append(query)
def create_attributes_dict(self, fk, score, level):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
"""
score = round(score, 2)
if level == self.LEVEL1:
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['kpi_set_fk'] ==
fk]['kpi_set_name'].values[0]
score_type = '%' if kpi_set_name in self.tools.KPI_SETS_WITH_PERCENT_AS_SCORE else ''
attributes = pd.DataFrame(
[(kpi_set_name, self.session_uid, self.store_id,
self.visit_date.isoformat(), format(score,
'.2f'), score_type, fk)],
columns=[
'kps_name', 'session_uid', 'store_fk', 'visit_date',
'score_1', 'score_2', 'kpi_set_fk'
])
elif level == self.LEVEL2:
kpi_name = self.kpi_static_data[self.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.LEVEL3:
data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
== fk]
atomic_kpi_name = data['atomic_kpi_name'].values[0].replace(
"'", "\\'")
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['atomic_kpi_fk'] ==
fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame([
(atomic_kpi_name, self.session_uid,
kpi_set_name, self.store_id, self.visit_date.isoformat(),
datetime.utcnow().isoformat(), score, kpi_fk, fk, None, None)
],
columns=[
'display_text', 'session_uid',
'kps_name', 'store_fk', 'visit_date',
'calculation_time', 'score',
'kpi_fk', 'atomic_kpi_fk',
'threshold', 'result'
])
else:
attributes = pd.DataFrame()
return attributes.to_dict()
@log_runtime('Saving to DB')
def commit_results_data(self):
"""
This function writes all KPI results to the DB, and commits the changes.
"""
cur = self.rds_conn.db.cursor()
delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
self.session_uid)
for query in delete_queries:
cur.execute(query)
for query in self.kpi_results_queries:
cur.execute(query)
self.rds_conn.db.commit()
class DIAGEOGTRToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
ACTIVATION_STANDARD = 'Activation Standard'
def __init__(self, data_provider, output):
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.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.set_templates_data = {}
self.store_info = self.data_provider[Data.STORE_INFO]
self.match_display_in_scene = self.get_match_display()
self.store_type = self.store_info['additional_attribute_1'].values[0]
self.kpi_static_data = self.get_kpi_static_data()
self.output = output
self.common = Common(self.data_provider)
self.commonV2 = CommonV2(self.data_provider)
self.tools = DIAGEOToolBox(self.data_provider, output,
match_display_in_scene=self.match_display_in_scene) # replace the old one
self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common)
self.kpi_results_queries = []
self.scores = {self.LEVEL1: {},
self.LEVEL2: {},
self.LEVEL3: {}}
def get_kpi_static_data(self):
"""
This function extracts the static KPI data and saves it into one global data frame.
The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
"""
query = DIAGEOQueries.get_all_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
def get_match_display(self):
"""
This function extracts the display matches data and saves it into one global data frame.
The data is taken from probedata.match_display_in_scene.
"""
query = DIAGEOQueries.get_match_display(self.session_uid)
match_display = pd.read_sql_query(query, self.rds_conn.db)
return match_display
def main_calculation(self, set_names):
"""
This function calculates the KPI results.
# """
assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2()
self.commonV2.save_json_to_new_tables(assortment_res_dict)
# if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys():
# self.set_templates_data[set_name] = self.tools.download_template(set_name)
#
# if set_name in ('Local MPA', 'MPA', 'New Products',):
# set_score = self.calculate_assortment_sets(set_name)
# else:
# return
# if set_score is False:
# return
# set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name'] == set_name]['kpi_set_fk'].values[0]
# self.write_to_db_result(set_fk, set_score, self.LEVEL1)
self.commonV2.commit_results_data()
return
def calculate_assortment_sets(self, set_name):
"""
This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
"""
scores = []
for params in self.set_templates_data[set_name]:
target = str(params.get(self.store_type, ''))
if target.isdigit() or target.capitalize() in (self.tools.RELEVANT_FOR_STORE, self.tools.OR_OTHER_PRODUCTS):
products = str(params.get(self.tools.PRODUCT_EAN_CODE,
params.get(self.tools.PRODUCT_EAN_CODE2, ''))).replace(',', ' ').split()
target = 1 if not target.isdigit() else int(target)
kpi_name = params.get(self.tools.GROUP_NAME, params.get(self.tools.PRODUCT_NAME))
kpi_static_data = self.kpi_static_data[(self.kpi_static_data['kpi_set_name'] == set_name) &
(self.kpi_static_data['kpi_name'] == kpi_name)]
if len(products) > 1:
result = 0
for product in products:
product_score = self.tools.calculate_assortment(product_ean_code=product)
result += product_score
try:
product_name = \
self.all_products[self.all_products['product_ean_code'] == product][
'product_name'].values[
0]
except Exception as e:
Log.warning('Product {} is not defined in the DB'.format(product))
continue
try:
atomic_fk = \
kpi_static_data[kpi_static_data['atomic_kpi_name'] == product_name][
'atomic_kpi_fk'].values[
0]
except Exception as e:
Log.warning('Product {} is not defined in the DB'.format(product_name))
continue
self.write_to_db_result(atomic_fk, product_score, level=self.LEVEL3)
score = 1 if result >= target else 0
else:
result = self.tools.calculate_assortment(product_ean_code=products)
atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
score = 1 if result >= target else 0
self.write_to_db_result(atomic_fk, score, level=self.LEVEL3)
scores.append(score)
kpi_fk = kpi_static_data['kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)
if not scores:
return False
set_score = (sum(scores) / float(len(scores))) * 100
return set_score
def calculate_activation_standard(self):
"""
This function calculates the Activation Standard KPI, and saves the result to the DB (for all 3 levels).
"""
final_score = 0
for params in self.tools.download_template(self.ACTIVATION_STANDARD):
set_name = params.get(self.tools.ACTIVATION_SET_NAME)
kpi_name = params.get(self.tools.ACTIVATION_KPI_NAME)
target = float(params.get(self.tools.ACTIVATION_TARGET))
target = target * 100 if target < 1 else target
score_type = params.get(self.tools.ACTIVATION_SCORE)
weight = float(params.get(self.tools.ACTIVATION_WEIGHT))
if kpi_name:
kpi_fk = self.kpi_static_data[(self.kpi_static_data['kpi_set_name'] == set_name) &
(self.kpi_static_data['kpi_name'] == kpi_name)]['kpi_fk'].values[0]
score = self.scores[self.LEVEL2].get(kpi_fk, 0)
else:
set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name'] == set_name]['kpi_set_fk'].values[0]
score = self.scores[self.LEVEL1].get(set_fk, 0)
if score >= target:
score = 100
else:
if score_type == 'PROPORTIONAL':
score = (score / float(target)) * 100
else:
score = 0
final_score += score * weight
self.save_level2_and_level3(self.ACTIVATION_STANDARD, set_name, score)
set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name'] ==
self.ACTIVATION_STANDARD]['kpi_set_fk'].values[0]
self.write_to_db_result(set_fk, final_score, self.LEVEL1)
def write_to_db_result(self, fk, score, level):
"""
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.
"""
attributes = self.create_attributes_dict(fk, score, level)
if level == self.LEVEL1:
table = KPS_RESULT
elif level == self.LEVEL2:
table = KPK_RESULT
elif level == self.LEVEL3:
table = KPI_RESULT
else:
return
query = insert(attributes, table)
self.kpi_results_queries.append(query)
def create_attributes_dict(self, fk, score, level):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
"""
if level == self.LEVEL1:
kpi_set_name = self.kpi_static_data[self.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.LEVEL2:
kpi_name = self.kpi_static_data[self.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.LEVEL3:
data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk'] == fk]
atomic_kpi_name = data['atomic_kpi_name'].values[0].replace("'", "\\'")
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk'] == fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame([(atomic_kpi_name, 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 save_level2_and_level3(self, set_name, kpi_name, score):
"""
Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
"""
kpi_data = self.kpi_static_data[(self.kpi_static_data['kpi_set_name'] == set_name) &
(self.kpi_static_data['kpi_name'] == kpi_name)]
kpi_fk = kpi_data['kpi_fk'].values[0]
atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
self.write_to_db_result(kpi_fk, score, self.LEVEL2)
self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)
@log_runtime('Saving to DB')
def commit_results_data(self):
"""
This function writes all KPI results to the DB, and commits the changes.
"""
insert_queries = self.merge_insert_queries(self.kpi_results_queries)
cur = self.rds_conn.db.cursor()
delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(self.session_uid)
for query in delete_queries:
cur.execute(query)
for query in insert_queries:
cur.execute(query)
self.rds_conn.db.commit()
@staticmethod
def merge_insert_queries(insert_queries):
query_groups = {}
for query in insert_queries:
static_data, inserted_data = query.split('VALUES ')
if static_data not in query_groups:
query_groups[static_data] = []
query_groups[static_data].append(inserted_data)
merged_queries = []
for group in query_groups:
merged_queries.append('{0} VALUES {1}'.format(group, ',\n'.join(query_groups[group])))
return merged_queries