Ejemplo n.º 1
0
class DIAGEOPT_SANDToolBox:
    LEVEL1 = 1
    LEVEL2 = 2
    LEVEL3 = 3
    ACTIVATION_STANDARD = 'Activation Standard'
    DIAGEO_MANUFACTURER = 'Diageo'

    def __init__(self, data_provider, output):
        self.k_engine = BaseCalculationsScript(data_provider, output)
        self.data_provider = data_provider
        self.project_name = self.data_provider.project_name
        self.session_uid = self.data_provider.session_uid
        self.products = self.data_provider[Data.PRODUCTS]
        self.all_products = self.data_provider[Data.ALL_PRODUCTS]
        self.match_product_in_scene = self.data_provider[Data.MATCHES]
        self.visit_date = self.data_provider[Data.VISIT_DATE]
        self.session_info = self.data_provider[Data.SESSION_INFO]
        self.rds_conn = PSProjectConnector(self.project_name,
                                           DbUsers.CalculationEng)
        self.store_info = self.data_provider[Data.STORE_INFO]
        self.store_channel = self.store_info['store_type'].values[0]
        if self.store_channel:
            self.store_channel = self.store_channel.upper()
        self.store_type = self.store_info['additional_attribute_1'].values[0]
        self.business_unit = self.get_business_unit()
        self.scene_info = self.data_provider[Data.SCENES_INFO]
        self.store_id = self.data_provider[Data.STORE_FK]
        self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
        self.match_display_in_scene = self.get_match_display()
        self.set_templates_data = {}
        self.kpi_static_data = self.get_kpi_static_data()
        self.set_scores = {}
        self.kpi_scores = {}
        self.kpi_results_queries = []

        self.output = output
        self.common = Common(self.data_provider)
        self.commonV2 = CommonV2(self.data_provider)
        self.tools = DIAGEOToolBox(
            self.data_provider,
            output,
            match_display_in_scene=self.match_display_in_scene)
        self.diageo_generator = DIAGEOGenerator(self.data_provider,
                                                self.output,
                                                self.common,
                                                menu=True)

    def get_business_unit(self):
        """
        This function returns the session's business unit (equal to store type for some KPIs)
        """
        query = DIAGEOQueries.get_business_unit_data(
            self.store_info['store_fk'].values[0])
        business_unit = pd.read_sql_query(query, self.rds_conn.db)['name']
        if not business_unit.empty:
            return business_unit.values[0]
        else:
            return ''

    def get_kpi_static_data(self):
        """
        This function extracts the static KPI data and saves it into one global data frame.
        The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
        """
        query = DIAGEOQueries.get_all_kpi_data()
        kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
        return kpi_static_data

    def get_match_display(self):
        """
        This function extracts the display matches data and saves it into one global data frame.
        The data is taken from probedata.match_display_in_scene.
        """
        query = DIAGEOQueries.get_match_display(self.session_uid)
        match_display = pd.read_sql_query(query, self.rds_conn.db)
        return match_display

    def main_calculation(self, set_names):
        """
        This function calculates the KPI results.
        """
        # Global assortment kpis
        assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2(
        )
        self.commonV2.save_json_to_new_tables(assortment_res_dict)
        total_scores_dict = []
        # saving in dictionary for  activation standard use
        if assortment_res_dict:
            total_scores_dict.append(assortment_res_dict)

        # Global Menu kpis
        menus_res_dict = self.diageo_generator.diageo_global_share_of_menu_cocktail_function(
            cocktail_product_level=True)
        self.commonV2.save_json_to_new_tables(menus_res_dict)

        for set_name in set_names:
            set_score = 0

            # if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys():
            #     self.set_templates_data[set_name] = self.tools.download_template(set_name)

            if set_name in ('Visible to Customer', 'Visible to Consumer %'):
                # Global function
                sku_list = filter(
                    None, self.scif[self.scif['product_type'] ==
                                    'SKU'].product_ean_code.tolist())
                res_dict = self.diageo_generator.diageo_global_visible_percentage(
                    sku_list)

                if res_dict:

                    #saving in dictionary for  activation standard use
                    total_scores_dict.append(res_dict)

                    # Saving to new tables
                    parent_res = res_dict[-1]
                    self.commonV2.save_json_to_new_tables(res_dict)

                    # Saving to old tables
                    set_score = result = parent_res['result']
                    self.save_level2_and_level3(set_name=set_name,
                                                kpi_name=set_name,
                                                score=result)

            elif set_name in ('Secondary Displays', 'Secondary'):
                # Global function
                res_json = self.diageo_generator.diageo_global_secondary_display_secondary_function(
                )
                if res_json:
                    # saving in dictionary for  activation standard use
                    total_scores_dict.append(res_json)
                    # Saving to new tables
                    self.commonV2.write_to_db_result(
                        fk=res_json['fk'],
                        numerator_id=1,
                        denominator_id=self.store_id,
                        result=res_json['result'])
                    # Saving to old tables
                    set_score = self.tools.calculate_number_of_scenes(
                        location_type='Secondary')
                    self.save_level2_and_level3(set_name, set_name, set_score)

            elif set_name in ('Activation Standard'):
                manufacturer_fk = self.all_products[
                    self.all_products['manufacturer_name'] ==
                    self.DIAGEO_MANUFACTURER]['manufacturer_fk'].values[0]
                self.set_templates_data[
                    set_name] = self.tools.download_template(set_name)
                results_list = self.diageo_generator.diageo_global_activation_standard_function(
                    total_scores_dict, self.set_templates_data[set_name],
                    self.store_id, manufacturer_fk)

                for result in results_list['old_tables_level2and3']:
                    self.save_level2_and_level3(result['kpi_set_name'],
                                                result['kpi_name'],
                                                result['score'])

                # saving results to old table
                self.write_to_db_result(
                    results_list['old_tables_level1']['fk'],
                    results_list['old_tables_level1']['score'],
                    results_list['old_tables_level1']['level'])
                res_json = results_list['new_tables_result']
                # saving results to new tables
                self.commonV2.save_json_to_new_tables(res_json)

            if set_score == 0:
                pass
            elif set_score is False:
                continue
            if set_name not in ('Activation Standard'):
                set_fk = self.kpi_static_data[
                    self.kpi_static_data['kpi_set_name'] ==
                    set_name]['kpi_set_fk'].values[0]
                self.write_to_db_result(set_fk, set_score, self.LEVEL1)

        # committing to new tables
        self.commonV2.commit_results_data()
        return

    def save_level2_and_level3(self, set_name, kpi_name, score):
        """
        Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
        """
        kpi_data = self.kpi_static_data[
            (self.kpi_static_data['kpi_set_name'] == set_name)
            & (self.kpi_static_data['kpi_name'] == kpi_name)]

        kpi_fk = kpi_data['kpi_fk'].values[0]
        self.kpi_scores[kpi_fk] = score
        atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
        self.write_to_db_result(kpi_fk, score, self.LEVEL2)
        self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)

    def calculate_brand_pouring_sets(self, set_name):
        """
        This function calculates every Brand-Pouring-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.tools.calculate_number_of_scenes(
                    **{self.tools.BRAND_POURING_FIELD: 'Y'}) > 0:
                # 'Pouring' scenes
                result = self.tools.calculate_brand_pouring_status(
                    params.get(self.tools.BRAND_NAME),
                    **{self.tools.BRAND_POURING_FIELD: 'Y'})
            elif self.tools.calculate_number_of_scenes(
                    **{self.tools.BRAND_POURING_FIELD: 'back_bar'}) > 0:
                # 'Back Bar' scenes
                result = self.tools.calculate_brand_pouring_status(
                    params.get(self.tools.BRAND_NAME),
                    **{self.tools.BRAND_POURING_FIELD: 'back_bar'})
            else:
                result = 0
            score = 1 if result else 0
            scores.append(score)

            self.save_level2_and_level3(set_name,
                                        params.get(self.tools.KPI_NAME), score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def calculate_posm_sets(self, set_name):
        """
        This function calculates every POSM-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.store_channel is None:
                break
            kpi_res = self.tools.calculate_posm(
                display_name=params.get(self.tools.DISPLAY_NAME))
            score = 1 if kpi_res > 0 else 0
            if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
                scores.append(score)
            if score == 1 or params.get(
                    self.store_type) == self.tools.RELEVANT_FOR_STORE:
                self.save_level2_and_level3(
                    set_name, params.get(self.tools.DISPLAY_NAME), score)
        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def calculate_assortment_sets(self, set_name):
        """
        This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            target = str(params.get(self.store_type, ''))
            if target.isdigit() or target.capitalize() in (
                    self.tools.RELEVANT_FOR_STORE,
                    self.tools.OR_OTHER_PRODUCTS):
                products = str(
                    params.get(self.tools.PRODUCT_EAN_CODE,
                               params.get(self.tools.PRODUCT_EAN_CODE2,
                                          ''))).replace(',', ' ').split()
                target = 1 if not target.isdigit() else int(target)
                kpi_name = params.get(self.tools.GROUP_NAME,
                                      params.get(self.tools.PRODUCT_NAME))
                kpi_static_data = self.kpi_static_data[
                    (self.kpi_static_data['kpi_set_name'] == set_name)
                    & (self.kpi_static_data['kpi_name'] == kpi_name)]
                if len(products) > 1:
                    result = 0
                    for product in products:
                        product_score = self.tools.calculate_assortment(
                            product_ean_code=product)
                        result += product_score
                        atomic_fk = kpi_static_data[
                            kpi_static_data['description'] ==
                            product]['atomic_kpi_fk'].values[0]
                        self.write_to_db_result(atomic_fk,
                                                product_score,
                                                level=self.LEVEL3)
                    score = 1 if result >= target else 0
                else:
                    result = self.tools.calculate_assortment(
                        product_ean_code=products)
                    atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
                    score = 1 if result >= target else 0
                    self.write_to_db_result(atomic_fk,
                                            score,
                                            level=self.LEVEL3)

                scores.append(score)
                kpi_fk = kpi_static_data['kpi_fk'].values[0]
                self.kpi_scores[kpi_fk] = score
                self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def calculate_activation_standard(self):
        """
        This function calculates the Activation Standard KPI, and saves the result to the DB (for all 3 levels).
        """
        final_score = 0
        template = self.tools.download_template(self.ACTIVATION_STANDARD)
        for params in template:
            set_name = params.get(self.tools.ACTIVATION_SET_NAME)
            kpi_name = params.get(self.tools.ACTIVATION_KPI_NAME)
            target = float(params.get(self.tools.ACTIVATION_TARGET))
            target = target * 100 if target < 1 else target
            score_type = params.get(self.tools.ACTIVATION_SCORE)
            weight = float(params.get(self.tools.ACTIVATION_WEIGHT))
            if kpi_name:
                kpi_fk = self.kpi_static_data[
                    (self.kpi_static_data['kpi_set_name'] == set_name)
                    & (self.kpi_static_data['kpi_name'] == kpi_name
                       )]['kpi_fk'].values[0]
                score = self.kpi_scores.get(kpi_fk)
            else:
                set_fk = self.kpi_static_data[
                    self.kpi_static_data['kpi_set_name'] ==
                    set_name]['kpi_set_fk'].values[0]
                score = self.set_scores.get(set_fk)
            if score >= target:
                score = 100
            else:
                if score_type == 'PROPORTIONAL':
                    score = (score / float(target)) * 100
                else:
                    score = 0
            final_score += score * weight
            self.save_level2_and_level3(self.ACTIVATION_STANDARD, set_name,
                                        score)
        total_score = 100 if final_score == 100 else 0
        set_fk = self.kpi_static_data[
            self.kpi_static_data['kpi_set_name'] ==
            self.ACTIVATION_STANDARD]['kpi_set_fk'].values[0]
        self.write_to_db_result(set_fk, total_score, self.LEVEL1)

    def write_to_db_result(self, fk, score, level):
        """
        This function the result data frame of every KPI (atomic KPI/KPI/KPI set),
        and appends the insert SQL query into the queries' list, later to be written to the DB.
        """
        attributes = self.create_attributes_dict(fk, score, level)
        if level == self.LEVEL1:
            table = KPS_RESULT
        elif level == self.LEVEL2:
            table = KPK_RESULT
        elif level == self.LEVEL3:
            table = KPI_RESULT
        else:
            return
        query = insert(attributes, table)
        self.kpi_results_queries.append(query)

    def create_attributes_dict(self, fk, score, level):
        """
        This function creates a data frame with all attributes needed for saving in KPI results tables.

        """
        score = round(score, 2)
        if level == self.LEVEL1:
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['kpi_set_fk'] ==
                fk]['kpi_set_name'].values[0]
            score_type = '%' if kpi_set_name in self.tools.KPI_SETS_WITH_PERCENT_AS_SCORE else ''
            attributes = pd.DataFrame(
                [(kpi_set_name, self.session_uid, self.store_id,
                  self.visit_date.isoformat(), format(score,
                                                      '.2f'), score_type, fk)],
                columns=[
                    'kps_name', 'session_uid', 'store_fk', 'visit_date',
                    'score_1', 'score_2', 'kpi_set_fk'
                ])

        elif level == self.LEVEL2:
            kpi_name = self.kpi_static_data[self.kpi_static_data['kpi_fk'] ==
                                            fk]['kpi_name'].values[0].replace(
                                                "'", "\\'")
            attributes = pd.DataFrame(
                [(self.session_uid, self.store_id, self.visit_date.isoformat(),
                  fk, kpi_name, score)],
                columns=[
                    'session_uid', 'store_fk', 'visit_date', 'kpi_fk',
                    'kpk_name', 'score'
                ])
        elif level == self.LEVEL3:
            data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
                                        == fk]
            atomic_kpi_name = data['atomic_kpi_name'].values[0].replace(
                "'", "\\'")
            kpi_fk = data['kpi_fk'].values[0]
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['atomic_kpi_fk'] ==
                fk]['kpi_set_name'].values[0]
            attributes = pd.DataFrame([
                (atomic_kpi_name, self.session_uid,
                 kpi_set_name, self.store_id, self.visit_date.isoformat(),
                 datetime.utcnow().isoformat(), score, kpi_fk, fk, None, None)
            ],
                                      columns=[
                                          'display_text', 'session_uid',
                                          'kps_name', 'store_fk', 'visit_date',
                                          'calculation_time', 'score',
                                          'kpi_fk', 'atomic_kpi_fk',
                                          'threshold', 'result'
                                      ])
        else:
            attributes = pd.DataFrame()
        return attributes.to_dict()

    @log_runtime('Saving to DB')
    def commit_results_data(self):
        """
        This function writes all KPI results to the DB, and commits the changes.
        """
        cur = self.rds_conn.db.cursor()
        delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
            self.session_uid)
        for query in delete_queries:
            cur.execute(query)
        for query in self.kpi_results_queries:
            cur.execute(query)
        self.rds_conn.db.commit()
Ejemplo n.º 2
0
class PENAFLORAR_SANDDIAGEOARToolBox:
    LEVEL1 = 1
    LEVEL2 = 2
    LEVEL3 = 3
    ACTIVATION_STANDARD = 'Activation Standard'

    def __init__(self, data_provider, output):
        self.output = output
        self.data_provider = data_provider
        self.project_name = self.data_provider.project_name
        self.session_uid = self.data_provider.session_uid
        self.products = self.data_provider[Data.PRODUCTS]
        self.all_products = self.data_provider[Data.ALL_PRODUCTS]
        self.match_product_in_scene = self.data_provider[Data.MATCHES]
        self.visit_date = self.data_provider[Data.VISIT_DATE]
        self.session_info = self.data_provider[Data.SESSION_INFO]
        self.scene_info = self.data_provider[Data.SCENES_INFO]
        self.store_id = self.data_provider[Data.STORE_FK]
        self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
        self.rds_conn = PSProjectConnector(self.project_name,
                                           DbUsers.CalculationEng)
        self.store_info = self.data_provider[Data.STORE_INFO]
        self.store_type = self.store_info['additional_attribute_1'].values[0]
        self.kpi_static_data = self.get_kpi_static_data()
        self.set_templates_data = {}
        self.match_display_in_scene = self.get_match_display()
        self.kpi_results_queries = []
        self.scores = {self.LEVEL1: {}, self.LEVEL2: {}, self.LEVEL3: {}}

        self.output = output
        self.common = Common(self.data_provider)
        self.commonV2 = CommonV2(self.data_provider)
        self.global_gen = DIAGEOGenerator(self.data_provider, self.output,
                                          self.common)
        self.tools = DIAGEOToolBox(
            self.data_provider,
            output,
            match_display_in_scene=self.match_display_in_scene
        )  # replace the old one
        self.diageo_generator = DIAGEOGenerator(self.data_provider,
                                                self.output, self.common)

    def get_kpi_static_data(self):
        """
        This function extracts the static KPI data and saves it into one global data frame.
        The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
        """
        query = DIAGEOQueries.get_all_kpi_data()
        kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
        return kpi_static_data

    def get_match_display(self):
        """
        This function extracts the display matches data and saves it into one global data frame.
        The data is taken from probedata.match_display_in_scene.
        """
        query = DIAGEOQueries.get_match_display(self.session_uid)
        match_display = pd.read_sql_query(query, self.rds_conn.db)
        return match_display

    def main_calculation(self, set_names):
        """
        This function calculates the KPI results.
        """
        log_runtime('Updating templates')(self.tools.update_templates)()

        # Global assortment kpis
        assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2(
        )
        self.commonV2.save_json_to_new_tables(assortment_res_dict)

        for set_name in set_names:
            set_score = 0
            if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys(
            ):
                try:
                    self.set_templates_data[
                        set_name] = self.tools.download_template(set_name)
                except:
                    Log.warning("Couldn't find a template for set name: " +
                                str(set_name))
                    continue
            # if set_name in ('MPA', 'New Products',):
            #     set_score = self.calculate_assortment_sets(set_name)

            # Global Visible to Customer / Visible to Consumer
            if set_name in ('Visible to Customer', 'Visible to Consumer %'):
                # Global function
                sku_list = filter(
                    None, self.scif[self.scif['product_type'] ==
                                    'SKU'].product_ean_code.tolist())
                res_dict = self.diageo_generator.diageo_global_visible_percentage(
                    sku_list)

                if res_dict:
                    # Saving to new tables
                    # parent_res = res_dict[-1]
                    self.commonV2.save_json_to_new_tables(res_dict)

                    # Saving to old tables
                    # result = parent_res['result']
                    # self.save_level2_and_level3(set_name=set_name, kpi_name=set_name, score=result)

                # Saving to old tables
                filters = {self.tools.VISIBILITY_PRODUCTS_FIELD: 'Y'}
                set_score = self.tools.calculate_visible_percentage(
                    visible_filters=filters)
                self.save_level2_and_level3(set_name, set_name, set_score)

            elif set_name in ('Relative Position'):
                # Global function
                res_dict = self.diageo_generator.diageo_global_relative_position_function(
                    self.set_templates_data[set_name],
                    location_type='template_display_name')

                if res_dict:
                    # Saving to new tables
                    self.commonV2.save_json_to_new_tables(res_dict)

                set_score = self.calculate_relative_position_sets(set_name)
            else:
                return

            if set_score == 0:
                pass
            elif set_score is False:
                continue

            set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name']
                                          == set_name]['kpi_set_fk'].values[0]
            self.write_to_db_result(set_fk, set_score, self.LEVEL1)

        # commiting to new tables
        self.commonV2.commit_results_data()

    def calculate_relative_position_sets(self, set_name):
        """
        This function calculates every relative-position-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.store_info.at[0, 'additional_attribute_2'] == params.get(
                    'additional_attribute_2', 'Empty'):
                tested_filters = {
                    params.get(TESTED_TYPE): params.get(TESTED_VALUE)
                }
                anchor_filters = {
                    params.get(ANCHOR_TYPE): params.get(ANCHOR_VALUE)
                }
                direction_data = {
                    'top':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.TOP_DISTANCE)),
                    'bottom':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.BOTTOM_DISTANCE)),
                    'left':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.LEFT_DISTANCE)),
                    'right':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.RIGHT_DISTANCE))
                }
                general_filters = {
                    'template_display_name': params.get(self.tools.LOCATION)
                }
                result = self.tools.calculate_relative_position(
                    tested_filters, anchor_filters, direction_data,
                    **general_filters)
                score = 1 if result else 0
                scores.append(score)

                self.save_level2_and_level3(set_name,
                                            params.get(self.tools.KPI_NAME),
                                            score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def _get_direction_for_relative_position(self, value):
        """
        This function converts direction data from the template (as string) to a number.
        """
        if value == self.tools.UNLIMITED_DISTANCE:
            value = 1000
        elif not value or not str(value).isdigit():
            value = 0
        else:
            value = int(value)
        return value

    def calculate_assortment_sets(self, set_name):
        """
        This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            target = str(params.get(self.store_type, ''))
            if target.isdigit() or target.capitalize() in (
                    self.tools.RELEVANT_FOR_STORE,
                    self.tools.OR_OTHER_PRODUCTS):
                products = str(
                    params.get(self.tools.PRODUCT_EAN_CODE,
                               params.get(self.tools.PRODUCT_EAN_CODE2,
                                          ''))).replace(',', ' ').split()
                target = 1 if not target.isdigit() else int(target)
                kpi_name = params.get(self.tools.GROUP_NAME,
                                      params.get(self.tools.PRODUCT_NAME))
                kpi_static_data = self.kpi_static_data[
                    (self.kpi_static_data['kpi_set_name'] == set_name)
                    & (self.kpi_static_data['kpi_name'] == kpi_name)]
                if len(products) > 1:
                    result = 0
                    for product in products:
                        product_score = self.tools.calculate_assortment(
                            product_ean_code=product)
                        result += product_score
                        atomic_fk = kpi_static_data[
                            kpi_static_data['description'] ==
                            product]['atomic_kpi_fk'].values[0]
                        self.write_to_db_result(atomic_fk,
                                                product_score,
                                                level=self.LEVEL3)
                    score = 1 if result >= target else 0
                else:
                    result = self.tools.calculate_assortment(
                        product_ean_code=products)
                    atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
                    score = 1 if result >= target else 0
                    self.write_to_db_result(atomic_fk,
                                            score,
                                            level=self.LEVEL3)

                scores.append(score)
                kpi_fk = kpi_static_data['kpi_fk'].values[0]
                self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def calculate_activation_standard(self):
        """
        This function calculates the Activation Standard KPI, and saves the result to the DB (for all 3 levels).
        """
        final_score = 0
        for params in self.tools.download_template(self.ACTIVATION_STANDARD):
            set_name = params.get(self.tools.ACTIVATION_SET_NAME)
            kpi_name = params.get(self.tools.ACTIVATION_KPI_NAME)
            target = float(params.get(self.tools.ACTIVATION_TARGET))
            target = target * 100 if target < 1 else target
            score_type = params.get(self.tools.ACTIVATION_SCORE)
            weight = float(params.get(self.tools.ACTIVATION_WEIGHT))
            if kpi_name:
                kpi_fk = self.kpi_static_data[
                    (self.kpi_static_data['kpi_set_name'] == set_name)
                    & (self.kpi_static_data['kpi_name'] == kpi_name
                       )]['kpi_fk'].values[0]
                score = self.scores[self.LEVEL2].get(kpi_fk, 0)
            else:
                set_fk = self.kpi_static_data[
                    self.kpi_static_data['kpi_set_name'] ==
                    set_name]['kpi_set_fk'].values[0]
                score = self.scores[self.LEVEL1].get(set_fk, 0)
            if score >= target:
                score = 100
            else:
                if score_type == 'PROPORTIONAL':
                    score = (score / float(target)) * 100
                else:
                    score = 0
            final_score += score * weight
            self.save_level2_and_level3(self.ACTIVATION_STANDARD, set_name,
                                        score)
        set_fk = self.kpi_static_data[
            self.kpi_static_data['kpi_set_name'] ==
            self.ACTIVATION_STANDARD]['kpi_set_fk'].values[0]
        self.write_to_db_result(set_fk, final_score, self.LEVEL1)

    def save_level2_and_level3(self, set_name, kpi_name, score):
        """
        Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
        """
        kpi_data = self.kpi_static_data[
            (self.kpi_static_data['kpi_set_name'] == set_name)
            & (self.kpi_static_data['kpi_name'] == kpi_name)]
        try:
            kpi_fk = kpi_data['kpi_fk'].values[0]
        except:
            Log.warning("kpi name or set name don't exist")
            return
        atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
        self.write_to_db_result(kpi_fk, score, self.LEVEL2)
        self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)

    def write_to_db_result(self, fk, score, level):
        """
        This function creates the result data frame of every KPI (atomic KPI/KPI/KPI set),
        and appends the insert SQL query into the queries' list, later to be written to the DB.
        """
        attributes = self.create_attributes_dict(fk, score, level)
        if level == self.LEVEL1:
            table = KPS_RESULT
        elif level == self.LEVEL2:
            table = KPK_RESULT
        elif level == self.LEVEL3:
            table = KPI_RESULT
        else:
            return
        query = insert(attributes, table)
        self.kpi_results_queries.append(query)

    def create_attributes_dict(self, fk, score, level):
        """
        This function creates a data frame with all attributes needed for saving in KPI results tables.

        """
        if level == self.LEVEL1:
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['kpi_set_fk'] ==
                fk]['kpi_set_name'].values[0]
            attributes = pd.DataFrame(
                [(kpi_set_name, self.session_uid, self.store_id,
                  self.visit_date.isoformat(), format(score, '.2f'), fk)],
                columns=[
                    'kps_name', 'session_uid', 'store_fk', 'visit_date',
                    'score_1', 'kpi_set_fk'
                ])
        elif level == self.LEVEL2:
            kpi_name = self.kpi_static_data[self.kpi_static_data['kpi_fk'] ==
                                            fk]['kpi_name'].values[0]
            attributes = pd.DataFrame(
                [(self.session_uid, self.store_id, self.visit_date.isoformat(),
                  fk, kpi_name, score)],
                columns=[
                    'session_uid', 'store_fk', 'visit_date', 'kpi_fk',
                    'kpk_name', 'score'
                ])
        elif level == self.LEVEL3:
            data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
                                        == fk]
            atomic_kpi_name = data['atomic_kpi_name'].values[0]
            kpi_fk = data['kpi_fk'].values[0]
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['atomic_kpi_fk'] ==
                fk]['kpi_set_name'].values[0]
            attributes = pd.DataFrame(
                [(atomic_kpi_name, self.session_uid,
                  kpi_set_name, self.store_id, self.visit_date.isoformat(),
                  datetime.utcnow().isoformat(), score, kpi_fk, fk)],
                columns=[
                    'display_text', 'session_uid', 'kps_name', 'store_fk',
                    'visit_date', 'calculation_time', 'score', 'kpi_fk',
                    'atomic_kpi_fk'
                ])
        else:
            attributes = pd.DataFrame()
        return attributes.to_dict()

    @log_runtime('Saving to DB')
    def commit_results_data(self):
        """
        This function writes all KPI results to the DB, and commits the changes.
        """
        insert_queries = self.merge_insert_queries(self.kpi_results_queries)
        self.rds_conn.disconnect_rds()
        self.rds_conn.connect_rds()
        cur = self.rds_conn.db.cursor()
        delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
            self.session_uid)
        for query in delete_queries:
            cur.execute(query)
        for query in insert_queries:
            cur.execute(query)
        self.rds_conn.db.commit()

    @staticmethod
    def merge_insert_queries(insert_queries):
        query_groups = {}
        for query in insert_queries:
            static_data, inserted_data = query.split('VALUES ')
            if static_data not in query_groups:
                query_groups[static_data] = []
            query_groups[static_data].append(inserted_data)
        merged_queries = []
        for group in query_groups:
            merged_queries.append('{0} VALUES {1}'.format(
                group, ',\n'.join(query_groups[group])))
        return merged_queries
Ejemplo n.º 3
0
class DIAGEOBR_SANDToolBox:

    LEVEL1 = 1
    LEVEL2 = 2
    LEVEL3 = 3

    def __init__(self, data_provider, output):
        self.k_engine = BaseCalculationsScript(data_provider, output)
        self.output = output
        self.data_provider = data_provider
        self.project_name = self.data_provider.project_name
        self.session_uid = self.data_provider.session_uid
        self.products = self.data_provider[Data.PRODUCTS]
        self.all_products = self.data_provider[Data.ALL_PRODUCTS]
        self.match_product_in_scene = self.data_provider[Data.MATCHES]
        self.visit_date = self.data_provider[Data.VISIT_DATE]
        self.session_info = self.data_provider[Data.SESSION_INFO]
        self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
        self.store_info = self.data_provider[Data.STORE_INFO]
        self.store_id = self.data_provider[Data.STORE_FK]
        self.store_channel = self.store_info['store_type'].values[0]
        if self.store_channel:
            self.store_channel = self.store_channel.upper()
        self.store_type = self.store_info['additional_attribute_1'].values[0]
        self.segment = self.get_business_unit_name()
        self.scene_info = self.data_provider[Data.SCENES_INFO]
        self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
        self.match_display_in_scene = self.get_match_display()
        self.set_templates_data = {}
        self.kpi_static_data = self.get_kpi_static_data()
        self.tools = DIAGEOToolBox(self.data_provider, output, match_display_in_scene=self.match_display_in_scene)
        self.kpi_results_queries = []
        self.common = Common(self.data_provider)
        self.commonV2 = CommonV2(self.data_provider)
        self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common)



    def get_business_unit_name(self):
        """
        This function extracts the static KPI data and saves it into one global data frame.
        The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
        """
        query = DIAGEOQueries.get_business_unit_name(self.store_id)
        business_unit_name = pd.read_sql_query(query, self.rds_conn.db)
        if business_unit_name['business_unit_name'].empty:
            return ""
        else:
            return business_unit_name['business_unit_name'].values[0]

    def get_kpi_static_data(self):
        """
        This function extracts the static KPI data and saves it into one global data frame.
        The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
        """
        query = DIAGEOQueries.get_all_kpi_data()
        kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
        return kpi_static_data

    def get_match_display(self):
        """
        This function extracts the display matches data and saves it into one global data frame.
        The data is taken from probedata.match_display_in_scene.
        """
        query = DIAGEOQueries.get_match_display(self.session_uid)
        match_display = pd.read_sql_query(query, self.rds_conn.db)
        return match_display

    def main_calculation(self, set_names):
        """
        This function calculates the KPI results.
        """
        log_runtime('Updating templates')(self.tools.update_templates)()

        # Global assortment kpis
        assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2()
        self.commonV2.save_json_to_new_tables(assortment_res_dict)

        for set_name in set_names:
            set_score = 0
            if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys():
                try:
                    self.set_templates_data[set_name] = self.tools.download_template(set_name)
                except:
                    Log.warning("Couldn't find a template for set name: " + str(set_name))
                    continue

            # if set_name in ('MPA', 'New Products'):
            #     set_score = self.calculate_assortment_sets(set_name)
            # elif set_name in ('POSM',):
            #     set_score = self.calculate_posm_sets(set_name)
            if set_name == 'Visible to Customer':

                # Global function
                sku_list = filter(None, self.scif[self.scif['product_type'] == 'SKU'].product_ean_code.tolist())
                res_dict = self.diageo_generator.diageo_global_visible_percentage(sku_list)

                if res_dict:
                    # Saving to new tables
                    parent_res = res_dict[-1]
                    for r in res_dict:
                        self.commonV2.write_to_db_result(**r)

                    # Saving to old tables
                    set_score = result = parent_res['result']
                    self.save_level2_and_level3(set_name=set_name, kpi_name=set_name, score=result)

                # filters = {self.tools.VISIBILITY_PRODUCTS_FIELD: 'Y'}
                # set_score = self.tools.calculate_visible_percentage(visible_filters=filters)
                # self.save_level2_and_level3(set_name, set_name, set_score)

            elif set_name in ('Secondary Displays', 'Secondary'):
                # Global function
                res_dict = self.diageo_generator.diageo_global_secondary_display_secondary_function()

                # Saving to new tables
                if res_dict:
                    self.commonV2.write_to_db_result(fk=res_dict['fk'], numerator_id=1, denominator_id=self.store_id,
                                                                                            result=res_dict['result'])

                # Saving to old tables
                set_score = self.tools.calculate_assortment(assortment_entity='scene_id', location_type='Secondary Shelf')
                self.save_level2_and_level3(set_name, set_name, set_score)

            if set_score == 0:
                pass
            elif set_score is False:
                return

            set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name'] == set_name]['kpi_set_fk'].values[0]
            self.write_to_db_result(set_fk, set_score, self.LEVEL1)

        # commiting to new tables
        self.commonV2.commit_results_data()

    def save_level2_and_level3(self, set_name, kpi_name, score):
        """
        Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
        """
        kpi_data = self.kpi_static_data[(self.kpi_static_data['kpi_set_name'] == set_name) &
                                        (self.kpi_static_data['kpi_name'] == kpi_name)]
        try:
            kpi_fk = kpi_data['kpi_fk'].values[0]
        except:
            Log.warning("kpi name or set name don't exist")
            return
        atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
        self.write_to_db_result(kpi_fk, score, self.LEVEL2)
        self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)

    def calculate_posm_sets(self, set_name):
        """
        This function calculates every POSM-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.store_channel is None:
                break

            kpi_res = self.tools.calculate_posm(display_name=params.get(self.tools.DISPLAY_NAME))
            score = 1 if kpi_res > 0 else 0
            if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
                scores.append(score)

            if score == 1 or params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
                self.save_level2_and_level3(set_name, params.get(self.tools.DISPLAY_NAME), score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def calculate_assortment_sets(self, set_name):
        """
        This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        segment = '{};{}'.format(self.store_type, self.segment)
        for params in self.set_templates_data[set_name]:
            if params.get(segment, '').capitalize() in (self.tools.RELEVANT_FOR_STORE,
                                                                self.tools.OR_OTHER_PRODUCTS):

                object_type = self.tools.ENTITY_TYPE_CONVERTER.get(params.get(self.tools.ENTITY_TYPE),
                                                                   'product_ean_code')
                objects = [str(params.get(self.tools.PRODUCT_EAN_CODE, params.get(self.tools.PRODUCT_EAN_CODE2, '')))]
                if params.get(self.store_type) == self.tools.OR_OTHER_PRODUCTS:
                    additional_objects = str(params.get(self.tools.ADDITIONAL_SKUS)).split(',')
                    objects.extend(additional_objects)
                filters = {object_type: objects}
                result = self.tools.calculate_assortment(**filters)
                score = 1 if result > 0 else 0
                scores.append(score)

                self.save_level2_and_level3(set_name, params.get(self.tools.PRODUCT_NAME), score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def write_to_db_result(self, fk, score, level):
        """
        This function the result data frame of every KPI (atomic KPI/KPI/KPI set),
        and appends the insert SQL query into the queries' list, later to be written to the DB.
        """
        attributes = self.create_attributes_dict(fk, score, level)
        if level == self.LEVEL1:
            table = KPS_RESULT
        elif level == self.LEVEL2:
            table = KPK_RESULT
        elif level == self.LEVEL3:
            table = KPI_RESULT
        else:
            return
        query = insert(attributes, table)
        self.kpi_results_queries.append(query)

    def create_attributes_dict(self, fk, score, level):
        """
        This function creates a data frame with all attributes needed for saving in KPI results tables.

        """
        score = round(score, 2)
        if level == self.LEVEL1:
            kpi_set_name = self.kpi_static_data[self.kpi_static_data['kpi_set_fk'] == fk]['kpi_set_name'].values[0]
            score_type = '%' if kpi_set_name in self.tools.KPI_SETS_WITH_PERCENT_AS_SCORE else ''
            attributes = pd.DataFrame([(kpi_set_name, self.session_uid, self.store_id, self.visit_date.isoformat(),
                                        format(score, '.2f'), score_type, fk)],
                                      columns=['kps_name', 'session_uid', 'store_fk', 'visit_date', 'score_1',
                                               'score_2', 'kpi_set_fk'])

        elif level == self.LEVEL2:
            kpi_name = self.kpi_static_data[self.kpi_static_data['kpi_fk'] == fk]['kpi_name'].values[0].replace("'", "\\'")
            attributes = pd.DataFrame([(self.session_uid, self.store_id, self.visit_date.isoformat(),
                                        fk, kpi_name, score)],
                                      columns=['session_uid', 'store_fk', 'visit_date', 'kpi_fk', 'kpk_name', 'score'])
        elif level == self.LEVEL3:
            data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk'] == fk]
            atomic_kpi_name = data['atomic_kpi_name'].values[0].replace("'", "\\'")
            kpi_fk = data['kpi_fk'].values[0]
            kpi_set_name = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk'] == fk]['kpi_set_name'].values[0]
            attributes = pd.DataFrame([(atomic_kpi_name, self.session_uid, kpi_set_name, self.store_id,
                                        self.visit_date.isoformat(), datetime.utcnow().isoformat(),
                                        score, kpi_fk, fk, None, None)],
                                      columns=['display_text', 'session_uid', 'kps_name', 'store_fk', 'visit_date',
                                               'calculation_time', 'score', 'kpi_fk', 'atomic_kpi_fk', 'threshold',
                                               'result'])
        else:
            attributes = pd.DataFrame()
        return attributes.to_dict()

    @log_runtime('Saving to DB')
    def commit_results_data(self):
        """
        This function writes all KPI results to the DB, and commits the changes.
        """
        self.rds_conn.disconnect_rds()
        self.rds_conn.connect_rds()
        cur = self.rds_conn.db.cursor()
        delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(self.session_uid)
        for query in delete_queries:
            cur.execute(query)
        for query in self.kpi_results_queries:
            cur.execute(query)
        self.rds_conn.db.commit()
Ejemplo n.º 4
0
class DIAGEOBENELUX_SANDToolBox:

    LEVEL1 = 1
    LEVEL2 = 2
    LEVEL3 = 3

    ACTIVATION_STANDARD = 'Activation Standard'

    def __init__(self, data_provider, output):
        self.k_engine = BaseCalculationsScript(data_provider, output)
        self.data_provider = data_provider
        self.project_name = self.data_provider.project_name
        self.session_uid = self.data_provider.session_uid
        self.products = self.data_provider[Data.PRODUCTS]
        self.all_products = self.data_provider[Data.ALL_PRODUCTS]
        self.match_product_in_scene = self.data_provider[Data.MATCHES]
        self.visit_date = self.data_provider[Data.VISIT_DATE]
        self.session_info = self.data_provider[Data.SESSION_INFO]
        self.rds_conn = PSProjectConnector(self.project_name,
                                           DbUsers.CalculationEng)
        self.store_info = self.data_provider[Data.STORE_INFO]
        self.store_channel = self.store_info['store_type'].values[0]
        if self.store_channel:
            self.store_channel = self.store_channel.upper()
        self.store_type = self.store_info['additional_attribute_1'].values[0]
        self.scene_info = self.data_provider[Data.SCENES_INFO]
        self.store_id = self.data_provider[Data.STORE_FK]
        self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
        self.match_display_in_scene = self.get_match_display()
        self.set_templates_data = {}
        self.kpi_static_data = self.get_kpi_static_data()
        self.output = output
        self.common = Common(self.data_provider)
        self.commonV2 = CommonV2(self.data_provider)
        self.global_gen = DIAGEOGenerator(self.data_provider, self.output,
                                          self.common)
        self.tools = DIAGEOToolBox(
            self.data_provider,
            output,
            match_display_in_scene=self.match_display_in_scene
        )  # replace the old one
        self.diageo_generator = DIAGEOGenerator(self.data_provider,
                                                self.output,
                                                self.common,
                                                menu=True)
        self.scores = {self.LEVEL1: {}, self.LEVEL2: {}, self.LEVEL3: {}}
        self.kpi_results_queries = []

    def get_kpi_static_data(self):
        """
        This function extracts the static KPI data and saves it into one global data frame.
        The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
        """
        query = DIAGEOQueries.get_all_kpi_data()
        kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
        return kpi_static_data

    def get_match_display(self):
        """
        This function extracts the display matches data and saves it into one global data frame.
        The data is taken from probedata.match_display_in_scene.
        """
        query = DIAGEOQueries.get_match_display(self.session_uid)
        match_display = pd.read_sql_query(query, self.rds_conn.db)
        return match_display

    def main_calculation(self):
        # SOS Out Of The Box kpis
        self.diageo_generator.activate_ootb_kpis(self.commonV2)

        # sos by scene type
        self.diageo_generator.sos_by_scene_type(self.commonV2)

        # Global assortment kpis
        assortment_res = self.diageo_generator.diageo_global_grouping_assortment_calculation(
        )
        self.commonV2.save_json_to_new_tables(assortment_res)

        # Global Menu kpis
        menus_res = self.diageo_generator.diageo_global_new_share_of_menu_function(
        )
        self.commonV2.save_json_to_new_tables(menus_res)

        # Global Secondary Displays function
        res_json = self.diageo_generator.diageo_global_secondary_display_secondary_function(
        )
        if res_json:
            self.commonV2.write_to_db_result(fk=res_json['fk'],
                                             numerator_id=1,
                                             denominator_id=self.store_id,
                                             result=res_json['result'])
        # committing to new tables
        self.commonV2.commit_results_data()
        # committing to the old tables
        self.common.commit_results_data()

    #
    # def main_calculation(self, set_names):
    #     """
    #     This function calculates the KPI results.
    #     """
    #     # Global assortment kpis
    #     assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2()
    #     self.commonV2.save_json_to_new_tables(assortment_res_dict)
    #
    #     # Global Menu kpis
    #     menus_res_dict = self.diageo_generator.diageo_global_share_of_menu_cocktail_function(
    #         cocktail_product_level=True)
    #     self.commonV2.save_json_to_new_tables(menus_res_dict)
    #
    #     for set_name in set_names:
    #         set_score = 0
    #
    #         # Global Secondary Displays
    #         if set_name in ('Secondary Displays', 'Secondary'):
    #             res_json = self.diageo_generator.diageo_global_secondary_display_secondary_function()
    #             if res_json:
    #                 self.commonV2.write_to_db_result(fk=res_json['fk'], numerator_id=1, denominator_id=self.store_id,
    #                                                  result=res_json['result'])
    #                 set_score = self.tools.calculate_number_of_scenes(location_type='Secondary')
    #                 self.save_level2_and_level3(set_name, set_name, set_score)
    #
    #         if set_score == 0:
    #             pass
    #         elif set_score is False:
    #             continue
    #
    #         set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name'] == set_name]['kpi_set_fk'].values[0]
    #         self.write_to_db_result(set_fk, set_score, self.LEVEL1)
    #
    #     # committing to new tables
    #     self.commonV2.commit_results_data()
    #
    #     return

    def save_level2_and_level3(self, set_name, kpi_name, score):
        """
        Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
        """
        kpi_data = self.kpi_static_data[
            (self.kpi_static_data['kpi_set_name'] == set_name)
            & (self.kpi_static_data['kpi_name'] == kpi_name)]
        kpi_fk = kpi_data['kpi_fk'].values[0]
        atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
        self.write_to_db_result(kpi_fk, score, self.LEVEL2)
        self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)

    def calculate_activation_standard(self):
        """
        This function calculates the Activation Standard KPI, and saves the result to the DB (for all 3 levels).
        """
        final_score = 0
        for params in self.tools.download_template(self.ACTIVATION_STANDARD):
            set_name = params.get(self.tools.ACTIVATION_SET_NAME)
            kpi_name = params.get(self.tools.ACTIVATION_KPI_NAME)
            target = float(params.get(self.tools.ACTIVATION_TARGET))
            target = target * 100 if target < 1 else target
            score_type = params.get(self.tools.ACTIVATION_SCORE)
            weight = float(params.get(self.tools.ACTIVATION_WEIGHT))
            if kpi_name:
                kpi_fk = self.kpi_static_data[
                    (self.kpi_static_data['kpi_set_name'] == set_name)
                    & (self.kpi_static_data['kpi_name'] == kpi_name
                       )]['kpi_fk'].values[0]
                score = self.scores[self.LEVEL2].get(kpi_fk, 0)
            else:
                set_fk = self.kpi_static_data[
                    self.kpi_static_data['kpi_set_name'] ==
                    set_name]['kpi_set_fk'].values[0]
                score = self.scores[self.LEVEL1].get(set_fk, 0)
            if score >= target:
                score = 100
            else:
                if score_type == 'PROPORTIONAL':
                    score = (score / float(target)) * 100
                else:
                    score = 0
            final_score += score * weight
            self.save_level2_and_level3(self.ACTIVATION_STANDARD, set_name,
                                        score)
        set_fk = self.kpi_static_data[
            self.kpi_static_data['kpi_set_name'] ==
            self.ACTIVATION_STANDARD]['kpi_set_fk'].values[0]
        self.write_to_db_result(set_fk, final_score, self.LEVEL1)

    def calculate_sos_sets(self, set_name):
        """
        This function calculates every SOS-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
                result = self.tools.calculate_share_of_shelf(
                    manufacturer=self.tools.DIAGEO,
                    include_empty=self.tools.EXCLUDE_EMPTY)
                score = 1 if result >= params.get(self.tools.TARGET) else 0
                scores.append(score)

                self.save_level2_and_level3(set_name,
                                            params.get(self.tools.KPI_NAME),
                                            score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def calculate_survey_sets(self, set_name):
        """
        This function calculates every Survey-Answer-typed KPI and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            survey_text = params.get(self.tools.SURVEY_QUESTION)
            answer_field = 'number_value' if params.get(
                self.tools.SURVEY_ANSWER_TYPE) == 'Numeric' else None
            answer_target = params.get(self.tools.SURVEY_ANSWER)
            survey_answer = self.tools.get_survey_answer(
                survey_text, answer_field)
            if answer_field == 'number_value':
                if str(answer_target).isdigit():
                    answer_target = '=={}'.format(answer_target)
                if not str(survey_answer).isdigit():
                    result = False
                else:
                    result = eval('{}{}'.format(survey_answer, answer_target))
            else:
                result = True if survey_answer in answer_target.split(
                    ';') else False
            score = 1 if result else 0
            scores.append(score)

            self.save_level2_and_level3(set_name,
                                        params.get(self.tools.KPI_NAME), score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def validate_survey(self, params):
        """
        This function checks whether the Survey-typed KPI should be run, based on the Execution Condition Field.
        """
        survey_validated = False
        condition = params.get(self.tools.SURVEY_CONDITION)
        if not condition:
            survey_validated = True
        else:
            question, answers = condition.split('=')
            survey_answer = self.tools.get_survey_answer(question)
            if survey_answer in answers.split(';'):
                survey_validated = True
        return survey_validated

    def calculate_block_together_sets(self, set_name):
        """
        This function calculates every block-together-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.store_channel == params.get(self.tools.CHANNEL,
                                                '').upper():
                filters = {'template_name': params.get(self.tools.LOCATION)}
                if params.get(self.tools.SUB_BRAND_NAME):
                    filters['sub_brand_name'] = params.get(
                        self.tools.SUB_BRAND_NAME)
                else:
                    filters['brand_name'] = params.get(self.tools.BRAND_NAME)
                result = self.tools.calculate_block_together(**filters)
                score = 1 if result else 0
                scores.append(score)

                self.save_level2_and_level3(set_name,
                                            params.get(self.tools.KPI_NAME),
                                            score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def calculate_relative_position_sets(self, set_name):
        """
        This function calculates every relative-position-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.store_channel == params.get(self.tools.CHANNEL,
                                                '').upper():
                tested_filters = {
                    'product_ean_code': params.get(self.tools.TESTED)
                }
                anchor_filters = {
                    'product_ean_code': params.get(self.tools.ANCHOR)
                }
                direction_data = {
                    'top':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.TOP_DISTANCE)),
                    'bottom':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.BOTTOM_DISTANCE)),
                    'left':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.LEFT_DISTANCE)),
                    'right':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.RIGHT_DISTANCE))
                }
                general_filters = {
                    'template_name': params.get(self.tools.LOCATION)
                }
                result = self.tools.calculate_relative_position(
                    tested_filters, anchor_filters, direction_data,
                    **general_filters)
                score = 1 if result else 0
                scores.append(score)

                self.save_level2_and_level3(set_name,
                                            params.get(self.tools.KPI_NAME),
                                            score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def _get_direction_for_relative_position(self, value):
        """
        This function converts direction data from the template (as string) to a number.
        """
        if value == self.tools.UNLIMITED_DISTANCE:
            value = 1000
        elif not value or not str(value).isdigit():
            value = 0
        else:
            value = int(value)
        return value

    def calculate_posm_sets(self, set_name):
        """
        This function calculates every POSM-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.store_channel is None:
                break

            kpi_res = self.tools.calculate_posm(
                display_name=params.get(self.tools.DISPLAY_NAME))
            score = 1 if kpi_res > 0 else 0
            if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
                scores.append(score)

            if score == 1 or params.get(
                    self.store_type) == self.tools.RELEVANT_FOR_STORE:
                self.save_level2_and_level3(
                    set_name, params.get(self.tools.DISPLAY_NAME), score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def calculate_assortment_sets(self, set_name):
        """
        This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            target = str(params.get(self.store_type, ''))
            if target.isdigit() or target.capitalize() in (
                    self.tools.RELEVANT_FOR_STORE,
                    self.tools.OR_OTHER_PRODUCTS):
                products = str(
                    params.get(self.tools.PRODUCT_EAN_CODE,
                               params.get(self.tools.PRODUCT_EAN_CODE2,
                                          ''))).replace(',', ' ').split()
                target = 1 if not target.isdigit() else int(target)
                kpi_name = params.get(self.tools.GROUP_NAME,
                                      params.get(self.tools.PRODUCT_NAME))
                kpi_static_data = self.kpi_static_data[
                    (self.kpi_static_data['kpi_set_name'] == set_name)
                    & (self.kpi_static_data['kpi_name'] == kpi_name)]
                if len(products) > 1:
                    result = 0
                    for product in products:
                        product_score = self.tools.calculate_assortment(
                            product_ean_code=product)
                        result += product_score
                        product_name = \
                        self.all_products[self.all_products['product_ean_code'] == product]['product_name'].values[0]
                        try:
                            atomic_fk = \
                            kpi_static_data[kpi_static_data['atomic_kpi_name'] == product_name]['atomic_kpi_fk'].values[
                                0]
                        except Exception as e:
                            Log.info(
                                'Product {} is not defined in the DB'.format(
                                    product_name))
                            continue
                        self.write_to_db_result(atomic_fk,
                                                product_score,
                                                level=self.LEVEL3)
                    score = 1 if result >= target else 0
                else:
                    result = self.tools.calculate_assortment(
                        product_ean_code=products)
                    atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
                    score = 1 if result >= target else 0
                    self.write_to_db_result(atomic_fk,
                                            score,
                                            level=self.LEVEL3)

                scores.append(score)
                kpi_fk = kpi_static_data['kpi_fk'].values[0]
                self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def write_to_db_result(self, fk, score, level):
        """
        This function the result data frame of every KPI (atomic KPI/KPI/KPI set),
        and appends the insert SQL query into the queries' list, later to be written to the DB.
        """
        attributes = self.create_attributes_dict(fk, score, level)
        if level == self.LEVEL1:
            table = KPS_RESULT
        elif level == self.LEVEL2:
            table = KPK_RESULT
        elif level == self.LEVEL3:
            table = KPI_RESULT
        else:
            return
        self.scores[level][fk] = score
        query = insert(attributes, table)
        self.kpi_results_queries.append(query)

    def create_attributes_dict(self, fk, score, level):
        """
        This function creates a data frame with all attributes needed for saving in KPI results tables.

        """
        score = round(score, 2)
        if level == self.LEVEL1:
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['kpi_set_fk'] ==
                fk]['kpi_set_name'].values[0]
            score_type = '%' if kpi_set_name in self.tools.KPI_SETS_WITH_PERCENT_AS_SCORE else ''
            attributes = pd.DataFrame(
                [(kpi_set_name, self.session_uid, self.store_id,
                  self.visit_date.isoformat(), format(score,
                                                      '.2f'), score_type, fk)],
                columns=[
                    'kps_name', 'session_uid', 'store_fk', 'visit_date',
                    'score_1', 'score_2', 'kpi_set_fk'
                ])

        elif level == self.LEVEL2:
            kpi_name = self.kpi_static_data[self.kpi_static_data['kpi_fk'] ==
                                            fk]['kpi_name'].values[0].replace(
                                                "'", "\\'")
            attributes = pd.DataFrame(
                [(self.session_uid, self.store_id, self.visit_date.isoformat(),
                  fk, kpi_name, score)],
                columns=[
                    'session_uid', 'store_fk', 'visit_date', 'kpi_fk',
                    'kpk_name', 'score'
                ])
        elif level == self.LEVEL3:
            data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
                                        == fk]
            atomic_kpi_name = data['atomic_kpi_name'].values[0].replace(
                "'", "\\'")
            kpi_fk = data['kpi_fk'].values[0]
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['atomic_kpi_fk'] ==
                fk]['kpi_set_name'].values[0]
            attributes = pd.DataFrame([
                (atomic_kpi_name, self.session_uid,
                 kpi_set_name, self.store_id, self.visit_date.isoformat(),
                 datetime.utcnow().isoformat(), score, kpi_fk, fk, None, None)
            ],
                                      columns=[
                                          'display_text', 'session_uid',
                                          'kps_name', 'store_fk', 'visit_date',
                                          'calculation_time', 'score',
                                          'kpi_fk', 'atomic_kpi_fk',
                                          'threshold', 'result'
                                      ])
        else:
            attributes = pd.DataFrame()
        return attributes.to_dict()

    @log_runtime('Saving to DB')
    def commit_results_data(self):
        """
        This function writes all KPI results to the DB, and commits the changes.
        """
        cur = self.rds_conn.db.cursor()
        delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
            self.session_uid)
        for query in delete_queries:
            cur.execute(query)
        for query in self.kpi_results_queries:
            cur.execute(query)
        self.rds_conn.db.commit()
