def sanity_check_counts():
""" Execute the sanity check queries and report the failed cases
Args:
No Arguments
Returns:
No return Arguments
"""
subtaskid = createSubTask("sanity check queries", getMaintaskId())
try:
conn = dbConnection()
cur = conn.cursor()
cur.execute("select query, name, expected_value from birepusr.etl_data_sanity_queries where project_name='planview'")
rows = cur.fetchall()
for row in rows:
conn = dbConnection()
cur = conn.cursor()
cur.execute(row[0])
x = cur.fetchall()
if x == []:
actual_value = 0
else:
actual_value = x[0][0]
condition = row[1]
expected_value = row[2]
status = 'Success'
if not expected_value == actual_value:
status = 'Fail'
message = '''Expected vs Actual values not matching for check '{0}': expected {1} rows but found {2}'''.format(condition, expected_value, actual_value)
send_custom_mail('Service-Desk ETL : DATA SANITY TEST FAILED', message)
executeQuery('''insert into birepusr.etl_data_sanity_test_results(condition, created_date, expected_value, actual_value, status, task_id,project_name) values ('{0}', now(), {1}, {2}, '{3}', '{4}','planview')'''.format(condition, expected_value, actual_value, status, subtaskid))
updateSubTask(subtaskid, "SUCCESS")
except (Exception, psycopg2.Error) as error:
insertErrorLog(subtaskid, error)
def insertCommonEntities():
"""
static insert all required entities in entity dimension.
this is one time insert
Args:
No Argument
Returns:
No Return
"""
executeQuery(insertEntitiesQuery)
def updateEntitySchema(entity_dim_id, schema):
"""
update planview schema information for each entity which is newly loaded into entity dimension
Args:
No Argument
Returns:
No Return
"""
executeQuery(
'''update idw.planview_entity_dim set entity_schema='{0}' where entity_dim_id={1};'''
.format(schema, entity_dim_id))
def updateSubTask(subtaskid, status):
"""
Method to update data extraction start entry whcih corresponds to respective sub task
Args:
1. integer : accpets subtask id as first argument for which audit entry needs to be updated
2. string : accepts staus as second argument for respective subtask id which needs to be updated
Returns:
No return variable
"""
subtaskupdatequery = ('''update birepusr.etl_audit_details
set end_date=now(),
status='{status}'
where audit_details_id={taskid}''').format(status=status, taskid=subtaskid)
print('''Subtask Ended with status {0}, taskid :{1}'''.format(status, subtaskid))
executeQuery(subtaskupdatequery)
def updateMainTask(taskid, status, taskname):
"""
Method to update data extraction start entry whcih corresponds to main task
Args:
1. integer : accpets task id as first argument for which audit entry needs to be updated
2. string : accepts staus as second argument for respective task id which needs to be updated
Returns:
No return variable
"""
maintaskupdatequery = ('''update birepusr.etl_audit
set end_date=now(),
status='{status}'
where audit_id={taskid} and end_date IS NULL''').format(status=status, taskid=taskid)
executeQuery(maintaskupdatequery)
print('''Main task Ended with status {0},Maintaskid : {1}'''.format(status, taskid))
if status == "SUCCESS":
send_completion_mail(None, status, taskname)
def updateSprintMetrics():
"""
This method trigger metrics update queries for a given sprint for given team
mertics includes estimated_points,completed points, active team members working on team etc.
Args:
No Arguments
Returns:
No Return
"""
subtaskid = createSubTask("update metrics for newly added sprints",
getMaintaskId())
executeQuery(dq.update_estimate_sprint_points)
executeQuery(dq.update_actual_completed_sprint_points)
executeQuery(dq.update_completed_sprint_points)
executeQuery(dq.update_team_members)
executeQuery(dq.update_actual_sprint_date)
executeQuery(dq.update_sprint_days)
updateSubTask(subtaskid, "SUCCESS")
def handler(event, context):
al.createMainTask("TimeSheet calculation", "COGS-Planview-Timesheet")
metrics.initUpdateSprintMetrics()
metrics.generateTimeSpentMetrics()
executeQuery(dq.update_maintenance_flag)
def executeBasicQueries():
executeQuery(queries)
executeQuery(dq.update_is_maintenance_flag_in_planview_project)
executeQuery(dq.update_is_time_reportable_flag)
executeQuery(dq.map_portfolio_and_project_dim_id_for_sprint_issues)
executeQuery(dq.update_is_consultant_flag)
executeQuery(dq.remove_system_user_from_team_map)