def get_all_kpi_data(self): results_list_new_db = [] scores_dict = {} if self.templates_info.empty: Log.info( "All KPI: session: {} doesnt have relevant external targets". format(self.session_uid)) return [], {} main_children = self.templates_info[self.templates_info[ self.template.KPI_GROUP] == self.RED_SCORE] for c in xrange(0, len(main_children)): main_child = main_children.iloc[c] main_child_kpi_fk = self.get_new_kpi_fk( main_child) # kpi fk from new tables main_kpi_identifier = self.commonV2.get_dictionary( kpi_fk=main_child_kpi_fk) if self.validate_store_type(main_child): children = self.templates_info[self.templates_info[ self.template.KPI_GROUP].str.encode( HelperConsts.UTF8) == main_child[ self.template.KPI_NAME].encode(HelperConsts.UTF8)] scores = [] for i in xrange(len(children)): child = children.iloc[i] numerator, denominator, result_new_db, numerator_id = 0, 0, 0, None kpi_weight = self.validate_kpi_run(child) if kpi_weight is not False: kpi_type = child[self.template.KPI_TYPE] result = threshold = None if kpi_type == self.SURVEY: score, result, threshold, survey_answer_fk = self.check_survey( child) threshold = None numerator, denominator, result_new_db = 1, 1, score * 100 numerator_id = survey_answer_fk elif kpi_type == self.SHARE_OF_SHELF: score, result, threshold, result_new_db, numerator, denominator = \ self.calculate_share_of_shelf(child) elif kpi_type == self.NUMBER_OF_SCENES: scene_types = self.get_scene_types(child) result = self.general_tools.calculate_number_of_scenes( **{SCENE_TYPE_FIELD: scene_types}) numerator, denominator, result_new_db = result, 1, result score = 1 if result >= 1 else 0 else: Log.warning( "KPI of type '{}' is not supported via assortments" .format(kpi_type)) continue if score is not False: if score is None: points = 0 else: points = float( child[self.template.WEIGHT] ) if kpi_weight is True else kpi_weight scores.append((points, score)) atomic_fk = self.get_atomic_fk(main_child, child) self.write_to_db_result( atomic_fk, (score, result, threshold, points), level=self.LEVEL3) identifier_parent = main_kpi_identifier child_name = '{}-{}'.format(child[self.template.TRANSLATION], 'Atomic') \ if main_child[self.template.KPI_NAME] == child[self.template.KPI_NAME] else child[ self.template.TRANSLATION] child.set_value(self.template.TRANSLATION, child_name) child_kpi_fk = self.get_new_kpi_fk( child) # kpi fk from new tables results_list_new_db.append( self.get_new_kpi_dict( child_kpi_fk, result_new_db, score, numerator, denominator, weight=points, target=denominator, identifier_parent=identifier_parent, numerator_id=numerator_id)) max_points = sum([score[0] for score in scores]) actual_points = sum([score[0] * score[1] for score in scores]) percentage = 0 if max_points == 0 else round( (actual_points / float(max_points)) * 100, 2) kpi_name = main_child[self.template.TRANSLATION] kpi_fk = self.kpi_static_data[ self.kpi_static_data['kpi_name'].str.encode( HelperConsts.UTF8) == kpi_name.encode( HelperConsts.UTF8)]['kpi_fk'].values[0] self.write_to_db_result( kpi_fk, (actual_points, max_points, percentage), level=self.LEVEL2) scores_dict[kpi_name] = (max_points, actual_points) results_list_new_db.append( self.get_new_kpi_dict( main_child_kpi_fk, percentage, percentage, actual_points, max_points, target=max_points, weight=actual_points, identifier_result=main_kpi_identifier, identifier_parent=self.RED_SCORE)) return results_list_new_db, scores_dict
def handle_survey_atomics(self, atomic_id, atomic_name, parent_name): # bring the kpi rows from the survey sheet rows = self.survey_sheet.loc[self.survey_sheet[Const.TEMPLATE_KPI_ID] == atomic_id] temp = rows[Const.TEMPLATE_STORE_TYPE] row_store_filter = rows[( temp.apply(lambda r: self.store_type_filter in [item.strip() for item in r.split(",")])) | (temp == "")] if row_store_filter.empty: return 0 else: # find the answer to the survey in session question_text = row_store_filter[ Const.TEMPLATE_SURVEY_QUESTION_TEXT].values[0] question_answer_template = row_store_filter[ Const.TEMPLATE_TARGET_ANSWER].values[0] score = row_store_filter[Const.TEMPLATE_SCORE].values[0] survey_result = self.survey.get_survey_answer( ('question_text', question_text)) if not survey_result: return 0 if '-' in question_answer_template: numbers = question_answer_template.split('-') try: numeric_survey_result = int(survey_result) except: Log.warning("Survey question - " + str(question_text) + " - doesn't have a numeric result") return 0 if numeric_survey_result < int( numbers[0]) or numeric_survey_result > int(numbers[1]): return 0 condition = row_store_filter[ Const.TEMPLATE_CONDITION].values[0] if condition != "": second_question_text = row_store_filter[ Const.TEMPLATE_SECOND_SURVEY_QUESTION_TEXT].values[0] second_survey_result = self.survey.get_survey_answer( ('question_text', second_question_text)) if not second_survey_result: second_survey_result = 0 second_numeric_survey_result = int(second_survey_result) survey_result = 1 if numeric_survey_result >= second_numeric_survey_result else -1 else: survey_result = 1 else: question_answer_template = question_answer_template.split(',') question_answer_template = [ item.strip() for item in question_answer_template ] if survey_result in question_answer_template: survey_result = 1 else: survey_result = -1 final_score = score if survey_result == 1 else 0 try: atomic_pk = self.common_v2.get_kpi_fk_by_kpi_name(atomic_name) except IndexError: Log.warning("There is no matching Kpi fk for kpi name: " + atomic_name) return 0 self.common_v2.write_to_db_result(fk=atomic_pk, numerator_id=self.region_fk, numerator_result=0, denominator_result=0, denominator_id=self.store_id, result=survey_result, score=final_score, identifier_result=atomic_name, identifier_parent=parent_name, should_enter=True, parent_fk=3) return final_score
def calculate_display_compliance(self): kpi_display_presence = self.kpi_static_data[ (self.kpi_static_data[KPI_TYPE_COL] == GSK_DISPLAY_PRESENCE) & (self.kpi_static_data['delete_time'].isnull())] kpi_display_sku_compliance = self.kpi_static_data[ (self.kpi_static_data[KPI_TYPE_COL] == GSK_DISPLAY_SKU_COMPLIANCE) & (self.kpi_static_data['delete_time'].isnull())] kpi_display_price_compliance = self.kpi_static_data[ (self.kpi_static_data[KPI_TYPE_COL] == GSK_DISPLAY_PRICE_COMPLIANCE ) & (self.kpi_static_data['delete_time'].isnull())] kpi_display_bay_purity = self.kpi_static_data[ (self.kpi_static_data[KPI_TYPE_COL] == GSK_DISPLAY_BAY_PURITY) & (self.kpi_static_data['delete_time'].isnull())] kpi_display_presence_sku = self.kpi_static_data[ (self.kpi_static_data[KPI_TYPE_COL] == GSK_DISPLAY_PRESENCE_SKU) & (self.kpi_static_data['delete_time'].isnull())] secondary_display_targets = self.targets[ self.targets['kpi_fk'] == kpi_display_presence['pk'].iloc[0]] # if no targets return if secondary_display_targets.empty: Log.warning( 'There is no target policy for calculating secondary display compliance.' ) return False else: current_scene_fk = self.scene_info.iloc[0].scene_fk display_per_sku_per_scene_calculated = False target_matched = False for idx, each_target in secondary_display_targets.iterrows(): if target_matched: Log.info( 'The session: {sess} - scene: {scene} has matched one target ' 'and won\'t run for another.'.format( sess=self.session_uid, scene=current_scene_fk)) continue # loop through each external target to fit the current store has_posm_recognized = False multi_posm_or_bay = False mandatory_sku_compliance = False optional_sku_compliance = False price_compliance = False is_scene_relevant = False scene_relevant_targets = pd.DataFrame() # check store relevance store_relevant_targets = each_target[STORE_IDENTIFIERS].dropna( ) _bool_store_check_df = self.store_info[list(store_relevant_targets.keys())] \ == store_relevant_targets.values is_store_relevant = _bool_store_check_df.all(axis=None) if is_store_relevant: # check scene relevance scene_relevant_targets = each_target[ SCENE_IDENTIFIERS].dropna() _bool_scene_check_df = self.scene_info[list(scene_relevant_targets.keys())] \ == scene_relevant_targets.values is_scene_relevant = _bool_scene_check_df.all(axis=None) if is_store_relevant and is_scene_relevant and not target_matched: # calculate display compliance for the matched external targets target_matched = True Log.info( 'The session: {sess} - scene: {scene} is relevant for calculating ' 'secondary display compliance.'.format( sess=self.session_uid, scene=current_scene_fk)) posm_relevant_targets = each_target[ POSM_IDENTIFIERS].dropna() mandatory_eans = _sanitize_csv( posm_relevant_targets[MANDATORY_EANS_KEY]) optional_posm_eans = [] if OPTIONAL_EAN_KEY in posm_relevant_targets: optional_posm_eans = _sanitize_csv( posm_relevant_targets[OPTIONAL_EAN_KEY]) # save detailed sku presence posm_to_check = each_target[POSM_PK_KEY] # FIND THE SCENES WHICH HAS THE POSM to check for multiposm or multibays is_posm_absent = self.match_display_in_scene[ self.match_display_in_scene['display_fk'] == posm_to_check].empty if is_posm_absent: Log.info( 'The scene: {scene} is relevant but POSM {pos} is not present. ' 'Save and start new scene.'.format( scene=current_scene_fk, pos=posm_to_check)) # calculate display per sku -- POSM is absent if not display_per_sku_per_scene_calculated: display_per_sku_per_scene_calculated = self.save_display_presence_per_sku( kpi=kpi_display_presence_sku, numerator_result=0, # 0 posm not recognized ) if len(self.match_product_in_scene['bay_number'].unique()) > 1 or \ len(self.match_display_in_scene) > 1: Log.info( 'The scene: {scene} is relevant and multi_bay_posm is True. ' 'Purity per bay is calculated and going to next scene.' .format(scene=current_scene_fk, pos=posm_to_check)) multi_posm_or_bay = True self.save_purity_per_bay(kpi_display_bay_purity) self.save_display_compliance_data([ { 'pk': kpi_display_presence.iloc[0].pk, 'result': int(has_posm_recognized), 'score': int(multi_posm_or_bay), 'numerator_id': posm_to_check, 'numerator_result': posm_to_check }, { 'pk': kpi_display_sku_compliance.iloc[0].pk, 'result': float(mandatory_sku_compliance), 'score': float(optional_sku_compliance), 'denominator_id': posm_to_check, 'denominator_result': posm_to_check }, { 'pk': kpi_display_price_compliance.iloc[0].pk, 'result': float(price_compliance), 'score': float(price_compliance), 'denominator_id': posm_to_check, 'denominator_result': posm_to_check }, ]) continue # this scene has the posm Log.info( 'The scene: {scene} is relevant and POSM {pos} is present.' .format(scene=current_scene_fk, pos=posm_to_check)) has_posm_recognized = True # check if this scene has multi bays or multi posm if len(self.match_product_in_scene['bay_number'].unique()) > 1 or \ len(self.match_display_in_scene) > 1: # Its multi posm or bay -- only purity calc per bay is possible Log.info( 'The scene: {scene} is relevant and POSM {pos} is present but multi_bay_posm is True. ' 'Purity per bay is calculated and going to next scene.' .format(scene=current_scene_fk, pos=posm_to_check)) multi_posm_or_bay = True # calculate display per sku for multi posm/multi bay if not display_per_sku_per_scene_calculated: display_per_sku_per_scene_calculated = self.save_display_presence_per_sku( kpi=kpi_display_presence_sku, numerator_result=2, # 2 multi posm ) self.save_display_compliance_data([ { 'pk': kpi_display_presence.iloc[0].pk, 'result': int(has_posm_recognized), 'score': int(multi_posm_or_bay), 'numerator_id': posm_to_check, 'numerator_result': posm_to_check }, { 'pk': kpi_display_sku_compliance.iloc[0].pk, 'result': float(mandatory_sku_compliance), 'score': float(optional_sku_compliance), 'denominator_id': posm_to_check, 'denominator_result': posm_to_check }, { 'pk': kpi_display_price_compliance.iloc[0].pk, 'result': float(price_compliance), 'score': float(price_compliance), 'denominator_id': posm_to_check, 'denominator_result': posm_to_check }, ]) self.save_purity_per_bay(kpi_display_bay_purity) continue Log.info( 'The scene: {scene} is relevant and POSM {pos} is present with only one bay.' .format(scene=current_scene_fk, pos=posm_to_check)) # save purity per bay self.save_purity_per_bay(kpi_display_bay_purity) # calculate display per sku for ALL SUCCESS if not display_per_sku_per_scene_calculated: display_per_sku_per_scene_calculated = self.save_display_presence_per_sku( kpi=kpi_display_presence_sku, posm_to_check=posm_to_check, numerator_result=1, # 1--one one posm mandatory_eans=mandatory_eans, optional_posm_eans=optional_posm_eans) # calculate compliance mandatory_sku_compliance = self.get_ean_presence_rate( mandatory_eans) optional_sku_compliance = self.get_ean_presence_rate( optional_posm_eans) if mandatory_sku_compliance: price_compliance = self.get_price_presence_rate( mandatory_eans) self.save_display_compliance_data([ { 'pk': kpi_display_presence.iloc[0].pk, 'result': int(has_posm_recognized), 'score': int(multi_posm_or_bay), 'numerator_id': posm_to_check, 'numerator_result': posm_to_check }, { 'pk': kpi_display_sku_compliance.iloc[0].pk, 'result': float(mandatory_sku_compliance), 'score': float(optional_sku_compliance), 'denominator_id': posm_to_check, 'denominator_result': posm_to_check }, { 'pk': kpi_display_price_compliance.iloc[0].pk, 'result': float(price_compliance), 'score': float(price_compliance), 'denominator_id': posm_to_check, 'denominator_result': posm_to_check }, ]) continue else: # the session/store is not part of the KPI targets Log.info( 'The session: {sess} - scene: {scene}, the current kpi target [pk={t_pk}] ' 'is not valid. Keep Looking...'.format( sess=self.session_uid, scene=current_scene_fk, t_pk=each_target.external_target_fk)) if scene_relevant_targets.empty: # Store failed Log.info( "Store info is {curr_data} but target is {store_data}" .format( curr_data=self.store_info.iloc[0][list( store_relevant_targets.keys())].to_json(), store_data=store_relevant_targets.to_json())) else: Log.info( "Scene info is {curr_data} but target is {store_data}" .format( curr_data=self.scene_info.iloc[0][list( scene_relevant_targets.keys())].to_json(), store_data=scene_relevant_targets.to_json())) continue else: if not display_per_sku_per_scene_calculated: # check if its secondary display type if not self.templates.loc[ (self.templates['template_group'] == 'Secondary display') & (~self.templates['template_name'].isin( ['Clipstrip', 'Hangsell']))].empty: Log.info( "Secondary Display => Session: {sess} - scene {scene} didn't qualify " "any external targets.".format( sess=self.session_uid, scene=self.scene_info.iloc[0].scene_fk, )) display_per_sku_per_scene_calculated = self.save_display_presence_per_sku( kpi=kpi_display_presence_sku, numerator_result=0) # 0--posm not recognized
def scene_main_calculation(self): if self.scene_tool_box.match_product_in_scene.empty: Log.warning('Match product in scene is empty for this scene') self.scene_tool_box.main_function() self.scene_tool_box.commit_results()
def calculate_facings_sos_kpi(self, kpi_df): kpi_name = kpi_df[Const.KPI_NAME].values[0] kpi_fk = self.common.get_kpi_fk_by_kpi_name(kpi_name) if kpi_fk is None: Log.warning("There is no matching Kpi fk for kpi name: " + kpi_name) return entity_name = kpi_df[Const.NUMERATOR_ENTITY].values[0] entity_name_for_fk = Const.NAME_TO_FK[entity_name] # iterate all categories (if kpi_df length > 1) for i, row in kpi_df.iterrows(): self.kpi_excluding = row[[ Const.EXCLUDE_EMPTY, Const.EXCLUDE_HANGER, Const.EXCLUDE_IRRELEVANT, Const.EXCLUDE_POSM, Const.EXCLUDE_OTHER, Const.STACKING, Const.EXCLUDE_SKU, Const.EXCLUDE_STOCK, Const.EXCLUDE_OSD ]] df = self.filter_df(row) df = df[df['width_mm_advance'] != -1] if df.empty: continue if row[Const.PER_SCENE_TYPE] == Const.EACH: scene_types = row[Const.SCENE_TYPE].split(",") scene_types = [item.strip() for item in scene_types] scene_types = set(scene_types).intersection( set(df['template_name'])) else: scene_types = [""] # Iterate scene types for sc in scene_types: filters = {} if sc != "": try: context_id = self.templates[ self.templates['template_name'] == sc]['template_fk'].iloc[0] except Exception as ex: Log.warning("No scene type with the following name: " + str(sc) + ", warning: " + str(ex)) continue filters['template_name'] = sc else: context_id = 0 category = row[Const.CATEGORY] if category != "": denominator_id = self.all_products[ self.all_products['category'] == category]['category_fk'].iloc[0] filters['category'] = category else: denominator_id = self.store_id all_denominators = df[entity_name].drop_duplicates().tolist() if row[Const.NUMERATOR] != "": all_denominators = [row[Const.NUMERATOR]] denominator = self.tools.get_filter_condition(df, **filters).sum() if denominator == 0: continue # iterate all entities for entity in all_denominators: filters[entity_name] = entity numerator = self.tools.get_filter_condition( df, **filters).sum() del filters[entity_name] if numerator == 0: continue result = float(numerator) / float(denominator) numerator_id = df[df[entity_name] == entity][entity_name_for_fk].values[0] self.common.write_to_db_result( fk=kpi_fk, numerator_id=numerator_id, denominator_id=denominator_id, context_id=context_id, numerator_result=numerator, denominator_result=denominator, result=result, score=result)
def run_project_calculations(self): self.timer.start() # use log.time_message project_name = self.data_provider.project_name if self.data_provider.visit_date.isoformat() < '2019-01-01': # kpi_file_name = '2018/MARS KPIs.xlsx' # kpi_range_targets_sheet_names = [2217, 2220, 2390, 2391, 2317, 2254] # kpi_channels = None Log.error( "Error: The visit date is out of date: {}. The sessions cannot be calculated." "".format(self.data_provider.visit_date.isoformat())) return elif self.data_provider.visit_date.isoformat() < '2019-12-29': # kpi_file_name = '2019/MARS KPIs.xlsx' # kpi_range_targets_sheet_names = [4317, 4650, 4254] # , 4388, 4389 # kpi_channels = [kpi_file_name, 'channels', 'channels'] Log.error( "Error: The visit is out of date threshold [2019-12-29]: {} . The session cannot be calculated." "".format(self.data_provider.visit_date.isoformat())) return elif self.data_provider.visit_date.isoformat() < '2020-06-14': kpi_file_name = '2020/MARS KPIs.xlsx' kpi_range_targets_sheet_names = [4317, 4650, 4254] kpi_channels = [kpi_file_name, 'channels', 'channels'] else: kpi_file_name = '2020_06_14/MARS KPIs.xlsx' kpi_range_targets_sheet_names = [4317, 4650, 4254] kpi_channels = [kpi_file_name, 'channels', 'channels'] # [file name, key, sheet name] kpi_template = \ [kpi_file_name, 'kpi_data', 'KPI'] kpi_golden_shelves = \ [kpi_file_name, 'golden_shelves', 'golden_shelves'] kpi_answers_translation = \ [kpi_file_name, 'survey_answers_translation', 'survey_answers_translation'] kpi_sku_lists = \ [kpi_file_name, 'sku_lists', 'sku_lists'] kpi_range_targets = \ [kpi_file_name, 'range_targets', kpi_range_targets_sheet_names] jg = MARSRU_PRODJSONGenerator(project_name) kpis_sheet_name = None if kpi_channels: jg.create_template_json(kpi_channels[0], kpi_channels[1], kpi_channels[2]) store_types = jg.project_kpi_dict.get('channels') for store_type in store_types: if store_type['Store type'] == self.data_provider.store_type: kpis_sheet_name = store_type['KPIs_Channel'] break else: kpis_sheet_name = 'KPI' if not kpis_sheet_name: Log.warning( "Error: Store channel is not defined for Store ID [{}] with Store type [{}]" "".format( self.data_provider.store_fk, (self.data_provider.store_type if self.data_provider.store_type else '').encode('utf-8'))) return jg.create_template_json(kpi_template[0], kpi_template[1], kpis_sheet_name) jg.create_template_json(kpi_golden_shelves[0], kpi_golden_shelves[1], kpi_golden_shelves[2]) jg.create_template_json(kpi_answers_translation[0], kpi_answers_translation[1], kpi_answers_translation[2]) jg.create_template_json(kpi_sku_lists[0], kpi_sku_lists[1], kpi_sku_lists[2]) jg.create_template_json(kpi_range_targets[0], kpi_range_targets[1], kpi_range_targets[2]) kpi_templates = jg.project_kpi_dict tool_box = MARSRU_PRODKPIToolBox(kpi_templates, self.data_provider, self.output) # Todo - Uncomment the OSA before deploying!!! tool_box.handle_update_custom_scif() tool_box.calculate_osa() tool_box.check_availability(kpi_templates.get('kpi_data')) tool_box.check_survey_answer(kpi_templates.get('kpi_data')) tool_box.check_number_of_scenes(kpi_templates.get('kpi_data')) tool_box.custom_average_shelves(kpi_templates.get('kpi_data')) tool_box.custom_number_bays(kpi_templates.get('kpi_data')) # tool_box.check_price(kpi_templates.get('kpi_data')) # tool_box.brand_blocked_in_rectangle(kpi_templates.get('kpi_data')) tool_box.custom_marsru_1(kpi_templates.get('kpi_data')) tool_box.check_layout_size(kpi_templates.get('kpi_data')) tool_box.golden_shelves(kpi_templates.get('kpi_data')) tool_box.facings_by_brand(kpi_templates.get('kpi_data')) # tool_box.multiple_brands_blocked_in_rectangle(kpi_templates.get('kpi_data')) # tool_box.negative_neighbors(kpi_templates.get('kpi_data')) tool_box.get_total_linear(kpi_templates.get('kpi_data')) tool_box.get_placed_near(kpi_templates.get('kpi_data')) tool_box.check_availability_on_golden_shelves( kpi_templates.get('kpi_data')) tool_box.check_for_specific_display(kpi_templates.get('kpi_data')) # the order is important - source KPIs must be calculated first (above) tool_box.check_range_kpis(kpi_templates.get('kpi_data')) tool_box.check_kpi_results(kpi_templates.get('kpi_data')) tool_box.check_block_and_neighbors_by_shelf( kpi_templates.get('kpi_data')) kpi_sets = tool_box.results_and_scores.keys() kpi_sets.remove('*') # Saving to old tables for kpi_set in kpi_sets: tool_box.write_to_db_result_level1(kpi_set[0]) tool_box.commit_results_data() # Saving to new tables for kpi_set in kpi_sets: tool_box.store_to_new_kpi_tables_level0(kpi_set[1]) tool_box.common.commit_results_data() self.timer.stop( 'MARSRU2_SANDProjectCalculations.run_project_calculations')
def calculate_shelf_position(self): kpi_name = Const.KPI_POSITION kpi_fk = self.get_kpi_fk_by_kpi_type(kpi_name) parent_fk = self.get_parent_fk(kpi_name) parent_kpi_name = Const.KPIS_HIERACHY[kpi_name] grand_parent_fk = self.get_parent_fk(parent_kpi_name) grand_parent_kpi_name = Const.KPIS_HIERACHY[parent_kpi_name] great_grand_parent_fk = self.get_parent_fk(grand_parent_kpi_name) numerator_facings = 0 denominator_facings = 0 scene_ratios = [] if self.relevant_scif.empty: score = 0 self.write_to_db( fk=kpi_fk, numerator_id=self.manufacturer_pk, numerator_result=numerator_facings, denominator_id=self.store_id, denominator_result=denominator_facings, score=score, ) else: scene_ids = list(self.relevant_scif.scene_id.unique()) for scene_id in scene_ids: scene_name = self.relevant_scif['template_name'][ self.relevant_scif['scene_id'] == scene_id].iloc[0] scene_name_fk = self.relevant_scif['template_fk'][ self.relevant_scif['scene_id'] == scene_id].iloc[0] frentes_target_df = self.main_template[ self.main_template['NOMBRE DE TAREA'] == scene_name] ean_codes = frentes_target_df['PRODUCT EAN'].unique().tolist() ean_codes_count = len(ean_codes) passing_ean = 0 for ean_code in ean_codes: filtered_frentes_df = frentes_target_df[ frentes_target_df['PRODUCT EAN'] == ean_code] score = 100 if score != 0: try: bay = 0 shelf = 0 product_fk = self.all_products['product_fk'][ self.all_products['product_ean_code'] == str( ean_code)] if product_fk.empty: product_fk = -1 else: product_fk = self.all_products['product_fk'][ self.all_products['product_ean_code'] == str(ean_code)].iloc[0] for index, row in filtered_frentes_df.iterrows(): target_bay = row['PUERTA'] target_shelf = row['PARRILLA'] if str(ean_code) in self.relevant_scif[ 'product_ean_code'].tolist(): relevant_matches = self.matches[ self.matches['scene_fk'] == scene_id] filtered_matches_df = relevant_matches[ (relevant_matches['bay_number'] == target_bay) & (relevant_matches['shelf_number'] == target_shelf) & (relevant_matches['product_fk'] == product_fk)] if filtered_matches_df.empty: filtered_product_only_df = \ relevant_matches[['product_fk', 'bay_number', 'shelf_number']][ relevant_matches['product_fk'] == product_fk].drop_duplicates() if filtered_product_only_df.empty: score = 0 self.write_to_db( fk=kpi_fk, numerator_id=bay, numerator_result=target_bay, denominator_id=shelf, denominator_result=target_shelf, score=score, context_id=product_fk, result=ean_code, identifier_parent=parent_fk, identifier_result=kpi_fk, should_enter=True) else: for p_index, p_row in filtered_product_only_df.iterrows( ): score = 0 bay = p_row['bay_number'] shelf = p_row['shelf_number'] self.write_to_db( fk=kpi_fk, numerator_id=bay, numerator_result=target_bay, denominator_id=shelf, denominator_result= target_shelf, score=score, context_id=product_fk, result=ean_code, identifier_parent=parent_fk, identifier_result=kpi_fk, should_enter=True) else: score = 100 bay = target_bay shelf = target_shelf self.write_to_db( fk=kpi_fk, numerator_id=bay, numerator_result=target_bay, denominator_id=shelf, denominator_result=target_shelf, score=score, context_id=product_fk, result=ean_code, identifier_parent=parent_fk, identifier_result=kpi_fk, should_enter=True) else: score = 0 self.write_to_db( fk=kpi_fk, numerator_id=bay, numerator_result=target_bay, denominator_id=shelf, denominator_result=target_shelf, score=score, context_id=product_fk, result=ean_code, identifier_parent=parent_fk, identifier_result=kpi_fk, should_enter=True) except: Log.warning("Failed in acomodo") if ean_codes_count != 0: ratio = round(passing_ean / float(ean_codes_count), 2) * 100 else: ratio = 0 scene_ratios.append(ratio) self.write_to_db(fk=parent_fk, numerator_id=scene_name_fk, numerator_result=passing_ean, denominator_id=scene_id, denominator_result=ean_codes_count, result=ratio, score=ratio, identifier_parent=grand_parent_fk, identifier_result=parent_fk, should_enter=True) kpi_weight = Const.KPI_WEIGHTS[grand_parent_kpi_name] final_ratio = self.calculate_average_ratio(scene_ratios) score = round(((final_ratio * .01) * kpi_weight), 2) self.write_to_db(fk=grand_parent_fk, numerator_id=0, denominator_id=self.store_id, result=final_ratio, score=score, identifier_parent=great_grand_parent_fk, identifier_result=grand_parent_fk, should_enter=True) return final_ratio
def calculate_block_adjacency(self, kpi_line, relevant_scif): kpi_result = 0 for scene in relevant_scif.scene_fk.unique(): scene_filter = {'scene_fk': scene} location_filter = {'scene_id': scene} mpis = self.filter_df(self.mpis, scene_filter) mpis = mpis[mpis['stacking_layer'] >= 1] # allowed = {'product_type': ['Other', 'Empty']} if kpi_line[Const.TESTED_PARAM] == kpi_line[Const.ANCHOR_PARAM]: filters = { kpi_line[Const.ANCHOR_PARAM]: [ kpi_line[Const.ANCHOR_VALUE], kpi_line[Const.TESTED_VALUE] ] } elif kpi_line[Const.TESTED_PARAM] == '': filters = { kpi_line[Const.ANCHOR_PARAM]: kpi_line[Const.ANCHOR_VALUE] } else: filters = { kpi_line[Const.ANCHOR_PARAM]: kpi_line[Const.ANCHOR_VALUE], kpi_line[Const.TESTED_PARAM]: kpi_line[Const.TESTED_VALUE] } items = set(self.filter_df(mpis, filters)['scene_match_fk'].values) additional = {'minimum_facing_for_block': 2} # allowed_items = set(self.filter_df(mpis, allowed)['scene_match_fk'].values) if not (items): break block_result = self.block.network_x_block_together( filters, location=location_filter, additional=additional) passed_blocks = block_result[block_result['is_block'] == True].cluster.tolist() if passed_blocks and kpi_line[Const.LIST_ATTRIBUTE]: match_fk_list = set(match for cluster in passed_blocks for node in cluster.nodes() for match in cluster.node[node] ['group_attributes']['match_fk_list']) all_graph = AdjacencyGraph( mpis, None, self.products, product_attributes=['rect_x', 'rect_y'], name=None, adjacency_overlap_ratio=.4) # associate all nodes in the master graph to their associated match_fks match_to_node = { int(node['match_fk']): i for i, node in all_graph.base_adjacency_graph.nodes( data=True) } # create a dict of all match_fks to their corresponding nodes node_to_match = { val: key for key, val in match_to_node.items() } edge_matches = set( sum([[ node_to_match[i] for i in all_graph.base_adjacency_graph[ match_to_node[match]].keys() ] for match in match_fk_list], [])) adjacent_matches = edge_matches - match_fk_list adj_mpis = mpis[(mpis['scene_match_fk'].isin(adjacent_matches)) & (~mpis['product_type'].isin( ['Empty', 'Irrelevant', 'Other', 'POS']))] for value in adj_mpis[kpi_line[ Const.LIST_ATTRIBUTE]].unique().tolist(): if kpi_line[Const.LIST_ATTRIBUTE] == 'brand_name': numerator_fk = adj_mpis[adj_mpis['brand_name'] == value].brand_fk.values[0] else: if value is not None: try: numerator_fk = \ self.custom_entity_data[self.custom_entity_data['name'] == value].pk.values[0] except IndexError: Log.warning( 'Custom entity "{}" does not exist'.format( value)) continue else: continue result_dict = self.build_dictionary_for_db_insert( kpi_name=kpi_line[Const.KPI_NAME], numerator_id=numerator_fk, numerator_result=1, result=1, denominator_id=scene, denominator_result=1) self.common.write_to_db_result(**result_dict) return elif kpi_line[ Const. LIST_ATTRIBUTE]: # return if this is a list_attribute KPI with no passing blocks return if passed_blocks: # exit loop if this isn't a list_attribute KPI, but has passing blocks kpi_result = 1 break if kpi_line[ Const. LIST_ATTRIBUTE]: # handle cases where there are no relevant products, return # so we miss the other check above template_fk = relevant_scif['template_fk'].values[0] result_dict = self.build_dictionary_for_db_insert( kpi_name=kpi_line[Const.KPI_NAME], numerator_id=999, numerator_result=kpi_result, result=kpi_result, denominator_id=template_fk, denominator_result=1) self.common.write_to_db_result(**result_dict) return
def calculate_red_score(self): kpi_source_json = self.json.create_kpi_source('KPI_Source.xlsx', self.pos_kpi_set_name) self.kpi_source_json = kpi_source_json kpi_source = {} for row in kpi_source_json: # Log.info('SOURCE: {}'.format(row.get(SOURCE))) kpi_source[row.pop(SOURCE)] = row if kpi_source: pass # elif self.test_store == "Y": # Log.warning('Warning. Session cannot be calculated: ' # 'Store is a test store. ' # 'Store ID {1}.' # .format(self.pos_kpi_set_name, self.store_id)) # return else: Log.warning('Warning. Session cannot be calculated. ' 'POS KPI Set name in store attribute is invalid - {0}. ' 'Store ID {1}.' .format(self.pos_kpi_set_name, self.store_id)) return mr_targets = {} for kpi_set, params in kpi_source.items(): if params.get(MR_TARGET) is not None: mr_targets.update({kpi_set: params[MR_TARGET]}) mr_targets.update({params[SET]: params[MR_TARGET]}) self.tool_box.mr_targets = mr_targets kpi_sets_types_to_calculate = [POS, SPIRITS] for kpi_set_type in kpi_sets_types_to_calculate: if kpi_source[kpi_set_type][SET]: Log.debug('KPI calculation stage: {}'.format(kpi_source[kpi_set_type][SET])) self.tool_box.set_kpi_set(kpi_source[kpi_set_type][SET], kpi_set_type) self.json.project_kpi_dict['kpi_data'] = [] self.json.create_kpi_data_json('kpi_data', kpi_source[kpi_set_type][FILE], sheet_name=kpi_source[kpi_set_type][SHEET], pos_kpi_set_name=self.pos_kpi_set_name) self.calculate_red_score_kpi_set(self.json.project_kpi_dict.get('kpi_data')[0], kpi_source[kpi_set_type][SET], mr_targets.get(kpi_set_type)) if kpi_source[GAPS][SET]: Log.debug('KPI calculation stage: {}'.format(kpi_source[GAPS][SET])) self.tool_box.set_kpi_set(kpi_source[GAPS][SET], GAPS) self.json.create_kpi_data_json( 'gaps', kpi_source[GAPS][FILE], sheet_name=kpi_source[GAPS][SHEET]) self.tool_box.calculate_gaps_old(self.json.project_kpi_dict.get('gaps')) self.tool_box.calculate_gaps_new(self.json.project_kpi_dict.get('gaps'), kpi_source[GAPS][SET]) if kpi_source[BENCHMARK][SET]: Log.debug('KPI calculation stage: {}'.format(kpi_source[BENCHMARK][SET])) self.tool_box.set_kpi_set(kpi_source[BENCHMARK][SET], BENCHMARK) self.json.create_kpi_data_json( 'benchmark', kpi_source[BENCHMARK][FILE], sheet_name=kpi_source[BENCHMARK][SHEET]) self.tool_box.calculate_benchmark(self.json.project_kpi_dict.get('benchmark'), kpi_source[BENCHMARK][SET]) if kpi_source[CONTRACT][FILE]: Log.debug('Importing Contract Execution template') self.json.create_kpi_data_json( 'contract', kpi_source[CONTRACT][FILE], sheet_name=kpi_source[CONTRACT][SHEET]) if kpi_source[TOPSKU][SET]: Log.debug('KPI calculation stage: {}'.format(kpi_source[TOPSKU][SET])) include_to_contract = True if self.json.project_kpi_dict.get('contract') else False self.tool_box.set_kpi_set(kpi_source[TOPSKU][SET], TOPSKU) self.tool_box.calculate_top_sku(include_to_contract, kpi_source[TOPSKU][SET]) if self.json.project_kpi_dict.get('contract'): if kpi_source[EQUIPMENT][SET]: equipment_target_data = self.tool_box.get_equipment_target_data() if equipment_target_data: if kpi_source[TARGET][SET]: Log.debug('KPI calculation stage: {}'.format(kpi_source[TARGET][SET])) self.tool_box.set_kpi_set(kpi_source[TARGET][SET], TARGET) self.json.project_kpi_dict['kpi_data'] = [] self.json.create_kpi_data_json('kpi_data', kpi_source[TARGET][FILE], sheet_name=kpi_source[TARGET][SHEET]) self.calculate_red_score_kpi_set(self.json.project_kpi_dict.get('kpi_data')[0], kpi_source[TARGET][SET]) Log.debug('KPI calculation stage: {}'.format(kpi_source[EQUIPMENT][SET])) self.tool_box.set_kpi_set(kpi_source[EQUIPMENT][SET], EQUIPMENT) self.tool_box.calculate_equipment_execution(self.json.project_kpi_dict.get('contract'), kpi_source[EQUIPMENT][SET], kpi_source[KPI_CONVERSION][FILE], equipment_target_data) if kpi_source[CONTRACT][SET]: Log.debug('KPI calculation stage: {}'.format(kpi_source[CONTRACT][SET])) self.tool_box.set_kpi_set(kpi_source[CONTRACT][SET], CONTRACT) self.tool_box.calculate_contract_execution(self.json.project_kpi_dict.get('contract'), kpi_source[CONTRACT][SET])
def scene_score(self): if self.scene_tool_box.match_product_in_scene.empty: Log.warning('Match product in scene is empty for this scene') else: self.scene_tool_box.main_function() self.common.commit_results_data()
def calculate_adjacency(self, kpi_line, relevant_scif): kpi_result = 0 for scene in relevant_scif.scene_fk.unique(): scene_filter = {'scene_fk': scene} mpis = self.filter_df(self.mpis, scene_filter) mpis = mpis[mpis['stacking_layer'] >= 1] # allowed = {'product_type': ['Other', 'Empty']} filters = { kpi_line[Const.ANCHOR_PARAM]: kpi_line[Const.ANCHOR_VALUE] } # determine if there are any matching products in the scene items = set(self.filter_df(mpis, filters)['scene_match_fk'].values) # allowed_items = set(self.filter_df(mpis, allowed)['scene_match_fk'].values) # items.update(allowed_items) if not (items): break all_graph = AdjacencyGraph(mpis, None, self.products, product_attributes=['rect_x', 'rect_y'], name=None, adjacency_overlap_ratio=.4) match_to_node = { int(node['match_fk']): i for i, node in all_graph.base_adjacency_graph.nodes(data=True) } node_to_match = {val: key for key, val in match_to_node.items()} edge_matches = set( sum([[ node_to_match[i] for i in all_graph.base_adjacency_graph[ match_to_node[item]].keys() ] for item in items], [])) adjacent_items = edge_matches - items adj_mpis = mpis[(mpis['scene_match_fk'].isin(adjacent_items)) & (~mpis['product_type'].isin( ['Empty', 'Irrelevant', 'Other', 'POS']))] if kpi_line[Const.LIST_ATTRIBUTE]: for value in adj_mpis[kpi_line[ Const.LIST_ATTRIBUTE]].unique().tolist(): if kpi_line[Const.LIST_ATTRIBUTE] == 'brand_name': numerator_fk = adj_mpis[adj_mpis['brand_name'] == value].brand_fk.values[0] else: if value is not None: try: numerator_fk = \ self.custom_entity_data[self.custom_entity_data['name'] == value].pk.values[0] except IndexError: Log.warning( 'Custom entity "{}" does not exist'.format( value)) continue else: continue result_dict = self.build_dictionary_for_db_insert( kpi_name=kpi_line[Const.KPI_NAME], numerator_id=numerator_fk, numerator_result=1, result=1, denominator_id=scene, denominator_result=1) self.common.write_to_db_result(**result_dict) return else: if kpi_line[Const.TESTED_VALUE] in adj_mpis[kpi_line[ Const.TESTED_PARAM]].unique().tolist(): kpi_result = 1 break if kpi_line[ Const. LIST_ATTRIBUTE]: # handle cases where there are no relevant products, return # so we miss the other check above template_fk = relevant_scif['template_fk'].values[0] result_dict = self.build_dictionary_for_db_insert( kpi_name=kpi_line[Const.KPI_NAME], numerator_id=999, numerator_result=kpi_result, result=kpi_result, denominator_id=template_fk, denominator_result=1) self.common.write_to_db_result(**result_dict)
def _get_own_manufacturer(self): """ Gets own_manufacturer fk according to the assortment product list """ if self._data_provider.own_manufacturer.empty: Log.warning('This project doesnt have own manufacturer ') return int(self._data_provider.own_manufacturer['param_value'].iloc[0])
def calculate_nestle_score(self, set_name): """ This function calculates the KPI results. """ set_scores = {} main_children = self.templates_data[self.templates_data[ self.templates_class.KPI_GROUP] == set_name] for c in xrange(len(main_children)): main_child = main_children.iloc[c] children = self.templates_data[self.templates_data[ self.templates_class.KPI_GROUP] == main_child[ self.templates_class.KPI_NAME]] scores = [] for i in xrange(len(children)): child = children.iloc[i] kpi_type = child[self.templates_class.KPI_TYPE] if not self.store_type in child[ self.templates_class.STORE_TYPE]: continue if not set(child[self.templates_class.SCENE_TYPE].split( self.templates_class.SEPARATOR)) & set( self.scif['template_name'].unique().tolist()): continue if kpi_type == self.BLOCK_TOGETHER: score = self.calculate_block_together_sets(child) elif kpi_type == self.FACING_COUNT: score = self.calculate_facing_count(child) elif kpi_type == self.AVAILABILITY: score = self.calculate_availability(child) elif kpi_type == self.FACING_SOS: score = self.calculate_facing_sos(child) elif kpi_type == self.SHELF_POSITION: score = self.calculate_shelf_position(child) else: Log.warning( "KPI of type '{}' is not supported".format(kpi_type)) continue if score is not None: child_score_weight = child[self.templates_class.WEIGHT] atomic_fk = self.get_atomic_fk(child) self.write_to_db_result(atomic_fk, score, level=self.LEVEL3) if isinstance(score, tuple): score = score[0] weighted_score = score * float(child_score_weight) scores.append(weighted_score) if not scores: scores = [0] if scores: score_type = main_child[self.templates_class.SCORE] score_weight = float(main_child[self.templates_class.WEIGHT]) if score_type == self.templates_class.SUM_OF_SCORES: score = sum(scores) else: score = 0 kpi_name = main_child[self.templates_class.KPI_NAME] kpi_fk = self.kpi_static_data[self.kpi_static_data['kpi_name'] == kpi_name]['kpi_fk'].values[0] # self.write_to_db_result(kpi_fk, score, level=self.LEVEL2) set_scores[kpi_fk] = (score_weight, score) # total_weight = sum([score[0] for score in set_scores.values()]) for kpi_fk in set_scores.keys(): self.write_to_db_result(kpi_fk, set_scores[kpi_fk][1], level=self.LEVEL2) # set_score = sum([score[0] * score[1] for score in set_scores.values()]) / total_weight set_score = round( sum([score[0] * score[1] for score in set_scores.values()]), 2) 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, level=self.LEVEL1)
def calculate_display_facing(self, kpi_df): kpi_name = kpi_df[Const.KPI_NAME].values[0] kpi_fk = self.common.get_kpi_fk_by_kpi_name(kpi_name, get_numerator=False) if kpi_fk is None: Log.warning("There is no matching Kpi fk for kpi name: " + kpi_name) return entity_name = kpi_df[Const.NUMERATOR_ENTITY].values[0] entity_name_for_fk = Const.NAME_TO_FK[entity_name] results_dict = {} # Iterate all rows of the KPI, each is calculated differently, # added to an aggregated dictionary on the end of the loops for i, row in kpi_df.iterrows(): scene_size = row[Const.SCENE_SIZE] self.kpi_excluding = row[[ Const.EXCLUDE_EMPTY, Const.EXCLUDE_HANGER, Const.EXCLUDE_IRRELEVANT, Const.EXCLUDE_POSM, Const.EXCLUDE_OTHER, Const.STACKING, Const.EXCLUDE_SKU, Const.EXCLUDE_STOCK, Const.EXCLUDE_OSD ]] # filter df to the specific template row df = self.filter_df(row) df = df[df['width_mm_advance'] != -1] if df.empty: continue number_of_scenes = len(df['scene_fk'].unique()) if row[Const.PER_SCENE_TYPE] == Const.EACH: scene_types = row[Const.SCENE_TYPE].split(",") scene_types = [item.strip() for item in scene_types] scene_types = set(scene_types).intersection( set(df['template_name'])) else: scene_types = [""] # Iterate scene types for sc in scene_types: filters = {} if sc != "": try: context_id = self.templates[ self.templates['template_name'] == sc]['template_fk'].iloc[0] except Exception as ex: Log.warning("No scene type with the following name: " + str(sc) + ", warning: " + str(ex)) continue filters['template_name'] = sc if scene_size != "": scenes = df['scene_fk'].unique() else: scenes = [""] else: context_id = 0 # if scene_size != "": # scene_size *= number_of_scenes scenes = [""] # Iterate scenes, inorder to get exact ratios between each scene with fixed 4000 mm size for scene in scenes: # If iterating scenes, replacing all current filters with {scene_fk: scene} if scene != "": filters = {'scene_fk': scene} category = row[Const.CATEGORY] if category != "": if category == Const.EACH: categories = set(self.df['category']) else: categories = [category] else: categories = [""] # Iterate categories for category in categories: if category != "": denominator_id = self.all_products[ self.all_products['category'] == category]['category_fk'].iloc[0] filters['category'] = category all_numerators = self.df[ self.df['category'] == category][entity_name].drop_duplicates( ).values.tolist() else: denominator_id = self.store_id all_numerators = df[entity_name].drop_duplicates( ).values.tolist() if row[Const.NUMERATOR] != "": all_numerators = [row[Const.NUMERATOR]] denominator = df[self.tools.get_filter_condition( df, **filters)].shape[0] if denominator == 0: continue elif scene_size != "": denominator *= scene_size # Iterate entities (manufacturer / product_fk...) for entity in all_numerators: filters[entity_name] = entity numerator = df[self.tools.get_filter_condition( df, **filters)].shape[0] del filters[entity_name] if scene_size != "": numerator *= scene_size try: numerator_id = self.all_products[ self.all_products[entity_name] == entity][entity_name_for_fk].values[0] except Exception as ex: Log.warning("No entity in this name " + entity + ", warning: " + str(ex)) numerator_id = -1 if (numerator_id, denominator_id, context_id) not in results_dict.keys(): results_dict[numerator_id, denominator_id, context_id] = [ numerator, denominator ] else: results_dict[numerator_id, denominator_id, context_id] = \ map(sum, zip(results_dict[numerator_id, denominator_id, context_id], [numerator, denominator])) if len(results_dict) == 0: return self.save_result_to_kpi_table(kpi_fk, results_dict)
def handle_sos_packs_atomics(self,atomic_id, atomic_name, parent_kpi): count_result = 0 # bring the kpi rows from the sos sheet rows = self.sos_packs_sheet.loc[self.sos_packs_sheet[Const.KPI_ID] == atomic_id] # get a single row row = self.find_row(rows) if row.empty: return # enter only if there is a matching store, region and state target = row[Const.TARGET].values[0] target_secondary = row[Const.SECONDARY_TARGET].values[0] target_packs = row[Const.PACKS_TARGET].values[0] result_type = row[Const.RESULT_TYPE].values[0] weight = row[Const.WEIGHT].values[0] # get the filters filters = self.get_filters_from_row(row.squeeze()) df = self.match_product_in_scene.copy() df = pd.merge(df, self.all_products, on="product_fk") product_size = row[Const.PRODUCT_SIZE].values[0] if product_size != "": df = self.filter_product_size(df, product_size) df_packs = self.count_of_scenes_packs(df, filters) df_packs = df_packs[df_packs['num_packs'] >= target_packs] number_of_valid_scenes = len(df_packs) if (number_of_valid_scenes >= target_secondary): count_result = weight # count number of facings if ('form_factor' in filters.keys()): del filters['form_factor'] df_numirator = self.count_of_scenes_facings(df,filters) # count_of_total_facings = self.count_of_facings(df,filters) df_numirator = df_numirator.rename(columns={'face_count': 'facings_nom'}) count_of_total_facings = df_numirator['facings_nom'].sum() for f in ['manufacturer_name', 'brand_name']: if f in filters: del filters[f] df_denominator = self.count_of_scenes_facings(df, filters) scene_types_groupby = pd.merge(df_numirator, df_denominator, how='left', on='scene_fk') df_target_filtered = scene_types_groupby[(scene_types_groupby['facings_nom'] / scene_types_groupby['face_count']) * 100 >= target] number_of_valid_scenes = len(df_target_filtered) if target_secondary == "": target_secondary = 1 if number_of_valid_scenes >= target_secondary: count_result = weight number_of_valid_scenes = len(set(df_target_filtered['scene_fk']).union(set(df_packs['scene_fk']))) number_of_not_valid_scenes = len(df_denominator['scene_fk'].drop_duplicates()) try: atomic_pk = self.common_db.get_kpi_fk_by_kpi_type(atomic_name) except IndexError: Log.warning("There is no matching Kpi fk for kpi name: " + atomic_name) return self.parent_kpis[parent_kpi] += count_result if result_type == 1: self.common_db.write_to_db_result(fk=atomic_pk, numerator_id=1, denominator_id=self.store_fk, denominator_result_after_actions=3, numerator_result=number_of_valid_scenes, identifier_parent=parent_kpi, denominator_result=number_of_not_valid_scenes, result=count_result, should_enter=True) elif result_type == 2: self.common_db.write_to_db_result(fk=atomic_pk, numerator_id=1, denominator_id=self.store_fk, numerator_result=count_of_total_facings, identifier_parent=parent_kpi, denominator_result_after_actions=2, denominator_result=0, result=count_result, should_enter=True) elif result_type == 3: self.common_db.write_to_db_result(fk=atomic_pk, numerator_id=1, denominator_id=self.store_fk, numerator_result=number_of_valid_scenes, identifier_parent=parent_kpi, denominator_result_after_actions=2, denominator_result=0, result=count_result, should_enter=True)
def calculate_block_together(self, allowed_products_filters=None, include_empty=EXCLUDE_EMPTY, **filters): """ :param allowed_products_filters: These are the parameters which are allowed to corrupt the block without failing it. :param include_empty: This parameter dictates whether or not to discard Empty-typed products. :param filters: These are the parameters which the blocks are checked for. :return: True - if in (at least) one of the scenes all the relevant SKUs are grouped together in one block; otherwise - returns False. """ relevant_scenes = self.scif[self.get_filter_condition(self.scif, **filters)]['scene_id'].unique().tolist() for field in filters.keys(): if field not in self.all_products.columns: filters.pop(field, None) if relevant_scenes: for scene in relevant_scenes: scene_graph = self.position_graphs.get(scene).copy() relevant_vertices = None for field in filters.keys(): values = filters[field] if isinstance(filters[field], (list, float)) else [filters[field]] vertices_for_field = set() for value in values: condition = {field: value} vertices = {v.index for v in scene_graph.vs.select(**condition)} vertices_for_field = vertices_for_field.union(vertices) if relevant_vertices is None: relevant_vertices = vertices_for_field else: relevant_vertices = relevant_vertices.intersection(vertices_for_field) if allowed_products_filters: allowed_vertices = None for field in allowed_products_filters.keys(): values = allowed_products_filters[field] \ if isinstance(allowed_products_filters[field], (list, float)) \ else [allowed_products_filters[field]] vertices_for_field = set() for value in values: condition = {field: value} vertices = {v.index for v in scene_graph.vs.select(**condition)} vertices_for_field = vertices_for_field.union(vertices) if allowed_vertices is None: allowed_vertices = vertices_for_field else: allowed_vertices = allowed_vertices.intersection(vertices_for_field) if include_empty == self.EXCLUDE_EMPTY: try: empty_vertices = {v.index for v in scene_graph.vs.select(product_type='Empty')} allowed_vertices = allowed_vertices.union(empty_vertices) except KeyError: Log.warning("Entity 'product_type' doesn't appear in the vertex attributes") relevant_vertices = relevant_vertices if relevant_vertices is not None else set() allowed_vertices = allowed_vertices if allowed_vertices is not None else set() else: allowed_vertices = [] all_vertices = {v.index for v in scene_graph.vs} vertices_to_remove = all_vertices.difference(relevant_vertices.union(allowed_vertices)) scene_graph.delete_vertices(vertices_to_remove) # removing clusters including 'allowed' SKUs only clusters = [cluster for cluster in scene_graph.clusters() if set(cluster).difference(allowed_vertices)] if len(clusters) == 1: return True else: Log.debug('None of the scenes contain relevant SKUs') return False
def handle_prod_seq_2_atomics(self, atomic_id, atomic_name, parent_kpi): count_result = 0 result = 0 # bring the kpi rows in the PROD_SEQ_2 sheet rows = self.prod_seq_2_sheet.loc[self.prod_seq_2_sheet[Const.KPI_ID] == atomic_id] # get a the correct rows temp = rows[Const.STORE_TYPE_TEMPLATE] matching_row = rows[(temp.apply(lambda r: self.store_type_filter in r.split(","))) | (temp == "")] groups_outside = matching_row[Const.BRAND_GROUP_OUTSIDE].values[0].split(',') groups_inside = matching_row[Const.BRAND_GROUP_INSIDE].values[0].split(',') del matching_row[Const.BRAND_GROUP_OUTSIDE] del matching_row[Const.BRAND_GROUP_INSIDE] filters = self.get_filters_from_row(matching_row.squeeze()) scenes = self.get_scene_list(filters) matches = self.match_product_in_scene.copy() matches = matches[matches['scene_fk'].isin(scenes)] matches_merged = pd.merge(matches, self.all_products, how='left', on='product_fk').fillna(0) matches_merged_ns = matches_merged[matches_merged['stacking_layer'] == 1] filtered_shelfs = matches_merged_ns[matches_merged_ns['brand_name'].isin(groups_inside)][['scene_fk', 'bay_number', 'shelf_number']].drop_duplicates() denominator_shelfs = len(filtered_shelfs) numerator_shelfs = 0 for i,row in filtered_shelfs.iterrows(): working_shelf = matches_merged_ns[(matches_merged_ns['scene_fk'] == row['scene_fk']) & (matches_merged_ns[ 'bay_number'] == row['bay_number']) & (matches_merged_ns['shelf_number'] == row['shelf_number'])] if len(groups_inside) == 1: check = True for g in groups_outside: if len(working_shelf[working_shelf['brand_name'] == g]) == 0: check = False if check == False: continue else: if len(working_shelf[working_shelf['brand_name'].isin(groups_outside)]) == 0: continue result = self.check_order_prod_seq_2(working_shelf, groups_outside, groups_inside) if result: numerator_shelfs += 1 target = matching_row[Const.TARGET].values[0] weight = matching_row[Const.WEIGHT].values[0] if ((denominator_shelfs != 0) and ((numerator_shelfs / denominator_shelfs) * 100 >= target)): count_result = weight if result == 0: return try: atomic_pk = self.common_db.get_kpi_fk_by_kpi_type(atomic_name) except IndexError: Log.warning("There is no matching Kpi fk for kpi name: " + atomic_name) return self.parent_kpis[parent_kpi] += count_result self.common_db.write_to_db_result(fk=atomic_pk, numerator_id=1, identifier_parent=parent_kpi, denominator_result_after_actions=1, numerator_result=numerator_shelfs, denominator_id=self.store_fk, should_enter=True, denominator_result=denominator_shelfs, result=count_result)
def calculate_oos_target(self): temp = self.oos_sheet[Const.TEMPLATE_STORE_TYPE] rows_stores_filter = self.oos_sheet[ temp.apply(lambda r: self.store_type_filter in [item.strip() for item in r.split(",")])] if rows_stores_filter.empty: weight = 0 else: weight = rows_stores_filter[Const.TEMPLATE_SCORE].values[0] all_data = pd.merge( self.scif[["store_id", "product_fk", "facings", "template_name"]], self.store_info, left_on="store_id", right_on="store_fk") if all_data.empty: return 0 json_policies = self.oos_policies.copy() json_policies[Const.POLICY] = self.oos_policies[Const.POLICY].apply( lambda line: json.loads(line)) diff_policies = json_policies[ Const.POLICY].drop_duplicates().reset_index() diff_table = json_normalize(diff_policies[Const.POLICY].tolist()) # remove all lists from df diff_table = diff_table.applymap(lambda x: x[0] if isinstance(x, list) else x) for col in diff_table.columns: diff_table[col] = diff_table[col].str.encode('utf-8') att = all_data.iloc[0][col].encode('utf-8') if att is None: return 0 diff_table = diff_table[diff_table[col] == att] if diff_table.shape[0] > 1: Log.warning("There is more than one possible match") return 0 if diff_table.empty: Log.warning("There is no matching policy for {}".format(str(all_data[diff_table.columns].iloc[0, :]\ .to_dict()))) return 0 selected_row = diff_policies.iloc[diff_table.index[0]][Const.POLICY] json_policies = json_policies[json_policies[Const.POLICY] == selected_row] products_to_check = json_policies['product_fk'].tolist() products_df = all_data[( all_data['product_fk'].isin(products_to_check))][[ 'product_fk', 'facings' ]].fillna(0) products_df = products_df.groupby('product_fk').sum().reset_index() try: atomic_pk_sku = self.common_v2.get_kpi_fk_by_kpi_name( Const.OOS_SKU_KPI) except IndexError: Log.warning("There is no matching Kpi fk for kpi name: " + Const.OOS_SKU_KPI) return 0 for product in products_to_check: if product not in products_df['product_fk'].values: products_df = products_df.append( { 'product_fk': product, 'facings': 0.0 }, ignore_index=True) for index, row in products_df.iterrows(): result = 0 if row['facings'] > 0 else 1 self.common_v2.write_to_db_result(fk=atomic_pk_sku, numerator_id=row['product_fk'], numerator_result=row['facings'], denominator_id=self.store_id, result=result, score=result, identifier_parent=Const.OOS_KPI, should_enter=True, parent_fk=3) not_existing_products_len = len( products_df[products_df['facings'] == 0]) result = not_existing_products_len / float(len(products_to_check)) try: atomic_pk = self.common_v2.get_kpi_fk_by_kpi_name(Const.OOS_KPI) result_oos_pk = self.common_v2.get_kpi_fk_by_kpi_name( Const.OOS_RESULT_KPI) except IndexError: Log.warning("There is no matching Kpi fk for kpi name: " + Const.OOS_KPI) return 0 score = result * weight self.common_v2.write_to_db_result( fk=atomic_pk, numerator_id=self.region_fk, numerator_result=not_existing_products_len, denominator_id=self.store_id, denominator_result=len(products_to_check), result=result, score=score, identifier_result=Const.OOS_KPI, parent_fk=3) self.common_v2.write_to_db_result( fk=result_oos_pk, numerator_id=self.region_fk, numerator_result=not_existing_products_len, denominator_id=self.store_id, denominator_result=len(products_to_check), result=result, score=result, parent_fk=3) return score
def calculate_distribution(self): kpi_name = Const.KPI_DISTRIBUTION kpi_fk = self.get_kpi_fk_by_kpi_type(kpi_name) parent_fk = self.get_parent_fk(kpi_name) parent_kpi_name = Const.KPIS_HIERACHY[kpi_name] grand_parent_fk = self.get_parent_fk(parent_kpi_name) kpi_weight = Const.KPI_WEIGHTS[parent_kpi_name] # place holding these for now, will fix tomorrow feb 19 score = 0 numerator_facings = 0 denominator_facings = 0 total_ean_codes = 0 if self.relevant_scif.empty: score = 0 self.write_to_db(fk=kpi_fk, numerator_id=self.manufacturer_pk, numerator_result=numerator_facings, denominator_id=self.store_id, denominator_result=denominator_facings, score=score, identifier_parent=parent_fk, identifier_result=kpi_fk, should_enter=True) else: scene_ids = list(self.relevant_scif.scene_id.unique()) for scene_id in scene_ids: scene_name = self.relevant_scif['template_name'][ self.relevant_scif['scene_id'] == scene_id].iloc[0] template_name_fk = self.relevant_scif['template_fk'][ self.relevant_scif['scene_id'] == scene_id].iloc[0] frentes_target_df = self.main_template[ self.main_template['NOMBRE DE TAREA'] == scene_name] ean_codes = frentes_target_df['PRODUCT EAN'].unique().tolist() ean_code_count = len(ean_codes) total_ean_codes += ean_code_count found_ean = 0 for ean_code in ean_codes: try: found_sku_df = self.relevant_scif[ self.relevant_scif['product_ean_code'] == str( ean_code)] if found_sku_df.empty: score = 0 found_ean += 1 else: score = 100 except: Log.warning("Distribution KPI Failed.") self.write_to_db(fk=kpi_fk, numerator_id=scene_id, numerator_result=ean_code, denominator_id=template_name_fk, result=score, score=score, identifier_parent=parent_fk, identifier_result=kpi_fk, should_enter=True) if total_ean_codes != 0: numerator_facings = found_ean denominator_facings = total_ean_codes ratio = round(numerator_facings / float(denominator_facings), 2) * 100 else: ratio = 0 score = round(((ratio * .01) * kpi_weight), 2) self.write_to_db(fk=parent_fk, numerator_id=numerator_facings, denominator_id=denominator_facings, result=ratio, score=score, identifier_parent=grand_parent_fk, identifier_result=parent_fk, should_enter=True) return ratio
def handle_sos_target_atomics(self, atomic_id, atomic_name, parent_name): denominator_number_of_total_facings = 0 count_result = -1 # bring the kpi rows from the sos sheet rows = self.sos_target_sheet.loc[self.sos_target_sheet[ Const.TEMPLATE_KPI_ID] == atomic_id] # get a single row row = self.find_row(rows) if row.empty: return 0 target = row[Const.TEMPLATE_TARGET_PRECENT].values[0] score = row[Const.TEMPLATE_SCORE].values[0] df = pd.merge(self.scif, self.store_info, how="left", left_on="store_id", right_on="store_fk") # get the filters filters = self.get_filters_from_row(row.squeeze()) numerator_number_of_facings = self.count_of_facings(df, filters) if numerator_number_of_facings != 0 and count_result == -1: if 'manufacturer_name' in filters.keys(): deno_manufacturer = row[ Const.TEMPLATE_MANUFACTURER_DENOMINATOR].values[0].strip() deno_manufacturer = deno_manufacturer.split(",") filters['manufacturer_name'] = [ item.strip() for item in deno_manufacturer ] denominator_number_of_total_facings = self.count_of_facings( df, filters) percentage = 100 * (numerator_number_of_facings / denominator_number_of_total_facings) count_result = score if percentage >= target else -1 if count_result == -1: return 0 try: atomic_pk = self.common_v2.get_kpi_fk_by_kpi_name(atomic_name) except IndexError: Log.warning("There is no matching Kpi fk for kpi name: " + atomic_name) return 0 self.common_v2.write_to_db_result( fk=atomic_pk, numerator_id=self.region_fk, numerator_result=numerator_number_of_facings, denominator_id=self.store_id, denominator_result=denominator_number_of_total_facings, result=count_result, score=count_result, identifier_result=atomic_name, identifier_parent=parent_name, should_enter=True, parent_fk=3) return count_result
def calculate_self_purity(self, kpi_data): score = 0 if kpi_data.empty: return group_name = kpi_data.iloc[0][CCMY_SANDConsts.KPI_GROUP] identifier_parent = self.common.get_dictionary(kpi_name=group_name) scene_types = self.get_scene_types(kpi_data.iloc[0]) self_purity_scene_list = self.scene_info[ self.scene_info['template_name'].isin(scene_types)][ CCMY_SANDConsts.SCENE_FK].unique().tolist() template_fk = self.scene_info[self.scene_info['template_name'].isin( scene_types)][CCMY_SANDConsts.TEMPLATE_FK].unique().tolist() template_fk = template_fk[0] if template_fk != [] else \ self.data_provider.all_templates[self.data_provider.all_templates['template_name'].isin( scene_types)]['template_fk'].iloc[0] df_all_shelfs = self.match_product_in_scene if self.match_product_in_scene.empty: return 0, 0, 0, template_fk df_all_shelfs_products = df_all_shelfs.merge( self.products, how='inner', on=CCMY_SANDConsts.PRODUCT_FK) list_columns = [ CCMY_SANDConsts.SCENE_FK, CCMY_SANDConsts.BAY_NUMBER, CCMY_SANDConsts.SHELF_NUMBER, CCMY_SANDConsts.MANUFACTURER_FK, CCMY_SANDConsts.PRODUCT_FK ] df_all_shelfs_products = pd.DataFrame( df_all_shelfs_products.groupby(list_columns).size().reset_index( name='count')) df_all_shelfs_products = df_all_shelfs_products[df_all_shelfs_products[ CCMY_SANDConsts.SCENE_FK].isin(self_purity_scene_list)] df_shelf_pure = df_all_shelfs_products[[ CCMY_SANDConsts.SCENE_FK, CCMY_SANDConsts.BAY_NUMBER, CCMY_SANDConsts.SHELF_NUMBER ]] df_shelf_pure.drop_duplicates(subset=None, keep='first', inplace=True) df_shelf_pure[CCMY_SANDConsts.IS_PURE] = CCMY_SANDConsts.PURE for x, params in kpi_data.iterrows(): for row_num_x, row_data_x in df_shelf_pure.iterrows(): for row_num_y, row_data_y in df_all_shelfs_products.iterrows(): if ((row_data_x[CCMY_SANDConsts.SCENE_FK] == row_data_y[CCMY_SANDConsts.SCENE_FK]) & (row_data_x[CCMY_SANDConsts.BAY_NUMBER] == row_data_y[CCMY_SANDConsts.BAY_NUMBER]) & (row_data_x[CCMY_SANDConsts.SHELF_NUMBER] == row_data_y[CCMY_SANDConsts.SHELF_NUMBER]) & (row_data_y[CCMY_SANDConsts.MANUFACTURER_FK] == CCMY_SANDConsts.GENERAL_MANUFACTURER) & (row_data_y[CCMY_SANDConsts.PRODUCT_FK] == CCMY_SANDConsts.IRRELEVANT) & (row_data_x[CCMY_SANDConsts.IS_PURE] == CCMY_SANDConsts.PURE)): row_data_x[ CCMY_SANDConsts.IS_PURE] = CCMY_SANDConsts.IMPURE print "Impure Shelf={}".format( row_data_y[CCMY_SANDConsts.SHELF_NUMBER]) continue elif ((row_data_x[CCMY_SANDConsts.SCENE_FK] == row_data_y[CCMY_SANDConsts.SCENE_FK]) & (row_data_x[CCMY_SANDConsts.BAY_NUMBER] == row_data_y[CCMY_SANDConsts.BAY_NUMBER]) & (row_data_x[CCMY_SANDConsts.SHELF_NUMBER] == row_data_y[CCMY_SANDConsts.SHELF_NUMBER]) & (row_data_y[CCMY_SANDConsts.MANUFACTURER_FK] not in [ CCMY_SANDConsts.CCBM, CCMY_SANDConsts.GENERAL_MANUFACTURER ]) & (row_data_y[CCMY_SANDConsts.PRODUCT_FK] != CCMY_SANDConsts.GENERAL_EMPTY_PRODUCT) & (row_data_x[CCMY_SANDConsts.IS_PURE] == CCMY_SANDConsts.PURE)): row_data_x[ CCMY_SANDConsts.IS_PURE] = CCMY_SANDConsts.IMPURE print "Impure Shelf={}".format( row_data_y[CCMY_SANDConsts.SHELF_NUMBER]) continue elif ((row_data_x[CCMY_SANDConsts.SCENE_FK] == row_data_y[CCMY_SANDConsts.SCENE_FK]) & (row_data_x[CCMY_SANDConsts.BAY_NUMBER] == row_data_y[CCMY_SANDConsts.BAY_NUMBER]) & (row_data_x[CCMY_SANDConsts.SHELF_NUMBER] == row_data_y[CCMY_SANDConsts.SHELF_NUMBER]) & (row_data_y[CCMY_SANDConsts.MANUFACTURER_FK] == CCMY_SANDConsts.GENERAL_MANUFACTURER) & (row_data_y[CCMY_SANDConsts.PRODUCT_FK] != CCMY_SANDConsts.GENERAL_EMPTY_PRODUCT) & (row_data_x[CCMY_SANDConsts.IS_PURE] == CCMY_SANDConsts.PURE)): row_data_x[ CCMY_SANDConsts.IS_PURE] = CCMY_SANDConsts.IMPURE print "Impure Shelf={}".format( row_data_y[CCMY_SANDConsts.SHELF_NUMBER]) continue num_of_pure_shelves = 0 if df_shelf_pure.empty else df_shelf_pure[ CCMY_SANDConsts.IS_PURE].sum() total_num_of_shelves = 0 if df_shelf_pure.empty else len( df_shelf_pure) score = score if total_num_of_shelves == 0 else ( num_of_pure_shelves / float(total_num_of_shelves)) df_atomic_kpi = self.kpi_static_data[(self.kpi_static_data[ CCMY_SANDConsts.ATOMIC_KPI_NAME] == params['KPI Name']) & ( self.kpi_static_data['kpi_name'] == group_name)] if df_atomic_kpi.empty: Log.warning("kpi {} from template, doesn't exist in DB".format( params['KPI Name'])) return 0 atomic_kpi_fk = df_atomic_kpi.iloc[0][ CCMY_SANDConsts.ATOMIC_KPI_FK] result = num_of_pure_shelves if df_atomic_kpi[CCMY_SANDConsts.ATOMIC_KPI_NAME].iloc[0] == \ CCMY_SANDConsts.KPI_NUM_PURE_SHELVES else total_num_of_shelves self.write_to_db_result(atomic_kpi_fk, (result, result, 0), level=self.LEVEL3) self.insert_db_new_results(params['KPI Name'], score, score, result, result, target=0, identifier_parent=identifier_parent) return num_of_pure_shelves, total_num_of_shelves, score, template_fk
def handle_survey_combo(self, atomic_id, atomic_name, parent_name): # bring the kpi rows from the survey sheet numerator = denominator = 0 rows = self.survey_combo_sheet.loc[self.survey_combo_sheet[ Const.TEMPLATE_KPI_ID] == atomic_id] temp = rows[Const.TEMPLATE_STORE_TYPE] row_store_filter = rows[( temp.apply(lambda r: self.store_type_filter in [item.strip() for item in r.split(",")])) | (temp == "")] if row_store_filter.empty: return 0 condition = row_store_filter[Const.TEMPLATE_CONDITION].values[0] condition_type = row_store_filter[ Const.TEMPLATE_CONDITION_TYPE].values[0] score = row_store_filter[Const.TEMPLATE_SCORE].values[0] # find the answer to the survey in session for i, row in row_store_filter.iterrows(): question_text = row[Const.TEMPLATE_SURVEY_QUESTION_TEXT] question_answer_template = row[Const.TEMPLATE_TARGET_ANSWER] survey_result = self.survey.get_survey_answer( ('question_text', question_text)) if not survey_result: continue if '-' in question_answer_template: numbers = question_answer_template.split('-') try: numeric_survey_result = int(survey_result) except: Log.warning("Survey question - " + str(question_text) + " - doesn't have a numeric result") continue if numeric_survey_result < int( numbers[0]) or numeric_survey_result > int(numbers[1]): continue numerator_or_denominator = row_store_filter[ Const.NUMERATOR_OR_DENOMINATOR].values[0] if numerator_or_denominator == Const.DENOMINATOR: denominator += numeric_survey_result else: numerator += numeric_survey_result else: continue if condition_type == '%': if denominator != 0: fraction = 100 * (float(numerator) / float(denominator)) else: if numerator > 0: fraction = 100 else: fraction = 0 result = score if fraction >= condition else 0 else: return 0 try: atomic_pk = self.common_v2.get_kpi_fk_by_kpi_name(atomic_name) except IndexError: Log.warning("There is no matching Kpi fk for kpi name: " + atomic_name) return 0 self.common_v2.write_to_db_result(fk=atomic_pk, numerator_id=self.region_fk, numerator_result=numerator, denominator_result=denominator, denominator_id=self.store_id, result=result, score=result, identifier_result=atomic_name, identifier_parent=parent_name, should_enter=True, parent_fk=3) return result
def calculate_sos_vs_target(self, kpi): """ The function filters only the relevant scenes by Location Type and calculates the linear SOS and the facing SOS according to Manufacturer and Category set in the target. :return: """ location_type = kpi['Location Type'] kpi_fk = self.common.get_kpi_fk_by_kpi_type( SOS_MANUFACTURER_CATEGORY + ('_' + location_type if location_type else '')) sos_store_policies = self.sos_store_policies[ self.sos_store_policies['kpi_fk'] == str(kpi_fk)] sos_store_policy = None store_policy_passed = 0 for index, policy in sos_store_policies.iterrows(): sos_store_policy = policy store_policy = json.loads(policy['store_policy']) store_policy_passed = 1 for key in store_policy.keys(): if key in self.store_info.columns.tolist(): if self.store_info[key][0] in store_policy[key]: continue else: store_policy_passed = 0 break else: Log.error("Store Policy attribute is not found: '{}'" ).format(key) store_policy_passed = 0 break if store_policy_passed: break score = potential_score = 0 if store_policy_passed: general_filters = {LOCATION_TYPE: location_type} sos_policy = json.loads(sos_store_policy['sos_policy']) numerator_sos_filters = { MANUFACTURER_NAME: sos_policy[NUMERATOR][MANUFACTURER], CATEGORY: sos_policy[DENOMINATOR][CATEGORY] } denominator_sos_filters = { CATEGORY: sos_policy[DENOMINATOR][CATEGORY] } numerator_id = self.all_products.loc[ self.all_products[MANUFACTURER_NAME] == sos_policy[NUMERATOR] [MANUFACTURER]][MANUFACTURER + '_fk'].values[0] denominator_id = self.all_products.loc[ self.all_products[CATEGORY] == sos_policy[DENOMINATOR] [CATEGORY]][CATEGORY + '_fk'].values[0] ignore_stacking = kpi['Ignore Stacking'] if kpi[ 'Ignore Stacking'] else 0 numer_facings, numer_linear = self.calculate_share_space( ignore_stacking=ignore_stacking, **dict(numerator_sos_filters, **general_filters)) denom_facings, denom_linear = self.calculate_share_space( ignore_stacking=ignore_stacking, **dict(denominator_sos_filters, **general_filters)) if kpi['KPI Type'].lower() == LINEAR_SOS_VS_TARGET: numerator_result = round(numer_linear, 0) denominator_result = round(denom_linear, 0) result = numer_linear / float( denom_linear) if denom_linear else 0 elif kpi['KPI Type'].lower() == FACINGS_SOS_VS_TARGET: numerator_result = numer_facings denominator_result = denom_facings result = numer_facings / float( denom_facings) if denom_facings else 0 else: Log.error("KPI Type is invalid: '{}'").format(kpi['KPI Type']) numerator_result = denominator_result = result = 0 if sos_store_policy['target']: sos_target = round(float(sos_store_policy['target']) * 100, 0) else: Log.error("SOS target is not set for Store ID {}").format( self.store_id) sos_target = 0 result_vs_target = result / (float(sos_target) / 100) * 100 if sos_target else 0 score = self.score_function(result_vs_target, kpi) potential_score = round(float(kpi['Weight']) * 100, 0) identifier_kpi = self.common.get_dictionary(kpi_fk=kpi_fk) identifier_parent = self.common.get_dictionary( kpi_fk=self.common.get_kpi_fk_by_kpi_type(kpi['KPI Group'])) self.common.write_to_db_result( fk=kpi_fk, numerator_id=numerator_id, numerator_result=numerator_result, denominator_id=denominator_id, denominator_result=denominator_result, result=result, score=score, weight=potential_score, target=sos_target, identifier_result=identifier_kpi, identifier_parent=identifier_parent, should_enter=True) else: Log.warning("Store Policy is not found for Store ID {}".format( self.store_id)) return score, potential_score, store_policy_passed
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