def _handle_promotion_wall_display(self): """ Handles promotion wall display. All display tags in a scene are aggregated to one display with multiple bays :return: """ Log.debug(self.log_prefix + ' Starting promotion display') promotion_tags = \ self.match_display_in_scene[self.match_display_in_scene['display_name'].isin(PROMOTION_WALL_DISPLAYS)] if not promotion_tags.empty: promotion_display_name = promotion_tags['display_name'].values[0] display_promotion_wall = promotion_tags.groupby( ['display_fk', 'scene_fk'], as_index=False).display_size.sum() display_promotion_wall['display_name'] = promotion_display_name display_promotion_wall_with_id = self._insert_into_display_surface( display_promotion_wall) promotion_wall_bays = promotion_tags[['scene_fk', 'bay_number']].copy() promotion_wall_bays.drop_duplicates(['scene_fk', 'bay_number'], inplace=True) # only valid tags are relevant promotion_wall_valid_bays = self._filter_valid_bays( promotion_wall_bays) display_promotion_wall_with_id_and_bays = \ display_promotion_wall_with_id.merge(promotion_wall_valid_bays, on='scene_fk') self._calculate_share_of_display( display_promotion_wall_with_id_and_bays)
def _exclude_sos(self, df): """ Calculate 'in_sos' field according to exclude rules. :param df: :return: """ Log.debug(self.log_prefix + ' calculating in_sos') excluded_templates = self.data_provider._data[ Fields.SOS_EXCLUDED_TEMPLATES] excluded_templates['excluded_templates'] = 1 excluded_template_products = self.data_provider._data[ Fields.SOS_EXCLUDED_TEMPLATE_PRODUCTS] excluded_template_products['excluded_template_products'] = 1 excluded_products = self.data_provider._data[ Fields.SOS_EXCLUDED_PRODUCTS] excluded_products['excluded_products'] = 1 df = df.merge(excluded_templates, how='left', on='template_fk') \ .merge(excluded_products, how='left', on='product_fk') \ .merge(excluded_template_products, how='left', on=['product_fk', 'template_fk']) condition = (df['excluded_templates'] == 1) | \ (df['excluded_template_products'] == 1) | (df['excluded_products'] == 1) df = df.drop([ 'excluded_templates', 'excluded_template_products', 'excluded_products' ], axis=1) df.loc[condition, 'in_sos'] = 0 df.loc[~condition, 'in_sos'] = 1 return df
def check_survey_answer(self, survey_text, target_answer): """ :param survey_text: The name of the survey in the DB. :param target_answer: The required answer/s for the KPI to pass. :return: True if the answer matches the target; otherwise - False. """ if not isinstance(survey_text, (list, tuple)): entity = 'question_text' value = survey_text else: entity, value = survey_text value = [value] if not isinstance(value, list) else value survey_data = self.survey_response[self.survey_response[entity].isin( value)] if survey_data.empty: Log.debug('Survey with {} = {} doesn\'t exist'.format( entity, value)) return None answer_field = 'selected_option_text' if not survey_data[ 'selected_option_text'].empty else 'number_value' target_answers = [target_answer ] if not isinstance(target_answer, (list, tuple)) else target_answer survey_answers = survey_data[answer_field].values.tolist() for answer in target_answers: if answer in survey_answers: return True return False
def get_entity_matrix(self, scene_id, entity): """ This function creates a list of lists: Each list represents a shelf in the scene - with the given entity for each facing, from left to right. """ if entity not in self.ATTRIBUTES_TO_SAVE: Log.debug("Entity '{}' is not set as an attribute in the graph".format(entity)) return None graph = self.get(scene_id).copy() edges_to_remove = graph.es.select(direction_ne='left') graph.delete_edges([edge.index for edge in edges_to_remove]) incidents_dict = {} matrix = [] for vertex in graph.vs: vertex_id = vertex.index incidents = graph.incident(vertex_id) if incidents: incidents_dict[graph.es[incidents[0]].target] = vertex_id else: matrix.append([vertex_id]) for i, row in enumerate(matrix): current = row[0] while current in incidents_dict.keys(): current = incidents_dict[current] row.append(current) for y, index in enumerate(row): row[y] = graph.vs[index][entity] matrix[i] = row return matrix
def calculate_sos(self, sos_type, kpi_fk, numerator_fk, denominator_fk, subset_filters, pop_filters, context=None): ratio = 0 df = self.match_product_in_scene.merge(self.scif, how='left', on=['scene_fk', 'product_fk']) df = df.drop(['facings'], axis=1) # denominator pop_filter = self.common_sos.get_filter_condition(df, **pop_filters) # numerator subset_filter = self.common_sos.get_filter_condition( df, **subset_filters) try: ratio = self.calculate_sos_by_policy(sos_type, kpi_fk, numerator_fk, denominator_fk, subset_filter=subset_filter, pop_filter=pop_filter, context=context) except Exception as e: Log.debug( 'calculate_sos_facing_by_scene can not be calculated for scene {}' .format(e.message)) return ratio
def calculate_sos(self, sos_type, kpi_fk, numerator_fk, denominator_fk, subset_filters, pop_filters, context=None): ratio = 0 # denominator pop_filter = self.common_sos.get_filter_condition( self.scif, **pop_filters) # numerator subset_filter = self.common_sos.get_filter_condition( self.scif, **subset_filters) try: ratio = self.calculate_sos_by_policy(sos_type, kpi_fk, numerator_fk, denominator_fk, subset_filter=subset_filter, pop_filter=pop_filter, context=context) except Exception as e: Log.debug( 'calculate_sos_facing_by_scene can not be calculated for scene {}' .format(e.message)) return ratio
def create_position_graphs(self, scene_id=None): """ This function creates a facings Graph for each scene of the given session. """ calc_start_time = datetime.datetime.utcnow() if scene_id: scenes = [scene_id] else: scenes = self.match_product_in_scene['scene_fk'].unique() for scene in scenes: matches = self.match_product_in_scene[(self.match_product_in_scene['scene_fk'] == scene) & (self.match_product_in_scene['stacking_layer'] == 1)] matches['distance_from_end_of_shelf'] = matches['n_shelf_items'] - matches['facing_sequence_number'] scene_graph = igraph.Graph(directed=True) edges = [] for f in xrange(len(matches)): facing = matches.iloc[f] facing_name = str(facing[VERTEX_FK_FIELD]) scene_graph.add_vertex(facing_name) # adding attributes to vertex vertex = scene_graph.vs.find(facing_name) for attribute in self.ATTRIBUTES_TO_SAVE: vertex[attribute] = facing[attribute] surrounding_products = self.get_surrounding_products(facing, matches) for direction in surrounding_products.keys(): for pk in surrounding_products[direction]: edge = dict(source=facing_name, target=str(pk), direction=direction) edges.append(edge) for edge in edges: scene_graph.add_edge(**edge) self.position_graphs[scene] = scene_graph calc_finish_time = datetime.datetime.utcnow() Log.debug('Creation of position graphs for scenes {} took {}'.format(scenes, calc_finish_time - calc_start_time))
def calculate_placement_count(self): """ this function calculates number of occurrences for each scene type and adds the correct score to insert query which will be later committed to report.kpi_result db :return: None """ df = self.scif[['template_name', 'scene_fk']] new_kpi_fk = self.common_new.get_kpi_fk_by_kpi_name(PLACEMENT_COUNT) # iterate scene types names for template_name in df.template_name.unique(): # get number of occurrences of this scene type value = self.get_scene_count(df, template_name) # get the atomic kpi name atomic_kpi_name = 'no' if template_name in self.template_dict: atomic_kpi_name = self.template_dict[template_name] if atomic_kpi_name != 'no': # get the atomic kpi fk of template name old_atomic_kpi_fk = self.old_common.get_kpi_fk_by_kpi_name(atomic_kpi_name, self.LEVEL3) Log.debug(atomic_kpi_name + " " + str(old_atomic_kpi_fk) + " " + str(value)) # add insert query for later use self.old_common.write_to_db_result(old_atomic_kpi_fk, score=value, level=self.LEVEL3) template_fk = self.templates[self.templates['template_name'] == template_name] if len(template_fk) == 0: Log.debug('template {} does not exist'.format(template_name)) else: template_fk = template_fk['template_fk'].values[0] self.common_new.write_to_db_result(fk=new_kpi_fk, numerator_id=template_fk, result=value, denominator_id=self.store_id)
def _params_validation(self, scenes_to_check, sequence_params, population): """ :param scenes_to_check: List of scenes that passed the location filters. :param sequence_params: The sequence additional attributes. :return: 1 in case of valid params, 0 otherwise. """ if not scenes_to_check: Log.debug("There aren't any valid scenes for sequence calculation") return 0 if self._matches.empty: Log.debug( "Cannot calculate Sequence KPI since the match product in scene is empty!" ) return 0 if not self._validate_population_structure(population): Log.debug("Wrong population structure! Exiting..") return 0 if set(sequence_params.keys()).difference( self._get_default_sequence_parameters().keys()): Log.debug("Wrong attributes in Additional Attributes! Exiting..") return 0 if not sequence_params[AdditionalAttr.REPEATING_OCCURRENCES] and \ sequence_params[AdditionalAttr.MIN_TAGS_OF_ENTITY] > 1: Log.debug( "In case repeating occurrences is False, minimum tags of facings param must be equal to 1" ) return 0 return 1
def _delete_previous_data(self): """ Deletes previous data from table: probedata.display_surface (by updating delete_time) and report.display_visit_summary. Using temp table for scenes list to prevent a lock :return: """ Log.debug( self.log_prefix + ' Deleting existing data in display_surface and display_visit_summary' ) drop_temp_table_query = "drop temporary table if exists probedata.t_scenes_to_delete_displays;" queries = [ drop_temp_table_query, """ create temporary table probedata.t_scenes_to_delete_displays as select pk as scene_fk from probedata.scene where session_uid = '{}';""" .format(self.session_uid), """ delete report.display_item_facts, probedata.display_surface from probedata.t_scenes_to_delete_displays join probedata.display_surface on probedata.display_surface.scene_fk = probedata.t_scenes_to_delete_displays.scene_fk left join report.display_item_facts on report.display_item_facts.display_surface_fk = probedata.display_surface.pk;""", drop_temp_table_query ] self.project_connector.disconnect_rds() self.project_connector.connect_rds() self.cur = self.project_connector.db.cursor() for query in queries: self.cur.execute(query)
def add_kpis_from_template(self): self.add_sets_to_static() self.add_kpis_to_static() self.add_atomics_to_static() Log.debug('{} Sets, {} KPIs and {} Atomics have been added'.format( self.kpi_counter['set'], self.kpi_counter['kpi'], self.kpi_counter['atomic']))
def calculate_existence_of_blocks(self, conditions, include_empty=EXCLUDE_EMPTY, min_number_of_blocks=1, **filters): """ :param conditions: A dictionary which contains assortment/availability conditions for filtering the blocks, in the form of: {entity_type: (0 for assortment or 1 for availability, a list of values =or None=, minimum number of assortment/availability)}. For example: {'product_ean_code': ('44545345434', 3)} :param include_empty: This parameter dictates whether or not to discard Empty-typed products. :param min_number_of_blocks: The number of blocks needed in order for the KPI to pass. If all appearances are required: == self.ALL. :param filters: These are the parameters which the blocks are checked for. :return: The number of blocks (from all scenes) which match the filters and conditions. """ filters, relevant_scenes = self.separate_location_filters_from_product_filters(**filters) if len(relevant_scenes) == 0: Log.debug('Block Together: No relevant SKUs were found for these filters {}'.format(filters)) return False number_of_blocks = 0 for scene in relevant_scenes: scene_graph = self.position_graphs.get(scene).copy() blocks, scene_graph = self.get_scene_blocks(scene_graph, allowed_products_filters=None, include_empty=include_empty, **filters) for block in blocks: entities_data = {entity: [] for entity in conditions.keys()} for vertex in block: vertex_attributes = scene_graph.vs[vertex].attributes() for entity in conditions.keys(): entities_data[entity].append(vertex_attributes[entity]) block_successful = True for entity in conditions.keys(): assortment_or_availability, values, minimum_result = conditions[entity] if assortment_or_availability == 0: if values: result = len(set(entities_data[entity]).intersection(values)) else: result = len(set(entities_data[entity])) elif assortment_or_availability == 1: if values: result = len([facing for facing in entities_data if facing in values]) else: result = len(entities_data[entity]) else: continue if result < minimum_result: block_successful = False break if block_successful: number_of_blocks += 1 if number_of_blocks >= min_number_of_blocks: return True else: if min_number_of_blocks == self.ALL: return False if number_of_blocks >= min_number_of_blocks or min_number_of_blocks == self.ALL: return True return False
def update_atomic_kpi_data(self): """ This function updates the kpi name, description and display_text according to the template. It reads the relevant set template, filters the relevant rows that need to be change and than collect and execute the queries in the DB. ** Note 1: It doesn't update the template itself!! ** ** Note 2: Use regular excel without any filters!! """ queries_to_execute = [] update_query = "UPDATE static.atomic_kpi SET name='{}', description='{}', display_text='{}' where pk={};" # Get the data relevant_kpi_data, filtered_project_data = self.get_relevant_kpi_data_and_template_data( ) if relevant_kpi_data.empty: return # Iterate over the uploaded template and create the queries for i in xrange(len(self.data)): row = self.data.iloc[i] new_name = row[CCRU_SANDConsts.KPI_ENG_NAME].replace('\n', '').strip() display = row[CCRU_SANDConsts.KPI_RUS_NAME].replace('\n', '').strip() presentation_order = row[CCRU_SANDConsts.SORTING] old_atomic_df = relevant_kpi_data[relevant_kpi_data[ CCRU_SANDConsts.PRESENTATION_ORDER] == presentation_order] if len(old_atomic_df) > 1: old_atomic_name = filtered_project_data[filtered_project_data[ CCRU_SANDConsts.SORTING] == presentation_order][ CCRU_SANDConsts.KPI_ENG_NAME].values[0] try: old_atom_fk = old_atomic_df[ old_atomic_df['atomic_kpi_name'] == old_atomic_name]['atomic_kpi_fk'].values[0] queries_to_execute.append( update_query.format(new_name, new_name, display.encode('utf-8'), old_atom_fk)) except Exception as e: Log.warning("No KPI defines for name = {}.".format( old_atomic_name)) continue else: old_atom_fk = old_atomic_df['atomic_kpi_fk'].values[0] queries_to_execute.append( update_query.format(new_name, new_name, display.encode('utf-8'), old_atom_fk)) # Execute the queries if queries_to_execute: cur = self.aws_conn.db.cursor() for query in queries_to_execute: cur.execute(query) print query self.aws_conn.db.commit() Log.debug("Total number of executed queries = {}".format( len(queries_to_execute)))
def calculate_promo_compliance(self): self.json.create_kpi_data_json('promo', 'KPI_Promo_Tracking.xlsx', sheet_name='2019') kpi_data = self.json.project_kpi_dict.get('promo') Log.debug('KPI calculation stage: {}'.format('Promo Compliance')) self.tool_box.calculate_promo_compliance_store(kpi_data) Log.debug('KPI calculation stage: {}'.format('Committing results new')) self.tool_box.common.commit_results_data()
def add_atomics_to_static(self): atomics = self.data.drop_duplicates( subset=[self.SET_NAME, self.KPI_NAME, self.ATOMIC_NAME], keep='first') cur = self.aws_conn.db.cursor() for i in xrange(len(atomics)): atomic = atomics.iloc[i] set_name = atomic[self.SET_NAME].replace("'", "\\'").encode('utf-8') kpi_name = unicode(atomic[self.KPI_NAME]).replace( "'", "\\'").encode('utf-8') atomic_name = unicode(atomic[self.ATOMIC_NAME]).replace( "'", "\\'").encode('utf-8') if self.ATOMIC_WEIGHT in atomics.iloc[i].keys( ) and atomics.iloc[i][self.ATOMIC_WEIGHT]: atomic_weight = float(atomics.iloc[i][self.ATOMIC_WEIGHT]) else: atomic_weight = 'NULL' if self.ATOMIC_DISPLAY_TEXT in atomics.iloc[i].keys(): atomic_display_text = unicode( atomics.iloc[i][self.ATOMIC_DISPLAY_TEXT]).replace( "'", "\\'").encode('utf-8') else: atomic_display_text = atomic_name if self.kpi_static_data[ (self.kpi_static_data['kpi_set_name'] == set_name) & (self.kpi_static_data['kpi_name'] == kpi_name) & (self.kpi_static_data['atomic_kpi_name'] == atomic_name)].empty: if set_name in self.kpis_added.keys( ) and kpi_name in self.kpis_added[set_name].keys(): kpi_fk = self.kpis_added[set_name][kpi_name] else: 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] level3_query = \ """ INSERT INTO static.atomic_kpi (kpi_fk, name, description, display_text, presentation_order, display, atomic_weight) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', {6}); """.format(kpi_fk, atomic_name, atomic_name, atomic_display_text, 1, 'Y', atomic_weight) cur.execute(level3_query) self.kpi_counter['atomic'] += 1 else: Log.debug( "Atomic '{}' already exists for KPI '{}' Set '{}'. Ignored" .format(atomic_name, kpi_name, set_name)) if i % 10 == 0: self.aws_conn.db.commit() cur = self.aws_conn.db.cursor() self.aws_conn.db.commit()
def calculate_block_together(self, allowed_products_filters=None, include_empty=EXCLUDE_EMPTY, minimum_block_ratio=1, result_by_scene=False, **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 minimum_block_ratio: The minimum (block number of facings / total number of relevant facings) ratio in order for KPI to pass (if ratio=1, then only one block is allowed). :param result_by_scene: True - The result is a tuple of (number of passed scenes, total relevant scenes); False - The result is True if at least one scene has a block, False - otherwise. :param filters: These are the parameters which the blocks are checked for. :return: see 'result_by_scene' above. """ filters, relevant_scenes = self.separate_location_filters_from_product_filters(**filters) if len(relevant_scenes) == 0: if result_by_scene: return 0, 0 else: Log.debug('Block Together: No relevant SKUs were found for these filters {}'.format(filters)) return True number_of_blocked_scenes = 0 cluster_ratios = [] for scene in relevant_scenes: scene_graph = self.position_graphs.get(scene).copy() clusters, scene_graph = self.get_scene_blocks(scene_graph, allowed_products_filters=allowed_products_filters, include_empty=include_empty, **filters) new_relevant_vertices = self.filter_vertices_from_graph(scene_graph, **filters) for cluster in clusters: relevant_vertices_in_cluster = set(cluster).intersection(new_relevant_vertices) if len(new_relevant_vertices) > 0: cluster_ratio = len(relevant_vertices_in_cluster) / float(len(new_relevant_vertices)) else: cluster_ratio = 0 cluster_ratios.append(cluster_ratio) if cluster_ratio >= minimum_block_ratio: if result_by_scene: number_of_blocked_scenes += 1 break else: if minimum_block_ratio == 1: return True else: all_vertices = {v.index for v in scene_graph.vs} non_cluster_vertices = all_vertices.difference(cluster) scene_graph.delete_vertices(non_cluster_vertices) return cluster_ratio, scene_graph if result_by_scene: return number_of_blocked_scenes, len(relevant_scenes) else: if minimum_block_ratio == 1: return False elif cluster_ratios: return max(cluster_ratios) else: return None
def calculate_product_sequence(self, sequence_filters, direction, empties_allowed=True, irrelevant_allowed=False, min_required_to_pass=STRICT_MODE, custom_graph=None, **general_filters): """ :param sequence_filters: One of the following: 1- a list of dictionaries, each containing the filters values of an organ in the sequence. 2- a tuple of (entity_type, [value1, value2, value3...]) in case every organ in the sequence is defined by only one filter (and of the same entity, such as brand_name, etc). :param direction: left/right/top/bottom - the direction of the sequence. :param empties_allowed: This dictates whether or not the sequence can be interrupted by Empty facings. :param irrelevant_allowed: This dictates whether or not the sequence can be interrupted by facings which are not in the sequence. :param min_required_to_pass: The number of sequences needed to exist in order for KPI to pass. If STRICT_MODE is activated, the KPI passes only if it has NO rejects. :param custom_graph: A filtered Positions graph - given in case only certain vertices need to be checked. :param general_filters: These are the parameters which the general data frame is filtered by. :return: True if the KPI passes; otherwise False. """ if isinstance(sequence_filters, (list, tuple)) and isinstance(sequence_filters[0], (str, unicode)): sequence_filters = [{sequence_filters[0]: values} for values in sequence_filters[1]] pass_counter = 0 reject_counter = 0 if not custom_graph: filtered_scif = self.scif[self.get_filter_condition(self.scif, **general_filters)] scenes = set(filtered_scif['scene_id'].unique()) for filters in sequence_filters: scene_for_filters = filtered_scif[self.get_filter_condition(filtered_scif, **filters)]['scene_id'].unique() scenes = scenes.intersection(scene_for_filters) if not scenes: Log.debug('None of the scenes include products from all types relevant for sequence') return True for scene in scenes: scene_graph = self.position_graphs.get(scene) scene_passes, scene_rejects = self.calculate_sequence_for_graph(scene_graph, sequence_filters, direction, empties_allowed, irrelevant_allowed) pass_counter += scene_passes reject_counter += scene_rejects if pass_counter >= min_required_to_pass: return True elif min_required_to_pass == self.STRICT_MODE and reject_counter > 0: return False else: scene_passes, scene_rejects = self.calculate_sequence_for_graph(custom_graph, sequence_filters, direction, empties_allowed, irrelevant_allowed) pass_counter += scene_passes reject_counter += scene_rejects if pass_counter >= min_required_to_pass or reject_counter == 0: return True else: return False
def is_query_result_valid(self, result, session_uid): if len(result) == 0: Log.warning('Could not generate MyChemist report for session {}. Query returned empty result'.format( session_uid)) return False if len(result[~result['connected_shelf_fk'].isnull()]) > 0: Log.debug('This session was generated by IOT device, omitting the report') return False return True
def check_connection(self, rds_conn): try: rds_conn.db.cursor().execute( "select pk from probedata.session where session_uid = '{}';". format(self.session_uid)) except: rds_conn.disconnect_rds() rds_conn.connect_rds() Log.debug('DB is reconnected') return False return True
def get_product_alt_code(self, product_name): """ takes sub_brand and returns its pk :param sub_brand: str :param brand_fk: we need it for the parent_id (different brands can have common sub_brand) :return: pk """ products = self.all_products[['product_name', 'product_fk', 'alt_code_1']] products = products.loc[products['alt_code_1'] == product_name] if products.empty: Log.debug('Product {} has no valid alt_code_1 attribute'.format(product_name)) return None else: return products['product_fk'].iloc[0]
def calculate_energy_drinks(self, shelf_occupation_dict, product_list_field): """ this function calculates score for energy drinks category """ score = 0 for shelf_number in range(1, shelf_occupation_dict.get(NUM_OF_SHELVES) + 1): for bay_number in range(1, shelf_occupation_dict.get(NUM_OF_BAYS) + 1): # get the current probe to calculate - specific shelf, bay, and only in main_placement scene type curr_probe = get_curr_probe(shelf_occupation_dict.get(DF), shelf_number, bay_number, shelf_occupation_dict.get(MAIN_PLACEMENT_SCENES)) if not curr_probe.empty: score += self.calculate_category(curr_probe, product_list_field) Log.debug("category score " + str(score)) return score
def calculate_relative_position(self, tested_filters, anchor_filters, direction_data, min_required_to_pass=1, **general_filters): """ :param tested_filters: The tested SKUs' filters. :param anchor_filters: The anchor SKUs' filters. :param direction_data: The allowed distance between the tested and anchor SKUs. In form: {'top': 4, 'bottom: 0, 'left': 100, 'right': 0} Alternative form: {'top': (0, 1), 'bottom': (1, 1000), ...} - As range. :param min_required_to_pass: The number of appearances needed to be True for relative position in order for KPI to pass. If all appearances are required: ==a string or a big number. :param general_filters: These are the parameters which the general data frame is filtered by. :return: True if (at least) one pair of relevant SKUs fits the distance requirements; otherwise - returns False. """ filtered_scif = self.scif[self.get_filter_condition(self.scif, **general_filters)] tested_scenes = filtered_scif[self.get_filter_condition(filtered_scif, **tested_filters)]['scene_id'].unique() anchor_scenes = filtered_scif[self.get_filter_condition(filtered_scif, **anchor_filters)]['scene_id'].unique() relevant_scenes = set(tested_scenes).intersection(anchor_scenes) if relevant_scenes: pass_counter = 0 reject_counter = 0 for scene in relevant_scenes: scene_graph = self.position_graphs.get(scene) if not len(scene_graph.vs): pass tested_vertices = self.filter_vertices_from_graph(scene_graph, **tested_filters) anchor_vertices = self.filter_vertices_from_graph(scene_graph, **anchor_filters) for tested_vertex in tested_vertices: for anchor_vertex in anchor_vertices: moves = {'top': 0, 'bottom': 0, 'left': 0, 'right': 0} path = scene_graph.get_shortest_paths(anchor_vertex, tested_vertex, output='epath') if path: path = path[0] for edge in path: moves[scene_graph.es[edge]['direction']] += 1 if self.validate_moves(moves, direction_data): pass_counter += 1 if isinstance(min_required_to_pass, int) and pass_counter >= min_required_to_pass: return True else: reject_counter += 1 else: Log.debug('Tested and Anchor have no direct path') if pass_counter > 0 and reject_counter == 0: return True else: return False else: Log.debug('None of the scenes contain both anchor and tested SKUs') return False
def is_new(self, data, level=3): if level == 3: existing = self.kpi_static_data[ (self.kpi_static_data[BATRUConst.SET_FK] == self.set_fk) & (self.kpi_static_data[BATRUConst.KPI_FK] == data[0]) & (self.kpi_static_data[BATRUConst.ATOMIC_NAME] == data[1])] elif level == 2: existing = self.kpi_static_data[ (self.kpi_static_data[BATRUConst.SET_FK] == self.set_fk) & (self.kpi_static_data[BATRUConst.KPI_NAME] == data[0])] else: Log.debug('not valid level for checking new KPIs') return False return existing.empty
def calculate_redbull_manufacturer(self, shelf_occupation_dict, product_list_field): """ this function calculates score for Red Bull manufacturer category it iterates shelves and bays as if they were matrix and aggregates score for each "cell" or "curr_probe" """ score = 0 for shelf_number in range(1, shelf_occupation_dict.get(NUM_OF_SHELVES) + 1): for bay_number in range(1, shelf_occupation_dict.get(NUM_OF_BAYS) + 1): # get the current probe to calculate - specific shelf, bay, and only in main_placement scene type curr_probe = get_curr_probe(shelf_occupation_dict.get(DF), shelf_number, bay_number, shelf_occupation_dict.get(MAIN_PLACEMENT_SCENES)) if not curr_probe.empty: score += self.calculate_manufacturer(curr_probe, product_list_field) Log.debug("manufacturer score " + str(score)) return score
def _filter_scenes_by_location(self, location): """ This method returns the relevant scene by location filters. :param location: A dictionary with location attributes like scene_id, template_fk, location_type and the requested values. E.g: {'template_name': ['great_template'], scene_id: [1, 2, 3]} """ relevant_scenes = self.scif.scene_id.unique().tolist() if location is not None: conditions = {self.input_parser.LOCATION: location} try: relevant_scenes = self.input_parser.filter_df(conditions, self.scif).scene_id.unique().tolist() except (AttributeError, KeyError): Log.debug("location parameter is not in the required structure.") relevant_scenes = [] return relevant_scenes
def main_function(self): """ This is the main KPI calculation function. It calculates the score for every KPI set and saves it to the DB. """ if self.tool_box.scif.empty: Log.warning('Scene item facts is empty for session {}'.format( self.tool_box.session_uid)) return self.tool_box.main_calculation() self.tool_box.commit_results_data() self.tool_box.common.commit_results_data() if self.tool_box.template_warnings: Log.debug('The following templates did not exist in DB - ' 'fallback to excel. Details: {}' ''.format(self.tool_box.template_warnings))
def get_compliance(self, manual_planogram_data=None, manual_scene_data=None): """ This function filters the irrelevant products out, creates a matrix that matches the bays of the POG and the scene and scores them, find the best way to match the bays and returns the match tags. :param manual_planogram_data: match_product_in_planogram (just for testing) :param manual_scene_data: match_product_in_scene (just for testing) :return: DF of match_product_in_scene_fk with the tags """ tag_compliance = pd.DataFrame(columns=[Keys.MATCH_FK, Keys.COMPLIANCE_STATUS_FK]) try: self.planogram_matches = self._data_provider.planogram_data if manual_planogram_data is\ None else manual_planogram_data self.scene_matches = self._data_provider.matches if manual_scene_data is None else manual_scene_data self._filter_irrelevant_out() self.scene_bays = self.scene_matches[Keys.BAY_NUMBER].unique().tolist() self.pog_bays = self.planogram_matches[Keys.BAY_NUMBER].unique().tolist() if len(self.scene_bays) == 1 and len(self.pog_bays) == 1: if self.scene_bays != self.pog_bays: scene_bay_data = self._get_df_of_bay(self.scene_matches, self.scene_bays[0]) pog_bay_data = self._get_df_of_bay(self.planogram_matches, self.pog_bays[0]) else: scene_bay_data = self.scene_matches pog_bay_data = self.planogram_matches tag_compliance, score = self._local_get_tag_planogram_compliance(scene_bay_data, pog_bay_data) elif len(self.scene_bays) < 5 and len(self.pog_bays) < 5: tag_compliance = self._get_iterated_position_full_solution() else: tag_compliance = self._get_iterated_position_greedy() if tag_compliance is None: tag_compliance = get_tag_planogram_compliance(self.scene_matches, self.planogram_matches) if 1 in tag_compliance['compliance_status_fk'].tolist(): try: compliance_products = pd.merge(self.scene_matches, tag_compliance, on='match_fk', how='left') pog_products = self.planogram_matches['product_fk'].unique().tolist() wrong_extra_tags = compliance_products[ (compliance_products['compliance_status_fk'] == 1) & (compliance_products['product_fk'].isin(pog_products))]['match_fk'].tolist() tag_compliance.loc[tag_compliance['match_fk'].isin(wrong_extra_tags), 'compliance_status_fk'] = 2 except Exception as er: Log.debug(er.message) except Exception as e: Log.error("Calculated compliance has failed: " + e.message) try: tag_compliance = get_tag_planogram_compliance(self.scene_matches, self.planogram_matches) except Exception as er: Log.error("Calculated compliance has failed: " + er.message) return tag_compliance
def add_kpis_to_static(self): kpis = self.data.drop_duplicates(subset=[self.SET_NAME, self.KPI_NAME], keep='first') cur = self.aws_conn.db.cursor() for i in xrange(len(kpis)): set_name = kpis.iloc[i][self.SET_NAME].replace( "'", "\\'").encode('utf-8') kpi_name = unicode(kpis.iloc[i][self.KPI_NAME]).replace( "'", "\\'").encode('utf-8') if self.KPI_WEIGHT in kpis.iloc[i].keys() and kpis.iloc[i][ self.KPI_WEIGHT]: kpi_weight = float(kpis.iloc[i][self.KPI_WEIGHT]) else: kpi_weight = 'NULL' if self.kpi_static_data[ (self.kpi_static_data['kpi_set_name'] == set_name) & (self.kpi_static_data['kpi_name'] == kpi_name)].empty: if set_name in self.sets_added.keys(): set_fk = self.sets_added[set_name] else: try: set_fk = self.kpi_static_data[ self.kpi_static_data['kpi_set_name'] == set_name]['kpi_set_fk'].values[0] except: set_fk = self.sets_added[set_name] level2_query = \ """ INSERT INTO static.kpi (kpi_set_fk, display_text, weight) VALUES ('{0}', '{1}', {2}); """.format(set_fk, kpi_name, kpi_weight) cur.execute(level2_query) if set_name in self.kpis_added.keys(): self.kpis_added[set_name][kpi_name] = cur.lastrowid else: self.kpis_added[set_name] = {kpi_name: cur.lastrowid} self.kpi_counter['kpi'] += 1 else: Log.debug( "KPI '{}' already exists for KPI Set '{}'. Ignored".format( kpi_name, set_name)) if i % 10 == 0: self.aws_conn.db.commit() cur = self.aws_conn.db.cursor() self.aws_conn.db.commit()
def add_sets_to_static(self): set_names = self.data[self.SET_NAME].unique().tolist() existing_set_names = self.kpi_static_data['kpi_set_name'].unique( ).tolist() set_names_to_add = set(set_names).difference(existing_set_names) if set_names_to_add: cur = self.aws_conn.db.cursor() for set_name in set_names_to_add: level1_query = \ """ INSERT INTO static.kpi_set (name, missing_kpi_score, enable, normalize_weight, expose_to_api, is_in_weekly_report) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}'); """.format(set_name.encode('utf-8'), 'Bad', 'Y', 'N', 'N', 'N') cur.execute(level1_query) self.sets_added[set_name.encode('utf-8')] = cur.lastrowid self.kpi_counter['set'] += 1 self.aws_conn.db.commit() set_names_ignored = set(set_names).difference(set_names_to_add) if set_names_ignored: Log.debug("KPI Sets '{}' already exist. Ignored".format( set_names_ignored))
def _insert_into_display_surface(self, display_surface): """ Inserts into probedata.display_surface the displays identified in each scene and its size. For each display it updates the new record pk in order to use as a foreign key when inserting into report.display_visit_summary. :param display_surface: :return: """ # Optional performance improvement # 1.Use df instead of to_dict Log.debug(self.log_prefix + ' Inserting to probedata.display_surface') display_surface_dict = display_surface.to_dict('records') query = '''insert into probedata.display_surface ( scene_fk , display_fk , surface ) values {};''' for display in display_surface_dict[:-1]: query_line = self._get_display_surface_query_line( display) + ',' + '{}' query = query.format(query_line) query = query.format( self._get_display_surface_query_line(display_surface_dict[-1])) self.cur.execute(query) self.project_connector.db.commit() last_insert_id = self.cur.lastrowid row_count = self.cur.rowcount if row_count == len(display_surface_dict): for j in range(0, len(display_surface_dict)): display_surface_dict[j]['display_surface_fk'] = last_insert_id last_insert_id += 1 else: msg = self.log_prefix + ' error: not all display were inserted.' Log.error(msg) raise Exception(msg) return pd.DataFrame(display_surface_dict)