class CCAAUToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 EXCLUDE_FILTER = 0 INCLUDE_FILTER = 1 def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(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.templates = self.data_provider[Data.TEMPLATES] 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.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.template = self.data_provider.all_templates # templates self.kpi_static_data = self.common.get_new_kpi_static_data() self.toolbox = GENERALToolBox(data_provider) kpi_path = os.path.dirname(os.path.realpath(__file__)) base_file = os.path.basename(kpi_path) self.exclude_filters = pd.read_excel(os.path.join( kpi_path[:-len(base_file)], 'Data', 'template.xlsx'), sheetname="Exclude") self.Include_filters = pd.read_excel(os.path.join( kpi_path[:-len(base_file)], 'Data', 'template.xlsx'), sheetname="Include") self.bay_count_kpi = pd.read_excel(os.path.join( kpi_path[:-len(base_file)], 'Data', 'template.xlsx'), sheetname="BayCountKPI") def main_calculation(self): """ This function calculates the KPI results. """ self.calculate_sos() self.calculate_bay_kpi() self.common.commit_results_data_to_new_tables() def calculate_sos(self): """ This function filtering Data frame - "scene item facts" by the parameters in the template. Sending the filtered data frames to linear Sos calculation and facing Sos calculation Writing the results to the new tables in DB """ facing_kpi_fk = self.kpi_static_data[ self.kpi_static_data['client_name'] == 'FACINGS_SOS_SCENE_TYPE_BY_MANUFACTURER']['pk'].iloc[0] linear_kpi_fk = self.kpi_static_data[ self.kpi_static_data['client_name'] == 'LINEAR_SOS_SCENE_TYPE_BY_MANUFACTURER']['pk'].iloc[0] den_facing_exclude_template = self.exclude_filters[ (self.exclude_filters['KPI'] == 'Share of Shelf by Facing') & (self.exclude_filters['apply on'] == 'Denominator')] den_linear_exclude_template = self.exclude_filters[ (self.exclude_filters['KPI'] == 'Share of Shelf by Linear') & (self.exclude_filters['apply on'] == 'Denominator')] num_facing_exclude_template = self.exclude_filters[ (self.exclude_filters['KPI'] == 'Share of Shelf by Facing') & (self.exclude_filters['apply on'] == 'Numerator')] num_linear_exclude_template = self.exclude_filters[ (self.exclude_filters['KPI'] == 'Share of Shelf by Linear') & (self.exclude_filters['apply on'] == 'Numerator')] scene_templates = self.scif['template_fk'].unique().tolist() scene_manufactures = self.scif['manufacturer_fk'].unique().tolist() # exclude filters denominator den_general_facing_filters = self.create_dict_filters( den_facing_exclude_template, self.EXCLUDE_FILTER) den_general_linear_filters = self.create_dict_filters( den_linear_exclude_template, self.EXCLUDE_FILTER) # exclude filters numerator num_general_facing_filters = self.create_dict_filters( num_facing_exclude_template, self.EXCLUDE_FILTER) num_general_linear_filters = self.create_dict_filters( num_linear_exclude_template, self.EXCLUDE_FILTER) df_num_fac = self.filter_2_cond(self.scif, num_facing_exclude_template) df_num_lin = self.filter_2_cond(self.scif, num_linear_exclude_template) df_den_lin = self.filter_2_cond(self.scif, den_facing_exclude_template) df_den_fac = self.filter_2_cond(self.scif, den_linear_exclude_template) for template in scene_templates: for manufacture in scene_manufactures: sos_filters = { "template_fk": (template, self.INCLUDE_FILTER), "manufacturer_fk": (manufacture, self.INCLUDE_FILTER) } tem_filters = {"template_fk": (template, self.INCLUDE_FILTER)} dict_num_facing = dict( (k, v) for d in [sos_filters, num_general_facing_filters] for k, v in d.items()) numerator_facings = self.calculate_share_space( df_num_fac, dict_num_facing)[0] dict_num_linear = dict( (k, v) for d in [sos_filters, num_general_linear_filters] for k, v in d.items()) numerator_linear = self.calculate_share_space( df_num_lin, dict_num_linear)[1] dict_den_facing = dict( (k, v) for d in [tem_filters, den_general_facing_filters] for k, v in d.items()) denominator_facings = self.calculate_share_space( df_den_fac, dict_den_facing)[0] dict_den_linear = dict( (k, v) for d in [tem_filters, den_general_linear_filters] for k, v in d.items()) denominator_linear = self.calculate_share_space( df_den_lin, dict_den_linear)[1] score_facing = 0 if denominator_facings == 0 else ( numerator_facings / denominator_facings) * 100 score_linear = 0 if denominator_linear == 0 else ( numerator_linear / denominator_linear) * 100 self.common.write_to_db_result_new_tables( facing_kpi_fk, manufacture, numerator_facings, score_facing, template, denominator_facings, score_facing) self.common.write_to_db_result_new_tables( linear_kpi_fk, manufacture, numerator_linear, score_linear, template, denominator_linear, score_linear) def create_dict_filters(self, template, param): """ :param template : Template of the desired filtering to data frame :param param : exclude /include :return: Dictionary of filters and parameter : exclude / include by demeaned """ filters_dict = {} template_without_second = template[template['Param 2'].isnull()] for row in template_without_second.iterrows(): filters_dict[row[1]['Param 1']] = (row[1]['Value 1'].split(','), param) return filters_dict def filter_2_cond(self, data_frame, template): """ :param template: Template of the desired filtering :param data_frame : Data frame :return: data frame filtered by entries in the template with 2 conditions """ template_without_second = template[template['Param 2'].notnull()] if template_without_second is not None: for row in template_without_second.iterrows(): data_frame = data_frame.loc[ (~data_frame[row[1]['Param 1']].isin(row[1]['Value 1']. split(','))) | (~data_frame[row[1]['Param 2']].isin(row[1]['Value 2']. split(',')))] return data_frame def calculate_share_space(self, data_frame, filters): """ :param filters: These are the parameters which the data frame is filtered by. :param data_frame : relevant scene item facts data frame (filtered ) :return: The total number of facings and the shelf width (in mm) according to the filters. """ filtered_scif = data_frame[self.toolbox.get_filter_condition( data_frame, **filters)] sum_of_facings = filtered_scif['facings'].sum() space_length = filtered_scif['gross_len_split_stack'].sum() return sum_of_facings, space_length def calculate_bay_kpi(self): bay_kpi_sheet = self.bay_count_kpi kpi = self.kpi_static_data.loc[self.kpi_static_data['type'] == BAY_COUNT_KPI] if kpi.empty: Log.info("CCAAU Calculate KPI Name:{} not found in DB".format( BAY_COUNT_KPI)) else: Log.info("CCAAU Calculate KPI Name:{} found in DB".format( BAY_COUNT_KPI)) bay_kpi_row = bay_kpi_sheet[bay_kpi_sheet['KPI Name'] == BAY_COUNT_KPI] if not bay_kpi_row.empty: scene_types_to_consider = bay_kpi_row['Scene Type'].iloc[0] if scene_types_to_consider == '*': # Consider all scene types scene_types_to_consider = 'all' else: scene_types_to_consider = [ x.strip() for x in scene_types_to_consider.split(',') ] mpis_with_scene = self.match_product_in_scene.merge( self.scene_info, how='left', on='scene_fk') mpis_with_scene_and_template = mpis_with_scene.merge( self.templates, how='left', on='template_fk') if scene_types_to_consider != 'all': mpis_with_scene_and_template = mpis_with_scene_and_template[ mpis_with_scene_and_template['template_name'].isin( scene_types_to_consider)] mpis_template_group = mpis_with_scene_and_template.groupby( 'template_fk') for template_fk, template_data in mpis_template_group: Log.info("Running for template ID {templ_id}".format( templ_id=template_fk, )) total_bays_for_scene_type = 0 scene_group = template_data.groupby('scene_fk') for scene_fk, scene_data in scene_group: Log.info( "KPI Name:{kpi} bay count is {bay_c} for scene ID {scene_id}" .format( kpi=BAY_COUNT_KPI, bay_c=int(scene_data['bay_number'].max()), scene_id=scene_fk, )) total_bays_for_scene_type += int( scene_data['bay_number'].max()) Log.info( "KPI Name:{kpi} total bay count is {bay_c} for template ID {templ_id}" .format( kpi=BAY_COUNT_KPI, bay_c=total_bays_for_scene_type, templ_id=template_fk, )) self.common.write_to_db_result_new_tables( fk=int(kpi['pk'].iloc[0]), numerator_id=int(template_fk), numerator_result=total_bays_for_scene_type, denominator_id=int(self.store_id), denominator_result=total_bays_for_scene_type, result=total_bays_for_scene_type, )
class CCPHLToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(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.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.template_data = pd.read_excel(TEMPLATE_PATH, 'KPIs').fillna('') self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_new_kpi_static_data() self.kpi_static_data = self.kpi_static_data[self.kpi_static_data['kpi_family_fk'] == CCPHLConsts.CUSTOM_KPIS] self.kpi_results_queries = [] self.mapping_param = {"manufacturer": "manufacturer_name"} self.mapping_entity = {"manufacturer": "manufacturer_fk", "store": "store_id", "scene_type": "template_fk", "brand": "brand_fk", "product": "product_fk"} def main_calculation(self): """ This function calculates the KPI results. """ for group in self.template_data[CCPHLConsts.KPI_GROUP].unique(): kpis = self.template_data[self.template_data[CCPHLConsts.KPI_GROUP] == group] if kpis.empty: print("KPI Group:{} is not valid".format(group)) continue for row_num, kpi in kpis.iterrows(): if kpi[CCPHLConsts.KPI_GROUP] == CCPHLConsts.SHELF_PURITY: self.calculate_self_purity_util(kpi) elif kpi[CCPHLConsts.KPI_GROUP] == CCPHLConsts.SHELF_CUTIL: self.calculate_self_purity_util(kpi) else: continue self.common.commit_results_data_to_new_tables() def calculate_self_purity_util(self, kpi): df_scene_data = self.scif if df_scene_data.empty: return scene_types = [x.strip() for x in kpi[CCPHLConsts.SCENE_TYPE].split(',')] filter_param_name_1 = kpi['filter_param_name_1'].strip() if len(filter_param_name_1) != 0: filter_param_value_1 = kpi['filter_param_value_1'].strip() else: filter_param_value_1 = "" if str(kpi[CCPHLConsts.EXCLUDE_EMPTY]).upper() == 'Y': df_scene_data = df_scene_data[df_scene_data[CCPHLConsts.PRODUCT_FK] != CCPHLConsts.EMPTY] df_scene_data = df_scene_data[df_scene_data[CCPHLConsts.PRODUCT_FK] != CCPHLConsts.GENERAL_EMPTY] if str(kpi[CCPHLConsts.EXCLUDE_IRRELEVANT]).upper() == 'Y': df_scene_data = df_scene_data[df_scene_data[CCPHLConsts.IRRELEVANT] != CCPHLConsts.IRRELEVANT] df_kpi_level_2_fk = self.kpi_static_data[self.kpi_static_data['type'] == kpi['kpi_name']] if df_kpi_level_2_fk.empty: kpi_level_2_fk = 0 else: kpi_level_2_fk = df_kpi_level_2_fk.iloc[0]['pk'] df_scene_data = df_scene_data[df_scene_data['template_name'].isin(scene_types)] group_list = [] for idx in range(1, 5): entity = kpi['entity' + str(idx)].strip() if entity == 'N/A' or len(entity) == 0: continue else: entity = self.mapping_entity[kpi['entity' + str(idx)].strip()] group_list.append(entity) denominator = 0 if group_list[0] == 'store_id': total_facings = df_scene_data['facings'].sum() denominator = float(total_facings) if len(filter_param_value_1) != 0: df_scene_data2 = df_scene_data[df_scene_data[self.mapping_param[filter_param_name_1]]==filter_param_value_1] else: df_scene_data2 = df_scene_data filter_columns = list(group_list) filter_columns.append('facings') df_scene_data2 = df_scene_data2[filter_columns] df_purity = pd.DataFrame(df_scene_data2.groupby(group_list).sum().reset_index()) store_zero_results = str(kpi[CCPHLConsts.STORE_ZERO_RESULTS]).strip().upper() for row_num, row_data in df_purity.iterrows(): numerator = row_data['facings'] if group_list[0] == 'template_fk': df_scene_count = df_scene_data[df_scene_data['template_fk'] == row_data['template_fk']] if df_scene_count.empty: total_facings = 0 else: total_facings = df_scene_count['facings'].sum() denominator = float(total_facings) try: result = round(float(numerator) / float(denominator), 4) except ZeroDivisionError: print("Error: {}".format(ZeroDivisionError.message)) continue if kpi_level_2_fk != 0: if result == 0: if store_zero_results == 'Y': self.common.write_to_db_result_new_tables(fk=kpi_level_2_fk, numerator_id=row_data[group_list[len(group_list)-1]], denominator_id=row_data[group_list[0]], numerator_result=numerator, denominator_result=denominator, result=result, score=result) else: self.common.write_to_db_result_new_tables(fk=kpi_level_2_fk, numerator_id=row_data[group_list[len(group_list) - 1]], denominator_id=row_data[group_list[0]], numerator_result=numerator, denominator_result=denominator, result=result, score=result)
class PERFETTICNToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(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.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_new_kpi_static_data() self.kpi_results_queries = [] self.store_info = self.data_provider[Data.STORE_INFO] self.assortment = Assortment(self.data_provider, self.output) self.template = self.data_provider.all_templates def main_calculation(self, *args, **kwargs): self.display_count() self.assortment_calculation() self.common.commit_results_data_to_new_tables() def display_count(self): """This function calculates how many displays find from types secondary shelf """ num_brands = {} display_info = self.scif['template_fk'] display_fks = display_info.unique() template_seco = self.template[ self.template['included_in_secondary_shelf_report'] == 'Y']['template_fk'] display_fks = list( filter(lambda x: x in template_seco.values, display_fks)) count_fk = self.kpi_static_data[self.kpi_static_data['client_name'] == 'COUNT OF DISPLAY']['pk'].iloc[0] for value in display_fks: num_brands[value] = display_info[display_info == value].count() score = num_brands[value] self.common.write_to_db_result_new_tables(count_fk, value, None, score, score, score, score) return def assortment_calculation(self): """ This function calculates 3 levels of assortment : level3 is assortment SKU level2 is assortment groups level1 how many groups passed out of all """ lvl3_result = self.assortment.calculate_lvl3_assortment() for result in lvl3_result.itertuples(): score = result.in_store if score >= 1: score = 100 self.common.write_to_db_result_new_tables( result.kpi_fk_lvl3, result.product_fk, result.in_store, score, result.assortment_group_fk, 1, score) if not lvl3_result.empty: lvl2_result = self.assortment.calculate_lvl2_assortment( lvl3_result) for result in lvl2_result.itertuples(): denominator_res = result.total res = np.divide(float(result.passes), float(denominator_res)) if result.passes >= 1: score = 100 else: score = 0 self.common.write_to_db_result_new_tables( result.kpi_fk_lvl2, result.assortment_group_fk, result.passes, (res * 100), result.assortment_super_group_fk, denominator_res, score) if not lvl2_result.empty: lvl1_result = self.assortment.calculate_lvl1_assortment( lvl2_result) for result in lvl1_result.itertuples(): denominator_res = result.total res = np.divide(float(result.passes), float(denominator_res)) if res >= 0: score = 100 else: score = 0 self.common.write_to_db_result_new_tables( fk=result.kpi_fk_lvl1, numerator_id=result.assortment_super_group_fk, numerator_result=result.passes, denominator_result=denominator_res, result=(res * 100), score=score) return
class HEINEKENTWToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 DIST_STORE_LVL1 = 1014 OOS_STORE_LVL1 = 1015 DIST_STORE_LVL2 = 1016 OOS_STORE_LVL2 = 1017 DIST_CATEGORY_LVL1 = 1018 OOS_CATEGORY_LVL1 = 1019 DIST_CATEGORY_LVL2 = 1020 OOS_CATEGORY_LVL2 = 1021 DISTRIBUTION = 4 OOS = 5 MANUFACTURER_FK = 175 # heinken manfucturer def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(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.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_new_kpi_static_data() self.kpi_results_queries = [] self.assortment = Assortment(self.data_provider, self.output) def main_calculation(self, *args, **kwargs): """ This function calculates the KPI results. """ lvl3_result = self.assortment.calculate_lvl3_assortment() self.category_assortment_calculation(lvl3_result) self.store_assortment_calculation(lvl3_result) self.common.commit_results_data_to_new_tables() # self.common.commit_results_data_to_new_tables() def category_assortment_calculation(self, lvl3_result): """ This function calculates 3 levels of assortment : level3 is assortment SKU level2 is assortment groups """ if not lvl3_result.empty: # cat_df = self.scif[['product_fk', 'category_fk']] cat_df = self.all_products[['product_fk', 'category_fk']] lvl3_with_cat = lvl3_result.merge(cat_df, on='product_fk', how='left') lvl3_with_cat = lvl3_with_cat[ lvl3_with_cat['category_fk'].notnull()] for result in lvl3_with_cat.itertuples(): if result.in_store == 1: score = self.DISTRIBUTION else: score = self.OOS # Distrubtion self.common.write_to_db_result_new_tables( fk=self.DIST_CATEGORY_LVL1, numerator_id=result.product_fk, numerator_result=score, result=score, denominator_id=result.category_fk, denominator_result=1, score=score, score_after_actions=score) if score == self.OOS: # OOS self.common.write_to_db_result_new_tables( fk=self.OOS_CATEGORY_LVL1, numerator_id=result.product_fk, numerator_result=score, result=score, denominator_id=result.category_fk, denominator_result=1, score=score, score_after_actions=score) category_list = lvl3_with_cat['category_fk'].unique() for cat in category_list: lvl3_result_cat = lvl3_with_cat[lvl3_with_cat["category_fk"] == cat] lvl2_result = self.assortment.calculate_lvl2_assortment( lvl3_result_cat) for result in lvl2_result.itertuples(): denominator_res = result.total res = np.divide(float(result.passes), float(denominator_res)) # Distrubtion self.common.write_to_db_result_new_tables( fk=self.DIST_CATEGORY_LVL2, numerator_id=self.MANUFACTURER_FK, numerator_result=result.passes, denominator_id=cat, denominator_result=denominator_res, result=res, score=res, score_after_actions=res) # OOS self.common.write_to_db_result_new_tables( fk=self.OOS_CATEGORY_LVL2, numerator_id=self.MANUFACTURER_FK, numerator_result=denominator_res - result.passes, denominator_id=cat, denominator_result=denominator_res, result=1 - res, score=(1 - res), score_after_actions=1 - res) return def store_assortment_calculation(self, lvl3_result): """ This function calculates the KPI results. """ for result in lvl3_result.itertuples(): if result.in_store == 1: score = self.DISTRIBUTION else: score = self.OOS # Distrubtion self.common.write_to_db_result_new_tables( fk=self.DIST_STORE_LVL1, numerator_id=result.product_fk, numerator_result=score, result=score, denominator_id=self.store_id, denominator_result=1, score=score) if score == self.OOS: # OOS self.common.write_to_db_result_new_tables( fk=self.OOS_STORE_LVL1, numerator_id=result.product_fk, numerator_result=score, result=score, denominator_id=self.store_id, denominator_result=1, score=score, score_after_actions=score) if not lvl3_result.empty: lvl2_result = self.assortment.calculate_lvl2_assortment( lvl3_result) for result in lvl2_result.itertuples(): denominator_res = result.total if not pd.isnull(result.target) and not pd.isnull( result.group_target_date ) and result.group_target_date <= self.assortment.current_date: denominator_res = result.target res = np.divide(float(result.passes), float(denominator_res)) # Distrubtion self.common.write_to_db_result_new_tables( fk=self.DIST_STORE_LVL2, numerator_id=self.MANUFACTURER_FK, denominator_id=self.store_id, numerator_result=result.passes, denominator_result=denominator_res, result=res, score=res, score_after_actions=res) # OOS self.common.write_to_db_result_new_tables( fk=self.OOS_STORE_LVL2, numerator_id=self.MANUFACTURER_FK, numerator_result=denominator_res - result.passes, denominator_id=self.store_id, denominator_result=denominator_res, result=1 - res, score=1 - res, score_after_actions=1 - res) return
class SOLARBRToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 EXCLUDE_EMPTY = False EXCLUDE_FILTER = 0 EMPTY = 'Empty' def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.k_engine = BaseCalculationsGroup(data_provider, output) 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.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.templates = {} self.session_id = self.data_provider.session_id self.score_templates = {} self.get_templates() self.get_score_template() self.manufacturer_fk = self.all_products[ self.all_products['manufacturer_name'] == 'Coca Cola'].iloc[0] self.sos = SOS(self.data_provider, self.output) self.total_score = 0 self.session_fk = self.data_provider[Data.SESSION_INFO]['pk'].iloc[0] self.toolbox = GENERALToolBox(self.data_provider) self.scenes_info = self.data_provider[Data.SCENES_INFO] self.kpi_results_new_tables_queries = [] # self.store_type = self.data_provider.store_type def get_templates(self): for sheet in Const.SHEETS_MAIN: self.templates[sheet] = pd.read_excel(MAIN_TEMPLATE_PATH, sheetname=sheet.decode("utf-8"), keep_default_na=False) def get_score_template(self): for sheet in Const.SHEETS_SCORE: self.score_templates[sheet] = pd.read_excel(SCORE_TEMPLATE_PATH, sheetname=sheet.decode("utf-8"), keep_default_na=False, encoding = "utf-8") def main_calculation(self, *args, **kwargs): main_template = self.templates[Const.KPIS] for i, main_line in main_template.iterrows(): self.calculate_main_kpi(main_line) self.commit_results() def calculate_main_kpi(self, main_line): kpi_name = main_line[Const.KPI_NAME] kpi_type = main_line[Const.Type] scene_types = self.does_exist(main_line, Const.SCENE_TYPES) result = score = 0 general_filters = {} scif_scene_types = self.scif['template_name'].unique().tolist() store_type = str(self.store_info["store_type"].iloc[0]) store_types = self.does_exist_store(main_line, Const.STORE_TYPES) if store_type in store_types: if scene_types: if (('All' in scene_types) or bool(set(scif_scene_types) & set(scene_types))) : if not ('All' in scene_types): general_filters['template_name'] = scene_types if kpi_type == Const.SOVI: relevant_template = self.templates[kpi_type] relevant_template = relevant_template[relevant_template[Const.KPI_NAME] == kpi_name] if relevant_template["numerator param 1"].all() and relevant_template["denominator param"].all(): function = self.get_kpi_function(kpi_type) for i, kpi_line in relevant_template.iterrows(): result, score = function(kpi_line, general_filters) else: pass else: pass @staticmethod def does_exist(kpi_line, column_name): """ checks if kpi_line has values in this column, and if it does - returns a list of these values :param kpi_line: line from template :param column_name: str :return: list of values if there are, otherwise None """ if column_name in kpi_line.keys() and kpi_line[column_name] != "": cell = kpi_line[column_name] if type(cell) in [int, float]: return [cell] elif type(cell) in [unicode, str]: return cell.split(", ") return None @staticmethod def does_exist_store(kpi_line, column_name): """ checks if kpi_line has values in this column, and if it does - returns a list of these values :param kpi_line: line from template :param column_name: str :return: list of values if there are, otherwise None """ if column_name in kpi_line.keys() and kpi_line[column_name] != "": cell = kpi_line[column_name] if type(cell) in [int, float]: return [cell] elif type(cell) in [unicode, str]: return cell.split(",") return None def calculate_sos(self, kpi_line, general_filters): kpi_name = kpi_line[Const.KPI_NAME] den_type = kpi_line[Const.DEN_TYPES_1] den_value = kpi_line[Const.DEN_VALUES_1].split(',') num_type = kpi_line[Const.NUM_TYPES_1] num_value = kpi_line[Const.NUM_VALUES_1].split(',') general_filters[den_type] = den_value sos_filters = {num_type : num_value} if kpi_line[Const.NUM_TYPES_2]: num_type_2 = kpi_line[Const.NUM_TYPES_2] num_value_2 = kpi_line[Const.NUM_VALUES_2].split(',') sos_filters[num_type_2] = num_value_2 sos_value = self.sos.calculate_share_of_shelf(sos_filters, **general_filters) # sos_value *= 100 sos_value = round(sos_value, 2) score = self.get_score_from_range(kpi_name, sos_value) manufacturer_products = self.all_products[ self.all_products['manufacturer_name'] == num_value[0]].iloc[0] manufacturer_fk = manufacturer_products["manufacturer_fk"] all_products = self.all_products[ self.all_products['category'] == den_value[0]].iloc[0] category_fk = all_products["category_fk"] numerator_res, denominator_res = self.get_numerator_and_denominator(sos_filters, **general_filters) self.common.write_to_db_result_new_tables(fk = 1, numerator_id=manufacturer_fk, numerator_result= numerator_res, denominator_id=category_fk, denominator_result= denominator_res, result=sos_value, score= score, score_after_actions= score) return sos_value, score def get_score_from_range(self, kpi_name, sos_value): store_type = str(self.store_info["store_type"].iloc[0]) self.score_templates[store_type] = self.score_templates[store_type].replace(kpi_name, kpi_name.encode("utf-8")) score_range = self.score_templates[store_type].query('Kpi == "' + str(kpi_name.encode("utf-8")) + '" & Low <= ' + str(sos_value) + ' & High >= ' + str(sos_value)+'') score = score_range['Score'].iloc[0] return score def get_kpi_function(self, kpi_type): """ transfers every kpi to its own function .encode('utf-8') :param kpi_type: value from "sheet" column in the main sheet :return: function """ if kpi_type == Const.SOVI: return self.calculate_sos else: Log.warning("The value '{}' in column sheet in the template is not recognized".format(kpi_type)) return None @staticmethod def round_result(result): return round(result, 3) def get_numerator_and_denominator(self, sos_filters=None, include_empty=False, **general_filters): if include_empty == self.EXCLUDE_EMPTY and 'product_type' not in sos_filters.keys() + general_filters.keys(): general_filters['product_type'] = (self.EMPTY, self.EXCLUDE_FILTER) pop_filter = self.toolbox.get_filter_condition(self.scif, **general_filters) subset_filter = self.toolbox.get_filter_condition(self.scif, **sos_filters) try: pop = self.scif filtered_population = pop[pop_filter] if filtered_population.empty: return 0,0 else: subset_population = filtered_population[subset_filter] # ratio = TBox.calculate_ratio_sum_field_in_rows(filtered_population, subset_population, Fd.FACINGS) df = filtered_population subset_df = subset_population sum_field = Fd.FACINGS try: Validation.is_empty_df(df) Validation.is_empty_df(subset_df) Validation.is_subset(df, subset_df) Validation.df_columns_equality(df, subset_df) Validation.validate_columns_exists(df, [sum_field]) Validation.validate_columns_exists(subset_df, [sum_field]) Validation.is_none(sum_field) except Exception, e: msg = "Data verification failed: {}.".format(e) # raise Exception(msg) default_value = 0 numerator = TBox.calculate_frame_column_sum(subset_df, sum_field, default_value) denominator = TBox.calculate_frame_column_sum(df, sum_field, default_value) return numerator, denominator except Exception as e: Log.error(e.message) return True def commit_results(self): insert_queries = self.merge_insert_queries(self.kpi_results_new_tables_queries) self.rds_conn.disconnect_rds() self.rds_conn.connect_rds() cur = self.rds_conn.db.cursor() delete_query = SOLARBRQueries.get_delete_session_results_query(self.session_uid, self.session_id) cur.execute(delete_query) for query in insert_queries: cur.execute(query) self.rds_conn.db.commit() self.rds_conn.disconnect_rds() @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