def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.set_up_template = pd.read_excel(os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data',
'gsk_set_up.xlsx'),
sheet_name='Functional KPIs',
keep_default_na=False)
self.gsk_generator = GSKGenerator(self.data_provider, self.output,
self.common, self.set_up_template)
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.templates = self.data_provider[Data.TEMPLATES]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.template = self.data_provider.all_templates # templates
self.kpi_static_data = self.common.get_new_kpi_static_data()
self.toolbox = GENERALToolBox(data_provider)
kpi_path = os.path.dirname(os.path.realpath(__file__))
base_file = os.path.basename(kpi_path)
self.exclude_filters = pd.read_excel(os.path.join(
kpi_path[:-len(base_file)], 'Data', 'template.xlsx'),
sheetname="Exclude")
self.Include_filters = pd.read_excel(os.path.join(
kpi_path[:-len(base_file)], 'Data', 'template.xlsx'),
sheetname="Include")
self.bay_count_kpi = pd.read_excel(os.path.join(
kpi_path[:-len(base_file)], 'Data', 'template.xlsx'),
sheetname="BayCountKPI")
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.k_engine = BaseCalculationsGroup(data_provider, output)
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.templates = {}
self.session_id = self.data_provider.session_id
self.score_templates = {}
self.get_templates()
self.get_score_template()
self.manufacturer_fk = self.all_products[
self.all_products['manufacturer_name'] == 'Coca Cola'].iloc[0]
self.sos = SOS(self.data_provider, self.output)
self.total_score = 0
self.session_fk = self.data_provider[Data.SESSION_INFO]['pk'].iloc[0]
self.toolbox = GENERALToolBox(self.data_provider)
self.scenes_info = self.data_provider[Data.SCENES_INFO]
self.kpi_results_new_tables_queries = []
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.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.united_scenes = self.get_united_scenes() # we don't need to check scenes without United products
self.survey = Survey(self.data_provider, self.output)
self.sos = SOS(self.data_provider, self.output)
self.templates = {}
self.common_db = Common(self.data_provider, CMA_COMPLIANCE)
self.region = self.store_info['region_name'].iloc[0]
self.store_type = self.store_info['store_type'].iloc[0]
self.program = self.store_info['additional_attribute_14'].iloc[0]
self.sales_center = self.store_info['additional_attribute_5'].iloc[0]
if self.store_type in STORE_TYPES: #####
self.store_type = STORE_TYPES[self.store_type] ####
self.store_attr = self.store_info['additional_attribute_15'].iloc[0]
self.kpi_static_data = self.common_db.get_kpi_static_data()
self.total_score = 0
for sheet in Const.SHEETS_CMA:
self.templates[sheet] = pd.read_excel(TEMPLATE_PATH, sheetname=sheet).fillna('')
def __init__(self, data_provider, output):
self.data_provider = data_provider
self.output = output
self.project_name = data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.tool_box = HEINEKENTWToolBox(self.data_provider, self.output)
self.common = Common(data_provider)
def __init__(self, data_provider, output):
self.data_provider = data_provider
self.output = output
self.project_name = data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.common = Common(data_provider)
self.tool_box = DIAGEODISPUSToolBox(self.data_provider, self.output, self.common)
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.templates_path = os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data')
self.excel_file_path = os.path.join(self.templates_path,
'inbevtradmx_template.xlsx')
self.availability = Availability(self.data_provider)
self.survey_response = self.data_provider[Data.SURVEY_RESPONSES]
self.geo = GeoLocation.Geo(self.rds_conn, self.session_uid,
self.data_provider, self.kpi_static_data,
self.common)
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.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.template_info = self.data_provider.all_templates
self.rds_conn = ProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.ps_data_provider = PsDataProvider(self.data_provider)
self.thresholds_and_results = {}
self.result_df = []
self.writing_to_db_time = datetime.timedelta(0)
self.kpi_results_queries = []
self.potential_products = {}
self.shelf_square_boundaries = {}
self.average_shelf_values = {}
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.all_template_data = parse_template(TEMPLATE_PATH, "KPI")
self.spacing_template_data = parse_template(TEMPLATE_PATH, "Spacing")
self.fixture_width_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE,
"Fixture Width",
dtype=pd.Int64Dtype())
self.facings_to_feet_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE,
"Conversion Table",
dtype=pd.Int64Dtype())
self.header_positions_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE,
"Header Positions")
self.flip_sign_positions_template = pd.read_excel(
FIXTURE_WIDTH_TEMPLATE, "Flip Sign Positions")
self.custom_entity_data = self.ps_data_provider.get_custom_entities(
1005)
self.ignore_stacking = False
self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack'
self.INCLUDE_FILTER = 1
self.assortment = Assortment(self.data_provider,
output=self.output,
ps_data_provider=self.ps_data_provider)
self.store_assortment = self.assortment.get_lvl3_relevant_ass()
self.kpi_new_static_data = self.common.get_new_kpi_static_data()
try:
self.mpis = self.match_product_in_scene.merge(self.products, on='product_fk', suffixes=['', '_p']) \
.merge(self.scene_info, on='scene_fk', suffixes=['', '_s']) \
.merge(self.template_info, on='template_fk', suffixes=['', '_t'])
except KeyError:
Log.warning('MPIS cannot be generated!')
return
self.adp = AltriaDataProvider(self.data_provider)
def __init__(self, data_provider, output, calculation_type, common_db2):
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.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.scif = self.scif[self.scif['product_type'] != "Irrelevant"]
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.templates = {}
self.result_values = self.ps_data_provider.get_result_values()
self.calculation_type = calculation_type
if self.calculation_type == Const.SOVI:
self.TEMPLATE_PATH = Const.TEMPLATE_PATH
self.RED_SCORE = Const.RED_SCORE
self.RED_SCORE_INTEG = Const.RED_SCORE_INTEG
for sheet in Const.SHEETS:
self.templates[sheet] = pd.read_excel(self.TEMPLATE_PATH, sheetname=sheet).fillna('')
self.converters = self.templates[Const.CONVERTERS]
self.scenes_results = self.ps_data_provider.get_scene_results(
self.scene_info['scene_fk'].drop_duplicates().values)
self.scenes_results = self.scenes_results[[Const.DB_RESULT, Const.DB_SCENE_FK, Const.DB_SCENE_KPI_FK]]
else:
self.TEMPLATE_PATH = Const.SURVEY_TEMPLATE_PATH
self.RED_SCORE = Const.MANUAL_RED_SCORE
self.RED_SCORE_INTEG = Const.MANUAL_RED_SCORE_INTEG
for sheet in Const.SHEETS_MANUAL:
self.templates[sheet] = pd.read_excel(self.TEMPLATE_PATH, sheetname=sheet).fillna('')
self.store_attr = self.store_info['additional_attribute_15'].iloc[0]
self.toolbox = FunctionsToolBox(self.data_provider, self.output, self.templates, self.store_attr)
self.common_db_integ = Common(self.data_provider, self.RED_SCORE_INTEG)
self.kpi_static_data_integ = self.common_db_integ.get_kpi_static_data()
self.common_db = Common(self.data_provider, self.RED_SCORE)
self.common_db2 = common_db2
self.region = self.store_info['region_name'].iloc[0]
self.store_type = self.store_info['store_type'].iloc[0]
if self.store_type in Const.STORE_TYPES:
self.store_type = Const.STORE_TYPES[self.store_type]
self.kpi_static_data = self.common_db.get_kpi_static_data()
main_template = self.templates[Const.KPIS]
self.templates[Const.KPIS] = main_template[(main_template[Const.REGION] == self.region) &
(main_template[Const.STORE_TYPE] == self.store_type)]
self.session_results = pd.DataFrame(columns=Const.COLUMNS_OF_RESULTS)
self.all_results = pd.DataFrame(columns=Const.COLUMNS_OF_RESULTS)
self.used_scenes = []
self.red_score = 0
self.set_fk = self.common_db2.get_kpi_fk_by_kpi_name(self.RED_SCORE)
self.set_integ_fk = self.common_db2.get_kpi_fk_by_kpi_name(self.RED_SCORE_INTEG)
self.weight_factor = self.get_weight_factor()
def __init__(self, data_provider, output, calculation_type):
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.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.scif = self.scif[self.scif['product_type'] != "Irrelevant"]
self.united_scenes = self.get_united_scenes(
) # we don't need to check scenes without United products
self.survey = Survey(self.data_provider, self.output)
self.templates = {}
self.calculation_type = calculation_type
if self.calculation_type == Const.SOVI:
self.TEMPLATE_PATH = TEMPLATE_PATH
self.RED_SCORE = Const.RED_SCORE
self.RED_SCORE_INTEG = Const.RED_SCORE_INTEG
for sheet in Const.SHEETS:
self.templates[sheet] = pd.read_excel(
self.TEMPLATE_PATH, sheetname=sheet).fillna('')
self.converters = self.templates[Const.CONVERTERS]
else:
self.TEMPLATE_PATH = SURVEY_TEMPLATE_PATH
self.RED_SCORE = Const.MANUAL_RED_SCORE
self.RED_SCORE_INTEG = Const.MANUAL_RED_SCORE_INTEG
for sheet in Const.SHEETS_MANUAL:
self.templates[sheet] = pd.read_excel(
self.TEMPLATE_PATH, sheetname=sheet).fillna('')
self.common_db_integ = Common(self.data_provider, self.RED_SCORE_INTEG)
self.kpi_static_data_integ = self.common_db_integ.get_kpi_static_data()
self.common_db = Common(self.data_provider, self.RED_SCORE)
self.region = self.store_info['region_name'].iloc[0]
self.store_type = self.store_info['store_type'].iloc[0]
if self.store_type in STORE_TYPES: #####
self.store_type = STORE_TYPES[self.store_type] ####
self.store_attr = self.store_info['additional_attribute_15'].iloc[0]
self.kpi_static_data = self.common_db.get_kpi_static_data()
main_template = self.templates[Const.KPIS]
self.templates[Const.KPIS] = main_template[
(main_template[Const.REGION] == self.region)
& (main_template[Const.STORE_TYPE] == self.store_type)]
self.scene_calculator = CCBOTTLERSUSSceneRedToolBox(
data_provider, output, self.templates, self)
self.scenes_results = pd.DataFrame(columns=Const.COLUMNS_OF_SCENE)
self.session_results = pd.DataFrame(columns=Const.COLUMNS_OF_SESSION)
self.all_results = pd.DataFrame(columns=Const.COLUMNS_OF_SCENE)
self.used_scenes = []
self.red_score = 0
self.weight_factor = self.get_weight_factor()
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.common2 = Common2(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.templates = self.data_provider.all_templates
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.templates_path = os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data')
self.excel_file_path = os.path.join(self.templates_path,
'inbevtradmx_template_11_v3.xlsx')
self.availability = Availability(self.data_provider)
self.survey_response = self.data_provider[Data.SURVEY_RESPONSES]
self.geo = GeoLocation.INBEVTRADMXGeo(self.rds_conn, self.session_uid,
self.data_provider,
self.kpi_static_data,
self.common, self.common2)
self.new_static_data = self.common2.kpi_static_data
self.manufacturer_fk = 1
self.match_displays_in_scene = self.data_provider.match_display_in_scene
self.mpis = self.mpis_merger()
self.all_data = pd.merge(self.scif,
self.match_product_in_scene[[
'product_fk', 'shelf_number', 'scene_fk',
'facing_sequence_number'
]],
how="inner",
left_on=['item_id', 'scene_id'],
right_on=['product_fk',
'scene_fk']).drop_duplicates()
self.ignore_stacking = False
self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack'
self.INCLUDE_FILTER = 1
self.EXCLUDE_FILTER = 0
self.CONTAIN_FILTER = 2
self.EXCLUDE_EMPTY = False
self.INCLUDE_EMPTY = True
class NESTLEUSToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
def main_calculation(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
score = 0
return score
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
def __init__(self, data_provider, output):
self.data_provider = data_provider
self.output = output
self.project_name = data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.tool_box = MOLSONCOORSHR_SANDToolBox(self.data_provider,
self.output)
self.common = Common(data_provider)
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.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.template_info = self.data_provider.all_templates
self.rds_conn = ProjectConnector(self.project_name, DbUsers.CalculationEng)
self.ps_data_provider = PsDataProvider(self.data_provider)
self.match_product_in_probe_state_reporting = self.ps_data_provider.get_match_product_in_probe_state_reporting()
self.kpi_results_queries = []
self.fixture_width_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Fixture Width", dtype=pd.Int64Dtype())
self.facings_to_feet_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Conversion Table", dtype=pd.Int64Dtype())
self.header_positions_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Header Positions")
self.flip_sign_positions_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Flip Sign Positions")
self.custom_entity_data = self.ps_data_provider.get_custom_entities(1005)
self.ignore_stacking = False
self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack'
self.kpi_new_static_data = self.common.get_new_kpi_static_data()
try:
self.mpis = self.match_product_in_scene.merge(self.products, on='product_fk', suffixes=['', '_p']) \
.merge(self.scene_info, on='scene_fk', suffixes=['', '_s']) \
.merge(self.template_info, on='template_fk', suffixes=['', '_t'])
except KeyError:
Log.warning('MPIS cannot be generated!')
return
self.adp = AltriaDataProvider(self.data_provider)
self.active_kpis = self._get_active_kpis()
self.external_targets = self.ps_data_provider.get_kpi_external_targets()
self.survey_dot_com_collected_this_session = self._get_survey_dot_com_collected_value()
self._add_smart_attributes_to_mpis()
self.scene_graphs = {}
self.excessive_flipsigns = False
self.incorrect_tags_in_pos_areas = []
class Generator:
def __init__(self, data_provider, output):
self.data_provider = data_provider
self.output = output
self.project_name = data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.tool_box = HEINEKENTWToolBox(self.data_provider, self.output)
self.common = Common(data_provider)
@log_runtime('Total Calculations', log_start=True)
def main_function(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
"""
if self.tool_box.scif.empty:
Log.warning('Scene item facts is empty for this session')
self.tool_box.main_calculation()
self.common.commit_results_data()
class Generator:
def __init__(self, data_provider, output):
self.data_provider = data_provider
self.output = output
self.project_name = data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.common = Common(data_provider)
self.tool_box = DIAGEODISPUSToolBox(self.data_provider, self.output, self.common)
@log_runtime('Total Calculations', log_start=True)
def main_function(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
"""
if self.tool_box.manual_collection_number.empty:
Log.warning('Manual collection numbers is empty for this session')
return
self.tool_box.main_calculation()
self.common.commit_results_data_to_new_tables()
def __init__(self, data_provider, output, common_db2):
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.manufacturer_fk = 1
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.united_scenes = self.get_united_scenes(
) # we don't need to check scenes without United products
self.survey = Survey(self.data_provider, self.output)
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.sos = SOS(self.data_provider, self.output)
self.templates = {}
self.common_db = Common(self.data_provider, SUB_PROJECT)
self.common_db2 = common_db2
self.result_values = self.ps_data_provider.get_result_values()
self.region = self.store_info['region_name'].iloc[0]
self.store_type = self.store_info['store_type'].iloc[0]
self.program = self.store_info['additional_attribute_14'].iloc[0]
self.sales_center = self.store_info['additional_attribute_5'].iloc[0]
if self.store_type in STORE_TYPES:
self.store_type = STORE_TYPES[self.store_type]
self.store_attr = self.store_info['additional_attribute_15'].iloc[0]
self.kpi_static_data = self.common_db.get_kpi_static_data()
self.ignore_stacking = False
self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack'
self.total_score = 0
self.total_count = 0
for sheet in Const.SHEETS_CMA:
self.templates[sheet] = pd.read_excel(TEMPLATE_PATH,
sheetname=sheet).fillna('')
self.tools = Shared(self.data_provider, self.output)
class Generator:
def __init__(self, data_provider, output):
self.data_provider = data_provider
self.output = output
self.project_name = data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.tool_box = CCAAUToolBox(self.data_provider, self.output)
self.common = Common(data_provider)
@log_runtime('Total Calculations', log_start=True)
def main_function(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
"""
if self.tool_box.scif.empty:
Log.warning('Scene item facts is empty for this session')
# for kpi_set_fk in self.tool_box.kpi_static_data['kpi_set_fk'].unique().tolist():
# score = self.tool_box.main_calculation(kpi_set_fk=kpi_set_fk)
# self.common.write_to_db_result(kpi_set_fk, self.tool_box.LEVEL1, score)
self.tool_box.main_calculation()
self.common.commit_results_data()
def __init__(self, data_provider, output, common_v2):
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.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.scif = self.scif[~(self.scif['product_type'] == 'Irrelevant')]
self.sw_scenes = self.get_sw_scenes(
) # we don't need to check scenes without United products
self.survey = Survey(self.data_provider, self.output)
self.sos = SOS(self.data_provider, self.output)
self.templates = {}
self.common_db = Common(self.data_provider, CMA_COMPLIANCE)
self.common_db2 = common_v2
self.common_scene = CommonV2(self.data_provider)
self.region = self.store_info['region_name'].iloc[0]
self.store_type = self.store_info['store_type'].iloc[0]
self.program = self.store_info['additional_attribute_3'].iloc[0]
self.sales_center = self.store_info['additional_attribute_5'].iloc[0]
if self.store_type in STORE_TYPES: #####
self.store_type = STORE_TYPES[self.store_type] ####
self.store_attr = self.store_info['additional_attribute_3'].iloc[0]
self.kpi_static_data = self.common_db.get_kpi_static_data()
self.total_score = 0
self.sub_scores = defaultdict(int)
self.sub_totals = defaultdict(int)
self.ignore_stacking = False
self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack'
for sheet in Const.SHEETS_CMA:
self.templates[sheet] = pd.read_excel(TEMPLATE_PATH,
sheetname=sheet).fillna('')
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.template_data = pd.read_excel(TEMPLATE_PATH, 'KPIs').fillna('')
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_new_kpi_static_data()
self.kpi_static_data = self.kpi_static_data[self.kpi_static_data['kpi_family_fk'] == CCPHLConsts.CUSTOM_KPIS]
self.kpi_results_queries = []
self.mapping_param = {"manufacturer": "manufacturer_name"}
self.mapping_entity = {"manufacturer": "manufacturer_fk", "store": "store_id",
"scene_type": "template_fk", "brand": "brand_fk", "product": "product_fk"}
def main_function(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
"""
Common(self.data_provider).commit_results_data()
self.calculate_red_score(
) # should be first, because it can include a deletion from the common
# # self.calculate_bci()
self.calculate_manufacturer_displays()
self.calculate_cma_compliance()
self.calculate_sovi()
self.calculate_ara()
self.calculate_msc()
self.calculate_liberty()
self.common_db.commit_results_data()
def main_function(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
"""
Common(self.data_provider).commit_results_data()
self.calculate_red_score()
# self.calculate_bci()
# self.calculate_manufacturer_displays()
self.calculate_cma_compliance()
# self.calculate_cma_compliance_sw()
self.calculate_warehouse_juice()
self.calculate_sovi()
self.calculate_msc()
self.calculate_liberty()
self.calculate_contact_center()
self.calculate_facings_by_shelf()
self.common_v2.commit_results_data() # saves results to new tables
def __init__(self, data_provider, output, common_db2):
self.output = output
self.data_provider = data_provider
self.common_db = Common(self.data_provider, SUB_PROJECT)
self.common_db2 = common_db2
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.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.scif = self.scif[~(self.scif['product_type'] == 'Irrelevant')]
self.sw_scenes = self.get_relevant_scenes(
) # we don't need to check scenes without United products
self.survey = Survey(self.data_provider, self.output)
self.sos = SOS(self.data_provider, self.output)
self.results = self.data_provider[Data.SCENE_KPI_RESULTS]
self.region = self.store_info['region_name'].iloc[0]
self.store_type = self.store_info['store_type'].iloc[0]
self.program = self.store_info['additional_attribute_3'].iloc[0]
self.sales_center = self.store_info['additional_attribute_5'].iloc[0]
if self.store_type in STORE_TYPES: #####
self.store_type = STORE_TYPES[self.store_type] ####
self.store_attr = self.store_info['additional_attribute_3'].iloc[0]
# self.kpi_static_data = self.common_db.get_kpi_static_data()
self.ignore_stacking = False
self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack'
self.sub_scores = defaultdict(int)
self.sub_totals = defaultdict(int)
self.templates = self.get_template()
self.hierarchy = self.templates[Const.KPIS].set_index(
Const.KPI_NAME)[Const.PARENT].to_dict()
self.templates = self.get_relevant_template(self.templates)
self.children = self.templates[Const.KPIS][Const.KPI_NAME]
self.tools = Shared(self.data_provider, self.output)
class CCAAUToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
EXCLUDE_FILTER = 0
INCLUDE_FILTER = 1
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.templates = self.data_provider[Data.TEMPLATES]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.template = self.data_provider.all_templates # templates
self.kpi_static_data = self.common.get_new_kpi_static_data()
self.toolbox = GENERALToolBox(data_provider)
kpi_path = os.path.dirname(os.path.realpath(__file__))
base_file = os.path.basename(kpi_path)
self.exclude_filters = pd.read_excel(os.path.join(
kpi_path[:-len(base_file)], 'Data', 'template.xlsx'),
sheetname="Exclude")
self.Include_filters = pd.read_excel(os.path.join(
kpi_path[:-len(base_file)], 'Data', 'template.xlsx'),
sheetname="Include")
self.bay_count_kpi = pd.read_excel(os.path.join(
kpi_path[:-len(base_file)], 'Data', 'template.xlsx'),
sheetname="BayCountKPI")
def main_calculation(self):
"""
This function calculates the KPI results.
"""
self.calculate_sos()
self.calculate_bay_kpi()
self.common.commit_results_data_to_new_tables()
def calculate_sos(self):
"""
This function filtering Data frame - "scene item facts" by the parameters in the template.
Sending the filtered data frames to linear Sos calculation and facing Sos calculation
Writing the results to the new tables in DB
"""
facing_kpi_fk = self.kpi_static_data[
self.kpi_static_data['client_name'] ==
'FACINGS_SOS_SCENE_TYPE_BY_MANUFACTURER']['pk'].iloc[0]
linear_kpi_fk = self.kpi_static_data[
self.kpi_static_data['client_name'] ==
'LINEAR_SOS_SCENE_TYPE_BY_MANUFACTURER']['pk'].iloc[0]
den_facing_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Facing')
& (self.exclude_filters['apply on'] == 'Denominator')]
den_linear_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Linear')
& (self.exclude_filters['apply on'] == 'Denominator')]
num_facing_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Facing')
& (self.exclude_filters['apply on'] == 'Numerator')]
num_linear_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Linear')
& (self.exclude_filters['apply on'] == 'Numerator')]
scene_templates = self.scif['template_fk'].unique().tolist()
scene_manufactures = self.scif['manufacturer_fk'].unique().tolist()
# exclude filters denominator
den_general_facing_filters = self.create_dict_filters(
den_facing_exclude_template, self.EXCLUDE_FILTER)
den_general_linear_filters = self.create_dict_filters(
den_linear_exclude_template, self.EXCLUDE_FILTER)
# exclude filters numerator
num_general_facing_filters = self.create_dict_filters(
num_facing_exclude_template, self.EXCLUDE_FILTER)
num_general_linear_filters = self.create_dict_filters(
num_linear_exclude_template, self.EXCLUDE_FILTER)
df_num_fac = self.filter_2_cond(self.scif, num_facing_exclude_template)
df_num_lin = self.filter_2_cond(self.scif, num_linear_exclude_template)
df_den_lin = self.filter_2_cond(self.scif, den_facing_exclude_template)
df_den_fac = self.filter_2_cond(self.scif, den_linear_exclude_template)
for template in scene_templates:
for manufacture in scene_manufactures:
sos_filters = {
"template_fk": (template, self.INCLUDE_FILTER),
"manufacturer_fk": (manufacture, self.INCLUDE_FILTER)
}
tem_filters = {"template_fk": (template, self.INCLUDE_FILTER)}
dict_num_facing = dict(
(k, v) for d in [sos_filters, num_general_facing_filters]
for k, v in d.items())
numerator_facings = self.calculate_share_space(
df_num_fac, dict_num_facing)[0]
dict_num_linear = dict(
(k, v) for d in [sos_filters, num_general_linear_filters]
for k, v in d.items())
numerator_linear = self.calculate_share_space(
df_num_lin, dict_num_linear)[1]
dict_den_facing = dict(
(k, v) for d in [tem_filters, den_general_facing_filters]
for k, v in d.items())
denominator_facings = self.calculate_share_space(
df_den_fac, dict_den_facing)[0]
dict_den_linear = dict(
(k, v) for d in [tem_filters, den_general_linear_filters]
for k, v in d.items())
denominator_linear = self.calculate_share_space(
df_den_lin, dict_den_linear)[1]
score_facing = 0 if denominator_facings == 0 else (
numerator_facings / denominator_facings) * 100
score_linear = 0 if denominator_linear == 0 else (
numerator_linear / denominator_linear) * 100
self.common.write_to_db_result_new_tables(
facing_kpi_fk, manufacture, numerator_facings,
score_facing, template, denominator_facings, score_facing)
self.common.write_to_db_result_new_tables(
linear_kpi_fk, manufacture, numerator_linear, score_linear,
template, denominator_linear, score_linear)
def create_dict_filters(self, template, param):
"""
:param template : Template of the desired filtering to data frame
:param param : exclude /include
:return: Dictionary of filters and parameter : exclude / include by demeaned
"""
filters_dict = {}
template_without_second = template[template['Param 2'].isnull()]
for row in template_without_second.iterrows():
filters_dict[row[1]['Param 1']] = (row[1]['Value 1'].split(','),
param)
return filters_dict
def filter_2_cond(self, data_frame, template):
"""
:param template: Template of the desired filtering
:param data_frame : Data frame
:return: data frame filtered by entries in the template with 2 conditions
"""
template_without_second = template[template['Param 2'].notnull()]
if template_without_second is not None:
for row in template_without_second.iterrows():
data_frame = data_frame.loc[
(~data_frame[row[1]['Param 1']].isin(row[1]['Value 1'].
split(','))) |
(~data_frame[row[1]['Param 2']].isin(row[1]['Value 2'].
split(',')))]
return data_frame
def calculate_share_space(self, data_frame, filters):
"""
:param filters: These are the parameters which the data frame is filtered by.
:param data_frame : relevant scene item facts data frame (filtered )
:return: The total number of facings and the shelf width (in mm) according to the filters.
"""
filtered_scif = data_frame[self.toolbox.get_filter_condition(
data_frame, **filters)]
sum_of_facings = filtered_scif['facings'].sum()
space_length = filtered_scif['gross_len_split_stack'].sum()
return sum_of_facings, space_length
def calculate_bay_kpi(self):
bay_kpi_sheet = self.bay_count_kpi
kpi = self.kpi_static_data.loc[self.kpi_static_data['type'] ==
BAY_COUNT_KPI]
if kpi.empty:
Log.info("CCAAU Calculate KPI Name:{} not found in DB".format(
BAY_COUNT_KPI))
else:
Log.info("CCAAU Calculate KPI Name:{} found in DB".format(
BAY_COUNT_KPI))
bay_kpi_row = bay_kpi_sheet[bay_kpi_sheet['KPI Name'] ==
BAY_COUNT_KPI]
if not bay_kpi_row.empty:
scene_types_to_consider = bay_kpi_row['Scene Type'].iloc[0]
if scene_types_to_consider == '*':
# Consider all scene types
scene_types_to_consider = 'all'
else:
scene_types_to_consider = [
x.strip() for x in scene_types_to_consider.split(',')
]
mpis_with_scene = self.match_product_in_scene.merge(
self.scene_info, how='left', on='scene_fk')
mpis_with_scene_and_template = mpis_with_scene.merge(
self.templates, how='left', on='template_fk')
if scene_types_to_consider != 'all':
mpis_with_scene_and_template = mpis_with_scene_and_template[
mpis_with_scene_and_template['template_name'].isin(
scene_types_to_consider)]
mpis_template_group = mpis_with_scene_and_template.groupby(
'template_fk')
for template_fk, template_data in mpis_template_group:
Log.info("Running for template ID {templ_id}".format(
templ_id=template_fk, ))
total_bays_for_scene_type = 0
scene_group = template_data.groupby('scene_fk')
for scene_fk, scene_data in scene_group:
Log.info(
"KPI Name:{kpi} bay count is {bay_c} for scene ID {scene_id}"
.format(
kpi=BAY_COUNT_KPI,
bay_c=int(scene_data['bay_number'].max()),
scene_id=scene_fk,
))
total_bays_for_scene_type += int(
scene_data['bay_number'].max())
Log.info(
"KPI Name:{kpi} total bay count is {bay_c} for template ID {templ_id}"
.format(
kpi=BAY_COUNT_KPI,
bay_c=total_bays_for_scene_type,
templ_id=template_fk,
))
self.common.write_to_db_result_new_tables(
fk=int(kpi['pk'].iloc[0]),
numerator_id=int(template_fk),
numerator_result=total_bays_for_scene_type,
denominator_id=int(self.store_id),
denominator_result=total_bays_for_scene_type,
result=total_bays_for_scene_type,
)
class ALTRIAUSToolBox:
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.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.template_info = self.data_provider.all_templates
self.rds_conn = ProjectConnector(self.project_name, DbUsers.CalculationEng)
self.ps_data_provider = PsDataProvider(self.data_provider)
self.match_product_in_probe_state_reporting = self.ps_data_provider.get_match_product_in_probe_state_reporting()
self.kpi_results_queries = []
self.fixture_width_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Fixture Width", dtype=pd.Int64Dtype())
self.facings_to_feet_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Conversion Table", dtype=pd.Int64Dtype())
self.header_positions_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Header Positions")
self.flip_sign_positions_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Flip Sign Positions")
self.custom_entity_data = self.ps_data_provider.get_custom_entities(1005)
self.ignore_stacking = False
self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack'
self.kpi_new_static_data = self.common.get_new_kpi_static_data()
try:
self.mpis = self.match_product_in_scene.merge(self.products, on='product_fk', suffixes=['', '_p']) \
.merge(self.scene_info, on='scene_fk', suffixes=['', '_s']) \
.merge(self.template_info, on='template_fk', suffixes=['', '_t'])
except KeyError:
Log.warning('MPIS cannot be generated!')
return
self.adp = AltriaDataProvider(self.data_provider)
self.active_kpis = self._get_active_kpis()
self.external_targets = self.ps_data_provider.get_kpi_external_targets()
self.survey_dot_com_collected_this_session = self._get_survey_dot_com_collected_value()
self._add_smart_attributes_to_mpis()
self.scene_graphs = {}
self.excessive_flipsigns = False
self.incorrect_tags_in_pos_areas = []
def _add_smart_attributes_to_mpis(self):
smart_attribute_data = \
self.adp.get_match_product_in_probe_state_values(self.mpis['probe_match_fk'].unique().tolist())
self.mpis = pd.merge(self.mpis, smart_attribute_data, on='probe_match_fk', how='left')
self.mpis['match_product_in_probe_state_fk'].fillna(0, inplace=True)
def main_calculation(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
self.generate_graph_per_scene()
for graph in self.scene_graphs.values():
self.calculate_fixture_and_block_level_kpis(graph)
self.calculate_register_type()
self.calculate_age_verification()
self.calculate_facings_by_scene_type()
self.calculate_session_flags()
return
def calculate_fixture_and_block_level_kpis(self, graph):
for node, node_data in graph.nodes(data=True):
if node_data['category'].value != 'POS':
self.calculate_fixture_width(node_data)
self.calculate_flip_sign(node, node_data, graph)
self.calculate_flip_sign_empty_space(node, node_data, graph)
self.calculate_flip_sign_locations(node_data)
self.calculate_total_shelves(node_data)
self.calculate_tags_by_fixture_block(node_data)
if node_data['block_number'].value == 1:
self.calculate_header(node, node_data, graph)
self.calculate_product_above_headers(node_data)
self.calculate_no_headers(node_data)
return
def generate_graph_per_scene(self):
relevant_scif = self.scif[self.scif['template_name'] == 'Tobacco Merchandising Space']
for scene in relevant_scif['scene_id'].unique().tolist():
agb = AltriaGraphBuilder(self.data_provider, scene)
self.scene_graphs[scene] = agb.get_graph()
if agb.incorrect_tags_in_pos_area:
self.incorrect_tags_in_pos_areas.extend(agb.incorrect_tags_in_pos_area)
if len(self.scene_graphs.keys()) > 1:
Log.warning("More than one Tobacco Merchandising Space scene detected. Results could be mixed!")
return
def calculate_fixture_width(self, node_data):
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Fixture Width')
width = node_data['calculated_width_ft'].value
width = round(width)
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
category_fk = self.get_category_fk_by_name(node_data['category'].value)
self.common_v2.write_to_db_result(kpi_fk, numerator_id=category_fk, denominator_id=self.store_id,
numerator_result=block_number, denominator_result=fixture_number,
result=width)
def calculate_flip_sign(self, node, node_data, graph):
if node_data['category'].value == 'Cigarettes':
self.calculate_flip_signs_cigarettes(node, node_data, graph)
else:
self.calculate_flip_signs_non_cigarettes(node, node_data, graph)
return
def calculate_flip_signs_non_cigarettes(self, node, node_data, graph):
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Flip Sign')
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
flip_signs_by_x_coord = {}
for neighbor in graph.neighbors(node):
neighbor_data = graph.nodes[neighbor]
if neighbor_data['category'].value != 'POS':
continue
if neighbor_data['pos_type'].value != 'Flip-Sign':
continue
center_x = neighbor_data['polygon'].centroid.coords[0][0]
flip_signs_by_x_coord[center_x] = neighbor_data
for i, pair in enumerate(sorted(flip_signs_by_x_coord.items())):
position = i+1
position_fk = self.get_custom_entity_pk(str(position))
if position_fk == 0 or position > 8:
self.excessive_flipsigns = True
Log.warning('More than 8 flip-sign positions found for a non-cigarettes block')
product_fk = pair[1]['product_fk'].value
width = pair[1]['calculated_width_ft'].value
implied_facings = pair[1]['width_of_signage_in_facings'].value
width = self.round_threshold(width)
self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=position_fk,
numerator_result=block_number, denominator_result=fixture_number,
result=width, score=implied_facings)
def calculate_flip_signs_cigarettes(self, node, node_data, graph):
width = node_data['calculated_width_ft'].value
fixture_bounds = node_data['polygon'].bounds
fixture_width_coord_units = abs(fixture_bounds[0] - fixture_bounds[2])
proportions_dict = self.get_flip_sign_position_proportions(round(width))
if not proportions_dict:
return
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Flip Sign')
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
f_slot_boxes = {}
left_bound = fixture_bounds[0] # start proportional divisions on left side of fixture
for position, proportion in proportions_dict.items():
right_bound = left_bound + (fixture_width_coord_units * proportion)
# TODO update this to use min and max height of graph
f_slot_boxes[position] = box(left_bound, -9999, right_bound, 9999)
left_bound = right_bound
for position, slot_box in f_slot_boxes.items():
for neighbor in graph.neighbors(node):
neighbor_data = graph.nodes[neighbor]
try:
if neighbor_data['pos_type'].value != 'Flip-Sign':
continue
except KeyError:
continue
flip_sign_bounds = neighbor_data['polygon'].bounds
flip_sign_box = box(flip_sign_bounds[0], flip_sign_bounds[1], flip_sign_bounds[2], flip_sign_bounds[3])
overlap_ratio = flip_sign_box.intersection(slot_box).area / flip_sign_box.area
if overlap_ratio >= REQUIRED_FLIP_SIGN_FSLOT_OVERLAP_RATIO:
product_fk = neighbor_data['product_fk'].value
position_fk = self.get_custom_entity_pk(position)
flip_sign_width = neighbor_data['calculated_width_ft'].value
flip_sign_width = self.round_threshold(flip_sign_width)
implied_facings = neighbor_data['width_of_signage_in_facings'].value
self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=position_fk,
numerator_result=block_number, denominator_result=fixture_number,
result=flip_sign_width, score=implied_facings)
return
def calculate_flip_sign_empty_space(self, node, node_data, graph):
if node_data['category'].value != 'Cigarettes':
return
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Empty Flip Sign Space')
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
fixture_width = node_data['calculated_width_ft'].value
fixture_width = self.round_threshold(fixture_width)
flip_sign_widths = []
for neighbor in graph.neighbors(node):
neighbor_data = graph.nodes[neighbor]
if neighbor_data['category'].value != 'POS':
continue
if neighbor_data['pos_type'].value != 'Flip-Sign':
continue
# exclude 'General POS Other'
if neighbor_data['product_fk'].value == 9304:
continue
neighbor_width = neighbor_data['calculated_width_ft'].value
neighbor_width = self.round_threshold(neighbor_width)
flip_sign_widths.append(neighbor_width)
empty_space = abs(fixture_width - sum(flip_sign_widths))
self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=self.store_id,
numerator_result=block_number, denominator_result=fixture_number,
result=empty_space)
def calculate_flip_sign_locations(self, node_data):
if node_data['category'].value != 'Cigarettes':
return
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Flip Sign Locations')
width = node_data['calculated_width_ft'].value
width = round(width)
proportions_dict = self.get_flip_sign_position_proportions(width)
if not proportions_dict:
return
for position, proportion in proportions_dict.items():
position_fk = self.get_custom_entity_pk(position)
self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=position_fk,
numerator_result=block_number, denominator_result=fixture_number,
result=round(proportion * width))
return
def get_flip_sign_position_proportions(self, width):
relevant_template = self.fixture_width_template[self.fixture_width_template['Fixture Width (ft)'] == width]
if relevant_template.empty:
Log.error("Unable to save flip sign location data. {}ft does not exist as a defined width".format(width))
return None
# this could definitely be simpler, but I'm tired and don't want to use my brain
relevant_template[['F1', 'F2', 'F3', 'F4']] = relevant_template[['F1', 'F2', 'F3', 'F4']].fillna(0)
f_slots = [relevant_template['F1'].iloc[0], relevant_template['F2'].iloc[0],
relevant_template['F3'].iloc[0], relevant_template['F4'].iloc[0]]
f_slots = [i for i in f_slots if i != 0]
proportions_dict = {}
for i, slot in enumerate(f_slots):
proportions_dict["F{}".format(i+1)] = slot / float(sum(f_slots))
return proportions_dict
def calculate_total_shelves(self, node_data):
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Total Shelves')
shelves = len(node_data['shelf_number'].values)
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
category_fk = self.get_category_fk_by_name(node_data['category'].value)
self.common_v2.write_to_db_result(kpi_fk, numerator_id=category_fk, denominator_id=self.store_id,
numerator_result=block_number, denominator_result=fixture_number,
result=shelves)
def calculate_tags_by_fixture_block(self, node_data):
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('FACINGS_BY_FIXTURE_BLOCK')
scene_match_fks = node_data['match_fk'].values
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
relevant_matches = self.mpis[self.mpis['scene_match_fk'].isin(scene_match_fks)]
relevant_matches = relevant_matches.groupby(['product_fk', 'shelf_number', 'match_product_in_probe_state_fk'],
as_index=False).agg({'scene_match_fk': 'min',
'probe_match_fk': 'count'})
relevant_matches.rename(columns={'probe_match_fk': 'facings'}, inplace=True)
for row in relevant_matches.itertuples():
self.common_v2.write_to_db_result(kpi_fk, numerator_id=row.product_fk,
denominator_id=row.match_product_in_probe_state_fk,
numerator_result=block_number, denominator_result=fixture_number,
result=row.facings, score=row.shelf_number,
context_id=row.scene_match_fk)
def calculate_header(self, node, node_data, graph):
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Header')
parent_category = node_data['category'].value
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
if parent_category not in self.header_positions_template.columns.tolist():
return
headers_by_x_coord = {}
menu_board_items_by_x_coord = {}
for neighbor in graph.neighbors(node):
neighbor_data = graph.nodes[neighbor]
if neighbor_data['category'].value != 'POS':
continue
if neighbor_data['pos_type'].value != 'Header':
continue
center_x = neighbor_data['polygon'].centroid.coords[0][0]
if neighbor_data['in_menu_board_area'].value is True:
menu_board_items_by_x_coord[center_x] = neighbor_data
else:
headers_by_x_coord[center_x] = neighbor_data
number_of_headers = len(headers_by_x_coord.keys())
if menu_board_items_by_x_coord:
number_of_headers += 1
relevant_positions = \
self.header_positions_template[(self.header_positions_template['Number of Headers'] == number_of_headers)]
if relevant_positions.empty:
if number_of_headers == 0:
return
else:
Log.error("Too many headers ({}) found for one block ({}). Unable to calculate positions!".format(
number_of_headers, parent_category))
return
positions = relevant_positions[parent_category].iloc[0]
positions = [position.strip() for position in positions.split(',')]
for i, pair in enumerate(sorted(headers_by_x_coord.items())):
position_fk = self.get_custom_entity_pk(positions[i])
product_fk = pair[1]['product_fk'].value
width = pair[1]['calculated_width_ft'].value
width = self.round_threshold(width)
self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=position_fk,
numerator_result=block_number, denominator_result=fixture_number,
result=width, score=width)
if menu_board_items_by_x_coord:
for pair in menu_board_items_by_x_coord.items():
position_fk = self.get_custom_entity_pk(positions[-1])
product_fk = pair[1]['product_fk'].value
width = pair[1]['calculated_width_ft'].value
width = self.round_threshold(width)
# width = 1 # this is because there is no real masking for menu board items
self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=position_fk,
numerator_result=block_number, denominator_result=fixture_number,
result=width, score=width)
return
def calculate_product_above_headers(self, node_data):
try:
product_above_header = node_data['product_above_header'].value
except KeyError:
return
if not product_above_header:
return
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Product Above Header')
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=self.store_id,
numerator_result=block_number, denominator_result=fixture_number,
result=1, score=1, context_id=fixture_number)
return
def calculate_no_headers(self, node_data):
try:
no_header = node_data['no_header'].value
except KeyError:
return
if not no_header:
return
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('No Header')
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=self.store_id,
numerator_result=block_number, denominator_result=fixture_number,
result=1, score=1, context_id=fixture_number)
return
def _get_active_kpis(self):
active_kpis = self.kpi_new_static_data[(self.kpi_new_static_data['kpi_calculation_stage_fk'] == 3) &
(self.kpi_new_static_data['valid_from'] <= self.visit_date) &
((self.kpi_new_static_data['valid_until']).isnull() |
(self.kpi_new_static_data['valid_until'] >= self.visit_date))]
return active_kpis
def calculate_facings_by_scene_type(self):
kpi_name = 'FACINGS_BY_SCENE_TYPE'
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(kpi_name)
if kpi_name not in self.active_kpis['type'].unique().tolist():
return
config = self.get_external_target_data_by_kpi_fk(kpi_fk)
product_types = config.product_type
template_names = config.template_name
relevant_mpis = self.mpis[(self.mpis['product_type'].isin(product_types)) &
(self.mpis['template_name'].isin(template_names))]
relevant_mpis = relevant_mpis.groupby(['product_fk',
'template_fk',
'match_product_in_probe_state_fk'],
as_index=False)['scene_match_fk'].count()
relevant_mpis.rename(columns={'scene_match_fk': 'facings'}, inplace=True)
for row in relevant_mpis.itertuples():
self.common_v2.write_to_db_result(kpi_fk, numerator_id=row.product_fk, denominator_id=row.template_fk,
context_id=row.match_product_in_probe_state_fk, result=row.facings)
return
def calculate_register_type(self):
if self.survey_dot_com_collected_this_session:
return
relevant_scif = self.scif[(self.scif['product_type'].isin(['POS', 'Other'])) &
(self.scif['category'] == 'POS Machinery')]
if relevant_scif.empty:
result = 0
product_fk = 0
else:
result = 1
product_fk = relevant_scif['product_fk'].iloc[0]
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Register Type')
self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id,
result=result)
def calculate_age_verification(self):
if self.survey_dot_com_collected_this_session:
return
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Age Verification')
relevant_scif = self.scif[self.scif['brand_name'].isin(['Age Verification'])]
if relevant_scif.empty:
result = 0
product_fk = 0
self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id,
result=result)
else:
result = 1
for product_fk in relevant_scif['product_fk'].unique().tolist():
self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id,
result=result)
return
def calculate_session_flags(self):
if self.excessive_flipsigns:
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Excessive Flip Signs Detected')
self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=self.store_id, result=1)
if self.incorrect_tags_in_pos_areas:
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Invalid Product in Header Areas')
relevant_product_fks = self.mpis[self.mpis['scene_match_fk'].isin(self.incorrect_tags_in_pos_areas)][
'product_fk'].unique().tolist()
for product_fk in relevant_product_fks:
self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id,
result=1)
def mark_tags_in_explorer(self, probe_match_fk_list, mpipsr_name):
if not probe_match_fk_list:
return
try:
match_type_fk = \
self.match_product_in_probe_state_reporting[
self.match_product_in_probe_state_reporting['name'] == mpipsr_name][
'match_product_in_probe_state_reporting_fk'].values[0]
except IndexError:
Log.warning('Name not found in match_product_in_probe_state_reporting table: {}'.format(mpipsr_name))
return
match_product_in_probe_state_values_old = self.common_v2.match_product_in_probe_state_values
match_product_in_probe_state_values_new = pd.DataFrame(columns=[MATCH_PRODUCT_IN_PROBE_FK,
MATCH_PRODUCT_IN_PROBE_STATE_REPORTING_FK])
match_product_in_probe_state_values_new[MATCH_PRODUCT_IN_PROBE_FK] = probe_match_fk_list
match_product_in_probe_state_values_new[MATCH_PRODUCT_IN_PROBE_STATE_REPORTING_FK] = match_type_fk
self.common_v2.match_product_in_probe_state_values = pd.concat([match_product_in_probe_state_values_old,
match_product_in_probe_state_values_new])
return
@staticmethod
def round_threshold(value, threshold=0.2):
return round(value - threshold + 0.5)
def _get_survey_dot_com_collected_value(self):
try:
sales_rep_fk = self.session_info['s_sales_rep_fk'].iloc[0]
except IndexError:
sales_rep_fk = 0
return int(sales_rep_fk) == 209050
def get_category_fk_by_name(self, category_name):
return self.all_products[self.all_products['category'] == category_name]['category_fk'].iloc[0]
def get_custom_entity_pk(self, name):
try:
return self.custom_entity_data[self.custom_entity_data['name'] == name]['pk'].iloc[0]
except IndexError:
Log.error('No custom entity found for {}'.format(name))
return 0
def get_external_target_data_by_kpi_fk(self, kpi_fk):
return self.external_targets[self.external_targets['kpi_fk'] == kpi_fk].iloc[0]
def commit(self):
self.common_v2.commit_results_data()
class HEINEKENTWToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
DIST_STORE_LVL1 = 1014
OOS_STORE_LVL1 = 1015
DIST_STORE_LVL2 = 1016
OOS_STORE_LVL2 = 1017
DIST_CATEGORY_LVL1 = 1018
OOS_CATEGORY_LVL1 = 1019
DIST_CATEGORY_LVL2 = 1020
OOS_CATEGORY_LVL2 = 1021
DISTRIBUTION = 4
OOS = 5
MANUFACTURER_FK = 175 # heinken manfucturer
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_new_kpi_static_data()
self.kpi_results_queries = []
self.assortment = Assortment(self.data_provider, self.output)
def main_calculation(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
lvl3_result = self.assortment.calculate_lvl3_assortment()
self.category_assortment_calculation(lvl3_result)
self.store_assortment_calculation(lvl3_result)
self.common.commit_results_data_to_new_tables()
# self.common.commit_results_data_to_new_tables()
def category_assortment_calculation(self, lvl3_result):
"""
This function calculates 3 levels of assortment :
level3 is assortment SKU
level2 is assortment groups
"""
if not lvl3_result.empty:
# cat_df = self.scif[['product_fk', 'category_fk']]
cat_df = self.all_products[['product_fk', 'category_fk']]
lvl3_with_cat = lvl3_result.merge(cat_df,
on='product_fk',
how='left')
lvl3_with_cat = lvl3_with_cat[
lvl3_with_cat['category_fk'].notnull()]
for result in lvl3_with_cat.itertuples():
if result.in_store == 1:
score = self.DISTRIBUTION
else:
score = self.OOS
# Distrubtion
self.common.write_to_db_result_new_tables(
fk=self.DIST_CATEGORY_LVL1,
numerator_id=result.product_fk,
numerator_result=score,
result=score,
denominator_id=result.category_fk,
denominator_result=1,
score=score,
score_after_actions=score)
if score == self.OOS:
# OOS
self.common.write_to_db_result_new_tables(
fk=self.OOS_CATEGORY_LVL1,
numerator_id=result.product_fk,
numerator_result=score,
result=score,
denominator_id=result.category_fk,
denominator_result=1,
score=score,
score_after_actions=score)
category_list = lvl3_with_cat['category_fk'].unique()
for cat in category_list:
lvl3_result_cat = lvl3_with_cat[lvl3_with_cat["category_fk"] ==
cat]
lvl2_result = self.assortment.calculate_lvl2_assortment(
lvl3_result_cat)
for result in lvl2_result.itertuples():
denominator_res = result.total
res = np.divide(float(result.passes),
float(denominator_res))
# Distrubtion
self.common.write_to_db_result_new_tables(
fk=self.DIST_CATEGORY_LVL2,
numerator_id=self.MANUFACTURER_FK,
numerator_result=result.passes,
denominator_id=cat,
denominator_result=denominator_res,
result=res,
score=res,
score_after_actions=res)
# OOS
self.common.write_to_db_result_new_tables(
fk=self.OOS_CATEGORY_LVL2,
numerator_id=self.MANUFACTURER_FK,
numerator_result=denominator_res - result.passes,
denominator_id=cat,
denominator_result=denominator_res,
result=1 - res,
score=(1 - res),
score_after_actions=1 - res)
return
def store_assortment_calculation(self, lvl3_result):
"""
This function calculates the KPI results.
"""
for result in lvl3_result.itertuples():
if result.in_store == 1:
score = self.DISTRIBUTION
else:
score = self.OOS
# Distrubtion
self.common.write_to_db_result_new_tables(
fk=self.DIST_STORE_LVL1,
numerator_id=result.product_fk,
numerator_result=score,
result=score,
denominator_id=self.store_id,
denominator_result=1,
score=score)
if score == self.OOS:
# OOS
self.common.write_to_db_result_new_tables(
fk=self.OOS_STORE_LVL1,
numerator_id=result.product_fk,
numerator_result=score,
result=score,
denominator_id=self.store_id,
denominator_result=1,
score=score,
score_after_actions=score)
if not lvl3_result.empty:
lvl2_result = self.assortment.calculate_lvl2_assortment(
lvl3_result)
for result in lvl2_result.itertuples():
denominator_res = result.total
if not pd.isnull(result.target) and not pd.isnull(
result.group_target_date
) and result.group_target_date <= self.assortment.current_date:
denominator_res = result.target
res = np.divide(float(result.passes), float(denominator_res))
# Distrubtion
self.common.write_to_db_result_new_tables(
fk=self.DIST_STORE_LVL2,
numerator_id=self.MANUFACTURER_FK,
denominator_id=self.store_id,
numerator_result=result.passes,
denominator_result=denominator_res,
result=res,
score=res,
score_after_actions=res)
# OOS
self.common.write_to_db_result_new_tables(
fk=self.OOS_STORE_LVL2,
numerator_id=self.MANUFACTURER_FK,
numerator_result=denominator_res - result.passes,
denominator_id=self.store_id,
denominator_result=denominator_res,
result=1 - res,
score=1 - res,
score_after_actions=1 - res)
return
class INBEVTRADMXToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.common2 = Common2(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.templates = self.data_provider.all_templates
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.templates_path = os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data')
self.excel_file_path = os.path.join(self.templates_path,
'inbevtradmx_template_11_v3.xlsx')
self.availability = Availability(self.data_provider)
self.survey_response = self.data_provider[Data.SURVEY_RESPONSES]
self.geo = GeoLocation.INBEVTRADMXGeo(self.rds_conn, self.session_uid,
self.data_provider,
self.kpi_static_data,
self.common, self.common2)
self.new_static_data = self.common2.kpi_static_data
self.manufacturer_fk = 1
self.match_displays_in_scene = self.data_provider.match_display_in_scene
self.mpis = self.mpis_merger()
self.all_data = pd.merge(self.scif,
self.match_product_in_scene[[
'product_fk', 'shelf_number', 'scene_fk',
'facing_sequence_number'
]],
how="inner",
left_on=['item_id', 'scene_id'],
right_on=['product_fk',
'scene_fk']).drop_duplicates()
self.ignore_stacking = False
self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack'
self.INCLUDE_FILTER = 1
self.EXCLUDE_FILTER = 0
self.CONTAIN_FILTER = 2
self.EXCLUDE_EMPTY = False
self.INCLUDE_EMPTY = True
# init functions:
def parse_template(self):
"""
convert excel file to data frame
:return: data frame
"""
template_df = pd.read_excel(self.excel_file_path,
sheetname='template',
encoding='utf-8')
template_df['Store Additional Attribute 4'] = template_df[
'Store Additional Attribute 4'].fillna('')
template_df['Store Additional Attribute 13'] = template_df[
'Store Additional Attribute 13'].fillna('')
return template_df
def check_store_type(self, row, relevant_columns):
"""
this method checks if the session store type is valid
:type relevant_columns: list
:param row: current KPI row
:return: boolean
"""
# make sure that we need to check this field
if relevant_columns.__contains__('store_type'):
# get the valid stores from the template
stores_df = pd.read_excel(self.excel_file_path,
sheetname='store types')
# create a list of the valid stores
stores = stores_df.values
return row['store_type'] in stores
else:
return True
# calculation:
def main_calculation(self):
# calculate geo
geo_result = self.geo.calculate_geo_location()
self.geo.write_geo_to_db(float(geo_result))
# calculate from template
self.calculate_kpi_set_from_template()
self.calculate_posm_displays()
self.common.commit_results_data()
self.common2.commit_results_data()
def calculate_kpi_set_from_template(self):
"""
this method chooses the correct set to calculate
there will always be only on set to calculate, depending on the field 'Store additional attribute 4' from
template
:return: None
"""
# get the template
parsed_template = self.parse_template()
# get all the unique sets
# sets = parsed_template['KPI Level 1 Name'].unique()
# get the session additional_attribute_4 & 13
additional_attribute_4 = self.store_info.additional_attribute_4.values[
0]
additional_attribute_13 = self.store_info.additional_attribute_13.values[
0]
set_name = self.choose_correct_sets_to_calculate(
additional_attribute_4, additional_attribute_13, parsed_template)
# for set_name in set_names:
# wrong value in additional attribute 4 - shouldn't calculate
if set_name == '':
Log.warning(
'Wrong value in additional attribute 4 - shouldnt calculate')
return -1
# get only the part of the template that is related to this set
set_template_df = parsed_template[parsed_template['KPI Level 1 Name']
== set_name]
# start calculating !
self.calculate_set_score(set_template_df, set_name)
@staticmethod
def choose_correct_sets_to_calculate(additional_attribute_4,
additional_attribute_13, template):
"""
choose what is the appropriate set to calculate
:param additional_attribute_4: session additional_attribute_4. if None, will ignore the kpi.
:param additional_attribute_13: session additional_attribute_13. if None, will ignore this attribute
:param template: parsed template
:return: set name to calculate - assuming each additional attribute 4 matches only 1 set name.
"""
template = template.dropna(subset=['Store Additional Attribute 4'],
axis=0)
# Makes sure attribute 4 exist
if not additional_attribute_4:
return ''
if additional_attribute_13:
sets = template[
(template['Store Additional Attribute 4'].str.contains(
additional_attribute_4))
& ((template['Store Additional Attribute 13'].str.contains(
additional_attribute_13))
| (template['Store Additional Attribute 13'] == ''))]
else:
sets = template[
(template['Store Additional Attribute 4'].str.contains(
additional_attribute_4))
& (template['Store Additional Attribute 13'] == '')]
if sets.empty:
return ''
else:
sets = sets['KPI Level 1 Name'].unique().tolist()
if len(sets) == 1:
set_name = sets[0]
elif additional_attribute_4 == 'BC':
set_name = sets[0]
elif additional_attribute_4 == 'BA':
set_name = sets[1]
elif additional_attribute_4 == 'MODELORAMA':
set_name = sets[2]
else:
Log.error('KPI should only run on one KPI set.')
return set_name
def calculate_set_score(self, set_df, set_name):
"""
this method iterates kpi set and calculates it's score
:param set_df: the set df to calculate score for
:param set_name: the kpi set name
:return: None
"""
set_score = 0
# get array of all kpi level 2 names
kpi_names = set_df['KPI Level 2 Name'].unique()
# iterate all kpi level 2
for kpi_name in kpi_names:
# calculate kpi level 2 score
kpi_score = self.calculate_kpi_level_2_score(
kpi_name, set_df, set_name)
# accumulate set score
set_score += kpi_score
# round set_score if the set is 'Set Urban'
if set_name == 'Set Urban':
set_score = round(set_score)
# finally, write level 1 kpi set score to DB
self.write_kpi_set_score_to_db(set_name, set_score)
def calculate_kpi_level_2_score(self, kpi_name, set_df, set_name):
"""
this method gets kpi level 2 name, and iterates it's related atomic kpis
:param set_name: kpi set name
:param kpi_name: kpi level 2 name
:param set_df: kpi set df
:return: kpi level 2 score
"""
kpi_df = set_df[set_df['KPI Level 2 Name'].str.encode('utf8') ==
kpi_name.encode('utf-8')]
kpi_score = 0
# iterate the all related atomic kpis
for i, row in kpi_df.iterrows():
# get atomic kpi name
kpi_level_3_name = row['KPI Level 3 Name']
# calculate atomic kpi score
atomic_kpi_score = self.calculate_atomic_kpi_score(
row, kpi_level_3_name, kpi_name, set_name)
# accumulate kpi level 2 score
kpi_score += atomic_kpi_score
write_to_all_levels = False
if len(kpi_df
) > 1: # if there is just one atomic we don't need two levels
write_to_all_levels = True
elif kpi_df['KPI Level 3 Name'].iloc[0] != kpi_df[
'KPI Level 2 Name'].iloc[0]:
write_to_all_levels = True
self.write_kpi_score_to_db(kpi_name, set_name, kpi_score,
write_to_all_levels)
return kpi_score
def calculate_atomic_kpi_score(self, row, kpi_level_3_name, kpi_name,
set_name):
"""
this method calculates score for specific atomic kpi
:param set_name: kpi set name
:param kpi_name: kpi name
:param kpi_level_3_name: the atomic kpi name
:param row: atomic kpi details
:return: atomic kpi score
"""
atomic_kpi_score = 0
# get column name to consider in calculation
relevant_columns = map(str.strip, str(row['column names']).split(','))
optional_columns = map(str.strip,
str(row['optional column']).split(','))
is_kpi_passed = 0
if self.check_store_type(row, relevant_columns):
# get weight of current atomic kpi
curr_weight = row['weights']
# figure out what type of calculation need to be done
if row['KPI type'] == 'Product Availability':
if kpi_level_3_name == 'URBAN':
score = self.calculate_weigthed_availability_score(
row, relevant_columns)
if score:
atomic_kpi_score = score
elif kpi_level_3_name == 'Hay o no hay # frentes':
if self.calculate_lead_availability_score(
row, relevant_columns):
is_kpi_passed = 1
elif kpi_level_3_name == 'Adherencia de materiales':
if self.calculate_or_availability(row, relevant_columns,
optional_columns):
is_kpi_passed = 1
else:
if self.calculate_availability_score(
row, relevant_columns):
is_kpi_passed = 1
if kpi_level_3_name == '100% Marcas HE':
curr_weight = self.unique_brand_count
elif row['KPI type'] == 'SOS':
ratio = self.calculate_sos_score(row, relevant_columns)
if (row['product_type'] == 'Empty') & (ratio <= 0.2):
if self.scif[self.scif['template_name'] ==
row['template_name']].empty:
is_kpi_passed = 0
else:
is_kpi_passed = 1
elif ratio == 1:
is_kpi_passed = 1
elif row['KPI type'] == 'Survey':
if self.calculate_survey_score(row):
is_kpi_passed = 1
if is_kpi_passed == 1:
atomic_kpi_score += curr_weight
# write result to DB
# the customer asked for this specific KPI will write 100 in DB if it passed even if the weight is 0
if kpi_level_3_name == 'Sin Espacios Vacios' and curr_weight == 0 and is_kpi_passed == 1:
# atomic_kpi_score = 100
self.write_atomic_to_db(kpi_level_3_name, 100, kpi_name,
set_name, is_kpi_passed, curr_weight)
else:
self.write_atomic_to_db(kpi_level_3_name, atomic_kpi_score,
kpi_name, set_name, is_kpi_passed,
curr_weight)
return atomic_kpi_score
# sos
def calculate_sos_score(self, row, relevant_columns):
"""
this method calculates share of shelf score according to columns from the data frame
:param relevant_columns: columns to check in the excel file
:param row: data frame to calculate from
:return: share of shelf score
"""
if 'shelf_number' in relevant_columns:
scif_index_list = []
df = self.all_data[
(self.all_data.template_name == row['template_name'])
& (self.all_data.shelf_number == row['shelf_number']) &
(self.all_data.facing_sequence_number > 0)]
df.shelf_number = df.shelf_number.astype(float)
df.shelf_number = df.shelf_number.astype(str)
df = df[df['product_type'] != 'Empty']
else:
# get df only with the correct template name
df = self.scif[self.scif.template_name == row['template_name']]
if not pd.isna(row['exclude product_type']):
for excl in [
e.strip() for e in row['exclude product_type'].split(',')
]:
df = df[df['product_type'] != excl]
# sum of all the facings in df
facings = df.facings.values.sum()
if facings == 0:
return 0
# create dictionary for calculating
filters_dict = self.create_sos_filtered_dictionary(
relevant_columns, row)
# reduce the df only to relevant columns
df = df[filters_dict.keys()]
# check if it's invasion KPI for the special case
inv = row['KPI Level 3 Name'][:3] == 'Inv'
ratio = self.calculate_sos_ratio(df, filters_dict, inv)
return ratio / facings
def calculate_sos_ratio(self, df, filters_dict, inv):
ratio = 0
if 'manufacturer_name' in df.columns.tolist():
filter_out = df[(df['product_type'] == ('Empty'))
& (df['manufacturer_name'] == 'General')].index
df.drop(filter_out, inplace=True)
for key in filters_dict:
delete = [key for value in filters_dict[key] if value in ['nan']]
for key in delete:
del filters_dict[key]
# iterate the data frame
for i, df_row in df.iterrows():
# initialize the boolean variable
bol = True
# special case that the customer asked
# if inv and df_row.product_type == 'Empty':
if df_row.product_type == 'Empty':
ratio = ratio + self.scif.facings.loc[i]
continue
# iterate the filtered dictionary keys
else:
for key in filters_dict.keys():
# check if the value in df row in in "key" column is in the filtered dictionary we created before
bol &= df_row[key] in filters_dict[key]
# that means success of the inner loop, that all the values matching for this data frame row
if bol:
if 'shelf_number' in filters_dict.keys():
ratio = ratio + self.all_data.facings.loc[i]
else:
# accumulate ratio
ratio = ratio + self.scif.facings.loc[i]
return ratio
def create_sos_filtered_dictionary(self, relevant_columns, row):
"""
this method filters out relevant columns from the template fso we can calculate SOS easily
:param relevant_columns: relevant columns from temolates
:param row: specific row from template
:return: dictionary
"""
# dictionary to send to the generic method
filters_dict = {}
self.handle_exclude_skus(filters_dict, relevant_columns, row)
# fill the dictionary
for column_value in relevant_columns:
if column_value in [
'Store Additional Attribute 4',
'Store Additional Attribute 13'
] or column_value == 'store_type':
continue
filters_dict[column_value] = map(
str.strip,
str(row.loc[column_value]).split(','))
return filters_dict
# availability:
def calculate_weigthed_availability_score(self, row, relevant_columns):
"""
this method calculates availability score according to columns from the data frame
:param row: data frame to calculate from
:param relevant_columns: columns to check in the excel file
:return: boolean
"""
passed = 0
# Gets the scene types
scene_types = row['template_name'].split(', ')
scenes_num = len(scene_types)
# man = row['manufacturer_name']
if 'scene_type' in relevant_columns:
relevant_columns.remove('scene_type')
# create filtered dictionary
filters_dict = self.create_availability_filtered_dictionary(
relevant_columns, row)
for scene in scene_types:
filters_dict.update({'template_name': scene})
# call the generic method from KPIUtils_v2
if self.availability.calculate_availability(**filters_dict):
passed += 1
return (passed / float(scenes_num)) * 100 if scenes_num else 0
def calculate_lead_availability_score(self, row, relevant_columns):
"""
this method calculates availability score according to columns from the data frame
:param row: data frame to calculate from
:param relevant_columns: columns to check in the excel file
:return: boolean
"""
# Gets the brand names
brand_names = row['brand_name'].split(', ')
if 'scene_type' in relevant_columns:
relevant_columns.remove('scene_type')
# create filtered dictionary
filters_dict = self.create_availability_filtered_dictionary(
relevant_columns, row)
for brand in brand_names:
filters_dict.update({'brand_name': brand})
# call the generic method from KPIUtils_v2
availability_score = self.availability.calculate_availability(
**filters_dict)
if self.decide_availability_score(row, availability_score):
return True
return False
def calculate_availability_score(self, row, relevant_columns):
"""
this method calculates availability score according to columns from the data frame
:param row: data frame to calculate from
:param relevant_columns: columns to check in the excel file
:return: boolean
"""
# create filtered dictionary
filters_dict = self.create_availability_filtered_dictionary(
relevant_columns, row)
for key in filters_dict:
delete = [key for value in filters_dict[key] if value in ['nan']]
for key in delete:
del filters_dict[key]
# call the generic method from KPIUtils_v2
availability_score = self.availability.calculate_availability(
**filters_dict)
filtered_df = self.scif[self.get_filter_condition(
self.scif, **filters_dict)]
self.unique_brand_count = len(filtered_df['brand_name'].unique())
# check if this score should pass or fail
return self.decide_availability_score(row, availability_score)
def create_availability_filtered_dictionary(self, relevant_columns, row):
"""
this method creates a dictionary with keys according to the specific row in the template
:param relevant_columns: columns to create keys by
:param row: the specific row in the template
:return: dictionary
"""
# dictionary to send to the generic method
filters_dict = {}
self.handle_exclude_skus(filters_dict, relevant_columns, row)
# fill the dictionary
for column_value in relevant_columns:
if column_value == 'Store Additional Attribute 4' or column_value == 'store_type' or \
column_value == 'Store Additional Attribute 13':
continue
filters_dict[column_value] = map(
str.strip,
str(row.loc[column_value]).split(','))
return filters_dict
def calculate_or_availability(self, row, relevant_columns,
optional_columns):
"""
this method calculates availability score according to columns from the data frame
:param row: data frame to calculate from
:param relevant_columns: columns to check in the excel file
:return: boolean
"""
for optional_column in optional_columns:
# create filtered dictionary
temp_relevant_columns = relevant_columns[:]
temp_relevant_columns.append(optional_column)
filters_dict = self.create_availability_filtered_dictionary(
temp_relevant_columns, row)
for key in filters_dict:
delete = [
key for value in filters_dict[key] if value in ['nan']
]
for key in delete:
del filters_dict[key]
# call the generic method from KPIUtils_v2
availability_score = self.availability.calculate_availability(
**filters_dict)
# check if this score should pass or fail
if self.decide_availability_score(row, availability_score):
return True
return False
def filter_product_names(self, exclude_skus):
"""
this method filters list of SKUs from self.scif
:param exclude_skus: list of SKUs to exclude from template
:return: filtered list
"""
return filter(lambda sku: sku not in exclude_skus,
self.scif.product_name.values)
@staticmethod
def decide_availability_score(row, availability_score):
"""
this method decides if the score should pass or fail according to the template
:param row: scpecific row from template
:param availability_score: score
:return: Boolean
"""
if availability_score == 0:
return False
else:
if row['KPI Level 1 Name'] == 'Set Modeloramas' and row[
'KPI Level 3 Name'] == 'Hay o no hay Pop?':
if row['KPI Level 2 Name'] == 'Pop Exterior':
return availability_score > 1
elif row['KPI Level 2 Name'] == 'Pop Interior':
return availability_score > 1
elif row['KPI Level 1 Name'] == 'Set Self Execution' and row[
'KPI Level 3 Name'] == 'Hay o no hay # frentes':
return availability_score > 24
else:
return True
# surveys:
def calculate_survey_score(self, row):
"""
this method calculates survey score according to columns from the data frame
:param row: data frame to calculate from
:return: boolean
"""
question_code = str(int(row['Survey Question Code']))
if not self.survey_response.empty and \
not self.survey_response[self.survey_response.code == question_code].empty:
answer = self.survey_response.selected_option_text[
self.survey_response.code == question_code].values[0]
if answer == 'Si':
return True
else:
if row['KPI Level 2 Name'] == 'Primer Impacto' and answer == 'No tiene Enfirador':
return True
return False
else:
return False
# help functions:
def calculate_posm_displays(self):
new_kpi_set_fk = self.common2.get_kpi_fk_by_kpi_name(DISPLAY_KPI)
for display_fk in self.match_displays_in_scene['display_fk'].to_list():
self.common2.write_to_db_result(fk=new_kpi_set_fk,
result=0,
numerator_id=self.manufacturer_fk,
denominator_id=display_fk)
new_kpi_set_fk = self.common2.get_kpi_fk_by_kpi_name(POSM_KPI)
for product_fk in self.scif['product_fk'][self.scif['location_type'] ==
'POSM'].to_list():
self.common2.write_to_db_result(fk=new_kpi_set_fk,
result=0,
numerator_id=self.manufacturer_fk,
denominator_id=product_fk)
def handle_exclude_skus(self, filters_dict, relevant_columns, row):
"""
this method checks if there is value in 'exclude skus' column in the template.
if exists, it filters out the relevant skus from the calculation
:param filters_dict: filtered dictionary
:param relevant_columns: columns to create keys by
:param row: specific row to calculate
:return: None
"""
try:
exclude_skus = row['exclude skus'].split(',')
except AttributeError:
exclude_skus = []
if exclude_skus:
# filter out some product names according to template
product_names = self.filter_product_names(exclude_skus)
filters_dict['product_name'] = product_names
if 'exclude skus' in row.to_dict().keys(
) and 'exclude skus' in relevant_columns:
relevant_columns.remove('exclude skus')
# db functions:
def write_kpi_set_score_to_db(self, set_name, set_score):
"""
this method writes set kpi score to static.kps_results DB
:param set_name: set name
:param set_score: set score
:return: None
"""
# kpi_set_fk = self.kpi_static_data.kpi_set_fk[self.kpi_static_data.kpi_set_name == set_name].unique()[0]
# self.common.write_to_db_result(kpi_set_fk, self.LEVEL1, set_score)
new_kpi_set_fk = self.common2.get_kpi_fk_by_kpi_name(set_name)
self.common2.write_to_db_result(
fk=new_kpi_set_fk,
result=set_score,
numerator_id=self.manufacturer_fk,
denominator_id=self.store_id,
identifier_result=self.common2.get_dictionary(name=set_name))
def write_kpi_score_to_db(self, kpi_name, set_name, kpi_score,
write_to_all_levels):
"""
this method writes kpi score to static.kpk_results DB
:param kpi_name: name of level 2 kpi
:param set_name: name of related set
:param kpi_score: the score
:return: None
"""
# kpi_fk = \
# self.kpi_static_data.kpi_fk[
# (self.kpi_static_data.kpi_name.str.encode('utf-8') == kpi_name.encode('utf-8')) &
# (self.kpi_static_data.kpi_set_name == set_name)].values[0]
# self.common.write_to_db_result(kpi_fk, self.LEVEL2, kpi_score)
if write_to_all_levels:
new_kpi_fk = self.common2.get_kpi_fk_by_kpi_name(kpi_name)
self.common2.write_to_db_result(
fk=new_kpi_fk,
result=kpi_score,
should_enter=True,
identifier_parent=self.common2.get_dictionary(name=set_name),
identifier_result=self.common2.get_dictionary(name=kpi_name))
def write_atomic_to_db(self, atomic_name, atomic_score, kpi_name, set_name,
is_kpi_passed, curr_weight):
"""
this method writes atomic kpi score to static.kpi_results DB
:param curr_weight: current weight of atomic kpi
:param is_kpi_passed: is this kpi passed
:param atomic_name: atomic kpi name
:param atomic_score: the score
:param kpi_name: name of related kpi
:param set_name: name of related set
:return:
"""
# atomic_kpi_fk = \
# self.kpi_static_data.atomic_kpi_fk[(self.kpi_static_data.atomic_kpi_name == atomic_name) &
# (self.kpi_static_data.kpi_name == kpi_name) &
# (self.kpi_static_data.kpi_set_name == set_name)].values[0]
# attrs = self.common.create_attributes_dict(fk=atomic_kpi_fk, score=is_kpi_passed, level=self.LEVEL3)
# attrs['result'] = {0: atomic_score}
# attrs['kpi_weight'] = {0: curr_weight}
# query = insert(attrs, self.common.KPI_RESULT)
# self.common.kpi_results_queries.append(query)
identifier_parent = self.common2.get_dictionary(name=kpi_name)
if atomic_name == kpi_name:
identifier_parent = self.common2.get_dictionary(name=set_name)
new_atomic_fk = self.common2.get_kpi_fk_by_kpi_name(atomic_name)
# kpi_fk = \
# self.new_static_data[self.new_static_data['client_name'].str.encode('utf-8') == kpi_name.encode('utf-8')][
# 'pk'].values[0]
self.common2.write_to_db_result(fk=new_atomic_fk,
result=atomic_score,
numerator_id=self.manufacturer_fk,
denominator_id=self.store_id,
weight=curr_weight,
should_enter=True,
score=is_kpi_passed,
identifier_parent=identifier_parent)
def mpis_merger(self):
try:
mpis = self.match_product_in_scene.merge(self.products, on='product_fk', suffixes=['', '_p']) \
.merge(self.scene_info, on='scene_fk', suffixes=['', '_s']) \
.merge(self.templates, on='template_fk', suffixes=['', '_t'])
return mpis
except:
pass
def get_filter_condition(self, df, **filters):
"""
:param df: The data frame to be filters.
:param filters: These are the parameters which the data frame is filtered by.
Every parameter would be a tuple of the value and an include/exclude flag.
INPUT EXAMPLE (1): manufacturer_name = ('Diageo', DIAGEOAUGENERALToolBox.INCLUDE_FILTER)
INPUT EXAMPLE (2): manufacturer_name = 'Diageo'
:return: a filtered Scene Item Facts data frame.
"""
if not filters:
return df['pk'].apply(bool)
if self.facings_field in df.keys():
filter_condition = (df[self.facings_field] > 0)
else:
filter_condition = None
for field in filters.keys():
if field in df.keys():
if isinstance(filters[field], tuple):
value, exclude_or_include = filters[field]
else:
value, exclude_or_include = filters[
field], self.INCLUDE_FILTER
if not value:
continue
if not isinstance(value, list):
value = [value]
if exclude_or_include == self.INCLUDE_FILTER:
condition = (df[field].isin(value))
elif exclude_or_include == self.EXCLUDE_FILTER:
condition = (~df[field].isin(value))
elif exclude_or_include == self.CONTAIN_FILTER:
condition = (df[field].str.contains(value[0], regex=False))
for v in value[1:]:
condition |= df[field].str.contains(v, regex=False)
else:
continue
if filter_condition is None:
filter_condition = condition
else:
filter_condition &= condition
else:
pass
# Log.warning('field {} is not in the Data Frame'.format(field))
return filter_condition
class CCPHLToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.template_data = pd.read_excel(TEMPLATE_PATH, 'KPIs').fillna('')
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_new_kpi_static_data()
self.kpi_static_data = self.kpi_static_data[self.kpi_static_data['kpi_family_fk'] == CCPHLConsts.CUSTOM_KPIS]
self.kpi_results_queries = []
self.mapping_param = {"manufacturer": "manufacturer_name"}
self.mapping_entity = {"manufacturer": "manufacturer_fk", "store": "store_id",
"scene_type": "template_fk", "brand": "brand_fk", "product": "product_fk"}
def main_calculation(self):
"""
This function calculates the KPI results.
"""
for group in self.template_data[CCPHLConsts.KPI_GROUP].unique():
kpis = self.template_data[self.template_data[CCPHLConsts.KPI_GROUP] == group]
if kpis.empty:
print("KPI Group:{} is not valid".format(group))
continue
for row_num, kpi in kpis.iterrows():
if kpi[CCPHLConsts.KPI_GROUP] == CCPHLConsts.SHELF_PURITY:
self.calculate_self_purity_util(kpi)
elif kpi[CCPHLConsts.KPI_GROUP] == CCPHLConsts.SHELF_CUTIL:
self.calculate_self_purity_util(kpi)
else:
continue
self.common.commit_results_data_to_new_tables()
def calculate_self_purity_util(self, kpi):
df_scene_data = self.scif
if df_scene_data.empty:
return
scene_types = [x.strip() for x in kpi[CCPHLConsts.SCENE_TYPE].split(',')]
filter_param_name_1 = kpi['filter_param_name_1'].strip()
if len(filter_param_name_1) != 0:
filter_param_value_1 = kpi['filter_param_value_1'].strip()
else:
filter_param_value_1 = ""
if str(kpi[CCPHLConsts.EXCLUDE_EMPTY]).upper() == 'Y':
df_scene_data = df_scene_data[df_scene_data[CCPHLConsts.PRODUCT_FK] != CCPHLConsts.EMPTY]
df_scene_data = df_scene_data[df_scene_data[CCPHLConsts.PRODUCT_FK] != CCPHLConsts.GENERAL_EMPTY]
if str(kpi[CCPHLConsts.EXCLUDE_IRRELEVANT]).upper() == 'Y':
df_scene_data = df_scene_data[df_scene_data[CCPHLConsts.IRRELEVANT] != CCPHLConsts.IRRELEVANT]
df_kpi_level_2_fk = self.kpi_static_data[self.kpi_static_data['type'] == kpi['kpi_name']]
if df_kpi_level_2_fk.empty:
kpi_level_2_fk = 0
else:
kpi_level_2_fk = df_kpi_level_2_fk.iloc[0]['pk']
df_scene_data = df_scene_data[df_scene_data['template_name'].isin(scene_types)]
group_list = []
for idx in range(1, 5):
entity = kpi['entity' + str(idx)].strip()
if entity == 'N/A' or len(entity) == 0:
continue
else:
entity = self.mapping_entity[kpi['entity' + str(idx)].strip()]
group_list.append(entity)
denominator = 0
if group_list[0] == 'store_id':
total_facings = df_scene_data['facings'].sum()
denominator = float(total_facings)
if len(filter_param_value_1) != 0:
df_scene_data2 = df_scene_data[df_scene_data[self.mapping_param[filter_param_name_1]]==filter_param_value_1]
else:
df_scene_data2 = df_scene_data
filter_columns = list(group_list)
filter_columns.append('facings')
df_scene_data2 = df_scene_data2[filter_columns]
df_purity = pd.DataFrame(df_scene_data2.groupby(group_list).sum().reset_index())
store_zero_results = str(kpi[CCPHLConsts.STORE_ZERO_RESULTS]).strip().upper()
for row_num, row_data in df_purity.iterrows():
numerator = row_data['facings']
if group_list[0] == 'template_fk':
df_scene_count = df_scene_data[df_scene_data['template_fk'] == row_data['template_fk']]
if df_scene_count.empty:
total_facings = 0
else:
total_facings = df_scene_count['facings'].sum()
denominator = float(total_facings)
try:
result = round(float(numerator) / float(denominator), 4)
except ZeroDivisionError:
print("Error: {}".format(ZeroDivisionError.message))
continue
if kpi_level_2_fk != 0:
if result == 0:
if store_zero_results == 'Y':
self.common.write_to_db_result_new_tables(fk=kpi_level_2_fk,
numerator_id=row_data[group_list[len(group_list)-1]],
denominator_id=row_data[group_list[0]],
numerator_result=numerator,
denominator_result=denominator,
result=result,
score=result)
else:
self.common.write_to_db_result_new_tables(fk=kpi_level_2_fk,
numerator_id=row_data[group_list[len(group_list) - 1]],
denominator_id=row_data[group_list[0]],
numerator_result=numerator,
denominator_result=denominator,
result=result,
score=result)
class CCBOTTLERSUSREDToolBox:
def __init__(self, data_provider, output, calculation_type):
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.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.scif = self.scif[self.scif['product_type'] != "Irrelevant"]
self.united_scenes = self.get_united_scenes(
) # we don't need to check scenes without United products
self.survey = Survey(self.data_provider, self.output)
self.templates = {}
self.calculation_type = calculation_type
if self.calculation_type == Const.SOVI:
self.TEMPLATE_PATH = TEMPLATE_PATH
self.RED_SCORE = Const.RED_SCORE
self.RED_SCORE_INTEG = Const.RED_SCORE_INTEG
for sheet in Const.SHEETS:
self.templates[sheet] = pd.read_excel(
self.TEMPLATE_PATH, sheetname=sheet).fillna('')
self.converters = self.templates[Const.CONVERTERS]
else:
self.TEMPLATE_PATH = SURVEY_TEMPLATE_PATH
self.RED_SCORE = Const.MANUAL_RED_SCORE
self.RED_SCORE_INTEG = Const.MANUAL_RED_SCORE_INTEG
for sheet in Const.SHEETS_MANUAL:
self.templates[sheet] = pd.read_excel(
self.TEMPLATE_PATH, sheetname=sheet).fillna('')
self.common_db_integ = Common(self.data_provider, self.RED_SCORE_INTEG)
self.kpi_static_data_integ = self.common_db_integ.get_kpi_static_data()
self.common_db = Common(self.data_provider, self.RED_SCORE)
self.region = self.store_info['region_name'].iloc[0]
self.store_type = self.store_info['store_type'].iloc[0]
if self.store_type in STORE_TYPES: #####
self.store_type = STORE_TYPES[self.store_type] ####
self.store_attr = self.store_info['additional_attribute_15'].iloc[0]
self.kpi_static_data = self.common_db.get_kpi_static_data()
main_template = self.templates[Const.KPIS]
self.templates[Const.KPIS] = main_template[
(main_template[Const.REGION] == self.region)
& (main_template[Const.STORE_TYPE] == self.store_type)]
self.scene_calculator = CCBOTTLERSUSSceneRedToolBox(
data_provider, output, self.templates, self)
self.scenes_results = pd.DataFrame(columns=Const.COLUMNS_OF_SCENE)
self.session_results = pd.DataFrame(columns=Const.COLUMNS_OF_SESSION)
self.all_results = pd.DataFrame(columns=Const.COLUMNS_OF_SCENE)
self.used_scenes = []
self.red_score = 0
self.weight_factor = self.get_weight_factor()
# main functions:
def main_calculation(self, *args, **kwargs):
"""
This function gets all the scene results from the SceneKPI, after that calculates every session's KPI,
and in the end it calls "filter results" to choose every KPI and scene and write the results in DB.
"""
main_template = self.templates[Const.KPIS]
if self.calculation_type == Const.SOVI:
self.scenes_results = self.scene_calculator.main_calculation()
session_template = main_template[main_template[Const.SESSION_LEVEL]
== Const.V]
for i, main_line in session_template.iterrows():
self.calculate_main_kpi(main_line)
else:
for i, main_line in main_template.iterrows():
self.calculate_manual_kpi(main_line)
self.choose_and_write_results()
def calculate_main_kpi(self, main_line):
"""
This function gets a line from the main_sheet, transfers it to the match function, and checks all of the
KPIs in the same name in the match sheet.
:param main_line: series from the template of the main_sheet.
"""
kpi_name = main_line[Const.KPI_NAME]
kpi_type = main_line[Const.SHEET]
relevant_scif = self.scif[
(self.scif['scene_id'].isin(self.united_scenes))
& (self.scif['product_type'] != 'Empty')]
scene_types = self.does_exist(main_line, Const.SCENE_TYPE)
if scene_types:
relevant_scif = relevant_scif[relevant_scif['template_name'].isin(
scene_types)]
scene_groups = self.does_exist(main_line, Const.SCENE_TYPE_GROUP)
if scene_groups:
relevant_scif = relevant_scif[relevant_scif['template_group'].isin(
scene_groups)]
if kpi_type == Const.SCENE_AVAILABILITY:
result = False if relevant_scif.empty else True
else:
isnt_dp = True if self.store_attr != Const.DP and main_line[
Const.STORE_ATTRIBUTE] == Const.DP else False
relevant_template = self.templates[kpi_type]
relevant_template = relevant_template[relevant_template[
Const.KPI_NAME] == kpi_name]
target = len(relevant_template) if main_line[Const.GROUP_TARGET] == Const.ALL \
else main_line[Const.GROUP_TARGET]
if main_line[Const.SAME_PACK] == Const.V:
result = self.calculate_availability_with_same_pack(
relevant_template, relevant_scif, isnt_dp, target)
else:
function = self.get_kpi_function(kpi_type)
passed_counter = 0
for i, kpi_line in relevant_template.iterrows():
answer = function(kpi_line, relevant_scif, isnt_dp)
if answer:
passed_counter += 1
result = passed_counter >= target
self.write_to_session_level(kpi_name=kpi_name, result=result)
def calculate_manual_kpi(self, main_line):
"""
This function gets a line from the main_sheet, transfers it to the match function, and checks all of the
KPIs in the same name in the match sheet.
:param main_line: series from the template of the main_sheet.
"""
kpi_name = main_line[Const.KPI_NAME]
relevant_template = self.templates[Const.SURVEY]
relevant_template = relevant_template[relevant_template[Const.KPI_NAME]
== kpi_name]
target = len(relevant_template) if main_line[Const.GROUP_TARGET] == Const.ALL \
else main_line[Const.GROUP_TARGET]
passed_counter = 0
for i, kpi_line in relevant_template.iterrows():
answer = self.calculate_survey_specific(kpi_line)
if answer:
passed_counter += 1
result = passed_counter >= target
self.write_to_session_level(kpi_name=kpi_name, result=result)
# write in DF:
def write_to_session_level(self, kpi_name, result=0):
"""
Writes a result in the DF
:param kpi_name: string
:param result: boolean
"""
result_dict = {Const.KPI_NAME: kpi_name, Const.RESULT: result * 1}
self.session_results = self.session_results.append(result_dict,
ignore_index=True)
def write_to_all_levels(self,
kpi_name,
result,
display_text,
weight,
scene_fk=None,
reuse_scene=False):
"""
Writes the final result in the "all" DF, add the score to the red score and writes the KPI in the DB
:param kpi_name: str
:param result: int
:param display_text: str
:param weight: int/float
:param scene_fk: for the scene's kpi
:param reuse_scene: this kpi can use scenes that were used
"""
score = self.get_score(weight) * (result > 0)
self.red_score += score
result_dict = {
Const.KPI_NAME: kpi_name,
Const.RESULT: result,
Const.SCORE: score
}
if scene_fk:
result_dict[Const.SCENE_FK] = scene_fk
if not reuse_scene:
self.used_scenes.append(scene_fk)
self.all_results = self.all_results.append(result_dict,
ignore_index=True)
self.write_to_db(kpi_name, score, display_text=display_text)
# survey:
def calculate_survey_specific(self,
kpi_line,
relevant_scif=None,
isnt_dp=None):
"""
returns a survey line if True or False
:param kpi_line: line from the survey sheet
:param relevant_scif:
:param isnt_dp:
:return: True or False - if the question gets the needed answer
"""
question = kpi_line[Const.Q_TEXT]
if not question:
question_id = kpi_line[Const.Q_ID]
if question_id == "":
Log.warning(
"The template has a survey question without ID or text")
return False
question = ('question_fk', int(question_id))
answers = kpi_line[Const.ACCEPTED_ANSWER].split(',')
min_answer = None if kpi_line[Const.REQUIRED_ANSWER] == '' else True
for answer in answers:
if self.survey.check_survey_answer(survey_text=question,
target_answer=answer,
min_required_answer=min_answer):
return True
return False
# availability:
def calculate_availability_with_same_pack(self, relevant_template,
relevant_scif, isnt_dp, target):
"""
checks if all the lines in the availability sheet passes the KPI, AND if all of these filtered scif has
at least one common product that has the same size and number of sub_packages.
:param relevant_template: all the match lines from the availability sheet.
:param relevant_scif: filtered scif
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we shouldn't calculate
DP lines
:param target: how many lines should pass
:return: boolean
"""
relevant_scif = relevant_scif.fillna("NAN")
# only items categorized as SSD should be evaluated in this calculation; see PROS-6342
relevant_scif = relevant_scif[relevant_scif['att4'] == 'SSD']
if relevant_scif.empty:
return False
sizes = relevant_scif['size'].tolist()
sub_packages_nums = relevant_scif['number_of_sub_packages'].tolist()
packages = set(zip(sizes, sub_packages_nums))
for package in packages:
passed_counter = 0
filtered_scif = relevant_scif[
(relevant_scif['size'] == package[0])
& (relevant_scif['number_of_sub_packages'] == package[1])]
for i, kpi_line in relevant_template.iterrows():
answer = self.calculate_availability(kpi_line, filtered_scif,
isnt_dp)
if answer:
passed_counter += 1
if passed_counter < target:
return False
return True
def calculate_availability(self, kpi_line, relevant_scif, isnt_dp):
"""
checks if all the lines in the availability sheet passes the KPI (there is at least one product
in this relevant scif that has the attributes).
:param relevant_scif: filtered scif
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we shouldn't calculate
DP lines
:param kpi_line: line from the availability sheet
:return: boolean
"""
if isnt_dp and kpi_line[Const.MANUFACTURER] in Const.DP_MANU:
return True
filtered_scif = self.filter_scif_availability(kpi_line, relevant_scif)
target = kpi_line[Const.TARGET]
return filtered_scif[
filtered_scif['facings'] > 0]['facings'].count() >= target
def filter_scif_specific(self, relevant_scif, kpi_line, name_in_template,
name_in_scif):
"""
takes scif and filters it from the template
:param relevant_scif: the current filtered scif
:param kpi_line: line from one sheet (availability for example)
:param name_in_template: the column name in the template
:param name_in_scif: the column name in SCIF
:return:
"""
values = self.does_exist(kpi_line, name_in_template)
if values:
if name_in_scif in Const.NUMERIC_VALUES_TYPES:
values = [float(x) for x in values]
return relevant_scif[relevant_scif[name_in_scif].isin(values)]
return relevant_scif
def filter_scif_availability(self, kpi_line, relevant_scif):
"""
calls filter_scif_specific for every column in the template of availability
:param kpi_line:
:param relevant_scif:
:return:
"""
names_of_columns = {
Const.MANUFACTURER: "manufacturer_name",
Const.BRAND: "brand_name",
Const.TRADEMARK: "att2",
Const.SIZE: "size",
Const.NUM_SUB_PACKAGES: "number_of_sub_packages",
Const.PREMIUM_SSD: "Premium SSD",
Const.INNOVATION_BRAND: "Innovation Brand",
}
for name in names_of_columns:
relevant_scif = self.filter_scif_specific(relevant_scif, kpi_line,
name,
names_of_columns[name])
return relevant_scif
# SOS:
def calculate_sos(self, kpi_line, relevant_scif, isnt_dp):
"""
calculates SOS line in the relevant scif.
:param kpi_line: line from SOS sheet.
:param relevant_scif: filtered scif.
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter
all the DP products out of the numerator.
:return: boolean
"""
kpi_name = kpi_line[Const.KPI_NAME]
if kpi_line[Const.EXCLUSION_SHEET] == Const.V:
exclusion_sheet = self.templates[Const.SKU_EXCLUSION]
relevant_exclusions = exclusion_sheet[exclusion_sheet[
Const.KPI_NAME] == kpi_name]
for i, exc_line in relevant_exclusions.iterrows():
relevant_scif = self.exclude_scif(exc_line, relevant_scif)
relevant_scif = relevant_scif[relevant_scif['product_type'] != "Empty"]
den_type = kpi_line[Const.DEN_TYPES_1]
den_value = kpi_line[Const.DEN_VALUES_1]
relevant_scif = self.filter_by_type_value(relevant_scif, den_type,
den_value)
if kpi_line[Const.SSD_STILL] != "":
relevant_scif = self.filter_by_type_value(
relevant_scif, Const.SSD_STILL, kpi_line[Const.SSD_STILL])
num_type = kpi_line[Const.NUM_TYPES_1]
num_value = kpi_line[Const.NUM_VALUES_1]
num_scif = self.filter_by_type_value(relevant_scif, num_type,
num_value)
if isnt_dp:
num_scif = num_scif[~(
num_scif['manufacturer_name'].isin(Const.DP_MANU))]
target = float(kpi_line[Const.TARGET]) / 100
percentage = num_scif['facings'].sum() / relevant_scif['facings'].sum() if relevant_scif['facings'].sum() > 0 \
else 0
return percentage >= target
# SOS majority:
def calculate_sos_maj(self, kpi_line, relevant_scif, isnt_dp):
"""
calculates SOS majority line in the relevant scif. Filters the denominator and sends the line to the
match function (majority or dominant)
:param kpi_line: line from SOS majority sheet.
:param relevant_scif: filtered scif.
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter
all the DP products out of the numerator (and the denominator of the dominant part).
:return: boolean
"""
kpi_name = kpi_line[Const.KPI_NAME]
if kpi_line[Const.EXCLUSION_SHEET] == Const.V:
exclusion_sheet = self.templates[Const.SKU_EXCLUSION]
relevant_exclusions = exclusion_sheet[exclusion_sheet[
Const.KPI_NAME] == kpi_name]
for i, exc_line in relevant_exclusions.iterrows():
relevant_scif = self.exclude_scif(exc_line, relevant_scif)
relevant_scif = relevant_scif[relevant_scif['product_type'] != "Empty"]
den_type = kpi_line[Const.DEN_TYPES_1]
den_value = kpi_line[Const.DEN_VALUES_1]
relevant_scif = self.filter_by_type_value(relevant_scif, den_type,
den_value)
den_type = kpi_line[Const.DEN_TYPES_2]
den_value = kpi_line[Const.DEN_VALUES_2]
relevant_scif = self.filter_by_type_value(relevant_scif, den_type,
den_value)
if kpi_line[Const.MAJ_DOM] == Const.MAJOR:
answer = self.calculate_majority_part(kpi_line, relevant_scif,
isnt_dp)
elif kpi_line[Const.MAJ_DOM] == Const.DOMINANT:
answer = self.calculate_dominant_part(kpi_line, relevant_scif,
isnt_dp)
else:
Log.warning("SOS majority does not know '{}' part".format(
kpi_line[Const.MAJ_DOM]))
answer = False
return answer
def calculate_majority_part(self, kpi_line, relevant_scif, isnt_dp):
"""
filters the numerator and checks if the SOS is bigger than 50%.
:param kpi_line: line from SOS majority sheet.
:param relevant_scif: filtered scif.
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter
all the DP products out of the numerator.
:return: boolean
"""
num_type = kpi_line[Const.NUM_TYPES_1]
num_value = kpi_line[Const.NUM_VALUES_1]
num_scif = self.filter_by_type_value(relevant_scif, num_type,
num_value)
num_type = kpi_line[Const.NUM_TYPES_2]
num_value = kpi_line[Const.NUM_VALUES_2]
num_scif = self.filter_by_type_value(num_scif, num_type, num_value)
if num_scif.empty:
return None
if isnt_dp:
num_scif = num_scif[~(
num_scif['manufacturer_name'].isin(Const.DP_MANU))]
target = Const.MAJORITY_TARGET
return num_scif['facings'].sum() / relevant_scif['facings'].sum(
) >= target
def calculate_dominant_part(self, kpi_line, relevant_scif, isnt_dp):
"""
filters the numerator and checks if the given value in the given type is the one with the most facings.
:param kpi_line: line from SOS majority sheet.
:param relevant_scif: filtered scif.
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter
all the DP products out.
:return: boolean
"""
type_name = self.get_column_name(kpi_line[Const.NUM_TYPES_1],
relevant_scif)
values = str(kpi_line[Const.NUM_VALUES_1]).split(', ')
if isnt_dp:
relevant_scif = relevant_scif[~(
relevant_scif['manufacturer_name'].isin(Const.DP_MANU))]
if kpi_line[Const.ADD_IF_NOT_DP] != "":
values_to_add = str(kpi_line[Const.ADD_IF_NOT_DP]).split(', ')
values = values + values_to_add
if type_name in Const.NUMERIC_VALUES_TYPES:
values = [float(x) for x in values]
max_facings, needed_one = 0, 0
values_type = relevant_scif[type_name].unique().tolist()
if None in values_type:
values_type.remove(None)
current_sum = relevant_scif[
relevant_scif[type_name].isnull()]['facings'].sum()
if current_sum > max_facings:
max_facings = current_sum
for value in values_type:
current_sum = relevant_scif[relevant_scif[type_name] ==
value]['facings'].sum()
if current_sum > max_facings:
max_facings = current_sum
if value in values:
needed_one += current_sum
return needed_one >= max_facings
# helpers:
def get_column_name(self, field_name, df):
"""
checks what the real field name in DttFrame is (if it exists in the DF or exists in the "converter" sheet).
:param field_name: str
:param df: scif/products
:return: real column name (if exists)
"""
if field_name in df.columns:
return field_name
if field_name.upper() in self.converters[
Const.NAME_IN_TEMP].str.upper().tolist():
field_name = self.converters[self.converters[
Const.NAME_IN_TEMP].str.upper() == field_name.upper()][
Const.NAME_IN_DB].iloc[0]
return field_name
return None
def filter_by_type_value(self, relevant_scif, type_name, value):
"""
filters scif with the type and value
:param relevant_scif: current filtered scif
:param type_name: str (from the template)
:param value: str
:return: new scif
"""
if type_name == "":
return relevant_scif
values = value.split(', ')
new_type_name = self.get_column_name(type_name, relevant_scif)
if not new_type_name:
print "There is no field '{}'".format(type_name)
return relevant_scif
if new_type_name in Const.NUMERIC_VALUES_TYPES:
values = [float(x) for x in values]
return relevant_scif[relevant_scif[new_type_name].isin(values)]
@staticmethod
def exclude_scif(exclude_line, relevant_scif):
"""
filters products out of the scif
:param exclude_line: line from the exclusion sheet
:param relevant_scif: current filtered scif
:return: new scif
"""
if exclude_line[Const.PRODUCT_EAN] != "":
exclude_products = exclude_line[Const.PRODUCT_EAN].split(', ')
relevant_scif = relevant_scif[~(
relevant_scif['product_ean_code'].isin(exclude_products))]
if exclude_line[Const.BRAND] != "":
exclude_brands = exclude_line[Const.BRAND].split(', ')
relevant_scif = relevant_scif[~(
relevant_scif['brand_name'].isin(exclude_brands))]
return relevant_scif
@staticmethod
def does_exist(kpi_line, column_name):
"""
checks if kpi_line has values in this column, and if it does - returns a list of these values
:param kpi_line: line from template
:param column_name: str
:return: list of values if there are, otherwise None
"""
if column_name in kpi_line.keys() and kpi_line[column_name] != "":
cell = kpi_line[column_name]
if type(cell) in [int, float]:
return [cell]
elif type(cell) in [unicode, str]:
return cell.split(", ")
return None
def get_kpi_function(self, kpi_type):
"""
transfers every kpi to its own function
:param kpi_type: value from "sheet" column in the main sheet
:return: function
"""
if kpi_type == Const.SURVEY:
return self.calculate_survey_specific
elif kpi_type == Const.AVAILABILITY:
return self.calculate_availability
elif kpi_type == Const.SOS:
return self.calculate_sos
elif kpi_type == Const.SOS_MAJOR:
return self.calculate_sos_maj
else:
Log.warning(
"The value '{}' in column sheet in the template is not recognized"
.format(kpi_type))
return None
def choose_and_write_results(self):
"""
writes all the KPI in the DB: first the session's ones, second the scene's ones and in the end the ones
that depends on the previous ones. After all it writes the red score
"""
# self.scenes_results.to_csv('results/{}/scene {}.csv'.format(self.calculation_type, self.session_uid))####
# self.session_results.to_csv('results/{}/session {}.csv'.format(self.calculation_type, self.session_uid))####
main_template = self.templates[Const.KPIS]
self.write_session_kpis(main_template)
if self.calculation_type == Const.SOVI:
self.write_scene_kpis(main_template)
self.write_condition_kpis(main_template)
self.write_missings(main_template)
self.write_to_db(self.RED_SCORE, self.red_score)
# result_dict = {Const.KPI_NAME: 'RED SCORE', Const.SCORE: self.red_score}####
# self.all_results = self.all_results.append(result_dict, ignore_index=True)####
# self.all_results.to_csv('results/{}/{}.csv'.format(self.calculation_type, self.session_uid))####
def write_missings(self, main_template):
"""
write 0 in all the KPIs that didn't get score
:param main_template:
"""
for i, main_line in main_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
if not self.all_results[self.all_results[Const.KPI_NAME] ==
kpi_name].empty:
continue
result = 0
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
self.write_to_all_levels(kpi_name, result, display_text, weight)
def write_session_kpis(self, main_template):
"""
iterates all the session's KPIs and saves them
:param main_template: main_sheet.
"""
session_template = main_template[main_template[Const.CONDITION] == ""]
if self.calculation_type == Const.SOVI:
session_template = session_template[session_template[
Const.SESSION_LEVEL] == Const.V]
for i, main_line in session_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
result = self.session_results[self.session_results[Const.KPI_NAME]
== kpi_name]
if result.empty:
continue
result = result.iloc[0][Const.RESULT]
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
self.write_to_all_levels(kpi_name, result, display_text, weight)
def write_incremental_kpis(self, scene_template):
"""
lets the incremental KPIs choose their scenes (if they passed).
if KPI passed some scenes, we will choose the scene that the children passed
:param scene_template: filtered main_sheet
:return: the new template (without the KPI written already)
"""
incremental_template = scene_template[
scene_template[Const.INCREMENTAL] != ""]
while not incremental_template.empty:
for i, main_line in incremental_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
reuse_scene = main_line[Const.REUSE_SCENE] == Const.V
kpi_results = self.scenes_results[self.scenes_results[
Const.KPI_NAME] == kpi_name]
if not reuse_scene:
kpi_results = kpi_results[~(
kpi_results[Const.SCENE_FK].isin(self.used_scenes))]
true_results = kpi_results[kpi_results[Const.RESULT] > 0]
increments = main_line[Const.INCREMENTAL]
if ', ' in increments:
first_kpi = increments.split(', ')[0]
others = increments.replace(', '.format(first_kpi), '')
scene_template.loc[scene_template[Const.KPI_NAME] ==
first_kpi, Const.INCREMENTAL] = others
if true_results.empty:
scene_template.loc[scene_template[Const.KPI_NAME] ==
kpi_name, Const.INCREMENTAL] = ""
else:
true_results = true_results.sort_values(by=Const.RESULT,
ascending=False)
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
scene_fk = true_results.iloc[0][Const.SCENE_FK]
self.write_to_all_levels(
kpi_name,
true_results.iloc[0][Const.RESULT],
display_text,
weight,
scene_fk=scene_fk,
reuse_scene=reuse_scene)
scene_template = scene_template[~(
scene_template[Const.KPI_NAME] == kpi_name)]
incremental_template = scene_template[
scene_template[Const.INCREMENTAL] != ""]
return scene_template
def write_regular_scene_kpis(self, scene_template):
"""
lets the regular KPIs choose their scenes (if they passed).
Like in the incremental part - if KPI passed some scenes, we will choose the scene that the children passed
:param scene_template: filtered main_sheet (only scene KPIs, and without the passed incremental)
:return: the new template (without the KPI written already)
"""
for i, main_line in scene_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
reuse_scene = main_line[Const.REUSE_SCENE] == Const.V
kpi_results = self.scenes_results[self.scenes_results[
Const.KPI_NAME] == kpi_name]
if not reuse_scene:
kpi_results = kpi_results[~(
kpi_results[Const.SCENE_FK].isin(self.used_scenes))]
true_results = kpi_results[kpi_results[Const.RESULT] > 0]
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
if true_results.empty:
continue
true_results = true_results.sort_values(by=Const.RESULT,
ascending=False)
scene_fk = true_results.iloc[0][Const.SCENE_FK]
self.write_to_all_levels(kpi_name,
true_results.iloc[0][Const.RESULT],
display_text,
weight,
scene_fk=scene_fk,
reuse_scene=reuse_scene)
scene_template = scene_template[~(
scene_template[Const.KPI_NAME] == kpi_name)]
return scene_template
def write_not_passed_scene_kpis(self, scene_template):
"""
lets the KPIs not passed choose their scenes.
:param scene_template: filtered main_sheet (only scene KPIs, and without the passed KPIs)
"""
for i, main_line in scene_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
reuse_scene = main_line[Const.REUSE_SCENE] == Const.V
kpi_results = self.scenes_results[self.scenes_results[
Const.KPI_NAME] == kpi_name]
if not reuse_scene:
kpi_results = kpi_results[~(
kpi_results[Const.SCENE_FK].isin(self.used_scenes))]
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
if kpi_results.empty:
continue
scene_fk = kpi_results.iloc[0][Const.SCENE_FK]
self.write_to_all_levels(kpi_name,
0,
display_text,
weight,
scene_fk=scene_fk,
reuse_scene=reuse_scene)
def write_scene_kpis(self, main_template):
"""
iterates every scene_kpi that does not depend on others, and choose the scene they will take:
1. the incrementals take their scene (if they passed).
2. the regular KPIs that passed choose their scenes.
3. the ones that didn't pass choose their random scenes.
:param main_template: main_sheet.
"""
scene_template = main_template[
(main_template[Const.SESSION_LEVEL] != Const.V)
& (main_template[Const.CONDITION] == "")]
scene_template = self.write_incremental_kpis(scene_template)
scene_template = self.write_regular_scene_kpis(scene_template)
self.write_not_passed_scene_kpis(scene_template)
def write_condition_kpis(self, main_template):
"""
writes all the KPI that depend on other KPIs by checking if the parent KPI has passed and in which scene.
:param main_template: main_sheet
"""
condition_template = main_template[
main_template[Const.CONDITION] != '']
for i, main_line in condition_template.iterrows():
condition = main_line[Const.CONDITION]
kpi_name = main_line[Const.KPI_NAME]
if self.calculation_type == Const.MANUAL or main_line[
Const.SESSION_LEVEL] == Const.V:
kpi_results = self.session_results[self.session_results[
Const.KPI_NAME] == kpi_name]
else:
kpi_results = self.scenes_results[self.scenes_results[
Const.KPI_NAME] == kpi_name]
condition_result = self.all_results[
(self.all_results[Const.KPI_NAME] == condition)
& (self.all_results[Const.RESULT] > 0)]
if condition_result.empty:
continue
condition_result = condition_result.iloc[0]
condition_scene = condition_result[Const.SCENE_FK]
if condition_scene and Const.SCENE_FK in kpi_results:
results = kpi_results[kpi_results[Const.SCENE_FK] ==
condition_scene]
else:
results = kpi_results
if results.empty:
continue
result = results.iloc[0][Const.RESULT]
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
scene_fk = results.iloc[0][
Const.SCENE_FK] if Const.SCENE_FK in kpi_results else None
self.write_to_all_levels(kpi_name,
result,
display_text,
weight,
scene_fk=scene_fk)
def get_united_scenes(self):
return self.scif[self.scif['United Deliver'] ==
'Y']['scene_id'].unique().tolist()
def get_weight_factor(self):
sum_weights = self.templates[Const.KPIS][Const.WEIGHT].sum()
return sum_weights / 100.0
def get_score(self, weight):
return weight / self.weight_factor
def write_to_db(self, kpi_name, score, display_text=''):
"""
writes result in the DB
:param kpi_name: str
:param score: float
:param display_text: str
"""
if kpi_name == self.RED_SCORE:
self.write_to_db_result(self.common_db.get_kpi_fk_by_kpi_name(
self.RED_SCORE, 1),
score=score,
level=1)
if self.common_db_integ:
self.write_to_db_result(
self.common_db_integ.get_kpi_fk_by_kpi_name(
self.RED_SCORE_INTEG, 1),
score=score,
level=1,
set_type=Const.MANUAL)
else:
self.write_to_db_result(self.common_db.get_kpi_fk_by_kpi_name(
kpi_name, 2),
score=score,
level=2)
self.write_to_db_result(self.common_db.get_kpi_fk_by_kpi_name(
kpi_name, 3),
score=score,
level=3,
display_text=display_text)
if self.common_db_integ:
self.write_to_db_result(
self.common_db_integ.get_kpi_fk_by_kpi_name(kpi_name, 3),
score=score,
level=3,
display_text=kpi_name,
set_type=Const.MANUAL)
def write_to_db_result(self,
fk,
level,
score,
set_type=Const.SOVI,
**kwargs):
"""
This function creates the result data frame of every KPI (atomic KPI/KPI/KPI set),
and appends the insert SQL query into the queries' list, later to be written to the DB.
"""
if kwargs:
kwargs['score'] = score
attributes = self.create_attributes_dict(fk=fk,
level=level,
set_type=set_type,
**kwargs)
else:
attributes = self.create_attributes_dict(fk=fk,
score=score,
set_type=set_type,
level=level)
if level == self.common_db.LEVEL1:
table = self.common_db.KPS_RESULT
elif level == self.common_db.LEVEL2:
table = self.common_db.KPK_RESULT
elif level == self.common_db.LEVEL3:
table = self.common_db.KPI_RESULT
else:
return
query = insert(attributes, table)
if set_type == Const.SOVI:
self.common_db.kpi_results_queries.append(query)
else:
self.common_db_integ.kpi_results_queries.append(query)
def create_attributes_dict(self,
score,
fk=None,
level=None,
display_text=None,
set_type=Const.SOVI,
**kwargs):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
or
you can send dict with all values in kwargs
"""
kpi_static_data = self.kpi_static_data if set_type == Const.SOVI else self.kpi_static_data_integ
if level == self.common_db.LEVEL1:
if kwargs:
kwargs['score'] = score
values = [val for val in kwargs.values()]
col = [col for col in kwargs.keys()]
attributes = pd.DataFrame(values, columns=col)
else:
kpi_set_name = kpi_static_data[kpi_static_data['kpi_set_fk'] ==
fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame(
[(kpi_set_name, self.session_uid, self.store_id,
self.visit_date.isoformat(), format(score, '.2f'), fk)],
columns=[
'kps_name', 'session_uid', 'store_fk', 'visit_date',
'score_1', 'kpi_set_fk'
])
elif level == self.common_db.LEVEL2:
if kwargs:
kwargs['score'] = score
values = [val for val in kwargs.values()]
col = [col for col in kwargs.keys()]
attributes = pd.DataFrame(values, columns=col)
else:
kpi_name = kpi_static_data[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.common_db.LEVEL3:
if kwargs:
kwargs['score'] = score
values = tuple([val for val in kwargs.values()])
col = [col for col in kwargs.keys()]
attributes = pd.DataFrame([values], columns=col)
else:
data = kpi_static_data[kpi_static_data['atomic_kpi_fk'] == fk]
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = kpi_static_data[kpi_static_data['atomic_kpi_fk']
== fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame(
[(display_text, self.session_uid, kpi_set_name,
self.store_id, self.visit_date.isoformat(),
datetime.utcnow().isoformat(), score, kpi_fk, fk)],
columns=[
'display_text', 'session_uid', 'kps_name', 'store_fk',
'visit_date', 'calculation_time', 'score', 'kpi_fk',
'atomic_kpi_fk'
])
else:
attributes = pd.DataFrame()
return attributes.to_dict()
def commit_results(self):
"""
committing the results in both sets
"""
self.common_db.delete_results_data_by_kpi_set()
self.common_db.commit_results_data_without_delete()
if self.common_db_integ:
self.common_db_integ.delete_results_data_by_kpi_set()
self.common_db_integ.commit_results_data_without_delete()