class DIAGEONGSANDToolBox: def __init__(self, data_provider, output): self.data_provider = data_provider self.output = output self.commonV2 = CommonV2(self.data_provider) self.common = Common(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common) self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] def main_calculation(self): # Global assortment kpis - v2 for API use assortment_res_dict_v2 = self.diageo_generator.diageo_global_assortment_function_v2( ) self.commonV2.save_json_to_new_tables(assortment_res_dict_v2) # 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) # Visible to Consumer % res_dict = self.diageo_generator.diageo_global_visible_percentage() self.commonV2.save_json_to_new_tables(res_dict) # Share of Shelf (SOS) % self.diageo_generator.activate_ootb_kpis(self.commonV2) # committing to the new tables self.commonV2.commit_results_data()
class DIAGEOAUToolBox: def __init__(self, data_provider, output): self.data_provider = data_provider self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.output = output self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common) 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_results = self.diageo_generator.diageo_global_grouping_assortment_calculation() self.commonV2.save_json_to_new_tables(assortment_results) # committing to new tables self.commonV2.commit_results_data() # committing to the old tables self.common.commit_results_data()
class DIAGEOUGToolBox: def __init__(self, data_provider, output): self.data_provider = data_provider self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.output = output self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common) def main_calculation(self): """This function calculates the KPI results.""" # 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_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) # committing to new tables self.commonV2.commit_results_data() # committing to the old tables self.common.commit_results_data()
class DIAGEOPLToolBox: def __init__(self, data_provider, output): self.data_provider = data_provider self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.output = output self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common, menu=True) def main_calculation(self): """ This function calculates the KPI results """ # 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_results = self.diageo_generator.diageo_global_grouping_assortment_calculation( ) self.commonV2.save_json_to_new_tables(assortment_results) # Global Share of Menu kpis menus_res_dict = self.diageo_generator.diageo_global_new_share_of_menu_function( ) self.commonV2.save_json_to_new_tables(menus_res_dict) # Global Visible to Consumer 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: self.commonV2.save_json_to_new_tables(res_dict) # 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()
class DIAGEOBRToolBox: def __init__(self, data_provider, output): self.data_provider = data_provider self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.output = output self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common, menu=True) def main_calculation(self): """ This function calculates the KPI results """ # 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_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 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']) # Global Menu kpis menus_res_dict = self.diageo_generator.diageo_global_new_share_of_menu_function( ) self.commonV2.save_json_to_new_tables(menus_res_dict) # committing to new tables self.commonV2.commit_results_data() # committing to the old tables self.common.commit_results_data()
class DIAGEOKEToolBox: def __init__(self, data_provider, output): self.data_provider = data_provider self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.output = output self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common) def main_calculation(self): """ This function calculates the KPI results. """ # 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_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 Visible to Consumer 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: self.commonV2.save_json_to_new_tables(res_dict) # committing to new tables self.commonV2.commit_results_data() # committing to the old tables self.common.commit_results_data()
class DIAGEOARDIAGEOARToolBox: def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.project_name = data_provider.project_name self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common) self.template_handler = TemplateHandler(self.project_name) def main_calculation(self): """ This function calculates the KPI results. """ log_runtime('Updating templates')(self.template_handler.update_templates)() # 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_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 Relative Position function template_data = self.template_handler.download_template(DiageoKpiNames.RELATIVE_POSITION) res_dict = self.diageo_generator.diageo_global_relative_position_function( template_data, location_type='template_name') self.commonV2.save_json_to_new_tables(res_dict) # committing to new tables self.commonV2.commit_results_data() # committing to the old tables self.common.commit_results_data()
class PS3SandToolBox: def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.project_name = data_provider.project_name self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common, menu=True) self.template_handler = TemplateHandler(self.project_name) def main_calculation(self): """ This function calculates the KPI results. """ # 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) log_runtime('Updating templates') self.template_handler.update_templates() # 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']) # Brand Blocking Global function template_data = self.template_handler.download_template(DiageoKpiNames.BRAND_BLOCKING) res_dict = self.diageo_generator.diageo_global_block_together( kpi_name=DiageoKpiNames.BRAND_BLOCKING, set_templates_data=template_data) self.commonV2.save_json_to_new_tables(res_dict) # Global Relative Position function template_data = self.template_handler.download_template(DiageoKpiNames.RELATIVE_POSITION) res_dict = self.diageo_generator.diageo_global_relative_position_function( template_data, location_type='template_name') self.commonV2.save_json_to_new_tables(res_dict) # Global Vertical Shelf Placement function template_data = self.template_handler.download_template(DiageoKpiNames.VERTICAL_SHELF_PLACEMENT) res_dict = self.diageo_generator.diageo_global_vertical_placement(template_data) self.commonV2.save_json_to_new_tables(res_dict) # committing to the new tables self.commonV2.commit_results_data() # committing to the old tables self.common.commit_results_data()
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 DIAGEOINToolBox: def __init__(self, data_provider, output): self.data_provider = data_provider self.commonV2 = CommonV2(self.data_provider) self.rds_conn = PSProjectConnector(self.data_provider.project_name, DbUsers.CalculationEng) self.store_assortment = PSAssortmentDataProvider(self.data_provider).execute(policy_name=None) self.output = output self.kpi_static_data = self.get_kpi_static_data() self.store_id = self.data_provider[Data.STORE_FK] self.own_manufacturer_fk = 12 self.store_info = self.get_store_info(self.store_id) self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.all_templates = self.data_provider[Data.ALL_TEMPLATES] self.common = Common(self.data_provider) self.scene_info = self.data_provider[Data.SCENES_INFO] self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common) self.template_handler = TemplateHandler(self.data_provider.project_name) self.template_path = os.path.join(os.path.dirname(os.path.dirname(os.path.realpath(__file__))), 'Data') self.template_path = os.path.join(self.template_path, "Template.xlsx") self.kpi_template_data = parse_template(self.template_path, sheet_name='KPI') self.store_info = self.data_provider[Data.STORE_INFO] self.current_date = datetime.now() def get_kpi_static_data(self): """ This function extracts the static new KPI data (new tables) and saves it into one 'global' data frame. The data is taken from static.kpi_level_2. """ query = Queries.get_new_kpi_data() kpi_static_data = pd.read_sql_query(query, self.rds_conn.db) return kpi_static_data def get_assortment_product_info(self): query = """SELECT pk, assortment_name FROM pservice.assortment""" return self.perform_query(query) def get_store_info(self, store_id): query = """SELECT st.name as store_name, st.pk as store_fk, st.store_number_1, st.sales_rep_name, st.store_type, st.distribution_type, st.address_city, st.customer_name, st.manager_name, st.comment as store_comment, st.test_store, sta.name state, st.additional_attribute_1, st.additional_attribute_2, st.additional_attribute_3, st.additional_attribute_4, st.additional_attribute_5, st.additional_attribute_6, st.additional_attribute_7, st.additional_attribute_8, st.additional_attribute_9, st.additional_attribute_10, st.additional_attribute_11, st.additional_attribute_12, st.additional_attribute_13, st.additional_attribute_14, st.additional_attribute_15, rg.name as region_name, rg.code as region_code, rg.remarks as region_remarks, st.region_fk, st.branch_fk, st.retailer_fk, state_fk, r.name as retailer_name FROM static.stores st JOIN static.regions rg on rg.pk = st.region_fk LEFT join static.retailer r on r.pk = st.retailer_fk LEFT join static.state sta on sta.pk= state_fk WHERE st.pk = {0}; """.format(store_id) return self.perform_query(query) def perform_query(self, query, **kwargs): """ This function creates a :class: `pandas.DataFrame` using a given cursor\n :param str query: The query :return: A newly created :class:`pandas.DataFrame` object with the results of the query """ df = self.data_provider.run_query(query) return df def main_calculation(self): """ This function calculates the KPI results. """ try: all_policies = PSAssortmentDataProvider(self.data_provider).get_policies() except KeyError: Log.warning("No assortment policies found for session {}".format(self.data_provider.session_uid)) pass except Exception as e: if 'No assortment policies were found' not in e.message: raise e Log.warning("No assortment policies found for session {}".format(self.data_provider.session_uid)) else: # Local Custom/Client Brand Group Presence brand_presence_policies = all_policies[all_policies['policy_type'] == 7] if not brand_presence_policies.empty: self.custom_brand_presence_main() # 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 - v2 for API use assortment_res_dict_v2 = self.diageo_generator.diageo_global_assortment_function_v2() self.commonV2.save_json_to_new_tables(assortment_res_dict_v2) # 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 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 save_results_to_db(self, results_list): if results_list: for result in results_list: if result is not None: self.commonV2.write_to_db_result(**result) @staticmethod def custom_brand_presence_per_scene(df, template_fk): list_results = [] df_product = df[['assortment_fk', 'kpi_fk_lvl2', 'product_fk', 'item_id']] df_product = pd.DataFrame( df_product.groupby(['assortment_fk', 'kpi_fk_lvl2'])['product_fk'].count().reset_index()) df_product['product_fk'].fillna(0, inplace=True) df_item = df[['assortment_fk', 'kpi_fk_lvl2', 'product_fk', 'item_id']] df_item = pd.DataFrame( df_item.groupby(['assortment_fk', 'kpi_fk_lvl2'])['item_id'].count().reset_index()) df_item['item_id'].fillna(0, inplace=True) df_result = df_product.merge(df_item, how='left', on="kpi_fk_lvl2") dict_result = {} for row_num, row_data in df_result.iterrows(): dict_result['fk'] = row_data['kpi_fk_lvl2'] dict_result['numerator_id'] = row_data['kpi_fk_lvl2'] dict_result['numerator_result'] = row_data['item_id'] # need to implement target per assortment if int(row_data['item_id']) > 0: dict_result['result'] = 1 else: dict_result['result'] = 0 dict_result['denominator_id'] = row_data['assortment_fk_x'] dict_result['denominator_result'] = row_data['product_fk'] dict_result['context_id'] = template_fk list_results.append(dict_result) dict_result = {} return list_results def custom_brand_presence_main(self): if self.store_assortment.empty: Log.warning('Store Policy/assortment missing for store_id={}'.format(self.store_id)) return brand_presence_kpis = self.kpi_template_data[self.kpi_template_data['kpi_group'] == 'BRAND_GROUP_PRESENCE'] if brand_presence_kpis.empty: Log.warning("No KPI in the template with kpi_group=BRAND_GROUP_PRESENCE") return bp_kpi_scene_lvl = brand_presence_kpis[brand_presence_kpis['kpi_name']=='BRAND_GROUP_PRESENCE_SCENE_LEVEL'] if bp_kpi_scene_lvl.empty: Log.warning('BRAND_GROUP_PRESENCE_SCENE_LEVEL - KPI not available in KPI template') return kpi_row_data = bp_kpi_scene_lvl.iloc[0] df_results = self.custom_brand_presence_scene_lvl(kpi_row_data) bp_kpi_mnf_ws = brand_presence_kpis[brand_presence_kpis['kpi_name'] == 'BRAND_GROUP_PRESENCE_OWN_MANF_WHOLE_STORE'] if bp_kpi_mnf_ws.empty: Log.warning('BRAND_GROUP_PRESENCE_OWN_MANF_WHOLE_STORE - KPI not available in KPI template') return self.calculate_brand_presence_overall_score(df_results) def custom_brand_presence_scene_lvl(self, kpi_row_data): df_results = pd.DataFrame() list_results = [] kpi_name = kpi_row_data['kpi_name'].strip() back_bar_template = kpi_row_data['back_bar_scenes'].strip() bb_template_name = self.all_templates[self.all_templates['template_name'] == back_bar_template] menu_template = kpi_row_data['menu_scenes'].strip() mn_template_name = self.all_templates[self.all_templates['template_name'] == menu_template] if bb_template_name.empty or mn_template_name.empty: Log.warning("BackBar:{} OR Menu:{} not matching in static.template".format(bb_template_name, menu_template)) return pd.DataFrame() bb_template_fk = bb_template_name.iloc[0]["template_fk"] mn_template_fk = mn_template_name.iloc[0]["template_fk"] bb_assortment = self.store_assortment[['assortment_fk', 'kpi_fk_lvl2', 'product_fk']] mn_assortment = self.store_assortment[['assortment_fk', 'kpi_fk_lvl2', 'product_fk']] if bb_assortment.empty and mn_assortment.empty: Log.warning("No assortments for custom brand presence") return pd.DataFrame(list_results) if self.kpi_template_data.empty: Log.warning("KPI Template is empty") return pd.DataFrame(list_results) df_scif_back_bar = self.scif[(self.scif['template_fk'] == bb_template_fk) & (self.scif['facings'] > 0) & (self.scif['product_type'] == 'SKU')] df_scif_back_bar = df_scif_back_bar[['product_fk', 'item_id']] df_scif_menu = self.scif[(self.scif['template_fk'] == mn_template_fk) & (self.scif['facings'] > 0) & (self.scif['product_type'] == 'POS')] df_scif_menu = df_scif_menu[['product_fk', 'item_id']] df_bb = bb_assortment.merge(df_scif_back_bar, how='left', on="product_fk") df_bb_results = pd.DataFrame(self.custom_brand_presence_per_scene(df_bb, bb_template_fk)) df_bb_results = df_bb_results[['numerator_id', 'result']] df_bb_results.columns = ['numerator_id', 'numerator_result'] df_mn = mn_assortment.merge(df_scif_menu, how='left', on="product_fk") df_mn_results = pd.DataFrame(self.custom_brand_presence_per_scene(df_mn, mn_template_fk)) df_mn_results = df_mn_results[['numerator_id', 'result']] df_mn_results.columns = ['denominator_id', 'denominator_result'] df_results = pd.merge(df_bb_results, df_mn_results, left_on=['numerator_id'], right_on=['denominator_id'], how='left') df_results['result'] = df_results['numerator_result'] + df_results['denominator_result'] df_results['result'] = df_results['result'].apply(lambda x: 1 if x > 1 else x) kpi_fk = self.get_kpi_fk_by_kpi_type(kpi_name) for row_num_results, row_data_results in df_results.iterrows(): self.commonV2.write_to_db_result(fk=kpi_fk, numerator_id=row_data_results['numerator_id'], numerator_result=row_data_results['numerator_result'], denominator_id=row_data_results['denominator_id'], denominator_result=row_data_results['denominator_result'], context_id=self.store_id, score=row_data_results['result'], result=row_data_results['result']) return df_results def get_kpi_fk_by_kpi_type(self, kpi_type): """ convert kpi name to kpi_fk :param kpi_type: string :return: fk """ assert isinstance(kpi_type, (unicode, basestring)), "name is not a string: %r" % kpi_type try: return self.kpi_static_data[self.kpi_static_data['type'] == kpi_type]['pk'].values[0] except IndexError: Log.info("Kpi name: {} is not equal to any kpi name in static table".format(kpi_type)) return None def calculate_brand_presence_overall_score(self, df): if df.empty: return kpi_fk = self.get_kpi_fk_by_kpi_type("BRAND_GROUP_PRESENCE_OWN_MANF_WHOLE_STORE") numerator_id = self.own_manufacturer_fk numerator_result = df['result'].sum() denominator_id = self.store_id denominator_result = df.shape[0] score = 0.0 try: score = numerator_result / float(denominator_result) except Exception as ex: Log.info("Warning: {}".format(ex.message)) self.commonV2.write_to_db_result(fk=kpi_fk, numerator_id=numerator_id, numerator_result=numerator_result, denominator_id=denominator_id, denominator_result=denominator_result, score=score, result=score)
class DIAGEOUKToolBox: def __init__(self, data_provider, output): self.data_provider = data_provider self.project_name = self.data_provider.project_name self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.output = output self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common, menu=True) self.template_handler = TemplateHandler(self.project_name) def main_calculation(self): """ This function calculates the KPI results. """ log_runtime('Updating templates') self.template_handler.update_templates() # 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 Equipment score kpis 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 Relative Position function template_data = self.template_handler.download_template( DiageoKpiNames.RELATIVE_POSITION) res_dict = self.diageo_generator.diageo_global_relative_position_function( template_data, location_type='template_name') self.commonV2.save_json_to_new_tables(res_dict) # 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']) # Global Visible to Consumer 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) self.commonV2.save_json_to_new_tables(res_dict) # committing to new tables self.commonV2.commit_results_data() # committing to the old tables self.common.commit_results_data() 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 DIAGEOMXToolBox: def __init__(self, data_provider, output): self.data_provider = data_provider self.project_name = self.data_provider.project_name self.ps_data_provider = PsDataProvider(data_provider) self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.output = output self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common) self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.session_uid = self.data_provider.session_uid def main_calculation(self): if not self.exists_survey_resposnse(): """ This function calculates the KPI results """ # 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_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 SOS kpi res_dict = self.diageo_generator.diageo_global_share_of_shelf_function( ) self.commonV2.save_json_to_new_tables(res_dict) # global touch point kpi template_path = os.path.join( os.path.dirname(os.path.dirname(os.path.realpath(__file__))), 'Data', 'TOUCH POINT (template) Agosto 2020.xlsx') res_dict = self.diageo_generator.diageo_global_touch_point_function( template_path) self.commonV2.save_json_to_new_tables(res_dict) # committing to the old tables self.common.commit_results_data() # commit to old tables # committing to new tables self.commonV2.commit_results_data() def exists_survey_resposnse(self): # Method is used to check in the db to if survey question_fk 98 is answered # If answered, the kpi should not run query = """SELECT session_uid,sr.question_fk, sr.selected_option_fk FROM probedata.survey_response sr Left Join static.survey_question_answer qa on sr.selected_option_text = qa.pk where sr.question_fk = 98 and session_uid = '{}';""".format( self.session_uid) cur = self.rds_conn.db.cursor() cur.execute(query) res = cur.fetchall() if res: relevant_response_df = pd.DataFrame(list(res)) try: survey_response = relevant_response_df.iloc[0, 2] except IndexError: survey_response = None else: survey_response = None return survey_response
class DIAGEOCOToolBox: 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.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.store_id = self.data_provider.store_fk self.template_handler = TemplateHandler( self.data_provider.project_name) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common, menu=True) def main_calculation(self): """ This function calculates the KPI results. """ self.template_handler.update_templates() # SOS Out Of The Box kpis self.diageo_generator.activate_ootb_kpis(self.common_v2) # sos by scene type self.diageo_generator.sos_by_scene_type(self.common_v2) # Global assortment kpis assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2( ) self.common_v2.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.common_v2.save_json_to_new_tables(assortment_res_dict_v3) # Global Share of Menu kpis menus_res_dict = self.diageo_generator.diageo_global_new_share_of_menu_function( ) self.common_v2.save_json_to_new_tables(menus_res_dict) # Global Secondary Displays KPI res_json = self.diageo_generator.diageo_global_secondary_display_secondary_function( ) if res_json: self.common_v2.write_to_db_result( fk=res_json['fk'], numerator_id=res_json['numerator_id'], denominator_id=self.store_id, result=res_json['result']) # Global Brand Pouring KPI template_data = self.template_handler.download_template( DiageoKpiNames.BRAND_POURING) results_list = self.diageo_generator.diageo_global_brand_pouring_status_function( template_data) self.save_results_to_db(results_list) # Global Brand Blocking KPI template_data = self.template_handler.download_template( DiageoKpiNames.BRAND_BLOCKING) results_list = self.diageo_generator.diageo_global_block_together( DiageoKpiNames.BRAND_BLOCKING, template_data) self.save_results_to_db(results_list) # Global Relative Position KPI template_data = self.template_handler.download_template( DiageoKpiNames.RELATIVE_POSITION) results_list = self.diageo_generator.diageo_global_relative_position_function( template_data) self.save_results_to_db(results_list) # Global TOUCH_POINT KPI templates_data = pd.read_excel(Const.TEMPLATE_PATH, Const.TOUCH_POINT_SHEET_NAME, header=Const.TOUCH_POINT_HEADER_ROW) template = templates_data.fillna(method='ffill').set_index( templates_data.keys()[0]) results_list = self.diageo_generator.diageo_global_touch_point_function( template=template, old_tables=True, new_tables=False, store_attribute=Consts.ADDITIONAL_ATTR_2) self.save_results_to_db(results_list) # committing to new tables self.common_v2.commit_results_data() # committing to the old tables self.common.commit_results_data() 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)