class Generator:
def __init__(self, data_provider, output):
self.data_provider = data_provider
self.output = output
self.project_name = data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.tool_box = NESTLEUSToolBox(self.data_provider, self.output)
self.common = Common(data_provider)
@log_runtime('Total Calculations', log_start=True)
def main_function(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
"""
if self.tool_box.scif.empty:
Log.warning('Scene item facts is empty for this session')
for kpi_set_fk in self.tool_box.kpi_static_data['kpi_set_fk'].unique(
).tolist():
score = self.tool_box.main_calculation(kpi_set_fk=kpi_set_fk)
self.common.write_to_db_result(kpi_set_fk, self.tool_box.LEVEL1,
score)
self.common.commit_results_data()
class INBEVTRADMXToolBox:
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.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.templates_path = os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data')
self.excel_file_path = os.path.join(self.templates_path,
'inbevtradmx_template.xlsx')
self.availability = Availability(self.data_provider)
self.survey_response = self.data_provider[Data.SURVEY_RESPONSES]
self.geo = GeoLocation.Geo(self.rds_conn, self.session_uid,
self.data_provider, self.kpi_static_data,
self.common)
def parse_template(self):
"""
convert excel file to data frame
:return: data frame
"""
template_df = pd.read_excel(self.excel_file_path, sheetname='template')
return template_df
def filter_product_names(self, exclude_skus):
"""
this method filters list of SKUs from self.scif
:param exclude_skus: list of SKUs to exclude from template
:return: filtered list
"""
return filter(lambda sku: sku not in exclude_skus,
self.scif.product_name.values)
def calculate_availability_score(self, row, relevant_columns):
"""
this method calculates availability score according to columns from the data frame
:param row: data frame to calculate from
:param relevant_columns: columns to check in the excel file
:return: boolean
"""
# create filtered dictionary
filters_dict = self.create_availability_filtered_dictionary(
relevant_columns, row)
# call the generic method from KPIUtils_v2
availability_score = self.availability.calculate_availability(
**filters_dict)
# check if this score should pass or fail
return self.decide_availability_score(row, availability_score)
def create_availability_filtered_dictionary(self, relevant_columns, row):
"""
this method creates a dictionary with keys according to the specific row in the template
:param relevant_columns: columns to create keys by
:param row: the specific row in the template
:return: dictionary
"""
# dictionary to send to the generic method
filters_dict = {}
self.handle_exclude_skus(filters_dict, relevant_columns, row)
# fill the dictionary
for column_value in relevant_columns:
if column_value == 'store Additional Attribute 4' or column_value == 'store_type':
continue
filters_dict[column_value] = map(
str.strip,
str(row.loc[column_value]).split(','))
return filters_dict
def handle_exclude_skus(self, filters_dict, relevant_columns, row):
"""
this method checks if there is value in 'exclude skus' column in the template.
if exists, it filters out the relevant skus from the calculation
:param filters_dict: filtered dictionary
:param relevant_columns: columns to create keys by
:param row: specific row to calculate
:return: None
"""
try:
exclude_skus = row['exclude skus'].split(',')
except AttributeError:
exclude_skus = []
if exclude_skus:
# filter out some product names according to template
product_names = self.filter_product_names(exclude_skus)
filters_dict['product_name'] = product_names
relevant_columns.remove('exclude skus')
@staticmethod
def decide_availability_score(row, availability_score):
"""
this method decides if the score should pass or fail according to the template
:param row: scpecific row from template
:param availability_score: score
:return: Boolean
"""
if availability_score == 0:
return False
else:
if row['KPI Level 1 Name'] == 'Set Modeloramas' and row[
'KPI Level 3 Name'] == 'Hay o no hay Pop?':
if row['KPI Level 2 Name'] == 'Pop Exterior':
return availability_score > 1
elif row['KPI Level 2 Name'] == 'Pop Interior':
return availability_score > 2
else:
return True
def calculate_sos_score(self, row, relevant_columns):
"""
this method calculates share of shelf score according to columns from the data frame
:param relevant_columns: columns to check in the excel file
:param row: data frame to calculate from
:return: share of shelf score
"""
# get df only with the correct template name
df = self.scif[self.scif.template_name == row['template_name']]
# sum of all the facings in df
facings = df.facings.values.sum()
if facings == 0:
return 0
# create dictionary for calculating
filters_dict = self.create_sos_filtered_dictionary(
relevant_columns, row)
# reduce the df only to relevant columns
df = df[filters_dict.keys()]
ratio = self.calculate_sos_ratio(df, filters_dict)
return ratio / facings
def calculate_sos_ratio(self, df, filters_dict):
ratio = 0
# iterate the data frame
for i, df_row in df.iterrows():
# initialize the boolean variable
bol = True
# iterate the filtered dictionary keys
for key in filters_dict.keys():
# check if the value in df row in in "key" column is in the filtered dictionary we created before
bol &= df_row[key] in filters_dict[key]
# that means success of the inner loop, that all the values matching for this data frame row
if bol:
# accumulate ratio
ratio = ratio + self.scif.facings.loc[i]
return ratio
def create_sos_filtered_dictionary(self, relevant_columns, row):
"""
this method filters out relevant columns from the template fso we can calculate SOS easily
:param relevant_columns: relevant columns from temolates
:param row: specific row from template
:return: dictionary
"""
# dictionary to send to the generic method
filters_dict = {}
self.handle_exclude_skus(filters_dict, relevant_columns, row)
# fill the dictionary
for column_value in relevant_columns:
if column_value == 'Store Additional Attribute 4' or column_value == 'store_type':
continue
filters_dict[column_value] = map(
str.strip,
str(row.loc[column_value]).split(','))
return filters_dict
def calculate_survey_score(self, row):
"""
this method calculates survey score according to columns from the data frame
:param row: data frame to calculate from
:return: boolean
"""
question_code = str(int(row['Survey Question Code']))
if not self.survey_response.empty and \
not self.survey_response[self.survey_response.code == question_code].empty:
answer = self.survey_response.selected_option_text[
self.survey_response.code == question_code].values[0]
if answer == 'Si':
return True
else:
if row['KPI Level 2 Name'] == 'Primer Impacto' and answer == 'No tiene Enfirador':
return True
return False
else:
return False
def check_store_type(self, row, relevant_columns):
"""
this method checks if the session store type is valid
:type relevant_columns: list
:param row: current KPI row
:return: boolean
"""
# make sure that we need to check this field
if relevant_columns.__contains__('store_type'):
# get the valid stores from the template
stores_df = pd.read_excel(self.excel_file_path,
sheetname='store types')
# create a list of the valid stores
stores = stores_df.values
return row['store_type'] in stores
else:
return True
def calculate_set_score(self, set_df, set_name):
"""
this method iterates kpi set and calculates it's score
:param set_df: the set df to calculate score for
:param set_name: the kpi set name
:return: None
"""
set_score = 0
# get array of all kpi level 2 names
kpi_names = set_df['KPI Level 2 Name'].unique()
# iterate all kpi level 2
for kpi_name in kpi_names:
# calculate kpi level 2 score
kpi_score = self.calculate_kpi_level_2_score(
kpi_name, set_df, set_name)
# write kpi level 2 score to DB
self.write_kpi_score_to_db(kpi_name, set_name, kpi_score)
# accumulate set score
set_score += kpi_score
# finally, write level 1 kpi set score to DB
self.write_kpi_set_score_to_db(set_name, set_score)
def calculate_kpi_level_2_score(self, kpi_name, set_df, set_name):
"""
this method gets kpi level 2 name, and iterates it's related atomic kpis
:param set_name: kpi set name
:param kpi_name: kpi level 2 name
:param set_df: kpi set df
:return: kpi level 2 score
"""
kpi_df = set_df[set_df['KPI Level 2 Name'] == kpi_name]
kpi_score = 0
# iterate the all related atomic kpis
for i, row in kpi_df.iterrows():
# get atomic kpi name
kpi_level_3_name = row['KPI Level 3 Name']
# calculate atomic kpi score
atomic_kpi_score = self.calculate_atomic_kpi_score(
row, kpi_level_3_name, kpi_name, set_name)
# accumulate kpi level 2 score
kpi_score += atomic_kpi_score
return kpi_score
def calculate_atomic_kpi_score(self, row, kpi_level_3_name, kpi_name,
set_name):
"""
this method calculates score for specific atomic kpi
:param set_name: kpi set name
:param kpi_name: kpi name
:param kpi_level_3_name: the atomic kpi name
:param row: atomic kpi details
:return: atomic kpi score
"""
atomic_kpi_score = 0
# get column name to consider in calculation
relevant_columns = map(str.strip, str(row['column names']).split(','))
if self.check_store_type(row, relevant_columns):
# get weight of current atomic kpi
curr_weight = row['weights']
# figure out what type of calculation need to be done
if row['KPI type'] == 'Product Availability':
if self.calculate_availability_score(row, relevant_columns):
atomic_kpi_score += curr_weight
elif row['KPI type'] == 'SOS':
ratio = self.calculate_sos_score(row, relevant_columns)
if (row['product_type'] == 'Empty') & (ratio <= 0.2):
atomic_kpi_score += curr_weight
elif ratio == 1:
atomic_kpi_score += curr_weight
elif row['KPI type'] == 'Survey':
if self.calculate_survey_score(row):
atomic_kpi_score += curr_weight
# write result to DB
self.write_atomic_to_db(kpi_level_3_name, atomic_kpi_score, kpi_name,
set_name)
return atomic_kpi_score
def write_kpi_set_score_to_db(self, set_name, set_score):
"""
this method writes set kpi score to static.kps_results DB
:param set_name: set name
:param set_score: set score
:return: None
"""
kpi_set_fk = self.kpi_static_data.kpi_set_fk[
self.kpi_static_data.kpi_set_name == set_name].unique()[0]
self.common.write_to_db_result(kpi_set_fk, self.LEVEL1, set_score)
def write_kpi_score_to_db(self, kpi_name, set_name, kpi_score):
"""
this method writes kpi score to static.kpk_results DB
:param kpi_name: name of level 2 kpi
:param set_name: name of related set
:param kpi_score: the score
:return: None
"""
kpi_fk = \
self.kpi_static_data.kpi_fk[(self.kpi_static_data.kpi_name == kpi_name) &
(self.kpi_static_data.kpi_set_name == set_name)].values[0]
self.common.write_to_db_result(kpi_fk, self.LEVEL2, kpi_score)
def write_atomic_to_db(self, atomic_name, atomic_score, kpi_name,
set_name):
"""
this method writes atomic kpi score to static.kpi_results DB
:param atomic_name: atomic kpi name
:param atomic_score: the score
:param kpi_name: name of related kpi
:param set_name: name of related set
:return:
"""
atomic_kpi_fk = \
self.kpi_static_data.atomic_kpi_fk[(self.kpi_static_data.atomic_kpi_name == atomic_name) &
(self.kpi_static_data.kpi_name == kpi_name) &
(self.kpi_static_data.kpi_set_name == set_name)].values[0]
self.common.write_to_db_result(atomic_kpi_fk, self.LEVEL3,
atomic_score)
def calculate_kpi_set_from_template(self):
"""
this method chooses the correct set to calculate
there will always be only on set to calculate, depending on the field 'Store additional attribute 4' from
template
:return: None
"""
# get the template
parsed_template = self.parse_template()
# get all the unique sets
sets = parsed_template['KPI Level 1 Name'].unique()
# get the session additional_attribute_4
additional_attribute_4 = self.store_info.additional_attribute_4.values[
0]
set_name = self.choose_correct_set_to_calculate(
additional_attribute_4, sets)
# wrong value in additional attribute 4 - shouldn't calculate
if set_name == '':
Log.warning(
'Wrong value in additional attribute 4 - shouldnt calculate')
return -1
# get only the part of the template that is related to this set
set_template_df = parsed_template[parsed_template['KPI Level 1 Name']
== set_name]
# start calculating !
self.calculate_set_score(set_template_df, set_name)
@staticmethod
def choose_correct_set_to_calculate(additional_attribute_4, sets):
"""
choose what is the appropriate set to calculate
:param additional_attribute_4: session additional_attribute_4
:param sets: list of optional sets
:return: set name to calculate
"""
if additional_attribute_4 == 'BC':
set_name = sets[0]
elif additional_attribute_4 == 'BA':
set_name = sets[1]
elif additional_attribute_4 == 'MODELORAMA':
set_name = sets[2]
else:
return ''
return set_name
def main_calculation(self):
# calculate geo
geo_result = self.geo.calculate_geo_location()
self.geo.write_geo_to_db(float(geo_result))
# calculate from template
self.calculate_kpi_set_from_template()
self.common.commit_results_data()
class AMERICASToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
FIRST_KPI = 'First KPI'
SECOND_KPI = 'Second KPI'
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.kpi_results_queries = []
def get_product_fk_from_product_name(self, product_name):
return self.all_products.product_fk[self.all_products.product_name ==
product_name].values[0]
def calculate_first_kpi(self):
"""
this method if in shelf number 2 all products are the product name below
:return: True/False
"""
product_name = 'CORONA LIGHT 355ML BOTE TALLCAN'
mpis = self.match_product_in_scene
second_shelf = mpis[mpis.shelf_number == 2]
unique_products_in_second_self = second_shelf.product_fk.unique()
if len(unique_products_in_second_self) != 1:
return False
else:
product_fk = self.get_product_fk_from_product_name(product_name)
if product_fk in unique_products_in_second_self:
return True
else:
return False
def calculate_second_kpi(self):
product_name = 'MODELO ESPECIAL 355ML BOTE SINGLE'
facing_counter = 0
try:
product_fk = self.get_product_fk_from_product_name(product_name)
products_in_scene_df = self.match_product_in_scene[
self.match_product_in_scene.product_fk == product_fk]
for i, row in products_in_scene_df.iterrows():
if str(row.face_count) == 'nan':
facing_counter += 1
else:
facing_counter += row.face_count
except IndexError:
pass
return facing_counter >= 5
def write_result_to_db(self, kpi_name, kpi_result):
if kpi_result:
score = 100
else:
score = 0
atomic_kpi_fk = self.kpi_static_data.atomic_kpi_fk[
self.kpi_static_data.atomic_kpi_name == kpi_name].values[0]
self.common.write_to_db_result(atomic_kpi_fk, self.LEVEL3, score)
kpi_fk = self.kpi_static_data.kpi_fk[self.kpi_static_data.kpi_name ==
kpi_name].values[0]
self.common.write_to_db_result(kpi_fk, self.LEVEL2, score)
kpi_set_fk = self.kpi_static_data.kpi_set_fk[
self.kpi_static_data.kpi_name == kpi_name].values[0]
self.common.write_to_db_result(kpi_set_fk, self.LEVEL1, score)
def main_calculation(self):
self.write_result_to_db(self.FIRST_KPI, self.calculate_first_kpi())
self.write_result_to_db(self.SECOND_KPI, self.calculate_second_kpi())
self.common.commit_results_data()
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