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