Ejemplo n.º 5
0
class DIAGEOUK_SANDToolBox:

    LEVEL1 = 1
    LEVEL2 = 2
    LEVEL3 = 3

    def __init__(self, data_provider, output):
        self.k_engine = BaseCalculationsScript(data_provider, output)
        self.data_provider = data_provider
        self.project_name = self.data_provider.project_name
        self.session_uid = self.data_provider.session_uid
        self.products = self.data_provider[Data.PRODUCTS]
        self.all_products = self.data_provider[Data.ALL_PRODUCTS]
        self.match_product_in_scene = self.data_provider[Data.MATCHES]
        self.visit_date = self.data_provider[Data.VISIT_DATE]
        self.session_info = self.data_provider[Data.SESSION_INFO]
        self.rds_conn = PSProjectConnector(self.project_name,
                                           DbUsers.CalculationEng)
        self.store_info = self.data_provider[Data.STORE_INFO]
        self.store_channel = self.store_info['store_type'].values[0]
        if self.store_channel:
            self.store_channel = self.store_channel.upper()
        self.store_type = self.store_info['additional_attribute_1'].values[0]
        self.scene_info = self.data_provider[Data.SCENES_INFO]
        self.store_id = self.data_provider[Data.STORE_FK]
        self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
        self.match_display_in_scene = self.get_match_display()
        self.set_templates_data = {}
        self.kpi_static_data = self.get_kpi_static_data()
        self.kpi_results_queries = []
        self.output = output
        self.common = Common(self.data_provider)
        self.commonV2 = CommonV2(self.data_provider)
        self.global_gen = DIAGEOGenerator(self.data_provider, self.output,
                                          self.common)
        self.tools = DIAGEOToolBox(
            self.data_provider,
            output,
            match_display_in_scene=self.match_display_in_scene
        )  # replace the old one
        self.diageo_generator = DIAGEOGenerator(self.data_provider,
                                                self.output,
                                                self.common,
                                                menu=True)

    def get_kpi_static_data(self):
        """
        This function extracts the static KPI data and saves it into one global data frame.
        The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
        """
        query = DIAGEOQueries.get_all_kpi_data()
        kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
        return kpi_static_data

    def get_match_display(self):
        """
        This function extracts the display matches data and saves it into one global data frame.
        The data is taken from probedata.match_display_in_scene.
        """
        query = DIAGEOQueries.get_match_display(self.session_uid)
        match_display = pd.read_sql_query(query, self.rds_conn.db)
        return match_display

    def main_calculation(self, set_names):
        """
        This function calculates the KPI results.
        """
        log_runtime('Updating templates')(self.tools.update_templates)()
        # SOS Out Of The Box kpis
        self.activate_ootb_kpis()

        # Global assortment kpis
        assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2(
        )
        self.commonV2.save_json_to_new_tables(assortment_res_dict)

        # Global assortment kpis - v3 for NEW MOBILE REPORTS use
        assortment_res_dict_v3 = self.diageo_generator.diageo_global_assortment_function_v3(
        )
        self.commonV2.save_json_to_new_tables(assortment_res_dict_v3)

        equipment_score_scenes = self.get_equipment_score_relevant_scenes()
        res_dict = self.diageo_generator.diageo_global_equipment_score(
            save_scene_level=False, scene_list=equipment_score_scenes)
        self.commonV2.save_json_to_new_tables(res_dict)

        # Global Menu kpis
        menus_res_dict = self.diageo_generator.diageo_global_share_of_menu_cocktail_function(
            cocktail_product_level=True)
        self.commonV2.save_json_to_new_tables(menus_res_dict)

        for set_name in set_names:
            set_score = 0
            if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys(
            ):
                try:
                    self.set_templates_data[
                        set_name] = self.tools.download_template(set_name)
                except:
                    Log.warning("Couldn't find a template for set name: " +
                                str(set_name))
                    continue

            # Global relative position
            if set_name in ('Relative Position'):
                # Global function
                res_dict = self.diageo_generator.diageo_global_relative_position_function(
                    self.set_templates_data[set_name],
                    location_type='template_group')
                self.commonV2.save_json_to_new_tables(res_dict)

                # Saving to old tables
                self.set_templates_data[set_name] = parse_template(
                    RELATIVE_PATH, lower_headers_row_index=2)
                set_score = self.calculate_relative_position_sets(set_name)
            # elif set_name in ('MPA', 'New Products', 'Local MPA'):
            elif set_name in ('Local MPA'):
                set_score = self.calculate_assortment_sets(set_name)

            # Global Secondary Displays
            elif set_name in ('Secondary Displays', 'Secondary'):
                # Global function
                res_json = self.diageo_generator.diageo_global_secondary_display_secondary_function(
                )
                if res_json:
                    # Saving to new tables
                    self.commonV2.write_to_db_result(
                        fk=res_json['fk'],
                        numerator_id=1,
                        denominator_id=self.store_id,
                        result=res_json['result'])

                # Saving to old tables
                set_score = self.tools.calculate_number_of_scenes(
                    location_type='Secondary')
                if not set_score:
                    set_score = self.tools.calculate_number_of_scenes(
                        location_type='Secondary Shelf')
                self.save_level2_and_level3(set_name, set_name, set_score)
            elif set_name == 'POSM':
                set_score = self.calculate_posm_sets(set_name)
            elif set_name in ('Visible to Customer', 'Visible to Consumer %'):
                # Global function
                sku_list = filter(
                    None, self.scif[self.scif['product_type'] ==
                                    'SKU'].product_ean_code.tolist())
                res_dict = self.diageo_generator.diageo_global_visible_percentage(
                    sku_list)

                if res_dict:
                    # Saving to new tables
                    parent_res = res_dict[-1]
                    self.commonV2.save_json_to_new_tables(res_dict)

                    # Saving to old tables
                    # result = parent_res['result']
                    # self.save_level2_and_level3(set_name=set_name, kpi_name=set_name, score=result)

                # Saving to old tables
                filters = {self.tools.VISIBILITY_PRODUCTS_FIELD: 'Y'}
                set_score = self.tools.calculate_visible_percentage(
                    visible_filters=filters)
                self.save_level2_and_level3(set_name, set_name, set_score)
            else:
                continue

            if set_score == 0:
                pass
            elif set_score is False:
                continue

            set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name']
                                          == set_name]['kpi_set_fk'].values[0]
            self.write_to_db_result(set_fk, set_score, self.LEVEL1)

        # commiting to new tables
        self.commonV2.commit_results_data()

    def save_level2_and_level3(self, set_name, kpi_name, score):
        """
        Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
        """
        kpi_data = self.kpi_static_data[
            (self.kpi_static_data['kpi_set_name'].str.encode('utf-8') ==
             set_name.encode('utf-8'))
            & (self.kpi_static_data['kpi_name'].str.encode('utf-8') ==
               kpi_name.encode('utf-8'))]
        try:
            kpi_fk = kpi_data['kpi_fk'].values[0]
        except:
            Log.warning("kpi name or set name don't exist")
            return
        atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
        self.write_to_db_result(kpi_fk, score, self.LEVEL2)
        self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)

    def calculate_relative_position_sets(self, set_name):
        """
        This function calculates every relative-position-typed KPI from the relevant sets, and returns the set final score.
        """

        scores = []
        for i in xrange(len(self.set_templates_data[set_name])):
            params = self.set_templates_data[set_name].iloc[i]
            if self.store_channel == params.get(self.tools.CHANNEL,
                                                '').upper():
                scif_tested_param = 'brand_name' if params.get(self.tools.TESTED_TYPE, '') == self.tools.BRAND \
                    else 'product_ean_code'
                scif_anchor_param = 'brand_name' if params.get(self.tools.ANCHOR_TYPE, '') == self.tools.BRAND \
                    else 'product_ean_code'
                tested_filters = {
                    scif_tested_param: params.get(self.tools.TESTED_NEW)
                }
                anchor_filters = {
                    scif_anchor_param: params.get(self.tools.ANCHOR_NEW)
                }

                direction_data = {
                    'top':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.TOP_DISTANCE)),
                    'bottom':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.BOTTOM_DISTANCE)),
                    'left':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.LEFT_DISTANCE)),
                    'right':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.RIGHT_DISTANCE))
                }
                if params.get(self.tools.LOCATION_OLD, ''):
                    general_filters = {
                        'template_group': params.get(self.tools.LOCATION_OLD)
                    }
                else:
                    general_filters = {}

                result = self.tools.calculate_relative_position(
                    tested_filters, anchor_filters, direction_data,
                    **general_filters)
                score = 1 if result else 0
                scores.append(score)

                self.save_level2_and_level3(set_name,
                                            params.get(self.tools.KPI_NAME),
                                            score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def _get_direction_for_relative_position(self, value):
        """
        This function converts direction data from the template (as string) to a number.
        """
        if value == self.tools.UNLIMITED_DISTANCE:
            value = 1000
        elif not value or not str(value).isdigit():
            value = 0
        else:
            value = int(value)
        return value

    def calculate_posm_sets(self, set_name):
        """
        This function calculates every POSM-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.store_channel is None:
                break

            kpi_res = self.tools.calculate_posm(
                display_name=params.get(self.tools.DISPLAY_NAME))
            score = 1 if kpi_res > 0 else 0
            if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
                scores.append(score)

            if score == 1 or params.get(
                    self.store_type) == self.tools.RELEVANT_FOR_STORE:
                self.save_level2_and_level3(
                    set_name, params.get(self.tools.DISPLAY_NAME), score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def calculate_assortment_sets(self, set_name):
        """
        This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            target = str(params.get(self.store_type, ''))
            if target.isdigit() or target.capitalize() in (
                    self.tools.RELEVANT_FOR_STORE,
                    self.tools.OR_OTHER_PRODUCTS):
                products = str(
                    params.get(self.tools.PRODUCT_EAN_CODE,
                               params.get(self.tools.PRODUCT_EAN_CODE2,
                                          ''))).replace(',', ' ').split()
                target = 1 if not target.isdigit() else int(target)
                kpi_name = params.get(self.tools.GROUP_NAME,
                                      params.get(self.tools.PRODUCT_NAME))
                kpi_static_data = self.kpi_static_data[
                    (self.kpi_static_data['kpi_set_name'] == set_name)
                    & (self.kpi_static_data['kpi_name'] == kpi_name)]
                if len(products) > 1:
                    result = 0
                    for product in products:
                        product_score = self.tools.calculate_assortment(
                            product_ean_code=product)
                        result += product_score
                        try:
                            product_name = self.all_products[
                                self.all_products['product_ean_code'] ==
                                product]['product_name'].values[0]
                        except Exception as e:
                            Log.warning(
                                'Product {} is not defined in the DB'.format(
                                    product))
                            continue
                        try:
                            atomic_fk = \
                            kpi_static_data[kpi_static_data['atomic_kpi_name'] == product_name]['atomic_kpi_fk'].values[
                                0]
                        except Exception as e:
                            Log.warning(
                                'Product {} is not defined in the DB'.format(
                                    product_name))
                            continue
                        self.write_to_db_result(atomic_fk,
                                                product_score,
                                                level=self.LEVEL3)
                    score = 1 if result >= target else 0
                else:
                    result = self.tools.calculate_assortment(
                        product_ean_code=products)
                    atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
                    score = 1 if result >= target else 0
                    self.write_to_db_result(atomic_fk,
                                            score,
                                            level=self.LEVEL3)

                scores.append(score)
                kpi_fk = kpi_static_data['kpi_fk'].values[0]
                self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    # def calculate_assortment_sets(self, set_name): # the old version. I changed it to the function of KE for local MPA.
    #     """
    #     This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
    #     """
    #     scores = []
    #     for params in self.set_templates_data[set_name]:
    #         if params.get(self.store_type, '').capitalize() in (self.tools.RELEVANT_FOR_STORE,
    #                                                             self.tools.OR_OTHER_PRODUCTS):
    #             object_type = self.tools.ENTITY_TYPE_CONVERTER.get(params.get(self.tools.ENTITY_TYPE),
    #                                                                'product_ean_code')
    #             objects = [str(params.get(self.tools.PRODUCT_EAN_CODE, params.get(self.tools.PRODUCT_EAN_CODE2, '')))]
    #             if params.get(self.store_type) == self.tools.OR_OTHER_PRODUCTS:
    #                 additional_objects = str(params.get(self.tools.ADDITIONAL_SKUS)).split(',')
    #                 objects.extend(additional_objects)
    #             filters = {object_type: objects}
    #             result = self.tools.calculate_assortment(**filters)
    #             score = 1 if result > 0 else 0
    #             scores.append(score)
    #
    #             self.save_level2_and_level3(set_name, params.get(self.tools.PRODUCT_NAME), score)
    #
    #     if not scores:
    #         return False
    #     set_score = (sum(scores) / float(len(scores))) * 100
    #     return set_score

    def write_to_db_result(self, fk, score, level):
        """
        This function the result data frame of every KPI (atomic KPI/KPI/KPI set),
        and appends the insert SQL query into the queries' list, later to be written to the DB.
        """
        attributes = self.create_attributes_dict(fk, score, level)
        if level == self.LEVEL1:
            table = KPS_RESULT
        elif level == self.LEVEL2:
            table = KPK_RESULT
        elif level == self.LEVEL3:
            table = KPI_RESULT
        else:
            return
        query = insert(attributes, table)
        self.kpi_results_queries.append(query)

    def create_attributes_dict(self, fk, score, level):
        """
        This function creates a data frame with all attributes needed for saving in KPI results tables.

        """
        score = round(score, 2)
        if level == self.LEVEL1:
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['kpi_set_fk'] ==
                fk]['kpi_set_name'].values[0]
            score_type = '%' if kpi_set_name in self.tools.KPI_SETS_WITH_PERCENT_AS_SCORE else ''
            attributes = pd.DataFrame(
                [(kpi_set_name, self.session_uid, self.store_id,
                  self.visit_date.isoformat(), format(score,
                                                      '.2f'), score_type, fk)],
                columns=[
                    'kps_name', 'session_uid', 'store_fk', 'visit_date',
                    'score_1', 'score_2', 'kpi_set_fk'
                ])

        elif level == self.LEVEL2:
            kpi_name = self.kpi_static_data[self.kpi_static_data['kpi_fk'] ==
                                            fk]['kpi_name'].values[0].replace(
                                                "'", "\\'")
            attributes = pd.DataFrame(
                [(self.session_uid, self.store_id, self.visit_date.isoformat(),
                  fk, kpi_name, score)],
                columns=[
                    'session_uid', 'store_fk', 'visit_date', 'kpi_fk',
                    'kpk_name', 'score'
                ])
        elif level == self.LEVEL3:
            data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
                                        == fk]
            atomic_kpi_name = data['atomic_kpi_name'].values[0].replace(
                "'", "\\'")
            kpi_fk = data['kpi_fk'].values[0]
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['atomic_kpi_fk'] ==
                fk]['kpi_set_name'].values[0]
            attributes = pd.DataFrame([
                (atomic_kpi_name, self.session_uid,
                 kpi_set_name, self.store_id, self.visit_date.isoformat(),
                 datetime.utcnow().isoformat(), score, kpi_fk, fk, None, None)
            ],
                                      columns=[
                                          'display_text', 'session_uid',
                                          'kps_name', 'store_fk', 'visit_date',
                                          'calculation_time', 'score',
                                          'kpi_fk', 'atomic_kpi_fk',
                                          'threshold', 'result'
                                      ])
        else:
            attributes = pd.DataFrame()
        return attributes.to_dict()

    def activate_ootb_kpis(self):

        # FACINGS_SOS_MANUFACTURER_IN_WHOLE_STORE - level 1
        sos_store_fk = self.commonV2.get_kpi_fk_by_kpi_name('SOS OUT OF STORE')
        sos_store = ManufacturerFacingsSOSInWholeStore(
            data_provider=self.data_provider,
            kpi_definition_fk=sos_store_fk).calculate()
        # FACINGS_SOS_CATEGORY_IN_WHOLE_STORE - level 2
        sos_cat_out_of_store_fk = self.commonV2.get_kpi_fk_by_kpi_name(
            'SOS CATEGORY OUT OF STORE')
        sos_cat_out_of_store = self.calculate_sos_of_cat_of_out_of_store_new(
            sos_cat_out_of_store_fk)

        # FACINGS_SOS_SUB_CATEGORY_OUT_OF_CATEGORY - level 3
        sos_sub_cat_out_of_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name(
            'SOS SUB CATEGORY OUT OF CATEGORY')
        sos_sub_cat_out_of_cat = SubCategoryFacingsSOSPerCategory(
            data_provider=self.data_provider,
            kpi_definition_fk=sos_sub_cat_out_of_cat_fk).calculate()

        # FACINGS_SOS_MANUFACTURER_OUT_OF_SUB_CATEGORY - level 4
        sos_man_out_of_sub_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name(
            'SOS MANUFACTURER OUT OF SUB CATEGORY')
        sos_man_out_of_sub_cat = ManufacturerFacingsSOSPerSubCategoryInStore(
            data_provider=self.data_provider,
            kpi_definition_fk=sos_man_out_of_sub_cat_fk).calculate()

        # FACINGS_SOS_BRAND_OUT_OF_SUB_CATEGORY_IN_WHOLE_STORE - level 5
        sos_brand_out_of_sub_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name(
            'SOS BRAND OUT OF MANUFACTURER')
        sos_brand_out_of_sub_cat = self.calculate_sos_of_brand_out_of_manufacturer_in_sub_cat(
            sos_brand_out_of_sub_cat_fk)

        # Savings results in Hierarchy
        self.save_hierarchy(sos_store, sos_cat_out_of_store,
                            sos_sub_cat_out_of_cat, sos_man_out_of_sub_cat,
                            sos_brand_out_of_sub_cat)

    def calculate_sos_of_brand_out_of_manufacturer_in_sub_cat(self, kpi_fk):
        res_list = []
        res_dict = dict()
        # Get rid of Irrelevant and Empty types and keep only facings > 1
        filtered_scif = self.scif[
            ~self.scif['product_type'].isin(['Irrelevant', 'Empty'])
            & self.scif['facings_ign_stack'] > 0]

        # Filter by each Sub Category and Manufacturer
        sub_cat_fk_list = filtered_scif['sub_category_fk'].unique().tolist()
        for sub_cat in sub_cat_fk_list:
            filtered_scif_by_sub_cat = filtered_scif[
                filtered_scif['sub_category_fk'] == sub_cat]
            list_of_relevant_manufacturers = filtered_scif_by_sub_cat[
                'manufacturer_fk'].unique().tolist()
            for manu_fk in list_of_relevant_manufacturers:
                filtered_scif_by_sub_cat_and_manufacturer = filtered_scif_by_sub_cat[
                    filtered_scif_by_sub_cat['manufacturer_fk'] == manu_fk]
                denominator_result = filtered_scif_by_sub_cat_and_manufacturer[
                    'facings_ign_stack'].sum()

                # Calculate results per Brand
                list_of_relevant_brands = filtered_scif_by_sub_cat_and_manufacturer[
                    'brand_fk'].unique().tolist()
                for brand_fk in list_of_relevant_brands:
                    filtered_scif_by_brand = filtered_scif_by_sub_cat_and_manufacturer[
                        filtered_scif_by_sub_cat_and_manufacturer['brand_fk']
                        == brand_fk]
                    facings_brand_results = filtered_scif_by_brand[
                        'facings_ign_stack'].sum()
                    result_for_brand = facings_brand_results / denominator_result

                    # Preparing the results' dictionary
                    res_dict['kpi_definition_fk'] = kpi_fk
                    res_dict['numerator_id'] = brand_fk
                    res_dict['numerator_result'] = facings_brand_results
                    res_dict['denominator_id'] = int(sub_cat)
                    res_dict['denominator_result'] = denominator_result
                    res_dict['identifier_result'] = (int(brand_fk),
                                                     int(sub_cat),
                                                     int(manu_fk))
                    res_dict['identifier_parent'] = int(manu_fk), (
                        int(sub_cat))
                    res_dict['result'] = result_for_brand
                    res_dict['score'] = result_for_brand
                    res_list.append(res_dict.copy())
        return res_list

    def calculate_sos_of_cat_of_out_of_store_new(self, kpi_fk):
        res_list = []
        res_dict = dict()
        # Get rid of Irrelevant and Empty types and keep only facings ignore stacking > 1
        filtered_scif = self.scif[
            ~self.scif['product_type'].isin(['Irrelevant', 'Empty'])
            & self.scif['facings_ign_stack'] > 0]
        denominator_result = filtered_scif['facings_ign_stack'].sum()
        categories_fk_list = filtered_scif['category_fk'].unique().tolist()

        # Calculate result per category (using facings_ign_stack!)
        for category_fk in categories_fk_list:
            filtered_scif_by_category = filtered_scif[
                filtered_scif['category_fk'] == category_fk]
            facings_category_result = filtered_scif_by_category[
                'facings_ign_stack'].sum()
            result_for_category = facings_category_result / denominator_result

            # Preparing the results' dictionary
            res_dict['kpi_definition_fk'] = kpi_fk
            res_dict['numerator_id'] = category_fk
            res_dict['numerator_result'] = facings_category_result
            res_dict['denominator_id'] = self.store_id
            res_dict['denominator_result'] = denominator_result
            res_dict['result'] = result_for_category
            res_dict['score'] = result_for_category
            res_list.append(res_dict.copy())
        return res_list

    def save_hierarchy(self, level_1, level_2, level_3, level_4, level_5):
        for i in level_1:
            res = i.to_dict
            kpi_identifier = "level_1"
            self.commonV2.write_to_db_result(
                fk=res['kpi_definition_fk'],
                numerator_id=res['numerator_id'],
                denominator_id=res['denominator_id'],
                numerator_result=res['numerator_result'],
                denominator_result=res['denominator_result'],
                result=res['result'],
                score=res['result'],
                identifier_result=kpi_identifier,
                should_enter=False)

        for res in level_2:
            kpi_identifier = "level_2_" + str(int(res['numerator_id']))
            parent_identifier = "level_1"
            self.commonV2.write_to_db_result(
                fk=res['kpi_definition_fk'],
                numerator_id=res['numerator_id'],
                denominator_id=res['denominator_id'],
                numerator_result=res['numerator_result'],
                denominator_result=res['denominator_result'],
                result=res['result'],
                score=res['result'],
                identifier_result=kpi_identifier,
                identifier_parent=parent_identifier,
                should_enter=True)

        for i in level_3:
            res = i.to_dict
            kpi_identifier = str(int(res['numerator_id']))
            parent_identifier = "level_2_" + str(int(res['denominator_id']))
            self.commonV2.write_to_db_result(
                fk=res['kpi_definition_fk'],
                numerator_id=res['numerator_id'],
                denominator_id=res['denominator_id'],
                numerator_result=res['numerator_result'],
                denominator_result=res['denominator_result'],
                result=res['result'],
                score=res['result'],
                identifier_result=kpi_identifier,
                identifier_parent=parent_identifier,
                should_enter=True)

        for i in level_4:
            res = i.to_dict
            kpi_identifier = "level_4_" + str(
                (int(res['numerator_id']), int(res['denominator_id'])))
            parent_identifier = str(int(res['denominator_id']))
            self.commonV2.write_to_db_result(
                fk=res['kpi_definition_fk'],
                numerator_id=res['numerator_id'],
                denominator_id=res['denominator_id'],
                numerator_result=res['numerator_result'],
                denominator_result=res['denominator_result'],
                result=res['result'],
                score=res['result'],
                identifier_result=kpi_identifier,
                identifier_parent=parent_identifier,
                should_enter=True)

        for res in level_5:
            kpi_identifier = "level_5_" + str(res['identifier_result'])
            parent_identifier = "level_4_" + str(res['identifier_parent'])
            self.commonV2.write_to_db_result(
                fk=res['kpi_definition_fk'],
                numerator_id=res['numerator_id'],
                denominator_id=res['denominator_id'],
                numerator_result=res['numerator_result'],
                denominator_result=res['denominator_result'],
                result=res['result'],
                score=res['result'],
                identifier_result=kpi_identifier,
                identifier_parent=parent_identifier,
                should_enter=True)

    @log_runtime('Saving to DB')
    def commit_results_data(self):
        """
        This function writes all KPI results to the DB, and commits the changes.
        """
        self.rds_conn.disconnect_rds()
        self.rds_conn.connect_rds()
        cur = self.rds_conn.db.cursor()
        delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
            self.session_uid)
        for query in delete_queries:
            cur.execute(query)
        for query in self.kpi_results_queries:
            cur.execute(query)
        self.rds_conn.db.commit()

    def get_equipment_score_relevant_scenes(self):
        scenes = []
        if not self.diageo_generator.scif.empty:
            scenes = self.diageo_generator.scif[self.diageo_generator.scif['template_name'] == \
                                                'ON - DRAUGHT TAPS']['scene_fk'].unique().tolist()
        return scenes
