def __init__(self, project, template_path):
self.template = pd.read_excel(template_path, sheetname=None)
self.indexed_template = {
key: df.set_index(Const.KPI_NAME)
for key, df in self.template.items()
if Const.KPI_NAME in df.columns
}
self.rds_conn = ProjectConnector(project, DbUsers.CalcAdmin)
self.cur = self.rds_conn.db.cursor()
self.kpi2_attribs = self.get_table_attributes(self.KPI)
self.kpi2_table = self.read_table(self.KPI)
self.kpi2_table = self.read_table(self.KPI)
self.kpi2_set = set(self.kpi2_table['type'])
self.kpi_pk_set = set(self.kpi2_table['pk'])
self.family_attribs = self.get_table_attributes(self.FAMILY_TABLE)
self.entity_table = self.read_table(self.ENTITY_TABLE)
self.family_table = self.read_table(self.FAMILY_TABLE)
self.calc_stage_table = self.read_table(self.CALC_STAGE_TABLE)
self.entity_set = set(self.entity_table['name'])
self.family_set = {x.upper() for x in self.family_table['name']}
self.calc_stage_set_pk = set(self.calc_stage_table['pk'])
self.family_dict = self.family_table.set_index('name')['pk'].to_dict()
self.entity_dict = self.entity_table.set_index('name')['pk'].to_dict()
self.pk_max_family = max(self.family_table['pk'])
self.errors = []
self.main()
def __init__(self, project, template_path):
self.entity_pairs = self.parse_all(template_path)
self.rds_conn = ProjectConnector(project, DbUsers.CalcAdmin)
self.cur = self.rds_conn.db.cursor()
self.custom_entity_attribs = self.get_table_attributes(
self.CUSTOM_ENTITY_TABLE)
self.entity_type_attribs = self.get_table_attributes(
self.ENTITY_TYPE_TABLE)
self.custom_entity_table = self.read_table(self.CUSTOM_ENTITY_TABLE)
self.entity_type_table = self.read_table(self.ENTITY_TYPE_TABLE)
self.custom_entity_set = set(self.custom_entity_table['name'])
self.entity_type_set = set(self.entity_type_table['name'])
self.custom_entity_pk = max(
[int(i) for i in self.custom_entity_table['pk']])
self.entity_type_pk = max(
[int(i) for i in self.entity_type_table['pk'] if int(i) < 999])
self.entity_type_dict = self.entity_type_table.set_index(
'name')['pk'].to_dict()
self.update_db()
def __init__(self,
arg_sd=None,
arg_ed=None,
arg_to=None,
pivoted='pivoted'):
self.log_prefix = BUCKET_FOLDER + '_' + PROJECT
Log.init(self.log_prefix)
Log.info(self.log_prefix + ' Opening SQL connector')
self.project_name = PROJECT
self.rds_conn = ProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.pivoted = True if pivoted == 'pivoted' else False
self.current_date = datetime.datetime.utcnow().date()
self.start_date = self.current_date - datetime.timedelta(1)
self.end_date = self.current_date - datetime.timedelta(1)
self.start_date = arg_sd if arg_sd else self.start_date.strftime(
'%Y-%m-%d')
self.end_date = arg_ed if arg_ed else self.end_date.strftime(
'%Y-%m-%d')
self.report_name = FILE_NAME + '_' + str(self.start_date) + '_' + str(self.end_date) \
+ ('_pivoted' if self.pivoted else '') \
+ '_' + str(int(time.mktime(datetime.datetime.now().timetuple()))) \
+ '.xlsx'
self.receivers = arg_to.split(',') if arg_to else None
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 check_db_values(self, xl_column_name, xl_column_type, db_column_name,
table_name):
Log.info(
"DB Values Check column_name:{} - Started".format(xl_column_name))
result = True
lst_rows = []
dbcon = ProjectConnector(self.project_name, DbUsers.CalculationEng)
for row_num, row_data in self.hierarchy_template.iterrows():
try:
if type(row_data[xl_column_name]) is str or type(
row_data[xl_column_name]) is unicode:
values = row_data[xl_column_name].strip()
else:
values = row_data[xl_column_name]
if xl_column_type == "list":
values = tuple(
["{}".format(v.strip()) for v in values.split(",")])
query = "SELECT {db_column_name} FROM {table_name} WHERE {db_column_name} IN {values}"
else:
values = u"{}".format(values)
query = u"SELECT {db_column_name} FROM {table_name} WHERE "
query += u" {db_column_name}='{values}'"
query = query.format(db_column_name=db_column_name,
table_name=table_name,
values=values)
query = query.strip().replace(",)", ")")
df_result = pd.read_sql(query, dbcon.db)
dict_result = dict()
if df_result.empty:
dict_result['column_name'] = xl_column_name
dict_result['values'] = values
dict_result['table_name'] = table_name
Log.error(dict_result)
lst_rows.append(dict_result)
if is_debug:
Log.info(dict_result)
result = False
else:
if xl_column_type == "list":
if len(df_result) != len(values):
dict_result['column_name'] = xl_column_name
dict_result['values'] = values
dict_result['table_name'] = table_name
Log.error(dict_result)
lst_rows.append(dict_result)
if is_debug:
Log.info(dict_result)
result = False
except Exception as ex:
result = False
Log.error("{} {}".format(ex.message, ex.args))
self.create_excel_log(lst_rows, "db_values_check")
Log.info("DB Values Check column_name:{} - Completed".format(
xl_column_name))
return result
def load_db_category_kpi(self):
dbcon = ProjectConnector(self.project_name, DbUsers.CalculationEng)
query = self.get_db_category_kpi_query()
df_result = pd.read_sql(query, dbcon.db)
if df_result.empty:
Log.error("Category KPI mapping missing in DB")
return pd.DataFrame()
else:
return df_result
def __init__(self, project, template_path):
self.template_results = self.load_template_results(template_path)
self.rds_conn = ProjectConnector(project, DbUsers.CalcAdmin)
self.cur = self.rds_conn.db.cursor()
self.result_value_attribs = self.get_table_attributes(
'static.kpi_result_value')
self.sql_results = self.result_values_query()
self.sql_types = self.result_types_query()
self.ps_type_pk = None
self.ps_result_pk = max(self.sql_results['pk'])
self.update_db()
def check_db_values(self):
result = True
dbcon = ProjectConnector(self.project_name, DbUsers.CalculationEng)
for check in self.data_mapping:
if "table_name" in check.keys():
xl_column_name = check['xl_column_name']
xl_column_type = check['xl_column_type']
db_column_name = check['db_column_name']
table_name = check['table_name']
for row_num, row_data in self.template.iterrows():
try:
values = row_data[xl_column_name].strip()
if xl_column_type == "list":
values = tuple([
"{}".format(v.strip())
for v in values.split(",")
])
query = "SELECT {db_column_name} FROM {table_name} WHERE {db_column_name} IN {values}"
else:
values = "{}".format(values)
query = "SELECT {db_column_name} FROM {table_name} WHERE {db_column_name}='{values}'"
query = query.format(db_column_name=db_column_name,
table_name=table_name,
values=values)
query = query.strip().replace(",)", ")")
df_result = pd.read_sql(query, dbcon.db)
if df_result.empty:
Log.error(
"{xl_column_name}={values} not in table {table_name}"
.format(xl_column_name=xl_column_name,
values=values,
table_name=table_name))
result = False
else:
if xl_column_type == "list":
if len(df_result) != len(values):
message = "{xl_column_name}={values}".format(
xl_column_name=xl_column_name,
values=values)
message += " one or more values not found in table {table_name}".format(
table_name=table_name)
Log.error(message)
result = False
except Exception as ex:
result = False
Log.error("{} {}".format(ex.message, ex.args))
return result
def load_all_active_products(self):
result = pd.DataFrame()
query = self.get_product_group_query()
dbcon = ProjectConnector(self.project_name, DbUsers.CalculationEng)
try:
df_result = pd.read_sql(query, dbcon.db)
if df_result.empty:
Log.error("Category KPI mapping missing in DB")
return result
else:
result = df_result
except Exception as ex:
Log.error("Error: {}".format(ex))
return result
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 __init__(self, project, path):
self.rds_conn = ProjectConnector(project, DbUsers.CalcAdmin)
self.cur = self.rds_conn.db.cursor()
self.kpi2_table = self.read_table(self.KPI)
self.kpi2_set = set(self.kpi2_table['type'])
self.kpi_pk_set = set(self.kpi2_table['pk'])
self.entity_table = self.read_table(self.ENTITY_TABLE)
self.family_table = self.read_table(self.FAMILY_TABLE)
self.calc_stage_table = self.read_table(self.CALC_STAGE_TABLE)
self.entity_set = set(self.entity_table['name'])
self.family_set = {x.upper() for x in self.family_table['name']}
self.calc_stage_set_pk = set(self.calc_stage_table['pk'])
self.family_dict = self.family_table.set_index('name')['pk'].to_dict()
self.entity_dict = self.entity_table.set_index('name')['pk'].to_dict()
self.template = pd.read_excel(path, sheet_name='KPIs')
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.templates_path = os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data')
self.excel_file_path = os.path.join(self.templates_path,
'Template.xlsx')
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 = ProjectConnector(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.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.products = self.data_provider[Data.PRODUCTS]
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.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = ProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_sub_brand_data = pd.read_sql_query(self.get_sub_brand_data(),
self.rds_conn.db)
self.kpi_results_queries = []
self.Presence_template = parse_template(TEMPLATE_PATH, "Presence")
self.BaseMeasure_template = parse_template(TEMPLATE_PATH,
"Base Measurement")
self.Anchor_template = parse_template(TEMPLATE_PATH, "Anchor")
self.Blocking_template = parse_template(TEMPLATE_PATH, "Blocking")
self.Adjaceny_template = parse_template(TEMPLATE_PATH, "Adjacency")
self.Eye_Level_template = parse_template(TEMPLATE_PATH, "Eye Level")
self.eye_level_definition = parse_template(TEMPLATE_PATH, "Shelves")
self.ignore_stacking = False
self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack'
self.availability = Availability(self.data_provider)
self.blocking_calc = Block(self.data_provider)
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.templates, on='template_fk', suffixes=['', '_t'])
def check_entity_values(self, entity_name_key, entity_value_key):
result = True
dbcon = ProjectConnector(self.project_name, DbUsers.CalculationEng)
if entity_name_key == "others":
entity = {'others': ('static_new.product', 'ean_code')}
else:
entity = {
'product': ('static_new.product', 'ean_code'),
'brand': ('static_new.brand', 'name'),
'category': ('static_new.category', 'name'),
'sub_category': ('static_new.sub_category', 'name')
}
for row_num, row_data in self.template.iterrows():
try:
if entity_name_key != "others":
xl_entity_type = row_data[entity_name_key]
else:
xl_entity_type = "others"
if pd.isnull(row_data[entity_value_key]):
continue
xl_entity_values = row_data[entity_value_key].strip()
if xl_entity_type not in entity.keys():
Log.error(
"{} not an valid entity, allowed entities={}".format(
xl_entity_type, entity.keys()))
result = False
continue
entity_table_name, entity_column_name = entity[xl_entity_type]
values = tuple([
"{}".format(v.strip()) for v in xl_entity_values.split(",")
])
values_count = Counter(values)
for value in values_count.items():
if value[1] > 1:
Log.error(
"row_number:{} Duplicate(s) values={} found in {}".
format(row_num + 2, entity_name_key, value))
result = False
query = "SELECT {db_column_name} FROM {table_name} WHERE {db_column_name} IN {values}"
query = query.format(db_column_name=entity_column_name,
table_name=entity_table_name,
values=values)
query = query.strip().replace(",)", ")")
df_result = pd.read_sql(query, dbcon.db)
if df_result.empty:
message = "row_num:{row_num} {xl_column_name}={values} ".format(
row_num=row_num + 3,
xl_column_name=entity_column_name,
values=values)
message += " not in table {table_name}".format(
table_name=entity_table_name)
Log.error(message)
result = False
else:
entity_values_rt = set(
df_result[entity_column_name].unique())
entity_values_ck = set(values)
missing_values = list(entity_values_ck - entity_values_rt)
if len(missing_values) != 0:
message = "row_number:{} {} =>{} missing values={}:".format(
row_num + 3, entity_name_key, entity_column_name,
missing_values)
Log.error(message)
result = False
except Exception as ex:
result = False
Log.error("{} {}".format(ex.message, ex.args))
return result
def check_entity_values(self, entity_name_key, entity_value_key):
Log.info("Entity Values Check - Started")
result = True
dbcon = ProjectConnector(self.project_name, DbUsers.CalculationEng)
if entity_name_key == "others":
entity = {'others': ('static_new.product', 'ean_code')}
else:
entity = {
'product': ('static_new.product', 'ean_code'),
'brand': ('static_new.brand', 'name'),
'category': ('static_new.category', 'name'),
'sub_category': ('static_new.sub_category', 'name')
}
lst_exceptions = []
for row_num, row_data in self.hierarchy_template.iterrows():
try:
if entity_name_key != "others":
xl_entity_type = row_data[entity_name_key]
else:
xl_entity_type = "others"
if pd.isnull(row_data[entity_value_key]):
continue
xl_entity_values = row_data[entity_value_key].strip()
if xl_entity_type not in entity.keys():
dict_exception = dict()
dict_exception[
'exception'] = 'invalid_entity ' + xl_entity_type
dict_exception[
'message'] = 'valid entities ' + entity.keys()
lst_exceptions.append(dict_exception)
if is_debug:
print(dict_exception)
result = False
continue
entity_table_name, entity_column_name = entity[xl_entity_type]
values = tuple([
"{}".format(v.strip()) for v in xl_entity_values.split(",")
])
values_count = Counter(values)
for value in values_count.items():
if value[1] > 1:
dict_exception = dict()
dict_exception['exception'] = "Duplicate values"
dict_exception[
'message'] = "row_number:{} Duplicate(s) values={} found in {}".format(
row_num + 2, entity_name_key, value)
lst_exceptions.append(dict_exception)
if is_debug:
print(dict_exception)
result = False
query = "SELECT {db_column_name} FROM {table_name} WHERE {db_column_name} IN {values}"
query = query.format(db_column_name=entity_column_name,
table_name=entity_table_name,
values=values)
query = query.strip().replace(",)", ")")
df_result = pd.read_sql(query, dbcon.db)
if df_result.empty:
dict_exception = dict()
dict_exception['exception'] = "missing column name"
message = "row_num:{row_num} {xl_column_name}={values}".format(
row_num=row_num + 3,
xl_column_name=entity_column_name,
values=values)
message += " not in table {table_name}".format(
table_name=entity_table_name)
dict_exception['message'] = message
if is_debug:
Log.error(dict_exception)
result = False
else:
entity_values_rt = set(
df_result[entity_column_name].unique())
entity_values_ck = set(values)
missing_values = list(entity_values_ck - entity_values_rt)
if len(missing_values) != 0:
dict_exception = dict()
dict_exception['exception'] = "missing values"
message = "row_number:{} {} =>{} missing values={}:".format(
row_num + 3, entity_name_key, entity_column_name,
missing_values)
dict_exception['message'] = message
if is_debug:
Log.error(dict_exception)
result = False
except Exception as ex:
result = False
Log.error("{} {}".format(ex.message, ex.args))
self.create_excel_log(lst_exceptions, "entity_values_check")
Log.info("Entity Values Check - Completed")
return result