def _assert_kpi_results_filled(self):
connector = PSProjectConnector(TestProjectsNames().TEST_PROJECT_1, DbUsers.Docker)
cursor = connector.db.cursor()
cursor.execute('''
SELECT * FROM report.kpi_results
''')
kpi_results = cursor.fetchall()
self.assertNotEquals(len(kpi_results), 0)
connector.disconnect_rds()
def _get_static_kpi(self):
connector = PSProjectConnector(self._project,self._dbUser)
try:
static = '''SELECT * FROM static.kpi_level_2;'''
return pd.read_sql_query(static, connector.db)
except Exception as e:
print e.message
finally:
connector.disconnect_rds()
def _assert_custom_scif_table_filled(self):
connector = PSProjectConnector(TestProjectsNames().TEST_PROJECT_1, DbUsers.Docker)
cursor = connector.db.cursor()
#cursor = connector.db.cursor(MySQLdb.cursors.DictCursor)
cursor.execute('''
SELECT * FROM pservice.custom_scene_item_facts
''')
kpi_results = cursor.fetchall()
self.assertNotEquals(len(kpi_results), 0)
connector.disconnect_rds()
def __init__(self, session_uid, store_id, visit_date, project_name):
rds_conn = PSProjectConnector(project_name, DbUsers.ReadOnly)
self._kpi_static_data = get_all_kpi_static_data(rds_conn.db)
rds_conn.disconnect_rds()
self._project_name = project_name
self._kpi_results_queries = []
self._kpk_results_queries = []
self._kps_results_queries = []
self._session_uid = session_uid
self._store_id = store_id
self._visit_date = visit_date
def _assert_scene_tables_kpi_results_filled(self, distinct_kpis_num=None):
connector = PSProjectConnector(TestProjectsNames().TEST_PROJECT_1, DbUsers.Docker)
cursor = connector.db.cursor()
cursor.execute('''
SELECT * FROM report.scene_kpi_results
''')
kpi_results = cursor.fetchall()
if distinct_kpis_num:
df = pd.DataFrame(kpi_results)
self.assertEquals(df['kpi_level_2_fk'].unique().__len__(), distinct_kpis_num)
else:
self.assertNotEquals(len(kpi_results), 0)
connector.disconnect_rds()
def _assert_kpi_results_filled(self):
connector = PSProjectConnector(TestProjectsNames().TEST_PROJECT_1,
DbUsers.Docker)
cursor = connector.db.cursor()
cursor.execute('''
SELECT * FROM report.kpi_level_2_results
''')
kpi_results = cursor.fetchall()
# silent test, diageomx are no longer using those templates and the test is failing
# self.assertNotEquals(len(kpi_results), 0)
connector.disconnect_rds()
def get_session_kpi_results(self):
query = '''
select kpir.*, kh.session_kpi_results_fk, kh.session_kpi_results_parent_fk
from report.kpi_level_2_results kpir
left join probedata.session ses on kpir.session_fk = ses.pk
left join report.kpi_hierarchy kh on kpir.pk = kh.session_kpi_results_parent_fk
-- left join report.kpi_level_2_results kpir2 on kh.session_kpi_results_parent_fk = kpir2.pk
where kh.session_kpi_results_fk is not null
and session_uid = '{}'
'''.format(self.session_uid)
con = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
data = pd.read_sql(query, con.db)
con.disconnect_rds()
return data
def _get_kpi_results(self):
connector = PSProjectConnector(self._project, self._dbUser)
try:
results = '''
SELECT
*
FROM
report.kpi_level_2_results,
probedata.session
WHERE
probedata.session.pk = report.kpi_level_2_results.session_fk
AND probedata.session.visit_date BETWEEN '{}' AND '{}';'''.format(self.start_date, self.end_date)
return pd.read_sql_query(results, connector.db)
except Exception as e:
print e.message
finally:
connector.disconnect_rds()
def _assert_new_tables_kpi_results_filled(self, distinct_kpis_num=None, list_of_kpi_names=None):
connector = PSProjectConnector(TestProjectsNames().TEST_PROJECT_1, DbUsers.Docker)
cursor = connector.db.cursor()
cursor.execute('''
SELECT kl2.pk, kl2.client_name, kl2r.kpi_level_2_fk, kl2r.result
FROM report.kpi_level_2_results kl2r left join static.kpi_level_2 kl2
on kpi_level_2_fk = kl2.pk
''')
kpi_results = cursor.fetchall()
df = pd.DataFrame(kpi_results)
if distinct_kpis_num:
self.assertEquals(df['kpi_level_2_fk'].unique().__len__(), distinct_kpis_num)
else:
self.assertNotEquals(len(kpi_results), 0)
if list_of_kpi_names:
exisitng_results = df['client_name'].unique()
result = all(elem in exisitng_results for elem in list_of_kpi_names)
self.assertTrue(result)
connector.disconnect_rds()
def commit_results_data(self):
"""
This function writes all KPI results to the DB, and commits the changes.
"""
rds_conn = PSProjectConnector(self._project_name,
DbUsers.CalculationEng)
cur = rds_conn.db.cursor()
delete_queries = MarsUsQueries.get_delete_session_results_query(
self._session_uid)
for query in delete_queries:
cur.execute(query)
queries = self.merge_insert_queries(self._kpi_results_queries)
for query in queries:
cur.execute(query)
queries = self.merge_insert_queries(self._kpk_results_queries)
for query in queries:
cur.execute(query)
for query in self._kps_results_queries:
cur.execute(query)
rds_conn.db.commit()
rds_conn.disconnect_rds()
def commit_results(self, queries):
self.rds_conn.disconnect_rds()
rds_conn = PSProjectConnector(PROJECT, DbUsers.CalculationEng)
cur = rds_conn.db.cursor()
for query in self.update_queries:
print query
try:
cur.execute(query)
except Exception as e:
Log.debug('Inserting to DB failed due to: {}'.format(e))
rds_conn.disconnect_rds()
rds_conn = PSProjectConnector(PROJECT, DbUsers.CalculationEng)
cur = rds_conn.db.cursor()
continue
rds_conn.db.commit()
rds_conn.disconnect_rds()
rds_conn = PSProjectConnector(PROJECT, DbUsers.CalculationEng)
cur = rds_conn.db.cursor()
for query in queries:
print query
try:
cur.execute(query)
except Exception as e:
Log.debug('Inserting to DB failed due to: {}'.format(e))
rds_conn.disconnect_rds()
rds_conn = PSProjectConnector(PROJECT, DbUsers.CalculationEng)
cur = rds_conn.db.cursor()
continue
rds_conn.db.commit()
class KCUS_KPIToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output, set_name=None):
self.k_engine = BaseCalculationsGroup(data_provider, output)
self.data_provider = data_provider
self.output = output
self.products = self.data_provider[Data.ALL_PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.project_name = data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.templates = self.data_provider[Data.ALL_TEMPLATES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.scenes_info = self.data_provider[Data.SCENES_INFO]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.session_info = SessionInfo(data_provider)
self.store_id = self.data_provider[Data.STORE_FK]
self.store_data = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.scif = self.scif.merge(self.data_provider[Data.STORE_INFO],
how='left',
left_on='store_id',
right_on='store_fk')
for sub in SUB:
if sub in (SUB.keys()):
self.scif = self.scif.rename(columns={sub: SUB.get(sub)})
for title in TITLE:
if title in (self.scif.columns.unique().tolist()):
self.scif = self.scif.rename(columns={title: TITLE.get(title)})
# self.generaltoolbox = KCUSGENERALToolBox(data_provider, output, geometric_kpi_flag=True)
# self.scif = self.scif.replace(' ', '', regex=True)
self.set_name = set_name
self.kpi_fetcher = KCUSFetcher(self.project_name, self.scif,
self.match_product_in_scene,
self.set_name, self.products,
self.session_uid)
self.all_template_data = parse_template(TEMPLATE_PATH, "Simple KPI's")
self.survey_response = self.data_provider[Data.SURVEY_RESPONSES]
self.sales_rep_fk = self.data_provider[
Data.SESSION_INFO]['s_sales_rep_fk'].iloc[0]
self.session_fk = self.data_provider[Data.SESSION_INFO]['pk'].iloc[0]
self.store_type = self.data_provider[
Data.STORE_INFO]['store_type'].iloc[0]
self.region = self.data_provider[
Data.STORE_INFO]['region_name'].iloc[0]
self.thresholds_and_results = {}
self.result_df = []
self.writing_to_db_time = datetime.timedelta(0)
self.kpi_results_queries = []
self.potential_products = {}
self.shelf_square_boundaries = {}
self.average_shelf_values = {}
self.kpi_static_data = self.get_kpi_static_data()
self.ignore_stacking = False
self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack'
self.max_shelf_of_bay = []
self.INCLUDE_FILTER = 1
self.MM_TO_FEET_CONVERSION = MM_TO_FEET_CONVERSION
self.EXCLUDE_EMPTY = True
def main_calculation(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
kpi_set_fk = 1
set_name = self.kpi_static_data.loc[
self.kpi_static_data['kpi_set_fk'] ==
kpi_set_fk]['kpi_set_name'].values[0]
template_data = self.all_template_data.loc[
self.all_template_data['KPI Level 1 Name'] == set_name]
try:
if set_name and not set(
template_data['Scene Types to Include'].values[0].encode(
).split(', ')) & set(
self.scif['template_name'].unique().tolist()):
Log.info('Category {} was not captured'.format(
template_data['category'].values[0]))
return
except Exception as e:
Log.info(
'KPI Set {} is not defined in the template'.format(set_name))
# for kpi_name in kpi_list:
for i, row in template_data.iterrows():
try:
kpi_name = row['KPI Level 2 Name']
if kpi_name in KPI_LEVEL_2_cat_space:
scene_type = [
s for s in row['Scene_Type'].encode().split(', ')
]
kpi_type = row['KPI_Type']
if row['Param1'] == 'Category' or 'sub_category':
category = row['Value1']
if kpi_type == 'category space':
self.calculate_category_space(
kpi_set_fk, kpi_name, scene_type, category)
except Exception as e:
Log.info('KPI {} calculation failed due to {}'.format(
kpi_name.encode('utf-8'), e))
continue
return
def calculate_category_space(self, kpi_set_fk, kpi_name, scene_types,
category):
template = self.all_template_data.loc[
(self.all_template_data['KPI Level 2 Name'] == kpi_name)
& (self.all_template_data['Value1'] == category)]
kpi_template = template.loc[template['KPI Level 2 Name'] == kpi_name]
if kpi_template.empty:
return None
kpi_template = kpi_template.iloc[0]
values_to_check = []
secondary_values_to_check = []
filters = {
'template_name': scene_types,
'category': kpi_template['Value1']
}
if kpi_template['Value1']:
values_to_check = self.all_products.loc[
self.all_products['category'] ==
kpi_template['Value1']]['category'].unique().tolist()
if kpi_template['Value2']:
if kpi_template['Value2'] in [
'Feminine Needs', 'Feminine Hygiene'
]:
sub_category_att = 'FEM NEEDS'
secondary_values_to_check = self.all_products.loc[
self.all_products[sub_category_att] ==
kpi_template['Value2']][sub_category_att].unique().tolist(
)
# elif kpi_template['Value2'] == 'Feminine Hygiene':
# sub_category_att = 'FEM HYGINE'
# secondary_values_to_check = self.all_products.loc[self.all_products['category'] == kpi_template['Value1']][
# sub_category_att].unique().tolist()
for primary_filter in values_to_check:
filters[kpi_template['Param1']] = primary_filter
if secondary_values_to_check:
for secondary_filter in secondary_values_to_check:
if secondary_filter == None:
continue
filters[sub_category_att] = secondary_filter
result = self.calculate_category_space_length(**filters)
score = result
self.write_to_db_result(kpi_set_fk,
score,
self.LEVEL3,
kpi_name=kpi_name,
score=score)
else:
result = self.calculate_category_space_length(**filters)
score = result
self.write_to_db_result(kpi_set_fk,
score,
self.LEVEL3,
kpi_name=kpi_name,
score=score)
def calculate_category_space_length(self, threshold=0.5, **filters):
"""
:param threshold: The ratio for a bay to be counted as part of a category.
:param filters: These are the parameters which the data frame is filtered by.
:return: The total shelf width (in mm) the relevant facings occupy.
"""
try:
#Remove this line of code when tagging is updated for femini hygiene.
if any(item in filters['template_name']
for item in ESTIMATE_SPACE_BY_BAYS_TEMPLATE_NAMES):
for k in filters.keys():
if k not in ['template_name', 'category']:
del filters[k]
filtered_scif = self.scif[self.get_filter_condition(
self.scif, **filters)]
if self.EXCLUDE_EMPTY == True:
filtered_scif = filtered_scif[
filtered_scif['product_type'] != 'Empty']
space_length = 0
bay_values = []
max_linear_of_bays = 0
product_fk_list = filtered_scif['product_fk'].unique().tolist()
# space_length_DEBUG = 0
for scene in filtered_scif['scene_fk'].unique().tolist():
scene_matches = self.match_product_in_scene[
self.match_product_in_scene['scene_fk'] == scene]
scene_filters = filters
scene_filters['scene_fk'] = scene
scene_filters['product_fk'] = product_fk_list
for bay in scene_matches['bay_number'].unique().tolist():
bay_total_linear = scene_matches.loc[
(scene_matches['bay_number'] == bay)
& (scene_matches['stacking_layer'] == 1) &
(scene_matches['status']
== 1)]['width_mm_advance'].sum()
max_linear_of_bays += bay_total_linear
scene_filters['bay_number'] = bay
tested_group_linear = scene_matches[
self.get_filter_condition(scene_matches,
**scene_filters)]
tested_group_linear_value = tested_group_linear[
'width_mm_advance'].loc[
tested_group_linear['stacking_layer'] == 1].sum()
if tested_group_linear_value:
bay_ratio = tested_group_linear_value / float(
bay_total_linear)
else:
bay_ratio = 0
if bay_ratio >= threshold:
# bay_num_of_shelves = len(scene_matches.loc[(scene_matches['bay_number'] == bay) &
# (scene_matches['stacking_layer'] == 1)][
# 'shelf_number'].unique().tolist())
# if kpi_name not in self.average_shelf_values.keys():
# self.average_shelf_values[kpi_name] = {'num_of_shelves': bay_num_of_shelves,
# 'num_of_bays': 1}
# else:
# self.average_shelf_values[kpi_name]['num_of_shelves'] += bay_num_of_shelves
# self.average_shelf_values[kpi_name]['num_of_bays'] += 1
# if bay_num_of_shelves:
# bay_final_linear_value = tested_group_linear_value / float(bay_num_of_shelves)
# else:
# bay_final_linear_value = 0
# bay_final_linear_value * self.MM_TO_FEET_CONVERSION
# space_length_DEBUG += bay_final_linear_value
bay_values.append(4)
else:
bay_values.append(0)
if filtered_scif['template_name'].iloc[
0] in ESTIMATE_SPACE_BY_BAYS_TEMPLATE_NAMES:
max_bays = len(
scene_matches['bay_number'].unique().tolist())
space_length = max_bays * 4
space_length = sum(bay_values)
except Exception as e:
Log.info('Linear Feet calculation failed due to {}'.format(e))
space_length = 0
return space_length
def get_category(self):
pass
def get_filter_condition(self, df, **filters):
"""
:param df: The data frame to be filters.
:param filters: These are the parameters which the data frame is filtered by.
Every parameter would be a tuple of the value and an include/exclude flag.
INPUT EXAMPLE (1): manufacturer_name = ('Diageo', DIAGEOAUPNGROGENERALToolBox.INCLUDE_FILTER)
INPUT EXAMPLE (2): manufacturer_name = 'Diageo'
:return: a filtered Scene Item Facts data frame.
"""
if not filters:
return df['pk'].apply(bool)
if self.facings_field in df.keys():
filter_condition = (df[self.facings_field] > 0)
else:
filter_condition = None
for field in filters.keys():
if field in df.keys():
if isinstance(filters[field], tuple):
value, exclude_or_include = filters[field]
else:
value, exclude_or_include = filters[
field], self.INCLUDE_FILTER
if not value:
continue
if not isinstance(value, list):
value = [value]
if exclude_or_include == self.INCLUDE_FILTER:
condition = (df[field].isin(value))
elif exclude_or_include == self.EXCLUDE_FILTER:
condition = (~df[field].isin(value))
elif exclude_or_include == self.CONTAIN_FILTER:
condition = (df[field].str.contains(value[0], regex=False))
for v in value[1:]:
condition |= df[field].str.contains(v, regex=False)
else:
continue
if filter_condition is None:
filter_condition = condition
else:
filter_condition &= condition
else:
Log.warning('field {} is not in the Data Frame'.format(field))
return filter_condition
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 = KCUSFetcher.get_all_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
def kpi_name_builder(self, kpi_name, **filters):
"""
This function builds kpi name according to naming convention
"""
for filter in filters.keys():
if filter == 'template_name':
continue
kpi_name = kpi_name.replace('{' + filter + '}',
str(filters[filter]))
kpi_name = kpi_name.replace("'", "\'")
return kpi_name
def write_to_db_result(self,
kpi_set_fk,
result,
level,
score=None,
threshold=None,
kpi_name=None,
kpi_fk=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(kpi_set_fk,
result=result,
level=level,
score=score,
threshold=threshold,
kpi_name=kpi_name,
kpi_fk=kpi_fk)
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,
kpi_set_fk,
result,
level,
score=None,
threshold=None,
kpi_name=None,
kpi_fk=None):
"""
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'] == kpi_set_fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame([(
kpi_set_name,
self.session_uid,
self.store_id,
self.visit_date.isoformat(),
result,
kpi_set_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'] ==
kpi_fk]['kpi_name'].values[0].replace("'", "\\'")
attributes = pd.DataFrame(
[(self.session_uid, self.store_id, self.visit_date.isoformat(),
kpi_fk, kpi_name, result)],
columns=[
'session_uid', 'store_fk', 'visit_date', 'kpi_fk',
'kpk_name', 'score'
])
elif level == self.LEVEL3:
kpi_set_name = \
self.kpi_static_data[self.kpi_static_data['kpi_set_fk'] == kpi_set_fk]['kpi_set_name'].values[0]
try:
atomic_kpi_fk = \
self.kpi_static_data[self.kpi_static_data['atomic_kpi_name'] == kpi_name]['atomic_kpi_fk'].values[0]
kpi_fk = self.kpi_static_data[
self.kpi_static_data['atomic_kpi_fk'] ==
atomic_kpi_fk]['kpi_fk'].values[0]
except Exception as e:
atomic_kpi_fk = None
kpi_fk = None
kpi_name = kpi_name.replace("'", "\\'")
attributes = pd.DataFrame(
[(kpi_name, self.session_uid, kpi_set_name, self.store_id,
self.visit_date.isoformat(),
datetime.datetime.utcnow().isoformat(), result, kpi_fk,
atomic_kpi_fk, threshold, score)],
columns=[
'display_text', 'session_uid', 'kps_name', 'store_fk',
'visit_date', 'calculation_time', 'result', 'kpi_fk',
'atomic_kpi_fk', 'threshold', 'score'
])
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 = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
cur = self.rds_conn.db.cursor()
delete_queries = KCUSFetcher.get_delete_session_results_query(
self.session_uid)
for query in delete_queries:
cur.execute(query)
self.rds_conn.db.commit()
self.rds_conn.disconnect_rds()
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
cur = self.rds_conn.db.cursor()
# for query in self.kpi_results_queries:
# try:
# cur.execute(query)
# except Exception as e:
# Log.info('Query {} failed due to {}'.format(query, e))
# continue
queries = self.merge_insert_queries(self.kpi_results_queries)
for query in queries:
cur.execute(query)
self.rds_conn.db.commit()
def merge_insert_queries(self, insert_queries):
# other_queries = []
query_groups = {}
for query in insert_queries:
if 'update' in query:
self.update_queries.append(query)
else:
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:
for group_index in xrange(0, len(query_groups[group]), 10**4):
merged_queries.append('{0} VALUES {1}'.format(
group, ',\n'.join(
query_groups[group][group_index:group_index + 10**4])))
return merged_queries
class SpecialProgramsToolBox:
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.tools = SpecialProgramsGENERALToolBox(self.data_provider,
self.output,
rds_conn=self.rds_conn)
self.kpi_static_data = self.get_kpi_static_data()
self.kpi_results_queries = []
self.pop_data = parse_template(TEMPLATE_PATH, 'POP')
self.pathway_data = parse_template(TEMPLATE_PATH, 'Pathway')
self.store_types = parse_template(TEMPLATE_PATH, 'store types')
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_type = self.store_info['store_type'].iloc[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 = SpecialProgramsQueries.get_all_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
def main_calculation(self):
"""
This function calculates the KPI results.
"""
scenes = self.scif['scene_fk'].unique().tolist()
if scenes:
for scene in scenes:
scene_data = self.scif.loc[self.scif['scene_fk'] == scene]
pop_result = self.calculate_pop(scene_data)
self.calculate_Pathway(pop_result, scene_data)
del scene_data
return
def calculate_pop(self, scene_data):
store_list = self.store_types['store types'].tolist()
for store_type in store_list:
if self.store_type in store_type:
pop_new_data = self.pop_data.loc[self.pop_data['store type'] ==
store_type]
for index, row in pop_new_data.iterrows():
template_group = [
str(g) for g in row['Template group'].split(',')
]
if scene_data['template_group'].values[
0] in template_group or template_group == ['']:
brands_list = row['brand_name'].split(',')
filters = {
'brand_name': brands_list,
'scene_fk':
scene_data['scene_fk'].unique().tolist()
}
result = self.tools.calculate_availability(**filters)
if result > 0:
self.write_to_db_result(name='{} POP'.format(
scene_data['scene_fk'].values[0]),
result=row['result'],
score=1,
level=self.LEVEL3)
return row['result']
del pop_new_data
break
self.write_to_db_result(name='{} POP'.format(
scene_data['scene_fk'].values[0]),
result='No POP',
score=0,
level=self.LEVEL3)
return
def calculate_Pathway(self, pop_result, scene_data):
result = 0
store_list = self.store_types['store types'].tolist()
for store_type in store_list:
if self.store_type in store_type:
try:
if pop_result:
pathways = self.pathway_data['result'].unique().tolist(
)
for pathway in pathways:
path_data = self.pathway_data.loc[
self.pathway_data['result'] == pathway]
if ',' in path_data['store type'].values[0]:
store_type_list = [
str(g) for g in path_data['store type'].
values[0].split(',')
]
else:
store_type_list = [
str(g)
for g in path_data['store type'].values[0]
]
if self.store_type in store_type_list:
if path_data['Template group'].values[0]:
template_group = [
str(g)
for g in path_data['Template group'].
values[0].split(',')
]
else:
template_group = []
if template_group:
if scene_data['template_group'].values[
0] in template_group:
result = self.check_path_way(
path_data, scene_data)
if result == 1:
return
else:
result = self.check_path_way(
path_data, scene_data)
if result == 1:
return
del path_data
del pathways
except Exception as e:
continue
if not result:
self.write_to_db_result(name='{} Pathway'.format(
scene_data['scene_fk'].values[0]),
result='No Pathway',
score=0,
level=self.LEVEL3)
return False
def check_path_way(self, path_data, scene_data):
filters = {'scene_fk': scene_data['scene_fk'].values[0]}
result = 0
filters[path_data['param1'].values[0]] = [
str(g) for g in path_data['value1'].values[0].split(",")
]
if not path_data['param2'].empty:
if path_data['param2'].values[0]:
filters[path_data['param2'].values[0]] = [
str(g) for g in path_data['value2'].values[0].split(",")
]
if path_data['Target'].values[0]:
target = float(path_data['Target'].values[0])
else:
target = 1
if path_data['calculation_type'].values[0] == 'availability':
result = self.tools.calculate_availability(**filters)
if result > 0:
result = 1
if result >= target:
result = 1
self.write_to_db_result(name='{} Pathway'.format(
scene_data['scene_fk'].values[0]),
result=path_data['result'].values[0],
score=1,
level=self.LEVEL3)
return result
elif path_data['calculation_type'].values[
0] == 'number of unique SKUs':
result = self.tools.calculate_assortment(**filters)
if result > 0:
result = 1
if result >= target:
score = result
self.write_to_db_result(name='{} Pathway'.format(
scene_data['scene_fk'].values[0]),
result=path_data['result'].values[0],
score=score,
level=self.LEVEL3)
return result
def write_to_db_result(self, score, level, result=None, name=None):
"""
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(score, level, result, name)
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, score, level, result=None, name=None):
"""
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'] ==
32]['kpi_set_name'].values[0]
attributes = pd.DataFrame(
[(kpi_set_name, self.session_uid, self.store_id,
self.visit_date.isoformat(), format(score, '.2f'), 32)],
columns=[
'kps_name', 'session_uid', 'store_fk', 'visit_date',
'score_1', 'kpi_set_fk'
])
elif level == self.LEVEL3:
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['kpi_set_fk'] ==
32]['kpi_set_name'].values[0]
attributes = pd.DataFrame(
[(name, self.session_uid, kpi_set_name, self.store_id,
self.visit_date.isoformat(), datetime.utcnow().isoformat(),
score, 246, result)],
columns=[
'display_text', 'session_uid', 'kps_name', 'store_fk',
'visit_date', 'calculation_time', 'score', 'kpi_fk',
'result'
])
else:
attributes = pd.DataFrame()
return attributes.to_dict()
@log_runtime('Saving to DB')
def commit_results_data(self, kpi_set_fk=None):
"""
This function writes all KPI results to the DB, and commits the changes.
"""
self.rds_conn.disconnect_rds()
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
atomic_pks = tuple()
if kpi_set_fk is not None:
query = SpecialProgramsQueries.get_atomic_pk_to_delete(
self.session_uid, kpi_set_fk)
kpi_atomic_data = pd.read_sql_query(query, self.rds_conn.db)
atomic_pks = tuple(kpi_atomic_data['pk'].tolist())
cur = self.rds_conn.db.cursor()
if atomic_pks:
delete_queries = SpecialProgramsQueries.get_delete_session_results_query(
self.session_uid, kpi_set_fk, atomic_pks)
for query in delete_queries:
cur.execute(query)
for query in self.kpi_results_queries:
cur.execute(query)
self.rds_conn.db.commit()
class MarsUsDogMainMealWet(object):
def __init__(self, data_provider, output):
self._data_provider = data_provider
self.project_name = self._data_provider.project_name
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.ReadOnly)
self._output = output
self._tools = MarsUsGENERALToolBox(self._data_provider,
self._output,
ignore_stacking=True)
self._template = ParseMarsUsTemplates()
self._writer = self._get_writer()
self.store_id = self._data_provider[Data.STORE_FK]
self._data_provider.channel = self.get_store_att15(self.store_id)
self._data_provider.retailer = self.get_store_retailer(self.store_id)
self._data_provider.probe_groups = self.get_probe_group(
self._data_provider.session_uid)
self.store_type = data_provider.store_type
self.rds_conn.disconnect_rds()
self._data_provider.trace_container = pd.DataFrame(columns=[
'kpi_display_text', 'scene_id', 'products&brands',
'allowed_products', 'kpi_pass'
])
def get_store_att15(self, store_fk):
query = MarsUsQueries.get_store_attribute(15, store_fk)
att15 = pd.read_sql_query(query, self.rds_conn.db)
return att15.values[0][0]
def get_store_att8(self, store_fk):
query = MarsUsQueries.get_store_attribute(8, store_fk)
att10 = pd.read_sql_query(query, self.rds_conn.db)
return att10.values[0][0]
def get_store_retailer(self, store_fk):
query = MarsUsQueries.get_store_retailer(store_fk)
att10 = pd.read_sql_query(query, self.rds_conn.db)
return att10.values[0][0]
def get_probe_group(self, session_uid):
query = MarsUsQueries.get_probe_group(session_uid)
probe_group = pd.read_sql_query(query, self.rds_conn.db)
return probe_group
@property
def _get_store_channel(self):
return self._data_provider.channel
@property
def _get_store_type(self):
return self._data_provider.store_type
@property
def _get_retailer_name(self):
return self._data_provider.retailer
def _get_writer(self):
return KpiResultsWriter(
session_uid=self._data_provider.session_uid,
project_name=self._data_provider.project_name,
store_id=self._data_provider[Data.STORE_FK],
visit_date=self._data_provider[Data.VISIT_DATE])
def calculate_scores(self):
"""
This function calculates the KPI results.
"""
if not self.is_relevant_retailer_channel():
Log.warning(
'retailer: {} and channel: {} are are not relevant'.format(
self._get_retailer_name, self._get_store_channel))
self._writer.commit_results_data()
return
if self._is_pet_food_category_excluded():
Log.warning(
'pet food category does not exists or it was excluded by decision unit'
)
self._writer.commit_results_data()
return
# template SPT
if self.is_relevant_retailer_channel_spt():
set_names = self._get_set_spt_names()
for set_name in set_names:
if self._is_session_irrelevant_for_set(set_name):
Log.info('Skipping set: {}'.format(set_name))
continue
Log.info('Starting set: {}'.format(set_name))
if not self.retailer_channel_has_sales_data(set_name):
Log.warning('no sales data for retailer: {},'
' channel: {}, set: {}'.format(
self._get_retailer_name,
self._get_store_channel, set_name))
template_data = self._template.parse_template(set_name, 0)
hierarchy = Definition(
template_data, self._get_store_channel,
self._get_retailer_name,
self._get_store_type).get_atomic_hierarchy_and_filters(
set_name)
preferred_range = template_data[
KPIConsts.PREFERRED_RANGE_SHEET]
Results(
self._tools, self._data_provider, self._writer,
preferred_range[preferred_range['Set name'] ==
set_name]).calculate(hierarchy)
# template BDB
if self.is_relevant_retailer_channel_bdb():
set_names = self._get_set_names()
for set_name in set_names:
if self._is_session_irrelevant_for_set(set_name):
Log.info('Skipping set: {}'.format(set_name))
continue
Log.info('Starting set: {}'.format(set_name))
if not self.retailer_channel_has_sales_data(set_name):
Log.warning('no sales data for retailer: {},'
' channel: {}, set: {}'.format(
self._get_retailer_name,
self._get_store_channel, set_name))
template_data = self._template.parse_template(set_name, 1)
hierarchy = Definition(
template_data, self._get_store_channel,
self._get_retailer_name,
self._get_store_type).get_atomic_hierarchy_and_filters(
set_name)
preferred_range = template_data[
KPIConsts.PREFERRED_RANGE_SHEET]
Results(
self._tools, self._data_provider, self._writer,
preferred_range[preferred_range['Set name'] ==
set_name]).calculate(hierarchy)
# self._data_provider.trace_container.to_csv('/home/Israel/Desktop/trace_block.csv')
self._writer.commit_results_data()
@staticmethod
def _get_set_names():
return [
DOG_MAIN_MEAL_DRY_2018, DOG_MAIN_MEAL_WET_2018, CAT_TREATS_2018,
CAT_MAIN_MEAL_DRY_2018, CAT_MAIN_MEAL_WET_2018, DOG_TREATS_2018
]
@staticmethod
def _get_set_spt_names():
return [
SPT_DOG_TREATS_Q1_2018, SPT_CAT_TREATS_Q1_2018,
SPT_CAT_MAIN_MEAL_Q1_2018, SPT_DOG_MAIN_MEAL_Q1_2018
]
def _is_pet_food_category_excluded(self):
category_fk = 13
decision_unit = self._data_provider._decision_unit_data_provider
session_category = decision_unit._session_categories_status
if session_category[session_category['category_fk'] ==
category_fk]['exclude_status_fk'].empty:
return True
category_excluded = \
session_category[session_category['category_fk'] == category_fk]['exclude_status_fk'].fillna(1).iloc[0] != 1
session_excluded = decision_unit._session_exclude_status[
'exclude_status_fk'].fillna(1).iloc[0] != 1
return category_excluded | session_excluded
def _is_session_irrelevant_for_set(self, set_name):
matches = self._tools.match_product_in_scene.copy()
relevant_filters = SET_PRE_CALC_CHECKS[set_name]
filtered_matches = matches[self._tools.get_filter_condition(
matches, **relevant_filters)]
if filtered_matches.empty:
return True
return False
@staticmethod
def _get_set_category_fk(set_name):
return SET_CATEGORIES[set_name]
def check_template(self):
set_names = self._get_set_names()
suspicious = {
'kpis with empty filters': [],
'filters with no data': {},
'kpi with no filters': []
}
for set_name in set_names:
template_data = self._template.parse_template(set_name)
hierarchy = Definition(
template_data,
self._get_store_channel).get_atomic_hierarchy_and_filters(
set_name)
product = self._data_provider.all_products
for kpi in hierarchy:
if kpi.has_key('filters'):
fil = kpi['filters']
if len(fil) == 0:
suspicious['kpis with empty filters'].append(
'set {} atomic {} filters are empty'.format(
set_name, kpi['atomic']))
for key, value in fil.iteritems():
if key == 'order by':
continue
key = self.split_filter(key)
key = self.rename_filter(key)
if isinstance(value, tuple):
value = value[0]
Log.info('set {} atomic {}, checking {} in {}'.format(
set_name, kpi['atomic'], value, key))
pp = product[product[key].isin(value)]
if len(pp) == 0:
suspicious['filters with no data'].setdefault(
key, set()).update(value)
else:
suspicious['kpi with no filters'].append(
'set {} kpi {} has no filters'.format(
set_name, kpi['atomic']))
return suspicious
@staticmethod
def split_filter(key):
key_list = key.split(';')
return key_list[0]
@staticmethod
def rename_filter(filter_):
if FILTER_NAMING_DICT.has_key(filter_):
return FILTER_NAMING_DICT[filter_]
else:
return filter_
def retailer_channel_has_sales_data(self, set_name):
sales = ParseMarsUsTemplates().get_mars_sales_data()
sales = sales[(sales['retailer'] == self._data_provider.retailer)
& (sales['channel'] == self._data_provider.channel) &
(sales['set'] == set_name.upper())]
if sales.empty:
return False
else:
return True
def retailer_channel_has_spt_sales_data(self, set_name):
sales = ParseMarsUsTemplates().get_mars_spt_sales_data()
sales = sales[(sales['retailer'] == self._data_provider.retailer)
& (sales['channel'] == self._data_provider.channel) &
(sales['set'] == set_name.upper())]
if sales.empty:
return False
else:
return True
def is_relevant_retailer_channel(self):
is_relevant_retailer = self._get_retailer_name in BDB_RETAILERS + SPT_RETAILERS
is_relevant_channel = self._get_store_channel in BDB_CHANNELS + SPT_CHANNELS
return is_relevant_retailer and is_relevant_channel
def is_relevant_retailer_channel_bdb(self):
is_relevant_retailer = self._get_retailer_name in BDB_RETAILERS
is_relevant_channel = self._get_store_channel in BDB_CHANNELS
return is_relevant_retailer and is_relevant_channel
def is_relevant_retailer_channel_spt(self):
is_relevant_retailer = self._get_retailer_name in SPT_RETAILERS
is_relevant_channel = self._get_store_channel in SPT_CHANNELS
return is_relevant_retailer and is_relevant_channel
class MONSTERCCUS_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.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.tools = MONSTERGENERALCCUS_SANDToolBox(self.data_provider, self.output, rds_conn=self.rds_conn)
self.kpi_static_data = self.get_kpi_static_data()
self.kpi_results_queries = []
self.ssd_score = 0 # Save the current score for Kpi1, fail Kp12 in case Kpi1 succeed.
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 = CCUS_SANDMONSTERQueries.get_all_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
def main_calculation(self):
"""
This function calculates the KPI results.
"""
score = 0
kpi1 = self.calculate_kpi1()
kpi2 = self.calculate_kpi2()
if kpi1 and kpi2:
score = 1
self.write_to_db_result(result=score,
score=score, level=self.LEVEL1)
return True
else:
self.write_to_db_result(result=score,
score=score, level=self.LEVEL1)
return False
def calculate_availability(self, filters1, filters2, check_sos=False, type=None):
score = 0
scif_scenes = self.scif.loc[self.scif['template_fk'].isin(TEMPLATE_COLD)]
scenes = scif_scenes['scene_id'].unique().tolist()
bays = self.match_product_in_scene['bay_number'].unique().tolist()
if scenes:
for scene in scenes:
for bay in bays:
filters1['scene_id'] = scene
filters1['bay_number'] = bay
filters2['scene_id'] = scene
filters2['bay_number'] = bay
if check_sos:
numerator_result = self.tools.calculate_availability(front_facing='Y', **filters2)
denominator_filters = {'scene_id': scene, 'bay_number': bay}
denominator_result = self.tools.calculate_availability(front_facing='Y', **denominator_filters)
result = 0 if denominator_result == 0 else (numerator_result / float(denominator_result))
if type == "calc1":
target = SOS_TARGET_KPI1
elif type == "calc2":
target = SOS_TARGET_KPI2
if result < target:
continue
result1 = self.tools.calculate_availability(**filters1)
result2 = self.tools.calculate_availability(**filters2)
score = 1 if (result1 and result2) >= 1 else 0
if score > 0:
return score
return score
def calculate_kpi1(self):
filters1 = {'product_ean_code': ['355', '356', '7084702650', '7084702652']}
filters2 = {'att2': ATT2, 'att4': SSD, 'manufacturer_fk': 1}
score = self.calculate_availability(filters1, filters2, check_sos=True,type="calc1")
if score > 0:
self.write_to_db_result(name='Is Mutant in the same door as Coke SSD', result=score,
score=1, level=self.LEVEL3)
self.ssd_score = 1
return True
else:
self.write_to_db_result(name='Is Mutant in the same door as Coke SSD?', result=score,
score=0, level=self.LEVEL3)
self.ssd_score = 0
return False
def calculate_kpi2(self):
result = 0
filters1 = {'product_ean_code': ['355', '356', '7084702650', '7084702652']}
filters2 = {'product_ean_code': MOUNTAIN_DEW}
score = self.calculate_availability(filters1, filters2, check_sos=True,type="calc2")
if score > 0 and self.ssd_score == 0:
self.write_to_db_result(name='Is Mutant in the same door as Mountain Dew?', result=score,
score=1, level=self.LEVEL3)
return True
else:
self.write_to_db_result(name='Is Mutant in the same door as Mountain Dew?', result=result,
score=0, level=self.LEVEL3)
return False
def write_to_db_result(self, score, level, result=None, name=None):
"""
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(score, level, result, name)
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, score, level, result=None, name=None):
"""
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'] == 27]['kpi_set_name'].values[0]
attributes = pd.DataFrame([(kpi_set_name, self.session_uid, self.store_id, self.visit_date.isoformat(),
format(score, '.2f'), 27)],
columns=['kps_name', 'session_uid', 'store_fk', 'visit_date', 'score_1',
'kpi_set_fk'])
elif level == self.LEVEL3:
kpi_set_name = self.kpi_static_data[self.kpi_static_data['kpi_set_fk'] == 27]['kpi_set_name'].values[0]
attributes = pd.DataFrame([(name, self.session_uid, kpi_set_name, self.store_id,
self.visit_date.isoformat(), datetime.utcnow().isoformat(),
score, 241, result)],
columns=['display_text', 'session_uid', 'kps_name', 'store_fk', 'visit_date',
'calculation_time', 'score', 'kpi_fk', 'result'])
else:
attributes = pd.DataFrame()
return attributes.to_dict()
@log_runtime('Saving to DB')
def commit_results_data(self, kpi_set_fk=None):
"""
This function writes all KPI results to the DB, and commits the changes.
"""
self.rds_conn.disconnect_rds()
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
atomic_pks = tuple()
if kpi_set_fk is not None:
query = CCUS_SANDMONSTERQueries.get_atomic_pk_to_delete(self.session_uid, kpi_set_fk)
kpi_atomic_data = pd.read_sql_query(query, self.rds_conn.db)
atomic_pks = tuple(kpi_atomic_data['pk'].tolist())
cur = self.rds_conn.db.cursor()
if atomic_pks:
delete_queries = CCUS_SANDMONSTERQueries.get_delete_session_results_query(self.session_uid, kpi_set_fk, atomic_pks)
for query in delete_queries:
cur.execute(query)
for query in self.kpi_results_queries:
cur.execute(query)
self.rds_conn.db.commit()
class CCRUProjectCalculations:
def __init__(self, data_provider, output):
self.data_provider = data_provider
self.output = output
self.project_name = self.data_provider.project_name
self.rds_conn = self.rds_connection()
self.tool_box = CCRUKPIToolBox(self.data_provider,
self.output,
update_kpi_set=True)
self.session_uid = self.tool_box.session_uid
self.visit_date = self.tool_box.visit_date
self.store_id = self.tool_box.store_id
self.test_store = self.tool_box.test_store
self.pos_kpi_set_name = self.tool_box.pos_kpi_set_name
self.session_info = SessionInfo(data_provider)
self.json = CCRUJsonGenerator()
self.results = {}
self.kpi_source_json = None
def main_function(self):
if str(self.visit_date) < self.tool_box.MIN_CALC_DATE:
Log.warning('Warning. Session cannot be calculated. '
'Visit date is less than {2} - {0}. '
'Store ID {1}.'.format(self.visit_date, self.store_id,
self.tool_box.MIN_CALC_DATE))
return
elif self.tool_box.visit_type in [self.tool_box.SEGMENTATION_VISIT]:
Log.warning(
'Warning. Session with Segmentation visit type has no KPI calculations.'
)
return
elif self.tool_box.visit_type in [self.tool_box.PROMO_VISIT]:
self.calculate_promo_compliance()
return
else:
if self.pos_kpi_set_name not in self.tool_box.ALLOWED_POS_SETS:
Log.warning(
'Warning. Session cannot be calculated. '
'POS KPI Set name in store attribute is invalid - {0}. '
'Store ID {1}.'.format(self.pos_kpi_set_name,
self.store_id))
else:
self.calculate_red_score()
if self.tool_box.visit_type in [self.tool_box.STANDARD_VISIT]:
if not self.tool_box.cooler_assortment.empty:
self.tool_box.set_kpi_set(CCRUConsts.COOLER_AUDIT_SCORE,
CCRUConsts.COOLER_AUDIT_SCORE)
self.calculate_cooler_audit()
Log.debug('KPI calculation stage: {}'.format('Committing results old'))
self.tool_box.commit_results_data_old()
Log.debug('KPI calculation stage: {}'.format('Committing results new'))
self.tool_box.commit_results_data_new()
Log.debug('KPI calculation is completed')
def calculate_red_score(self):
kpi_source_json = self.json.create_kpi_source('KPI_Source.xlsx',
self.pos_kpi_set_name)
kpi_source = {}
for row in kpi_source_json:
# Log.info('SOURCE: {}'.format(row.get(SOURCE)))
kpi_source[row.pop(SOURCE)] = row
if kpi_source:
pass
# elif self.test_store == "Y":
# Log.warning('Warning. Session cannot be calculated: '
# 'Store is a test store. '
# 'Store ID {1}.'
# .format(self.pos_kpi_set_name, self.store_id))
# return
else:
Log.warning(
'Warning. Session cannot be calculated. '
'POS KPI Set name in store attribute is invalid - {0}. '
'Store ID {1}.'.format(self.pos_kpi_set_name, self.store_id))
return
mr_targets = {}
for kpi_set, params in kpi_source.items():
if params.get(MR_TARGET) is not None:
mr_targets.update({kpi_set: params[MR_TARGET]})
mr_targets.update({params[SET]: params[MR_TARGET]})
self.tool_box.mr_targets = mr_targets
kpi_sets_types_to_calculate = [POS] # SPIRITS are excluded
for kpi_set_type in kpi_sets_types_to_calculate:
if kpi_source[kpi_set_type][SET]:
Log.debug('KPI calculation stage: {}'.format(
kpi_source[kpi_set_type][SET]))
self.tool_box.set_kpi_set(kpi_source[kpi_set_type][SET],
kpi_set_type)
self.json.project_kpi_dict['kpi_data'] = []
self.json.create_kpi_data_json(
'kpi_data',
kpi_source[kpi_set_type][FILE],
sheet_name=kpi_source[kpi_set_type][SHEET],
pos_kpi_set_name=self.pos_kpi_set_name)
self.calculate_red_score_kpi_set(
self.json.project_kpi_dict.get('kpi_data')[0],
kpi_source[kpi_set_type][SET],
mr_targets.get(kpi_set_type))
if kpi_source[GAPS][SET]:
Log.debug('KPI calculation stage: {}'.format(
kpi_source[GAPS][SET]))
self.tool_box.set_kpi_set(kpi_source[GAPS][SET], GAPS)
self.json.create_kpi_data_json('gaps',
kpi_source[GAPS][FILE],
sheet_name=kpi_source[GAPS][SHEET])
self.tool_box.calculate_gaps_old(
self.json.project_kpi_dict.get('gaps'))
self.tool_box.calculate_gaps_new(
self.json.project_kpi_dict.get('gaps'), kpi_source[GAPS][SET])
if kpi_source[BENCHMARK][SET]:
Log.debug('KPI calculation stage: {}'.format(
kpi_source[BENCHMARK][SET]))
self.tool_box.set_kpi_set(kpi_source[BENCHMARK][SET], BENCHMARK)
self.json.create_kpi_data_json(
'benchmark',
kpi_source[BENCHMARK][FILE],
sheet_name=kpi_source[BENCHMARK][SHEET])
self.tool_box.calculate_benchmark(
self.json.project_kpi_dict.get('benchmark'),
kpi_source[BENCHMARK][SET])
if kpi_source[CONTRACT][FILE]:
Log.debug('Importing Contract Execution template')
self.json.create_kpi_data_json(
'contract',
kpi_source[CONTRACT][FILE],
sheet_name=kpi_source[CONTRACT][SHEET])
if kpi_source[TOPSKU][SET]:
Log.debug('KPI calculation stage: {}'.format(
kpi_source[TOPSKU][SET]))
include_to_contract = True if self.json.project_kpi_dict.get(
'contract') else False
self.tool_box.set_kpi_set(kpi_source[TOPSKU][SET], TOPSKU)
self.tool_box.calculate_top_sku(include_to_contract,
kpi_source[TOPSKU][SET])
if self.json.project_kpi_dict.get('contract'):
if kpi_source[EQUIPMENT][SET]:
equipment_target_data = self.tool_box.get_equipment_target_data(
)
if equipment_target_data:
if kpi_source[TARGET][SET]:
Log.debug('KPI calculation stage: {}'.format(
kpi_source[TARGET][SET]))
self.tool_box.set_kpi_set(kpi_source[TARGET][SET],
TARGET)
self.json.project_kpi_dict['kpi_data'] = []
self.json.create_kpi_data_json(
'kpi_data',
kpi_source[TARGET][FILE],
sheet_name=kpi_source[TARGET][SHEET])
self.calculate_red_score_kpi_set(
self.json.project_kpi_dict.get('kpi_data')[0],
kpi_source[TARGET][SET])
Log.debug('KPI calculation stage: {}'.format(
kpi_source[EQUIPMENT][SET]))
self.tool_box.set_kpi_set(kpi_source[EQUIPMENT][SET],
EQUIPMENT)
self.tool_box.calculate_equipment_execution(
self.json.project_kpi_dict.get('contract'),
kpi_source[EQUIPMENT][SET],
kpi_source[KPI_CONVERSION][FILE],
equipment_target_data)
if kpi_source[CONTRACT][SET]:
Log.debug('KPI calculation stage: {}'.format(
kpi_source[CONTRACT][SET]))
self.tool_box.set_kpi_set(kpi_source[CONTRACT][SET], CONTRACT)
self.tool_box.calculate_contract_execution(
self.json.project_kpi_dict.get('contract'),
kpi_source[CONTRACT][SET])
# Log.debug('KPI calculation stage: {}'.format('Committing results old'))
# self.tool_box.commit_results_data_old()
#
# Log.debug('KPI calculation stage: {}'.format('Committing results new'))
# self.tool_box.commit_results_data_new()
def calculate_red_score_kpi_set(self,
kpi_data,
kpi_set_name,
set_target=None):
score = 0
score += self.tool_box.check_availability(kpi_data)
score += self.tool_box.check_facings_sos(kpi_data)
score += self.tool_box.check_share_of_cch(kpi_data)
score += self.tool_box.check_number_of_skus_per_door_range(kpi_data)
score += self.tool_box.check_number_of_doors(kpi_data)
score += self.tool_box.check_number_of_scenes(kpi_data)
score += self.tool_box.check_number_of_scenes_no_tagging(kpi_data)
score += self.tool_box.check_customer_cooler_doors(kpi_data)
score += self.tool_box.check_atomic_passed(kpi_data)
score += self.tool_box.check_atomic_passed_on_the_same_scene(kpi_data)
score += self.tool_box.check_sum_atomics(kpi_data)
score += self.tool_box.check_dummies(kpi_data)
score += self.tool_box.check_weighted_average(kpi_data)
score += self.tool_box.check_kpi_scores(kpi_data)
score = round(float(score), 3)
self.tool_box.create_kpi_groups(kpi_data.values()[0])
self.tool_box.write_to_kpi_results_old(
pd.DataFrame(
[(kpi_set_name, self.session_uid, self.store_id,
self.visit_date.isoformat(), format(score, '.3f'), None)],
columns=[
'kps_name', 'session_uid', 'store_fk', 'visit_date',
'score_1', 'kpi_set_fk'
]), 'level1')
set_target = set_target if set_target is not None else 100
self.tool_box.update_kpi_scores_and_results(
{
'KPI ID': 0,
'KPI name Eng': kpi_set_name,
'KPI name Rus': kpi_set_name,
'Parent': 'root'
}, {
'target': set_target,
'weight': None,
'result': score,
'score': score,
'weighted_score': score,
'level': 0
})
# INTEGRATION is excluded from calculation starting 2020
#
# if kpi_set_type == POS:
# Log.debug('KPI calculation stage: {}'.format(kpi_source[INTEGRATION][SET]))
# self.tool_box.prepare_hidden_set(kpi_data, kpi_source[INTEGRATION][SET])
def calculate_promo_compliance(self):
self.json.create_kpi_data_json('promo',
'KPI_Promo_Tracking.xlsx',
sheet_name='2019')
kpi_data = self.json.project_kpi_dict.get('promo')
Log.debug('KPI calculation stage: {}'.format('Promo Compliance'))
self.tool_box.calculate_promo_compliance_store(kpi_data)
Log.debug('KPI calculation stage: {}'.format('Committing results new'))
self.tool_box.common.commit_results_data()
def calculate_cooler_audit(self):
self.json.create_kpi_data_json('cooler_audit',
'Cooler_Quality.xlsx',
sheet_name='ALL')
kpi_data = self.json.project_kpi_dict.get('cooler_audit')[0]
group_model_map = pd.read_excel(os.path.join(self.json.base_path,
'Cooler_Quality.xlsx'),
sheet_name=GROUP_MODEL_MAP)
self.tool_box.calculate_cooler_kpis(kpi_data, group_model_map)
def rds_connection(self):
if not hasattr(self, '_rds_conn'):
self._rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
try:
pd.read_sql_query('select pk from probedata.session limit 1',
self._rds_conn.db)
except:
self._rds_conn.disconnect_rds()
self._rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
return self._rds_conn
class PNGAUToolBox(PNGAUConsts):
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.all_templates = self.data_provider[Data.ALL_TEMPLATES]
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_type = self.data_provider[
Data.STORE_INFO]['store_type'].values[0]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.brand_to_manufacturer = {
x['brand_name']: x['manufacturer_name']
for i, x in self.scif[['brand_name', 'manufacturer_name'
]].drop_duplicates().iterrows()
}
self.match_display_in_scene = self.get_match_display()
self.store_retailer = self.get_retailer()
self.tools = PNGAUGENERALToolBox(self.data_provider,
self.output,
rds_conn=self.rds_conn)
if self.visit_date <= datetime(2017, 12, 31).date():
self.TEMPLATE_PATH = os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data',
'Template Dec.xlsx')
else:
self.TEMPLATE_PATH = os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data',
'Template.xlsx')
self.template_data = parse_template(self.TEMPLATE_PATH, 'KPIs')
self.scoring_data = parse_template(self.TEMPLATE_PATH, 'Scores')
self.category_scene_types, self.template_groups = self.get_category_scene_types(
)
self._custom_templates = {}
self.scenes_types_for_categories = {}
self.kpi_static_data = self.get_kpi_static_data()
self.kpi_results_queries = []
self.kpi_results = {}
self.atomic_results = {}
self.categories = self.all_products['category_fk'].unique().tolist()
@property
def rds_conn(self):
if not hasattr(self, '_rds_conn'):
self._rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
try:
pd.read_sql_query('select pk from probedata.session limit 1',
self._rds_conn.db)
except:
Log.info('Disconnecting and connecting to DB')
self._rds_conn.disconnect_rds()
self._rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
return self._rds_conn
@property
def empty_spaces(self):
if not hasattr(self, '_empty_spaces'):
matches = self.match_product_in_scene.merge(self.products,
on='product_fk',
how='left',
suffixes=['', '_1'])
self._empty_spaces = PNGAUEmptySpaces(matches)
self._empty_spaces.calculate_empty_spaces()
return self._empty_spaces.empty_spaces
def get_template(self, name):
"""
This function extracts the template's data, given a sheet name.
"""
if name not in self._custom_templates.keys():
self._custom_templates[name] = parse_template(
self.TEMPLATE_PATH, name)
return self._custom_templates[name]
def get_category_scene_types(self):
"""
This function converts the category-scene template into a dictionary.
"""
scene_types = parse_template(self.TEMPLATE_PATH, 'Category-Scene_Type')
category_scene_types = {self.PRIMARY_SHELF: []}
for category in scene_types[self.CATEGORY].unique():
data = scene_types[scene_types[self.CATEGORY] == category]
types = data[self.SCENE_TYPES].unique().tolist()
category_scene_types[category] = types
if category != self.DISPLAY:
category_scene_types[self.PRIMARY_SHELF].extend(types)
template_groups = {}
for template_group in scene_types[self.TEMPLATE_GROUP].unique():
if template_group:
template_groups[template_group] = scene_types[scene_types[
self.TEMPLATE_GROUP] == template_group][
self.SCENE_TYPES].unique().tolist()
return category_scene_types, template_groups
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 = PNGAUQueries.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 = PNGAUQueries.get_match_display(self.session_uid)
match_display = pd.read_sql_query(query, self.rds_conn.db)
match_display = match_display.merge(
self.scene_info[['scene_fk', 'template_fk']],
on='scene_fk',
how='left')
match_display = match_display.merge(self.all_templates,
on='template_fk',
how='left',
suffixes=['', '_y'])
return match_display
def get_retailer(self):
query = PNGAUQueries.get_retailer(self.store_id)
data = pd.read_sql_query(query, self.rds_conn.db)
retailer = '' if data.empty else data['retailer'].values[0]
if 'woolworth' in retailer.lower():
retailer = 'Woolworths'
return retailer
@log_runtime('Main Calculation')
def main_calculation(self):
"""
This function calculates the KPI results.
"""
self.calculation_per_product()
self.calculation_per_brand()
self.calculation_per_manufacturer()
self.calculation_per_category()
set_names = self.template_data[self.SET_NAME].tolist()
scoring_sets = self.scoring_data[self.SET_NAME].tolist()
for set_name in set(set_names):
self.write_to_db_result(level=self.LEVEL1, kpi_set_name=set_name)
for set_name in set(scoring_sets):
set_weight = float(self.scoring_data[self.scoring_data[
self.SET_NAME] == set_name][self.WEIGHT].values[0])
set_weight = int(set_weight * 100)
self.write_to_db_result(score=set_weight,
level=self.LEVEL1,
kpi_set_name=set_name)
def get_target(self, kpi_name, category):
"""
This function extracts a given KPI's target from the 'Targets' template.
"""
target = None
targets_data = self.get_template('Targets')
targets_data = targets_data[(targets_data[self.KPI_NAME] == kpi_name) &
(targets_data[self.CATEGORY] == category)]
if not targets_data.empty:
targets_data = targets_data.iloc[0]
if self.store_retailer in targets_data.keys():
target = targets_data[self.store_retailer]
if target:
try:
if isinstance(target,
(str, unicode)) and '%' in target:
target = float(target.replace('%', '')) / 100
target = int(float(target) * 10000)
except ValueError:
pass
return target
def calculate_distribution(self, category, params):
"""
This function calculates 'Distribution' typed KPIs (and its sub-KPIs - the KPIs which inherit its results).
"""
set_name = params[self.SET_NAME]
scenes_filters = self.get_scenes_filters(params, category)
if params[self.CUSTOM_SHEET]:
custom_template = self.get_template(params[self.CUSTOM_SHEET])
if self.store_type not in custom_template.columns:
return
distribution_products = custom_template[
(custom_template[self.CATEGORY] == category)
& (custom_template[self.store_type].apply(bool))]
distribution_products = distribution_products[
self.PRODUCT_EAN].unique().tolist()
else:
distribution_products = self.scif[
(self.scif['category'] == category)
& (self.scif['in_assort_sc'] == 1)]['product_ean_code'].unique(
).tolist()
number_of_distributed_products = 0
for product_ean_code in distribution_products:
product_brand = self.all_products[
self.all_products['product_ean_code'] == product_ean_code]
if product_brand.empty:
# Log.warning('Product EAN {} does not match any active product'.format(product_ean_code))
continue
product_brand = product_brand['brand_name'].values[0]
kpi_name = params[self.KPI_NAME].format(category=category,
brand=product_brand,
ean=product_ean_code)
result = int(
self.tools.calculate_assortment(
product_ean_code=product_ean_code, **scenes_filters))
self.save_result(set_name, kpi_name, result=result)
number_of_distributed_products += result
self.calculation_by_reference(category, set_name,
number_of_distributed_products,
len(distribution_products))
def calculation_by_reference(self, category, reference_name, numerator,
denominator):
"""
Given a parent-KPI's results, this function calculates a reference KPI.
"""
referenced_kpis = self.template_data[self.template_data[
self.REFERENCE_KPI] == reference_name]
for x, params in referenced_kpis.iterrows():
score = 0
aggregation_type = params[self.AGGREGATED_SCORE]
if aggregation_type.startswith('Percentage'):
if aggregation_type.endswith('Passed'):
numerator = numerator
else:
numerator = denominator - numerator
result = 0 if not denominator else round(
(numerator / float(denominator)) * 100, 2)
elif aggregation_type.startswith('Count'):
if aggregation_type.endswith('All'):
result = denominator
elif aggregation_type.endswith('Passed'):
result = numerator
else:
result = denominator - numerator
score = 0 if not denominator else (result /
float(denominator)) * 10000
else:
continue
kpi_name = params[self.KPI_NAME].format(category=category)
self.save_result(params[self.SET_NAME],
kpi_name,
score=score,
result=result,
threshold=denominator)
def get_scenes_filters(self, params, category):
"""
This function extracts the scene-type data (==filter) of a given KPI.
"""
filters = {}
if params[self.SCENE_TYPES] and params[self.SCENE_TYPES] != 'ALL':
scene_types = params[self.SCENE_TYPES].split(self.SEPARATOR)
template_names = []
for scene_type in scene_types:
if scene_type == self.CATEGORY_PRIMARY_SHELF:
# if category in self.category_scene_types.keys():
# template_names.extend(self.category_scene_types[category])
template_names.extend(
self.category_scene_types[self.PRIMARY_SHELF])
elif scene_type == self.DISPLAY:
template_names.extend(
self.category_scene_types[self.DISPLAY])
else:
template_names.append(scene_type)
if template_names:
filters['template_name'] = template_names
return filters
def calculation_per_product(self):
"""
This function calculates all KPIs which are saved per product_ean_code.
"""
kpis = self.template_data[self.template_data[self.SUB_CALCULATION] ==
'Product']
for x, entity in self.scif.drop_duplicates(
subset=['product_ean_code']).iterrows():
for y, params in kpis.iterrows():
score = result = threshold = None
set_name = params[self.SET_NAME]
kpi_name = params[self.KPI_NAME].format(
category=entity['category'],
brand=entity['brand_name'],
ean=entity['product_ean_code'])
kpi_type = params[self.KPI_TYPE]
scene_filters = self.get_scenes_filters(
params, entity['category'])
if kpi_type == self.FACING_COUNT:
result = int(
self.tools.calculate_availability(
product_ean_code=entity['product_ean_code'],
**scene_filters))
else:
continue
self.save_result(set_name,
kpi_name,
score=score,
result=result,
threshold=threshold)
def calculation_per_brand(self):
"""
This function calculates all KPIs which are saved per brand_name.
"""
kpis = self.template_data[self.template_data[self.SUB_CALCULATION] ==
'Brand']
for x, entity in self.scif.drop_duplicates(
subset=['brand_fk']).iterrows():
for y, params in kpis.iterrows():
score = result = threshold = None
set_name = params[self.SET_NAME]
kpi_name = None
kpi_type = params[self.KPI_TYPE]
scene_filters = self.get_scenes_filters(
params, entity['category'])
if kpi_type == self.FACING_COUNT:
result = int(
self.tools.calculate_availability(
brand_fk=entity['brand_fk'], **scene_filters))
threshold = int(
self.tools.calculate_availability(
category_fk=entity['category_fk'],
**scene_filters))
if set_name == 'Size of Display Raw Data':
score = 0 if not threshold else (
result / float(threshold)) * 10000
elif kpi_type == self.FACING_SOS:
score = self.tools.calculate_share_of_shelf(
{'brand_fk': entity['brand_fk']},
category_fk=entity['category_fk'],
**scene_filters)
score *= 10000
elif kpi_type == self.LINEAR_SOS:
score, dummy = self.tools.calculate_linear_share_of_shelf(
{'brand_fk': entity['brand_fk']},
category_fk=entity['category_fk'],
**scene_filters)
score *= 10000
elif kpi_type == self.SHELF_SPACE_LENGTH:
result = int(
self.tools.calculate_share_space_length(
brand_fk=entity['brand_fk'],
category_fk=entity['category_fk'],
**scene_filters))
threshold = int(
self.tools.calculate_share_space_length(
category_fk=entity['category_fk'],
**scene_filters))
elif kpi_type == self.ASSORTMENT:
if params[self.MANUFACTURERS]:
manufacturers = params[self.MANUFACTURERS].split(
self.SEPARATOR)
if entity['manufacturer_name'] not in manufacturers:
continue
result = int(
self.tools.calculate_assortment(
brand_fk=entity['brand_fk']))
elif kpi_type == self.COUNT_OF_SCENES:
brand_aggregation = 0
for group in self.template_groups.keys():
result = int(
self.tools.calculate_number_of_scenes(
category_fk=entity['category_fk'],
brand_fk=entity['brand_fk'],
template_name=self.template_groups[group]))
brand_aggregation += result
kpi_name = params[self.KPI_NAME].format(
category=entity['category'],
brand=entity['brand_name'],
display_type=group)
self.save_result(set_name, kpi_name, result=result)
reference_kpi = self.template_data[self.template_data[
self.REFERENCE_KPI] == params[self.SET_NAME]].iloc[0]
reference_name = reference_kpi[self.KPI_NAME].format(
category=entity['category'],
brand=entity['brand_name'])
self.save_result(reference_kpi[self.SET_NAME],
reference_name,
result=brand_aggregation)
continue
else:
continue
if kpi_name is None:
kpi_name = params[self.KPI_NAME].format(
category=entity['category'],
brand=entity['brand_name'])
self.save_result(set_name,
kpi_name,
score=score,
result=result,
threshold=threshold)
def calculation_per_manufacturer(self):
"""
This function calculates all KPIs which are saved per manufacturer_name.
"""
kpis = self.template_data[self.template_data[self.SUB_CALCULATION] ==
'Manufacturer']
for x, entity in self.scif.drop_duplicates(
subset=['manufacturer_fk', 'category']).iterrows():
for y, params in kpis.iterrows():
score = result = threshold = None
set_name = params[self.SET_NAME]
kpi_name = params[self.KPI_NAME].format(
category=entity['category'],
manufacturer=entity['manufacturer_name'])
kpi_type = params[self.KPI_TYPE]
scene_filters = self.get_scenes_filters(
params, entity['category'])
if kpi_type == self.FACING_COUNT:
result = int(
self.tools.calculate_availability(
manufacturer_fk=entity['manufacturer_fk'],
category_fk=entity['category_fk'],
**scene_filters))
elif kpi_type == self.FACING_SOS:
score = self.tools.calculate_share_of_shelf(
{'manufacturer_fk': entity['manufacturer_fk']},
category_fk=entity['category_fk'],
**scene_filters)
score *= 10000
elif kpi_type == self.LINEAR_SOS:
score, dummy = self.tools.calculate_linear_share_of_shelf(
{'manufacturer_fk': entity['manufacturer_fk']},
category_fk=entity['category_fk'],
**scene_filters)
score *= 10000
elif kpi_type == self.SHELF_SPACE_LENGTH:
result = int(
self.tools.calculate_share_space_length(
manufacturer_fk=entity['manufacturer_fk'],
category_fk=entity['category_fk'],
**scene_filters))
elif kpi_type == self.ASSORTMENT_SHARE:
category_assortment = self.tools.calculate_availability(
category_fk=entity['category_fk'])
manufacturer_assortment = self.tools.calculate_availability(
manufacturer_fk=entity['manufacturer_fk'],
category_fk=entity['category_fk'])
score = 0 if not category_assortment else manufacturer_assortment / float(
category_assortment)
score *= 10000
else:
continue
self.save_result(set_name,
kpi_name,
score=score,
result=result,
threshold=threshold)
def calculation_per_category(self):
"""
This function calculates all KPIs which are saved per category.
"""
kpis = self.template_data[self.template_data[self.SUB_CALCULATION] ==
'Category']
for x, entity in self.scif.drop_duplicates(
subset=['category_fk']).iterrows():
for y, params in kpis.iterrows():
score = result = threshold = None
set_name = params[self.SET_NAME]
kpi_name = None
kpi_type = params[self.KPI_TYPE]
scene_filters = self.get_scenes_filters(
params, entity['category'])
if kpi_type == self.EMPTY_FACING_SOS:
result = self.tools.calculate_share_of_shelf(
{'product_type': 'Empty'},
include_empty=self.tools.INCLUDE_EMPTY,
category_fk=entity['category_fk'],
**scene_filters)
result *= 10000
elif kpi_type == self.DISTRIBUTION:
self.calculate_distribution(entity['category'], params)
elif kpi_type == self.COUNT_OF_POSM_BY_TYPE:
if scene_filters:
match_display = self.match_display_in_scene[
self.tools.get_filter_condition(
self.match_display_in_scene, **scene_filters)]
else:
match_display = self.match_display_in_scene.copy()
custom_sheet = self.get_template(params[self.CUSTOM_SHEET])
if self.store_retailer not in custom_sheet.columns:
continue
filtered_sheet = custom_sheet[
(custom_sheet[self.CATEGORY] == entity['category'])
& (custom_sheet[self.store_retailer].apply(bool))]
for display_type in filtered_sheet[self.BRAND].unique():
displays = filtered_sheet[filtered_sheet[self.BRAND] ==
display_type]
for display_name in displays[self.CLAIM_NAME].unique():
kpi_name = params[self.KPI_NAME].format(
category=entity['category'],
display_type=display_type,
display=display_name)
result = len(match_display[
match_display['display_name'] == display_name])
self.save_result(set_name, kpi_name, result=result)
continue
elif kpi_type == self.COUNT_OF_POSM:
custom_sheet = self.get_template(params[self.CUSTOM_SHEET])
if self.store_retailer not in custom_sheet.columns:
continue
filtered_sheet = custom_sheet[
(custom_sheet[self.CATEGORY] == entity['category'])
& (custom_sheet[self.store_retailer].apply(bool))]
displays = filtered_sheet[
self.CLAIM_NAME].unique().tolist()
result = len(match_display[
match_display['display_name'].isin(displays)])
elif kpi_type == self.ASSORTMENT_SHARE:
reference_results = self.convert_results_to_df(
params[self.REFERENCE_KPI])
reference_results = reference_results[
reference_results['category'] == entity['category']]
category_assortment = self.tools.calculate_assortment(
category_fk=entity['category_fk'])
for brand in reference_results['brand'].unique():
brand_assortment = reference_results[
reference_results['brand'] ==
brand]['result'].sum()
score = 0 if not category_assortment else int(
(brand_assortment / float(category_assortment)) *
10000)
kpi_name = params[self.KPI_NAME].format(
category=entity['category'], brand=brand)
self.save_result(set_name,
kpi_name,
score=score,
result=brand_assortment,
threshold=category_assortment)
continue
elif kpi_type == self.SHARE_OF_DISPLAY:
reference_kpi = self.template_data[(~self.template_data[
self.SET_NAME].isin([params[self.SET_NAME]])) & (
self.template_data[self.REFERENCE_KPI] == params[
self.REFERENCE_KPI])].iloc[0]
reference_results = self.convert_results_to_df(
params[self.REFERENCE_KPI])
reference_results = reference_results[
reference_results['category'] == entity['category']]
manufacturers = params[self.MANUFACTURERS].split(
self.SEPARATOR)
total_share = reference_results['result'].sum()
manufacturer_share = 0
for brand in reference_results['brand'].unique():
brand_share = reference_results[
reference_results['brand'] ==
brand]['result'].sum()
# P&G Share
brand_manufacturer = self.brand_to_manufacturer.get(
brand, '')
if brand_manufacturer in manufacturers:
manufacturer_share += brand_share
# Share for brand
score = 0 if not total_share else int(
(brand_share / float(total_share)) * 10000)
reference_name = reference_kpi[self.KPI_NAME].format(
category=entity['category'], brand=brand)
self.save_result(reference_kpi[self.SET_NAME],
reference_name,
score=score)
score = 0 if not total_share else int(
(manufacturer_share / float(total_share)) * 10000)
elif kpi_type == self.EMPTY_SPACES:
category_empty = self.empty_spaces.get(
entity['category'], {}).get('png', 0)
category_facings = int(
self.tools.calculate_availability(
category_fk=entity['category_fk'],
manufacturer_fk=entity['manufacturer_fk']))
category_facings_with_empty = category_facings + category_empty
if not category_facings_with_empty:
score = 0
else:
score = int(
(category_empty /
float(category_facings_with_empty)) * 10000)
result = category_empty
threshold = category_facings_with_empty
else:
continue
if result is not None or score is not None or threshold is not None:
if kpi_name is None:
kpi_name = params[self.KPI_NAME].format(
category=entity['category'])
self.save_result(set_name,
kpi_name,
score=score,
result=result,
threshold=threshold)
self.scoring_calculation(category=entity['category'])
def convert_results_to_df(self, set_name):
"""
This function extracts the atomic KPI results of a given KPI, and turns it into a dataframe.
"""
output = []
if set_name in self.atomic_results.keys():
for atomic in self.atomic_results[set_name].keys():
results = {'result': self.atomic_results[set_name][atomic]}
for data in atomic.split(self.UNICODE_DASH):
name, value = data.split(':')
results[name.lower()] = value
output.append(results)
return pd.DataFrame(output)
def save_result(self,
set_name,
kpi_name,
score=None,
result=None,
threshold=None):
"""
Given a score and/or result and/or threshold, this function writes an atomic KPI to the DB.
"""
if isinstance(score, float):
score = int(round(score))
if isinstance(result, float):
result = int(round(result))
if isinstance(threshold, float):
threshold = int(round(threshold))
self.write_to_db_result(level=self.LEVEL3,
score=score,
result=result,
threshold=threshold,
kpi_name=set_name,
atomic_kpi_name=kpi_name.replace("'", ""))
def scoring_calculation(self, category):
"""
This function calculates the results of the scoring KPIs, for a given category.
"""
kpi_name = 'Category:{}'.format(category)
for x, params in self.scoring_data.iterrows():
set_name = params[self.SET_NAME]
target = self.get_target(set_name, category)
if target is None:
continue
kpi_type = params[self.KPI_TYPE]
reference_kpi = params[self.REFERENCE_KPI]
scenes_filters = self.get_scenes_filters(params, category)
if reference_kpi:
reference_results = self.convert_results_to_df(reference_kpi)
if reference_results.empty:
continue
reference_results = reference_results[
reference_results['category'] == category]
if kpi_type == 'Percentage Passed':
total_calculated = len(reference_results)
total_passed = reference_results['result'].sum()
result = 0 if not total_calculated else int(
(total_passed / float(total_calculated)) * 10000)
else:
continue
else:
if kpi_type == self.LINEAR_SOS:
result, dummy = self.tools.calculate_linear_share_of_shelf(
sos_filters={
'manufacturer_name': self.OWN_MANUFACTURER
},
category=category,
**scenes_filters)
result *= 10000
elif kpi_type == self.FACING_SOS:
result = self.tools.calculate_share_of_shelf(
sos_filters={
'manufacturer_name': self.OWN_MANUFACTURER
},
category=category,
**scenes_filters)
result *= 10000
elif kpi_type == self.POSM_ASSORTMENT:
result = self.calculate_posm_assortment_share(
category, params, scenes_filters)
elif kpi_type == self.SURVEY_QUESTION:
survey_template = self.get_template(
params[self.CUSTOM_SHEET])
survey_template = survey_template[
(survey_template[self.KPI_NAME] == set_name)
& (survey_template[self.CATEGORY] == category)]
if survey_template.empty:
continue
survey_id = None
for y, survey in survey_template.iterrows():
if self.store_type in survey[self.STORE_TYPE].split(
self.SEPARATOR):
survey_id = int(float(survey[self.SURVEY_ID]))
if survey_id is None:
continue
result = self.tools.get_survey_answer(
('question_fk', survey_id))
else:
continue
weight = int(float(params[self.WEIGHT]) * 100)
if isinstance(target, (int, float)):
score = weight if result >= target else 0
else:
score = weight if result == target else 0
self.save_result(set_name,
kpi_name,
result=result,
score=score,
threshold=target)
def calculate_posm_assortment_share(self, category, params, filters):
"""
This function calculates the share of POSM assortment (number of unique POSMs out of all POSMs in the template).
"""
template_data = self.get_template(params[self.CUSTOM_SHEET])
template_data = template_data[template_data[self.CATEGORY] == category]
if template_data.empty or self.store_retailer not in template_data.keys(
):
return None
posm_items = template_data[template_data[self.store_retailer].apply(
bool)][self.CLAIM_NAME].tolist()
match_display = self.match_display_in_scene[
self.tools.get_filter_condition(self.match_display_in_scene,
**filters)]
assortment = len(match_display[match_display['display_name'].isin(
posm_items)]['display_name'].unique())
result = 0 if len(posm_items) == 0 else int(
(assortment / float(len(posm_items))) * 10000)
return result
def write_to_db_result(self,
level,
score=None,
result=None,
threshold=None,
**kwargs):
"""
This function creates the result data frame of every KPI (atomic KPI/KPI/KPI set),
and appends the insert SQL query into the queries' list, later to be written to the DB.
"""
attributes = self.create_attributes_dict(level, score, result,
threshold, **kwargs)
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,
level,
score=None,
result=None,
threshold=None,
**kwargs):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
"""
if level == self.LEVEL1:
set_name = kwargs['kpi_set_name']
set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name']
== set_name]['kpi_set_fk'].values[0]
attributes = pd.DataFrame(
[(set_name, self.session_uid, self.store_id,
self.visit_date.isoformat(), score, set_fk)],
columns=[
'kps_name', 'session_uid', 'store_fk', 'visit_date',
'score_1', 'kpi_set_fk'
])
elif level == self.LEVEL2:
kpi_name = kwargs['kpi_name']
kpi_fk = self.kpi_static_data[self.kpi_static_data['kpi_name'] ==
kpi_name]['kpi_fk'].values[0]
attributes = pd.DataFrame(
[(self.session_uid, self.store_id, self.visit_date.isoformat(),
kpi_fk, kpi_name, score)],
columns=[
'session_uid', 'store_fk', 'visit_date', 'kpi_fk',
'kpk_name', 'score'
])
self.kpi_results[kpi_name] = score
elif level == self.LEVEL3:
kpi_name = kwargs['kpi_name']
kpi_fk = self.kpi_static_data[self.kpi_static_data['kpi_name'] ==
kpi_name]['kpi_fk'].values[0]
kpi_set_name = self.kpi_static_data[
self.kpi_static_data['kpi_fk'] ==
kpi_fk]['kpi_set_name'].values[0]
atomic_kpi_name = kwargs['atomic_kpi_name']
attributes = pd.DataFrame(
[(atomic_kpi_name, self.session_uid,
kpi_set_name, self.store_id, self.visit_date.isoformat(),
datetime.utcnow().isoformat(), score, result, threshold,
kpi_fk)],
columns=[
'display_text', 'session_uid', 'kps_name', 'store_fk',
'visit_date', 'calculation_time', 'score', 'result',
'threshold', 'kpi_fk'
])
if kpi_set_name not in self.atomic_results.keys():
self.atomic_results[kpi_set_name] = {}
self.atomic_results[kpi_set_name][
atomic_kpi_name] = result if result is not None else score
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)
cur = self.rds_conn.db.cursor()
delete_queries = PNGAUQueries.get_delete_session_results_query(
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 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 CCRU_SANDContract:
STORE_NUMBER = 'Store Number'
START_DATE = 'Start Date'
END_DATE = 'End Date'
def __init__(self, rds_conn=None):
self.cloud_path = CLOUD_BASE_PATH
self.temp_path = os.path.join(TEMPLATES_TEMP_PATH, 'TempFile')
self.stores = {}
self.stores_processed = []
self.invalid_stores = []
self.stores_with_invalid_dates = []
self.stores_with_invalid_targets = []
def __del__(self):
if os.path.exists(self.temp_path):
os.remove(self.temp_path)
@property
def amz_conn(self):
if not hasattr(self, '_amz_conn'):
self._amz_conn = StorageFactory.get_connector(BUCKET)
return self._amz_conn
@property
def rds_conn(self):
if not hasattr(self, '_rds_conn'):
self._rds_conn = PSProjectConnector(PROJECT,
DbUsers.CalculationEng)
try:
pd.read_sql_query('select pk from probedata.session limit 1',
self._rds_conn.db)
except Exception as e:
self._rds_conn.disconnect_rds()
self._rds_conn = PSProjectConnector(PROJECT,
DbUsers.CalculationEng)
return self._rds_conn
@property
def store_data(self):
if not hasattr(self, '_store_data'):
query = "select pk as store_fk, store_number_1 as store_number from static.stores"
self._store_data = pd.read_sql_query(query, self.rds_conn.db)
return self._store_data
def get_store_fk(self, store_number):
store_number = str(store_number)
if store_number in self.stores:
store_fk = self.stores[store_number]
else:
store_fk = self.store_data[self.store_data['store_number'] ==
store_number]
if not store_fk.empty:
store_fk = store_fk['store_fk'].values[0]
self.stores[store_number] = store_fk
else:
store_fk = None
return store_fk
def get_json_file_content(self, file_name):
"""
This function receives a KPI set name and return its relevant template as a JSON.
"""
cloud_path = os.path.join(CLOUD_BASE_PATH, file_name)
with open(self.temp_path, 'wb') as f:
try:
self.amz_conn.download_file(cloud_path, f)
except:
f.write('{}')
with open(self.temp_path, 'rb') as f:
data = json.load(f)
os.remove(self.temp_path)
return data
def parse_and_upload_file(self):
Log.info("Starting template parsing and validation")
parsed_args = self.parse_arguments()
file_path = parsed_args.file
kpi_weights = zip(
list(
pd.read_excel(file_path,
header=2,
sheet_name=TARGETS_SHEET_NAME).columns)[3:],
list(
pd.read_excel(file_path,
skipcols=3,
sheet_name=TARGETS_SHEET_NAME).iloc[0].values))
kpi_weights = {x[0]: x[1] for x in kpi_weights}
raw_data = pd.read_excel(file_path,
skiprows=2,
sheet_name=TARGETS_SHEET_NAME,
dtype={
self.STORE_NUMBER: str
}).fillna('')
if self.STORE_NUMBER not in raw_data.columns:
Log.error('File must contain a {} column header'.format(
self.STORE_NUMBER))
return
if self.START_DATE not in raw_data.columns:
Log.error('File must contain a {} column header'.format(
self.START_DATE))
return
if self.END_DATE not in raw_data.columns:
Log.error('File must contain a {} column header'.format(
self.END_DATE))
return
raw_data[self.STORE_NUMBER] = raw_data[self.STORE_NUMBER].astype(str)
raw_data[self.START_DATE] = raw_data[self.START_DATE].astype(str)
raw_data[self.END_DATE] = raw_data[self.END_DATE].astype(str)
Log.info("Starting Stores validation")
target_data_new = {}
count_stores_total = raw_data.shape[0]
for x, row in raw_data.iterrows():
store_number = row[self.STORE_NUMBER]
store_id = self.get_store_fk(store_number)
if store_id is None:
self.invalid_stores.append(store_number)
else:
if store_id not in target_data_new.keys():
target_data_new[store_id] = []
row = row.to_dict()
row_to_append = {
self.STORE_NUMBER: row[self.STORE_NUMBER],
self.START_DATE: row[self.START_DATE],
self.END_DATE: row[self.END_DATE]
}
for key in row.keys():
if key in kpi_weights:
row_to_append[str(key)] = [row[key], kpi_weights[key]]
target_data_new[store_id].append(row_to_append)
count_stores_processed = x + 1
if count_stores_processed % 1000 == 0 or count_stores_processed == count_stores_total:
Log.info("Number of stores validated: {}/{}".format(
count_stores_processed, count_stores_total))
if self.invalid_stores:
Log.warning(
"The following stores do not exist in the DB and will be ignored ({}): "
"{}".format(len(self.invalid_stores), self.invalid_stores))
Log.info("Starting data processing")
count_stores_total = len(target_data_new.keys())
for x, store_id in enumerate(target_data_new.keys()):
data_new = target_data_new[store_id][0]
start_date_new = dt.datetime.strptime(data_new[self.START_DATE],
'%Y-%m-%d').date()
end_date_new = dt.datetime.strptime(data_new[self.END_DATE],
'%Y-%m-%d').date()
store_number = data_new[self.STORE_NUMBER]
if not start_date_new <= end_date_new:
self.stores_with_invalid_dates += [store_number]
else:
target_data = []
target_data_cur = self.get_json_file_content(str(store_id))
for data_cur in target_data_cur:
try:
start_date_cur = dt.datetime.strptime(
data_cur[self.START_DATE], '%Y-%m-%d').date()
end_date_cur = dt.datetime.strptime(
data_cur[self.END_DATE], '%Y-%m-%d').date()
store_number_cur = data_cur[self.STORE_NUMBER]
except:
self.stores_with_invalid_targets += [store_number]
continue
if store_number_cur == store_number \
and start_date_cur <= end_date_new \
and end_date_cur >= start_date_new:
details_new = data_new.copy()
del details_new[self.START_DATE]
del details_new[self.END_DATE]
details_cur = data_cur.copy()
del details_cur[self.START_DATE]
del details_cur[self.END_DATE]
if details_cur == details_new:
data_new[self.START_DATE] = str(
start_date_cur
) if start_date_cur <= start_date_new else str(
start_date_new)
else:
end_date_cur = start_date_new - dt.timedelta(
days=1)
if start_date_cur <= end_date_cur:
data_cur[self.END_DATE] = str(end_date_cur)
target_data += [data_cur]
else:
target_data += [data_cur]
target_data += [data_new]
try:
with open(self.temp_path, 'wb') as f:
f.write(json.dumps(target_data))
self.amz_conn.save_file(self.cloud_path, str(store_id),
self.temp_path)
except Exception as e:
Log.error(
"Store Seq/ID/Number: {}/{}/{}. Error: {}".format(
x, store_id, store_number, e))
Log.error("target_data: {}".format(target_data))
count_stores_processed = x + 1
self.stores_processed += [store_number]
if count_stores_processed % 1000 == 0 or count_stores_processed == count_stores_total:
Log.info("Number of stores processed: {}/{}".format(
count_stores_processed, count_stores_total))
# Log.debug("Stores processed: {}".format(self.stores_processed))
self.stores_processed = []
if os.path.exists(self.temp_path):
os.remove(self.temp_path)
if self.invalid_stores:
Log.warning(
"The following stores do not exist in the DB and were ignored ({}): "
"{}".format(len(self.invalid_stores), self.invalid_stores))
if self.stores_with_invalid_dates:
Log.warning(
"The following stores have invalid date period and were ignored ({}): "
"{}".format(len(self.stores_with_invalid_dates),
self.stores_with_invalid_dates))
if self.stores_with_invalid_targets:
Log.warning(
"The following stores have invalid target format and were ignored ({}): "
"{}".format(len(self.stores_with_invalid_dates),
self.stores_with_invalid_dates))
Log.info("Execution targets are uploaded successfully. " +
("Incorrect template data were ignored (see above)"
if self.invalid_stores or self.stores_with_invalid_dates
or self.stores_with_invalid_targets else ""))
@staticmethod
def parse_arguments():
"""
This function gets the arguments from the command line / configuration in case of a local run and manages them.
To run it locally just copy: -e prod --file **your file path** to the configuration parameters
:return:
"""
parser = argparse.ArgumentParser(description='Execution Contract')
parser.add_argument('--env',
'-e',
type=str,
help='The environment - dev/int/prod')
parser.add_argument('--file',
type=str,
required=True,
help='The targets template')
return parser.parse_args()
class DISPLAYSToolBox(DISPLAYSConsts):
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.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.all_products = self.data_provider[Data.ALL_PRODUCTS]
# self.all_products = self.all_products.merge(self.get_additional_attributes(), on='product_fk', how='left')
self.match_display_in_scene = self.get_match_display()
self.tools = DISPLAYSGENERALToolBox(self.data_provider,
self.output,
rds_conn=self.rds_conn,
scif=self.scif)
self.template_data = parse_template(TEMPLATE_PATH)
self.kpi_static_data = self.get_kpi_static_data()
self.kpi_results_queries = []
# def get_additional_attributes(self):
# query = DISPLAYSQueries.get_attributes_data()
# attributes = pd.read_sql_query(query, self.rds_conn.db)
# return attributes
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 = DISPLAYSQueries.get_match_display(self.session_uid)
match_display = pd.read_sql_query(query, self.rds_conn.db)
match_display = match_display.merge(
self.scif.drop_duplicates(subset=['scene_fk']),
on='scene_fk',
how='left')
return match_display
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 = DISPLAYSQueries.get_all_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
def main_calculation(self):
"""
This function calculates the KPI results.
"""
kpi_results = {
self.kpi_static_data.iloc[k]['kpi_name']: [0, 0, 0, 0, 0, 0]
for k in xrange(len(self.kpi_static_data))
if self.kpi_static_data.iloc[k]['kpi_fk'] not in (195, 196, 197,
198, 199, 200,
201, 202)
}
relevant_displays = self.template_data[
self.SCENE_RECOGNITION].unique().tolist()
actual_displays = self.match_display_in_scene[
self.match_display_in_scene['display_name'].isin(
relevant_displays)]
for scene in actual_displays['scene_fk'].unique():
scene_displays = actual_displays[actual_displays['scene_fk'] ==
scene]
calculate_all = False
if scene_displays.iloc[0]['template_name'] == 'Display (End Cap/Palette Drop/Rack)' \
and scene_displays['display_name'].unique().tolist() == ['Display']:
display_type = scene_displays['display_name'].values[0]
kpi_data = self.template_data[self.template_data[
self.SCENE_RECOGNITION] == display_type]
bays = sorted(scene_displays['bay_number'].unique().tolist())
bay_groups = [[]]
for bay in bays:
if len(bay_groups[-1]) < 3:
bay_groups[-1].append(bay)
else:
bay_groups.append([bay])
for i, group in enumerate(bay_groups):
display = scene_displays.iloc[0]
display['bay_number'] = group
for p in xrange(len(kpi_data)):
params = kpi_data.iloc[p]
score = self.calculate_facing_sos(params, display)
for x, s in enumerate(score):
kpi_results[params[self.KPI_NAME]][x * 3 + 1] += s
if len(bay_groups) == 1 or len(
bay_groups[-1]
) == 3 or i + 2 < len(bay_groups):
for x, s in enumerate(score):
kpi_results[params[self.KPI_NAME]][x * 3 +
2] += s
elif i + 2 == len(bay_groups):
display['bay_number'] = group + bay_groups[i + 1]
score = self.calculate_facing_sos(params, display)
for x, s in enumerate(score):
kpi_results[params[self.KPI_NAME]][x * 3 +
2] += s
else:
calculate_all = True
del scene_displays
for d in xrange(len(scene_displays)):
display = scene_displays.iloc[d]
display_name = display['display_name']
kpi_data = self.template_data[self.template_data[
self.SCENE_RECOGNITION] == display_name]
for p in xrange(len(kpi_data)):
params = kpi_data.iloc[p]
score = self.calculate_facing_sos(params, display)
for x, s in enumerate(score):
if display_name == 'Display':
kpi_results[params[self.KPI_NAME]][x * 3 + 0] += s
else:
kpi_results[params[self.KPI_NAME]][x] += s
if calculate_all and display['display_name'] == 'Display':
for x, s in enumerate(score):
kpi_results[params[self.KPI_NAME]][x * 3 + 1] += s
kpi_results[params[self.KPI_NAME]][x * 3 + 2] += s
for kpi_name in kpi_results.keys():
self.write_to_db_result(kpi_name, 100, level=self.LEVEL2)
self.write_to_db_result(kpi_name,
kpi_results[kpi_name],
level=self.LEVEL3)
self.write_to_db_result('Manufacturer Displays',
100,
level=self.LEVEL1)
def calculate_facing_sos(self, params, display):
filters = self.get_filters(params)
numerator = self.tools.calculate_availability(
bay_number=display['bay_number'],
product_type=('Empty', self.tools.EXCLUDE_FILTER),
scene_fk=display['scene_fk'],
**filters)
denominator = self.tools.calculate_availability(
bay_number=display['bay_number'],
product_type=('Empty', self.tools.EXCLUDE_FILTER),
scene_fk=display['scene_fk'])
result = 0 if denominator == 0 else numerator / float(denominator)
target = map(float, str(params[self.SOS_TARGET]).split(self.SEPARATOR))
scores = []
for t in target:
scores.append(1 if result > t else 0)
return scores
def get_filters(self, params):
products = set()
if params[self.MANUFACTURERS]:
products = products.union(
self.get_product_list(manufacturer_name=params[
self.MANUFACTURERS].split(self.SEPARATOR)))
if params[self.BRANDS]:
products = products.union(
self.get_product_list(
att2=params[self.BRANDS].split(self.SEPARATOR)))
else:
products = products.union(
self.all_products['product_ean_code'].unique().tolist())
if params[self.SSD_OR_STILL]:
products = products.intersection(
self.get_product_list(att4=params[self.SSD_OR_STILL]))
if params[self.MANUFACTURERS_TO_EXCLUDE]:
products = products.difference(
self.get_product_list(manufacturer_name=params[
self.MANUFACTURERS_TO_EXCLUDE].split(self.SEPARATOR)))
if params[self.BRANDS_TO_EXCLUDE]:
products = products.difference(
self.get_product_list(
att2=params[self.BRANDS_TO_EXCLUDE].split(self.SEPARATOR)))
products = products.difference(
self.get_product_list(manufacturer_name='GENERAL'))
# products = self.all_products[self.all_products['att2'].isin(params[self.ATT2].split(self.SEPARATOR))]['product_ean_code'].unique().tolist()
return dict(product_ean_code=list(products))
def get_product_list(self, **filters):
product_list = self.all_products[self.tools.get_filter_condition(
self.all_products, **filters)]
return set(product_list['product_ean_code'].unique().tolist())
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, name, score, level):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
"""
if level == self.LEVEL1:
set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name']
== name]['kpi_set_fk'].values[0]
attributes = pd.DataFrame(
[(name, self.session_uid, self.store_id,
self.visit_date.isoformat(), format(score, '.2f'), set_fk)],
columns=[
'kps_name', 'session_uid', 'store_fk', 'visit_date',
'score_1', 'kpi_set_fk'
])
elif level == self.LEVEL2:
kpi_fk = self.kpi_static_data[self.kpi_static_data['kpi_name'] ==
name]['kpi_fk'].values[0]
attributes = pd.DataFrame(
[(self.session_uid, self.store_id, self.visit_date.isoformat(),
kpi_fk, name, score)],
columns=[
'session_uid', 'store_fk', 'visit_date', 'kpi_fk',
'kpk_name', 'score'
])
elif level == self.LEVEL3:
score, score_2, score_3, result, result_2, result_3 = score
data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_name']
== name]
atomic_fk = data['atomic_kpi_fk'].values[0]
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = data['kpi_set_name'].values[0]
attributes = pd.DataFrame(
[(name, self.session_uid, kpi_set_name, self.store_id,
self.visit_date.isoformat(), datetime.utcnow().isoformat(),
score, score_2, score_3, result, result_2, result_3, kpi_fk,
atomic_fk)],
columns=[
'display_text', 'session_uid', 'kps_name', 'store_fk',
'visit_date', 'calculation_time', 'score', 'score_2',
'score_3', 'result', 'result_2', 'result_3', '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.
"""
self.rds_conn.disconnect_rds()
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
cur = self.rds_conn.db.cursor()
delete_queries = DISPLAYSQueries.get_delete_session_results_query(
self.session_uid, self.kpi_static_data)
for query in delete_queries:
cur.execute(query)
for query in self.kpi_results_queries:
cur.execute(query)
self.rds_conn.db.commit()
class CCRUCCHKPIFetcher:
TCCC = ['TCCC', 'BF']
def __init__(self, project_name):
self.project_name = project_name
self.rds_conn = self.rds_connection()
self.kpi_set_name = None
self.kpi_static_data = None
def rds_connection(self):
if not hasattr(self, '_rds_conn'):
self._rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
try:
pd.read_sql_query('select pk from probedata.session limit 1',
self._rds_conn.db)
except:
self._rds_conn.disconnect_rds()
self._rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
return self._rds_conn
@staticmethod
def get_delete_session_results(session_uid, session_fk):
queries = [
"delete from report.kps_results where session_uid = '{}';".format(
session_uid),
"delete from report.kpk_results where session_uid = '{}';".format(
session_uid),
"delete from report.kpi_results where session_uid = '{}';".format(
session_uid),
"delete from pservice.custom_gaps where session_fk = '{}';".format(
session_fk),
"delete from pservice.custom_scene_item_facts where session_fk = '{}';"
.format(session_fk)
]
return queries
def get_static_kpi_data(self, kpi_set_name=None):
kpi_set_name = kpi_set_name if kpi_set_name else self.kpi_set_name
self.rds_conn = self.rds_connection()
query = """
select api.name as atomic_kpi_name, api.pk as atomic_kpi_fk,
kpi.display_text as kpi_name, kpi.pk as kpi_fk,
kps.name as kpi_set_name, kps.pk as kpi_set_fk
from static.atomic_kpi api
join static.kpi kpi on kpi.pk = api.kpi_fk
join static.kpi_set kps on kps.pk = kpi.kpi_set_fk
where kps.name = '{}'
""".format(kpi_set_name)
df = pd.read_sql_query(query, self.rds_conn.db)
return df
def get_kpi_set_fk(self):
kpi_set_fk = self.kpi_static_data['kpi_set_fk']
return kpi_set_fk.values[0]
def get_kpi_fk(self, kpi_name):
try:
kpi_name = kpi_name.decode('utf-8')
except UnicodeEncodeError:
pass
kpi_fk = self.kpi_static_data[self.kpi_static_data['kpi_name'] ==
kpi_name.replace("\\'", "'")]['kpi_fk']
if not kpi_fk.empty:
return kpi_fk.values[0]
else:
return None
def get_atomic_kpi_fk(self, atomic_kpi_name, kpi_fk=None):
try:
atomic_kpi_name = atomic_kpi_name.decode('utf-8')
except UnicodeEncodeError:
pass
if kpi_fk:
atomic_kpi_fk = self.kpi_static_data[
(self.kpi_static_data['kpi_fk'] == kpi_fk)
& (self.kpi_static_data['atomic_kpi_name'] ==
atomic_kpi_name.replace("\\'", "'"))]['atomic_kpi_fk']
else:
atomic_kpi_fk = self.kpi_static_data[
self.kpi_static_data['atomic_kpi_name'] ==
atomic_kpi_name.replace("\\'", "'")]['atomic_kpi_fk']
if not atomic_kpi_fk.empty:
return atomic_kpi_fk.values[0]
else:
return None
def get_category_target_by_region(self, category, store_id):
store_type_dict = {
'PoS 2017 - MT - Hypermarket': 'Hypermarket',
'PoS 2017 - MT - Supermarket': 'Supermarket',
'PoS 2017 - MT - Superette': 'Superette'
}
store_region_fk = self.get_store_region(store_id)
branch_fk = self.get_store_branch(store_id)
jg = CCRUJsonGenerator()
jg.create_kpi_data_json('cat_targets_by_region',
'MT Shelf facings_2017.xlsx')
targets = jg.project_kpi_dict['cat_targets_by_region']
final_target = 0
for row in targets:
if row.get('branch_fk') == branch_fk and row.get('region_fk') == store_region_fk \
and row.get('store_type') == store_type_dict.get(self.set_name):
final_target = row.get(category)
else:
continue
return final_target
def get_store_number(self, store_id):
query = """
SELECT store_number_1
FROM static.stores ss
WHERE ss.pk = {};
""".format(store_id)
cur = self.rds_conn.db.cursor()
cur.execute(query)
res = cur.fetchall()[0]
return res[0]
def get_store_region(self, store_id):
query = """
SELECT region_fk
FROM static.stores ss
WHERE ss.pk = {};
""".format(store_id)
cur = self.rds_conn.db.cursor()
cur.execute(query)
res = cur.fetchall()[0]
return res[0]
def get_attr15_store(self, store_id):
query = """
SELECT additional_attribute_15
FROM static.stores ss
WHERE ss.pk = {};
""".format(store_id)
cur = self.rds_conn.db.cursor()
cur.execute(query)
result = cur.fetchall()
try:
result = float(result[0][0].replace(',', '.'))
except:
result = 1.0
return result
def get_test_store(self, store_id):
query = """
SELECT test_store
FROM static.stores ss
WHERE ss.pk = {};
""".format(store_id)
cur = self.rds_conn.db.cursor()
cur.execute(query)
res = cur.fetchall()[0]
return res[0]
def get_store_branch(self, store_id):
query = """
SELECT branch_fk
FROM static.stores ss
WHERE ss.pk = {};
""".format(store_id)
cur = self.rds_conn.db.cursor()
cur.execute(query)
res = cur.fetchall()[0]
return res[0]
def get_external_session_id(self, session_uid):
query = """
SELECT external_session_id
FROM probedata.session ss
WHERE ss.session_uid = '{}';
""".format(session_uid)
cur = self.rds_conn.db.cursor()
cur.execute(query)
res = cur.fetchall()[0]
return res[0]
def get_store_info(self, store_id):
query = """
SELECT s.pk as store_fk, s.additional_attribute_3, r.name as retailer, s.store_number_1,
s.business_unit_fk, s.additional_attribute_5, s.district_fk, s.test_store
FROM static.stores s
left join static.retailer r
on r.pk = s.retailer_fk where s.pk = '{}'
""".format(store_id)
store_info = pd.read_sql_query(query, self.rds_conn.db)
return store_info
def get_store_area_df(self, session_uid):
query = """
select sst.scene_fk, st.name, sc.session_uid from probedata.scene_store_task_area_group_items sst
join static.store_task_area_group_items st on st.pk=sst.store_task_area_group_item_fk
join probedata.scene sc on sc.pk=sst.scene_fk
where sc.delete_time is null and sc.session_uid = '{}';
""".format(session_uid)
cur = self.rds_conn.db.cursor()
cur.execute(query)
res = cur.fetchall()
df = pd.DataFrame(
list(res), columns=['scene_fk', 'store_area_name', 'session_uid'])
return df
def get_kpi_result_values(self):
self.rds_conn = self.rds_connection()
query = """
select
rt.pk as result_type_fk,
rt.name as result_type,
rv.pk as result_value_fk,
rv.value as result_value
from static.kpi_result_value rv
join static.kpi_result_type rt on rt.pk=rv.kpi_result_type_fk;
"""
df = pd.read_sql_query(query, self.rds_conn.db)
return df
def get_kpi_entity_types(self):
self.rds_conn = self.rds_connection()
query = """
select * from static.kpi_entity_type;
"""
df = pd.read_sql_query(query, self.rds_conn.db)
return df
def get_kpi_entity(self, entity, entity_type_fk, entity_table_name,
entity_uid_field):
self.rds_conn = self.rds_connection()
query = """
select
'{0}' as entity,
{1} as type,
pk as fk,
{2} as uid_field
from {3};
""".format(entity, entity_type_fk, entity_uid_field,
entity_table_name)
df = pd.read_sql_query(query, self.rds_conn.db)
return df
def get_session_user(self, session_uid):
query = """
SELECT
sr.login_name AS user_name,
ur.role AS user_role,
sr.position AS user_position
FROM probedata.session ss
LEFT JOIN static.sales_reps sr ON sr.pk=ss.s_sales_rep_fk
LEFT JOIN static.mobile_user_roles ur ON ur.sales_rep_fk=sr.pk
WHERE ss.session_uid='{}';
""".format(session_uid)
result = pd.read_sql_query(
query, self.rds_conn.db).to_dict(orient='records')[0]
return result
def get_planned_visit_flag(self, session_uid):
query = """
SELECT pv.planned_flag
FROM probedata.session ss
LEFT JOIN pservice.planned_visits pv ON 1
AND pv.store_fk=ss.store_fk
AND pv.sales_rep_fk=ss.s_sales_rep_fk
AND pv.visit_date=ss.visit_date
WHERE ss.session_uid='{}';
""".format(session_uid)
result = pd.read_sql_query(query, self.rds_conn.db)[0][0]
return result
def get_top_skus_for_store(self, store_fk, visit_date):
query = """
select
anchor_product_fk,
group_concat(product_fk) as product_fks,
max(min_facings) as min_facings
from (
select
ifnull(ts.anchor_product_fk, ts.product_fk) as anchor_product_fk,
ts.product_fk as product_fk,
ifnull(ts.min_facings, 1) as min_facings
from {} ts
where ts.store_fk = {}
and ts.start_date <= '{}'
and ifnull(ts.end_date, curdate()) >= '{}'
) t
group by anchor_product_fk;
""".format('pservice.custom_osa', store_fk, visit_date,
visit_date)
data = pd.read_sql_query(query, self.rds_conn.db)
return data.groupby(['anchor_product_fk']).agg({
'product_fks': 'first',
'min_facings': 'first'
}).to_dict()
def get_custom_entity(self, entity_type):
self.rds_conn = self.rds_connection()
query = \
"""
SELECT en.pk, en.name
FROM static.custom_entity en
JOIN static.kpi_entity_type et ON et.pk=en.entity_type_fk
WHERE et.name = '{}';
""".format(entity_type)
data = pd.read_sql_query(query, self.rds_conn.db)
return data
def get_kpi_level_2_fk(self, kpi_level_2_type):
query = \
"""
SELECT pk FROM static.kpi_level_2
WHERE type = '{}';
""".format(kpi_level_2_type)
data = pd.read_sql_query(query, self.rds_conn.db)
return None if data.empty else data.values[0][0]
def get_kpi_operation_type_fk(self, kpi_operation_type):
query = \
"""
SELECT pk FROM static.kpi_operation_type
WHERE operation_type = '{}';
""".format(kpi_operation_type)
data = pd.read_sql_query(query, self.rds_conn.db)
return None if data.empty else data.values[0][0]
def get_scene_item_prices(self, scene_list):
query = """
SELECT
scene_fk,
product_fk,
MIN(IF((is_promotion = 0 AND outlier <> (-2) AND outlier <> 2)
OR (is_promotion = 0 AND (ISNULL(outlier) OR outlier = 0)), price, NULL)) AS price
FROM (
SELECT
mpip.product_fk AS product_fk
,mpip.probe_fk AS probe_fk
,pr.scene_fk AS scene_fk
,mpippav.value AS price
,mpippav.is_promotion AS is_promotion
,IF(ISNULL(tipr.soft_min) OR ISNULL(mpippav.value),NULL
,IF(mpippav.value > tipr.soft_min AND mpippav.value <= tipr.soft_max, 0
,IF(mpippav.value > tipr.hard_min AND mpippav.value <= tipr.soft_min, -1
,IF(mpippav.value > tipr.soft_max AND mpippav.value <= tipr.hard_max, 1
,IF(mpippav.value <= tipr.hard_min, -2
,2
)
)
)
)
) AS outlier
FROM probedata.match_product_in_probe mpip
JOIN probedata.match_product_in_probe_price_attribute_value mpippav ON mpip.pk = mpippav.match_product_in_probe_fk
JOIN probedata.probe pr ON pr.pk = mpip.probe_fk AND ISNULL(pr.delete_time)
LEFT JOIN (
SELECT
table1.pk
,table1.product_fk AS product_fk
,table1.soft_min AS soft_min
,table1.soft_max AS soft_max
,table1.hard_min AS hard_min
,table1.hard_max AS hard_max
,table1.avg_price AS avg_price
,table1.creation_time AS start_date
,IFNULL(table2.creation_time, NOW()) AS end_date
FROM (
SELECT ppr.*, @rownum := @rownum+1 AS rownum
FROM static.product_price_range ppr
JOIN (SELECT @rownum := 0) r
ORDER BY ppr.product_fk,ppr.creation_time
) table1
LEFT JOIN (
SELECT ppr.*, @rownum2 := @rownum2+1 AS rownum2
FROM static.product_price_range ppr
JOIN (SELECT @rownum2 := -1) r
ORDER BY ppr.product_fk,ppr.creation_time
) table2 ON table1.product_fk = table2.product_fk AND table1.rownum = table2.rownum2
) tipr ON tipr.product_fk = mpip.product_fk AND (mpip.creation_time BETWEEN tipr.start_date AND tipr.end_date)
WHERE 1
AND pr.scene_fk IN ({scene_list})
) prices
GROUP BY scene_fk, product_fk
HAVING price IS NOT NULL;
""".format(
scene_list=','.join([unicode(x) for x in scene_list]))
data = pd.read_sql_query(query, self.rds_conn.db)
return data
def get_scene_survey_response(self, scenes_list):
if scenes_list:
if len(scenes_list) == 1:
query = """
select sr.*, sq.question_text, sq.group_name
from probedata.scene_survey_response sr
join static.survey_question sq on sr.question_fk=sq.pk
where sq.delete_time is null
and sq.group_name in ('Cooler Audit', 'Cooler Audit Test')
and sr.delete_time is null
and sr.scene_fk in ({});
""".format(scenes_list[0])
else:
query = """
select sr.*, sq.question_text, sq.group_name
from probedata.scene_survey_response sr
join static.survey_question sq on sr.question_fk=sq.pk
where sq.delete_time is null
and sq.group_name in ('Cooler Audit', 'Cooler Audit Test')
and sr.delete_time is null
and sr.scene_fk in {};
""".format(tuple(scenes_list))
data = pd.read_sql_query(query, self.rds_conn.db)
else:
data = pd.DataFrame(
columns=['pk', 'text_value', 'question_text', 'group_name'])
return data
def get_all_coolers_from_assortment_list(self, cooler_list):
cooler_list = list(map(str, cooler_list))
if len(cooler_list) == 1:
query = """
select c.pk as cooler_fk, c.cooler_id, c.cooler_model_fk, m.name as cooler_model_name
from pservice.cooler c
left join pservice.cooler_model m
on c.cooler_model_fk = m.pk
where c.cooler_id = '{}';
""".format(cooler_list[0])
else:
query = """
select c.pk as cooler_fk, c.cooler_id, c.cooler_model_fk, m.name as cooler_model_name
from pservice.cooler c
left join pservice.cooler_model m
on c.cooler_model_fk = m.pk
where c.cooler_id in {};
""".format(tuple(cooler_list))
data = pd.read_sql_query(query, self.rds_conn.db)
return data
def get_kpi_external_targets(self, visit_date, store_fk):
query = """SELECT ext.*, ot.operation_type from static.kpi_external_targets ext
LEFT JOIN static.kpi_operation_type ot on ext.kpi_operation_type_fk=ot.pk
WHERE
((ext.start_date<='{}' and ext.end_date is null) or
(ext.start_date<='{}' and ext.end_date>='{}'))
AND ot.operation_type='COOLER_AUDIT'
AND ext.key_fk={}
""".format(visit_date, visit_date, visit_date, store_fk)
data = pd.read_sql_query(query, self.rds_conn.db)
return data
class CCBRToolBox:
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.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.tools = CCBRGENERALToolBox(self.data_provider,
self.output,
rds_conn=self.rds_conn)
self.store_info = self.data_provider[Data.STORE_INFO]
self.kpi_results_queries = []
self.survey = Survey(self.data_provider, self.output)
self.kpi_results_new_tables_queries = []
self.New_kpi_static_data = self.get_new_kpi_static_data()
self.session_id = self.data_provider.session_id
self.prices_per_session = PsDataProvider(
self.data_provider, self.output).get_price_union(self.session_id)
self.common_db = Common(self.data_provider)
self.count_sheet = pd.read_excel(PATH, Const.COUNT).fillna("")
self.group_count_sheet = pd.read_excel(PATH,
Const.GROUP_COUNT).fillna("")
self.survey_sheet = pd.read_excel(PATH, Const.SURVEY).fillna("")
def main_calculation(self):
"""
This function calculates the KPI results.
"""
kpis_sheet = pd.read_excel(PATH, Const.KPIS).fillna("")
for index, row in kpis_sheet.iterrows():
self.handle_atomic(row)
self.handle_simon_kpis()
self.commit_results_data()
def handle_simon_kpis(self):
"""
activate the availability and pricing functions
"""
active_products = self.all_products.loc[
self.all_products["is_active"] > 0]
self.calculate_availability(active_products)
self.calculate_pricing(self.all_products)
def calculate_availability(self, active_products):
"""
calculates the availability for all products per session, used is sovi and sovi vertical reports
:param active_products: a df containing only active products
"""
active_products_sku_and_other = active_products[
(active_products['product_type'] == 'SKU')
| (active_products['product_type'] == 'Other')]
active_products_pks = active_products_sku_and_other[
'product_fk'].unique().tolist()
filters = {'product_fk': active_products_pks}
filtered_df = self.scif[self.tools.get_filter_condition(
self.scif, **filters)]
facing_filtered = filtered_df.loc[filtered_df['facings'] > 0][[
'template_fk', 'product_fk', 'facings'
]]
facing_filtered_pks = facing_filtered['product_fk'].unique().tolist()
for product in facing_filtered_pks:
product_df = facing_filtered.loc[facing_filtered['product_fk'] ==
product]
product_template_fks = product_df['template_fk'].unique().tolist()
for template_fk in product_template_fks:
sum_facing = product_df.loc[product_df['template_fk'] ==
template_fk]['facings'].sum()
self.write_to_db_result_new_tables(fk=Const.AVAILABILITY_PK,
numerator_id=product,
score='1',
denominator_id=template_fk,
numerator_result='1',
result=sum_facing)
def calculate_pricing(self, all_products):
"""
inserting the db the pricing of all active and inactive skus.
used in preco and preco vertical reports
:param all_products: df containing all products
"""
only_sku_type_products = all_products.loc[all_products['product_type']
== 'SKU']
all_products_fks_size = only_sku_type_products[['product_fk',
'size']].fillna("")
product_fks_and_prices = self.prices_per_session
merge_size_and_price = pd.merge(all_products_fks_size,
product_fks_and_prices,
how='left',
on='product_fk')
merge_size_and_price['value'] = merge_size_and_price['value'].fillna(
'0')
for row in merge_size_and_price.itertuples():
product = row[1] # row['product_fk']
size = row[2] # row['size']
price = row[3] # row['value']
if size == '':
size = 0
if price > 0:
self.write_to_db_result_new_tables(fk=Const.PRICING_PK,
numerator_id=product,
numerator_result=size,
result=price)
def handle_atomic(self, row):
"""
run the correct kpi for a specific row in the template
:param row: a row from the template
"""
atomic_name = row[Const.ENGLISH_KPI_NAME].strip()
kpi_type = row[Const.KPI_TYPE].strip()
if kpi_type == Const.SURVEY:
self.handle_survey_atomics(atomic_name)
elif kpi_type == Const.COUNT:
self.handle_count_atomics(atomic_name)
elif kpi_type == Const.GROUP_COUNT:
self.handle_group_count_atomics(atomic_name)
def handle_survey_atomics(self, atomic_name):
"""
handle survey questions
:param atomic_name: the name of the kpi
:return: only if the survey filters aren't satisfied
"""
row = self.survey_sheet.loc[self.survey_sheet[Const.ENGLISH_KPI_NAME]
== atomic_name]
if row.empty:
Log.warning("Dataframe is empty, wrong kpi name: " + atomic_name)
return
store_type_filter = self.store_info['store_type'].values[0].strip()
store_type_template = row[Const.STORE_TYPE_TEMPLATE].values[0].strip()
# if cell in template is not empty
if store_type_template != "":
store_types = store_type_template.split(",")
store_types = [item.strip() for item in store_types]
if store_type_filter not in store_types:
return
# find the answer to the survey in session
question_id = row[Const.SURVEY_QUESTION_ID].values[0]
question_answer_template = row[Const.TARGET_ANSWER].values[0]
survey_result = self.survey.get_survey_answer(
('question_fk', question_id))
if question_answer_template == Const.NUMERIC:
if not survey_result:
survey_result = 0
if not isinstance(survey_result, (int, long, float)):
Log.warning("question id " + str(question_id) +
" in template is not a number")
survey_result = 0
else:
answer = self.survey.check_survey_answer(
('question_fk', question_id), question_answer_template)
survey_result = 1 if answer else -1
try:
atomic_pk = self.common_db.get_kpi_fk_by_kpi_name_new_tables(
atomic_name)
except IndexError:
Log.warning("There is no matching Kpi fk for kpi name: " +
atomic_name)
return
self.write_to_db_result_new_tables(fk=atomic_pk,
numerator_id=self.session_id,
numerator_result=survey_result,
result=survey_result)
def handle_count_atomics(self, atomic_name):
"""
handle count kpis, used in consolidada report
:param atomic_name: the name of the kpi to calculate
"""
sum_of_count = 0
target = 0
count_result = 0
row = self.count_sheet.loc[self.count_sheet[Const.ENGLISH_KPI_NAME] ==
atomic_name]
if row.empty:
Log.warning("Dataframe is empty, wrong kpi name: " + atomic_name)
return
try:
atomic_pk = self.common_db.get_kpi_fk_by_kpi_name_new_tables(
atomic_name)
except IndexError:
Log.warning("There is no matching Kpi fk for kpi name: " +
atomic_name)
return
for index, row in row.iterrows():
sum_of_count, target, count_result = self.handle_count_row(row)
if not isinstance(sum_of_count, (int, float, long)):
sum_of_count = count_result
self.write_to_db_result_new_tables(fk=atomic_pk,
numerator_id=self.session_id,
numerator_result=sum_of_count,
denominator_result=target,
result=count_result)
def handle_group_count_atomics(self, atomic_name):
"""
handle group count kpis (different from count in or and and conditions), used in consolidada report
:param atomic_name: the name of the kpi to calculate
"""
rows = self.group_count_sheet.loc[self.group_count_sheet[
Const.GROUP_KPI_NAME] == atomic_name]
group_weight = 0
group_result = 0
group_target = 0
group_sum_of_count = 0
sum_of_count_df = pd.DataFrame()
target_operator = ""
if rows.empty:
Log.warning("Dataframe is empty, wrong kpi name: " + atomic_name)
return
try:
atomic_pk = self.common_db.get_kpi_fk_by_kpi_name_new_tables(
atomic_name)
except IndexError:
Log.warning("There is no matching Kpi fk for kpi name: " +
atomic_name)
return
for index, row in rows.iterrows():
target_operator = row[Const.TARGET_OPERATOR].strip()
weight = row[Const.WEIGHT]
sum_of_count, target, count_result = self.handle_count_row(row)
if count_result >= 1:
group_weight += weight
if group_weight >= 100:
# use for getting numeric results instead of 1 and 0
if (target_operator == '+'):
sum_of_count_df = pd.concat(
[sum_of_count_df, sum_of_count])
else:
group_result = 1
break
# conditional, if given -1000 kpi must fail
elif count_result == -1000:
group_result = 0
break
# use for getting numeric results instead of 1 and 0
if (target_operator == '+'):
if sum_of_count_df.empty:
group_sum_of_count = 0
else:
group_sum_of_count = len(sum_of_count_df.groupby('scene_id'))
group_result = group_sum_of_count
self.write_to_db_result_new_tables(fk=atomic_pk,
numerator_id=self.session_id,
numerator_result=group_sum_of_count,
denominator_result=group_target,
result=group_result)
def handle_count_row(self, row):
"""
filters qall params in aspecific row and send it to the correct count calculation
:param row:
:return:
"""
count_type = row[Const.COUNT_TYPE].strip()
target = row[Const.TARGET]
target_operator = row[Const.TARGET_OPERATOR].strip()
product_template = row[Const.PRODUCT]
store_type_filter = self.store_info['store_type'].values[0]
store_type_template = row[Const.STORE_TYPE_TEMPLATE]
product_size = row[Const.PRODUCT_SIZE]
product_size_operator = row[Const.PRODUCT_SIZE_OPERATOR].strip()
product_measurement_unit = row[Const.MEASUREMENT_UNIT].strip()
consider_few = row[Const.CONSIDER_FEW]
multipack_template = row[Const.MULTIPACK].strip()
multipack_df = None
# filter store type
if store_type_template != "":
store_types = store_type_template.split(",")
store_types = [item.strip() for item in store_types]
if store_type_filter not in store_types:
return 0, 0, 0
filtered_df = self.scif.copy()
# filter product
if product_template != "":
products_to_check = product_template.split(",")
products_to_check = [item.strip() for item in products_to_check]
filtered_df = filtered_df[filtered_df['product_name'].isin(
products_to_check)]
if filtered_df.empty:
return 0, 0, 0
# filter product size
if product_size != "":
if product_measurement_unit == 'l':
product_size *= 1000
ml_df = filtered_df[filtered_df['size_unit'] == 'ml']
l_df = filtered_df[filtered_df['size_unit'] == 'l']
if multipack_template != "":
multipack_df = filtered_df[filtered_df['MPACK'] == 'Y']
temp_df = l_df.copy()
temp_df['size'] = l_df['size'].apply((lambda x: x * 1000))
filtered_df = pd.concat([temp_df, ml_df])
if product_size_operator == '<':
filtered_df = filtered_df[filtered_df['size'] < product_size]
elif product_size_operator == '<=':
filtered_df = filtered_df[filtered_df['size'] <= product_size]
elif product_size_operator == '>':
filtered_df = filtered_df[filtered_df['size'] > product_size]
elif product_size_operator == '>=':
filtered_df = filtered_df[filtered_df['size'] >= product_size]
elif product_size_operator == '=':
filtered_df = filtered_df[filtered_df['size'] == product_size]
# multipack conditions is an or between product size and MPACK
if multipack_template != "":
filtered_df = pd.concat([filtered_df,
multipack_df]).drop_duplicates()
filters = self.get_filters_from_row(row)
count_of_units = 0
if count_type == Const.SCENE:
count_of_units = self.count_of_scenes(filtered_df, filters,
target_operator, target)
elif count_type == Const.FACING:
count_of_units = self.count_of_facings(filtered_df, filters,
consider_few, target)
elif count_type == Const.SCENE_SOS:
count_of_units = self.count_of_sos(filtered_df, filters)
else:
Log.warning("Couldn't find a correct COUNT variable in template")
if target_operator == '<=':
count_result = 1 if (target <= count_of_units) else 0
# use for getting numeric results instead of 1 and 0
elif target_operator == '+':
if isinstance(count_of_units, (int, float, long)):
count_result = count_of_units
else:
count_result = len(count_of_units)
else:
count_result = 1 if (target >= count_of_units) else 0
return count_of_units, target, count_result
def get_filters_from_row(self, row):
"""
handle filters appering in scif
:param row: row containing all filters
:return: a dictionary of the filters
"""
filters = dict(row)
# no need to be accounted for, fields that aren't in scif
for field in Const.DELETE_FIELDS:
if field in filters:
del filters[field]
if Const.WEIGHT in filters.keys():
del filters[Const.WEIGHT]
if Const.GROUP_KPI_NAME in filters.keys():
del filters[Const.GROUP_KPI_NAME]
exclude_manufacturer = filters[Const.EXCLUDE_MANUFACTURER].strip()
if exclude_manufacturer != "":
filters[Const.MANUFACTURER] = (exclude_manufacturer,
Const.EXCLUDE_FILTER)
del filters[Const.EXCLUDE_MANUFACTURER]
exclude_category = filters[Const.EXCLUDE_CATEGORY].strip()
if exclude_category != "":
filters[Const.CATEGORY] = (exclude_category, Const.EXCLUDE_FILTER)
del filters[Const.EXCLUDE_CATEGORY]
# filter all the empty cells
for key in filters.keys():
if (filters[key] == ""):
del filters[key]
elif isinstance(filters[key], tuple):
filters[key] = (filters[key][0].split(","), filters[key][1])
else:
filters[key] = filters[key].split(",")
filters[key] = [item.strip() for item in filters[key]]
return self.create_filters_according_to_scif(filters)
def create_filters_according_to_scif(self, filters):
"""
adjusting the template names to scif names
:param filters: only the scif filters in the template shape
:return: the filters dictionary
"""
convert_from_scif = {
Const.TEMPLATE_GROUP: 'template_group',
Const.TEMPLATE_NAME: 'template_name',
Const.BRAND: 'brand_name',
Const.CATEGORY: 'category',
Const.MANUFACTURER: 'manufacturer_name',
Const.PRODUCT_TYPE: 'product_type',
Const.MULTIPACK: 'MPAK'
}
for key in filters.keys():
filters[convert_from_scif[key]] = filters.pop(key)
return filters
def count_of_scenes(self, filtered_df, filters, target_operator, target):
"""
calculate the count of scene types
:param filtered_df: the first filtered (no scif filters) dataframe
:param filters: the scif filters
:param target_operator: the operation to do, + for returning a dataframe (used in group count)
:param target: the target
:return: dataframe for group counts +, number of scenes for all other functions
"""
scene_data = filtered_df[self.tools.get_filter_condition(
filtered_df, **filters)]
if target_operator == '+':
# filter by scene_id and by template_name (scene type)
scene_types_groupby = scene_data.groupby(
['template_name', 'scene_id'])['facings'].sum().reset_index()
number_of_scenes = scene_types_groupby[
scene_types_groupby['facings'] >= target]
else:
number_of_scenes = len(scene_data['scene_id'].unique())
return number_of_scenes
def count_of_sos(self, filtered_df, filters):
"""
calculating the share of shelf
:param filtered_df: the first filtered (no scif filters) dataframe
:param filters: the scif filters
:return: the number of different scenes answered the condition (hard coded 50%)
"""
scene_data = filtered_df[self.tools.get_filter_condition(
filtered_df, **filters)]
scene_data = scene_data.rename(columns={"facings": "facings_nom"})
# filter by scene_id and by template_name (scene type)
scene_types_groupby = scene_data.groupby(['template_name', 'scene_id'
])['facings_nom'].sum()
all_products_groupby = self.scif.groupby(['template_name', 'scene_id'
])['facings'].sum()
merge_result = pd.concat((scene_types_groupby, all_products_groupby),
axis=1,
join='inner').reset_index()
return len(merge_result[
merge_result['facings_nom'] >= merge_result['facings'] * 0.5])
def count_of_facings(self, filtered_df, filters, consider_few, target):
'''
calculate the count of facings
:param filtered_df: the first filtered (no scif filters) dataframe
:param filters: the scif filters
:param consider_few: in case there is a need to consider more then one brand
:param target: the target to pass
:return:
'''
facing_data = filtered_df[self.tools.get_filter_condition(
filtered_df, **filters)]
if consider_few != "":
facing_data_groupby = facing_data.groupby(['brand_name'
])['facings'].sum()
if len(facing_data_groupby[
facing_data_groupby >= target]) >= consider_few:
number_of_facings = facing_data['facings'].sum()
else:
number_of_facings = 0
else:
number_of_facings = facing_data['facings'].sum()
return number_of_facings
def get_new_kpi_static_data(self):
"""
This function extracts the static new KPI data (new tables) and saves it into one global data frame.
The data is taken from static.kpi_level_2.
"""
query = CCBRQueries.get_new_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
def write_to_db_result_new_tables(self,
fk,
numerator_id,
numerator_result,
result,
denominator_id=None,
denominator_result=None,
score=None):
"""
This function creates the result data frame of new rables KPI,
and appends the insert SQL query into the queries' list, later to be written to the DB.
"""
table = KPI_NEW_TABLE
attributes = self.create_attributes_dict_new_tables(
fk, numerator_id, numerator_result, denominator_id,
denominator_result, result, score)
query = insert(attributes, table)
self.kpi_results_new_tables_queries.append(query)
def create_attributes_dict_new_tables(self, kpi_fk, numerator_id,
numerator_result, denominator_id,
denominator_result, result, score):
"""
This function creates a data frame with all attributes needed for saving in KPI results new tables.
"""
attributes = pd.DataFrame(
[(kpi_fk, self.session_id, numerator_id, numerator_result,
denominator_id, denominator_result, result, score)],
columns=[
'kpi_level_2_fk', 'session_fk', 'numerator_id',
'numerator_result', 'denominator_id', 'denominator_result',
'result', 'score'
])
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_new_tables_queries)
self.rds_conn.disconnect_rds()
self.rds_conn.connect_rds()
cur = self.rds_conn.db.cursor()
delete_query = CCBRQueries.get_delete_session_results_query(
self.session_uid, self.session_id)
cur.execute(delete_query)
for query in insert_queries:
cur.execute(query)
self.rds_conn.db.commit()
self.rds_conn.disconnect_rds()
@staticmethod
def merge_insert_queries(insert_queries):
query_groups = {}
for query in insert_queries:
static_data, inserted_data = query.split('VALUES ')
if static_data not in query_groups:
query_groups[static_data] = []
query_groups[static_data].append(inserted_data)
merged_queries = []
for group in query_groups:
merged_queries.append('{0} VALUES {1}'.format(
group, ',\n'.join(query_groups[group])))
return merged_queries
class PNGJP_SAND2KpiQualitative_ToolBox(Consts):
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
EXCLUDE_FILTER = 0
INCLUDE_FILTER = 1
EXCLUDE_EMPTY = False
INCLUDE_EMPTY = True
EXCLUDE_IRRELEVANT = False
INCLUDE_IRRELEVANT = True
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.all_templates = self.data_provider[Data.ALL_TEMPLATES]
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_type = self.data_provider[Data.STORE_INFO][
StoreInfoConsts.STORE_TYPE].values[0]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.match_display_in_scene = self.get_match_display()
self.data_provider.probe_groups = self.get_probe_group(
self.data_provider.session_uid)
self.tools = PNGJP_SAND2GENERALToolBox(self.data_provider,
self.output,
rds_conn=self.rds_conn)
self.template_name = 'TemplateQualitative.xlsx'
self.TEMPLATE_PATH = os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data',
self.template_name)
self.template_data = parse_template(self.TEMPLATE_PATH, self.HIERARCHY)
self.golden_zone_data = parse_template(self.TEMPLATE_PATH,
self.GOLDEN_ZONE)
self.golden_zone_data_criteria = parse_template(
self.TEMPLATE_PATH, self.GOLDEN_ZONE_CRITERIA)
self.block_data = parse_template(self.TEMPLATE_PATH, self.BLOCK)
self.adjacency_data = parse_template(self.TEMPLATE_PATH,
self.ADJACENCY)
self.anchor_data = parse_template(self.TEMPLATE_PATH, self.ANCHOR)
self.perfect_execution_data = parse_template(self.TEMPLATE_PATH,
self.PERFECT_EXECUTION)
self.category_list_data = parse_template(self.TEMPLATE_PATH,
self.CATEGORY_LIST)
self.product_groups_data = parse_template(self.TEMPLATE_PATH,
self.PRODUCT_GROUP)
self._custom_templates = {}
self.scenes_types_for_categories = {}
self.kpi_static_data = self.get_kpi_static_data()
self.kpi_results_queries = []
self.kpi_results = {}
self.atomic_results = {}
self.categories = self.all_products[
ProductsConsts.CATEGORY_FK].unique().tolist()
self.display_types = [
'Aisle', 'Casher', 'End-shelf', 'Entrance', 'Island', 'Side-End',
'Side-net'
]
self.custom_scif_queries = []
self.session_fk = self.data_provider[Data.SESSION_INFO][
BasicConsts.PK].iloc[0]
self.block = Block(data_provider=self.data_provider,
rds_conn=self.rds_conn)
self.adjacency = Adjancency(data_provider=self.data_provider,
rds_conn=self.rds_conn)
self.fix_utf_space_problem()
self.kpi_scores = {}
@property
def rds_conn(self):
if not hasattr(self, '_rds_conn'):
self._rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
try:
pd.read_sql_query('select pk from probedata.session limit 1',
self._rds_conn.db)
except:
self._rds_conn.disconnect_rds()
self._rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
return self._rds_conn
@property
def _allowed_products(self):
return {
ProductsConsts.PRODUCT_TYPE:
[ProductTypeConsts.OTHER, ProductTypeConsts.EMPTY]
}
def get_template(self, name):
if name not in self._custom_templates.keys():
self._custom_templates[name] = parse_template(
self.TEMPLATE_PATH, name)
return self._custom_templates[name]
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 = PNGJP_SAND2Queries.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 = PNGJP_SAND2Queries.get_match_display(self.session_uid)
match_display = pd.read_sql_query(query, self.rds_conn.db)
match_display = match_display.merge(self.scene_info[[
SceneInfoConsts.SCENE_FK, SceneInfoConsts.TEMPLATE_FK
]],
on=SceneInfoConsts.SCENE_FK,
how='left')
match_display = match_display.merge(self.all_templates,
on=TemplatesConsts.TEMPLATE_FK,
how='left',
suffixes=['', '_y'])
return match_display
def get_probe_group(self, session_uid):
query = PNGJP_SAND2Queries.get_probe_group(session_uid)
probe_group = pd.read_sql_query(query, self.rds_conn.db)
return probe_group
def fix_utf_space_problem(self):
self.template_data['fixed KPI name'] = self.template_data[
'KPI name'].str.replace(' ', '')
self.golden_zone_data['fixed KPI name'] = self.golden_zone_data[
'KPI name'].str.replace(' ', '')
self.block_data['fixed KPI name'] = self.block_data[
'KPI name'].str.replace(' ', '')
self.adjacency_data['fixed KPI name'] = self.adjacency_data[
'KPI name'].str.replace(' ', '')
self.anchor_data['fixed KPI name'] = self.anchor_data[
'KPI name'].str.replace(' ', '')
self.perfect_execution_data[
'fixed KPI name'] = self.perfect_execution_data[
'KPI name'].str.replace(' ', '')
self.template_data['fixed KPI name'] = self.template_data[
'KPI name'].str.replace(' ', '')
self.kpi_static_data['fixed atomic_kpi_name'] = self.kpi_static_data[
'atomic_kpi_name'].str.replace(' ', '')
@log_runtime('Main Calculation')
def main_calculation(self):
"""
This function calculates the KPI results.
"""
for category in self.template_data['Category Name'].unique().tolist():
category = \
self.all_products[
self.all_products[ProductsConsts.CATEGORY_LOCAL_NAME].str.encode(
HelperConsts.UTF8) == category.encode(HelperConsts.UTF8)][
ProductsConsts.CATEGORY_LOCAL_NAME].values[0]
self.category_calculation(category)
# for kpi_set in self.template_data[self.SET_NAME].unique().tolist():
for kpi_set in [
'Golden Zone', 'Block', 'Adjacency', 'Perfect Execution',
'Anchor'
]:
self.write_to_db_result(score=None,
level=self.LEVEL1,
kpi_set_name=kpi_set)
kpi_set_fk = self.kpi_static_data.loc[
self.kpi_static_data['kpi_set_name'] == kpi_set][
KpsResults.KPI_SET_FK].values[0]
set_kpis = self.kpi_static_data.loc[
self.kpi_static_data['kpi_set_name'] ==
kpi_set]['kpi_name'].unique().tolist()
for kpi in set_kpis:
self.write_to_db_result(score=None,
level=self.LEVEL2,
kpi_set_fk=kpi_set_fk,
kpi_name=kpi)
def category_calculation(self, category):
self.calculation_per_entity(category)
self.category_aggregation_calculation(category)
def scene_type_not_exists(self, sfs):
result = True
session_scene_types = self.scif["template_name"].unique().tolist()
for session_scene_type in session_scene_types:
for sf in sfs:
if session_scene_type == sf:
return False
return result
def calculation_per_entity(self, category):
template_data = self.template_data[
self.template_data['Category Name'].str.encode(
HelperConsts.UTF8) == category.encode(HelperConsts.UTF8)]
filters = {ProductsConsts.CATEGORY_LOCAL_NAME: category}
for kpi in template_data['fixed KPI name'].unique().tolist():
entity_kpis = template_data.loc[
template_data['fixed KPI name'].str.encode(
HelperConsts.UTF8) == kpi.encode(HelperConsts.UTF8)]
entity_filters = filters
for p in xrange(len(entity_kpis)):
try:
score = threshold = result = None
params = entity_kpis.iloc[p]
set_name = params[self.SET_NAME]
kpi_type = params[self.KPI_TYPE]
scenes_filters = self.get_scenes_filters(params)
kpi_filters = dict(scenes_filters, **entity_filters)
if self.scene_type_not_exists(
scenes_filters['template_name']):
continue
if kpi_type == self.GOLDEN_ZONE:
kpi_params = self.golden_zone_data[
self.golden_zone_data['fixed KPI name'].str.encode(
HelperConsts.UTF8) == kpi.encode(
HelperConsts.UTF8)]
score, result, threshold = self.calculate_golden_zone(
kpi, kpi_filters, kpi_params)
elif kpi_type == self.BLOCK:
kpi_params = self.block_data[
self.block_data['fixed KPI name'].str.encode(
HelperConsts.UTF8) == kpi.encode(
HelperConsts.UTF8)]
score, result, threshold = self.calculate_block(
kpi, kpi_filters, kpi_params)
elif kpi_type == self.ANCHOR:
kpi_params = self.anchor_data[
self.anchor_data['fixed KPI name'].str.encode(
HelperConsts.UTF8) == kpi.encode(
HelperConsts.UTF8)]
score, result, threshold = self.calculate_anchor(
kpi, kpi_filters, kpi_params)
elif kpi_type == self.ADJACENCY:
kpi_params = self.adjacency_data[
self.adjacency_data['fixed KPI name'].str.encode(
HelperConsts.UTF8) == kpi.encode(
HelperConsts.UTF8)]
score, result, threshold = self.calculate_adjacency(
kpi, kpi_filters, kpi_params)
else:
Log.debug(
"KPI type '{}' is not supported".format(kpi_type))
continue
extra_data = self.get_extra_data_from_params(kpi_params)
self.kpi_scores.update({kpi: score})
self.write_result(score,
result,
threshold,
kpi,
category,
set_name,
template_data,
extra_data=extra_data)
except Exception as ex:
Log.warning("Exception:{} no score/result for '{}'".format(
ex.message, kpi_type))
def category_aggregation_calculation(self, category):
template_data = self.template_data[
(self.template_data['Category Name'].str.encode(HelperConsts.UTF8)
== category.encode(HelperConsts.UTF8))
& (self.template_data['Set Name'] == 'Perfect Execution')]
for kpi in template_data['fixed KPI name'].unique().tolist():
entity_kpis = template_data.loc[
template_data['fixed KPI name'].str.encode(
HelperConsts.UTF8) == kpi.encode(HelperConsts.UTF8)]
for p in xrange(len(entity_kpis)):
score = threshold = result = None
params = entity_kpis.iloc[p]
set_name = params[self.SET_NAME]
kpi_type = params[self.KPI_TYPE]
st = [
x.strip()
for x in params['Scene Types to Include'].split(",")
]
if self.scene_type_not_exists(st):
continue
if kpi_type == self.PERFECT_EXECUTION:
score, result, threshold = self.calculate_perfect_execution(
kpi)
self.write_result(score, result, threshold, kpi, category,
set_name, template_data)
def _get_filtered_products(self):
products = self.data_provider.products.copy()
filtered_products_fk = set(
products[ProductsConsts.PRODUCT_FK].tolist())
return {ProductsConsts.PRODUCT_FK: list(filtered_products_fk)}
def _get_ean_codes_by_product_group_id(self,
column_name=Consts.PRODUCT_GROUP_ID,
**params):
return self.product_groups_data[self.product_groups_data['Group Id'] ==
params[column_name].values[0].split('.')[0]]['Product EAN Code'].values[0]. \
split(self.SEPARATOR)
def _get_allowed_products(self, allowed):
allowed_products = set()
# allowed.setdefault(ProductsConsts.PRODUCT_TYPE, []).
# extend(self._allowed_products[ProductsConsts.PRODUCT_TYPE])
for key, value in allowed.items():
products = self.data_provider.products.copy()
allowed_bulk = set(products[self.tools.get_filter_condition(
products, **{key: value})][ProductsConsts.PRODUCT_FK].tolist())
allowed_products.update(allowed_bulk)
return {ProductsConsts.PRODUCT_FK: list(allowed_products)}
def check_bay(self, matches, probe_group, threshold, **filters):
relevant_bays = matches[(matches[ProductsConsts.PRODUCT_FK].isin(
filters[ProductsConsts.PRODUCT_FK]))
& (matches['probe_group_id'] == probe_group)]
relevant_bays['freq'] = relevant_bays.groupby(
MatchesConsts.BAY_NUMBER)[MatchesConsts.BAY_NUMBER].transform(
'count')
relevant_bays = relevant_bays[relevant_bays['freq'] >= threshold][
MatchesConsts.BAY_NUMBER].unique().tolist()
if relevant_bays:
relevant_bays.sort()
return {'left': relevant_bays[0], 'right': relevant_bays[-1]}
return {}
def get_scenes_filters(self, params):
filters = {}
if params[self.SCENE_TYPES_TO_INCLUDE]:
scene_types = params[self.SCENE_TYPES_TO_INCLUDE].split(
self.SEPARATOR)
template_names = []
for scene_type in scene_types:
template_names.append(scene_type)
if template_names:
filters[TemplatesConsts.TEMPLATE_NAME] = template_names
return filters
def write_to_db_result(self,
score,
level,
threshold=None,
level3_score=None,
**kwargs):
"""
This function creates the result data frame of every KPI (atomic KPI/KPI/KPI set),
and appends the insert SQL query into the queries' list, later to be written to the DB.
"""
attributes = self.create_attributes_dict(score, level, threshold,
level3_score, **kwargs)
if level == self.LEVEL1:
table = Consts.KPS_RESULT
elif level == self.LEVEL2:
table = Consts.KPK_RESULT
elif level == self.LEVEL3:
table = Consts.KPI_RESULT
else:
return
query = insert(attributes, table)
self.kpi_results_queries.append(query)
def create_attributes_dict(self,
score,
level,
threshold=None,
level3_score=None,
**kwargs):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
"""
if level == self.LEVEL1:
set_name = kwargs['kpi_set_name']
set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name']
== set_name][
KpsResults.KPI_SET_FK].values[0]
if score is not None:
attributes = pd.DataFrame(
[(set_name, self.session_uid, self.store_id,
self.visit_date.isoformat(), format(score,
'.2f'), set_fk)],
columns=[
KpsResults.KPS_NAME, KpiResults.SESSION_UID,
KpiResults.STORE_FK, KpiResults.VISIT_DATE,
KpsResults.SCORE_1, KpsResults.KPI_SET_FK
])
else:
attributes = pd.DataFrame(
[(set_name, self.session_uid, self.store_id,
self.visit_date.isoformat(), None, set_fk)],
columns=[
KpsResults.KPS_NAME, KpiResults.SESSION_UID,
KpiResults.STORE_FK, KpiResults.VISIT_DATE,
KpsResults.SCORE_1, KpsResults.KPI_SET_FK
])
elif level == self.LEVEL2:
kpi_name = kwargs['kpi_name']
kpi_set_fk = kwargs[KpsResults.KPI_SET_FK]
kpi_fk = \
self.kpi_static_data[(self.kpi_static_data['kpi_name'].str.encode(HelperConsts.UTF8) == kpi_name.encode(
HelperConsts.UTF8)) &
(self.kpi_static_data[KpsResults.KPI_SET_FK] == kpi_set_fk)][
KpiResults.KPI_FK].values[0]
attributes = pd.DataFrame(
[(self.session_uid, self.store_id, self.visit_date.isoformat(),
kpi_fk, kpi_name, score)],
columns=[
KpkResults.SESSION_UID, KpkResults.STORE_FK,
KpkResults.VISIT_DATE, KpiResults.KPI_FK,
KpkResults.KPK_NAME, KpiResults.SCORE
])
self.kpi_results[kpi_name] = score
elif level == self.LEVEL3:
kpi_name = kwargs['kpi_name']
kpi_fk = self.kpi_static_data[
self.kpi_static_data['kpi_name'].str.encode(
HelperConsts.UTF8) == kpi_name.encode(HelperConsts.UTF8)][
KpiResults.KPI_FK].values[0]
atomic_kpi_name = kwargs['atomic_kpi_name']
atomic_kpi_fk = kwargs[KpiResults.ATOMIC_KPI_FK]
kpi_set_name = kwargs['kpi_set_name']
if level3_score is None and threshold is None:
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,
atomic_kpi_fk)],
columns=[
KpiResults.DISPLAY_TEXT, KpsResults.SESSION_UID,
KpsResults.KPS_NAME, KpsResults.STORE_FK,
KpsResults.VISIT_DATE, KpiResults.CALCULATION_TIME,
KpiResults.RESULT, KpiResults.KPI_FK,
KpiResults.ATOMIC_KPI_FK
])
elif level3_score is not None and threshold is None:
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,
level3_score, None, atomic_kpi_fk)],
columns=[
KpiResults.DISPLAY_TEXT, KpsResults.SESSION_UID,
KpsResults.KPS_NAME, KpsResults.STORE_FK,
KpsResults.VISIT_DATE, KpiResults.CALCULATION_TIME,
KpiResults.RESULT, KpiResults.KPI_FK, KpiResults.SCORE,
KpiResults.THRESHOLD, KpiResults.ATOMIC_KPI_FK
])
elif level3_score is None and threshold is not None:
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, threshold,
None, atomic_kpi_fk)],
columns=[
KpiResults.DISPLAY_TEXT, KpsResults.SESSION_UID,
KpsResults.KPS_NAME, KpsResults.STORE_FK,
KpsResults.VISIT_DATE, KpiResults.CALCULATION_TIME,
KpiResults.RESULT, KpiResults.KPI_FK,
KpiResults.THRESHOLD, KpiResults.SCORE,
KpiResults.ATOMIC_KPI_FK
])
else:
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, threshold,
level3_score, atomic_kpi_fk)],
columns=[
KpiResults.DISPLAY_TEXT, KpsResults.SESSION_UID,
KpsResults.KPS_NAME, KpsResults.STORE_FK,
KpsResults.VISIT_DATE, KpiResults.CALCULATION_TIME,
KpiResults.RESULT, KpiResults.KPI_FK,
KpiResults.THRESHOLD, KpiResults.SCORE,
KpiResults.ATOMIC_KPI_FK
])
if kpi_set_name not in self.atomic_results.keys():
self.atomic_results[kpi_set_name] = {}
self.atomic_results[kpi_set_name][atomic_kpi_name] = score
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 = PNGJPQueries.get_delete_session_results_query(self.session_uid)
# for query in delete_queries:
# cur.execute(query)
queries = self.merge_insert_queries(self.kpi_results_queries)
for query in queries:
cur.execute(query)
self.rds_conn.db.commit()
def merge_insert_queries(self, 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:
for group_index in xrange(0, len(query_groups[group]), 10**4):
merged_queries.append('{0} VALUES {1}'.format(
group, ',\n'.join(
query_groups[group][group_index:group_index + 10**4])))
return merged_queries
def get_extra_data_from_params(self, params):
extra_df = {}
if 'Brand' in params.columns and params['Brand'].values[0] != "":
extra_df['brand'] = params['Brand'].values[0]
else:
extra_df['brand'] = 'XX'
if 'Product Group Id' in params.columns and params[
'Product Group Id'].values[0] != "":
extra_df['group'] = params['Product Group Id'].values[0]
elif 'Product Group Id;A' in params.columns and 'Product Group Id;B' in params.columns:
extra_df['group'] = "A-" + params['Product Group Id;A'].values[0] + ";B-" + \
params['Product Group Id;B'].values[0]
else:
extra_df['group'] = 'XX'
return extra_df
@kpi_runtime(kpi_desc='calculate_golden_zone', project_name='pngjp')
def calculate_golden_zone(self, kpi, kpi_filters, params):
kpi_filter = kpi_filters.copy()
assortment_entity = ProductsConsts.PRODUCT_EAN_CODE
if params[self.BRANDS].values[0]:
kpi_filter[ProductsConsts.BRAND_LOCAL_NAME] = params[
self.BRANDS].values[0]
total_group_skus = int(
self.tools.calculate_availability(**kpi_filter))
elif params[self.PRODUCT_GROUP_ID].values[0]:
product_eans = self._get_ean_codes_by_product_group_id(**params)
kpi_filter[assortment_entity] = product_eans
total_group_skus = int(
self.tools.calculate_availability(**kpi_filter))
else:
product_eans = params['Product EAN Code'].values[0].split(
self.SEPARATOR)
kpi_filter[assortment_entity] = product_eans
total_group_skus = int(
self.tools.calculate_availability(**kpi_filter))
result = int(
self.tools.calculate_linear_facings_on_golden_zone(
self.golden_zone_data_criteria, **kpi_filter))
score = 0
threshold = float(params[self.GROUP_GOLDEN_ZONE_THRESHOLD].values[0])
if total_group_skus:
score = 100 if (result / float(total_group_skus)) >= \
float(params[self.GROUP_GOLDEN_ZONE_THRESHOLD].values[0]) else 0
result = (result / float(total_group_skus)) * 100
return score, result, threshold
@kpi_runtime(kpi_desc='calculate_block', project_name='pngjp')
def calculate_block(self, kpi, kpi_filters, params):
allowed_products_filters = {}
threshold = 0
kpi_filter = kpi_filters.copy()
block_threshold = params['Threshold'].values[0]
if params[self.PRODUCT_GROUP_ID].values[0] is not None:
product_eans = self._get_ean_codes_by_product_group_id(**params)
kpi_filter[ProductsConsts.PRODUCT_EAN_CODE] = product_eans
if (params[self.ALLOWED_PRODUCT_GROUP_ID].values[0] is not None) and (
params[self.ALLOWED_PRODUCT_GROUP_ID].values[0] != ''):
product_eans = self._get_ean_codes_by_product_group_id(
column_name=self.ALLOWED_PRODUCT_GROUP_ID, **params)
allowed_products_filters[
ProductsConsts.PRODUCT_EAN_CODE] = product_eans
else:
allowed_products_filters = None
if params[self.VERTICAL].values[0] == 'Y':
block_result, num_of_shelves = self.tools.calculate_block_together(
vertical=True,
allowed_products_filters=allowed_products_filters,
minimum_block_ratio=float(block_threshold),
**kpi_filter)
score = 100 if block_result and num_of_shelves >= 3 else 0
result = 1 if block_result and num_of_shelves >= 3 else 0
else:
block_result = self.tools.calculate_block_together(
minimum_block_ratio=float(block_threshold),
allowed_products_filters=allowed_products_filters,
**kpi_filter)
score = 100 if block_result else 0
result = 1 if block_result else 0
return score, result, threshold
@kpi_runtime(kpi_desc='calculate_anchor', project_name='pngjp')
def calculate_anchor(self, kpi, kpi_filters, params):
score = result = threshold = 0
kpi_filter = kpi_filters.copy()
minimum_products = int(params['Minimum Products'].values[0])
params.pop('Minimum Products')
block_threshold = params['Threshold'].values[0]
params.pop('Threshold')
product_eans = self._get_ean_codes_by_product_group_id(**params)
kpi_filter[ProductsConsts.PRODUCT_EAN_CODE] = product_eans
allowed = {
ProductsConsts.PRODUCT_TYPE: [
ProductTypeConsts.OTHER, ProductTypeConsts.EMPTY,
ProductTypeConsts.IRRELEVANT
]
}
# allowed = params['allowed']
allowed_products = self._get_allowed_products(allowed)
filtered_products_all = self._get_filtered_products()
filter_products_after_exclude = {
ProductsConsts.PRODUCT_FK:
list(
set(filtered_products_all[ProductsConsts.PRODUCT_FK]) -
set(allowed_products[ProductsConsts.PRODUCT_FK]))
}
filtered_products_sub_group = params.copy().to_dict()
filtered_products_sub_group.update(kpi_filter)
separate_filters, relevant_scenes = self.tools.separate_location_filters_from_product_filters(
**filtered_products_sub_group)
for scene in relevant_scenes:
separate_filters.update({SceneInfoConsts.SCENE_FK: scene})
kpi_filter.update({SceneInfoConsts.SCENE_FK: scene})
block_result = self.tools.calculate_block_together(
minimum_block_ratio=float(block_threshold), **kpi_filter)
if block_result:
matches = self.tools.match_product_in_scene
relevant_probe_group = matches[matches[MatchesConsts.SCENE_FK]
== scene]
for probe_group in relevant_probe_group[
'probe_group_id'].unique().tolist():
relevant_bay = self.check_bay(
relevant_probe_group, probe_group, minimum_products,
**filter_products_after_exclude)
if not relevant_bay:
continue
for direction in ['left', 'right']:
separate_filters.update({
MatchesConsts.BAY_NUMBER:
relevant_bay[direction]
})
edge = self.tools.calculate_products_on_edge(
position=direction,
edge_population=filter_products_after_exclude,
min_number_of_shelves=2,
**separate_filters)
if edge[0] > 0:
score = 100
result = 1
break
return score, result, threshold
@kpi_runtime(kpi_desc='calculate_adjacency', project_name='pngjp')
def calculate_adjacency(self, kpi, kpi_filters, params):
score = result = threshold = 0
kpi_filter = kpi_filters.copy()
target = params['Threshold']
target = float(target.values[0])
a_target = params.get('Threshold A')
if not a_target.empty:
params.pop('Threshold A')
a_target = float(a_target.values[0])
b_target = params.get('Threshold B')
if not b_target.empty:
params.pop('Threshold B')
b_target = float(b_target.values[0])
group_a = {
ProductsConsts.PRODUCT_EAN_CODE:
self._get_ean_codes_by_product_group_id('Product Group Id;A',
**params)
}
group_b = {
ProductsConsts.PRODUCT_EAN_CODE:
self._get_ean_codes_by_product_group_id('Product Group Id;B',
**params)
}
# allowed_filter = self._get_allowed_products({ProductsConsts.PRODUCT_TYPE:
# ([self.EMPTY, self.IRRELEVANT], self.EXCLUDE_FILTER)})
allowed_filter = self._get_allowed_products({
ProductsConsts.PRODUCT_TYPE: [
ProductTypeConsts.IRRELEVANT, ProductTypeConsts.EMPTY,
ProductTypeConsts.OTHER
]
})
allowed_filter_without_other = self._get_allowed_products({
ProductsConsts.PRODUCT_TYPE:
[ProductTypeConsts.IRRELEVANT, ProductTypeConsts.EMPTY]
})
scene_filters = {
TemplatesConsts.TEMPLATE_NAME:
kpi_filter[TemplatesConsts.TEMPLATE_NAME]
}
filters, relevant_scenes = self.tools.separate_location_filters_from_product_filters(
**scene_filters)
for scene in relevant_scenes:
adjacency = self.tools.calculate_adjacency(
group_a, group_b, {SceneInfoConsts.SCENE_FK: scene},
allowed_filter, allowed_filter_without_other, a_target,
b_target, target)
if adjacency:
direction = params.get('Direction', 'All').values[0]
if direction == 'All':
score = result = adjacency
else:
# a = self.data_provider.products[
# self.tools.get_filter_condition(self.data_provider.products, **group_a)]
# [ProductsConsts.PRODUCT_FK].tolist()
#
# b = self.data_provider.products[
# self.tools.get_filter_condition(self.data_provider.products, **group_b)]
# [ProductsConsts.PRODUCT_FK].tolist()
# a = self.scif[self.scif[ProductsConsts.PRODUCT_FK].isin(a)]
# [ProductsConsts.PRODUCT_NAME].drop_duplicates()
# b = self.scif[self.scif[ProductsConsts.PRODUCT_FK].isin(b)]
# [ProductsConsts.PRODUCT_NAME].drop_duplicates()
edges_a = self.tools.calculate_block_edges(
minimum_block_ratio=a_target,
**dict(group_a,
allowed_products_filters=allowed_filter,
**{MatchesConsts.SCENE_FK: scene}))
edges_b = self.tools.calculate_block_edges(
minimum_block_ratio=b_target,
**dict(group_b,
allowed_products_filters=allowed_filter,
**{MatchesConsts.SCENE_FK: scene}))
if edges_a and edges_b:
if direction == 'Vertical':
if sorted(set(edges_a['shelfs'])) == sorted(set(edges_b['shelfs'])) and \
len(set(edges_a['shelfs'])) == 1:
score = result = 0
elif max(edges_a['shelfs']) <= min(
edges_b['shelfs']):
score = 100
result = 1
# elif max(edges_b['shelfs']) <= min(edges_a['shelfs']):
# score = 100
# result = 1
elif direction == 'Horizontal':
if set(edges_a['shelfs']).intersection(
edges_b['shelfs']):
extra_margin_a = (
edges_a['visual']['right'] -
edges_a['visual']['left']) / 10
extra_margin_b = (
edges_b['visual']['right'] -
edges_b['visual']['left']) / 10
edges_a_right = edges_a['visual'][
'right'] - extra_margin_a
edges_b_left = edges_b['visual'][
'left'] + extra_margin_b
edges_b_right = edges_b['visual'][
'right'] - extra_margin_b
edges_a_left = edges_a['visual'][
'left'] + extra_margin_a
if edges_a_right <= edges_b_left:
score = 100
result = 1
elif edges_b_right <= edges_a_left:
score = 100
result = 1
return score, result, threshold
def calculate_perfect_execution(self, kpi):
score = result = threshold = 0
tested_kpis = self.perfect_execution_data[
self.perfect_execution_data['fixed KPI name'].str.encode(
HelperConsts.UTF8) == kpi.encode(HelperConsts.UTF8)]
for i, tested_kpi in tested_kpis.iterrows():
try:
param_score = int(
self.kpi_scores[tested_kpi['KPI test name'].replace(
' ', '')])
except:
param_score = 0
if param_score == 100:
score = 100
result = 1
else:
score = result = 0
break
return score, result, threshold
def write_result(self,
score,
result,
threshold,
kpi,
category,
set_name,
template_data,
extra_data=None):
kpi_name = template_data.loc[
template_data['fixed KPI name'].str.encode(HelperConsts.UTF8) ==
kpi.encode(HelperConsts.UTF8)]['KPI name'].values[0]
if extra_data is not None:
brand = extra_data['brand']
group = extra_data['group']
kpi_name = self.KPI_FORMAT.format(
category=category.encode(HelperConsts.UTF8),
brand=brand.encode(HelperConsts.UTF8),
group=str(group),
question=kpi_name.encode(HelperConsts.UTF8))
else:
kpi_name = self.KPI_FORMAT.format(
category=category.encode(HelperConsts.UTF8),
brand='XX',
group='XX',
question=kpi_name.encode(HelperConsts.UTF8))
while ' ' in kpi_name:
kpi_name = kpi_name.replace(' ', ' ')
atomic_kpi_fk = \
self.kpi_static_data[
self.kpi_static_data['fixed atomic_kpi_name'].str.encode(HelperConsts.UTF8) == kpi.encode(
HelperConsts.UTF8)][
KpiResults.ATOMIC_KPI_FK].values[0]
if result is not None or score is not None:
if not kpi_name:
kpi_name = self.KPI_FORMAT.format(category=category)
if score is None and threshold is None:
self.write_to_db_result(score=result,
level=self.LEVEL3,
kpi_set_name=set_name,
kpi_name=category,
atomic_kpi_name=kpi_name,
atomic_kpi_fk=atomic_kpi_fk)
elif score is not None and threshold is None:
self.write_to_db_result(score=result,
level=self.LEVEL3,
level3_score=score,
kpi_set_name=set_name,
kpi_name=category,
atomic_kpi_name=kpi_name,
atomic_kpi_fk=atomic_kpi_fk)
elif score is None and threshold is not None:
self.write_to_db_result(score=result,
level=self.LEVEL3,
threshold=threshold,
kpi_set_name=set_name,
kpi_name=category,
atomic_kpi_name=kpi_name,
atomic_kpi_fk=atomic_kpi_fk)
else:
self.write_to_db_result(score=result,
level=self.LEVEL3,
level3_score=score,
threshold=threshold,
kpi_set_name=set_name,
kpi_name=category,
atomic_kpi_name=kpi_name,
atomic_kpi_fk=atomic_kpi_fk)
class BATRUAssortment:
def __init__(self):
self.parsed_args = _parse_arguments()
self.project = self.parsed_args.project
self.rds_conn = self.rds_connect
self.file_path = self.parsed_args.file
self.start_date = self.parsed_args.date
self.partial_update = self.parsed_args.update
self.store_data = self.get_store_data
self.all_products = self.get_product_data
self.current_top_skus = self.get_current_top_skus
self.stores = {}
self.products = {}
self.all_queries = []
if self.start_date is None:
self.current_date = datetime.now().date()
else:
self.current_date = datetime.strptime(self.start_date,
'%Y-%m-%d').date()
self.deactivate_date = self.current_date - timedelta(1)
self.activate_date = self.current_date
if self.partial_update in ('1', 'True', 'Yes', 'Y'):
self.partial_update = True
else:
self.partial_update = False
def upload_assortment(self):
"""
This is the main function of the assortment.
It does the validation and then upload the assortment.
:return:
"""
Log.debug("Parsing and validating the assortment template")
is_valid, invalid_inputs = self.p1_assortment_validator()
Log.info("Assortment upload is started")
self.upload_store_assortment_file()
if not is_valid:
Log.warning("Errors were found during the template validation")
if invalid_inputs[INVALID_STORES]:
Log.warning("The following stores don't exist in the DB: {}"
"".format(invalid_inputs[INVALID_STORES]))
if invalid_inputs[INVALID_PRODUCTS]:
Log.warning("The following products don't exist in the DB: {}"
"".format(invalid_inputs[INVALID_PRODUCTS]))
Log.info("Assortment upload is finished")
@property
def rds_connect(self):
self.rds_conn = PSProjectConnector(self.project,
DbUsers.CalculationEng)
try:
pd.read_sql_query('select pk from probedata.session limit 1',
self.rds_conn.db)
except Exception as e:
self.rds_conn.disconnect_rds()
self.rds_conn = PSProjectConnector(self.project,
DbUsers.CalculationEng)
return self.rds_conn
@property
def get_store_data(self):
query = "select pk as store_fk, store_number_1 as store_number from static.stores"
self.store_data = pd.read_sql_query(query, self.rds_conn.db)
return self.store_data
@property
def get_product_data(self):
query = "select pk as product_fk, product_ean_code from static.product " \
"where delete_date is null"
self.all_products = pd.read_sql_query(query, self.rds_conn.db)
return self.all_products
@property
def get_current_top_skus(self):
query = """select store_fk, product_fk
from pservice.custom_osa
where end_date is null"""
data = pd.read_sql_query(query, self.rds_conn.db)
return data
def p1_assortment_validator(self):
"""
This function validates the store assortment template.
It compares the OUTLET_ID (= store_number_1) and the products ean_code to the stores and products from the DB
:return: False in case of an error and True in case of a valid template
"""
raw_data = self.parse_assortment_template()
legal_template = True
invalid_inputs = {INVALID_STORES: [], INVALID_PRODUCTS: []}
valid_stores = self.store_data.loc[
self.store_data['store_number'].isin(raw_data[OUTLET_ID])]
if len(valid_stores) != len(raw_data[OUTLET_ID].unique()):
invalid_inputs[INVALID_STORES] = list(
set(raw_data[OUTLET_ID].unique()) -
set(valid_stores['store_number']))
Log.debug("The following stores don't exist in the DB: {}".format(
invalid_inputs[INVALID_STORES]))
legal_template = False
valid_product = self.all_products.loc[self.all_products[EAN_CODE].isin(
raw_data[EAN_CODE])]
if len(valid_product) != len(raw_data[EAN_CODE].unique()):
invalid_inputs[INVALID_PRODUCTS] = list(
set(raw_data[EAN_CODE].unique()) -
set(valid_product[EAN_CODE]))
Log.debug(
"The following products don't exist in the DB: {}".format(
invalid_inputs[INVALID_PRODUCTS]))
legal_template = False
return legal_template, invalid_inputs
def parse_assortment_template(self):
"""
This functions turns the csv into DF
It tries to handle all of the possible format situation that I encountered yet (different delimiter and unicode)
:return: DF that contains the store_number_1 (Outlet ID) and the product_ean_code of the assortments
"""
data = pd.read_csv(self.file_path, sep='\t')
if OUTLET_ID not in data.columns or EAN_CODE not in data.columns:
data = pd.read_csv(self.file_path)
if OUTLET_ID not in data.columns or EAN_CODE not in data.columns:
data = pd.read_csv(self.file_path, encoding='utf-7')
data = data.drop_duplicates(subset=data.columns, keep='first')
data = data.fillna('')
return data
def set_end_date_for_irrelevant_assortments(self, stores_list):
"""
This function sets an end_date to all of the irrelevant stores in the assortment.
:param stores_list: List of the stores from the assortment template
"""
Log.debug("Closing assortment for stores out of template")
irrelevant_stores = self.store_data.loc[
~self.store_data['store_number'].
isin(stores_list)]['store_fk'].unique().tolist()
current_assortment_stores = self.current_top_skus['store_fk'].unique(
).tolist()
stores_to_remove = list(
set(irrelevant_stores).intersection(
set(current_assortment_stores)))
for store in stores_to_remove:
query = [
self.get_store_deactivation_query(store, self.deactivate_date)
]
self.commit_results(query)
Log.debug("Assortment is closed for ({}) stores".format(
len(stores_to_remove)))
def upload_store_assortment_file(self):
raw_data = self.parse_assortment_template()
data = []
list_of_stores = raw_data[OUTLET_ID].unique().tolist()
if not self.partial_update:
self.set_end_date_for_irrelevant_assortments(list_of_stores)
Log.debug("Preparing assortment data for update")
store_counter = 0
for store in list_of_stores:
store_data = {}
store_products = raw_data.loc[raw_data[OUTLET_ID] ==
store][EAN_CODE].tolist()
store_data[store] = store_products
data.append(store_data)
store_counter += 1
if store_counter % 1000 == 0 or store_counter == len(
list_of_stores):
Log.debug("Assortment is prepared for {}/{} stores".format(
store_counter, len(list_of_stores)))
Log.debug("Updating assortment data in DB")
store_counter = 0
for store_data in data:
self.update_db_from_json(store_data)
if self.all_queries:
queries = self.merge_insert_queries(self.all_queries)
self.commit_results(queries)
self.all_queries = []
store_counter += 1
if store_counter % 1000 == 0 or store_counter == len(data):
Log.debug(
"Assortment is updated in DB for {}/{} stores".format(
store_counter, len(data)))
@staticmethod
def merge_insert_queries(queries):
"""
This function aggregates all of the insert queries
:param queries: all of the queries (update and insert) for the assortment
:return: The merged insert queries
"""
query_groups = {}
other_queries = []
for query in queries:
if 'VALUES' not in query:
other_queries.append(query)
continue
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:
for group_index in xrange(0, len(query_groups[group]), 10**4):
merged_queries.append('{0} VALUES {1}'.format(
group, ',\n'.join(
query_groups[group][group_index:group_index + 10**4])))
return other_queries + merged_queries
def update_db_from_json(self, data):
update_products = set()
missing_products = set()
store_number = data.keys()[0]
if store_number is None:
Log.debug("'{}' column or value is missing".format(STORE_NUMBER))
return
store_fk = self.get_store_fk(store_number)
if store_fk is None:
Log.debug(
'Store Number {} does not exist in DB'.format(store_number))
return
for key in data[store_number]:
validation = False
if isinstance(key, (float, int)):
validation = True
elif isinstance(key, (str, unicode)):
validation = True
if validation:
product_ean_code = str(key).split(',')[-1]
product_fk = self.get_product_fk(product_ean_code)
if product_fk is None:
missing_products.add(product_ean_code)
else:
update_products.add(product_fk)
if missing_products:
Log.debug(
'The following EAN Codes for Store Number {} do not exist in DB: {}.'
''.format(store_number, list(missing_products)))
queries = []
current_products = self.current_top_skus[
self.current_top_skus['store_fk'] ==
store_fk]['product_fk'].tolist()
products_to_deactivate = tuple(
set(current_products).difference(update_products))
products_to_activate = tuple(
set(update_products).difference(current_products))
if products_to_deactivate:
if len(products_to_deactivate) == 1:
queries.append(
self.get_deactivation_query(
store_fk, "(" + str(products_to_deactivate[0]) + ")",
self.deactivate_date))
else:
queries.append(
self.get_deactivation_query(store_fk,
tuple(products_to_deactivate),
self.deactivate_date))
for product_fk in products_to_activate:
queries.append(
self.get_activation_query(store_fk, product_fk,
self.activate_date))
self.all_queries.extend(queries)
Log.debug(
'Store Number {} - Products to update {}: Deactivated {}, Activated {}'
''.format(store_number, len(update_products),
len(products_to_deactivate), len(products_to_activate)))
def get_store_fk(self, store_number):
"""
This functions returns the store's fk
:param store_number: 'store_number_1' attribute of the store
:return: store fk
"""
store_number = str(store_number)
if store_number in self.stores:
store_fk = self.stores[store_number]
else:
store_fk = self.store_data[self.store_data['store_number'] ==
store_number]
if not store_fk.empty:
store_fk = store_fk['store_fk'].values[0]
self.stores[store_number] = store_fk
else:
store_fk = None
return store_fk
def get_product_fk(self, product_ean_code):
product_ean_code = str(product_ean_code).strip()
if product_ean_code in self.products:
product_fk = self.products[product_ean_code]
else:
product_fk = self.all_products[
self.all_products['product_ean_code'] == product_ean_code]
if not product_fk.empty:
product_fk = product_fk['product_fk'].values[0]
self.products[product_ean_code] = product_fk
else:
product_fk = None
return product_fk
@staticmethod
def get_deactivation_query(store_fk, product_fks, date):
query = \
"""
update {} set end_date = '{}', is_current = NULL
where store_fk = {} and product_fk in {} and end_date is null;
"""\
.format(STORE_ASSORTMENT_TABLE, date, store_fk, product_fks)
return query
@staticmethod
def get_store_deactivation_query(store_fk, date):
query = \
"""
update {} set end_date = '{}', is_current = NULL
where store_fk = {} and end_date is null;
""".format(STORE_ASSORTMENT_TABLE, date, store_fk)
return query
@staticmethod
def get_activation_query(store_fk, product_fk, date):
attributes = pd.DataFrame(
[(store_fk, product_fk, str(date), 1)],
columns=['store_fk', 'product_fk', 'start_date', 'is_current'])
query = insert(attributes.to_dict(), STORE_ASSORTMENT_TABLE)
return query
def commit_results(self, queries):
"""
This function commits the results into the DB in batches.
query_num is the number of queires that were executed in the current batch
After batch_size is reached, the function re-connects the DB and cursor.
"""
self.rds_conn.connect_rds()
cursor = self.rds_conn.db.cursor()
batch_size = 1000
query_num = 0
failed_queries = []
for query in queries:
try:
cursor.execute(query)
# print query
except Exception as e:
Log.warning(
'Committing to DB failed to due to: {}. Query: {}'.format(
e, query))
self.rds_conn.db.commit()
failed_queries.append(query)
self.rds_conn.connect_rds()
cursor = self.rds_conn.db.cursor()
continue
if query_num > batch_size:
self.rds_conn.db.commit()
self.rds_conn.connect_rds()
cursor = self.rds_conn.db.cursor()
query_num = 0
query_num += 1
self.rds_conn.db.commit()
class CczaTemplateValidator(Main_Template):
def __init__(self, project_name, file_url=DEFAULT_PATH):
Main_Template.__init__(self)
self.project = project_name
self.template_path = file_url
self.rds_conn = self.rds_connect
self.store_data = self.get_store_data()
self.store_types_db = self.store_data[Const.ATTR3].values.tolist()
# self.all_products = self.get_product_data
self.kpi_sheets = {}
self.kpis_lvl2 = self.get_kpis_new_tables()
self.kpis_old = self.get_kpis_old_tables()
self.db_static_data = {}
self.get_static_db_table_contents()
self.type_function_map = self.map_validation_function_to_valid_type()
def get_static_db_table_contents(self):
# all_tables = map(lambda y: '{}.{}'.format(y[0], y[1]), map(lambda x: x.split('.'),
# Parameters.TYPE_DB_MAP.values()))
table_data = {}
for entity, table in Parameters.TYPE_DB_MAP.items():
table_col = table.split('.')
table_name = '{}.{}'.format(table_col[0], table_col[1])
if table_data.get(table_name) is None:
query = """ select * from {} """.format(table_name)
table_contents = pd.read_sql_query(query, self.rds_conn.db)
table_data.update({table_name: table_contents})
else:
table_contents = table_data.get(table_name)
self.db_static_data[entity] = table_contents[table_col[-1]]
for templ_column, table in Parameters.COLUMN_DB_MAP.items():
table_col = table.split('.')
table_name = '{}.{}'.format(table_col[0], table_col[1])
if table_data.get(table_name) is None:
query = """ select * from {} """.format(table_name)
table_contents = pd.read_sql_query(query, self.rds_conn.db)
table_data.update({table_name: table_contents})
else:
table_contents = table_data.get(table_name)
self.db_static_data[templ_column] = table_contents[table_col[-1]]
def get_kpis_new_tables(self):
query = """select * from static.kpi_level_2 """
kpis = pd.read_sql_query(query, self.rds_conn.db)
return kpis
def get_kpis_old_tables(self):
query = """select api.name as atomic_kpi_name, api.pk as atomic_kpi_fk,
kpi.display_text as kpi_name, kpi.pk as kpi_fk,
kps.name as kpi_set_name, kps.pk as kpi_set_fk
from static.atomic_kpi api
left join static.kpi kpi on kpi.pk = api.kpi_fk
join static.kpi_set kps on kps.pk = kpi.kpi_set_fk
where kps.name = "Red Score" """
kpis = pd.read_sql_query(query, self.rds_conn.db)
return kpis
@property
def rds_connect(self):
self.rds_conn = PSProjectConnector(self.project, DbUsers.Ps)
try:
pd.read_sql_query('select pk from probedata.session limit 1',
self.rds_conn.db)
except Exception as e:
self.rds_conn.disconnect_rds()
self.rds_conn = PSProjectConnector(self.project, DbUsers.Ps)
return self.rds_conn
def get_store_data(self):
query = """select * from static.stores where is_active="Y" and delete_date is null and test_store != "Y" """
store_data = pd.read_sql_query(query, self.rds_conn.db)
return store_data
# @property
# def get_product_data(self):
# query = "select * from static.product"
# self.all_products = pd.read_sql_query(query, self.rds_conn.db)
# return self.store_data
def validate_template_data(self):
try:
self.errorHandler.log_info('Checking tabs and columns')
self.check_all_tabs_exist_and_have_relevant_columns()
except Exception as e:
self.errorHandler.log_error(
'Unhandled error in format checking: {}'.format(e))
try:
self.errorHandler.log_info('Checking store_types')
self.check_store_types_and_extra_columns()
except Exception as e:
self.errorHandler.log_error(
'Unhandled error in store type validation: {}'.format(e))
try:
self.errorHandler.log_info('Checking sheets data')
self.check_kpis_sheets()
except Exception as e:
self.errorHandler.log_error(
'Unhandled error in sheet data validation: {}'.format(e))
error_file_link = self.dump_logs_to_file_and_upload_to_bucket()
print error_file_link
return
def dump_logs_to_file_and_upload_to_bucket(self):
file_path = self.errorHandler.dump_to_file()
error_file_link = Utilities.upload_file_to_s3(
file_path, '{}.log'.format(self.execution_unique_id),
os.path.join(Parameters.BUCKET_FOLDER,
Config.get_environment().upper(),
self.project.upper()), Parameters.LOG_FILE_BUCKET)
return error_file_link
def check_kpis_sheets(self):
for sheet, template_df in self.kpi_sheets.items():
self.errorHandler.log_info('Validating Sheet: {}'.format(sheet))
self.errorHandler.log_info('Checking configurable validations')
self.perform_configurable_validations(sheet, template_df)
self.errorHandler.log_info('Checking additional validations')
self.perform_additional_validations(sheet, template_df)
def perform_configurable_validations(self, sheet, template_df):
columns_to_validate = filter(
lambda x: x in Parameters.SHEETS_COL_MAP[sheet],
template_df.columns.values)
for templ_column in columns_to_validate:
self.errorHandler.log_info(
'Validating sheet {}, column: {}'.format(sheet, templ_column))
validation_params = Parameters.SHEETS_COL_VALID_TYPE[Parameters.ALL][templ_column] if \
templ_column in Parameters.SHEETS_COL_VALID_TYPE[Parameters.ALL].keys() else \
Parameters.SHEETS_COL_VALID_TYPE[sheet][templ_column]
if validation_params.get('filter_out'):
filtering_param = validation_params.get('filter_out')
template_df = template_df[~(
template_df[templ_column].isin(filtering_param))]
self.validate_empty(sheet, template_df, templ_column,
validation_params)
self.validate_particular_values(sheet, template_df, templ_column,
validation_params)
def validate_particular_values(self, sheet, template_df, templ_column,
validation_params):
val_types = validation_params.get('type')
val_sources = validation_params.get('source')
if val_types is not None:
for i in range(len(val_types)):
val_type = val_types[i]
val_source = val_sources[i]
self.type_function_map[val_type](sheet, template_df,
templ_column, val_source)
def check_value_based_on_type(self, sheet, template_df, templ_column,
val_source):
type_source_col = val_source['column']
empty_type_records = template_df[
(template_df[type_source_col] == '') |
(template_df[type_source_col].isnull())]
if len(empty_type_records) > 0:
self.errorHandler.log_error(
'Sheet: {}. Column: {}. Entity types are not completed for kpis: '
'{}'.format(sheet, type_source_col,
empty_type_records[Const.ATOMIC_NAME].values))
sheet_df = template_df[[type_source_col, templ_column]]
sheet_df = sheet_df[(~(sheet_df[type_source_col].isnull()))
& (~(sheet_df[type_source_col] == ''))]
sheet_df = sheet_df[(~(sheet_df[templ_column].isnull()))
& (~(sheet_df[templ_column] == ''))]
# handle double entity
double_entity_df = sheet_df[sheet_df[type_source_col].str.contains(
',')]
if not double_entity_df.empty:
missing_values_df = double_entity_df[~(
double_entity_df[templ_column].str.contains(','))]
if len(missing_values_df) > 0:
self.errorHandler.log_error(
'Sheet {}. One of multiple values is missing '
'in column {}'.format(sheet, templ_column))
double_entity_df = double_entity_df[
double_entity_df[templ_column].str.contains(',')]
duplicate_df = double_entity_df.copy()
double_entity_df[type_source_col] = double_entity_df[
type_source_col].apply(lambda x: x.split(',')[0].strip())
double_entity_df[templ_column] = double_entity_df[
templ_column].apply(lambda x: x.split(',')[0].strip())
duplicate_df[type_source_col] = duplicate_df[
type_source_col].apply(lambda x: x.split(',')[1].strip())
duplicate_df[templ_column] = duplicate_df[templ_column].apply(
lambda x: x.split(',')[1].strip())
joined_template_df = pd.concat([double_entity_df, duplicate_df])
self.check_value_based_on_type(sheet, joined_template_df,
templ_column, val_source)
# handle single entity
sheet_df = sheet_df[~(sheet_df[type_source_col].str.contains(','))]
sheet_df = sheet_df.sort_values(by=[type_source_col])
sheet_df['accumulated_values'] = sheet_df.groupby(type_source_col)[
templ_column].apply(lambda x: (x + ',').cumsum().str.strip())
sheet_df = sheet_df.drop_duplicates(subset=[type_source_col],
keep='last')
sheet_df = sheet_df.reset_index(drop=True)
for i, row in sheet_df.iterrows():
validated_entity = row[type_source_col]
template_values = row['accumulated_values'][0:-1].split(',')
template_values = set(map(lambda z: z.strip(), template_values))
db_col = val_source['db'][validated_entity].split('.')[-1]
db_values = set(self.db_static_data[validated_entity].values)
diff = template_values.difference(db_values)
if len(diff) > 0:
self.errorHandler.log_error(
'Sheet: {}, Column: {}, Entity: {} '
'does not match DB: {}'.format(sheet, templ_column,
validated_entity, diff))
def check_value_based_on_property(self, sheet, template_df, templ_column,
val_source):
prop_name, col = val_source.split('.')
# source_values = set(self.__getattribute__(prop_name)[col].values)
source_values = set(getattr(self, prop_name)[col].values)
template_values = filter(lambda x: x == x or x != '' or x is not None,
template_df[templ_column].values)
template_values = set(template_values)
diff = template_values.difference(source_values)
if len(diff) > 0:
self.errorHandler.log_error('{} values do not match {} of {} '
'in sheet {}: {}'.format(
templ_column, col, prop_name,
sheet, diff))
def check_db_values(self, sheet, template_df, templ_column, val_source):
db_col = val_source.split('.')[-1]
db_col_values = filter(lambda x: x == x and x != '' and x is not None,
self.db_static_data[templ_column].values)
db_col_values = set(db_col_values)
template_values = filter(
lambda x: x == x and x != '' and x is not None,
template_df[templ_column].values)
template_values = set(template_values)
diff = template_values.difference(db_col_values)
if len(diff) > 0:
try:
template_values1 = set(
map(lambda x: float(x), list(template_values)))
db_col_values1 = set(
map(lambda x: float(x), list(db_col_values)))
diff1 = template_values1.difference(db_col_values1)
if len(diff1) > 0:
self.errorHandler.log_error(
'Sheet {}: . Column: {}. Values do not match values of'
' column {} in DB table {}: '
'{}'.format(sheet, templ_column, db_col, val_source,
diff))
except Exception:
self.errorHandler.log_error(
'Sheet {}: . Column: {}. Values do not match values of'
' column {} in DB table {}: '
'{}'.format(sheet, templ_column, db_col, val_source, diff))
def validate_list(self, sheet, template_df, templ_column, val_source):
template_values = filter(
lambda x: x == x and x != '' and x is not None,
template_df[templ_column].values)
template_values_final = []
for raw_value in template_values:
values = map(lambda x: x.strip(), raw_value.split(','))
for value in values:
template_values_final.append(value)
template_values = set(template_values_final)
val_values = set(val_source)
diff = template_values.difference(val_values)
if len(diff) > 0:
self.errorHandler.log_error(
'Values in column {} in sheet {} do not match the allowed values {}: '
'{}'.format(templ_column, sheet, val_values, diff))
def validate_empty(self, sheet, template_df, templ_column,
validation_params):
if validation_params.get('disallow_empty'):
empty_values = filter(
lambda x: (x == '') or (x is None) or (x != x),
template_df[templ_column].values)
if len(empty_values) > 0:
self.errorHandler.log_error(
'Column {} in sheet {} has empty values'.format(
templ_column, sheet))
def perform_additional_validations(self, sheet, template_df):
if sheet == Const.KPIS:
self.validate_weights_in_kpis_sheet(sheet, template_df)
if sheet != Const.KPIS:
self.check_duplicate_atomics(sheet, template_df)
if sheet in Parameters.TARGET_SHEET_WEIGHT_SHEET_MAP.keys():
self.compare_weights_vs_targets_sheet(sheet, template_df)
if sheet in [
Const.LIST_OF_ENTITIES, Const.SOS_WEIGHTS,
Const.PRICING_WEIGHTS, Const.SURVEY_QUESTIONS
]:
self.check_weights_for_lvl2_kpis_add_to_100(sheet, template_df)
def check_weights_for_lvl2_kpis_add_to_100(self, sheet, template_df):
non_store_columns = list(
filter(lambda x: x in Parameters.SHEETS_COL_MAP[sheet],
template_df.columns.values))
non_store_columns.remove(Const.KPI_NAME)
template_df = template_df.drop(non_store_columns, axis=1)
groupby_dict = {}
store_col = filter(lambda x: x != Const.KPI_NAME,
template_df.columns.values)
template_df[store_col] = template_df[store_col].replace('', 0.0)
template_df[store_col] = template_df[store_col].astype(float)
for col in store_col:
groupby_dict.update({col: np.sum})
aggregate_df = template_df.groupby([Const.KPI_NAME],
as_index=False).agg(groupby_dict)
for i, row in aggregate_df.iterrows():
row_values = map(lambda x: round(x, 6), row[store_col].values)
all_100 = all(map(lambda x: x == 100 or x == 1, row_values))
if not all_100:
self.errorHandler.log_error(
'Sheet {}. KPI Name: {} . Not '
'all weights per stores add up to 100'.format(
sheet, row[Const.KPI_NAME]))
def compare_weights_vs_targets_sheet(self, target_sheet, targets_df):
weight_sheet = Parameters.TARGET_SHEET_WEIGHT_SHEET_MAP[target_sheet]
weights_df = self.kpi_sheets[weight_sheet]
self.compare_kpi_lists(target_sheet, targets_df, weight_sheet,
weights_df)
self.compare_weights_and_targets_store_sections(
target_sheet, targets_df, weight_sheet, weights_df)
def compare_weights_and_targets_store_sections(self, target_sheet,
targets_df, weight_sheet,
weights_df):
if Const.KPI_NAME in targets_df.columns.values and Const.ATOMIC_NAME in targets_df.columns.values:
targets_df = targets_df.sort_values(
[Const.KPI_NAME, Const.ATOMIC_NAME])
targets_df = targets_df.reset_index(drop=True)
atomics_ordered = targets_df[Const.ATOMIC_NAME].values
existing_store_types = filter(lambda x: x in self.store_types_db,
targets_df.columns.values)
target_stores_df = targets_df[existing_store_types]
target_stores_df = target_stores_df.sort_index(axis=1)
store_columns_t = target_stores_df.columns.values
target_values = target_stores_df.values
target_values = target_values.astype(np.bool)
weights_df = weights_df.sort_values(
[Const.KPI_NAME, Const.ATOMIC_NAME])
weights_df = weights_df.reset_index(drop=True)
existing_store_types = filter(lambda x: x in self.store_types_db,
weights_df.columns.values)
weight_stores_df = weights_df[existing_store_types]
weight_stores_df = weight_stores_df.sort_index(axis=1)
store_columns_w = weight_stores_df.columns.values
weight_values = weight_stores_df.values
weight_values = weight_values.astype(np.bool)
if np.array_equal(store_columns_t, store_columns_w
) and target_values.shape == weight_values.shape:
compare = np.isclose(target_values, weight_values)
compare_df = pd.DataFrame(compare, columns=store_columns_t)
if not compare.all():
self.errorHandler.log_error(
'weights and targets are not aligned in sheets'
' {} and {}'.format(target_sheet, weight_sheet))
for col in compare_df.columns.values:
store_values = compare_df[col].values
compare_col_res = store_values.all()
# compare_col_res = all(compare_df[col].values.tolist())
if not compare_col_res:
atomics = atomics_ordered[np.where(~store_values)]
self.errorHandler.log_error(
'Sheets: {} and {}. Fix weights or targets '
'for store {}: {}'.format(
weight_sheet, target_sheet, col, atomics))
else:
self.errorHandler.log_error(
'Stores or kpi lists are not the same in '
'sheets {} and {}'.format(target_sheet, weight_sheet))
def compare_kpi_lists(self, target_sheet, targets_df, weight_sheet,
weights_df):
weights_df = weights_df[[Const.KPI_NAME, Const.ATOMIC_NAME]]
weights_df.rename(columns={Const.KPI_NAME: 'KPI_Name_Weights'},
inplace=True)
targets_df = targets_df[[Const.KPI_NAME, Const.ATOMIC_NAME]]
targets_df.rename(columns={Const.KPI_NAME: 'KPI_Name_Targets'},
inplace=True)
validation_df = pd.merge(targets_df,
weights_df,
on=[Const.ATOMIC_NAME],
how='outer')
missing_weights = validation_df[
validation_df['KPI_Name_Targets'].isnull()]
if len(missing_weights) > 0:
atomics = missing_weights[Const.ATOMIC_NAME].values.tolist()
self.errorHandler.log_error(
'Sheet: {} has atomics that are missing '
'from Sheet {}: {}'.format(target_sheet, weight_sheet,
atomics))
missing_targets = validation_df[
validation_df['KPI_Name_Weights'].isnull()]
if len(missing_targets) > 0:
atomics = missing_weights[Const.ATOMIC_NAME].values.tolist()
self.errorHandler.log_error(
'Sheet: {} has atomics that are missing '
'from Sheet {}: {}'.format(weight_sheet, target_sheet,
atomics))
def check_duplicate_atomics(self, sheet, template_df):
atomics_df = template_df[[Const.ATOMIC_NAME]]
atomics_df['count'] = 1
atomics_df = atomics_df.groupby([Const.ATOMIC_NAME],
as_index=False).agg({'count': np.sum})
atomics_df = atomics_df[atomics_df['count'] > 1]
for i, row in atomics_df.iterrows():
self.errorHandler.log_error(
'Sheet {} has duplicate atomic kpi rows for '
'kpi {}'.format(sheet, row[Const.ATOMIC_NAME]))
def validate_weights_in_kpis_sheet(self, sheet, template_df):
# store_weights_df = template_df.drop(Parameters.SHEETS_COL_MAP[sheet], axis=1)
existing_store_types = filter(lambda x: x in self.store_types_db,
template_df.columns.values)
store_weights_df = template_df[existing_store_types]
store_weights = store_weights_df.values
try:
store_weights.astype(float)
except (ValueError, TypeError):
self.errorHandler.log_error(
'Sheet: {}. Not all weights in KPIs sheet are filled or '
'are numeric'.format(sheet))
total_weights = store_weights[store_weights.shape[0] -
1:store_weights.shape[0]][0].astype(
float)
validate_100 = all(map(lambda x: x == 100, total_weights))
if not validate_100:
self.errorHandler.log_error(
'Sheet: {}. Certain weights per store type are not equal to 100'
.format(sheet))
total_weights_summed = store_weights[0:-1].astype(float)
total_weights_summed = total_weights_summed.sum(axis=0)
validate_100 = all(map(lambda x: x == 100, total_weights_summed))
if not validate_100:
self.errorHandler.log_error(
'Sheet: {}. Certain weights per store type do not add up to 100'
.format(sheet))
def check_all_tabs_exist_and_have_relevant_columns(self):
for name in Const.sheet_names_and_rows:
try:
template_df = parse_template(
self.template_path,
sheet_name=name,
lower_headers_row_index=Const.sheet_names_and_rows[name])
columns = template_df.columns.values
columns = filter(lambda x: 'Unnamed' not in x, columns)
template_df = template_df[columns]
self.check_template_columns(name, columns)
self.kpi_sheets[name] = template_df
except Exception as e: # look up the type of exception in case sheet name is missing
self.errorHandler.log_error(
'Sheet {} is missing in the file'.format(name))
def check_template_columns(self, sheet, columns):
missing_columns = filter(lambda x: x not in columns,
Parameters.SHEETS_COL_MAP[sheet])
if len(missing_columns) > 0:
self.errorHandler.log_error('The following columns are missing '
'from sheet {}: {}'.format(
sheet, missing_columns))
def check_store_types_and_extra_columns(self):
for sheet, template_df in self.kpi_sheets.items():
store_types = set(template_df.columns.values).difference(
set(Parameters.SHEETS_COL_MAP[sheet]))
store_types_in_db = set(self.store_data[Const.ATTR3].values)
template_vs_db = store_types.difference(store_types_in_db)
if len(template_vs_db) > 0:
self.errorHandler.log_error(
'Sheet: {}. Store types {} exist in template but do not '
'exist in DB / or extra columns exist. Only data related to stores types '
'existing in DB will be further validated'.format(
sheet, template_vs_db))
db_vs_template = store_types_in_db.difference(store_types)
if len(db_vs_template) > 0:
self.errorHandler.log_error(
'Sheet: {}. Store types {} exist in db but do not '
'exist in the template / or extra columns exist'.format(
sheet, template_vs_db))
def map_validation_function_to_valid_type(self):
type_func_map = {
'db': self.check_db_values,
'list': self.validate_list,
'type_value': self.check_value_based_on_type,
'prop': self.check_value_based_on_property
}
return type_func_map
class AutoAssortmentHandler:
STORE_NUMBER = 'Store Number'
PRODUCT_EAN_CODE = 'Product EAN'
def __init__(self, rds_conn=None):
if rds_conn is not None:
self._rds_conn = rds_conn
self.stores = {}
self.products = {}
self.all_queries = []
self.update_queries = []
@property
def current_top_skus(self):
if not hasattr(self, '_current_top_skus'):
self._current_top_skus = self.get_current_top_skus()
return self._current_top_skus
@property
def rds_conn(self):
if not hasattr(self, '_rds_conn'):
self._rds_conn = PSProjectConnector(PROJECT,
DbUsers.CalculationEng)
try:
pd.read_sql_query('select pk from probedata.session limit 1',
self._rds_conn.db)
except:
self._rds_conn.disconnect_rds()
self._rds_conn = PSProjectConnector(PROJECT,
DbUsers.CalculationEng)
return self._rds_conn
@property
def store_data(self):
if not hasattr(self, '_store_data'):
query = "select pk as store_fk, store_number_1 as store_number from static.stores"
self._store_data = pd.read_sql_query(query, self.rds_conn.db)
return self._store_data
@property
def product_data(self):
if not hasattr(self, '_product_data'):
query = "select pk as product_fk, ean_code from static_new.product " \
"where delete_date is null".format(CORRELATION_FIELD)
self._product_data = pd.read_sql_query(query, self.rds_conn.db)
return self._product_data
def get_store_fk(self, store_number):
store_number = str(store_number)
if store_number in self.stores:
store_fk = self.stores[store_number]
else:
store_fk = self.store_data[self.store_data['store_number'] ==
store_number]
if not store_fk.empty:
store_fk = store_fk['store_fk'].values[0]
self.stores[store_number] = store_fk
else:
store_fk = None
return store_fk
def get_product_fk(self, product_ean_code):
product_ean_code = str(product_ean_code).strip()
if product_ean_code in self.products:
product_fk = self.products[product_ean_code]
else:
if product_ean_code.isdigit():
ean_to_check = int(product_ean_code)
else:
ean_to_check = product_ean_code
product_fk = self.product_data[self.product_data['ean_code'] ==
ean_to_check]
if not product_fk.empty:
product_fk = product_fk['product_fk'].values[0]
self.products[product_ean_code] = product_fk
else:
product_fk = None
return product_fk
def get_product_ean_code(self, product_fk):
product_fk = str(product_fk).strip()
if product_fk in self.products:
product_ean_code = self.products[product_fk]
else:
if product_fk.isdigit():
ean_to_check = int(product_fk)
else:
ean_to_check = product_fk
product_fk = self.product_data[self.product_data['product_fk'] ==
ean_to_check]
if not product_fk.empty:
product_ean_code = product_fk['ean_code'].values[0]
self.products[product_fk] = product_ean_code
else:
product_ean_code = None
return product_ean_code
def get_current_top_skus(self):
query = """select store_fk, product_fk
from pservice.custom_osa
where end_date is null"""
data = pd.read_sql_query(query, self.rds_conn.db)
return data
def get_current_assortment_per_store(self, store_fk, visit_date):
query = """select product_fk
from pservice.custom_osa
where end_date is null and store_fk = {} and start_date <= '{}'""".format(
store_fk, visit_date)
data = pd.read_sql_query(query, self.rds_conn.db)
return data['product_fk'].unique().tolist()
def update_db_from_json(self,
data,
immediate_change=False,
discard_missing_products=False):
products = set()
missing_products = set()
store_number = data.pop(self.STORE_NUMBER, None)
if store_number is None:
Log.warning("'{}' is required in data".format(self.STORE_NUMBER))
return
# store_fk = self.get_store_fk(store_number)
store_fk = store_number
if store_fk is None:
Log.warning(
'Store {} does not exist. Exiting...'.format(store_number))
return
for key in data.keys():
validation = False
# if not data[key]:
# validation = False
# elif isinstance(data[key], (float, int)) and data[key]:
# validation = True
# elif isinstance(data[key], (str, unicode)) and data[key].isdigit() and int(data[key]):
# validation = True
if data[key]:
if not key == 'date':
validation = True
if validation:
# product_ean_code = str(key).split(',')[-1]
product_fk = str(key).split(',')[-1]
# product_fk = self.get_product_fk(product_ean_code)
# product_ean_code = self.get_product_ean_code(product_fk)
if product_fk is None:
Log.warning(
'Product PK {} does not exist'.format(product_fk))
missing_products.add(product_fk)
continue
try:
products.add(int(product_fk))
except Exception as e:
continue
if missing_products and not discard_missing_products:
Log.warning('Some EANs do not exist: {}. Exiting...'.format(
'; '.join(missing_products)))
return
if products:
current_date = data['date']
if immediate_change:
deactivate_date = current_date
activate_date = current_date
else:
deactivate_date = current_date
activate_date = current_date
queries = []
current_skus = self.current_top_skus[
self.current_top_skus['store_fk'] ==
store_fk]['product_fk'].tolist()
products_to_deactivate = []
# products_to_deactivate = set(current_skus).difference(products)
products_to_activate = set(products).difference(current_skus)
# for product_fk in products_to_deactivate:
# if product_fk.isdigit():
# queries.append(self.get_deactivation_query(store_fk, product_fk, deactivate_date))
for product_fk in products_to_activate:
try:
if activate_date in self.stores_min_dates[product_fk,
store_fk]:
queries.append(
self.get_activation_query(store_fk, product_fk,
activate_date))
except Exception as e:
queries.append(
self.get_activation_query(store_fk, product_fk,
activate_date))
# self.commit_results(queries)
self.all_queries.extend(queries)
Log.debug(
'{} - Out of {} products, {} products were deactivated and {} products were activated'
.format(store_number,
len(products), len(products_to_deactivate),
len(products_to_activate)))
else:
Log.debug('{} - No products are configured as Top SKUs'.format(
store_number))
def upload_top_sku_file(self,
file_path,
data_first_cell,
ean_row_index,
store_number_column_index,
update_correlations=False):
raw_data = pd.read_csv(file_path)
raw_data = raw_data.fillna('')
data = []
self.stores_min_dates = {}
for index_data, store_raw_data in raw_data.iterrows():
# store_data = {self.STORE_NUMBER: index_data[store_number_column_index]}
store_data = {
self.STORE_NUMBER: store_raw_data['store_fk'],
'date': store_raw_data['date']
}
columns = list(store_raw_data.keys())
columns.remove('store_fk')
columns.remove('woy')
columns.remove('date')
try:
columns.remove('stores_name')
except Exception as e:
pass
for column in columns:
store_data[column] = store_raw_data[column]
if store_raw_data[column]:
if (column, store_raw_data['store_fk']
) in self.stores_min_dates.keys():
if datetime.strptime(
self.stores_min_dates[
column, store_raw_data['store_fk']],
"%Y-%m-%d") > datetime.strptime(
store_raw_data['date'], "%Y-%m-%d"):
self.stores_min_dates[
column,
store_raw_data['store_fk']] = store_raw_data[
'date']
else:
self.stores_min_dates[
column,
store_raw_data['store_fk']] = store_raw_data[
'date']
data.append(store_data)
if update_correlations:
self.update_correlations(data[0].keys())
for store_data in data:
self.update_db_from_json(store_data, immediate_change=True)
queries = self.merge_insert_queries(self.all_queries)
self.commit_results(queries)
return data
def update_correlations(self, products_data):
correlations = {}
for products in products_data:
products = str(products)
if products.count(','):
correlated_products = set()
products = products.split(',')
main_product = products.pop(-1).strip()
for product in products:
product_fk = self.get_product_fk(product)
if product_fk is not None:
correlated_products.add(product_fk)
if correlated_products:
correlations[main_product] = list(correlated_products)
if correlations:
queries = [self.get_delete_correlation_query()]
for product_ean_code in correlations:
queries.append(
self.get_correlation_query(product_ean_code,
correlations[product_ean_code]))
self.commit_results(queries)
delattr(self, '_product_data')
@staticmethod
def get_deactivation_query(store_fk, product_fk, date):
query = """update {} set end_date = '{}', is_current = NULL
where store_fk = {} and product_fk = {} and end_date is null""".format(
TOP_SKU_TABLE, date, store_fk, product_fk)
return query
def get_activation_query(self, store_fk, product_fk, date):
# if date in self.stores_min_dates[store_fk]:
attributes = pd.DataFrame(
[(store_fk, product_fk, str(date), None)],
columns=['store_fk', 'product_fk', 'start_date', 'is_current'])
# else:
# end_date = datetime.strptime(date, "%Y-%m-%d") + timedelta(7)
# attributes = pd.DataFrame([(store_fk, product_fk, str(date), None, str(end_date))],
# columns=['store_fk', 'product_fk', 'start_date', 'is_current', 'end_date'])
query = insert(attributes.to_dict(), TOP_SKU_TABLE)
return query
@staticmethod
def get_delete_correlation_query():
query = 'update static.product set {0} = null where {0} is not null'.format(
CORRELATION_FIELD)
return query
@staticmethod
def get_correlation_query(anchor_ean_code, correlated_products):
if len(correlated_products) == 1:
condition = 'pk = {}'.format(correlated_products[0])
else:
condition = 'pk in ({})'.format(tuple(correlated_products))
query = "update static.product set {} = '{}' where {}".format(
CORRELATION_FIELD, anchor_ean_code, condition)
return query
def commit_results(self, queries):
self.rds_conn.disconnect_rds()
rds_conn = PSProjectConnector(PROJECT, DbUsers.CalculationEng)
cur = rds_conn.db.cursor()
for query in self.update_queries:
print query
try:
cur.execute(query)
except Exception as e:
Log.debug('Inserting to DB failed due to: {}'.format(e))
rds_conn.disconnect_rds()
rds_conn = PSProjectConnector(PROJECT, DbUsers.CalculationEng)
cur = rds_conn.db.cursor()
continue
rds_conn.db.commit()
rds_conn.disconnect_rds()
rds_conn = PSProjectConnector(PROJECT, DbUsers.CalculationEng)
cur = rds_conn.db.cursor()
for query in queries:
print query
try:
cur.execute(query)
except Exception as e:
Log.debug('Inserting to DB failed due to: {}'.format(e))
rds_conn.disconnect_rds()
rds_conn = PSProjectConnector(PROJECT, DbUsers.CalculationEng)
cur = rds_conn.db.cursor()
continue
rds_conn.db.commit()
def get_top_skus_for_store(self, store_fk, visit_date):
query = """
select ts.product_fk, p.product_ean_code
from {} ts
join static.product p on p.pk = ts.product_fk
where ts.store_fk = {} and '{}' between ts.start_date and ifnull(ts.end_date, curdate())
""".format(TOP_SKU_TABLE, store_fk, visit_date)
data = pd.read_sql_query(query, self.rds_conn.db)
return data.groupby('product_fk')['product_ean_code'].first().to_dict()
def get_correlated_products(self, product_ean_code):
return self.product_data[self.product_data['correlation'] ==
product_ean_code]['product_fk'].tolist()
@staticmethod
def get_custom_scif_query(session_fk, scene_fk, product_fk, in_assortment,
distributed):
in_assortment = 1 if in_assortment else 0
out_of_stock = 1 if not distributed else 0
attributes = pd.DataFrame(
[(session_fk, scene_fk, product_fk, in_assortment, out_of_stock)],
columns=[
'session_fk', 'scene_fk', 'product_fk', 'in_assortment_osa',
'oos_osa'
])
query = insert(attributes.to_dict(), CUSTOM_SCIF_TABLE)
return query
def merge_insert_queries(self, insert_queries):
# other_queries = []
query_groups = {}
for query in insert_queries:
if 'update' in query:
self.update_queries.append(query)
else:
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:
for group_index in xrange(0, len(query_groups[group]), 10**4):
merged_queries.append('{0} VALUES {1}'.format(
group, ',\n'.join(
query_groups[group][group_index:group_index + 10**4])))
# merged_queries.extend(other_queries)
return merged_queries
def read_custom_query(self, query):
local_con = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
df = pd.read_sql_query(query, local_con.db)
local_con.disconnect_rds()
return df
class PURINAToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.session_info = self.data_provider[Data.SESSION_INFO]
self.session_fk = self.session_info['pk'].values[0]
self.kpi_results_queries = []
self.kpi_static_queries = []
self.purina_scif = self.scif.loc[self.scif['category_fk'] == PET_FOOD_CATEGORY]
def calculate_purina(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
if not self.is_session_purina():
return
# Update all new static KPIs
self.create_new_static_kpi()
self.kpi_static_data = self.common.get_kpi_static_data(refresh=True)
self.update_kpi_score()
self.run_data_collecting()
self.common.commit_results_data()
def update_kpi_score(self):
# Only to see results in join :(
for kpi in PURINA_KPI:
kpi_fk = self.get_kpi_fk_by_kpi_name(kpi, self.LEVEL2, set_name=PURINA_SET)
self.common.write_to_db_result(kpi_fk, self.LEVEL2, 1)
def run_data_collecting(self):
"""
This function run the man calculation of linear sos with sub category out of subsegment
or price out of subsegment
:param price_kpi:
:return:
"""
data = self.purina_scif.dropna(subset=[LINEAR_SIZE])
if data.empty:
Log.info("No relevant purina's products were found in session.")
return
# subseg_name_list = data[SCIF_SUBSEGMENT].unique()
# for subseg in subseg_name_list:
# if not subseg:
# subseg = NO_SUBSEG
# by_subseg = data.loc[pd.isnull(data[SCIF_SUBSEGMENT])]
# subseg_ft = self.cm_to_ft(sum(by_subseg[LINEAR_SIZE]))
# else:
# by_subseg = data.loc[data[SCIF_SUBSEGMENT] == subseg]
# subseg_ft = self.cm_to_ft(sum(by_subseg[LINEAR_SIZE]))
# atomic_fk = self.get_kpi_fk_by_kpi_name(subseg, self.LEVEL3, father=SUBSEGMENT_KPI, set_name=SUBSEGMENT_SET)
# self.common.old_write_to_db_result(fk=atomic_fk, level=self.LEVEL3, score=subseg_ft)
# atomic_fk = self.get_kpi_fk_by_kpi_name(subseg, self.LEVEL3, father=SUBSEGMENT_KPI, set_name=PRICE_SET)
# self.common.old_write_to_db_result(fk=atomic_fk, level=self.LEVEL3, score=subseg_ft)
# gets all category linear size
category_ft = self.cm_to_ft(sum(data[LINEAR_SIZE]))
fk = self.get_kpi_fk_by_kpi_name(PURINA_SET, self.LEVEL1)
self.common.write_to_db_result(fk, self.LEVEL1, category_ft)
man = data['manufacturer_name'].unique()
for mf in man:
by_mf = data.loc[data['manufacturer_name'] == mf]
manufacturer_ft = self.cm_to_ft(sum(by_mf[LINEAR_SIZE]))
relevant_kpi_fk = self.kpi_static_data.loc[(self.kpi_static_data['kpi_name'] == MANUFACTUR) &
(self.kpi_static_data['kpi_set_name'] == PURINA_SET)]['kpi_fk'].values[0]
atomic_fk = self.get_kpi_fk_by_kpi_name(mf, self.LEVEL3, father=MANUFACTUR, set_name=PURINA_SET)
if atomic_fk:
self.common.write_to_db_result(fk=atomic_fk, atomic_kpi_fk=atomic_fk, level=self.LEVEL3,
score=manufacturer_ft, score_2=manufacturer_ft,
session_uid=self.session_uid, store_fk=self.store_id,
display_text=mf.replace("'","''"),
visit_date=self.visit_date.isoformat(),
calculation_time=datetime.utcnow().isoformat(),
kps_name=PURINA_SET,
kpi_fk=relevant_kpi_fk)
else:
print 'atomic cannot be saved for manufacturer {}'.format(mf)
brands = by_mf['brand_name'].unique()
for brand in brands:
by_brand = by_mf.loc[data['brand_name'] == brand]
brand_ft = self.cm_to_ft(sum(by_brand[LINEAR_SIZE]))
kpi_fk = self.kpi_static_data.loc[(self.kpi_static_data['kpi_name'] == BRAND) &
(self.kpi_static_data['kpi_set_name'] == PURINA_SET)]['kpi_fk'].values[0]
atomic_fk = self.get_kpi_fk_by_kpi_name(brand, self.LEVEL3, father=BRAND, set_name=PURINA_SET)
if atomic_fk:
self.common.write_to_db_result(fk=atomic_fk, atomic_kpi_fk=atomic_fk, level=self.LEVEL3,
score=brand_ft, score_2=brand_ft, style=mf.replace("'","''"),
session_uid=self.session_uid, store_fk=self.store_id,
display_text=brand.replace("'","''"),
visit_date=self.visit_date.isoformat(),
calculation_time=datetime.utcnow().isoformat(),
kps_name=PURINA_SET,
kpi_fk=kpi_fk)
else:
print 'atomic cannot be saved for brand {}'.format(brand)
categories = by_brand[SCIF_CATEOGRY].unique()
for cat in categories:
if not cat:
cat = OTHER
by_cat = by_brand.loc[pd.isnull(by_brand[SCIF_PRICE])]
cat_ft = self.cm_to_ft(sum(by_cat[LINEAR_SIZE]))
else:
by_cat = by_brand.loc[data[SCIF_SUB_CATEOGRY] == cat]
cat_ft = self.cm_to_ft(sum(by_cat[LINEAR_SIZE]))
kpi_fk = self.kpi_static_data.loc[(self.kpi_static_data['kpi_name'] == CATEGORY) &
(self.kpi_static_data['kpi_set_name'] == PURINA_SET)]['kpi_fk'].values[0]
atomic_fk = self.get_kpi_fk_by_kpi_name(cat, self.LEVEL3, father=CATEGORY, set_name=PURINA_SET)
if atomic_fk:
self.common.write_to_db_result(fk=atomic_fk, atomic_kpi_fk=atomic_fk, level=self.LEVEL3,
score=cat_ft,
score_2=cat_ft, style=mf.replace("'","''"),
result=brand.replace("'","''"),
session_uid=self.session_uid, store_fk=self.store_id,
display_text=cat.replace("'","''"),
visit_date=self.visit_date.isoformat(),
calculation_time=datetime.utcnow().isoformat(),
kps_name=PURINA_SET,
kpi_fk=kpi_fk)
else:
print 'atomic cannot be saved for category {}'.format(cat)
sub_cats = by_cat[SCIF_SUB_CATEOGRY].unique()
for sub_cat in sub_cats:
if not sub_cat:
sub_cat = OTHER
by_sub_cat = by_cat.loc[pd.isnull(by_cat[SCIF_PRICE])]
sub_cat_ft = self.cm_to_ft(sum(by_sub_cat[LINEAR_SIZE]))
else:
by_sub_cat = by_cat.loc[data[SCIF_SUB_CATEOGRY] == sub_cat]
sub_cat_ft = self.cm_to_ft(sum(by_sub_cat[LINEAR_SIZE]))
# write to db under sub category atomic kpi score with brand name in results
kpi_fk = self.kpi_static_data.loc[(self.kpi_static_data['kpi_name'] == SUB_CATEGORY) &
(self.kpi_static_data['kpi_set_name'] == PURINA_SET)][
'kpi_fk'].values[0]
atomic_fk = self.get_kpi_fk_by_kpi_name(sub_cat, self.LEVEL3, father=SUB_CATEGORY,
set_name=PURINA_SET)
if atomic_fk:
self.common.write_to_db_result(fk=atomic_fk, atomic_kpi_fk=atomic_fk, level=self.LEVEL3,
score=sub_cat_ft,
score_2=sub_cat_ft, style=mf.replace("'","''"),
result=brand.replace("'","''"),
result_2=cat.replace("'","''"),
session_uid=self.session_uid, store_fk=self.store_id,
display_text=sub_cat.replace("'","''"),
visit_date=self.visit_date.isoformat(),
calculation_time=datetime.utcnow().isoformat(),
kps_name=PURINA_SET,
kpi_fk=kpi_fk)
else:
print 'atomic cannot be saved for sub category {}'.format(sub_cat)
prices = by_sub_cat[SCIF_PRICE].unique()
for price_class in prices:
if not price_class:
price_class = OTHER
by_prices = by_sub_cat.loc[pd.isnull(by_sub_cat[SCIF_PRICE])]
price_ft = self.cm_to_ft(sum(by_prices[LINEAR_SIZE]))
else:
by_prices = by_sub_cat.loc[by_sub_cat[SCIF_PRICE] == price_class]
price_ft = self.cm_to_ft(sum(by_prices[LINEAR_SIZE]))
kpi_fk = self.kpi_static_data.loc[(self.kpi_static_data['kpi_name'] == PRICE_KPI) &
(self.kpi_static_data['kpi_set_name'] == PURINA_SET)][
'kpi_fk'].values[0]
atomic_fk = self.get_kpi_fk_by_kpi_name(price_class, self.LEVEL3, father=PRICE_KPI,
set_name=PURINA_SET)
if atomic_fk:
self.common.write_to_db_result(fk=atomic_fk, atomic_kpi_fk=atomic_fk, level=self.LEVEL3,
score=price_ft,
score_2=price_ft, style=mf.replace("'","''"),
result=brand.replace("'","''"),
result_2=cat.replace("'","''"),
result_3=sub_cat.replace("'","''"),
session_uid=self.session_uid, store_fk=self.store_id,
display_text=price_class.replace("'", "''"),
visit_date=self.visit_date.isoformat(),
calculation_time=datetime.utcnow().isoformat(),
kps_name=PURINA_SET,
kpi_fk=kpi_fk )
else:
print 'atomic cannot be saved for price class {}'.format(price_class)
@staticmethod
def cm_to_ft(cm):
return cm / 30.48
def get_labels(self):
query = """select pk, labels, ean_code
from static_new.product
"""
labels = pd.read_sql_query(query, self.rds_conn.db)
return labels
def get_kpi_fk_by_kpi_name(self, kpi_name, kpi_level, father=None, logic_father=None, set_name=None):
if kpi_level == self.LEVEL1:
column_key = 'kpi_set_fk'
column_value = 'kpi_set_name'
father_value = 'kpi_set_name'
elif kpi_level == self.LEVEL2:
column_key = 'kpi_fk'
column_value = 'kpi_name'
father_value = 'kpi_set_name'
elif kpi_level == self.LEVEL3:
column_key = 'atomic_kpi_fk'
column_value = 'atomic_kpi_name'
father_value = 'kpi_name'
else:
raise ValueError('invalid level')
try:
relevant = self.kpi_static_data[self.kpi_static_data[column_value] == kpi_name]
if father:
relevant = relevant[relevant[father_value] == father]
if set_name:
relevant = relevant[relevant['kpi_set_name'] == set_name]
return relevant[column_key].values[0]
except IndexError:
Log.info('Kpi name: {}, isn\'t equal to any kpi name in static table'.format(kpi_name))
return None
def create_new_static_kpi(self):
# This functions takes all brands, sub categories, categories and manufacturers in session.
# The function adds them to database in case they are new.
brands = self.get_all_brands()
sub_cats = self.get_all_sub_categories()
manufacturer = self.get_all_manufacturers()
cats = self.get_all_categories()
prices = self.get_all_price_classes()
new_brands = self.purina_scif.loc[~self.purina_scif['brand_name'].isin(brands)]['brand_name'].unique()
new_manufacturer = self.purina_scif.loc[~self.purina_scif['manufacturer_name'].isin(manufacturer)][
'manufacturer_name'].unique()
new_sub_cat = self.purina_scif.loc[(~self.purina_scif[SCIF_SUB_CATEOGRY].isin(sub_cats)) &
(~pd.isnull(self.purina_scif[SCIF_SUB_CATEOGRY]))][SCIF_SUB_CATEOGRY].unique()
new_cat = self.purina_scif.loc[(~self.purina_scif[SCIF_CATEOGRY].isin(cats)) &
(~pd.isnull(self.purina_scif[SCIF_CATEOGRY]))][SCIF_CATEOGRY].unique()
new_prices = self.purina_scif.loc[(~self.purina_scif[SCIF_PRICE].isin(prices)) &
(~pd.isnull(self.purina_scif[SCIF_PRICE]))][SCIF_PRICE].unique()
self.save_static_atomics(BRAND, new_brands, PURINA_SET)
self.save_static_atomics(MANUFACTUR, new_manufacturer, PURINA_SET)
self.save_static_atomics(CATEGORY, new_cat, PURINA_SET)
self.save_static_atomics(SUB_CATEGORY, new_sub_cat, PURINA_SET)
self.save_static_atomics(PRICE_KPI, new_prices, PURINA_SET)
self.commit_static_data()
def get_all_brands(self):
return self.kpi_static_data.loc[self.kpi_static_data['kpi_name'] == BRAND]['atomic_kpi_name']
def get_all_sub_categories(self):
return self.kpi_static_data.loc[self.kpi_static_data['kpi_name'] == SUB_CATEGORY]['atomic_kpi_name']
def get_all_manufacturers(self):
return self.kpi_static_data.loc[self.kpi_static_data['kpi_name'] == MANUFACTUR]['atomic_kpi_name']
def get_all_categories(self):
return self.kpi_static_data.loc[self.kpi_static_data['kpi_name'] == CATEGORY]['atomic_kpi_name']
def get_all_price_classes(self):
return self.kpi_static_data.loc[self.kpi_static_data['kpi_name'] == PRICE_KPI]['atomic_kpi_name']
def save_static_atomics(self, kpi_name, atomics, set_name):
kpi_fk = self.kpi_static_data.loc[(self.kpi_static_data['kpi_name'] == kpi_name) &
(self.kpi_static_data['kpi_set_name'] == set_name)]['kpi_fk'].values[0]
for current in atomics:
current = current.replace("'", "''")
query = """
INSERT INTO {0} (`kpi_fk`, `name`, `description`, `display_text`,`presentation_order`, `display`)
VALUES ('{1}', '{2}', '{3}', '{4}', '{5}', '{6}');""".format(STATIC_ATOMIC,
kpi_fk, current, current, current, 1, 'Y')
self.kpi_static_queries.append(query)
def commit_static_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()
# ProjectConnector(self.project_name, DbUsers.CalculationEng)
cur = self.rds_conn.db.cursor()
for query in self.kpi_static_queries:
try:
cur.execute(query)
except Exception as e:
Log.info('query {} could not be executed.'.format(query))
self.rds_conn.db.commit()
self.rds_conn.disconnect_rds()
def is_session_purina(self):
# This function checks is the session is of Purina project by its category and that it is a successful visit.
session_data = self.get_session_category_data()
session_data = session_data.loc[(session_data['category_fk'] == 13) &
(session_data['resolution_fk'] == 1) &
(session_data['exclude_status_fk'] == 1)]
if not session_data.empty:
return True
return False
def get_session_category_data(self):
local_con = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
query = """select category_fk, resolution_fk, exclude_status_fk from probedata.session_category
where session_fk = {}""".format(self.session_fk)
data = pd.read_sql_query(query, local_con.db)
return data
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 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()