Ejemplo n.º 6
0
class DIAGEOTWToolBox:
    SOS_SETS = 'SOS_SETS'
    LEVEL1 = 1
    LEVEL2 = 2
    LEVEL3 = 3

    ACTIVATION_STANDARD = 'Activation Standard'

    def __init__(self, data_provider, output):
        self.k_engine = BaseCalculationsScript(data_provider, output)
        self.data_provider = data_provider
        self.project_name = self.data_provider.project_name
        self.session_uid = self.data_provider.session_uid
        self.products = self.data_provider[Data.PRODUCTS]
        self.all_products = self.data_provider[Data.ALL_PRODUCTS]
        self.match_product_in_scene = self.data_provider[Data.MATCHES]
        self.visit_date = self.data_provider[Data.VISIT_DATE]
        self.session_info = self.data_provider[Data.SESSION_INFO]
        self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
        self.store_info = self.data_provider[Data.STORE_INFO]
        self.store_channel = self.store_info['store_type'].values[0]
        if self.store_channel:
            self.store_channel = self.store_channel.upper()
        self.store_type = self.store_info['additional_attribute_1'].values[0]
        self.scene_info = self.data_provider[Data.SCENES_INFO]
        self.store_id = self.data_provider[Data.STORE_FK]
        self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
        self.match_display_in_scene = self.get_match_display()
        self.set_templates_data = {}
        self.kpi_static_data = self.get_kpi_static_data()
        self.scores = {self.LEVEL1: {},
                       self.LEVEL2: {},
                       self.LEVEL3: {}}
        self.kpi_results_queries = []
        self.output = output
        self.common = Common(self.data_provider)
        self.commonV2 = CommonV2(self.data_provider)
        self.tools = DIAGEOToolBox(self.data_provider, output,
                                   match_display_in_scene=self.match_display_in_scene)  # replace the old one
        self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common)

    def get_kpi_static_data(self):
        """
        This function extracts the static KPI data and saves it into one global data frame.
        The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
        """
        query = DIAGEOQueries.get_all_kpi_data()
        kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
        return kpi_static_data

    def get_match_display(self):
        """
        This function extracts the display matches data and saves it into one global data frame.
        The data is taken from probedata.match_display_in_scene.
        """
        query = DIAGEOQueries.get_match_display(self.session_uid)
        match_display = pd.read_sql_query(query, self.rds_conn.db)
        return match_display

    def main_calculation(self, set_names):
        """
        This function calculates the KPI results.
        """

        # SOS Out Of The Box kpis
        self.activate_ootb_kpis()

        # sos by scene type
        self.diageo_generator.sos_by_scene_type(self.commonV2)

        # Global assortment kpis
        assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2()
        self.commonV2.save_json_to_new_tables(assortment_res_dict)

        # Global assortment kpis - v3 for NEW MOBILE REPORTS use
        assortment_res_dict_v3 = self.diageo_generator.diageo_global_assortment_function_v3()
        self.commonV2.save_json_to_new_tables(assortment_res_dict_v3)

        # Global menu calculation
        menus_res_dict = self.diageo_generator.diageo_global_new_share_of_menu_function()
        self.commonV2.save_json_to_new_tables(menus_res_dict)

        for set_name in set_names:
            # if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys():
            #     self.set_templates_data[set_name] = self.tools.download_template(set_name)

            # if set_name in ('Local MPA', 'MPA', 'New Products',):
            #     set_score = self.calculate_assortment_sets(set_name)
            # if set_name in ('Relative Position',):
            #     set_score = self.calculate_relative_position_sets(set_name)
            # elif set_name in ('Brand Blocking',):
            #     set_score = self.calculate_block_together_sets(set_name)
            if set_name in ('SOS',):
                set_score = self.calculate_sos_sets(set_name)
                if set_score:
                    fk = self.common.get_kpi_fk_by_kpi_name_new_tables(self.SOS_SETS)
                    self.commonV2.write_to_db_result(fk=fk, numerator_id=1, denominator_id=self.store_id,
                                                     result=set_score)
            # elif set_name == 'Visible to Customer':
            #     visible_filters = {self.tools.VISIBILITY_PRODUCTS_FIELD: 'Y'}
            #     filters = {"manufacturer_name": "Diageo"}
            #     set_score = self.tools.calculate_visible_percentage(visible_filters=visible_filters, **filters)
            #     self.save_level2_and_level3(set_name, set_name, set_score)
            # elif set_name == 'Secondary':
            #     set_score = self.tools.calculate_number_of_scenes(location_type='Secondary')
            #     self.save_level2_and_level3(set_name, set_name, set_score)
            # elif set_name == 'Survey Questions':
            #     set_score = self.calculate_survey_sets(set_name)
            # elif set_name in ('POSM',):
            #     set_score = self.calculate_posm_sets(set_name)
            else:
                continue

            if set_score == 0:
                pass
            elif set_score is False:
                continue

            set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name'] == set_name]['kpi_set_fk'].values[0]
            self.write_to_db_result(set_fk, set_score, self.LEVEL1)
        # commiting to new tables
        self.commonV2.commit_results_data()

    def save_level2_and_level3(self, set_name, kpi_name, score):
        """
        Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
        """
        kpi_data = self.kpi_static_data[(self.kpi_static_data['kpi_set_name'] == set_name) &
                                        (self.kpi_static_data['kpi_name'] == kpi_name)]
        kpi_fk = kpi_data['kpi_fk'].values[0]
        atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
        self.write_to_db_result(kpi_fk, score, self.LEVEL2)
        self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)

    def calculate_activation_standard(self):
        """
        This function calculates the Activation Standard KPI, and saves the result to the DB (for all 3 levels).
        """
        final_score = 0
        for params in self.tools.download_template(self.ACTIVATION_STANDARD):
            set_name = params.get(self.tools.ACTIVATION_SET_NAME)
            kpi_name = params.get(self.tools.ACTIVATION_KPI_NAME)
            target = float(params.get(self.tools.ACTIVATION_TARGET))
            target = target * 100 if target < 1 else target
            score_type = params.get(self.tools.ACTIVATION_SCORE)
            weight = float(params.get(self.tools.ACTIVATION_WEIGHT))
            if kpi_name:
                kpi_fk = self.kpi_static_data[(self.kpi_static_data['kpi_set_name'] == set_name) &
                                              (self.kpi_static_data['kpi_name'] == kpi_name)]['kpi_fk'].values[0]
                score = self.scores[self.LEVEL2].get(kpi_fk, 0)
            else:
                set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name'] == set_name]['kpi_set_fk'].values[0]
                score = self.scores[self.LEVEL1].get(set_fk, 0)
            if score >= target:
                score = 100
            else:
                if score_type == 'PROPORTIONAL':
                    score = (score / float(target)) * 100
                else:
                    score = 0
            final_score += score * weight
            self.save_level2_and_level3(self.ACTIVATION_STANDARD, set_name, score)
        set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name'] ==
                                      self.ACTIVATION_STANDARD]['kpi_set_fk'].values[0]
        self.write_to_db_result(set_fk, final_score, self.LEVEL1)

    def calculate_sos_sets(self, set_name):
        """
        This function calculates every SOS-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
                result = self.tools.calculate_share_of_shelf(manufacturer=self.tools.DIAGEO,
                                                             include_empty=self.tools.EXCLUDE_EMPTY)
                score = 1 if result >= params.get(self.tools.TARGET) else 0
                scores.append(score)

                self.save_level2_and_level3(set_name, params.get(self.tools.KPI_NAME), score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def calculate_survey_sets(self, set_name):
        """
        This function calculates every Survey-Answer-typed KPI and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            survey_text = params.get(self.tools.SURVEY_QUESTION)
            answer_field = 'number_value' if params.get(self.tools.SURVEY_ANSWER_TYPE) == 'Numeric' else None
            answer_target = params.get(self.tools.SURVEY_ANSWER)
            survey_answer = self.tools.get_survey_answer(survey_text, answer_field)
            if answer_field == 'number_value':
                if str(answer_target).isdigit():
                    answer_target = '=={}'.format(answer_target)
                if not str(survey_answer).isdigit():
                    result = False
                else:
                    result = eval('{}{}'.format(survey_answer, answer_target))
            else:
                result = True if survey_answer in answer_target.split(';') else False
            score = 1 if result else 0
            scores.append(score)

            self.save_level2_and_level3(set_name, params.get(self.tools.KPI_NAME), score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def validate_survey(self, params):
        """
        This function checks whether the Survey-typed KPI should be run, based on the Execution Condition Field.
        """
        survey_validated = False
        condition = params.get(self.tools.SURVEY_CONDITION)
        if not condition:
            survey_validated = True
        else:
            question, answers = condition.split('=')
            survey_answer = self.tools.get_survey_answer(question)
            if survey_answer in answers.split(';'):
                survey_validated = True
        return survey_validated

    def calculate_block_together_sets(self, set_name):
        """
        This function calculates every block-together-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.store_channel == params.get(self.tools.CHANNEL, '').upper():
                filters = {'template_name': params.get(self.tools.LOCATION)}
                if params.get(self.tools.SUB_BRAND_NAME):
                    filters['sub_brand_name'] = params.get(self.tools.SUB_BRAND_NAME)
                else:
                    filters['brand_name'] = params.get(self.tools.BRAND_NAME)
                result = self.tools.calculate_block_together(**filters)
                score = 1 if result else 0
                scores.append(score)

                self.save_level2_and_level3(set_name, params.get(self.tools.KPI_NAME), score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def calculate_relative_position_sets(self, set_name):
        """
        This function calculates every relative-position-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.store_channel == params.get(self.tools.CHANNEL, '').upper():
                tested_filters = {'product_ean_code': params.get(self.tools.TESTED)}
                anchor_filters = {'product_ean_code': params.get(self.tools.ANCHOR)}
                direction_data = {'top': self._get_direction_for_relative_position(params.get(self.tools.TOP_DISTANCE)),
                                  'bottom': self._get_direction_for_relative_position(
                                      params.get(self.tools.BOTTOM_DISTANCE)),
                                  'left': self._get_direction_for_relative_position(
                                      params.get(self.tools.LEFT_DISTANCE)),
                                  'right': self._get_direction_for_relative_position(
                                      params.get(self.tools.RIGHT_DISTANCE))}
                general_filters = {'template_name': params.get(self.tools.LOCATION)}
                result = self.tools.calculate_relative_position(tested_filters, anchor_filters, direction_data,
                                                                **general_filters)
                score = 1 if result else 0
                scores.append(score)

                self.save_level2_and_level3(set_name, params.get(self.tools.KPI_NAME), score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def _get_direction_for_relative_position(self, value):
        """
        This function converts direction data from the template (as string) to a number.
        """
        if value == self.tools.UNLIMITED_DISTANCE:
            value = 1000
        elif not value or not str(value).isdigit():
            value = 0
        else:
            value = int(value)
        return value

    def calculate_posm_sets(self, set_name):
        """
        This function calculates every POSM-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.store_channel is None:
                break

            kpi_res = self.tools.calculate_posm(display_name=params.get(self.tools.DISPLAY_NAME))
            score = 1 if kpi_res > 0 else 0
            if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
                scores.append(score)

            if score == 1 or params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
                self.save_level2_and_level3(set_name, params.get(self.tools.DISPLAY_NAME), score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def calculate_assortment_sets(self, set_name):
        """
        This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            target = str(params.get(self.store_type, ''))
            if target.isdigit() or target.capitalize() in (self.tools.RELEVANT_FOR_STORE, self.tools.OR_OTHER_PRODUCTS):
                products = str(params.get(self.tools.PRODUCT_EAN_CODE,
                                          params.get(self.tools.PRODUCT_EAN_CODE2, ''))).replace(',', ' ').split()
                target = 1 if not target.isdigit() else int(target)
                kpi_name = params.get(self.tools.GROUP_NAME, params.get(self.tools.PRODUCT_NAME))
                kpi_static_data = self.kpi_static_data[(self.kpi_static_data['kpi_set_name'] == set_name) &
                                                       (self.kpi_static_data['kpi_name'] == kpi_name)]
                if len(products) > 1:
                    result = 0
                    for product in products:
                        product_score = self.tools.calculate_assortment(product_ean_code=product)
                        result += product_score
                        product_name = \
                            self.all_products[self.all_products['product_ean_code'] == product]['product_name'].values[
                                0]
                        try:
                            atomic_fk = \
                                kpi_static_data[kpi_static_data['atomic_kpi_name'] == product_name][
                                    'atomic_kpi_fk'].values[
                                    0]
                        except Exception as e:
                            Log.info('Product {} is not defined in the DB'.format(product_name))
                            continue
                        self.write_to_db_result(atomic_fk, product_score, level=self.LEVEL3)
                    score = 1 if result >= target else 0
                else:
                    result = self.tools.calculate_assortment(product_ean_code=products)
                    atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
                    score = 1 if result >= target else 0
                    self.write_to_db_result(atomic_fk, score, level=self.LEVEL3)

                scores.append(score)
                kpi_fk = kpi_static_data['kpi_fk'].values[0]
                self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def write_to_db_result(self, fk, score, level):
        """
        This function the result data frame of every KPI (atomic KPI/KPI/KPI set),
        and appends the insert SQL query into the queries' list, later to be written to the DB.
        """
        attributes = self.create_attributes_dict(fk, score, level)
        if level == self.LEVEL1:
            table = KPS_RESULT
        elif level == self.LEVEL2:
            table = KPK_RESULT
        elif level == self.LEVEL3:
            table = KPI_RESULT
        else:
            return
        self.scores[level][fk] = score
        query = insert(attributes, table)
        self.kpi_results_queries.append(query)

    def activate_ootb_kpis(self):
        # FACINGS_SOS_MANUFACTURER_IN_WHOLE_STORE - level 1
        sos_store_fk = self.commonV2.get_kpi_fk_by_kpi_name('SOS OUT OF STORE')
        sos_store = ManufacturerFacingsSOSInWholeStore(data_provider=self.data_provider,
                                                       kpi_definition_fk=sos_store_fk).calculate()
        # FACINGS_SOS_CATEGORY_IN_WHOLE_STORE - level 2
        sos_cat_out_of_store_fk = self.commonV2.get_kpi_fk_by_kpi_name('SOS CATEGORY OUT OF STORE')
        sos_cat_out_of_store = self.calculate_sos_of_cat_of_out_of_store_new(sos_cat_out_of_store_fk)

        # FACINGS_SOS_SUB_CATEGORY_OUT_OF_CATEGORY - level 3
        sos_sub_cat_out_of_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name('SOS SUB CATEGORY OUT OF CATEGORY')
        sos_sub_cat_out_of_cat = SubCategoryFacingsSOSPerCategory(data_provider=self.data_provider,
                                                                  kpi_definition_fk=sos_sub_cat_out_of_cat_fk).calculate()

        # FACINGS_SOS_MANUFACTURER_OUT_OF_SUB_CATEGORY - level 4
        sos_man_out_of_sub_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name('SOS MANUFACTURER OUT OF SUB CATEGORY')
        sos_man_out_of_sub_cat = ManufacturerFacingsSOSPerSubCategoryInStore(
            data_provider=self.data_provider, kpi_definition_fk=sos_man_out_of_sub_cat_fk).calculate()

        # FACINGS_SOS_BRAND_OUT_OF_SUB_CATEGORY_IN_WHOLE_STORE - level 5
        sos_brand_out_of_sub_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name('SOS BRAND OUT OF MANUFACTURER')
        sos_brand_out_of_sub_cat = self.calculate_sos_of_brand_out_of_manufacturer_in_sub_cat(
            sos_brand_out_of_sub_cat_fk)

        # Savings results in Hierarchy
        self.save_hierarchy(sos_store, sos_cat_out_of_store, sos_sub_cat_out_of_cat, sos_man_out_of_sub_cat,
                            sos_brand_out_of_sub_cat)

    def calculate_sos_of_brand_out_of_manufacturer_in_sub_cat(self, kpi_fk):
        pass
        res_list = []
        res_dict = dict()
        # Get rid of Irrelevant and Empty types and keep only facings > 1
        filtered_scif = self.scif[
            ~self.scif['product_type'].isin(['Irrelevant', 'Empty']) & self.scif['facings_ign_stack'] > 0]

        # Filter by each Sub Category and Manufacturer
        sub_cat_fk_list = filtered_scif['sub_category_fk'].unique().tolist()
        for sub_cat in sub_cat_fk_list:
            filtered_scif_by_sub_cat = filtered_scif[filtered_scif['sub_category_fk'] == sub_cat]
            list_of_relevant_manufacturers = filtered_scif_by_sub_cat['manufacturer_fk'].unique().tolist()
            for manu_fk in list_of_relevant_manufacturers:
                filtered_scif_by_sub_cat_and_manufacturer = filtered_scif_by_sub_cat[
                    filtered_scif_by_sub_cat['manufacturer_fk'] == manu_fk]
                denominator_result = filtered_scif_by_sub_cat_and_manufacturer['facings_ign_stack'].sum()

                # Calculate results per Brand
                list_of_relevant_brands = filtered_scif_by_sub_cat_and_manufacturer['brand_fk'].unique().tolist()
                for brand_fk in list_of_relevant_brands:
                    filtered_scif_by_brand = filtered_scif_by_sub_cat_and_manufacturer[
                        filtered_scif_by_sub_cat_and_manufacturer['brand_fk'] == brand_fk]
                    facings_brand_results = filtered_scif_by_brand['facings_ign_stack'].sum()
                    result_for_brand = facings_brand_results / denominator_result

                    # Preparing the results' dictionary
                    res_dict['kpi_definition_fk'] = kpi_fk
                    res_dict['numerator_id'] = brand_fk
                    res_dict['numerator_result'] = facings_brand_results
                    res_dict['denominator_id'] = int(sub_cat)
                    res_dict['denominator_result'] = denominator_result
                    res_dict['identifier_result'] = (int(brand_fk), int(sub_cat), int(manu_fk))
                    res_dict['identifier_parent'] = int(manu_fk), (int(sub_cat))
                    res_dict['result'] = result_for_brand
                    res_dict['score'] = result_for_brand
                    res_list.append(res_dict.copy())
        return res_list

    def calculate_sos_of_cat_of_out_of_store_new(self, kpi_fk):
        res_list = []
        res_dict = dict()
        # Get rid of Irrelevant and Empty types and keep only facings ignore stacking > 1
        filtered_scif = self.scif[
            ~self.scif['product_type'].isin(['Irrelevant', 'Empty']) & self.scif['facings_ign_stack'] > 0]
        denominator_result = filtered_scif['facings_ign_stack'].sum()
        categories_fk_list = filtered_scif['category_fk'].unique().tolist()

        # Calculate result per category (using facings_ign_stack!)
        for category_fk in categories_fk_list:
            filtered_scif_by_category = filtered_scif[filtered_scif['category_fk'] == category_fk]
            facings_category_result = filtered_scif_by_category['facings_ign_stack'].sum()
            result_for_category = facings_category_result / denominator_result

            # Preparing the results' dictionary
            res_dict['kpi_definition_fk'] = kpi_fk
            res_dict['numerator_id'] = category_fk
            res_dict['numerator_result'] = facings_category_result
            res_dict['denominator_id'] = self.store_id
            res_dict['denominator_result'] = denominator_result
            res_dict['result'] = result_for_category
            res_dict['score'] = result_for_category
            res_list.append(res_dict.copy())
        return res_list

    def save_hierarchy(self, level_1, level_2, level_3, level_4, level_5):
        for i in level_1:
            res = i.to_dict
            kpi_identifier = "level_1"
            self.commonV2.write_to_db_result(fk=res['kpi_definition_fk'], numerator_id=res['numerator_id'],
                                             denominator_id=res['denominator_id'],
                                             numerator_result=res['numerator_result'],
                                             denominator_result=res['denominator_result'], result=res['result'],
                                             score=res['result'],
                                             identifier_result=kpi_identifier, should_enter=False)

        for res in level_2:
            kpi_identifier = "level_2_" + str(int(res['numerator_id']))
            parent_identifier = "level_1"
            self.commonV2.write_to_db_result(fk=res['kpi_definition_fk'], numerator_id=res['numerator_id'],
                                             denominator_id=res['denominator_id'],
                                             numerator_result=res['numerator_result'],
                                             denominator_result=res['denominator_result'], result=res['result'],
                                             score=res['result'],
                                             identifier_result=kpi_identifier,
                                             identifier_parent=parent_identifier, should_enter=True)

        for i in level_3:
            res = i.to_dict
            kpi_identifier = str(int(res['numerator_id']))
            parent_identifier = "level_2_" + str(int(res['denominator_id']))
            self.commonV2.write_to_db_result(fk=res['kpi_definition_fk'], numerator_id=res['numerator_id'],
                                             denominator_id=res['denominator_id'],
                                             numerator_result=res['numerator_result'],
                                             denominator_result=res['denominator_result'], result=res['result'],
                                             score=res['result'],
                                             identifier_result=kpi_identifier,
                                             identifier_parent=parent_identifier, should_enter=True)

        for i in level_4:
            res = i.to_dict
            kpi_identifier = "level_4_" + str((int(res['numerator_id']), int(res['denominator_id'])))
            parent_identifier = str(int(res['denominator_id']))
            self.commonV2.write_to_db_result(fk=res['kpi_definition_fk'], numerator_id=res['numerator_id'],
                                             denominator_id=res['denominator_id'],
                                             numerator_result=res['numerator_result'],
                                             denominator_result=res['denominator_result'], result=res['result'],
                                             score=res['result'],
                                             identifier_result=kpi_identifier,
                                             identifier_parent=parent_identifier, should_enter=True)

        for res in level_5:
            kpi_identifier = "level_5_" + str(res['identifier_result'])
            parent_identifier = "level_4_" + str(res['identifier_parent'])
            self.commonV2.write_to_db_result(fk=res['kpi_definition_fk'], numerator_id=res['numerator_id'],
                                             denominator_id=res['denominator_id'],
                                             numerator_result=res['numerator_result'],
                                             denominator_result=res['denominator_result'], result=res['result'],
                                             score=res['result'],
                                             identifier_result=kpi_identifier, identifier_parent=parent_identifier,
                                             should_enter=True)

    def create_attributes_dict(self, fk, score, level):
        """
        This function creates a data frame with all attributes needed for saving in KPI results tables.

        """
        score = round(score, 2)
        if level == self.LEVEL1:
            kpi_set_name = self.kpi_static_data[self.kpi_static_data['kpi_set_fk'] == fk]['kpi_set_name'].values[0]
            score_type = '%' if kpi_set_name in self.tools.KPI_SETS_WITH_PERCENT_AS_SCORE else ''
            attributes = pd.DataFrame([(kpi_set_name, self.session_uid, self.store_id, self.visit_date.isoformat(),
                                        format(score, '.2f'), score_type, fk)],
                                      columns=['kps_name', 'session_uid', 'store_fk', 'visit_date', 'score_1',
                                               'score_2', 'kpi_set_fk'])

        elif level == self.LEVEL2:
            kpi_name = self.kpi_static_data[self.kpi_static_data['kpi_fk'] == fk]['kpi_name'].values[0].replace("'",
                                                                                                                "\\'")
            attributes = pd.DataFrame([(self.session_uid, self.store_id, self.visit_date.isoformat(),
                                        fk, kpi_name, score)],
                                      columns=['session_uid', 'store_fk', 'visit_date', 'kpi_fk', 'kpk_name', 'score'])
        elif level == self.LEVEL3:
            data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk'] == fk]
            atomic_kpi_name = data['atomic_kpi_name'].values[0].replace("'", "\\'")
            kpi_fk = data['kpi_fk'].values[0]
            kpi_set_name = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk'] == fk]['kpi_set_name'].values[0]
            attributes = pd.DataFrame([(atomic_kpi_name, self.session_uid, kpi_set_name, self.store_id,
                                        self.visit_date.isoformat(), datetime.utcnow().isoformat(),
                                        score, kpi_fk, fk, None, None)],
                                      columns=['display_text', 'session_uid', 'kps_name', 'store_fk', 'visit_date',
                                               'calculation_time', 'score', 'kpi_fk', 'atomic_kpi_fk', 'threshold',
                                               'result'])
        else:
            attributes = pd.DataFrame()
        return attributes.to_dict()

    @log_runtime('Saving to DB')
    def commit_results_data(self):
        """
        This function writes all KPI results to the DB, and commits the changes.
        """
        cur = self.rds_conn.db.cursor()
        delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(self.session_uid)
        for query in delete_queries:
            cur.execute(query)
        for query in self.kpi_results_queries:
            cur.execute(query)
        self.rds_conn.db.commit()
Ejemplo n.º 7
0
class DIAGEOKE_SANDToolBox:
    LEVEL1 = 1
    LEVEL2 = 2
    LEVEL3 = 3

    def __init__(self, data_provider, output):
        self.k_engine = BaseCalculationsScript(data_provider, output)
        self.data_provider = data_provider
        self.project_name = self.data_provider.project_name
        self.session_uid = self.data_provider.session_uid
        self.products = self.data_provider[Data.PRODUCTS]
        self.all_products = self.data_provider[Data.ALL_PRODUCTS]
        self.match_product_in_scene = self.data_provider[Data.MATCHES]
        self.visit_date = self.data_provider[Data.VISIT_DATE]
        self.session_info = self.data_provider[Data.SESSION_INFO]
        self.rds_conn = PSProjectConnector(self.project_name,
                                           DbUsers.CalculationEng)
        self.store_info = self.data_provider[Data.STORE_INFO]
        self.store_channel = self.store_info['store_type'].values[0]
        if self.store_channel:
            self.store_channel = self.store_channel.upper()
        self.store_type = self.store_info['additional_attribute_1'].values[0]
        self.scene_info = self.data_provider[Data.SCENES_INFO]
        self.store_id = self.data_provider[Data.STORE_FK]
        self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
        self.match_display_in_scene = self.get_match_display()
        self.set_templates_data = {}
        self.kpi_static_data = self.get_kpi_static_data()
        self.kpi_results_queries = []

        self.output = output
        self.common = Common(self.data_provider)
        self.commonV2 = CommonV2(self.data_provider)
        self.tools = DIAGEOToolBox(
            self.data_provider,
            output,
            match_display_in_scene=self.match_display_in_scene)
        self.diageo_generator = DIAGEOGenerator(self.data_provider,
                                                self.output, self.common)

    def get_kpi_static_data(self):
        """
        This function extracts the static KPI data and saves it into one global data frame.
        The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
        """
        query = DIAGEOQueries.get_all_kpi_data()
        kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
        return kpi_static_data

    def get_match_display(self):
        """
        This function extracts the display matches data and saves it into one global data frame.
        The data is taken from probedata.match_display_in_scene.
        """
        query = DIAGEOQueries.get_match_display(self.session_uid)
        match_display = pd.read_sql_query(query, self.rds_conn.db)
        return match_display

    def main_calculation(self, set_names):
        """
        This function calculates the KPI results.
        """
        # Global assortment kpis
        assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2(
        )
        self.commonV2.save_json_to_new_tables(assortment_res_dict)

        for set_name in set_names:
            set_score = 0

            # Global Visible to Customer / Visible to Consumer
            if set_name in ('Visible to Customer', 'Visible to Consumer %'):
                # Global function
                sku_list = filter(
                    None, self.scif[self.scif['product_type'] ==
                                    'SKU'].product_ean_code.tolist())
                res_dict = self.diageo_generator.diageo_global_visible_percentage(
                    sku_list)

                if res_dict:
                    # Saving to new tables
                    parent_res = res_dict[-1]
                    self.commonV2.save_json_to_new_tables(res_dict)

                    # Saving to old tables
                    set_score = result = parent_res['result']
                    self.save_level2_and_level3(set_name=set_name,
                                                kpi_name=set_name,
                                                score=result)

            if set_score == 0:
                pass
            elif set_score is False:
                continue

            set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name']
                                          == set_name]['kpi_set_fk'].values[0]
            self.write_to_db_result(set_fk, set_score, self.LEVEL1)

        # committing to new tables
        self.commonV2.commit_results_data()
        return

    def save_level2_and_level3(self, set_name, kpi_name, score):
        """
        Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
        """
        kpi_data = self.kpi_static_data[
            (self.kpi_static_data['kpi_set_name'] == set_name)
            & (self.kpi_static_data['kpi_name'] == kpi_name)]

        kpi_fk = kpi_data['kpi_fk'].values[0]
        atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
        self.write_to_db_result(kpi_fk, score, self.LEVEL2)
        self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)

    def calculate_sos_set(self):
        # todo: This is a deprecated function
        """
        This function calculates SOS for the Manufacturer (Level1), Category (Level2) and Brand (Level3) levels.
        """
        # diageo_data = self.scif[self.scif['manufacturer_name'] == DIAGEO]
        categories = self.all_products[self.all_products['manufacturer_name']
                                       ==
                                       DIAGEO]['category'].unique().tolist()
        for category in categories:
            kpi_data = self.kpi_static_data[
                (self.kpi_static_data['kpi_set_name'] == 'Local SOS')
                & (self.kpi_static_data['kpi_name'] == category)]
            if not kpi_data.empty:
                kpi_fk = kpi_data['kpi_fk'].values[0]
                kpi_result = self.tools.calculate_share_of_shelf(
                    sos_filters={'category': category},
                    include_empty=self.tools.INCLUDE_EMPTY,
                    category=(GENERAL, self.tools.EXCLUDE_FILTER))
                self.write_to_db_result(kpi_fk,
                                        round(kpi_result * 100, 2),
                                        level=self.LEVEL2)
                category_data = self.scif[self.scif['category'] == category]
                for brand in category_data['brand_name'].unique():
                    atomic_data = kpi_data[kpi_data['atomic_kpi_name'] ==
                                           brand]
                    if not atomic_data.empty:
                        atomic_fk = atomic_data['atomic_kpi_fk'].values[0]
                        numerator = self.tools.calculate_availability(
                            brand_name=brand, category=category)
                        denominator = self.tools.calculate_availability(
                            category=category)
                        if denominator:
                            atomic_result = numerator / float(denominator)
                        else:
                            atomic_result = 0
                        self.write_to_db_result(atomic_fk,
                                                round(atomic_result * 100, 2),
                                                level=self.LEVEL3,
                                                result='{}/{}'.format(
                                                    int(numerator),
                                                    int(denominator)))
        set_result = self.tools.calculate_share_of_shelf(
            sos_filters={'manufacturer_name': DIAGEO},
            include_empty=self.tools.INCLUDE_EMPTY,
            category=(GENERAL, self.tools.EXCLUDE_FILTER))
        set_result = round(set_result * 100, 2)
        return set_result

    def calculate_posm_sets(self, set_name):
        """
        This function calculates every POSM-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.store_info['store_type'] is None:
                break

            kpi_res = self.tools.calculate_posm(
                display_name=params.get(self.tools.DISPLAY_NAME))
            score = 1 if kpi_res > 0 else 0
            if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
                scores.append(score)

            if score == 1 or params.get(
                    self.store_type) == self.tools.RELEVANT_FOR_STORE:
                self.save_level2_and_level3(
                    set_name, params.get(self.tools.DISPLAY_NAME), score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def calculate_assortment_sets(self, set_name):
        """
        This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            target = str(params.get(self.store_type, ''))
            if target.isdigit() or target.capitalize() in (
                    self.tools.RELEVANT_FOR_STORE,
                    self.tools.OR_OTHER_PRODUCTS):
                products = str(
                    params.get(self.tools.PRODUCT_EAN_CODE,
                               params.get(self.tools.PRODUCT_EAN_CODE2,
                                          ''))).replace(',', ' ').split()
                target = 1 if not target.isdigit() else int(target)
                kpi_name = params.get(self.tools.GROUP_NAME,
                                      params.get(self.tools.PRODUCT_NAME))
                kpi_static_data = self.kpi_static_data[
                    (self.kpi_static_data['kpi_set_name'] == set_name)
                    & (self.kpi_static_data['kpi_name'] == kpi_name)]
                if len(products) > 1:
                    result = 0
                    for product in products:
                        product_score = self.tools.calculate_assortment(
                            product_ean_code=product)
                        result += product_score
                        product_name = self.all_products[
                            self.all_products['product_ean_code'] ==
                            product]['product_name'].values[0]
                        try:
                            atomic_fk = kpi_static_data[
                                kpi_static_data['atomic_kpi_name'] ==
                                product_name]['atomic_kpi_fk'].values[0]
                        except Exception as e:
                            Log.info(
                                'Product {} is not defined in the DB'.format(
                                    product_name))
                            continue
                        self.write_to_db_result(atomic_fk,
                                                product_score,
                                                level=self.LEVEL3)
                    score = 1 if result >= target else 0
                else:
                    result = self.tools.calculate_assortment(
                        product_ean_code=products)
                    atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
                    score = 1 if result >= target else 0
                    self.write_to_db_result(atomic_fk,
                                            score,
                                            level=self.LEVEL3)

                scores.append(score)
                kpi_fk = kpi_static_data['kpi_fk'].values[0]
                self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    # def calculate_sos_sets(self, set_name):
    #     """
    #     This function calculates every SOS-typed KPI from the relevant sets, and returns the set final score.
    #     """
    #     scores = []
    #     for params in self.set_templates_data[set_name]:
    #         if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
    #             result = self.tools.calculate_share_of_shelf(manufacturer=self.tools.DIAGEO,
    #                                                          include_empty=self.tools.EXCLUDE_EMPTY)
    #             score = 1 if result >= params.get(self.tools.TARGET) else 0
    #             scores.append(score)
    #
    #             self.save_level2_and_level3(set_name, params.get(self.tools.KPI_NAME), score)
    #
    #     set_score = (sum(scores) / float(len(scores))) * 100
    #     return set_score

    def write_to_db_result(self, fk, score, level, result=None):
        """
        This function the result data frame of every KPI (atomic KPI/KPI/KPI set),
        and appends the insert SQL query into the queries' list, later to be written to the DB.
        """
        attributes = self.create_attributes_dict(fk,
                                                 score,
                                                 level,
                                                 result=result)
        if level == self.LEVEL1:
            table = KPS_RESULT
        elif level == self.LEVEL2:
            table = KPK_RESULT
        elif level == self.LEVEL3:
            table = KPI_RESULT
        else:
            return
        query = insert(attributes, table)
        self.kpi_results_queries.append(query)

    def create_attributes_dict(self, fk, score, level, result=None):
        """
        This function creates a data frame with all attributes needed for saving in KPI results tables.

        """
        score = round(score, 2)
        if level == self.LEVEL1:
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['kpi_set_fk'] ==
                fk]['kpi_set_name'].values[0]
            score_type = '%' if kpi_set_name in self.tools.KPI_SETS_WITH_PERCENT_AS_SCORE else ''
            attributes = pd.DataFrame(
                [(kpi_set_name, self.session_uid, self.store_id,
                  self.visit_date.isoformat(), format(score,
                                                      '.2f'), score_type, fk)],
                columns=[
                    'kps_name', 'session_uid', 'store_fk', 'visit_date',
                    'score_1', 'score_2', 'kpi_set_fk'
                ])

        elif level == self.LEVEL2:
            kpi_name = self.kpi_static_data[self.kpi_static_data['kpi_fk'] ==
                                            fk]['kpi_name'].values[0].replace(
                                                "'", "\\'")
            attributes = pd.DataFrame(
                [(self.session_uid, self.store_id, self.visit_date.isoformat(),
                  fk, kpi_name, score)],
                columns=[
                    'session_uid', 'store_fk', 'visit_date', 'kpi_fk',
                    'kpk_name', 'score'
                ])
        elif level == self.LEVEL3:
            data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
                                        == fk]
            atomic_kpi_name = data['atomic_kpi_name'].values[0].replace(
                "'", "\\'")
            kpi_fk = data['kpi_fk'].values[0]
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['atomic_kpi_fk'] ==
                fk]['kpi_set_name'].values[0]
            attributes = pd.DataFrame(
                [(atomic_kpi_name, self.session_uid,
                  kpi_set_name, self.store_id, self.visit_date.isoformat(),
                  datetime.utcnow().isoformat(), score, kpi_fk, fk, result)],
                columns=[
                    'display_text', 'session_uid', 'kps_name', 'store_fk',
                    'visit_date', 'calculation_time', 'score', 'kpi_fk',
                    'atomic_kpi_fk', 'result'
                ])
        else:
            attributes = pd.DataFrame()
        return attributes.to_dict()

    @log_runtime('Saving to DB')
    def commit_results_data(self):
        """
        This function writes all KPI results to the DB, and commits the changes.
        """
        cur = self.rds_conn.db.cursor()
        delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
            self.session_uid)
        for query in delete_queries:
            cur.execute(query)
        for query in self.kpi_results_queries:
            cur.execute(query)
        self.rds_conn.db.commit()
