def select_user_engagements_by_stage(stage, engLeadID):
try:
dbConn = psycopg2.connect(
DBGeneral.return_db_native_connection('em_db'))
dbConn = dbConn
cur = dbConn.cursor()
queryStr = "select count(*) from ice_engagement INNER JOIN " +\
"ice_engagement_engagement_team ON " +\
"ice_engagement_engagement_team.engagement_id= " +\
"ice_engagement.uuid Where " +\
"(ice_engagement.engagement_stage " +\
"= '%s') and " % stage +\
"(ice_engagement_engagement_team.iceuserprofile_id = " +\
"%s );" % engLeadID
logger.debug("Query : " + queryStr)
cur.execute(queryStr)
result = cur.fetchall()
dbConn.close()
logger.debug("Query result: " + str(result))
logger.debug(result[0][0])
return result[0][0]
# If failed - count the failure and add the error to list of errors.
except BaseException:
errorMsg = "select_user_engagements_by_stage FAILED "
raise Exception(errorMsg, "select_user_engagements_by_stage")
def select_recent_vf_of_user(user_uuid, fetchNum):
try:
dbConn = psycopg2.connect(
DBGeneral.return_db_native_connection('em_db'))
dbConn = dbConn
cur = dbConn.cursor()
queryStr = "SELECT vf_id FROM public.ice_recent_engagement " +\
"where user_uuid = '%s' order by last_update " % user_uuid +\
"desc limit 20;"
logger.debug("Query : " + queryStr)
cur.execute(queryStr)
if (fetchNum == 0):
result = str(cur.fetchall())
elif (fetchNum == 1):
result = str(cur.fetchone())
if(result.find("',)") != -1): # formatting strings e.g uuid
result = result.partition('\'')[-1].rpartition('\'')[0]
elif(result.find(",)") != -1): # formatting ints e.g id
result = result.partition('(')[-1].rpartition(',')[0]
dbConn.close()
logger.debug("Query result: " + str(result))
return result
# If failed - count the failure and add the error to list of errors.
except BaseException:
errorMsg = "select_where FAILED "
raise Exception(errorMsg, "select_where")
def select_where_pr_state(queryColumnName, queryTableName,
whereParametrType, whereParametrValue, fetchNum):
try:
dbConn = psycopg2.connect(
DBGeneral.return_db_native_connection('em_db'))
dbConn = dbConn
cur = dbConn.cursor()
queryStr = \
"select %s from %s " % (queryColumnName, queryTableName) +\
"Where %s = '%s' and " % (
whereParametrType, whereParametrValue) +\
"state = 'peer_review';"
logger.debug("Query : " + queryStr)
cur.execute(queryStr)
if (fetchNum == 0):
result = str(cur.fetchall())
elif (fetchNum == 1):
result = str(cur.fetchone())
if (result.find("',)") != -1): # formatting strings e.g uuid
result = result.partition('\'')[-1].rpartition('\'')[0]
elif (result.find(",)") != -1): # formatting ints e.g id
result = result.partition('(')[-1].rpartition(',')[0]
dbConn.close()
if result is None:
errorMsg = "select_where_pr_state FAILED "
logger.error(errorMsg)
raise
logger.debug("Query result: " + str(result))
return result
# If failed - count the failure and add the error to list of errors.
except BaseException:
errorMsg = "select_where FAILED "
raise Exception(errorMsg, "select_where")
def delete_ecomp_release(uuid, name):
try:
queryTableName = "ice_ecomp_release"
# Connect to General 'default'.
dbConn = psycopg2.connect(
DBGeneral.return_db_native_connection("em_db"))
dbConn = dbConn
cur = dbConn.cursor()
except Exception as e:
errorMsg = "Failed to create connection to DBGeneral.because :" + \
str(e)
raise Exception(errorMsg)
try:
# Create INSERT query.
queryStr = "DELETE FROM %s WHERE uuid = '%s';" % (
queryTableName, uuid)
logger.debug("Query: " + queryStr)
cur.execute(queryStr) # Execute query.
dbConn.commit()
logger.debug("Test results are in General now.")
except Exception as e:
errorMsg = "Failed to delete ECOMP release from General ." +\
" because :" + \
str(e)
raise Exception(errorMsg)
raise
dbConn.close()
def insert_ecomp_release(uuid, name, ui_visibility="TRUE"):
try:
queryTableName = "ice_ecomp_release"
# Connect to General 'default'.
dbConn = psycopg2.connect(
DBGeneral.return_db_native_connection("em_db"))
dbConn = dbConn
cur = dbConn.cursor()
logger.debug("DATABASE_TYPE: " + settings.DATABASE_TYPE)
except Exception as e:
errorMsg = "Failed to create connection to General: " + str(e)
raise Exception(errorMsg)
try:
logger.debug("DATABASE_TYPE: " + settings.DATABASE_TYPE)
# Create INSERT query.
queryStr = "INSERT INTO %s " % queryTableName +\
"(""uuid, name, weight, ui_visibility"") VALUES " +\
"('%s', '%s', " % (uuid, name) +\
"'%s', '%s');" % (0, ui_visibility)
logger.debug("Query: " + queryStr)
cur.execute(queryStr) # Execute query.
dbConn.commit()
logger.debug("Test results are in General now.")
except Exception as e:
errorMsg = "Failed to insert ECOMP release to General:" + str(e)
raise Exception(errorMsg)
dbConn.close()
def update_query(queryStr):
try:
nativeIceDb = psycopg2.connect(
DBGeneral.return_db_native_connection('cms_db'))
dbConn = nativeIceDb
cur = dbConn.cursor()
logger.debug("Query: " + queryStr)
cur.execute(queryStr)
dbConn.commit()
dbConn.close()
logger.debug("Update query success!")
# If failed - count the failure and add the error to list of errors.
except BaseException:
raise Exception("Couldn't fetch answer using the given query.")
def update_checklist_to_review_state(queryTableName):
try:
dbConn = psycopg2.connect(
DBGeneral.return_db_native_connection('em_db'))
dbConn = dbConn
cur = dbConn.cursor()
queryStr = "UPDATE ice_checklist SET state='review' Where " +\
"name= '%s' and state= 'pending';" % (
queryTableName)
logger.debug("Query : " + queryStr)
cur.execute(queryStr)
dbConn.commit()
dbConn.close()
# If failed - count the failure and add the error to list of errors.
except BaseException:
errorMsg = "Could not Update User"
raise Exception(errorMsg, "Update")
def select_query(queryStr):
try:
nativeIceDb = psycopg2.connect(
DBGeneral.return_db_native_connection('cms_db'))
dbConn = nativeIceDb
cur = dbConn.cursor()
logger.debug("Query: " + queryStr)
cur.execute(queryStr)
result = str(cur.fetchone())
if (result.find("',)") != -1): # formatting strings e.g uuid
result = result.partition('\'')[-1].rpartition('\'')[0]
elif (result.find(",)") != -1): # formatting ints e.g id
result = result.partition('(')[-1].rpartition(',')[0]
dbConn.close()
logger.debug("Query result: " + str(result))
return result
# If failed - count the failure and add the error to list of errors.
except BaseException:
raise Exception("Couldn't fetch answer using the given query.")
def update_all_decisions_to_approve(whereParametrValue):
try:
dbConn = psycopg2.connect(
DBGeneral.return_db_native_connection('em_db'))
dbConn = dbConn
cur = dbConn.cursor()
queryStr = "UPDATE ice_checklist_decision SET " +\
"review_value='approved' , peer_review_value='approved' " +\
"Where checklist_id = '%s';" % (
whereParametrValue)
logger.debug(queryStr)
cur.execute(queryStr)
dbConn.commit()
logger.debug("Query : " + queryStr)
# If failed - count the failure and add the error to list of errors.
except Exception as e:
errorMsg = "Could not Update User"
logger.debug(e)
raise Exception(errorMsg, "Update")
finally:
dbConn.close()
def select_all_user_engagements(engLeadID):
try:
dbConn = psycopg2.connect(
DBGeneral.return_db_native_connection('em_db'))
dbConn = dbConn
cur = dbConn.cursor()
queryStr = "select COUNT(*) from ice_engagement_engagement_team" +\
" Where iceuserprofile_id = %s" % engLeadID +\
" and (select " +\
"engagement_stage from public.ice_engagement " +\
"where uuid = engagement_id LIMIT 1) != 'Archived';"
logger.debug("Query : " + queryStr)
cur.execute(queryStr)
result = cur.fetchall()
dbConn.close()
logger.debug("Query result: " + str(result))
logger.debug(result[0][0])
return result[0][0]
# If failed - count the failure and add the error to list of errors.
except BaseException:
errorMsg = "select_user_engagements_by_stage FAILED "
raise Exception(errorMsg, "select_user_engagements_by_stage")
