class DIAGEOAUToolBox: def __init__(self, data_provider, output): self.data_provider = data_provider self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.output = output self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common) 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_results = self.diageo_generator.diageo_global_grouping_assortment_calculation() self.commonV2.save_json_to_new_tables(assortment_results) # committing to new tables self.commonV2.commit_results_data() # committing to the old tables self.common.commit_results_data()
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.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.tools = CCBRGENERALToolBox(self.data_provider, self.output, rds_conn=self.rds_conn) self.store_info = self.data_provider[Data.STORE_INFO] self.kpi_results_queries = [] self.survey = Survey(self.data_provider, self.output) self.kpi_results_new_tables_queries = [] self.New_kpi_static_data = self.get_new_kpi_static_data() self.session_id = self.data_provider.session_id self.prices_per_session = PsDataProvider( self.data_provider, self.output).get_price_union(self.session_id) self.common_db = Common(self.data_provider) self.count_sheet = pd.read_excel(PATH, Const.COUNT).fillna("") self.group_count_sheet = pd.read_excel(PATH, Const.GROUP_COUNT).fillna("") self.survey_sheet = pd.read_excel(PATH, Const.SURVEY).fillna("")
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 __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.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) # self.store_type = self.data_provider[Data.STORE_INFO]['store_type'].iloc[0] query_store_type = CCZAQueries.get_attr3(self.session_uid) store_type = pd.read_sql_query(query_store_type, self.rds_conn.db) self.store_type = store_type[Const.ATTR3].iloc[0] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.tools = GENERALToolBox(self.data_provider, self.output, rds_conn=self.rds_conn) self.survey_response = self.data_provider[Data.SURVEY_RESPONSES] self.tool_box_for_flow = ToolBox # create data-frames from template self.kpi_sheets = {} for name in Const.sheet_names_and_rows: self.kpi_sheets[name] = parse_template(TEMPLATE_PATH, sheet_name=name, lower_headers_row_index=Const.sheet_names_and_rows[name]) self.common = Common(self.data_provider, Const.RED_SCORE) self.survey_handler = Survey(self.data_provider, self.output, self.kpi_sheets[Const.SURVEY_QUESTIONS]) self.kpi_static_data = self.common.kpi_static_data self.kpi_results_queries = [] self.common_v2 = CommonV2(self.data_provider) self.own_manuf_fk = self.get_own_manufacturer_fk() self.scif_match_react = self.scif[self.scif[ScifConsts.RLV_SOS_SC] == 1]
class DIAGEOUGToolBox: def __init__(self, data_provider, output): self.data_provider = data_provider self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.output = output self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common) def main_calculation(self): """This function calculates the KPI results.""" # SOS Out Of The Box kpis self.diageo_generator.activate_ootb_kpis(self.commonV2) # sos by scene type self.diageo_generator.sos_by_scene_type(self.commonV2) # Global assortment kpis assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2() self.commonV2.save_json_to_new_tables(assortment_res_dict) # Global assortment kpis - v3 for NEW MOBILE REPORTS use assortment_res_dict_v3 = self.diageo_generator.diageo_global_assortment_function_v3() self.commonV2.save_json_to_new_tables(assortment_res_dict_v3) # committing to new tables self.commonV2.commit_results_data() # committing to the old tables self.common.commit_results_data()
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.tools = CUBAUCUBAUGENERALToolBox(self.data_provider, self.output, rds_conn=self.rds_conn) self.common = Common(self.data_provider) self.common_sos = SOS(self.data_provider, self.output) self.kpi_results_queries = [] self.k_engine = BaseCalculationsGroup(data_provider, output) self.store_type = data_provider.store_type self.matches_with_direction = self.get_match_product_in_scene_with_direction( )
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 __init__(self, data_provider, output): self.data_provider = data_provider self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.output = output self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common)
class DIAGEOIEToolBox: def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.output = output self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common, menu=True) def main_calculation(self): """ This function calculates the KPI results. """ # SOS Out Of The Box kpis sos_res = self.diageo_generator.global_sos_calculation( include_posm=True) self.commonV2.save_json_to_new_tables(sos_res) # sos by scene type self.diageo_generator.sos_by_scene_type_with_posm(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 Tap Brand Score template_path = os.path.join( os.path.dirname(os.path.dirname(os.path.realpath(__file__))), 'Data', 'Brand Score.xlsx') res_dict = self.diageo_generator.diageo_global_tap_brand_score_function( template_path, save_to_tables=False, calculate_components=True) self.commonV2.save_json_to_new_tables(res_dict) # Global Visible to Consumer 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: self.commonV2.save_json_to_new_tables(res_dict) # committing to new tables self.commonV2.commit_results_data() # committing to the old tables self.common.commit_results_data()
def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.project_name = data_provider.project_name self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common) self.template_handler = TemplateHandler(self.project_name)
class DIAGEOPLToolBox: def __init__(self, data_provider, output): self.data_provider = data_provider self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.output = output self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common, menu=True) def main_calculation(self): """ This function calculates the KPI results """ # SOS Out Of The Box kpis self.diageo_generator.activate_ootb_kpis(self.commonV2) # sos by scene type self.diageo_generator.sos_by_scene_type(self.commonV2) # Global assortment kpis assortment_results = self.diageo_generator.diageo_global_grouping_assortment_calculation( ) self.commonV2.save_json_to_new_tables(assortment_results) # Global Share of Menu kpis menus_res_dict = self.diageo_generator.diageo_global_new_share_of_menu_function( ) self.commonV2.save_json_to_new_tables(menus_res_dict) # Global Visible to Consumer 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: self.commonV2.save_json_to_new_tables(res_dict) # 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 run_project_calculations(self): self.timer.start() common = Common(self.data_provider) heinz = HEINZGenerator(self.data_provider, self.output, common) heinz.heinz_global_distribution_per_category() heinz.heinz_global_share_of_shelf_function() heinz.heinz_global_price_adherence( pd.read_excel(os.path.join( os.path.dirname(os.path.realpath(__file__)), 'Config', 'Price Adherence Targets 3Ene2019.xlsx'), sheetname="Price Adherence")) heinz.heinz_global_extra_spaces() common.commit_results_data_to_new_tables() self.timer.stop('KPIGenerator.run_project_calculations')
def __init__(self, data_provider, output): self.data_provider = data_provider self.project_name = self.data_provider.project_name self.ps_data_provider = PsDataProvider(data_provider) self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.output = output self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common) self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.session_uid = self.data_provider.session_uid
class DIAGEOBRToolBox: def __init__(self, data_provider, output): self.data_provider = data_provider self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.output = output self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common, menu=True) def main_calculation(self): """ This function calculates the KPI results """ # SOS Out Of The Box kpis self.diageo_generator.activate_ootb_kpis(self.commonV2) # sos by scene type self.diageo_generator.sos_by_scene_type(self.commonV2) # Global assortment kpis assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2( ) self.commonV2.save_json_to_new_tables(assortment_res_dict) # Global assortment kpis - v3 for NEW MOBILE REPORTS use assortment_res_dict_v3 = self.diageo_generator.diageo_global_assortment_function_v3( ) self.commonV2.save_json_to_new_tables(assortment_res_dict_v3) # Global 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']) # Global Menu kpis menus_res_dict = self.diageo_generator.diageo_global_new_share_of_menu_function( ) self.commonV2.save_json_to_new_tables(menus_res_dict) # committing to new tables self.commonV2.commit_results_data() # committing to the old tables self.common.commit_results_data()
def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.project_name = self.data_provider.project_name self.commonV2 = CommonV2(self.data_provider) self.common = Common(self.data_provider) self.assortment = Assortment(self.data_provider, self.output) self.own_manuf_fk = int( self.data_provider.own_manufacturer.param_value.values[0]) self.store_info = self.data_provider[Data.STORE_INFO] self.new_kpi_static_data = self.commonV2.get_new_kpi_static_data() self.all_products_include_deleted = self.data_provider[ Data.ALL_PRODUCTS_INCLUDING_DELETED] self.visit_date = self.data_provider[Data.VISIT_DATE]
class DIAGEOKEToolBox: def __init__(self, data_provider, output): self.data_provider = data_provider self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.output = output self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common) def main_calculation(self): """ This function calculates the KPI results. """ # SOS Out Of The Box kpis self.diageo_generator.activate_ootb_kpis(self.commonV2) # sos by scene type self.diageo_generator.sos_by_scene_type(self.commonV2) # Global assortment kpis assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2( ) self.commonV2.save_json_to_new_tables(assortment_res_dict) # Global assortment kpis - v3 for NEW MOBILE REPORTS use assortment_res_dict_v3 = self.diageo_generator.diageo_global_assortment_function_v3( ) self.commonV2.save_json_to_new_tables(assortment_res_dict_v3) # Global Visible to Consumer 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: self.commonV2.save_json_to_new_tables(res_dict) # committing to new tables self.commonV2.commit_results_data() # committing to the old tables self.common.commit_results_data()
class DIAGEOARDIAGEOARToolBox: def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.project_name = data_provider.project_name self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common) self.template_handler = TemplateHandler(self.project_name) def main_calculation(self): """ This function calculates the KPI results. """ log_runtime('Updating templates')(self.template_handler.update_templates)() # SOS Out Of The Box kpis self.diageo_generator.activate_ootb_kpis(self.commonV2) # sos by scene type self.diageo_generator.sos_by_scene_type(self.commonV2) # Global assortment kpis assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2() self.commonV2.save_json_to_new_tables(assortment_res_dict) # Global assortment kpis - v3 for NEW MOBILE REPORTS use assortment_res_dict_v3 = self.diageo_generator.diageo_global_assortment_function_v3() self.commonV2.save_json_to_new_tables(assortment_res_dict_v3) # Global Relative Position function template_data = self.template_handler.download_template(DiageoKpiNames.RELATIVE_POSITION) res_dict = self.diageo_generator.diageo_global_relative_position_function( template_data, location_type='template_name') self.commonV2.save_json_to_new_tables(res_dict) # committing to new tables self.commonV2.commit_results_data() # committing to the old tables self.common.commit_results_data()
def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.project_name = self.data_provider.project_name self.adjacency = Adjancency(self.data_provider) self.block = Block(self.data_provider) self.template_name = 'summary_kpis.xlsx' self.TEMPLATE_PATH = os.path.join( os.path.dirname(os.path.dirname(os.path.realpath(__file__))), 'Data', self.template_name) self.template_data = parse_template(self.TEMPLATE_PATH, "KPIs") self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.tools = GENERALToolBox(self.data_provider) self.common = Common(self.data_provider) self.kpi_results_queries = [] self.cub_tools = CUBAUCUBAUGENERALToolBox(self.data_provider, self.output) self.store_type = self.data_provider.store_type self.store_info = self.data_provider[Data.STORE_INFO] self.session_uid = self.data_provider.session_uid self.visit_date = self.data_provider.visit_date
def __init__(self, data_provider, output): self.data_provider = data_provider self.commonV2 = CommonV2(self.data_provider) self.rds_conn = PSProjectConnector(self.data_provider.project_name, DbUsers.CalculationEng) self.store_assortment = PSAssortmentDataProvider(self.data_provider).execute(policy_name=None) self.output = output self.kpi_static_data = self.get_kpi_static_data() self.store_id = self.data_provider[Data.STORE_FK] self.own_manufacturer_fk = 12 self.store_info = self.get_store_info(self.store_id) self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.all_templates = self.data_provider[Data.ALL_TEMPLATES] self.common = Common(self.data_provider) self.scene_info = self.data_provider[Data.SCENES_INFO] self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common) self.template_handler = TemplateHandler(self.data_provider.project_name) self.template_path = os.path.join(os.path.dirname(os.path.dirname(os.path.realpath(__file__))), 'Data') self.template_path = os.path.join(self.template_path, "Template.xlsx") self.kpi_template_data = parse_template(self.template_path, sheet_name='KPI') self.store_info = self.data_provider[Data.STORE_INFO] self.current_date = datetime.now()
class CCBRToolBox: 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.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.tools = CCBRGENERALToolBox(self.data_provider, self.output, rds_conn=self.rds_conn) self.store_info = self.data_provider[Data.STORE_INFO] self.kpi_results_queries = [] self.survey = Survey(self.data_provider, self.output) self.kpi_results_new_tables_queries = [] self.New_kpi_static_data = self.get_new_kpi_static_data() self.session_id = self.data_provider.session_id self.prices_per_session = PsDataProvider( self.data_provider, self.output).get_price_union(self.session_id) self.common_db = Common(self.data_provider) self.count_sheet = pd.read_excel(PATH, Const.COUNT).fillna("") self.group_count_sheet = pd.read_excel(PATH, Const.GROUP_COUNT).fillna("") self.survey_sheet = pd.read_excel(PATH, Const.SURVEY).fillna("") def main_calculation(self): """ This function calculates the KPI results. """ kpis_sheet = pd.read_excel(PATH, Const.KPIS).fillna("") for index, row in kpis_sheet.iterrows(): self.handle_atomic(row) self.handle_simon_kpis() self.commit_results_data() def handle_simon_kpis(self): """ activate the availability and pricing functions """ active_products = self.all_products.loc[ self.all_products["is_active"] > 0] self.calculate_availability(active_products) self.calculate_pricing(self.all_products) def calculate_availability(self, active_products): """ calculates the availability for all products per session, used is sovi and sovi vertical reports :param active_products: a df containing only active products """ active_products_sku_and_other = active_products[ (active_products['product_type'] == 'SKU') | (active_products['product_type'] == 'Other')] active_products_pks = active_products_sku_and_other[ 'product_fk'].unique().tolist() filters = {'product_fk': active_products_pks} filtered_df = self.scif[self.tools.get_filter_condition( self.scif, **filters)] facing_filtered = filtered_df.loc[filtered_df['facings'] > 0][[ 'template_fk', 'product_fk', 'facings' ]] facing_filtered_pks = facing_filtered['product_fk'].unique().tolist() for product in facing_filtered_pks: product_df = facing_filtered.loc[facing_filtered['product_fk'] == product] product_template_fks = product_df['template_fk'].unique().tolist() for template_fk in product_template_fks: sum_facing = product_df.loc[product_df['template_fk'] == template_fk]['facings'].sum() self.write_to_db_result_new_tables(fk=Const.AVAILABILITY_PK, numerator_id=product, score='1', denominator_id=template_fk, numerator_result='1', result=sum_facing) def calculate_pricing(self, all_products): """ inserting the db the pricing of all active and inactive skus. used in preco and preco vertical reports :param all_products: df containing all products """ only_sku_type_products = all_products.loc[all_products['product_type'] == 'SKU'] all_products_fks_size = only_sku_type_products[['product_fk', 'size']].fillna("") product_fks_and_prices = self.prices_per_session merge_size_and_price = pd.merge(all_products_fks_size, product_fks_and_prices, how='left', on='product_fk') merge_size_and_price['value'] = merge_size_and_price['value'].fillna( '0') for row in merge_size_and_price.itertuples(): product = row[1] # row['product_fk'] size = row[2] # row['size'] price = row[3] # row['value'] if size == '': size = 0 if price > 0: self.write_to_db_result_new_tables(fk=Const.PRICING_PK, numerator_id=product, numerator_result=size, result=price) def handle_atomic(self, row): """ run the correct kpi for a specific row in the template :param row: a row from the template """ atomic_name = row[Const.ENGLISH_KPI_NAME].strip() kpi_type = row[Const.KPI_TYPE].strip() if kpi_type == Const.SURVEY: self.handle_survey_atomics(atomic_name) elif kpi_type == Const.COUNT: self.handle_count_atomics(atomic_name) elif kpi_type == Const.GROUP_COUNT: self.handle_group_count_atomics(atomic_name) def handle_survey_atomics(self, atomic_name): """ handle survey questions :param atomic_name: the name of the kpi :return: only if the survey filters aren't satisfied """ row = self.survey_sheet.loc[self.survey_sheet[Const.ENGLISH_KPI_NAME] == atomic_name] if row.empty: Log.warning("Dataframe is empty, wrong kpi name: " + atomic_name) return store_type_filter = self.store_info['store_type'].values[0].strip() store_type_template = row[Const.STORE_TYPE_TEMPLATE].values[0].strip() # if cell in template is not empty if store_type_template != "": store_types = store_type_template.split(",") store_types = [item.strip() for item in store_types] if store_type_filter not in store_types: return # find the answer to the survey in session question_id = row[Const.SURVEY_QUESTION_ID].values[0] question_answer_template = row[Const.TARGET_ANSWER].values[0] survey_result = self.survey.get_survey_answer( ('question_fk', question_id)) if question_answer_template == Const.NUMERIC: if not survey_result: survey_result = 0 if not isinstance(survey_result, (int, long, float)): Log.warning("question id " + str(question_id) + " in template is not a number") survey_result = 0 else: answer = self.survey.check_survey_answer( ('question_fk', question_id), question_answer_template) survey_result = 1 if answer else -1 try: atomic_pk = self.common_db.get_kpi_fk_by_kpi_name_new_tables( atomic_name) except IndexError: Log.warning("There is no matching Kpi fk for kpi name: " + atomic_name) return self.write_to_db_result_new_tables(fk=atomic_pk, numerator_id=self.session_id, numerator_result=survey_result, result=survey_result) def handle_count_atomics(self, atomic_name): """ handle count kpis, used in consolidada report :param atomic_name: the name of the kpi to calculate """ sum_of_count = 0 target = 0 count_result = 0 row = self.count_sheet.loc[self.count_sheet[Const.ENGLISH_KPI_NAME] == atomic_name] if row.empty: Log.warning("Dataframe is empty, wrong kpi name: " + atomic_name) return try: atomic_pk = self.common_db.get_kpi_fk_by_kpi_name_new_tables( atomic_name) except IndexError: Log.warning("There is no matching Kpi fk for kpi name: " + atomic_name) return for index, row in row.iterrows(): sum_of_count, target, count_result = self.handle_count_row(row) if not isinstance(sum_of_count, (int, float, long)): sum_of_count = count_result self.write_to_db_result_new_tables(fk=atomic_pk, numerator_id=self.session_id, numerator_result=sum_of_count, denominator_result=target, result=count_result) def handle_group_count_atomics(self, atomic_name): """ handle group count kpis (different from count in or and and conditions), used in consolidada report :param atomic_name: the name of the kpi to calculate """ rows = self.group_count_sheet.loc[self.group_count_sheet[ Const.GROUP_KPI_NAME] == atomic_name] group_weight = 0 group_result = 0 group_target = 0 group_sum_of_count = 0 sum_of_count_df = pd.DataFrame() target_operator = "" if rows.empty: Log.warning("Dataframe is empty, wrong kpi name: " + atomic_name) return try: atomic_pk = self.common_db.get_kpi_fk_by_kpi_name_new_tables( atomic_name) except IndexError: Log.warning("There is no matching Kpi fk for kpi name: " + atomic_name) return for index, row in rows.iterrows(): target_operator = row[Const.TARGET_OPERATOR].strip() weight = row[Const.WEIGHT] sum_of_count, target, count_result = self.handle_count_row(row) if count_result >= 1: group_weight += weight if group_weight >= 100: # use for getting numeric results instead of 1 and 0 if (target_operator == '+'): sum_of_count_df = pd.concat( [sum_of_count_df, sum_of_count]) else: group_result = 1 break # conditional, if given -1000 kpi must fail elif count_result == -1000: group_result = 0 break # use for getting numeric results instead of 1 and 0 if (target_operator == '+'): if sum_of_count_df.empty: group_sum_of_count = 0 else: group_sum_of_count = len(sum_of_count_df.groupby('scene_id')) group_result = group_sum_of_count self.write_to_db_result_new_tables(fk=atomic_pk, numerator_id=self.session_id, numerator_result=group_sum_of_count, denominator_result=group_target, result=group_result) def handle_count_row(self, row): """ filters qall params in aspecific row and send it to the correct count calculation :param row: :return: """ count_type = row[Const.COUNT_TYPE].strip() target = row[Const.TARGET] target_operator = row[Const.TARGET_OPERATOR].strip() product_template = row[Const.PRODUCT] store_type_filter = self.store_info['store_type'].values[0] store_type_template = row[Const.STORE_TYPE_TEMPLATE] product_size = row[Const.PRODUCT_SIZE] product_size_operator = row[Const.PRODUCT_SIZE_OPERATOR].strip() product_measurement_unit = row[Const.MEASUREMENT_UNIT].strip() consider_few = row[Const.CONSIDER_FEW] multipack_template = row[Const.MULTIPACK].strip() multipack_df = None # filter store type if store_type_template != "": store_types = store_type_template.split(",") store_types = [item.strip() for item in store_types] if store_type_filter not in store_types: return 0, 0, 0 filtered_df = self.scif.copy() # filter product if product_template != "": products_to_check = product_template.split(",") products_to_check = [item.strip() for item in products_to_check] filtered_df = filtered_df[filtered_df['product_name'].isin( products_to_check)] if filtered_df.empty: return 0, 0, 0 # filter product size if product_size != "": if product_measurement_unit == 'l': product_size *= 1000 ml_df = filtered_df[filtered_df['size_unit'] == 'ml'] l_df = filtered_df[filtered_df['size_unit'] == 'l'] if multipack_template != "": multipack_df = filtered_df[filtered_df['MPACK'] == 'Y'] temp_df = l_df.copy() temp_df['size'] = l_df['size'].apply((lambda x: x * 1000)) filtered_df = pd.concat([temp_df, ml_df]) if product_size_operator == '<': filtered_df = filtered_df[filtered_df['size'] < product_size] elif product_size_operator == '<=': filtered_df = filtered_df[filtered_df['size'] <= product_size] elif product_size_operator == '>': filtered_df = filtered_df[filtered_df['size'] > product_size] elif product_size_operator == '>=': filtered_df = filtered_df[filtered_df['size'] >= product_size] elif product_size_operator == '=': filtered_df = filtered_df[filtered_df['size'] == product_size] # multipack conditions is an or between product size and MPACK if multipack_template != "": filtered_df = pd.concat([filtered_df, multipack_df]).drop_duplicates() filters = self.get_filters_from_row(row) count_of_units = 0 if count_type == Const.SCENE: count_of_units = self.count_of_scenes(filtered_df, filters, target_operator, target) elif count_type == Const.FACING: count_of_units = self.count_of_facings(filtered_df, filters, consider_few, target) elif count_type == Const.SCENE_SOS: count_of_units = self.count_of_sos(filtered_df, filters) else: Log.warning("Couldn't find a correct COUNT variable in template") if target_operator == '<=': count_result = 1 if (target <= count_of_units) else 0 # use for getting numeric results instead of 1 and 0 elif target_operator == '+': if isinstance(count_of_units, (int, float, long)): count_result = count_of_units else: count_result = len(count_of_units) else: count_result = 1 if (target >= count_of_units) else 0 return count_of_units, target, count_result def get_filters_from_row(self, row): """ handle filters appering in scif :param row: row containing all filters :return: a dictionary of the filters """ filters = dict(row) # no need to be accounted for, fields that aren't in scif for field in Const.DELETE_FIELDS: if field in filters: del filters[field] if Const.WEIGHT in filters.keys(): del filters[Const.WEIGHT] if Const.GROUP_KPI_NAME in filters.keys(): del filters[Const.GROUP_KPI_NAME] exclude_manufacturer = filters[Const.EXCLUDE_MANUFACTURER].strip() if exclude_manufacturer != "": filters[Const.MANUFACTURER] = (exclude_manufacturer, Const.EXCLUDE_FILTER) del filters[Const.EXCLUDE_MANUFACTURER] exclude_category = filters[Const.EXCLUDE_CATEGORY].strip() if exclude_category != "": filters[Const.CATEGORY] = (exclude_category, Const.EXCLUDE_FILTER) del filters[Const.EXCLUDE_CATEGORY] # filter all the empty cells for key in filters.keys(): if (filters[key] == ""): del filters[key] elif isinstance(filters[key], tuple): filters[key] = (filters[key][0].split(","), filters[key][1]) else: filters[key] = filters[key].split(",") filters[key] = [item.strip() for item in filters[key]] return self.create_filters_according_to_scif(filters) def create_filters_according_to_scif(self, filters): """ adjusting the template names to scif names :param filters: only the scif filters in the template shape :return: the filters dictionary """ convert_from_scif = { Const.TEMPLATE_GROUP: 'template_group', Const.TEMPLATE_NAME: 'template_name', Const.BRAND: 'brand_name', Const.CATEGORY: 'category', Const.MANUFACTURER: 'manufacturer_name', Const.PRODUCT_TYPE: 'product_type', Const.MULTIPACK: 'MPAK' } for key in filters.keys(): filters[convert_from_scif[key]] = filters.pop(key) return filters def count_of_scenes(self, filtered_df, filters, target_operator, target): """ calculate the count of scene types :param filtered_df: the first filtered (no scif filters) dataframe :param filters: the scif filters :param target_operator: the operation to do, + for returning a dataframe (used in group count) :param target: the target :return: dataframe for group counts +, number of scenes for all other functions """ scene_data = filtered_df[self.tools.get_filter_condition( filtered_df, **filters)] if target_operator == '+': # filter by scene_id and by template_name (scene type) scene_types_groupby = scene_data.groupby( ['template_name', 'scene_id'])['facings'].sum().reset_index() number_of_scenes = scene_types_groupby[ scene_types_groupby['facings'] >= target] else: number_of_scenes = len(scene_data['scene_id'].unique()) return number_of_scenes def count_of_sos(self, filtered_df, filters): """ calculating the share of shelf :param filtered_df: the first filtered (no scif filters) dataframe :param filters: the scif filters :return: the number of different scenes answered the condition (hard coded 50%) """ scene_data = filtered_df[self.tools.get_filter_condition( filtered_df, **filters)] scene_data = scene_data.rename(columns={"facings": "facings_nom"}) # filter by scene_id and by template_name (scene type) scene_types_groupby = scene_data.groupby(['template_name', 'scene_id' ])['facings_nom'].sum() all_products_groupby = self.scif.groupby(['template_name', 'scene_id' ])['facings'].sum() merge_result = pd.concat((scene_types_groupby, all_products_groupby), axis=1, join='inner').reset_index() return len(merge_result[ merge_result['facings_nom'] >= merge_result['facings'] * 0.5]) def count_of_facings(self, filtered_df, filters, consider_few, target): ''' calculate the count of facings :param filtered_df: the first filtered (no scif filters) dataframe :param filters: the scif filters :param consider_few: in case there is a need to consider more then one brand :param target: the target to pass :return: ''' facing_data = filtered_df[self.tools.get_filter_condition( filtered_df, **filters)] if consider_few != "": facing_data_groupby = facing_data.groupby(['brand_name' ])['facings'].sum() if len(facing_data_groupby[ facing_data_groupby >= target]) >= consider_few: number_of_facings = facing_data['facings'].sum() else: number_of_facings = 0 else: number_of_facings = facing_data['facings'].sum() return number_of_facings def get_new_kpi_static_data(self): """ This function extracts the static new KPI data (new tables) and saves it into one global data frame. The data is taken from static.kpi_level_2. """ query = CCBRQueries.get_new_kpi_data() kpi_static_data = pd.read_sql_query(query, self.rds_conn.db) return kpi_static_data def write_to_db_result_new_tables(self, fk, numerator_id, numerator_result, result, denominator_id=None, denominator_result=None, score=None): """ This function creates the result data frame of new rables KPI, and appends the insert SQL query into the queries' list, later to be written to the DB. """ table = KPI_NEW_TABLE attributes = self.create_attributes_dict_new_tables( fk, numerator_id, numerator_result, denominator_id, denominator_result, result, score) query = insert(attributes, table) self.kpi_results_new_tables_queries.append(query) def create_attributes_dict_new_tables(self, kpi_fk, numerator_id, numerator_result, denominator_id, denominator_result, result, score): """ This function creates a data frame with all attributes needed for saving in KPI results new tables. """ attributes = pd.DataFrame( [(kpi_fk, self.session_id, numerator_id, numerator_result, denominator_id, denominator_result, result, score)], columns=[ 'kpi_level_2_fk', 'session_fk', 'numerator_id', 'numerator_result', 'denominator_id', 'denominator_result', 'result', 'score' ]) 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_new_tables_queries) self.rds_conn.disconnect_rds() self.rds_conn.connect_rds() cur = self.rds_conn.db.cursor() delete_query = CCBRQueries.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
class DIAGEOMXToolBox: def __init__(self, data_provider, output): self.data_provider = data_provider self.project_name = self.data_provider.project_name self.ps_data_provider = PsDataProvider(data_provider) self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.output = output self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common) self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.session_uid = self.data_provider.session_uid def main_calculation(self): if not self.exists_survey_resposnse(): """ This function calculates the KPI results """ # SOS Out Of The Box kpis self.diageo_generator.activate_ootb_kpis(self.commonV2) # sos by scene type self.diageo_generator.sos_by_scene_type(self.commonV2) # Global assortment kpis assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v2( ) self.commonV2.save_json_to_new_tables(assortment_res_dict) # Global assortment kpis - v3 for NEW MOBILE REPORTS use assortment_res_dict_v3 = self.diageo_generator.diageo_global_assortment_function_v3( ) self.commonV2.save_json_to_new_tables(assortment_res_dict_v3) # global SOS kpi res_dict = self.diageo_generator.diageo_global_share_of_shelf_function( ) self.commonV2.save_json_to_new_tables(res_dict) # global touch point kpi template_path = os.path.join( os.path.dirname(os.path.dirname(os.path.realpath(__file__))), 'Data', 'TOUCH POINT (template) Agosto 2020.xlsx') res_dict = self.diageo_generator.diageo_global_touch_point_function( template_path) self.commonV2.save_json_to_new_tables(res_dict) # committing to the old tables self.common.commit_results_data() # commit to old tables # committing to new tables self.commonV2.commit_results_data() def exists_survey_resposnse(self): # Method is used to check in the db to if survey question_fk 98 is answered # If answered, the kpi should not run query = """SELECT session_uid,sr.question_fk, sr.selected_option_fk FROM probedata.survey_response sr Left Join static.survey_question_answer qa on sr.selected_option_text = qa.pk where sr.question_fk = 98 and session_uid = '{}';""".format( self.session_uid) cur = self.rds_conn.db.cursor() cur.execute(query) res = cur.fetchall() if res: relevant_response_df = pd.DataFrame(list(res)) try: survey_response = relevant_response_df.iloc[0, 2] except IndexError: survey_response = None else: survey_response = None return survey_response
class 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()
class CCUSLiveDemoToolBox: def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.project_name = self.data_provider.project_name self.commonV2 = CommonV2(self.data_provider) self.common = Common(self.data_provider) self.assortment = Assortment(self.data_provider, self.output) self.own_manuf_fk = int( self.data_provider.own_manufacturer.param_value.values[0]) self.store_info = self.data_provider[Data.STORE_INFO] self.new_kpi_static_data = self.commonV2.get_new_kpi_static_data() self.all_products_include_deleted = self.data_provider[ Data.ALL_PRODUCTS_INCLUDING_DELETED] self.visit_date = self.data_provider[Data.VISIT_DATE] def main_calculation(self): """ This function calculates the KPI results. """ # Global assortment kpis # assortment_res_dict = self.diageo_generator.diageo_global_assortment_function_v3() Log.info('In function main_calculation reg-data provider ') assortment_res_dict = self.assortment_calc() self.commonV2.save_json_to_new_tables(assortment_res_dict) self.commonV2.commit_results_data() def get_oos_action_excluded(self, lvl3_result): excluded_from_oos = self.commonV2.get_oos_exclude_values() if excluded_from_oos.empty: return products_excluded = excluded_from_oos.product_fk.unique() lvl3_result.loc[((lvl3_result['in_store'] == 0) & (lvl3_result['product_fk'].isin(products_excluded))), 'in_store'] = 1 def assortment_calc(self): """ This function calculates the KPI results. """ dict_list = [] diageo_fk = self.own_manuf_fk store_fk = self.store_info[Consts.STORE_FK].iloc[0] lvl3_result = self.assortment.calculate_lvl3_assortment() if lvl3_result.empty: return dict_list # remove live session kpis from regular kpi calculation live_session_kpis = list(self.new_kpi_static_data[ self.new_kpi_static_data['live_session_relevance'] == 1]['pk']) self.get_oos_action_excluded(lvl3_result) lvl3_result = lvl3_result[~lvl3_result.kpi_fk_lvl2. isin(live_session_kpis)] if lvl3_result.empty: return dict_list dist_kpi_fk = \ self.new_kpi_static_data[(self.new_kpi_static_data['client_name'] == Consts.DISTRIBUTION_KPI) | (self.new_kpi_static_data['type'] == Consts.DISTRIBUTION_KPI)][ Consts.PK].values[0] kpis = self.new_kpi_static_data[self.new_kpi_static_data['type'].isin( Consts.LMPA_KPIS)][Consts.PK] if kpis.empty: lvl3_result = pd.merge(lvl3_result, self.all_products_include_deleted[[ Consts.PRODUCT_FK, 'category_fk' ]], on=[Consts.PRODUCT_FK]) else: lvl3_result = pd.merge( lvl3_result[~(lvl3_result['kpi_fk_lvl2'].isin(kpis.values))], self.all_products_include_deleted[[ Consts.PRODUCT_FK, 'category_fk' ]], on=[Consts.PRODUCT_FK]) if not lvl3_result.empty: for result in lvl3_result.itertuples(): score = 2 if result.in_store == 1 else 1 kpi_name = \ self.new_kpi_static_data[self.new_kpi_static_data[Consts.PK] == result.kpi_fk_lvl3][ 'type'].values[ 0] + Consts.CATEGORY_LEVEL_KPI kpi_fk_by_manu = \ self.new_kpi_static_data[self.new_kpi_static_data['type'] == kpi_name][Consts.PK].values[0] identifier_level_2 = self.commonV2.get_dictionary( category_fk=result.category_fk, kpi_fk=result.kpi_fk_lvl2) # by category dict_list.append( self.build_dictionary_for_db_insert_v2( fk=kpi_fk_by_manu, numerator_id=result.product_fk, numerator_result=result.facings, result=score, score=score, denominator_id=result.category_fk, denominator_result=1, identifier_parent=identifier_level_2, should_enter=True)) dist_results = lvl3_result[lvl3_result['kpi_fk_lvl3'] == dist_kpi_fk] for res in dist_results.itertuples(): kpi_fk = \ self.new_kpi_static_data[ self.new_kpi_static_data['type'] == Consts.OOS_SKU_KPI + Consts.CATEGORY_LEVEL_KPI][ Consts.PK].values[0] parent_kpi_fk = \ self.new_kpi_static_data[ self.new_kpi_static_data['type'] == Consts.OOS_KPI + Consts.CATEGORY_LEVEL_KPI][ Consts.PK].values[0] num_res = res.facings if not res.in_store: is_oos = 1 identifier_level_2 = self.commonV2.get_dictionary( category_fk=res.category_fk, kpi_fk=parent_kpi_fk) dict_list.append( self.build_dictionary_for_db_insert_v2( fk=kpi_fk, numerator_id=res.product_fk, numerator_result=num_res, result=is_oos, score=is_oos, denominator_id=res.category_fk, denominator_result=1, identifier_parent=identifier_level_2)) lvl2_result = self.assortment.calculate_lvl2_assortment_v2( lvl3_result) for result in lvl2_result.itertuples(): kpi_name = self.new_kpi_static_data[ self.new_kpi_static_data[Consts.PK] == result. kpi_fk_lvl2]['type'].values[0] + Consts.CATEGORY_LEVEL_KPI identifier_level_2 = self.commonV2.get_dictionary( category_fk=result.category_fk, kpi_fk=result.kpi_fk_lvl2) identifier_level_1 = self.commonV2.get_dictionary( kpi_fk=result.kpi_fk_lvl2) kpi_fk_by_cat = \ self.new_kpi_static_data[self.new_kpi_static_data['type'] == kpi_name][Consts.PK].values[0] denominator_res = result.total if not pd.isnull(result.target) and not pd.isnull( result.group_target_date ) and result.group_target_date.date() <= self.visit_date: denominator_res = result.target res = np.divide(float(result.passes), float(denominator_res)) * 100 if res >= 100: score = 100 else: score = 0 res = 100 if res > 100 else res # Manufacturer level KPI dict_list.append( self.build_dictionary_for_db_insert_v2( fk=kpi_fk_by_cat, numerator_id=result.category_fk, numerator_result=result.passes, result=res, score=score, denominator_id=diageo_fk, denominator_result=denominator_res, identifier_result=identifier_level_2, identifier_parent=identifier_level_1, should_enter=True)) dist_fk = self.new_kpi_static_data[self.new_kpi_static_data['type'] == Consts.DISTRIBUTION_LVL2][ Consts.PK].values[0] dist_results = lvl2_result[lvl2_result['kpi_fk_lvl2'] == dist_fk] for res in dist_results.itertuples(): kpi_fk = \ self.new_kpi_static_data[ self.new_kpi_static_data['type'] == Consts.OOS_KPI + Consts.CATEGORY_LEVEL_KPI][ Consts.PK].values[0] result = np.divide(float(res.total - res.passes), float(res.total)) * 100 identifier_level_1 = self.commonV2.get_dictionary( kpi_fk=kpi_fk) if result >= 100: score = 0 else: score = 100 identifier_level_2 = self.commonV2.get_dictionary( category_fk=res.category_fk, kpi_fk=kpi_fk) num_res = res.total - res.passes dict_list.append( self.build_dictionary_for_db_insert_v2( fk=kpi_fk, numerator_id=res.category_fk, numerator_result=num_res, result=result, score=score, denominator_id=diageo_fk, denominator_result=res.total, identifier_result=identifier_level_2, identifier_parent=identifier_level_1, should_enter=True)) if not lvl2_result.empty: lvl1_result = self.assortment.calculate_lvl1_assortment_v2( lvl2_result) for result in lvl1_result.itertuples(): kpi_level_1_name = \ self.new_kpi_static_data[self.new_kpi_static_data[Consts.PK] == result.kpi_fk_lvl2][ 'type'].values[ 0] + Consts.MANUFACTURER_LEVEL_KPI kpi_level_1 = \ self.new_kpi_static_data[self.new_kpi_static_data['type'] == kpi_level_1_name][ Consts.PK].values[0] identifier_level_1 = self.commonV2.get_dictionary( kpi_fk=result.kpi_fk_lvl2) denominator_res = result.total res = np.divide(float(result.passes), float(denominator_res)) * 100 if res >= 100: score = 100 else: score = 0 dict_list.append( self.build_dictionary_for_db_insert_v2( fk=kpi_level_1, numerator_id=diageo_fk, numerator_result=result.passes, result=res, score=score, denominator_id=store_fk, denominator_result=denominator_res, identifier_result=identifier_level_1, should_enter=False)) dist_fk = self.new_kpi_static_data[ self.new_kpi_static_data['type'] == Consts.DISTRIBUTION_LVL2][Consts.PK].values[0] dist_results = lvl1_result[lvl1_result['kpi_fk_lvl2'] == dist_fk] for res in dist_results.itertuples(): kpi_fk = self.new_kpi_static_data[ self.new_kpi_static_data['type'] == Consts.OOS_KPI + Consts.MANUFACTURER_LEVEL_KPI][Consts.PK].values[0] result = np.divide(float(res.total - res.passes), float(res.total)) * 100 if result >= 100: score = 100 else: score = 0 kpi_ident = self.new_kpi_static_data[ self.new_kpi_static_data['type'] == Consts.OOS_KPI + Consts.CATEGORY_LEVEL_KPI][Consts.PK].values[0] identifier_level_1 = self.commonV2.get_dictionary( kpi_fk=kpi_ident) dict_list.append( self.build_dictionary_for_db_insert_v2( fk=kpi_fk, numerator_id=diageo_fk, numerator_result=res.passes, result=result, score=score, denominator_id=store_fk, denominator_result=res.total, identifier_result=identifier_level_1)) return dict_list def build_dictionary_for_db_insert_v2( self, fk=None, kpi_name=None, numerator_id=0, numerator_result=0, result=0, denominator_id=0, denominator_result=0, score=0, denominator_result_after_actions=None, context_id=None, target=None, identifier_parent=None, identifier_result=None, should_enter=None): try: insert_params = dict() if not fk: if not kpi_name: return else: insert_params[ 'fk'] = self.common.get_kpi_fk_by_kpi_name_new_tables( kpi_name) else: insert_params['fk'] = fk insert_params['numerator_id'] = numerator_id insert_params['numerator_result'] = numerator_result insert_params['denominator_id'] = denominator_id insert_params['denominator_result'] = denominator_result insert_params['result'] = result insert_params['score'] = score if target: insert_params['target'] = target if denominator_result_after_actions: insert_params[ 'denominator_result_after_actions'] = denominator_result_after_actions if context_id: insert_params['context_id'] = context_id if identifier_parent: insert_params['identifier_parent'] = identifier_parent insert_params['should_enter'] = True if identifier_result: insert_params['identifier_result'] = identifier_result if should_enter: insert_params['should_enter'] = should_enter return insert_params except IndexError: Log.error('error in build_dictionary_for_db_insert') return None
def run_project_calculations(self): self.timer.start() common = Common(self.data_provider) COOLERSCREENSUSKGenerator(self.data_provider, self.output, common).main_function() self.timer.stop('KPIGenerator.run_project_calculations')
class CUBAUCUBAUToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 OwnMan = 'CUB' OwnManFK = 1 SOS = ['TAP', 'CUB'] ON_PREMISE = 'On Premise' OFF_PREMISE = 'Off Premise' TOP_PALLET = 'Cool Room Stacks Top' FRONT_PALLET = 'Cool Room Stacks Front' PRODUCT_TYPES_TO_EXCLUDE = ['POS', 'Irrelevant'] 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.tools = CUBAUCUBAUGENERALToolBox(self.data_provider, self.output, rds_conn=self.rds_conn) self.common = Common(self.data_provider) self.common_sos = SOS(self.data_provider, self.output) self.kpi_results_queries = [] self.k_engine = BaseCalculationsGroup(data_provider, output) self.store_type = data_provider.store_type self.matches_with_direction = self.get_match_product_in_scene_with_direction( ) def main_calculation(self): """ This function calculates the KPI results. """ if self.store_type == self.ON_PREMISE: sos_types = ['TAP', 'TRAX'] elif self.store_type == self.OFF_PREMISE: sos_types = ['CUB', 'TRAX'] self.update_custom_scif() for sos in sos_types: exclude_scene = self.get_exclude_scene(sos) product_type = self.get_product_filter(sos) self.own_manufacturer_out_store(sos, product_type, exclude_scene) self.all_param_out_category('manufacturer_fk', 'category_fk', sos, product_type, exclude_scene, context=None) self.all_param_out_category('manufacturer_fk', 'category_fk', sos, product_type, exclude_scene, context='template_fk') self.all_param_out_category('brand_fk', 'category_fk', sos, product_type, exclude_scene, context='template_fk') return def get_product_filter(self, sos): if sos == 'TAP': product_type = 'POS' else: product_type = (['POS', 'Irrelevant'], 0) return product_type def get_exclude_scene(self, sos): if self.store_type == self.ON_PREMISE: exclude_scene = None else: if sos == 'CUB': exclude_scene = ['Cool Room Stacks Front'] else: exclude_scene = ['Cool Room Stacks Top'] return exclude_scene def own_manufacturer_out_store(self, sos_type, product_type, exclude_scene=None): kpi_type = '{}_SHARE_MANUFACTURER_OUT_OF_STORE'.format(sos_type) kpi_fk = self.common.New_kpi_static_data[ self.common.New_kpi_static_data['type'] == kpi_type]['pk'].values[0] subset_filters = {'manufacturer_name': self.OwnMan} pop_filters = { 'template_display_name': (exclude_scene, 0), 'product_type': product_type } self.calculate_sos(sos_type, kpi_fk, self.OwnManFK, self.store_id, subset_filters, pop_filters) return def all_param_out_category(self, param_1, param_2, sos_type, product_type, exclude_scene=None, context=None): if not context: # only for own manufacturer out of category df = self.scif.loc[:, [param_1, param_2]] relevant_df = df.drop_duplicates(subset=param_2) kpi_type = '{}_SHARE_OWN_{}_OUT_OF_CATEGORY'.format( sos_type, (param_1.replace('_fk', '')).upper()) else: df = self.scif.loc[:, [param_1, param_2, context]] kpi_type = '{}_SHARE_{}_OUT_OF_CATEGORY'.format( sos_type, (param_1.replace('_fk', '')).upper()) relevant_df = df.drop_duplicates() kpi_fk = self.common.New_kpi_static_data[ self.common.New_kpi_static_data['type'] == kpi_type]['pk'].values[0] for i, row in relevant_df.iterrows(): if not context: pop_filters = { 'template_display_name': (exclude_scene, 0), 'product_type': product_type, param_2: row[param_2] } subset_filters = {param_1: self.OwnManFK} else: pop_filters = { 'template_display_name': (exclude_scene, 0), 'product_type': product_type, param_2: row[param_2], context: row[context] } subset_filters = {param_1: row[param_1]} if context: self.calculate_sos(sos_type, kpi_fk, row[param_1], row[param_2], subset_filters, pop_filters, row[context]) else: self.calculate_sos(sos_type, kpi_fk, self.OwnManFK, row[param_2], subset_filters, pop_filters) return def calculate_sos(self, sos_type, kpi_fk, numerator_fk, denominator_fk, subset_filters, pop_filters, context=None): ratio = 0 df = self.match_product_in_scene.merge(self.scif, how='left', on=['scene_fk', 'product_fk']) df = df.drop(['facings'], axis=1) # denominator pop_filter = self.common_sos.get_filter_condition(df, **pop_filters) # numerator subset_filter = self.common_sos.get_filter_condition( df, **subset_filters) try: ratio = self.calculate_sos_by_policy(sos_type, kpi_fk, numerator_fk, denominator_fk, subset_filter=subset_filter, pop_filter=pop_filter, context=context) except Exception as e: Log.debug( 'calculate_sos_facing_by_scene can not be calculated for scene {}' .format(e.message)) return ratio def calculate_sos_by_policy(self, sos_type, kpi_fk, numerator_fk, denominator_fk, subset_filter, pop_filter, context=None): matches = self.matches_with_direction.merge( self.scif, how='left', on=['scene_fk', 'product_fk']) den_df = matches[pop_filter] nom_df = den_df[subset_filter] if sos_type == 'CUB': # IN Top pallet scene type we should include only top facings: denominator = den_df.loc[(den_df['template_display_name'] == self.TOP_PALLET) & (den_df['image_direction'] == 'Top')]['product_fk'].count() + \ den_df.loc[~(den_df['template_display_name'] == self.TOP_PALLET) & (den_df['stacking_layer'] == 1)]['product_fk'].count() numerator = nom_df.loc[(nom_df['template_display_name'] == self.TOP_PALLET) & (nom_df['image_direction'] == 'Top')]['product_fk'].count() + \ nom_df.loc[~(nom_df['template_display_name'] == self.TOP_PALLET) & (nom_df['stacking_layer'] == 1)]['product_fk'].count() else: denominator = den_df['product_fk'].count() numerator = nom_df['product_fk'].count() if denominator != 0: ratio = (numerator / float(denominator)) self.common.write_to_db_result_new_tables(kpi_fk, numerator_fk, numerator, round(ratio, 2), denominator_fk, denominator, round(ratio, 2), context_id=context) return def update_custom_scif(self): # only for 'CUB Share' and 'TRAX Share' scenes = self.scif['scene_id'].unique().tolist() for scene in scenes: template_name = self.scif.loc[self.scif['scene_fk'] == scene][ 'template_display_name'].values[0] products = self.scif[(self.scif['scene_id'] == scene) & ( ~self.scif['product_type'].isin(self.PRODUCT_TYPES_TO_EXCLUDE) )]['product_fk'].unique().tolist() for product in products: facings_cub_share = None facings_trax_share = None if template_name == self.FRONT_PALLET: facings_trax_share = self.scif[ (self.scif['scene_id'] == scene) & (self.scif['product_fk'] == product )]['facings'].values[0] elif template_name == self.TOP_PALLET: if self.store_type == self.OFF_PREMISE: facings_cub_share = self.matches_with_direction[ (self.matches_with_direction['scene_fk'] == scene) & (self.matches_with_direction['product_fk'] == product) & (self.matches_with_direction['image_direction'] == 'Top')]['product_fk'].count() else: if self.store_type == self.OFF_PREMISE: facings_cub_share = self.scif[ (self.scif['scene_id'] == scene) & (self.scif['product_fk'] == product )]['facings_ign_stack'].values[0] facings_trax_share = self.scif[ (self.scif['scene_id'] == scene) & (self.scif['product_fk'] == product )]['facings'].values[0] self.common.get_custom_scif_query( scene_fk=scene, product_fk=product, in_assortment_OSA=facings_cub_share, oos_osa=facings_trax_share, mha_in_assortment=None, mha_oos=None, length_mm_custom=None) self.common.commit_custom_scif() return def get_match_product_in_scene_with_direction(self): query = CUBAUCUBAUQueries.get_facings_by_direction(self.session_uid) local_con = PSProjectConnector(self.project_name, DbUsers.CalculationEng) products_per_direction = pd.read_sql_query(query, local_con.db) matches_with_direction = self.match_product_in_scene.merge( products_per_direction, left_on='scene_match_fk', right_on='match_product_fk') return matches_with_direction
class Summary: def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.project_name = self.data_provider.project_name self.adjacency = Adjancency(self.data_provider) self.block = Block(self.data_provider) self.template_name = 'summary_kpis.xlsx' self.TEMPLATE_PATH = os.path.join( os.path.dirname(os.path.dirname(os.path.realpath(__file__))), 'Data', self.template_name) self.template_data = parse_template(self.TEMPLATE_PATH, "KPIs") self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.tools = GENERALToolBox(self.data_provider) self.common = Common(self.data_provider) self.kpi_results_queries = [] self.cub_tools = CUBAUCUBAUGENERALToolBox(self.data_provider, self.output) self.store_type = self.data_provider.store_type self.store_info = self.data_provider[Data.STORE_INFO] self.session_uid = self.data_provider.session_uid self.visit_date = self.data_provider.visit_date # @log_runtime('Main Calculation') def main_calculation(self): """ This function calculates the KPI results. """ self.calc_adjacency() self.calc_block() self.calc_competitor_adjacency() return def calc_adjacency(self): kpi_data = self.template_data.loc[self.template_data['KPI Type'] == ADJACENCY] for index, row in kpi_data.iterrows(): # kpi_filters = {} # kpi_filters['template_display_name'] = row['display name'].split(",") adjacent_products = self.calculate_relative_adjacency(row) if adjacent_products: self.write_to_db(row, 100, adjacent_products) else: self.write_to_db(row, 0, adjacent_products) return def calc_competitor_adjacency(self): kpi_data = self.template_data.loc[self.template_data['KPI Type'] == COMPETITOR_ADJACENCY] for index, row in kpi_data.iterrows(): kpi_filters = {} kpi_filters['template_display_name'] = row['display name'].split( ",") if row['Store additional attribute 1']: if not self.store_info['additional_attribute_1'].values[ 0] in row['Store additional attribute 1'].split(","): continue competitor_products = self.calculate_relative_adjacency( row, **kpi_filters) if competitor_products: self.write_to_db(row, 100, 1) else: self.write_to_db(row, 0, 0) return def calc_block(self): kpi_data = self.template_data.loc[self.template_data['KPI Type'] == BLOCK] for index, row in kpi_data.iterrows(): result = None kpi_filters = {} if row['store type']: if not row['store type'] == self.store_type: continue entity_type = row['Block Entity Type'] entity_value = row['Block Entity Value'].split(",") kpi_filters[entity_type] = entity_value kpi_filters['template_display_name'] = row['display name'].strip( ).split(",") threshold = row['Target'] if threshold: threshold = float(threshold) block_result = self.cub_tools.calculate_block_together( minimum_block_ratio=threshold, **kpi_filters) # block_result = self.block.calculate_block_together(minimum_block_ratio=threshold, **kpi_filters) #todo:open bug to Ilan filters, relevant_scenes = self.cub_tools.separate_location_filters_from_product_filters( **kpi_filters) score = 100 if block_result else 0 if len(relevant_scenes) == 0: result = 'No relevant products or scenes' score = None self.write_to_db(row, score, result) return def calculate_relative_adjacency(self, kpi_data, **general_filters): adjacencies = [] if kpi_data.empty: return None direction_data = { 'top': (1, 1), 'bottom': (1, 1), 'left': (1, 1), 'right': (1, 1) } entity_type = kpi_data['Block Entity Type'] entity_value = kpi_data['Block Entity Value'].split(",") tested_filters = {entity_type: entity_value} entity_to_return = kpi_data['Anchor Entity to return'] if kpi_data['Anchor Entity Type'] != '': anchor_filters = { kpi_data['Anchor Entity Type']: kpi_data['Anchor Entity Value'] } else: anchor_filters = None if not self.scif.empty: adjacencies = self.cub_tools.calculate_adjacency_relativeness( tested_filters, direction_data, entity_to_return, anchor_filters, **general_filters) if adjacencies: total_result = 0 for neighbor in list(set(adjacencies)): if str(neighbor) != 'nan': if not total_result: total_result = neighbor.replace("'", "\\'").encode('utf-8') else: total_result = total_result + ', ' + neighbor.replace( "'", "\\'").encode('utf-8') else: total_result = None # self.write_to_db(kpi_data, score, total_result) return str(total_result) def write_to_db(self, kpi_data, score, result=None): kpi_name = kpi_data['KPI'] kpi_fk = self.common.get_kpi_fk_by_kpi_name(kpi_name, 2) # store_fk = self.store_info.store_fk.values[0] atomic_kpi_fk = self.common.get_kpi_fk_by_kpi_name(kpi_name, 3) self.common.write_to_db_result(kpi_fk, 2, score) kwargs = self.common.create_basic_dict(atomic_kpi_fk) kwargs['display_text'] = kpi_name kwargs['result'] = result self.common.write_to_db_result(fk=kpi_fk, level=3, score=score, **kwargs)
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()
class CCZAToolBox: 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.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) # self.store_type = self.data_provider[Data.STORE_INFO]['store_type'].iloc[0] query_store_type = CCZAQueries.get_attr3(self.session_uid) store_type = pd.read_sql_query(query_store_type, self.rds_conn.db) self.store_type = store_type[Const.ATTR3].iloc[0] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.tools = GENERALToolBox(self.data_provider, self.output, rds_conn=self.rds_conn) self.survey_response = self.data_provider[Data.SURVEY_RESPONSES] self.tool_box_for_flow = ToolBox # create data-frames from template self.kpi_sheets = {} for name in Const.sheet_names_and_rows: self.kpi_sheets[name] = parse_template(TEMPLATE_PATH, sheet_name=name, lower_headers_row_index=Const.sheet_names_and_rows[name]) self.common = Common(self.data_provider, Const.RED_SCORE) self.survey_handler = Survey(self.data_provider, self.output, self.kpi_sheets[Const.SURVEY_QUESTIONS]) self.kpi_static_data = self.common.kpi_static_data self.kpi_results_queries = [] self.common_v2 = CommonV2(self.data_provider) self.own_manuf_fk = self.get_own_manufacturer_fk() self.scif_match_react = self.scif[self.scif[ScifConsts.RLV_SOS_SC] == 1] def get_own_manufacturer_fk(self): own_manufacturer_fk = self.data_provider.own_manufacturer.param_value.values[0] return int(float(own_manufacturer_fk)) def sos_main_calculation(self): store_sos_ident_par, store_facings = self.calculate_own_manufacturer_out_of_store() category_df = self.calculate_sos_category_out_of_store(store_sos_ident_par, store_facings) if not category_df.empty: manufacturer_cat_df = self.calculate_sos_manufacturer_out_of_category(category_df) self.calculate_sos_brand_out_of_manufacturer(manufacturer_cat_df) def calculate_own_manufacturer_out_of_store(self): manuf_out_of_store_fk = self.common_v2.get_kpi_fk_by_kpi_type(Const.SOS_OWN_MANUF_OUT_OF_STORE) store_sos_ident_par = self.common_v2.get_dictionary(kpi_fk=manuf_out_of_store_fk) num_result = self.scif_match_react[self.scif_match_react[ScifConsts.MANUFACTURER_FK] == self.own_manuf_fk] \ [ScifConsts.FACINGS_IGN_STACK].sum() denom_result = float(self.scif_match_react[ScifConsts.FACINGS_IGN_STACK].sum()) sos_result = num_result / denom_result if denom_result else 0 self.common_v2.write_to_db_result(fk=manuf_out_of_store_fk, numerator_id=self.own_manuf_fk, denominator_id=self.store_id, numerator_result=num_result, denominator_result=denom_result, score=sos_result * 100, result=sos_result, identifier_result=store_sos_ident_par, should_enter=True) return store_sos_ident_par, denom_result def calculate_sos_brand_out_of_manufacturer(self, manuf_df): kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(Const.SOS_BRAND_OUT_CAT) brand_man_cat_df = self.scif_match_react.groupby([ScifConsts.CATEGORY_FK, ScifConsts.MANUFACTURER_FK, ScifConsts.BRAND_FK], as_index=False).agg({ScifConsts.FACINGS_IGN_STACK: np.sum}) brand_man_cat_df = brand_man_cat_df.merge(manuf_df, on=[ScifConsts.CATEGORY_FK, ScifConsts.MANUFACTURER_FK], how='left') brand_man_cat_df['sos'] = brand_man_cat_df[ScifConsts.FACINGS_IGN_STACK] / brand_man_cat_df['man_cat_facings'] for i, row in brand_man_cat_df.iterrows(): self.common_v2.write_to_db_result(fk=kpi_fk, numerator_id=row[ScifConsts.BRAND_FK], denominator_id=row[ScifConsts.MANUFACTURER_FK], numerator_result=row[ScifConsts.FACINGS_IGN_STACK], denominator_result=row['man_cat_facings'], result=row['sos'], context_id=row[ScifConsts.CATEGORY_FK], score=row['sos'] * 100, identifier_parent=row['manuf_id_parent'], should_enter=True) def calculate_sos_manufacturer_out_of_category(self, cat_df): kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(Const.SOS_MANUF_OUT_OF_CAT) manuf_in_cat_df = self.scif_match_react.groupby([ScifConsts.CATEGORY_FK, ScifConsts.MANUFACTURER_FK], as_index=False).agg({ScifConsts.FACINGS_IGN_STACK: np.sum}) manuf_in_cat_df = manuf_in_cat_df.merge(cat_df, on=ScifConsts.CATEGORY_FK, how='left') manuf_in_cat_df['sos'] = manuf_in_cat_df[ScifConsts.FACINGS_IGN_STACK] / manuf_in_cat_df['category_facings'] manuf_in_cat_df['id_result'] = manuf_in_cat_df.apply(self.build_identifier_parent_man_in_cat, axis=1, args=(kpi_fk,)) for i, row in manuf_in_cat_df.iterrows(): self.common_v2.write_to_db_result(fk=kpi_fk, numerator_id=row[ScifConsts.MANUFACTURER_FK], denominator_id=row[ScifConsts.CATEGORY_FK], numerator_result=row[ScifConsts.FACINGS_IGN_STACK], denominator_result=row['category_facings'], result=row['sos'], score=row['sos'] * 100, identifier_parent=row['cat_id_parent'], identifier_result=row['id_result'], should_enter=True) manuf_in_cat_df.rename(columns={ScifConsts.FACINGS_IGN_STACK: 'man_cat_facings', 'id_result': 'manuf_id_parent'}, inplace=True) manuf_in_cat_df = manuf_in_cat_df[[ScifConsts.MANUFACTURER_FK, ScifConsts.CATEGORY_FK, 'man_cat_facings', 'manuf_id_parent']] return manuf_in_cat_df @staticmethod def build_identifier_parent_man_in_cat(row, kpi_fk): id_result_dict = {Const.KPI_FK: kpi_fk, ScifConsts.CATEGORY_FK: row[ScifConsts.CATEGORY_FK], ScifConsts.MANUFACTURER_FK: row[ScifConsts.MANUFACTURER_FK]} return id_result_dict def calculate_sos_category_out_of_store(self, store_sos_ident_par, store_facings): kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(Const.SOS_CAT_OUT_OF_STORE) cat_df = self.scif_match_react.groupby([ScifConsts.CATEGORY_FK], as_index=False).agg({ScifConsts.FACINGS_IGN_STACK: np.sum}) cat_df['sos'] = cat_df[ScifConsts.FACINGS_IGN_STACK] / store_facings cat_df['id_result'] = cat_df[ScifConsts.CATEGORY_FK].apply(lambda x: {ScifConsts.CATEGORY_FK: x, Const.KPI_FK: kpi_fk}) for i, row in cat_df.iterrows(): self.common_v2.write_to_db_result(fk=kpi_fk, numerator_id=row[ScifConsts.CATEGORY_FK], denominator_id=self.store_id, numerator_result=row[ScifConsts.FACINGS_IGN_STACK], denominator_result=store_facings, result=row['sos'], score=row['sos'] * 100, identifier_parent=store_sos_ident_par, identifier_result=row['id_result'], should_enter=True) cat_df.rename(columns={ScifConsts.FACINGS_IGN_STACK: 'category_facings', 'id_result': 'cat_id_parent'}, inplace=True) cat_df = cat_df[[ScifConsts.CATEGORY_FK, 'category_facings', 'cat_id_parent']] return cat_df def main_calculation_red_score(self): set_score = 0 try: set_name = self.kpi_sheets[Const.KPIS].iloc[len(self.kpi_sheets[Const.KPIS]) - 1][ Const.KPI_NAME] kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(set_name) set_identifier_res = self.common_v2.get_dictionary(kpi_fk=kpi_fk) if self.store_type in self.kpi_sheets[Const.KPIS].keys().tolist(): for i in xrange(len(self.kpi_sheets[Const.KPIS]) - 1): params = self.kpi_sheets[Const.KPIS].iloc[i] percent = self.get_percent(params[self.store_type]) if percent == 0: continue kpi_score = self.main_calculation_lvl_2(identifier_parent=set_identifier_res, params=params) set_score += kpi_score * percent else: Log.warning('The store-type "{}" is not recognized in the template'.format(self.store_type)) return kpi_names = {Const.column_name1: set_name} set_fk = self.get_kpi_fk_by_kpi_path(self.common.LEVEL1, kpi_names) if set_fk: try: self.common.write_to_db_result(score=set_score, level=self.common.LEVEL1, fk=set_fk) except Exception as exception: Log.error('Exception in the set {} writing to DB: {}'.format(set_name, exception.message)) self.common_v2.write_to_db_result(fk=kpi_fk, numerator_id=self.own_manuf_fk, denominator_id=self.store_id, score=set_score, result=set_score, identifier_result=set_identifier_res, should_enter=True) except Exception as exception: Log.error('Exception in the kpi-set calculating: {}'.format(exception.message)) pass def main_calculation_lvl_2(self, identifier_parent, *args, **kwargs): """ :param kwargs: dict - kpi line from the template. the function gets the kpi (level 2) row, and calculates its children. :return: float - score of the kpi. """ kpi_params = kwargs['params'] kpi_name = kpi_params[Const.KPI_NAME] kpi_score = 0.0 set_name = kpi_params[Const.KPI_GROUP] kpi_type = kpi_params[Const.KPI_TYPE] target = kpi_params[Const.TARGET] kpi_fk_lvl_2 = self.common_v2.get_kpi_fk_by_kpi_type(kpi_name) lvl_2_identifier_par = self.common_v2.get_dictionary(kpi_fk=kpi_fk_lvl_2) if kpi_name != Const.FLOW: for i in range(len(self.kpi_sheets[target])): if kpi_params[Const.WEIGHT_SHEET].strip(): target_series = self.kpi_sheets[target].iloc[i] weight_sheet = self.kpi_sheets[kpi_params[Const.WEIGHT_SHEET]] atomic_params = weight_sheet[(weight_sheet[Const.KPI_NAME] == target_series[Const.KPI_NAME]) & (weight_sheet[Const.ATOMIC_NAME] == target_series[Const.ATOMIC_NAME]) ].iloc[0] atomic_params[Const.targets_line] = target_series # atomic_params = self.kpi_sheets[kpi_params[Const.WEIGHT_SHEET]].iloc[i] # atomic_params[Const.targets_line] = self.kpi_sheets[target].iloc[i] else: atomic_params = self.kpi_sheets[target].iloc[i] percent = self.get_percent(atomic_params[self.store_type]) if percent == 0.0 or atomic_params[Const.KPI_NAME] != kpi_name: continue atomic_params[Const.type] = kpi_type atomic_score = self.calculate_atomic(atomic_params, set_name, lvl_2_identifier_par) if atomic_score is None: atomic_score = 0.0 Log.error('The calculated score is not good.') kpi_score += atomic_score * percent else: atomic_row = self.kpi_sheets[target].iloc[0] atomic_params = atomic_row.to_dict() atomic_params.update({Const.ATOMIC_NAME: kpi_name, Const.KPI_NAME: kpi_name, Const.type: kpi_name}) kpi_score = self.calculate_atomic(atomic_params, set_name, lvl_2_identifier_par) kpi_names = {Const.column_name1: set_name, Const.column_name2: kpi_name} kpi_fk = self.get_kpi_fk_by_kpi_path(self.common.LEVEL2, kpi_names) if kpi_fk: try: self.common.write_to_db_result(score=kpi_score, level=self.common.LEVEL2, fk=kpi_fk) except Exception as e: Log.error('Exception in the kpi {} writing to DB: {}'.format(kpi_name, e.message)) self.common_v2.write_to_db_result(fk=kpi_fk_lvl_2, numerator_id=self.own_manuf_fk, denominator_id=self.store_id, score=kpi_score, result=kpi_score, identifier_parent=identifier_parent, identifier_result=lvl_2_identifier_par, should_enter=True) return kpi_score def calculate_atomic(self, atomic_params, set_name, lvl_2_identifier_parent): """ :param atomic_params: dict - atomic kpi line from the template :param set_name: str - name of the set, for DB. the function gets the atomic (level 3) row, and calculates it by the options (availability/SOS/flow/survey). :return: float - score of the atomic kpi. """ atomic_name = atomic_params[Const.ATOMIC_NAME] kpi_name = atomic_params[Const.KPI_NAME] atomic_type = atomic_params[Const.type] if atomic_type == Const.AVAILABILITY: atomic_score = self.calculate_availability(atomic_params) elif atomic_type == Const.SOS_FACINGS: atomic_score = self.calculate_sos(atomic_params) elif atomic_type == Const.SURVEY_QUESTION: if Const.KPI_TYPE in atomic_params.keys() and atomic_params[Const.KPI_TYPE] != Const.SURVEY: atomic_score = self.calculate_survey_with_types(atomic_params) else: atomic_score = self.calculate_survey_with_codes(atomic_params) elif atomic_type == Const.FLOW: atomic_score = self.calculate_flow(atomic_params) else: atomic_score = 0.0 Log.error('The type "{}" is unknown'.format(atomic_type)) kpi_names = {Const.column_name1: set_name, Const.column_name2: kpi_name, Const.column_name3: atomic_name} atomic_fk = self.get_kpi_fk_by_kpi_path(self.common.LEVEL3, kpi_names) if atomic_fk: try: self.common.write_to_db_result(score=atomic_score, level=self.common.LEVEL3, fk=atomic_fk) except Exception as e: Log.error('Exception in the atomic-kpi {} writing to DB: {}'.format(atomic_name, e.message)) kpi_fk_lvl_3 = self.common_v2.get_kpi_fk_by_kpi_type(atomic_name) if atomic_name != Const.FLOW \ else self.common_v2.get_kpi_fk_by_kpi_type(Const.FLOW_LVL_3) self.common_v2.write_to_db_result(fk=kpi_fk_lvl_3, numerator_id=self.own_manuf_fk, denominator_id=self.store_id, result=atomic_score, score=atomic_score, identifier_parent=lvl_2_identifier_parent, should_enter=True) return atomic_score @kpi_runtime() def calculate_availability(self, atomic_params): """ :param atomic_params: dict - atomic kpi line from the template :return: 100 if this product is available, 0 otherwise. """ availability = self.calculate_all_availability(atomic_params[Const.ENTITY_TYPE], atomic_params[Const.ENTITY_VAL], atomic_params[Const.ENTITY_TYPE2], atomic_params[Const.ENTITY_VAL2], atomic_params[Const.IN_NOT_IN], Converters.convert_type(atomic_params[Const.TYPE_FILTER]), atomic_params[Const.VALUE_FILTER]) return 100.0 * (availability > 0) def calculate_all_availability(self, type1, value1, type2, value2, in_or_not, filter_type, filter_value): """ :param atomic_params: dict - atomic kpi line from the template. checks the kind of the survey and sends it to the match function :return: float - score. """ if not type1 or not value1: Log.warning('There is no type and value in the atomic availability') return 0.0 type1 = Converters.convert_type(type1) value1 = value1.split(',') value1 = map(lambda x: x.strip(), value1) type2 = Converters.convert_type(type2) value2 = value2 filters = {type1: value1} if type2 and value2: value2 = value2.split(',') value2 = map(lambda x: x.strip(), value2) filters[type2] = value2 if in_or_not: filters = self.update_filters(filters, in_or_not, filter_type, filter_value) return self.tools.calculate_availability(**filters) @kpi_runtime() def calculate_survey_with_types(self, atomic_params): """ :param atomic_params: dict - atomic kpi line from the template. checks the kind of the survey and sends it to the match function :return: float - score. """ atomic_score = 0.0 accepted_answer = atomic_params[Const.ACCEPTED_ANSWER_RESULT] atomic_type = atomic_params[Const.KPI_TYPE] if not accepted_answer: if atomic_type == Const.AVAILABILITY: accepted_answer = 1 else: Log.warning('There is no accepted answer to {} in the template'.format( atomic_params[Const.ATOMIC_NAME])) return atomic_score if atomic_type == Const.AVAILABILITY: availability = self.calculate_all_availability(atomic_params[Const.ENTITY_TYPE], atomic_params[Const.ENTITY_VAL], atomic_params[Const.ENTITY_TYPE2], atomic_params[Const.ENTITY_VAL2], atomic_params[Const.IN_NOT_IN], atomic_params[Const.TYPE_FILTER], atomic_params[Const.VALUE_FILTER]) atomic_score = 100.0 * (availability >= float(accepted_answer)) elif atomic_type == Const.SCENE_COUNT: count = self.calculate_scene_count(atomic_params) atomic_score = 100.0 * (count >= float(accepted_answer)) elif atomic_type == Const.PLANOGRAM: # atomic_score = self.calculate_planogram(atomic_params) atomic_score = self.calculate_planogram_new(atomic_params) else: Log.warning('The type "{}" is not recognized'.format(atomic_type)) return atomic_score @kpi_runtime() def calculate_survey_with_codes(self, atomic_params): """ :param atomic_params: dict - atomic kpi line from the template. checks survey question. :return: float - score. """ atomic_score = 0.0 try: code = str(int(float(atomic_params[Const.SURVEY_Q_CODE]))) except ValueError: Log.warning('The atomic "{}" has no survey code'.format(atomic_params[Const.ATOMIC_NAME])) return atomic_score survey_text = atomic_params[Const.ATOMIC_NAME] if Const.targets_line in atomic_params.keys(): try: accepted_answer = float(atomic_params[Const.targets_line][self.store_type]) except ValueError: Log.warning('The atomic "{}" has no target'.format(atomic_params[Const.ATOMIC_NAME])) return atomic_score survey_data = self.tools.survey_response[self.tools.survey_response['code'].isin([code])] if survey_data.empty: Log.warning('Survey with {} = {} does not exist'.format('code', code)) survey_data = self.tools.survey_response[ self.tools.survey_response['question_text'].isin([survey_text])] if survey_data.empty: return atomic_score survey_answer = survey_data['number_value'].iloc[0] check_survey = accepted_answer >= survey_answer else: survey_code_couple = ('code', code) accepted_answer = atomic_params[Const.ACCEPTED_ANSWER_RESULT] check_survey = self.tools.check_survey_answer(survey_code_couple, accepted_answer) if check_survey is None: check_survey = self.tools.check_survey_answer(survey_text, accepted_answer) if check_survey is None: return atomic_score atomic_score = 100.0 * check_survey return atomic_score def calculate_planogram(self, atomic_params): """ :param atomic_params: dict - atomic kpi line from the template :return: 100 if there is planogram, 0 otherwise. """ type_name = Converters.convert_type(atomic_params[Const.ENTITY_TYPE]) values = atomic_params[Const.ENTITY_VAL].split(',') values = map(lambda x: x.strip(), values) wanted_answer = float(atomic_params[Const.ACCEPTED_ANSWER_RESULT]) filtered_scenes = self.scif[self.scif[type_name].isin(values)]['scene_id'].unique() count = 0 for scene_id in filtered_scenes: query = CCZAQueries.getPlanogramByTemplateName(scene_id) planogram = pd.read_sql_query(query, self.rds_conn.db) if 1 in planogram['match_compliance_status'].unique().tolist(): count += 1 if count >= wanted_answer: return 100.0 return 0.0 def calculate_planogram_new(self, atomic_params): """ :param atomic_params: dict - atomic kpi line from the template :return: 100 if there is scene which has at least one correctly positioned product, 0 otherwise. """ type_name = Converters.convert_type(atomic_params[Const.ENTITY_TYPE]) values = map(lambda x: x.strip(), atomic_params[Const.ENTITY_VAL].split(',')) wanted_answer = float(atomic_params[Const.ACCEPTED_ANSWER_RESULT]) filtered_scenes = self.scif[self.scif[type_name].isin(values)][ScifConsts.SCENE_FK].unique() scenes_passing = 0 for scene in filtered_scenes: incor_tags = self.match_product_in_scene[(self.match_product_in_scene[ScifConsts.SCENE_FK] == scene) & (~(self.match_product_in_scene[MatchesConsts.COMPLIANCE_STATUS_FK] == 3))] if len(incor_tags) == 0: scenes_passing += 1 score = 100 if scenes_passing >= wanted_answer else 0 return score def calculate_scene_count(self, atomic_params): """ :param atomic_params: dict - atomic kpi line from the template :return: int - amount of scenes. """ filters = {Converters.convert_type( atomic_params[Const.ENTITY_TYPE]): map(lambda x: x.strip(), atomic_params[Const.ENTITY_VAL].split(','))} scene_count = self.tools.calculate_number_of_scenes(**filters) return scene_count @kpi_runtime() def calculate_sos(self, atomic_params): """ :param atomic_params: dict - atomic kpi line from the template :return: the percent of SOS (if it's binary - 100 if more than target, otherwise 0). """ numerator_type = atomic_params[Const.ENTITY_TYPE_NUMERATOR] numerator_value = atomic_params[Const.NUMERATOR] denominator_type = atomic_params[Const.ENTITY_TYPE_DENOMINATOR] denominator_value = atomic_params[Const.DENOMINATOR] in_or_not = atomic_params[Const.IN_NOT_IN] filter_type = Converters.convert_type(atomic_params[Const.TYPE_FILTER]) filter_value = atomic_params[Const.VALUE_FILTER] denominator_filters = self.get_default_filters(denominator_type, denominator_value) numerator_filters = self.get_default_filters(numerator_type, numerator_value) if in_or_not: numerator_filters = self.update_filters(numerator_filters, in_or_not, filter_type, filter_value) denominator_filters = self.update_filters(denominator_filters, in_or_not, filter_type, filter_value) atomic_score = self.tools.calculate_share_of_shelf( sos_filters=numerator_filters, **denominator_filters) * 100 if atomic_params[Const.SCORE] == Const.BINARY: try: return 100 * (atomic_score >= float(atomic_params[Const.targets_line][ self.store_type])) except ValueError: Log.warning('The target for {} is bad in store {}'.format( atomic_params[Const.ATOMIC_NAME], self.store_type)) return 0.0 elif atomic_params[Const.SCORE] != Const.NUMERIC: Log.error('The score is not numeric and not binary.') return atomic_score def update_filters(self, filters, in_or_not, filter_type, filter_value): """ :param filters: the source filters as dict. :param in_or_not: boolean if it should include or not include :param filter_type: str :param filter_value: str adds to exist filter if to include/exclude one more condition. :return: dict - the updated filter. """ filter_type = Converters.convert_type(filter_type) if "Not" in in_or_not: list_of_negative = list(self.scif[~(self.scif[filter_type] == filter_value)][filter_type].unique()) filters[filter_type] = list_of_negative elif "In" in in_or_not: filters[filter_type] = filter_value else: Log.warning('The value in "In/Not In" in the template should be "Not in", "In" or empty') return filters @kpi_runtime() def calculate_flow(self, atomic_params): """ checking if the shelf is sorted like the brands list. :return: 100 if it's fine, 0 otherwise. """ population_entity_type = Converters.convert_type(atomic_params[Const.ENTITY_TYPE]) progression_list = map(lambda x: x.strip(), atomic_params[Const.ENTITY_VAL].split(',')) location_entity_type = Converters.convert_type(atomic_params[Const.TYPE_FILTER]) location_values = map(lambda x: x.strip(), atomic_params[Const.VALUE_FILTER].split(',')) is_in_param = False if atomic_params[Const.IN_NOT_IN] == u'Not in' else True filter_loc_param = self.scif[location_entity_type].isin(location_values) if is_in_param else\ ~self.scif[location_entity_type].isin(location_values) filtered_scif = self.scif[ filter_loc_param & (self.scif['tagged'] >= 1) & (self.scif[population_entity_type].isin(progression_list))] join_on = ['scene_fk', 'product_fk'] match_product_join_scif = pd.merge(filtered_scif, self.match_product_in_scene, on=join_on, how='left', suffixes=('_x', '_matches')) progression_field = 'brand_name' group_column = 'scene_fk' progression_cross_shelves_true = (self.tool_box_for_flow.progression( df=match_product_join_scif, progression_list=progression_list, progression_field=progression_field, at_least_one=False, left_to_right=False, cross_bays=True, cross_shelves=True, include_stacking=False, group_by=group_column)) progression_cross_shelves_false = (self.tool_box_for_flow.progression( df=match_product_join_scif, progression_list=progression_list, progression_field=progression_field, at_least_one=False, left_to_right=False, cross_bays=True, cross_shelves=False, include_stacking=False, group_by=group_column)) return 100.0 * (progression_cross_shelves_true or progression_cross_shelves_false) def get_kpi_fk_by_kpi_path(self, kpi_level, kpi_names): """ :param kpi_level: int - kpi level. :param kpi_names: dict - all the path from the set. :return: int - the fk of this kpi in the DB. """ for name in kpi_names: assert isinstance(kpi_names[name], (unicode, basestring)), "name is not a string: %r" % kpi_names[name] try: if kpi_level == self.common.LEVEL1: return self.kpi_static_data[self.kpi_static_data[Const.column_name1] == kpi_names[Const.column_name1]][ Const.column_key1].values[0] elif kpi_level == self.common.LEVEL2: return self.kpi_static_data[ (self.kpi_static_data[Const.column_name1] == kpi_names[Const.column_name1]) & (self.kpi_static_data[Const.column_name2] == kpi_names[Const.column_name2])][ Const.column_key2].values[0] elif kpi_level == self.common.LEVEL3: return self.kpi_static_data[ (self.kpi_static_data[Const.column_name1].str.encode('utf8') == kpi_names[Const.column_name1].encode('utf8')) & (self.kpi_static_data[Const.column_name2].str.encode('utf8') == kpi_names[Const.column_name2].encode('utf8')) & (self.kpi_static_data[Const.column_name3].str.encode('utf8') == kpi_names[Const.column_name3].encode('utf8'))][ Const.column_key3].values[0] else: raise ValueError, 'invalid level' except IndexError: Log.info('Kpi path: {}, is not equal to any kpi name in static table'.format(kpi_names)) return None @staticmethod def get_default_filters(type_name, value_name): """ :param type_name: string that contains list of types :param value_name: string that contains list of values in the same length :return: filter as dict. """ if ',' in type_name: types = type_name.split(',') types = map(lambda x: x.strip(), types) values = value_name.split(',') values = map(lambda x: x.strip(), values) filters = {} if len(types) != len(values): Log.warning('there are {} types and {} values, should be the same amount'.format( len(types), len(values))) else: for i in xrange(len(types)): filters[Converters.convert_type(types[i])] = values[i] else: filters = {Converters.convert_type(type_name): map(lambda x: x.strip(), value_name.split(','))} return filters @staticmethod def get_percent(num): """ :param num: a cell from the template, can be float in percent, int or string for 0 :return: the number divided by 100 """ try: answer = float(num) ans = answer if answer < 1 else answer / 100.0 if ans < 0 or ans > 100: Log.error('The weight is {} in the template, not possible.'.format(ans)) return 0.0 return ans except ValueError: return 0.0
class PS3SandToolBox: def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.project_name = data_provider.project_name self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common, menu=True) self.template_handler = TemplateHandler(self.project_name) def main_calculation(self): """ This function calculates the KPI results. """ # SOS Out Of The Box kpis self.diageo_generator.activate_ootb_kpis(self.commonV2) # sos by scene type self.diageo_generator.sos_by_scene_type(self.commonV2) log_runtime('Updating templates') self.template_handler.update_templates() # 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']) # Brand Blocking Global function template_data = self.template_handler.download_template(DiageoKpiNames.BRAND_BLOCKING) res_dict = self.diageo_generator.diageo_global_block_together( kpi_name=DiageoKpiNames.BRAND_BLOCKING, set_templates_data=template_data) self.commonV2.save_json_to_new_tables(res_dict) # Global Relative Position function template_data = self.template_handler.download_template(DiageoKpiNames.RELATIVE_POSITION) res_dict = self.diageo_generator.diageo_global_relative_position_function( template_data, location_type='template_name') self.commonV2.save_json_to_new_tables(res_dict) # Global Vertical Shelf Placement function template_data = self.template_handler.download_template(DiageoKpiNames.VERTICAL_SHELF_PLACEMENT) res_dict = self.diageo_generator.diageo_global_vertical_placement(template_data) self.commonV2.save_json_to_new_tables(res_dict) # committing to the new tables self.commonV2.commit_results_data() # committing to the old tables self.common.commit_results_data()
class DIAGEOUKToolBox: def __init__(self, data_provider, output): self.data_provider = data_provider self.project_name = self.data_provider.project_name self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.output = output self.common = Common(self.data_provider) self.commonV2 = CommonV2(self.data_provider) self.diageo_generator = DIAGEOGenerator(self.data_provider, self.output, self.common, menu=True) self.template_handler = TemplateHandler(self.project_name) def main_calculation(self): """ This function calculates the KPI results. """ log_runtime('Updating templates') self.template_handler.update_templates() # 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 Equipment score kpis 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 Relative Position function template_data = self.template_handler.download_template( DiageoKpiNames.RELATIVE_POSITION) res_dict = self.diageo_generator.diageo_global_relative_position_function( template_data, location_type='template_name') self.commonV2.save_json_to_new_tables(res_dict) # 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']) # Global Visible to Consumer 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) self.commonV2.save_json_to_new_tables(res_dict) # committing to new tables self.commonV2.commit_results_data() # committing to the old tables self.common.commit_results_data() 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