Ejemplo n.º 8
0
class DIAGEOIN_SANDToolBox:
    LEVEL1 = 1
    LEVEL2 = 2
    LEVEL3 = 3

    def __init__(self, data_provider, output):
        self.k_engine = BaseCalculationsScript(data_provider, output)
        self.data_provider = data_provider
        self.project_name = self.data_provider.project_name
        self.session_uid = self.data_provider.session_uid
        self.rds_conn = PSProjectConnector(self.project_name,
                                           DbUsers.CalculationEng)
        self.match_display_in_scene = self.get_match_display()
        self.output = output
        self.store_info = self.data_provider[Data.STORE_INFO]
        self.store_id = self.data_provider[Data.STORE_FK]
        self.visit_date = self.data_provider[Data.VISIT_DATE]
        self.all_products = self.data_provider[Data.ALL_PRODUCTS]
        self.match_product_in_scene = self.data_provider[Data.MATCHES]
        self.set_templates_data = {}
        self.kpi_results_queries = []
        self.store_channel = self.store_info['store_type'].values[0]
        if self.store_channel:
            self.store_channel = self.store_channel.upper()
        self.segment = self.get_business_unit_name()
        self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
        self.store_type = self.store_info['additional_attribute_1'].values[0]
        self.kpi_static_data = self.get_kpi_static_data()
        self.common = Common(self.data_provider)
        self.commonV2 = CommonV2(self.data_provider)
        self.tools = DIAGEOToolBox(
            self.data_provider,
            output,
            match_display_in_scene=self.match_display_in_scene)
        self.diageo_generator = DIAGEOGenerator(self.data_provider,
                                                self.output, self.common)

    def get_match_display(self):
        """
        This function extracts the display matches data and saves it into one global data frame.
        The data is taken from probedata.match_display_in_scene.
        """
        query = DIAGEOQueries.get_match_display(self.session_uid)
        match_display = pd.read_sql_query(query, self.rds_conn.db)
        return match_display

    def get_business_unit_name(self):
        """
        This function extracts the static KPI data and saves it into one global data frame.
        The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
        """
        query = DIAGEOQueries.get_business_unit_name(self.store_id)
        business_unit_name = pd.read_sql_query(query, self.rds_conn.db)
        if business_unit_name['business_unit_name'].empty:
            return ""
        else:
            return business_unit_name['business_unit_name'].values[0]

    def get_kpi_static_data(self):
        """
        This function extracts the static KPI data and saves it into one global data frame.
        The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
        """
        query = DIAGEOQueries.get_all_kpi_data()
        kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
        return kpi_static_data

    def main_calculation(self, set_names):
        """
        This function calculates the KPI results.
        """
        # # SOS Out Of The Box kpis
        self.activate_ootb_kpis()

        # Global assortment kpis
        assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v3(
        )
        self.commonV2.save_json_to_new_tables(assortment_res_dict)

        for set_name in set_names:
            if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys(
            ):
                try:
                    self.set_templates_data[
                        set_name] = self.tools.download_template(set_name)
                except:
                    Log.warning("Couldn't find a template for set name: " +
                                str(set_name))
                    continue

            if set_name in ('Secondary Displays', 'Secondary'):
                # Global function
                res_dict = self.diageo_generator.diageo_global_secondary_display_secondary_function(
                )

                # Saving to new tables
                if res_dict:
                    self.commonV2.write_to_db_result(
                        fk=res_dict['fk'],
                        numerator_id=1,
                        denominator_id=self.store_id,
                        result=res_dict['result'])

                # Saving to old tables
                set_score = self.tools.calculate_assortment(
                    assortment_entity='scene_id',
                    location_type='Secondary Shelf')
                self.save_level2_and_level3(set_name, set_name, set_score)

            # elif set_name == 'Visible to Customer':
            #
            #     # Global function
            #     sku_list = filter(None, self.scif[self.scif['product_type'] == 'SKU'].product_ean_code.tolist())
            #     res_dict = self.diageo_generator.diageo_global_visible_percentage(sku_list)
            #
            #     if res_dict:
            #         # Saving to new tables
            #         parent_res = res_dict[-1]
            #         for r in res_dict:
            #             self.commonV2.write_to_db_result(**r)
            #
            #         # Saving to old tables
            #         set_score = result = parent_res['result']
            #         self.save_level2_and_level3(set_name=set_name, kpi_name=set_name, score=result)
            #
            #     # filters = {self.tools.VISIBILITY_PRODUCTS_FIELD: 'Y'}
            #     # set_score = self.tools.calculate_visible_percentage(visible_filters=filters)
            #     # self.save_level2_and_level3(set_name, set_name, set_score)

            # elif set_name in ('MPA', 'New Products'):
            #     set_score = self.calculate_assortment_sets(set_name)
            # elif set_name in ('POSM',):
            #     set_score = self.calculate_posm_sets(set_name)
            else:
                continue

            if set_score == 0:
                pass
            elif set_score is False:
                continue

            set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name']
                                          == set_name]['kpi_set_fk'].values[0]
            self.write_to_db_result(set_fk, set_score, self.LEVEL1)

        # commiting to new tables
        self.commonV2.commit_results_data()

    def activate_ootb_kpis(self):
        # FACINGS_SOS_MANUFACTURER_IN_WHOLE_STORE - level 1
        sos_store_fk = self.commonV2.get_kpi_fk_by_kpi_name('SOS OUT OF STORE')
        sos_store = ManufacturerFacingsSOSInWholeStore(
            data_provider=self.data_provider,
            kpi_definition_fk=sos_store_fk).calculate()
        # FACINGS_SOS_CATEGORY_IN_WHOLE_STORE - level 2
        sos_cat_out_of_store_fk = self.commonV2.get_kpi_fk_by_kpi_name(
            'SOS CATEGORY OUT OF STORE')
        sos_cat_out_of_store = self.calculate_sos_of_cat_of_out_of_store_new(
            sos_cat_out_of_store_fk)

        # FACINGS_SOS_SUB_CATEGORY_OUT_OF_CATEGORY - level 3
        sos_sub_cat_out_of_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name(
            'SOS SUB CATEGORY OUT OF CATEGORY')
        sos_sub_cat_out_of_cat = SubCategoryFacingsSOSPerCategory(
            data_provider=self.data_provider,
            kpi_definition_fk=sos_sub_cat_out_of_cat_fk).calculate()

        # FACINGS_SOS_MANUFACTURER_OUT_OF_SUB_CATEGORY - level 4
        sos_man_out_of_sub_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name(
            'SOS MANUFACTURER OUT OF SUB CATEGORY')
        sos_man_out_of_sub_cat = ManufacturerFacingsSOSPerSubCategoryInStore(
            data_provider=self.data_provider,
            kpi_definition_fk=sos_man_out_of_sub_cat_fk).calculate()

        # FACINGS_SOS_BRAND_OUT_OF_SUB_CATEGORY_IN_WHOLE_STORE - level 5
        sos_brand_out_of_sub_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name(
            'SOS BRAND OUT OF SUB CATEGORY')
        sos_brand_out_of_sub_cat = self.calculate_sos_of_brand_out_of_manufacturer_in_sub_cat(
            sos_brand_out_of_sub_cat_fk)

        # Savings results in Hierarchy
        self.save_hierarchy(sos_store, sos_cat_out_of_store,
                            sos_sub_cat_out_of_cat, sos_man_out_of_sub_cat,
                            sos_brand_out_of_sub_cat)

    def calculate_sos_of_brand_out_of_manufacturer_in_sub_cat(self, kpi_fk):
        res_list = []
        res_dict = dict()
        # Get rid of Irrelevant and Empty types and keep only facings > 1
        filtered_scif = self.scif[
            ~self.scif['product_type'].isin(['Irrelevant', 'Empty'])
            & self.scif['facings_ign_stack'] > 0]

        # Filter by each Sub Category and Manufacturer
        sub_cat_fk_list = filtered_scif['sub_category_fk'].unique().tolist()
        for sub_cat in sub_cat_fk_list:
            filtered_scif_by_sub_cat = filtered_scif[
                filtered_scif['sub_category_fk'] == sub_cat]
            list_of_relevant_manufacturers = filtered_scif_by_sub_cat[
                'manufacturer_fk'].unique().tolist()
            for manu_fk in list_of_relevant_manufacturers:
                filtered_scif_by_sub_cat_and_manufacturer = filtered_scif_by_sub_cat[
                    filtered_scif_by_sub_cat['manufacturer_fk'] == manu_fk]
                denominator_result = filtered_scif_by_sub_cat_and_manufacturer[
                    'facings_ign_stack'].sum()

                # Calculate results per Brand
                list_of_relevant_brands = filtered_scif_by_sub_cat_and_manufacturer[
                    'brand_fk'].unique().tolist()
                for brand_fk in list_of_relevant_brands:
                    filtered_scif_by_brand = filtered_scif_by_sub_cat_and_manufacturer[
                        filtered_scif_by_sub_cat_and_manufacturer['brand_fk']
                        == brand_fk]
                    facings_brand_results = filtered_scif_by_brand[
                        'facings_ign_stack'].sum()
                    result_for_brand = facings_brand_results / denominator_result

                    # Preparing the results' dictionary
                    res_dict['kpi_definition_fk'] = kpi_fk
                    res_dict['numerator_id'] = brand_fk
                    res_dict['numerator_result'] = facings_brand_results
                    res_dict['denominator_id'] = int(sub_cat)
                    res_dict['denominator_result'] = denominator_result
                    res_dict['identifier_result'] = (int(brand_fk),
                                                     int(sub_cat),
                                                     int(manu_fk))
                    res_dict['identifier_parent'] = int(manu_fk), (
                        int(sub_cat))
                    res_dict['result'] = result_for_brand
                    res_dict['score'] = result_for_brand
                    res_list.append(res_dict.copy())
        return res_list

    def calculate_sos_of_cat_of_out_of_store_new(self, kpi_fk):
        res_list = []
        res_dict = dict()
        # Get rid of Irrelevant and Empty types and keep only facings ignore stacking > 1
        filtered_scif = self.scif[
            ~self.scif['product_type'].isin(['Irrelevant', 'Empty'])
            & self.scif['facings_ign_stack'] > 0]
        denominator_result = filtered_scif['facings_ign_stack'].sum()
        categories_fk_list = filtered_scif['category_fk'].unique().tolist()

        # Calculate result per category (using facings_ign_stack!)
        for category_fk in categories_fk_list:
            filtered_scif_by_category = filtered_scif[
                filtered_scif['category_fk'] == category_fk]
            facings_category_result = filtered_scif_by_category[
                'facings_ign_stack'].sum()
            result_for_category = facings_category_result / denominator_result

            # Preparing the results' dictionary
            res_dict['kpi_definition_fk'] = kpi_fk
            res_dict['numerator_id'] = category_fk
            res_dict['numerator_result'] = facings_category_result
            res_dict['denominator_id'] = self.store_id
            res_dict['denominator_result'] = denominator_result
            res_dict['result'] = result_for_category
            res_dict['score'] = result_for_category
            res_list.append(res_dict.copy())
        return res_list

    def save_hierarchy(self, level_1, level_2, level_3, level_4, level_5):
        for i in level_1:
            res = i.to_dict
            kpi_identifier = "level_1"
            self.commonV2.write_to_db_result(
                fk=res['kpi_definition_fk'],
                numerator_id=res['numerator_id'],
                denominator_id=res['denominator_id'],
                numerator_result=res['numerator_result'],
                denominator_result=res['denominator_result'],
                result=res['result'],
                score=res['result'],
                identifier_result=kpi_identifier,
                should_enter=False)

        for res in level_2:
            kpi_identifier = "level_2_" + str(int(res['numerator_id']))
            parent_identifier = "level_1"
            self.commonV2.write_to_db_result(
                fk=res['kpi_definition_fk'],
                numerator_id=res['numerator_id'],
                denominator_id=res['denominator_id'],
                numerator_result=res['numerator_result'],
                denominator_result=res['denominator_result'],
                result=res['result'],
                score=res['result'],
                identifier_result=kpi_identifier,
                identifier_parent=parent_identifier,
                should_enter=True)

        for i in level_3:
            res = i.to_dict
            kpi_identifier = str(int(res['numerator_id']))
            parent_identifier = "level_2_" + str(int(res['denominator_id']))
            self.commonV2.write_to_db_result(
                fk=res['kpi_definition_fk'],
                numerator_id=res['numerator_id'],
                denominator_id=res['denominator_id'],
                numerator_result=res['numerator_result'],
                denominator_result=res['denominator_result'],
                result=res['result'],
                score=res['result'],
                identifier_result=kpi_identifier,
                identifier_parent=parent_identifier,
                should_enter=True)

        for i in level_4:
            res = i.to_dict
            kpi_identifier = "level_4_" + str(
                (int(res['numerator_id']), int(res['denominator_id'])))
            parent_identifier = str(int(res['denominator_id']))
            self.commonV2.write_to_db_result(
                fk=res['kpi_definition_fk'],
                numerator_id=res['numerator_id'],
                denominator_id=res['denominator_id'],
                numerator_result=res['numerator_result'],
                denominator_result=res['denominator_result'],
                result=res['result'],
                score=res['result'],
                identifier_result=kpi_identifier,
                identifier_parent=parent_identifier,
                should_enter=True)

        for res in level_5:
            kpi_identifier = "level_5_" + str(res['identifier_result'])
            parent_identifier = "level_4_" + str(res['identifier_parent'])
            self.commonV2.write_to_db_result(
                fk=res['kpi_definition_fk'],
                numerator_id=res['numerator_id'],
                denominator_id=res['denominator_id'],
                numerator_result=res['numerator_result'],
                denominator_result=res['denominator_result'],
                result=res['result'],
                score=res['result'],
                identifier_result=kpi_identifier,
                identifier_parent=parent_identifier,
                should_enter=True)

    def save_level2_and_level3(self, set_name, kpi_name, score):
        """
        Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
        """
        kpi_data = self.kpi_static_data[
            (self.kpi_static_data['kpi_set_name'] == set_name)
            & (self.kpi_static_data['kpi_name'] == kpi_name)]
        kpi_fk = kpi_data['kpi_fk'].values[0]
        atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
        self.write_to_db_result(kpi_fk, score, self.LEVEL2)
        self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)

    def calculate_posm_sets(self, set_name):
        """
        This function calculates every POSM-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.store_channel is None:
                break

            kpi_res = self.tools.calculate_posm(
                display_name=params.get(self.tools.DISPLAY_NAME))
            score = 1 if kpi_res > 0 else 0
            if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
                scores.append(score)

            if score == 1 or params.get(
                    self.store_type) == self.tools.RELEVANT_FOR_STORE:
                self.save_level2_and_level3(
                    set_name, params.get(self.tools.DISPLAY_NAME), score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def calculate_assortment_sets(self, set_name):
        """
        This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        segment = '{};{}'.format(self.store_type, self.segment)
        for params in self.set_templates_data[set_name]:
            if params.get(segment,
                          '').capitalize() in (self.tools.RELEVANT_FOR_STORE,
                                               self.tools.OR_OTHER_PRODUCTS):

                object_type = self.tools.ENTITY_TYPE_CONVERTER.get(
                    params.get(self.tools.ENTITY_TYPE), 'product_ean_code')
                objects = [
                    str(
                        params.get(
                            self.tools.PRODUCT_EAN_CODE,
                            params.get(self.tools.PRODUCT_EAN_CODE2, '')))
                ]
                if params.get(self.store_type) == self.tools.OR_OTHER_PRODUCTS:
                    additional_objects = str(
                        params.get(self.tools.ADDITIONAL_SKUS)).split(',')
                    objects.extend(additional_objects)
                filters = {object_type: objects}
                result = self.tools.calculate_assortment(**filters)
                score = 1 if result > 0 else 0
                scores.append(score)

                self.save_level2_and_level3(
                    set_name, params.get(self.tools.PRODUCT_NAME), score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def write_to_db_result(self, fk, score, level):
        """
        This function the result data frame of every KPI (atomic KPI/KPI/KPI set),
        and appends the insert SQL query into the queries' list, later to be written to the DB.
        """
        attributes = self.create_attributes_dict(fk, score, level)
        if level == self.LEVEL1:
            table = KPS_RESULT
        elif level == self.LEVEL2:
            table = KPK_RESULT
        elif level == self.LEVEL3:
            table = KPI_RESULT
        else:
            return
        query = insert(attributes, table)
        self.kpi_results_queries.append(query)

    def create_attributes_dict(self, fk, score, level):
        """
        This function creates a data frame with all attributes needed for saving in KPI results tables.

        """
        score = round(score, 2)
        if level == self.LEVEL1:
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['kpi_set_fk'] ==
                fk]['kpi_set_name'].values[0]
            score_type = '%' if kpi_set_name in self.tools.KPI_SETS_WITH_PERCENT_AS_SCORE else ''
            attributes = pd.DataFrame(
                [(kpi_set_name, self.session_uid, self.store_id,
                  self.visit_date.isoformat(), format(score,
                                                      '.2f'), score_type, fk)],
                columns=[
                    'kps_name', 'session_uid', 'store_fk', 'visit_date',
                    'score_1', 'score_2', 'kpi_set_fk'
                ])

        elif level == self.LEVEL2:
            kpi_name = self.kpi_static_data[self.kpi_static_data['kpi_fk'] ==
                                            fk]['kpi_name'].values[0].replace(
                                                "'", "\\'")
            attributes = pd.DataFrame(
                [(self.session_uid, self.store_id, self.visit_date.isoformat(),
                  fk, kpi_name, score)],
                columns=[
                    'session_uid', 'store_fk', 'visit_date', 'kpi_fk',
                    'kpk_name', 'score'
                ])
        elif level == self.LEVEL3:
            data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
                                        == fk]
            atomic_kpi_name = data['atomic_kpi_name'].values[0].replace(
                "'", "\\'")
            kpi_fk = data['kpi_fk'].values[0]
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['atomic_kpi_fk'] ==
                fk]['kpi_set_name'].values[0]
            attributes = pd.DataFrame([
                (atomic_kpi_name, self.session_uid,
                 kpi_set_name, self.store_id, self.visit_date.isoformat(),
                 datetime.utcnow().isoformat(), score, kpi_fk, fk, None, None)
            ],
                                      columns=[
                                          'display_text', 'session_uid',
                                          'kps_name', 'store_fk', 'visit_date',
                                          'calculation_time', 'score',
                                          'kpi_fk', 'atomic_kpi_fk',
                                          'threshold', 'result'
                                      ])
        else:
            attributes = pd.DataFrame()
        return attributes.to_dict()

    @log_runtime('Saving to DB')
    def commit_results_data(self):
        """
        This function writes all KPI results to the DB, and commits the changes.
        """
        cur = self.rds_conn.db.cursor()
        delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
            self.session_uid)
        for query in delete_queries:
            cur.execute(query)
        for query in self.kpi_results_queries:
            cur.execute(query)
        self.rds_conn.db.commit()
