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)
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 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()