def add_new_kpi_to_static_tables(self, set_fk, new_kpi_list):
"""
:param set_fk: The relevant KPI set FK.
:param new_kpi_list: a list of all new KPI's parameters.
This function adds new KPIs to the DB ('Static' table) - both to level2 (KPI) and level3 (Atomic KPI).
"""
session = OrmSession(self.project_name, writable=True)
with session.begin(subtransactions=True):
for kpi in new_kpi_list:
level2_query = """
INSERT INTO static.kpi (kpi_set_fk, display_text)
VALUES ('{0}', '{1}');""".format(
set_fk, kpi.get(KPI_NAME))
result = session.execute(level2_query)
kpi_fk = result.lastrowid
level3_query = """
INSERT INTO static.atomic_kpi (kpi_fk, name, description, display_text,
presentation_order, display)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}');""".format(
kpi_fk, kpi.get(KPI_NAME), kpi.get(KPI_NAME),
kpi.get(KPI_NAME), 1, 'Y')
session.execute(level3_query)
session.close()
return
def add_kpi_sets_to_static(self, set_names):
"""
This function is to be ran at a beginning of a projects - and adds the constant KPI sets data to the DB.
"""
session = OrmSession(self.project_name, writable=True)
with session.begin(subtransactions=True):
for set_name in set_names:
level1_query = """
INSERT INTO static.kpi_set (name, missing_kpi_score, enable, normalize_weight,
expose_to_api, is_in_weekly_report)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}');""".format(set_name, 'Bad', 'Y',
'N', 'N', 'N')
session.execute(level1_query)
session.close()
return
def insert_new_kpis_old(self, project, kpi_list=None):
"""
This function inserts KPI metadata to static tables
"""
session = OrmSession(project, writable=True)
try:
voting_process_pk_dic = {}
with session.begin(subtransactions=True):
for kpi in kpi_list.values()[0]:
if kpi.get('To include in first calculation?') == 4:
Log.info('Trying to write KPI {}'.format(
kpi.get('KPI name Eng')))
# # kpi_level_1_hierarchy = pd.DataFrame(data=[('Canteen', None, None, 'WEIGHTED_AVERAGE',
# # 1, '2016-11-28', None, None)],
# # columns=['name', 'short_name', 'eng_name', 'operator',
# # 'version', 'valid_from', 'valid_until', 'delete_date'])
# # self.output.add_kpi_hierarchy(Keys.KPI_LEVEL_1, kpi_level_1_hierarchy)
# if kpi.get('level') == 2:
# kpi_level_2_hierarchy = pd.DataFrame(data=[
# (1, kpi.get('KPI Name ENG'), None, None, None, None, kpi.get('weight'), 1, '2016-12-25', None, None)],
# columns=['kpi_level_1_fk', 'name', 'short_name', 'eng_name', 'operator',
# 'score_func', 'original_weight', 'version', 'valid_from', 'valid_until',
# 'delete_date'])
# self.output.add_kpi_hierarchy(Keys.KPI_LEVEL_2, kpi_level_2_hierarchy)
# elif kpi.get('level') == 3:
# kpi_level_3_hierarchy = pd.DataFrame(data=[(1, kpi.get('KPI Name ENG'), None, None, None,
# None, kpi.get('weight'), 1, '2016-12-25', None, None)],
# columns=['kpi_level_2_fk', 'name', 'short_name', 'eng_name', 'operator',
# 'score_func', 'original_weight', 'version', 'valid_from',
# 'valid_until', 'delete_date'])
# self.output.add_kpi_hierarchy(Keys.KPI_LEVEL_3, kpi_level_3_hierarchy)
# else:
# Log.info('No KPIs to insert')
# self.data_provider.export_kpis_hierarchy(self.output)
# insert_trans = """
# INSERT INTO static.kpi_level_1 (name,
# operator, version, valid_from)
# VALUES ('{0}', '{1}', '{2}', '{3}');""".format('test', 'WEIGHTED_AVERAGE', 1,
# '2016-11-28')
# insert_trans_level1 = """
# INSERT INTO static.kpi_set (name,
# missing_kpi_score, enable, normalize_weight, expose_to_api, is_in_weekly_report)
# VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}');""".format('Hypermarket', 'Bad',
# 'Y', 'N', 'N', 'N')
# Log.get_logger().debug(insert_trans_level1)
# result = session.execute(insert_trans_level1)
insert_trans_level2 = """
INSERT INTO static.kpi (kpi_set_fk,
logical_operator, weight, display_text)
VALUES ('{0}', '{1}', '{2}', '{3}');""".format(
34, kpi.get('Logical Operator'),
kpi.get('KPI Weight'), kpi.get('KPI name Eng'))
# # #
# # # # # insert_trans = """
# # # # # UPDATE static.kpi_level_1 SET short_name=null, eng_name=null, valid_until=null, delete_date=null
# # # # # WHERE pk=1;"""
# Log.get_logger().debug(insert_trans_level2)
result = session.execute(insert_trans_level2)
kpi_fk = result.lastrowid
insert_trans_level3 = """
INSERT INTO static.atomic_kpi (kpi_fk,
name, description, display_text, presentation_order, display)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}');""".format(
kpi_fk, kpi.get('KPI name Eng'),
kpi.get('KPI name Eng'), kpi.get('KPI name Eng'),
1, 'Y')
Log.get_logger().debug(insert_trans_level3)
result = session.execute(insert_trans_level3)
# voting_process_pk = result.lastrowid
# voting_process_pk_dic[kpi] = voting_process_pk
# Log.info('KPI level 1 was inserted to the DB')
# Log.info('Inserted voting process {} in project {} SQL DB'.format(voting_process_pk, project))
# voting_session_fk = self.insert_production_session(voting_process_pk, kpi, session)
# self.insert_production_tag(voting_process_pk, voting_session_fk, kpi, session)
session.close()
# return voting_process_pk_dic
return
except Exception as e:
Log.error(
'Caught exception while inserting new voting process to SQL: {}'
.format(str(e)))
return -1