Ejemplo n.º 9
0
class PENAFLORARToolBox:
    LEVEL1 = 1
    LEVEL2 = 2
    LEVEL3 = 3
    ACTIVATION_STANDARD = 'Activation Standard'

    def __init__(self, data_provider, output):
        self.output = output
        self.data_provider = data_provider
        self.project_name = self.data_provider.project_name
        self.session_uid = self.data_provider.session_uid
        self.products = self.data_provider[Data.PRODUCTS]
        self.all_products = self.data_provider[Data.ALL_PRODUCTS]
        self.match_product_in_scene = self.data_provider[Data.MATCHES]
        self.visit_date = self.data_provider[Data.VISIT_DATE]
        self.session_info = self.data_provider[Data.SESSION_INFO]
        self.scene_info = self.data_provider[Data.SCENES_INFO]
        self.store_id = self.data_provider[Data.STORE_FK]
        self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
        self.rds_conn = PSProjectConnector(self.project_name,
                                           DbUsers.CalculationEng)
        self.store_info = self.data_provider[Data.STORE_INFO]
        self.store_type = self.store_info['additional_attribute_1'].values[0]
        self.kpi_static_data = self.get_kpi_static_data()
        self.set_templates_data = {}
        self.match_display_in_scene = self.get_match_display()
        self.kpi_results_queries = []
        self.scores = {self.LEVEL1: {}, self.LEVEL2: {}, self.LEVEL3: {}}
        self.output = output
        self.common = Common(self.data_provider)
        self.commonV2 = CommonV2(self.data_provider)
        self.tools = DIAGEOToolBox(
            self.data_provider,
            output,
            match_display_in_scene=self.match_display_in_scene
        )  # replace the old one
        self.diageo_generator = DIAGEOGenerator(self.data_provider,
                                                self.output, self.common)

    def get_kpi_static_data(self):
        """
        This function extracts the static KPI data and saves it into one global data frame.
        The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
        """
        query = DIAGEOQueries.get_all_kpi_data()
        kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
        return kpi_static_data

    def get_match_display(self):
        """
        This function extracts the display matches data and saves it into one global data frame.
        The data is taken from probedata.match_display_in_scene.
        """
        query = DIAGEOQueries.get_match_display(self.session_uid)
        match_display = pd.read_sql_query(query, self.rds_conn.db)
        return match_display

    def main_calculation(self, set_names):
        """
        This function calculates the KPI results.
        """
        # SOS Out Of The Box kpis
        self.activate_ootb_kpis()

        # sos by scene type
        self.diageo_generator.sos_by_scene_type(self.commonV2)

        # Global assortment kpis
        assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2(
        )
        self.commonV2.save_json_to_new_tables(assortment_res_dict)

        # Global assortment kpis - v3 for NEW MOBILE REPORTS use
        assortment_res_dict_v3 = self.diageo_generator.diageo_global_assortment_function_v3(
        )
        self.commonV2.save_json_to_new_tables(assortment_res_dict_v3)

        for set_name in set_names:
            if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys(
            ):
                try:
                    self.set_templates_data[
                        set_name] = self.tools.download_template(set_name)
                except:
                    Log.warning("Couldn't find a template for set name: " +
                                str(set_name))
                    continue

            # Global Visible to Customer / Visible to Consumer
            if set_name in ('Visible to Customer', 'Visible to Consumer %',
                            'Visible to Consumer'):
                # Global function
                sku_list = filter(
                    None, self.scif[self.scif['product_type'] ==
                                    'SKU'].product_ean_code.tolist())
                res_dict = self.diageo_generator.diageo_global_visible_percentage(
                    sku_list)

                if res_dict:
                    # Saving to new tables
                    self.commonV2.save_json_to_new_tables(res_dict)

                # Saving to old tables
                filters = {self.tools.VISIBILITY_PRODUCTS_FIELD: 'Y'}
                set_score = self.tools.calculate_visible_percentage(
                    visible_filters=filters)
                self.save_level2_and_level3(set_name, set_name, set_score)

            elif set_name in ('Relative Position'):
                # Global function
                res_dict = self.diageo_generator.diageo_global_relative_position_function(
                    self.set_templates_data[set_name],
                    location_type='template_display_name')

                if res_dict:
                    # Saving to new tables
                    self.commonV2.save_json_to_new_tables(res_dict)

                set_score = self.calculate_relative_position_sets(set_name)

            else:
                continue

            if set_score == 0:
                pass
            elif set_score is False:
                continue

            set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name']
                                          == set_name]['kpi_set_fk'].values[0]
            self.write_to_db_result(set_fk, set_score, self.LEVEL1)

        # commiting to new tables
        self.commonV2.commit_results_data()

    def calculate_assortment_sets(self, set_name):
        """
        This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            target = str(params.get(self.store_type, ''))
            if target.isdigit() or target.capitalize() in (
                    self.tools.RELEVANT_FOR_STORE,
                    self.tools.OR_OTHER_PRODUCTS):
                products = str(
                    params.get(self.tools.PRODUCT_EAN_CODE,
                               params.get(self.tools.PRODUCT_EAN_CODE2,
                                          ''))).replace(',', ' ').split()
                target = 1 if not target.isdigit() else int(target)
                kpi_name = params.get(self.tools.GROUP_NAME,
                                      params.get(self.tools.PRODUCT_NAME))
                kpi_static_data = self.kpi_static_data[
                    (self.kpi_static_data['kpi_set_name'] == set_name)
                    & (self.kpi_static_data['kpi_name'] == kpi_name)]
                if len(products) > 1:
                    result = 0
                    for product in products:
                        product_score = self.tools.calculate_assortment(
                            product_ean_code=product)
                        result += product_score
                        atomic_fk = kpi_static_data[
                            kpi_static_data['description'] ==
                            product]['atomic_kpi_fk'].values[0]
                        self.write_to_db_result(atomic_fk,
                                                product_score,
                                                level=self.LEVEL3)
                    score = 1 if result >= target else 0
                else:
                    result = self.tools.calculate_assortment(
                        product_ean_code=products)
                    atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
                    score = 1 if result >= target else 0
                    self.write_to_db_result(atomic_fk,
                                            score,
                                            level=self.LEVEL3)

                scores.append(score)
                kpi_fk = kpi_static_data['kpi_fk'].values[0]
                self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def calculate_relative_position_sets(self, set_name):
        """
        This function calculates every relative-position-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.store_info.at[0, 'additional_attribute_2'] == params.get(
                    'additional_attribute_2', 'Empty'):
                tested_filters = {
                    params.get(TESTED_TYPE): params.get(TESTED_VALUE)
                }
                anchor_filters = {
                    params.get(ANCHOR_TYPE): params.get(ANCHOR_VALUE)
                }
                direction_data = {
                    'top':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.TOP_DISTANCE)),
                    'bottom':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.BOTTOM_DISTANCE)),
                    'left':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.LEFT_DISTANCE)),
                    'right':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.RIGHT_DISTANCE))
                }
                general_filters = {
                    'template_display_name': params.get(self.tools.LOCATION)
                }
                result = self.tools.calculate_relative_position(
                    tested_filters, anchor_filters, direction_data,
                    **general_filters)
                score = 1 if result else 0
                scores.append(score)

                self.save_level2_and_level3(set_name,
                                            params.get(self.tools.KPI_NAME),
                                            score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def _get_direction_for_relative_position(self, value):
        """
        This function converts direction data from the template (as string) to a number.
        """
        if value == self.tools.UNLIMITED_DISTANCE:
            value = 1000
        elif not value or not str(value).isdigit():
            value = 0
        else:
            value = int(value)
        return value

    def calculate_activation_standard(self):
        """
        This function calculates the Activation Standard KPI, and saves the result to the DB (for all 3 levels).
        """
        final_score = 0
        for params in self.tools.download_template(self.ACTIVATION_STANDARD):
            set_name = params.get(self.tools.ACTIVATION_SET_NAME)
            kpi_name = params.get(self.tools.ACTIVATION_KPI_NAME)
            target = float(params.get(self.tools.ACTIVATION_TARGET))
            target = target * 100 if target < 1 else target
            score_type = params.get(self.tools.ACTIVATION_SCORE)
            weight = float(params.get(self.tools.ACTIVATION_WEIGHT))
            if kpi_name:
                kpi_fk = self.kpi_static_data[
                    (self.kpi_static_data['kpi_set_name'] == set_name)
                    & (self.kpi_static_data['kpi_name'] == kpi_name
                       )]['kpi_fk'].values[0]
                score = self.scores[self.LEVEL2].get(kpi_fk, 0)
            else:
                set_fk = self.kpi_static_data[
                    self.kpi_static_data['kpi_set_name'] ==
                    set_name]['kpi_set_fk'].values[0]
                score = self.scores[self.LEVEL1].get(set_fk, 0)
            if score >= target:
                score = 100
            else:
                if score_type == 'PROPORTIONAL':
                    score = (score / float(target)) * 100
                else:
                    score = 0
            final_score += score * weight
            self.save_level2_and_level3(self.ACTIVATION_STANDARD, set_name,
                                        score)
        set_fk = self.kpi_static_data[
            self.kpi_static_data['kpi_set_name'] ==
            self.ACTIVATION_STANDARD]['kpi_set_fk'].values[0]
        self.write_to_db_result(set_fk, final_score, self.LEVEL1)

    def save_level2_and_level3(self, set_name, kpi_name, score):
        """
        Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
        """
        kpi_data = self.kpi_static_data[
            (self.kpi_static_data['kpi_set_name'] == set_name)
            & (self.kpi_static_data['kpi_name'] == kpi_name)]

        kpi_fk = kpi_data['kpi_fk'].values[0]
        atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
        self.write_to_db_result(kpi_fk, score, self.LEVEL2)
        self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)

    def activate_ootb_kpis(self):
        # FACINGS_SOS_MANUFACTURER_IN_WHOLE_STORE - level 1
        sos_store_fk = self.commonV2.get_kpi_fk_by_kpi_name('SOS OUT OF STORE')
        sos_store = ManufacturerFacingsSOSInWholeStore(
            data_provider=self.data_provider,
            kpi_definition_fk=sos_store_fk).calculate()
        # FACINGS_SOS_CATEGORY_IN_WHOLE_STORE - level 2
        sos_cat_out_of_store_fk = self.commonV2.get_kpi_fk_by_kpi_name(
            'SOS CATEGORY OUT OF STORE')
        sos_cat_out_of_store = self.calculate_sos_of_cat_of_out_of_store_new(
            sos_cat_out_of_store_fk)

        # FACINGS_SOS_SUB_CATEGORY_OUT_OF_CATEGORY - level 3
        sos_sub_cat_out_of_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name(
            'SOS SUB CATEGORY OUT OF CATEGORY')
        sos_sub_cat_out_of_cat = SubCategoryFacingsSOSPerCategory(
            data_provider=self.data_provider,
            kpi_definition_fk=sos_sub_cat_out_of_cat_fk).calculate()

        # FACINGS_SOS_MANUFACTURER_OUT_OF_SUB_CATEGORY - level 4
        sos_man_out_of_sub_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name(
            'SOS MANUFACTURER OUT OF SUB CATEGORY')
        sos_man_out_of_sub_cat = ManufacturerFacingsSOSPerSubCategoryInStore(
            data_provider=self.data_provider,
            kpi_definition_fk=sos_man_out_of_sub_cat_fk).calculate()

        # FACINGS_SOS_BRAND_OUT_OF_SUB_CATEGORY_IN_WHOLE_STORE - level 5
        sos_brand_out_of_sub_cat_fk = self.commonV2.get_kpi_fk_by_kpi_name(
            'SOS BRAND OUT OF MANUFACTURER')
        sos_brand_out_of_sub_cat = self.calculate_sos_of_brand_out_of_manufacturer_in_sub_cat(
            sos_brand_out_of_sub_cat_fk)

        # Savings results in Hierarchy
        self.save_hierarchy(sos_store, sos_cat_out_of_store,
                            sos_sub_cat_out_of_cat, sos_man_out_of_sub_cat,
                            sos_brand_out_of_sub_cat)

    def calculate_sos_of_brand_out_of_manufacturer_in_sub_cat(self, kpi_fk):
        pass
        res_list = []
        res_dict = dict()
        # Get rid of Irrelevant and Empty types and keep only facings > 1
        filtered_scif = self.scif[
            ~self.scif['product_type'].isin(['Irrelevant', 'Empty'])
            & self.scif['facings_ign_stack'] > 0]

        # Filter by each Sub Category and Manufacturer
        sub_cat_fk_list = filtered_scif['sub_category_fk'].unique().tolist()
        for sub_cat in sub_cat_fk_list:
            filtered_scif_by_sub_cat = filtered_scif[
                filtered_scif['sub_category_fk'] == sub_cat]
            list_of_relevant_manufacturers = filtered_scif_by_sub_cat[
                'manufacturer_fk'].unique().tolist()
            for manu_fk in list_of_relevant_manufacturers:
                filtered_scif_by_sub_cat_and_manufacturer = filtered_scif_by_sub_cat[
                    filtered_scif_by_sub_cat['manufacturer_fk'] == manu_fk]
                denominator_result = filtered_scif_by_sub_cat_and_manufacturer[
                    'facings_ign_stack'].sum()

                # Calculate results per Brand
                list_of_relevant_brands = filtered_scif_by_sub_cat_and_manufacturer[
                    'brand_fk'].unique().tolist()
                for brand_fk in list_of_relevant_brands:
                    filtered_scif_by_brand = filtered_scif_by_sub_cat_and_manufacturer[
                        filtered_scif_by_sub_cat_and_manufacturer['brand_fk']
                        == brand_fk]
                    facings_brand_results = filtered_scif_by_brand[
                        'facings_ign_stack'].sum()
                    result_for_brand = facings_brand_results / denominator_result

                    # Preparing the results' dictionary
                    res_dict['kpi_definition_fk'] = kpi_fk
                    res_dict['numerator_id'] = brand_fk
                    res_dict['numerator_result'] = facings_brand_results
                    res_dict['denominator_id'] = int(sub_cat)
                    res_dict['denominator_result'] = denominator_result
                    res_dict['identifier_result'] = (int(brand_fk),
                                                     int(sub_cat),
                                                     int(manu_fk))
                    res_dict['identifier_parent'] = int(manu_fk), (
                        int(sub_cat))
                    res_dict['result'] = result_for_brand
                    res_dict['score'] = result_for_brand
                    res_list.append(res_dict.copy())
        return res_list

    def calculate_sos_of_cat_of_out_of_store_new(self, kpi_fk):
        res_list = []
        res_dict = dict()
        # Get rid of Irrelevant and Empty types and keep only facings ignore stacking > 1
        filtered_scif = self.scif[
            ~self.scif['product_type'].isin(['Irrelevant', 'Empty'])
            & self.scif['facings_ign_stack'] > 0]
        denominator_result = filtered_scif['facings_ign_stack'].sum()
        categories_fk_list = filtered_scif['category_fk'].unique().tolist()

        # Calculate result per category (using facings_ign_stack!)
        for category_fk in categories_fk_list:
            filtered_scif_by_category = filtered_scif[
                filtered_scif['category_fk'] == category_fk]
            facings_category_result = filtered_scif_by_category[
                'facings_ign_stack'].sum()
            result_for_category = facings_category_result / denominator_result

            # Preparing the results' dictionary
            res_dict['kpi_definition_fk'] = kpi_fk
            res_dict['numerator_id'] = category_fk
            res_dict['numerator_result'] = facings_category_result
            res_dict['denominator_id'] = self.store_id
            res_dict['denominator_result'] = denominator_result
            res_dict['result'] = result_for_category
            res_dict['score'] = result_for_category
            res_list.append(res_dict.copy())
        return res_list

    def save_hierarchy(self, level_1, level_2, level_3, level_4, level_5):
        for i in level_1:
            res = i.to_dict
            kpi_identifier = "level_1"
            self.commonV2.write_to_db_result(
                fk=res['kpi_definition_fk'],
                numerator_id=res['numerator_id'],
                denominator_id=res['denominator_id'],
                numerator_result=res['numerator_result'],
                denominator_result=res['denominator_result'],
                result=res['result'],
                score=res['result'],
                identifier_result=kpi_identifier,
                should_enter=False)

        for res in level_2:
            kpi_identifier = "level_2_" + str(int(res['numerator_id']))
            parent_identifier = "level_1"
            self.commonV2.write_to_db_result(
                fk=res['kpi_definition_fk'],
                numerator_id=res['numerator_id'],
                denominator_id=res['denominator_id'],
                numerator_result=res['numerator_result'],
                denominator_result=res['denominator_result'],
                result=res['result'],
                score=res['result'],
                identifier_result=kpi_identifier,
                identifier_parent=parent_identifier,
                should_enter=True)

        for i in level_3:
            res = i.to_dict
            kpi_identifier = str(int(res['numerator_id']))
            parent_identifier = "level_2_" + str(int(res['denominator_id']))
            self.commonV2.write_to_db_result(
                fk=res['kpi_definition_fk'],
                numerator_id=res['numerator_id'],
                denominator_id=res['denominator_id'],
                numerator_result=res['numerator_result'],
                denominator_result=res['denominator_result'],
                result=res['result'],
                score=res['result'],
                identifier_result=kpi_identifier,
                identifier_parent=parent_identifier,
                should_enter=True)

        for i in level_4:
            res = i.to_dict
            kpi_identifier = "level_4_" + str(
                (int(res['numerator_id']), int(res['denominator_id'])))
            parent_identifier = str(int(res['denominator_id']))
            self.commonV2.write_to_db_result(
                fk=res['kpi_definition_fk'],
                numerator_id=res['numerator_id'],
                denominator_id=res['denominator_id'],
                numerator_result=res['numerator_result'],
                denominator_result=res['denominator_result'],
                result=res['result'],
                score=res['result'],
                identifier_result=kpi_identifier,
                identifier_parent=parent_identifier,
                should_enter=True)

        for res in level_5:
            kpi_identifier = "level_5_" + str(res['identifier_result'])
            parent_identifier = "level_4_" + str(res['identifier_parent'])
            self.commonV2.write_to_db_result(
                fk=res['kpi_definition_fk'],
                numerator_id=res['numerator_id'],
                denominator_id=res['denominator_id'],
                numerator_result=res['numerator_result'],
                denominator_result=res['denominator_result'],
                result=res['result'],
                score=res['result'],
                identifier_result=kpi_identifier,
                identifier_parent=parent_identifier,
                should_enter=True)

    def write_to_db_result(self, fk, score, level):
        """
        This function creates the result data frame of every KPI (atomic KPI/KPI/KPI set),
        and appends the insert SQL query into the queries' list, later to be written to the DB.
        """
        attributes = self.create_attributes_dict(fk, score, level)
        if level == self.LEVEL1:
            table = KPS_RESULT
        elif level == self.LEVEL2:
            table = KPK_RESULT
        elif level == self.LEVEL3:
            table = KPI_RESULT
        else:
            return
        query = insert(attributes, table)
        self.kpi_results_queries.append(query)

    def create_attributes_dict(self, fk, score, level):
        """
        This function creates a data frame with all attributes needed for saving in KPI results tables.

        """
        if level == self.LEVEL1:
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['kpi_set_fk'] ==
                fk]['kpi_set_name'].values[0]
            attributes = pd.DataFrame(
                [(kpi_set_name, self.session_uid, self.store_id,
                  self.visit_date.isoformat(), format(score, '.2f'), fk)],
                columns=[
                    'kps_name', 'session_uid', 'store_fk', 'visit_date',
                    'score_1', 'kpi_set_fk'
                ])
        elif level == self.LEVEL2:
            kpi_name = self.kpi_static_data[self.kpi_static_data['kpi_fk'] ==
                                            fk]['kpi_name'].values[0].replace(
                                                "'", "''")
            attributes = pd.DataFrame(
                [(self.session_uid, self.store_id, self.visit_date.isoformat(),
                  fk, kpi_name, score)],
                columns=[
                    'session_uid', 'store_fk', 'visit_date', 'kpi_fk',
                    'kpk_name', 'score'
                ])
        elif level == self.LEVEL3:
            data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
                                        == fk]
            atomic_kpi_name = data['atomic_kpi_name'].values[0].replace(
                "'", "''")
            kpi_fk = data['kpi_fk'].values[0]
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['atomic_kpi_fk'] ==
                fk]['kpi_set_name'].values[0]
            attributes = pd.DataFrame(
                [(atomic_kpi_name, self.session_uid,
                  kpi_set_name, self.store_id, self.visit_date.isoformat(),
                  datetime.utcnow().isoformat(), score, kpi_fk, fk)],
                columns=[
                    'display_text', 'session_uid', 'kps_name', 'store_fk',
                    'visit_date', 'calculation_time', 'score', 'kpi_fk',
                    'atomic_kpi_fk'
                ])
        else:
            attributes = pd.DataFrame()
        return attributes.to_dict()

    @log_runtime('Saving to DB')
    def commit_results_data(self):
        """
        This function writes all KPI results to the DB, and commits the changes.
        """
        insert_queries = self.merge_insert_queries(self.kpi_results_queries)
        rds_conn = PSProjectConnector(self.project_name,
                                      DbUsers.CalculationEng)
        cur = rds_conn.db.cursor()
        delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
            self.session_uid)
        for query in delete_queries:
            cur.execute(query)
        for query in insert_queries:
            cur.execute(query)
        rds_conn.db.commit()

    @staticmethod
    def merge_insert_queries(insert_queries):
        query_groups = {}
        for query in insert_queries:
            static_data, inserted_data = query.split('VALUES ')
            if static_data not in query_groups:
                query_groups[static_data] = []
            query_groups[static_data].append(inserted_data)
        merged_queries = []
        for group in query_groups:
            merged_queries.append('{0} VALUES {1}'.format(
                group, ',\n'.join(query_groups[group])))
        return merged_queries
Ejemplo n.º 10
0
class DIAGEOGAToolBox:
    LEVEL1 = 1
    LEVEL2 = 2
    LEVEL3 = 3

    def __init__(self, data_provider, output):
        self.k_engine = BaseCalculationsScript(data_provider, output)
        self.output = output
        self.data_provider = data_provider
        self.project_name = self.data_provider.project_name
        self.session_uid = self.data_provider.session_uid
        self.products = self.data_provider[Data.PRODUCTS]
        self.all_products = self.data_provider[Data.ALL_PRODUCTS]
        self.match_product_in_scene = self.data_provider[Data.MATCHES]
        self.visit_date = self.data_provider[Data.VISIT_DATE]
        self.session_info = self.data_provider[Data.SESSION_INFO]
        self.scene_info = self.data_provider[Data.SCENES_INFO]
        self.store_id = self.data_provider[Data.STORE_FK]
        self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
        self.rds_conn = PSProjectConnector(self.project_name,
                                           DbUsers.CalculationEng)
        self.match_display_in_scene = self.get_match_display()
        self.tools = DIAGEOToolBox(
            self.data_provider,
            output,
            match_display_in_scene=self.match_display_in_scene)
        self.common = Common(self.data_provider)
        self.diageo_generator = DIAGEOGenerator(self.data_provider,
                                                self.output,
                                                self.common,
                                                menu=True)
        self.kpi_static_data = self.get_kpi_static_data()
        self.kpi_results_queries = []
        self.store_info = self.data_provider[Data.STORE_INFO]
        self.store_type = self.store_info['additional_attribute_1'].values[0]
        self.commonV2 = CommonV2(self.data_provider)

        self.set_templates_data = {}
        self.kpi_static_data = self.get_kpi_static_data()

    def get_kpi_static_data(self):
        """
        This function extracts the static KPI data and saves it into one global data frame.
        The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
        """
        query = DIAGEOQueries.get_all_kpi_data()
        kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
        return kpi_static_data

    def main_calculation(self, set_names):
        """
        This function calculates the KPI results.
        """

        assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2(
        )
        self.commonV2.save_json_to_new_tables(assortment_res_dict)

        # Global Menu kpis
        menus_res_dict = self.diageo_generator.diageo_global_share_of_menu_cocktail_function(
            cocktail_product_level=True)
        self.commonV2.save_json_to_new_tables(menus_res_dict)

        for set_name in set_names:
            set_score = 0
            if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys(
            ):
                self.set_templates_data[
                    set_name] = self.tools.download_template(set_name)
            # if set_name in ('MPA', 'New Products'):
            #    set_score = self.calculate_assortment_sets(set_name)
            if set_name in ('Secondary Displays'):
                res_json = self.diageo_generator.diageo_global_secondary_display_secondary_function(
                )
                if res_json:
                    # Saving to new tables
                    self.commonV2.write_to_db_result(
                        fk=res_json['fk'],
                        numerator_id=1,
                        denominator_id=self.store_id,
                        result=res_json['result'])
                    set_score = self.tools.calculate_number_of_scenes(
                        location_type='Secondary')
                    self.save_level2_and_level3(set_name, set_name, set_score)
            if set_score == 0:
                pass
            elif set_score is False:
                continue

            set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name']
                                          == set_name]['kpi_set_fk'].values[0]
            self.write_to_db_result(set_fk, set_score, self.LEVEL1)

        # committing to new tables
        self.commonV2.commit_results_data()

    def calculate_assortment_sets(self, set_name):
        """
        This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            target = str(params.get(self.store_type, ''))
            if target.isdigit() or target.capitalize() in (
                    self.tools.RELEVANT_FOR_STORE,
                    self.tools.OR_OTHER_PRODUCTS):
                products = str(
                    params.get(self.tools.PRODUCT_EAN_CODE,
                               params.get(self.tools.PRODUCT_EAN_CODE2,
                                          ''))).replace(',', ' ').split()
                target = 1 if not target.isdigit() else int(target)
                kpi_name = params.get(self.tools.GROUP_NAME,
                                      params.get(self.tools.PRODUCT_NAME))
                kpi_static_data = self.kpi_static_data[
                    (self.kpi_static_data['kpi_set_name'] == set_name)
                    & (self.kpi_static_data['kpi_name'] == kpi_name)]
                if len(products) > 1:
                    result = 0
                    for product in products:
                        product_score = self.tools.calculate_assortment(
                            product_ean_code=product)
                        result += product_score
                        atomic_fk = kpi_static_data[
                            kpi_static_data['description'] ==
                            product]['atomic_kpi_fk'].values[0]
                        self.write_to_db_result(atomic_fk,
                                                product_score,
                                                level=self.LEVEL3)
                    score = 1 if result >= target else 0
                else:
                    result = self.tools.calculate_assortment(
                        product_ean_code=products)
                    atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
                    score = 1 if result >= target else 0
                    self.write_to_db_result(atomic_fk,
                                            score,
                                            level=self.LEVEL3)

                scores.append(score)
                kpi_fk = kpi_static_data['kpi_fk'].values[0]
                self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)

        if not scores:
            return 0
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def save_level2_and_level3(self, set_name, kpi_name, score):
        """
        Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
        """
        kpi_data = self.kpi_static_data[
            (self.kpi_static_data['kpi_set_name'] == set_name)
            & (self.kpi_static_data['kpi_name'] == kpi_name)]

        kpi_fk = kpi_data['kpi_fk'].values[0]
        atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
        self.write_to_db_result(kpi_fk, score, self.LEVEL2)
        self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)

    def write_to_db_result(self, fk, score, level):
        """
        This function creates the result data frame of every KPI (atomic KPI/KPI/KPI set),
        and appends the insert SQL query into the queries' list, later to be written to the DB.
        """
        attributes = self.create_attributes_dict(fk, score, level)
        if level == self.LEVEL1:
            table = KPS_RESULT
        elif level == self.LEVEL2:
            table = KPK_RESULT
        elif level == self.LEVEL3:
            table = KPI_RESULT
        else:
            return
        query = insert(attributes, table)
        self.kpi_results_queries.append(query)

    def create_attributes_dict(self, fk, score, level):
        """
        This function creates a data frame with all attributes needed for saving in KPI results tables.

        """
        if level == self.LEVEL1:
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['kpi_set_fk'] ==
                fk]['kpi_set_name'].values[0]
            attributes = pd.DataFrame(
                [(kpi_set_name, self.session_uid, self.store_id,
                  self.visit_date.isoformat(), format(score, '.2f'), fk)],
                columns=[
                    'kps_name', 'session_uid', 'store_fk', 'visit_date',
                    'score_1', 'kpi_set_fk'
                ])
        elif level == self.LEVEL2:
            kpi_name = self.kpi_static_data[self.kpi_static_data['kpi_fk'] ==
                                            fk]['kpi_name'].values[0]
            attributes = pd.DataFrame(
                [(self.session_uid, self.store_id, self.visit_date.isoformat(),
                  fk, kpi_name.replace("'", "''"), score)],
                columns=[
                    'session_uid', 'store_fk', 'visit_date', 'kpi_fk',
                    'kpk_name', 'score'
                ])
        elif level == self.LEVEL3:
            data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
                                        == fk]
            atomic_kpi_name = data['atomic_kpi_name'].values[0]
            kpi_fk = data['kpi_fk'].values[0]
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['atomic_kpi_fk'] ==
                fk]['kpi_set_name'].values[0]
            attributes = pd.DataFrame(
                [(atomic_kpi_name.replace("'", "''"), self.session_uid,
                  kpi_set_name, self.store_id, self.visit_date.isoformat(),
                  datetime.utcnow().isoformat(), score, kpi_fk, fk)],
                columns=[
                    'display_text', 'session_uid', 'kps_name', 'store_fk',
                    'visit_date', 'calculation_time', 'score', 'kpi_fk',
                    'atomic_kpi_fk'
                ])
        else:
            attributes = pd.DataFrame()
        return attributes.to_dict()

    @log_runtime('Saving to DB')
    def commit_results_data(self):
        """
        This function writes all KPI results to the DB, and commits the changes.
        """
        cur = self.rds_conn.db.cursor()
        delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
            self.session_uid)
        for query in delete_queries:
            cur.execute(query)
        for query in self.kpi_results_queries:
            cur.execute(query)
        self.rds_conn.db.commit()

    def get_match_display(self):
        """
        This function extracts the display matches data and saves it into one global data frame.
        The data is taken from probedata.match_display_in_scene.
        """
        query = DIAGEOQueries.get_match_display(self.session_uid)
        match_display = pd.read_sql_query(query, self.rds_conn.db)
        return match_display
Ejemplo n.º 11
0
class DIAGEOCO_SANDToolBox:
    LEVEL1 = 1
    LEVEL2 = 2
    LEVEL3 = 3
    DIAGEO_MANUFACTURER = 'DIAGEO'

    def __init__(self, data_provider, output):
        self.output = output
        self.data_provider = data_provider
        self.common = Common(self.data_provider)
        self.common_v2 = CommonV2(self.data_provider)
        self.project_name = self.data_provider.project_name
        self.session_uid = self.data_provider.session_uid
        self.products = self.data_provider[Data.PRODUCTS]
        self.all_products = self.data_provider[Data.ALL_PRODUCTS]
        self.match_product_in_scene = self.data_provider[Data.MATCHES]
        self.visit_date = self.data_provider[Data.VISIT_DATE]
        self.session_info = self.data_provider[Data.SESSION_INFO]
        self.scene_info = self.data_provider[Data.SCENES_INFO]
        self.store_id = self.data_provider[Data.STORE_FK]
        self.store_info = self.data_provider[Data.STORE_INFO]
        self.store_channel = self.store_info['store_type'].values[0]
        if self.store_channel:
            self.store_channel = self.store_channel.upper()
        self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
        self.rds_conn = PSProjectConnector(self.project_name,
                                           DbUsers.CalculationEng)
        self.kpi_static_data = self.common.get_kpi_static_data()
        self.kpi_results_queries = []
        self.set_templates_data = {}
        self.match_display_in_scene = self.get_match_display()
        self.tools = DIAGEOToolBox(
            self.data_provider,
            output,
            match_display_in_scene=self.match_display_in_scene)
        self.global_gen = DIAGEOGenerator(self.data_provider,
                                          self.output,
                                          self.common,
                                          menu=True)

    def main_calculation(self):
        """
        This function calculates the KPI results.
        """
        set_names = [
            'Brand Blocking', 'Secondary Displays', 'Brand Pouring',
            'TOUCH POINT', 'Relative Position', 'Activation Standard'
        ]
        total_scores_dict = []

        self.tools.update_templates()
        self.set_templates_data['TOUCH POINT'] = pd.read_excel(
            Const.TEMPLATE_PATH,
            Const.TOUCH_POINT_SHEET_NAME,
            header=Const.TOUCH_POINT_HEADER_ROW)

        # the manufacturer name for DIAGEO is 'Diageo' by default. We need to redefine this for DiageoCO
        self.global_gen.tool_box.DIAGEO = 'DIAGEO'
        assortment_res_dict = self.global_gen.diageo_global_assortment_function_v2(
        )
        self.common_v2.save_json_to_new_tables(assortment_res_dict)
        menus_res_dict = self.global_gen.diageo_global_share_of_menu_cocktail_function(
        )
        self.common_v2.save_json_to_new_tables(menus_res_dict)

        for set_name in set_names:
            set_score = 0

            if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys(
            ):
                self.set_templates_data[
                    set_name] = self.tools.download_template(set_name)

            if set_name == 'Secondary Displays':
                result = self.global_gen.diageo_global_secondary_display_secondary_function(
                )
                total_scores_dict.append(result)
                if result:
                    self.common_v2.write_to_db_result(**result)
                set_score = self.tools.calculate_assortment(
                    assortment_entity='scene_id',
                    location_type='Secondary Shelf')
                self.save_level2_and_level3(set_name, set_name, set_score)

            elif set_name == 'Brand Pouring':
                results_list = self.global_gen.diageo_global_brand_pouring_status_function(
                    self.set_templates_data[set_name])
                total_scores_dict.append(results_list)
                self.save_results_to_db(results_list)
                set_score = self.calculate_brand_pouring_sets(set_name)

            elif set_name == 'Brand Blocking':
                results_list = self.global_gen.diageo_global_block_together(
                    set_name, self.set_templates_data[set_name])
                total_scores_dict.append(results_list)
                self.save_results_to_db(results_list)
                set_score = self.calculate_block_together_sets(set_name)

            elif set_name == 'Relative Position':
                results_list = self.global_gen.diageo_global_relative_position_function(
                    self.set_templates_data[set_name])
                total_scores_dict.append(results_list)
                self.save_results_to_db(results_list)
                set_score = self.calculate_relative_position_sets(set_name)

            elif set_name == 'Activation Standard':

                manufacturer_fk = self.all_products[
                    self.all_products['manufacturer_name'] ==
                    self.DIAGEO_MANUFACTURER]['manufacturer_fk'].values[0]

                results_list = self.global_gen.diageo_global_activation_standard_function(
                    total_scores_dict, self.set_templates_data[set_name],
                    self.store_id, manufacturer_fk)
                for result in results_list['old_tables_level2and3']:
                    self.save_level2_and_level3(result['kpi_set_name'],
                                                result['kpi_name'],
                                                result['score'])
                self.write_to_db_result(
                    results_list['old_tables_level1']['fk'],
                    results_list['old_tables_level1']['score'],
                    results_list['old_tables_level1']['level'])
                self.save_results_to_db(results_list['new_tables_result'])

            elif set_name == 'TOUCH POINT':
                store_attribute = 'additional_attribute_2'
                template = self.set_templates_data[set_name].fillna(
                    method='ffill').set_index(
                        self.set_templates_data[set_name].keys()[0])
                results_list = self.global_gen.diageo_global_touch_point_function(
                    template=template,
                    old_tables=True,
                    new_tables=False,
                    store_attribute=store_attribute)
                total_scores_dict.append(results_list)
                self.save_results_to_db(results_list)
            else:
                return

            if set_score == 0:
                pass
            elif set_score is False:
                return

            # if set_name != 'TOUCH POINT': # we need to do this to prevent duplicate entries in report.kps_results
            #     set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name'] == set_name]['kpi_set_fk'].values[0]
            #     self.write_to_db_result(set_fk, set_score, self.LEVEL1)
        return

    def save_results_to_db(self, results_list):
        if results_list:
            for result in results_list:
                if result is not None:
                    self.common_v2.write_to_db_result(**result)

    def calculate_brand_pouring_sets(self, set_name):
        """
        This function calculates every Brand-Pouring-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.tools.calculate_number_of_scenes(
                    **{self.tools.BRAND_POURING_FIELD: 'Y'}) > 0:
                # 'Pouring' scenes
                result = self.tools.calculate_brand_pouring_status(
                    params.get(self.tools.BRAND_NAME),
                    **{self.tools.BRAND_POURING_FIELD: 'Y'})
            elif self.tools.calculate_number_of_scenes(
                    **{self.tools.BRAND_POURING_FIELD: 'back_bar'}) > 0:
                # 'Back Bar' scenes
                result = self.tools.calculate_brand_pouring_status(
                    params.get(self.tools.BRAND_NAME),
                    **{self.tools.BRAND_POURING_FIELD: 'back_bar'})
            else:
                result = 0
            score = 1 if result else 0
            scores.append(score)

            self.save_level2_and_level3(set_name,
                                        params.get(self.tools.KPI_NAME), score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def calculate_relative_position_sets(self, set_name):
        """
        This function calculates every relative-position-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.store_channel == params.get(self.tools.CHANNEL,
                                                '').upper():
                tested_filters = {
                    'product_ean_code': params.get(self.tools.TESTED)
                }
                anchor_filters = {
                    'product_ean_code': params.get(self.tools.ANCHOR)
                }
                direction_data = {
                    'top':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.TOP_DISTANCE)),
                    'bottom':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.BOTTOM_DISTANCE)),
                    'left':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.LEFT_DISTANCE)),
                    'right':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.RIGHT_DISTANCE))
                }
                general_filters = {
                    'template_name': params.get(self.tools.LOCATION)
                }
                result = self.tools.calculate_relative_position(
                    tested_filters, anchor_filters, direction_data,
                    **general_filters)
                score = 1 if result else 0
                scores.append(score)

                self.save_level2_and_level3(set_name,
                                            params.get(self.tools.KPI_NAME),
                                            score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def _get_direction_for_relative_position(self, value):
        """
        This function converts direction data from the template (as string) to a number.
        """
        if value == self.tools.UNLIMITED_DISTANCE:
            value = 1000
        elif not value or not str(value).isdigit():
            value = 0
        else:
            value = int(value)
        return value

    def calculate_block_together_sets(self, set_name):
        """
        This function calculates every block-together-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.store_channel == params.get(self.tools.CHANNEL,
                                                '').upper():
                filters = {'template_name': params.get(self.tools.LOCATION)}
                if params.get(self.tools.SUB_BRAND_NAME):
                    filters['sub_brand_name'] = params.get(
                        self.tools.SUB_BRAND_NAME)
                else:
                    filters['brand_name'] = params.get(self.tools.BRAND_NAME)
                result = self.tools.calculate_block_together(**filters)
                score = 1 if result else 0
                scores.append(score)

                self.save_level2_and_level3(set_name,
                                            params.get(self.tools.KPI_NAME),
                                            score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def get_match_display(self):
        """
        This function extracts the display matches data and saves it into one global data frame.
        The data is taken from probedata.match_display_in_scene.
        """
        query = DIAGEOQueries.get_match_display(self.session_uid)
        match_display = pd.read_sql_query(query, self.rds_conn.db)
        return match_display

    def save_level2_and_level3(self, set_name, kpi_name, score):
        """
        Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
        """
        kpi_data = self.kpi_static_data[
            (self.kpi_static_data['kpi_set_name'] == set_name)
            & (self.kpi_static_data['kpi_name'] == kpi_name)]
        kpi_fk = kpi_data['kpi_fk'].values[0]
        atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
        self.write_to_db_result(kpi_fk, score, self.LEVEL2)
        self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)

    def write_to_db_result(self, fk, score, level):
        """
        This function the result data frame of every KPI (atomic KPI/KPI/KPI set),
        and appends the insert SQL query into the queries' list, later to be written to the DB.
        """
        attributes = self.create_attributes_dict(fk, score, level)
        if level == self.LEVEL1:
            table = KPS_RESULT
        elif level == self.LEVEL2:
            table = KPK_RESULT
        elif level == self.LEVEL3:
            table = KPI_RESULT
        else:
            return
        query = insert(attributes, table)
        self.kpi_results_queries.append(query)

    def create_attributes_dict(self, fk, score, level):
        """
        This function creates a data frame with all attributes needed for saving in KPI results tables.

        """
        score = round(score, 2)
        if level == self.LEVEL1:
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['kpi_set_fk'] ==
                fk]['kpi_set_name'].values[0]
            score_type = '%' if kpi_set_name in self.tools.KPI_SETS_WITH_PERCENT_AS_SCORE else ''
            attributes = pd.DataFrame(
                [(kpi_set_name, self.session_uid, self.store_id,
                  self.visit_date.isoformat(), format(score,
                                                      '.2f'), score_type, fk)],
                columns=[
                    'kps_name', 'session_uid', 'store_fk', 'visit_date',
                    'score_1', 'score_2', 'kpi_set_fk'
                ])

        elif level == self.LEVEL2:
            kpi_name = self.kpi_static_data[self.kpi_static_data['kpi_fk'] ==
                                            fk]['kpi_name'].values[0].replace(
                                                "'", "\\'")
            attributes = pd.DataFrame(
                [(self.session_uid, self.store_id, self.visit_date.isoformat(),
                  fk, kpi_name, score)],
                columns=[
                    'session_uid', 'store_fk', 'visit_date', 'kpi_fk',
                    'kpk_name', 'score'
                ])
        elif level == self.LEVEL3:
            data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
                                        == fk]
            atomic_kpi_name = data['atomic_kpi_name'].values[0].replace(
                "'", "\\'")
            kpi_fk = data['kpi_fk'].values[0]
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['atomic_kpi_fk'] ==
                fk]['kpi_set_name'].values[0]
            attributes = pd.DataFrame([
                (atomic_kpi_name, self.session_uid,
                 kpi_set_name, self.store_id, self.visit_date.isoformat(),
                 datetime.utcnow().isoformat(), score, kpi_fk, fk, None, None)
            ],
                                      columns=[
                                          'display_text', 'session_uid',
                                          'kps_name', 'store_fk', 'visit_date',
                                          'calculation_time', 'score',
                                          'kpi_fk', 'atomic_kpi_fk',
                                          'threshold', 'result'
                                      ])
        else:
            attributes = pd.DataFrame()
        return attributes.to_dict()

    def commit_results_data(self):
        # self.common.commit_results_data_to_new_tables()
        self.common_v2.commit_results_data()  # new tables

        # old tables
        cur = self.rds_conn.db.cursor()
        delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
            self.session_uid)
        for query in delete_queries:
            cur.execute(query)
        for query in self.kpi_results_queries:
            cur.execute(query)
        # needed to save Touch Point values
        for query in self.common.kpi_results_queries:
            cur.execute(query)

        # this is only needed temporarily until the global assortment function is updated to use the new commonv2 object
        insert_queries = self.common.merge_insert_queries(
            self.common.kpi_results_new_tables_queries)
        for query in insert_queries:
            cur.execute(query)

        self.rds_conn.db.commit()
Ejemplo n.º 12
0
class DIAGEOGRSANDToolBox:
    LEVEL1 = 1
    LEVEL2 = 2
    LEVEL3 = 3

    def __init__(self, data_provider, output):
        self.k_engine = BaseCalculationsScript(data_provider, output)
        self.data_provider = data_provider
        self.project_name = self.data_provider.project_name
        self.session_uid = self.data_provider.session_uid
        self.products = self.data_provider[Data.PRODUCTS]
        self.all_products = self.data_provider[Data.ALL_PRODUCTS]
        self.match_product_in_scene = self.data_provider[Data.MATCHES]
        self.visit_date = self.data_provider[Data.VISIT_DATE]
        self.session_info = self.data_provider[Data.SESSION_INFO]
        self.rds_conn = PSProjectConnector(self.project_name,
                                           DbUsers.CalculationEng)
        self.store_info = self.data_provider[Data.STORE_INFO]
        self.store_channel = self.store_info['store_type'].values[0]
        if self.store_channel:
            self.store_channel = self.store_channel.upper()
        self.store_type = self.store_info['additional_attribute_1'].values[0]
        self.scene_info = self.data_provider[Data.SCENES_INFO]
        self.store_id = self.data_provider[Data.STORE_FK]
        self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
        self.match_display_in_scene = self.get_match_display()
        self.set_templates_data = {}
        self.kpi_static_data = self.get_kpi_static_data()
        self.kpi_results_queries = []
        self.output = output
        self.common = Common(self.data_provider)
        self.commonV2 = CommonV2(self.data_provider)
        self.tools = DIAGEOToolBox(
            self.data_provider,
            output,
            match_display_in_scene=self.match_display_in_scene)
        self.diageo_generator = DIAGEOGenerator(self.data_provider,
                                                self.output,
                                                self.common,
                                                menu=True)

    def get_kpi_static_data(self):
        """
        This function extracts the static KPI data and saves it into one global data frame.
        The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
        """
        query = DIAGEOQueries.get_all_kpi_data()
        kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
        return kpi_static_data

    def get_match_display(self):
        """
        This function extracts the display matches data and saves it into one global data frame.
        The data is taken from probedata.match_display_in_scene.
        """
        query = DIAGEOQueries.get_match_display(self.session_uid)
        match_display = pd.read_sql_query(query, self.rds_conn.db)
        return match_display

    def main_calculation(self, set_names):
        """
        This function calculates the KPI results.
        """
        # Global assortment kpis
        assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2(
        )
        self.commonV2.save_json_to_new_tables(assortment_res_dict)
        menus_res_dict = self.diageo_generator.diageo_global_share_of_menu_cocktail_function(
            cocktail_product_level=True)
        self.commonV2.save_json_to_new_tables(menus_res_dict)
        for set_name in set_names:
            if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys(
            ):
                self.set_templates_data[
                    set_name] = self.tools.download_template(set_name)
            set_score = 0

            if set_name in ('Secondary Displays', 'Secondary'):
                # Global function
                res_json = self.diageo_generator.diageo_global_secondary_display_secondary_function(
                )
                if res_json:
                    # Saving to new tables
                    self.commonV2.write_to_db_result(
                        fk=res_json['fk'],
                        numerator_id=1,
                        denominator_id=self.store_id,
                        result=res_json['result'])

                # Saving to old tables
                set_score = self.tools.calculate_number_of_scenes(
                    location_type='Secondary')
                self.save_level2_and_level3(set_name, set_name, set_score)

            if set_score == 0:
                pass
            elif set_score is False:
                continue

            set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name']
                                          == set_name]['kpi_set_fk'].values[0]
            self.write_to_db_result(set_fk, set_score, self.LEVEL1)
        # committing to new tables
        self.commonV2.commit_results_data()
        return

    def save_level2_and_level3(self, set_name, kpi_name, score):
        """
        Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
        """
        kpi_data = self.kpi_static_data[
            (self.kpi_static_data['kpi_set_name'] == set_name)
            & (self.kpi_static_data['kpi_name'] == kpi_name)]

        kpi_fk = kpi_data['kpi_fk'].values[0]
        atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
        self.write_to_db_result(kpi_fk, score, self.LEVEL2)
        self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)

    def calculate_brand_pouring_sets(self, set_name):
        """
        This function calculates every Brand-Pouring-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.tools.calculate_number_of_scenes(
                    **{self.tools.BRAND_POURING_FIELD: 'Y'}) > 0:
                # 'Pouring' scenes
                result = self.tools.calculate_brand_pouring_status(
                    params.get(self.tools.BRAND_NAME),
                    **{self.tools.BRAND_POURING_FIELD: 'Y'})
            elif self.tools.calculate_number_of_scenes(
                    **{self.tools.BRAND_POURING_FIELD: 'back_bar'}) > 0:
                # 'Back Bar' scenes
                result = self.tools.calculate_brand_pouring_status(
                    params.get(self.tools.BRAND_NAME),
                    **{self.tools.BRAND_POURING_FIELD: 'back_bar'})
            else:
                result = 0
            score = 1 if result else 0
            scores.append(score)

            self.save_level2_and_level3(set_name,
                                        params.get(self.tools.KPI_NAME), score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def calculate_block_together_sets(self, set_name):
        """
        This function calculates every block-together-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.store_channel == params.get(self.tools.CHANNEL,
                                                '').upper():
                filters = {'template_name': params.get(self.tools.LOCATION)}
                if params.get(self.tools.SUB_BRAND_NAME):
                    filters['sub_brand_name'] = params.get(
                        self.tools.SUB_BRAND_NAME)
                else:
                    filters['brand_name'] = params.get(self.tools.BRAND_NAME)
                result = self.tools.calculate_block_together(**filters)
                score = 1 if result else 0
                scores.append(score)

                self.save_level2_and_level3(set_name,
                                            params.get(self.tools.KPI_NAME),
                                            score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def calculate_relative_position_sets(self, set_name):
        """
        This function calculates every relative-position-typed KPI from the relevant sets, and returns
        the set's final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            if self.store_channel == params.get(self.tools.CHANNEL,
                                                '').upper():
                tested_filters = {
                    'product_ean_code': params.get(self.tools.TESTED)
                }
                anchor_filters = {
                    'product_ean_code': params.get(self.tools.ANCHOR)
                }
                direction_data = {
                    'top':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.TOP_DISTANCE)),
                    'bottom':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.BOTTOM_DISTANCE)),
                    'left':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.LEFT_DISTANCE)),
                    'right':
                    self._get_direction_for_relative_position(
                        params.get(self.tools.RIGHT_DISTANCE))
                }
                general_filters = {
                    'template_name': params.get(self.tools.LOCATION)
                }
                result = self.tools.calculate_relative_position(
                    tested_filters, anchor_filters, direction_data,
                    **general_filters)
                score = 1 if result else 0
                scores.append(score)

                self.save_level2_and_level3(set_name,
                                            params.get(self.tools.KPI_NAME),
                                            score)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def _get_direction_for_relative_position(self, value):
        """
        This function converts direction data from the template (as string) to a number.
        """
        if value == self.tools.UNLIMITED_DISTANCE:
            value = 1000
        elif not value or not str(value).isdigit():
            value = 0
        else:
            value = int(value)
        return value

    def calculate_posm_sets(self, set_name):
        """
        This function calculates every POSM-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        appeared_posm = False
        for params in self.set_templates_data[set_name]:
            if self.store_info['store_type'] is None:
                break

            kpi_res = self.tools.calculate_posm(
                display_name=params.get(self.tools.DISPLAY_NAME))
            score = 1 if kpi_res > 0 else 0
            if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
                scores.append(score)

            if score == 1 or params.get(
                    self.store_type) == self.tools.RELEVANT_FOR_STORE:
                self.save_level2_and_level3(
                    set_name, params.get(self.tools.DISPLAY_NAME), score)
                appeared_posm = True

        if scores:
            set_score = (sum(scores) / float(len(scores))) * 100
        elif appeared_posm:
            set_score = 0
        else:
            return False
        return set_score

    def calculate_assortment_sets(self, set_name):
        """
        This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            target = str(params.get(self.store_type, ''))
            if target.isdigit() or target.capitalize() in (
                    self.tools.RELEVANT_FOR_STORE,
                    self.tools.OR_OTHER_PRODUCTS):
                products = str(
                    params.get(self.tools.PRODUCT_EAN_CODE,
                               params.get(self.tools.PRODUCT_EAN_CODE2,
                                          ''))).replace(',', ' ').split()
                target = 1 if not target.isdigit() else int(target)
                kpi_name = params.get(self.tools.GROUP_NAME,
                                      params.get(self.tools.PRODUCT_NAME))
                kpi_static_data = self.kpi_static_data[
                    (self.kpi_static_data['kpi_set_name'] == set_name)
                    & (self.kpi_static_data['kpi_name'] == kpi_name)]
                if len(products) > 1:
                    result = 0
                    for product in products:
                        product_score = self.tools.calculate_assortment(
                            product_ean_code=product)
                        result += product_score
                        try:
                            product_name = \
                                self.all_products[self.all_products['product_ean_code'] == product][
                                    'product_name'].values[
                                    0]
                        except Exception as e:
                            Log.warning(
                                'Product {} is not defined in the DB'.format(
                                    product))
                            continue
                        try:
                            atomic_fk = \
                                kpi_static_data[kpi_static_data['atomic_kpi_name'] == product_name][
                                    'atomic_kpi_fk'].values[
                                    0]
                        except Exception as e:
                            Log.warning(
                                'Product {} is not defined in the DB'.format(
                                    product_name))
                            continue
                        self.write_to_db_result(atomic_fk,
                                                product_score,
                                                level=self.LEVEL3)
                    score = 1 if result >= target else 0
                else:
                    result = self.tools.calculate_assortment(
                        product_ean_code=products)
                    atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
                    score = 1 if result >= target else 0
                    self.write_to_db_result(atomic_fk,
                                            score,
                                            level=self.LEVEL3)

                scores.append(score)
                kpi_fk = kpi_static_data['kpi_fk'].values[0]
                self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    # def calculate_sos_sets(self, set_name):
    #     """
    #     This function calculates every SOS-typed KPI from the relevant sets, and returns the set final score.
    #     """
    #     scores = []
    #     for params in self.set_templates_data[set_name]:
    #         if params.get(self.store_type) == self.tools.RELEVANT_FOR_STORE:
    #             result = self.tools.calculate_share_of_shelf(manufacturer=self.tools.DIAGEO,
    #                                                          include_empty=self.tools.EXCLUDE_EMPTY)
    #             score = 1 if result >= params.get(self.tools.TARGET) else 0
    #             scores.append(score)
    #
    #             self.save_level2_and_level3(set_name, params.get(self.tools.KPI_NAME), score)
    #
    #     set_score = (sum(scores) / float(len(scores))) * 100
    #     return set_score

    def write_to_db_result(self, fk, score, level):
        """
        This function the result data frame of every KPI (atomic KPI/KPI/KPI set),
        and appends the insert SQL query into the queries' list, later to be written to the DB.
        """
        attributes = self.create_attributes_dict(fk, score, level)
        if level == self.LEVEL1:
            table = KPS_RESULT
        elif level == self.LEVEL2:
            table = KPK_RESULT
        elif level == self.LEVEL3:
            table = KPI_RESULT
        else:
            return
        query = insert(attributes, table)
        self.kpi_results_queries.append(query)

    def create_attributes_dict(self, fk, score, level):
        """
        This function creates a data frame with all attributes needed for saving in KPI results tables.

        """
        score = round(score, 2)
        if level == self.LEVEL1:
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['kpi_set_fk'] ==
                fk]['kpi_set_name'].values[0]
            score_type = '%' if kpi_set_name in self.tools.KPI_SETS_WITH_PERCENT_AS_SCORE else ''
            attributes = pd.DataFrame(
                [(kpi_set_name, self.session_uid, self.store_id,
                  self.visit_date.isoformat(), format(score,
                                                      '.2f'), score_type, fk)],
                columns=[
                    'kps_name', 'session_uid', 'store_fk', 'visit_date',
                    'score_1', 'score_2', 'kpi_set_fk'
                ])

        elif level == self.LEVEL2:
            kpi_name = self.kpi_static_data[self.kpi_static_data['kpi_fk'] ==
                                            fk]['kpi_name'].values[0].replace(
                                                "'", "\\'")
            attributes = pd.DataFrame(
                [(self.session_uid, self.store_id, self.visit_date.isoformat(),
                  fk, kpi_name, score)],
                columns=[
                    'session_uid', 'store_fk', 'visit_date', 'kpi_fk',
                    'kpk_name', 'score'
                ])
        elif level == self.LEVEL3:
            data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk']
                                        == fk]
            atomic_kpi_name = data['atomic_kpi_name'].values[0].replace(
                "'", "\\'")
            kpi_fk = data['kpi_fk'].values[0]
            kpi_set_name = self.kpi_static_data[
                self.kpi_static_data['atomic_kpi_fk'] ==
                fk]['kpi_set_name'].values[0]
            attributes = pd.DataFrame([
                (atomic_kpi_name, self.session_uid,
                 kpi_set_name, self.store_id, self.visit_date.isoformat(),
                 datetime.utcnow().isoformat(), score, kpi_fk, fk, None, None)
            ],
                                      columns=[
                                          'display_text', 'session_uid',
                                          'kps_name', 'store_fk', 'visit_date',
                                          'calculation_time', 'score',
                                          'kpi_fk', 'atomic_kpi_fk',
                                          'threshold', 'result'
                                      ])
        else:
            attributes = pd.DataFrame()
        return attributes.to_dict()

    @log_runtime('Saving to DB')
    def commit_results_data(self):
        """
        This function writes all KPI results to the DB, and commits the changes.
        """
        cur = self.rds_conn.db.cursor()
        delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(
            self.session_uid)
        for query in delete_queries:
            cur.execute(query)
        for query in self.kpi_results_queries:
            cur.execute(query)
        self.rds_conn.db.commit()
Ejemplo n.º 13
0
class DIAGEOGTRToolBox:
    LEVEL1 = 1
    LEVEL2 = 2
    LEVEL3 = 3
    ACTIVATION_STANDARD = 'Activation Standard'

    def __init__(self, data_provider, output):
        self.output = output
        self.data_provider = data_provider
        self.project_name = self.data_provider.project_name
        self.session_uid = self.data_provider.session_uid
        self.products = self.data_provider[Data.PRODUCTS]
        self.all_products = self.data_provider[Data.ALL_PRODUCTS]
        self.match_product_in_scene = self.data_provider[Data.MATCHES]
        self.visit_date = self.data_provider[Data.VISIT_DATE]
        self.session_info = self.data_provider[Data.SESSION_INFO]
        self.scene_info = self.data_provider[Data.SCENES_INFO]
        self.store_id = self.data_provider[Data.STORE_FK]
        self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
        self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
        self.set_templates_data = {}
        self.store_info = self.data_provider[Data.STORE_INFO]
        self.match_display_in_scene = self.get_match_display()
        self.store_type = self.store_info['additional_attribute_1'].values[0]
        self.kpi_static_data = self.get_kpi_static_data()
        self.output = output
        self.common = Common(self.data_provider)
        self.commonV2 = CommonV2(self.data_provider)
        self.tools = DIAGEOToolBox(self.data_provider, output,
                                   match_display_in_scene=self.match_display_in_scene)  # replace the old one
        self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common)

        self.kpi_results_queries = []

        self.scores = {self.LEVEL1: {},
                       self.LEVEL2: {},
                       self.LEVEL3: {}}

    def get_kpi_static_data(self):
        """
        This function extracts the static KPI data and saves it into one global data frame.
        The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
        """
        query = DIAGEOQueries.get_all_kpi_data()
        kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
        return kpi_static_data

    def get_match_display(self):
        """
        This function extracts the display matches data and saves it into one global data frame.
        The data is taken from probedata.match_display_in_scene.
        """
        query = DIAGEOQueries.get_match_display(self.session_uid)
        match_display = pd.read_sql_query(query, self.rds_conn.db)
        return match_display

    def main_calculation(self, set_names):
        """
        This function calculates the KPI results.
        # """
        assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2()

        self.commonV2.save_json_to_new_tables(assortment_res_dict)

        # if set_name not in self.tools.KPI_SETS_WITHOUT_A_TEMPLATE and set_name not in self.set_templates_data.keys():
        #     self.set_templates_data[set_name] = self.tools.download_template(set_name)
        #
        # if set_name in ('Local MPA', 'MPA', 'New Products',):
        #     set_score = self.calculate_assortment_sets(set_name)
        # else:
        #     return
        # if set_score is False:
        #     return
        # set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name'] == set_name]['kpi_set_fk'].values[0]
        # self.write_to_db_result(set_fk, set_score, self.LEVEL1)

        self.commonV2.commit_results_data()
        return

    def calculate_assortment_sets(self, set_name):
        """
        This function calculates every Assortment-typed KPI from the relevant sets, and returns the set final score.
        """
        scores = []
        for params in self.set_templates_data[set_name]:
            target = str(params.get(self.store_type, ''))
            if target.isdigit() or target.capitalize() in (self.tools.RELEVANT_FOR_STORE, self.tools.OR_OTHER_PRODUCTS):
                products = str(params.get(self.tools.PRODUCT_EAN_CODE,
                                          params.get(self.tools.PRODUCT_EAN_CODE2, ''))).replace(',', ' ').split()
                target = 1 if not target.isdigit() else int(target)
                kpi_name = params.get(self.tools.GROUP_NAME, params.get(self.tools.PRODUCT_NAME))
                kpi_static_data = self.kpi_static_data[(self.kpi_static_data['kpi_set_name'] == set_name) &
                                                       (self.kpi_static_data['kpi_name'] == kpi_name)]
                if len(products) > 1:
                    result = 0
                    for product in products:
                        product_score = self.tools.calculate_assortment(product_ean_code=product)
                        result += product_score
                        try:
                            product_name = \
                                self.all_products[self.all_products['product_ean_code'] == product][
                                    'product_name'].values[
                                    0]
                        except Exception as e:
                            Log.warning('Product {} is not defined in the DB'.format(product))
                            continue
                        try:
                            atomic_fk = \
                                kpi_static_data[kpi_static_data['atomic_kpi_name'] == product_name][
                                    'atomic_kpi_fk'].values[
                                    0]
                        except Exception as e:
                            Log.warning('Product {} is not defined in the DB'.format(product_name))
                            continue
                        self.write_to_db_result(atomic_fk, product_score, level=self.LEVEL3)
                    score = 1 if result >= target else 0
                else:
                    result = self.tools.calculate_assortment(product_ean_code=products)
                    atomic_fk = kpi_static_data['atomic_kpi_fk'].values[0]
                    score = 1 if result >= target else 0
                    self.write_to_db_result(atomic_fk, score, level=self.LEVEL3)

                scores.append(score)
                kpi_fk = kpi_static_data['kpi_fk'].values[0]
                self.write_to_db_result(kpi_fk, score, level=self.LEVEL2)

        if not scores:
            return False
        set_score = (sum(scores) / float(len(scores))) * 100
        return set_score

    def calculate_activation_standard(self):
        """
        This function calculates the Activation Standard KPI, and saves the result to the DB (for all 3 levels).
        """
        final_score = 0
        for params in self.tools.download_template(self.ACTIVATION_STANDARD):
            set_name = params.get(self.tools.ACTIVATION_SET_NAME)
            kpi_name = params.get(self.tools.ACTIVATION_KPI_NAME)
            target = float(params.get(self.tools.ACTIVATION_TARGET))
            target = target * 100 if target < 1 else target
            score_type = params.get(self.tools.ACTIVATION_SCORE)
            weight = float(params.get(self.tools.ACTIVATION_WEIGHT))
            if kpi_name:
                kpi_fk = self.kpi_static_data[(self.kpi_static_data['kpi_set_name'] == set_name) &
                                              (self.kpi_static_data['kpi_name'] == kpi_name)]['kpi_fk'].values[0]
                score = self.scores[self.LEVEL2].get(kpi_fk, 0)
            else:
                set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name'] == set_name]['kpi_set_fk'].values[0]
                score = self.scores[self.LEVEL1].get(set_fk, 0)
            if score >= target:
                score = 100
            else:
                if score_type == 'PROPORTIONAL':
                    score = (score / float(target)) * 100
                else:
                    score = 0
            final_score += score * weight
            self.save_level2_and_level3(self.ACTIVATION_STANDARD, set_name, score)
        set_fk = self.kpi_static_data[self.kpi_static_data['kpi_set_name'] ==
                                      self.ACTIVATION_STANDARD]['kpi_set_fk'].values[0]
        self.write_to_db_result(set_fk, final_score, self.LEVEL1)

    def write_to_db_result(self, fk, score, level):
        """
        This function creates the result data frame of every KPI (atomic KPI/KPI/KPI set),
        and appends the insert SQL query into the queries' list, later to be written to the DB.
        """
        attributes = self.create_attributes_dict(fk, score, level)
        if level == self.LEVEL1:
            table = KPS_RESULT
        elif level == self.LEVEL2:
            table = KPK_RESULT
        elif level == self.LEVEL3:
            table = KPI_RESULT
        else:
            return
        query = insert(attributes, table)
        self.kpi_results_queries.append(query)

    def create_attributes_dict(self, fk, score, level):
        """
        This function creates a data frame with all attributes needed for saving in KPI results tables.

        """
        if level == self.LEVEL1:
            kpi_set_name = self.kpi_static_data[self.kpi_static_data['kpi_set_fk'] == fk]['kpi_set_name'].values[0]
            attributes = pd.DataFrame([(kpi_set_name, self.session_uid, self.store_id, self.visit_date.isoformat(),
                                        format(score, '.2f'), fk)],
                                      columns=['kps_name', 'session_uid', 'store_fk', 'visit_date', 'score_1',
                                               'kpi_set_fk'])
        elif level == self.LEVEL2:
            kpi_name = self.kpi_static_data[self.kpi_static_data['kpi_fk'] == fk]['kpi_name'].values[0].replace("'", "\\'")
            attributes = pd.DataFrame([(self.session_uid, self.store_id, self.visit_date.isoformat(),
                                        fk, kpi_name, score)],
                                      columns=['session_uid', 'store_fk', 'visit_date', 'kpi_fk', 'kpk_name', 'score'])
        elif level == self.LEVEL3:
            data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk'] == fk]
            atomic_kpi_name = data['atomic_kpi_name'].values[0].replace("'", "\\'")
            kpi_fk = data['kpi_fk'].values[0]
            kpi_set_name = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk'] == fk]['kpi_set_name'].values[0]
            attributes = pd.DataFrame([(atomic_kpi_name, self.session_uid, kpi_set_name, self.store_id,
                                        self.visit_date.isoformat(), datetime.utcnow().isoformat(),
                                        score, kpi_fk, fk)],
                                      columns=['display_text', 'session_uid', 'kps_name', 'store_fk', 'visit_date',
                                               'calculation_time', 'score', 'kpi_fk', 'atomic_kpi_fk'])
        else:
            attributes = pd.DataFrame()
        return attributes.to_dict()

    def save_level2_and_level3(self, set_name, kpi_name, score):
        """
        Given KPI data and a score, this functions writes the score for both KPI level 2 and 3 in the DB.
        """
        kpi_data = self.kpi_static_data[(self.kpi_static_data['kpi_set_name'] == set_name) &
                                        (self.kpi_static_data['kpi_name'] == kpi_name)]
        kpi_fk = kpi_data['kpi_fk'].values[0]
        atomic_kpi_fk = kpi_data['atomic_kpi_fk'].values[0]
        self.write_to_db_result(kpi_fk, score, self.LEVEL2)
        self.write_to_db_result(atomic_kpi_fk, score, self.LEVEL3)

    @log_runtime('Saving to DB')
    def commit_results_data(self):
        """
        This function writes all KPI results to the DB, and commits the changes.
        """
        insert_queries = self.merge_insert_queries(self.kpi_results_queries)
        cur = self.rds_conn.db.cursor()
        delete_queries = DIAGEOQueries.get_delete_session_results_query_old_tables(self.session_uid)
        for query in delete_queries:
            cur.execute(query)
        for query in insert_queries:
            cur.execute(query)
        self.rds_conn.db.commit()

    @staticmethod
    def merge_insert_queries(insert_queries):
        query_groups = {}
        for query in insert_queries:
            static_data, inserted_data = query.split('VALUES ')
            if static_data not in query_groups:
                query_groups[static_data] = []
            query_groups[static_data].append(inserted_data)
        merged_queries = []
        for group in query_groups:
            merged_queries.append('{0} VALUES {1}'.format(group, ',\n'.join(query_groups[group])))
        return merged_queries